CREATE OR REPLACE PROCEDURE PRO_GET_PLAN
IS
/******************************************************************************
NAME: PRO_GET_PLAN
PURPOSE:
NOTES: 从配网生产系统获取停电计划生成检修单
Modified
******************************************************************************/
msg varchar2(1000);
BEGIN
insert into pd_td_outage_apply_bill
(oid,
code,
APPLY_UNIT,
APPLY_UNIT_ID,
transformer_id,
START_TIME,
END_TIME,
POWER_BOUND,
ITEM_AFFILLIATED,
ITEM_AFFILLIATED_ID,
EXECUTE_MEANS,
WRITE_NAME,
FZRMOBILE_PHONE,
OUTAGE_TYPE,
OUTAGE_TYPEID,
WORK_STAR,
WORK_END,
WORK_PROPERTY,
CONSTRUCT_UNIT,
FEW,
WORK_CONTENT,
TASK_STATE,
STATUS,
APPLY_GROUP_NAME,
APPLY_GROUP,
CONTENT_USERID,
CONTENT_NAME,
MOBILE_PHONE,
transformer_name)
select oid,
FC_getoutageapllycode(START_TIME) code,--调用配网生产库生成编号函数,避免数据重复
dept_name,
APPLY_UNIT_ID,
transformer_id,
START_TIME,
END_TIME,
POWER_BOUND,
ITEM_AFFILLIATED,
ITEM_AFFILLIATED_ID,
EXECUTE_MEANS,
WRITE_NAME,
FZRMOBILE_PHONE,
OUTAGE_TYPE,
OUTAGE_TYPEID,
WORK_STAR,
WORK_END,
WORK_PROPERTY,
CONSTRUCT_UNIT,
FEW,
WORK_CONTENT,
TASK_STATE,
STATUS,
APPLY_GROUP_NAME,
APPLY_GROUP,
CONTENT_USERID,
CONTENT_NAME,
MOBILE_PHONE,
description from
(
select
distinct
t1.oid,
t2.dept_name,
to_number(t1.apply_unit) APPLY_UNIT_ID,--配网生产数据库中这个字段才是所属分局编号
t1.transformer_id,
t1.START_TIME,
t1.END_TIME,
t1.POWER_BOUND,
t1.ITEM_AFFILLIATED,
t1.ITEM_AFFILLIATED_ID,
t1.EXECUTE_MEANS,
t1.WRITE_NAME,
t1.FZRMOBILE_PHONE,
t1.OUTAGE_TYPE,
t1.OUTAGE_TYPEID,
t1.WORK_STAR,
t1.WORK_END,
t1.WORK_PROPERTY,
t1.CONSTRUCT_UNIT,
t1.FEW,
t1.WORK_CONTENT,
t1.TASK_STATE,
t1.STATUS,
t1.APPLY_GROUP_NAME,
t1.APPLY_GROUP,
t1.CONTENT_USERID,
t1.CONTENT_NAME,
t1.MOBILE_PHONE,
t3.description from PW_MONTH_PC_PLAN_CHILD_ATP@pwsc_dblink t1,pw_core_dept@pwsc_dblink t2,
v_kxmc_tree t3
where not exists(select 1 from PD_TD_OUTAGE_APPLY_BILL
where t1.oid=PD_TD_OUTAGE_APPLY_BILL.oid)
and t1.apply_unit in ('107')--已经上线的分局
and to_number(t1.apply_unit)=t2.dept_id
and to_char(t1.start_time,'yyyyMM')>='201211'
and (case
when instr(t1.transformer_id,',')>0
then substr(t1.transformer_id,0,instr(t1.transformer_id,',')-1)
else t1.transformer_id end)=t3.objectsid(+)--配网生产系统中的停电计划变电站可以为空,也可以是多个
and t1.status in ('2I','5E')--此状态表示停电计划已经处理完成 '2I'表示已经发布还未处理完成的 '5E'表示变更发布的
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
msg:='获取配网生产系统停电计划生成检修单存储过程出错:'||sqlcode||sqlerrm;
INSERT INTO RH_SYS_LOG VALUES(SYSDATE,'1050',msg);
COMMIT;
END PRO_GET_PLAN;