create or replace Procedure Pro_Drivemail_Open
Is
V_SQL VARCHAR2(5000);
--CNT NUMBER;
BEGIN
--SELECT COUNT(*) INTO CNT FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND LENGTH(SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1))>=6;
FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND LENGTH(SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1))>=6) LOOP
V_SQL:='INSERT /*+ APPEND */ INTO EDM_SYSTEM_SEND_TMP NOLOGGING
(ID,EMAIL,OPEN_TIME,SENDING_TIME,OPEN,CLICK)
SELECT ID,EMAIL,OPEN_TIME,SENDING_TIME,OPEN,CLICK
FROM '||C.TNAME||' WHERE ACTIVE = 1 AND SENDING_TIME IS NOT NULL';
--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END LOOP;
--------------------------------------------------------------------
INSERT /*+ APPEND */ INTO EDM_SYSTEM_SEND NOLOGGING
SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY EMAIL ORDER BY NVL(OPEN_TIME,SYSDATE-3650) DESC) AS RK
FROM EDM_SYSTEM_SEND_TMP A;
COMMIT;
--------------------------------------------------------------------
INSERT /*+ APPEND */ INTO EDM_SYSTEM_ACTIVE NOLOGGING
(EMAIL,SENDDATE_REC,OPENDATE_REC,SENDTIMES_TOT,OPENTIMES_TOT,SENDTIMES_MON,OPENTIMES_MON)
SELECT A.EMAIL,A.SENDING_TIME,A.OPEN_TIME,B.SENDTIMES_TOT,B.OPENTIMES_TOT,C.SENDTIMES_MON,C.OPENTIMES_MON
FROM EDM_SYSTEM_SEND A, ---ALL Mails Table
(SELECT EMAIL,
COUNT(*) SENDTIMES_TOT,
SUM(CASE WHEN OPEN <> 0 THEN 1 ELSE 0 END) OPENTIMES_TOT
FROM EDM_SYSTEM_SEND
GROUP BY EMAIL ) B, ---Total Times Table
(SELECT EMAIL,
COUNT(*) SENDTIMES_MON,
SUM(CASE WHEN OPEN <> 0 THEN 1 ELSE 0 END) OPENTIMES_MON
FROM EDM_SYSTEM_SEND
WHERE (SENDING_TIME-SYSDATE)<=60
GROUP BY EMAIL ) C ---Month Times Table
WHERE A.RK = 1
AND A.EMAIL = B.EMAIL(+)
AND A.EMAIL = C.EMAIL(+);
Commit;
DELETE EDM_SYSTEM_SEND_TMP NOLOGGING;
COMMIT;
End Pro_Drivemail_Open;