[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row (l_PUBLISH_DATE DATE,
l_START_DATE DATE,
l_END_DATE DATE,
l_TAXPAYER_ID NUMBER,
l_CONTRIBUTOR_TYPE_CODE VARCHAR2,
l_NEW_CONTRIBUTOR_FLAG VARCHAR2,
l_RATE_CHANGE_FLAG VARCHAR2,
l_PERCEPTION_RATE NUMBER,
l_WHT_RATE NUMBER,
l_PERCEPTION_GROUP_NUM NUMBER,
l_WHT_GROUP_NUM NUMBER,
l_WHT_DEFAULT_FLAG VARCHAR2,
l_CALLING_RESP VARCHAR2
) IS
final_insert_check VARCHAR2(1) := 'N';
l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
l_last_UPDATE_DATE DATE := SYSDATE;
l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
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;
final_insert_check := 'N';
FND_FILE.put_line( FND_FILE.LOG, 'In Insert_Row, value of final_insert_check'|| final_insert_check);
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
FND_FILE.put_line( FND_FILE.LOG, 'In Insert_Row, inserted record into JL_AR_TURN_UPL');
UPDATE JL_AR_TURN_UPL SET WHT_RATE = l_WHT_RATE, WHT_GROUP_NUM = l_WHT_GROUP_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;
FND_FILE.put_line( FND_FILE.LOG, 'UPDATED record in JL_AR_TURN_UPL for Payables');
UPDATE JL_AR_TURN_UPL SET PERCEPTION_RATE = l_PERCEPTION_RATE, PERCEPTION_GROUP_NUM = l_PERCEPTION_GROUP_NUM
WHERE TAXPAYER_ID = l_TAXPAYER_ID
AND START_DATE = l_START_DATE
AND END_DATE = l_END_DATE;
FND_FILE.put_line( FND_FILE.LOG, 'UPDATED record in JL_AR_TURN_UPL for Receivables');
FND_FILE.put_line( FND_FILE.LOG,'AN ERROR IS ENCOUNTERED WHILE INSERTING INTO FINAL TABLE '|| SQLCODE || 'ERROR' || SQLERRM);
END Insert_Row;
SELECT MAX(PUBLISH_DATE) INTO l_PUBLISHDATE_ALL FROM JL_AR_TURN_UPL
WHERE TAXPAYER_ID = l_TAXPAYER_ID;
SELECT /*+ cardinality(PV.PAV,1) */ 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
-- Bug 14563117 End
WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
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);
UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_TAXPAYER_ID_NOT_AVAILABLE'
WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
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_D2 = l_END_DATE;
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_D2 = l_END_DATE;
SELECT APINA.INVOICE_ID,
APIND.DISTRIBUTION_LINE_NUMBER,
APIND.INVOICE_DISTRIBUTION_ID
FROM PO_VENDORS PV,
AP_INVOICES APINA,
AP_INVOICE_DISTRIBUTIONS 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 NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
--AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name 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 UPPER(LOCATION_CODE) = 'BUENOS AIRES' --bug 8622329
(SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(REGION_2) = 'BUENOS AIRES' --bug 9865805
AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE)))
AND NVL(APINA.INVOICE_AMOUNT,0) <> NVL(APINA.AMOUNT_PAID,0)
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYERID_C);
AND pv.vendor_id in (select vendor_id from po_vendor_sites_all where org_id = l_org_id); --bug 10091261
SELECT * FROM JL_AR_TURN_UPL_T;
l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
l_last_UPDATE_DATE DATE := SYSDATE;
l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
SELECT COUNT(*) INTO duplicate_check_count FROM
(SELECT DISTINCT PUBLISH_DATE, START_DATE, END_DATE FROM JL_AR_TURN_UPL_T) TMP;
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;
INSERT INTO JGZZ_AR_TAX_GLOBAL_TMP(JG_INFO_N1, JG_INFO_D1, JG_INFO_D2, JG_INFO_V1)
VALUES (l_TAXPAYER_ID, l_START_DATE, l_END_DATE, NULL);
JL_ZZ_AR_UPLOAD_TAXES.JL_AR_UPDATE_CUST_SITE_TAX(l_TAXPAYER_ID,
l_AWT_TAX_TYPE,
l_PERCEPTION_TAX_TYPE,
l_ORG_ID,
l_PUBLISH_DATE,
l_START_DATE,
l_END_DATE,
l_RETURN_STATUS ); -- out parameter for status
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 WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
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
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
WHERE name = l_WHT_GROUP_NUM_ALL
AND tax_type = 'AWT';
UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_MANY_WHT_RATE_GROUP'
WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATCH'
WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JL_AR_AP_GRP_NO_MATCH'
WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
SELECT DISTINCT 'Y' INTO same_prev_rec_flag FROM JL_AR_TURN_UPL WHERE
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;
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');
FND_FILE.PUT_LINE( FND_FILE.LOG,'10 .INSERTED DATA IN JL_AR_TURN_UPL SAME_REC_FLAG IS Y AND DELETED FROM TMP TABLE');
SELECT DISTINCT 'Y' INTO same_taxpayerid_flag FROM JL_AR_TURN_UPL WHERE
PUBLISH_DATE <> l_PUBLISH_DATE
AND START_DATE <> l_START_DATE
AND END_DATE <> l_END_DATE
AND TAXPAYER_ID = l_TAXPAYER_ID;
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;
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');
FND_FILE.PUT_LINE( FND_FILE.LOG,'16 .INSERTED DATA IN TABLE JL_AR_TURN_UPL WHEN WHT_CHECK_FLAG IS Y ');
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 NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
SELECT 'Y',
SAWTC.SUPP_AWT_CODE_ID
INTO taxtype_code_check,
l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD 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);
UPDATE JL_ZZ_AP_SUP_AWT_CD 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;
FND_FILE.PUT_LINE( FND_FILE.LOG,'19 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
INSERT INTO JL_ZZ_AP_SUP_AWT_CD
(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
FND_FILE.PUT_LINE( FND_FILE.LOG,'21 . INSERTED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
FND_FILE.PUT_LINE( FND_FILE.LOG,'21. INSERT NOT DONE IN JL_ZZ_AP_SUP_AWT_CD '|| SQLCODE ||' -ERROR- '|| SQLERRM);
SELECT SAWTC.SUPP_AWT_CODE_ID INTO l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD 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 NVL(papf.EFFECTIVE_END_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 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
--bug 8530918 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_INV FROM dual;
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);
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
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 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 WHERE name LIKE 'TURN_BSAS_GRP%')); */
BEGIN ---code to insert data INTO jl_zz_ap_inv_dis_wh_all
INSERT INTO JL_ZZ_AP_INV_DIS_WH
(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
SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
FND_FILE.put_line( FND_FILE.LOG,'22. C '|| SQL%ROWCOUNT ||'Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
FND_FILE.put_line( FND_FILE.LOG,'22. C Failed while Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
/*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
FROM JL_ZZ_AP_INV_DIS_WH
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 IN
(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD 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 WHERE name LIKE 'TURN_BSAS_GRP%')
AND sawtc.effective_end_DATE IS NULL);*/
UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
FND_FILE.put_line( FND_FILE.LOG,'23 B. Updated '|| SQL%ROWCOUNT ||' records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG,'24. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
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');
FND_FILE.put_line( FND_FILE.LOG,'26 . Inserted data in JL_AR_TURN_UPL for All Records ');
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');
FND_FILE.put_line( FND_FILE.LOG,'28. Inserted data in JL_AR_TURN_UPL when same_rec_flag is N AND l_RATE_CHANGE_FLAG is N');
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 WHERE START_DATE = l_START_DATE
AND END_DATE = l_END_DATE
AND PUBLISH_DATE = l_PUBLISH_DATE
AND TAXPAYER_ID = l_TAXPAYER_ID);
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 WHERE TAXPAYER_ID = l_TAXPAYER_ID AND
END_DATE NOT IN (SELECT MAX(END_DATE) FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID);
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;
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 JG_INFO_D2 = l_END_DATE;
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_TYPES FROM dual;
SELECT PV.VENDOR_ID INTO l_VENDOR_ID 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.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
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
FND_FILE.put_line( FND_FILE.LOG,'34 . Inserted data in JL_ZZ_AP_SUPP_AWT_TYPES for '|| SQL%ROWCOUNT ||' Records');
FND_FILE.put_line( FND_FILE.LOG,'35 .Insert in JL_ZZ_AP_SUPP_AWT_TYPES not done - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
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 NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
AND SAWT.AWT_TYPE_CODE = l_AWT_TAX_TYPE
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
UPDATE JL_ZZ_AP_SUP_AWT_CD 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;
FND_FILE.PUT_LINE( FND_FILE.LOG,'38 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
INSERT INTO JL_ZZ_AP_SUP_AWT_CD
(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
FND_FILE.put_line( FND_FILE.LOG,'38 . Inserted data in JL_ZZ_AP_SUP_AWT_CD for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_TYPE_ID_CD);
FND_FILE.put_line( FND_FILE.LOG,'39 . Failed to INSERT into JL_ZZ_AP_SUP_AWT_CD Insert not done '|| SQLCODE || 'ERROR' || SQLERRM);
SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO taxtype_code_check, l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD SAWTC
WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
AND SAWTC.TAX_ID = l_tax_id;
UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = NULL
WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
UPDATE JL_ZZ_AP_SUP_AWT_CD 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;
FND_FILE.PUT_LINE( FND_FILE.LOG,'41 . UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD for '|| SQL%ROWCOUNT ||' Records and code id is :'|| l_SUPP_AWT_TYPE_ID_CD);
FND_FILE.PUT_LINE( FND_FILE.LOG,'42 . UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR PRIMARY_TAX_FLAG AS N AND EFFECTIVE_START_DATE FOR '|| SQL%ROWCOUNT ||' RECORDS');
SELECT SAWTC.SUPP_AWT_CODE_ID
INTO l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD 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 NVL(papf.EFFECTIVE_END_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 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
--bug 8530918 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_INV FROM dual;
FND_FILE.PUT_LINE( FND_FILE.LOG,'43 UPDATE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
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
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 SAWTC, JL_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 WHERE name LIKE 'TURN_BSAS_GRP%')); */
FND_FILE.put_line( FND_FILE.LOG,'44 Error in Inv Dist select'|| SQLCODE ||' -ERROR- '|| SQLERRM);
INSERT INTO JL_ZZ_AP_INV_DIS_WH
(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
SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
FND_FILE.put_line( FND_FILE.LOG,'45 A. '|| SQL%ROWCOUNT ||'Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
FND_FILE.put_line( FND_FILE.LOG,'45 A. Failed while Inserting records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
/*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
FROM JL_ZZ_AP_INV_DIS_WH
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 IN
(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD 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 WHERE name LIKE 'TURN_BSAS_GRP%')
AND sawtc.effective_end_DATE IS NULL);*/
UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
FND_FILE.put_line( FND_FILE.LOG,'46 A. Updated '|| SQL%ROWCOUNT ||' records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG,'46 C. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
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');
FND_FILE.put_line( FND_FILE.LOG,'47 . Inserted data in JL_AR_TURN_UPL for All Records ');
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_INFO_D2 = l_END_DATE;
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_INFO_D2 = l_END_DATE;
l_last_UPDATEd_by NUMBER(15) := NVL(fnd_profile.value('USER_ID'), 1);
l_last_UPDATE_DATE DATE := SYSDATE;
l_last_UPDATE_login NUMBER(15) := NVL(fnd_global.conc_login_id, 1);
SELECT APINA.INVOICE_ID,
APIND.DISTRIBUTION_LINE_NUMBER,
APIND.INVOICE_DISTRIBUTION_ID
FROM PO_VENDORS PV,
AP_INVOICES APINA,
AP_INVOICE_DISTRIBUTIONS 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 NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
--AND APIND.TAX_CODE_ID in (SELECT TAX_ID FROM AP_TAX_CODES WHERE name 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 UPPER(LOCATION_CODE) = 'BUENOS AIRES' AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE))) --bug 8622329
(SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE UPPER(REGION_2) = 'BUENOS AIRES' AND trunc(SYSDATE) <= NVL(inactive_DATE, trunc(SYSDATE))) --bug 9865805
AND APINA.INVOICE_ID = APIND.INVOICE_ID
AND NVL(APINA.INVOICE_AMOUNT,0) <> NVL(APINA.AMOUNT_PAID,0)
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYERID_C);
AND pv.vendor_id in (select vendor_id from po_vendor_sites_all where org_id = l_org_id); --bug 10091261
SELECT * FROM JL_AR_TURN_UPL WHERE START_DATE <> l_START_DATE_CURR_MAX;
SELECT MAX(START_DATE) INTO l_START_DATE_CURR_MAX FROM JL_AR_TURN_UPL;
SELECT MAX(END_DATE) INTO l_END_DATE_CURR_MAX FROM JL_AR_TURN_UPL;
SELECT name
INTO l_WHT_GROUP_NUM_DEF_ATC
FROM AP_TAX_CODES ATC
WHERE global_attribute1 = 'Y'
AND tax_type = 'AWT';
SELECT tax_id
INTO l_TAX_ID_DEF_ATC
FROM AP_TAX_CODES
WHERE name = l_WHT_GROUP_NUM_DEF_ATC
AND tax_type = 'AWT';
SELECT TAX_RATE
INTO l_WHT_RATE_DEF_ATR
FROM AP_AWT_TAX_RATES
WHERE TAX_NAME = l_WHT_GROUP_NUM_DEF_ATC;
SELECT TRIM(leading '0' FROM (SUBSTR(tax_name, 14, 2)))
INTO l_WHT_GROUP_NUM_DEF_NO
FROM AP_AWT_TAX_RATES
WHERE tax_name = l_WHT_GROUP_NUM_DEF_ATC;
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.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
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);
JL_ZZ_AR_UPLOAD_TAXES.JL_AR_UPDATE_CUST_SITE_TAX(l_TAXPAYER_ID,
l_AWT_TAX_TYPE,
l_PERCEPTION_TAX_TYPE,
l_ORG_ID,
l_PUBLISH_DATE,
l_START_DATE_CURR_MAX,
l_END_DATE_CURR_MAX,
l_RETURN_STATUS ); -- out parameter for status
SELECT MAX(START_DATE) INTO l_START_DATE_SEC_MAX FROM JL_AR_TURN_UPL WHERE TAXPAYER_ID = l_TAXPAYER_ID;
SELECT last_day(l_START_DATE_SEC_MAX) INTO l_END_DATE_SEC_MAX FROM dual;
SELECT SAWTC.SUPP_AWT_TYPE_ID,
SAWTC.SUPP_AWT_CODE_ID
INTO l_SUPP_AWT_TYPE_ID_CD,
l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD 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 NVL(papf.EFFECTIVE_END_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.primary_tax_flag = 'Y'
--AND SAWT.WH_SUBJECT_FLAG = 'Y' -- Indicates whether the supplier is subject to the withholding tax type
AND SAWTC.TAX_ID IN (SELECT TAX_ID FROM AP_TAX_CODES WHERE name LIKE 'TURN_BSAS_GRP%')
AND SAWTC.EFFECTIVE_END_DATE IS NULL
--bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = l_END_DATE_SEC_MAX
WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
FND_FILE.PUT_LINE( FND_FILE.LOG,'52 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR '|| SQL%ROWCOUNT || 'RECORDS');
SELECT jl_zz_ap_sup_awt_cd_s.nextval INTO l_SUPP_AWT_CODE_ID_SEQ FROM dual;
INSERT INTO JL_ZZ_AP_SUP_AWT_CD
(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_DEF_ATC, -- default tax code 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_CURR_MAX, --EFFECTIVE_START_DATE
NULL); --EFFECTIVE_END_DATE
FND_FILE.PUT_LINE( FND_FILE.LOG,'53 .INSERTED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR SUPP_AWT_CODE_ID '|| L_SUPP_AWT_CODE_ID_SEQ);
FND_FILE.PUT_LINE( FND_FILE.LOG,'53. INSERTED NOT DONE FOR JL_ZZ_AP_SUP_AWT_CD '|| SQLCODE ||' -ERROR- '|| SQLERRM);
SELECT 'Y', SAWTC.SUPP_AWT_CODE_ID INTO def_taxtype_code_check, l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD SAWTC
WHERE SAWTC.SUPP_AWT_TYPE_ID = l_SUPP_AWT_TYPE_ID_CD
AND SAWTC.TAX_ID = l_TAX_ID_DEF_ATC;
UPDATE JL_ZZ_AP_SUP_AWT_CD SET EFFECTIVE_END_DATE = NULL
WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
UPDATE JL_ZZ_AP_SUP_AWT_CD SET
TAX_ID = l_TAX_ID_DEF_ATC, --- default tax code id
PRIMARY_TAX_FLAG = 'Y',
EFFECTIVE_START_DATE = l_START_DATE_CURR_MAX,
EFFECTIVE_END_DATE = NULL
WHERE SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD;
FND_FILE.PUT_LINE( FND_FILE.LOG,'54 .UPDATED DATA IN JL_ZZ_AP_SUP_AWT_CD FOR SUPP_AWT_CODE_ID WITH OLD TAX_ID for CODE ID'|| l_SUPP_AWT_CODE_ID_CD);
SELECT SAWTC.SUPP_AWT_CODE_ID
INTO l_SUPP_AWT_CODE_ID_CD
FROM JL_ZZ_AP_SUP_AWT_CD 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 NVL(papf.EFFECTIVE_END_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 = l_TAX_ID_DEF_ATC -- to pick up the tax code WHERE tax id is def ATC
AND SAWTC.primary_tax_flag = 'Y'
--AND SAWT.WH_SUBJECT_FLAG = 'Y'
AND sawtc.effective_end_DATE IS NULL
--bug 8530918 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_INV FROM dual;
FND_FILE.PUT_LINE( FND_FILE.LOG,'54 UPDATE NOT DONE FOR JL_ZZ_AP_INV_DIS_WH - '|| SQLCODE ||' -ERROR- '|| SQLERRM);
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
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 SAWTC, JL_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 WHERE name LIKE 'TURN_BSAS_GRP%')); */
INSERT INTO JL_ZZ_AP_INV_DIS_WH
(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, --Def 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
SELECT JL_ZZ_AP_INV_DIS_WH_S.NEXTVAL INTO l_INV_DISTRIB_AWT_ID_INV FROM dual;
FND_FILE.PUT_LINE( FND_FILE.LOG,'55 A. INSERTED RECORDS INTO JL_ZZ_AP_INV_DIS_WH_S '|| SQL%ROWCOUNT || 'RECORDS' || SQLCODE ||' -ERROR- '|| SQLERRM);
FND_FILE.put_line( FND_FILE.LOG,'55 A. Failed while Inserted records in JL_ZZ_AP_INV_DIS_WH '|| SQLCODE || 'ERROR' || SQLERRM);
/*SELECT INV_DISTRIB_AWT_ID INTO l_INV_DISTRIB_AWT_ID_DIS
FROM JL_ZZ_AP_INV_DIS_WH
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 IN
(SELECT SUPP_AWT_CODE_ID FROM JL_ZZ_AP_SUP_AWT_CD 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 WHERE name LIKE 'TURN_BSAS_GRP%')
AND sawtc.effective_end_DATE IS NULL);*/
UPDATE JL_ZZ_AP_INV_DIS_WH SET SUPP_AWT_CODE_ID = l_SUPP_AWT_CODE_ID_CD
WHERE INV_DISTRIB_AWT_ID = l_INV_DISTRIB_AWT_ID_DIS;
FND_FILE.PUT_LINE( FND_FILE.LOG,'55 C.UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID WITH CURRENT (DEF) TAX_ID for'|| SQL%ROWCOUNT || 'RECORD' || SQLCODE ||' -ERROR- '|| SQLERRM);
FND_FILE.PUT_LINE( FND_FILE.LOG,'56. UPDATED DATA IN JL_ZZ_AP_INV_DIS_WH FOR SUPP_AWT_CODE_ID ');
SELECT * INTO ALL_REC FROM JL_AR_TURN_UPL
WHERE START_DATE = l_START_DATE_SEC_MAX AND TAXPAYER_ID = l_TAXPAYER_ID;
Insert_Row (ALL_REC.PUBLISH_DATE, -- inserting the old original publish DATE FROM government
l_START_DATE_CURR_MAX, -- inserting the current max start and end DATEs
l_END_DATE_CURR_MAX,
ALL_REC.TAXPAYER_ID,
ALL_REC.CONTRIBUTOR_TYPE_CODE,
ALL_REC.NEW_CONTRIBUTOR_FLAG,
ALL_REC.RATE_CHANGE_FLAG,
ALL_REC.PERCEPTION_RATE,
l_WHT_RATE_DEF_ATR, -- inserting the defualt tax rate
ALL_REC.PERCEPTION_GROUP_NUM,
l_WHT_GROUP_NUM_DEF_NO, -- inserting the defualt tax group
'Y',
'AP');
UPDATE JGZZ_AR_TAX_GLOBAL_TMP SET JG_INFO_V1 = 'JLZZ_RECORD_FAILED_AWT_SETUP'
WHERE JG_INFO_N1 = l_TAXPAYER_ID AND JG_INFO_D1 = l_START_DATE AND JG_INFO_D2 = l_END_DATE;
DELETE JGZZ_AR_TAX_GLOBAL_TMP; -- Truncating previous records, if any, in JGZZ_AR_TAX_GLOBAL_TMP table
FND_FILE.put_line( FND_FILE.LOG,'------------------------------- VALID PARAMETER SELECTION CRITERIA ---------------------------------');
FND_FILE.put_line( FND_FILE.LOG,' To view only the Valid Data (After validation), Enter the Review Temporary Data as NO, Validate Temporary Data as NO, Finalize Data as NO and select the Responsibility and Dates Appropriately');
SELECT MAX(start_DATE) INTO P_START_DATE FROM JL_AR_TURN_UPL;
SELECT MAX(end_DATE) INTO P_END_DATE FROM JL_AR_TURN_UPL;