[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_sty_id;
SELECT CODE
FROM okl_strm_type_b
WHERE id = p_sty_id;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'N'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND DEPENDENT_STY_ID = p_sty_id
AND PRICING_NAME IS NOT NULL;
SELECT CODE
FROM okl_strm_type_b
WHERE id = p_sty_id;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRIMARY_STY_ID, PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRIMARY_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRIMARY_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT PRIMARY_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND PRICING_NAME IS NOT NULL;
x_primary_sty_id.delete;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID, PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_ID = p_primary_sty_id
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose
AND PRICING_NAME IS NOT NULL;
SELECT pdt_id, start_date
FROM okl_k_headers_full_v
WHERE id = l_khr_id;
SELECT DEPENDENT_STY_ID,PRICING_NAME
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE PRIMARY_YN = 'N'
AND STL.PDT_ID = l_product_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = p_primary_sty_purpose
AND DEPENDENT_STY_PURPOSE = p_dependent_sty_purpose
AND PRICING_NAME IS NOT NULL;
PROCEDURE update_rvi_amount(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
p_chr_id IN NUMBER,
p_purpose IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
-- cursor to fetch the abosrbed fee line with purpose as RVI
CURSOR rvi_fee_csr IS
SELECT KLE.ID ID
FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE, OKC_LINE_STYLES_B LSE
WHERE CLE.CHR_ID = p_chr_id
AND CLE.DNZ_CHR_ID = p_chr_id
AND CLE.ID = KLE.ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FEE'
AND KLE.FEE_PURPOSE_CODE = 'RVI'
AND KLE.FEE_TYPE = 'ABSORBED';
SELECT LINE_ID, ITEM_ID, ITEM_OBJECT1_ID1, ITEM_OBJECT1_ID2, LINE_START_DATE, LINE_END_DATE, LINE_AMOUNT
FROM okl_k_fee_lines_uv
WHERE LINE_ID = p_line_id;
SELECT ele.amount
FROM okl_strm_elements ele,
okl_streams str
WHERE ele.stm_id = p_strm_id
AND str.khr_id = p_khr_id
AND str.sty_id = p_sty_id
AND upper(str.say_code) = 'CURR'
AND upper(str.active_yn) = 'Y';
l_api_name VARCHAR2(255) := 'update_rvi_amount';
okl_maintain_fee_pvt.update_fee_type(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_fee_types_rec => l_fee_types_rec,
x_fee_types_rec => lx_fee_types_rec);
okl_streams_pub.delete_streams(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_rec => l_stmv_rec);
END update_rvi_amount;
SELECT tax_book
FROM okl_txd_assets_v txd
WHERE txd.asset_number = p_asset_num;
SELECT trns.value,
books.book_type_code
FROM okl_sgn_translations trns,
fa_book_controls books
WHERE trns.jtot_object1_code = 'FA_BOOK_CONTROLS'
AND trns.object1_id1 = books.book_type_code
AND books.book_type_code = p_book;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM okl_txd_assets_b txd
WHERE txd.asset_number = p_asset_number
AND txd.tax_book = fnd_profile.value('OKL_REPORTING_PDT_ASSET_BOOK'));
SELECT 'Y'
FROM fa_book_controls
WHERE book_class = 'TAX'
AND mass_copy_source_book = p_corp_book
AND book_type_code = OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_RPT_PROD_BOOK_TYPE_CODE);
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND cle.sts_code = 'TERMINATED'
AND lse.lty_code = 'FREE_FORM1');
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 'Y'
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.id = p_cle_id
AND cle.lse_id = lse.id
AND cle.sts_code = 'TERMINATED'
AND lse.lty_code = 'FREE_FORM1');
l_additional_parameters.delete;
l_additional_parameters.delete;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT cle.id id
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE dnz_chr_id = p_khr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE';
PROCEDURE delete_working_streams(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER) AS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_WKG_STRMS';
print( l_api_name, 'DELETE:'||to_char(l_strms_orig_rec.strm_id));
print( l_api_name, 'DELETE:'||to_char(l_strms_rep_rec.strm_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);
End delete_working_streams;
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_chr_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,
-- srsreeni Bug 5917582 start
TO_NUMBER(rul2.rule_information7) stub_days,
TO_NUMBER(rul2.rule_information8) stub_amount,
-- srsreeni Bug 5917582 end
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,
lse.lty_code lty_code,
kle.capital_amount capital_amount,
kle.residual_value residual_value
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_chr_id
AND rul2.rule_information_category = 'LASLL'
AND rgp.RGD_CODE = 'LALEVL'
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','TERMINATED', 'COMPLETE')
AND lse.lty_code = 'FEE'
AND kle.fee_type = 'PASSTHROUGH'
AND cle.id = kle.id
AND cle.lse_id = lse.id
--srsreeni bug 5869271 start add
ORDER BY cle_id,sty_id, FND_DATE.canonical_to_date(rul2.rule_information2);
SELECT 'Y' What
FROM dual
WHERE EXISTS(SELECT stm.id stmid
FROM okl_streams stm
WHERE stm.khr_id = khrid
--AND stm.say_code = 'CURR'
AND stm.say_code = 'WORK'
AND stm.sgn_code = 'STMP'
AND NVL(stm.purpose_code, 'ORIGIN') = p
AND stm.active_yn = 'N');
SELECT stm.id stmid
FROM okl_streams stm
WHERE stm.khr_id = khrid
AND stm.kle_id = kleid
AND stm.sty_id = styid
AND stm.say_code = 'CURR'
AND nvl(stm.purpose_code, 'ORIGIN') = p
AND stm.active_yn = 'Y';
Select dependent_sty_id,dependent_sty_name
FROM OKL_STRM_TMPT_LINES_UV where primary_sty_id = payment_type_id
and pdt_id = (select pdt_id from okl_k_headers where id = p_chr_id)
and dependent_sty_purpose = p_purpose
and dependent_sty_id is not null;
l_stmv_tbl.delete;
okl_streams_pub.delete_streams(p_api_version => p_api_version,
x_return_status => x_return_status,
p_init_msg_list => p_init_msg_list,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_tbl => l_stmv_tbl);
l_selv_tbl.delete;
lx_selv_tbl.delete;
l_selv_tbl.delete;
lx_selv_tbl.delete;
l_selv_tbl.DELETE(1, l_selv_tbl.COUNT);
amounts.DELETE(1, amounts.COUNT);
Okl_Streams_Pub.update_streams(
p_api_version => l_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_stmv_rec => l_stmv_rec
,x_stmv_rec => x_stmv_rec);
l_selv_tbl.DELETE(1, l_selv_tbl.COUNT);
Select sty.id sty_id,
sty.name
From okl_streams str,
okl_strm_type_tl sty
Where sty.id = str.sty_id
and str.khr_id = chrId
and sty.name = st
and sty.language = 'US'
and nvl( str.kle_id, 'XXX' ) = 'XXX';
l_strmele_tbl.DELETE(1, l_strmele_tbl.COUNT);
SELECT DISTINCT str.transaction_number transaction_number
FROM okl_streams str
WHERE str.khr_id = chrId
AND str.say_code = 'CURR'
AND nvl(str.purpose_code, 'ORIGIN') = pp;
SELECT nvl(sum(ele.AMOUNT),0)
FROM okl_strm_elements ele,
okl_streams str,
okl_strm_type_b sty
WHERE str.khr_id = chrId
AND nvl(str.kle_id, -1) = kleid
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND nvl(str.purpose_code, 'ORIGIN') = p
AND sty.stream_type_purpose <> 'REBOOK_BILLING_ADJUSTMENT'
AND ele.stm_id = str.id
AND nvl(ele.date_billed, sysdate+1) < sysdate;
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.initial_direct_cost,
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 in ( 'FREE_FORM1', 'FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET')--bug# 3343133 (ssiruvol)
AND tl.id = kle.id
AND tl.language = USERENV('LANG')
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
--Start of bug#3121708 modification BAKUCHIB
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT chr.orig_system_source_code,
chr.start_date,
chr.authoring_org_id,
chr.inv_organization_id,
khr.deal_type,
pdt.id pid,
chr.currency_code currency_code,
khr.term_duration term,
chr.orig_system_id1
FROM okc_k_headers_v chr,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = chr.id
AND chr.id = chrId
AND khr.pdt_id = pdt.id(+);
SELECT nvl(tl.id,-1) id,
b.stream_type_subclass
FROM okl_strm_type_tl tl,
okl_strm_type_b b
WHERE tl.language = USERENV('LANG')
AND tl.name = p_strm_name
AND tl.id = b.id;
l_selv_tbl_secure.DELETE;
l_selv_tbl_intm_rentals.DELETE;
l_selv_tbl_intm_rentals.DELETE(1, l_selv_tbl_intm_rentals.LAST);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 'Y'
FROM okl_rbk_selected_contract
WHERE khr_id = p_chr_id
AND status = 'UNDER REVISION');
SELECT NVL(SUM(ele.AMOUNT),0) amount
FROM okl_strm_elements ele,
okl_streams str,
okl_strm_type_b sty
WHERE str.khr_id = p_chr_id
AND NVL(str.kle_id, -1) = p_kle_id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND nvl(str.purpose_code, 'ORIGIN') = p_purpose
AND sty.stream_type_purpose <> 'REBOOK_BILLING_ADJUSTMENT'
AND ele.stm_id = str.id
AND NVL(ele.date_billed, SYSDATE+1) < SYSDATE;
SELECT NVL(SUM(ele.AMOUNT),0) amount
FROM okl_strm_elements ele,
okl_streams str,
okl_strm_type_b sty
WHERE str.khr_id = p_chr_id
AND NVL(str.kle_id, -1) = p_kle_id
AND str.sty_id = sty.id
AND str.say_code = 'CURR'
AND nvl(str.purpose_code, 'ORIGIN') = p_purpose
AND sty.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
AND ele.stm_id = str.id
AND ele.date_billed IS NULL;
SELECT kle.id kle_id,
kle.start_date
FROM okc_k_lines_b kle,
okc_line_styles_b lse
WHERE kle.lse_id = lse.id
AND lse.lty_code in ( 'FREE_FORM1', 'FEE', 'SOLD_SERVICE', 'LINK_SERV_ASSET')--bug# 3343133 (ssiruvol)
AND kle.dnz_chr_id = p_chr_id;
SELECT nvl(tl.id,-1) id,
b.stream_type_subclass
FROM okl_strm_type_tl tl,
okl_strm_type_b b
WHERE tl.language = USERENV('LANG')
AND tl.name = p_strm_name
AND tl.id = b.id;
SELECT OKL_SIF_SEQ.NEXTVAL
FROM dual;
l_selv_tbl_secure.DELETE;
l_selv_tbl_intm_rentals.DELETE;
SELECT distinct crg.cle_id line_id,
(NVL(crl.rule_information5,-1)) structure
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okc_rules_b crl1
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLL'
AND crl1.rule_information_category = 'LASLH'
AND crl.object2_id1 = crl1.id
AND crl1.object1_id1 = styid
AND crg.dnz_chr_id = chrId
AND (NVL(crl.rule_information5,-1)) not in (-1,0);
SELECT (NVL(crl.rule_information5,-1)) structure,
fnd_date.canonical_to_date(crl.RULE_INFORMATION2) START_DATE,
crl.RULE_INFORMATION3 PERIOD_IN_MONTHS,
crl.RULE_INFORMATION6 AMOUNT,
nvl(crl.RULE_INFORMATION10,'N') ARREARS,
crl.RULE_INFORMATION7 STUB_DAYS,
crl.RULE_INFORMATION8 STUB_AMOUNT,
TUOM.ID1 FREQUENCY,
null END_DATE
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okc_rules_b crl1,
okl_time_units_v tuom
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLL'
AND crl1.rule_information_category = 'LASLH'
AND crl.object2_id1 = crl1.id
AND crl1.object1_id1 = styId
AND crg.dnz_chr_id = chrId
AND TUOM.ID1=crl.OBJECT1_ID1
AND (NVL(crl.rule_information5,-1)) not in (-1,0)
AND crg.cle_id = kleId
ORDER BY start_date ASC;
SELECT str.Id,
str.transaction_number,
str.sgn_code sgn_code,
str.khr_id,
str.sty_id,
str.say_code,
str.active_yn,
str.kle_id
FROM okl_streams str
WHERE str.khr_id = chrId
AND str.kle_id = kleId
AND str.say_code = 'WORK'
--Added by kthiruva for Streams Performance Patch
--Bug 4346646 - Start of Changes
AND str.sty_id = styId
--Bug 4346646 -End of Changes
AND nvl( str.purpose_code, 'ORIGIN') = pp;
SELECT ele.id,
ele.date_billed,
ele.stream_element_date,
ele.amount,
ele.accrued_yn,
ele.comments,
ele.stm_id stm_id,
ele.se_line_number se_line_number
FROM okl_strm_elements ele
WHERE ele.stm_id = stmid
ORDER BY ele.stream_element_date;
SELECT count(stm.id) cnt
FROM okl_streams stm
WHERE stm.khr_id = khrid
AND stm.say_code = 'WORK'
AND stm.sgn_code = 'STMP'
AND nvl(stm.purpose_code, 'ORIGIN') = p;
l_payment_tbl.delete;
okl_streams_pub.update_streams(
p_api_version => l_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_rec => l_stmv_rec
,p_selv_tbl => l_selv_tbl
,x_stmv_rec => x_stmv_rec
,x_selv_tbl => x_selv_tbl);
l_selv_tbl.DELETE(1, l_selv_tbl.COUNT);
l_selv_tbl_adv_rentals.DELETE(1, l_selv_tbl_adv_rentals.COUNT);
Procedure update_contract_yields(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN VARCHAR2,
p_chr_yields IN yields_rec_type) AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_YIELDS';
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM okl_streams
WHERE khr_id= p_dnz_chr_id
AND say_code = 'WORK'
AND sgn_code = 'STMP');
SELECT 1
FROM dual
WHERE EXISTS (SELECT '1'
FROM okl_subsidies_b subb,
okc_k_lines_b cle_fin,
okc_line_styles_b lse_fin,
okc_k_lines_b cle,
okl_k_headers khr,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.dnz_chr_id = khr.id
AND khr.deal_type IN ('LEASEDF','LEASEOP','LEASEST','LOAN')
AND cle.cle_id = cle_fin.id
AND cle.dnz_chr_id = cle_fin.dnz_chr_id
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND subb.id = kle.subsidy_id
AND cle_fin.id = cle.cle_id
AND cle_fin.lse_id = lse_fin.id
AND lse_fin.lty_code = 'FREE_FORM1');
okl_contract_pvt.update_contract_header(
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_restricted_update => OKL_API.G_FALSE,
p_chrv_rec => pl_chrv_rec,
p_khrv_rec => pl_khrv_rec,
p_edit_mode => 'N',
x_chrv_rec => xl_chrv_rec,
x_khrv_rec => xl_khrv_rec);
end update_contract_yields;
-- Code for bulk update ends here -02/28/2004 - HKPATEL
i NUMBER;
Select ele.id
From okl_strm_elements ele,
okl_streams str,
--okl_strm_type_v sty,
OKL_STRM_TMPT_LINES_UV sty,
okl_k_lines_full_v kle,
okc_line_styles_b lse,
okc_statuses_b sts
Where ele.stm_id = str.id
and str.khr_id = chrId
and str.kle_id = kle.id
and str.sty_id = sty.primary_sty_id
--and sty.name = 'SECURITY DEPOSIT'
and sty.primary_sty_purpose = 'SECURITY_DEPOSIT'
and sty.pdt_id = pdtId
and str.say_code = 'CURR'
and nvl(str.purpose_code, 'ORIGIN') = pp
and str.active_yn = 'Y'
and KLE.LSE_ID = LSE.ID
and KLE.fee_type = 'SECDEPOSIT'
and lse.lty_code = 'FEE'
and kle.dnz_chr_id = str.khr_id
and sts.code = kle.sts_code
--Start of bug#3121708 modification BAKUCHIB
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED')
--End of bug#3121708 modification BAKUCHIB
and ele.amount < 0
Order By ele.stream_element_date;
SELECT count(stm.id) cnt
FROM okl_streams stm
WHERE stm.khr_id = khrid
AND stm.say_code = 'WORK'
AND stm.sgn_code = 'STMP'
AND nvl(stm.purpose_code, 'ORIGIN') = p;
select value
from okl_pdt_pqy_vals_uv
where name = 'LEASE' and pdt_id = product_id;
SELECT 'N'
FROM OKL_STREAMS str
WHERE EXISTS (SELECT ID FROM OKL_STRM_ELEMENTS ele WHERE ele.STM_ID = str.ID AND ele.DATE_BILLED IS NULL )
AND str.KHR_ID = chrId
AND str.STY_ID = styId
AND str.SAY_CODE = status
AND nvl(str.purpose_code, 'ORIGIN') = pp;
-- Code commented and added for bulk update by HKPATEL - 02/28/2005
/*
l_stmv_tbl(i).id := l_strms_rec.STRM_ID;
-- Code commented and added ends here for bulk update by HKPATEL - 02/28/2005
END IF;
-- Code for bulk update -02/28/2004 - HKPATEL
l_id_tbl(i):= l_strms_rec.STRM_ID;
-- Code for bulk update ends here -02/28/2004 - HKPATEL
-- code added and commented for user defined streams
/*
If (( l_strm_name = 'FEDERAL DEPRECIATION' ) OR
( l_strm_name = 'STATE DEPRECIATION' ) OR
( l_strm_name = 'BOOK DEPRECIATION' )) THEN
*/
If (( l_strm_name = 'FEDERAL_DEPRECIATION' ) OR
( l_strm_name = 'STATE_DEPRECIATION' ) OR
( l_strm_name = 'BOOK_DEPRECIATION' )) THEN
--l_stmv_tbl(i).active_yn := 'N';
/* Commented for Bulk update by HKPATEL -02/18/2005
If ( l_stmv_tbl.COUNT > 0 ) Then
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);
Commented code for Bulk update ends here by HKPATEL -02/18/2005 */
-- Bulk update
BEGIN
--Added by kthiruva for Debugging
write_to_log('Prior to the Historing of Current streams and updation of Work streams to current');
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_id_tbl(i);
write_to_log('Statuses updated successfully');
-- Bulk update ends here
End If;
l_selv_tbl.delete(1, l_selv_tbl.COUNT );
Okl_Streams_pub.delete_stream_elements(
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_selv_tbl => l_selv_tbl);
select ORIG_SYSTEM_ID1
from okc_k_headers_b
where id=cp_chr_id
and trim(ORIG_SYSTEM_SOURCE_CODE)='OKL_REBOOK';
SELECT LNK_CLE.ID LNK_ID
, FEE_CLE.ID FEE_ID
, LNK_CLE.DATE_TERMINATED
FROM OKC_K_LINES_B LNK_CLE
, OKC_LINE_STYLES_B LNK_LSE
, OKL_K_LINES FEE_KLE
, OKC_K_LINES_B FEE_CLE
, OKC_STATUSES_B STS
WHERE LNK_CLE.CLE_ID = FEE_CLE.ID
AND LNK_CLE.DNZ_CHR_ID = cp_chr_id
AND FEE_CLE.DNZ_CHR_ID = cp_chr_id
AND LNK_LSE.ID = LNK_CLE.LSE_ID
AND LNK_LSE.LTY_CODE = 'LINK_FEE_ASSET'
AND STS.CODE = LNK_CLE.STS_CODE
AND STS.STE_CODE = 'TERMINATED' -- LINKED line is terminated
AND FEE_KLE.ID = FEE_CLE.ID;
SELECT CLE.ID LINE_ID
, CLE.DATE_TERMINATED
FROM OKL_K_HEADERS KHR
, OKC_K_LINES_B CLE
, OKC_LINE_STYLES_B LSE
, OKC_STATUSES_B STS
WHERE CLE.DNZ_CHR_ID = KHR.ID
AND LSE.ID = CLE.LSE_ID
AND LSE.LTY_CODE ='FREE_FORM1'
AND STS.CODE = CLE.STS_CODE
AND STS.STE_CODE = 'TERMINATED'
AND KHR.ID = cp_chr_id
--vdamerla Bug6149951 starts. Need to restrict only LOAN-REVOLVING deal type for a terminated asset
--LOAN and LEASE stream types needs to be copied as they will not be requested for a terminated asset
-- AND KHR.DEAL_TYPE = 'LOAN' -- only LOAN contracts (exclude REV-LOAN)
AND KHR.DEAL_TYPE <> 'LOAN-REVOLVING'
--vdamerla Bug6149951 ends
;
SELECT STM.ID STREAM_ID
, STM.STY_ID
, STM.SGN_CODE
, STM.PURPOSE_CODE
, STM.ACTIVE_YN -- vdamerla Forward Ported Bug 6082209
, SEL.STREAM_ELEMENT_DATE
, SEL.AMOUNT
, SEL.SE_LINE_NUMBER
, SEL.COMMENTS -- vdamerla Forward Ported Bug 6344223
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_B STY -- vdamerla Forward Ported Bug 6082209
WHERE SEL.STM_ID = STM.ID
AND STM.KHR_ID = cp_chr_id
AND STM.KLE_ID = cp_cle_id
AND (nvl(STM.purpose_code,'ORIG') = (select nvl(si.PURPOSE_CODE,'ORIG') from okl_stream_interfaces si
where transaction_number = cp_trx_number))
AND STM.SAY_CODE = 'CURR'
-- vdamerla Forward Ported Bug6344223
AND SEL.STREAM_ELEMENT_DATE <= nvl(cp_date_terminated, SEL.STREAM_ELEMENT_DATE)
AND STM.SGN_CODE <> 'INTC'
AND STM.STY_ID = STY.ID
AND STY.stream_type_purpose NOT IN
('INVESTOR_CNTRCT_OBLIGATION_PAY', 'INVESTOR_DISB_ADJUSTMENT', 'INVESTOR_EVERGREEN_RENT_PAY', 'INVESTOR_INTEREST_INCOME',
'INVESTOR_INTEREST_PAYABLE', 'INVESTOR_LATE_FEE_PAYABLE', 'INVESTOR_LATE_INTEREST_PAY', 'INVESTOR_PAYABLE',
'INVESTOR_PRETAX_INCOME', 'INVESTOR_PRINCIPAL_PAYABLE', 'INVESTOR_RECEIVABLE', 'INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_RENT_BUYBACK', 'INVESTOR_RENT_DISB_BASIS', 'INVESTOR_RENT_PAYABLE', 'INVESTOR_RESIDUAL_BUYBACK',
'INVESTOR_RESIDUAL_DISB_BASIS', 'INVESTOR_RESIDUAL_PAY', 'INVESTOR_VARIABLE_INTEREST', 'PV_RENT_SECURITIZED',
'PV_RV_SECURITIZED', 'ADVANCE_RENT', 'PASS_THROUGH_EVERGREEN_FEE', 'PASS_THROUGH_EVERGREEN_RENT', 'PASS_THROUGH_EVERGREEN_SERVICE',
'PASS_THROUGH_FEE', 'PASS_THROUGH_SERVICE', 'PASS_THRU_EXP_ACCRUAL', 'PASS_THRU_REV_ACCRUAL', 'PASS_THRU_SVC_EXP_ACCRUAL',
'PASS_THRU_SVC_REV_ACCRUAL', 'PV_RV', 'PV_RV_GUARANTEED', 'PV_RV_UNGUARANTEED', 'RESIDUAL_GUARANTEED', 'RESIDUAL_VALUE',
'SECURITY_DEPOSIT', 'SERVICE_EXPENSE', 'SERVICE_FEE_AMORT_SCHEDULE', 'SERVICE_FEE_AUDIT_LETTER', 'SERVICE_FEE_DOCUMENT_REQ',
'SERVICE_FEE_EXCHG_REQUEST', 'SERVICE_FEE_GENERAL', 'SERVICE_FEE_INTEREST_CONV', 'SERVICE_FEE_INVOICE_DEMAND',
'SERVICE_FEE_INVOICE_REPRINT', 'SERVICE_FEE_PMT_CHANGE', 'SERVICE_FEE_REST_REQUEST', 'SERVICE_FEE_TERM_REQUEST',
'SERVICE_FEE_TRANS_REQUEST', 'SERVICE_FEE_VAR_RATE_STMNT', 'SERVICE_FEE_VAT_SCHEDULE', 'SERVICE_INCOME', 'SERVICE_PAYMENT',
'SERVICE_RENEWAL')
--AND SEL.STREAM_ELEMENT_DATE <= cp_date_terminated
-- vdamerla End Forward Ported Bug6344223
AND STM.SGN_CODE NOT IN ('INTC','LATE_CALC') -- Bug 6472228
ORDER BY STM.ID -- to get the stream elements of a stream together
;
SELECT '!'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_trx_contracts trx
WHERE CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND ktrx.source_trx_type = 'TCN'
AND trx.tcn_type = 'ALT'
AND trx.id = ktrx.source_trx_id
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED');
SELECT distinct stm.kle_id
FROM okl_streams stm,
okc_k_lines_b ln
WHERE stm.khr_id = p_chr_id
AND stm.say_code = 'CURR'
AND stm.kle_id = ln.id
AND ln.STS_CODE = 'TERMINATED'
AND (nvl(stm.purpose_code,'ORIG') = (select nvl(si.PURPOSE_CODE,'ORIG')
from okl_stream_interfaces si
where si.transaction_number = p_trx_number));
SELECT date_terminated
FROM okc_k_lines_b
WHERE id = p_line_id;
SELECT STM.ID STREAM_ID
, STM.STY_ID
, STM.SGN_CODE
, STM.PURPOSE_CODE
, STM.ACTIVE_YN
, SEL.STREAM_ELEMENT_DATE
, SEL.AMOUNT
, SEL.SE_LINE_NUMBER
, SEL.COMMENTS
FROM OKL_STREAMS STM
, OKL_STRM_ELEMENTS SEL
, OKL_STRM_TYPE_B STY
WHERE SEL.STM_ID = STM.ID
AND STM.KHR_ID = cp_chr_id
AND STM.KLE_ID IS NULL
AND (nvl(STM.purpose_code,'ORIG') = (select nvl(si.PURPOSE_CODE,'ORIG') from okl_stream_interfaces si
where transaction_number = cp_trx_number))
AND STM.SAY_CODE = 'CURR'
AND STM.SGN_CODE <> 'INTC'
AND STM.STY_ID = STY.ID
AND STY.stream_type_purpose NOT IN
('INVESTOR_CNTRCT_OBLIGATION_PAY', 'INVESTOR_DISB_ADJUSTMENT', 'INVESTOR_EVERGREEN_RENT_PAY', 'INVESTOR_INTEREST_INCOME',
'INVESTOR_INTEREST_PAYABLE', 'INVESTOR_LATE_FEE_PAYABLE', 'INVESTOR_LATE_INTEREST_PAY', 'INVESTOR_PAYABLE',
'INVESTOR_PRETAX_INCOME', 'INVESTOR_PRINCIPAL_PAYABLE', 'INVESTOR_RECEIVABLE', 'INVESTOR_RENTAL_ACCRUAL',
'INVESTOR_RENT_BUYBACK', 'INVESTOR_RENT_DISB_BASIS', 'INVESTOR_RENT_PAYABLE', 'INVESTOR_RESIDUAL_BUYBACK',
'INVESTOR_RESIDUAL_DISB_BASIS', 'INVESTOR_RESIDUAL_PAY', 'INVESTOR_VARIABLE_INTEREST', 'PV_RENT_SECURITIZED',
'PV_RV_SECURITIZED', 'ADVANCE_RENT', 'PASS_THROUGH_EVERGREEN_FEE', 'PASS_THROUGH_EVERGREEN_RENT', 'PASS_THROUGH_EVERGREEN_SERVICE',
'PASS_THROUGH_FEE', 'PASS_THROUGH_SERVICE', 'PASS_THRU_EXP_ACCRUAL', 'PASS_THRU_REV_ACCRUAL', 'PASS_THRU_SVC_EXP_ACCRUAL',
'PASS_THRU_SVC_REV_ACCRUAL', 'PV_RV', 'PV_RV_GUARANTEED', 'PV_RV_UNGUARANTEED', 'RESIDUAL_GUARANTEED', 'RESIDUAL_VALUE',
'SECURITY_DEPOSIT', 'SERVICE_EXPENSE', 'SERVICE_FEE_AMORT_SCHEDULE', 'SERVICE_FEE_AUDIT_LETTER', 'SERVICE_FEE_DOCUMENT_REQ',
'SERVICE_FEE_EXCHG_REQUEST', 'SERVICE_FEE_GENERAL', 'SERVICE_FEE_INTEREST_CONV', 'SERVICE_FEE_INVOICE_DEMAND',
'SERVICE_FEE_INVOICE_REPRINT', 'SERVICE_FEE_PMT_CHANGE', 'SERVICE_FEE_REST_REQUEST', 'SERVICE_FEE_TERM_REQUEST',
'SERVICE_FEE_TRANS_REQUEST', 'SERVICE_FEE_VAR_RATE_STMNT', 'SERVICE_FEE_VAT_SCHEDULE', 'SERVICE_INCOME', 'SERVICE_PAYMENT',
'SERVICE_RENEWAL')
ORDER BY STM.ID; -- to get the stream elements of a stream together
SELECT cle.id new_cle_id
FROM okc_k_lines_b cle
,okc_k_lines_b ole
WHERE cle.dnz_chr_id=cp_rbk_chr_id
and ole.id = cp_ole_id
and ole.lse_id = cle.lse_id
and ole.id=cle.orig_system_id1;
SELECT 'Y'
FROM OKL_STRM_TYPE_B sty,
OKL_PROD_STRM_TYPES psty
WHERE sty.id = psty.sty_id
AND psty.pdt_id = cp_pdt_id
AND psty.accrual_yn = 'Y'
--srsreeni Might need this
AND psty.sty_id=cp_sty_id
--
AND sty.stream_type_purpose NOT IN ('SUBSIDY_INCOME');
select nvl(purpose_code,'ORIGIN')
from okl_stream_interfaces
where transaction_number = p_trx_number;
SELECT pdt_id
FROM okl_k_headers
WHERE id = p_chr_id;
SELECT pdt.reporting_pdt_id
FROM okl_k_headers khr,
okl_products pdt
WHERE khr.id = p_chr_id
AND khr.pdt_id = pdt.id;
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS_ALL;
SELECT 'Y'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx,
okl_trx_contracts trx,
okl_txl_quote_lines_b txl
WHERE CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY'
--
AND ktrx.source_trx_type = 'TCN'
AND trx.tcn_type = 'ALT'
AND trx.id = ktrx.source_trx_id
AND TXL.QTE_ID = TRX.QTE_ID
AND TXL.qlt_code = 'AMCFIA'
AND TXL.kle_id = p_kle_id
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED')
AND TXL.QTE_ID = TRX.QTE_ID;
-- Code for bulk update ends here -02/18/2004 - HKPATEL
Cursor ylds_csr( chrId NUMBER ) IS
Select 'N' exist
from okl_k_headers_full_v
where id = chrId
and pre_tax_yield is NULL
and after_tax_yield is NULL
and pre_tax_irr is NULL
and after_tax_irr is NULL
and implicit_interest_rate is NULL;
SELECT DISTINCT cle.id line_id,
lev.rate
FROM okl_sif_ret_levels lev,
okl_sif_rets ret,
okl_stream_interfaces sif,
okl_sif_lines line,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse,
okl_sif_fees fee
WHERE lev.sir_id = ret.id
AND lev.level_type = 'Payment'
AND ret.transaction_number = sif.transaction_number
AND sif.id = line.sif_id
AND line.sif_id = fee.sif_id
AND line.id = fee.sil_id
AND line.kle_id = cle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND kle.fee_type = 'FINANCED'
AND fee.fee_index_number = lev.index_number
AND line.sil_type = 'SGN'
AND sif.transaction_number IN (SELECT MAX(transaction_number)
FROM okl_stream_interfaces
WHERE khr_id = p_chr_id
AND purpose_code IS NULL);
SELECT sll.id sll_id,
slh.id slh_id
FROM okc_rule_groups_b rgp,
okc_rules_b slh,
okc_rules_b sll
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = p_cle_id
AND rgp.id = slh.rgp_id
AND sll.rgp_id = slh.rgp_id
AND sll.dnz_chr_id = slh.dnz_chr_id
AND slh.dnz_chr_id = rgp.dnz_chr_id
AND sll.object2_id1 = slh.id
AND sll.object2_id2 = '#'
AND sll.jtot_object2_code = 'OKL_STRMHDR';
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT 1
FROM okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = p_chr_id
AND khr.pdt_id = pdt.id
AND pdt.reporting_pdt_id IS NOT NULL);
SELECT str.id strm_id,
str.kle_id,
str.sty_id sty_id,
str.sgn_code sgn_code,
str.sgn_code alloc_yn,
str.comments alloc_basis,
str.transaction_number trn_num,
str.purpose_code,
str.say_code,
str.active_yn
FROM okl_streams str
WHERE str.khr_id = p_chr_id
AND str.purpose_code = 'REPORT'
AND str.say_code = 'CURR'
AND str.sgn_code NOT IN ('INTC','LATE_CALC') -- bug 6472228
AND (NVL( str.kle_id, -1) = -1 OR
str.kle_id IN (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 kle.dnz_chr_id = p_chr_id
AND sts.code = kle.sts_code
AND lse.lty_code NOT IN ('INSURANCE')));
SELECT * FROM (
SELECT SIF.SIS_CODE REQUEST_STATUS
FROM OKL_STREAM_INTERFACES SIF
WHERE SIF.KHR_ID = p_khr_id
AND SIF.PURPOSE_CODE = 'REPORT'
ORDER BY SIF.TRANSACTION_NUMBER DESC)
WHERE ROWNUM < 2;
SELECT * FROM (
SELECT SIF.TRANSACTION_NUMBER TRANSACTION_NUMBER
FROM OKL_STREAM_INTERFACES SIF
WHERE SIF.KHR_ID = p_khr_id
AND SIF.PURPOSE_CODE = 'REPORT'
ORDER BY SIF.TRANSACTION_NUMBER DESC)
WHERE ROWNUM < 2;
OKL_RULE_PUB.update_rule(
-- End of modification for bug#3320656 revoked to OKL_RULE_PUB -- bakuchib
-- end of modification for bug#3320656 -- bakuchib
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_rulv_rec => r_rulv_rec,
p_edit_mode => 'N',
x_rulv_rec => rx_rulv_rec);
OKL_RULE_PUB.update_rule(
-- End of modification for bug#3320656 revoked to OKL_RULE_PUB -- bakuchib
-- end of modification for bug#3320656 -- bakuchib
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_rulv_rec => l_rulv_rec,
p_edit_mode => 'N',
x_rulv_rec => lx_rulv_rec);
write_to_log('Before the call to update_contract_yields');
update_contract_yields(
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_chr_id => p_chr_id,
p_chr_yields => l_chr_yields);
write_to_log('After the call to update_contract_yields, the return status is :'||x_return_status);
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);
/* Commeneted for Bulk update by HKPATEL -02/28/04
m_stmv_tbl(j).id := non_mg_rep_rec.strm_id;
UPDATE OKL_STREAMS
SET say_code = 'HIST',
active_yn = 'N',
date_history = sysdate
WHERE ID = l_id_tbl(i);
/* Commented for Bulk Update - HKPATEL - 02/18/2004
IF (m_stmv_tbl.COUNT > 0) THEN
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 => m_stmv_tbl,
x_stmv_tbl => mx_stmv_tbl);
write_to_log('Prior to the call to update_rvi_amount');
update_rvi_amount(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
p_chr_id => p_chr_id,
p_purpose => 'ORIGIN',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
write_to_log('After the call to Update_rvi_amount, the return status is :'||x_return_status);
write_to_log('Prior to the call to update_rvi_amount');
update_rvi_amount(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
p_chr_id => p_chr_id,
p_purpose => 'REPORT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
write_to_log('After the call to Update_rvi_amount, the return status is :'||x_return_status);
--2)If yes, we update the contract status
--3)If no and the request is still being processed, we register an alert and wait on the alert.
--4)The alert would be signalled by the reporting product once its stream generation request
-- is complete.
--5)Once the alert is received, the regular product can update the contract status to complete.
--6)If the reporting product's stream generation request does not complete, and there is no
-- alert signalled, then the contract status is not updated.
IF l_mgaap_yn = 'Y' THEN
--Fetching the request status of the Reporting Product
OPEN get_stream_req_status_csr(p_chr_id);
write_to_log('Prior to the call to update_contract_status');
okl_contract_status_pub.update_contract_status(
l_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
'COMPLETE',
p_chr_id );
write_to_log('After the call to update_contract_status, return status is :'||x_return_status);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
p_api_version => l_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_khr_id => l_chr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
okl_contract_status_pub.update_contract_status(
l_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
'COMPLETE',
p_chr_id );
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
p_api_version => l_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_khr_id => l_chr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
SELECT tl.name name,
stm.stream_type_class stream_type_class,
tl.description alloc_basis,
stm.capitalize_yn capitalize_yn,
stm.periodic_yn periodic_yn
FROM okl_strm_type_b stm,
okl_strm_type_tl tl
WHERE tl.id = stm.id
AND tl.language = 'US'
AND stm.id = styid;
SELECT id styid
FROM okl_strm_type_tl
WHERE LANGUAGE = 'US'
AND NAME = strmName;
SELECT cle.id
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.dnz_chr_id = chrid
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND sts.code = cle.sts_code
--Start of bug#3121708 modification BAKUCHIB
AND sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED');
SELECT kle.fee_type
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.dnz_chr_id = chrid
AND cle.id = kle.id
AND kle.id = feeId
AND sts.code = cle.sts_code
--Start of bug#3121708 modification BAKUCHIB
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED');
SELECT cle.id fin_id
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle,
okc_statuses_b sts
WHERE cle.dnz_chr_id = chrId
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
AND kle.fee_type = 'FINANCED';
SELECT cle.id fin_id
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle,
okc_statuses_b sts
WHERE cle.dnz_chr_id = chrId
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
AND kle.fee_type = 'ROLLOVER';
SELECT okc.id kle_id
FROM okc_k_lines_b okc,
okl_k_lines kle ,
okc_line_styles_b lse
WHERE okc.cle_id = cleId
AND okc.lse_id = lse.id
AND okc.id = kle.id
AND lty_code = 'LINK_FEE_ASSET';
SELECT TO_NUMBER(sgn.value) sty_id,
cle.id
FROM okl_sgn_translations sgn,
okl_strm_type_b sty,
okl_subsidies_v sub,
okl_k_lines kle,
okc_statuses_b sts,
okc_line_styles_b lse,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
AND cle.id = kle.id
AND TO_CHAR(kle.sty_id) = sgn.object1_id1
AND sgn.object1_id1 = TO_CHAR(sty.id)
AND sgn.jtot_object1_code = 'OKL_STRMTYP'
AND kle.subsidy_id = sub.id;
SELECT kle.sty_id,
cle.id
--srsreeni Bug5699923 Start
,sts.ste_code
--srsreeni Bug5699923 End
FROM okl_k_lines kle,
okc_k_lines_b cle,
okl_subsidies_v sub,
okc_statuses_b sts,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
AND cle.id = kle.id
AND kle.subsidy_id = sub.id;
SELECT 'Y'
FROM OKL_K_HEADERS
WHERE ID = p_chr_id
AND DEAL_TYPE = 'LOAN';
SELECT FND_DATE.canonical_to_date(rule_information5) date_returned
FROM okc_rules_b
WHERE dnz_chr_id = chrId
AND rule_information_category = 'LASDEP';
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,
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 kle.fee_type = 'SECDEPOSIT'
and sts.code = kle.sts_code
--Start of bug#3121708 modification BAKUCHIB
and sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT stl.ST_GEN_TMPT_SET_NAME strm_name,
stl.pricing_name pricing_name,
kle.id line_id,
stl.id styp_id
FROM OKL_STRM_TMPT_LINES_UV stl,
okc_k_items cim,
okl_k_lines_full_v kle,
okc_line_styles_b ls
WHERE cim.cle_id = kle.id
AND ls.id = kle.lse_id
AND ls.lty_code = 'FEE'
AND cim.object1_id1 = stl.primary_sty_id
AND cim.object1_id2 = '#'
AND kle.id = kleid
AND stl.pricing_name is not null;
SELECT cle.id,
NVL(kle.date_funding,cle.start_date) date_funding
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED')
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1';
SELECT subb.accounting_method_code,
cle.id
FROM okl_subsidies_b subb,
okc_k_lines_b cle_fin,
okc_line_styles_b lse_fin,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.cle_id = p_line_id
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND subb.id = kle.subsidy_id
AND cle_fin.id = cle.cle_id
AND cle_fin.lse_id = lse_fin.id
AND lse_fin.lty_code = 'FREE_FORM1';
SELECT 1
FROM dual
WHERE EXISTS (SELECT '1'
FROM okl_subsidies_b subb,
okc_k_lines_b cle_fin,
okc_line_styles_b lse_fin,
okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.cle_id = cle_fin.id
AND cle.dnz_chr_id = cle_fin.dnz_chr_id
AND cle.id = kle.id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND subb.id = kle.subsidy_id
AND cle_fin.id = cle.cle_id
AND cle_fin.lse_id = lse_fin.id
AND lse_fin.lty_code = 'FREE_FORM1');
SELECT kle.sty_id
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_b lse,
okl_subsidies_v sub
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.id = p_sub_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND cle.id = kle.id
AND kle.subsidy_id = sub.id;
SELECT sty_inc.code strm_desc
FROM okl_sgn_translations sgn,
okl_strm_type_b sty_inc,
okl_strm_type_b sty,
okl_subsidies_v sub,
okl_k_lines kle,
okc_line_styles_b lse,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.id = p_sub_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'SUBSIDY'
AND cle.id = kle.id
AND TO_CHAR(kle.sty_id) = sgn.object1_id1
AND sgn.object1_id1 = TO_CHAR(sty.id)
AND TO_CHAR(sty_inc.id) = sgn.value
AND sgn.jtot_object1_code = 'OKL_STRMTYP'
AND kle.subsidy_id = sub.id;
SELECT code
FROM okl_strm_type_b
WHERE id = p_sty_id;
SELECT lse.lty_code,
kle.fee_type,
tl.name,
tl.item_description,
kle.id,
kle.cle_id,
kle.oec,
kle.residual_code,
kle.capital_amount,
kle.initial_direct_cost,
kle.delivered_date,
kle.date_funding_required,
kle.residual_grnty_amount,
kle.date_funding,
kle.residual_value,
kle.date_delivery_expected,
kle.orig_system_id1 old_line_id,
kle.amount,
kle.price_negotiated,
kle.start_date,
kle.end_date,
kle.orig_system_id1,
sts.ste_code,
kle.tradein_amount,
kle.date_funding_expected,
kle.capital_reduction,
kle.capitalize_down_payment_yn
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 = 'LINK_FEE_ASSET'
AND tl.id = kle.id
AND kle.cle_id = p_cle_id
AND tl.language = userenv('LANG')
AND kle.dnz_chr_id = p_chr_d
AND sts.code = kle.sts_code
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT crl.rule_information2 amount,
styt.name strm_name
FROM okl_strm_type_tl styt,
okc_k_items cim,
okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = 'LAFEXP'
AND crl.RULE_INFORMATION_CATEGORY = 'LAFEXP'
AND crg.dnz_chr_id = p_chr_d
AND crg.cle_id = p_cle_id
AND cim.dnz_chr_id = crg.dnz_chr_id
AND cim.cle_id = crg.cle_id
AND cim.object1_id1 = TO_CHAR(styt.id)
AND cim.object1_id2 = '#'
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND styt.language = USERENV('lang');
SELECT okc.id id,
okc.chr_id chr_id,
okc.cle_id cle_id,
okc.dnz_chr_id dnz_chr_id,
kle.capital_amount capital_amount,
kle.amount amount,
lse.lty_code lty_code
-- srsreeni - Bug#5699923 - Added - Start
, sts.ste_code
-- srsreeni - Bug#5699923 - Added - End
FROM okc_k_lines_b okc,
okl_k_lines kle ,
okc_line_styles_b lse
,okc_statuses_b sts
WHERE okc.cle_id = cleId
AND okc.lse_id = lse.id
AND okc.id = kle.id
AND lty_code = 'LINK_FEE_ASSET'
AND sts.code=okc.sts_code;
SELECT kle.amount amount,
styt.name strm_name,
kle.fee_purpose_code fee_purpose_code,
styt.id sty_id
FROM okl_strm_type_tl styt,
okc_k_items cim,
okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.dnz_chr_id = p_chr_d
AND cle.id = p_cle_id
AND cle.id = kle.id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cim.cle_id = cle.id
AND cim.object1_id1 = TO_CHAR(styt.id)
AND cim.object1_id2 = '#'
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND styt.language = USERENV('lang');
SELECT SUM(TO_NUMBER(NVL(crl.rule_information3,1))) level_cnt
-- End of modification for Bug#3322910 BAKUCHIB Modified
--srsreeni Bug5890437 end
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crl.object2_id1 = rlgpId
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
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';
l_select Varchar2(2000);
select distinct(nvl(crl.RULE_INFORMATION5,-1)) structure
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 = chrId;
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT PARM.BASE_RATE
FROM OKL_K_RATE_PARAMS parm
WHERE PARM.KHR_ID = p_chr_id
AND PARM.PARAMETER_TYPE_CODE = 'ACTUAL'
AND p_date BETWEEN EFFECTIVE_FROM_DATE AND NVL(EFFECTIVE_TO_DATE,p_date);
l_select Varchar2(2000);
SELECT
gts.interest_calc_meth_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id =chrId;
SELECT
implicit_interest_rate
FROM
okl_k_headers
WHERE id=chrId;
select distinct(nvl(crl.RULE_INFORMATION5,-1)) structure
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 = chrId;
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT crl.id slh_id,
crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okl_strm_type_v sty
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
AND crl.object1_id1 = sty.id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
ORDER BY crl.rule_information1;
l_ppd_tbl.delete;
l_ppd_tbl.delete;
l_select Varchar2(2000);
select distinct(nvl(crl.RULE_INFORMATION5,-1)) structure
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 = chrId;
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT crl.id slh_id,
crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okl_strm_type_v sty
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
AND crl.object1_id1 = sty.id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
ORDER BY crl.rule_information1;
SELECT PARM.BASE_RATE
FROM OKL_K_RATE_PARAMS parm
WHERE PARM.KHR_ID = p_chr_id
AND PARM.PARAMETER_TYPE_CODE = 'ACTUAL'
AND p_date BETWEEN EFFECTIVE_FROM_DATE AND NVL(EFFECTIVE_TO_DATE,p_date);
l_ppd_tbl.delete;
l_ppd_tbl.delete;
SELECT count(crl.id)
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okl_strm_type_v sty
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLH'
AND crg.dnz_chr_id = chrId
AND crl.object1_id1 = sty.id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
ORDER BY crl.rule_information1;
SELECT count(crl.id)
FROM okc_rule_groups_b crg,
okc_rules_b crl,
okl_strm_type_v sty
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = 'LALEVL'
AND crl.rule_information_category = 'LASLH'
AND crg.dnz_chr_id = chrId
AND crl.object1_id1 = sty.id
AND sty.stream_type_purpose = strmPurpose
ORDER BY crl.rule_information1;
l_select Varchar2(2000);
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle
WHERE cle.dnz_chr_id = chrId
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND kle.fee_type in ('ROLLOVER', 'FINANCED'));
SELECT authoring_org_id,
currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT chr.orig_system_source_code,
chr.start_date,
chr.end_date,
chr.template_yn,
chr.authoring_org_id,
khr.expected_delivery_date,
chr.inv_organization_id,
khr.deal_type,
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 = chrId
AND khr.pdt_id = pdt.id(+);
SELECT crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information4,
crl.rule_information5,
crl.rule_information6,
crl.rule_information10,
crl.rule_information13,
crl.rule_information11
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId;
SELECT DISTINCT hl.country country
FROM hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
okl_txl_itm_insts iti,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_ib
WHERE cle_ib.dnz_chr_id = chrId
AND cle_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND iti.kle_id = cle_ib.id
AND iti.object_id1_new = hpsu.party_site_use_id
AND iti.object_id2_new = '#'
AND hpsu.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id;
SELECT id styid
FROM okl_strm_type_tl
WHERE LANGUAGE = 'US'
AND NAME = strmName;
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.date_terminated,
kle.residual_value,
kle.date_delivery_expected,
kle.orig_system_id1 old_line_id,
kle.amount,
kle.price_negotiated,
kle.start_date,
kle.end_date,
kle.orig_system_id1,
kle.fee_type,
kle.initial_direct_cost,
tl.item_description,
tl.name,
sts.ste_code,
--Added for Pricing Enhancements
kle.tradein_amount,
kle.date_funding_expected,
-- Added for Down Payment Enhancement
kle.capital_reduction,
kle.capitalize_down_payment_yn
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 kle_sub.amount, kle_sub.SUBSIDY_OVERRIDE_AMOUNT
FROM okl_subsidies_b subb,
okc_k_lines_b cle_fin,
okc_line_styles_b lse_fin,
okc_k_lines_b cle_sub,
okl_k_lines kle_sub,
okc_line_styles_b lse_sub
WHERE cle_fin.id = p_cle_id
AND cle_fin.dnz_chr_id = p_chr_id
AND cle_sub.id = kle_sub.id
AND cle_sub.lse_id = lse_sub.id
AND lse_sub.lty_code = 'SUBSIDY'
AND subb.id = kle_sub.subsidy_id
AND cle_fin.id = cle_sub.cle_id
AND cle_fin.lse_id = lse_fin.id
AND lse_fin.lty_code = 'FREE_FORM1'
AND subb.accounting_method_code = 'NET';
SELECT trns.value,
books.book_type_code
FROM okl_sgn_translations trns,
fa_book_controls books
WHERE trns.jtot_object1_code = 'FA_BOOK_CONTROLS'
AND trns.object1_id1 = books.book_type_code
AND books.book_type_code = Book
AND trns.sgn_code = 'STMP';
SELECT crl.id slh_id,
crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
ORDER BY crl.rule_information1;
SELECT tl.name name,
stm.stream_type_purpose stream_type_purpose,
stm.stream_type_class stream_type_class,
tl.description alloc_basis,
stm.capitalize_yn capitalize_yn,
stm.periodic_yn periodic_yn
FROM okl_strm_type_b stm,
okl_strm_type_tl tl
WHERE tl.id = stm.id
AND tl.language = 'US'
AND stm.id = styid;
SELECT crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crl.object2_id1 = rlgpId
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT PRIMARY_STY_ID
FROM OKL_STRM_TMPT_LINES_UV STL
WHERE STL.PRIMARY_YN = 'Y'
AND STL.PDT_ID = l_pdt_id
AND (STL.START_DATE <= l_contract_start_date)
AND (STL.END_DATE >= l_contract_start_date OR STL.END_DATE IS NULL)
AND PRIMARY_STY_PURPOSE = 'RESIDUAL_VALUE';
SELECT --cle.id new_cle_id
cle.orig_system_id1
FROM okc_k_lines_b cle
,okc_k_lines_b ole
WHERE cle.dnz_chr_id=cp_rbk_chr_id
and cle.id = cp_ole_id
and ole.lse_id = cle.lse_id
and ole.id=cle.orig_system_id1;
l_ast_dtl_tbl.delete;
select crl.id
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and crl.RULE_INFORMATION2 is null
and crg.dnz_chr_id = chrId
and crg.cle_id = cleId;
select crl.id
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and nvl(crl.RULE_INFORMATION1, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION2, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION3, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION6, 'XXX' ) = 'XXX'
and crg.dnz_chr_id = chrId;
OKL_RULE_PUB.delete_rule(
p_api_version => l_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_rulv_rec => l_rulv_rec );
delete_working_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_chr_id => p_chr_id);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_ERROR);
select crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and crg.dnz_chr_id = chrId
and crg.cle_id = cleId
order by crl.RULE_INFORMATION1;
select crl.id
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and nvl(crl.RULE_INFORMATION1, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION2, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION3, 'XXX' ) = 'XXX'
and nvl(crl.RULE_INFORMATION6, 'XXX' ) = 'XXX'
and crg.dnz_chr_id = chrId
and nvl(crg.cle_id, -1) = cleId;
OKL_RULE_PUB.delete_rule(
p_api_version => l_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_rulv_rec => l_rulv_rec );
OKL_RULE_PUB.delete_rule(
p_api_version => l_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_rulv_rec => l_rulv_rec );
Select max(transaction_number)+1 into temp
From okl_streams;
okl_contract_status_pub.update_contract_status(
l_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
'COMPLETE',
p_chr_id );
delete_working_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_chr_id => p_khr_id);
/* Commented code for Bulk update by HKPATEL -02/28/2005
l_stmv_tbl(i).id := l_strms_rec.STRM_ID;
UPDATE OKL_STREAMS
SET say_code = 'CURR',
active_yn = 'Y',
date_current = sysdate
WHERE ID = l_id_tbl(i);
write_to_log('Streams updated succesfully');
/* Commented for Bulk update by HKPATEL -02/28/2004
If ( l_stmv_tbl.COUNT > 0 ) Then
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);
okl_contract_status_pub.update_contract_status(
l_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
'COMPLETE',
p_khr_id );
write_to_log('After the call to update_contract_status, return status is :'||x_return_status);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
p_api_version => l_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_khr_id => p_khr_id,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
SELECT cle.id
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.lse_id = lse.id
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED')
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND kle.fee_type = G_FEE_TYPE;
SELECT COUNT(*)
FROM okc_rules_b slh,
okc_rule_groups_b rgp,
okl_k_lines kle
WHERE rgp.dnz_chr_id = p_khr_id
AND kle.id = p_kle_id
AND rgp.rgd_code= G_RGD_CODE
AND rgp.id = slh.rgp_id
AND kle.id = rgp.cle_id
AND kle.fee_type = G_FEE_TYPE
AND slh.rule_information_category = G_RUL_SLH_CODE;
SELECT cle.id line_id
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_khr_id
AND cle.lse_id = lse.id
AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED')
AND lse.lty_code IN ('FREE_FORM1','FEE','SOLD_SERVICE');
SELECT rul.id slh_id
FROM okc_rule_groups_b rgp,
okc_rules_b rul
WHERE rul.rgp_id = rgp.id
AND rgp.rgd_code = G_RGD_CODE
AND rul.rule_information_category = G_RUL_SLH_CODE
AND rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_cle_id;
SELECT FND_DATE.canonical_to_date(sll.rule_information2) start_date,
/* DECODE(SLL.rule_information7,
NULL,(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),to_number(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12))-1),
TO_NUMBER(SLL.rule_information7),FND_DATE.canonical_to_date(sll.rule_information2)+SLL.rule_information7-1,
(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),to_number(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12))-1)) end_date,
*/
-- Bug 5362566: Start
OKL_LA_PAYMENTS_PVT.get_display_end_date(
sll.RULE_INFORMATION2, sll.RULE_INFORMATION7, sll.OBJECT1_ID1, sll.RULE_INFORMATION3,
okl_la_payments_pvt.get_start_day( sll.id,sll.dnz_chr_id,sll.rgp_id,sll.object2_id1,sll.RULE_INFORMATION2)
,(SELECT end_date FROM okc_k_headers_b WHERE id = rgp.dnz_chr_id ) --Bug#5441811
)
END_DATE,
-- Bug 5362566: End
-- Start of BAKUCHIB modification bug#3121708
FND_NUMBER.canonical_to_number(NVL(sll.rule_information6,sll.rule_information8)) amount,
-- End of BAKUCHIB modification bug#3121708
sll.rule_information13 rate,
nvl(sll.rule_information10,'Y') arrears_yn,
sll.rule_information5 structure
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_cle_id
AND slh.id = p_slh_id
AND rgp.rgd_code = G_RGD_CODE
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = G_RUL_SLH_CODE
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = G_RUL_SLL_CODE
--Added by kthiruva for Bug 5130703
AND slh.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
ORDER BY FND_DATE.canonical_to_date(sll.rule_information2);
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM okc_k_headers_b
WHERE id = p_khr_id);
start_date okc_rules_b.last_update_date%TYPE := okl_api.G_MISS_DATE,
end_date okc_rules_b.last_update_date%TYPE := okl_api.G_MISS_DATE);
lt_sll_date_tbl_type.DELETE;
SELECT kle_rv.id asset_line_id,
kle_rv.oec oec,
cle_so.START_DATE
FROM okc_k_headers_b chr_rv,
okc_line_styles_b lse_rv,
okl_k_lines kle_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
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.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 = 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_rv.id = kle_rv.id;
SELECT kle_rv.id asset_line_id,
kle_rv.oec oec,
kle_rv.residual_percentage residual_percent,
cle_so.start_date
FROM okc_k_headers_b chr_rv,
okc_line_styles_b lse_rv,
okl_k_lines kle_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
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.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 = 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 = cle_rv.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_rv.id = kle_rv.id;
Select SCS_CODE,
START_DATE
From okc_K_headers_b chr
Where chr.id = p_khr_id;
Select SCS_CODE,
START_DATE
From okc_K_headers_b chr
Where chr.id = p_khr_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,
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 = chrId
AND khr.pdt_id = pdt.id(+);
SELECT crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information4,
crl.rule_information5,
crl.rule_information6,
crl.rule_information10,
crl.rule_information13,
crl.rule_information11
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId;
SELECT DISTINCT hl.country country
FROM hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
okl_txl_itm_insts iti,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_ib
WHERE cle_ib.dnz_chr_id = chrId
AND cle_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND iti.kle_id = cle_ib.id
AND iti.object_id1_new = hpsu.party_site_use_id
AND iti.object_id2_new = '#'
AND hpsu.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id;
SELECT kle.id,
kle.oec,
kle.residual_code,
kle.capital_amount,
kle.delivered_date,
kle.date_funding_required,
kle.residual_grnty_amount,
kle.residual_code guarantee_type,
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,
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
--Start of bug#3121708 modification BAKUCHIB
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
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,
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 = 'SO_PAYMENT'
AND kle.id = p_cle_id
AND tl.id = kle.id
AND tl.language = userenv('LANG')
AND kle.dnz_chr_id = p_chr_id
AND sts.code = kle.sts_code
--Start of bug#3121708 modification BAKUCHIB
AND sts.ste_code not in ('HOLD', 'EXPIRED', 'CANCELLED');
SELECT txl.life_in_months,
txl.corporate_book,
txl.deprn_method,
txl.in_service_date,
txl.salvage_value,
txl.percent_salvage_value,
txl.depreciation_cost,
mth.id1,
ct.prorate_convention_code
FROM okl_txl_assets_b txl,
okx_asst_dep_methods_v mth,
okx_ast_ct_bk_dfs_v ct
WHERE mth.method_code = txl.deprn_method
AND mth.life_in_months = txl.life_in_months
AND ct.category_id = txl.depreciation_id
AND ct.book_type_code = txl.corporate_book
AND txl.asset_number = ass
-- Start of Bug#3388812 Modification - BAKUCHIB
AND txl.in_service_date BETWEEN ct.start_dpis AND NVL(ct.end_dpis,txl.in_service_date);
SELECT txd.cost,
txd.deprn_method_tax,
txd.life_in_months_tax,
txd.salvage_value,
txd.tax_book,
mth.id1
FROM okl_txd_assets_v txd,
okx_asst_dep_methods_v mth
WHERE mth.method_code = txd.deprn_method_tax
AND mth.life_in_months = txd.life_in_months_tax
AND txd.asset_number = ass;
SELECT trns.value,
books.book_type_code
FROM okl_sgn_translations trns,
fa_book_controls books
WHERE trns.jtot_object1_code = 'FA_BOOK_CONTROLS'
AND trns.object1_id1 = books.book_type_code
AND trns.sgn_code = 'STMP'
AND books.book_type_code = Book;
SELECT id styid
FROM okl_strm_type_tl
WHERE LANGUAGE = 'US'
AND NAME = strmName;
SELECT tl.name name,
stm.stream_type_class stream_type_class,
stm.stream_type_purpose,
tl.description alloc_basis,
stm.capitalize_yn capitalize_yn,
stm.periodic_yn periodic_yn
FROM okl_strm_type_b stm,
okl_strm_type_tl tl
WHERE tl.id = stm.id
AND tl.language = 'US'
AND stm.id = styid;
SELECT crl.id slh_id,
crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
ORDER BY crl.rule_information1;
SELECT crl.object1_id1,
crl.rule_information1,
crl.rule_information2,
crl.rule_information3,
crl.rule_information5,
crl.rule_information6,
crl.rule_information7,
crl.rule_information8,
crl.rule_information13,
crl.rule_information10
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.id
AND crl.object2_id1 = rlgpId
AND crg.rgd_code = rgcode
AND crl.rule_information_category = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId
ORDER BY FND_DATE.canonical_to_date(crl.rule_information2);
SELECT DISTINCT(nvl(crl.rule_information5,-1)) structure
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 = chrId;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_k_lines kle
WHERE cle.dnz_chr_id = chrId
AND cle.lse_id = lse.id
AND lse.lty_code = 'FEE'
AND cle.id = kle.id
AND kle.fee_type in ('ROLLOVER', 'FINANCED'));
SELECT authoring_org_id,
currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT NVL(kle.amount, 0) total_cap_fee
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_k_items cim,
okl_strm_type_b sty
WHERE cle.dnz_chr_id = p_chr_id
AND cle.id = kle.id
AND cle.id = cim.cle_id
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND cim.object1_id1 = to_char(sty.id)
AND sty.capitalize_yn = 'Y';
SELECT chrv.end_date chr_end_date,
MAX(FND_DATE.canonical_to_date(sll.rule_information2)) pay_start_date,
MAX(DECODE(SLL.rule_information7,
NULL,(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),to_number(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12))-1),
TO_NUMBER(SLL.rule_information7),FND_DATE.canonical_to_date(sll.rule_information2)+SLL.rule_information7-1,
(ADD_MONTHS(FND_DATE.canonical_to_date(sll.rule_information2),to_number(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12))-1))) pay_end_date
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okc_k_headers_b chrv
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND slh.id = p_slh_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 chrv.id = rgp.dnz_chr_id
GROUP BY chrv.end_date;
SELECT
--Added by srsreeni for bug6076113
-- rate_params.days_in_a_year_code,
-- DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code
DECODE(rate_params.days_in_a_month_code,'30','360',rate_params.days_in_a_month_code) days_in_a_month_code,
rate_params.days_in_a_year_code
--srsreeni bug6076113 ends
FROM okl_k_rate_params rate_params
WHERE khr_id = p_chr_id;
SELECT DECODE(gts.days_in_month_code,'30','360',gts.days_in_month_code) days_in_month_code,
gts.days_in_yr_code
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT nvl(id,-1)
INTO styid
FROM okl_strm_type_tl
WHERE LANGUAGE = 'US'
AND name = 'RESIDUAL VALUE INSURANCE PREMIUM';