[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor l_start_date_csr(cp_kle_id IN NUMBER, cp_sequence IN NUMBER) IS select start_date from (
select rownum sequence, --this can replace the seq since seq num is not stored by authoring
A.* from
(Select sll.id,
styp.code payment_type,
sll.object1_id1 Pay_freq,
sll.rule_information1 seq,
fnd_date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information3 number_periods,
sll.rule_information4 tuoms_per_period,
sll.rule_information6 amount,
sll.rule_information7 stub_days,
sll.rule_information8 stub_amount,
sll.rule_information10 advance_or_arrears,
sll.rule_information13 rate,
rgp.cle_id cle_id
from okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_v styp,
okc_rule_groups_b rgp
where to_number(sll.object2_id1) = slh.id
and sll.rule_information_category = 'LASLL'
and sll.dnz_chr_id = rgp.dnz_chr_id
and sll.rgp_id = rgp.id
and slh.rule_information_category = 'LASLH'
and slh.dnz_chr_id = rgp.dnz_chr_id
and slh.rgp_id = rgp.id
and slh.object1_id1 = styp.id
and rgp.rgd_code = 'LALEVL'
and rgp.cle_id = cp_kle_id
order by rgp.cle_id
, fnd_date.canonical_to_date(sll.rule_information2)) A
)
where sequence = cp_sequence;
Select fnd_date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information7 stub_days,
sll.rule_information8 stub_amount
from okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_v styp,
okc_rule_groups_b rgp
where to_number(sll.object2_id1) = slh.id
and sll.rule_information_category = 'LASLL'
and sll.dnz_chr_id = rgp.dnz_chr_id
and sll.rgp_id = rgp.id
and slh.rule_information_category = 'LASLH'
and slh.dnz_chr_id = rgp.dnz_chr_id
and slh.rgp_id = rgp.id
and slh.object1_id1 = styp.id
and rgp.rgd_code = 'LALEVL'
and rgp.cle_id = cp_kle_id
and sll.rule_information7 IS NOT NULL
and sll.rule_information8 IS NOT NULL
--and fnd_date.canonical_to_date(sll.rule_information2) <= cp_start_date
order by abs(fnd_date.canonical_to_date(sll.rule_information2) - cp_start_date) asc;
SELECT id,
deal_type,
start_date,
end_date,
currency_code,
pdt_id,
authoring_org_id
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT interest_basis_code,
calculation_formula_id,
nvl(principal_basis_code, 'ACTUAL') principal_basis_code,
days_in_a_month_code,
days_in_a_year_code,
catchup_settlement_code
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND SYSDATE BETWEEN effective_from_date and nvl(effective_to_date, SYSDATE);
SELECT ppm.revenue_recognition_method,
ppm.interest_calculation_basis
FROM okl_product_parameters_v ppm
WHERE ppm.id = p_product_id;
SELECT hou.name,
book.currency_code
FROM hr_operating_units hou,
GL_LEDGERS_PUBLIC_V book
WHERE hou.set_of_books_id = book.ledger_id
AND hou.ORGANIZATION_ID = mo_global.get_current_org_id();
SELECT vip.interest_calc_start_date,
vip.interest_calc_end_date,
vip.interest_rate,
OKL_ACCOUNTING_UTIL.format_amount(NVL(vip.principal_balance,0),chr.currency_code) principal_balance,
OKL_ACCOUNTING_UTIL.format_amount(NVL(vip.interest_amt,0),chr.currency_code) interest_amt,
vip.interest_calc_days
FROM okl_var_int_params vip,
okc_k_headers_b chr
WHERE chr.id = p_contract_id
AND vip.khr_id = chr.id
AND vip.request_id = g_request_id;
select NVL(sum(sel.amount), 0) amount
from
okl_K_lines_full_v kle,
okc_statuses_b sts,
okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE kle.dnz_chr_id = p_chr_id
AND kle.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND kle.dnz_chr_id = stm.khr_id
AND kle.id = stm.kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'RENT'
AND stm.id = sel.stm_id
AND ((p_advance_or_arrears = 'ARREARS' and sel.stream_element_date > p_date)
OR (p_advance_or_arrears <> 'ARREARS' and sel.stream_element_date >= p_date));
SELECT NVL(SUM(sele.amount),0) amount
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_b sty
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 nvl( str.purpose_code, 'XXXX' ) = 'XXXX'
AND str.khr_id = p_chr_id
AND str.kle_id = p_kle_id
AND nvl(str.kle_id, -1) = -1
AND ((p_advance_or_arrears = 'ARREARS' and sele.stream_element_date > p_date)
OR (p_advance_or_arrears <> 'ARREARS' and sele.stream_element_date >= p_date));
select NVL(sum(sel.amount), 0) amount
from
okl_K_lines_full_v kle,
okc_statuses_b sts,
okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty
WHERE kle.dnz_chr_id = p_chr_id
AND kle.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD')
AND kle.dnz_chr_id = stm.khr_id
AND kle.id = stm.kle_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'LEASE_INCOME'
AND stm.id = sel.stm_id
AND sel.stream_element_date >= TRUNC(p_date, 'MONTH');
SELECT nvl(kle.residual_value,0) 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 --289326506849179644190030423574805590144
AND KLE.id = p_kle_id
AND kle.sts_code = sts.code
AND sts.ste_code NOT IN ('TERMINATED', 'EXPIRED', 'CANCELLED', 'HOLD');
select sum(okl_line.capital_amount) asset_cost
from okc_k_lines_b okc_line,
okc_line_styles_b style,
okl_k_lines okl_line
where okc_line.chr_id = p_chr_id --276779267018378275653765386722943545472
and okc_line.lse_id = style.id
and style.lty_code = 'FREE_FORM1'
and okc_line.id = okl_line.id;
SELECT NVL(SUM(AR_REC.AMOUNT_APPLIED),0) AMOUNT
FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC,
AR_PAYMENT_SCHEDULES_ALL AR_PAY,
okl_bpd_tld_ar_lines_v TLD,
OKL_STRM_TYPE_B STRM
WHERE AR_REC.APPLY_DATE <= p_date -- TO BE REPLACED WITH APPLY DATE
AND TLD.KHR_ID = p_chr_id --298074492502791815515340871034511143040
AND TLD.STY_ID = STRM.ID
AND STRM.STREAM_TYPE_PURPOSE = 'PRINCIPAL_PAYMENT'
AND TLD.CUSTOMER_TRX_ID = AR_PAY.CUSTOMER_TRX_ID
AND AR_PAY.PAYMENT_SCHEDULE_ID = AR_REC.APPLIED_PAYMENT_SCHEDULE_ID
AND AR_PAY.CLASS = 'INV'
AND AR_REC.STATUS = 'APP';
select nvl(sum(ap_inv.payment_amount_total), 0) AMOUNT
from ap_invoices_all ap_inv,
okl_trx_ap_invoices_b okl_inv,
ap_payment_schedules_all pay_sche
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
where okl_inv.id = okl_inv_ln.tap_id
and okl_inv_ln.khr_id = p_chr_id
and ap_inv.application_id = fnd_app.application_id
and fnd_app.application_short_name = 'OKL'
and okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
and cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
and okl_inv.funding_type_code = 'BORROWER_PAYMENT'
and ap_inv.invoice_id = pay_sche.invoice_id
and pay_sche.creation_date > p_date
and ap_inv.payment_status_flag in ('Y','P');
Select FA_BOOKS.ASSET_ID ASSET_ID,
FA_BOOKS.cost ASSET_COST,
FA_BOOKS.book_type_code BOOK_TYPE_CODE
from FA_BOOKS ,
FA_BOOK_CONTROLS,
OKC_K_LINES_B LINES,
OKC_LINE_STYLES_B STYLE,
OKC_K_ITEMS KITEM
where FA_BOOKS.asset_id = KITEM.OBJECT1_ID1
and LINES.DNZ_CHR_ID = p_chr_id --291511068054787299132375269533568315520
and LINES.cle_id = p_kle_id
and LINES.ID = KITEM.CLE_ID
and LINES.LSE_ID = STYLE.ID
and STYLE.LTY_CODE = 'FIXED_ASSET'
and FA_BOOKS.book_type_code = FA_BOOK_CONTROLS.book_type_code
and FA_BOOK_CONTROLS.book_class = 'CORPORATE'
and FA_BOOKS.transaction_header_id_out is null;
select sum(deprn_amount) ACCUMULATED_DEPRECIATION
from fa_deprn_summary
where Asset_id = p_asset_id
and book_type_code = p_book_type_code;
SELECT khr.deal_type deal_type
FROM okc_k_headers_b CHR,
okl_k_headers khr
WHERE khr.id = CHR.id
AND khr.id = p_khr_id;
SELECT sum(sel.amount) amount
FROM okl_strm_elements sel,okl_streams stm, okl_strm_type_b sty
WHERE stm.khr_id = p_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND sel.stm_id = stm.id
and trunc(sel.stream_element_date) = ( select trunc(max(sel.stream_element_date))
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND sel.stm_id = stm.id
and trunc(sel.stream_element_date) <= p_bal_date);
select trunc(min(sel.stream_element_date))
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_b sty
WHERE stm.khr_id = p_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE'
AND sel.stm_id = stm.id
and sel.stream_element_date >= p_bal_date;
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND LANGUAGE = cp_language;
select ive.value VALUE,
(LEAST(trunc(p_end_date),NVL(datetime_invalid, sysdate))
- GREATEST(trunc(p_start_date),datetime_valid)) DAYS
,idx.name index_name,
GREATEST(trunc(p_start_date),datetime_valid) VALID_FROM,
LEAST(trunc(p_end_date),NVL(datetime_invalid, sysdate)) VALID_UNTIL
from okl_indices idx,
okl_index_values ive
where idx.id = ive.idx_id
AND ive.idx_id = l_interest_rec.index_name
AND (p_start_date between datetime_valid and nvl(datetime_invalid, SYSDATE)
OR (p_end_date between datetime_valid and nvl(datetime_invalid, SYSDATE))
OR (datetime_valid >= p_start_date AND nvl(datetime_invalid,SYSDATE) <= p_end_date) )
order by VALID_FROM;
SELECT val.value, ind.name index_name
FROM okl_index_values val
,okl_indices ind
WHERE val.idx_id = p_index_name
AND ind.id = val.idx_id
AND val.datetime_valid = (SELECT MAX(val.datetime_valid)
FROM OKL_INDEX_VALUES val ,
OKL_INDICES ind
WHERE ind.id = val.idx_id
AND val.idx_id = p_index_name
AND val.datetime_valid < p_end_date);
select min(sel.stream_element_date)
FROM okl_strm_elements sel,okl_streams stm, okl_strm_type_b sty,
okl_k_headers khr, okc_k_headers_b chr
WHERE stm.khr_id = c_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND (sty.stream_type_purpose = 'RENT' OR sty.stream_type_purpose = 'PRINCIPAL_PAYMENT')
AND sel.stm_id = stm.id
and khr.id = stm.khr_id
and chr.id = khr.id
and sel.stream_element_date > nvl(khr.date_last_interim_interest_cal,chr.start_date);
SELECT chr.start_date
,khr.deal_type
,khr.date_last_interim_interest_cal
,chr.end_date
,chr.currency_code
FROM okc_k_headers_b chr
,okl_k_headers khr
WHERE chr.id = c_contract_id
AND khr.id = chr.id;
select max(tai.date_invoiced) last_date_invoiced, ipm.param_value int_rate
from okl_trx_ar_invoices_v tai, okl_trx_params_b ipm
where tai.khr_id = c_contract_id
and tai.description = 'Variable Interest Stream Billing'
and ipm.source_id = tai.id
and param_value <> 0
group by ipm.param_value;
SELECT distinct khr.id khr_id,
khr.deal_type,
khr.date_last_interim_interest_cal,
chr.contract_number,
chr.start_date start_date
FROM okc_k_headers_b chr,
okl_k_headers khr,
okc_statuses_b khs,
okc_rules_b rules,
okc_rule_groups_b rgp
WHERE CHR.CONTRACT_NUMBER = NVL(l_contract_number,CHR.CONTRACT_NUMBER)
AND khr.deal_type IN ('LOAN','LEASEDF','LOAN-REVOLVING',
'LEASEST','LEASEOP')
AND chr.id = khr.id
AND khs.code = chr.sts_code
AND khs.ste_code = 'ACTIVE'
AND rules.dnz_chr_id = chr.id
AND rules.rule_information_category = 'LAINTP'
AND rgp.id = rules.rgp_id
AND rgp.chr_id = chr.id
AND rgp.rgd_code = 'LAIIND'
AND NVL(rules.rule_information1, 'N') = 'Y'
ORDER BY khr.deal_type, chr.contract_number;
SELECT stm.id
FROM okl_streams stm
WHERE stm.khr_id = p_khr_id
AND stm.sty_id = p_sty_id;
SELECT SE_LINE_NUMBER
FROM OKL_STRM_ELEMENTS_V
WHERE stm_id = c_stm_id
ORDER BY SE_LINE_NUMBER DESC;
SELECT okl_sif_seq.nextval
FROM dual;
SELECT add_months(NVL(date_last_interim_interest_cal, start_date), decode(pay_freq, 'M', 1, 'Q', 3, 'S', 6, 'A', 12, 1)) next_reamort_date
FROM okl_k_headers_full_v khr,
(Select distinct rgp.dnz_chr_id khr_id
,sll.object1_id1 Pay_freq
from okc_rules_b sll,
okc_rules_b slh,
okl_strm_type_b styp,
okc_rule_groups_b rgp
where to_number(sll.object2_id1) = slh.id
and sll.rule_information_category = 'LASLL'
and sll.dnz_chr_id = rgp.dnz_chr_id
and sll.rgp_id = rgp.id
and slh.rule_information_category = 'LASLH'
and slh.dnz_chr_id = rgp.dnz_chr_id
and slh.rgp_id = rgp.id
and slh.object1_id1 = styp.id
and styp.stream_type_purpose = 'RENT'
and rgp.rgd_code = 'LALEVL'
and rgp.dnz_chr_id = cp_khr_id
) pay
where khr.id = cp_khr_id
and khr.id = pay.khr_id;
-- Declare records: i - insert, u - update, r - result
------------------------------------------------------------
-- Transaction headers
i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
Okl_Trx_Ar_Invoices_Pub.update_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_taiv_rec
,r_taiv_rec);
print_debug('Updated the TAI record successfully');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Updated the TAI record successfully');
SELECT CHR.currency_code
,CHR.authoring_org_id
,hru.set_of_books_id
INTO
i_taiv_rec.currency_code
,i_taiv_rec.org_id
,i_taiv_rec.set_of_books_id
FROM okc_k_headers_b CHR
,hr_operating_units hru
WHERE CHR.id = r_contracts_csr.khr_id
AND hru.organization_id = CHR.authoring_org_id;
print_debug('Before inserting into TAI');
-- Insert transaction header record
---------------------------------------------
Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_taiv_rec
,r_taiv_rec);
Okl_Txl_Ar_Inv_Lns_Pub.update_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_tilv_rec
,r_tilv_rec);
-- Insert transaction line record
---------------------------------------------
Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_tilv_rec
,r_tilv_rec);
OKL_IPM_PVT.insert_row
(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_ipm_rec => l_ipm_rec
,x_ipm_rec => lx_ipm_rec);
print_debug (' -- IPM Inserted .');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' -- IPM Inserted .');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'After Inserting into Trx Params - Status: '||x_return_status);
-- Insert transaction line detail record
----------------------------------------------------
Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_tldv_rec
,r_tldv_rec);
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = l_due_date
where khr.id = r_contracts_csr.khr_id;
SELECT rule_information3
FROM okc_rules_b rule,
okc_rule_groups_b rgp
WHERE rgp.id = rule.rgp_id
AND rgp.dnz_chr_id = p_khr_id
AND rgd_code = 'LABILL'
AND rule_information_category = 'LAINVD';
SELECT term.printing_lead_days
FROM okl_k_headers_full_v khr
,hz_customer_profiles cp
,ra_terms term
WHERE khr.id = p_khr_id
AND khr.bill_to_site_use_id = cp.site_use_id
AND cp.standard_terms = term.term_id;
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.kle_id = p_line_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.kle_id = p_line_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.kle_id = p_line_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.kle_id = p_line_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT nvl(sel_int_pmt.amount, 0) interest
FROM okl_strm_type_v sty_int_pmt
,okl_streams_v stm_int_pmt
,okl_strm_elements_v sel_int_pmt
WHERE stm_int_pmt.khr_id = p_khr_id
AND stm_int_pmt.id = sel_int_pmt.stm_id
AND sel_int_pmt.stream_element_date = p_stream_element_date
AND stm_int_pmt.sty_id = sty_int_pmt.id
AND stm_int_pmt.active_yn = 'Y'
AND stm_int_pmt.say_code = 'CURR'
AND sty_int_pmt.stream_type_purpose IN ('INTEREST_PAYMENT', 'VARIABLE_INTEREST');
SELECT nvl(sel_prin_pmt.amount, 0) principal
FROM okl_strm_type_v sty_prin_pmt
,okl_streams_v stm_prin_pmt
,okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = p_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_stream_element_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_PAYMENT';
SELECT principal_basis_code
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, SYSDATE)
AND parameter_type_code = 'ACTUAL';
SELECT start_date, currency_code
FROM okl_k_headers_full_v
WHERE khr_id = p_contract_id;
SELECT MAX(stream_element_date)
FROM OKL_ASSET_STREAMS_UV
WHERE contract_id = p_contract_id
AND line_id = p_line_id
AND stream_element_date < p_start_date
AND stream_type_purpose_code = 'PRINCIPAL_BALANCE';
SELECT MAX(stream_element_date)
FROM OKL_ASSET_STREAMS_UV
WHERE contract_id = p_contract_id
AND stream_element_date < p_start_date
AND stream_type_purpose_code = 'PRINCIPAL_BALANCE';
SELECT amount
FROM OKL_ASSET_STREAMS_UV
WHERE contract_id = p_contract_id
AND line_id = p_line_id
AND stream_element_date = p_stream_element_date
AND stream_type_purpose_code = 'PRINCIPAL_BALANCE';
SELECT SUM(nvl(amount,0))
FROM OKL_ASSET_STREAMS_UV
WHERE contract_id = p_contract_id
AND stream_element_date = p_stream_element_date
AND stream_type_purpose_code = 'PRINCIPAL_BALANCE';
SELECT ppm.revenue_recognition_method,
ppm.interest_calculation_basis
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.pdt_id = ppm.id
AND khr.id = p_contract_id;
SELECT raa.apply_date receipt_date,
sum(raa.amount_applied) principal_pmt_rcpt_amt
FROM
okl_cnsld_ar_strms_b lsm
,okl_cnsld_ar_lines_b lln
,okl_cnsld_ar_hdrs_b cnr
,ar_payment_schedules_all aps
,ar_receivable_applications_all raa
,ar_cash_receipts_all cra
,okl_strm_type_v sty
WHERE lsm.receivables_invoice_id > 0
AND lsm.lln_id = lln.id
AND lln.cnr_id = cnr.id
AND cnr.trx_status_code = 'PROCESSED'
AND lsm.khr_id = p_contract_id
AND lsm.kle_id = NVL(p_line_id, lsm.kle_id)
AND lsm.receivables_invoice_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (raa.application_type = 'CASH' or raa.application_type = 'CM')
AND raa.status = 'APP'
AND raa.apply_date BETWEEN p_start_date AND NVL(p_due_date, raa.apply_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND lsm.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT', 'PRINCIPAL_CATCHUP')
GROUP BY raa.apply_date
ORDER BY raa.apply_date asc;
SELECT raa.apply_date receipt_date
, lsm.kle_id
, sel_ln_pmt.stream_element_date
, sum(raa.amount_applied) loan_pmt_rcpt_amt
, get_prorated_principal_amt(lsm.kle_id, sel_ln_pmt.stream_element_date,
sum(raa.amount_applied), l_currency_code) principal_pmt_rcpt_amt
FROM
okl_cnsld_ar_strms_b lsm,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_hdrs_b cnr,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_v sty_ln_pmt,
okl_strm_elements_v sel_ln_pmt
WHERE lsm.receivables_invoice_id > 0
AND lsm.lln_id = lln.id
AND lln.cnr_id = cnr.id
AND cnr.trx_status_code = 'PROCESSED'
AND lsm.khr_id = NVL(p_contract_id, lsm.khr_id)
AND lsm.kle_id = NVL(p_line_id, lsm.kle_id)
AND lsm.receivables_invoice_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (raa.application_type = 'CASH' or raa.application_type = 'CM')
AND raa.status = 'APP'
AND raa.apply_date BETWEEN p_start_date AND NVL(p_due_date, raa.apply_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND lsm.sty_id = sty_ln_pmt.id
AND ( sty_ln_pmt.stream_type_purpose IN ('LOAN_PAYMENT', 'VARIABLE_LOAN_PAYMENT', 'UNSCHEDULED_LOAN_PAYMENT'))
AND lsm.sel_id = sel_ln_pmt.id
GROUP BY raa.apply_date
, lsm.kle_id
, sel_ln_pmt.stream_element_date
ORDER BY raa.apply_date asc;
SELECT start_date, currency_code
FROM okl_k_headers_full_v
WHERE khr_id = p_contract_id;
SELECT ppm.revenue_recognition_method,
ppm.interest_calculation_basis
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.pdt_id = ppm.id
AND khr.id = p_contract_id;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_b okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date BETWEEN p_start_date AND NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION
SELECT raa.apply_date txn_date,
sum(raa.amount_applied) txn_amount,
'R' txn_date
FROM okl_cnsld_ar_strms_b lsm,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_hdrs_b cnr,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_v sty
WHERE lsm.receivables_invoice_id > 0
AND lsm.lln_id = lln.id
AND lln.cnr_id = cnr.id
AND cnr.trx_status_code = 'PROCESSED'
AND lsm.khr_id = NVL(p_contract_id, lsm.khr_id)
AND lsm.receivables_invoice_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (raa.application_type = 'CASH' or raa.application_type = 'CM')
AND raa.status = 'APP'
AND raa.apply_date BETWEEN p_start_date AND NVL(p_due_date, raa.apply_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND lsm.sty_id = sty.id
AND sty.stream_type_purpose = decode(p_rev_rec_method, 'ACTUAL', 'UNSCHEDULED_LOAN_PAYMENT','UNSCHEDULED_PRINCIPAL_PAYMENT')
GROUP BY raa.apply_date
ORDER BY txn_date asc;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date BETWEEN p_start_date AND NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION
SELECT raa.apply_date txn_date,
sum(raa.amount_applied) txn_amount,
'R' txn_date
FROM okl_cnsld_ar_strms_b lsm,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_hdrs_b cnr,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_v sty
WHERE lsm.receivables_invoice_id > 0
AND lsm.lln_id = lln.id
AND lln.cnr_id = cnr.id
AND cnr.trx_status_code = 'PROCESSED'
AND lsm.khr_id = NVL(p_contract_id, lsm.khr_id)
AND lsm.receivables_invoice_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (raa.application_type = 'CASH' or raa.application_type = 'CM')
AND raa.status = 'APP'
AND raa.apply_date BETWEEN p_start_date AND NVL(p_due_date, raa.apply_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND lsm.sty_id = sty.id
AND sty.stream_type_purpose IN ('VARIABLE_LOAN_PAYMENT','UNSCHEDULED_LOAN_PAYMENT')
GROUP BY raa.apply_date
ORDER BY txn_date asc;
SELECT nvl(principal_basis_code, 'ACTUAL')
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';
SELECT start_date, currency_code
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT sel.amount
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = p_line_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date = p_stream_element_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT SUM(amount)
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date = p_stream_element_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_BALANCE';
SELECT cra.receipt_date receipt_date
,SUM(nvl(ad.amount_cr,0))- SUM(nvl(ad.amount_dr,0)) principal_pmt_rcpt_amt -- 4884843, 4872370
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_b sty,
ar_distributions_all ad
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type IN ('CASH','CM')
AND raa.status = 'APP'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id
GROUP BY cra.receipt_date
UNION ALL
SELECT cra.receipt_date receipt_date
,SUM(raa.line_applied) principal_pmt_rcpt_amt -- 4884843, 4872370
FROM okl_txd_ar_ln_dtls_b tld,
ra_customer_trx_lines_all ractrl,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = p_contract_id
AND tld.khr_id = p_contract_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type IN ('CASH','CM')
AND raa.status = 'APP'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND ad.ref_customer_trx_Line_Id IS NULL)
GROUP BY cra.receipt_date
UNION
SELECT ocb.termination_date receipt_date,
sum(ocb.termination_value_amt) principal_pmt_rcpt_amt
FROM okl_contract_balances ocb
WHERE ocb.khr_id = p_contract_id
AND ocb.kle_id = NVL(p_line_id, kle_id)
AND ocb.termination_date BETWEEN p_start_date AND p_due_date
GROUP BY ocb.termination_date
UNION
SELECT sel.stream_element_date receipt_date,
sum(sel.amount) principal_pmt_rcpt_amt
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = NVL(p_line_id, str.kle_id)
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date BETWEEN p_start_date AND NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
GROUP BY sel.stream_element_date
ORDER BY receipt_date asc;
SELECT sel.stream_element_date receipt_date,
sum(sel.amount) principal_pmt_rcpt_amt
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.kle_id = NVL(p_line_id, str.kle_id)
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
GROUP BY sel.stream_element_date
ORDER BY receipt_date asc;
lx_receipt_tbl.delete;
lx_receipt_tbl.delete;
SELECT start_date, currency_code
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT ppm.revenue_recognition_method
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.pdt_id = ppm.id
AND khr.id = p_contract_id;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date <= NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION
SELECT sel.stream_element_date txn_date,
sum(sel.amount) txn_amount,
'R' txn_type
FROM
okl_strm_elements sel
,okl_streams str
,okl_strm_type_v sty
WHERE sel.stm_id = str.id
AND str.khr_id = p_contract_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= NVL(p_due_date, sel.stream_element_date)
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
GROUP BY sel.stream_element_date
ORDER BY txn_date asc, txn_type;
SELECT iph.check_date txn_date,
sum(iph.amount) txn_amount,
'P' txn_type
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = p_contract_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date <= NVL(p_due_date, iph.check_date)
GROUP BY iph.check_date
UNION
SELECT cra.receipt_date txn_date,
sum(raa.line_applied) txn_amount, -- 4884843, 4872370
'R' txn_type
FROM okl_bpd_tld_ar_lines_v tld,
ar_payment_schedules_all aps,
ar_receivable_applications_all raa,
ar_cash_receipts_all cra,
okl_strm_type_v sty
WHERE tld.trx_status_code = 'PROCESSED'
AND tld.khr_id = NVL(p_contract_id, tld.khr_id)
AND tld.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (raa.application_type = 'CASH' or raa.application_type = 'CM')
AND raa.status = 'APP'
AND cra.receipt_date <= NVL(p_due_date, cra.receipt_date)
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
GROUP BY cra.receipt_date
ORDER BY txn_date asc, txn_type;
lx_receipt_tbl.delete;
SELECT deal_type
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
CURSOR c_int_calc_date_csr(cp_khr_id NUMBER) IS SELECT (max(sel.stream_element_date) - 1) last_interest_calc_date
FROM okl_streams stm,
okl_strm_elements sel,
okl_strm_type_b sty
WHERE stm.khr_id = cp_khr_id
AND stm.id = sel.stm_id
AND stm.sty_id = sty.id
AND (sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL' OR sty.stream_type_purpose = 'DAILY_INTEREST_INTEREST');
CURSOR c_khr_start_date_csr(cp_khr_id NUMBER) IS SELECT start_date - 1
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT krp.interest_index_id
, NVL(krp.base_rate, 0) base_rate
, NVL(krp.interest_start_date, chr.start_date) interest_start_date
, NVL(krp.adder_rate, 0) adder_rate
, NVL(krp.maximum_rate, 9999) maximum_rate
, NVL(krp.minimum_rate, 0) minimum_rate
, krp.rate_delay_code
, NVL(krp.rate_delay_frequency, 0) rate_delay_frequency
, NVL(krp.rate_change_start_date, chr.start_date) rate_change_start_date
, NVL(krp.rate_change_frequency_code, 'DAILY') rate_change_frequency_code
, NVL(krp.rate_change_value, 0) rate_change_value
--if the calling process is Daily Interest, get the last int cal date from
--the Daily Interest streams
--for a REAMORT contract, if the calling process is Reamortization (initiate_request)
--and it is being run for the first time for the contract, the last interest calc date
--defaults to the start date (as there may be a previously derived value due to the fact
--that the Reamort may not have completed in its entirety)
--if it is called from any other process for a REAMORT contract, the last int calc date
--defaults to start date minus 1
--for all other types of contracts, the last int calc date
--defaults to start date minus 1
, decode(p_process_flag, 'DAILY_INTEREST', get_last_int_calc_date(chr.id),
decode(ppm.quality_val, 'REAMORT', NVL(khr.date_last_interim_interest_cal,
decode(OKL_VARIABLE_INTEREST_PVT.G_CALC_METHOD_CODE, 'REAMORT', chr.start_date, chr.start_date-1)), NVL(khr.date_last_interim_interest_cal, chr.start_date - 1))) date_last_interim_interest_cal
, chr.start_date contract_start_date
, chr.end_date contract_end_date
, chr.id khr_id
, ppm.quality_val interest_calculation_basis
, NULL pay_freq
FROM okl_k_rate_params krp
, okl_k_headers khr
, okc_k_headers_b chr
, okl_prod_qlty_val_uv ppm
WHERE krp.rowid = cp_rate_param_rowid
AND krp.khr_id = khr.id
AND khr.id = chr.id
--AND TRUNC(SYSDATE) BETWEEN krp.effective_from_date and NVL(krp.effective_to_date, trunc(SYSDATE))
--AND krp.parameter_type_code = 'ACTUAL'
AND khr.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS';
SELECT vip.interest_rate VALUE,
GREATEST(trunc(cp_start_date),interest_calc_start_date) VALID_FROM,
LEAST(trunc(cp_end_date),NVL(interest_calc_end_date, trunc(sysdate))) VALID_UNTIL
FROM okl_var_int_params vip
WHERE vip.khr_id = cp_khr_id
AND (cp_start_date BETWEEN vip.interest_calc_start_date AND nvl(vip.interest_calc_end_date, trunc(cp_start_date))
OR (cp_end_date BETWEEN vip.interest_calc_start_date AND nvl(vip.interest_calc_end_date, trunc(cp_end_date)))
OR (vip.interest_calc_start_date >= cp_start_date AND nvl(vip.interest_calc_end_date, trunc(sysdate + 9999)) <= cp_end_date) )
AND vip.calc_method_code = NVL(cp_process_flag, vip.calc_method_code)
AND vip.valid_yn = 'Y'
ORDER BY VALID_FROM;
select sll_rulb.object1_id1 pay_freq
from okc_rules_b sll_rulb,
okc_rules_b slh_rulb,
okl_strm_type_b styb,
okc_rule_groups_b rgpb
where sll_rulb.rgp_id = rgpb.id
and sll_rulb.rule_information_category = 'LASLL'
and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
and sll_rulb.object2_id1 = to_char(slh_rulb.id)
and slh_rulb.rgp_id = rgpb.id
and slh_rulb.rule_information_category = 'LASLH'
and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
and styb.id = slh_rulb.object1_id1
and styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
and rgpb.dnz_chr_id = cp_khr_id
and rgpb.rgd_code = 'LALEVL'
order by sll_rulb.rule_information1;
SELECT ive.value VALUE,
GREATEST(trunc(cp_start_date),datetime_valid) VALID_FROM,
LEAST(trunc(cp_end_date),NVL(datetime_invalid, trunc(sysdate))) VALID_UNTIL
FROM okl_indices idx,
okl_index_values ive
WHERE idx.id = cp_index_id
AND idx.id = ive.idx_id
AND (cp_start_date BETWEEN ive.datetime_valid AND nvl(ive.datetime_invalid, trunc(cp_start_date))
OR (cp_end_date BETWEEN ive.datetime_valid AND nvl(ive.datetime_invalid, trunc(cp_end_date)))
OR (ive.datetime_valid >= cp_start_date AND nvl(ive.datetime_invalid, trunc(sysdate + 9999)) <= cp_end_date) )
ORDER BY VALID_FROM;
l_eff_int_tbl.delete;
select interest_calc_start_date
,interest_calc_end_date
,interest_rate
from (select vip.interest_calc_start_date
,vip.interest_calc_end_date
,vip.interest_rate
from okl_var_int_params vip
where vip.khr_id = cp_khr_id
and vip.interest_calc_end_date < cp_from_date
AND vip.calc_method_code = NVL(cp_process_flag, vip.calc_method_code)
AND vip.valid_yn = 'Y'
order by vip.interest_calc_end_date desc)
where rownum = 1;
l_eff_int_with_tol_tbl.delete;
l_int_param_tbl.delete;
l_interest_rate_tbl.delete;
SELECT krp.rowid rate_param_rowid,
GREATEST(trunc(cp_start_date),krp.effective_from_date) start_date,
LEAST(trunc(cp_end_date),NVL(krp.effective_to_date, trunc(sysdate + 9999))) end_date
FROM okl_k_rate_params krp
WHERE krp.khr_id = cp_khr_id
AND krp.parameter_type_code = 'ACTUAL'
AND (cp_start_date BETWEEN krp.effective_from_date AND nvl(krp.effective_to_date, trunc(cp_start_date))
OR (cp_end_date BETWEEN krp.effective_from_date AND nvl(krp.effective_to_date, trunc(cp_end_date)))
OR (krp.effective_from_date >= cp_start_date AND nvl(krp.effective_to_date, trunc(sysdate + 9999)) <= cp_end_date));
l_interest_rate_tbl.delete;
l_interest_rate_tbl_out.delete;
g_vir_tbl(g_vir_tbl_counter).program_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_updated_by := FND_GLOBAL.user_id;
g_vir_tbl(g_vir_tbl_counter).last_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_update_login := FND_GLOBAL.login_id;
SELECT stm.id
FROM okl_streams stm
WHERE stm.khr_id = p_khr_id
AND nvl(stm.kle_id, -9999) = NVL(p_kle_id, -9999)
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.sty_id = p_sty_id;
SELECT okl_sif_seq.nextval
FROM dual;
SELECT SE_LINE_NUMBER
FROM OKL_STRM_ELEMENTS_V
WHERE stm_id = c_stm_id
ORDER BY SE_LINE_NUMBER DESC;
SELECT set_of_books_id
FROM OKL_SYS_ACCT_OPTS;
SELECT id, try_id, description
FROM okl_trx_types_v
WHERE name = p_trx_name;
SELECT lookup_code, meaning, description
FROM fnd_lookups
WHERE lookup_code = p_lookup_code
AND lookup_type = p_lookup_type;
SELECT CONTRACT_NUMBER
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id ;
SELECT ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM OKL_K_HEADERS
WHERE ID = p_khr_id ;
print_debug('Executing procedure OKL_TRX_AR_INVOICES_PUB.INSERT_TRX_AR_INVOICES');
Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
i_taiv_rec,
r_taiv_rec);
print_error_message('Unexpected error raised in call to Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices');
print_error_message('Error raised in call to Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices');
Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
i_tilv_rec,
r_tilv_rec);
print_error_message('Unexpected error raised in call to Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns');
print_error_message('Error raised in call to Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns');
Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
(p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
i_tldv_rec,
r_tldv_rec);
print_error_message('Unexpected error raised in call to Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls');
print_error_message('Error raised in call to Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls');
-- Derive and Insert Distribution Line
------------------------------------------------------------
FND_FILE.PUT_LINE ( FND_FILE.LOG, ' -- Creating Distributions. Supplied parameters:');
OKL_STREAMS_PUB.delete_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_rec => lx_selv_rec);
OKL_STREAMS_PUB.delete_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_rec => lx_stmv_rec);
OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_rec => l_trxH_out_rec);
OKL_STREAMS_PUB.delete_stream_elements(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_selv_rec => lx_selv_rec);
OKL_STREAMS_PUB.delete_streams(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_stmv_rec => lx_stmv_rec);
OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
p_api_version => 1.0,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tcnv_rec => l_trxH_out_rec);
print_debug( 'Program Update date : '|| p_vir_tbl(l_index).program_update_date);
print_debug( 'last_updated_by : '|| p_vir_tbl(l_index).last_updated_by);
print_debug( 'last_update_date : '|| p_vir_tbl(l_index).last_update_date);
print_debug( 'last_update_login : '|| p_vir_tbl(l_index).last_update_login);
INSERT INTO okl_var_int_params VALUES p_vir_tbl(l_index);
print_debug ('No. of records inserted : '|| SQL%rowcount);
print_debug ('Exception during bulk insert');
SELECT 'Y' return_value
FROM okc_k_headers_b chrb,
okc_k_headers_b chrb2,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_old = chrb.id
AND ktrx.tsu_code = 'PROCESSED'
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 chrb.id = cp_khr_id
AND chrb2.orig_system_source_code = 'OKL_REBOOK'
AND chrb2.id = ktrx.khr_id_new
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = chrb.id
AND vpb.source_table = 'OKL_TRX_CONTRACTS'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ktrx.id);
SELECT 'Y' return_value
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
AND stm.id = sel.stm_id
AND sel.stream_element_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT 'Y' return_value
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
AND stm.id = sel.stm_id
AND sel.stream_element_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT 'Y' return_value
FROM okl_txd_ar_ln_dtls_b tld
,ra_customer_trx_lines_all ractrl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,ar_payment_schedules_all aps
,ar_receivable_applications_all raa
,ar_cash_receipts_all cra
,okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = cp_khr_id
AND tld.khr_id = cp_khr_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type IN ('CASH','CM')
AND raa.status = 'APP'
AND cra.receipt_date < cp_from_date
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_id)
AND EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND (ad.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT 'Y' return_value
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = cp_khr_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
AND iph.check_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = okl_inv_ln.khr_id
AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = iph.invoice_payment_id);
SELECT 'Y' return_value
FROM okl_contract_balances ocb
WHERE ocb.khr_id = cp_khr_id
AND ocb.termination_date < cp_from_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = ocb.khr_id
AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ocb.id);
SELECT ktrx.id source_id
,'OKL_TRX_CONTRACTS' source_table
FROM okc_k_headers_b chrb,
okc_k_headers_b chrb2,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_old = chrb.id
AND ktrx.tsu_code = 'PROCESSED'
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 chrb.id = cp_khr_id
AND chrb2.orig_system_source_code = 'OKL_REBOOK'
AND chrb2.id = ktrx.khr_id_new
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = chrb.id
AND vpb.source_table = 'OKL_TRX_CONTRACTS'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ktrx.id);
SELECT sel.id source_id
, 'OKL_STRM_ELEMENTS_V' source_table
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
AND stm.id = sel.stm_id
--AND sel.stream_element_date BETWEEN cp_from_date AND cp_to_date
AND sel.stream_element_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT sel.id source_id
, 'OKL_STRM_ELEMENTS_V' source_table
FROM okl_streams stm,
okl_strm_type_b sty,
okl_strm_elements sel
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
AND stm.id = sel.stm_id
--AND sel.stream_element_date BETWEEN cp_from_date AND cp_to_date
AND sel.stream_element_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = stm.khr_id
AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = sel.id);
SELECT DISTINCT raa.receivable_application_id source_id
, 'AR_RECEIVABLE_APPLICATIONS_ALL' source_table
FROM okl_txd_ar_ln_dtls_b tld
,ra_customer_trx_lines_all ractrl
,okl_txl_ar_inv_lns_b til
,okl_trx_ar_invoices_b tai
,ar_payment_schedules_all aps
,ar_receivable_applications_all raa
,ar_cash_receipts_all cra
,okl_strm_type_b sty
WHERE tai.trx_status_code = 'PROCESSED'
AND tai.khr_id = cp_khr_id
AND tld.khr_id = cp_khr_id
AND ractrl.customer_trx_id = aps.customer_trx_id
AND raa.applied_customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND raa.application_type IN ('CASH','CM')
AND raa.status = 'APP'
AND cra.receipt_date <= cp_to_date
AND raa.cash_receipt_id = cra.cash_receipt_id
AND tld.sty_id = sty.id
AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
AND to_char(tld.id) = ractrl.interface_line_attribute14
AND tld.til_id_details = til.id
AND til.tai_id = tai.id
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = tld.khr_id
AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = raa.receivable_application_id)
AND EXISTS (SELECT 1
FROM ar_distributions_all ad
WHERE raa.receivable_application_id = ad.source_id
AND ad.source_table = 'RA'
AND (ad.ref_customer_trx_Line_Id IS NULL OR
ad.ref_customer_trx_Line_Id = ractrl.customer_trx_line_id));
SELECT iph.invoice_payment_id source_id
, 'AP_INVOICE_PAYMENTS_ALL' source_table
FROM ap_invoices_all ap_inv,
okl_trx_ap_invoices_v okl_inv,
ap_invoice_payment_history_v iph
,okl_cnsld_ap_invs_all cnsld
,okl_txl_ap_inv_lns_all_b okl_inv_ln
,fnd_application fnd_app
WHERE okl_inv.id = okl_inv_ln.tap_id
AND okl_inv_ln.khr_id = cp_khr_id
AND ap_inv.application_id = fnd_app.application_id
AND fnd_app.application_short_name = 'OKL'
AND okl_inv_ln.cnsld_ap_inv_id = cnsld.cnsld_ap_inv_id
AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
AND ap_inv.invoice_id = iph.invoice_id
--AND iph.check_date BETWEEN cp_from_date AND cp_to_date
AND iph.check_date <= cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = okl_inv_ln.khr_id
AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = iph.invoice_payment_id);
SELECT ocb.id source_id
, 'OKL_CONTRACT_BALANCES' source_table
FROM okl_contract_balances ocb
WHERE ocb.khr_id = cp_khr_id
AND ocb.termination_date BETWEEN cp_from_date AND cp_to_date
AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
WHERE vpb.khr_id = ocb.khr_id
AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
AND vpb.int_cal_process = 'VARIABLE_INTEREST'
AND vpb.source_id = ocb.id);
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vpb_tbl(g_vpb_tbl_counter).program_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vpb_tbl(g_vpb_tbl_counter).last_update_date := SYSDATE;
g_vpb_tbl(g_vpb_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
print_debug( 'Program Update date : '|| p_vpb_tbl(l_index).program_update_date);
print_debug( 'last_updated_by : '|| p_vpb_tbl(l_index).last_updated_by);
print_debug( 'last_update_date : '|| p_vpb_tbl(l_index).last_update_date);
print_debug( 'last_update_login : '|| p_vpb_tbl(l_index).last_update_login);
INSERT INTO OKL_VAR_PRINCIPAL_BAL_TXN VALUES p_vpb_tbl(l_index);
print_debug ('No. of records inserted : '|| SQL%rowcount);
print_debug ('Exception during bulk insert');
SELECT id
FROM okl_var_int_process_b
WHERE PARENT_TRX_ID = p_trx_id;
select sll_rulb.dnz_chr_id khr_id,
rgpb.cle_id cle_id,
--LASLL values
sll_rulb.rule_information_category sll_rule_information_category,
to_number(sll_rulb.rule_information1) seq, -- 4899594
sll_rulb.rule_information2 start_date,
sll_rulb.rule_information3 number_periods,
sll_rulb.rule_information4 tuoms_per_period,
sll_rulb.object1_id1 Pay_freq,
sll_rulb.rule_information5 structure,
nvl( sll_rulb.rule_information10,'N') advance_or_arrears,
sll_rulb.rule_information6 amount,
sll_rulb.rule_information7 stub_days,
sll_rulb.rule_information8 stub_amount,
sll_rulb.rule_information13 rate,
sll_rulb.jtot_object1_code time_unit_of_measure,
sll_rulb.jtot_object2_code stream_level_header,
--LASLH values
slh_rulb.rule_information_category slh_rule_information_category,
slh_rulb.jtot_object1_code stream_type_source,
slh_rulb.jtot_object2_code time_value,
slh_rulb.object1_id1 sty_id,
slh_rulb.rule_information1 billing_schedule_type,
slh_rulb.rule_information2 rate_type
from okc_rules_b sll_rulb,
okc_rules_b slh_rulb,
okl_strm_type_b styb,
okc_rule_groups_b rgpb
where sll_rulb.rgp_id = rgpb.id
and sll_rulb.rule_information_category = 'LASLL'
and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
and sll_rulb.object2_id1 = to_char(slh_rulb.id)
and slh_rulb.rgp_id = rgpb.id
and slh_rulb.rule_information_category = 'LASLH'
and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
and styb.id = slh_rulb.object1_id1
and styb.stream_type_purpose IN ('RENT', 'PRINCIPAL_PAYMENT')
and rgpb.dnz_chr_id = p_khr_id
and rgpb.cle_id = p_cle_id
and rgpb.rgd_code = 'LALEVL'
order by to_number(sll_rulb.rule_information1); -- 4899594
select id1 from okl_time_units_v
where name = l_freq;
SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
,currency_code
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
select object1_id1 from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = 'LASLL';
select pg_source_seq.NEXTVAL into l_source from dual;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,1)
where khr.id = p_chr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,3)
where khr.id = p_chr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,6)
where khr.id = p_chr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,12)
where khr.id = p_chr_id;
OKL_VIP_PVT.update_row(
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_vipv_rec => l_vipv_rec,
x_vipv_rec => x_vipv_rec);
SELECT id
FROM okl_k_lines_full_v
WHERE chr_id = p_khr_id
AND lse_id = G_FIN_LINE_LTY_ID
AND nvl(date_terminated, p_due_date + 1) > p_due_date
ORDER BY id;
g_vir_tbl.delete;
SELECT interest_basis_code, calculation_formula_id
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';
SELECT name
FROM okl_formulae_v
WHERE id = p_formula_id;
SELECT ppm.interest_calculation_basis
FROM okl_k_headers khr,
okl_product_parameters_v ppm
WHERE khr.pdt_id = ppm.id
AND khr.id = p_contract_id;
l_program_update_date DATE;
SELECT okc.contract_number
, NVL(okl.date_last_interim_interest_cal, okc.start_date) start_date
, okl.deal_type deal_type
, ppm.quality_val interest_calculation_basis
, okc.authoring_org_id
, round(months_between(okc.end_date, okl.date_last_interim_interest_cal)) remaining_term_in_months
FROM okc_k_headers_b okc
, okl_k_headers okl
, okl_prod_qlty_val_uv ppm
WHERE okc.id = cp_khr_id
AND okl.id = okc.id
AND okl.pdt_id = ppm.pdt_id
AND ppm.quality_name = 'INTEREST_CALCULATION_BASIS';
SELECT max(sel.stream_element_date)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_b sty,
okc_k_headers_b khr
WHERE stm.khr_id = p_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND sty.billable_yn = 'Y'
AND sel.stm_id = stm.id
and sel.stream_element_date <= p_reamort_date
and stm.khr_id = khr.id;*/
SELECT (KHR.TERM_DURATION - round(months_between(nvl(p_period_date,K.START_DATE), K.START_DATE))) MTH,
KHR.TERM_DURATION term_duration
FROM OKC_K_HEADERS_B K, OKL_K_HEADERS KHR
WHERE K.id = p_contract_id
and K.id = KHR.id;
select object1_id1 from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = 'LASLL';
SELECT interest_rate,interest_calc_end_date
FROM okl_var_int_params
WHERE khr_id = p_contract_id
AND INTEREST_CALC_END_DATE = (SELECT max(INTEREST_CALC_END_DATE) FROM okl_var_int_params WHERE khr_id = p_contract_id);
SELECT NVL(date_last_interim_interest_cal, start_date) reamort_date
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
l_csm_stream_types_tbl.DELETE;
l_rents_prin_tbl.delete;
l_rents_tbl_in.delete;
l_req_stream_types_tbl.delete;
l_csm_line_details_tbl.delete(l_row_counter);
OKL_VIP_PVT.insert_row(
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_vipv_rec => l_vipv_rec,
x_vipv_rec => x_vipv_rec);
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,1)
where khr.id = p_khr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,3)
where khr.id = p_khr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,6)
where khr.id = p_khr_id;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = add_months(l_reamort_date,12)
where khr.id = p_khr_id;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
INTO
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
FROM dual;
g_vir_tbl.delete;
g_vir_tbl(g_vir_tbl_counter).program_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_updated_by := FND_GLOBAL.USER_ID;
g_vir_tbl(g_vir_tbl_counter).last_update_date := SYSDATE;
g_vir_tbl(g_vir_tbl_counter).last_update_login := FND_GLOBAL.LOGIN_ID;
g_vir_tbl.delete;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = p_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date <= p_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = p_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = p_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = p_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = p_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
g_vir_tbl.delete;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sel_prin_pmt.sel_id IS NULL
AND sty_prin_pmt.stream_type_purpose = 'VARIABLE_LOAN_PAYMENT';
SELECT id
FROM okl_k_lines_full_v
WHERE chr_id = cp_khr_id
AND lse_id = G_FIN_LINE_LTY_ID
AND nvl(date_terminated, cp_due_date + 1) > cp_due_date
ORDER BY id;
SELECT sel.id,
sel.stream_element_date,
sel.amount
FROM okl_strm_type_v sty,
okl_streams str,
okl_strm_elements sel
WHERE sel.stm_id = str.id
AND str.khr_id = cp_khr_id
AND str.kle_id = cp_kle_id
AND str.say_code = 'CURR'
AND str.active_yn = 'Y'
AND sel.stream_element_date <= cp_due_date
AND str.sty_id = sty.id
AND sty.stream_type_purpose = 'PRINCIPAL_PAYMENT'
AND NOT EXISTS (
SELECT 'X'
FROM okl_strm_elements selc
WHERE selc.sel_id = sel.id)
ORDER BY stream_element_date;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date = cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = cp_stream_type_purpose ; -- 'PRINCIPAL_PAYMENT'
g_vir_tbl.delete;
g_vpb_tbl.delete;
g_vpb_tbl.delete;
SELECT fml.name
FROM okl_formulae_v fml
WHERE fml.id = p_formula_id;
SELECT chrb.id contract_id, cleb.id kle_id,
selb.stream_element_date, selb.amount amount
FROM okc_k_headers_b chrb, okc_k_lines_b cleb,
okc_line_styles_b lseb, okl_strm_type_b styb,
okl_strm_elements selb, okl_streams stmb
WHERE cleb.dnz_chr_id = chrb.id
AND cleb.chr_id = chrb.id
AND chrb.id = p_contract_id
AND cleb.lse_id = lseb.id
AND lseb.lty_code = 'FREE_FORM1'
AND stmb.khr_id = chrb.id
AND stmb.kle_id = cleb.id
AND stmb.sty_id = styb.id
AND selb.stm_id = stmb.id
AND chrb.id = stmb.khr_id
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
AND styb.stream_type_purpose = 'RENT'
--fix for bug # 4940113
--AND selb.date_billed IS NOT NULL
--change on 15 Nov 2005 by pgomes for bug fix 4740293
--AND selb.stream_element_date BETWEEN p_from_date AND NVL(p_to_date,SYSDATE);
UPDATE okl_k_headers
SET date_last_interim_interest_cal = l_stream_element_date
WHERE id = p_contract_id;
select khr.date_last_interim_interest_cal date_last_interim_interest_cal
, NVL(rpm.catchup_start_date, khr.start_date) catchup_start_date
, NVL(rpm.catchup_frequency_code, 'MONTHLY') catchup_frequency_code
, NVL(khr.date_terminated, khr.end_date) end_date
from okl_k_headers_full_v khr
, okl_k_rate_params rpm
where khr.id = rpm.khr_id
and khr.id = cp_khr_id;
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose in ('INTEREST_CATCHUP', 'INTEREST_PAYMENT') ; -- 'PRINCIPAL_PAYMENT'
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date >= cp_start_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose in ('INTEREST_CATCHUP', 'INTEREST_PAYMENT') ; -- 'PRINCIPAL_PAYMENT'
SELECT nvl(SUM(nvl(sel_prin_pmt.amount, 0)),0) pmt_amt
FROM okl_strm_type_v sty_prin_pmt,
okl_streams_v stm_prin_pmt,
okl_strm_elements_v sel_prin_pmt
WHERE stm_prin_pmt.khr_id = cp_khr_id
AND stm_prin_pmt.id = sel_prin_pmt.stm_id
AND sel_prin_pmt.stream_element_date BETWEEN cp_start_date AND cp_due_date
AND stm_prin_pmt.sty_id = sty_prin_pmt.id
AND stm_prin_pmt.active_yn = 'Y'
AND stm_prin_pmt.say_code = 'CURR'
AND sty_prin_pmt.stream_type_purpose = 'PRINCIPAL_CATCHUP' ;
SELECT catchup_settlement_code
FROM okl_k_rate_params
WHERE khr_id = p_khr_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';
SELECT id
FROM okl_k_lines_full_v
WHERE chr_id = p_khr_id
AND lse_id = G_FIN_LINE_LTY_ID
ORDER BY id;
g_vir_tbl.delete;
g_vpb_tbl.delete;
g_vpb_tbl.delete;
SELECT 'Y'
FROM okl_var_int_params
WHERE khr_id = p_chr_id
AND request_id = p_req_id;
SELECT distinct khr.id khr_id,
khr.deal_type,
khr.date_last_interim_interest_cal,
chr.contract_number,
chr.start_date start_date,
chr.date_terminated termination_date,
chr.end_date end_date,
chr.currency_code,
ppm.interest_calculation_basis,
ppm.revenue_recognition_method,
chr.authoring_org_id,
khr.pdt_id,
--Bug# 7277007
ppm.name product_name
FROM okc_k_headers_b chr,
okl_k_headers khr,
okc_statuses_b khs,
okl_product_parameters_v ppm
WHERE CHR.CONTRACT_NUMBER = NVL(p_contract_number,CHR.CONTRACT_NUMBER)
AND khr.deal_type IN ('LOAN','LEASEDF','LOAN-REVOLVING','LEASEST','LEASEOP')
AND chr.id = khr.id
AND khs.code = chr.sts_code
AND khs.ste_code = 'ACTIVE'
AND khr.pdt_id = ppm.id
AND ppm.interest_calculation_basis <> 'FIXED'
ORDER BY khr.deal_type, chr.contract_number;
SELECT interest_basis_code,
calculation_formula_id,
nvl(principal_basis_code, 'ACTUAL'),
days_in_a_month_code,
days_in_a_year_code,
rate_change_value,
catchup_settlement_code
FROM okl_k_rate_params
WHERE khr_id = p_contract_id
AND p_effective_date BETWEEN effective_from_date and nvl(effective_to_date, p_effective_date)
AND parameter_type_code = 'ACTUAL';
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = l_due_date
WHERE khr.id = G_CONTRACT_ID;
UPDATE okl_k_headers khr
SET khr.date_last_interim_interest_cal = l_catchup_date
where khr.id = G_CONTRACT_ID;