[Home] [Help]
4475: --
4476: AND chrb.id = cp_khr_id
4477: AND chrb2.orig_system_source_code = 'OKL_REBOOK'
4478: AND chrb2.id = ktrx.khr_id_new
4479: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4480: WHERE vpb.khr_id = chrb.id
4481: AND vpb.source_table = 'OKL_TRX_CONTRACTS'
4482: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4483: AND vpb.source_id = ktrx.id);
4493: AND stm.sty_id = sty.id
4494: AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
4495: AND stm.id = sel.stm_id
4496: AND sel.stream_element_date < cp_from_date
4497: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4498: WHERE vpb.khr_id = stm.khr_id
4499: AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
4500: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4501: AND vpb.source_id = sel.id);
4511: AND stm.sty_id = sty.id
4512: AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
4513: AND stm.id = sel.stm_id
4514: AND sel.stream_element_date < cp_from_date
4515: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4516: WHERE vpb.khr_id = stm.khr_id
4517: AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
4518: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4519: AND vpb.source_id = sel.id);
4549: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
4550: AND to_char(tld.id) = ractrl.interface_line_attribute14
4551: AND tld.til_id_details = til.id
4552: AND til.tai_id = tai.id
4553: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4554: WHERE vpb.khr_id = tld.khr_id
4555: AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
4556: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4557: AND vpb.source_id = raa.receivable_application_id)
4582: AND cnsld.cnsld_ap_inv_id = to_number(ap_inv.reference_key1)
4583: AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
4584: AND ap_inv.invoice_id = iph.invoice_id
4585: AND iph.check_date < cp_from_date
4586: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4587: WHERE vpb.khr_id = okl_inv_ln.khr_id
4588: AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
4589: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4590: AND vpb.source_id = iph.invoice_payment_id);
4595: SELECT 'Y' return_value
4596: FROM okl_contract_balances ocb
4597: WHERE ocb.khr_id = cp_khr_id
4598: AND ocb.termination_date < cp_from_date
4599: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4600: WHERE vpb.khr_id = ocb.khr_id
4601: AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
4602: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4603: AND vpb.source_id = ocb.id);
4630: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
4631: AND to_char(tld.id) = ractrl.interface_line_attribute14
4632: AND tld.til_id_details = til.id
4633: AND til.tai_id = tai.id
4634: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4635: WHERE vpb.khr_id = tld.khr_id
4636: AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
4637: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4638: AND vpb.source_id = raa.receivable_application_id)
4667: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
4668: AND to_char(tld.id) = ractrl.interface_line_attribute14
4669: AND tld.til_id_details = til.id
4670: AND til.tai_id = tai.id
4671: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4672: WHERE vpb.khr_id = tld.khr_id
4673: AND vpb.source_table = 'AR_ADJUSTMENTS_ALL'
4674: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4675: AND vpb.source_id = adj.adjustment_id);
4874:
4875: l_api_name CONSTANT VARCHAR2(30) := 'populate_txns';
4876: l_api_version CONSTANT NUMBER := 1.0;
4877: L_FETCH_SIZE CONSTANT NUMBER := 100;
4878: l_source_id OKL_VAR_PRINCIPAL_BAL_TXN.source_id%TYPE;
4879: l_source_table OKL_VAR_PRINCIPAL_BAL_TXN.source_table%TYPE;
4880:
4881: --get the id's of Rebook Transactions
4882: CURSOR l_rebook_csr(cp_khr_id NUMBER) IS
4875: l_api_name CONSTANT VARCHAR2(30) := 'populate_txns';
4876: l_api_version CONSTANT NUMBER := 1.0;
4877: L_FETCH_SIZE CONSTANT NUMBER := 100;
4878: l_source_id OKL_VAR_PRINCIPAL_BAL_TXN.source_id%TYPE;
4879: l_source_table OKL_VAR_PRINCIPAL_BAL_TXN.source_table%TYPE;
4880:
4881: --get the id's of Rebook Transactions
4882: CURSOR l_rebook_csr(cp_khr_id NUMBER) IS
4883: SELECT ktrx.id source_id
4894: --
4895: AND chrb.id = cp_khr_id
4896: AND chrb2.orig_system_source_code = 'OKL_REBOOK'
4897: AND chrb2.id = ktrx.khr_id_new
4898: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4899: WHERE vpb.khr_id = chrb.id
4900: AND vpb.source_table = 'OKL_TRX_CONTRACTS'
4901: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4902: AND vpb.source_id = ktrx.id);
4913: AND sty.stream_type_purpose = 'DAILY_INTEREST_PRINCIPAL'
4914: AND stm.id = sel.stm_id
4915: --AND sel.stream_element_date BETWEEN cp_from_date AND cp_to_date
4916: AND sel.stream_element_date <= cp_to_date
4917: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4918: WHERE vpb.khr_id = stm.khr_id
4919: AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
4920: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4921: AND vpb.source_id = sel.id);
4932: AND sty.stream_type_purpose = 'PRINCIPAL_CATCHUP'
4933: AND stm.id = sel.stm_id
4934: --AND sel.stream_element_date BETWEEN cp_from_date AND cp_to_date
4935: AND sel.stream_element_date <= cp_to_date
4936: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4937: WHERE vpb.khr_id = stm.khr_id
4938: AND vpb.source_table = 'OKL_STRM_ELEMENTS_V'
4939: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4940: AND vpb.source_id = sel.id);
4971: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
4972: AND to_char(tld.id) = ractrl.interface_line_attribute14
4973: AND tld.til_id_details = til.id
4974: AND til.tai_id = tai.id
4975: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
4976: WHERE vpb.khr_id = tld.khr_id
4977: AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
4978: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
4979: AND vpb.source_id = raa.receivable_application_id)
5005: AND okl_inv.funding_type_code = 'BORROWER_PAYMENT'
5006: AND ap_inv.invoice_id = iph.invoice_id
5007: --AND iph.check_date BETWEEN cp_from_date AND cp_to_date
5008: AND iph.check_date <= cp_to_date
5009: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
5010: WHERE vpb.khr_id = okl_inv_ln.khr_id
5011: AND vpb.source_table = 'AP_INVOICE_PAYMENTS_ALL'
5012: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
5013: AND vpb.source_id = iph.invoice_payment_id);
5019: , 'OKL_CONTRACT_BALANCES' source_table
5020: FROM okl_contract_balances ocb
5021: WHERE ocb.khr_id = cp_khr_id
5022: AND ocb.termination_date BETWEEN cp_from_date AND cp_to_date
5023: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
5024: WHERE vpb.khr_id = ocb.khr_id
5025: AND vpb.source_table = 'OKL_CONTRACT_BALANCES'
5026: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
5027: AND vpb.source_id = ocb.id);
5055: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
5056: AND to_char(tld.id) = ractrl.interface_line_attribute14
5057: AND tld.til_id_details = til.id
5058: AND til.tai_id = tai.id
5059: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
5060: WHERE vpb.khr_id = tld.khr_id
5061: AND vpb.source_table = 'AR_RECEIVABLE_APPLICATIONS_ALL'
5062: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
5063: AND vpb.source_id = raa.receivable_application_id)
5092: AND sty.stream_type_purpose IN ('PRINCIPAL_PAYMENT', 'UNSCHEDULED_PRINCIPAL_PAYMENT')
5093: AND to_char(tld.id) = ractrl.interface_line_attribute14
5094: AND tld.til_id_details = til.id
5095: AND til.tai_id = tai.id
5096: AND NOT EXISTS (select 1 FROM OKL_VAR_PRINCIPAL_BAL_TXN vpb
5097: WHERE vpb.khr_id = tld.khr_id
5098: AND vpb.source_table = 'AR_ADJUSTMENTS_ALL'
5099: AND vpb.int_cal_process = 'VARIABLE_INTEREST'
5100: AND vpb.source_id = adj.adjustment_id);