CREATE TABLE JOB_EXECUTION 
(
RUN_DATE DATE,
RUN_ORDER NUMBER,
RUN_DIV VARCHAR2(50),
RUN_TYPE VARCHAR2(50),
RUN_FMT  VARCHAR2(50),
PICKUP_STATUS  VARCHAR2(50),
START_TIME DATE,
END_TIME DATE,
REMARKS VARCHAR2(4000)
);

INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT  ,PICKUP_STATUS  ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50.
AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52.
AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57.
FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59.
ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
METRICs PROCESS: as of @ 26-Sep-2013 10:39:59.
METRIC_7 PROCESS: as of @ 26-Sep-2013 10:52:39.
METRIC_4 PROCESS: as of @ 26-Sep-2013 10:52:40.
METRIC_1 PROCESS: as of @ 26-Sep-2013 10:53:17.
METRIC_10 PROCESS: as of @ 26-Sep-2013 10:53:21.
METRIC_5 PROCESS: as of @ 26-Sep-2013 11:03:19.
METRIC_8 PROCESS: as of @ 26-Sep-2013 11:03:20.
METRIC_2 PROCESS: as of @ 26-Sep-2013 11:03:29.
METRIC_11 PROCESS: as of @ 26-Sep-2013 11:04:01.
METRIC_6 PROCESS: as of @ 26-Sep-2013 11:13:46.
METRIC_9 PROCESS: as of @ 26-Sep-2013 11:13:46.
METRIC_3 PROCESS: as of @ 26-Sep-2013 11:14:10.
METRIC_12 PROCESS: as of @ 26-Sep-2013 11:14:21.
BENCHMARKs PROCESS: as of @ 26-Sep-2013 11:14:26.
BENCHMARKs PROCESS: as of @ 26-Sep-2013 11:20:35.
REPORTING_6 PROCESS: as of @ 26-Sep-2013 11:24:47.
REPORTING_1 PROCESS: as of @ 26-Sep-2013 11:26:12.
FINISHED: as of @ 26-Sep-2013 11:26:21"');



INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT  ,PICKUP_STATUS  ,START_TIME ,END_TIME ,REMARKS) VALUES
(sysdate,1,'BE','FILE-HFM','MAR_FCST-2012','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 24-Sep-2013 04:04:19.
AFM-Load END PROCESS: as of @ 24-Sep-2013 04:04:20.
AUTO-LOAD RUNNING :as of @ 24-Sep-2013 04:04:27.
FILEs-LOAD PROCESS: as of @ 24-Sep-2013 04:04:29.
ALLOCATIONs PROCESS: as of @ 24-Sep-2013 04:04:58.
METRICs PROCESS: as of @ 24-Sep-2013 04:11:22.
METRIC_4 PROCESS: as of @ 24-Sep-2013 04:13:35.
METRIC_4 PROCESS: as of @ 24-Sep-2013 04:13:35.
METRIC_10 PROCESS: as of @ 24-Sep-2013 04:13:36.
METRIC_7 PROCESS: as of @ 24-Sep-2013 04:13:59.
METRIC_5 PROCESS: as of @ 24-Sep-2013 04:15:27.
METRIC_2 PROCESS: as of @ 24-Sep-2013 04:15:27.
METRIC_11 PROCESS: as of @ 24-Sep-2013 04:15:30.
METRIC_8 PROCESS: as of @ 24-Sep-2013 04:16:15.
METRIC_3 PROCESS: as of @ 24-Sep-2013 04:17:12.
METRIC_6 PROCESS: as of @ 24-Sep-2013 04:17:13.
METRIC_12 PROCESS: as of @ 24-Sep-2013 04:17:22.
METRIC_9 PROCESS: as of @ 24-Sep-2013 04:18:09.
BENCHMARKs PROCESS: as of @ 24-Sep-2013 04:18:33.
BENCHMARKs PROCESS: as of @ 24-Sep-2013 04:20:43.
REPORTING_6 PROCESS: as of @ 24-Sep-2013 04:23:03.
REPORTING_1 PROCESS: as of @ 24-Sep-2013 04:23:06.
FINISHED: as of @ 24-Sep-2013 04:23:17"');

WITH reporting_info AS (
      SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME,
      REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD,
      REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
      REPLACE(substr(substr(remarks,instr(remarks,'METRICs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'METRICs PROCESS: as of @ ')),chr(10))-2 ),'METRICs PROCESS: as of @ ','') METRIC,
      replace(substr(substr(remarks,instr(remarks,'BENCHMARKs PROCESS: as of @ ',1,2)), 0,instr(substr(remarks,instr(remarks,'BENCHMARKs PROCESS: as of @ ',1,2)),chr(10))-2 ),'BENCHMARKs PROCESS: as of @ ','') BENCHMARK,
      REPLACE(substr(remarks,instr(remarks,'FINISHED: as of @ ')),'FINISHED: as of @ ','')  REPORTING
      FROM JOB_EXECUTION b 
      WHERE B.PICKUP_STATUS = 'FINISHED'
      AND B.RUN_DIV IN ('AE','BE','PS')
      AND TRUNC(B.RUN_DATE) >= TRUNC(SYSDATE-500)
      --AND b.run_type = 'FILE-HFM'
)
SELECT R.RUN_DIV,SUBSTR(R.RUN_FMT,0,INSTR(R.RUN_FMT,'-',1)-1) AS FMT
,SUBSTR(R.RUN_FMT,INSTR(R.RUN_FMT,'-',1)+1) AS YEAR
,R.BATCH_ID,R.BATCH_START_TIME
, R.BATCH_END_TIME,
ROUND(((TO_DATE(R.ALLOCATION,'dd-Mon-yyyy hh24:mi:ss') -  TO_DATE(FILE_LOAD,'dd-mon-yyyy hh24:mi:ss') )*1440),1) UNALLOCATED,
round(((to_date(r.METRIC,'dd-Mon-yyyy hh24:mi:ss') -  to_date(ALLOCATION,'dd-mon-yyyy hh24:mi:ss') )*1440),1) ALLOCATION,
round(((to_date(r.BENCHMARK,'dd-Mon-yyyy hh24:mi:ss') -  to_date(METRIC,'dd-mon-yyyy hh24:mi:ss') )*1440),1) METRIC,
round(((to_date(r.REPORTING,'dd-Mon-yyyy hh24:mi:ss') -  to_date(BENCHMARK,'dd-mon-yyyy hh24:mi:ss') )*1440),1) REPORTING,
round(((to_date(r.REPORTING,'dd-Mon-yyyy hh24:mi:ss') -  to_date(FILE_LOAD,'dd-mon-yyyy hh24:mi:ss') )*1440),1) OVER_ALL_TIME_TAKEN*/
from reporting_info r
WHERE 
R.RUN_TYPE = 'FILE-HFM'
AND r.benchmark NOT LIKE 'Load Process%' AND r.benchmark NOT LIKE 'Has been%';