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,
null le_id
-- products should join using
-- nvl(zx_exemptions_v.le_id, :le_id) = :le_id
-- :le_id should be the one passed by products
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 lkp.security_group_id = 0
and lkp.view_application_id = 0
and lkp.language = userenv('LANG')
and ex.exemption_status_code in ('PRIMARY',
'MANUAL',
'UNAPPROVED')
and ex.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'
-- added for future use in case exemptions defined before the flag
-- was checked also needs to be displayed.
-- or (ou.use_le_as_subscriber_flag = 'Y'
-- and ou.effective_from_use_le > ex.effective_from))
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,
null le_id
-- products should join using
-- nvl(zx_exemptions_v.le_id, :le_id) = :le_id
-- :le_id should be the one passed by products
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 lkp.security_group_id = 0
and lkp.view_application_id = 0
and lkp.language = userenv('LANG')
and ex.exemption_status_code in ('PRIMARY',
'MANUAL',
'UNAPPROVED')
and ex.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'
-- added for future use in case exemptions defined before the flag
-- was checked also needs to be displayed.
-- or (ou.use_le_as_subscriber_flag = 'Y'
-- and ou.effective_from_use_le > ex.effective_from))
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,
ptp_party.party_id party_id,
null party_site_id,
ex.effective_from,
ex.effective_to,
ex.content_owner_id,
ptp_ou.party_id org_id,
ptp_le.party_id le_id
-- products should join using
-- nvl(zx_exemptions_v.le_id, :le_id) = :le_id
-- :le_id should be the one passed by products
FROM
zx_exemptions ex,
fnd_lookup_values lkp,
zx_party_tax_profile ptp_party,
zx_party_tax_profile ptp_ou,
zx_party_tax_profile ptp_le,
hr_operating_units hrou,
gl_ledger_le_v gl_le,
xle_entity_profiles xle
WHERE
ex.exempt_reason_code = lkp.lookup_code
and lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
and lkp.security_group_id = 0
and lkp.view_application_id = 0
and lkp.language = userenv('LANG')
and ex.exemption_status_code in ('PRIMARY',
'MANUAL',
'UNAPPROVED' )
and ex.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 ptp_ou.party_type_code = 'OU'
and ptp_ou.use_le_as_subscriber_flag = 'Y'
and ptp_ou.party_id = hrou.organization_id
and hrou.set_of_books_id = gl_le.ledger_id
and gl_le.legal_entity_id = xle.legal_entity_id
and xle.party_id = ptp_le.party_id
and ptp_le.party_type_code = 'FIRST_PARTY'
and ex.content_owner_id = ptp_le.party_tax_profile_id
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,
ptp_ou.party_id org_id,
ptp_le.party_id le_id
-- products should join using
-- nvl(zx_exemptions_v.le_id, :le_id) = :le_id
-- :le_id should be the one passed by products
FROM
zx_exemptions ex,
fnd_lookup_values lkp,
zx_party_tax_profile ptp_party_site,
zx_party_tax_profile ptp_ou,
zx_party_tax_profile ptp_le,
hr_operating_units hrou,
gl_ledger_le_v gl_le,
xle_entity_profiles xle,
hz_party_sites ps
WHERE
ex.exempt_reason_code = lkp.lookup_code
and lkp.lookup_type = 'ZX_EXEMPTION_REASON_CODE'
and lkp.security_group_id = 0
and lkp.view_application_id = 0
and lkp.language = userenv('LANG')
and ex.exemption_status_code in ('PRIMARY',
'MANUAL',
'UNAPPROVED' )
and ex.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 ptp_ou.party_type_code = 'OU'
and ptp_ou.use_le_as_subscriber_flag = 'Y'
and ptp_ou.party_id = hrou.organization_id
and hrou.set_of_books_id = gl_le.ledger_id
and gl_le.legal_entity_id = xle.legal_entity_id
and xle.party_id = ptp_le.party_id
and ptp_le.party_type_code = 'FIRST_PARTY'
and ex.content_owner_id = ptp_le.party_tax_profile_id
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
,
NULL LE_ID
-- PRODUCTS SHOULD JOIN USING
-- NVL(ZX_EXEMPTIONS_V.LE_ID
, :LE_ID) = :LE_ID
-- :LE_ID SHOULD BE THE ONE PASSED BY PRODUCTS
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 LKP.SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.LANGUAGE = USERENV('LANG')
AND EX.EXEMPTION_STATUS_CODE IN ('PRIMARY'
,
'MANUAL'
,
'UNAPPROVED')
AND EX.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'
-- ADDED FOR FUTURE USE IN CASE EXEMPTIONS DEFINED BEFORE THE FLAG
-- WAS CHECKED ALSO NEEDS TO BE DISPLAYED.
-- OR (OU.USE_LE_AS_SUBSCRIBER_FLAG = 'Y'
--
AND OU.EFFECTIVE_FROM_USE_LE > EX.EFFECTIVE_FROM))
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
,
NULL LE_ID
-- PRODUCTS SHOULD JOIN USING
-- NVL(ZX_EXEMPTIONS_V.LE_ID
, :LE_ID) = :LE_ID
-- :LE_ID SHOULD BE THE ONE PASSED BY PRODUCTS
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 LKP.SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.LANGUAGE = USERENV('LANG')
AND EX.EXEMPTION_STATUS_CODE IN ('PRIMARY'
,
'MANUAL'
,
'UNAPPROVED')
AND EX.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'
-- ADDED FOR FUTURE USE IN CASE EXEMPTIONS DEFINED BEFORE THE FLAG
-- WAS CHECKED ALSO NEEDS TO BE DISPLAYED.
-- OR (OU.USE_LE_AS_SUBSCRIBER_FLAG = 'Y'
--
AND OU.EFFECTIVE_FROM_USE_LE > EX.EFFECTIVE_FROM))
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
,
PTP_PARTY.PARTY_ID PARTY_ID
,
NULL PARTY_SITE_ID
,
EX.EFFECTIVE_FROM
,
EX.EFFECTIVE_TO
,
EX.CONTENT_OWNER_ID
,
PTP_OU.PARTY_ID ORG_ID
,
PTP_LE.PARTY_ID LE_ID
-- PRODUCTS SHOULD JOIN USING
-- NVL(ZX_EXEMPTIONS_V.LE_ID
, :LE_ID) = :LE_ID
-- :LE_ID SHOULD BE THE ONE PASSED BY PRODUCTS
FROM
ZX_EXEMPTIONS EX
,
FND_LOOKUP_VALUES LKP
,
ZX_PARTY_TAX_PROFILE PTP_PARTY
,
ZX_PARTY_TAX_PROFILE PTP_OU
,
ZX_PARTY_TAX_PROFILE PTP_LE
,
HR_OPERATING_UNITS HROU
,
GL_LEDGER_LE_V GL_LE
,
XLE_ENTITY_PROFILES XLE
WHERE
EX.EXEMPT_REASON_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
AND LKP.SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.LANGUAGE = USERENV('LANG')
AND EX.EXEMPTION_STATUS_CODE IN ('PRIMARY'
,
'MANUAL'
,
'UNAPPROVED' )
AND EX.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 PTP_OU.PARTY_TYPE_CODE = 'OU'
AND PTP_OU.USE_LE_AS_SUBSCRIBER_FLAG = 'Y'
AND PTP_OU.PARTY_ID = HROU.ORGANIZATION_ID
AND HROU.SET_OF_BOOKS_ID = GL_LE.LEDGER_ID
AND GL_LE.LEGAL_ENTITY_ID = XLE.LEGAL_ENTITY_ID
AND XLE.PARTY_ID = PTP_LE.PARTY_ID
AND PTP_LE.PARTY_TYPE_CODE = 'FIRST_PARTY'
AND EX.CONTENT_OWNER_ID = PTP_LE.PARTY_TAX_PROFILE_ID
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
,
PTP_OU.PARTY_ID ORG_ID
,
PTP_LE.PARTY_ID LE_ID
-- PRODUCTS SHOULD JOIN USING
-- NVL(ZX_EXEMPTIONS_V.LE_ID
, :LE_ID) = :LE_ID
-- :LE_ID SHOULD BE THE ONE PASSED BY PRODUCTS
FROM
ZX_EXEMPTIONS EX
,
FND_LOOKUP_VALUES LKP
,
ZX_PARTY_TAX_PROFILE PTP_PARTY_SITE
,
ZX_PARTY_TAX_PROFILE PTP_OU
,
ZX_PARTY_TAX_PROFILE PTP_LE
,
HR_OPERATING_UNITS HROU
,
GL_LEDGER_LE_V GL_LE
,
XLE_ENTITY_PROFILES XLE
,
HZ_PARTY_SITES PS
WHERE
EX.EXEMPT_REASON_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
AND LKP.SECURITY_GROUP_ID = 0
AND LKP.VIEW_APPLICATION_ID = 0
AND LKP.LANGUAGE = USERENV('LANG')
AND EX.EXEMPTION_STATUS_CODE IN ('PRIMARY'
,
'MANUAL'
,
'UNAPPROVED' )
AND EX.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 PTP_OU.PARTY_TYPE_CODE = 'OU'
AND PTP_OU.USE_LE_AS_SUBSCRIBER_FLAG = 'Y'
AND PTP_OU.PARTY_ID = HROU.ORGANIZATION_ID
AND HROU.SET_OF_BOOKS_ID = GL_LE.LEDGER_ID
AND GL_LE.LEGAL_ENTITY_ID = XLE.LEGAL_ENTITY_ID
AND XLE.PARTY_ID = PTP_LE.PARTY_ID
AND PTP_LE.PARTY_TYPE_CODE = 'FIRST_PARTY'
AND EX.CONTENT_OWNER_ID = PTP_LE.PARTY_TAX_PROFILE_ID
|
|
|