OKL Lease Advance Monies

Description
Categories: BI Publisher, Financials
Application: Lease and Finance Management
Source: Lease Advance Monies Report
Short Name: OKLBPADVMRPT
DB package:
                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
Parameter Name SQL text Validation
To Date
 
Date
From Date
 
Date
Receipt Application Status
 
LOV Oracle