Argentine Tax BSAS Code

  • Published on
    27-Oct-2014

  • View
    153

  • Download
    1

DESCRIPTION

CREATE OR REPLACE PACKAGE BODY APPS.JL_AR_APPLICABLE_TAXES AS /* $Header: jlarpatb.pls 120.0.12000000.4 2009/02/03 12:39:04 nivnaray noship $ */ --------------------------------------------------------------------------------Global…

Transcript

CREATE OR REPLACE PACKAGE BODY APPS.JL_AR_APPLICABLE_TAXES AS /* $Header: jlarpatb.pls 120.0.12000000.4 2009/02/03 12:39:04 nivnaray noship $ */ --------------------------------------------------------------------------------Global Variables ------------------------------------------------------------------------------p_debug_log l_AWT_TAX_TYPE l_PERCEPTION_TAX_TYPE l_ORG_ID ORGANIZATION_ID'); g_current_runtime_level time_level; g_level_statement nt; g_level_procedure re; g_level_event g_level_exception on; g_level_error g_level_unexpected ted; l_RETURN_STATUS l_taxpayer l_taxpayer_ar VARCHAR2(1) VARCHAR2(10) VARCHAR2(10) NUMBER(15) := := := := 'Y'; 'TURN_BSAS'; 'TOPBA'; oe_profile.value('SO_ CONSTANT NUMBER := fnd_log.g_current_run CONSTANT NUMBER := fnd_log.level_stateme CONSTANT NUMBER := fnd_log.level_procedu CONSTANT NUMBER := fnd_log.level_event; CONSTANT NUMBER := fnd_log.level_excepti CONSTANT NUMBER := fnd_log.level_error; CONSTANT NUMBER := fnd_log.level_unexpec VARCHAR2(1); VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; DATE, DATE, DATE, NUMBER, VARCHAR2, VARCHAR2, VARCHAR2, NUMBER, NUMBER, NUMBER, NUMBER, VARCHAR2, VARCHAR2 PROCEDURE Insert_Row (l_PUBLISH_DATE l_START_DATE l_END_DATE l_TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG l_PERCEPTION_RATE l_WHT_RATE l_PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM l_WHT_DEFAULT_FLAG l_CALLING_RESP ) IS final_insert_check l_created_by l_creation_DATE l_last_UPDATEd_by l_last_UPDATE_DATE l_last_UPDATE_login BEGIN VARCHAR2(1) := 'N'; NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1); DATE := SYSDATE; NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1); DATE := SYSDATE; NUMBER(15) := NVL(fnd_global.conc_login_id, 1); BEGIN SELECT 'Y' INTO final_insert_check FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND START_DATE = l_START_DATE AND END_DATE = l_END_DATE; EXCEPTION WHEN NO_DATA_FOUND THEN final_insert_check := 'N'; END; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG, 'In Insert_Row, value of final_insert_che ck'|| final_insert_check); END IF; IF final_insert_check = 'N' THEN INSERT INTO JL_AR_TURN_UPL( ORG_ID, PUBLISH_DATE, START_DATE, END_DATE, TAXPAYER_ID, CONTRIBUTOR_TYPE_CODE, NEW_CONTRIBUTOR_FLAG, RATE_CHANGE_FLAG, PERCEPTION_RATE, WHT_RATE, PERCEPTION_GROUP_NUM, WHT_GROUP_NUM, WHT_DEFAULT_FLAG, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATION_DATE, CREATED_BY) VALUES ( l_ORG_ID, --ORG_ID l_PUBLISH_DATE, --PUBLISH_DATE l_START_DATE, --START_DATE l_END_DATE, --END_DATE l_TAXPAYER_ID, --TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE, --CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG, --NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG, --RATE_CHANGE_FLAG l_PERCEPTION_RATE, --PERCEPTION_RATE l_WHT_RATE, --WHT_RATE l_PERCEPTION_GROUP_NUM, --PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM, --WHT_GROUP_NUM l_WHT_DEFAULT_FLAG, --WHT_DEFAULT_FLAG l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_login, --LAST_UPDATE_LOGIN l_creation_DATE, --CREATION_DATE l_created_by); --CREATED_BY ELSE IF l_CALLING_RESP = 'AP' THEN UPDATE JL_AR_TURN_UPL SET WHT_RATE = l_WHT_RATE, WHT_GROUP_NUM = l_WHT_G ROUP_NUM, WHT_DEFAULT_FLAG = l_WHT_DEFAULT_FLAG WHERE TAXPAYER_ID = l_TAXPAYER_ID AND START_DATE = l_START_DATE AND END_DATE = l_END_DATE; ELSIF l_CALLING_RESP = 'AR' THEN UPDATE JL_AR_TURN_UPL SET PERCEPTION_RATE = l_PERCEPTION_RATE, PERCEPTIO N_GROUP_NUM = l_PERCEPTION_GROUP_NUM WHERE TAXPAYER_ID = l_TAXPAYER_ID AND START_DATE = l_START_DATE AND END_DATE = l_END_DATE; END IF; END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE INSERTING I NTO FINAL TABLE '|| SQLCODE || 'ERROR' || SQLERRM); END IF; END Insert_Row; FUNCTION FORMAT_DATE(INPUT_DATE IN DATE) RETURN DATE IS l_DATE DATE; BEGIN l_DATE := TO_DATE(INPUT_DATE, 'DD/MM/YYYY'); RETURN l_DATE; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHEN VALIDATING D ATE'|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; END FORMAT_DATE; FUNCTION VALID_NUMBER(INPUT_NUM IN NUMBER) RETURN BOOLEAN IS l_valid_num NUMBER; BEGIN l_valid_num := TO_NUMBER(INPUT_NUM); RETURN TRUE; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHEN VALIDATING N UMBER'|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; RETURN FALSE; END VALID_NUMBER; FUNCTION BASIC_VALIDATION(l_TAXPAYERID IN NUMBER) RETURN BOOLEAN IS l_PUBLISH_DATE l_START_DATE l_END_DATE l_TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG l_PERCEPTION_RATE l_WHT_RATE l_PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM l_PUBLISHDATE_ALL valid_flag DATE; DATE; DATE; NUMBER(15); VARCHAR2(1); VARCHAR2(1); VARCHAR2(1); NUMBER(15,2); NUMBER(15,2); NUMBER(15); NUMBER(15); DATE; VARCHAR2(1) := 'Y'; CURSOR C2 IS SELECT * FROM JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYERID; V_TEMPREC C2%ROWTYPE; BEGIN OPEN C2; LOOP FETCH C2 INTO V_TEMPREC; EXIT WHEN C2%NOTFOUND; l_PUBLISH_DATE l_START_DATE l_END_DATE l_TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG l_PERCEPTION_RATE l_WHT_RATE l_PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM := V_TEMPREC.PUBLISH_DATE; := V_TEMPREC.START_DATE; := V_TEMPREC.END_DATE; := := := := := := := := V_TEMPREC.TAXPAYER_ID; V_TEMPREC.CONTRIBUTOR_TYPE_CODE; V_TEMPREC.NEW_CONTRIBUTOR_FLAG; V_TEMPREC.RATE_CHANGE_FLAG; V_TEMPREC.PERCEPTION_RATE; V_TEMPREC.WHT_RATE; V_TEMPREC.PERCEPTION_GROUP_NUM; V_TEMPREC.WHT_GROUP_NUM; BEGIN SELECT MAX(PUBLISH_DATE) INTO l_PUBLISHDATE_ALL FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYERID; IF (l_PUBLISH_DATE < l_PUBLISHDATE_ALL) AND (l_START_DATE > l_END_DATE) THEN valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING DATE CHECK VAL IDATION'); END IF; END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'NO PREVIOUS RECORD IN JL_AR_TURN_UPL' ); END IF; END; IF VALID_NUMBER(V_TEMPREC.TAXPAYER_ID) THEN l_TAXPAYER_ID := TO_NUMBER(V_TEMPREC.TAXPAYER_ID, '99999999999'); ELSE valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED IN TAXPAYER ID (NUMBER) CHECK VALIDATION'); END IF; END IF; -- AP tax payer id available check BEGIN SELECT DISTINCT 'Y' INTO l_taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF WHERE NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID); EXCEPTION WHEN NO_DATA_FOUND THEN l_taxpayer := 'N'; WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED IN TAX_PAYER VALIDATION FOR AP '|| SQLCODE || 'ERROR' || SQLERRM); END IF; RETURN FALSE; -- fetch next record END; -- AR tax payer id available check BEGIN SELECT DISTINCT 'Y' INTO l_taxpayer_ar FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS_ALL HZCA, HZ_CUST_ACCT_SITES_ALL HZAS, HZ_CUST_SITE_USES_ALL HZSU WHERE HZCA.PARTY_ID = HZP.PARTY_ID AND HZCA.CUST_ACCOUNT_ID = HZAS.CUST_ACCOUNT_ID AND HZAS.CUST_ACCT_SITE_ID = HZSU.CUST_ACCT_SITE_ID AND HZSU.ORG_ID = l_ORG_ID AND HZP.JGZZ_FISCAL_CODE = TO_CHAR(l_TAXPAYER_ID); EXCEPTION WHEN NO_DATA_FOUND THEN l_taxpayer_ar := 'N'; WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED IN TAX_PAYER VALIDATION FOR AR'|| SQLCODE || 'ERROR' || SQLERRM); END IF; RETURN FALSE; -- fetch next record END; IF l_taxpayer = 'N' AND l_taxpayer_ar = 'N' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'TAXPAYER ID IS NOT AVAILABLE'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_TAXPAYER_ID_NOT_AVA ILABLE' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_IN FO_D2 = l_END_DATE; RETURN FALSE; END IF; l_CONTRIBUTOR_TYPE_CODE := V_TEMPREC.CONTRIBUTOR_TYPE_CODE; l_NEW_CONTRIBUTOR_FLAG := V_TEMPREC.NEW_CONTRIBUTOR_FLAG; l_RATE_CHANGE_FLAG := V_TEMPREC.RATE_CHANGE_FLAG; IF (l_CONTRIBUTOR_TYPE_CODE NOT IN ('D', 'C')) THEN valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING CONTRIBUTOR TYPE CHECK VALIDATION'); END IF; ELSIF (l_NEW_CONTRIBUTOR_FLAG NOT IN ('S', 'N', 'B')) THEN valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING NEW CONTRIBUTOR CHECK VALIDATION'); END IF; ELSIF (l_RATE_CHANGE_FLAG NOT IN ('S', 'N')) THEN valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING RATE CHANGE FLAG CHECK VALIDATION'); END IF; ELSE valid_flag := 'Y'; END IF; IF VALID_NUMBER(V_TEMPREC.PERCEPTION_RATE) THEN l_PERCEPTION_RATE := TO_NUMBER(V_TEMPREC.PERCEPTION_RATE); ELSE valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING PERCEPTION_RATE CHECK VALIDATION'); END IF; END IF; IF VALID_NUMBER(V_TEMPREC.WHT_RATE) THEN l_WHT_RATE := TO_NUMBER(V_TEMPREC.WHT_RATE); ELSE valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING WHT_RATE CHECK V ALIDATION'); END IF; END IF; IF VALID_NUMBER(NVL(V_TEMPREC.PERCEPTION_GROUP_NUM, 0)) THEN l_PERCEPTION_GROUP_NUM := TO_NUMBER(NVL(V_TEMPREC.PERCEPTION_GROUP_NUM, 0), '99'); ELSE valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING PERCEPTION_GROUP _NUM CHECK VALIDATION'); END IF; END IF; IF VALID_NUMBER(NVL(V_TEMPREC.WHT_GROUP_NUM, 0)) THEN l_WHT_GROUP_NUM := TO_NUMBER(NVL(V_TEMPREC.WHT_GROUP_NUM, 0), '99'); ELSE valid_flag := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING WHT_GROUP_NUM CH ECK VALIDATION'); END IF; END IF; IF (valid_flag = 'Y') THEN RETURN TRUE; ELSIF (valid_flag = 'N') THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'RECORD FAILED DURING BASIC CHECK VALI DATION, PLEASE REFER LOG FILE/REPORT '); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAIL_BASIC_C HECK' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_IN FO_D2 = l_END_DATE; RETURN FALSE; END IF; END LOOP; CLOSE C2; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'AN ERROR WAS ENCOUNTERED DURING BASIC CHE CK VALIDATION'|| SQLCODE || 'ERROR' || SQLERRM); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAIL_BASIC_CHECK ' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D 2 = l_END_DATE; RETURN FALSE; END BASIC_VALIDATION; PROCEDURE FINAL_VALIDATION IS l_PUBLISH_DATE l_START_DATE l_END_DATE l_TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG l_PERCEPTION_RATE DATE; DATE; DATE; NUMBER(15); VARCHAR2(1); VARCHAR2(1); VARCHAR2(1); NUMBER(15,2); l_WHT_RATE l_PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM l_WHT_DEFAULT_FLAG l_TAX_NAME_AP_AWT l_TAX_RATE_AP_AWT l_EFFECTIVE_START_DATE l_EFFECTIVE_END_DATE l_WHT_RATE_ALL l_WHT_GROUP_NUM_ALL NUMBER(15,2); NUMBER(15); NUMBER(15); VARCHAR2(1) := 'N'; NUMBER(15); NUMBER(15); DATE; DATE; AP_AWT_TAX_RATES_ALL.TAX_RATE%TYPE; AP_AWT_TAX_RATES_ALL.TAX_NAME%TYPE; JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID ap_tax_codes.tax_id%TYPE; JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID PO_VENDORS.VENDOR_ID%TYPE; JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ AP_INVOICES_ALL.INVOICE_ID%TYPE; AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTIO AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DIS DATE := NULL; DATE := NULL; DATE; JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ l_SUPP_AWT_CODE_ID_CD %TYPE; l_SUPP_AWT_TYPE_ID_CD %TYPE; l_SUPP_AWT_CODE_ID_SEQ %TYPE; l_SUPP_AWT_TYPE_ID_SEQ %TYPE; l_tax_id l_SUPP_AWT_TYPE_ID_TYPES %TYPE; l_VENDOR_ID l_INV_DISTRIB_AWT_ID_INV ID%TYPE; l_INVOICE_ID_INA l_DISTRIBUTION_LINE_NUMBER_IND N_LINE_NUMBER%TYPE; l_INVOICE_DISTRIBUTION_ID_IND TRIBUTION_ID%TYPE; l_START_DATE_SEC_MAX l_END_DATE_SEC_MAX l_EFFECTIVE_START_DATE_COMP l_INV_DISTRIB_AWT_ID_DIS ID%TYPE; CURSOR CUR3(l_TAXPAYERID_C NUMBER) IS SELECT APINA.INVOICE_ID, APIND.DISTRIBUTION_LINE_NUMBER,APIND.INVOICE_DISTRIBU TION_ID FROM PO_VENDORS PV, AP_INVOICES_ALL APINA, AP_INVOICE_DISTRIBUTIONS_ALL APIND, PER_ALL_PEOPLE_F PAPF WHERE PV.VENDOR_ID = APINA.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE --AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHERE na me like 'TURN_BSAS_GRP%') AND APINA.INVOICE_ID = APIND.INVOICE_ID AND APIND.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND APIND.GLOBAL_ATTRIBUTE3 IN (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = 'Buenos Aires' AND trunc(SYSDATE) = P_START_DATE and end_DATE 1 THEN duplicate_check_flag := 'Y'; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'1 .Found more than one set of START_DAT E or END_DATE'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_WRONG_DATE' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO _D2 = l_END_DATE; NUMBER := NULL; VARCHAR2(1) := 'Y'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; NUMBER(15) DATE NUMBER(15) DATE NUMBER(15) NUMBER(15) := := := := := := NVL(fnd_profile.value('USER_ID'), 1); SYSDATE; NVL(fnd_profile.value('USER_ID'), 1); SYSDATE; NVL(fnd_global.conc_login_id, 1); oe_profile.value('SO_ORGANIZATION_ID'); RAISE_APPLICATION_ERROR(- 20999,'Found more than one set of START_DATE / E ND_DATE'|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; -- will Stop the process, because of duplicate PUBLISH_DATE, START_D ATE, END_DATE in TMP table OPEN C3; LOOP FETCH C3 INTO V_TEMPREC; EXIT WHEN C3%NOTFOUND; BEGIN l_PUBLISH_DATE := V_TEMPREC.PUBLISH_DATE; l_START_DATE := V_TEMPREC.START_DATE; l_END_DATE := V_TEMPREC.END_DATE; l_TAXPAYER_ID := V_TEMPREC.TAXPAYER_ID; l_CONTRIBUTOR_TYPE_CODE := V_TEMPREC.CONTRIBUTOR_TYPE_CODE; l_NEW_CONTRIBUTOR_FLAG := V_TEMPREC.NEW_CONTRIBUTOR_FLAG; l_RATE_CHANGE_FLAG := V_TEMPREC.RATE_CHANGE_FLAG; l_PERCEPTION_RATE := V_TEMPREC.PERCEPTION_RATE; l_WHT_RATE := V_TEMPREC.WHT_RATE; l_PERCEPTION_GROUP_NUM := V_TEMPREC.PERCEPTION_GROUP_NUM; l_WHT_GROUP_NUM := V_TEMPREC.WHT_GROUP_NUM; INSERT INTO JGZZ_AR_TAX_GLOBAL_TMP(JG_INFO_N1, JG_INFO_D1, JG_INFO_D2, J G_INFO_V1) VALUES (l_TAXPAYER_ID, l_START_DATE, l_END_DATE, NULL); IF BASIC_VALIDATION(l_TAXPAYER_ID) THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'2 .RECORD PASSED IN BASIC VALIDATIO N FOR TAXPAYER: '|| l_TAXPAYER_ID); END IF; ELSE IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'3 .RECORD FAILED DURING BASIC VALID ATION FOR TAXPAYER : '|| l_TAXPAYER_ID); END IF; GOTO L3; END IF; -- AR Code Hook IF l_taxpayer_ar = 'Y' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'3 A .GOING TO START THE AR VALIDATI ON FOR TAXPAYER: '|| l_TAXPAYER_ID); END IF; BEGIN JL_ZZ_AR_UPLOAD_TAXES.JL_AR_UPDATE_CUST_SITE_TAX(l_TAXPAYER_ID, l_AWT_TAX_TYPE, l_PERCEPTION_TAX_TY PE, l_ORG_ID, l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_RETURN_STATUS ); -- out parameter for status IF l_RETURN_STATUS = 'Y' THEN FND_FILE.put_line(FND_FILE.LOG,'58. AR validation completed succes sfully'); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line(FND_FILE.LOG,'AR VALIDATION FAILED WITH RETUN ST ATUS'|| l_RETURN_STATUS || SQLCODE || 'ERROR' || SQLERRM); END IF; RAISE_APPLICATION_ERROR(- 20999,'AR VALIDATION FAILED'|| SQLCODE ||' -ERROR- '|| SQLERRM); END; END IF; -- If data present in AP tax payer id, then AP Validation starts here IF l_taxpayer = 'Y' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'3 B .GOING TO START THE AP VALIDAT ION FOR TAXPAYER: '|| l_TAXPAYER_ID); END IF; BEGIN SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND START_DATE NOT IN (SELECT MAX(START_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID); SELECT MAX(END_DATE) INTO l_END_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHE RE TAXPAYER_ID = l_TAXPAYER_ID AND END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXP AYER_ID = l_TAXPAYER_ID); EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'4 .NO PREVIOUS RECORD AVAILABLE I N JL_AR_TURN_UPL TABLE'); END IF; END; l_START_DATE_SEC_MAX := NVL(l_START_DATE_SEC_MAX, l_START_DATE); l_END_DATE_SEC_MAX := NVL(l_END_DATE_SEC_MAX, l_END_DATE); -- To check the WHT_GROUP_NUM + wht_ rate (TDD 9.4.A. Additional Check 1) BEGIN SELECT 'TURN_BSAS_GRP' || lpad(WHT_GROUP_NUM, 2, '0'), WHT_RATE INTO l_WHT_GROUP_NUM_ALL, l_WHT_RATE_ALL FROM JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID GROUP BY WHT _RATE, WHT_GROUP_NUM; SELECT 'Y' INTO WHT_GROUP_NUM_rate_flag FROM AP_AWT_TAX_RATES_ALL WHERE tax_name = l_WHT_GROUP_NUM_ALL AND tax_rate = l_WHT_RATE_ALL; SELECT tax_id INTO l_tax_id FROM AP_TAX_CODES_ALL WHERE name = l_WHT _GROUP_NUM_ALL AND tax_type = 'AWT'; EXCEPTION WHEN TOO_MANY_ROWS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'4 .MORE THAN ONE WHT_RATE AND WHT _GROUP_NUM FOUND FOR A TAX_PAYER_ID'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_MANY_WHT_RATE_G ROUP' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND J G_INFO_D2 = l_END_DATE; RAISE_APPLICATION_ERROR(- 20999,'AN ERROR WAS ENCOUNTERED IN WHT_GRO UP_NUM AND WHT_RATE VALIDATION '|| SQLCODE ||' -ERROR- '|| SQLERRM); WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'5 .Wht Rate and Wht group for thi s record from Government File doesnt match with AP Wht Tax Setup.'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATC H' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND J G_INFO_D2 = l_END_DATE; RAISE_APPLICATION_ERROR(- 20999,'AN ERROR WAS ENCOUNTERED IN WHT_GRO UP_NUM AND WHT_RATE VALIDATION '|| SQLCODE ||' -ERROR- '|| SQLERRM); END; IF WHT_GROUP_NUM_rate_flag = 'N' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'6 .Wht Rate and Wht group for thi s record from Government File doesnt match with AP Wht Tax Setup.'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATC H' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND J G_INFO_D2 = l_END_DATE; RAISE_APPLICATION_ERROR(- 20999,'WHT_RATE AND WHT_GROUP_NUM FROM GOV ERNMENT FILE DOESNT MATCH WITH AP WHT TAX SETUP '|| SQLCODE ||' -ERROR- '|| SQLE RRM); END IF; /* -- To compare JL_AR_TURN _UPL_T Upload Table records with JL_AR_TURN_UPL (9.4.A.3) BEGIN same_prev_rec_flag := 'N'; --Intialising the value of same_prev_r ec_flag everytime to check whether the record available already SELECT DISTINCT 'Y' INTO same_prev_rec_flag FROM JL_AR_TURN_UPL WHER E PUBLISH_DATE = l_PUBLISH_DATE AND START_DATE = l_START_DATE AND END_DATE = l_END_DATE AND TAXPAYER_ID = l_TAXPAYER_ID AND CONTRIBUTOR_TYPE_CODE = l_CONTRIBUTOR_TYPE_CODE AND NEW_CONTRIBUTOR_FLAG = l_NEW_CONTRIBUTOR_FLAG AND RATE_CHANGE_FLAG = l_RATE_CHANGE_FLAG AND PERCEPTION_RATE = l_PERCEPTION_RATE AND WHT_RATE = l_WHT_RATE AND PERCEPTION_GROUP_NUM = l_PERCEPTION_GROUP_NUM AND WHT_GROUP_NUM = l_WHT_GROUP_NUM; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'7 .THE VALUE FOR SAME_PREV_RECORD _FLAG:'||same_prev_rec_flag); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'7A .NO CORRESPONDING DATA IN JL_A R_TURN_UPL'); END IF; WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'8 .FAILED WHILE CHECKING THE CORR ESPONDING DATA IN JL_AR_TURN_UPL'); END IF; END; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'9 .VALUE OF SAME_PREV_RECORD_FLAG : '|| same_prev_rec_flag); END IF; -- For exactly same records simply copying that to ALL table FROM TMP and fetching next record (TDD 3.1) IF same_prev_rec_flag = 'Y' then FND_FILE.PUT_LINE( FND_FILE.LOG,'10. Going to fetch next record as Taxpayer Id was already present'); GOTO L3; -- If the same exact Taxpayer valu e was already present in final table then simply fetch next record END IF; ---/* IF same_rec_flag = 'Y' THEN Insert_Row (l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_TAXPAYER_ID, l_CONTRIBUTOR_TYPE_CODE, l_NEW_CONTRIBUTOR_FLAG, l_RATE_CHANGE_FLAG, l_PERCEPTION_RATE, l_WHT_RATE, l_PERCEPTION_GROUP_NUM, l_WHT_GROUP_NUM, l_WHT_DEFAULT_FLAG, 'AP'); --delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'10 .INSERTED DATA IN JL_AR_TURN_U PL SAME_REC_FLAG IS Y AND DELETED FROM TMP TABLE'); END IF; GOTO L3; --fetching next record END IF; */ --To check whether TMP taxpayer id present alrea dy in previous months in ALL table (3.2) BEGIN SELECT DISTINCT 'Y' INTO same_taxpayerid_flag FROM JL_AR_TURN_UPL WH ERE PUBLISH_DATE l_PUBLISH_DATE AND START_DATE l_START_DATE AND END_DATE l_END_DATE AND TAXPAYER_ID = l_TAXPAYER_ID; EXCEPTION WHEN NO_DATA_FOUND THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'12 .NO CORRESPONDING TAXPAYER_ID IN JL_AR_TURN_UPL'); END IF; WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'13 .FAILED WHILE CHECKING THE COR RESPONDING TAXPAYER_ID IN JL_AR_TURN_UPL'); END IF; END; IF same_taxpayerid_flag = 'Y' AND l_RATE_CHANGE_FLAG = 'S' THEN wht_check_flag := 'N'; BEGIN SELECT 'Y' INTO wht_check_flag FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND WHT_GROUP_NUM = l_WHT_GROUP_NUM AND WHT_RATE = l_WHT_RATE AND START_DATE = l_START_DATE_SEC_MAX AND END_DATE = l_END_DATE_SEC_MAX; EXCEPTION WHEN NO_DATA_FOUND THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'14. NO CORRESPONDING DATA FOR l _WHT_GROUP_NUM AND l_WHT_RATE'); END IF; WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'15 .FAILED WHILE CHECKING THE C ORRESPONDING DATA IN l_WHT_GROUP_NUM AND l_WHT_RATE'); END IF; END; -- for record s of same wht group/wht rate, simply copy (TDD 3.2.1.1) IF wht_check_flag = 'Y' THEN Insert_Row (l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_TAXPAYER_ID, l_CONTRIBUTOR_TYPE_CODE, l_NEW_CONTRIBUTOR_FLAG, l_RATE_CHANGE_FLAG, l_PERCEPTION_RATE, l_WHT_RATE, l_PERCEPTION_GROUP_NUM, l_WHT_GROUP_NUM, l_WHT_DEFAULT_FLAG, 'AP'); -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'16 .INSERTED DATA IN TABLE JL_A R_TURN_UPL WHEN WHT_CHECK_FLAG IS Y '); END IF; GOTO L3; -- If the wht rate and wht grou p are different (3.2.1.2) then UPDATE the tax SETups ELSE BEGIN SELECT SAWT.SUPP_AWT_TYPE_ID INTO l_SUPP_AWT_TYPE_ID_CD FROM PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PAPF WHERE SAWT.VENDOR_ID = PV.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1 099)) = TO_CHAR(l_TAXPAYER_ID); IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'17. EFFECTIVE_END_DATE (l_START _DATE-1) : '|| l_START_DATE); FND_FILE.put_line( FND_FILE.LOG,'17. l_tax_id : '|| l_tax_id || 'l_TAXPAYER_ID : '|| l_TAXPAYER_ID); END IF; SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ F ROM dual; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'18. l_SUPP_AWT_CODE_ID_SEQ'|| l _SUPP_AWT_CODE_ID_SEQ); FND_FILE.put_line( FND_FILE.LOG,'18. l_SUPP_AWT_TYPE_ID_CD'|| l_ SUPP_AWT_TYPE_ID_CD); END IF; BEGIN SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO taxtype_code_check, l_ SUPP_AWT_CODE_ID_CD FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD AND SAWTC.TAX_ID = l_tax_id AND (SAWTC.EFFECTIVE_START_DATE = l_START_DATE OR SAWTC. EFFECTIVE_END_DATE = l_END_DATE); EXCEPTION WHEN NO_DATA_FOUND THEN taxtype_code_check := 'N'; WHEN OTHERS THEN taxtype_code_check := 'N'; FND_FILE.PUT_LINE( FND_FILE.LOG,'21. Error occured while fetch ing data in JL_ZZ_AP_SUP_AWT_CD_ALL'); END; EXCEPTION WHEN OTHERS THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'21. No records fetched from JL_Z Z_AP_SUP_AWT_CD_ALL for Taxpayer Id :||l_TAXPAYER_ID'); END; IF taxtype_code_check = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'21. Records already present in JL_ ZZ_AP_SUP_AWT_CD_ALL table, no modifications'); FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . L_TAX_ID'|| L_TAX_ID || 'l_SU PP_AWT_TYPE_ID_CD' || l_SUPP_AWT_TYPE_ID_CD); FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . L_EFFECTIVE_START_DATE'|| l_S TART_DATE || 'EFFECTIVE_END_DATE' || l_END_DATE); ELSE BEGIN -- CODE TO UPDATE T HE EFFECTIVE_END_DATE FOR OTHER PRIMARY_TAX_FLAG = 'Y' AND OTHER L_TAX_ID UPDATE JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC SET SAWTC.EFFECTIVE_END_DATE = l_START_DATE - 1 WHERE SAWTC.primary_tax_flag = 'Y' AND SAWTC.SUPP_AWT_CODE_ID l_SUPP_AWT_CODE_ID_SEQ AND SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD --AND SAWTC.TAX_ID l_tax_id -- other l_tax_id AND SAWTC.EFFECTIVE_END_DATE IS NULL; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'19 .UPDATED DATA IN JL_ZZ_AP_SU P_AWT_CD_ALL FOR '|| SQL%ROWCOUNT || 'RECORDS'); FND_FILE.PUT_LINE( FND_FILE.LOG,'19. EFFECTIVE_END_DATE (L_START _DATE-1) : '|| L_START_DATE); FND_FILE.PUT_LINE( FND_FILE.LOG,'19. L_TAX_ID : '|| L_TAX_ID || ' L_TAXPAYER_ID : '|| L_TAXPAYER_ID); END IF; INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL (SUPP_AWT_CODE_ID, SUPP_AWT_TYPE_ID, TAX_ID, PRIMARY_TAX_FLAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) VALUES (l_SUPP_AWT_CODE_ID_SEQ, --SUPP_AWT_CODE_ID l_SUPP_AWT_TYPE_ID_CD, --SUPP_AWT_TYPE_ID l_tax_id, --TAX_ID 'Y', --PRIMARY_TAX_FLAG l_created_by, --CREATED_BY l_creation_DATE, --CREATION_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATE_login, --LAST_UPDATE_LOGIN l_ORG_ID, --ORG_ID l_START_DATE, --EFFECTIVE_START_DATE NULL); --EFFECTIVE_END_DATE IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . INSERTED DATA IN JL_ZZ_A P_SUP_AWT_CD_ALL FOR '|| SQL%ROWCOUNT || 'RECORDS'); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'21. INSERT NOT DONE IN JL_ZZ_ AP_SUP_AWT_CD_ALL '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; END; END IF; -- CODE TO UPDATE/INSERT JL_ZZ_AP_INV_DIS_WH_ALL (TDD 3.2.1.1) BEGIN -- TO GET THE CURRENT MONT H SUPP_AWT_CODE_ID FOR THE PRESENT TAX_ID SELECT SAWTC.SUPP_AWT_CODE_ID INTO l_SUPP_AWT_CODE_ID_CD FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PA PF WHERE SAWT.VENDOR_ID = PV.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID --AND SAWTC.TAX_ID in (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHERE name like 'TURN_BSAS_GRP%') AND SAWTC.TAX_ID = l_tax_id AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE AND SAWTC.primary_tax_flag = 'Y' AND sawtc.effective_end_DATE IS NULL AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num _1099)) = TO_CHAR(l_TAXPAYER_ID); SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_I NV FROM dual; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'22 B SELECT TAX CODE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH_ALL - '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; END; OPEN CUR3(l_TAXPAYER_ID); LOOP FETCH CUR3 INTO INV_REC; EXIT WHEN CUR3%NOTFOUND; BEGIN AWT_CODE_INV_AVAIL_FLAG := 'N'; SELECT 'Y', INV_DISTRIB_AWT_ID INTO AWT_CODE_INV_AVAIL_FLAG, l _INV_DISTRIB_AWT_ID_DIS FROM JL_ZZ_AP_INV_DIS_WH_ALL WHERE INVOICE_ID = INV_REC.INVOICE_ID AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBE R AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID AND SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD; /*(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID -- AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHER E name LIKE 'TURN_BSAS_GRP%')); */ EXCEPTION WHEN NO_DATA_FOUND THEN AWT_CODE_INV_AVAIL_FLAG := 'N'; FND_FILE.put_line( FND_FILE.LOG,'22 B No earlier same tax code id in Inv Dist'|| SQLCODE ||' -ERROR- '|| SQLERRM); WHEN OTHERS THEN AWT_CODE_INV_AVAIL_FLAG := 'N'; FND_FILE.put_line( FND_FILE.LOG,'22 B Error in Inv Dist'|| SQL CODE ||' -ERROR- '|| SQLERRM); END; IF AWT_CODE_INV_AVAIL_FLAG = 'N' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'22 NV'|| l_INV_DISTRIB_AWT_ID_INV); FND_FILE.put_line( FND_FILE.LOG,'22 INV_REC.INVOICE_ID); FND_FILE.put_line( FND_FILE.LOG,'22 INE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER); FND_FILE.put_line( FND_FILE.LOG,'22 || l_SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'22 D'|| INV_REC.INVOICE_DISTRIBUTION_ID); END IF; BEGIN insert data INTO jl_zz_ap_inv_dis_wh_all INSERT INTO JL_ZZ_AP_INV_DIS_WH_ALL (INV_DISTRIB_AWT_ID, INVOICE_ID, DISTRIBUTION_LINE_NUMBER, SUPP_AWT_CODE_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID, INVOICE_DISTRIBUTION_ID) VALUES B l_INV_DISTRIB_AWT_ID_I B INV_REC.INVOICE_ID'|| B INV_REC.DISTRIBUTION_L B l_SUPP_AWT_CODE_ID_CD' B INVOICE_DISTRIBUTION_I ---code to (l_INV_DISTRIB_AWT_ID_INV, --INV_DISTRIB_AWT_ID INV_REC.INVOICE_ID, --INVOICE_ID INV_REC.DISTRIBUTION_LINE_NUMBER, --DISTRIBUTION_LINE_NUMBE R l_SUPP_AWT_CODE_ID_CD, --SUPP_AWT_CODE_ID l_created_by, --CREATED_BY l_creation_DATE, --CREATION_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATE_login, --LAST_UPDATE_LOGIN l_ORG_ID, --ORG_ID INV_REC.INVOICE_DISTRIBUTION_ID); --Invoice distribution -- R12 Changes ID SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ ID_INV FROM dual; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'22. C '|| SQL%ROWCOUNT || 'Inserted records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'22. C Failed while Insert ed records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; END; ELSE BEGIN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'23 _INV'|| l_INV_DISTRIB_AWT_ID_INV); FND_FILE.put_line( FND_FILE.LOG,'23 | INV_REC.INVOICE_ID); FND_FILE.put_line( FND_FILE.LOG,'23 _LINE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER); FND_FILE.put_line( FND_FILE.LOG,'23 D'|| l_SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'23 _DIS'|| l_INV_DISTRIB_AWT_ID_DIS); FND_FILE.put_line( FND_FILE.LOG,'23 _ID'|| INV_REC.INVOICE_DISTRIBUTION_ID); END IF; A l_INV_DISTRIB_AWT_ID A INV_REC.INVOICE_ID'| A INV_REC.DISTRIBUTION A l_SUPP_AWT_CODE_ID_C A l_INV_DISTRIB_AWT_ID A INVOICE_DISTRIBUTION /*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS FROM JL_ZZ_AP_INV_DIS_WH_ALL WHERE INVOICE_ID = INV_REC.INVOICE_ID AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUM BER AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ ID AND SUPP_AWT_CODE_ID IN (SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID AND SAWTC.primary_tax_flag = 'Y' AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES_ALL WH ERE name LIKE 'TURN_BSAS_GRP%') AND sawtc.effective_end_DATE IS NULL);*/ UPDATE JL_ZZ_AP_INV_DIS_WH_ALL SET SUPP_AWT_CODE_ID = l_SUPP _AWT_CODE_ID_CD WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'23 B. Updated '|| SQL%ROW COUNT ||' records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); FND_FILE.put_line( FND_FILE.LOG,'23 B. l_SUPP_AWT_CODE_ID_ CD:'|| l_SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'23 B. l_INV_DISTRIB_AWT_I D_DIS:'|| l_INV_DISTRIB_AWT_ID_DIS); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'23 B. Failed while updati ng records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; END; END IF; END LOOP; CLOSE CUR3; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'24. UPDATED DATA IN JL_ZZ_AP_IN V_DIS_WH_ALL FOR SUPP_AWT_CODE_ID '); END IF; Insert_Row (l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_TAXPAYER_ID, l_CONTRIBUTOR_TYPE_CODE, l_NEW_CONTRIBUTOR_FLAG, l_RATE_CHANGE_FLAG, l_PERCEPTION_RATE, l_WHT_RATE, l_PERCEPTION_GROUP_NUM, l_WHT_GROUP_NUM, l_WHT_DEFAULT_FLAG, 'AP'); -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'26 . Inserted data in JL_AR_TUR N_UPL for All Records '); END IF; END IF; GOTO L3; --code to inse rt in ALL table for rate_change_flag N (TDD 3.2.2) ELSIF same_taxpayerid_flag = 'Y' AND l_RATE_CHANGE_FLAG = 'N' THEN Insert_Row (l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_TAXPAYER_ID, l_CONTRIBUTOR_TYPE_CODE, l_NEW_CONTRIBUTOR_FLAG, l_RATE_CHANGE_FLAG, l_PERCEPTION_RATE, l_WHT_RATE, l_PERCEPTION_GROUP_NUM, l_WHT_GROUP_NUM, l_WHT_DEFAULT_FLAG, 'AP'); IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'28. Inserted data in JL_AR_TURN_U PL when same_rec_flag is N AND l_RATE_CHANGE_FLAG is N'); FND_FILE.put_line( FND_FILE.LOG,'Fetching Record Record after Taxp ayer Id '|| l_TAXPAYER_ID); END IF; --delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID; --GOTO L3; END IF; -- To check the data EXIST in JL_AR_TURN_UPL_T, b ut DO NOT EXIST in JL_AR_TURN_UPL table (TDD 3.3) BEGIN SELECT 'Y' INTO exist_check_flag FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID NOT IN (SELECT TAXPAYER_ID FROM JL_AR_TURN_UPL_T W HERE START_DATE = l_START_DATE AND END_DATE = l_END_DATE AND PUBLISH_DATE = l_PUBLISH_DATE AND TAXPAYER_ID = l_TAXPAYER_ID); EXCEPTION WHEN NO_DATA_FOUND THEN exist_check_flag := 'Y'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,' 29 . NO DATA EXIST IN TMP TABLE OTHER THAN ALL TABLE DATA'); END IF; WHEN OTHERS THEN exist_check_flag := 'Y'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,' 30 .FAILED IN EXIST_CHECK_FLAG V ALIDATION'|| EXIST_CHECK_FLAG); END IF; END; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,' 31 . EXIST_CHECK_FLAG'|| EXIST_CHE CK_FLAG); END IF; SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL W HERE TAXPAYER_ID = l_TAXPAYER_ID AND START_DATE NOT IN (SELECT MAX(START_DATE) FROM JL_AR_TURN_UPL WHERE TA XPAYER_ID = l_TAXPAYER_ID); SELECT MAX(END_DATE) INTO l_END_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID AND END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAY ER_ID = l_TAXPAYER_ID); IF exist_check_flag = 'Y' THEN wht_check_unique := 'Y'; BEGIN SELECT 'N' INTO wht_check_unique FROM JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID AND WHT_GROUP_NUM = l_WHT_GROUP_NUM AND WHT_RATE = l_WHT_RATE; EXCEPTION WHEN NO_DATA_FOUND THEN wht_check_unique := 'N'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,' 32 .NO DATA IN WHT_CHECK_FLAG IN JL_AR_TURN_UPL'); END IF; WHEN OTHERS THEN wht_check_unique := 'Y'; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'33 .FAILED IN WHT_CHECK_FLAG VA LIDATION'); END IF; RAISE_APPLICATION_ERROR(- 20999,'FAILED IN WHT_CHECK_FLAG VALIDATI ON '|| SQLCODE ||' -ERROR- '|| SQLERRM); END; END IF; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,' 31 . WHT_CHECK_UNIQUE'|| WHT_CHECK _UNIQUE); END IF; -- If the wht_rate /WHT_GROUP_NUM in TMP were not unique then raise error (3.3.1) IF wht_check_unique = 'Y' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,' 33 WHT_rate and WHT_GROUP_NUM FROM government file was not unique'); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_GRP_NOT_UNIQUE ' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND J G_INFO_D2 = l_END_DATE; RAISE_APPLICATION_ERROR(- 20999,'33 A An error was encountered in J L_AR_GRP_NOT_UNIQUE- '|| SQLCODE ||' -ERROR- '|| SQLERRM); GOTO L3; --raise error and fetch next record. ELSE --- If wht unique then INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES (3.3. 2) BEGIN SELECT JL_ZZ_AP_SUPP_AWT_TYPES_s.nextval INTO l_SUPP_AWT_TYPE_ID_T YPES FROM dual; SELECT PV.VENDOR_ID INTO l_VENDOR_ID FROM PO_VENDORS PV, PER_ALL_P EOPLE_F PAPF WHERE NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1 099)) = TO_CHAR(l_TAXPAYER_ID); IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'34 . l_SUPP_AWT_TYPE_ID_TYPES'| | l_SUPP_AWT_TYPE_ID_TYPES || 'and' || 'l_VENDOR_ID' || l_VENDOR_ID); END IF; INSERT INTO JL_ZZ_AP_SUPP_AWT_TYPES( SUPP_AWT_TYPE_ID, VENDOR_ID, AWT_TYPE_CODE, WH_SUBJECT_FLAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) VALUES (l_SUPP_AWT_TYPE_ID_TYPES, --SUPP_AWT_TYPE_ID, l_VENDOR_ID, --VENDOR_ID 'TURN_BSAS', --AWT_TYPE_CODE 'Y', --WH_SUBJECT_FLAG l_created_by, --CREATED_BY l_creation_DATE, --CREATION_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATE_login); --LAST_UPDATE_LOGIN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'34 . Inserted data in JL_ZZ_AP_ SUPP_AWT_TYPES for '|| SQL%ROWCOUNT ||' Records'); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'35 .Insert in JL_ZZ_AP_SUPP_AWT _TYPES not done - '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; END; BEGIN --- TO INSERT IN TO JL_ZZ_AP_SUP_AWT_CD_ALL (3.3.2) SELECT SAWT.SUPP_AWT_TYPE_ID INTO l_SUPP_AWT_TYPE_ID_CD FROM PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PAPF WHERE SAWT.VENDOR_ID = PV.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1 099)) = TO_CHAR(l_TAXPAYER_ID); IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'36. EFFECTIVE_END_DATE (l_START _DATE-1) : '|| l_START_DATE); FND_FILE.put_line( FND_FILE.LOG,'36. l_tax_id : '|| l_tax_id || 'l_TAXPAYER_ID : '|| l_TAXPAYER_ID); END IF; SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ F ROM dual; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'37. l_SUPP_AWT_CODE_ID_SEQ'|| l _SUPP_AWT_CODE_ID_SEQ); FND_FILE.put_line( FND_FILE.LOG,'37. l_SUPP_AWT_TYPE_ID_CD'|| l_ SUPP_AWT_TYPE_ID_CD); END IF; -- code to UPDATE t he EFFECTIVE_END_DATE for other primary_tax_flag = 'Y' and other l_tax_id UPDATE JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC SET SAWTC.EFFECTIVE_END_DATE = l_START_DATE - 1 WHERE SAWTC.primary_tax_flag = 'Y' AND SAWTC.SUPP_AWT_CODE_ID l_SUPP_AWT_CODE_ID_SEQ AND SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD --AND SAWTC.TAX_ID l_tax_id -- other l_tax_id AND SAWTC.EFFECTIVE_END_DATE IS NULL; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'38 .UPDATED DATA IN JL_ZZ_AP_SU P_AWT_CD_ALL FOR '|| SQL%ROWCOUNT || 'RECORDS'); FND_FILE.PUT_LINE( FND_FILE.LOG,'38. EFFECTIVE_END_DATE (L_START _DATE-1) : '|| L_START_DATE); FND_FILE.PUT_LINE( FND_FILE.LOG,'38. L_TAX_ID : '|| L_TAX_ID || 'for L_TAXPAYER_ID : '|| L_TAXPAYER_ID); END IF; INSERT INTO JL_ZZ_AP_SUP_AWT_CD_ALL (SUPP_AWT_CODE_ID, SUPP_AWT_TYPE_ID, TAX_ID, PRIMARY_TAX_FLAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) VALUES (l_SUPP_AWT_CODE_ID_SEQ, --SUPP_AWT_CODE_ID l_SUPP_AWT_TYPE_ID_CD, --SUPP_AWT_TYPE_ID l_tax_id, --TAX_ID 'Y', --PRIMARY_TAX_FLAG l_created_by, --CREATED_BY l_creation_DATE, --CREATION_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATE_login, --LAST_UPDATE_LOGIN l_ORG_ID, --ORG_ID l_START_DATE, --EFFECTIVE_START_DATE NULL); --EFFECTIVE_END_DATE IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'38 . Inserted data in JL_ZZ_AP_ SUP_AWT_CD_ALL for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_T YPE_ID_CD); END IF; COMMIT; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'39 . Failed to INSERT into JL_Z Z_AP_SUP_AWT_CD_ALL Insert not done '|| SQLCODE || 'ERROR' || SQLERRM); END IF; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'39. l_SUPP_AWT_CODE_ID_CD'|| l_ SUPP_AWT_CODE_ID_CD); END IF; BEGIN SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO taxtype_code_check, l_SU PP_AWT_CODE_ID_CD FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD AND SAWTC.TAX_ID = l_tax_id; --AND (SAWTC.EFFECTIVE_START_DATE = l_START_DATE OR SAWTC. EFFECTIVE_END_DATE = l_END_DATE); EXCEPTION WHEN NO_DATA_FOUND THEN taxtype_code_check := 'N'; END; IF taxtype_code_check = 'Y' THEN UPDATE JL_ZZ_AP_SUP_AWT_CD_ALL SET EFFECTIVE_END_DATE = NULL WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD; FND_FILE.PUT_LINE( FND_FILE.LOG,'40 Records already present in J L_ZZ_AP_SUP_AWT_CD_ALL table TYPE ID'|| l_SUPP_AWT_TYPE_ID_CD); FND_FILE.PUT_LINE( FND_FILE.LOG,'40. l_tax_id'|| l_tax_id || 'L_ EFFECTIVE_START_DATE' || L_START_DATE); NULL; ELSE ----If insertion fails , then will do the UPDATE in awt_cd_all UPDATE JL_ZZ_AP_SUP_AWT_CD_ALL SET TAX_ID = l_tax_id, PRIMARY_TAX_FLAG = 'Y', EFFECTIVE_START_DATE = l_START_DATE, EFFECTIVE_END_DATE = NULL WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD; END IF; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'41 . l_tax_id'|| l_tax_id || 'l _EFFECTIVE_START_DATE' || l_START_DATE); FND_FILE.PUT_LINE( FND_FILE.LOG,'41 . UPDATED DATA IN JL_ZZ_AP_S UP_AWT_CD_ALL for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_TY PE_ID_CD); END IF; END; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'42 . l_tax_id'|| l_tax_id || 'l_S UPP_AWT_CODE_ID_CD' || l_SUPP_AWT_CODE_ID_CD || 'l_EFFECTIVE_START_DATE-1' || l_ START_DATE); FND_FILE.PUT_LINE( FND_FILE.LOG,'42 . UPDATED DATA IN JL_ZZ_AP_SUP _AWT_CD_ALL FOR PRIMARY_TAX_FLAG AS N AND EFFECTIVE_START_DATE FOR '|| SQL%ROWCO UNT ||' RECORDS'); END IF; -- Code to insert/ UPDATE JL_ZZ_AP_INV_DIS_WH_ALL (TDD 3.3.2) BEGIN -- To get the curr ent month Supp_Awt_Code_id for the present tax_id SELECT SAWTC.SUPP_AWT_CODE_ID INTO l_SUPP_AWT_CODE_ID_CD FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PAPF WHERE SAWT.VENDOR_ID = PV.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE AND SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID --AND SAWTC.TAX_ID in (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHERE name like 'TURN_BSAS_GRP%') AND SAWTC.TAX_ID = l_tax_id AND SAWTC.primary_tax_flag = 'Y' AND SAWTC.EFFECTIVE_START_DATE = l_START_DATE AND sawtc.effective_end_DATE IS NULL AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1 099)) = TO_CHAR(l_TAXPAYER_ID); SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'43 UPDATE NOT DONE FOR JL_ZZ_AP _INV_DIS_WH_ALL - '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; END; OPEN CUR3(l_TAXPAYER_ID); LOOP FETCH CUR3 INTO INV_REC; EXIT WHEN CUR3%NOTFOUND; BEGIN AWT_CODE_INV_AVAIL_FLAG := 'N'; SELECT 'Y', INV_DISTRIB_AWT_ID INTO AWT_CODE_INV_AVAIL_FLAG, l_I NV_DISTRIB_AWT_ID_DIS FROM JL_ZZ_AP_INV_DIS_WH_ALL WHERE INVOICE_ID = INV_REC.INVOICE_ID AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUMBER AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ID AND SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD; /*(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, J L_ZZ_AP_SUPP_AWT_TYPES SAWT WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHERE name LIKE 'TURN_BSAS_GRP%')); */ EXCEPTION WHEN NO_DATA_FOUND THEN AWT_CODE_INV_AVAIL_FLAG := 'N'; FND_FILE.put_line( FND_FILE.LOG,'44 No earlier data in Inv Dist for this Invoice'|| SQLCODE ||' -ERROR- '|| SQLERRM); WHEN OTHERS THEN AWT_CODE_INV_AVAIL_FLAG := 'N'; FND_FILE.put_line( FND_FILE.LOG,'44 Error in Inv Dist select'|| SQLCODE ||' -ERROR- '|| SQLERRM); END; IF AWT_CODE_INV_AVAIL_FLAG = 'N' THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'45 | l_INV_DISTRIB_AWT_ID_INV); FND_FILE.put_line( FND_FILE.LOG,'45 REC.INVOICE_ID); FND_FILE.put_line( FND_FILE.LOG,'45 NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER); FND_FILE.put_line( FND_FILE.LOG,'45 _SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'45 INV_REC.INVOICE_DISTRIBUTION_ID); END IF; to insert data INTO jl_zz_ap_inv_dis_wh_all BEGIN INSERT INTO JL_ZZ_AP_INV_DIS_WH_ALL (INV_DISTRIB_AWT_ID, INVOICE_ID, DISTRIBUTION_LINE_NUMBER, SUPP_AWT_CODE_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, ORG_ID, INVOICE_DISTRIBUTION_ID) VALUES (l_INV_DISTRIB_AWT_ID_INV, --INV_DISTRIB_AWT_ID INV_REC.INVOICE_ID, --INVOICE_ID INV_REC.DISTRIBUTION_LINE_NUMBER, --DISTRIBUTION_LINE_NUMBER l_SUPP_AWT_CODE_ID_CD, --SUPP_AWT_CODE_ID l_created_by, --CREATED_BY l_creation_DATE, --CREATION_DATE l_last_UPDATEd_by, --LAST_UPDATED_BY l_last_UPDATE_DATE, --LAST_UPDATE_DATE l_last_UPDATE_login, --LAST_UPDATE_LOGIN l_ORG_ID, --ORG_ID INV_REC.INVOICE_DISTRIBUTION_ID); --Invoice distribution ID -- R12 Changes l_INV_DISTRIB_AWT_ID_INV'| INV_REC.INVOICE_ID'|| INV_ INV_REC.DISTRIBUTION_LINE_ l_SUPP_AWT_CODE_ID_CD'|| l INVOICE_DISTRIBUTION_ID'|| ---code SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID _INV FROM dual; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'45 A. '|| SQL%ROWCOUNT ||'I nserted records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'45 A. Failed while Insertin g records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; END; ELSE BEGIN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'45 B l_INV_DISTRIB_AWT_ID_I NV'|| l_INV_DISTRIB_AWT_ID_INV); FND_FILE.put_line( FND_FILE.LOG,'45 B INV_REC.INVOICE_ID'|| INV_REC.INVOICE_ID); FND_FILE.put_line( FND_FILE.LOG,'45 B INV_REC.DISTRIBUTION_L INE_NUMBER'|| INV_REC.DISTRIBUTION_LINE_NUMBER); FND_FILE.put_line( FND_FILE.LOG,'45 B l_SUPP_AWT_CODE_ID_CD' || l_SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'45 B l_INV_DISTRIB_AWT_ID_D IS'|| l_INV_DISTRIB_AWT_ID_DIS); FND_FILE.put_line( FND_FILE.LOG,'45 B INVOICE_DISTRIBUTION_I D'|| INV_REC.INVOICE_DISTRIBUTION_ID); END IF; /*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS FROM JL_ZZ_AP_INV_DIS_WH_ALL WHERE INVOICE_ID = INV_REC.INVOICE_ID AND DISTRIBUTION_LINE_NUMBER = INV_REC.DISTRIBUTION_LINE_NUM BER AND INVOICE_DISTRIBUTION_ID = INV_REC.INVOICE_DISTRIBUTION_ ID AND SUPP_AWT_CODE_ID IN (SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD_ALL SAWTC, JL_ZZ_AP_SUPP_AWT_TYPES SAWT WHERE SAWT.SUPP_AWT_TYPE_ID = SAWTC.SUPP_AWT_TYPE_ID AND SAWTC.primary_tax_flag = 'Y' AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES_ALL WH ERE name LIKE 'TURN_BSAS_GRP%') AND sawtc.effective_end_DATE IS NULL);*/ UPDATE JL_ZZ_AP_INV_DIS_WH_ALL SET SUPP_AWT_CODE_ID = l_SUPP_A WT_CODE_ID_CD WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'46 A. Updated '|| SQL%ROWCO UNT ||' records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); FND_FILE.put_line( FND_FILE.LOG,'46 A. l_SUPP_AWT_CODE_ID_CD :'|| l_SUPP_AWT_CODE_ID_CD); FND_FILE.put_line( FND_FILE.LOG,'46 A. l_INV_DISTRIB_AWT_ID_ DIS:'|| l_INV_DISTRIB_AWT_ID_DIS); END IF; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'46 B. Failed while updating records in JL_ZZ_AP_INV_DIS_WH_ALL '|| SQLCODE || 'ERROR' || SQLERRM); END IF; END; END IF; END LOOP; CLOSE CUR3; IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'46 C. UPDATED DATA IN JL_ZZ_AP_IN V_DIS_WH_ALL FOR SUPP_AWT_CODE_ID '); END IF; Insert_Row (l_PUBLISH_DATE, l_START_DATE, l_END_DATE, l_TAXPAYER_ID, l_CONTRIBUTOR_TYPE_CODE, l_NEW_CONTRIBUTOR_FLAG, l_RATE_CHANGE_FLAG, l_PERCEPTION_RATE, l_WHT_RATE, l_PERCEPTION_GROUP_NUM, l_WHT_GROUP_NUM, l_WHT_DEFAULT_FLAG, 'AP'); -- delete JL_AR_TURN_UPL_T WHERE TAXPAYER_ID = l_TAXPAYER_ID; IF p_debug_log = 'Y' THEN FND_FILE.put_line( FND_FILE.LOG,'47 . Inserted data in JL_AR_TURN_ UPL for All Records '); END IF; END IF; END IF; -- AP coding and validation was completed here NULL; --Re Initialising the FLAGS: ----------------------------duplicate_check_count wht_check_unique duplicate_check_flag same_rec_flag wht_check_flag := := := := := NULL; 'Y'; 'N'; 'N'; 'N'; exist_check_flag WHT_GROUP_NUM_rate_flag same_taxpayerid_flag taxtype_code_check l_taxpayer l_taxpayer_ar AWT_CODE_INV_AVAIL_FLAG := := := := := := := 'N'; 'N'; 'N'; 'N'; 'N'; 'N'; 'N'; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'49 .TAXPAYER ID FAILED IN FINAL VALID ATION, FETCHING NEXT RECORD - '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_FINAL _CHECK' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_IN FO_D2 = l_END_DATE; END; COMMIT; END LOOP; CLOSE C3; EXCEPTION WHEN OTHERS THEN IF p_debug_log = 'Y' THEN FND_FILE.PUT_LINE( FND_FILE.LOG,'49 .AN ERROR WAS ENCOUNTERED WHEN FINAL V ALIDATION - '|| SQLCODE ||' -ERROR- '|| SQLERRM); END IF; UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_FINAL_CHE CK' WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D 2 = l_END_DATE; END FINAL_VALIDATION; -- Default Setup for records not present in current TMP file (TDD 9.4.B) PROCEDURE VALIDATE_AWT_SETUP IS l_PUBLISH_DATE l_START_DATE l_END_DATE l_TAXPAYER_ID l_CONTRIBUTOR_TYPE_CODE l_NEW_CONTRIBUTOR_FLAG l_RATE_CHANGE_FLAG l_PERCEPTION_RATE DATE; DATE; DATE; NUMBER(15); VARCHAR2(1); VARCHAR2(1); VARCHAR2(1); NUMBER(15,2); l_WHT_RATE l_PERCEPTION_GROUP_NUM l_WHT_GROUP_NUM l_WHT_DEFAULT_FLAG l_TAXPAYER_ID_ALL l_START_DATE_CURR_MAX l_START_DATE_SEC_MAX l_END_DATE_SEC_MAX l_END_DATE_CURR_MAX l_PUBLISH_DATE_CURR_MAX l_EFFECTIVE_START_DATE l_WHT_GROUP_NUM_DEF_NO def_taxtype_code_check AWT_CODE_INV_AVAIL_FLAG l_WHT_GROUP_NUM_DEF_ATC l_TAX_ID_DEF_ATC l_WHT_RATE_DEF_ATR ALL_REC NUMBER(15,2); NUMBER(15); NUMBER(15); VARCHAR2(1); NUMBER(15); DATE; DATE := NULL; DATE := NULL; DATE; DATE; DATE; NUMBER; VARCHAR2(1) := 'N'; VARCHAR2(1) := 'N'; AP_TAX_CODES_ALL.NAME%TYPE; AP_TAX_CODES_ALL.TAX_ID%TYPE; AP_AWT_TAX_RATES_ALL.TAX_RATE%TYPE; JL_AR_TURN_UPL%ROWTYPE; JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID ap_tax_codes.tax_id%TYPE; JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID PO_VENDORS.VENDOR_ID%TYPE; JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ AP_INVOICES_ALL.INVOICE_ID%TYPE; AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTIO AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DIS JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID l_SUPP_AWT_CODE_ID_CD %TYPE; l_SUPP_AWT_TYPE_ID_CD %TYPE; l_tax_id l_SUPP_AWT_TYPE_ID_TYPES %TYPE; l_VENDOR_ID l_INV_DISTRIB_AWT_ID_INV ID%TYPE; l_INVOICE_ID_INA l_DISTRIBUTION_LINE_NUMBER_IND N_LINE_NUMBER%TYPE; l_INVOICE_DISTRIBUTION_ID_IND TRIBUTION_ID%TYPE; l_INV_DISTRIB_AWT_ID_DIS ID%TYPE; l_SUPP_AWT_CODE_ID_SEQ %TYPE; --WHO COLUMNS: ----------------l_created_by l_creation_DATE l_last_UPDATEd_by l_last_UPDATE_DATE l_last_UPDATE_login l_ORG_ID := 3687; NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1); DATE := SYSDATE; NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1); DATE := SYSDATE; NUMBER(15) := NVL(fnd_global.conc_login_id, 1); NUMBER(15) := oe_profile.value('SO_ORGANIZATION_ID'); -- CURSOR CUR4(l_TAXPAYERID_C NUMBER) IS SELECT APINA.INVOICE_ID, APIND.DISTRIBUTION_LINE_NUMBER, APIND.INVOICE_DISTRIB UTION_ID FROM PO_VENDORS PV, AP_INVOICES_ALL APINA, AP_INVOICE_DISTRIBUTIONS_ALL APIND, PER_ALL_PEOPLE_F PAPF WHERE PV.VENDOR_ID = APINA.VENDOR_ID AND NVL(pv.employee_id, - 99) = papf.person_id (+) AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) = SYSDATE --AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES_ALL WHERE na me like 'TURN_BSAS_GRP%') AND APINA.INVOICE_ID = APIND.INVOICE_ID AND APIND.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND APIND.GLOBAL_ATTRIBUTE3 IN (SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = 'Buenos Aires' AND trunc(SYSDATE)