OKL Lease Advance Monies

                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

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