[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(AMOUNT),0) interest_billed_amount -- lpt.RECEIVABLES_INVOICE_ID
--dcshanmu bug 6734738 end
FROM okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
--dcshanmu bug 6734738 start
WHERE lpt.contract_id = cp_khr_id
--dcshanmu bug 6734738 end
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose
IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
AND TRUNC(NVL(cp_to_date,SYSDATE))
--dcshanmu bug 6734738 start
--)
;
SELECT sum(aps.amount_due_original) interest_billed_amount
FROM okl_cnsld_ar_strms_b lsm,
okl_strm_type_v sty,
okl_strm_elements sel,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE lsm.khr_id = cp_khr_id
AND lsm.khr_id = khr.id
AND lsm.sty_id = sty.id
AND sty.stream_type_purpose = 'VARIABLE_LOAN_PAYMENT'
AND lsm.sel_id = sel.id
AND sel.sel_id IS NULL
AND lsm.receivables_invoice_id = aps.customer_trx_id
AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE)));
SELECT NVL(SUM(interest_paid_amount),0) interest_paid_amount
FROM
(
SELECT NVL(SUM(AMOUNT),0)- NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0) interest_paid_amount
FROM okl_bpd_ar_inv_lines_v lpt1
where contract_id=cp_khr_id
and RECEIVABLES_INVOICE_ID in
(
SELECT RECEIVABLES_INVOICE_ID
FROM
okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE
lpt.contract_id = lpt1.contract_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose IN
('INTEREST_PAYMENT', 'VARIABLE_INTEREST','INTEREST_CATCHUP')
AND TRUNC(aps.trx_date)
BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
)
group by RECEIVABLES_INVOICE_ID
having NVL(SUM(AMOUNT),0) < NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0)
UNION ALL
SELECT NVL(SUM(sel.amount),0) interest_paid_amount
FROM okl_strm_type_v sty,
okl_streams_v stm,
okl_strm_elements sel,
okc_k_headers_b khr
WHERE stm.khr_id = cp_khr_id
AND stm.kle_id = NVL(null, stm.kle_id)
AND stm.khr_id = khr.id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
AND stm.id = sel.stm_id
AND TRUNC(sel.stream_element_date)
BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
);
SELECT principal_basis_code
FROM okl_k_rate_params
WHERE khr_id = cp_khr_id
AND parameter_type_code = 'ACTUAL'
AND TRUNC(SYSDATE) BETWEEN effective_from_date AND NVL(effective_to_date, TRUNC(SYSDATE));
SELECT chr.start_date,
chr.currency_code,
chr.end_date
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.id = khr.id
AND khr.id = cp_khr_id;
SELECT ppm.interest_calculation_basis
,ppm.revenue_recognition_method
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.id = cp_khr_id
AND khr.pdt_id = ppm.id;
SELECT NVL(SUM(amount),0)
FROM okl_strm_elements sel,
okl_streams str,
okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = cp_khr_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= p_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT';
SELECT chr.start_date, rpm.principal_basis_code
FROM okc_k_headers_b chr,
okl_k_headers khr,
okl_k_rate_params rpm
WHERE chr.id = khr.id
AND rpm.khr_id = khr.id
AND rpm.parameter_type_code = 'ACTUAL'
AND TRUNC(SYSDATE) BETWEEN rpm.effective_from_date AND NVL(rpm.effective_to_date, TRUNC(SYSDATE))
AND khr.id = cp_khr_id;
SELECT MAX(sel.stream_element_date)
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 <= p_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT MAX(sel.stream_element_date)
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 <= p_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT ppm.interest_calculation_basis
,ppm.revenue_recognition_method
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.id = cp_khr_id
AND khr.pdt_id = ppm.id;
SELECT deal_type
FROM okl_k_headers
WHERE id = p_contract_id;
SELECT SUM(principal_paid_amount) principal_paid_amount
FROM
(
SELECT SUM(AMOUNT)- SUM(AMOUNT_LINE_ITEMS_REMAINING) principal_paid_amount
FROM okl_bpd_ar_inv_lines_v lpt1
where contract_id=cp_khr_id
and RECEIVABLES_INVOICE_ID in
(
SELECT RECEIVABLES_INVOICE_ID
FROM
okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE
lpt.contract_id = lpt1.contract_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose IN
('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
AND TRUNC(aps.trx_date)
BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
)
group by RECEIVABLES_INVOICE_ID
having SUM(AMOUNT_LINE_ITEMS_REMAINING)< SUM(AMOUNT)
UNION ALL
SELECT NVL(SUM(sel.amount),0) principal_paid_amount
FROM okl_strm_type_v sty,
okl_streams_v stm,
okl_strm_elements sel,
okc_k_headers_b khr
WHERE stm.khr_id = cp_khr_id
AND stm.kle_id = NVL(null, stm.kle_id)
AND stm.khr_id = khr.id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
AND stm.id = sel.stm_id
AND TRUNC(sel.stream_element_date)
BETWEEN TRUNC(NVL(cp_from_date, khr.start_date)) AND TRUNC(NVL(cp_to_date, SYSDATE))
);
SELECT interest_calc_end_date, interest_rate
FROM okl_var_int_params
WHERE khr_id = cp_khr_id
ORDER BY interest_calc_end_date DESC;
SELECT ppm.quality_val interest_calculation_basis
, end_date
FROM okl_k_headers_full_v khr,
okl_prod_qlty_val_uv ppm
WHERE khr.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS'
AND khr.id = cp_khr_id;
SELECT chr.start_date
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.id = khr.id
AND khr.id = cp_khr_id;
SELECT NVL(SUM(AMOUNT),0) principal_billed_amount --RECEIVABLES_INVOICE_ID
-- dcshanmu bug 6734738 end
FROM okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE
-- dcshanmu bug 6734738 start
lpt.contract_id = cp_khr_id
-- dcshanmu bug 6734738 end
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose IN
('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP')
AND TRUNC(aps.trx_date) BETWEEN TRUNC(NVL(cp_from_date, khr.start_date))
AND TRUNC(NVL(cp_to_date, SYSDATE))
-- dcshanmu bug 6734738 start
--)
;
SELECT NVL(SUM(AMOUNT_DUE_ORIGINAL),0) interest_billed_amount
FROM okl_bpd_ar_inv_lines_v lpt1
where contract_id=cp_khr_id
and RECEIVABLES_INVOICE_ID in
(
SELECT lpt.RECEIVABLES_INVOICE_ID
FROM okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE lpt.contract_id = lpt1.contract_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose = 'FLOAT_FACTOR_ADJUSTMENT'
AND TRUNC(aps.trx_date) <= cp_effective_date
);
SELECT NVL(SUM(lpt1.AMOUNT_DUE_ORIGINAL),0) loan_billed_amount
FROM okl_bpd_ar_inv_lines_v lpt1
where contract_id=cp_khr_id
and RECEIVABLES_INVOICE_ID in
(
SELECT lpt.RECEIVABLES_INVOICE_ID
FROM okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE lpt.contract_id = lpt1.contract_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
AND TRUNC(aps.trx_date) <= cp_effective_date
);
SELECT
(NVL(SUM(AMOUNT_DUE_ORIGINAL),0)- NVL(SUM(AMOUNT_LINE_ITEMS_REMAINING),0)) loan_paid_amount
FROM okl_bpd_ar_inv_lines_v lpt1
where contract_id=cp_khr_id
and RECEIVABLES_INVOICE_ID in
(
SELECT lpt.RECEIVABLES_INVOICE_ID
FROM okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE lpt.contract_id = lpt1.contract_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND sty.stream_type_purpose IN
('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT')
AND TRUNC(aps.trx_date) <= cp_effective_date
);
SELECT NVL(SUM(sel.amount), 0) loan_excess_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.id = sel.stm_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'EXCESS_LOAN_PAYMENT_PAID';
SELECT date_last_interim_interest_cal
FROM okl_k_headers
WHERE id = cp_khr_id;
SELECT pdt_id
FROM okl_k_headers
WHERE id = cp_khr_id;
SELECT DECODE(sll.object1_id1,'M',1,'Q',3,'S',6,'A',12) pay_freq
FROM okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_v styp,
okc_rule_groups_b rgp
WHERE TO_NUMBER(sll.object2_id1) = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.dnz_chr_id = rgp.dnz_chr_id
AND sll.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND slh.dnz_chr_id = rgp.dnz_chr_id
AND slh.rgp_id = rgp.id
AND slh.object1_id1 = styp.id
AND styp.stream_type_purpose = 'RENT'
AND rgp.rgd_code = 'LALEVL'
AND rgp.dnz_chr_id = cp_khr_id
AND ROWNUM < 2;
SELECT chr.start_date,
chr.end_date
FROM OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE chr.id = khr.id
AND khr.id = cp_khr_id;
SELECT max(sel.stream_element_date)
FROM okl_streams_v stm,
okl_strm_type_v sty,
okl_strm_elements_v sel
WHERE stm.khr_id = cp_khr_id
AND stm.id = sel.stm_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND sel.stream_element_date <= cp_term_date;
SELECT catchup_start_date,DECODE(catchup_frequency_code,'MONTHLY',1,'QUARTERLY',3,'SEMI_ANNUAL',6,'ANNUAL',12) pay_freq
FROM okl_k_rate_params
WHERE khr_id = cp_khr_id;
SELECT
lpt.amount_due_remaining,
lpt.due_date INVOICE_DATE,
lpt.TXD_ID LSM_ID,
lpt.TXD_ID tld_id,
aps.customer_trx_id receivables_invoice_id
FROM
okl_bpd_ar_inv_lines_v lpt,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okl_k_headers_full_v khr
WHERE
lpt.CONTRACT_ID = cp_khr_id
AND lpt.contract_number = khr.contract_number
AND lpt.sty_id = sty.id
AND lpt.RECEIVABLES_INVOICE_ID = aps.customer_trx_id
AND aps.status = 'OP'
AND aps.class = 'INV'
AND sty.stream_type_purpose
IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT');