OPM Query to identify In Transit Transactions
Introduction:
This blog shares the query to identify In Transit Transactions.
Cause of the Issue:
This blog shares the query to identify In Transit Transactions.
This would help in simplifying OPM Period Close
How do we solve:
SELECT
ood.organization_code,
geh.transaction_Date,
msib.segment1 item_number,
geh.transaction_quantity,
geh.transaction_Value,
ooh.order_number sales_order_Number,
ool.line_number order_line_number,
ooh.ordered_Date order_date,
hp.party_name Customer,
hps.party_site_number Customer_Site,
xep.name LE_Name,
hou.name OU_Name
FROM
oe_order_headers_all ooh,
oe_order_lines_all ool,
oe_order_headers_all oohr,
oe_order_lines_all oolr,
gmf_xla_extract_headers geh,
gmf_xla_extract_lines gel,
mtl_system_items_b msib,
org_organization_definitions ood,
xle_entity_profiles xep,
hr_operating_units hou,
hz_cust_Accounts_All hcaa,
hz_parties hp,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps
WHERE
ooh.header_id = ool.header_id
AND ool.reference_header_id = oohr.header_id (+)
AND ool.reference_line_id = oolr.line_id (+)
AND gel.header_id = geh.header_id
AND ool.line_id = geh.source_line_id
and geh.inventory_item_id=msib.inventory_item_id
and ood.organization_id=geh.organization_id
and ood.operating_unit=hou.organization_id
and ood.legal_entity=xep.legal_entity_id
and hcaa.cust_Account_id=ooh.sold_to_org_id
and hcaa.party_id=hp.party_id
and hcasa.cust_acct_Site_id=hcsua.cust_Acct_Site_id
and hcaa.cust_Account_id=hcasa.cust_account_id
and hcsua.site_use_id=ooh.invoice_to_org_id
and hcasa.party_site_id=hps.party_site_id
and hp.party_id=hps.party_id
AND geh.entity_code = ‘ORDERMANAGEMENT’
AND geh.event_class_code IN (
‘RMA’,
‘SHIPPING’
)
AND gel.journal_line_type = ‘DCOGS’
— AND geh.organization_id = 204
— AND geh.transaction_date BETWEEN TO_DATE(‘&v_From_Date’, ‘DD-MON-YYYY’) AND to_date(to_char(‘&v_To_Date’ || ‘ 23:59:59’), ‘DD-MON-YYYY HH24:MI:SS’
GROUP BY
ood.organization_code,
xep.name,
hou.name,
geh.transaction_Date,
msib.segment1 ,
geh.transaction_quantity,
geh.transaction_Value,
ooh.order_number ,
ool.line_number ,
ooh.ordered_Date ,
hp.party_name ,
hps.party_site_number ,
ool.line_id,
ool.line_number,
oohr.order_number,
oolr.line_number,
gel.journal_line_type,
nvl(ool.reference_line_id, ool.line_id)
HAVING ( SUM(gel.trans_amount_raw) <> 0
OR SUM(gel.base_amount_raw) <> 0
OR SUM(gel.trans_amount) <> 0
OR SUM(gel.base_amount) <> 0 )
ORDER BY
ooh.order_number,
ool.line_id
