CREATE OR REPLACE  PROCEDURE 
    "SYSTEM"."SP_EQPT_HEAD_STATUS_HISTORY" (TableName OUT NOCOPY 
    VARCHAR2)  is
V_UNSCHEDULE_DOWN   NUMBER(9);
V_SCHEDULE_DOWN     NUMBER(9);
V_UNSCHEDULE_WAIT   NUMBER(9);
V_UNSCHEDULE_REPAIR NUMBER(9);
V_SCHEDULE_REPAIR   NUMBER(9);
V_SCHEDULE_WAIT     NUMBER(9);
V_STANDBY           NUMBER(9);
V_ENGINEERING       NUMBER(9);
V_PRODUCTIVE        NUMBER(9);
V_NON_SCHEDULE      NUMBER(9);
V_DAY_RRN           NUMBER(15);

V_RECORD_NUM        NUMBER(8);   --FOR CHECK DUPLICATE KEY USING
-- V_DAY_COUNT         VARCHAR2(32);--FOR CURSOR C_EQPTDAY USING
-- V_MAX_DAYRRN        NUMBER(15);  --FOR GET MAX DAYRRN FROM EQUIPMENT_STATUS_HEAD_HISTORY
TP_TIME             DATE;

V_START_TIME   DATE;
V_END_TIME   DATE;

V_EQPT_EVENT_COUNT   NUMBER(32);

CURSOR C_EQPTEVENT IS
SELECT E_V.EQUIPMENT_RRN,E_M.EQUIPMENT_ID,E_M.FACILITY_RRN,E_M.FACILITY_ID,
       E_V.EVENT_ID,E_V.TRANS_ID,E_V.STATUS_START_TIMESTAMP,
       E_V.STATUS_END_TIMESTAMP,E_V.STATUS_KEEP_TIME,E_V.EQUIPMENT_STATUS1
FROM EQUIPMENT_EVENT_HISTORY E_V,EQPT_SEQ E_S,EQUIPMENT_MASTER E_M
WHERE E_V.EQUIPMENT_RRN=E_S.EQPT_RRN
AND   E_V.HIST_SEQ>E_S.HIST_SEQ
AND E_V.EQUIPMENT_RRN=E_M.EQUIPMENT_RRN(+)
UNION
SELECT E_V.EQUIPMENT_RRN,E_M.EQUIPMENT_ID,E_M.FACILITY_RRN,E_M.FACILITY_ID,
       E_V.EVENT_ID,E_V.TRANS_ID,E_V.STATUS_START_TIMESTAMP,
       E_V.STATUS_END_TIMESTAMP,E_V.STATUS_KEEP_TIME,E_V.EQUIPMENT_STATUS1
FROM EQUIPMENT_EVENT_HISTORY E_V,EQUIPMENT_MASTER E_M
WHERE E_V.EQUIPMENT_RRN NOT IN (SELECT EQPT_RRN FROM EQPT_SEQ)
AND E_V.EQUIPMENT_RRN=E_M.EQUIPMENT_RRN(+);

begin
  FOR C_EQPT IN C_EQPTEVENT LOOP
      V_UNSCHEDULE_DOWN:=0;
      V_SCHEDULE_DOWN:=0;
      V_UNSCHEDULE_WAIT:=0;
      V_UNSCHEDULE_REPAIR:=0;
      V_SCHEDULE_REPAIR:=0;
      V_SCHEDULE_WAIT:=0;
      V_STANDBY:=0;
      V_ENGINEERING:=0;
      V_PRODUCTIVE:=0;
      V_NON_SCHEDULE:=0;
      V_START_TIME:=C_EQPT.STATUS_START_TIMESTAMP;
      V_END_TIME:=C_EQPT.STATUS_END_TIMESTAMP;
      while TO_CHAR(V_END_TIME,'YYYY/MM/DD') >TO_CHAR(V_START_TIME,'YYYY/MM/DD')
          loop
          TP_TIME:=TO_DATE(TO_CHAR(V_START_TIME,'YYYY/MM/DD'),'YYYY/MM/DD');
          --FOR UNSCHEDULE WAIT TIME
          IF C_EQPT.EQUIPMENT_STATUS1='CALIBRATE'  OR C_EQPT.EQUIPMENT_STATUS1='AWAITG CALIB'
                                                   OR C_EQPT.EQUIPMENT_STATUS1='AWAITG REPR'  THEN
             V_UNSCHEDULE_WAIT:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;   --END OF FOR UNSCHEDULE WAIT TIME
          --FOR UNSCHEDULE REPAIR TIME
          IF C_EQPT.EQUIPMENT_STATUS1='REPAIR'  THEN
             V_UNSCHEDULE_REPAIR:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF; -- END OF FOR UNSCHEDULE REPAIR TIME
          --FOR SCHEDULE REPAIR TIME
          IF C_EQPT.EQUIPMENT_STATUS1='PM'  THEN
             V_SCHEDULE_REPAIR:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF; -- END OF FOR SCHEDULE REPAIR TIME
          --FOR SCHEDULE_WAIT_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='AWAITG PM'  THEN
             V_SCHEDULE_WAIT:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;-- END OF SCHEDULE_WAIT_TIME
          --FOR STANDBY_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='HOLD' OR C_EQPT.EQUIPMENT_STATUS1='IDLE' 
                                             OR C_EQPT.EQUIPMENT_STATUS1='READY'  THEN
             V_STANDBY:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;--END OF STANDBY_TIME
          --FOR ENGINEERING_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='ENG'   THEN
             V_ENGINEERING:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;--END OF FOR ENGINEERING_TIME
          --FOR PRODUCTIVE_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='PROD'   THEN
             V_PRODUCTIVE:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;--END OF FOR PRODUCTIVE_TIME
          --FOR NON_SCHEDULE_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='INSTALLED'  OR  C_EQPT.EQUIPMENT_STATUS1='ACCEPTANCE' THEN
             V_NON_SCHEDULE:=(TP_TIME+1-V_START_TIME)*24*3600;
          END IF;--END OF FOR NON_SCHEDULE_TIME
          --FOR UNSCHEDULE_DOWN_TIMES
          SELECT COUNT(*) into V_UNSCHEDULE_DOWN FROM EQUIPMENT_EVENT_HISTORY 
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN 
          AND
          STATUS_START_TIMESTAMP>=TP_TIME
          AND
          STATUS_START_TIMESTAMP<=TP_TIME+1
          AND
          EVENT_ID='DOWN';
          --FOR SCHEDULE_DOWN_TIMES
          SELECT COUNT(*) into V_SCHEDULE_DOWN FROM EQUIPMENT_EVENT_HISTORY 
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN 
          AND
          STATUS_START_TIMESTAMP>=TP_TIME
          AND
          STATUS_START_TIMESTAMP<=TP_TIME+1
          AND
          EVENT_ID='SCHD PM';
          --FOR DAY_RRN
          SELECT DAY_RRN INTO V_DAY_RRN FROM DAY_MASTER
          WHERE CALENDAR_DATE=TP_TIME
          AND FACILITY_RRN=C_EQPT.FACILITY_RRN;
          --CHECK IF HAVE DUPLICATE KEY
          SELECT COUNT(*) INTO V_RECORD_NUM FROM EQUIPMENT_STATUS_HEAD_HISTORY
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN  AND DAY_RRN=V_DAY_RRN;
          --FOR UPDATE TABLE 
          IF V_RECORD_NUM=0 THEN  
              --INSERT INTO EQUIPMENT_STATUS_HEAD_HISTORY
              INSERT INTO EQUIPMENT_STATUS_HEAD_HISTORY
              (EQUIPMENT_RRN,EQUIPMENT_ID,DAY_RRN,CALENDAR_DATE,FACILITY_RRN,FACILITY_ID
              ,UNSCHEDULE_DOWN_TIMES,SCHEDULE_DOWN_TIMES,UNSCHEDULE_WAIT_TIME
              ,UNSCHEDULE_REPAIR_TIME,SCHEDULE_REPAIR_TIME,SCHEDULE_WAIT_TIME
              ,STANDBY_TIME,ENGINEERING_TIME,PRODUCTIVE_TIME,NON_SCHEDULE_TIME)
              VALUES(C_EQPT.EQUIPMENT_RRN,C_EQPT.EQUIPMENT_ID,V_DAY_RRN,TP_TIME,
                     C_EQPT.FACILITY_RRN,C_EQPT.FACILITY_ID,
                     V_UNSCHEDULE_DOWN,V_SCHEDULE_DOWN,V_UNSCHEDULE_WAIT,
                     V_UNSCHEDULE_REPAIR,V_SCHEDULE_REPAIR,V_SCHEDULE_WAIT,
                     V_STANDBY,V_ENGINEERING,V_PRODUCTIVE,V_NON_SCHEDULE);
          ELSE
              UPDATE EQUIPMENT_STATUS_HEAD_HISTORY
              SET    --UNSCHEDULE_DOWN_TIMES=UNSCHEDULE_DOWN_TIMES+V_UNSCHEDULE_DOWN,
                     UNSCHEDULE_DOWN_TIMES=V_UNSCHEDULE_DOWN,
                     --SCHEDULE_DOWN_TIMES=SCHEDULE_DOWN_TIMES+V_SCHEDULE_DOWN,
                     SCHEDULE_DOWN_TIMES=V_SCHEDULE_DOWN,
                     UNSCHEDULE_WAIT_TIME=UNSCHEDULE_WAIT_TIME+V_UNSCHEDULE_WAIT,
                     UNSCHEDULE_REPAIR_TIME=UNSCHEDULE_REPAIR_TIME+V_UNSCHEDULE_REPAIR,
                     SCHEDULE_REPAIR_TIME=SCHEDULE_REPAIR_TIME+V_SCHEDULE_REPAIR,
                     SCHEDULE_WAIT_TIME=SCHEDULE_WAIT_TIME+V_SCHEDULE_WAIT,
                     STANDBY_TIME=STANDBY_TIME+V_STANDBY,
                     ENGINEERING_TIME=ENGINEERING_TIME+V_ENGINEERING,
                     PRODUCTIVE_TIME=PRODUCTIVE_TIME+V_PRODUCTIVE,
                     NON_SCHEDULE_TIME=NON_SCHEDULE_TIME+V_NON_SCHEDULE
              WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN  AND DAY_RRN=V_DAY_RRN;
          END IF;--END FOR UPDATE TABLE
          --CHANGE THE VALUE OF V_START_TIME
          V_START_TIME:=TO_DATE(TO_CHAR(V_START_TIME,'YYYY/MM/DD'),'YYYY/MM/DD')+1;
     end loop;    
          
           
    IF TO_CHAR(V_END_TIME,'YYYY/MM/DD')=TO_CHAR(V_START_TIME,'YYYY/MM/DD')THEN
          TP_TIME:=TO_DATE(TO_CHAR(V_START_TIME,'YYYY/MM/DD'),'YYYY/MM/DD');
          --FOR UNSCHEDULE WAIT TIME
          IF C_EQPT.EQUIPMENT_STATUS1='CALIBRATE'  OR C_EQPT.EQUIPMENT_STATUS1='AWAITG CALIB'
                                                   OR C_EQPT.EQUIPMENT_STATUS1='AWAITG REPR'  THEN
             V_UNSCHEDULE_WAIT:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;   --END OF FOR UNSCHEDULE WAIT TIME
          --FOR UNSCHEDULE REPAIR TIME
          IF C_EQPT.EQUIPMENT_STATUS1='REPAIR'  THEN
             V_UNSCHEDULE_REPAIR:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF; -- END OF FOR UNSCHEDULE REPAIR TIME
          --FOR SCHEDULE REPAIR TIME
          IF C_EQPT.EQUIPMENT_STATUS1='PM'  THEN
             V_SCHEDULE_REPAIR:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF; -- END OF FOR SCHEDULE REPAIR TIME
          --FOR SCHEDULE_WAIT_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='AWAITG PM'  THEN
             V_SCHEDULE_WAIT:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;-- END OF SCHEDULE_WAIT_TIME
          --FOR STANDBY_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='HOLD' OR C_EQPT.EQUIPMENT_STATUS1='IDLE' 
                                             OR C_EQPT.EQUIPMENT_STATUS1='READY'  THEN
             V_STANDBY:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;--END OF STANDBY_TIME
          --FOR ENGINEERING_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='ENG'   THEN
             V_ENGINEERING:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;--END OF FOR ENGINEERING_TIME
          --FOR PRODUCTIVE_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='PROD'   THEN
             V_PRODUCTIVE:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;--END OF FOR PRODUCTIVE_TIME
          --FOR NON_SCHEDULE_TIME
          IF C_EQPT.EQUIPMENT_STATUS1='INSTALLED'  OR  C_EQPT.EQUIPMENT_STATUS1='ACCEPTANCE' THEN
             V_NON_SCHEDULE:=(V_END_TIME-V_START_TIME)*24*3600;
          END IF;--END OF FOR NON_SCHEDULE_TIME
          --FOR UNSCHEDULE_DOWN_TIMES
          SELECT COUNT(*) into V_UNSCHEDULE_DOWN FROM EQUIPMENT_EVENT_HISTORY 
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN 
          AND
          STATUS_START_TIMESTAMP>=TP_TIME
          AND
          STATUS_START_TIMESTAMP<=TP_TIME+1
          AND
          EVENT_ID='DOWN';
          --FOR SCHEDULE_DOWN_TIMES
          SELECT COUNT(*) into V_SCHEDULE_DOWN FROM EQUIPMENT_EVENT_HISTORY 
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN 
          AND
          STATUS_START_TIMESTAMP>=TP_TIME
          AND
          STATUS_START_TIMESTAMP<=TP_TIME+1
          AND
          EVENT_ID='SCHD PM';
          --FOR DAY_RRN
          SELECT DAY_RRN INTO V_DAY_RRN FROM DAY_MASTER
          WHERE CALENDAR_DATE=TP_TIME
          AND FACILITY_RRN=C_EQPT.FACILITY_RRN;
          --CHECK IF HAVE DUPLICATE KEY
          SELECT COUNT(*) INTO V_RECORD_NUM FROM EQUIPMENT_STATUS_HEAD_HISTORY
          WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN  AND DAY_RRN=V_DAY_RRN;
          IF V_RECORD_NUM=0 THEN  
              --INSERT INTO EQUIPMENT_STATUS_HEAD_HISTORY
              INSERT INTO EQUIPMENT_STATUS_HEAD_HISTORY
              (EQUIPMENT_RRN,EQUIPMENT_ID,DAY_RRN,CALENDAR_DATE,FACILITY_RRN,FACILITY_ID
              ,UNSCHEDULE_DOWN_TIMES,SCHEDULE_DOWN_TIMES,UNSCHEDULE_WAIT_TIME
              ,UNSCHEDULE_REPAIR_TIME,SCHEDULE_REPAIR_TIME,SCHEDULE_WAIT_TIME
              ,STANDBY_TIME,ENGINEERING_TIME,PRODUCTIVE_TIME,NON_SCHEDULE_TIME)
              VALUES(C_EQPT.EQUIPMENT_RRN,C_EQPT.EQUIPMENT_ID,V_DAY_RRN,TP_TIME,
                     C_EQPT.FACILITY_RRN,C_EQPT.FACILITY_ID,
                     V_UNSCHEDULE_DOWN,V_SCHEDULE_DOWN,V_UNSCHEDULE_WAIT,
                     V_UNSCHEDULE_REPAIR,V_SCHEDULE_REPAIR,V_SCHEDULE_WAIT,
                     V_STANDBY,V_ENGINEERING,V_PRODUCTIVE,V_NON_SCHEDULE);
          ELSE
              UPDATE EQUIPMENT_STATUS_HEAD_HISTORY
              SET    --UNSCHEDULE_DOWN_TIMES=UNSCHEDULE_DOWN_TIMES+V_UNSCHEDULE_DOWN,
                     UNSCHEDULE_DOWN_TIMES=V_UNSCHEDULE_DOWN,
                     --SCHEDULE_DOWN_TIMES=SCHEDULE_DOWN_TIMES+V_SCHEDULE_DOWN,
                     SCHEDULE_DOWN_TIMES=V_SCHEDULE_DOWN,
                     UNSCHEDULE_WAIT_TIME=UNSCHEDULE_WAIT_TIME+V_UNSCHEDULE_WAIT,
                     UNSCHEDULE_REPAIR_TIME=UNSCHEDULE_REPAIR_TIME+V_UNSCHEDULE_REPAIR,
                     SCHEDULE_REPAIR_TIME=SCHEDULE_REPAIR_TIME+V_SCHEDULE_REPAIR,
                     SCHEDULE_WAIT_TIME=SCHEDULE_WAIT_TIME+V_SCHEDULE_WAIT,
                     STANDBY_TIME=STANDBY_TIME+V_STANDBY,
                     ENGINEERING_TIME=ENGINEERING_TIME+V_ENGINEERING,
                     PRODUCTIVE_TIME=PRODUCTIVE_TIME+V_PRODUCTIVE,
                     NON_SCHEDULE_TIME=NON_SCHEDULE_TIME+V_NON_SCHEDULE
              WHERE EQUIPMENT_RRN=C_EQPT.EQUIPMENT_RRN  AND DAY_RRN=V_DAY_RRN;
          END IF;                                             
      END IF;
  END LOOP;
  --GET THE LATTEST HIST_SEQ OF TABLE EQUIPMENT_EVENT_HISTORY
  V_EQPT_EVENT_COUNT:=0;
  DELETE FROM EQPT_SEQ;
  SELECT COUNT(*) INTO V_EQPT_EVENT_COUNT FROM EQUIPMENT_EVENT_HISTORY;
  IF V_EQPT_EVENT_COUNT>0 THEN
    INSERT INTO EQPT_SEQ(EQPT_RRN,HIST_SEQ)
    SELECT EQUIPMENT_RRN,MAX(HIST_SEQ) FROM EQUIPMENT_EVENT_HISTORY
    GROUP BY EQUIPMENT_RRN; 
  END IF;
  
end SP_EQPT_HEAD_STATUS_HISTORY;