[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_WO_CST_ETL_PKG
Source
1 package body isc_maint_wo_cst_etl_pkg
2 /* $Header: iscmaintwocstetb.pls 120.0 2005/05/25 17:31:32 appldev noship $ */
3 as
4
5 g_pkg_name constant varchar2(30) := 'isc_maint_wo_cst_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_WO_CST_FACT';
18 g_max_date constant date := to_date('4712/01/01','yyyy/mm/dd');
19
20 procedure local_init
21 as
22 begin
23 g_user_id := fnd_global.user_id;
24 g_login_id := fnd_global.login_id;
25 g_global_start_date := bis_common_parameters.get_global_start_date;
26 g_program_id := fnd_global.conc_program_id;
27 g_program_login_id := fnd_global.conc_login_id;
28 g_program_application_id := fnd_global.prog_appl_id;
29 g_request_id := fnd_global.conc_request_id;
30 end local_init;
31
32 procedure logger
33 ( p_proc_name varchar2
34 , p_stmt_id number
35 , p_message varchar2
36 )
37 as
38 begin
39 bis_collection_utilities.log( g_pkg_name || '.' || p_proc_name ||
40 ' #' || p_stmt_id || ' ' ||
41 p_message
42 , 3 );
43 end logger;
44
45 function get_schema_name
46 ( x_schema_name out nocopy varchar2
47 , x_error_message out nocopy varchar2 )
48 return number as
49
50 l_isc_schema varchar2(30);
51 l_status varchar2(30);
52 l_industry varchar2(30);
53
54 begin
55
56 if fnd_installation.get_app_info('ISC', l_status, l_industry, l_isc_schema) then
57 x_schema_name := l_isc_schema;
58 else
59 x_error_message := 'FND_INSTALLATION.GET_APP_INFO returned false';
60 return -1;
61 end if;
62
63 return 0;
64
65 exception
66 when others then
67 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
68 return -1;
69
70 end get_schema_name;
71
72 function truncate_table
73 ( p_isc_schema in varchar2
74 , p_table_name in varchar2
75 , x_error_message out nocopy varchar2 )
76 return number as
77
78 begin
79
80 execute immediate 'truncate table ' || p_isc_schema || '.' || p_table_name;
81
82 return 0;
83
84 exception
85 when others then
86 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
87 return -1;
88
89 end truncate_table;
90
91 function gather_statistics
92 ( p_isc_schema in varchar2
93 , p_table_name in varchar2
94 , x_error_message out nocopy varchar2 )
95 return number as
96
97 begin
98
99 fnd_stats.gather_table_stats( ownname => p_isc_schema
100 , tabname => p_table_name
101 );
102
103 return 0;
104
105 exception
106 when others then
107 x_error_message := 'Error in function gather_statistics : ' || sqlerrm;
108 return -1;
109
110 end gather_statistics;
111
112 function compute_cost_conv_rates
113 ( p_isc_schema in varchar2
114 , x_error_message out nocopy varchar2 )
115 return number as
116
117 l_proc_name constant varchar2(30):= 'compute_cost_conv_rates';
118 l_stmt_id number;
119
120 l_global_currency_code1 varchar2(15);
121 l_global_rate_type1 varchar2(15);
122 l_global_currency_code2 varchar2(15);
123 l_global_rate_type2 varchar2(15);
124
125 /* EURO currency became official on 01-JAN-1999 */
126 l_euro_start_date constant date := to_date ('01/01/1999', 'mm/dd/yyyy');
127
128 /* GL API returns -3 if EURO rate missing on 01-JAN-1999 */
129 l_euro_missing_at_start constant number := -3;
130
131 l_all_rates_found boolean;
132
133 -- Set up a cursor to get all the invalid rates.
134 -- By the logic of the fii_currency.get_global_rate_primary
135 -- API, the returned value is -ve if no rate exists:
136 -- -1 for dates with no rate.
137 -- -2 for unrecognized conversion rates.
138 -- Also, cross check with the org-date pairs in the staging table,
139 -- in case some orgs never had a functional currency code defined.
140 cursor c_invalid_rates is
141 select distinct
142 mp.organization_code
143 , decode( least( r.conversion_rate1, r.conversion_rate2 )
144 , l_euro_missing_at_start, l_euro_start_date
145 , r.transaction_date) transaction_date
146 , r.base_currency_code
147 , nvl(r.conversion_rate1, -999) primary_rate
148 , nvl(r.conversion_rate2, -999) secondary_rate
149 from
150 isc_maint_wo_cst_conv_rates r
151 , mtl_parameters mp
152 , ( select /*+ index_ffs(isc_maint_wo_cst_sum_stg) */ distinct
153 organization_id
154 , completion_date
155 from isc_maint_wo_cst_sum_stg
156 ) s
157 where ( nvl(r.conversion_rate1, -999) < 0 or
158 nvl(r.conversion_rate2, -999) < 0 )
159 and mp.organization_id = s.organization_id
160 and r.transaction_date (+) = s.completion_date
161 and r.organization_id (+) = s.organization_id;
162
163 l_exception exception;
164 l_error_message varchar2(4000);
165 l_rowcount number;
166
167 begin
168
169 bis_collection_utilities.log( 'Begin Currency Conversion', 1 );
170
171 -- get the primary global currency code
172 l_stmt_id := 10;
173 l_global_currency_code1 := bis_common_parameters.get_currency_code;
174 if l_global_currency_code1 is null then
175 l_error_message := 'Unable to get primary global currency code.'; -- translatable message?
176 logger( l_proc_name, l_stmt_id, l_error_message );
177 raise l_exception;
178 end if;
179
180 bis_collection_utilities.log( 'Primary global currency code: ' || l_global_currency_code1, 2 );
181
182 -- get the primary global rate type
183 l_stmt_id := 20;
184 l_global_rate_type1 := bis_common_parameters.get_rate_type;
185 if l_global_rate_type1 is null then
186 l_error_message := 'Unable to get primary global rate type.'; -- translatable message?
187 logger( l_proc_name, l_stmt_id, l_error_message );
188 raise l_exception;
189 end if;
190
191 bis_collection_utilities.log( 'Primary global rate type: ' || l_global_rate_type1, 2 );
192
193 -- get the secondary global currency code
194 l_stmt_id := 30;
195 l_global_currency_code2 := bis_common_parameters.get_secondary_currency_code;
196
197 if l_global_currency_code2 is not null then
198 bis_collection_utilities.log( 'Secondary global currency code: ' || l_global_currency_code2, 2 );
199 else
200 bis_collection_utilities.log( 'Secondary global currency code is not defined', 2 );
201 end if;
202
203 -- get the secondary global rate type
204 l_stmt_id := 40;
205 l_global_rate_type2 := bis_common_parameters.get_secondary_rate_type;
206 if l_global_rate_type2 is null and l_global_currency_code2 is not null then
207 l_error_message := 'Unable to get secondary global rate type.'; -- translatable message?
208 logger( l_proc_name, l_stmt_id, l_error_message );
209 raise l_exception;
210 end if;
211
212 if l_global_currency_code2 is not null then
213 bis_collection_utilities.log( 'Secondary global rate type: ' || l_global_rate_type2, 2 );
214 end if;
215
216 -- truncate the conversion rates work table
217 l_stmt_id := 50;
218 if truncate_table
219 ( p_isc_schema
220 , 'ISC_MAINT_WO_CST_CONV_RATES'
221 , l_error_message ) <> 0 then
222 logger( l_proc_name, l_stmt_id, l_error_message );
223 raise l_exception;
224 end if;
225
226 bis_collection_utilities.log( 'Currency conversion table truncated', 2 );
227
228 -- Get all the distinct organization and date pairs and the
229 -- base currency codes for the orgs into the conversion rates
230 -- work table.
231
232 -- Use the fii_currency.get_global_rate_primary function to get the
233 -- conversion rate given a currency code and a date.
234 -- only attempt to get conversion rate for rows that are complete
235 -- (have complete_flag = 'Y')
236 --
237 -- The function returns:
238 -- 1 for currency code when is the global currency
239 -- -1 for dates for which there is no currency conversion rate
240 -- -2 for unrecognized currency conversion rates
241
242 -- By selecting distinct org and currency code from the gl_set_of_books
243 -- and hr_organization_information, take care of duplicate codes.
244
245 l_stmt_id := 60;
246 insert /*+ append */
247 into isc_maint_wo_cst_conv_rates
248 ( organization_id
249 , transaction_date
250 , base_currency_code
251 , conversion_rate1
252 , conversion_rate2
253 , creation_date
254 , last_update_date
255 , created_by
256 , last_updated_by
257 , last_update_login
258 , program_id
259 , program_login_id
260 , program_application_id
261 , request_id
262 )
263 select
264 s.organization_id
265 , s.completion_date
266 , c.currency_code
267 , decode( s.completed_flag -- only attempt conversion for real completion dates
268 , 'Y', fii_currency.get_global_rate_primary
269 ( c.currency_code
270 , s.completion_date )
271 , 0
272 ) conversion_rate1
276 , 'Y', fii_currency.get_global_rate_secondary
273 , decode( l_global_currency_code2
274 , null, 0 -- only attempt conversion if secondary currency defined
275 , decode( s.completed_flag -- only attempt conversion for real completion dates
277 ( c.currency_code
278 , s.completion_date )
279 , 0
280 )
281 ) conversion_rate2
282 , sysdate
283 , sysdate
284 , g_user_id
285 , g_user_id
286 , g_login_id
287 , g_program_id
288 , g_program_login_id
289 , g_program_application_id
290 , g_request_id
291 from
292 ( select /*+ index_ffs(isc_maint_wo_cst_sum_stg)
293 parallel_index(isc_maint_wo_cst_sum_stg) */ distinct
294 organization_id
295 , completion_date
296 , completed_flag
297 from
298 isc_maint_wo_cst_sum_stg
299 ) s
300 , ( select distinct
301 hoi.organization_id
302 , gsob.currency_code
303 from
304 hr_organization_information hoi
305 , gl_sets_of_books gsob
306 where hoi.org_information_context = 'Accounting Information'
307 and hoi.org_information1 = to_char(gsob.set_of_books_id)
308 ) c
309 where c.organization_id = s.organization_id;
310
311 l_rowcount := sql%rowcount;
312
313 commit;
314
315 bis_collection_utilities.log( l_rowcount || ' rows inserted into currency conversion table', 2 );
316
317 l_all_rates_found := true;
318
319 -- gather statistics on conversion rates table before returning
320 l_stmt_id := 70;
321 if gather_statistics
322 ( p_isc_schema
323 , 'ISC_MAINT_WO_CST_CONV_RATES'
324 , l_error_message ) <> 0 then
325 logger( l_proc_name, l_stmt_id, l_error_message );
326 raise l_exception;
327 end if;
328
329 bis_collection_utilities.log( 'Currency conversion table analyzed', 2 );
330
331 -- Check that all rates have been found and are non-negative.
332 -- If there is a problem, notify user.
333 l_stmt_id := 80;
334 for invalid_rate_rec in c_invalid_rates loop
335
336 -- print the header out
337 if c_invalid_rates%rowcount = 1 then
338 bis_collection_utilities.writeMissingRateHeader;
339 end if;
340
341 l_all_rates_found := false;
342
343 if invalid_rate_rec.primary_rate < 0 then
344 bis_collection_utilities.writeMissingRate
345 ( l_global_rate_type1
346 , invalid_rate_rec.base_currency_code
347 , l_global_currency_code1
348 , invalid_rate_rec.transaction_date );
349 end if;
350
351 if invalid_rate_rec.secondary_rate < 0 then
352 bis_collection_utilities.writeMissingRate
353 ( l_global_rate_type2
354 , invalid_rate_rec.base_currency_code
355 , l_global_currency_code2
356 , invalid_rate_rec.transaction_date );
357 end if;
358
359 end loop;
360
361 -- If all rates not found raise an exception
362 if not l_all_rates_found then
363 l_error_message := 'Missing currency rates exist.'; -- translatable message?
364 logger( l_proc_name, l_stmt_id, l_error_message );
365 raise l_exception;
366 end if;
367
368 bis_collection_utilities.log( 'End Currency Conversion', 1 );
369
370 return 0;
371
372 exception
373
374 when l_exception then
375 x_error_message := l_error_message;
376 return -1;
377
378 when others then
379 rollback;
383 return -1;
380 l_error_message := substr( sqlerrm, 1, 4000 );
381 logger( l_proc_name, l_stmt_id, l_error_message );
382 x_error_message := 'Load conversion rate computation failed'; -- translatable message?
384
385 end compute_cost_conv_rates;
386
387 function get_last_refresh_date
388 ( x_refresh_date out nocopy date
389 , x_error_message out nocopy varchar2 )
390 return number as
391
392 l_refresh_date date;
393
394 begin
395
396 l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(g_object_name));
397 if l_refresh_date = g_global_start_date then
398 x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
399 return -1;
400 end if;
401
402 x_refresh_date := l_refresh_date;
403 return 0;
404
405 exception
406 when others then
407 x_error_message := 'Error in function get_last_refresh_date : ' || sqlerrm;
408 return -1;
412
409
410 end get_last_refresh_date;
411
413 -- -------------------------------------------------------------------
414 -- PUBLIC PROCEDURES
415 -- -------------------------------------------------------------------
416 procedure initial_load
417 ( errbuf out nocopy varchar2
418 , retcode out nocopy number
419 )
420 as
421
422 l_proc_name constant varchar2(30) := 'initial_load';
423 l_stmt_id number;
424 l_exception exception;
425 l_error_message varchar2(4000);
426 l_isc_schema varchar2(100);
427
428 l_timer number;
429 l_rowcount number;
430 l_temp_rowcount number;
431
432 l_collect_from_date date;
433 l_collect_to_date date;
434
435 type t_number_tab is table of number;
436 l_organization_tbl t_number_tab;
437 l_work_order_tbl t_number_tab;
438
439 begin
440
441 local_init;
442
443 bis_collection_utilities.log( 'Begin Initial Load' );
444
445 l_stmt_id := 0;
446 if not bis_collection_utilities.setup( g_object_name ) then
447 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
448 logger( l_proc_name, l_stmt_id, l_error_message );
449 raise g_bis_setup_exception;
450 end if;
451
452 -- determine the date we last collected to
453 l_stmt_id := 10;
454 if g_global_start_date is null then
455 l_error_message := 'Unable to get DBI global start date.'; -- translatable message?
456 logger( l_proc_name, l_stmt_id, l_error_message );
457 raise l_exception;
458 end if;
459
460 l_collect_from_date := g_global_start_date;
461 l_collect_to_date := sysdate;
462
463 bis_collection_utilities.log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
464 bis_collection_utilities.log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
465
466 -- get the isc schema name
467 l_stmt_id := 20;
468 if get_schema_name
469 ( l_isc_schema
470 , l_error_message ) <> 0 then
471 logger( l_proc_name, l_stmt_id, l_error_message );
472 raise l_exception;
473 end if;
474
475 -- truncate the staging table
476 l_stmt_id := 30;
477 if truncate_table
478 ( l_isc_schema
479 , 'ISC_MAINT_WO_CST_SUM_STG'
480 , l_error_message ) <> 0 then
481 logger( l_proc_name, l_stmt_id, l_error_message );
482 raise l_exception;
483 end if;
484
485 bis_collection_utilities.log( 'Staging table truncated', 1 );
486
487 -- ------------------------------------------------------------
488 -- this inserts into the staging table the "current"
489 -- total actual and estimated costs for completed or closed
490 -- work orders when nvl(completion_date,closed_date) on or
491 -- after global start date.
492 -- only cancelled work orders my be closed with no completion
493 -- date -- to be confirmed.
494 -- ------------------------------------------------------------
495 l_stmt_id := 40;
496 insert /*+ append parallel(s) */
497 into isc_maint_wo_cst_sum_stg s
498 ( organization_id
499 , work_order_id
500 , department_id
501 , maint_cost_category
502 , estimated_flag
503 , completion_date
504 , completed_flag
505 , actual_mat_cost_b
506 , actual_lab_cost_b
507 , actual_eqp_cost_b
508 , estimated_mat_cost_b
509 , estimated_lab_cost_b
510 , estimated_eqp_cost_b
511 , creation_date
512 , created_by
513 , last_update_date
514 , last_updated_by
515 , last_update_login
516 , program_id
517 , program_login_id
518 , program_application_id
519 , request_id
520 )
521 select /*+ parallel(wo) parallel(pb) */
522 wo.organization_id
523 , wo.work_order_id
524 , nvl(pb.operations_dept_id,-1) -- nvl'd so merge stmt join is successful
525 , nvl(pb.maint_cost_category,-1)-- nvl'd so merge stmt join is successful
526 , decode( sum( sum( abs(pb.system_estimated_mat_cost)
527 + abs(pb.system_estimated_lab_cost)
528 + abs(pb.system_estimated_eqp_cost)
529 )
530 ) over( partition by wo.organization_id, wo.work_order_id )
531 , 0, 'N'
532 , 'Y') estimated_flag
533 , nvl(wo.completion_date,wo.closed_date)
534 , 'Y'
535 , sum(pb.actual_mat_cost)
536 , sum(pb.actual_lab_cost)
537 , sum(pb.actual_eqp_cost)
538 , sum(pb.system_estimated_mat_cost)
539 , sum(pb.system_estimated_lab_cost)
540 , sum(pb.system_estimated_eqp_cost)
541 , sysdate
542 , g_user_id
543 , sysdate
544 , g_user_id
545 , g_login_id
546 , g_program_id
547 , g_program_login_id
548 , g_program_application_id
549 , g_request_id
550 from
551 isc_maint_work_orders_f wo
552 , wip_eam_period_balances pb
553 where
554 wo.work_order_id = pb.wip_entity_id
555 and wo.organization_id = pb.organization_id
556 and nvl(wo.completion_date, wo.closed_date) >= g_global_start_date
557 and wo.status_type in (4, 5, 12)
558 group by
559 wo.organization_id
560 , wo.work_order_id
561 , nvl(pb.operations_dept_id,-1)
562 , nvl(pb.maint_cost_category,-1)
563 , nvl(wo.completion_date,wo.closed_date);
564
565 l_rowcount := sql%rowcount;
566
567 bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
568
569 -- gather statistics on staging table before computing
570 -- conversion rates
571 l_stmt_id := 50;
572 if gather_statistics
573 ( l_isc_schema
574 , 'ISC_MAINT_WO_CST_SUM_STG'
575 , l_error_message ) <> 0 then
579
576 logger( l_proc_name, l_stmt_id, l_error_message );
577 raise l_exception;
578 end if;
580 bis_collection_utilities.log( 'Staging table analyzed', 1 );
581
582 -- check currency conversion rates
583 l_stmt_id := 60;
584 if compute_cost_conv_rates
585 ( l_isc_schema
586 , l_error_message ) <> 0 then
587 logger( l_proc_name, l_stmt_id, l_error_message );
588 raise l_exception;
589 end if;
590
591 -- truncate the fact table
592 l_stmt_id := 70;
593 if truncate_table
594 ( l_isc_schema
595 , 'ISC_MAINT_WO_CST_SUM_F'
596 , l_error_message ) <> 0 then
597 logger( l_proc_name, l_stmt_id, l_error_message );
598 raise l_exception;
599 end if;
600
601 bis_collection_utilities.log( 'Base summary table truncated', 1 );
602
603 -- insert into base fact from staging table
604 l_stmt_id := 80;
605 insert /*+ append parallel(f) */
606 into isc_maint_wo_cst_sum_f f
607 ( organization_id
608 , work_order_id
609 , department_id
610 , maint_cost_category
611 , estimated_flag
612 , completion_date
613 , conversion_rate1
614 , conversion_rate2
615 , actual_mat_cost_b
616 , actual_lab_cost_b
617 , actual_eqp_cost_b
618 , estimated_mat_cost_b
619 , estimated_lab_cost_b
620 , estimated_eqp_cost_b
621 , creation_date
622 , created_by
623 , last_update_date
624 , last_updated_by
625 , last_update_login
626 , program_id
627 , program_login_id
628 , program_application_id
629 , request_id
630 )
631 select /*+ parallel(s) parallel(c) */
632 s.organization_id
633 , s.work_order_id
634 , s.department_id
635 , s.maint_cost_category
636 , s.estimated_flag
637 , s.completion_date
638 , c.conversion_rate1
639 , decode( c.conversion_rate2
640 , 0, null
641 , c.conversion_rate2 )
642 , s.actual_mat_cost_b
643 , s.actual_lab_cost_b
644 , s.actual_eqp_cost_b
645 , s.estimated_mat_cost_b
646 , s.estimated_lab_cost_b
647 , s.estimated_eqp_cost_b
648 , sysdate
649 , g_user_id
650 , sysdate
651 , g_user_id
652 , g_login_id
653 , g_program_id
654 , g_program_login_id
655 , g_program_application_id
656 , g_request_id
657 from
658 isc_maint_wo_cst_sum_stg s
659 , isc_maint_wo_cst_conv_rates c
660 where
661 c.organization_id = s.organization_id
662 and c.transaction_date = s.completion_date;
663
664 l_rowcount := sql%rowcount;
665
666 commit;
667
668 bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
669
670 -- house keeping -- cleanup staging/currency conversion tables
671 l_stmt_id := 90;
672 if truncate_table
673 ( l_isc_schema
674 , 'ISC_MAINT_WO_CST_SUM_STG'
675 , l_error_message ) <> 0 then
676 logger( l_proc_name, l_stmt_id, l_error_message );
677 raise l_exception;
678 end if;
679
680 bis_collection_utilities.log( 'Staging table truncated', 1 );
681
682 l_stmt_id := 100;
683 if truncate_table
684 ( l_isc_schema
685 , 'ISC_MAINT_WO_CST_CONV_RATES'
686 , l_error_message ) <> 0 then
687 logger( l_proc_name, l_stmt_id, l_error_message );
688 raise l_exception;
689 end if;
690
691 bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
692
693 l_stmt_id := 110;
694 bis_collection_utilities.wrapup( p_status => true
695 , p_period_from => l_collect_from_date
696 , p_period_to => l_collect_to_date
697 , p_count => l_rowcount
698 );
699
700 bis_collection_utilities.log('End Initial Load');
701
702 errbuf := null;
703 retcode := g_success;
704
705 exception
706 when g_bis_setup_exception then
707 rollback;
708 errbuf := l_error_message;
709 retcode := g_error;
710
711 when others then
712 rollback;
713 if l_error_message is null then
714 l_error_message := substr(sqlerrm,1,4000);
715 end if;
716 bis_collection_utilities.wrapup( p_status => false
717 , p_message => l_error_message
718 , p_period_from => l_collect_from_date
719 , p_period_to => l_collect_to_date
720 );
721 errbuf := l_error_message;
722 retcode := g_error;
723
724 end initial_load;
728 , retcode out nocopy number
725
726 procedure incremental_load
727 ( errbuf out nocopy varchar2
729 )
730 as
731
732 l_proc_name constant varchar2(30) := 'incremental_load';
733 l_stmt_id number;
734 l_exception exception;
735 l_error_message varchar2(4000);
736 l_isc_schema varchar2(100);
737
738 l_timer number;
739 l_rowcount number;
740 l_temp_rowcount number;
741
742 l_collect_from_date date;
743 l_collect_to_date date;
744
745 begin
746
747 local_init;
748
749 bis_collection_utilities.log( 'Begin Incremental Load' );
750
751 l_stmt_id := 0;
752 if not bis_collection_utilities.setup( g_object_name ) then
753 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
754 logger( l_proc_name, l_stmt_id, l_error_message );
755 raise g_bis_setup_exception;
756 end if;
757
758 -- determine the date we last collected to
759 l_stmt_id := 10;
760 if get_last_refresh_date(l_collect_to_date, l_error_message) <> 0 then
761 logger( l_proc_name, l_stmt_id, l_error_message );
762 raise l_exception;
763 end if;
764 l_collect_from_date := l_collect_to_date + 1/86400;
765 l_collect_to_date := sysdate;
766
767 bis_collection_utilities.log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
768 bis_collection_utilities.log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
769
770 -- get the isc schema name
771 l_stmt_id := 20;
772 if get_schema_name
773 ( l_isc_schema
774 , l_error_message ) <> 0 then
775 logger( l_proc_name, l_stmt_id, l_error_message );
776 raise l_exception;
777 end if;
778
779 -- truncate the staging table
780 l_stmt_id := 30;
781 if truncate_table
782 ( l_isc_schema
783 , 'ISC_MAINT_WO_CST_SUM_STG'
784 , l_error_message ) <> 0 then
788
785 logger( l_proc_name, l_stmt_id, l_error_message );
786 raise l_exception;
787 end if;
789 bis_collection_utilities.log( 'Staging table truncated', 1 );
790
791 -- ------------------------------------------------------------
792 -- this inserts into the staging table the "current"
793 -- total actual and estimated costs for completed or closed
794 -- work orders where the actual or estimated costs have been
795 -- updated since the last collection OR
796 -- the nvl(completion_date,closed_date) <> completion_date
797 -- on base summary and the work order fact has been updated
798 -- since the last collection
799 -- ------------------------------------------------------------
800 l_stmt_id := 40;
801 insert /*+ append */
802 into isc_maint_wo_cst_sum_stg
803 ( organization_id
804 , work_order_id
805 , department_id
806 , maint_cost_category
807 , estimated_flag
808 , completion_date
809 , completed_flag
810 , actual_mat_cost_b
811 , actual_lab_cost_b
812 , actual_eqp_cost_b
813 , estimated_mat_cost_b
814 , estimated_lab_cost_b
815 , estimated_eqp_cost_b
816 , creation_date
817 , created_by
818 , last_update_date
819 , last_updated_by
820 , last_update_login
821 , program_id
822 , program_login_id
823 , program_application_id
824 , request_id
825 )
826 select
827 organization_id
828 , work_order_id
829 , nvl(operations_dept_id,-1) -- nvl'd so merge stmt join is successful
830 , nvl(maint_cost_category,-1)-- nvl'd so merge stmt join is successful
831 , decode( sum( sum( abs(system_estimated_mat_cost)
832 + abs(system_estimated_lab_cost)
833 + abs(system_estimated_eqp_cost)
834 )
835 ) over( partition by organization_id, work_order_id )
836 , 0, 'N'
837 , 'Y') estimated_flag
838 , decode( status_type
839 , 4, completion_date -- complete
840 , 5, completion_date -- complete - no charges
841 , 12, nvl(completion_date,closed_date) -- closed
842 , g_max_date )
843 , decode( status_type
844 , 4, 'Y'
845 , 5, 'Y'
846 , 12, 'Y'
847 , 'N' )
848 , sum(actual_mat_cost)
849 , sum(actual_lab_cost)
850 , sum(actual_eqp_cost)
851 , sum(system_estimated_mat_cost)
852 , sum(system_estimated_lab_cost)
853 , sum(system_estimated_eqp_cost)
854 , sysdate
855 , g_user_id
856 , sysdate
857 , g_user_id
858 , g_login_id
859 , g_program_id
860 , g_program_login_id
861 , g_program_application_id
862 , g_request_id
863 from
864 ( select
865 wo.organization_id
869 , wo.closed_date
866 , wo.work_order_id
867 , wo.status_type
868 , wo.completion_date
870 , pb.operations_dept_id
871 , pb.maint_cost_category
872 , pb.actual_mat_cost
873 , pb.actual_lab_cost
874 , pb.actual_eqp_cost
875 , pb.system_estimated_mat_cost
876 , pb.system_estimated_lab_cost
877 , pb.system_estimated_eqp_cost
878 from
879 wip_eam_period_balances pb
880 , (
881 -- identifies all completed/closed work orders that
882 -- have at least on cost balance row updated since
883 -- last collection
884 select
885 wo.organization_id
886 , wo.work_order_id
887 , wo.status_type
888 , wo.completion_date
889 , wo.closed_date
890 from
891 isc_maint_work_orders_f wo
892 , wip_eam_period_balances pb
893 where
894 pb.last_update_date >= l_collect_from_date
895 and wo.work_order_id = pb.wip_entity_id
896 and wo.organization_id = pb.organization_id
897 and wo.status_type in (4, 5, 12)
898 --
899 union
900 --
901 -- identifies all work orders that have been updated since last
902 -- collection whos completion_date differs from the previously
903 -- recorded completion_date (this allows us to verify the
904 -- currency conversion for all changed completion_dates also
905 -- allows us to clear completion_date for "un-completed" work
906 -- orders)
907 -- and identifies all completed or closed work orders that have
908 -- been re-estimated since last collection (this allows us to
909 -- catch work orders that have had a resource estimated deleted
910 -- that resulted in a row being deleted from period balances)
911 select
912 wo.organization_id
913 , wo.work_order_id
914 , wo.status_type
915 , wo.completion_date
916 , wo.closed_date
917 from
918 isc_maint_work_orders_f wo
919 , wip_eam_period_balances pb
920 , isc_maint_wo_cst_sum_f f
921 where
922 wo.last_update_date >= l_collect_from_date
923 and ( nvl(nvl(wo.completion_date,closed_date),g_max_date) <> nvl(f.completion_date,g_max_date) or
924 -- this is necessary to pick up cost for any work order where the estimated
925 -- resource was deleted this resulted in a
926 ( wo.last_estimation_date >= l_collect_from_date and
927 nvl(wo.completion_date,closed_date) is not null )
928 )
929 and wo.work_order_id = pb.wip_entity_id
930 and wo.organization_id = pb.organization_id
931 -- need to outer join here to ensure we pick up completed work order that
932 -- may not already exist in the wo cst base summary
933 and wo.work_order_id = f.work_order_id(+)
934 and wo.organization_id = f.organization_id(+)
935 ) wo
936 where
937 wo.work_order_id = pb.wip_entity_id
938 and wo.organization_id = pb.organization_id
939 --
940 union all
941 --
942 -- returns an all "zero" cost row from isc_maint_wo_cst_sum_f
943 -- for all completed/closed work orders have been updated since
944 -- last collection and have been re-estimated since last collection
945 -- (this allows us to catch work orders that have had a resource
946 -- estimated deleted that resulted in a row being deleted from
947 -- period balances and zero out the isc_maint_wo_cst_sum_f row)
948 select
949 wo.organization_id
950 , wo.work_order_id
951 , wo.status_type
955 , f.maint_cost_category
952 , wo.completion_date
953 , wo.closed_date
954 , f.department_id
956 , 0 actual_mat_cost
957 , 0 actual_lab_cost
958 , 0 actual_eqp_cost
959 , 0 system_estimated_mat_cost
960 , 0 system_estimated_lab_cost
961 , 0 system_estimated_eqp_cost
962 from
963 isc_maint_work_orders_f wo
964 , isc_maint_wo_cst_sum_f f
965 where
966 wo.status_type in (4, 5, 12)
967 and wo.last_update_date >= l_collect_from_date
968 and wo.last_estimation_date >= l_collect_from_date
969 and wo.work_order_id = f.work_order_id
970 and wo.organization_id = f.organization_id
971 )
972 group by
973 organization_id
974 , work_order_id
975 , nvl(operations_dept_id,-1) -- nvl'd so merge stmt join is successful
976 , nvl(maint_cost_category,-1)-- nvl'd so merge stmt join is successful
977 , decode( status_type
978 , 4, completion_date
979 , 5, completion_date
980 , 12, nvl(completion_date,closed_date)
981 , g_max_date )
982 , decode( status_type
983 , 4, 'Y'
984 , 5, 'Y'
985 , 12, 'Y'
986 , 'N' );
987
988 l_rowcount := sql%rowcount;
989
990 bis_collection_utilities.log( l_rowcount || ' rows inserted into staging table', 1 );
991
992 -- gather statistics on staging table before computing
993 -- conversion rates
994 l_stmt_id := 50;
995 if gather_statistics
996 ( l_isc_schema
997 , 'ISC_MAINT_WO_CST_SUM_STG'
998 , l_error_message ) <> 0 then
999 logger( l_proc_name, l_stmt_id, l_error_message );
1000 raise l_exception;
1001 end if;
1002
1003 bis_collection_utilities.log( 'Staging table analyzed', 1 );
1004
1005 -- check currency conversion rates
1006 l_stmt_id := 60;
1007 if compute_cost_conv_rates
1008 ( l_isc_schema
1009 , l_error_message ) <> 0 then
1010 logger( l_proc_name, l_stmt_id, l_error_message );
1011 raise l_exception;
1012 end if;
1013
1014 -- merge staging table into base fact
1015 l_stmt_id := 70;
1016 merge into isc_maint_wo_cst_sum_f f
1017 using
1018 ( select
1019 s.organization_id
1020 , s.work_order_id
1021 , s.department_id
1022 , s.maint_cost_category
1023 , s.estimated_flag
1024 , decode( s.completed_flag
1025 , 'Y', s.completion_date
1026 , null ) completion_date
1027 , decode( s.completed_flag
1028 , 'Y', c.conversion_rate1
1029 , null ) conversion_rate1
1030 , decode( s.completed_flag
1031 , 'Y', decode( c.conversion_rate2
1032 , 0, null
1033 , c.conversion_rate2
1034 )
1035 , null ) conversion_rate2
1036 , decode( s.completed_flag
1037 , 'Y', s.actual_mat_cost_b
1038 , null ) actual_mat_cost_b
1039 , decode( s.completed_flag
1040 , 'Y', s.actual_lab_cost_b
1041 , null ) actual_lab_cost_b
1042 , decode( s.completed_flag
1043 , 'Y', s.actual_eqp_cost_b
1044 , null ) actual_eqp_cost_b
1045 , decode( s.completed_flag
1046 , 'Y', s.estimated_mat_cost_b
1047 , null ) estimated_mat_cost_b
1048 , decode( s.completed_flag
1049 , 'Y', s.estimated_lab_cost_b
1050 , null ) estimated_lab_cost_b
1051 , decode( s.completed_flag
1052 , 'Y', s.estimated_eqp_cost_b
1053 , null ) estimated_eqp_cost_b
1054 , sysdate creation_date
1055 , g_user_id created_by
1056 , sysdate last_update_date
1057 , g_user_id last_updated_by
1058 , g_login_id last_update_login
1059 , g_program_id program_id
1060 , g_program_login_id program_login_id
1061 , g_program_application_id program_application_id
1062 , g_request_id request_id
1063 from
1064 isc_maint_wo_cst_sum_stg s
1065 , isc_maint_wo_cst_conv_rates c
1066 where
1067 c.organization_id = s.organization_id
1068 and c.transaction_date = s.completion_date
1069 ) s
1070 on
1071 ( f.organization_id = s.organization_id
1072 and f.work_order_id = s.work_order_id
1073 and f.department_id = s.department_id
1074 and f.maint_cost_category = s.maint_cost_category
1075 )
1076 when matched then update
1077 set
1078 f.estimated_flag = s.estimated_flag
1079 , f.completion_date = s.completion_date
1080 , f.conversion_rate1 = s.conversion_rate1
1081 , f.conversion_rate2 = s.conversion_rate2
1082 , f.actual_mat_cost_b = s.actual_mat_cost_b
1083 , f.actual_lab_cost_b = s.actual_lab_cost_b
1084 , f.actual_eqp_cost_b = s.actual_eqp_cost_b
1085 , f.estimated_mat_cost_b = s.estimated_mat_cost_b
1086 , f.estimated_lab_cost_b = s.estimated_lab_cost_b
1087 , f.estimated_eqp_cost_b = s.estimated_eqp_cost_b
1088 , f.last_update_date = s.last_update_date
1089 , f.last_updated_by = s.last_updated_by
1090 , f.last_update_login = s.last_update_login
1091 , f.program_id = s.program_id
1092 , f.program_login_id = s.program_login_id
1093 , f.program_application_id = s.program_application_id
1094 , f.request_id = s.request_id
1095 when not matched then insert
1096 ( organization_id
1097 , work_order_id
1098 , department_id
1099 , maint_cost_category
1100 , estimated_flag
1101 , completion_date
1105 , actual_lab_cost_b
1102 , conversion_rate1
1103 , conversion_rate2
1104 , actual_mat_cost_b
1106 , actual_eqp_cost_b
1107 , estimated_mat_cost_b
1108 , estimated_lab_cost_b
1109 , estimated_eqp_cost_b
1110 , creation_date
1111 , created_by
1112 , last_update_date
1113 , last_updated_by
1114 , last_update_login
1115 , program_id
1116 , program_login_id
1117 , program_application_id
1118 , request_id
1119 )
1120 values
1121 ( s.organization_id
1122 , s.work_order_id
1123 , s.department_id
1124 , s.maint_cost_category
1125 , s.estimated_flag
1126 , s.completion_date
1127 , s.conversion_rate1
1128 , s.conversion_rate2
1129 , s.actual_mat_cost_b
1130 , s.actual_lab_cost_b
1131 , s.actual_eqp_cost_b
1132 , s.estimated_mat_cost_b
1133 , s.estimated_lab_cost_b
1134 , s.estimated_eqp_cost_b
1135 , s.creation_date
1136 , s.created_by
1137 , s.last_update_date
1138 , s.last_updated_by
1139 , s.last_update_login
1140 , s.program_id
1141 , s.program_login_id
1142 , s.program_application_id
1143 , s.request_id
1144 );
1145
1146 l_rowcount := sql%rowcount;
1147
1148 commit;
1149
1150 bis_collection_utilities.log( l_rowcount || ' rows merged into base summary', 1 );
1151
1152 -- house keeping -- cleanup staging/currency conversion tables
1153 l_stmt_id := 80;
1154 if truncate_table
1155 ( l_isc_schema
1156 , 'ISC_MAINT_WO_CST_SUM_STG'
1157 , l_error_message ) <> 0 then
1158 logger( l_proc_name, l_stmt_id, l_error_message );
1159 raise l_exception;
1160 end if;
1161
1162 bis_collection_utilities.log( 'Staging table truncated', 1 );
1163
1164 l_stmt_id := 90;
1165 if truncate_table
1166 ( l_isc_schema
1167 , 'ISC_MAINT_WO_CST_CONV_RATES'
1168 , l_error_message ) <> 0 then
1169 logger( l_proc_name, l_stmt_id, l_error_message );
1170 raise l_exception;
1171 end if;
1172
1173 bis_collection_utilities.log( 'Currency conversion table truncated', 1 );
1174
1175 l_stmt_id := 100;
1176 bis_collection_utilities.wrapup( p_status => true
1177 , p_period_from => l_collect_from_date
1178 , p_period_to => l_collect_to_date
1179 , p_count => l_rowcount
1180 );
1181
1182 bis_collection_utilities.log('End Incremental Load');
1183
1184 errbuf := null;
1185 retcode := g_success;
1186
1187
1188 exception
1189 when g_bis_setup_exception then
1190 rollback;
1191 errbuf := l_error_message;
1192 retcode := g_error;
1193
1194 when others then
1195 rollback;
1196 if l_error_message is null then
1197 l_error_message := substr(sqlerrm,1,4000);
1198 end if;
1199 bis_collection_utilities.wrapup( p_status => false
1200 , p_message => l_error_message
1201 , p_period_from => l_collect_from_date
1202 , p_period_to => l_collect_to_date
1203 );
1204 errbuf := l_error_message;
1205 retcode := g_error;
1206
1207 end incremental_load;
1208
1209 end isc_maint_wo_cst_etl_pkg;