[Home] [Help]
PACKAGE BODY: APPS.ISC_MAINT_WO_ETL_PKG
Source
1 Package Body ISC_MAINT_WO_ETL_PKG AS
2 /*$Header: iscmaintwoetlb.pls 120.3 2006/01/03 03:04:30 nbhamidi noship $ */
3
4 g_sysdate DATE := SYSDATE;
5 g_user_id NUMBER := nvl(fnd_global.user_id, -1);
6 g_login_id NUMBER := nvl(fnd_global.login_id, -1);
7 g_global_start_date DATE := SYSDATE;
8 g_last_collection_date DATE;
9 g_ok NUMBER(1) := 0;
10 g_warning NUMBER(1) := 1;
11 g_error NUMBER(1) := -1;
12 g_program_id NUMBER := fnd_global.CONC_PROGRAM_ID;
13 g_program_login_id NUMBER := fnd_global.CONC_LOGIN_ID;
14 g_program_application_id NUMBER := fnd_global.PROG_APPL_ID;
15 g_request_id NUMBER := fnd_global.CONC_REQUEST_ID;
16
17
18 FUNCTION Save_Last_Collection_Date RETURN BOOLEAN
19 IS
20
21 CURSOR c_Last_Collection_Date is
22 Select Last_Update_Date
23 from ISC_MAINT_WORK_ORDERS_F
24 WHERE Organization_id = -99 and Work_Order_id = -99 and Entity_Type = -1;
25
26 l_last_update_date DATE;
27 l_stmt_num number;
28 l_err_num NUMBER;
29 l_err_msg VARCHAR2(255);
30
31 BEGIN
32
33 l_stmt_num := 10;
34 OPEN c_Last_Collection_Date;
35 FETCH c_Last_Collection_Date into l_last_update_date;
36 IF c_Last_Collection_Date %notfound THEN
37
38 l_stmt_num := 20;
39 INSERT INTO ISC_MAINT_WORK_ORDERS_F
40 (ORGANIZATION_ID, WORK_ORDER_ID, WORK_ORDER_NAME, ENTITY_TYPE, CREATION_DATE,
41 LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
42 program_id, program_login_id, program_application_id, request_id)
43 VALUES(-99, -99, 'LAST COLLECTION DATE', -1, SYSDATE, SYSDATE, -1, -1, -1, -1, -1, -1, -1);
44
45 ELSE
46
47 l_stmt_num := 30;
48 UPDATE ISC_MAINT_WORK_ORDERS_F
49 SET LAST_UPDATE_DATE = SYSDATE
50 WHERE Organization_id = -99 and Work_Order_id = -99 and Entity_Type = -1;
51
52 END IF;
53
54 commit;
55
56 RETURN TRUE;
57
58 EXCEPTION
59
60 WHEN OTHERS THEN
61 rollback;
62 l_err_num := SQLCODE;
63 l_err_msg := 'ISC_MAINT_WO_ETL_PKG.Save_Last_Collection_Date ('
64 || to_char(l_stmt_num)
65 || '): '
66 || substr(SQLERRM, 1,200);
67
68 BIS_COLLECTION_UTILITIES.put_line('ISC_MAINT_WO_ETL_PKG.Save_Last_Collection_Date - Error at statement ('
69 || to_char(l_stmt_num)
70 || ')');
71
72 BIS_COLLECTION_UTILITIES.put_line('Error Number: ' || to_char(l_err_num));
73 BIS_COLLECTION_UTILITIES.put_line('Error Message: ' || l_err_msg);
74
75 return FALSE;
76
77 END Save_Last_Collection_Date;
78
79
80
81 PROCEDURE GET_WORK_ORDERS_INITIAL_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
82 IS
83 l_stmt_num NUMBER;
84 l_row_count NUMBER;
85 l_err_num NUMBER;
86 l_err_msg VARCHAR2(255);
87 l_isc_schema VARCHAR2(30);
88 l_status VARCHAR2(30);
89 l_industry VARCHAR2(30);
90 l_list dbms_sql.varchar2_table;
91 BEGIN
92
93 l_list(1) := 'BIS_GLOBAL_START_DATE';
94
95 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
96
97 IF BIS_COLLECTION_UTILITIES.SETUP( 'ISC_MAINT_WORK_ORDERS_F' ) = FALSE THEN
98 RAISE_APPLICATION_ERROR(-20000, errbuf);
99 End if;
100
101 l_stmt_num := 1;
102 IF fnd_installation.get_app_info( 'ISC', l_status, l_industry, l_isc_schema) THEN
103 -- execute immediate 'truncate table ' || l_isc_schema || '.MLOG$_ISC_MAINT_WORK_ORDER'; -- RSG will now take care of this
104 execute immediate 'truncate table ' || l_isc_schema || '.ISC_MAINT_WORK_ORDERS_F PURGE MATERIALIZED VIEW LOG';
105 END IF;
106
107 l_stmt_num := 10;
108 BEGIN
109 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into g_global_start_date from DUAL;
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 BIS_COLLECTION_UTILITIES.put_line('Global start date is not available. Cannot proceed.');
113 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
114 retcode := SQLCODE;
115 errbuf := SQLERRM;
116 return;
117 END;
118
119 -- Store current sysdate as the Last Collection Date.
120 l_stmt_num := 20;
121 if Save_Last_Collection_Date = FALSE THEN
122 BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Last Collection Date.');
123 RAISE_APPLICATION_ERROR(-20000, errbuf);
124 end if;
125
126
127 l_stmt_num := 30;
128 /* Insert into Work Orders Fact Table */
129
130 /* EAM Work Orders master extraction into DBI EAM Work Orders Base Table */
131
132 INSERT /*+ append parallel(ISC_MAINT_WORK_ORDERS_F) */ INTO ISC_MAINT_WORK_ORDERS_F
133 (
134 Organization_id
135 ,Work_Order_id
136 ,Work_Order_Name
137 ,Description
138 ,Entity_Type
139 ,Work_Order_Type
140 ,Status_Type
141 ,Department_id
142 ,Released_date
143 ,WO_Creation_date
144 ,WO_Creation_datetime
145 ,DBI_Completion_date
146 ,Completion_date
147 ,Completion_datetime
148 ,Closed_date
149 ,Scheduled_Start_date
150 ,DBI_Scheduled_Completion_date
151 ,Scheduled_Completion_date
152 ,Last_Estimation_Date
153 ,Days_Late
154 ,Include_WO
155 ,Asset_Group_id
156 ,Activity_id
157 ,instance_id -- added as part of R12
158 ,user_defined_status_id -- added as part of R12
159 ,Creation_Date
160 ,Last_Update_Date
161 ,Created_By
162 ,Last_Updated_By
163 ,Last_Update_Login
164 ,program_id
165 ,program_login_id
166 ,program_application_id
167 ,request_id
168 )
169 Select /*+ use_hash(WDJ) use_hash(WE) parallel(WDJ) parallel(WE) */
170 we.ORGANIZATION_ID Organization_ID
171 ,we.wip_entity_id Work_Order_id
172 ,we.WIP_ENTITY_NAME Work_order_name
173 ,wdj.Description Description
174 ,decode(we.ENTITY_TYPE,6,1,7,2,-1) Entity_Type
175 ,nvl(wdj.WORK_ORDER_TYPE, -1) Work_Order_Type
176 ,wdj.STATUS_TYPE Status_Type
177 ,nvl(wdj.owning_department,-1) department_id
178 ,trunc(wdj.DATE_RELEASED) Released_date
179 ,CASE WHEN trunc(wdj.creation_date) < g_global_start_date THEN g_global_start_date
180 ELSE trunc(wdj.creation_date)
181 END wo_creation_date /* To start counting work order backlog from GSD */
182 ,CASE WHEN trunc(wdj.creation_date) < g_global_start_date THEN g_global_start_date
183 ELSE wdj.creation_date
184 END wo_creation_datetime
185 ,CASE WHEN nvl(trunc(wdj.DATE_COMPLETED),trunc(wdj.DATE_CLOSED)) < g_global_start_date THEN g_global_start_date
186 ELSE nvl(trunc(wdj.DATE_COMPLETED),trunc(wdj.DATE_CLOSED))
187 END DBI_Completion_Date
188 ,trunc(wdj.DATE_COMPLETED) Completion_Date
189 ,wdj.DATE_COMPLETED Completion_Datetime
190 ,trunc(wdj.DATE_CLOSED) Closed_date
191 ,trunc(wdj.SCHEDULED_START_DATE) Scheduled_Start_Date
192 ,CASE WHEN trunc(wdj.SCHEDULED_COMPLETION_DATE) < g_global_start_date THEN g_global_start_date
193 ELSE trunc(wdj.SCHEDULED_COMPLETION_DATE)
194 END DBI_Scheduled_Completion_Date /* To start counting past due work orders from GSD */
195 ,trunc(wdj.SCHEDULED_COMPLETION_DATE) Scheduled_Completion_Date /* For display and conditions in drill down reports */
196 ,wdj.LAST_ESTIMATION_DATE /* Used by Work Order Costing ETL */
197 ,trunc(wdj.DATE_COMPLETED) - trunc(wdj.SCHEDULED_COMPLETION_DATE) Days_Late
198 , CASE WHEN wdj.STATUS_TYPE in (14, 15, 7) THEN 0 /* Do not include Pending Close, Failed Close, Cancelled in reports */
199 WHEN wdj.DATE_COMPLETED is null and wdj.DATE_CLOSED is not null THEN 0 /* This WO's were Cancelled */
200 ELSE 1
201 END Include_WO
202 ,nvl(wdj.ASSET_GROUP_ID, wdj.REBUILD_ITEM_ID) Asset_Group_ID
203 ,nvl(we.PRIMARY_ITEM_ID, -1) Activity_ID
204 ,case wdj.maintenance_object_type
205 when 2 then -1 /* to include assets and rebuilds */
206 when 3 then nvl(wdj.maintenance_object_id,-1)
207 end instance_id
208 ,ewod.user_defined_status_id user_defined_status_id
209 ,g_sysdate CREATION_DATE
210 ,g_sysdate LAST_UPDATE_DATE
211 ,g_user_id CREATED_BY
212 ,g_user_id LAST_UPDATED_BY
213 ,g_login_id LAST_UPDATE_LOGIN
214 ,g_program_id program_id
215 ,g_program_login_id program_login_id
216 ,g_program_application_id program_application_id
217 ,g_request_id request_id
218 From
219 WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE , EAM_WORK_ORDER_DETAILS ewod
220 WHERE
221 WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
222 AND ENTITY_TYPE in (6, 7) /* Maintenance job, Closed maintenance job */
223 AND wdj.maintenance_object_source = 1 /* Work Orders created by EAM only */
224 AND wdj.JOB_TYPE = 3 /* Non-standard job */
225 AND nvl(wdj.DATE_CLOSED, sysdate) >= g_global_start_date
226 AND WDJ.WIP_ENTITY_ID = ewod.WIP_ENTITY_ID
227 AND wdj. maintenance_object_type in(2,3) ; /* change as per eam-ib change to cater to assets and rebuilds */
228
229 l_row_count := sql%rowcount;
230 commit;
231
232 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished EAM Work Orders Extraction into Base Table: '|| l_row_count || ' row(s) inserted');
233
234
235 BIS_COLLECTION_UTILITIES.WRAPUP(
236 p_status => TRUE,
237 p_count => l_row_count,
238 p_message => 'Successfully loaded EAM Work Orders Base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
239 );
240
241 ELSE
242 retcode := g_error;
243 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
244 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile option BIS_GLOBAL_START_DATE is setup.');
245
246 END IF;
247
248 EXCEPTION
249 WHEN OTHERS THEN
250 rollback;
251
252 l_err_num := SQLCODE;
253 l_err_msg := 'ISC_MAINT_WO_ETL_PKG.GET_WORK_ORDERS_INITIAL_LOAD ('
254 || to_char(l_stmt_num)
255 || '): '
256 || substr(SQLERRM, 1,200);
257 BIS_COLLECTION_UTILITIES.PUT_LINE('ISC_MAINT_WO_ETL_PKG.GET_WORK_ORDERS_INITIAL_LOAD - Error at statement ('
258 || to_char(l_stmt_num)
259 || ')');
260 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
261 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
262 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE,
263 l_row_count,
264 'EXCEPTION '|| l_err_num||' : '||l_err_msg
265 );
266
267 retcode := SQLCODE;
268 errbuf := SQLERRM;
269 RAISE_APPLICATION_ERROR(-20000, errbuf);
270 /*please note that this api will commit!!*/
271
272 END GET_WORK_ORDERS_INITIAL_LOAD;
273
274
275
276
277 PROCEDURE GET_WORK_ORDERS_INCR_LOAD(errbuf in out NOCOPY varchar2, retcode in out NOCOPY varchar2)
278 IS
279 l_stmt_num NUMBER;
280 l_row_count NUMBER;
281 l_err_num NUMBER;
282 l_err_msg VARCHAR2(255);
283 l_list dbms_sql.varchar2_table;
284 BEGIN
285
286 l_list(1) := 'BIS_GLOBAL_START_DATE';
287
288 IF (bis_common_parameters.check_global_parameters(l_list)) THEN
289
290 IF BIS_COLLECTION_UTILITIES.SETUP( 'ISC_MAINT_WORK_ORDERS_F' ) = FALSE THEN
291 RAISE_APPLICATION_ERROR(-20000, errbuf);
292 End if;
293
294 l_stmt_num := 10;
295 BEGIN
296 select BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE into g_global_start_date from DUAL;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 BIS_COLLECTION_UTILITIES.put_line('Global start date is not available. Cannot proceed.');
300 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
301 retcode := SQLCODE;
302 errbuf := SQLERRM;
303 return;
304 WHEN OTHERS then /* added as a part of standard */
305 return;
306 END;
307
308 l_stmt_num := 20;
309 BEGIN
310 SELECT Last_Update_Date INTO g_last_collection_date FROM ISC_MAINT_WORK_ORDERS_F
311 WHERE Organization_id = -99 and Work_Order_id = -99 and Entity_Type = -1;
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 BIS_COLLECTION_UTILITIES.put_line('Last collection date is not available. Cannot proceed.');
315 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
316 retcode := SQLCODE;
317 errbuf := SQLERRM;
318 return;
319 when OTHERS then /* added as a part of standard */
320 return;
321 END;
322
323
324 l_stmt_num := 30;
325 -- Store current sysdate as the Last Collection Date.
326 if Save_Last_Collection_Date = FALSE THEN
327 BIS_COLLECTION_UTILITIES.put_line('Failed to store current sysdate as the Last Collection Date.');
328 RAISE_APPLICATION_ERROR(-20000, errbuf);
329 end if;
330
331
332 l_stmt_num := 40;
333 /* EAM Work Orders extraction into Work Orders Base Table */
334
335 MERGE INTO ISC_MAINT_WORK_ORDERS_F f USING
336 (
337 Select
338 Organization_id
339 ,Work_Order_id
340 ,Work_Order_Name
341 ,Description
342 ,Entity_Type
343 ,Work_Order_Type
344 ,Status_Type
345 ,department_id
346 ,Released_date
347 ,WO_Creation_date
348 ,WO_Creation_datetime
349 ,DBI_Completion_date
350 ,Completion_date
351 ,Completion_datetime
352 ,Closed_date
353 ,Scheduled_Start_date
354 ,DBI_Scheduled_Completion_date
355 ,Scheduled_Completion_date
356 ,Last_estimation_date
357 ,days_late
358 ,Include_WO
359 ,Asset_Group_id
360 ,Activity_id
361 ,instance_id --added as part of R12
362 ,user_defined_Status_id --added as part of R12
363 ,g_sysdate CREATION_DATE
364 ,g_sysdate LAST_UPDATE_DATE
365 ,g_user_id CREATED_BY
366 ,g_user_id LAST_UPDATED_BY
367 ,g_login_id LAST_UPDATE_LOGIN
368 ,g_program_id program_id
369 ,g_program_login_id program_login_id
370 ,g_program_application_id program_application_id
371 ,g_request_id request_id
372 from
373 (
374 Select
375 we.ORGANIZATION_ID Organization_ID
376 ,we.wip_entity_id Work_Order_id
377 ,we.WIP_ENTITY_NAME Work_order_name
378 ,wdj.Description Description
379 ,decode(we.ENTITY_TYPE,6,1,7,2,-1) Entity_Type
380 ,nvl(wdj.WORK_ORDER_TYPE, -1) Work_Order_Type
381 ,wdj.STATUS_TYPE status_Type
382 ,nvl(wdj.owning_department,-1) department_id
383 ,trunc(wdj.DATE_RELEASED) Released_date
384 ,CASE WHEN trunc(wdj.creation_date) < g_global_start_date THEN g_global_start_date
385 ELSE trunc(wdj.creation_date)
386 END wo_creation_date /* To start counting work order backlog from GSD */
387 ,CASE WHEN trunc(wdj.creation_date) < g_global_start_date THEN g_global_start_date
388 ELSE wdj.creation_date
389 END wo_creation_datetime
390 ,CASE WHEN nvl(trunc(wdj.DATE_COMPLETED),trunc(wdj.DATE_CLOSED)) < g_global_start_date THEN g_global_start_date
391 ELSE nvl(trunc(wdj.DATE_COMPLETED),trunc(wdj.DATE_CLOSED))
392 END DBI_Completion_Date /* In case a new work order is created after GSD w/o completion date and then completed before GSD */
393 ,trunc(wdj.DATE_COMPLETED) Completion_Date
394 ,wdj.DATE_COMPLETED Completion_Datetime
395 ,trunc(wdj.DATE_CLOSED) Closed_date
396 ,trunc(wdj.SCHEDULED_START_DATE) Scheduled_Start_Date
397 ,CASE WHEN trunc(wdj.SCHEDULED_COMPLETION_DATE) < g_global_start_date THEN g_global_start_date
398 ELSE trunc(wdj.SCHEDULED_COMPLETION_DATE)
399 END DBI_Scheduled_Completion_Date /* In case a new work order is created after GSD with scheduled completion date before GSD */
400 ,trunc(wdj.SCHEDULED_COMPLETION_DATE) Scheduled_Completion_Date
401 ,wdj.LAST_ESTIMATION_DATE /* Used by Work Order Costing ETL */
402 ,trunc(wdj.DATE_COMPLETED) - trunc(wdj.SCHEDULED_COMPLETION_DATE) days_late
403 , CASE WHEN wdj.STATUS_TYPE in (14, 15, 7) THEN 0 /* Do not include Pending Close, Failed Close, Cancelled in reports */
404 WHEN wdj.DATE_COMPLETED is null and wdj.DATE_CLOSED is not null THEN 0 /* This WO's were Cancelled */
405 ELSE 1
406 END Include_WO
407 ,nvl(wdj.ASSET_GROUP_ID, wdj.REBUILD_ITEM_ID) Asset_Group_ID
408 ,nvl(we.PRIMARY_ITEM_ID, -1) Activity_ID
409 ,case wdj.maintenance_object_type
410 when 2 then -1 /* to include assets and rebuilds */
411 when 3 then nvl(wdj.maintenance_object_id,-1)
412 end instance_id
413 ,ewod.user_defined_Status_id user_Defined_Status_id
414 ,wdj.last_update_date
415 From
416 WIP_DISCRETE_JOBS WDJ, WIP_ENTITIES WE , eam_work_order_details ewod
417 WHERE
418 WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
419 AND we.ENTITY_TYPE in (6, 7) /* Maintenance job, Closed maintenance job */
420 AND wdj.JOB_TYPE = 3 /* Non-standard job */
421 AND wdj.maintenance_object_source = 1 /* Work Orders created by EAM only */
422 AND nvl(wdj.date_closed, sysdate) >= g_global_start_date
423 AND WDJ.WIP_ENTITY_ID = ewod.WIP_ENTITY_ID
424 and wdj.maintenance_object_type in (2,3) /* change as per eam-ib to include assets and rebuilds */
425 and (
426 wdj.last_update_date > g_last_collection_date
427 or ewod.last_update_date > g_last_collection_date
428 )
429 -- New Work Orders and existing work orders that have been updated
430 )) s
431 ON (f.Organization_id = s.Organization_id
432 and f.Work_Order_id = s.Work_Order_id
433 )
434 WHEN MATCHED THEN
435 UPDATE SET
436 f.Entity_Type = s.Entity_Type
437 ,f.Description = s.Description
438 ,f.Work_Order_Type = s.Work_Order_Type
439 ,f.Status_Type = s.Status_Type
440 ,f.department_id = s.department_id
441 ,f.Released_date = s.Released_date
442 ,f.WO_Creation_date = s.WO_Creation_date
443 ,f.WO_Creation_datetime = s.WO_Creation_datetime
444 ,f.DBI_Completion_date = s.DBI_Completion_date
445 ,f.Completion_date = s.Completion_date
446 ,f.Completion_datetime = s.Completion_datetime
447 ,f.Closed_date = s.Closed_date
448 ,f.Scheduled_Start_date = s.Scheduled_Start_date
449 ,f.DBI_Scheduled_Completion_date = s.DBI_Scheduled_Completion_date
450 ,f.Scheduled_Completion_date = s.Scheduled_Completion_date
451 ,f.Last_estimation_date = s.Last_estimation_date
452 ,f.days_late = s.days_late
453 ,f.Include_WO = s.Include_WO
454 ,f.Asset_Group_id = s.Asset_Group_id
455 ,f.Activity_id = s.Activity_id
456 ,f.instance_id = s.instance_id
457 ,f.user_Defined_status_id = s.user_Defined_Status_id
458 ,f.Last_Update_Date = s.Last_Update_Date
459 ,f.Last_Updated_By = s.Last_Updated_By
460 ,f.Last_Update_Login = s.Last_Update_Login
461 ,f.program_id = s.program_id
462 ,f.program_login_id = s.program_login_id
463 ,f.program_application_id = s.program_application_id
464 ,f.request_id = s.request_id
465 WHEN NOT MATCHED THEN
466 INSERT (Organization_id, Work_Order_id, Work_Order_Name, Description, Entity_Type, Work_Order_Type, Status_Type
467 ,department_id, Released_date, DBI_Completion_date, WO_Creation_date, WO_Creation_datetime, Completion_date, Completion_datetime, Closed_date
468 ,Scheduled_Start_date, DBI_Scheduled_Completion_date, Scheduled_Completion_date, Last_estimation_date
469 ,days_late, Include_WO, Asset_Group_id, Activity_id,instance_id,user_Defined_Status_id, CREATION_DATE, LAST_UPDATE_DATE
470 ,CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, program_id, program_login_id, program_application_id, request_id)
471 VALUES (s.Organization_id, s.Work_Order_id, s.Work_Order_Name, s.Description, s.Entity_Type, s.Work_Order_Type, s.Status_Type
472 ,s.department_id, s.Released_date, s.DBI_Completion_date, s.WO_Creation_date, s.WO_Creation_datetime, s.Completion_date, s.Completion_datetime, s.Closed_date
473 ,s.Scheduled_Start_date, s.DBI_Scheduled_Completion_date, s.Scheduled_Completion_date, s.Last_estimation_date
474 ,s.days_late, s.Include_WO, s.Asset_Group_id, s.Activity_id, s.instance_id, s.user_Defined_Status_id, s.CREATION_DATE, s.LAST_UPDATE_DATE
475 ,s.CREATED_BY, s.LAST_UPDATED_BY, s.LAST_UPDATE_LOGIN, s.program_id, s.program_login_id, s.program_application_id, s.request_id);
476
477 l_row_count := sql%rowcount;
478 commit;
479
480 BIS_COLLECTION_UTILITIES.PUT_LINE('Finished EAM Work Orders Extraction into Base Table: '|| l_row_count || ' row(s) inserted/updated');
481
482 BIS_COLLECTION_UTILITIES.WRAPUP(
483 p_status => TRUE,
484 p_count => l_row_count,
485 p_message => 'Successfully loaded EAM Work Orders Base table at ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
486 );
487
488 ELSE
489 retcode := g_error;
490 BIS_COLLECTION_UTILITIES.PUT_LINE('Global Parameters are not setup.');
491 BIS_COLLECTION_UTILITIES.PUT_LINE('Please check that the profile option BIS_GLOBAL_START_DATE is setup.');
492
493 END IF;
494
495 EXCEPTION
496 WHEN OTHERS THEN
497 rollback;
498
499
500 l_err_num := SQLCODE;
501 l_err_msg := 'ISC_MAINT_WO_ETL_PKG.GET_WORK_ORDERS_INCR_LOAD ('
502 || to_char(l_stmt_num)
503 || '): '
504 || substr(SQLERRM, 1,200);
505 BIS_COLLECTION_UTILITIES.PUT_LINE('ISC_MAINT_WO_ETL_PKG.GET_WORK_ORDERS_INCR_LOAD - Error at statement ('
506 || to_char(l_stmt_num)
507 || ')');
508 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Number: ' || to_char(l_err_num));
509 BIS_COLLECTION_UTILITIES.PUT_LINE('Error Message: ' || l_err_msg);
510 BIS_COLLECTION_UTILITIES.WRAPUP( FALSE, l_row_count, 'EXCEPTION '|| l_err_num||' : '||l_err_msg );
511
512 retcode := SQLCODE;
513 errbuf := SQLERRM;
514 RAISE_APPLICATION_ERROR(-20000, errbuf);
515 /*please note that this api will commit!!*/
516
517 END GET_WORK_ORDERS_INCR_LOAD;
518
519
520 End ISC_MAINT_WO_ETL_PKG;