OKL Lease Advance Monies

Description

Categories: BI Publisher
Columns: Apl Check Number, Apl Receipt Date, Apl Remittance Amount, Apl Cust Name, Apl Customer Number, Apl Currency Code, Apl Amount, Apl Contract Number, Apl Invoice Number, Apl Trx Date ...

Application: Lease and Finance Management
Source: Lease Advance Monies Report
Short Name: OKLBPADVMRPT
DB package:

Copy

                SELECT DISTINCT ar_cash_receipts.receipt_number APL_CHECK_NUMBER,
                TO_CHAR(ar_cash_receipts.receipt_date,'DD-MON-RRRR') APL_RECEIPT_DATE,
                okl_accounting_util.format_amount(ar_cash_receipts.amount,ar_cash_receipts.CURRENCY_CODE) APL_REMITTANCE_AMOUNT,
                okx_customer_accounts_v.NAME APL_CUST_NAME,
                okx_customer_accounts_v.description APL_CUSTOMER_NUMBER,
                ar_cash_receipts.CURRENCY_CODE APL_CURRENCY_CODE,
                okl_accounting_util.format_amount(( SELECT  NVL(Sum(amount_applied),0)
                FROM    AR_RECEIVABLE_APPLICATIONS_ALL
                WHERE   status = 'ACC'
                AND     cash_Receipt_id = ar_cash_receipts.cash_receipt_id),ar_cash_receipts.CURRENCY_CODE) APL_AMOUNT,
                okc_k_headers_all_b.Contract_Number APL_CONTRACT_NUMBER ,
                ar_payment_schedules_all.trx_number APL_INVOICE_NUMBER,
                ar_payment_schedules_all.TRX_DATE APL_TRX_DATE,
                okl_accounting_util.format_amount(ar_receivable_applications_all.amount_applied,ar_payment_schedules_all.INVOICE_CURRENCY_CODE) APL_AMOUNT_APPLIED
                FROM    ar_cash_receipts,
                        ar_receivable_applications_all,
                        ar_payment_schedules_all,
                        okl_txl_rcpt_apps_all_b,
                        okx_customer_accounts_v,
                        okc_k_headers_all_b, 
                        okl_trx_csh_rcpt_all_b
                WHERE   okl_trx_csh_rcpt_all_b.id = okl_txl_rcpt_apps_all_b.RCT_ID_DETAILS
                AND     ar_cash_receipts.pay_from_customer = okx_customer_accounts_v.ID1
                AND     okc_k_headers_all_b.id(+)         = okl_txl_rcpt_apps_all_b.khr_id
                AND     okl_trx_csh_rcpt_all_b.receipt_type = 'ADV'
                AND     ar_cash_receipts.cash_receipt_id = okl_trx_csh_rcpt_all_b.cash_receipt_id
                AND     ar_cash_receipts.cash_receipt_id = ar_receivable_applications_all.cash_receipt_id
                AND     ar_receivable_applications_all.status = 'APP'
                AND     ar_receivable_applications_all.display = 'Y'
                AND     ar_receivable_applications_all.applied_payment_schedule_id = ar_payment_schedules_all.payment_schedule_id 
                AND     ar_cash_receipts.Receipt_date  > = NVL(:P_FROM_DATE,ar_cash_receipts.Receipt_date)
                AND     ar_cash_receipts.Receipt_date  < = NVL(:P_TO_DATE,ar_cash_receipts.Receipt_date)
                AND     :P_RCPT_APPLIC_STAT IN ('APPLIED','BOTH')
                ORDER BY ar_cash_receipts.receipt_number