VIEW: APPS.AR_PAYMENT_SCHEDULES_TRX2_V

Source

View Text - Preformatted

SELECT 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.CUST_TRX_TYPE_ID , PS.CASH_RECEIPT_ID , PS.CLASS , PS.STATUS , NULL , NULL , PS.RECEIPT_CONFIRMED_FLAG , PS.SELECTED_FOR_RECEIPT_BATCH_ID , PS.COLLECTOR_LAST , PS.FOLLOW_UP_CODE_LAST , NULL , /* PS.IN_COLLECTION */ PS.TERM_ID , /* */ substrb(party.party_name,1,50) , CUST_ACCT.ACCOUNT_NUMBER , SU.LOCATION , BS.NAME , CTT.NAME ,ARPT_SQL_FUNC_UTIL.get_lookup_meaning('INV/CM',PS.CLASS) , CT.DOC_SEQUENCE_VALUE , CT.PURCHASE_ORDER , CT.PREVIOUS_CUSTOMER_TRX_ID , 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 , NULL , PS.AMOUNT_DUE_REMAINING , PS.ACCTD_AMOUNT_DUE_REMAINING , arpt_sql_func_util.get_lookup_meaning('INVOICE_TRX_STATUS',ps.status) , DECODE(PS.STATUS, 'OP', to_date(NULL), PS.ACTUAL_DATE_CLOSED) , DECODE(PS.STATUS, 'OP', to_date(NULL), 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 */ , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('YES/NO',DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID, NULL, 'N','Y')) , DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID, NULL, 'N','Y') /*SELECTED_FOR_RECEIPT_CODE*/ , NULL , /* AL_RECEIPT_CONFIRMED.MEANING */ NULL , /* CR_SELECTED_RECEIPT_NUMBER */ NULL , /* ARB_SELECTED_NAME */ NULL , /* ARB_SELECTED_DATE */ DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) , NULL , /* DUNNING_DATE_LAST MAX(ARC.CORRESPONDENCE_DATE)*/ PS.CALL_DATE_LAST , PS.PROMISE_AMOUNT_LAST , TO_NUMBER(NULL) , /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST , ARPT_SQL_FUNC_UTIL.get_lookup_meaning('FOLLOW_UP',PS.FOLLOW_UP_CODE_LAST) , PS.FOLLOW_UP_DATE_LAST , NULL , /* AL_IN_COLLECTION.MEANING */ ARPT_SQL_FUNC_UTIL.get_term_details(PS.TERM_ID,'NAME') , SUBSTR(ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE (CT.CUSTOMER_TRX_ID, CTT.TYPE),1,30) , /* CHARGEBACK_INVOICE_NUM */ SUBSTR(ARPT_SQL_FUNC_UTIL.GET_BS_NAME_FOR_CB_INVOICE(CTT.TYPE, CT.CUSTOMER_TRX_ID),1,50) , /* CHARGEBACK_BATCH_SOURCE */ CONS.CONS_BILLING_NUMBER , PS.STAGED_DUNNING_LEVEL , PS.DUNNING_LEVEL_OVERRIDE_DATE , NULL /* REMITTANCE_METHOD */ , PS.GLOBAL_ATTRIBUTE_CATEGORY , PS.GLOBAL_ATTRIBUTE1 , PS.GLOBAL_ATTRIBUTE2 , PS.GLOBAL_ATTRIBUTE3 , PS.GLOBAL_ATTRIBUTE4 , PS.GLOBAL_ATTRIBUTE5 , PS.GLOBAL_ATTRIBUTE6 , PS.GLOBAL_ATTRIBUTE7 , PS.GLOBAL_ATTRIBUTE8 , PS.GLOBAL_ATTRIBUTE9 , PS.GLOBAL_ATTRIBUTE10 , PS.GLOBAL_ATTRIBUTE11 , PS.GLOBAL_ATTRIBUTE12 , PS.GLOBAL_ATTRIBUTE13 , PS.GLOBAL_ATTRIBUTE14 , PS.GLOBAL_ATTRIBUTE15 , PS.GLOBAL_ATTRIBUTE16 , PS.GLOBAL_ATTRIBUTE17 , PS.GLOBAL_ATTRIBUTE18 , PS.GLOBAL_ATTRIBUTE19 , PS.GLOBAL_ATTRIBUTE20 , PS.CONS_INV_ID , PS.LAST_UPDATED_BY , PS.LAST_UPDATE_DATE , PS.LAST_UPDATE_LOGIN , PS.CREATED_BY , PS.CREATION_DATE , CT.COMMENTS , CT.LEGAL_ENTITY_ID , CT.BILLING_DATE , PS.ORG_ID FROM /* SHIV RAGUNAT 10/10/96 ADDED THE FOLLOWING 3 TABLES TO GET */ /* THE FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ ar_cons_inv_all cons, ra_cust_trx_types_all ctt, ra_batch_sources_all bs, ra_customer_trx_all ct, hz_cust_site_uses_all su, hz_cust_accounts cust_acct, hz_parties party, ar_payment_schedules ps WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID AND PS.ORG_ID = SU.ORG_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND PS.ORG_ID = CT.ORG_ID AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID AND CT.ORG_ID = BS.ORG_ID AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.ORG_ID = CTT.ORG_ID AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+) AND PS.ORG_ID = CONS.ORG_ID (+) AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS,PS.STATUS)

View Text - HTML Formatted

SELECT 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.CUST_TRX_TYPE_ID
, PS.CASH_RECEIPT_ID
, PS.CLASS
, PS.STATUS
, NULL
, NULL
, PS.RECEIPT_CONFIRMED_FLAG
, PS.SELECTED_FOR_RECEIPT_BATCH_ID
, PS.COLLECTOR_LAST
, PS.FOLLOW_UP_CODE_LAST
, NULL
, /* PS.IN_COLLECTION */ PS.TERM_ID
, /* */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, SU.LOCATION
, BS.NAME
, CTT.NAME
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM'
, PS.CLASS)
, CT.DOC_SEQUENCE_VALUE
, CT.PURCHASE_ORDER
, CT.PREVIOUS_CUSTOMER_TRX_ID
, 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
, NULL
, PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICE_TRX_STATUS'
, PS.STATUS)
, DECODE(PS.STATUS
, 'OP'
, TO_DATE(NULL)
, PS.ACTUAL_DATE_CLOSED)
, DECODE(PS.STATUS
, 'OP'
, TO_DATE(NULL)
, 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 */
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('YES/NO'
, DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID
, NULL
, 'N'
, 'Y'))
, DECODE(PS.SELECTED_FOR_RECEIPT_BATCH_ID
, NULL
, 'N'
, 'Y') /*SELECTED_FOR_RECEIPT_CODE*/
, NULL
, /* AL_RECEIPT_CONFIRMED.MEANING */ NULL
, /* CR_SELECTED_RECEIPT_NUMBER */ NULL
, /* ARB_SELECTED_NAME */ NULL
, /* ARB_SELECTED_DATE */ DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE)
, NULL
, /* DUNNING_DATE_LAST MAX(ARC.CORRESPONDENCE_DATE)*/ PS.CALL_DATE_LAST
, PS.PROMISE_AMOUNT_LAST
, TO_NUMBER(NULL)
, /* ACCTD_PROMISE_AMOUNT_LAST */ PS.PROMISE_DATE_LAST
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('FOLLOW_UP'
, PS.FOLLOW_UP_CODE_LAST)
, PS.FOLLOW_UP_DATE_LAST
, NULL
, /* AL_IN_COLLECTION.MEANING */ ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS.TERM_ID
, 'NAME')
, SUBSTR(ARPT_SQL_FUNC_UTIL.GET_CB_INVOICE (CT.CUSTOMER_TRX_ID
, CTT.TYPE)
, 1
, 30)
, /* CHARGEBACK_INVOICE_NUM */ SUBSTR(ARPT_SQL_FUNC_UTIL.GET_BS_NAME_FOR_CB_INVOICE(CTT.TYPE
, CT.CUSTOMER_TRX_ID)
, 1
, 50)
, /* CHARGEBACK_BATCH_SOURCE */ CONS.CONS_BILLING_NUMBER
, PS.STAGED_DUNNING_LEVEL
, PS.DUNNING_LEVEL_OVERRIDE_DATE
, NULL /* REMITTANCE_METHOD */
, PS.GLOBAL_ATTRIBUTE_CATEGORY
, PS.GLOBAL_ATTRIBUTE1
, PS.GLOBAL_ATTRIBUTE2
, PS.GLOBAL_ATTRIBUTE3
, PS.GLOBAL_ATTRIBUTE4
, PS.GLOBAL_ATTRIBUTE5
, PS.GLOBAL_ATTRIBUTE6
, PS.GLOBAL_ATTRIBUTE7
, PS.GLOBAL_ATTRIBUTE8
, PS.GLOBAL_ATTRIBUTE9
, PS.GLOBAL_ATTRIBUTE10
, PS.GLOBAL_ATTRIBUTE11
, PS.GLOBAL_ATTRIBUTE12
, PS.GLOBAL_ATTRIBUTE13
, PS.GLOBAL_ATTRIBUTE14
, PS.GLOBAL_ATTRIBUTE15
, PS.GLOBAL_ATTRIBUTE16
, PS.GLOBAL_ATTRIBUTE17
, PS.GLOBAL_ATTRIBUTE18
, PS.GLOBAL_ATTRIBUTE19
, PS.GLOBAL_ATTRIBUTE20
, PS.CONS_INV_ID
, PS.LAST_UPDATED_BY
, PS.LAST_UPDATE_DATE
, PS.LAST_UPDATE_LOGIN
, PS.CREATED_BY
, PS.CREATION_DATE
, CT.COMMENTS
, CT.LEGAL_ENTITY_ID
, CT.BILLING_DATE
, PS.ORG_ID
FROM /* SHIV RAGUNAT 10/10/96 ADDED THE FOLLOWING 3 TABLES TO GET */ /* THE FUNCTIONAL CURRENCY FOR FUNCTIONAL AMOUNT CALCULATION */ AR_CONS_INV_ALL CONS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_BATCH_SOURCES_ALL BS
, RA_CUSTOMER_TRX_ALL CT
, HZ_CUST_SITE_USES_ALL SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_PAYMENT_SCHEDULES PS
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PS.CUSTOMER_SITE_USE_ID = SU.SITE_USE_ID
AND PS.ORG_ID = SU.ORG_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND PS.ORG_ID = CT.ORG_ID
AND CT.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND CT.ORG_ID = BS.ORG_ID
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID = CTT.ORG_ID
AND PS.CONS_INV_ID = CONS.CONS_INV_ID (+)
AND PS.ORG_ID = CONS.ORG_ID (+)
AND PS.STATUS = NVL(ARP_VIEW_CONSTANTS.GET_STATUS
, PS.STATUS)