[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_FTFR_RPT_PKG
Source
1 package body isc_fs_ftfr_rpt_pkg
2 /* $Header: iscfsftfrrptb.pls 120.4 2006/04/12 20:44:04 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_NFTF_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_FTFR_TBL_REP';
7 g_trd_rep_func constant varchar2(50) := 'ISC_FS_FTFR_TRD_REP';
8
9 g_dual_view_by varchar2(1);
10
11 function is_dual_view_by
12 ( p_param in bis_pmv_page_parameter_tbl
13 )
14 return varchar2
15 is
16
17 cursor c_bua(b_session number) is
18 select session_value
19 from bis_user_attributes
20 where user_id = fnd_global.user_id
21 and session_id = b_session
22 and function_name = g_detail_rep_func
23 and attribute_name = 'VIEW_BY';
24
25 l_session_id number;
26 l_bua_view_by varchar2(100);
27
28 begin
29
30 l_session_id := isc_fs_rpt_util_pkg.get_parameter_value
31 ( p_param
32 , 'BIS_ICX_SESSION_ID'
33 );
34
35 open c_bua( l_session_id );
36 fetch c_bua into l_bua_view_by;
37 close c_bua;
38
39 return
40 case
41 when l_bua_view_by like '%-%' then 'Y'
42 else 'N'
43 end;
44
45 end is_dual_view_by;
46
47 function get_fact_mv_name
48 ( p_report_type in varchar2
49 , p_param in bis_pmv_page_parameter_tbl
50 , p_dim_bmap in number
51 , p_custom_output in out nocopy bis_query_attributes_tbl
52 )
53 return varchar2
54 is
55
56 l_top_node varchar2(1);
57 l_resource varchar2(1);
58 l_alias varchar2(3);
59 l_district_leaf_node varchar2(1);
60
61 begin
62
63 if nvl(isc_fs_rpt_util_pkg.get_parameter_value
64 ( p_param
65 , isc_fs_rpt_util_pkg.G_CATEGORY
66 ),'All') = 'All' then
67 l_top_node := 'Y';
68 else
69 l_top_node := 'N';
70 end if;
71
72 if p_report_type = 'FTFR' then
73
74 l_district_leaf_node := isc_fs_rpt_util_pkg.is_district_leaf_node
75 ( p_param );
76
77 isc_fs_rpt_util_pkg.bind_group_id
78 ( p_dim_bmap
79 , p_custom_output
80 , isc_fs_rpt_util_pkg.G_CATEGORY
81 , isc_fs_rpt_util_pkg.G_PRODUCT
82 , isc_fs_rpt_util_pkg.G_CUSTOMER
83 );
84
85 if bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
86
87 return '(
88 select
89 v.top_node_flag vbh_top_node_flag
90 , v.parent_id vbh_parent_category_id
91 , v.imm_child_id vbh_child_category_id
92 , f.time_id
93 , f.period_type_id ' || case
94 when l_district_leaf_node = 'N' then '
95 , den.parent_prg_id parent_district_id
96 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
97 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
98 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
99 else '
100 , f.parent_district_id
101 , f.record_type
102 , f.district_id
103 , f.district_id_c'
104 end || '
105 , f.customer_id
106 , f.product_id
107 , f.incident_severity_id
108 , f.ftf_count
109 , f.non_ftf_count
110 , nvl(f.ftf_count,0) + nvl(non_ftf_count,0) sr_count
111 from
112 isc_fs_011_mv f' || case
113 when l_district_leaf_node = 'N' then '
114 , isc_fs_002_mv den'
115 end || '
116 , eni_denorm_hierarchies v
117 , mtl_default_category_sets m
118 where
119 m.functional_area_id = 11
120 and v.object_id = m.category_set_id
121 and v.dbi_flag = ''Y''
122 and v.object_type = ''CATEGORY_SET''
123 and f.vbh_category_id = v.child_id' || case
124 when l_district_leaf_node = 'N' then '
125 and f.parent_district_id = den.rg_id'
126 end || '
127 and f.grp_id = &ISC_GRP_ID' || case
128 when l_top_node = 'Y' then '
129 and v.top_node_flag = ''Y''' end || '
130 )';
131
132 elsif bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_PRODUCT_BMAP) = isc_fs_rpt_util_pkg.G_PRODUCT_BMAP or
133 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP) = isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP or
134 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_SEVERITY_BMAP) = isc_fs_rpt_util_pkg.G_SEVERITY_BMAP then
135
136 return '(
137 select
138 f.time_id
139 , f.period_type_id ' || case
140 when l_district_leaf_node = 'N' then '
141 , den.parent_prg_id parent_district_id
142 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
143 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
144 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
145 else '
146 , f.parent_district_id
147 , f.record_type
148 , f.district_id
149 , f.district_id_c'
150 end || '
151 , f.customer_id
152 , f.product_id
153 , f.incident_severity_id
154 , f.ftf_count
155 , f.non_ftf_count
156 , nvl(f.ftf_count,0) + nvl(non_ftf_count,0) sr_count
157 from
158 isc_fs_011_mv f' || case
159 when l_district_leaf_node = 'N' then '
160 , isc_fs_002_mv den'
161 end || '
162 where
163 f.grp_id = &ISC_GRP_ID ' || case
164 when l_district_leaf_node = 'N' then '
165 and f.parent_district_id = den.rg_id'
166 end || '
167 )';
168 else
169
170 return '(
171 select
172 f.time_id
173 , f.period_type_id
174 , f.parent_district_id
175 , f.record_type
176 , f.district_id
177 , f.district_id_c
178 , f.ftf_count
179 , f.non_ftf_count
180 , nvl(f.ftf_count,0) + nvl(non_ftf_count,0) sr_count
181 from isc_fs_012_mv f
182 )';
183 end if;
184
185 elsif p_report_type = 'NFTF_DETAIL' then
186
187 g_dual_view_by := is_dual_view_by(p_param);
188 if g_dual_view_by = 'Y' then
189 l_alias := 't2';
190 else
191 l_alias := 't';
192 end if;
193
194 if isc_fs_rpt_util_pkg.get_parameter_id
195 ( p_param
196 , isc_fs_rpt_util_pkg.G_DISTRICT
197 ) like '%.%' then
198 l_resource := 'Y';
199 else
200 l_resource := 'N';
201 end if;
202
203 return '(
204 select
205 t.task_id
206 , t.task_number
207 , ' || l_alias || '.ftf_assignee_id assignee_id
208 , ' || l_alias || '.ftf_assignee_type assignee_type ' ||
209 case
210 when l_resource = 'N' then '
211 , d.parent_prg_id parent_district_id '
212 else
213 case
214 when g_dual_view_by = 'Y' then '
215 , decode( t2.ftf_ttr_district_rule, ''0'', t2.ftf_assignee_id, t2.owner_id ) || ''.'' || decode( t2.ftf_ttr_district_rule, ''0'', t2.ftf_district_id, t2.owner_district_id ) district_id_c '
216 else '
217 , decode( t.ftf_ttr_district_rule, ''0'', t.ftf_assignee_id, t.owner_id ) || ''.'' || decode( t.ftf_ttr_district_rule, ''0'', t.ftf_district_id, t.owner_district_id ) district_id_c '
218 end
219 end || '
220 , t.source_object_name
221 , t.source_object_id
222 , t.task_status_id
223 , t.task_type_id
224 , r.customer_id
225 , r.report_date
226 , t.actual_start_date
227 , t.actual_end_date
228 , t.actual_effort_hrs
229 , nvl(s.master_id,s.id) product_id ' ||
230 case
231 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
232 '
233 , v.top_node_flag vbh_top_node_flag
237 , t.owner_id
234 , v.parent_id vbh_parent_category_id
235 , v.imm_child_id vbh_child_category_id'
236 end || '
238 , t.owner_type
239 , r.incident_type_id
240 , null incident_status_id' -- this should be r.incident_status_id but the column is not in the table as yet
241 || '
242 , r.incident_owner_id
243 , r.incident_severity_id
244 from
245 isc_fs_tasks_f t' ||
246 case
247 when g_dual_view_by = 'Y' then '
248 , isc_fs_tasks_f t2'
249 end || '
250 , biv_dbi_resolution_sum_f r
251 , eni_oltp_item_star s' ||
252 case
253 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
254 '
255 , eni_denorm_hierarchies v
256 , mtl_default_category_sets m'
257 end ||
258 case
259 when l_resource = 'N' then '
260 , isc_fs_002_mv d'
261 end || '
262 where
263 t.include_task_in_ftf_flag = ''Y''
264 and r.time_to_resolution is not null' ||
265 case
266 when g_dual_view_by = 'Y' then '
267 and t2.include_task_in_ftf_flag = ''Y''
268 and t2.ftf_flag = ''N''
269 and t2.source_object_id = r.incident_id
270 and t2.source_object_id = t.source_object_id '
271 else '
272 and t.ftf_flag = ''N''
273 and t.source_object_id = r.incident_id
274 ' end || '
275 and r.inventory_item_id = s.inventory_item_id
276 and r.inv_organization_id = s.organization_id' ||
277 case
278 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
279 '
280 and m.functional_area_id = 11
281 and v.object_id = m.category_set_id
282 and v.dbi_flag = ''Y''
283 and v.object_type = ''CATEGORY_SET''
284 and s.vbh_category_id = v.child_id' ||
285 case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
286 end ||
287 case
288 when l_resource = 'N' then
289 case
290 when g_dual_view_by = 'Y' then '
291 and d.rg_id = decode( t2.ftf_ttr_district_rule, ''0'', t2.ftf_district_id, t2.owner_district_id )'
292 else '
293 and d.rg_id = decode( t.ftf_ttr_district_rule, ''0'', t.ftf_district_id, t.owner_district_id )'
294 end
295 end || '
296 )';
297
298 else -- should not happen!!!
299 return '';
300
301 end if;
302
303 end get_fact_mv_name;
304
305 procedure get_tbl_sql
306 ( p_param in bis_pmv_page_parameter_tbl
307 , x_custom_sql out nocopy varchar2
308 , x_custom_output out nocopy bis_query_attributes_tbl
309 )
310 is
311
312 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
313 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
314 l_custom_output bis_query_attributes_tbl;
315 l_curr_suffix varchar2(3);
316 l_where_clause varchar2(10000);
317 l_viewby_select varchar2(400); -- needed to be increased from 200
318 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
319 l_dim_bmap number;
320 l_comparison_type varchar2(200);
321 l_xtd varchar2(200);
322 l_mv varchar2(10000);
323 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
324 l_view_by varchar2(200);
325 l_product varchar2(50);
326 l_stmt varchar2(32700);
327 l_to_date_type varchar2(200);
328
329 begin
330
331 isc_fs_rpt_util_pkg.register_dimension_levels
332 ( l_dimension_tbl
333 , l_dim_filter_map
334 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
335 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
336 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
337 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
338 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
339 );
340
341 isc_fs_rpt_util_pkg.process_parameters
342 ( p_param => p_param
343 , p_dimension_tbl => l_dimension_tbl
344 , p_dim_filter_map => l_dim_filter_map
345 , p_trend => 'N'
346 , p_custom_output => l_custom_output
347 , x_cur_suffix => l_curr_suffix
348 , x_where_clause => l_where_clause
349 , x_viewby_select => l_viewby_select
350 , x_join_tbl => l_join_tbl
351 , x_dim_bmap => l_dim_bmap
352 , x_comparison_type => l_comparison_type
353 , x_xtd => l_xtd
354 );
355
356 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
357 l_to_date_type := 'XTD';
358 else
359 l_to_date_type := 'RLX';
360 end if;
361
362 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
363 ( p_param
364 , 'VIEW_BY'
365 );
366
367 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
368 l_product := 'v4.description ISC_ATTRIBUTE_3';
369 else
370 l_product := 'null ISC_ATTRIBUTE_3';
371 end if;
372
373 l_mv := get_fact_mv_name
374 ( 'FTFR'
375 , p_param
376 , l_dim_bmap
377 , l_custom_output
378 );
379
380 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
381
382 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
383 , p_col_name => 'ftf_count'
384 , p_alias_name => 'ftf_count'
385 , p_to_date_type => l_to_date_type
386 );
387
388 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
389 , p_col_name => 'non_ftf_count'
393
390 , p_alias_name => 'non_ftf_count'
391 , p_to_date_type => l_to_date_type
392 );
394 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
395 , p_col_name => 'sr_count'
396 , p_alias_name => 'sr_count'
397 , p_to_date_type => l_to_date_type
398 );
399
400 l_stmt := 'select
401 ' || l_viewby_select || '
402 , ISC_MEASURE_1
403 , ISC_MEASURE_2
404 , ISC_MEASURE_3
405 , ISC_MEASURE_4
406 , ISC_MEASURE_5
407 , ISC_MEASURE_6
408 , ISC_MEASURE_7
409 , ISC_MEASURE_8
410 , ISC_MEASURE_1 ISC_MEASURE_9
411 , ISC_MEASURE_2 ISC_MEASURE_10
412 , ISC_MEASURE_5 ISC_MEASURE_11
413 , ISC_MEASURE_6 ISC_MEASURE_12
414 , ISC_MEASURE_2 ISC_MEASURE_13
415 , ISC_MEASURE_3 ISC_MEASURE_14
416 , ISC_MEASURE_21
417 , ISC_MEASURE_22
418 , ISC_MEASURE_23
419 , ISC_MEASURE_24
420 , ISC_MEASURE_25
421 , ISC_MEASURE_26
422 , ISC_MEASURE_27
423 , ISC_MEASURE_28
424 , ISC_MEASURE_21 ISC_MEASURE_29
425 , ISC_MEASURE_22 ISC_MEASURE_30
426 , ISC_MEASURE_25 ISC_MEASURE_31
427 , ISC_MEASURE_26 ISC_MEASURE_32
428 , ISC_MEASURE_22 ISC_MEASURE_33
429 , ISC_MEASURE_23 ISC_MEASURE_34
430 , ' || l_product || '
431 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
432 ( l_view_by
433 , g_task_rep_func
434 , 'ISC_ATTRIBUTE_4' ) || '
435 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
436 ( l_view_by
437 , g_task_rep_func
438 , 'ISC_ATTRIBUTE_5' ) || '
439 , ' || isc_fs_rpt_util_pkg.get_detail_drill_down
440 ( p_view_by => l_view_by
441 , p_function_name => g_detail_rep_func
442 , p_check_column_name => 'ISC_MEASURE_8'
443 , p_column_alias => 'ISC_ATTRIBUTE_6'
444 , p_extra_params => '&VIEW_BY=DUMMY+SERVICE_REQUEST-DUMMY+TASK'
445 , p_check_resource => 'Y'
446 ) || '
447 from (
448 select
449 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
450 , iset.*
451 from ( select * from (
452 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
453 , ' || isc_fs_rpt_util_pkg.rate_column
454 ( 'p_ftf_count'
455 , 'p_sr_count'
456 , 'ISC_MEASURE_1'
457 , 'Y'
458 ) || '
459 , ' || isc_fs_rpt_util_pkg.rate_column
460 ( 'c_ftf_count'
461 , 'c_sr_count'
462 , 'ISC_MEASURE_2'
463 , 'Y'
464 ) || '
465 , ' || isc_fs_rpt_util_pkg.change_column
466 ( isc_fs_rpt_util_pkg.rate_column
467 ( 'c_ftf_count'
468 , 'c_sr_count'
469 , null
470 , 'Y'
471 )
472 , isc_fs_rpt_util_pkg.rate_column
473 ( 'p_ftf_count'
474 , 'p_sr_count'
475 , null
476 , 'Y'
477 )
478 , 'ISC_MEASURE_3'
479 , 'N'
480 ) || '
481 , nvl(c_ftf_count,0) ISC_MEASURE_4
482 , ' || isc_fs_rpt_util_pkg.rate_column
483 ( 'p_non_ftf_count'
484 , 'p_sr_count'
485 , 'ISC_MEASURE_5'
486 , 'Y'
487 ) || '
488 , ' || isc_fs_rpt_util_pkg.rate_column
489 ( 'c_non_ftf_count'
490 , 'c_sr_count'
491 , 'ISC_MEASURE_6'
492 , 'Y'
493 ) || '
494 , ' || isc_fs_rpt_util_pkg.change_column
495 ( isc_fs_rpt_util_pkg.rate_column
496 ( 'c_non_ftf_count'
497 , 'c_sr_count'
498 , null
499 , 'Y'
500 )
501 , isc_fs_rpt_util_pkg.rate_column
502 ( 'p_non_ftf_count'
503 , 'p_sr_count'
504 , null
505 , 'Y'
506 )
507 , 'ISC_MEASURE_7'
508 , 'N'
509 ) || '
510 , nvl(c_non_ftf_count,0) ISC_MEASURE_8
511 , ' || isc_fs_rpt_util_pkg.rate_column
512 ( 'p_ftf_count_total'
513 , 'p_sr_count_total'
514 , 'ISC_MEASURE_21'
515 , 'Y'
516 ) || '
517 , ' || isc_fs_rpt_util_pkg.rate_column
518 ( 'c_ftf_count_total'
519 , 'c_sr_count_total'
520 , 'ISC_MEASURE_22'
521 , 'Y'
522 ) || '
523 , ' || isc_fs_rpt_util_pkg.change_column
524 ( isc_fs_rpt_util_pkg.rate_column
525 ( 'c_ftf_count_total'
526 , 'c_sr_count_total'
527 , null
528 , 'Y'
529 )
530 , isc_fs_rpt_util_pkg.rate_column
531 ( 'p_ftf_count_total'
532 , 'p_sr_count_total'
533 , null
534 , 'Y'
535 )
536 , 'ISC_MEASURE_23'
537 , 'N'
538 ) || '
539 , nvl(c_ftf_count_total,0) ISC_MEASURE_24
540 , ' || isc_fs_rpt_util_pkg.rate_column
541 ( 'p_non_ftf_count_total'
542 , 'p_sr_count_total'
543 , 'ISC_MEASURE_25'
544 , 'Y'
545 ) || '
546 , ' || isc_fs_rpt_util_pkg.rate_column
547 ( 'c_non_ftf_count_total'
548 , 'c_sr_count_total'
549 , 'ISC_MEASURE_26'
550 , 'Y'
551 ) || '
552 , ' || isc_fs_rpt_util_pkg.change_column
553 ( isc_fs_rpt_util_pkg.rate_column
554 ( 'c_non_ftf_count_total'
555 , 'c_sr_count_total'
556 , null
557 , 'Y'
558 )
559 , isc_fs_rpt_util_pkg.rate_column
560 ( 'p_non_ftf_count_total'
561 , 'p_sr_count_total'
562 , null
566 , 'N'
563 , 'Y'
564 )
565 , 'ISC_MEASURE_27'
567 ) || '
568 , nvl(c_non_ftf_count_total,0) ISC_MEASURE_28
569 from ' || poa_dbi_template_pkg.status_sql
570 ( p_fact_name => l_mv
571 , p_where_clause => l_where_clause
572 , p_join_tables => l_join_tbl
573 , p_use_windowing => 'Y' --'N'
574 , p_col_name => l_col_tbl
575 , p_use_grpid => 'N'
576 , p_paren_count => 3
577 , p_filter_where => '1=1) iset '
578 , p_generate_viewby => 'Y'
579 );
580
581 isc_fs_rpt_util_pkg.enhance_time_join
582 ( l_stmt
583 , 'N'
584 );
585
586 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
587 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
588
589 poa_dbi_util_pkg.get_custom_rolling_binds
590 ( p_custom_output => l_custom_output
591 , p_xtd => l_xtd
592 );
593
594 x_custom_output := l_custom_output;
595
596 x_custom_sql := l_stmt;
597
598 end get_tbl_sql;
599
600 procedure get_trd_sql
601 ( p_param in bis_pmv_page_parameter_tbl
602 , x_custom_sql out nocopy varchar2
603 , x_custom_output out nocopy bis_query_attributes_tbl
604 )
605 is
606
607 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
608 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
609 l_custom_output bis_query_attributes_tbl;
610 l_curr_suffix varchar2(3);
611 l_where_clause varchar2(10000);
612 l_viewby_select varchar2(400); -- needed to be increased from 200
613 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
614 l_dim_bmap number;
615 l_comparison_type varchar2(200);
616 l_xtd varchar2(200);
617 l_mv varchar2(10000);
618 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
619 l_stmt varchar2(32767);
620 l_to_date_type varchar2(200);
621
622 begin
623
624 isc_fs_rpt_util_pkg.register_dimension_levels
625 ( l_dimension_tbl
626 , l_dim_filter_map
627 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
628 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
629 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
630 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
631 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
632 );
633
634 isc_fs_rpt_util_pkg.process_parameters
635 ( p_param => p_param
636 , p_dimension_tbl => l_dimension_tbl
637 , p_dim_filter_map => l_dim_filter_map
638 , p_trend => 'Y'
639 , p_custom_output => l_custom_output
640 , x_cur_suffix => l_curr_suffix
641 , x_where_clause => l_where_clause
642 , x_viewby_select => l_viewby_select
643 , x_join_tbl => l_join_tbl
644 , x_dim_bmap => l_dim_bmap
645 , x_comparison_type => l_comparison_type
646 , x_xtd => l_xtd
647 );
648
649 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
650 l_to_date_type := 'XTD';
651 else
652 l_to_date_type := 'RLX';
653 end if;
654
655 --l_where_clause := l_where_clause || ' and fact.closed > 0';
656
657 l_mv := get_fact_mv_name
658 ( 'FTFR'
659 , p_param
660 , l_dim_bmap
661 , l_custom_output
662 );
663
664 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
665
666 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
667 , p_col_name => 'ftf_count'
668 , p_alias_name => 'ftf_count'
669 , p_to_date_type => l_to_date_type
670 , p_grand_total => 'N'
671 );
672
673 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
674 , p_col_name => 'sr_count'
675 , p_alias_name => 'sr_count'
676 , p_to_date_type => l_to_date_type
677 , p_grand_total => 'N'
678 );
679
680 l_stmt := 'select
681 cal.name VIEWBY
682 , ' || isc_fs_rpt_util_pkg.rate_column
683 ( 'iset.p_ftf_count'
684 , 'iset.p_sr_count'
685 , 'ISC_MEASURE_1'
686 , 'Y'
687 ) || '
688 , ' || isc_fs_rpt_util_pkg.rate_column
689 ( 'iset.c_ftf_count'
690 , 'iset.c_sr_count'
691 , 'ISC_MEASURE_2'
692 , 'Y'
693 ) || '
694 , ' || isc_fs_rpt_util_pkg.change_column
695 ( isc_fs_rpt_util_pkg.rate_column
696 ( 'iset.c_ftf_count'
697 , 'iset.c_sr_count'
698 , null
699 , 'Y'
700 )
701 , isc_fs_rpt_util_pkg.rate_column
702 ( 'iset.p_ftf_count'
703 , 'iset.p_sr_count'
704 , null
705 , 'Y'
706 )
707 , 'ISC_MEASURE_3'
708 , 'N'
709 ) ||
710 isc_fs_rpt_util_pkg.get_trend_drill
711 ( l_xtd
712 , g_trd_rep_func
713 , 'ISC_ATTRIBUTE_1'
714 , 'ISC_ATTRIBUTE_2'
715 ) || '
716 from
717 ' || poa_dbi_template_pkg.trend_sql
718 ( p_xtd => l_xtd
719 , p_comparison_type => l_comparison_type
720 , p_fact_name => l_mv
721 , p_where_clause => l_where_clause
722 , p_col_name => l_col_tbl
723 , p_use_grpid => 'N'
724 );
725
729 );
726 isc_fs_rpt_util_pkg.enhance_time_join
727 ( l_stmt
728 , 'Y'
730
731 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
732 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
733
734 x_custom_sql := l_stmt;
735
736 x_custom_output := l_custom_output;
737
738 poa_dbi_util_pkg.get_custom_rolling_binds
739 ( p_custom_output => l_custom_output
740 , p_xtd => l_xtd
741 );
742
743 x_custom_output := l_custom_output;
744
745 poa_dbi_util_pkg.get_custom_trend_binds
746 ( x_custom_output => l_custom_output
747 , p_xtd => l_xtd
748 , p_comparison_type => l_comparison_type
749 );
750
751 if l_custom_output is not null then
752 for i in 1..l_custom_output.count loop
753 x_custom_output.extend;
754 x_custom_output(x_custom_output.count) := l_custom_output(i);
755 end loop;
756 end if;
757
758 end get_trd_sql;
759
760 procedure get_dtl_rpt_sql
761 ( p_param in bis_pmv_page_parameter_tbl
762 , x_custom_sql out nocopy varchar2
763 , x_custom_output out nocopy bis_query_attributes_tbl
764 )
765 as
766
767 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
768 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
769 l_custom_output bis_query_attributes_tbl;
770 l_curr_suffix varchar2(3);
771 l_where_clause varchar2(10000);
772 l_viewby_select varchar2(400); -- needed to be increased from 200
773 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
774 l_dim_bmap number;
775 l_comparison_type varchar2(200);
776 l_xtd varchar2(200);
777 l_mv varchar2(10000);
778 l_cost_element varchar2(200);
779 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
780 l_stmt varchar2(32767);
781 l_rank_order varchar2(200);
782 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
783 l_order_by varchar2(200);
784 l_asc_desc varchar2(100);
785
786 begin
787
788 -- split into two calls as limit is 10 and have 11
789 isc_fs_rpt_util_pkg.register_dimension_levels
790 ( l_dimension_tbl
791 , l_dim_filter_map
792 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
793 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
794 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
795 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
796 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'N'
797 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
798 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
799 , isc_fs_rpt_util_pkg.G_TASK_STATUS, 'N'
800 );
801
802 isc_fs_rpt_util_pkg.register_dimension_levels
803 ( l_dimension_tbl
804 , l_dim_filter_map
805 , isc_fs_rpt_util_pkg.G_SR_TYPE, 'N'
806 , isc_fs_rpt_util_pkg.G_SR_STATUS, 'N'
807 , isc_fs_rpt_util_pkg.G_SR_OWNER, 'N'
808 , isc_fs_rpt_util_pkg.G_SEVERITY, 'Y'
809 );
810
811 isc_fs_rpt_util_pkg.process_parameters
812 ( p_param => p_param
813 , p_dimension_tbl => l_dimension_tbl
814 , p_dim_filter_map => l_dim_filter_map
815 , p_trend => 'D'
816 , p_custom_output => l_custom_output
817 , x_cur_suffix => l_curr_suffix
818 , x_where_clause => l_where_clause
819 , x_viewby_select => l_viewby_select
820 , x_join_tbl => l_join_tbl
821 , x_dim_bmap => l_dim_bmap
822 , x_comparison_type => l_comparison_type
823 , x_xtd => l_xtd
824 );
825
826 l_mv := get_fact_mv_name
827 ( 'NFTF_DETAIL'
828 , p_param
829 , l_dim_bmap
830 , l_custom_output
831 );
832
833 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
834 ( p_param
835 , 'ORDERBY'
836 );
837
838 if l_order_by like '% DESC%' then
839 l_asc_desc := ' desc ';
840 else
841 l_asc_desc := ' asc ';
842 end if;
843
844 l_rank_order := 'order by ' ||
845 case
846 when l_order_by like '%ISC_MEASURE_4%' then
847 'report_date ' || l_asc_desc || ', source_object_id' || l_asc_desc
848 else
849 'source_object_name ' || l_asc_desc
850 end ||
851 case
852 when g_dual_view_by = 'Y' then
853 ', actual_start_date asc, actual_end_date asc, task_number'
854 end;
855
856 isc_fs_rpt_util_pkg.add_detail_column
857 ( p_detail_col_tbl => l_detail_col_tbl
858 , p_dimension_tbl => l_dimension_tbl
859 , p_fact_col_name => 'source_object_name'
860 , p_fact_col_total => 'N'
861 , p_column_key => 'source_object_name'
862 );
863
864 isc_fs_rpt_util_pkg.add_detail_column
865 ( p_detail_col_tbl => l_detail_col_tbl
866 , p_dimension_tbl => l_dimension_tbl
867 , p_fact_col_name => 'source_object_id'
868 , p_fact_col_total => 'N'
869 , p_column_key => 'source_object_id'
870 );
871
872 if g_dual_view_by = 'Y' then
873
874 isc_fs_rpt_util_pkg.add_detail_column
875 ( p_detail_col_tbl => l_detail_col_tbl
876 , p_dimension_tbl => l_dimension_tbl
877 , p_fact_col_name => 'task_number'
878 , p_fact_col_total => 'N'
879 , p_column_key => 'task_number'
880 );
881
882 isc_fs_rpt_util_pkg.add_detail_column
883 ( p_detail_col_tbl => l_detail_col_tbl
887 , p_column_key => 'task_id'
884 , p_dimension_tbl => l_dimension_tbl
885 , p_fact_col_name => 'task_id'
886 , p_fact_col_total => 'N'
888 );
889
890 isc_fs_rpt_util_pkg.add_detail_column
891 ( p_detail_col_tbl => l_detail_col_tbl
892 , p_dimension_tbl => l_dimension_tbl
893 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_STATUS
894 , p_column_key => 'task_status'
895 );
896
897 isc_fs_rpt_util_pkg.add_detail_column
898 ( p_detail_col_tbl => l_detail_col_tbl
899 , p_dimension_tbl => l_dimension_tbl
900 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_TYPE
901 , p_column_key => 'task_type'
902 );
903
904 isc_fs_rpt_util_pkg.add_detail_column
905 ( p_detail_col_tbl => l_detail_col_tbl
906 , p_dimension_tbl => l_dimension_tbl
907 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
908 , p_column_key => 'task_owner'
909 );
910
911 isc_fs_rpt_util_pkg.add_detail_column
912 ( p_detail_col_tbl => l_detail_col_tbl
913 , p_dimension_tbl => l_dimension_tbl
914 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
915 , p_column_key => 'task_assignee'
916 );
917
918 isc_fs_rpt_util_pkg.add_detail_column
919 ( p_detail_col_tbl => l_detail_col_tbl
920 , p_dimension_tbl => l_dimension_tbl
921 , p_fact_col_name => 'actual_start_date'
922 , p_fact_col_total => 'N'
923 , p_column_key => 'actual_start_date'
924 );
925
926 isc_fs_rpt_util_pkg.add_detail_column
927 ( p_detail_col_tbl => l_detail_col_tbl
928 , p_dimension_tbl => l_dimension_tbl
929 , p_fact_col_name => 'actual_end_date'
930 , p_fact_col_total => 'N'
931 , p_column_key => 'actual_end_date'
932 );
933
934 isc_fs_rpt_util_pkg.add_detail_column
935 ( p_detail_col_tbl => l_detail_col_tbl
936 , p_dimension_tbl => l_dimension_tbl
937 , p_fact_col_name => 'actual_effort_hrs'
938 , p_fact_col_total => 'Y'
939 , p_column_key => 'actual_effort_hrs'
940 );
941
942 else
943
944 isc_fs_rpt_util_pkg.add_detail_column
945 ( p_detail_col_tbl => l_detail_col_tbl
946 , p_dimension_tbl => l_dimension_tbl
947 , p_dimension_level => isc_fs_rpt_util_pkg.G_SR_STATUS
948 , p_column_key => 'sr_status'
949 );
950
951 isc_fs_rpt_util_pkg.add_detail_column
952 ( p_detail_col_tbl => l_detail_col_tbl
953 , p_dimension_tbl => l_dimension_tbl
954 , p_dimension_level => isc_fs_rpt_util_pkg.G_SR_TYPE
955 , p_column_key => 'sr_type'
956 );
957
958 isc_fs_rpt_util_pkg.add_detail_column
959 ( p_detail_col_tbl => l_detail_col_tbl
960 , p_dimension_tbl => l_dimension_tbl
961 , p_dimension_level => isc_fs_rpt_util_pkg.G_SR_OWNER
962 , p_column_key => 'sr_owner'
963 );
964
965 isc_fs_rpt_util_pkg.add_detail_column
966 ( p_detail_col_tbl => l_detail_col_tbl
967 , p_dimension_tbl => l_dimension_tbl
968 , p_fact_col_name => 'report_date'
969 , p_fact_col_total => 'N'
970 , p_column_key => 'report_date'
971 );
972
973 end if;
974
975 isc_fs_rpt_util_pkg.add_detail_column
976 ( p_detail_col_tbl => l_detail_col_tbl
977 , p_dimension_tbl => l_dimension_tbl
978 , p_dimension_level => isc_fs_rpt_util_pkg.G_PRODUCT
979 , p_column_key => 'product'
980 );
981
982 isc_fs_rpt_util_pkg.add_detail_column
983 ( p_detail_col_tbl => l_detail_col_tbl
984 , p_dimension_tbl => l_dimension_tbl
985 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
986 , p_column_key => 'customer'
987 );
988
989 l_stmt := 'select
990 oset.source_object_name VIEWBY
991 , oset.source_object_id VIEWBYID' ||
992 case
993 when g_dual_view_by = 'Y' then '
994 , oset.task_number EXTRAVIEWBY
995 , oset.task_id EXTRAVIEWBYID
996 , ' || isc_fs_rpt_util_pkg.get_detail_column
997 (l_detail_col_tbl,'task_status','ISC_ATTRIBUTE_1') || '
998 , ' || isc_fs_rpt_util_pkg.get_detail_column
999 (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_2') || '
1000 , ' || isc_fs_rpt_util_pkg.get_detail_column
1001 (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_3') || '
1002 , ' || isc_fs_rpt_util_pkg.get_detail_column
1003 (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_4') || '
1004 , oset.actual_start_date ISC_MEASURE_1
1005 , oset.actual_end_date ISC_MEASURE_2
1006 , oset.actual_effort_hrs ISC_MEASURE_3
1007 , null ISC_MEASURE_4'
1008 else '
1009 , null EXTRAVIEWBY
1010 , null EXTRAVIEWBYID
1011 , ' || -- biv_dbi_resolution_sum_f does not currently provide incident_status_id
1012 -- isc_fs_rpt_util_pkg.get_detail_column
1013 -- (l_detail_col_tbl,'sr_status','ISC_ATTRIBUTE_1') || '
1014 'null ISC_ATTRIBUTE_1' || '
1015 , ' || isc_fs_rpt_util_pkg.get_detail_column
1016 (l_detail_col_tbl,'sr_type','ISC_ATTRIBUTE_2') || '
1017 , ' || isc_fs_rpt_util_pkg.get_detail_column
1018 (l_detail_col_tbl,'sr_owner','ISC_ATTRIBUTE_3') || '
1019 , null ISC_ATTRIBUTE_4
1020 , null ISC_MEASURE_1
1021 , null ISC_MEASURE_2
1022 , null ISC_MEASURE_3
1023 , oset.report_date ISC_MEASURE_4'
1024 end || '
1025 , ' || isc_fs_rpt_util_pkg.get_detail_column
1026 (l_detail_col_tbl,'product','ISC_ATTRIBUTE_5') || '
1027 , ' || isc_fs_rpt_util_pkg.get_detail_column
1028 (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_6') ||
1029 case
1033 , null'
1030 when g_dual_view_by = 'Y' then '
1031 , oset.actual_effort_hrs_total'
1032 else '
1034 end || ' ISC_MEASURE_23
1035 , ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_12' ||
1036 case
1037 when g_dual_view_by = 'Y' then '
1038 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_13'
1039 else '
1040 , null ISC_ATTRIBUTE_13'
1041 end || '
1042 from
1043 ' || isc_fs_rpt_util_pkg.detail_sql
1044 ( p_detail_col_tbl => l_detail_col_tbl
1045 , p_dimension_tbl => l_dimension_tbl
1046 , p_mv_name => l_mv
1047 , p_where_clause => l_where_clause
1048 , p_rank_order => l_rank_order
1049 , p_override_date_clause => 'report_date >= &BIS_CURRENT_EFFECTIVE_START_DATE and report_date < &BIS_CURRENT_ASOF_DATE +1'
1050 );
1051
1052
1053 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1054 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
1055
1056 x_custom_sql := l_stmt;
1057
1058 x_custom_output := l_custom_output;
1059
1060 end get_dtl_rpt_sql;
1061
1062 end isc_fs_ftfr_rpt_pkg;