OPM – Intransit Value Details
Introduction:
This report will provide item wise intransit value detail for 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 intransit value details report to check the data, so we can identify quickly and reduce lot of time.
SELECT a.item_number, a.description, a.uom, a.from_org, a.to_org,
a.shipment_num, a.shipment_date, a.quantity, a.item_cost,
(a.quantity * a.item_cost) intransit_value, a.legal_entity_name,
(SELECT NAME
FROM hr_operating_units
WHERE 1 = 1 AND organization_id = a.operating_unit) operating_unit
FROM (SELECT msi.segment1 item_number, msi.description,
msi.primary_uom_code uom, forg.organization_code from_org,
torg.organization_code to_org, rsh.shipment_num shipment_num,
TO_DATE (rsh.shipped_date) shipment_date, ms.quantity,
NVL ((SELECT DISTINCT gic.acctg_cost
FROM gmf_fiscal_policies gfp,
cm_mthd_mst mthd,
gmf_period_statuses gps,
gl_item_cst gic
WHERE 1 = 1
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 gic.cost_type_id = gps.cost_type_id
AND gic.organization_id =
ms.from_organization_id
AND gic.inventory_item_id = ms.item_id
AND gfp.legal_entity_id = forg.legal_entity
AND rsh.shipped_date > gic.start_date
AND rsh.shipped_date < gic.end_date),
0
) item_cost,
gle.legal_entity_name, forg.operating_unit
FROM mtl_supply ms,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
mtl_material_transactions mmt,
mtl_system_items_b msi,
org_organization_definitions forg,
org_organization_definitions torg,
gmf_legal_entities_vw2 gle
WHERE 1 = 1
AND ms.shipment_line_id = rsl.shipment_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND mmt.transaction_id(+) = rsl.mmt_transaction_id
AND msi.inventory_item_id = ms.item_id
AND msi.organization_id = ms.from_organization_id
AND ms.from_organization_id = forg.organization_id
AND ms.to_organization_id = torg.organization_id
AND gle.legal_entity_id = forg.legal_entity
AND forg.operating_unit = NVL (:p_op_unit, forg.operating_unit)
AND gle.legal_entity_name =
NVL (:p_legal_entity, gle.legal_entity_name)) a
ORDER BY operating_unit
