DARTS : Check daily extracts status

----- Check the queue last x runs
select x.prcsname,
       x.prcsinstance,
       x.prcsjobname,
       x.prcstype,
       x.runtime,
       extract(hour from x.Timex) Time_Hr,
       extract(minute from x.Timex) Time_Min,
       x.oprid,
       x.runcntlid,
       x.xlatlongname as RUN_STATUS
  From (select a.prcsname,
               a.prcsinstance,
               a.prcsjobname,
               a.prcstype,
               c.enddttm - c.begindttm as timex,
               a.mainjobname,
               to_char(a.rundttm, 'mm/dd/yyyy  hh:mi:ssam') as runtime,
               a.oprid,
               a.runstatus,
               a.runcntlid,
               bb.xlatlongname,
               rank() over(partition by a.prcsname order by a.rundttm desc) as rank
          from sysadm.PSPRCSQUE a,
               (select boo.fieldvalue, boo.xlatlongname
                  from sysadm.psxlatitem boo
                 where boo.fieldname = 'RUNSTATUS'
                   and boo.eff_status = 'A'
                   and boo.effdt =
                       (select max(berry.effdt)
                          from sysadm.psxlatitem berry
                         where berry.fieldname = boo.fieldname
                           and berry.fieldvalue = boo.fieldvalue)) bb,
               sysadm.psprcsrqst c
         where a.runstatus = bb.fieldvalue(+)
           and a.prcsname = c.prcsname
           and a.prcsinstance = c.prcsinstance
           and a.prcsname in ('UM_CSPA5',
                              'UM_CSP4',
                              'UM_CS5',
                              'UM_CS4',
                              'UMSTUEXT',
                              'UMSTUARC',
                              'UM_STUCRSTAB',
                              'UMFINAID',
                              'UMAPPEXT',
                              'UMPROEXT',
                              'UMAPPARC',
                              'UMSTUARC',
                              'UMCLSSCH',
                              'UM_APPACAD_P',
                              'UMEMPARC',
                              'UM_F_EMPLOYE',
                              'UMFAIDBF',
                              'UMFAIDDT',
                              'UM_FINA',
                              'UM_FINA2',
                              'UM_CS13')
        --      and a.rundttm = (select max(b.rundttm) from psprcsque b where b.prcsname = a.prcsname)
        ) x
 where x.rank <= 2
 order by x.prcsname, x.rank desc;
 
------ Check Extract & Archives in my control table
select x.processname,
       x.start_date,
       x.start_time,
       x.oprid,
       x.run_cntl_id,
       x.as_of_date,
       x.um_begin_term,
       x.um_end_term,
       x.flag1,
       x.flag2,
       x.flag3,
       x.runstatus,
       x.end_date,
       x.end_time,
       x.row_count,
       x.row_count2,
       x.rank
  from (select a.processname,
               to_char(a.startdatetime, 'mm/dd/yyyy') as start_date,
               to_char(a.startdatetime, 'hh:mi:ssam') as start_time,
               a.oprid,
               a.run_cntl_id,
               a.as_of_date,
               a.um_begin_term,
               a.um_end_term,
               a.flag1,
               a.flag2,
               a.flag3,
               decode(a.runstatus,
                      '9',
                      'Success',
                      '7',
                      'Processing',
                      '3',
                      'Error',
                      '??') as runstatus,
               to_char(a.enddatetime, 'mm/dd/yyyy') as end_date,
               to_char(a.enddatetime, 'hh:mi:ssam') as end_time,
               a.row_count,
               a.row_count2,
               rank() over(partition by a.processname order by a.processname, a.startdatetime desc) as rank
          from sysadm.ps_um_extract_cntl a
         where a.startdatetime > sysdate - 62) x
 where x.rank <= 2
 order by x.processname, x.rank desc;