[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(AMOUNT),0) interest_billed_amount
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 (cp_kle_id IS NULL OR (cp_kle_id IS NOT NULL AND lpt.contract_line_id = cp_kle_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));
SELECT NVL(SUM(interest_paid_amount),0) interest_paid_amount
FROM
(
SELECT NVL(SUM(AMOUNT),0)- NVL(SUM(LINE_AMOUNT_REMAINING_WOTAX),0) interest_paid_amount
FROM okl_bpd_ar_inv_lines_v lpt1,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okc_k_headers_b khr
WHERE contract_id=cp_khr_id
AND lpt1.contract_id = khr.id
AND lpt1.sty_id = sty.id
AND (cp_kle_id IS NULL OR (cp_kle_id IS NOT NULL AND lpt1.contract_line_id = cp_kle_id))
AND lpt1.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))
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(cp_kle_id, 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(LINE_AMOUNT_REMAINING_WOTAX) principal_paid_amount
FROM okl_bpd_ar_inv_lines_v lpt1,
okl_strm_type_b sty,
ar_payment_schedules_all aps,
okc_k_headers_b khr
WHERE contract_id=cp_khr_id
AND lpt1.contract_id = khr.id
AND lpt1.sty_id = sty.id
AND lpt1.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))
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 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
AND parameter_type_code = 'ACTUAL';
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
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');
SELECT kle.id
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
SELECT 'Y'
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = 'FEE'
AND kle.fee_type IN('FINANCED','ROLLOVER')
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code not in ( 'HOLD', 'EXPIRED', 'CANCELLED');
SELECT 'Y'
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 str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT 'Y'
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 str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT id trx_try_id
FROM okl_trx_types_tl
WHERE name = p_transaction_type
AND language = 'US';
SELECT khr.pdt_id,
pdt.name pdt_name,
chr.scs_code
FROM okl_k_headers khr,
okl_products pdt,
okc_k_headers_b chr
WHERE khr.id = p_chr_id
AND chr.id = khr.id
AND khr.pdt_id = pdt.id;
SELECT name
FROM okl_trx_types_tl try
WHERE id = p_trx_type_id
AND try.language = USERENV('LANG');
SELECT ACCOUNT_DERIVATION
FROM okl_sys_acct_opts;
SELECT ID
,CODE_COMBINATION_ID
,AE_LINE_TYPE
,CRD_CODE
,ACCOUNT_BUILDER_YN
,PERCENTAGE
FROM OKL_AE_TMPT_LNES
WHERE avl_id = p_avl_id;
SELECT 'Y',
chr.contract_number
FROM okl_strm_elements sel
,okl_streams str
,okl_strm_type_b sty
,okc_k_headers_b chr
WHERE chr.id = p_khr_id
AND sel.stm_id = str.id
AND str.khr_id = chr.id
AND str.kle_id IS NULL
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND str.sty_id = sty.id
AND sty.stream_type_purpose in ('VARIABLE_INTEREST', 'VARIABLE_LOAN_PAYMENT', 'INTEREST_CATCHUP');