CREATE OR REPLACE PROCEDURE HRIS_NEWS_TO_PROC(
    P_NEWS_ID HRIS_NEWS.NEWS_ID%TYPE,
    P_EMPLOYEE_ID HRIS_EMPLOYEES.EMPLOYEE_ID%TYPE:=NULL)
AS
  V_ASSIGN_COUNT           NUMBER;
  V_COMPANY_IDS            VARCHAR2(1000 BYTE) :=NULL;
  V_BRANCH_IDS             VARCHAR2(1000 BYTE) :=NULL;
  V_DEPARTMENT_IDS         VARCHAR2(1000 BYTE) :=NULL;
  V_DESIGNATION_IDS        VARCHAR2(1000 BYTE) :=NULL;
  V_POSITION_IDS           VARCHAR2(1000 BYTE) :=NULL;
  V_SERVICE_TYPE_IDS       VARCHAR2(1000 BYTE) :=NULL;
  V_SERVICE_EVENT_TYPE_IDS VARCHAR2(1000 BYTE) :=NULL;
  V_EMPLOYEE_TYPES         VARCHAR2(1000 BYTE) :=NULL;
  V_GENDER_IDS             VARCHAR2(1000 BYTE) :=NULL;
  V_EMPLOYEE_IDS           VARCHAR2(1000 BYTE) :=NULL;
  V_QUERY                  VARCHAR2(1000 BYTE);
  V_EMPLOYEE_ID            NUMBER;
  --
TYPE cur_typ
IS
  REF
  CURSOR;
    c cur_typ;
  BEGIN
    --
    DELETE
    FROM HRIS_NEWS_EMPLOYEE
    WHERE NEWS_ID    =P_NEWS_ID
    AND (EMPLOYEE_ID =
      CASE
        WHEN P_EMPLOYEE_ID IS NOT NULL
        THEN P_EMPLOYEE_ID
      END
    OR P_EMPLOYEE_ID IS NULL);
  --
  SELECT COUNT(*)
  INTO V_ASSIGN_COUNT
  FROM HRIS_NEWS_TO
  WHERE NEWS_ID=P_NEWS_ID;
  --
  IF(V_ASSIGN_COUNT    =0) THEN
    IF (P_EMPLOYEE_ID IS NULL) THEN
      V_QUERY         :='SELECT EMPLOYEE_ID
FROM HRIS_EMPLOYEES
WHERE STATUS     = ''E''';
    ELSE
      V_QUERY :='SELECT EMPLOYEE_ID
FROM HRIS_EMPLOYEES
WHERE STATUS     = ''E''
AND EMPLOYEE_ID = '||P_EMPLOYEE_ID;
    END IF;
  ELSE
    FOR assign IN
    (SELECT * FROM HRIS_NEWS_TO WHERE NEWS_ID= P_NEWS_ID
    )
    LOOP
      IF(assign.COMPANY_ID IS NOT NULL)THEN
        IF(V_COMPANY_IDS   IS NULL)THEN
          V_COMPANY_IDS    :=V_COMPANY_IDS||assign.COMPANY_ID;
        ELSE
          V_COMPANY_IDS :=V_COMPANY_IDS||','||assign.COMPANY_ID;
        END IF;
      END IF;
      --
      IF(assign.BRANCH_ID IS NOT NULL)THEN
        IF(V_BRANCH_IDS   IS NULL)THEN
          V_BRANCH_IDS    :=V_BRANCH_IDS||assign.BRANCH_ID;
        ELSE
          V_BRANCH_IDS :=V_BRANCH_IDS||','||assign.BRANCH_ID;
        END IF;
      END IF;
      --
      IF(assign.DEPARTMENT_ID IS NOT NULL)THEN
        IF(V_DEPARTMENT_IDS   IS NULL)THEN
          V_DEPARTMENT_IDS    :=V_DEPARTMENT_IDS||assign.DEPARTMENT_ID;
        ELSE
          V_DEPARTMENT_IDS :=V_DEPARTMENT_IDS||','||assign.DEPARTMENT_ID;
        END IF;
      END IF;
      --
      IF(assign.DESIGNATION_ID IS NOT NULL)THEN
        IF(V_DESIGNATION_IDS   IS NULL)THEN
          V_DESIGNATION_IDS    :=V_DESIGNATION_IDS||assign.DESIGNATION_ID;
        ELSE
          V_DESIGNATION_IDS :=V_DESIGNATION_IDS||','||assign.DESIGNATION_ID;
        END IF;
      END IF;
      --
      IF(assign.POSITION_ID IS NOT NULL)THEN
        IF(V_POSITION_IDS   IS NULL)THEN
          V_POSITION_IDS    :=V_POSITION_IDS||assign.POSITION_ID;
        ELSE
          V_POSITION_IDS :=V_POSITION_IDS||','||assign.POSITION_ID;
        END IF;
      END IF;
      --
      IF(assign.SERVICE_TYPE_ID IS NOT NULL)THEN
        IF(V_SERVICE_TYPE_IDS   IS NULL)THEN
          V_SERVICE_TYPE_IDS    :=V_SERVICE_TYPE_IDS||assign.SERVICE_TYPE_ID;
        ELSE
          V_SERVICE_TYPE_IDS :=V_SERVICE_TYPE_IDS||','||assign.SERVICE_TYPE_ID;
        END IF;
      END IF;
      --
      IF(assign.SERVICE_EVENT_TYPE_ID IS NOT NULL)THEN
        IF(V_SERVICE_EVENT_TYPE_IDS   IS NULL)THEN
          V_SERVICE_EVENT_TYPE_IDS    :=V_SERVICE_EVENT_TYPE_IDS||assign.SERVICE_EVENT_TYPE_ID;
        ELSE
          V_SERVICE_EVENT_TYPE_IDS :=V_SERVICE_EVENT_TYPE_IDS||','||assign.SERVICE_EVENT_TYPE_ID;
        END IF;
      END IF;
      --
      IF(assign.EMPLOYEE_TYPE IS NOT NULL)THEN
        IF(V_EMPLOYEE_TYPES   IS NULL)THEN
          V_EMPLOYEE_TYPES    :=V_EMPLOYEE_TYPES||'''' ||assign.EMPLOYEE_TYPE||'''';
        ELSE
          V_EMPLOYEE_TYPES :=V_EMPLOYEE_TYPES||','''||assign.EMPLOYEE_TYPE||'''';
        END IF;
      END IF;
      --
      IF(assign.GENDER_ID IS NOT NULL)THEN
        IF(V_GENDER_IDS   IS NULL)THEN
          V_GENDER_IDS    :=V_GENDER_IDS||assign.GENDER_ID;
        ELSE
          V_GENDER_IDS :=V_GENDER_IDS||','||assign.GENDER_ID;
        END IF;
      END IF;
      --
      IF(assign.EMPLOYEE_ID IS NOT NULL)THEN
        IF(V_EMPLOYEE_IDS   IS NULL)THEN
          V_EMPLOYEE_IDS    :=V_EMPLOYEE_IDS||assign.EMPLOYEE_ID;
        ELSE
          V_EMPLOYEE_IDS :=V_EMPLOYEE_IDS||','||assign.EMPLOYEE_ID;
        END IF;
      END IF;
      --
    END LOOP;
    --
    V_QUERY          := 'SELECT EMPLOYEE_ID FROM HRIS_EMPLOYEES WHERE 1=1';
    IF(V_COMPANY_IDS IS NOT NULL) THEN
      V_QUERY        :=V_QUERY||' AND COMPANY_ID IN ('||V_COMPANY_IDS||')';
    END IF;
    IF(V_BRANCH_IDS IS NOT NULL) THEN
      V_QUERY       :=V_QUERY||' AND BRANCH_ID IN ('||V_BRANCH_IDS||')';
    END IF;
    IF(V_DEPARTMENT_IDS IS NOT NULL) THEN
      V_QUERY           :=V_QUERY||' AND DEPARTMENT_ID IN ('||V_DEPARTMENT_IDS||')';
    END IF;
    IF(V_DESIGNATION_IDS IS NOT NULL) THEN
      V_QUERY            :=V_QUERY||' AND DESIGNATION_ID IN ('||V_DESIGNATION_IDS||')';
    END IF;
    IF(V_POSITION_IDS IS NOT NULL) THEN
      V_QUERY         :=V_QUERY||' AND POSITION_ID IN ('||V_POSITION_IDS||')';
    END IF;
    IF(V_SERVICE_TYPE_IDS IS NOT NULL) THEN
      V_QUERY             :=V_QUERY||' AND SERVICE_TYPE_ID IN ('||V_SERVICE_TYPE_IDS||')';
    END IF;
    IF(V_SERVICE_EVENT_TYPE_IDS IS NOT NULL) THEN
      V_QUERY                   :=V_QUERY||' AND SERVICE_EVENT_TYPE_ID IN ('||V_SERVICE_EVENT_TYPE_IDS||')';
    END IF;
    IF(V_EMPLOYEE_TYPES IS NOT NULL) THEN
      V_QUERY           :=V_QUERY||' AND EMPLOYEE_TYPE IN ('||V_EMPLOYEE_TYPES||')';
    END IF;
    IF(V_GENDER_IDS IS NOT NULL) THEN
      V_QUERY       :=V_QUERY||' AND GENDER_ID IN ('||V_GENDER_IDS||')';
    END IF;
    IF(V_EMPLOYEE_IDS IS NOT NULL) THEN
      V_QUERY         :=V_QUERY||' AND EMPLOYEE_ID IN ('||V_EMPLOYEE_IDS||')';
    END IF;
  END IF;
  --
  OPEN c FOR V_QUERY ;
  LOOP
    FETCH c INTO V_EMPLOYEE_ID;
    EXIT
  WHEN c%NOTFOUND;
    IF(P_EMPLOYEE_ID  IS NOT NULL )THEN
      IF P_EMPLOYEE_ID = V_EMPLOYEE_ID THEN
        INSERT
        INTO HRIS_NEWS_EMPLOYEE
          (
            NEWS_ID,
            EMPLOYEE_ID
          )
          VALUES
          (
            P_NEWS_ID,
            V_EMPLOYEE_ID
          );
      END IF;
    ELSE
      INSERT
      INTO HRIS_NEWS_EMPLOYEE
        (
          NEWS_ID,
          EMPLOYEE_ID
        )
        VALUES
        (
          P_NEWS_ID,
          V_EMPLOYEE_ID
        );
    END IF;
  END LOOP;
  CLOSE c;
END;