[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TECH_UTL_RPT_PKG
Source
1 PACKAGE body ISC_FS_TECH_UTL_RPT_PKG AS
2 /*$Header: iscfstutlrptb.pls 120.1 2005/11/24 18:36:07 kreardon noship $ */
3 function get_fact_mv_name
4 ( p_report_type in varchar2
5 , p_param in bis_pmv_page_parameter_tbl
6 , p_dim_bmap in number
7 , p_custom_output in out nocopy bis_query_attributes_tbl
8 )
9 return varchar2
10 is
11 begin
12
13 if p_report_type = 'ACTUAL_HRS' then
14
15 isc_fs_rpt_util_pkg.bind_group_id
16 ( p_dim_bmap
17 , p_custom_output
18 , isc_fs_rpt_util_pkg.G_TASK_TYPE
19 );
20
21 -- R12 resource type
22 return '(
23 select
24 f.record_type
25 ,f.parent_district_id
26 ,f.district_id
27 ,district_id_c
28 ,f.task_type_id
29 ,f.time_id
30 ,f.period_type_id
31 ,f.actual_effort_hrs Labor_Hrs
32 ,f.actual_travel_duration_hrs Travel_Hrs
33 ,nvl(f.actual_effort_hrs,0) + nvl(f.actual_travel_duration_hrs,0) Total_Act_Hrs
34 from isc_fs_013_mv f
35 where f.grp_id = &ISC_GRP_ID)';
36
37 elsif p_report_type = 'PLANNED_HRS' then
38
39 -- R12 resource type
40 return '(
41 select
42 f.record_type
43 ,f.parent_district_id
44 ,f.district_id
45 ,district_id_c
46 ,-999 task_type_id
47 ,f.time_id
48 ,f.period_type_id
49 ,f.planned_hrs Total_Planned_Hrs
50 from isc_fs_014_mv f)';
51
52 else -- should not happen!!!
53 return '';
54
55 end if;
56
57 end get_fact_mv_name;
58
59
60 procedure get_tbl_sql
61 ( p_param in bis_pmv_page_parameter_tbl
62 , x_custom_sql out nocopy varchar2
63 , x_custom_output out nocopy bis_query_attributes_tbl
64 )
65 is
66
67 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
68 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
69 l_custom_output bis_query_attributes_tbl;
70 l_curr_suffix varchar2(3);
71 l_where_clause varchar2(10000);
72 l_viewby_select varchar2(400); -- needed to be increased from 200
73 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
74 l_dim_bmap number;
75 l_comparison_type varchar2(200);
76 l_xtd varchar2(200);
77
78 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
79 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
80
81 l_mv VARCHAR2 (10000);
82 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
83 l_view_by varchar2(200);
84
85 l_stmt varchar2(32700);
86 l_cur_planned_hours varchar2(35);
87 l_prev_planned_hours varchar2(35);
88 l_to_date_type varchar2(20);
89
90 begin
91
92 isc_fs_rpt_util_pkg.register_dimension_levels
93 ( l_dimension_tbl
94 , l_dim_filter_map
95 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
96 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
97 );
98
99 isc_fs_rpt_util_pkg.check_district_filter
100 ( p_param
101 , l_dim_filter_map
102 );
103
104 isc_fs_rpt_util_pkg.process_parameters
105 ( p_param => p_param
106 , p_dimension_tbl => l_dimension_tbl
107 , p_dim_filter_map => l_dim_filter_map
108 , p_trend => 'N'
109 , p_custom_output => l_custom_output
110 , x_cur_suffix => l_curr_suffix
111 , x_where_clause => l_where_clause
112 , x_viewby_select => l_viewby_select
113 , x_join_tbl => l_join_tbl
114 , x_dim_bmap => l_dim_bmap
115 , x_comparison_type => l_comparison_type
116 , x_xtd => l_xtd
117 );
118
119 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
120 l_to_date_type := 'XTD';
121 else
122 l_to_date_type := 'RLX';
123 end if;
124
125 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
126 ( p_param
127 , 'VIEW_BY'
128 );
129
130 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl();
131 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl();
132 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
133
134 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
135 , p_col_name => 'Labor_Hrs'
136 , p_alias_name => 'Labor_Hrs'
137 , p_grand_total => 'Y'
138 , p_prior_code => poa_dbi_util_pkg.both_priors
139 , p_to_date_type => l_to_date_type
140 );
141
142 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl1
143 , p_col_name => 'Travel_Hrs'
144 , p_alias_name => 'Travel_Hrs'
145 , p_grand_total => 'Y'
146 , p_prior_code => poa_dbi_util_pkg.both_priors
147 , p_to_date_type => l_to_date_type
148 );
149
150 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl1
151 , p_col_name => 'Total_Act_Hrs'
152 , p_alias_name => 'Total_Act_Hrs'
153 , p_grand_total => 'Y'
154 , p_prior_code => poa_dbi_util_pkg.both_priors
155 , p_to_date_type => l_to_date_type
156 );
157
158 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl2
159 , p_col_name => 'Total_Planned_Hrs'
160 , p_alias_name => 'Total_Planned_Hrs'
161 , p_grand_total => 'Y'
162 , p_prior_code => poa_dbi_util_pkg.both_priors
163 , p_to_date_type => l_to_date_type
164 );
165
166 l_mv_tbl.extend;
167 l_mv_tbl(1).mv_name := 'MV_PLACEHOLDER1';
168 l_mv_tbl(1).mv_col := l_col_tbl1;
169 l_mv_tbl(1).mv_where := l_where_clause;
170 l_mv_tbl(1).in_join_tbls := NULL;
171 l_mv_tbl(1).use_grp_id := 'N';
172
173 l_mv_tbl.extend;
174 l_mv_tbl(2).mv_name := 'MV_PLACEHOLDER2';
175 l_mv_tbl(2).mv_col := l_col_tbl2;
176 l_mv_tbl(2).mv_where := replace(l_where_clause, '&' || isc_fs_rpt_util_pkg.G_TASK_TYPE, '-999');
177 l_mv_tbl(2).in_join_tbls := NULL;
178 l_mv_tbl(2).use_grp_id := 'N';
179
180 l_stmt := poa_dbi_template_pkg.union_all_status_sql
181 (p_mv => l_mv_tbl
182 ,p_join_tables => l_join_tbl
183 ,p_use_windowing => 'Y'
184 ,p_paren_count => 3
185 ,p_filter_where => ' (ISC_Measure_1 > 0 or ISC_Measure_2 > 0 or ISC_Measure_3 > 0 or ISC_Measure_13 > 0 or ISC_Measure_6 > 0 ' ||
186 'or ISC_Measure_10 > 0 or ISC_Measure_5 > 0 or ISC_Measure_7 > 0 or ISC_Measure_8 > 0 or ISC_Measure_9 > 0 ' ||
187 'or ISC_Measure_11 > 0 or ISC_Measure_12 > 0 ' || ') ) iset'
188 ,p_generate_viewby => 'Y'
189 );
190
191 l_mv := get_fact_mv_name
192 ( 'ACTUAL_HRS'
193 , p_param
194 , l_dim_bmap
195 , l_custom_output
196 );
197
198 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER1', l_mv );
199
200 l_mv := get_fact_mv_name
201 ( 'PLANNED_HRS'
202 , p_param
203 , l_dim_bmap
204 , l_custom_output
205 );
206
207 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER2', l_mv );
208
209 if l_view_by = isc_fs_rpt_util_pkg.G_TASK_TYPE then /* If View by Task Type, Total Plan hours are distributed to each task type */
210 l_cur_planned_hours := 'c_Total_Planned_Hrs_total' ;
211 l_prev_planned_hours := 'p_Total_Planned_Hrs_total';
212 else
213 l_cur_planned_hours := 'c_Total_Planned_Hrs' ;
214 l_prev_planned_hours := 'p_Total_Planned_Hrs';
215 end if;
216
217
218 l_stmt := 'select
219 ' || l_viewby_select || '
220 , ISC_MEASURE_1
221 , ISC_MEASURE_2
222 , ISC_MEASURE_3
223 , ISC_MEASURE_13
224 , ISC_MEASURE_6
225 , ISC_MEASURE_10
226 , ISC_MEASURE_5
227 , ISC_MEASURE_7
228 , ISC_MEASURE_8
229 , ISC_MEASURE_9
230 , ISC_MEASURE_11
231 , ISC_MEASURE_12
232 , ISC_MEASURE_15
233 , ISC_MEASURE_16
234 , ISC_MEASURE_17
235 , ISC_MEASURE_24
236 , ISC_MEASURE_18
237 , ISC_MEASURE_19
238 , ISC_MEASURE_20
239 , ISC_MEASURE_21
240 , ISC_MEASURE_22
241 , ISC_MEASURE_23
242 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
243 ( l_view_by
244 , 'ISC_FS_TECH_UTL_TBL_REP'
245 , 'ISC_ATTRIBUTE_2' ) || '
246 from (
247 select
248 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
249 , iset.*
250 from ( select * from (
251 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
252 , ' || isc_fs_rpt_util_pkg.rate_column
253 ( 'p_Total_Act_Hrs'
254 , l_prev_planned_hours
255 , 'ISC_MEASURE_1'
256 ) || '
257 , ' || isc_fs_rpt_util_pkg.rate_column
258 ( 'c_Total_Act_Hrs'
259 , l_cur_planned_hours
260 , 'ISC_MEASURE_2'
261 ) || '
262 , ' || isc_fs_rpt_util_pkg.change_column
263 ( isc_fs_rpt_util_pkg.rate_column
264 ( 'c_Total_Act_Hrs'
265 , l_cur_planned_hours
266 , null
267 )
268 , isc_fs_rpt_util_pkg.rate_column
269 ( 'p_Total_Act_Hrs'
270 , l_prev_planned_hours
271 , null
272 )
273 , 'ISC_MEASURE_3'
274 , 'N'
275 ) || '
276 , nvl(c_Total_Planned_Hrs,0) ISC_MEASURE_13
277 , ' || isc_fs_rpt_util_pkg.rate_column
278 ( 'p_Labor_Hrs'
279 , l_prev_planned_hours
280 , 'ISC_MEASURE_6'
281 ) || '
282 , ' || isc_fs_rpt_util_pkg.rate_column
283 ( 'p_Travel_Hrs'
284 , l_cur_planned_hours
285 , 'ISC_MEASURE_10'
286 ) || '
287 , nvl(c_Labor_Hrs,0) ISC_MEASURE_5
288 , ' || isc_fs_rpt_util_pkg.rate_column
289 ( 'c_Labor_Hrs'
290 , l_cur_planned_hours
291 , 'ISC_MEASURE_7'
292 ) || '
293 , ' || isc_fs_rpt_util_pkg.change_column
294 ( isc_fs_rpt_util_pkg.rate_column
295 ( 'c_Labor_Hrs'
296 , l_cur_planned_hours
297 , null
298 )
299 , isc_fs_rpt_util_pkg.rate_column
300 ( 'p_Labor_Hrs'
301 , l_prev_planned_hours
302 , null
303 )
304 , 'ISC_MEASURE_8'
305 , 'N'
306 ) || '
307 , nvl(c_Travel_Hrs,0) ISC_MEASURE_9
308 , ' || isc_fs_rpt_util_pkg.rate_column
309 ( 'c_Travel_Hrs'
310 , l_cur_planned_hours
311 , 'ISC_MEASURE_11'
312 ) || '
313 , ' || isc_fs_rpt_util_pkg.change_column
314 ( isc_fs_rpt_util_pkg.rate_column
315 ( 'c_Travel_Hrs'
316 , l_cur_planned_hours
317 , null
318 )
319 , isc_fs_rpt_util_pkg.rate_column
320 ( 'p_Travel_Hrs'
321 , l_prev_planned_hours
322 , null
323 )
324 , 'ISC_MEASURE_12'
325 , 'N'
326 ) || '
327 , ' || isc_fs_rpt_util_pkg.rate_column
328 ( 'p_Total_Act_Hrs_total'
329 , 'p_Total_Planned_Hrs_total'
330 , 'ISC_MEASURE_15'
331 ) || '
332 , ' || isc_fs_rpt_util_pkg.rate_column
333 ( 'c_Total_Act_Hrs_total'
334 , 'c_Total_Planned_Hrs_total'
335 , 'ISC_MEASURE_16'
336 ) || '
337 , ' || isc_fs_rpt_util_pkg.change_column
338 ( isc_fs_rpt_util_pkg.rate_column
339 ( 'c_Total_Act_Hrs_total'
340 , 'c_Total_Planned_Hrs_total'
341 , null
342 )
343 , isc_fs_rpt_util_pkg.rate_column
344 ( 'p_Total_Act_Hrs_total'
345 , 'p_Total_Planned_Hrs_total'
346 , null
347 )
348 , 'ISC_MEASURE_17'
349 , 'N'
350 ) || '
351 , nvl(c_Total_Planned_Hrs_total,0) ISC_MEASURE_24
352 , nvl(c_Labor_Hrs_total,0) ISC_MEASURE_18
353 , ' || isc_fs_rpt_util_pkg.rate_column
354 ( 'c_Labor_Hrs_total'
355 , 'c_Total_Planned_Hrs_total'
356 , 'ISC_MEASURE_19'
357 ) || '
358 , ' || isc_fs_rpt_util_pkg.change_column
359 ( isc_fs_rpt_util_pkg.rate_column
360 ( 'c_Labor_Hrs_total'
361 , 'c_Total_Planned_Hrs_total'
362 , null
363 )
364 , isc_fs_rpt_util_pkg.rate_column
365 ( 'p_Labor_Hrs_total'
366 , 'p_Total_Planned_Hrs_total'
367 , null
368 )
369 , 'ISC_MEASURE_20'
370 , 'N'
371 ) || '
372 , nvl(c_Travel_Hrs_total,0) ISC_MEASURE_21
373 , ' || isc_fs_rpt_util_pkg.rate_column
374 ( 'c_Travel_Hrs_total'
375 , 'c_Total_Planned_Hrs_total'
376 , 'ISC_MEASURE_22'
377 ) || '
378 , ' || isc_fs_rpt_util_pkg.change_column
379 ( isc_fs_rpt_util_pkg.rate_column
380 ( 'c_Travel_Hrs_total'
381 , 'c_Total_Planned_Hrs_total'
382 , null
383 )
384 , isc_fs_rpt_util_pkg.rate_column
385 ( 'p_Travel_Hrs_total'
386 , 'p_Total_Planned_Hrs_total'
387 , null
388 )
389 , 'ISC_MEASURE_23'
390 , 'N'
391 ) || '
392 from (' || l_stmt;
393
394
395 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
396 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
397
398 poa_dbi_util_pkg.get_custom_rolling_binds
399 ( p_custom_output => l_custom_output
400 , p_xtd => l_xtd
401 );
402
403 x_custom_output := l_custom_output;
404
405 isc_fs_rpt_util_pkg.enhance_time_join
406 ( l_stmt
407 , 'N'
408 );
409
410 x_custom_sql := l_stmt;
411
412 end get_tbl_sql;
413
414
415 procedure get_trd_sql
416 ( p_param in bis_pmv_page_parameter_tbl
417 , x_custom_sql out nocopy varchar2
418 , x_custom_output out nocopy bis_query_attributes_tbl
419 )
420 is
421
422 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
423 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
424 l_custom_output bis_query_attributes_tbl;
425 l_curr_suffix varchar2(3);
426 l_where_clause varchar2(10000);
427 l_viewby_select varchar2(400); -- needed to be increased from 200
428 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
429 l_dim_bmap number;
430 l_comparison_type varchar2(200);
431 l_xtd varchar2(200);
432 l_mv varchar2(10000);
433 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
434 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
435 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
436 l_col_tbl3 poa_dbi_util_pkg.poa_dbi_col_tbl;
437 l_stmt varchar2(32767);
438 l_to_date_type varchar2(20);
439
440 begin
441
442 isc_fs_rpt_util_pkg.register_dimension_levels
443 ( l_dimension_tbl
444 , l_dim_filter_map
445 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
446 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
447 );
448
449 isc_fs_rpt_util_pkg.check_district_filter
450 ( p_param
451 , l_dim_filter_map
452 );
453
454 isc_fs_rpt_util_pkg.process_parameters
455 ( p_param => p_param
456 , p_dimension_tbl => l_dimension_tbl
457 , p_dim_filter_map => l_dim_filter_map
458 , p_trend => 'Y'
459 , p_custom_output => l_custom_output
460 , x_cur_suffix => l_curr_suffix
461 , x_where_clause => l_where_clause
462 , x_viewby_select => l_viewby_select
463 , x_join_tbl => l_join_tbl
464 , x_dim_bmap => l_dim_bmap
465 , x_comparison_type => l_comparison_type
466 , x_xtd => l_xtd
467 );
468
469 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
470 l_to_date_type := 'XTD';
471 else
472 l_to_date_type := 'RLX';
473 end if;
474
475 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl();
476 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl();
477
478 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
479
480
481 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
482 , p_col_name => 'Labor_Hrs'
483 , p_alias_name => 'Labor_Hrs'
484 , p_grand_total => 'N'
485 , p_prior_code => poa_dbi_util_pkg.both_priors
486 , p_to_date_type => l_to_date_type
487 );
488
489 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl1
490 , p_col_name => 'Travel_Hrs'
491 , p_alias_name => 'Travel_Hrs'
492 , p_grand_total => 'N'
493 , p_prior_code => poa_dbi_util_pkg.both_priors
494 , p_to_date_type => l_to_date_type
495 );
496
497 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl1
498 , p_col_name => 'Total_Act_Hrs'
499 , p_alias_name => 'Total_Act_Hrs'
500 , p_grand_total => 'N'
501 , p_prior_code => poa_dbi_util_pkg.both_priors
502 , p_to_date_type => l_to_date_type
503 );
504
505 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl2
506 , p_col_name => 'Total_Planned_Hrs'
507 , p_alias_name => 'Total_Planned_Hrs'
508 , p_grand_total => 'N'
509 , p_prior_code => poa_dbi_util_pkg.both_priors
510 , p_to_date_type => l_to_date_type
511 );
512
513 l_mv_tbl.extend;
514 l_mv_tbl(1).mv_name := 'MV_PLACEHOLDER1';
515 l_mv_tbl(1).mv_col := l_col_tbl1;
516 l_mv_tbl(1).mv_where := l_where_clause;
517 l_mv_tbl(1).in_join_tbls := NULL;
518 l_mv_tbl(1).use_grp_id := 'N';
519 l_mv_tbl(1).mv_xtd := l_xtd;
520
521 l_mv_tbl.extend;
522 l_mv_tbl(2).mv_name := 'MV_PLACEHOLDER2';
523 l_mv_tbl(2).mv_col := l_col_tbl2;
524 l_mv_tbl(2).mv_where := replace(l_where_clause, '&' || isc_fs_rpt_util_pkg.G_TASK_TYPE, '-999');
525 l_mv_tbl(2).in_join_tbls := NULL;
526 l_mv_tbl(2).use_grp_id := 'N';
527 l_mv_tbl(2).mv_xtd := l_xtd;
528
529 l_stmt := poa_dbi_template_pkg.union_all_trend_sql(
530 p_mv => l_mv_tbl,
531 p_comparison_type => l_comparison_type,
532 p_filter_where => NULL
533 );
534
535
536 l_mv := get_fact_mv_name
537 ( 'ACTUAL_HRS'
538 , p_param
539 , l_dim_bmap
540 , l_custom_output
541 );
542
543 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER1', l_mv );
544
545 l_mv := get_fact_mv_name
546 ( 'PLANNED_HRS'
547 , p_param
548 , l_dim_bmap
549 , l_custom_output
550 );
551
552 l_stmt := replace( l_stmt, 'MV_PLACEHOLDER2', l_mv );
553
554 l_stmt := 'select
555 cal_name VIEWBY ' || '
556 , ' || isc_fs_rpt_util_pkg.rate_column
557 ( 'p_Total_Act_Hrs'
558 , 'p_Total_Planned_Hrs'
559 , 'ISC_MEASURE_1'
560 ) || '
561 , ' || isc_fs_rpt_util_pkg.rate_column
562 ( 'c_Total_Act_Hrs'
563 , 'c_Total_Planned_Hrs'
564 , 'ISC_MEASURE_2'
565 ) || '
566 , ' || isc_fs_rpt_util_pkg.change_column
567 ( isc_fs_rpt_util_pkg.rate_column
568 ( 'c_Total_Act_Hrs'
569 , 'c_Total_Planned_Hrs'
570 , null
571 )
572 , isc_fs_rpt_util_pkg.rate_column
573 ( 'p_Total_Act_Hrs'
574 , 'p_Total_Planned_Hrs'
575 , null
576 )
577 , 'ISC_MEASURE_3'
578 , 'N'
579 ) || '
580 , ' || isc_fs_rpt_util_pkg.rate_column
581 ( 'p_Labor_Hrs'
582 , 'p_Total_Planned_Hrs'
583 , 'ISC_MEASURE_6'
584 ) || '
585 , ' || isc_fs_rpt_util_pkg.rate_column
586 ( 'c_Labor_Hrs'
587 , 'c_Total_Planned_Hrs'
588 , 'ISC_MEASURE_7'
589 ) || '
590 , ' || isc_fs_rpt_util_pkg.change_column
591 ( isc_fs_rpt_util_pkg.rate_column
592 ( 'c_Labor_Hrs'
593 , 'c_Total_Planned_Hrs'
594 , null
595 )
596 , isc_fs_rpt_util_pkg.rate_column
597 ( 'p_Labor_Hrs'
598 , 'p_Total_Planned_Hrs'
599 , null
600 )
601 , 'ISC_MEASURE_8'
602 , 'N'
603 ) || '
604 , ' || isc_fs_rpt_util_pkg.rate_column
605 ( 'p_Travel_Hrs'
606 , 'p_Total_Planned_Hrs'
607 , 'ISC_MEASURE_10'
608 ) || '
609 , ' || isc_fs_rpt_util_pkg.rate_column
610 ( 'c_Travel_Hrs'
611 , 'c_Total_Planned_Hrs'
612 , 'ISC_MEASURE_11'
613 ) || '
614 , ' || isc_fs_rpt_util_pkg.change_column
615 ( isc_fs_rpt_util_pkg.rate_column
616 ( 'c_Travel_Hrs'
617 , 'c_Total_Planned_Hrs'
618 , null
619 )
620 , isc_fs_rpt_util_pkg.rate_column
621 ( 'p_Travel_Hrs'
622 , 'p_Total_Planned_Hrs'
623 , null
624 )
625 , 'ISC_MEASURE_12'
626 , 'N'
627 ) ||
628 isc_fs_rpt_util_pkg.get_trend_drill
629 ( l_xtd
630 , 'ISC_FS_TECH_UTL_TRD_REP'
631 , 'ISC_ATTRIBUTE_1'
632 , 'ISC_ATTRIBUTE_2'
633 , p_override_end_date => 'cal_end_date'
634 ) || '
635 from
636 ' || l_stmt;
637
638 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
639 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
640
641 poa_dbi_util_pkg.get_custom_rolling_binds
642 ( p_custom_output => l_custom_output
643 , p_xtd => l_xtd
644 );
645
646 x_custom_output := l_custom_output;
647
648 poa_dbi_util_pkg.get_custom_trend_binds
649 ( x_custom_output => l_custom_output
650 , p_xtd => l_xtd
651 , p_comparison_type => l_comparison_type
652 );
653
654 if l_custom_output is not null then
655 for i in 1..l_custom_output.count loop
656 x_custom_output.extend;
657 x_custom_output(x_custom_output.count) := l_custom_output(i);
658 end loop;
659 end if;
660
661 l_stmt := replace( l_stmt, ', cal.start_date cal_start_date', ', cal.start_date cal_start_date, cal.end_date cal_end_date');
662 l_stmt := replace( l_stmt, ', cal_start_date', ', cal_start_date, cal_end_date');
663 -- l_stmt := replace( l_stmt, 'group by cal_name, cal_start_date', 'group by cal_name, cal_start_date, cal_end_date');
664
665 isc_fs_rpt_util_pkg.enhance_time_join
666 ( l_stmt
667 , 'Y'
668 );
669
670 x_custom_sql := l_stmt;
671
672 end get_trd_sql;
673
674
675
676 END ISC_FS_TECH_UTL_RPT_PKG;