Opm Accounting Preprocessor debugging scripts in R12

Posted by Oracle ERP Apps Guide 

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
    )
  );

Categories: Apps Technical, Basic Concepts, OPM, Script, SQL

Related Post:

Apps Technical

• R12 Supplier Contact creation API
• How to Get GL Code Combination Description
• Important OPM Process Execution & Inventory Tables in R12
• How to Stop/Block Work Flow Mailer Send Mail from the Cloned Instance
• How to use CUSTOM.pll in Oracle Apps

Basic Concepts

• Important OPM Process Execution & Inventory Tables in R12
• How to Stop/Block Work Flow Mailer Send Mail from the Cloned Instance
• Oracle Back to Back Order in Order Management
• What is Batch Close Variance in OPM?
• What are all Oracle ASCP Plan Types?

OPM Quality Basic Queries

select test_class Class,test_class_desc Description from GMD_TEST_CLASSES

select * from gmd_test_classes------------------------CLASSSES AVAILABLE

select * from  GMD_TEST_METHODS---------TEST METHODS\

SELECT TEST_METHOD_CODE METHOD,TEST_METHOD_DESC DESCRIPTION , TEST_QTY QTY, TEST_QTY_UOM UOM, DISPLAY_PRECISION Stored_Precision,
Days, hours,Minutes, seconds,TEST_REPLICATE Replicate from GMD_TEST_METHODS------------------------------------Test_Methods

select * from GMD_UNITS ---------------------------------Test_Units
select QCUNIT_CODE Units, QCUNIT_DESC Description from gmd_units-----------------Test Units

select * from MTL_ACTIONS --------------------action codes
select ACTION_CODE Action , DESCRIPTION from mtl_actions-----action codes


select ORGANIZATION_ID Organization, SAMPLE_LAST_ASSIGNED Sample_Last_Assigned,SS_LAST_ASSIGNED SS_Last_Assigned
from gmd_quality_config----------------------------------Process Quality Parameters
select * from gmd_quality_config --------------------------------Process Quality Parameters

select  SAMPLING_PLAN_NAME Plan_Name, SAMPLING_PLAN_DESC Description,SAMPLE_CNT_REQ Count,
SAMPLE_QTY Quantity, SAMPLE_QTY_UOM UOM,
Decode( Frequency_type , 'F' , 'Fixed Number') Frequency, FREQUENCY_CNT Per,
Decode(FREQUENCY_PER , 'FS','Batch step',
                       'FR', 'Receipt',
                       'FB', 'Batch') Sample_UOM,
RESERVE_CNT_REQ Res_Count, RESERVE_QTY Res_Qty,
ARCHIVE_CNT_REQ Arch_Count,ARCHIVE_QTY Arch_Qty
from gmd_sampling_plans----------------------------------------------------sampling Plan
select * from gmd_sampling_plans------------sampling Plan




select * from GMD_QC_TESTS where test_code='% BTM'--------------------TEST DATA

select TE.TEST_CODE Test, TE.TEST_DESC Description,TE.TEST_CLASS Class,
ME.TEST_METHOD_CODE Method,ME.TEST_METHOD_DESC Method_DESCRIPTION,
Decode (TE.TEST_TYPE,'L', 'Numeric range With Display Text',
                   'E', 'Expression',
                   'N','Numeric Range',
                   'T','Text Range',
                   'U','Non-Validated',
                   'V','List of Test Values') Data_Type,
TE.TEST_UNIT Unit,
Decode (TE.PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','high') Priority,
TE.MIN_VALUE_NUM Range_from, TE.MAX_VALUE_NUM Range_To,
TE.DISPLAY_PRECISION Stored_Precision,TE.REPORT_PRECISION Report_Precision,
TE.EXPRESSION,
tv.MIN_NUM Min_Value,tv.max_num Max_Value,tv.DISPLAY_LABEL_NUMERIC_RANGE Display
from GMD_QC_TESTS TE,
GMD_TEST_METHODS ME,
gmd_qc_test_values tv
where te.test_method_id=me.test_method_id
and te.test_id=tv.test_id------------------------------------------------Test Data

select  * from gmd_qc_test_values---------------------------Values in Test Data Samples


select * from gmd_specifications where spec_name ='726 Sell / 200 Kg'----------------Process Quality Specifiction

select gmsp.Spec_name Spec,gmsp.SPEC_DESC Description,gmsp.SPEC_VERS Version,gms.description Status,
Decode (gmsp.SPEC_TYPE,'I','Item',
                  'M','Monitoring') Spec_Type,
gmsp.OVERLAY_IND Overlay,gmsp.base_spec_id Base_Spec,
gmsp.INVENTORY_ITEM_ID Item_ID,
kfiv.CONCATENATED_SEGMENTS Item_code,kfiv.description Item_desc,
gmsp.GRADE_CODE Grade,gmsp.OWNER_ORGANIZATION_ID Owner_Organization,fnu.user_name,
gst.seq Target_Seq,
(select gct.TEST_CLASS from GMD_QC_TESTS gct where gct.test_id=gst.test_id) Target_Class,
(select TEST_METHOD_CODE from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method,
(select TEST_METHOD_DESC from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method_Desc,
gst.MIN_VALUE_NUM Target_Minimum,gst.MAX_VALUE_NUM Target_Maximum,gst.TEST_QTY Target_Quantity,
gst.TEST_QTY_UOM Target_UOM, gst.TEST_REPLICATE Target_Replicate,
Decode(gst.TEST_PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','High') Target_Priority,
gst.OPTIONAL_IND Target_optional,
gst.DISPLAY_PRECISION Formating_Stored_Precision,
gst.REPORT_PRECISION Report_Stored_Precision
from gmd_specifications gmsp,
gmd_status gms,
mtl_system_items_kfv kfiv,
gmd_spec_tests gst,
fnd_user fnu
where gmsp.spec_status=gms.status_code
and gmsp.spec_id=gst.spec_id
--and gst.test_id=gct.test_id
and gmsp.INVENTORY_ITEM_ID=kfiv.INVENTORY_ITEM_ID
and gmsp.OWNER_ORGANIZATION_ID=kfiv.ORGANIZATION_ID
and fnu.user_id=gmsp.owner_id---------------------------------------------Product Quality specifications



select gmsp.Spec_name Spec,gmsp.SPEC_DESC Description,gmsp.SPEC_VERS Version,gms.description Status,
Decode (gmsp.SPEC_TYPE,'I','Item',
                  'M','Monitoring') Spec_Type,
gmsp.OVERLAY_IND Overlay,gmsp.base_spec_id Base_Spec,
gmsp.INVENTORY_ITEM_ID Item_ID,
kfiv.CONCATENATED_SEGMENTS Item_code,kfiv.description Item_desc,
gmsp.GRADE_CODE Grade,gmsp.OWNER_ORGANIZATION_ID Owner_Organization,fnu.user_name,
gst.seq Target_Seq,
(select gct.TEST_CLASS from GMD_QC_TESTS gct where gct.test_id=gst.test_id) Target_Class,
(select TEST_METHOD_CODE from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method,
(select TEST_METHOD_DESC from GMD_TEST_METHODS gtm where gtm.TEST_METHOD_ID=gst.TEST_METHOD_ID) Target_Test_Method_Desc,
gst.MIN_VALUE_NUM Target_Minimum,gst.MAX_VALUE_NUM Target_Maximum,gst.TEST_QTY Target_Quantity,
gst.TEST_QTY_UOM Target_UOM, gst.TEST_REPLICATE Target_Replicate,
Decode(gst.TEST_PRIORITY,'1L','Low',
                 '5N','Normal',
                 '8H','High') Target_Priority,
gst.OPTIONAL_IND Target_optional,
gst.DISPLAY_PRECISION Formating_Stored_Precision,
gst.REPORT_PRECISION Report_Stored_Precision,
ver.ORGANIZATION_CODE VALIDITY_ORG_CODE ,
Decode (ver.SPEC_TYPE,'I','Inventory',
                  'W','WIP',
                  'C','Customer',
                  'S','Supplier') VALIDITY_Spec_Type,
Decode (ver.DELETE_MARK,'0','No',
                  '1','Yes') Validity_Deleted,
ver.SPEC_VR_STATUS_DESC Validity_Spec_rule,
ver.start_date Validity_start_date,
ver.end_date Validity_end_date,
ver.PARENT_LOT_NUMBER Validity_Parent_Lot,
ver.LOT_NUMBER Validity_Lot_Number,
ver.SUBINVENTORY Validity_SubInventory,
ver.IN_SPEC_LOT_STATUS_CODE Validity_LOT_Status,
ver.OUT_OF_SPEC_LOT_STATUS_CODE Validity_out_of_spec_Lot
from gmd_specifications gmsp,
gmd_status gms,
mtl_system_items_kfv kfiv,
gmd_spec_tests gst,
GMD_ALL_SPEC_VRS_VL ver,
fnd_user fnu
where gmsp.spec_status=gms.status_code
and gmsp.spec_id=gst.spec_id
and ver.spec_id=gmsp.spec_id
and ver.spec_id=gst.spec_id
and gmsp.INVENTORY_ITEM_ID=kfiv.INVENTORY_ITEM_ID
and gmsp.OWNER_ORGANIZATION_ID=kfiv.ORGANIZATION_ID
and fnu.user_id=gmsp.owner_id---------------------------------------------Product Quality specifications With Validity Rules

Steps involved in Implementation

HI All,

I would like to consolidate a list of best practices and common mistakes done in implmentation projects.
I think you people might have come across different best practices and some amazing workarounds.
I request all of you to provide me your valuble inputs to put a road map for a ideal implementation.
From now onwards I will be sending different best practices and Process so that we can evalutate the pros and cons of each.

First of all let me list different techonologies and tools required at different stages of implementation.
From now onwards we can evaluate each technology and tool for different types of situations.

Data loading stage:
1.sql loader
2.GUI tools like DATA loader
3.Oracle External Tables(from 9i)
4.WebADI
User interface Development:
1.Forms6i
2.OAF(Jdeveloper) Reporting Tools:
1.Reports Builder
2.XML Publisher
3.PL/SQL Reports
4.Oracle Discoverer
Business logic implementation:
1.Work Flow
2.AME(Approval's Management) Outbound interfaces:
1.EDI
2.XML Gate way
3.Custom made outbound interfaces using PL/Sql
Inbound Interfaces:
1.Open Interfaces
2.API’s
3.EDI
4.XML Gateway(Not Sure)
Others:
1.Oracle Alerts
2.OAF Personalization
3.Forms Personalization
Tools:
1.TOAD
2.SQL Client
3.PL/SQL Developer

Code Formatting Tools:
1.Formatter Plus
2.PL/SQL Developer Formatter Configuration Tools:
1.VSS
2.CVS


===========================================================

1. keep the source of all Customized objects(Tables,Packages,Procedures ..) in the PLS folder in the custom top.
It is easy to say but making people to follow it is very difficult(Even i don't :-) ).One way to make people to follow is by dropping objects whose lastest source is not present in the UNIX box.
we need to compare the last update date in the UNIX box and last_modified date in the all_objects...i witnessed this idea really working..This also removes the Cost factor of maintaining a VSS(VSS might have its own Advantages) also...

2.All Customized objects should be labeled.Example XXLGEOM2309.and the name of the object should be decided prior to the development
Lets say for example u have a Custom process involving a concurrent program,a form and a workflow.
For each object if u have one lablel right at the design phase all the documets can have a process design showing different components getting involved at differnt stages in the process.

3.A Repository of all the process and all the objects associated with it should be maintained.Thats what MD70 should contain...But most of the times it wont..they are always some missing objects.
we will realize the importance of it when the people starts leaving the company during implemenataion..

Functional:

1.Its always better to identify one process owner From the customer side for each process or Area.
Even though it is not with in our control the management should insist for it from the starting because it clears a lot of hurdles during implementation

2.All Functional people should have a fair idea of all the technologies used . they need not be experts but aleast they should understand the usages of it,its advantages&limitations .
(Guys with a little idea straightaway dont start urself giving techincal solutions..use the knowlege to understand what's happening..in future after enought experience u may....)
This will help u a lot when u r designing solutions...

Technical:
Let us discuss the reports..

1.The most common custom objects we see in any normal implentation(10-20% Customization) is Reports.
I personally feel the layout designing of the report should be the in the last stage of the implementation.Because the 90% of the Customer concern will be the data that he will get to analyze and 10% will be the Format it is presented.
so it is better to show the user the raw data every time using the tools like toad.Once user is happy with the data just have the sql and keep it aside.This gives us the flexibility of adding a column or removing it during the process development
Because adding a column or removing a column in tools like report builder takes a considerable amount of time.
It doesn't remove total rework but alteast reduces it considerably..

First let me list different types of reports from customer perspective..

1.Reports Presented to Authorities and top management--Look & Feel and the layout are important
2.Reports used for analyzation purpose and day-to-day operatons--like daily Shipping details report and Shipping lead time)
3.Reports Used for Record purpose--Keeping the data aside
4.MIS Reporting--Summary reports ,Report used to tally data from differnt applications in apps--Perfomance is pretty important as they run on huge data .

Let me list different reporting tools and some of their advantages


Report Builder:
Advantages:
Good for Complex layouts involving images and for Pre -Printed Document
Developer will have more control on the layout
Many Technical People knows very much how to use it
Disadvantages:
Report building time is Relatively High
Handling small layout changes is even more difficult
Data & Layout are combined into one unit--If I want to see same data in 3 different layouts we have to design three reports

PL/SQL Reports
Advantages
Easy to Build and maintain
Just PL/sql Knowledge is Enough
Should be used for analyzation reports and day-to day operation Reports
Disadvantage
Can handle only very simple layouts

XML Publisher
Advantages
Replacement for Report Builder
Can handle Complex layout also
Layout Design can be done in Microsoftword,XSLT --Compartively Easy
We can have one report with different layouts and layout can be selected at run time
I heard even we can get single report in multiple layout by running it only one time--Not sure as I have not used it
Disadvantages:
Only available in after 11.5.9(with a patch),11.5.10 onwards
Requires sound knowledge of microsoft word tables and word feautures
Many people still don’t have much idea on it..
Some time output goes wavierd on hand experience is required to find workarounds--(My own Experience)
Discoverer Reports
Advantages:
Multi Dimension analysis
Easy to build and use
Disadvantages:
Separate License
No formatting options at all..can be used only for analysis not for reporting 

=======================================================================

HI All,
Here i try to analyze different Data loading methods available...and some other debatable issues..
Best Practices: 
1.Always have the rightly balanced teams.
A typical ratio for medium complexity process would be(1:1:2 )(Functional:Technical Designer:Developers).
Techical designer should be a guy who understands both the importance of business and technical limitations.
And the rule should be , technical designer should always be involved at the Functional Discussions with the customer.
This always help for not committing things which are not technically feasible..(Nothing is impossible ..but the effort needed to make it happen is worth or not??)

2.The other big Question is how should I divide my project team???
Method A>Functional,Technical( With in technical people by technologies they are good at like ..reports ,Forms & OAF ...)
Method B> One Process one team.(Atleast team size should be more than 20 to implement this)

There are advantages & disadvantages of both the ways..I am part of implementations which are done both ways....Lets me list some of them...
Method A: 
Advantages: Less time is taken for development of components..(As technical people are doing what they are good at..)
Disadvantages:
1.I don't know whether I am correct or not in quoting this but the truth is you will find a clear divide between the two teams..And once things start going wrong people start blaming each other...I think every one might have experienced it already..
2.For technical people it is even bigger loss because you work on different components belonging to different process and never understand how your component fits in the overall solution. At the end of day it will be just a
piece of code...
3.Any change in the process during the course of development is very difficult to handle, as there will be inter dependencies among components

Method B: 
Advantages:
1.Every one will feel the ownership of the process and better team spirit.
2.Technical team will also have better understanding of the processes and will able to implement the changes faster (as In a development project change is unavoidable ) as they have all the components with them

Disadvantages: 
1.Development time might be a bit long as the technical people in u r team might not have expertise in all the technologies involved in the process

My views might be a bit biased as I am strong supporter for method B..

Technical:Coming to our today's technical discussion ...data loading..This will be one of the first steps(development) and the last step(before live) of a implementation..
The typical way of data loading is a three step process.
1.To load data from the legacy/Flat files to temporary tables.
2..Perform all the validations on the temporary table data and load it to the open interfaces or API's
3.Run the Open interface Concurrent programs/API's to load data to the standard tables.

First let me figure ways of data movement in oracle apps..
1.Open Interfaces-- used for live interfaces(every day activity) & one time loading
2.API's--Almost same as Open interfaces but are easy to handle.(validation & integrity logic is taken care by them)
3.Out Bound Interfaces--Required if we integrate oracle apps with third party or legacy systems
4.EDI--automation process..we will talk about them later

For loading data
SQL Loader:1.Used when data is available in flat files(tab delimited,Comma delimited)
2.Faster & Easier way of loading data
3.Can use sql functions to modify data

DB Links: if the legacy systems is on oracle data base the best thing is to get access to the data customer want to import through db links

Data loader tool : These are third party tools used for loading ,which automates the process of punching data.There are very much user friendly and not much technical expertise is required to work with them.
But the biggest disadvantage of these tools is they are slow.If you have huge data it is not advisable to use them(unless u r patient enough to see thru :-) )
The data loaded will be valid because it is as good as manual punching..

XML: Oracle Provides Apis to import XML data and to export data into XML.This should be most convenient way for data interaction as most of the other technology systems are able to parse XML data easily.
There are some limitations(can be easily overcome) also for these in oracle like while importing XML data into oracle tables oracle can't parse huge files

WEBADI: These are oracle provided templates for data loading into some standard interfaces.Easy to use. we can create the custom api's and use for data loading..i felt this is one of the best ways of loading data

UTL_FILE: it is PL/SQL way of loading data into oracle tables.This packages gives us the API to read and data into flat files.This method is very useful when the data to be loaded is less and more validations are required
before loading.One of the limitations of this pacakge it reads data in lines and the maximum length it can read is 1022 charecters..In writing data to files it can write only 32K in one shot..later we need to close the file
and reopen it again..

External Table:This concept concept comes from Oracle 9i onwards.This is one of the easiest way of loading data.Once you create the external table you can simple use the select stament to query the table.
On performance basis this is as good as Direct path loading of SQLLDR.(Technical People Give a try for this...)

Caution:Disable any indexes on the table before loading data..other wise it will slow down the process of loading data.

On summary for all conversions(one time data movement) use External Tables or SQL Loader
For interfaces use PL/sql,dblinks or XML
HI all i have not used SQL loader much..most of the time i have used External tables ,UTL_FIle and XML.So people who has much exposure can come up with any limitations or advantages of it..
Plz let me know if any thing is wrong or any other suggestions to make this better....