[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT stm.id STY_ID
, stm.code sty_code
, caf.sts_code status
, caf.dnz_khr_id khr_id
, (nvl(sum(amount * number_of_periods),0) + nvl(sum(stub_amount),0)) Total
FROM okl_cash_flows caf
, okl_strm_type_b stm
, okl_cash_flow_levels cfl
WHERE dnz_qte_id = cp_qte_id
AND caf.sty_id = stm.id
AND caf.id = cfl.caf_id
AND caf.sts_code IN ( G_CURRENT_STATUS, G_PROPOSED_STATUS)
AND caf.cft_code = G_CASH_FLOW_TYPE
GROUP BY stm.id
, stm.code
, caf.sts_code
, caf.dnz_khr_id
ORDER BY stm.id;
| 22-Apr-2005 4299668 PAGARG Instead of calling insert for each cash flow object,
| store it in table and finally call bulk insert
*=======================================================================*/
PROCEDURE create_cash_flow_object(p_api_version IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_obj_type_code IN VARCHAR2,
p_src_table IN VARCHAR2,
p_src_id IN NUMBER,
p_base_src_id IN NUMBER,
p_sts_code IN VARCHAR2,
x_cfo_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
--This cursor checks if an object already exists
CURSOR l_cash_flow_objects_csr(cp_oty_code IN VARCHAR2, cp_source_table IN VARCHAR2,
cp_source_id IN NUMBER, cp_sts_code IN VARCHAR2,
cp_base_src_id IN NUMBER) IS
--SELECT cfo.id
SELECT 'x'
FROM okl_cash_flow_objects cfo, okl_cash_flows caf, OKL_TRX_QTE_CF_OBJECTS qco
WHERE cfo.id = caf.cfo_id
AND cfo.id = qco.cfo_id
AND cfo.oty_code = cp_oty_code
AND cfo.source_table = cp_source_table
AND cfo.source_id = cp_source_id
AND caf.sts_code = cp_sts_code
AND qco.base_source_id = cp_base_src_id;
| 22-Apr-2005 4299668 PAGARG Instead of calling insert for each cash flow
| and cash flow level, store it in table and
| finally call bulk insert
*=======================================================================*/
PROCEDURE create_cash_flows(p_api_version IN NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cashflow_rec IN cashflow_rec_type,
px_new_cash_flow IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
-- This cursor checks if a cash flow header has already been created for a stream type (payment type)
CURSOR l_cashflow_csr(cp_cfo_id IN NUMBER, cp_sty_id IN NUMBER) IS
SELECT id
FROM okl_cash_flows
WHERE cfo_id = cp_cfo_id
AND sty_id = cp_sty_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
IF l_caf_id IS NULL THEN -- Stream type has not been inserted yet in the cash flow header
lp_cafv_rec.cfo_id := p_cashflow_rec.p_cfo_id;
| 22-Apr-2005 PAGARG Bug 4299668 Instead of calling insert for each quote
| cash flow object, prepare table of records and finally
| call bulk insert for all four objects
*=======================================================================*/
PROCEDURE get_current_payments(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_quote_id IN NUMBER,
p_khr_id IN NUMBER) AS
/*-----------------------------------------------------------------------+
| Cursor Declarations |
+-----------------------------------------------------------------------*/
-- get the current contract level payments
CURSOR l_kpayments_csr(cp_chr_id IN NUMBER) IS
SELECT rgp.cle_id cle_id,
sttyp.id1 sty_id,
sttyp.code stream_type,
tuom.id1 frequency,
sll_rul.rule_information1 seq_num,
sll_rul.rule_information2 start_date,
sll_rul.rule_information3 period_in_months,
sll_rul.rule_information5 advance_periods,
sll_rul.rule_information6 amount,
sll_rul.rule_information10 due_arrears_yn,
sll_rul.rule_information7 stub_days,
sll_rul.rule_information8 stub_amount,
rgp.dnz_chr_id
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND rgp.dnz_chr_id = cp_chr_id
AND rgp.cle_id IS NULL
ORDER BY stream_type, start_date;
SELECT cle.id, cle.lse_id, lse.lty_code
FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND cle.sts_code IN ('BOOKED', 'TERMINATED')
AND chr_id = cp_chr_id;
SELECT rgp.cle_id cle_id,
sttyp.id1 sty_id,
sttyp.code stream_type,
tuom.id1 frequency,
sll_rul.rule_information1 seq_num,
sll_rul.rule_information2 start_date,
sll_rul.rule_information3 period_in_months,
sll_rul.rule_information5 advance_periods,
sll_rul.rule_information6 amount,
sll_rul.rule_information10 due_arrears_yn,
sll_rul.rule_information7 stub_days,
sll_rul.rule_information8 stub_amount,
rgp.dnz_chr_id
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = cp_cle_id
ORDER BY stream_type, start_date;
SELECT cim.object1_id1, cle.id
FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
WHERE cle.lse_id = lse.id
AND lse.lty_code = cp_line_type_code
AND cim.cle_id = cle.id
AND cle.cle_id = cp_line_id;
okl_cfo_pvt.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cfov_tbl => g_cfov_tbl_type,
x_cfov_tbl => gx_cfov_tbl_type);
'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
OKL_QCO_PVT.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qcov_tbl => g_qcov_tbl_type,
x_qcov_tbl => gx_qcov_tbl_type);
'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
okl_caf_pvt.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_tbl => g_cafv_tbl_type,
x_cafv_tbl => gx_cafv_tbl_type);
'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
OKL_CFL_PVT.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_tbl => g_cflv_tbl_type,
x_cflv_tbl => gx_cflv_tbl_type);
'after call to okl_cfo_pvt.insert_row_bulk :'||l_return_status);
SELECT caf.id, caf.sty_id, nvl(caf.due_arrears_yn, 'N') due_arrears_yn, cfl.start_date, caf.number_of_advance_periods,
cfl.amount, cfl.number_of_periods, cfl.fqy_code, cfl.level_sequence, cfl.stub_days, cfl.stub_amount
FROM okl_cash_flows caf, okl_cash_flow_levels cfl
WHERE cfo_id = cp_cfo_id
AND caf.id = cfl.caf_id
AND caf.sts_code = G_CURRENT_STATUS
AND caf.cft_code = G_CASH_FLOW_TYPE
ORDER BY caf.sty_id, cfl.start_date;
SELECT okl_lla_util_pvt.calculate_end_date
(cp_firstperiodstartdt,
cp_number_of_months,
to_char(cp_firstperiodstartdt, 'DD') ) +1
FROM dual;
SELECT months_between(cp_quote_eff_dt,cp_period_start_dt)
FROM DUAL;
SELECT cim.object1_id1, cle.id
FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
WHERE cle.lse_id = lse.id
AND lse.lty_code = cp_line_type_code
AND cim.cle_id = cle.id
AND cle.cle_id = cp_line_id
AND cle.sts_code = 'BOOKED';
SELECT id,
--kle_id,
asset_quantity, quote_quantity
FROM okl_txl_quote_lines_b
WHERE qte_id = cp_quote_id
AND qlt_code = 'AMCFIA'
AND kle_id = cp_kle_id;
SELECT cim.object1_id1
FROM okc_k_lines_b cle, okc_line_styles_b lse, okc_k_items cim
WHERE cle.lse_id = lse.id
AND lse.lty_code = cp_line_type
AND cim.cle_id = cle.id
AND cle.id = cp_fee_serviced_asset_line_id;
SELECT DISTINCT cfo.id, qco.base_source_id
FROM okl_cash_flow_objects cfo, okl_cash_flows caf, OKL_TRX_QTE_CF_OBJECTS qco
WHERE cfo.id = caf.cfo_id
AND cfo.id = qco.cfo_id
AND cfo.oty_code = cp_oty_code
AND cfo.source_table = G_OBJECT_SRC_TABLE
AND cfo.source_id = cp_quote_id
AND caf.sts_code = G_CURRENT_STATUS
AND caf.cft_code = G_CASH_FLOW_TYPE;
SELECT caf.sty_id, caf.due_arrears_yn, cfl.start_date, caf.number_of_advance_periods, cfl.amount,
cfl.number_of_periods, cfl.fqy_code, cfl.level_Sequence, cfl.stub_days, cfl.stub_amount
FROM okl_cash_flows caf, okl_cash_flow_levels cfl
WHERE caf.id = cfl.caf_id
AND caf.cfo_id = cp_obj_id
ORDER BY caf.sty_id, cfl.start_date;
okl_cfo_pvt.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cfov_tbl => g_cfov_tbl_type,
x_cfov_tbl => gx_cfov_tbl_type);
OKL_QCO_PVT.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qcov_tbl => g_qcov_tbl_type,
x_qcov_tbl => gx_qcov_tbl_type);
okl_caf_pvt.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_tbl => g_cafv_tbl_type,
x_cafv_tbl => gx_cafv_tbl_type);
OKL_CFL_PVT.insert_row_bulk(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_tbl => g_cflv_tbl_type,
x_cflv_tbl => gx_cflv_tbl_type);
SELECT khr_id, trunc(date_effective_from)
FROM okl_trx_quotes_b
WHERE id = cp_id;
g_cfov_tbl_type.delete;
g_cafv_tbl_type.delete;
g_cflv_tbl_type.delete;
g_qcov_tbl_type.delete;
g_cfov_tbl_type.delete;
g_cafv_tbl_type.delete;
g_cflv_tbl_type.delete;
g_qcov_tbl_type.delete;