CREATE OR REPLACE PROCEDURE HRIS_HOLIDAY_ASSIGN_AUTO(
    P_HOLIDAY_ID HRIS_HOLIDAY_MASTER_SETUP.HOLIDAY_ID%TYPE,
    P_EMPLOYEE_ID HRIS_EMPLOYEES. EMPLOYEE_ID%TYPE:=NULL)
AS
  V_IS_EMP_IN CHAR;
BEGIN
  DELETE
  FROM HRIS_EMPLOYEE_HOLIDAY
  WHERE HOLIDAY_ID = P_HOLIDAY_ID
  AND (EMPLOYEE_ID =
    CASE
      WHEN P_EMPLOYEE_ID IS NOT NULL
      THEN P_EMPLOYEE_ID
    END
  OR P_EMPLOYEE_ID IS NULL);
  --
  FOR employee IN
  (SELECT E.EMPLOYEE_ID,
    H.START_DATE
  FROM HRIS_EMPLOYEES E,
    (SELECT TRUNC(START_DATE) AS START_DATE
    FROM HRIS_HOLIDAY_MASTER_SETUP
    WHERE HOLIDAY_ID = P_HOLIDAY_ID
    AND STATUS       ='E'
    ) H
  WHERE E.STATUS         ='E'
  AND TRUNC(E.JOIN_DATE) < H.START_DATE
  AND (E.EMPLOYEE_ID     =
    CASE
      WHEN P_EMPLOYEE_ID IS NOT NULL
      THEN P_EMPLOYEE_ID
    END
  OR P_EMPLOYEE_ID IS NULL)
  )
  LOOP
    V_IS_EMP_IN    := HRIS_IS_EMP_IN(employee.EMPLOYEE_ID,'HRIS_HOLIDAY_MASTER_SETUP','HOLIDAY_ID',P_HOLIDAY_ID);
    IF V_IS_EMP_IN !='Y' THEN
      CONTINUE;
    END IF;
    INSERT
    INTO HRIS_EMPLOYEE_HOLIDAY
      (
        HOLIDAY_ID,
        EMPLOYEE_ID
      )
      VALUES
      (
        P_HOLIDAY_ID,
        employee.EMPLOYEE_ID
      );
    IF employee.START_DATE<TRUNC(SYSDATE) THEN
NULL;
-- need this task to be run on schedule | pending for now
--       HRIS_REATTENDANCE(employee.START_DATE,employee.EMPLOYEE_ID,employee.START_DATE);
    END IF;
  END LOOP;
END;