[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID
FROM OKL_Strm_type_b
WHERE stream_type_purpose = p_stream_type_purpose;
select clet.name
from okc_line_styles_b addon_lseb,
okc_k_lines_b addon_cleb,
okc_line_styles_b model_lseb,
okc_k_lines_b model_cleb,
okc_k_lines_tl clet,
okc_line_styles_b lseb,
okc_k_lines_b cleb
where
addon_cleb.cle_id = model_cleb.id
and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
and addon_lseb.id = addon_cleb.lse_id
and addon_lseb.lty_code = 'ADD_ITEM'
--
and model_cleb.cle_id = cleb.id
and model_cleb.dnz_chr_id = cleb.dnz_chr_id
and model_lseb.id = model_cleb.lse_id
and model_lseb.lty_code = 'ITEM'
--
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.chr_id = p_rbk_chr_id --rebook copy
and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
--to avoid picking up new asset added during rebook
and cleb.orig_system_id1 is not null
and exists (select '1'
from okc_k_lines_b addon_cleb2,
okc_k_lines_b model_cleb2,
okc_k_lines_b cleb2
where addon_cleb2.id <> nvl(addon_cleb.orig_system_id1,-999)
and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
and cleb2.id = nvl(cleb.orig_system_id1,-999)
and addon_cleb2.cle_id = model_cleb2.id
and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
and model_cleb2.cle_id = cleb2.id
and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
and cleb2.chr_id = p_orig_chr_id --orig contract
and cleb2.dnz_chr_id = p_orig_chr_id);--orig contract
select clet.name
from okc_line_styles_b addon_lseb,
okc_k_lines_b addon_cleb,
okc_line_styles_b model_lseb,
okc_k_lines_b model_cleb,
okc_k_lines_tl clet,
okc_line_styles_b lseb,
okc_k_lines_b cleb
where
addon_cleb.cle_id = model_cleb.id
and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
and addon_lseb.id = addon_cleb.lse_id
and addon_lseb.lty_code = 'ADD_ITEM'
--
and model_cleb.cle_id = cleb.id
and model_cleb.dnz_chr_id = cleb.dnz_chr_id
and model_lseb.id = model_cleb.lse_id
and model_lseb.lty_code = 'ITEM'
--
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.chr_id = p_rbk_chr_id --rebook copy
and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
--to avoid picking up new asset added during rebook
and cleb.orig_system_id1 is not null
and not exists (select '1'
from okc_k_lines_b addon_cleb2,
okc_k_lines_b model_cleb2,
okc_k_lines_b cleb2
where addon_cleb2.id = nvl(addon_cleb.orig_system_id1,-999)
and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
and cleb2.id = nvl(cleb.orig_system_id1,-999)
and addon_cleb2.cle_id = model_cleb2.id
and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
and model_cleb2.cle_id = cleb2.id
and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
and cleb2.chr_id = p_orig_chr_id --orig contract
and cleb2.dnz_chr_id = p_orig_chr_id); --orig contract
select clet.name
from okc_k_items cim,
okc_line_styles_b addon_lseb,
okc_k_lines_b addon_cleb,
okc_line_styles_b model_lseb,
okc_k_lines_b model_cleb,
okc_k_lines_tl clet,
okc_line_styles_b lseb,
okc_k_lines_b cleb
where cim.cle_id = addon_cleb.id
and cim.dnz_chr_id = addon_cleb.dnz_chr_id
and addon_cleb.cle_id = model_cleb.id
and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
and addon_lseb.id = addon_cleb.lse_id
and addon_lseb.lty_code = 'ADD_ITEM'
--
and model_cleb.cle_id = cleb.id
and model_cleb.dnz_chr_id = cleb.dnz_chr_id
and model_lseb.id = model_cleb.lse_id
and model_lseb.lty_code = 'ITEM'
--
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.chr_id = p_rbk_chr_id --rebook copy
and cleb.dnz_chr_id = p_rbk_chr_id --rebook copy
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
--to avoid picking up new asset added during rebook
and cleb.orig_system_id1 is not null
and exists (select '1'
from okc_k_items cim2,
okc_k_lines_b addon_cleb2,
okc_k_lines_b model_cleb2,
okc_k_lines_b cleb2
where cim2.cle_id = addon_cleb2.id
and cim2.dnz_chr_id = addon_cleb2.dnz_chr_id
and ( (cim2.object1_id1 <> cim.object1_id1)
OR
(cim2.object1_id2 <> cim.object1_id2)
OR
(addon_cleb2.price_unit <> addon_cleb.price_unit)
)
and addon_cleb2.id = nvl(addon_cleb.orig_system_id1,-999)
and model_cleb2.id = nvl(model_cleb.orig_system_id1,-999)
and cleb2.id = nvl(cleb.orig_system_id1,-999)
and addon_cleb2.cle_id = model_cleb2.id
and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
and model_cleb2.cle_id = cleb2.id
and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
and cleb2.chr_id = p_orig_chr_id --orig contract
and cleb2.dnz_chr_id = p_orig_chr_id);--orig contract
Cursor l_deleted_addon_csr (p_rbk_chr_id in NUMBER,
p_orig_chr_id in NUMBER) is
select clet.name
from okc_line_styles_b addon_lseb,
okc_k_lines_b addon_cleb,
okc_line_styles_b model_lseb,
okc_k_lines_b model_cleb,
okc_k_lines_tl clet,
okc_line_styles_b lseb,
okc_k_lines_b cleb
where
addon_cleb.cle_id = model_cleb.id
and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
and addon_lseb.id = addon_cleb.lse_id
and addon_lseb.lty_code = 'ADD_ITEM'
--
and model_cleb.cle_id = cleb.id
and model_cleb.dnz_chr_id = cleb.dnz_chr_id
and model_lseb.id = model_cleb.lse_id
and model_lseb.lty_code = 'ITEM'
--
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.chr_id = p_orig_chr_id --orig contract
and cleb.dnz_chr_id = p_orig_chr_id --orig contract
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
and not exists (select '1'
from okc_k_lines_b addon_cleb2,
okc_k_lines_b model_cleb2,
okc_k_lines_b cleb2
where nvl(addon_cleb2.orig_system_id1,-999) = addon_cleb.id
and nvl(model_cleb2.orig_system_id1,-999) = model_cleb.id
--and nvl(cleb2.orig_system_id1,-999) = cleb2.id
and nvl(cleb2.orig_system_id1,-999) = cleb.id
and addon_cleb2.cle_id = model_cleb2.id
and addon_cleb2.dnz_chr_id = model_cleb2.dnz_chr_id
and model_cleb2.cle_id = cleb2.id
and model_cleb2.dnz_chr_id = cleb2.dnz_chr_id
and cleb2.chr_id = p_rbk_chr_id --rebook copy
and cleb2.dnz_chr_id = p_rbk_chr_id --rebook copy
--to avoid picking up new asset added during rebook
and cleb2.orig_system_id1 is not null);
select clet.name
from okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where kle.id = cleb.id
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.chr_id = p_rbk_chr_id --rebook chr_id
and cleb.dnz_chr_id = p_rbk_chr_id --rebook chr id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'FREE_FORM1'
--to avoid picking up new asset during rebook
and cleb.orig_system_id1 is not null
and exists (select '1'
from okl_k_lines kle2,
okc_k_lines_b cleb2
where kle2.id = cleb2.id
and cleb2.id = nvl(cleb.orig_system_id1,-999)
and (
-- Bug# 4721428: allow updates to trade-in
--(nvl(kle2.tradein_amount,-99) <> nvl(kle.tradein_amount,-99))
--OR
(nvl(kle2.capital_reduction,-99) <> nvl(kle.capital_reduction,-99))
OR
(nvl(kle2.capital_reduction_percent,-99) <> nvl(kle.capital_reduction_percent,-99))
)
and cleb2.chr_id = p_orig_chr_id --orig contract
and cleb2.dnz_chr_id = p_orig_chr_id); --orig_contract
open l_deleted_Addon_csr(p_rbk_chr_id => p_rbk_chr_id,
p_orig_chr_id => p_orig_chr_id);
Fetch l_deleted_Addon_csr into l_asset_number;
If l_deleted_addon_csr%NOTFOUND then
NULL;
Close l_deleted_Addon_csr;
If l_deleted_addon_csr%ISOPEN then
close l_deleted_addon_csr;
If l_deleted_addon_csr%ISOPEN then
close l_deleted_addon_csr;
Select chrb.bill_to_site_use_id,
chrb.cust_acct_id
from okc_k_headers_b chrb
where id = ChrId;
Select cleb.bill_to_site_use_id
from okc_k_lines_b cleb
where cleb.dnz_chr_id = ChrId
and cleb.sts_code <> 'ABANDONED'
and cleb.bill_to_site_use_id is not null;
Select cplb.bill_to_site_use_id,
cplb.cust_acct_id
from okc_k_party_roles_b cplb
where cplb.chr_id = ChrId
and cplb.dnz_chr_id = ChrId
and (cplb.bill_to_site_use_id is NOT NULL
OR
cplb.cust_acct_id is NOT NULL);
select 'A'
from okx_cust_site_uses_v site_use,
hz_cust_acct_sites_all site
where site_use.id1 = btoid
and site_use.site_use_code = 'BILL_TO'
and site_use.b_status = 'A'
--and site_use.cust_acct_site_status = 'A'
and site.cust_acct_site_id = site_use.cust_acct_site_id
and site.status = 'A'
and site_use.cust_account_id = custacctid;
select start_date
from okc_k_headers_b
where id = p_chr_id;
select rbk_cle.price_unit rbk_price_unit,
orig_cle.price_unit orig_price_unit
from okc_k_lines_b rbk_cle,
okc_line_styles_b rbk_lse,
okc_k_lines_b orig_cle
where rbk_cle.dnz_chr_id = p_rbk_chr_id
and rbk_lse.lty_code = p_item_lty_code
and rbk_cle.lse_id = rbk_lse.id
and orig_cle.id = rbk_cle.orig_system_id1
and orig_cle.dnz_chr_id = p_orig_chr_id;
SELECT '!',chr.orig_system_id1, ktrx.date_transaction_occurred,ktrx.id
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND CHR.id = p_chr_id
AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK'
AND ktrx.representation_type = 'PRIMARY'; -- MGAAP 7263041
SELECT '!', ktrx.date_transaction_occurred,
--Bug# 4212626
ktrx.id,
source_trx_id -- 4542290
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
where CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY' -- MGAAP 7263041
/*------------------------------------------------
--AND EXISTS (SELECT '1'
-- FROM okl_trx_contracts ktrx
-- WHERE ktrx.KHR_ID = chr.id
-- AND ktrx.tsu_code = 'ENTERED'
-- AND ktrx.rbr_code IS NOT NULL
-- AND ktrx.tcn_type = 'TRBK')
------------------------------------------------*/
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED'); --check with debdip
SELECT TCN_TYPE
FROM OKL_TRX_CONTRACTS
WHERE ID = p_trx_id;
SELECT '!'
FROM okc_k_headers_b CHR
WHERE chr.ID = p_chr_id
AND exists (SELECT '1'
FROM
OKC_LINE_STYLES_B lse,
OKC_K_LINES_B cle
WHERE cle.sts_code = 'APPROVED'
AND lse.id = cle.lse_id
AND lse.lty_code = 'USAGE'
AND cle.dnz_chr_id = chr.id);
SELECT '!'
FROM okc_k_headers_b CHR
where chr.ID = p_chr_id
AND nvl(chr.orig_system_source_code,'XXXX') = 'OKL_RELEASE';
SELECT '!'
FROM okc_k_headers_b CHR
WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
and chr.ID = p_chr_id
AND exists (SELECT '1'
FROM OKC_RULES_B rul
WHERE rul.dnz_chr_id = chr.id
AND rul.rule_information_category = 'LARLES'
AND nvl(rule_information1,'N') = 'Y');
SELECT cle.cle_id finasst_id,
cim.object1_id1 asset_id,
cle_orig.cle_id orig_finasst_id,
asr.id asset_return_id
FROM OKL_ASSET_RETURNS_B asr,
OKC_K_LINES_B cle_orig,
OKC_LINE_STYLES_B lse_orig,
OKC_K_ITEMS cim_orig,
OKC_K_ITEMS cim,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts,
OKL_TXL_ASSETS_B txl
WHERE asr.kle_id = cle_orig.cle_id
AND asr.ars_code = 'RE_LEASE'
AND cim.object1_id1 = cim_orig.object1_id1
AND cim.object1_id2 = cim_orig.object1_id2
AND cim.jtot_object1_code = cim_orig.jtot_object1_code
AND cim.id <> cim_orig.id
AND cle_orig.id = cim_orig.cle_id
AND cle_orig.dnz_chr_id = cim_orig.dnz_chr_id
AND cle_orig.lse_id = lse_orig.id
AND lse_orig.lty_code = 'FIXED_ASSET'
AND cim.cle_id = cle.id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND cle.id = txl.kle_id
AND cle.dnz_chr_id = p_rel_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FIXED_ASSET'
AND cle.sts_code = sts.code
AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
(select trx.tas_type,
ttyp.name
from OKL_TRX_ASSETS trx,
OKL_TRX_TYPES_TL ttyp
where trx.id = txl.tas_id
and trx.try_id = ttyp.id
and ttyp.name = 'Internal Asset Creation'
and ttyp.language = 'US'
and trx.tsu_code <> 'PROCESSED'
--Bug# 3533936
and trx.tas_type = 'CRL')
AND txl.tal_type = 'CRL';
select nvl(subb.effective_to_date,sysdate) effective_to_date,
nvl(subb.expire_after_days,0) expire_after_days,
clet_sub.name subsidy_name,
clet_asst.name asset_number,
subb.id subsidy_id, -- added for subsidy pools enhancement
kle_sub.amount subsidy_amount, -- added for subsidy pools enhancement
kle_sub.subsidy_override_amount subsidy_override_amount, -- added for subsidy pools enhancement
cleb_asst.start_date asset_start_date, -- added for subsidy pools enhancement
cleb_asst.id asset_id -- added for subsidy pools enhancement
,subb.effective_from_date effective_from_date -- added : Bug 6050165 : prasjain
from okl_subsidies_b subb,
okl_k_lines kle_sub,
okc_k_lines_tl clet_sub,
okc_k_lines_b cleb_sub,
okc_line_styles_b lseb_sub,
okc_k_lines_tl clet_asst,
okc_k_lines_b cleb_asst,
okc_line_styles_b lseb_asst
where subb.id = kle_sub.subsidy_id
--and nvl(subb.effective_to_date,sysdate)+nvl(subb.expire_after_days,0) < sysdate
and kle_sub.id = cleb_sub.id
and clet_sub.id = cleb_sub.id
and clet_sub.language = userenv('LANG')
and cleb_sub.cle_id = cleb_asst.id
and cleb_sub.dnz_chr_id = cleb_asst.dnz_chr_id
and cleb_sub.sts_code <> 'ABANDONED'
and lseb_sub.id = cleb_sub.lse_id
and lseb_sub.lty_code = 'SUBSIDY'
and clet_asst.id = cleb_asst.id
and clet_asst.language = userenv('LANG')
and cleb_asst.chr_id = p_chr_id
and cleb_asst.dnz_chr_id = p_chr_id
and lseb_asst.id = cleb_asst.lse_id
and lseb_asst.lty_code = 'FREE_FORM1'
and cleb_asst.sts_code <> 'ABANDONED';
Select 'Y'
from okl_subsidies_b sub,
okc_k_lines_b cleb
where sub.id = p_subsidy_id
and cleb.id = p_asset_cle_id
and TRUNC(cleb.start_date) between TRUNC(sub.effective_from_date)
and TRUNC(nvl(sub.effective_to_date,cleb.start_date));
Select rul.rule_information1 capitalize_interest_flag
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LACPLN';
Select contract_number
,start_date
From okc_k_headers_b chr
where chr.id = pchrid;
SELECT MAX(sel.stream_element_date) stream_element_date
FROM okl_strm_elements sel
WHERE sel.stm_id = p_stm_id
AND sel.date_billed IS NOT NULL;
Okl_Transaction_Pub.update_trx_status(
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_chrv_id,
p_status => 'PROCESSED',
x_tcnv_rec => l_tcnv_rec
);
okl_contract_status_pub.update_contract_status(
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_khr_status => 'ABANDONED',
p_chr_id => p_chrv_id);
Subsidy enhancement added as part of the bug#6688570. As per the update
given by the PM *** SRAWLING 11/20/07 10:16 am ***, the subsidy needs
to be billed even if the contract is for re-leased assets and the amount
needs to be tracked to a subsidy pool.
*/
---------------------------------------------------------------------------------------
--Bug# 3143522: Subsidies enhancement : Check for expired subsidies and stop activation
-- Create billing transaction for 'BILL' subsidies
---------------------------------------------------------------------------------------
l_subsidy_exists := OKL_API.G_FALSE;
okl_asset_returns_pub.update_asset_returns(
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_artv_rec => l_artv_rec
,x_artv_rec => lx_artv_rec);