PO interface based on project, Task, Expenditure type
Staging.sql
CREATE TABLE APPS.XX_NEW_PO
(
ROWNUMBER NUMBER,
SUPPLIER_NUMBER VARCHAR2(30 BYTE),
SUPPLIER_SITE VARCHAR2(30 BYTE),
SUPPLIER_NAME VARCHAR2(240 BYTE),
PO_NUMBER VARCHAR2(30 BYTE),
SHIP_TO_LOCATION VARCHAR2(100 BYTE),
CURRENCY_CODE VARCHAR2(30 BYTE),
BILL_TO_LOCATION VARCHAR2(100 BYTE),
PO_DESCRIPTION VARCHAR2(500 BYTE),
PO_DATE DATE,
APPROVAL_STATUS VARCHAR2(30 BYTE),
BUYER_ID NUMBER,
PO_LINE_NO NUMBER,
LINE_TYPE VARCHAR2(30 BYTE),
PROJECT_CODE VARCHAR2(30 BYTE),
TASK_CODE VARCHAR2(240 BYTE),
EXPENDITURE_TYPE VARCHAR2(30 BYTE),
RECEIPT_NO VARCHAR2(30 BYTE),
RECEIPT_DATE DATE,
ITEM_CODE VARCHAR2(30 BYTE),
ORACLE_ITEM_CODE VARCHAR2(30 BYTE),
ITEM_NAME VARCHAR2(240 BYTE),
UOM VARCHAR2(30 BYTE),
QUANTITY NUMBER,
RATE NUMBER,
NEED_BY_DATE DATE,
SHIP_TO_ORG_CODE VARCHAR2(240 BYTE),
CHARGE_ACCOUNT VARCHAR2(240 BYTE),
DESTINATION_TYPE VARCHAR2(30 BYTE),
GL_DATE DATE,
OU_NAME VARCHAR2(240 BYTE),
ERR_FLAG CHAR(2 BYTE),
ERR_MSG VARCHAR2(1000 BYTE),
STATUS VARCHAR2(150 BYTE),
ORG_CODE VARCHAR2(50 BYTE),
BUYER VARCHAR2(250 BYTE),
ACCRUAL_ACCOUNT VARCHAR2(240 BYTE)
)
Package Spec
CREATE OR REPLACE PACKAGE APPS.XX_OPENPO_PKG
IS
PROCEDURE dopo_validation;
PROCEDURE XX_OPEN_GRN_PO (ERRBUF OUT VARCHAR2, RETCODE OUT NUMBER);
END XX_OPENPO_PKG;
/
Package Body
CREATE OR REPLACE PACKAGE BODY APPS.XX_OPENPO_PKG
IS
PROCEDURE DOPO_VALIDATION
IS
—
CURSOR C_AP_CUR
IS
SELECT *
FROM XX_NEW_PO A
WHERE 1 = 1 AND NVL (A.STATUS, ‘N’) = ‘N’;
— AND A.PO_NUMBER=’TEC/IT/233/19′;
—
L_VENDOR_ID NUMBER;
L_VENDOR_SITE_ID NUMBER;
L_LIAB_CC_ID NUMBER;
L_ERR_MSG VARCHAR2 (1000);
L_ORGANIZATION_ID NUMBER;
L_OU_ID NUMBER;
L_BILL_LOC_ID NUMBER;
L_SHIP_LOC_ID NUMBER;
L_PROJECT_ID NUMBER;
L_TASK_ID NUMBER;
L_ITEM_ID NUMBER;
L_CURRENCY_CODE VARCHAR2 (50);
L_BUYER_ID NUMBER;
L_EXPENDITURE_TYPE VARCHAR2 (200);
L_UOM_CODE VARCHAR2 (100);
L_ACCR_CC_ID NUMBER;
—
BEGIN
—
FOR C_AP_REC IN C_AP_CUR
LOOP
—
L_ERR_MSG := NULL;
— OU
—
BEGIN
SELECT ORGANIZATION_ID
INTO L_OU_ID
FROM HR_OPERATING_UNITS
WHERE NAME = C_AP_REC.OU_NAME;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Org is not Exist’;
—
END;
—
— VENDOR
BEGIN
—
SELECT VENDOR_ID
INTO L_VENDOR_ID
FROM AP_SUPPLIERS
WHERE SEGMENT1 = C_AP_REC.SUPPLIER_NUMBER;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘Vendor Number is not Exist’;
END;
—
— VENDOR SITES
BEGIN
—
SELECT SS.VENDOR_SITE_ID
INTO L_VENDOR_SITE_ID
FROM AP_SUPPLIERS S, AP_SUPPLIER_SITES_ALL SS
WHERE SS.VENDOR_ID = S.VENDOR_ID
AND SS.VENDOR_SITE_CODE = C_AP_REC.SUPPLIER_SITE
AND SEGMENT1 = C_AP_REC.SUPPLIER_NUMBER
AND PURCHASING_SITE_FLAG = ‘Y’
AND ORG_ID = L_OU_ID;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Vendor Site/Site Org is not Exist’;
—
END;
— SHIP TO LOCATION
—
BEGIN
SELECT LOCATION_ID
INTO L_SHIP_LOC_ID
FROM HR_LOCATIONS
WHERE LOCATION_CODE = C_AP_REC.SHIP_TO_LOCATION;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Ship To Location is not Exist’;
—
END;
— CURRENCY
BEGIN
SELECT CURRENCY_CODE
INTO L_CURRENCY_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = C_AP_REC.CURRENCY_CODE;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Currency is not Exist’;
—
END;
— BILL TO LOCATION
—
BEGIN
SELECT LOCATION_ID
INTO L_BILL_LOC_ID
FROM HR_LOCATIONS
WHERE LOCATION_CODE = C_AP_REC.BILL_TO_LOCATION;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Bill To Location is not Exist’;
—
END;
— BUYER
—
BEGIN
SELECT AGENT_ID
INTO L_BUYER_ID
FROM PER_ALL_PEOPLE_F PPF, PO_AGENTS PA
WHERE FULL_NAME = C_AP_REC.BUYER
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
AND PPF.PERSON_ID = PA.AGENT_ID;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Buyer is not Exist’;
—
END;
— PROJECT
—
BEGIN
IF C_AP_REC.PROJECT_CODE IS NULL
THEN
L_PROJECT_ID := NULL;
ELSE
SELECT PROJECT_ID
INTO L_PROJECT_ID
FROM PA_PROJECTS_ALL
WHERE SEGMENT1 = C_AP_REC.PROJECT_CODE AND ORG_ID = L_OU_ID;
END IF; —
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Project is not Exist’;
—
END;
— TASK
—
BEGIN
IF C_AP_REC.PROJECT_CODE IS NULL AND C_AP_REC.TASK_CODE IS NULL
THEN
L_TASK_ID := NULL;
ELSE
SELECT TASK_ID
INTO L_TASK_ID
FROM PA_PROJECTS_ALL PA, PA_TASKS PT
WHERE SEGMENT1 = C_AP_REC.PROJECT_CODE
AND PT.PROJECT_ID = PA.PROJECT_ID
AND ROWNUM = 1
AND PT.TASK_NUMBER = C_AP_REC.TASK_CODE
AND PA.ORG_ID = L_OU_ID;
END IF;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Task is not Exist’;
—
END;
— EXPENDITURE TYPE
—
BEGIN
IF C_AP_REC.EXPENDITURE_TYPE IS NULL
THEN
L_EXPENDITURE_TYPE := NULL;
ELSE
SELECT EXPENDITURE_TYPE
INTO L_EXPENDITURE_TYPE
FROM PA_EXPENDITURE_TYPES PET
WHERE PET.EXPENDITURE_TYPE = C_AP_REC.EXPENDITURE_TYPE;
END IF;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Expenditure Type is not Exist’;
—
END;
— SHIP TO ORG CODE
—
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = TRIM (C_AP_REC.SHIP_TO_ORG_CODE);
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Ship To Org is not Exist’;
—
END;
— ITEM
BEGIN
—
SELECT INVENTORY_ITEM_ID
INTO L_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE UPPER (SEGMENT1) = UPPER (C_AP_REC.ITEM_CODE)
AND ORGANIZATION_ID = L_ORGANIZATION_ID
AND INVENTORY_ITEM_STATUS_CODE = ‘Active’;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘Item is not Exist’;
END;
— UOM
BEGIN
—
SELECT UOM_CODE
INTO L_UOM_CODE
FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CODE = C_AP_REC.UOM;
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG := L_ERR_MSG || ‘, ‘ || ‘UOM is not Exist’;
END;
— CHARGE ACCOUNT
BEGIN
—
SELECT CC.CODE_COMBINATION_ID
INTO L_LIAB_CC_ID
FROM GL_CODE_COMBINATIONS_KFV CC
WHERE ENABLED_FLAG = ‘Y’
AND CC.CONCATENATED_SEGMENTS = C_AP_REC.CHARGE_ACCOUNT
AND EXISTS (
SELECT 1
FROM HR_OPERATING_UNITS OU, GL_LEDGERS L
WHERE OU.SET_OF_BOOKS_ID = L.LEDGER_ID
AND L.CHART_OF_ACCOUNTS_ID = CC.CHART_OF_ACCOUNTS_ID
AND OU.ORGANIZATION_ID = L_OU_ID);
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Charge Account is not Exist’;
END;
–/
— ACCRUAL ACCOUNT
—
IF C_AP_REC.PROJECT_CODE IS NOT NULL THEN
—
BEGIN
SELECT CC.CODE_COMBINATION_ID
INTO L_ACCR_CC_ID
FROM GL_CODE_COMBINATIONS_KFV CC
WHERE ENABLED_FLAG=’Y’
AND CC.CONCATENATED_SEGMENTS = C_AP_REC.ACCRUAL_ACCOUNT
AND EXISTS (
SELECT 1
FROM HR_OPERATING_UNITS OU, GL_LEDGERS L
WHERE OU.SET_OF_BOOKS_ID = L.LEDGER_ID
AND L.CHART_OF_ACCOUNTS_ID = CC.CHART_OF_ACCOUNTS_ID
AND OU.ORGANIZATION_ID = L_OU_ID);
—
EXCEPTION
WHEN OTHERS
THEN
L_ERR_MSG :=
L_ERR_MSG || ‘, ‘ || ‘Accrual Account Is Not Exist’;
END;
END IF;
—
—
L_ERR_MSG := LTRIM (L_ERR_MSG, ‘, ‘);
—
IF (L_ERR_MSG IS NOT NULL)
THEN
—
UPDATE XX_NEW_PO
SET STATUS = ‘E’,
ERR_MSG = L_ERR_MSG
WHERE ROWNUMBER = C_AP_REC.ROWNUMBER;
—
END IF;
—
END LOOP;
—
COMMIT;
—
END DOPO_VALIDATION;
PROCEDURE XX_OPEN_GRN_PO (ERRBUF OUT VARCHAR2, RETCODE OUT NUMBER)
IS
L_HEADER_ID NUMBER;
L_LINE_ID NUMBER;
L_DIST_ID NUMBER;
L_ORGANIZATION_ID NUMBER;
L_LOCATION_ID NUMBER;
L_CATEGORY_ID NUMBER;
L_BUYER_ID NUMBER;
L_TERM_ID NUMBER;
L_EXCH_RATE_TYPE VARCHAR2 (30);
L_EXCH_RATE NUMBER;
L_EXCH_RATE_DATE DATE;
L_LINE_NO NUMBER;
L_CHARGE_ACCOUNT_ID NUMBER;
L_REQUEST_ID NUMBER;
L_REVISION_NO NUMBER;
L_PO_NUMBER VARCHAR2 (30);
L_PO_HEADER_ID NUMBER;
L_PO_LINE_ID NUMBER;
L_ADD_CTR NUMBER;
L_UPDATE_CTR NUMBER;
LV_RESULT NUMBER;
LV_API_ERRORS PO_API_ERRORS_REC_TYPE;
LV_USER_ID NUMBER := FND_PROFILE.VALUE (‘USER_ID’);
LV_RESP_ID NUMBER := FND_PROFILE.VALUE (‘RESP_ID’);
LV_RESP_APPL_ID NUMBER := FND_PROFILE.VALUE (‘RESP_APPL_ID’);
LV_MSG_OUT VARCHAR2 (4000);
LV_REVISION_NUM NUMBER;
LV_PO_NUMBER VARCHAR2 (30);
LV_ORG_ID NUMBER;
LV_LINE_NO NUMBER;
LN_TASK_ID NUMBER;
LV_ERR_FLAG VARCHAR2 (100);
LV_ERR_MSG VARCHAR2 (5000);
LN_ERR_CNT NUMBER := 0;
LN_PO_NUM VARCHAR2 (50);
L_DESC VARCHAR2 (480);
L_LINE_CNT NUMBER;
CURSOR C1
IS
SELECT DISTINCT TRIM (A.PO_NUMBER) PO_NUMBER, A.PO_DATE,
A.CURRENCY_CODE, A.BUYER,
SUBSTR (A.PO_DESCRIPTION,1,240) PO_DESCRIPTION,
PV.VENDOR_ID, PVS.VENDOR_SITE_ID, PVS.ORG_ID,
BILL_LOC.LOCATION_ID BILL_LOCATION_ID,
SHIP_LOC.LOCATION_ID SHIP_LOCATION_ID,
NVL (A.STATUS, ‘N’) STATUS
FROM XX_NEW_PO A,
PO_VENDORS PV,
HR_OPERATING_UNITS HOU,
PO_VENDOR_SITES_ALL PVS,
HR_LOCATIONS BILL_LOC,
HR_LOCATIONS SHIP_LOC
WHERE PV.SEGMENT1 = TRIM (A.SUPPLIER_NUMBER)
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND PVS.VENDOR_SITE_CODE = TRIM (A.SUPPLIER_SITE)
AND PVS.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.NAME = TRIM (REPLACE (A.OU_NAME, CHR (13), ‘ ‘))
AND NVL (A.STATUS, ‘N’) = ‘N’
AND OU_NAME = ‘XXXX’
— AND po_number=’TEC/IT/245/19′
–and po_number =’TEC/IT/199/19-MOG’
AND BILL_LOC.LOCATION_CODE = TRIM (A.BILL_TO_LOCATION)
AND SHIP_LOC.LOCATION_CODE = TRIM (A.SHIP_TO_LOCATION)
AND PO_LINE_NO = 1;
WC1 C1%ROWTYPE;
CURSOR C2
IS
SELECT A.ROWNUMBER, A.SUPPLIER_NUMBER, A.SUPPLIER_SITE, A.PO_NUMBER,
A.PO_DESCRIPTION, A.PO_DATE, A.PO_LINE_NO, A.PROJECT_CODE,
A.RECEIPT_NO, A.RECEIPT_DATE, A.ITEM_CODE, A.ORACLE_ITEM_CODE,
A.QUANTITY, A.RATE, PV.VENDOR_ID, PVS.VENDOR_SITE_ID,
MSI.INVENTORY_ITEM_ID, PVS.ORG_ID, MSI.DESCRIPTION,
MSI.PRIMARY_UOM_CODE, OOD.ORGANIZATION_ID, A.SHIP_TO_ORG_CODE,
GCC.CODE_COMBINATION_ID CHARGE_ACCOUNT_ID,
GCC1.CODE_COMBINATION_ID ACCRUAL_ACCOUNT_ID, A.TASK_CODE,
A.EXPENDITURE_TYPE, A.NEED_BY_DATE, A.GL_DATE,
(SELECT PA.PROJECT_ID
FROM PA_PROJECTS_ALL PA
WHERE PA.SEGMENT1(+) = TRIM (A.PROJECT_CODE)
AND ROWNUM = 1
AND PA.ORG_ID = HOU.ORGANIZATION_ID) PROJECT_ID,
(SELECT PT.TASK_ID
FROM PA_PROJECTS_ALL PA, PA_TASKS PT
WHERE PA.SEGMENT1 = TRIM (A.PROJECT_CODE)
AND PT.PROJECT_ID = PA.PROJECT_ID
AND PA.ORG_ID = HOU.ORGANIZATION_ID
AND PT.TASK_NUMBER(+) = A.TASK_CODE
AND ROWNUM = 1
— SUBSTR (A.EXPENDITURE_TYPE, 1, 3)
— || ’00’
) TASK_ID
FROM XX_NEW_PO A,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
MTL_SYSTEM_ITEMS_B MSI,
–ORG_ORGANIZATION_DEFINITIONS OOD,
HR_ALL_ORGANIZATION_UNITS OOD,
GL_CODE_COMBINATIONS_KFV GCC,
GL_CODE_COMBINATIONS_KFV GCC1,
HR_OPERATING_UNITS HOU
WHERE TRIM (A.PO_NUMBER) = WC1.PO_NUMBER
AND PV.SEGMENT1 = TRIM (A.SUPPLIER_NUMBER)
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND PVS.VENDOR_SITE_CODE = TRIM (A.SUPPLIER_SITE)
AND OOD.NAME = TRIM (A.SHIP_TO_ORG_CODE)
— AND OOD.ORGANIZATION_CODE = A.ORG_CODE
AND PVS.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.NAME = TRIM (REPLACE (A.OU_NAME, CHR (13), ‘ ‘))
AND GCC.CONCATENATED_SEGMENTS = TRIM (A.CHARGE_ACCOUNT)
AND GCC1.CONCATENATED_SEGMENTS(+) = TRIM (A.ACCRUAL_ACCOUNT)
AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSI.SEGMENT1 = TRIM (A.ITEM_CODE);
CURSOR C3
IS
SELECT *
FROM XX_NEW_PO A
WHERE NVL (A.STATUS, ‘N’) = ‘N’;
CURSOR C4
IS
SELECT COUNT (*)
FROM XX_NEW_PO A
WHERE 1 = 1
AND NVL (A.STATUS, ‘N’) = ‘N’
AND NVL (TRIM (ERR_FLAG), ‘N’) = ‘Y’;
BEGIN
/*BEGIN
UPDATE XX_NEW_PO TG
SET ORG_CODE = (SELECT ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS D
WHERE D.ORGANIZATION_NAME = TRIM(TG.SHIP_TO_ORG_CODE));
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘ERROR IN ORG CODE’);
END;
*/
/* FOR T IN C3
LOOP
LV_ERR_FLAG := NULL;
LV_ERR_MSG := NULL;
BEGIN
IF T.PROJECT_CODE IS NULL
THEN
LV_ERR_FLAG:=NULL;
ELSE
SELECT ‘N’
INTO LV_ERR_FLAG
FROM PA_TASKS PT, PA_PROJECTS_ALL PA
WHERE PT.TASK_NUMBER = T.TASK_CODE
AND PA.PROJECT_ID = PT.PROJECT_ID
AND PA.SEGMENT1 = T.PROJECT_CODE;
END IF;
EXCEPTION
WHEN OTHERS
THEN
–LN_TASK_ID:=-9999;
LV_ERR_FLAG := ‘Y’;
LV_ERR_MSG := ‘INVALID PROJECT TASK’ || ‘,’ || SQLERRM;
END;
BEGIN
SELECT PERSON_ID
INTO L_BUYER_ID
FROM PER_ALL_PEOPLE_F
WHERE FULL_NAME = T.BUYER;
EXCEPTION
WHEN OTHERS
THEN
–LN_TASK_ID:=-9999;
LV_ERR_FLAG := ‘Y’;
LV_ERR_MSG := ‘BUYER INVALID’ || ‘,’ || SQLERRM;
END;
UPDATE XX_NEW_PO
SET ERR_FLAG = LV_ERR_FLAG,
ERR_MSG = LV_ERR_MSG
WHERE PO_NUMBER = T.PO_NUMBER AND ROWNUMBER = T.ROWNUMBER;
END LOOP;
COMMIT;
*/
LN_ERR_CNT := 0;
OPEN C4;
FETCH C4
INTO LN_ERR_CNT;
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Count:’ || LN_ERR_CNT);
CLOSE C4;
IF LN_ERR_CNT = 0
THEN
— L_BUYER_ID := 2519; — 2519;
OPEN C1;
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Cursor C1 Open’);
LOOP
FETCH C1
INTO WC1;
EXIT WHEN C1%NOTFOUND;
L_EXCH_RATE_TYPE := ”;
L_EXCH_RATE := ”;
L_EXCH_RATE_DATE := ”;
IF WC1.CURRENCY_CODE != ‘AED’
THEN
BEGIN
SELECT CONVERSION_RATE
INTO L_EXCH_RATE
FROM GL_DAILY_RATES
WHERE CONVERSION_DATE = ’31-AUG-2019′
AND TO_CURRENCY = ‘AED’
AND CONVERSION_TYPE = ‘Corporate’
AND FROM_CURRENCY = WC1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS
THEN
LV_ERR_MSG :=
LV_ERR_MSG
|| ‘, ‘
|| ‘Currency Conversion Rate is not exists’;
END;
L_EXCH_RATE_TYPE := ‘Corporate’;
L_EXCH_RATE_DATE := ’31-AUG-2019′;
END IF;
BEGIN
SELECT AGENT_ID
INTO L_BUYER_ID
FROM PER_ALL_PEOPLE_F PPF,PO_AGENTS PA
WHERE FULL_NAME = WC1.BUYER
AND PPF.PERSON_ID=PA.AGENT_ID
AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
EXCEPTION
WHEN OTHERS
THEN
–LN_TASK_ID:=-9999;
LV_ERR_FLAG := ‘Y’;
LV_ERR_MSG := ‘Buyer Invalid’ || ‘,’ || SQLERRM;
END;
SELECT PO_HEADERS_INTERFACE_S.NEXTVAL
INTO L_HEADER_ID
FROM DUAL;
FND_FILE.PUT_LINE (FND_FILE.LOG,
‘Enter Into PO Headers Interface’);
INSERT INTO PO_HEADERS_INTERFACE
(INTERFACE_HEADER_ID, DOCUMENT_TYPE_CODE, VENDOR_ID,
VENDOR_SITE_ID, CURRENCY_CODE,
APPROVAL_REQUIRED_FLAG, COMMENTS, ORG_ID, AGENT_ID,
SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID,
INTERFACE_SOURCE_CODE, BATCH_ID, APPROVAL_STATUS,
RATE_TYPE, RATE_DATE, RATE,
ACTION, CREATION_DATE, DOCUMENT_NUM, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
ATTRIBUTE15 — DATA MIGRATION REFERENCE
)
VALUES (L_HEADER_ID, ‘STANDARD’, WC1.VENDOR_ID,
WC1.VENDOR_SITE_ID, WC1.CURRENCY_CODE, –‘AED’,
‘N’, WC1.PO_DESCRIPTION, WC1.ORG_ID, L_BUYER_ID,
WC1.SHIP_LOCATION_ID, WC1.BILL_LOCATION_ID,
‘TARGETOPENPO’, 131201, ‘APPROVED’,
L_EXCH_RATE_TYPE, L_EXCH_RATE_DATE, L_EXCH_RATE,
‘ORIGINAL’, WC1.PO_DATE, WC1.PO_NUMBER, LV_USER_ID,
SYSDATE, LV_USER_ID,
‘Open PO Migration’
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
‘PO Number:’ || WC1.PO_NUMBER || ‘.’
);
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Open Cursor C2’);
BEGIN
SELECT TRIM (A.SHIP_TO_ORG_CODE)
INTO LN_PO_NUM
FROM XX_NEW_PO A,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
MTL_SYSTEM_ITEMS_B MSI,
— ORG_ORGANIZATION_DEFINITIONS OOD,
HR_ALL_ORGANIZATION_UNITS OOD,
GL_CODE_COMBINATIONS_KFV GCC,
HR_OPERATING_UNITS HOU
WHERE TRIM (A.PO_NUMBER) = WC1.PO_NUMBER
AND PV.SEGMENT1 = TRIM (A.SUPPLIER_NUMBER)
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND PVS.VENDOR_SITE_CODE = TRIM (A.SUPPLIER_SITE)
AND ROWNUM = 1
AND OOD.NAME = TRIM (A.SHIP_TO_ORG_CODE)
AND PVS.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.NAME = TRIM (REPLACE (A.OU_NAME, CHR (13), ‘ ‘))
AND GCC.CONCATENATED_SEGMENTS = TRIM (A.CHARGE_ACCOUNT)
AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSI.SEGMENT1 = TRIM (A.ITEM_CODE);
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘PONUM:’ || LN_PO_NUM);
EXCEPTION
WHEN OTHERS
THEN
/*FND_FILE.PUT_LINE (FND_FILE.LOG,
‘PONUM:’||TRIM (A.PO_NUMBER)
||’,SUPPLIER:’||TRIM (A.SUPPLIER_NUMBER)
||’,SUPPLIERSITE:’||TRIM (A.SUPPLIER_SITE)||’,ORGCODE:’|| TRIM (A.SHIP_TO_ORG_CODE)
||’,OU:’|| TRIM (REPLACE (A.OU_NAME, CHR (13), ‘ ‘))
||’,CGACCT:’|| TRIM (A.CHARGE_ACCOUNT)
||’,ITEM:’|| TRIM (A.ITEM_CODE)
);*/
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Error’ || SQLERRM);
END;
FOR I IN C2
LOOP
L_LINE_NO := NVL (L_LINE_NO, 0) + 1;
—
L_LINE_CNT := 0;
L_DESC := NULL;
—
BEGIN
SELECT COUNT (*)
INTO L_LINE_CNT
FROM XX_NEW_PO NP, HR_OPERATING_UNITS ORG
WHERE ORG.NAME = NP.OU_NAME
AND TRIM (NP.PO_NUMBER) = WC1.PO_NUMBER;
EXCEPTION
WHEN OTHERS
THEN
L_LINE_CNT := 0;
END;
—
IF (L_LINE_CNT = 1)
THEN
L_DESC := NULL;
ELSIF (L_LINE_CNT > 1)
THEN
L_DESC := I.PO_DESCRIPTION;
END IF;
—
SELECT PO_LINES_INTERFACE_S.NEXTVAL
INTO L_LINE_ID
FROM DUAL;
SELECT PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
INTO L_DIST_ID
FROM DUAL;
FND_FILE.PUT_LINE (FND_FILE.LOG,
‘Enter Into PO Lines Interface’
);
INSERT INTO PO_LINES_INTERFACE
(INTERFACE_LINE_ID, INTERFACE_HEADER_ID,
LINE_TYPE_ID, ITEM_ID, ITEM_DESCRIPTION,
UNIT_PRICE, UOM_CODE, QUANTITY,
SHIP_TO_LOCATION_ID, SHIPMENT_NUM, NEED_BY_DATE,
–PROMISED_DATE,
LINE_NUM, SHIP_TO_ORGANIZATION_ID,
LINE_ATTRIBUTE15, ACCRUE_ON_RECEIPT_FLAG,
TAXABLE_FLAG, LINE_ATTRIBUTE14, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
NOTE_TO_VENDOR
)
VALUES (L_LINE_ID, L_HEADER_ID,
1, I.INVENTORY_ITEM_ID, I.DESCRIPTION,
I.RATE, I.PRIMARY_UOM_CODE, I.QUANTITY,
WC1.SHIP_LOCATION_ID, 1, I.NEED_BY_DATE,
I.PO_LINE_NO,
–L_LINE_NO,
I.ORGANIZATION_ID,
I.ROWNUMBER, ‘Y’,
‘Y’, ‘TARGETOPENPO’, SYSDATE,
LV_USER_ID, SYSDATE, LV_USER_ID,
L_DESC
–I.PO_DESCRIPTION
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
‘Enter Into PO Distributions Interface’
);
IF NVL (I.PROJECT_CODE, ‘X’) = ‘X’
THEN
BEGIN
FND_FILE.PUT_LINE
(FND_FILE.LOG,
‘Enter Into PO Distributions Interface->1’
);
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_LINE_ID, INTERFACE_HEADER_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM, QUANTITY_ORDERED, ORG_ID,
CHARGE_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID,
DELIVER_TO_PERSON_ID,
ATTRIBUTE15, ACCRUE_ON_RECEIPT_FLAG,
ACCRUED_FLAG, GL_ENCUMBERED_DATE,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_DATE, LAST_UPDATED_BY
)
VALUES (L_LINE_ID, L_HEADER_ID,
L_DIST_ID,
1, I.QUANTITY, I.ORG_ID,
I.CHARGE_ACCOUNT_ID,
I.ACCRUAL_ACCOUNT_ID,
L_BUYER_ID,
I.ROWNUMBER, ‘Y’,
‘N’, I.GL_DATE,
SYSDATE, LV_USER_ID,
SYSDATE, LV_USER_ID
);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
END;
ELSE
FND_FILE.PUT_LINE
(FND_FILE.LOG,
‘Enter Into PO Distributions Interface->2’
);
BEGIN
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_LINE_ID, INTERFACE_HEADER_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM, QUANTITY_ORDERED,
ORG_ID, CHARGE_ACCOUNT_ID,
ACCRUAL_ACCOUNT_ID, DELIVER_TO_PERSON_ID,
ATTRIBUTE15, ACCRUE_ON_RECEIPT_FLAG,
ACCRUED_FLAG, GL_ENCUMBERED_DATE,
PROJECT_ID, TASK_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ORGANIZATION_ID,
EXPENDITURE_ITEM_DATE,
PROJECT_ACCOUNTING_CONTEXT, CREATION_DATE,
CREATED_BY, LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES (L_LINE_ID, L_HEADER_ID,
L_DIST_ID,
1, I.QUANTITY,
I.ORG_ID, I.CHARGE_ACCOUNT_ID,
I.ACCRUAL_ACCOUNT_ID, L_BUYER_ID,
I.ROWNUMBER, ‘Y’,
‘N’, I.GL_DATE,
I.PROJECT_ID, I.TASK_ID,
I.EXPENDITURE_TYPE,
I.ORG_ID,
I.GL_DATE,
‘Y’, SYSDATE,
LV_USER_ID, SYSDATE,
LV_USER_ID
);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
END;
END IF;
UPDATE XX_NEW_PO
SET STATUS = ‘Sucess’
WHERE PO_NUMBER = I.PO_NUMBER AND ROWNUMBER = I.ROWNUMBER;
COMMIT;
END LOOP;
END LOOP;
CLOSE C1;
ELSE
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Validation Error’);
END IF;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLERRM);
COMMIT;
END XX_OPEN_GRN_PO;
END XX_OPENPO_PKG;
/
