[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_BAC_AGING_RPT_PKG
Source
1 package body isc_fs_task_bac_aging_rpt_pkg
2 /* $Header: iscfstkbarptb.pls 120.3 2006/04/12 20:47:07 kreardon noship $ */
3 as
4
5 g_detail_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_AGING_RPT_REP';
6 g_task_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_AGING_TBL_REP';
7 g_trend_rep_func constant varchar2(50) := 'ISC_FS_TASK_BAC_AGING_TRD_REP';
8
9 function get_fact_mv_name
10 ( p_report_type in varchar2
11 , p_param in bis_pmv_page_parameter_tbl
12 , p_dim_bmap in number
13 , p_custom_output in out nocopy bis_query_attributes_tbl
14 )
15 return varchar2
16 is
17
18 l_top_node varchar2(1);
19 l_resource varchar2(1);
20 l_district_leaf_node varchar2(1);
21
22 begin
23
24 if nvl(isc_fs_rpt_util_pkg.get_parameter_value
25 ( p_param
26 , isc_fs_rpt_util_pkg.G_CATEGORY
27 ),'All') = 'All' then
28 l_top_node := 'Y';
29 else
30 l_top_node := 'N';
31 end if;
32
33 if p_report_type = 'TASK_BACKLOG_AGING' then
34
35 l_district_leaf_node := isc_fs_rpt_util_pkg.is_district_leaf_node
36 ( p_param );
37
38 isc_fs_rpt_util_pkg.bind_group_id
39 ( p_dim_bmap
40 , p_custom_output
41 , isc_fs_rpt_util_pkg.G_CATEGORY
42 , isc_fs_rpt_util_pkg.G_PRODUCT
43 , isc_fs_rpt_util_pkg.G_CUSTOMER
44 );
45
46 if bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
47
48 return '(
49 select
50 v.top_node_flag vbh_top_node_flag
51 , v.parent_id vbh_parent_category_id
52 , v.imm_child_id vbh_child_category_id
53 , f.report_date ' || case
54 when l_district_leaf_node = 'N' then '
55 , den.parent_prg_id parent_district_id
56 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
57 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
58 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
59 else '
60 , f.parent_district_id
61 , f.record_type
62 , f.district_id
63 , f.district_id_c'
64 end || '
65 , f.task_type_id
66 , f.customer_id
67 , f.product_id
68 , f.backlog_count
69 , f.total_backlog_age
70 , f.backlog_age_b1
71 , f.backlog_age_b2
72 , f.backlog_age_b3
73 , f.backlog_age_b4
74 , f.backlog_age_b5
75 , f.backlog_age_b6
76 , f.backlog_age_b7
77 , f.backlog_age_b8
78 , f.backlog_age_b9
79 , f.backlog_age_b10
80 from
81 isc_fs_007_mv f' || case
82 when l_district_leaf_node = 'N' then '
83 , isc_fs_002_mv den'
84 end || '
85 , eni_denorm_hierarchies v
86 , mtl_default_category_sets m
87 where
88 m.functional_area_id = 11
89 and v.object_id = m.category_set_id
90 and v.dbi_flag = ''Y''
91 and v.object_type = ''CATEGORY_SET''
92 and f.vbh_category_id = v.child_id
93 and f.grp_id = &ISC_GRP_ID' || case
94 when l_district_leaf_node = 'N' then '
95 and f.parent_district_id = den.rg_id'
96 end ||
97 case
98 when l_top_node = 'Y' then '
99 and v.top_node_flag = ''Y''' end || '
100 )';
101
102 elsif bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_PRODUCT_BMAP) = isc_fs_rpt_util_pkg.G_PRODUCT_BMAP or
103 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP) = isc_fs_rpt_util_pkg.G_CUSTOMER_BMAP or
104 bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP) = isc_fs_rpt_util_pkg.G_TASK_TYPE_BMAP then
105
106 return '(
107 select
108 f.report_date ' || case
109 when l_district_leaf_node = 'N' then '
110 , den.parent_prg_id parent_district_id
111 , decode( den.record_type, ''GROUP'', den.record_type, f.record_type ) record_type
112 , decode( den.record_type, ''GROUP'', den.prg_id , f.district_id ) district_id
113 , decode( den.record_type, ''GROUP'', to_char(den.prg_id), f.district_id_c ) district_id_c'
114 else '
115 , f.parent_district_id
116 , f.record_type
117 , f.district_id
118 , f.district_id_c'
119 end || '
120 , f.task_type_id
121 , f.customer_id
122 , f.product_id
123 , f.backlog_count
124 , f.total_backlog_age
125 , f.backlog_age_b1
126 , f.backlog_age_b2
127 , f.backlog_age_b3
128 , f.backlog_age_b4
129 , f.backlog_age_b5
130 , f.backlog_age_b6
131 , f.backlog_age_b7
132 , f.backlog_age_b8
133 , f.backlog_age_b9
134 , f.backlog_age_b10
135 from
136 isc_fs_007_mv f' || case
137 when l_district_leaf_node = 'N' then '
138 , isc_fs_002_mv den'
139 end || '
140 where
141 f.grp_id = &ISC_GRP_ID' || case
142 when l_district_leaf_node = 'N' then '
143 and f.parent_district_id = den.rg_id'
144 end || '
145 )';
146
147 else
148
149 return '(
150 select
151 f.report_date
152 , f.parent_district_id
153 , f.record_type
154 , f.district_id
155 , f.district_id_c
156 , f.backlog_count
157 , f.total_backlog_age
158 , f.backlog_age_b1
159 , f.backlog_age_b2
160 , f.backlog_age_b3
161 , f.backlog_age_b4
162 , f.backlog_age_b5
163 , f.backlog_age_b6
164 , f.backlog_age_b7
165 , f.backlog_age_b8
166 , f.backlog_age_b9
167 , f.backlog_age_b10
168 from isc_fs_008_mv f
169 )';
170
171 end if;
172
173 elsif p_report_type = 'TASK_BACKLOG_AGING_DETAIL' then
174
175 if isc_fs_rpt_util_pkg.get_parameter_id
176 ( p_param
177 , isc_fs_rpt_util_pkg.G_DISTRICT
178 ) like '%.%' then
179 l_resource := 'Y';
180 else
181 l_resource := 'N';
182 end if;
183
184 return '(
185 select
186 t.task_id
187 , t.task_number
188 , b.backlog_date_to
189 , b.backlog_status_code
190 , t.task_type_id
191 , t.owner_id
192 , t.owner_type
193 , decode(t.first_asgn_creation_date,null,to_number(null),t.act_bac_assignee_id) assignee_id
194 , decode(t.first_asgn_creation_date,null,null,t.act_bac_assignee_type) assignee_type ' ||
195 case
196 when l_resource = 'N' then '
197 , d.parent_prg_id parent_district_id '
198 else '
199 , t.act_bac_assignee_id || ''.'' || t.act_bac_district_id district_id_c '
200 end || '
201 , t.actual_start_date
202 , t.actual_end_date
203 , t.planned_start_date
204 , greatest( 0 + ((&ISC_FS_CURRENT_ASOF_DATE + &ISC_FS_CURRENT_TIME) - nvl(t.planned_start_date,sysdate+365)), 0) age_days
205 , t.source_object_name
206 , t.source_object_id
207 , t.incident_date
208 , t.customer_id
209 , nvl(s.master_id,s.id) product_id ' ||
210 case
211 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
212 '
213 , v.top_node_flag vbh_top_node_flag
214 , v.parent_id vbh_parent_category_id
215 , v.imm_child_id vbh_child_category_id'
216 end || '
217 from
218 isc_fs_tasks_f t
219 , isc_fs_task_backlog_f b
220 , eni_oltp_item_star s' ||
221 case
222 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
223 '
224 , eni_denorm_hierarchies v
225 , mtl_default_category_sets m'
226 end ||
227 case
228 when l_resource = 'N' then '
229 , isc_fs_002_mv d'
230 end || '
231 where
232 t.task_id = b.task_id
233 and t.task_type_rule = ''DISPATCH''
234 and t.source_object_type_code = ''SR''
235 and t.deleted_flag = ''N''
236 and t.inventory_item_id = s.inventory_item_id
237 and t.inv_organization_id = s.organization_id' ||
238 case
239 when bitand(p_dim_bmap,isc_fs_rpt_util_pkg.G_CATEGORY_BMAP) = isc_fs_rpt_util_pkg.G_CATEGORY_BMAP then
240 '
241 and m.functional_area_id = 11
242 and v.object_id = m.category_set_id
243 and v.dbi_flag = ''Y''
244 and v.object_type = ''CATEGORY_SET''
245 and s.vbh_category_id = v.child_id' ||
246 case l_top_node when 'Y' then ' and v.top_node_flag = ''Y''' end
247 end ||
248 case
249 when l_resource = 'N' then '
250 and d.rg_id = t.act_bac_district_id'
251 end || '
252 )';
253
254 else -- should not happen!!!
255 return '';
256
257 end if;
258
259 end get_fact_mv_name;
260
261 procedure get_time_bit_patterns
262 ( p_xtd in varchar2
263 , p_comparison_type in varchar2
264 , x_current_bit out nocopy number
265 , x_previous_bit out nocopy number
266 )
267 is
268
269 l_period_bit_tbl isc_fs_task_bac_age_etl_pkg.t_period_bit_tbl;
270
271 begin
272
273 x_current_bit := l_period_bit_tbl(p_xtd).curr;
274 x_previous_bit := case p_comparison_type
275 when 'S' then l_period_bit_tbl(p_xtd).prior_period
276 else l_period_bit_tbl(p_xtd).prior_year
277 end;
278
279 end get_time_bit_patterns;
280
281 function get_calendar
282 ( p_xtd in varchar2
283 )
284 return varchar2
285 is
286
287 l_prefix varchar2(20);
288
289 begin
290
291 case p_xtd
292 when 'WTD' then l_prefix := 'week';
293 when 'MTD' then l_prefix := 'ent_period';
294 when 'QTD' then l_prefix := 'ent_qtr';
295 when 'YTD' then l_prefix := 'ent_year';
296 when 'DAY' then l_prefix := 'day';
297 end case;
298
299 return '(
300 select ' || l_prefix || '_start_date start_date
301 , trunc(aging_date) end_date
302 , trunc(aging_date) report_date
303 from isc_fs_task_bac_dates_c
304 where bitand(record_type_id,&ISC_FS_CURRENT_BIT)=&ISC_FS_CURRENT_BIT
305 or bitand(record_type_id,&ISC_FS_PREVIOUS_BIT)=&ISC_FS_PREVIOUS_BIT )';
306
307 end get_calendar;
308
309 procedure get_curr_prior_dates
310 ( p_xtd in varchar2
311 , p_comparison_type in varchar2
312 , p_custom_output in out nocopy bis_query_attributes_tbl
313 )
314 is
315
316 l_period_bit_tbl constant isc_fs_task_bac_age_etl_pkg.t_period_bit_tbl :=
317 isc_fs_task_bac_age_etl_pkg.get_period_bit_tbl;
318
319 l_current_as_of_date date;
320 l_previous_as_of_date date;
321 l_current_start_date date;
322 l_previous_start_date date;
323
324 l_current_bit number;
325 l_previous_bit number;
326
327 begin
328
329 l_current_bit := l_period_bit_tbl(p_xtd).curr;
330 l_previous_bit := case p_comparison_type
331 when 'S' then l_period_bit_tbl(p_xtd).prior_period
332 else l_period_bit_tbl(p_xtd).prior_year
333 end;
334
335 select
336 max( decode( bitand(record_type_id,l_current_bit)
337 , l_current_bit, aging_date
338 , null
339 ) ) current_asof_date
340 , max( decode( bitand(record_type_id,l_previous_bit)
341 , l_previous_bit, aging_date
342 , null
343 ) ) previous_asof_date
344 , min( decode( bitand(record_type_id,l_current_bit)
345 , l_current_bit, decode( l_current_bit
346 , isc_fs_task_bac_age_etl_pkg.G_WTD, week_start_date
347 , isc_fs_task_bac_age_etl_pkg.G_MTD, ent_period_start_date
348 , isc_fs_task_bac_age_etl_pkg.G_QTD, case
349 when p_comparison_type = 'Y' then
350 case
351 when rnk <=4 then ent_qtr_start_date
352 else null
353 end
354 else ent_qtr_start_date
355 end
356 , isc_fs_task_bac_age_etl_pkg.G_YTD, ent_year_start_date
357 , isc_fs_task_bac_age_etl_pkg.G_DAY, day_start_date
358 )
359 , null
360 ) ) current_report_start_date
361 , min( decode( bitand(record_type_id,l_previous_bit)
362 , l_previous_bit, decode( l_current_bit
363 , isc_fs_task_bac_age_etl_pkg.G_WTD, week_start_date
364 , isc_fs_task_bac_age_etl_pkg.G_MTD, ent_period_start_date
365 , isc_fs_task_bac_age_etl_pkg.G_QTD, ent_qtr_start_date
366 , isc_fs_task_bac_age_etl_pkg.G_YTD, ent_year_start_date
367 , isc_fs_task_bac_age_etl_pkg.G_DAY, day_start_date
368 )
369 , null
370 ) ) previous_report_start_date
371 into
372 l_current_as_of_date
373 , l_previous_as_of_date
374 , l_current_start_date
375 , l_previous_start_date
376 from
377 ( select record_type_id
378 , aging_date
379 , week_start_date
380 , ent_period_start_date
381 , ent_qtr_start_date
382 , ent_year_start_date
383 , day_start_date
384 , rank() over(partition by bitand(record_type_id,l_current_bit) order by aging_date desc) rnk
385 from isc_fs_task_bac_dates_c
386 where
387 bitand(record_type_id,l_current_bit) = l_current_bit
388 or bitand(record_type_id,l_previous_bit) = l_previous_bit
389 );
390
391 isc_fs_rpt_util_pkg.add_custom_bind_parameter
392 ( p_custom_output => p_custom_output
393 , p_parameter_name => '&ISC_FS_CURRENT_ASOF_DATE'
394 , p_parameter_data_type => bis_pmv_parameters_pub.date_bind
395 , p_parameter_value => to_char(l_current_as_of_date,'dd/mm/yyyy')
396 );
397
398 isc_fs_rpt_util_pkg.add_custom_bind_parameter
399 ( p_custom_output => p_custom_output
400 , p_parameter_name => '&ISC_FS_PREVIOUS_ASOF_DATE'
401 , p_parameter_data_type => bis_pmv_parameters_pub.date_bind
402 , p_parameter_value => to_char(l_previous_as_of_date,'dd/mm/yyyy')
403 );
404
405 isc_fs_rpt_util_pkg.add_custom_bind_parameter
406 ( p_custom_output => p_custom_output
407 , p_parameter_name => '&ISC_FS_CURRENT_TIME'
408 , p_parameter_data_type => bis_pmv_parameters_pub.numeric_bind
409 , p_parameter_value => l_current_as_of_date - trunc(l_current_as_of_date)
410 );
411
412 isc_fs_rpt_util_pkg.add_custom_bind_parameter
413 ( p_custom_output => p_custom_output
414 , p_parameter_name => '&ISC_FS_CURR_REPORT_START_DATE'
415 , p_parameter_data_type => bis_pmv_parameters_pub.date_bind
416 , p_parameter_value => to_char(l_current_start_date,'dd/mm/yyyy')
417 );
418
419 isc_fs_rpt_util_pkg.add_custom_bind_parameter
420 ( p_custom_output => p_custom_output
421 , p_parameter_name => '&ISC_FS_PREV_REPORT_START_DATE'
422 , p_parameter_data_type => bis_pmv_parameters_pub.date_bind
423 , p_parameter_value => to_char(l_previous_start_date,'dd/mm/yyyy')
424 );
425
426 isc_fs_rpt_util_pkg.add_custom_bind_parameter
427 ( p_custom_output => p_custom_output
428 , p_parameter_name => '&ISC_FS_CURRENT_BIT'
429 , p_parameter_data_type => bis_pmv_parameters_pub.numeric_bind
430 , p_parameter_value => l_current_bit
431 );
432
433 isc_fs_rpt_util_pkg.add_custom_bind_parameter
434 ( p_custom_output => p_custom_output
435 , p_parameter_name => '&ISC_FS_PREVIOUS_BIT'
436 , p_parameter_data_type => bis_pmv_parameters_pub.numeric_bind
437 , p_parameter_value => l_previous_bit
438 );
439
440 end get_curr_prior_dates;
441
442 procedure get_tbl_sql
443 ( p_param in bis_pmv_page_parameter_tbl
444 , x_custom_sql out nocopy varchar2
445 , x_custom_output out nocopy bis_query_attributes_tbl
446 )
447 is
448
449 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
450 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
451 l_custom_output bis_query_attributes_tbl;
452 l_curr_suffix varchar2(3);
453 l_where_clause varchar2(10000);
454 l_viewby_select varchar2(400); -- needed to be increased from 200
455 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
456 l_dim_bmap number;
457 l_comparison_type varchar2(200);
458 l_xtd varchar2(200);
459 l_mv varchar2(10000);
460 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
461 l_view_by varchar2(200);
462 l_product varchar2(200);
463 l_stmt varchar2(32700);
464 l_to_date_type varchar2(200);
465
466 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
467
468 begin
469
470 isc_fs_rpt_util_pkg.register_dimension_levels
471 ( l_dimension_tbl
472 , l_dim_filter_map
473 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
474 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
475 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
476 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
477 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
478 );
479
480 isc_fs_rpt_util_pkg.process_parameters
481 ( p_param => p_param
482 , p_dimension_tbl => l_dimension_tbl
483 , p_dim_filter_map => l_dim_filter_map
484 , p_trend => 'N'
485 , p_custom_output => l_custom_output
486 , x_cur_suffix => l_curr_suffix
487 , x_where_clause => l_where_clause
488 , x_viewby_select => l_viewby_select
489 , x_join_tbl => l_join_tbl
490 , x_dim_bmap => l_dim_bmap
491 , x_comparison_type => l_comparison_type
492 , x_xtd => l_xtd
493 );
494
495 l_to_date_type := 'XTD';
496
497 l_view_by := isc_fs_rpt_util_pkg.get_parameter_value
498 ( p_param
499 , 'VIEW_BY'
500 );
501
502 if l_view_by = isc_fs_rpt_util_pkg.G_PRODUCT then
503 l_product := 'v4.description ISC_ATTRIBUTE_1';
504 else
505 l_product := 'null ISC_ATTRIBUTE_1';
506 end if;
507
508 l_mv := get_fact_mv_name
509 ( 'TASK_BACKLOG_AGING'
510 , p_param
511 , l_dim_bmap
512 , l_custom_output
513 );
514
515 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
516
517 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
518 , p_col_name => 'backlog_count'
519 , p_alias_name => 'backlog'
520 , p_to_date_type => 'BAL'
521 );
522
523 poa_dbi_util_pkg.add_column( p_col_tbl => l_col_tbl
524 , p_col_name => 'total_backlog_age'
525 , p_alias_name => 'backlog_age'
526 , p_to_date_type => 'BAL'
527 );
528
529 poa_dbi_util_pkg.add_bucket_columns
530 ( p_short_name => 'BIV_FS_BACKLOG_AGING'
531 , p_col_tbl => l_col_tbl
532 , p_col_name => 'backlog_age'
533 , p_alias_name => 'backlog'
534 , p_prior_code => poa_dbi_util_pkg.no_priors
535 , p_to_date_type => 'BAL'
536 , x_bucket_rec => l_bucket_rec
537 );
538
539 l_stmt := 'select
540 ' || l_viewby_select || '
541 , ISC_MEASURE_1
542 , ISC_MEASURE_2
543 , ISC_MEASURE_3
544 , ISC_MEASURE_4
545 , ISC_MEASURE_5
546 , ISC_MEASURE_6
547 ' || poa_dbi_util_pkg.get_bucket_outer_query
548 ( p_bucket_rec => l_bucket_rec
549 , p_col_name => 'ISC_MEASURE_7'
550 , p_alias_name => 'ISC_MEASURE_7'
551 , p_prefix => null
552 , p_suffix => null
553 , p_total_flag => 'N'
554 ) || '
555 , ISC_MEASURE_21
556 , ISC_MEASURE_22
557 , ISC_MEASURE_23
558 , ISC_MEASURE_24
559 , ISC_MEASURE_25
560 , ISC_MEASURE_26
561 ' || poa_dbi_util_pkg.get_bucket_outer_query
562 ( p_bucket_rec => l_bucket_rec
563 , p_col_name => 'ISC_MEASURE_27'
564 , p_alias_name => 'ISC_MEASURE_27'
565 , p_prefix => null
566 , p_suffix => null
567 , p_total_flag => 'N'
568 ) || '
569 , ' || l_product || '
570 , ' || isc_fs_rpt_util_pkg.get_district_drill_down
571 ( l_view_by
572 , g_task_rep_func
573 , 'ISC_ATTRIBUTE_2' ) || '
574 , ' || isc_fs_rpt_util_pkg.get_category_drill_down
575 ( l_view_by
576 , g_task_rep_func
577 , 'ISC_ATTRIBUTE_3' ) || '
578 , ' || isc_fs_rpt_util_pkg.get_bucket_drill_down
579 ( p_bucket_rec => l_bucket_rec
580 , p_view_by => l_view_by
581 , p_function_name => g_detail_rep_func
582 , p_check_column_name => 'ISC_MEASURE_7'
583 , p_column_alias => 'ISC_ATTRIBUTE_4'
584 , p_extra_params => '&BIV_FS_BACKLOG_AGING='
585 , p_check_resource => 'Y'
586 ) || '
587 from (
588 select
589 row_number() over(&ORDER_BY_CLAUSE nulls last, '|| isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || ')-1 rnk
590 , iset.*
591 from ( select * from (
592 select ' || isc_fs_rpt_util_pkg.get_inner_select_col(l_join_tbl) || '
593 , nvl(p_backlog,0) ISC_MEASURE_1
594 , nvl(c_backlog,0) ISC_MEASURE_2
595 , ' || isc_fs_rpt_util_pkg.change_column
596 ( 'c_backlog'
597 , 'p_backlog'
598 , 'ISC_MEASURE_3' ) || '
599 , ' || isc_fs_rpt_util_pkg.rate_column
600 ( 'p_backlog_age'
601 , 'p_backlog'
602 , 'ISC_MEASURE_4'
603 , 'N'
604 ) || '
605 , ' || isc_fs_rpt_util_pkg.rate_column
606 ( 'c_backlog_age'
607 , 'c_backlog'
608 , 'ISC_MEASURE_5'
609 , 'N'
610 ) || '
611 , ' || isc_fs_rpt_util_pkg.change_column
612 ( isc_fs_rpt_util_pkg.rate_column
613 ( 'c_backlog_age'
614 , 'c_backlog'
615 , null
616 , 'N'
617 )
618 , isc_fs_rpt_util_pkg.rate_column
619 ( 'p_backlog_age'
620 , 'p_backlog'
621 , null
622 , 'N'
623 )
624 , 'ISC_MEASURE_6'
625 , 'N'
626 ) || '
627 ' || poa_dbi_util_pkg.get_bucket_outer_query
628 ( p_bucket_rec => l_bucket_rec
629 , p_col_name => 'backlog'
630 , p_alias_name => 'ISC_MEASURE_7'
631 , p_prefix => 'nvl(c_'
632 , p_suffix => ',0)/abs(decode(c_backlog,0,null,c_backlog))*100'
633 , p_total_flag => 'N'
634 ) || '
635 , nvl(p_backlog_total,0) ISC_MEASURE_21
636 , nvl(c_backlog_total,0) ISC_MEASURE_22
637 , ' || isc_fs_rpt_util_pkg.change_column
638 ( 'c_backlog_total'
639 , 'p_backlog_total'
640 , 'ISC_MEASURE_23' ) || '
641 , ' || isc_fs_rpt_util_pkg.rate_column
642 ( 'p_backlog_age_total'
643 , 'p_backlog_total'
644 , 'ISC_MEASURE_24'
645 , 'N'
646 ) || '
647 , ' || isc_fs_rpt_util_pkg.rate_column
648 ( 'c_backlog_age_total'
649 , 'c_backlog_total'
650 , 'ISC_MEASURE_25'
651 , 'N'
652 ) || '
653 , ' || isc_fs_rpt_util_pkg.change_column
654 ( isc_fs_rpt_util_pkg.rate_column
655 ( 'c_backlog_age_total'
656 , 'c_backlog_total'
657 , null
658 , 'N'
659 )
660 , isc_fs_rpt_util_pkg.rate_column
661 ( 'p_backlog_age_total'
662 , 'p_backlog_total'
663 , null
664 , 'N'
665 )
666 , 'ISC_MEASURE_26'
667 , 'N'
668 ) || '
669 ' || poa_dbi_util_pkg.get_bucket_outer_query
670 ( p_bucket_rec => l_bucket_rec
671 , p_col_name => 'backlog'
672 , p_alias_name => 'ISC_MEASURE_27'
673 , p_prefix => 'nvl(c_'
674 , p_suffix => ',0)/abs(decode(c_backlog_total,0,null,c_backlog_total))*100'
675 , p_total_flag => 'Y'
676 ) || '
677 from
678 ' || poa_dbi_template_pkg.status_sql
679 ( p_fact_name => l_mv
680 , p_where_clause => l_where_clause
681 , p_join_tables => l_join_tbl
682 , p_use_windowing => 'Y' --'N'
683 , p_col_name => l_col_tbl
684 , p_use_grpid => 'N'
685 , p_paren_count => 3
686 , p_filter_where => '(isc_measure_1 >0 or isc_measure_2>0)) iset '
687 , p_generate_viewby => 'Y'
688 );
689
690 poa_dbi_util_pkg.get_custom_balance_binds
691 ( p_custom_output => l_custom_output
692 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
693 , p_xtd => l_xtd
694 );
695
696 get_curr_prior_dates
697 ( l_xtd
698 , l_comparison_type
699 , l_custom_output
700 );
701
702 l_stmt := replace( l_stmt, 'BIS_PREVIOUS_EFFECTIVE_END_DATE', 'ISC_FS_PREVIOUS_ASOF_DATE' );
703 l_stmt := replace( l_stmt, 'BIS_CURRENT_EFFECTIVE_END_DATE', 'ISC_FS_CURRENT_ASOF_DATE' );
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_output := l_custom_output;
709
710 x_custom_sql := l_stmt;
711
712 end get_tbl_sql;
713
714 procedure get_trd_sql
715 ( p_param in bis_pmv_page_parameter_tbl
716 , x_custom_sql out nocopy varchar2
717 , x_custom_output out nocopy bis_query_attributes_tbl
718 )
719 is
720
721 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
722 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
723 l_custom_output bis_query_attributes_tbl;
724 l_curr_suffix varchar2(3);
725 l_where_clause varchar2(10000);
726 l_viewby_select varchar2(400); -- needed to be increased from 200
727 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
728 l_dim_bmap number;
729 l_comparison_type varchar2(200);
730 l_xtd varchar2(200);
731 l_mv varchar2(10000);
732 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
733 l_stmt varchar2(32767);
734 l_to_date_type varchar2(200);
735
736 begin
737
738 isc_fs_rpt_util_pkg.register_dimension_levels
739 ( l_dimension_tbl
740 , l_dim_filter_map
741 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
742 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
743 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
744 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
745 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
746 );
747
748 isc_fs_rpt_util_pkg.process_parameters
749 ( p_param => p_param
750 , p_dimension_tbl => l_dimension_tbl
751 , p_dim_filter_map => l_dim_filter_map
752 , p_trend => 'Y'
753 , p_custom_output => l_custom_output
754 , x_cur_suffix => l_curr_suffix
755 , x_where_clause => l_where_clause
756 , x_viewby_select => l_viewby_select
757 , x_join_tbl => l_join_tbl
758 , x_dim_bmap => l_dim_bmap
759 , x_comparison_type => l_comparison_type
760 , x_xtd => l_xtd
761 );
762
763 l_to_date_type := 'XTD';
764
765 l_mv := get_fact_mv_name
766 ( 'TASK_BACKLOG_AGING'
767 , p_param
768 , l_dim_bmap
769 , l_custom_output
770 );
771
772 l_stmt := 'select
773 cal.name VIEWBY
774 , nvl(iset.p_backlog,0) ISC_MEASURE_1
775 , nvl(iset.c_backlog,0) ISC_MEASURE_2
776 , ' || isc_fs_rpt_util_pkg.change_column
777 ( 'c_backlog'
778 , 'p_backlog'
779 , 'ISC_MEASURE_3' ) || '
780 , ' || isc_fs_rpt_util_pkg.rate_column
781 ( 'p_backlog_age'
782 , 'p_backlog'
783 , 'ISC_MEASURE_4'
784 , 'N'
785 ) || '
786 , ' || isc_fs_rpt_util_pkg.rate_column
787 ( 'c_backlog_age'
788 , 'c_backlog'
789 , 'ISC_MEASURE_5'
790 , 'N'
791 ) || '
792 , ' || isc_fs_rpt_util_pkg.change_column
793 ( isc_fs_rpt_util_pkg.rate_column
794 ( 'c_backlog_age'
795 , 'c_backlog'
796 , null
797 , 'N'
798 )
799 , isc_fs_rpt_util_pkg.rate_column
800 ( 'p_backlog_age'
801 , 'p_backlog'
802 , null
803 , 'N'
804 )
805 , 'ISC_MEASURE_6'
806 , 'N'
807 ) || '
808 from
809 ( select
810 n.start_date
811 , sum(case
812 when (n.start_date between &ISC_FS_CURR_REPORT_START_DATE and &ISC_FS_CURRENT_ASOF_DATE and i.report_date = LEAST (n.end_date, &ISC_FS_CURRENT_ASOF_DATE) ) then
813 backlog
814 else
815 null
816 end) c_backlog
817 , lag(sum(case
818 when (n.start_date between &ISC_FS_PREV_REPORT_START_DATE and &ISC_FS_PREVIOUS_ASOF_DATE and i.report_date = LEAST (n.end_date, &ISC_FS_PREVIOUS_ASOF_DATE) ) then
819 backlog
820 else
821 null
822 end), &LAG) over (order by n.start_date) p_backlog
823 , sum(case
824 when (n.start_date between &ISC_FS_CURR_REPORT_START_DATE and &ISC_FS_CURRENT_ASOF_DATE and i.report_date = LEAST (n.end_date, &ISC_FS_CURRENT_ASOF_DATE) ) then
825 backlog_age
826 else
827 null
828 end) c_backlog_age
829 , lag(sum(case
830 when (n.start_date between &ISC_FS_PREV_REPORT_START_DATE and &ISC_FS_PREVIOUS_ASOF_DATE and i.report_date = LEAST (n.end_date, &ISC_FS_PREVIOUS_ASOF_DATE) ) then
831 backlog_age
832 else
833 null
834 end), &LAG) over (order by n.start_date) p_backlog_age
835 from
836 ( select
837 n.start_date
838 , n.report_date
839 , sum(backlog_count) backlog
840 , sum(total_backlog_age) backlog_age
841 from ' || l_mv || ' fact
842 , ' || get_calendar(l_xtd) || ' n
843 where fact.report_date = n.report_date
844 and n.start_date between &ISC_FS_PREV_REPORT_START_DATE and &ISC_FS_CURRENT_ASOF_DATE
845 ' || l_where_clause || '
846 group by
847 n.start_date
848 , n.report_date
849 ) i
850 , ' || poa_dbi_util_pkg.get_calendar_table
851 ( period_type => l_xtd
852 , p_include_prior => 'N'
853 ) || ' n
854 where i.start_date(+) = n.start_date
855 and n.start_date between &ISC_FS_PREV_REPORT_START_DATE and &ISC_FS_CURRENT_ASOF_DATE
856 group by
857 n.start_date
858 ) iset
859 , ' || poa_dbi_util_pkg.get_calendar_table
860 ( period_type => l_xtd
861 , p_include_prior => 'N'
862 ) || ' cal
863 where cal.start_date between &ISC_FS_CURR_REPORT_START_DATE and &ISC_FS_CURRENT_ASOF_DATE
864 and cal.start_date = iset.start_date(+)
865 order by cal.start_date';
866
867 l_stmt := replace( l_stmt
868 , '&BIS_PREVIOUS_EFFECTIVE_END_DATE'
869 , '&ISC_FS_PREVIOUS_ASOF_DATE'
870 );
871 l_stmt := replace( l_stmt
872 , '&BIS_CURRENT_EFFECTIVE_END_DATE'
873 , '&ISC_FS_CURRENT_ASOF_DATE'
874 );
875 l_stmt := replace( l_stmt
876 , '&BIS_PREVIOUS_REPORT_START_DATE'
877 , '&ISC_FS_PREV_REPORT_START_DATE'
878 );
879 l_stmt := replace( l_stmt
880 , '&BIS_PREVIOUS_ASOF_DATE'
881 , '&ISC_FS_PREVIOUS_ASOF_DATE'
882 );
883 l_stmt := replace( l_stmt
884 , '&BIS_CURRENT_REPORT_START_DATE'
885 , '&ISC_FS_CURR_REPORT_START_DATE'
886 );
887 l_stmt := replace( l_stmt
888 , '&BIS_CURRENT_ASOF_DATE'
889 , '&ISC_FS_CURRENT_ASOF_DATE'
890 );
891 l_stmt := replace( l_stmt
892 , '&BIS_CURRENT_EFFECTIVE_START_DATE'
893 , 'sysdate' -- never actually used
894 );
895
896 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
897 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
898
899 x_custom_sql := l_stmt;
900
901 poa_dbi_util_pkg.get_custom_balance_binds
902 ( p_custom_output => l_custom_output
903 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
904 , p_xtd => l_xtd
905 );
906
907 get_curr_prior_dates
908 ( l_xtd
909 , l_comparison_type
910 , l_custom_output
911 );
912
913 x_custom_output := l_custom_output;
914
915 poa_dbi_util_pkg.get_custom_trend_binds
916 ( p_xtd => l_xtd
917 , p_comparison_type => l_comparison_type
918 , x_custom_output => l_custom_output
919 );
920
921 if l_custom_output is not null then
922 for i in 1..l_custom_output.count loop
923 x_custom_output.extend;
924 x_custom_output(x_custom_output.count) := l_custom_output(i);
925 end loop;
926 end if;
927
928 end get_trd_sql;
929
930 procedure get_dtl_rpt_sql
931 ( p_param in bis_pmv_page_parameter_tbl
932 , x_custom_sql out nocopy varchar2
933 , x_custom_output out nocopy bis_query_attributes_tbl
934 )
935 as
936
937 l_dimension_tbl isc_fs_rpt_util_pkg.t_dimension_tbl;
938 l_dim_filter_map poa_dbi_util_pkg.poa_dbi_dim_map;
939 l_custom_output bis_query_attributes_tbl;
940 l_curr_suffix varchar2(3);
941 l_where_clause varchar2(10000);
942 l_viewby_select varchar2(400); -- needed to be increased from 200
943 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
944 l_dim_bmap number;
945 l_comparison_type varchar2(200);
946 l_xtd varchar2(200);
947 l_mv varchar2(10000);
948 l_cost_element varchar2(200);
949 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
950 l_stmt varchar2(32767);
951 l_rank_order varchar2(200);
952 l_detail_col_tbl isc_fs_rpt_util_pkg.t_detail_column_tbl;
953 l_order_by varchar2(200);
954 l_asc_desc varchar2(100);
955
956 begin
957
958 isc_fs_rpt_util_pkg.register_dimension_levels
959 ( l_dimension_tbl
960 , l_dim_filter_map
961 , isc_fs_rpt_util_pkg.G_CATEGORY, 'Y'
962 , isc_fs_rpt_util_pkg.G_PRODUCT, 'Y'
963 , isc_fs_rpt_util_pkg.G_CUSTOMER, 'Y'
964 , isc_fs_rpt_util_pkg.G_DISTRICT, 'Y'
965 , isc_fs_rpt_util_pkg.G_TASK_TYPE, 'Y'
966 , isc_fs_rpt_util_pkg.G_TASK_OWNER, 'N'
967 , isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE, 'N'
968 );
969
970 isc_fs_rpt_util_pkg.process_parameters
971 ( p_param => p_param
972 , p_dimension_tbl => l_dimension_tbl
973 , p_dim_filter_map => l_dim_filter_map
974 , p_trend => 'D'
975 , p_custom_output => l_custom_output
976 , x_cur_suffix => l_curr_suffix
977 , x_where_clause => l_where_clause
978 , x_viewby_select => l_viewby_select
979 , x_join_tbl => l_join_tbl
980 , x_dim_bmap => l_dim_bmap
981 , x_comparison_type => l_comparison_type
982 , x_xtd => l_xtd
983 );
984
985 l_mv := get_fact_mv_name
986 ( 'TASK_BACKLOG_AGING_DETAIL'
987 , p_param
988 , l_dim_bmap
989 , l_custom_output
990 );
991
992 l_order_by := isc_fs_rpt_util_pkg.get_parameter_value
993 ( p_param
994 , 'ORDERBY'
995 );
996
997 if l_order_by like '% DESC%' then
998 l_asc_desc := ' desc ';
999 else
1000 l_asc_desc := ' asc ';
1001 end if;
1002
1003 l_rank_order := 'order by ' ||
1004 case
1005 when l_order_by like '%ISC_MEASURE_1 %' then
1006 'actual_start_date'
1007 when l_order_by like '%ISC_MEASURE_2 %' then
1008 'actual_end_date'
1009 when l_order_by like '%ISC_MEASURE_3 %' then
1010 'planned_start_date'
1011 when l_order_by like '%ISC_MEASURE_4 %' then
1012 'age_days'
1013 when l_order_by like '%ISC_MEASURE_5 %' then
1014 'incident_date'
1015 end ||
1016 l_asc_desc ||
1017 'nulls last, task_id';
1018
1019 isc_fs_rpt_util_pkg.add_detail_column
1020 ( p_detail_col_tbl => l_detail_col_tbl
1021 , p_dimension_tbl => l_dimension_tbl
1022 , p_fact_col_name => 'task_number'
1023 , p_fact_col_total => 'N'
1024 , p_column_key => 'task_number'
1025 );
1026
1027 isc_fs_rpt_util_pkg.add_detail_column
1028 ( p_detail_col_tbl => l_detail_col_tbl
1029 , p_dimension_tbl => l_dimension_tbl
1030 , p_fact_col_name => 'task_id'
1031 , p_fact_col_total => 'N'
1032 , p_column_key => 'task_id'
1033 );
1034
1035 isc_fs_rpt_util_pkg.add_detail_column
1036 ( p_detail_col_tbl => l_detail_col_tbl
1037 , p_dimension_tbl => l_dimension_tbl
1038 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_TYPE
1039 , p_column_key => 'task_type'
1040 );
1041
1042 isc_fs_rpt_util_pkg.add_detail_column
1043 ( p_detail_col_tbl => l_detail_col_tbl
1044 , p_dimension_tbl => l_dimension_tbl
1045 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_OWNER
1046 , p_column_key => 'task_owner'
1047 );
1048
1049 isc_fs_rpt_util_pkg.add_detail_column
1050 ( p_detail_col_tbl => l_detail_col_tbl
1051 , p_dimension_tbl => l_dimension_tbl
1052 , p_dimension_level => isc_fs_rpt_util_pkg.G_TASK_ASSIGNEE
1053 , p_column_key => 'task_assignee'
1054 );
1055
1056 isc_fs_rpt_util_pkg.add_detail_column
1057 ( p_detail_col_tbl => l_detail_col_tbl
1058 , p_dimension_tbl => l_dimension_tbl
1059 , p_fact_col_name => 'actual_start_date'
1060 , p_fact_col_total => 'N'
1061 , p_column_key => 'actual_start_date'
1062 );
1063
1064 isc_fs_rpt_util_pkg.add_detail_column
1065 ( p_detail_col_tbl => l_detail_col_tbl
1066 , p_dimension_tbl => l_dimension_tbl
1067 , p_fact_col_name => 'actual_end_date'
1068 , p_fact_col_total => 'N'
1069 , p_column_key => 'actual_end_date'
1070 );
1071
1072 isc_fs_rpt_util_pkg.add_detail_column
1073 ( p_detail_col_tbl => l_detail_col_tbl
1074 , p_dimension_tbl => l_dimension_tbl
1075 , p_fact_col_name => 'planned_start_date'
1076 , p_fact_col_total => 'N'
1077 , p_column_key => 'planned_start_date'
1078 );
1079
1080 isc_fs_rpt_util_pkg.add_detail_column
1081 ( p_detail_col_tbl => l_detail_col_tbl
1082 , p_dimension_tbl => l_dimension_tbl
1083 , p_fact_col_name => 'age_days'
1084 , p_fact_col_total => 'N'
1085 , p_column_key => 'age_days'
1086 );
1087
1088 isc_fs_rpt_util_pkg.add_detail_column
1089 ( p_detail_col_tbl => l_detail_col_tbl
1090 , p_dimension_tbl => l_dimension_tbl
1091 , p_fact_col_name => 'source_object_name'
1092 , p_fact_col_total => 'N'
1093 , p_column_key => 'source_object_name'
1094 );
1095
1096 isc_fs_rpt_util_pkg.add_detail_column
1097 ( p_detail_col_tbl => l_detail_col_tbl
1098 , p_dimension_tbl => l_dimension_tbl
1099 , p_fact_col_name => 'incident_date'
1100 , p_fact_col_total => 'N'
1101 , p_column_key => 'incident_date'
1102 );
1103
1104 isc_fs_rpt_util_pkg.add_detail_column
1105 ( p_detail_col_tbl => l_detail_col_tbl
1106 , p_dimension_tbl => l_dimension_tbl
1107 , p_fact_col_name => 'source_object_id'
1108 , p_fact_col_total => 'N'
1109 , p_column_key => 'source_object_id'
1110 );
1111
1112 isc_fs_rpt_util_pkg.add_detail_column
1113 ( p_detail_col_tbl => l_detail_col_tbl
1114 , p_dimension_tbl => l_dimension_tbl
1115 , p_dimension_level => isc_fs_rpt_util_pkg.G_CUSTOMER
1116 , p_column_key => 'customer'
1117 );
1118
1119 isc_fs_rpt_util_pkg.add_detail_column
1120 ( p_detail_col_tbl => l_detail_col_tbl
1121 , p_dimension_tbl => l_dimension_tbl
1122 , p_dimension_level => isc_fs_rpt_util_pkg.G_PRODUCT
1123 , p_column_key => 'product'
1124 );
1125
1126 l_stmt := 'select
1127 oset.task_number ISC_ATTRIBUTE_1
1128 , ' || isc_fs_rpt_util_pkg.get_detail_column
1129 (l_detail_col_tbl,'task_type','ISC_ATTRIBUTE_2') || '
1130 , ' || isc_fs_rpt_util_pkg.get_detail_column
1131 (l_detail_col_tbl,'task_owner','ISC_ATTRIBUTE_3') || '
1132 , ' || isc_fs_rpt_util_pkg.get_detail_column
1133 (l_detail_col_tbl,'task_assignee','ISC_ATTRIBUTE_4') || '
1134 , oset.actual_start_date ISC_MEASURE_1
1135 , oset.actual_end_date ISC_MEASURE_2
1136 , oset.planned_start_date ISC_MEASURE_3
1137 , oset.age_days ISC_MEASURE_4
1138 , oset.source_object_name ISC_ATTRIBUTE_5
1139 , oset.incident_date ISC_MEASURE_5
1140 , ' || isc_fs_rpt_util_pkg.get_detail_column
1141 (l_detail_col_tbl,'customer','ISC_ATTRIBUTE_6') || '
1142 , ' || isc_fs_rpt_util_pkg.get_detail_column
1143 (l_detail_col_tbl,'product','ISC_ATTRIBUTE_7') || '
1144 , ' || isc_fs_rpt_util_pkg.get_sr_detail_page_function('oset.source_object_id') || ' ISC_ATTRIBUTE_8
1145 , null ISC_ATTRIBUTE_9'
1146 -- above is needed to associate bucket set with report
1147 || '
1148 , ' || isc_fs_rpt_util_pkg.get_task_detail_page_function('oset.task_id') || ' ISC_ATTRIBUTE_10
1149 from
1150 ' || isc_fs_rpt_util_pkg.detail_sql
1151 ( p_detail_col_tbl => l_detail_col_tbl
1152 , p_dimension_tbl => l_dimension_tbl
1153 , p_mv_name => l_mv
1154 , p_where_clause => l_where_clause
1155 , p_rank_order => l_rank_order
1156 , p_override_date_clause => 'backlog_date_to = to_date(''4712/12/31'',''yyyy/mm/dd'') and age_days between &ISC_FS_LOW and &ISC_FS_HIGH'
1157 );
1158
1159 poa_dbi_util_pkg.get_custom_balance_binds
1160 ( p_custom_output => l_custom_output
1161 , p_balance_fact => isc_fs_task_act_bac_etl_pkg.g_object_name
1162 , p_xtd => l_xtd
1163 );
1164
1165 -- needed because used in calculating the age at ISC_FS_CURRENT_ASOF_DATE + &ISC_FS_CURRENT_TIME
1166 get_curr_prior_dates
1167 ( l_xtd
1168 , l_comparison_type
1169 , l_custom_output
1170 );
1171
1172 isc_fs_rpt_util_pkg.bind_low_high
1173 ( p_param
1174 , isc_fs_rpt_util_pkg.G_BACKLOG_AGING_DISTRIB
1175 , 'BIV_FS_BACKLOG_AGING'
1176 , l_custom_output
1177 );
1178
1179 -- the next line can be used to dump the contents of the PMV parameters as comments into stmt
1180 -- l_stmt := l_stmt || isc_fs_rpt_util_pkg.dump_parameters(p_param);
1181
1182 x_custom_sql := l_stmt;
1183
1184 x_custom_output := l_custom_output;
1185
1186 end get_dtl_rpt_sql;
1187
1188 end isc_fs_task_bac_aging_rpt_pkg;