In response to many recent requests, I’ve included tactical details and technical code used to create SLOP (slow moving obsolete product) report. This post is geared toward all of the die-hard Oracle techies and serves as a returned favor for all the code posted in other resources that has assisted me.
Using BI Publisher with Oracle E-Business Suite to Analyze Inventory
Which items (on-hand and cost) have a high quantity on hand or a very high value of on-hand inventory and have no future demand (MRP and Sales Orders? The following emphasizes two techniques. The first using a Stored Procedure to generate Excel output using PI Publisher. The second is to provide all the queries that provide very powerful data for analyzing your inventory.
Package Specification
CREATE OR REPLACE PACKAGE
APPS.SLOP_REPORT authid
current_user asprocedure main(errbuf out varchar2,
retcode out varchar2);
procedure xml_output(p_tag in
varchar2, p_msg in varchar2);
Package Body
CREATE OR REPLACE PACKAGE BODY APPS.SLOP_REPORT
AS
PROCEDURE main (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
v_org_id NUMBER := 95;
v_Planned_orders NUMBER := 0;
v_WIP_Scheduled_Makes NUMBER := 0;
v_WIP_Scheduled_Issues NUMBER := 0;
v_PO_Scheduled_Receipts NUMBER := 0;
v_SO_Open_Qty NUMBER := 0;
CURSOR slop_lines
IS
SELECT --org.organization_code ORG,
org.organization_id,
MSI.INVENTORY_ITEM_ID,
msi.segment1 ITEM,
--msi.description DESCRIPTION,
REPLACE (REPLACE (msi.description, '&', '&' || 'amp;'),
'Ø',
NULL)
DESCRIPTION,
NVL (cst.item_cost, 0) COST,
REPLACE (msi.attribute1, '&', '&' || 'amp;') Loc1,
REPLACE (msi.attribute2, '&', '&' || 'amp;') Loc2,
NVL (SUM (ohq.transaction_quantity), 0) QOH,
msi.primary_unit_of_measure UOM,
MSI.PLANNING_MAKE_BUY_CODE MAKEBUY,
AGNT.AGENT_NAME BUYER,
(NVL (cst.item_cost, 0)
* NVL (SUM (ohq.transaction_quantity), 0))
EXT_COST
FROM bom.cst_item_costs cst,
inv.mtl_system_items_b msi,
inv.mtl_onhand_quantities_detail ohq,
apps.inv_organization_name_v org,
PO_AGENTS_V AGNT
WHERE cst.organization_id(+) = msi.organization_id
AND msi.inventory_item_id = cst.inventory_item_id(+)
AND msi.inventory_item_id = ohq.inventory_item_id
AND msi.organization_id = ohq.organization_id
AND msi.organization_id = org.organization_id
AND cst.cost_type_id = 1 --in (1,2)
AND msi.organization_id = v_org_id
AND ohq.subinventory_code = 'STOCK'
AND NVL (ohq.transaction_quantity, 0) != 0
AND MSI.BUYER_ID = AGNT.AGENT_ID(+)
GROUP BY --org.organization_code, org.organization_id,
org.organization_id,
MSI.INVENTORY_ITEM_ID,
msi.segment1,
msi.description,
cst.item_cost,
msi.attribute1,
msi.attribute2,
msi.primary_unit_of_measure,
msi.planner_code,
ohq.subinventory_code,
MSI.PLANNING_MAKE_BUY_CODE,
AGNT.AGENT_NAME;
BEGIN
xml_output (NULL, '<?xml version="1.0" encoding="UTF-8"?>');
xml_output (NULL, '<SCH_SLOP_REPORT>');
FOR record IN slop_lines
LOOP
xml_output (NULL, '<SLOP_ITEM>');
xml_output ('ORGANIZATION_ID', record.ORGANIZATION_ID);
xml_output ('INVENTORY_ITEM_ID', record.INVENTORY_ITEM_ID);
xml_output ('ITEM', record.ITEM);
xml_output ('DESCRIPTION', record.DESCRIPTION);
xml_output ('COST', record.COST);
xml_output ('LOC1', record.LOC1);
xml_output ('LOC2', record.LOC2);
xml_output ('QOH', record.QOH);
xml_output ('UOM', record.UOM);
xml_output ('EXT_COST', record.EXT_COST);
xml_output ('MAKEBUY', record.MAKEBUY);
xml_output ('BUYER', record.BUYER);
BEGIN
SELECT SUM (NVL (r.NEW_ORDER_QUANTITY, 0))
INTO v_Planned_orders
FROM MRP_RECOMMENDATIONS r
WHERE r.ORDER_TYPE = 5
AND r.INVENTORY_ITEM_ID = record.INVENTORY_ITEM_ID
AND r.ORGANIZATION_ID = record.ORGANIZATION_ID
AND r.COMPILE_DESIGNATOR = 'ACTIVEMRP'
GROUP BY r.inventory_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_Planned_orders := 0;
END;
xml_output ('PLANNED_ORDERS', v_Planned_orders);
BEGIN
SELECT SUM (wdj.START_QUANTITY - QUANTITY_COMPLETED) Open_Qty
INTO v_WIP_Scheduled_Makes
FROM WIP.WIP_DISCRETE_JOBS WDJ
WHERE (WDJ.STATUS_TYPE = 1 OR WDJ.STATUS_TYPE = 3)
AND wdj.primary_item_id = record.INVENTORY_ITEM_ID
AND WDJ.ORGANIZATION_ID = record.ORGANIZATION_ID
GROUP BY wdj.primary_item_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_WIP_Scheduled_Makes := 0;
END;
xml_output ('WIP_SCHEDULED_MAKES', v_WIP_Scheduled_Makes);
BEGIN
SELECT SUM (WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)
Open_Qty
INTO v_WIP_Scheduled_Issues
FROM WIP.WIP_DISCRETE_JOBS WDJ,
WIP.WIP_REQUIREMENT_OPERATIONS WRO
WHERE (WDJ.STATUS_TYPE = 1 OR WDJ.STATUS_TYPE = 3)
AND WRO.INVENTORY_ITEM_ID = record.INVENTORY_ITEM_ID
AND WRO.ORGANIZATION_ID = record.ORGANIZATION_ID
AND WRO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WRO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
GROUP BY WRO.INVENTORY_ITEM_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_WIP_Scheduled_Issues := 0;
END;
xml_output ('WIP_SCHEDULED_ISSUES', v_WIP_Scheduled_Issues);
BEGIN
SELECT SUM(PLL.QUANTITY
- (PLL.QUANTITY_CANCELLED + PLL.QUANTITY_RECEIVED))
POQty
INTO v_PO_Scheduled_Receipts
FROM PO.PO_LINES_ALL PL,
PO.PO_LINE_LOCATIONS_ALL PLL,
INV.MTL_SYSTEM_ITEMS_B I
WHERE PLL.CLOSED_DATE IS NULL
AND PL.ITEM_ID = record.INVENTORY_ITEM_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = record.ORGANIZATION_ID
AND PLL.NEED_BY_DATE > SYSDATE
AND PL.PO_LINE_ID = PLL.PO_LINE_ID
AND PL.ITEM_ID = I.INVENTORY_ITEM_ID
AND PLL.SHIP_TO_ORGANIZATION_ID = I.ORGANIZATION_ID
GROUP BY I.INVENTORY_ITEM_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_PO_Scheduled_Receipts := 0;
END;
xml_output ('PO_SCHEDULED_RECEIPTS', v_PO_Scheduled_Receipts);
BEGIN
SELECT SUM (OOL.ORDERED_QUANTITY) Order_Qty
INTO v_SO_Open_Qty
FROM ONT.OE_ORDER_LINES_ALL OOL
WHERE OOL.CANCELLED_FLAG <> 'Y'
AND OOL.OPEN_FLAG = 'Y'
AND OOL.BOOKED_FLAG = 'Y'
AND OOL.INVENTORY_ITEM_ID = record.INVENTORY_ITEM_ID
AND OOL.SHIP_FROM_ORG_ID = record.ORGANIZATION_ID
GROUP BY OOL.INVENTORY_ITEM_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_SO_Open_Qty := 0;
END;
xml_output ('SO_OPEN_QTY', v_SO_Open_Qty);
xml_output (NULL, '</SLOP_ITEM>');
END LOOP;
xml_output (NULL, '</SCH_SLOP_REPORT>');
END main;
PROCEDURE xml_output (p_tag IN VARCHAR2, p_msg IN VARCHAR2)
IS
v_msg VARCHAR2 (240) := NULL;
BEGIN
IF p_Tag IS NULL
THEN
v_msg := p_msg;
ELSE
v_msg := '<' || p_tag || '>' || p_msg || '</' || p_tag || '>';
END IF;
apps.fnd_file.put_line (apps.fnd_file.output, v_msg);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.LOG,
'XML_OUTPUT FUNCTION ERROR');
apps.fnd_file.put_line (apps.fnd_file.LOG,
SQLCODE || '-' || SQLERRM);
END xml_output;
END SLOP_REPORT;
/
