[Home] [Help]
125: c_tax_regime_code varchar2,
126: c_tax varchar2,
127: c_tax_jurisdiction_code varchar2) IS
128: SELECT distinct ptp.party_id
129: FROM zx_registrations reg,
130: zx_party_tax_profile ptp
131: WHERE ptp.party_tax_profile_id = reg.party_tax_profile_id
132: AND reg.registration_number = c_registration_number
133: AND sysdate >= reg.effective_from
168:
169:
170: CURSOR c_third_party_reg_num (c_ptp_id number, c_tax_reg_num varchar2) IS
171: SELECT registration_number
172: FROM zx_registrations
173: WHERE party_tax_profile_id = (SELECT s.party_tax_profile_id
174: FROM zx_party_tax_profile s,
175: zx_party_tax_profile ptp,
176: hz_party_sites hzps
181: and registration_number = c_tax_reg_num;
182:
183: CURSOR c_establishment_reg_num (c_ptp_id number, c_tax_reg_num varchar2) IS
184: SELECT regt.registration_number
185: FROM zx_registrations regt
186: where regt.party_tax_profile_id = (SELECT ptpp.party_tax_profile_id
187: FROM xle_fp_establishment_v est,
188: xle_fp_establishment_v estp,
189: zx_party_tax_profile ptp,
403: HZP.ADDRESS4||' '||
404: HZP.CITY||' '||
405: HZP.STATE) PARTY_SITE_NAME
406: INTO l_party_name, l_party_site_name
407: FROM ZX_REGISTRATIONS REG,
408: HZ_PARTY_SITES HZS,
409: HZ_PARTIES HZP,
410: ZX_PARTY_TAX_PROFILE ZXP
411: WHERE HZP.PARTY_ID = HZS.PARTY_ID
449: AND lookup_code = 'THIRD_PARTY';
450:
451: SELECT HZP.PARTY_NAME
452: INTO l_party_name
453: FROM ZX_REGISTRATIONS REG,
454: HZ_PARTIES HZP,
455: ZX_PARTY_TAX_PROFILE ZXP
456: WHERE HZP.PARTY_ID = ZXP.PARTY_ID
457: AND ZXP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
605: AND ptp.party_tax_profile_id <> c_ptp_id;
606:
607: CURSOR trn_exists_in_reg_tbl(c_ptp_id number,c_registration_number varchar2) IS
608: SELECT party_tax_profile_id
609: FROM zx_registrations
610: WHERE party_tax_profile_id = c_ptp_id
611: AND registration_number = c_registration_number
612: AND TRUNC(sysdate) between effective_from and NVL(effective_to,sysdate+1);
613:
1038: FROM zx_party_tax_profile
1039: where party_tax_profile_id = p_ptp_id
1040: and rep_registration_number = p_tax_reg_num
1041: and record_type_code = 'MIGRATED'
1042: and not exists(SELECT 1 FROM zx_registrations reg
1043: where reg.party_tax_profile_id = p_ptp_id
1044: and reg.registration_number = p_tax_reg_num);
1045:
1046: p_return_status := FND_API.G_RET_STS_SUCCESS;
1062: IF p_tax_regime_code IS NULL THEN
1063: BEGIN
1064: SELECT allow_dup_regn_num_flag
1065: INTO l_allow_regn_num_flag
1066: FROM zx_registrations reg, zx_taxes_b t
1067: WHERE reg.party_tax_profile_id = NVL(p_ptp_id,G_INVALID_PTP_ID)
1068: AND reg.registration_number = p_tax_reg_num
1069: AND TRUNC(sysdate) between reg.effective_from and NVL(reg.effective_to,sysdate+1)
1070: AND t.tax_regime_code = reg.tax_regime_code
1081: END;
1082: BEGIN
1083: SELECT t.allow_dup_regn_num_flag
1084: INTO l_allow_regn_num_flag
1085: FROM zx_registrations reg,zx_taxes_b t,zx_party_tax_profile ptp
1086: WHERE ptp.party_tax_profile_id = NVL(p_ptp_id,G_INVALID_PTP_ID)
1087: AND ptp.rep_registration_number = p_tax_reg_num
1088: AND reg.registration_number = p_tax_reg_num
1089: AND reg.party_tax_profile_id <> p_ptp_id
1226: -- Added for Cross Registration number validation (6774002)
1227: BEGIN
1228: SELECT COUNT(REG.REGISTRATION_NUMBER)
1229: INTO l_total_count
1230: FROM ZX_REGISTRATIONS REG,
1231: ZX_PARTY_TAX_PROFILE PTP
1232: WHERE PTP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
1233: AND REG.REGISTRATION_NUMBER = l_trn
1234: AND PTP.PARTY_TAX_PROFILE_ID <> G_PTP_ID
1250: IF L_PARTY_TYPE_CODE = 'THIRD_PARTY' THEN
1251: BEGIN
1252: SELECT COUNT(REGISTRATION_NUMBER)
1253: INTO l_specific_count
1254: FROM ZX_REGISTRATIONS REG
1255: WHERE REG.PARTY_TAX_PROFILE_ID IN
1256: (SELECT S.PARTY_TAX_PROFILE_ID
1257: FROM ZX_PARTY_TAX_PROFILE S,
1258: ZX_PARTY_TAX_PROFILE PTP,