[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
where khr_id = p_khr_id
and kle_id = r_rollover_fee.kleId;
update okl_streams
set active_yn = 'N'
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 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 = cleId
and vHdr.DNZ_CHR_ID = khrId
and vHdr.PASSTHRU_TERM = 'BASE';
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 */
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' );
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 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(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.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
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
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' );
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';
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
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
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
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(+);
l_selv_tbl.DELETE;
l_selv_tbl.delete;
lx_selv_tbl.delete;
SELECT strm.id
, strm.say_code
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)
WHERE ID = l_strm_id_tbl(i);
SELECT chr.currency_code
FROM okc_k_headers_b chr
WHERE chr.id = p_khr_id;