|
CREATE DEFINER=`mysqladmin`@`%` PROCEDURE `pr_wf_excel_init`(
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? in pa_cnc varchar(100), ??
?? ??? ??? ? ? ? ? ? ? ? ? ? ? ? ? ? OUT OSA_RETURN_CODE VARCHAR(5), ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
?? ??? ??? ? ? ? ? ? ? ? ? ? ? ? ? ? OUT OSA_SQLERRM VARCHAR(500)
)
__END: BEGIN?
?? ??? ??? ?declare ls_process_ins_id varchar(64);
?? ??? ??? ?declare ls_process_defind_id varchar(64);
?? ??? ??? ?declare ls_process_initfile_id varchar(64);
?? ??? ??? ?declare ls_process_initfile_path varchar(64);
?? ??? ??? ?declare ls_process_initfile_type varchar(128);
?? ??? ??? ?declare ls_process_initfile_type_copy varchar(128);
?? ??? ??? ?declare ls_process_initnode varchar(128);
?? ??? ??? ?declare ls_count_pqct int;
?? ??? ??? ?declare ls_uuid varchar(64);
?? ??? ??? ?
?? ??? ??? ?DECLARE done INT DEFAULT FALSE; ? ?-- 游标循环不能缺少的语句
?? ??? ??? ?
?? ??? ??? ?DECLARE Cursor_File CURSOR FOR?
?? ??? ??? ?(
?? ??? ??? ??? ??? ??? ?-- t_ebom_cncroot-- t_ebom_cncfiles-- t_ebom_outfile
?? ??? ??? ??? ??? ??? ?-- t_mbom_cnctec-- t_mbom_resfile-- 查询 填充文件id 文件路径?? ?
?? ??? ??? ??? ??? ?SELECT DISTINCT fileid,ftype,typeid FROM
?? ??? ??? ??? ??? ??? ??? ?(
?? ??? ??? ??? ??? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ?select a.fileid,'规格更改书' as ftype,'GGGG' as typeid ? ? ? ? ? ??
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?from t_ebom_cncroot a ?where a.eecnc=pa_cnc ?and ?cncbiltype='GGGG' ? ? ? ? ? ? ? ? ? ? ? ? ??
?? ??? ??? ??? ??? ??? ??? ??? ??? ?union all?
?? ??? ??? ??? ??? ??? ??? ??? ??? ?select a.fileid,typedesc,tab.typeid from t_ebom_outfile a ? ? ? ? ? ??
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?join t_sys_classdital tab ?on a.fitype=tab.typeid ? ? ? ? ? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?where a.eecnc=pa_cnc?
?? ??? ??? ??? ??? ??? ??? ??? ??? ?union all?
?? ??? ??? ??? ??? ??? ??? ??? ??? ?select a.fileid,typedesc,tab.typeid from t_mbom_resfile a ? ? ? ? ? ??
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?join t_sys_classdital tab ?on a.filetype=tab.typeid ? ? ? ? ? ? ? ? ? ? ??
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?where a.bgncnc=pa_cnc ?
?? ??? ??? ??? ??? ??? ??? ?)t
? ? ? ?);?
?? ??? ??? ??
?? ??? ??? ?DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true
?? ??? ??? ?
? ? ? ?/*---------- ? ? ?异常处理机制 ?模板 ? ? ----------*/?
? ? ? ?-- DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION ?如果有了游标，就不能有 NOT FOUND
?? ? ?DECLARE EXIT HANDLER FOR SQLWARNING,SQLEXCEPTION
?? ? ?BEGIN
?? ??? ? ?GET DIAGNOSTICS CONDITION 1
?? ??? ? ?OSA_RETURN_CODE = RETURNED_SQLSTATE, OSA_SQLERRM = MESSAGE_TEXT;?
?? ? ?END; ?
? ? ? set OSA_RETURN_CODE ="OK";
? ? ? set OSA_SQLERRM="noErr";?
?? ??? ??? ?
-- ?? ??? ??? ?设置同一批次的uuid
?? ??? ??? ?set ls_uuid=REPLACE(UUID(),'-','');
?? ??? ??? ?
?? ??? ??? ?-- 获取最新+有效的 工序
? ? ? ? set done = false;
? ? ? ? open Cursor_File;
?? ??? ??? ??? ??? ??? ?FileLoop: loop fetch Cursor_File into ls_process_initfile_id,ls_process_initfile_path,ls_process_initfile_type;
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?if done then
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?leave FileLoop;
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?end if;?? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? ?if instr(ls_process_initfile_type,'GGGG')>0 then ?set ls_process_initfile_type_copy = 'GGGG'; -- if 条件 =
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?elseif instr(ls_process_initfile_type,'PQCT')>0 then ?set ls_process_initfile_type_copy = ls_process_initfile_type; -- elseif关键字
-- ?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?elseif instr(ls_process_initfile_type,'PQCT')>0 then ?set ls_process_initfile_type = ls_process_initfile_type; -- elseif关键字
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?else set ls_process_initfile_type_copy = 'EBOM_OUTFILE'; ? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?end if;
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?-- 审批人信息
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?insert into t_approve_init_temp
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? with tab_act_info as(?? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? SELECT?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? a.activity_id_,a.START_TIME_ as start_date,a.END_TIME_ ?as end_date,u.user_name,u.user_id,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? ls_process_initfile_id as file_id,f_getfilepath(ls_process_initfile_id) as file_path
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?FROM act_ext_hi_taskinst a,t_sys_user u?? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?WHERE a.ASSIGNEE_=u.user_id ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) ?FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.END_TIME_ in?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?SELECT max(a.END_TIME_)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?FROM act_ext_hi_taskinst a ?,t_sys_user u
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?WHERE a.ASSIGNEE_=u.user_id ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) ?FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.EXEC_RESULT_='OK'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.ACTIVITY_ID_ <> 'startEvent'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?GROUP BY a.activity_id_
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?ORDER BY a.seq_
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.ACTIVITY_ID_ <> 'startEvent'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?ORDER BY a.seq_
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?)?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?select null,ls_uuid,s.activity_id_,s.start_date as start_date,s.end_date as end_date,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?s.user_name,s.user_id,s.file_id,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?s.file_path,k.app_row,k.app_cell,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?f_getusersigpath(s.user_id) as sig_path,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?-- ?? ?case f_getusersigpath(s.user_id) when INSERT(f_getusersigpath(s.user_id),'/') then 'Y' end as ib_sign,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?'sign_value' as insert_flag
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?from ?tab_act_info s join t_approve_init k on s.activity_id_ = k.app_wfnode
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?WHERE INSTR (ls_process_initfile_type_copy,k.app_type)>0
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?union all
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?-- 审批时间信息
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?select null,ls_uuid,l.app_wfnode,?? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.start_date from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as start_date,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.end_date from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as end_date,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.user_name from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as user_name,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.user_id from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as user_id,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.file_id from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as file_id,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(select k.file_path from tab_act_info k where left(l.app_wfnode,length(l.app_wfnode)-5)=k.activity_id_ ) as file_path,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?l.app_row,l.app_cell,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?'time' as sig_path,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?'insert_time' as insert_flag
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?from ?t_approve_init l
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? where ?l.app_wfnode not in (
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?select ?s.activity_id_
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?from ?tab_act_info s?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?join t_approve_init k
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?on s.activity_id_ = k.app_wfnode
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? )
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? and instr(l.app_wfnode,
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ? (SELECT left(a.activity_id_,10)?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?FROM act_ext_hi_taskinst a,t_sys_user u?? ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?WHERE a.ASSIGNEE_=u.user_id ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) ?FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.END_TIME_ in?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?(
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?SELECT min(a.END_TIME_)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?FROM act_ext_hi_taskinst a ?,t_sys_user u
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?WHERE a.ASSIGNEE_=u.user_id ?
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.PROC_INST_ID_= (SELECT max(PROC_INST_ID_) ?FROM act_ext_ru_procinst WHERE BUSINESS_KEY_= pa_cnc)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.EXEC_RESULT_='OK'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.ACTIVITY_ID_ <> 'startEvent'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?and a.ACTIVITY_ID_ <> 'startEvent'
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?ORDER BY a.seq_
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?)
?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?)>0;
?? ??? ??? ??? ??? ??? ?end loop FileLoop;
? ? ? ? close Cursor_File;
?? ??? ??? ??? ?
?? ??? ??? ??? ?select * from t_approve_init_temp l where l.uuid = ls_uuid;
?? ??? ??? ??? ?
?? ??? ??? ??? ?
END
|