CREATE OR REPLACE PACKAGE BODY PK_TASK IS
TYPE TASKBYMETYPE IS RECORD(
EMP_ID SYS_WFW_TASK.HANDER_EMP_ID%TYPE,
UNHAND_COUNT NUMBER);
TYPE TASKBYMETYPELIST IS TABLE OF TASKBYMETYPE INDEX BY BINARY_INTEGER;
PROCEDURE SP_HANDBYME_SENDMAIL_TIMER IS
TASKBYMELIST TASKBYMETYPELIST;
v_mail_language VARCHAR2(40):='all';
v_sys_name VARCHAR2(10) :='HOL-WAS';
v_mailResult VARCHAR2(50);
BEGIN
SELECT T.HANDER_EMP_ID, COUNT(0) HANDER_COUNT
BULK COLLECT
INTO TASKBYMELIST
FROM (SELECT T.*
FROM SYS_WFW_TASK T
UNION ALL
SELECT S.*
FROM SYS_WFW_TASK_HIS S) T
INNER JOIN (SELECT *
FROM SYS_WFW_PROC
UNION ALL
SELECT *
FROM SYS_WFW_PROC_HIS) P
ON P.PROC_ID = T.PROC_ID
WHERE T.ENABLED = 1
AND T.TASK_STATUS_ID = 'TS0001'
GROUP BY T.HANDER_EMP_ID;
IF TASKBYMELIST.COUNT>0 THEN
FOR i IN TASKBYMELIST.FIRST .. TASKBYMELIST.LAST LOOP
--do something
COMMIT;
END LOOP;
END IF;
END;
END PK_TASK;