--*****************************PACKAGE CODE***********************************************
--*******************PACKAGE*****************
CREATE OR REPLACE PACKAGE DATAFILE3_PKG IS
PROCEDURE MAP(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE VALIDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE LOAD(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE SUBMIT_PROGRAM(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE ERROR_REPORT(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE CALL_ALL_PRODS(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
END DATAFILE3_PKG;
/
CREATE OR REPLACE PACKAGE BODY DATAFILE3_PKG IS
PROCEDURE MAP(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
BEGIN
UPDATE DATAFILE3 SET
PROCESS_FLAG=1,
TRANSACTION_TYPE='CREATE',
SET_PROCESS_ID=500,
LAST_UPDATE_DATE=TRUNC(SYSDATE),
CREATION_DATE=TRUNC(SYSDATE),
LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
CREATED_BY=FND_GLOBAL.USER_ID,
STATUS_FLAG='N',
ERROR_MESSAGE=NULL,
ORGANIZATION_ID=NULL;
END MAP;
--********VALIDATE*******************************************************************************************************************************
--****ORG VALIDATION******
PROCEDURE VALIDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
V_ORGANIZATION_ID NUMBER;
CURSOR ORG_CUR IS
SELECT * FROM DATAFILE3;
BEGIN
FOR ORG_REC IN ORG_CUR LOOP
BEGIN
SELECT ORGANIZATION_ID INTO V_ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS
WHERE UPPER(ORGANIZATION_CODE)=UPPER(ORG_REC.ORGANIZATION_CODE);
UPDATE DATAFILE3 SET STATUS_FLAG='V',ORGANIZATION_ID=V_ORGANIZATION_ID
WHERE TRANSACTION_ID=ORG_REC.TRANSACTION_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE DATAFILE3 SET STATUS_FLAG='VE',ERROR_MESSAGE='INVALID ORGANIZATION'
WHERE TRANSACTION_ID=ORG_REC.TRANSACTION_ID;
WHEN TOO_MANY_ROWS THEN
UPDATE DATAFILE3 SET STATUS_FLAG='VE',ERROR_MESSAGE='MORE THAN ONE ORGANIZATION EXITS'
WHERE TRANSACTION_ID=ORG_REC.TRANSACTION_ID;
WHEN OTHERS THEN
UPDATE DATAFILE3 SET STATUS_FLAG='VE',ERROR_MESSAGE='INVALID ORGANIZATION'
WHERE TRANSACTION_ID=ORG_REC.TRANSACTION_ID;
END;
END LOOP;
COMMIT;
END VALIDATE;
--***********LOAD**************************************************************************************************************************
PROCEDURE LOAD(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
CURSOR LOAD_CUR IS
SELECT
ITEM_NUMBER,
DESCRIPTION,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
SET_PROCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DECODE(INVENTORY_ITEM_FLAG,'Yes','Y','No','N',INVENTORY_ITEM_FLAG) "INVENTORY_ITEM_FLAG",
DECODE(STOCK_ENABLED,'Yes','Y','No','N',STOCK_ENABLED) "STOCK_ENABLED",
DECODE(RETURNABLE,'Yes','Y','No','N',RETURNABLE) "RETURNABLE",
DECODE(RFQ_REQUIRED,'Yes','Y','No','N',RFQ_REQUIRED) "RFQ_REQUIRED",
DECODE(ALLOW_SUBSTITUTE_RECEIPTS,'Yes','Y','No','N',ALLOW_SUBSTITUTE_RECEIPTS) "ALLOW_SUBSTITUTE_RECEIPTS",
DECODE(ALLOW_UNORDERED_RECEIPTS,'Yes','Y','No','N',ALLOW_UNORDERED_RECEIPTS) "ALLOW_UNORDERED_RECEIPTS",
DECODE(PURCHASABLE,'Yes','Y','No','N',PURCHASABLE) "PURCHASABLE",
DECODE(CUSTOMER_ORDERED,'Yes','Y','No','N',CUSTOMER_ORDERED) "CUSTOMER_ORDERED",
ITEM_PRICE,
DECODE(INTERNAL_ORDERED,'Yes','Y','No','N',INTERNAL_ORDERED) "INTERNAL_ORDERED",
MARKET_PRICE,
STATUS_FLAG,
TRANSACTION_ID
FROM DATAFILE3
WHERE STATUS_FLAG='V';
BEGIN
FOR LOAD_REC IN LOAD_CUR LOOP
BEGIN
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
(SEGMENT1,
DESCRIPTION,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
SET_PROCESS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INVENTORY_ITEM_FLAG,
STOCK_ENABLED_FLAG,
RETURNABLE_FLAG,
RFQ_REQUIRED_FLAG,
ALLOW_SUBSTITUTE_RECEIPTS_FLAG,
ALLOW_UNORDERED_RECEIPTS_FLAG,
PURCHASING_ITEM_FLAG,
CUSTOMER_ORDER_FLAG,
INTERNAL_ORDER_FLAG,
LIST_PRICE_PER_UNIT,
MARKET_PRICE) VALUES
(LOAD_REC.ITEM_NUMBER,
LOAD_REC.DESCRIPTION,
LOAD_REC.ORGANIZATION_ID,
LOAD_REC.INVENTORY_ITEM_ID,
LOAD_REC.PROCESS_FLAG,
LOAD_REC.TRANSACTION_TYPE,
LOAD_REC.SET_PROCESS_ID,
LOAD_REC.LAST_UPDATE_DATE,
LOAD_REC.LAST_UPDATED_BY,
LOAD_REC.CREATION_DATE,
LOAD_REC.CREATED_BY,
LOAD_REC.INVENTORY_ITEM_FLAG,
LOAD_REC.STOCK_ENABLED,
LOAD_REC.RETURNABLE,
LOAD_REC.RFQ_REQUIRED,
LOAD_REC.ALLOW_SUBSTITUTE_RECEIPTS,
LOAD_REC.ALLOW_UNORDERED_RECEIPTS,
LOAD_REC.PURCHASABLE,
LOAD_REC.CUSTOMER_ORDERED,
LOAD_REC.INTERNAL_ORDERED,
LOAD_REC.ITEM_PRICE,
LOAD_REC.MARKET_PRICE);
EXCEPTION
WHEN OTHERS THEN
UPDATE DATAFILE3 SET STATUS_FLAG='LE',ERROR_MESSAGE=ERROR_MESSAGE||'-'||'ERROR WHILE LOADING DATA'
WHERE TRANSACTION_ID=LOAD_REC.TRANSACTION_ID;
END;
END LOOP;
COMMIT;
END LOAD;
--****************SUBMIT_PROGRAM*************
PROCEDURE SUBMIT_PROGRAM(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
V_REQUEST_ID NUMBER;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(USER_ID=>FND_GLOBAL.user_id,
RESP_ID=>FND_GLOBAL.resp_id,
RESP_APPL_ID=>FND_GLOBAL.resp_appl_id);
V_REQUEST_ID:=FND_REQUEST.submit_request ( application =>'INV',
program =>'INCOIN',
description =>'TATA ITENS INTERFACE',
argument1=>204,
argument2=>1,
argument3=>1,
argument4=>1,
argument5=>2,
argument6=>500,
argument7=>1);
DBMS_OUTPUT.PUT_LINE('V_REQUEST_ID'||V_REQUEST_ID);
COMMIT;
END SUBMIT_PROGRAM;
--***************ERROR_REPORT**************
PROCEDURE ERROR_REPORT(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
V NUMBER;
VE NUMBER;
LE NUMBER;
MM NUMBER;
CURSOR ERR_CUR IS SELECT * FROM DATAFILE3 WHERE STATUS_FLAG NOT IN('V');
BEGIN
SELECT COUNT(*) INTO MM FROM DATAFILE3;
SELECT COUNT(*) INTO V FROM DATAFILE3 WHERE STATUS_FLAG='V';
SELECT COUNT(*) INTO VE FROM DATAFILE3 WHERE STATUS_FLAG='VE';
SELECT COUNT(*) INTO LE FROM DATAFILE3 WHERE STATUS_FLAG='LE';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ITEM CREATION ERROR REPORT');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ---------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'SUMMARY DETAILS:');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'_______________ ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS PROCESSED',36,' ')||':'||MM);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS SUCCESFULLY VALIDATED',36,' ')||':'||V);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS FAILED IN VALIDATION',36,' ')||':'||VE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS FAILED IN LOAD',36,' ')||':'||LE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'ERROR RECORDS:');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('TRANSACTION_ID',20,' ')|| RPAD('ITEM_NUMBER',30,' ')||
RPAD('ORGANIZATION_CODE',30,' ')||RPAD('ERROR_MESSAGE',500,' '));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('______________',20,' ')|| RPAD('_________',30,' ')||
RPAD('________',30,' ')||RPAD('_____________',500,' '));
FOR ERR_REC IN ERR_CUR LOOP
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(ERR_REC.TRANSACTION_ID,30,' ')|| RPAD(ERR_REC.ITEM_NUMBER,30,' ')||
RPAD(ERR_REC.ORGANIZATION_CODE,20,' ')||RPAD( ERR_REC.ERROR_MESSAGE,500,' '));
END;
END LOOP;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' *********END OF THE ERROR REPORT**********');
END ERROR_REPORT;
--***********CALL_ALL_PRODS****************
PROCEDURE CALL_ALL_PRODS(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER) IS
V1 VARCHAR2(90);
V2 NUMBER;
BEGIN
DATAFILE3_PKG.MAP(V1,V2);
DATAFILE3_PKG.VALIDATE(V1,V2);
DATAFILE3_PKG.LOAD(V1,V2);
DATAFILE3_PKG.SUBMIT_PROGRAM(V1,V2);
DATAFILE3_PKG.ERROR_REPORT(V1,V2);
END CALL_ALL_PRODS;
END DATAFILE3_PKG;
/
No comments:
Post a Comment