[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(sum(nvl(sub_kle.subsidy_override_amount,nvl(sub_kle.amount,0))),0)
from okl_subsidies_b subb,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
where subb.id = sub_kle.subsidy_id
and subb.accounting_method_code = 'NET'
and sub_kle.id = sub_cle.id
and sub_cle.cle_id = p_asset_cle_id
and sub_cle.lse_id = sub_lse.id
and sub_lse.lty_code = 'SUBSIDY';
/* SELECT 'x'
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 'x'
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_k_lines_b CLEB
,okl_k_lines KLE
WHERE CLEB.id = KLE.id
AND CLEB.dnz_chr_id = p_chr_id
AND CLEB.chr_id = p_chr_id
AND CLEB.LSE_ID = 33 --for asset line
AND NVL(KLE.re_lease_yn,'N')= 'Y')
AND NOT EXISTS (SELECT '1'
FROM okc_k_lines_b CLEB
,okl_k_lines KLE
WHERE CLEB.id = KLE.id
AND CLEB.dnz_chr_id = p_chr_id
AND CLEB.chr_id = p_chr_id
AND CLEB.LSE_ID = 33 --for asset line
AND NVL(KLE.re_lease_yn,'N')= 'N');
SELECT 'x'
/* FROM dual
WHERE exists
(SELECT (1) */
FROM OKC_K_LINES_B cle,
OKC_K_ITEMS itm,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND itm.dnz_chr_id = cle.dnz_chr_id
AND itm.cle_id = cle.id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
AND itm.object1_id1 is not null
AND itm.object1_id2 is not null
AND sts.code = cle.sts_code;
SELECT 'x'
/* FROM dual
WHERE exists
(SELECT (1) */
FROM OKC_K_LINES_B cle,
OKC_K_ITEMS itm,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND itm.dnz_chr_id = cle.dnz_chr_id
AND itm.cle_id = cle.id
AND lse.id = cle.lse_id
AND lse.lty_code = 'INST_ITEM'
AND itm.object1_id1 is not null
AND itm.object1_id2 is not null
AND sts.code = cle.sts_code;
SELECT 'x'
/* FROM dual
WHERE exists
(SELECT (1) */
FROM OKC_K_LINES_B cle,
OKC_K_ITEMS itm,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND itm.dnz_chr_id = cle.dnz_chr_id
AND itm.cle_id = cle.id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
AND itm.object1_id1 is null
AND itm.object1_id2 is null
AND sts.code = cle.sts_code;
select cle_fin.id fin_line_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) location_id
from hz_locations hl,
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.location_type_code = 'HZ_LOCATIONS'
-- and csi.location_id = hl.location_id
and csi.install_location_type_code = 'HZ_LOCATIONS' -- cklee
and csi.install_location_id = hl.location_id -- cklee
and cle_fin.dnz_chr_id = p_dnz_chr_id
order by cle_fin.id asc;
select cle_fin.id fin_line_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) location_id
from hz_locations hl,
hz_party_sites hps,
--Bug# 3569441 :
--hz_party_site_uses hpsu,
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'
--Bug# 3569441 :
--and csi.install_location_id = hpsu.party_site_use_id
and csi.install_location_id = hps.party_site_id
and csi.install_location_type_code = 'HZ_PARTY_SITES'
--and hpsu.site_use_type = 'INSTALL_AT'
--and hpsu.party_site_id = hps.party_site_id
and hps.location_id = hl.location_id
and cle_fin.dnz_chr_id = p_dnz_chr_id
order by cle_fin.id asc;
select cle_fin.id fin_line_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) location_id
from hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpsu,
okl_txl_itm_insts iti,
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 iti.kle_id = cle_ib.id
and iti.object_id1_new = hpsu.party_site_use_id
and iti.object_id2_new = '#'
and hpsu.party_site_id = hps.party_site_id
and hps.location_id = hl.location_id
and cle_fin.dnz_chr_id = p_dnz_chr_id
order by cle_fin.id asc;
select nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
-- sts.meaning sts_code,
-- nalc.location_id location_id,
-- nast.fin_line_id parent_line_id,
nast.parent_line_id,
nast.dnz_chr_id
from okl_new_assets_uv nast
where nast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
-- and nvl(upper(nalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(nalc.location_id),'x'))
-- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and nast.sts_code <> 'ABANDONED'
-- and nast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
--bug# 4202325 : added following condition
and nast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
-- sts.meaning sts_code,
-- oalc.location_id location_id,
-- oast.fin_line_id parent_line_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
-- and nvl(upper(oalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(oalc.location_id),'x'))
-- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and oast.sts_code <> 'ABANDONED'
-- and oast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
and oast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
-- sts.meaning sts_code,
-- nalc.location_id location_id,
-- nast.fin_line_id parent_line_id,
nast.parent_line_id,
nast.dnz_chr_id
from okl_new_assets_uv nast
where nast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
-- and nvl(upper(nalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(nalc.location_id),'x'))
-- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and nast.sts_code <> 'ABANDONED'
-- and nast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
--bug# 4202325 : added following condition
and nast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
-- sts.meaning sts_code,
-- oalc.location_id location_id,
-- oast.fin_line_id parent_line_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
-- and nvl(upper(oalc.location_id),'x') like nvl(upper(p_las_rec.location_id),nvl(upper(oalc.location_id),'x'))
-- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and oast.sts_code <> 'ABANDONED'
-- and oast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
and oast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
SELECT st.ste_code,
khr.deal_type
-- FROM OKL_K_HEADERS_FULL_V chr,
FROM okc_k_headers_b chr,
okl_k_headers khr,
okc_statuses_b st
WHERE khr.id = chr.id
and chr.id = p_chr_id
and st.code = chr.sts_code;
SELECT 'x'
/*FROM dual
WHERE exists
(SELECT (1)*/
FROM OKC_K_LINES_B cle,
OKC_K_ITEMS itm,
OKC_LINE_STYLES_B lse,
OKC_STATUSES_B sts
WHERE cle.dnz_chr_id = p_dnz_chr_id
AND itm.dnz_chr_id = cle.dnz_chr_id
AND itm.cle_id = cle.id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
AND itm.object1_id1 is not null
AND itm.object1_id2 is not null
AND sts.code = cle.sts_code
AND sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');--);
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
-- sts.meaning sts_code,
-- oalc.location_id location_id,
-- oast.fin_line_id parent_line_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_loan_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
-- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and oast.sts_code <> 'ABANDONED'
-- and oast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
and oast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
-- sts.meaning sts_code,
-- oalc.location_id location_id,
-- oast.fin_line_id parent_line_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_loan_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
-- and oast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and oast.sts_code <> 'ABANDONED'
-- and oast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
and oast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
select nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
-- sts.meaning sts_code,
-- nalc.location_id location_id,
-- nast.fin_line_id parent_line_id,
nast.parent_line_id,
nast.dnz_chr_id
from okl_new_loan_assets_uv nast
where nast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
-- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and nast.sts_code <> 'ABANDONED'
-- and nast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
--bug#4202325 Added following condition
and nast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
-- sts.meaning sts_code,
-- nalc.location_id location_id,
-- nast.fin_line_id parent_line_id,
nast.parent_line_id,
nast.dnz_chr_id
from okl_new_loan_assets_uv nast
where nast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(nast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(nast.asset_number))
and nvl(upper(nast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(nast.vendor_name),'x'))
and nast.oec between nvl(p_las_rec.from_oec,nast.oec) and nvl(p_las_rec.to_oec,nast.oec)
and nvl(nast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(nast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(nast.residual_value,0))
and upper(nast.description) like nvl(upper(p_las_rec.description),upper(nast.description))
and nast.sts_code like nvl(p_las_rec.sts_code,nast.sts_code)
and nvl(nast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(nast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(nast.start_date,to_date('1111','yyyy')))
and nvl(nast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(nast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(nast.end_date,to_date('1111','yyyy')))
and nvl(nast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(nast.date_terminated,to_date('1111','yyyy')))
-- and nast.dnz_chr_id = p_las_rec.dnz_chr_id
-- and nast.sts_code <> 'ABANDONED'
-- and nast.sts_code = sts.code
-- and sts.LANGUAGE = userenv('LANG')
--bug#4202325 Added following condition
and nast.ASSET_STATUS_CODE <> 'ABANDONED'
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
oast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = oast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= oast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
-- sts.meaning sts_code,
-- oalc.location_id location_id,
-- oast.fin_line_id parent_line_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
oast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = oast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= oast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_loan_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
oast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = oast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= oast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) desc;
select oast.asset_number,
oast.year_manufactured,
oast.manufacturer_name,
oast.description,
oast.current_units,
oast.oec,
oast.vendor_name,
oast.residual_value,
oast.start_date,
oast.end_date,
oast.date_terminated,
oast.sts_code,
oast.location_id,
oast.parent_line_id,
oast.dnz_chr_id
from okl_old_loan_assets_uv oast
where oast.dnz_chr_id = p_las_rec.dnz_chr_id
and upper(oast.asset_number) like nvl(upper(p_las_rec.asset_number),upper(oast.asset_number))
and nvl(upper(oast.vendor_name),'x') like nvl(upper(p_las_rec.vendor_name),nvl(upper(oast.vendor_name),'x'))
and oast.oec between nvl(p_las_rec.from_oec,oast.oec) and nvl(p_las_rec.to_oec,oast.oec)
and nvl(oast.residual_value,0) between nvl(p_las_rec.from_residual_value,nvl(oast.residual_value,0)) and nvl(p_las_rec.to_residual_value,nvl(oast.residual_value,0))
and upper(oast.description) like nvl(upper(p_las_rec.description),upper(oast.description))
and oast.sts_code like nvl(p_las_rec.sts_code,oast.sts_code)
and nvl(oast.start_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_start_date,nvl(oast.start_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_start_date,nvl(oast.start_date,to_date('1111','yyyy')))
and nvl(oast.end_date,to_date('1111','yyyy')) between nvl(p_las_rec.from_end_date,nvl(oast.end_date,to_date('1111','yyyy'))) and nvl(p_las_rec.to_end_date,nvl(oast.end_date,to_date('1111','yyyy')))
and nvl(oast.date_terminated,to_date('1111','yyyy')) between nvl(p_las_rec.from_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy'))) and nvl(p_las_rec.to_date_terminated,nvl(oast.date_terminated,to_date('1111','yyyy')))
and ( oast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
oast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = oast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= oast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
order by decode(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',to_char(start_date,'dd-mon-yyyy')
,'ETDT',to_char(end_date,'dd-mon-yyyy')
,'TRDT',to_char(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) asc;
SELECT nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
nast.parent_line_id,
nast.dnz_chr_id
FROM okl_new_assets_uv nast
WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
--bug# 4202325 : added following condition
AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
nast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = nast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= nast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
ORDER BY DECODE(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
--Bug# 2747693
--,'DESC',description
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) ASC;
SELECT nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
nast.parent_line_id,
nast.dnz_chr_id
FROM okl_new_assets_uv nast
WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
--bug# 4202325 : added following condition
AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
nast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = nast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= nast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
ORDER BY DECODE(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) DESC;
SELECT nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
nast.parent_line_id,
nast.dnz_chr_id
FROM okl_new_loan_assets_uv nast
WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
nast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = nast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= nast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
ORDER BY DECODE(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) ASC;
SELECT nast.asset_number,
nast.year_manufactured,
nast.manufacturer_name,
nast.description,
nast.current_units,
nast.oec,
nast.vendor_name,
nast.residual_value,
nast.start_date,
nast.end_date,
nast.date_terminated,
nast.sts_code,
nast.location_id,
nast.parent_line_id,
nast.dnz_chr_id
FROM okl_new_loan_assets_uv nast
WHERE nast.dnz_chr_id = p_las_rec.dnz_chr_id
AND UPPER(nast.asset_number) LIKE NVL(UPPER(p_las_rec.asset_number),UPPER(nast.asset_number))
AND NVL(UPPER(nast.vendor_name),'x') LIKE NVL(UPPER(p_las_rec.vendor_name),NVL(UPPER(nast.vendor_name),'x'))
AND nast.oec BETWEEN NVL(p_las_rec.from_oec,nast.oec) AND NVL(p_las_rec.to_oec,nast.oec)
AND NVL(nast.residual_value,0) BETWEEN NVL(p_las_rec.from_residual_value,NVL(nast.residual_value,0)) AND NVL(p_las_rec.to_residual_value,NVL(nast.residual_value,0))
AND UPPER(nast.description) LIKE NVL(UPPER(p_las_rec.description),UPPER(nast.description))
AND nast.sts_code LIKE NVL(p_las_rec.sts_code,nast.sts_code)
AND NVL(nast.start_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_start_date,NVL(nast.start_date,TO_DATE('1111','yyyy')))
AND NVL(nast.end_date,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_end_date,NVL(nast.end_date,TO_DATE('1111','yyyy')))
AND NVL(nast.date_terminated,TO_DATE('1111','yyyy')) BETWEEN NVL(p_las_rec.from_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy'))) AND NVL(p_las_rec.to_date_terminated,NVL(nast.date_terminated,TO_DATE('1111','yyyy')))
AND ( nast.ASSET_STATUS_CODE <> 'ABANDONED'
OR (
nast.ASSET_STATUS_CODE = 'ABANDONED'
and exists (
select 1
FROM okl_txl_assets_b a, okl_trx_assets b, okl_txd_assets_b c,okl_trx_types_tl d
where a.tas_id = b.id
and b.tsu_code = 'PROCESSED'
and c.tal_id = a.id
and c.split_percent is not null
and a.kle_id =(
SELECT cle.id
FROM OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE cle.dnz_chr_id = nast.dnz_chr_id
AND lse.id = cle.lse_id
AND lse.lty_code = 'FIXED_ASSET'
and cle_id= nast.parent_line_id
)
AND b.try_id = D.ID
AND D.LANGUAGE = 'US'
AND D.NAME = 'Split Asset'
)
)
)
ORDER BY DECODE(p_las_rec.p_order_by
,'AST',asset_number
,'YRMF',year_manufactured
,'MFNM',manufacturer_name
,'DESC',4
,'QTY',current_units
,'OEC',oec
,'VEDN',vendor_name
,'RESV',residual_value
,'STDT',TO_CHAR(start_date,'dd-mon-yyyy')
,'ETDT',TO_CHAR(end_date,'dd-mon-yyyy')
,'TRDT',TO_CHAR(date_terminated,'dd-mon-yyyy')
,'STS',sts_code
,'LOC',location_id
,asset_number) DESC;
PROCEDURE update_contract_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_id IN NUMBER,
p_date_delivery_expected IN DATE,
p_date_funding_expected IN DATE,
p_org_id IN NUMBER,
p_organization_id IN NUMBER
) AS
l_api_name VARCHAR2(30) := 'update_contract_line';
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);
PROCEDURE update_contract_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_id IN NUMBER,
p_chr_id IN NUMBER,
p_manufacturer_name IN VARCHAR2,
p_model_number IN VARCHAR2,
p_year_of_manufacture IN VARCHAR2,
p_vendor_name IN VARCHAR2,
p_vendor_id IN VARCHAR2,
p_cpl_id IN NUMBER,
p_notes IN VARCHAR2
) AS
l_api_name VARCHAR2(30) := 'update_contract_line';
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);
PROCEDURE update_fin_cap_cost(
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_new_yn IN VARCHAR2,
p_asset_number IN VARCHAR2,
p_top_line_id IN NUMBER,
p_dnz_chr_id IN NUMBER,
p_capital_reduction IN NUMBER,
p_capital_reduction_percent IN NUMBER,
p_oec IN NUMBER,
p_cap_down_pay_yn IN VARCHAR2,
p_down_payment_receiver IN VARCHAR2
) AS
l_api_name VARCHAR2(30) := 'update_fin_cap_cost';
SELECT kle.tradein_amount
FROM okl_k_lines kle
WHERE kle.id = p_top_line_id;
OKL_CREATE_KLE_PUB.update_fin_cap_cost(
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 => p_new_yn,
p_asset_number => p_asset_number,
p_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
PROCEDURE update_fin_cap_cost(
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_fin_adj_tbl IN fin_adj_tbl_type
) AS
l_api_name VARCHAR2(30) := 'update_fin_cap_cost_tbl';
update_fin_cap_cost(
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 => lp_new_yn,
p_asset_number => lp_asset_number,
p_top_line_id => l_fin_adj_rec.p_top_line_id,
p_dnz_chr_id => l_fin_adj_rec.p_dnz_chr_id,
p_capital_reduction => l_fin_adj_rec.p_capital_reduction,
p_capital_reduction_percent => l_fin_adj_rec.p_capital_reduction_percent,
p_oec => l_fin_adj_rec.p_oec,
p_cap_down_pay_yn => l_fin_adj_rec.p_cap_down_pay_yn,
p_down_payment_receiver => l_fin_adj_rec.p_down_payment_receiver);