View: ICX_AR_PAYMENT_SCHED_CM_V

View Text

SELECT PS.ROWID ROW_ID
, PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER TRX_NUMBER
, PS.TRX_DATE TRX_DATE
, AL_CLASS.MEANING AL_CLASS_MEANING
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, TO_CHAR(PS.AMOUNT_DUE_ORIGINAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_ORIGINAL
, TO_CHAR(PS.TAX_ORIGINAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) TAX_ORIGINAL
, TO_CHAR(PS.FREIGHT_ORIGINAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) FREIGHT_ORIGINAL
, TO_CHAR(PS.AMOUNT_DUE_REMAINING
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_DUE_REMAINING
, PS.DUE_DATE DUE_DATE
, CT.PURCHASE_ORDER CT_PURCHASE_ORDER
, CT.WAYBILL_NUMBER WAYBILL_NUMBER
, CT.SHIP_VIA SHIP_VIA
, FRT.ATTRIBUTE14 SHIP_VIA_URL
, TO_CHAR(PS.AMOUNT_ADJUSTED
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_ADJUSTED
, TO_CHAR(PS.AMOUNT_CREDITED
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_CREDITED
, TO_CHAR(PS.AMOUNT_APPLIED
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( PS.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_APPLIED
, FRT.DESCRIPTION SHIP_VIA_MEANING
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.EXCHANGE_RATE EXCHANGE_RATE
, PS.GL_DATE GL_DATE
, PS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1 ATTRIBUTE1
, PS.ATTRIBUTE2 ATTRIBUTE2
, PS.ATTRIBUTE3 ATTRIBUTE3
, PS.ATTRIBUTE4 ATTRIBUTE4
, PS.ATTRIBUTE5 ATTRIBUTE5
, PS.ATTRIBUTE6 ATTRIBUTE6
, PS.ATTRIBUTE7 ATTRIBUTE7
, PS.ATTRIBUTE8 ATTRIBUTE8
, PS.ATTRIBUTE9 ATTRIBUTE9
, PS.ATTRIBUTE10 ATTRIBUTE10
, PS.ATTRIBUTE11 ATTRIBUTE11
, PS.ATTRIBUTE12 ATTRIBUTE12
, PS.ATTRIBUTE13 ATTRIBUTE13
, PS.ATTRIBUTE14 ATTRIBUTE14
, PS.ATTRIBUTE15 ATTRIBUTE15
, PS.CUSTOMER_ID CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PS .CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.CASH_RECEIPT_ID CASH_RECEIPT_ID
, PS.CLASS CLASS
, PS.STATUS STATUS
, PS.RECEIPT_CONFIRMED_FLAG RECEIPT_CONFIRMED_FLAG
, PS.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
, PS.COLLECTOR_LAST COLLECTOR_LAST
, PS.FOLLOW_UP_CODE_LAST FOLLOW_UP_CODE_LAST
, PS.IN_COLLECTION IN_COLLECTION
, PS.TERM_ID TERM_ID
, PS.DUE_DATE - PS.TRX_DATE DAYS_LATE
, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, PS.ACTUAL_DATE_CLOSED ACTUAL_DATE_CLOSED
, PS.GL_DATE_CLOSED GL_DATE_CLOSED
, PS.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
, PS.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
, PS.TAX_REMAINING TAX_REMAINING
, PS.FREIGHT_REMAINING FREIGHT_REMAINING
, PS.AMOUNT_ADJUSTED_PENDING AMOUNT_ADJUSTED_PENDING
, PS.RECEIVABLES_CHARGES_CHARGED RECEIVABLES_CHARGES_CHARGED
, PS.RECEIVABLES_CHARGES_REMAINING RECEIVABLES_CHARGES_REMAINING
, PS.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
, PS.DISPUTE_DATE DISPUTE_DATE
, PS.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
, PS.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
, PS.CALL_DATE_LAST CALL_DATE_LAST
, PS.PROMISE_AMOUNT_LAST PROMISE_AMOUNT_LAST
, PS.PROMISE_DATE_LAST PROMISE_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 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 AL_CLASS.LOOKUP_TYPE = 'INV/CM'
AND PS.CLASS = 'CM'
AND CT.SHIP_VIA = FRT.FREIGHT_CODE(+)
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(FRT.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) GROUP BY PS.ROWID
, PS.PAYMENT_SCHEDULE_ID
, PS.TRX_NUMBER
, PS.TRX_DATE
, PS.TERMS_SEQUENCE_NUMBER
, PS.INVOICE_CURRENCY_CODE
, PS.EXCHANGE_RATE
, PS.GL_DATE
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* */ PS.CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID
, PS.CASH_RECEIPT_ID
, PS.CLASS
, PS.STATUS
, PS.RECEIPT_CONFIRMED_FLAG
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, PS.COLLECTOR_LAST
, PS.FOLLOW_UP_CODE_LAST
, PS.IN_COLLECTION
, PS.TERM_ID
, /* */ RAC.CUSTOMER_NAME
, RAC.CUSTOMER_NUMBER
, SU.LOCATION
, SU.ADDRESS_ID
, ADDR.CITY
, ADDR.STATE
, AL_CLASS.MEANING
, CT.DOC_SEQUENCE_VALUE
, CT.PURCHASE_ORDER
, 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
, PS.DUE_DATE - PS.TRX_DATE
, PS.DUE_DATE
, PS.AMOUNT_DUE_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_DUE_ORIGINAL */ PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.ACTUAL_DATE_CLOSED
, PS.GL_DATE_CLOSED
, PS.AMOUNT_LINE_ITEMS_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_LINE_ITEMS_ORIG */ PS.AMOUNT_LINE_ITEMS_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_LINE_ITEMS_REMAIN */ PS.TAX_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_TAX_ORIGINAL */ PS.TAX_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_TAX_REMAINING */ PS.FREIGHT_ORIGINAL
, TO_NUMBER(NULL)
, /* ACCTD_FREIGHT_ORIGINAL */ PS.FREIGHT_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_FREIGHT_REMAINING */ PS.AMOUNT_ADJUSTED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_ADJUSTED */ PS.AMOUNT_ADJUSTED_PENDING
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_ADJUSTED_PENDING */ PS.RECEIVABLES_CHARGES_CHARGED
, TO_NUMBER(NULL)
, /* ACCTD_REC_CHARGES_CHARGED */ PS.RECEIVABLES_CHARGES_REMAINING
, TO_NUMBER(NULL)
, /* ACCTD_REC_CHARGES_REMAINING */ PS.AMOUNT_APPLIED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_APPLIED */ PS.AMOUNT_CREDITED
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_CREDITED */ PS.AMOUNT_IN_DISPUTE
, TO_NUMBER(NULL)
, /* ACCTD_AMOUNT_IN_DISPUTE */ PS.DISPUTE_DATE
, PS.DISCOUNT_TAKEN_EARNED
, TO_NUMBER(NULL)
, /* ACCTD_DISCOUNT_TAKEN_EARNED */ PS.DISCOUNT_TAKEN_UNEARNED
, TO_NUMBER(NULL)
, /* ACCTD_DISCOUNT_TAKEN_UNEARNED */ NULL
, /* CR_SELECTED_RECEIPT_NUMBER */ NULL
, /* ARB_SELECTED_NAME */ NULL
, /* ARB_SELECTED_DATE */ DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, NULL
, TRUNC(SYSDATE) - PS.DUE_DATE)
, PS.CALL_DATE_LAST
, PS.PROMISE_AMOUNT_LAST
, TO_NUMBER(NULL)
, /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST
, PS.FOLLOW_UP_DATE_LAST
, NULL
, NULL
, /* ADDED FOR IC TEAM */ CT.WAYBILL_NUMBER
, CT.SHIP_VIA
, FRT.DESCRIPTION
, FRT.ATTRIBUTE14

Columns