[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cleb_fin.id
FROM okc_k_lines_b cleb_fin,
okc_statuses_b sts
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND cleb_fin.lse_id = 33
AND cleb_fin.sts_code = sts.code
AND sts.ste_code <> 'CANCELLED';
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(sll.rule_information7) stub_days,
TO_NUMBER(sll.rule_information3) periods,
DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12) frequency,
sll.rule_information10 arrears_yn
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id
AND slh.object1_id1 = sty.id
AND sty.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT')
ORDER BY start_date;
SELECT bill_date
,SUM(principal) principal
,SUM(interest) interest
, 0 princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date
,sel.amount principal
,0 interest
,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
-- AND sel.date_billed IS NOT NULL --Billing is done in OKL
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
UNION ALL
SELECT sel.stream_element_date bill_date
,0 principal
,sel.amount interest
,decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
AND sel.stm_id = stm.id
-- AND sel.date_billed IS NOT NULL --Billing is done in OKL
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date,payment_type
UNION ALL
SELECT bill_date,
0 principal,
0 interest,
SUM(princ_pay_down) princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
0 interest,
sel.amount princ_pay_down,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
)
-- principal paydown rebooks the contract and all streams are regenerated
-- Loan Payment Paydown is not permitted with Rev Rec = 'STREAMS'
GROUP BY bill_date, payment_type
ORDER BY bill_date;
l_temp_tbl.DELETE;
SELECT bill_date
,(SUM(principal) + SUM(interest)) total_amount
,SUM(principal) principal
,SUM(interest) interest
FROM
(
SELECT sel.stream_element_date bill_date
,sel.amount principal
,0 interest
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
-- AND sel.date_billed IS NOT NULL --Billing is done in OKL
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
UNION ALL
SELECT sel.stream_element_date bill_date
,0 principal
,sel.amount interest
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
AND sel.stm_id = stm.id
-- AND sel.date_billed IS NOT NULL --Billing is done in OKL
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date
ORDER BY bill_date;
SELECT sel.stream_element_date bill_date
,sum(sel.amount) ppd_amount
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
-- AND sel.date_billed IS NOT NULL --Billing is done in OKL
AND stm.SAY_CODE = 'CURR'
AND stm.active_yn = 'Y'
GROUP BY sel.stream_element_date
ORDER BY bill_date;
l_temp_tbl.DELETE;
SELECT receipt_date,
SUM(principal) principal,
SUM(interest) interest
FROM
(
SELECT sel_dii.stream_element_date receipt_date,
0 principal,
sel_dii.amount interest,
sel_dii.request_id
FROM okl_strm_elements sel_dii,
okl_streams stm_dii,
okl_strm_type_b sty_dii
WHERE stm_dii.khr_id = p_chr_id
AND sty_dii.id = stm_dii.sty_id
AND sty_dii.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
AND sel_dii.stm_id = stm_dii.id
AND stm_dii.say_code = 'CURR'
AND stm_dii.active_yn = 'Y'
UNION ALL
SELECT sel_dip.stream_element_date receipt_date,
sel_dip.amount principal,
0 interest,
sel_dip.request_id
FROM okl_strm_elements sel_dip,
okl_streams stm_dip,
okl_strm_type_b sty_dip
WHERE stm_dip.khr_id = p_chr_id
AND sty_dip.id = stm_dip.sty_id
AND sty_dip.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
AND sel_dip.stm_id = stm_dip.id
AND stm_dip.say_code = 'CURR'
AND stm_dip.active_yn = 'Y'
)
GROUP BY receipt_date,request_id
ORDER BY receipt_date;
SELECT receipt_date,
SUM(principal) principal,
SUM(interest) interest
FROM
(
SELECT sel_dii.stream_element_date receipt_date,
0 principal,
sel_dii.amount interest
FROM okl_strm_elements sel_dii,
okl_streams stm_dii,
okl_strm_type_b sty_dii
WHERE stm_dii.khr_id = p_chr_id
AND sty_dii.id = stm_dii.sty_id
AND sty_dii.stream_type_purpose = 'DAILY_INTEREST_INTEREST'
AND sel_dii.stm_id = stm_dii.id
AND stm_dii.say_code = 'CURR'
AND stm_dii.active_yn = 'Y'
UNION ALL
SELECT sel_dip.stream_element_date receipt_date,
sel_dip.amount principal,
0 interest
FROM okl_strm_elements sel_dip,
okl_streams stm_dip,
okl_strm_type_b sty_dip
WHERE stm_dip.khr_id = p_chr_id
AND sty_dip.id = stm_dip.sty_id
AND sty_dip.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
AND sel_dip.stm_id = stm_dip.id
AND stm_dip.say_code = 'CURR'
AND stm_dip.active_yn = 'Y'
)
GROUP BY receipt_date
ORDER BY receipt_date;
SELECT MAX(sel.stream_element_date)
FROM okl_streams stm,
okl_strm_elements sel,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL;
SELECT chr.start_date
FROM okc_k_headers_b chr
WHERE chr.id = p_chr_id;
SELECT bill_date,
SUM(principal) principal,
SUM(interest) interest,
0 princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest,
G_BILLED payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'VARIABLE_INTEREST'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL
UNION ALL
SELECT sel.stream_element_date bill_date,
sel.amount principal,
0 interest,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
UNION ALL
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest,
G_PROJECTED payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NULL
AND sel.stream_element_date > p_last_bill_date
)
GROUP BY bill_date,payment_type
UNION ALL
SELECT bill_date,
0 principal,
0 interest,
SUM(princ_pay_down) princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
0 interest,
sel.amount princ_pay_down,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date,payment_type
ORDER BY bill_date;
SELECT MAX(sel.stream_element_date)
FROM okl_streams stm,
okl_strm_elements sel,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL;
SELECT chr.start_date
FROM okc_k_headers_b chr
WHERE chr.id = p_chr_id;
SELECT bill_date,
SUM(principal) principal,
SUM(interest) interest
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'VARIABLE_INTEREST'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL
UNION ALL
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NULL
AND sel.stream_element_date > p_last_bill_date
UNION ALL
SELECT sel.stream_element_date bill_date,
sel.amount principal,
0 interest
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT','UNSCHEDULED_PRINCIPAL_PAYMENT')
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date
ORDER BY bill_date;
SELECT bill_date,
SUM(principal) principal,
SUM(interest) interest,
0 princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
UNION ALL
SELECT sel.stream_element_date bill_date,
sel.amount principal,
0 interest,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date,payment_type
UNION ALL
SELECT sel.stream_element_date bill_date,
SUM(sel.amount) principal,
0 interest,
0 princ_pay_down,
G_BILLED payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL
GROUP BY sel.stream_element_date
UNION ALL
SELECT sel.stream_element_date bill_date,
0 principal,
SUM(sel.amount) interest,
0 princ_pay_down,
G_BILLED payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'INTEREST_CATCHUP'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sel.date_billed IS NOT NULL
GROUP BY sel.stream_element_date
UNION ALL
SELECT bill_date,
0 principal,
0 interest,
SUM(princ_pay_down) princ_pay_down,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
0 interest,
sel.amount princ_pay_down,
decode(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date,payment_type
ORDER BY bill_date;
SELECT bill_date,
SUM(principal) principal,
SUM(interest) interest,
payment_type
FROM
(
SELECT sel.stream_element_date bill_date,
0 principal,
sel.amount interest,
DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose IN ('INTEREST_PAYMENT','INTEREST_CATCHUP')
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
UNION ALL
SELECT sel.stream_element_date bill_date,
sel.amount principal,
0 interest,
DECODE(sel.date_billed,NULL,G_PROJECTED,G_BILLED) payment_type
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_chr_id
AND sty.id = stm.sty_id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT','PRINCIPAL_CATCHUP','UNSCHEDULED_PRINCIPAL_PAYMENT')
AND sel.stm_id = stm.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
)
GROUP BY bill_date,payment_type
ORDER BY bill_date;
SELECT
rgpb.cle_id kle_id,
rulb2.rule_information2 start_date,
rulb2.rule_information3 level_periods,
rulb2.rule_information7 stub_days,
rulb2.rule_information8 stub_amount,
rulb2.rule_information10 arrear_yn
FROM okc_k_lines_b cleb,
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb,
okc_statuses_b sts
WHERE rgpb.chr_id IS NULL
AND rgpb.dnz_chr_id = cleb.dnz_chr_id
AND rgpb.cle_id = cleb.id
AND cleb.dnz_chr_id = p_chr_id
AND sts.code = cleb.sts_code
AND sts.ste_code <> 'CANCELLED'
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','PRINCIPAL_PAYMENT')
ORDER BY kle_id, start_date, level_periods;
SELECT ppm_rrm.quality_val rev_rec_method,
ppm_icb.quality_val int_calc_basis,
khr.implicit_interest_rate,
chrb.start_date
FROM okl_k_headers khr,
okl_prod_qlty_val_uv ppm_rrm,
okl_prod_qlty_val_uv ppm_icb,
okc_k_headers_b chrb
WHERE chrb.id = p_chr_id
AND khr.id = chrb.id
AND ppm_rrm.pdt_id = khr.pdt_id
AND ppm_rrm.quality_name = 'REVENUE_RECOGNITION_METHOD'
AND ppm_icb.pdt_id = khr.pdt_id
AND ppm_icb.quality_name = 'INTEREST_CALCULATION_BASIS';