Opm accounting preprocesser(gmfxupd) debugging scripts

Opm Accounting preprocesser debugging scripts

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

OPM Cost Update Fails With ORA-1 On GL_ITEM_CST_U1

OPM Cost Update concurrent program (GMCCUPD) is failing with:

ORA-00001: unique constraint (GMF.GL_ITEM_CST_U1) violated
Failed while inserting rows into gl_item_cst table.

Cause

When you run a 'Final' Cost Update, this selects rows from CM_CMPT_DTL
where ROLLOVER_IND is zero, sets this value to '1', and inserts rows
into GL_ITEM_CST with FINAL_FLAG also set to '1'.

In this case there were rows in CM_CMPT_DTL for the relevant Calendar
and Period where ROLLOVER_IND was still set to zero, but where the
corresponding GL_ITEM_CST rows had FINAL_FLAG = '1'.

When Cost Update tried to re-process the CM_CMPT_DTL rows, it needed to
insert another 'FINAL_FLAG = 1' row into GL_ITEM_CST, and this was the
duplicate.

This situation should not arise - I can only think that a 'Final' Cost
Update was run in error, then someone went in via a back-end tool and
updated CM_CMPT_DTL.ROLLOVER_IND to zero without realising that they
would also need to do something with GL_ITEM_CST (and GL_ITEM_DTL).

OPM Cost Update Fails With ORA-1 On GL_ITEM_CST_U1

Solution

If this data situation arises, then it will be necessary to update the
OPM Financial data via SQL*Plus or similar.

Please engage the assistance of Oracle Applications Support before
attempting this.

The following script will show if you have any occurrences of this
problem:

select distinct c.rollover_ind, g.final_flag
from cm_cmpt_dtl c, gl_item_cst g, gl_item_dtl d
where g.itemcost_id = d.itemcost_id
and d.cost_cmpntcls_id = c.cost_cmpntcls_id
and c.item_id = g.item_id
and c.whse_code = g.whse_code
and c.calendar_code = g.calendar_code
and c.period_code = g.period_code
and c.cost_mthd_code = g.cost_mthd_code;

Note that the only problem combination is zero in 'ROLLOVER_IND'
and '1' in 'FINAL_FLAG'.

Inventory reports

Inventory Reports

This chapter covers the following topics:

• Inactive Items Report
• Item Reservations Report
• Lot Transaction Register
• Genealogy Report
• Material Account Distribution Detail
• Move Order Pick Slip Report
• Material Account Distribution Summary
• Serial Number Transaction Register
• Transaction Historical Summary Report
• Transaction Register
• Transaction Source Type Summary
• Shortages Summary Report
• Shortage Parameter Report
• Global Transaction Purge
• Transaction Purge
• Create Deferred Logical Transactions
• Open Period Status Control
• Close Period Status Control
• Expired Lots Report
• Lot Inventory Report
• Material Status Definition Report
• Lot Master Report
• Activate On-hand Level Material Status Tracking
• Grade Change History
• Item Categories Report
• Item Cross-References Listing
• Item Definition Detail
• Item Definition Summary
• Item Demand History Report
• Item Relationships Listing
• Item Statuses Report
• Item-Subinventory Report
• Item Template Listing
• Serial Number Detail
• Customer Item Commodity Codes Listing
• Customer Item Cross References Report
• Customer Items Report
• Item Organization Assignment Report
• Forecast Rule Listing
• Item Replenishment Count Report
• PAR Replenishment Count Worksheet
• Min-Max Planning Report
• ABC Assignments Report
• ABC Descending Value Report
• Cycle Count Entries and Adjustments Report
• Cycle Count Hit/Miss Analysis
• Cycle Count Listing
• Physical Inventory Item Accuracy Report
• Physical Inventory Adjustments Report
• Physical Inventory Counts Report
• Cycle Count Open Requests Listing
• Cycle Count Unscheduled Items Report
• Cycle Counts Pending Approval Report
• Physical Inventory Tags
• Physical Inventory Tag Listing
• Physical Inventory Missing Tag Listing
• Physical Inventory Trend Report
• Physical Inventory Summary Report
• Print Cycle Count Entries Open Interface Data
• Purge Cycle Count Entries Open Interface Data
• Import Cycle Count Entries from Open Interface
• Cycle Count Schedule Requests Report
• Item Quantities Summary Report
• Locator Quantities Report
• Subinventory Quantities Report
• VMI Onhand by Supplier Report
• Vendor Lot Trace Report
• Account Alias Listing
• Freight Carrier Listing
• Inter-organization Shipping Information Listing
• Locator Listing
• Organization Parameters Listing
• Planner Listing
• Status Attributes Listing
• Subinventory Listing
• Units of Measure Listing
• Intercompany AR Invoices Report
• Intercompany AP Invoices Report
• Reorder Point Report
• Organization Hierarchy Exceptions Report
• Inventory Transaction by Cost Center and Account Report
• Inventory Charges and Product Usage Report
• Consumption Advice Diagnostics
• Movement Statistics Exception Report
• Movement Statistics Reset Status Report
• Movement Statistics Processor
• Movement Statistics Report
• Inventory Packing Slip
• Warehouse Inventory Adjustment Advice
• Warehouse Inventory (Onhand) Report Message
• Country-Specific Reports