Custom Report in Application Manager 8.0

[Anon Anon]

I am trying to create a custom report that will show detailed information for active scheduled jobs/process flows in Application Manager 8.0.  I have found some reports that display the active scheduled jobs/process flows, but they only provide the schedule name and next run date for detail.  We have several jobs/process flows that have schedules named the exact same, but are different.  I would like my custom report to display the active scheduled jobs/process flows, but also show the frequency information of the job/process flow's schedule (i.e. days of the week the job runs, does it run every so many minutes, hours, days, weeks, etc.).  The reports I have found that list the active scheduled jobs/process flows use the am_schedules table.  I have found that the table aw_module_sched appears to have the detailed frequency information for schedules, but I cannot find the proper table/fields that will allow me to join this information correctly to the am_schedules information.  Does anyone know how to create a custom report in Application Manager 8.0 that will provide the data I'm attempting to pull? If so, what tables and fields would need to be used in the SQL of the custom report? Thanks for any help any one can provide!

[Jessica Isaacson]

Our AM instance has been shut down for a while, so I can't test with good data. But I stole the following from an old report, which seems to meet your needs pretty well (provided you are also using Oracle?).
Notes:
- I am not sure if aw_multi_start is still relevant in v8; we used it in v6. Also I don't remember how "start_time" works... seconds after midnight maybe? But if you have next_datetime you probably dont' need those.
- I included the important column aw_holiday_grp, which indicates if any calendars are used. If so, you'd have to join on the holiday tables to get specifics for them.

SELECT jt.so_module object_name, sch.aw_sch_name sched_name,
  to_char(sch.aw_next_datetime,'MM/DD/YYYY HH24:MI') next_datetime, 
  ROUND((sch.aw_next_datetime - sysdate)*24*60) mins_til_next, 
  sch.aw_next_rundate next_run_date, sch.aw_start_time start_time, sch.aw_sch_start sched_start_date,
  sch.aw_last_rundate sched_last_run_date, 
  sch.aw_days_of_week days_of_week, sch.aw_sch_interval sched_interval_every, 
  DECODE(sch.aw_sch_units, -5, 'MINUTES', -4, 'HOURS', -3, 'DAYS', -2, 'WEEKS', -1, 'MONTHS', 'OTHER') interval_descr,
  sch.aw_multi_start, aw_holiday_grp
FROM aw_module_sched sch, so_job_table jt
WHERE sch.aw_job_seq = jt.so_job_seq
AND sch.aw_sch_end   > sysdate
ORDER BY so_module, aw_next_rundate

[Anon Anon]

Thanks! Sorry for such a late response.  I obviously do not visit the community site often.  We are also on Oracle and this looks like it will be very helpful.  Much appreciated!

[Pat Richards]

to exclude inactive schedules from the results, add the following clause :

and sch.aw_active='Y'