BEGIN
  FOR mv IN
  (SELECT *
  FROM
    (SELECT EMPLOYEE_ID,
      FISCAL_YEAR_ID,
      MONTH_ID,
      ROW_NUMBER() OVER (PARTITION BY EMPLOYEE_ID ORDER BY EMPLOYEE_ID,FISCAL_YEAR_ID ) R
    FROM HRIS_FLAT_VALUE_DETAIL
    )
  WHERE R!=1
  )
  LOOP
  
  DELETE FROM HRIS_FLAT_VALUE_DETAIL WHERE EMPLOYEE_ID = mv.EMPLOYEE_ID AND FISCAL_YEAR_ID = mv.FISCAL_YEAR_ID AND MONTH_ID = mv.MONTH_ID;
  
  END LOOP;
END;


BEGIN
  FOR u IN
  (SELECT U.*
  FROM HRIS_USERS U
  JOIN HRIS_EMPLOYEES E
  ON (U.EMPLOYEE_ID  = E.EMPLOYEE_ID)
  WHERE U.USER_NAME IN
    (SELECT USER_NAME FROM HRIS_USERS GROUP BY USER_NAME HAVING COUNT(*) > 1
    )
  AND E.STATUS= 'D'
  ORDER BY USER_NAME
  )
  LOOP
    DELETE FROM HRIS_USERS WHERE USER_ID= u.USER_ID;
  END LOOP;
END;
--
SELECT USER_NAME FROM HRIS_USERS GROUP BY USER_NAME HAVING COUNT(*) > 1;


SELECT * FROM HRIS_USERS ORDER BY CREATED_DT DESC;

UPDATE HRIS_USERS SET ROLE_ID = 3 WHERE CREATED_DT = '14-AUG-17';


-- JGI RECEPTION SHIFT QUERIES

SELECT * FROM HRIS_EMPLOYEES WHERE LOWER(FIRST_NAME)  LIKE LOWER('%NAJINA%');
--
SELECT * FROM HRIS_EMPLOYEES WHERE LOWER(FIRST_NAME)  LIKE LOWER('%SUJA%');
--
SELECT * FROM HRIS_EMPLOYEES WHERE LOWER(FIRST_NAME)  LIKE LOWER('%SABIN%');
--
SELECT USER_NAME,FN_DECRYPT_PASSWORD(PASSWORD) FROM HRIS_USERS WHERE EMPLOYEE_ID=700342;

SELECT * FROM HRIS_SHIFTS;

--1,4,3

--700378 |SUJASMI | sujasmi_shrestha_maleku | 9849100048s
--700291 |NAJINA | najina_maharjan | rameshbishnu


DESC HRIS_EMPLOYEE_SHIFTS;


INSERT INTO HRIS_EMPLOYEE_SHIFTS VALUES(700378,4,'16-JUL-17','01-JAN-19');
--
SELECT * FROM HRIS_EMPLOYEE_SHIFTS;



DELETE  FROM HRIS_EMPLOYEE_SHIFTS;

DELETE FROM HRIS_ATTENDANCE_DETAIL WHERE EMPLOYEE_ID = 700291;

BEGIN
  HRIS_REATTENDANCE('16-JUL-17',700291);
END;
--

UPDATE HRIS_SHIFTS SET EARLY_OUT = 5 WHERE SHIFT_ID=3;

--


SELECT SHIFT_ID,LATE_IN,EARLY_OUT FROM HRIS_SHIFTS;

UPDATE HRIS_SHIFTS SET EARLY_OUT = EARLY_OUT/60 WHERE SHIFT_ID = 7;



-- SELECT AD.EMPLOYEE_ID,
  E.FULL_NAME                             AS EMPLOYEE_NAME,
  TO_CHAR(AD.ATTENDANCE_DT,'DD-MON-YYYY') AS ATTENDANCE_DATE,
  TO_CHAR(AD.IN_TIME,'HH:MI AM')          AS IN_TIME,
  TO_CHAR(AD.OUT_TIME,'HH:MI AM')         AS OUT_TIME,
  LATE_STATUS_DESC(AD.LATE_STATUS)        AS LATE_STATUS_DETAIL,
  AD.SHIFT_ID,
  S.SHIFT_ENAME AS SHIFT_NAME
FROM HRIS_ATTENDANCE_DETAIL AD
JOIN HRIS_EMPLOYEES E
ON (AD.EMPLOYEE_ID =E.EMPLOYEE_ID)
JOIN HRIS_SHIFTS S
ON (AD.SHIFT_ID      =S.SHIFT_ID)
WHERE AD.EMPLOYEE_ID = 700291
ORDER BY AD.ATTENDANCE_DT ASC;
--
SELECT DISTINCT ES.SHIFT_ID,
  S.SHIFT_ENAME                    AS SHIFT_NAME,
  TO_CHAR(S.START_TIME,'HH:MI AM') AS IN_TIME,
  TO_CHAR(S.END_TIME,'HH:MI AM')   AS OUT_TIME
FROM HRIS_EMPLOYEE_SHIFTS ES
JOIN HRIS_SHIFTS S
ON (ES.SHIFT_ID= S.SHIFT_ID);
--
SELECT HRIS_BEST_CASE_SHIFT(700291,TO_DATE('17-JUL-2017','DD-MON-YYYY'))
FROM DUAL;
--
SELECT (AD.OUT_TIME-TRUNC(AD.OUT_TIME))-(AD.IN_TIME-TRUNC(AD.IN_TIME))
FROM HRIS_ATTENDANCE_DETAIL AD
WHERE AD.EMPLOYEE_ID= 700291;

-- 

DECLARE
  V_COUNTER NUMBER;
BEGIN
  SELECT MAX(ID)
  INTO V_COUNTER
  FROM HRIS_EMPLOYEE_SHIFT_ASSIGN ;
  FOR employee IN
  (SELECT * FROM HRIS_EMPLOYEES WHERE STATUS ='E' AND COMPANY_ID =4
  )
  LOOP
    V_COUNTER:=V_COUNTER+1;
    INSERT
    INTO HRIS_EMPLOYEE_SHIFT_ASSIGN VALUES
      (
        employee.EMPLOYEE_ID,
        17,
        TRUNC(SYSDATE),
        NULL,
        NULL,
        'E',
        700284,
        NULL,
        '25-NOV-17',
        '30-APR-18',
        V_COUNTER
      );
  END LOOP;
END;
/
-- AUTO INSERT FISCAL_YEAR_MONTH_NO IN HRIS_MONTH_CODE
DECLARE
  V_MONTH_COUNTER        NUMBER(2,0);
  V_FISCAL_MONTH_COUNTER NUMBER(2,0);
BEGIN
  FOR fyear IN
  (SELECT DISTINCT FISCAL_YEAR_ID FROM HRIS_MONTH_CODE
  )
  LOOP
    V_MONTH_COUNTER       :=4;
    V_FISCAL_MONTH_COUNTER:=1;
    FOR months IN
    (SELECT     *
    FROM HRIS_MONTH_CODE
    WHERE FISCAL_YEAR_ID = fyear.FISCAL_YEAR_ID
    ORDER BY FROM_DATE
    )
    LOOP
      UPDATE HRIS_MONTH_CODE
      SET MONTH_NO           =V_MONTH_COUNTER,
        FISCAL_YEAR_MONTH_NO =V_FISCAL_MONTH_COUNTER
      WHERE MONTH_ID         =months.MONTH_ID;
      V_MONTH_COUNTER       :=V_MONTH_COUNTER       +1;
      V_FISCAL_MONTH_COUNTER:=V_FISCAL_MONTH_COUNTER+1;
      IF(V_MONTH_COUNTER     =13) THEN
        V_MONTH_COUNTER     :=1;
      END IF;
    END LOOP;
  END LOOP;
END;
/

-- 

-- CREATE ATTENDANCE BACKUP
DROP TABLE HRIS_ATTENDANCE_BACKUP;
CREATE TABLE HRIS_ATTENDANCE_BACKUP AS SELECT * FROM HRIS_ATTENDANCE;
DELETE FROM HRIS_ATTENDANCE;

-- RESTORE ATTENDANCE BACKUP
INSERT INTO HRIS_ATTENDANCE ((SELECT * FROM HRIS_ATTENDANCE_BACKUP) MINUS (SELECT * FROM HRIS_ATTENDANCE));
DROP TABLE HRIS_ATTENDANCE_BACKUP;

-- SUBSTITUTE LEAVE FIX QUERY
BEGIN
  FOR sub_leave IN
  (SELECT        *
  FROM
    (SELECT AD.EMPLOYEE_ID,
      AD.TOTAL_DAYS AS ACTUAL,
      LA.TOTAL_DAYS AS FACT,
      (
      CASE
        WHEN AD.TOTAL_DAYS=LA.TOTAL_DAYS
        THEN 'Y'
        ELSE 'N'
      END) AS TEST
    FROM
      (SELECT EMPLOYEE_ID,
        SUM(NO_OF_DAYS) AS TOTAL_DAYS
      FROM HRIS_EMPLOYEE_LEAVE_ADDITION
      GROUP BY EMPLOYEE_ID
      ) AD
    LEFT JOIN
      (SELECT EMPLOYEE_ID,
        TOTAL_DAYS
      FROM HRIS_EMPLOYEE_LEAVE_ASSIGN
      WHERE LEAVE_ID=8
      ) LA
    ON (AD.EMPLOYEE_ID=LA.EMPLOYEE_ID)
    )
  WHERE TEST='N'
  )
  LOOP
    UPDATE HRIS_EMPLOYEE_LEAVE_ASSIGN
    SET TOTAL_DAYS   =sub_leave.FACT
    WHERE EMPLOYEE_ID = sub_leave.EMPLOYEE_ID
    AND LEAVE_ID      = 8;
    HRIS_RECALCULATE_LEAVE(sub_leave.EMPLOYEE_ID);
  END LOOP;
END;
/

SELECT 'UPDATE HRIS_ATTENDANCE SET EMPLOYEE_ID= '||EMPLOYEE_ID||' WHERE THUMB_ID = '||ID_THUMB_ID||';' FROM HRIS_EMPLOYEES WHERE ID_THUMB_ID IN (3189,3190,3188,3088,3089); 

SELECT 'BEGIN HRIS_REATTENDANCE(''16-JUL-17'','||EMPLOYEE_ID||'); END;' FROM HRIS_EMPLOYEES WHERE ID_THUMB_ID IN (3189,3190,3188,3088,3089); 

-- ON MISSED LEAVE REQUEST NOTIFICATION
BEGIN
  FOR lr IN
  (SELECT LR.*
  FROM hris_employee_leave_request lr
  LEFT JOIN hris_notification n
  ON ( n.route LIKE ( '%'
    || lr.id
    || '%' )
  AND n.route LIKE ( '%leaveapprove%' ) )
  WHERE lr.status   = 'RQ'
  AND n.MESSAGE_ID IS NULL
  AND REQUESTED_DT  > TRUNC(SYSDATE-10)
  )
  LOOP
    INSERT
    INTO HRIS_NOTIFICATION
      (
        MESSAGE_ID,
        MESSAGE_DATETIME,
        MESSAGE_TITLE,
        MESSAGE_DESC,
        MESSAGE_FROM,
        MESSAGE_TO,
        STATUS,
        EXPIRY_TIME,
        ROUTE
      )
      VALUES
      (
        (SELECT MAX(MESSAGE_ID)+1 FROM HRIS_NOTIFICATION
        )
        ,
        sysdate,
        'Leave Request',
        'Leave Request of '
        ||
        (SELECT FULL_NAME FROM HRIS_EMPLOYEES WHERE EMPLOYEE_ID= lr.EMPLOYEE_ID
        )
        ||' from '
        ||lr.START_DATE
        ||' to '
        ||lr.end_date,
        lr.EMPLOYEE_ID,
        (SELECT RECOMMEND_BY
        FROM HRIS_RECOMMENDER_APPROVER
        WHERE EMPLOYEE_ID =lr.EMPLOYEE_ID
        ),
        'U',
        TRUNC(sysdate+14),
        '{"route":"leaveapprove","action":"view","id":"'
        ||lr.id
        ||'","role":2}'
      );
  END LOOP;
END;
/

-- JGI leave transfer query 
SELECT
    elr.*
FROM
    hris_employee_leave_request elr
    LEFT JOIN hris_employee_leave_assign ela ON (
            elr.employee_id = ela.employee_id
        AND
            elr.leave_id = ela.leave_id
    )
WHERE
    ela.employee_id IS NULL;
--   
BEGIN
  FOR elr IN
  (SELECT elr.*
  FROM hris_employee_leave_request elr
  LEFT JOIN hris_employee_leave_assign ela
  ON ( elr.employee_id   = ela.employee_id
  AND elr.leave_id       = ela.leave_id )
  WHERE ela.employee_id IS NULL
  )
  LOOP
    IF(elr.LEAVE_ID=1) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=11 WHERE ID=elr.ID;
    END IF;
    IF(elr.LEAVE_ID=2) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=12 WHERE ID=elr.ID;
    END IF;
    IF(elr.LEAVE_ID=6) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=16 WHERE ID=elr.ID;
    END IF;
    IF(elr.LEAVE_ID=11) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=1 WHERE ID=elr.ID;
    END IF;
    IF(elr.LEAVE_ID=12) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=2 WHERE ID=elr.ID;
    END IF;
    IF(elr.LEAVE_ID=16) THEN
      UPDATE HRIS_EMPLOYEE_LEAVE_REQUEST SET LEAVE_ID=6 WHERE ID=elr.ID;
    END IF;
  END LOOP;
END;
/


-- QUERY TO CHECK ATTENDANCE REQUEST NOT AFFECTED ON ATTENDANCE REPORT.
SELECT ad.*
FROM hris_attendance_request ar
JOIN hris_attendance_detail ad
ON ( ar.employee_id     = ad.employee_id
AND ar.attendance_dt    = ad.attendance_dt )
WHERE ad.overall_status = 'AB'
AND ar.status           = 'AP';
-- FIX TO UNAFFECTED ATTENDANCE REPORT ON ATTENDANCE REQUEST APPROVAL
SELECT 'BEGIN HRIS_REATTENDANCE('''
  ||AD.ATTENDANCE_DT
  ||''','
  ||AD.EMPLOYEE_ID
  ||','''
  ||AD.ATTENDANCE_DT
  ||'''); END;
/'
FROM hris_attendance_request ar
JOIN hris_attendance_detail ad
ON ( ar.employee_id     = ad.employee_id
AND ar.attendance_dt    = ad.attendance_dt )
WHERE ad.overall_status = 'AB'
AND ar.status           = 'AP';

-- CHECK ATTENDANCE REPORT THAT IS NOT EFFECTED BY TRAVEL REQUEST APPROVAL
SELECT AD.*
FROM HRIS_EMPLOYEE_TRAVEL_REQUEST ETR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (ETR.EMPLOYEE_ID=AD.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETR.FROM_DATE AND ETR."TO_DATE"))
WHERE AD.OVERALL_STATUS NOT IN ('TV','VP')
AND ETR.STATUS               ='AP';
--
-- FIX TO ATTENDANCE REPORT THAT IS NOT EFFECTED BY TRAVEL REQUEST APPROVAL
SELECT 'BEGIN HRIS_REATTENDANCE('''
  ||AD.ATTENDANCE_DT
  ||''','
  ||AD.EMPLOYEE_ID
  ||','''
  ||AD.ATTENDANCE_DT
  ||'''); END;
/'
FROM HRIS_EMPLOYEE_TRAVEL_REQUEST ETR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (ETR.EMPLOYEE_ID=AD.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETR.FROM_DATE AND ETR."TO_DATE"))
WHERE AD.OVERALL_STATUS NOT IN ('TV','VP')
AND ETR.STATUS               ='AP';

-- 

-- CHECK ATTENDANCE REPORT NOT AFFECTED ON TRAINING REQUEST APPROVAL
SELECT AD.*
FROM HRIS_EMPLOYEE_TRAINING_REQUEST ETR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (AD.EMPLOYEE_ID =ETR.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETR.START_DATE AND ETR.END_DATE))
WHERE ETR.IS_WITHIN_COMPANY ='N'
AND ETR.STATUS              ='AP'
AND AD.OVERALL_STATUS NOT  IN ('TN','TP');
-- FIX ATTENDANCE REPORT NOT AFFECTED ON TRAINING REQUEST APPROVAL
SELECT 'BEGIN HRIS_REATTENDANCE('''
  ||AD.ATTENDANCE_DT
  ||''','
  ||AD.EMPLOYEE_ID
  ||','''
  ||AD.ATTENDANCE_DT
  ||'''); END;
/'
FROM HRIS_EMPLOYEE_TRAINING_REQUEST ETR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (AD.EMPLOYEE_ID =ETR.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETR.START_DATE AND ETR.END_DATE))
WHERE ETR.IS_WITHIN_COMPANY ='N'
AND ETR.STATUS              ='AP'
AND AD.OVERALL_STATUS NOT  IN ('TN','TP');


-- CHECK IF ATTENDANCE REPORT IS NOT AFFECTED BY TRAINING ASSIGN
SELECT AD.*
FROM
  (SELECT ETA.EMPLOYEE_ID,
    TMS.START_DATE,
    TMS.END_DATE,
    TMS.IS_WITHIN_COMPANY
  FROM HRIS_EMPLOYEE_TRAINING_ASSIGN ETA
  JOIN HRIS_TRAINING_MASTER_SETUP TMS
  ON (ETA.TRAINING_ID=TMS.TRAINING_ID)
  ) ETA
LEFT JOIN HRIS_ATTENDANCE_DETAIL AD
ON (ETA.EMPLOYEE_ID=AD.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETA.START_DATE AND ETA.END_DATE))
WHERE AD.OVERALL_STATUS NOT IN ('TN','TP')
AND ETA.IS_WITHIN_COMPANY    ='N';
-- FIX IF ATTENDANCE REPORT IS NOT AFFECTED BY TRAINING ASSIGN
SELECT 'BEGIN HRIS_REATTENDANCE('''
  ||AD.ATTENDANCE_DT
  ||''','
  ||AD.EMPLOYEE_ID
  ||','''
  ||AD.ATTENDANCE_DT
  ||'''); END;
/'
FROM
  (SELECT ETA.EMPLOYEE_ID,
    TMS.START_DATE,
    TMS.END_DATE,
    TMS.IS_WITHIN_COMPANY
  FROM HRIS_EMPLOYEE_TRAINING_ASSIGN ETA
  JOIN HRIS_TRAINING_MASTER_SETUP TMS
  ON (ETA.TRAINING_ID=TMS.TRAINING_ID)
  ) ETA
LEFT JOIN HRIS_ATTENDANCE_DETAIL AD
ON (ETA.EMPLOYEE_ID=AD.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ETA.START_DATE AND ETA.END_DATE))
WHERE AD.OVERALL_STATUS NOT IN ('TN','TP')
AND ETA.IS_WITHIN_COMPANY    ='N';
            

-- CHECK IF APPROVED LEAVE IS NOT REFLECTED ON ATTENDANCE_REPORT
SELECT AD.*
FROM HRIS_EMPLOYEE_LEAVE_REQUEST ELR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (AD.EMPLOYEE_ID =ELR.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ELR.START_DATE AND ELR.END_DATE))
WHERE ELR.STATUS           ='AP'
AND AD.OVERALL_STATUS NOT IN ('LV','LP');
-- FIX IF APPROVED LEAVE IS NOT REFLECTED ON ATTENDANCE_REPORT
SELECT 'BEGIN HRIS_REATTENDANCE('''
  ||AD.ATTENDANCE_DT
  ||''','
  ||AD.EMPLOYEE_ID
  ||','''
  ||AD.ATTENDANCE_DT
  ||'''); END;
/'
FROM HRIS_EMPLOYEE_LEAVE_REQUEST ELR
JOIN HRIS_ATTENDANCE_DETAIL AD
ON (AD.EMPLOYEE_ID =ELR.EMPLOYEE_ID
AND (AD.ATTENDANCE_DT BETWEEN ELR.START_DATE AND ELR.END_DATE))
WHERE ELR.STATUS           ='AP'
AND AD.OVERALL_STATUS NOT IN ('LV','LP');

-- MANAGE RESIGNED STAFFS
UPDATE HRIS_EMPLOYEES
SET SERVICE_TYPE_ID=
  (SELECT SERVICE_TYPE_ID FROM HRIS_SERVICE_TYPES WHERE TYPE ='RESIGNED'
  ) ,
  STATUS      ='E',
  RETIRED_FLAG='Y'
WHERE STATUS  ='D';

