-----存储过程------CREATEORREPLACEPROCEDURE SP_START_WORKFLOW_STATUS
(
P_PROC_CODE IN VARCHAR2 -- 主实例代码, P_MAIN_ID IN NUMBER -- 事件表id, P_BIZ_NO IN VARCHAR2 -- 事件表单号, P_EMPID IN VARCHAR2 -- 流程审批人, P_STAGE IN VARCHAR2 -- 流程节点, O_RESULT OUT VARCHAR2
, O_NEXT_NODE_ID OUT NVARCHAR2
)AS
V_PROCESS_DEF_MAIN_ID NUMBER(18);
V_PROCESS_INSTANCE_SEQID NUMBER(18);
V_PROCESS_INSTANCE_HIS_SEQID NUMBER(18);BEGINSELECT ID INTO V_PROCESS_DEF_MAIN_ID FROM PROCESS_DEF_MAIN WHERE PROCESS_CODE = P_PROC_CODE;IF V_PROCESS_DEF_MAIN_ID ISNOTNULLTHENINSERTINTO PROCESS_INSTANCE
(PROCESS_DEF_MAIN_ID,
BIZ_ID,
BIZ_NO,
PROCESS_CODE,
NODE_ID,
ACTIVITY_ID,
COMMENTS,
VERSION,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(V_PROCESS_DEF_MAIN_ID,
P_MAIN_ID,
P_BIZ_NO,
P_PROC_CODE,
P_STAGE,'ACT_CREATION',NULL,0,1,
SYSDATE,'SYS',
SYSDATE,'SYS')RETURN ID INTO V_PROCESS_INSTANCE_SEQID;INSERTINTO PROCESS_USER
(PROCESS_INSTANCE_ID,
USER_ID,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(V_PROCESS_INSTANCE_SEQID,
P_EMPID,-- P_EMPID1,
SYSDATE,'SYS',
SYSDATE,'SYS');INSERTINTO PROCESS_INSTANCE_HIS
(PROCESS_DEF_MAIN_ID,
PROCESS_INSTANCE_ID,
BIZ_ID,
BIZ_NO,
PROCESS_CODE,
NODE_ID,
ACTIVITY_ID,
COMMENTS,
VERSION,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(V_PROCESS_DEF_MAIN_ID,
V_PROCESS_INSTANCE_SEQID,
P_MAIN_ID,
P_BIZ_NO,
P_PROC_CODE,
P_STAGE,'ACT_CREATION',NULL,0,1,
SYSDATE,'SYS',
SYSDATE,'SYS')RETURN ID INTO V_PROCESS_INSTANCE_HIS_SEQID;INSERTINTO PROCESS_USER_HIS
(PROCESS_INSTANCE_HIS_ID,
USER_ID,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(V_PROCESS_INSTANCE_HIS_SEQID,
P_EMPID,1,
SYSDATE,'SYS',
SYSDATE,'SYS');
O_RESULT :='OK';
O_NEXT_NODE_ID :='0';ENDIF;COMMIT;
EXCEPTION
WHEN OTHERS THEN
O_RESULT :='FAILED';
O_NEXT_NODE_ID :=null;
dbms_output.put_line(SQLERRM);ROLLBACK;END SP_START_WORKFLOW_STATUS;createorreplaceprocedure SP_UPDATE_WORKFLOW_STATUS(P_PROC_CODE IN NVARCHAR2,-- process code 主流程code
P_CUR_NODE_ID IN NVARCHAR2,-- current node id 单前节点
P_CUR_ACTIVITY_ID IN NVARCHAR2,-- current activity id 单前动作
P_CONDITION IN NVARCHAR2:=0,-- activity condition 流程版本
P_MAIN_ID IN NUMBER,--业务 id
P_BIZ_NO IN NVARCHAR2,--业务单号
P_EMPID IN NVARCHAR2,-- current emp id(UPDATE_BY) 当前user
P_TO_EMP_ID IN NVARCHAR2,-- approve emp id 下一个user
P_SAVE_IN_HISTORY NVARCHAR2 :='Y',--whether need current step move to history
P_COMMENTS IN NVARCHAR2:=NULL,-- COMMENTS
O_RESULT OUT NVARCHAR2,
O_NEXT_NODE_ID OUT NVARCHAR2)is
V_NEXT_NODE_ID NVARCHAR2(50);
V_PROCESS_INSTANCE_HIS_SEQID NUMBER(18);
V_HIS_SEQ_NO NVARCHAR2(20) := TO_CHAR(SYSDATE,'YYYYMMDDHH24MMSS');BEGINSELECT B.NEXT_NODE_ID INTO V_NEXT_NODE_ID
FROM PROCESS_DEF_MAIN A
INNERJOIN PROCESS_DEF_NODE_ACTIVITY B ON A.ID = B.PROCESS_DEF_MAIN_ID
WHERE A.DELETED =0AND A.PROCESS_CODE = P_PROC_CODE
AND B.CURRENT_NODE_ID = P_CUR_NODE_ID
AND B.ACTIVITY_ID = P_CUR_ACTIVITY_ID
AND B.CONDITION = P_CONDITION;
dbms_output.put_line(V_NEXT_NODE_ID);--move process instance to historyIF P_SAVE_IN_HISTORY ='Y'AND V_NEXT_NODE_ID ISNOTNULLTHEN-- SHOULD BE ONLY 1 RECORD FOR EACH BIZ_ID AND PROCESS_CODE-- UPDATE ACTIVITY TO CURRENT ACTIVITYUPDATE PROCESS_INSTANCE SET ACTIVITY_ID=P_CUR_ACTIVITY_ID,COMMENTS=P_COMMENTS,UPDATED_AT=SYSDATE,UPDATED_BY=P_EMPID WHERE BIZ_ID = P_MAIN_ID AND PROCESS_CODE = P_PROC_CODE;FOR PROC_INS_RECORD IN(SELECT*FROM PROCESS_INSTANCE P
WHERE P.BIZ_ID = P_MAIN_ID
AND P.PROCESS_CODE = P_PROC_CODE)LOOPINSERTINTO PROCESS_INSTANCE_HIS
(PROCESS_INSTANCE_ID,
PROCESS_DEF_MAIN_ID,
BIZ_ID,
BIZ_NO,
PROCESS_CODE,
NODE_ID,
ACTIVITY_ID,
COMMENTS,
VERSION,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(PROC_INS_RECORD.ID,
PROC_INS_RECORD.PROCESS_DEF_MAIN_ID,
PROC_INS_RECORD.BIZ_ID,
PROC_INS_RECORD.BIZ_NO,
PROC_INS_RECORD.PROCESS_CODE,
PROC_INS_RECORD.NODE_ID,
PROC_INS_RECORD.ACTIVITY_ID,
PROC_INS_RECORD.COMMENTS,
PROC_INS_RECORD.VERSION,
PROC_INS_RECORD.ACTIVE,
SYSDATE,'SYS',
SYSDATE,'SYS')RETURN ID INTO V_PROCESS_INSTANCE_HIS_SEQID;FOR PROC_USER_INS_RECORD IN(SELECT*FROM PROCESS_USER U
WHERE U.PROCESS_INSTANCE_ID =
PROC_INS_RECORD.ID AND U.USER_ID=P_EMPID)LOOPINSERTINTO PROCESS_USER_HIS
(PROCESS_INSTANCE_HIS_ID,
USER_ID,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(V_PROCESS_INSTANCE_HIS_SEQID,
PROC_USER_INS_RECORD.USER_ID,
PROC_USER_INS_RECORD.ACTIVE,
SYSDATE,'SYS',
SYSDATE,'SYS');ENDLOOP;-- update current processUPDATE PROCESS_INSTANCE SET NODE_ID = V_NEXT_NODE_ID,UPDATED_AT=SYSDATE,UPDATED_BY=P_EMPID WHERE ID = PROC_INS_RECORD.ID;-- ****delete old user****DELETEFROM PROCESS_USER WHERE PROCESS_INSTANCE_ID=PROC_INS_RECORD.ID;-- insert new user for PROCESS_USER (split P_TO_EMP_ID by ',')FOR TO_USER_RECORD IN(SELECTDISTINCT TRIM(REGEXP_SUBSTR(T.NAME,'[^,]+',1, LEVELS.COLUMN_VALUE))AS NAME
FROM(SELECT P_TO_EMP_ID AS NAME FROM DUAL) T,TABLE(CAST(MULTISET
(SELECTLEVELFROM DUAL
CONNECTBYLEVEL<=
LENGTH(REGEXP_REPLACE(T.NAME,'[^,]+'))+1)AS
SYS.ODCINUMBERLIST)) LEVELS
ORDERBY NAME
)LOOPINSERTINTO PROCESS_USER
(PROCESS_INSTANCE_ID,
USER_ID,
ACTIVE,
CREATE_TIME,
CREATED_BY,
UPDATED_AT,
UPDATED_BY)VALUES(PROC_INS_RECORD.ID,
TO_USER_RECORD.NAME,1,
SYSDATE,'SYS',
SYSDATE,'SYS');ENDLOOP;ENDLOOP;ELSE-- NO NEED MOVE TO HISTORY,ONLY UPDATE PROCESS_INSTANCE STATUSIF V_NEXT_NODE_ID ISNOTNULLTHENUPDATE PROCESS_INSTANCE SET NODE_ID = V_NEXT_NODE_ID,COMMENTS=P_COMMENTS,ACTIVITY_ID=P_CUR_ACTIVITY_ID,UPDATED_AT=SYSDATE,UPDATED_BY=P_EMPID WHERE BIZ_ID = P_MAIN_ID AND PROCESS_CODE = P_PROC_CODE;ENDIF;ENDIF;IF V_NEXT_NODE_ID ISNULLTHEN
O_NEXT_NODE_ID :=NULL;
O_RESULT :='FAILED';ELSE
O_NEXT_NODE_ID := V_NEXT_NODE_ID;
O_RESULT :='OK';ENDIF;
EXCEPTION
WHEN OTHERS THEN
O_NEXT_NODE_ID :=NULL;
O_RESULT :='FAILED';
dbms_output.put_line(SQLERRM);end SP_UPDATE_WORKFLOW_STATUS;-----存储过程------
927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
