View: TAX_EXEMPTIONS_QP_V

Product:	AR - Receivables
Description:	Lists Candidate Exemption Certificates for the Bill To Customer and the Ship To Site
Implementation/DBA Data:	APPS.TAX_EXEMPTIONS_QP_V

View Text

SELECT S.SITE_USE_ID SHIP_TO_SITE_USE_ID
, ACCT_SITE.CUST_ACCOUNT_ID SHIP_TO_CUSTOMER_ID
, X.CUSTOMER_ID BILL_TO_CUSTOMER_ID
, X.SITE_USE_ID EXEMPTION_SHIP_TO_SITE_USE_ID
, V1.LOCATION_SEGMENT_USER_VALUE || DECODE ( V2.LOCATION_SEGMENT_USER_VALUE
, NULL
, NULL
, '.' ) || V2.LOCATION_SEGMENT_USER_VALUE || DECODE ( V3.LOCATION_SEGMENT_USER_VALUE
, NULL
, NULL
, '.' ) || V3.LOCATION_SEGMENT_USER_VALUE LOCATION
, X.CUSTOMER_EXEMPTION_NUMBER TAX_EXEMPT_NUMBER
, X.REASON_CODE TAX_EXEMPT_REASON_CODE
, ARL.MEANING TAX_EXEMPT_REASON_MEANING
, X.PERCENT_EXEMPT PERCENT_EXEMPT
, X.STATUS STATUS_CODE
, X.START_DATE START_DATE
, X.END_DATE END_DATE
, X.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
, DECODE( X.SITE_USE_ID
, NULL
, DECODE(X.LOCATION_ID_SEGMENT_3
, NULL
, DECODE(X.LOCATION_ID_SEGMENT_2
, NULL
, DECODE(X.LOCATION_ID_SEGMENT_1
, NULL
, 100
, 3)
, 2)
, 1)
, 0 ) +DECODE( X.STATUS
, 'PRIMARY'
, 0
, 'MANUAL'
, 0.1
, 'UNAPPROVED'
, 0.4
, 'EXPIRED'
, 0.5
, 0.8 ) DISPLAY_ORDER
, X.TAX_CODE TAX_CODE
FROM AR_LOCATION_COMBINATIONS C
, AR_LOCATION_VALUES V1
, AR_LOCATION_VALUES V2
, AR_LOCATION_VALUES V3
, AR_LOOKUPS ARL
, RA_TAX_EXEMPTIONS X
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_CUST_SITE_USES S
WHERE LOC_ASSIGN.LOC_ID = C.LOCATION_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99)
AND S.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND NVL(X.LOCATION_ID_SEGMENT_1
, C.LOCATION_ID_SEGMENT_1) = C.LOCATION_ID_SEGMENT_1
AND NVL(X.LOCATION_ID_SEGMENT_2
, C.LOCATION_ID_SEGMENT_2) = C.LOCATION_ID_SEGMENT_2
AND NVL(X.LOCATION_ID_SEGMENT_3
, C.LOCATION_ID_SEGMENT_3) = C.LOCATION_ID_SEGMENT_3
AND V1.LOCATION_SEGMENT_ID(+) = X.LOCATION_ID_SEGMENT_1
AND V2.LOCATION_SEGMENT_ID(+) = X.LOCATION_ID_SEGMENT_2
AND V3.LOCATION_SEGMENT_ID(+) = X.LOCATION_ID_SEGMENT_3
AND ARL.LOOKUP_TYPE = 'TAX_REASON'
AND ARL.LOOKUP_CODE = X.REASON_CODE
AND X.EXEMPTION_TYPE = 'CUSTOMER'
AND NVL( X.SITE_USE_ID
, S.SITE_USE_ID ) = S.SITE_USE_ID UNION ALL SELECT S.SITE_USE_ID SHIP_TO_SITE_USE_ID
, ACCT_SITE.CUST_ACCT_SITE_ID SHIP_TO_CUSTOMER_ID
, X.CUSTOMER_ID BILL_TO_CUSTOMER_ID
, X.SITE_USE_ID EXEMPTION_SHIP_TO_SITE_USE_ID
, NULL LOCATION
, X.CUSTOMER_EXEMPTION_NUMBER TAX_EXEMPT_NUMBER
, X.REASON_CODE TAX_EXEMPT_REASON_CODE
, ARL.MEANING TAX_EXEMPT_REASON_MEANING
, X.PERCENT_EXEMPT PERCENT_EXEMPT
, X.STATUS STATUS_CODE
, X.START_DATE START_DATE
, X.END_DATE END_DATE
, X.TAX_EXEMPTION_ID TAX_EXEMPTION_ID
, DECODE( X.SITE_USE_ID
, NULL
, 100
, 0) +DECODE( X.STATUS
, 'PRIMARY'
, 0.9
, 'MANUAL'
, 0.5
, 'UNAPPROVED'
, 0.4
, 'EXPIRED'
, 0.3
, 0.2 ) DISPLAY_ORDER
, X.TAX_CODE TAX_CODE
FROM AR_LOOKUPS ARL
, RA_TAX_EXEMPTIONS X
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN
, HZ_CUST_SITE_USES S
WHERE LOC_ASSIGN.LOC_ID IS NULL /* INTERNATIONAL ADDRESS */
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
AND NVL(ACCT_SITE.ORG_ID
, -99) = NVL(LOC_ASSIGN.ORG_ID
, -99)
AND S.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ARL.LOOKUP_TYPE = 'TAX_REASON'
AND ARL.LOOKUP_CODE = X.REASON_CODE
AND X.EXEMPTION_TYPE = 'CUSTOMER'
AND NVL( X.SITE_USE_ID
, S.SITE_USE_ID ) = S.SITE_USE_ID

Columns