MERGE INTO SYS_USER_POS PS
USING (SELECT U.USERID, M.ORG_ID, M.POS_ID
FROM SYS_USER U
INNER JOIN MID_SYS_USER_PMP_SYNC M ON U.ACCOUNT = M.ACCOUNT WHERE M.CHECK_STATUS = '0') INTEG
ON (PS.USERID = INTEG.USERID)
WHEN NOT MATCHED THEN
INSERT
(PS.USERPOSID, PS.POSID, PS.USERID, PS.ISPRIMARY, PS.ORGID, PS.ASSID)
VALUES
(UNIQUEIDUTIL_ID.NEXTVAL,
INTEG.POS_ID,
INTEG.USERID,
1,
INTEG.ORG_ID,
0)
WHEN MATCHED THEN
UPDATE SET PS.POSID = INTEG.POS_ID, PS.ORGID = INTEG.ORG_ID;
INSERT INTO SYS_BPM_USE_ROLE
(ID,
ROLEID,
ROLENAME,
USERID,
ACCOUNT,
FULLNAME,
ORGID,
ORGTYPE,
AREAID,
CATEGORYID,
CREATETIME,
BQ_ORGID)
SELECT UNIQUEIDUTIL_ID.NEXTVAL AS ID,
M.USERATTRID AS ROLEID,
M.USERATTRNAME AS ROLENAME,
N.USERID AS USERID,
SU.ACCOUNT AS ACCOUNT,
SU.FULLNAME AS FULLNAME,
N.ORGID AS ORGID,
M.ORGTYPE AS ORGTYPE,
M.AREAID AS AREAID,
M.CATEGORYID AS CATEGORYID,
V_IDATE AS CREATETIME,
(SELECT SO.ORG_ID
FROM SYS_ORG_BPM SO
WHERE SO.ORG_TYPE_X = V_IBQ
AND ROWNUM = 1
START WITH SO.ORG_ID = O.ORG_ID
CONNECT BY SO.ORG_ID = PRIOR SO.ORG_SUPID) AS BQ_ORGID
FROM MID_SYS_BPM_USE_ROLE M
LEFT JOIN (SELECT UR.ROLEID AS ROLEID,
UR.USERID AS USERID,
DECODE(URO.ORGID, NULL, UR.ORGID, URO.ORGID) ORGID
FROM SYS_USER_ROLE UR
LEFT JOIN SYS_USERROLE_ORG URO ON URO.USERROLEID =
UR.USERROLEID) N ON M.USERATTRID =
N.ROLEID