|
CREATE OR REPLACE procedure p_dev_change_total( --集装箱公司设备变动台帐
ls_year varchar2 --年份 ) as ls_dev_nam dev_change_total.DEV_NAM%type; --机械名称 ls_dev_no dev_change_total.DEV_NO%type; --机械编号 ls_stand_txt dev_change_total.STAND_TXT%type; --规格 ls_model_txt dev_change_total.MODEL_TXT%type; --型号 ln_capacity_num dev_change_total.CAPACITY_NUM%type; --安全负荷 ls_factory_nam dev_change_total.FACTORY_NAM%type; --制造厂家 ld_out_dte dev_change_total.OUT_DTE%type; --制造年月 ls_change_instance dev_change_total.CHANGE_INSTANCE%type; --变动情况 ls_change_reason dev_change_total.CHANGE_REASON%type; --变动原因 ls_change_nbr dev_change_total.CHANGE_NBR%type; --序号 ln_change_no dev_change_total.CHANGE_NO%type; --序列号 ldt_change_dte date; ls_kind_cod1_no varchar(4); ls_kind_cod1_nam varchar(16); ln_kind_cod1_id number(3,0); ln_kind_cod1_xlh number(3,0); --定义游标 --“新增” Cursor get_dev_change_new Is Select dev_no,dev_nam,stand_txt,model_txt,capacity_num,factory_nam,out_dte,card_dte From dev_card Where dev_kind_cod1 = ls_kind_cod1_no and (nvl(mana_stat_cod,‘1‘) <> ‘7‘) and to_char(card_dte,‘yyyy‘) = ls_year order by dev_no; --“租用” Cursor get_dev_change_hire Is Select dev_no,dev_nam,stand_txt,model_txt,capacity_num,factory_nam,out_dte,card_dte From dev_card Where dev_kind_cod1 = ls_kind_cod1_no and (nvl(mana_stat_cod,‘1‘) = ‘7‘) and to_char(card_dte,‘yyyy‘) = ls_year order by dev_no; --“出租” Cursor get_dev_change_lend Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.lend_dte From dev_card a,dev_led b Where b.DEV_no = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and a.mana_stat_cod = ‘6‘ and to_char(b.lend_dte,‘yyyy‘) = ls_year order by a.dev_no; --“封存” Cursor get_dev_change_depot Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.reason_txt,b.depot_dte From dev_card a,dev_depot b Where b.DEV_no = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.depot_dte,‘yyyy‘) = ls_year order by a.dev_no,b.depot_dte; --“启封” Cursor get_dev_change_depot_start Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.sreason_txt,b.start_dte From dev_card a,dev_depot b Where b.DEV_no = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.start_dte,‘yyyy‘) = ls_year order by a.dev_no,b.start_dte; --“闲置” Cursor get_dev_change_idle Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.ireson_txt,b.idle_dte From dev_card a,dev_idle b Where b.DEV_cod = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.idle_dte,‘yyyy‘) = ls_year order by a.dev_no,b.idle_dte; --“闲置启用” Cursor get_dev_change_idle_start Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.breason_txt,b.begin_dte From dev_card a,dev_idle b Where b.DEV_cod = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.begin_dte,‘yyyy‘) = ls_year order by a.dev_no,b.begin_dte; --“停用” Cursor get_dev_change_stop Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.sreason_txt,b.stop_dte From dev_card a,dev_stop b Where b.stop_cod = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.stop_dte,‘yyyy‘) = ls_year order by a.dev_no,b.stop_dte; --“停用启用” Cursor get_dev_change_stop_start Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.breason_txt,b.begin_dte From dev_card a,dev_stop b Where b.stop_cod = a.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.begin_dte,‘yyyy‘) = ls_year order by a.dev_no,b.begin_dte; --取报废的设备信息 Cursor get_dev_change_reject Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,c.reject_rea,b.reject_dte From dev_card a,dev_disposal_app_b b,dev_reject_app_b c Where b.DEV_NO = a.DEV_NO and b.DEV_NO = c.DEV_NO and a.dev_kind_cod1 = ls_kind_cod1_no and a.mana_stat_cod = ‘0‘ and to_char(b.reject_dte,‘yyyy‘) = ls_year order by a.dev_no; --取外调的设备信息 Cursor get_dev_change_remove Is Select a.dev_no,a.dev_nam,a.stand_txt,a.model_txt,a.capacity_num,a.factory_nam,a.out_dte,b.reason_txt,b.remove_dte From dev_card a,dev_remove b Where b.DEV_COD = a.DEV_NO and b.MOVE_MOD = ‘0‘ and a.dev_kind_cod1 = ls_kind_cod1_no and to_char(b.remove_dte,‘yyyy‘) = ls_year order by a.dev_no,b.remove_dte; --取得设备类别1 cursor get_kind_cod1 is select dev_kid_cod1,dev_kid_nam1 from c_dev_typ1 order by dev_kid_cod1; begin --依次对各种设备类别1的设备进行新增、报废、外调这三种情况的统计 open get_kind_cod1; ln_change_no := 1; loop fetch get_kind_cod1 into ls_kind_cod1_no,ls_kind_cod1_nam; exit when get_kind_cod1%notfound; ln_kind_cod1_xlh := ln_change_no; ln_change_no := ln_change_no + 1; -- 新增 open get_dev_change_new; ln_kind_cod1_id := 1; loop fetch get_dev_change_new into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ldt_change_dte; exit when get_dev_change_new%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 增加‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把新增的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_new; -- 租用 open get_dev_change_hire; loop fetch get_dev_change_hire into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ldt_change_dte; exit when get_dev_change_hire%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 租用‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把租用的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_hire; -- 出租 open get_dev_change_lend; loop fetch get_dev_change_lend into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ldt_change_dte; exit when get_dev_change_lend%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 出租‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把出租的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_lend; -- 封存 open get_dev_change_depot; loop fetch get_dev_change_depot into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_depot%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 封存‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把封存的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_depot; -- 启封 open get_dev_change_depot_start; loop fetch get_dev_change_depot_start into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_depot_start%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 启封‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把启封的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_depot_start; -- 闲置 open get_dev_change_idle; loop fetch get_dev_change_idle into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_idle%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 闲置‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把闲置的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_idle; -- 闲置启用 open get_dev_change_idle_start; loop fetch get_dev_change_idle_start into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_idle_start%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 闲置启用‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把闲置启用的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_idle_start; -- 停用 open get_dev_change_stop; loop fetch get_dev_change_stop into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_stop%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 停用‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把停用的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_stop; -- 停用启用 open get_dev_change_stop_start; loop fetch get_dev_change_stop_start into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_stop_start%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 停用启用‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把停用启用的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_stop_start; --取得设备报废信息 open get_dev_change_reject; loop fetch get_dev_change_reject into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_reject%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 报废‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把报废的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_reject; ls_change_reason := ‘‘; --取得设备外调信息 open get_dev_change_remove; loop fetch get_dev_change_remove into ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_reason,ldt_change_dte; exit when get_dev_change_remove%notfound; ls_change_instance := to_char(ldt_change_dte,‘yyyy/mm/dd‘)||‘ 外调‘; ls_change_nbr := to_char(ln_kind_cod1_id); --把外调的设备信息放到表(dev_change_total)中 insert into dev_change_total values(ls_year,ln_change_no,ls_change_nbr,ls_dev_no,ls_dev_nam,ls_stand_txt,ls_model_txt,ln_capacity_num,ls_factory_nam,ld_out_dte,ls_change_instance,ls_change_reason); ln_kind_cod1_id := ln_kind_cod1_id + 1; ln_change_no := ln_change_no + 1; end loop; close get_dev_change_remove; ls_change_reason := ‘‘; --根据设备类别1的代码，设置其对应的"序号" if ls_kind_cod1_no = ‘01‘ then ls_change_nbr := ‘一‘; end if; if ls_kind_cod1_no = ‘02‘ then ls_change_nbr := ‘二‘; end if; if ls_kind_cod1_no = ‘03‘ then ls_change_nbr := ‘三‘; end if; if ls_kind_cod1_no = ‘04‘ then ls_change_nbr := ‘四‘; end if; if ls_kind_cod1_no = ‘05‘ then ls_change_nbr := ‘五‘; end if; if ls_kind_cod1_no = ‘06‘ then ls_change_nbr := ‘六‘; end if; ls_dev_no := to_char(ln_change_no - ln_kind_cod1_xlh - 1) || ‘台‘; --把按设备类别1进行的合计数添加到设备变动台帐表(dev_change_total)中 insert into dev_change_total (change_year,change_no,change_nbr,dev_no,dev_nam) values (ls_year,ln_kind_cod1_xlh,ls_change_nbr,ls_dev_no,ls_kind_cod1_nam); end loop; commit; close get_kind_cod1; end; / |
|
|
来自： moonboat > 《database》
