Orgn: &&organization_id 
==================
SELECT * FROM mtl_parameters WHERE organization_id = &&organization_id;

Item details FOR Item: &&inv_item_id 
=========================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
  &&organization_id
AND inventory_item_id =
  &&inv_item_id;

Cost Type details FOR Cost Type: &&cost_type
=================================
SELECT * FROM cm_mthd_mst WHERE cost_mthd_code = ‘&&cost_type’;

Period Balances FOR Item: &&inv_item_id Orgn: &&organization_id 
==============================================
SELECT pbal.inventory_item_id AS item_id,
  pbal.primary_quantity       AS quantity,
  pbal.organization_id        AS mtl_organization_id,
  pbal.period_balance_id      AS perd_bal_id,
  NVL(pbal.lot_number,’ ‘),
  pbal.subinventory_code,
  pbal.locator_id,
  pbal.costed_flag,
  pbal.acct_period_id
FROM org_acct_periods oap,
  gmf_period_balances pbal,
  gmf_fiscal_policies gfp,
  gl_ledgers gl,
  hr_organization_information hoi
WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND oap.schedule_close_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND hoi.organization_id =
  &&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id         = hoi.org_information2
AND gl.ledger_id                = gfp.ledger_id
AND oap.period_set_name         = gl.period_set_name
AND oap.organization_id         = pbal.organization_id
AND pbal.acct_period_id         = oap.acct_period_id
AND pbal.inventory_item_id      =
  &&inv_item_id
AND pbal.organization_id =
  &&organization_id ;
  
Item Costs 
==========
SELECT c.inventory_item_id ,
  c.organization_id ,
  c.period_id ,
  TO_CHAR (end_date, ‘MM/DD/YYYY hh24:mi:ss’) perd_end_date ,
  m.cost_mthd_code ,
  c.cost_type_id ,
  c.acctg_cost ,
  c.itemcost_id ,
  d.cost_cmpntcls_id ,
  d.cost_analysis_code ,
  d.cmptcost_amt
FROM gl_item_cst c,
  gl_item_dtl d,
  cm_mthd_mst m
WHERE d.itemcost_id     = c.itemcost_id
AND c.inventory_item_id =
  &&inv_item_id
AND c.organization_id =
  &&organization_id
AND c.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
ORDER BY c.end_date DESC ;

Actual Cost Adjs 
============
SELECT d.inventory_item_id,
  d.organization_id,
  d.cost_cmpntcls_id,
  d.cost_analysis_code,
  d.cost_adjust_id,
  d.adjust_qty,
  d.adjust_qty_uom,
  d.adjust_cost,
  d.reason_code,
  NVL(d.adjustment_ind, DECODE(d.adjust_qty, 0, 1, 0)) adjustment_ind,
  TO_CHAR(d.adjustment_date,’MM/DD/YYYY hh24:mi:ss’),
  cmpt.usage_ind,
  d.adjust_status,
  d.subledger_ind,
  d.delete_mark
FROM cm_adjs_dtl d,
  cm_cmpt_mst cmpt,
  cm_mthd_mst m
WHERE d.inventory_item_id =
  &&inv_item_id
AND d.organization_id =
  &&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id        = m.cost_type_id
AND m.cost_mthd_code      = ‘&&cost_type’
AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
ORDER BY d.inventory_item_id,
  d.organization_id,
  d.adjustment_date,
  d.cost_adjust_id ;
  
Lot Cost Adjs 
==========
SELECT lca.inventory_item_id,
  lca.lot_number,
  lca.reason_code,
  TO_CHAR(lca.adjustment_date,’MM/DD/YYYY hh24:mi:ss’) adjustment_date,
  lca.adjustment_id,
  lcad.cost_cmpntcls_id,
  lcad.cost_analysis_code,
  (NVL(lcad.adjustment_cost,0) * NVL(lca.onhand_qty,0)) delta_amount,
  lca.onhand_qty,
  lca.organization_id,
  lcad.adjustment_cost,
  cmpt.usage_ind ,
  lca.old_cost_header_id ,
  lca.new_cost_header_id ,
  lca.gl_posted_ind ,
  lca.applied_ind
FROM gmf_lot_cost_adjustment_dtls lcad,
  gmf_lot_cost_adjustments lca,
  cm_cmpt_mst cmpt,
  cm_mthd_mst m
WHERE lca.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code     = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id    = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
  &&inv_item_id
AND lca.organization_id =
  &&organization_id
ORDER BY 1,
  12,
  3,
  4,
  6,
  7,
  8 ;
  
GL Expense Allocation 
===============
SELECT gps.calendar_code,
  gps.period_code,
  gps.cost_type_id,
  ‘ ‘ dtl_cost_mthd_code,
  bas.inventory_item_id AS item_id,
  bas.whse_code,
  bas.cmpntcls_id,
  cmpt.cost_cmpntcls_code,
  bas.analysis_code,
  dtl.allocated_expense_amt,
  NVL(dtl.period_qty,0),
  bas.organization_id,
  dtl.period_id,
  dtl.alloc_id,
  dtl.line_no,
  dtl.allocdtl_id,
  cmpt.usage_ind ,
  mst.alloc_code ,
  dtl.ac_status ,
  dtl.delete_mark
FROM gl_aloc_dtl dtl,
  gl_aloc_bas bas,
  gl_aloc_mst mst,
  gmf_period_statuses gps,
  cm_cmpt_mst cmpt ,
  hr_organization_information hoi ,
  cm_mthd_mst m
WHERE mst.legal_entity_id       = hoi.org_information2
AND hoi.organization_id         = bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id       =
  &&inv_item_id
AND bas.organization_id =
  &&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id          = mst.alloc_id
AND dtl.alloc_id          = bas.alloc_id
AND dtl.line_no           = bas.line_no
AND dtl.cost_type_id      = gps.cost_type_id
AND gps.period_id         = dtl.period_id
AND gps.start_date       >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code   = ‘&&cost_type’
AND gps.delete_mark    = 0
ORDER BY dtl.period_id,
  dtl.cost_type_id,
  bas.inventory_item_id,
  bas.organization_id,
  bas.cmpntcls_id,
  bas.analysis_code ;
  
Extract Header (COSTREVAL) 
====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );

Extract Lines (COSTREVAL) 
==================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );
  
SLA Events (COSTREVAL) 
=================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );
  
Extract Header (ACTCOSTADJ) 
=======================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Extract Lines (ACTCOSTADJ) 
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Sla Events (ACTCOSTADJ) 
====================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Extract Header (LOTCOSTADJ) 
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
Extract Lines (LOTCOSTADJ) 
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
SLA Events (LOTCOSTADJ) 
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
Extract Header (GLCOSTALOC) 
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
Extract Lines (GLCOSTALOC) 
====================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
SLA Events (GLCOSTALOC) 
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
ORDER MANAGEMENT RELATED: 
ORDER header info FOR sales ORDER NUMBER: &ord_num 
=====================================
SELECT o.name operating_unit,
  h.header_id,
  h.ship_from_org_id inv_org,
  h.order_type_id,
  h.flow_status_code status,
  h.open_flag,
  booked_flag,
  h.cancelled_flag,
  h.ordered_date ord_dt
FROM oe_order_headers_all h,
  hr_operating_units o
WHERE header_id =
  &hid
AND o.organization_id                                                    = h.org_id;

ORDER line details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==================================================
SELECT rtrim(l.line_number
  ||’.’
  || l.shipment_number
  ||’.’
  || l.option_number
  ||’.’
  || l.component_number
  ||’.’
  || l.service_number, ‘.’) line_num,
  l.line_id,
  l.split_from_line_id,
  l.split_by,
  l.flow_status_code status,
  l.source_document_type_id,
  l.org_id,
  l.ordered_item item,
  l.ordered_quantity qty1,
  l.order_quantity_uom um1,
  l.ordered_quantity2 qty2,
  l.ordered_quantity_uom2 um2,
  l.shipped_quantity sqty,
  l.shipping_quantity sgqty,
  l.ship_tolerance_above sta,
  l.ship_tolerance_below stb,
  l.fulfilled_flag ful_flag,
  l.shipping_interfaced_flag ship_int_flag,
  l.fulfilled_quantity fqty,
  l.invoiced_quantity invoice_qty,
  l.invoice_interface_status_code inv_int_sta_code,
  l.open_flag,
  l.booked_flag,
  l.cancelled_quantity cqty,
  l.cancelled_flag,
  l.inventory_item_id,
  l.ship_from_org_id,
  w.organization_code,
  w.process_enabled_flag,–w.loct_ctl wlc,
  m.lot_divisible_flag,
  m.tracking_quantity_ind,
  m.dual_uom_control dualum_ind,
  m.secondary_default_ind,
  m.primary_uom_code uom,
  m.secondary_uom_code uom2,
  m.dual_uom_deviation_high,
  m.dual_uom_deviation_low,
  m.lot_control_code lot_ctl,
  m.child_lot_flag sublot_ctl,
  m.location_control_code loct_ctl,
  m.grade_control_flag grade_ctl,
  m.lot_status_enabled status_ctl,
  –i.lot_indivisible, i.noninv_ind, i.dualum_ind, i.lot_ctl, i.sublot_ctl, i.loct_ctl,
  –i.grade_ctl,i.status_ctl,
  DECODE(m.ont_pricing_qty_source,’P’,’Primary’,’Secondary’) ont_pricing_qty_source,
  l.source_document_line_id sdli,
  l.source_type_code src_type,
  ott.name line_type,
  TO_CHAR(l.creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
  TO_CHAR(l.last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM oe_order_lines_all l,
  mtl_parameters w,
  mtl_system_items_b m,
  oe_transaction_types_tl ott
WHERE l.header_id =
  &hid
AND l.line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND l.ship_from_org_id  = w.organization_id
AND l.inventory_item_id = m.inventory_item_id
AND l.ship_from_org_id  = m.organization_id
AND l.line_type_id      = ott.transaction_type_id
AND ott.language        =
  ( SELECT fl.language_code FROM fnd_languages fl WHERE fl.installed_flag = ‘B’
  )
ORDER BY l.line_id;

Delivery line details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
====================================================
SELECT source_line_number,
  source_line_id,
  delivery_detail_id,
  split_from_delivery_detail_id,
  move_order_line_id,
  DECODE(rele ased_status,’R’,’R-Ready for rel’,’S’,’S-Rel to whse’,’Y’,’Y-Staged’, ‘C’,’C-Shipped’,’B’,’B-Backordered’,’D’,’D-Cancelled’,released_status) rel_sts,
  requested_quantity,
  requested_quantity_uom,
  requested_quantity2,
  requested_quantity_uom2,
  picked_quantity,
  picked_quantity2,
  shipped_quantity,
  shipped_quantity2,
  lot_number lotno,
  sublot_number slotno,
  oe_interfaced_flag oeif,
  inv_interfaced_flag invif,
  ship_tolerance_above sta,
  ship_tolerance_below stb,
  ship_set_id,
  inventory_item_id,
  src_requested_quantity,
  src_requested_quantity_uom,
  src_requested_quantity2,
  src_requested_quantity_uom2,
  organization_id inv_org,
  cancelled_quantity,
  cancelled_quantity2,
  delivered_quantity,
  delivered_quantity2,
  TO_CHAR(creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
  TO_CHAR(last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM wsh_delivery_details
WHERE source_header_id =
  &hid
AND source_line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
ORDER BY source_line_id,
  delivery_detail_id;
  
Reservation details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
====================================================
SELECT mso.sales_order_id,
  typ.name
INTO :sales_ord_id,
  :ord_type_name
FROM mtl_sales_orders mso,
  oe_order_headers_all ord,
  oe_transaction_types_tl typ
WHERE ord.header_id =
  &hid
AND ord.order_type_id = typ.transaction_type_id
AND language          =
  ( SELECT fl.language_code FROM fnd_languages fl WHERE fl.installed_flag = ‘B’
  )
AND mso.segment1 =
  &ord_num
AND typ.name = mso.segment2;
dbms_output.put_line(‘sales order id order number order type ‘);
dbms_output.put_line(‘——————————————————————‘);
dbms_output.put_line(rpad(TO_CHAR(:sales_ord_id), 16, ‘ ‘)||rpad(‘&ord_num’, 20, ‘ ‘)||:ord_type_name);
END;
/
SELECT res.reservation_id reserv_id,
  DECODE(res.ship_ready_flag, 1,’1=released’, 2,’2=submitted’, TO_CHAR(res.ship_ready_flag)) ship_ready,
  res.demand_source_header_id ds_head_id,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  res.demand_source_line_id ds_line_id,
  res.primary_reservation_quantity res_q,
  res.primary_uom_code uom,
  res.secondary_reservation_quantity sec_res_q,
  inv_convert.inv_um_convert( res.inventory_item_id, res.lot_number, res.organization_id, 5, res.primary_reservation_quantity, res.primary_uom_code, res.secondary_uom_code, NULL, NULL) calc_sec_res_q,
  res.secondary_uom_code uom2,
  res.lot_number lot_num,
  res.organization_id orgn_id,
  res.subinventory_code subinv,
  — res.revision rev,
  res.locator_id loc_id,
  res.detailed_quantity dtl_q,
  res.secondary_detailed_quantity sec_dtl_q,
  res.inventory_item_id inventory_item_id,
  itm.segment1 item,
  res.requirement_date requird_d,
  res.demand_source_delivery ds_deliv,
  res.demand_source_type_id ds_type,
  — res.serial_number serial_num,
  res.supply_source_header_id ss_header_id,
  res.supply_source_line_id ss_source_line,
  res.supply_source_line_detail ss_source_line_det
  –enable_timestamp ,to_char(res.creation_date,’dd-mon hh24:mi:ss’) create_dt
  –enable_timestamp ,to_char(res.last_update_date,’dd-mon hh24:mi:ss’) update_dt
  –enable_timestamp ,res.request_id request_id
FROM mtl_reservations res,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE res.demand_source_header_id          = :sales_ord_id
AND res.demand_source_type_id             IN (2,8,9,21,22)
AND res.demand_source_line_id              = lin.line_id(+)
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id)
AND res.organization_id                    = itm.organization_id(+)
AND res.inventory_item_id                  = itm.inventory_item_id(+)
ORDER BY NVL(lin.top_model_line_id, lin.line_id),
  NVL(lin.ato_line_id, lin.line_id),
  NVL(lin.sort_order, ‘0000’),
  NVL(lin.link_to_line_id, lin.line_id),
  NVL(lin.source_document_line_id, lin.line_id),
  lin.line_id,
  res.reservation_id;
  
Allocation details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==================================================
SELECT tmp.transaction_temp_id mtl_trns_id,
  tmp.move_order_line_id move_line_id,
  itm.segment1 item,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  tmp.primary_quantity prm_q,
  tmp.secondary_transaction_quantity sec_q,
  inv_convert.inv_um_convert( tmp.inventory_item_id, 5, tmp.primary_quantity, itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL) tmp_cal_sec_q,
  lot.primary_quantity lot_prm_q,
  lot.secondary_quantity lot_sec_q,
  inv_convert.inv_um_convert( tmp.inventory_item_id, lot.lot_number, tmp.organization_id, 5, NVL(lot.primary_quantity,0), itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
  tmp.secondary_uom_code uom2,
  lot.lot_number lot_num,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.pick_slip_number pick_slip,
  tmp.transfer_subinventory to_sub,
  tmp.transfer_to_location to_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_material_transactions_temp tmp,
  mtl_transaction_lots_temp lot,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.demand_source_line   = lin.line_id
AND lin.line_category_code     = ‘ORDER’
AND lin.ship_from_org_id       = itm.organization_id(+)
AND lin.inventory_item_id      = itm.inventory_item_id(+)
AND lot.transaction_temp_id (+)= tmp.transaction_temp_id
AND lin.header_id              =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);
–create index mtl_material_transactions_n99 on mtl_material_transactions(trx_source_line_id);
prompt mtl_material_transactions (trn) – picked lines
PROMPT
— This is commented out because it runs slowly without an index
–<do not run> CREATE INDEX MTL_MATL_TRANS_777
–<do not run> ON INV.MTL_MATERIAL_TRANSACTIONS
–<do not run> (trx_source_line_id);
SELECT
  /*moac_sql_no_changes*/
  trn.transaction_id mtl_trns_id,
  trn.move_order_line_id move_line_id,
  trn.OPM_COSTED_FLAG,
  trn.SHIPMENT_COSTED,
  trn.SO_ISSUE_ACCOUNT_TYPE,
  trn.COGS_RECOGNITION_PERCENT,
  trn.TRANSFER_PRICE,
  trn.FOB_POINT,
  trn.OWNING_ORGANIZATION_ID,
  trn.TRANSFER_ORGANIZATION_ID,
  trn.TRANSACTION_ACTION_ID,
  trn.TRANSACTION_SOURCE_TYPE_ID,
  trn.TRANSACTION_TYPE_ID,
  trn.TRANSACTION_QUANTITY,
  trn.TRANSACTION_UOM,
  trn.TRANSACTION_DATE,
  trn.TRANSFER_TRANSACTION_ID,
  cst.ACCTG_COST,
  cst.period_id,
  DECODE(trn.transaction_type_id, 52,’52=stage trans’, 33,’33=so issue’, trn.transaction_type_id) trans_type,
  (SELECT TO_CHAR(LIN.line_number)
    || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
    || TO_CHAR(LIN.shipment_number))
    || DECODE(LIN.option_number, NULL, NULL, ‘.’
    || TO_CHAR(LIN.option_number))
    || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
    || ‘.’
    ||TO_CHAR(LIN.component_number))
    || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
    || DECODE(LIN.option_number, NULL, ‘.’, NULL )
    || ‘.’
    || TO_CHAR(LIN.service_number))
  FROM oe_order_lines_all lin
  WHERE trn.trx_source_line_id = lin.line_id
  ) line,
  trn.trx_source_line_id line_id,
  trn.primary_quantity prm_q,
  trn.secondary_transaction_quantity sec_q,
  inv_convert.inv_um_convert( trn.inventory_item_id, 5, trn.primary_quantity, itm.primary_uom_code, trn.secondary_uom_code, NULL, NULL) trn_cal_sec_q,
  lot.primary_quantity lot_prm_q,
  lot.secondary_transaction_quantity lot_sec_q,
  inv_convert.inv_um_convert( trn.inventory_item_id, lot.lot_number, trn.organization_id, 5, NVL(lot.primary_quantity,0), itm.primary_uom_code, trn.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
  trn.secondary_uom_code uom2,
  lot.lot_number lot_num,
  trn.subinventory_code from_sub,
  trn.locator_id from_loc_id,
  trn.pick_slip_number pick_slip,
  trn.transfer_subinventory to_sub,
  trn.transfer_locator_id to_loc_id,
  trn.organization_id orgn_id,
  trn.transaction_source_id
FROM mtl_material_transactions trn,
  mtl_transaction_lot_numbers lot,
  mtl_system_items_b itm,
  gl_item_cst cst,
  gmf_fiscal_policies gfp,
  gmf_period_statuses gps,
  gmf_organization_definitions god,
  cm_mthd_mst cmm
WHERE trn.trx_source_line_id IN
  (SELECT DISTINCT line_id
  FROM oe_order_lines_all lin1
  WHERE lin1.header_id =
    &hid
  AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin1.line_id, lin1.top_model_line_id, lin1.ato_line_id, lin1.link_to_line_id, lin1.reference_line_id, lin1.service_reference_line_id)
  )
AND trn.organization_id            = itm.organization_id
AND trn.inventory_item_id          = itm.inventory_item_id
AND lot.transaction_id (+)         = trn.transaction_id
AND trn.transaction_source_type_id = 2
AND gfp.cost_type_id               = cmm.cost_type_id
AND cst.period_id                  = gps.period_id
AND gps.legal_entity_id            = gfp.legal_entity_id
AND gfp.legal_entity_id            = god.legal_entity_id
AND trn.organization_id            = god.organization_id
AND trn.transaction_date          >= gps.start_date
AND trn.transaction_date          <= gps.end_date
ORDER BY trn.trx_source_line_id,
  trn.transaction_id;
prompt mtl_transactions_interface (mti)
prompt
SELECT
  /*MOAC_SQL_NO_CHANGES*/
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  det.delivery_detail_id del_detail_id,
  itm.segment1 item,
  tmp.primary_quantity prm_q,
  secondary_transaction_quantity sec_q,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.source_line_id   = lin.line_id
AND lin.line_category_code = ‘order’
AND lin.ship_from_org_id   = itm.organization_id(+)
AND lin.inventory_item_id  = itm.inventory_item_id(+)
AND det.source_line_id     = lin.line_id
AND lin.header_id          =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id)
UNION ALL
SELECT TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  det.delivery_detail_id del_detail_id,
  itm.segment1 item,
  tmp.primary_quantity prm_q,
  tmp.secondary_transaction_quantity sec_q,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.trx_source_line_id = lin.line_id
AND lin.line_category_code   = ‘return’
AND lin.ship_from_org_id     = itm.organization_id(+)
AND lin.inventory_item_id    = itm.inventory_item_id(+)
AND det.source_line_id       = lin.line_id
AND lin.header_id            =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)                         IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);

Trip details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==============================================
SELECT wdd.source_header_id,
  wdd.source_line_id,
  assign.delivery_assignment_id,
  assign.delivery_id,
  assign.delivery_detail_id,
  deli.name deli_name,
  deli.status_code deli_status,
  legs.delivery_leg_id,
  legs.pick_up_stop_id,
  — legs.drop_off_stop_id,
  DECODE(stops.pending_interface_flag, NULL, ‘Not Pending’, ‘Y’, ‘Pending’, stops.pending_interface_flag) pif,
  stops.status_code trip_stop_status,
  stops.actual_departure_date,
  trips.name trip_name,
  trips.trip_id,
  trips.status_code trip_status
FROM wsh_delivery_details wdd,
  wsh_delivery_assignments assign,
  wsh_new_deliveries deli,
  wsh_delivery_legs legs,
  wsh_trip_stops stops,
  wsh_trips trips
WHERE wdd.source_header_id =
  &hid
AND wdd.source_line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND wdd.delivery_detail_id =assign.delivery_detail_id
AND assign.delivery_id     = deli.delivery_id
AND deli.delivery_id       =legs.delivery_id
AND legs.pick_up_stop_id   = stops.stop_id
AND stops.trip_id          = trips.trip_id
ORDER BY assign.delivery_assignment_id,
  assign.delivery_id,
  assign.delivery_detail_id;
  
Move ORDER details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid
 ===================================================
SELECT DISTINCT trl.line_id mo_line_id,
  trh.request_number mo_number,
  — trl.header_id mv_hdr_id,
  trl.line_number mv_line_num,
  DECODE(trl.line_status, 1, ‘1=Incomplete’, 2, ‘2=Pend Aprvl’, 3, ‘3=Approved’, 4, ‘4=Not Apprvd’, 5, ‘5=Closed’, 6, ‘6=Canceled’, 7, ‘7=Pre Apprvd’, 8, ‘8=Part Aprvd’) mv_line_stat,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  trl.txn_source_line_id ord_line_id,
  det.delivery_detail_id ,
  itm.segment1 item,
  trl.quantity qty,
  trl.primary_quantity prm_q,
  trl.quantity_delivered dlv_q,
  trl.quantity_detailed dtl_q,
  trl.secondary_quantity sec_q,
  trl.secondary_quantity_detailed sec_dtl_q,
  trl.secondary_quantity_delivered sec_dlv_q,
  trl.move_order_type_name move_type_name,
  DECODE(trl.transaction_source_type_id,2,’Sales Order’,trl.transaction_source_type_id) trns_src_type,
  trl.transaction_type_name trns_type_name,
  trl.organization_id orgn_id,
  trl.from_subinventory_code from_sub,
  trl.from_locator_id from_loc_id,
  trl.to_subinventory_code to_sub,
  trl.to_locator_id to_loc_id,
  trl.lot_number lot_num,
  trl.transaction_header_id trns_head_id
FROM mtl_txn_request_lines_v trl,
  mtl_txn_request_headers trh,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE trl.line_id = det.move_order_line_id
  –trl.txn_source_line_id = lin.line_id
AND lin.ship_from_org_id  = itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND det.source_line_id    = lin.line_id
AND trl.header_id         = trh.header_id
AND lin.header_id         =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)              IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);

Extract Header details FOR sales ORDER NUMBER: &ord_num 
========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt
WHERE geh.transaction_id           = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
Extract Lines FOR sales ORDER NUMBER: &ord_num 
==================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id                = geh.header_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
SLA Events FOR sales ORDER NUMBER: &ord_num 
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_events xe
WHERE xe.event_id                  = geh.event_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
SLA Headers FOR sales ORDER NUMBER: &ord_num 
==================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 2
  AND mmt.trx_source_line_id        IN
    (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
    )
  );

SLA Lines FOR sales ORDER NUMBER: &ord_num 
================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 2
  AND mmt.trx_source_line_id        IN
    (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
    )
  );
  
SLA Distributions FOR sales ORDER NUMBER: &ord_num 
=====================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt,
  xla.xla_distribution_links dl
WHERE dl.event_id                  = geh.event_id
AND dl.application_id              = 555
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
Item Cost details 
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE mmt.transaction_source_type_id = 2
    AND god.organization_id              = mmt.organization_id
    AND gfp.legal_entity_id              = god.legal_entity_id
    AND mthd.cost_type_id                = gfp.cost_type_id
    AND gps.legal_entity_id              = gfp.legal_entity_id
    AND gps.cost_type_id                 = gfp.cost_type_id
    AND mmt.transaction_date            >= gps.start_date
    AND mmt.transaction_date            <= gps.end_date
    AND mmt.trx_source_line_id          IN
      (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
      )
    )
  );
  
COGS RELATED:
Run Following Queries BY entering related SO# numbers AND RMA# Numbers FOR which COGS/DCOGS being analized **/ 
OE Headers 
==========
SELECT * FROM oe_order_headers_all WHERE order_number IN ( ‘&so_number’);

OE lines 
=====
SELECT oeh.order_number,
  oeh.header_id,
  oeh.order_type_id,
  oel.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel
WHERE oel.header_id   = oeh.header_id
AND oeh.order_number IN ( ‘&so_number’);

MMT rows              
=======
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  mmt.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt
WHERE mmt.trx_source_line_id        = oel.line_id
AND oel.header_id                   = oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number               IN ( ‘&so_number’);

COGS events                         
===========
SELECT *
FROM cst_cogs_events
WHERE cogs_om_line_id IN
  (SELECT oel.line_id
  FROM oe_order_headers_all oeh,
    oe_order_lines_all oel
  WHERE oel.header_id   = oeh.header_id
  AND oeh.order_number IN ( ‘&so_number’)
  ) ;

Extract Headers 
===========
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  geh.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt,
  gmf_xla_extract_headers geh
WHERE mmt.transaction_id            = geh.transaction_id
AND mmt.trx_source_line_id          = oel.line_id
AND oel.header_id                   = oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number               IN ( ‘&so_number’);

Extract Lines                       
=========
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  gel.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt,
  gmf_xla_extract_headers geh,
  gmf_xla_extract_lines gel
WHERE gel.header_id                      = geh.header_id
AND geh.transaction_id                   = mmt.transaction_id
AND mmt.trx_source_line_id               = oel.line_id
AND oel.header_id                        = oeh.header_id
AND mmt.transaction_source_type_id      IN (2, 12)
AND oeh.order_number                    IN ( ‘&so_number’);

INVENTORY RELATED: 
Material transactions 
==============
SELECT *
FROM mtl_material_transactions
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid
AND transaction_id =
  &&transid;
  
Lot NUMBER transactions 
=================
SELECT *
FROM mtl_transaction_lot_numbers
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid
AND transaction_id =
  &&transid;
  
MTL Parameter organization details Organization id: &&orgid 
==========================================
SELECT * FROM mtl_parameters WHERE organization_id = &&orgid;

Item details FOR Organization id: &&orgid AND item id: &&invitemid 
==============================================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid;
  
Extract Header details FOR Transaction id: &&transid 
====================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘INVENTORY’
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Extract Lines details FOR Transaction id: &&transid 
===================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE geh.entity_code   = ‘INVENTORY’
AND gel.header_id       = geh.header_id
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Sla Events FOR Transaction id: &&transid 
============================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE geh.entity_code   = ‘INVENTORY’
AND xe.event_id         = geh.event_id
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Item Component Cost details FOR Transaction id: &&transid 
==========================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE god.organization_id = mmt.organization_id
    AND mmt.transaction_id    =
      &&transid
    AND mmt.organization_id =
      &&orgid
    AND mmt.inventory_item_id =
      &&invitemid
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
  
PURCHASING RELATED: 
PO Header details FOR purchase ORDER id: &&poheaderid 
=======================================
SELECT * FROM po.po_headers_all WHERE po_header_id = &poheaderid;

PO Line details FOR purchase ORDER id: &&poheaderid 
=====================================
SELECT * FROM po_lines_all WHERE po_header_id = &poheaderid;

PO Line location details FOR purchase ORDER id: &&poheaderid 
===========================================
SELECT * FROM po_line_locations WHERE po_header_id = &poheaderid;

PO distribution details FOR purchase ORDER id: &&poheaderid 
==========================================
SELECT * FROM po_distributions WHERE po_header_id = &poheaderid;

RCV Transactions FOR purchase ORDER id: &&poheaderid 
=======================================
SELECT * FROM po.rcv_transactions WHERE po_header_id = &&poheaderid;

RCV accounting txns FOR purchase ORDER id: &&poheaderid 
=========================================
SELECT * FROM gmf.gmf_rcv_accounting_txns WHERE po_header_id = &&poheaderid;

MMT txns FOR purchase ORDER id: &&poheaderid 
==================================
SELECT *
FROM inv.mtl_material_transactions
WHERE transaction_source_type_id = 1
AND rcv_transaction_id          IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Opm financials data 
===============
Extract Header details FOR purchase ORDER id: &&poheaderid: 
==========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt
WHERE geh.transaction_id           = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt
WHERE geh.transaction_id    = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Extract Lines details FOR purchase ORDER id: &&poheaderid 
========================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id                = geh.header_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt ,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id         = geh.header_id
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Sla Events FOR purchase ORDER id: &&poheaderid 
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_events xe
WHERE xe.event_id                  = geh.event_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt,
  xla.xla_events xe
WHERE xe.event_id           = geh.event_id
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Sla Header details FOR purchase ORDER id: &&poheaderid 
======================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 1
  AND mmt.rcv_transaction_id        IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  )
UNION ALL
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    gmf.gmf_rcv_accounting_txns mmt
  WHERE geh.transaction_id    = mmt.rcv_transaction_id
  AND mmt.rcv_transaction_id IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  );
  
Sla Lines details FOR purchase ORDER id: &&poheaderid 
======================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 1
  AND mmt.rcv_transaction_id        IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  )
UNION ALL
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    gmf.gmf_rcv_accounting_txns mmt
  WHERE geh.transaction_id    = mmt.rcv_transaction_id
  AND mmt.rcv_transaction_id IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  );
  
Sla Distributions FOR purchase ORDER id: &&poheaderid 
======================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_distribution_links dl
WHERE dl.event_id                  = geh.event_id
AND dl.application_id              = 555
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt ,
  xla.xla_distribution_links dl
WHERE dl.event_id           = geh.event_id
AND dl.application_id       = 555
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Item Cost details 
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt,
      rcv_transactions rct
    WHERE mmt.transaction_source_type_id = 1
    AND god.organization_id              = mmt.organization_id
    AND mmt.rcv_transaction_id           = rct.transaction_id
    AND rct.po_header_id                 =
      &&poheaderid
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
  

PRODUCTION MANAGEMENT: 
Batch Header details FOR Batch id: &&batch_id 
================================
SELECT * FROM gme_batch_header WHERE batch_id=’&&batch_id’;

Recipe details FOR Batch id: &&batch_id 
============================
SELECT r.*
FROM gme_batch_header b ,
  gmd_recipes r ,
  gmd_recipe_validity_rules vr
WHERE b.batch_id=
  &&batch_id
AND b.recipe_validity_rule_id                          =vr.recipe_validity_rule_id
AND vr.recipe_id                                       =r.recipe_id;

Recipe Validity Rules details FOR Batch id: &&batch_id 
======================================
SELECT vr.*
FROM gme_batch_header b ,
  gmd_recipes r ,
  gmd_recipe_validity_rules vr
WHERE b.batch_id=
  &&batch_id
AND b.recipe_validity_rule_id                   =vr.recipe_validity_rule_id
AND vr.recipe_id                                =r.recipe_id;

Batch Material Details FOR Batch id: &&batch_id 
=================================
SELECT d.* FROM gme_material_details d WHERE d.batch_id = &&batch_id;

Inventory Transactions details FOR Batch id: &&batch_id 
=======================================
SELECT t.*
FROM mtl_material_transactions t
WHERE t.transaction_source_id =
  &&batch_id
AND t.transaction_source_type_id                              = 5;

Batch Material Transaction Pairs FOR Batch NUMBER: &&batch_id 
=============================================
SELECT * FROM gme_transaction_pairs p WHERE p.batch_id = &&batch_id;

Batch RESOURCE Transactions details FOR Batch NUMBER: &&batch_id 
===============================================
SELECT t.*
FROM gme_batch_header h,
  gme_resource_txns t
WHERE h.batch_id =
  &&batch_id
AND h.batch_id                        = t.doc_id
AND t.doc_type                        = ‘PROD’;

Yield Layers FOR Batch id: &&batch_id 
===========================
SELECT *
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
  (SELECT DISTINCT t.organization_id,
    t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_source_id =
    &&batch_id
  AND t.transaction_source_type_id = 5
  );
  
Material Consumption Layers FOR Batch id: &&batch_id 
=======================================
SELECT *
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
  (SELECT DISTINCT t.organization_id,
    t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_source_id =
    &&batch_id
  AND t.transaction_source_type_id = 5
  );
  
RESOURCE Consumption Layers FOR Batch id: &&batch_id 
========================================
SELECT *
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
  (SELECT t.poc_trans_id
  FROM gme_resource_txns t
  WHERE t.doc_id =
    &&batch_id
  AND t.doc_type = ‘PROD’
  );
  
VIB Details FOR Batch id: &&batch_id 
==========================
SELECT *
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
  (SELECT br.requirement_id
  FROM gmf_batch_requirements br
  WHERE br.batch_id =
    &&batch_id
  );
  
Batch Requirement Details FOR Batch id: &&batch_id 
=====================================
SELECT * FROM gmf_batch_requirements br WHERE br.batch_id = &&batch_id;

Layer cost details FOR Batch id: &&batch_id 
==============================
SELECT *
FROM gmf_layer_cost_details c
WHERE c.layer_id IN
  (SELECT il.layer_id
  FROM gme_batch_header h,
    mtl_material_transactions t,
    gmf_incoming_material_layers il
  WHERE h.batch_id =
    &&batch_id
  AND h.batch_id                   = t.transaction_source_id
  AND t.transaction_source_type_id = 5
  AND il.mmt_transaction_id        = t.transaction_id
  AND il.mmt_organization_id       = t.organization_id
  );
  
Extract Header details FOR Batch id: &&batch_id 
=================================
SELECT *
FROM gmf_xla_extract_headers
WHERE entity_code      = ‘PRODUCTION’
AND source_document_id =
  &l_batch_id;
  
Extract Lines details FOR Batch id: &&batch_id 
=================================
SELECT *
FROM gmf_xla_extract_lines
WHERE header_id IN
  (SELECT header_id
  FROM gmf_xla_extract_headers
  WHERE entity_code      = ‘PRODUCTION’
  AND source_document_id =
    &l_batch_id
  );
  
Sla Events FOR Batch id: &&batch_id 
==========================
SELECT xe.*
FROM gmf_xla_extract_headers eh,
  xla_events xe
WHERE eh.event_id         = xe.event_id
AND xe.application_id     =555
AND eh.entity_code        = ‘PRODUCTION’
AND eh.source_document_id =
  &l_batch_id;
  
Item Component Cost details FOR Batch id: &&batch_id 
=======================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE mmt.transaction_source_type_id = 5
    AND god.organization_id              = mmt.organization_id
    AND mmt.transaction_source_id        =
      &&batch_id
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply