[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID
FROM OKL_TXL_ASSETS_B
WHERE KLE_ID = p_clev_fa_id
AND ASSET_NUMBER = p_asset_number;
SELECT 'N' new_asset
FROM okl_asset_returns_b KAR
WHERE KAR.kle_id IN (SELECT KLE.cle_id
FROM okl_txl_assets_b TXL
,okl_txd_assets_b TXD
,okc_k_lines_b KLE
WHERE TXD.asset_number = p_asset_number
AND KLE.id = TXL.kle_id
AND TXL.id = TXD.tal_id )
AND KAR.ars_code = 'RE_LEASE';
PROCEDURE update_all_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_las_rec IN las_rec_type,
x_las_rec OUT NOCOPY las_rec_type) IS
l_clev_fin_rec OKL_OKC_MIGRATION_PVT.clev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ALL_LINE';
SELECT 1
FROM OKL_TXL_ITM_INSTS
WHERE dnz_cle_id = p_dnz_cle_id;
OKL_CREATE_KLE_PVT.update_all_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_new_yn => l_new_yn,
p_asset_number => p_las_rec.asset_number,
p_clev_fin_rec => l_clev_fin_rec,
p_klev_fin_rec => l_klev_fin_rec,
p_clev_model_rec => l_clev_model_rec,
p_cimv_model_rec => l_cimv_model_rec,
p_clev_fa_rec => l_clev_fa_rec,
p_cimv_fa_rec => l_cimv_fa_rec,
p_talv_fa_rec => l_talv_fa_rec,
p_clev_ib_rec => l_clev_ib_rec,
p_itiv_ib_rec => l_itiv_rec,
x_clev_fin_rec => x_clev_fin_rec,
x_clev_model_rec => x_clev_model_rec,
x_clev_fa_rec => x_clev_fa_rec,
x_clev_ib_rec => x_clev_ib_rec);
END update_all_line;
SELECT id, deal_type, sts_code
FROM okl_k_headers_full_v
WHERE id = p_khr_id;
SELECT cleb_mdl.id clev_model_id,
cim_mdl.object1_id1 inventory_item_id,
cim_mdl.object1_id2 inventory_org_id,
cim_mdl.number_of_items,
cleb_mdl.price_unit,
msit.description inventory_item_name
FROM okc_k_lines_b cleb_mdl,
okc_line_styles_b lse_mdl,
okc_k_items cim_mdl,
mtl_system_items_tl msit
WHERE cim_mdl.cle_id = cleb_mdl.id
AND cim_mdl.dnz_chr_id = cleb_mdl.dnz_chr_id
AND cim_mdl.jtot_object1_code = 'OKX_SYSITEM'
AND lse_mdl.id = cleb_mdl.lse_id
AND lse_mdl.lty_code = 'ITEM'
AND msit.inventory_item_id = cim_mdl.object1_id1
AND msit.organization_id = cim_mdl.object1_id2
AND msit.language = USERENV('LANG')
AND cleb_mdl.dnz_chr_id = p_dnz_chr_id
AND cleb_mdl.cle_id = p_clev_fin_id;
SELECT cleb_fa.id clev_fa_id,
txl.id txl_id,
txl.model_number,
kle_fa.year_built,
txl.fa_location_id,
ast_loc.name fa_location_name,
txl.manufacturer_name,
txl.asset_key_id,
ast_key.concatenated_segments asset_key_name,
cim_fa.object1_id1 asset_id
FROM okl_txl_assets_b txl,
okc_line_styles_b lse_fa,
okl_k_lines kle_fa,
okc_k_lines_b cleb_fa,
okl_asset_key_lov_uv ast_key,
okx_ast_locs_v ast_loc,
okc_k_items cim_fa
WHERE cleb_fa.id = kle_fa.id
AND lse_fa.id = cleb_fa.lse_id
AND lse_fa.lty_code = 'FIXED_ASSET'
AND cleb_fa.id = txl.kle_id
AND txl.asset_key_id = ast_key.code_combination_id(+)
AND txl.fa_location_id = ast_loc.location_id(+)
AND cleb_fa.cle_id = p_clev_fin_id
AND cleb_fa.dnz_chr_id = p_dnz_chr_id
and cleb_fa.id = cim_fa.cle_id
AND cleb_fa.dnz_chr_id = cim_fa.dnz_chr_id;
SELECT cleb_ib.id clev_ib_id,
iti.id,
iti.object_id1_new party_site_use_id
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okc_line_styles_b lse_inst,
okc_line_styles_b lse_ib,
okl_txl_itm_insts iti
WHERE cleb_inst.cle_id = p_clev_fin_id
AND cleb_inst.dnz_chr_id = p_dnz_chr_id
AND cleb_inst.lse_id = lse_inst.id
AND lse_inst.lty_code = 'FREE_FORM2'
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = lse_ib.id
AND lse_ib.lty_code = 'INST_ITEM'
AND iti.kle_id = cleb_ib.id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) party_site_name
FROM hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpu
WHERE hpu.party_site_use_id = p_party_site_use_id
AND hps.party_site_id = hpu.party_site_id
AND hl.location_id = hps.location_id;
select cle_ib.id clev_ib_id,
substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,
hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,
hl.postal_code,null,hl.country,null, null,null,null,null,null,
null,'n','n',80,1,1),1,80) party_site_name
from hz_locations hl,
hz_party_sites hps,
csi_item_instances csi,
okc_k_items cim_ib,
okc_line_styles_b lse_ib,
okc_k_lines_b cle_ib,
okc_line_styles_b lse_inst,
okc_k_lines_b cle_inst,
okc_line_styles_b lse_fin,
okc_k_lines_b cle_fin
where cle_fin.cle_id is null
and cle_fin.chr_id = cle_fin.dnz_chr_id
and lse_fin.id = cle_fin.lse_id
and lse_fin.lty_code = 'FREE_FORM1'
and cle_inst.cle_id = cle_fin.id
and cle_inst.dnz_chr_id = cle_fin.dnz_chr_id
and cle_inst.lse_id = lse_inst.id
and lse_inst.lty_code = 'FREE_FORM2'
and cle_ib.cle_id = cle_inst.id
and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
and cle_ib.lse_id = lse_ib.id
and lse_ib.lty_code = 'INST_ITEM'
and cim_ib.cle_id = cle_ib.id
and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
and cim_ib.object1_id1 = csi.instance_id
and cim_ib.object1_id2 = '#'
and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
and csi.install_location_id = hps.party_site_id
and csi.install_location_type_code = 'HZ_PARTY_SITES'
and hps.location_id = hl.location_id
and cle_fin.dnz_chr_id = p_chr_id
and cle_fin.id = p_clev_fin_id
order by cle_fin.id asc;
select faa.asset_key_ccid ccid,
fakw.concatenated_segments segs
from fa_additions faa,
fa_asset_keywords_kfv fakw
where faa.asset_key_ccid = fakw.code_combination_id
and faa.asset_number = p_asset_number;
select loc.name fa_location_name,
loc.id1 fa_location_id,
fa.manufacturer_name,
fa.model_number
from fa_distribution_history fa_hist,
okx_ast_locs_v loc,
fa_additions_b fa
where fa.asset_id = fa_hist.asset_id
and fa_hist.location_id = loc.location_id
and fa.asset_number = p_asset_number
and fa_hist.transaction_header_id_out is null
and fa_hist.retirement_id is null;
select name
from okc_k_lines_tl
where id = p_cle_id;
SELECT kle_fa.model_number,
kle_fa.year_built,
kle_fa.manufacturer_name
FROM okc_line_styles_b lse_fa,
okl_k_lines kle_fa,
okc_k_lines_b cleb_fa
WHERE cleb_fa.id = kle_fa.id
AND lse_fa.id = cleb_fa.lse_id
AND lse_fa.lty_code = 'FIXED_ASSET'
AND cleb_fa.cle_id = p_clev_fin_id
AND cleb_fa.dnz_chr_id = p_dnz_chr_id;
SELECT SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) ship_to_site_name
FROM hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu
WHERE csu.site_use_id = p_site_use_id
AND cas.cust_acct_site_id = csu.cust_acct_site_id
AND hps.party_site_id = cas.party_site_id
AND hl.location_id = hps.location_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_labill_rgpv_rec,
x_rgpv_rec => lx_labill_rgpv_rec);
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_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
p_edit_mode => 'N',
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_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_lapmth_rulv_rec,
x_rulv_rec => lx_lapmth_rulv_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_labacc_rulv_rec,
x_rulv_rec => lx_labacc_rulv_rec);
SELECT clet_fin.name asset_number,
clet_fin.item_description,
cleb_fin.bill_to_site_use_id,
csu.location bill_to_site_name
FROM okc_k_lines_b cleb_fin,
hz_cust_site_uses_all csu ,
okc_k_lines_tl clet_fin
WHERE cleb_fin.id = p_cle_id
AND csu.site_use_id = cleb_fin.bill_to_site_use_id
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = userenv('LANG');
SELECT rul.rgp_id,rgp.rgd_code,rul.ID,rul.object1_id1,rul.object1_id2,rul.rule_information1,rul.rule_information2,
rul.rule_information3, rul.rule_information4
FROM okc_rules_b rul,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = p_rgd_code
AND rgp.id = rul.rgp_id
AND rgp.dnz_chr_id = rul.dnz_chr_id
AND rul.rule_information_category = p_rule_info_cat;
SELECT name
FROM okx_receipt_methods_v
WHERE id1 = p_object1_id1;
SELECT description name,bank bank_name
FROM okx_rcpt_method_accounts_v
WHERE id1 = p_object1_id1;
PROCEDURE update_assetaddon_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_addon_rec IN addon_rec_type,
x_addon_rec OUT NOCOPY addon_rec_type) IS
BEGIN
null;
END update_assetaddon_line;
PROCEDURE update_assetaddon_line(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_addon_tbl IN addon_tbl_type,
x_addon_tbl OUT NOCOPY addon_tbl_type) IS
BEGIN
null;
END update_assetaddon_line;
SELECT cleb_fin.id fin_asset_id,
clet_fin.name asset_number,
clet_fin.item_description description,
NVL(kle_fin.oec,0) oec
FROM okc_k_lines_b cleb_fin,
okc_k_lines_tl clet_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin,
okc_statuses_b sts
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = USERENV('LANG')
AND cleb_fin.id = kle_fin.id
AND lse_fin.id = cleb_fin.lse_id
AND lse_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
SELECT cleb_fin.id fin_asset_id,
clet_fin.name asset_number,
clet_fin.item_description description,
NVL(kle_fin.oec,0) oec
FROM okc_k_lines_b cleb_fin,
okc_k_lines_tl clet_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin,
okc_statuses_b sts
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = USERENV('LANG')
AND cleb_fin.id = kle_fin.id
AND lse_fin.id = cleb_fin.lse_id
AND lse_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND kle_fin.tradein_amount IS NULL
AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
SELECT SUM(NVL(kle_fin.tradein_amount,0)) amount
FROM okc_k_lines_b cleb_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND cleb_fin.sts_code = 'TERMINATED'
AND kle_fin.id = cleb_fin.id
AND lse_fin.id = cleb_fin.id
AND lse_fin.lty_code = 'FREE_FORM1';
ELSIF p_mode = 'UPDATE' THEN
FOR l_asset IN c_assets LOOP
i := i + 1;
SELECT currency_code
INTO l_currency_code
FROM okc_k_headers_b
WHERE id = l_chr_id;
SELECT cleb_fin.id fin_asset_id,
clet_fin.name asset_number,
clet_fin.item_description description,
NVL(kle_fin.oec,0) oec,
NVL(kle_fin.capitalize_down_payment_yn,'Y') capitalize_yn,
NVL(kle_fin.down_payment_receiver_code,'LESSOR') receiver_code
FROM okc_k_lines_b cleb_fin,
okc_k_lines_tl clet_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin,
okc_statuses_b sts
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = USERENV('LANG')
AND cleb_fin.id = kle_fin.id
AND lse_fin.id = cleb_fin.lse_id
AND lse_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
SELECT cleb_fin.id fin_asset_id,
clet_fin.name asset_number,
clet_fin.item_description description,
NVL(kle_fin.oec,0) oec,
NVL(kle_fin.capitalize_down_payment_yn,'Y') capitalize_yn,
NVL(kle_fin.down_payment_receiver_code,'LESSOR') receiver_code
FROM okc_k_lines_b cleb_fin,
okc_k_lines_tl clet_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin,
okc_statuses_b sts
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = USERENV('LANG')
AND cleb_fin.id = kle_fin.id
AND lse_fin.id = cleb_fin.lse_id
AND lse_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND (kle_fin.capital_reduction IS NULL AND
kle_fin.capital_reduction_percent IS NULL)
AND NVL(kle_fin.re_lease_yn,'N') = 'N'; --ndani - 01-Apr-2013, Bug# 16567807, Adjustments should not be allowed for re-lease assets
SELECT SUM( NVL(kle_fin.capital_reduction,0) +
(NVL(kle_fin.capital_reduction_percent,0)/100 * kle_fin.oec)) amount
FROM okc_k_lines_b cleb_fin,
okl_k_lines kle_fin,
okc_line_styles_b lse_fin
WHERE cleb_fin.dnz_chr_id = p_chr_id
AND cleb_fin.chr_id = p_chr_id
AND cleb_fin.sts_code = 'TERMINATED'
AND kle_fin.id = cleb_fin.id
AND lse_fin.id = cleb_fin.id
AND lse_fin.lty_code = 'FREE_FORM1';
ELSIF p_mode = 'UPDATE' THEN
FOR l_asset IN c_assets LOOP
i := i + 1;
SELECT currency_code
INTO l_currency_code
FROM okc_k_headers_b
WHERE id = l_chr_id;
SELECT OKL_ACCOUNTING_UTIL.format_amount(SUM(NVL(kle_sub.subsidy_override_amount,kle_sub.amount)), cleb_sub.currency_code) subsidy_amount
FROM okl_k_lines kle_sub,
okc_k_lines_b cleb_sub
WHERE kle_sub.subsidy_id = p_subsidy_id
AND cleb_sub.dnz_chr_id = p_khr_id
AND cleb_sub.id = kle_sub.id
AND cleb_sub.sts_code <> 'ABANDONED'
GROUP BY cleb_sub.currency_code;
SELECT OKL_ACCOUNTING_UTIL.format_amount(SUM( NVL(kle_fin.capital_reduction,0) + (NVL(kle_fin.capital_reduction_percent,0)/100 * kle_fin.oec) ), cleb_fin.currency_code) down_payment_amount
FROM okl_k_lines kle_fin,
okc_k_lines_b cleb_fin
WHERE cleb_fin.chr_id = p_khr_id
AND cleb_fin.dnz_chr_id = p_khr_id
AND kle_fin.id = cleb_fin.id
AND (kle_fin.capital_reduction_percent IS NOT NULL OR
kle_fin.capital_reduction IS NOT NULL)
GROUP BY cleb_fin.currency_code;