[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_BACKLOG_ETL_PKG
Source
1 package body isc_depot_backlog_etl_pkg as
2 /* $Header: iscdepotetlbb.pls 120.2 2006/09/21 01:20:05 kreardon noship $ */
3
4 -- Global Varaiables
5
6 c_error constant number := -1; -- concurrent manager error code
7 c_warning constant number := 1; -- concurrent manager warning code
8 c_ok constant number := 0; -- concurrent manager success code
9 c_errbuf_size constant number := 300; -- length of formatted error message
10
11 g_global_start_date date;
12 g_isc_schema varchar2(30);
13 g_object_name constant varchar2(30) := 'ISC_DR_REPAIR_ORDERS_F';
14
15 -- Common Procedures (for initial and incremental load)
16
17 -- err_mesg
18 function err_mesg
19 ( p_mesg in varchar2
20 , p_proc_name in varchar2 default null
21 , p_stmt_id in number default -1
22 )
23 return varchar2
24
25 is
26
27 l_formatted_message varchar2(300);
28
29 begin
30
31 l_formatted_message := substr( p_proc_name || ' #' || to_char(p_stmt_id)
32 || ': ' || p_mesg
33 , 1
34 , c_errbuf_size
35 );
36 return l_formatted_message;
37
38 exception
39
40 when others then
41 -- the exception happened in the exception reporting function
42 -- return with error.
43 l_formatted_message := 'Error in error reporting. ' || p_mesg;
44 return l_formatted_message;
45
46 end err_mesg;
47
48 -- Common Procedures Definitions
49 -- check_initial_load_setup
50 -- Gets the GSD.
51
52 function check_initial_load_setup
53 ( x_global_start_date out nocopy date
54 , x_isc_schema out nocopy varchar2
55 , x_message out nocopy varchar2
56 )
57 return number
58 is
59
60 l_proc_name constant varchar2(40) := 'check_initial_load_setup';
61 l_stmt_id number;
62 l_setup_good boolean;
63 l_status varchar2(30);
64 l_industry varchar2(30);
65 l_message varchar2(100);
66
67 l_exception exception;
68 l_error_mesg constant varchar2(100) := 'Error in Global setup';
69
70 begin
71
72 -- Initialization
73 l_stmt_id := 0;
74
75 -- Check for the global start date setup.
76 -- These parameter must be set up prior to any DBI load.
77
78 x_global_start_date := trunc(bis_common_parameters.get_global_start_date);
79
80 if x_global_start_date is null then
81 l_message := 'Global Start Date is NULL';
82 raise l_exception;
83 end if;
84
85 l_setup_good := fnd_installation.get_app_info
86 ( 'ISC'
87 , l_status
88 , l_industry
89 , x_isc_schema
90 );
91
92 if l_setup_good = false or x_isc_schema is null then
93 l_message := 'ISC schema not found';
94 raise l_exception;
95 end if;
96
97 return c_ok;
98
99 exception
100
101 when l_exception then
102 x_message := err_mesg( l_error_mesg || ': ' || l_message
103 , l_proc_name
104 , l_stmt_id
105 );
106 bis_collection_utilities.put_line( x_message );
107 return c_error;
108
109 when others then
110 x_message := err_mesg( sqlerrm, l_proc_name, l_stmt_id );
111 bis_collection_utilities.put_line( x_message );
112 return c_error;
113
114 end check_initial_load_setup;
115
116 -- check_incr_load_setup
117 -- Gets the last run date.
118
119 function get_last_run_date
120 ( p_fact_name in varchar2
121 , x_run_date out nocopy date
122 , x_message out nocopy varchar2
123 )
124 return number
125 is
126
127 l_func_name constant varchar2(40) := 'get_last_run_date';
128 l_stmt_id number;
129
130 begin
131
132 -- Initialization
133 l_stmt_id := 0;
134
135 select last_run_date
136 into x_run_date
137 from isc_dr_inc
138 where fact_name = p_fact_name;
139
140 return c_ok;
141
142 exception
143
144 when no_data_found then
145 x_message := err_mesg( p_fact_name ||
146 ': Please run the Intial Load Request Set for ' ||
147 'the Depot Repair Management page.'
148 , l_func_name
149 , l_stmt_id
150 );
151 bis_collection_utilities.put_line( x_message );
152 return c_error;
153
154 when others then
155 x_message := err_mesg( sqlerrm, l_func_name, l_stmt_id );
156 bis_collection_utilities.put_line( x_message );
157 return c_error;
158
159 end get_last_run_date;
160
161
162 function get_master_organization_id
163 ( x_organization_id out nocopy number
164 , x_message out nocopy varchar2
165 )
166 return number
167 is
168
169 l_func_name constant varchar2(40) := 'get_master_organization_id';
170 l_profile_name varchar2(240);
171 l_stmt_id number;
172 l_master_org number;
173 l_org number;
174
175 l_exception exception;
176 l_err_msg varchar2(2000);
177
178 cursor master_org_cur is
179 select distinct master_organization_id
180 from mtl_parameters;
181
182 begin
183
184 l_stmt_id := 0;
185
186 for master_org_cur_rec in master_org_cur loop
187
188 l_master_org := master_org_cur_rec.master_organization_id;
189 if master_org_cur%rowcount > 1 then
190 l_master_org := null;
191 exit;
192 end if;
193
194 end loop;
195
196 /* Get the site level value for Service: Inventory Validation Organization */
197 if l_master_org is null then
198
199 l_stmt_id := 10;
200 l_org := fnd_profile.value_specific
201 ( name => 'CS_INV_VALIDATION_ORG'
202 , user_id => -1
203 , responsibility_id => -1
204 , application_id => -1
205 );
206
207
208 if l_org is null then
209
210 l_stmt_id := 20;
211 select user_profile_option_name
212 into l_profile_name
213 from fnd_profile_options_vl
214 where profile_option_name = 'CS_INV_VALIDATION_ORG';
215
216 fnd_message.set_name( 'ISC', 'ISC_DEPOT_MISSING_INV_VAL_ORG' );
217 fnd_message.set_token( 'ISC_DEPOT_PROFILE_NAME', l_profile_name );
218 l_err_msg := fnd_message.get;
219 raise l_exception;
220
221 end if;
222
223 l_stmt_id := 30;
224 select master_organization_id
225 into l_master_org
226 from mtl_parameters
227 where organization_id = l_org;
228
229 end if;
230
231 x_organization_id := l_master_org;
232 return c_ok;
233
234 exception
235
236 when l_exception then
237 x_message := l_err_msg;
238 bis_collection_utilities.put_line( x_message );
239 return c_error;
240
241 when others then
242 x_message := err_mesg ( sqlerrm, l_func_name, l_stmt_id );
243 bis_collection_utilities.put_line( x_message );
244 return c_error;
245
246 end get_master_organization_id;
247
248 -- initial_load
249 -- Parameters:
250 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
251 -- errbuf - empty on successful completion, message on error or warning
252
253 procedure initial_load
254 ( errbuf in out nocopy varchar2
255 , retcode in out nocopy number
256 )
257 is
258
259 l_stmnt_id number;
260 l_run_date date;
261 l_proc_name constant varchar2(30) := 'intitial_load';
262 l_master_org number;
263
264 l_user_id constant number := nvl(fnd_global.user_id,-1);
265 l_login_id constant number := nvl(fnd_global.login_id,-1);
266 l_program_id constant number := nvl(fnd_global.conc_program_id,-1);
267 l_program_login_id constant number := nvl(fnd_global.conc_login_id,-1);
268 l_program_application_id constant number := nvl(fnd_global.prog_appl_id,-1);
269 l_request_id constant number := nvl(fnd_global.conc_request_id,-1);
270
271 l_message varchar2(32000);
272 l_exception exception;
273 l_rowcount number;
274 l_to_date date;
275
276 begin
277
278 bis_collection_utilities.log( 'Begin Initial Load' );
279
280 l_stmnt_id := 0;
281 if not bis_collection_utilities.setup( g_object_name ) then
282 l_message := err_mesg( 'Error in BIS_COLLECTION_UTILITIES.Setup'
283 , l_proc_name
284 , l_stmnt_id
285 );
286 bis_collection_utilities.put_line( l_message );
287 raise l_exception;
288 end if;
289
290 l_stmnt_id := 10;
291 if check_initial_load_setup
292 ( x_global_start_date => g_global_start_date
293 , x_isc_schema => g_isc_schema
294 , x_message => l_message
295 ) <> c_ok then
296 raise l_exception;
297 end if;
298 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
299
300 l_stmnt_id := 20;
301 delete
302 from isc_dr_inc
303 where fact_name = 'ISC_DR_REPAIR_ORDERS_F';
304
305 bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
306
307 l_stmnt_id := 30;
308 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_REPAIR_ORDERS_F';
309
310 bis_collection_utilities.log( 'Truncated table ISC_DR_REPAIR_ORDERS_F', 1 );
311
312 l_stmnt_id := 40;
313 l_run_date := sysdate - 5/(24*60);
314 l_to_date := sysdate;
315
316 l_stmnt_id := 50;
317 if get_master_organization_id
318 ( x_organization_id => l_master_org
319 , x_message => l_message
320 ) <> c_ok then
321 raise l_exception;
322 end if;
323 bis_collection_utilities.log( 'Master organization id: ' || l_master_org, 1 );
324
325 l_stmnt_id := 60;
326 insert /*+ append parallel(isc_dr_repair_orders_f) */
327 into isc_dr_repair_orders_f
328 ( repair_line_id
329 , repair_number
330 , repair_organization_id
331 , master_organization_id
332 , inventory_item_id
333 , item_org_id
334 , repair_type_id
335 , incident_id
336 , incident_number
337 , customer_id
338 , ro_creation_date
339 , dbi_ro_creation_date
340 , repair_mode
341 , date_closed
342 , dbi_date_closed
343 , promise_date
344 , dbi_promise_date
345 , flow_status_id
346 , status
347 , serial_number
348 , quantity
349 , uom_code
350 , created_by
351 , creation_date
352 , last_update_date
353 , last_updated_by
354 , last_update_login
355 , program_id
356 , program_login_id
357 , program_application_id
358 , request_id
359 )
360 select /*+ use_hash(inc, cr) parallel(inc) parallel(cr)*/
361 cr.repair_line_id
362 , cr.repair_number
363 , nvl(cr.owning_organization_id,-1)
364 , l_master_org master_organization_id
365 , cr.inventory_item_id
366 , cr.inventory_item_id || '-' || l_master_org
367 , cr.repair_type_id
368 , inc.incident_id
369 , inc.incident_number
370 , inc.customer_id
371 , trunc(cr.creation_date)
372 , case
373 when cr.creation_date < g_global_start_date then
374 g_global_start_date
375 else trunc(cr.creation_date)
376 end
377 , cr.repair_mode
378 , cr.date_closed
379 , trunc(cr.date_closed)
380 , trunc(cr.promise_date)
381 , case
382 when cr.promise_date < g_global_start_date then
383 g_global_start_date - 1
384 else trunc(cr.promise_date)
385 end
386 , cr.flow_status_id
387 , cr.status
388 , cr.serial_number
389 , cr.quantity
390 , cr.unit_of_measure
391 , l_user_id
392 , sysdate
393 , sysdate
394 , l_user_id
395 , l_login_id
396 , l_program_id
397 , l_program_login_id
398 , l_program_application_id
399 , l_request_id
400 from
401 csd_repairs cr
402 , cs_incidents_all_b inc
403 where
404 cr.incident_id = inc.incident_id
405 and ( cr.status in ('O','H','D') or
406 cr.Date_closed >= g_global_start_date
407 );
408
409 l_rowcount := sql%rowcount;
410 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_REPAIR_ORDERS_F' , 1 );
411
412 l_stmnt_id := 70;
413 insert into isc_dr_inc
414 ( fact_name
415 , last_run_date
416 , created_by
417 , creation_date
418 , last_update_date
419 , last_updated_by
420 , last_update_login
421 , program_id
422 , program_login_id
423 , program_application_id
424 , request_id
425 )
426 values
427 ( 'ISC_DR_REPAIR_ORDERS_F'
428 , l_run_date
429 , l_user_id
430 , sysdate
431 , sysdate
432 , l_user_id
433 , l_login_id
434 , l_program_id
435 , l_program_login_id
436 , l_program_application_id
437 , l_request_id
438 );
439
440 bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
441
442 l_stmnt_id := 80;
443 commit;
444
445 l_stmnt_id := 90;
446 bis_collection_utilities.wrapup( p_status => true
447 , p_period_from => g_global_start_date
448 , p_period_to => l_to_date
449 , p_count => l_rowcount
450 );
451
452 retcode := c_ok;
453
454 bis_collection_utilities.log( 'End Initial Load' );
455
456 exception
457
458 when l_exception then
459 rollback;
460 bis_collection_utilities.wrapup( p_status => false
461 , p_period_from => g_global_start_date
462 , p_period_to => l_to_date
463 , p_message => l_message
464 );
465 retcode := c_error;
466 errbuf := l_message;
467
468 when others then
469 rollback;
470 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
471 bis_collection_utilities.put_line( l_message );
472 bis_collection_utilities.wrapup( p_status => false
473 , p_period_from => g_global_start_date
474 , p_period_to => l_to_date
475 , p_message => l_message
476 );
477 retcode := c_error;
478 errbuf := l_message;
479
480 end initial_load;
481
482 -- incr_load
483 -- Parameters:
484 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
485 -- errbuf - empty on successful completion, message on error or warning
486 --
487
488 procedure incr_load
489 ( errbuf in out nocopy varchar2
490 , retcode in out nocopy number
491 )
492 is
493
494 l_stmnt_id number;
495 l_run_date date;
496 l_last_run_date date;
497 l_proc_name constant varchar2(30) := 'incr_load';
498 l_master_org number;
499
500 l_user_id constant number := nvl(fnd_global.user_id,-1);
501 l_login_id constant number := nvl(fnd_global.login_id,-1);
502 l_program_id constant number := nvl(fnd_global.conc_program_id,-1);
503 l_program_login_id constant number := nvl(fnd_global.conc_login_id,-1);
504 l_program_application_id constant number := nvl(fnd_global.prog_appl_id,-1);
505 l_request_id constant number := nvl(fnd_global.conc_request_id,-1);
506
507 l_message varchar2(32000);
508 l_exception exception;
509 l_rowcount number;
510 l_to_date date;
511
512 begin
513
514 bis_collection_utilities.log( 'Begin Incremental Load' );
515
516 l_stmnt_id := 0;
517 if not bis_collection_utilities.setup( g_object_name ) then
518 l_message := err_mesg( 'Error in BIS_COLLECTION_UTILITIES.Setup'
519 , l_proc_name
520 , l_stmnt_id
521 );
522 bis_collection_utilities.put_line( l_message );
523 raise l_exception;
524 end if;
525
526 l_stmnt_id := 10;
527 if get_last_run_date
528 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
529 , x_run_date => l_last_run_date
530 , x_message => l_message
531 ) <> c_ok then
532 raise l_exception;
533 end if;
534
535 if l_last_run_date is null then
536 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
537 raise l_exception;
538 end if;
539
540 bis_collection_utilities.log( 'From Date: ' || fnd_date.date_to_displaydt(l_last_run_date), 1 );
541
542 l_stmnt_id := 20;
543 l_run_date := sysdate - 5/(24*60);
544 l_to_date := sysdate;
545
546 l_stmnt_id := 30;
547 if get_master_organization_id
548 ( x_organization_id => l_master_org
549 , x_message => l_message
550 ) <> c_ok then
551 raise l_exception;
552 end if;
553 bis_collection_utilities.log( 'Master organization id: ' || l_master_org, 1 );
554
555 l_stmnt_id := 40;
556 merge into isc_dr_repair_orders_f fact
557 using
558 ( select
559 cr.repair_line_id repair_line_id
560 , cr.repair_number repair_number
561 , nvl(cr.owning_organization_id, -1) organization_id
562 , cr.inventory_item_id inventory_item_id
563 , cr.repair_type_id repair_type_id
564 , inc.incident_id incident_id
565 , inc.incident_number incident_number
566 , inc.customer_id customer_id
567 , trunc(cr.creation_date) ro_creation_date
568 , case
569 when cr.creation_date < g_global_start_date then
570 g_global_start_date
571 else trunc(cr.creation_date)
572 end dbi_ro_creation_date
573 , cr.repair_mode repair_mode
574 , cr.date_closed date_closed
575 , trunc(cr.date_closed) dbi_date_closed
576 , trunc(cr.promise_date) promise_date
577 , case
578 when cr.promise_date < g_global_start_date then
579 g_global_start_date - 1
580 else
581 trunc(cr.promise_date)
582 end dbi_promise_date
583 , cr.flow_status_id flow_status_id
584 , cr.status status
585 , cr.serial_number serial_number
586 , cr.quantity quantity
587 , cr.unit_of_measure uom_code
588 from
589 csd_repairs cr
590 , cs_incidents_all_b inc
591 where
592 cr.incident_id = inc.incident_id
593 and cr.last_update_date > l_last_run_date
594 ) oltp
595 on
596 ( fact.repair_line_id = oltp.repair_line_id )
597 when matched then
598 update set
599 fact.repair_number = oltp.repair_number
600 , fact.repair_organization_id = oltp.organization_id
601 , fact.master_organization_id = l_master_org
602 , fact.inventory_item_id = oltp.inventory_item_id
603 , fact.item_org_id = oltp.inventory_item_id || '-' || l_master_org
604 , fact.repair_type_id = oltp.repair_type_id
605 , fact.incident_id = oltp.incident_id
606 , fact.incident_number = oltp.incident_number
607 , fact.customer_id = oltp.customer_id
608 , fact.ro_creation_date = oltp.ro_creation_date
609 , fact.dbi_ro_creation_date = oltp.dbi_ro_creation_date
610 , fact.date_closed = oltp.date_closed
611 , fact.dbi_date_closed = oltp.dbi_date_closed
612 , fact.repair_mode = oltp.repair_mode
613 , fact.promise_date = oltp.promise_date
614 , fact.dbi_promise_date = oltp.dbi_promise_date
615 , fact.flow_status_id = oltp.flow_status_id
616 , fact.status = oltp.status
617 , fact.serial_number = oltp.serial_number
618 , fact.quantity = oltp.quantity
619 , fact.uom_code = oltp.uom_code
620 , fact.last_update_date = sysdate
621 , fact.last_updated_by = l_user_id
622 , fact.last_update_login = l_login_id
623 , fact.program_id = l_program_id
624 , fact.program_login_id = l_program_login_id
625 , fact.program_application_id = l_program_application_id
626 , fact.request_id = l_request_id
627 when not matched then
628 insert
629 ( repair_line_id
630 , repair_number
631 , repair_organization_id
632 , master_organization_id
633 , inventory_item_id
634 , item_org_id
635 , repair_type_id
636 , incident_id
637 , incident_number
638 , customer_id
639 , ro_creation_date
640 , dbi_ro_creation_date
641 , repair_mode
642 , date_closed
643 , dbi_date_closed
644 , promise_date
645 , dbi_promise_date
646 , flow_status_id
647 , status
648 , serial_number
649 , quantity
650 , uom_code
651 , created_by
652 , creation_date
653 , last_update_date
654 , last_updated_by
655 , last_update_login
656 , program_id
657 , program_login_id
658 , program_application_id
659 , request_id
660 )
661 values
662 ( oltp.repair_line_id
663 , oltp.repair_number
664 , oltp.organization_id
665 , l_master_org
666 , oltp.inventory_item_id
667 , oltp.inventory_item_id || '-' || l_master_org
668 , oltp.repair_type_id
669 , oltp.incident_id
670 , oltp.incident_number
671 , oltp.customer_id
672 , oltp.ro_creation_date
673 , oltp.dbi_ro_creation_date
674 , oltp.repair_mode
675 , oltp.date_closed
676 , oltp.dbi_date_closed
677 , oltp.promise_date
678 , oltp.dbi_promise_date
679 , oltp.flow_status_id
680 , oltp.status
681 , oltp.serial_number
682 , oltp.quantity
683 , oltp.uom_code
684 , l_user_id
685 , sysdate
686 , sysdate
687 , l_user_id
688 , l_login_id
689 , l_program_id
690 , l_program_login_id
691 , l_program_application_id
692 , l_request_id
693 );
694
695 l_rowcount := sql%rowcount;
696 bis_collection_utilities.log( 'Merged ' || l_rowcount || ' rows into ISC_DR_REPAIR_ORDERS_F' , 1 );
697
698 l_stmnt_id := 50;
699 update isc_dr_inc
700 set
701 last_run_date = l_run_date
702 , last_update_date = sysdate
703 , last_updated_by = l_user_id
704 , last_update_login = l_login_id
705 , program_id = l_program_id
706 , program_login_id = l_program_login_id
707 , program_application_id = l_program_application_id
708 , request_id = l_request_id
709 where fact_name = 'ISC_DR_REPAIR_ORDERS_F';
710
711 bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
712
713 l_stmnt_id := 60;
714 commit;
715
716 l_stmnt_id := 70;
717 bis_collection_utilities.wrapup( p_status => true
718 , p_period_from => l_last_run_date
719 , p_period_to => l_to_date
720 , p_count => l_rowcount
721 );
722
723 retcode := c_ok;
724
725 bis_collection_utilities.log( 'End Incremental Load' );
726
727 exception
728
729 when l_exception then
730 rollback;
731 bis_collection_utilities.wrapup( p_status => false
732 , p_period_from => l_last_run_date
733 , p_period_to => l_to_date
734 , p_message => l_message
735 );
736 retcode := c_error;
737 errbuf := l_message;
738
739 when others then
740 rollback;
741 l_message := err_mesg( sqlerrm, l_proc_name, l_stmnt_id );
742 bis_collection_utilities.put_line( l_message );
743 bis_collection_utilities.wrapup( p_status => false
744 , p_period_from => l_last_run_date
745 , p_period_to => l_to_date
746 , p_message => l_message
747 );
748 retcode := c_error;
749 errbuf := l_message;
750
751 end incr_load;
752
753 end isc_depot_backlog_etl_pkg;