1 PACKAGE BODY CSD_Migrate_From_115X_PKG6
2 /* $Header: csdmig6b.pls 120.6 2005/09/23 12:27:37 sangigup noship $ */
3 AS
4
5 /*-------------------------------------------------------------------------------*/
6 /* procedure name: CSD_TASKS_MIG6 */
7 /* description : procedure for migrating Task data */
8 /* from 11.5.10 to R12 */
9 /* purpose : Create Repair Task record in CSD_TASKS */
10 /*-------------------------------------------------------------------------------*/
11
12 PROCEDURE csd_task_mig6(p_slab_number IN NUMBER DEFAULT 1)
13 IS
14
15 v_min NUMBER;
16 v_max NUMBER;
17 v_error_text VARCHAR2(2000);
18 MAX_BUFFER_SIZE NUMBER := 500;
19 l_repair_task_id NUMBER;
20 error_process EXCEPTION;
21
22 /*CURSOR get_repair_tasks(p_start_repair_line_id number, p_end_repair_line_id number)
23 IS
24 select jtf_tasks_vl.task_id, repair_line_id
25 from jtf_tasks_vl, csd_repairs
26 where jtf_tasks_vl.source_object_id = csd_repairs.repair_line_id
27 and source_object_type_code='DR'
28 and repair_line_id between p_start_repair_line_id
29 and p_end_repair_line_id
30 and not exists( select 'x' from csd_tasks
31 where csd_tasks.task_id = jtf_tasks_vl.task_id
32 and csd_tasks.repair_line_id = csd_repairs.repair_line_id);*/
33
34 BEGIN
35 -- Get the Slab Number for the table
36
37 BEGIN
38
39 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS',
40 'CSD',
41 p_slab_number,
42 v_min,
43 v_max);
44
45 IF v_min IS NULL
46 THEN
47 RETURN;
48 END IF;
49
50 END;
51
52 -- Migration code for creating Repair Tasks
53 insert into CSD_TASKS (
54 REPAIR_TASK_ID,
55 TASK_ID,
56 REPAIR_LINE_ID,
57 APPLICABLE_QA_PLANS,
58 OBJECT_VERSION_NUMBER,
59 CREATED_BY,
60 CREATION_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_DATE,
63 LAST_UPDATE_LOGIN
64 ) select
65 CSD.csd_tasks_s.nextval,
66 jtf_tasks_vl.task_id,
67 csd_repairs.repair_line_id,
68 'N',
69 1,
70 FND_GLOBAL.user_id,
71 SYSDATE,
72 FND_GLOBAL.user_id,
73 SYSDATE,
74 FND_GLOBAL.login_id
75 from jtf_tasks_vl,
76 CSD_REPAIRS
77 where jtf_tasks_vl.source_object_id = csd_repairs.repair_line_id
78 and source_object_type_code='DR'
79 and repair_line_id between v_min and v_max
80 and not exists( select 'x' from csd_tasks
81 where csd_tasks.task_id = jtf_tasks_vl.task_id
82 and csd_tasks.repair_line_id = csd_repairs.repair_line_id);
83
84
85 COMMIT;
86
87 END csd_task_mig6;
88
89
90 /*-----------------------------------------------------------------------------*/
91 /* procedure name: csd_flex_flow_mig6 */
92 /* description : Migration script for 12.0 Flex Flow specific changes. */
93 /*-----------------------------------------------------------------------------*/
94
95 PROCEDURE csd_flex_flow_mig6
96
97 IS
98
99 -- Definitions --
100 TYPE VARTAB IS TABLE OF VARCHAR2(1);
101 TYPE NUMTAB IS TABLE OF NUMBER;
102 TYPE REP_LINE_ID_ARRAY_TYPE IS VARRAY (1000)
103 OF CSD.CSD_REPAIRS.REPAIR_LINE_ID%TYPE;
104 TYPE ORIG_SOURCE_REF_ARRAY_TYPE IS VARRAY (1000)
105 OF CSD.CSD_REPAIRS.ORIGINAL_SOURCE_REFERENCE%TYPE;
106 TYPE FLOW_STATUS_ID_ARRAY_TYPE IS VARRAY (1000)
107 OF CSD.CSD_REPAIRS.FLOW_STATUS_ID%TYPE;
108 TYPE INVENTORY_ORG_ID_ARRAY_TYPE IS VARRAY (1000)
109 OF CSD.CSD_REPAIRS.INVENTORY_ORG_ID%TYPE;
110 TYPE INVENTORY_ITEM_ID_ARRAY_TYPE IS VARRAY (1000)
111 OF CSD.CSD_REPAIRS.INVENTORY_ITEM_ID%TYPE;
112
113 -- Variables --
114 repair_line_id_arr REP_LINE_ID_ARRAY_TYPE;
115 original_source_reference_arr ORIG_SOURCE_REF_ARRAY_TYPE;
116 flow_status_id_arr FLOW_STATUS_ID_ARRAY_TYPE;
117 inventory_org_id_arr INVENTORY_ORG_ID_ARRAY_TYPE;
118 inventory_item_id_arr INVENTORY_ITEM_ID_ARRAY_TYPE;
119 l_inv_org_id NUMBER;
120
121 -- Do not mess with the order of values below.
122 -- It is crucial for the logic further below.
123 FlowStatusCodes VARTAB := VARTAB('C', 'H', 'O', 'D');
124 FlowStatusIDs NUMTAB := NUMTAB(-1, -1, -1, -1);
125
126 -- Constants --
127 c_closed_index CONSTANT NUMBER := 1;
128 c_hold_index CONSTANT NUMBER := 2;
129 c_open_index CONSTANT NUMBER := 3;
130 c_draft_index CONSTANT NUMBER := 4;
131 MAX_BUFFER_SIZE NUMBER := 1000;
132
133 -- EXCEPTIONS --
134 UNIQUE_CONSTRAINT_VIOLATED Exception;
135
136 -- This will trap all exceptions that have
137 -- SQLCODE = -00001 and name it as 'UNIQUE_CONSTRAINT_VIOLATED'.
138 PRAGMA EXCEPTION_INIT( UNIQUE_CONSTRAINT_VIOLATED, -00001 );
139
140
141 -- Cursor to derive the eligible repair orders
142 Cursor c_get_upd_ro_cursor is
143 Select
144 repair_line_id,
145 original_source_reference,
146 flow_status_id,
147 inventory_org_id,
148 inventory_item_id
149 from csd_repairs
150 where flow_status_id is null
151 or inventory_org_id is null;
152
153 -- Cursor to get om rma org id
154 CURSOR c_get_om_rma_org_id (p_repair_line_id in number) IS
155 SELECT oel.ship_from_org_id
156 FROM oe_order_lines_all oel,
157 csd_repairs cr
158 WHERE
159 cr.repair_line_id = p_repair_line_id
160 AND cr.original_source_header_id = oel.header_id
161 AND cr.original_source_line_id = oel.line_id;
162
163 -- Cursor to get ro rma org id
164 CURSOR c_get_ro_rma_org_id (p_repair_line_id in number) IS
165 SELECT oel.ship_from_org_Id
166 FROM oe_order_lines_all oel,
167 csd_product_transactions cp
168 WHERE cp.repair_line_id = p_repair_line_Id
169 AND cp.action_type in ('RMA','MOVE_IN')
170 AND cp.order_line_id = oel.line_id
171 AND cp.order_header_id = oel.header_id;
172
173 -- Cursor to get item org id
174 CURSOR c_get_item_org_id (p_inventory_item_id in number) IS
175 SELECT organization_id
176 FROM mtl_system_items_kfv
177 WHERE inventory_item_id = p_inventory_item_id;
178
179 BEGIN
180
181 -- STEP 1: Insert records to populate the flow statuses definitions
182
183 FOR i IN FlowStatusCodes.FIRST..FlowStatusCodes.LAST LOOP
184
185 BEGIN
186 insert into CSD_FLOW_STATUSES_B (
187 FLOW_STATUS_ID,
188 FLOW_STATUS_CODE,
189 STATUS_CODE,
190 SEEDED_FLAG,
191 OBJECT_VERSION_NUMBER,
192 CREATION_DATE,
193 CREATED_BY,
194 LAST_UPDATE_DATE,
195 LAST_UPDATED_BY,
196 LAST_UPDATE_LOGIN
197 ) values (
198 CSD_FLOW_STATUSES_S1.nextval,
199 FlowStatusCodes(i),
200 FlowStatusCodes(i), -- literal value is same
201 'Y',
202 1,
203 SYSDATE,
204 FND_GLOBAL.USER_ID,
205 SYSDATE,
206 FND_GLOBAL.USER_ID,
207 FND_GLOBAL.LOGIN_ID
208 );
209
210 EXCEPTION
211 WHEN UNIQUE_CONSTRAINT_VIOLATED THEN
212 -- Do nothing, as the record already exists
213 NULL;
214 END;
215
216 END LOOP;
217
218 insert into CSD_FLOW_STATUSES_TL (
219 FLOW_STATUS_ID,
220 EXTERNAL_DISPLAY_STATUS,
221 CREATED_BY,
222 CREATION_DATE,
223 LAST_UPDATED_BY,
224 LAST_UPDATE_DATE,
225 LAST_UPDATE_LOGIN,
226 LANGUAGE,
227 SOURCE_LANG
228 ) select
229 FS_B.flow_status_id,
230 NULL, -- EXTERNAL_DISPLAY_STATUS
231 FND_GLOBAL.user_id,
232 SYSDATE,
233 FND_GLOBAL.user_id,
234 SYSDATE,
235 FND_GLOBAL.login_id,
236 L.LANGUAGE_CODE,
237 L.LANGUAGE_CODE
238 from FND_LANGUAGES L,
239 CSD_FLOW_STATUSES_B FS_B
240 where L.INSTALLED_FLAG in ('I', 'B')
241 AND FS_B.flow_status_code in ('C','H','O','D')
242 and not exists
243 (select 'x'
244 from CSD_FLOW_STATUSES_TL T
245 where T.FLOW_STATUS_ID = FS_B.flow_status_id
246 and T.LANGUAGE = L.LANGUAGE_CODE);
247
248 -- STEP 2: Insert into the status transitions for each Repair Type.
249
250 insert into CSD_FLWSTS_TRANS_B (
251 REPAIR_TYPE_ID,
252 FROM_FLOW_STATUS_ID,
253 TO_FLOW_STATUS_ID,
254 WF_ITEM_TYPE,
255 WF_PROCESS_NAME,
256 REASON_REQUIRED_FLAG,
257 CAPTURE_ACTIVITY_FLAG,
258 ALLOW_ALL_RESP_FLAG,
259 OBJECT_VERSION_NUMBER,
260 FLWSTS_TRAN_ID,
261 CREATION_DATE,
262 CREATED_BY,
263 LAST_UPDATE_DATE,
264 LAST_UPDATED_BY,
265 LAST_UPDATE_LOGIN
266 )
267 SELECT RT.repair_type_id,
268 FS_B1.flow_status_id,
269 FS_B2.flow_status_id,
270 NULL, -- P_WF_ITEM_TYPE
271 NULL, -- P_WF_PROCESS_NAME
272 'N', -- P_REASON_REQUIRED_FLAG,
273 'Y', -- P_CAPTURE_ACTIVITY_FLAG,
274 'Y', -- P_ALLOW_ALL_RESP_FLAG,
275 1,
276 CSD_FLWSTS_TRANS_S1.nextval,
277 SYSDATE,
278 FND_GLOBAL.user_id,
279 SYSDATE,
280 FND_GLOBAL.user_id,
281 FND_GLOBAL.login_id
282 FROM CSD_REPAIR_TYPES_B RT,
283 CSD_FLOW_STATUSES_B FS_B1,
284 CSD_FLOW_STATUSES_B FS_B2
285 WHERE FS_B1.flow_status_code IN ('C','H','O')
286 AND FS_B2.flow_status_code IN ('C','H','O')
287 AND FS_B2.flow_status_code <> FS_B1.flow_status_code
288 AND NOT EXISTS
289 ( SELECT 'x'
290 FROM CSD_FLWSTS_TRANS_B FLWSTS_B
291 WHERE FLWSTS_B.repair_type_id = RT.repair_type_id
292 AND FLWSTS_B.from_flow_status_id = FS_B1.flow_status_id
293 AND FLWSTS_B.to_flow_status_id = FS_B2.flow_status_id
294 );
295
296 -- Populate TL table now.
297
298 insert into CSD_FLWSTS_TRANS_TL (
299 FLWSTS_TRAN_ID,
300 DESCRIPTION,
301 CREATED_BY,
302 CREATION_DATE,
303 LAST_UPDATED_BY,
304 LAST_UPDATE_DATE,
305 LAST_UPDATE_LOGIN,
306 LANGUAGE,
307 SOURCE_LANG
308 ) select
309 FLWSTS_B.flwsts_tran_id,
310 NULL, -- P_DESCRIPTION,
311 FND_GLOBAL.user_id,
312 SYSDATE,
313 FND_GLOBAL.user_id,
314 SYSDATE,
315 FND_GLOBAL.login_id,
316 L.LANGUAGE_CODE,
317 L.LANGUAGE_CODE
318 from FND_LANGUAGES L,
319 CSD_FLWSTS_TRANS_B FLWSTS_B
320 where L.INSTALLED_FLAG in ('I', 'B')
321 and not exists
322 (select 'x'
323 from CSD_FLWSTS_TRANS_TL T
324 where T.FLWSTS_TRAN_ID = FLWSTS_B.flwsts_tran_id
325 and T.LANGUAGE = L.LANGUAGE_CODE);
326
327 -- STEP 3: Get all the flow status Ids for use in next steps.
328
329 FOR i IN FlowStatusCodes.FIRST..FlowStatusCodes.LAST LOOP
330 SELECT flow_status_id
331 INTO FlowStatusIDs(i)
332 FROM CSD_FLOW_STATUSES_B
333 WHERE flow_status_code = FlowStatusCodes(i);
334 END LOOP;
335
336 -- STEP 4: Update all repair types that do not have a value.
337
338 UPDATE CSD_REPAIR_TYPES_B
339 SET start_flow_Status_id = FlowStatusIDs(c_open_index),
340 last_update_date = SYSDATE,
341 last_updated_by = FND_GLOBAL.user_id,
342 last_update_login = FND_GLOBAL.login_id
343 WHERE start_flow_Status_id IS NULL;
344
345 -- STEP 5: Update all repair orders that do not have a value.
346 -- Update Inventory_Org_id and Flow_status_id
347
348 -- Repair Inventory Org id derivation
349 -- a.If the Repair Order is created by OM relinking process then
350 -- the Org Id is derived from the original_source_line_id
351 -- of the RO Line.
352 -- b.If the RMA/IO is created from Depot, then the Org Id
353 -- is derived from the Order line of the corresponding Product
354 -- Transaction lines ( RMA/MOVE_IN line)
355 -- c.If there are no RMA/MOVE_IN lines then the Organization id of
356 -- Item is defaulted as Repair Inv Org id
357
358 OPEN c_get_upd_ro_cursor;
359
360 LOOP
361 FETCH c_get_upd_ro_cursor BULK COLLECT INTO repair_line_id_arr,
362 original_source_reference_arr,flow_status_id_arr,inventory_org_id_arr,
363 inventory_item_id_arr LIMIT MAX_BUFFER_SIZE;
364
365 FOR i IN 1..repair_line_id_arr.COUNT
366 LOOP
367
368 l_inv_org_id := null;
369
370 IF ( original_source_reference_arr(i) = 'RMA' ) THEN
371
372 OPEN c_get_om_rma_org_id (repair_line_id_arr(i));
373 FETCH c_get_om_rma_org_id into l_inv_org_id;
374 CLOSE c_get_om_rma_org_id;
375
376 ELSE
377
378 OPEN c_get_ro_rma_org_id (repair_line_id_arr(i));
379 FETCH c_get_ro_rma_org_id into l_inv_org_id;
380 CLOSE c_get_ro_rma_org_id;
381
382 END IF;
383
384 IF ( l_inv_org_id is null ) THEN
385
386 OPEN c_get_item_org_id (repair_line_id_arr(i));
387 FETCH c_get_item_org_id into l_inv_org_id;
388 CLOSE c_get_item_org_id;
389
390 END IF;
391
392 UPDATE CSD_REPAIRS
393 SET flow_Status_id = decode(status,
394 'O', FlowStatusIDs(c_open_index),
395 'C', FlowStatusIDs(c_closed_index),
396 'H', FlowStatusIDs(c_hold_index),
397 'D', FlowStatusIDs(c_draft_index)
398 ),
399 inventory_org_id = l_inv_org_id,
400 last_update_date = SYSDATE,
404
401 last_updated_by = FND_GLOBAL.user_id,
402 last_update_login = FND_GLOBAL.login_id
403 WHERE repair_line_id = repair_line_id_arr(i);
405 END LOOP;
406
407 COMMIT;
408
409 EXIT WHEN c_get_upd_ro_cursor%NOTFOUND;
410
411 END LOOP;
412
413 IF c_get_upd_ro_cursor%ISOPEN THEN
414 CLOSE c_get_upd_ro_cursor;
415 END IF;
416
417 COMMIT;
418
419 END csd_flex_flow_mig6;
420
421
422 /*-----------------------------------------------------------------------------*/
423 /* procedure name: csd_ro_diagnostic_codes_mig6 */
424 /* description : Migration script for 12.0 Diagnostic Code specific changes. */
425 /*-----------------------------------------------------------------------------*/
426
427 PROCEDURE csd_ro_diagnostic_codes_mig6
428
429 IS
430
431 -- Definitions --
432 TYPE REP_LINE_ID_ARRAY_TYPE IS VARRAY (1000)
433 OF CSD.CSD_RO_DIAGNOSTIC_CODES.REPAIR_LINE_ID%TYPE;
434 TYPE INV_ITEM_ID_ARRAY_TYPE IS VARRAY(1000)
435 OF CSD.CSD_REPAIRS.inventory_item_id%TYPE;
436
437 -- Variables --
438 rep_line_id_arr REP_LINE_ID_ARRAY_TYPE;
439 inv_item_id_arr INV_ITEM_ID_ARRAY_TYPE;
440 v_error_text VARCHAR2(2000);
441
442 -- Constants --
443 MAX_BUFFER_SIZE NUMBER := 500;
444
445 -- Exceptions --
446 error_process EXCEPTION;
447
448 -- Cursors --
449 -- cursor to get the repair order items for ro diagnostic codes
450 -- that don't have diagnostic item ids.
451 CURSOR get_rodc_repair_item_cursor
452 IS
453 SELECT DISTINCT
454 dc.repair_line_id, rep.inventory_item_id
455 FROM
456 CSD_RO_DIAGNOSTIC_CODES dc,
457 CSD_REPAIRS rep
458 WHERE dc.diagnostic_item_id IS NULL
459 AND rep.repair_line_id = dc.repair_line_id;
460
461 BEGIN
462
463
464 -- Update all RO diagnostic codes that do not have a diagnostic item.
465 -- Default diagnostic item will be the repair order item
466 OPEN get_rodc_repair_item_cursor;
467 LOOP -- sangigup
468 FETCH get_rodc_repair_item_cursor
469 BULK COLLECT INTO rep_line_id_arr, inv_item_id_arr LIMIT MAX_BUFFER_SIZE;
470
471 FOR i IN 1..rep_line_id_arr.COUNT
472 LOOP
473
474
475 UPDATE
476 CSD_RO_DIAGNOSTIC_CODES
477 SET
478 diagnostic_item_id = inv_item_id_arr(i)
479 WHERE repair_line_id = rep_line_id_arr(i);
480
481 END LOOP;
482
483 COMMIT;
484
485 EXIT WHEN get_rodc_repair_item_cursor%NOTFOUND;
486 END LOOP;
487
488 IF get_rodc_repair_item_cursor%ISOPEN
489 THEN
490 CLOSE get_rodc_repair_item_cursor;
491 END IF;
492
493 COMMIT;
494
495 END csd_ro_diagnostic_codes_mig6;
496
497
498
499
500 /*-----------------------------------------------------------------------------*/
501 /* procedure name: csd_ro_service_codes_mig6 */
502 /* description : Migration script for 12.0 Diagnostic Code specific changes. */
503 /*-----------------------------------------------------------------------------*/
504
505 PROCEDURE csd_ro_service_codes_mig6
506
507 IS
508
509 -- Definitions --
510 TYPE REP_LINE_ID_ARRAY_TYPE IS VARRAY (1000)
511 OF CSD.CSD_RO_SERVICE_CODES.REPAIR_LINE_ID%TYPE;
512 TYPE INV_ITEM_ID_ARRAY_TYPE IS VARRAY(1000)
513 OF CSD.CSD_REPAIRS.INVENTORY_ITEM_ID%TYPE;
514
515 -- Variables --
516 rep_line_id_arr REP_LINE_ID_ARRAY_TYPE;
517 inv_item_id_arr INV_ITEM_ID_ARRAY_TYPE;
518 v_error_text VARCHAR2(2000);
519
520 -- Constants --
521 MAX_BUFFER_SIZE NUMBER := 500;
522
523 -- Exceptions --
524 error_process EXCEPTION;
525
526 -- Cursors --
527 -- cursor to get the repair order items for ro service codes
528 -- that don't have service item ids.
529 CURSOR get_rosc_repair_item_cursor
530 IS
531 SELECT DISTINCT
532 sc.repair_line_id, rep.inventory_item_id
533 FROM
534 CSD_RO_SERVICE_CODES sc,
535 CSD_REPAIRS rep
536 WHERE sc.service_item_id IS NULL
537 AND rep.repair_line_id = sc.repair_line_id;
538
539 BEGIN
540
541 -- Update all RO service codes that do not have a service item.
542 -- Default service item will be the repair order item
543 OPEN get_rosc_repair_item_cursor;
544 LOOP
545 FETCH get_rosc_repair_item_cursor
546 BULK COLLECT INTO rep_line_id_arr, inv_item_id_arr LIMIT MAX_BUFFER_SIZE;
547
548 FOR i IN 1..rep_line_id_arr.COUNT
549 LOOP
550
551 UPDATE
552 CSD_RO_SERVICE_CODES
553 SET
554 service_item_id = inv_item_id_arr(i)
555 WHERE repair_line_id = rep_line_id_arr(i);
556 -- AND service_item_id IS NULL;
557
558 END LOOP;
559
560 COMMIT;
561
562 EXIT WHEN get_rosc_repair_item_cursor%NOTFOUND;
563 END LOOP;
564
565 IF get_rosc_repair_item_cursor%ISOPEN
566 THEN
567 CLOSE get_rosc_repair_item_cursor;
568 END IF;
569 COMMIT;
570 END csd_ro_service_codes_mig6;
571
572 END CSD_Migrate_From_115X_PKG6;
[Home] [Help]