[Home] [Help]
PACKAGE BODY: APPS.BIV_DBI_COLLECTION_AGE
Source
1 package body biv_dbi_collection_age as
2 /* $Header: bivsrvcageb.pls 120.0 2005/05/25 10:50:34 appldev noship $ */
3
4 g_bis_setup_exception exception;
5 g_user_id number := fnd_global.user_id;
6 g_login_id number := fnd_global.login_id;
7
8 procedure load_backlog_aging
9 ( errbuf in out nocopy varchar2
10 , retcode in out nocopy varchar2)
11 is
12
13 l_exception exception;
14 l_error_message varchar2(4000);
15
16 l_log_rowid rowid;
17 l_process_type varchar2(30);
18 l_collect_from_date date;
19 l_collect_to_date date;
20 l_success_flag varchar2(1);
21 l_staging_flag varchar2(1);
22 l_activity_flag varchar2(1);
23 l_closed_flag varchar2(1);
24 l_backlog_flag varchar2(1);
25 l_resolution_flag varchar2(1);
26
27 l_biv_schema varchar2(100);
28
29 l_rowcount number;
30
31 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
32 l_return_status varchar2(3);
33 l_error_tbl bis_utilities_pub.error_tbl_type;
34
35 begin
36
37 if not bis_collection_utilities.setup( 'BIV_BAC_AGE_SUM_F' ) then
38 raise g_bis_setup_exception;
39 end if;
40
41 biv_dbi_collection_util.get_last_log( l_log_rowid
42 , l_process_type
43 , l_collect_from_date
44 , l_collect_to_date
45 , l_success_flag
46 , l_staging_flag
47 , l_activity_flag
48 , l_closed_flag
49 , l_backlog_flag
50 , l_resolution_flag
51 );
52
53 if nvl(l_success_flag,'X') <> 'Y' then
54 l_error_message := 'Backlog Age process called for after an incompleted initial/incremental load';
55 raise l_exception;
56 end if;
57
58 bis_bucket_pub.retrieve_bis_bucket
59 ( p_short_name => 'BIV_DBI_BACKLOG_AGING'
60 , x_bis_bucket_rec => l_bucket_rec
61 , x_return_status => l_return_status
62 , x_error_tbl => l_error_tbl
63 );
64
65 if l_return_status <> 'S' then
66 if l_error_tbl is not null then
67 l_error_message := l_error_tbl(1).error_description;
68 else
69 l_error_message := 'Unable to retrieve aging bucket definition';
70 end if;
71 raise l_exception;
72 end if;
73
74 bis_collection_utilities.log('Starting Backlog Aging Load, aging As At ' ||
75 fnd_date.date_to_displaydt(l_collect_to_date));
76
77 if biv_dbi_collection_util.get_schema_name
78 ( l_biv_schema
79 , l_error_message ) <> 0 then
80 raise l_exception;
81 end if;
82
83 bis_collection_utilities.log('truncating dates table',1);
84
85 if biv_dbi_collection_util.truncate_table
86 ( l_biv_schema
87 , 'BIV_DBI_BACKLOG_AGE_DATES'
88 , l_error_message ) <> 0 then
89 raise l_exception;
90 end if;
91
92 bis_collection_utilities.log('loading dates table',1);
93
94 insert
95 into biv_dbi_backlog_age_dates
96 ( report_date
97 , record_type_id
98 , creation_date
99 , created_by
100 , last_update_date
101 , last_updated_by
102 , last_update_login
103 )
104 select
105 report_date
106 , record_type_id
107 , sysdate
108 , g_user_id
109 , sysdate
110 , g_user_id
111 , g_login_id
112 from
113 (
114 /* Dates for RLX model */
115 select
116 least(add_months(trunc(l_collect_to_date),case when m.id >8 then -12 else 0 end)
117 + t.offset * case
118 when m.id in (1,5,9) then 7
119 when m.id in (2,6,10) then 30
120 when m.id in (3,7,11) then 90
121 when m.id in (4,8,12) then 365
122 else 1
123 end
124 + case
125 when m.id = 5 then -7
126 when m.id = 6 then -30
127 when m.id = 7 then -90
128 when m.id = 8 then -365
129 else 0
130 end
131 + 86399/86400,l_collect_to_date) report_date
132 , sum( power(2,m.id) ) record_type_id
133 from
134 biv_trend_rpt t
135 , oki_dbi_multiplexer_b m
136 where t.current_ind = 1
137 and m.id <= 12
138 and t.offset > case
139 when m.id in (1,5,9) then -13
140 when m.id in (2,6,10) then -12
141 when m.id in (3,7,11) then -8
142 when m.id in (4,8,12) then -4
143 else 1
144 end
145 group by
146 least(add_months(trunc(l_collect_to_date),case when m.id >8 then -12 else 0 end)
147 + t.offset * case
148 when m.id in (1,5,9) then 7
149 when m.id in (2,6,10) then 30
150 when m.id in (3,7,11) then 90
151 when m.id in (4,8,12) then 365
152 else 1
153 end
154 + case
155 when m.id = 5 then -7
156 when m.id = 6 then -30
157 when m.id = 7 then -90
158 when m.id = 8 then -365
159 else 0
160 end
161 + 86399/86400,l_collect_to_date)
162 UNION ALL
163 /* Dates for XTD Model */
164 select
165 least(end_date + 86399/86400, l_collect_to_date) report_date
166 ,sum( power(2,id+12) ) record_type_id
167 from
168 (select -- DAY
169 id
170 , end_date
171 from
172 ( select m.id
173 , least(fii.end_date, m.the_date) end_date
174 , rank() over (partition by m.id order by fii.start_date desc ) rnk
175 from fii_time_day fii
176 , (select
177 id
178 , case
179 when id = 11 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date)
180 when id = 6 then (trunc(l_collect_to_date))
181 else l_collect_to_date
182 end the_date
183 from oki_dbi_multiplexer_b
184 where id in (1,6,11)
185 ) m
186 where fii.start_date < m.the_date
187 )
188 where rnk <= 7
189 --
190 union all
191 --
192 select -- WTD
193 id
194 , end_date
195 from
196 ( select m.id
197 , least(fii.end_date, m.the_date) end_date
198 , rank() over (partition by m.id order by fii.start_date desc ) rnk
199 from fii_time_week fii
200 , (select
201 id
202 , case
203 when id = 12 then FII_TIME_API.sd_lyswk(l_collect_to_date)
204 when id = 7 then FII_TIME_API.sd_pwk(l_collect_to_date)
205 else l_collect_to_date
206 end the_date
207 from oki_dbi_multiplexer_b
208 where id in (2,7,12)
209 ) m
210 where fii.start_date < m.the_date
211 )
212 where rnk <= 13
213 --
214 union all
215 --
216 select -- MTD
217 id
218 , end_date
219 from
220 ( select m.id
221 , least(fii.end_date, m.the_date) end_date
222 , rank() over (partition by m.id order by fii.start_date desc ) rnk
223 from fii_time_ent_period fii
224 , (select
225 id
226 , case
227 when id = 13 then FII_TIME_API.ent_sd_lysper_end(l_collect_to_date)
228 when id = 8 then FII_TIME_API.ent_sd_pper_end(l_collect_to_date)
229 else l_collect_to_date
230 end the_date
231 from oki_dbi_multiplexer_b
232 where id in (3,8,13)
233 ) m
234 where fii.start_date < m.the_date
235 )
236 where rnk <= 12
237 --
238 union all
239 --
240 select -- QTD
241 id
242 , end_date
243 from
244 ( select m.id
245 , least(fii.end_date, m.the_date) end_date
246 , rank() over (partition by m.id order by fii.start_date desc ) rnk
247 from fii_time_ent_qtr fii
248 , (select
249 id
250 , case
251 when id = 14 then FII_TIME_API.ent_sd_lysqtr_end(l_collect_to_date)
252 when id = 9 then FII_TIME_API.ent_sd_pqtr_end (l_collect_to_date)
253 else l_collect_to_date
254 end the_date
255 from oki_dbi_multiplexer_b
256 where id in (4,9,14)
257 ) m
258 where fii.start_date < m.the_date
259 )
260 where (id in (4,9) and rnk <=8) or (id = 14 and rnk <= 4)
261 --
262 union all
263 --
264 select -- YTD
265 id
266 , end_date
267 from
268 ( select m.id
269 , least(fii.end_date, m.the_date) end_date
270 , rank() over (partition by m.id order by fii.start_date desc ) rnk
271 from fii_time_ent_year fii
272 , (select
273 id
274 , case
275 when id = 15 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date)
276 when id = 10 then FII_TIME_API.ent_sd_lyr_end(l_collect_to_date)
277 else l_collect_to_date
278 end the_date
279 from oki_dbi_multiplexer_b
280 where id in (5,10,15)
281 ) m
282 where fii.start_date < m.the_date
283 )
284 where rnk <= 4
285 )
286 group by end_date
287 );
288
289 l_rowcount := sql%rowcount;
290
291 bis_collection_utilities.log(l_rowcount || ' rows inserted',2);
292
293 bis_collection_utilities.log('gathering stats on dates table',1);
294
295 if biv_dbi_collection_util.gather_statistics
296 ( l_biv_schema
297 , 'BIV_DBI_BACKLOG_AGE_DATES'
298 , l_error_message ) <> 0 then
299 raise l_exception;
300 end if;
301
302 bis_collection_utilities.log('truncating Backlog Aging table',1);
303
304 if biv_dbi_collection_util.truncate_table
305 ( l_biv_schema
306 , 'BIV_BAC_AGE_SUM_F'
307 , l_error_message ) <> 0 then
308 raise l_exception;
309 end if;
310
311 bis_collection_utilities.log('inserting Backlog Aging rows',1);
312
313 insert /*+ APPEND parallel(f) */
314 into biv_bac_age_sum_f f
315 ( report_date
316 , grp_id
317 , incident_type_id
318 , incident_severity_id
319 , customer_id
320 , owner_group_id
321 , incident_status_id
322 , vbh_category_id
323 , product_id
324 , backlog_count
325 , total_backlog_age
326 , backlog_age_b1
327 , backlog_age_b2
328 , backlog_age_b3
329 , backlog_age_b4
330 , backlog_age_b5
331 , backlog_age_b6
332 , backlog_age_b7
333 , backlog_age_b8
334 , backlog_age_b9
335 , backlog_age_b10
336 , escalated_count
337 , total_escalated_age
338 , escalated_age_b1
339 , escalated_age_b2
340 , escalated_age_b3
341 , escalated_age_b4
342 , escalated_age_b5
343 , escalated_age_b6
344 , escalated_age_b7
345 , escalated_age_b8
346 , escalated_age_b9
347 , escalated_age_b10
348 , unowned_count
349 , total_unowned_age
350 , unowned_age_b1
351 , unowned_age_b2
352 , unowned_age_b3
353 , unowned_age_b4
354 , unowned_age_b5
355 , unowned_age_b6
356 , unowned_age_b7
357 , unowned_age_b8
358 , unowned_age_b9
359 , unowned_age_b10
360 , creation_date
361 , created_by
362 , last_update_date
363 , last_updated_by
364 , last_update_login
365 , resolved_flag
366 , incident_urgency_id
367 , incident_owner_id
368 , escalated_flag
369 )
370 select /*+ parallel(f) parallel(c) parallel(pc) ordered use_merge(F) */
371 trunc(c.report_date) report_date
372 , 0 grp_id
373 , f.incident_type_id
374 , f.incident_severity_id
375 , f.customer_id
376 , f.owner_group_id
377 , f.incident_status_id
378 , pc.vbh_category_id
379 , nvl(pc.master_id,pc.id)
380 , count(*) backlog_count
381 , sum(c.report_date-f.incident_date)
382 total_backlog_age
383 , sum(case
384 when (l_bucket_rec.range1_low is null or
385 c.report_date-f.incident_date >= l_bucket_rec.range1_low) and
386 (l_bucket_rec.range1_high is null or
387 c.report_date-f.incident_date < l_bucket_rec.range1_high) then
388 1 else 0
389 end) backlog_age_b1
390 , sum(case
391 when c.report_date-f.incident_date >= l_bucket_rec.range2_low and
392 (l_bucket_rec.range2_high is null or
393 c.report_date-f.incident_date < l_bucket_rec.range2_high) then
394 1 else 0
395 end) backlog_age_b2
396 , sum(case
397 when c.report_date-f.incident_date >= l_bucket_rec.range3_low and
398 (l_bucket_rec.range3_high is null or
399 c.report_date-f.incident_date < l_bucket_rec.range3_high) then
400 1 else 0
401 end) backlog_age_b3
402 , sum(case
403 when c.report_date-f.incident_date >= l_bucket_rec.range4_low and
404 (l_bucket_rec.range4_high is null or
405 c.report_date-f.incident_date < l_bucket_rec.range4_high) then
406 1 else 0
407 end) backlog_age_b4
408 , sum(case
409 when c.report_date-f.incident_date >= l_bucket_rec.range5_low and
410 (l_bucket_rec.range5_high is null or
411 c.report_date-f.incident_date < l_bucket_rec.range5_high) then
412 1 else 0
413 end) backlog_age_b5
414 , sum(case
415 when c.report_date-f.incident_date >= l_bucket_rec.range6_low and
416 (l_bucket_rec.range6_high is null or
417 c.report_date-f.incident_date < l_bucket_rec.range6_high) then
418 1 else 0
419 end) backlog_age_b6
420 , sum(case
421 when c.report_date-f.incident_date >= l_bucket_rec.range7_low and
422 (l_bucket_rec.range7_high is null or
423 c.report_date-f.incident_date < l_bucket_rec.range7_high) then
424 1 else 0
425 end) backlog_age_b7
426 , sum(case
427 when c.report_date-f.incident_date >= l_bucket_rec.range8_low and
428 (l_bucket_rec.range8_high is null or
429 c.report_date-f.incident_date < l_bucket_rec.range8_high) then
430 1 else 0
431 end) backlog_age_b8
432 , sum(case
433 when c.report_date-f.incident_date >= l_bucket_rec.range9_low and
434 (l_bucket_rec.range9_high is null or
435 c.report_date-f.incident_date < l_bucket_rec.range9_high) then
436 1 else 0
437 end) backlog_age_b9
438 , sum(case
439 when c.report_date-f.incident_date >= l_bucket_rec.range10_low and
440 (l_bucket_rec.range10_high is null or
441 c.report_date-f.incident_date < l_bucket_rec.range10_high) then
442 1 else 0
443 end) backlog_age_b10
444 , sum(decode(f.escalated_date,null,null,1)) escalated_count
445 , sum(decode(f.escalated_date,null,null, c.report_date-f.incident_date))
446 total_escalated_age
447 , sum(decode(f.escalated_date,null,null
448 ,case
449 when (l_bucket_rec.range1_low is null or
450 c.report_date-f.incident_date >= l_bucket_rec.range1_low) and
451 (l_bucket_rec.range1_high is null or
452 c.report_date-f.incident_date < l_bucket_rec.range1_high) then
453 1 else 0
454 end)) escalated_age_b1
455 , sum(decode(f.escalated_date,null,null
456 ,case
457 when c.report_date-f.incident_date >= l_bucket_rec.range2_low and
458 (l_bucket_rec.range2_high is null or
459 c.report_date-f.incident_date < l_bucket_rec.range2_high) then
460 1 else 0
461 end)) escalated_age_b2
462 , sum(decode(f.escalated_date,null,null
463 ,case
464 when c.report_date-f.incident_date >= l_bucket_rec.range3_low and
465 (l_bucket_rec.range3_high is null or
466 c.report_date-f.incident_date < l_bucket_rec.range3_high) then
467 1 else 0
468 end)) escalated_age_b3
469 , sum(decode(f.escalated_date,null,null
470 ,case
471 when c.report_date-f.incident_date >= l_bucket_rec.range4_low and
472 (l_bucket_rec.range4_high is null or
473 c.report_date-f.incident_date < l_bucket_rec.range4_high) then
474 1 else 0
475 end)) escalated_age_b4
476 , sum(decode(f.escalated_date,null,null
477 ,case
478 when c.report_date-f.incident_date >= l_bucket_rec.range5_low and
479 (l_bucket_rec.range5_high is null or
480 c.report_date-f.incident_date < l_bucket_rec.range5_high) then
481 1 else 0
482 end)) escalated_age_b5
483 , sum(decode(f.escalated_date,null,null
484 ,case
485 when c.report_date-f.incident_date >= l_bucket_rec.range6_low and
486 (l_bucket_rec.range6_high is null or
487 c.report_date-f.incident_date < l_bucket_rec.range6_high) then
488 1 else 0
489 end)) escalated_age_b6
490 , sum(decode(f.escalated_date,null,null
491 ,case
492 when c.report_date-f.incident_date >= l_bucket_rec.range7_low and
493 (l_bucket_rec.range7_high is null or
494 c.report_date-f.incident_date < l_bucket_rec.range7_high) then
495 1 else 0
496 end)) escalated_age_b7
497 , sum(decode(f.escalated_date,null,null
498 ,case
499 when c.report_date-f.incident_date >= l_bucket_rec.range8_low and
500 (l_bucket_rec.range8_high is null or
501 c.report_date-f.incident_date < l_bucket_rec.range8_high) then
502 1 else 0
503 end)) escalated_age_b8
504 , sum(decode(f.escalated_date,null,null
505 ,case
506 when c.report_date-f.incident_date >= l_bucket_rec.range9_low and
507 (l_bucket_rec.range9_high is null or
508 c.report_date-f.incident_date < l_bucket_rec.range9_high) then
509 1 else 0
510 end)) escalated_age_b9
511 , sum(decode(f.escalated_date,null,null
512 ,case
513 when c.report_date-f.incident_date >= l_bucket_rec.range10_low and
514 (l_bucket_rec.range10_high is null or
515 c.report_date-f.incident_date < l_bucket_rec.range10_high) then
516 1 else 0
517 end)) escalated_age_b10
518 , sum(decode(f.unowned_date,null,null,1)) unowned_count
519 , sum(decode(f.unowned_date,null,null, c.report_date-f.incident_date))
520 total_unowned_age
521 , sum(decode(f.unowned_date,null,null
522 ,case
523 when (l_bucket_rec.range1_low is null or
524 c.report_date-f.incident_date >= l_bucket_rec.range1_low) and
525 (l_bucket_rec.range1_high is null or
526 c.report_date-f.incident_date < l_bucket_rec.range1_high) then
527 1 else 0
528 end)) unowned_age_b1
529 , sum(decode(f.unowned_date,null,null
530 ,case
531 when c.report_date-f.incident_date >= l_bucket_rec.range2_low and
532 (l_bucket_rec.range2_high is null or
533 c.report_date-f.incident_date < l_bucket_rec.range2_high) then
534 1 else 0
535 end)) unowned_age_b2
536 , sum(decode(f.unowned_date,null,null
537 ,case
538 when c.report_date-f.incident_date >= l_bucket_rec.range3_low and
539 (l_bucket_rec.range3_high is null or
540 c.report_date-f.incident_date < l_bucket_rec.range3_high) then
541 1 else 0
542 end)) unowned_age_b3
543 , sum(decode(f.unowned_date,null,null
544 ,case
545 when c.report_date-f.incident_date >= l_bucket_rec.range4_low and
546 (l_bucket_rec.range4_high is null or
547 c.report_date-f.incident_date < l_bucket_rec.range4_high) then
548 1 else 0
549 end)) unowned_age_b4
550 , sum(decode(f.unowned_date,null,null
551 ,case
552 when c.report_date-f.incident_date >= l_bucket_rec.range5_low and
553 (l_bucket_rec.range5_high is null or
554 c.report_date-f.incident_date < l_bucket_rec.range5_high) then
555 1 else 0
556 end)) unowned_age_b5
557 , sum(decode(f.unowned_date,null,null
558 ,case
559 when c.report_date-f.incident_date >= l_bucket_rec.range6_low and
560 (l_bucket_rec.range6_high is null or
561 c.report_date-f.incident_date < l_bucket_rec.range6_high) then
562 1 else 0
563 end)) unowned_age_b6
564 , sum(decode(f.unowned_date,null,null
565 ,case
566 when c.report_date-f.incident_date >= l_bucket_rec.range7_low and
567 (l_bucket_rec.range7_high is null or
568 c.report_date-f.incident_date < l_bucket_rec.range7_high) then
569 1 else 0
570 end)) unowned_age_b7
571 , sum(decode(f.unowned_date,null,null
572 ,case
573 when c.report_date-f.incident_date >= l_bucket_rec.range8_low and
574 (l_bucket_rec.range8_high is null or
575 c.report_date-f.incident_date < l_bucket_rec.range8_high) then
576 1 else 0
577 end)) unowned_age_b8
578 , sum(decode(f.unowned_date,null,null
579 ,case
580 when c.report_date-f.incident_date >= l_bucket_rec.range9_low and
581 (l_bucket_rec.range9_high is null or
582 c.report_date-f.incident_date < l_bucket_rec.range9_high) then
583 1 else 0
584 end)) unowned_age_b9
585 , sum(decode(f.unowned_date,null,null
586 ,case
587 when c.report_date-f.incident_date >= l_bucket_rec.range10_low and
588 (l_bucket_rec.range10_high is null or
589 c.report_date-f.incident_date < l_bucket_rec.range10_high) then
590 1 else 0
591 end)) unowned_age_b10
592 , sysdate
593 , g_user_id
594 , sysdate
595 , g_user_id
596 , g_login_id
597 , f.resolved_flag
598 , f.incident_urgency_id
599 , f.incident_owner_id
600 , f.escalated_flag
601 from
602 (select /*+ parallel(a) */ distinct(report_date) report_date from biv_dbi_backlog_age_dates a) c
603 , biv_dbi_backlog_sum_f f
604 , eni_oltp_item_star pc
605 where
606 c.report_date between greatest(f.backlog_date_from, f.incident_date)
607 and f.backlog_date_to+0.99999
608 and c.report_date > f.incident_date
609 and f.inventory_item_id = pc.inventory_item_id
610 and f.inv_organization_id = pc.organization_id
611 group by
612 c.report_date
613 , f.incident_type_id
614 , f.incident_severity_id
615 , f.customer_id
616 , f.owner_group_id
617 , f.incident_status_id
618 , pc.vbh_category_id
619 , nvl(pc.master_id,pc.id)
620 , resolved_flag
621 , incident_urgency_id
622 , incident_owner_id
623 , escalated_flag
624 ;
625
626 l_rowcount := sql%rowcount;
627
628 bis_collection_utilities.log(l_rowcount || ' rows inserted',2);
629 commit;
630
631 --
632 bis_collection_utilities.log('gathering stats on Backlog Aging table',2);
633 if biv_dbi_collection_util.gather_statistics
634 ( l_biv_schema
635 , 'BIV_BAC_AGE_SUM_F'
636 , l_error_message ) <> 0 then
637 raise l_exception;
638 end if;
639 --
640
641 bis_collection_utilities.log('inserting group set rows into Backlog Aging table',2);
642
643 insert /*+ APPEND parallel(f) */
644 into biv_bac_age_sum_f f
645 ( report_date
646 , grp_id
647 , incident_type_id
648 , incident_severity_id
649 , vbh_category_id
650 , product_id
651 , customer_id
652 , owner_group_id
653 , incident_status_id
654 , backlog_count
655 , total_backlog_age
656 , backlog_age_b1
657 , backlog_age_b2
658 , backlog_age_b3
659 , backlog_age_b4
660 , backlog_age_b5
661 , backlog_age_b6
662 , backlog_age_b7
663 , backlog_age_b8
664 , backlog_age_b9
665 , backlog_age_b10
666 , escalated_count
667 , total_escalated_age
668 , escalated_age_b1
669 , escalated_age_b2
670 , escalated_age_b3
671 , escalated_age_b4
672 , escalated_age_b5
673 , escalated_age_b6
674 , escalated_age_b7
675 , escalated_age_b8
676 , escalated_age_b9
677 , escalated_age_b10
678 , unowned_count
679 , total_unowned_age
680 , unowned_age_b1
681 , unowned_age_b2
682 , unowned_age_b3
683 , unowned_age_b4
684 , unowned_age_b5
685 , unowned_age_b6
686 , unowned_age_b7
687 , unowned_age_b8
688 , unowned_age_b9
689 , unowned_age_b10
690 , creation_date
691 , created_by
692 , last_update_date
693 , last_updated_by
694 , last_update_login
695 , resolved_flag
696 , incident_urgency_id
697 , incident_owner_id
698 , escalated_flag
699 )
700 select /*+ parallel(f) */
701 report_date
702 , decode( grouping_id( product_id
703 , customer_id
704 , owner_group_id
705 , incident_status_id ) , 14, 4 -- Status
706 , 13, 3 -- Assignment Group
707 , 11, 2 -- Customer
708 , 7, 1 -- Prod/Cat
709 , 0 ) grp_id
710 , incident_type_id
711 , incident_severity_id
712 , vbh_category_id
713 , product_id
714 , customer_id
715 , owner_group_id
716 , incident_status_id
717 , sum(backlog_count)
718 , sum(total_backlog_age)
719 , sum(backlog_age_b1)
720 , sum(backlog_age_b2)
721 , sum(backlog_age_b3)
722 , sum(backlog_age_b4)
723 , sum(backlog_age_b5)
724 , sum(backlog_age_b6)
725 , sum(backlog_age_b7)
726 , sum(backlog_age_b8)
727 , sum(backlog_age_b9)
728 , sum(backlog_age_b10)
729 , sum(escalated_count)
730 , sum(total_escalated_age)
731 , sum(escalated_age_b1)
732 , sum(escalated_age_b2)
733 , sum(escalated_age_b3)
734 , sum(escalated_age_b4)
735 , sum(escalated_age_b5)
736 , sum(escalated_age_b6)
737 , sum(escalated_age_b7)
738 , sum(escalated_age_b8)
739 , sum(escalated_age_b9)
740 , sum(escalated_age_b10)
741 , sum(unowned_count)
742 , sum(total_unowned_age)
743 , sum(unowned_age_b1)
744 , sum(unowned_age_b2)
745 , sum(unowned_age_b3)
746 , sum(unowned_age_b4)
747 , sum(unowned_age_b5)
748 , sum(unowned_age_b6)
749 , sum(unowned_age_b7)
750 , sum(unowned_age_b8)
751 , sum(unowned_age_b9)
752 , sum(unowned_age_b10)
753 , sysdate
754 , g_user_id
755 , sysdate
756 , g_user_id
757 , g_login_id
758 , resolved_flag
759 , incident_urgency_id
760 , incident_owner_id
761 , escalated_flag
762 from
763 biv_bac_age_sum_f f
764 group by
765 report_date
766 , incident_type_id
767 , incident_severity_id
768 , vbh_category_id
769 , resolved_flag
770 , incident_urgency_id
771 , incident_owner_id
772 , escalated_flag
773 , grouping sets ( (product_id)
774 , (customer_id)
775 , (owner_group_id)
776 , (incident_status_id)
777 );
778 --
779 l_rowcount := sql%rowcount;
780 --
781 bis_collection_utilities.log(l_rowcount || ' rows inserted for grouping sets',2);
782 commit;
783 --
784
785 bis_collection_utilities.log('Backlog Age Load Complete');
786
787 commit;
788
789 bis_collection_utilities.wrapup( p_status => true
790 , p_period_from => l_collect_to_date
791 , p_period_to => l_collect_to_date
792 , p_count => l_rowcount
793 );
794
795 exception
796 when g_bis_setup_exception then
797 rollback;
798 errbuf := 'Error in BIS_COLLECTION_UTILITIES.Setup';
799 retcode := '2';
800
801 when others then
802 rollback;
803 if l_error_message is null then
804 l_error_message := substr(sqlerrm,1,4000);
805 end if;
806 bis_collection_utilities.wrapup( p_status => false
807 , p_message => l_error_message
808 , p_period_from => l_collect_to_date
809 , p_period_to => l_collect_to_date
810 );
811 errbuf := l_error_message;
812 retcode := '2';
813
814 end load_backlog_aging;
815
816 end biv_dbi_collection_age;