Automic Workload Automation

we made this a little more complex because we often have user requesting information about how their programs are scheduled.
Query pulls data related to potential period objects being used together with schedules and also data related to workflows and schedules being actually active/inactive at header level.
Of course, you should change a few things like the schema name, client number and possibly workflow name

Hope this helps

SELECT zz.JPP_OBJECT WORKFLOW,
       zz.SCHEDULE,
       zz.START_TIME,
       zz.Exceptions,
       zz.DAYS,
       zz.WF_STATUS_IN_SCHD,
       DECODE (xx.PERIOD, NULL, 'No period', xx.PERIOD) PERIOD,
       DECODE (pp.oh_inactive,  1, 'Inactive',  0, 'Active') WF_Active,
       zz.Schd_Status
  FROM (  SELECT aa.jpp_oh_idnr,
                 aa.jpp_object,
                 bb.oh_name Schedule,
                 REGEXP_REPLACE (
                    LISTAGG (SUBSTR (aa.jpp_erlststtime, 3), ', ')
                       WITHIN GROUP (ORDER BY aa.jpp_lnr DESC),
                    '([^, ]*)(, \1)+($|, )',
                    '\1\3')
                    Start_Time,
                 DECODE (aa.jpp_cctype,
                         3, 'Does not run on below days',
                         2, 'Runs on below days',
                         aa.jpp_cctype)
                    Exceptions,
                 NVL (
                    REGEXP_REPLACE (
                       LISTAGG (ee.calendar, ', ')
                          WITHIN GROUP (ORDER BY ee.JPPC_JPP_LNR DESC),
                       '([^, ]*)(, \1)+($|, )',
                       '\1\3'),
                    'DAYS')
                    DAYS,
                 DECODE (bb.oh_inactive,
                         '0', 'Active',
                         '1', 'Inactive',
                         bb.oh_inactive)
                    Schd_Status,
                 DECODE (aa.jpp_active,
                         '1', 'Active',
                         '0', 'Inactive',
                         aa.jpp_active)
                    WF_Status_in_Schd
            FROM uc4db.OH bb,
                 uc4db.OSA cc,
                 uc4db.JPP aa
                 LEFT JOIN
                 (SELECT jppc_calekeyname  AS calendar,
                         jppc_oh_idnr,
                         jppc_jpp_lnr
                    FROM uc4db.jppc) ee
                    ON     ee.jppc_oh_idnr = aa.jpp_oh_idnr
                       AND ee.jppc_jpp_lnr = aa.jpp_lnr
           WHERE     aa.jpp_oh_idnr = bb.oh_idnr
                 AND cc.OSA_OH_IDNR = bb.OH_IDNR
                 AND bb.oh_client = 9012                                             -- set client where you do the search
                 AND jpp_object like 'JOBP.RMT_%'                                    -- set workflow name
        GROUP BY aa.jpp_oh_idnr,
                 aa.jpp_object,
                 bb.oh_name,
                 bb.oh_inactive,
                 aa.jpp_active,
                 aa.jpp_cctype
        ORDER BY aa.jpp_object) zz
       LEFT JOIN (SELECT oh_name, oca_value AS PERIOD
                    FROM uc4db.oh, uc4db.oca
                   WHERE OH_idnr = oca_oh_idnr AND oh_client = 9012) xx             -- set client where you do the search
          ON zz.JPP_OBJECT = xx.oh_name
       JOIN uc4db.oh pp ON zz.JPP_OBJECT = pp.oh_name
 WHERE pp.oh_client = 9012;                                                         -- set client where you do the search​

Original Message:
Sent: 11-07-2017 11:19 AM
From: Pete Wirfs
Subject: What Table in Automic DB contains a JSCH(Schedule) Calendar details

My query was only designed to report JSCH schedules.