PO Interface

CREATE OR REPLACE PROCEDURE PO_INT(ERRBUF OUT VARCHAR2,
                                    RETCODE OUT VARCHAR2)
AS
CURSOR C1 IS SELECT * FROM XX_HEADERS;
CURSOR C2 IS SELECT * FROM XX_LINES;
CURSOR C3 IS SELECT * FROM XX_DIST;
L_VENDOR_ID NUMBER(10);
L_ITEM VARCHAR2(150);
L_FLAG VARCHAR2(4) DEFAULT 'A';
L_MSG VARCHAR2(200);
L_SITE_CODE VARCHAR2(100);
L_CURR_CODE VARCHAR2(10);
L_ORG_ID NUMBER(6);
begin
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
FOR X1 IS C1 LOOP
BEGIN
 SELECT VENDOR_ID
 INTO L_VENDOR_ID
 FROM PO_VENDORS
 WHERE VENDOR_NAME = X1.VENDOR_NAME
 EXCEPTION WHEN OTHERS THEN
 L_FLAG :='E';
 L_MSG := 'VENDOR ID IS NOT IN SYSTEM';
 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED'||L_MSG);
 END;
 -- VENDOR SITE CODE VALIDATION
 BEGIN
 SELECT VENDOR_SITE_CODE
 INTO L_SITE_CODE
 FROM PO_VENDOR_SITES_ALL
 WHERE VENDOR_SITE_CODE=X1.VENDOR_SITE_CODE;
 EXCEPTION WHEN OTHERS THEN
 L_FLAG  :='E';
 L_MSG := 'VENDOR SITE CODE IS NOT IN SYSTEM';
 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED'||L_MSG);
 END;
 -- END OF SITE CODE VALIDATION
-- CURRECY CODE VALIDATION
BEGIN
SELECT CURRENCY_CODE
INTO L_CURR_CODE
FROM FND_CURRENCIES WHERE
CURRENCY_CODE=X1.CURRENCY_CODE;
EXCEPTION WHEN OTHERS THEN
L_FLAG :='E';
L_MSG:='CURRENCY CODE IS INVALID';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED'||L_MSG);
END;
--END OF TE CURRENCY VALIDATIN
-- OPERATING UNIT ID VAIDATION
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORG_ID
FROM HR_OPERATING_UNITS
WHERE ORGANIZATION_ID=X1.ORG_ID;
EXCEPTION WHEN OTHERS THEN
L_FLAG :='E',
L_MSG:='INVALID ORGANIZATION ID';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED'++L_MSG);
END;
--END OF THE ORG ID VALIDATION
IF L_FLAG !='E' THEN
INSERT INTO PO_HEADERS_INTERFACE
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,CREATION_DATE
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
)
VALUES
(
X1.INTERFACE_HEADER_ID,
,X1.BATCH_ID
,X1.ACTION
,X1.ORG_ID
,X1.DOCUMENT_TYPE_CODE
,X1.CURRENCY_CODE
,X1.AGENT_NAME
,X1.VENDOR_NAME
,X1.VENDOR_SITE_CODE
,X1.SHIP_TO_LOCATION
,X1.BILL_TO_LOCATION
,SYSDATE-10
,X1.APPROVAL_STATUS
,SYSDATE
,X1.FREIGHT_TERMS
);
END IF;
END LOOP;
FOR X2 IN C2 LOOP
L_FLAG :='A';
--ITEM VALIDATION
BEGIN
SELECT SEGMENT1
INTO L_ITEM
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 =X2.ITEM
AND ORGANIZATION_ID FND_PROFILE.VALUE('ORG_ID');
EXCEPTION WHEN OTHERS THEN
L_FLAG :='E';
L_MSG := 'ITEM ISNOT VALID ITEM';
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR OCCURED'||L_MSG);
END;
--END OF THE ITEM VALIDATION
IF L_FLAG !='E' THEN
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,ITEM_ID
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,LIST_PRICE_PER_UNIT
)
VALUES
(
X2.INTERFACE_LINE_ID
,X2.INTERFACE_HEADER_ID
,X2.LINE_NUM
,X2.SHIPMENT_NUM
,X2.LINE_TYPE
,X2.ITEM
,X2.ITEM_DESCRIPTION
,X2.ITEM_ID
,X2.UOM_CODE
,X2.QUANTITY
,X2.UNIT_PRICE
,X2.SHIP_TO_ORGANIZATION_CODE
,X2.SHIP_TO_LOCATION
,SYSDATE
,SYSDATE
,X2.LIST_PRICE_PER_UNIT
)
END IF;
END LOOP;
FOR X3 IN C3 LOOP
L_FLAG :='A';
IF L_FLAG !='E' THEN
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(
INTERFACE_HEADER_ID
,INTERFACE_LINE_ID
,INTERFACE_DISTRIBUTION_ID
,SET_OF_BOOKS_ID
,DESTINATION_ORGANIZATION_ID
,ORG_ID
,QUANTITY_ORDERED
)
VALUES
(
X3.INTERFACE_HEADER_ID
,X3.INTERFACE_LINE_ID
,X3.INTERFACE_DISTRIBUTION_ID
,X3.SET_OF_BOOKS_ID
,X3.DESTINATION_ORGANIZATION_ID
,X3.ORG_ID
,X3.QUANTITY_ORDERED
);
END IF;
END LOOP;
COMMIT;
END PO_INT;