Items Creation throug API approach

--************package specifications*****************

CREATE OR REPLACE PACKAGE ITEMAPI_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 ITEMAPI_PKG;
/

--******************package body*********************

CREATE OR REPLACE PACKAGE BODY ITEMAPI_PKG IS

--****************map proc is started here**************

PROCEDURE MAP(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
BEGIN
UPDATE ITEMAPI SET API_VERSION='1.0';
COMMIT;
END MAP;

--***************map proc is ended here*******************

-----------------------------------------------------------------

--*************validate proc is started here***************

PROCEDURE VALIDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
V_ORGANIZATION_ID NUMBER;
CURSOR ORG_CUR IS
SELECT * FROM ITEMAPI;
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 ITEMAPI SET  STATUS_FLAG='V'WHERE TRANS_ID=ORG_REC.TRANS_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='INVALID ORGANIZATION'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
WHEN TOO_MANY_ROWS THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='MORE THAN ONE ORGANIZATION EXITS'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
WHEN OTHERS THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='INVALID ORGANIZATION'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
END;
END LOOP;
COMMIT;
END VALIDATE;

--**************validate proc is ended here****************

------------------------------------------------------------------

--**************load proc is started here*******************

PROCEDURE LOAD(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
CURSOR ITEM_CUR IS SELECT * FROM ITEMAPI;

      V_Inventory_Item_Id        NUMBER;
      V_Organization_Id          NUMBER;
      V_return_status            VARCHAR2(90);
      V_msg_count                NUMBER;
      V_msg_data                 VARCHAR2(90);

BEGIN
FOR ITEM_REC IN ITEM_CUR LOOP
BEGIN
    fnd_global.APPS_INITIALIZE(USER_ID=>1318,RESP_ID=>50583,RESP_APPL_ID=>401);
     ego_item_pub.PROCESS_ITEM(
      p_api_version  =>1.0,---------------------map
      p_init_msg_list =>  FND_API.G_TRUE,
      p_commit        => FND_API.G_TRUE,
      p_Transaction_Type =>ITEM_REC.TRANSACTION_TYPE,
      p_Segment1   =>ITEM_REC.SEGMENT1,
      p_Description =>ITEM_REC.DESCRIPTION,
      p_Long_Description =>ITEM_REC.LONG_DESCRIPTION,
      p_Organization_Id  =>ITEM_REC.ORGANIZATION_ID,
      p_Template_Id     =>ITEM_REC.TEMPLATE_ID,----------------------map
      p_Inventory_Item_Status_Code =>ITEM_REC.STATUS_CODE,
      p_approval_status            => ITEM_REC.APPROVAL_STATUS,
      x_Inventory_Item_Id =>V_Inventory_Item_Id,
      x_Organization_Id    =>V_Organization_Id,
      x_return_status      => V_return_status,
      x_msg_count          => V_msg_count,
      x_msg_data          => V_msg_data);
      dbms_output.put_line(V_Inventory_Item_Id);
      dbms_output.put_line(V_Organization_Id);
           dbms_output.put_line(V_return_status);
                dbms_output.put_line(V_msg_count);
                     dbms_output.put_line(V_msg_data);
COMMIT;
END;
END LOOP;
END LOAD;

--**************load proc is ended here**************

----------------------------------------------------------------------

--**************no submit program in API approach bcz there is no standard program API is the responsible of loading the data into base tables(No interface tables)************

PROCEDURE SUBMIT_PROGRAM(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
BEGIN
NULL;
END SUBMIT_PROGRAM;

--********************error_report proc started here ******************

PROCEDURE ERROR_REPORT(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
V NUMBER;
VE NUMBER;
LE NUMBER;
MM NUMBER;
CURSOR ERR_CUR IS SELECT * FROM ITEMAPI WHERE STATUS_FLAG NOT IN('V');
BEGIN
SELECT COUNT(*) INTO MM FROM ITEMAPI;
SELECT COUNT(*) INTO V FROM ITEMAPI WHERE STATUS_FLAG='V';
SELECT COUNT(*) INTO VE FROM ITEMAPI WHERE STATUS_FLAG='VE';
SELECT COUNT(*) INTO LE FROM ITEMAPI 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('TRANS_ID',20,' ')|| RPAD('SEGMENT1',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.TRANS_ID,30,' ')|| RPAD(ERR_REC.SEGMENT1,30,' ')||
RPAD(ERR_REC.ORGANIZATION_CODE,20,' ')||RPAD(    ERR_REC.ERROR_MSG,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;

--****************error_report proc is ended here******************

------------------------------------------------------------------------

--****************Call all procds proc is started here*************

PROCEDURE CALL_ALL_PRODS(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
v1 varchar2(90);
v2 number;
BEGIN
map(v1,v2);
validate(v1,v2);
load(v1,v2);
error_report(v1,v2);
END CALL_ALL_PRODS;

--**********call all prods proc is ended here*************************
END ITEMAPI_PKG;
/