/* Formatted on 10/26/2017 4:06:50 PM (QP5 v5.115.810.9015) */
CREATE OR REPLACE PROCEDURE AP_INTERFACING (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2)
IS
V_INVOICE_ID AP_INVOICES_ALL.INVOICE_ID%TYPE;
V_INVOICE_LINE_ID AP_INVOICES_ALL.INVOICE_ID%TYPE;
V_VENDOR_SITE_ID PO_VENDOR_SITES_ALL.VENDOR_SITE_ID%TYPE;
V_INVOICE_NUM AP_INVOICES_ALL.INVOICE_NUM%TYPE;
V_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
ERROR_FLAG VARCHAR2 (10) := 'N';
PAYMENT_MENTHOD AP_LOOKUP_CODES.LOOKUP_CODE%TYPE;
V_COMBINATION_ID GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
V_INVOICE_LINE_ID VARCHAR2 (20);
V_LAST_DATE DATE;
V_VENDOR_ID PO_VENDORS.VENDOR_ID%TYPE;
V_NUM NUMBER (10);
V_COUNT NUMBER (10);
V_VENDOR VARCHAR2 (50);
CURSOR AP_CUR
IS
SELECT A.INVOICE_NUM,
A.VENDOR_NAME,
A.VENDOR_ID,
A.INVOICE_CURRENCY_CODE,
A.SOURCE, --SELECT * FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE LIKE 'SOURCE'
A.GROUP_ID,
A.INVOICE_AMOUNT,
A.PROCESS_FLAG,
A.VENDOR_SITE_ID,
A.PAYMENT_METHOD,
A.CODE_COMB,
A.LOD_ID
FROM AP_INVOICES_STG_TBL A
WHERE PROCESS_FLAG = 'Y';
BEGIN
FOR AP_REC IN AP_CUR
LOOP
SELECT AP_INVOICES_INTERFACE_S.NEXTVAL INTO V_INVOICE_ID FROM DUAL;
V_NUM := V_NUM + 1;
IF AP_REC.INVOICE_AMOUNT > 0
THEN
SELECT DISTINCT (VENDOR_NAME)
INTO V_VENDOR
FROM AP_INVOICES_STG_TBL
WHERE VENDOR_NAME = AP_REC.VENDOR_NAME;
/* VALIDATING VENDOR ID */
BEGIN
SELECT DISTINCT (VENDOR_ID)
INTO V_VENDOR_ID --VALUE FOR VENDOR_ID
FROM PO_VENDORS
WHERE UPPER (VENDOR_NAME) = UPPER (V_VENDOR);
--AND SYSDATE >= START_DATE_ACTIVE;
-- AND SYSDATE < END_DATE_ACTIVE;--DEPENDS UPON OUR REQUIREMENT
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'NO VENDOR ID FOUND OR VENDOR STATUS IS NOT ACTIVE ='
|| AP_REC.VENDOR_NAME
|| V_NUM
);
ERROR_FLAG := 'Y';
CONTINUE;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'EXCEPTION' || SQLCODE || SQLERRM);
ERROR_FLAG := 'Y';
CONTINUE;
END;
/* VALIDATE INVOICE NUMBER IF NULL THEN ASSIGN INVOICE NUMBER EQUAL TO WEEK_END_DATE.*/
SELECT INVOICE_NUM
INTO V_INVOICE_NUM
FROM AP_INVOICES_STG_TBL
WHERE INVOICE_NUM = AP_REC.INVOICE_NUM;
IF V_INVOICE_NUM IS NULL
THEN
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') INTO V_LAST_DATE FROM DUAL;
V_INVOICE_NUM := V_LAST_DATE; --VALUE FOR INVOICE NUMBER
END IF;
/* VALIDATION FOR VENDOR_SITE_ID */
BEGIN
SELECT DISTINCT (VENDOR_SITE_ID)
INTO V_VENDOR_SITE_ID
FROM PO_VENDOR_SITES_ALL
WHERE VENDOR_SITE_ID = AP_REC.VENDOR_SITE_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'NO VENDOR SITE CODE FOUND FOR THIS VENDOR ID - '
|| V_VENDOR_ID
|| V_NUM
);
ERROR_FLAG := 'Y';
CONTINUE;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'EXCEPTION' || SQLCODE || SQLERRM);
ERROR_FLAG := 'Y';
CONTINUE;
END;
/* INVOICE CURRENCY CODE */
BEGIN
SELECT DISTINCT (CURRENCY_CODE)
INTO V_CURRENCY_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE =
LTRIM (RTRIM (UPPER (AP_REC.INVOICE_CURRENCY_CODE)))
AND SYSDATE BETWEEN NVL (START_DATE_ACTIVE, SYSDATE)
AND NVL (END_DATE_ACTIVE, SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'CURRENCY CODE DOES NOT EXIST OR NOT VALID FOR THE VENDOR '
|| AP_REC.VENDOR_NAME
|| V_NUM
);
ERROR_FLAG := 'Y';
CONTINUE;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'EXCEPTION' || SQLCODE || SQLERRM);
ERROR_FLAG := 'Y';
CONTINUE;
END;
--VALIDATING THE INVOICE NUMBER DUPLICATES IF WE HAVE ANY
SELECT COUNT ( * )
INTO V_COUNT
FROM AP_INVOICES_ALL
WHERE INVOICE_NUM = V_INVOICE_NUM AND VENDOR_ID = V_VENDOR_ID;
IF V_COUNT > 0
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
' INVOCIE ALREADY EXISTS - ' || V_INVOICE_NUM);
ERROR_FLAG := 'Y';
END IF;
--VALIDATING THE PAYMENT METHOD THROUGH AP_LOOKUP_CODES SELECT * FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE LIKE 'PAYMENT_METHOD' WHERE WE GET THE LOOKUP_CODES
BEGIN
SELECT DISTINCT (LOOKUP_CODE)
INTO PAYMENT_MENTHOD
FROM AP_LOOKUP_CODES
WHERE LOOKUP_CODE = AP_REC.PAYMENT_METHOD;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
' PAYMENT METHOD DOES NOT EXIST PLEASE CHECK THE PAYMENT METHOD FOR THE VENDOR'
|| AP_REC.PAYMENT_METHOD
|| V_NUM
);
ERROR_FLAG := 'Y';
CONTINUE;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Exception' || SQLCODE || SQLERRM);
ERROR_FLAG := 'Y';
CONTINUE;
END;
--WE NEED TO VALIDATE EVEN THE GL CODE COMBINATIONS ID FOR THE ABOVE RECORDS.
BEGIN
SELECT DISTINCT (CODE_COMBINATION_ID)
INTO V_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE CODE_COMBINATION_ID = AP_REC.CODE_COMB;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'GL CODE COMBINATION DOESNOT EXSISTS FOR THE ABOVE RECORD PLEASE VERIFY THE CODE COMBINATION ID'
|| V_NUM
);
ERROR_FLAG := 'Y';
CONTINUE;
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'Exception' || SQLCODE || SQLERRM);
ERROR_FLAG := 'Y';
CONTINUE;
END;
ELSE
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'THE INVOICE AMOUNT IS MISSING OR DOESNOT EXISTS FOR THE ABOVE TRANSACTIONS OR RECORDS'
|| V_NUM
);
ERROR_FLAG := 'Y';
END IF;
IF (ERROR_FLAG = 'N')
THEN
--INSERTING VALIDATED VALUES TO INTERFACE TABLE(HEADERS)
INSERT INTO AP_INVOICES_INTERFACE (INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
VENDOR_ID,
VENDOR_NAME,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
SOURCE,
PAYMENT_METHOD_LOOKUP_CODE,
ACCTS_PAY_CODE_COMBINATION_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (V_INVOICE_ID,
AP_REC.INVOICE_NUM,
'STANDARD',
SYSDATE,
V_VENDOR_ID,
AP_REC.VENDOR_NAME,
V_VENDOR_SITE_ID,
AP_REC.INVOICE_AMOUNT,
V_CURRENCY_CODE,
AP_REC.SOURCE,
AP_REC.PAYMENT_METHOD,
V_COMBINATION_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
--STARTS HERE TO PROCESS INVOICE LINES DATA, FETCHING SEQUENCE VALUE FOR INVOICE_LINE_ID
--SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL INTO V_INVOICE_LINE_ID FROM DUAL;
INSERT INTO AP_INVOICE_LINES_INTERFACE (INVOICE_ID,
INVOICE_LINE_ID,
LINE_NUMBER,
LINE_TYPE_LOOKUP_CODE,
AMOUNT,
DIST_CODE_COMBINATION_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (V_INVOICE_ID,
AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
1,
'ITEM',
AP_REC.INVOICE_AMOUNT,
AP_REC.CODE_COMB,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
UPDATE AP_INVOICES_STG_TBL
SET PROCESS_FLAG = 'N'
WHERE LOD_ID = AP_REC.LOD_ID;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, SQLCODE || SQLERRM);
ROLLBACK;
END;
/
Always Make Sure that before creating the developing the interface confirm whether the suppliers are assigned to the concerned organisation and
Also make sure that org_id in the interface tables exists and also check the code combination that we are assigining should be matching with the liablity account that we are using to that concerned organization
for more information please see the below Screen Shots.
Then go to GL_CODE_COMBINATIONS TABLE and serach for that particular account and use that code combination.
Then once the data is in the interface tables then run the standard open interface program import
Make sure that the parameters that we pass in concurrent program should match the org id and group.
The the effected tables are:
select * from AP_invoices_all
select * from AP_invoice_lines_all
select * from AP_Invoice_distributions_all
No comments:
Post a Comment