The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_BOOKED_KLE_DELETE CONSTANT VARCHAR2(200) := 'OKL_LLA_BOOKED_KLE_DELETE';
G_PAST_BOOKED_KLE_DELETE CONSTANT VARCHAR2(200) := 'OKL_LLA_P_BOOKED_KLE_DELETE';
G_FUNDED_KLE_DELETE CONSTANT VARCHAR2(200) := 'OKL_LLA_FUNDED_KLE_DELETE';
G_DELETE_CONT_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_DELETE_CONT_ERROR';
G_DELETE_CONT_RBK_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_DELETE_CONT_RBK_ERROR';
G_DELETE_CONT_FUND_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_DELETE_CONT_FUND_ERROR';
G_DELETE_CONT_RCPT_ERROR CONSTANT VARCHAR2(30) := 'OKL_LLA_DELETE_CONT_RCPT_ERROR';
G_NO_UPDATE_ALLOWED_EXCEPTION exception;
G_NO_UPDATE_ALLOWED CONSTANT VARCHAR2(200) := 'OKL_NO_UPDATE_ALLOWED';
G_WF_EVT_CR_LMT_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.credit_limit.updated';
G_WF_EVT_ASSET_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.lease_contract.asset_updated';
G_WF_EVT_SERVICE_UPDATED CONSTANT VARCHAR2(60) := 'oracle.apps.okl.la.lease_contract.service_fee_updated';
G_WF_EVT_FEE_UPDATED CONSTANT VARCHAR2(50) := 'oracle.apps.okl.la.lease_contract.fee_updated';
Select str.id strm_id,
str.kle_id,
str.sty_id,
str.sgn_code
from OKL_STREAMS str
where str.say_code = 'CURR'
and str.kle_id = cleId;
l_stream_update_err EXCEPTION;
Okl_Streams_pub.update_streams(
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_stmv_tbl => l_stmv_tbl,
x_stmv_tbl => x_stmv_tbl);
raise l_stream_update_err;
l_stmv_tbl.delete;
When l_stream_update_err then
If (l_stmv_tbl.COUNT > 0) then
l_stmv_tbl.delete;
l_stmv_tbl.delete;
PROCEDURE kle_delete_allowed(p_cle_id IN NUMBER,
x_deletion_type OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
--Cursor to check if the contract is booked
Cursor Chr_sts_crs(p_cle_id IN Number) IS
SELECT chr.sts_code
FROM okc_k_headers_b chr,
okc_k_lines_b cle
WHERE chr.ID = cle.dnz_chr_id
AND cle.ID = p_cle_id;
SELECT 'Y'
FROM okc_k_headers_bh chrh,
okc_k_headers_b chr,
okc_k_lines_b cle
WHERE chrh.contract_number = chr.contract_number
AND chr.ID = cle.dnz_chr_id
AND chrh.sts_code = G_OKL_BOOKED_STS_CODE
AND cle.ID = p_cle_id
AND rownum < 2;
SELECT 'Y'
FROM OKL_TXL_AP_INV_LNS_B fln
WHERE fln.kle_id = p_cle_id
And rownum < 2;
SELECT 'Y'
FROM OKL_STREAMS str
WHERE str.kle_id = p_cle_id
And rownum < 2;
l_deletion_type Varchar2(1) default 'P'; --P : physical delete
Select 'Y' rbk_asst_flag,
clet.NAME
from okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb
where chrb.id = cleb.dnz_chr_id
and chrb.scs_code = 'LEASE'
and chrb.orig_system_source_code = 'OKL_REBOOK'
and clet.id = cleb.id
and clet.language = userenv('LANG')
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
and cleb.id = p_cle_id
and cleb.orig_system_id1 is not NULL
and exists (select '1'
from okc_k_headers_b orig_chrb,
okc_k_lines_b orig_cleb
where orig_chrb.id = chrb.orig_system_id1
and orig_cleb.id = cleb.orig_system_id1
--Bug# 4375800 :
and orig_cleb.sts_code <> 'ABANDONED'
and orig_cleb.dnz_chr_id = orig_chrb.id);
p_msg_name => G_BOOKED_KLE_DELETE);
p_msg_name => G_PAST_BOOKED_KLE_DELETE);
p_msg_name => G_FUNDED_KLE_DELETE);
p_msg_name => 'OKL_LA_REBOOK_LINE_DELETE',
p_token1 => 'ASSET_NUMBER',
p_token1_value => l_rbk_asst_rec.name);
END kle_delete_allowed;
SELECT chr.sts_code,
chr.scs_code,
khr.term_duration,
chr.start_date,
chr.end_date
From okl_k_headers khr,
okc_k_headers_b chr
Where khr.id = chr.id
And chr.id = p_chr_id;
SELECT 'Y'
FROM okc_k_headers_bh chrh,
okc_k_headers_b chr
WHERE chrh.contract_number = chr.contract_number
AND chr.ID = p_chr_id
AND chrh.sts_code = G_OKL_BOOKED_STS_CODE
AND rownum < 2;
Select 'Y'
From okc_k_headers_b chr
where chr.orig_system_source_code = 'OKL_REBOOK'
and chr.id = p_chr_id;
Select pdt.from_date,
pdt.to_date
From okl_products pdt,
okl_k_headers khr
where pdt.id = khr.pdt_id
and khr.id = p_chr_id;
SELECT DATE_TRANSACTION_OCCURRED
FROM okl_trx_contracts ktrx
WHERE ktrx.KHR_ID_NEW = rbk_chr_id
AND ktrx.tsu_code = 'ENTERED'
--AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY';
Select orig.start_date,
orig.end_date,
orig_k.term_duration
from okl_k_headers orig_k,
okc_k_headers_b orig,
okc_k_headers_b rbk
where orig_k.id = orig.id
and orig.id = rbk.orig_system_id1
and rbk.id = rbk_chr_id;
Select nvl(template_yn,'N')
From okc_k_headers_b
Where id = p_chr_id;
IF p_chr_id is not null Then --it is an update
--get old value of template y/N flag
Open chk_template_csr(p_chr_id => p_chr_id);
PROCEDURE Asset_Logical_Delete( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cle_id IN NUMBER,
p_asset_number IN VARCHAR2) IS
--cursor to get new asset number
Cursor c_asset_no IS
select 'DUMMY'||TO_CHAR(OKL_FAN_SEQ.NEXTVAL)
FROM dual;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKL_TXL_ASSETS_V
WHERE asset_number = p_asset_number);
select 'Y'
from okc_k_lines_b kleb,
okc_k_lines_tl kle,
okc_line_styles_b lse
where kle.name = p_asset_number
and kle.id = kleb.id
and kle.language = USERENV('LANG')
and kleb.lse_id = lse.id
and lse.lty_code = 'FREE_FORM1';
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKX_ASSETS_V
WHERE asset_number = p_asset_number);
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT '1'
FROM OKL_TXD_ASSETS_V
WHERE asset_number = p_asset_number);
Select 'Y'
From Dual
Where exists (select '1'
from okc_k_lines_b cle,
okc_line_styles_b lse
where cle.lse_id = lse.id
and lse.lty_code = 'FREE_FORM1'
and cle.id = p_line_id);
Select txlv.id,
txlv.asset_number
From OKL_TXL_ASSETS_V txlv,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
Where txlv.kle_id = cle.id
And cle.lse_id = lse.id
And lse.lty_code = 'FIXED_ASSET'
And cle.cle_id = p_finasst_id
And txlv.asset_number = p_asstNo;
Select txdv.id,
txdv.asset_number
From OKL_TXD_ASSETS_V txdv,
OKL_TXL_ASSETS_V txlv,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
Where txdv.tal_id = txlv.id
And txlv.kle_id = cle.id
And cle.lse_id = lse.id
And lse.lty_code = 'FIXED_ASSET'
And cle.cle_id = p_finasst_id
And txdv.asset_number = p_asstno;
okl_okc_migration_pvt.update_contract_line(
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_restricted_update => OKC_API.G_FALSE,
p_clev_rec => l_clev_rec,
x_clev_rec => lx_clev_rec);
okl_txl_assets_pub.update_txl_asset_Def(
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_tlpv_rec => l_tlpv_rec,
x_tlpv_rec => lx_tlpv_rec);
okl_txd_assets_pub.update_txd_asset_Def(
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_adpv_rec => l_adpv_rec,
x_adpv_rec => lx_adpv_rec);
END Asset_logical_Delete;
Procedure Linked_Asset_Delete( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_cle_id IN NUMBER,
p_deletion_type IN VARCHAR2) IS
--cursor to check if line is financial asset top line
CURSOR l_is_finasst (p_line_id OKC_K_LINES_B.ID%TYPE) IS
Select 'Y'
From Dual
Where exists (select '1'
from okc_k_lines_b cle,
okc_line_styles_b lse
where cle.lse_id = lse.id
and lse.lty_code = 'FREE_FORM1'
and cle.id = p_line_id);
Select lnk.id
From okc_k_lines_b lnk,
okc_line_styles_b lnk_lse,
okc_statuses_b sts,
okc_k_items cim
Where lnk.id = cim.cle_id
and lnk.dnz_chr_id = cim.dnz_chr_id
and lnk.lse_id = lnk_lse.id
and lnk_lse.lty_code in ('LINK_FEE_ASSET','LINK_SERV_ASSET','LINK_USAGE_ASSET')
and sts.code = lnk.sts_code
and sts.ste_code not in ('EXPIRED','TERMINATED','CANCELLED')
and cim.jtot_object1_code = 'OKX_COVASST'
and cim.object1_id1 = to_char(p_line_id)
and cim.object1_id2 = '#';
okl_okc_migration_pvt.update_contract_line
(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_clev_rec => l_clev_rec,
x_clev_rec => l_clev_rec_out
);
OKL_CONTRACT_PVT.delete_contract_line
(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_line_id => l_lnk_cle_id
);
END Linked_Asset_Delete;
Select 'Y'
From okc_k_headers_b chr
where chr.orig_system_source_code = 'OKL_REBOOK'
and chr.id = p_chr_id;
select chrb.start_date,
chrb.end_date,
chrb.scs_code,
chrb.currency_code,
chrb.sts_code
from okc_k_headers_b chrb
where chrb.id = p_chr_id;
select cleb.start_date,
cleb.end_date,
cleb.currency_code,
cleb.sts_code
from okc_k_lines_b cleb
where cleb.id = p_cle_id;
select lseb.lty_code,
lseb.lse_type
from okc_line_styles_b lseb
where lseb.id = p_lse_id;
SELECT DATE_TRANSACTION_OCCURRED
FROM okl_trx_contracts ktrx
WHERE ktrx.KHR_ID_NEW = rbk_chr_id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP
AND ktrx.representation_type = 'PRIMARY';
Select khr.currency_code,
khr.currency_conversion_type,
khr.currency_conversion_date,
khr.currency_conversion_rate
from okl_k_headers_full_v khr,
okc_k_headers_b rbk_chr
where khr.id = rbk_chr.orig_system_id1
and rbk_chr.id = p_rbk_chr_id;
select id
From okc_rule_groups_b rgp
Where rgp.chr_id = pchrid
and rgp.dnz_chr_id = pchrid
and rgp.rgd_code = 'LATOWN';
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.rgp_id = prgpid
and rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
Select name
From OKL_PRODUCTS_V
Where id = pdtid;
Select khr.pdt_id
from okl_k_headers khr,
okc_k_headers_b rbk_chr
where khr.id = rbk_chr.orig_system_id1
and rbk_chr.id = p_rbk_chr_id;
select khr.pdt_id,
chrb.scs_code,
chrb.orig_system_source_code
from okl_k_headers khr,
okc_k_headers_b chrb
where khr.id = chrb.id
and chrb.id = p_chr_id;
select nvl(rulb.rule_information1,'N') release_Asset_yn
from okc_rules_b rulb,
okc_rule_groups_b rgpb
where rulb.rule_information_category = 'LARLES'
and rulb.rgp_id = rgpb.id
and rulb.dnz_chr_id = rgpb.dnz_chr_id
and rgpb.rgd_code = 'LARLES'
and rgpb.chr_id = p_chr_id
and rgpb.dnz_chr_id = p_chr_id;
select txdb.id
from okl_txd_assets_b txdb,
okl_txl_assets_b txlb,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where txdb.tax_book = p_book_type_code
and txdb.tal_id = txlb.id
and txlb.kle_id = cleb.id
and cleb.lse_id = lseb.id
and lseb.lty_code = 'FIXED_ASSET'
and cleb.dnz_chr_id = p_chr_id;
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_latown_rulv_rec,
x_rulv_rec => lx_latown_rulv_rec);
Select khr.pdt_id,
khr.start_date
from okl_k_headers_full_v khr
where id = orig_chr_id;
select user_profile_option_name
from fnd_profile_options_vl
where profile_option_name = p_profile_option_name;
OKL_KHR_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_khrv_rec => l_khrv_rec,
x_khrv_rec => x_khrv_rec);
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2,
p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
p_khrv_rec IN khrv_rec_type,
p_edit_mode IN VARCHAR2,
x_chrv_rec OUT NOCOPY okl_okc_migration_pvt.chrv_rec_type,
x_khrv_rec OUT NOCOPY khrv_rec_type) IS
subtype rulv_tbl_type is OKL_RULE_PUB.rulv_tbl_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
select 'x'
from OKL_K_HEADERS_V
where id = l_id;
SELECT cle.id
From okc_k_lines_b cle,
okc_statuses_b sts
where cle.dnz_chr_id = cle.chr_id
and cle.chr_id = p_chr_id
and sts.code = cle.sts_code
And sts.ste_code not in ('HOLD','EXPIRED','TERMINATED');
SELECT cle.id,
cle.start_date,
cle.end_date,
cle.orig_system_id1,
--Bug#
cle.lse_id
From okc_k_lines_b cle
connect by prior cle.id = cle.cle_id
start with cle.id = p_cle_id
and exists (select 1
from okc_statuses_b sts
where sts.code = cle.sts_code
and sts.ste_code not in ('HOLD','EXPIRED','TERMINATED'));
SELECT chr.currency_code,
chr.start_date,
chr.end_date,
chr.scs_code,
chr.orig_system_source_code,
chr.orig_system_id1,
chr.authoring_org_id --MOAC
FROM okc_k_headers_b chr
WHERE chr.id = chrid;
SELECT khr.currency_conversion_type,
khr.currency_conversion_rate,
khr.currency_conversion_date,
khr.term_duration,
khr.pdt_id,
--bug# 3180583
khr.multi_gaap_yn
FROM okl_k_headers khr
WHERE khr.id = chrid;
SELECT DEAL_TYPE, REPORTING_PDT_ID
FROM OKL_PRODUCT_PARAMETERS_V
WHERE ID = p_pdt_id;
l_delete_flag VARCHAR(1) := 'N';
select txdb.id
from okl_txd_assets_b txdb,
okl_txl_assets_b txlb,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where txdb.tax_book = p_book_type_code
and txdb.tal_id = txlb.id
and txlb.kle_id = cleb.id
and cleb.lse_id = lseb.id
and lseb.lty_code = 'FIXED_ASSET'
and cleb.dnz_chr_id = p_chr_id;
SELECT
cle.start_date,
cle.end_date
From okc_k_lines_b cle
Where cle.id = p_cle_id;
select lseb.lty_code
from okc_line_styles_b lseb
where lseb.id = p_lse_id;
select subb.maximum_term
from okl_subsidies_b subb,
okl_k_lines kle
where subsidy_id = kle.subsidy_id
and kle.id = p_cle_id;
SELECT DISTINCT TAS_ID
FROM OKL_TXL_ASSETS_B
WHERE KLE_ID IN
(SELECT OKC.ID
FROM OKC_K_LINES_B OKC,
OKC_LINE_STYLES_B OKC_ST
WHERE OKC_ST.LTY_CODE ='FREE_FORM1'
AND OKC.LSE_ID=OKC_ST.ID
AND OKC.CHR_ID =OKC.DNZ_CHR_ID
AND OKC.CHR_ID =p_chr_id1);
l_delete_flag := 'N';
l_delete_flag := 'Y';
l_delete_flag := 'Y';
IF (l_delete_flag = 'Y') THEN
-- Delete reporting asset tax book
l_rep_book_type := OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_RPT_PROD_BOOK_TYPE_CODE);
OKL_TXD_ASSETS_PUB.delete_txd_asset_Def(
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_adpv_tbl => l_adpv_tbl);
okl_okc_migration_pvt.update_contract_header(
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_restricted_update => p_restricted_update,
p_chrv_rec => l_curr_chrv_rec,
x_chrv_rec => x_chrv_rec);
OKL_TRX_ASSETS_PVT.UPDATE_TRX_ASS_H_DEF(p_api_version ,
p_init_msg_list ,
x_return_status ,
x_msg_count ,
x_msg_data ,
l_tasv_rec ,
x_tasv_rec) ;
okl_okc_migration_pvt.update_contract_line(
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_clev_rec => l_clev_rec,
x_clev_rec => lx_clev_rec);
OKL_LA_PAYMENTS_PVT.update_pymt_start_date
(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_chr_id => l_chrv_rec.id);
OKL_KHR_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_khrv_rec => l_khrv_rec,
x_khrv_rec => x_khrv_rec);
OKL_KHR_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_khrv_rec => l_khrv_rec,
x_khrv_rec => x_khrv_rec);
END update_contract_header;
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2,
p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
p_khrv_rec IN khrv_rec_type,
x_chrv_rec OUT NOCOPY okl_okc_migration_pvt.chrv_rec_type,
x_khrv_rec OUT NOCOPY khrv_rec_type) IS
l_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
update_contract_header(
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_restricted_update => p_restricted_update,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
p_edit_mode => 'Y',
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
END update_contract_header;
PROCEDURE update_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_restricted_update IN VARCHAR2,
p_chrv_tbl IN okl_okc_migration_pvt.chrv_tbl_type,
p_khrv_tbl IN khrv_tbl_type,
x_chrv_tbl OUT NOCOPY okl_okc_migration_pvt.chrv_tbl_type,
x_khrv_tbl OUT NOCOPY khrv_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_HEADER';
update_contract_header(
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_restricted_update => p_restricted_update,
p_chrv_rec => p_chrv_tbl(i),
p_khrv_rec => l_khrv_tbl(i),
x_chrv_rec => x_chrv_tbl(i),
x_khrv_rec => x_khrv_tbl(i));
END update_contract_header;
PROCEDURE delete_contract(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_contract_id IN okc_k_headers_b.id%TYPE) IS
SUBTYPE chrv_rec_type IS OKL_OKC_MIGRATION_PVT.CHRV_REC_TYPE;
G_CANNOT_DELETE VARCHAR2(200) := 'OKC_CANNOT_DELETE';
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT';
SELECT 'Y'
FROM okl_trx_ar_invoices_b
WHERE khr_id = p_id
AND trx_status_code <> 'ERROR'
AND rownum < 2;
SELECT 'Y'
FROM okc_k_headers_bh chrh,
okc_k_headers_b chr
WHERE chrh.contract_number = chr.contract_number
AND chr.id = p_id
AND chrh.sts_code = G_OKL_BOOKED_STS_CODE
AND rownum < 2;
SELECT id stm_id
FROM OKL_STREAMS
WHERE khr_id = p_khr_id;
SELECT id trx_id
FROM OKL_TRX_CONTRACTS
WHERE khr_id = p_khr_id
AND representation_type = 'PRIMARY';
SELECT id
FROM OKL_TXL_CNTRCT_LNS
WHERE khr_id = p_khr_id;
SELECT cle.id top_line
FROM okc_line_styles_b lse,
okc_k_lines_b cle
WHERE 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.lse_parent_id IS NULL
AND lse.lse_type = G_TLS_TYPE;
SELECT sts_code, orig_system_source_code, contract_number,authoring_org_id --CDUBEY authoring_org_id added for MOAC
FROM okc_k_headers_b
WHERE id = p_chr_id;
FUNCTION DELETE_GOVERNANCES( p_chr_id number) Return varchar2 IS
l_return_status varchar2(30);
SELECT ID
FROM OKC_GOVERNANCES_V
WHERE dnz_chr_id = p_id;
okc_contract_pub.delete_governance (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_gvev_tbl => l_gvev_tbl_in
);
p_msg_name => G_CANNOT_DELETE,
p_token1 => G_TABLE_NAME_TOKEN,
p_token1_value => 'Governances',
p_token2 => G_SQLCODE_TOKEN,
p_token2_value => sqlcode,
p_token3 => G_SQLERRM_TOKEN,
p_token3_value => sqlerrm);
END DELETE_GOVERNANCES;
FUNCTION DELETE_RULE_GROUPS( p_chr_id number) Return varchar2 IS
l_return_status varchar2(30);
SELECT ID
FROM OKC_RULE_GROUPS_V
WHERE dnz_chr_id = p_id;
OKC_RULE_PUB.delete_rule_group (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rgpv_tbl => l_rgpv_tbl_in
);
p_msg_name => G_CANNOT_DELETE,
p_token1 => G_TABLE_NAME_TOKEN,
p_token1_value => 'Rule Groups',
p_token2 => G_SQLCODE_TOKEN,
p_token2_value => sqlcode,
p_token3 => G_SQLERRM_TOKEN,
p_token3_value => sqlerrm);
END DELETE_RULE_GROUPS;
FUNCTION DELETE_K_PARTY_ROLES( p_chr_id number) Return varchar2 IS
--Bug# 4558486
l_cplv_tbl_in OKL_OKC_MIGRATION_PVT.cplv_tbl_type;
SELECT ID
FROM OKC_K_PARTY_ROLES_V
WHERE dnz_chr_id = p_id;
OKL_K_PARTY_ROLES_PVT.DELETE_K_PARTY_ROLE (
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_cplv_tbl => l_cplv_tbl_in,
p_kplv_tbl => l_kplv_tbl_in
);
p_msg_name => G_CANNOT_DELETE,
p_token1 => G_TABLE_NAME_TOKEN,
p_token1_value => 'Contract Party Roles',
p_token2 => G_SQLCODE_TOKEN,
p_token2_value => sqlcode,
p_token3 => G_SQLERRM_TOKEN,
p_token3_value => sqlerrm);
END DELETE_K_PARTY_ROLES;
FUNCTION DELETE_CONTACTS( p_chr_id number) Return varchar2 IS
l_ctcv_tbl_in OKC_CONTRACT_PARTY_PUB.ctcv_tbl_type;
SELECT ID
FROM OKC_CONTACTS_V
WHERE dnz_chr_id = p_id;
OKC_CONTRACT_PARTY_PUB.Delete_Contact(
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_ctcv_tbl => l_ctcv_tbl_in);
p_msg_name => G_CANNOT_DELETE,
p_token1 => G_TABLE_NAME_TOKEN,
p_token1_value => 'Contacts',
p_token2 => G_SQLCODE_TOKEN,
p_token2_value => sqlcode,
p_token3 => G_SQLERRM_TOKEN,
p_token3_value => sqlerrm);
END DELETE_CONTACTS;
FUNCTION DELETE_RG_PARTY_ROLES( p_chr_id number) Return varchar2 IS
l_rmpv_tbl_in OKC_RULE_PUB.rmpv_tbl_type;
SELECT ID
FROM OKC_RG_PARTY_ROLES_V
WHERE dnz_chr_id = p_id;
OKC_RULE_PUB.delete_rg_mode_pty_role (
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_rmpv_tbl => l_rmpv_tbl_in
);
p_msg_name => G_CANNOT_DELETE,
p_token1 => G_TABLE_NAME_TOKEN,
p_token1_value => 'Rule Group Party Roles',
p_token2 => G_SQLCODE_TOKEN,
p_token2_value => sqlcode,
p_token3 => G_SQLERRM_TOKEN,
p_token3_value => sqlerrm);
END DELETE_RG_PARTY_ROLES;
p_msg_name => G_DELETE_CONT_RBK_ERROR,
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => l_contract_number);
select count(1) into l_funding_count
from okl_trx_ap_invoices_b a
,okl_txl_ap_inv_lns_all_b b
where a.id = b.tap_id
and b.khr_id = p_contract_id
and a.funding_type_code is not null
and rownum < 2;
p_msg_name => G_DELETE_CONT_FUND_ERROR,
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => l_contract_number);
p_msg_name => G_PAST_BOOKED_KLE_DELETE);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In okl_contract_pvt.delete_contract: ' || p_contract_id || ':' || l_chr_invoices);
p_msg_name => G_DELETE_CONT_RCPT_ERROR,
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => l_contract_number);
p_msg_name => G_DELETE_CONT_ERROR,
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => l_contract_number,
p_token2 => 'STATUS',
p_token2_value => l_sts_code);
OKL_ACCOUNT_DIST_PUB.DELETE_ACCT_ENTRIES(
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_source_id => r_get_source_id.id,
p_source_table => 'OKL_TXL_CNTRCT_LNS');
OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(
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_tcnv_rec => r_tcnv_rec);
OKL_STREAMS_PUB.delete_streams(
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_stmv_rec => l_stmv_rec);
OKL_CONTRACT_PUB.delete_contract_line(
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_line_id => r_get_k_top_line.top_line);
OKC_K_HISTORY_PUB.delete_all_rows(
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_chr_id => p_contract_id);
x_return_status := DELETE_GOVERNANCES(p_contract_id);
x_return_status := DELETE_RULE_GROUPS(p_contract_id);
x_return_status := DELETE_CONTACTS(p_contract_id);
x_return_status := DELETE_K_PARTY_ROLES(p_contract_id);
x_return_status := DELETE_RG_PARTY_ROLES(p_contract_id);
DELETE OKL_K_RATE_PARAMS
WHERE KHR_ID = p_contract_id;
OKL_CONTRACT_PUB.delete_contract_header(
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_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec);
END delete_contract;
PROCEDURE delete_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chrv_rec IN okl_okc_migration_pvt.chrv_rec_type,
p_khrv_rec IN khrv_rec_type) IS
l_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
okl_okc_migration_pvt.delete_contract_header(
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_chrv_rec => l_chrv_rec);
OKL_KHR_PVT.Delete_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_khrv_rec => p_khrv_rec);
END delete_contract_header;
PROCEDURE delete_contract_header(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chrv_tbl IN okl_okc_migration_pvt.chrv_tbl_type,
p_khrv_tbl IN khrv_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_HEADER';
delete_contract_header(
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_chrv_rec => p_chrv_tbl(i),
p_khrv_rec => l_khrv_tbl(i));
END delete_contract_header;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT h.currency_code
FROM okc_k_headers_b h,
okc_k_lines_b l
WHERE h.id = l.dnz_chr_id
AND l.id = p_line_id;
SELECT lse.lty_code
FROM okc_line_styles_b lse,
okc_k_lines_b line
WHERE lse.id = line.lse_id
AND line.id = p_line_id;
OKL_KLE_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_klev_rec => l_klev_rec,
x_klev_rec => x_klev_rec);
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
p_klev_rec IN klev_rec_type,
p_edit_mode IN VARCHAR2,
x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
x_klev_rec OUT NOCOPY klev_rec_type) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
select 'x'
from OKL_K_LINES_V
where id = l_id;
select cleb.dnz_chr_id,
cleb.cle_id,
cleb.lse_id,
cleb.start_date,
cleb.end_date,
cleb.currency_code,
cleb.sts_code
from okc_k_lines_b cleb
where id = p_cle_id;
SELECT lse.lty_code
FROM okc_line_styles_b lse,
okc_k_lines_b line
WHERE lse.id = line.lse_id
AND line.id = p_line_id;
select rlobj.object1_id1
from okc_k_rel_objs_v rlobj
where rlobj.chr_id = p_okl_chr_id
and rlobj.cle_id = p_okl_cle_id
and rlobj.rty_code = 'OKLSRV'
and rlobj.jtot_object1_code = 'OKL_SERVICE_LINE';
select dnz_chr_id from okc_k_lines_b where id = p_oks_cle_id;
okl_okc_migration_pvt.update_contract_line(
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_clev_rec => l_clev_rec,
x_clev_rec => x_clev_rec);
OKL_KLE_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_klev_rec => l_klev_rec,
x_klev_rec => x_klev_rec);
OKL_KLE_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_klev_rec => l_klev_rec,
x_klev_rec => x_klev_rec);
l_event_name := G_WF_EVT_CR_LMT_UPDATED;
l_event_name := G_WF_EVT_ASSET_UPDATED;
l_event_name := G_WF_EVT_SERVICE_UPDATED;
l_event_name := G_WF_EVT_FEE_UPDATED;
END update_contract_line;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
p_klev_rec IN klev_rec_type,
x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
x_klev_rec OUT NOCOPY klev_rec_type) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
update_contract_line(
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_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
p_edit_mode => 'Y',
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec);
END update_contract_line;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_klev_tbl IN klev_tbl_type,
p_edit_mode IN VARCHAR2,
x_clev_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type,
x_klev_tbl OUT NOCOPY klev_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
update_contract_line(
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_clev_rec => p_clev_tbl(i),
p_klev_rec => l_klev_tbl(i),
p_edit_mode => p_edit_mode,
x_clev_rec => x_clev_tbl(i),
x_klev_rec => x_klev_tbl(i));
END update_contract_line;
PROCEDURE update_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_klev_tbl IN klev_tbl_type,
x_clev_tbl OUT NOCOPY okl_okc_migration_pvt.clev_tbl_type,
x_klev_tbl OUT NOCOPY klev_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_LINE';
update_contract_line(
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_clev_tbl => p_clev_tbl,
p_klev_tbl => l_klev_tbl,
p_edit_mode => 'Y',
x_clev_tbl => x_clev_tbl,
x_klev_tbl => x_klev_tbl);
END update_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
p_klev_rec IN klev_rec_type) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
SELECT DNZ_CHR_ID
FROM OKC_K_LINES_B
WHERE ID = P_CLE_ID;
SELECT tas_id,
id
FROM OKL_TXL_ASSETS_B
WHERE kle_id = p_kle_id;
SELECT ID
FROM OKL_TXD_ASSETS_B
WHERE tal_id = p_tal_id;
SELECT tas_id,
id
FROM OKL_TXL_ITM_INSTS
WHERE kle_id = p_kle_id;
Select id
FROM OKL_SUPP_INVOICE_DTLS
where cle_id = p_kle_id;
Select pyh.id
FROM OKL_PARTY_PAYMENT_HDR pyh
WHERE pyh.cle_id = p_kle_id;
Select pyd.id
FROM OKL_PARTY_PAYMENT_DTLS pyd,
OKC_K_PARTY_ROLES_B cplb
WHERE pyd.cpl_id = cplb.id
AND nvl(cplb.cle_id,-9999) = p_kle_id;
SELECT kpl.id
FROM okl_k_party_roles kpl,
okc_k_party_roles_b cpl
WHERE kpl.id = cpl.id
AND cpl.cle_id = p_kle_id
AND cpl.dnz_chr_id = p_khr_id;
SELECT lse.lty_code
FROM okc_line_styles_b lse,
okc_k_lines_b lns
WHERE lns.id = p_line_id
AND lse.id = lns.lse_id;
kle_delete_allowed(p_cle_id => l_klev_rec.id,
x_deletion_type => l_deletion_type,
x_return_status => x_return_status);
If l_deletion_type = 'L' Then --logical delete
--update line status to 'Abandoned'
l_clev_rec.sts_code := G_OKL_CANCELLED_STS_CODE;
update_contract_line(
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_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => l_clev_rec_out,
x_klev_rec => l_klev_rec_out
);
Asset_Logical_Delete( 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_cle_id => l_clev_rec_out.id,
p_asset_number => l_clev_rec_out.name);
Linked_Asset_Delete(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_cle_id => l_clev_rec_out.id,
p_deletion_type => l_deletion_type);
null; --delete is not allowed this will be normally an error
Linked_Asset_Delete(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_cle_id => l_clev_rec.id,
p_deletion_type => l_deletion_type);
OKL_PYD_PVT.delete_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_ppydv_tbl => l_ppydv_tbl);
l_ppydv_tbl.DELETE;
OKL_PARTY_PAYMENTS_PVT.DELETE_PARTY_PAYMENT_HDR
(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_pphv_tbl => l_pphv_tbl );
l_pphv_tbl.DELETE;
OKL_KPL_PVT.Delete_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_kplv_tbl => l_kplv_tbl);
l_kplv_tbl.DELETE;
okl_okc_migration_pvt.delete_contract_line(
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_clev_rec => l_clev_rec);
OKL_TXD_ASSETS_PUB.delete_txd_asset_Def(
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_adpv_rec => l_adpv_rec);
OKL_TXL_ASSETS_PUB.delete_txl_asset_Def(
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_tlpv_rec => l_tlpv_rec);
OKL_TRX_ASSETS_PUB.delete_trx_ass_h_Def(
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_thpv_rec => l_thpv_rec);
OKL_TXL_ITM_INSTS_PUB.delete_txl_itm_insts(
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_iipv_rec => l_iipv_rec);
OKL_TRX_ASSETS_PUB.delete_trx_ass_h_Def(
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_thpv_rec => l_iti_thpv_rec);
OKL_SUPP_INVOICE_DTLS_PUB.delete_sup_inv_dtls
(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_sidv_tbl => l_sidv_tbl);
l_sidv_tbl.DELETE;
OKL_KLE_PVT.Delete_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_klev_rec => l_klev_rec);
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_klev_tbl IN klev_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
delete_contract_line(
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_clev_rec => p_clev_tbl(i),
p_klev_rec => l_klev_tbl(i));
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_line_id IN NUMBER) IS
l_cle_Id NUMBER;
IS SELECT ID
FROM OKC_K_Lines_b
WHERE cle_id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
IS SELECT ID
FROM Okc_K_Lines_b
WHERE cle_Id=P_Parent_Id;
SELECT DNZ_CHR_ID
FROM OKC_K_LINES_B
WHERE ID = P_CLE_ID;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
SELECT tas_id,
id
FROM OKL_TXL_ASSETS_B
WHERE kle_id = p_kle_id;
SELECT ID
FROM OKL_TXD_ASSETS_B
WHERE tal_id = p_tal_id;
SELECT tas_id,
id
FROM OKL_TXL_ITM_INSTS
WHERE kle_id = p_kle_id;
Select id
FROM OKL_SUPP_INVOICE_DTLS
where cle_id = p_kle_id;
Select pyd.id
FROM OKL_PARTY_PAYMENT_DTLS pyd,
OKC_K_PARTY_ROLES_B cplb
WHERE pyd.cpl_id = cplb.id
AND nvl(cplb.cle_id,-9999) = p_kle_id;
Select pyh.id
FROM OKL_PARTY_PAYMENT_HDR pyh
WHERE pyh.cle_id = p_kle_id;
SELECT kpl.id
FROM okl_k_party_roles kpl,
okc_k_party_roles_b cpl
WHERE kpl.id = cpl.id
AND cpl.cle_id = p_kle_id
AND cpl.dnz_chr_id = p_khr_id;
SELECT lse.lty_code
FROM okc_line_styles_b lse,
okc_k_lines_b lns
WHERE lns.id = p_line_id
AND lse.id = lns.lse_id;
SELECT COUNT(id) FROM OKC_K_LINES_B
WHERE id = p_line_id;
kle_delete_allowed(p_cle_id => l_klev_tbl_in(i).id,
x_deletion_type => l_deletion_type,
x_return_status => x_return_status);
If (l_deletion_type in ('L','N')) Then --logical delete or not allowed
Exit;
okl_okc_migration_pvt.update_contract_line
(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_clev_rec => l_clev_rec,
x_clev_rec => l_clev_rec_out
);
Asset_Logical_Delete( 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_cle_id => l_clev_rec_out.id,
p_asset_number => l_clev_rec_out.name);
Linked_Asset_Delete(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_cle_id => l_clev_rec_out.id,
p_deletion_type => l_deletion_type);
ElsIf l_deletion_type = 'P' Then --physical delete
-- sjalasut added code to raise business event. the cursor is processed here but the actual
-- event is raised later. the cursor cannot be processed just before raising the event
-- as the records would have been deleted by that time and no information about the line can
-- be derived. fetch the line style code for the record
OPEN lty_code_csr(p_line_id);
Linked_Asset_Delete(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_cle_id => p_line_id,
p_deletion_type => l_deletion_type);
OKL_PYD_PVT.delete_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_ppydv_tbl => l_ppydv_tbl);
l_ppydv_tbl.DELETE;
OKL_PARTY_PAYMENTS_PVT.DELETE_PARTY_PAYMENT_HDR
(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_pphv_tbl => l_pphv_tbl );
l_pphv_tbl.DELETE;
OKL_KPL_PVT.Delete_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_kplv_tbl => l_kplv_tbl);
l_kplv_tbl.DELETE;
okc_contract_pub.delete_contract_line(
p_api_version => l_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_line_id => p_line_id);
OKL_TXD_ASSETS_PUB.delete_txd_asset_Def(
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_adpv_rec => l_adpv_rec);
OKL_TXL_ASSETS_PUB.delete_txl_asset_Def(
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_tlpv_rec => l_tlpv_rec);
OKL_TRX_ASSETS_PUB.delete_trx_ass_h_Def(
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_thpv_rec => l_thpv_rec);
OKL_TXL_ITM_INSTS_PUB.delete_txl_itm_insts(
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_iipv_rec => l_iipv_rec);
OKL_TRX_ASSETS_PUB.delete_trx_ass_h_Def(
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_thpv_rec => l_iti_thpv_rec);
OKL_SUPP_INVOICE_DTLS_PUB.delete_sup_inv_dtls
(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_sidv_tbl => l_sidv_tbl);
l_sidv_tbl.DELETE;
OKL_KLE_PVT.delete_row(
p_api_version => l_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_klev_tbl => l_klev_tbl_in);
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
p_klev_rec IN klev_rec_type,
p_delete_cascade_yn IN VARCHAR2) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
If p_delete_cascade_yn = 'Y' Then
delete_contract_line(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_line_id => l_klev_rec.id);
ElsIf p_delete_cascade_yn = 'N' Then
delete_contract_line(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_clev_rec => l_clev_rec,
p_klev_rec => p_klev_rec);
END delete_contract_line;
PROCEDURE delete_contract_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_clev_tbl IN okl_okc_migration_pvt.clev_tbl_type,
p_klev_tbl IN klev_tbl_type,
p_delete_cascade_yn IN varchar2) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CONTRACT_LINE';
delete_contract_line(
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_clev_rec => p_clev_tbl(i),
p_klev_rec => l_klev_tbl(i),
p_delete_cascade_yn => p_delete_cascade_yn);
END delete_contract_line;
PROCEDURE update_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_rec IN okl_okc_migration_pvt.gvev_rec_type,
x_gvev_rec OUT NOCOPY okl_okc_migration_pvt.gvev_rec_type) IS
l_gvev_rec okl_okc_migration_pvt.gvev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GOVERNANCE';
okl_okc_migration_pvt.update_governance(
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_gvev_rec => l_gvev_rec,
x_gvev_rec => x_gvev_rec);
END update_governance;
PROCEDURE update_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_tbl IN okl_okc_migration_pvt.gvev_tbl_type,
x_gvev_tbl OUT NOCOPY okl_okc_migration_pvt.gvev_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_GOVERNANCE';
update_governance(
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_gvev_rec => p_gvev_tbl(i),
x_gvev_rec => x_gvev_tbl(i));
END update_governance;
PROCEDURE delete_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_rec IN okl_okc_migration_pvt.gvev_rec_type) IS
l_gvev_rec okl_okc_migration_pvt.gvev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GOVERNANCE';
SELECT DNZ_CHR_ID
FROM OKC_GOVERNANCES
WHERE ID = P_GVE_ID;
okl_okc_migration_pvt.delete_governance(
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_gvev_rec => l_gvev_rec);
END delete_governance;
PROCEDURE delete_governance(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_gvev_tbl IN okl_okc_migration_pvt.gvev_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GOVERNANCE';
delete_governance(
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_gvev_rec => p_gvev_tbl(i));
END delete_governance;
select a.ste_code,
a.code,
cst.meaning,
b.scs_code,
b.currency_code,
--Bug# 2857899
nvl(b.template_yn,'N') template_yn
from okc_statuses_b a,
okc_k_headers_b b,
okl_k_headers c,
OKC_STATUSES_TL cst
where b.id = c.id
and b.id = chrId
and a.CODE = b.STS_CODE
and cst.code = a.code
and cst.LANGUAGE = userenv('LANG');
select khr.SHORT_DESCRIPTION,
khr.CONTRACT_NUMBER,
khr.PROGRAM_NAME,
khr.PRODUCT_NAME,
khr.PDT_ID,
khr.ID,
khr.AUTHORING_ORG_ID,
khr.PRODUCT_DESCRIPTION,
khr.INV_ORG_ID,
khr.START_DATE,
khr.END_DATE,
cst.MEANING,
--Added by dpsingh for LE Project
khr.LEGAL_ENTITY_ID
from OKL_LA_HEADERS_UV khr,
OKC_STATUSES_TL cst
where khr.ID = chrId
and cst.CODE = khr.STS_CODE
and cst.LANGUAGE = userenv('LANG');
SELECT CHRB.CURRENCY_CODE,
CHRB.AUTHORING_ORG_ID,
CHRB.INV_ORGANIZATION_ID,
CHRB.BUY_OR_SELL,
CHRB.CUST_ACCT_ID,
CUS.NAME,
CUS.DESCRIPTION
FROM OKC_K_HEADERS_B CHRB,
OKX_CUSTOMER_ACCOUNTS_V CUS
WHERE CUS.ID1 = CHRB.cust_acct_id
AND CHRB.ID = chrId;
select prog.contract_number
from OKL_K_HEADERS KHR,
OKC_K_HEADERS_B PROG
where PROG.ID = KHR.KHR_ID
and khr.id = p_chr_id;
select SECURITIZED_CODE
from okl_k_headers khr
where khr.id = p_chr_id;
SELECT 'Y' FROM okc_k_headers_b chr
WHERE id = p_contract_id
AND EXISTS
(
SELECT 'x' FROM okc_k_items cim
WHERE cim.object1_id1 = to_char(chr.id)
AND EXISTS
(
SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'SHARED'
AND cle.id = cim.cle_id
)
AND EXISTS
(
SELECT 'x' FROM okc_k_headers_b chr2
WHERE chr2.id = cim.dnz_chr_id
AND chr2.scs_code = 'SYNDICATION'
AND chr2.sts_code not in ('TERMINATED','ABANDONED')
)
)
AND chr.scs_code in ('LEASE','LOAN');