Inventory Exception Report - ( SQL*Plus- Fancy HTML )

/* Fancy HTML Inventory Exception Report - SQL*Plus for Missing Subinventory Assignments */


SET SERVEROUTPUT ON SIZE 1000000;
SET VERIFY OFF;
SET PAGESIZE 200;
SET FEEDBACK OFF;
SET MARKUP HTML ENTMAP OFF;
DECLARE
L_ITEM_ID NUMBER;
L_ORGANIZATION_ID NUMBER := 0;
L_ITEM_COUNT NUMBER;
M_EXISTS VARCHAR2 (1);
L_ERR_FLAG CHAR (1) := 'N';
M_ITEM VARCHAR2 (240);
M_ORG_CODE VARCHAR2 (240);
M_SUBINV VARCHAR2 (240);

CURSOR C
IS
SELECT SEGMENT1, INVENTORY_ITEM_ID, ORGANIZATION_ID
FROM APPS.MTL_SYSTEM_ITEMS
WHERE ORGANIZATION_ID = '&1'
AND STOCK_ENABLED_FLAG = 'Y'
AND CREATION_DATE > '01-JUN-2011'; -- CUT OF DATE

CURSOR SUB_INVS (
P_ORGANIZATION_ID IN NUMBER
)
IS
SELECT DISTINCT SECONDARY_INVENTORY_NAME
FROM APPS.MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = '&1'
AND SECONDARY_INVENTORY_NAME IN ('ELECS-HA', 'STAGING');
BEGIN

-- fnd_file.put_line (fnd_file.LOG,'RUSHIKESH Log ');
-- FND_FILE.put_line(FND_FILE.output,'RUSHIKESH Output');

DBMS_OUTPUT.PUT_LINE('Report Run Date : '
|| TO_CHAR (SYSDATE, 'DD-MON-RRRR HH:MI:SS'));
DBMS_OUTPUT.PUT_LINE('ITEM SUB INVENTORY MISSIG ASSIGNMENT REPORT');
DBMS_OUTPUT.PUT_LINE('' ); L_ITEM_COUNT := 0; FOR R IN C LOOP L_ITEM_ID := NULL; L_ORGANIZATION_ID := NULL; IF (L_ERR_FLAG <> 'Y') THEN FOR R1 IN SUB_INVS (R.ORGANIZATION_ID) LOOP BEGIN -- CHECK IF THE ITEM- ORGANIZATION - SUBINVENTORY COMBINATION IS ALREADY EXISTING -- SELECT 'm' INTO M_EXISTS FROM APPS.MTL_ITEM_SUB_INVENTORIES WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID AND ORGANIZATION_ID = R.ORGANIZATION_ID AND SECONDARY_INVENTORY = R1.SECONDARY_INVENTORY_NAME; --DBMS_OUTPUT.PUT_LINE('INVENTORY_ITEM_ID :'||R.INVENTORY_ITEM_ID); --DBMS_OUTPUT.PUT_LINE('ORGANIZATION_ID :'||R.ORGANIZATION_ID); --DBMS_OUTPUT.PUT_LINE('SUB INVENTORY :'||R1.SECONDARY_INVENTORY_NAME); ----- IF THE COMBINATION DOESNOT EXIST THEN POPULATE TO THE SUBINVENTORY TABLE ---------------- EXCEPTION WHEN NO_DATA_FOUND THEN SELECT SEGMENT1 INTO M_ITEM FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID = R.INVENTORY_ITEM_ID AND ORGANIZATION_ID = R.ORGANIZATION_ID; SELECT ORGANIZATION_CODE INTO M_ORG_CODE FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_ID = R.ORGANIZATION_ID; DBMS_OUTPUT.PUT_LINE( '' || '' || ''); L_ITEM_COUNT := L_ITEM_COUNT + 1; WHEN OTHERS THEN NULL; END; END LOOP; END IF; -- END IF; END LOOP; DBMS_OUTPUT.PUT_LINE('

Item Code	Organization	Missing Subinventory
'|| M_ITEM ||'	' || M_ORG_CODE ||'	' || R1.SECONDARY_INVENTORY_NAME ||'

' );
END;
/