VIEW: APPS.ZX_EXEMPTIONS_V

Source

View Text - Preformatted

SELECT
   ex.exempt_certificate_number,
   ex.exempt_reason_code,
   lkp.Meaning,
   Ex.Exemption_Status_code,
   Ex.rate_modifier,
   Ex.tax_regime_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,
   Ou.party_id org_id
FROM
   zx_exemptions ex,
   fnd_lookup_values lkp,
   zx_party_tax_profile ptp_party ,
   zx_party_tax_profile ou
WHERE
   Ex.exempt_reason_code = lkp.lookup_code
   and Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
   and security_group_id = 0
   and lkp.VIEW_APPLICATION_ID=0
   AND lkp.LANGUAGE=USERENV('LANG')
   and exemption_status_code in ('PRIMARY',
   'MANUAL',
   'UNAPPROVED')
   and exempt_certificate_number is not null
   and ex.party_tax_profile_id = ptp_party.party_tax_profile_id
   and ptp_party.party_type_code = 'THIRD_PARTY'
   AND ex.content_owner_id = ou.party_tax_profile_id
   AND ou.party_type_code = 'OU'
   AND ou.use_le_as_subscriber_flag = 'N'
UNION
ALL
SELECT
   ex.exempt_certificate_number,
   ex.exempt_reason_code,
   lkp.Meaning,
   Ex.Exemption_Status_code,
   Ex.rate_modifier,
   Ex.tax_regime_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  ,
   Ou.party_id org_id
FROM
   zx_exemptions ex,
   fnd_lookup_values lkp,
   zx_party_tax_profile ptp_party_site,
   zx_party_tax_profile ou,
   hz_party_sites ps

WHERE
   Ex.exempt_reason_code = lkp.lookup_code
   and Lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
   and security_group_id = 0
   and lkp.VIEW_APPLICATION_ID=0
   AND lkp.LANGUAGE=USERENV('LANG')
   and exemption_status_code in ('PRIMARY',
   'MANUAL',
   'UNAPPROVED')
   and exempt_certificate_number is not null
   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
   AND ex.content_owner_id = ou.party_tax_profile_id
   AND ou.party_type_code = 'OU'
   AND ou.use_le_as_subscriber_flag = 'N'
   

View Text - HTML Formatted

SELECT EX.EXEMPT_CERTIFICATE_NUMBER
, EX.EXEMPT_REASON_CODE
, LKP.MEANING
, EX.EXEMPTION_STATUS_CODE
, EX.RATE_MODIFIER
, EX.TAX_REGIME_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
, OU.PARTY_ID ORG_ID FROM ZX_EXEMPTIONS EX
, FND_LOOKUP_VALUES LKP
, ZX_PARTY_TAX_PROFILE PTP_PARTY
, ZX_PARTY_TAX_PROFILE OU WHERE EX.EXEMPT_REASON_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
AND SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID=0
AND LKP.LANGUAGE=USERENV('LANG')
AND EXEMPTION_STATUS_CODE IN ('PRIMARY'
, 'MANUAL'
, 'UNAPPROVED')
AND EXEMPT_CERTIFICATE_NUMBER IS NOT NULL
AND EX.PARTY_TAX_PROFILE_ID = PTP_PARTY.PARTY_TAX_PROFILE_ID
AND PTP_PARTY.PARTY_TYPE_CODE = 'THIRD_PARTY'
AND EX.CONTENT_OWNER_ID = OU.PARTY_TAX_PROFILE_ID
AND OU.PARTY_TYPE_CODE = 'OU'
AND OU.USE_LE_AS_SUBSCRIBER_FLAG = 'N' UNION ALL SELECT EX.EXEMPT_CERTIFICATE_NUMBER
, EX.EXEMPT_REASON_CODE
, LKP.MEANING
, EX.EXEMPTION_STATUS_CODE
, EX.RATE_MODIFIER
, EX.TAX_REGIME_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
, OU.PARTY_ID ORG_ID FROM ZX_EXEMPTIONS EX
, FND_LOOKUP_VALUES LKP
, ZX_PARTY_TAX_PROFILE PTP_PARTY_SITE
, ZX_PARTY_TAX_PROFILE OU
, HZ_PARTY_SITES PS WHERE EX.EXEMPT_REASON_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
AND SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID=0
AND LKP.LANGUAGE=USERENV('LANG')
AND EXEMPTION_STATUS_CODE IN ('PRIMARY'
, 'MANUAL'
, 'UNAPPROVED')
AND EXEMPT_CERTIFICATE_NUMBER IS NOT NULL
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
AND EX.CONTENT_OWNER_ID = OU.PARTY_TAX_PROFILE_ID
AND OU.PARTY_TYPE_CODE = 'OU'
AND OU.USE_LE_AS_SUBSCRIBER_FLAG = 'N'