[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_BACKLOG_RPT_PKG
Source
1 package body isc_fs_task_backlog_rpt_pkg
2 /* $Header: iscfstkbsrptb.pls 120.3 2006/04/12 20:47:29 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_STATUS_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_STATUS_TBL_REP';
7
8 function get_detail_drill
9 ( p_view_by in varchar2
10 , p_column in varchar2
11 , p_col_alias in varchar2
12 )
13 return varchar2
14 is
15 l_column_name varchar2(30);
16 begin
17
18 return
19 -- R12 resource type
20 isc_fs_rpt_util_pkg.get_detail_drill_down
21 ( p_view_by => p_view_by
22 , p_function_name => g_detail_rep_func
23 , p_check_column_name => 'ISC_MEASURE_4_B' || p_column
24 , p_extra_params => '&BIV_FS_BACKLOG_STATUS=' || p_column
25 , p_column_alias => p_col_alias || '_B' || p_column
26 , p_check_resource => 'Y'
27 );
28
29 end get_detail_drill;
30
31 function get_fact_mv_name
32 ( p_report_type in varchar2
33 , p_param in bis_pmv_page_parameter_tbl
34 , p_dim_bmap in number
35 , p_custom_output in out nocopy bis_query_attributes_tbl
36 )
37 return varchar2
38 is
39
40 l_top_node varchar2(1);
41 l_resource varchar2(1);
42 l_district_leaf_node varchar2(1);
43
44 begin
45
46 if nvl(isc_fs_rpt_util_pkg.get_parameter_value
47 ( p_param
48 , isc_fs_rpt_util_pkg.G_CATEGORY
49 ),'All') = 'All' then
50 l_top_node := 'Y';
51 else
52 l_top_node := 'N';
53 end if;
54
55 if p_report_type = 'TASK_BACKLOG_STATUS' then
56
57 l_district_leaf_node := isc_fs_rpt_util_pkg.is_district_leaf_node
58 ( p_param );
59
60 isc_fs_rpt_util_pkg.bind_group_id
61 ( p_dim_bmap
62 , p_custom_output
63 , isc_fs_rpt_util_pkg.G_CATEGORY
64 , isc_fs_rpt_util_pkg.G_PRODUCT
65 , isc_fs_rpt_util_pkg.G_CUSTOMER
66 );
67
68 if bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
69
70 return '(
71 select
72 v.top_node_flag vbh_top_node_flag
73 , v.parent_id vbh_parent_category_id
74 , v.imm_child_id vbh_child_category_id
75 , c.report_date ' || case
76 when l_district_leaf_node = 'N' then '
77 , den.parent_prg_id parent_district_id
78 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
79 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
80 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
81 else '
82 , f.parent_district_id
83 , f.record_type
84 , f.district_id
85 , f.district_id_c'
86 end || '
87 , f.task_type_id
88 , f.customer_id
89 , f.product_id
90 , f.backlog_count
91 , f.in_planning_count
92 , f.assigned_count
93 , f.working_count
94 , f.completed_count
95 , f.other_count
96 from
97 isc_fs_005_mv f' || case
98 when l_district_leaf_node = 'N' then '
99 , isc_fs_002_mv den'
100 end || '
101 , eni_denorm_hierarchies v
102 , mtl_default_category_sets m
103 , fii_time_structures c
104 where
105 m.functional_area_id = 11
106 and v.object_id = m.category_set_id
107 and v.dbi_flag = ''Y''
108 and v.object_type = ''CATEGORY_SET''
109 and f.vbh_category_id = v.child_id
110 and f.time_id = c.time_id
111 and f.period_type_id = c.period_type_id
112 and f.time_id = c.time_id
113 and bitand(c.record_type_id,512) = 512
114 and f.grp_id = &ISC_GRP_ID' || case
115 when l_district_leaf_node = 'N' then '
116 and f.parent_district_id = den.rg_id'
117 end ||
118 case
119 when l_top_node = 'Y' then '
120 and v.top_node_flag = ''Y''' end || '
121 )';
122
123 elsif bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_PRODUCT_BMAP) = isc_fs_rpt_util_pkg.G_PRODUCT_BMAP or
124 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP) = isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP or
125 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP) = isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP then
126
127 return '(
128 select
129 c.report_date ' || case
130 when l_district_leaf_node = 'N' then '
131 , den.parent_prg_id parent_district_id
132 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
133 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
134 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
135 else '
136 , f.parent_district_id
137 , f.record_type
138 , f.district_id
139 , f.district_id_c'
140 end || '
141 , f.task_type_id
142 , f.customer_id
143 , f.product_id
144 , f.backlog_count
145 , f.in_planning_count
146 , f.assigned_count
147 , f.working_count
148 , f.completed_count
149 , f.other_count
150 from
151 isc_fs_005_mv f' || case
152 when l_district_leaf_node = 'N' then '
153 , isc_fs_002_mv den'
154 end || '
155 , fii_time_structures c
156 where
157 f.period_type_id = c.period_type_id
158 and f.time_id = c.time_id
159 and bitand(c.record_type_id,512) = 512
160 and f.grp_id = &ISC_GRP_ID' || case
161 when l_district_leaf_node = 'N' then '
162 and f.parent_district_id = den.rg_id'
163 end || '
164 )';
165
166 else
167
168 return '(
169 select
170 c.report_date
171 , f.parent_district_id
172 , f.record_type
173 , f.district_id
174 , f.district_id_c
175 , f.backlog_count
176 , f.in_planning_count
177 , f.assigned_count
178 , f.working_count
179 , f.completed_count
180 , f.other_count
181 from
182 isc_fs_006_mv f
183 , fii_time_structures c
184 where
185 f.period_type_id = c.period_type_id
186 and f.time_id = c.time_id
187 and bitand(c.record_type_id,512) = 512
188 )';
189
190 end if;
191
192 elsif p_report_type = 'TASK_BACKLOG_DETAIL' then
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 , b.backlog_date_from
208 , b.backlog_date_to
209 , b.backlog_status_code
210 , t.task_type_id
211 , t.owner_id
212 -- R12 resource type
213 , t.owner_type
214 , decode(t.first_asgn_creation_date,null,to_number(null),t.act_bac_assignee_id) assignee_id
215 , decode(t.first_asgn_creation_date,null,null,t.act_bac_assignee_type) assignee_type ' ||
216 case
217 when l_resource = 'N' then '
218 , d.parent_prg_id parent_district_id '
219 else '
220 , t.act_bac_assignee_id || ''.'' || t.act_bac_district_id district_id_c '
221 end || '
222 , t.actual_start_date
223 , t.actual_end_date
224 , t.planned_start_date
225 , t.source_object_name
226 , t.source_object_id
227 , t.incident_date
228 , t.customer_id
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
234 , v.parent_id vbh_parent_category_id
235 , v.imm_child_id vbh_child_category_id'
236 end || '
237 from
238 isc_fs_tasks_f t
239 , isc_fs_task_backlog_f b
240 , eni_oltp_item_star s' ||
241 case
242 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
243 '
244 , eni_denorm_hierarchies v
245 , mtl_default_category_sets m'
246 end ||
247 case
248 when l_resource = 'N' then '
249 , isc_fs_002_mv d'
250 end || '
251 where
252 t.task_id = b.task_id
253 and t.task_type_rule = ''DISPATCH''
254 and t.source_object_type_code = ''SR''
255 and t.deleted_flag = ''N''
256 and t.inventory_item_id = s.inventory_item_id
257 and t.inv_organization_id = s.organization_id' ||
258 case
259 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
260 '
261 and m.functional_area_id = 11
262 and v.object_id = m.category_set_id
263 and v.dbi_flag = ''Y''
264 and v.object_type = ''CATEGORY_SET''
265 and s.vbh_category_id = v.child_id' ||
266 case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
267 end ||
268 case
269 when l_resource = 'N' then '
270 and d.rg_id = t.act_bac_district_id'
271 end || '
272 )';
273
274 else -- should not happen!!!
275 return '';
276
277 end if;
278
279 end get_fact_mv_name;
280
281 procedure get_tbl_sql
282 ( p_param in bis_pmv_page_parameter_tbl
283 , x_custom_sql out nocopy varchar2
284 , x_custom_output out nocopy bis_query_attributes_tbl
285 )
286 is
287
288 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
289 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
290 l_custom_output bis_query_attributes_tbl;
291 l_curr_suffix varchar2(3);
292 l_where_clause varchar2(10000);
293 l_viewby_select varchar2(400); -- needed to be increased from 200
294 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
295 l_dim_bmap number;
296 l_comparison_type varchar2(200);
297 l_xtd varchar2(200);
298 l_mv varchar2(10000);
299 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
300 l_view_by varchar2(200);
301 l_product varchar2(50);
302 l_stmt varchar2(32700);
303 l_to_date_type varchar2(200);
304
305 begin
306
307 isc_fs_rpt_util_pkg.register_dimension_levels
308 ( l_dimension_tbl
309 , l_dim_filter_map
310 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
311 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
312 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
313 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
314 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
315 );
316
317 isc_fs_rpt_util_pkg.process_parameters
318 ( p_param => p_param
319 , p_dimension_tbl => l_dimension_tbl
320 , p_dim_filter_map => l_dim_filter_map
321 , p_trend => 'N'
322 , p_custom_output => l_custom_output
323 , x_cur_suffix => l_curr_suffix
324 , x_where_clause => l_where_clause
325 , x_viewby_select => l_viewby_select
326 , x_join_tbl => l_join_tbl
327 , x_dim_bmap => l_dim_bmap
328 , x_comparison_type => l_comparison_type
329 , x_xtd => l_xtd
330 );
331
332 if l_xtd in ('DAY','WTD','MTD','QTD','YTD') then
333 l_to_date_type := 'XTD';
334 else
335 l_to_date_type := 'RLX';
336 end if;
337
338 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
339 ( p_param
340 , 'VIEW_BY'
341 );
342
343 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
344 l_product := 'v4.description ISC_ATTRIBUTE_1';
345 else
346 l_product := 'null ISC_ATTRIBUTE_1';
347 end if;
348
349 l_mv := get_fact_mv_name
350 ( 'TASK_BACKLOG_STATUS'
351 , p_param
352 , l_dim_bmap
353 , l_custom_output
354 );
355
356 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
357
358 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
359 , p_col_name => 'backlog_count'
360 , p_alias_name => 'backlog'
361 , p_to_date_type => 'BAL'
362 );
363
364 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
365 , p_col_name => 'in_planning_count'
366 , p_alias_name => 'in_planning'
367 , p_to_date_type => 'BAL'
368 , p_prior_code => poa_dbi_util_pkg.no_priors
369 );
370
371 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
372 , p_col_name => 'working_count'
373 , p_alias_name => 'working'
374 , p_to_date_type => 'BAL'
375 , p_prior_code => poa_dbi_util_pkg.no_priors
376 );
377
378 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
379 , p_col_name => 'assigned_count'
380 , p_alias_name => 'assigned'
381 , p_to_date_type => 'BAL'
382 , p_prior_code => poa_dbi_util_pkg.no_priors
383 );
384
385 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
386 , p_col_name => 'completed_count'
387 , p_alias_name => 'completed'
388 , p_to_date_type => 'BAL'
389 , p_prior_code => poa_dbi_util_pkg.no_priors
390 );
391
392 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
393 , p_col_name => 'other_count'
394 , p_alias_name => 'other'
395 , p_to_date_type => 'BAL'
396 , p_prior_code => poa_dbi_util_pkg.no_priors
397 );
398
399 l_stmt := 'select
400 ' || l_viewby_select || '
401 , ISC_MEASURE_1
402 , ISC_MEASURE_2
403 , ISC_MEASURE_3
404 , ISC_MEASURE_4_B1
405 , ISC_MEASURE_4_B2
406 , ISC_MEASURE_4_B3
407 , ISC_MEASURE_4_B4
408 , ISC_MEASURE_4_B5
409 , ISC_MEASURE_21
410 , ISC_MEASURE_22
411 , ISC_MEASURE_23
412 , ISC_MEASURE_24_B1
413 , ISC_MEASURE_24_B2
414 , ISC_MEASURE_24_B3
415 , ISC_MEASURE_24_B4
416 , ISC_MEASURE_24_B5
417 , ' || l_product || '
418 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
419 ( l_view_by
420 , g_task_rep_func
421 , 'ISC_ATTRIBUTE_2' ) || '
422 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
423 ( l_view_by
424 , g_task_rep_func
425 , 'ISC_ATTRIBUTE_3' ) || '
426 , ' || get_detail_drill( l_view_by, '1', 'ISC_ATTRIBUTE_4' ) || '
427 , ' || get_detail_drill( l_view_by, '2', 'ISC_ATTRIBUTE_4' ) || '
428 , ' || get_detail_drill( l_view_by, '3', 'ISC_ATTRIBUTE_4' ) || '
429 , ' || get_detail_drill( l_view_by, '4', 'ISC_ATTRIBUTE_4' ) || '
430 , ' || get_detail_drill( l_view_by, '5', 'ISC_ATTRIBUTE_4' ) || '
431 from (
432 select
433 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
434 , iset.*
435 from ( select * from (
436 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
437 , nvl(p_backlog,0) ISC_MEASURE_1
438 , nvl(c_backlog,0) ISC_MEASURE_2
439 , ' || isc_fs_rpt_util_pkg.change_column
440 ( 'c_backlog'
441 , 'p_backlog'
442 , 'ISC_MEASURE_3' ) || '
443 , ' || isc_fs_rpt_util_pkg.rate_column
444 ( 'c_in_planning'
445 , 'c_backlog'
446 , 'ISC_MEASURE_4_B1'
447 ) || '
448 , ' || isc_fs_rpt_util_pkg.rate_column
449 ( 'c_assigned'
450 , 'c_backlog'
451 , 'ISC_MEASURE_4_B2'
452 ) || '
453 , ' || isc_fs_rpt_util_pkg.rate_column
454 ( 'c_working'
455 , 'c_backlog'
456 , 'ISC_MEASURE_4_B3'
457 ) || '
458 , ' || isc_fs_rpt_util_pkg.rate_column
459 ( 'c_completed'
460 , 'c_backlog'
461 , 'ISC_MEASURE_4_B4'
462 ) || '
463 , ' || isc_fs_rpt_util_pkg.rate_column
464 ( 'c_other'
465 , 'c_backlog'
466 , 'ISC_MEASURE_4_B5'
467 ) || '
468 , nvl(p_backlog_total,0) ISC_MEASURE_21
469 , nvl(c_backlog_total,0) ISC_MEASURE_22
470 , ' || isc_fs_rpt_util_pkg.change_column
471 ( 'c_backlog_total'
472 , 'p_backlog_total'
473 , 'ISC_MEASURE_23' ) || '
474 , ' || isc_fs_rpt_util_pkg.rate_column
475 ( 'c_in_planning_total'
476 , 'c_backlog_total'
477 , 'ISC_MEASURE_24_B1'
478 ) || '
479 , ' || isc_fs_rpt_util_pkg.rate_column
480 ( 'c_assigned_total'
481 , 'c_backlog_total'
482 , 'ISC_MEASURE_24_B2'
483 ) || '
484 , ' || isc_fs_rpt_util_pkg.rate_column
485 ( 'c_working_total'
486 , 'c_backlog_total'
487 , 'ISC_MEASURE_24_B3'
488 ) || '
489 , ' || isc_fs_rpt_util_pkg.rate_column
490 ( 'c_completed_total'
491 , 'c_backlog_total'
492 , 'ISC_MEASURE_24_B4'
493 ) || '
494 , ' || isc_fs_rpt_util_pkg.rate_column
495 ( 'c_other_total'
496 , 'c_backlog_total'
497 , 'ISC_MEASURE_24_B5'
498 ) || '
499 from ' || poa_dbi_template_pkg.status_sql
500 ( p_fact_name => l_mv
501 , p_where_clause => l_where_clause
502 , p_join_tables => l_join_tbl
503 , p_use_windowing => 'Y' --'N'
504 , p_col_name => l_col_tbl
505 , p_use_grpid => 'N'
506 , p_paren_count => 3
507 , p_filter_where => '(isc_measure_1 >0 or isc_measure_2>0)) iset ' --null
508 , p_generate_viewby => 'Y'
509 );
510
511 poa_dbi_util_pkg.get_custom_balance_binds
512 ( p_custom_output => l_custom_output
513 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
514 , p_xtd => l_xtd
515 );
516
517 isc_fs_rpt_util_pkg.enhance_time_join
518 ( l_stmt
519 , 'N'
520 );
521
522 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
523 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
524
525 x_custom_output := l_custom_output;
526
527 x_custom_sql := l_stmt;
528
529 end get_tbl_sql;
530
531 procedure get_dtl_rpt_sql
532 ( p_param in bis_pmv_page_parameter_tbl
533 , x_custom_sql out nocopy varchar2
534 , x_custom_output out nocopy bis_query_attributes_tbl
535 )
536 as
537
538 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
539 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
540 l_custom_output bis_query_attributes_tbl;
541 l_curr_suffix varchar2(3);
542 l_where_clause varchar2(10000);
543 l_viewby_select varchar2(400); -- needed to be increased from 200
544 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
545 l_dim_bmap number;
546 l_comparison_type varchar2(200);
547 l_xtd varchar2(200);
548 l_mv varchar2(10000);
549 l_cost_element varchar2(200);
550 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
551 l_stmt varchar2(32767);
552 l_rank_order varchar2(200);
553 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
554 l_order_by varchar2(200);
555 l_asc_desc varchar2(100);
556
557 begin
558
559 isc_fs_rpt_util_pkg.register_dimension_levels
560 ( l_dimension_tbl
561 , l_dim_filter_map
562 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
563 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
564 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
565 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
566 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
567 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
568 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
569 , isc_fs_rpt_util_pkg.G_BACKLOG_STATUS, 'Y'
570 );
571
572 isc_fs_rpt_util_pkg.process_parameters
573 ( p_param => p_param
574 , p_dimension_tbl => l_dimension_tbl
575 , p_dim_filter_map => l_dim_filter_map
576 , p_trend => 'D'
577 , p_custom_output => l_custom_output
578 , x_cur_suffix => l_curr_suffix
579 , x_where_clause => l_where_clause
580 , x_viewby_select => l_viewby_select
581 , x_join_tbl => l_join_tbl
582 , x_dim_bmap => l_dim_bmap
583 , x_comparison_type => l_comparison_type
584 , x_xtd => l_xtd
585 );
586
587 l_mv := get_fact_mv_name
588 ( 'TASK_BACKLOG_DETAIL'
589 , p_param
590 , l_dim_bmap
591 , l_custom_output
592 );
593
594 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
595 ( p_param
596 , 'ORDERBY'
597 );
598
599 if l_order_by like '% DESC%' then
600 l_asc_desc := ' desc ';
601 else
602 l_asc_desc := ' asc ';
603 end if;
604
605 l_rank_order := 'order by ' ||
606 case
607 when l_order_by like '%ISC_MEASURE_1 %' then
608 'actual_start_date'
609 when l_order_by like '%ISC_MEASURE_2 %' then
610 'actual_end_date'
611 when l_order_by like '%ISC_MEASURE_3 %' then
612 'planned_start_date'
613 when l_order_by like '%ISC_MEASURE_4 %' then
614 'incident_date'
615 end ||
616 l_asc_desc ||
617 'nulls last, task_id';
618
619 isc_fs_rpt_util_pkg.add_detail_column
620 ( p_detail_col_tbl => l_detail_col_tbl
621 , p_dimension_tbl => l_dimension_tbl
622 , p_fact_col_name => 'task_number'
623 , p_fact_col_total => 'N'
624 , p_column_key => 'task_number'
625 );
626
627 isc_fs_rpt_util_pkg.add_detail_column
628 ( p_detail_col_tbl => l_detail_col_tbl
629 , p_dimension_tbl => l_dimension_tbl
630 , p_fact_col_name => 'task_id'
631 , p_fact_col_total => 'N'
632 , p_column_key => 'task_id'
633 );
634
635 isc_fs_rpt_util_pkg.add_detail_column
636 ( p_detail_col_tbl => l_detail_col_tbl
637 , p_dimension_tbl => l_dimension_tbl
638 , p_dimension_level => isc_fs_rpt_util_pkg.G_BACKLOG_STATUS
639 , p_column_key => 'task_status'
640 );
641
642 isc_fs_rpt_util_pkg.add_detail_column
643 ( p_detail_col_tbl => l_detail_col_tbl
644 , p_dimension_tbl => l_dimension_tbl
645 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_TYPE
646 , p_column_key => 'task_type'
647 );
648
649 isc_fs_rpt_util_pkg.add_detail_column
650 ( p_detail_col_tbl => l_detail_col_tbl
651 , p_dimension_tbl => l_dimension_tbl
652 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
653 , p_column_key => 'task_owner'
654 );
655
656 isc_fs_rpt_util_pkg.add_detail_column
657 ( p_detail_col_tbl => l_detail_col_tbl
658 , p_dimension_tbl => l_dimension_tbl
659 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
660 , p_column_key => 'task_assignee'
661 );
662
663 isc_fs_rpt_util_pkg.add_detail_column
664 ( p_detail_col_tbl => l_detail_col_tbl
665 , p_dimension_tbl => l_dimension_tbl
666 , p_fact_col_name => 'actual_start_date'
667 , p_fact_col_total => 'N'
668 , p_column_key => 'actual_start_date'
669 );
670
671 isc_fs_rpt_util_pkg.add_detail_column
672 ( p_detail_col_tbl => l_detail_col_tbl
673 , p_dimension_tbl => l_dimension_tbl
674 , p_fact_col_name => 'actual_end_date'
675 , p_fact_col_total => 'N'
676 , p_column_key => 'actual_end_date'
677 );
678
679 isc_fs_rpt_util_pkg.add_detail_column
680 ( p_detail_col_tbl => l_detail_col_tbl
681 , p_dimension_tbl => l_dimension_tbl
682 , p_fact_col_name => 'planned_start_date'
683 , p_fact_col_total => 'N'
684 , p_column_key => 'planned_start_date'
685 );
686
687 isc_fs_rpt_util_pkg.add_detail_column
688 ( p_detail_col_tbl => l_detail_col_tbl
689 , p_dimension_tbl => l_dimension_tbl
690 , p_fact_col_name => 'source_object_name'
691 , p_fact_col_total => 'N'
692 , p_column_key => 'source_object_name'
693 );
694
695 isc_fs_rpt_util_pkg.add_detail_column
696 ( p_detail_col_tbl => l_detail_col_tbl
697 , p_dimension_tbl => l_dimension_tbl
698 , p_fact_col_name => 'incident_date'
699 , p_fact_col_total => 'N'
700 , p_column_key => 'incident_date'
701 );
702
703 isc_fs_rpt_util_pkg.add_detail_column
704 ( p_detail_col_tbl => l_detail_col_tbl
705 , p_dimension_tbl => l_dimension_tbl
706 , p_fact_col_name => 'source_object_id'
707 , p_fact_col_total => 'N'
708 , p_column_key => 'source_object_id'
709 );
710
711 isc_fs_rpt_util_pkg.add_detail_column
712 ( p_detail_col_tbl => l_detail_col_tbl
713 , p_dimension_tbl => l_dimension_tbl
714 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
715 , p_column_key => 'customer'
716 );
717
718 isc_fs_rpt_util_pkg.add_detail_column
719 ( p_detail_col_tbl => l_detail_col_tbl
720 , p_dimension_tbl => l_dimension_tbl
721 , p_dimension_level => isc_fs_rpt_util_pkg.G_PRODUCT
722 , p_column_key => 'product'
723 );
724
725 l_stmt := 'select
726 oset.task_number ISC_ATTRIBUTE_1
727 , ' || isc_fs_rpt_util_pkg.get_detail_column
728 (l_detail_col_tbl,'task_status','ISC_ATTRIBUTE_2') || '
729 , ' || isc_fs_rpt_util_pkg.get_detail_column
730 (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_3') || '
731 , ' || isc_fs_rpt_util_pkg.get_detail_column
732 (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_4') || '
733 , ' || isc_fs_rpt_util_pkg.get_detail_column
734 (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_5') || '
735 , oset.actual_start_date ISC_MEASURE_1
736 , oset.actual_end_date ISC_MEASURE_2
737 , oset.planned_start_date ISC_MEASURE_3
738 , oset.source_object_name ISC_ATTRIBUTE_6
739 , oset.incident_date ISC_MEASURE_4
740 , ' || isc_fs_rpt_util_pkg.get_detail_column
741 (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_7') || '
742 , ' || isc_fs_rpt_util_pkg.get_detail_column
743 (l_detail_col_tbl,'product','ISC_ATTRIBUTE_8') || '
744 , ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_9
745 , null ISC_ATTRIBUTE_10'
746 -- above is needed to associate bucket set with report
747 || '
748 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_11
749 from
750 ' || isc_fs_rpt_util_pkg.detail_sql
751 ( p_detail_col_tbl => l_detail_col_tbl
752 , p_dimension_tbl => l_dimension_tbl
753 , p_mv_name => l_mv
754 , p_where_clause => l_where_clause
755 , p_rank_order => l_rank_order
756 , p_override_date_clause => 'least(&LAST_COLLECTION,&BIS_CURRENT_ASOF_DATE) between fact.backlog_date_from and fact.backlog_date_to'
757 );
758
759 poa_dbi_util_pkg.get_custom_balance_binds
760 ( p_custom_output => l_custom_output
761 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
762 , p_xtd => l_xtd
763 );
764
765 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
766 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
767
768 x_custom_sql := l_stmt;
769
770 x_custom_output := l_custom_output;
771
772 end get_dtl_rpt_sql;
773
774 end isc_fs_task_backlog_rpt_pkg;