View: ICX_RA_CUSTOMER_TRX_V

View Text

SELECT CT.ROWID ROW_ID
, CT.TRX_NUMBER TRX_NUMBER
, CT.TRX_DATE TRX_DATE
, CT.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AL_CLASS.MEANING AL_CLASS_MEANING
, CT.PURCHASE_ORDER CT_PURCHASE_ORDER
, CT.WAYBILL_NUMBER WAYBILL_NUMBER
, SUM(DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, NULL
, TRUNC(SYSDATE) - PS.DUE_DATE)) DAYS_PAST_DUE
, MIN(PS.DUE_DATE) DUE_DATE
, TO_CHAR(SUM(PS.AMOUNT_DUE_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_ORIGINAL
, TO_CHAR(SUM(PS.AMOUNT_DUE_REMAINING)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_REMAINING
, TO_CHAR(SUM(PS.AMOUNT_LINE_ITEMS_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_LINE_ITEMS_ORIGINAL
, TO_CHAR(SUM(PS.TAX_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) TAX_ORIGINAL
, TO_CHAR(SUM(PS.FREIGHT_ORIGINAL)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) FREIGHT_ORIGINAL
, TO_CHAR(SUM(PS.AMOUNT_ADJUSTED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_ADJUSTED
, TO_CHAR(SUM(PS.AMOUNT_APPLIED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_APPLIED
, TO_CHAR(SUM(PS.AMOUNT_CREDITED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_CREDITED
, TO_CHAR(SUM(PS.DISCOUNT_TAKEN_EARNED)
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT_TAKEN_EARNED
, CT.SHIP_VIA SHIP_VIA
, FRT.ATTRIBUTE14 SHIP_VIA_URL
, FRT.DESCRIPTION SHIP_VIA_MEANING
, CT.EXCHANGE_RATE EXCHANGE_RATE
, MIN(PS.GL_DATE) GL_DATE
, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1 ATTRIBUTE1
, CT.ATTRIBUTE2 ATTRIBUTE2
, CT.ATTRIBUTE3 ATTRIBUTE3
, CT.ATTRIBUTE4 ATTRIBUTE4
, CT.ATTRIBUTE5 ATTRIBUTE5
, CT.ATTRIBUTE6 ATTRIBUTE6
, CT.ATTRIBUTE7 ATTRIBUTE7
, CT.ATTRIBUTE8 ATTRIBUTE8
, CT.ATTRIBUTE9 ATTRIBUTE9
, CT.ATTRIBUTE10 ATTRIBUTE10
, CT.ATTRIBUTE11 ATTRIBUTE11
, CT.ATTRIBUTE12 ATTRIBUTE12
, CT.ATTRIBUTE13 ATTRIBUTE13
, CT.ATTRIBUTE14 ATTRIBUTE14
, CT.ATTRIBUTE15 ATTRIBUTE15
, CT.BILL_TO_CUSTOMER_ID CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID CUSTOMER_SITE_USE_ID
, CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, MIN(PS.CLASS) CLASS
, MIN(PS.STATUS) STATUS
, CT.TERM_ID TERM_ID
, SUM(PS.ACCTD_AMOUNT_DUE_REMAINING) ACCTD_AMOUNT_DUE_REMAINING
, MIN(PS.ACTUAL_DATE_CLOSED) ACTUAL_DATE_CLOSED
, MIN(PS.GL_DATE_CLOSED) GL_DATE_CLOSED
, SUM(PS.AMOUNT_LINE_ITEMS_REMAINING) AMOUNT_LINE_ITEMS_REMAINING
, SUM(PS.TAX_REMAINING) TAX_REMAINING
, SUM(PS.FREIGHT_REMAINING) FREIGHT_REMAINING
, SUM(PS.AMOUNT_ADJUSTED_PENDING) AMOUNT_ADJUSTED_PENDING
, SUM(PS.RECEIVABLES_CHARGES_CHARGED) RECEIVABLES_CHARGES_CHARGED
, SUM(PS.RECEIVABLES_CHARGES_REMAINING) RECEIVABLES_CHARGES_REMAINING
, SUM(PS.AMOUNT_IN_DISPUTE) AMOUNT_IN_DISPUTE
, MIN(PS.DISPUTE_DATE) DISPUTE_DATE
, SUM(PS.DISCOUNT_TAKEN_UNEARNED) DISCOUNT_TAKEN_UNEARNED
, MIN(PS.CALL_DATE_LAST) CALL_DATE_LAST
, SUM(PS.PROMISE_AMOUNT_LAST) PROMISE_AMOUNT_LAST
, MIN(PS.PROMISE_DATE_LAST) PROMISE_DATE_LAST
, MIN(PS.FOLLOW_UP_DATE_LAST) FOLLOW_UP_DATE_LAST
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, CT.INTERFACE_HEADER_CONTEXT INTERFACE_HEADER_CONTEXT
, CT.INTERFACE_HEADER_ATTRIBUTE1 INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2 INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3 INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4 INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5 INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6 INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7 INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8 INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9 INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10 INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11 INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12 INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13 INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14 INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15 INTERFACE_HEADER_ATTRIBUTE15
, RAC.CUSTOMER_NAME RAC_CUSTOMER_NAME
, RAC.CUSTOMER_NUMBER RAC_CUSTOMER_NUMBER
, SU.LOCATION LOCATION
, SU.ADDRESS_ID ADDRESS_ID
, ADDR.CITY || '
, ' || ADDR.STATE CITY_STATE
, ADDR.CITY CITY
, ADDR.STATE STATE
FROM AR_PAYMENT_SCHEDULES PS
, RA_CUSTOMERS RAC
, RA_SITE_USES SU
, RA_ADDRESSES ADDR
, RA_CUSTOMER_TRX CT
, AR_LOOKUPS AL_CLASS
, ORG_FREIGHT FRT
, FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE PS.CUSTOMER_ID = RAC.CUSTOMER_ID (+)
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID (+)
AND SU.ADDRESS_ID = ADDR.ADDRESS_ID (+)
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND PS.CLASS = AL_CLASS.LOOKUP_CODE
AND CT.SHIP_VIA = FRT.FREIGHT_CODE(+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(FRT.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND AL_CLASS.LOOKUP_TYPE = 'INV/CM' GROUP BY CT.CUSTOMER_TRX_ID
, CT.ROWID
, CT.TRX_NUMBER
, CT.TRX_DATE
, CT.INVOICE_CURRENCY_CODE
, PS.INVOICE_CURRENCY_CODE
, AL_CLASS.MEANING
, CT.PURCHASE_ORDER
, CT.WAYBILL_NUMBER
, CT.SHIP_VIA
, FRT.DESCRIPTION
, FRT.ATTRIBUTE14
, CT.EXCHANGE_RATE
, CT.ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1
, CT.ATTRIBUTE2
, CT.ATTRIBUTE3
, CT.ATTRIBUTE4
, CT.ATTRIBUTE5
, CT.ATTRIBUTE6
, CT.ATTRIBUTE7
, CT.ATTRIBUTE8
, CT.ATTRIBUTE9
, CT.ATTRIBUTE10
, CT.ATTRIBUTE11
, CT.ATTRIBUTE12
, CT.ATTRIBUTE13
, CT.ATTRIBUTE14
, CT.ATTRIBUTE15
, CT.BILL_TO_CUSTOMER_ID
, CT.BILL_TO_SITE_USE_ID
, CT.CUSTOMER_TRX_ID
, CT.TERM_ID
, CT.DOC_SEQUENCE_VALUE
, CT.INTERFACE_HEADER_CONTEXT
, CT.INTERFACE_HEADER_ATTRIBUTE1
, CT.INTERFACE_HEADER_ATTRIBUTE2
, CT.INTERFACE_HEADER_ATTRIBUTE3
, CT.INTERFACE_HEADER_ATTRIBUTE4
, CT.INTERFACE_HEADER_ATTRIBUTE5
, CT.INTERFACE_HEADER_ATTRIBUTE6
, CT.INTERFACE_HEADER_ATTRIBUTE7
, CT.INTERFACE_HEADER_ATTRIBUTE8
, CT.INTERFACE_HEADER_ATTRIBUTE9
, CT.INTERFACE_HEADER_ATTRIBUTE10
, CT.INTERFACE_HEADER_ATTRIBUTE11
, CT.INTERFACE_HEADER_ATTRIBUTE12
, CT.INTERFACE_HEADER_ATTRIBUTE13
, CT.INTERFACE_HEADER_ATTRIBUTE14
, CT.INTERFACE_HEADER_ATTRIBUTE15
, RAC.CUSTOMER_NAME
, RAC.CUSTOMER_NUMBER
, SU.LOCATION
, SU.ADDRESS_ID
, ADDR.CITY || '
, ' || ADDR.STATE
, ADDR.CITY
, ADDR.STATE

Columns