OPM – Work in Process Report
Introduction:
This report will provide Work in Process items detail period wise.
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 WIP report to check the data, so we can identify quickly and reduce lot of time.
SELECT batch_no, actual_start_date, to_char(item) item, item_description,qty,accounted_dr,
accounted_cr,rate, batch_id,decode(accounted_dr,0,-accounted_cr,accounted_cr,0,accounted_dr) VALUE,organization_code,period_name
FROM (SELECT gbh.batch_no, gbh.actual_start_date actual_start_date,
msi.segment1 item, msi.description item_description,
transaction_quantity qty,
NVL (xel.accounted_dr, 0) accounted_dr,
NVL (xel.accounted_cr, 0) accounted_cr,
NVL
((SELECT a.acctg_cost
FROM gl_item_cst a
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 1 = 1
AND god.organization_id =
mmt.organization_id
AND mmt.transaction_source_id =
gbh.batch_id
AND mmt.inventory_item_id =
geh.inventory_item_id
AND mmt.organization_id =
geh.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)),
0
) rate,
gbh.batch_id,god.organization_code,xe.period_name
FROM gmf.gmf_xla_extract_headers geh,
gme_batch_header gbh,
mtl_system_items_b msi,
xla.xla_ae_headers xe,
xla.xla_ae_lines xel,
org_organization_definitions god, –gmf_organization_definitions god,
gmf_legal_entities_vw2 gle
WHERE 1 = 1
AND gbh.batch_id = geh.source_document_id
— AND geh.source_document_id = ‘754073’
AND event_class_code = ‘BATCH_MATERIAL’
AND geh.inventory_item_id = msi.inventory_item_id
AND geh.organization_id = msi.organization_id
AND xe.event_id = geh.event_id
AND xe.ae_header_id = xel.ae_header_id
AND xel.accounting_class_code = ‘WIP_VALUATION’
AND god.organization_id = geh.organization_id
AND batch_status = 2
AND gle.legal_entity_id = god.legal_entity
AND gle.legal_entity_name =NVL (:p_legal_entity, gle.legal_entity_name)
— AND god.organization_code in (:P_ORG_1,:P_ORG_2)
and (god.ORGANIZATION_CODE =nvl(:P_ORG_1,god.ORGANIZATION_CODE)
OR god.ORGANIZATION_CODE =nvl(:P_ORG_2,god.ORGANIZATION_CODE))
AND xe.period_name =:p_period
UNION ALL
SELECT gbh.batch_no, gbh.actual_start_date actual_start_date,
geh.resources item,
(SELECT resource_desc
FROM cr_rsrc_mst a
WHERE resources = geh.resources) item_description,
transaction_quantity qty,
NVL (xel.accounted_dr, 0) accounted_dr,
NVL (xel.accounted_cr, 0) accounted_cr,
(SELECT nominal_cost
FROM cm_rsrc_dtl
WHERE period_id IN (
SELECT period_id
FROM gmf_period_statuses
WHERE 1 = 1
AND period_code = xe.period_name
AND legal_entity_id = god.legal_entity)
AND (resources = geh.resources)
AND (organization_id = geh.organization_id)
AND (legal_entity_id = god.legal_entity)) rate,
gbh.batch_id,god.organization_code,xe.period_name
FROM gmf.gmf_xla_extract_headers geh,
gme_batch_header gbh,
mtl_system_items_b msi,
xla.xla_ae_headers xe,
xla.xla_ae_lines xel,
org_organization_definitions god, –gmf_organization_definitions god,
gmf_legal_entities_vw2 gle
WHERE 1 = 1
AND gbh.batch_id = geh.source_document_id
— AND geh.source_document_id = ‘754073’
AND event_class_code = ‘BATCH_RESOURCE’
AND geh.inventory_item_id = msi.inventory_item_id
AND geh.organization_id = msi.organization_id
AND xe.event_id = geh.event_id
AND xe.ae_header_id = xel.ae_header_id
AND xel.accounting_class_code = ‘WIP_VALUATION’
AND god.organization_id = geh.organization_id
AND batch_status = 2
AND gle.legal_entity_id = god.legal_entity
AND gle.legal_entity_name =NVL (:p_legal_entity, gle.legal_entity_name)
— AND god.organization_code in (:P_ORG_1,:P_ORG_2)
and (god.ORGANIZATION_CODE =nvl(:P_ORG_1,god.ORGANIZATION_CODE)
OR god.ORGANIZATION_CODE =nvl(:P_ORG_2,god.ORGANIZATION_CODE))
AND xe.period_name =:p_period)
WHERE 1 = 1
AND NVL (xxopm_period_close_pkg.xxopm_account_cr_fun (batch_id), 0) <>
NVL (xxopm_period_close_pkg.xxopm_account_dr_fun (batch_id), 0)
order by batch_no
