Interface to process Item Pending Statuses

CREATE OR REPLACE PACKAGE XX_PEND_STATUS_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE XX_PEND_ITEM_STG;
PROCEDURE XX_PEND_VAL_STG;
PROCEDURE XX_PROCESS_PEND;
END XX_PEND_STATUS_PKG;


CREATE OR REPLACE PACKAGE BODY XX_PEND_STATUS_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
XX_PEND_ITEM_STG;
XX_PEND_VAL_STG;
XX_PROCESS_PEND;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END MAIN;

PROCEDURE XX_PEND_ITEM_STG IS
CURSOR CUR_PEND_ITEM IS
SELECT MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MP.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
MIS.INVENTORY_ITEM_STATUS_CODE,
MIS.DESCRIPTION,
MPI.IMPLEMENTED_DATE,
MPI.EFFECTIVE_DATE,
MPI.PENDING_FLAG
FROM MTL_SYSTEM_ITEMS_B MSI,
MTL_PENDING_ITEM_STATUS MPI,
MTL_PARAMETERS MP,
MTL_ITEM_STATUS MIS
WHERE MSI.INVENTORY_ITEM_ID=MPI.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MPI.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND MPI.STATUS_CODE=MIS.INVENTORY_ITEM_STATUS_CODE
AND MSI.INVENTORY_ITEM_ID='8063'
AND MP.ORGANIZATION_ID='204'
AND MPI.PENDING_FLAG='Y';
BEGIN
DELETE XX_PENDING_ITEM_STATUS;
COMMIT;
FOR REC_CUR_PEND_ITEM IN CUR_PEND_ITEM LOOP
INSERT INTO XX_PENDING_ITEM_STATUS
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
STATUS_CODE,
IMPLEMENTED_DATE,
PENDING_FLAG,
EFFECTIVE_DATE,
STATUS_FLAG)
VALUES
(REC_CUR_PEND_ITEM.INVENTORY_ITEM_ID,
REC_CUR_PEND_ITEM.ORGANIZATION_ID,
REC_CUR_PEND_ITEM.INVENTORY_ITEM_STATUS_CODE,
REC_CUR_PEND_ITEM.IMPLEMENTED_DATE,
REC_CUR_PEND_ITEM.PENDING_FLAG,
REC_CUR_PEND_ITEM.EFFECTIVE_DATE,
'N');
END LOOP;
END XX_PEND_ITEM_STG;

PROCEDURE XX_PEND_VAL_STG
IS
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_INVENTORY_ITEM_STATUS_CODE MTL_ITEM_STATUS.INVENTORY_ITEM_STATUS_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_PEND_VALID IS
SELECT A.*,ROWID
FROM XX_PENDING_ITEM_STATUS A WHERE A.STATUS_FLAG='N';
BEGIN
FOR REC_CUR_PEND_VALID IN CUR_PEND_VALID LOOP
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = REC_CUR_PEND_VALID.ORGANIZATION_ID;
UPDATE XX_PENDING_ITEM_STATUS
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ORGANIZATION ID DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_PEND_VALID.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_PENDING_ITEM_STATUS
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_STATUS_CODE
INTO L_INVENTORY_ITEM_STATUS_CODE
FROM MTL_ITEM_STATUS
WHERE INVENTORY_ITEM_STATUS_CODE=REC_CUR_PEND_VALID.STATUS_CODE;
UPDATE XX_PENDING_ITEM_STATUS
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM STATUS CODE DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_PENDING_ITEM_STATUS
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
ELSE
UPDATE XX_PENDING_ITEM_STATUS
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
END IF;
END LOOP;
END XX_PEND_VAL_STG;

PROCEDURE XX_PROCESS_PEND AS
A VARCHAR2(1000);
B NUMBER;
BEGIN
BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT('204');
END;
INV_ITEM_STATUS_CP.PROCESS_PENDING_STATUS(A,B,p_Org_Id => NULL,p_Item_Id => NULL ,
p_commit => 'TRUE',p_prog_appid => NULL,p_request_id => NULL,p_user_id =>'1318',
p_login_id => '-1',p_init_msg_list => FND_API.G_TRUE,p_msg_logname => 'FILE');
END XX_PROCESS_PEND;
END XX_PEND_STATUS_PKG;