[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT id,
deal_type,
start_date,
end_date,
currency_code,
pdt_id,
authoring_org_id
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT interest_basis_code,
calculation_formula_id,
nvl(principal_basis_code, 'ACTUAL') principal_basis_code,
days_in_a_month_code,
days_in_a_year_code,
catchup_settlement_code
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND SYSDATE BETWEEN effective_from_date and nvl(effective_to_date, SYSDATE);
SELECT ppm.revenue_recognition_method,
ppm.interest_calculation_basis
FROM okl_product_parameters_v ppm
WHERE ppm.id = p_product_id;
SELECT hou.name,
book.currency_code
FROM hr_operating_units hou,
GL_LEDGERS_PUBLIC_V book
WHERE hou.set_of_books_id = book.ledger_id
AND hou.ORGANIZATION_ID = mo_global.get_current_org_id();
SELECT vip.interest_calc_start_date,
vip.interest_calc_end_date,
vip.interest_rate,
OKL_ACCOUNTING_UTIL.format_amount(NVL(vip.principal_balance,0),chr.currency_code) principal_balance,
OKL_ACCOUNTING_UTIL.format_amount(NVL(vip.interest_amt,0),chr.currency_code) interest_amt,
vip.interest_calc_days,
--Bug# 12974796
vip.id vir_id,
vip.kle_id,
cle.name asset_number,
DECODE(NVL(vip.catchup_amt,0),0,' ',OKL_ACCOUNTING_UTIL.format_amount(NVL(vip.catchup_amt,0),chr.currency_code)) catchup_amt,
vip.catchup_settlement_code
FROM okl_var_int_params vip,
okc_k_headers_b chr,
okc_k_lines_v cle
WHERE chr.id = p_contract_id
AND vip.khr_id = chr.id
AND vip.request_id = g_request_id
AND vip.kle_id = cle.id
ORDER BY asset_number, vip.interest_calc_start_date;
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = p_lookup_code
AND lookup_type = p_lookup_type;
SELECT vip.interest_calc_start_date,
vip.interest_calc_end_date,
vip.interest_rate,
SUM(NVL(vip.principal_balance,0)) principal_balance,
SUM(NVL(vip.interest_amt,0)) interest_amt,
vip.interest_calc_days,
chr.currency_code
FROM okl_var_int_params vip,
okc_k_headers_b chr
WHERE vip.khr_id = p_contract_id
AND chr.id = vip.khr_id
AND vip.request_id = g_request_id
GROUP BY vip.interest_calc_start_date, vip.interest_calc_end_date, vip.interest_rate, vip.interest_calc_days, chr.currency_code
ORDER BY vip.interest_calc_start_date;
SELECT rule_information3
FROM okc_rules_b rule,
okc_rule_groups_b rgp
WHERE rgp.id = rule.rgp_id
AND rgp.dnz_chr_id = p_khr_id
AND rgd_code = 'LABILL'
AND rule_information_category = 'LAINVD';
SELECT term.printing_lead_days
FROM okl_k_headers_full_v khr
,hz_customer_profiles cp
,ra_terms term
WHERE khr.id = p_khr_id
AND khr.bill_to_site_use_id = cp.site_use_id
AND cp.standard_terms = term.term_id;
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.kle_id = p_line_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.kle_id = p_line_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.kle_id = p_line_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.kle_id = p_line_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.khr_id = p_khr_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = p_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT nvl(principal_basis_code, 'ACTUAL')
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';
SELECT start_date, currency_code
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT sel.stream_element_date stream_element_date,
sel.amount amount
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = p_line_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
--Bug# 12903938
AND sel.stream_element_date BETWEEN p_start_date AND p_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
--Bug# 12903938
ORDER BY sel.stream_element_date;
SELECT sel.stream_element_date stream_element_date,
SUM(amount) amount
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
,okc_k_lines_b cle
,okc_statuses_b sts
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
--Bug# 12903938
AND sel.stream_element_date BETWEEN p_start_date AND p_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND cle.chr_id = str.khr_id
AND cle.dnz_chr_id = str.khr_id
AND cle.id = str.kle_id
AND cle.lse_id = G_FIN_LINE_LTY_ID
AND cle.sts_code = sts.code
AND sts.ste_code <> 'CANCELLED'
--Bug# 12903938
GROUP BY sel.stream_element_date
ORDER BY sel.stream_element_date;
SELECT sel.stream_element_date stream_element_date,
sel.amount amount
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = p_line_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date = (SELECT MAX(sel1.stream_element_date)
FROM okl_strm_elements sel1
WHERE sel1.stm_id = sel.stm_id
AND sel1.stream_element_date < p_start_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT sel.stream_element_date stream_element_date,
SUM(amount) amount
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
,okc_k_lines_b cle
,okc_statuses_b sts
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date = (SELECT MAX(sel1.stream_element_date)
FROM okl_strm_elements sel1
WHERE sel1.stm_id = sel.stm_id
AND sel1.stream_element_date < p_start_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND cle.chr_id = str.khr_id
AND cle.dnz_chr_id = str.khr_id
AND cle.id = str.kle_id
AND cle.lse_id = G_FIN_LINE_LTY_ID
AND cle.sts_code = sts.code
AND sts.ste_code <> 'CANCELLED'
GROUP BY stream_element_date;
SELECT cra.receipt_date receipt_date
,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) principal_pmt_rcpt_amt -- 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,
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 tld.kle_id = NVL(p_line_id, tld.kle_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 ('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 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 cra.receipt_date
UNION ALL
SELECT cra.receipt_date receipt_date
,SUM(raa.line_applied) principal_pmt_rcpt_amt -- 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 tld.kle_id = NVL(p_line_id, tld.kle_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 ('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 EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.ref_customer_trx_Line_Id IS NULL)
GROUP BY cra.receipt_date
UNION ALL
SELECT ocb.termination_date receipt_date,
sum(ocb.termination_value_amt) principal_pmt_rcpt_amt
FROM okl_contract_balances ocb
WHERE ocb.khr_id = p_contract_id
AND ocb.kle_id = NVL(p_line_id, kle_id)
AND ocb.termination_date BETWEEN p_start_date AND p_due_date
GROUP BY ocb.termination_date
UNION ALL
SELECT sel.stream_element_date receipt_date,
sum(sel.amount) principal_pmt_rcpt_amt
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = NVL(p_line_id, str.kle_id)
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date BETWEEN p_start_date AND NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
GROUP BY sel.stream_element_date
UNION ALL
SELECT cm.trx_date receipt_date
,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) principal_pmt_rcpt_amt
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,
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 tld.kle_id = NVL(p_line_id, tld.kle_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 ('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 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) principal_pmt_rcpt_amt -- 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,
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 tld.kle_id = NVL(p_line_id, tld.kle_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 ('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 EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
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)) principal_pmt_rcpt_amt
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 tld.kle_id = NVL(p_line_id, tld.kle_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 sel.stream_element_date receipt_date,
sum(sel.amount) principal_pmt_rcpt_amt
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = NVL(p_line_id, str.kle_id)
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
GROUP BY sel.stream_element_date
ORDER BY receipt_date asc;
lx_receipt_tbl.delete;
lx_receipt_tbl.delete;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_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 = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date <= NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION
SELECT sel.stream_element_date txn_date,
sum(sel.amount) txn_amount,
'R' txn_type
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
GROUP BY sel.stream_element_date
ORDER BY txn_date asc, txn_type;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_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 = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date <= NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION ALL
SELECT cra.receipt_date txn_date,
sum(raa.line_applied) txn_amount, -- 4884843, 4872370
'R' txn_type
FROM okl_bpd_tld_ar_lines_v tld,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_v sty
WHERE tld.trx_status_code = 'PROCESSED'
AND tld.khr_id = NVL(p_contract_id, tld.khr_id)
AND tld.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 = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
GROUP BY cra.receipt_date
UNION ALL
SELECT cm.trx_date txn_date,
sum(raa.line_applied) txn_amount,
'R' txn_type
FROM okl_bpd_tld_ar_lines_v tld,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ra_customer_trx_all cm,
okl_strm_type_v sty
WHERE tld.trx_status_code = 'PROCESSED'
AND tld.khr_id = NVL(p_contract_id, tld.khr_id)
AND tld.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 = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
GROUP BY cm.trx_date
UNION ALL
SELECT adj.apply_date txn_date,
sum( -1 * NVL(adj.amount,0)) txn_amount,
'R' txn_type
FROM okl_bpd_tld_ar_lines_v tld,
ar_payment_schedules_all aps,
ar_adjustments_all adj,
okl_strm_type_v sty
WHERE tld.trx_status_code = 'PROCESSED'
AND tld.khr_id = NVL(p_contract_id, tld.khr_id)
AND tld.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 = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
GROUP BY adj.apply_date
ORDER BY txn_date asc, txn_type;
lx_receipt_tbl.delete;
SELECT deal_type
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
CURSOR c_int_calc_date_csr(cp_khr_id NUMBER) IS SELECT (max(sel.stream_element_date) - 1) last_interest_calc_date
FROM okl_streams stm,
okl_strm_elements sel,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND stm.id = sel.stm_id
AND stm.sty_id = sty.id
AND (sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL' OR sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST');
CURSOR c_khr_start_date_csr(cp_khr_id NUMBER) IS SELECT start_date - 1
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT Fnd_Date.canonical_to_date(rulb2.RULE_INFORMATION2) start_date,
rulb2.RULE_INFORMATION3 level_periods,
rulb2.RULE_INFORMATION7 stub_days,
rulb2.RULE_INFORMATION8 stub_amount,
rulb2.object1_id1 frequency
FROM okc_k_lines_b cleb,
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id IS NULL
AND rgpb.dnz_chr_id = cleb.dnz_chr_id
AND rgpb.cle_id = cleb.id
AND cleb.id = p_cle_id
AND cleb.dnz_chr_id = p_contract_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
AND styb.STREAM_TYPE_PURPOSE IN ('RENT', 'LOAN_PAYMENT', 'PRINCIPAL_PAYMENT')
ORDER BY start_date;
SELECT cle.id
FROM okc_k_lines_v cle,
okc_line_styles_b sty,
okc_statuses_b sts
WHERE cle.lse_id = sty.id
AND cle.dnz_chr_id = p_contract_id
AND sty.lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT krp.interest_index_id
, NVL(krp.base_rate, 0) base_rate
, NVL(krp.interest_start_date, chr.start_date) interest_start_date
, NVL(krp.adder_rate, 0) adder_rate
, NVL(krp.maximum_rate, 9999) maximum_rate
, NVL(krp.minimum_rate, 0) minimum_rate
, krp.rate_delay_code
, NVL(krp.rate_delay_frequency, 0) rate_delay_frequency
, NVL(krp.rate_change_start_date, chr.start_date) rate_change_start_date
, NVL(krp.rate_change_frequency_code, 'DAILY') rate_change_frequency_code
, NVL(krp.rate_change_value, 0) rate_change_value
--if the calling process is Daily Interest, get the last int cal date from
--the Daily Interest streams
--for a REAMORT contract, if the calling process is Reamortization (initiate_request)
--and it is being run for the first time for the contract, the last interest calc date
--defaults to the start date (as there may be a previously derived value due to the fact
--that the Reamort may not have completed in its entirety)
--if it is called from any other process for a REAMORT contract, the last int calc date
--defaults to start date minus 1
--for all other types of contracts, the last int calc date
--defaults to start date minus 1
, decode(p_process_flag, 'DAILY_INTEREST', get_last_int_calc_date(chr.id),
decode(ppm.quality_val, 'REAMORT', NVL(khr.date_last_interim_interest_cal,
decode(OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE, 'REAMORT', chr.start_date, chr.start_date-1)), NVL(khr.date_last_interim_interest_cal, chr.start_date - 1))) date_last_interim_interest_cal
, chr.start_date contract_start_date
, chr.end_date contract_end_date
, chr.id khr_id
, ppm.quality_val interest_calculation_basis
, NULL pay_freq
FROM okl_k_rate_params krp
, okl_k_headers khr
, okc_k_headers_b chr
, okl_prod_qlty_val_uv ppm
WHERE krp.rowid = cp_rate_param_rowid
AND krp.khr_id = khr.id
AND khr.id = chr.id
--AND TRUNC(SYSDATE) BETWEEN krp.effective_from_date and NVL(krp.effective_to_date, trunc(SYSDATE))
--AND krp.parameter_type_code = 'ACTUAL'
AND khr.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS';
SELECT DISTINCT
vip.interest_rate VALUE,
GREATEST(trunc(cp_start_date),interest_calc_start_date) VALID_FROM,
LEAST(trunc(cp_end_date),NVL(interest_calc_end_date, trunc(sysdate))) VALID_UNTIL
FROM okl_var_int_params vip
WHERE vip.khr_id = cp_khr_id
AND (cp_start_date BETWEEN vip.interest_calc_start_date AND nvl(vip.interest_calc_end_date, trunc(cp_start_date))
OR (cp_end_date BETWEEN vip.interest_calc_start_date AND nvl(vip.interest_calc_end_date, trunc(cp_end_date)))
OR (vip.interest_calc_start_date >= cp_start_date AND nvl(vip.interest_calc_end_date, trunc(sysdate + 9999)) <= cp_end_date) )
AND vip.calc_method_code = NVL(cp_process_flag, vip.calc_method_code)
AND vip.valid_yn = 'Y'
ORDER BY VALID_FROM;
select sll_rulb.object1_id1 pay_freq
from okc_rules_b sll_rulb,
okc_rules_b slh_rulb,
okl_strm_type_b styb,
okc_rule_groups_b rgpb
where sll_rulb.rgp_id = rgpb.id
and sll_rulb.rule_information_category = 'LASLL'
and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
and sll_rulb.object2_id1 = to_char(slh_rulb.id)
and slh_rulb.rgp_id = rgpb.id
and slh_rulb.rule_information_category = 'LASLH'
and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
and styb.id = slh_rulb.object1_id1
and styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
and rgpb.dnz_chr_id = cp_khr_id
and rgpb.rgd_code = 'LALEVL'
order by sll_rulb.rule_information1;
SELECT ive.value VALUE,
GREATEST(trunc(cp_start_date),datetime_valid) VALID_FROM,
LEAST(trunc(cp_end_date),NVL(datetime_invalid, GREATEST(trunc(sysdate),trunc(cp_end_date)))) VALID_UNTIL
FROM okl_indices idx,
okl_index_values ive
WHERE idx.id = cp_index_id
AND idx.id = ive.idx_id
AND (cp_start_date BETWEEN ive.datetime_valid AND nvl(ive.datetime_invalid, trunc(cp_start_date))
OR (cp_end_date BETWEEN ive.datetime_valid AND nvl(ive.datetime_invalid, trunc(cp_end_date)))
OR (ive.datetime_valid >= cp_start_date AND nvl(ive.datetime_invalid, trunc(sysdate + 9999)) <= cp_end_date) )
ORDER BY VALID_FROM;
SELECT NVL(khr.date_last_interim_interest_cal, chr.start_date) last_interest_calc_date
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE chr.id = p_khr_id
AND khr.id = chr.id;
l_eff_int_tbl.delete;
select interest_calc_start_date
,interest_calc_end_date
,interest_rate
from (select vip.interest_calc_start_date
,vip.interest_calc_end_date
,vip.interest_rate
from okl_var_int_params vip
where vip.khr_id = cp_khr_id
and vip.interest_calc_end_date < cp_from_date
AND vip.calc_method_code = NVL(cp_process_flag, vip.calc_method_code)
AND vip.valid_yn = 'Y'
order by vip.interest_calc_end_date desc)
where rownum = 1;
l_eff_int_with_tol_tbl.delete;
l_int_param_tbl.delete;
l_interest_rate_tbl.delete;
SELECT krp.rowid rate_param_rowid,
GREATEST(trunc(cp_start_date),krp.effective_from_date) start_date,
LEAST(trunc(cp_end_date),NVL(krp.effective_to_date, trunc(sysdate + 9999))) end_date
FROM okl_k_rate_params krp
WHERE krp.khr_id = cp_khr_id
AND krp.parameter_type_code = 'ACTUAL'
AND (cp_start_date BETWEEN krp.effective_from_date AND nvl(krp.effective_to_date, trunc(cp_start_date))
OR (cp_end_date BETWEEN krp.effective_from_date AND nvl(krp.effective_to_date, trunc(cp_end_date)))
OR (krp.effective_from_date >= cp_start_date AND nvl(krp.effective_to_date, trunc(sysdate + 9999)) <= cp_end_date));
l_interest_rate_tbl.delete;
l_interest_rate_tbl_out.delete;
g_vir_tbl(g_vir_tbl_counter).program_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_updated_by := FND_GLOBAL.user_id;
g_vir_tbl(g_vir_tbl_counter).last_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_update_login := FND_GLOBAL.login_id;
print_debug( 'Program Update date : '|| p_vir_tbl(l_index).program_update_date);
print_debug( 'last_updated_by : '|| p_vir_tbl(l_index).last_updated_by);
print_debug( 'last_update_date : '|| p_vir_tbl(l_index).last_update_date);
print_debug( 'last_update_login : '|| p_vir_tbl(l_index).last_update_login);
SELECT 'Y' return_value
FROM okc_k_headers_b chrb,
okc_k_headers_b chrb2,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_old = chrb.id
AND ktrx.tsu_code = 'PROCESSED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND chrb.id = cp_khr_id
AND chrb2.orig_system_source_code = 'OKL_REBOOK'
AND chrb2.id = ktrx.khr_id_new
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = chrb.id
AND vpb.source_table = 'OKL_TRX_CONTRACTS'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ktrx.id);
SELECT 'Y' return_value
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements 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 < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT 'Y' return_value
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
AND stm.id = sel.stm_id
AND sel.stream_element_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT /*+ leading(tai, til, tld, sty, ractrl, aps) */
'Y' return_value
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 < cp_from_date
AND raa.cash_receipt_id = cra.cash_receipt_id
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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_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.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT 'Y' return_value
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,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 = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = okl_inv_ln.khr_id
AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = iph.invoice_payment_id);
SELECT 'Y' return_value
FROM okl_contract_balances ocb
WHERE ocb.khr_id = cp_khr_id
AND ocb.termination_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = ocb.khr_id
AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ocb.id);
SELECT /*+ leading(tai, til, tld, sty, ractrl, aps) */
'Y' return_value
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 < cp_from_date
AND raa.customer_trx_id = cm.customer_trx_id
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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_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.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT /*+ leading(tai, til, tld, sty, ractrl, aps) */
'Y' return_value
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 < cp_from_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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_ADJUSTMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = adj.adjustment_id);
SELECT ktrx.id source_id
,'OKL_TRX_CONTRACTS' source_table
FROM okc_k_headers_b chrb,
okc_k_headers_b chrb2,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_old = chrb.id
AND ktrx.tsu_code = 'PROCESSED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND chrb.id = cp_khr_id
AND chrb2.orig_system_source_code = 'OKL_REBOOK'
AND chrb2.id = ktrx.khr_id_new
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = chrb.id
AND vpb.source_table = 'OKL_TRX_CONTRACTS'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ktrx.id);
SELECT sel.id source_id
, 'OKL_STRM_ELEMENTS_V' source_table
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements 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 BETWEEN cp_from_date AND cp_to_date
AND sel.stream_element_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT sel.id source_id
, 'OKL_STRM_ELEMENTS_V' source_table
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
AND stm.id = sel.stm_id
--AND sel.stream_element_date BETWEEN cp_from_date AND cp_to_date
AND sel.stream_element_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT DISTINCT raa.receivable_application_id source_id
, 'AR_RECEIVABLE_APPLICATIONS_ALL' source_table
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 <= cp_to_date
AND raa.cash_receipt_id = cra.cash_receipt_id
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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_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.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT iph.invoice_payment_id source_id
, 'AP_INVOICE_PAYMENTS_ALL' source_table
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,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 = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
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 cp_to_date
AND iph.check_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = okl_inv_ln.khr_id
AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = iph.invoice_payment_id);
SELECT ocb.id source_id
, 'OKL_CONTRACT_BALANCES' source_table
FROM okl_contract_balances ocb
WHERE ocb.khr_id = cp_khr_id
AND ocb.termination_date BETWEEN cp_from_date AND cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = ocb.khr_id
AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ocb.id);
SELECT DISTINCT raa.receivable_application_id source_id
, 'AR_RECEIVABLE_APPLICATIONS_ALL' source_table
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 <= cp_to_date
AND raa.customer_trx_id = cm.customer_trx_id
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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_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.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT DISTINCT adj.adjustment_id source_id
, 'AR_ADJUSTMENTS_ALL' source_table
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 = cp_khr_id
AND tld.khr_id = cp_khr_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 <= cp_to_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 NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_ADJUSTMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = adj.adjustment_id);
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
print_debug( 'Program Update date : '|| p_vpb_tbl(l_index).program_update_date);
print_debug( 'last_updated_by : '|| p_vpb_tbl(l_index).last_updated_by);
print_debug( 'last_update_date : '|| p_vpb_tbl(l_index).last_update_date);
print_debug( 'last_update_login : '|| p_vpb_tbl(l_index).last_update_login);
SELECT id
FROM okl_k_lines_full_v
WHERE chr_id = p_khr_id
AND lse_id = G_FIN_LINE_LTY_ID
AND nvl(date_terminated, p_due_date + 1) > p_due_date
ORDER BY id;
OKL_VR_DATA_HANDLER_PVT.insert_vir_params(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_TRUE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vir_tbl => g_vir_tbl);
g_vir_tbl.delete;
SELECT name
FROM okl_formulae_v
WHERE id = p_formula_id;
SELECT NVL(rul2.rule_information10,'N') arrears_yn
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = rgp.dnz_chr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND rgp.cle_id IS NULL
AND rgp.chr_id = khrid
AND rgp.dnz_chr_id = khrid
AND rgp.rgd_code = 'LALEVL'
AND rul1.dnz_chr_id = rgp.dnz_chr_id
AND rul1.rule_information_category = 'LASLH'
AND rul1.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND TO_NUMBER(rul1.object1_id1) = sty.id
AND sty.stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE'
ORDER BY FND_DATE.canonical_to_date(rul2.rule_information2);
SELECT 'Y'
FROM okl_var_int_params
WHERE khr_id = p_chr_id
AND request_id = p_req_id;
SELECT distinct khr.id khr_id,
khr.deal_type,
khr.date_last_interim_interest_cal,
chr.contract_number,
chr.start_date start_date,
chr.date_terminated termination_date,
chr.end_date end_date,
chr.currency_code,
ppm.interest_calculation_basis,
ppm.revenue_recognition_method,
chr.authoring_org_id,
khr.pdt_id,
--Bug# 7277007
iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bankruptcy_sts, --- Added as part of Bug# 14119181
ppm.name product_name
FROM okc_k_headers_b chr,
okl_k_headers khr,
okc_statuses_b khs,
okl_product_parameters_v ppm,
hz_cust_accounts hca --- Added as part of Bug# 14119181
WHERE CHR.CONTRACT_NUMBER = NVL(p_contract_number,CHR.CONTRACT_NUMBER)
AND khr.deal_type IN ('LOAN','LEASEDF','LOAN-REVOLVING','LEASEST','LEASEOP')
AND chr.id = khr.id
AND khs.code = chr.sts_code
AND khs.ste_code = 'ACTIVE'
AND khr.pdt_id = ppm.id
AND ppm.interest_calculation_basis <> 'FIXED'
AND hca.cust_account_id = chr.cust_acct_id --- Added as part of Bug# 14119181
AND hca.status = 'A' --- Added as part of Bug# 14119181
ORDER BY khr.deal_type, chr.contract_number;
SELECT interest_basis_code,
calculation_formula_id,
nvl(principal_basis_code, 'ACTUAL'),
days_in_a_month_code,
days_in_a_year_code,
rate_change_value,
catchup_settlement_code
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';