[Home] [Help]
PACKAGE BODY: APPS.BIS_BIA_REFRESH_REPORT
Source
1 PACKAGE BODY BIS_BIA_REFRESH_REPORT AS
2 /* $Header: BISRPTSB.pls 120.0 2005/06/01 14:24:02 appldev noship $ */
3 version CONSTANT CHAR (80)
4 := '$Header: BISRPTSB.pls 120.0 2005/06/01 14:24:02 appldev noship $
5 ';
6
7 FUNCTION get_request_set_time_qry (
8 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl
9 )
10 RETURN VARCHAR2
11 IS
12 sql_str VARCHAR2 (6000);
13 vorderby VARCHAR2 (200);
14 vset VARCHAR2 (2000);
15 BEGIN
16 FOR i IN 1 .. p_page_parameter_tbl.COUNT
17 LOOP
18 IF p_page_parameter_tbl (i).parameter_name =
19 'DBI_REQUEST_SET+DBI_REQUEST_SET'
20 THEN
21 vset := p_page_parameter_tbl (i).parameter_id;
22 END IF;
23
24 IF p_page_parameter_tbl (i).parameter_name = 'ORDER BY'
25 OR p_page_parameter_tbl (i).parameter_name = 'ORDERBY'
26 THEN
27 vorderby := p_page_parameter_tbl (i).parameter_value;
28 END IF;
29 END LOOP;
30
31 IF vset IS NULL OR vset IN ('',' ')
32 THEN
33 vset := 'All';
34 END IF;
35
36 IF NVL (vorderby, 'ORDERBY') = 'ORDERBY'
37 THEN
38 vorderby := ' UPPER(BIS_REQUEST_SET_DISPLAY_NAME) ASC';
39 END IF;
40
41 vorderby := REPLACE (vorderby, 'BIS_REQUEST_LAST_DATE', 'actual_completion_date');
42 vorderby := REPLACE (vorderby, 'BIS_REQUEST_SET_DISPLAY_NAME','UPPER(user_request_set_name)');
43
44 sql_str :=
45 'SELECT request_set_name bis_request_set_short_name,
46 user_request_set_name bis_request_set_display_name,
47 meaning bis_request_refresh_type,
48 request_set_id bis_request_set_id, request_id bis_request_id,
49 user_name bis_requestor, bis_request_refresh_time,
50 TO_CHAR (actual_completion_date,
51 ''DD-MON-YYYY HH24:MI:SS''
52 ) bis_request_last_date
53 FROM (SELECT DISTINCT s.user_request_set_name, s.request_set_name, s.request_set_id,
54 c.actual_completion_date
55 ,c.request_id,
56 u.user_name,
57 bis_bia_refresh_report.time_interval(c.actual_completion_date
58 - c.requested_start_date
59 ) bis_request_refresh_time,
60 RANK () OVER (PARTITION BY s.request_set_id ORDER BY c.actual_completion_date DESC)
61 pos,
62 v.meaning
63 FROM bis_request_set_objects_v o,
64 fnd_request_sets_vl s,
65 fnd_concurrent_requests c,
66 fnd_concurrent_programs p,
67 fnd_user u,
68 bis_request_set_options r,
69 fnd_common_lookups v
70 WHERE o.object_type = ''PAGE''
71 AND bis_impl_dev_pkg.get_function_by_page (o.object_name) IS NOT NULL
72 AND s.application_id = 191
73 AND s.request_set_name = o.request_set_name
74 AND p.concurrent_program_id = c.concurrent_program_id
75 AND p.application_id = 0
76 AND p.concurrent_program_name = ''FNDRSSUB''
77 AND c.argument1 = s.application_id
78 AND c.argument2 = s.request_set_id
79 AND c.status_code IN (''C'', ''G'', ''R'', ''I'')
80 AND u.user_id = c.requested_by
81 AND s.request_set_name = r.request_set_name
82 AND r.option_name = ''REFRESH_MODE''
83 AND r.option_value = v.lookup_code
84 AND v.lookup_type = ''BIS_REFRESH_MODE''
85 AND s.request_set_name =
86 DECODE (NVL (&DBI_REQUEST_SET+DBI_REQUEST_SET, ''All''),
87 ''All'', s.request_set_name,
88 NVL (&DBI_REQUEST_SET+DBI_REQUEST_SET, ''All'')
89 )
90 )
91 WHERE pos = 1
92 ORDER BY '
93 || vorderby;
94 RETURN sql_str;
95 END get_request_set_time_qry;
96
97 FUNCTION get_request_stage_time_qry (
98 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl
99 )
100 RETURN VARCHAR2
101 IS
102 sql_str VARCHAR2 (2000);
103 vorderby VARCHAR2 (200);
104 l_request_id fnd_concurrent_requests.request_id%TYPE;
105 l_request_set_id fnd_request_sets_vl.request_set_id%TYPE;
106 l_request_set_name fnd_request_sets_vl.request_set_name%TYPE;
107 l_user_request_set_name fnd_request_sets_vl.user_request_set_name%TYPE;
108 BEGIN
109 FOR i IN 1 .. p_page_parameter_tbl.COUNT
110 LOOP
111 IF p_page_parameter_tbl (i).parameter_name = 'REQUEST_SET_ID'
112 THEN
113 l_request_set_id := p_page_parameter_tbl (i).parameter_value;
114 ELSIF p_page_parameter_tbl (i).parameter_name = 'REQUEST_SET_NAME'
115 THEN
116 l_request_set_name := p_page_parameter_tbl (i).parameter_value;
117 ELSIF p_page_parameter_tbl (i).parameter_name =
118 'USER_REQUEST_SET_NAME'
119 THEN
120 l_user_request_set_name :=
121 p_page_parameter_tbl (i).parameter_value;
122 ELSIF p_page_parameter_tbl (i).parameter_name = 'REQUEST_ID'
123 THEN
124 l_request_id := p_page_parameter_tbl (i).parameter_value;
125 END IF;
126
127 IF p_page_parameter_tbl (i).parameter_name = 'ORDER BY'
128 OR p_page_parameter_tbl (i).parameter_name = 'ORDERBY'
129 THEN
130 vorderby := p_page_parameter_tbl (i).parameter_value;
131 END IF;
132 END LOOP;
133
134 -- if nvl(vOrderBy,'ORDERBY') = 'ORDERBY' then vOrderBy := 'ELAPSED_TIME' ; end if;
135 IF NVL (vorderby, 'ORDERBY') = 'ORDERBY'
136 THEN
137 vorderby := ' display_sequence ASC';
138 END IF;
139
140 IF INSTR (vorderby, 'BIS_REQUEST_SET_STAGE') >= 1
141 THEN
142 IF INSTR (vorderby, 'ASC') >= 1
143 THEN
144 vorderby := ' display_sequence ASC';
145 ELSE
146 vorderby := ' display_sequence DESC';
147 END IF;
148 END IF;
149
150 sql_str :=
151 'SELECT stg.request_set_id BIS_REQUEST_SET_ID,'
152 || ''''
153 || l_request_set_name
154 || ''''
155 || 'BIS_REQUEST_SET_NAME,'
156 || ''''
157 || l_request_id
158 || ''''
159 || 'BIS_PRIORITY_REQUEST_ID,
160 stg.request_set_stage_id BIS_REQUEST_SET_STAGE_ID,
161 stg.user_stage_name BIS_REQUEST_SET_STAGE_NAME,
162 con.request_id BIS_REQUEST_ID,
163 bis_bia_refresh_report.time_interval(con.actual_completion_date
164 - con.requested_start_date
165 ) BIS_REQUEST_REFRESH_TIME
166
167 FROM fnd_concurrent_requests con, fnd_request_set_stages_vl stg
168 WHERE con.priority_request_id = '
169 || l_request_id
170 || ' AND con.priority_request_id = con.parent_request_id
171 AND stg.request_set_id = '
172 || l_request_set_id
173 || 'AND con.argument2 = TO_CHAR (stg.request_set_id)
174 AND con.argument3 = TO_CHAR (stg.request_set_stage_id) ORDER BY'
175 || vorderby;
176 RETURN sql_str;
177 END get_request_stage_time_qry;
178
179 FUNCTION get_request_object_time_qry (
180 p_page_parameter_tbl IN bis_pmv_page_parameter_tbl
181 )
182 RETURN VARCHAR2
183 IS
184 sql_str VARCHAR2 (6000);
185 vorderby VARCHAR2 (200);
186 l_priority_request_id NUMBER (30);
187 l_parent_request_id NUMBER (30);
188 BEGIN
189 IF p_page_parameter_tbl.COUNT > 0
190 THEN
191 FOR i IN 1 .. p_page_parameter_tbl.COUNT
192 LOOP
193 IF p_page_parameter_tbl (i).parameter_name = 'PARENT_REQUEST_ID'
194 THEN
195 l_parent_request_id :=
196 p_page_parameter_tbl (i).parameter_value;
197 END IF;
198
199 IF p_page_parameter_tbl (i).parameter_name = 'ORDER BY'
200 OR p_page_parameter_tbl (i).parameter_name = 'ORDERBY'
201 THEN
202 vorderby := p_page_parameter_tbl (i).parameter_value;
203 END IF;
204 END LOOP;
205 END IF;
206
207 IF NVL (vorderby, 'ORDERBY') = 'ORDERBY'
208 THEN
209 vorderby := 'display_sequence ASC, request_id ASC';
210 END IF;
211
212 IF INSTR (vorderby, 'BIS_REQUEST_SET_STAGE') >= 1
213 THEN
214 IF INSTR (vorderby, 'ASC') >= 1
215 THEN
216 vorderby := ' display_sequence ASC, request_id ASC';
217 ELSE
218 vorderby := ' display_sequence DESC, request_id DESC';
219 END IF;
220 END IF;
221
222 IF l_parent_request_id IS NOT NULL
223 THEN
224 sql_str :=
225 'SELECT
226 request_set_stage_id BIS_REQUEST_SET_STAGE_ID
227 , user_stage_name BIS_REQUEST_SET_STAGE_NAME
228 , NVL(
229 (
230 SELECT fnd_lv.meaning
231 FROM bis_obj_properties bobjp
232 , fnd_common_lookups fnd_lv
233 WHERE bobjp.object_name = m.object_name
234 AND fnd_lv.lookup_type = ''BIS_OBJECT_TYPE''
235 AND fnd_lv.lookup_code = bobjp.object_type
236 )
237 , (
238 SELECT meaning
239 FROM fnd_common_lookups
240 WHERE lookup_type = ''BIS_OBJECT_TYPE''
241 AND lookup_code = ''TABLE''
242 AND m.object_name LIKE ''MLOG$_%''
243 )
244 ) BIS_REQUEST_OBJECT_TYPE
245 , object_name BIS_REQUEST_OBJECT_NAME
246 , request_id BIS_REQUEST_ID
247 , user_concurrent_program_name BIS_CONCURRENT_DISPLAY_NAME
248 , BIS_REQUEST_REFRESH_TIME
249 , display_sequence BIS_REQUEST_STAGE_DISPLAY_SEQ
250 FROM (
251 SELECT rset.request_id
252 , DECODE(cprog.concurrent_program_name, ''FNDGTST'', rset.argument2, brlog.object_name) object_name
253 , stage.user_stage_name
254 , stage.request_set_stage_id
255 , stage.display_sequence
256 , rset.actual_start_date
257 , rset.actual_completion_date
258 , bis_bia_refresh_report.time_interval(rset.actual_completion_date
259 - rset.actual_start_date
260 ) BIS_REQUEST_REFRESH_TIME
261 , cprog.user_concurrent_program_name
262 FROM fnd_concurrent_requests rset
263 , fnd_concurrent_requests rset_stg
264 , fnd_concurrent_programs_vl cprog
265 , fnd_request_set_stages_vl stage
266 , bis_refresh_log brlog
267 WHERE rset.priority_request_id = &REQUEST_ID+REQUEST_ID
268 AND rset.parent_request_id NOT IN (-1, &REQUEST_ID+REQUEST_ID)
269 AND rset.concurrent_program_id = cprog.concurrent_program_id
270 AND rset.request_id = brlog.request_id(+)
271 AND rset.parent_request_id = rset_stg.request_id
272 AND rset_stg.argument3 = TO_CHAR(stage.request_set_stage_id)
273 AND rset_stg.priority_request_id = &REQUEST_ID+REQUEST_ID
274 AND rset.parent_request_id ='
275 || l_parent_request_id
276 || ') m order by '
277 || vorderby;
278 ELSE
279 sql_str :=
280 'SELECT
281 request_set_stage_id BIS_REQUEST_SET_STAGE_ID
282 , user_stage_name BIS_REQUEST_SET_STAGE_NAME
283 , NVL(
284 (
285 SELECT fnd_lv.meaning
286 FROM bis_obj_properties bobjp
287 , fnd_common_lookups fnd_lv
288 WHERE bobjp.object_name = m.object_name
289 AND fnd_lv.lookup_type = ''BIS_OBJECT_TYPE''
290 AND fnd_lv.lookup_code = bobjp.object_type
291 )
292 , (
293 SELECT meaning
294 FROM fnd_common_lookups
295 WHERE lookup_type = ''BIS_OBJECT_TYPE''
296 AND lookup_code = ''TABLE''
297 AND m.object_name LIKE ''MLOG$_%''
298 )
299 ) BIS_REQUEST_OBJECT_TYPE
303 , BIS_REQUEST_REFRESH_TIME
300 , object_name BIS_REQUEST_OBJECT_NAME
301 , request_id BIS_REQUEST_ID
302 , user_concurrent_program_name BIS_CONCURRENT_DISPLAY_NAME
304 , display_sequence BIS_REQUEST_STAGE_DISPLAY_SEQ
305 FROM (
306 SELECT rset.request_id
307 , DECODE(cprog.concurrent_program_name, ''FNDGTST'', rset.argument2, brlog.object_name) object_name
308 , stage.user_stage_name
309 , stage.request_set_stage_id
310 , stage.display_sequence
311 , rset.actual_start_date
312 , rset.actual_completion_date
313 , bis_bia_refresh_report.time_interval(rset.actual_completion_date
314 - rset.actual_start_date
315 ) BIS_REQUEST_REFRESH_TIME
316 , cprog.user_concurrent_program_name
317 FROM fnd_concurrent_requests rset
318 , fnd_concurrent_requests rset_stg
319 , fnd_concurrent_programs_vl cprog
320 , fnd_request_set_stages_vl stage
321 , bis_refresh_log brlog
322 WHERE rset.priority_request_id = &REQUEST_ID+REQUEST_ID
323 AND rset.parent_request_id NOT IN (-1, &REQUEST_ID+REQUEST_ID)
324 AND rset.concurrent_program_id = cprog.concurrent_program_id
325 AND rset.request_id = brlog.request_id(+)
326 AND rset.parent_request_id = rset_stg.request_id
327 AND rset_stg.argument3 = TO_CHAR(stage.request_set_stage_id)
328 AND rset_stg.priority_request_id = &REQUEST_ID+REQUEST_ID
329 AND stage.request_set_stage_id in (
330 DECODE (NVL (&DBI_REQUEST_SET+DBI_REQUEST_STAGE, ''ALL''),
331 ''ALL'', stage.request_set_stage_id,
332 NVL (&DBI_REQUEST_SET+DBI_REQUEST_STAGE, ''ALL''))
333 )
334 ) m order by '
335 || vorderby;
336 END IF;
337
338 RETURN sql_str;
339 END get_request_object_time_qry;
340
341 FUNCTION time_interval (p_interval IN NUMBER)
342 RETURN VARCHAR2
343 IS
344 l_result VARCHAR2 (30) := '';
345 l_dummy PLS_INTEGER := 0;
346
347 FUNCTION format (p_value IN NUMBER)
348 RETURN VARCHAR2
349 IS
350 l_str VARCHAR2 (30) := '';
351 BEGIN
352 IF p_value < 10
353 THEN
354 l_str := '0' || TO_CHAR (p_value);
355 ELSE
356 l_str := p_value;
357 END IF;
358
359 RETURN l_str;
360 END format;
361 BEGIN
362 l_dummy := FLOOR (p_interval) * 24 + MOD (FLOOR (p_interval * 24), 24);
363 l_result := format (l_dummy) || ':';
364 l_dummy := MOD (FLOOR (p_interval * 24 * 60), 60);
365 l_result := l_result || format (l_dummy) || ':';
366 l_dummy := MOD (FLOOR (p_interval * 24 * 60 * 60), 60);
367 l_result := l_result || format (l_dummy);
368 RETURN l_result;
369 END time_interval;
370 END BIS_BIA_REFRESH_REPORT;
371