CREATE OR REPLACE PROCEDURE XXIPZ_AUTOINVOICE_PROC( ERRBUFF OUT VARCHAR2, RETCODE OUT NUMBER, P_ORG_ID IN NUMBER, P_INTERFACE_LINE_CONTEXT IN VARCHAR2, P_BATCH_SOURCE_NAME IN VARCHAR2, P_GL_DATE IN DATE , P_CUST_TRX_TYPE_NAME_I IN VARCHAR2 ) IS FLS_USER_EXCEPTION EXCEPTION; FLS_ERR_LOC NUMBER := 0; FLS_ERROR_TEXT VARCHAR2(360); FLS_INTERFACE_LINE_CONTEXT VARCHAR2(30) := P_INTERFACE_LINE_CONTEXT ; FLS_BATCH_SOURCE_NAME VARCHAR2(50) := P_BATCH_SOURCE_NAME; FLS_INTERFACE_LINE_ATTRIBUTE1 VARCHAR2(30); FLS_INTERFACE_LINE_ATTRIBUTE2 VARCHAR2(30) := '1'; FLS_LINE_TYPE VARCHAR2(20) := 'LINE'; FLS_GL_DATE DATE := TO_DATE(P_GL_DATE); FLS_TRX_DATE DATE; FLS_DESCRIPTION VARCHAR2(240); FLS_AMOUNT NUMBER; FLS_CURRENCY_CODE VARCHAR2(15); FLS_CONVERSION_TYPE VARCHAR2(30); FLS_CONVERSION_RATE NUMBER; FLS_TERM_NAME VARCHAR2(15); FLS_UNIT_SELLING_PRICE NUMBER; FLS_QUANTITY_ORDERED NUMBER := 1; FLS_SALES_ORDER VARCHAR2(50); FLS_SALES_ORDER_DATE DATE; FLS_PRIMARY_SALESREP_NUMBER VARCHAR2(30); FLS_SHIP_TO_CUSTOMER_NAME VARCHAR2(240) ; FLS_BILL_TO_CUSTOMER_NAME VARCHAR2(240) ; FLS_ORIG_BILL_CUSTOMER_REF VARCHAR2(240); FLS_ORIG_SHIP_CUSTOMER_REF VARCHAR2(240); FLS_TAX_CODE VARCHAR2(50); FLS_ACCOUNT_CLASS VARCHAR2(30); FLS_PERCENT NUMBER(15) ; FLS_CODE_COMBINATION_ID NUMBER(15) ; FLS_ORG_ID NUMBER; FLS_SET_OF_BOOKS_ID NUMBER; FLS_BASE_CURRENCY VARCHAR2(50); FLS_AUTO_TRX_NUMBER_FLAG VARCHAR2(50); FLS_CUST_TRX_TYPE_NAME_I VARCHAR2(30) := P_CUST_TRX_TYPE_NAME_I; --FLS_CUST_TRX_TYPE_NAME_C VARCHAR2(30) := P_CUST_TRX_TYPE_NAME_C; FLS_ORIG_BILL_ADDRESS_ID NUMBER; FLS_ORIG_SHIP_ADDRESS_ID NUMBER; FLS_ORIG_BILL_ADDRESS_REF VARCHAR2(50); FLS_ORIG_SHIP_ADDRESS_REF VARCHAR2(50); FLS_CREATED_BY VARCHAR2(100); FLS_CREATION_DATE DATE; FLS_LAST_UPDATED_BY VARCHAR2(100); FLS_LAST_UPDATE_DATE DATE; FLS_LAST_UPDATE_LOGIN VARCHAR2(100); FLS_AMOUNT_INCLUDES_TAX_FLAG VARCHAR2(1); L_TERM_NAME NUMBER; L_COUNT_CURRENCY_CODE1 NUMBER; L_COUNT_CURRENCY_CODE2 NUMBER; L_CONVERSION_TYPE NUMBER; L_TAX_CODE NUMBER; L_PRIMARY_SALESREP_NUMBER NUMBER; L_ORIG_BILL_CUSTOMER_REF NUMBER; L_ORIG_BILL_ADDRESS_REF NUMBER; L_ORIG_SHIP_CUSTOMER_REF NUMBER; L_ORIG_SHIP_ADDRESS_REF NUMBER; TOTAL_COUNT NUMBER := 0; LINE_COUNT NUMBER := 0; REJECTED_COUNT NUMBER := 0; CURSOR C_LINE_DUPLICATE IS SELECT INTERFACE_LINE_ATTRIBUTE1 FROM XX_RA_AUTOINVOICE_STG RAIS_1 WHERE ROWID > ( SELECT MIN (ROWID) FROM XX_RA_AUTOINVOICE_STG RAIS_2 WHERE RAIS_1.INTERFACE_LINE_ATTRIBUTE1 = RAIS_2.INTERFACE_LINE_ATTRIBUTE1 ); CURSOR C_RA_INTERFACE_LINES IS SELECT ERROR_FLAG , ERROR_MESSAGE , INTERFACE_LINE_CONTEXT , INTERFACE_LINE_ATTRIBUTE1 , INTERFACE_LINE_ATTRIBUTE2 , BATCH_SOURCE_NAME , LINE_TYPE , TRX_NUMBER , TRX_DATE , DESCRIPTION , AMOUNT , CURRENCY_CODE , CONVERSION_TYPE , CONVERSION_RATE , TERM_NAME , UNIT_SELLING_PRICE , QUANTITY_ORDERED , SALES_ORDER , SALES_ORDER_DATE , PRIMARY_SALESREP_NUMBER , SHIP_TO_CUSTOMER_NAME , BILL_TO_CUSTOMER_NAME , TAX_CODE , ORIG_SYSTEM_SHIP_ADDRESS_REF , ORIG_SYSTEM_BILL_ADDRESS_REF , D_ACCOUNT_CLASS , D_CODE_COMBINATION_ID , D_AMOUNT FROM XX_RA_AUTOINVOICE_STG WHERE ERROR_FLAG = 'N' AND PROCESS_FLAG= 'N' ; -- FRIL C_RA_INTERFACE_LINES%ROWTYPE; -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- BEGIN FND_FILE.NEW_LINE (FND_FILE.LOG, 2); FND_FILE.PUT_LINE (FND_FILE.LOG, 'FLOWSERVE INVOICE INTERFACE EXTRACT PROCESS FOR ORG ID '|| P_ORG_ID ); FND_FILE.PUT_LINE (FND_FILE.LOG,'**********************************************************************'); FLS_ERR_LOC := 10; -- DUPLICATE RECORD ERROR_FLAG=Y FOR DUPLICATE IN C_LINE_DUPLICATE LOOP IF C_LINE_DUPLICATE%FOUND THEN UPDATE XX_RA_AUTOINVOICE_STG SET ERROR_FLAG = 'Y' , ERROR_MESSAGE = 'DUPLICATE INVOICE NUMBER(INTERFACE_LINE_ATTRIBUTE1)' WHERE INTERFACE_LINE_ATTRIBUTE1 = DUPLICATE.INTERFACE_LINE_ATTRIBUTE1; FLS_ERROR_TEXT := 'DUPLICATE INVOICE NUMBER IS NOT ALLOWED ' || DUPLICATE.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END IF; END LOOP; FLS_ERR_LOC := 15; -- ORGANIZATION ID VALIDATION IF P_ORG_ID IS NULL THEN FLS_ERROR_TEXT := 'ORG ID IS NULL ' || P_ORG_ID; RAISE FLS_USER_EXCEPTION; ELSE FLS_ORG_ID := P_ORG_ID; END IF; FLS_ERR_LOC := 25; -- SET SET OF BOOKS ID FOR THE ORGANIZATION BEGIN SELECT SET_OF_BOOKS_ID INTO FLS_SET_OF_BOOKS_ID FROM AR_SYSTEM_PARAMETERS_ALL WHERE ORG_ID = P_ORG_ID; IF FLS_SET_OF_BOOKS_ID IS NULL THEN FLS_ERROR_TEXT := 'SET OF BOOKS ID IS NULL FOR ORGANIZATION=' || TO_CHAR (P_ORG_ID); RAISE FLS_USER_EXCEPTION; END IF; DBMS_OUTPUT.PUT_LINE(' SET UP BOOK ID'||FLS_SET_OF_BOOKS_ID); EXCEPTION WHEN NO_DATA_FOUND THEN FLS_ERROR_TEXT := 'SET OF BOOKS ID IS NOT AVAILABLE FOR ORGANIZATION='|| TO_CHAR (P_ORG_ID); RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 30; -- GET BASE CURRENCY FOR THE SET OF BOOKS ID BEGIN SELECT CURRENCY_CODE INTO FLS_BASE_CURRENCY FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID = FLS_SET_OF_BOOKS_ID; IF FLS_BASE_CURRENCY IS NULL THEN FLS_ERROR_TEXT := 'AR BASE CURRENCY CODE IS NULL FOR SET_OF_BOOKS_ID=' || TO_CHAR (FLS_SET_OF_BOOKS_ID); RAISE FLS_USER_EXCEPTION; END IF; DBMS_OUTPUT.PUT_LINE(' CURRENCY CODE--'||FLS_BASE_CURRENCY); EXCEPTION WHEN NO_DATA_FOUND THEN FLS_ERROR_TEXT := 'AR BASE CURRENCY CODE IS NOT AVAILABLE FOR SET_OF_BOOKS_ID=' || TO_CHAR (FLS_SET_OF_BOOKS_ID); RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 35; TOTAL_COUNT := 0; LINE_COUNT := 0; REJECTED_COUNT := 0; FOR FRIL IN C_RA_INTERFACE_LINES LOOP BEGIN TOTAL_COUNT := C_RA_INTERFACE_LINES%ROWCOUNT; DBMS_OUTPUT.PUT_LINE(' TOTOAL COUNT--'||TOTAL_COUNT); -- DESCRIPTION VALIDATION IF FRIL.DESCRIPTION IS NULL THEN FLS_ERROR_TEXT := 'DESCRIPTION IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_DESCRIPTION := FRIL.DESCRIPTION; END IF; FLS_ERR_LOC := 45; -- CURRENCY VALIDATION IF FRIL.CURRENCY_CODE IS NULL THEN FLS_ERROR_TEXT := 'CURRENCY IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE SELECT COUNT(1) INTO L_COUNT_CURRENCY_CODE1 FROM FND_CURRENCIES WHERE CURRENCY_CODE = FRIL.CURRENCY_CODE; IF L_COUNT_CURRENCY_CODE1 < 1 THEN FLS_ERROR_TEXT := 'CURRENCY IS NOT DEFINED IN "FND_CURRENCIES" FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_CURRENCY_CODE := FRIL.CURRENCY_CODE; END IF; END IF; DBMS_OUTPUT.PUT_LINE(' currency code--'|| L_COUNT_CURRENCY_CODE1); FLS_ERR_LOC := 50; -- CONVERSION TYPE VALIDATION IF FRIL.CURRENCY_CODE = FLS_BASE_CURRENCY THEN FLS_CONVERSION_TYPE := 'User'; FLS_CONVERSION_RATE := 1; ELSE IF FRIL.CONVERSION_TYPE IS NULL THEN FLS_ERROR_TEXT := 'CONVERSION TYPE IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE SELECT COUNT(1) INTO L_CONVERSION_TYPE FROM GL_DAILY_CONVERSION_TYPES WHERE CONVERSION_TYPE = FRIL.CONVERSION_TYPE ; FLS_ERR_LOC := 55; IF L_CONVERSION_TYPE < 1 THEN FLS_ERROR_TEXT := 'CONVERSION TYPE IS NOT DEFINED IN "GL_DAILY_CONVERSION_TYPES" FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE IF FLS_CONVERSION_TYPE = 'User' THEN IF FRIL.CONVERSION_RATE IS NULL THEN FLS_ERROR_TEXT := 'CONVERSION RATE CAN NOT BE NULL FOR USER CONVERSION TYPE FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_CONVERSION_TYPE := FRIL.CONVERSION_TYPE; FLS_CONVERSION_RATE := FRIL.CONVERSION_RATE; END IF; ELSE FLS_CONVERSION_TYPE := FRIL.CONVERSION_TYPE; FLS_CONVERSION_RATE := NULL; END IF; END IF; END IF; END IF; DBMS_OUTPUT.PUT_LINE(' conversion type--'|| L_CONVERSION_TYPE); FLS_ERR_LOC := 60; -- TERM NAME VALIDATION SELECT COUNT(1) INTO L_TERM_NAME FROM RA_TERMS WHERE NAME = FRIL.TERM_NAME; IF L_TERM_NAME < 1 THEN FLS_ERROR_TEXT := 'TERM NAME IS NOT DEFINED IN "RA_TERMS" FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_TERM_NAME := FRIL.TERM_NAME; END IF; DBMS_OUTPUT.PUT_LINE('term name--'|| L_TERM_NAME); FLS_ERR_LOC := 65; /* -- TAX CODE VALIDATION SELECT COUNT(1) INTO L_TAX_CODE FROM AR_VAT_TAX WHERE TAX_CODE = FRIL.TAX_CODE; IF L_TAX_CODE > 0 THEN FLS_TAX_CODE := FRIL.TAX_CODE; ELSE FLS_ERROR_TEXT := 'TAX CODE IS NOT DEFINED IN "RA_VAT_TAX" FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END IF;*/ /* ELSE FLS_TAX_CODE := FRIL.TAX_CODE; SELECT AMOUNT_INCLUDES_TAX_OVERRIDE INTO FLS_AMOUNT_INCLUDES_TAX_FLAG FROM AR_VAT_TAX WHERE TAX_CODE= FRIL.TAX_CODE ; */ DBMS_OUTPUT.PUT_LINE('tax code--'|| L_TAX_CODE); FLS_ERR_LOC := 70; -- SALESREP NUMBER VALIDATION BEGIN SELECT SALESREP_NUMBER INTO FLS_PRIMARY_SALESREP_NUMBER FROM RA_SALESREPS WHERE NAME = FRIL.PRIMARY_SALESREP_NUMBER ; DBMS_OUTPUT.PUT_LINE(' SALESREP'||FLS_PRIMARY_SALESREP_NUMBER); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN RAISE FLS_USER_EXCEPTION; END; DBMS_OUTPUT.PUT_LINE('salesrep number--'||FLS_PRIMARY_SALESREP_NUMBER); FLS_ERR_LOC := 75; -- ORIG_SYSTEM_BILL_CUSTOMER_REF VALIDATION IF FRIL.BILL_TO_CUSTOMER_NAME IS NULL THEN FLS_ERROR_TEXT := 'BILL TO CUSTOMER NAME IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE SELECT COUNT(1) INTO L_ORIG_BILL_CUSTOMER_REF FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.BILL_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A' ; IF L_ORIG_BILL_CUSTOMER_REF > 0 THEN BEGIN SELECT HCA.ORIG_SYSTEM_REFERENCE INTO FLS_ORIG_BILL_CUSTOMER_REF FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.BILL_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A'; DBMS_OUTPUT.PUT_LINE('cust ref --'|| L_ORIG_BILL_CUSTOMER_REF); EXCEPTION WHEN TOO_MANY_ROWS THEN FLS_ERROR_TEXT := 'BILL TO CUSTOMER NAME HAVE MANY PARTY_ID FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' BILL_TO_CUSTOMER_NAME ' || FRIL.BILL_TO_CUSTOMER_NAME; RAISE FLS_USER_EXCEPTION; WHEN OTHERS THEN FLS_ERROR_TEXT := 'BILL TO CUSTOMER NAME HAVE ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' BILL_TO_CUSTOMER_NAME ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 76; --GETTING ORIG_SYSTEM_BILL_ADDRESS_REF FOR GIVEN CUSTOMER BEGIN SELECT HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE INTO FLS_ORIG_BILL_ADDRESS_REF FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE IN (SELECT HCA.ORIG_SYSTEM_REFERENCE FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.BILL_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A')) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'BILL_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; DBMS_OUTPUT.PUT_LINE('bill to addr --'||FLS_ORIG_BILL_ADDRESS_REF); EXCEPTION WHEN NO_DATA_FOUND THEN FLS_ERROR_TEXT := 'CUSTOMER DO NOT HAVE BILL TO ADDRESS FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || ' CUSTOMER NAME IS ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; WHEN TOO_MANY_ROWS THEN FLS_ERROR_TEXT := 'CUSTOMER HAVE MORE THEN ONE BILL TO ADDRESS FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || ' CUSTOMER NAME IS ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; WHEN OTHERS THEN FLS_ERROR_TEXT := 'UNKNOWN CUSTOMER ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || ' CUSTOMER NAME IS ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 77; -- GETTING ORIG_SYSTEM_BILL_ADDRESS_ID FOR GIVEN CUSTOMER BEGIN SELECT COUNT(1) INTO L_ORIG_BILL_ADDRESS_REF FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE = FLS_ORIG_BILL_CUSTOMER_REF ) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'BILL_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID -- AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FRIL.ORIG_SYSTEM_BILL_ADDRESS_REF AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_BILL_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_BILL_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; FLS_ERR_LOC := 80; IF L_ORIG_BILL_ADDRESS_REF > 0 THEN BEGIN SELECT HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID INTO FLS_ORIG_BILL_ADDRESS_ID FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE = FLS_ORIG_BILL_CUSTOMER_REF ) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'BILL_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_BILL_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; DBMS_OUTPUT.PUT_LINE('bill to addr id --'||FLS_ORIG_BILL_ADDRESS_ID); EXCEPTION WHEN OTHERS THEN FLS_ERROR_TEXT := 'BILL TO CUSTOMER ADDRESS REFERENCE ID HAVE ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END; ELSE FLS_ERROR_TEXT := 'BILL TO CUSTOMER ADDRESS REFERENCE IS NOT PRESENT FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END IF; EXCEPTION WHEN OTHERS THEN FLS_ERROR_TEXT := 'UNKNOWN CUSTOMER ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || 'cUSTOMER NAME IS ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; ELSE FLS_ERR_LOC := 84; FLS_ERROR_TEXT := 'BILL TO CUSTOMER REFERENCE IS NOT PRESENT IN HZ_PARTIES FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' BILL_TO CUSTOMER NAME ' || FRIL.BILL_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END IF; -- L_ORIG_BILL_CUSTOMER_REF > 0 END IF; --FRIL.BILL_TO_CUSTOMER_NAME IS NULL FLS_ERR_LOC := 85; -- ORIG_SYSTEM_SHIP_CUSTOMER_REF VALIDATION IF FRIL.SHIP_TO_CUSTOMER_NAME IS NULL THEN FLS_ERROR_TEXT := 'SHIP TO CUSTOMER NAME IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE SELECT COUNT(1) INTO L_ORIG_SHIP_CUSTOMER_REF FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.SHIP_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A' ; IF L_ORIG_SHIP_CUSTOMER_REF > 0 THEN BEGIN SELECT HCA.ORIG_SYSTEM_REFERENCE INTO FLS_ORIG_SHIP_CUSTOMER_REF FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.SHIP_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A'; EXCEPTION WHEN TOO_MANY_ROWS THEN FLS_ERROR_TEXT := 'SHIP TO CUSTOMER NAME HAVE MANY PARTY_ID FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' SHIP_TO_CUSTOMER_NAME ' || FRIL.SHIP_TO_CUSTOMER_NAME; RAISE FLS_USER_EXCEPTION; WHEN OTHERS THEN FLS_ERROR_TEXT := 'SHIP TO CUSTOMER NAME HAVE ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' SHIP_TO_CUSTOMER_NAME ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 90; --GETTING ORIG_SYSTEM_SHIP_ADDRESS_REF FOR GIVEN CUSTOMER BEGIN SELECT HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE INTO FLS_ORIG_SHIP_ADDRESS_REF FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE IN (SELECT HCA.ORIG_SYSTEM_REFERENCE FROM HZ_CUST_ACCOUNTS HCA , HZ_PARTIES HP WHERE HP.PARTY_ID = HCA.PARTY_ID AND HP.PARTY_NAME = FRIL.SHIP_TO_CUSTOMER_NAME AND HP.STATUS = 'A' AND hca.status ='A')) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'SHIP_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; EXCEPTION WHEN NO_DATA_FOUND THEN FLS_ERROR_TEXT := 'CUSTOMER DO NOT HAVE SHIP TO ADDRESS FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || 'cUSTOMER NAME IS ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; WHEN TOO_MANY_ROWS THEN FLS_ERROR_TEXT := 'CUSTOMER HAVE MORE THEN ONE SHIP TO ADDRESS FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || ' CUSTOMER NAME IS ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; WHEN OTHERS THEN FLS_ERROR_TEXT := 'UNKNOWN CUSTOMER ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || 'cUSTOMER NAME IS ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; FLS_ERR_LOC := 95; -- GETTING ORIG_SYSTEM_SHIP_ADDRESS_ID FOR GIVEN CUSTOMER BEGIN SELECT COUNT(1) INTO L_ORIG_SHIP_ADDRESS_REF FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE = FLS_ORIG_SHIP_CUSTOMER_REF ) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'SHIP_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID -- AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FRIL.ORIG_SYSTEM_SHIP_ADDRESS_REF AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_SHIP_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_SHIP_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; FLS_ERR_LOC := 100; IF L_ORIG_SHIP_ADDRESS_REF > 0 THEN BEGIN SELECT HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID INTO FLS_ORIG_SHIP_ADDRESS_ID FROM HZ_CUST_ACCOUNTS , HZ_CUST_ACCT_SITES_ALL , HZ_CUST_SITE_USES_ALL , HZ_PARTY_SITES WHERE HZ_CUST_ACCOUNTS. CUST_ACCOUNT_ID = ( SELECT CUST_ACCOUNT_ID FROM HZ_CUST_ACCOUNTS WHERE ORIG_SYSTEM_REFERENCE = FLS_ORIG_SHIP_CUSTOMER_REF ) AND HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES_ALL.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID = HZ_CUST_SITE_USES_ALL.CUST_ACCT_SITE_ID AND HZ_CUST_SITE_USES_ALL.SITE_USE_CODE = 'SHIP_TO' AND HZ_CUST_ACCT_SITES_ALL.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID AND HZ_PARTY_SITES.ORIG_SYSTEM_REFERENCE = FLS_ORIG_SHIP_ADDRESS_REF AND HZ_CUST_SITE_USES_ALL.ORG_ID = P_ORG_ID AND HZ_PARTY_SITES.STATUS = 'A' AND HZ_CUST_ACCOUNTS.STATUS = 'A' AND HZ_CUST_SITE_USES_ALL.PRIMARY_FLAG = 'Y'; DBMS_OUTPUT.PUT_LINE('ship addr id --'||FLS_ORIG_SHIP_ADDRESS_ID); EXCEPTION WHEN OTHERS THEN FLS_ERROR_TEXT := 'SHIP TO CUSTOMER ADDRESS REFERENCE ID HAVE ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END; ELSE FLS_ERROR_TEXT := 'SHIP TO CUSTOMER ADDRESS REFERENCE IS NOT PRESENT FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; END IF; EXCEPTION WHEN OTHERS THEN FLS_ERROR_TEXT := 'UNKNOWN CUSTOMER ERROR FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 || 'cUSTOMER NAME IS ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END; ELSE FLS_ERR_LOC := 105; FLS_ERROR_TEXT := 'SHIP TO CUSTOMER REFERENCE IS NOT PRESENT IN HZ_PARTIES FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ||' SHIP_TO CUSTOMER NAME ' || FRIL.SHIP_TO_CUSTOMER_NAME ; RAISE FLS_USER_EXCEPTION; END IF; -- L_ORIG_SHIP_CUSTOMER_REF > 0 END IF; --FRIL.SHIP_TO_CUSTOMER_NAME IS NULL FLS_ERR_LOC := 110; -- ACCOUNT CLASS VALIDATION IF FRIL.D_ACCOUNT_CLASS IS NULL THEN FLS_ERROR_TEXT := 'ACCOUNT CLASS IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE IF FRIL.D_ACCOUNT_CLASS NOT IN ('REV','FREIGHT','TAX','CHARGES','UNBILL','UNEARN') THEN FLS_ERROR_TEXT := 'ACCOUNT CLASS IS NOT VALID FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_ACCOUNT_CLASS := FRIL.D_ACCOUNT_CLASS; END IF; END IF; DBMS_OUTPUT.PUT_LINE('acct class --'||FLS_ACCOUNT_CLASS); FLS_ERR_LOC := 115; /* -- CODE_COMBINATION_ID VALIDATION IF FRIL.D_CODE_COMBINATION_ID IS NULL THEN FLS_ERROR_TEXT := 'CODE_COMBINATION_ID IS NULL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1; RAISE FLS_USER_EXCEPTION; ELSE FLS_CODE_COMBINATION_ID := FRIL.D_CODE_COMBINATION_ID; END IF; DBMS_OUTPUT.PUT_LINE('code combination --'||FLS_CODE_COMBINATION_ID); */ -- OTHER COLUMN DATA FLS_ERR_LOC := 120; FLS_TRX_DATE := FRIL.TRX_DATE; FLS_AMOUNT := FRIL.AMOUNT; FLS_UNIT_SELLING_PRICE := FRIL.UNIT_SELLING_PRICE ; FLS_QUANTITY_ORDERED := FRIL.QUANTITY_ORDERED ; FLS_SALES_ORDER := FRIL.SALES_ORDER ; FLS_SALES_ORDER_DATE := FRIL.SALES_ORDER_DATE; FLS_CREATED_BY := FND_GLOBAL.USER_ID; FLS_CREATION_DATE := SYSDATE; FLS_LAST_UPDATED_BY := FND_GLOBAL.USER_ID; FLS_LAST_UPDATE_DATE := SYSDATE; FLS_LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID; FLS_ERR_LOC := 125; -- INSERT INTO RA_INTERFACE_LINES_ALL DBMS_OUTPUT.PUT_LINE(' INSERT STARTS'); BEGIN INSERT INTO RA_INTERFACE_LINES_ALL ( INTERFACE_LINE_CONTEXT , INTERFACE_LINE_ATTRIBUTE1 , INTERFACE_LINE_ATTRIBUTE2 , CUST_TRX_TYPE_NAME , BATCH_SOURCE_NAME , SET_OF_BOOKS_ID , LINE_TYPE , GL_DATE , TRX_DATE , DESCRIPTION , AMOUNT , CURRENCY_CODE , CONVERSION_TYPE , CONVERSION_RATE , TERM_NAME , UNIT_SELLING_PRICE , QUANTITY_ORDERED , SALES_ORDER , SALES_ORDER_DATE , PRIMARY_SALESREP_NUMBER , ORIG_SYSTEM_BILL_CUSTOMER_REF , ORIG_SYSTEM_SHIP_CUSTOMER_REF , TAX_CODE , ORIG_SYSTEM_BILL_ADDRESS_ID , ORIG_SYSTEM_SHIP_ADDRESS_ID , AMOUNT_INCLUDES_TAX_FLAG , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN ) VALUES ( FLS_INTERFACE_LINE_CONTEXT , FRIL.INTERFACE_LINE_ATTRIBUTE1 , FLS_INTERFACE_LINE_ATTRIBUTE2 , FLS_CUST_TRX_TYPE_NAME_I , FLS_BATCH_SOURCE_NAME , FLS_SET_OF_BOOKS_ID , FLS_LINE_TYPE , FLS_GL_DATE , FLS_TRX_DATE , FLS_DESCRIPTION , FLS_AMOUNT , FLS_CURRENCY_CODE , FLS_CONVERSION_TYPE , FLS_CONVERSION_RATE , DECODE(SIGN(FLS_AMOUNT),-1,NULL,FLS_TERM_NAME) , FLS_UNIT_SELLING_PRICE , FLS_QUANTITY_ORDERED , FLS_SALES_ORDER , FLS_SALES_ORDER_DATE , FLS_PRIMARY_SALESREP_NUMBER , FLS_ORIG_BILL_CUSTOMER_REF , FLS_ORIG_SHIP_CUSTOMER_REF , FRIL.TAX_CODE , FLS_ORIG_BILL_ADDRESS_ID , FLS_ORIG_SHIP_ADDRESS_ID , FLS_AMOUNT_INCLUDES_TAX_FLAG , FLS_CREATED_BY , FLS_CREATION_DATE , FLS_LAST_UPDATED_BY , FLS_LAST_UPDATE_DATE , FLS_LAST_UPDATE_LOGIN ); --DBMS_OUTPUT.PUT_LINE(' INSERT ends in RA_INTERFACE_LINES_ALL '); FLS_ERR_LOC := 130; INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL ( INTERFACE_LINE_CONTEXT , INTERFACE_LINE_ATTRIBUTE1 , INTERFACE_LINE_ATTRIBUTE2 , ACCOUNT_CLASS , AMOUNT , PERCENT , CODE_COMBINATION_ID , ORG_ID , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN ) VALUES ( FLS_INTERFACE_LINE_CONTEXT , FRIL.INTERFACE_LINE_ATTRIBUTE1 , FLS_INTERFACE_LINE_ATTRIBUTE2 , FLS_ACCOUNT_CLASS , FLS_AMOUNT , 100 , FLS_CODE_COMBINATION_ID , FLS_ORG_ID , FLS_CREATED_BY , FLS_CREATION_DATE , FLS_LAST_UPDATED_BY , FLS_LAST_UPDATE_DATE , FLS_LAST_UPDATE_LOGIN ) ; --DBMS_OUTPUT.PUT_LINE(' INSERT ends in RA_INTERFACE_DISTRIBUTIONS_ALL '); FLS_ERR_LOC := 135; COMMIT; LINE_COUNT := LINE_COUNT + 1; FND_FILE.PUT_LINE (FND_FILE.LOG, '1 ROW IS SUCCESSFULLY INSERTED INTO INTERFACE TABLE FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ); UPDATE XX_RA_AUTOINVOICE_STG SET PROCESS_FLAG = 'Y' WHERE INTERFACE_LINE_ATTRIBUTE1 = FRIL.INTERFACE_LINE_ATTRIBUTE1; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; ERRBUFF := SQLERRM; RETCODE := SQLCODE; FND_FILE.NEW_LINE (FND_FILE.LOG, 1); FND_FILE.PUT_LINE (FND_FILE.LOG, 'INSERT FAIL FOR INVOICE NUMBER ' || FRIL.INTERFACE_LINE_ATTRIBUTE1 ); FND_FILE.PUT_LINE ( FND_FILE.LOG,'ERROR LOC: ' || TO_CHAR (FLS_ERR_LOC) ); FND_FILE.PUT_LINE ( FND_FILE.LOG, 'UNKNOWN ERROR' || SQLERRM); END; EXCEPTION WHEN FLS_USER_EXCEPTION THEN ROLLBACK; ERRBUFF := SQLERRM; RETCODE := SQLCODE; FND_FILE.NEW_LINE (FND_FILE.LOG, 1); FND_FILE.PUT_LINE ( FND_FILE.LOG,'ERROR LOC: ' || TO_CHAR (FLS_ERR_LOC) ); FND_FILE.PUT_LINE ( FND_FILE.LOG, 'ERROR IS: ' || FLS_ERROR_TEXT); WHEN OTHERS THEN ROLLBACK; ERRBUFF := SQLERRM; RETCODE := SQLCODE; FND_FILE.NEW_LINE (FND_FILE.LOG, 1); FND_FILE.PUT_LINE ( FND_FILE.LOG,'ERROR LOC: ' || TO_CHAR (FLS_ERR_LOC) ); FND_FILE.PUT_LINE ( FND_FILE.LOG, 'UNKNOWN ERROR' || SQLERRM); END; END LOOP; REJECTED_COUNT := TOTAL_COUNT - LINE_COUNT; FND_FILE.NEW_LINE (FND_FILE.LOG, 2); FND_FILE.PUT_LINE (FND_FILE.LOG,'*********************************************************************'); FND_FILE.PUT_LINE (FND_FILE.LOG,'* INTERFACE STATUS *'); FND_FILE.PUT_LINE (FND_FILE.LOG,'*********************************************************************'); FND_FILE.PUT_LINE (FND_FILE.LOG, 'TOTAL RECORDS ------------------' || TOTAL_COUNT ); FND_FILE.PUT_LINE (FND_FILE.LOG, 'SUCCESSFULLY INSERTED ----------' || LINE_COUNT ); FND_FILE.PUT_LINE (FND_FILE.LOG, 'REJECTED ROCORDS --------------' || REJECTED_COUNT); FND_FILE.PUT_LINE (FND_FILE.LOG,'*********************************************************************'); EXCEPTION WHEN OTHERS THEN ROLLBACK; ERRBUFF := SQLERRM; RETCODE := SQLCODE; FND_FILE.PUT_LINE ( FND_FILE.LOG,'ERROR LOC: ' || TO_CHAR (FLS_ERR_LOC) ); FND_FILE.PUT_LINE ( FND_FILE.LOG, 'UNKNOWN ERROR' || SQLERRM); END XXIPZ_AUTOINVOICE_PROC ;