[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rte.amount rate,
ele.stream_element_date ele_date,
ele.comments
FROM okc_k_headers_b chr_so,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okl_strm_type_b sty,
okl_streams stm,
okl_strm_elements ele,
okl_strm_elements rte
WHERE stm.khr_id = chr_so.id
AND chr_so.id = p_khr_id
AND stm.kle_id = cle_so.id
AND cle_so.dnz_chr_id = chr_so.id
AND cle_so.lse_id = lse_so.id
AND lse_so.lty_code = p_line_type --'SO_PAYMENT'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND ele.stm_id = stm.id
AND rte.stm_id = stm.id
AND rte.sel_id = ele.id
ORDER BY ele.stream_element_date;
SELECT rte.amount rate,
ele.stream_element_date ele_date,
ele.comments
FROM okc_k_headers_b chr_so,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okl_strm_type_b sty,
okl_streams stm,
okl_strm_elements ele,
okl_strm_elements rte
WHERE stm.khr_id = chr_so.id
AND chr_so.id = p_khr_id
AND stm.kle_id = cle_so.id
AND cle_so.dnz_chr_id = chr_so.id
AND cle_so.lse_id = lse_so.id
AND lse_so.lty_code = 'SO_PAYMENT'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND ele.stm_id = stm.id
AND rte.stm_id = stm.id
AND rte.sel_id = ele.id
ORDER BY ele.stream_element_date;
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(SLL.rule_information3) periods,
DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) dpp,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
NVL(sll.rule_information10, 'N') arrears_yn,
FND_NUMBER.canonical_to_number(sll.rule_information6) rent_amount
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
AND styt.name = streamName
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 sel.id se_id,
sel.amount se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears,
sel.sel_id se_sel_id
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE stm.kle_id = p_kle_id
AND stm.say_code = 'CURR'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
AND styt.name = streamName
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date;
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 TRUNC(FND_DATE.canonical_to_date(crl.rule_information2))
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLL'
AND crg.dnz_chr_id = p_khr_id
AND crg.cle_id = p_kle_id
AND TRUNC(FND_DATE.canonical_to_date(crl.rule_information2)) > TRUNC(p_date)
AND crl.rule_information2 IS NOT NULL
AND crl.rule_information6 IS NOT NULL
ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
Select nvl(fee_type, 'XYZ' ) fee_type
from okl_k_lines
where id = p_kle_id;
SELECT styt.name
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
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 FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(SLL.rule_information3) periods,
DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) dpp,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
NVL(sll.rule_information10, 'N') arrears_yn,
FND_NUMBER.canonical_to_number(sll.rule_information6) rent_amount
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
AND styt.name = streamName
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 sel.id se_id,
sel.amount se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears,
sel.sel_id se_sel_id,
sty.stream_type_purpose,
DECODE(sty.stream_type_purpose,
'UNSCHEDULED_PRINCIPAL_PAYMENT','UPP',
'UNSCHEDULED_INTEREST_PAYMENT','UIP',
'DOWN_PAYMENT','DOWN_PMNT',
'PRINCIPAL_PAYMENT', 'PRIN_PMNT',
'RENT' ) cf_purpose
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE stm.kle_id = p_kle_id
AND stm.say_code = 'WORK'
AND DECODE(stm.purpose_code, NULL, '-99', 'REPORT') = p_purpose_code
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
AND (styt.name = streamName OR
sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT' OR
sty.stream_type_purpose = 'UNSCHEDULED_INTEREST_PAYMENT' OR
sty.stream_type_purpose = 'DOWN_PAYMENT' OR
sty.stream_type_purpose = 'PRINCIPAL_PAYMENT')
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date;
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 TRUNC(FND_DATE.canonical_to_date(crl.rule_information2))
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLL'
AND crg.dnz_chr_id = p_khr_id
AND crg.cle_id = p_kle_id
AND TRUNC(FND_DATE.canonical_to_date(crl.rule_information2)) > TRUNC(p_date)
AND crl.rule_information2 IS NOT NULL
AND crl.rule_information6 IS NOT NULL
ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
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 styt.name
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
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,
sts.STE_CODE,
trunc(nvl(kle.DATE_TERMINATED, sysdate)) date_terminated
FROM okl_k_lines_full_v kle,
okc_statuses_b sts
WHERE kle.id = p_kle_id
and kle.dnz_chr_id = p_khr_id
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
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 ( sub_pre_tax_yield / 100 ) sub_pre_tax_yield
FROM okl_k_lines kle
WHERE kle.id = p_kle_id;
SELECT sel.id se_id,
sel.amount se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears,
sel.sel_id se_sel_id
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 = 'CURR'
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date;
Select SCS_CODE,
START_DATE
From okc_K_headers_b chr
Where chr.id = p_khr_id;
SELECT trunc(FND_DATE.canonical_to_date(rul_sll.rule_information2)) start_date,
NVL(rul_sll.rule_information10, 'Y') arrears_yn,
khr_so.term_duration
FROM okc_k_headers_b chr_so,
okl_k_headers khr_so,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okc_rule_groups_b rgp_pay,
okc_rules_b rul_slh,
okc_rules_b rul_sll,
okl_strm_type_b sty
WHERE cle_so.id = p_Kle_id
AND cle_so.dnz_chr_id = p_khr_id
AND cle_so.lse_id = lse_so.id
AND cle_so.dnz_chr_id = chr_so.id
AND khr_so.id = chr_so.id
AND cle_so.START_DATE = chr_so.START_DATE
AND lse_so.lty_code = p_line_type --'SO_PAYMENT'
AND rgp_pay.cle_id = cle_so.id
AND rgp_pay.dnz_chr_id = cle_so.dnz_chr_id
AND rgp_pay.rgd_code = 'LALEVL'
AND rgp_pay.id = rul_slh.rgp_id
AND rul_slh.rule_information_category = 'LASLH'
AND TO_CHAR(rul_slh.id) = rul_sll.object2_id1
AND rul_sll.rule_information_category = 'LASLL'
AND TO_NUMBER(rul_slh.object1_id1) = sty.id
AND sty.stream_type_purpose = 'RENT'
ORDER BY rul_sll.rule_information2;
SELECT sel_amt.id se_id,
sel_amt.amount se_amount,
trunc(sel_amt.stream_element_date) se_date,
DECODE(sel_amt.sel_id,NULL,'N','Y') stub,
sel_amt.comments se_arrears,
sel_rate.comments payment_missing_yn
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_amt,
okl_strm_elements sel_rate,
okc_k_headers_b chr_so,
okc_k_lines_b cle,
okc_line_styles_b lse
WHERE stm.khr_id = p_khr_id
AND stm.kle_id = p_kle_id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel_amt.stm_id
AND stm.kle_id = cle.id
AND cle.dnz_chr_id = chr_so.id
AND cle.START_DATE = chr_so.START_DATE
AND cle.lse_id = lse.id
AND sel_rate.comments = 'N' -- bug# 3381706
AND lse.lty_code = p_line_type --'SO_PAYMENT'
AND sel_amt.id = sel_rate.sel_id
ORDER BY sel_amt.stream_element_date;
Select SCS_CODE,
START_DATE
From okc_K_headers_b chr
Where chr.id = p_khr_id;
SELECT DISTINCT
sel_amt.id id,
sel_amt.amount cf_amount,
sel_amt.stream_element_date cf_date,
sel_rate.amount rate,
sel_rate.comments miss_amt,
sel_amt.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
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,
chr_so.start_date,
chr_so.end_date
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_rate,
okl_strm_elements sel_amt,
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
WHERE stm.khr_id = p_khr_id
--AND stm.kle_id = p_kle_id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel_amt.stm_id
AND sel_amt.comments IS NOT NULL
AND stm.id = sel_rate.stm_id
AND sel_rate.sel_id = sel_amt.id
AND stm.kle_id = cle.id
AND cle.dnz_chr_id = chr_so.id
AND kle.id = cle.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 = 'SECDEPOSIT'
AND sty.stream_type_purpose = 'SECURITY_DEPOSIT'
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';
SELECT DISTINCT
sel_amt.id id,
sel_amt.amount cf_amount,
sel_amt.stream_element_date cf_date,
sel_rate.amount rate,
sel_rate.comments miss_amt,
sel_amt.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
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,
chr_so.start_date,
lse.lty_code
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_rate,
okl_strm_elements sel_amt,
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
WHERE stm.khr_id = p_khr_id
AND stm.kle_id = cle.id
AND stm.kle_id = kle.id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel_amt.stm_id
AND sel_amt.comments IS NOT NULL
AND stm.id = sel_rate.stm_id
AND sel_rate.sel_id = sel_amt.id
AND stm.kle_id = cle.id
AND kle.id = cle.id
AND cle.dnz_chr_id = chr_so.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 NOT IN ('SECDEPOSIT', 'PASSTHROUGH' )
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';
SELECT DISTINCT
sel_amt.id id,
sel_amt.amount cf_amount,
sel_amt.stream_element_date cf_date,
sel_rate.amount rate,
sel_rate.comments miss_amt,
sel_amt.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
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,
chr_so.start_date
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_rate,
okl_strm_elements sel_amt,
okc_k_headers_b chr_so,
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 = p_khr_id
AND stm.kle_id = p_kle_id
AND stm.kle_id = cle.id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel_amt.stm_id
AND sel_amt.comments IS NOT NULL
AND stm.id = sel_rate.stm_id
AND sel_rate.sel_id = sel_amt.id
AND stm.kle_id = cle.id
AND cle.dnz_chr_id = chr_so.id
AND trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND lse.lty_code = p_line_type --'SO_PAYMENT'
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_NUMBER(slh.object1_id1) = stm.sty_id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT DISTINCT DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) days_per_period,
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle_so.END_DATE
FROM okc_k_headers_b chr_so,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okc_rule_groups_b rgp_pay,
okc_rules_b rul_slh,
okc_rules_b rul_sll,
okl_strm_type_b sty
WHERE cle_so.id = p_kle_id
AND cle_so.dnz_chr_id = p_khr_id
AND cle_so.lse_id = lse_so.id
AND cle_so.dnz_chr_id = chr_so.id
AND trunc(cle_so.START_DATE) = trunc(chr_so.START_DATE )
AND lse_so.lty_code = p_line_type --'SO_PAYMENT'
AND cle_so.id = rgp_pay.cle_id
AND rgp_pay.dnz_chr_id = cle_so.dnz_chr_id
AND rgp_pay.rgd_code = 'LALEVL'
AND rgp_pay.id = rul_slh.rgp_id
AND rul_slh.rule_information_category = 'LASLH'
AND TO_CHAR(rul_slh.id) = rul_sll.object2_id1
AND rul_sll.rule_information_category = 'LASLL'
AND TO_NUMBER(rul_slh.object1_id1) = sty.id
AND sty.stream_type_purpose = 'RENT';
SELECT NVL(kle.amount, 0) amount,
cle.start_date
FROM okc_k_headers_b chr_so,
okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.lse_id = lse.id
AND nvl(kle.fee_type, 'XXX') not in ( 'SECDEPOSIT', 'INCOME', 'CAPITALIZED' )
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND chr_so.id = cle.dnz_chr_id
AND trunc(chr_so.START_DATE) = trunc(cle.START_DATE)
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH')
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp,
okc_rules_b rul,
okc_rules_b rul2
WHERE rgp.cle_id = cle.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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL);
SELECT DISTINCT
sel_amt.id id,
sel_amt.amount cf_amount,
sel_amt.stream_element_date cf_date,
sel.amount rate,
sel.comments miss_amt,
sel.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
chr_so.START_DATE,
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,
NVL(TO_NUMBER(laptpr.rule_information1), 100) pass_through_percentage,
sll.rule_information10 arrears_yn
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_amt,
okl_strm_elements sel,
okc_k_headers_b chr_so,
okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle,
okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okc_rule_groups_b rgp2,
okc_rules_b laptpr
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel.stm_id
AND sel.comments IS NOT NULL
AND stm.kle_id = cle.id
AND cle.lse_id = lse.id
AND chr_so.id = cle.dnz_chr_id
AND trunc(chr_so.START_DATE) = trunc(cle.START_DATE)
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND stm.id = sel_amt.stm_id
AND sel_amt.comments IS NOT NULL
AND stm.id = sel.stm_id
AND sel.sel_id = sel_amt.id
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND stm.kle_id = rgp2.cle_id
AND rgp2.rgd_code = 'LAPSTH'
AND rgp2.id = laptpr.rgp_id
AND laptpr.rule_information_category = 'LAPTPR';
SELECT TO_NUMBER(rul.rule_information1) periods,
TO_NUMBER(rul.rule_information2) cf_amount,
DECODE(rul2.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) cf_dpp,
DECODE(rul2.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) cf_ppy,
DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) cf_mpp,
cle.start_date start_date
FROM okc_rules_b rul,
okc_rules_b rul2,
okc_rule_groups_b rgp,
okc_k_headers_b chr_so,
okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = rgp.cle_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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL
AND cle.dnz_chr_id = chr_so.id
AND trunc(chr_so.START_DATE) = trunc(cle.START_DATE)
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH');
SELECT NVL(a.precision,0) precision
FROM fnd_currencies a,
okc_k_headers_b b
WHERE b.currency_code = a.currency_code
AND b.id = p_khr_id;
SELECT start_date
FROM okc_k_headers_b b
WHERE b.id = p_khr_id;
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,
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_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND stm.id = sel.stm_id
AND sel.comments IS NOT NULL
AND stm.khr_id = rgp.dnz_chr_id
AND rgp.cle_id IS NULL
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';
Select 'Y' What
from dual
Where Exists(Select crl.id slh_id
From OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
okc_K_lines_b cle_lnk,
okl_K_lines kle_roll
Where crl.rgp_id = crg.id
and crg.RGD_CODE = 'LALEVL'
and crl.RULE_INFORMATION_CATEGORY = 'LASLL'
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 ('MISCELLANEOUS', 'FINANCED', 'ROLLOVER', 'PASSTHROUGH'));
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,
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,
cle.id kleId,
lse.lty_code
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 = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND sty.stream_type_purpose NOT LIKE 'ESTIMATED_PROPERTY_TAX'
AND stm.id = sel.stm_id
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 = p_khr_id
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 IN ('FREE_FORM1', 'FEE', 'LINK_FEE_ASSET')
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';
SELECT DISTINCT
kle.id,
NVL(kle.residual_value, 0) cf_amount,
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,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle.start_date,
cle.date_terminated,
cle.sts_code
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b styt
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
AND kle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND slh.object1_id1 = TO_CHAR(styt.id)
AND styt.stream_type_purpose = 'RENT';
SELECT FND_DATE.canonical_to_date(rule_information5)
FROM okc_rules_b
WHERE dnz_chr_id = p_khr_id
AND rule_information_category = 'LASDEP';
SELECT cle.id id,
cle.start_date start_date,
kle.capital_amount,
kle.capitalized_interest,
kle.date_funding_expected
FROM okc_k_lines_b cle,
okl_K_lines kle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.id = kle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1';
SELECT DISTINCT cle.id id,
cle.start_date start_date,
kle.capital_amount,
kle.capitalized_interest,
kle.date_funding_expected,
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,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle.date_terminated,
cle.sts_code
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b sty
WHERE cle.chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED') -- Bug 5011438
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = kle.id
AND kle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND sty.stream_type_purpose = 'RENT';
SELECT NVL(kle.amount, 0) amount,
cle.start_date,
cle.id kleId,
lse.lty_code
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_k_items cim,
okl_strm_type_b sty
WHERE cle.chr_id = p_khr_id
AND cle.lse_id = lse.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND kle.fee_type not in ( 'SECDEPOSIT', 'INCOME' )
AND lse.lty_code in ( 'FEE', 'LINK_FEE_ASSET')
AND cle.id = kle.id
AND cle.id = cim.cle_id
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND cim.object1_id1 = sty.id
AND sty.version = '1.0'
AND NVL(sty.capitalize_yn,'N') <> 'Y'
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH')
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp,
okc_rules_b rul,
okc_rules_b rul2
WHERE rgp.cle_id = cle.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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL);
SELECT DISTINCT
cle.id cleId,
sel.id id,
sel.amount cf_amount,
sel.stream_element_date cf_date,
sel.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
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,
NVL(TO_NUMBER(laptpr.rule_information1), 100) pass_through_percentage
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,
okc_rule_groups_b rgp2,
okc_rules_b laptpr
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND stm.id = sel.stm_id
AND sel.comments IS NOT NULL
AND stm.kle_id = cle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND lse.lty_code in ('FEE', 'LINK_FEE_ASSET')
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 stm.kle_id = rgp2.cle_id
AND rgp2.rgd_code = 'LAPSTH'
AND rgp2.id = laptpr.rgp_id
AND laptpr.rule_information_category = 'LAPTPR';
SELECT TO_NUMBER(rul.rule_information1) periods,
TO_NUMBER(rul.rule_information2) cf_amount,
DECODE(rul2.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) cf_dpp,
DECODE(rul2.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) cf_ppy,
DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) cf_mpp,
cle.start_date start_date
FROM okc_rules_b rul,
okc_rules_b rul2,
okc_rule_groups_b rgp,
okc_k_lines_b cle,
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 = 'FEE'
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH')
AND cle.id = rgp.cle_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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL;
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;
select to_char(kle1.id) top_svc_id,
kle1.amount top_amount,
kle.amount link_amount
from okl_k_lines_full_v kle,
okl_k_lines_full_v kle1,
okc_line_styles_b lse,
okc_statuses_b sts
where KLE1.LSE_ID = LSE.ID
and ((lse.lty_code = 'SOLD_SERVICE') OR (lse.lty_code = 'FEE'and kle1.fee_type ='PASSTHROUGH'))
and kle.dnz_chr_id = chrId
and kle1.dnz_chr_id = kle.dnz_chr_id
and sts.code = kle1.sts_code
and kle.id = linkId
and kle.cle_id = kle1.id
and sts.ste_code not in ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT currency_code
INTO l_currency_code
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT NVL(precision,0)
INTO l_precision
FROM fnd_currencies
WHERE currency_code = l_currency_code;
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;
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(SLL.rule_information3) periods,
DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) dpp,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
NVL(sll.rule_information10, 'N') arrears_yn,
FND_NUMBER.canonical_to_number(sll.rule_information6) rent_amount
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.stream_type_purpose = 'RENT'
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 sel.id se_id,
sel.amount se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.kle_id = p_kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.stream_type_purpose = 'RENT' --'LOAN PAYMENT'
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date;
select kle.id,
kle.start_date
from okl_k_lines_full_v kle,
okc_statuses_b sts
where kle.dnz_chr_id = p_khr_id
and kle.id = p_kle_id
and sts.code = kle.sts_code
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
l_additional_parameters.delete;
Select SCS_CODE,
START_DATE
From okc_K_headers_b chr
Where chr.id = p_khr_id;
SELECT DISTINCT
sel_amt.id id,
sel_amt.amount cf_amount,
sel_amt.stream_element_date cf_date,
sel_rate.amount rate,
sel_rate.comments miss_amt,
sel_amt.comments cf_arrear,
sty.stream_type_purpose cf_purpose,
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,
chr_so.start_date
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel_rate,
okl_strm_elements sel_amt,
okc_k_headers_b chr_so,
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 = p_khr_id
AND stm.kle_id = p_kle_id
AND stm.kle_id = cle.id
AND stm.say_code = 'WORK'
AND stm.purpose_code = 'FLOW'
AND stm.sty_id = sty.id
AND stm.id = sel_amt.stm_id
AND sel_amt.comments IS NOT NULL
AND stm.id = sel_rate.stm_id
AND sel_rate.sel_id = sel_amt.id
AND stm.kle_id = cle.id
AND cle.dnz_chr_id = chr_so.id
AND trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND lse.lty_code = p_line_type --'SO_PAYMENT'
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_NUMBER(slh.object1_id1) = stm.sty_id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT DISTINCT DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) days_per_period,
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle_so.END_DATE
FROM okc_k_headers_b chr_so,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okc_rule_groups_b rgp_pay,
okc_rules_b rul_slh,
okc_rules_b rul_sll,
okl_strm_type_b styt
WHERE cle_so.id = p_kle_id
AND cle_so.dnz_chr_id = p_khr_id
AND cle_so.lse_id = lse_so.id
AND cle_so.dnz_chr_id = chr_so.id
AND trunc(cle_so.START_DATE) = trunc(chr_so.START_DATE )
AND lse_so.lty_code = p_line_type --'SO_PAYMENT'
AND cle_so.id = rgp_pay.cle_id
AND rgp_pay.dnz_chr_id = cle_so.dnz_chr_id
AND rgp_pay.rgd_code = 'LALEVL'
AND rgp_pay.id = rul_slh.rgp_id
AND rul_slh.rule_information_category = 'LASLH'
AND TO_CHAR(rul_slh.id) = rul_sll.object2_id1
AND rul_sll.rule_information_category = 'LASLL'
AND TO_NUMBER(rul_slh.object1_id1) = styt.id
AND styt.stream_type_purpose = 'RENT';
SELECT NVL(a.precision,0) precision
FROM fnd_currencies a,
okc_k_headers_b b
WHERE b.currency_code = a.currency_code
AND b.id = p_khr_id;
SELECT start_date
FROM okc_k_headers_b b
WHERE b.id = p_khr_id;
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;
SELECT SUM(to_number(nvl(rul_rv.rule_information2,rul_rv.rule_information4))) Residual_value,
DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) days_per_period,
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle_so.id cle_id,
cle_so.start_date
FROM okc_k_headers_b chr_rv,
okc_line_styles_b lse_rv,
okc_k_lines_b cle_rv,
okc_rules_b rul_rv,
okc_rule_groups_b rgp_rv,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okc_rules_b rul_so,
okc_rule_groups_b rgp_so,
okc_rule_groups_b rgp_pay,
okc_rules_b rul_slh,
okc_rules_b rul_sll,
okl_strm_type_b styt
WHERE rgp_so.cle_id = p_kle_id
AND rgp_so.dnz_chr_id = p_khr_id
AND rgp_so.rgd_code = 'SOPYSC'
AND rgp_so.dnz_chr_id = rul_so.dnz_chr_id
AND rgp_so.id = rul_so.rgp_id
AND rul_so.rule_information_category = 'SOPMSC'
AND rgp_so.cle_id = cle_so.id
AND cle_so.id = p_kle_id
AND cle_so.dnz_chr_id = rul_so.dnz_chr_id
AND cle_so.lse_id = lse_so.id
AND lse_so.lty_code = 'SO_PAYMENT'
AND rul_rv.object1_id1 = to_char(rul_so.id)
AND rul_rv.dnz_chr_id = p_khr_id
AND rul_rv.dnz_chr_id = rul_so.dnz_chr_id
AND rul_rv.rgp_id = rgp_rv.id
AND rgp_rv.rgd_code = 'SOPSAD'
AND rgp_rv.dnz_chr_id = rul_so.dnz_chr_id
AND rgp_rv.cle_id = cle_rv.id
AND cle_rv.lse_id = lse_rv.id
AND lse_rv.lty_code = 'FREE_FORM1'
AND rgp_rv.dnz_chr_id = chr_rv.id
AND chr_rv.START_DATE = cle_rv.START_DATE
AND cle_so.id = rgp_pay.cle_id
AND rgp_pay.rgd_code = 'LALEVL'
AND rgp_pay.id = rul_slh.rgp_id
AND rul_slh.rule_information_category = 'LASLH'
AND TO_CHAR(rul_slh.id) = rul_sll.object2_id1
AND rul_sll.rule_information_category = 'LASLL'
AND rul_slh.object1_id1 = TO_CHAR(styt.id)
AND styt.stream_type_purpose = 'RENT'
GROUP BY DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360),
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1),
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12),
cle_so.id,
cle_so.start_date;
Select id
from okl_streams
where khr_id = p_khr_id
--and kle_id = p_kle_id
and purpose_code in ('PLOW', 'FLOW', 'STUBS');
Select id
from okl_strm_type_tl
where language = 'US'
and name = n;
SELECT DISTINCT
cle.id kleId,
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_tl stm
WHERE chr_so.id = p_khr_id
and cle.sts_code in( 'INCOMPLETE', 'COMPLETE')--'ENTERED'
AND cle.dnz_chr_id = chr_so.id
AND kle.id = cle.id
AND trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND (lse.lty_code = 'FEE' OR lse.lty_code = 'LINK_FEE_ASSET')
AND ( kle.fee_type <> 'CAPITALIZED' OR kle.fee_type IS NULL )
AND cle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_NUMBER(slh.object1_id1) = stm.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
order by stm.id, FND_DATE.canonical_to_date(sll.rule_information2);
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', 'TERMINATED', 'EXPIRED', 'CANCELLED'));
lt_pay_tbl.DELETE;
lt_pay_tbl.DELETE;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, l_prog_name || ' #of streams to delete - ' || i );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, l_prog_name || i || '# of streams are getting deleted ' );
Okl_Streams_pub.delete_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);
Okl_Streams_pub.delete_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);
Okl_Streams_pub.delete_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);
Okl_Streams_pub.delete_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);
SELECT DISTINCT
kle.id,
NVL(kle.residual_value, 0) cf_amount,
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,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle.start_date
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b styt
WHERE cle.chr_id = p_khr_id
AND cle.sts_code in ( 'BOOKED', 'TERMINATED' )
AND cle.lse_id = lse.id
AND cle.id = kleId
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = kle.id
AND kle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND TO_NUMBER(slh.object1_id1) = styt.id
AND styt.stream_type_purpose = 'RENT'; -- Bug 4626837
SELECT cle.id id,
cle.start_date start_date
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.sts_code in ( 'BOOKED', 'TERMINATED' )
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id IN (
SELECT distinct stm.kle_id
FROM okl_streams stm,
okl_strm_elements ele,
okl_strm_type_b sty
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'LOAN PAYMENT'
AND ele.stm_id = stm.id
AND ele.stream_element_date > p_ppd_date );
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 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,
khr.implicit_interest_rate,
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(+);
lx_selv_tbl.DELETE;
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,
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_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND stm.id = sel.stm_id
AND sel.comments IS NOT NULL
AND stm.khr_id = rgp.dnz_chr_id
AND rgp.cle_id IS NULL
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';
Select 'Y' What
from dual
Where Exists(Select crl.id slh_id
From OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
okc_K_lines_b cle_lnk,
okl_K_lines kle_roll
Where crl.rgp_id = crg.id
and crg.RGD_CODE = 'LALEVL'
and crl.RULE_INFORMATION_CATEGORY = 'LASLL'
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 = 'FINANCED');
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,
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 = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND sty.stream_type_purpose NOT LIKE 'ESTIMATED_PROPERTY_TAX'
AND stm.id = sel.stm_id
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 = p_khr_id
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';
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,
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,
cle.id kleId,
lse.lty_code,
kle.fee_type
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel,
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
WHERE stm.khr_id = p_khr_id
AND stm.say_code = 'WORK'
AND stm.purpose_code IS NULL
AND stm.sty_id = sty.id
AND sty.stream_type_purpose NOT LIKE 'ESTIMATED_PROPERTY_TAX'
AND stm.id = sel.stm_id
AND sel.comments IS NOT NULL
AND stm.kle_id = cle.id
AND cle.id = kle.id
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp2
WHERE rgp2.dnz_chr_id = p_khr_id
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 IN ('FEE', 'LINK_FEE_ASSET')
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';
SELECT DISTINCT
kle.id,
NVL(kle.residual_value, 0) cf_amount,
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,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle.start_date,
cle.date_terminated,
cle.sts_code
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b sty
WHERE cle.chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED') -- Bug 5011438
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = kle.id
AND kle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND sty.stream_type_purpose = 'RENT';
SELECT FND_DATE.canonical_to_date(rule_information5)
FROM okc_rules_b
WHERE dnz_chr_id = p_khr_id
AND rule_information_category = 'LASDEP';
SELECT cle.id id,
cle.start_date start_date,
kle.capital_amount,
kle.capitalized_interest,
kle.date_funding_expected
FROM okc_k_lines_b cle,
okl_K_lines kle,
okc_line_styles_b lse
WHERE cle.chr_id = p_khr_id
AND cle.id = kle.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1';
SELECT DISTINCT cle.id id,
cle.start_date start_date,
kle.capital_amount,
kle.capitalized_interest,
kle.date_funding_expected,
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,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle.date_terminated,
cle.sts_code
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll,
okl_strm_type_b sty
WHERE cle.chr_id = p_khr_id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED') -- Bug 5011438
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.id = kle.id
AND kle.id = rgp.cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND sty.stream_type_purpose = 'RENT';
SELECT NVL(kle.amount, 0) amount,
cle.start_date,
cle.id kleid,
lse.lty_code lty_code,
kle.fee_type fee_type
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_k_items cim,
okl_strm_type_b sty
WHERE cle.chr_id = p_khr_id
AND cle.lse_id = lse.id
AND cle.sts_code IN ('PASSED', 'COMPLETE', 'TERMINATED')
AND kle.fee_type not in ( 'SECDEPOSIT', 'INCOME' )
AND lse.lty_code in ( 'FEE', 'LINK_FEE_ASSET')
AND cle.id = kle.id
AND cle.id = cim.cle_id
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND cim.object1_id1 = sty.id
AND sty.version = '1.0'
AND NVL(sty.capitalize_yn,'N') <> 'Y'
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH')
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp,
okc_rules_b rul,
okc_rules_b rul2
WHERE rgp.cle_id = cle.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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL);
SELECT TO_NUMBER(rul.rule_information1) periods,
TO_NUMBER(rul.rule_information2) cf_amount,
DECODE(rul2.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) cf_dpp,
DECODE(rul2.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) cf_ppy,
DECODE(rul2.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) cf_mpp,
cle.start_date start_date,
kle.fee_type
FROM okc_rules_b rul,
okc_rules_b rul2,
okc_rule_groups_b rgp,
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 = 'FEE'
AND NOT EXISTS (SELECT 1
FROM okc_rule_groups_b rgp
WHERE rgp.cle_id = cle.id
AND rgp.rgd_code = 'LAPSTH')
AND cle.id = rgp.cle_id
AND cle.id = kle.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'
AND rul.rule_information1 IS NOT NULL
AND rul.rule_information2 IS NOT NULL
AND rul2.object1_id1 IS NOT NULL;
SELECT currency_code
INTO l_currency_code
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT NVL(precision,0)
INTO l_precision
FROM fnd_currencies
WHERE currency_code = l_currency_code;
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;
SELECT SUM(to_number(nvl(rul_rv.rule_information2,rul_rv.rule_information4))) Residual_value,
DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360) days_per_period,
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1) periods_per_year,
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) months_per_period,
cle_so.id cle_id,
cle_so.start_date
FROM okc_k_headers_b chr_rv,
okc_line_styles_b lse_rv,
okc_k_lines_b cle_rv,
okc_rules_b rul_rv,
okc_rule_groups_b rgp_rv,
okc_line_styles_b lse_so,
okc_k_lines_b cle_so,
okc_rules_b rul_so,
okc_rule_groups_b rgp_so,
okc_rule_groups_b rgp_pay,
okc_rules_b rul_slh,
okc_rules_b rul_sll,
okl_strm_type_b sty
WHERE rgp_so.cle_id = p_kle_id
AND rgp_so.dnz_chr_id = p_khr_id
AND rgp_so.rgd_code = 'SOPYSC'
AND rgp_so.dnz_chr_id = rul_so.dnz_chr_id
AND rgp_so.id = rul_so.rgp_id
AND rul_so.rule_information_category = 'SOPMSC'
AND rgp_so.cle_id = cle_so.id
AND cle_so.id = p_kle_id
AND cle_so.dnz_chr_id = rul_so.dnz_chr_id
AND cle_so.lse_id = lse_so.id
AND lse_so.lty_code = 'SO_PAYMENT'
AND rul_rv.object1_id1 = to_char(rul_so.id)
AND rul_rv.dnz_chr_id = p_khr_id
AND rul_rv.dnz_chr_id = rul_so.dnz_chr_id
AND rul_rv.rgp_id = rgp_rv.id
AND rgp_rv.rgd_code = 'SOPSAD'
AND rgp_rv.dnz_chr_id = rul_so.dnz_chr_id
AND rgp_rv.cle_id = cle_rv.id
AND cle_rv.lse_id = lse_rv.id
AND lse_rv.lty_code = 'FREE_FORM1'
AND rgp_rv.dnz_chr_id = chr_rv.id
AND chr_rv.START_DATE = cle_rv.START_DATE
AND cle_so.id = rgp_pay.cle_id
AND rgp_pay.rgd_code = 'LALEVL'
AND rgp_pay.id = rul_slh.rgp_id
AND rul_slh.rule_information_category = 'LASLH'
AND TO_CHAR(rul_slh.id) = rul_sll.object2_id1
AND rul_sll.rule_information_category = 'LASLL'
AND TO_NUMBER(rul_slh.object1_id1) = sty.id
AND sty.stream_type_purpose = 'RENT'
GROUP BY DECODE(rul_sll.object1_id1, 'M', 30, 'Q', 90, 'S', 180, 'A', 360),
DECODE(rul_sll.object1_id1, 'M', 12, 'Q', 4, 'S', 2, 'A', 1),
DECODE(rul_sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12),
cle_so.id,
cle_so.start_date;
Select id
from okl_streams
where khr_id = p_khr_id
--and kle_id = p_kle_id
and purpose_code in ('PLOW', 'FLOW', 'STUBS');
Select id
from okl_strm_type_tl
where language = 'US'
and name = n;
SELECT DISTINCT
cle.id kleId,
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 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 nvl(kle.fee_type, 'XXX') <> '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 stm.id;
SELECT DISTINCT
cle.id kleId,
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 trunc(cle.START_DATE) = trunc(chr_so.START_DATE)
AND cle.lse_id = lse.id
AND kle.id = cle.id
AND lse.lty_code in ('FEE', 'LINK_FEE_ASSET')
ANd nvl(kle.fee_type, 'XXX') <> '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 stm.id, FND_DATE.canonical_to_date(sll.rule_information2);
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', 'TERMINATED', 'EXPIRED', 'CANCELLED'));
lt_pay_tbl.DELETE;
lt_pay_tbl.DELETE;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, l_prog_name || ' #of streams to delete - ' || i );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, l_prog_name || i || '# of streams are getting deleted ' );
Okl_Streams_pub.delete_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);
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 = p_kle_id;
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 = p_kle_id;
Okl_Streams_pub.delete_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);
Okl_Streams_pub.delete_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);
Okl_Streams_pub.delete_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);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_khr_id;
select end_date, CURRENCY_CODE from okc_k_headers_all_b where id=khr_id;
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
TO_NUMBER(SLL.rule_information3) periods,
DECODE(sll.object1_id1, 'M', 30, 'Q', 120, 'S', 180, 'A', 360) dpp,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
NVL(sll.rule_information10, 'N') arrears_yn,
FND_NUMBER.canonical_to_number(sll.rule_information6) rent_amount
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
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 TO_NUMBER(slh.object1_id1) = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG')
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 sum(sel.amount) se_amount,
sel.stream_element_date se_date,
sel.comments se_arrears,
sty.stream_type_purpose,
decode(sty.stream_type_purpose,'LOAN_PAYMENT',1,'UNSCHEDULED_PRINCIPAL_PAYMENT',2,'UNSCHEDULED_LOAN_PAYMENT',2,'VARIABLE_LOAN_PAYMENT',3,4) stream_ordr
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 = 'CURR'
AND DECODE(stm.purpose_code, NULL, '-99', 'REPORT') = '-99'
AND stm.sty_id = sty.id
AND sty.version = '1.0'
AND sty.id = styt.id
AND STYT.LANGUAGE = USERENV('LANG') -- Bug 4626837
AND sty.stream_type_purpose in ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT','UNSCHEDULED_PRINCIPAL_PAYMENT','UNSCHEDULED_LOAN_PAYMENT')
AND sel.date_billed is null
AND stm.id = sel.stm_id
AND sel.stream_element_date >=p_loan_start_date
AND sel.stream_element_date <=p_end_date
GROUP BY
sel.stream_element_date ,
decode(sty.stream_type_purpose,'LOAN_PAYMENT',1,'UNSCHEDULED_PRINCIPAL_PAYMENT',2,'UNSCHEDULED_LOAN_PAYMENT',2,'VARIABLE_LOAN_PAYMENT',3,4),
sel.comments,
sty.stream_type_purpose
ORDER BY stream_element_date,
decode(sty.stream_type_purpose,'LOAN_PAYMENT',1,'UNSCHEDULED_PRINCIPAL_PAYMENT',2,'UNSCHEDULED_LOAN_PAYMENT',2,'VARIABLE_LOAN_PAYMENT',3,4);
SELECT PRECISION
FROM fnd_currencies_vl
WHERE currency_code = p_currency_code
AND enabled_flag = 'Y'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active, SYSDATE) >= SYSDATE;