update tb_fdn_deviceaccount set czMC =replace(czMC,'站','') where zdmc in ('大连站','沈阳北站');
delete from tb_fdn_deviceaccount where zdmc is null;
update tb_fdn_deviceaccount set czMC =null where czMC='客运车间' and zdmc in ('大连站','沈阳北站');
--站段
update tb_fdn_deviceaccount set zdbh = (select deptcode from tb_aut_department where tb_aut_department.deptname = tb_fdn_deviceaccount.zdmc)
where exists(select 1 from tb_aut_department where tb_aut_department.deptname = tb_fdn_deviceaccount.zdmc) and zdmc in ('大连站','沈阳北站');
--车站
update tb_fdn_deviceaccount set CZbh = (select deptcode from tb_aut_department where tb_aut_department.deptname = tb_fdn_deviceaccount.czmc and tb_aut_department.dept_type_name = '车站')
where exists(select 1 from tb_aut_department where tb_aut_department.deptname = tb_fdn_deviceaccount.czmc and tb_aut_department.dept_type_name = '车站') and zdmc in ('大连站','沈阳北站');
--设备变动情况
update tb_fdn_deviceaccount set SBBDQKBH = (select para_code from tb_sys_parameter where para_type_Code = '22000' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.SBBDQKMC and tb_sys_parameter.para_name is not null
)
where exists(select 1 from tb_sys_parameter where para_type_Code = '39000' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.SBBDQKMC and tb_sys_parameter.para_name is not null) and zdmc in ('大连站','沈阳北站');
--来源方式
update tb_fdn_deviceaccount set LYFSBH = (select para_code from tb_sys_parameter where para_type_Code = '39000' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.LYFSMC and tb_sys_parameter.para_name is not null
)
where exists(select 1 from tb_sys_parameter where para_type_Code = '39000' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.LYFSMC and tb_sys_parameter.para_name is not null) and zdmc in ('大连站','沈阳北站');
--产权类型
update tb_fdn_deviceaccount set CQLXBH = (select para_code from tb_sys_parameter where para_type_Code = '10200' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.CQLXMC and tb_sys_parameter.para_name is not null
)
where exists(select 1 from tb_sys_parameter where para_type_Code = '10200' and
tb_sys_parameter.para_name = tb_fdn_deviceaccount.CQLXMC and tb_sys_parameter.para_name is not null) and zdmc in ('大连站','沈阳北站');
--更新类别信息
update tb_fdn_deviceaccount set sblxMC =sbflMC,sbflMC = sblxMC where sblxMC in ('第五类','第八类','第十二类','第六类','第十一类','第十三类','第十四类') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='机车车辆' where sbflMC = '第一类' and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='集装箱' where sbflMC = '第二类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='线路' where sbflMC = '第三类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='信号设备' where sbflMC = '第四类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='房屋' where sbflMC = '第五类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='建筑物' where sbflMC = '第六类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='机械动力设备' where sbflMC = '第七类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='运输启动设备' where sbflMC = '第八类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='传导设备' where sbflMC = '第九类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='电气化供电设备' where sbflMC = '第十类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='仪器仪表' where (sbflMC = '第十一类' or sbflMC = '十一类' or sbflMC = '第 十一类') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='工具及器具' where (sbflMC = '第十二类' or sbflMC = '十二类' or sbflMC = '第 十二类服务设备') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='通信设备' where (sbflMC = '第十三类' or sbflMC = '十三类') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='信息技术设备' where (sbflMC = '第十四类' or sbflMC = '十四类') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='高价互换配件' where sbflMC = '第十五类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='土地' where sbflMC = '第十六类'and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='其他' where sbflMC = '第十七类' or sbflMC is null and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='消防系统' where sbflMC = ' 消防系统' and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='其他' where (sbflMC = '其它' or sbflMC is null or sbflMC = ' ') and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='房屋' where sbflMC = '五类' and zdmc in ('大连站','沈阳北站');
update tb_fdn_deviceaccount set sbflMC ='建筑物' where sbflMC = '六类' and zdmc in ('大连站','沈阳北站');
--设备分类
update tb_fdn_deviceaccount set sbflbh = (select code from tb_fdn_devicetype where tb_fdn_devicetype.name = tb_fdn_deviceaccount.sbflMC and tb_fdn_devicetype.parentcode is null)
where exists(select 1 from tb_fdn_devicetype where tb_fdn_devicetype.name = tb_fdn_deviceaccount.sbflMC and tb_fdn_devicetype.parentcode is null) and zdmc in ('大连站','沈阳北站');
--设备类型
update tb_fdn_deviceaccount set sblxbh = (select code from tb_fdn_devicetype where tb_fdn_devicetype.name = tb_fdn_deviceaccount.sblxMC
and tb_fdn_devicetype.parentcode = tb_fdn_deviceaccount.sbflbh)
where exists(select 1 from tb_fdn_devicetype where tb_fdn_devicetype.name = tb_fdn_deviceaccount.sblxMC and tb_fdn_devicetype.parentcode = tb_fdn_deviceaccount.sbflbh )
and zdmc in ('大连站','沈阳北站');
--更新设备品牌
insert into TB_FDN_DEVICEBRAND (Sbflbh,Sbflmc,Sblxbh,Sblxmc,ppbh,Ppmc)
select distinct tb_fdn_deviceaccount.Sbflbh,tb_fdn_deviceaccount.sbflmc,
tb_fdn_deviceaccount.Sblxbh, tb_fdn_deviceaccount.sblxmc, NULL,tb_fdn_deviceaccount.ppmc from tb_fdn_deviceaccount where ppmc is not null
and concat(concat(ppmc,tb_fdn_deviceaccount.sbflmc), tb_fdn_deviceaccount.sblxmc) not in (select concat(concat(ppmc,sbflmc), sblxmc) from TB_FDN_DEVICEBRAND);
--设备分类品牌
update TB_FDN_DEVICEBRAND set sbflbh = (select code from tb_fdn_devicetype where tb_fdn_devicetype.name = TB_FDN_DEVICEBRAND.sbflMC and tb_fdn_devicetype.parentcode is null)
where exists(select 1 from tb_fdn_devicetype where tb_fdn_devicetype.name = TB_FDN_DEVICEBRAND.sbflMC and tb_fdn_devicetype.parentcode is null);
--设备类型品牌
update TB_FDN_DEVICEBRAND set sblxbh = (select code from tb_fdn_devicetype where tb_fdn_devicetype.name = TB_FDN_DEVICEBRAND.sblxMC
and tb_fdn_devicetype.parentcode = TB_FDN_DEVICEBRAND.sbflbh)
where exists(select 1 from tb_fdn_devicetype where tb_fdn_devicetype.name = TB_FDN_DEVICEBRAND.sblxMC and tb_fdn_devicetype.parentcode = TB_FDN_DEVICEBRAND.sbflbh );
update TB_FDN_DEVICEBRAND set ppbh = rownum;
update tb_fdn_deviceaccount set ppbh = (select ppbh from TB_FDN_DEVICEBRAND where TB_FDN_DEVICEBRAND.Ppmc = tb_fdn_deviceaccount.ppmc
and TB_FDN_DEVICEBRAND.Sbflbh = tb_fdn_deviceaccount.sbflbh
and nvl(TB_FDN_DEVICEBRAND.Sblxmc,'00000') = nvl(tb_fdn_deviceaccount.Sblxmc,'00000'))
where exists(select 1 from TB_FDN_DEVICEBRAND where TB_FDN_DEVICEBRAND.Ppmc = tb_fdn_deviceaccount.ppmc
and TB_FDN_DEVICEBRAND.Sbflbh = tb_fdn_deviceaccount.sbflbh
and nvl(TB_FDN_DEVICEBRAND.Sblxmc,'00000') = nvl(tb_fdn_deviceaccount.Sblxmc,'00000') );