create or replace function fn_assetode(i_type_code in varchar2,
i_src_COLM IN varchar2,
i_date in varchar2,
i_src_cd in varchar2)
return varchar2 as
/*定义变量*/
o_tar_code varchar2(40);
begin
if i_type_code=‘1‘ then
select fi_id into o_tar_code
from t1
where
(case when i_scr_cd in (‘NFC‘) THEN NFC_ASSET_ID
WHEN i_scr_cd IN (‘GLD‘) THEN GLD_ASSET_ID)=i_src_COLM
ELSIF (i_type_code=‘2‘ OR i_type_code=‘3‘) THEN
SELECT t2.fi_id into o_tar_code
from (select distinct new_windcode ,asharecode , row_number() over(partition by new_windcode order by times desc) rn from win_asset_id_mapping)t1
inner join t2
on t1.asharecode=t2.asharecode
where t1.asharecode is not null and t1.rn=1
and t1.new_windcode=i_src_COLM
elsif i_type_code=‘4‘ then
select fi_id into o_tar_code
from t1
where to_date(i_date,‘YYYYMMDDHH24MISS‘)>=txdate else
o_tar_code=‘‘;
end if;
return o_tar_code;
end fn_assetode;