Tuesday, July 18, 2023

Oracle apps inventory query for complete information

 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

/