[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_ASSET_DT_ETL_PKG
Source
1 PACKAGE BODY ISC_MAINT_ASSET_DT_ETL_PKG as
2 /*$Header: iscmaintadtetlb.pls 120.4 2006/07/26 03:47:50 kreardon noship $ */
3 g_pkg_name constant varchar2(30) := 'isc_maint_asset_dt_etl_pkg';
4 g_user_id number;
5 g_login_id number;
6 g_program_id number;
7 g_program_login_id number;
8 g_program_application_id number;
9 g_request_id number;
10 g_success constant varchar2(10) := '0';
11 g_error constant varchar2(10) := '-1';
12 g_warning constant varchar2(10) := '1';
13 g_bis_setup_exception exception;
14 g_global_start_date date;
15 g_object_name constant varchar2(30) := 'ISC_MAINT_ASSET_DOWN_F';
16 g_max_date constant date := to_date('4712/01/01','yyyy/mm/dd');
17 l_max_date constant date := to_date('4712/12/31','yyyy/mm/dd');
18
19
20 procedure local_init -- sets up the user_id,login_id and the global start date.
21 as
22 begin
23 g_user_id := fnd_global.user_id;
24 g_login_id := fnd_global.login_id;
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 g_global_start_date := bis_common_parameters.get_global_start_date;
30 --g_global_start_date := to_date('0100/01/01','yyyy/mm/dd') ;
31 end local_init;
32
33 procedure logger
34 ( p_proc_name varchar2
35 , p_stmt_id number
36 , p_message varchar2
37 )
38 as
39
40 begin
41
42 bis_collection_utilities.log
43 ( substr( g_pkg_name || '.' || p_proc_name || ' #' || p_stmt_id || p_message
44 , 1
45 , 1991 -- [2000 - (3*3)]
46 )
47 , 3
48 );
49
50 end logger;
51
52 function get_schema_name -- returns the schema name of the object
53 (x_schema_name out nocopy varchar2
54 ,x_error_message out nocopy varchar2 )
55 return number as
56 l_biv_schema varchar2(30);
57 l_status varchar2(30);
58 l_industry varchar2(30);
59 begin
60 if(fnd_installation.get_app_info('ISC', l_status, l_industry, l_biv_schema)) then
61 x_schema_name := l_biv_schema;
62 else
63 x_error_message := 'FIND_INSTALLATION.GET_APP_INFO returned false';
64 return -1;
65 end if;
66 return 0;
67 exception
68 when others then
69 x_error_message := 'Error in function get_schema_name : ' || sqlerrm;
70 return -1;
71 end get_schema_name;
72
73
74 function truncate_table -- truncates the fact table.Should be called before initial load only
75 ( p_biv_schema in varchar2
76 , p_table_name in varchar2
77 , x_error_message out nocopy varchar2 )
78 return number as
79 begin
80 execute immediate 'truncate table ' || p_biv_schema || '.' || p_table_name;
81 return 0;
82 exception
83 when others then
84 x_error_message := 'Error in function truncate_table : ' || sqlerrm;
85 return -1;
86 end truncate_table;
87
88 function get_last_refresh_date -- gets the last refresh date of the fact table
89 ( x_refresh_date out nocopy date
90 , x_error_message out nocopy varchar2 )
91 return number as
92 l_refresh_date date;
93 begin
94 l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(g_object_name));
95 if l_refresh_date = g_global_start_date then
96 x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
97 x_error_message := x_error_message || ' Refresh Date is '|| l_refresh_date;
98 x_error_message := x_error_message || ' global start date is '|| g_global_start_date ;
99
100 return -1;
101 end if;
102 x_refresh_date := l_refresh_date;
103 return 0;
104 exception
105 when others then
106 x_error_message := 'Error in function get_last_refresh_date : ' || sqlerrm ;
107 return -1;
108 end get_last_refresh_date;
109
110 ------------------------------------------- Public procedures---------------------------------------------------------------
111 procedure initial_load -- Initial load program for Downtime.
112 (errbuf out nocopy varchar2
113 ,retcode out nocopy number
114 )
115 as
116 l_proc_name constant varchar2(30) := 'initial_load';
117 l_stmt_id number;
118 l_exception exception;
119 l_error_message varchar2(4000);
120 l_biv_schema varchar2(100);
121 l_timer number;
122 l_rowcount number;
123 l_temp_rowcount number;
124 l_collect_from_date date;
125 l_collect_to_date date;
126 type t_number_tab is table of number;
127 l_organization_tbl t_number_tab;
128 l_work_order_tbl t_number_tab;
129
130 begin
131 local_init;
132
133 bis_collection_utilities.log( 'Begin Initial Load' );
134
135 l_stmt_id := 0;
136 if not bis_collection_utilities.setup( g_object_name ) then
137 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
138 logger( l_proc_name, l_stmt_id, l_error_message );
139 raise g_bis_setup_exception;
140 end if;
141
142 l_stmt_id := 10;
143
144 if g_global_start_date is null then
145 l_error_message := 'Unable to get DBI global start date.';
146 logger( l_proc_name, l_stmt_id, l_error_message );
147 raise l_exception;
148 end if;
149
150 l_collect_from_date := g_global_start_date;
151 l_collect_to_date := sysdate;
152
153
154 -- get the biv schema name
155 l_stmt_id := 20;
156
157 if get_schema_name
158 (l_biv_schema
159 ,l_error_message ) <> 0 then
160 logger( l_proc_name, l_stmt_id, l_error_message );
161 raise l_exception;
162 end if;
163
164 -- truncate the Fact table
165
166 l_stmt_id := 30;
167
168 if truncate_table
169 (l_biv_schema
170 ,'ISC_MAINT_ASSET_DOWN_F'
171 ,l_error_message ) <> 0 then
172 logger( l_proc_name, l_stmt_id, l_error_message );
173 raise l_exception;
174 end if;
175
176
177 ---------------------------------------------initial load ----------------------------------
178
179 -- This inserts into the fact table all the asset downtime instances where the
180 -- completion date is on or after the global start date.
181 -- The start date for the calculation of the downtime hours is calculated on the DBI_start_date.
182
183 ---------------------------------------------the initial load query would come here----------
184
185
186
187
188 l_stmt_id := 40;
189
190 ------starts here------
191
192
193 Insert /*+ append parallel(f) */ into
194 ISC_MAINT_ASSET_DOWN_F f
195 (
196 asset_status_id
197 ,instance_id
198 ,asset_group_id
199 ,category_id
200 ,asset_criticality_code
201 ,organization_id
202 ,department_id
203 ,work_order_id
204 ,operation_seq_number
205 ,description
206 ,enable_flag
207 ,start_date
208 ,end_date
209 ,dbi_start_date
210 ,effective_start_date
211 ,effective_end_date
212 ,creation_date
213 ,created_by
214 ,last_update_date
215 ,last_updated_by
216 ,last_update_login
217 ,program_id
218 ,program_login_id
219 ,program_application_id
220 ,request_id
221 )
222
223 select /*+ parallel(msn) parallel(fact) */
224 fact.asset_status_id asset_status_id
225 ,fact.maintenance_object_id instance_id
226 ,fact.asset_group_id asset_group_id
227 ,nvl(cii.category_id,-1) category_id
228 ,nvl(cii.asset_criticality_code,'-1') asset_criticality_code
229 ,fact.organization_id oraganization_id
230 ,nvl(eomd.owning_department_id,-1) department_id
231 ,fact.wip_entity_id work_order_id
232 ,fact.operation_seq_num operation_seq_number
233 ,fact.description shutdown_description
234 ,fact.enable_flag enable_flag
235 ,fact.start_date start_date
236 ,fact.end_date end_date
237 ,greatest(fact.start_date,g_global_start_date)
238 dbi_start_date
239 ,greatest(fact.effective_start_date,g_global_start_date)
240 effective_start_date
241 ,effective_end_date effective_end_date
242 ,sysdate creation_date
243 ,g_user_id created_by
244 ,sysdate last_update_date
245 ,g_user_id last_updated_by
246 ,g_login_id last_update_login
247 ,g_program_id program_id
248 ,g_program_login_id program_login_id
249 ,g_program_application_id program_application_id
250 ,g_request_id request_id
251 from
252 (
253 select
254 maintenance_object_id
255 ,start_date
256 ,end_date
257 ,asset_status_id
258 ,asset_group_id
259 ,organization_id
260 ,wip_entity_id
261 ,operation_seq_num
262 ,description
263 ,enable_flag
264 ,last_update_date
265 ,case
266 when effective_start_date >= effective_end_date then
267 null
268 else
269 effective_start_date
270 end
271 effective_start_date
272 ,case
273 when effective_start_date >= effective_end_date then
274 null
275 else
276 effective_end_date
277 end
278 effective_end_date
279 from
280 ( select
281 maintenance_object_id
282 ,start_date
283 ,end_date
284 ,asset_status_id
285 ,asset_group_id
286 ,organization_id
287 ,wip_entity_id
288 ,operation_seq_num
289 ,description
290 ,enable_flag
291 ,last_update_date
292 ,case
293 when start_date > lag(max_so_far,1,start_date-1)
294 over(partition by maintenance_object_id order by rn) then
295 start_date
296 else
297 lag(max_so_far,1) over(partition by maintenance_object_id order by rn)
298 end
299 effective_start_date
300 ,max_so_far effective_end_date
301
302 from
303 ( select /*+ parallel(EASH) */
304 maintenance_object_id
305 ,start_date
306 ,end_date
307 ,asset_status_id
308 ,asset_group_id
309 ,organization_id
310 ,wip_entity_id
311 ,operation_seq_num
312 ,description
313 ,nvl(enable_flag,'Y') enable_flag
314 ,last_update_date
315 ,min(start_date) over(partition by maintenance_object_id order by start_date, end_date)
316 min_so_far
317 , max(end_date) over(partition by maintenance_object_id order by start_date, end_date)
318 max_so_far
319 , row_number() over(partition by maintenance_object_id order by start_date, end_date)
320 rn
321 from EAM_ASSET_STATUS_HISTORY EASH where (enable_flag = 'Y' or enable_flag is NULL ) and
322 start_date <> end_date
323 and maintenance_object_type = 3
324 )
325 )
326
327 )fact
328 ,csi_item_instances cii /* table that contains the instance_id, category and criticality */
329 ,EAM_ORG_MAINT_DEFAULTS eomd /* table containing the owning department */
330
331 where
332 fact.maintenance_object_id = cii.instance_id
333 and fact.maintenance_object_id = eomd.object_id(+) /* an asset need not be owned to any dept */
334 and fact.organization_id = eomd.organization_id(+)
335 and eomd.object_type(+) = 50
336 and fact.end_date >=g_global_start_date;
337
338 l_rowcount := sql%rowcount;
339
340 commit;
341
342 ----ends here------
343
344
345 l_stmt_id := 90;
346
347 bis_collection_utilities.log( l_rowcount || ' rows inserted into base summary', 1 );
348
349 bis_collection_utilities.wrapup( p_status => true
350 , p_period_from => l_collect_from_date
351 , p_period_to => l_collect_to_date
352 , p_count => l_rowcount
353 );
354
355 bis_collection_utilities.log('End Initial Load');
356
357
358
359
360 errbuf := null;
361 retcode := g_success;
362 exception
363 when g_bis_setup_exception then
364 rollback;
365 errbuf := l_error_message;
366 retcode := g_error;
367 when others then
368 rollback;
369 if l_error_message is null then
370 l_error_message := substr(sqlerrm,1,4000);
371 end if;
372 logger( l_proc_name, l_stmt_id, l_error_message );
373 bis_collection_utilities.wrapup(p_status => false
374 ,p_message => l_error_message
375 ,p_period_from => l_collect_from_date
376 ,p_period_to => l_collect_to_date
377 );
378
379 errbuf := l_error_message;
380 retcode := g_error;
381
382 end initial_load; --- here we end the initial load
383
384 procedure incremental_load -- the incremental load procedure
385 (errbuf out nocopy varchar2
386 ,retcode out nocopy number
387 )
388 as
389
390 l_proc_name constant varchar2(30) := 'incremental_load';
391 l_stmt_id number;
392 l_exception exception;
393 l_error_message varchar2(4000);
394 l_biv_schema varchar2(100);
395 l_timer number;
396 l_rowcount number;
397 l_temp_rowcount number;
398 l_collect_from_date date;
399 l_collect_to_date date;
400
401 begin
402 local_init;
403
404 bis_collection_utilities.log( 'Begin Incremental Load' );
405
406 l_stmt_id := 0;
407
408 if not bis_collection_utilities.setup( g_object_name ) then
409 l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
410 logger( l_proc_name, l_stmt_id, l_error_message );
411 raise g_bis_setup_exception;
412 end if;
413
414 -- determine the date we last collected to
415
416 l_stmt_id := 10;
417
418 if get_last_refresh_date(l_collect_to_date, l_error_message) <> 0 then
419 logger( l_proc_name, l_stmt_id, l_error_message );
420 raise l_exception;
421 end if;
422
423 l_collect_from_date := l_collect_to_date + 1/86400;
424 l_collect_to_date := sysdate;
425
426 -- get the biv schema name
427
428 l_stmt_id := 20;
429
430 if get_schema_name
431 (l_biv_schema
432 ,l_error_message ) <> 0 then
433 logger( l_proc_name, l_stmt_id, l_error_message );
434 raise l_exception;
435 end if;
436
437 -- merge staging table into base fact
438 l_stmt_id := 30;
439
440 ----------------incremental load starts from here---------------
441
442 /* removed the alternate query given by the performance team */
443
444 merge into isc_maint_asset_down_f nbmaf
445 using
446 (
447 -- this inline view compares the new data set with the old data set and
448 -- only returns rows that have logically changed.
449 -- this is done to ensure that the merge inserts new downtime rows and
450 -- only existing rows that have actually changed.
451 select
452 o.rowid old_rowid
453 , n.asset_status_id
454 , n.instance_id
455 , n.asset_group_id
456 , n.category_id
457 , n.asset_criticality_code
458 , n.organization_id
459 , n.department_id
460 , n.work_order_id
461 , n.operation_seq_number
462 , n.description
463 , n.start_date
464 , n.end_date
465 , n.dbi_start_date
466 , n.effective_start_date
467 , n.effective_end_date
468 , n.enable_flag
469 from
470 (
471 -- this inline view contains the current state of all enable_flag rows and
472 -- any rows that have become disabled since the last collection
473 -- by joining isc_maint_asset_down_f data to mtl_serial_numbers data
474 -- we also rename the maintenance_object_id to instance_id to remain consistent
475 -- with all the other regions
476 select
477 a.asset_status_id
478 , a.maintenance_object_id instance_id
479 , a.asset_group_id
480 , nvl(cii.category_id,-1) category_id
481 , nvl(cii.asset_criticality_code,'-1') asset_criticality_code
482 , a.organization_id
483 , nvl(eomd.owning_department_id,-1) department_id
484 , a.wip_entity_id work_order_id
485 , a.operation_seq_num operation_seq_number
486 , a.description
487 , a.start_date
488 , a.end_date
489 , a.dbi_start_date
490 , a.effective_start_date
491 , a.effective_end_date
492 , a.enable_flag enable_flag
493 from
494 (
495 -- this inline view contains the current state of all enable_flag rows and
496 -- any rows that have become disabled since the last collection
497 -- based only on isc_maint_asset_down_f
498 select
499 asset_status_id
500 , maintenance_object_id
501 , asset_group_id
502 , organization_id
503 , wip_entity_id
504 , operation_seq_num
505 , description
506 , start_date
507 , end_date
508 , dbi_start_date
509 , effective_start_date
510 , effective_end_date
511 , enable_flag
512 from
513 (
514 -- this inline view nulls out effective start and effective end dates
515 -- where they are not meaningful, that is the downtime row is fully
516 -- overlapped by a prior downtime
517 select
518 asset_status_id
519 , maintenance_object_id
520 , asset_group_id
521 , organization_id
522 , wip_entity_id
523 , operation_seq_num
524 , description
525 , start_date
526 , end_date
527 , dbi_start_date
528 , case
529 when effective_start_date >= effective_end_date then
530 null
531 else
532 effective_start_date
533 end effective_start_date
534 , case
535 when effective_start_date >= effective_end_date then
536 null
537 else
538 effective_end_date
539 end effective_end_date
540 , enable_flag
541 from
542 (
543 -- this inline view calculates the effective start and effective end date.
544 -- - for each downtime by comparing the dbi start date of the current row
545 -- with the max so far date of the previous row. if it is less then they
546 -- overlap, it it is great then there is a new effective start date.
547 -- - or each downtime the end date is the max so far date
548 select
549 asset_status_id
550 , maintenance_object_id
551 , asset_group_id
552 , organization_id
553 , wip_entity_id
554 , operation_seq_num
555 , description
556 , start_date
557 , end_date
558 , dbi_start_date
559 , case
560 when enable_flag = 'Y' then
561 case
562 when dbi_start_date > lag(max_so_far,1,dbi_start_date-1)
563 over(partition by maintenance_object_id order by rn) then
564 dbi_start_date
565 else
566 lag(max_so_far,1) over(partition by maintenance_object_id order by rn)
567 end
568 else
569 null
570 end effective_start_date
571 , case
572 when enable_flag = 'Y' then
573 max_so_far
574 else
575 null
576 end effective_end_date
577 , enable_flag
578 from
579 (
580 -- this inline view identifies all enable_flag rows and all rows that have
581 -- been updated since last collection (newly disabled rows)
582 -- it only considers rows with an end date >= gsd
583 -- it assumes that data can no longer be updated, the existing row will
584 -- be disabled and an new row will be inserted.
585 --
586 -- - it calculates dbi_start_date
587 -- - it determines the min start date-to date (min_so_far) for each maintenance_object_id (asset)
588 -- - it determines the max end date-to date (max_so_far) for each maintenance_object_id (asset)
589 -- - it determines the logical order of the downtime rows for each maintenance_object_id (asset)
590 --
591 -- disabled rows are ranked last
592 --
593 select
594 asset_status_id
595 , maintenance_object_id
596 , asset_group_id
597 , organization_id
598 , wip_entity_id
599 , operation_seq_num
600 , description
601 , start_date
602 , end_date
603 , greatest(start_date,g_global_start_date) dbi_start_date
604 , nvl(enable_flag,'Y') enable_flag
605 , min(decode(nvl(enable_flag,'Y'),'Y',greatest(start_date,g_global_start_date),null))
606 over(partition by maintenance_object_id
607 order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) min_so_far
608 , max(decode(nvl(enable_flag,'Y'),'Y',end_date,null))
609 over(partition by maintenance_object_id
610 order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) max_so_far
611 , row_number()
612 over(partition by maintenance_object_id
613 order by decode(nvl(enable_flag,'Y'),'Y',start_date,null), decode(nvl(enable_flag,'Y'),'Y',end_date,null)) rn
614 from
615 ---
616 (
617 select * from eam_asset_status_history
618 where
619 (
620 ( creation_date > l_collect_from_date and nvl(enable_flag,'Y') = 'Y' )
621 or
622 ( (last_update_date > l_collect_from_date and creation_date < l_collect_from_date ) or nvl(enable_flag,'Y') = 'Y' )
623 )and end_date >= g_global_start_date and start_date <> end_date
624 and maintenance_object_type = 3
625
626 )
627 )
628 )
629 )
630 ) a
631 , csi_item_instances cii /* extract the instance_id,category and criticality of the asset */
632 , eam_org_maint_defaults eomd /* extract the department of the asset */
633 where
634 a.maintenance_object_id = cii.instance_id
635 and a.maintenance_object_id = eomd.object_id(+) /* department is not mandatory */
636 and eomd.object_type(+)= 50 /* bug 4750689 */
637 and a.organization_id = eomd.organization_id(+)
638 ) n
639 , isc_maint_asset_down_f o
640 where
641 n.asset_status_id = o.asset_status_id(+)
642 and ( o.asset_status_id is null or
643 n.category_id <> o.category_id or
644 n.asset_criticality_code <> o.asset_criticality_code or
645 n.organization_id <> o.organization_id or
646 n.department_id <> o.department_id or
647 nvl(n.work_order_id,-1) <> nvl(o.work_order_id,-1) or
648 nvl(n.operation_seq_number,-1) <> nvl(o.operation_seq_number,-1) or
649 nvl(n.description,'%%') <> nvl(o.description,'%%') or
650 n.start_date <> o.start_date or
651 n.end_date <> o.end_date or
652 n.dbi_start_date <> o.dbi_start_date or
653 nvl(n.effective_start_date,l_max_date) <> nvl(o.effective_start_date,l_max_date) or
654 nvl(n.effective_end_date,l_max_date) <> nvl(o.effective_end_date,l_max_date) or
655 nvl(n.enable_flag,'Y') <> nvl(o.enable_flag,'Y')
656 )
657 ) data
658 on
659 (
660 data.old_rowid = nbmaf.rowid
661 )
662 when matched then
663 update set
664 -- 3 rows from updation of the mtl_serial_numbers table.
665 nbmaf.category_id = data.category_id
666 , nbmaf.asset_criticality_code = data.asset_criticality_code
667 , nbmaf.department_id = data.department_id
668 -- 5 rows from updation of the eam_asset_status_history table.
669 , nbmaf.work_order_id = data.work_order_id
670 , nbmaf.operation_seq_number = data.operation_seq_number
671 , nbmaf.description = data.description
672 , nbmaf.start_date = data.start_date
673 , nbmaf.end_date = data.end_date
674 , nbmaf.dbi_start_date = data.dbi_start_date
675 -- 2 rows for the updation of the asset effective start and end dates due
676 -- to addition of the n row which might impact the asset effective
677 --- downtime.
678 , nbmaf.effective_start_date = data.effective_start_date
679 , nbmaf.effective_end_date = data.effective_end_date
680 , nbmaf.enable_flag = data.enable_flag
681 --- the standard who cols that are to be updated.
682 , nbmaf.last_update_date = sysdate
683 , nbmaf.last_updated_by = g_user_id
684 , nbmaf.last_update_login = g_login_id
685 , nbmaf.program_id = g_program_id
686 , nbmaf.program_login_id = g_program_login_id
687 , nbmaf.program_application_id = g_program_application_id
688 , nbmaf.request_id = g_request_id
689
690
691 when not matched then
692 insert
693 (
694 asset_status_id
695 , instance_id
696 , asset_group_id
697 , category_id
698 , asset_criticality_code
699 , organization_id
700 , department_id
701 , work_order_id
702 , operation_seq_number
703 , description
704 , enable_flag
705 , start_date
706 , end_date
707 , dbi_start_date
708 , effective_start_date
709 , effective_end_date
710 , creation_date
711 , created_by
712 , last_update_date
713 , last_updated_by
714 , last_update_login
715 , program_id
716 , program_login_id
717 , program_application_id
718 , request_id
719 )
720 values
721 (
722 data.asset_status_id
723 , data.instance_id
724 , data.asset_group_id
725 , data.category_id
726 , data.asset_criticality_code
727 , data.organization_id
728 , data.department_id
729 , data.work_order_id
730 , data.operation_seq_number
731 , data.description
732 , data.enable_flag
733 , data.start_date
734 , data.end_date
735 , data.dbi_start_date
736 , data.effective_start_date
737 , data.effective_end_date
738 , sysdate
739 , g_user_id
740 , sysdate
741 , g_user_id
742 , g_login_id
743 , g_program_id
744 , g_program_login_id
745 , g_program_application_id
746 , g_request_id
747 );
748
749
750
751 l_rowcount := sql%rowcount;
752 commit;
753
754
755 bis_collection_utilities.log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
756 bis_collection_utilities.log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
757 bis_collection_utilities.log( l_rowcount || ' rows merged into base summary', 1 );
758
759 l_stmt_id := 40;
760
761 bis_collection_utilities.wrapup( p_status => true
762 , p_period_from => l_collect_from_date
763 , p_period_to => l_collect_to_date
764 , p_count => l_rowcount
765 );
766 bis_collection_utilities.log('Incremental Load complete');
767
768 errbuf := null;
769 retcode := g_success;
770
771 exception
772 when g_bis_setup_exception then
773 rollback;
774 errbuf := l_error_message;
775 retcode := g_error;
776 when others then
777 rollback;
778
779 if l_error_message is null then
780 l_error_message := substr(sqlerrm,1,4000);
781 end if;
782
783 logger( l_proc_name, l_stmt_id, l_error_message );
784 bis_collection_utilities.wrapup( p_status => false
785 ,p_message => l_error_message
786 ,p_period_from => l_collect_from_date
787 ,p_period_to => l_collect_to_date
788 );
789 errbuf := l_error_message;
790 retcode := g_error;
791 ---------------- incremental load ends -----------------------------------------
792 end incremental_load;
793
794 end isc_maint_asset_dt_etl_pkg;