View: TAX_LINES_INVOICE_IMPORT_V_V

View Text

SELECT L.ORIG_SYSTEM_SHIP_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, L.ORIG_SYSTEM_BILL_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL) TRX_CHARGE_LINE_ID
, L.INTERFACE_LINE_ID TRX_LINE_ID
, L.CUSTOMER_TRX_ID TRX_HEADER_ID
, TO_NUMBER(NULL) TRX_LINK_TO_CUST_TRX_LINE_ID
, L.TRX_DATE TRX_DATE
, L.GL_DATE GL_DATE
, V.TAX_CODE TAX_CODE
, V.TAX_RATE TAX_RATE
, TO_NUMBER(NULL) TAX_AMOUNT
, H.SHIP_TO_SITE_USE_ID SHIP_TO_SITE_USE_ID
, H.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
, NULL SHIP_TO_POSTAL_CODE
, NULL BILL_TO_POSTAL_CODE
, DECODE( TYPE.TYPE
, 'CM'
, LI.INVENTORY_ITEM_ID
, L.INVENTORY_ITEM_ID ) INVENTORY_ITEM_ID
, DECODE( TYPE.TYPE
, 'CM'
, LI.MEMO_LINE_ID
, L.MEMO_LINE_ID ) 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
, TO_NUMBER(NULL) SHIP_TO_LOCATION_CCID
, TO_NUMBER(NULL) BILL_TO_LOCATION_CCID
, DECODE( TYPE.TYPE
, 'CM'
, H.INVOICING_RULE_ID
, L.INVOICING_RULE_ID) INVOICING_RULE_ID
, DECODE(L.QUANTITY * L.UNIT_SELLING_PRICE
, NULL
, L.AMOUNT
, DECODE(C.MINIMUM_ACCOUNTABLE_UNIT
, NULL
, ROUND(L.QUANTITY * L.UNIT_SELLING_PRICE
, C.PRECISION)
, ROUND(L.QUANTITY * L.UNIT_SELLING_PRICE / C.MINIMUM_ACCOUNTABLE_UNIT) * C.MINIMUM_ACCOUNTABLE_UNIT)) EXTENDED_AMOUNT
, H.EXCHANGE_RATE EXCHANGE_RATE
, L.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, C.CURRENCY_CODE CURRENCY_CODE
, C.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION PRECISION
, L.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, L.USSGL_TRANSACTION_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, L.REQUEST_ID REQUEST_ID
, ARP_TAX_VIEW_VERTEX.POO_ADDRESS_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, L.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) POO_ADDRESS_CODE
, ARP_TAX_VIEW_VERTEX.POA_ADDRESS_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, L.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID
, H.PRIMARY_SALESREP_ID) POA_ADDRESS_CODE
, ARP_TAX_VIEW_VERTEX.SHIP_FROM_ADDRESS_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, L.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID
, L.WAREHOUSE_ID) SHIP_FROM_ADDRESS_CODE
, NULL SHIP_TO_ADDRESS_CODE
, H.FOB_POINT FOB_CODE
, L.QUANTITY TAXED_QUANTITY
, ARP_TAX_VIEW_VERTEX.PRODUCT_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, L.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID
, L.INVENTORY_ITEM_ID
, L.MEMO_LINE_ID) PART_NUMBER
, TO_NUMBER(NULL) TAX_LINE_NUMBER
, AL.LOOKUP_CODE LOCATION_QUALIFIER
, 'Y' TAXABLE_FLAG
, TO_NUMBER(NULL) COMPOUNDING_PRECEDENCE
, TO_NUMBER(NULL) TAX_EXEMPTION_ID
, TO_NUMBER(NULL) TAX_EXCEPTION_ID
, ARP_TAX_VIEW_VERTEX.VENDOR_CONTROL_EXEMPTIONS( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID
, TYPE.CUST_TRX_TYPE_ID) VENDOR_CONTROL_EXEMPTIONS
, ARP_TAX_VIEW_VERTEX.TRX_LINE_TYPE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) ATTRIBUTE1
, 1 ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, ARP_TAX_VIEW_VERTEX.USE_SECONDARY( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) NUMERIC_ATTRIBUTE1
, ARP_TAX_VIEW_VERTEX.STATE_TYPE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) NUMERIC_ATTRIBUTE2
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE3
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE4
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE5
, DECODE(H.TRX_NUMBER
, '~$~$$$'
, TO_CHAR(H.CUSTOMER_TRX_ID)
, H.TRX_NUMBER) TRX_NUMBER
, NULL SHIP_TO_CUSTOMER_NUMBER
, NULL SHIP_TO_CUSTOMER_NAME
, NULL BILL_TO_CUSTOMER_NUMBER
, NULL BILL_TO_CUSTOMER_NAME
, LI.PREVIOUS_CUSTOMER_TRX_LINE_ID PREVIOUS_TRX_LINE_ID
, L.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_TRX_HEADER_ID
, NULL PREVIOUS_TRX_NUMBER
, 'Y' AUDIT_FLAG
, NULL TRX_LINE_TYPE
, ARP_TAX_VIEW_VERTEX.DIVISION_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) DIVISION_CODE
, ARP_TAX_VIEW_VERTEX.COMPANY_CODE( 'TAX_LINES_INVOICE_IMPORT_V_V INVOICE'
, H.CUSTOMER_TRX_ID
, L.INTERFACE_LINE_ID) COMPANY_CODE
, NULL TAX_HEADER_LEVEL_FLAG
, NULL TAX_ROUNDING_RULE
, TO_NUMBER(NULL) VAT_TAX_ID
, H.CUST_TRX_TYPE_ID TRX_TYPE_ID
, V.AMOUNT_INCLUDES_TAX_FLAG AMOUNT_INCLUDES_TAX_FLAG
, L.WAREHOUSE_ID SHIP_FROM_WAREHOUSE_ID
, H.ORG_ID POO_ID
, H.ORG_ID POA_ID
, H.TERM_ID PAYMENT_TERM_ID
, NULL PAYMENT_TERMS_DISCOUNT_PERCENT
, V.TAXABLE_BASIS TAXABLE_BASIS
, V.TAX_CALCULATION_PLSQL_BLOCK TAX_CALCULATION_PLSQL_BLOCK
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL NUMERIC_ATTRIBUTE6
, NULL NUMERIC_ATTRIBUTE7
, NULL NUMERIC_ATTRIBUTE8
, NULL NUMERIC_ATTRIBUTE9
, NULL NUMERIC_ATTRIBUTE10
FROM RA_INTERFACE_LINES_GT L
, RA_CUSTOMER_TRX H
, RA_CUSTOMER_TRX_LINES LI
, AR_VAT_TAX V
, FND_CURRENCIES C
, RA_CUST_TRX_TYPES TYPE
, AR_LOOKUPS AL
WHERE (DECODE(LI.VAT_TAX_ID
, NULL
, TYPE.TAX_CALCULATION_FLAG
, 'Y') = 'Y' OR NVL(L.TAX_EXEMPT_FLAG
, 'S') = 'R')
AND NOT EXISTS (SELECT 'X'
FROM RA_INTERFACE_LINES_GT L2
, AR_SYSTEM_PARAMETERS P
WHERE L.INTERFACE_LINE_ID = L2.LINK_TO_LINE_ID
AND NVL(L2.ORG_ID
, -99)=NVL(P.ORG_ID
, -99)
AND L2.LINE_TYPE = 'TAX'
AND P.TAX_METHOD = 'VAT' )
AND NOT EXISTS (SELECT 'X'
FROM AR_MEMO_LINES M
WHERE M.MEMO_LINE_ID = L.MEMO_LINE_ID
AND M.LINE_TYPE IN ('TAX'
, 'FREIGHT'))
AND L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID
AND H.INVOICE_CURRENCY_CODE = C.CURRENCY_CODE
AND L.LINE_TYPE = 'LINE'
AND L.INTERFACE_LINE_ID = LI.CUSTOMER_TRX_LINE_ID(+)
AND NVL( L.INTERFACE_STATUS
, '~') <> 'P'
AND H.CUST_TRX_TYPE_ID = TYPE.CUST_TRX_TYPE_ID
AND (TYPE.TYPE <> 'CM' OR H.PREVIOUS_CUSTOMER_TRX_ID IS NULL)
AND L.VAT_TAX_ID = V.VAT_TAX_ID(+)
AND L.TRX_DATE BETWEEN NVL(V.START_DATE
, TO_DATE( '01011900'
, 'DDMMYYYY'))
AND NVL(V.END_DATE
, L.TRX_DATE)
AND AL.LOOKUP_TYPE = 'ARTAXVDR_LOC_QUALIFIER'
AND ((AL.ENABLED_FLAG = 'Y'
AND V.TAX_TYPE = 'LOCATION'
AND ARP_PROCESS_TAX.VENDOR_INSTALLED_FLAG = 'Y') OR (AL.LOOKUP_CODE = 'ALL'
AND (NVL(V.TAX_TYPE
, 'VAT') <> 'LOCATION' OR ARP_PROCESS_TAX.VENDOR_INSTALLED_FLAG <> 'Y')))
AND SYSDATE BETWEEN NVL(AL.START_DATE_ACTIVE
, SYSDATE)
AND NVL(AL.END_DATE_ACTIVE
, SYSDATE)

Columns