[Home] [Help]
PACKAGE BODY: APPS.ISC_FS_TASK_ACT_BAC_ETL_PKG
Source
1 package body isc_fs_task_act_bac_etl_pkg
2 /* $Header: iscfsactbacetlb.pls 120.4 2005/11/24 18:29:59 kreardon noship $ */
3 as
4
5 g_pkg_name constant varchar2(30) := 'isc_fs_task_act_bac_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_max_date constant date := to_date('4712/12/31','yyyy/mm/dd');
18
19 procedure bis_collection_utilities_log
20 ( m varchar2, indent number default null )
21 as
22 begin
23
24 --if indent is not null then
25 -- for i in 1..indent loop
26 -- dbms_output.put('__');
27 -- end loop;
28 --end if;
29 --dbms_output.put_line(substr(m,1,254));
30
31 bis_collection_utilities.log( substr(m,1,2000-(nvl(indent,0)*3)), nvl(indent,0) );
32
33 end bis_collection_utilities_log;
34
35 procedure local_init
36 as
37 begin
38 g_user_id := fnd_global.user_id;
39 g_login_id := fnd_global.login_id;
40 g_global_start_date := bis_common_parameters.get_global_start_date;
41 g_program_id := fnd_global.conc_program_id;
42 g_program_login_id := fnd_global.conc_login_id;
43 g_program_application_id := fnd_global.prog_appl_id;
44 g_request_id := fnd_global.conc_request_id;
45 end local_init;
46
47 procedure logger
48 ( p_proc_name varchar2
49 , p_stmt_id number
50 , p_message varchar2
51 )
52 as
53 begin
54 bis_collection_utilities_log( g_pkg_name || '.' || p_proc_name ||
55 ' #' || p_stmt_id || ' ' ||
56 p_message
57 , 3 );
58 end logger;
59
60 function get_schema_name
61 ( x_schema_name out nocopy varchar2
62 , x_error_message out nocopy varchar2 )
63 return number as
64
65 l_isc_schema varchar2(30);
66 l_status varchar2(30);
67 l_industry varchar2(30);
68
69 begin
70
71 if fnd_installation.get_app_info('ISC', l_status, l_industry, l_isc_schema) then
72 x_schema_name := l_isc_schema;
73 else
74 x_error_message := 'FND_INSTALLATION.GET_APP_INFO returned false';
75 return -1;
76 end if;
77
78 return 0;
79
80 exception
81 when others then
82 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
83 return -1;
84
85 end get_schema_name;
86
87 function truncate_table
88 ( p_isc_schema in varchar2
89 , p_table_name in varchar2
90 , x_error_message out nocopy varchar2 )
91 return number as
92
93 begin
94
95 execute immediate 'truncate table ' || p_isc_schema || '.' || p_table_name;
96
97 return 0;
98
99 exception
100 when others then
101 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
102 return -1;
103
104 end truncate_table;
105
106 function gather_statistics
107 ( p_isc_schema in varchar2
108 , p_table_name in varchar2
109 , x_error_message out nocopy varchar2 )
110 return number as
111
112 begin
113
114 fnd_stats.gather_table_stats( ownname => p_isc_schema
115 , tabname => p_table_name
116 );
117
118 return 0;
119
120 exception
121 when others then
122 x_error_message := 'Error in function gather_statistics : ' || sqlerrm;
123 return -1;
124
125 end gather_statistics;
126
127 function get_last_refresh_date
128 ( x_refresh_date out nocopy date
129 , x_error_message out nocopy varchar2
130 , p_object_name in varchar2 default null
131 )
132 return number as
133
134 l_refresh_date date;
135
136 begin
137
138 l_refresh_date := fnd_date.displaydt_to_date
139 ( bis_collection_utilities.get_last_refresh_period
140 ( nvl(p_object_name,g_object_name) )
141 );
142 if l_refresh_date = g_global_start_date then
143 x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
144 return -1;
145 end if;
146
147 x_refresh_date := l_refresh_date;
148 return 0;
149
150 exception
151 when others then
152 x_error_message := 'Error in function get_last_refresh_date : ' || sqlerrm;
153 return -1;
154
155 end get_last_refresh_date;
156
157 -- -------------------------------------------------------------------
158 -- PUBLIC PROCEDURES
159 -- -------------------------------------------------------------------
160 procedure initial_load
161 ( errbuf out nocopy varchar2
162 , retcode out nocopy number
163 )
164 as
165
166 l_proc_name constant varchar2(30) := 'initial_load';
167 l_stmt_id number;
168 l_exception exception;
169 l_error_message varchar2(4000);
170 l_isc_schema varchar2(100);
171
172 l_timer number;
173 l_rowcount number;
174 l_temp_rowcount number;
175
176 l_collect_from_date date;
177 l_collect_to_date date;
178
179 begin
180
181 local_init;
182
183 bis_collection_utilities_log( 'Begin Initial Load' );
184
185 l_stmt_id := 0;
186 if not bis_collection_utilities.setup( g_object_name ) then
187 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
188 logger( l_proc_name, l_stmt_id, l_error_message );
189 raise g_bis_setup_exception;
190 end if;
191
192 -- check global start date
193 l_stmt_id := 10;
194 if g_global_start_date is null then
195 l_error_message := 'Unable to get DBI global start date.'; -- translatable message?
196 logger( l_proc_name, l_stmt_id, l_error_message );
197 raise l_exception;
198 end if;
199
200 l_collect_from_date := g_global_start_date;
201
202 -- determine the date we last collected to
203 l_stmt_id := 20;
204 if get_last_refresh_date
205 ( l_collect_to_date
206 , l_error_message
207 , isc_fs_task_etl_pkg.g_object_name ) <> 0 then
208 logger( l_proc_name, l_stmt_id, l_error_message );
209 raise l_exception;
210 end if;
211
212 bis_collection_utilities_log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
213 bis_collection_utilities_log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
214
215 -- get the isc schema name
216 l_stmt_id := 30;
217 if get_schema_name
218 ( l_isc_schema
219 , l_error_message ) <> 0 then
220 logger( l_proc_name, l_stmt_id, l_error_message );
221 raise l_exception;
222 end if;
223
224 -- truncate the isc_fs_task_activity_f fact table
225 l_stmt_id := 40;
226 if truncate_table
227 ( l_isc_schema
228 , 'ISC_FS_TASK_ACTIVITY_F'
229 , l_error_message ) <> 0 then
230 logger( l_proc_name, l_stmt_id, l_error_message );
231 raise l_exception;
232 end if;
233
234 bis_collection_utilities_log( 'Task Activity base summary table truncated', 1 );
235
236 -- truncate the isc_fs_task_backlog_f fact table
237 l_stmt_id := 50;
238 if truncate_table
239 ( l_isc_schema
240 , 'ISC_FS_TASK_BACKLOG_F'
241 , l_error_message ) <> 0 then
242 logger( l_proc_name, l_stmt_id, l_error_message );
243 raise l_exception;
244 end if;
245
246 bis_collection_utilities_log( 'Task Backlog base summary table truncated', 1 );
247
248 -- insert into base fact tables
249 l_stmt_id := 60;
250
251 insert /*+ append
252 parallel(isc_fs_task_activity_f)
253 parallel(isc_fs_task_backlog_f)
254 */
255 ALL
256 when 1 in (first_opened, reopened, closed) then
257 into isc_fs_task_activity_f
258 ( task_id
259 , task_audit_id
260 , activity_date
261 , first_opened
262 , reopened
263 , closed
264 , created_by
265 , creation_date
266 , last_updated_by
267 , last_update_date
268 , last_update_login
269 , program_id
270 , program_login_id
271 , program_application_id
272 , request_id
273 -- denomalized columns
274 , source_object_type_code
275 , task_type_id
276 , task_type_rule
277 , deleted_flag
278 , act_bac_assignee_id
279 -- R12 resource type impact
280 , act_bac_assignee_type
281 , act_bac_district_id
282 , inventory_item_id
283 , inv_organization_id
284 , customer_id
285 -- denomalized columns
286 )
287 values
288 ( task_id
289 , task_audit_id
290 , activity_date
291 , first_opened
292 , reopened
293 , closed
294 , g_user_id
295 , sysdate
296 , g_user_id
297 , sysdate
298 , g_login_id
299 , g_program_id
300 , g_program_login_id
301 , g_program_application_id
302 , g_request_id
303 -- denomalized columns
304 , source_object_type_code
305 , task_type_id
306 , task_type_rule
307 , deleted_flag
308 , act_bac_assignee_id
309 -- R12 resource type impact
310 , act_bac_assignee_type
311 , act_bac_district_id
312 , inventory_item_id
313 , inv_organization_id
314 , customer_id
315 -- denomalized columns
316 )
317 when backlog_date_from is not null then
318 into isc_fs_task_backlog_f
319 ( task_id
320 , task_audit_id
321 , backlog_date_from
322 , backlog_date_to
323 , backlog_status_code
324 , created_by
325 , creation_date
326 , last_updated_by
327 , last_update_date
328 , last_update_login
329 , program_id
330 , program_login_id
331 , program_application_id
332 , request_id
333 -- denomalized columns
334 , source_object_type_code
335 , task_type_id
336 , task_type_rule
337 , deleted_flag
338 , act_bac_assignee_id
339 -- R12 resource type impact
340 , act_bac_assignee_type
341 , act_bac_district_id
342 , inventory_item_id
343 , inv_organization_id
344 , customer_id
345 , planned_start_date
346 -- denomalized columns
347 )
348 values
349 ( task_id
350 , task_audit_id
351 , backlog_date_from
352 , backlog_date_to
353 , backlog_status_code
354 , g_user_id
355 , sysdate
356 , g_user_id
357 , sysdate
358 , g_login_id
359 , g_program_id
360 , g_program_login_id
361 , g_program_application_id
362 , g_request_id
363 -- denomalized columns
364 , source_object_type_code
365 , task_type_id
366 , task_type_rule
367 , deleted_flag
368 , act_bac_assignee_id
369 -- R12 resource type impact
370 , act_bac_assignee_type
371 , act_bac_district_id
372 , inventory_item_id
373 , inv_organization_id
374 , customer_id
375 , planned_start_date
376 -- denomalized columns
377 )
378 select /*+ parallel(a)
379 parallel(s_new)
380 parallel(s_old)
381 parallel(e_act)
382 use_nl(s_new,s_old)
383 */
384 a.task_id
385 , a.task_audit_id
386 , trunc(a.audit_date) activity_date
387 , decode( a.task_audit_id
388 , -1, 1
389 , null ) first_opened
390 , case
391 when a.task_audit_id < 0 then
392 null
393 when nvl(s_new.closed_flag,'N') = 'N' and
394 nvl(s_old.closed_flag,'N') = 'Y' then
395 1
396 else
397 null
398 end reopened
399 , case
400 when a.task_audit_id = -2 then
401 null
402 when a.task_audit_id = -1 and
403 nvl(s_new.closed_flag,'N') = 'Y' then
404 1
405 when nvl(s_new.closed_flag,'N') = 'Y' and
406 nvl(s_old.closed_flag,'N') = 'N' then
407 1
408 else
409 null
410 end closed
411 , case
412 when nvl(s_new.closed_flag,'N') = 'N' then
413 case
414 -- note: the sequence of the "when" is important, don't change it!
415 when nvl(s_new.schedulable_flag,'N') = 'Y' or
416 trunc(a.audit_date) < trunc(nvl(a.first_asgn_creation_date,g_max_date)) then
417 1 --'IN PLANNING'
418 when nvl(s_new.working_flag,'N') = 'Y' then
419 3 --'WORKING'
420 when nvl(s_new.assigned_flag,'N') = 'Y' then
421 2 --'ASSIGNED'
422 when nvl(s_new.completed_flag,'N') = 'Y' then
423 4 -- 'COMPLETED'
424 else
425 5 -- 'OTHER'
426 end
427 else
428 null
429 end backlog_status_code
430 , case
431 when last_row_for_day_flag = 'Y' and
432 nvl(s_new.closed_flag,'N') = 'N' then
433 trunc(a.audit_date)
434 else null
435 end backlog_date_from
436 , case
437 when last_row_for_day_flag = 'Y' and
438 nvl(s_new.closed_flag,'N') = 'N' then
439 lead(trunc(a.audit_date)-1,1,g_max_date) over(partition by task_id order by a.audit_date, a.task_audit_id)
440 else null
441 end backlog_date_to
442 -- denomalized columns
443 , source_object_type_code
444 , task_type_id
445 , task_type_rule
446 , deleted_flag
447 , act_bac_assignee_id
448 -- R12 resource type impact
449 , act_bac_assignee_type
450 , act_bac_district_id
451 , inventory_item_id
452 , inv_organization_id
453 , customer_id
454 , planned_start_date
455 -- denomalized columns
456 from
457 ( --
458 -- this selects audits (including initial creation) for all
459 -- tasks of interest where the task was created on or after
460 -- GSD and all audit rows since GSD for tasks of interest
461 -- created before GSD
462 select /*+ no_merge parallel(x)
463 */
464 task_id
465 , task_audit_id
466 , audit_date
467 , case
468 when task_audit_id < 0 then
469 lead(old_task_status_id,1,task_status_id)
470 over(partition by task_id order by audit_date, task_audit_id)
471 else
472 task_status_id
473 end task_status_id
474 , case
475 when task_audit_id = -1 then
476 -1
477 else
478 old_task_status_id
479 end old_task_status_id
480 , first_asgn_creation_date
481 , decode( row_number()
482 over(partition by task_id, trunc(audit_date) order by audit_date desc, task_audit_id desc)
483 , 1, 'Y'
484 , 'N' ) last_row_for_day_flag
485 -- denomalized columns
486 , source_object_type_code
487 , task_type_id
488 , task_type_rule
489 , deleted_flag
490 , act_bac_assignee_id
491 -- R12 resource type impact
492 , act_bac_assignee_type
493 , act_bac_district_id
494 , inventory_item_id
495 , inv_organization_id
496 , customer_id
497 , planned_start_date
498 -- denomalized columns
499 from
500 ( --
501 -- this query selects the current state of all tasks based on the
502 -- data that was collected into isc_fs_tasks_f.
503 -- the row from this query will be the marker
504 -- 1. for the initial row for tasks created after GSD or
505 -- 2. for the beginning row tasks created before GSD that are
506 -- included in the beginning backlog
507 --
508 select /*+ parallel(t) no_merge
509 */
510 t.task_id
511 , case
512 when t.task_creation_date < l_collect_from_date then
513 -2
514 else
515 -1
516 end task_audit_id
517 , case
518 when t.task_creation_date < l_collect_from_date then
519 l_collect_from_date
520 else
521 t.task_creation_date
522 end audit_date
523 , t.task_status_id old_task_status_id
524 , t.task_status_id
525 , t.first_asgn_creation_date
526 -- denomalized columns
527 , t.source_object_type_code
528 , t.task_type_id
529 , t.task_type_rule
530 , t.deleted_flag
531 , t.act_bac_assignee_id
532 -- R12 resource type impact
533 , t.act_bac_assignee_type
534 , t.act_bac_district_id
535 , t.inventory_item_id
536 , t.inv_organization_id
537 , t.customer_id
538 , t.planned_start_date
539 -- denomalized columns
540 from
541 isc_fs_tasks_f t
542 where
543 t.source_object_type_code = 'SR'
544 -- don't restrict to just rule of 'DISPATCH' as
545 -- could subsequently change type and we would
546 -- miss out on the initial backlog/activity
547 -- and t.task_type_rule = 'DISPATCH'
548 and t.task_creation_date <= l_collect_to_date
549 and nvl(t.task_split_flag,'N') in ('N','M')
550 --
551 union all
552 --
553 -- this query selects all rows from the task audit table
554 -- for tasks that were collected into isc_fs_tasks_f.
555 --
556 -- only include audits created between GSD and the end date
557 -- of the load to isc_fs_tasks_f.
558 --
559 -- the first row for an audit may ne consumed twice, once
560 -- for the initial values for the task and again for the
561 -- new values (the change).
562 --
563 select /*+ ordered
564 parallel(t)
565 parallel(a)
566 use_hash(a)
567 pq_distribute(a,hash,hash)
568 */
569 a.task_id
570 , a.task_audit_id
571 , a.creation_date audit_date
572 , a.old_task_status_id
573 , a.new_task_status_id
574 , t.first_asgn_creation_date
575 -- denomalized columns
576 , t.source_object_type_code
577 , t.task_type_id
578 , t.task_type_rule
579 , t.deleted_flag
580 , t.act_bac_assignee_id
581 -- R12 resource type impact
582 , t.act_bac_assignee_type
583 , t.act_bac_district_id
584 , t.inventory_item_id
585 , t.inv_organization_id
586 , t.customer_id
587 , t.planned_start_date
588 -- denomalized columns
589 from
590 isc_fs_tasks_f t
591 , jtf_task_audits_b a
592 where
593 t.task_id = a.task_id
594 and t.source_object_type_code = 'SR'
595 -- don't restrict to just rule of 'DISPATCH' as
596 -- could subsequently change type and we would
597 -- miss out on the initial backlog/activity
598 -- and t.task_type_rule = 'DISPATCH'
599 and a.creation_date >= l_collect_from_date
600 and a.creation_date+0 <= l_collect_to_date
601 and nvl(t.task_split_flag,'N') in ('N','M')
602 ) x
603 ) a
604 , jtf_task_statuses_b s_old
605 , jtf_task_statuses_b s_new
606 where
607 a.task_status_id = s_new.task_status_id
608 and a.old_task_status_id = s_old.task_status_id(+);
609
610 l_rowcount := sql%rowcount;
611
612 bis_collection_utilities_log( l_rowcount || ' rows inserted into base summaries', 1 );
613
614 commit;
615
616 l_stmt_id := 70;
617 bis_collection_utilities.wrapup( p_status => true
618 , p_period_from => l_collect_from_date
619 , p_period_to => l_collect_to_date
620 , p_count => l_rowcount
621 );
622
623 bis_collection_utilities_log('End Initial Load');
624
625 errbuf := null;
626 retcode := g_success;
627
628 exception
629 when g_bis_setup_exception then
630 rollback;
631 errbuf := l_error_message;
632 retcode := g_error;
633 bis_collection_utilities_log('End Initial Load with Error');
634
635 when l_exception then
636 rollback;
637 if l_error_message is null then
638 l_error_message := substr(sqlerrm,1,4000);
639 end if;
640 bis_collection_utilities.wrapup( p_status => false
641 , p_message => l_error_message
642 , p_period_from => l_collect_from_date
643 , p_period_to => l_collect_to_date
644 );
645 errbuf := l_error_message;
646 retcode := g_error;
647 bis_collection_utilities_log('End Initial Load with Error');
648
649 when others then
650 rollback;
651 if l_error_message is null then
652 l_error_message := substr(sqlerrm,1,4000);
653 end if;
654 logger( l_proc_name, l_stmt_id, l_error_message );
655 bis_collection_utilities.wrapup( p_status => false
656 , p_message => l_error_message
657 , p_period_from => l_collect_from_date
658 , p_period_to => l_collect_to_date
659 );
660 errbuf := l_error_message;
661 retcode := g_error;
662 bis_collection_utilities_log('End Initial Load with Error');
663
664 end initial_load;
665
666 procedure incremental_load
667 ( errbuf out nocopy varchar2
668 , retcode out nocopy number
669 )
670 as
671
672 type t_rowid_tbl is table of rowid;
673 type t_date_tbl is table of date;
674
675 l_proc_name constant varchar2(30) := 'incremental_load';
676 l_stmt_id number;
677 l_exception exception;
678 l_error_message varchar2(4000);
679 l_isc_schema varchar2(100);
680
681 l_timer number;
682 l_rowcount number;
683 l_temp_rowcount number;
684
685 l_collect_from_date date;
686 l_collect_to_date date;
687
688 l_rowid_tbl t_rowid_tbl;
689 l_backlog_date_to t_date_tbl;
690
691 cursor c_updated is
692 select
693 task_id
694 , source_object_type_code
695 , task_type_id
696 , task_type_rule
697 , deleted_flag
698 , act_bac_assignee_id
699 -- R12 resource type impact
700 , act_bac_assignee_type
701 , act_bac_district_id
702 , inventory_item_id
703 , inv_organization_id
704 , customer_id
705 , planned_start_date
706 from isc_fs_tasks_f
707 where last_update_date >= l_collect_from_date;
708
709 begin
710
711 local_init;
712
713 bis_collection_utilities_log( 'Begin Incremental Load' );
714
715 l_stmt_id := 0;
716 if not bis_collection_utilities.setup( g_object_name ) then
717 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
718 logger( l_proc_name, l_stmt_id, l_error_message );
719 raise g_bis_setup_exception;
720 end if;
721
722 -- determine the date we last collected to
723 l_stmt_id := 10;
724 if get_last_refresh_date
725 ( l_collect_to_date
726 , l_error_message
727 ) <> 0 then
728 logger( l_proc_name, l_stmt_id, l_error_message );
729 raise l_exception;
730 end if;
731 l_collect_from_date := l_collect_to_date + 1/86400;
732
733 -- determine the date that we last collected tasks/assignments to
734 l_stmt_id := 20;
735 if get_last_refresh_date
736 ( l_collect_to_date
737 , l_error_message
738 , isc_fs_task_etl_pkg.g_object_name
739 ) <> 0 then
740 logger( l_proc_name, l_stmt_id, l_error_message );
741 raise l_exception;
742 end if;
743
744 bis_collection_utilities_log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
745 bis_collection_utilities_log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
746
747 if l_collect_from_date >= l_collect_to_date then
748
749 bis_collection_utilities_log( 'Nothing to process', 2 );
750 bis_collection_utilities.wrapup( p_status => true
751 , p_period_from => l_collect_from_date
752 , p_period_to => l_collect_to_date
753 , p_count => 0
754 );
755
756 bis_collection_utilities_log('End Incremental Load');
757 errbuf := null;
758 retcode := g_success;
759 return;
760 end if;
761
762 -- get the isc schema name
763 l_stmt_id := 30;
764 if get_schema_name
765 ( l_isc_schema
766 , l_error_message ) <> 0 then
767 logger( l_proc_name, l_stmt_id, l_error_message );
768 raise l_exception;
769 end if;
770
771 -- truncate the staging table
772 l_stmt_id := 40;
773 if truncate_table
774 ( l_isc_schema
775 , 'ISC_FS_TASK_ACT_BAC_STG'
776 , l_error_message ) <> 0 then
777 logger( l_proc_name, l_stmt_id, l_error_message );
778 raise l_exception;
779 end if;
780
781 bis_collection_utilities_log( 'Staging table truncated', 1 );
782
783 bis_collection_utilities_log( 'Inserting Task audit history into staging table', 1 );
784
785 --
786 -- insert rows based on tasks created or tasks updated
787 --
788 l_stmt_id := 50;
789 insert into isc_fs_task_act_bac_stg
790 ( task_id
791 , task_audit_id
792 , status_flag
793 , audit_date
794 , first_opened
795 , reopened
796 , closed
797 , last_row_for_day_flag
798 , backlog_status_code
799 , created_by
800 , creation_date
801 , last_updated_by
802 , last_update_date
803 , last_update_login
804 , program_id
805 , program_login_id
806 , program_application_id
807 , request_id
808 -- denomalized columns
809 , source_object_type_code
810 , task_type_id
811 , task_type_rule
812 , deleted_flag
813 , act_bac_assignee_id
814 -- R12 resource type impact
815 , act_bac_assignee_type
816 , act_bac_district_id
817 , inventory_item_id
818 , inv_organization_id
819 , customer_id
820 , planned_start_date
821 -- denomalized columns
822 )
823 /* These hints are given assuming the num of rows from ISC_FS_TASKS_F with
824 the last_update_date filter would be in the range 3000 - 6000 */
825 select /*+ ordered use_nl(s_new,s_old) */
826 a.task_id
827 , a.task_audit_id
828 , decode( nvl(s_new.closed_flag,'N')
829 , 'N', 'O'
830 , 'C' ) status_flag
831 , a.audit_date
832 , decode( a.task_audit_id
833 , -1, 1
834 , null ) first_opened
835 , case
836 when a.task_audit_id < 0 then
837 null
838 when nvl(s_new.closed_flag,'N') = 'N' and
839 nvl(s_old.closed_flag,'N') = 'Y' then
840 1
841 else
842 null
843 end reopened
844 , case
845 when a.task_audit_id = -2 then
846 null
847 when a.task_audit_id = -1 and
848 nvl(s_new.closed_flag,'N') = 'Y' then
849 1
850 when nvl(s_new.closed_flag,'N') = 'Y' and
851 nvl(s_old.closed_flag,'N') = 'N' then
852 1
853 else
854 null
855 end closed
856 , last_row_for_day_flag
857 , case
858 when nvl(s_new.closed_flag,'N') = 'N' then
859 case
860 -- note: the sequence of the "when" is important, don't change it!
861 when nvl(s_new.schedulable_flag,'N') = 'Y' or
862 trunc(a.audit_date) < trunc(nvl(a.first_asgn_creation_date,g_max_date)) then
863 1 --'IN PLANNING' -- in planning
864 when nvl(s_new.working_flag,'N') = 'Y' then
865 3 --'WORKING' -- working
866 when nvl(s_new.assigned_flag,'N') = 'Y' then
867 2 --'ASSIGNED' -- assigned
868 when nvl(s_new.completed_flag,'N') = 'Y' then
869 4 --'COMPLETED' -- completed
870 else
871 5 --'OTHER' -- others
872 end
873 else
874 null
875 end backlog_status_code
876 , g_user_id
877 , sysdate
878 , g_user_id
879 , sysdate
880 , g_login_id
881 , g_program_id
882 , g_program_login_id
883 , g_program_application_id
884 , g_request_id
885 -- denomalized columns
886 , a.source_object_type_code
887 , a.task_type_id
888 , a.task_type_rule
889 , a.deleted_flag
890 , a.act_bac_assignee_id
891 -- R12 resource type impact
892 , a.act_bac_assignee_type
893 , a.act_bac_district_id
894 , a.inventory_item_id
895 , a.inv_organization_id
896 , a.customer_id
897 , a.planned_start_date
898 -- denomalized columns
899 from
900 ( --
901 -- this selects audits (including initial creation) for all
902 -- tasks updated since last collection based on isc_fs_tasks_f
903 select
904 task_id
905 , task_audit_id
906 , audit_date
907 , case
908 when task_audit_id < 0 then
909 lead(old_task_status_id,1,task_status_id)
910 over(partition by task_id order by audit_date, task_audit_id)
911 else
912 task_status_id
913 end task_status_id
914 , case
915 when task_audit_id = -1 then
916 -1
917 else
918 old_task_status_id
919 end old_task_status_id
920 , first_asgn_creation_date
921 , decode( row_number()
922 over( partition by task_id, trunc(audit_date)
923 order by audit_date desc, task_audit_id desc)
924 , 1, 'Y'
925 , 'N' ) last_row_for_day_flag
926 -- denomalized columns
927 , source_object_type_code
928 , task_type_id
929 , task_type_rule
930 , deleted_flag
931 , act_bac_assignee_id
932 -- R12 resource type impact
933 , act_bac_assignee_type
934 , act_bac_district_id
935 , inventory_item_id
936 , inv_organization_id
937 , customer_id
938 , planned_start_date
939 -- denomalized columns
940 from
941 ( --
942 -- this query selects the current state of all tasks based on the
943 -- data that was collected into isc_fs_tasks_f.
944 -- the row from this query will be the marker for the initial row
945 -- for tasks created since last collection
946 --
947 select
948 t.task_id
949 , -1 task_audit_id
950 , t.task_creation_date audit_date
951 , t.task_status_id old_task_status_id
952 , t.task_status_id
953 , t.first_asgn_creation_date
954 -- denomalized columns
955 , t.source_object_type_code
956 , t.task_type_id
957 , t.task_type_rule
958 , t.deleted_flag
959 , t.act_bac_assignee_id
960 -- R12 resource type impact
961 , t.act_bac_assignee_type
962 , t.act_bac_district_id
963 , t.inventory_item_id
964 , t.inv_organization_id
965 , t.customer_id
966 , t.planned_start_date
967 -- denomalized columns
968 from
969 isc_fs_tasks_f t
970 where
971 t.last_update_date >= l_collect_from_date
972 and t.task_creation_date >= l_collect_from_date
973 and nvl(t.task_split_flag,'N') in ('N','M')
974 --
975 union all
976 --
977 -- this query selects rows from the task audit table created between
978 -- last collection of activty/backlog and last collection of
979 -- isc_fs_tasks_f for tasks that were updated in isc_fs_tasks_f
980 -- since last collection of activty/backlog.
981 --
982 select /*+ ordered use_nl(A) */
983 a.task_id
984 , a.task_audit_id
985 , a.creation_date audit_date
986 , a.old_task_status_id
987 , a.new_task_status_id
988 , t.first_asgn_creation_date
989 -- denomalized columns
990 , t.source_object_type_code
991 , t.task_type_id
992 , t.task_type_rule
993 , t.deleted_flag
994 , t.act_bac_assignee_id
995 -- R12 resource type impact
996 , t.act_bac_assignee_type
997 , t.act_bac_district_id
998 , t.inventory_item_id
999 , t.inv_organization_id
1000 , t.customer_id
1001 , t.planned_start_date
1002 -- denomalized columns
1003 from
1004 isc_fs_tasks_f t
1005 , jtf_task_audits_b a
1006 where
1007 t.task_id = a.task_id
1008 and t.last_update_date >= l_collect_from_date
1009 and nvl(t.task_split_flag,'N') in ('N','M')
1010 and a.creation_date >= l_collect_from_date
1011 and a.creation_date <= l_collect_to_date
1012 ) x
1013 ) a
1014 , jtf_task_statuses_b s_old
1015 , jtf_task_statuses_b s_new
1016 where
1017 a.task_status_id = s_new.task_status_id
1018 and a.old_task_status_id = s_old.task_status_id(+);
1019
1020 --
1021
1022 l_rowcount := sql%rowcount;
1023
1024 bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1025
1026 commit;
1027
1028 bis_collection_utilities_log( 'Inserting beginning task backlog into staging table', 1 );
1029
1030 -- insert a row for each task in the latest backlog
1031 -- this row will later be compared with the subsequent
1032 -- last row for day for the same task to determine if
1033 -- it needs to be closed off
1034 --
1035 l_stmt_id := 60;
1036 insert into isc_fs_task_act_bac_stg
1037 ( task_id
1038 , task_audit_id
1039 , backlog_status_code
1040 , status_flag
1041 , audit_date
1042 , backlog_rowid
1043 , last_row_for_day_flag
1044 , created_by
1045 , creation_date
1046 , last_updated_by
1047 , last_update_date
1048 , last_update_login
1049 , program_id
1050 , program_login_id
1051 , program_application_id
1052 , request_id
1053 -- denomalized columns
1054 , source_object_type_code
1055 , task_type_id
1056 , task_type_rule
1057 , deleted_flag
1058 , act_bac_assignee_id
1059 -- R12 resource type impact
1060 , act_bac_assignee_type
1061 , act_bac_district_id
1062 , inventory_item_id
1063 , inv_organization_id
1064 , customer_id
1065 , planned_start_date
1066 -- denomalized columns
1067 )
1068 select
1069 b.task_id
1070 , b.task_audit_id
1071 , b.backlog_status_code
1072 , 'O'
1073 , b.backlog_date_from
1074 , b.rowid
1075 , 'Y'
1076 , g_user_id
1077 , sysdate
1078 , g_user_id
1079 , sysdate
1080 , g_login_id
1081 , g_program_id
1082 , g_program_login_id
1083 , g_program_application_id
1084 , g_request_id
1085 -- denomalized columns
1086 , t.source_object_type_code
1087 , t.task_type_id
1088 , t.task_type_rule
1089 , t.deleted_flag
1090 , t.act_bac_assignee_id
1091 -- R12 resource type impact
1092 , t.act_bac_assignee_type
1093 , t.act_bac_district_id
1094 , t.inventory_item_id
1095 , t.inv_organization_id
1096 , t.customer_id
1097 , t.planned_start_date
1098 -- denomalized columns
1099 from
1100 isc_fs_task_backlog_f b
1101 , isc_fs_tasks_f t
1102 where
1103 b.backlog_date_to = g_max_date
1104 and b.task_id = t.task_id;
1105
1106 l_rowcount := sql%rowcount;
1107
1108 bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1109
1110 commit;
1111
1112 -- gather stats on staging table
1113 l_stmt_id := 70;
1114 if gather_statistics
1115 ( l_isc_schema
1116 , 'ISC_FS_TASK_ACT_BAC_STG'
1117 , l_error_message ) <> 0 then
1118 logger( l_proc_name, l_stmt_id, l_error_message );
1119 raise l_exception;
1120 end if;
1121
1122 bis_collection_utilities_log( 'Gathered statistics on staging table', 1 );
1123
1124 -- we need to deal with the case where the task has had a task assignement
1125 -- added or cancelled (even deleted) after the last audit row for that task.
1126 -- not sure how likely this is as testing has shown that task is updated when
1127 -- assignment is changed but making sure.
1128 --
1129 -- the issue is that backlog_status_code is based on both task_status_id (flags)
1130 -- and whether or not the task has assignments. task_status_id is picked up
1131 -- by audit rows, task having/not having assignments is not.
1132 --
1133 -- for each task in the audit table we find the "last" row, if that task is open
1134 -- and the date of that row is not the same as the collection, we compare
1135 -- backlog_status_code of that row with backlog_status_code calculated based
1136 -- on isc_fs_tasks_f. if they differ we insert a "dummy" audit row.
1137 --
1138 -- the most likely (but not highly likely as above) reason that they might differ
1139 -- is that at the time of the last audit row the backlog_status_code was IN PLANNING
1140 -- bacause there were no assignments, otherwise it would have been WORKING etc.
1141 -- by adding an assignment to the task we need to recompute backlog_status_code.
1142 -- the reverse hold true, if the task assignment is cancelled, we may need to
1143 -- move backlog_status_code from WORKING etc back to IN PLANNING.
1144
1145 bis_collection_utilities_log( 'Inserting closing backlog status into staging table', 1 );
1146
1147 l_stmt_id := 80;
1148 insert into isc_fs_task_act_bac_stg
1149 ( task_id
1150 , task_audit_id
1151 , backlog_status_code
1152 , status_flag
1153 , audit_date
1154 , last_row_for_day_flag
1155 , created_by
1156 , creation_date
1157 , last_updated_by
1158 , last_update_date
1159 , last_update_login
1160 , program_id
1161 , program_login_id
1162 , program_application_id
1163 , request_id
1164 -- denomalized columns
1165 , source_object_type_code
1166 , task_type_id
1167 , task_type_rule
1168 , deleted_flag
1169 , act_bac_assignee_id
1170 -- R12 resource type impact
1171 , act_bac_assignee_type
1172 , act_bac_district_id
1173 , inventory_item_id
1174 , inv_organization_id
1175 , customer_id
1176 , planned_start_date
1177 -- denomalized columns
1178 )
1179 /* If the volume of the table ISC_FS_TASK_ACT_BAC_STG is going to be high, create
1180 an index on LAST_ROW_FOR_DAY_FLAG with a histogram */
1181 select /*+ ordered use_nl(T,S) */
1182 t.task_id
1183 , -0.1 task_audit_id
1184 , case
1185 -- note: the sequence of the "when" is important, don't change it!
1186 when nvl(s.schedulable_flag,'N') = 'Y' or
1187 trunc(l_collect_to_date) < trunc(nvl(t.first_asgn_creation_date,g_max_date)) then
1188 1 --'IN PLANNING'
1189 when nvl(s.working_flag,'N') = 'Y' then
1190 3 --'WORKING'
1191 when nvl(s.assigned_flag,'N') = 'Y' then
1192 2 --'ASSIGNED'
1193 when nvl(s.completed_flag,'N') = 'Y' then
1194 4 --'COMPLETED'
1195 else
1196 5 --'OTHER'
1197 end backlog_status_code
1198 , 'O' status_flag
1199 , l_collect_to_date audit_date
1200 , 'Y' last_row_for_day_flag
1201 , g_user_id
1202 , sysdate
1203 , g_user_id
1204 , sysdate
1205 , g_login_id
1206 , g_program_id
1207 , g_program_login_id
1208 , g_program_application_id
1209 , g_request_id
1210 -- denomalized columns
1211 , t.source_object_type_code
1212 , t.task_type_id
1213 , t.task_type_rule
1214 , t.deleted_flag
1215 , t.act_bac_assignee_id
1216 -- R12 resource type impact
1217 , t.act_bac_assignee_type
1218 , t.act_bac_district_id
1219 , t.inventory_item_id
1220 , t.inv_organization_id
1221 , t.customer_id
1222 , t.planned_start_date
1223 -- denomalized columns
1224 from
1225 ( select
1226 task_id
1227 , audit_date
1228 , backlog_status_code
1229 , status_flag
1230 , rank() over(partition by task_id order by audit_date desc, task_audit_id desc) rnk
1231 from
1232 isc_fs_task_act_bac_stg
1233 where last_row_for_day_flag = 'Y'
1234 ) b
1235 , isc_fs_tasks_f t
1236 , jtf_task_statuses_b s
1237 where
1238 b.rnk = 1
1239 and b.status_flag = 'O'
1240 and trunc(b.audit_date) < trunc(l_collect_to_date)
1241 and b.task_id = t.task_id
1242 and t.task_status_id = s.task_status_id
1243 and nvl(s.closed_flag,'N') = 'N'
1244 and b.backlog_status_code <> case
1245 -- note: the sequence of the "when" is important, don't change it!
1246 when nvl(s.schedulable_flag,'N') = 'Y' or
1247 trunc(l_collect_to_date) < trunc(nvl(t.first_asgn_creation_date,g_max_date)) then
1248 1 --'IN PLANNING'
1249 when nvl(s.working_flag,'N') = 'Y' then
1250 3 --'WORKING'
1251 when nvl(s.assigned_flag,'N') = 'Y' then
1252 2 --'ASSIGNED'
1253 when nvl(s.completed_flag,'N') = 'Y' then
1254 4 --'COMPLETED'
1255 else
1256 5 --'OTHER'
1257 end;
1258
1259 l_rowcount := sql%rowcount;
1260
1261 bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1262
1263 commit;
1264
1265 bis_collection_utilities_log( 'Hiding ''duplicate'' rows from backlog query', 1 );
1266
1267 -- hide 'duplicate' rows from backlog query
1268 l_stmt_id := 90;
1269 update isc_fs_task_act_bac_stg
1270 set status_flag = lower(status_flag)
1271 , last_updated_by = g_user_id
1272 , last_update_date = sysdate
1273 , last_update_login = g_login_id
1274 , program_id = g_program_id
1275 , program_login_id = g_program_login_id
1276 , program_application_id = g_program_application_id
1277 , request_id = g_request_id
1278 where rowid in ( select rowid
1279 from
1280 ( select
1281 task_id || '^' ||
1282 backlog_status_code conc_key
1283 , lag(task_id || '^' ||
1284 backlog_status_code
1285 ,1,'^')
1286 over (order by
1287 task_id
1288 , audit_date
1289 , task_audit_id) prev_conc_key
1290 from
1291 isc_fs_task_act_bac_stg
1292 where
1293 last_row_for_day_flag = 'Y'
1294 )
1295 where conc_key = prev_conc_key
1296 );
1297
1298 l_temp_rowcount := sql%rowcount;
1299
1300 bis_collection_utilities_log(l_temp_rowcount || ' rows updated',2);
1301
1302 commit;
1303
1304 bis_collection_utilities_log('Staging table complete');
1305
1306 -- ---------------------------------------------- --
1307 -- do not issue another commit until we are done!
1308 -- ---------------------------------------------- --
1309
1310 bis_collection_utilities_log( 'Updating changes to denormalized data for existing rows', 1 );
1311
1312 -- pick up changes in denormalized columns from isc_fs_tasks_f
1313 -- for existing activity and end dated backlog
1314 l_stmt_id := 92;
1315 l_rowcount := 0;
1316 l_temp_rowcount := 0;
1317
1318 for i in c_updated loop
1319
1320 l_stmt_id := 94;
1321 update isc_fs_task_activity_f
1322 set
1323 source_object_type_code = i.source_object_type_code
1324 , task_type_id = i.task_type_id
1325 , task_type_rule = i.task_type_rule
1326 , deleted_flag = i.deleted_flag
1327 , act_bac_assignee_id = i.act_bac_assignee_id
1328 -- R12 resource type impact
1329 , act_bac_assignee_type = i.act_bac_assignee_type
1330 , act_bac_district_id = i.act_bac_district_id
1331 , inventory_item_id = i.inventory_item_id
1332 , inv_organization_id = i.inv_organization_id
1333 , customer_id = i.customer_id
1334 , last_updated_by = g_user_id
1335 , last_update_date = sysdate
1336 , last_update_login = g_login_id
1337 , program_id = g_program_id
1338 , program_login_id = g_program_login_id
1339 , program_application_id = g_program_application_id
1340 , request_id = g_request_id
1341 where
1342 task_id = i.task_id
1343 and ( nvl(source_object_type_code,'X') <> nvl(i.source_object_type_code,'X') or -- should not be null
1344 nvl(task_type_id,-5) <> nvl(i.task_type_id,-5) or -- should not be null
1345 nvl(task_type_rule,'X') <> nvl(i.task_type_rule,'X') or -- may be null
1346 nvl(deleted_flag,'X') <> nvl(i.deleted_flag,'X') or -- should not be null
1347 nvl(act_bac_assignee_id,-5) <> nvl(i.act_bac_assignee_id,-5) or -- should not be null
1348 -- R12 resource type impact
1349 nvl(act_bac_assignee_type,'X') <> nvl(i.act_bac_assignee_type,'X') or -- should not be null
1350 nvl(act_bac_district_id,-5) <> nvl(i.act_bac_district_id,-5) or -- should not be null
1351 nvl(inventory_item_id,-5) <> nvl(i.inventory_item_id,-5) or -- should not be null
1352 nvl(inv_organization_id,-5) <> nvl(i.inv_organization_id,-5) or -- should not be null
1353 nvl(customer_id,-5) <> nvl(i.customer_id,-5) -- should not be null
1354 );
1355 l_rowcount := l_rowcount + sql%rowcount;
1356
1357 l_stmt_id := 96;
1358 update isc_fs_task_backlog_f
1359 set
1360 source_object_type_code = i.source_object_type_code
1361 , task_type_id = i.task_type_id
1362 , task_type_rule = i.task_type_rule
1363 , deleted_flag = i.deleted_flag
1364 , act_bac_assignee_id = i.act_bac_assignee_id
1365 -- R12 resource type impact
1366 , act_bac_assignee_type = i.act_bac_assignee_type
1367 , act_bac_district_id = i.act_bac_district_id
1368 , inventory_item_id = i.inventory_item_id
1369 , inv_organization_id = i.inv_organization_id
1370 , customer_id = i.customer_id
1371 , planned_start_date = i.planned_start_date
1372 , last_updated_by = g_user_id
1373 , last_update_date = sysdate
1374 , last_update_login = g_login_id
1375 , program_id = g_program_id
1376 , program_login_id = g_program_login_id
1377 , program_application_id = g_program_application_id
1378 , request_id = g_request_id
1379 where
1380 task_id = i.task_id
1381 and ( nvl(source_object_type_code,'X') <> nvl(i.source_object_type_code,'X') or -- should not be null
1382 nvl(task_type_id,-5) <> nvl(i.task_type_id,-5) or -- should not be null
1383 nvl(task_type_rule,'X') <> nvl(i.task_type_rule,'X') or -- may be null
1384 nvl(deleted_flag,'X') <> nvl(i.deleted_flag,'X') or -- should not be null
1385 nvl(act_bac_assignee_id,-5) <> nvl(i.act_bac_assignee_id,-5) or -- should not be null
1386 -- R12 resource type impact
1387 nvl(act_bac_assignee_type,'X') <> nvl(i.act_bac_assignee_type,'X') or -- should not be null
1388 nvl(act_bac_district_id,-5) <> nvl(i.act_bac_district_id,-5) or -- should not be null
1389 nvl(inventory_item_id,-5) <> nvl(i.inventory_item_id,-5) or -- should not be null
1390 nvl(inv_organization_id,-5) <> nvl(i.inv_organization_id,-5) or -- should not be null
1391 nvl(customer_id,-5) <> nvl(i.customer_id,-5) or -- should not be null
1392 nvl(planned_start_date,g_max_date) <> nvl(i.planned_start_date,g_max_date) -- may be null
1393 );
1394 l_temp_rowcount := l_temp_rowcount + sql%rowcount;
1395
1396 end loop;
1397
1398 bis_collection_utilities_log(l_rowcount || ' rows updated in activity base summary',2);
1399 bis_collection_utilities_log(l_temp_rowcount || ' rows updated in backlog base summary',2);
1400
1401 bis_collection_utilities_log( 'Calculating changes to previous current task backlog rows', 1 );
1402
1403 --
1404 -- determine if the previous backlog row needs to be closed off
1405 -- as there is a subsequent row for the same task, either closed or
1406 -- open but with different properties
1407 --
1408 l_stmt_id := 100;
1409 select
1410 backlog_rowid
1411 , lead_audit_date -1
1412 bulk collect into
1413 l_rowid_tbl
1414 , l_backlog_date_to
1415 from
1416 ( select
1417 backlog_rowid
1418 , lead( backlog_status_code, 1, backlog_status_code )
1419 over( partition by task_id order by audit_date, task_audit_id ) lead_backlog_status_code
1420 , lead( status_flag, 1, status_flag )
1421 over( partition by task_id order by audit_date, task_audit_id ) lead_status_flag
1422 , lead( trunc(audit_date), 1, null )
1423 over( partition by task_id order by audit_date, task_audit_id ) lead_audit_date
1424 , backlog_status_code
1425 , status_flag
1426 from
1427 isc_fs_task_act_bac_stg
1428 where
1429 last_row_for_day_flag = 'Y'
1430 and status_flag in ('O','C')
1431 )
1432 where backlog_rowid is not null
1433 and lead_audit_date is not null
1434 and ( lead_backlog_status_code <> backlog_status_code or
1435 lead_status_flag <> status_flag );
1436
1437 bis_collection_utilities_log( 'Updating changed previous current task backlog rows', 2 );
1438 --
1439 -- updated the previous backlog row that need to be closed off
1440 -- as there is a subsequent row for the same task, either closed or
1441 -- open with different properties
1442 --
1443 l_stmt_id := 110;
1444 forall i in 1..l_rowid_tbl.count
1445 update isc_fs_task_backlog_f
1446 set
1447 backlog_date_to = l_backlog_date_to(i)
1448 , last_updated_by = g_user_id
1449 , last_update_date = sysdate
1450 , last_update_login = g_login_id
1451 , program_id = g_program_id
1452 , program_login_id = g_program_login_id
1453 , program_application_id = g_program_application_id
1454 , request_id = g_request_id
1455 where rowid = l_rowid_tbl(i);
1456
1457 l_rowcount := sql%rowcount;
1458
1459 bis_collection_utilities_log( l_rowid_tbl.count || ' rows updated', 3 );
1460
1461 bis_collection_utilities_log( 'Inserting activity', 1 );
1462
1463 --
1464 -- insert the activity rows into isc_fs_task_activity_f
1465 --
1466 l_stmt_id := 120;
1467 insert into isc_fs_task_activity_f
1468 ( task_id
1469 , task_audit_id
1470 , activity_date
1471 , first_opened
1472 , reopened
1473 , closed
1474 , created_by
1475 , creation_date
1476 , last_updated_by
1477 , last_update_date
1478 , last_update_login
1479 , program_id
1480 , program_login_id
1481 , program_application_id
1482 , request_id
1483 -- denomalized columns
1484 , source_object_type_code
1485 , task_type_id
1486 , task_type_rule
1487 , deleted_flag
1488 , act_bac_assignee_id
1489 -- R12 resource type impact
1490 , act_bac_assignee_type
1491 , act_bac_district_id
1492 , inventory_item_id
1493 , inv_organization_id
1494 , customer_id
1495 -- denomalized columns
1496 )
1497 select
1498 task_id
1499 , task_audit_id
1500 , trunc(audit_date)
1501 , first_opened
1502 , reopened
1503 , closed
1504 , g_user_id
1505 , sysdate
1506 , g_user_id
1507 , sysdate
1508 , g_login_id
1509 , g_program_id
1510 , g_program_login_id
1511 , g_program_application_id
1512 , g_request_id
1513 -- denomalized columns
1514 , source_object_type_code
1515 , task_type_id
1516 , task_type_rule
1517 , deleted_flag
1518 , act_bac_assignee_id
1519 -- R12 resource type impact
1520 , act_bac_assignee_type
1521 , act_bac_district_id
1522 , inventory_item_id
1523 , inv_organization_id
1524 , customer_id
1525 -- denomalized columns
1526 from
1527 isc_fs_task_act_bac_stg
1528 where
1529 trunc(audit_date) >= g_global_start_date
1530 and 1 in ( first_opened
1531 , reopened
1532 , closed
1533 );
1534
1535 l_rowcount := sql%rowcount;
1536
1537 bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1538
1539 bis_collection_utilities_log( 'Inserting backlog history', 1 );
1540
1541 --
1542 -- insert the new backlog rows into isc_fs_task_backlog_f
1543 --
1544 l_stmt_id := 130;
1545 insert
1546 first
1547 when status_flag = 'O' and
1548 backlog_rowid is null then
1549 into isc_fs_task_backlog_f
1550 ( task_id
1551 , task_audit_id
1552 , backlog_date_from
1553 , backlog_date_to
1554 , backlog_status_code
1555 , created_by
1556 , creation_date
1557 , last_updated_by
1558 , last_update_date
1559 , last_update_login
1560 , program_id
1561 , program_login_id
1562 , program_application_id
1563 , request_id
1564 -- denomalized columns
1565 , source_object_type_code
1566 , task_type_id
1567 , task_type_rule
1568 , deleted_flag
1569 , act_bac_assignee_id
1570 -- R12 resource type impact
1571 , act_bac_assignee_type
1572 , act_bac_district_id
1573 , inventory_item_id
1574 , inv_organization_id
1575 , customer_id
1576 , planned_start_date
1577 -- denomalized columns
1578 )
1579 values
1580 ( task_id
1581 , task_audit_id
1582 , greatest(backlog_date_from, g_global_start_date)
1583 , greatest(backlog_date_to, g_global_start_date)
1584 , backlog_status_code
1585 , g_user_id
1586 , sysdate
1587 , g_user_id
1588 , sysdate
1589 , g_login_id
1590 , g_program_id
1591 , g_program_login_id
1592 , g_program_application_id
1593 , g_request_id
1594 -- denomalized columns
1595 , source_object_type_code
1596 , task_type_id
1597 , task_type_rule
1598 , deleted_flag
1599 , act_bac_assignee_id
1600 -- R12 resource type impact
1601 , act_bac_assignee_type
1602 , act_bac_district_id
1603 , inventory_item_id
1604 , inv_organization_id
1605 , customer_id
1606 , planned_start_date
1607 -- denomalized columns
1608 )
1609 select
1610 task_id
1611 , task_audit_id
1612 , trunc(audit_date) backlog_date_from
1613 , lead(trunc(audit_date)-1,1,g_max_date)
1614 over(partition by task_id order by audit_date, task_audit_id) backlog_date_to
1615 , backlog_status_code
1616 , backlog_rowid
1617 , status_flag
1618 -- denomalized columns
1619 , source_object_type_code
1620 , task_type_id
1621 , task_type_rule
1622 , deleted_flag
1623 , act_bac_assignee_id
1624 -- R12 resource type impact
1625 , act_bac_assignee_type
1626 , act_bac_district_id
1627 , inventory_item_id
1628 , inv_organization_id
1629 , customer_id
1630 , planned_start_date
1631 -- denomalized columns
1632 from
1633 isc_fs_task_act_bac_stg
1634 where
1635 status_flag in ('O','C')
1636 and last_row_for_day_flag = 'Y';
1637
1638 l_temp_rowcount := sql%rowcount;
1639
1640 bis_collection_utilities_log( l_temp_rowcount || ' rows inserted', 2 );
1641
1642 l_rowcount := l_rowcount + l_temp_rowcount;
1643
1644 commit;
1645
1646 -- house keeping -- cleanup staging table
1647 l_stmt_id := 140;
1648 if truncate_table
1649 ( l_isc_schema
1650 , 'ISC_FS_TASK_ACT_BAC_STG'
1651 , l_error_message ) <> 0 then
1652 logger( l_proc_name, l_stmt_id, l_error_message );
1653 raise l_exception;
1654 end if;
1655
1656 bis_collection_utilities_log( 'Staging table truncated', 1 );
1657
1658 l_stmt_id := 150;
1659 bis_collection_utilities.wrapup( p_status => true
1660 , p_period_from => l_collect_from_date
1661 , p_period_to => l_collect_to_date
1662 , p_count => l_rowcount
1663 );
1664
1665 bis_collection_utilities_log('End Incremental Load');
1666
1667 errbuf := null;
1668 retcode := g_success;
1669
1670 exception
1671 when g_bis_setup_exception then
1672 rollback;
1673 errbuf := l_error_message;
1674 retcode := g_error;
1675 bis_collection_utilities_log('End Incremential Load with Error');
1676
1677 when l_exception then
1678 rollback;
1679 if l_error_message is null then
1680 l_error_message := substr(sqlerrm,1,4000);
1681 end if;
1682 bis_collection_utilities.wrapup( p_status => false
1683 , p_message => l_error_message
1684 , p_period_from => l_collect_from_date
1685 , p_period_to => l_collect_to_date
1686 );
1687 errbuf := l_error_message;
1688 retcode := g_error;
1689 bis_collection_utilities_log('End Incremential Load with Error');
1690
1691 when others then
1692 rollback;
1693 if l_error_message is null then
1694 l_error_message := substr(sqlerrm,1,4000);
1695 end if;
1696 logger( l_proc_name, l_stmt_id, l_error_message );
1697 bis_collection_utilities.wrapup( p_status => false
1698 , p_message => l_error_message
1699 , p_period_from => l_collect_from_date
1700 , p_period_to => l_collect_to_date
1701 );
1702 errbuf := l_error_message;
1703 retcode := g_error;
1704 bis_collection_utilities_log('End Incremential Load with Error');
1705
1706 end incremental_load;
1707
1708 end isc_fs_task_act_bac_etl_pkg;