[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'T' flag
from dual where exists
(
select 1
from okc_k_lines_b kle,
okc_line_styles_b lse,
okc_statuses_b sts
where KLE.LSE_ID = LSE.ID
and lse.lty_code = 'SOLD_SERVICE'
and kle.chr_id = chrId
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
);
select chr.orig_system_source_code,
chr.start_date,
chr.end_date,
chr.template_yn,
chr.authoring_org_id,
chr.inv_organization_id,
khr.deal_type,
to_char(pdt.id) pid,
nvl(pdt.reporting_pdt_id, -1) report_pdt_id,
chr.currency_code currency_code,
khr.term_duration term
from okc_k_headers_v chr,
okl_k_headers khr,
okl_products_v pdt
where khr.id = chr.id
and chr.id = p_khr_id
and khr.pdt_id = pdt.id(+);
Select distinct nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
Where slh.rgp_id = rgp.id
and rgp.RGD_CODE = 'LALEVL'
and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
and slh.object1_id1 = sty.id
and sty.stream_type_purpose = 'RENT'
and rgp.dnz_chr_id = p_khr_id;
SELECT sel.id
FROM okl_streams stm,
okl_strm_elements sel
where stm.khr_id = p_khr_id
and stm.say_code = 'HIST'
and stm.SGN_CODE = 'MANL'
and stm.active_yn = 'N'
and stm.purpose_code = 'STUBS'
and stm.comments = 'STUB STREAMS'
and sel.stm_id = stm.id;
SELECT DISTINCT
cle.id kleId,
kle.amount fee_amount,
cle.start_date fee_start_date,
stm.id styId,
sll.object1_id1 frequency,
TO_NUMBER(sll.rule_information3) periods,
FND_DATE.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information5 structure,
sll.rule_information10 advance_arrears,
FND_NUMBER.canonical_to_number(sll.rule_information6) amount,
TO_NUMBER(sll.rule_information7) stub_days,
TO_NUMBER(sll.rule_information8) stub_amount
FROM okc_k_headers_b chr_so,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b stm
WHERE chr_so.id = p_khr_id
and cle.sts_code in( 'COMPLETE', 'INCOMPLETE')--'ENTERED'
AND cle.dnz_chr_id = chr_so.id
AND cle.id = kle.id
AND kle.id = feeId
AND trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND ((lse.lty_code = 'FEE' AND kle.fee_type = 'ROLLOVER') OR lse.lty_code = 'LINK_FEE_ASSET')
AND kle.fee_type <> 'CAPITALIZED'
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(stm.id)
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
order by cle.id,stm.id, FND_DATE.canonical_to_date(sll.rule_information2);
SELECT DISTINCT
to_char(cle.id) kleId,
kle.amount fee_amount,
cle.start_date fee_start_date
FROM okc_k_headers_b chr_so,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE chr_so.id = p_khr_id
and cle.sts_code in( 'COMPLETE', 'INCOMPLETE')--'ENTERED'
AND cle.dnz_chr_id = chr_so.id
AND cle.id = kle.id
AND trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND ((lse.lty_code = 'FEE' AND kle.fee_type = 'ROLLOVER') OR lse.lty_code = 'LINK_FEE_ASSET')
AND kle.fee_type <> 'CAPITALIZED';
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
update okl_streams
set say_code = 'HIST',
date_history = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
where khr_id = p_khr_id
and kle_id = r_rollover_fee.kleId;
update okl_streams
set active_yn = 'N',
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
where khr_id = p_khr_id
and kle_id = r_rollover_fee.kleId;
l_tmp_selv_tbl.DELETE;
l_selv_tbl.DELETE;
l_principal_tbl.DELETE;
l_interest_tbl.DELETE;
l_prin_bal_tbl.DELETE;
l_termination_tbl.DELETE;
l_pre_tax_inc_tbl.DELETE;
l_principal_tbl.delete;
l_interest_tbl.delete;
l_prin_bal_tbl.delete;
l_termination_tbl.delete;
l_pre_tax_inc_tbl.delete;
SELECT sel.amount income_amount,
sel.stream_element_date income_date
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.id = sel.stm_id
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND styt.language = 'US'
AND styt.name = p_sty_name
AND sel.stream_element_date >= p_start_date
AND sel.stream_element_date <= LAST_DAY(p_end_date) --bug# 3379436
ORDER BY sel.stream_element_date;
Select distinct nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
Where slh.rgp_id = rgp.id
and rgp.RGD_CODE = 'LALEVL'
and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
and slh.object1_id1 = sty.id
and sty.stream_type_purpose = 'RENT'
and rgp.dnz_chr_id = p_khr_id;
Select rul.*
from okc_rules_b rul,
okc_rule_groups_b rgp
where rul.rgp_id = rgp.id
and rul.rule_information_category = p_rule_code
and rul.dnz_chr_id = rgp.dnz_chr_id
and rgp.chr_id = p_chr_id
and rgp.rgd_code = p_rgd_code
and rgp.dnz_chr_id = p_chr_id;
SELECT styt.name
FROM okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE sty.id = p_sty_id
AND sty.version = '1.0' -- not really needed in 1159
AND sty.id = styt.id
AND styt.language = 'US'
AND sty.start_date <= TRUNC(SYSDATE)
AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);
SELECT sty.id
FROM okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE styt.name = p_sty_name
AND styt.language = 'US'
AND styt.id = sty.id
AND sty.version = '1.0' -- not really needed in 1159
AND sty.start_date <= TRUNC(SYSDATE)
AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);
select chr.orig_system_source_code,
chr.start_date,
chr.end_date,
chr.template_yn,
chr.authoring_org_id,
chr.inv_organization_id,
khr.deal_type,
to_char(pdt.id) pid, --358660899972842057983133434721270318297
nvl(pdt.reporting_pdt_id, -1) report_pdt_id,
chr.currency_code currency_code,
khr.term_duration term
from okc_k_headers_v chr,
okl_k_headers khr,
okl_products_v pdt
where khr.id = chr.id
and chr.id = p_khr_id
and khr.pdt_id = pdt.id(+);
Select distinct nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
Where slh.rgp_id = rgp.id
and rgp.RGD_CODE = 'LALEVL'
and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
and slh.object1_id1 = sty.id
and sty.stream_type_purpose = 'RENT'
and rgp.dnz_chr_id = p_khr_id;
SELECT okl_sif_seq.nextval INTO l_transaction_number FROM DUAL;
l_stmv_rec.say_code := 'WORK'; -- calling API will update to CURR as required
l_stmv_rec.active_yn := 'N'; -- calling API will update to Y as required
SELECT NVL(TO_NUMBER(rul.rule_information1), 100) pass_through_percentage
FROM okc_rule_groups_b rgp,
okc_rules_b rul
WHERE rgp.cle_id = kleid
AND rgp.rgd_code = 'LAPSTH'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPTPR';
SELECT COUNT(stm.id),
stm.sty_id
FROM okl_streams stm
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND NVL(stm.purpose_code, '-99') = p_purpose_code
AND stm.kle_id IS NULL
HAVING COUNT(stm.id) > 1
GROUP BY stm.kle_id, stm.sty_id;
SELECT id
FROM okl_streams
WHERE khr_id = p_khr_id
AND kle_id IS NULL
AND say_code = 'WORK'
AND sty_id = p_sty_id
AND NVL(purpose_code, '-99') = p_purpose_code;
SELECT id
FROM okl_streams
WHERE khr_id = p_khr_id
AND kle_id IS NULL
AND sty_id = p_sty_id
AND say_code = 'WORK'
AND NVL(purpose_code, '-99') = p_purpose_code
AND id <> p_stm_id;
SELECT se_line_number
FROM okl_strm_elements
WHERE stm_id = p_stm_id
ORDER BY se_line_number DESC;
SELECT id
FROM okl_strm_elements
WHERE stm_id = p_stm_id
ORDER BY stream_element_date;
DELETE FROM okl_streams WHERE id = l_stm.id
and khr_id = p_khr_id;
UPDATE okl_strm_elements
SET stm_id = l_stm_first,
se_line_number = (l_line_num + n)
WHERE id = l_sel.id;
DELETE FROM OKL_STREAMS WHERE purpose_code = 'STUBS'
and khr_id = p_khr_id;
SELECT COUNT(stm.id),
stm.kle_id,
stm.sty_id
FROM okl_streams stm,
okc_k_lines_b cle,
okc_line_styles_b lse
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND NVL(stm.purpose_code, '-99') = p_purpose_code
AND stm.kle_id IS NOT NULL
AND stm.kle_id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code IN ('FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET', 'FREE_FORM1', 'LINK_FEE_ASSET')
HAVING COUNT(stm.id) > 1
GROUP BY stm.kle_id, stm.sty_id;
SELECT id
FROM okl_streams
WHERE khr_id = p_khr_id
AND kle_id = p_kle_id
AND sty_id = p_sty_id
AND say_code = 'WORK'
AND NVL(purpose_code, '-99') = p_purpose_code;
SELECT id
FROM okl_streams
WHERE khr_id = p_khr_id
AND kle_id = p_kle_id
AND sty_id = p_sty_id
AND say_code = 'WORK'
AND NVL(purpose_code, '-99') = p_purpose_code
AND id <> p_stm_id;
SELECT se_line_number
FROM okl_strm_elements
WHERE stm_id = p_stm_id
ORDER BY se_line_number DESC;
SELECT id
FROM okl_strm_elements
WHERE stm_id = p_stm_id
ORDER BY stream_element_date;
DELETE FROM okl_streams WHERE id = l_stm.id
and khr_id = p_khr_id;
UPDATE okl_strm_elements
SET stm_id = l_stm_first,
se_line_number = (l_line_num + n)
WHERE id = l_sel.id;
DELETE FROM OKL_STREAMS WHERE purpose_code = 'STUBS'
and khr_id = p_khr_id;
SELECT ele.stream_element_date,
ele.id element_id,
stm.id stream_id,
stm.kle_id kle_id,
stm.sty_id,
sty.stream_type_purpose stm_purpose,
ele.amount amount
FROM okl_streams stm,
okl_strm_elements ele,
okl_strm_type_b sty
WHERE stm.khr_id = p_khr_id
AND ele.stm_id = stm.id
AND stm.say_code = 'WORK'
AND NVL(stm.purpose_code, '-99') = p_purpose_code
AND sty.id = stm.sty_id
AND sty.stream_type_purpose in ( 'RENT_ACCRUAL','PASS_THRU_PRO_FEE_ACCRUAL',
'PASS_THRU_REV_ACCRUAL', 'SERVICE_INCOME',
'AMORTIZE_FEE_INCOME', 'ACCRUED_FEE_INCOME', 'LEASE_INCOME','INTEREST_INCOME',
'PASS_THRU_EXP_ACCRUAL', 'ACCOUNTING', 'AMORTIZED_FEE_EXPENSE','SUBSIDY_INCOME',
'PASS_THRU_SVC_EXP_ACCRUAL','PASS_THRU_SVC_REV_ACCRUAL')
--Added by dpsingh for consolidation to happen only for ISG streams 5949810
AND (stm.sgn_code <> 'STMP' and stm.sgn_code <> 'STMP-REBK')
--dpsingh 5949810 ends
ORDER BY stm.sty_id,stm.kle_id,ele.stream_element_date;
d := 1; -- Index for PL/SQL table for storing the streams which have to be deleted.
DELETE okl_strm_elements
WHERE id = l_ele_id_del_tbl(indx) AND
stm_id = l_streamid_del_tbl(indx);
UPDATE okl_strm_elements
SET stream_element_date = l_sel_date_mod_tbl(indx),
amount = l_amt_mod_tbl(indx)
WHERE id = l_ele_id_mod_tbl(indx) AND
stm_id = l_streamid_mod_tbl(indx);
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
chr.end_date,
khr.deal_type,
khr.term_duration,
khr.pre_tax_irr,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
select kle.id,
kle.oec,
kle.residual_code,
kle.capital_amount,
kle.delivered_date,
kle.date_funding_required,
kle.residual_grnty_amount,
kle.date_funding,
kle.residual_value,
kle.amount,
kle.price_negotiated,
kle.start_date,
kle.end_date,
kle.orig_system_id1,
kle.fee_type,
kle.initial_direct_cost,
kle.capital_reduction,
kle.capital_reduction_percent,
NVL(kle.capitalize_down_payment_yn, 'N') capitalize_down_payment_yn
/* ,tl.item_description,
tl.name */
--Bug# 9725362
,sts.ste_code
from okl_k_lines_full_v kle,
okc_line_styles_b lse,
-- okc_k_lines_tl tl,
okc_statuses_b sts
where KLE.LSE_ID = LSE.ID
and lse.lty_code = lnetype
-- and tl.id = kle.id
-- and tl.language = userenv('LANG')
and kle.dnz_chr_id = chrid
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
Select nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
Where slh.rgp_id = rgp.id
and rgp.RGD_CODE = 'LALEVL'
and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
and rgp.dnz_chr_id = chrId
and rgp.cle_id = kleId
and sty.id = to_number(slh.object1_id1)
and sty.stream_type_purpose NOT IN ('ESTIMATED_PROPERTY_TAX', 'UNSCHEDULED_PRINCIPAL_PAYMENT', 'DOWN_PAYMENT');
SELECT rul2.object1_id1 frequency,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_strm_type_b sty
WHERE rul1.rgp_id= rgp.id
ANd rgp.rgd_code = 'LALEVL'
AND rul2.rule_information_category = 'LASLL'
and rul1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rul2.rgp_id = rgp.id
AND rgp.dnz_chr_id = khrid
AND rgp.cle_id = kleid
AND cle.id = rgp.cle_id
AND cle.sts_code IN ('PASSED', 'COMPLETE')
AND cle.id = kle.id
AND cle.lse_id = lse.id
and sty.id = to_number(rul1.object1_id1)
and sty.stream_type_purpose NOT IN ('ESTIMATED_PROPERTY_TAX', 'UNSCHEDULED_PRINCIPAL_PAYMENT' );
Select to_number(Value)
from okl_sgn_translations
where jtot_object1_code = 'OKL_STRMTYP'
and object1_id1 = to_char( styId );
Select name
from okl_strm_type_tl
where language = 'US'
and id = styId;
SELECT DISTINCT
sel.id id,
sel.amount cf_amount,
sel.stream_element_date cf_date,
sel.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
sll.object1_id1 cf_frequency,
DECODE(sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) days_per_period,
DECODE(sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE stm.khr_id = khrId
AND stm.kle_id = kleId
AND stm.say_code = 'WORK'
AND nvl(stm.purpose_code, '-99') = pCode
AND stm.sty_id = sty.id
AND stm.id = sel.stm_id
AND sty.stream_type_purpose IN ( 'RENT', 'LOAN_PAYMENT')
AND sel.comments IS NOT NULL
AND stm.kle_id = cle.id
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp2
WHERE rgp2.dnz_chr_id = khrId
AND rgp2.cle_id = cle.id
AND rgp2.rgd_code = 'LAPSTH')
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(stm.sty_id)
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND cle.id = kleid;
l_selv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
l_selv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
l_selv_tbl.DELETE;
l_tmp_selv_tbl.DELETE;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
l_asbv_tbl.delete;
l_selv_tbl.delete;
r_inflow_line_tbl.delete;
l_selv_tbl.DELETE;
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
l_selv_tbl.DELETE;
lx_selv_tbl.DELETE;
l_pt_tbl.DELETE;
l_selv_tbl.delete;
l_pt_tbl.delete;
lx_selv_tbl.delete;
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
(SELECT rgp.cle_id cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
lse.lty_code lty_code,
kle.capital_amount capital_amount,
kle.residual_value residual_value,
kle.fee_type fee_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul1.rule_information_category = 'LASLH'
AND rul1.jtot_object1_code = 'OKL_STRMTYP'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.id = kle.id
AND cle.lse_id = lse.id)
UNION
(SELECT TO_NUMBER(NULL) cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
NULL lty_code,
TO_NUMBER(NULL) capital_amount,
TO_NUMBER(NULL) residual_value,
NULL fee_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul1.rule_information_category = 'LASLH'
AND rul1.jtot_object1_code = 'OKL_STRMTYP'
AND rul2.rgp_id = rgp.id
AND rgp.cle_id IS NULL
AND TO_NUMBER(rul2.object2_id1) = rul1.id)
ORDER BY cle_id, sty_id, start_date;
SELECT kle.id,
NVL(kle.residual_value, 0) residual_value,
cle.start_date
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = kle.id;
SELECT 'Y'
FROM okc_rule_groups_b
WHERE cle_id = p_cle_id
AND rgd_code = 'LAPSTH';
select tl.name strm_name,
sty.capitalize_yn capitalize_yn,
kle.id line_id,
sty.id styp_id,
sty.stream_type_class stream_type_class
from okl_strm_type_tl tl,
okl_strm_type_v sty,
okc_k_items cim,
okl_k_lines_full_v kle,
okc_line_styles_b ls
where tl.id = sty.id
and tl.language = 'US'
and cim.cle_id = kle.id
and ls.id = kle.lse_id
and ls.lty_code = 'FEE'
and cim.object1_id1 = sty.id
and cim.object1_id2 = '#'
and kle.id = kleid;
l_selv_tbl.DELETE;
l_pt_tbl.DELETE;
l_pt_pro_fee_tbl.DELETE;
l_selv_tbl.delete;
l_pt_tbl.delete;
l_pt_pro_fee_tbl.delete;
lx_selv_tbl.delete;
SELECT cle.id cle_id,
cle.start_date start_date,
NVL(cle.date_terminated, cle.end_date) end_date
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE')
-- Removing 'TERMINATED' Status Lines as Partial Terminated Asset Accruals
-- will be copied over any way.
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1';
SELECT TO_NUMBER(rul1.object1_id1) sty_id,
TO_NUMBER(rul2.rule_information3) periods,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.id = p_cle_id
-- 12944979: For Terminated lines, copy_accrual_streams_isg will syncup
AND cle.sts_code IN ('PASSED', 'COMPLETE')
AND sty.id = rul1.object1_id1
AND sty.stream_type_purpose = 'RENT';
l_selv_tbl.delete;
l_selv_tbl.DELETE;
SELECT 1 order_sequence
, lse.lty_code line_type -- 'FREE_FORM1'
, cle.id kle_id
, NULL fee_id
, cle.date_terminated date_terminated
FROM okc_k_lines_b cle
, okc_line_styles_b lse
, okc_statuses_b sts
WHERE cle.dnz_chr_id = cp_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FREE_FORM1'
AND sts.code = cle.sts_code
AND sts.ste_code = 'TERMINATED'
UNION
-- Query the Link Fee Asset Lines that got terminated
SELECT 2 order_sequence
, lnk_lse.lty_code line_type -- 'LINK_FEE_ASSET'
, lnk_cle.id kle_id
, fee_cle.id fee_id
, lnk_cle.date_terminated date_terminated
FROM okc_k_lines_b lnk_cle
, okl_k_lines fee_kle
, okc_k_lines_b fee_cle
, okc_line_styles_b lnk_lse
, okc_statuses_b sts
WHERE lnk_cle.dnz_chr_id = cp_chr_id
AND fee_cle.dnz_chr_id = cp_chr_id
AND lnk_cle.cle_id = fee_cle.id
AND lnk_lse.id = lnk_cle.lse_id
AND lnk_lse.lty_code = 'LINK_FEE_ASSET'
AND sts.code = lnk_cle.sts_code
AND sts.ste_code = 'TERMINATED'
AND fee_kle.id = FEE_CLE.ID
UNION
SELECT DISTINCT
3 order_sequence
, lse1.lty_code line_type
, cle1.id kle_id
, NULL fee_id
, cle1.date_terminated date_terminated
FROM okl_streams stm
, okc_k_lines_b cle1
, okc_line_styles_b lse1
, okc_statuses_b sts1
WHERE stm.khr_id = cp_chr_id
AND stm.say_code = 'CURR'
AND stm.kle_id = cle1.id
AND lse1.id = cle1.lse_id
AND lse1.lty_code NOT IN ( 'FREE_FORM1' , 'LINK_FEE_ASSET' )
AND sts1.code = cle1.sts_code
AND sts1.ste_code = 'TERMINATED'
ORDER BY order_sequence, line_type, kle_id;
SELECT STM.ID STREAM_ID
, STM.STY_ID
, STM.SGN_CODE
, STM.PURPOSE_CODE
, STM.ACTIVE_YN
, SEL.STREAM_ELEMENT_DATE
, SEL.AMOUNT
, SEL.SE_LINE_NUMBER
, SEL.COMMENTS
, SEL.ACCRUED_YN
, STM.TRANSACTION_NUMBER
, STY.STREAM_TYPE_PURPOSE
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_B STY
WHERE SEL.STM_ID = STM.ID
AND STM.KHR_ID = cp_chr_id
AND STM.KLE_ID = cp_cle_id
AND DECODE(STM.PURPOSE_CODE, NULL, '-99', 'REPORT' ) = cp_purpose_code
AND STM.SAY_CODE = 'CURR'
AND SEL.STREAM_ELEMENT_DATE <= nvl(cp_date_terminated, SEL.STREAM_ELEMENT_DATE)
AND STM.SGN_CODE <> 'INTC'
AND STM.STY_ID = STY.ID
AND STY.stream_type_purpose IN
-- List of the Accrual Streams identified
(
'ACCRUED_FEE_EXPENSE'
,'ACCRUED_FEE_INCOME'
,'ACTUAL_INCOME_ACCRUAL'
,'EXPENSE'
,'INTEREST_INCOME'
,'LEASE_INCOME'
,'PASS_THRU_EXP_ACCRUAL'
,'PASS_THRU_REV_ACCRUAL'
,'PASS_THRU_SVC_EXP_ACCRUAL'
,'PASS_THRU_SVC_REV_ACCRUAL'
,'PROCESSING_FEE_ACCRUAL'
,'RENT_ACCRUAL'
,'SERVICE_EXPENSE'
,'SERVICE_INCOME'
-- Apart from copying accruals, we are copying the following stream with the purpose
-- as we have modified the generate_streams api code, not to call get_loan_amortization
-- Hence, we will be loosing out all these streams
,'INTEREST_PAYMENT'
,'PRINCIPAL_PAYMENT'
,'PRINCIPAL_BALANCE'
,'TERMINATION_VALUE'
)
AND STM.SGN_CODE NOT IN ('INTC','LATE_CALC')
ORDER BY STM.ID, SEL.SE_LINE_NUMBER -- to get the stream elements of a stream together
;
SELECT '!'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_trx_contracts trx
WHERE CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY'
AND ktrx.source_trx_type = 'TCN'
AND trx.tcn_type = 'ALT'
AND trx.id = ktrx.source_trx_id
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED');
SELECT cle.id new_cle_id
FROM okc_k_lines_b cle
,okc_k_lines_b ole
WHERE cle.dnz_chr_id=cp_rbk_chr_id
and ole.id = cp_ole_id
and ole.lse_id = cle.lse_id
and ole.id=cle.orig_system_id1;
SELECT 'Y'
FROM OKL_STRM_TYPE_B sty,
OKL_PROD_STRM_TYPES psty
WHERE sty.id = psty.sty_id
AND psty.pdt_id = cp_pdt_id
AND psty.accrual_yn = 'Y'
AND psty.sty_id=cp_sty_id;
SELECT pdt_id
FROM okl_k_headers
WHERE id = p_chr_id;
SELECT pdt.reporting_pdt_id
FROM okl_k_headers khr,
okl_products pdt
WHERE khr.id = p_chr_id
AND khr.pdt_id = pdt.id;
SELECT stm.id stm_id
,stm.transaction_number transaction_number
FROM okl_streams stm
WHERE stm.khr_id = cp_chr_id
AND stm.kle_id = cp_kle_id
AND stm.sty_id = cp_sty_id
AND stm.sgn_code = cp_sgn_code
AND NVL(stm.purpose_code,-99) = NVL(cp_purpose_code,-99)
AND stm.say_code = cp_say_code;
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT 'Y'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_trx_contracts trx,
okl_txl_quote_lines_b txl
WHERE CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY'
AND ktrx.source_trx_type = 'TCN'
AND trx.tcn_type = 'ALT'
AND trx.id = ktrx.source_trx_id
AND TXL.QTE_ID = TRX.QTE_ID
AND TXL.qlt_code = 'AMCFIA'
AND TXL.kle_id = p_kle_id
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED')
AND TXL.QTE_ID = TRX.QTE_ID
UNION
SELECT 'Y'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_trx_contracts trx,
okl_txl_quote_lines_b txl
, OKC_K_ITEMS LNK_ITM
WHERE CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.source_trx_type = 'TCN'
AND trx.tcn_type = 'ALT'
AND trx.id = ktrx.source_trx_id
AND TXL.QTE_ID = TRX.QTE_ID
AND TXL.qlt_code = 'AMCFIA'
AND TXL.kle_id = LNK_ITM.OBJECT1_ID1
AND LNK_ITM.CLE_ID = p_kle_id
AND LNK_ITM.DNZ_CHR_ID = p_chr_id
AND LNK_ITM.JTOT_OBJECT1_CODE = 'OKX_COVASST'
AND LNK_ITM.OBJECT1_ID2 = '#'
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED')
AND TXL.QTE_ID = TRX.QTE_ID;
SELECT okl_sif_seq.nextval
INTO l_wrk_strm_trx_number
FROM DUAL;
print(l_prog_name, 'Before calling APIs to Delete the Identified Streams l_del_stm_id_tbl.COUNT=' || l_del_stm_id_tbl.COUNT );
print(l_prog_name, 'Before calling Delete okl_strm_elements statement : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
DELETE okl_strm_elements sel
WHERE sel.stm_id = l_del_stm_id_tbl(i);
print(l_prog_name, 'After calling Delete okl_strm_elements statement : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
print(l_prog_name, 'Before calling Delete okl_streams statement : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
DELETE okl_streams stm
WHERE stm.id = l_del_stm_id_tbl(i);
print(l_prog_name, 'After calling Delete okl_streams statement : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
print(l_prog_name, 'Before calling APIs to insert the copied over streams. l_stmv_tbl.COUNT =' || l_stmv_tbl.COUNT );
SELECT stm.id stm_id
,stm.kle_id kle_id
,stm.sty_id sty_id
,sty.stream_type_purpose stm_purpose
,stm.say_code say_code
,stm.purpose_code purpose_code
,sty.stream_type_purpose sty_purpose
,sty.code sty_code
,DECODE(
sty.stream_type_purpose,
'RENT_ACCRUAL', 1,
99 ) sty_priority
FROM okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_khr_id
AND stm.say_code = p_say_code
AND NVL(stm.purpose_code, '-99') = p_purpose
AND sty.id = stm.sty_id
AND sty.stream_type_purpose IN (
-- Accrual Streams generated using Straight Line Algorithm
( SELECT 'RENT_ACCRUAL' FROM DUAL WHERE p_context IN ( 'FULL' ) )
,( SELECT 'ACCRUED_FEE_INCOME' FROM DUAL WHERE p_context IN ( 'FULL' ) )
,( SELECT 'ACCRUED_FEE_EXPENSE' FROM DUAL WHERE p_context IN ( 'FULL' ) )
--sechawla 10-aug-09 Added 'SERVICE_LINES' context for Service Income and Service Expense
--streams to support Prospective Rebooking for these streams
,( SELECT 'SERVICE_INCOME' FROM DUAL WHERE p_context IN ( 'FULL','SERVICE_LINES' ) )
,( SELECT 'SERVICE_EXPENSE' FROM DUAL WHERE p_context IN ( 'FULL', 'SERVICE_LINES') )
,( SELECT 'PASS_THRU_REV_ACCRUAL' FROM DUAL WHERE p_context IN ('FULL', 'PASSTHRU_ONLY' ) )
,( SELECT 'PASS_THRU_EXP_ACCRUAL' FROM DUAL WHERE p_context IN ('FULL', 'PASSTHRU_ONLY' ) )
-- 15-Sep-10 SECHAWLA 10045043 : begin
,( SELECT 'PASS_THRU_SVC_EXP_ACCRUAL' FROM DUAL WHERE p_context IN ('FULL', 'PASSTHRU_ONLY' ) )
-- 15-Sep-10 SECHAWLA 10045043 : end
-- Accrual Streams generated using Spread By Income Logic
,( SELECT 'AMORTIZE_FEE_INCOME' FROM DUAL WHERE p_context IN ('FULL' ) )
,( SELECT 'AMORTIZED_FEE_EXPENSE' FROM DUAL WHERE p_context IN ('FULL') )
,( SELECT 'SUBSIDY_INCOME' FROM DUAL WHERE p_context IN ('FULL') )
)
AND stm.sgn_code NOT IN ( 'STMP' , 'STMP-REBK')
ORDER BY DECODE( sty.stream_type_purpose, 'RENT_ACCRUAL', 1, 99 ) ASC;
SELECT sel.id sel_id,
sel.amount se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears,
sel.sel_id se_sel_id,
stm.id stm_id,
stm.khr_id khr_id,
stm.kle_id kle_id,
stm.sty_id sty_id,
stm.say_code say_code,
stm.purpose_code purpose_code
FROM okl_strm_elements sel,
okl_streams stm
WHERE stm.kle_id = p_kle_id
AND stm.khr_id = p_khr_id
AND stm.sty_id = p_sty_id
AND DECODE(stm.purpose_code,
NULL, '-99',
'REPORT'
) = p_purpose_code
AND stm.say_code = p_say_code
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date;
SELECT cle.orig_system_id1 orig_kle_id
,chr.orig_system_id1 orig_khr_id
FROM okc_k_lines_b cle
,okc_k_headers_b chr
WHERE cle.id = p_kle_id
AND cle.dnz_chr_id = chr.id
AND cle.orig_system_id1 IS NOT NULL;
SELECT TO_NUMBER(slh.object1_id1) sty_id,
sty.stream_type_purpose sty_purpose,
FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(sll.rule_information3) periods,
sll.object1_id1 frequency,
sll.rule_information5 structure,
DECODE(sll.rule_information10,
'Y', 'ARREARS',
'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(sll.rule_information6) amount,
TO_NUMBER(sll.rule_information7) stub_days,
TO_NUMBER(sll.rule_information8) stub_amount,
DECODE( sll.rule_information7
-- Case: When Payment Level is Regular Periods
,NULL
,( ADD_MONTHS
( FND_DATE.canonical_to_date(sll.rule_information2),
NVL(TO_NUMBER(sll.rule_information3),1)
* DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12)
) - 1
)
-- Case: When Payment Level is Regular Periods
,FND_DATE.canonical_to_date(sll.rule_information2)
+ TO_NUMBER(sll.rule_information7) - 1
) end_date
FROM okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b sty
WHERE sll.dnz_chr_id = p_khr_id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id
AND TO_NUMBER(sll.object2_id1) = slh.id
AND rgp.cle_id = p_kle_id
AND sty.id = slh.object1_id1
AND sty.stream_type_purpose NOT IN
( 'DOWN_PAYMENT'
,'ESTIMATED_PROPERTY_TAX'
,'UNSCHEDULED_PRINCIPAL_PAYMENT'
)
ORDER BY sty.id, FND_DATE.canonical_to_date(sll.rule_information2);
SELECT TO_NUMBER(rul.rule_information1) periods,
TO_NUMBER(rul.rule_information2) amount,
DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12, NULL) mpp,
cle.start_date,
cle.sts_code,
kle.fee_type,
( ADD_MONTHS(cle.start_date,
TO_NUMBER(rul.rule_information1) -- Periods
* DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12, NULL)
) - 1 ) end_date
FROM okc_rules_b rul,
okc_rules_b rul2,
okc_rule_groups_b rgp,
okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.id = p_kle_id
AND cle.sts_code IN ('NEW', 'INCOMPLETE', 'PASSED', 'COMPLETE')
AND kle.id = cle.id
AND kle.fee_type NOT IN
(
'FINANCED'
,'ABSORBED'
,'ROLLOVER'
)
AND rgp.cle_id = cle.id
AND rgp.dnz_chr_id = p_khr_id
AND rgp.rgd_code = 'LAFEXP'
AND rgp.id = rul.rgp_id
AND rgp.id = rul2.rgp_id
AND rul.rule_information_category = 'LAFEXP'
AND rul2.rule_information_category = 'LAFREQ';
SELECT TRUNC(sel.stream_element_date) income_date
,SUM(sel.amount) income_amount
FROM okl_strm_elements sel
,okl_streams stm
,okl_strm_type_b sty
,okl_strm_type_tl styt
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.id = sel.stm_id
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND styt.language = 'US'
AND styt.name = p_sty_name
AND sel.stream_element_date >= p_start_date
AND sel.stream_element_date <= LAST_DAY(p_end_date)
AND DECODE(stm.purpose_code, NULL, '-99', 'REPORT') = p_purpose_code
GROUP BY TRUNC(sel.stream_element_date)
ORDER BY TRUNC(sel.stream_element_date) ASC;
SELECT DISTINCT NVL(slh.object1_id1, -1) styid
FROM OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
WHERE slh.rgp_id = rgp.id
AND rgp.RGD_CODE = 'LALEVL'
AND sll.RULE_INFORMATION_CATEGORY = 'LASLL'
AND slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND slh.object1_id1 = sty.id
AND sty.stream_type_purpose = 'RENT'
AND rgp.dnz_chr_id = p_khr_id;
l_accrual_streams_tbl.DELETE;
l_work_acc_strms_tbl.DELETE;
l_curr_acc_strms_tbl.DELETE;
l_selv_tbl.DELETE;
lx_selv_tbl.DELETE;
print(l_prog_name, 'Total Number of Rental Accrual Stream Elements to be updated ' || l_sel_id_tbl.COUNT );
print(l_prog_name, 'Start: Performaing the Bulk Update of ' || l_sel_id_tbl.COUNT || ' Streams. '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
UPDATE okl_strm_elements
SET amount = l_sel_amt_tbl(i)
WHERE id = l_sel_id_tbl(i)
AND stm_id = l_stm_id_tbl(i);
print(l_prog_name, 'Done: Performaing the Bulk Update of ' || l_sel_id_tbl.COUNT || ' Streams '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
l_sel_id_tbl.DELETE;
l_sel_amt_tbl.DELETE;
/*-- Store this updated amount and ids for bulk updation later
l_sel_id_tbl(i) := l_work_acc_strms_tbl(inx_work).sel_id;
print(l_prog_name, 'Total Number of Stream Elements to be updated ' || l_sel_id_tbl.COUNT );
print(l_prog_name, 'Start: Performaing the Bulk Update of ' || l_sel_id_tbl.COUNT || ' Streams. '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
UPDATE okl_strm_elements
SET amount = l_sel_amt_tbl(i)
WHERE id = l_sel_id_tbl(i)
AND stm_id = l_stm_id_tbl(i);
print(l_prog_name, 'Done: Performaing the Bulk Update of ' || l_sel_id_tbl.COUNT || ' Streams '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
chr.end_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y'),
nvl(rpar.base_rate, 10) base_rate
FROM okc_k_headers_b chr,
okl_k_headers khr,
OKL_K_RATE_PARAMS rpar
WHERE khr.id = p_khr_id
AND chr.id = khr.id
AND rpar.khr_id(+) = khr.id;
SELECT FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information8) stub_amount
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.sts_code = 'TERMINATED'
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND cle.id = kleId
AND sty.id = rul1.object1_id1
AND sty.stream_type_purpose = 'TERMINATION_VALUE';
SELECT rgp.cle_id cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
sty.stream_type_purpose,
FND_DATE.canonical_to_date(cle.start_date) start_date,
NULL periods,
NULL frequency,
NULL structure,
NULL advance_arrears,
NULL amount,
NULL stub_days,
NULL stub_amount,
lse.lty_code lty_code,
NULL capital_amount,
NULL residual_value,
kle.fee_type fee_type,
kle.origination_income origination_income,
kle.amount fee_amount,
'INCOME_AMORT' fee_line_level_type
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_strm_type_b sty,
okc_rule_groups_b rgp,
okc_rules_b rul1
WHERE
rul1.dnz_chr_id = p_khr_id
AND rul1.rule_information_category = 'LASLH'
AND rul1.rgp_id = rgp.id
AND rgp.cle_id = cle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND sty.id = rul1.object1_id1
AND kle.fee_type = 'INCOME'
AND kle.origination_income IS NOT NULL
UNION
-- This part of the cursor will continue to fetch the Payments information for all fees
-- so that ISG uses this information and generate the Accrued Fee Income Streams
SELECT rgp.cle_id cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
sty.stream_type_purpose,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
lse.lty_code lty_code,
kle.capital_amount capital_amount,
kle.residual_value residual_value,
kle.fee_type fee_type,
kle.origination_income origination_income,
kle.amount fee_amount,
DECODE(kle.fee_type, 'INCOME', 'INCOME_ACCRUED', 'FEE_PAYMENT') fee_line_level_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_strm_type_b sty
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND sty.id = rul1.object1_id1
UNION
SELECT TO_NUMBER(NULL) cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
sty.stream_type_purpose,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
NULL lty_code,
TO_NUMBER(NULL) capital_amount,
TO_NUMBER(NULL) residual_value,
NULL fee_type,
NULL origination_income,
NULL fee_amount,
'ASSET_ACCRUALS' fee_line_level_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND rgp.cle_id IS NULL
AND sty.id = rul1.object1_id1
AND TO_NUMBER(rul2.object2_id1) = rul1.id;
SELECT kle.id,
NVL(kle.residual_value, 0) residual_value,
cle.start_date,
cle.sts_code,
kle.fee_type,
lse.lty_code,
kle.amount,
kle.capital_amount,
kle.capitalized_interest
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_khr_id
-- 12944979: For Terminated lines, copy_accrual_streams_isg will syncup
AND cle.sts_code IN ('PASSED', 'COMPLETE') --, 'TERMINATED')
AND cle.lse_id = lse.id
AND (lse.lty_code = 'FREE_FORM1' OR
kle.fee_type = 'FINANCED' OR
kle.fee_type = 'ROLLOVER' OR
lse.lty_code = 'LINK_FEE_ASSET')
AND cle.id = kle.id;
Select nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B rgp,
OKC_RULES_B sll,
okc_rules_b slh,
okl_strm_type_b sty
Where slh.rgp_id = rgp.id
and rgp.RGD_CODE = 'LALEVL'
and sll.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
and rgp.dnz_chr_id = chrId
and rgp.cle_id = kleId
and sty.id = to_number(slh.object1_id1)
and sty.stream_type_purpose NOT IN ('ESTIMATED_PROPERTY_TAX', 'UNSCHEDULED_PRINCIPAL_PAYMENT', 'DOWN_PAYMENT');
Select nvl(slh.object1_id1, -1) styId
From OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
okc_rules_b slh,
okc_K_lines_b cle_lnk,
okl_K_lines kle_roll
Where slh.rgp_id = crg.id
and crg.RGD_CODE = 'LALEVL'
and crl.RULE_INFORMATION_CATEGORY = 'LASLL'
and slh.RULE_INFORMATION_CATEGORY = 'LASLH'
AND TO_CHAR(slh.id) = crl.object2_id1
and crg.dnz_chr_id = chrId
and crg.cle_id = kleId
and crg.cle_id = cle_lnk.id
and cle_lnk.cle_id = kle_roll.id
and kle_roll.fee_type in ('ROLLOVER', 'FINANCED');
Select 'Y'
from dual
where Exists (
select vDtls.DISBURSEMENT_BASIS,
vDtls.DISBURSEMENT_FIXED_AMOUNT,
vDtls.DISBURSEMENT_PERCENT,
vDtls.PROCESSING_FEE_BASIS,
vDtls.PROCESSING_FEE_FIXED_AMOUNT,
vDtls.PROCESSING_FEE_PERCENT
from okl_party_payment_hdr vHdr,
okl_party_payment_dtls vDtls
where vDtls.payment_hdr_id = vHdr.id
and vHdr.CLE_ID = p_cle_id);
SELECT kle.id,
nvl(kle.fee_type, 'LINK_FEE_ASSET') fee_type,
lse.lty_code
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND (kle.fee_type='FINANCED' OR kle.fee_type='ROLLOVER' OR lse.lty_code='LINK_FEE_ASSET');
Select 'Y'
From dual
where Exists(
select kle.id
from okl_k_lines_full_v kle,
okc_line_styles_b lse,
okc_statuses_b sts
where KLE.LSE_ID = LSE.ID
and lse.lty_code = 'SUBSIDY'
and kle.dnz_chr_id = p_khr_id
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED'));
SELECT slh.object1_id1
FROM okc_rules_b slh,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = finfeesId
AND rgp.rgd_code= 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH';
SELECT slh.object1_id1
FROM okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b stm
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.rgd_code= 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = stm.id
AND stm.STREAM_TYPE_PURPOSE = 'RENT';
SELECT rbk_chr.contract_number rbk_contract_number,
rbk_chr.orig_system_id1 original_chr_id,
trx.rbr_code rbk_reason_code,
trx.date_transaction_occurred revision_date,
'ONLINE_REBOOK' rebook_type
,rbk_chr.start_date rbk_chr_start_date
,orig_chr.start_date orig_chr_start_date
FROM okc_k_headers_all_b rbk_chr,
okc_k_headers_all_b orig_chr,
okl_trx_contracts_all trx
WHERE rbk_chr.id = p_khr_id
AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
AND trx.khr_id_new = rbk_chr.id
AND trx.tsu_code = 'ENTERED'
AND trx.tcn_type = 'TRBK'
AND rbk_chr.orig_system_id1 = orig_chr.id
UNION
SELECT orig_chr.contract_number rbk_contract_number,
orig_chr.id original_chr_id,
trx.rbr_code rbk_reason_code,
trx.date_transaction_occurred revision_date,
'MASS_REBOOK' rebook_type
,orig_chr.start_date rbk_chr_start_date
,orig_chr.start_date orig_chr_start_date
FROM okc_k_headers_all_b orig_chr,
okl_trx_contracts_all trx
WHERE orig_chr.id = p_khr_id
AND trx.khr_id = orig_chr.id
AND trx.tsu_code = 'ENTERED'
AND trx.tcn_type = 'TRBK'
AND EXISTS
(
SELECT '1'
FROM okl_rbk_selected_contract rbk_chr
WHERE rbk_chr.khr_id = orig_chr.id
AND rbk_chr.status <> 'PROCESSED'
);
SELECT amort_inc_adj_rev_dt_yn rbk_prospectively
FROM okl_sys_acct_opts_all sysop
,okc_k_headers_b chr
WHERE sysop.org_id = chr.authoring_org_id;
SELECT 'EXISTS'
FROM DUAL
WHERE EXISTS
(
SELECT 1
FROM okc_k_lines_b cle
WHERE cle.dnz_chr_id = cp_khr_id
-- 12944979: For Terminated lines, copy_accrual_streams_isg will syncup
AND cle.sts_code = 'TERMINATED'
);
UPDATE OKC_K_HEADERS_TL
SET COMMENTS = CONTRACT_COMMENTS
WHERE ID = P_KHR_ID
AND LANGUAGE = USERENV('LANG');
l_principal_tbl.DELETE;
l_interest_tbl.DELETE;
l_prin_bal_tbl.DELETE;
l_termination_tbl.DELETE;
l_pre_tax_inc_tbl.DELETE;
UPDATE OKC_K_HEADERS_TL
SET COMMENTS = CONTRACT_COMMENTS
WHERE ID = P_KHR_ID
AND LANGUAGE = USERENV('LANG');
l_principal_tbl.DELETE;
l_interest_tbl.DELETE;
l_prin_bal_tbl.DELETE;
l_termination_tbl.DELETE;
l_pre_tax_inc_tbl.DELETE;
l_principal_tbl.delete;
l_interest_tbl.delete;
l_prin_bal_tbl.delete;
l_termination_tbl.delete;
l_pre_tax_inc_tbl.delete;
okl_contract_pub.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => lx_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_tbl => l_clev_tbl,
p_klev_tbl => l_klev_tbl,
p_edit_mode => 'N',
x_clev_tbl => x_clev_tbl,
x_klev_tbl => x_klev_tbl);
update okc_K_headers_tl
set comments = contract_comments
where id = p_khr_id
and language = userenv('LANG');
update okc_K_headers_tl
set comments = contract_comments
where id = p_khr_id
and language = userenv('LANG');
update okc_K_headers_tl
set comments = contract_comments
where id = p_khr_id
and language = userenv('LANG');
update okc_K_headers_tl
set comments = contract_comments
where id = p_khr_id
and language = userenv('LANG');
l_inflows_tbl.DELETE;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
UPDATE okl_streams
SET sty_id = l_sty_id,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE khr_id = p_khr_id
AND say_code = 'WORK'
AND NVL(purpose_code, '-99') = l_purpose_code
AND sty_id = l_rent_sty_id;
UPDATE okl_streams
SET sty_id = l_sty_id,
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE khr_id = p_khr_id
AND kle_id = r_financed_fees.id
AND say_code = 'WORK'
AND NVL(purpose_code, '-99') = l_purpose_code
AND sty_id = l_rent_sty_id;
UPDATE okl_strm_elements
SET comments=NULL
WHERE stm_id IN
(SELECT id from okl_streams WHERE khr_id = p_khr_id and say_code = 'WORK');
* delete the duplicates from the okl_streams header table.
*/
-- Modified by RGOOTY: Start
IF (p_generation_type <> 'RESIDUAL VALUE' AND p_generation_type <> 'CAPITAL REDUCTION')
THEN
consolidate_line_streams(p_khr_id => p_khr_id,
p_purpose_code => l_purpose_code,
x_return_status => lx_return_status);
SELECT TO_NUMBER(NULL) cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
NULL lty_code,
TO_NUMBER(NULL) capital_amount,
TO_NUMBER(NULL) residual_value
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_tl sty
WHERE rul2.dnz_chr_id = khrid
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND rul1.rgp_id = rgp.id
AND rgp.cle_id IS NULL
AND rul2.object2_id1 = rul1.id
AND rul1.object1_id1 = sty.id
AND sty.language = USERENV('LANG')
AND sty.name = 'VARIABLE INTEREST SCHEDULE';
Select str.id strm_id
From okl_streams str
Where str.sty_id = styId
and str.khr_id = chrId
and str.say_code = 'WORK';
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
lx_selv_tbl.delete;
l_selv_tbl.DELETE;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
pcn.streams_from_date,
pcn.streams_to_date,
ele.STREAM_ELEMENT_DATE,
ele.se_line_number,
ele.id stream_ele_id,
ele.amount stream_ele_amount,
sty.name stream_name,
stm.id stm_id
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm,
okl_strm_elements ele
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and ele.stm_id = stm.id
and pol.khr_id = p_agreement_id
and stm.khr_id = p_contract_id
and stm.kle_id = p_kle_id
and stm.sty_id = p_sty_id
and pol.status_code = p_pool_status
and ele.stream_element_date >= nvl(pcn.streams_from_date, ele.stream_element_date-1)
and ele.stream_element_date <= nvl(pcn.STREAMS_TO_DATE, OKL_POOL_PVT.G_FINAL_DATE)
AND pcn.status_code IN (G_IA_STS_ACTIVE,G_IA_STS_NEW) -- Added by VARANGAN -Pool Contents Impact - 26/11/07
order by pcn.khr_id, pcn.kle_id, pcn.sty_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pkhr.start_date contract_start_date,
pkhr.contract_number agreement_number,
nvl(pkhr.after_tax_yield, -1) agmnt_yield,
nvl(khr.PRE_TAX_IRR, -1) contract_yield
from okl_pools pol,
okl_pool_contents pcn,
okl_K_headers_full_v pkhr,
okl_k_headers khr
where pcn.pol_id = pol.id
and pcn.khr_id = khr.id
and pol.khr_id = pkhr.id
and pol.khr_id = agmntID
and pcn.khr_id = khrId
AND pcn.status_CODE IN (G_IA_STS_ACTIVE,G_IA_STS_NEW) ; -- Added by VARANGAN -Pool Contents Impact - 26/11/07
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
pcn.streams_from_date,
pcn.streams_to_date,
ele.STREAM_ELEMENT_DATE,
ele.se_line_number,
ele.id stream_ele_id,
ele.amount stream_ele_amount,
sty.name stream_name,
stm.id stm_id
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm,
okl_strm_elements ele
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and ele.stm_id = stm.id
and pol.khr_id = p_agreement_id
and stm.khr_id = p_contract_id
and stm.kle_id = p_kle_id
and stm.sty_id = p_sty_id
and pol.status_code = p_pool_status
and ele.stream_element_date >= nvl(pcn.streams_from_date, ele.stream_element_date-1)
and ele.stream_element_date <= nvl(pcn.STREAMS_TO_DATE, OKL_POOL_PVT.G_FINAL_DATE)
AND pcn.status_code = G_PC_STS_PENDING
order by pcn.khr_id, pcn.kle_id, pcn.sty_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pkhr.start_date contract_start_date,
pkhr.contract_number agreement_number,
nvl(pkhr.after_tax_yield, -1) agmnt_yield,
nvl(khr.PRE_TAX_IRR, -1) contract_yield
from okl_pools pol,
okl_pool_contents pcn,
okl_K_headers_full_v pkhr,
okl_k_headers khr
where pcn.pol_id = pol.id
and pcn.khr_id = khr.id
and pol.khr_id = pkhr.id
and pol.khr_id = agmntID
and pcn.khr_id = khrId
AND pcn.status_CODE = G_PC_STS_PENDING ;
select crl.RULE_INFORMATION1 method
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = 'LASEIR'
and crl.RULE_INFORMATION_CATEGORY = 'LASEIR'
and crg.dnz_chr_id = p_agreement_id;
Select sty.stream_type_subclass name,
sty.stream_type_purpose stream_type_purpose
from okl_strm_type_tl tl,
okl_strm_type_v sty
where tl.language = 'US'
and sty.id = tl.id
and tl.id = p_sty_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
pcn.streams_from_date,
pcn.streams_to_date,
ele.STREAM_ELEMENT_DATE,
ele.se_line_number,
ele.id stream_ele_id,
ele.amount stream_ele_amount,
sty.name stream_name,
stm.id stm_id
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm,
okl_strm_elements ele
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and pcn.status_code IN (G_IA_STS_ACTIVE,G_IA_STS_NEW)
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and ele.stm_id = stm.id
and pol.khr_id = p_agreement_id
and stm.khr_id = p_contract_id
and stm.kle_id = p_kle_id
and stm.sty_id = p_sty_id
and pol.status_code = p_pool_status
and ele.stream_element_date >= nvl(pcn.streams_from_date, ele.stream_element_date-1)
and ele.stream_element_date <= nvl(pcn.STREAMS_TO_DATE, OKL_POOL_PVT.G_FINAL_DATE)
order by ele.stream_element_date,pcn.khr_id, pcn.kle_id, pcn.sty_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
nvl(pkhr.after_tax_yield, -1) agmnt_yield,
pkhr.contract_number agreement_number,
nvl(khr.PRE_TAX_IRR, -1) contract_yield
from okl_pools pol,
okl_pool_contents pcn,
okl_K_headers_full_v pkhr,
okl_k_headers khr
where pcn.pol_id = pol.id
and pcn.khr_id = khr.id
and pol.khr_id = pkhr.id
and pol.khr_id = agmntID
and pcn.khr_id = khrId
AND pcn.status_code IN (G_IA_STS_ACTIVE,G_IA_STS_NEW) ; -- Added by VARANGAN -Pool Contents Impact - 26/11/07
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
pcn.streams_from_date,
pcn.streams_to_date,
ele.STREAM_ELEMENT_DATE,
ele.se_line_number,
ele.id stream_ele_id,
ele.amount stream_ele_amount,
sty.name stream_name,
stm.id stm_id
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm,
okl_strm_elements ele
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and pcn.status_code IN (G_PC_STS_PENDING)
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and ele.stm_id = stm.id
and pol.khr_id = p_agreement_id
and stm.khr_id = p_contract_id
and stm.kle_id = p_kle_id
and stm.sty_id = p_sty_id
and pol.status_code = p_pool_status
and ele.stream_element_date >= nvl(pcn.streams_from_date, ele.stream_element_date-1)
and ele.stream_element_date <= nvl(pcn.STREAMS_TO_DATE, OKL_POOL_PVT.G_FINAL_DATE)
order by ele.stream_element_date,pcn.khr_id, pcn.kle_id, pcn.sty_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
nvl(pkhr.after_tax_yield, -1) agmnt_yield,
pkhr.contract_number agreement_number,
nvl(khr.PRE_TAX_IRR, -1) contract_yield
from okl_pools pol,
okl_pool_contents pcn,
okl_K_headers_full_v pkhr,
okl_k_headers khr
where pcn.pol_id = pol.id
and pcn.khr_id = khr.id
and pol.khr_id = pkhr.id
and pol.khr_id = agmntID
and pcn.khr_id = khrId
and pcn.status_code IN (G_PC_STS_PENDING) ;
select crl.RULE_INFORMATION1 method
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = 'LASEIR'
and crl.RULE_INFORMATION_CATEGORY = 'LASEIR'
and crg.dnz_chr_id = p_agreement_id;
Select sty.stream_type_subclass name,
sty.stream_type_purpose stream_type_purpose
from okl_strm_type_tl tl,
okl_strm_type_v sty
where tl.language = 'US'
and sty.id = tl.id
and tl.id = p_sty_id;
select stm.id strm_id
from okl_strm_type_v sty,
okl_strm_type_tl stl,
okl_streams stm
where stl.language = 'US'
and stl.name = strmname
and sty.id = stl.id
and stm.sty_id = sty.id
and stm.kle_id = kleId
and stm.khr_id = khrId
and stm.say_code = 'CURR';
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(stm.khr_id, 0) = nvl(p_contract_id, -1)
and pcn.status_code IN (G_IA_STS_NEW,G_IA_STS_ACTIVE)
Union
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(pol.khr_id, 0) = nvl(p_agreement_id, -1)
and pcn.status_code IN (G_IA_STS_NEW,G_IA_STS_ACTIVE)
order by agreement_id, contract_id, asset_id, stream_type_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(stm.khr_id, 0) = nvl(p_contract_id, -1)
and pcn.status_code=G_PC_STS_PENDING
Union
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(pol.khr_id, 0) = nvl(p_agreement_id, -1)
and pcn.status_code=G_PC_STS_PENDING
order by agreement_id, contract_id, asset_id, stream_type_id;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
select stm.id strm_id
from okl_strm_type_v sty,
okl_strm_type_tl stl,
okl_streams stm
where stl.language = 'US'
and stl.name = strmname
and sty.id = stl.id
and stm.sty_id = sty.id
and stm.kle_id = kleId
and stm.khr_id = khrId
and stm.say_code = 'CURR';
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(stm.khr_id, 0) = nvl(p_contract_id, -1)
and pcn.status_code IN(G_IA_STS_NEW,G_IA_STS_ACTIVE)
Union
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(pol.khr_id, 0) = nvl(p_agreement_id, -1)
and pcn.status_code IN(G_IA_STS_NEW,G_IA_STS_ACTIVE)
order by agreement_id, contract_id, asset_id, stream_type_id;
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(stm.khr_id, 0) = nvl(p_contract_id, -1)
and pcn.status_code=G_PC_STS_PENDING
Union
select pol.khr_id agreement_id,
pcn.khr_id contract_id,
pcn.kle_id asset_id,
pcn.sty_id stream_type_id,
sty.stream_type_subclass stream_type_subclass,
sty.stream_type_purpose stream_type_purpose
from okl_pools pol,
okl_pool_contents pcn,
okl_strm_type_v sty,
okl_streams stm
where pcn.sty_id = sty.id
and pcn.pol_id = pol.id
and stm.kle_id = pcn.kle_id
and stm.khr_id = pcn.khr_id
and stm.sty_id = pcn.sty_id
and stm.say_code = 'CURR'
and stm.active_yn = 'Y'
and pol.status_code = poolstat
and nvl(pol.khr_id, 0) = nvl(p_agreement_id, -1)
and pcn.status_code=G_PC_STS_PENDING
order by agreement_id, contract_id, asset_id, stream_type_id;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
Okl_Streams_pub.update_streams(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
l_stmv_tbl.delete;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
SELECT nvl(khr.implicit_interest_rate, 0),
khr.deal_type
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
SELECT ele.amount,
ele.stream_element_date
FROM okl_streams stm,
okl_strm_elements ele,
okl_strm_type_b sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.khr_id = p_khr_id
AND stm.kle_id = p_kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND ele.stm_id = stm.id
AND ele.stream_element_date =
( SELECT max( ele.stream_element_date)
FROM okl_streams stm,
okl_strm_elements ele,
okl_strm_type_b sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.khr_id = p_khr_id
AND stm.kle_id = p_kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND ele.stm_id = stm.id
AND ele.stream_element_date <= p_date );
SELECT DISTINCT
sll.object1_id1 frequency,
nvl(sll.rule_information10, 'N') advance_arrears
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = styId
AND slh.id = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND sll.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_kle_id;
Select 'Y' What
from dual where Exists(
SELECT nvl(kle.fee_type, 'XYZ'),
nvl(lse.lty_code, 'XYZ')
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND (kle.fee_type = 'FINANCED' OR kle.fee_type = 'ROLLOVER' OR lse.lty_code = 'LINK_FEE_ASSET')
AND cle.id = kle.id
AND cle.id = p_kle_id);
SELECT sty.id
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_khr_id
AND rgp.cle_id = p_kle_id
AND rgp.rgd_code= 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
ORDER BY fnd_date.canonical_to_date(sll.rule_information2);
select kle.id
from okl_k_lines_full_v kle,
okc_line_styles_b lse,
okc_statuses_b sts
where KLE.LSE_ID = LSE.ID
and lse.lty_code in ('FREE_FORM1', 'FEE')
AND nvl(kle.fee_type,'-99') in ( '-99', 'FINANCED', 'ROLLOVER')
and kle.dnz_chr_id = p_khr_id
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT TO_NUMBER(NULL) cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
NULL lty_code,
TO_NUMBER(NULL) capital_amount,
TO_NUMBER(NULL) residual_value
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okl_strm_type_b sty
WHERE rul2.dnz_chr_id = khrid
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND rgp.cle_id IS NULL
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 str.id strm_id
From okl_streams str
Where str.sty_id = styId
and str.khr_id = chrId
and str.say_code = 'WORK';
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
chr.end_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
lx_selv_tbl.delete;
SELECT rgp.cle_id cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
lse.lty_code lty_code,
kle.capital_amount capital_amount,
kle.residual_value residual_value,
kle.fee_type fee_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul1.rule_information_category = 'LASLH'
AND rul1.jtot_object1_code = 'OKL_STRMTYP'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.id = kle.id
AND cle.lse_id = lse.id
ORDER BY cle_id, sty_id, start_date;
SELECT chr.currency_code
FROM okc_k_headers_b chr
WHERE chr.id = p_khr_id;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
SELECT rgp.cle_id cle_id,
TO_NUMBER(rul1.object1_id1) sty_id,
sty.stream_type_purpose,
FND_DATE.canonical_to_date(rul2.rule_information2) start_date,
TO_NUMBER(rul2.rule_information3) periods,
rul2.object1_id1 frequency,
rul2.rule_information5 structure,
DECODE(rul2.rule_information10, 'Y', 'ARREARS', 'ADVANCE') advance_arrears,
FND_NUMBER.canonical_to_number(rul2.rule_information6) amount,
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
lse.lty_code lty_code,
kle.capital_amount capital_amount,
kle.residual_value residual_value,
kle.fee_type fee_type
FROM okc_rule_groups_b rgp,
okc_rules_b rul1,
okc_rules_b rul2,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_strm_type_b sty
WHERE
rul2.dnz_chr_id = p_khr_id
AND rul2.rule_information_category = 'LASLL'
AND rul2.rgp_id = rgp.id
AND TO_NUMBER(rul2.object2_id1) = rul1.id
AND rgp.cle_id = cle.id
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND sty.id = rul1.object1_id1;
SELECT to_char(pdt.id) pid,
chr.currency_code currency_code,
khr.multi_gaap_yn multi_gaap_yn -- R12.1.2
FROM okc_k_headers_v chr,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = chr.id
AND chr.id = p_khr_id
AND khr.pdt_id = pdt.id(+);
select reporting_pdt_id
from okl_products
where id = p_pdt_id;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
l_stmv_tbl.delete; -- R12.1.2
l_full_selv_tbl.delete; -- R12.1.2
SELECT strm.id
, strm.say_code
, purpose_code -- R12.1.2
FROM okl_streams strm
WHERE khr_id = p_khr_id;
UPDATE OKL_STREAMS
SET say_code = l_say_code_tbl(i),
active_yn = l_active_yn_tbl(i),
date_history = l_date_history_tbl(i),
date_current = l_date_curr_tbl(i),
last_updated_by = FND_GLOBAL.USER_ID, -- BUG:14749215 changes start here
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14749215 changes end here
WHERE ID = l_strm_id_tbl(i);
SELECT chr.currency_code
FROM okc_k_headers_b chr
WHERE chr.id = p_khr_id;
select multi_gaap_yn
from okl_k_headers
where id = p_khr_id;