包头：
CREATE OR REPLACE PACKAGE AAS.MSL_GLOBAL_AGENT
AS
PROCEDURE READY_DATA;
PROCEDURE EXECUTE_DATA;
END MSL_GLOBAL_AGENT;
/
包体：
CREATE OR REPLACE PACKAGE BODY AAS.MSL_GLOBAL_AGENT
AS
PROCEDURE READY_DATA
IS
NOW DATE DEFAULT TRUNC (SYSDATE);
/******************************************************************************
NAME: READY_DATA
PURPOSE: 抓取各個主管的最大的Unit進行代理設置及取消.
FLAG:
0.ERP代理已設定,這里要輪環嘗試去設定OA代理
4.ERP代理已取消,這裡的程序嘗試去取消OA的代理
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 2011/3/7 ABYLIVER.ZHANG 1. CREATED THIS PROCEDURE.
******************************************************************************/
BEGIN
--無效的去掉
UPDATE MSL_AGENT
SET FLAG = 1
WHERE ( (IDATE_FM > NOW AND IDATE_TO < NOW) OR IDATE_FM > IDATE_TO)
AND FLAG IN (0, 4);
--更新部門的level,level越小,則部門的層級越大;
--如果某主管同時兼任部,處,課主管并全部設置了代理人,則只抓部級代理人.
UPDATE MSL_AGENT A
SET IS_MAX_UNIT =
NVL ( (SELECT UNITLEVEL
FROM UNIT@AGENT_BPM
WHERE UNITID = A.UNITID),
100),
COMPANY =
DECODE (INSTR (UNITID, '-'),
0, UNITID,
SUBSTR (UNITID, 1, INSTR (UNITID, '-') - 1))
WHERE FLAG = 0;
UPDATE MSL_AGENT A
SET IS_MAX_UNIT =
NVL ( (SELECT UNITLEVEL
FROM UNIT@AGENT_BPM
WHERE UNITID = A.UNITID),
100),
COMPANY =
DECODE (INSTR (UNITID, '-'),
0, UNITID,
SUBSTR (UNITID, 1, INSTR (UNITID, '-') - 1))
WHERE FLAG = 4;
--根據以上更新得到最小的level部門
UPDATE MSL_AGENT A
SET IS_MAX_UNIT = 1
WHERE ROWID =
(SELECT ROWID
FROM MSL_AGENT
WHERE GULID = A.GULID AND FLAG = 0
AND IS_MAX_UNIT =
(SELECT MIN (IS_MAX_UNIT)
FROM MSL_AGENT
WHERE GULID = A.GULID AND FLAG = 0)
AND ROWNUM = 1);
UPDATE MSL_AGENT A
SET IS_MAX_UNIT = 1
WHERE ROWID =
(SELECT ROWID
FROM MSL_AGENT
WHERE GULID = A.GULID AND FLAG = 4
AND IS_MAX_UNIT =
(SELECT MIN (IS_MAX_UNIT)
FROM MSL_AGENT
WHERE GULID = A.GULID AND FLAG = 4)
AND ROWNUM = 1);
COMMIT;
END READY_DATA;
PROCEDURE EXECUTE_DATA
IS
TEMP VARCHAR2 (200);
UPDATEDCOUNT NUMBER;
NOW DATE DEFAULT TRUNC (SYSDATE);
--兩個cursor內容不能合併在一起,,需要先刪除無效的,不然會造成 正確的代理資料不能插入.
CURSOR V_ABORT
IS
SELECT A.ROWID AS ROW_ID,
S.SYS_NAME,
B.EMPNO,
B.CHINESE_NAME AS EMPNAME,
B.GUID || '' AS GUID,
B.ENGLISH_NAME AS GULID,
B.COMPANY || '-' || B.ONBOARD_DEPTNO AS UNITID,
E.EMPNO AS AGENT_EMPNO,
E.CHINESE_NAME AS AGENT_EMPNAME,
E.GUID || '' AS AGENT_GUID,
E.ENGLISH_NAME AS AGENT_GULID,
E.COMPANY || '-' || E.ONBOARD_DEPTNO AS AGENT_UNITID,
A.IDATE_FM,
A.IDATE_TO,
9999 AS CREATE_BY,
A.FLAG
FROM MSL_AGENT A,
AAS_GUID_EMPLOYEE B,
AAS_GUID_EMPLOYEE E,
MSL_AGENT_SYSTEM S
WHERE A.FLAG = 4
AND A.IS_MAX_UNIT = 1
AND S.FLAG=1
AND IDATE_FM <= NOW
AND IDATE_TO >= NOW
AND B.ENGLISH_NAME = A.GULID
AND B.COMPANY = A.COMPANY
AND E.ENGLISH_NAME = A.AGENT_GULID
AND E.COMPANY = A.COMPANY;
CURSOR V_AGENTS
IS
SELECT A.ROWID AS ROW_ID,
S.SYS_NAME,
B.EMPNO,
B.CHINESE_NAME AS EMPNAME,
B.GUID || '' AS GUID,
B.ENGLISH_NAME AS GULID,
B.COMPANY || '-' || B.ONBOARD_DEPTNO AS UNITID,
E.EMPNO AS AGENT_EMPNO,
E.CHINESE_NAME AS AGENT_EMPNAME,
E.GUID || '' AS AGENT_GUID,
E.ENGLISH_NAME AS AGENT_GULID,
E.COMPANY || '-' || E.ONBOARD_DEPTNO AS AGENT_UNITID,
A.IDATE_FM,
A.IDATE_TO,
9999 AS CREATE_BY,
A.FLAG
FROM MSL_AGENT A,
AAS_GUID_EMPLOYEE B,
AAS_GUID_EMPLOYEE E,
MSL_AGENT_SYSTEM S
WHERE A.FLAG = 0
AND A.IS_MAX_UNIT = 1
AND S.FLAG=1
AND IDATE_FM <= NOW
AND IDATE_TO >= NOW
AND B.ENGLISH_NAME = A.GULID
AND B.COMPANY = A.COMPANY
AND E.ENGLISH_NAME = A.AGENT_GULID
AND E.COMPANY = A.COMPANY;
/******************************************************************************
NAME: MSL_AGENT_PROC
PURPOSE: MSL代理設置(和AAS無關):
1. 如果存在有效代理,則等待有效代理的失效.
2. 如果沒有,則先進行 設定有全局代理的代理.
3. 檢查系統還沒有設置代理,則抓取最大的Unit進行代理設置.
FLAG:
0.ERP代理已設定,這里要輪環嘗試去設定OA代理
1.代理期間無效,這里把狀態壓到1
2.代理期間無效,ERP代理已取消;OA不需要任何處理
4.ERP代理已取消,這裡的程序嘗試去取消OA的代理
REVISIONS:
VER DATE AUTHOR DESCRIPTION
--------- ---------- --------------- ------------------------------------
1.0 2011/3/7 ABYLIVER.ZHANG 1. CREATED THIS PROCEDURE.
RFQ , RFQ SYSTEM
AAS , 權限申請系統
ESPS, 員工建議提案系統
SCSJ, 治具管控系統
CIP,CIP SYSTEM
ECREDIT,E-CREDIT
ELEGAL,MITAC E-LEGAL 法務電子平台
EPATENT, MITAC E-PATENT 專利電子平台
SERVICECENTER,GLOBAL SERVICE CENTER
GATECREDIT, 順達出厂證
UNITOUT,費正常進出口報關系統
******************************************************************************/
BEGIN
--刪除代理
FOR CA IN V_ABORT
LOOP
BEGIN
IF CA.SYS_NAME = 'RFQ'
THEN
UPDATE RFQ.RFQ_AGENT
SET VALID = 0
WHERE VALID = 1
AND AGENT =
CA.AGENT_GULID || '(' || CA.AGENT_EMPNAME || ')'
AND GULID = CA.GULID
AND UPDATEBY = CA.CREATE_BY || '';
END IF;
IF CA.SYS_NAME = 'AAS'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'AAS')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'ESPS'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'ESS')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'SCSJ'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'SCSJ')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'CIP'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'CIP')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'ECREDIT'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'CreditCheck')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'EPATENT'
THEN
DELETE AGENT@AGENT_BPM.US.ORACLE.COM
WHERE PROCESS IN
(SELECT DEFINITIONNAME
FROM PROCESSDEF@AGENT_BPM.US.ORACLE.COM
WHERE DEFINITIONSTATUS = 'A'
AND DEFINITIONDESCRIPTION = 'EPATENT')
AND USERID = CA.GUID
AND AGENTUSERID = CA.AGENT_GUID;
END IF;
IF CA.SYS_NAME = 'SERVICECENTER'
THEN
/*UPDATE FACTORY.SERV_AGENT
SET FLAG = 'N'
WHERE EMPNO = CA.EMPNO
AND AGENT = CA.AGENT_EMPNO
AND C_GROUP_ID = 1
AND FLAG = 'Y';*/
UPDATE FACTORY.SERV_DEPTAGENT
SET FLAG = 'N', D_TIME = SYSDATE, D_PERSON = CA.EMPNO
WHERE C_PERSON = CA.EMPNO
AND AGENT = CA.AGENT_EMPNO
AND FLAG = 'Y';
END IF;
IF CA.SYS_NAME = 'GATECREDIT'
THEN
UPDATE FACTORY.E_AGENT
SET FLAG = 'N'
WHERE EMPNO = CA.EMPNO
AND AGENT = CA.AGENT_EMPNO
AND C_GROUP_ID IN (5, 20)
AND FLAG = 'Y';
END IF;
IF CA.SYS_NAME = 'UNITOUT'
THEN
UPDATE FACTORY.UNINOUT_AGENT
SET FLAG = 'N'
WHERE EMPNO = CA.EMPNO
AND AGENT = CA.AGENT_EMPNO
AND FLAG = 'Y';
END IF;
IF CA.SYS_NAME = 'ELEGAL'
THEN
UPDATE LG_AGENT@AGENT_ELEGAL
SET STATUS = 0
WHERE TYPE = 'AGENT'
AND STATUS = 1
AND USERID = CA.GUID
AND AG_USERID = CA.AGENT_GUID;
END IF;
EXCEPTION
WHEN OTHERS
THEN
UPDATE MSL_AGENT
SET REMARK =
SUBSTR (REMARK, 1, 3800)
|| CA.SYS_NAME
|| ' - '
|| CA.FLAG
|| ' -ERROR '
|| SYSDATE
|| CHR (13)
WHERE ROWID = CA.ROW_ID;
END;
COMMIT;
END LOOP;
FOR CA IN V_AGENTS
LOOP
BEGIN
IF CA.SYS_NAME = 'RFQ'
THEN
INSERT INTO RFQ.RFQ_AGENT (ID,
OU,
AGENT,
STARTDATE,
ENDDATE,
UPDATEBY,
VALID,
LASTUPDATETIME,
GULID,
AGENTENAME,
OU_ID)
(SELECT RFQ.SEQ_RFQ.NEXTVAL,
O.OU,
CA.AGENT_GULID || '(' || CA.AGENT_EMPNAME || ')',
CA.IDATE_FM,
CA.IDATE_TO,
CA.CREATE_BY,
1,
SYSDATE,
CA.GULID,
CA.AGENT_EMPNAME,
ID
FROM RFQ.RFQ_OU O
WHERE VALID = 1
AND NOT EXISTS
(SELECT OU
FROM RFQ.RFQ_AGENT
WHERE OU = O.OU
AND GULID = CA.GULID
AND STARTDATE <= NOW + 1
AND ENDDATE >= NOW
AND VALID = 1));
END IF;
IF CA.SYS_NAME = 'AAS'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'AAS'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'ESPS'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'ESS'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'SCSJ'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'SCSJ'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'CIP'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'CIP'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'ECREDIT'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'CreditCheck'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'EPATENT'
THEN
INSERT INTO AGENT@AGENT_BPM.US.ORACLE.COM (
USERID,
ROLEID,
PROCESS,
AGENTUSERID,
STARTTIME,
ENDTIME,
NOTIFYAGENTUSERWHENCREATE,
NOTIFYAGENTEDUSERWHENCREATE)
(SELECT BOSS.USERID,
'',
PD.DEFINITIONNAME,
AGT.USERID,
CA.IDATE_FM,
CA.IDATE_TO,
'Y',
'Y'
FROM USERTABLE@AGENT_BPM.US.ORACLE.COM BOSS,
USERTABLE@AGENT_BPM.US.ORACLE.COM AGT,
PROCESSDEF@AGENT_BPM.US.ORACLE.COM PD
WHERE BOSS.USERID = CA.GUID
AND AGT.USERID = CA.AGENT_GUID
AND PD.DEFINITIONSTATUS = 'A'
AND PD.DEFINITIONDESCRIPTION = 'EPATENT'
AND NOT EXISTS
(SELECT USERID
FROM AGENT@AGENT_BPM.US.ORACLE.COM
WHERE STARTTIME <= NOW + 1
AND ENDTIME >= NOW
AND USERID = CA.GUID
AND PROCESS = PD.DEFINITIONNAME));
END IF;
IF CA.SYS_NAME = 'SERVICECENTER'
THEN
/*INSERT INTO FACTORY.SERV_AGENT (A_ID,
EMPNO,
AGENT,
C_GROUP_ID,
FLAG,
FROM_TIME,
TO_TIME,
C_TIME)
(SELECT FACTORY.E_AGENT_ID.NEXTVAL,
CA.EMPNO,
CA.AGENT_EMPNO,
1,
'Y',
CA.IDATE_FM,
CA.IDATE_TO,
SYSDATE
FROM DUAL
WHERE NOT EXISTS
(SELECT A_ID
FROM FACTORY.SERV_AGENT
WHERE EMPNO = CA.EMPNO
AND C_GROUP_ID = 1
AND FROM_TIME <= NOW + 1
AND TO_TIME >= NOW
AND FLAG = 'Y'));*/
INSERT INTO FACTORY.SERV_DEPTAGENT (AGENT_ID,
DEPTNO,
AGENT,
FLAG,
C_TIME,
C_PERSON,
FROM_TIME,
TO_TIME)
(SELECT FACTORY.SERV_DEPTAGENT_ID.NEXTVAL,
DEPTNO,
CA.AGENT_EMPNO,
'Y',
SYSDATE,
CA.EMPNO,
CA.IDATE_FM,
CA.IDATE_TO
FROM AAS_HR_EMPLOYEE EMP
WHERE COMPANY_CODE = 'MSL' AND EMPNO = CA.EMPNO
AND NOT EXISTS
(SELECT DEPTNO
FROM FACTORY.SERV_DEPTAGENT
WHERE DEPTNO = EMP.DEPTNO
AND FLAG = 'Y'
AND NVL (FROM_TIME, SYSDATE) <=
NOW + 1
AND NVL (TO_TIME, SYSDATE) >=
NOW));
END IF;
IF CA.SYS_NAME = 'GATECREDIT'
THEN
INSERT INTO FACTORY.E_AGENT (A_ID,
EMPNO,
AGENT,
C_GROUP_ID,
FLAG,
FROM_TIME,
TO_TIME,
C_TIME)
(SELECT FACTORY.E_AGENT_ID.NEXTVAL,
CA.EMPNO,
CA.AGENT_EMPNO,
5,
'Y',
CA.IDATE_FM,
CA.IDATE_TO,
SYSDATE
FROM DUAL
WHERE NOT EXISTS
(SELECT A_ID
FROM FACTORY.E_AGENT
WHERE EMPNO = CA.EMPNO
AND FROM_TIME <= NOW + 1
AND TO_TIME >= NOW
AND C_GROUP_ID = 5
AND FLAG = 'Y'));
INSERT INTO FACTORY.E_AGENT (A_ID,
EMPNO,
AGENT,
C_GROUP_ID,
FLAG,
FROM_TIME,
TO_TIME,
C_TIME)
(SELECT FACTORY.E_AGENT_ID.NEXTVAL,
CA.EMPNO,
CA.AGENT_EMPNO,
20,
'Y',
CA.IDATE_FM,
CA.IDATE_TO,
SYSDATE
FROM DUAL
WHERE NOT EXISTS
(SELECT A_ID
FROM FACTORY.E_AGENT
WHERE EMPNO = CA.EMPNO
AND FROM_TIME <= NOW + 1
AND TO_TIME >= NOW
AND C_GROUP_ID = 20
AND FLAG = 'Y'));
END IF;
IF CA.SYS_NAME = 'UNITOUT'
THEN
INSERT INTO FACTORY.UNINOUT_AGENT (A_ID,
EMPNO,
AGENT,
C_GROUP_ID,
FLAG,
FROM_TIME,
TO_TIME,
C_TIME)
(SELECT FACTORY.UNINOUT_AGENT_ID.NEXTVAL,
CA.EMPNO,
CA.AGENT_EMPNO,
GROUP_ID,
'Y',
CA.IDATE_FM,
CA.IDATE_TO,
SYSDATE
FROM (SELECT 1 AS GROUP_ID FROM DUAL
UNION
SELECT B.C_GROUP_ID
FROM FACTORY.CHECK_MEMBER A,
FACTORY.CHECK_GROUP B
WHERE A.C_GROUP_ID = B.C_GROUP_ID
AND A.STATUS = 'Y'
AND B.STATUS = 'Y'
AND A.C_MEMBER_NAME = CA.EMPNO) X
WHERE NOT EXISTS
(SELECT A_ID
FROM FACTORY.UNINOUT_AGENT
WHERE EMPNO = CA.EMPNO
AND FROM_TIME <= NOW + 1
AND TO_TIME >= NOW
AND GROUP_ID = X.GROUP_ID
AND FLAG = 'Y'));
END IF;
IF CA.SYS_NAME = 'ELEGAL'
THEN
INSERT INTO LG_AGENT@AGENT_ELEGAL (AGID,
BEGIN,
END,
USERID,
GULID,
USERNAME,
ROLEID,
ROLENAME,
UNITID,
UNITNAME,
COMPANY,
UNITGRADE,
UNITPATH,
AG_USERID,
AG_GULID,
AG_USERNAME,
AG_ROLEID,
AG_ROLENAME,
AG_UNITID,
AG_UNITNAME,
AG_COMPANY,
AG_UNITGRADE,
AG_UNITPATH,
TYPE,
ISDATE,
STATUS,
IS_VIEWDETAIL)
(SELECT SEQ_LG.NEXTVAL@AGENT_ELEGAL,
CA.IDATE_FM,
CA.IDATE_TO,
A.*,
B.*,
'AGENT',
SYSDATE,
1,
0
FROM (SELECT D.USERID,
D.GULID,
D.USERNAME,
A.ROLEID,
D.EMAIL,
A.UNITID,
A.UNITNAME,
SUBSTR (A.UNITID,
0,
INSTR (A.UNITID, '-') - 1),
B.UNIT_GRADE AS UNITGRADE,
E.UNITPATH
FROM SFROLE@AGENT_BPM A,
UNIT@AGENT_BPM B,
USERROLERELATION@AGENT_BPM C,
USERTABLE@AGENT_BPM D,
UNITPATHTABLE@AGENT_BPM E
WHERE A.UNITID = B.UNITID
AND C.USERID = D.USERID
AND A.ROLEID = C.ROLEID
AND B.UNITID = E.UNITID
AND D.USERID = CA.GUID
AND B.UNITID = CA.UNITID) A,
(SELECT D.USERID,
D.GULID,
D.USERNAME,
A.ROLEID,
D.EMAIL,
A.UNITID,
A.UNITNAME,
SUBSTR (A.UNITID,
0,
INSTR (A.UNITID, '-') - 1),
B.UNIT_GRADE AS UNITGRADE,
E.UNITPATH
FROM SFROLE@AGENT_BPM A,
UNIT@AGENT_BPM B,
USERROLERELATION@AGENT_BPM C,
USERTABLE@AGENT_BPM D,
UNITPATHTABLE@AGENT_BPM E
WHERE A.UNITID = B.UNITID
AND C.USERID = D.USERID
AND A.ROLEID = C.ROLEID
AND B.UNITID = E.UNITID
AND D.USERID = CA.AGENT_GUID
AND B.UNITID = CA.AGENT_UNITID) B
WHERE NOT EXISTS
(SELECT AGID
FROM LG_AGENT@AGENT_ELEGAL
WHERE ROLEID = A.ROLEID
AND BEGIN <= NOW + 1
AND END >= NOW
AND STATUS = 1
AND TYPE = 'AGENT'));
END IF;
EXCEPTION
WHEN OTHERS
THEN
UPDATE MSL_AGENT
SET REMARK =
SUBSTR (REMARK, 1, 3800)
|| CA.SYS_NAME
|| ' - '
|| CA.FLAG
|| ' -ERROR '
|| SYSDATE
|| CHR (13)
WHERE ROWID = CA.ROW_ID;
END;
COMMIT;
END LOOP;
END EXECUTE_DATA;
END MSL_GLOBAL_AGENT;
/