create or replace procedure WF_USERAUD(
P_USERID IN VARCHAR2,
P_ROLES IN VARCHAR2,
P_GUANLBMS IN VARCHAR2,
P_ISPASSED INT
) AS
/*------------------------------------------------------------------------------
功能描述:用户审核存储过程.
参数:
P_USERID 用户ID(昵称)
P_ROLES 所属职位(格式 角色id1,角色id2,角色id3 )
P_GUANLBMS 所管理的部门(格式 部门id1,部门id2,部门id3,
P_ISPASSED (只能传 1,2 其他值都非法 1:审核通过,2.审核驳回)
v_count int;
begin
IF P_USERID IS NULL THEN
RAISE_APPLICATION_ERROR(-20004, '非法的参数P_USERID值不允许为空值['||P_USERID||']');
END IF;
IF P_ISPASSED IS NULL THEN
RAISE_APPLICATION_ERROR(-20005, '非法的参数P_ISPASSED值['||P_USERID||']');
END IF;
IF P_ISPASSED2 THEN
RAISE_APPLICATION_ERROR(-20001, '非法的参数ISPASSED值['||P_ISPASSED||'] 只允许1,2');
END IF;
--如果驳回,则设置用户被驳回,并退出
IF P_ISPASSED=2 THEN
--直接删除,2015-9-9,jason
DELETE FROM CORE_USER WHERE RWID=P_USERID;
DELETE FROM CORE_WF_DONE WHERE NVL(AUDITRLT,0)=0 AND CREATERID=P_USERID AND
DEFID='F67169924BE5498FBC337C83C84FAAC7';
COMMIT;
RETURN;
--UPDATE CORE_USER SET ISPASSED=P_ISPASSED WHERE RWID=P_USERID;
--UPDATE CORE_WF_DONE SET AUDITRLT=P_ISPASSED WHERE NVL(AUDITRLT,0)=0 AND CREATERID=P_USERID AND
DEFID='F67169924BE5498FBC337C83C84FAAC7';
--return;
END IF;
v_count:=0;
SELECT COUNT(*) INTO v_count FROM
table(CORE_SPLIT(P_ROLES,',')) A
where not exists(
SELECT 1 FROM CORE_ROLE B
where B.RWID = A.column_value);
if(v_count>0) then
RAISE_APPLICATION_ERROR(-20003, '角色(所属职责)['||P_ROLES||']存在非法的角色id');
end if;
--角色\管理的部门被修改了 删除掉原来的
DELETE CORE_USERDEPT WHERE USERID=P_USERID;
--添加用户部门角色关系表CORE_USERDEPT
INSERT INTO CORE_USERDEPT(Userid, Roleid, Deptid)
SELECT distinct P_USERID,r.COLUMN_VALUE, d.COLUMN_VALUE FROM
table(CORE_SPLIT(P_ROLES,',')) r, table(CORE_SPLIT(P_GUANLBMS,',')) d;
--更新用户表
UPDATE CORE_USER SET ISPASSED=P_ISPASSED WHERE RWID=P_USERID;
UPDATE CORE_WF_DONE SET AUDITRLT=P_ISPASSED WHERE NVL(AUDITRLT,0)=0 AND CREATERID=P_USERID AND
DEFID='F67169924BE5498FBC337C83C84FAAC7';
COMMIT;
end WF_USERAUD;
/