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