独自のprocedureバックアップ

CREATE OR REPLACE PROCEDURE EMS_REVERSE_OVERTIME_DAYS(IN_OTID INTEGER,
                                                     IN_USER VARCHAR2) AS

  V_OTID      EMS_OVERTIME_APPLICATION.OVERTIME_APPLICATION_ID%TYPE := NULL;
  V_EMPID     EMS_OVERTIME_APPLICATION.ASSOCIATE_ID%TYPE := NULL;
  V_DAYS      EMS_OVERTIME_APPLICATION.DAYS%TYPE := NULL;
  V_EMPEXIST  INTEGER := 0;
  V_ERROR_MSG VARCHAR2(100);

BEGIN
  SELECT EMS_OVERTIME_APPLICATION.OVERTIME_APPLICATION_ID,
         EMS_OVERTIME_APPLICATION.ASSOCIATE_ID,
         EMS_OVERTIME_APPLICATION.DAYS
    INTO V_OTID, V_EMPID, V_DAYS
    FROM EMS_OVERTIME_APPLICATION
   WHERE EMS_OVERTIME_APPLICATION.OVERTIME_APPLICATION_ID = IN_OTID
     AND EMS_OVERTIME_APPLICATION.STATUTORY_HOLIDAY = 0;
  SELECT COUNT(*)
    INTO V_EMPEXIST
    FROM DUAL
   WHERE EXISTS (SELECT 1
            FROM EMS_OVERTIME_DAYS
           WHERE EMS_OVERTIME_DAYS.ASSOCIATE_ID = V_EMPID);
  IF V_EMPEXIST <> 0 THEN
    UPDATE EMS_OVERTIME_DAYS
       SET EMS_OVERTIME_DAYS.OVERTIME_DAYS = EMS_OVERTIME_DAYS.OVERTIME_DAYS +
                                             V_DAYS,
           EMS_OVERTIME_DAYS.UPDATE_DATE   = SYSDATE,
           EMS_OVERTIME_DAYS.UPDATE_BY     = IN_USER
     WHERE EMS_OVERTIME_DAYS.ASSOCIATE_ID = V_EMPID;
  ELSE
    INSERT INTO EMS_OVERTIME_DAYS
    VALUES
      (V_EMPID, V_DAYS, SYSDATE, IN_USER, SYSDATE, IN_USER);
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    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_REVERSE_OVERTIME_DAYS',
       SYSDATE,
       'SYSTEM');
    COMMIT;
END EMS_REVERSE_OVERTIME_DAYS;