[Home] [Help]
PACKAGE BODY: APPS.CSD_MIGRATE_FROM_115X_PKG
Source
1 PACKAGE BODY CSD_Migrate_From_115X_PKG AS
2 /* $Header: csdmig1b.pls 115.10 2004/05/07 02:21:38 saupadhy ship $ */
3
4 PROCEDURE csd_repairs_mig(p_slab_number IN NUMBER DEFAULT 1) IS
5
6 Type NumTabType is VARRAY(10000) of NUMBER;
7 repair_line_id_mig NumTabType;
8
9 Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
10 rowid_mig RowidTabtype;
11
12 v_min NUMBER;
13 v_max NUMBER;
14 v_error_text VARCHAR2(2000);
15 MAX_BUFFER_SIZE NUMBER := 500;
16
17 error_process exception;
18
19 CURSOR csd_repairs_cursor (p_start number, p_end number) is
20 select cr.repair_line_id,
21 cr.rowid
22 from csd_repairs cr
23 where cr.repair_mode IS NULL
24 and cr.repair_line_id >= p_start
25 and cr.repair_line_id <= p_end;
26
27 BEGIN
28
29 -- Get the Slab Number for the table
30 Begin
31 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS'
32 ,'CSD'
33 ,p_slab_number
34 ,v_min
35 ,v_max);
36 if v_min is null then
37 return;
38 end if;
39 End;
40
41 -- Migration code for CSD_REPAIRS
42 OPEN csd_repairs_cursor(v_min,v_max);
43 LOOP
44 FETCH csd_repairs_cursor bulk collect into
45 repair_line_id_mig,
46 rowid_mig
47 LIMIT MAX_BUFFER_SIZE;
48
49 FOR j in 1..repair_line_id_mig.count
50 LOOP
51 SAVEPOINT CSD_REPAIRS;
52 Begin
53 UPDATE csd_repairs
54 SET repair_mode = 'WIP',
55 auto_process_rma = 'N',
56 last_update_date = sysdate,
57 last_updated_by = fnd_global.user_id,
58 last_update_login = fnd_global.login_id
59 WHERE rowid = rowid_mig(j);
60 IF SQL%NOTFOUND then
61 Raise error_process;
62 End If;
63 Exception
64 When error_process then
65 ROLLBACK to CSD_REPAIRS;
66 v_error_text := substr(sqlerrm,1,1000)||'Repair Line Id:'||repair_line_id_mig(j);
67 INSERT INTO CSD_UPG_ERRORS
68 (ORIG_SYSTEM_REFERENCE,
69 TARGET_SYSTEM_REFERENCE,
70 ORIG_SYSTEM_REFERENCE_ID,
71 UPGRADE_DATETIME,
72 ERROR_MESSAGE,
73 MIGRATION_PHASE)
74 VALUES( 'CSD_REPAIRS'
75 ,'CSD_REPAIRS'
76 ,repair_line_id_mig(j)
77 ,sysdate
78 ,v_error_text
79 ,'11.5.8' );
80
81 commit;
82
83 raise_application_error( -20000, 'Error while migrating CSD_REPAIRS table data: Error while updating CSD_REPAIRS. '|| v_error_text);
84
85
86 End;
87 END LOOP;
88
89 COMMIT;
90 EXIT WHEN csd_repairs_cursor%notfound;
91 END LOOP;
92
93 if csd_repairs_cursor%isopen then
94 close csd_repairs_cursor;
95 end if;
96
97 COMMIT;
98
99 END csd_repairs_mig;
100
101
102 PROCEDURE csd_product_txn_lines_mig (p_slab_number IN NUMBER DEFAULT 1) IS
103
104 l_booked_flag BOOLEAN;
105 l_skip_flag BOOLEAN;
106 l_estimate_status varchar2(30);
107 l_uom varchar2(30) := 'DAY';
108 l_dummy varchar2(1);
109 v_min NUMBER;
110 v_max NUMBER;
111
112 Type NumTabType is VARRAY(10000) of NUMBER;
113 estimate_detail_id_mig NumTabType;
114 source_id_mig NumTabType;
115 order_header_id_mig NumTabType;
116 order_line_id_mig NumTabType;
117 quantity_required_mig NumTabType;
118 repair_type_id_mig NumTabType;
119
120 Type DateTabType is VARRAY(1000) of Date;
121 creation_date_mig DateTabType;
122
123 Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
124 rowid_mig RowidTabtype;
125
126
127 l_repair_estimate_id CSD_REPAIR_ESTIMATE_LINES.REPAIR_ESTIMATE_ID%TYPE;
128 l_estimate_detail_id CSD_REPAIR_ESTIMATE_LINES.ESTIMATE_DETAIL_ID%TYPE;
129 l_creation_date DATE;
130
131 l_rma varchar2(30) := 'RMA';
132 l_ship varchar2(30) := 'SHIP';
133 l_cust_prod varchar2(30) := 'CUST_PROD';
134
135 l_ACTION_TYPE CSD_PRODUCT_TRANSACTIONS.ACTION_TYPE%TYPE;
136 l_ACTION_CODE CSD_PRODUCT_TRANSACTIONS.ACTION_CODE%TYPE;
137 l_INTERFACE_TO_OM_FLAG CSD_PRODUCT_TRANSACTIONS.INTERFACE_TO_OM_FLAG%TYPE;
138 l_BOOK_SALES_ORDER_FLAG CSD_PRODUCT_TRANSACTIONS.BOOK_SALES_ORDER_FLAG%TYPE;
139 l_RELEASE_SALES_ORDER_FLAG CSD_PRODUCT_TRANSACTIONS.RELEASE_SALES_ORDER_FLAG%TYPE;
140 l_SHIP_SALES_ORDER_FLAG CSD_PRODUCT_TRANSACTIONS.SHIP_SALES_ORDER_FLAG%TYPE;
141 l_PROD_TXN_STATUS CSD_PRODUCT_TRANSACTIONS.PROD_TXN_STATUS%TYPE;
142 l_PROD_TXN_CODE CSD_PRODUCT_TRANSACTIONS.PROD_TXN_CODE%TYPE;
143
144 l_repair_line_id Number;
145 l_released_status varchar2(1);
146 l_repair_type_ref varchar2(30);
147 l_ENTERED varchar2(30) := 'ENTERED';
148 l_SUBMITTED varchar2(30) := 'SUBMITTED';
149 l_BOOKED varchar2(30) := 'BOOKED';
150 l_RELEASED varchar2(30) := 'RELEASED';
151 l_SHIPPED varchar2(30) := 'SHIPPED';
152
153 error_process exception;
154 v_error_text VARCHAR2(2000);
155 MAX_BUFFER_SIZE NUMBER := 500;
156
157 CURSOR PRODUCT_TXN_LINES (p_start number,
158 p_end number ) IS
159 select ced.estimate_detail_id,
160 ced.source_id,
161 ced.order_header_id,
162 ced.order_line_id,
163 ced.quantity_required,
164 ced.creation_date,
165 cra.repair_type_id,
166 ced.rowid
167 from cs_estimate_details ced,
168 csd_repairs cra
169 where ced.source_id = cra.repair_line_id
170 and ced.inventory_item_id = cra.inventory_item_id
171 and ced.source_code = 'DR'
172 and not exists ( select '*' from csd_product_transactions cpt
173 where cpt.estimate_detail_id = ced.estimate_detail_id)
174 and not exists ( select '*' from csd_repair_estimate_lines crel
175 where crel.estimate_detail_id = ced.estimate_detail_id)
176 and ced.estimate_detail_id >= p_start
177 and ced.estimate_detail_id <= p_end;
178 -- Shiv Ragunathan, 11/19/03, Added the above 'not exists' clause to
179 -- prevent data in cs_estimate_details from being migrated,
180 -- if this was created from Depot Repair in 11.5.8. IN 11.5.7, this
181 -- was not possible. This is introduced as this code is also run,
182 -- when upgrading from 11.5.8 ( or 11.5.9 ) as well.
183
184 -- Following cursor gets all the delivery line for a given order header id and order line id
185 -- Bug# 3615184
186 Cursor Delivery_line_Status_cur( p_Order_Header_id Number, p_Order_line_id Number) Is
187 Select Released_Status
188 From Wsh_Delivery_Details
189 Where source_header_id = p_order_Header_id
190 and source_line_id = p_order_line_id ;
191 l_prod_txn_line_released_flag Varchar2(1) ;
192 l_prod_txn_line_shipped_flag Varchar2(1) ;
193
194 BEGIN
195
196 -- Get the Slab Number for the table
197 Begin
198 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CS_ESTIMATE_DETAILS'
199 ,'CSD'
200 ,p_slab_number
201 ,v_min
202 ,v_max);
203 if v_min is null then
204 return;
205 end if;
206 End;
207
208
209 OPEN PRODUCT_TXN_LINES(v_min,v_max);
210 LOOP
211
212 FETCH PRODUCT_TXN_LINES bulk collect into
213 estimate_detail_id_mig,
214 source_id_mig,
215 order_header_id_mig,
216 order_line_id_mig,
217 quantity_required_mig,
218 creation_date_mig,
219 repair_type_id_mig,
220 rowid_mig
221 LIMIT MAX_BUFFER_SIZE;
222
223 -- Migrate csd_product_transactions
224 FOR j in 1..estimate_detail_id_mig.count
225 LOOP
226 Begin
227
228 SAVEPOINT product_txn;
229
230 l_repair_line_id := source_id_mig(j);
231 l_estimate_detail_id := estimate_detail_id_mig(j);
232 l_action_code := l_cust_prod;
233 l_prod_txn_code := 'PRE';
234 l_creation_date := creation_date_mig(j);
235
236 IF quantity_required_mig(j) > 0 then
237 l_action_type := l_ship;
238 Else
239 l_action_type := l_rma;
240 End IF;
241
242 IF order_header_id_mig(j) is null then
243 l_INTERFACE_TO_OM_FLAG := 'N';
244 l_BOOK_SALES_ORDER_FLAG := 'N';
245 l_RELEASE_SALES_ORDER_FLAG := 'N';
246 l_SHIP_SALES_ORDER_FLAG := 'N';
247 l_PROD_TXN_STATUS := l_entered;
248 Else
249 l_INTERFACE_TO_OM_FLAG := 'Y';
250 l_PROD_TXN_STATUS := l_submitted;
251 END IF;
252
253 Begin
254 Select 'x'
255 into l_dummy
256 from oe_order_headers_all
257 where header_id = order_header_id_mig(j)
258 and booked_flag = 'Y';
259 l_booked_flag := TRUE;
260 Exception
261 When No_data_found then
262 l_booked_flag := FALSE;
263 End ;
264
265 If NOT(l_booked_flag) then
266 l_BOOK_SALES_ORDER_FLAG := 'N';
267 l_RELEASE_SALES_ORDER_FLAG := 'N';
268 l_SHIP_SALES_ORDER_FLAG := 'N';
269 Else
270 l_BOOK_SALES_ORDER_FLAG := 'Y';
271 l_PROD_TXN_STATUS := l_booked;
272
273 IF quantity_required_mig(j) > 0 then
274 Begin
275 -- To fix bug 3615184 added following lines.
276 -- Released_Status column can have following values in 11.5.7
277 -- Released_Status = 'Y' means Pick Released
278 -- Released_Status = 'B' means Back Ordered
279 -- Released_Status = 'S' means Released To Warehouse
280 -- Released_Status = 'C' means Interfaced or shipped
281 -- Released_Status = 'R' Ready to Release
282 -- Released_Status = 'N' Not Ready to Release
283 --
284 -- Initialize these variables to Null before using them
285 l_prod_txn_line_released_flag := Null;
286 l_prod_txn_line_shipped_flag := Null;
287 For Delivery_line_Status_Rec In Delivery_line_Status_cur
288 (Order_Header_Id_mig(j), Order_line_id_mig(j)) Loop
289 If Delivery_Line_Status_Rec.Released_Status = 'C' Then
290 l_prod_txn_line_shipped_flag := 'Y';
291 ElsIf Delivery_Line_Status_Rec.Released_Status in ( 'Y','B','S') Then
292 l_prod_txn_line_released_flag := 'Y';
293 End if;
294 End Loop;
295 If l_prod_txn_line_released_flag = 'Y' then
296 l_RELEASE_SALES_ORDER_FLAG := 'Y';
297 l_SHIP_SALES_ORDER_FLAG := 'N';
298 l_PROD_TXN_STATUS := l_released;
299 ElsIf l_prod_txn_line_Shipped_flag = 'Y' then
300 l_RELEASE_SALES_ORDER_FLAG := 'Y';
301 l_SHIP_SALES_ORDER_FLAG := 'Y';
302 l_PROD_TXN_STATUS := l_shipped;
303 Else
304 l_RELEASE_SALES_ORDER_FLAG := 'N';
305 l_SHIP_SALES_ORDER_FLAG := 'N';
306 l_PROD_TXN_STATUS := l_booked;
307 End If;
308 End ;
309 End If;
310 /*************
311 If l_released_status = 'C' then
312 l_RELEASE_SALES_ORDER_FLAG := 'Y';
313 l_SHIP_SALES_ORDER_FLAG := 'Y';
314 l_PROD_TXN_STATUS := l_shipped;
315 Elsif l_released_status in ('Y','B','S') then
316 l_RELEASE_SALES_ORDER_FLAG := 'Y';
317 l_SHIP_SALES_ORDER_FLAG := 'N';
318 l_PROD_TXN_STATUS := l_released;
319 Else
320 l_RELEASE_SALES_ORDER_FLAG := 'N';
321 l_SHIP_SALES_ORDER_FLAG := 'N';
322 l_PROD_TXN_STATUS := l_booked;
323 End If;
324 *************/
325
326 End If;
327
328 Begin
329 INSERT INTO CSD_PRODUCT_TRANSACTIONS(
330 PRODUCT_TRANSACTION_ID,
331 REPAIR_LINE_ID,
332 ESTIMATE_DETAIL_ID,
333 ACTION_TYPE,
334 ACTION_CODE,
335 LOT_NUMBER,
336 SUB_INVENTORY,
337 INTERFACE_TO_OM_FLAG,
338 BOOK_SALES_ORDER_FLAG,
339 RELEASE_SALES_ORDER_FLAG,
340 SHIP_SALES_ORDER_FLAG,
341 PROD_TXN_STATUS,
342 PROD_TXN_CODE,
343 LAST_UPDATE_DATE,
344 CREATION_DATE,
345 LAST_UPDATED_BY,
346 CREATED_BY,
347 LAST_UPDATE_LOGIN,
348 ATTRIBUTE1,
349 ATTRIBUTE2,
350 ATTRIBUTE3,
351 ATTRIBUTE4,
352 ATTRIBUTE5,
353 ATTRIBUTE6,
354 ATTRIBUTE7,
355 ATTRIBUTE8,
356 ATTRIBUTE9,
357 ATTRIBUTE10,
358 ATTRIBUTE11,
359 ATTRIBUTE12,
360 ATTRIBUTE13,
361 ATTRIBUTE14,
362 ATTRIBUTE15,
363 CONTEXT,
364 OBJECT_VERSION_NUMBER
365 ) VALUES (
366 CSD_PRODUCT_TRANSACTIONS_S1.nextval
367 ,l_repair_line_id
368 ,l_estimate_detail_id
369 ,l_action_type
370 ,l_action_code
371 ,NULL
372 ,NULL
373 ,l_interface_to_om_flag
374 ,l_book_sales_order_flag
375 ,l_release_sales_order_flag
376 ,l_ship_sales_order_flag
377 ,l_prod_txn_status
378 ,l_prod_txn_code
379 ,sysdate
380 ,l_creation_date
381 ,FND_GLOBAL.USER_ID
382 ,FND_GLOBAL.USER_ID
383 ,FND_GLOBAL.LOGIN_ID
384 ,NULL
385 ,NULL
386 ,NULL
387 ,NULL
388 ,NULL
389 ,NULL
390 ,NULL
391 ,NULL
392 ,NULL
393 ,NULL
394 ,NULL
395 ,NULL
396 ,NULL
397 ,NULL
398 ,NULL
399 ,NULL
400 ,1 );
401
402 Exception
403 When OTHERS THEN
404 v_error_text := 'Error Msg :'||substr(sqlerrm,1,1000)||'est_detail_id :'||estimate_detail_id_mig(j);
405 Raise error_process;
406 End;
407
408 Exception
409 When error_process then
410 ROLLBACK to product_txn;
411 INSERT INTO CSD_UPG_ERRORS
412 (ORIG_SYSTEM_REFERENCE,
413 TARGET_SYSTEM_REFERENCE,
414 ORIG_SYSTEM_REFERENCE_ID,
415 UPGRADE_DATETIME,
416 ERROR_MESSAGE,
417 MIGRATION_PHASE)
418 VALUES(
419 'CS_ESTIMATE_DETAILS'
420 ,'CSD_PRODUCT_TRANSACTIONS'
421 ,estimate_detail_id_mig(j)
422 ,sysdate
423 ,v_error_text
424 ,'11.5.8' );
425
426 commit;
427
428 raise_application_error( -20000, 'Error while migrating CSD_PRODUCT_TRANSACTIONS table data. '|| v_error_text);
429
430
431 When others then
432 ROLLBACK to product_txn;
433 v_error_text := 'Err Msg:'||substr(sqlerrm,1,1000)||'Estimate Detail Id:'||estimate_detail_id_mig(j);
434 INSERT INTO CSD_UPG_ERRORS
435 (ORIG_SYSTEM_REFERENCE,
436 TARGET_SYSTEM_REFERENCE,
437 ORIG_SYSTEM_REFERENCE_ID,
438 UPGRADE_DATETIME,
439 ERROR_MESSAGE,
440 MIGRATION_PHASE)
441 VALUES(
442 'CS_ESTIMATE_DETAILS'
443 ,'CSD_PRODUCT_TRANSACTIONS'
444 ,estimate_detail_id_mig(j)
445 ,sysdate
446 ,v_error_text
447 ,'11.5.8' );
448
449 commit;
450
451 raise_application_error( -20000, 'Error while migrating CSD_PRODUCT_TRANSACTIONS table data. '|| v_error_text);
452
453 End;
454 End Loop; --end of for loop
455
456 commit;
457
458 Exit when product_txn_lines%notfound;
459
460 End Loop;
461
462 IF product_txn_lines%isopen then
463 close product_txn_lines;
464 END IF;
465
466 COMMIT;
467
468 End Csd_product_txn_lines_mig;
469
470
471 PROCEDURE CSD_REPAIR_ESTIMATE_MIG (p_slab_number IN NUMBER DEFAULT 1) IS
472
473 TYPE NumArray IS VARRAY(10000) OF NUMBER;
474 TYPE Char3Array IS VARRAY(10000) OF VARCHAR2(3);
475 TYPE Char240Array IS VARRAY(10000) OF VARCHAR2(240);
476 TYPE Char30Array IS VARRAY(10000) OF VARCHAR2(30);
477 TYPE DateArray IS VARRAY(10000) OF DATE;
478
479 l_rep_estimate_id_arr NumArray := NumArray();
480 l_source_id_arr NumArray := NumArray();
481 l_creation_date_arr DateArray := DateArray();
482 l_promise_date_arr DateArray := DateArray();
483 l_summary_arr Char240Array := Char240Array();
484 l_approval_status_arr Char30Array := Char30Array();
485
486 -- Local counters
487 l_array_size NUMBER; -- Number of elements in varrays
488 l_min_id NUMBER; -- Minimum Repair Line ID
489 l_max_id NUMBER; -- Maximum Repair Line ID
490
491 l_dummy varchar2(1);
492 l_repair_estimate_id NUMBER;
493 l_repair_line_id NUMBER;
494 l_estimate_status CSD_REPAIR_ESTIMATE.ESTIMATE_STATUS%TYPE;
495 l_estimate_date DATE;
496 l_work_summary CSD_REPAIR_ESTIMATE.WORK_SUMMARY%TYPE;
497 l_lead_time CSD_REPAIR_ESTIMATE.LEAD_TIME%TYPE;
498 l_lead_time_uom CSD_REPAIR_ESTIMATE.LEAD_TIME_UOM%TYPE := 'DAY';
499 l_estimate_freeze_flag CSD_REPAIR_ESTIMATE.ESTIMATE_FREEZE_FLAG%TYPE := 'N';
500
501 v_error_text VARCHAR2(2000);
502 MAX_BUFFER_SIZE NUMBER := 500;
503
504 -- estimate header cursor
505 CURSOR CUR_ESTIMATE_HEADER (p_min_id number, p_max_id number) IS
506 select distinct
507 ced.source_id,
508 cia.summary,
509 cra.creation_date,
510 NVL(cra.promise_date, cra.creation_date),
511 cra.approval_status
512 from cs_estimate_details ced,
513 cs_incidents_all_b cia,
514 csd_repairs cra
515 where ced.source_id = cra.repair_line_id
516 and ced.incident_id = cia.incident_id
517 and ced.inventory_item_id <> cra.inventory_item_id
518 and ced.source_code = 'DR'
519 and ced.estimate_detail_id >= p_min_id -- slab limits
520 and ced.estimate_detail_id <= p_max_id -- slab limits
521 and not exists ( select '*' from csd_repair_estimate cre
522 where cre.repair_line_id = ced.source_id)
523 and not exists ( select '*' from csd_product_transactions cpt
524 where cpt.estimate_detail_id = ced.estimate_detail_id);
525 -- Shiv Ragunathan, 11/19/03, Added the above 'not exists' clause to
526 -- prevent data in cs_estimate_details from being migrated,
527 -- if this was created from Depot Repair in 11.5.8. IN 11.5.7, this
528 -- was not possible. This is introduced as this code is also run,
529 -- when upgrading from 11.5.8 ( or 11.5.9 ) as well.
530
531 BEGIN
532
533 -- Initialize Update range boundaries
534 csd_mig_slabs_pkg.get_table_slabs ( 'CS_ESTIMATE_DETAILS',
535 'CSD',
536 p_slab_number,
537 l_min_id,
538 l_max_id);
539
540
541 IF l_min_id IS NULL THEN
542 RETURN;
543 END IF;
544
545 -- get default values if needed into variables from cursors
546 -- Migrate csd_repair_estimate
547 Begin
548 select 'x'
549 into l_dummy
550 from fnd_lookups
551 where lookup_type = 'CSD_UNIT_OF_MEASURE'
552 and lookup_code = l_lead_time_uom;
553 Exception
554 When no_data_found then
555 v_error_text := 'No Data found for lookup code'||l_lead_time_uom||'of lookup type CSD_UNIT_OF_MEASURE';
556 RAISE_APPLICATION_ERROR(-20000, v_error_text);
557 End;
558
559 -- Open main cursor
560 OPEN cur_estimate_header( l_min_id, l_max_id );
561 LOOP
562 -- Start fetch loop. Use BULK COLLECT option. Fetch row
563 -- columns into PL/SQL arrays.
564 FETCH cur_estimate_header
565 BULK COLLECT INTO
566 l_source_id_arr,
567 l_summary_arr,
568 l_creation_date_arr,
569 l_promise_date_arr,
570 l_approval_status_arr
571 LIMIT MAX_BUFFER_SIZE;
572
573 -- get total count
574 l_array_size := l_source_id_arr.COUNT;
575
576 FOR i IN 1..l_array_size
577 LOOP
578
579 SAVEPOINT ESTIMATE_HEADER;
580
581 IF l_approval_status_arr(i) = 'A' then
582 l_estimate_status := 'ACCEPTED';
583 Elsif l_approval_status_arr(i) = 'R' then
584 l_estimate_status := 'REJECTED';
585 Else
586 l_estimate_status := 'NEW';
587 End If;
588
589
590 l_repair_line_id := l_source_id_arr(i);
591 l_estimate_date := l_creation_date_arr(i);
592 l_work_summary := NVL(l_summary_arr(i),'Migration');
593 l_lead_time := trunc(l_promise_date_arr(i)-l_creation_date_arr(i));
594
595 IF l_lead_time = 0 then
596 l_lead_time := 1;
597 End If;
598
599 BEGIN
600 INSERT INTO CSD_REPAIR_ESTIMATE
601 ( REPAIR_ESTIMATE_ID
602 ,REPAIR_LINE_ID
603 ,ESTIMATE_STATUS
604 ,ESTIMATE_DATE
605 ,WORK_SUMMARY
606 ,LEAD_TIME
607 ,LEAD_TIME_UOM
608 ,CREATION_DATE
609 ,CREATED_BY
610 ,LAST_UPDATED_BY
611 ,LAST_UPDATE_DATE
612 ,LAST_UPDATE_LOGIN
613 ,OBJECT_VERSION_NUMBER
614 ,ESTIMATE_FREEZE_FLAG)
615 VALUES (
616 CSD_REPAIR_ESTIMATE_S1.nextval
617 ,l_repair_line_id
618 ,l_estimate_status
619 ,l_estimate_date
620 ,l_work_summary
621 ,l_lead_time
622 ,l_lead_time_uom
623 ,l_estimate_date
624 ,FND_GLOBAL.USER_ID
625 ,FND_GLOBAL.USER_ID
626 ,sysdate
627 ,FND_GLOBAL.LOGIN_ID
628 ,1
629 ,l_estimate_freeze_flag);
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 v_error_text := substr(SQLERRM,2000);
634 ROLLBACK to ESTIMATE_HEADER;
635 INSERT INTO csd_upg_errors
636 (orig_system_reference,
637 target_system_reference,
638 orig_system_reference_id,
639 upgrade_datetime,
640 error_message,
641 migration_phase)
642 VALUES ('CS_ESTIMATE_DETAILS',
643 'CSD_REPAIR_ESTIMATE',
644 l_repair_line_id,
645 sysdate,
646 v_error_text,
647 '11.5.8');
648
649 commit;
650
651 raise_application_error( -20000, 'Error while migrating CSD_ESTIMATE_DETAILS table data: Error while inserting into CSD_REPAIR_ESTIMATE. '|| v_error_text);
652
653
654 END ;
655
656 END LOOP;-- end of inner
657
658 COMMIT;
659
660 EXIT WHEN cur_estimate_header%NOTFOUND;
661
662 END LOOP;-- End of outer cursor
663
664 IF cur_estimate_header%isopen THEN
665 CLOSE cur_estimate_header;
666 END IF;
667
668 COMMIT;
669 -- Api body ends here
670
671 END CSD_REPAIR_ESTIMATE_MIG;
672
673 PROCEDURE CSD_REPAIR_ESTIMATE_LINES_MIG (p_slab_number IN NUMBER DEFAULT 1) IS
674
675 TYPE NumArray IS VARRAY(10000) OF NUMBER;
676 TYPE Char3Array IS VARRAY(10000) OF VARCHAR2(3);
677 TYPE Char15Array IS VARRAY(10000) OF VARCHAR2(15);
678 TYPE Char30Array IS VARRAY(10000) OF VARCHAR2(30);
679 TYPE DateArray IS VARRAY(10000) OF DATE;
680
681 l_repair_estimate_id_arr NumArray := NumArray();
682 l_estimate_detail_id_arr NumArray := NumArray();
683 l_source_id_arr NumArray := NumArray();
684 l_order_header_id_arr NumArray := NumArray();
685 l_order_line_id_arr NumArray := NumArray();
686 l_creation_date_arr DateArray := DateArray();
687
688 -- Local counters
689 l_array_size NUMBER; -- Number of elements in varrays
690 l_min_id NUMBER; -- Minimum Repair Line ID
691 l_max_id NUMBER; -- Maximum Repair Line ID
692
693 l_repair_estimate_id CSD_REPAIR_ESTIMATE_LINES.REPAIR_ESTIMATE_ID%TYPE;
694 l_estimate_detail_id CSD_REPAIR_ESTIMATE_LINES.ESTIMATE_DETAIL_ID%TYPE;
695 l_item_cost CSD_REPAIR_ESTIMATE_LINES.ITEM_COST%TYPE;
696 l_creation_date DATE;
697
698 v_error_text VARCHAR2(2000);
699 MAX_BUFFER_SIZE NUMBER := 500;
700
701 -- estimate line cursor
702 CURSOR CUR_ESTIMATE_LINES (p_min_id number, p_max_id number) IS
703 select ced.estimate_detail_id,
704 ced.source_id,
705 ced.order_header_id,
706 ced.order_line_id,
707 ced.creation_date,
708 cre.repair_estimate_id
709 from cs_estimate_details ced,
710 csd_repairs cra,
711 csd_repair_estimate cre
712 where ced.source_id = cra.repair_line_id
713 and cra.repair_line_id = cre.repair_line_id
714 and ced.inventory_item_id <> cra.inventory_item_id
715 and ced.source_code = 'DR'
716 and ced.estimate_detail_id >= p_min_id -- slab limits
717 and ced.estimate_detail_id <= p_max_id -- slab limits
718 and not exists ( select '*' from csd_repair_estimate_lines crel
719 where crel.estimate_detail_id = ced.estimate_detail_id)
720 and not exists ( select '*' from csd_product_transactions cpt
721 where cpt.estimate_detail_id = ced.estimate_detail_id);
722 -- Shiv Ragunathan, 11/19/03, Added the above 'not exists' clause to
723 -- prevent data in cs_estimate_details from being migrated,
724 -- if this was created from Depot Repair in 11.5.8. IN 11.5.7, this
725 -- was not possible. This is introduced as this code is also run,
726 -- when upgrading from 11.5.8 ( or 11.5.9 ) as well.
727
728
729 BEGIN
730
731 -- Initialize Update range boundaries
732 csd_mig_slabs_pkg.get_table_slabs ( 'CS_ESTIMATE_DETAILS',
733 'CSD',
734 p_slab_number,
735 l_min_id,
736 l_max_id);
737
738
739 IF l_min_id IS NULL THEN
740 RETURN;
741 END IF;
742
743 -- get default values if needed into variables from cursors
744 -- Migrate csd_repair_estimate
745
746 -- Open main cursor
747 OPEN cur_estimate_lines( l_min_id, l_max_id );
748
749 LOOP
750 -- Start fetch loop. Use BULK COLLECT option. Fetch row
751 -- columns into PL/SQL arrays.
752 FETCH cur_estimate_lines
753 BULK COLLECT INTO
754 l_estimate_detail_id_arr,
755 l_source_id_arr,
756 l_order_header_id_arr,
757 l_order_line_id_arr,
758 l_creation_date_arr,
759 l_repair_estimate_id_arr
760 LIMIT MAX_BUFFER_SIZE;
761
762 -- get total count
763 l_array_size := l_estimate_detail_id_arr.COUNT;
764
765 FOR i IN 1..l_array_size
766 LOOP
767
768 SAVEPOINT ESTIMATE_LINES;
769
770 l_repair_estimate_id := l_repair_estimate_id_arr(i);
771 l_estimate_detail_id := l_estimate_detail_id_arr(i);
772 l_creation_date := l_creation_date_arr(i);
773 l_item_cost := 1;
774
775 BEGIN
776 INSERT INTO CSD_REPAIR_ESTIMATE_LINES(
777 REPAIR_ESTIMATE_LINE_ID
778 ,REPAIR_ESTIMATE_ID
779 ,CREATION_DATE
780 ,CREATED_BY
781 ,LAST_UPDATED_BY
782 ,LAST_UPDATE_DATE
783 ,LAST_UPDATE_LOGIN
784 ,OBJECT_VERSION_NUMBER
785 ,ESTIMATE_DETAIL_ID
786 ,ITEM_COST)
787 VALUES (
788 CSD_REPAIR_ESTIMATE_LINES_S1.nextval
789 ,l_repair_estimate_id
790 ,l_creation_date
791 ,FND_GLOBAL.USER_ID
792 ,FND_GLOBAL.USER_ID
793 ,sysdate
794 ,FND_GLOBAL.LOGIN_ID
795 ,1
796 ,l_estimate_detail_id
797 ,l_item_cost);
798 EXCEPTION
799 WHEN OTHERS THEN
800 v_error_text := substr(SQLERRM,2000);
801 ROLLBACK to ESTIMATE_LINES;
802 INSERT INTO csd_upg_errors
803 (orig_system_reference,
804 target_system_reference,
805 orig_system_reference_id,
806 upgrade_datetime,
807 error_message,
808 migration_phase)
809 VALUES ('CS_ESTIMATE_DETAILS',
810 'CSD_REPAIR_ESTIMATE_LINES',
811 l_estimate_detail_id,
812 sysdate,
813 v_error_text,
814 '11.5.8');
815 commit;
816
817 raise_application_error( -20000, 'Error while migrating CS_ESTIMATE_DETAILS table data: Error while inserting into CSD_REPAIR_ESTIMATE_LINES. '|| v_error_text);
818
819 END;
820
821 END LOOP;-- end of inner
822
823 COMMIT;
824 EXIT WHEN cur_estimate_lines%NOTFOUND;
825
826 END LOOP;-- End of outer cursor
827
828 IF cur_estimate_lines%isopen THEN
829 CLOSE cur_estimate_lines;
830 END IF;
831
832 COMMIT;
833 -- Api body ends here
834
835 END CSD_REPAIR_ESTIMATE_LINES_MIG;
836
837 -- Migration procedure for CSD_REPAIR_JOB_XREF
838 PROCEDURE CSD_REPAIR_JOB_XREF_MIG (p_slab_number NUMBER DEFAULT 1) IS
839
840 Type NumTabType is VARRAY(10000) of NUMBER;
841 inventory_item_id_mig NumTabType;
842 repair_job_xref_id_mig NumTabType;
843
844 Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
845 rowid_mig RowidTabtype;
846
847 CURSOR csd_repair_job_xref_cursor (p_min number,p_max number) is
848 select crjx.repair_job_xref_id,
849 cr.inventory_item_id,
850 crjx.rowid
851 from csd_repair_job_xref crjx,
852 csd_repairs cr
853 where crjx.repair_line_id = cr.repair_line_id
854 and crjx.repair_job_xref_id >= p_min
855 and crjx.repair_job_xref_id <= p_max
856 and crjx.inventory_item_id is null;
857
858 l_min NUMBER;
859 l_max NUMBER;
860 l_error_text VARCHAR2(2000);
861
862 MAX_BUFFER_SIZE NUMBER := 500;
863
864 BEGIN
865
866 -- Initialize the min and max limit
867 csd_mig_slabs_pkg.get_table_slabs
868 (p_table_name =>'CSD_REPAIR_JOB_XREF',
869 p_module =>'CSD',
870 p_slab_number => p_slab_number,
871 x_start_slab => l_min,
872 x_end_slab => l_max);
873
874 -- Check the min and max limit
875 IF l_min is null THEN
876 RETURN;
877 END IF;
878
879 -- Open the cursor and update the table
880 OPEN csd_repair_job_xref_cursor(l_min,l_max);
881 LOOP
882 FETCH csd_repair_job_xref_cursor bulk collect into
883 repair_job_xref_id_mig,
884 inventory_item_id_mig,
885 rowid_mig
886 LIMIT MAX_BUFFER_SIZE;
887
888 FOR j in 1..repair_job_xref_id_mig.count
889 LOOP
890
891 SAVEPOINT CSD_REPAIR_JOB_XREF;
892
893 BEGIN
894 UPDATE csd_repair_job_xref
895 SET inventory_item_id = inventory_item_id_mig(j),
896 last_update_date = sysdate,
897 last_update_login = fnd_global.login_id,
898 last_updated_by = fnd_global.user_id
899 WHERE rowid = rowid_mig(j);
900 EXCEPTION
901 WHEN OTHERS THEN
902 -- when errored rollback and insert the message into
903 -- the csd upgrade errors table
904 Rollback to CSD_REPAIR_JOB_XREF;
905 l_error_text := 'Repair job xref updation Error '||substr(sqlerrm,1,1000);
906
907 INSERT INTO csd_upg_errors
908 (orig_system_reference,
909 target_system_reference,
910 orig_system_reference_id,
911 upgrade_datetime,
912 error_message,
913 migration_phase)
914 VALUES
915 ('CSD_REPAIR_JOB_XREF',
916 'CSD_REPAIR_JOB_XREF',
917 repair_job_xref_id_mig(j),
918 sysdate,
919 l_error_text,
920 '11.5.8');
921
922 commit;
923
924 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_JOB_XREF table data. '|| l_error_text);
925
926
927 END;
928
929 END LOOP;
930
931 COMMIT;
932
933 EXIT WHEN csd_repair_job_xref_cursor%notfound;
934 END LOOP;
935
936 if csd_repair_job_xref_cursor%isopen then
937 close csd_repair_job_xref_cursor;
938 end if;
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 l_error_text := substr(sqlerrm,1,1000);
943 if csd_repair_job_xref_cursor%isopen then
944 close csd_repair_job_xref_cursor;
945 end if;
946 RAISE_APPLICATION_ERROR(-20000, 'Error while migrating CSD_REPAIR_JOB_XREF table data. '||l_error_text);
947 END CSD_REPAIR_JOB_XREF_MIG;
948
949
950 -- Migration procedure for CSD_REPAIR_TYPES_B
951 PROCEDURE CSD_REPAIR_TYPES_B_MIG IS
952
953 Type NumTabType is VARRAY(10000) of NUMBER;
954 repair_type_id_mig NumTabType;
955
956 Type RowidTabType is VARRAY(1000) of VARCHAR2(30);
957 rowid_mig RowidTabtype;
958
959 l_repair_type_ref CSD_REPAIR_TYPES_B.REPAIR_TYPE_REF%TYPE := 'SR';
960 l_repair_mode CSD_REPAIR_TYPES_B.REPAIR_MODE%TYPE := 'WIP';
961 l_seeded_flag CSD_REPAIR_TYPES_B.SEEDED_FLAG%TYPE := 'N';
962 l_auto_process_rma CSD_REPAIR_TYPES_B.AUTO_PROCESS_RMA%TYPE := 'N';
963 l_interface_to_om_flag CSD_REPAIR_TYPES_B.INTERFACE_TO_OM_FLAG%TYPE := 'N';
964 l_booK_sales_order_flag CSD_REPAIR_TYPES_B.BOOK_SALES_ORDER_FLAG%TYPE := 'N';
965 l_release_sales_order_flag CSD_REPAIR_TYPES_B.RELEASE_SALES_ORDER_FLAG%TYPE := 'N';
966 l_ship_sales_order_flag CSD_REPAIR_TYPES_B.SHIP_SALES_ORDER_FLAG%TYPE := 'N';
967
968 CURSOR csd_repair_types_b_cursor is
969 select crtb.repair_type_id,
970 crtb.rowid
971 from csd_repair_types_b crtb
972 where crtb.seeded_flag IS NULL;
973
974 l_min NUMBER;
975 l_max NUMBER;
976 l_error_text VARCHAR2(2000);
977
978 MAX_BUFFER_SIZE NUMBER := 500;
979
980 BEGIN
981
982 -- Open the cursor and update the table
983 OPEN csd_repair_types_b_cursor;
984 LOOP
985 FETCH csd_repair_types_b_cursor bulk collect into
986 repair_type_id_mig,
987 rowid_mig
988 LIMIT MAX_BUFFER_SIZE;
989
990 FOR j in 1..repair_type_id_mig.count
991 LOOP
992 BEGIN
993 SAVEPOINT CSD_REPAIR_TYPES_B;
994
995 UPDATE csd_repair_types_b
996 SET repair_mode = l_repair_mode,
997 repair_type_ref = l_repair_type_ref,
998 auto_process_rma = l_auto_process_rma,
999 interface_to_om_flag = l_interface_to_om_flag,
1000 book_sales_order_flag = l_book_sales_order_flag,
1001 release_sales_order_flag = l_release_sales_order_flag,
1002 seeded_flag = l_seeded_flag,
1003 last_update_date = sysdate,
1004 last_update_login = fnd_global.login_id,
1005 last_updated_by = fnd_global.user_id
1006 WHERE rowid = rowid_mig(j);
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 -- when errored rollback and insert the message into
1010 -- the csd upgrade errors table
1011 Rollback to CSD_REPAIR_TYPES_B;
1012 l_error_text := 'Repair Type Updation Error'||substr(sqlerrm,1,1000);
1013 INSERT INTO csd_upg_errors
1014 (orig_system_reference,
1015 target_system_reference,
1016 orig_system_reference_id,
1017 upgrade_datetime,
1018 error_message,
1019 migration_phase)
1020 VALUES
1021 ('CSD_REPAIR_TYPES_B',
1022 'CSD_REPAIR_TYPES_B',
1023 repair_type_id_mig(j),
1024 sysdate,
1025 l_error_text,
1026 '11.5.8');
1027
1028 commit;
1029
1030 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_TYPES_B table data. '|| l_error_text);
1031
1032
1033 END;
1034 END LOOP;
1035
1036 COMMIT;
1037
1038 EXIT WHEN csd_repair_types_b_cursor%notfound;
1039 END LOOP;
1040
1041 if csd_repair_types_b_cursor%isopen then
1042 close csd_repair_types_b_cursor;
1043 end if;
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 l_error_text := substr(sqlerrm,1,1000);
1048 if csd_repair_types_b_cursor%isopen then
1049 close csd_repair_types_b_cursor;
1050 end if;
1051 RAISE_APPLICATION_ERROR(-20000, 'Error while migrating CSD_REPAIR_TYPES_B table data. '||l_error_text);
1052 END CSD_REPAIR_TYPES_B_MIG;
1053
1054 END CSD_Migrate_From_115X_PKG;
1055