[Home] [Help]
| Product: | JL - Latin America Localizations |
|---|---|
| Description: | N/A |
| Implementation/DBA Data: | Not implemented in this database |
SELECT H.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID
, H.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
, TO_NUMBER(NULL) TRX_CHARGE_LINE_ID
, L.CUSTOMER_TRX_LINE_ID TRX_LINE_ID
, H.CUSTOMER_TRX_ID TRX_HEADER_ID
, TO_NUMBER(NULL) TRX_LINK_TO_CUST_TRX_LINE_ID
, NVL(R.TRX_DATE
, H.TRX_DATE) TRX_DATE
, D.GL_DATE GL_DATE
, V.TAX_CODE TAX_CODE
, TO_NUMBER(NULL) 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
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_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
, NULL SHIP_TO_LOCATION_CCID
, NULL BILL_TO_LOCATION_CCID
, H.INVOICING_RULE_ID INVOICING_RULE_ID
, NVL(L.GROSS_EXTENDED_AMOUNT
, L.EXTENDED_AMOUNT) EXTENDED_AMOUNT
, H.EXCHANGE_RATE EXCHANGE_RATE
, H.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, C.CURRENCY_CODE CURRENCY_CODE
, C.MINIMUM_ACCOUNTABLE_UNIT MINIMUM_ACCOUNTABLE_UNIT
, C.PRECISION PRECISION
, L.DEFAULT_USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
, L.DEFAULT_USSGL_TRX_CODE_CONTEXT USSGL_TRX_CODE_CONTEXT
, TO_NUMBER(NULL) REQUEST_ID
, TO_CHAR(NULL) POO_ADDRESS_CODE
, TO_CHAR(NULL) POA_ADDRESS_CODE
, TO_CHAR(NULL) SHIP_FROM_ADDRESS_CODE
, TO_CHAR(NULL) SHIP_TO_ADDRESS_CODE
, H.FOB_POINT FOB_CODE
, DECODE(T.TYPE
, 'CM'
, L.QUANTITY_CREDITED
, L.QUANTITY_INVOICED) TAXED_QUANTITY
, TO_NUMBER(NULL) PART_NUMBER
, TO_NUMBER(NULL) TAX_LINE_NUMBER
, 'ALL' LOCATION_QUALIFIER
, 'Y' TAXABLE_FLAG
, NULL COMPOUNDING_PRECEDENCE
, TO_NUMBER(NULL) TAX_EXEMPTION_ID
, TO_NUMBER(NULL) TAX_EXCEPTION_ID
, TO_CHAR(NULL) VENDOR_CONTROL_EXEMPTIONS
, SYS.GLOBAL_ATTRIBUTE13 ATTRIBUTE1
, L.GLOBAL_ATTRIBUTE2 ATTRIBUTE2
, NULL ATTRIBUTE3
, 'AR' ATTRIBUTE4
, NULL ATTRIBUTE5
, DECODE(V.TAX_TYPE
, 'TAX_GROUP'
, NULL
, DECODE( LENGTH(TRANSLATE(V.GLOBAL_ATTRIBUTE1
, '0123456789 '
, '0123456789'))
, LENGTH(TRANSLATE(V.GLOBAL_ATTRIBUTE1
, '0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ_-
, :.'
, '0123456789'))
, V.GLOBAL_ATTRIBUTE1
, -99)) NUMERIC_ATTRIBUTE1
, DECODE(V.TAX_TYPE
, 'TAX_GROUP'
, L.VAT_TAX_ID
, NULL) NUMERIC_ATTRIBUTE2
, TO_NUMBER(NULL) NUMERIC_ATTRIBUTE3
, HRL.LOCATION_ID NUMERIC_ATTRIBUTE4
, H.RELATED_CUSTOMER_TRX_ID NUMERIC_ATTRIBUTE5
, 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
, L.PREVIOUS_CUSTOMER_TRX_LINE_ID PREVIOUS_TRX_LINE_ID
, L.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_TRX_HEADER_ID
, P.TRX_NUMBER PREVIOUS_TRX_NUMBER
, 'Y' AUDIT_FLAG
, TO_CHAR(NULL) TRX_LINE_TYPE
, TO_CHAR(NULL) DIVISION_CODE
, TO_CHAR(NULL) 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
, NVL(L.AMOUNT_INCLUDES_TAX_FLAG
, V.AMOUNT_INCLUDES_TAX_FLAG) AMOUNT_INCLUDES_TAX_FLAG
, ORG.ORGANIZATION_ID SHIP_FROM_WAREHOUSE_ID
, TO_NUMBER(NULL) POO_ID
, TO_NUMBER(NULL) POA_ID
, H.TERM_ID PAYMENT_TERM_ID
, TO_NUMBER(NULL) PAYMENT_TERMS_DISCOUNT_PERCENT
, V.TAXABLE_BASIS TAXABLE_BASIS
, V.TAX_CALCULATION_PLSQL_BLOCK TAX_CALCULATION_PLSQL_BLOCK
, NULL ATTRIBUTE6
, V.TAX_TYPE ATTRIBUTE7
, NULL ATTRIBUTE8
, L.GLOBAL_ATTRIBUTE3 ATTRIBUTE9
, NVL(HRL.GLOBAL_ATTRIBUTE1
, 'DEFAULT') ATTRIBUTE10
, TO_NUMBER(TO_CHAR(H.TRX_DATE
, 'YYYYMMDD')) NUMERIC_ATTRIBUTE6
, 2 NUMERIC_ATTRIBUTE7
, NULL NUMERIC_ATTRIBUTE8
, SYS.LOCATION_STRUCTURE_ID NUMERIC_ATTRIBUTE9
, TO_NUMBER(SYS.GLOBAL_ATTRIBUTE10) NUMERIC_ATTRIBUTE10
FROM RA_CUST_TRX_LINE_GL_DIST D
, RA_CUST_TRX_TYPES PT
, RA_CUSTOMER_TRX P
, RA_CUSTOMER_TRX R
, RA_CUST_TRX_TYPES T
, FND_CURRENCIES_VL C
, HR_LOCATIONS_ALL HRL
, HR_ORGANIZATION_UNITS ORG
, AR_VAT_TAX V
, RA_CUSTOMER_TRX_LINES L
, RA_CUSTOMER_TRX H
, AR_SYSTEM_PARAMETERS SYS
WHERE H.CUST_TRX_TYPE_ID = T.CUST_TRX_TYPE_ID AND (DECODE(L.VAT_TAX_ID
, NULL
, T.TAX_CALCULATION_FLAG
, 'Y') = 'Y' OR NVL(L.TAX_EXEMPT_FLAG
, 'S') = 'R' OR ARP_PROCESS_TAX_2.MANUAL_TAX_FLAG = 'Y' ) AND L.CUSTOMER_TRX_ID = H.CUSTOMER_TRX_ID AND H.INVOICE_CURRENCY_CODE = C.CURRENCY_CODE AND H.CUSTOMER_TRX_ID = D.CUSTOMER_TRX_ID(+) AND D.ACCOUNT_CLASS(+) = 'REC' AND D.LATEST_REC_FLAG(+) = 'Y' AND L.LINE_TYPE(+) = 'LINE' AND NVL(L.AUTOTAX
, 'Y') <> 'I' AND L.VAT_TAX_ID = V.VAT_TAX_ID AND NVL(R.TRX_DATE
, H.TRX_DATE) >= NVL(V.START_DATE
, TO_DATE( '01011900'
, 'DDMMYYYY')) AND NVL(R.TRX_DATE
, H.TRX_DATE) <= NVL(V.END_DATE
, NVL(R.TRX_DATE
, H.TRX_DATE)) AND L.PREVIOUS_CUSTOMER_TRX_ID = P.CUSTOMER_TRX_ID(+) AND P.CUST_TRX_TYPE_ID = PT.CUST_TRX_TYPE_ID(+) AND (T.TYPE <> 'CM' OR H.PREVIOUS_CUSTOMER_TRX_ID IS NULL) AND NVL(PT.TYPE
, 'INV') IN ( 'INV'
, 'DM'
, 'CM') AND V.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND H.RELATED_CUSTOMER_TRX_ID = R.CUSTOMER_TRX_ID(+) AND HRL.LOCATION_ID = ORG.LOCATION_ID AND ORG.ORGANIZATION_ID = NVL(L.WAREHOUSE_ID
, OE_PROFILE.VALUE('SO_ORGANIZATION_ID'))