Query to find scheduled concurrent programs

1SELECT fcr.request_id,
2            fcpt.user_concurrent_program_name
3         || NVL2 (fcr.description, ' (' || fcr.description || ')', NULL)
4            conc_prog,
5         fu.user_name                                       requestor,
6         fu.description                                     requested_by,
7         fu.email_address,
8         frt.responsibility_name                            requested_by_resp,
9         TRIM (fl.meaning)                                  status,
10         fcr.phase_code,
11         fcr.status_code,
12         fcr.argument_text                                  "PARAMETERS",
13         '------>'                                          dates,
14         TO_CHAR (fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,
15         TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
16            requested_start,
17         TO_CHAR ( (fcr.requested_start_date), 'HH24:MI:SS') start_time,
18         '------>'                                          holds,
19         DECODE (fcr.hold_flag,  'Y', 'Yes',  'N', 'No')    on_hold,
20         CASE WHEN fcr.hold_flag = 'Y' THEN SUBSTR (u2.description, 0, 40) END
21            last_update_by,
22         CASE WHEN fcr.hold_flag = 'Y' THEN fcr.last_update_date END
23            last_update_date,
24         '------>'                                          prints,
25         fcr.number_of_copies                               print_count,
26         fcr.printer,
27         fcr.print_style,
28         '------>'                                          schedule,
29         fcr.increment_dates,
30         CASE
31            WHEN fcrc.CLASS_INFO IS NULL
32            THEN
33                  'Yes: '
34               || TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
35            ELSE
36               'n/a'
37         END
38            run_once,
39         CASE
40            WHEN fcrc.class_type = 'P'
41            THEN
42                  'Repeat every '
43               || SUBSTR (fcrc.class_info, 1, INSTR (fcrc.class_info, ':') - 1)
44               || DECODE (SUBSTR (fcrc.class_info,
45                                    INSTR (fcrc.class_info,
46                                           ':',
47                                           1,
48                                           1)
49                                  + 1,
50                                  1),
51                          'N', ' minutes',
52                          'M', ' months',
53                          'H', ' hours',
54                          'D', ' days')
55               || DECODE (SUBSTR (fcrc.class_info,
56                                    INSTR (fcrc.class_info,
57                                           ':',
58                                           1,
59                                           2)
60                                  + 1,
61                                  1),
62                          'S', ' from the start of the prior run',
63                          'C', ' from the completion of the prior run')
64            ELSE
65               'n/a'
66         END
67            set_days_of_week,
68         CASE
69            WHEN     fcrc.class_type = 'S'
70                 AND INSTR (SUBSTR (fcrc.class_info, 33), '1', 1) > 0
71            THEN
72                  'Days of week: '
73               || DECODE (SUBSTR (fcrc.class_info, 33, 1), '1', 'Sun, ')
74               || DECODE (SUBSTR (fcrc.class_info, 34, 1), '1', 'Mon, ')
75               || DECODE (SUBSTR (fcrc.class_info, 35, 1), '1', 'Tue, ')
76               || DECODE (SUBSTR (fcrc.class_info, 36, 1), '1', 'Wed, ')
77               || DECODE (SUBSTR (fcrc.class_info, 37, 1), '1', 'Thu, ')
78               || DECODE (SUBSTR (fcrc.class_info, 38, 1), '1', 'Fri, ')
79               || DECODE (SUBSTR (fcrc.class_info, 39, 1), '1', 'Sat ')
80            ELSE
81               'n/a'
82         END
83            days_of_week,
84         CASE
85            WHEN     fcrc.class_type = 'S'
86                 AND INSTR (SUBSTR (fcrc.class_info, 1, 31), '1', 1) > 0
87            THEN
88                  'Set Days of Month: '
89               || DECODE (SUBSTR (fcrc.class_info, 1, 1), '1', '1st, ')
90               || DECODE (SUBSTR (fcrc.class_info, 2, 1), '1', '2nd, ')
91               || DECODE (SUBSTR (fcrc.class_info, 3, 1), '1', '3rd, ')
92               || DECODE (SUBSTR (fcrc.class_info, 4, 1), '1', '4th, ')
93               || DECODE (SUBSTR (fcrc.class_info, 5, 1), '1', '5th, ')
94               || DECODE (SUBSTR (fcrc.class_info, 6, 1), '1', '6th, ')
95               || DECODE (SUBSTR (fcrc.class_info, 7, 1), '1', '7th, ')
96               || DECODE (SUBSTR (fcrc.class_info, 8, 1), '1', '8th, ')
97               || DECODE (SUBSTR (fcrc.class_info, 9, 1), '1', '9th, ')
98               || DECODE (SUBSTR (fcrc.class_info, 10, 1), '1', '10th, ')
99               || DECODE (SUBSTR (fcrc.class_info, 11, 1), '1', '11th, ')
100               || DECODE (SUBSTR (fcrc.class_info, 12, 1), '1', '12th, ')
101               || DECODE (SUBSTR (fcrc.class_info, 13, 1), '1', '13th, ')
102               || DECODE (SUBSTR (fcrc.class_info, 14, 1), '1', '14th, ')
103               || DECODE (SUBSTR (fcrc.class_info, 15, 1), '1', '15th, ')
104               || DECODE (SUBSTR (fcrc.class_info, 16, 1), '1', '16th, ')
105               || DECODE (SUBSTR (fcrc.class_info, 17, 1), '1', '17th, ')
106               || DECODE (SUBSTR (fcrc.class_info, 18, 1), '1', '18th, ')
107               || DECODE (SUBSTR (fcrc.class_info, 19, 1), '1', '19th, ')
108               || DECODE (SUBSTR (fcrc.class_info, 20, 1), '1', '20th, ')
109               || DECODE (SUBSTR (fcrc.class_info, 21, 1), '1', '21st, ')
110               || DECODE (SUBSTR (fcrc.class_info, 22, 1), '1', '22nd, ')
111               || DECODE (SUBSTR (fcrc.class_info, 23, 1), '1', '23rd,')
112               || DECODE (SUBSTR (fcrc.class_info, 24, 1), '1', '24th, ')
113               || DECODE (SUBSTR (fcrc.class_info, 25, 1), '1', '25th, ')
114               || DECODE (SUBSTR (fcrc.class_info, 26, 1), '1', '26th, ')
115               || DECODE (SUBSTR (fcrc.class_info, 27, 1), '1', '27th, ')
116               || DECODE (SUBSTR (fcrc.class_info, 28, 1), '1', '28th, ')
117               || DECODE (SUBSTR (fcrc.class_info, 29, 1), '1', '29th, ')
118               || DECODE (SUBSTR (fcrc.class_info, 30, 1), '1', '30th, ')
119               || DECODE (SUBSTR (fcrc.class_info, 31, 1), '1', '31st. ')
120            ELSE
121               'n/a'
122         END
123            days_of_month,
124         CASE
125            WHEN     fcrc.class_type = 'S'
126                 AND SUBSTR (fcrc.class_info, 32, 1) = '1'
127            THEN
128               'Yes'
129            ELSE
130               'n/a'
131         END
132            last_day_of_month_ticked,
133         fcrc.CLASS_INFO
134    FROM applsys.fnd_concurrent_requests  fcr,
135         applsys.fnd_user                 fu,
136         applsys.fnd_user                 u2,
137         applsys.fnd_concurrent_programs  fcp,
138         applsys.fnd_concurrent_programs_tl fcpt,
139         applsys.fnd_printer_styles_tl    fpst,
140         applsys.fnd_conc_release_classes fcrc,
141         applsys.fnd_responsibility_tl    frt,
142         apps.fnd_lookups                 fl
143   WHERE     fcp.application_id = fcpt.application_id
144         AND fcr.requested_by = fu.user_id
145         AND fcr.concurrent_program_id = fcp.concurrent_program_id
146         AND fcr.program_application_id = fcp.application_id
147         AND fcr.concurrent_program_id = fcpt.concurrent_program_id
148         AND fcr.responsibility_id = frt.responsibility_id
149         AND fcr.last_updated_by = u2.user_id
150         AND fcr.print_style = fpst.printer_style_name(+)
151         AND fcr.release_class_id = fcrc.release_class_id(+)
152         AND fcr.status_code = fl.lookup_code
153         AND fl.lookup_type = 'CP_STATUS_CODE'
154         AND fcr.phase_code = 'P'
155         AND 1 = 1
156ORDER BY fu.description, fcr.requested_start_date ASC