[Home] [Help]
PACKAGE BODY: APPS.BIS_RSG_PMV_REPORT_PKG
Source
1 PACKAGE BODY BIS_RSG_PMV_REPORT_PKG AS
2 /* $Header: BISRSPRB.pls 120.2 2006/03/27 09:34:38 amitgupt noship $ */
3
4 PROCEDURE debug ( text varchar2 )
5 IS
6 BEGIN
7 --INSERT INTO AMIT_DEBUG(TEXT) VALUES(text);
8 --commit;
9 null;
10 END;
11
12 FUNCTION returnLogUrl(req_id Number, fromReport Number) return Varchar2 IS
13
14 pUrlString Varchar2(300);
15
16 BEGIN
17 ---pUrlString := fnd_webfile.get_url(3, req_id, null, null, 1);
18
19 --if(pUrlString is NULL) then
20 if(fromReport = 1) then
21 pUrlString := 'pFunctionName=BIS_BIA_RSG_RPT_ERR_MSG_PG&formErrorType=NO_LOG_URL1&formRSID='||req_id;
22 else
23 pUrlString := 'pFunctionName=BIS_BIA_RSG_RPT_ERR_MSG_PG&formErrorType=NO_LOG_URL&formRSID='||req_id;
24 end if;
25 --end if;
26
27 return pUrlString;
28 END returnLogUrl;
29
30 FUNCTION format (p_value IN NUMBER) RETURN VARCHAR2 IS
31 l_str VARCHAR2 (30);
32 BEGIN
33 l_str := '';
34 IF p_value < 10
35 THEN
36 l_str := '0' || TO_CHAR (p_value);
37 ELSE
38 l_str := p_value;
39 END IF;
40
41 RETURN l_str;
42 END format;
43
44 FUNCTION time_interval (p_interval IN NUMBER)RETURN NUMBER
45 IS
46 l_dummy NUMBER;
47
48 BEGIN
49 l_dummy := p_interval * 24;
50 RETURN l_dummy;
51 END time_interval;
52
53
54 FUNCTION time_interval_str (p_interval IN NUMBER) RETURN VARCHAR2
55 IS
56 l_result VARCHAR2 (30);
57 l_dummy PLS_INTEGER;
58 BEGIN
59 l_dummy := FLOOR (p_interval) * 24 + MOD (FLOOR (p_interval * 24), 24);
60 l_result := format (l_dummy) || ':';
61 l_dummy := MOD (FLOOR (p_interval * 24 * 60), 60);
62 l_result := l_result || format (l_dummy) || ':';
63 l_dummy := MOD (FLOOR (p_interval * 24 * 60 * 60), 60);
64 l_result := l_result || format (l_dummy);
65 RETURN l_result;
66 END time_interval_str;
67
68 FUNCTION time_interval_HHMM (p_interval IN NUMBER) RETURN VARCHAR2
69 IS
70 l_result VARCHAR2 (30);
71 l_dummy PLS_INTEGER;
72 BEGIN
73 l_dummy := FLOOR (p_interval) * 24 + MOD (FLOOR (p_interval * 24), 24);
74 l_result := format (l_dummy) || ':';
75 l_dummy := MOD (FLOOR (p_interval * 24 * 60), 60);
76 l_result := l_result || format (l_dummy) ;
77 RETURN l_result;
78 END time_interval_HHMM;
79
80
81 FUNCTION duration(
82 p_duration number) return NUMBER IS
83 BEGIN
84 if(p_duration is null) then
85 return null;
86 else
87 return time_interval(p_duration);
88 end if;
89 END duration;
90
91 FUNCTION duration_HHMM(
92 p_duration number) return VARCHAR2 IS
93 BEGIN
94 if(p_duration is null) then
95 return null;
96 else
97 return time_interval_HHMM(p_duration);
98 end if;
99 END duration_HHMM;
100
101 FUNCTION duration_str(
102 p_duration number) return VARCHAR2 IS
103 BEGIN
104 if(p_duration is null) then
105 return null;
106 else
107 return time_interval_str(p_duration);
108 end if;
109 END duration_str;
110
111 FUNCTION get_meaning(p_status_code VARCHAR2,
112 p_phase_code VARCHAR2) return VARCHAR2 IS
113
114 l_meaning VARCHAR2(80);
115 BEGIN
116 -- if status code is normal('C') then we have to display Completed, else take the value for
117 -- that status code
118 IF (p_status_code = 'C') THEN
119 SELECT MEANING INTO l_meaning FROM FND_LOOKUPS
120 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE' AND
121 LOOKUP_CODE = p_phase_code;
122 ELSE
123 SELECT MEANING INTO l_meaning FROM FND_LOOKUPS
124 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND
125 LOOKUP_CODE = p_status_code;
126 END IF;
127
128 return l_meaning;
129
130 END get_meaning;
131
132 --added for bug 4486989
133 -- function is added for timezone conversion
134 -- is also being called from BIS_SUBMIT_REQUESTSET AND BIS_BIA_RSG_PSTATE
135 FUNCTION date_to_charDTTZ(pServerDate DATE) return varchar2 IS
136 l_server_code varchar2(50);
137 l_client_code varchar2(50);
138 l_client_date DATE;
139 BEGIN
140 -- get the timezones code
141 l_server_code := fnd_timezones.get_server_timezone_code;
142 l_client_code := fnd_timezones.get_client_timezone_code;
143
144 -- call adjust time zone to convert to client timezone
145 l_client_date := fnd_timezones_pvt.adjust_datetime(
146 date_time => pServerDate
147 ,from_tz => l_server_code
148 ,to_tz => l_client_code
149 );
150 --convert the date format
151 return to_char(l_client_date,FND_PROFILE.value('ICX_DATE_FORMAT_MASK') || ' HH24:MI:SS');
152 END date_to_charDTTZ;
153
154 PROCEDURE request_set_perf_report
155 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
156 x_custom_sql OUT NOCOPY VARCHAR2,
157 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
158
159 l_sql_stmt VARCHAR2(32767);
160 l_custom_rec BIS_QUERY_ATTRIBUTES;
161 pname VARCHAR2(2000);
162 pvalue VARCHAR2(2000);
163 pid VARCHAR2(2000);
164 rs_id VARCHAR2(2200);
165 rs_type varchar2(30);
166 rs_history varchar2(30);
167
168 l_col VARCHAR2(100);
169 view_by_id Number;
170 counter Number;
171 l_days_cond VARCHAR2(200);
172 l_type_cond VARCHAR2(200);
173 l_rsid_cond VARCHAR2(2200);
174
175 BEGIN
176
177 pname := NULL;
178 pvalue := NULL;
179 pid := NULL;
180 rs_id := null;
181 rs_type := null;
182 rs_history := null;
183 l_col := NULL;
184 view_by_id := 0;
185 counter :=1;
186 l_days_cond := NULL;
187 l_type_cond := NULL;
188 l_rsid_cond := NULL;
189
190 FOR counter IN 1..p_param.count LOOP
191 pname := p_param(counter).parameter_name;
192 pvalue := p_param(counter).parameter_value;
193 pid := p_param(counter).parameter_id;
194 -- debug( '( ' || pname || ', ' || pvalue || ' )' );
195
196 if pname = 'BIS_D_RS_NAME+BIS_D_RS_NAME' then
197 -- debug( '( ' || pname || ' PID_amit, ' || pid || ' )' );
198 rs_id := pid ;
199 end if;
200
201 if pname = 'BISDRSTYPE+BISDRSTYPE' then
202 rs_type := pid ;
203 end if;
204
205 if pname = 'BISDRSDAYS+BISDRSDAYS' then
206 rs_history := pid ;
207 end if;
208
209 IF ( UPPER(pname) LIKE '%VIEW_BY%') THEN
210 IF( UPPER(pvalue) = 'BIS_D_RS_NAME+BIS_D_RS_NAME' ) THEN
211 l_col := 'request_set_id';
212 view_by_id := 0;
213 END IF;
214 IF( UPPER(pvalue) = 'BISDRSTYPE+BISDRSTYPE' ) THEN
215 l_col := 'request_set_type';
216 view_by_id := 1;
217 END IF;
218 END IF;
219
220 END LOOP;
221
222 counter :=1;
223
224 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
225 x_custom_output := bis_query_attributes_tbl();
226
227 if (rs_history is not null AND rs_history <> 'All') then
228 l_days_cond := 'and r.last_update_date >= sysdate - :BIND_HISTORY ';
229 l_sql_stmt := l_sql_stmt|| l_days_cond;
230 l_custom_rec.attribute_name := ':BIND_HISTORY';
231 l_custom_rec.attribute_value := rs_history;
232 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
233 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
234 x_custom_output.extend;
235 x_custom_output(counter) := l_custom_rec;
236 counter:= counter+1;
237 end if;
238
239 if (rs_type is not null AND rs_type <> 'All') then
240 l_type_cond := 'and r.request_set_type= :BIND_TYPE ';
241 l_sql_stmt := l_sql_stmt || l_type_cond;
242
243 l_custom_rec.attribute_name := ':BIND_TYPE';
244 l_custom_rec.attribute_value := rs_type;
245 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
246 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
247 x_custom_output.extend;
248 x_custom_output(counter) := l_custom_rec;
249 end if;
250
251 if (rs_id is not null AND rs_id <> 'All') then
252 l_rsid_cond := 'and r.request_set_id in (' || rs_id||') ';
253 end if;
254
255 --if view by is request set type
256 IF(view_by_id = 1) THEN
257 l_sql_stmt := 'select
258 view_type.value AS VIEWBY,
259 view_type.ID AS VIEWBYID,
260 BIS_RSG_PMV_REPORT_PKG.duration(avg(avg_run_time)) AS BIS_TIME,
261 BIS_RSG_PMV_REPORT_PKG.duration_str(avg(avg_run_time)) AS BIS_REQUEST_REFRESH_TIME,
262 BIS_RSG_PMV_REPORT_PKG.duration(max(max_run_time)) AS BIS_MAXIMUM,
263 BIS_RSG_PMV_REPORT_PKG.duration_str(max(max_run_time)) AS BIS_MAX,
264 BIS_RSG_PMV_REPORT_PKG.duration(min(min_run_time)) AS BIS_MINIMUM,
265 BIS_RSG_PMV_REPORT_PKG.duration_str(min(min_run_time)) AS BIS_MIN,
266 sum(num_runs) AS BIS_RUN,
267 sum(space_use)/(1024*1024) AS BIS_RS_TOTAL_SPACE_OCCUPIED,
268 NULL
269 BISREPORTURL ';
270
271 ELSE -- if the view by is request set name
272 l_sql_stmt := 'select
273 view_type.value AS VIEWBY,
274 view_type.ID AS VIEWBYID,
275 BIS_RSG_PMV_REPORT_PKG.duration(avg_run_time) AS BIS_TIME,
276 BIS_RSG_PMV_REPORT_PKG.duration_str(avg_run_time) AS BIS_REQUEST_REFRESH_TIME,
277 BIS_RSG_PMV_REPORT_PKG.duration(max_run_time) AS BIS_MAXIMUM,
278 BIS_RSG_PMV_REPORT_PKG.duration_str(max_run_time) AS BIS_MAX,
279 BIS_RSG_PMV_REPORT_PKG.duration(min_run_time) AS BIS_MINIMUM,
280 BIS_RSG_PMV_REPORT_PKG.duration_str(min_run_time) AS BIS_MIN,
281 num_runs AS BIS_RUN,
282 space_use/(1024*1024) AS BIS_RS_TOTAL_SPACE_OCCUPIED,
283 nvl2(space_use,
284 ''pFunctionName=BIS_BIA_RSG_SPACE_DET_PGE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',
285 null)
286 BISREPORTURL ';
287
288 end if;
289
290 --common part of the query
291 l_sql_stmt := l_sql_stmt || 'from
292 (
293 select agg_runs.request_set_id,
294 agg_runs.request_set_type,
295 min_run_time,
296 max_run_time,
297 Num_runs,
298 avg_run_time
299 from
300 (select request_set_id,request_set_type
301 ,Min(completion_date-start_date) min_run_time
302 ,Max(completion_date-start_date) max_run_time
303 ,avg(completion_date- start_date) avg_run_time
304 ,R.REQUEST_SET_TYPE rstype
305 from
306 bis_rs_run_history r
307 where
308 r.phase_code =''C'' AND (STATUS_CODE=''G''
309 or STATUS_CODE=''C'') ';
310
311 l_sql_stmt:= l_sql_stmt||l_days_cond|| l_type_cond ||l_rsid_cond;
312
313 l_sql_stmt:= l_sql_stmt || 'GROUP BY r.request_set_id,r.request_set_type ) agg_time, ';
314
315 l_sql_stmt:= l_sql_stmt || '(Select request_set_id,request_set_type
316 ,count(r.request_set_id) Num_runs
317 from
318 bis_rs_run_history r, bis_rs_names_v v
319 where
320 r.phase_code =''C''
321 AND v.id = r.request_set_id
322 AND r.STATUS_CODE<>''X'' ';
323
324 -- added join with bis_rs_names_v for bug 4293781
325
326 l_sql_stmt:= l_sql_stmt||l_days_cond|| l_type_cond ||l_rsid_cond;
327
328 l_sql_stmt:= l_sql_stmt || 'GROUP BY r.request_set_id,r.request_set_type ) agg_runs
329 where agg_time.request_set_id(+)=agg_runs.request_set_id
330 ) tmp, ';
331
332 l_sql_stmt:= l_sql_stmt || '
333 (select tmp.srid srid, sum(tmp.object_space_usage) space_use,tmp.rsid rsid, tmp.rstype rstype
334 from
335 (select distinct object_name, object_type, object_space_usage,p.set_request_id srid,
336 Latestreq.request_set_id rsid,
337 Latestreq.request_set_type rstype from bis_obj_refresh_history o,
338 bis_rs_prog_run_history p,
339 (select max(request_id) maxid, request_set_id,request_set_type
340 from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
341 OR STATUS_CODE =''G'')
342 group by request_set_id,request_set_type) Latestreq
343 where
344 o.prog_request_id = p.request_id
345 and p.set_request_id = Latestreq.maxid) tmp
346 group by tmp.srid,tmp.rsid, tmp.rstype ) total_space ';
347
348 --if view by is request set type
349 IF(view_by_id = 1) THEN
350 l_sql_stmt:= l_sql_stmt ||', BIS_RS_REFRESH_TYPE_V view_type ';
351 ELSE
352 l_sql_stmt:= l_sql_stmt ||', BIS_RS_NAMES_V view_type ';
353 END IF;
354
355 l_sql_stmt:= l_sql_stmt ||'where
356 total_space.rsid(+)=tmp.request_set_id
357 and view_type.ID = tmp.'||l_col;
358
359 --if view by is request set type
360 IF(view_by_id = 1) THEN
361 l_sql_stmt:= l_sql_stmt || ' GROUP BY VIEW_TYPE.ID,VIEW_TYPE.VALUE ';
362 END IF;
363
364 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
365
366 --debug(l_sql_stmt);
367 x_custom_sql := l_sql_stmt;
368
369 END request_set_perf_report ;
370
371 PROCEDURE request_set_perf_det_rep
372 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
373 x_custom_sql OUT NOCOPY VARCHAR2,
374 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
375
376 l_sql_stmt VARCHAR2(32767);
377 l_custom_rec BIS_QUERY_ATTRIBUTES;
378 pname VARCHAR2(2000);
379 pvalue VARCHAR2(2000);
380 pid VARCHAR2(2000);
381 rs_id VARCHAR2(30);
382 rs_type varchar2(30);
383 rs_history varchar2(30);
384 rs_run_id varchar2(2000);
385
386 counter number;
387
388 BEGIN
389
390 pname := NULL;
391 pvalue := NULL;
392 pid := NULL;
393 rs_id := null;
394 rs_type := null;
395 rs_history := null;
396 rs_run_id := NULL;
397 counter :=1;
398
399 FOR counter IN 1..p_param.count LOOP
400 pname := p_param(counter).parameter_name;
401 pvalue := p_param(counter).parameter_value;
402 pid := p_param(counter).parameter_id;
403 --debug( '( ' || pname || ', ' || pvalue || ' )' );
404
405 if pname = 'BIS_D_RS_NAME+BIS_D_RS_NAME' then
406 rs_id := pid ;
407 end if;
408
409 if pname = 'BIS_D_RS_TYPE2+BIS_D_RS_TYPE2' then
410 rs_type := pid ;
411 end if;
412
413 if pname = 'BISDRSDAYS+BISDRSDAYS' then
414 rs_history := pid ;
415 end if;
416
417 if pname = 'BIS_D_RS_RUN_ID+BIS_D_RS_RUN_ID' then
418 -- debug( '( ' || pname || ' PID_amit, ' || pid || ' )' );
419 -- debug( '( ' || pname || ', ' || pvalue || ' )' );
420 rs_run_id := pid ;
421 end if;
422
423
424 END LOOP;
425
426 counter := 1;
427
428 l_sql_stmt := 'select B.request_id AS VIEWBY,
429 B.request_id AS VIEWBYID,
430 BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(start_date) AS BIS_RS_START_TIME,
431 BIS_RSG_PMV_REPORT_PKG.duration_str(completion_date - start_date) AS BIS_RS_DURATION,
432 BIS_RSG_PMV_REPORT_PKG.get_meaning(STATUS_CODE,PHASE_CODE) AS BIS_RS_STATUS,
433 ''pFunctionName=BIS_BIA_RSG_SUB_REQS_PGE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' BISREPORTURL
434 ,BIS_RSG_PMV_REPORT_PKG.returnLogUrl( B.request_id,2) BISLOGFILEURL
435 from BIS_RS_RUN_HISTORY B, FND_REQUEST_SETS_VL F
436 where F.request_set_id = B.request_set_id
437 AND PHASE_CODE = ''C'' and STATUS_CODE <>''X'' ';
438
439
440 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
441 x_custom_output := bis_query_attributes_tbl();
442
443
444 if (rs_history is not null AND rs_history <> 'All') then
445 l_sql_stmt:= l_sql_stmt||'and B.last_update_date >= sysdate - :BIND_HISTORY ';
446 l_custom_rec.attribute_name := ':BIND_HISTORY';
447 l_custom_rec.attribute_value := rs_history;
448 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
449 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
450 x_custom_output.extend;
451 x_custom_output(counter) := l_custom_rec;
452 counter:= counter+1;
453 end if;
454
455 if (rs_type is not null AND rs_type <> 'All') then
456 l_sql_stmt := l_sql_stmt || 'and B.request_set_type= :BIND_TYPE ';
457
458 l_custom_rec.attribute_name := ':BIND_TYPE';
459 l_custom_rec.attribute_value := rs_type;
460 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
461 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
462 x_custom_output.extend;
463 x_custom_output(counter) := l_custom_rec;
464 counter:= counter+1;
465 end if;
466
467 if (rs_id is not null AND rs_id <> 'All') then
468 l_sql_stmt := l_sql_stmt || 'and f.request_set_id= :BIND_RSID ';
469
470 l_custom_rec.attribute_name := ':BIND_RSID';
471 l_custom_rec.attribute_value := rs_id;
472 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
473 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
474 x_custom_output.extend;
475 x_custom_output(counter) := l_custom_rec;
476 end if;
477
478 if (rs_run_id is not null AND rs_run_id <> 'All') then
479 l_sql_stmt := l_sql_stmt || 'and B.request_id in ('||rs_run_id||') ';
480 end if;
481
482 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
483
484 --debug(l_sql_stmt);
485 x_custom_sql := l_sql_stmt;
486
487 END request_set_perf_det_rep ;
488
489 PROCEDURE request_set_sub_req_rep
490 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
491 x_custom_sql OUT NOCOPY VARCHAR2,
492 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
493
494 l_sql_stmt VARCHAR2(32767);
495 l_custom_rec BIS_QUERY_ATTRIBUTES;
496 pname VARCHAR2(2000);
497 pvalue VARCHAR2(2000);
498 pid VARCHAR2(2000);
499 rs_id VARCHAR2(30);
500 rs_type varchar2(30);
501 rs_history varchar2(30);
502 rs_run_id varchar2(2000);
503 rs_stage_req varchar2(2000);
504 rs_sub_req varchar2(2000);
505 rs_prog_req varchar2(2000);
506
507
508 counter number;
509 l_setid_cond VARCHAR2(200);
510 l_type_cond VARCHAR2(200);
511 l_runid_cond1 VARCHAR2(200);
512 l_runid_cond2 VARCHAR2(200);
513 l_stage_cond1 VARCHAR2(200);
514 l_stage_cond2 VARCHAR2(200);
515 l_prog_cond VARCHAR2(2200);
516 l_prog_cond2 VARCHAR2(2200);
517
518 BEGIN
519 pname := NULL;
520 pvalue := NULL;
521 pid := NULL;
522 rs_id := null;
523 rs_type := null;
524 rs_history := null;
525 rs_run_id :=null;
526 rs_stage_req :=null;
527 rs_sub_req :=null;
528 rs_prog_req :=null;
529
530
531 counter :=1;
532 l_setid_cond :=NULL;
533 l_type_cond :=NULL;
534 l_runid_cond1 :=NULL;
535 l_runid_cond2 :=NULL;
536 l_stage_cond1 :=NULL;
537 l_stage_cond2 :=NULL;
538 l_prog_cond :=NULL;
539 l_prog_cond2 :=NULL;
540
541 FOR counter IN 1..p_param.count LOOP
542 pname := p_param(counter).parameter_name;
543 pvalue := p_param(counter).parameter_value;
544 pid := p_param(counter).parameter_id;
545 --- debug( '( ' || pname || ', ' || pvalue || ', ' || pid || ' )' );
546
547 if pname = 'BISDRSDAYS+BISDRSDAYS' then
548 rs_history := pid ;
549 end if;
550
551 if pname = 'BIS_D_RS_NAME+BIS_D_RS_NAME' then
552 rs_id := pid ;
553 end if;
554
555 if pname = 'BIS_D_RS_TYPE2+BIS_D_RS_TYPE2' then
556 rs_type := pid ;
557 end if;
558
559 if pname = 'BIS_D_RS_RUN_ID+BIS_D_RS_RUN_ID' then
560 rs_run_id := pid ;
561 end if;
562
563 if pname = 'BISDRSSTAGE+BISDRSSTAGE' then
564 rs_stage_req := pid ;
565 end if;
566
567 /*if pname = 'BISDRSSUBR+BISDRSSUBR' then
568 rs_sub_req := pid ;
569 end if;*/
570
571 if pname = 'BISDRSPROG+BISDRSPROG' then
572 rs_prog_req := pid ;
573 end if;
574
575 END LOOP;
576
577 counter := 1;
578
579 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
580 x_custom_output := bis_query_attributes_tbl();
581
582
583 if (rs_type is not null AND rs_type <> 'All') then
584 l_type_cond :='and R.request_set_type= :BIND_TYPE ';
585
586 l_custom_rec.attribute_name := ':BIND_TYPE';
587 l_custom_rec.attribute_value := rs_type;
588 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
589 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
590 x_custom_output.extend;
591 x_custom_output(counter) := l_custom_rec;
592 counter:= counter+1;
593 end if;
594
595
596 if (rs_id is not null AND rs_id <> 'All') then
597 l_setid_cond:= 'and R.request_set_id= :BIND_RSID ';
598
599 l_custom_rec.attribute_name := ':BIND_RSID';
600 l_custom_rec.attribute_value := rs_id;
601 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
602 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
603 x_custom_output.extend;
604 x_custom_output(counter) := l_custom_rec;
605 counter:=counter+1;
606 end if;
607
608 if (rs_run_id is not null AND rs_run_id <> 'All') then
609 l_runid_cond1 := 'and R.request_id= :BIND_RUNID ';
610 l_runid_cond2 := 'and B.set_request_id= :BIND_RUNID ';
611
612 l_custom_rec.attribute_name := ':BIND_RUNID';
613 l_custom_rec.attribute_value := rs_run_id;
614 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
615 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
616 x_custom_output.extend;
617 x_custom_output(counter) := l_custom_rec;
618 counter:=counter+1;
619 end if;
620
621 if (rs_stage_req is not null AND rs_stage_req <> 'All') then
622 --l_stage_cond1 := 'and B.request_id in ('|| rs_stage_req ||') ';
623 l_stage_cond1 := 'and B.request_id = :BIND_STAGEID ';
624 --l_stage_cond2 := 'and B.STAGE_REQUEST_ID in ('|| rs_stage_req ||') ';
625 l_stage_cond2 := 'and B.STAGE_REQUEST_ID = :BIND_STAGEID ';
626
627 l_custom_rec.attribute_name := ':BIND_STAGEID';
628 l_custom_rec.attribute_value := rs_stage_req;
629 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
630 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
631 x_custom_output.extend;
632 x_custom_output(counter) := l_custom_rec;
633 end if;
634
635 if (rs_prog_req is not null AND rs_prog_req <> 'All') then
636 l_prog_cond := 'and B.program_id in (' || rs_prog_req ||') ';
637 l_prog_cond2:= 'and exists (Select 1 from bis_rs_prog_run_history where stage_request_id=B.request_id and program_id in(' || rs_prog_req ||'))';
638 end if;
639
640 l_sql_stmt := 'select
641 rid BIS_RS_SUB_REQS_ID,
642 rname BIS_RS_REQUEST_NAME
643 , BIS_RSG_PMV_REPORT_PKG.date_to_charDTTZ(started) BIS_RS_START_TIME
644 ,dur BIS_RS_DURATION
645 ,BIS_RSG_PMV_REPORT_PKG.get_meaning(tmp.STATUS,''C'') BIS_RS_STATUS
646 ,LOG_MESSAGE BIS_LOG_MESSAGE
647 ,decode(url,'' '',null,url) BISREPORTURL
648 ,BIS_RSG_PMV_REPORT_PKG.returnLogUrl(rid,1) BISLOGFILEURL
649 FROM ';
650
651 l_sql_stmt:= l_sql_stmt||
652 '(select R.request_id rid,F.user_request_set_name rname,
653 R.start_date started,
654 BIS_RSG_PMV_REPORT_PKG.Duration_str(R.completion_date-R.start_date) dur,
655 STATUS_CODE status, R.completion_text LOG_MESSAGE, '' '' url
656 from bis_rs_run_history R,fnd_request_sets_vl F
657 where F.request_set_id = R.request_set_id AND R.PHASE_CODE=''C'' ' ||
658 l_type_cond ||l_setid_cond ||l_runid_cond1
659 ||' union
660 select B.request_id rid, F.user_stage_name rname,
661 B.start_date started,
662 BIS_RSG_PMV_REPORT_PKG.Duration_str(B.completion_date-B.start_date) dur,
663 B.STATUS_CODE status,B.completion_text LOG_MESSAGE,'' '' url
664 from
665 bis_rs_stage_run_history B, fnd_request_set_stages_vl F,bis_rs_run_history R
666 where F.request_set_stage_id = B.stage_id
667 and R.request_id = B.set_request_id AND R.PHASE_CODE=''C'' '
668 ||l_type_cond||l_setid_cond||l_runid_cond2||l_stage_cond1||l_prog_cond2
669 ||' union
670 Select B.request_id rid, F.user_concurrent_program_name rname, B.start_date started,
671 BIS_RSG_PMV_REPORT_PKG.Duration_str(B.completion_date-B.start_date) dur,
672 B.STATUS_CODE status,B.completion_text LOG_MESSAGE,
673 decode(num_obj,0,
674 ''pFunctionName=BIS_BIA_RSG_RPT_ERR_MSG_PG&formErrorType=NO_OBJECT'',
675 ''pFunctionName=BIS_BIA_RSG_REQ_DETAILS_PGE&BIS_RS_SUB_REQS_ID=BIS_RS_SUB_REQS_ID&BISRSSTAGE=''||B.stage_request_id||''&BISRSPROG=''||B.program_id||''&pParamIds=Y''
676 ) url
677 from
678 bis_rs_prog_run_history B, fnd_concurrent_programs_vl F,
679 bis_rs_run_history R,bis_rs_stage_run_history S,
680 (select distinct prog_request_id prid, 1 num_obj from bis_obj_refresh_history
681 union select request_id prid,0 num_obj from bis_rs_prog_run_history where request_id not
682 in(select prog_request_id from bis_obj_refresh_history)) count_obj
683 where F.concurrent_program_id = B.program_id
684 and F.APPLICATION_ID = B.Prog_app_id
685 and R.request_id=B.set_request_id
686 and S.request_id=B.stage_request_id
687 and B.request_id=count_obj.prid
688 AND R.PHASE_CODE=''C'' '
689 ||l_type_cond||l_setid_cond||l_runid_cond2||l_stage_cond2
690 ||l_prog_cond ||
691 ') tmp ';
692
693
694
695 if (rs_sub_req is not null AND rs_sub_req <> 'All') then
696 l_sql_stmt:=l_sql_stmt||'Where rid in ('|| rs_sub_req ||') ';
697 end if;
698
699
700 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
701
702 -- debug(l_sql_stmt);
703 x_custom_sql := l_sql_stmt;
704
705 END request_set_sub_req_rep ;
706
707 PROCEDURE request_details_report
708 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
709 x_custom_sql OUT NOCOPY VARCHAR2,
710 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
711
712 l_sql_stmt VARCHAR2(32767);
713 l_custom_rec BIS_QUERY_ATTRIBUTES;
714 base varchar2(257);
715 pname VARCHAR2(2000);
716 pvalue VARCHAR2(2000);
717 pid VARCHAR2(2000);
718 rs_id VARCHAR2(30);
719 rs_type varchar2(30);
720 rs_history varchar2(30);
721 rs_run_id varchar2(30);
722 rs_stage_req varchar2(200);
723 rs_sub_req varchar2(2000);
724 rs_prog_req varchar2(2000);
725
726 counter number;
727
728
729 BEGIN
730
731 pname := NULL;
732 pvalue := NULL;
733 pid := NULL;
734
735 rs_id := null;
736 rs_type := null;
737 rs_history := null;
738 rs_run_id :=null;
739 rs_stage_req :=null;
740 rs_sub_req :=null;
741 rs_prog_req :=null;
742
743
744 counter :=1;
745
746 FOR counter IN 1..p_param.count LOOP
747 pname := p_param(counter).parameter_name;
748 pvalue := p_param(counter).parameter_value;
749 pid := p_param(counter).parameter_id;
750 --debug( '( ' || pname || ', ' || pvalue || ' )' );
751
752 if pname = 'BISDRSDAYS+BISDRSDAYS' then
753 rs_history := pid ;
754 end if;
755
756 if pname = 'BIS_D_RS_NAME+BIS_D_RS_NAME' then
757 rs_id := pid ;
758 end if;
759
760 if pname = 'BIS_D_RS_TYPE2+BIS_D_RS_TYPE2' then
761 rs_type := pid ;
762 end if;
763
764 if pname = 'BIS_D_RS_RUN_ID+BIS_D_RS_RUN_ID' then
765 rs_run_id := pid ;
766 end if;
767
768 if pname = 'BISDRSSTAGE+BISDRSSTAGE' then
769 rs_stage_req := pid ;
770 end if;
771
772 /*if pname = 'BISDRSSUBR+BISDRSSUBR' then
773 rs_sub_req := pid ;
774 end if;*/
775
776 if pname = 'BISDRSPROG+BISDRSPROG' then
777 rs_prog_req := pid ;
778 end if;
779
780 END LOOP;
781
782 counter := 1;
783 fnd_profile.get('APPS_FRAMEWORK_AGENT', base);
784
785 l_sql_stmt:='select distinct object_name BIS_REQUEST_OBJECT_NAME,
786 --BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) BIS_REQUEST_OBJECT_TYPE,
787 CASE O.OBJECT_TYPE WHEN ''MV_LOG'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
788 WHEN ''BSC_CUSTOM_KPI'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
789 ELSE BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) END BIS_REQUEST_OBJECT_TYPE,
790 0 BIS_OBJECT_ROW_COUNT,
791 decode(refresh_type,''INCR'',
792 BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_REQUEST_SET_TYPE'',''INCR_LOAD''),
793 ''ANALYZED'',
794 BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_REFRESH_MODE'',''ANALYZED''),
795 ''CONSIDER_REFRESH'',
796 BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_REFRESH_MODE'',''CONSIDER_REFRESH''),
797 BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_REQUEST_SET_TYPE'',''INIT_LOAD'')) BIS_REQUEST_REFRESH_TYPE,
798 null BIS_RS_STATUS,
799 decode(O.OBJECT_TYPE,''MV_LOG'',NULL,''BSC_CUSTOM_KPI'',NULL,'||''''||
800 'pFunctionName=BIS_BIA_RSG_DEPENDENCIES_ALONE'
801 ||'&requestType=RSGReport&ObjType=''||o.object_type||''&ObjName=BIS_REQUEST_OBJECT_NAME''
802 ) BISREPORTURL
803 from bis_obj_refresh_history O,bis_rs_prog_run_history P,bis_rs_run_history R,
804 bis_rs_stage_run_history S
805 where O.prog_request_id = P.request_id
806 AND R.REQUEST_ID = S.SET_REQUEST_ID
807 AND S.REQUEST_ID = P.STAGE_REQUEST_ID
808 AND R.PHASE_CODE = ''C'' ';
809
810 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
811 x_custom_output := bis_query_attributes_tbl();
812
813 if (rs_type is not null AND rs_type <> 'All') then
814 l_sql_stmt :=l_sql_stmt||'and R.REQUEST_SET_TYPE= :BIND_TYPE ';
815
816 l_custom_rec.attribute_name := ':BIND_TYPE';
817 l_custom_rec.attribute_value := rs_type;
818 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
819 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
820 x_custom_output.extend;
821 x_custom_output(counter) := l_custom_rec;
822 counter:= counter+1;
823 end if;
824
825
826 if (rs_id is not null AND rs_id <> 'All') then
827 l_sql_stmt :=l_sql_stmt||'and R.REQUEST_SET_ID= :BIND_RSID ';
828 l_custom_rec.attribute_name := ':BIND_RSID';
829 l_custom_rec.attribute_value := rs_id;
830 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
831 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
832 x_custom_output.extend;
833 x_custom_output(counter) := l_custom_rec;
834 counter:=counter+1;
835 end if;
836
837 if (rs_stage_req is not null AND rs_stage_req <> 'All') then
838 l_sql_stmt :=l_sql_stmt|| 'and S.request_id = :BIND_STAGEID ';
839 l_custom_rec.attribute_name := ':BIND_STAGEID';
840 l_custom_rec.attribute_value := rs_stage_req;
841 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
842 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
843 x_custom_output.extend;
844 x_custom_output(counter) := l_custom_rec;
845 counter:=counter+1;
846 end if;
847
848 if (rs_prog_req is not null AND rs_prog_req <> 'All') then
849 l_sql_stmt :=l_sql_stmt|| 'and P.program_id in (' ||rs_prog_req||') ';
850 end if;
851
852 if (rs_run_id is not null AND rs_run_id <> 'All') then
853 l_sql_stmt :=l_sql_stmt||'and R.request_id= :BIND_RUNID ';
854
855 l_custom_rec.attribute_name := ':BIND_RUNID';
856 l_custom_rec.attribute_value := rs_run_id;
857 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
858 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
859 x_custom_output.extend;
860 x_custom_output(counter) := l_custom_rec;
861 end if;
862
863 if (rs_sub_req is not null AND rs_sub_req <> 'All') then
864 l_sql_stmt :=l_sql_stmt||'and P.request_id IN ('||rs_sub_req ||') ';
865 end if;
866
867 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
868
869 --debug(l_sql_stmt);
870 x_custom_sql := l_sql_stmt;
871
872 END request_details_report ;
873
874
875 PROCEDURE request_set_space_rep
876 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
877 x_custom_sql OUT NOCOPY VARCHAR2,
878 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
879
880 l_sql_stmt VARCHAR2(32767);
881 l_custom_rec BIS_QUERY_ATTRIBUTES;
882 base VARCHAR2(257);
883 pname VARCHAR2(2000);
884 pvalue VARCHAR2(2000);
885 pid VARCHAR2(2000);
886 rs_id VARCHAR2(30);
887 rs_type VARCHAR2(30);
888 rs_latest_id VARCHAR2(30);
889 rs_stage_req varchar2(200);
890 rs_prog_req varchar2(2000);
891
892
893 counter number;
894
895
896 BEGIN
897
898 pname := NULL;
899 pvalue := NULL;
900 pid := NULL;
901 rs_id := NULL;
902 rs_type := NULL;
903 rs_latest_id := NULL;
904 rs_stage_req := NULL;
905 rs_prog_req := NULL;
906
907 counter :=1;
908
909 FOR counter IN 1..p_param.count LOOP
910 pname := p_param(counter).parameter_name;
911 pvalue := p_param(counter).parameter_value;
912 pid := p_param(counter).parameter_id;
913 -- debug( '( ' || pname || ', ' || pvalue ||', ' || pid || ' )' );
914
915 if pname = 'BIS_D_RS_NAME+BIS_D_RS_NAME' then
916 rs_id := pid ;
917 end if;
918
919 if pname = 'BISDRSSTAGE+BISDRSSTAGE' then
920 rs_stage_req := pid ;
921 end if;
922
923 if pname = 'BISDRSPROG+BISDRSPROG' then
924 rs_prog_req := pid ;
925 end if;
926
927 END LOOP;
928
929 counter := 1;
930 fnd_profile.get('APPS_FRAMEWORK_AGENT', base);
931
932 l_sql_stmt:='SELECT Distinct OBJECT_NAME BIS_REQUEST_OBJECT_NAME,
933 --BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) BIS_REQUEST_OBJECT_TYPE,
934 CASE O.OBJECT_TYPE WHEN ''MV_LOG'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
935 WHEN ''BSC_CUSTOM_KPI'' THEN BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning( ''BIS_OBJECT_TYPE_RPT'',O.OBJECT_TYPE)
936 ELSE BIS_REQUESTSET_VIEWHISTORY.get_bis_lookup_meaning( ''BIS_OBJECT_TYPE'',O.OBJECT_TYPE) END BIS_REQUEST_OBJECT_TYPE,
937 O.TABLESPACE_NAME BIS_TABLESPACE_NAME,
938 -- next two columns are not in use anymore..
939 0 BIS_TABLESPACE_SIZE,
940 0 BIS_TABLESPACE_FREE_SPACE,
941 OBJECT_SPACE_USAGE/(1024*1024) BIS_RS_TOTAL_SPACE_OCCUPIED,
942 object_row_count BIS_OBJECT_ROW_COUNT,
943 (OBJECT_SPACE_USAGE/TOTAL_SPACE.BYTES)*100 BIS_RS_PCT_SPACE_USED,
944 decode(O.OBJECT_TYPE,''MV_LOG'',NULL,''BSC_CUSTOM_KPI'',NULL,'||''''||
945 'pFunctionName=BIS_BIA_RSG_DEPENDENCIES_ALONE'
946 ||'&requestType=RSGReport&ObjType=''||o.object_type||''&ObjName=BIS_REQUEST_OBJECT_NAME''
947 ) BISREPORTURL
948 from BIS_OBJ_REFRESH_HISTORY O,
949 (select max(request_id) maxid, request_set_id rsid,request_set_type rstype
950 from bis_rs_run_history WHERE PHASE_CODE=''C'' AND (STATUS_CODE=''C''
951 OR STATUS_CODE =''G'')
952 group by request_set_id,request_set_type) Latestreq,
953 bis_rs_prog_run_history P, bis_rs_stage_run_history S,
954 (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
955 group by TABLESPACE_NAME) TOTAL_SPACE
956 where
957 Latestreq.maxid=S.set_request_id
958 and S.request_id= P.stage_request_id
959 and P.request_id=O.prog_request_id
960 AND TOTAL_SPACE.TABLESPACE_NAME=O.TABLESPACE_NAME ';
961
962 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
963 x_custom_output := bis_query_attributes_tbl();
964
965 if (rs_id is not null AND rs_id <> 'All') then
966 l_sql_stmt :=l_sql_stmt||'and Latestreq.rsid= :BIND_RSID ';
967
968 l_custom_rec.attribute_name := ':BIND_RSID';
969 l_custom_rec.attribute_value := rs_id;
970 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
971 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
972 x_custom_output.extend;
973 x_custom_output(counter) := l_custom_rec;
974 counter:=counter+1;
975 end if;
976
977 if (rs_stage_req is not null AND rs_stage_req <> 'All') then
978 l_sql_stmt :=l_sql_stmt|| 'and S.request_id = :BIND_STAGEID ';
979 l_custom_rec.attribute_name := ':BIND_STAGEID';
980 l_custom_rec.attribute_value := rs_stage_req;
981 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
982 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
983 x_custom_output.extend;
984 x_custom_output(counter) := l_custom_rec;
985 end if;
986
987 if (rs_prog_req is not null AND rs_prog_req <> 'All') then
988 l_sql_stmt :=l_sql_stmt|| 'and P.program_id in ('|| rs_prog_req||') ';
989 end if;
990
991 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
992
993 --debug(l_sql_stmt);
994 x_custom_sql := l_sql_stmt;
995
996 END request_set_space_rep ;
997
998 PROCEDURE tablespace_detail_report
999 ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1000 x_custom_sql OUT NOCOPY VARCHAR2,
1001 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1002
1003 l_sql_stmt VARCHAR2(32767);
1004 l_custom_rec BIS_QUERY_ATTRIBUTES;
1005 pname VARCHAR2(2000);
1006 pvalue VARCHAR2(2000);
1007 pid VARCHAR2(2000);
1008 ts_name VARCHAR2(30);
1009
1010 counter number;
1011
1012
1013 BEGIN
1014
1015 pname := NULL;
1016 pvalue := NULL;
1017 pid := NULL;
1018 ts_name := null;
1019
1020 counter :=1;
1021
1022 FOR counter IN 1..p_param.count LOOP
1023 pname := p_param(counter).parameter_name;
1024 pvalue := p_param(counter).parameter_value;
1025 pid := p_param(counter).parameter_id;
1026 --debug( '( ' || pname || ', ' || pvalue || ' )' );
1027
1028 if pname = 'BIS_TABLESPACE_NAME+BIS_TABLESPACE_NAME' then
1029 ts_name := pid ;
1030 end if;
1031
1032 END LOOP;
1033
1034 l_sql_stmt:='select DB_TS.TABLESPACE_NAME BIS_TS_NAME_PARAM,
1035 TOTAL_SPACE.BYTES/(1024*1024) BIS_TABLESPACE_SIZE,
1036 INITIAL_EXTENT/(1024*1024) BIS_TS_INIT_EXTENT,
1037 NEXT_EXTENT/(1024*1024) BIS_TS_NEXT_EXTENT,
1038 MAX_EXTENTS BIS_TS_MAX_EXTENT,
1039 free_space.BYTES/(1024*1024) BIS_TABLESPACE_FREE_SPACE
1040 from dba_tablespaces DB_TS,
1041 (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files
1042 group by TABLESPACE_NAME) TOTAL_SPACE,
1043 (select TABLESPACE_NAME, sum(BYTES) BYTES from dba_free_space
1044 group by TABLESPACE_NAME) FREE_SPACE
1045 WHERE free_space.tablespace_name=db_ts.tablespace_name
1046 AND total_space.tablespace_name=db_ts.tablespace_name
1047 AND db_ts.contents = ''PERMANENT'' ';
1048
1049 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1050 x_custom_output := bis_query_attributes_tbl();
1051
1052 if (ts_name is not null AND ts_name <> 'All') then
1053 l_sql_stmt :=l_sql_stmt||'and db_ts.tablespace_name= :BIND_NAME ';
1054
1055 l_custom_rec.attribute_name := ':BIND_NAME';
1056 l_custom_rec.attribute_value := ts_name;
1057 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1058 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
1059 x_custom_output.extend;
1060 x_custom_output(1) := l_custom_rec;
1061 end if;
1062
1063
1064 l_sql_stmt := l_sql_stmt || ' &' || 'ORDER_BY_CLAUSE NULLS LAST ';
1065
1066 --debug(l_sql_stmt);
1067 x_custom_sql := l_sql_stmt;
1068
1069 END tablespace_detail_report ;
1070
1071 FUNCTION gtitle(p_param BIS_PMV_PAGE_PARAMETER_TBL) return varchar2 IS
1072
1073 pname VARCHAR2(2000);
1074 pvalue VARCHAR2(2000);
1075 pid VARCHAR2(2000);
1076
1077 BEGIN
1078 pname := NULL;
1079 pvalue := NULL;
1080 pid := NULL;
1081
1082 FOR counter IN 1..p_param.count LOOP
1083 pname := p_param(counter).parameter_name;
1084 pvalue := p_param(counter).parameter_value;
1085 pid := p_param(counter).parameter_id;
1086
1087 IF ( UPPER(pname) LIKE '%VIEW_BY%') THEN
1088 IF( UPPER(pvalue) = 'BIS_D_RS_NAME+BIS_D_RS_NAME' ) THEN
1089 return BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning('BIS_BIA_RSG_REPORT','GRAPH_TITLE_NAME');
1090 END IF;
1091 IF( UPPER(pvalue) = 'BISDRSTYPE+BISDRSTYPE' ) THEN
1092 return BIS_REQUESTSET_VIEWHISTORY.get_sys_lookup_meaning('BIS_BIA_RSG_REPORT','GRAPH_TITLE_TYPE');
1093 END IF;
1094 END IF;
1095
1096 END LOOP;
1097
1098 END gtitle;
1099
1100 FUNCTION get_max_stg(prog_id Number,
1101 set_req_id Number,
1102 stage_id Varchar2) return Number IS
1103 l_stmt Varchar2(200);
1104 stg_req_id Number;
1105
1106 cursor get_max_stg (pid NUMBER ,srid NUMBER) is
1107 Select max(stage_request_id) from bis_rs_prog_run_history where program_id=pid
1108 and set_request_id= srid;
1109
1110 BEGIN
1111
1112 if(stage_id = 'ALL') then
1113 stg_req_id := NULL;
1114 open get_max_stg(prog_id,set_req_id);
1115 Fetch get_max_stg into stg_req_id;
1116 close get_max_stg;
1117 return stg_req_id;
1118 else
1119 return stage_id;
1120 end if;
1121
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 NULL;
1125 END get_max_stg;
1126
1127 FUNCTION get_latest_run(req_set_id Number) return Number IS
1128 l_stmt Varchar2(300);
1129 Latest_run_id Number;
1130
1131 cursor get_latest_run (rsid NUMBER) is
1132 select max(request_id) maxid
1133 from bis_rs_run_history WHERE PHASE_CODE='C' AND (STATUS_CODE='C'
1134 OR STATUS_CODE ='G') and request_set_id= rsid
1135 group by request_set_id,request_set_type;
1136
1137
1138 BEGIN
1139
1140 Latest_run_id := NULL;
1141 open get_latest_run(req_set_id);
1142 Fetch get_latest_run into Latest_run_id;
1143 close get_latest_run;
1144
1145
1146 return Latest_run_id;
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 NULL;
1150 END get_latest_run;
1151
1152 FUNCTION Check_rsid(req_set_id Number) return Number IS
1153 l_stmt Varchar2(300);
1154
1155 ret_val Number;
1156 cursor check_rsid (rsid NUMBER) is
1157 select 1 from dual where exists
1158 (select r.request_set_id from bis_rs_run_history r, bis_rs_prog_run_history p,
1159 bis_obj_refresh_history o
1160 where
1161 r.phase_code ='C' AND (r.STATUS_CODE='C' OR r.STATUS_CODE ='G')
1162 and p.set_request_id=r.Request_id
1163 and p.request_id = o.prog_request_id and r.request_set_id= rsid);
1164
1165 BEGIN
1166
1167 ret_val := 0;
1168 open check_rsid(req_set_id);
1169 Fetch check_rsid into ret_val;
1170 close check_rsid;
1171
1172 return ret_val;
1173
1174 END Check_rsid;
1175
1176 END;