[Home] [Help]
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 ptp.party_tax_profile_id <> g_ptp_id
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 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 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 distinct ptp.party_id,ptp.party_tax_profile_id
FROM zx_party_tax_profile ptp
WHERE ptp.rep_registration_number = c_registration_number
AND ptp.party_type_code = c_party_type_code
AND ptp.party_tax_profile_id <> c_ptp_id;
SELECT party_tax_profile_id
FROM zx_registrations
WHERE party_tax_profile_id = c_ptp_id
AND registration_number = c_registration_number
AND TRUNC(sysdate) between effective_from and NVL(effective_to,sysdate+1);
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 party_name
INTO l_party_name
FROM hz_parties
WHERE party_id = l_party_id;
SELECT distinct ptp.party_id, ptp.party_tax_profile_id
into l_party_id,l_ptp_id
FROM zx_party_tax_profile ptp
where ptp.rep_registration_number = p_tax_reg_num
and ptp.party_type_code = p_party_type_code
and ptp.party_tax_profile_id <> p_ptp_id
and not exists (SELECT 1
FROM zx_party_tax_profile reg,
hz_party_sites hzs,
hz_party_sites hzr
where reg.party_tax_profile_id = p_ptp_id
and ptp.party_id = hzs.party_site_id
and reg.party_id = hzr.party_site_id
and hzs.party_id = hzr.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 ptp.party_id,ptp.party_tax_profile_id
into l_party_id,l_ptp_id
FROM zx_party_tax_profile ptp
where ptp.rep_registration_number = p_tax_reg_num
and ptp.party_type_code = p_party_type_code
and ptp.party_tax_profile_id <> p_ptp_id
and not exists (SELECT 1
FROM xle_fp_establishment_v est,
xle_fp_establishment_v estp,
zx_party_tax_profile ptpp
where ptpp.party_tax_profile_id = p_ptp_id
and estp.party_id = ptpp.party_id
and estp.legal_entity_id = est.legal_entity_id
and est.party_id = ptp.party_id);
SELECT distinct establishment_name
INTO l_party_name
FROM xle_fp_establishment_v
WHERE party_id = l_party_id;
SELECT COUNT(PTP.rep_registration_number)
INTO l_total_count
FROM ZX_PARTY_TAX_PROFILE PTP
WHERE PTP.rep_registration_number = p_tax_reg_num
AND ptp.party_tax_profile_id <> p_ptp_id;
SELECT count(S.PARTY_TAX_PROFILE_ID)
INTO l_specific_count
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.REP_REGISTRATION_NUMBER = P_TAX_REG_NUM
AND S.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE';
SELECT meaning
INTO x_party_type_token
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = 'THIRD_PARTY_SITE';
SELECT ZXP.PARTY_TAX_PROFILE_ID,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_ptp_id,l_party_name, l_party_site_name
FROM 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_TYPE_CODE = 'THIRD_PARTY_SITE'
AND NOT EXISTS (SELECT PTP.PARTY_TAX_PROFILE_ID
FROM 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 IS NULL
OR HZPS.PARTY_SITE_ID = ZXP.PARTY_ID))
AND ZXP.REP_REGISTRATION_NUMBER = p_tax_reg_num
AND ROWNUM = 1;
SELECT count(s.party_tax_profile_id)
into l_specific_count
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 ptp.rep_registration_number = p_tax_reg_num
and s.party_type_code = 'THIRD_PARTY';
SELECT meaning
INTO x_party_type_token
FROM fnd_lookups
WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
AND lookup_code = 'THIRD_PARTY';
SELECT ZXP.PARTY_TAX_PROFILE_ID,HZP.PARTY_NAME
INTO l_ptp_id,l_party_name
FROM HZ_PARTIES HZP,
ZX_PARTY_TAX_PROFILE ZXP
WHERE HZP.PARTY_ID = ZXP.PARTY_ID
AND ZXP.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND NOT EXISTS (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 S.PARTY_TAX_PROFILE_ID = ZXP.PARTY_TAX_PROFILE_ID)
AND ZXP.REP_REGISTRATION_NUMBER = p_tax_reg_num
AND ROWNUM = 1;
SELECT rep_registration_number into l_trn
FROM zx_party_tax_profile
where party_tax_profile_id = p_ptp_id
and rep_registration_number = p_tax_reg_num
and record_type_code = 'MIGRATED'
and not exists(SELECT 1 FROM zx_registrations reg
where reg.party_tax_profile_id = p_ptp_id
and reg.registration_number = p_tax_reg_num);
SELECT allow_dup_regn_num_flag
INTO l_allow_regn_num_flag
FROM zx_registrations reg, zx_taxes_b t
WHERE reg.party_tax_profile_id = NVL(p_ptp_id,G_INVALID_PTP_ID)
AND reg.registration_number = p_tax_reg_num
AND TRUNC(sysdate) between reg.effective_from and NVL(reg.effective_to,sysdate+1)
AND t.tax_regime_code = reg.tax_regime_code
AND t.tax = reg.tax;
SELECT t.allow_dup_regn_num_flag
INTO l_allow_regn_num_flag
FROM zx_registrations reg,zx_taxes_b t,zx_party_tax_profile ptp
WHERE ptp.party_tax_profile_id = NVL(p_ptp_id,G_INVALID_PTP_ID)
AND ptp.rep_registration_number = p_tax_reg_num
AND reg.registration_number = p_tax_reg_num
AND reg.party_tax_profile_id <> p_ptp_id
AND TRUNC(sysdate) between reg.effective_from and NVL(reg.effective_to,sysdate+1)
AND t.tax_regime_code = reg.tax_regime_code
AND t.tax = reg.tax;
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 PTP.PARTY_TAX_PROFILE_ID <> G_PTP_ID
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 = G_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;
SELECT DECODE(ceil(((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4
)/11) * 11 -
((REGEXP_SUBSTR(num,'[0-9]',1,1))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,2))*4+
(REGEXP_SUBSTR(num,'[0-9]',1,3))*3+
(REGEXP_SUBSTR(num,'[0-9]',1,4))*2+
(REGEXP_SUBSTR(num,'[0-9]',1,5))*7+
(REGEXP_SUBSTR(num,'[0-9]',1,6))*6+
(REGEXP_SUBSTR(num,'[0-9]',1,7))*5+
(REGEXP_SUBSTR(num,'[0-9]',1,8))*4),
10,'INVALID',
(REGEXP_SUBSTR(num,'[0-9]',1,9)),'VALID',
'INVALID') FINAL_RESULT
INTO l_result
FROM (SELECT
REGEXP_SUBSTR(REPLACE(REGEXP_SUBSTR(p_trn_value,'^(CHE-[0-9]{3}.[0-9]{3}.[0-9]{3}) (MWST|TVA|IVA)$'),'.',''),'[0-9]+') NUM
FROM DUAL
);