[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PREMIUM , IPF_CODE, DATE_TO , DATE_FROM
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.kle_id = p_contract_line_id
AND IPYB.khr_id = p_contract_id ;
SELECT COUNT(*)
FROM okl_strm_elements STRE, OKL_STREAMS STR
WHERE STR.ID = STRE.STM_ID
AND STR.STY_ID = p_sty_id
AND STRE.DATE_BILLED IS NOT NULL
AND STR.KHR_ID = p_contract_id
AND STR.KLE_ID = p_contract_line_id;
SELECT ID
FROM OKL_STRM_TYPE_TL
WHERE NAME = 'INSURANCE RECEIVABLE'
AND LANGUAGE = 'US';
SELECT IPF_CODE, DATE_TO , DATE_FROM --, CANCELLATION_DATE --++Effective DatedTermination ++---
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.kle_id = p_contract_line_id
AND IPYB.khr_id = p_contract_id ;
SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
--okl_strm_type_tl sty,
okl_strm_type_v sty,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'RENT'
AND sty.stream_type_purpose = 'RENT'
--AND sty.LANGUAGE = 'US'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND NVL( str.purpose_code, 'XXXX' ) = 'XXXX'
AND str.khr_id = chrId
AND str.kle_id = kle.id
AND kle.chr_id = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
GROUP BY str.link_hist_stream_id;
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele
WHERE stm_id = p_stm_id
AND date_billed IS NOT NULL;
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
--okl_strm_type_tl sty
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'RENT'
AND sty.stream_type_purpose = 'RENT'
--AND sty.LANGUAGE = 'US'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND NVL( str.purpose_code, 'XXXX' ) = 'XXXX'
AND str.khr_id = chrId
AND NVL(str.kle_id, -1) = -1;
SELECT NVL(SUM(sele.amount),0) INTO l_income
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'UNEARNED INCOME'
AND str.khr_id = p_chr_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(kle.residual_value,0) Value,
NVL(kle.residual_percentage,0) Percent
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.ID = lineId
AND KLE.DNZ_CHR_ID = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(kle.residual_value,0) Value,
NVL(kle.residual_percentage,0) Percent
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.ID = lineId
AND KLE.DNZ_CHR_ID = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT END_DATE
FROM okc_k_headers_b
WHERE id = cp_chr_id;
SELECT kle.id lineId
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.DNZ_CHR_ID = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT lse.lty_code
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.id = p_cle_id
AND cle.lse_id = lse.id;
SELECT SUM(kle.oec) oec
FROM OKL_K_LINES_V kle,
OKC_K_LINES_V cle,
OKC_K_HEADERS_V CHR
WHERE CHR.id = p_dnz_chr_id
AND CHR.id = cle.dnz_chr_id
AND cle.sts_code NOT IN ( 'ABANDONED', 'TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND cle.id = kle.id;
SELECT SUM(kle.oec) oec
FROM OKL_K_LINES_V kle,
OKC_K_LINES_V cle,
OKC_K_HEADERS_V CHR
WHERE CHR.id = p_dnz_chr_id
AND CHR.id = cle.dnz_chr_id
AND cle.sts_code NOT IN ( 'ABANDONED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND cle.id = kle.id;
SELECT SUM(cle.price_unit * cim.number_of_items) oec
FROM okc_subclass_top_line stl,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okc_k_items_v cim,
okc_k_lines_v cle
WHERE cle.cle_id = p_top_cle_id
AND cle.dnz_chr_id = p_dnz_chr_id
AND cle.id = cim.cle_id
AND cle.dnz_chr_id = cim.dnz_chr_id
AND cle.lse_id = lse1.id
AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = G_FIN_LINE_LTY_CODE
AND lse2.id = stl.lse_id
AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE);
SELECT SUM(cle.price_unit* cim.number_of_items) oec
FROM okc_subclass_top_line stl,
okc_line_styles_b lse3,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okc_k_items_v cim,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.dnz_chr_id = cim.dnz_chr_id
AND cle.id = cim.cle_id
AND cle.lse_id = lse1.id
AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = G_MODEL_LINE_LTY_CODE
AND lse2.lse_parent_id = lse3.id
AND lse3.lty_code = G_FIN_LINE_LTY_CODE
AND lse3.id = stl.lse_id
AND stl.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE)
--AND cle.cle_id IN (SELECT cle.id
AND exists (SELECT 1 --cle.id
FROM okc_subclass_top_line stlx,
okc_line_styles_b lse2x,
okc_line_styles_b lse1x,
okc_k_lines_b clex
WHERE clex.cle_id = p_top_cle_id
AND clex.dnz_chr_id = p_dnz_chr_id
AND clex.lse_id = lse1x.id
AND lse1x.lty_code = G_MODEL_LINE_LTY_CODE
AND lse1x.lse_parent_id = lse2x.id
AND lse2x.lty_code = G_FIN_LINE_LTY_CODE
AND lse2x.id = stlx.lse_id
AND stlx.scs_code IN (G_LEASE_SCS_CODE,G_LOAN_SCS_CODE)
AND clex.id = cle.cle_id);
SELECT chrb.orig_system_source_code
FROM okc_k_headers_b chrb
where chrb.id = p_chr_id;
SELECT SUM(kle.expected_asset_cost) expected_asset_cost
FROM OKL_K_LINES kle,
OKC_K_LINES_B cleb
WHERE kle.id = cleb.id
AND cleb.dnz_chr_id = p_chr_id
AND cleb.lse_id = 33
AND cleb.sts_code NOT IN ( 'ABANDONED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT SUM(kle.expected_asset_cost) expected_asset_cost
FROM OKL_K_LINES kle,
OKC_K_LINES_B cleb
WHERE kle.id = cleb.id
AND cleb.dnz_chr_id = p_chr_id
AND cleb.lse_id = 33
AND cleb.sts_code NOT IN ( 'ABANDONED', 'TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
Select nvl(kle.expected_asset_cost,0) expected_asset_cost
from okl_k_lines kle
where kle.id = p_cle_id;
SELECT 1
--FROM DUAL
--WHERE EXISTS (SELECT 1
FROM OKC_K_HEADERS_B CHR
WHERE CHR.id = p_dnz_chr_id; --);
SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_iD = p_chr_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(kle.tradein_amount),0) INTO l_tradeIn_value
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_iD = p_chr_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(kle.tradein_amount,0.0) amnt,
kle.dnz_chr_id chrId,
kle.id lneId
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = kLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND kLE.dnz_chr_id = chrID
AND kLE.ID = lineID
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(kle.tradein_amount,0.0) amnt,
kle.dnz_chr_id chrId,
kle.id lneId
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = kLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND kLE.dnz_chr_id = chrID
AND kLE.ID = lineID
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT kle.id
FROM okc_line_styles_b ls,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE ls.id = kle.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = chrId
AND kle.sts_code = sts.code
-- start: cklee: okl.h Sales Quote IA Authoring
AND kle.CAPITALIZE_DOWN_PAYMENT_YN = 'Y'
-- end: cklee: okl.h Sales Quote IA Authoring
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT kle.id
FROM okc_line_styles_b ls,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE ls.id = kle.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = chrId
AND kle.sts_code = sts.code
-- start: cklee: okl.h Sales Quote IA Authoring
AND kle.CAPITALIZE_DOWN_PAYMENT_YN = 'Y'
-- end: cklee: okl.h Sales Quote IA Authoring
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(kle.capital_reduction,0) capital_reduction,
NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
NVL(kle.CAPITALIZE_DOWN_PAYMENT_YN, 'N') CAPITALIZE_DOWN_PAYMENT_YN,
sts.ste_code
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_id = p_chr_id
AND KLE.ID = p_line_id
AND kle.sts_code = sts.code
;
/* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_id = p_chr_id
AND KLE.ID = p_line_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
/* SELECT NVL(kle.capital_reduction,0) INTO l_capred_value
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_id = p_chr_id
AND KLE.ID = p_line_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
/* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_id = p_chr_id
AND KLE.ID = p_line_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
/* SELECT NVL(kle.capital_reduction_percent,0) INTO l_capred_percent
FROM OKC_LINE_STYLES_B LS,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.dnz_chr_id = p_chr_id
AND KLE.ID = p_line_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND cim.object1_id1 = to_char(kleId)
AND CLEB_COV.sts_code = STSB.code
AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(kle_cov.capital_amount),0) CapAmountLines
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND cim.object1_id1 = to_char(kleId)
AND CLEB_COV.sts_code = STSB.code
AND STSB.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT ID,ORIG_SYSTEM_SOURCE_CODE
FROM OKC_K_HEADERS_B
WHERE ID = p_chr_id;
SELECT kle.id
FROM okc_line_styles_b ls,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE ls.id = kle.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT kle.id
FROM okc_line_styles_b ls,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE ls.id = kle.lse_id
AND ls.lty_code = 'FREE_FORM1'
AND kle.dnz_chr_id = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(kle.capital_amount),0) CapAmountSubLines
FROM OKC_LINE_STYLES_B LS,
okc_k_items cim,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND ls.lty_code = 'LINK_SERV_ASSET'
AND cim.jtot_object1_code = 'OKX_COVASST'
AND kle.id = cim.cle_id
AND cim.object1_id1 = to_char(kleId)
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT sty.capitalize_yn,
sty.name
FROM okl_strm_type_v sty,
okc_k_items cim,
okc_line_styles_b ls,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE cim.cle_id = kle.id
AND ls.id = kle.lse_id
AND ls.lty_code = 'SOLD_SERVICE'
AND cim.object1_id1 = sty.id
AND cim.object1_id2 = '#'
AND kle.id = kleid
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(A.amount),0)
FROM okl_trx_ap_invoices_b A
,okl_txl_ap_inv_lns_all_b B
WHERE A.id = B.tap_id
AND B.khr_id = p_contract_id
AND A.funding_type_code = 'PREFUNDING'
AND A.trx_status_code IN ('APPROVED', 'PROCESSED')
AND A.amount < 0;
SELECT NVL(SUM(A.amount),0)
FROM okl_trx_ap_invoices_b A
,okl_Txl_ap_inv_lns_all_b B
WHERE A.id = B.tap_id
AND B.khr_id = p_contract_id
AND A.funding_type_code = 'PREFUNDING'
AND A.trx_status_code IN ('APPROVED', 'PROCESSED')
AND A.amount > 0;
SELECT NVL(SUM(A.amount),0)
FROM okl_trx_ap_invoices_b A
,okl_txl_ap_inv_lns_all_b B
WHERE A.id = B.TAP_ID
AND A.khr_id = p_contract_id
AND A.funding_type_code NOT IN ('SUPPLIER_RETENTION', 'MANUAL_DISB')
AND A.trx_status_code IN ('APPROVED', 'PROCESSED');
SELECT NVL(SUM(B.amount),0)
FROM okl_trx_ap_invoices_b A,
okl_txl_ap_inv_lns_all_b B
WHERE A.id = B.tap_id
AND B.khr_id = p_contract_id
AND A.trx_status_code IN ('APPROVED', 'PROCESSED')
AND A.funding_type_code = 'SUPPLIER_RETENTION';
SELECT NVL(SUM(A.amount),0)
FROM OKL_K_LINES_FULL_V A
WHERE A.dnz_chr_id = p_contract_id
AND A.credit_nature = 'NEW'
AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
;
SELECT NVL(SUM(A.amount),0)
FROM OKL_K_LINES_FULL_V A
WHERE A.dnz_chr_id = p_contract_id
AND A.credit_nature = 'ADD'
AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
;
SELECT NVL(SUM(A.amount),0)
FROM OKL_K_LINES_FULL_V A
WHERE A.dnz_chr_id = p_contract_id
AND A.credit_nature = 'REDUCE'
AND NVL(TRUNC(A.start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) -- fixed trunc issues
;
SELECT days_in_a_month_code
FROM OKL_K_RATE_PARAMS
WHERE khr_id = p_khr_id
AND parameter_type_code = 'ACTUAL'
AND effective_to_date IS NULL;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_khr_id;
SELECT days_in_a_year_code
FROM OKL_K_RATE_PARAMS
WHERE khr_id = p_khr_id
AND parameter_type_code = 'ACTUAL'
AND effective_to_date IS NULL;
SELECT
rulb2.RULE_INFORMATION10 arrears_yn
FROM okc_k_lines_b cleb,
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id IS NULL
AND rgpb.dnz_chr_id = cleb.dnz_chr_id
AND rgpb.cle_id = cleb.id
AND cleb.dnz_chr_id = p_khr_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL';
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id;
SELECT days_in_a_year_code
FROM OKL_K_RATE_PARAMS
WHERE khr_id = p_ctr_id
AND parameter_type_code = 'ACTUAL'
AND effective_to_date IS NULL;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id;
SELECT idv.value
FROM OKL_K_RATE_PARAMS okl,
OKL_INDEX_VALUES idv,
OKL_INDICES idx
WHERE okl.khr_id = p_ctr_id
AND okl.parameter_type_code = 'ACTUAL'
AND okl.effective_to_date IS NULL
AND okl.interest_index_id = idx.id
AND idx.ID = idv.idx_id
AND idv.datetime_valid = (SELECT MAX(idv.datetime_valid)
FROM OKL_INDEX_VALUES idv ,
OKL_INDICES idx,
OKL_K_RATE_PARAMS rate
WHERE rate.khr_id = p_ctr_id
AND rate.parameter_type_code = 'ACTUAL'
AND rate.effective_to_date IS NULL
AND rate.interest_index_id = idx.id
AND idx.id = idv.idx_id
AND idv.datetime_valid <= p_accrual_date);
SELECT adder_rate
FROM OKL_K_RATE_PARAMS
WHERE khr_id = p_ctr_id
AND parameter_type_code = 'ACTUAL'
AND effective_to_date IS NULL;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_khr_id;
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_ctr_id;
SELECT SUM(ste.amount)
FROM OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste,
OKL_STRM_TYPE_B sty
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = sty.id
AND sty.id = p_sty_id
AND stm.active_yn = 'Y'
AND stm.say_code ='CURR'
AND ste.stm_id = stm.id
AND ste.stream_element_date >= p_date
AND ste.date_billed IS NULL;
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_ctr_id;
SELECT SUM(ste.amount)
FROM OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste,
OKL_STRM_TYPE_B sty
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = sty.id
AND sty.id = p_lease_inc_sty_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND ste.stream_element_date >=
(SELECT TRUNC(MIN(ste.stream_element_date),'MM')
FROM OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste,
OKL_STRM_TYPE_B sty
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = sty.id
AND sty.id = l_rent_sty_id
AND stm.active_yn = 'Y'
AND ste.stm_id = stm.id
AND ste.stream_element_date >= p_date
AND ste.date_billed IS NULL)
AND ste.stream_element_date <=
(SELECT LAST_DAY(MAX(ste.stream_element_date))
FROM OKL_STREAMS stm,
OKL_STRM_ELEMENTS ste,
OKL_STRM_TYPE_B sty
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = sty.id
AND sty.id = l_rent_sty_id
AND stm.active_yn = 'Y'
AND ste.stm_id = stm.id
AND ste.stream_element_date >= p_date
AND ste.date_billed IS NULL);
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_ctr_id;
SELECT SUM(NVL(RESIDUAL_VALUE,0)) - SUM(NVL(RESIDUAL_GRNTY_AMOUNT, 0))
FROM OKL_K_LINES_FULL_V
WHERE DNZ_CHR_ID = p_ctr_id;
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_ctr_id;
SELECT SUM(ste.amount)
FROM OKL_STRM_ELEMENTS ste,
OKL_STRM_TYPE_B sty,
OKL_STREAMS stm
WHERE stm.khr_id = p_ctr_id
AND stm.sty_id = sty.id
AND sty.id = p_subsidy_inc_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND stm.id = ste.stm_id
AND ste.accrued_yn IS NULL
AND ste.stream_element_date <= p_date;
SELECT contract_number
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_ctr_id;
SELECT SUM(trx.amount)
FROM OKL_TRX_CONTRACTS trx,
OKL_TRX_TYPES_V try,
OKL_TXL_CNTRCT_LNS txl
WHERE trx.khr_id = p_ctr_id
--Fixed Bug 5707866 SLA Uptake Project by nikshah, changed tsu_code to PROCESSED from ENTERED
AND trx.tsu_code ='PROCESSED'
AND trx.try_id = try.id
AND trx.representation_type = 'PRIMARY' -- MGAAP OTHER 7263041
AND try.name = 'Accrual'
AND trx.date_transaction_occurred <= p_date
AND trx.id = txl.tcn_id
AND txl.sty_id = p_sty_id;
SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
FROM OKC_K_LINES_B KLE_FA,
OKC_LINE_STYLES_B LSE_FA,
OKC_K_LINES_B KLE_IL,
OKC_LINE_STYLES_B LSE_IL,
OKC_K_LINES_B KLE_IB,
OKC_LINE_STYLES_B LSE_IB,
OKC_K_ITEMS ITE,
CSI_ITEM_INSTANCES CII,
HZ_PARTY_SITES PSI,
HZ_LOCATIONS LOC,
-- HZ_PARTIES PAR,
HZ_PARTY_SITE_USES PSU,
HZ_LOC_ASSIGNMENTS LOA
WHERE kle_fa.id = cp_fin_asset_cle_id
AND lse_fa.id = kle_fa.lse_id
AND lse_fa.lty_code = 'FREE_FORM1'
AND kle_il.cle_id = kle_fa.id
AND lse_il.id = kle_il.lse_id
AND lse_il.lty_code = 'FREE_FORM2'
AND kle_ib.cle_id = kle_il.id
AND lse_ib.id = kle_ib.lse_id
AND lse_ib.lty_code = 'INST_ITEM'
AND ite.cle_id = kle_ib.id
AND ite.jtot_object1_code = 'OKX_IB_ITEM'
AND cii.instance_id = ite.object1_id1
AND cii.install_location_type_code = 'HZ_PARTY_SITES'
AND psi.party_site_id = cii.install_location_id
AND loc.location_id = psi.location_id
-- AND par.party_id = psi.party_id
AND psu.party_site_id = psi.party_site_id
AND psu.site_use_type = 'INSTALL_AT'
AND loc.location_id = loa.location_id
UNION
SELECT DISTINCT LOA.loc_id location_id, LOC.postal_code -- 3682465
FROM OKC_K_LINES_B KLE_FA,
OKC_LINE_STYLES_B LSE_FA,
OKC_K_LINES_B KLE_IL,
OKC_LINE_STYLES_B LSE_IL,
OKC_K_LINES_B KLE_IB,
OKC_LINE_STYLES_B LSE_IB,
OKC_K_ITEMS ITE,
CSI_ITEM_INSTANCES CII,
--HZ_PARTY_SITES PSI,
HZ_LOCATIONS LOC,
--HZ_PARTIES PAR,
--HZ_PARTY_SITE_USES PSU,
HZ_LOC_ASSIGNMENTS LOA
WHERE kle_fa.id = cp_fin_asset_cle_id
AND lse_fa.id = kle_fa.lse_id
AND lse_fa.lty_code = 'FREE_FORM1'
AND kle_il.cle_id = kle_fa.id
AND lse_il.id = kle_il.lse_id
AND lse_il.lty_code = 'FREE_FORM2'
AND kle_ib.cle_id = kle_il.id
AND lse_ib.id = kle_ib.lse_id
AND lse_ib.lty_code = 'INST_ITEM'
AND ite.cle_id = kle_ib.id
AND ite.jtot_object1_code = 'OKX_IB_ITEM'
AND cii.instance_id = ite.object1_id1
AND cii.install_location_type_code = 'HZ_LOCATIONS'
AND loc.location_id = cii.install_location_id
--AND loc.location_id = psi.location_id
--AND par.party_id = psi.party_id
--AND psu.party_site_id = psi.party_site_id
--AND psu.site_use_type = 'INSTALL_AT'
AND loc.location_id = loa.location_id
AND EXISTS (SELECT 1
FROM HZ_PARTY_SITES psi,
HZ_PARTY_SITE_USES psu
WHERE psi.location_id = loc.location_id
AND psu.party_site_id = psi.party_site_id
AND psu.site_use_type = 'INSTALL_AT');
SELECT loc_assign.loc_id location_id, LOC.postal_code -- 3682465
FROM HZ_PARTY_SITES party_site,
HZ_LOC_ASSIGNMENTS loc_assign,
HZ_LOCATIONS loc,
HZ_CUST_ACCT_SITES_ALL acct_site,
HZ_PARTIES party,
HZ_CUST_ACCOUNTS cust_acct,
HZ_CUST_SITE_USES cust_site_uses
WHERE acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND loc.location_id = loc_assign.location_id
AND acct_site.cust_acct_site_id = cust_site_uses.cust_acct_site_id
AND party.party_id = cust_acct.party_id
AND cust_site_uses.site_use_id = p_bill_to_site_use_id
AND cust_acct.cust_account_id = p_cust_acct_id;
SELECT l.name
FROM okc_k_lines_v l
WHERE l.id = cp_cle_id;
SELECT h.contract_number
FROM okc_k_headers_v h
WHERE h.id = cp_chr_id;
SELECT trunc(qte.date_effective_from) date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
SELECT SYSDATE INTO l_sysdate FROM DUAL;
SELECT SUM (NVL (ste.amount, 0)) amount_due
FROM okl_streams stm,
okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND ste.date_billed IS NULL
AND NVL (ste.amount, 0) <> 0
-- SECHAWLA 04-MAR-03 Added the following 3 conditions to restrict the unbilled receivables calculation to only
-- billable streams
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM (NVL (ste.amount, 0)) amount_due
FROM okl_streams stm,
okl_strm_type_b sty, -- SECHAWLA 04-MAR-03 Added this table to get the billable_yn flag
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND ste.date_billed IS NULL
AND NVL (ste.amount, 0) <> 0
-- SECHAWLA 04-MAR-03 Added the following 3 conditions to restrict the unbilled receivables calculation to only
-- billable streams
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y';
SELECT SUM (NVL (ste.amount, 0)) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'N' -- reporting strems are inactive
AND stm.say_code = 'CURR' -- reporting streams are current
AND ste.stm_id = stm.id
AND ste.date_billed IS NULL -- reporting streams never get billed
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y' -- reporting streams are billable
-- AND sty.capitalize_yn = 'N'
AND stm.purpose_code = 'REPORT'
AND ste.STREAM_ELEMENT_DATE > cp_trx_date;
SELECT a.start_date, a.contract_number, b.pdt_id
FROM okc_k_headers_b a, okl_k_headers b
WHERE a.id = b.id
AND a.id = cp_khr_id;
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.id = p_sty_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id;
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.id = p_sty_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR' -- reporting streams are current
AND stm.active_yn = 'N' -- reporting strems are inactive
AND stm.purpose_code IS NULL
AND sel.date_billed IS NULL -- reporting streams never get billed
--AND sty.billable_yn = 'N' -- PRE-TAX streams are not billable
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id
AND stm.purpose_code = 'REPORT'
AND sel.STREAM_ELEMENT_DATE > cp_trx_date;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.stream_type_purpose) = 'SECURITY_DEPOSIT'
AND str.say_code = 'CURR'
--multigaap changes
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
--end multigaap changes
AND str.khr_id = p_contract_id;
SELECT NVL(SUM(RESIDUAL_VALUE),0)
FROM okl_k_lines_full_v
WHERE dnz_chr_id= p_contract_id
AND sts_code <> 'TERMINATED';
SELECT NVL(SUM(cs.amount),0)
FROM okl_streams_v asv,okl_strm_type_v bs,
okl_strm_elements_v cs,
okl_streams str,
okl_strm_type_v sty
WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'RESIDUAL VALUE'
AND str.say_code = 'CURR'
--multigaap changes
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE is NULL --end multigaap changes AND
cs.stream_element_date >= SYSDATE AND
asv.khr_id = p_contract_id; */
SELECT NVL(RESIDUAL_VALUE,0)
FROM okl_k_lines
WHERE id = p_contract_line_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.stream_type_purpose) = 'RENT'
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
AND SELE.DATE_BILLED IS NULL
AND SELE.STREAM_ELEMENT_DATE > SYSDATE
AND str.khr_id = p_contract_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'RENT' --sechawla 04-dec-08 : remoevd
AND UPPER(sty.stream_type_purpose) = 'RENT' --sechawla 04-dec-08 : added
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
AND SELE.DATE_BILLED IS NULL
AND SELE.STREAM_ELEMENT_DATE > SYSDATE
AND str.khr_id = p_contract_id --sechawla 04-dec-08 : added
AND str.kle_id = p_contract_line_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
--okl_streams str, MGAAP 7263041
okl_streams_rep_v str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME'
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND (STR.PURPOSE_CODE IS NULL OR STR.PURPOSE_CODE='REPORT')
AND (sele.accrued_yn IS NULL OR sele.accrued_yn = 'N')
AND SELE.STREAM_ELEMENT_DATE > SYSDATE
AND str.khr_id = p_contract_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'UNEARNED INCOME'
AND str.say_code = 'CURR'
--multigaap changes
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
--end multigaap changes
AND str.khr_id = p_contract_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele,
--okl_streams str, MGAAP 7263041
okl_streams_rep_v str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRE-TAX INCOME'
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
AND (sele.accrued_yn IS NULL OR sele.accrued_yn = 'N')
AND SELE.STREAM_ELEMENT_DATE > SYSDATE
AND str.kle_id = p_contract_line_id;
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.name = 'PRE-TAX INCOME'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.kle_id = p_contract_line_id
AND sel.stm_id = stm.id
AND sel.stream_element_date > SYSDATE;
SELECT line.id parent_line_id
FROM okc_k_lines_b line
,okc_line_styles_v lse
WHERE line.lse_id=lse.id
AND lse.lty_code= 'FREE_FORM1'
AND line.sts_code <> 'ABANDONED'
AND dnz_chr_id = p_contract_id;
SELECT CORPORATE_BOOK, ASSET_ID
FROM OKX_ASSET_LINES_V
WHERE OKX_ASSET_LINES_V.PARENT_LINE_ID = p_contract_line_id;
SELECT NVL(SUM(deprn_amount), 0) deprn_amount
FROM OKX_AST_DPRTNS_V
WHERE Asset_id = p_asset_id
AND book_type_code = p_corporate_book_code
AND status = 'A'
AND NVL(start_date_active,SYSDATE) <= SYSDATE
AND NVL(end_date_active,SYSDATE + 1) > SYSDATE;
SELECT start_date FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT NVL(cs.amount,0)
FROM okl_streams_v asv,okl_strm_type_v bs,
okl_strm_elements_v cs,
okl_streams str,
okl_strm_type_v sty,
okc_k_headers_v okh
WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL BALANCE'
AND str.say_code = 'CURR'
--multigaap changes
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE is NULL
--end multigaap changes
AND cs.stream_element_date >= SYSDATE
AND cs.stream_element_date BETWEEN okh.start_date AND okh.end_date
AND asv.khr_id = okh.id
AND asv.khr_id = p_contract_id;
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.khr_id = p_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date =
( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.khr_id = p_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date <= SYSDATE);
SELECT SUM(APS.AMOUNT_DUE_REMAINING)
FROM
AR_PAYMENT_SCHEDULES_ALL APS,
/*
16-Aug-2007, ankushar Bug# 5499193
start changes, modified the cursor to replace reference to okl_cnsld_ar_strms_b
*/
okl_bpd_tld_ar_lines_v LSM,
-- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
/* 16-Aug-2007 ankushar end changes */
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LSE
WHERE
LSM.KLE_ID = p_contract_line_id
AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND APS.STATUS = 'OP'
AND APS.CLASS IN ('INV')
AND LSM.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET';
SELECT SUM(APS.AMOUNT_DUE_REMAINING)
FROM
AR_PAYMENT_SCHEDULES_ALL APS,
/*
16-Aug-2007, ankushar Bug# 5499193
start changes, modified the cursor to replace reference to okl_cnsld_ar_strms_b
*/
okl_bpd_tld_ar_lines_v LSM,
-- OKC_K_HEADERS_B CHR, commenting unused table, ankushar Bug# 5499193
/* 16-Aug-2007 ankushar end changes */
OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LSE
WHERE
LSM.KLE_ID = p_contract_line_id
AND LSM.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND APS.STATUS = 'OP'
AND APS.CLASS IN ('CM')
AND LSM.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET';
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.khr_id = p_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date =
( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.khr_id = p_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date <= p_date); -- rmunjulu EDAT
SELECT NVL(sel.amount,0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.kle_id = p_contract_line_id
AND sel.stm_id = stm.id
AND sel.stream_element_date =
( SELECT NVL(MAX(sel.stream_element_date), SYSDATE)
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.kle_id = p_contract_line_id
AND stm.purpose_code IS NULL
AND sel.stm_id = stm.id
AND sel.stream_element_date <= p_date); -- rmunjulu EDAT
SELECT trunc(qte.date_effective_from) date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
SELECT SYSDATE INTO l_sysdate FROM dual;
SELECT NVL(SUM(NVL(sub_kle.subsidy_override_amount, sub_kle.amount)),0)
FROM okl_subsidies_b subb,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
WHERE subb.id = sub_kle.subsidy_id
AND subb.accounting_method_code = NVL(UPPER(c_accounting_method),subb.accounting_method_code)
AND sub_kle.id = sub_cle.id
AND sub_cle.lse_id = sub_lse.id
AND sub_lse.lty_code = 'SUBSIDY'
AND sub_cle.sts_code <> 'ABANDONED'
AND sub_cle.dnz_chr_id = c_contract_id
AND subb.customer_visible_yn = 'Y'
;
SELECT line.id parent_line_id
FROM okc_k_lines_b line
,okc_line_styles_v lse
WHERE line.lse_id=lse.id
AND lse.lty_code= 'FREE_FORM1'
AND line.sts_code <> 'ABANDONED'
AND dnz_chr_id = p_contract_id;
SELECT CORPORATE_BOOK, ASSET_ID
FROM OKX_ASSET_LINES_V
WHERE OKX_ASSET_LINES_V.PARENT_LINE_ID = p_contract_line_id;
SELECT NVL(SUM(deprn_amount), 0) deprn_amount
FROM OKX_AST_DPRTNS_V
WHERE Asset_id = p_asset_id
AND book_type_code = p_corporate_book_code
AND status = 'A'
AND NVL(start_date_active,SYSDATE) <= SYSDATE
AND NVL(end_date_active,SYSDATE + 1) > SYSDATE;
SELECT start_date FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT sel.amount rent
,sel.stream_element_date due_date
,stm.kle_id
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'RENT'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
AND sel.date_billed IS NULL;
SELECT sel.amount rent
,sel.stream_element_date due_date
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.stream_type_purpose = 'RENT'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code IS NULL
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id
AND sel.date_billed IS NULL;
SELECT rl.id
FROM okc_rule_groups_v rg,
okc_rules_v rl
WHERE rl.rgp_id = rg.id
AND rl.dnz_chr_id = rg.dnz_chr_id
AND rg.cle_id = c_line_id
AND rg.rgd_code = 'LALEVL'
AND rl.rule_information_category = 'LASLH'
AND rl.dnz_chr_id = c_khr_id
AND rl.object1_id1=(SELECT id FROM okl_strm_type_b WHERE code='RENT');
SELECT rl.object1_id1 frequency
FROM okc_rule_groups_v rg,
okc_rules_v rl
WHERE rl.rgp_id = rg.id
AND rl.dnz_chr_id = rg.dnz_chr_id
AND rg.cle_id = c_line_id
AND rg.rgd_code = 'LALEVL'
AND rl.rule_information_category = 'LASLL'
AND rl.dnz_chr_id = c_khr_id
AND rl.object2_id1=c_rent_slh_id
AND ROWNUM = 1;
SELECT implicit_interest_rate
FROM okl_k_headers
WHERE id=c_contract_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id=c_contract_id;
SELECT NVL(SUM(amount),0)
FROM OKL_CURE_PAYMENT_LINES
WHERE chr_id = p_chr_id
AND status = 'CURES_IN_POSSESSION'
AND cured_flag = 'Y';
SELECT NVL(SUM(amount_due_remaining), 0)
FROM okl_bpd_leasing_payment_trx_v
WHERE contract_id = p_chr_id;*/
SELECT NVL(SUM(amount_due_remaining), 0)
FROM okl_bpd_ar_inv_lines_v
WHERE contract_id = p_chr_id;
SELECT SUM(amount)
FROM OKL_cure_payment_lines_v
WHERE chr_id = p_contract_id
AND status = 'CURES_IN_POSSESSION'; */
SELECT 'Y'
FROM OKL_CURE_PAYMENT_LINES
WHERE EXISTS (SELECT 1
FROM OKL_CURE_PAYMENT_LINES
WHERE status = 'CURES_IN_POSSESSION'
AND cured_flag = 'Y'
AND chr_id = p_chr_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_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 NVL( SUM(kle.capitalized_interest), 0.0)
FROM OKC_LINE_STYLES_B LS,
OKL_K_LINES_FULL_V KLE,
okc_statuses_b sts
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.DNZ_CHR_ID = chrId
-- start: cklee 05/18/2004 fixed for bug#3625609
AND KLE.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL( kle.capitalized_interest, 0.0)
FROM OKC_LINE_STYLES_B LS,
OKL_K_LINES_FULL_V KLE
WHERE LS.ID = KLE.LSE_ID
AND LS.LTY_CODE ='FREE_FORM1'
AND KLE.DNZ_CHR_ID = chrId
AND KLE.id = kleId;
SELECT rulb.rule_information2 payment_event
FROM okc_rules_b rulb
,okc_rule_groups_b rgpb
WHERE rgpb.dnz_chr_id = p_khr_id
AND rgpb.chr_id = p_khr_id
AND rgpb.rgd_code = 'LASEIR'
AND rgpb.id = rulb.rgp_id
AND rulb.rule_information_category = 'LASEIR';
SELECT NVL(SUM(selb.amount),0) total_amount
FROM okl_strm_elements selb
,okl_streams stmb
,okl_pool_contents pocb
,okl_strm_type_v styv --ankushar --Bug 6594724
,okc_k_headers_b chrb --ankushar --Bug 6594724
WHERE stmb.khr_id = p_dnz_chr_id
AND stmb.kle_id = p_kle_id
AND stmb.sty_id = p_sty_id
AND selb.stm_id = stmb.id
AND stmb.active_yn = 'Y'
AND stmb.say_code = 'CURR'
AND selb.date_billed IS NULL
AND pocb.kle_id = p_kle_id
AND pocb.sty_id = p_sty_id
-- mvasudev, 03/30/2004
AND pocb.status_Code = 'ACTIVE'
/*
ankushar --Bug 6594724: Unable to terminate Investor Agreement with Residual Streams
Start changes
*/
AND stmb.sty_id = styv.id
AND pocb.khr_id = chrb.id
AND(selb.stream_element_date > SYSDATE
OR
(styv.stream_type_subclass = 'RESIDUAL'
and chrb.STS_CODE IN ('TERMINATED','EXPIRED')
)
)
/* ankushar Bug 6594724
End Changes
*/
-- end, mvasudev, 03/30/2004
AND (selb.stream_element_date BETWEEN pocb.streams_from_date
AND NVL(pocb.streams_to_date,G_FINAL_DATE)
);
SELECT NVL(SUM(amount_due_original - amount_due_remaining),0) total_amount
FROM okl_bpd_leasing_payment_trx_v
WHERE contract_id = p_dnz_chr_id
AND contract_line_id = p_kle_id
AND stream_type_id = p_sty_id
AND amount_due_original <> amount_due_remaining;
SELECT NVL(SUM(selb.amount),0) total_amount
FROM okl_strm_elements selb
,okl_streams stmb
,okl_pool_contents pocb
WHERE stmb.khr_id = p_dnz_chr_id
AND stmb.kle_id = p_kle_id
AND stmb.sty_id = p_sty_id
AND selb.stm_id = stmb.id
AND stmb.active_yn = 'Y'
AND stmb.say_code = 'CURR'
AND selb.date_billed IS NULL
AND pocb.kle_id = p_kle_id
AND pocb.sty_id = p_sty_id
AND pocb.status_code = 'PENDING'
AND selb.stream_element_date > SYSDATE
AND (selb.stream_element_date BETWEEN pocb.streams_from_date
AND NVL(pocb.streams_to_date,G_FINAL_DATE) );
SELECT NVL(SUM(selb.amount),0) total_amount
FROM okl_strm_elements selb
,okl_streams stmb
,okl_strm_type_v styv
,okl_pool_contents pocb
WHERE stmb.source_id = p_chr_id
AND styv.stream_type_purpose = p_sty_purpose
AND stmb.sty_id = styv.id
AND selb.stm_id = stmb.id
AND stmb.active_yn = 'Y'
AND stmb.say_code = 'CURR'
AND pocb.status_code <> 'PENDING'
AND pocb.khr_id = stmb.khr_id
AND pocb.kle_id = stmb.kle_id;
SELECT NVL(SUM(selb.amount),0) total_amount
FROM okl_strm_elements selb
,okl_streams stmb
,okl_strm_type_v styv
,okl_pool_contents pocb
WHERE stmb.source_id = p_chr_id
AND styv.stream_type_purpose = p_sty_purpose
AND stmb.sty_id = styv.id
AND selb.stm_id = stmb.id
AND stmb.active_yn = 'Y'
AND stmb.say_code = 'CURR'
AND pocb.status_code = 'PENDING'
AND pocb.khr_id = stmb.khr_id
AND pocb.kle_id = stmb.kle_id;
SELECT DISTINCT kleb.percent_stake,clet.id
FROM okl_k_lines kleb,
okc_k_lines_b clet,
okc_k_lines_b cles
WHERE kleb.id = cles.id
AND cles.cle_id = clet.id
AND clet.dnz_chr_id = p_chr_id
--AND kleb.sty_id = styb.id
AND kleb.stream_type_subclass = p_sty_subclass;
SELECT NVL(SUM(SELB.AMOUNT),0)
FROM
OKL_STREAMS STMB,
OKL_STRM_ELEMENTS SELB,
OKL_STRM_TYPE_V STYV
WHERE STMB.ID = SELB.STM_ID
AND STMB.STY_ID = STYV.ID
AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
AND STMB.SAY_CODE = 'CURR'
AND STMB.ACTIVE_YN = 'Y'
AND EXISTS (SELECT 1
FROM OKL_POOL_CONTENTS POC,
OKL_POOLS POL,
OKL_STRM_TYPE_V STYS
WHERE POC.POL_ID = POL.ID
AND POC.KHR_ID = STMB.KHR_ID
AND POL.KHR_ID = P_CONTRACT_ID
AND POC.STY_ID = STYS.ID
AND STYS.STREAM_TYPE_SUBCLASS IN ('RENT','LOAN_PAYMENT') --Bug # 6740000 ssdeshpa--For Loan Contracts Addition into the Pool
AND POC.status_code <> Okl_Pool_Pvt.G_POC_STS_PENDING --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
);
SELECT NVL(SUM(SELB.AMOUNT),0)
FROM
OKL_STREAMS STMB,
OKL_STRM_ELEMENTS SELB,
OKL_STRM_TYPE_V STYV
WHERE STMB.ID = SELB.STM_ID
AND STMB.STY_ID = STYV.ID
AND STYV.STREAM_TYPE_PURPOSE IN ('INVESTOR_PRETAX_INCOME','INVESTOR_RENTAL_ACCRUAL','INVESTOR_INTEREST_INCOME')
AND STMB.SAY_CODE = 'CURR'
AND STMB.ACTIVE_YN = 'Y'
AND EXISTS (SELECT 1 FROM OKL_POOL_CONTENTS POC,
OKL_POOLS POL,
OKL_STRM_TYPE_V STYS
WHERE POC.POL_ID = POL.ID
AND POC.KHR_ID = STMB.KHR_ID
AND POL.KHR_ID = P_CONTRACT_ID
AND POC.STY_ID = STYS.ID
AND STYS.STREAM_TYPE_SUBCLASS IN ('RENT','LOAN_PAYMENT') --Bug # 6740000 ssdeshpa--For Loan Contracts Addition into the Pool
AND POC.status_code = Okl_Pool_Pvt.G_POC_STS_PENDING);
SELECT
NVL(SUM(NVL(KLEB.AMOUNT,0)),0)
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB
WHERE
CLEB.ID = KLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'INVESTMENT' AND
CLEB.DNZ_CHR_ID = p_contract_id;
SELECT
NVL(SUM(NVL(KLEB.AMOUNT_STAKE,0)),0)
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB
WHERE
CLEB.ID = KLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'INVESTMENT' AND
CLEB.DNZ_CHR_ID = p_contract_id;
SELECT polb.id
FROM okl_pools polb
WHERE polb.khr_id = p_khr_id;
SELECT DISTINCT kleb.percent_stake,clet.id
FROM okl_k_lines kleb,
okc_k_lines_b clet,
okc_k_lines_b cles
WHERE kleb.id = cles.id
AND cles.cle_id = clet.id
AND clet.dnz_chr_id = p_contract_id
AND kleb.stream_type_subclass = p_sty_subclass;
SELECT
NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
FROM
okl_strm_type_v styv,
okl_streams stmb,
okl_strm_elements selb,
okl_pool_contents pocb
WHERE styv.stream_type_subclass = p_stm_sub_class
AND styv.id = stmb.sty_id
AND stmb.id = selb.stm_id
AND pocb.pol_id = p_pol_id
AND stmb.ID = pocb.STM_ID
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
AND selb.STREAM_ELEMENT_DATE
BETWEEN pocb.STREAMS_FROM_DATE AND NVL(pocb.STREAMS_TO_DATE, G_FINAL_DATE)
AND pocb.status_code <> Okl_Pool_Pvt.G_POC_STS_PENDING ; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
SELECT
NVL(SUM(NVL(selb.AMOUNT,0)),0) AMOUNT
FROM
okl_strm_type_v styv,
okl_streams stmb,
okl_strm_elements selb,
okl_pool_contents pocb
WHERE styv.stream_type_subclass = p_stm_sub_class
AND styv.id = stmb.sty_id
AND stmb.id = selb.stm_id
AND pocb.pol_id = p_pol_id
AND stmb.ID = pocb.STM_ID
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
AND selb.STREAM_ELEMENT_DATE
BETWEEN pocb.STREAMS_FROM_DATE AND NVL(pocb.STREAMS_TO_DATE, G_FINAL_DATE)
AND pocb.status_code = Okl_Pool_Pvt.G_POC_STS_PENDING ;
SELECT
NVL(SUM(NVL(KLEB.initial_direct_cost,0)),0)
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB
WHERE
KLEB.ID = CLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'FEE' AND
KLEB.FEE_TYPE IN ('EXPENSE','MISCELLANEOUS') AND
CLEB.CHR_ID = l_dnz_chr_id AND
CLEB.STS_CODE IN ('APPROVED', 'COMPLETE');
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules aps
, okl_strm_type_v sm
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class ='INV'
AND (aps.due_date + p_grace_days) < SYSDATE
AND NVL(aps.amount_due_remaining, 0) > 0
AND sm.id = ocas.STY_ID
AND sm.name <> 'CURE';*/
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules aps
, okl_strm_type_v sm
WHERE ocas.khr_id = p_contract_id
AND ocas.customer_trx_id = aps.customer_trx_id
AND aps.class ='INV'
AND (aps.due_date + p_grace_days) < SYSDATE
AND NVL(aps.amount_due_remaining, 0) > 0
AND sm.id = ocas.STY_ID
AND sm.name <> 'CURE';
SELECT khr_id INTO l_program_id
FROM okl_k_headers
WHERE id=p_contract_id;
SELECT NVL(SUM(negotiated_amount),0)
FROM okl_cure_amounts
WHERE chr_id =p_contract_id
AND status ='CURESINPROGRESS';
SELECT NVL(SUM(short_fund_amount),0)
FROM okl_cure_amounts
WHERE chr_id =p_contract_id
AND status ='CURESINPROGRESS';
SELECT stsb.ste_code
FROM okc_statuses_b stsb,
okc_k_lines_b cleb
WHERE stsb.code = cleb.sts_code
AND cleb.id = p_cle_id;
SELECT cleb.id
FROM okc_k_lines_b cleb,
okc_statuses_b stsb,
okc_line_styles_b lseb
WHERE cleb.chr_id = p_chr_id
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FREE_FORM1'
AND cleb.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
SELECT cleb.id
FROM okc_k_lines_b cleb,
okc_statuses_b stsb,
okc_line_styles_b lseb
WHERE cleb.chr_id = p_chr_id
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FREE_FORM1'
AND cleb.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED');
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
--okl_strm_type_tl sty,
okl_strm_type_v sty,
okl_K_lines_full_v kle,
okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'AMORTIZED EXPENSE'
AND sty.stream_type_purpose = 'AMORTIZED_FEE_EXPENSE'
--AND sty.LANGUAGE = 'US'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND NVL( str.purpose_code, 'XXXX' ) <> 'REPORT'
AND str.khr_id = chrId
AND NVL(str.kle_id,-9999) = kle.id
AND kle.dnz_chr_id = chrId
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
--okl_strm_type_tl sty
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'AMORTIZED EXPENSE'
AND sty.stream_type_purpose = 'AMORTIZED_FEE_EXPENSE'
--AND sty.LANGUAGE = 'US'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND NVL( str.purpose_code, 'XXXX' ) <> 'REPORT'
AND str.khr_id = chrId
AND NVL(str.kle_id, -9999) = -9999;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'Y'
AND ste.stream_element_date > cp_date -- rmunjulu EDAT
AND pocb.status_Code <> 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code= 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND ste.stream_element_date > cp_date -- gboomina bug 4775555 -- check for disbs after termination
AND pocb.status_Code <> 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'Y'
AND ste.stream_element_date > cp_date
AND pocb.status_Code = 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND ste.stream_element_date > cp_date
AND pocb.status_Code = 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT trunc(qte.date_effective_from) date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
select sysdate into l_sysdate from dual;
SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste,
okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND ste.date_billed IS NULL
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'N'
AND pocb.status_Code <> 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)), 0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste,
okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND ste.date_billed IS NULL
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'N'
AND pocb.status_Code = 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'Y'
AND ste.stream_element_date > cp_date -- rmunjulu EDAT
AND pocb.status_Code <> 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
,okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code= 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND ste.stream_element_date > cp_date -- gboomina bug 4775555 -- check for disbs after termination
AND pocb.status_Code <> 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_due
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste,
okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND sty.billable_yn = 'Y'
AND ste.stream_element_date > cp_date
AND pocb.status_Code = 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT NVL(SUM (NVL (ste.amount, 0)),0) amount_payable
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste,
okl_pool_contents pocb
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = NVL(cp_contract_line_id, stm.kle_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.id = cp_sty_id
AND ste.stream_element_date > cp_date
AND pocb.status_Code = 'PENDING'
AND pocb.khr_id = stm.khr_id
AND pocb.kle_id = stm.kle_id
AND pocb.sty_id = stm.sty_id;
SELECT trunc(qte.date_effective_from) date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
select sysdate into l_sysdate from dual;
SELECT kle_id
FROM OKL_TXL_QUOTE_LINES_B
WHERE QTE_ID = p_quote_id
AND QLT_CODE = 'AMCFIA';
SELECT asset_value net_investment
FROM OKL_TRX_QUOTES_V qte
WHERE qte.id = p_quote_id;
SELECT SUM(amount)
FROM OKL_TXL_QUOTE_LINES_B
WHERE qte_id = p_quote_id
AND qlt_code NOT IN ('AMCFIA','AMCTAX', 'AMYOUB', 'AMCSDD'); -- rmunjulu 3842101 Added security deposit
SELECT SUM(ste.amount) amount
FROM okl_streams stm,
okl_strm_type_b sty,
okc_k_lines_b kle,
okc_statuses_b kls,
okc_line_styles_b lse,
okl_strm_elements ste,
okl_k_lines cle
WHERE stm.khr_id = cp_chr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND kle.id = stm.kle_id
AND kls.code = kle.sts_code
AND kls.ste_code = 'ACTIVE'
AND lse.id = kle.lse_id
AND kle.id = cle.id
AND lse.lty_code = 'FEE'
AND ste.date_billed IS NULL -- rmunjulu EDAT
AND trunc(ste.stream_element_date) > trunc(cp_date) -- rmunjulu EDAT
AND cle.fee_type = cp_fee_type;
SELECT SUM(ste.amount) amount
FROM okl_streams stm
,okl_strm_type_b sty
,okc_k_lines_b kle
,okc_statuses_b kls
,okc_line_styles_b lse
,okl_strm_elements ste
,okl_k_lines cle
,okc_k_lines_b cles
WHERE stm.khr_id = cp_chr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND cles.id = stm.kle_id
AND cles.cle_id = kle.id
AND kls.code = kle.sts_code
AND kls.ste_code = 'ACTIVE'
AND lse.id = cles.lse_id
AND kle.id = cle.id
AND lse.lty_code = 'LINK_FEE_ASSET'
AND ste.date_billed IS NULL -- rmunjulu EDAT
AND trunc(ste.stream_element_date) > trunc(cp_date) -- rmunjulu EDAT
AND cle.fee_type = cp_fee_type;
SELECT SUM(ste.amount) amount
FROM okl_streams stm
,okl_strm_type_b sty
,okc_k_lines_b kle
,okc_statuses_b kls
,okc_line_styles_b lse
,okl_strm_elements ste
,okl_k_lines cle
,okc_k_lines_b cles
,okc_k_items cim
WHERE stm.khr_id = cp_chr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND cles.id = stm.kle_id
AND lse.id = cles.lse_id
AND lse.lty_code = 'LINK_FEE_ASSET'
AND cles.cle_id = kle.id
AND kls.code = kle.sts_code
AND kls.ste_code = 'ACTIVE'
AND kle.id = cle.id
AND cle.fee_type = cp_fee_type
AND cim.object1_id1 = cp_asset_id
AND ste.date_billed IS NULL -- rmunjulu EDAT
AND trunc(ste.stream_element_date) > trunc(cp_date) -- rmunjulu EDAT
AND cim.cle_id = cles.id;
SELECT trunc(qte.date_effective_from) date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
select sysdate into l_sysdate from dual;
select (case
when ROUND(MONTHS_BETWEEN(chr.end_date, cle.start_date)) <= 12 then NVL(kle.oec,0) * .2
else NVL(kle.oec,0) * .1
end) subsidy_amount
from okc_k_lines_b cle,
okl_k_lines kle,
okc_k_headers_b chr
where chr.id = cle.dnz_chr_id
and kle.id = cle.id
and cle.id = p_cle_id -- FREE_FORM1 (FIN)
;
SELECT ROUND(MONTHS_BETWEEN(CHR.end_date, cle.start_date)) months,
NVL(kle.oec,0) oec
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_k_headers_b CHR
WHERE CHR.id = cle.dnz_chr_id
AND kle.id = cle.id
AND cle.id = p_cle_id -- FREE_FORM1 (FIN)
;
SELECT sub.accounting_method_code,
top_cle.date_terminated,
--TO_NUMBER(sgn.value) sty_id
sub_kle.sty_id sty_id, /* Bug 6353756 */
top_cle.dnz_chr_id chr_id
FROM --okl_sgn_translations sgn,
okl_subsidies_b sub,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_k_lines_b top_cle
WHERE --sgn.jtot_object1_code = 'OKL_STRMTYP'
--AND sgn.object1_id1 = TO_CHAR(sub_kle.sty_id) AND
sub.id = sub_kle.subsidy_id
AND sub_cle.cle_id = top_cle.id
AND sub_kle.id = sub_cle.id
AND sub_cle.cle_id = p_cle_id -- FREE_FORM1
;
SELECT NVL(SUM(selb.amount),0)
FROM
okl_streams stmb,
okl_strm_elements selb
WHERE stmb.id = selb.stm_id
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
AND selb.date_billed IS NULL
AND TRUNC(selb.stream_element_date) > TRUNC(p_date_terminated)
AND stmb.kle_id = p_cle_id -- FREE_FORM1
AND stmb.sty_id = p_sty_id
;
SELECT NVL(str.link_hist_stream_id,-1) link_hist_stream_id,
NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
--okl_streams str, MGAAP 7263041
okl_streams_rep_v str,
okl_strm_type_v sty,
okc_k_headers_b CHR,
okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
--AND UPPER(sty.name) = 'PRE-TAX INCOME'
AND sty.stream_type_purpose = 'LEASE_INCOME'
AND (NVL(str.purpose_code,'XXXX') = 'XXXX' OR
NVL(str.purpose_code,'XXXX') = 'REPORT')
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND CHR.id = p_chr_id
AND CHR.sts_code = sts.code
AND CHR.id = str.khr_id
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
GROUP BY str.link_hist_stream_id;
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele
WHERE stm_id = p_stm_id
AND NVL(accrued_yn,'N') = 'Y';
SELECT
NVL(SUM(NVL(KLEB.amount,0)),0)
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB,
okc_statuses_b sts
WHERE
KLEB.ID = CLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'FEE' AND
KLEB.FEE_TYPE IN ('FINANCED') AND
CLEB.DNZ_CHR_ID = l_dnz_chr_id AND
CLEB.sts_code = sts.code AND
sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT
NVL(SUM(NVL(KLEB.amount,0)),0)
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB,
okc_statuses_b sts
WHERE
KLEB.ID = CLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'FEE' AND
KLEB.FEE_TYPE IN ('ABSORBED') AND
CLEB.DNZ_CHR_ID = l_dnz_chr_id AND
CLEB.sts_code = sts.code AND
sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT TAP.AMOUNT,
TAP.KHR_ID
FROM OKL_TRX_AP_INVOICES_B TAP
WHERE TAP.TRX_STATUS_CODE IN ('APPROVED','PROCESSED') -- push to AP
AND TAP.FUNDING_TYPE_CODE IS NOT NULL
-- start: cklee - okl.h ER 05/25/2005
AND NOT EXISTS (SELECT 1
FROM OKC_K_HEADERS_B KHR
WHERE KHR.ID = TAP.KHR_ID
AND ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP')
-- end: cklee - okl.h ER 05/25/2005
;
SELECT TAR.AMOUNT,
TAR.KHR_ID
FROM okl_cnsld_ar_strms_b TAR
WHERE TAR.receivables_invoice_id <> -99999
AND exists (SELECT STY.ID
FROM okl_strm_type_b STY
WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND STY.ID = TAR.STY_ID);
SELECT NVL(SUM(TAP.AMOUNT),0)
FROM OKL_TRX_AP_INVOICES_B TAP,
-- okl_txl_ap_inv_lns_all_b tpl,
(
select gov.dnz_chr_id chr_id,
crd.ID credit_id
from OKC_K_HEADERS_B crd,
okc_Governances gov
where crd.id = gov.chr_id_referred
and crd.sts_code = 'ACTIVE'
and crd.scs_code = 'CREDITLINE_CONTRACT'
union
select mla_g.dnz_chr_id chr_id,
crd.ID credit_id
from OKC_K_HEADERS_B crd,
okc_Governances gov,
OKC_K_HEADERS_B mla,
okc_Governances mla_g
where crd.id = gov.chr_id_referred
and crd.sts_code = 'ACTIVE'
and crd.scs_code = 'CREDITLINE_CONTRACT'
and gov.dnz_chr_id = mla.id
and mla.id = mla_g.chr_id_referred
and mla.scs_code = 'MASTER_LEASE'
) ccg
--WHERE TAP.id = TPL.tap_id
where ccg.chr_id = Tap.KHR_ID
AND ccg.credit_id = p_credit_id
AND TAP.TRX_STATUS_CODE IN ('APPROVED','PROCESSED') -- push to AP
AND TAP.FUNDING_TYPE_CODE IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OKC_K_HEADERS_B KHR
WHERE KHR.ID = tap.KHR_ID
AND ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP')
;
SELECT NVL(SUM(ARL.AMOUNT),0)
FROM okl_bpd_ar_inv_lines_v ARL,
(
select gov.dnz_chr_id chr_id,
crd.ID credit_id
from OKC_K_HEADERS_B crd,
okc_Governances gov
where crd.id = gov.chr_id_referred
and crd.sts_code = 'ACTIVE'
and crd.scs_code = 'CREDITLINE_CONTRACT'
union
select mla_g.dnz_chr_id chr_id,
crd.ID credit_id
from OKC_K_HEADERS_B crd,
okc_Governances gov,
OKC_K_HEADERS_B mla,
okc_Governances mla_g
where crd.id = gov.chr_id_referred
and crd.sts_code = 'ACTIVE'
and crd.scs_code = 'CREDITLINE_CONTRACT'
and gov.dnz_chr_id = mla.id
and mla.id = mla_g.chr_id_referred
and mla.scs_code = 'MASTER_LEASE'
) ccg
WHERE ccg.chr_id = ARL.CONTRACT_ID
AND ccg.credit_id = p_credit_id
AND ARL.receivables_invoice_id <> -99999
AND ARL.interface_line_context = 'OKL_CONTRACTS'
AND exists (SELECT STY.ID
FROM okl_strm_type_b STY
WHERE STY.STREAM_TYPE_PURPOSE = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
AND STY.ID = ARL.STY_ID);
SELECT TAP.AMOUNT,
TPL.KHR_ID
FROM OKL_TRX_AP_INVOICES_B TAP
,OKL_TXL_AP_INV_LNS_ALL_B TPL
WHERE TAP.ID = TPL.TAP_ID
AND TAP.TRX_STATUS_CODE IN ('APPROVED','PROCESSED') -- push to AP
AND TAP.FUNDING_TYPE_CODE IS NOT NULL
-- start: cklee - okl.h ER 05/25/2005
AND NOT EXISTS (SELECT 1
FROM OKC_K_HEADERS_B KHR
WHERE KHR.ID = TPL.KHR_ID
AND ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP')
-- end: cklee - okl.h ER 05/25/2005
;
SELECT TAP.AMOUNT,
TAP.KHR_ID
FROM OKL_TRX_AP_INVOICES_B TAP
where TAP.TRX_STATUS_CODE IN ('APPROVED','PROCESSED') -- push to AP
AND TAP.FUNDING_TYPE_CODE IS NOT NULL
-- start: cklee - okl.h ER 05/25/2005
AND NOT EXISTS (SELECT 1
FROM OKC_K_HEADERS_B KHR
WHERE KHR.ID = tap.KHR_ID
AND ORIG_SYSTEM_SOURCE_CODE = 'OKL_LEASE_APP')
;
SELECT fab.asset_id,
fab.book_type_code
FROM okc_k_lines_v fin_ast_cle,
okc_statuses_b stsb,
fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fin_ast_cle.id = p_cle_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND fad.asset_number = fin_ast_cle.name
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
--AND fbc.book_class = 'CORPORATE'
AND fbc.book_class = p_book_class
AND fab.book_type_code = NVL(p_book_type_code,fab.book_type_code);
SELECT fab.asset_id,
fab.book_type_code
FROM okc_k_lines_v fin_ast_cle,
okc_statuses_b stsb,
fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fin_ast_cle.id = p_cle_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED')
AND fad.asset_number = fin_ast_cle.name
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
--AND fbc.book_class = 'CORPORATE'
AND fbc.book_class = p_book_class
AND fab.book_type_code = NVL(p_book_type_code,fab.book_type_code);
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT fab.asset_id,
fab.book_type_code
FROM okc_k_lines_v fin_ast_cle,
okc_statuses_b stsb,
fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fin_ast_cle.id = p_cle_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND fad.asset_number = fin_ast_cle.name
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
--AND fbc.book_class = 'CORPORATE';
SELECT fab.asset_id,
fab.book_type_code
FROM okc_k_lines_v fin_ast_cle,
okc_statuses_b stsb,
fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fin_ast_cle.id = p_cle_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED')
AND fad.asset_number = fin_ast_cle.name
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
--AND fbc.book_class = 'CORPORATE';
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT fin_cle.id
FROM okc_k_lines_b fin_cle,
okc_line_styles_b lse,
okc_statuses_b stsb
WHERE fin_cle.dnz_chr_id = p_chr_id
AND fin_cle.chr_id = p_chr_id
AND fin_cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND fin_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED');
SELECT fin_cle.id
FROM okc_k_lines_b fin_cle,
okc_line_styles_b lse,
okc_statuses_b stsb
WHERE fin_cle.dnz_chr_id = p_chr_id
AND fin_cle.chr_id = p_chr_id
AND fin_cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND fin_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED');
SELECT fin_cle.id
FROM okc_k_lines_b fin_cle,
okc_line_styles_b lse,
okc_statuses_b stsb
WHERE fin_cle.dnz_chr_id = p_chr_id
AND fin_cle.chr_id = p_chr_id
AND fin_cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND fin_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED');
SELECT fin_cle.id
FROM okc_k_lines_b fin_cle,
okc_line_styles_b lse,
okc_statuses_b stsb
WHERE fin_cle.dnz_chr_id = p_chr_id
AND fin_cle.chr_id = p_chr_id
AND fin_cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND fin_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED');
SELECT NVL(SUM(kle.capital_amount),0)
--bug# 4899328
--+ NVL(SUM(kle.capitalized_interest),0) CapAmountLines
FROM OKC_LINE_STYLES_B LSEB,
OKL_K_LINES KLE,
OKC_K_LINES_B CLEB,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB.LSE_ID
AND LSEB.lty_code = 'FREE_FORM1'
AND KLE.id = CLEB.ID
AND CLEB.CHR_ID = ChrId
AND CLEB.DNZ_CHR_ID = ChrId
AND CLEB.sts_code = STSB.code
AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT
NVL(SUM(NVL(KLEB.amount,0)),0) ROLLOVER_AMOUNT
FROM
OKL_K_LINES KLEB,
OKC_K_LINES_B CLEB,
OKC_LINE_STYLES_B LSEB,
OKC_STATUSES_B STS
WHERE
KLEB.ID = CLEB.ID AND
CLEB.LSE_ID = LSEB.ID AND
LSEB.LTY_CODE = 'FEE' AND
KLEB.FEE_TYPE = 'ROLLOVER' AND
CLEB.DNZ_CHR_ID = l_dnz_chr_id AND
CLEB.STS_CODE = STS.CODE AND
STS.STE_CODE NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT NVL(chr.TOT_CL_TRANSFER_AMT,0)
FROM OKL_K_HEADERS chr
WHERE chr.ID = ChrId;
SELECT NVL(chr.TOT_CL_NET_TRANSFER_AMT,0)
FROM OKL_K_HEADERS chr
WHERE chr.ID = ChrId;
SELECT SUM (NVL (ste.amount, 0)) amount_due
--FROM okl_streams stm,
FROM okl_streams_rep_v stm,
okl_strm_type_b sty,
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
-- Added the following 3 conditions to restrict the unbilled receivables calculation to only
-- billable streams
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND ste.STREAM_ELEMENT_DATE > nvl(cp_trx_date,sysdate); -- gkadarka added this null check
SELECT SUM (NVL (ste.amount, 0)) amount_due
FROM okl_streams stm,
okl_strm_type_b sty, -- Added this table to get the billable_yn flag
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'N' -- reporting strems are inactive
AND stm.say_code = 'CURR' -- reporting streams are current
AND ste.stm_id = stm.id
--AND ste.date_billed IS NULL -- reporting streams never get billed
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y' -- reporting streams are billable
AND stm.purpose_code = 'REPORT'
AND ste.STREAM_ELEMENT_DATE > nvl(cp_trx_date,sysdate); -- gkadarka added this null check
SELECT nvl(ste.amount, 0) amount
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements ste
WHERE stm.khr_id = cp_contract_id
AND stm.kle_id = cp_contract_line_id
AND stm.sty_id = NVL (cp_stream_type_id, stm.sty_id)
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND NVL (ste.amount, 0) <> 0
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND ste.stream_element_date > nvl(cp_trx_date,sysdate);
SELECT currency_code
FROM okc_k_lines_b
WHERE id = p_kle_id;
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
--okl_streams stm, MGAAP 7263041
okl_streams_rep_v stm,
okl_strm_type_v sty
WHERE sty.id = p_sty_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND (stm.purpose_code IS NULL OR stm.purpose_code='REPORT')
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id
-- guru Added
AND sel.STREAM_ELEMENT_DATE > nvl(cp_trx_date,sysdate); -- gkadarka added this null check
SELECT NVL(SUM(sel.amount),0)
FROM okl_strm_elements sel,
--okl_streams stm, MGAAP 7263041
okl_streams_rep_v stm,
okl_strm_type_v sty
WHERE sty.id = p_sty_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR' -- reporting streams are current
AND stm.active_yn = 'N' -- reporting strems are inactive
AND stm.purpose_code IS NULL
-- AND sel.date_billed IS NULL -- reporting streams never get billed
--AND sty.billable_yn = 'N' -- PRE-TAX streams are not billable
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id
AND stm.purpose_code = 'REPORT'
AND sel.STREAM_ELEMENT_DATE > nvl(cp_trx_date,sysdate); -- gkadarka added this null check
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT NVL(sel.amount,0) amount
FROM okl_strm_elements sel,
--okl_streams stm, MGAAP 7263041
okl_streams_rep_v stm,
okl_strm_type_v sty
WHERE sty.id = p_sty_id
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND (stm.purpose_code IS NULL OR stm.purpose_code='REPORT')
AND stm.kle_id = c_contract_line_id
AND sel.stm_id = stm.id
AND sel.STREAM_ELEMENT_DATE > nvl(cp_trx_date,sysdate);
SELECT currency_code
FROM okc_k_lines_b
WHERE id = p_kle_id;
SELECT qte.qtp_code qtp_code
FROM okl_trx_quotes_v qte
WHERE qte.id = p_quote_id;
SELECT nvl(tql.asset_value,0) residual_value
FROM okl_txl_quote_lines_v tql
WHERE tql.qte_id = p_quote_id
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = p_kle_id;
SELECT depreciation_cost, ID
FROM okl_txl_assets_b
WHERE kle_id = p_kle_id
AND tal_type = 'AML'
AND ROWNUM < 2;
SELECT
sel.amount
FROM
okl_strm_elements sel,
okl_streams stm
WHERE
stm.sty_id = p_sty_id
AND stm.khr_id = p_contract_id
AND stm.kle_id = p_contract_line_id
AND sel.stream_element_date <= p_date
AND stm.say_code = 'CURR'
AND stm.id = sel.stm_id
ORDER BY sel.stream_element_date DESC;
SELECT TO_NUMBER(laslh.object1_id1)
INTO l_fee_payment_id
FROM okc_rule_groups_b lalevl,
okc_rules_b laslh
WHERE lalevl.cle_id = p_contract_line_id
AND lalevl.rgd_code = 'LALEVL'
AND lalevl.id = laslh.rgp_id
AND laslh.rule_information_category = 'LASLH';
SELECT cle.id
FROM
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_headers_b chr
WHERE
chr.id = p_contract_id
AND cle.chr_id = chr.id
AND cle.sts_code = chr.sts_code
AND cle.id = kle.id
AND kle.fee_type = 'FINANCED';
SELECT cle.id
FROM
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_headers_b chr
WHERE
chr.id = p_contract_id
AND cle.chr_id = chr.id
AND cle.sts_code = chr.sts_code
AND cle.id = kle.id
AND kle.fee_type = 'ROLLOVER';
SELECT qte.date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_qte_id;
SELECT nvl(fab.cost,0) current_cost
FROM fa_books fab,
fa_book_controls fbc,
okc_k_items_v itm,
okc_k_lines_b kle,
okc_line_styles_v lse
WHERE fbc.book_class = 'CORPORATE'
AND fab.book_type_code = fbc.book_type_code
AND fab.asset_id = itm.object1_id1
AND itm.cle_id = kle.id
AND kle.cle_id = p_kle_id
AND kle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
AND fab.transaction_header_id_out IS NULL;
SELECT nvl(sum(amount_remaining),0)
FROM okl_cs_bpd_inv_dtl_v
WHERE chr_id = c_contract_id
AND due_date <= SYSDATE;
SELECT NVL(sum(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND sel.stm_id = stm.id
AND date_billed is null
AND stream_element_date <= SYSDATE;
SELECT nvl(KLE.residual_value,0) residual_value
FROM OKL_K_LINES KLE
WHERE KLE.id = p_kle_id;
SELECT nvl(RET.proceeds_of_sale,0) sales_proceeds
FROM --OKX_ASSET_LINES_V OAL,
FA_RETIREMENTS RET
WHERE RET.RETIREMENT_ID = p_retirement_id;
SELECT qte.date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_qte_id;
SELECT sum(TQL.asset_value) outstanding_bal
FROM OKL_TXL_QUOTE_LINES_B TQL
WHERE TQL.qte_id = p_qte_id
AND TQL.qlt_code = 'AMCFIA';
SELECT qte.date_effective_from
FROM okl_trx_quotes_b qte
WHERE qte.id = p_qte_id;
SELECT base_rate
FROM okl_k_rate_params_v
WHERE khr_id = p_khr_id;
SELECT deal_type
FROM OKL_K_HEADERS
WHERE id = cp_khr_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele
,okl_streams str
,okl_strm_type_v sty
,okc_k_lines_v line
,okc_statuses_b sts
,okc_line_styles_b style
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND str.purpose_code IS NULL
AND sele.date_billed IS NULL
AND line.chr_id = str.khr_id
AND line.id = str.kle_id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
AND line.sts_code = sts.code
AND sts.ste_code = 'ACTIVE'
AND str.khr_id = cp_contract_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele
--,okl_streams str MGAAP 7263041
,okl_streams_rep_v str
,okl_strm_type_v sty
,okc_k_lines_b line
,okc_line_styles_b style
,okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND (STR.PURPOSE_CODE IS NULL OR STR.PURPOSE_CODE='REPORT')
AND nvl(sele.accrued_yn,'N') = 'N'
AND str.kle_id = line.id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
AND line.sts_code = sts.code
AND sts.ste_code = 'ACTIVE'
AND line.chr_id = cp_contract_id;
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele
,okl_streams str
,okl_strm_type_v sty
,okc_k_lines_v line
-- ,okc_statuses_b sts
,okc_line_styles_b style
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND str.purpose_code IS NULL
AND sele.date_billed IS NULL
AND line.chr_id = str.khr_id
AND line.id = str.kle_id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
-- AND line.sts_code = sts.code
-- AND sts.ste_code = 'TERMINATED'
AND str.khr_id = cp_contract_id
AND line.id = NVL(cp_contract_line_id, line.id);
SELECT NVL(SUM(sele.amount),0)
FROM okl_strm_elements sele
--,okl_streams str MGAAP 7263041
,okl_streams_rep_v str
,okl_strm_type_v sty
,okc_k_lines_b line
,okc_line_styles_b style
-- ,okc_statuses_b sts
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.stream_type_purpose) = 'LEASE_INCOME' -- pre-tax income has steam type purpose as lease_income
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND (STR.PURPOSE_CODE IS NULL OR STR.PURPOSE_CODE='REPORT')
AND nvl(sele.accrued_yn,'N') = 'N'
AND str.kle_id = line.id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
-- AND line.sts_code = sts.code
-- AND sts.ste_code = 'TERMINATED'
AND line.chr_id = cp_contract_id
AND line.id = NVL(cp_contract_line_id, line.id);
SELECT MIN(sel.stream_element_date)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.billable_yn = 'Y'
AND sty.code NOT LIKE '%TAX%'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date > sysdate;
SELECT NVL(sum(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.billable_yn = 'Y'
AND sty.code NOT LIKE '%TAX%'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date = c_next_due_date;
SELECT orig_system_id1
FROM okc_k_headers_b
WHERE id = p_contract_id
AND orig_system_source_code = 'OKL_LEASE_APP';
SELECT id
FROM okl_lease_quotes_b
WHERE parent_object_id = p_leaseapp_id
AND parent_object_code = 'LEASEAPP'
AND primary_quote = 'Y';
SELECT id
FROM okl_lease_quotes_b
WHERE parent_object_id = p_leaseapp_id
AND parent_object_code = 'LEASEAPP'
AND primary_quote = 'Y';
SELECT nvl(amount, 0)
FROM okl_trx_ap_invoices_b
WHERE id = p_contract_line_id;
SELECT kp.object1_id1, okc.cust_acct_id
FROM okc_k_party_roles_b kp, okc_k_headers_b okc
WHERE kp.dnz_chr_id = p_contract_id
AND kp.rle_code = 'LESSEE'
AND kp.dnz_chr_id = okc.id;
SELECT prospect_id, cust_acct_id
FROM okl_lease_applications_b
WHERE id = p_lease_app_id;
SELECT khr_id
FROM okl_k_headers
WHERE id = p_contract_id;
SELECT program_agreement_id
FROM okl_lease_applications_b
WHERE id = p_lease_app_id;
SELECT date_transaction_occurred
FROM okl_trx_contracts
WHERE khr_id = p_contract_id
AND representation_type = 'PRIMARY'; -- MGAAP OTHER 7263041
SELECT a.date_invoiced
FROM okl_trx_ap_invoices_b a
-- ,okl_txl_ap_inv_lns_all_b b --cklee 09/21/07
-- WHERE a.id = b.tap_id
where a.khr_id = p_contract_id;
SELECT fab.asset_id,
fab.book_type_code
FROM okc_k_lines_v fin_ast_cle,
okc_statuses_b stsb,
fa_additions fad,
fa_book_controls fbc,
fa_books fab
WHERE fin_ast_cle.id = p_cle_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.sts_code = stsb.code
AND stsb.ste_code NOT IN ('HOLD','CANCELLED')
AND fad.asset_number = fin_ast_cle.name
AND fab.asset_id = fad.asset_id
AND fab.book_type_code = fbc.book_type_code
AND fab.transaction_header_id_out IS NULL
--AND fbc.book_class = 'CORPORATE';
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT (ASSETCOMP.NUMBER_OF_UNITS * ASSETCOMP.UNIT_COST) AST_COST
FROM OKL_ASSET_COMPONENTS_B ASSETCOMP
WHERE ASSETCOMP.PRIMARY_COMPONENT = 'YES'
AND ASSETCOMP.ASSET_ID = p_asset_id;
SELECT sum(ASSETCOMP.NUMBER_OF_UNITS * ASSETCOMP.UNIT_COST) ADDON_AMNT
FROM OKL_ASSET_COMPONENTS_B ASSETCOMP
WHERE ASSETCOMP.PRIMARY_COMPONENT = 'NO'
AND ASSETCOMP.ASSET_ID = p_asset_id;
SELECT SUM(amount) capitalized_fee_amount
FROM okl_line_relationships_v lre
WHERE source_line_type = 'ASSET'
AND related_line_type = 'CAPITALIZED'
AND source_line_id = p_asset_id;
SELECT sum(adj.value) cap_down_payment
FROM okl_assets_b ast, okl_cost_adjustments_b adj
WHERE ast.parent_object_code = 'LEASEQUOTE'
AND adj.parent_object_id = ast.id
AND adj.ADJUSTMENT_SOURCE_TYPE = 'DOWN_PAYMENT'
AND adj.PROCESSING_TYPE = 'CAPITALIZE'
AND ast.id = p_asset_id;
SELECT sum(adj.value) tradeIn_amount
FROM okl_assets_b ast, okl_cost_adjustments_b adj
WHERE ast.parent_object_code = 'LEASEQUOTE'
AND adj.parent_object_id = ast.id
AND adj.adjustment_source_type = 'TRADEIN'
AND ast.id = p_asset_id;
SELECT lse.lty_code
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.id = p_line_id
AND cle.lse_id = lse.id;
SELECT SUM(cle.price_unit * cim.number_of_items) asset_cost
FROM okc_subclass_top_line stl,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okc_k_items_v cim,
okc_k_lines_v cle
WHERE cle.cle_id = p_line_id
AND cle.dnz_chr_id = p_dnz_chr_id
AND cle.id = cim.cle_id
AND cle.dnz_chr_id = cim.dnz_chr_id
AND cle.lse_id = lse1.id
AND lse1.lty_code = 'ITEM'
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = 'FREE_FORM1'
AND lse2.id = stl.lse_id
AND stl.scs_code IN ('LEASE','LOAN');
SELECT SUM(cle.price_unit* cim.number_of_items) add_on
FROM okc_subclass_top_line stl,
okc_line_styles_b lse3,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okc_k_items_v cim,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND cle.dnz_chr_id = cim.dnz_chr_id
AND cle.id = cim.cle_id
AND cle.lse_id = lse1.id
AND lse1.lty_code = 'ADD_ITEM'
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = 'ITEM'
AND lse2.lse_parent_id = lse3.id
AND lse3.lty_code = 'FREE_FORM1'
AND lse3.id = stl.lse_id
AND stl.scs_code IN ('LEASE','LOAN')
AND exists (SELECT 1
FROM okc_subclass_top_line stlx,
okc_line_styles_b lse2x,
okc_line_styles_b lse1x,
okc_k_lines_b clex
WHERE clex.cle_id = p_line_id
AND clex.dnz_chr_id = p_dnz_chr_id
AND clex.lse_id = lse1x.id
AND lse1x.lty_code = 'ITEM'
AND lse1x.lse_parent_id = lse2x.id
AND lse2x.lty_code = 'FREE_FORM1'
AND lse2x.id = stlx.lse_id
AND stlx.scs_code IN ('LEASE','LOAN')
AND clex.id = cle.cle_id);
SELECT SUM(kle_cov.capital_amount) Cap_fee
FROM okc_line_styles_b lseb,
okc_k_items cim,
okl_k_lines kle_cov,
okc_k_lines_b cleb_cov,
okc_statuses_b stsb
WHERE lseb.id = cleb_cov.lse_id
AND lseb.lty_code = 'LINK_FEE_ASSET'
AND cim.jtot_object1_code = 'OKX_COVASST'
AND cleb_cov.id = cim.cle_id
AND kle_cov.id = cleb_cov.id
AND cleb_cov.dnz_chr_id = cim.dnz_chr_id
AND cleb_cov.dnz_chr_id = p_dnz_chr_id
AND cim.object1_id1 = p_line_id
AND cleb_cov.sts_code = stsb.code
AND stsb.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED',
'HOLD');
SELECT NVL(kle.capital_reduction,0) capital_reduction,
NVL(kle.tradein_amount,0) tradein_amount,
NVL(kle.capital_reduction_percent,0) capital_reduction_percent,
kle.capitalize_down_payment_yn capitalize_down_payment_yn
FROM okc_line_styles_b ls,
okl_k_lines_full_v kle,
okc_statuses_b sts
WHERE kle.dnz_chr_id = p_dnz_chr_id
AND kle.id = p_line_id
AND ls.id = kle.lse_id
AND ls.lty_code ='FREE_FORM1'
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED',
'HOLD');
SELECT SUM(cle.price_unit* cim.number_of_items) add_on_cost
FROM okc_subclass_top_line stl,
okc_line_styles_b lse3,
okc_line_styles_b lse2,
okc_line_styles_b lse1,
okc_k_items_v cim,
okc_k_lines_b cle
WHERE cle.dnz_chr_id = p_contract_id
AND cle.dnz_chr_id = cim.dnz_chr_id
AND cle.id = cim.cle_id
AND cle.lse_id = lse1.id
AND lse1.lty_code = 'ADD_ITEM' -- G_ADDON_LINE_LTY_CODE
AND lse1.lse_parent_id = lse2.id
AND lse2.lty_code = 'ITEM' -- G_MODEL_LINE_LTY_CODE
AND lse2.lse_parent_id = lse3.id
AND lse3.lty_code = 'FREE_FORM1' -- G_FIN_LINE_LTY_CODE
AND lse3.id = stl.lse_id
AND stl.scs_code IN ('LEASE','LOAN')
AND exists (SELECT 1
FROM okc_subclass_top_line stlx,
okc_line_styles_b lse2x,
okc_line_styles_b lse1x,
okc_k_lines_b clex
WHERE clex.cle_id = p_contract_line_id -- lse_id = 33>
AND clex.dnz_chr_id = p_contract_id
AND clex.lse_id = lse1x.id
AND lse1x.lty_code = 'ITEM' -- G_MODEL_LINE_LTY_CODE
AND lse1x.lse_parent_id = lse2x.id
AND lse2x.lty_code = 'FREE_FORM1' -- G_FIN_LINE_LTY_CODE
AND lse2x.id = stlx.lse_id
AND stlx.scs_code IN ('LEASE','LOAN')
AND clex.id = cle.cle_id);
SELECT NVL(SUM(sub_kle.amount),0)
FROM okl_subsidies_b subb,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
WHERE subb.id = sub_kle.subsidy_id
AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
AND sub_kle.id = sub_cle.id
AND sub_cle.lse_id = sub_lse.id
AND sub_lse.lty_code = 'SUBSIDY'
AND sub_cle.sts_code <> 'ABANDONED'
AND sub_cle.dnz_chr_id = p_contract_id
AND sub_cle.cle_id = p_fin_asset_line_id
AND subb.customer_visible_yn = 'Y'
;
SELECT NVL(SUM(sub_kle.subsidy_override_amount),0)
FROM okl_subsidies_b subb,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
WHERE subb.id = sub_kle.subsidy_id
AND subb.accounting_method_code = NVL(UPPER(p_accounting_method),subb.accounting_method_code)
AND sub_kle.id = sub_cle.id
AND sub_cle.lse_id = sub_lse.id
AND sub_lse.lty_code = 'SUBSIDY'
AND sub_cle.sts_code <> 'ABANDONED'
AND sub_cle.dnz_chr_id = p_contract_id
AND sub_cle.cle_id = p_fin_asset_line_id
AND subb.customer_visible_yn = 'Y'
;
SELECT sum(kle_cov.amount) asset_fin_fee_amt
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
okl_k_lines fee_line,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND CLEB_COV.dnz_chr_id = c_chr_id
AND cim.object1_id1 = to_char(c_fin_asset_line_id) --lse_id = 33
AND CLEB_COV.sts_code = STSB.code
and CLEB_COV.cle_id = fee_line.id
and fee_line.fee_type = 'FINANCED'
AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT sum(kle_cov.amount) asset_fin_fee_amt
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
okl_k_lines fee_line,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND CLEB_COV.dnz_chr_id = c_chr_id
AND cim.object1_id1 = to_char(c_fin_asset_line_id) --lse_id = 33
AND CLEB_COV.sts_code = STSB.code
and CLEB_COV.cle_id = fee_line.id
and fee_line.fee_type = 'FINANCED'
AND STSB.ste_code NOT IN ( 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT sum(kle_cov.amount) asset_roll_fee_amt
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
okl_k_lines fee_line,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND CLEB_COV.dnz_chr_id = c_chr_id
AND cim.object1_id1 = to_char(c_fin_asset_line_id) --lse_id = 33
AND CLEB_COV.sts_code = STSB.code
and CLEB_COV.cle_id = fee_line.id
and fee_line.fee_type = 'ROLLOVER'
AND STSB.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
SELECT sum(kle_cov.amount) asset_roll_fee_amt
FROM OKC_LINE_STYLES_B LSEB,
OKC_K_ITEMS CIM,
OKL_K_LINES KLE_COV,
okl_k_lines fee_line,
OKC_K_LINES_B CLEB_COV,
OKC_STATUSES_B STSB
WHERE LSEB.ID = CLEB_COV.LSE_ID
AND LSEB.lty_code = 'LINK_FEE_ASSET'
AND CIM.jtot_object1_code = 'OKX_COVASST'
AND CLEB_COV.id = CIM.cle_id
AND KLE_COV.id = CLEB_COV.ID
AND CLEB_COV.DNZ_CHR_ID = CIM.DNZ_CHR_ID
AND CLEB_COV.dnz_chr_id = c_chr_id
AND cim.object1_id1 = to_char(c_fin_asset_line_id) --lse_id = 33
AND CLEB_COV.sts_code = STSB.code
and CLEB_COV.cle_id = fee_line.id
and fee_line.fee_type = 'ROLLOVER'
AND STSB.ste_code NOT IN ( 'EXPIRED', 'CANCELLED', 'HOLD');