The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct ptp.party_id
FROM zx_registrations reg,
zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = reg.party_tax_profile_id
AND reg.registration_number = c_registration_number
AND sysdate >= reg.effective_from
AND (sysdate <= reg.effective_to OR reg.effective_to IS NULL)
AND ptp.party_type_code = c_party_type_code
AND ((c_tax_regime_code IS NULL)
OR
(reg.tax_regime_code IS NULL)
OR
(c_tax_regime_code IS NOT NULL
AND reg.tax_regime_code = c_tax_regime_code))
AND ((c_tax IS NULL)
OR
(reg.tax IS NULL)
OR
(c_tax IS NOT NULL
AND reg.tax = c_tax))
AND ((c_tax_jurisdiction_code IS NULL)
OR
(reg.tax_jurisdiction_code IS NULL)
OR
(c_tax_jurisdiction_code IS NOT NULL
AND reg.tax_jurisdiction_code = c_tax_jurisdiction_code));
SELECT distinct party_type_code
FROM zx_party_tax_profile
WHERE party_tax_profile_id = c_ptp_id;
SELECT allow_dup_regn_num_flag
FROM zx_taxes_b
WHERE tax_regime_code = p_tax_regime_code
AND tax = p_tax;
select registration_number
from zx_registrations
where party_tax_profile_id = (select s.party_tax_profile_id
from zx_party_tax_profile s,
zx_party_tax_profile ptp,
hz_party_sites hzps
where ptp.party_tax_profile_id = c_ptp_id
and ptp.party_id = hzps.party_site_id
and hzps.party_id = s.party_id
and s.party_type_code = 'THIRD_PARTY')
and registration_number = c_tax_reg_num;
select regt.registration_number
from zx_registrations regt
where regt.party_tax_profile_id = (select ptpp.party_tax_profile_id
from xle_fp_establishment_v est,
xle_fp_establishment_v estp,
zx_party_tax_profile ptp,
zx_party_tax_profile ptpp
where estp.party_id = ptpp.party_id
and estp.legal_entity_id = est.legal_entity_id
and est.party_id = ptp.party_id
and ptp.party_tax_profile_id = c_ptp_id
and estp.main_establishment_flag = 'Y'
and ptpp.party_type_code = 'LEGAL_ESTABLISHMENT')
and regt.registration_number = c_tax_reg_num;
SELECT meaning
INTO x_party_type_token
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = p_party_type_code;
SELECT distinct party_name
INTO l_party_name
FROM hz_parties
WHERE party_id = l_party_id;
SELECT distinct p.party_name, nvl(ps.party_site_name, p.address1||' '||p.address2||' '||p.address3||' '||p.address4||' '||p.city||' '||p.state) party_site_name
INTO l_party_name, l_party_site_name
FROM hz_parties p,
hz_party_sites ps
WHERE p.party_id = ps.party_id
AND ps.party_site_id = l_party_id;
SELECT distinct establishment_name
INTO l_party_name
FROM xle_fp_establishment_v
WHERE party_id = l_party_id;
SELECT meaning
INTO x_party_type_token
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = 'THIRD_PARTY_SITE';
SELECT HZP.PARTY_NAME,
NVL(HZS.PARTY_SITE_NAME, HZP.ADDRESS1||' '||
HZP.ADDRESS2||' '||
HZP.ADDRESS3||' '||
HZP.ADDRESS4||' '||
HZP.CITY||' '||
HZP.STATE) PARTY_SITE_NAME
INTO l_party_name, l_party_site_name
FROM ZX_REGISTRATIONS REG,
HZ_PARTY_SITES HZS,
HZ_PARTIES HZP,
ZX_PARTY_TAX_PROFILE ZXP
WHERE HZP.PARTY_ID = HZS.PARTY_ID
AND HZS.PARTY_SITE_ID = ZXP.PARTY_ID
AND ZXP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
AND ZXP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND REG.PARTY_TAX_PROFILE_ID NOT IN (SELECT S.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE S,
ZX_PARTY_TAX_PROFILE PTP,
HZ_PARTY_SITES HZPS
WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
AND PTP.PARTY_ID = HZPS.PARTY_ID
AND HZPS.PARTY_SITE_ID = S.PARTY_ID
AND S.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
AND REG.REGISTRATION_NUMBER = p_tax_reg_num
AND SYSDATE >= REG.EFFECTIVE_FROM
AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
AND ((p_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
OR (p_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = p_tax_regime_code))
AND ((p_tax IS NULL) OR (REG.TAX IS NULL)
OR (p_tax IS NOT NULL AND REG.TAX = p_tax))
AND ((p_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
OR (p_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = p_tax_jurisdiction_code))
AND ROWNUM = 1;
SELECT meaning
INTO x_party_type_token
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = 'THIRD_PARTY';
SELECT HZP.PARTY_NAME
INTO l_party_name
FROM ZX_REGISTRATIONS REG,
HZ_PARTIES HZP,
ZX_PARTY_TAX_PROFILE ZXP
WHERE HZP.PARTY_ID = ZXP.PARTY_ID
AND ZXP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
AND ZXP.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND REG.PARTY_TAX_PROFILE_ID NOT IN (SELECT S.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE S,
ZX_PARTY_TAX_PROFILE PTP,
HZ_PARTY_SITES HZPS
WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
AND PTP.PARTY_ID = HZPS.PARTY_SITE_ID
AND HZPS.PARTY_ID = S.PARTY_ID
AND S.PARTY_TYPE_CODE = 'THIRD_PARTY')
AND REG.REGISTRATION_NUMBER = p_tax_reg_num
AND SYSDATE >= REG.EFFECTIVE_FROM
AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
AND ((p_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
OR (p_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = p_tax_regime_code))
AND ((p_tax IS NULL) OR (REG.TAX IS NULL)
OR (p_tax IS NOT NULL AND REG.TAX = p_tax))
AND ((p_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
OR (p_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = p_tax_jurisdiction_code))
AND ROWNUM = 1;
SELECT COUNT(REG.REGISTRATION_NUMBER)
INTO l_total_count
FROM ZX_REGISTRATIONS REG,
ZX_PARTY_TAX_PROFILE PTP
WHERE PTP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
AND REG.REGISTRATION_NUMBER = l_trn
AND SYSDATE >= REG.EFFECTIVE_FROM
AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
AND ((l_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
OR (l_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = l_tax_regime_code))
AND ((l_tax IS NULL) OR (REG.TAX IS NULL)
OR (l_tax IS NOT NULL AND REG.TAX = l_tax))
AND ((l_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
OR (l_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = l_tax_jurisdiction_code));
SELECT COUNT(REGISTRATION_NUMBER)
INTO l_specific_count
FROM ZX_REGISTRATIONS REG
WHERE REG.PARTY_TAX_PROFILE_ID IN (SELECT S.PARTY_TAX_PROFILE_ID
FROM ZX_PARTY_TAX_PROFILE S,
ZX_PARTY_TAX_PROFILE PTP,
HZ_PARTY_SITES HZPS
WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
AND PTP.PARTY_ID = HZPS.PARTY_ID
AND HZPS.PARTY_SITE_ID = S.PARTY_ID
AND S.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
AND REG.REGISTRATION_NUMBER = l_trn
AND SYSDATE >= REG.EFFECTIVE_FROM
AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
AND ((l_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
OR (l_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = l_tax_regime_code))
AND ((l_tax IS NULL) OR (REG.TAX IS NULL)
OR (l_tax IS NOT NULL AND REG.TAX = l_tax))
AND ((l_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
OR (l_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = l_tax_jurisdiction_code));
select instr(N_Check_Digit,check_digit) into
N_Check_Flag from dual;
SELECT Decode(SubStr(trn_value,1,1),'Y',1,2)
||SubStr(trn_value,2,length(trn_value)-2)
INTO work_trn
FROM DUAL;