[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_MTTR_ETL_PKG
Source
1 package body isc_depot_mttr_etl_pkg as
2 /* $Header: iscdepotmttretlb.pls 120.1 2006/09/21 01:21:32 kreardon noship $ */
3
4 -- Package Variables
5
6 g_global_start_date date;
7 g_isc_schema varchar2(30);
8 g_object_name constant varchar2(30) := 'ISC_DR_MTTR_F';
9
10 -- Initial Load
11 -- Parameters:
12 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
13 -- errbuf - empty on successful completion, message on error or warning
14 --
15
16 procedure initial_load
17 ( errbuf in out nocopy varchar2
18 , retcode in out nocopy number
19 )
20 is
21
22 l_stmt_id number;
23 l_run_date date;
24 l_proc_name constant varchar2(30) := 'initial_load';
25 l_isc_schema varchar2(30);
26 l_err_msg varchar2(200);
27
28 l_user_id constant number := nvl(fnd_global.user_id,-1);
29 l_login_id constant number := nvl(fnd_global.login_id,-1);
30 l_program_id constant number := nvl(fnd_global.conc_program_id,-1);
31 l_program_login_id constant number := nvl(fnd_global.conc_login_id,-1);
32 l_program_application_id constant number := nvl(fnd_global.prog_appl_id,-1);
33 l_request_id constant number := nvl(fnd_global.conc_request_id,-1);
34
35 l_message varchar2(32000);
36 l_exception exception;
37 l_temp_rowcount number;
38 l_rowcount number;
39 l_to_date date;
40
41 begin
42
43 bis_collection_utilities.log( 'Begin Initial Load' );
44
45 l_stmt_id := 0;
46 if not bis_collection_utilities.setup( g_object_name ) then
47 l_message := isc_depot_backlog_etl_pkg.err_mesg
48 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
49 , l_proc_name
50 , l_stmt_id
51 );
52 bis_collection_utilities.put_line( l_message );
53 raise l_exception;
54 end if;
55
56 l_stmt_id := 10;
57 if isc_depot_backlog_etl_pkg.check_initial_load_setup
58 ( x_global_start_date => g_global_start_date
59 , x_isc_schema => g_isc_schema
60 , x_message => l_message
61 ) <> c_ok then
62 raise l_exception;
63 end if;
64
65 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
66
67 l_stmt_id := 20;
68 delete
69 from isc_dr_inc
70 where fact_name = 'ISC_DR_MTTR_F';
71
72 bis_collection_utilities.log( 'Deleted from table ISC_DR_INC', 1 );
73
74 l_stmt_id := 30;
75 execute immediate 'TRUNCATE TABLE ' || g_isc_schema || '.ISC_DR_MTTR_F';
76
77 bis_collection_utilities.log( 'Truncated table ISC_DR_MTTR_F', 1 );
78
79 l_stmt_id := 40;
80 if isc_depot_backlog_etl_pkg.get_last_run_date
81 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
82 , x_run_date => l_run_date
83 , x_message => l_message
84 ) <> c_ok then
85 raise l_exception;
86 end if;
87
88 if l_run_date is null then
89 l_message := 'Please launch the Intial Load Request Set for the Depot Repair Management page.';
90 raise l_exception;
91 end if;
92
93 bis_collection_utilities.log( 'Repair Order To Date: ' || fnd_date.date_to_displaydt(l_run_date), 1 );
94
95 l_to_date := sysdate;
96
97 l_stmt_id := 50;
98 -- Insertion of Non-refurbishment repair orders for Serialized and
99 -- Non-serialized items
100
101 insert /*+ append parallel(isc_dr_mttr_f) */
102 into isc_dr_mttr_f
103 ( repair_line_id
104 , repair_start_date
105 , repair_end_date
106 , time_to_repair
107 , created_by
108 , creation_date
109 , last_update_date
110 , last_updated_by
111 , last_update_login
112 , program_id
113 , program_login_id
114 , program_application_id
115 , request_id
116 )
117 select /*+ ordered use_hash(crt, bdr, cpt, ced, pdr, cpt1, ced1, ibs)
118 parallel(crt) parallel(cpt) parallel(ced) parallel(pdr)
119 parallel(cpt1) parallel(ced1) parallel(ibs) parallel(bdr) */
120 bdr.repair_line_id repair_line_id
121 , min(pdr.transaction_date) repair_start_date
122 , max(ibs.actual_shipment_date) repair_end_date
123 , max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
124 , l_user_id
125 , sysdate
126 , sysdate
127 , l_user_id
128 , l_login_id
129 , l_program_id
130 , l_program_login_id
131 , l_program_application_id
132 , l_request_id
133 from
134 csd_repair_types_b crt
135 , isc_dr_repair_orders_f bdr
136 , csd_product_transactions cpt
137 , cs_estimate_details ced
138 , poa_dbi_rtx_f pdr
139 , csd_product_transactions cpt1
140 , cs_estimate_details ced1
141 , isc_book_sum2_f ibs
142 where
143 bdr.repair_line_id = cpt.repair_line_id
144 and bdr.repair_type_id = crt.repair_type_id
145 and crt.repair_type_ref <> 'RF'
146 and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
147 and cpt.action_code = 'CUST_PROD'
148 and cpt.estimate_detail_id = ced.estimate_detail_id
149 and ced.order_line_id = pdr.oe_order_line_id
150 and pdr.transaction_type = 'DELIVER'
151 and bdr.repair_line_id = cpt1.repair_line_id
152 and cpt1.action_type in ('SHIP','WALK_IN_ISSUE')
153 and cpt1.action_code = 'CUST_PROD'
154 and cpt1.estimate_detail_id = ced1.estimate_detail_id
155 and ced1.order_line_id = ibs.line_id
156 and ibs.actual_shipment_date is not null
157 and bdr.status = 'C'
158 and bdr.date_closed is not null
159 and bdr.ro_creation_date >= g_global_start_date
160 group by
161 bdr.repair_line_id;
162
163 l_rowcount := sql%rowcount;
164 bis_collection_utilities.log( 'Inserted ' || l_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
165
166 l_stmt_id := 60;
167 commit;
168
169 l_stmt_id := 70;
170 -- Insertion of repair orders with Refurbishment(IO) repair type for
171 -- Non-serialized items
172
173 insert /*+ append parallel(isc_dr_mttr_f) */
174 into isc_dr_mttr_f
175 ( repair_line_id
176 , repair_start_date
177 , repair_end_date
178 , time_to_repair
179 , created_by
180 , creation_date
181 , last_update_date
182 , last_updated_by
183 , last_update_login
184 , program_id
185 , program_login_id
186 , program_application_id
187 , request_id
188 )
189 select /*+ use_hash(crt, bdr) parallel(bdr) */
190 bdr.repair_line_id repair_line_id
191 , min(pdr.transaction_date) repair_start_date
192 , max(ibs.actual_shipment_date) repair_end_date
193 , max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
194 , l_user_id
195 , sysdate
196 , sysdate
197 , l_user_id
198 , l_login_id
199 , l_program_id
200 , l_program_login_id
201 , l_program_application_id
202 , l_request_id
203 from
204 isc_dr_repair_orders_f bdr
205 , csd_repair_types_b crt
206 , csd_product_transactions cpt
207 , csd_product_transactions cpt1
208 , poa_dbi_rtx_f pdr
209 , isc_book_sum2_f ibs
210 where
211 bdr.repair_line_id = cpt.repair_line_id
212 and bdr.serial_number is null
213 and bdr.repair_type_id = crt.repair_type_id
214 and crt.repair_type_ref = 'RF'
215 and cpt.action_type = 'MOVE_IN'
216 -- and cpt.action_code = 'DEFECTIVES'
217 and cpt.req_line_id = pdr.requisition_line_id
218 and pdr.transaction_type = 'DELIVER'
219 and bdr.repair_line_id = cpt1.repair_line_id
220 and cpt1.action_type = 'MOVE_OUT'
221 -- and cpt1.action_code = 'USABLES'
222 and cpt1.order_line_id = ibs.line_id
223 and ibs.actual_shipment_date is not null
224 and bdr.status = 'C'
225 and bdr.date_closed is not null
226 and bdr.ro_creation_date >= g_global_start_date
227 group by
228 bdr.repair_line_id;
229
230 l_temp_rowcount := sql%rowcount;
231 bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
232 l_rowcount := l_rowcount + l_temp_rowcount;
233
234 l_stmt_id := 80;
235 commit;
236
237 l_stmt_id := 90;
238 -- Insertion of repair orders with Refurbishment(IO) repair type for
239 -- Serialized items
240
241 insert /*+ append parallel(isc_dr_mttr_f) */
242 into isc_dr_mttr_f
243 ( repair_line_id
244 , repair_start_date
245 , repair_end_date
246 , time_to_repair
247 , created_by
248 , creation_date
249 , last_update_date
250 , last_updated_by
251 , last_update_login
252 , program_id
253 , program_login_id
254 , program_application_id
255 , request_id
256 )
257 select /* use_hash(crt, bdr) parallel(bdr) */
258 bdr.repair_line_id repair_line_id
259 , pdr.transaction_date repair_start_date
260 , ibs.actual_shipment_date repair_end_date
261 , (ibs.actual_shipment_date - pdr.transaction_date) time_to_repair
262 , l_user_id
263 , sysdate
264 , sysdate
265 , l_user_id
266 , l_login_id
267 , l_program_id
268 , l_program_login_id
269 , l_program_application_id
270 , l_request_id
271 from
272 isc_dr_repair_orders_f bdr
273 , csd_repair_types_b crt
274 , csd_product_transactions cpt
275 , csd_product_transactions cpt1
276 , poa_dbi_rtx_f pdr
277 , mtl_unit_transactions mut
278 , isc_book_sum2_f ibs
279 where
280 bdr.repair_line_id = cpt.repair_line_id
281 and bdr.serial_number is not null
282 and bdr.repair_type_id = crt.repair_type_id
283 and crt.repair_type_ref = 'RF'
284 and cpt.action_type = 'MOVE_IN'
285 -- and cpt.action_code = 'DEFECTIVES'
286 and cpt.req_line_id = pdr.requisition_line_id
287 and pdr.transaction_type = 'DELIVER'
288 and pdr.inv_transaction_id = mut.transaction_id
289 and cpt.source_serial_number = mut.serial_number
290 and bdr.repair_line_id = cpt1.repair_line_id
291 and cpt1.action_type = 'MOVE_OUT'
292 -- and cpt1.action_code = 'USABLES'
293 and cpt1.order_line_id = ibs.line_id
294 and ibs.actual_shipment_date is not null
295 and cpt1.prod_txn_status = 'SHIPPED'
296 and bdr.status = 'C'
297 and bdr.date_closed is not null
298 and bdr.ro_creation_date >= g_global_start_date;
299
300 l_temp_rowcount := sql%rowcount;
301 bis_collection_utilities.log( 'Inserted ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
302 l_rowcount := l_rowcount + l_temp_rowcount;
303
304 l_stmt_id := 100;
305 commit;
306
307 l_stmt_id := 110;
308 -- Insertion into the incremental log table
309
310 insert into
311 isc_dr_inc
312 ( fact_name
313 , last_run_date
314 , created_by
315 , creation_date
316 , last_update_date
317 , last_updated_by
318 , last_update_login
319 , program_id
320 , program_login_id
321 , program_application_id
322 , request_id
323 )
324 values
325 ( 'ISC_DR_MTTR_F'
326 , l_run_date
327 , l_user_id
328 , sysdate
329 , sysdate
330 , l_user_id
331 , l_login_id
332 , l_program_id
333 , l_program_login_id
334 , l_program_application_id
335 , l_request_id
336 );
337
338 bis_collection_utilities.log( 'Inserted into table ISC_DR_INC', 1 );
339
340 l_stmt_id := 120;
341 commit;
342
343 l_stmt_id := 130;
344 bis_collection_utilities.wrapup( p_status => true
345 , p_period_from => g_global_start_date
346 , p_period_to => l_to_date
347 , p_count => l_rowcount
348 );
349
350 retcode := c_ok;
351
352 bis_collection_utilities.log( 'End Initial Load' );
353
354 exception
355
356 when l_exception then
357 rollback;
358 bis_collection_utilities.wrapup( p_status => false
359 , p_period_from => g_global_start_date
360 , p_period_to => l_to_date
361 , p_message => l_message
362 );
363 retcode := c_error;
364 errbuf := l_message;
365
366 when others then
367 rollback;
368 l_message := isc_depot_backlog_etl_pkg.err_mesg
369 ( sqlerrm
370 , l_proc_name
371 , l_stmt_id
372 );
373 bis_collection_utilities.put_line( l_message );
374 bis_collection_utilities.wrapup( p_status => false
375 , p_period_from => g_global_start_date
376 , p_period_to => l_to_date
377 , p_message => l_message
378 );
379 retcode := c_error;
380 errbuf := l_message;
381
382 end initial_load;
383
384 -- Incremental Load
385 -- Parameters:
386 -- retcode - 0 on successful completion, -1 on error and 1 for warning.
387 -- errbuf - empty on successful completion, message on error or warning
388 --
389
390 procedure incr_load
391 ( errbuf in out nocopy varchar2
392 , retcode in out nocopy number
393 )
394 is
395
396 l_stmt_id number;
397 l_run_date date;
398 l_proc_name constant varchar2(30) := 'incr_load';
399 l_last_run_date date;
400 l_err_msg varchar2(200);
401
402 l_user_id constant number := nvl(fnd_global.user_id,-1);
403 l_login_id constant number := nvl(fnd_global.login_id,-1);
404 l_program_id constant number := nvl(fnd_global.conc_program_id,-1);
405 l_program_login_id constant number := nvl(fnd_global.conc_login_id,-1);
406 l_program_application_id constant number := nvl(fnd_global.prog_appl_id,-1);
407 l_request_id constant number := nvl(fnd_global.conc_request_id,-1);
408
409 l_message varchar2(32000);
410 l_exception exception;
411 l_temp_rowcount number;
412 l_rowcount number;
413 l_to_date date;
414
415 begin
416
417 bis_collection_utilities.log( 'Begin Incremental Load' );
418
419 l_stmt_id := 0;
420 if not bis_collection_utilities.setup( g_object_name ) then
421 l_message := isc_depot_backlog_etl_pkg.err_mesg
422 ( 'Error in BIS_COLLECTION_UTILITIES.Setup'
423 , l_proc_name
424 , l_stmt_id
425 );
426 bis_collection_utilities.put_line( l_message );
427 raise l_exception;
428 end if;
429
430 l_stmt_id := 10;
431 if isc_depot_backlog_etl_pkg.check_initial_load_setup
432 ( x_global_start_date => g_global_start_date
433 , x_isc_schema => g_isc_schema
434 , x_message => l_message
435 ) <> c_ok then
436 raise l_exception;
437 end if;
438
439 bis_collection_utilities.log( 'Global Start Date: ' || fnd_date.date_to_displaydt(g_global_start_date), 1 );
440
441 l_stmt_id := 20;
442 if isc_depot_backlog_etl_pkg.get_last_run_date
443 ( p_fact_name => 'ISC_DR_MTTR_F'
444 , x_run_date => l_run_date
445 , x_message => l_message
446 ) <> c_ok then
447 raise l_exception;
448 end if;
449
450 if l_run_date is null then
451 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
452 raise l_exception;
453 end if;
454
455 bis_collection_utilities.log( 'MTTR From Date: ' || fnd_date.date_to_displaydt(l_run_date), 1 );
456
457 l_stmt_id := 30;
458 if isc_depot_backlog_etl_pkg.get_last_run_date
459 ( p_fact_name => 'ISC_DR_REPAIR_ORDERS_F'
460 , x_run_date => l_last_run_date
461 , x_message => l_message
462 ) <> c_ok then
463 raise l_exception;
464 end if;
465
466 if l_run_date is null then
467 l_message := 'Please run the Intial Load Request Set for the Depot Repair Management page.';
468 raise l_exception;
469 end if;
470
471 bis_collection_utilities.log( 'Repair Order From Date: ' || fnd_date.date_to_displaydt(l_last_run_date), 1 );
472
473 l_to_date := sysdate;
474
475 l_stmt_id := 40;
476 -- Insertion / Updation of repair orders with Non-refurbishment repair type
477 -- for Serialized and Non-serialized items
478
479 merge into
480 isc_dr_mttr_f fact
481 using
482 ( select
483 bdr.repair_line_id repair_line_id
484 , min(pdr.transaction_date) repair_start_date
485 , max(ibs.actual_shipment_date) repair_end_date
486 , max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
487 from
488 isc_dr_repair_orders_f bdr
489 , csd_repair_types_b crt
490 , csd_product_transactions cpt
491 , csd_product_transactions cpt1
492 , cs_estimate_details ced
496 where
493 , cs_estimate_details ced1
494 , poa_dbi_rtx_f pdr
495 , isc_book_sum2_f ibs
497 bdr.repair_line_id = cpt.repair_line_id
498 and bdr.repair_type_id = crt.repair_type_id
499 and crt.repair_type_ref <> 'RF'
500 and cpt.action_type in ('RMA','WALK_IN_RECEIPT')
501 and cpt.action_code = 'CUST_PROD'
502 and cpt.estimate_detail_id = ced.estimate_detail_id
503 and ced.order_line_id = pdr.oe_order_line_id
504 and pdr.transaction_type = 'DELIVER'
505 and bdr.repair_line_id = cpt1.repair_line_id
506 and cpt1.action_type in ('SHIP','WALK_IN_ISSUE')
507 and cpt1.action_code = 'CUST_PROD'
508 and cpt1.estimate_detail_id = ced1.estimate_detail_id
509 and ced1.order_line_id = ibs.line_id
510 and ibs.actual_shipment_date is not null
511 and bdr.status = 'C'
512 and bdr.ro_creation_date >= g_global_start_date
513 and bdr.date_closed > l_run_date
514 group by
515 bdr.repair_line_id
516 ) oltp
517 on
518 ( fact.repair_line_id = oltp.repair_line_id )
519 when matched then
520 update
521 set fact.repair_start_date = oltp.repair_start_date
522 , fact.repair_end_date = oltp.repair_end_date
523 , fact.time_to_repair = oltp.time_to_repair
524 , fact.last_update_date = sysdate
525 , fact.last_updated_by = l_user_id
526 , fact.last_update_login = l_login_id
527 , fact.program_id = l_program_id
528 , fact.program_login_id = l_program_login_id
529 , fact.program_application_id = l_program_application_id
530 , fact.request_id = l_request_id
531 when not matched then
532 insert
533 ( repair_line_id
534 , repair_start_date
535 , repair_end_date
536 , time_to_repair
537 , created_by
538 , creation_date
539 , last_update_date
540 , last_updated_by
541 , last_update_login
542 , program_id
543 , program_login_id
544 , program_application_id
545 , request_id
546 )
547 values
548 ( oltp.repair_line_id
549 , oltp.repair_start_date
550 , oltp.repair_end_date
551 , oltp.time_to_repair
552 , l_user_id
553 , sysdate
554 , sysdate
555 , l_user_id
556 , l_login_id
557 , l_program_id
558 , l_program_login_id
559 , l_program_application_id
560 , l_request_id
561 );
562
563 l_rowcount := sql%rowcount;
564 bis_collection_utilities.log( 'Merged ' || l_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
565
566 l_stmt_id := 50;
567 -- Insertion/Updation of repair orders with Refurbishment repair type
568 -- for Non-serialized items
569
570 merge into
571 isc_dr_mttr_f fact
572 using
573 ( select
574 bdr.repair_line_id repair_line_id
575 , min(pdr.transaction_date) repair_start_date
576 , max(ibs.actual_shipment_date) repair_end_date
577 , max(ibs.actual_shipment_date) - min(pdr.transaction_date) time_to_repair
578 from
579 isc_dr_repair_orders_f bdr
580 , csd_repair_types_b crt
581 , csd_product_transactions cpt
582 , csd_product_transactions cpt1
583 , poa_dbi_rtx_f pdr
584 , isc_book_sum2_f ibs
585 where
586 bdr.repair_line_id = cpt.repair_line_id
587 and bdr.serial_number is null
588 and bdr.repair_type_id = crt.repair_type_id
589 and crt.repair_type_ref = 'RF'
590 and cpt.action_type = 'MOVE_IN'
591 -- and cpt.action_code = 'DEFECTIVES'
592 and cpt.req_line_id = pdr.requisition_line_id
593 and pdr.transaction_type = 'DELIVER'
594 and bdr.repair_line_id = cpt1.repair_line_id
595 and cpt1.action_type = 'MOVE_OUT'
596 -- and cpt1.action_code = 'USABLES'
597 and cpt1.order_line_id = ibs.line_id
598 and ibs.actual_shipment_date is not null
599 and bdr.status = 'C'
600 and bdr.ro_creation_date >= g_global_start_date
601 and bdr.date_closed > l_run_date
602 group by
603 bdr.repair_line_id
604 ) oltp
605 on
606 ( fact.repair_line_id = oltp.repair_line_id )
607 when matched then
608 update
609 set fact.repair_start_date = oltp.repair_start_date
610 , fact.repair_end_date = oltp.repair_end_date
611 , fact.time_to_repair = oltp.time_to_repair
612 , fact.last_update_date = sysdate
613 , fact.last_updated_by = l_user_id
614 , fact.last_update_login = l_login_id
615 , fact.program_id = l_program_id
616 , fact.program_login_id = l_program_login_id
617 , fact.program_application_id = l_program_application_id
618 , fact.request_id = l_request_id
619 when not matched then
620 insert
621 ( repair_line_id
622 , repair_start_date
623 , repair_end_date
624 , time_to_repair
625 , created_by
626 , creation_date
627 , last_update_date
628 , last_updated_by
629 , last_update_login
630 , program_id
631 , program_login_id
632 , program_application_id
633 , request_id
634 )
635 values
636 ( oltp.repair_line_id
637 , oltp.repair_start_date
638 , oltp.repair_end_date
639 , oltp.time_to_repair
640 , l_user_id
641 , sysdate
642 , sysdate
643 , l_user_id
644 , l_login_id
645 , l_program_id
646 , l_program_login_id
647 , l_program_application_id
648 , l_request_id
649 );
650
651 l_temp_rowcount := sql%rowcount;
652 bis_collection_utilities.log( 'Merged ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
653 l_rowcount := l_rowcount + l_temp_rowcount;
654
655 l_stmt_id := 60;
656 -- Insertion/Updation of repair orders with Refurbishment repair type for
657 -- Serialized items
658
659 merge into
660 isc_dr_mttr_f fact
661 using
662 ( select
663 bdr.repair_line_id repair_line_id
664 , pdr.transaction_date repair_start_date
665 , ibs.actual_shipment_date repair_end_date
666 , ibs.actual_shipment_date - pdr.transaction_date time_to_repair
667 from
668 isc_dr_repair_orders_f bdr
669 , csd_repair_types_b crt
670 , csd_product_transactions cpt
671 , csd_product_transactions cpt1
672 , poa_dbi_rtx_f pdr
673 , mtl_unit_transactions mut
674 , isc_book_sum2_f ibs
675 where
676 bdr.repair_line_id = cpt.repair_line_id
677 and bdr.serial_number is not null
678 and bdr.repair_type_id = crt.repair_type_id
679 and crt.repair_type_ref = 'RF'
680 and cpt.action_type = 'MOVE_IN'
681 -- and cpt.action_code = 'DEFECTIVES'
682 and cpt.req_line_id = pdr.requisition_line_id
683 and pdr.transaction_type = 'DELIVER'
684 and pdr.inv_transaction_id = mut.transaction_id
685 and cpt.source_serial_number = mut.serial_number
686 and bdr.repair_line_id = cpt1.repair_line_id
687 and cpt1.action_type = 'MOVE_OUT'
688 -- and cpt1.action_code = 'USABLES'
689 and cpt1.order_line_id = ibs.line_id
690 and ibs.actual_shipment_date is not null
691 and cpt1.prod_txn_status = 'SHIPPED'
692 and bdr.status = 'C'
693 and bdr.ro_creation_date >= g_global_start_date
694 and bdr.date_closed > l_run_date
695 ) oltp
696 on
697 ( fact.repair_line_id = oltp.repair_line_id )
698 when matched then
699 update
700 set fact.repair_start_date = oltp.repair_start_date
701 , fact.repair_end_date = oltp.repair_end_date
702 , fact.time_to_repair = oltp.time_to_repair
703 , fact.last_update_date = sysdate
704 , fact.last_updated_by = l_user_id
705 , fact.last_update_login = l_login_id
706 , fact.program_id = l_program_id
707 , fact.program_login_id = l_program_login_id
708 , fact.program_application_id = l_program_application_id
709 , fact.request_id = l_request_id
710 when not matched then
711 insert
712 ( repair_line_id
713 , repair_start_date
714 , repair_end_date
715 , time_to_repair
716 , created_by
717 , creation_date
718 , last_update_date
719 , last_updated_by
720 , last_update_login
721 , program_id
722 , program_login_id
723 , program_application_id
724 , request_id
725 )
726 values
727 ( oltp.repair_line_id
728 , oltp.repair_start_date
729 , oltp.repair_end_date
730 , oltp.time_to_repair
731 , l_user_id
732 , sysdate
733 , sysdate
734 , l_user_id
735 , l_login_id
736 , l_program_id
737 , l_program_login_id
738 , l_program_application_id
739 , l_request_id
740 );
741
742 l_temp_rowcount := sql%rowcount;
743 bis_collection_utilities.log( 'Merged ' || l_temp_rowcount || ' rows into ISC_DR_MTTR_F' , 1 );
744 l_rowcount := l_rowcount + l_temp_rowcount;
745
746 l_stmt_id := 70;
747 update isc_dr_inc
748 set last_run_date = l_last_run_date
749 , last_update_date = sysdate
750 , last_updated_by = l_user_id
751 , last_update_login = l_login_id
752 , program_id = l_program_id
753 , program_login_id = l_program_login_id
754 , program_application_id = l_program_application_id
755 , request_id = l_request_id
756 WHERE fact_name = 'ISC_DR_MTTR_F' ;
757
758 bis_collection_utilities.log( 'Updated into table ISC_DR_INC', 1 );
759
760 l_stmt_id := 80;
761 commit;
762
763 l_stmt_id := 90;
764 bis_collection_utilities.wrapup( p_status => true
765 , p_period_from => l_run_date
766 , p_period_to => l_to_date
767 , p_count => l_rowcount
768 );
769
770 retcode := c_ok;
771
772 bis_collection_utilities.log( 'End Incremental Load' );
773
774 exception
775
776 when l_exception then
777 rollback;
778 bis_collection_utilities.wrapup( p_status => false
779 , p_period_from => l_run_date
780 , p_period_to => l_to_date
781 , p_message => l_message
782 );
783 retcode := c_error;
784 errbuf := l_message;
785
786 when others then
787 rollback;
788 l_message := isc_depot_backlog_etl_pkg.err_mesg
789 ( sqlerrm
790 , l_proc_name
791 , l_stmt_id
792 );
793 bis_collection_utilities.put_line( l_message );
794 bis_collection_utilities.wrapup( p_status => false
795 , p_period_from => l_run_date
796 , p_period_to => l_to_date
797 , p_message => l_message
798 );
799 retcode := c_error;
800 errbuf := l_message;
801
802 end incr_load;
803
804 -- Comments on the Initial and Incremental Load
805
806 -- During the insertion of repair orders with Refurbishment repair type for
807 -- serialized items , a check on the product transaction status is necessary
808 -- because any split made in the order line id, while shipping the serialized
809 -- items, will be reflected in the csd_product_transaction table only after
810 -- all the serialized items are shipped, however the status corresponding to
811 -- the repair order will be updated.
812
813 -- Currently, the condition check on the action codes are commented as action
814 -- types 'MOVE_IN' and 'MOVE_OUT' consists of only one action code
815 -- 'DEFECTIVES' and 'USABLES' respectively. However in the future, Depot
816 -- Repair may include more action codes -- for the 'MOVE_IN' and 'MOVE_OUT'
817 -- action types, this being the case these lines have to be uncommented.
818
819 -- For Non-refurbishment repair type, if shipment of items is done by spliting
820 -- the line id manually in Order Management form then the max of shipment date
821 -- corresponding to the line id stamped on the cs_estimate_details is alone
822 -- taken. The shipment date on the split line ids are not taken into
823 -- consideration. This requires a 'Connect By' to be included in the query
824 -- which may cause performance issues.
825
826 -- For refurbishment repair order with serialized item, a check on the serial
827 -- number for the item received is required , as when multiple repair orders
828 -- are created simultaneously for different serial numbers of an item, the
829 -- requisition line ids for the ROs are the same. Hence to get the accurate
830 -- repair start date corresponding to each of the repair order, a check is
831 -- required on the serial number of the item received.
832
833 end isc_depot_mttr_etl_pkg;