[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT start_date, deal_type
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT cra.receipt_date receipt_date
,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount --4884843, 4872370
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_b sty_ln_pmt,
ar_distributions_all ad
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CASH'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty_ln_pmt.id
AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC' )
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
GROUP BY receipt_date
UNION ALL
SELECT cra.receipt_date receipt_date
,SUM(raa.line_applied) receipt_amount --4884843, 4872370
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_b sty_ln_pmt
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CASH'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty_ln_pmt.id
AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.source_type = 'REC'
AND ad.ref_customer_trx_Line_Id IS NULL)
GROUP BY receipt_date
UNION ALL
SELECT cm.trx_date receipt_date
,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) receipt_amount
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ra_customer_trx_all cm,
okl_strm_type_b sty_ln_pmt,
ar_distributions_all ad
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CM'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
AND raa.customer_trx_id = cm.customer_trx_id
AND tld.sty_id = sty_ln_pmt.id
AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC' )
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
GROUP BY cm.trx_date
UNION ALL
SELECT cm.trx_date receipt_date
,SUM(raa.line_applied) receipt_amount
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ra_customer_trx_all cm,
okl_strm_type_b sty_ln_pmt
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CM'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
AND raa.customer_trx_id = cm.customer_trx_id
AND tld.sty_id = sty_ln_pmt.id
AND sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT', 'AMBCOC')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.source_type = 'REC'
AND ad.ref_customer_trx_Line_Id IS NULL)
GROUP BY cm.trx_date
UNION ALL
SELECT adj.apply_date receipt_date
,SUM(nvl(ad.amount_dr,0))- SUM(nvl(ad.amount_cr,0)) receipt_amount
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_adjustments_all adj,
okl_strm_type_b sty,
ar_distributions_all ad
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND adj.customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND adj.status = 'A'
AND adj.apply_date <= NVL(p_due_date, adj.apply_date)
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND adj.adjustment_id = ad.source_id
AND ad.source_table = 'ADJ'
AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
GROUP BY adj.apply_date
ORDER BY receipt_date asc;
SELECT cra.receipt_date receipt_date
,SUM(line_applied) receipt_amount --4884843, 4872370
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CASH'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose in ('UNSCHEDULED_LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
GROUP BY receipt_date
UNION ALL
SELECT cm.trx_date receipt_date
,SUM(line_applied) receipt_amount
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ra_customer_trx_all cm,
okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type = 'CM'
AND raa.status = 'APP'
AND raa.display = 'Y'
AND cm.trx_date <= NVL(p_due_date, cm.trx_date)
AND raa.customer_trx_id = cm.customer_trx_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose in ('UNSCHEDULED_LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
GROUP BY cm.trx_date
UNION ALL
SELECT adj.apply_date receipt_date
,SUM(-1 * NVL(adj.amount,0)) receipt_amount
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_adjustments_all adj,
okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND adj.customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND adj.status = 'A'
AND adj.apply_date <= NVL(p_due_date, adj.apply_date)
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
GROUP BY adj.apply_date
ORDER BY receipt_date asc;
Cursor c_khr_csr(cp_khr_id IN NUMBER) IS select khr.id khr_id
, khr.contract_number
, khr.start_date
, khr.deal_type
, khr.currency_code
, ppm.revenue_recognition_method
, ppm.interest_calculation_basis
, ppm.name product_name
from okl_k_headers_full_v khr
,okl_product_parameters_v ppm
where khr.pdt_id = ppm.id
and ppm.revenue_recognition_method = 'ACTUAL'
and khr.id = NVL(cp_khr_id, khr.id)
order by khr.contract_number;
Cursor c_principal_paid_csr(cp_khr_id IN NUMBER, cp_from_date IN DATE) IS select nvl(sum(sel.amount), 0) principal_paid
from okl_streams_v stm
, okl_strm_type_v sty
, okl_strm_elements_v sel
where stm.khr_id = cp_khr_id
and stm.sty_id = sty.id
and sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
and stm.id = sel.stm_id
and sel.stream_element_date <= trunc(cp_from_date);
Cursor c_excess_principal_paid_csr(cp_khr_id IN NUMBER) IS select nvl(sum(sel.amount), 0) excess_principal_paid
from okl_streams_v stm
, okl_strm_type_v sty
, okl_strm_elements_v sel
where stm.khr_id = cp_khr_id
and stm.sty_id = sty.id
and sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID'
and stm.id = sel.stm_id;
select trunc(cbl.termination_date) term_date
, nvl(sum(cbl.termination_value_amt), 0) term_value
from okl_contract_balances cbl
where cbl.khr_id = cp_khr_id
and cbl.termination_date between cp_from_date and cp_to_date
group by trunc(cbl.termination_date);
select iph.check_date borrower_payment_date
, sum(iph.amount) borrower_payment
from ap_invoices_all ap_inv
, okl_trx_ap_invoices_v okl_inv
, ap_invoice_payment_history_v iph
, okl_txl_ap_inv_lns_all_b okl_inv_ln
, okl_cnsld_Ap_invs_all okl_cnsld
, fnd_application fnd_app
where okl_inv.id = okl_inv_ln.tap_id
and okl_inv_ln.khr_id = cp_khr_id
and ap_inv.application_id = fnd_app.application_id
and fnd_app.application_short_name = 'OKL'
and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
and ap_inv.invoice_id = iph.invoice_id
and iph.check_date BETWEEN cp_from_date AND NVL(cp_to_date, iph.check_date)
group by iph.check_date;
select sum(iph.amount) payment_amount
from ap_invoices_all ap_inv
, okl_trx_ap_invoices_v okl_inv
, ap_invoice_payment_history_v iph
, okl_txl_ap_inv_lns_all_b okl_inv_ln
, okl_cnsld_Ap_invs_all okl_cnsld
, fnd_application fnd_app
where okl_inv.id = okl_inv_ln.tap_id
and okl_inv_ln.khr_id = cp_khr_id
and ap_inv.application_id = fnd_app.application_id
and fnd_app.application_short_name = 'OKL'
and okl_inv_ln.cnsld_ap_inv_id = okl_cnsld.cnsld_ap_inv_id
and okl_cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
and ap_inv.product_table = 'OKL_CNSLD_AP_INVS_ALL'
and ap_inv.invoice_num = okl_inv.vendor_invoice_number
and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
and ap_inv.invoice_id = iph.invoice_id
and iph.check_date <= cp_from_date;
select sum(sel.amount) exist_amount
from okl_streams_v stm
, okl_strm_type_v sty
, okl_strm_elements_v sel
where stm.khr_id = cp_khr_id
and stm.sty_id = sty.id
and sty.stream_type_purpose = cp_sty_purpose
and stm.id = sel.stm_id
and sel.stream_element_date = trunc(cp_receipt_date);
SELECT khr.id khr_id
,khr.contract_number
,khr.authoring_org_id
FROM okl_k_headers_full_v khr,
okl_prod_qlty_val_uv ppm,
okc_statuses_b ste
WHERE khr.contract_number = NVL(cp_contract_number, khr.contract_number)
AND khr.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'REVENUE_RECOGNITION_METHOD'
AND ppm.quality_val = 'ACTUAL'
AND khr.sts_code = ste.code
AND ste.ste_code in ('ACTIVE', 'TERMINATED');