Department Number, Category Number and Cost of item based on the Item ID
Department Number, Category Number and Cost of item based on the
Item ID:-
--------------------------------------------------------------------------------------------
The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your
--------------------------------------------------------------------------------------------
The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.
select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your
INV On-hand Quatity of one Particular Item in whole Organization
INV On-hand Quatity of one Particular Item in whole Organization:-
-----------------------------------------------------------------------------------
The following Query will On-hand Quatity of particular item in the whole Organization
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';
Example:-
Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;
If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.
select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;
-----------------------------------------------------------------------------------
The following Query will On-hand Quatity of particular item in the whole Organization
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID';
Example:-
Note:- 123456 is the item ID of the particular item. This can be found in the base table of the item (MTL_SYSTEM_ITEMS_B). SEGMENT1 column of this table would have Item Name and Inventory_item_id is the primary column of the table. The follow query is using this value.
select sum(primary_transaction_quantity) from mtl_onhand_quantities_detail where inventory_item_id = 123456;
If you want to know the on-hand Quantity of particular item at all the Inventory Organization then use the following the Query.
select sum(primary_transaction_quantity), organization_id from mtl_onhand_quantities_detail where inventory_item_id = 'Your Inventory Organization ID'
group by organization_id;
Get all the
Assembly completion Lot Numbers
Get all the Assembly completion Lot
Numbers: -
-------------------------------------------------------
The following query gets the information about the Lot Numbers of Assembly Completion.
Note:- I have commented "mmt.organization_id = your_organization_id" condition. If you want to get the results specific to some inventory organization then, you can use this condition. Organization_id is representing the Inventory Organization.
SELECT mmt.transaction_id
, mmt.transaction_date
, mmt.inventory_item_id
, mmt.organization_id
, mtn.lot_number
, msi.shelf_life_days
, msi.shelf_life_code
, msi.segment1
FROM mtl_material_txns_val_v mmt
,mtl_transaction_lot_numbers mtn
,mtl_lot_numbers mln
, mtl_system_items_b msi
WHERE mmt.transaction_id = mtn.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mtn.lot_number = mln.lot_number
AND mtn.organization_id = mln.organization_id
AND mmt.transaction_type_name = 'WIP Completion'
AND mmt.transaction_action = 'Assembly completion'
AND NVL (mln.attribute1, 'N') <> 'Y'
-- AND mmt.organization_id = your_organization_id
AND mmt.transaction_date >= mmt.transaction_date
AND mmt.transaction_date <= mmt.transaction_date
AND mmt.inventory_item_id >= mmt.inventory_item_id
AND mmt.inventory_item_id <= mmt.inventory_item_id
-------------------------------------------------------
The following query gets the information about the Lot Numbers of Assembly Completion.
Note:- I have commented "mmt.organization_id = your_organization_id" condition. If you want to get the results specific to some inventory organization then, you can use this condition. Organization_id is representing the Inventory Organization.
SELECT mmt.transaction_id
, mmt.transaction_date
, mmt.inventory_item_id
, mmt.organization_id
, mtn.lot_number
, msi.shelf_life_days
, msi.shelf_life_code
, msi.segment1
FROM mtl_material_txns_val_v mmt
,mtl_transaction_lot_numbers mtn
,mtl_lot_numbers mln
, mtl_system_items_b msi
WHERE mmt.transaction_id = mtn.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mtn.lot_number = mln.lot_number
AND mtn.organization_id = mln.organization_id
AND mmt.transaction_type_name = 'WIP Completion'
AND mmt.transaction_action = 'Assembly completion'
AND NVL (mln.attribute1, 'N') <> 'Y'
-- AND mmt.organization_id = your_organization_id
AND mmt.transaction_date >= mmt.transaction_date
AND mmt.transaction_date <= mmt.transaction_date
AND mmt.inventory_item_id >= mmt.inventory_item_id
AND mmt.inventory_item_id <= mmt.inventory_item_id
Get the
Assembly Details
Get the Assembly Details:-
------------------------------
The following query would get all the details about the Assembly
SELECT a.assembly_item_id
, b.component_item_id
, c.segment1 component_item
, c.shelf_life_code
, c.shelf_life_days
, c.lot_control_code
,a.assembly_item_id
,a.organization_id
FROM bom_bill_of_materials_v a,
bom_inventory_components_v b,
mtl_system_items_b c
WHERE a.bill_sequence_id = b.bill_sequence_id
AND b.component_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
-- AND a.organization_id = l_organization_id
------------------------------
The following query would get all the details about the Assembly
SELECT a.assembly_item_id
, b.component_item_id
, c.segment1 component_item
, c.shelf_life_code
, c.shelf_life_days
, c.lot_control_code
,a.assembly_item_id
,a.organization_id
FROM bom_bill_of_materials_v a,
bom_inventory_components_v b,
mtl_system_items_b c
WHERE a.bill_sequence_id = b.bill_sequence_id
AND b.component_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
-- AND a.organization_id = l_organization_id
On-hand inventory information:-
--------------------------------------
The following select statement would extracts all on-hand inventory information
from Oracle
Applications base tables.
Note:- You may need to modify the query to match with your instance conditions
and your requirements.
SELECT
NVL(substr(org.organization_code, 1,3), ' ') orgcode
,NVL(substr(msi.segment1, 1, 8), ' ') seg11
,NVL(substr(msi.segment1, 9, 8), ' ') seg12
,NVL(substr(msi.segment1, 17, 4), ' ') seg13
,NVL(moq.subinventory_code, ' ') sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ' ') trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
-- AND hou.type = 'DC'
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;
Query : Get
Inventory Accounting Entries linked to GL
Query : Get Inventory Accounting Entries linked to GL
select mta.transaction_id,
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1
mmt.organization_id,
msi.segment1,
mta.transaction_date,
mta.primary_quantity,
gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
gcc.segment4 || '.' || gcc.segment5 account,
DECODE(SIGN(mta.transaction_value),
1,
mta.transaction_value,
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
NULL)) Entered_Dr,
DECODE(SIGN(mta.transaction_value),
-1,
(-1 * mta.transaction_value),
0,
0,
NULL,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value))) Entered_Cr,
DECODE(SIGN(mta.base_transaction_value),
1,
mta.base_transaction_value,
0,
0,
NULL) Accounted_Dr,
DECODE(SIGN(mta.base_transaction_value),
-1,
(-1 * mta.base_transaction_value),
0,
0,
NULL) Accounted_Cr,
gh.currency_code,
mtt.transaction_type_name,
decode(mta.gl_batch_id, -1, 'N', 'Y') "Transfered_Flag",
mta.gl_batch_id,
gh.je_header_id
from inv.mtl_material_transactions mmt,
inv.mtl_transaction_types mtt,
inv.mtl_system_items_b msi,
inv.mtl_transaction_accounts mta,
gl.gl_code_combinations gcc,
gl.gl_je_batches gb,
gl.gl_je_headers gh,
gl.gl_je_lines gl,
gl.gl_import_references gr
where mmt.organization_id = msi.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
and mmt.transaction_id = mta.transaction_id
and gcc.code_combination_id = mta.reference_account
and mtt.transaction_type_id = mmt.transaction_type_id
and gb.je_batch_id = gh.je_batch_id
and gh.je_header_id = gl.je_header_id
and gl.code_combination_id = mta.reference_account
and mta.gl_batch_id =
to_number(substr(gb.name, 1, instr(gb.name, ' ') - 1))
and gh.je_Category = 'MTL'
and gh.je_source = 'Inventory'
and gh.name = 'XXX' ---REPLACE XXX WITH NAME
and gl.je_line_num = gr.je_line_num
and gr.je_header_id = gl.je_header_id
and gr.je_line_num = gl.je_line_num
and mta.gl_batch_id = gr.reference_1
and gh.period_name = '&period_name' -- ENTER THE PERIOD
and upper(gb.name) like upper('%&gl_batch_name%')
order by 1
Query :
INVENTORY & ORDER MANAGEMENT
Query : INVENTORY & ORDER MANAGEMENT:
Just replace xxx in the query with your organization_id
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1
SELECT
msi.segment1 as Item_Number
, msi.description as Item_Description
, SUM(ool.ordered_quantity) AS SO_quantity
, AVG(oh_qty) as on_hand
FROM
(SELECT
msi.inventory_item_id, msi.organization_id, SUM(ohd.primary_transaction_quantity) AS oh_qty
FROM
mtl_system_items_b msi
JOIN mtl_onhand_quantities_detail ohd ON
(msi.inventory_item_id = ohd.inventory_item_id AND msi.organization_id = ohd.organization_id)
WHERE ohd.subinventory_code = 'Warehouse' AND msi.organization_id = xxx GROUP BY msi.inventory_item_id, msi.organization_id) Q1
JOIN
oe_order_lines_all ool ON (q1.inventory_item_id = ool.inventory_item_id AND q1.organization_id = ool.ship_from_org_id)
JOIN oe_order_headers_all ooh ON (ool.header_id = ooh.header_id)
JOIN mtl_system_items_b msi ON (ool.flow_status_code = 'AWAITING_SHIPPING' AND ool.inventory_item_id = msi.inventory_item_id AND ool.ship_from_org_id = msi.organization_id)
GROUP BY msi.inventory_item_id, msi.segment1, msi.description
ORDER BY msi.segment1
Onhand
Quantity at given date
Below is the query that can
help in getting onhand quantity at given date. The query inputs the Item ID,
organization ID and date.
SELECT SUM (target_qty)
, item_id
FROM (SELECT moqv.subinventory_code subinv
, moqv.inventory_item_id item_id
, SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
GROUP BY moqv.subinventory_code
, moqv.inventory_item_id
, moqv.item_cost
UNION
SELECT mmt.subinventory_code subinv
, mmt.inventory_item_id item_id
, -SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt
, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >= TO_DATE (:hist_date) + 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
GROUP BY mmt.subinventory_code
, mmt.inventory_item_id) oq
GROUP BY oq.item_id
Oracle Apps Inventory Queries
--Reservation Qty for SKU
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and mtr.inventory_item_id =1
and ooh.order_number = 316
--Reservation Qty for Order
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and ooh.order_number = 316
--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id,
hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag,
hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id,
hps.location_id,hpsu.primary_per_type
,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county
FROM
oe_order_headers_all ooh,
hz_cust_accounts_all hca,
hz_parties hp,hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
where
ooh.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id
and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hl.location_id = hps.location_id
and ooh.order_number = 351
order by hps.party_site_id
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and mtr.inventory_item_id =1
and ooh.order_number = 316
--Reservation Qty for Order
select SUM(mtr.reservation_quantity)
from
mtl_reservations mtr ,
oe_order_lines_all ool,
oe_order_headers_all ooh
where mtr.DEMAND_SOURCE_LINE_ID = ool.line_id
and ool.header_id = ooh.header_id
and ool.ship_from_org_id = mtr.organization_id
and ooh.order_number = 316
--Order to HZ (Customer Location)
select ooh.ship_to_org_id,ooh.sold_to_org_id ,hp.party_name,hca.party_id,
hca.account_number,hcsu.site_use_code,hcsu.location,hcsu.primary_flag,
hcsu.bill_to_site_use_id,--hpsu.site_use_type,hps.party_site_number,hps.party_site_id,
hps.location_id,hpsu.primary_per_type
,hl.address1,hl.address2,hl.address3,hl.address4,hl.city,hl.state,hl.postal_code,hl.county
FROM
oe_order_headers_all ooh,
hz_cust_accounts_all hca,
hz_parties hp,hz_party_sites hps,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu,
hz_locations hl
where
ooh.sold_to_org_id = hca.cust_account_id
and hca.party_id = hp.party_id
and hca.party_id = hps.party_id
and hca.cust_account_id = hcas.cust_account_id
and hps.party_site_id = hcas.party_site_id
and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and hl.location_id = hps.location_id
and ooh.order_number = 351
order by hps.party_site_id
INVENTORY QUERY
SELECT msi.segment1 AS item_code
, mp.organization_code AS org_code
, decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinv_code
, sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0))AS ohq
FROM
apps.mtl_system_items_b msi
, apps.mtl_onhand_quantities moq
, apps.mtl_parameters mp
, (SELECT msec.secondary_inventory_name,
msec.organization_id
FROM mtl_secondary_inventories msec
WHERE msec.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')) sinv
WHERE AND msi.inventory_item_status_code = 'Active'
AND msi.inventory_item_id = moq.inventory_item_id(+)
AND msi.organization_id = moq.organization_id(+)
AND (moq.subinventory_code IN ('Sub_INVA','Sub_INVB') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('Sub_INVA','Sub_INVB')))
AND msi.organization_id = mp.organization_id
AND msi.organization_id = sinv.organization_id
GROUP BY msi.segment1,
mp.organization_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name)
ORDER BY msi.segment1,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
msi.segment1;
--------------------------------------------------------------------------------------------------------
SELECT hou.NAME org, mp.organization_code AS org_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name) AS subinventory,
mil.concatenated_segments LOCATOR, mil.segment1 warehouse_id,
mil.segment2 product_type, mil.segment3 container,
mil.segment4 aisle, mil.segment5 bay, mil.segment6 shelf,
mil.segment7 project, msi.segment1 AS item_code,
msi.description description, msi.primary_uom_code primary_uom,
sum(decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.transaction_quantity,sinv.secondary_inventory_name,moq.transaction_quantity,0)) AS ohq,
moq.lot_number lot_number, mln.expiration_date expire_date,
cic.item_cost standard_cost, msi.list_price_per_unit,
mil.attribute1 container_seal, fu1.user_name qty_created_by,
fu2.user_name qty_last_updated_by, msi.attribute1 rotatable,
msi.attribute2 slep_eligible, msi.attribute3 categoryi_v,
TO_NUMBER (msi.attribute4) "Case To Bottle",
TO_NUMBER (msi.attribute5) "Case To Each",
TO_NUMBER (msi.attribute6) "Pallet QTY",
mcst.category_set_name category_name,
mc.concatenated_segments item_category,
TO_DATE (SUBSTR (mil.attribute2, 1, 10), 'YYYY/MM/DD') seal_date,
mil.attribute4 seal_color, mil.attribute3 seal_by,
element10.element_value "Indications",
element20.element_value "Dose",
element30.element_value "Customer",
element40.element_value "Adult/Ped",
element50.element_value "Pregnancy Class",
element60.element_value "Product Identifier",
element70.element_value "Conv UOM", mln.attribute1 slep_status,
mc.segment1 agent_type, mc.segment2 administrator,
mc.segment3 drug_type, mc.segment4 drug_strength,
mcr1.cross_reference, mcr2.cross_reference, mcr3.cross_reference,
mcr4.cross_reference, misi.min_minmax_quantity,
misi.max_minmax_quantity, msi.min_minmax_quantity,
msi.max_minmax_quantity, mln.attribute2 original_expiration_date,
mln.attribute3 first_extension_project,
mln.attribute4 first_extension_date,
mln.attribute5 second_extension_project,
mln.attribute6 second_extension_date,
mln.attribute7 third_extension_project,
mln.attribute8 third_extension_date,
mln.attribute9 fourth_extension_project,
mln.attribute10 fourth_extension_date, msi.organization_id,
msi.inventory_item_id
FROM apps.mtl_system_items_b msi,
apps.mtl_onhand_quantities moq,
apps.mtl_parameters mp,
apps.hr_organization_units hou,
apps.mtl_item_locations_kfv mil,
apps.mtl_lot_numbers mln,
apps.cst_item_costs cic,
apps.fnd_user fu1,
apps.fnd_user fu2,
apps.mtl_category_sets_tl mcst,
apps.mtl_category_sets_b mcs,
apps.mtl_categories_kfv mc,
apps.mtl_item_categories mic,
apps.mtl_descr_element_values element10,
apps.mtl_descr_element_values element20,
apps.mtl_descr_element_values element30,
apps.mtl_descr_element_values element40,
apps.mtl_descr_element_values element50,
apps.mtl_descr_element_values element60,
apps.mtl_descr_element_values element70,
apps.mtl_cross_references mcr1,
apps.mtl_cross_references mcr2,
apps.mtl_cross_references mcr3,
apps.mtl_cross_references mcr4,
apps.mtl_item_sub_inventories misi,
(SELECT msec.secondary_inventory_name, msec.organization_id
FROM apps.mtl_secondary_inventories msec
WHERE msec.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')) sinv
WHERE 1 = 1
AND hou.organization_id = mp.organization_id
AND moq.locator_id = mil.inventory_location_id(+)
AND moq.subinventory_code = mil.subinventory_code(+)
AND moq.organization_id = mil.organization_id(+)
AND mil.enabled_flag(+) = 'Y'
AND msi.inventory_item_status_code = 'Active'
AND msi.inventory_item_id = moq.inventory_item_id(+)
AND msi.organization_id = moq.organization_id(+)
AND mln.lot_number(+) = moq.lot_number
AND mln.organization_id(+) = moq.organization_id
AND mln.inventory_item_id(+) = moq.inventory_item_id
AND msi.inventory_item_id = cic.inventory_item_id(+)
AND msi.organization_id = cic.organization_id(+)
AND fu2.user_id(+) = moq.last_updated_by
AND fu1.user_id(+) = moq.created_by
AND mc.structure_id = mcs.structure_id
AND mc.category_id = mic.category_id
AND mic.inventory_item_id(+) = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_set_id = mcs.category_set_id
AND mcs.category_set_id = mcst.category_set_id
AND mcst.category_set_name = 'Inventory'
AND element10.element_name(+) = 'Indications'
AND element10.inventory_item_id(+) = msi.inventory_item_id
AND element20.element_name(+) = 'Dose'
AND element20.inventory_item_id(+) = msi.inventory_item_id
AND element30.element_name(+) = 'Customer'
AND element30.inventory_item_id(+) = msi.inventory_item_id
AND element40.element_name(+) = 'Adult/Ped'
AND element40.inventory_item_id(+) = msi.inventory_item_id
AND element50.element_name(+) = 'Pregnancy Class'
AND element50.inventory_item_id(+) = msi.inventory_item_id
AND element60.element_name(+) = 'Product Identifier'
AND element60.inventory_item_id(+) = msi.inventory_item_id
AND element70.element_name(+) = 'Conv UOM'
AND element70.inventory_item_id(+) = msi.inventory_item_id
AND msi.inventory_item_id = mcr1.inventory_item_id(+)
AND mcr1.cross_reference_type(+) = 'NDC'
AND msi.inventory_item_id = mcr2.inventory_item_id(+)
AND mcr2.cross_reference_type(+) = 'NSN'
AND msi.inventory_item_id = mcr3.inventory_item_id(+)
AND mcr3.cross_reference_type(+) = 'NDA'
AND msi.inventory_item_id = mcr4.inventory_item_id(+)
AND mcr4.cross_reference_type(+) = 'FMCS'
AND moq.organization_id = misi.organization_id(+)
AND moq.inventory_item_id = misi.inventory_item_id(+)
AND moq.subinventory_code = misi.secondary_inventory(+)
AND (moq.subinventory_code IN ('SUBIN_A','SUBINV_B') OR (moq.subinventory_code IS NULL AND sinv.secondary_inventory_name IN ('SUBIN_A','SUBINV_B')))
AND msi.organization_id = mp.organization_id
AND msi.organization_id = sinv.organization_id
GROUP BY hou.NAME,
msi.segment1,
mp.organization_code,
decode(moq.subinventory_code,sinv.secondary_inventory_name,moq.subinventory_code,sinv.secondary_inventory_name),
mil.concatenated_segments,
mil.segment1,
mil.segment2,
mil.segment3,
mil.segment4,
mil.segment5,
mil.segment6,
mil.segment7,
msi.description,
msi.primary_uom_code,
moq.lot_number,
mln.expiration_date,
cic.item_cost,
msi.list_price_per_unit,
mil.attribute1,
fu1.user_name,
fu2.user_name,
msi.attribute1,
msi.attribute2,
msi.attribute3,
TO_NUMBER (msi.attribute4),
TO_NUMBER (msi.attribute5),
TO_NUMBER (msi.attribute6),
mcst.category_set_name,
mc.concatenated_segments,
mil.attribute2,
mil.attribute3,
mil.attribute4,
element10.element_value,
element20.element_value,
element30.element_value,
element40.element_value,
element50.element_value,
element60.element_value,
element70.element_value,
mln.attribute1,
mc.segment1,
mc.segment2,
mc.segment3,
mc.segment4,
mcr1.cross_reference,
mcr2.cross_reference,
mcr3.cross_reference,
mcr4.cross_reference,
misi.min_minmax_quantity,
misi.max_minmax_quantity,
msi.min_minmax_quantity,
msi.max_minmax_quantity,
mln.attribute2,
mln.attribute3,
mln.attribute4,
mln.attribute5,
mln.attribute6,
mln.attribute7,
mln.attribute8,
mln.attribute9,
mln.attribute10,
msi.organization_id,
msi.inventory_item_id
ORDER BY 4;
No comments:
Post a Comment