1. Tables and Views for Financials
  2. ZX_EXEMPTIONS_V

ZX_EXEMPTIONS_V

Details

  • Schema: FUSION

  • Object owner: FND

  • Object type: VIEW

Columns

Name

EXEMPT_CERTIFICATE_NUMBER

EXEMPT_REASON_CODE

MEANING

EXEMPTION_STATUS_CODE

RATE_MODIFIER

TAX_REGIME_CODE

CUST_ACCOUNT_ID

SITE_USE_ID

PARTY_ID

PARTY_SITE_ID

EFFECTIVE_FROM

EFFECTIVE_TO

CONTENT_OWNER_ID

ORG_ID

LE_ID

TAX_EXEMPTION_ID

Query

SQL_Statement

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,

CASE WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY'

THEN ptp_party_ptysite.party_id

WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY_SITE'

THEN (SELECT ps.party_id

FROM hz_party_sites ps

WHERE ps.party_site_id = ptp_party_ptysite.party_id)

ELSE NULL

END party_id,

CASE WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY'

THEN NULL

WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY_SITE'

THEN ptp_party_ptysite.party_id

ELSE NULL

END party_site_id,

ex.effective_from,

ex.effective_to,

ex.content_owner_id,

ptp_ou.party_id AS org_id,

NULL AS le_id,

ex.tax_exemption_id

FROM

fnd_lookup_values lkp,

zx_exemptions ex,

zx_party_tax_profile ptp_party_ptysite,

zx_party_tax_profile ptp_ou

WHERE lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'

AND lkp.view_application_id = 0

AND lkp.language = USERENV('LANG')

AND ex.exempt_reason_code = lkp.lookup_code

AND ex.exemption_status_code IN ('PRIMARY','MANUAL','UNAPPROVED')

AND ex.exempt_certificate_number IS NOT NULL

AND NVL(ex.duplicate_exemption,0) = 0

AND ex.party_tax_profile_id = ptp_party_ptysite.party_tax_profile_id

AND ptp_party_ptysite.party_type_code IN ('THIRD_PARTY', 'THIRD_PARTY_SITE')

AND ex.content_owner_id = ptp_ou.party_tax_profile_id

AND ptp_ou.party_type_code = 'OU'

AND ptp_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,

CASE WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY'

THEN ptp_party_ptysite.party_id

WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY_SITE'

THEN (SELECT ps.party_id

FROM hz_party_sites ps

WHERE ps.party_site_id = ptp_party_ptysite.party_id)

ELSE NULL

END party_id,

CASE WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY'

THEN NULL

WHEN ptp_party_ptysite.party_type_code = 'THIRD_PARTY_SITE'

THEN ptp_party_ptysite.party_id

ELSE NULL

END party_site_id,

ex.effective_from,

ex.effective_to,

ex.content_owner_id,

ptp_ou.party_id AS org_id,

ptp_le.party_id AS le_id,

ex.tax_exemption_id

FROM

fnd_lookup_values lkp,

zx_exemptions ex,

zx_party_tax_profile ptp_party_ptysite,

zx_party_tax_profile ptp_le,

xle_entity_profiles xle,

gl_ledger_config_details glcd,

gl_ledgers gl,

hr_operating_units hrou,

zx_party_tax_profile ptp_ou

WHERE lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'

AND lkp.view_application_id = 0

AND lkp.language = USERENV('LANG')

AND ex.exempt_reason_code = lkp.lookup_code

AND ex.exemption_status_code IN ('PRIMARY','MANUAL','UNAPPROVED')

AND ex.exempt_certificate_number IS NOT NULL

AND NVL(ex.duplicate_exemption,0) = 0

AND ex.party_tax_profile_id = ptp_party_ptysite.party_tax_profile_id

AND ptp_party_ptysite.party_type_code IN ('THIRD_PARTY', 'THIRD_PARTY_SITE')

AND ex.content_owner_id = ptp_le.party_tax_profile_id

AND ptp_le.party_type_code = 'FIRST_PARTY'

AND xle.party_id = ptp_le.party_id

AND glcd.object_id = xle.legal_entity_id

AND glcd.object_type_code = 'LEGAL_ENTITY'

AND glcd.setup_step_code = 'NONE'

AND gl.configuration_id = glcd.configuration_id

AND hrou.set_of_books_id = gl.ledger_id

AND ptp_ou.party_id = hrou.organization_id

AND ptp_ou.party_type_code = 'OU'

AND ptp_ou.use_le_as_subscriber_flag = 'Y'