VIEW: APPS.TAX_EXEMPTIONS_QP_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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