OPM – Inventory Reconciliation Report
Introduction:
This report will provide item wise inventory reconciliation 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 inventory reconciliation value details report to check the data, so we can identify quickly and reduce lot of time.
SELECT item_code, item_description, uom, opening_stock,
transaction_contribute_cost, net_production_issue, sales_order_issue,
transfer_issue, other_transactions,
SUM ( opening_stock
+ transaction_contribute_cost
+ net_production_issue
+ sales_order_issue
+ transfer_issue
+ other_transactions
) caluclated_closing_stock,
closing_stock, organization_code,
( closing_stock
– (SUM ( opening_stock
+ transaction_contribute_cost
+ net_production_issue
+ sales_order_issue
+ transfer_issue
+ other_transactions
)
)
) difference,0 actual_period_cost,0 pervious_cost,0 transaction_cost
FROM (SELECT msi.segment1 item_code, msi.description item_description,
msi.primary_unit_of_measure uom,
NVL
(xxopm_period_close_pkg.xxopm_opening_stock_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) opening_stock,
NVL
(xxopm_period_close_pkg.xxopm_transctn_cntrbt_cost_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) transaction_contribute_cost,
NVL
(xxopm_period_close_pkg.xxopm_net_production_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) net_production_issue,
NVL
(xxopm_period_close_pkg.xxopm_sales_order_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) sales_order_issue,
NVL
(xxopm_period_close_pkg.xxopm_transfer_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) transfer_issue,
NVL
(xxopm_period_close_pkg.xxopm_other_transaction_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) other_transactions,
SUM (gpb.primary_quantity) closing_stock,
mp.organization_code,
msi.inventory_item_id
FROM mtl_system_items_b msi,
gmf_period_balances gpb,
org_acct_periods_v oapv,
mtl_parameters mp,
org_organization_definitions god,
gmf_legal_entities_vw2 gle
WHERE 1 = 1
— AND mp.organization_code IN (:p_org_1, :p_org_2)
AND msi.inventory_item_status_code = ‘Active’
AND msi.inventory_item_id = gpb.inventory_item_id
AND gpb.organization_id = msi.organization_id
AND gpb.acct_period_id = oapv.acct_period_id
AND gpb.organization_id = oapv.organization_id
AND oapv.rec_type = ‘ORG_PERIOD’
AND oapv.period_name = :p_period
AND gpb.organization_id = mp.organization_id
AND gpb.organization_id = god.organization_id
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 =nvl(:P_ORG_1,god.ORGANIZATION_CODE)
OR god.ORGANIZATION_CODE =nvl(:P_ORG_2,god.ORGANIZATION_CODE))
AND ‘Quantity’=:P_REPORT_TYPE
GROUP BY msi.segment1,
msi.description,
msi.primary_unit_of_measure,
msi.inventory_item_id,
msi.organization_id,
oapv.period_name,
mp.organization_code)
GROUP BY item_code,
item_description,
uom,
opening_stock,
transaction_contribute_cost,
net_production_issue,
sales_order_issue,
transfer_issue,
other_transactions,
closing_stock,
organization_code
UNION ALL
SELECT item_code, item_description, uom,
round((opening_stock * pervious_cost),2) opening_stock,
round((transaction_cost
),0) transaction_contribute_cost,
round((net_production_issue * actual_period_cost),2) net_production_issue,
round((sales_order_issue * actual_period_cost),2) sales_order_issue,
round((transfer_issue * actual_period_cost),2) transfer_issue,
round((other_transactions * actual_period_cost),2) other_transactions,
round((SUM ( (opening_stock * pervious_cost)
+ (transaction_cost)
+ (net_production_issue * actual_period_cost)
+ (sales_order_issue * actual_period_cost)
+ (transfer_issue * actual_period_cost)
+ (other_transactions * actual_period_cost)
)
),2) caluclated_closing_stock,
round((closing_stock * actual_period_cost),2) closing_stock,
organization_code,
round( ( (closing_stock * actual_period_cost)
– (SUM ( (opening_stock * pervious_cost)
+ (transaction_cost)
+ (net_production_issue * actual_period_cost)
+ (sales_order_issue * actual_period_cost)
+ (transfer_issue * actual_period_cost)
+ (other_transactions * actual_period_cost)
)
)
),2) difference,
NVL (actual_period_cost, 0) actual_period_cost,
NVL (pervious_cost, 0) pervious_cost,transaction_cost
FROM (SELECT msi.segment1 item_code, msi.description item_description,
msi.primary_unit_of_measure uom,
NVL
(xxopm_period_close_pkg.xxopm_opening_stock_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) opening_stock,
NVL
(xxopm_period_close_pkg.xxopm_transctn_cntrbt_cost_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) transaction_contribute_cost,
NVL
(xxopm_period_close_pkg.xxopm_net_production_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) net_production_issue,
NVL
(xxopm_period_close_pkg.xxopm_sales_order_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) sales_order_issue,
NVL
(xxopm_period_close_pkg.xxopm_transfer_issue_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) transfer_issue,
NVL
(xxopm_period_close_pkg.xxopm_other_transaction_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
) other_transactions,
SUM (gpb.primary_quantity) closing_stock,
mp.organization_code, msi.inventory_item_id,
NVL
((SELECT acctg_cost
FROM gl_item_cst gic, org_acct_periods_v oap
WHERE 1 = 1
AND gic.organization_id = oap.organization_id
AND gic.organization_id = msi.organization_id
AND gic.inventory_item_id = msi.inventory_item_id
AND oap.rec_type = ‘ORG_PERIOD’
AND oap.period_name = oapv.period_name
AND gic.start_date BETWEEN oap.start_date
AND oap.end_date
AND gic.end_date >= oap.end_date),
0
) actual_period_cost,
NVL
((SELECT acctg_cost
FROM gl_item_cst gic, org_acct_periods_v oap
WHERE 1 = 1
AND gic.organization_id = oap.organization_id
AND gic.organization_id = msi.organization_id
AND gic.inventory_item_id = msi.inventory_item_id
AND oap.rec_type = ‘ORG_PERIOD’
AND oap.period_name =
TO_CHAR (ADD_MONTHS (’01’ || :p_period, -1),
‘MON-RR’
)
AND gic.start_date BETWEEN oap.start_date
AND oap.end_date
AND gic.end_date >= oap.end_date),
0
) pervious_cost,
NVL
(xxopm_period_close_pkg.xxopm_transaction_cost_fun (msi.inventory_item_id,
msi.organization_id,
oapv.period_name
),
0
)transaction_cost
FROM mtl_system_items_b msi,
gmf_period_balances gpb,
org_acct_periods_v oapv,
mtl_parameters mp,
org_organization_definitions god,
gmf_legal_entities_vw2 gle
WHERE 1 = 1
— AND mp.organization_code IN (:p_org_1, :p_org_2)
AND msi.inventory_item_status_code = ‘Active’
AND msi.inventory_item_id = gpb.inventory_item_id
AND gpb.organization_id = msi.organization_id
AND gpb.acct_period_id = oapv.acct_period_id
AND gpb.organization_id = oapv.organization_id
AND oapv.rec_type = ‘ORG_PERIOD’
AND oapv.period_name = :p_period
AND gpb.organization_id = mp.organization_id
AND gpb.organization_id = god.organization_id
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 =nvl(:P_ORG_1,god.ORGANIZATION_CODE)
OR god.ORGANIZATION_CODE =nvl(:P_ORG_2,god.ORGANIZATION_CODE))
AND ‘Value’ = :p_report_type
GROUP BY msi.segment1,
msi.description,
msi.primary_unit_of_measure,
msi.inventory_item_id,
msi.organization_id,
oapv.period_name,
mp.organization_code)
GROUP BY item_code,
item_description,
uom,
opening_stock,
transaction_contribute_cost,
net_production_issue,
sales_order_issue,
transfer_issue,
other_transactions,
closing_stock,
organization_code,
actual_period_cost,
pervious_cost,
inventory_item_id,transaction_cost
ORDER BY ORGANIZATION_CODE
===========================
FUNCTION xxopm_opening_stock_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_opening_stock NUMBER;
v_period_dt VARCHAR2 (100);
BEGIN
v_period_dt := ’01’ || p_period_name;
SELECT opening_stock
INTO v_opening_stock
FROM (SELECT msi.segment1 item_code,
msi.description item_description,
msi.primary_unit_of_measure uom,
SUM (gpb.primary_quantity) opening_stock
FROM mtl_system_items_b msi, gmf_period_balances gpb
WHERE 1 = 1
AND msi.organization_id = p_org_id
AND msi.inventory_item_status_code = ‘Active’
AND msi.inventory_item_id = gpb.inventory_item_id
AND gpb.organization_id = msi.organization_id
AND gpb.acct_period_id =
(SELECT acct_period_id
FROM org_acct_periods_v
WHERE period_name =
TO_CHAR (ADD_MONTHS (v_period_dt, -1),
‘MON-RR’
)
AND ORGANIZATION_ID=p_org_id
AND rec_type = ‘ORG_PERIOD’)
AND msi.inventory_item_id = p_item_id
GROUP BY msi.segment1,
msi.description,
msi.primary_unit_of_measure);
RETURN NVL (v_opening_stock, 0);
END;
FUNCTION xxopm_transctn_cntrbt_cost_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (primary_quantity)
INTO v_qty
FROM mtl_material_transactions mmt, org_acct_periods_v oapv
WHERE 1 = 1
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = oapv.organization_id
AND mmt.organization_id = p_org_id
AND mmt.acct_period_id = oapv.acct_period_id
AND TRUNC (mmt.transaction_date) BETWEEN oapv.start_date
AND oapv.end_date
AND oapv.period_name = p_period_name
AND mmt.transaction_type_id IN (
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE 1 = 1
AND transaction_type_name IN
(‘PO Receipt’, ‘PO Rcpt Adjust’, ‘Return to Vendor’,
‘WIP Completion’, ‘WIP Completion Return’))
AND oapv.rec_type = ‘ORG_PERIOD’;
RETURN NVL (v_qty, 0);
END;
FUNCTION xxopm_net_production_issue_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (primary_quantity)
INTO v_qty
FROM mtl_material_transactions mmt, org_acct_periods_v oapv
WHERE 1 = 1
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = oapv.organization_id
AND mmt.organization_id = p_org_id
AND mmt.acct_period_id = oapv.acct_period_id
AND TRUNC (mmt.transaction_date) BETWEEN oapv.start_date
AND oapv.end_date
AND oapv.period_name = p_period_name
AND mmt.transaction_type_id IN (
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE 1 = 1
AND transaction_type_name IN (‘WIP Issue’, ‘WIP Return’))
AND oapv.rec_type = ‘ORG_PERIOD’;
RETURN NVL (v_qty, 0);
END;
FUNCTION xxopm_sales_order_issue_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (primary_quantity)
INTO v_qty
FROM mtl_material_transactions mmt, org_acct_periods_v oapv
WHERE 1 = 1
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = oapv.organization_id
AND mmt.organization_id = p_org_id
AND mmt.acct_period_id = oapv.acct_period_id
AND TRUNC (mmt.transaction_date) BETWEEN oapv.start_date
AND oapv.end_date
AND oapv.period_name = p_period_name
AND mmt.transaction_type_id IN (
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE 1 = 1
AND transaction_type_name IN (‘Sales order issue’))
AND oapv.rec_type = ‘ORG_PERIOD’;
RETURN NVL (v_qty, 0);
END;
FUNCTION xxopm_transfer_issue_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (primary_quantity)
INTO v_qty
FROM mtl_material_transactions mmt, org_acct_periods_v oapv
WHERE 1 = 1
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = oapv.organization_id
AND mmt.organization_id = p_org_id
AND mmt.acct_period_id = oapv.acct_period_id
AND TRUNC (mmt.transaction_date) BETWEEN oapv.start_date
AND oapv.end_date
AND oapv.period_name = p_period_name
AND mmt.transaction_type_id IN (
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE 1 = 1
AND transaction_type_name IN
(‘Int Req Direct Org Xfer’, ‘Direct Org Transfer’))
AND oapv.rec_type = ‘ORG_PERIOD’;
RETURN NVL (v_qty, 0);
END;
FUNCTION xxopm_other_transaction_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (primary_quantity)
INTO v_qty
FROM mtl_material_transactions mmt, org_acct_periods_v oapv
WHERE 1 = 1
AND mmt.inventory_item_id = p_item_id
AND mmt.organization_id = oapv.organization_id
AND mmt.organization_id = p_org_id
AND mmt.acct_period_id = oapv.acct_period_id
AND TRUNC (mmt.transaction_date) BETWEEN oapv.start_date
AND oapv.end_date
AND oapv.period_name = p_period_name
AND mmt.transaction_type_id IN (
SELECT transaction_type_id
FROM mtl_transaction_types
WHERE 1 = 1
AND transaction_type_name NOT IN
(‘PO Receipt’, ‘PO Rcpt Adjust’, ‘Return to Vendor’,
‘WIP Completion’, ‘WIP Completion Return’,
‘WIP Issue’, ‘WIP Return’, ‘Sales order issue’,
‘Int Req Direct Org Xfer’, ‘Direct Org Transfer’))
AND oapv.rec_type = ‘ORG_PERIOD’;
RETURN NVL (v_qty, 0);
END;
FUNCTION xxopm_transaction_cost_fun (
p_item_id NUMBER,
p_org_id NUMBER,
p_period_name VARCHAR2
)
RETURN NUMBER
AS
v_qty NUMBER;
BEGIN
SELECT SUM (a.cost_amt)
INTO v_qty
FROM (SELECT (cal.cost_amt * trans_qty) cost_amt
FROM gl_item_cst gic,
org_acct_periods_v oap,
cm_acst_led cal
WHERE 1 = 1
AND gic.organization_id = oap.organization_id
AND gic.organization_id = p_org_id
AND gic.inventory_item_id = p_item_id
AND cal.organization_id = gic.organization_id
AND cal.inventory_item_id = gic.inventory_item_id
AND oap.rec_type = ‘ORG_PERIOD’
AND oap.period_name = p_period_name
AND cal.cost_type_id = gic.cost_type_id
AND gic.start_date BETWEEN oap.start_date AND oap.end_date
AND gic.end_date >= oap.end_date
GROUP BY cal.cost_amt, trans_qty) a;
RETURN NVL (v_qty, 0);
END;
