[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_ETL_PKG
Source
1 package body isc_fs_task_etl_pkg
2 /* $Header: iscfstasketlb.pls 120.6 2005/12/01 19:02:44 kreardon noship $ */
3 as
4
5 g_pkg_name constant varchar2(30) := 'isc_fs_task_etl_pkg';
6 g_user_id number;
7 g_login_id number;
8 g_program_id number;
9 g_program_login_id number;
10 g_program_application_id number;
11 g_request_id number;
12 g_success constant varchar2(10) := '0';
13 g_error constant varchar2(10) := '-1';
14 g_warning constant varchar2(10) := '1';
15 g_bis_setup_exception exception;
16 g_global_start_date date;
17 g_ttr_ftf_rule varchar2(30);
18 g_time_uom_class constant varchar2(10) := fnd_profile.value('JTF_TIME_UOM_CLASS');
19 g_uom_hours constant varchar2(10) := fnd_profile.value('CSF_UOM_HOURS');
20 g_time_base_to_hours number;
21
22
23 procedure bis_collection_utilities_log
24 ( m varchar2, indent number default null )
25 as
26 begin
27 --if indent is not null then
28 -- for i in 1..indent loop
29 -- dbms_output.put('__');
30 -- end loop;
31 --end if;
32 --dbms_output.put_line(substr(m,1,254));
33
34 bis_collection_utilities.log( substr(m,1,2000-(nvl(indent,0)*3)), nvl(indent,0) );
35
36 end bis_collection_utilities_log;
37
38 procedure local_init
39 as
40
41 cursor c_time_base is
42 select 1 / decode( conversion_rate
43 , 0 , 1 -- prevent "divide by zero" error
44 , conversion_rate )
45 from mtl_uom_conversions
46 where uom_class = g_time_uom_class
47 and uom_code = g_uom_hours
48 and inventory_item_id = 0;
49
50 begin
51
52 g_user_id := fnd_global.user_id;
53 g_login_id := fnd_global.login_id;
54 g_global_start_date := bis_common_parameters.get_global_start_date;
55 g_program_id := fnd_global.conc_program_id;
56 g_program_login_id := fnd_global.conc_login_id;
57 g_program_application_id := fnd_global.prog_appl_id;
58 g_request_id := fnd_global.conc_request_id;
59
60 g_ttr_ftf_rule := nvl(fnd_profile.value('ISC_FS_TTR_FTF_DISTRICT_RULE'),'0');
61
62 -- the base UOM_CODE for the UOM_CLASS may not be the same as the
63 -- UOM_CODE for "hours". We need to convert everything to "hours"
64 -- so we
65 open c_time_base;
66 fetch c_time_base into g_time_base_to_hours;
67 close c_time_base;
68
69 end local_init;
70
71 procedure logger
72 ( p_proc_name varchar2
73 , p_stmt_id number
74 , p_message varchar2
75 )
76 as
77 begin
78 bis_collection_utilities_log( g_pkg_name || '.' || p_proc_name ||
79 ' #' || p_stmt_id || ' ' ||
80 p_message
81 , 3 );
82 end logger;
83
84 function get_schema_name
85 ( x_schema_name out nocopy varchar2
86 , x_error_message out nocopy varchar2 )
87 return number as
88
89 l_isc_schema varchar2(30);
90 l_status varchar2(30);
91 l_industry varchar2(30);
92
93 begin
94
95 if fnd_installation.get_app_info('ISC', l_status, l_industry, l_isc_schema) then
96 x_schema_name := l_isc_schema;
97 else
98 x_error_message := 'FND_INSTALLATION.GET_APP_INFO returned false';
99 return -1;
100 end if;
101
102 return 0;
103
104 exception
105 when others then
106 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
107 return -1;
108
109 end get_schema_name;
110
111 function truncate_table
112 ( p_isc_schema in varchar2
113 , p_table_name in varchar2
114 , x_error_message out nocopy varchar2
115 )
116 return number as
117
118 begin
119
120 execute immediate 'truncate table ' || p_isc_schema || '.' || p_table_name;
121
122 return 0;
123
124 exception
125 when others then
126 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
127 return -1;
128
129 end truncate_table;
130
131 function gather_statistics
132 ( p_isc_schema in varchar2
133 , p_table_name in varchar2
134 , x_error_message out nocopy varchar2 )
135 return number as
136
137 begin
138
139 fnd_stats.gather_table_stats( ownname => p_isc_schema
140 , tabname => p_table_name
141 );
142
143 return 0;
144
145 exception
146 when others then
147 x_error_message := 'Error in function gather_statistics : ' || sqlerrm;
148 return -1;
149
150 end gather_statistics;
151
152 function get_last_refresh_date
153 ( x_refresh_date out nocopy date
154 , x_error_message out nocopy varchar2 )
155 return number as
156
157 l_refresh_date date;
158
159 begin
160
161 l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(g_object_name));
162 if l_refresh_date = g_global_start_date then
163 x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
164 return -1;
165 end if;
166
167 x_refresh_date := l_refresh_date;
168 return 0;
169
170 exception
171 when others then
172 x_error_message := 'Error in function get_last_refresh_date : ' || sqlerrm;
173 return -1;
174
175 end get_last_refresh_date;
176
177 function get_tr_ftf_rule_meaning
178 ( p_lookup_code in varchar2
179 )
180 return varchar2
181 as
182
183 cursor c_meaning is
184 select meaning
185 from fnd_lookup_values_vl
186 where lookup_type = 'ISC_FS_TTR_FTF_DISTRICT_RULE'
187 and lookup_code = p_lookup_code;
188
189 l_meaning varchar2(80);
190
191 begin
192
193 open c_meaning;
194 fetch c_meaning into l_meaning;
195 close c_meaning;
196
197 return nvl(l_meaning,'NULL');
198
199 end get_tr_ftf_rule_meaning;
200
201 function check_district_rule
202 ( p_mode in varchar2
203 , x_error_message out nocopy varchar2
204 )
205 return number
206 as
207
208 l_attributes DBMS_SQL.VARCHAR2_TABLE;
209 l_count number;
210 l_last_load varchar2(150);
211
212 begin
213
214 bis_collection_utilities.get_last_user_attributes
215 ( g_object_name
216 , l_attributes
217 , l_count
218 );
219
220 if l_count > 0 then
221 l_last_load := l_attributes(1);
222 else
223 l_last_load := 'X';
224 end if;
225
226 if p_mode = 'initial_load' then
227 bis_collection_utilities_log('MTTR/FTFR Rule: ' || get_tr_ftf_rule_meaning(g_ttr_ftf_rule), 1);
228 else
229 if g_ttr_ftf_rule = l_last_load then
230 bis_collection_utilities_log('MTTR/FTFR Rule: ' || get_tr_ftf_rule_meaning(g_ttr_ftf_rule), 1);
231 else
232 bis_collection_utilities_log('Previous MTTR/FTFR Rule: ' || get_tr_ftf_rule_meaning(l_last_load), 1);
233 bis_collection_utilities_log('Current MTTR/FTFR Rule: ' || get_tr_ftf_rule_meaning(g_ttr_ftf_rule), 1);
234 x_error_message := 'MTTR/FTFR Rule mismatch';
235 return -1;
236 end if;
237 end if;
238
239 return 0;
240
241 end check_district_rule;
242
243 -- -------------------------------------------------------------------
244 -- PUBLIC PROCEDURES
245 -- -------------------------------------------------------------------
246 procedure initial_load
247 ( errbuf out nocopy varchar2
248 , retcode out nocopy number
249 )
250 as
251
252 l_proc_name constant varchar2(30) := 'initial_load';
253 l_stmt_id number;
254 l_exception exception;
255 l_error_message varchar2(4000);
256 l_isc_schema varchar2(100);
257
258 l_rowcount number;
259
260 l_collect_from_date date;
261 l_collect_to_date date;
262
263 begin
264
265 local_init;
266
267 bis_collection_utilities_log( 'Begin Initial Load' );
268
269 l_stmt_id := 0;
270 if not bis_collection_utilities.setup( g_object_name ) then
271 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
272 logger( l_proc_name, l_stmt_id, l_error_message );
273 raise g_bis_setup_exception;
274 end if;
275
276 -- determine the date we last collected to
277 l_stmt_id := 10;
278 if g_global_start_date is null then
279 l_error_message := 'Unable to get DBI global start date.'; -- translatable message?
280 logger( l_proc_name, l_stmt_id, l_error_message );
281 raise l_exception;
282 end if;
283
284 l_collect_from_date := g_global_start_date;
285 l_collect_to_date := sysdate;
286
287 bis_collection_utilities_log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
288 bis_collection_utilities_log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
289
290 -- check MTTR/FTFR district rule
291 l_stmt_id := 20;
292 if check_district_rule
293 ( l_proc_name
294 , l_error_message ) <> 0 then -- should never fail for intial load
295 logger( l_proc_name, l_stmt_id, l_error_message );
296 raise l_exception;
297 end if;
298
299 -- get the isc schema name
300 l_stmt_id := 30;
301 if get_schema_name
302 ( l_isc_schema
303 , l_error_message ) <> 0 then
304 logger( l_proc_name, l_stmt_id, l_error_message );
305 raise l_exception;
306 end if;
307
308 -- truncate the events table
309 l_stmt_id := 40;
310 if truncate_table
311 ( l_isc_schema
312 , 'ISC_FS_EVENTS'
313 , l_error_message ) <> 0 then
314 logger( l_proc_name, l_stmt_id, l_error_message );
315 raise l_exception;
316 end if;
317
318 bis_collection_utilities_log( 'Events table truncated', 1 );
319
320 -- truncate the party merge events table
321 l_stmt_id := 50;
322 if truncate_table
323 ( l_isc_schema
324 , 'ISC_FS_PARTY_MERGE_EVENTS'
325 , l_error_message ) <> 0 then
326 logger( l_proc_name, l_stmt_id, l_error_message );
327 raise l_exception;
328 end if;
329
330 bis_collection_utilities_log( 'Party Merge Events table truncated', 1 );
331
332 -- enable event logging
333 l_stmt_id := 60;
334 if isc_fs_event_log_etl_pkg.enable_events
335 ( l_error_message ) <> 0 then
336 logger( l_proc_name, l_stmt_id, l_error_message );
337 raise l_exception;
338 end if;
339
340 bis_collection_utilities_log( 'Event logging enabled', 1 );
341
342 -- truncate the isc_fs_tasks_f fact table
343 l_stmt_id := 70;
344 if truncate_table
345 ( l_isc_schema
346 , 'ISC_FS_TASKS_F'
347 , l_error_message ) <> 0 then
348 logger( l_proc_name, l_stmt_id, l_error_message );
349 raise l_exception;
350 end if;
351
352 bis_collection_utilities_log( 'Task base summary table truncated', 1 );
353
354 -- truncate the isc_fs_task_assignmnts_f fact table
355 l_stmt_id := 80;
356 if truncate_table
357 ( l_isc_schema
358 , 'ISC_FS_TASK_ASSIGNMNTS_F'
359 , l_error_message ) <> 0 then
360 logger( l_proc_name, l_stmt_id, l_error_message );
361 raise l_exception;
362 end if;
363
364 bis_collection_utilities_log( 'Task Assignments base summary table truncated', 1 );
365
366 -- R12 dep/arr
367 -- attempt to truncate obsolete isc_fs_dep_arr_tasks_f table,
368 -- ignore any errors as table may not exist.
369 l_stmt_id := 90;
370 if truncate_table
371 ( l_isc_schema
372 , 'ISC_FS_DEP_ARR_TASKS_F'
373 , l_error_message ) = 0 then
374 bis_collection_utilities_log( 'Obsolete Departure/Arrival Task base summary table truncated', 1 );
375 end if;
376
377 -- truncate the isc_fs_capacity_f fact table
378 l_stmt_id := 95;
379 if truncate_table
380 ( l_isc_schema
381 , 'ISC_FS_CAPACITY_F'
382 , l_error_message ) <> 0 then
383 logger( l_proc_name, l_stmt_id, l_error_message );
384 raise l_exception;
385 end if;
386
387 bis_collection_utilities_log( 'Capacity base summary table truncated', 1 );
388 -- R12 dep/arr
389
390 -- insert into base fact tables
391 l_stmt_id := 100;
392 insert /*+ append
393 parallel(isc_fs_tasks_f)
394 parallel(isc_fs_task_assignmnts_f)
395 parallel(isc_fs_capacity_f)
396 */
397 ALL
398 when source_object_type_code = 'SR' and task_rn = 1 then into
399 isc_fs_tasks_f
400 ( task_id
401 , task_number
402 , task_type_id
403 , task_type_rule
404 , break_fix_flag
405 , task_status_id
406 , owner_id
407 -- R12 resource type impact
408 , owner_type
409 , owner_district_id
410 , customer_id
411 , address_id
412 -- R12 impact
413 , location_id
414 , planned_start_date
415 , planned_end_date
416 , scheduled_start_date
417 , scheduled_end_date
418 , actual_start_date
419 , actual_end_date
420 , source_object_type_code
421 , source_object_id
422 , source_object_name
423 , planned_effort_hrs
424 , actual_effort_hrs
425 , cancelled_flag
426 , completed_flag
427 , closed_flag
428 , deleted_flag
429 , task_creation_date
430 --
431 , first_asgn_creation_date
432 --
433 , act_bac_assignee_id
434 -- R12 resource type impact
435 , act_bac_assignee_type
436 , act_bac_district_id
437 --
438 , ftf_assignee_id
439 -- R12 resource type impact
440 , ftf_assignee_type
441 , ftf_district_id
442 --
443 , ttr_assignee_id
444 -- R12 resource type impact
445 , ttr_assignee_type
446 , ttr_district_id
447 --
448 , ftf_ttr_district_rule
449 --
450 , created_by
451 , creation_date
452 , last_updated_by
453 , last_update_date
454 , last_update_login
455 , program_id
456 , program_login_id
457 , program_application_id
458 , request_id
459 --
460 , include_task_in_ttr_flag
461 , include_task_in_ftf_flag
462 , ftf_flag
463 --
464 , incident_date
465 , inventory_item_id
466 , inv_organization_id
467 --
468 -- R12 impact
469 , task_split_flag
470 , parent_task_id
471 --
472 )
473 values
474 ( task_id
475 , task_number
476 , task_type_id
477 , task_type_rule
478 , break_fix_flag
479 , task_status_id
480 , task_owner_id
481 -- R12 resource type impact
482 , task_owner_type
483 , task_district_id
484 , customer_id
485 , address_id
486 -- R12 impact
487 , location_id
488 , task_planned_start_date
489 , task_planned_end_date
490 , task_scheduled_start_date
491 , task_scheduled_end_date
492 , task_actual_start_date
493 , task_actual_end_date
494 , source_object_type_code
495 , source_object_id
496 , source_object_name
497 , task_planned_effort_hrs
498 , task_actual_effort_hrs
499 , task_cancelled_flag
500 , task_completed_flag
501 , task_closed_flag
502 , task_deleted_flag
503 , task_creation_date
504 --
505 -- R12 impact null value for first_asgn_creation_date for "child" task
506 , decode(task_split_flag,'D',to_date(null),first_asgn_creation_date)
507 --
508 -- R12 impact null value for act_bac_assignee_id for "child" task
509 , decode(task_split_flag,'D',to_number(null),nvl(act_bac_assignee_id,task_owner_id))
510 -- R12 resource type impact
511 , decode(task_split_flag,'D',null,nvl(act_bac_assignee_type,task_owner_type))
512 --
513 -- R12 impact null value for act_bac_district_id for "child" task
514 , decode(task_split_flag,'D',to_number(null),nvl(act_bac_district_id,task_district_id))
515 --
516 -- R12 impact null value for ftf_assignee_id for "child" task
517 , decode(task_split_flag,'D',to_number(null),nvl(ftf_assignee_id,task_owner_id))
518 -- R12 resource type impact
519 , decode(task_split_flag,'D',null,nvl(ftf_assignee_type,task_owner_type))
520 -- R12 impact null value for ftf_district_id for "child" task
521 , decode(task_split_flag,'D',to_number(null),nvl(ftf_district_id,task_district_id))
522 --
523 -- R12 impact null value for ttr_assignee_id for "child" task
524 , decode(task_split_flag,'D',to_number(null),nvl(ttr_assignee_id,task_owner_id))
525 -- R12 resource type impact
526 , decode(task_split_flag,'D',null,nvl(ttr_assignee_type,task_owner_type))
527 --
528 -- R12 impact null value for ttr_district_id for "child" task
529 , decode(task_split_flag,'D',to_number(null),nvl(ttr_district_id,task_district_id))
530 --
531 -- values for ftf owner/district are determined by rule in MV and detail report
532 , g_ttr_ftf_rule
533 --
534 , g_user_id
535 , l_collect_to_date -- don't use sysdate as need to synchronize dates
536 , g_user_id
537 , l_collect_to_date -- don't use sysdate as need to synchronize dates for activity and backlog
538 , g_login_id
539 , g_program_id
540 , g_program_login_id
541 , g_program_application_id
542 , g_request_id
543 --
544 , include_task_in_ttr_flag
545 , include_task_in_ftf_flag
546 , ftf_flag
547 --
548 , incident_date
549 , inventory_item_id
550 , inv_organization_id
551 --
552 -- R12 impact
553 , task_split_flag
554 , parent_task_id
555 --
556 )
557 when source_object_type_code = 'SR' and task_assignment_id is not null then into
558 isc_fs_task_assignmnts_f
559 ( task_id
560 , task_assignment_id
561 , deleted_flag
562 , cancelled_flag
563 , assignment_creation_date
564 , resource_id
565 -- R12 resource type impact
566 , resource_type
567 , district_id
568 , actual_effort_hrs
569 , sched_travel_distance_km
570 , sched_travel_duration_min
571 , actual_travel_distance_km
572 , actual_travel_duration_min
573 , actual_start_date
574 , actual_end_date
575 , report_date
576 , created_by
577 , creation_date
578 , last_updated_by
579 , last_update_date
580 , last_update_login
581 , program_id
582 , program_login_id
583 , program_application_id
584 , request_id
585 )
586 values
587 ( task_id
588 , task_assignment_id
589 , asgn_deleted_flag
590 , asgn_cancelled_flag
591 , asgn_creation_date
592 , asgn_resource_id
593 -- R12 resource type impact
594 , asgn_resource_type
595 , asgn_district_id
596 , asgn_actual_effort_hrs
597 , sched_travel_distance_km
598 , sched_travel_duration_min
599 , actual_travel_distance_km
600 , actual_travel_duration_min
601 , asgn_actual_start_date
602 , asgn_actual_end_date
603 , case
604 when asgn_deleted_flag = 'N' and
605 asgn_actual_end_date >= g_global_start_date then
606 trunc(asgn_actual_end_date)
607 else
608 null
609 end
610 , g_user_id
611 , l_collect_to_date -- don't use sysdate as need to synchronize dates
612 , g_user_id
613 , l_collect_to_date -- don't use sysdate as need to synchronize dates
614 , g_login_id
615 , g_program_id
616 , g_program_login_id
617 , g_program_application_id
618 , g_request_id
619 )
620 when source_object_type_code = 'TASK' and task_rn = 1 then into
621 -- R12 dep/arr
622 isc_fs_capacity_f
623 ( task_id
624 , owner_id
625 -- R12 resource type impact
626 , owner_type
627 , district_id
628 , blocked_trip_flag
629 , object_capacity_id
630 , capacity_date
631 , capacity_hours
632 , deleted_flag
633 , created_by
634 , creation_date
635 , last_updated_by
636 , last_update_date
637 , last_update_login
638 , program_id
639 , program_login_id
640 , program_application_id
641 , request_id
642 )
643 values
644 ( task_id
645 , task_owner_id
646 -- R12 resource type impact
647 , task_owner_type
648 , task_district_id
649 , blocked_trip_flag
650 , object_capacity_id
651 , capacity_date
652 , capacity_hours
653 , task_deleted_flag
654 , g_user_id
655 , sysdate
656 , g_user_id
657 , sysdate
658 , g_login_id
659 , g_program_id
660 , g_program_login_id
661 , g_program_application_id
662 , g_request_id
663 -- R12 dep/arr
664 )
665 --
666 -- this query selects all tasks and task assignments for tasks of
667 -- interest
668 -- R12 dep/arr
669 -- . Task Type departure (20) and source object "TASK" or
670 -- Task source object "SR"
671 -- . Task Created on/after GSD or
672 -- Task has assignments created on/after GSD or
673 -- Task status is not closed (source object "SR" only)
674 --
675 select /*+ leading(x) parallel(x)
676 use_nl(t_eff,t_peff,ta_eff,ta_std,ta_atd)
677 parallel(t_eff)
678 parallel(ta_eff)
679 parallel(ta_std)
680 parallel(ta_atd)
681 parallel(bf)
682 parallel(x)
683 */
684 x.task_id
685 --
686 -- simple columns for isc_fs_tasks_f
687 --
688 , x.task_rn
689 , x.task_number
690 , x.task_type_id
691 , x.task_type_rule
692 , x.break_fix_flag
693 , x.task_status_id
694 , x.task_owner_id
695 -- R12 resource type impact
696 , x.task_owner_type
697 , x.task_district_id
698 , x.customer_id
699 , x.address_id
700 -- R12 impact
701 , x.location_id
702 , x.task_planned_start_date
703 , x.task_planned_end_date
704 , x.task_scheduled_start_date
705 , x.task_scheduled_end_date
706 , x.task_actual_start_date
707 , x.task_actual_end_date
708 , x.source_object_type_code
709 , x.source_object_id
710 , x.source_object_name
711 , (x.task_planned_effort * t_peff.conversion_rate * g_time_base_to_hours) task_planned_effort_hrs
712 , (x.task_actual_effort * t_eff.conversion_rate * g_time_base_to_hours) task_actual_effort_hrs
713 , x.task_creation_date
714 , x.task_cancelled_flag
715 , x.task_completed_flag
716 , x.task_closed_flag
717 , x.task_deleted_flag
718 --
719 -- complex columns for isc_fs_tasks_f
720 --
721 -- returns the date of the first created (not cancelled) assignment for the task
722 -- R12 impact
723 , min( x.asgn_creation_date_unc )
724 keep( dense_rank first
725 order by x.asgn_creation_date_unc nulls last
726 )
727 over( partition by nvl(x.parent_task_id,x.task_id) ) first_asgn_creation_date
728 --
729 -- assignee/district are determined uniquely for activity/backlog, ftf and ttr
730 -- as they have different rules for establishing ownership and dealing with
731 -- cancelled assignments.
732 --
733 -- activity/backlog
734 -- based on last assignment creation date of non-cancelled assignments
735 -- R12 impact in the case of split "parent" tasks, it is from the last "scheduled"
736 -- "child" task assignments.
737 --
738 , max( x.act_bac_resource_id )
739 keep( dense_rank last
740 order by
741 x.task_scheduled_end_date
742 , x.asgn_creation_date_unc nulls first
743 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
744 , x.task_assignment_id
745 )
746 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_assignee_id
747 -- R12 resource type impact
748 , max( x.act_bac_resource_type )
749 keep( dense_rank last
750 order by
751 x.task_scheduled_end_date
752 , x.asgn_creation_date_unc nulls first
753 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
754 , x.task_assignment_id
755 )
756 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_assignee_type
757 -- R12 impact
758 , max( x.act_bac_district_id )
759 keep( dense_rank last
760 order by
761 x.task_scheduled_end_date
762 , x.asgn_creation_date_unc nulls first
763 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
764 , x.task_assignment_id
765 )
766 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_district_id
767 --
768 -- ftf
769 -- based on last assignment creation date, exclude cancelled assignments unless
770 -- they have actual_end_date not null
771 --
772 -- R12 impact
773 , max( x.ftf_ttr_resource_id )
774 keep( dense_rank last
775 order by
776 x.task_scheduled_end_date
777 , x.asgn_creation_date_unc nulls first
778 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
779 , x.task_assignment_id
780 )
781 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_assignee_id
782 -- R12 resource type impact
783 , max( x.ftf_ttr_resource_type )
784 keep( dense_rank last
785 order by
786 x.task_scheduled_end_date
787 , x.asgn_creation_date_unc nulls first
788 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
789 , x.task_assignment_id
790 )
791 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_assignee_type
792 -- R12 impact
793 , max( x.ftf_ttr_district_id )
794 keep( dense_rank last
795 order by
796 x.task_scheduled_end_date
797 , x.asgn_creation_date_unc nulls first
798 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
799 , x.task_assignment_id
800 )
801 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_district_id
802 --
803 -- ttr
804 -- based on the last worked (assignment actual end date), exclude cancelled assignments unless
805 -- they have actual_end_date not null
806 --
807 -- R12 impact
808 , max( x.ftf_ttr_resource_id )
809 keep( dense_rank last
810 order by
811 x.asgn_actual_end_date nulls first
812 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
813 , x.task_assignment_id
814 )
815 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_assignee_id
816 -- R12 resource type impact
817 , max( x.ftf_ttr_resource_type )
818 keep( dense_rank last
819 order by
820 x.asgn_actual_end_date nulls first
821 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
822 , x.task_assignment_id
823 )
824 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_assignee_type
825 -- R12 impact
826 , max( x.ftf_ttr_district_id )
827 keep( dense_rank last
828 order by
829 x.asgn_actual_end_date nulls first
830 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
831 , x.task_assignment_id
832 )
833 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_district_id
834 --
835 -- ttr
836 -- only rows (one per SR) with 'Y' are included in ttr reports
837 -- a) not deleted, type rule = DISPATCH, break/fix enabled
838 -- b) latest actual end date, if no actual end dates, latest scheduled end date
839 -- c) "normal" or "parent" tasks
840 , case
841 when x.task_ttr_ftf_flag <> 'Y' then 'N'
842 when rank() over( partition by
843 x.source_object_type_code
844 , x.source_object_id
845 order by
846 x.task_ttr_ftf_flag desc
847 , x.task_ttr_ftf_actual_end_date desc nulls last
848 , x.task_ttr_ftf_sched_end_date desc nulls last
849 , x.task_creation_date desc -- always use creation_date ahead of xxx_id for RAC
850 , x.task_id desc
851 ) <> 1 then 'N'
852 else 'Y'
853 end include_task_in_ttr_flag
854 --
855 -- ftf
856 -- multiple rows per SR with 'Y' are included in non ftf detail report
857 -- not deleted, type rule = DISPATCH, break/fix enabled
858 -- "normal" or "parent" tasks
859 , x.task_ttr_ftf_flag include_task_in_ftf_flag
860 --
861 -- ftf
862 -- only rows (one per SR) with 'Y' or 'N' are included in ftf reports
863 -- a) not deleted, type rule = DISPATCH, break/fix enabled
864 -- b) earliest actual start date, if no actual start dates, latest scheduled start date
865 -- c) trunc( earliest actual end date ) = trunc ( latest actual end date ) = ftf else non ftf
866 -- d) "normal" or "parent" tasks
867 , case
868 when x.task_ttr_ftf_flag <> 'Y' then '-'
869 when rank() over( partition by
870 x.source_object_type_code
871 , x.source_object_id
872 order by
873 x.task_ttr_ftf_flag desc
874 , x.task_ttr_ftf_actual_start_date
875 , x.task_ttr_ftf_sched_start_date
876 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
877 , x.task_id
878 ) <> 1 then '-'
879 when trunc( first_value( x.task_ttr_ftf_actual_start_date ) -- use start_date
880 over( partition by
881 x.source_object_type_code
882 , x.source_object_id
883 order by
884 x.task_ttr_ftf_flag desc
885 , x.task_ttr_ftf_actual_start_date nulls last -- use start_date
886 , x.task_ttr_ftf_sched_start_date nulls last -- use start_date
887 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
888 , x.task_id
889 rows between unbounded preceding and unbounded following
890 )
891 ) <>
892 trunc( last_value( x.task_ttr_ftf_actual_start_date ) -- use start_date
893 over( partition by
894 x.source_object_type_code
895 , x.source_object_id
896 order by
897 x.task_ttr_ftf_flag
898 , x.task_ttr_ftf_actual_start_date nulls first -- use start_date
899 , x.task_ttr_ftf_sched_start_date nulls first -- use start_date
900 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
901 , x.task_id
902 rows between unbounded preceding and unbounded following
903 )
904 ) then 'N'
905 else 'Y'
906 end ftf_flag
907 --
908 , x.incident_date
909 , x.inventory_item_id
910 , x.inv_organization_id
911 --
912 -- R12 impact
913 , x.task_split_flag
914 , x.parent_task_id
915 --
916 -- simple columns for isc_fs_task_assignmnts_f
917 --
918 , x.task_assignment_id
919 , 'N' asgn_deleted_flag
920 , x.asgn_cancelled_flag
921 , x.asgn_creation_date
922 , x.asgn_resource_id
923 -- R12 resource type impact
924 , x.asgn_resource_type
925 , x.asgn_district_id
926 , (x.asgn_actual_effort * ta_eff.conversion_rate * g_time_base_to_hours) asgn_actual_effort_hrs
927 , x.sched_travel_distance sched_travel_distance_km
928 , (x.sched_travel_duration * ta_std.conversion_rate * g_time_base_to_hours)* 60 sched_travel_duration_min
929 , x.actual_travel_distance actual_travel_distance_km
930 , (x.actual_travel_duration * ta_atd.conversion_rate * g_time_base_to_hours) * 60 actual_travel_duration_min
931 , x.asgn_actual_start_date
932 , x.asgn_actual_end_date
933 --
934 -- R12 dep/arr
935 -- simple columns for isc_fs_capacity_f
936 --
937 , x.object_capacity_id
938 , x.capacity_date
939 , x.capacity_hours
940 , x.blocked_trip_flag
941 --
942 -- R12 dep/arr
943 --
944 from
945 ( --
946 -- this query returns all of tasks of interest joined to
947 -- their assignments if any and resolves field service
948 -- district
949 --
950 select /*+ parallel(t)
951 use_nl(dgt,dga,tt,tas,oc,bf)
952 parallel(tt)
953 parallel(dga)
954 parallel(dgt)
955 parallel(tas)
956 parallel(oc)
957 parallel(bf)
958 */
959 t.task_id
960 , t.task_number
961 , t.task_type_id
962 , tt.rule task_type_rule
963 , nvl(bf.enabled,'N') break_fix_flag
964 , case
965 -- R12 impact
966 when t.task_split_flag = 'D' then 'N'
967 when nvl(tt.rule,'X') = 'DISPATCH' and
968 nvl(bf.enabled,'N') = 'Y' and
969 'Y' in (t.task_closed_flag,t.task_completed_flag) and
970 t.incident_date >= g_global_start_date then 'Y'
971 else 'N'
972 end task_ttr_ftf_flag
973 , case
974 -- R12 impact
975 when t.task_split_flag = 'D' then to_date(null)
976 when nvl(tt.rule,'X') = 'DISPATCH' and
977 nvl(bf.enabled,'N') = 'Y' and
978 'Y' in (t.task_closed_flag,t.task_completed_flag) and
979 t.incident_date >= g_global_start_date then t.task_actual_start_date
980 else null
981 end task_ttr_ftf_actual_start_date
982 , case
983 -- R12 impact
984 when t.task_split_flag = 'D' then to_date(null)
985 when nvl(tt.rule,'X') = 'DISPATCH' and
986 nvl(bf.enabled,'N') = 'Y' and
987 'Y' in (t.task_closed_flag,t.task_completed_flag) and
988 t.incident_date >= g_global_start_date then t.task_actual_end_date
989 else null
990 end task_ttr_ftf_actual_end_date
991 , case
992 -- R12 impact
993 when t.task_split_flag = 'D' then to_date(null)
994 when nvl(tt.rule,'X') = 'DISPATCH' and
995 nvl(bf.enabled,'N') = 'Y' and
996 'Y' in (t.task_closed_flag,t.task_completed_flag) and
997 t.incident_date >= g_global_start_date then t.task_scheduled_start_date
998 else null
999 end task_ttr_ftf_sched_start_date
1000 , case
1001 -- R12 impact
1002 when t.task_split_flag = 'D' then to_date(null)
1003 when nvl(tt.rule,'X') = 'DISPATCH' and
1004 nvl(bf.enabled,'N') = 'Y' and
1005 'Y' in (t.task_closed_flag,t.task_completed_flag) and
1006 t.incident_date >= g_global_start_date then t.task_scheduled_end_date
1007 else null
1008 end task_ttr_ftf_sched_end_date
1009 , t.task_status_id
1010 , t.task_owner_id
1011 -- R12 resource type impact
1012 , t.task_owner_type
1013 , decode( t.task_owner_type
1014 , 'GROUP', t.task_owner_id
1015 , nvl(dgt.group_id,-1)
1016 ) task_district_id
1017 , t.customer_id
1018 , t.address_id
1019 -- R12 impact
1020 , t.location_id
1021 , t.task_planned_start_date
1022 , t.task_planned_end_date
1023 , t.task_actual_start_date
1024 , t.task_actual_end_date
1025 , t.task_scheduled_start_date
1026 , t.task_scheduled_end_date
1027 , t.source_object_type_code
1028 , t.source_object_id
1029 , t.source_object_name
1030 , t.task_planned_effort
1031 , t.task_actual_effort
1032 , t.task_planned_effort_uom
1033 , t.task_actual_effort_uom
1034 , t.task_creation_date
1035 , t.task_cancelled_flag
1036 , t.task_completed_flag
1037 , t.task_closed_flag
1038 , row_number() over( partition by t.task_id
1039 order by t.asgn_creation_date
1040 ) task_rn
1041 , 'N' task_deleted_flag
1042 --
1043 , t.task_assignment_id
1044 , t.asgn_creation_date
1045 , t.asgn_resource_id
1046 -- R12 resource type impact
1047 , t.asgn_resource_type
1048 , decode( t.asgn_resource_type
1049 , 'GROUP', t.asgn_resource_id
1050 , nvl(dga.group_id,-1)
1051 ) asgn_district_id
1052 , t.asgn_actual_effort
1053 , t.asgn_actual_effort_uom
1054 , t.sched_travel_distance
1055 , t.sched_travel_duration
1056 , t.sched_travel_duration_uom
1057 , t.actual_travel_distance
1058 , t.actual_travel_duration
1059 , t.actual_travel_duration_uom
1060 , t.asgn_actual_start_date
1061 , t.asgn_actual_end_date
1062 , nvl(tas.cancelled_flag,'N') asgn_cancelled_flag
1063 --
1064 -- activity/backlog
1065 -- only return non-null resource type code for uncancelled
1066 -- only return non-null resource id for uncancelled
1067 , decode( nvl(tas.cancelled_flag,'N')
1068 , 'N', t.asgn_resource_id
1069 , null ) act_bac_resource_id
1070 -- R12 resource type impact
1071 , decode( nvl(tas.cancelled_flag,'N')
1072 , 'N', t.asgn_resource_type
1073 , null ) act_bac_resource_type
1074 -- only return non-null district id for uncancelled
1075 -- R12 resource type impact
1076 , decode( nvl(tas.cancelled_flag,'N')
1077 , 'N', decode( t.asgn_resource_type
1078 , 'GROUP', t.asgn_resource_id
1079 , 'TEAM', -1
1080 , 'RESOURCE', nvl(dga.group_id,-1)
1081 , dga.group_id
1082 )
1083 , null ) act_bac_district_id
1084 --
1085 -- ftf/ttr
1086 -- return resource type code of assignments with actual end date, or un-cancelled
1087 -- return resource id of assignments with actual end date, or un-cancelled
1088 , decode( t.asgn_actual_end_date
1089 , null, decode( nvl(tas.cancelled_flag,'N')
1090 , 'N', t.asgn_resource_id
1091 , null )
1092 , t.asgn_resource_id ) ftf_ttr_resource_id
1093 -- R12 resource type impact
1094 , decode( t.asgn_actual_end_date
1095 , null, decode( nvl(tas.cancelled_flag,'N')
1096 , 'N', t.asgn_resource_type
1097 , null )
1098 , t.asgn_resource_type ) ftf_ttr_resource_type
1099 -- return district id of assignments with actual end date, or un-cancelled
1100 -- R12 resource type impact
1101 , decode( t.asgn_actual_end_date
1102 , null, decode( nvl(tas.cancelled_flag,'N')
1103 , 'N', decode( t.asgn_resource_type
1104 , 'GROUP', t.asgn_resource_id
1105 , 'TEAM', -1
1106 , 'RESOURCE', nvl(dga.group_id,-1)
1107 , dga.group_id
1108 )
1109 , null )
1110 , decode( t.asgn_resource_type
1111 , 'GROUP', t.asgn_resource_id
1112 , 'TEAM', -1
1113 , 'RESOURCE', nvl(dga.group_id,-1)
1114 , dga.group_id
1115 )
1116 ) ftf_ttr_district_id
1117 -- return non-null creation date for un-cancelled
1118 , decode( nvl(tas.cancelled_flag,'N')
1119 , 'N', t.asgn_creation_date
1120 , null ) asgn_creation_date_unc
1121 --
1122 -- R12 dep/arr
1123 , oc.object_capacity_id
1124 , trunc(oc.end_date_time) capacity_date
1125 , ( oc.end_date_time - oc.start_date_time) * 24 capacity_hours
1126 , decode( oc.status
1127 , 1, 'N'
1128 , 0, 'Y'
1129 , null
1130 ) blocked_trip_flag
1131 -- R12 dep/arr
1132 --
1133 , t.incident_date
1134 , t.inventory_item_id
1135 , t.inv_organization_id
1136 --
1137 -- R12 impact
1138 , t.task_split_flag
1139 , t.parent_task_id
1140 --
1141 from
1142 ( -- need to nest this as an inline view to allow for
1143 -- chaining of outer joins
1144 select /*+ no_merge parallel(t)
1145 parallel(ta)
1146 parallel(ts)
1147 parallel(i)
1148 parallel(a)
1149 use_hash(t,ta,i) pq_distribute(A,hash,hash) pq_distribute(I,hash,hash)
1150 */
1151 t.source_object_type_code
1152 , t.source_object_id
1153 , t.source_object_name
1154 , t.task_id
1155 , t.task_number
1156 , t.task_status_id
1157 , t.task_type_id
1158 -- R12 resource type impact
1159 , t.owner_id task_owner_id
1160 , decode( t.owner_type_code
1161 , 'RS_GROUP', 'GROUP'
1162 , 'RS_TEAM', 'TEAM'
1163 , null, null
1164 , 'RESOURCE'
1165 ) task_owner_type
1166 , t.customer_id
1167 , t.address_id
1168 -- R12 impact
1169 , t.location_id
1170 , t.planned_start_date task_planned_start_date
1171 , t.planned_end_date task_planned_end_date
1172 , t.actual_start_date task_actual_start_date
1173 , t.actual_end_date task_actual_end_date
1174 , t.scheduled_start_date task_scheduled_start_date
1175 , t.scheduled_end_date task_scheduled_end_date
1176 , t.planned_effort task_planned_effort
1177 , t.planned_effort_uom task_planned_effort_uom
1178 , t.actual_effort task_actual_effort
1179 , t.actual_effort_uom task_actual_effort_uom
1180 , t.creation_date task_creation_date
1181 , nvl(ts.cancelled_flag,'N') task_cancelled_flag
1182 , nvl(ts.completed_flag,'N') task_completed_flag
1183 , nvl(ts.closed_flag,'N') task_closed_flag
1184 --
1185 -- R12 resource type impact
1186 , ta.resource_id asgn_resource_id
1187 , decode( ta.resource_type_code
1188 , 'RS_GROUP', 'GROUP'
1189 , 'RS_TEAM', 'TEAM'
1190 , null, null
1191 , 'RESOURCE'
1192 ) asgn_resource_type
1193 , ta.creation_date asgn_creation_date
1194 , ta.task_assignment_id
1195 , ta.assignment_status_id
1196 -- R12 dep/arr
1197 , ta.object_capacity_id
1198 , ta.actual_effort asgn_actual_effort
1199 , ta.actual_effort_uom asgn_actual_effort_uom
1200 , ta.sched_travel_distance
1201 , ta.sched_travel_duration
1202 , ta.sched_travel_duration_uom
1203 , ta.actual_travel_distance
1204 , ta.actual_travel_duration
1205 , ta.actual_travel_duration_uom
1206 , ta.actual_start_date asgn_actual_start_date
1207 , ta.actual_end_date asgn_actual_end_date
1208 --
1209 , i.incident_date
1210 , nvl2( i.inventory_item_id+i.inv_organization_id
1211 , i.inventory_item_id
1212 , -1 ) inventory_item_id
1213 , nvl2( i.inventory_item_id+i.inv_organization_id
1214 , i.inv_organization_id
1215 , -99 )inv_organization_id
1216 --
1217 -- R12 impact
1218 , t.task_split_flag
1219 , t.parent_task_id
1220 --
1221 from
1222 jtf_tasks_b t
1223 , jtf_task_assignments ta
1224 , jtf_task_statuses_b ts
1225 , cs_incidents_all_b i
1226 , ( select /*+ no_merge parallel(a) */
1227 distinct task_id
1228 from
1229 jtf_task_audits_b a
1230 where
1231 new_source_object_type_code = 'SR'
1232 and creation_date >= l_collect_from_date
1233 ) a
1234 where
1235 t.task_id = ta.task_id(+)
1236 and t.task_status_id = ts.task_status_id
1237 and ( (
1238 -- include all SR tasks with a creation_date on/after GSD
1239 t.source_object_type_code = 'SR' and
1240 -- don't restrict to just rule of 'DISPATCH' as
1241 -- could subsequently change type and we would
1242 -- miss out on the backlog/activity
1243 -- and tt.rule = 'DISPATCH'
1244 t.creation_date >= l_collect_from_date
1245 ) or
1246 -- include all SR tasks current in backlog
1247 ( t.source_object_type_code = 'SR' and
1248 nvl(ts.closed_flag,'N') = 'N'
1249 ) or
1250 -- include all SR tasks in backlog at GSD
1251 -- actually considering all SR tasks that have an audit
1252 -- row on/after GSD as this suggests they were created
1253 -- prior to GSD and were "probably" open at GSD (is does
1254 -- matter if we pick up some closed ones as they will
1255 -- never be measured)
1256 ( t.source_object_type_code = 'SR' and
1257 a.task_id is not null
1258 ) or
1259 -- R12 dep/arr
1260 -- include all dep tasks
1261 -- with a planned_start_date on/after GSD
1262 ( t.source_object_type_code = 'TASK' and
1263 t.task_type_id = 20 and
1264 -- R12 dep/arr
1265 t.planned_start_date >= l_collect_from_date
1266 )
1267 )
1268 and nvl(t.deleted_flag,'N') <> 'Y'
1269 and decode( t.source_object_type_code
1270 , 'SR', t.source_object_id
1271 , -777 ) = i.incident_id(+)
1272 and decode( t.source_object_type_code
1273 , 'SR', t.task_id
1274 , -777 ) = a.task_id(+)
1275 and ( t.source_object_type_code = 'TASK' or
1276 ( t.source_object_type_code = 'SR' and t.customer_id is not null )
1277 -- ignore SR tasks with NULL customer_id
1278 )
1279 ) t
1280 , jtf_task_types_b tt
1281 , jtf_rs_default_groups dgt
1282 , jtf_rs_default_groups dga
1283 , jtf_task_statuses_b tas
1284 -- R12 dep/arr
1285 , cac_sr_object_capacity oc
1286 -- R12 dep/arr
1287 , isc_fs_break_fix_tasks bf
1288 where
1289 t.task_type_id = tt.task_type_id
1290 -- R12 resource type impact
1291 and decode( t.task_owner_type
1292 , 'RESOURCE', nvl(t.task_owner_id,-2)
1293 , -2
1294 ) = dgt.resource_id(+)
1295 and decode( t.source_object_type_code
1296 , 'SR', trunc(t.task_creation_date)
1297 , trunc(t.task_planned_start_date) ) >= dgt.start_date(+)
1298 and decode( t.source_object_type_code
1299 , 'SR', trunc(t.task_creation_date)
1300 , trunc(t.task_planned_start_date) ) <= dgt.end_date(+)
1301 and 'FLD_SRV_DISTRICT' = dgt.usage(+)
1302 -- R12 resource type impact
1303 and decode( t.asgn_resource_type
1304 , 'RESOURCE', nvl(t.asgn_resource_id,-2)
1305 , -2
1306 ) = dga.resource_id(+)
1307 and decode( t.source_object_type_code
1308 , 'SR', trunc(nvl(t.asgn_creation_date,sysdate))
1309 , trunc(t.task_planned_start_date) ) >= dga.start_date(+)
1310 and decode( t.source_object_type_code
1311 , 'SR', trunc(nvl(t.asgn_creation_date,sysdate))
1312 , trunc(t.task_planned_start_date) ) <= dga.end_date(+)
1313 and 'FLD_SRV_DISTRICT' = dga.usage(+)
1314 and nvl(t.assignment_status_id,-123) = tas.task_status_id(+)
1315 -- R12 dep/arr
1316 and nvl(t.object_capacity_id,-123) = oc.object_capacity_id(+)
1317 -- R12 dep/arr
1318 and t.task_type_id = bf.task_type_id(+)
1319 ) x
1320 , mtl_uom_conversions t_eff
1321 , mtl_uom_conversions t_peff
1322 , mtl_uom_conversions ta_eff
1323 , mtl_uom_conversions ta_std
1324 , mtl_uom_conversions ta_atd
1325 where
1326 --
1327 t_peff.inventory_item_id = 0
1328 and t_peff.uom_class = g_time_uom_class
1329 and t_peff.uom_code = nvl(x.task_planned_effort_uom,g_uom_hours)
1330 --
1331 and t_eff.inventory_item_id = 0
1332 and t_eff.uom_class = g_time_uom_class
1333 and t_eff.uom_code = nvl(x.task_actual_effort_uom,g_uom_hours)
1334 --
1335 and ta_eff.inventory_item_id = 0
1336 and ta_eff.uom_class = g_time_uom_class
1337 and ta_eff.uom_code = nvl(x.asgn_actual_effort_uom,g_uom_hours)
1338 --
1339 and ta_std.inventory_item_id = 0
1340 and ta_std.uom_class = g_time_uom_class
1341 and ta_std.uom_code = nvl(x.sched_travel_duration_uom,g_uom_hours)
1342 --
1343 and ta_atd.inventory_item_id = 0
1344 and ta_atd.uom_class = g_time_uom_class
1345 and ta_atd.uom_code = nvl(x.actual_travel_duration_uom,g_uom_hours)
1346 --
1347 ;
1348
1349 l_rowcount := sql%rowcount;
1350
1351 commit;
1352
1353 bis_collection_utilities_log( l_rowcount || ' rows inserted into base summaries', 1 );
1354
1355 l_stmt_id := 110;
1356 bis_collection_utilities.wrapup( p_status => true
1357 , p_period_from => l_collect_from_date
1358 , p_period_to => l_collect_to_date
1359 , p_count => l_rowcount
1360 , p_attribute1 => g_ttr_ftf_rule
1361 );
1362
1363 bis_collection_utilities_log('End Initial Load');
1364
1365 errbuf := null;
1366 retcode := g_success;
1367
1368 exception
1369 when g_bis_setup_exception then
1370 rollback;
1371 errbuf := l_error_message;
1372 retcode := g_error;
1373 bis_collection_utilities_log('End Initial Load with Error');
1374
1375 when l_exception then
1376 rollback;
1377 if l_error_message is null then
1378 l_error_message := substr(sqlerrm,1,4000);
1379 end if;
1380 bis_collection_utilities.wrapup( p_status => false
1381 , p_message => l_error_message
1382 , p_period_from => l_collect_from_date
1383 , p_period_to => l_collect_to_date
1384 );
1385 errbuf := l_error_message;
1386 retcode := g_error;
1387 bis_collection_utilities_log('End Initial Load with Error');
1388
1389 when others then
1390 rollback;
1391 if l_error_message is null then
1392 l_error_message := substr(sqlerrm,1,4000);
1393 end if;
1394 logger( l_proc_name, l_stmt_id, l_error_message );
1395 bis_collection_utilities.wrapup( p_status => false
1396 , p_message => l_error_message
1397 , p_period_from => l_collect_from_date
1398 , p_period_to => l_collect_to_date
1399 );
1400 errbuf := l_error_message;
1401 retcode := g_error;
1402 bis_collection_utilities_log('End Initial Load with Error');
1403
1404 end initial_load;
1405
1406 procedure incremental_load
1407 ( errbuf out nocopy varchar2
1408 , retcode out nocopy number
1409 )
1410 as
1411
1412 l_proc_name constant varchar2(30) := 'incremental_load';
1413 l_stmt_id number;
1414 l_exception exception;
1415 l_error_message varchar2(4000);
1416 l_isc_schema varchar2(100);
1417
1418 l_rowcount number;
1419 l_temp_rowcount number;
1420
1421 l_collect_from_date date;
1422 l_collect_to_date date;
1423
1424 l_resource_busy exception;
1425 pragma exception_init(l_resource_busy, -54);
1426
1427 begin
1428
1429 local_init;
1430
1431 bis_collection_utilities_log( 'Begin Incremental Load' );
1432
1433 l_stmt_id := 0;
1434 if not bis_collection_utilities.setup( g_object_name ) then
1435 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
1436 logger( l_proc_name, l_stmt_id, l_error_message );
1437 raise g_bis_setup_exception;
1438 end if;
1439
1440 -- determine the date we last collected to
1441 l_stmt_id := 10;
1442 if get_last_refresh_date(l_collect_to_date, l_error_message) <> 0 then
1443 logger( l_proc_name, l_stmt_id, l_error_message );
1444 raise l_exception;
1445 end if;
1446 l_collect_from_date := l_collect_to_date + 1/86400;
1447 l_collect_to_date := sysdate;
1448
1449 bis_collection_utilities_log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
1450 bis_collection_utilities_log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
1451
1452 -- check MTTR/FTFR district rule
1453 l_stmt_id := 20;
1454 if check_district_rule
1455 ( l_proc_name
1456 , l_error_message ) <> 0 then
1457 logger( l_proc_name, l_stmt_id, l_error_message );
1458 raise l_exception;
1459 end if;
1460
1461 -- get the isc schema name
1462 l_stmt_id := 30;
1463 if get_schema_name
1464 ( l_isc_schema
1465 , l_error_message ) <> 0 then
1466 logger( l_proc_name, l_stmt_id, l_error_message );
1467 raise l_exception;
1468 end if;
1469
1470 -- truncate the staging table isc_fs_events_stg
1471 l_stmt_id := 40;
1472 if truncate_table
1473 ( l_isc_schema
1474 , 'ISC_FS_EVENTS_STG'
1475 , l_error_message ) <> 0 then
1476 logger( l_proc_name, l_stmt_id, l_error_message );
1477 raise l_exception;
1478 end if;
1479
1480 bis_collection_utilities_log( 'Staging table truncated', 1 );
1481
1482 -- insert into staging table from events table
1483 l_stmt_id := 40;
1484 insert into isc_fs_events_stg
1485 ( source
1486 , event_rowid
1487 , task_id
1488 , source_object_type_code
1489 , source_object_id
1490 , task_assignment_id
1491 )
1492 select
1493 1
1494 , rowid
1495 , task_id
1496 , source_object_type_code
1497 , source_object_id
1498 , task_assignment_id
1499 from
1500 isc_fs_events;
1501
1502 l_rowcount := sql%rowcount;
1503 commit;
1504
1505 bis_collection_utilities_log( l_rowcount || ' rows inserted into staging table from events', 1 );
1506
1507 -- insert into staging table from party merge events table
1508 l_stmt_id := 50;
1509 insert into isc_fs_events_stg
1510 ( source
1511 , event_rowid
1512 , task_id
1513 , source_object_type_code
1514 , source_object_id
1515 )
1516 select
1517 2
1518 , rowid
1519 , task_id
1520 , source_object_type_code
1521 , source_object_id
1522 from
1523 isc_fs_party_merge_events;
1524
1525 l_rowcount := sql%rowcount;
1526 commit;
1527
1528 bis_collection_utilities_log( l_rowcount || ' rows inserted into staging table from party mearge events', 1 );
1529
1530 -- gather stats for staging table
1531 l_stmt_id := 60;
1532 if gather_statistics
1533 ( l_isc_schema
1534 , 'ISC_FS_EVENTS_STG'
1535 , l_error_message ) <> 0 then
1536 logger( l_proc_name, l_stmt_id, l_error_message );
1537 raise l_exception;
1538 end if;
1539
1540 bis_collection_utilities_log( 'Gathered stats for staging table', 1 );
1541
1542 -- do the merge into isc_fs_task_assignmnts_f
1543 l_stmt_id := 70;
1544 merge into isc_fs_task_assignmnts_f o
1545 using (
1546 select /*+ ordered use_nl(TA,TS,T_SCH,T_ACT,E_ACT,DGA) */
1547 -- deleted task assignments are moved to negative task_id
1548 -- to break join with isc_fs_tasks_f later
1549 nvl(ta.task_id,0-e.task_id) task_id
1550 , e.task_assignment_id
1551 , decode(ta.task_id,null,'Y','N') deleted_flag
1552 , nvl(ts.cancelled_flag,'N') cancelled_flag
1553 , ta.creation_date assignment_creation_date
1554 -- R12 resource type impact
1555 , ta.resource_id resource_id
1556 , decode( ta.resource_type_code
1557 , 'RS_GROUP', 'GROUP'
1558 , 'RS_TEAM', 'TEAM'
1559 , null, null
1560 , 'RESOURCE'
1561 ) resource_type
1562 , decode( ta.resource_type_code
1563 , 'RS_GROUP', ta.resource_id
1564 , nvl(dga.group_id,-1)
1565 ) district_id
1566 , (ta.actual_effort * e_act.conversion_rate * g_time_base_to_hours) actual_effort_hrs
1567 , ta.sched_travel_distance sched_travel_distance_km
1568 , (ta.sched_travel_duration * t_sch.conversion_rate * g_time_base_to_hours) * 60 sched_travel_duration_min
1569 , ta.actual_travel_distance actual_travel_distance_km
1570 , (ta.actual_travel_duration * t_act.conversion_rate * g_time_base_to_hours) * 60 actual_travel_duration_min
1571 , ta.actual_start_date
1572 , ta.actual_end_date
1573 , case
1574 when decode(ta.task_id,null,'Y','N') = 'N' and
1575 ta.actual_end_date >= g_global_start_date then
1576 trunc(ta.actual_end_date)
1577 else
1578 null
1579 end report_date
1580 from
1581 jtf_task_assignments ta
1582 , ( select /*+ NO_MERGE */ distinct
1583 task_id
1584 , task_assignment_id
1585 from
1586 isc_fs_events_stg
1587 where task_assignment_id is not null
1588 and source_object_type_code = 'SR'
1589 ) e
1590 , jtf_rs_default_groups dga
1591 , jtf_task_statuses_b ts
1592 , mtl_uom_conversions t_sch
1593 , mtl_uom_conversions t_act
1594 , mtl_uom_conversions e_act
1595 where
1596 -- needs to be out to handle deleted assignments
1597 e.task_assignment_id = ta.task_assignment_id(+)
1598 -- needs nvl/outer to handle deleted assignments
1599 and nvl(ta.assignment_status_id,-1) = ts.task_status_id(+)
1600 and t_sch.inventory_item_id = 0
1601 and t_sch.uom_class = g_time_uom_class
1602 and t_sch.uom_code = nvl(ta.sched_travel_duration_uom,g_uom_hours)
1603 and t_act.inventory_item_id = 0
1604 and t_act.uom_class = g_time_uom_class
1605 and t_act.uom_code = nvl(ta.actual_travel_duration_uom,g_uom_hours)
1606 and e_act.inventory_item_id = 0
1607 and e_act.uom_class = g_time_uom_class
1608 and e_act.uom_code = nvl(ta.actual_effort_uom,g_uom_hours)
1609 --
1610 and decode( ta.resource_type_code
1611 , null, -2
1612 , 'RS_GROUP', -2
1613 , 'RS_TEAM', -2
1614 , ta.resource_id ) = dga.resource_id(+)
1615 and trunc(nvl(ta.creation_date,sysdate)) >= dga.start_date(+)
1616 and trunc(nvl(ta.creation_date,sysdate)) <= dga.end_date(+)
1617 and 'FLD_SRV_DISTRICT' = dga.usage(+)
1618 ) n
1619 on (
1620 o.task_assignment_id = n.task_assignment_id
1621 )
1622 when matched then
1623 update
1624 set
1625 o.task_id = n.task_id
1626 , o.deleted_flag = n.deleted_flag
1627 , o.cancelled_flag = n.cancelled_flag
1628 , o.resource_id = n.resource_id
1629 -- R12 resource type impact
1630 , o.resource_type = n.resource_type
1631 , o.district_id = n.district_id
1632 , o.actual_effort_hrs = n.actual_effort_hrs
1633 , o.sched_travel_distance_km = n.sched_travel_distance_km
1634 , o.sched_travel_duration_min = n.sched_travel_duration_min
1635 , o.actual_travel_distance_km = n.actual_travel_distance_km
1636 , o.actual_travel_duration_min = n.actual_travel_duration_min
1637 , o.actual_start_date = n.actual_start_date
1638 , o.actual_end_date = n.actual_end_date
1639 , o.report_date = n.report_date
1640 , o.last_updated_by = g_user_id
1641 , o.last_update_date = l_collect_to_date -- don't use sysdate as need to synchronize dates
1642 , o.last_update_login = g_login_id
1643 , o.program_id = g_program_id
1644 , o.program_login_id = g_program_login_id
1645 , o.program_application_id = g_program_application_id
1646 , o.request_id = g_request_id
1647 when not matched then
1648 insert
1649 ( task_id
1650 , task_assignment_id
1651 , deleted_flag
1652 , cancelled_flag
1653 , assignment_creation_date
1654 , resource_id
1655 -- R12 resource type impact
1656 , resource_type
1657 , district_id
1658 , actual_effort_hrs
1659 , sched_travel_distance_km
1660 , sched_travel_duration_min
1661 , actual_travel_distance_km
1662 , actual_travel_duration_min
1663 , actual_start_date
1664 , actual_end_date
1665 , report_date
1666 , created_by
1667 , creation_date
1668 , last_updated_by
1669 , last_update_date
1670 , last_update_login
1671 , program_id
1672 , program_login_id
1673 , program_application_id
1674 , request_id
1675 )
1676 values
1677 ( n.task_id
1678 , n.task_assignment_id
1679 , n.deleted_flag
1680 , n.cancelled_flag
1681 , n.assignment_creation_date
1682 , n.resource_id
1683 -- R12 resource type impact
1684 , n.resource_type
1685 , n.district_id
1686 , n.actual_effort_hrs
1687 , n.sched_travel_distance_km
1688 , n.sched_travel_duration_min
1689 , n.actual_travel_distance_km
1690 , n.actual_travel_duration_min
1691 , n.actual_start_date
1692 , n.actual_end_date
1693 , n.report_date
1694 , g_user_id
1695 , l_collect_to_date -- don't use sysdate as need to synchronize dates
1696 , g_user_id
1697 , l_collect_to_date -- don't use sysdate as need to synchronize dates
1698 , g_login_id
1699 , g_program_id
1700 , g_program_login_id
1701 , g_program_application_id
1702 , g_request_id
1703 );
1704
1705 l_rowcount := sql%rowcount;
1706
1707 bis_collection_utilities_log( l_rowcount || ' rows merged into task assignments base summary', 1 );
1708
1709 -- do the merge into isc_fs_tasks_f
1710 l_stmt_id := 80;
1711 merge into isc_fs_tasks_f o
1712 using (
1713 select
1714 task_id
1715 , task_number
1716 , task_type_id
1717 , task_type_rule
1718 , break_fix_flag
1719 , task_status_id
1720 , owner_id
1721 -- R12 resource type impact
1722 , owner_type
1723 , owner_district_id
1724 , customer_id
1725 , address_id
1726 -- R12 impact
1727 , location_id
1728 , planned_start_date
1729 , planned_end_date
1730 , scheduled_start_date
1731 , scheduled_end_date
1732 , actual_start_date
1733 , actual_end_date
1734 , source_object_type_code
1735 , source_object_id
1736 , source_object_name
1737 , planned_effort_hrs
1738 , actual_effort_hrs
1739 , cancelled_flag
1740 , completed_flag
1741 , closed_flag
1742 , deleted_flag
1743 , task_creation_date
1744 -- R12 impact null value for first_asgn_creation_date for "child" task
1745 , decode(task_split_flag,'D',to_date(null),first_asgn_creation_date) first_asgn_creation_date
1746 --
1747 -- R12 impact null value for act_bac_assignee_id for "child" task
1748 , decode(task_split_flag,'D',to_number(null),nvl(act_bac_assignee_id,owner_id)) act_bac_assignee_id
1749 -- R12 resource type impact
1750 , decode(task_split_flag,'D',null,nvl(act_bac_assignee_type,owner_type)) act_bac_assignee_type
1751 -- R12 impact null value for act_bac_district_id for "child" task
1752 , decode(task_split_flag,'D',to_number(null),nvl(act_bac_district_id,owner_district_id)) act_bac_district_id
1753 , include_task_in_ttr_flag
1754 , include_task_in_ftf_flag
1755 , ftf_flag
1756 --
1757 -- R12 impact null value for ftf_assignee_id for "child" task
1758 , decode(task_split_flag,'D',to_number(null),nvl(ftf_assignee_id,owner_id)) ftf_assignee_id
1759 -- R12 resource type impact
1760 , decode(task_split_flag,'D',null,nvl(ftf_assignee_type,owner_type)) ftf_assignee_type
1761 -- R12 impact null value for ftf_district_id for "child" task
1762 , decode(task_split_flag,'D',to_number(null),nvl(ftf_district_id,owner_district_id)) ftf_district_id
1763 --
1764 -- R12 impact null value for ttr_assignee_id for "child" task
1765 , decode(task_split_flag,'D',to_number(null),nvl(ttr_assignee_id,owner_id)) ttr_assignee_id
1766 -- R12 resource type impact
1767 , decode(task_split_flag,'D',null,nvl(ttr_assignee_type,owner_type)) ttr_assignee_type
1768 -- R12 impact null value for ttr_district_id for "child" task
1769 , decode(task_split_flag,'D',to_number(null),nvl(ttr_district_id,owner_district_id)) ttr_district_id
1770 --
1771 , incident_date
1772 , inventory_item_id
1773 , inv_organization_id
1774 , task_split_flag
1775 , parent_task_id
1776 from
1777 -- note: the select statement for the incremental load logic has been
1778 -- been rewritten to match the initial load. this was necessary to
1779 -- accommodate the multiple levels of across aggregation which could
1780 -- not be included in the previous logic.
1781 (
1782 /* needs to be reviewed by performance team for r12 as changed from 11i */
1783 select
1784 --
1785 -- simple columns
1786 --
1787 x.task_id
1788 , x.task_rn
1789 , x.task_number
1790 , x.task_type_id
1791 , x.task_type_rule
1792 , x.break_fix_flag
1793 , x.task_status_id
1794 , x.owner_id
1795 -- R12 resource type impact
1796 , x.owner_type
1797 , x.owner_district_id
1798 , x.customer_id
1799 , x.address_id
1800 -- R12 impact
1801 , x.location_id
1802 , x.planned_start_date
1803 , x.planned_end_date
1804 , x.scheduled_start_date
1805 , x.scheduled_end_date
1806 , x.actual_start_date
1807 , x.actual_end_date
1808 , x.source_object_type_code
1809 , x.source_object_id
1810 , x.source_object_name
1811 , x.planned_effort_hrs
1812 , x.actual_effort_hrs
1813 , x.task_creation_date
1814 , x.cancelled_flag
1815 , x.completed_flag
1816 , x.closed_flag
1817 , x.deleted_flag
1818 --
1819 , x.incident_date
1820 , x.inventory_item_id
1821 , x.inv_organization_id
1822 --
1823 , x.task_split_flag
1824 , x.parent_task_id
1825 --
1826 --
1827 -- complex columns
1828 --
1829 -- returns the date of the first created (not cancelled) assignment for the task
1830 , min( x.asgn_creation_date_unc )
1831 keep( dense_rank first
1832 order by x.asgn_creation_date_unc nulls last
1833 )
1834 over( partition by nvl(x.parent_task_id,x.task_id) ) first_asgn_creation_date
1835 --
1836 -- assignee/district are determined uniquely for activity/backlog, ftf and ttr
1837 -- as they have different rules for establishing ownership and dealing with
1838 -- cancelled assignments.
1839 --
1840 -- activity/backlog
1841 -- based on last assignment creation date of non-cancelled assignments, in the case of
1842 -- split "parent" tasks, it is from the last "scheduled" "child" task assignments.
1843 --
1844 , max( x.act_bac_resource_id )
1845 keep( dense_rank last
1846 order by
1847 x.scheduled_end_date
1848 , x.asgn_creation_date_unc nulls first
1849 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1850 , x.task_assignment_id )
1851 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_assignee_id
1852 -- R12 resource type impact
1853 , max( x.act_bac_resource_type )
1854 keep( dense_rank last
1855 order by
1856 x.scheduled_end_date
1857 , x.asgn_creation_date_unc nulls first
1858 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1859 , x.task_assignment_id )
1860 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_assignee_type
1861 --
1862 , max( x.act_bac_district_id )
1863 keep( dense_rank last
1864 order by
1865 x.scheduled_end_date
1866 , x.asgn_creation_date_unc nulls first
1867 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1868 , x.task_assignment_id )
1869 over( partition by nvl(x.parent_task_id,x.task_id) ) act_bac_district_id
1870 --
1871 -- ftf
1872 -- based on last assignment creation date, exclude cancelled assignments unless
1873 -- they have actual_end_date not null
1874 --
1875 , max( x.ftf_ttr_resource_id )
1876 keep( dense_rank last
1877 order by
1878 x.scheduled_end_date
1879 , x.asgn_creation_date_unc nulls first
1880 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1881 , x.task_assignment_id)
1882 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_assignee_id
1883 -- R12 resource type impact
1884 , max( x.ftf_ttr_resource_type )
1885 keep( dense_rank last
1886 order by
1887 x.scheduled_end_date
1888 , x.asgn_creation_date_unc nulls first
1889 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1890 , x.task_assignment_id)
1891 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_assignee_type
1892 --
1893 , max( x.ftf_ttr_district_id )
1894 keep( dense_rank last
1895 order by
1896 x.scheduled_end_date
1897 , x.asgn_creation_date_unc nulls first
1898 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1899 , x.task_assignment_id)
1900 over( partition by nvl(x.parent_task_id,x.task_id) ) ftf_district_id
1901 --
1902 -- ttr
1903 -- based on the last worked (assignment actual end date), exclude cancelled assignments unless
1904 -- they have actual_end_date not null
1905 --
1906 , max( x.ftf_ttr_resource_id )
1907 keep( dense_rank last
1908 order by
1909 x.asgn_actual_end_date nulls first
1910 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1911 , x.task_assignment_id )
1912 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_assignee_id
1913 -- R12 resource type impact
1914 , max( x.ftf_ttr_resource_type )
1915 keep( dense_rank last
1916 order by
1917 x.asgn_actual_end_date nulls first
1918 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1919 , x.task_assignment_id )
1920 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_assignee_type
1921 --
1922 , max( x.ftf_ttr_district_id )
1923 keep( dense_rank last
1924 order by
1925 x.asgn_actual_end_date nulls first
1926 , x.asgn_creation_date -- always use creation_date ahead of xxx_id for RAC
1927 , x.task_assignment_id )
1928 over( partition by nvl(x.parent_task_id,x.task_id) ) ttr_district_id
1929 --
1930 --
1931 -- ttr
1932 -- only rows (one per SR) with 'Y' are included in ttr reports
1933 -- a) not deleted, type rule = DISPATCH, break/fix enabled
1934 -- b) latest actual end date, if no actual end dates, latest scheduled end date
1935 -- c) "normal" or "parent" tasks
1936 , case
1937 when x.ttr_ftf_flag <> 'Y' then 'N'
1938 when rank() over( partition by
1939 x.source_object_type_code
1940 , x.source_object_id
1941 order by
1942 x.ttr_ftf_flag desc
1943 , x.ttr_ftf_actual_end_date desc nulls last
1944 , x.ttr_ftf_sched_end_date desc nulls last
1945 , x.task_creation_date desc -- always use creation_date ahead of xxx_id for RAC
1946 , x.task_id desc
1947 ) <> 1 then 'N'
1948 else 'Y'
1949 end include_task_in_ttr_flag
1950 --
1951 -- ftf
1952 -- multiple rows per SR with 'Y' are included in non ftf detail report
1953 -- not deleted, type rule = DISPATCH, break/fix enabled
1954 -- "normal" or "parent" tasks
1955 , x.ttr_ftf_flag include_task_in_ftf_flag
1956 --
1957 -- ftf
1958 -- only rows (one per SR) with 'Y' or 'N' are included in ftf reports
1959 -- a) not deleted, type rule = DISPATCH, break/fix enabled
1960 -- b) earliest actual start date, if no actual start dates, latest scheduled start date
1961 -- c) trunc( earliest actual end date ) = trunc ( latest actual end date ) = ftf else non ftf
1962 -- d) "normal" or "parent" tasks
1963 , case
1964 when x.ttr_ftf_flag <> 'Y' then '-'
1965 when rank() over( partition by
1966 x.source_object_type_code
1967 , x.source_object_id
1968 order by
1969 x.ttr_ftf_flag desc
1970 , x.ttr_ftf_actual_start_date
1971 , x.ttr_ftf_sched_start_date
1972 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
1973 , x.task_id
1974 ) <> 1 then '-'
1975 when trunc( first_value( x.ttr_ftf_actual_start_date ) -- use start_date
1976 over( partition by
1977 x.source_object_type_code
1978 , x.source_object_id
1979 order by
1980 x.ttr_ftf_flag desc
1981 , x.ttr_ftf_actual_start_date nulls last -- use start_date
1982 , x.ttr_ftf_sched_start_date nulls last -- use start_date
1983 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
1984 , x.task_id
1985 rows between unbounded preceding and unbounded following
1986 )
1987 ) <>
1988 trunc( last_value( x.ttr_ftf_actual_start_date ) -- use start_date
1989 over( partition by
1990 x.source_object_type_code
1991 , x.source_object_id
1992 order by
1993 x.ttr_ftf_flag
1994 , x.ttr_ftf_actual_start_date nulls first -- use start_date
1995 , x.ttr_ftf_sched_start_date nulls first -- use start_date
1996 , x.task_creation_date -- always use creation_date ahead of xxx_id for RAC
1997 , x.task_id
1998 rows between unbounded preceding and unbounded following
1999 )
2000 ) then 'N'
2001 else 'Y'
2002 end ftf_flag
2003 --
2004 from
2005 ( select
2006 t.task_id
2007 , t.task_number
2008 , t.task_type_id
2009 , t.task_type_rule
2010 , t.break_fix_flag
2011 , row_number() over( partition by t.task_id
2012 order by t.asgn_creation_date
2013 ) task_rn
2014 , case
2015 when t.task_split_flag = 'D' then 'N'
2016 when nvl(t.task_type_rule,'X') = 'DISPATCH' and
2017 t.break_fix_flag = 'Y' and
2018 'Y' in (t.closed_flag,t.completed_flag) and
2019 t.incident_date >= g_global_start_date then 'Y'
2020 else 'N'
2021 end ttr_ftf_flag
2022 , case
2023 when t.task_split_flag = 'D' then to_date(null)
2024 when nvl(t.task_type_rule,'X') = 'DISPATCH' and
2025 t.break_fix_flag = 'Y' and
2026 'Y' in (t.closed_flag,t.completed_flag) and
2027 t.incident_date >= g_global_start_date then t.actual_start_date
2028 else null
2029 end ttr_ftf_actual_start_date
2030 , case
2031 when t.task_split_flag = 'D' then to_date(null)
2032 when nvl(t.task_type_rule,'X') = 'DISPATCH' and
2033 t.break_fix_flag = 'Y' and
2034 'Y' in (t.closed_flag,t.completed_flag) and
2035 t.incident_date >= g_global_start_date then t.actual_end_date
2036 else null
2037 end ttr_ftf_actual_end_date
2038 , case
2039 when t.task_split_flag = 'D' then to_date(null)
2040 when nvl(t.task_type_rule,'X') = 'DISPATCH' and
2041 t.break_fix_flag = 'Y' and
2042 'Y' in (t.closed_flag,t.completed_flag) and
2043 t.incident_date >= g_global_start_date then t.scheduled_start_date
2044 else null
2045 end ttr_ftf_sched_start_date
2046 , case
2047 when t.task_split_flag = 'D' then to_date(null)
2048 when nvl(t.task_type_rule,'X') = 'DISPATCH' and
2049 t.break_fix_flag = 'Y' and
2050 'Y' in (t.closed_flag,t.completed_flag) and
2051 t.incident_date >= g_global_start_date then t.scheduled_end_date
2052 else null
2053 end ttr_ftf_sched_end_date
2054 , t.task_status_id
2055 , t.owner_id
2056 -- R12 resource type impact
2057 , t.owner_type
2058 , t.owner_district_id
2059 , t.customer_id
2060 , t.address_id
2061 -- R12 impact
2062 , t.location_id
2063 , t.planned_start_date
2064 , t.planned_end_date
2065 , t.planned_effort_hrs
2066 , t.actual_start_date
2067 , t.actual_end_date
2068 , t.actual_effort_hrs
2069 , t.scheduled_start_date
2070 , t.scheduled_end_date
2071 , t.source_object_type_code
2072 , t.source_object_id
2073 , t.source_object_name
2074 , t.task_creation_date
2075 , t.cancelled_flag
2076 , t.completed_flag
2077 , t.closed_flag
2078 , t.deleted_flag
2079 --
2080 , t.task_assignment_id
2081 , t.asgn_creation_date
2082 , t.asgn_resource_id
2083 -- R12 resource type impact
2084 , t.asgn_resource_type
2085 , t.asgn_district_id
2086 , t.asgn_actual_end_date
2087 , t.asgn_cancelled_flag
2088 --
2089 -- activity/backlog
2090 -- only return non-null resource type code for uncancelled
2091 -- only return non-null resource id for uncancelled
2092 , decode( t.asgn_cancelled_flag
2093 , 'N', t.asgn_resource_id
2094 , null ) act_bac_resource_id
2095 -- R12 resource type impact
2096 , decode( t.asgn_cancelled_flag
2097 , 'N', t.asgn_resource_type
2098 , null ) act_bac_resource_type
2099 -- only return non-null district id for uncancelled
2100 , decode( t.asgn_cancelled_flag
2101 , 'N', t.asgn_district_id
2102 , null ) act_bac_district_id
2103 --
2104 -- ftf/ttr
2105 -- return resource type code of assignments with actual end date, or un-cancelled
2106 -- return resource id of assignments with actual end date, or un-cancelled
2107 , decode( t.asgn_actual_end_date
2108 , null, decode( t.asgn_cancelled_flag
2109 , 'N', t.asgn_resource_id
2110 , null )
2111 , t.asgn_resource_id ) ftf_ttr_resource_id
2112 -- R12 resource type impact
2113 , decode( t.asgn_actual_end_date
2114 , null, decode( t.asgn_cancelled_flag
2115 , 'N', t.asgn_resource_type
2116 , null )
2117 , t.asgn_resource_type ) ftf_ttr_resource_type
2118 -- return district id of assignments with actual end date, or un-cancelled
2119 , decode( t.asgn_actual_end_date
2120 , null, decode( t.asgn_cancelled_flag
2121 , 'N', t.asgn_district_id
2122 , null )
2123 , t.asgn_district_id ) ftf_ttr_district_id
2124 -- return non-null creation date for un-cancelled
2125 , decode( t.asgn_cancelled_flag
2126 , 'N', t.asgn_creation_date
2127 , null ) asgn_creation_date_unc
2128 --
2129 , t.incident_date
2130 , t.inventory_item_id
2131 , t.inv_organization_id
2132 --
2133 , t.task_split_flag
2134 , t.parent_task_id
2135 --
2136 from
2137 ( select
2138 t.source_object_type_code
2139 -- hide source_object_id for deleted tasks from partitioning above
2140 , case
2141 when t.deleted_flag = 'Y' or
2142 t.customer_id is null then
2143 -- tasks with null customer_id are invalid
2144 0-t.source_object_id
2145 else
2146 t.source_object_id
2147 end source_object_id
2148 , t.source_object_name
2149 , t.task_id
2150 , t.task_number
2151 , t.task_status_id
2152 , t.task_type_id
2153 , case
2154 when t.customer_id is null or
2155 -- tasks with null customer_id are invalid
2156 t.deleted_flag = 'Y' then
2157 null
2158 else
2159 tt.rule
2160 end task_type_rule
2161 , case
2162 when t.customer_id is null or
2163 -- tasks with null customer_id are invalid
2164 t.deleted_flag = 'Y' then
2165 'N'
2166 else
2167 nvl(bf.enabled,'N')
2168 end break_fix_flag
2169 -- R12 resource type impact
2170 , t.owner_id owner_id
2171 , decode( t.owner_type_code
2172 , 'RS_GROUP', 'GROUP'
2173 , 'RS_TEAM', 'TEAM'
2174 , null, null
2175 , 'RESOURCE'
2176 ) owner_type
2177 , decode( t.owner_type_code
2178 , 'RS_GROUP', t.owner_id
2179 , nvl(dgt.group_id,-1)
2180 ) owner_district_id
2181 , nvl(t.customer_id,-2) customer_id
2182 -- allow for null customer_id
2183 , t.address_id
2184 -- R12 impact
2185 , t.location_id
2186 , t.planned_start_date
2187 , t.planned_end_date
2188 , t.actual_start_date
2189 , t.actual_end_date
2190 , t.scheduled_start_date
2191 , t.scheduled_end_date
2192 , (t.planned_effort * t_peff.conversion_rate * g_time_base_to_hours) planned_effort_hrs
2193 , (t.actual_effort * t_eff.conversion_rate * g_time_base_to_hours) actual_effort_hrs
2194 , t.creation_date task_creation_date
2195 , nvl(ts.cancelled_flag,'N') cancelled_flag
2196 , nvl(ts.completed_flag,'N') completed_flag
2197 , nvl(ts.closed_flag,'N') closed_flag
2198 , decode(t.customer_id,null,'Y',nvl(t.deleted_flag,'N')) deleted_flag
2199 -- tasks with null customer_id are invalid
2200 --
2201 , ta.resource_id asgn_resource_id
2202 -- R12 resource type impact
2203 , ta.resource_type asgn_resource_type
2204 , ta.district_id asgn_district_id
2205 , ta.assignment_creation_date asgn_creation_date
2206 , ta.task_assignment_id
2207 , ta.cancelled_flag asgn_cancelled_flag
2208 , ta.actual_start_date asgn_actual_start_date
2209 , ta.actual_end_date asgn_actual_end_date
2210 --
2211 , i.incident_date
2212 , nvl2( i.inventory_item_id+i.inv_organization_id
2213 , i.inventory_item_id
2214 , -1
2215 ) inventory_item_id
2216 , nvl2( i.inventory_item_id+i.inv_organization_id
2217 , i.inv_organization_id
2218 , -99
2219 )inv_organization_id
2220 --
2221 , t.task_split_flag
2222 -- hide parent_task_id for deleted tasks from partitioning above
2223 , case
2224 when t.deleted_flag = 'Y' or
2225 t.customer_id is null then
2226 -- tasks with null customer_id are invalid
2227 0-t.parent_task_id
2228 else
2229 t.parent_task_id
2230 end parent_task_id
2231 from
2232 jtf_tasks_b t
2233 , isc_fs_task_assignmnts_f ta
2234 , jtf_task_statuses_b ts
2235 , jtf_task_types_b tt
2236 , mtl_uom_conversions t_eff
2237 , mtl_uom_conversions t_peff
2238 , ( select /*+ NO_MERGE */ distinct
2239 source_object_id
2240 from
2241 isc_fs_events_stg
2242 where
2243 source_object_type_code = 'SR'
2244 ) e
2245 , cs_incidents_all_b i
2246 , isc_fs_break_fix_tasks bf
2247 , jtf_rs_default_groups dgt
2248 where
2249 t.source_object_id = e.source_object_id
2250 and t.source_object_type_code = 'SR'
2251 --
2252 and t.source_object_id = i.incident_id
2253 --
2254 and t.task_id = ta.task_id(+)
2255 --
2256 and t.task_status_id = ts.task_status_id
2257 --
2258 and t.task_type_id = tt.task_type_id
2259 --
2260 and t.task_type_id = bf.task_type_id(+)
2261 --
2262 -- R12 resource type impact
2263 and decode( t.owner_type_code
2264 , null, -2
2265 , 'RS_GROUP', -2
2266 , 'RS_TEAM', -2
2267 , t.owner_id
2268 ) = dgt.resource_id(+)
2269 and trunc(t.creation_date) >= dgt.start_date(+)
2270 and trunc(t.creation_date) <= dgt.end_date(+)
2271 and 'FLD_SRV_DISTRICT' = dgt.usage(+)
2272 --
2273 and t_peff.inventory_item_id = 0
2274 and t_peff.uom_class = g_time_uom_class
2275 and t_peff.uom_code = nvl(t.planned_effort_uom,g_uom_hours)
2276 --
2277 and t_eff.inventory_item_id = 0
2278 and t_eff.uom_class = g_time_uom_class
2279 and t_eff.uom_code = nvl(t.actual_effort_uom,g_uom_hours)
2280 ) t
2281 ) x
2282 )
2283 where task_rn = 1
2284 ) n
2285 on ( o.task_id = n.task_id
2286 )
2287 when matched then
2288 update
2289 set
2290 o.task_type_id = n.task_type_id
2291 , o.task_type_rule = n.task_type_rule
2292 , o.break_fix_flag = n.break_fix_flag
2293 , o.task_status_id = n.task_status_id
2294 , o.owner_id = n.owner_id
2295 -- R12 resource type impact
2296 , o.owner_type = n.owner_type
2297 , o.owner_district_id = n.owner_district_id
2298 , o.customer_id = n.customer_id
2299 , o.address_id = n.address_id
2300 -- R12 impact
2301 , o.location_id = n.location_id
2302 , o.planned_start_date = n.planned_start_date
2303 , o.planned_end_date = n.planned_end_date
2304 , o.scheduled_start_date = n.scheduled_start_date
2305 , o.scheduled_end_date = n.scheduled_end_date
2306 , o.actual_start_date = n.actual_start_date
2307 , o.actual_end_date = n.actual_end_date
2308 --
2309 , o.source_object_type_code = n.source_object_type_code
2310 , o.source_object_id = n.source_object_id
2311 , o.source_object_name = n.source_object_name
2312 --
2313 , o.planned_effort_hrs = n.planned_effort_hrs
2314 , o.actual_effort_hrs = n.actual_effort_hrs
2315 , o.cancelled_flag = n.cancelled_flag
2316 , o.completed_flag = n.completed_flag
2317 , o.closed_flag = n.closed_flag
2318 , o.deleted_flag = n.deleted_flag
2319 , o.first_asgn_creation_date = n.first_asgn_creation_date
2320 , o.act_bac_assignee_id = n.act_bac_assignee_id
2321 -- R12 resource type impact
2322 , o.act_bac_assignee_type = n.act_bac_assignee_type
2323 , o.act_bac_district_id = n.act_bac_district_id
2324 , o.ftf_assignee_id = n.ftf_assignee_id
2325 -- R12 resource type impact
2326 , o.ftf_assignee_type = n.ftf_assignee_type
2327 , o.ftf_district_id = n.ftf_district_id
2328 , o.ttr_assignee_id = n.ttr_assignee_id
2329 -- R12 resource type impact
2330 , o.ttr_assignee_type = n.ttr_assignee_type
2331 , o.ttr_district_id = n.ttr_district_id
2332 --
2333 , o.ftf_ttr_district_rule = g_ttr_ftf_rule
2334 --
2335 , o.include_task_in_ttr_flag = n.include_task_in_ttr_flag
2336 , o.include_task_in_ftf_flag = n.include_task_in_ftf_flag
2337 , o.ftf_flag = n.ftf_flag
2338 --
2339 , o.incident_date = n.incident_date
2340 , o.inventory_item_id = n.inventory_item_id
2341 , o.inv_organization_id = n.inv_organization_id
2342 --
2343 -- R12 impact
2344 , o.task_split_flag = n.task_split_flag
2345 , o.parent_task_id = n.parent_task_id
2346 --
2347 , o.last_updated_by = g_user_id
2348 , o.last_update_date = l_collect_to_date -- don't use sysdate as need to synchronize dates
2349 , o.last_update_login = g_login_id
2350 , o.program_id = g_program_id
2351 , o.program_login_id = g_program_login_id
2352 , o.program_application_id = g_program_application_id
2353 , o.request_id = g_request_id
2354 when not matched then
2355 insert
2356 ( task_id
2357 , task_number
2358 , task_type_id
2359 , task_type_rule
2360 , break_fix_flag
2361 , task_status_id
2362 , owner_id
2363 -- R12 resource type impact
2364 , owner_type
2365 , owner_district_id
2366 , customer_id
2367 , address_id
2368 -- R12 impact
2369 , location_id
2370 , planned_start_date
2371 , planned_end_date
2372 , scheduled_start_date
2373 , scheduled_end_date
2374 , actual_start_date
2375 , actual_end_date
2376 , source_object_type_code
2377 , source_object_id
2378 , source_object_name
2379 , planned_effort_hrs
2380 , actual_effort_hrs
2381 , cancelled_flag
2382 , completed_flag
2383 , closed_flag
2384 , deleted_flag
2385 , task_creation_date
2386 , first_asgn_creation_date
2387 , act_bac_assignee_id
2388 -- R12 resource type impact
2389 , act_bac_assignee_type
2390 , act_bac_district_id
2391 , ftf_assignee_id
2392 -- R12 resource type impact
2393 , ftf_assignee_type
2394 , ftf_district_id
2395 , ttr_assignee_id
2396 -- R12 resource type impact
2397 , ttr_assignee_type
2398 , ttr_district_id
2399 , ftf_ttr_district_rule
2400 , created_by
2401 , creation_date
2402 , last_updated_by
2403 , last_update_date
2404 , last_update_login
2405 , program_id
2406 , program_login_id
2407 , program_application_id
2408 , request_id
2409 --
2410 , include_task_in_ttr_flag
2411 , include_task_in_ftf_flag
2412 , ftf_flag
2413 --
2414 , incident_date
2415 , inventory_item_id
2416 , inv_organization_id
2417 --
2418 -- R12 impact
2419 , task_split_flag
2420 , parent_task_id
2421 )
2422 values
2423 ( n.task_id
2424 , n.task_number
2425 , n.task_type_id
2426 , n.task_type_rule
2427 , n.break_fix_flag
2428 , n.task_status_id
2429 , n.owner_id
2430 -- R12 resource type impact
2431 , n.owner_type
2432 , n.owner_district_id
2433 , n.customer_id
2434 , n.address_id
2435 -- R12 impact
2436 , n.location_id
2437 , n.planned_start_date
2438 , n.planned_end_date
2439 , n.scheduled_start_date
2440 , n.scheduled_end_date
2441 , n.actual_start_date
2442 , n.actual_end_date
2443 , n.source_object_type_code
2444 , n.source_object_id
2445 , n.source_object_name
2446 , n.planned_effort_hrs
2447 , n.actual_effort_hrs
2448 , n.cancelled_flag
2449 , n.completed_flag
2450 , n.closed_flag
2451 , n.deleted_flag
2452 , n.task_creation_date
2453 , n.first_asgn_creation_date
2454 , n.act_bac_assignee_id
2455 -- R12 resource type impact
2456 , n.act_bac_assignee_type
2457 , n.act_bac_district_id
2458 , n.ftf_assignee_id
2459 -- R12 resource type impact
2460 , n.ftf_assignee_type
2461 , n.ftf_district_id
2462 , n.ttr_assignee_id
2463 -- R12 resource type impact
2464 , n.ttr_assignee_type
2465 , n.ttr_district_id
2466 --
2467 , g_ttr_ftf_rule
2468 , g_user_id
2469 , l_collect_to_date -- don't use sysdate as need to synchronize dates
2470 , g_user_id
2471 , l_collect_to_date -- don't use sysdate as need to synchronize dates
2472 , g_login_id
2473 , g_program_id
2474 , g_program_login_id
2475 , g_program_application_id
2476 , g_request_id
2477 --
2478 , n.include_task_in_ttr_flag
2479 , n.include_task_in_ftf_flag
2480 , n.ftf_flag
2481 --
2482 , n.incident_date
2483 , n.inventory_item_id
2484 , n.inv_organization_id
2485 --
2486 -- R12 impact
2487 , n.task_split_flag
2488 , n.parent_task_id
2489 );
2490
2491 l_temp_rowcount := sql%rowcount;
2492
2493 bis_collection_utilities_log( l_temp_rowcount || ' rows merged into tasks base summary', 1 );
2494
2495 l_rowcount := l_rowcount + l_temp_rowcount;
2496
2497 -- R12 dep/arr
2498 -- do the merge into isc_fs_capacity_f
2499 l_stmt_id := 90;
2500 merge into isc_fs_capacity_f o
2501 using (
2502 select /*+ ordered use_nl(T,TA,DG,OC) */
2503 t.task_id
2504 -- R12 resource type impact
2505 , t.owner_id owner_id
2506 , decode( t.owner_type_code
2507 , 'RS_GROUP', 'GROUP'
2508 , 'RS_TEAM', 'TEAM'
2509 , null, null
2510 , 'RESOURCE'
2511 ) owner_type
2512 , decode( t.owner_type_code
2513 , 'RS_GROUP', t.owner_id
2514 , nvl(dg.group_id,-1)
2515 ) district_id
2516 , ta.object_capacity_id
2517 , trunc(oc.end_date_time) capacity_date
2518 , (oc.end_date_time - oc.start_date_time ) * 24 capacity_hours
2519 , decode( oc.STATUS
2520 , 1, 'N'
2521 , 0, 'Y'
2522 , null
2523 ) blocked_trip_flag
2524 , decode( t.task_type_id
2525 , 20, nvl(t.deleted_flag,'N')
2526 , 'Y'
2527 ) deleted_flag
2528 from
2529 jtf_tasks_b t
2530 , jtf_task_assignments ta
2531 , ( select /*+ NO_MERGE */ distinct
2532 task_id
2533 from isc_fs_events_stg
2534 where source_object_type_code = 'TASK'
2535 ) e
2536 , jtf_rs_default_groups dg
2537 , cac_sr_object_capacity oc
2538 where
2539 e.task_id = t.task_id
2540 -- needs to be out to handle deleted assignments
2541 and t.task_id = ta.task_id(+)
2542 --
2543 and decode( t.owner_type_code
2544 , null, -2
2545 , 'RS_GROUP', -2
2546 , 'RS_TEAM', -2
2547 , t.owner_id ) = dg.resource_id(+)
2548 and trunc(t.planned_start_date) >= dg.start_date(+)
2549 and trunc(t.planned_start_date) <= dg.end_date(+)
2550 and 'FLD_SRV_DISTRICT' = dg.usage(+)
2551 --
2552 and nvl(ta.object_capacity_id,-123) = oc.object_capacity_id(+)
2553 ) n
2554 on (
2555 o.task_id = n.task_id
2556 )
2557 when matched then
2558 update
2559 set
2560 o.owner_id = n.owner_id
2561 -- R12 resource type impact
2562 , o.owner_type = n.owner_type
2563 , o.district_id = n.district_id
2564 , o.object_capacity_id = n.object_capacity_id
2565 , o.capacity_date = n.capacity_date
2566 , o.capacity_hours = n.capacity_hours
2567 , o.blocked_trip_flag = n.blocked_trip_flag
2568 , o.deleted_flag = n.deleted_flag
2569 , o.last_updated_by = g_user_id
2570 , o.last_update_date = sysdate
2571 , o.last_update_login = g_login_id
2572 , o.program_id = g_program_id
2573 , o.program_login_id = g_program_login_id
2574 , o.program_application_id = g_program_application_id
2575 , o.request_id = g_request_id
2576 when not matched then
2577 insert
2578 ( task_id
2579 , owner_id
2580 -- R12 resource type impact
2581 , owner_type
2582 , district_id
2583 , object_capacity_id
2584 , capacity_date
2585 , capacity_hours
2586 , blocked_trip_flag
2587 , deleted_flag
2588 , created_by
2589 , creation_date
2590 , last_updated_by
2591 , last_update_date
2592 , last_update_login
2593 )
2594 values
2595 ( n.task_id
2596 , n.owner_id
2597 -- R12 resource type impact
2598 , n.owner_type
2599 , n.district_id
2600 , n.object_capacity_id
2601 , n.capacity_date
2602 , n.capacity_hours
2603 , n.blocked_trip_flag
2604 , n.deleted_flag
2605 , g_user_id
2606 , sysdate
2607 , g_user_id
2608 , sysdate
2609 , g_login_id
2610 );
2611 -- R12 dep/arr
2612
2613 l_temp_rowcount := sql%rowcount;
2614
2615 bis_collection_utilities_log( l_temp_rowcount || ' rows merged into capacity base summary', 1 );
2616
2617 l_rowcount := l_rowcount + l_temp_rowcount;
2618
2619 -- delete processed rows from events table
2620 l_stmt_id := 100;
2621 delete from isc_fs_events
2622 where rowid in ( select event_rowid from isc_fs_events_stg where source = 1 );
2623
2624 l_temp_rowcount := sql%rowcount;
2625
2626 bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from events table', 1 );
2627
2628 -- delete processed rows from party merge events table
2629 l_stmt_id := 110;
2630 delete from isc_fs_party_merge_events
2631 where rowid in ( select event_rowid from isc_fs_events_stg where source = 2 );
2632
2633 l_temp_rowcount := sql%rowcount;
2634
2635 bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from party merge events table', 1 );
2636
2637 commit;
2638
2639 bis_collection_utilities_log( 'Cleaning up..', 1 );
2640
2641 -- attempt (no fail) to truncate party merge events table is zero rows
2642 l_stmt_id := 120;
2643 begin
2644
2645 lock table isc_fs_party_merge_events in exclusive mode nowait;
2646
2647 select count(*)
2648 into l_temp_rowcount
2649 from isc_fs_party_merge_events;
2650
2651 if l_temp_rowcount = 0 then
2652 if truncate_table
2653 ( l_isc_schema
2654 , 'ISC_FS_PARTY_MERGE_EVENTS'
2655 , l_error_message ) <> 0 then
2656 logger( l_proc_name, l_stmt_id, l_error_message );
2657 raise l_exception;
2658 end if;
2659 bis_collection_utilities_log( 'Party merge events table truncated', 2 );
2660 else
2661 bis_collection_utilities_log( l_temp_rowcount || ' new unprocessed rows party merge events table', 2 );
2662 end if;
2663
2664 exception
2665 when l_exception then
2666 raise l_exception;
2667 when l_resource_busy then
2668 bis_collection_utilities_log( 'Unable to lock party merge events table at this time', 2 );
2669 when others then
2670 raise;
2671 end;
2672
2673 commit;
2674
2675 -- house keeping -- cleanup staging table
2676 l_stmt_id := 130;
2677 if truncate_table
2678 ( l_isc_schema
2679 , 'ISC_FS_EVENTS_STG'
2680 , l_error_message ) <> 0 then
2681 logger( l_proc_name, l_stmt_id, l_error_message );
2682 raise l_exception;
2683 end if;
2684
2685 bis_collection_utilities_log( 'Staging table truncated', 2 );
2686
2687 l_stmt_id := 140;
2688 bis_collection_utilities.wrapup( p_status => true
2689 , p_period_from => l_collect_from_date
2690 , p_period_to => l_collect_to_date
2691 , p_count => l_rowcount
2692 , p_attribute1 => g_ttr_ftf_rule
2693 );
2694
2695 bis_collection_utilities_log('End Incremental Load');
2696
2697 errbuf := null;
2698 retcode := g_success;
2699
2700 exception
2701 when g_bis_setup_exception then
2702 rollback;
2703 errbuf := l_error_message;
2704 retcode := g_error;
2705 bis_collection_utilities_log('End Incremential Load with Error');
2706
2707 when l_exception then
2708 rollback;
2709 if l_error_message is null then
2710 l_error_message := substr(sqlerrm,1,4000);
2711 end if;
2712 bis_collection_utilities.wrapup( p_status => false
2713 , p_message => l_error_message
2714 , p_period_from => l_collect_from_date
2715 , p_period_to => l_collect_to_date
2716 );
2717 errbuf := l_error_message;
2718 retcode := g_error;
2719 bis_collection_utilities_log('End Incremential Load with Error');
2720
2721 when others then
2722 rollback;
2723 if l_error_message is null then
2724 l_error_message := substr(sqlerrm,1,4000);
2725 end if;
2726 logger( l_proc_name, l_stmt_id, l_error_message );
2727 bis_collection_utilities.wrapup( p_status => false
2728 , p_message => l_error_message
2729 , p_period_from => l_collect_from_date
2730 , p_period_to => l_collect_to_date
2731 );
2732 errbuf := l_error_message;
2733 retcode := g_error;
2734 bis_collection_utilities_log('End Incremential Load with Error');
2735
2736 end incremental_load;
2737
2738 end isc_fs_task_etl_pkg;