[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE JL_AR_UPDATE_CUST_SITE_TAX
(P_TAXPAYER_ID IN NUMBER,
P_TAX_TYPE IN VARCHAR2 := 'TURN_BSAS',
P_CATEG IN VARCHAR2 := 'TOPBA',
P_ORG_ID IN NUMBER,
P_PUBLISH_DATE IN DATE,
P_START_DATE IN DATE,
P_END_DATE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
CURSOR FIND_CT_FOR_TAXPAYER(C_TAXPAYER_ID NUMBER) IS
SELECT CUST.CUST_ACCOUNT_ID
FROM HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS CUST
WHERE CUST.PARTY_ID = PARTY.PARTY_ID
AND PARTY.JGZZ_FISCAL_CODE = To_Char(C_TAXPAYER_ID);
SELECT HZSU.SITE_USE_ID
FROM HZ_PARTIES HZP,
HZ_CUST_ACCOUNTS HZCA,
HZ_CUST_ACCT_SITES HZAS,
HZ_CUST_SITE_USES HZSU
WHERE HZCA.CUST_ACCOUNT_ID = C_CUSTOMER_ID
AND 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 = P_ORG_ID
ORDER BY HZSU.SITE_USE_ID;
SELECT * FROM JL_AR_TURN_UPL_T
WHERE TAXPAYER_ID = C_TAX_PAYER_ID;
FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','JL_AR_UPDATE_CUST_SITE_TAX(+)');
SELECT 'Y'
INTO l_exist_in_tmp
FROM JL_AR_TURN_UPL_T
WHERE TAXPAYER_ID = P_TAXPAYER_ID;
SELECT 'Y'
INTO l_proper_tax_rate_code
FROM AR_VAT_TAX VAT,
JL_ZZ_AR_TX_CATEG JZ
WHERE VAT.ORG_ID = P_ORG_ID
AND VAT.GLOBAL_ATTRIBUTE1 = JZ.TAX_CATEGORY_ID
AND VAT.ORG_ID = JZ.ORG_ID
AND JZ.TAX_CATEGORY = P_CATEG
AND VAT.ENABLED_FLAG = 'Y'
AND VAT.TAX_CODE = P_TAX_TYPE||'_GRP'||l_perception_group_num
AND VAT.TAX_RATE = l_perception_rate
AND VAT.START_DATE <= P_START_DATE
AND NVL(VAT.END_DATE,TO_DATE('31/12/4092','DD/MM/RRRR'))
>= NVL(P_END_DATE,LAST_DAY(P_START_DATE));
UPDATE JGZZ_AR_TAX_GLOBAL_TMP
SET JG_INFO_V1 = 'JL_AR_AR_GRP_NO_MATCH'
WHERE JG_INFO_N1 = P_TAXPAYER_ID
AND JG_INFO_D1 = P_START_DATE
AND JG_INFO_D2 = P_END_DATE;
SELECT Max(JALL.START_DATE),Max(JALL.END_DATE)
INTO l_start_date_sec_max,l_end_date_sec_max
FROM JL_AR_TURN_UPL_ALL JALL
WHERE JALL.TAXPAYER_ID = P_TAXPAYER_ID;
THE TAXPAYER ID NOT EXISTS IN THE LAST MONTH THEN INSERT THE RECORDS
INTO THE FINAL TABLE AND UPDATE THE CUST SITE WITH THE TAXC0DE GIVEN.*/
IF l_exist_in_all = 'N' THEN
IF Nvl(l_proper_tax_rate_code,'N') = 'Y' THEN
l_data_change_flag := 'Y';
SELECT DISTINCT 'Y'
INTO l_data_change_flag
FROM HZ_PARTIES HZP,
HZ_CUST_ACCOUNTS HZCA,
HZ_CUST_ACCT_SITES HZAS,
HZ_CUST_SITE_USES HZSU
WHERE HZP.JGZZ_FISCAL_CODE = To_Char(l_taxpayer_id)
AND 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 = P_ORG_ID
AND (HZSU.TAX_CODE IS NULL OR
HZSU.TAX_CODE <> P_TAX_TYPE||'_GRP'||l_perception_group_num);
SELECT VAT.TAX_CODE, VAT.TAX_RATE
INTO l_def_tax_code, l_def_tax_rate
FROM AR_VAT_TAX VAT,
JL_ZZ_AR_TX_CATEG JZ
WHERE VAT.GLOBAL_ATTRIBUTE7 = 'Y'
AND VAT.ORG_ID = P_ORG_ID
AND VAT.GLOBAL_ATTRIBUTE1 = JZ.TAX_CATEGORY_ID
AND VAT.ORG_ID = JZ.ORG_ID
AND JZ.TAX_CATEGORY = P_CATEG
AND VAT.ENABLED_FLAG = 'Y'
AND VAT.START_DATE <= P_START_DATE
AND NVL(VAT.END_DATE,TO_DATE('31/12/4092','DD/MM/RRRR'))
>= NVL(P_END_DATE,LAST_DAY(P_START_DATE));
UPDATE JGZZ_AR_TAX_GLOBAL_TMP
SET JG_INFO_V1 = 'JL_AR_AR_NO_DFLT_FLAG_SET'
WHERE JG_INFO_N1 = P_TAXPAYER_ID
AND JG_INFO_D1 = P_START_DATE
AND JG_INFO_D2 = P_END_DATE;
SELECT Max(JALL.START_DATE),Max(JALL.END_DATE)
INTO l_start_date_sec_max,l_end_date_sec_max
FROM JL_AR_TURN_UPL_ALL JALL
WHERE JALL.TAXPAYER_ID = P_TAXPAYER_ID;
SELECT CONTRIBUTOR_TYPE_CODE,
NEW_CONTRIBUTOR_FLAG,
PERCEPTION_RATE,
WHT_RATE,
WHT_GROUP_NUM
INTO l_contributor_type_code,
l_new_contributor_flag,
l_perception_rate,
l_wht_rate,
l_wht_group_num
FROM JL_AR_TURN_UPL_ALL
WHERE TAXPAYER_ID = P_TAXPAYER_ID
AND START_DATE = l_start_date_sec_max
AND END_DATE = l_end_date_sec_max;
JL_AR_APPLICABLE_TAXES.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_def_tax_flag,
'AR');
RAISE_APPLICATION_ERROR(-20999,'Failed when inserting the details for given Taxpayer: '
||l_taxpayer_id||' - '||SQLCODE||'- ERROR - '||SQLERRM);
'Updated Site Use Ids:'||l_site_use_id);
SELECT OBJECT_VERSION_NUMBER
INTO l_obj_version
FROM HZ_CUST_SITE_USES
WHERE SITE_USE_ID = l_site_use_id;
HZ_CUST_ACCOUNT_SITE_V2PUB.update_cust_site_use (
FND_API.G_TRUE,
l_cust_site_use_rec,
l_obj_version,
x_return_status,
x_msg_count,
x_msg_data );
FND_LOG.STRING(g_level_statement,'ZX.PLSQL.JL_ZZ_APPLICABLE_TAXES','JL_AR_UPDATE_CUST_SITE_TAX(-)');
END JL_AR_UPDATE_CUST_SITE_TAX;