CREATE OR REPLACE PROCEDURE PRC_STATISTICS_RTX(
--参数IN表示输入参数，OUT表示输出参数，类型可以使用任意Oracle中的合法类型。
CHECKYEAR IN VARCHAR2,
CHECKQUARTER IN VARCHAR2) AS
-----------------定义变量-------------------------
KPI_USER_ID NUMBER(11); --当前遍历的员工(被评价人)
KPI_PRAISE_ID NUMBER(11); --评价人
KPI_CONTACT_TIME NUMBER(11); --联系次数
KPI_DIRECTOR VARCHAR2(50); --直接主管,组织结构多负责人，所以可能有多个直接主管
KPI_DEPT_MANAGER VARCHAR2(50); --部门主管,组织结构多负责人，所以可能有多个部门主管
KPI_SUBORDINATE VARCHAR2(1000); --直接下属员工 (组长查询自己的组员，经理及总监查询小组负责人)
KPI_VARIABLE VARCHAR2(20); --每次遍历游标时，临时存放数据
D_QUESTION NUMBER(11); --主管的问卷ID
E_QUESTION NUMBER(11); --普通员工的问卷ID
--主管考核维度 直接定义，免去每次for循环来遍历
D_CHECK_LEVEL1 NUMBER(11); --直接主管
D_CHECK_LEVEL2 NUMBER(11); --直接下属
D_CHECK_LEVEL3 NUMBER(11); --同部门
D_CHECK_LEVEL4 NUMBER(11); --合作部门
--主管考核维度对应的上限人数
D_CHECK_NUM1 NUMBER(11); --直接主管
D_CHECK_NUM2 NUMBER(11); --直接下属
D_CHECK_NUM3 NUMBER(11); --同部门
D_CHECK_NUM4 NUMBER(11); --合作部门
--普通员工考核维度
E_CHECK_LEVEL1 NUMBER(11); --直接主管
E_CHECK_LEVEL2 NUMBER(11); --部门主管
E_CHECK_LEVEL3 NUMBER(11); --同部门
E_CHECK_LEVEL4 NUMBER(11); --合作部门
--普通员工考核维度对应的上限人数
E_CHECK_NUM1 NUMBER(11); --直接主管
E_CHECK_NUM2 NUMBER(11); --部门主管
E_CHECK_NUM3 NUMBER(11); --同部门
E_CHECK_NUM4 NUMBER(11); --合作部门
-----------------------定义数组---------------------
--考核维度表
-- checklevel m_kpi_360check_level%rowtype; --定义一个变量（每一行的数据）
TYPE CHECKLEVELTBL IS TABLE OF M_KPI_360CHECK_LEVEL%ROWTYPE INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_CHECKLEVEL CHECKLEVELTBL; --用新的表结构定义一个变量（当做数组使用）
--问卷
-- question t_360check_questions%rowtype;
TYPE QUESTIONTBL IS TABLE OF T_360CHECK_QUESTIONS%ROWTYPE INDEX BY BINARY_INTEGER;
V_QUESTION QUESTIONTBL;
--需要考核的员工
-- userextend t_usr_extend%rowtype;
TYPE USEREXTENDTBL IS TABLE OF T_USR_EXTEND%ROWTYPE INDEX BY BINARY_INTEGER;
V_EXTEND USEREXTENDTBL;
--员工的直接主管
TYPE DIRECTORTBL IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_DIRECTOR DIRECTORTBL;
--RTX及邮件统计的发送人，接收人和联系次数
TYPE RTXCONTACT IS TABLE OF W_ECM_IM_LOG%ROWTYPE INDEX BY BINARY_INTEGER; --用变量定义一个表结构
V_RTXCONTACT RTXCONTACT;
---------------定义游标--------------------------
CURSOR CURSOR_1(NO NUMBER) IS
SELECT O.MANAGER_IDS
FROM M_ORGANIZATION O
WHERE O.PARENT_ID IN
(SELECT ORG_ID FROM M_EMPLOYEE E WHERE E.USER_ID = NO);
BEGIN
------------存储过程开始执行，记录日志--------------
WRITE_LOG('PROCEDURE:TEST3',
'START',
'存储过程TEST3开始执行：统计员工RTX及邮件联系人，生成绩效考核答卷表---------开始执行');
COMMIT;
SELECT * BULK COLLECT INTO V_CHECKLEVEL FROM M_KPI_360CHECK_LEVEL T; --考核维度
SELECT * BULK COLLECT
INTO V_QUESTION
FROM T_360CHECK_QUESTIONS
WHERE CHECK_YEAR = CHECKYEAR
AND CHECK_QUARTER = CHECKQUARTER; -- 问卷
SELECT T.* BULK COLLECT
INTO V_EXTEND
FROM T_USR_EXTEND T
JOIN M_EMPLOYEE E
ON T.USER_ID = E.USER_ID
WHERE T.IS_CHECK = '1'
AND E.STATUS = '1'; --参与考核员工
-----遍历考核维度，依次获得不同员工级别不同考核维度对应的维度ID-----
FOR I IN 1 .. V_CHECKLEVEL.COUNT LOOP
--主管
IF V_CHECKLEVEL(I).EMP_LEVEL = 0 THEN
CASE
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 1 THEN
--直接主管
D_CHECK_LEVEL1 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM1 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 2 THEN
--直接下属
D_CHECK_LEVEL2 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM2 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 3 THEN
--同部门员工
D_CHECK_LEVEL3 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM3 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 4 THEN
--合作部门员工
D_CHECK_LEVEL4 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
D_CHECK_NUM4 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
END CASE;
END IF;
--普通员工
IF V_CHECKLEVEL(I).EMP_LEVEL = 1 THEN
CASE
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 1 THEN
--直接主管
E_CHECK_LEVEL1 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM1 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 5 THEN
--部门主管（二级主管）
E_CHECK_LEVEL2 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM2 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 3 THEN
--同部门员工
E_CHECK_LEVEL3 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM3 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
WHEN V_CHECKLEVEL(I).APPRAISE_LEVEL = 4 THEN
--合作部门员工
E_CHECK_LEVEL4 := V_CHECKLEVEL(I).CHECK_LEVEL_ID;
E_CHECK_NUM4 := V_CHECKLEVEL(I).UPPER_LIMIT_NUM;
END CASE;
END IF;
END LOOP;
--遍历问卷表，获得不同级别员工对应的问卷ID
FOR I IN 1 .. V_QUESTION.COUNT LOOP
--主管
IF V_QUESTION(I).EMP_LEVEL = 0 THEN
D_QUESTION := V_QUESTION(I).QID;
END IF;
--普通员工
IF V_QUESTION(I).EMP_LEVEL = 1 THEN
E_QUESTION := V_QUESTION(I).QID;
END IF;
END LOOP;
--把数据插入临时表中
------RTX和邮件的联系次数之和，插入临时表-----------
INSERT INTO W_ECM_IM_LOG
SELECT A.IM_FROM_SENDER, A.PRAISE, SUM(NUM) AS NUM
FROM (SELECT T.IM_FROM_SENDER,
REGEXP_SUBSTR(IM_TO_RECEIVERS, '[^,]+', 1, L) AS PRAISE,
SUM(NUM) AS NUM
FROM (SELECT T.IM_FROM_SENDER,
T.IM_TO_RECEIVERS,
COUNT(*) AS NUM
FROM T_ECM_IM_LOG T
GROUP BY T.IM_FROM_SENDER, T.IM_TO_RECEIVERS) T,
(SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 100)
WHERE L <= LENGTH(IM_TO_RECEIVERS) -
LENGTH(REPLACE(IM_TO_RECEIVERS, ',')) + 1
GROUP BY IM_FROM_SENDER,
REGEXP_SUBSTR(IM_TO_RECEIVERS, '[^,]+', 1, L)
UNION ALL
SELECT U1.DOMAIN_USER AS IM_FROM_SENDER,
U2.DOMAIN_USER AS PRAISE,
A.NUM
FROM (SELECT T.MAIL_FROM_SENDERS,
T.MAIL_TO_RECEIVERS,
COUNT(*) AS NUM
FROM T_ECM_MAIL_LOG T
GROUP BY T.MAIL_FROM_SENDERS, T.MAIL_TO_RECEIVERS) A,
M_USERS U1,
M_USERS U2
WHERE A.MAIL_FROM_SENDERS = U1.REG_IP
AND A.MAIL_TO_RECEIVERS = U2.REG_IP
AND U1.DOMAIN_USER IS NOT NULL
AND U2.DOMAIN_USER IS NOT NULL) A
GROUP BY IM_FROM_SENDER, PRAISE;
--------------------------------开始遍历员工-----------------------------------------------
--遍历所有参与考核的员工
FOR I IN 1 .. V_EXTEND.COUNT LOOP
KPI_USER_ID := V_EXTEND(I).USER_ID; --当前遍历的员工
------主管--------------------
IF V_EXTEND(I).EMP_LEVEL = 0 THEN
--查找直接主管
SELECT O.MANAGER_IDS BULK COLLECT
INTO V_DIRECTOR
FROM M_ORGANIZATION O
WHERE O.MANAGER_IDS IS NOT NULL
START WITH O.ORG_ID IN (SELECT ORG_ID
FROM M_EMPLOYEE E
WHERE E.USER_ID = KPI_USER_ID)
CONNECT BY ORG_ID = PRIOR PARENT_ID
ORDER BY O.TYPE DESC;
IF V_DIRECTOR.COUNT >= 2 THEN
KPI_DIRECTOR := V_DIRECTOR(2);
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DIRECTOR || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM1; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL1,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
----------查找直接下属-------------
KPI_SUBORDINATE := '';
--该游标查询自己下级小组负责人，所以查询组长的直接下属时为空
OPEN CURSOR_1(KPI_USER_ID);
FETCH CURSOR_1
INTO KPI_SUBORDINATE;
--如果游标返回结果为空，则当前遍历员工是组长，查询自己相同小组下的员工作为自己的直接下属
IF CURSOR_1%NOTFOUND THEN
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND E.ORG_ID IN
(SELECT ORG_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND U1.USER_ID = KPI_USER_ID
AND E.USER_ID = U2.USER_ID
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM2;
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
--直接下属拼接成字符串，查询同部门员工时，这部分员工不包括在内
KPI_SUBORDINATE := KPI_SUBORDINATE || ',' || V_RTXCONTACT(J)
.PRAISE;
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL2,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
ELSE
LOOP
FETCH CURSOR_1
INTO KPI_VARIABLE;
KPI_SUBORDINATE := KPI_SUBORDINATE || ',' || KPI_VARIABLE;
EXIT WHEN CURSOR_1%NOTFOUND;
END LOOP;
KPI_SUBORDINATE := KPI_SUBORDINATE || ',';
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_SUBORDINATE || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM2;
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL2,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
CLOSE CURSOR_1;
---------------------查找同部门同事--------------------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE --除去直接主管
',%' || E1.USER_ID || ',%'
AND ',' || KPI_SUBORDINATE || ',' NOT LIKE --除去直接下属
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM3;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL3,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
----------------------查找不同部门同事------------------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID NOT IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_SUBORDINATE || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= D_CHECK_NUM4;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
D_CHECK_LEVEL4,
D_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
END IF;
----------------普通员工---------------
IF V_EXTEND(I).EMP_LEVEL = '1' THEN
--递归查询，既包括直接主管，又包括部门主管
SELECT O.MANAGER_IDS BULK COLLECT
INTO V_DIRECTOR
FROM M_ORGANIZATION O
WHERE O.MANAGER_IDS IS NOT NULL
START WITH O.ORG_ID IN (SELECT ORG_ID
FROM M_EMPLOYEE E
WHERE E.USER_ID = KPI_USER_ID)
CONNECT BY ORG_ID = PRIOR PARENT_ID
ORDER BY O.TYPE DESC;
---------直接主管-------
IF V_DIRECTOR.COUNT >= 1 THEN
KPI_DIRECTOR := V_DIRECTOR(1);
--和直接主管之间RTX联系次数
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DIRECTOR || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM1; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL1,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
------部门主管-----
IF V_DIRECTOR.COUNT >= 2 THEN
KPI_DEPT_MANAGER := V_DIRECTOR(2);
--和部门主管之间RTX联系次数
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U1.USER_ID = KPI_USER_ID
AND ',' || KPI_DEPT_MANAGER || ',' LIKE
'%,' || U2.USER_ID || ',%'
ORDER BY NUM DESC) A
WHERE ROWNUM <= E_CHECK_LEVEL2; --组织结构多负责人
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL2,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
-------------部门内同事-------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_DEPT_MANAGER || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM3;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL3,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
-------------部门间同事-------
SELECT A.* BULK COLLECT
INTO V_RTXCONTACT
FROM (
SELECT U1.USER_ID AS ACCEPT, U2.USER_ID AS PRAISE, T.NUM
FROM W_ECM_IM_LOG T, M_USERS U1, M_USERS U2, M_EMPLOYEE E1
WHERE T.ACCEPT = U1.DOMAIN_USER
AND T.PRAISE = U2.DOMAIN_USER
AND U2.USER_ID = E1.USER_ID
AND U1.USER_ID = KPI_USER_ID
AND E1.DEPT_ID NOT IN
(SELECT DEPT_ID
FROM M_EMPLOYEE
WHERE USER_ID = KPI_USER_ID)
AND ',' || KPI_DIRECTOR || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
AND ',' || KPI_DEPT_MANAGER || ',' NOT LIKE
',%' || E1.USER_ID || ',%'
ORDER BY T.NUM DESC) A
WHERE ROWNUM <= E_CHECK_NUM4;
IF V_DIRECTOR.COUNT >= 1 THEN
FOR J IN 1 .. V_RTXCONTACT.COUNT LOOP
INSERT INTO T_360CHECK_ANSWER_TMP
(CA_ID,
ACCEPT_APPRAISE_USER,
APPRAISE_USER,
CHECK_LEVEL_ID,
QID,
STATUS,
CONTACT_TIMES)
VALUES
(OA_SEQ_BASE.NEXTVAL,
KPI_USER_ID,
V_RTXCONTACT(J).PRAISE,
E_CHECK_LEVEL4,
E_QUESTION,
0,
V_RTXCONTACT(J).NUM);
END LOOP;
END IF;
END IF;
END LOOP;
--------------------RTX和邮件统计结束，记录日志----------------------------
WRITE_LOG('PROCEDURE:TEST3',
'END',
'存储过程TEST3开始执行：统计员工RTX及邮件联系人，生成绩效考核答卷表------执行结束');
COMMIT;
EXCEPTION
--捕获所有的异常（类似JAVA中的Exception）
WHEN OTHERS THEN
ROLLBACK;
--插入异常日志(方法必须有返回值，这里调用另一个存储过程，记录日志)
WRITE_LOG('PROCEDURE:TEST3',
'ERROR',
SUBSTR(SQLERRM, 0, 3000) ||
SUBSTR(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 0, 1000));
END PRC_STATISTICS_RTX;
扫一扫
4137
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
