Oracle Application inventory query for complete details of inventory item.
SELECT /*MTL_INVENTORY*/
mmt.transaction_date "Trans_Date",
decode(mmt.transaction_action_id),2,decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id),
to_char(mmt.transfer_transaction_id),5,decode(mmt.primary_quantity),-1,
to_char(mmt.transaction_id),to_char(mmt.transfer_transaction_id)
,3,to_char(mta.transaction_id),to_char(mmt.transaction_id) "Trans_ID",
mtt.transaction_type_name "Trans_Type",
DECODE(mta.transaction_value),1,mta.transaction_value,0,decode(mta.base_transaction_value),1,0,0,
decode(mta.primary_quantity),1,0,0,0,NULL,NULL,NULL,decode(mta.base_transaction_value),1,
mta.base_transaction_value,0,decode(mta.primary_quantity),1,0,0,0,NULL,NULL,NULL "Entered_Dr",
DECODE(mta.transaction_value), 0,decode(mta.base_transaction_value),-1,0,0,
decode(mta.primary_quantity),-1,0,null,null,-1,(-1*mta.transaction_value),NULL,
decode(mta.base_transaction_value),-1,(-1*mta.base_transaction_value),0,
decode(mta.primary_quantity), -1,0,null,null,NULL"Entered_Cr"
FROM
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
mtl_transaction_accounts mta,
cst_cost_elements cce,
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_item_locations mil,
mtl_transaction_reasons mtr,
mtl_parameters mp,
mtl_txn_source_types mtst,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mtl_system_items_vl msi
WHERE
mmt.transaction_id = mta.transaction_id AND
(mmt.transaction_action_id NOT IN (2,3,5) OR (mmt.transaction_action_id IN (2,3,5))) AND
mmt.primary_quantity < 0 AND
mmt.primary_quantity = mta.primary_quantity AND
mmt.inventory_item_id = mta.inventory_item_id AND
mmt.organization_id = mp.organization_id AND
mmt.organization_id = mil.organization_id AND
mmt.locator_id = mil.inventory_location_id AND
mmt.currency_conversion_type = glct.conversion_type AND
mtt.transaction_type_id = mmt.transaction_type_id AND
mtst.transaction_source_type_id = mmt.transaction_source_type_id AND
mtr.reason_id = mmt.reason_id AND
sob.set_of_books_id = gps.set_of_books_id AND
gps.period_name = jel.period_name AND
gps.application_id = 401 AND
--mta.transaction_date BETWEEN gps.start_date AND (trunc( gps.end_date)+0.99999) AND
msi.inventory_item_id = mmt.inventory_item_id AND
msi.organization_id = mmt.organization_id AND
cce.cost_element_id = mta.cost_element_id AND
lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND
lu1.lookup_code = mta.accounting_line_type AND
lu2.lookup_type = 'SYS_YES_NO' AND
lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND
lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND
lu3.lookup_code = 1 AND
jeh.je_header_id = jel.je_header_id AND
R.je_header_id = jeh.je_header_id AND
R.je_line_num = jel.je_line_num AND
jel.set_of_books_id = sob.set_of_books_id AND
mta.gl_batch_id = R.reference_1 AND
je_source = 'Inventory' AND
je_category = 'MTL' AND
R.gl_sl_link_id IS NOT NULL AND
R.reference_3 IS NOT NULL AND
mta.transaction_id = R.reference_3 AND
(mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL) AND
R.reference_3 IS NULL AND
mta.reference_account = jel.code_combination_id AND
NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND
DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND
NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND
NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ')
UNION ALL
SELECT
mmt.transaction_date "Trans_Date",
decode(mmt.transaction_action_id), 2, decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id),
to_char(mmt.transfer_transaction_id), 5, decode(mmt.primary_quantity),-1,to_char(mmt.transaction_id),
to_char(mmt.transfer_transaction_id), 3, to_char(mta.transaction_id),
to_char(mmt.transaction_id) "Trans_ID",
mtt.transaction_type_name "Trans_Type",
DECODE(mta.transaction_value), 1,mta.transaction_value, 0, decode(mta.base_transaction_value),
1,0, 0, decode(mta.primary_quantity),1,0, 0,0, NULL, NULL, NULL,decode(mta.base_transaction_value),1,
mta.base_transaction_value, 0,decode(mta.primary_quantity),1,0, 0,0, NULL, NULL, NULL "Entered_Dr",
DECODE(mta.transaction_value), 0,decode(mta.base_transaction_value), -1,0, 0,decode(mta.primary_quantity),
-1,0,null, null,-1,(-1*mta.transaction_value), NULL,decode(mta.base_transaction_value), -1,
(-1*mta.base_transaction_value),0,decode(mta.primary_quantity),-1,0,null, null, NULL "Entered_Cr"
FROM
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
mtl_transaction_accounts mta,
cst_cost_elements cce,
mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_item_locations mil,
mtl_transaction_reasons mtr,
mtl_parameters mp,
mtl_txn_source_types mtst,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mtl_system_items_vl msi
WHERE
mmt.transfer_transaction_id = mta.transaction_id AND
mmt.transaction_action_id IN (2,3,5) AND
mmt.primary_quantity > 0 AND
mmt.primary_quantity = mta.primary_quantity AND
mmt.inventory_item_id = mta.inventory_item_id AND
mmt.organization_id = mp.organization_id AND
mmt.organization_id = mil.organization_id AND
mmt.locator_id = mil.inventory_location_id AND
mmt.currency_conversion_type = glct.conversion_type AND
mtt.transaction_type_id = mmt.transaction_type_id AND
mtst.transaction_source_type_id = mmt.transaction_source_type_id AND
mtr.reason_id = mmt.reason_id AND
sob.set_of_books_id = gps.set_of_books_id AND
gps.period_name = jel.period_name AND
gps.application_id = 401 AND
--mta.transaction_date BETWEEN gps.start_date AND (trunc( gps.end_date)+0.99999) AND
msi.inventory_item_id = mmt.inventory_item_id AND
msi.organization_id = mmt.organization_id AND
--cce.cost_element_id(+) = mta.cost_element_id AND
lu1.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND
lu1.lookup_code = mta.accounting_line_type AND
lu2.lookup_type = 'SYS_YES_NO' AND
lu2.lookup_code = decode(mta.gl_batch_id, -1, 2, 1) AND
lu3.lookup_type = 'CST_VIEW_ACCOUNTING' AND
lu3.lookup_code = 1 AND
jeh.je_header_id = jel.je_header_id AND
R.je_header_id = jeh.je_header_id AND
R.je_line_num = jel.je_line_num AND
jel.set_of_books_id = sob.set_of_books_id AND
mta.gl_batch_id = R.reference_1 AND
je_source = 'Inventory' AND
je_category = 'MTL' AND
R.gl_sl_link_id IS NOT NULL AND
R.reference_3 IS NOT NULL AND
mta.transaction_id = R.reference_3 AND
(mta.gl_sl_link_id = R.gl_sl_link_id ) OR ( R.gl_sl_link_id IS NULL) AND
R.reference_3 IS NULL AND
mta.reference_account = jel.code_combination_id AND
NVL(mta.currency_code,sob.currency_code) = jeh.currency_code AND
DECODE(mta.encumbrance_type_id, NULL,'A','E') = jeh.actual_flag AND
NVL(mta.encumbrance_type_id,-1) = NVL(jeh.encumbrance_type_id,-1) AND
NVL(mmt.ussgl_transaction_code,'#ZZZ') = NVL(jeh.ussgl_transaction_code,'#ZZZ')
UNION ALL
SELECT
wt.transaction_date "Trans_Date",
wt.transaction_id "Trans_ID",
lu1.meaning "Trans_Type",
DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),1,nvl(wta.transaction_value,wta.base_transaction_value),0,0,NULL) "Entered_Dr",
DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),-1,(-1*nvl(wta.transaction_value,wta.base_transaction_value)),NULL) "Entered_Cr"
FROM
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
wip_transaction_accounts wta,
wip_transactions wt,
wip_entities we,
wip_lines wl,
cst_cost_elements cce,
wip_flow_schedules wfs,
po_headers poh,
bom_resources br,
mtl_transaction_reasons mtr,
bom_departments bd,
org_organization_definitions ood,
mtl_system_items_kfv msik,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mfg_lookups lu4,
mfg_lookups lu5
WHERE --wt.transaction_id=6454302 AND --6454308 AND --6454302
wta.transaction_id = wt.transaction_id AND
we.wip_entity_id = wt.wip_entity_id AND
wl.line_id(+) = wt.line_id AND
we.organization_id = wt.organization_id AND
bd.department_id(+) = wt.department_id AND
mtr.reason_id(+) = wt.reason_id AND
poh.po_header_id(+) = wt.po_header_id AND
cce.cost_element_id = wta.cost_element_id AND
br.resource_id(+) = wta.resource_id AND
wfs.wip_entity_id (+) = we.wip_entity_id AND
lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE' AND
lu1.lookup_code(+) = wt.transaction_type AND
lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND
lu2.lookup_code = wta.accounting_line_type AND
lu3.lookup_code(+) = wta.basis_type AND
lu3.lookup_type(+) = 'CST_BASIS' AND
lu4.lookup_code = 2 AND
lu4.lookup_type = 'CST_VIEW_ACCOUNTING' AND
lu5.lookup_type = 'SYS_YES_NO' AND
lu5.lookup_code = decode(wta.gl_batch_id, -1, 2, 1) AND
sob.set_of_books_id = ood.set_of_books_id AND
gps.application_id = 401 AND
gps.set_of_books_id = ood.set_of_books_id AND
gps.period_name = jeh.period_name AND
--wta.transaction_date BETWEEN gps.start_date AND (trunc(gps.end_date)+0.99999) AND
wt.currency_conversion_type = glct.conversion_type(+) AND
wt.organization_id = ood.organization_id AND
jel.je_header_id = jeh.je_header_id AND
R.je_header_id = jeh.je_header_id AND
R.je_line_num = jel.je_line_num AND
sob.set_of_books_id = jel.set_of_books_id AND
R.gl_sl_link_id IS NOT NULL AND
R.reference_3 IS NOT NULL AND
wta.gl_batch_id = R.reference_1 AND
wta.transaction_id = R.reference_3 AND
wta.reference_account = jel.code_combination_id AND
wta.GL_SL_LINK_ID = R.gl_sl_link_id AND
jeh.je_source = 'Inventory' AND
jeh.je_category = 'WIP' AND
msik.inventory_item_id(+) = we.primary_item_id AND
msik.organization_id(+) = we.organization_id
UNION ALL
SELECT
wt.transaction_date "Trans_Date",
wt.transaction_id "Trans_ID",
lu1.meaning "Trans_Type",
DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)),
1,nvl(wta.transaction_value,wta.base_transaction_value), 0,0,NULL) "Entered_Dr",
DECODE(sign(nvl(wta.transaction_value,wta.base_transaction_value)), -1,
(-1*nvl(wta.transaction_value,wta.base_transaction_value)), NULL) "Entered_Cr"
FROM
gl_je_lines jel,
gl_je_headers jeh,
gl_import_references R,
gl_period_statuses gps,
gl_sets_of_books sob,
gl_daily_conversion_types glct,
wip_transaction_accounts wta,
wip_transactions wt,
wip_entities we,
wip_lines wl,
cst_cost_elements cce,
wip_flow_schedules wfs,
po_headers poh,
bom_resources br,
mtl_transaction_reasons mtr,
bom_departments bd,
org_organization_definitions ood,
mtl_system_items_kfv msik,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
mfg_lookups lu4,
mfg_lookups lu5
WHERE
wta.transaction_id = wt.transaction_id AND
we.wip_entity_id = wt.wip_entity_id AND
wl.line_id(+) = wt.line_id AND
we.organization_id = wt.organization_id AND
bd.department_id(+) = wt.department_id AND
mtr.reason_id(+) = wt.reason_id AND
poh.po_header_id(+) = wt.po_header_id AND
cce.cost_element_id(+) = wta.cost_element_id AND
br.resource_id(+) = wta.resource_id AND
wfs.wip_entity_id (+) = we.wip_entity_id AND
lu1.lookup_type(+) = 'WIP_TRANSACTION_TYPE' AND
lu1.lookup_code(+) = wt.transaction_type AND
lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' AND
lu2.lookup_code = wta.accounting_line_type AND
lu3.lookup_code(+) = wta.basis_type AND
lu3.lookup_type(+) = 'CST_BASIS' AND
lu4.lookup_code = 2 AND
lu4.lookup_type = 'CST_VIEW_ACCOUNTING' AND
lu5.lookup_type = 'SYS_YES_NO' AND
lu5.lookup_code = decode(wta.gl_batch_id, -1, 2, 1) AND
sob.set_of_books_id = ood.set_of_books_id AND
gps.application_id = 401 AND
gps.set_of_books_id = ood.set_of_books_id AND
gps.period_name = jeh.period_name AND
--wta.transaction_date BETWEEN gps.start_date AND gps.end_date AND
wt.currency_conversion_type = glct.conversion_type(+) AND
wt.organization_id = ood.organization_id AND
jel.je_header_id = jeh.je_header_id AND
R.je_header_id = jeh.je_header_id AND
R.je_line_num = jel.je_line_num AND
sob.set_of_books_id = jel.set_of_books_id AND
wta.gl_batch_id = R.reference_1 AND
R.gl_sl_link_id IS NULL AND
R.reference_3 IS NULL AND
wta.reference_account = jel.code_combination_id AND
nvl(wta.currency_code,sob.currency_code) = jeh.currency_code AND
jeh.je_source = 'Inventory' AND
jeh.je_category = 'WIP' AND
msik.inventory_item_id(+) = we.primary_item_id AND
msik.organization_id(+) = we.organization_id
/
