[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_ACTIVITY_RPT_PKG
Source
1 package body isc_fs_task_activity_rpt_pkg
2 /* $Header: iscfstkarptb.pls 120.3 2006/04/12 20:46:24 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_TASK_ACTIVITY_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_TASK_ACTIVITY_TBL_REP';
7 g_trd_rep_func constant varchar2(50) := 'ISC_FS_TASK_CLOSED_ACT_TRD_REP';
8 g_first_opened varchar2(80);
9 g_reopened varchar2(80);
10 g_closed varchar2(80);
11 g_language varchar2(100);
12
13 procedure get_session_language
14 ( p_param in bis_pmv_page_parameter_tbl
15 )
16 is
17
18 l_session_id number;
19
20 cursor c_session_lang is
21 select
22 language_code
23 from icx_sessions
24 where session_id = l_session_id;
25
26 begin
27
28 -- this is needed userenv('LANG') does not always
29 -- contain the language of the users report/page session
30 -- so we would return values for the wrong language!
31
32 l_session_id := isc_fs_rpt_util_pkg.get_parameter_value
33 ( p_param
34 , 'BIS_ICX_SESSION_ID'
35 );
36 open c_session_lang;
37 fetch c_session_lang into g_language;
38 close c_session_lang;
39
40 if g_language is null then
41 g_language := userenv('LANG');
42 end if;
43
44 end get_session_language;
45
46 procedure load_long_labels
47 is
48 cursor c_attributes is
49 select attribute_code
50 , replace(attribute_label_long,'''',''''||'''') attribute_label_long
51 from
52 ak_region_items_tl
53 where region_code = 'ISC_FS_TASK_ACTIVITY_TBL'
54 and region_application_id = 454
55 and attribute_code in ( 'ISC_MEASURE_1', 'ISC_MEASURE_2', 'ISC_MEASURE_7' )
56 and attribute_application_id = 454
57 and language = g_language;
58 begin
59 for i in c_attributes loop
60 if i.attribute_code = 'ISC_MEASURE_1' then
61 g_first_opened := i.attribute_label_long;
62 elsif i.attribute_code = 'ISC_MEASURE_2' then
63 g_reopened := i.attribute_label_long;
64 elsif i.attribute_code = 'ISC_MEASURE_7' then
65 g_closed := i.attribute_label_long;
66 end if;
67 end loop;
68 end load_long_labels;
69
70 function get_detail_drill
71 ( p_view_by in varchar2
72 , p_column in number -- 1 = first_opened, 2 = reopened, 3 = closed
73 , p_measure_col in varchar2
74 , p_col_alias in varchar2
75 )
76 return varchar2
77 is
78 l_column_name varchar2(30);
79 l_column_label varchar2(80);
80 begin
81
82 if g_first_opened is null then
83 load_long_labels;
84 end if;
85
86 if p_column = 1 then
87 l_column_name := 'FIRST_OPENED';
88 l_column_label := g_first_opened;
89 elsif p_column = 2 then
90 l_column_name := 'REOPENED';
91 l_column_label := g_reopened;
92 elsif p_column = 3 then
93 l_column_name := 'CLOSED';
94 l_column_label := g_closed;
95 end if;
96
97 return
98 case
99 when p_view_by in ( isc_fs_rpt_util_pkg.G_PRODUCT
100 , isc_fs_rpt_util_pkg.G_CUSTOMER
101 , isc_fs_rpt_util_pkg.G_TASK_TYPE
102 -- R12 resource type
103 , isc_fs_rpt_util_pkg.G_DISTRICT ) then
104 isc_fs_rpt_util_pkg.get_detail_drill_down
105 ( p_view_by => p_view_by
106 , p_function_name => g_detail_rep_func
107 , p_check_column_name => p_measure_col
108 , p_extra_params => '&ISC_PARAMETER_1=' || l_column_name || '&ISC_ATTRIBUTE_9=' || l_column_label
109 , p_column_alias => p_col_alias
110 , p_check_column => 'Y'
111 , p_check_resource => 'Y'
112 )
113 else
114 'null ' || p_col_alias
115 end;
116
117 end get_detail_drill;
118
119 function get_fact_mv_name
120 ( p_report_type in varchar2
121 , p_param in bis_pmv_page_parameter_tbl
122 , p_dim_bmap in number
123 , p_custom_output in out nocopy bis_query_attributes_tbl
124 )
125 return varchar2
126 is
127
128 l_top_node varchar2(1);
129 l_resource varchar2(1);
130 l_district_leaf_node varchar2(1);
131
132 begin
133
134 if nvl(isc_fs_rpt_util_pkg.get_parameter_value
135 ( p_param
136 , isc_fs_rpt_util_pkg.G_CATEGORY
137 ),'All') = 'All' then
138 l_top_node := 'Y';
139 else
140 l_top_node := 'N';
141 end if;
142
143 if p_report_type = 'TASK_ACTIVITY' then
144
145 l_district_leaf_node := isc_fs_rpt_util_pkg.is_district_leaf_node
146 ( p_param );
147
148 isc_fs_rpt_util_pkg.bind_group_id
149 ( p_dim_bmap
150 , p_custom_output
151 , isc_fs_rpt_util_pkg.G_CATEGORY
152 , isc_fs_rpt_util_pkg.G_PRODUCT
153 , isc_fs_rpt_util_pkg.G_CUSTOMER
154 );
155
156 if bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
157
158 return '(
159 select
160 v.top_node_flag vbh_top_node_flag
161 , v.parent_id vbh_parent_category_id
162 , v.imm_child_id vbh_child_category_id
163 , f.time_id
164 , f.period_type_id ' || case
165 when l_district_leaf_node = 'N' then '
166 , den.parent_prg_id parent_district_id
167 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
168 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
169 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
170 else '
171 , f.parent_district_id
172 , f.record_type
173 , f.district_id
174 , f.district_id_c'
175 end || '
176 , f.task_type_id
177 , f.customer_id
178 , f.product_id
179 , f.first_opened
180 , f.reopened
181 , nvl(f.first_opened,0)+nvl(f.reopened,0) opened
182 , f.closed
183 from
184 isc_fs_003_mv f' || case
185 when l_district_leaf_node = 'N' then '
186 , isc_fs_002_mv den'
187 end || '
188 , eni_denorm_hierarchies v
189 , mtl_default_category_sets m
190 where
191 m.functional_area_id = 11
192 and v.object_id = m.category_set_id
193 and v.dbi_flag = ''Y''
194 and v.object_type = ''CATEGORY_SET''
195 and f.vbh_category_id = v.child_id' || case
196 when l_district_leaf_node = 'N' then '
197 and f.parent_district_id = den.rg_id'
198 end || '
199 and f.grp_id = &ISC_GRP_ID' || case
200 when l_top_node = 'Y' then '
201 and v.top_node_flag = ''Y''' end || '
202 )';
203
204 elsif bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_PRODUCT_BMAP) = isc_fs_rpt_util_pkg.G_PRODUCT_BMAP or
205 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP) = isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP or
206 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP) = isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP then
207
208 return '(
209 select
210 f.time_id
211 , f.period_type_id ' || case
212 when l_district_leaf_node = 'N' then '
213 , den.parent_prg_id parent_district_id
214 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
215 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
216 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
217 else '
218 , f.parent_district_id
219 , f.record_type
220 , f.district_id
221 , f.district_id_c'
222 end || '
223 , f.task_type_id
224 , f.customer_id
225 , f.product_id
226 , f.first_opened
227 , f.reopened
228 , nvl(f.first_opened,0)+nvl(f.reopened,0) opened
229 , f.closed
230 from
231 isc_fs_003_mv f' || case
232 when l_district_leaf_node = 'N' then '
233 , isc_fs_002_mv den'
234 end || '
235 where
236 f.grp_id = &ISC_GRP_ID ' || case
237 when l_district_leaf_node = 'N' then '
238 and f.parent_district_id = den.rg_id'
239 end || '
240 )';
241
242 else
243
244 return '(
245 select
246 f.time_id
247 , f.period_type_id
248 , f.parent_district_id
249 , f.record_type
250 , f.district_id
251 , f.district_id_c
252 , f.first_opened
253 , f.reopened
254 , nvl(f.first_opened,0)+nvl(f.reopened,0) opened
255 , f.closed
256 from isc_fs_004_mv f
257 )';
258
259 end if;
260
261 elsif p_report_type = 'TASK_ACTIVITY_DETAIL' then
262
263 if isc_fs_rpt_util_pkg.get_parameter_id
264 ( p_param
265 , isc_fs_rpt_util_pkg.G_DISTRICT
266 ) like '%.%' then
267 l_resource := 'Y';
268 else
269 l_resource := 'N';
270 end if;
271
272 return '(
273 select
274 t.task_id
275 , t.task_number
276 , a.activity_date report_date
277 , a.first_opened
278 , a.reopened
279 , a.closed
280 , t.task_type_id
281 , t.owner_id
282 -- R12 resource type
283 , t.owner_type
284 , decode(t.first_asgn_creation_date,null,to_number(null),t.act_bac_assignee_id) assignee_id
285 , decode(t.first_asgn_creation_date,null,null,t.act_bac_assignee_type) assignee_type' ||
286 case
287 when l_resource = 'N' then '
288 , d.parent_prg_id parent_district_id '
289 else '
290 , t.act_bac_assignee_id || ''.'' || t.act_bac_district_id district_id_c '
291 end || '
292 , t.actual_start_date
293 , t.actual_end_date
294 , t.actual_effort_hrs
295 , t.source_object_name
296 , t.source_object_id
297 , t.customer_id
298 , nvl(s.master_id,s.id) product_id ' ||
299 case
300 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
301 '
302 , v.top_node_flag vbh_top_node_flag
303 , v.parent_id vbh_parent_category_id
304 , v.imm_child_id vbh_child_category_id'
305 end || '
306 from
307 isc_fs_tasks_f t
308 , isc_fs_task_activity_f a
309 , eni_oltp_item_star s' ||
310 case
311 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
312 '
313 , eni_denorm_hierarchies v
314 , mtl_default_category_sets m'
315 end ||
316 case
317 when l_resource = 'N' then '
318 , isc_fs_002_mv d'
319 end || '
320 where
321 t.task_id = a.task_id
322 and t.task_type_rule = ''DISPATCH''
323 and t.source_object_type_code = ''SR''
324 and t.deleted_flag = ''N''
325 and t.inventory_item_id = s.inventory_item_id
326 and t.inv_organization_id = s.organization_id' ||
327 case
328 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
329 '
330 and m.functional_area_id = 11
331 and v.object_id = m.category_set_id
332 and v.dbi_flag = ''Y''
333 and v.object_type = ''CATEGORY_SET''
334 and s.vbh_category_id = v.child_id' ||
335 case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
336 end ||
337 case
338 when l_resource = 'N' then '
339 and d.rg_id = t.act_bac_district_id'
340 end || '
341 )';
342
343 else -- should not happen!!!
344 return '';
345
346 end if;
347
348 end get_fact_mv_name;
349
350 procedure get_tbl_sql
351 ( p_param in bis_pmv_page_parameter_tbl
352 , x_custom_sql out nocopy varchar2
353 , x_custom_output out nocopy bis_query_attributes_tbl
354 )
355 is
356
357 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
358 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
359 l_custom_output bis_query_attributes_tbl;
360 l_curr_suffix varchar2(3);
361 l_where_clause varchar2(10000);
362 l_viewby_select varchar2(400); -- needed to be increased from 200
363 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
364 l_dim_bmap number;
365 l_comparison_type varchar2(200);
366 l_xtd varchar2(200);
367 l_mv varchar2(10000);
368 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
369 l_view_by varchar2(200);
370 l_product varchar2(50);
371 l_stmt varchar2(32700);
372 l_to_date_type varchar2(200);
373
374 begin
375
376 isc_fs_rpt_util_pkg.register_dimension_levels
377 ( l_dimension_tbl
378 , l_dim_filter_map
379 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
380 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
381 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
382 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
383 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
384 );
385
386 isc_fs_rpt_util_pkg.process_parameters
387 ( p_param => p_param
388 , p_dimension_tbl => l_dimension_tbl
389 , p_dim_filter_map => l_dim_filter_map
390 , p_trend => 'N'
391 , p_custom_output => l_custom_output
392 , x_cur_suffix => l_curr_suffix
393 , x_where_clause => l_where_clause
394 , x_viewby_select => l_viewby_select
395 , x_join_tbl => l_join_tbl
396 , x_dim_bmap => l_dim_bmap
397 , x_comparison_type => l_comparison_type
398 , x_xtd => l_xtd
399 );
400
401 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
402 l_to_date_type := 'XTD';
403 else
404 l_to_date_type := 'RLX';
405 end if;
406
407 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
408 ( p_param
409 , 'VIEW_BY'
410 );
411
412 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
413 l_product := 'v4.description ISC_ATTRIBUTE_1';
414 else
415 l_product := 'null ISC_ATTRIBUTE_1';
416 end if;
417
418 l_mv := get_fact_mv_name
419 ( 'TASK_ACTIVITY'
420 , p_param
421 , l_dim_bmap
422 , l_custom_output
423 );
424
425 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
426
427 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
428 , p_col_name => 'first_opened'
429 , p_alias_name => 'first_opened'
430 , p_to_date_type => l_to_date_type
431 , p_prior_code => poa_dbi_util_pkg.no_priors
432 );
433
434 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
435 , p_col_name => 'reopened'
436 , p_alias_name => 'reopened'
437 , p_to_date_type => l_to_date_type
438 , p_prior_code => poa_dbi_util_pkg.no_priors
439 );
440
441 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
442 , p_col_name => 'opened'
443 , p_alias_name => 'opened'
444 , p_to_date_type => l_to_date_type
445 );
446
447 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
448 , p_col_name => 'closed'
449 , p_alias_name => 'closed'
450 , p_to_date_type => l_to_date_type
451 );
452
453 get_session_language
454 ( p_param => p_param
455 );
456
457 l_stmt := 'select
458 ' || l_viewby_select || '
459 , ISC_MEASURE_1
460 , ISC_MEASURE_2
461 , ISC_MEASURE_3
462 , ISC_MEASURE_4
463 , ISC_MEASURE_5
464 , ISC_MEASURE_6
465 , ISC_MEASURE_8
466 , ISC_MEASURE_7
467 , ISC_MEASURE_9
468 , ISC_MEASURE_10
469 , ISC_MEASURE_11
470 , ISC_MEASURE_21
471 , ISC_MEASURE_22
472 , ISC_MEASURE_23
473 , ISC_MEASURE_24
474 , ISC_MEASURE_25
475 , ISC_MEASURE_26
476 , ISC_MEASURE_27
477 , ISC_MEASURE_28
478 , ISC_MEASURE_29
479 , ISC_MEASURE_30
480 , ISC_MEASURE_31
481 , ' || l_product || '
482 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
483 ( l_view_by
484 , g_task_rep_func
485 , 'ISC_ATTRIBUTE_2' ) || '
486 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
487 ( l_view_by
488 , g_task_rep_func
489 , 'ISC_ATTRIBUTE_3' ) || '
490 , ' || get_detail_drill( l_view_by, 1, 'ISC_MEASURE_1', 'ISC_ATTRIBUTE_4' ) || '
491 , ' || get_detail_drill( l_view_by, 2, 'ISC_MEASURE_2', 'ISC_ATTRIBUTE_5' ) || '
492 , ' || get_detail_drill( l_view_by, 3, 'ISC_MEASURE_7', 'ISC_ATTRIBUTE_6' ) || '
493 from (
494 select
495 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
496 , iset.*
497 from ( select * from (
498 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
499 , nvl(c_first_opened,0) ISC_MEASURE_1
500 , nvl(c_reopened,0) ISC_MEASURE_2
501 , nvl(p_opened,0) ISC_MEASURE_3
502 , nvl(c_opened,0) ISC_MEASURE_4' || '
503 , ' || isc_fs_rpt_util_pkg.change_column
504 ( 'c_opened'
505 , 'p_opened'
506 , 'ISC_MEASURE_5' ) || '
507 , nvl(p_closed,0) ISC_MEASURE_6
508 , nvl(c_closed,0) ISC_MEASURE_7' || '
509 , ' || isc_fs_rpt_util_pkg.change_column
510 ( 'c_closed'
511 , 'p_closed'
512 , 'ISC_MEASURE_8' ) || '
513 , ' || isc_fs_rpt_util_pkg.rate_column
514 ( 'p_opened'
515 , 'p_closed'
516 , 'ISC_MEASURE_9'
517 , 'N' ) || '
518 , ' || isc_fs_rpt_util_pkg.rate_column
519 ( 'c_opened'
520 , 'c_closed'
521 , 'ISC_MEASURE_10'
522 , 'N' ) || '
523 , ' || isc_fs_rpt_util_pkg.change_column
524 ( isc_fs_rpt_util_pkg.rate_column( 'c_opened'
525 , 'c_closed'
526 , null
527 , 'N' )
528 , isc_fs_rpt_util_pkg.rate_column( 'p_opened'
529 , 'p_closed'
530 , null
531 , 'N' )
532 , 'ISC_MEASURE_11'
533 , 'N') || '
534 , nvl(c_first_opened_total,0) ISC_MEASURE_21
535 , nvl(c_reopened_total,0) ISC_MEASURE_22
536 , nvl(p_opened_total,0) ISC_MEASURE_23
537 , nvl(c_opened_total,0) ISC_MEASURE_24' || '
538 , ' || isc_fs_rpt_util_pkg.change_column
539 ( 'c_opened_total'
540 , 'p_opened_total'
541 , 'ISC_MEASURE_25' ) || '
542 , nvl(p_closed_total,0) ISC_MEASURE_26
543 , nvl(c_closed_total,0) ISC_MEASURE_27' || '
544 , ' || isc_fs_rpt_util_pkg.change_column
545 ( 'c_closed_total'
546 , 'p_closed_total'
547 , 'ISC_MEASURE_28' ) || '
548 , ' || isc_fs_rpt_util_pkg.rate_column
549 ( 'p_opened_total'
550 , 'p_closed_total'
551 , 'ISC_MEASURE_29'
552 , 'N') || '
553 , ' || isc_fs_rpt_util_pkg.rate_column
554 ( 'c_opened_total'
555 , 'c_closed_total'
556 , 'ISC_MEASURE_30'
557 , 'N') || '
558 , ' || isc_fs_rpt_util_pkg.change_column
559 ( isc_fs_rpt_util_pkg.rate_column( 'c_opened_total'
560 , 'c_closed_total'
561 , null
562 , 'N' )
563 , isc_fs_rpt_util_pkg.rate_column( 'p_opened_total'
564 , 'p_closed_total'
565 , null
566 , 'N' )
567 , 'ISC_MEASURE_31'
568 , 'N' ) || '
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 ' --null
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 --insert into isc_fs_keith values( dbms_utility.get_time, l_stmt );
599
600 end get_tbl_sql;
601
602 procedure get_trd_sql
603 ( p_param in bis_pmv_page_parameter_tbl
604 , x_custom_sql out nocopy varchar2
605 , x_custom_output out nocopy bis_query_attributes_tbl
606 )
607 is
608
609 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
610 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
611 l_custom_output bis_query_attributes_tbl;
612 l_curr_suffix varchar2(3);
613 l_where_clause varchar2(10000);
614 l_viewby_select varchar2(400); -- needed to be increased from 200
615 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
616 l_dim_bmap number;
617 l_comparison_type varchar2(200);
618 l_xtd varchar2(200);
619 l_mv varchar2(10000);
620 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
621 l_stmt varchar2(32767);
622 l_to_date_type varchar2(200);
623
624 begin
625
626 isc_fs_rpt_util_pkg.register_dimension_levels
627 ( l_dimension_tbl
628 , l_dim_filter_map
629 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
630 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
631 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
632 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
633 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
634 );
635
636 isc_fs_rpt_util_pkg.process_parameters
637 ( p_param => p_param
638 , p_dimension_tbl => l_dimension_tbl
639 , p_dim_filter_map => l_dim_filter_map
640 , p_trend => 'Y'
641 , p_custom_output => l_custom_output
642 , x_cur_suffix => l_curr_suffix
643 , x_where_clause => l_where_clause
644 , x_viewby_select => l_viewby_select
645 , x_join_tbl => l_join_tbl
646 , x_dim_bmap => l_dim_bmap
647 , x_comparison_type => l_comparison_type
648 , x_xtd => l_xtd
649 );
650
651 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
652 l_to_date_type := 'XTD';
653 else
654 l_to_date_type := 'RLX';
655 end if;
656
657 l_where_clause := l_where_clause || ' and fact.closed > 0';
658
659 l_mv := get_fact_mv_name
660 ( 'TASK_ACTIVITY'
661 , p_param
662 , l_dim_bmap
663 , l_custom_output
664 );
665
666 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
667
668 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
669 , p_col_name => 'closed'
670 , p_alias_name => 'closed'
671 , p_to_date_type => l_to_date_type
672 , p_grand_total => 'N'
673 );
674
675 l_stmt := 'select
676 cal.name VIEWBY
677 , nvl(iset.p_closed,0) ISC_MEASURE_1
678 , nvl(iset.c_closed,0) ISC_MEASURE_2' || '
679 , ' ||
680 isc_fs_rpt_util_pkg.change_column
681 ( 'iset.c_closed'
682 , 'iset.p_closed'
683 , 'ISC_MEASURE_3' ) ||
684 isc_fs_rpt_util_pkg.get_trend_drill
685 ( l_xtd
686 , g_trd_rep_func
687 , 'ISC_ATTRIBUTE_1'
688 , 'ISC_ATTRIBUTE_2'
689 ) || '
690 from
691 ' || poa_dbi_template_pkg.trend_sql
692 ( p_xtd => l_xtd
693 , p_comparison_type => l_comparison_type
694 , p_fact_name => l_mv
695 , p_where_clause => l_where_clause
696 , p_col_name => l_col_tbl
697 , p_use_grpid => 'N'
698 );
699
700 isc_fs_rpt_util_pkg.enhance_time_join
701 ( l_stmt
702 , 'Y'
703 );
704
705 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
706 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
707
708 x_custom_sql := l_stmt;
709
710 poa_dbi_util_pkg.get_custom_rolling_binds
711 ( p_custom_output => l_custom_output
712 , p_xtd => l_xtd
713 );
714
715 x_custom_output := l_custom_output;
716
717 poa_dbi_util_pkg.get_custom_trend_binds
718 ( x_custom_output => l_custom_output
719 , p_xtd => l_xtd
720 , p_comparison_type => l_comparison_type
721 );
722
723 if l_custom_output is not null then
724 for i in 1..l_custom_output.count loop
725 x_custom_output.extend;
726 x_custom_output(x_custom_output.count) := l_custom_output(i);
727 end loop;
728 end if;
729
730 --insert into isc_fs_keith values( dbms_utility.get_time, l_stmt );
731
732 end get_trd_sql;
733
734 procedure get_dtl_rpt_sql
735 ( p_param in bis_pmv_page_parameter_tbl
736 , x_custom_sql out nocopy varchar2
737 , x_custom_output out nocopy bis_query_attributes_tbl
738 )
739 as
740
741 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
742 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
743 l_custom_output bis_query_attributes_tbl;
744 l_curr_suffix varchar2(3);
745 l_where_clause varchar2(10000);
746 l_viewby_select varchar2(400); -- needed to be increased from 200
747 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
748 l_dim_bmap number;
749 l_comparison_type varchar2(200);
750 l_xtd varchar2(200);
751 l_mv varchar2(10000);
752 l_cost_element varchar2(200);
753 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
754 l_stmt varchar2(32767);
755 l_rank_order varchar2(200);
756 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
757 l_order_by varchar2(200);
758 l_asc_desc varchar2(100);
759
760 begin
761
762 isc_fs_rpt_util_pkg.register_dimension_levels
763 ( l_dimension_tbl
764 , l_dim_filter_map
765 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
766 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
767 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
768 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
769 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
770 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
771 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
772 );
773
774 isc_fs_rpt_util_pkg.process_parameters
775 ( p_param => p_param
776 , p_dimension_tbl => l_dimension_tbl
777 , p_dim_filter_map => l_dim_filter_map
778 , p_trend => 'D'
779 , p_custom_output => l_custom_output
780 , x_cur_suffix => l_curr_suffix
781 , x_where_clause => l_where_clause
782 , x_viewby_select => l_viewby_select
783 , x_join_tbl => l_join_tbl
784 , x_dim_bmap => l_dim_bmap
785 , x_comparison_type => l_comparison_type
786 , x_xtd => l_xtd
787 );
788
789 l_where_clause := l_where_clause || ' and 1 = decode(&' ||
790 isc_fs_rpt_util_pkg.G_ACTIVITY_EVENT ||
791 ',''FIRST_OPENED'',fact.first_opened,''REOPENED'',fact.reopened,fact.closed)';
792
793 l_mv := get_fact_mv_name
794 ( 'TASK_ACTIVITY_DETAIL'
795 , p_param
796 , l_dim_bmap
797 , l_custom_output
798 );
799
800 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
801 ( p_param
802 , 'ORDERBY'
803 );
804
805 if l_order_by like '% DESC%' then
806 l_asc_desc := ' desc ';
807 else
808 l_asc_desc := ' asc ';
809 end if;
810
811 l_rank_order := 'order by ' ||
812 case
813 when l_order_by like '%ISC_MEASURE_1 %' then
814 'report_date'
815 when l_order_by like '%ISC_MEASURE_2 %' then
816 'actual_start_date'
817 when l_order_by like '%ISC_MEASURE_3 %' then
818 'actual_end_date'
819 when l_order_by like '%ISC_MEASURE_4 %' then
820 'actual_effort_hrs'
821 end ||
822 l_asc_desc ||
823 'nulls last, task_id';
824
825 isc_fs_rpt_util_pkg.add_detail_column
826 ( p_detail_col_tbl => l_detail_col_tbl
827 , p_dimension_tbl => l_dimension_tbl
828 , p_fact_col_name => 'task_number'
829 , p_fact_col_total => 'N'
830 , p_column_key => 'task_number'
831 );
832
833 isc_fs_rpt_util_pkg.add_detail_column
834 ( p_detail_col_tbl => l_detail_col_tbl
835 , p_dimension_tbl => l_dimension_tbl
836 , p_fact_col_name => 'task_id'
837 , p_fact_col_total => 'N'
838 , p_column_key => 'task_id'
839 );
840
841 isc_fs_rpt_util_pkg.add_detail_column
842 ( p_detail_col_tbl => l_detail_col_tbl
843 , p_dimension_tbl => l_dimension_tbl
844 , p_fact_col_name => 'report_date'
845 , p_fact_col_total => 'N'
846 , p_column_key => 'activity_date'
847 );
848
849 isc_fs_rpt_util_pkg.add_detail_column
850 ( p_detail_col_tbl => l_detail_col_tbl
851 , p_dimension_tbl => l_dimension_tbl
852 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_TYPE
853 , p_column_key => 'task_type'
854 );
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_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
860 , p_column_key => 'task_owner'
861 );
862
863 isc_fs_rpt_util_pkg.add_detail_column
864 ( p_detail_col_tbl => l_detail_col_tbl
865 , p_dimension_tbl => l_dimension_tbl
866 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
867 , p_column_key => 'task_assignee'
868 );
869
870 isc_fs_rpt_util_pkg.add_detail_column
871 ( p_detail_col_tbl => l_detail_col_tbl
872 , p_dimension_tbl => l_dimension_tbl
873 , p_fact_col_name => 'actual_start_date'
874 , p_fact_col_total => 'N'
875 , p_column_key => 'actual_start_date'
876 );
877
878 isc_fs_rpt_util_pkg.add_detail_column
879 ( p_detail_col_tbl => l_detail_col_tbl
880 , p_dimension_tbl => l_dimension_tbl
881 , p_fact_col_name => 'actual_end_date'
882 , p_fact_col_total => 'N'
883 , p_column_key => 'actual_end_date'
884 );
885
886 isc_fs_rpt_util_pkg.add_detail_column
887 ( p_detail_col_tbl => l_detail_col_tbl
888 , p_dimension_tbl => l_dimension_tbl
889 , p_fact_col_name => 'actual_effort_hrs'
890 , p_fact_col_total => 'N'
891 , p_column_key => 'actual_effort_hrs'
892 );
893
894 isc_fs_rpt_util_pkg.add_detail_column
895 ( p_detail_col_tbl => l_detail_col_tbl
896 , p_dimension_tbl => l_dimension_tbl
897 , p_fact_col_name => 'source_object_name'
898 , p_fact_col_total => 'N'
899 , p_column_key => 'source_object_name'
900 );
901
902 isc_fs_rpt_util_pkg.add_detail_column
903 ( p_detail_col_tbl => l_detail_col_tbl
904 , p_dimension_tbl => l_dimension_tbl
905 , p_fact_col_name => 'source_object_id'
906 , p_fact_col_total => 'N'
907 , p_column_key => 'source_object_id'
908 );
909
910 isc_fs_rpt_util_pkg.add_detail_column
911 ( p_detail_col_tbl => l_detail_col_tbl
912 , p_dimension_tbl => l_dimension_tbl
913 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
914 , p_column_key => 'customer'
915 );
916
917 isc_fs_rpt_util_pkg.add_detail_column
918 ( p_detail_col_tbl => l_detail_col_tbl
919 , p_dimension_tbl => l_dimension_tbl
920 , p_dimension_level => isc_fs_rpt_util_pkg.G_PRODUCT
921 , p_column_key => 'product'
922 );
923
924 l_stmt := 'select
925 oset.task_number ISC_ATTRIBUTE_1
926 , oset.activity_date ISC_MEASURE_1
927 , ' || isc_fs_rpt_util_pkg.get_detail_column
928 (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_2') || '
929 , ' || isc_fs_rpt_util_pkg.get_detail_column
930 (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_3') || '
931 , ' || isc_fs_rpt_util_pkg.get_detail_column
932 (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_4') || '
933 , oset.actual_start_date ISC_MEASURE_2
934 , oset.actual_end_date ISC_MEASURE_3
935 , oset.actual_effort_hrs ISC_MEASURE_4
936 , oset.source_object_name ISC_ATTRIBUTE_5
937 , ' || isc_fs_rpt_util_pkg.get_detail_column
938 (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_6') || '
939 , ' || isc_fs_rpt_util_pkg.get_detail_column
940 (l_detail_col_tbl,'product','ISC_ATTRIBUTE_7') || '
941 , ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_8
942 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_10
943 from
944 ' || isc_fs_rpt_util_pkg.detail_sql
945 ( p_detail_col_tbl => l_detail_col_tbl
946 , p_dimension_tbl => l_dimension_tbl
947 , p_mv_name => l_mv
948 , p_where_clause => l_where_clause
949 , p_rank_order => l_rank_order
950 );
951
952 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
953 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
954
955 x_custom_sql := l_stmt;
956
957 x_custom_output := l_custom_output;
958
959 --insert into isc_fs_keith values( dbms_utility.get_time, l_stmt );
960
961 end get_dtl_rpt_sql;
962
963
964 end isc_fs_task_activity_rpt_pkg;