View: OE_TAX_LINES_SUMMARY_VBR

View Text

SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID SHIP_TO_CUSTOMER_ID
, ACCT_SITE_BILL.CUST_ACCOUNT_ID BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL) TRX_CHARGE_LINE_ID
, L.LINE_ID TRX_LINE_ID
, L.HEADER_ID TRX_HEADER_ID
, TO_NUMBER(NULL) TRX_LINK_TO_CUST_TRX_LINE_ID
, L.TAX_DATE TRX_DATE
, NULL GL_DATE
, JL_ZZ_TAX.GET_CATEGORY_TAX_CODE ( TGR.TAX_CATEGORY_ID
, INV_TYPE.CUST_TRX_TYPE_ID
, S_SHIP.SITE_USE_ID
, S_BILL.SITE_USE_ID
, L.INVENTORY_ITEM_ID
, TGR.GROUP_TAX_ID
, NULL
, ACCT_SITE_SHIP.CUST_ACCOUNT_ID
, ACCT_SITE_BILL.CUST_ACCOUNT_ID
, L.TAX_DATE
, 'OE'
, NVL(L.SHIP_FROM_ORG_ID
, H.SHIP_FROM_ORG_ID)
, 'RATE'
, L.GLOBAL_ATTRIBUTE5
, ORG.ORGANIZATION_ID
, SYS.LOCATION_STRUCTURE_ID
, TO_NUMBER(SYS.GLOBAL_ATTRIBUTE10)
, SYS.SET_OF_BOOKS_ID
, TTA.TAX_ATTRIBUTE_VALUE
, (NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0)) * NVL(L.UNIT_SELLING_PRICE
, 0)
, OTA.TAX_ATTRIBUTE_VALUE
, CTA.TAX_ATTRIBUTE_VALUE
, HRL.LOCATION_ID
, L.GLOBAL_ATTRIBUTE6 ) TAX_CODE
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) TAX_AMOUNT
, S_SHIP.SITE_USE_ID SHIP_TO_SITE_USE_ID
, S_BILL.SITE_USE_ID BILL_TO_SITE_USE_ID
, LOC_SHIP.POSTAL_CODE SHIP_TO_POSTAL_CODE
, LOC_BILL.POSTAL_CODE BILL_TO_POSTAL_CODE
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, NULL MEMO_LINE_ID
, L.TAX_EXEMPT_FLAG TAX_EXEMPT_FLAG
, L.TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER
, L.TAX_EXEMPT_REASON_CODE TAX_EXEMPT_REASON_CODE
, LOC_ASSIGN_SHIP.LOC_ID SHIP_TO_LOCATION_CCID
, LOC_ASSIGN_BILL.LOC_ID BILL_TO_LOCATION_CCID
, L.INVOICING_RULE_ID INVOICING_RULE_ID
, (NVL( L.ORDERED_QUANTITY
, 0 ) - NVL( L.CANCELLED_QUANTITY
, 0 )) * NVL( L.UNIT_SELLING_PRICE
, 0 ) EXTENDED_AMOUNT
, H.CONVERSION_RATE EXCHANGE_RATE
, NULL SET_OF_BOOKS_ID
, C.CURRENCY_CODE TRX_CURRENCY_CODE
, C.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION PRECISION
, NULL USSGL_TRANSACTION_CODE
, NULL USSGL_TRX_CODE_CONTEXT
, NULL REQUEST_ID
, NULL POO_ADDRESS_CODE
, NULL POA_ADDRESS_CODE
, NULL SHIP_FROM_ADDRESS_CODE
, NULL SHIP_TO_ADDRESS_CODE
, L.FOB_POINT_CODE FOB_CODE
, NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) TAXED_QUANTITY
, NULL PART_NUMBER
, TO_NUMBER(NULL) TAX_LINE_NUMBER
, 'ALL' LOCATION_QUALIFIER
, 'Y' TAXABLE_FLAG
, NULL COMPOUNDING_PRECEDENCE
, NULL TAX_EXEMPTION_ID
, NULL TAX_EXCEPTION_ID
, NULL VENDOR_CONTROL_EXEMPTIONS
, NULL ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE5 ATTRIBUTE2
, TTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE3
, 'OE' ATTRIBUTE4
, OTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE5
, TGR.TAX_CATEGORY_ID NUMERIC_ATTRIBUTE1
, TGR.GROUP_TAX_ID NUMERIC_ATTRIBUTE2
, JL_ZZ_TAX.GET_TAX_BASE_RATE ( TGR.TAX_CATEGORY_ID
, INV_TYPE.CUST_TRX_TYPE_ID
, S_SHIP.SITE_USE_ID
, S_BILL.SITE_USE_ID
, L.INVENTORY_ITEM_ID
, TGR.GROUP_TAX_ID
, NULL
, ACCT_SITE_SHIP.CUST_ACCOUNT_ID
, ACCT_SITE_BILL.CUST_ACCOUNT_ID
, L.TAX_DATE
, 'OE'
, NVL(L.SHIP_FROM_ORG_ID
, H.SHIP_FROM_ORG_ID)
, 'BASE'
, L.GLOBAL_ATTRIBUTE5
, ORG.ORGANIZATION_ID
, SYS.LOCATION_STRUCTURE_ID
, TO_NUMBER(SYS.GLOBAL_ATTRIBUTE10)
, TTA.TAX_ATTRIBUTE_VALUE
, OTA.TAX_ATTRIBUTE_VALUE
, CTA.TAX_ATTRIBUTE_VALUE
, HRL.LOCATION_ID
, L.GLOBAL_ATTRIBUTE6 ) NUMERIC_ATTRIBUTE3
, HRL.LOCATION_ID NUMERIC_ATTRIBUTE4
, NULL NUMERIC_ATTRIBUTE5
, TO_NUMBER(NULL) TRX_NUMBER
, SHIP_CUST_ACCT.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER
, PARTY_SHIP.PARTY_NAME SHIP_TO_CUSTOMER_NAME
, BILL_CUST_ACCT.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, PARTY_BILL.PARTY_NAME BILL_TO_CUSTOMER_NAME
, TO_NUMBER(NULL) PREVIOUS_TRX_LINE_ID
, TO_NUMBER(NULL) PREVIOUS_TRX_HEADER_ID
, TO_NUMBER(NULL) PREVIOUS_TRX_NUMBER
, 'Y' AUDIT_FLAG
, NULL TRX_LINE_TYPE
, NULL DIVISION_CODE
, NULL COMPANY_CODE
, DECODE( NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE
, 'N')
, 'Y'
, NVL( S_BILL.TAX_HEADER_LEVEL_FLAG
, NVL( BILL_CUST_ACCT.TAX_HEADER_LEVEL_FLAG
, NVL( SYS.TAX_HEADER_LEVEL_FLAG
, 'N' )))
, NVL( SYS.TAX_HEADER_LEVEL_FLAG
, 'N' )) TAX_HEADER_LEVEL_FLAG
, DECODE( NVL( SYS.TAX_ROUNDING_ALLOW_OVERRIDE
, 'N')
, 'Y'
, NVL( S_BILL.TAX_ROUNDING_RULE
, NVL( BILL_CUST_ACCT.TAX_ROUNDING_RULE
, NVL( SYS.TAX_ROUNDING_RULE
, 'NEAREST')))
, NVL( SYS.TAX_ROUNDING_RULE
, 'NEAREST' )) TAX_ROUNDING_RULE
, TO_NUMBER(NULL) VAT_TAX_ID
, INV_TYPE.CUST_TRX_TYPE_ID TRX_TYPE_ID
, 'N' AMOUNT_INCLUDES_TAX_FLAG
, NVL(L.SHIP_FROM_ORG_ID
, H.SHIP_FROM_ORG_ID)
, TO_NUMBER(NULL) POO_ID
, TO_NUMBER(NULL) POA_ID
, L.PAYMENT_TERM_ID PAYMENT_TERM_ID
, NULL PAYMENT_TERMS_DISCOUNT_PERCENT
, VL.TAXABLE_BASIS TAXABLE_BASIS
, VL.TAX_CALCULATION_PLSQL_BLOCK TAX_CALCULATION_PLSQL_BLOCK
, DECODE(NVL(ACCT_SITE_SHIP.CUST_ACCT_SITE_ID
, -1)
, -1
, NVL(ACCT_SITE_BILL.GLOBAL_ATTRIBUTE8
, 'DEFAULT')
, NVL(ACCT_SITE_SHIP.GLOBAL_ATTRIBUTE8
, 'DEFAULT')) ATTRIBUTE6
, VL.GLOBAL_ATTRIBUTE3 ATTRIBUTE7
, CTA.TAX_ATTRIBUTE_VALUE ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE6 ATTRIBUTE9
, NULL ATTRIBUTE10
, TO_NUMBER(TO_CHAR(L.TAX_DATE
, 'YYYYMMDD')) NUMERIC_ATTRIBUTE6
, 7 NUMERIC_ATTRIBUTE7
, NULL NUMERIC_ATTRIBUTE8
, NULL NUMERIC_ATTRIBUTE9
, NULL NUMERIC_ATTRIBUTE10
FROM FND_CURRENCIES_VL C
, OE_TRANSACTION_TYPES_VL LINE_TYPE
, RA_CUST_TRX_TYPES INV_TYPE
, JL_ZZ_AR_TX_GROUPS TGR
, JL_ZZ_AR_TX_ATT_CLS TTA
, JL_ZZ_AR_TX_ATT_CLS OTA
, HR_LOCATIONS HRL
, HR_ORGANIZATION_UNITS ORG
, JL_ZZ_AR_TX_CUS_CLS CTA
, HZ_PARTY_SITES PARTY_SITE_SHIP
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN_SHIP
, HZ_LOCATIONS LOC_SHIP
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
, RA_SITE_USES S_SHIP
, HZ_PARTIES PARTY_SHIP
, HZ_CUST_ACCOUNTS SHIP_CUST_ACCT
, HZ_PARTY_SITES PARTY_SITE_BILL
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN_BILL
, HZ_LOCATIONS LOC_BILL
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE_BILL
, RA_SITE_USES S_BILL
, HZ_PARTIES PARTY_BILL
, HZ_CUST_ACCOUNTS BILL_CUST_ACCT
, JL_ZZ_AR_TX_CATEG TXC
, AR_VAT_TAX VL
, OE_ORDER_LINES L
, OE_ORDER_HEADERS H
, AR_SYSTEM_PARAMETERS SYS
WHERE L.HEADER_ID = H.HEADER_ID
AND C.CURRENCY_CODE = H.TRANSACTIONAL_CURR_CODE
AND L.SHIP_TO_ORG_ID = S_SHIP.SITE_USE_ID(+)
AND S_SHIP.SITE_USE_CODE = 'SHIP_TO'
AND ACCT_SITE_SHIP.PARTY_SITE_ID = PARTY_SITE_SHIP.PARTY_SITE_ID(+)
AND LOC_SHIP.LOCATION_ID(+) = PARTY_SITE_SHIP.LOCATION_ID
AND LOC_SHIP.LOCATION_ID = LOC_ASSIGN_SHIP.LOCATION_ID (+)
AND S_SHIP.ADDRESS_ID = ACCT_SITE_SHIP.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE_BILL.PARTY_SITE_ID = PARTY_SITE_BILL.PARTY_SITE_ID
AND LOC_BILL.LOCATION_ID = PARTY_SITE_BILL.LOCATION_ID
AND LOC_BILL.LOCATION_ID = LOC_ASSIGN_BILL.LOCATION_ID
AND S_BILL.ADDRESS_ID = ACCT_SITE_BILL.CUST_ACCT_SITE_ID
AND PARTY_SHIP.PARTY_ID(+) = SHIP_CUST_ACCT.PARTY_ID
AND PARTY_BILL.PARTY_ID(+) = BILL_CUST_ACCT.PARTY_ID
AND SHIP_CUST_ACCT.CUST_ACCOUNT_ID(+) = ACCT_SITE_SHIP.CUST_ACCOUNT_ID
AND BILL_CUST_ACCT.CUST_ACCOUNT_ID(+) = ACCT_SITE_BILL.CUST_ACCOUNT_ID
AND L.INVOICE_TO_ORG_ID = S_BILL.SITE_USE_ID(+)
AND S_BILL.SITE_USE_CODE = 'BILL_TO'
AND L.LINE_TYPE_ID = LINE_TYPE.TRANSACTION_TYPE_ID
AND LINE_TYPE.TRANSACTION_TYPE_CODE = 'LINE'
AND LINE_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID
AND ((INV_TYPE.TAX_CALCULATION_FLAG = 'Y' ) OR (NVL(L.TAX_EXEMPT_FLAG
, 'S') = 'R' ) )
AND SYS.SET_OF_BOOKS_ID = VL.SET_OF_BOOKS_ID
AND L.TAX_CODE = VL.TAX_CODE
AND L.TAX_DATE BETWEEN VL.START_DATE
AND NVL(VL.END_DATE
, TO_DATE( '31122199'
, 'DDMMYYYY'))
AND VL.TAX_TYPE = 'TAX_GROUP'
AND NVL(VL.ENABLED_FLAG
, 'Y') = 'Y'
AND NVL(VL.TAX_CLASS
, 'O') = 'O'
AND VL.VAT_TAX_ID = TGR.GROUP_TAX_ID
AND TGR.CALCULATE_IN_OE = 'Y'
AND TGR.ESTABLISHMENT_TYPE = OTA.TAX_ATTRIBUTE_VALUE
AND OTA.TAX_ATTR_CLASS_TYPE = 'ORGANIZATION_CLASS'
AND OTA.TAX_CATEGORY_ID = TGR.TAX_CATEGORY_ID
AND OTA.TAX_ATTRIBUTE_TYPE = 'ORGANIZATION_ATTRIBUTE'
AND OTA.TAX_ATTRIBUTE_NAME = TXC.ORG_TAX_ATTRIBUTE
AND OTA.ENABLED_FLAG = 'Y'
AND TGR.TAX_CATEGORY_ID = TXC.TAX_CATEGORY_ID
AND NVL(TXC.START_DATE_ACTIVE
, L.TAX_DATE) <= L.TAX_DATE
AND TXC.END_DATE_ACTIVE >= L.TAX_DATE
AND TXC.TAX_RULE_SET = SYS.GLOBAL_ATTRIBUTE13
AND NVL(HRL.GLOBAL_ATTRIBUTE1
, 'DEFAULT') = OTA.TAX_ATTR_CLASS_CODE
AND ORG.LOCATION_ID = HRL.LOCATION_ID
AND ORG.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID
, NVL(H.SHIP_FROM_ORG_ID
, OE_PROFILE.VALUE('SO_ORGANIZATION_ID')))
AND CTA.ADDRESS_ID = NVL(ACCT_SITE_SHIP.CUST_ACCT_SITE_ID
, ACCT_SITE_BILL.CUST_ACCT_SITE_ID)
AND CTA.TAX_ATTR_CLASS_CODE = DECODE(NVL(ACCT_SITE_SHIP.CUST_ACCT_SITE_ID
, -1)
, -1
, NVL(ACCT_SITE_BILL.GLOBAL_ATTRIBUTE8
, 'DEFAULT')
, NVL(ACCT_SITE_SHIP.GLOBAL_ATTRIBUTE8
, 'DEFAULT'))
AND CTA.TAX_CATEGORY_ID = TGR.TAX_CATEGORY_ID
AND CTA.TAX_ATTRIBUTE_NAME = TXC.CUS_TAX_ATTRIBUTE
AND CTA.ENABLED_FLAG = 'Y'
AND TGR.CONTRIBUTOR_TYPE = CTA.TAX_ATTRIBUTE_VALUE
AND TGR.TRANSACTION_NATURE = TTA.TAX_ATTRIBUTE_VALUE
AND TTA.TAX_ATTR_CLASS_TYPE = 'TRANSACTION_CLASS'
AND TTA.TAX_CATEGORY_ID = TGR.TAX_CATEGORY_ID
AND TTA.TAX_ATTRIBUTE_TYPE = 'TRANSACTION_ATTRIBUTE'
AND TTA.TAX_ATTRIBUTE_NAME = TXC.TXN_TAX_ATTRIBUTE
AND TTA.TAX_ATTR_CLASS_CODE = L.GLOBAL_ATTRIBUTE6
AND TTA.ENABLED_FLAG = 'Y'
AND L.TAX_DATE <= TGR.END_DATE_ACTIVE
AND L.TAX_DATE >= NVL(TGR.START_DATE_ACTIVE
, L.TAX_DATE)
AND NVL(LOC_ASSIGN_SHIP.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(LOC_ASSIGN_BILL.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99) UNION SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID SHIP_TO_CUSTOMER_ID
, ACCT_SITE_BILL.CUST_ACCOUNT_ID BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL) TRX_CHARGE_LINE_ID
, L.LINE_ID TRX_LINE_ID
, L.HEADER_ID TRX_HEADER_ID
, TO_NUMBER(NULL) TRX_LINK_TO_CUST_TRX_LINE_ID
, L.TAX_DATE TRX_DATE
, NULL GL_DATE
, VL.TAX_CODE TAX_CODE
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) TAX_AMOUNT
, S_SHIP.SITE_USE_ID SHIP_TO_SITE_USE_ID
, S_BILL.SITE_USE_ID BILL_TO_SITE_USE_ID
, LOC_SHIP.POSTAL_CODE SHIP_TO_POSTAL_CODE
, LOC_BILL.POSTAL_CODE BILL_TO_POSTAL_CODE
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, NULL MEMO_LINE_ID
, L.TAX_EXEMPT_FLAG TAX_EXEMPT_FLAG
, L.TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER
, L.TAX_EXEMPT_REASON_CODE TAX_EXEMPT_REASON_CODE
, LOC_ASSIGN_SHIP.LOC_ID SHIP_TO_LOCATION_CCID
, LOC_ASSIGN_BILL.LOC_ID BILL_TO_LOCATION_CCID
, L.INVOICING_RULE_ID INVOICING_RULE_ID
, (NVL( L.ORDERED_QUANTITY
, 0 ) - NVL( L.CANCELLED_QUANTITY
, 0 )) * NVL( L.UNIT_SELLING_PRICE
, 0 ) EXTENDED_AMOUNT
, H.CONVERSION_RATE EXCHANGE_RATE
, NULL SET_OF_BOOKS_ID
, C.CURRENCY_CODE TRX_CURRENCY_CODE
, C.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION PRECISION
, NULL USSGL_TRANSACTION_CODE
, NULL USSGL_TRX_CODE_CONTEXT
, NULL REQUEST_ID
, NULL POO_ADDRESS_CODE
, NULL POA_ADDRESS_CODE
, NULL SHIP_FROM_ADDRESS_CODE
, NULL SHIP_TO_ADDRESS_CODE
, L.FOB_POINT_CODE FOB_CODE
, NVL(L.ORDERED_QUANTITY
, 0) - NVL(L.CANCELLED_QUANTITY
, 0) TAXED_QUANTITY
, NULL PART_NUMBER
, TO_NUMBER(NULL) TAX_LINE_NUMBER
, 'ALL' LOCATION_QUALIFIER
, 'Y' TAXABLE_FLAG
, NULL COMPOUNDING_PRECEDENCE
, NULL TAX_EXEMPTION_ID
, NULL TAX_EXCEPTION_ID
, NULL VENDOR_CONTROL_EXEMPTIONS
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, TO_NUMBER(DECODE(LTRIM(VL.GLOBAL_ATTRIBUTE1
, '0123456789 ')
, NULL
, VL.GLOBAL_ATTRIBUTE1
, NULL)) NUMERIC_ATTRIBUTE1
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE2
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE3
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE4
, NULL NUMERIC_ATTRIBUTE5
, TO_NUMBER(NULL) TRX_NUMBER
, SHIP_CUST_ACCT.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER
, PARTY_SHIP.PARTY_NAME SHIP_TO_CUSTOMER_NAME
, BILL_CUST_ACCT.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER
, PARTY_BILL.PARTY_NAME BILL_TO_CUSTOMER_NAME
, TO_NUMBER(NULL) PREVIOUS_TRX_LINE_ID
, TO_NUMBER(NULL) PREVIOUS_TRX_HEADER_ID
, TO_NUMBER(NULL) PREVIOUS_TRX_NUMBER
, 'Y' AUDIT_FLAG
, NULL TRX_LINE_TYPE
, NULL DIVISION_CODE
, NULL COMPANY_CODE
, DECODE( NVL(SYS.TAX_ROUNDING_ALLOW_OVERRIDE
, 'N')
, 'Y'
, NVL( S_BILL.TAX_HEADER_LEVEL_FLAG
, NVL( BILL_CUST_ACCT.TAX_HEADER_LEVEL_FLAG
, NVL( SYS.TAX_HEADER_LEVEL_FLAG
, 'N' )))
, NVL( SYS.TAX_HEADER_LEVEL_FLAG
, 'N' )) TAX_HEADER_LEVEL_FLAG
, DECODE( NVL( SYS.TAX_ROUNDING_ALLOW_OVERRIDE
, 'N')
, 'Y'
, NVL( S_BILL.TAX_ROUNDING_RULE
, NVL( BILL_CUST_ACCT.TAX_ROUNDING_RULE
, NVL( SYS.TAX_ROUNDING_RULE
, 'NEAREST')))
, NVL( SYS.TAX_ROUNDING_RULE
, 'NEAREST' )) TAX_ROUNDING_RULE
, TO_NUMBER(NULL) VAT_TAX_ID
, INV_TYPE.CUST_TRX_TYPE_ID TRX_TYPE_ID
, VL.AMOUNT_INCLUDES_TAX_FLAG AMOUNT_INCLUDES_TAX_FLAG
, NVL(L.SHIP_FROM_ORG_ID
, H.SHIP_FROM_ORG_ID) SHIP_FROM_WAREHOUSE_ID
, TO_NUMBER(NULL) POO_ID
, TO_NUMBER(NULL) POA_ID
, TO_NUMBER(NULL) PAYMENT_TERM_ID
, NULL PAYMENT_TERMS_DISCOUNT_PERCENT
, VL.TAXABLE_BASIS TAXABLE_BASIS
, VL.TAX_CALCULATION_PLSQL_BLOCK TAX_CALCULATION_PLSQL_BLOCK
, NULL ATTRIBUTE6
, VL.GLOBAL_ATTRIBUTE3 ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, TO_NUMBER(TO_CHAR(L.TAX_DATE
, 'YYYYMMDD')) NUMERIC_ATTRIBUTE6
, 7 NUMERIC_ATTRIBUTE7
, NULL NUMERIC_ATTRIBUTE8
, NULL NUMERIC_ATTRIBUTE9
, NULL NUMERIC_ATTRIBUTE10
FROM OE_ORDER_LINES L
, OE_ORDER_HEADERS H
, FND_CURRENCIES_VL C
, HZ_PARTY_SITES PARTY_SITE_SHIP
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN_SHIP
, HZ_LOCATIONS LOC_SHIP
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
, HZ_PARTY_SITES PARTY_SITE_BILL
, HZ_LOC_ASSIGNMENTS LOC_ASSIGN_BILL
, HZ_LOCATIONS LOC_BILL
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE_BILL
, RA_SITE_USES S_SHIP
, RA_SITE_USES S_BILL
, HZ_PARTIES PARTY_SHIP
, HZ_CUST_ACCOUNTS SHIP_CUST_ACCT
, HZ_PARTIES PARTY_BILL
, HZ_CUST_ACCOUNTS BILL_CUST_ACCT
, OE_TRANSACTION_TYPES_VL LINE_TYPE
, RA_CUST_TRX_TYPES INV_TYPE
, AR_VAT_TAX_ALL_B VL
, AR_SYSTEM_PARAMETERS SYS
WHERE L.HEADER_ID = H.HEADER_ID AND C.CURRENCY_CODE = H.TRANSACTIONAL_CURR_CODE AND L.SHIP_TO_ORG_ID = S_SHIP.SITE_USE_ID(+) AND S_SHIP.SITE_USE_CODE = 'SHIP_TO'
AND ACCT_SITE_SHIP.PARTY_SITE_ID = PARTY_SITE_SHIP.PARTY_SITE_ID(+)
AND LOC_SHIP.LOCATION_ID(+) = PARTY_SITE_SHIP.LOCATION_ID
AND LOC_SHIP.LOCATION_ID = LOC_ASSIGN_SHIP.LOCATION_ID (+)
AND S_SHIP.ADDRESS_ID = ACCT_SITE_SHIP.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE_BILL.PARTY_SITE_ID = PARTY_SITE_BILL.PARTY_SITE_ID
AND LOC_BILL.LOCATION_ID = PARTY_SITE_BILL.LOCATION_ID
AND LOC_BILL.LOCATION_ID = LOC_ASSIGN_BILL.LOCATION_ID
AND S_BILL.ADDRESS_ID = ACCT_SITE_BILL.CUST_ACCT_SITE_ID AND PARTY_SHIP.PARTY_ID(+) = SHIP_CUST_ACCT.PARTY_ID AND PARTY_BILL.PARTY_ID(+) = BILL_CUST_ACCT.PARTY_ID AND SHIP_CUST_ACCT.CUST_ACCOUNT_ID(+) = ACCT_SITE_SHIP.CUST_ACCOUNT_ID AND BILL_CUST_ACCT.CUST_ACCOUNT_ID(+) = ACCT_SITE_BILL.CUST_ACCOUNT_ID AND L.INVOICE_TO_ORG_ID = S_BILL.SITE_USE_ID(+) AND S_BILL.SITE_USE_CODE = 'BILL_TO' AND L.LINE_TYPE_ID = LINE_TYPE.TRANSACTION_TYPE_ID AND LINE_TYPE.TRANSACTION_TYPE_CODE = 'LINE' AND LINE_TYPE.CUST_TRX_TYPE_ID = INV_TYPE.CUST_TRX_TYPE_ID AND ((INV_TYPE.TAX_CALCULATION_FLAG = 'Y' ) OR (NVL(L.TAX_EXEMPT_FLAG
, 'S') = 'R' ) ) AND L.TAX_DATE BETWEEN VL.START_DATE AND NVL(VL.END_DATE
, TO_DATE( '31122199'
, 'DDMMYYYY')) AND L.TAX_CODE = VL.TAX_CODE(+) AND NVL(VL.TAX_TYPE
, 'VAT') != 'TAX_GROUP' AND NVL(VL.SET_OF_BOOKS_ID
, SYS.SET_OF_BOOKS_ID) = SYS.SET_OF_BOOKS_ID AND NVL(VL.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99) AND NVL(VL.AMOUNT_INCLUDES_TAX_FLAG
, 'N') = 'N' AND NVL(VL.ENABLED_FLAG
, 'Y') = 'Y' AND NVL(VL.TAX_CLASS
, 'O') = 'O'
AND NVL(LOC_ASSIGN_SHIP.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND NVL(LOC_ASSIGN_BILL.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV( 'CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)

Columns