Introduction:
This report will provide deferred cost of the good sold.
Cause of the issue:
Every month needs to be run the lot of programs to process the transaction for OPM then if we have any mismatch data between the periods, it’s take lot of time to find the wrong data and needs to be run he programs one by one again so it’s take lot of time and needs to wait until complete the program to run next program.
How do we solve:
So, we develop a custom program for DCOGS report to check the data, so we can identify quickly and reduce lot of time.
SELECT ood.organization_code,
TO_CHAR (geh.transaction_date, ‘DD-MON-YYYY’) transaction_date,
msi.segment1 item, ool.ordered_quantity quantity,
ROUND (SUM (gel.trans_amount_raw), 12) AS VALUE,
ooh.order_number sales_oreder_num, ool.line_number order_line_num,
TO_CHAR (ooh.ordered_date, ‘DD-MON-YYYY’) order_date,
hp.party_name customer_name,hps.PARTY_SITE_NAME party_site_name, ool.line_id,
gel.journal_line_type, gle.legal_entity_name, gps.period_code,hps.PARTY_SITE_NUMBER
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,
org_organization_definitions ood,
mtl_system_items_b msi,
gmf_legal_entities_vw2 gle,
hz_cust_accounts hca,
hz_parties hp,
hz_party_sites hps,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua,
gl_item_cst cst,
gmf_period_statuses gps,
cm_mthd_mst mthd
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.entity_code = ‘ORDERMANAGEMENT’
AND geh.event_class_code IN (‘RMA’, ‘SHIPPING’)
AND gel.journal_line_type = ‘DCOGS’
AND geh.organization_id = ood.organization_id
AND msi.inventory_item_id = ool.inventory_item_id
AND msi.organization_id = geh.organization_id
AND gle.legal_entity_id = ood.legal_entity
AND hca.cust_account_id = ooh.sold_to_org_id
AND hp.party_id = hca.party_id
AND hp.party_id = hps.party_id
AND hps.party_site_id = hcasa.party_site_id
AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
AND hcsua.site_use_id = ooh.ship_to_org_id
AND cst.organization_id = msi.organization_id
AND cst.inventory_item_id = msi.inventory_item_id
AND cst.organization_id = ood.organization_id
AND cst.period_id = gps.period_id
AND cst.cost_type_id = mthd.cost_type_id
AND gle.legal_entity_name = NVL (:p_legal_entity, gle.legal_entity_name)
AND ( ood.organization_code = NVL (:p_org_1, ood.organization_code)
OR ood.organization_code = NVL (:p_org_2, ood.organization_code)
)
AND gps.period_code = :p_curr_period
AND mthd.cost_mthd_code = :p_cost_type
GROUP BY ooh.order_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),
geh.transaction_date,
ood.organization_code,
msi.segment1,
ool.ordered_quantity,
ooh.ordered_date,
hp.party_name,
hps.PARTY_SITE_NAME,
gle.legal_entity_name,
gps.period_code,
hps.PARTY_SITE_NUMBER
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
