[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CHR.scs_code,
khr.deal_type
FROM okc_k_headers_v CHR,
okl_k_headers_v khr
WHERE CHR.ID = p_khr_id AND khr.ID = CHR.ID;
PROCEDURE update_quote_status(p_term_rec IN term_rec_type) IS
lp_qtev_rec OKL_TRX_QUOTES_PUB.qtev_rec_type;
:= g_module_name || 'update_quote_status';
SELECT tmt_status_code
FROM okl_trx_contracts trx
WHERE trx.qte_id = p_qte_id
--rkuttiya added for 12.1.1 Multi GAAP Project
AND trx.representation_type = 'PRIMARY';
SELECT qst_code
FROM okl_trx_quotes_v
WHERE id = p_qte_id
AND (qtp_code LIKE 'TER%' OR qtp_code LIKE 'RES%');
OKL_TRX_QUOTES_PUB.update_trx_quotes (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data,
p_qtev_rec => lp_qtev_rec,
x_qtev_rec => lx_qtev_rec);
END update_quote_status;
SELECT qte.qtp_code qtp_code,
qte.qrs_code qrs_code
FROM okl_trx_quotes_v qte
WHERE qte.ID = p_quote_id;
SELECT khr.contract_number contract_number
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id;
ELSIF (p_term_rec.p_control_flag = 'TRMNT_QUOTE_UPDATE')
THEN
-- Get the lookup meaning for quote type
l_quote_type :=
okl_am_util_pvt.get_lookup_meaning
(p_lookup_type => 'OKL_QUOTE_TYPE',
p_lookup_code => p_term_rec.p_quote_type,
p_validate_yn => g_yes
);
ELSIF (p_tmt_flag = 'TMT_CONTRACT_UPDATED_YN')
THEN
px_tcnv_rec.tmt_contract_updated_yn := g_yes;
ELSIF (p_tmt_flag = 'TMT_STREAMS_UPDATED_YN')
THEN
px_tcnv_rec.tmt_streams_updated_yn := g_yes;
ELSIF (p_tmt_flag = 'TMT_CONTRACT_UPDATED_YN')
THEN
px_tcnv_rec.tmt_contract_updated_yn := g_no;
ELSIF (p_tmt_flag = 'TMT_STREAMS_UPDATED_YN')
THEN
px_tcnv_rec.tmt_streams_updated_yn := g_no;
ELSIF (p_tmt_flag = 'TMT_CONTRACT_UPDATED_YN')
THEN
px_tcnv_rec.tmt_contract_updated_yn := p_ret_val;
ELSIF (p_tmt_flag = 'TMT_STREAMS_UPDATED_YN')
THEN
px_tcnv_rec.tmt_streams_updated_yn := p_ret_val;
IF p_trn_mode = 'INSERT'
THEN
IF (is_debug_statement_on)
THEN
okl_debug_pub.log_debug
(g_level_statement,
l_module_name,
'calling OKL_TRX_CONTRACTS_PUB.create_trx_contracts'
);
ELSIF p_trn_mode = 'UPDATE'
THEN
IF (is_debug_statement_on)
THEN
okl_debug_pub.log_debug
(g_level_statement,
l_module_name,
'calling OKL_TRX_CONTRACTS_PUB.update_trx_contracts'
);
okl_trx_contracts_pub.update_trx_contracts
(p_api_version => l_api_version,
p_init_msg_list => g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tcnv_rec => lp_tcnv_rec,
x_tcnv_rec => lx_tcnv_rec
);
'called OKL_TRX_CONTRACTS_PUB.update_trx_contracts , return status: '
|| l_return_status
);
SELECT kle.ID kle_id,
kle.NAME asset_name,
tql.asset_quantity asset_quantity,
tql.ID tql_id,
tql.quote_quantity quote_quantity,
tql.split_kle_name split_kle_name -- RMUNJULU 2757312
FROM okl_txl_quote_lines_v tql,
okc_k_lines_v kle
WHERE tql.qte_id = p_qte_id
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = kle.ID;
SELECT khr.sts_code sts_code
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id;
SELECT kle.sts_code sts_code
FROM okc_k_lines_v kle
WHERE kle.ID = p_kle_id;
SELECT COUNT (txd.ID) ib_lines_count
FROM okl_txd_quote_line_dtls txd
WHERE txd.tql_id = p_tql_id;
SELECT txd.kle_id
FROM okl_txd_quote_line_dtls txd
WHERE txd.tql_id = p_tql_id;
'calling OKL_TXD_ASSETS_PUB.update_txd_asset_def'
);
okl_txd_assets_pub.update_txd_asset_def
(p_api_version => l_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_adpv_rec => lx_txd_assets_rec,
x_adpv_rec => lx_txdv_rec
);
'called OKL_TXD_ASSETS_PUB.update_txd_asset_def , return status: '
|| x_return_status
);
SELECT COUNT (txd.ID) ib_lines_count
FROM okl_txd_quote_line_dtls txd
WHERE txd.tql_id = p_tql_id;
SELECT txd.kle_id
FROM okl_txd_quote_line_dtls txd
WHERE txd.tql_id = p_tql_id;
SELECT kle.NAME NAME
FROM okc_k_lines_v kle
WHERE kle.ID = p_kle_id;
SELECT 'N',
txl.ID
FROM okl_txl_assets_b txl,
okl_trx_assets trx,
okc_k_lines_v kle_fin,
okc_k_lines_v kle_fix,
okc_line_styles_b lty_fin,
okc_line_styles_b lty_fix
WHERE txl.tal_type = 'ALI'
AND trx.tsu_code = 'ENTERED'
AND txl.tas_id = trx.ID
AND kle_fin.lse_id = lty_fin.ID
AND lty_fin.lty_code = 'FREE_FORM1'
AND kle_fin.ID = kle_fix.cle_id
AND kle_fix.lse_id = lty_fix.ID
AND lty_fix.lty_code = 'FIXED_ASSET'
AND txl.kle_id = kle_fix.ID
AND kle_fin.ID = p_kle_id;
SELECT asset_number
FROM okl_txd_assets_b
WHERE tal_id = p_tal_id AND target_kle_id IS NULL;
SELECT 'Y' a
FROM okx_assets_v okx
WHERE okx.asset_number = p_asset_number;
SELECT 'Y' a
FROM okl_k_lines_full_v kle,
okc_line_styles_b lse
WHERE kle.NAME = p_asset_number
AND kle.lse_id = lse.ID
AND lse.lty_code = 'FIXED_ASSET';
'calling OKL_TXL_QUOTE_LINES_PUB.update_txl_quote_lines'
);
okl_txl_quote_lines_pub.update_txl_quote_lines
(p_api_version => l_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tqlv_tbl => lp_tqlv_tbl,
x_tqlv_tbl => lx_tqlv_tbl
);
'called OKL_TXL_QUOTE_LINES_PUB.update_txl_quote_lines , return status: '
|| x_return_status
);
SELECT stm.ID ID
FROM okl_streams_v stm
WHERE stm.kle_id = p_kle_id AND stm.say_code = 'CURR';
'In param, px_tcnv_rec.tmt_streams_updated_yn: '
|| px_tcnv_rec.tmt_streams_updated_yn
);
AND NVL (px_tcnv_rec.tmt_streams_updated_yn, '?') <> g_yes
)
OR (p_trn_already_set = g_no)
THEN
-- if streams found then
IF (l_streams_found = g_yes)
THEN
j := 1;
'calling OKL_STREAMS_PUB.update_streams'
);
okl_streams_pub.update_streams
(p_api_version => l_api_version,
p_init_msg_list => g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_stmv_tbl => lp_stmv_tbl,
x_stmv_tbl => lx_stmv_tbl
);
'called OKL_STREAMS_PUB.update_streams , return status: '
|| l_return_status
);
p_tmt_flag => 'TMT_STREAMS_UPDATED_YN',
p_tsu_code => 'WORKING',
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_STREAMS_UPDATED_YN',
p_tsu_code => 'WORKING',
p_ret_val => NULL,
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_STREAMS_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_STREAMS_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_STREAMS_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
SELECT khr.pdt_id pdt_id,
CHR.scs_code -- rmunjulu 4622198
,
CHR.org_id --akrangan added for sla ae uptake
FROM okl_k_headers_v khr,
okc_k_headers_b CHR -- rmunjulu 4622198
WHERE khr.ID = p_khr_id AND khr.ID = CHR.ID; -- rmunjulu 4622198
SELECT prd.description description
FROM okl_products_v prd
WHERE prd.ID = p_pdt_id;
SELECT cle.ID cle_id
FROM okc_k_lines_b cle,
okl_txl_quote_lines_b tql
WHERE tql.qte_id = p_qte_id
AND tql.qlt_code = 'AMCFIA'
AND tql.kle_id = cle.ID
UNION
SELECT DISTINCT hdrcle.ID cle_id
FROM okc_k_lines_b cle,
okc_k_lines_b hdrcle,
okc_k_items cim,
okl_txl_quote_lines_b tql,
okc_k_headers_b CHR,
okc_line_styles_b lse
WHERE hdrcle.chr_id = p_chr_id
AND cle.cle_id = hdrcle.ID
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cim.jtot_object1_code = 'OKX_COVASST'
AND cim.cle_id = cle.ID
AND cim.object1_id1 = tql.kle_id
AND tql.qte_id = p_qte_id
AND tql.qlt_code = 'AMCFIA'
AND hdrcle.lse_id = lse.ID
AND lse.lty_code IN ('FEE', 'SOLD_SERVICE')
AND hdrcle.chr_id = CHR.ID
AND hdrcle.sts_code = CHR.sts_code;
SELECT attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM okl_k_headers okl
WHERE okl.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_lines okl
WHERE okl.ID = p_kle_id;
SELECT NAME,
tax_owner
FROM okl_product_parameters_v
WHERE ID = p_pdt_id;
'calling okl_trx_contracts_pub.update_trx_contracts'
);
okl_trx_contracts_pub.update_trx_contracts
(p_api_version => l_api_version,
p_init_msg_list => g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tcnv_rec => lip_tcnv_rec,
p_tclv_tbl => l_tclv_tbl,
x_tcnv_rec => lix_tcnv_rec,
x_tclv_tbl => lx_tclv_tbl
);
'called okl_trx_contracts_pub.update_trx_contracts , return status: '
|| l_return_status
);
SELECT NVL (tql.amount, 0) amount --SECHAWLA 11-MAR-03 : Added nvl
FROM okl_txl_quote_lines_v tql
WHERE tql.kle_id = p_kle_id
AND tql.qte_id = p_qte_id
AND tql.qlt_code = 'AMBPOC'; -- Purchase Amount
SELECT fb.book_type_code,
fb.asset_id,
fb.contract_id
FROM fa_books fb,
fa_additions_b fab,
fa_book_controls fbc
WHERE fb.asset_id = fab.asset_id
AND fb.book_type_code = fbc.book_type_code
AND NVL (fbc.date_ineffective, cp_sysdate + 1) > cp_sysdate
AND fb.transaction_header_id_out IS NULL
AND fab.asset_number = cp_asset_number;
SELECT khr.end_date end_date
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id;
SELECT arr.ID ID,
okl_am_util_pvt.get_lookup_meaning ('OKL_ASSET_RETURN_STATUS',
arr.ars_code,
'N'
) ret_status
FROM okl_asset_returns_v arr
WHERE arr.kle_id = p_kle_id AND arr.ars_code <> 'CANCELLED';
SELECT SUM (blp.amount_due_remaining)
FROM okl_bpd_leasing_payment_trx_v blp
WHERE blp.contract_id = p_khr_id;
SELECT oblp.amount_due_remaining amount,
oblp.stream_type_id stream_type_id,
osty.NAME stream_meaning,
oblp.payment_schedule_id schedule_id,
oblp.receivables_invoice_number ar_invoice_number,
otil.ID til_id,
-999 tld_id
FROM okl_bpd_leasing_payment_trx_v oblp,
okl_txl_ar_inv_lns_v otil,
okl_strm_type_v osty
WHERE oblp.contract_id = p_khr_id
AND oblp.receivables_invoice_id = otil.receivables_invoice_id
AND oblp.stream_type_id = osty.ID
AND oblp.amount_due_remaining > 0
UNION
SELECT oblp.amount_due_remaining amount,
oblp.stream_type_id stream_type_id,
osty.NAME stream_meaning,
oblp.payment_schedule_id schedule_id,
oblp.receivables_invoice_number ar_invoice_number,
otai.til_id_details til_id,
otai.ID tld_id
FROM okl_bpd_leasing_payment_trx_v oblp,
okl_txd_ar_ln_dtls_v otai,
okl_strm_type_v osty
WHERE oblp.contract_id = p_khr_id
AND oblp.receivables_invoice_id = otai.receivables_invoice_id
AND oblp.stream_type_id = osty.ID
AND oblp.amount_due_remaining > 0;
SELECT khr.pdt_id
FROM okl_k_headers_v khr
WHERE khr.ID = p_khr_id;
SELECT dst.code_combination_id
FROM okl_trns_acc_dstrs dst
WHERE dst.source_id = p_source_id
AND dst.source_table = p_source_table
AND dst.cr_dr_flag = 'C'
AND dst.percentage = 100;
'calling OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts'
);
okl_trx_ar_adjsts_pub.insert_trx_ar_adjsts
(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_adjv_rec => lp_adjv_rec,
x_adjv_rec => lx_adjv_rec
);
'called OKL_TRX_AR_ADJSTS_PUB.insert_trx_ar_adjsts , return status: '
|| l_return_status
);
'calling OKL_TXL_ADJSTS_LNS_PUB.insert_txl_adjsts_lns'
);
okl_txl_adjsts_lns_pub.insert_txl_adjsts_lns
(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_ajlv_tbl => lp_ajlv_tbl,
x_ajlv_tbl => lx_ajlv_tbl
);
'called OKL_TXL_ADJSTS_LNS_PUB.insert_txl_adjsts_lns , return status: '
|| l_return_status
);
SELECT cle.ID ID,
cle.NAME NAME,
kle.residual_value residual_value
FROM okl_k_lines_v kle,
okc_k_lines_v cle,
okc_k_headers_v khr
WHERE cle.chr_id = p_khr_id
AND cle.chr_id = khr.ID
AND cle.sts_code = khr.sts_code
AND kle.ID = cle.ID
AND ROWNUM < 2;
SELECT qlt.kle_id
FROM okl_txl_quote_lines_b qlt
WHERE qlt.qlt_code = 'AMCFIA' AND qlt.qte_id = p_qte_id;
SELECT khr.sts_code
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id;
SELECT trn.tsu_code
FROM okl_trx_contracts trn
WHERE trn.ID = p_trx_id;
update_quote_status(p_term_rec);
SELECT khr.end_date end_date,
khr.sts_code sts_code
FROM okc_k_headers_v khr
WHERE khr.ID = p_chr_id;
SELECT A.pdt_id, A.MULTI_GAAP_YN, B.REPORTING_PDT_ID
FROM okl_k_headers_v A,
okl_products B
WHERE A.ID = p_khr_id
AND B.ID = A.pdt_id; -- MGAAP 7263041
SELECT khr.end_date end_date,
khr.sts_code sts_code
FROM okc_k_headers_v khr
WHERE khr.ID = p_chr_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM okc_subclass_top_line stl,
okc_line_styles_v lse,
okc_k_lines_v cle,
okc_k_headers_v khr
WHERE cle.ID = p_top_line_id
AND cle.dnz_chr_id = p_dnz_chr_id
AND cle.cle_id IS NULL
AND cle.chr_id = cle.dnz_chr_id
AND cle.lse_id = lse.ID
AND lse.lty_code = g_fin_line_lty_code
AND lse.lse_type = g_tls_type
AND lse.lse_parent_id IS NULL
AND lse.ID = stl.lse_id
AND cle.sts_code = khr.sts_code
AND cle.dnz_chr_id = khr.ID
AND stl.scs_code IN (g_lease_scs_code, g_loan_scs_code));
| PRIVATE PROCEDURE update_payments
|
| DESCRIPTION
| This procedure updates the financial asset, service line and service
| subline level payments with the proposed payments calculated during
| quote creation
|
| CALLED FROM PROCEDURES/FUNCTIONS
| update_lines
|
| CALLS PROCEDURES/FUNCTIONS
|
|
| PARAMETERS
| p_quote_id IN Quote ID
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 28-OCT-2003 SECHAWLA 22846988 Created
| 20-SEP-04 SECHAWLA 3816891 : Modified payment processing
| for Arrears
| 29-SEP-04 PAGARG Bug #3921591: Added payment
| processing for Rollover Fee line
| 15-JUN-06 SMADHAVA Bug#5043646: Modified payment processing
| for fee header level payments
*=======================================================================*/
PROCEDURE update_payments (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT okc_api.g_false,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_quote_id IN NUMBER
)
IS
-- This cursor returns all the proposed objects of a particular type, created during quote creation
-- some of the proposed objects may not have any associated cash flows
CURSOR l_quoteobjects_csr (
cp_qte_id IN NUMBER
)
IS
SELECT DISTINCT qco.cfo_id cfo_id,
qco.base_source_id line_id,
caf.sts_code,
cfo.oty_code
FROM okl_trx_qte_cf_objects qco,
okl_cash_flows caf,
okl_cash_flow_objects cfo
WHERE cfo.ID = qco.cfo_id
AND cfo.ID =
caf.cfo_id(+)
-- cash flow object is created even if there are no proposed payments (quote eff dt < first level start date)
--Bug #3921591: pagarg +++ Rollover +++
-- Included fee line and fee asset line type cash flow objects also
AND cfo.oty_code IN
('FINANCIAL_ASSET_LINE',
'SERVICE_LINE',
'SERVICED_ASSET_LINE',
'FEE_LINE',
'FEE_ASSET_LINE'
)
AND qco.qte_id = cp_qte_id
AND NVL (caf.sts_code, 'PROPOSED') = 'PROPOSED';
SELECT TRUNC (date_effective_from)
FROM okl_trx_quotes_b
WHERE ID = cp_qte_id;
SELECT ID tql_id,
asset_quantity asset_quantity,
quote_quantity quote_quantity
FROM okl_txl_quote_lines_b
WHERE qte_id = cp_qte_id
AND qlt_code = 'AMCFIA'
AND kle_id = cp_kle_id;
SELECT ID caf_id,
sty_id,
dnz_khr_id,
number_of_advance_periods,
due_arrears_yn
FROM okl_cash_flows
WHERE cfo_id = cp_cfo_id
AND sts_code = 'PROPOSED'
AND cft_code = 'PAYMENT_SCHEDULE';
SELECT ID cfl_id,
amount,
number_of_periods,
fqy_code,
stub_days,
stub_amount,
start_date
FROM okl_cash_flow_levels
WHERE caf_id = cp_caf_id;
SELECT DISTINCT rgp.ID rgp_id,
slh_rul.ID slh_id,
rgp.dnz_chr_id,
sttyp.id1 sty_id,
sll_rul.rule_information5 advance_periods,
sll_rul.rule_information10 due_arrears_yn,
sll_rul.object1_id1 frequency
FROM okc_rules_b sll_rul,
okc_rules_b slh_rul,
okc_rule_groups_b rgp,
okl_strmtyp_source_v sttyp
WHERE 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;
SELECT caf.ID,
caf.sty_id,
cfl.start_date,
cfl.amount,
cfl.number_of_periods,
cfl.stub_days,
cfl.stub_amount,
NVL (caf.due_arrears_yn, 'N') due_arrears_yn,
-- SECHAWLA 20-SEP-04 3816891 : Added
cfl.fqy_code -- SECHAWLA 20-SEP-04 3816891 : Added
FROM okl_cash_flows caf,
okl_cash_flow_levels cfl
WHERE cfo_id = cp_cfo_id
AND caf.sty_id = cp_sty_id
AND caf.ID = cfl.caf_id
AND caf.sts_code = 'PROPOSED'
AND caf.cft_code = 'PAYMENT_SCHEDULE'
ORDER BY cfl.start_date;
SELECT rgp.cle_id cle_id,
sttyp.id1 sty_id,
sttyp.code stream_type,
sll_rul.rule_information2 start_date,
sll_rul.rule_information3 periods,
sll_rul.rule_information6 amount,
sll_rul.rule_information7 stub_days,
sll_rul.rule_information8 stub_amount,
rgp.dnz_chr_id
FROM okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE 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 sttyp.id1 = cp_sty_id
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 start_date;
SELECT cim.object1_id1,
cle.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
AND cim.cle_id = cle.ID
AND cle.ID = cp_fee_serviced_asset_line_id;
SELECT 'X'
FROM okc_k_lines_b linked_asset,
okc_line_styles_b line_styl,
okc_k_items item,
okc_statuses_b sts
WHERE linked_asset.lse_id = line_styl.ID
AND line_styl.lty_code = cp_line_type
AND item.cle_id = linked_asset.ID
AND linked_asset.cle_id = cp_line_id
AND sts.code = linked_asset.sts_code
AND sts.ste_code = 'ACTIVE';
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
AND cim.cle_id = cle.ID
AND cle.cle_id = cp_line_id;
SELECT ADD_MONTHS (cp_currlevelstartdt, cp_number_of_months)
FROM DUAL;
:= g_module_name || 'update_payments';
SAVEPOINT update_payments;
'calling OKL_LA_PAYMENTS_PVT.delete_payment'
);
okl_la_payments_pvt.delete_payment
(p_api_version => p_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_pym_tbl => lpym_del_tbl,
--bug #7498330
p_source_trx => 'TQ'
);
'called OKL_LA_PAYMENTS_PVT.delete_payment , return status: '
|| l_return_status
);
'calling OKL_LA_PAYMENTS_PVT.delete_payment'
);
okl_la_payments_pvt.delete_payment
(p_api_version => p_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_pym_tbl => lpym_del_tbl,
--bug # 7498330
p_source_trx => 'TQ'
);
'calling OKL_LA_PAYMENTS_PVT.delete_payment '
|| l_return_status
);
lp_pym_tbl (pym_tbl_ind).update_type := 'CREATE';
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
l_splitpymt_tbl.DELETE
(i,
l_splitpymt_tbl.COUNT);
l_splitpymt_tbl.DELETE (i,
l_splitpymt_tbl.COUNT);
'calling OKL_LA_PAYMENTS_PVT.delete_payment'
);
okl_la_payments_pvt.delete_payment
(p_api_version => p_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_pym_tbl => lpym_del_tbl,
--bug # 7498330
p_source_trx => 'TQ'
);
'calling OKL_LA_PAYMENTS_PVT.delete_payment , return status: '
|| l_return_status
);
'calling OKL_LA_PAYMENTS_PVT.delete_payment'
);
okl_la_payments_pvt.delete_payment
(p_api_version => p_api_version,
p_init_msg_list => okc_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_del_pym_tbl => lpym_del_tbl,
--bug # 7498330
p_source_trx => 'TQ'
);
'calling OKL_LA_PAYMENTS_PVT.delete_payment , return status: '
|| l_return_status
);
lp_pym_tbl (i).update_type := 'CREATE';
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
p_update_type => 'CREATE',
x_rulv_tbl => lx_rulv_tbl
);
ROLLBACK TO update_payments;
ROLLBACK TO update_payments;
ROLLBACK TO update_payments;
END update_payments;
PROCEDURE update_lines (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_term_rec IN term_rec_type,
p_sys_date IN DATE,
p_klev_tbl IN klev_tbl_type,
p_status IN VARCHAR2,
p_trn_reason_code IN VARCHAR2,
x_klev_tbl OUT NOCOPY klev_tbl_type,
-- BAKUCHIB 28-MAR-03 2877278 Added parameter
x_msg_tbl OUT NOCOPY g_msg_tbl
)
IS
-- RMUNJULU Bug # 2484327 changed parameters to IN
-- We need to change the status of lines
CURSOR l_trmnt_line_csr (
p_cle_id IN okc_k_lines_b.ID%TYPE
)
IS
SELECT cle.ID ID
FROM okc_k_lines_b cle
CONNECT BY PRIOR cle.ID = cle.cle_id
START WITH cle.ID = p_cle_id;
SELECT cim.cle_id cle_id
FROM okc_k_items cim
WHERE cim.dnz_chr_id = p_chr_id
AND cim.jtot_object1_code = 'OKX_COVASST'
AND cim.object1_id1 IN (
SELECT cle.ID
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.ID
AND lse.lty_code = g_fin_line_lty_code
AND lse.lse_type = g_tls_type
AND cle.sts_code = p_sts_code
AND cle.ID = p_cle_id);
SELECT kle.sts_code sts_code
FROM okc_k_lines_b kle
WHERE kle.cle_id = p_cle_id;
SELECT '1'
FROM DUAL
WHERE EXISTS (
SELECT '1'
FROM okc_k_lines_b cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND lse.ID = cle.lse_id
AND lse.lty_code IN
(g_ser_line_lty_code,
g_srl_line_lty_code,
g_fee_line_lty_code,
g_fel_line_lty_code,
g_usg_line_lty_code,
g_usl_line_lty_code
));
SELECT SUM (kle.capital_amount) amount,
cle.cle_id cle_id
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE kle.ID = cle.ID
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.ID
AND lse.lty_code IN (g_srl_line_lty_code, g_fel_line_lty_code)
AND cle.sts_code <> p_sts_code
AND cle.date_terminated IS NULL
GROUP BY cle.cle_id;
SELECT SUM (kle.capital_amount) amount,
cle.cle_id cle_id
FROM okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
WHERE kle.ID = cle.ID
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.ID
AND lse.lty_code IN (g_srl_line_lty_code, g_fel_line_lty_code)
AND cle.date_terminated IS NULL
GROUP BY cle.cle_id;
SELECT rl.ID ID,
rl.rule_information6 payment_amount
FROM okc_rule_groups_b rg,
okc_rules_b rl,
okc_k_lines_v cle,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.ID = p_cle_id
AND cle.lse_id = lse.ID
AND lse.lty_code IN (g_ser_line_lty_code, g_fee_line_lty_code)
AND lse.lse_type = 'TLS'
AND cle.cle_id IS NULL
AND rg.dnz_chr_id = cle.dnz_chr_id
AND rg.cle_id = cle.ID
AND rg.chr_id IS NULL
AND rg.ID = rl.rgp_id
AND rg.rgd_code = 'LALEVL'
AND rl.rule_information_category =
'LASLL'
-- RMUNJULU 29-AUG-03 OKC RULES MIGRATION
AND EXISTS (
SELECT '1'
FROM okc_k_lines_v cle_sl,
okc_line_styles_b lse_sl
WHERE cle_sl.dnz_chr_id = p_chr_id
AND cle_sl.cle_id = cle.ID
AND cle_sl.lse_id = lse_sl.ID
AND cle_sl.sts_code <> p_sts_code
AND lse_sl.lty_code IN
(g_srl_line_lty_code, g_fel_line_lty_code));
l_api_name CONSTANT VARCHAR2 (30) := 'update_lines';
:= g_module_name || 'update_lines';
SELECT cim.object1_id1 object1_id1,
cim.object1_id2 object1_id2,
cim.jtot_object1_code jtot_object1_code
FROM okc_k_items cim
WHERE cim.dnz_chr_id = p_dnz_chr_id AND cim.cle_id = p_cle_id;
SELECT siv.NAME NAME
FROM okx_system_items_v siv
WHERE siv.id1 = p_id1 AND siv.id2 = p_id2;
SELECT ssv.NAME NAME
FROM okl_strmtyp_source_v ssv
WHERE ssv.id1 = p_id1 AND ssv.id2 = p_id2;
SELECT siv.NAME NAME
FROM okx_system_items_v siv
WHERE siv.id1 = p_id1 AND siv.id2 = p_id2;
SELECT 'Y'
FROM okl_contract_balances
WHERE kle_id = p_kle_id;
SAVEPOINT update_lines;
'calling OKL_CONTRACT_PUB.update_contract_line'
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
'calling OKL_CONTRACT_PUB.update_contract_line , return status: '
|| x_return_status
);
'calling OKL_CONTRACT_PUB.update_contract_line'
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => r_clev_rec,
p_klev_rec => r_klev_rec,
x_clev_rec => rx_clev_rec,
x_klev_rec => rx_klev_rec
);
'called OKL_CONTRACT_PUB.update_contract_line , return status: '
|| x_return_status
);
'calling OKL_CONTRACT_PUB.update_contract_line'
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => m_clev_rec,
p_klev_rec => m_klev_rec,
x_clev_rec => mx_clev_rec,
x_klev_rec => mx_klev_rec
);
'called OKL_CONTRACT_PUB.update_contract_line , return status: '
|| x_return_status
);
'calling OKL_CONTRACT_PUB.update_contract_line'
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => lap_clev_rec,
p_klev_rec => lap_klev_rec,
x_clev_rec => lax_clev_rec,
x_klev_rec => lax_klev_rec
);
'called OKL_CONTRACT_PUB.update_contract_line , return status: '
|| l_return_status
);
'calling OKL_CBL_PVT.update_row'
);
okl_cbl_pvt.update_row (p_api_version => p_api_version,
p_init_msg_list => okl_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cblv_rec => p_cblv_rec,
x_cblv_rec => x_cblv_rec
);
'called OKL_CBL_PVT.update_row , return status: '
|| x_return_status
);
'calling OKL_CBL_PVT.insert_row'
);
okl_cbl_pvt.insert_row (p_api_version => p_api_version,
p_init_msg_list => okl_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cblv_rec => p_cblv_rec,
x_cblv_rec => x_cblv_rec
);
'called OKL_CBL_PVT.insert_row , return status: '
|| x_return_status
);
update_payments (p_api_version => p_api_version,
p_init_msg_list => okc_api.g_false,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => l_return_status,
p_quote_id => p_term_rec.p_quote_id
);
'called update_payments , return status: '
|| l_return_status
);
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => n_clev_rec,
p_klev_rec => n_klev_rec,
x_clev_rec => nx_clev_rec,
x_klev_rec => nx_klev_rec);
OKL_RULE_PUB.update_rule(
p_api_version => p_api_version,
p_init_msg_list => G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => r_rulv_rec,
x_rulv_rec => rx_rulv_rec);
ROLLBACK TO update_lines;
ROLLBACK TO update_lines;
ROLLBACK TO update_lines;
END update_lines;
PROCEDURE update_contract (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_term_rec IN term_rec_type,
p_sys_date IN DATE,
p_status IN VARCHAR2,
p_trn_reason_code IN VARCHAR2,
px_msg_tbl IN OUT NOCOPY g_msg_tbl
)
IS
-- Get the contract details
CURSOR get_k_dtls_csr (
p_khr_id IN NUMBER
)
IS
SELECT khr.object_version_number object_version_number,
khr.sts_code sts_code,
khr.authoring_org_id authoring_org_id
--CDUBEY authoring_org_id added for MOAC
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id;
SELECT stv.ste_code ste_code
FROM okc_statuses_v stv
WHERE stv.code = p_sts_code;
SELECT stv.meaning meaning
FROM okc_statuses_v stv
WHERE stv.code = p_sts_code;
SELECT kle.ID ID
FROM okc_k_lines_v kle,
okc_k_headers_v khr
WHERE kle.dnz_chr_id = p_khr_id
AND kle.sts_code = khr.sts_code
AND kle.chr_id = khr.ID;
l_api_name CONSTANT VARCHAR2 (30) := 'update_contract';
:= g_module_name || 'update_contract';
SAVEPOINT update_contract;
'calling OKL_CONTRACT_PUB.update_contract_line'
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec
);
'called OKL_CONTRACT_PUB.update_contract_line , return status: '
|| x_return_status
);
'calling OKC_CONTRACT_PUB.update_contract_header'
);
okc_contract_pub.update_contract_header
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_restricted_update => g_true,
p_chrv_rec => lp_chrv_rec,
x_chrv_rec => lx_chrv_rec
);
'called OKC_CONTRACT_PUB.update_contract_header , return status: '
|| l_return_status
);
ROLLBACK TO update_contract;
ROLLBACK TO update_contract;
ROLLBACK TO update_contract;
END update_contract;
SELECT kle.ID ID
FROM okc_k_lines_v kle,
okc_k_headers_v khr,
okc_line_styles_v lse
WHERE kle.dnz_chr_id = p_khr_id
AND kle.dnz_chr_id = khr.ID
AND kle.sts_code = khr.sts_code
AND kle.lse_id = lse.ID
AND lse.lty_code = g_fin_line_lty_code;
SELECT tcn.tmt_status_code -- akrangan sla tmt_status_code changes
FROM okl_trx_contracts tcn
WHERE tcn.ID = p_tcn_id;
update_lines
(p_api_version => p_api_version,
p_init_msg_list => g_false,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => l_return_status,
p_term_rec => p_term_rec,
p_sys_date => p_sys_date,
p_klev_tbl => p_klev_tbl,
p_status => p_status,
p_trn_reason_code => px_tcnv_rec.trn_code,
x_klev_tbl => lx_klev_tbl,
-- BAKUCHIB 28-MAR-03 2877278 Added
x_msg_tbl => l_msg_tbl
);
'called update_lines , return status: '
|| l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
update_contract (p_api_version => p_api_version,
p_init_msg_list => g_false,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => l_return_status,
p_term_rec => p_term_rec,
p_sys_date => p_sys_date,
p_status => p_status,
p_trn_reason_code => px_tcnv_rec.trn_code,
px_msg_tbl => l_msg_tbl
);
'called update_contract , return status: '
|| l_return_status
);
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
NULL; --Mass Rebook will have updated the termination trn
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'PROCESSED',
px_tcnv_rec => px_tcnv_rec
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'PROCESSED',
px_tcnv_rec => px_tcnv_rec
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
p_tmt_flag => 'TMT_CONTRACT_UPDATED_YN',
p_tsu_code => 'ERROR',
px_tcnv_rec => px_tcnv_rec
);
'In param, p_tcnv_rec.tmt_contract_updated_yn: '
|| p_tcnv_rec.tmt_contract_updated_yn
);
SELECT SYSDATE
INTO l_sys_date
FROM DUAL;
p_trn_mode => 'INSERT',
x_id => lx_id,
x_return_status => l_return_status
);
p_control_flag => 'UPDATE',
px_tcnv_rec => lp_tcnv_rec,
x_return_status => l_return_status
);
IF NVL (lp_tcnv_rec.tmt_contract_updated_yn, 'N') = 'N'
AND NVL (lp_tcnv_rec.tmt_generic_flag2_yn, 'N') = 'Y'
THEN
l_return_status := g_ret_sts_success;
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);
p_trn_mode => 'UPDATE',
x_id => lx_id,
x_return_status => l_return_status
);