Onhand Inventory by Revision Report
Introduction
This Query will help to generate the On-hand Inventory by Revision Report
Script:
Step 1: Create Package Spec
CREATE OR REPLACE PACKAGE xxxx_inventory_pkg
AS
FUNCTION xxxx_onhand_qty (
p_inv_item_id IN VARCHAR2,
p_org_id NUMBER,
p_qty_type IN VARCHAR2,
p_subinventory_code IN VARCHAR2
)
RETURN NUMBER;
END xxxx_inventory_pkg;
/
Step 2: Create Package Body
CREATE OR REPLACE PACKAGE BODY xxxx_inventory_pkg
AS
FUNCTION xxxx_onhand_qty (
p_inv_item_id VARCHAR2,
p_org_id NUMBER,
p_qty_type VARCHAR2,
p_subinventory_code VARCHAR2
)
RETURN NUMBER
IS
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
v_item_id NUMBER;
v_organization_id NUMBER;
v_qoh NUMBER;
v_rqoh NUMBER;
v_atr NUMBER;
v_att NUMBER;
v_qr NUMBER;
v_qs NUMBER;
v_lot_control_code BOOLEAN;
v_serial_control_code BOOLEAN;
l_qty NUMBER;
BEGIN
SELECT inventory_item_id, mp.organization_id
INTO v_item_id, v_organization_id
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE msib.inventory_item_id = p_inv_item_id
AND msib.organization_id = mp.organization_id
AND msib.organization_id = p_org_id; — :organization_code;
v_qoh := NULL;
v_rqoh := NULL;
v_atr := NULL;
v_lot_control_code := FALSE;
v_serial_control_code := FALSE;
–fnd_client_info.set_org_context (1);
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => ‘F’,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => v_lot_control_code,
p_is_serial_control => v_serial_control_code,
p_revision => NULL, — p_revision,
p_lot_number => NULL, — p_lot_number,
p_lot_expiration_date => SYSDATE,
p_subinventory_code => p_subinventory_code,
— p_subinventory_code,
p_locator_id => NULL, — p_locator_id,
p_onhand_source => 3,
x_qoh => v_qoh, — Quantity on-hand
x_rqoh => v_rqoh,
–reservable quantity on-hand
x_qr => v_qr,
x_qs => v_qs,
x_att => v_att, — available to transact
x_atr => v_atr — available to reserve
);
IF p_qty_type = ‘OHQ’
THEN –On Hand qty
l_qty := v_qoh; –v_QuantityOnhand;
ELSE
IF p_qty_type = ‘ATR’
THEN –Available to Reserve
l_qty := v_atr;
ELSE
IF p_qty_type = ‘ATT’
THEN –Available to Transact
l_qty := v_att;
END IF;
END IF;
END IF;
RETURN l_qty;
–return v_atr;
–DBMS_OUTPUT.put_line (‘On-Hand Quantity: ‘ || v_qoh);
–DBMS_OUTPUT.put_line (‘Available to reserve: ‘ || v_atr);
–DBMS_OUTPUT.put_line (‘Quantity Reserved: ‘ || v_qr);
–DBMS_OUTPUT.put_line (‘Quantity Suggested: ‘ || v_qs);
–DBMS_OUTPUT.put_line (‘Available to Transact: ‘ || v_att);
–DBMS_OUTPUT.put_line (‘Available to Reserve: ‘ || v_atr);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘ERROR: ‘ || SQLERRM);
END xxxx_onhand_qty;
END xxxx_inventory_pkg;
/
Step 3: Create the View
CREATE OR REPLACE VIEW “XXXX_INV_ONHAND_BY_REVSION” (“ORGANIZATION_CODE”,
“INVENTORY_ITEM_ID”,
“ORGANIZATION_ID”,
“SEGMENT2”,
“SEGMENT3”,
“BRAND”,
“ITEM”,
“DESCRIPTION”,
“STATUS”,
“SOURCE”,
“MOQ”,
“CASE”,
“STD_COST”,
“REVISION”,
“IT_REV”,
“VENDOR_PO”,
“TOTAL_OH”,
“DIFF”,
“PRIMARY_TRANSACTION_QUANTITY”,
“RES”
)
AS
SELECT ood.organization_code, msib.inventory_item_id, msib.organization_id,
micv.segment2, micv.segment3, micv1.segment5 brand,
msib.segment1 item, msib.description description,
msib.inventory_item_status_code status,
DECODE (msib.planning_make_buy_code,
‘2’, ‘BUY’,
‘1’, ‘MAKE’
) SOURCE, msib.minimum_order_quantity moq,
conversion_rate CASE, ROUND (cic.item_cost, 2) std_cost,
dirv.revision,
(SELECT mir1.revision
FROM mtl_item_revisions mir1
WHERE mir1.revision_id IN (
SELECT MAX (mir.revision_id)
FROM mtl_item_revisions mir
WHERE mir.organization_id = msib.organization_id
AND mir.inventory_item_id = msib.inventory_item_id))
it_rev,
(SELECT SUM (NVL (pla.quantity, 0) – NVL (plla.quantity_received, 0)
)
FROM po_headers_all pha,
po_vendors pv,
po_lines_all pla,
po_line_locations_all plla,
mtl_system_items_b msi,
po_vendor_sites_all pvs
WHERE pha.vendor_id = pv.vendor_id
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.item_id = msi.inventory_item_id
AND msi.organization_id = plla.ship_to_organization_id
AND pvs.vendor_site_id = pha.vendor_site_id
AND pha.org_id = 3
AND msi.inventory_item_id = msib.inventory_item_id
AND pla.quantity <> plla.quantity_received
AND (pha.closed_code = ‘OPEN’ OR NVL (pha.closed_code, ‘X’) =
‘X’
)
AND ( pla.closed_code NOT LIKE ‘%CLOSED’
OR NVL (pla.closed_code, ‘X’) = ‘X’
)
AND ( plla.closed_code NOT LIKE ‘%CLOSED’
OR NVL (plla.closed_code, ‘X’) = ‘X’
)) vendor_po,
xxxx_inventory_rpt_pkg.xxxx_onhand_qty
(msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
) total_oh,
( xxxx_inventory_rpt_pkg.xxxx_onhand_qty (msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
)
– NVL ((SELECT SUM (moqd.primary_transaction_quantity)
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.inventory_item_id = msib.inventory_item_id
AND moqd.organization_id = msib.organization_id
AND moqd.revision IS NOT NULL
GROUP BY moqd.inventory_item_id, moqd.organization_id),
0
)
) diff,
dirv.primary_transaction_quantity,
(( xxxx_inventory_rpt_pkg.xxxx_onhand_qty (msib.inventory_item_id,
msib.organization_id,
‘OHQ’,
NULL
)
– NVL ((SELECT SUM (moqd.primary_transaction_quantity)
FROM mtl_onhand_quantities_detail moqd
WHERE moqd.inventory_item_id = msib.inventory_item_id
AND moqd.organization_id = msib.organization_id
GROUP BY moqd.inventory_item_id, moqd.organization_id),
0
)
)
) res
FROM mtl_system_items_b msib,
org_organization_definitions ood,
mtl_item_categories_v micv,
mtl_item_categories_v micv1,
cst_item_costs cic,
(SELECT inventory_item_id, organization_id,
SUM
(primary_transaction_quantity
) primary_transaction_quantity,
revision
FROM mtl_onhand_quantities_detail
GROUP BY inventory_item_id, organization_id, revision) dirv,
mtl_uom_conversions muc
WHERE msib.organization_id = ood.organization_id(+)
AND micv.category_set_name(+) = ‘Inventory’
AND msib.inventory_item_id = micv.inventory_item_id(+)
AND msib.organization_id = micv.organization_id(+)
AND micv1.category_set_name(+) = ‘Brand’
AND micv1.control_level_disp(+) = ‘Master’
AND msib.inventory_item_id = micv1.inventory_item_id(+)
AND msib.organization_id = micv1.organization_id(+)
AND cic.cost_type_id(+) = 1
AND msib.inventory_item_id = cic.inventory_item_id(+)
AND msib.organization_id = cic.organization_id(+)
AND msib.inventory_item_id = dirv.inventory_item_id
AND msib.organization_id = dirv.organization_id
AND muc.unit_of_measure(+) = ‘CASE’
AND msib.inventory_item_id = muc.inventory_item_id(+);
Step 4: Enable the DBMS output and run the below anonymous block
DECLARE
v_view_data_query CLOB;
v_revision_data CLOB;
p_org_code NUMBER := 124;
BEGIN
BEGIN
SELECT
LISTAGG(””
|| revision
|| ””
|| ‘ ‘
|| revision, ‘, ‘) WITHIN GROUP(
ORDER BY
revision
)
INTO v_revision_data
FROM
( (
SELECT
revision
FROM
mtl_item_revisions
WHERE
revision <> ‘0’
GROUP BY
revision
) );
END;
v_view_data_query := ‘ SELECT
*
FROM
(
SELECT
ORGANIZATION_CODE,
SEGMENT2,
SEGMENT3,
BRAND,
ITEM,
DESCRIPTION,
STATUS,
SOURCE,
MOQ,
CASE,
STD_COST,
IT_REV,
REVISION,
VENDOR_PO,
TOTAL_OH,
DIFF,
PRIMARY_TRANSACTION_QUANTITY,
RES
FROM
XXXX_INV_ONHAND_BY_REVSION
WHERE
ORGANIZATION_ID = ‘
|| p_org_code
|| ‘
) PIVOT (
SUM ( PRIMARY_TRANSACTION_QUANTITY )
FOR REVISION
IN ( ‘
|| v_revision_data
|| ‘ )
) ‘;
dbms_output.put_line(‘Final Query ‘ || v_view_data_query);
END;
Step 5: Get the Final sql query from DBMS output and run the sql query
Got any queries?
Do drop a note by writing us at Venkatesh.b@doyensys.com or use the comment section below to ask your questions
