222222222
来源:互联网 发布:西南交大网络远程教育 编辑:程序博客网 时间:2024/06/04 01:08
CREATE OR REPLACE PROCEDURE P_PLA_WF_IMGINV (
v_buId IN PLA_PLAN_INVEST_MAIN.PLAN_INVEST_MAIN_ID%TYPE,
v_oper IN varchar2,
v_coldstate IN varchar2,
v_cnewstate IN varchar2,
v_noldstate IN varchar2,
v_nnewstate IN varchar2
)
IS
--标段年度形象计划 工作流审批 单据状态更改 存储过程
v_ostate varchar2 (200);
v_nstate varchar2 (200);
v_state PLA_PLAN_INVEST_MAIN.status%TYPE;
v_error_code int;
v_result varchar2 (200);
BEGIN
--操作
IF (LOWER (v_oper) = 'trans')
THEN
v_ostate := v_coldstate;
v_nstate := v_cnewstate;
ELSE
v_ostate := v_coldstate;
v_nstate := v_noldstate;
END IF;
--查询状态
SELECT status
INTO v_state
FROM PLA_PLAN_INVEST_MAIN
WHERE PLAN_INVEST_MAIN_ID = v_buId;
-- 如果修改前和待修改后的状态相同则不做动作
IF (v_state = v_nstate)
THEN
RETURN;
END IF;
-- 如果修改前的状态为已审批则不允许再修改状态
IF (v_state = 'C175_2')
THEN
RAISE_APPLICATION_ERROR (
-20999,
'配置错误:修改前的状态为已审批,不允许再修改状态'
);
RETURN;
END IF;
-- 判断条件已通过,更新状态
UPDATE PLA_PLAN_INVEST_MAIN
SET status = v_nstate
WHERE PLAN_INVEST_MAIN_ID = v_buId;
IF (LOWER(v_nstate)='c175_2') THEN --如果是已经通过审核的进度,则要更新主表的审批日期字段。
UPDATE PLA_PLAN_INVEST_MAIN SET APPROVAL_DATE=SYSDATE WHERE PLAN_INVEST_MAIN_ID = v_buId;
END IF;
commit;
--异常处理
EXCEPTION
WHEN OTHERS
THEN
--事务回滚
ROLLBACK;
v_error_code := SQLCODE;
v_result := SUBSTR (SQLERRM, 1, 200);
DBMS_OUTPUT.put_line (
'错误代码为:' || v_error_code || '。错误信息为:' || v_result || '.'
);
END P_PLA_WF_IMGINV;