[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
v.tax_exemption_id,
v.exemption_type_code,
v.exempt_certificate_number,
v.exempt_reason_code,
v.meaning,
v.Exemption_Status_code,
v.tax_regime_code,
v.Tax_status_code,
v.Tax,
v.Tax_Rate_Code,
v.cust_account_id,
v.site_use_id,
v.party_id,
v.party_site_id,
v.effective_from,
v.effective_to,
v.content_owner_id,
v.PRODUCT_ID,
v.INVENTORY_ORG_ID,
v.RATE_MODIFIER,
v.TAX_JURISDICTION_ID,
v.PARTY_TAX_PROFILE_ID,
decode(zxr.country_code, p_country_code, 1, 2) select_order
FROM
(SELECT
ex.tax_exemption_id,
ex.exemption_type_code,
ex.exempt_certificate_number,
ex.exempt_reason_code,
lkp.Meaning,
Ex.Exemption_Status_code,
Ex.tax_regime_code,
Ex.Tax_status_code,
Ex.Tax,
Ex.Tax_Rate_Code,
Ex.Cust_account_id,
Ex.Site_use_id,
Ptp_party.party_id party_id,
null party_site_id,
Ex.effective_from,
Ex.effective_to,
Ex.content_owner_id,
Ex.PRODUCT_ID,
Ex.INVENTORY_ORG_ID,
Ex.RATE_MODIFIER,
Ex.TAX_JURISDICTION_ID,
Ex.PARTY_TAX_PROFILE_ID
FROM
zx_exemptions ex, fnd_lookups lkp,
zx_party_tax_profile ptp_party
WHERE
Ex.exempt_reason_code = lkp.lookup_code and
Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE' and
ex.exemption_status_code in ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' ) and
ex.party_tax_profile_id = ptp_party.party_tax_profile_id and
ptp_party.party_type_code = 'THIRD_PARTY'
UNION
SELECT
ex.tax_exemption_id,
ex.exemption_type_code,
ex.exempt_certificate_number,
ex.exempt_reason_code,
lkp.Meaning,
Ex.Exemption_Status_code,
Ex.tax_regime_code,
Ex.Tax_status_code,
Ex.Tax,
Ex.Tax_Rate_Code,
Ex.Cust_account_id,
Ex.Site_use_id,
ps.party_id party_id,
Ptp_party_site.party_id party_site_id,
Ex.effective_from,
Ex.effective_to,
Ex.content_owner_id,
Ex.PRODUCT_ID,
Ex.INVENTORY_ORG_ID,
Ex.RATE_MODIFIER,
Ex.TAX_JURISDICTION_ID,
Ex.PARTY_TAX_PROFILE_ID
FROM
zx_exemptions ex, fnd_lookups lkp,
zx_party_tax_profile ptp_party_site, hz_party_sites ps
WHERE
Ex.exempt_reason_code = lkp.lookup_code and
Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE' and
ex.exemption_status_code in ( 'PRIMARY', 'MANUAL', 'UNAPPROVED' ) and
ex.party_tax_profile_id = ptp_party_site.party_tax_profile_id and
ptp_party_site.party_type_code = 'THIRD_PARTY_SITE' and
ptp_party_site.party_id = ps.party_site_id) v, zx_regimes_b zxr
WHERE
v.content_owner_id = p_ptp_id
AND v.tax_regime_code = zxr.tax_regime_code
AND nvl(v.EXEMPT_CERTIFICATE_NUMBER, -99) = nvl(p_exempt_certificate_number, -99)
AND nvl(v.EXEMPT_REASON_CODE, -99) = nvl(p_reason_code,-99)
AND nvl(v.site_use_id,nvl(p_ship_to_site_use_id,p_bill_to_site_use_id)) =
nvl(p_ship_to_site_use_id, p_bill_to_site_use_id)
AND nvl(v.cust_account_id, p_bill_to_cust_acct_id) = p_bill_to_cust_acct_id
AND nvl(v.PARTY_SITE_ID,nvl(p_ship_to_party_site_id, p_bill_to_party_site_id))=
nvl(p_ship_to_party_site_id, p_bill_to_party_site_id)
and v.party_id = p_bill_to_party_id
AND v.EXEMPTION_STATUS_CODE = 'PRIMARY'
AND TRUNC(NVL(p_trx_date,sysdate))
BETWEEN TRUNC(v.EFFECTIVE_FROM)
AND TRUNC(NVL(v.EFFECTIVE_TO,NVL(p_trx_date,sysdate)))
ORDER BY select_order;
SELECT_ORDER NUMBER);
SELECT hzl.COUNTRY
INTO l_country_code
FROM hz_locations hzl,
hz_party_sites hzp
WHERE hzl.location_id = hzp.location_id
AND hzp.PARTY_SITE_ID = p_ship_to_party_site_id;
l_exemption_rec_tbl.delete;