自个的procedure 2

自个用的

CREATE OR REPLACE PROCEDURE EMS_AE_REPORT(I_BEGIN_DATE       IN VARCHAR2,
                                          I_END_DATE         IN VARCHAR2,
                                          I_BEGIN_DATEF      IN VARCHAR2,
                                          I_END_DATEL        IN VARCHAR2,
                                          OUT_CURSOR_AMPM    OUT SYS_REFCURSOR,
                                          OUT_CURSOR_PMCOUNT OUT SYS_REFCURSOR,
                                          O_BEGIN_DATE1      OUT VARCHAR2,
                                          O_END_DATE1        OUT VARCHAR2,
                                          O_BEGIN_DATE2      OUT VARCHAR2,
                                          O_END_DATE2        OUT VARCHAR2,
                                          O_BEGIN_DATE3      OUT VARCHAR2,
                                          O_END_DATE3        OUT VARCHAR2,
                                          P_OUT_MESSAGE      OUT VARCHAR2) AS

  -- The specific date
  P_BEGIN_DATE DATE := TO_DATE(I_BEGIN_DATE, 'yyyy-mm-dd hh24:mi:ss');
  P_END_DATE   DATE := TO_DATE(I_END_DATE, 'yyyy-mm-dd hh24:mi:ss');
  -- The current month
  P_BEGIN_DATE1 DATE := TO_DATE(I_BEGIN_DATEF,'yyyy-mm-dd hh24:mi:ss');
  P_END_DATE1   DATE := TO_DATE(I_END_DATEL,'yyyy-mm-dd hh24:mi:ss');
  -- The last 1 month
  P_BEGIN_DATE2 DATE := ADD_MONTHS(TO_DATE(I_BEGIN_DATEF,'yyyy-mm-dd hh24:mi:ss'),-1);
  P_END_DATE2   DATE := ADD_MONTHS(TO_DATE(I_END_DATEL,'yyyy-mm-dd hh24:mi:ss'), -1);
  -- The last 2 month
  P_BEGIN_DATE3 DATE := ADD_MONTHS(TO_DATE(I_BEGIN_DATEF,'yyyy-mm-dd hh24:mi:ss'), -2);
  P_END_DATE3   DATE := ADD_MONTHS(TO_DATE(I_END_DATEL,'yyyy-mm-dd hh24:mi:ss'), -2);
  V_ERROR_MSG   VARCHAR2(100);
  --Current Year
  P_YBEGIN_DATE DATE := TO_DATE(CONCAT(SUBSTR(I_BEGIN_DATEF, 1, 4),'-1-1 00:00:00') , 'yyyy-mm-dd hh24:mi:ss');
  P_YEND_DATE   DATE := TO_DATE(CONCAT(SUBSTR(I_END_DATEL, 1, 4),'-12-31 23:59:59'), 'yyyy-mm-dd hh24:mi:ss');

BEGIN
  P_OUT_MESSAGE := '';
  --THE First cursor

  execute immediate 'alter session set nls_date_language=''american''';
  OPEN OUT_CURSOR_AMPM FOR
 SELECT T2.ASSOCIATE_NAME,
        T2.ASSOCIATE_ID,
        T1.ASSOCIATE_NAME,
        T1.ASSOCIATE_ID
   FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID = 2) T1
  RIGHT OUTER JOIN (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (3, 4)) T2 ON T1.AM =
                                                                                   T2.ASSOCIATE_ID
  GROUP BY T2.ASSOCIATE_NAME,
           T1.ASSOCIATE_NAME,
           T1.ASSOCIATE_ID,
           T2.ASSOCIATE_ID
  ORDER BY T2.ASSOCIATE_NAME ASC;
  --The secode cursor
  OPEN OUT_CURSOR_PMCOUNT FOR
    SELECT 'PEEP' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_PEEP
                        WHERE EMS_AE_TRACKER_PEEP.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE AND P_END_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'PEEP1' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_PEEP
                        WHERE EMS_AE_TRACKER_PEEP.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE1 AND P_END_DATE1) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'PEEP2' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_PEEP
                        WHERE EMS_AE_TRACKER_PEEP.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE2 AND P_END_DATE2) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'PEEP3' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_PEEP
                        WHERE EMS_AE_TRACKER_PEEP.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE3 AND P_END_DATE3) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'RR' TNAME, T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME, COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_RR
                        WHERE EMS_AE_TRACKER_RR.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE AND P_END_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.Pm)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'RR1' TNAME, T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME, COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_RR
                        WHERE EMS_AE_TRACKER_RR.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE1 AND P_END_DATE1) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.Pm)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'RR2' TNAME, T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME, COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_RR
                        WHERE EMS_AE_TRACKER_RR.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE2 AND P_END_DATE2) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.Pm)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'RR3' TNAME, T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME, COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_RR
                        WHERE EMS_AE_TRACKER_RR.UPDATE_DATE BETWEEN
                              P_BEGIN_DATE3 AND P_END_DATE3) T2 ON T1.ASSOCIATE_ID =
                                                                   TO_NUMBER(T2.PM)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'ATTRITION' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_ATTRITION_TRACKER
                        WHERE EMS_ATTRITION_TRACKER.FLG = 0
                          AND EMS_ATTRITION_TRACKER.CREATION_DATE BETWEEN
                              P_BEGIN_DATE AND P_END_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'ATTRITION_1' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_ATTRITION_TRACKER
                        WHERE EMS_ATTRITION_TRACKER.FLG = 0
                          AND EMS_ATTRITION_TRACKER.UPDATE_DATE BETWEEN
                              P_YBEGIN_DATE AND P_YEND_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'ATTRITION_0' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_ATTRITION_TRACKER
                        WHERE EMS_ATTRITION_TRACKER.FLG = 1
                          AND EMS_ATTRITION_TRACKER.UPDATE_DATE BETWEEN
                              P_YBEGIN_DATE AND P_YEND_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'MEET' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_MEET
                        WHERE EMS_AE_TRACKER_MEET.UPDATE_DATE BETWEEN
                              P_YBEGIN_DATE AND P_YEND_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
    UNION ALL
    SELECT 'CORPORATE' TNAME,
           T1.ASSOCIATE_ID,
           T1.ASSOCIATE_NAME,
           COUNT(T2.ROWID)
      FROM (SELECT * FROM EMS_USER WHERE EMS_USER.ROLE_ID IN (2, 3, 4)) T1
      LEFT OUTER JOIN (SELECT *
                         FROM EMS_AE_TRACKER_CORPORATE
                        WHERE EMS_AE_TRACKER_CORPORATE.UPDATE_DATE BETWEEN
                              P_YBEGIN_DATE AND P_YEND_DATE) T2 ON T1.ASSOCIATE_ID =
                                                                 TO_NUMBER(T2.UPDATE_BY)
     GROUP BY T1.ASSOCIATE_ID, T1.ASSOCIATE_NAME
     ORDER BY ASSOCIATE_ID;

  -- Return the last 1, 2, 3 month
  O_BEGIN_DATE1 := TO_CHAR(P_BEGIN_DATE1, 'yyyy-Mon');
  O_END_DATE1   := TO_CHAR(P_END_DATE1, 'yyyy-Mon');
  O_BEGIN_DATE2 := TO_CHAR(P_BEGIN_DATE2, 'yyyy-Mon');
  O_END_DATE2   := TO_CHAR(P_END_DATE2, 'yyyy-Mon');
  O_BEGIN_DATE3 := TO_CHAR(P_BEGIN_DATE3, 'yyyy-Mon');
  O_END_DATE3   := TO_CHAR(P_END_DATE3, 'yyyy-Mon');

EXCEPTION
  WHEN OTHERS THEN
    P_OUT_MESSAGE := p_out_message ||
                     'Exception when running EMS_AE_REPORT';
    V_ERROR_MSG   := SQLCODE || '--' || SQLERRM;
    ROLLBACK;
    INSERT INTO EMS_ERROR_LOG
      (ERROR_ID,
       ERROR_CODE,
       ERROR_MESSAGE,
       ERROR_LOGGED_IN,
       ERROR_RAISED_ON,
       ERROR_RAISED_BY)
    VALUES
      (EMS_ERROR_SEQ.NEXTVAL,
       SUBSTR(V_ERROR_MSG, 1, 10),
       SUBSTR(V_ERROR_MSG, 11, 255),
       'PROCEDURE-EMS_AE_REPORT',
       SYSDATE,
       'SYSTEM');
    COMMIT;
END EMS_AE_REPORT;