[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select id, passthru_term
from okl_party_payment_hdr
where
dnz_chr_id = p_dnz_chr_id and cle_id = p_cle_id and
passthru_term = p_passthru_term;
select 1
from okc_k_headers_all_b
where id = p_dnz_chr_id
and scs_code = 'INVESTOR';
x_base.last_updated_by := OKL_API.G_MISS_NUM;
x_base.last_update_date := OKL_API.G_MISS_DATE;
x_base.last_update_login := OKL_API.G_MISS_NUM;
x_evg.last_updated_by := OKL_API.G_MISS_NUM;
x_evg.last_update_date := OKL_API.G_MISS_DATE;
x_evg.last_update_login := OKL_API.G_MISS_NUM;
PROCEDURE delete_passthru_party(
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_cpl_id IN NUMBER
) IS
/* not required as cpl_id is passed
CURSOR cpl_csr (p_chr_id NUMBER,
p_cle_id NUMBER,
p_vendor_id NUMBER) IS
SELECT id
FROM okc_k_party_roles_v
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND object1_id1 = 6
AND rle_code = 'OKL_VENDOR';
SELECT id
FROM okl_party_payment_dtls
WHERE cpl_id = p_cpl_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PASSTHRU_PARTY';
OKL_PARTY_PAYMENTS_PVT.delete_party_payment_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_ppydv_tbl => l_ppydv_tbl);
l_ppydv_tbl.DELETE;
okl_okc_migration_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_rec => l_cplv_rec);
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_rec => l_cplv_rec,
p_kplv_rec => l_kplv_rec);
END delete_passthru_party;
select object1_id1
from okc_k_party_roles_b
where
id = p_cpl_id;
okl_party_payments_pvt.update_party_payment_dtls(
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_ppydv_rec => l_base,
x_ppydv_rec => x_ppydv_rec);
okl_party_payments_pvt.update_party_payment_dtls(
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_ppydv_rec => l_evg,
x_ppydv_rec => x_ppydv_rec);
SELECT payout_basis
FROM okl_party_payment_hdr
WHERE cle_id = p_cle_id
AND passthru_term = p_passthru_term;
SELECT orig_system_id1
FROM okc_k_lines_b
WHERE id = p_cle_id;
okl_party_payments_pvt.update_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_rec => l_pphv_rec,
x_pphv_rec => x_pphv_rec);
okl_party_payments_pvt.update_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_rec => l_pphv_rec,
x_pphv_rec => x_pphv_rec);
PROCEDURE delete_payment_hdrs(
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_passthru_rec IN passthru_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_payment_hdrs';
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_rec => l_pphv_rec);
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_rec => l_pphv_rec);
END delete_payment_hdrs;
SELECT a.attribute_label_long
FROM ak_region_items ri, AK_REGIONS r, AK_ATTRIBUTES_vL a
WHERE ri.region_code = r.region_code
AND ri.attribute_code = a.attribute_code
AND ri.attribute_application_id = a.attribute_application_id
AND ri.region_application_id = r.region_application_id
AND ri.attribute_code = p_ak_attribute
AND ri.region_code = p_ak_region;
select id1
from okx_vendors_v
where name = p_fee_types_rec.party_name;
select name
from okx_vendors_v
where id1 = p_fee_types_rec.party_id1;
select OKL_STRMTYP.id1
from OKL_STRMTYP_SOURCE_V OKL_STRMTYP
where OKL_STRMTYP.name = p_fee_types_rec.item_name
and OKL_STRMTYP.STATUS = 'A';
SELECT sty_id
FROM okl_strm_tmpt_full_uv
WHERE nvl(CAPITALIZE_YN,'N') = 'Y'
AND STY_PURPOSE = 'EXPENSE'
AND pdt_id = p_pdt_id
AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
AND sty_name = p_fee_types_rec.item_name;
SELECT sty_id
FROM okl_strm_tmpt_full_uv
WHERE STY_PURPOSE = 'FEE_PAYMENT'
AND pdt_id = p_pdt_id
AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
AND sty_name = p_fee_types_rec.item_name;
SELECT sty_id
FROM okl_strm_tmpt_full_uv
WHERE STY_PURPOSE = 'PASS_THROUGH_FEE'
AND pdt_id = p_pdt_id
AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
AND sty_name = p_fee_types_rec.item_name;
SELECT sty_id
FROM okl_strm_tmpt_full_uv
WHERE STY_PURPOSE = 'SECURITY_DEPOSIT'
AND pdt_id = p_pdt_id
AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
AND sty_name = p_fee_types_rec.item_name;
SELECT sty_id
FROM okl_strm_tmpt_full_uv
WHERE STY_PURPOSE = 'EXPENSE'
AND pdt_id = p_pdt_id
AND trunc(p_start_date) BETWEEN trunc(okl_strm_tmpt_full_uv.START_DATE)
AND nvl(trunc(okl_strm_tmpt_full_uv.END_DATE),p_start_date+1)
AND sty_name = p_fee_types_rec.item_name;
select name
from OKL_STRMTYP_SOURCE_V OKL_STRMTYP
where OKL_STRMTYP.id1 = p_fee_types_rec.item_id1
and OKL_STRMTYP.STATUS = 'A';
select chr.start_date, khr.pdt_id
from okc_k_headers_b chr, okl_k_headers khr
where chr.id = p_fee_types_rec.dnz_chr_id
and chr.id = khr.id;
SELECT rqt.qte_id
FROM OKL_LA_ROLLOVER_FEE_UV rqt,
OKC_K_HEADERS_B chr
WHERE CHR.CUST_ACCT_ID = rqt.cust_acct_id
AND rqt.rollover_quote = p_fee_types_rec.roll_qt
AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
and nvl(trunc(date_effective_to),trunc(p_start_date+1))
AND chr.id = p_fee_types_rec.dnz_chr_id;
SELECT rqt.rollover_quote
FROM OKL_LA_ROLLOVER_FEE_UV rqt,
OKC_K_HEADERS_B chr,
okl_k_headers khr
WHERE chr.id = khr.id
and chr.currency_code = rqt.currency_code
and CHR.CUST_ACCT_ID = rqt.cust_acct_id
AND rqt.qte_id = p_fee_types_rec.qte_id
AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
and nvl(trunc(date_effective_to),trunc(p_start_date+1))
AND chr.id = p_fee_types_rec.dnz_chr_id;
SELECT rqt.rollover_quote
FROM OKL_LA_ROLLOVER_FEE_UV rqt,
OKC_K_HEADERS_B chr,
okl_k_headers khr
WHERE chr.id = khr.id
and chr.currency_code = rqt.currency_code
and CHR.CUST_ACCT_ID = rqt.cust_acct_id
AND rqt.qte_id = p_fee_types_rec.qte_id
AND trunc(p_start_date) between nvl(trunc(date_effective_from),trunc(p_start_date))
and nvl(trunc(date_effective_to),trunc(p_start_date+1))
and khr.khr_id = rqt.khr_id
and chr.id = p_fee_types_rec.dnz_chr_id;
select khr_id
from okl_k_headers
where id = p_fee_types_rec.dnz_chr_id;
select 1
from okl_k_lines kle,
okc_k_lines_b cle
where cle.id = kle.id
and kle.qte_id = p_qte_id
and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id
and cle.id <> p_fee_types_rec.line_id;
select 1
from okl_k_lines kle,
okc_k_lines_b cle
where cle.id = kle.id
and kle.qte_id = p_qte_id
and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id;
select lookup_code
from fnd_lookups fnd
where fnd.lookup_type = G_OKL_FEE_PURPOSE_LOOKUP_TYPE
and fnd.lookup_code = p_fee_purpose_code;
select hz.party_name
from
okc_k_lines_b cle
, okc_k_headers_all_b chr
, hz_parties hz
, hz_cust_accounts hca
where
cle.chr_id = chr.id
and chr.scs_code = 'INVESTOR'
-- and chr.sts_code='NEW'
and hca.cust_account_id = cle.cust_acct_id
and hz.party_id = hca.party_id
and hz.party_id = p_fee_types_rec.party_id1
and chr.id = p_fee_types_rec.dnz_chr_id;
select hz.party_id
from
okc_k_lines_b cle
, okc_k_headers_all_b chr
, hz_parties hz
, hz_cust_accounts hca
where
cle.chr_id = chr.id
and chr.scs_code = 'INVESTOR'
-- and chr.sts_code='NEW'
and hca.cust_account_id = cle.cust_acct_id
and hz.party_id = hca.party_id
and hz.party_name = p_fee_types_rec.party_name
and chr.id = p_fee_types_rec.dnz_chr_id;
select id
from okc_line_styles_v
where lty_code = 'FEE';
select currency_code,sts_code
from okc_k_headers_b
where id = chr_id;
PROCEDURE update_fee_top_line(
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_clev_rec IN okl_okc_migration_pvt.clev_rec_type,
p_klev_rec IN okl_kle_pvt.klev_rec_type,
p_cimv_rec IN okl_okc_migration_pvt.cimv_rec_type,
p_cplv_rec IN okl_okc_migration_pvt.cplv_rec_type,
x_clev_rec OUT NOCOPY okl_okc_migration_pvt.clev_rec_type,
x_klev_rec OUT NOCOPY okl_kle_pvt.klev_rec_type,
x_cimv_rec OUT NOCOPY okl_okc_migration_pvt.cimv_rec_type,
x_cplv_rec OUT NOCOPY okl_okc_migration_pvt.cplv_rec_type) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type := p_clev_rec;
l_api_name CONSTANT VARCHAR2(30) := 'update_fee_top_line';
SELECT cle.id,
cle.start_date,
cle.end_date
FROM okc_k_lines_b cle
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id;
okl_contract_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,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec);
OKL_CONTRACT_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_sub_clev_rec,
p_klev_rec => l_sub_klev_rec,
x_clev_rec => x_sub_clev_rec,
x_klev_rec => x_sub_klev_rec
);
okl_okc_migration_pvt.update_contract_item(
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_cimv_rec => l_cimv_rec,
x_cimv_rec => x_cimv_rec);
okl_okc_migration_pvt.update_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_rec => l_cplv_rec,
x_cplv_rec => x_cplv_rec);
okl_k_party_roles_pvt.update_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_rec => l_cplv_rec,
x_cplv_rec => x_cplv_rec,
p_kplv_rec => l_kplv_rec,
x_kplv_rec => x_kplv_rec );
okl_okc_migration_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_rec => l_cplv_rec);
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_rec => l_cplv_rec,
p_kplv_rec => l_kplv_rec);
END update_fee_top_line;
PROCEDURE update_fee_type(
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_fee_types_rec IN fee_types_rec_type,
x_fee_types_rec OUT NOCOPY fee_types_rec_type
) IS
lp_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type := p_fee_types_rec;
l_api_name CONSTANT VARCHAR2(30) := 'update_fee_type';
select rgp.id,
rul.id
from okc_rules_v rul,
okc_rule_groups_v rgp
where rgp.id = rul.rgp_id
and rgp.rgd_code = 'LAPSTH'
and rul.rule_information_category = 'LASTRM'
and rgp.cle_id = p_fee_types_rec.line_id
and rul.dnz_chr_id = p_fee_types_rec.dnz_chr_id
and rgp.dnz_chr_id = p_fee_types_rec.dnz_chr_id;
SELECT kle.qte_id,
cle.orig_system_id1
FROM okl_k_lines kle,
okc_k_lines_b cle
WHERE cle.id = p_cle_id
AND kle.id = cle.id;
SELECT cle.start_date
FROM okc_k_lines_b cle
WHERE cle.id = p_cle_id;
OKL_LLA_UTIL_PVT.check_line_update_allowed
(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 => lp_fee_types_rec.line_id);
p_msg_name => 'OKL_LA_RBK_ROLL_QT_UPDATE');
OKL_MAINTAIN_FEE_PVT.update_fee_top_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 => lp_clev_rec,
p_klev_rec => lp_klev_rec,
p_cimv_rec => lp_cimv_rec,
p_cplv_rec => lp_cplv_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec,
x_cimv_rec => lx_cimv_rec,
x_cplv_rec => lx_cplv_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 => lx_clev_rec.dnz_chr_id,
p_cle_id => lx_clev_rec.id);
update_strmtp_rul(
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_fee_types_rec.dnz_chr_id,
p_cle_id => p_fee_types_rec.line_id,
p_rgp_id => l_rgp_id,
p_rul_id => l_rul_id
);
PROCEDURE delete_fee_type(
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_fee_types_rec IN fee_types_rec_type
) IS
l_clev_rec okl_okc_migration_pvt.clev_rec_type ;
l_api_name CONSTANT VARCHAR2(30) := 'delete_fee_type';
SELECT fee_purpose_code
FROM okl_k_lines
WHERE ID = p_line_id;
select 'Y'
from OKC_K_HEADERS_B chr
where chr.id = p_fee_types_rec.dnz_chr_id
and chr.orig_system_source_code = 'OKL_REBOOK';
select cle.id
from okc_k_lines_b cle
where cle.dnz_chr_id = p_chr_id
and cle.cle_id = p_line_id;
select kle.fee_type,
cle.orig_system_id1
from okc_k_lines_b cle,
okl_k_lines kle
where cle.id = kle.id
and cle.dnz_chr_id = p_fee_types_rec.dnz_chr_id
and cle.id = p_fee_types_rec.line_id;
select kle_fee.fee_type,
cim.object1_id1,
cim.dnz_chr_id
from okc_k_items cim,
okc_k_lines_b cleb,
okl_k_lines kle_fee,
okc_k_lines_b cleb_fee,
okc_line_styles_b lseb_fee
where cim.cle_id = cleb.id
and cim.dnz_chr_id = cleb.dnz_chr_id
and cim.jtot_object1_code = 'OKX_COVASST'
and cleb.cle_id = cleb_fee.id
and cleb.dnz_chr_id = cleb_fee.dnz_chr_id
and kle_fee.id = cleb_fee.id
and lseb_fee.id = cleb_fee.lse_id
and lseb_fee.lty_code = 'FEE'
and cleb_fee.id = p_cle_id
--Bug# 6512668: Exclude asset lines in Abandoned status
and cleb.sts_code <> 'ABANDONED';
p_msg_name => 'OKL_LA_RVI_DELETE_NOT_ALLOWED');
okl_contract_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,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec);
okl_contract_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_sl_clev_rec,
p_klev_rec => l_sl_klev_rec,
x_clev_rec => x_sl_clev_rec,
x_klev_rec => x_sl_klev_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 => l_line_id
);
okl_contract_pub.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 => l_fin_clev_tbl,
p_klev_tbl => l_fin_klev_tbl,
x_clev_tbl => lx_fin_clev_tbl,
x_klev_tbl => lx_fin_klev_tbl);
select id
from okc_rule_groups_v
where cle_id = p_cle_id
and chr_id is null
and dnz_chr_id = p_chr_id
and rgd_code = 'LAPSTH';
select rgp.id,
rul.id
from okc_rules_v rul,
okc_rule_groups_v rgp
where rgp.id = rul.rgp_id
and rgp.rgd_code = 'LAPSTH'
and rul.rule_information_category = 'LASTRM'
and rgp.cle_id = p_cle_id
and rgp.chr_id is null
and rul.dnz_chr_id = p_chr_id
and rgp.dnz_chr_id = p_chr_id;
OKL_RULE_PUB.update_rule_group(
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_rgpv_rec => lp_lapsth_rgpv_rec,
x_rgpv_rec => lx_lapsth_rgpv_rec);
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 => lp_lastrm_rulv_rec,
x_rulv_rec => lx_lastrm_rulv_rec);
OKL_RULE_PUB.delete_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 => lp_lastrm_rulv_rec);
OKL_RULE_PUB.delete_rule_group(
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_rgpv_rec => lp_lapsth_rgpv_rec);
select object1_id1,object1_id2
from okc_k_items_v
where cle_id = p_cle_id
and dnz_chr_id = p_chr_id;
select rul.object1_id1,
rul.object1_id2
from okc_rules_v rul,
okc_rule_groups_v rgp
where rgp.id = rul.rgp_id
and rgp.rgd_code = 'LAPSTH'
and rul.rule_information_category = 'LASTRM'
and rgp.cle_id = p_cle_id
and rgp.chr_id is null
and rul.dnz_chr_id = p_chr_id
and rgp.dnz_chr_id = p_chr_id;
PROCEDURE update_strmtp_rul(
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_chr_id IN NUMBER,
p_cle_id IN NUMBER,
p_rgp_id IN NUMBER,
p_rul_id IN NUMBER
) IS
lp_lapsth_rgpv_rec OKL_OKC_MIGRATION_PVT.rgpv_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'update_strmtp_rul';
select object1_id1,object1_id2
from okc_k_items_v
where cle_id = p_cle_id
and 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 => lp_lastrm_rulv_rec,
x_rulv_rec => lx_lastrm_rulv_rec);
SELECT khr.contract_number, qte.quote_number
FROM okc_k_headers_v khr,okl_trx_quotes_b qte
WHERE khr.id = chrID
AND qte.id = qteID;
SELECT cle.name
FROM okc_k_headers_b khr,
okl_k_lines kle,
okc_k_lines_v cle,
okl_trx_quotes_b qte
WHERE cle.id = kle.id
AND khr.id = chrID
AND khr.id = cle.dnz_chr_id
AND qte.id = qteID
AND kle.qte_id = qte.id;
SELECT 1 FROM okc_k_headers_b
WHERE id = chrID
AND cust_acct_id = (SELECT khr.cust_acct_id FROM okc_k_headers_b khr,okl_trx_quotes_b qte
WHERE khr.id = qte.khr_id
AND qte.id = qteID);
SELECT 1 FROM okc_k_headers_b
WHERE id = chrID
AND currency_code = (SELECT khr.currency_code FROM okc_k_headers_b khr,okl_trx_quotes_b qte
WHERE khr.id = qte.khr_id
AND qte.id = qteID);
SELECT 1 FROM okl_trx_quotes_b
WHERE id = qteID
AND qst_code = 'APPROVED';
SELECT 1 FROM okl_trx_quotes_b
WHERE id = qteID
AND consolidated_yn = 'N';
SELECT 1 FROM okl_trx_quotes_b
WHERE id = qteID
AND qtp_code IN ('TER_ROLL_PURCHASE' , 'TER_ROLL_WO_PURCHASE');
SELECT 1
FROM okc_k_headers_b khr,
okl_k_lines kle,
okc_k_lines_b cle,
okl_trx_quotes_b qte
WHERE cle.id = kle.id
AND khr.id = chrID
AND khr.id = cle.dnz_chr_id
AND qte.id = qteID
AND kle.qte_id = qte.id
AND trunc(qte.date_effective_from) <= cle.start_date
AND nvl(trunc(qte.date_effective_to), cle.start_date) >= cle.start_date
AND NOT EXISTS (
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cle.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'))
AND NOT EXISTS (
SELECT 'Y'
FROM okc_k_headers_b khr1
WHERE khr1.id = chrID
AND khr1.orig_system_source_code IN ('OKL_RELEASE'));
(SELECT SUM(tql.amount)
FROM okl_trx_quotes_b qte, okl_txl_quote_lines_b tql
WHERE qte.id = qteID
AND tql.qte_id= qte.id
AND tql.qlt_code not in ('AMCFIA', 'AMCTAX', 'AMYOUB', 'BILL_ADJST'))
INTERSECT
(SELECT SUM(KLE1.amount) FROM okc_k_lines_b cleb, okl_k_lines kle1
WHERE cleb.dnz_chr_id = chrID
AND kle1.ID = cleb.ID
AND kle1.fee_type = 'ROLLOVER'
AND kle1.qte_id = qteID
AND NOT EXISTS (
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cleb.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'))
/* Added to exclude this check for Re-lease contracts. */
AND NOT EXISTS (
SELECT 'Y'
FROM okc_k_headers_b khr1
WHERE khr1.id = chrID
AND khr1.orig_system_source_code IN ('OKL_RELEASE')));
SELECT 'Y'
FROM okc_k_headers_b
WHERE orig_system_source_code = 'OKL_REBOOK'
AND id = p_chr_id;
SELECT orig_system_id1
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.id = kle.id
AND dnz_chr_id = p_chr_id
AND kle.qte_id = p_qte_id;
SELECT COUNT(1)
FROM okl_rbk_selected_contract
WHERE khr_id = p_chr_id
AND NVL(status,'NEW') = 'UNDER REVISION';
select nvl(sum(kle.amount),0) amt
from okc_k_lines_b cle,
okl_k_lines kle,
okc_line_styles_b lse
where cle.id = kle.id
and cle.dnz_chr_id = p_chr_id
and lse.id = cle.lse_id
and lse.lty_code = 'FEE'
and fee_type = 'ROLLOVER';
OKL_CONTRACT_PUB.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 => 'F',
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
p_edit_mode => 'N',
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
SELECT kle.qte_id
FROM okc_k_headers_b khr, okc_k_lines_b cleb, okl_k_lines kle
WHERE khr.id = p_chr_id
AND cleb.dnz_chr_id = khr.id
AND kle.ID = cleb.ID
AND kle.fee_type = 'ROLLOVER';
SELECT nvl(ORIG_SYSTEM_SOURCE_CODE,'XXX')
FROM okc_k_headers_b khr
WHERE khr.id = p_chr_id;
PROCEDURE create_update_link_assets (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE,
p_cle_id IN NUMBER,
p_chr_id IN NUMBER,
p_capitalize_yn IN VARCHAR2,
p_link_asset_tbl IN link_asset_tbl_type,
p_derive_assoc_amt IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_program_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'create_update_link_assets';
l_update_line_item_tbl okl_contract_line_item_pvt.line_item_tbl_type;
m BINARY_INTEGER := 1; -- update table index
SELECT txl.asset_number
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okl_txl_assets_b txl
WHERE cle.id = txl.kle_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
AND cle.cle_id = p_fin_asset_id
AND cle.dnz_chr_id = p_chr_id
AND txl.dnz_khr_id = p_chr_id;
SELECT SUM(NVL(kle.capital_amount,kle.amount)) amount
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.cle_id = p_cle_id
AND cle.dnz_chr_id = p_chr_id
AND cle.sts_code = 'TERMINATED'
AND kle.id = cle.id;
SELECT NVL(amount, 0)
INTO l_line_amount
FROM okl_k_lines
WHERE id = p_cle_id;
SELECT currency_code
INTO l_currency_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT NVL(expected_asset_cost, 0)
INTO l_asset_oec
FROM okl_k_lines
WHERE id = l_link_asset_tbl(i).fin_asset_id;
SELECT NVL(oec, 0)
INTO l_asset_oec
FROM okl_k_lines
WHERE id = l_link_asset_tbl(i).fin_asset_id;
SELECT NVL(expected_asset_cost, 0)
INTO l_asset_oec
FROM okl_k_lines
WHERE id = l_link_asset_tbl(i).fin_asset_id;
SELECT NVL(oec, 0)
INTO l_asset_oec
FROM okl_k_lines
WHERE id = l_link_asset_tbl(i).fin_asset_id;
l_update_line_item_tbl(m).cle_id := l_link_asset_tbl(i).link_line_id;
l_update_line_item_tbl(m).item_id := l_link_asset_tbl(i).link_item_id;
l_update_line_item_tbl(m).chr_id := p_chr_id;
l_update_line_item_tbl(m).parent_cle_id := p_cle_id;
l_update_line_item_tbl(m).item_id1 := l_link_asset_tbl(i).fin_asset_id;
l_update_line_item_tbl(m).item_id2 := '#';
l_update_line_item_tbl(m).item_object1_code := 'OKX_COVASST';
l_update_line_item_tbl(m).serv_cov_prd_id := NULL;
l_update_line_item_tbl(m).capital_amount := l_assoc_amount;
l_update_line_item_tbl(m).name := l_link_asset_tbl(i).asset_number;
FETCH c_asset_number INTO l_update_line_item_tbl(m).name;
IF l_update_line_item_tbl.COUNT > 0 THEN
okl_contract_line_item_pvt.update_contract_line_item( p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => lx_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_item_tbl => l_update_line_item_tbl,
x_line_item_tbl => lx_line_item_tbl);
END create_update_link_assets;
SELECT cle.id fin_asset_id,
cle.name asset_number
FROM okc_k_lines_v cle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ('CANCELLED','TERMINATED');
SELECT cov_ast_cle.id cov_ast_cle_id,
cov_ast_cim.id cov_ast_cim_id
FROM okc_k_lines_b cov_ast_cle,
okc_k_items cov_ast_cim
WHERE cov_ast_cle.dnz_chr_id = p_chr_id
AND cov_ast_cle.cle_id = p_fee_cle_id
AND cov_ast_cim.cle_id = cov_ast_cle.id
AND cov_ast_cim.object1_id1 = TO_CHAR(p_fin_ast_id)
AND cov_ast_cim.object1_id2 = '#'
and cov_ast_cim.jtot_object1_code = 'OKX_COVASST';
create_update_link_assets (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_cle_id => p_cle_id,
p_chr_id => p_chr_id,
p_capitalize_yn => p_capitalize_yn,
p_link_asset_tbl => l_link_asset_tbl,
p_derive_assoc_amt => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT
START_DATE, END_DATE, ORIG_SYSTEM_SOURCE_CODE, ORIG_SYSTEM_ID1
FROM OKL_K_HEADERS_FULL_V
WHERE id = p_chr_id;
SELECT
gts.pricing_engine
FROM
okl_k_headers khr,
okl_products_v pdt,
okl_ae_tmpt_sets_v aes,
OKL_ST_GEN_TMPT_SETS gts
WHERE
khr.pdt_id = pdt.id AND
pdt.aes_id = aes.id AND
aes.gts_id = gts.id AND
khr.id = p_chr_id;
SELECT rule_information1
FROM okc_rules_v rul, okc_rule_groups_v rgb
WHERE rul.dnz_chr_id = p_chr_id AND
rule_information_category = 'LARVAU' AND
rgb.id = rul.rgp_id AND rgd_code like 'LARVIN';
SELECT kleb.id,Kleb.amount
FROM
okc_k_lines_b cleb,okl_k_lines kleb,okc_line_styles_b lseb
WHERE cleb.dnz_chr_id = p_chr_id AND
kleb.id = cleb.id AND
cleb.lse_id = lseb.id AND
lseb.lty_code = 'FEE' AND
kleb.fee_purpose_code = 'RVI';
SELECT styb.id, styb.code
FROM okl_strm_type_b styb,
okc_k_items cim,
okc_k_lines_b cleb,
okl_k_lines kle
WHERE styb.id = cim.object1_id1
AND '#' = cim.object1_id2
AND cim.jtot_object1_code = 'OKL_STRMTYP'
AND cim.cle_id = cleb.id
AND cim.dnz_chr_id = cleb.dnz_chr_id
AND cleb.lse_id = 52
AND kle.id = cleb.id
AND kle.fee_type = 'ABSORBED'
AND kle.fee_purpose_code = 'RVI'
AND cleb.dnz_chr_id = p_chr_id;
SELECT id
FROM okc_k_items_v
WHERE cle_id = fee_line_id;
update_fee_type(
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_fee_types_rec => l_fee_types_rec,
x_fee_types_rec => x_fee_types_rec);
delete_fee_type(
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_fee_types_rec => l_fee_types_rec);
delete_fee_type(
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_fee_types_rec => l_fee_types_rec);
PROCEDURE update_party(
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_kpl_rec IN party_rec_type,
x_kpl_rec OUT NOCOPY party_rec_type
) AS
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'update_party';
okl_k_party_roles_pvt.update_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_rec => lp_cplv_rec,
x_cplv_rec => lx_cplv_rec,
p_kplv_rec => lp_kplv_rec,
x_kplv_rec => lx_kplv_rec);