[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_REQ_WO_ETL_PKG
Source
1 package body isc_maint_req_wo_etl_pkg
2 /* $Header: iscmaintreqwoetb.pls 120.1 2005/09/13 21:23:11 nbhamidi noship $ */
3 as
4
5 g_pkg_name constant varchar2(30) := 'isc_maint_req_wo_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_object_name constant varchar2(30) := 'ISC_MAINT_REQ_WO_FACT';
18
19 procedure local_init
20 as
21 begin
22 g_user_id := fnd_global.user_id;
23 g_login_id := fnd_global.login_id;
24 g_global_start_date := bis_common_parameters.get_global_start_date;
25 g_program_id := fnd_global.conc_program_id;
26 g_program_login_id := fnd_global.conc_login_id;
27 g_program_application_id := fnd_global.prog_appl_id;
28 g_request_id := fnd_global.conc_request_id;
29 end local_init;
30
31 procedure logger
32 ( p_proc_name varchar2
33 , p_stmt_id number
34 , p_message varchar2
35 )
36 as
37 begin
38 bis_collection_utilities.log( g_pkg_name || '.' || p_proc_name ||
39 ' #' || p_stmt_id || ' ' ||
40 p_message
41 , 3 );
42 end logger;
43
44 function get_schema_name
45 ( x_schema_name out nocopy varchar2
46 , x_error_message out nocopy varchar2 )
47 return number as
48
49 l_isc_schema varchar2(30);
50 l_status varchar2(30);
51 l_industry varchar2(30);
52
53 begin
54
55 if fnd_installation.get_app_info('ISC', l_status, l_industry, l_isc_schema) then
56 x_schema_name := l_isc_schema;
57 else
58 x_error_message := 'FND_INSTALLATION.GET_APP_INFO returned false';
59 return -1;
60 end if;
61
62 return 0;
63
64 exception
65 when others then
66 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
67 return -1;
68
69 end get_schema_name;
70
71 function truncate_table
72 ( p_isc_schema in varchar2
73 , p_table_name in varchar2
74 , x_error_message out nocopy varchar2 )
75 return number as
76
77 begin
78
79 execute immediate 'truncate table ' || p_isc_schema || '.' || p_table_name;
80
81 return 0;
82
83 exception
84 when others then
85 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
86 return -1;
87
88 end truncate_table;
89
90 function gather_statistics
91 ( p_isc_schema in varchar2
92 , p_table_name in varchar2
93 , x_error_message out nocopy varchar2 )
94 return number as
95
96 begin
97
98 fnd_stats.gather_table_stats( ownname => p_isc_schema
99 , tabname => p_table_name
100 );
101
102 return 0;
103
104 exception
105 when others then
106 x_error_message := 'Error in function gather_statistics : ' || sqlerrm;
107 return -1;
108
109 end gather_statistics;
110
111 function get_last_refresh_date
112 ( x_refresh_date out nocopy date
113 , x_error_message out nocopy varchar2 )
114 return number as
115
116 l_refresh_date date;
117
118 begin
119
120 l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(g_object_name));
121 if l_refresh_date = g_global_start_date then
122 x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
123 return -1;
124 end if;
125
126 x_refresh_date := l_refresh_date;
127 return 0;
128
129 exception
130 when others then
131 x_error_message := 'Error in function get_last_refresh_date : ' || sqlerrm;
132 return -1;
133
134 end get_last_refresh_date;
135
136 -- -------------------------------------------------------------------
137 -- PUBLIC PROCEDURES
138 -- -------------------------------------------------------------------
139 procedure initial_load
140 ( errbuf out nocopy varchar2
141 , retcode out nocopy number
142 )
143 as
144
145 l_proc_name constant varchar2(30) := 'initial_load';
146 l_stmt_id number;
147 l_exception exception;
148 l_error_message varchar2(4000);
149 l_isc_schema varchar2(100);
150
151 l_timer number;
152 l_rowcount number;
153 l_temp_rowcount number;
154
155 l_collect_from_date date;
156 l_collect_to_date date;
157 l_missing_completion_date constant date := to_date('31/12/4712','dd/mm/yyyy');
158
159 begin
160
161 local_init;
162
163 bis_collection_utilities.log( 'Begin Initial Load' );
164
165 l_stmt_id := 0;
166 if not bis_collection_utilities.setup( g_object_name ) then
167 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
168 logger( l_proc_name, l_stmt_id, l_error_message );
169 raise g_bis_setup_exception;
170 end if;
171
172 -- determine the date we last collected to
173 l_stmt_id := 10;
174 if g_global_start_date is null then
175 l_error_message := 'Unable to get DBI global start date.'; -- translatable message?
176 logger( l_proc_name, l_stmt_id, l_error_message );
177 raise l_exception;
178 end if;
179
180 l_collect_from_date := g_global_start_date;
181 l_collect_to_date := sysdate;
182
183 bis_collection_utilities.log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
184 bis_collection_utilities.log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
185
186 -- get the isc schema name
187 l_stmt_id := 20;
188 if get_schema_name
189 ( l_isc_schema
190 , l_error_message ) <> 0 then
191 logger( l_proc_name, l_stmt_id, l_error_message );
192 raise l_exception;
193 end if;
194
195 -- truncate the fact table
196 l_stmt_id := 30;
197 if truncate_table
198 ( l_isc_schema
199 , 'ISC_MAINT_REQ_WO_F'
200 , l_error_message ) <> 0 then
201 logger( l_proc_name, l_stmt_id, l_error_message );
202 raise l_exception;
203 end if;
204
205 bis_collection_utilities.log( 'Base summary table truncated', 1 );
206
207 -- insert into base fact from staging table
208 l_stmt_id := 40;
209 insert /*+ append parallel(f) */
210 into isc_maint_req_wo_f f
211 ( request_type
212 , maint_request_id
213 , association_id
214 , request_number
215 , organization_id
216 , department_id
217 , asset_group_id
218 , instance_id /* replaced asset_number with instance_id */
219 , request_start_date
220 , request_severity_id
221 , work_order_id
222 , completion_date
223 , response_days
224 , completion_days
225 , work_order_count
226 , creation_date
227 , created_by
228 , last_update_date
229 , last_updated_by
230 , last_update_login
231 , program_id
232 , program_login_id
233 , program_application_id
234 , request_id
235 )
236 --
237 -- select SR/WO associations
238 --
239 select /*+ parallel(x) */
240 '2' request_type
241 , maint_request_id
242 , association_id
243 , request_number
244 , nvl(organization_id,-1)
245 , nvl(department_id,-1)
246 , nvl(asset_group_id,-1)
247 , nvl(instance_id,-1)
248 , request_start_date
249 , request_severity_id
250 , work_order_id
251 , completion_date
252 , response_days
253 , completion_days
254 , work_order_count
255 , sysdate
256 , g_user_id
257 , sysdate
258 , g_user_id
259 , g_login_id
260 , g_program_id
261 , g_program_login_id
262 , g_program_application_id
263 , g_request_id
264 from
265 ( select /*+ parallel(x) */
266 maint_request_id
267 , association_id
268 , request_number
269 , organization_id
270 , department_id
271 , asset_group_id
272 , instance_id
273 , request_start_date
274 , request_severity_id
275 , work_order_id
276 -- if this is the last completion date for all WOs for the SR and
277 -- the completion date is not null we attribute the completion date
278 -- to this SR/WO association
279 , case
280 when completion_rank = 1 and
281 completion_datetime <> l_missing_completion_date then
282 trunc(completion_datetime)
283 else
284 null
285 end completion_date
286 -- if this is the last completion date for all WOs for the SR and
287 -- the completion date is not null we attribute the min response days
288 -- to this SR/WO association
289 , case
290 when completion_rank = 1 and
291 completion_datetime <> l_missing_completion_date then
292 min_response_days
293 else
294 null
295 end response_days
296 -- if this is the last completion date for all WOs for the SR and
297 -- the completion date is not null we attribute the completion days
298 -- to this SR/WO association
299 , case
300 when completion_rank = 1 and
301 completion_datetime <> l_missing_completion_date then
302 completion_days
303 else
304 null
305 end completion_days
306 -- if this is the last completion date for all WOs for the SR and
307 -- the completion date is not null we attribute the number of work orders
308 -- to this SR/WO association
309 , case
310 when completion_rank = 1 and
311 completion_datetime <> l_missing_completion_date then
312 work_order_count
313 else
314 null
315 end work_order_count
316 from
317 ( select /*+ parallel(i) parallel(a) parallel(w) */
318 i.incident_id maint_request_id
319 , a.wo_service_entity_assoc_id association_id
320 , i.incident_number request_number
321 , a.maintenance_organization_id organization_id
322 , i.owning_department_id department_id
323 , i.inventory_item_id asset_group_id
324 , i.customer_product_id instance_id
325 , i.incident_date request_start_date
326 , i.incident_severity_id request_severity_id
327 , w.work_order_id
328 , w.completion_datetime
329 -- calculate the response days for each SR/WO association
330 -- this should never be less than 0 days
331 , greatest(w.wo_creation_datetime - i.incident_date, 0) response_days
332 -- calculate the completion days for each SR/WO association
333 -- this should never be less than 0 days
334 , greatest(w.completion_datetime - i.incident_date, 0) completion_days
335 -- rank the SR/WO associations for the same SR based on WO completion date,
336 -- the WO with the latest completion date is ranked first. A null
337 -- completion date will always outrank a not null completion date
338 , row_number()
339 over(partition by i.incident_id
340 order by nvl(w.completion_datetime,l_missing_completion_date) desc
341 , a.wo_service_entity_assoc_id) completion_rank
342 -- determine the min response days for all SR/WO associations for
343 -- the same SR
344 , min(greatest(w.wo_creation_datetime - i.incident_date, 0))
345 over(partition by i.incident_id) min_response_days
346 , count(*) over(partition by i.incident_id) work_order_count
347 from
348 cs_incidents_all_b i
349 , eam_wo_service_association a
350 , isc_maint_work_orders_f w
351 where
352 i.incident_id = a.service_request_id
353 and a.wip_entity_id = w.work_order_id
354 and a.maintenance_organization_id = w.organization_id
355 and nvl(a.enable_flag,'Y') = 'Y'
356 -- exclude all cancelled work orders
357 and w.status_type <> 7
358 ) x
359 ) x
360 where nvl(completion_date,g_global_start_date) >= g_global_start_date
361 union all
362 --
363 -- select WR/WO associations
364 --
365 select /*+ parallel(r) parallel(w) */
366 '1' request_type
367 , r.work_request_id maint_request_id
368 , r.work_request_id association_id
369 , r.work_request_number request_number
370 , nvl(r.organization_id,-1) organization_id
371 , nvl(r.work_request_owning_dept,-1) department_id
372 , nvl(r.asset_group,-1) asset_group_id
373 , nvl(r.maintenance_object_id,-1) instance_id
374 , r.creation_date request_start_date
375 , nvl(r.work_request_priority_id,-1) request_severity_id
376 , w.work_order_id
377 , trunc(w.completion_datetime) completion_date
378 , case
379 when w.completion_datetime is not null then
380 greatest(w.wo_creation_datetime - r.creation_date, 0)
381 else
382 null
383 end response_days
384 , case
385 when w.completion_datetime is not null then
386 greatest(w.completion_datetime - r.creation_date, 0)
387 else
388 null
389 end completion_days
390 , case
391 when w.completion_datetime is not null then
392 1
393 else
394 null
395 end work_order_count
396 , sysdate
397 , g_user_id
398 , sysdate
399 , g_user_id
400 , g_login_id
401 , g_program_id
402 , g_program_login_id
403 , g_program_application_id
404 , g_request_id
405 from
406 wip_eam_work_requests r
407 , isc_maint_work_orders_f w
408 where
409 r.wip_entity_id = w.work_order_id
410 and r.organization_id = w.organization_id
411 -- only include WR with WO completion_date >= global start date
412 and nvl(w.completion_date,g_global_start_date) >= g_global_start_date
413 -- exclude all cancelled work orders
414 and w.status_type <> 7;
415
416 l_rowcount := sql%rowcount;
417
418 commit;
419
420 bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
421
422 l_stmt_id := 50;
426 , p_count => l_rowcount
423 bis_collection_utilities.wrapup( p_status => true
424 , p_period_from => l_collect_from_date
425 , p_period_to => l_collect_to_date
427 );
428
429 bis_collection_utilities.log('End Initial Load');
430
431 errbuf := null;
432 retcode := g_success;
433
434 exception
435 when g_bis_setup_exception then
436 rollback;
437 errbuf := l_error_message;
438 retcode := g_error;
439
440 when others then
441 rollback;
442 if l_error_message is null then
443 l_error_message := substr(sqlerrm,1,4000);
444 end if;
445 bis_collection_utilities.wrapup( p_status => false
446 , p_message => l_error_message
447 , p_period_from => l_collect_from_date
448 , p_period_to => l_collect_to_date
449 );
450 errbuf := l_error_message;
451 retcode := g_error;
452
453 end initial_load;
454
455 procedure incremental_load
456 ( errbuf out nocopy varchar2
457 , retcode out nocopy number
458 )
459 as
460
461 l_proc_name constant varchar2(30) := 'incremental_load';
462 l_stmt_id number;
463 l_exception exception;
464 l_error_message varchar2(4000);
465 l_isc_schema varchar2(100);
466
467 l_timer number;
468 l_rowcount number;
469 l_temp_rowcount number;
470
471 l_collect_from_date date;
472 l_collect_to_date date;
473
474 l_missing_completion_date constant date := to_date('31/12/4712','dd/mm/yyyy');
475 l_disabled_completion_date constant date := to_date('01/01/1111','dd/mm/yyyy');
476
477 begin
478
479 local_init;
480
481 bis_collection_utilities.log( 'Begin Incremental Load' );
482
483 l_stmt_id := 0;
484 if not bis_collection_utilities.setup( g_object_name ) then
485 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
486 logger( l_proc_name, l_stmt_id, l_error_message );
487 raise g_bis_setup_exception;
488 end if;
489
490 -- determine the date we last collected to
491 l_stmt_id := 10;
492 if get_last_refresh_date(l_collect_to_date, l_error_message) <> 0 then
493 logger( l_proc_name, l_stmt_id, l_error_message );
494 raise l_exception;
495 end if;
496 l_collect_from_date := l_collect_to_date + 1/86400;
497 l_collect_to_date := sysdate;
498
499 bis_collection_utilities.log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
500 bis_collection_utilities.log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
501
502 -- get the isc schema name
503 l_stmt_id := 20;
504 if get_schema_name
505 ( l_isc_schema
506 , l_error_message ) <> 0 then
507 logger( l_proc_name, l_stmt_id, l_error_message );
508 raise l_exception;
509 end if;
510
511 -- truncate the staging table
512 l_stmt_id := 30;
513 if truncate_table
514 ( l_isc_schema
515 , 'ISC_MAINT_REQ_WO_STG'
516 , l_error_message ) <> 0 then
517 logger( l_proc_name, l_stmt_id, l_error_message );
518 raise l_exception;
519 end if;
520
521 bis_collection_utilities.log( 'Staging table truncated', 1 );
522
523 -- this detects all SR with changed associations
524 l_stmt_id := 40;
525 insert into
526 isc_maint_req_wo_stg
527 ( maint_request_id
528 , phase_id
529 , creation_date
530 , created_by
531 , last_update_date
532 , last_updated_by
533 , last_update_login
534 , program_id
535 , program_login_id
536 , program_application_id
537 , request_id
538 )
539 select distinct
540 service_request_id
541 , 1
542 , sysdate
543 , g_user_id
544 , sysdate
545 , g_user_id
546 , g_login_id
547 , g_program_id
548 , g_program_login_id
549 , g_program_application_id
550 , g_request_id
551 from eam_wo_service_association a
552 where a.last_update_date >= l_collect_from_date;
553
554 l_rowcount := sql%rowcount;
555
556 bis_collection_utilities.log( l_rowcount || ' rows inserted staging table from association', 1 );
557
558 -- this detects all SR where associated WO is updated
559 l_stmt_id := 50;
560 merge into
561 isc_maint_req_wo_stg s
562 using
563 ( select distinct
564 service_request_id maint_request_id
565 , 2 phase_id
566 from eam_wo_service_association a
567 , isc_maint_work_orders_f w
568 where
569 a.wip_entity_id = w.work_order_id
570 and a.maintenance_organization_id = w.organization_id
571 and w.last_update_date >= l_collect_from_date
572 ) n
573 on ( s.maint_request_id = n.maint_request_id )
574 when matched then
575 update
576 set phase_id = s.phase_id + n.phase_id
577 , last_update_date = sysdate
578 , last_updated_by = g_user_id
579 , last_update_login = g_login_id
580 , program_id = g_program_id
581 , program_login_id = g_program_login_id
582 , program_application_id = g_program_application_id
583 , request_id = g_request_id
584 when not matched then
585 insert
586 ( maint_request_id
587 , phase_id
588 , creation_date
589 , created_by
590 , last_update_date
591 , last_updated_by
592 , last_update_login
593 , program_id
597 )
594 , program_login_id
595 , program_application_id
596 , request_id
598 values
599 ( n.maint_request_id
600 , n.phase_id
601 , sysdate
602 , g_user_id
603 , sysdate
604 , g_user_id
605 , g_login_id
606 , g_program_id
607 , g_program_login_id
608 , g_program_application_id
609 , g_request_id
610 );
611
612 l_rowcount := sql%rowcount;
613
614 bis_collection_utilities.log( l_rowcount || ' rows merged into staging table from work orders', 1 );
615
616 -- this detects new or updated SR of type maintenance
617 /*
618 -- this is no longer performed, we will only track changes to dimension values
619 -- in the service request at completion of the work order or if there has been
620 -- a change at the association level.
621
622 l_stmt_id := 60;
623 merge into
624 isc_maint_req_wo_stg s
625 using
626 ( select distinct
627 incident_id maint_request_id
628 , 3 phase_id
629 from cs_incidents_audit_b a
630 , cs_incident_types_b t
631 where
632 a.creation_date >= l_collect_from_date
633 and a.incident_type_id = t.incident_type_id
634 and t.maintenance_flag = 'Y'
635 and ( ( a.change_incident_type_flag = 'Y' and a.old_incident_type_id is null ) or
636 a.change_inventory_item_flag = 'Y' or
637 a.change_inv_organization_flag = 'Y' or
638 ( ( a.item_serial_number is null and a.old_item_serial_number is not null) or
639 ( a.old_item_serial_number is null and a.item_serial_number is not null ) or
640 ( a.old_item_serial_number <> a.item_serial_number ) ) or
641 ( ( a.owning_department_id is null and a.old_owning_department_id is not null) or
642 ( a.old_owning_department_id is null and a.owning_department_id is not null ) or
643 ( a.old_owning_department_id <> a.owning_department_id ) )
644 )
645 ) n
646 on ( s.maint_request_id = n.maint_request_id )
647 when matched then
648 update
649 set phase_id = s.phase_id + n.phase_id
650 , last_update_date = sysdate
651 , last_updated_by = g_user_id
652 , last_update_login = g_login_id
653 , program_id = g_program_id
654 , program_login_id = g_program_login_id
655 , program_application_id = g_program_application_id
656 , request_id = g_request_id
657 when not matched then
658 insert
659 ( maint_request_id
660 , phase_id
661 , creation_date
662 , created_by
663 , last_update_date
664 , last_updated_by
665 , last_update_login
666 , program_id
667 , program_login_id
668 , program_application_id
669 , request_id
670 )
671 values
672 ( n.maint_request_id
673 , n.phase_id
674 , sysdate
675 , g_user_id
676 , sysdate
677 , g_user_id
678 , g_login_id
679 , g_program_id
680 , g_program_login_id
681 , g_program_application_id
682 , g_request_id
683 );
684
685 l_rowcount := sql%rowcount;
686
687 bis_collection_utilities.log( l_rowcount || ' rows merged into staging table from incident audits', 1 );
688 */
689
690 -- gather statistics on staging table
691 l_stmt_id := 70;
692 if gather_statistics
693 ( l_isc_schema
694 , 'ISC_MAINT_REQ_WO_STG'
695 , l_error_message ) <> 0 then
696 logger( l_proc_name, l_stmt_id, l_error_message );
697 raise l_exception;
698 end if;
699
700 bis_collection_utilities.log( 'Staging table analyzed', 1 );
701
702 -- merge staging table into base fact
703 l_stmt_id := 80;
704 merge
705 into isc_maint_req_wo_f f
706 using
707 (
708 --
709 -- select SR/WO associations
710 --
711 select
712 '2' request_type
713 , maint_request_id
714 , association_id
715 , request_number
716 , nvl(organization_id,-1) organization_id
717 , nvl(department_id,-1) department_id
718 , nvl(asset_group_id,-1) asset_group_id
719 , nvl(instance_id,-1) instance_id /* replaced asset_number with instance_id */
720 , request_start_date
721 , request_severity_id
722 , work_order_id
723 -- if this is the last completion date for all WOs for the SR and
724 -- the completion date is not null we attribute the completion date
725 -- to this SR/WO association
726 , case
727 when completion_rank = 1 and
728 completion_datetime <> l_missing_completion_date and
729 completion_datetime <> l_disabled_completion_date then
730 trunc(completion_datetime)
731 else
732 null
733 end completion_date
734 -- if this is the last completion date for all WOs for the SR and
735 -- the completion date is not null we attribute the min response days
736 -- to this SR/WO association
737 , case
738 when completion_rank = 1 and
739 completion_datetime <> l_missing_completion_date and
740 completion_datetime <> l_disabled_completion_date then
741 min_response_days
742 else
743 null
744 end response_days
745 -- if this is the last completion date for all WOs for the SR and
746 -- the completion date is not null we attribute the completion days
747 -- to this SR/WO association
748 , case
749 when completion_rank = 1 and
753 else
750 completion_datetime <> l_missing_completion_date and
751 completion_datetime <> l_disabled_completion_date then
752 completion_days
754 null
755 end completion_days
756 -- if this is the last completion date for all WOs for the SR and
757 -- the completion date is not null we attribute the number of work orders
758 -- to this SR/WO association
759 , case
760 when completion_rank = 1 and
761 completion_datetime <> l_missing_completion_date and
762 completion_datetime <> l_disabled_completion_date then
763 work_order_count
764 else
765 null
766 end work_order_count
767 from
768 ( select
769 i.incident_id maint_request_id
770 , a.wo_service_entity_assoc_id association_id
771 , i.incident_number request_number
772 , a.maintenance_organization_id organization_id
773 , i.owning_department_id department_id
774 , i.inventory_item_id asset_group_id
775 , i.customer_product_id instance_id
776 , i.incident_date request_start_date
777 , i.incident_severity_id request_severity_id
778 , case
779 when nvl(a.enable_flag,'Y') = 'Y' then
780 w.work_order_id
781 else
782 null
783 end work_order_id
784 , case
785 when nvl(a.enable_flag,'Y') = 'Y' and
786 w.status_type <> 7 then
787 w.completion_datetime
788 else
789 null
790 end completion_datetime
791 -- calculate the response days for each SR/WO association
792 -- this should never be less than 0 days
793 , case
794 when nvl(a.enable_flag,'Y') = 'Y' and
795 w.status_type <> 7 then
796 greatest(w.wo_creation_datetime - i.incident_date, 0)
797 else
798 null
799 end response_days
800 -- calculate the completion days for each SR/WO association
801 -- this should never be less than 0 days
802 , case
803 when nvl(a.enable_flag,'Y') = 'Y' and
804 w.status_type <> 7 then
805 greatest(w.completion_datetime - i.incident_date, 0)
806 else
807 null
808 end completion_days
809 -- rank the SR/WO associations for the same SR based on WO completion date,
810 -- the WO with the latest completion date is ranked first. A null
811 -- completion date will always outrank a not null completion date
812 , row_number()
813 over(partition by i.incident_id
814 order by case
815 when nvl(a.enable_flag,'Y') = 'Y' and
816 w.status_type <> 7 then
817 nvl(w.completion_datetime,l_missing_completion_date)
818 else
819 l_disabled_completion_date
820 end desc
821 , a.wo_service_entity_assoc_id) completion_rank
822
823 , min(greatest(case
824 when nvl(a.enable_flag,'Y') = 'Y' and
825 w.status_type <> 7 then
826 w.wo_creation_datetime - i.incident_date
827 else
828 999999999999999
829 end, 0))
830 over(partition by i.incident_id) min_response_days
831 , sum( case
832 when nvl(a.enable_flag,'Y') = 'Y' and
833 w.status_type <> 7 then
834 1
835 else
836 0
837 end ) over(partition by i.incident_id) work_order_count
838 from
839 cs_incidents_all_b i
840 , eam_wo_service_association a
841 , isc_maint_work_orders_f w
842 , isc_maint_req_wo_stg c
843 where
844 i.incident_id = a.service_request_id
845 and a.wip_entity_id = w.work_order_id
846 and a.maintenance_organization_id = w.organization_id
847 and a.service_request_id = c.maint_request_id
848 )
849 union all
850 --
851 -- select WR/WO associations
852 --
853 select
854 '1' request_type
855 , r.work_request_id maint_request_id
856 , r.work_request_id association_id
857 , r.work_request_number request_number
858 , nvl(r.organization_id,-1) organization_id
859 , nvl(r.work_request_owning_dept,-1) department_id
860 , nvl(r.asset_group,-1) asset_group_id
861 , nvl(r.maintenance_object_id,-1) instance_id
862 , r.creation_date request_start_date
863 , nvl(r.work_request_priority_id,-1) request_severity_id
864 , w.work_order_id
865 , case
866 when w.completion_datetime is not null and
867 w.status_type <> 7 then
868 trunc(w.completion_datetime)
869 else
870 null
871 end completion_date
872 , case
873 when w.completion_datetime is not null and
874 w.status_type <> 7 then
875 greatest(w.wo_creation_datetime - r.creation_date, 0)
876 else
877 null
878 end response_days
879 , case
880 when w.completion_datetime is not null and
881 w.status_type <> 7 then
882 greatest(w.completion_datetime - r.creation_date, 0)
883 else
884 null
885 end completion_days
886 , case
887 when w.completion_datetime is not null and
888 w.status_type <> 7 then
889 1
890 else
891 null
892 end work_order_count
893 from
894 wip_eam_work_requests r
895 , isc_maint_work_orders_f w
896 where
897 r.wip_entity_id = w.work_order_id(+)
898 and r.organization_id = w.organization_id(+)
899 and ( r.last_update_date >= l_collect_from_date or
900 w.last_update_date >= l_collect_from_date )
901 ) s
902 on
903 ( f.request_type = s.request_type and
904 f.maint_request_id = s.maint_request_id and
905 f.association_id = s.association_id )
906 when matched then
907 update
908 set f.organization_id = s.organization_id
909 , f.department_id = s.department_id
910 , f.asset_group_id = s.asset_group_id
911 , f.instance_id = s.instance_id
912 , f.request_severity_id = s.request_severity_id
913 , f.work_order_id = s.work_order_id
914 , f.completion_date = s.completion_date
915 , f.response_days = s.response_days
916 , f.completion_days = s.completion_days
917 , f.work_order_count = s.work_order_count
918 , f.last_update_date = sysdate
919 , f.last_updated_by = g_user_id
920 , f.last_update_login = g_login_id
921 , f.program_id = g_program_id
922 , f.program_login_id = g_program_login_id
923 , f.program_application_id = g_program_application_id
924 , f.request_id = g_request_id
925 when not matched then
926 insert
927 ( request_type
928 , maint_request_id
929 , association_id
930 , request_number
931 , organization_id
932 , department_id
933 , asset_group_id
934 , instance_id
935 , request_start_date
936 , request_severity_id
937 , work_order_id
938 , completion_date
939 , response_days
940 , completion_days
941 , work_order_count
942 , creation_date
943 , created_by
944 , last_update_date
945 , last_updated_by
946 , last_update_login
947 , program_id
948 , program_login_id
949 , program_application_id
950 , request_id
951 )
952 values
953 ( s.request_type
954 , s.maint_request_id
955 , s.association_id
956 , s.request_number
957 , s.organization_id
958 , s.department_id
959 , s.asset_group_id
960 , s.instance_id
961 , s.request_start_date
962 , s.request_severity_id
963 , s.work_order_id
964 , s.completion_date
965 , s.response_days
966 , s.completion_days
967 , s.work_order_count
968 , sysdate
969 , g_user_id
970 , sysdate
971 , g_user_id
972 , g_login_id
973 , g_program_id
974 , g_program_login_id
975 , g_program_application_id
976 , g_request_id
977 );
978
979 l_rowcount := sql%rowcount;
980
981 commit;
982
983 bis_collection_utilities.log( l_rowcount || ' rows merged into base summary', 1 );
984
985 -- housekeeping/cleanup truncate the staging table
986 l_stmt_id := 90;
987 if truncate_table
988 ( l_isc_schema
989 , 'ISC_MAINT_REQ_WO_STG'
990 , l_error_message ) <> 0 then
991 logger( l_proc_name, l_stmt_id, l_error_message );
992 raise l_exception;
993 end if;
994
995 bis_collection_utilities.log( 'Staging table truncated', 1 );
996 l_stmt_id := 100;
997 bis_collection_utilities.wrapup( p_status => true
998 , p_period_from => l_collect_from_date
999 , p_period_to => l_collect_to_date
1000 , p_count => l_rowcount
1001 );
1002
1003 bis_collection_utilities.log('End Incremental Load');
1004
1005 errbuf := null;
1006 retcode := g_success;
1007
1008 exception
1009 when g_bis_setup_exception then
1010 rollback;
1011 errbuf := l_error_message;
1012 retcode := g_error;
1013
1014 when others then
1015 rollback;
1016 if l_error_message is null then
1017 l_error_message := substr(sqlerrm,1,4000);
1018 end if;
1019 bis_collection_utilities.wrapup( p_status => false
1020 , p_message => l_error_message
1021 , p_period_from => l_collect_from_date
1022 , p_period_to => l_collect_to_date
1023 );
1024 errbuf := l_error_message;
1025 retcode := g_error;
1026
1027 end incremental_load;
1028
1029 end isc_maint_req_wo_etl_pkg;