VIEW: APPS.OKL_BPD_TRX_SUM_RCPT_DTLS_UV

Source

View Text - Preformatted

SELECT HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER , PMTSCH.trx_number RECEIVABLE_INVOICE_NUMBER , HD.AMOUNT INVOICE_AMOUNT , PMTSCH.AMOUNT_DUE_REMAINING BALANCE , (PMTSCH.AMOUNT_DUE_ORIGINAL - PMTSCH.AMOUNT_DUE_REMAINING) AMOUNT_PAID , STY.name STREAM_NAME , HD.DATE_CONSOLIDATED INVOICE_DATE , ARCASH.RECEIPT_NUMBER CHECK_NUMBER , ARCASH.RECEIPT_DATE , OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE', STY.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING , ARAPP.AMOUNT_APPLIED , 1/NVL(ARAPP.trans_to_receipt_rate,1) CURRENCY_CONVERSION_RATE , BATSRC.name BATCH_SOURCE , HD.CURRENCY_CODE INVOICE_CURRECY_CODE , ARCASH.CASH_RECEIPT_ID , CNSLD.KHR_ID CONTRACT_ID, CN.CONTRACT_NUMBER CONTRACT_NUMBER, ARCASH.CURRENCY_CODE CURRENCY_CODE, NULL DESCRIPTION, ARAPP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER, ARAPP.APPLIED_CUSTOMER_TRX_ID APPLIED_INVOICE_ID, ARAPP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID FROM OKL_CNSLD_AR_STRMS_B CNSLD,AR_PAYMENT_SCHEDULES_ALL PMTSCH,HZ_PARTIES HP,OKL_STRM_TYPE_V STY,AR_RECEIVABLE_APPLICATIONS_ALL ARAPP,AR_CASH_RECEIPTS_ALL ARCASH,AR_RECEIPT_METHODS ARRCPT,OKL_CNSLD_AR_LINES_B LN,OKL_CNSLD_AR_HDRS_B HD,RA_CUSTOMER_TRX_ALL INV,RA_BATCH_SOURCES_ALL BATSRC,HZ_CUST_ACCOUNTS HC,OKC_K_HEADERS_B CN WHERE HD.ID = LN.CNR_ID AND LN.ID = CNSLD.LLN_ID AND CNSLD.RECEIVABLES_INVOICE_ID = PMTSCH.CUSTOMER_TRX_ID AND ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND STY.ID = CNSLD.STY_ID AND ARAPP.cash_receipt_id = ARCASH.cash_receipt_id AND ARRCPT.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID AND PMTSCH.class = 'INV' AND PMTSCH.customer_trx_id = INV.customer_TRX_ID AND INV.batch_source_id = BATSRC.batch_source_id AND HD.IXX_ID = HC.CUST_ACCOUNT_ID AND HC.PARTY_ID = HP.PARTY_ID AND CNSLD.KHR_ID = CN.ID UNION SELECT ARL.RECEIVABLES_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER , PMTSCH.trx_number RECEIVABLE_INVOICE_NUMBER , ARL.AMOUNT INVOICE_AMOUNT , PMTSCH.AMOUNT_DUE_REMAINING BALANCE , (PMTSCH.AMOUNT_DUE_ORIGINAL - PMTSCH.AMOUNT_DUE_REMAINING) AMOUNT_PAID , STY.name STREAM_NAME , ARL.DUE_DATE INVOICE_DATE , ARCASH.RECEIPT_NUMBER CHECK_NUMBER , ARCASH.RECEIPT_DATE , OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE', STY.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING , ARAPP.AMOUNT_APPLIED , 1/NVL(ARAPP.trans_to_receipt_rate,1) CURRENCY_CONVERSION_RATE , BATSRC.name BATCH_SOURCE , ARL.CURRENCY_CODE INVOICE_CURRECY_CODE , ARCASH.CASH_RECEIPT_ID , ARL.CONTRACT_ID CONTRACT_ID, CN.CONTRACT_NUMBER CONTRACT_NUMBER, ARCASH.CURRENCY_CODE CURRENCY_CODE, NULL DESCRIPTION, ARAPP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER, ARAPP.APPLIED_CUSTOMER_TRX_ID APPLIED_INVOICE_ID, ARAPP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID FROM AR_PAYMENT_SCHEDULES_ALL PMTSCH, HZ_PARTIES HP,OKL_STRM_TYPE_V STY, AR_RECEIVABLE_APPLICATIONS_ALL ARAPP, AR_CASH_RECEIPTS_ALL ARCASH, AR_RECEIPT_METHODS ARRCPT, RA_CUSTOMER_TRX_ALL INV, RA_BATCH_SOURCES_ALL BATSRC, HZ_CUST_ACCOUNTS HC, OKC_K_HEADERS_B CN , okl_bpd_ar_inv_lines_v ARL WHERE ARL.RECEIVABLES_INVOICE_ID = PMTSCH.CUSTOMER_TRX_ID AND ARAPP.applied_payment_schedule_id = PMTSCH.payment_schedule_id AND STY.ID = ARL.STY_ID AND ARAPP.cash_receipt_id = ARCASH.cash_receipt_id AND ARRCPT.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID AND PMTSCH.class = 'INV' AND PMTSCH.customer_trx_id = INV.customer_TRX_ID AND INV.batch_source_id = BATSRC.batch_source_id AND ARL.IXX_ID = HC.CUST_ACCOUNT_ID AND HC.PARTY_ID = HP.PARTY_ID AND ARL.CONTRACT_ID = CN.ID

View Text - HTML Formatted

SELECT HD.CONSOLIDATED_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, PMTSCH.TRX_NUMBER RECEIVABLE_INVOICE_NUMBER
, HD.AMOUNT INVOICE_AMOUNT
, PMTSCH.AMOUNT_DUE_REMAINING BALANCE
, (PMTSCH.AMOUNT_DUE_ORIGINAL - PMTSCH.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, STY.NAME STREAM_NAME
, HD.DATE_CONSOLIDATED INVOICE_DATE
, ARCASH.RECEIPT_NUMBER CHECK_NUMBER
, ARCASH.RECEIPT_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, STY.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
, ARAPP.AMOUNT_APPLIED
, 1/NVL(ARAPP.TRANS_TO_RECEIPT_RATE
, 1) CURRENCY_CONVERSION_RATE
, BATSRC.NAME BATCH_SOURCE
, HD.CURRENCY_CODE INVOICE_CURRECY_CODE
, ARCASH.CASH_RECEIPT_ID
, CNSLD.KHR_ID CONTRACT_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ARCASH.CURRENCY_CODE CURRENCY_CODE
, NULL DESCRIPTION
, ARAPP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER
, ARAPP.APPLIED_CUSTOMER_TRX_ID APPLIED_INVOICE_ID
, ARAPP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
FROM OKL_CNSLD_AR_STRMS_B CNSLD
, AR_PAYMENT_SCHEDULES_ALL PMTSCH
, HZ_PARTIES HP
, OKL_STRM_TYPE_V STY
, AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
, AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIPT_METHODS ARRCPT
, OKL_CNSLD_AR_LINES_B LN
, OKL_CNSLD_AR_HDRS_B HD
, RA_CUSTOMER_TRX_ALL INV
, RA_BATCH_SOURCES_ALL BATSRC
, HZ_CUST_ACCOUNTS HC
, OKC_K_HEADERS_B CN
WHERE HD.ID = LN.CNR_ID
AND LN.ID = CNSLD.LLN_ID
AND CNSLD.RECEIVABLES_INVOICE_ID = PMTSCH.CUSTOMER_TRX_ID
AND ARAPP.APPLIED_PAYMENT_SCHEDULE_ID = PMTSCH.PAYMENT_SCHEDULE_ID
AND STY.ID = CNSLD.STY_ID
AND ARAPP.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND ARRCPT.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID
AND PMTSCH.CLASS = 'INV'
AND PMTSCH.CUSTOMER_TRX_ID = INV.CUSTOMER_TRX_ID
AND INV.BATCH_SOURCE_ID = BATSRC.BATCH_SOURCE_ID
AND HD.IXX_ID = HC.CUST_ACCOUNT_ID
AND HC.PARTY_ID = HP.PARTY_ID
AND CNSLD.KHR_ID = CN.ID UNION SELECT ARL.RECEIVABLES_INVOICE_NUMBER CONSOLIDATED_INVOICE_NUMBER
, PMTSCH.TRX_NUMBER RECEIVABLE_INVOICE_NUMBER
, ARL.AMOUNT INVOICE_AMOUNT
, PMTSCH.AMOUNT_DUE_REMAINING BALANCE
, (PMTSCH.AMOUNT_DUE_ORIGINAL - PMTSCH.AMOUNT_DUE_REMAINING) AMOUNT_PAID
, STY.NAME STREAM_NAME
, ARL.DUE_DATE INVOICE_DATE
, ARCASH.RECEIPT_NUMBER CHECK_NUMBER
, ARCASH.RECEIPT_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_STREAM_TYPE_PURPOSE'
, STY.STREAM_TYPE_PURPOSE) STREAM_PURPOSE_MEANING
, ARAPP.AMOUNT_APPLIED
, 1/NVL(ARAPP.TRANS_TO_RECEIPT_RATE
, 1) CURRENCY_CONVERSION_RATE
, BATSRC.NAME BATCH_SOURCE
, ARL.CURRENCY_CODE INVOICE_CURRECY_CODE
, ARCASH.CASH_RECEIPT_ID
, ARL.CONTRACT_ID CONTRACT_ID
, CN.CONTRACT_NUMBER CONTRACT_NUMBER
, ARCASH.CURRENCY_CODE CURRENCY_CODE
, NULL DESCRIPTION
, ARAPP.APPLIED_CUSTOMER_TRX_ID LINE_NUMBER
, ARAPP.APPLIED_CUSTOMER_TRX_ID APPLIED_INVOICE_ID
, ARAPP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
FROM AR_PAYMENT_SCHEDULES_ALL PMTSCH
, HZ_PARTIES HP
, OKL_STRM_TYPE_V STY
, AR_RECEIVABLE_APPLICATIONS_ALL ARAPP
, AR_CASH_RECEIPTS_ALL ARCASH
, AR_RECEIPT_METHODS ARRCPT
, RA_CUSTOMER_TRX_ALL INV
, RA_BATCH_SOURCES_ALL BATSRC
, HZ_CUST_ACCOUNTS HC
, OKC_K_HEADERS_B CN
, OKL_BPD_AR_INV_LINES_V ARL
WHERE ARL.RECEIVABLES_INVOICE_ID = PMTSCH.CUSTOMER_TRX_ID
AND ARAPP.APPLIED_PAYMENT_SCHEDULE_ID = PMTSCH.PAYMENT_SCHEDULE_ID
AND STY.ID = ARL.STY_ID
AND ARAPP.CASH_RECEIPT_ID = ARCASH.CASH_RECEIPT_ID
AND ARRCPT.RECEIPT_METHOD_ID = ARCASH.RECEIPT_METHOD_ID
AND PMTSCH.CLASS = 'INV'
AND PMTSCH.CUSTOMER_TRX_ID = INV.CUSTOMER_TRX_ID
AND INV.BATCH_SOURCE_ID = BATSRC.BATCH_SOURCE_ID
AND ARL.IXX_ID = HC.CUST_ACCOUNT_ID
AND HC.PARTY_ID = HP.PARTY_ID
AND ARL.CONTRACT_ID = CN.ID