[Home] [Help]
PACKAGE BODY: APPS.CSD_MIGRATE_FROM_115X_PKG3
Source
1 PACKAGE BODY CSD_Migrate_From_115X_PKG3
2 /* $Header: csdmig3b.pls 120.4 2008/02/15 04:03:09 takwong ship $ */
3 AS
4
5 /*-------------------------------------------------------------------------------*/
6 /* procedure name: CSD_REPAIR_ACT_HDR_MIG3 */
7 /* description : procedure for migrating ACTUALS Headers data */
8 /* from 11.5.9 to 11.5.10 */
9 /* purpose : Create Repair Actual header record in CSD_REPAIR_ACTUALS */
10 /*-------------------------------------------------------------------------------*/
11
12 PROCEDURE csd_repair_act_hdr_mig3(p_slab_number IN NUMBER DEFAULT 1)
13 IS
14
15 TYPE ACT_HDR_REC_ARRAY_TYPE IS VARRAY(1000) OF NUMBER;
16 act_hdr_arr ACT_HDR_REC_ARRAY_TYPE;
17 v_min NUMBER;
18 v_max NUMBER;
19 v_error_text VARCHAR2(2000);
20 MAX_BUFFER_SIZE NUMBER := 500;
21 l_repair_actual_id NUMBER;
22 error_process EXCEPTION;
23
24 CURSOR get_act_hdr(p_start_rep_line_id number, p_end_rep_line_id number)
25 IS
26 -- gilam: changed the EXISTS query part so that the lines are not getting from
27 -- csd_repair_estimate_lines_v since the view only has lines of
28 -- charge line type = ESTIMATE, but we want lines that have type ACTUAL
29 SELECT distinct cr.repair_line_id repair_line_id
30 FROM csd_repairs cr
31 , csd_repair_estimate cre
32 WHERE cr.repair_line_id = cre.repair_line_id
33 AND EXISTS ( SELECT 'x'
34 FROM csd_repair_estimate_lines crel, cs_estimate_details ced
35 WHERE cre.repair_estimate_id = crel.repair_estimate_id
36 AND crel.estimate_detail_id = ced.estimate_detail_id
37 AND ced.order_line_id is not null)
38 AND NOT EXISTS ( SELECT 'x'
39 FROM csd_repair_actuals cra
40 WHERE cr.repair_line_id = cra.repair_line_id)
41 AND cr.repair_line_id >= p_start_rep_line_id
42 AND cr.repair_line_id <= p_end_rep_line_id;
43
44 BEGIN
45 -- Get the Slab Number for the table
46
47 BEGIN
48
49 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS',
50 'CSD',
51 p_slab_number,
52 v_min,
53 v_max);
54
55 IF v_min IS NULL
56 THEN
57 RETURN;
58 END IF;
59
60 END;
61
62 -- Migration code for creating Actual Header
63 OPEN get_act_hdr(v_min, v_max);
64
65 LOOP
66 FETCH get_act_hdr BULK COLLECT INTO act_hdr_arr LIMIT MAX_BUFFER_SIZE;
67 FOR j IN 1..act_hdr_arr.COUNT
68 LOOP
69 SAVEPOINT CSD_ACTUAL_HEADER;
70
71 BEGIN
72 -- gilam: clearing out repair actual id for creaing new header
73 l_repair_actual_id := null;
74
75 APPS.CSD_REPAIR_ACTUALS_PKG.INSERT_ROW( px_REPAIR_ACTUAL_ID => l_repair_actual_id
76 ,p_OBJECT_VERSION_NUMBER => 1
77 ,p_REPAIR_LINE_ID => act_hdr_arr(j)
78 ,P_CREATED_BY => fnd_global.user_id
79 ,P_CREATION_DATE => sysdate
80 ,P_LAST_UPDATED_BY => fnd_global.user_id
81 ,P_LAST_UPDATE_DATE => sysdate
82 ,P_LAST_UPDATE_LOGIN => fnd_global.login_id
83 ,p_ATTRIBUTE_CATEGORY => null
84 ,p_ATTRIBUTE1 => null
85 ,p_ATTRIBUTE2 => null
86 ,p_ATTRIBUTE3 => null
87 ,p_ATTRIBUTE4 => null
88 ,p_ATTRIBUTE5 => null
89 ,p_ATTRIBUTE6 => null
90 ,p_ATTRIBUTE7 => null
91 ,p_ATTRIBUTE8 => null
92 ,p_ATTRIBUTE9 => null
93 ,p_ATTRIBUTE10 => null
94 ,p_ATTRIBUTE11 => null
95 ,p_ATTRIBUTE12 => null
96 ,p_ATTRIBUTE13 => null
97 ,p_ATTRIBUTE14 => null
98 ,p_ATTRIBUTE15 => null);
99
100
101
102 IF SQL%NOTFOUND
103 THEN
104 RAISE error_process;
105 END IF;
106
107 EXCEPTION
108 WHEN error_process THEN
109 ROLLBACK TO CSD_ACTUAL_HEADER;
110 v_error_text := substr(sqlerrm, 1, 1000)
111 || 'Actual Repair Line Id:'
112 || act_hdr_arr(j);
113
114 INSERT INTO CSD_UPG_ERRORS
115 (ORIG_SYSTEM_REFERENCE,
116 TARGET_SYSTEM_REFERENCE,
117 ORIG_SYSTEM_REFERENCE_ID,
118 UPGRADE_DATETIME,
119 ERROR_MESSAGE,
120 MIGRATION_PHASE)
121 VALUES ('CSD_REPAIR_ACTUALS',
122 'CSD_REPAIR_ACTUALS',
123 act_hdr_arr(j),
124 sysdate,
125 v_error_text,
126 '11.5.10');
127
128 commit;
129
130 raise_application_error( -20000, 'Error while migrating ACTUALS Headers data: Error while inserting into CSD_REPAIR_ACTUALS. '|| v_error_text);
131
132 END;
133 END LOOP;
134 COMMIT;
135 EXIT WHEN get_act_hdr%NOTFOUND;
136 END LOOP;
137
138 IF get_act_hdr%ISOPEN
139 THEN
140 CLOSE get_act_hdr;
141 END IF;
142 COMMIT;
143 END csd_repair_act_hdr_mig3;
144
145
146 /*-------------------------------------------------------------------------------*/
147 /* procedure name: CSD_CHARGE_ESTIMATE_LINES_MIG3 */
148 /* description : procedure for migrating ESTIMATES data in CS_ESTIMATE_DETAILS */
149 /* table from 11.5.9 to 11.5.10 */
150 /* purpose : Step 1 Data Migration document for Actuals */
151 /* Update all the 1159 not interfaced to OM charge lines in */
152 /* cs_estimate_details table to charge_line_type = ESTIMATE */
153 /* from charge_line_type = ACTUAL */
154 /*-------------------------------------------------------------------------------*/
155
156 PROCEDURE csd_charge_estimate_lines_mig3(p_slab_number IN NUMBER DEFAULT 1)
157 IS
158
159 TYPE EST_LINES_REC_ARRAY_TYPE IS VARRAY(1000) OF NUMBER;
160 est_lines_arr EST_LINES_REC_ARRAY_TYPE;
161 v_min NUMBER;
162 v_max NUMBER;
163 v_error_text VARCHAR2(2000);
164 MAX_BUFFER_SIZE NUMBER := 500;
165 error_process EXCEPTION;
166
167 -- gilam: bug 3410383 - changed query to use repair line id instead
168 /*
169 CURSOR get_est_lines(p_start_est_det_id number, p_end_est_det_id number)
170 IS
171 SELECT ced.estimate_detail_id
172 FROM cs_estimate_details ced
173 , csd_repair_estimate_lines cr
174 WHERE cr.estimate_detail_id = ced.estimate_detail_id
175 AND ced.charge_line_type = 'ACTUAL'
176 AND ced.order_line_id is null
177 AND ced.original_source_code = 'DR'
178 AND ced.source_code = 'DR'
179 AND NOT EXISTS ( SELECT 'x'
180 FROM csd_repair_actual_lines cral
181 WHERE cral.estimate_detail_id = cr.estimate_detail_id)
182 AND ced.estimate_detail_id >= p_start_est_det_id
183 AND ced.estimate_detail_id <= p_end_est_det_id;
184 */
185
186 CURSOR get_est_lines(p_start_rep_line_id number, p_end_rep_line_id number)
187 IS
188 SELECT ced.estimate_detail_id
189 FROM cs_estimate_details ced
190 , csd_repairs cr
191 , csd_repair_estimate cre
192 , csd_repair_estimate_lines crel
193 WHERE cr.repair_line_id = cre.repair_line_id
194 AND cre.repair_estimate_id = crel.repair_estimate_id
195 AND crel.estimate_detail_id = ced.estimate_detail_id
196 AND ced.charge_line_type = 'ACTUAL'
197 AND ced.order_line_id is null
198 AND ced.original_source_code = 'DR'
199 AND ced.source_code = 'DR'
200 AND NOT EXISTS ( SELECT 'x'
201 FROM csd_repair_actual_lines cral
202 WHERE cral.estimate_detail_id = crel.estimate_detail_id)
203 AND cr.repair_line_id >= p_start_rep_line_id
204 AND cr.repair_line_id <= p_end_rep_line_id;
205 -- gilam: end bug fix 3410303 - changed query
206
207 BEGIN
208
209 -- Get the Slab Number for the table
210
211 BEGIN
212
213 -- gilam: bug 3410383 - changed slab table to csd_repairs
214 /*
215 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CS_ESTIMATE_DETAILS',
216 'CSD',
217 p_slab_number,
218 v_min,
219 v_max);
220 */
221
222 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS',
223 'CSD',
224 p_slab_number,
225 v_min,
226 v_max);
227 -- gilam: end bug fix 3410383
228
229 IF v_min IS NULL
230 THEN
231 RETURN;
232 END IF;
233
234 END;
235
236 -- Migration code for Repair Estimate Lines
237
238 OPEN get_est_lines(v_min, v_max);
239
240 LOOP
241 FETCH get_est_lines BULK COLLECT INTO est_lines_arr LIMIT MAX_BUFFER_SIZE;
242 FOR j IN 1..est_lines_arr.COUNT
243 LOOP
244 SAVEPOINT CSD_ESTIMATE_LINES;
245
246 BEGIN
247
248 UPDATE CS_ESTIMATE_DETAILS
249 SET CHARGE_LINE_TYPE = 'ESTIMATE'
250 WHERE ESTIMATE_DETAIL_ID = est_lines_arr(j);
251
252 IF SQL%NOTFOUND
253 THEN
254 RAISE error_process;
255 END IF;
256
257 EXCEPTION
258 WHEN error_process THEN
259 ROLLBACK TO CSD_ESTIMATE_LINES;
260 v_error_text := substr(sqlerrm, 1, 1000)
261 || 'Estimate Detail Id:'
262 || est_lines_arr(j);
263
264 INSERT INTO CSD_UPG_ERRORS
265 (ORIG_SYSTEM_REFERENCE,
266 TARGET_SYSTEM_REFERENCE,
267 ORIG_SYSTEM_REFERENCE_ID,
268 UPGRADE_DATETIME,
269 ERROR_MESSAGE,
270 MIGRATION_PHASE)
271 VALUES ('CSD_REPAIR_ESTIMATE_LINES',
272 'CS_ESTIMATE_DETAILS',
273 est_lines_arr(j),
274 sysdate,
275 v_error_text,
276 '11.5.10');
277
278 commit;
279
280 raise_application_error( -20000, 'Error while migrating ESTIMATES data in CS_ESTIMATE_DETAILS: Error while Updating CS_ESTIMATE_DETAILS. '|| v_error_text);
281
282 END;
283 END LOOP;
284 COMMIT;
285 EXIT WHEN get_est_lines%NOTFOUND;
286 END LOOP;
287
288 IF get_est_lines%ISOPEN
289 THEN
290 CLOSE get_est_lines;
291 END IF;
292 COMMIT;
293 END csd_charge_estimate_lines_mig3;
294
295 /*-------------------------------------------------------------------------------*/
296 /* procedure name: CSD_REPAIR_ESTIMATE_LINES_MIG3 */
297 /* description : procedure for migrating ESTIMATES data in CS_ESTIMATE_DETAILS */
298 /* table from 11.5.9 to 11.5.10 */
299 /* purpose : Mandatory Step for all CSD_REPAIR_ESTIMATE_LINES records */
300 /* Update all records in 1159 CSD_REPAIR_ESTIMATE_LINES table for*/
301 /* New Cols Added : EST_LINE_SOURCE_TYPE_CODE = 'MANUAL' */
302 /* , EST_LINE_SOURCE_ID1 = NULL */
303 /* , EST_LINE_SOURCE_ID2 = NULL */
304 /* , RO_SERVICE_CODE_ID = NULL */
305 /* */
306 /*-------------------------------------------------------------------------------*/
307
308 PROCEDURE csd_repair_estimate_lines_mig3(p_slab_number IN NUMBER DEFAULT 1)
309 IS
310
311 TYPE REP_EST_LINES_REC_ARRAY_TYPE IS VARRAY(1000) OF NUMBER;
312 rep_est_lines_arr REP_EST_LINES_REC_ARRAY_TYPE;
313 v_min NUMBER;
314 v_max NUMBER;
315 v_error_text VARCHAR2(2000);
316 MAX_BUFFER_SIZE NUMBER := 500;
317 error_process EXCEPTION;
318
319 -- gilam: bug 3410383 - changed query to use repair line id instead
320 /*
321 CURSOR get_rep_est_lines(p_start_rep_est_lin_id number, p_end_rep_est_lin_id number)
322 IS
323 SELECT cr.repair_estimate_line_id
324 FROM csd_repair_estimate_lines cr
325 WHERE cr.est_line_source_type_code is null
326 AND cr.repair_estimate_line_id >= p_start_rep_est_lin_id
327 AND cr.repair_estimate_line_id <= p_end_rep_est_lin_id;
328 */
329
330 CURSOR get_rep_est_lines(p_start_rep_line_id number, p_end_rep_line_id number)
331 IS
332 SELECT crel.repair_estimate_line_id
333 FROM csd_repairs cr
334 , csd_repair_estimate cre
335 , csd_repair_estimate_lines crel
336 WHERE cr.repair_line_id = cre.repair_line_id
337 AND cre.repair_estimate_id = crel.repair_estimate_id
338 AND crel.est_line_source_type_code is null
339 AND cr.repair_line_id >= p_start_rep_line_id
340 AND cr.repair_line_id <= p_end_rep_line_id;
341 -- gilam: end bug fix 3410303 - changed query
342
343 BEGIN
344
345 -- Get the Slab Number for the table
346
347 BEGIN
348
349 -- gilam: bug 3410383 - changed slab table to csd_repairs
350 /*
351 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIR_ESTIMATE_LINES',
352 'CSD',
353 p_slab_number,
354 v_min,
355 v_max);
356 */
357
358 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS',
359 'CSD',
360 p_slab_number,
361 v_min,
362 v_max);
363 -- gilam: end bug fix 3410383
364
365 IF v_min IS NULL
366 THEN
367 RETURN;
368 END IF;
369
370 END;
371
372 -- Migration code for Repair Estimate Lines
373
374 OPEN get_rep_est_lines(v_min, v_max);
375
376 LOOP
377 FETCH get_rep_est_lines BULK COLLECT INTO rep_est_lines_arr LIMIT MAX_BUFFER_SIZE;
378 FOR j IN 1..rep_est_lines_arr.COUNT
379 LOOP
380 SAVEPOINT CSD_ESTIMATE_LINES;
381
382 BEGIN
383
384 UPDATE CSD_REPAIR_ESTIMATE_LINES
385 SET EST_LINE_SOURCE_TYPE_CODE = 'MANUAL'
386 , EST_LINE_SOURCE_ID1 = NULL
387 , EST_LINE_SOURCE_ID2 = NULL
388 , RO_SERVICE_CODE_ID = NULL
389 WHERE REPAIR_ESTIMATE_LINE_ID = rep_est_lines_arr(j);
390
391 IF SQL%NOTFOUND
392 THEN
393 RAISE error_process;
394 END IF;
395
396 EXCEPTION
397 WHEN error_process THEN
398 ROLLBACK TO CSD_ESTIMATE_LINES;
399 v_error_text := substr(sqlerrm, 1, 1000)
400 || 'Repair Estimate Line Id:'
401 || rep_est_lines_arr(j);
402
403 INSERT INTO CSD_UPG_ERRORS
404 (ORIG_SYSTEM_REFERENCE,
405 TARGET_SYSTEM_REFERENCE,
406 ORIG_SYSTEM_REFERENCE_ID,
407 UPGRADE_DATETIME,
408 ERROR_MESSAGE,
409 MIGRATION_PHASE)
410 VALUES ('CSD_REPAIR_ESTIMATE_LINES',
411 'CSD_REPAIR_ESTIMATE_LINES',
412 rep_est_lines_arr(j),
413 sysdate,
414 v_error_text,
415 '11.5.10');
416 commit;
417
418 raise_application_error( -20000, 'Error while migrating ESTIMATES data in CS_ESTIMATE_DETAILS: Error while Updating CSD_REPAIR_ESTIMATE_LINES. '|| v_error_text);
419
420 END;
421 END LOOP;
422 COMMIT;
423 EXIT WHEN get_rep_est_lines%NOTFOUND;
424 END LOOP;
425
426 IF get_rep_est_lines%ISOPEN
427 THEN
428 CLOSE get_rep_est_lines;
429 END IF;
430 COMMIT;
431 END csd_repair_estimate_lines_mig3;
432
433 /*-------------------------------------------------------------------------------*/
434 /* procedure name: CSD_ACTTOEST_CHARGE_LINE_MIG3 */
435 /* description : procedure for copying the ACTUAL CHARGE LINE TO ESTIMATE */
436 /* CHARGE LINE and linking the Actual charge line to Depot Actuals*/
437 /* and Estimate charge line to Depot Estimate line */
438 /* for the CSD_REPAIR_ESTIMATE_LINES table data */
439 /* during migration from 11.5.9 to 11.5.10 */
440 /* Update all records in 1159 CSD_REPAIR_ESTIMATE_LINES table for*/
441 /* purpose : Step 2A and 2B of 11510 Actuals Data Migration Steps */
442 /* creates new Estimate charge line */
443 /* 1. linking the new Estimate charge line to Depot Estimate line*/
444 /* 2. create a new Depot Actual line and */
445 /* 3. link the Actual line created to old Estimate charge line */
446 /* */
447 /*-------------------------------------------------------------------------------*/
448
449 PROCEDURE csd_acttoest_charge_line_mig3(p_slab_number IN NUMBER DEFAULT 1)
450 IS
451
452 -- TYPE EST_DET_REC_ARRAY_TYPE IS VARRAY(1000) OF CS.CS_ESTIMATE_DETAILS%ROWTYPE;
453 -- est_det_arr EST_DET_REC_ARRAY_TYPE;
454
455 -- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
456 -- as some of the columns have been dropped in 11.5.10 by Charges
457
458 -- gilam: define each column individually
459 TYPE NumTabTypeI IS TABLE OF NUMBER
460 INDEX by Binary_Integer;
461 v_LINE_NUMBER NumTabTypeI ;
462 v_QUANTITY_REQUIRED NumTabTypeI ;
463 v_SELLING_PRICE NumTabTypeI ;
464 v_AFTER_WARRANTY_COST NumTabTypeI ;
465 v_TRANSACTION_TYPE_ID NumTabTypeI ;
466 v_ORDER_HEADER_ID NumTabTypeI ;
467 --v_ORGANIZATION_ID NumTabTypeI ;
468 v_COVERAGE_BILL_RATE_ID NumTabTypeI ;
469 v_ORIGINAL_SOURCE_ID NumTabTypeI ;
470 v_CONTRACT_ID NumTabTypeI ;
471 v_COVERAGE_ID NumTabTypeI ;
472 v_COVERAGE_TXN_GROUP_ID NumTabTypeI ;
473 v_CONVERSION_RATE NumTabTypeI ;
474 v_ORDER_LINE_ID NumTabTypeI ;
475 v_PRICE_LIST_HEADER_ID NumTabTypeI ;
476 --v_FUNC_CURR_AFT_WARR_COST NumTabTypeI ;
477 v_OBJECT_VERSION_NUMBER NumTabTypeI ;
478 --v_SECURITY_GROUP_ID NumTabTypeI ;
479 --v_ORIG_SYSTEM_REFERENCE_ID NumTabTypeI ;
480 v_ORG_ID NumTabTypeI ;
481 --v_TRANS_INV_ORGANIZATION_ID NumTabTypeI ;
482 v_TRANSACTION_INVENTORY_ORG NumTabTypeI ;
483 v_SHIP_TO_CONTACT_ID NumTabTypeI ;
484 v_BILL_TO_CONTACT_ID NumTabTypeI ;
485 v_SHIP_TO_ACCOUNT_ID NumTabTypeI ;
486 v_INVOICE_TO_ACCOUNT_ID NumTabTypeI ;
487 v_LIST_PRICE NumTabTypeI ;
488 v_CONTRACT_DISCOUNT_AMOUNT NumTabTypeI ;
489 v_BILL_TO_PARTY_ID NumTabTypeI ;
490 v_SHIP_TO_PARTY_ID NumTabTypeI ;
491 --sangigup 4610625
492 v_contract_line_id NumTabTypeI;
493 --sangigup 4610625
494
495
496 TYPE NumTabTypeII IS TABLE OF NUMBER(15,0)
497 INDEX by Binary_Integer;
498 --v_TECHNICIAN_ID NumTabTypeII ;
499 --v_ESTIMATE_ID NumTabTypeII ;
500 v_SOURCE_ID NumTabTypeII ;
501 --v_SYSTEM_ID NumTabTypeII ;
502 --v_RMA_HEADER_ID NumTabTypeII ;
503 --v_ESTIMATE_BUSINESS_GROUP_ID NumTabTypeII ;
504 v_INVENTORY_ITEM_ID NumTabTypeII ;
505 v_LAST_UPDATE_BY NumTabTypeII ;
506 --v_EST_TAX_AMOUNT NumTabTypeII ;
507 v_CREATED_BY NumTabTypeII ;
508 v_LAST_UPDATE_LOGIN NumTabTypeII ;
509 --v_RMA_LINE_ID NumTabTypeII ;
510 --v_DIAGNOSIS_ID NumTabTypeII ;
511 --v_TIME_ZONE_ID NumTabTypeII ;
512 v_TXN_BILLING_TYPE_ID NumTabTypeII ;
513 v_INVOICE_TO_ORG_ID NumTabTypeII ;
514 v_SHIP_TO_ORG_ID NumTabTypeII ;
515 v_COVERAGE_BILLING_TYPE_ID NumTabTypeII ;
516 v_ESTIMATE_DETAIL_ID NumTabTypeII ;
517 v_CUSTOMER_PRODUCT_ID NumTabTypeII ;
518 v_BUSINESS_PROCESS_ID NumTabTypeII ;
519 v_INCIDENT_ID NumTabTypeII ;
520 v_LINE_TYPE_ID NumTabTypeII ;
521
522 --TYPE NumTabTypeIII IS TABLE OF NUMBER(30,0)
523 -- INDEX by Binary_Integer;
524 --v_RMA_NUMBER NumTabTypeIII ;
525 --v_RMA_LINE_NUMBER NumTabTypeIII ;
526
527 TYPE DateTabType IS TABLE OF DATE
528 INDEX by Binary_Integer;
529 v_LAST_UPDATE_DATE DateTabType ;
530 v_CREATION_DATE DateTabType ;
531 v_INSTALLED_CP_RETURN_BY_DATE DateTabType ;
532 v_NEW_CP_RETURN_BY_DATE DateTabType ;
533 --v_TXN_START_TIME DateTabType ;
534 --v_TXN_END_TIME DateTabType ;
535 v_CONVERSION_RATE_DATE DateTabType ;
536 --v_ACTIVITY_DATE DateTabType ;
537 --v_ACTIVITY_START_TIME DateTabType ;
538 --v_ACTIVITY_END_TIME DateTabType ;
539 v_ACTIVITY_START_DATE_TIME DateTabType ;
540 v_ACTIVITY_END_DATE_TIME DateTabType ;
541
542 TYPE VCharTabTypeI IS TABLE OF VARCHAR2(1)
543 INDEX by Binary_Integer;
544 v_INTERFACE_TO_OE_FLAG VCharTabTypeI ;
545 v_ROLLUP_FLAG VCharTabTypeI ;
546 --v_ADD_TO_ORDER VCharTabTypeI ;
547 v_ADD_TO_ORDER_FLAG VCharTabTypeI ;
548 --v_EXCEPTION_COVERAGE_USED VCharTabTypeI ;
549 --v_UPGRADED_STATUS_FLAG VCharTabTypeI ;
550 v_NO_CHARGE_FLAG VCharTabTypeI ;
551 v_GENERATED_BY_BCA_ENGINE_FLAG VCharTabTypeI ;
552 v_LINE_SUBMITTED VCharTabTypeI ;
553
554 TYPE VCharTabTypeII IS TABLE OF VARCHAR2(3)
555 INDEX by Binary_Integer;
556 v_UNIT_OF_MEASURE_CODE VCharTabTypeII ;
557 v_ITEM_REVISION VCharTabTypeII ;
558
559 TYPE VCharTabTypeIII IS TABLE OF VARCHAR2(10)
560 INDEX by Binary_Integer;
561 v_ORIGINAL_SOURCE_CODE VCharTabTypeIII ;
562 v_SOURCE_CODE VCharTabTypeIII ;
563 --v_TRANS_SUBINVENTORY VCharTabTypeIII ;
564 v_TRANSACTION_SUB_INVENTORY VCharTabTypeIII ;
565
566 TYPE VCharTabTypeIV IS TABLE OF VARCHAR2(15)
567 INDEX by Binary_Integer;
568 v_CURRENCY_CODE VCharTabTypeIV ;
569
570 TYPE VCharTabTypeV IS TABLE OF VARCHAR2(30)
571 INDEX by Binary_Integer;
572 v_SERIAL_NUMBER VCharTabTypeV ;
573 v_PRICING_CONTEXT VCharTabTypeV ;
574 v_CONTEXT VCharTabTypeV ;
575 v_LINE_CATEGORY_CODE VCharTabTypeV ;
576 v_CONVERSION_TYPE_CODE VCharTabTypeV ;
577 v_RETURN_REASON_CODE VCharTabTypeV ;
578 --v_TAX_CODE VCharTabTypeV ;
579 v_CHARGE_LINE_TYPE VCharTabTypeV ;
580 v_SUBMIT_FROM_SYSTEM VCharTabTypeV ;
581
582 TYPE VCharTabTypeVI IS TABLE OF VARCHAR2(50)
583 INDEX by Binary_Integer;
584 --v_ORIGINAL_SYSTEM_REFERENCE VCharTabTypeVI ;
585 --v_ORIGINAL_SYS_LINE_REFERENCE VCharTabTypeVI ;
586 v_PURCHASE_ORDER_NUM VCharTabTypeVI ;
587 --v_ORIG_SYSTEM_REFERENCE VCharTabTypeVI ;
588 --v_ORIG_SYSTEM_LINE_REFERENCE VCharTabTypeVI ;
589 --sangigup 4610625
590 v_rate_type_Code VCharTabTypeVI;
591 --sangigup
592
593 TYPE VCharTabTypeVII IS TABLE OF VARCHAR2(150)
594 INDEX by Binary_Integer;
595 v_ATTRIBUTE1 VCharTabTypeVII ;
596 v_ATTRIBUTE2 VCharTabTypeVII ;
597 v_ATTRIBUTE3 VCharTabTypeVII ;
598 v_ATTRIBUTE4 VCharTabTypeVII ;
599 v_ATTRIBUTE5 VCharTabTypeVII ;
600 v_ATTRIBUTE6 VCharTabTypeVII ;
601 v_ATTRIBUTE7 VCharTabTypeVII ;
602 v_ATTRIBUTE8 VCharTabTypeVII ;
603 v_ATTRIBUTE9 VCharTabTypeVII ;
604 v_ATTRIBUTE10 VCharTabTypeVII ;
605 v_ATTRIBUTE11 VCharTabTypeVII ;
606 v_ATTRIBUTE12 VCharTabTypeVII ;
607 v_ATTRIBUTE13 VCharTabTypeVII ;
608 v_ATTRIBUTE14 VCharTabTypeVII ;
609 v_ATTRIBUTE15 VCharTabTypeVII ;
610 v_PRICING_ATTRIBUTE1 VCharTabTypeVII ;
611 v_PRICING_ATTRIBUTE2 VCharTabTypeVII ;
612 v_PRICING_ATTRIBUTE3 VCharTabTypeVII ;
613 v_PRICING_ATTRIBUTE4 VCharTabTypeVII ;
614 v_PRICING_ATTRIBUTE5 VCharTabTypeVII ;
615 v_PRICING_ATTRIBUTE6 VCharTabTypeVII ;
616 v_PRICING_ATTRIBUTE7 VCharTabTypeVII ;
617 v_PRICING_ATTRIBUTE8 VCharTabTypeVII ;
618 v_PRICING_ATTRIBUTE9 VCharTabTypeVII ;
619 v_PRICING_ATTRIBUTE10 VCharTabTypeVII ;
620 v_PRICING_ATTRIBUTE11 VCharTabTypeVII ;
621 v_PRICING_ATTRIBUTE12 VCharTabTypeVII ;
622 v_PRICING_ATTRIBUTE13 VCharTabTypeVII ;
623 v_PRICING_ATTRIBUTE14 VCharTabTypeVII ;
624 v_PRICING_ATTRIBUTE15 VCharTabTypeVII ;
625 v_PRICING_ATTRIBUTE16 VCharTabTypeVII ;
626 v_PRICING_ATTRIBUTE17 VCharTabTypeVII ;
627 v_PRICING_ATTRIBUTE18 VCharTabTypeVII ;
628 v_PRICING_ATTRIBUTE19 VCharTabTypeVII ;
629 v_PRICING_ATTRIBUTE20 VCharTabTypeVII ;
630 v_PRICING_ATTRIBUTE21 VCharTabTypeVII ;
631 v_PRICING_ATTRIBUTE22 VCharTabTypeVII ;
632 v_PRICING_ATTRIBUTE23 VCharTabTypeVII ;
633 v_PRICING_ATTRIBUTE24 VCharTabTypeVII ;
634 v_PRICING_ATTRIBUTE25 VCharTabTypeVII ;
635 v_PRICING_ATTRIBUTE26 VCharTabTypeVII ;
636 v_PRICING_ATTRIBUTE27 VCharTabTypeVII ;
637 v_PRICING_ATTRIBUTE28 VCharTabTypeVII ;
638 v_PRICING_ATTRIBUTE29 VCharTabTypeVII ;
639 v_PRICING_ATTRIBUTE30 VCharTabTypeVII ;
640 v_PRICING_ATTRIBUTE31 VCharTabTypeVII ;
641 v_PRICING_ATTRIBUTE32 VCharTabTypeVII ;
642 v_PRICING_ATTRIBUTE33 VCharTabTypeVII ;
643 v_PRICING_ATTRIBUTE34 VCharTabTypeVII ;
644 v_PRICING_ATTRIBUTE35 VCharTabTypeVII ;
645 v_PRICING_ATTRIBUTE36 VCharTabTypeVII ;
646 v_PRICING_ATTRIBUTE37 VCharTabTypeVII ;
647 v_PRICING_ATTRIBUTE38 VCharTabTypeVII ;
648 v_PRICING_ATTRIBUTE39 VCharTabTypeVII ;
649 v_PRICING_ATTRIBUTE40 VCharTabTypeVII ;
650 v_PRICING_ATTRIBUTE41 VCharTabTypeVII ;
651 v_PRICING_ATTRIBUTE42 VCharTabTypeVII ;
652 v_PRICING_ATTRIBUTE43 VCharTabTypeVII ;
653 v_PRICING_ATTRIBUTE44 VCharTabTypeVII ;
654 v_PRICING_ATTRIBUTE45 VCharTabTypeVII ;
655 v_PRICING_ATTRIBUTE46 VCharTabTypeVII ;
656 v_PRICING_ATTRIBUTE47 VCharTabTypeVII ;
657 v_PRICING_ATTRIBUTE48 VCharTabTypeVII ;
658 v_PRICING_ATTRIBUTE49 VCharTabTypeVII ;
659 v_PRICING_ATTRIBUTE50 VCharTabTypeVII ;
660 v_PRICING_ATTRIBUTE51 VCharTabTypeVII ;
661 v_PRICING_ATTRIBUTE52 VCharTabTypeVII ;
662 v_PRICING_ATTRIBUTE53 VCharTabTypeVII ;
663 v_PRICING_ATTRIBUTE54 VCharTabTypeVII ;
664 v_PRICING_ATTRIBUTE55 VCharTabTypeVII ;
665 v_PRICING_ATTRIBUTE56 VCharTabTypeVII ;
666 v_PRICING_ATTRIBUTE57 VCharTabTypeVII ;
667 v_PRICING_ATTRIBUTE58 VCharTabTypeVII ;
668 v_PRICING_ATTRIBUTE59 VCharTabTypeVII ;
669 v_PRICING_ATTRIBUTE60 VCharTabTypeVII ;
670 v_PRICING_ATTRIBUTE61 VCharTabTypeVII ;
671 v_PRICING_ATTRIBUTE62 VCharTabTypeVII ;
672 v_PRICING_ATTRIBUTE63 VCharTabTypeVII ;
673 v_PRICING_ATTRIBUTE64 VCharTabTypeVII ;
674 v_PRICING_ATTRIBUTE65 VCharTabTypeVII ;
675 v_PRICING_ATTRIBUTE66 VCharTabTypeVII ;
676 v_PRICING_ATTRIBUTE67 VCharTabTypeVII ;
677 v_PRICING_ATTRIBUTE68 VCharTabTypeVII ;
678 v_PRICING_ATTRIBUTE69 VCharTabTypeVII ;
679 v_PRICING_ATTRIBUTE70 VCharTabTypeVII ;
680 v_PRICING_ATTRIBUTE71 VCharTabTypeVII ;
681 v_PRICING_ATTRIBUTE72 VCharTabTypeVII ;
682 v_PRICING_ATTRIBUTE73 VCharTabTypeVII ;
683 v_PRICING_ATTRIBUTE74 VCharTabTypeVII ;
684 v_PRICING_ATTRIBUTE75 VCharTabTypeVII ;
685 v_PRICING_ATTRIBUTE76 VCharTabTypeVII ;
686 v_PRICING_ATTRIBUTE77 VCharTabTypeVII ;
687 v_PRICING_ATTRIBUTE78 VCharTabTypeVII ;
688 v_PRICING_ATTRIBUTE79 VCharTabTypeVII ;
689 v_PRICING_ATTRIBUTE80 VCharTabTypeVII ;
690 v_PRICING_ATTRIBUTE81 VCharTabTypeVII ;
691 v_PRICING_ATTRIBUTE82 VCharTabTypeVII ;
692 v_PRICING_ATTRIBUTE83 VCharTabTypeVII ;
693 v_PRICING_ATTRIBUTE84 VCharTabTypeVII ;
694 v_PRICING_ATTRIBUTE85 VCharTabTypeVII ;
695 v_PRICING_ATTRIBUTE86 VCharTabTypeVII ;
696 v_PRICING_ATTRIBUTE87 VCharTabTypeVII ;
697 v_PRICING_ATTRIBUTE88 VCharTabTypeVII ;
698 v_PRICING_ATTRIBUTE89 VCharTabTypeVII ;
699 v_PRICING_ATTRIBUTE90 VCharTabTypeVII ;
700 v_PRICING_ATTRIBUTE91 VCharTabTypeVII ;
701 v_PRICING_ATTRIBUTE92 VCharTabTypeVII ;
702 v_PRICING_ATTRIBUTE93 VCharTabTypeVII ;
703 v_PRICING_ATTRIBUTE94 VCharTabTypeVII ;
704 v_PRICING_ATTRIBUTE95 VCharTabTypeVII ;
705 v_PRICING_ATTRIBUTE96 VCharTabTypeVII ;
706 v_PRICING_ATTRIBUTE97 VCharTabTypeVII ;
707 v_PRICING_ATTRIBUTE98 VCharTabTypeVII ;
708 v_PRICING_ATTRIBUTE99 VCharTabTypeVII ;
709 v_PRICING_ATTRIBUTE100 VCharTabTypeVII ;
710
711 TYPE VCharTabTypeVIII IS TABLE OF VARCHAR2(2000)
712 INDEX by Binary_Integer;
713 v_SUBMIT_RESTRICTION_MESSAGE VCharTabTypeVIII ;
714 v_SUBMIT_ERROR_MESSAGE VCharTabTypeVIII ;
715
716 v_min NUMBER;
717 v_max NUMBER;
718 v_error_text VARCHAR2(2000);
719 MAX_BUFFER_SIZE NUMBER := 500;
720 error_process EXCEPTION;
721 l_Array_Size Number ;
722
723 l_old_est_detail_id NUMBER;
724 l_new_est_detail_id NUMBER;
725 l_rep_est_line_id NUMBER;
726 l_actual_id NUMBER;
727 x_actual_line_id NUMBER;
728
729 l_line_num NUMBER := 1 ;
730 l_ed_id NUMBER;
731 x_object_version_number NUMBER;
732
733 -- gilam: changed sql to list out all the columns
734 -- get all the OM interfaced charge lines created from Depot Repair
735
736 -- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
737 -- as some of the columns have been dropped in 11.5.10 by Charges
738
739 -- gilam: bug 3410383 - changed query to use repair line id instead
740 /*
741 CURSOR get_charge_est_details(p_start_est_det_id number, p_end_est_det_id number)
742 IS
743 SELECT ced.estimate_detail_id,
744 ced.last_update_date,
745 ced.last_updated_by,
746 ced.creation_date,
747 ced.created_by,
748 ced.last_update_login,
749 --ced.estimate_id,
750 ced.line_number,
751 ced.inventory_item_id,
752 ced.serial_number,
753 ced.quantity_required,
754 ced.unit_of_measure_code,
755 ced.selling_price,
756 ced.after_warranty_cost,
757 ced.pricing_context,
758 ced.pricing_attribute1,
759 ced.pricing_attribute2,
760 ced.pricing_attribute3,
761 ced.pricing_attribute4,
762 ced.pricing_attribute5,
763 ced.pricing_attribute6,
764 ced.pricing_attribute7,
765 ced.pricing_attribute8,
766 ced.pricing_attribute9,
767 ced.pricing_attribute10,
768 ced.pricing_attribute11,
769 ced.pricing_attribute12,
770 ced.pricing_attribute13,
771 ced.pricing_attribute14,
772 ced.pricing_attribute15,
773 ced.attribute1,
774 ced.attribute2,
775 ced.attribute3,
776 ced.attribute4,
777 ced.attribute5,
778 ced.attribute6,
779 ced.attribute7,
780 ced.attribute8,
781 ced.attribute9,
782 ced.attribute10,
783 ced.attribute11,
784 ced.attribute12,
785 ced.attribute13,
786 ced.attribute14,
787 ced.attribute15,
788 ced.context,
789 --ced.organization_id,
790 --ced.diagnosis_id,
791 --ced.estimate_business_group_id,
792 ced.transaction_type_id,
793 ced.customer_product_id,
794 ced.order_header_id,
795 --ced.original_system_reference,
796 --ced.original_system_line_reference,
797 ced.installed_cp_return_by_date,
798 ced.new_cp_return_by_date,
799 ced.interface_to_oe_flag,
800 ced.rollup_flag,
801 --ced.add_to_order,
802 --ced.system_id,
803 --ced.rma_header_id,
804 --ced.rma_number,
805 --ced.rma_line_id,
806 --ced.rma_line_number,
807 --ced.technician_id,
808 --ced.txn_start_time,
809 --ced.txn_end_time,
810 ced.coverage_bill_rate_id,
811 ced.coverage_billing_type_id,
812 --ced.time_zone_id,
813 ced.txn_billing_type_id,
814 ced.business_process_id,
815 ced.incident_id,
816 ced.original_source_id,
817 ced.original_source_code,
818 ced.source_id,
819 ced.source_code,
820 ced.contract_id,
821 ced.coverage_id,
822 ced.coverage_txn_group_id,
823 ced.invoice_to_org_id,
824 ced.ship_to_org_id,
825 ced.purchase_order_num,
826 ced.line_type_id,
827 ced.line_category_code,
828 ced.currency_code,
829 ced.conversion_rate,
830 ced.conversion_type_code,
831 ced.conversion_rate_date,
832 ced.return_reason_code,
833 ced.order_line_id,
834 ced.price_list_header_id,
835 --ced.func_curr_aft_warr_cost,
836 --ced.orig_system_reference,
837 --ced.orig_system_line_reference,
838 ced.add_to_order_flag,
839 --ced.exception_coverage_used,
840 --ced.tax_code,
841 --ced.est_tax_amount,
842 ced.object_version_number,
843 ced.pricing_attribute16,
844 ced.pricing_attribute17,
845 ced.pricing_attribute18,
846 ced.pricing_attribute19,
847 ced.pricing_attribute20,
848 ced.pricing_attribute21,
849 ced.pricing_attribute22,
850 ced.pricing_attribute23,
851 ced.pricing_attribute24,
852 ced.pricing_attribute25,
853 ced.pricing_attribute26,
854 ced.pricing_attribute27,
855 ced.pricing_attribute28,
856 ced.pricing_attribute29,
857 ced.pricing_attribute30,
858 ced.pricing_attribute31,
859 ced.pricing_attribute32,
860 ced.pricing_attribute33,
861 ced.pricing_attribute34,
862 ced.pricing_attribute35,
863 ced.pricing_attribute36,
864 ced.pricing_attribute37,
865 ced.pricing_attribute38,
866 ced.pricing_attribute39,
867 ced.pricing_attribute40,
868 ced.pricing_attribute41,
869 ced.pricing_attribute42,
870 ced.pricing_attribute43,
871 ced.pricing_attribute44,
872 ced.pricing_attribute45,
873 ced.pricing_attribute46,
874 ced.pricing_attribute47,
875 ced.pricing_attribute48,
876 ced.pricing_attribute49,
877 ced.pricing_attribute50,
878 ced.pricing_attribute51,
879 ced.pricing_attribute52,
880 ced.pricing_attribute53,
881 ced.pricing_attribute54,
882 ced.pricing_attribute55,
883 ced.pricing_attribute56,
884 ced.pricing_attribute57,
885 ced.pricing_attribute58,
886 ced.pricing_attribute59,
887 ced.pricing_attribute61,
888 ced.pricing_attribute62,
889 ced.pricing_attribute63,
890 ced.pricing_attribute64,
891 ced.pricing_attribute65,
892 ced.pricing_attribute66,
893 ced.pricing_attribute67,
894 ced.pricing_attribute68,
895 ced.pricing_attribute69,
896 ced.pricing_attribute70,
897 ced.pricing_attribute71,
898 ced.pricing_attribute72,
899 ced.pricing_attribute73,
900 ced.pricing_attribute74,
901 ced.pricing_attribute75,
902 ced.pricing_attribute76,
903 ced.pricing_attribute77,
904 ced.pricing_attribute78,
905 ced.pricing_attribute79,
906 ced.pricing_attribute80,
907 ced.pricing_attribute81,
908 ced.pricing_attribute82,
909 ced.pricing_attribute83,
910 ced.pricing_attribute84,
911 ced.pricing_attribute85,
912 ced.pricing_attribute86,
913 ced.pricing_attribute87,
914 ced.pricing_attribute88,
915 ced.pricing_attribute89,
916 ced.pricing_attribute90,
917 ced.pricing_attribute91,
918 ced.pricing_attribute92,
919 ced.pricing_attribute93,
920 ced.pricing_attribute94,
921 ced.pricing_attribute95,
922 ced.pricing_attribute96,
923 ced.pricing_attribute97,
924 ced.pricing_attribute98,
925 ced.pricing_attribute99,
926 ced.pricing_attribute100,
927 ced.pricing_attribute60,
928 --ced.security_group_id,
929 --ced.upgraded_status_flag,
930 --ced.orig_system_reference_id,
931 ced.no_charge_flag,
932 ced.org_id,
933 ced.item_revision,
934 --ced.trans_inv_organization_id,
935 --ced.trans_subinventory,
936 --ced.activity_date,
937 --ced.activity_start_time,
938 --ced.activity_end_time,
939 ced.generated_by_bca_engine_flag,
940 ced.transaction_inventory_org,
941 ced.transaction_sub_inventory,
942 ced.activity_start_date_time,
943 ced.activity_end_date_time,
944 ced.charge_line_type,
945 ced.ship_to_contact_id,
946 ced.bill_to_contact_id,
947 ced.ship_to_account_id,
948 ced.invoice_to_account_id,
949 ced.list_price,
950 ced.contract_discount_amount,
951 ced.bill_to_party_id,
952 ced.ship_to_party_id,
953 ced.submit_restriction_message,
954 ced.submit_error_message,
955 ced.line_submitted,
956 ced.submit_from_system
957 FROM cs_estimate_details ced
958 , csd_repair_estimate_lines cr
959 WHERE cr.estimate_detail_id = ced.estimate_detail_id
960 AND ced.charge_line_type = 'ACTUAL'
961 AND ced.order_line_id is not null
962 AND ced.original_source_code = 'DR'
963 AND ced.source_code = 'DR'
964 AND NOT EXISTS ( SELECT 'x'
965 FROM csd_repair_actual_lines cral
966 WHERE cral.estimate_detail_id = cr.estimate_detail_id)
967 AND ced.estimate_detail_id >= p_start_est_det_id
968 AND ced.estimate_detail_id <= p_end_est_det_id;
969 */
970
971 CURSOR get_charge_est_details(p_start_rep_line_id number, p_end_rep_line_id number)
972 IS
973 SELECT ced.estimate_detail_id,
974 ced.last_update_date,
975 ced.last_updated_by,
976 ced.creation_date,
977 ced.created_by,
978 ced.last_update_login,
979 --ced.estimate_id,
980 ced.line_number,
981 ced.inventory_item_id,
982 ced.serial_number,
983 ced.quantity_required,
984 ced.unit_of_measure_code,
985 ced.selling_price,
986 ced.after_warranty_cost,
987 ced.pricing_context,
988 ced.pricing_attribute1,
989 ced.pricing_attribute2,
990 ced.pricing_attribute3,
991 ced.pricing_attribute4,
992 ced.pricing_attribute5,
993 ced.pricing_attribute6,
994 ced.pricing_attribute7,
995 ced.pricing_attribute8,
996 ced.pricing_attribute9,
997 ced.pricing_attribute10,
998 ced.pricing_attribute11,
999 ced.pricing_attribute12,
1000 ced.pricing_attribute13,
1001 ced.pricing_attribute14,
1002 ced.pricing_attribute15,
1003 ced.attribute1,
1004 ced.attribute2,
1005 ced.attribute3,
1006 ced.attribute4,
1007 ced.attribute5,
1008 ced.attribute6,
1009 ced.attribute7,
1010 ced.attribute8,
1011 ced.attribute9,
1012 ced.attribute10,
1013 ced.attribute11,
1014 ced.attribute12,
1015 ced.attribute13,
1016 ced.attribute14,
1017 ced.attribute15,
1018 ced.context,
1019 --ced.organization_id,
1020 --ced.diagnosis_id,
1021 --ced.estimate_business_group_id,
1022 ced.transaction_type_id,
1023 ced.customer_product_id,
1024 ced.order_header_id,
1025 --ced.original_system_reference,
1026 --ced.original_system_line_reference,
1027 ced.installed_cp_return_by_date,
1028 ced.new_cp_return_by_date,
1029 ced.interface_to_oe_flag,
1030 ced.rollup_flag,
1031 --ced.add_to_order,
1032 --ced.system_id,
1033 --ced.rma_header_id,
1034 --ced.rma_number,
1035 --ced.rma_line_id,
1036 --ced.rma_line_number,
1037 --ced.technician_id,
1038 --ced.txn_start_time,
1039 --ced.txn_end_time,
1040 ced.coverage_bill_rate_id,
1041 ced.coverage_billing_type_id,
1042 --ced.time_zone_id,
1043 ced.txn_billing_type_id,
1044 ced.business_process_id,
1045 ced.incident_id,
1046 ced.original_source_id,
1047 ced.original_source_code,
1048 ced.source_id,
1049 ced.source_code,
1050 ced.contract_id,
1051 ced.coverage_id,
1052 ced.coverage_txn_group_id,
1053 ced.invoice_to_org_id,
1054 ced.ship_to_org_id,
1055 ced.purchase_order_num,
1056 ced.line_type_id,
1057 ced.line_category_code,
1058 ced.currency_code,
1059 ced.conversion_rate,
1060 ced.conversion_type_code,
1061 ced.conversion_rate_date,
1062 ced.return_reason_code,
1063 ced.order_line_id,
1064 ced.price_list_header_id,
1065 --ced.func_curr_aft_warr_cost,
1066 --ced.orig_system_reference,
1067 --ced.orig_system_line_reference,
1068 ced.add_to_order_flag,
1069 --ced.exception_coverage_used,
1070 --ced.tax_code,
1071 --ced.est_tax_amount,
1072 ced.object_version_number,
1073 ced.pricing_attribute16,
1074 ced.pricing_attribute17,
1075 ced.pricing_attribute18,
1076 ced.pricing_attribute19,
1077 ced.pricing_attribute20,
1078 ced.pricing_attribute21,
1079 ced.pricing_attribute22,
1080 ced.pricing_attribute23,
1081 ced.pricing_attribute24,
1082 ced.pricing_attribute25,
1083 ced.pricing_attribute26,
1084 ced.pricing_attribute27,
1085 ced.pricing_attribute28,
1086 ced.pricing_attribute29,
1087 ced.pricing_attribute30,
1088 ced.pricing_attribute31,
1089 ced.pricing_attribute32,
1090 ced.pricing_attribute33,
1091 ced.pricing_attribute34,
1092 ced.pricing_attribute35,
1093 ced.pricing_attribute36,
1094 ced.pricing_attribute37,
1095 ced.pricing_attribute38,
1096 ced.pricing_attribute39,
1097 ced.pricing_attribute40,
1098 ced.pricing_attribute41,
1099 ced.pricing_attribute42,
1100 ced.pricing_attribute43,
1101 ced.pricing_attribute44,
1102 ced.pricing_attribute45,
1103 ced.pricing_attribute46,
1104 ced.pricing_attribute47,
1105 ced.pricing_attribute48,
1106 ced.pricing_attribute49,
1107 ced.pricing_attribute50,
1108 ced.pricing_attribute51,
1109 ced.pricing_attribute52,
1110 ced.pricing_attribute53,
1111 ced.pricing_attribute54,
1112 ced.pricing_attribute55,
1113 ced.pricing_attribute56,
1114 ced.pricing_attribute57,
1115 ced.pricing_attribute58,
1116 ced.pricing_attribute59,
1117 ced.pricing_attribute61,
1118 ced.pricing_attribute62,
1119 ced.pricing_attribute63,
1120 ced.pricing_attribute64,
1121 ced.pricing_attribute65,
1122 ced.pricing_attribute66,
1123 ced.pricing_attribute67,
1124 ced.pricing_attribute68,
1125 ced.pricing_attribute69,
1126 ced.pricing_attribute70,
1127 ced.pricing_attribute71,
1128 ced.pricing_attribute72,
1129 ced.pricing_attribute73,
1130 ced.pricing_attribute74,
1131 ced.pricing_attribute75,
1132 ced.pricing_attribute76,
1133 ced.pricing_attribute77,
1134 ced.pricing_attribute78,
1135 ced.pricing_attribute79,
1136 ced.pricing_attribute80,
1137 ced.pricing_attribute81,
1138 ced.pricing_attribute82,
1139 ced.pricing_attribute83,
1140 ced.pricing_attribute84,
1141 ced.pricing_attribute85,
1142 ced.pricing_attribute86,
1143 ced.pricing_attribute87,
1144 ced.pricing_attribute88,
1145 ced.pricing_attribute89,
1146 ced.pricing_attribute90,
1147 ced.pricing_attribute91,
1148 ced.pricing_attribute92,
1149 ced.pricing_attribute93,
1150 ced.pricing_attribute94,
1151 ced.pricing_attribute95,
1152 ced.pricing_attribute96,
1153 ced.pricing_attribute97,
1154 ced.pricing_attribute98,
1155 ced.pricing_attribute99,
1156 ced.pricing_attribute100,
1157 ced.pricing_attribute60,
1158 --ced.security_group_id,
1159 --ced.upgraded_status_flag,
1160 --ced.orig_system_reference_id,
1161 ced.no_charge_flag,
1162 ced.org_id,
1163 ced.item_revision,
1164 --ced.trans_inv_organization_id,
1165 --ced.trans_subinventory,
1166 --ced.activity_date,
1167 --ced.activity_start_time,
1168 --ced.activity_end_time,
1169 ced.generated_by_bca_engine_flag,
1170 ced.transaction_inventory_org,
1171 ced.transaction_sub_inventory,
1172 ced.activity_start_date_time,
1173 ced.activity_end_date_time,
1174 ced.charge_line_type,
1175 ced.ship_to_contact_id,
1176 ced.bill_to_contact_id,
1177 ced.ship_to_account_id,
1178 ced.invoice_to_account_id,
1179 ced.list_price,
1180 ced.contract_discount_amount,
1181 ced.bill_to_party_id,
1182 ced.ship_to_party_id,
1183 ced.submit_restriction_message,
1184 ced.submit_error_message,
1185 ced.line_submitted,
1186 ced.submit_from_system
1187 --sangigup 4610625
1188 ,ced.contract_line_id
1189 ,ced.rate_type_code
1190 --sangigup
1191
1192 FROM cs_estimate_details ced
1193 , csd_repairs cr
1194 , csd_repair_estimate cre
1195 , csd_repair_estimate_lines crel
1196 WHERE cr.repair_line_id = cre.repair_line_id
1197 AND cre.repair_estimate_id = crel.repair_estimate_id
1198 AND crel.estimate_detail_id = ced.estimate_detail_id
1199 AND ced.charge_line_type = 'ACTUAL'
1200 AND ced.order_line_id is not null
1201 AND ced.original_source_code = 'DR'
1202 AND ced.source_code = 'DR'
1203 AND NOT EXISTS ( SELECT 'x'
1204 FROM csd_repair_actual_lines cral
1205 WHERE cral.estimate_detail_id = crel.estimate_detail_id)
1206 AND cr.repair_line_id >= p_start_rep_line_id
1207 AND cr.repair_line_id <= p_end_rep_line_id;
1208 -- gilam: end bug fix 3410303 - changed query
1209
1210 BEGIN
1211
1212 -- Get the Slab Number for the table
1213
1214 BEGIN
1215
1216 -- gilam: bug 3410383 - changed slab table to csd_repairs
1217 /*
1218 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CS_ESTIMATE_DETAILS',
1219 'CSD',
1220 p_slab_number,
1221 v_min,
1222 v_max);
1223 */
1224
1225 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIRS',
1226 'CSD',
1227 p_slab_number,
1228 v_min,
1229 v_max);
1230 -- gilam: end bug fix 3410383
1231
1232 IF v_min IS NULL
1233 THEN
1234 RETURN;
1235 END IF;
1236
1237 END;
1238
1239 -- Migration code for Repair Estimate Lines
1240
1241 OPEN get_charge_est_details(v_min, v_max);
1242
1243 LOOP
1244
1245 -- gilam: bug 3362408/3362418 - commented out all the columns that are not used in insert_row
1246 -- as some of the columns have been dropped in 11.5.10 by Charges
1247
1248 -- gilam: changed FETCH stmt to fetch into individual tables
1249 FETCH get_charge_est_details BULK COLLECT
1250 INTO
1251 v_ESTIMATE_DETAIL_ID ,
1252 v_LAST_UPDATE_DATE ,
1253 v_LAST_UPDATE_BY ,
1254 v_CREATION_DATE ,
1255 v_CREATED_BY ,
1256 v_LAST_UPDATE_LOGIN ,
1257 --v_ESTIMATE_ID ,
1258 v_LINE_NUMBER ,
1259 v_INVENTORY_ITEM_ID ,
1260 v_SERIAL_NUMBER ,
1261 v_QUANTITY_REQUIRED ,
1262 v_UNIT_OF_MEASURE_CODE ,
1263 v_SELLING_PRICE ,
1264 v_AFTER_WARRANTY_COST ,
1265 v_PRICING_CONTEXT ,
1266 v_PRICING_ATTRIBUTE1 ,
1267 v_PRICING_ATTRIBUTE2 ,
1268 v_PRICING_ATTRIBUTE3 ,
1269 v_PRICING_ATTRIBUTE4 ,
1270 v_PRICING_ATTRIBUTE5 ,
1271 v_PRICING_ATTRIBUTE6 ,
1272 v_PRICING_ATTRIBUTE7 ,
1273 v_PRICING_ATTRIBUTE8 ,
1274 v_PRICING_ATTRIBUTE9 ,
1275 v_PRICING_ATTRIBUTE10 ,
1276 v_PRICING_ATTRIBUTE11 ,
1277 v_PRICING_ATTRIBUTE12 ,
1278 v_PRICING_ATTRIBUTE13 ,
1279 v_PRICING_ATTRIBUTE14 ,
1280 v_PRICING_ATTRIBUTE15 ,
1281 v_ATTRIBUTE1 ,
1282 v_ATTRIBUTE2 ,
1283 v_ATTRIBUTE3 ,
1284 v_ATTRIBUTE4 ,
1285 v_ATTRIBUTE5 ,
1286 v_ATTRIBUTE6 ,
1287 v_ATTRIBUTE7 ,
1288 v_ATTRIBUTE8 ,
1289 v_ATTRIBUTE9 ,
1290 v_ATTRIBUTE10 ,
1291 v_ATTRIBUTE11 ,
1292 v_ATTRIBUTE12 ,
1293 v_ATTRIBUTE13 ,
1294 v_ATTRIBUTE14 ,
1295 v_ATTRIBUTE15 ,
1296 v_CONTEXT ,
1297 --v_ORGANIZATION_ID ,
1298 --v_DIAGNOSIS_ID ,
1299 --v_ESTIMATE_BUSINESS_GROUP_ID ,
1300 v_TRANSACTION_TYPE_ID ,
1301 v_CUSTOMER_PRODUCT_ID ,
1302 v_ORDER_HEADER_ID ,
1303 --v_ORIGINAL_SYSTEM_REFERENCE ,
1304 --v_ORIGINAL_SYS_LINE_REFERENCE ,
1305 v_INSTALLED_CP_RETURN_BY_DATE ,
1306 v_NEW_CP_RETURN_BY_DATE ,
1307 v_INTERFACE_TO_OE_FLAG ,
1308 v_ROLLUP_FLAG ,
1309 --v_ADD_TO_ORDER ,
1310 --v_SYSTEM_ID ,
1311 --v_RMA_HEADER_ID ,
1312 --v_RMA_NUMBER ,
1313 --v_RMA_LINE_ID ,
1314 --v_RMA_LINE_NUMBER ,
1315 --v_TECHNICIAN_ID ,
1316 --v_TXN_START_TIME ,
1317 --v_TXN_END_TIME ,
1318 v_COVERAGE_BILL_RATE_ID ,
1319 v_COVERAGE_BILLING_TYPE_ID ,
1320 --v_TIME_ZONE_ID ,
1321 v_TXN_BILLING_TYPE_ID ,
1322 v_BUSINESS_PROCESS_ID ,
1323 v_INCIDENT_ID ,
1324 v_ORIGINAL_SOURCE_ID ,
1325 v_ORIGINAL_SOURCE_CODE ,
1326 v_SOURCE_ID ,
1327 v_SOURCE_CODE ,
1328 v_CONTRACT_ID ,
1329 v_COVERAGE_ID ,
1330 v_COVERAGE_TXN_GROUP_ID ,
1331 v_INVOICE_TO_ORG_ID ,
1332 v_SHIP_TO_ORG_ID ,
1333 v_PURCHASE_ORDER_NUM ,
1334 v_LINE_TYPE_ID ,
1335 v_LINE_CATEGORY_CODE ,
1336 v_CURRENCY_CODE ,
1337 v_CONVERSION_RATE ,
1338 v_CONVERSION_TYPE_CODE ,
1339 v_CONVERSION_RATE_DATE ,
1340 v_RETURN_REASON_CODE ,
1341 v_ORDER_LINE_ID ,
1342 v_PRICE_LIST_HEADER_ID ,
1343 --v_FUNC_CURR_AFT_WARR_COST ,
1344 --v_ORIG_SYSTEM_REFERENCE ,
1345 --v_ORIG_SYSTEM_LINE_REFERENCE ,
1346 v_ADD_TO_ORDER_FLAG ,
1347 --v_EXCEPTION_COVERAGE_USED ,
1348 --v_TAX_CODE ,
1349 --v_EST_TAX_AMOUNT ,
1350 v_OBJECT_VERSION_NUMBER ,
1351 v_PRICING_ATTRIBUTE16 ,
1352 v_PRICING_ATTRIBUTE17 ,
1353 v_PRICING_ATTRIBUTE18 ,
1354 v_PRICING_ATTRIBUTE19 ,
1355 v_PRICING_ATTRIBUTE20 ,
1356 v_PRICING_ATTRIBUTE21 ,
1357 v_PRICING_ATTRIBUTE22 ,
1358 v_PRICING_ATTRIBUTE23 ,
1359 v_PRICING_ATTRIBUTE24 ,
1360 v_PRICING_ATTRIBUTE25 ,
1361 v_PRICING_ATTRIBUTE26 ,
1362 v_PRICING_ATTRIBUTE27 ,
1363 v_PRICING_ATTRIBUTE28 ,
1364 v_PRICING_ATTRIBUTE29 ,
1365 v_PRICING_ATTRIBUTE30 ,
1366 v_PRICING_ATTRIBUTE31 ,
1367 v_PRICING_ATTRIBUTE32 ,
1368 v_PRICING_ATTRIBUTE33 ,
1369 v_PRICING_ATTRIBUTE34 ,
1370 v_PRICING_ATTRIBUTE35 ,
1371 v_PRICING_ATTRIBUTE36 ,
1372 v_PRICING_ATTRIBUTE37 ,
1373 v_PRICING_ATTRIBUTE38 ,
1374 v_PRICING_ATTRIBUTE39 ,
1375 v_PRICING_ATTRIBUTE40 ,
1376 v_PRICING_ATTRIBUTE41 ,
1377 v_PRICING_ATTRIBUTE42 ,
1378 v_PRICING_ATTRIBUTE43 ,
1379 v_PRICING_ATTRIBUTE44 ,
1380 v_PRICING_ATTRIBUTE45 ,
1381 v_PRICING_ATTRIBUTE46 ,
1382 v_PRICING_ATTRIBUTE47 ,
1383 v_PRICING_ATTRIBUTE48 ,
1384 v_PRICING_ATTRIBUTE49 ,
1385 v_PRICING_ATTRIBUTE50 ,
1386 v_PRICING_ATTRIBUTE51 ,
1387 v_PRICING_ATTRIBUTE52 ,
1388 v_PRICING_ATTRIBUTE53 ,
1389 v_PRICING_ATTRIBUTE54 ,
1390 v_PRICING_ATTRIBUTE55 ,
1391 v_PRICING_ATTRIBUTE56 ,
1392 v_PRICING_ATTRIBUTE57 ,
1393 v_PRICING_ATTRIBUTE58 ,
1394 v_PRICING_ATTRIBUTE59 ,
1395 v_PRICING_ATTRIBUTE61 ,
1396 v_PRICING_ATTRIBUTE62 ,
1397 v_PRICING_ATTRIBUTE63 ,
1398 v_PRICING_ATTRIBUTE64 ,
1399 v_PRICING_ATTRIBUTE65 ,
1400 v_PRICING_ATTRIBUTE66 ,
1401 v_PRICING_ATTRIBUTE67 ,
1402 v_PRICING_ATTRIBUTE68 ,
1403 v_PRICING_ATTRIBUTE69 ,
1404 v_PRICING_ATTRIBUTE70 ,
1405 v_PRICING_ATTRIBUTE71 ,
1406 v_PRICING_ATTRIBUTE72 ,
1407 v_PRICING_ATTRIBUTE73 ,
1408 v_PRICING_ATTRIBUTE74 ,
1409 v_PRICING_ATTRIBUTE75 ,
1410 v_PRICING_ATTRIBUTE76 ,
1411 v_PRICING_ATTRIBUTE77 ,
1412 v_PRICING_ATTRIBUTE78 ,
1413 v_PRICING_ATTRIBUTE79 ,
1414 v_PRICING_ATTRIBUTE80 ,
1415 v_PRICING_ATTRIBUTE81 ,
1416 v_PRICING_ATTRIBUTE82 ,
1417 v_PRICING_ATTRIBUTE83 ,
1418 v_PRICING_ATTRIBUTE84 ,
1419 v_PRICING_ATTRIBUTE85 ,
1420 v_PRICING_ATTRIBUTE86 ,
1421 v_PRICING_ATTRIBUTE87 ,
1422 v_PRICING_ATTRIBUTE88 ,
1423 v_PRICING_ATTRIBUTE89 ,
1424 v_PRICING_ATTRIBUTE90 ,
1425 v_PRICING_ATTRIBUTE91 ,
1426 v_PRICING_ATTRIBUTE92 ,
1427 v_PRICING_ATTRIBUTE93 ,
1428 v_PRICING_ATTRIBUTE94 ,
1429 v_PRICING_ATTRIBUTE95 ,
1430 v_PRICING_ATTRIBUTE96 ,
1431 v_PRICING_ATTRIBUTE97 ,
1432 v_PRICING_ATTRIBUTE98 ,
1433 v_PRICING_ATTRIBUTE99 ,
1434 v_PRICING_ATTRIBUTE100 ,
1435 v_PRICING_ATTRIBUTE60 ,
1436 --v_SECURITY_GROUP_ID ,
1437 --v_UPGRADED_STATUS_FLAG ,
1438 --v_ORIG_SYSTEM_REFERENCE_ID ,
1439 v_NO_CHARGE_FLAG ,
1440 v_ORG_ID ,
1441 v_ITEM_REVISION ,
1442 --v_TRANS_INV_ORGANIZATION_ID ,
1443 --v_TRANS_SUBINVENTORY ,
1444 --v_ACTIVITY_DATE ,
1445 --v_ACTIVITY_START_TIME ,
1446 --v_ACTIVITY_END_TIME ,
1447 v_GENERATED_BY_BCA_ENGINE_FLAG ,
1448 v_TRANSACTION_INVENTORY_ORG ,
1449 v_TRANSACTION_SUB_INVENTORY ,
1450 v_ACTIVITY_START_DATE_TIME ,
1451 v_ACTIVITY_END_DATE_TIME ,
1452 v_CHARGE_LINE_TYPE ,
1453 v_SHIP_TO_CONTACT_ID ,
1454 v_BILL_TO_CONTACT_ID ,
1455 v_SHIP_TO_ACCOUNT_ID ,
1456 v_INVOICE_TO_ACCOUNT_ID ,
1457 v_LIST_PRICE ,
1458 v_CONTRACT_DISCOUNT_AMOUNT ,
1459 v_BILL_TO_PARTY_ID ,
1460 v_SHIP_TO_PARTY_ID ,
1461 v_SUBMIT_RESTRICTION_MESSAGE ,
1462 v_SUBMIT_ERROR_MESSAGE ,
1463 v_LINE_SUBMITTED ,
1464 v_SUBMIT_FROM_SYSTEM
1465 --sangigup 4610625
1466 , v_contract_line_id
1467 , v_rate_type_Code
1468 --sangigup
1469
1470 LIMIT MAX_BUFFER_SIZE;
1471
1472 -- gilam: added array size for looping
1473 -- Loop through each of the arrays to get values for other variables
1474 l_Array_size := v_ESTIMATE_DETAIL_ID.Count ;
1475 If l_Array_Size = 0 Then
1476 Exit;
1477 End If;
1478
1479 --FOR j IN 1..est_det_arr.COUNT
1480 FOR j IN 1..l_Array_Size
1481
1482 LOOP
1483 SAVEPOINT CSD_COPY_ESTIMATE_DETAILS;
1484
1485 BEGIN
1486 -- This is interfaced Charge lines (type - Actual) estimate_detail_id
1487 l_old_est_detail_id := v_ESTIMATE_DETAIL_ID(j);
1488
1489 -- charges display sequence number
1490 SELECT max(line_number) + 1
1491 INTO l_line_num
1492 FROM CS_ESTIMATE_DETAILS
1493 WHERE incident_id = v_INCIDENT_ID(j);
1494
1495 l_line_num := NVL(l_line_num,1);
1496
1497 -- This is newly created Charge lines (type - Estimate) estimate_detail_id
1498 -- l_ed_id is our l_new_est_detail_id
1499 SELECT cs_estimate_details_s.nextval
1500 INTO l_new_est_detail_id
1501 FROM SYS.DUAL;
1502
1503 -- get the actual header id : only one actual header for each repair line
1504 SELECT repair_actual_id
1505 INTO l_actual_id
1506 FROM CSD_REPAIR_ACTUALS
1507 WHERE repair_line_id = v_ORIGINAL_SOURCE_ID(j);
1508
1509 -- gilam: add this query to get repair estimate line id for creating actual line
1510 -- get the repair estimate line id : to set as the source id of the new actual line
1511 SELECT repair_estimate_line_id
1512 INTO l_rep_est_line_id
1513 FROM CSD_REPAIR_ESTIMATE_LINES
1514 WHERE estimate_detail_id = l_old_est_detail_id;
1515
1516 BEGIN
1517 -- Now copy the Actual line to Estimate charge line
1518
1519 CS_ESTIMATE_DETAILS_PKG.Insert_Row(
1520 p_org_id => v_ORG_ID(j),
1521 p_incident_id => v_INCIDENT_ID(j),
1522 p_original_source_id => v_ORIGINAL_SOURCE_ID(j),
1523 p_original_source_code => v_ORIGINAL_SOURCE_CODE(j),
1524 p_source_id => v_SOURCE_ID(j),
1525 p_source_code => v_SOURCE_CODE(j),
1526 p_contract_id => v_CONTRACT_ID(j),
1527 p_coverage_id => v_COVERAGE_ID(j),
1528 p_coverage_txn_group_id => v_COVERAGE_TXN_GROUP_ID(j),
1529 p_CURRENCY_CODE => v_CURRENCY_CODE(j),
1530 p_CONVERSION_RATE => v_CONVERSION_RATE(j),
1531 p_CONVERSION_TYPE_CODE => v_CONVERSION_TYPE_CODE(j),
1532 p_CONVERSION_RATE_DATE => v_CONVERSION_RATE_DATE(j),
1533 p_invoice_to_org_id => v_INVOICE_TO_ORG_ID(j),
1534 p_ship_to_org_id => v_SHIP_TO_ORG_ID(j),
1535 p_purchase_order_num => v_PURCHASE_ORDER_NUM(j),
1536 p_order_line_id => NULL, -- changed
1537 p_line_type_id => v_LINE_TYPE_ID(j),
1538 p_LINE_CATEGORY_CODE => v_LINE_CATEGORY_CODE(j),
1539 p_price_list_header_id => v_PRICE_LIST_HEADER_ID(j), -- changed
1540 p_line_number => v_LINE_NUMBER(j),
1541 p_inventory_item_id => v_INVENTORY_ITEM_ID(j),
1542 p_item_revision => v_ITEM_REVISION(j),
1543 p_SERIAL_NUMBER => v_SERIAL_NUMBER(j),
1544 p_quantity_required => v_QUANTITY_REQUIRED(j),
1545 p_unit_of_measure_code => v_UNIT_OF_MEASURE_CODE(j),
1546 p_selling_price => v_SELLING_PRICE(j),
1547 p_after_warranty_cost => v_AFTER_WARRANTY_COST(j),
1548 p_business_process_id => v_BUSINESS_PROCESS_ID(j),
1549 p_transaction_type_id => v_TRANSACTION_TYPE_ID(j),
1550 p_customer_product_id => v_CUSTOMER_PRODUCT_ID(j),
1551 p_order_header_id => NULL, -- changed
1552 p_installed_cp_return_by_date => v_INSTALLED_CP_RETURN_BY_DATE(j),
1553 p_new_cp_return_by_date => v_NEW_CP_RETURN_BY_DATE(j),
1554 p_interface_to_oe_flag => 'N', -- changed
1555 p_rollup_flag => v_ROLLUP_FLAG(j),
1556 p_no_charge_flag => v_NO_CHARGE_FLAG(j),
1557 p_add_to_order_flag => 'N', -- changed
1558 p_return_reason_code => v_RETURN_REASON_CODE(j),
1559 p_generated_by_bca_engine_flag => NULL, -- changed
1560 p_transaction_inventory_org => v_TRANSACTION_INVENTORY_ORG(j),
1561 p_transaction_sub_inventory => v_TRANSACTION_SUB_INVENTORY(j),
1562 p_charge_line_type => 'ESTIMATE', -- changed
1563 p_ship_to_account_id => v_SHIP_TO_ACCOUNT_ID(j),
1564 p_invoice_to_account_id => v_INVOICE_TO_ACCOUNT_ID(j), -- changed
1565 p_ship_to_contact_id => v_SHIP_TO_CONTACT_ID(j),
1566 p_bill_to_contact_id => v_BILL_TO_CONTACT_ID(j),
1567 p_list_price => v_LIST_PRICE(j),
1568 p_activity_start_date_time => v_ACTIVITY_START_DATE_TIME(j),
1569 p_activity_end_date_time => v_ACTIVITY_END_DATE_TIME(j),
1570 p_contract_discount_amount => v_CONTRACT_DISCOUNT_AMOUNT(j),
1571 p_bill_to_party_id => v_BILL_TO_PARTY_ID(j),
1572 p_ship_to_party_id => v_SHIP_TO_PARTY_ID(j),
1573 p_pricing_context => v_PRICING_CONTEXT(j),
1574 p_pricing_attribute1 => v_PRICING_ATTRIBUTE1(j),
1575 p_pricing_attribute2 => v_PRICING_ATTRIBUTE2(j),
1576 p_pricing_attribute3 => v_PRICING_ATTRIBUTE3(j),
1577 p_pricing_attribute4 => v_PRICING_ATTRIBUTE4(j),
1578 p_pricing_attribute5 => v_PRICING_ATTRIBUTE5(j),
1579 p_pricing_attribute6 => v_PRICING_ATTRIBUTE6(j),
1580 p_pricing_attribute7 => v_PRICING_ATTRIBUTE7(j),
1581 p_pricing_attribute8 => v_PRICING_ATTRIBUTE8(j),
1582 p_pricing_attribute9 => v_PRICING_ATTRIBUTE9(j),
1583 p_pricing_attribute10 => v_PRICING_ATTRIBUTE10(j),
1584 p_pricing_attribute11 => v_PRICING_ATTRIBUTE11(j),
1585 p_pricing_attribute12 => v_PRICING_ATTRIBUTE12(j),
1586 p_pricing_attribute13 => v_PRICING_ATTRIBUTE13(j),
1587 p_pricing_attribute14 => v_PRICING_ATTRIBUTE14(j),
1588 p_pricing_attribute15 => v_PRICING_ATTRIBUTE15(j),
1589 p_pricing_attribute16 => v_PRICING_ATTRIBUTE16(j),
1590 p_pricing_attribute17 => v_PRICING_ATTRIBUTE17(j),
1591 p_pricing_attribute18 => v_PRICING_ATTRIBUTE18(j),
1592 p_pricing_attribute19 => v_PRICING_ATTRIBUTE19(j),
1593 p_pricing_attribute20 => v_PRICING_ATTRIBUTE20(j),
1594 p_pricing_attribute21 => v_PRICING_ATTRIBUTE21(j),
1595 p_pricing_attribute22 => v_PRICING_ATTRIBUTE22(j),
1596 p_pricing_attribute23 => v_PRICING_ATTRIBUTE23(j),
1597 p_pricing_attribute24 => v_PRICING_ATTRIBUTE24(j),
1598 p_pricing_attribute25 => v_PRICING_ATTRIBUTE25(j),
1599 p_pricing_attribute26 => v_PRICING_ATTRIBUTE26(j),
1600 p_pricing_attribute27 => v_PRICING_ATTRIBUTE27(j),
1601 p_pricing_attribute28 => v_PRICING_ATTRIBUTE28(j),
1602 p_pricing_attribute29 => v_PRICING_ATTRIBUTE29(j),
1603 p_pricing_attribute30 => v_PRICING_ATTRIBUTE30(j),
1604 p_pricing_attribute31 => v_PRICING_ATTRIBUTE31(j),
1605 p_pricing_attribute32 => v_PRICING_ATTRIBUTE32(j),
1606 p_pricing_attribute33 => v_PRICING_ATTRIBUTE33(j),
1607 p_pricing_attribute34 => v_PRICING_ATTRIBUTE34(j),
1608 p_pricing_attribute35 => v_PRICING_ATTRIBUTE35(j),
1609 p_pricing_attribute36 => v_PRICING_ATTRIBUTE36(j),
1610 p_pricing_attribute37 => v_PRICING_ATTRIBUTE37(j),
1611 p_pricing_attribute38 => v_PRICING_ATTRIBUTE38(j),
1612 p_pricing_attribute39 => v_PRICING_ATTRIBUTE39(j),
1613 p_pricing_attribute40 => v_PRICING_ATTRIBUTE40(j),
1614 p_pricing_attribute41 => v_PRICING_ATTRIBUTE41(j),
1615 p_pricing_attribute42 => v_PRICING_ATTRIBUTE42(j),
1616 p_pricing_attribute43 => v_PRICING_ATTRIBUTE43(j),
1617 p_pricing_attribute44 => v_PRICING_ATTRIBUTE44(j),
1618 p_pricing_attribute45 => v_PRICING_ATTRIBUTE45(j),
1619 p_pricing_attribute46 => v_PRICING_ATTRIBUTE46(j),
1620 p_pricing_attribute47 => v_PRICING_ATTRIBUTE47(j),
1621 p_pricing_attribute48 => v_PRICING_ATTRIBUTE48(j),
1622 p_pricing_attribute49 => v_PRICING_ATTRIBUTE49(j),
1623 p_pricing_attribute50 => v_PRICING_ATTRIBUTE50(j),
1624 p_pricing_attribute51 => v_PRICING_ATTRIBUTE51(j),
1625 p_pricing_attribute52 => v_PRICING_ATTRIBUTE52(j),
1626 p_pricing_attribute53 => v_PRICING_ATTRIBUTE53(j),
1627 p_pricing_attribute54 => v_PRICING_ATTRIBUTE54(j),
1628 p_pricing_attribute55 => v_PRICING_ATTRIBUTE55(j),
1629 p_pricing_attribute56 => v_PRICING_ATTRIBUTE56(j),
1630 p_pricing_attribute57 => v_PRICING_ATTRIBUTE57(j),
1631 p_pricing_attribute58 => v_PRICING_ATTRIBUTE58(j),
1632 p_pricing_attribute59 => v_PRICING_ATTRIBUTE59(j),
1633 p_pricing_attribute60 => v_PRICING_ATTRIBUTE60(j),
1634 p_pricing_attribute61 => v_PRICING_ATTRIBUTE61(j),
1635 p_pricing_attribute62 => v_PRICING_ATTRIBUTE62(j),
1636 p_pricing_attribute63 => v_PRICING_ATTRIBUTE63(j),
1637 p_pricing_attribute64 => v_PRICING_ATTRIBUTE64(j),
1638 p_pricing_attribute65 => v_PRICING_ATTRIBUTE65(j),
1639 p_pricing_attribute66 => v_PRICING_ATTRIBUTE66(j),
1640 p_pricing_attribute67 => v_PRICING_ATTRIBUTE67(j),
1641 p_pricing_attribute68 => v_PRICING_ATTRIBUTE68(j),
1642 p_pricing_attribute69 => v_PRICING_ATTRIBUTE69(j),
1643 p_pricing_attribute70 => v_PRICING_ATTRIBUTE70(j),
1644 p_pricing_attribute71 => v_PRICING_ATTRIBUTE71(j),
1645 p_pricing_attribute72 => v_PRICING_ATTRIBUTE72(j),
1646 p_pricing_attribute73 => v_PRICING_ATTRIBUTE73(j),
1647 p_pricing_attribute74 => v_PRICING_ATTRIBUTE74(j),
1648 p_pricing_attribute75 => v_PRICING_ATTRIBUTE75(j),
1649 p_pricing_attribute76 => v_PRICING_ATTRIBUTE76(j),
1650 p_pricing_attribute77 => v_PRICING_ATTRIBUTE77(j),
1651 p_pricing_attribute78 => v_PRICING_ATTRIBUTE78(j),
1652 p_pricing_attribute79 => v_PRICING_ATTRIBUTE79(j),
1653 p_pricing_attribute80 => v_PRICING_ATTRIBUTE80(j),
1654 p_pricing_attribute81 => v_PRICING_ATTRIBUTE81(j),
1655 p_pricing_attribute82 => v_PRICING_ATTRIBUTE82(j),
1656 p_pricing_attribute83 => v_PRICING_ATTRIBUTE83(j),
1657 p_pricing_attribute84 => v_PRICING_ATTRIBUTE84(j),
1658 p_pricing_attribute85 => v_PRICING_ATTRIBUTE85(j),
1659 p_pricing_attribute86 => v_PRICING_ATTRIBUTE86(j),
1660 p_pricing_attribute87 => v_PRICING_ATTRIBUTE87(j),
1661 p_pricing_attribute88 => v_PRICING_ATTRIBUTE88(j),
1662 p_pricing_attribute89 => v_PRICING_ATTRIBUTE89(j),
1663 p_pricing_attribute90 => v_PRICING_ATTRIBUTE90(j),
1664 p_pricing_attribute91 => v_PRICING_ATTRIBUTE91(j),
1665 p_pricing_attribute92 => v_PRICING_ATTRIBUTE92(j),
1666 p_pricing_attribute93 => v_PRICING_ATTRIBUTE93(j),
1667 p_pricing_attribute94 => v_PRICING_ATTRIBUTE94(j),
1668 p_pricing_attribute95 => v_PRICING_ATTRIBUTE95(j),
1669 p_pricing_attribute96 => v_PRICING_ATTRIBUTE96(j),
1670 p_pricing_attribute97 => v_PRICING_ATTRIBUTE97(j),
1671 p_pricing_attribute98 => v_PRICING_ATTRIBUTE98(j),
1672 p_pricing_attribute99 => v_PRICING_ATTRIBUTE99(j),
1673 p_pricing_attribute100 => v_PRICING_ATTRIBUTE100(j),
1674 p_attribute1 => v_ATTRIBUTE1(j),
1675 p_attribute2 => v_ATTRIBUTE2(j),
1676 p_attribute3 => v_ATTRIBUTE3(j),
1677 p_attribute4 => v_ATTRIBUTE4(j),
1678 p_attribute5 => v_ATTRIBUTE5(j),
1679 p_attribute6 => v_ATTRIBUTE6(j),
1680 p_attribute7 => v_ATTRIBUTE7(j),
1681 p_attribute8 => v_ATTRIBUTE8(j),
1682 p_attribute9 => v_ATTRIBUTE9(j),
1683 p_attribute10 => v_ATTRIBUTE10(j),
1684 p_attribute11 => v_ATTRIBUTE11(j),
1685 p_attribute12 => v_ATTRIBUTE12(j),
1686 p_attribute13 => v_ATTRIBUTE13(j),
1687 p_attribute14 => v_ATTRIBUTE14(j),
1688 p_attribute15 => v_ATTRIBUTE15(j),
1689 p_context => v_CONTEXT(j),
1690 p_coverage_bill_rate_id => v_COVERAGE_BILL_RATE_ID(j),
1691 p_coverage_billing_type_id => null,
1692 p_txn_billing_type_id => v_TXN_BILLING_TYPE_ID(j),
1693 p_submit_restriction_message => v_SUBMIT_RESTRICTION_MESSAGE(j),
1694 p_submit_error_message => v_SUBMIT_ERROR_MESSAGE(j),
1695 p_submit_from_system => v_SUBMIT_FROM_SYSTEM(j),
1696 p_line_submitted => null,
1697 --sangigup 4610625
1698 p_contract_line_id => null,
1699 p_rate_type_Code => null,
1700 --sangigup
1701 p_last_update_date => sysdate,
1702 --p_last_update_login => p_user_id,
1703 p_last_update_login => null, -- changed to null for create
1704 p_last_updated_by => FND_GLOBAL.USER_ID,
1705 p_creation_date => sysdate,
1706 p_created_by => FND_GLOBAL.USER_ID,
1707 p_estimate_detail_id => l_new_est_detail_id,
1708 x_object_version_number => x_object_version_number
1709 );
1710
1711 -- changed values
1712 --p_estimate_detail_id => l_ed_id,
1713 --p_interface_to_oe_flag => est_det_arr(j).interface_to_oe_flag,
1714 --p_order_header_id => est_det_arr(j).order_header_id,
1715 --p_order_line_id => est_det_arr(j).order_line_id,
1716 --p_add_to_order_flag => est_det_arr(j).add_to_order_flag,
1717 --p_charge_line_type => est_det_arr(j).charge_line_type,
1718 --p_price_list_header_id => est_det_arr(j).price_list_id,
1719 --p_inventory_item_id => est_det_arr(j).inventory_item_id_in,
1720 --p_generated_by_bca_engine_flag => est_det_arr(j).generated_by_bca_engine,
1721 --p_invoice_to_account_id => est_det_arr(j).bill_to_account_id,
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 v_error_text := substr(sqlerrm, 1, 1000)
1726 || 'CS_ESTIMATE_DETAILS_PKG.Insert_Row Failed - For Estimate Detail Id:'
1727 || v_ESTIMATE_DETAIL_ID(j);
1728 RAISE error_process;
1729 END;
1730
1731 BEGIN
1732
1733 -- Call table handler CSD_REPAIR_ACTUALS_LINES_PKG.Insert_Row to
1734 -- insert the record into CSD_REPAIR_ACTUAL_LINES
1735 -- this is Depot Actual line created from the Estimate line
1736
1737 -- gilam: clear out actual line id when creating new row
1738 x_actual_line_id := null;
1739
1740 -- gilam: changed actual source code to ESTIMATE and
1741 -- actual source id to repair estimate line id
1742 CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row(
1743 px_REPAIR_ACTUAL_LINE_ID => x_actual_line_id
1744 ,p_OBJECT_VERSION_NUMBER => 1
1745 ,p_ESTIMATE_DETAIL_ID => l_old_est_detail_id
1746 ,p_REPAIR_ACTUAL_ID => l_actual_id
1747 ,p_REPAIR_LINE_ID => v_ORIGINAL_SOURCE_ID(j)
1748 ,p_CREATED_BY => FND_GLOBAL.USER_ID
1749 ,p_CREATION_DATE => SYSDATE
1750 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
1751 ,p_LAST_UPDATE_DATE => SYSDATE
1752 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
1753 ,p_ITEM_COST => null
1754 ,p_JUSTIFICATION_NOTES => null
1755 ,p_RESOURCE_ID => null
1756 ,p_OVERRIDE_CHARGE_FLAG => null
1757 ,p_ACTUAL_SOURCE_CODE => 'ESTIMATE'
1758 ,p_ACTUAL_SOURCE_ID => l_rep_est_line_id
1759 ,p_WARRANTY_CLAIM_FLAG => null
1760 ,p_WARRANTY_NUMBER => null
1761 ,p_WARRANTY_STATUS_CODE => null
1762 ,p_REPLACED_ITEM_ID => null
1763 ,p_ATTRIBUTE_CATEGORY => null
1764 ,p_ATTRIBUTE1 => null
1765 ,p_ATTRIBUTE2 => null
1766 ,p_ATTRIBUTE3 => null
1767 ,p_ATTRIBUTE4 => null
1768 ,p_ATTRIBUTE5 => null
1769 ,p_ATTRIBUTE6 => null
1770 ,p_ATTRIBUTE7 => null
1771 ,p_ATTRIBUTE8 => null
1772 ,p_ATTRIBUTE9 => null
1773 ,p_ATTRIBUTE10 => null
1774 ,p_ATTRIBUTE11 => null
1775 ,p_ATTRIBUTE12 => null
1776 ,p_ATTRIBUTE13 => null
1777 ,p_ATTRIBUTE14 => null
1778 ,p_ATTRIBUTE15 => null
1779 ,p_LOCATOR_ID => null
1780 ,p_LOC_SEGMENT1 => null
1781 ,p_LOC_SEGMENT2 => null
1782 ,p_LOC_SEGMENT3 => null
1783 ,p_LOC_SEGMENT4 => null
1784 ,p_LOC_SEGMENT5 => null
1785 ,p_LOC_SEGMENT6 => null
1786 ,p_LOC_SEGMENT7 => null
1787 ,p_LOC_SEGMENT8 => null
1788 ,p_LOC_SEGMENT9 => null
1789 ,p_LOC_SEGMENT10 => null
1790 ,p_LOC_SEGMENT11 => null
1791 ,p_LOC_SEGMENT12 => null
1792 ,p_LOC_SEGMENT13 => null
1793 ,p_LOC_SEGMENT14 => null
1794 ,p_LOC_SEGMENT15 => null
1795 ,p_LOC_SEGMENT16 => null
1796 ,p_LOC_SEGMENT17 => null
1797 ,p_LOC_SEGMENT18 => null
1798 ,p_LOC_SEGMENT19 => null
1799 ,p_LOC_SEGMENT20 => null);
1800
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 v_error_text := substr(sqlerrm, 1, 1000)
1804 || 'CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row Failed - For Estimate Detail Id:'
1805 || l_old_est_detail_id
1806 || ' Loop for Estimate Detail Id: '
1807 || v_ESTIMATE_DETAIL_ID(j);
1808 RAISE error_process;
1809 END;
1810
1811 BEGIN
1812
1813 -- Update the Depot Estimate line to point to new charge line (type - Estimate)
1814 -- where it is pointing to the old charge line (type - Actual)
1815 UPDATE CSD_REPAIR_ESTIMATE_LINES
1816 SET ESTIMATE_DETAIL_ID = l_new_est_detail_id
1817 WHERE ESTIMATE_DETAIL_ID = l_old_est_detail_id;
1818
1819 EXCEPTION
1820 WHEN OTHERS THEN
1821 v_error_text := substr(sqlerrm, 1, 1000)
1822 || 'UPDATE CSD_REPAIR_ESTIMATE_LINES Failed - To update Estimate Detail Id to :'
1823 || l_new_est_detail_id
1824 || ' from Estimate Detail Id: '
1825 || l_old_est_detail_id;
1826 RAISE error_process;
1827 END;
1828
1829 IF SQL%NOTFOUND
1830 THEN
1831 RAISE error_process;
1832 END IF;
1833
1834 EXCEPTION
1835 WHEN error_process THEN
1836 ROLLBACK TO CSD_COPY_ESTIMATE_DETAILS;
1837 -- check if v_error_text is null, if yes then set the value
1838 -- else the value is propagated from the error sources above
1839 if (v_error_text is null) then
1840 v_error_text := substr(sqlerrm, 1, 1000)
1841 || 'Actual Estimate Detail Id:'
1842 || v_ESTIMATE_DETAIL_ID(j);
1843 end if;
1844
1845 INSERT INTO CSD_UPG_ERRORS
1846 (ORIG_SYSTEM_REFERENCE,
1847 TARGET_SYSTEM_REFERENCE,
1848 ORIG_SYSTEM_REFERENCE_ID,
1849 UPGRADE_DATETIME,
1850 ERROR_MESSAGE,
1851 MIGRATION_PHASE)
1852 VALUES ('CSD_REPAIR_ESTIMATE_LINES',
1853 'CS_ESTIMATE_DETAILS',
1854 v_ESTIMATE_DETAIL_ID(j),
1855 sysdate,
1856 v_error_text,
1857 '11.5.10');
1858
1859 commit;
1860
1861 raise_application_error( -20000, v_error_text);
1862
1863 END;
1864 END LOOP;
1865 COMMIT;
1866 EXIT WHEN get_charge_est_details%NOTFOUND;
1867 END LOOP;
1868
1869 /* gilam: commented out the varray rowtype code
1870 -- This is interfaced Charge lines (type - Actual) estimate_detail_id
1871 l_old_est_detail_id := est_det_arr(j).estimate_detail_id;
1872
1873 -- charges display sequence number
1874 SELECT max(line_number) + 1
1875 INTO l_line_num
1876 FROM CS_ESTIMATE_DETAILS
1877 WHERE incident_id = est_det_arr(j).incident_id;
1878
1879 l_line_num := NVL(l_line_num,1);
1880
1881 -- This is newly created Charge lines (type - Estimate) estimate_detail_id
1882 -- l_ed_id is our l_new_est_detail_id
1883 SELECT cs_estimate_details_s.nextval
1884 INTO l_new_est_detail_id
1885 FROM SYS.DUAL;
1886
1887 -- get the actual header id : only one actual header for each repair line
1888 SELECT repair_actual_id
1889 INTO l_actual_id
1890 FROM CSD_REPAIR_ACTUALS
1891 WHERE repair_line_id = est_det_arr(j).original_source_id;
1892
1893 -- gilam: add this query to get repair estimate line id for creating actual line
1894 -- get the repair estimate line id : to set as the source id of the new actual line
1895 SELECT repair_estimate_line_id
1896 INTO l_rep_est_line_id
1897 FROM CSD_REPAIR_ESTIMATE_LINES
1898 WHERE estimate_detail_id = l_old_est_detail_id;
1899
1900 BEGIN
1901 -- Now copy the Actual line to Estimate charge line
1902 CS_ESTIMATE_DETAILS_PKG.Insert_Row(
1903 p_org_id => est_det_arr(j).org_id,
1904 p_incident_id => est_det_arr(j).incident_id,
1905 p_original_source_id => est_det_arr(j).original_source_id,
1906 p_original_source_code => est_det_arr(j).original_source_code,
1907 p_source_id => est_det_arr(j).source_id,
1908 p_source_code => est_det_arr(j).source_code,
1909 p_contract_id => est_det_arr(j).contract_id,
1910 p_coverage_id => est_det_arr(j).coverage_id,
1911 p_coverage_txn_group_id => est_det_arr(j).coverage_txn_group_id,
1912 p_CURRENCY_CODE => est_det_arr(j).currency_code,
1913 p_CONVERSION_RATE => est_det_arr(j).conversion_rate,
1914 p_CONVERSION_TYPE_CODE => est_det_arr(j).conversion_type_code,
1915 p_CONVERSION_RATE_DATE => est_det_arr(j).conversion_rate_date,
1916 p_invoice_to_org_id => est_det_arr(j).invoice_to_org_id,
1917 p_ship_to_org_id => est_det_arr(j).ship_to_org_id,
1918 p_purchase_order_num => est_det_arr(j).purchase_order_num,
1919 p_order_line_id => NULL, -- changed
1920 p_line_type_id => est_det_arr(j).line_type_id,
1921 p_LINE_CATEGORY_CODE => est_det_arr(j).LINE_CATEGORY_CODE,
1922 p_price_list_header_id => est_det_arr(j).price_list_header_id, -- changed
1923 p_line_number => l_line_num,
1924 p_inventory_item_id => est_det_arr(j).inventory_item_id,
1925 p_item_revision => est_det_arr(j).item_revision,
1926 p_SERIAL_NUMBER => est_det_arr(j).SERIAL_NUMBER,
1927 p_quantity_required => est_det_arr(j).quantity_required,
1928 p_unit_of_measure_code => est_det_arr(j).unit_of_measure_code,
1929 p_selling_price => est_det_arr(j).selling_price,
1930 p_after_warranty_cost => est_det_arr(j).after_warranty_cost,
1931 p_business_process_id => est_det_arr(j).business_process_id,
1932 p_transaction_type_id => est_det_arr(j).transaction_type_id,
1933 p_customer_product_id => est_det_arr(j).customer_product_id,
1934 p_order_header_id => NULL, -- changed
1935 p_installed_cp_return_by_date => est_det_arr(j).installed_cp_return_by_date,
1936 p_new_cp_return_by_date => est_det_arr(j).new_cp_return_by_date,
1937 p_interface_to_oe_flag => 'N', -- changed
1938 p_rollup_flag => est_det_arr(j).rollup_flag,
1939 p_no_charge_flag => est_det_arr(j).no_charge_flag,
1940 p_add_to_order_flag => 'N', -- changed
1941 p_return_reason_code => est_det_arr(j).return_reason_code,
1942 p_generated_by_bca_engine_flag => NULL, -- changed
1943 p_transaction_inventory_org => est_det_arr(j).transaction_inventory_org,
1944 p_transaction_sub_inventory => est_det_arr(j).transaction_sub_inventory,
1945 p_charge_line_type => 'ESTIMATE', -- changed
1946 p_ship_to_account_id => est_det_arr(j).ship_to_account_id,
1947 p_invoice_to_account_id => est_det_arr(j).bill_to_party_id, -- changed
1948 p_ship_to_contact_id => est_det_arr(j).ship_to_contact_id,
1949 p_bill_to_contact_id => est_det_arr(j).bill_to_contact_id,
1950 p_list_price => est_det_arr(j).list_price,
1951 p_activity_start_date_time => est_det_arr(j).activity_start_time,
1952 p_activity_end_date_time => est_det_arr(j).activity_end_time,
1953 p_contract_discount_amount => est_det_arr(j).contract_discount_amount,
1954 p_bill_to_party_id => est_det_arr(j).bill_to_party_id,
1955 p_ship_to_party_id => est_det_arr(j).ship_to_party_id,
1956 p_pricing_context => est_det_arr(j).pricing_context,
1957 p_pricing_attribute1 => est_det_arr(j).pricing_attribute1,
1958 p_pricing_attribute2 => est_det_arr(j).pricing_attribute2,
1959 p_pricing_attribute3 => est_det_arr(j).pricing_attribute3,
1960 p_pricing_attribute4 => est_det_arr(j).pricing_attribute4,
1961 p_pricing_attribute5 => est_det_arr(j).pricing_attribute5,
1962 p_pricing_attribute6 => est_det_arr(j).pricing_attribute6,
1963 p_pricing_attribute7 => est_det_arr(j).pricing_attribute7,
1964 p_pricing_attribute8 => est_det_arr(j).pricing_attribute8,
1965 p_pricing_attribute9 => est_det_arr(j).pricing_attribute9,
1966 p_pricing_attribute10 => est_det_arr(j).pricing_attribute10,
1967 p_pricing_attribute11 => est_det_arr(j).pricing_attribute11,
1968 p_pricing_attribute12 => est_det_arr(j).pricing_attribute12,
1969 p_pricing_attribute13 => est_det_arr(j).pricing_attribute13,
1970 p_pricing_attribute14 => est_det_arr(j).pricing_attribute14,
1971 p_pricing_attribute15 => est_det_arr(j).pricing_attribute15,
1972 p_pricing_attribute16 => est_det_arr(j).pricing_attribute16,
1973 p_pricing_attribute17 => est_det_arr(j).pricing_attribute17,
1974 p_pricing_attribute18 => est_det_arr(j).pricing_attribute18,
1975 p_pricing_attribute19 => est_det_arr(j).pricing_attribute19,
1976 p_pricing_attribute20 => est_det_arr(j).pricing_attribute20,
1977 p_pricing_attribute21 => est_det_arr(j).pricing_attribute21,
1978 p_pricing_attribute22 => est_det_arr(j).pricing_attribute22,
1979 p_pricing_attribute23 => est_det_arr(j).pricing_attribute23,
1980 p_pricing_attribute24 => est_det_arr(j).pricing_attribute24,
1981 p_pricing_attribute25 => est_det_arr(j).pricing_attribute25,
1982 p_pricing_attribute26 => est_det_arr(j).pricing_attribute26,
1983 p_pricing_attribute27 => est_det_arr(j).pricing_attribute27,
1984 p_pricing_attribute28 => est_det_arr(j).pricing_attribute28,
1985 p_pricing_attribute29 => est_det_arr(j).pricing_attribute29,
1986 p_pricing_attribute30 => est_det_arr(j).pricing_attribute30,
1987 p_pricing_attribute31 => est_det_arr(j).pricing_attribute31,
1988 p_pricing_attribute32 => est_det_arr(j).pricing_attribute32,
1989 p_pricing_attribute33 => est_det_arr(j).pricing_attribute33,
1990 p_pricing_attribute34 => est_det_arr(j).pricing_attribute34,
1991 p_pricing_attribute35 => est_det_arr(j).pricing_attribute35,
1992 p_pricing_attribute36 => est_det_arr(j).pricing_attribute36,
1993 p_pricing_attribute37 => est_det_arr(j).pricing_attribute37,
1994 p_pricing_attribute38 => est_det_arr(j).pricing_attribute38,
1995 p_pricing_attribute39 => est_det_arr(j).pricing_attribute39,
1996 p_pricing_attribute40 => est_det_arr(j).pricing_attribute40,
1997 p_pricing_attribute41 => est_det_arr(j).pricing_attribute41,
1998 p_pricing_attribute42 => est_det_arr(j).pricing_attribute42,
1999 p_pricing_attribute43 => est_det_arr(j).pricing_attribute43,
2000 p_pricing_attribute44 => est_det_arr(j).pricing_attribute44,
2001 p_pricing_attribute45 => est_det_arr(j).pricing_attribute45,
2002 p_pricing_attribute46 => est_det_arr(j).pricing_attribute46,
2003 p_pricing_attribute47 => est_det_arr(j).pricing_attribute47,
2004 p_pricing_attribute48 => est_det_arr(j).pricing_attribute48,
2005 p_pricing_attribute49 => est_det_arr(j).pricing_attribute49,
2006 p_pricing_attribute50 => est_det_arr(j).pricing_attribute50,
2007 p_pricing_attribute51 => est_det_arr(j).pricing_attribute51,
2008 p_pricing_attribute52 => est_det_arr(j).pricing_attribute52,
2009 p_pricing_attribute53 => est_det_arr(j).pricing_attribute53,
2010 p_pricing_attribute54 => est_det_arr(j).pricing_attribute54,
2011 p_pricing_attribute55 => est_det_arr(j).pricing_attribute55,
2012 p_pricing_attribute56 => est_det_arr(j).pricing_attribute56,
2013 p_pricing_attribute57 => est_det_arr(j).pricing_attribute57,
2014 p_pricing_attribute58 => est_det_arr(j).pricing_attribute58,
2015 p_pricing_attribute59 => est_det_arr(j).pricing_attribute59,
2016 p_pricing_attribute60 => est_det_arr(j).pricing_attribute60,
2017 p_pricing_attribute61 => est_det_arr(j).pricing_attribute61,
2018 p_pricing_attribute62 => est_det_arr(j).pricing_attribute62,
2019 p_pricing_attribute63 => est_det_arr(j).pricing_attribute63,
2020 p_pricing_attribute64 => est_det_arr(j).pricing_attribute64,
2021 p_pricing_attribute65 => est_det_arr(j).pricing_attribute65,
2022 p_pricing_attribute66 => est_det_arr(j).pricing_attribute66,
2023 p_pricing_attribute67 => est_det_arr(j).pricing_attribute67,
2024 p_pricing_attribute68 => est_det_arr(j).pricing_attribute68,
2025 p_pricing_attribute69 => est_det_arr(j).pricing_attribute69,
2026 p_pricing_attribute70 => est_det_arr(j).pricing_attribute70,
2027 p_pricing_attribute71 => est_det_arr(j).pricing_attribute71,
2028 p_pricing_attribute72 => est_det_arr(j).pricing_attribute72,
2029 p_pricing_attribute73 => est_det_arr(j).pricing_attribute73,
2030 p_pricing_attribute74 => est_det_arr(j).pricing_attribute74,
2031 p_pricing_attribute75 => est_det_arr(j).pricing_attribute75,
2032 p_pricing_attribute76 => est_det_arr(j).pricing_attribute76,
2033 p_pricing_attribute77 => est_det_arr(j).pricing_attribute77,
2034 p_pricing_attribute78 => est_det_arr(j).pricing_attribute78,
2035 p_pricing_attribute79 => est_det_arr(j).pricing_attribute79,
2036 p_pricing_attribute80 => est_det_arr(j).pricing_attribute80,
2037 p_pricing_attribute81 => est_det_arr(j).pricing_attribute81,
2038 p_pricing_attribute82 => est_det_arr(j).pricing_attribute82,
2039 p_pricing_attribute83 => est_det_arr(j).pricing_attribute83,
2040 p_pricing_attribute84 => est_det_arr(j).pricing_attribute84,
2041 p_pricing_attribute85 => est_det_arr(j).pricing_attribute85,
2042 p_pricing_attribute86 => est_det_arr(j).pricing_attribute86,
2043 p_pricing_attribute87 => est_det_arr(j).pricing_attribute87,
2044 p_pricing_attribute88 => est_det_arr(j).pricing_attribute88,
2045 p_pricing_attribute89 => est_det_arr(j).pricing_attribute89,
2046 p_pricing_attribute90 => est_det_arr(j).pricing_attribute90,
2047 p_pricing_attribute91 => est_det_arr(j).pricing_attribute91,
2048 p_pricing_attribute92 => est_det_arr(j).pricing_attribute92,
2049 p_pricing_attribute93 => est_det_arr(j).pricing_attribute93,
2050 p_pricing_attribute94 => est_det_arr(j).pricing_attribute94,
2051 p_pricing_attribute95 => est_det_arr(j).pricing_attribute95,
2052 p_pricing_attribute96 => est_det_arr(j).pricing_attribute96,
2053 p_pricing_attribute97 => est_det_arr(j).pricing_attribute97,
2054 p_pricing_attribute98 => est_det_arr(j).pricing_attribute98,
2055 p_pricing_attribute99 => est_det_arr(j).pricing_attribute99,
2056 p_pricing_attribute100 => est_det_arr(j).pricing_attribute100,
2057 p_attribute1 => est_det_arr(j).attribute1,
2058 p_attribute2 => est_det_arr(j).attribute2,
2059 p_attribute3 => est_det_arr(j).attribute3,
2060 p_attribute4 => est_det_arr(j).attribute4,
2061 p_attribute5 => est_det_arr(j).attribute5,
2062 p_attribute6 => est_det_arr(j).attribute6,
2063 p_attribute7 => est_det_arr(j).attribute7,
2064 p_attribute8 => est_det_arr(j).attribute8,
2065 p_attribute9 => est_det_arr(j).attribute9,
2066 p_attribute10 => est_det_arr(j).attribute10,
2067 p_attribute11 => est_det_arr(j).attribute11,
2068 p_attribute12 => est_det_arr(j).attribute12,
2069 p_attribute13 => est_det_arr(j).attribute13,
2070 p_attribute14 => est_det_arr(j).attribute14,
2071 p_attribute15 => est_det_arr(j).attribute15,
2072 p_context => est_det_arr(j).context,
2073 p_coverage_bill_rate_id => est_det_arr(j).coverage_bill_rate_id,
2074 p_coverage_billing_type_id => null,
2075 p_txn_billing_type_id => est_det_arr(j).txn_billing_type_id,
2076 p_submit_restriction_message => est_det_arr(j).submit_restriction_message,
2077 p_submit_error_message => est_det_arr(j).submit_error_message,
2078 p_submit_from_system => est_det_arr(j).submit_from_system,
2079 p_line_submitted => null,
2080 p_last_update_date => sysdate,
2081 --p_last_update_login => p_user_id,
2082 p_last_update_login => null, -- changed to null for create
2083 p_last_updated_by => FND_GLOBAL.USER_ID,
2084 p_creation_date => sysdate,
2085 p_created_by => FND_GLOBAL.USER_ID,
2086 p_estimate_detail_id => l_new_est_detail_id,
2087 x_object_version_number => x_object_version_number );
2088
2089 -- changed values
2090 --p_estimate_detail_id => l_ed_id,
2091 --p_interface_to_oe_flag => est_det_arr(j).interface_to_oe_flag,
2092 --p_order_header_id => est_det_arr(j).order_header_id,
2093 --p_order_line_id => est_det_arr(j).order_line_id,
2094 --p_add_to_order_flag => est_det_arr(j).add_to_order_flag,
2095 --p_charge_line_type => est_det_arr(j).charge_line_type,
2096 --p_price_list_header_id => est_det_arr(j).price_list_id,
2097 --p_inventory_item_id => est_det_arr(j).inventory_item_id_in,
2098 --p_generated_by_bca_engine_flag => est_det_arr(j).generated_by_bca_engine,
2099 --p_invoice_to_account_id => est_det_arr(j).bill_to_account_id,
2100
2101 EXCEPTION
2102 WHEN OTHERS THEN
2103 v_error_text := substr(sqlerrm, 1, 1000)
2104 || 'CS_ESTIMATE_DETAILS_PKG.Insert_Row Failed - For Estimate Detail Id:'
2105 || est_det_arr(j).estimate_detail_id;
2106 RAISE error_process;
2107 END;
2108
2109 BEGIN
2110
2111 -- Call table handler CSD_REPAIR_ACTUALS_LINES_PKG.Insert_Row to
2112 -- insert the record into CSD_REPAIR_ACTUAL_LINES
2113 -- this is Depot Actual line created from the Estimate line
2114
2115 -- gilam: clear out actual line id when creating new row
2116 x_actual_line_id := null;
2117
2118 -- gilam: changed actual source code to ESTIMATE and
2119 -- actual source id to repair estimate line id
2120 CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row(
2121 px_REPAIR_ACTUAL_LINE_ID => x_actual_line_id
2122 ,p_OBJECT_VERSION_NUMBER => 1
2123 ,p_ESTIMATE_DETAIL_ID => l_old_est_detail_id
2124 ,p_REPAIR_ACTUAL_ID => l_actual_id
2125 ,p_REPAIR_LINE_ID => est_det_arr(j).original_source_id
2126 ,p_CREATED_BY => FND_GLOBAL.USER_ID
2127 ,p_CREATION_DATE => SYSDATE
2128 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
2129 ,p_LAST_UPDATE_DATE => SYSDATE
2130 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
2131 ,p_ITEM_COST => null
2132 ,p_JUSTIFICATION_NOTES => null
2133 ,p_RESOURCE_ID => null
2134 ,p_OVERRIDE_CHARGE_FLAG => null
2135 ,p_ACTUAL_SOURCE_CODE => 'ESTIMATE'
2136 ,p_ACTUAL_SOURCE_ID => l_rep_est_line_id
2137 ,p_WARRANTY_CLAIM_FLAG => null
2138 ,p_WARRANTY_NUMBER => null
2139 ,p_WARRANTY_STATUS_CODE => null
2140 ,p_REPLACED_ITEM_ID => null
2141 ,p_ATTRIBUTE_CATEGORY => null
2142 ,p_ATTRIBUTE1 => null
2143 ,p_ATTRIBUTE2 => null
2144 ,p_ATTRIBUTE3 => null
2145 ,p_ATTRIBUTE4 => null
2146 ,p_ATTRIBUTE5 => null
2147 ,p_ATTRIBUTE6 => null
2148 ,p_ATTRIBUTE7 => null
2149 ,p_ATTRIBUTE8 => null
2150 ,p_ATTRIBUTE9 => null
2151 ,p_ATTRIBUTE10 => null
2152 ,p_ATTRIBUTE11 => null
2153 ,p_ATTRIBUTE12 => null
2154 ,p_ATTRIBUTE13 => null
2155 ,p_ATTRIBUTE14 => null
2156 ,p_ATTRIBUTE15 => null
2157 ,p_LOCATOR_ID => null
2158 ,p_LOC_SEGMENT1 => null
2159 ,p_LOC_SEGMENT2 => null
2160 ,p_LOC_SEGMENT3 => null
2161 ,p_LOC_SEGMENT4 => null
2162 ,p_LOC_SEGMENT5 => null
2163 ,p_LOC_SEGMENT6 => null
2164 ,p_LOC_SEGMENT7 => null
2165 ,p_LOC_SEGMENT8 => null
2166 ,p_LOC_SEGMENT9 => null
2167 ,p_LOC_SEGMENT10 => null
2168 ,p_LOC_SEGMENT11 => null
2169 ,p_LOC_SEGMENT12 => null
2170 ,p_LOC_SEGMENT13 => null
2171 ,p_LOC_SEGMENT14 => null
2172 ,p_LOC_SEGMENT15 => null
2173 ,p_LOC_SEGMENT16 => null
2174 ,p_LOC_SEGMENT17 => null
2175 ,p_LOC_SEGMENT18 => null
2176 ,p_LOC_SEGMENT19 => null
2177 ,p_LOC_SEGMENT20 => null);
2178
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 v_error_text := substr(sqlerrm, 1, 1000)
2182 || 'CSD_REPAIR_ACTUAL_LINES_PKG.Insert_Row Failed - For Estimate Detail Id:'
2183 || l_old_est_detail_id
2184 || ' Loop for Estimate Detail Id: '
2185 || est_det_arr(j).estimate_detail_id;
2186 RAISE error_process;
2187 END;
2188
2189 BEGIN
2190
2191 -- Update the Depot Estimate line to point to new charge line (type - Estimate)
2192 -- where it is pointing to the old charge line (typa - Actual)
2193 UPDATE CSD_REPAIR_ESTIMATE_LINES
2194 SET ESTIMATE_DETAIL_ID = l_new_est_detail_id
2195 WHERE ESTIMATE_DETAIL_ID = l_old_est_detail_id;
2196
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 v_error_text := substr(sqlerrm, 1, 1000)
2200 || 'UPDATE CSD_REPAIR_ESTIMATE_LINES Failed - To update Estimate Detail Id to :'
2201 || l_new_est_detail_id
2202 || ' from Estimate Detail Id: '
2203 || l_old_est_detail_id;
2204 RAISE error_process;
2205 END;
2206
2207 IF SQL%NOTFOUND
2208 THEN
2209 RAISE error_process;
2210 END IF;
2211
2212 EXCEPTION
2213 WHEN error_process THEN
2214 ROLLBACK TO CSD_COPY_ESTIMATE_DETAILS;
2215 -- check if v_error_text is null, if yes then set the value
2216 -- else the value is propagated from the error sources above
2217 if (v_error_text is null) then
2218 v_error_text := substr(sqlerrm, 1, 1000)
2219 || 'Actual Estimate Detail Id:'
2220 || est_det_arr(j).estimate_detail_id;
2221 end if;
2222
2223 INSERT INTO CSD_UPG_ERRORS
2224 (ORIG_SYSTEM_REFERENCE,
2225 TARGET_SYSTEM_REFERENCE,
2226 ORIG_SYSTEM_REFERENCE_ID,
2227 UPGRADE_DATETIME,
2228 ERROR_MESSAGE,
2229 MIGRATION_PHASE)
2230 VALUES ('CSD_REPAIR_ESTIMATE_LINES',
2231 'CS_ESTIMATE_DETAILS',
2232 est_det_arr(j).estimate_detail_id,
2233 sysdate,
2234 v_error_text,
2235 '11.5.10');
2236
2237 END;
2238 END LOOP;
2239 COMMIT;
2240 EXIT WHEN get_charge_est_details%NOTFOUND;
2241 END LOOP;
2242 */
2243
2244 IF get_charge_est_details%ISOPEN
2245 THEN
2246 CLOSE get_charge_est_details;
2247 END IF;
2248 COMMIT;
2249 END csd_acttoest_charge_line_mig3;
2250
2251 /*-------------------------------------------------------------------------------*/
2252
2253 /* procedure name: CSD_REPAIR_JOB_XREF_MIG3 */
2254
2255 /* description : procedure for migrating CSD_REPAIR_JOB_XREF table data */
2256
2257 /* from 11.5.9 to 11.5.10 */
2258
2259 /* */
2260
2261 /*-------------------------------------------------------------------------------*/
2262
2263 PROCEDURE csd_repair_job_xref_mig3(p_slab_number IN NUMBER)
2264 IS
2265
2266 TYPE NumTabType IS VARRAY(1000) OF NUMBER;
2267 repair_job_xref_id_mig NumTabType;
2268
2269 TYPE RowidTabType IS VARRAY(1000) OF VARCHAR2(30);
2270 rowid_mig RowidTabtype;
2271 v_min NUMBER;
2272 v_max NUMBER;
2273 v_error_text VARCHAR2(2000);
2274 MAX_BUFFER_SIZE NUMBER := 500;
2275 error_process EXCEPTION;
2276
2277
2278 -- Cursor to get all rows where wip_entity_id has the same value
2279 -- as the group_id
2280
2281 CURSOR get_job_name_id_rows_cursor (p_start number, p_end number)
2282 IS
2283 SELECT repair_job_xref_id, rowid
2284 FROM csd_repair_job_xref
2285 WHERE wip_entity_id = group_id AND repair_job_xref_id >= p_start
2286 AND repair_job_xref_id <= p_end;
2287
2288
2289 -- Cursor to get all rows where source_type_code is NULL
2290
2291 CURSOR get_source_type_rows_cursor (p_start number, p_end number)
2292 IS
2293 SELECT repair_job_xref_id, rowid
2294 FROM csd_repair_job_xref
2295 WHERE source_type_code IS NULL AND repair_job_xref_id >= p_start
2296 AND repair_job_xref_id <= p_end;
2297
2298 BEGIN
2299
2300 -- Get the Slab Number for the table
2301
2302 BEGIN
2303 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIR_JOB_XREF',
2304 'CSD',
2305 p_slab_number,
2306 v_min,
2307 v_max);
2308
2309 IF v_min IS NULL
2310 THEN
2311 RETURN;
2312 END IF;
2313
2314 END;
2315
2316 -- Migration code for Job_name and WIP_entity_id in CSD_REPAIR_JOB_XREF
2317
2318 OPEN get_job_name_id_rows_cursor (v_min, v_max);
2319
2320 LOOP
2321
2322 -- Get all rows where wip_entity_id has the same value
2323 -- as the group_id
2324
2325 FETCH get_job_name_id_rows_cursor BULK COLLECT INTO repair_job_xref_id_mig,
2326 rowid_mig LIMIT MAX_BUFFER_SIZE;
2327 FOR j IN 1..repair_job_xref_id_mig.COUNT
2328 LOOP
2329 SAVEPOINT CSD_REPAIR_JOB_XREF;
2330
2331 BEGIN
2332
2333 -- Update wip_entity_id to NULL and
2334 -- job name to CSD||group_id
2335
2336 UPDATE csd_repair_job_xref
2337 SET wip_entity_id = NULL,
2338 job_name = 'CSD' || group_id
2339 WHERE rowid = rowid_mig(j);
2340
2341 IF SQL%NOTFOUND
2342 THEN
2343 RAISE error_process;
2344 END IF;
2345
2346 EXCEPTION
2347 WHEN error_process THEN
2348 ROLLBACK TO CSD_REPAIR_JOB_XREF;
2349 v_error_text := substr(sqlerrm, 1, 1000)
2350 || 'Repair Job Xref Id:'
2351 || repair_job_xref_id_mig(j);
2352
2353 INSERT INTO CSD_UPG_ERRORS
2354 (ORIG_SYSTEM_REFERENCE,
2355 TARGET_SYSTEM_REFERENCE,
2356 ORIG_SYSTEM_REFERENCE_ID,
2357 UPGRADE_DATETIME,
2358 ERROR_MESSAGE,
2359 MIGRATION_PHASE)
2360 VALUES ('CSD_REPAIR_JOB_XREF',
2361 'CSD_REPAIR_JOB_XREF',
2362 repair_job_xref_id_mig(j),
2363 sysdate,
2364 v_error_text,
2365 '11.5.10');
2366
2367 commit;
2368
2369 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_JOB_XREF table data: Error while updating csd_repair_job_xref. '|| v_error_text);
2370
2371 END;
2372 END LOOP;
2373 COMMIT;
2374 EXIT WHEN get_job_name_id_rows_cursor%NOTFOUND;
2375 END LOOP;
2376
2377 IF get_job_name_id_rows_cursor%ISOPEN
2378 THEN
2379 CLOSE get_job_name_id_rows_cursor;
2380 END IF;
2381
2382 -- Migration code for SOURCE_TYPE_CODE in CSD_REPAIR_JOB_XREF
2383
2384 OPEN get_source_type_rows_cursor (v_min, v_max);
2385
2386 LOOP
2387
2388 -- Get all rows where source_type_code is NULL
2389
2390 FETCH get_source_type_rows_cursor BULK COLLECT INTO repair_job_xref_id_mig,
2391 rowid_mig LIMIT MAX_BUFFER_SIZE;
2392 FOR j IN 1..repair_job_xref_id_mig.COUNT
2393 LOOP
2394 SAVEPOINT CSD_REPAIR_JOB_XREF;
2395
2396 BEGIN
2397
2398 -- Update source_type_code to MANUAL
2399
2400 UPDATE csd_repair_job_xref
2401 SET source_type_code = 'MANUAL'
2402 WHERE rowid = rowid_mig(j);
2403
2404 IF SQL%NOTFOUND
2405 THEN
2406 RAISE error_process;
2407 END IF;
2408
2409 EXCEPTION
2410 WHEN error_process THEN
2411 ROLLBACK TO CSD_REPAIR_JOB_XREF;
2412 v_error_text := substr(sqlerrm, 1, 1000)
2413 || 'Repair Job Xref Id:'
2414 || repair_job_xref_id_mig(j);
2415
2416 INSERT INTO CSD_UPG_ERRORS
2417 (ORIG_SYSTEM_REFERENCE,
2418 TARGET_SYSTEM_REFERENCE,
2419 ORIG_SYSTEM_REFERENCE_ID,
2420 UPGRADE_DATETIME,
2421 ERROR_MESSAGE,
2422 MIGRATION_PHASE)
2423 VALUES ('CSD_REPAIR_JOB_XREF',
2424 'CSD_REPAIR_JOB_XREF',
2425 repair_job_xref_id_mig(j),
2426 sysdate,
2427 v_error_text,
2428 '11.5.10');
2429
2430 commit;
2431
2432 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_JOB_XREF table data: Error while updating csd_repair_job_xref. '|| v_error_text);
2433
2434 END;
2435 END LOOP;
2436 COMMIT;
2437 EXIT WHEN get_source_type_rows_cursor%NOTFOUND;
2438 END LOOP;
2439
2440 IF get_job_name_id_rows_cursor%ISOPEN
2441 THEN
2442 CLOSE get_source_type_rows_cursor;
2443 END IF;
2444 COMMIT;
2445 END csd_repair_job_xref_mig3;
2446
2447
2448 /*-------------------------------------------------------------------------------*/
2449 /* procedure name: CSD_REPAIR_TYPE_MIG3 */
2450 /* description : procedure for migrating repair_type_ref table data */
2451 /* from 11.5.9 to 11.5.10 */
2452 /* */
2453 /* Repair Types. - In the 11.5.10, we will changed the repair type ref for */
2454 /* the seeeded repair type - "Walk-In Repair" to "Repair and Return", */
2455 /* and the repair type ref for the seed repair type - */
2456 /* "Walk-In Repair with Loaner" to "Loaner, Repair and Return". */
2457 /*-------------------------------------------------------------------------------*/
2458
2459 PROCEDURE csd_repair_type_mig3
2460 IS
2461
2462 BEGIN
2463
2464 BEGIN
2465
2466 Update csd_repair_types_b set repair_type_ref = 'RR' where repair_type_ref = 'WR';
2467 /*
2468 EXCEPTION
2469 WHEN error_process THEN
2470 ROLLBACK TO CSD_MASS_RO_SN_ERRORS;
2471 v_error_text := substr(sqlerrm, 1, 1000)
2472 || 'Not able to update Repair type from RR to WR';
2473
2474 INSERT INTO CSD_UPG_ERRORS
2475 (ORIG_SYSTEM_REFERENCE,
2476 TARGET_SYSTEM_REFERENCE,
2477 ORIG_SYSTEM_REFERENCE_ID,
2478 UPGRADE_DATETIME,
2479 ERROR_MESSAGE,
2480 MIGRATION_PHASE)
2481 VALUES ('CSD_REPAIR_TYPE_ERROR',
2482 'CSD_REPAIR_TYPE_UPDATE',
2483 null,
2484 sysdate,
2485 v_error_text,
2486 '11.5.10');
2487 */
2488 END;
2489
2490 BEGIN
2491 Update csd_repair_types_b set repair_type_ref = 'ARR' where repair_type_ref = 'WRL';
2492 /*
2493 EXCEPTION
2494 WHEN error_process THEN
2495 ROLLBACK TO CSD_MASS_RO_SN_ERRORS;
2496 v_error_text := substr(sqlerrm, 1, 1000)
2497 || 'Not able to update Repair type from ARR to WRL';
2498
2499 INSERT INTO CSD_UPG_ERRORS
2500 (ORIG_SYSTEM_REFERENCE,
2501 TARGET_SYSTEM_REFERENCE,
2502 ORIG_SYSTEM_REFERENCE_ID,
2503 UPGRADE_DATETIME,
2504 ERROR_MESSAGE,
2505 MIGRATION_PHASE)
2506 VALUES ('CSD_REPAIR_TYPE_ERROR',
2507 'CSD_REPAIR_TYPE_UPDATE',
2508 null,
2509 sysdate,
2510 v_error_text,
2511 '11.5.10');
2512 */
2513 END;
2514
2515 COMMIT;
2516
2517 end csd_repair_type_mig3;
2518
2519
2520 /*-------------------------------------------------------------------------------*/
2521 /* procedure name: CSD_COST_DATA_MIG3 */
2522 /* description : procedure for migrating csd_repair_estimate_lines */
2523 /* table cost data */
2524 /* from 11.5.9 to 11.5.10 */
2525 /* */
2526 /* If item_cost is not null and in differnt currency from the estimate line, */
2527 /* we convert it to charges currency and stamp it back on the table. */
2528 /*-------------------------------------------------------------------------------*/
2529 Procedure CSD_Cost_data_mig3(p_slab_number IN NUMBER)
2530 IS
2531
2532 -- --------- ------ -------------------------------------------
2533 Type NumTabType is VARRAY(10000) OF NUMBER;
2534 repair_estimate_line_id_mig NumTabType;
2535 estimate_detail_id_mig NumTabType;
2536 item_cost_mig NumTabType;
2537 original_cost_mig NumTabType;
2538 resource_id_mig NumTabType;
2539
2540
2541 Type VarCharTabType is VARRAY(10000) OF Varchar2(3);
2542 chg_currency_code_mig VarcharTabType;
2543 chg_uom_code_mig VarcharTabType;
2544
2545 Type DateTabType is VARRAY(10000) of Date;
2546 chg_creation_date_mig DateTabType;
2547
2548 Type RowidTabType is VARRAY(10000) of VARCHAR2(30);
2549 rowid_mig RowidTabType;
2550
2551 v_min NUMBER;
2552 v_max NUMBER;
2553 v_error_text Varchar2(2000);
2554 MAX_BUFFER_SIZE Number := 500;
2555 skip_process Exception;
2556
2557 --4/26/04, Shiv Ragunathan, Introduced the following exception. When this exception is raised,
2558 -- an error is raised and the process is stopped. This does not happen for skip_process.
2559 error_process Exception;
2560
2561 l_cost_currency_code VARCHAR2(30);
2562
2563 l_item_cost NUMBER;
2564 l_original_cost NUMBER;
2565 l_organization_id NUMBER;
2566 l_creation_Date DATE;
2567 l_conversion_type varchar2(30);
2568 l_max_roll_days number;
2569 l_inventory_item_id NUMBER;
2570 l_user_rate NUMBER;
2571 l_rate number;
2572 x_conv_amount number;
2573 l_denominator number;
2574 l_numerator number;
2575 l_orig_or_item_cost number;
2576 l_est_line_uom_code varchar2(3);
2577 l_billing_category_type varchar2(10);
2578 l_res_cost number;
2579 l_res_uom_code varchar2(3);
2580 -- Added a new column ORIGINAL_COST to CSD_REPAIR_ESTIMATE_LINES table.
2581 -- This column will be updated with the item_cost during migration. If conversion goes through
2582 -- succesfully then item_cost is updated with the converted value and original_Cost is
2583 --updated with the original item_cost. IF conversion does not go through then item_cost is
2584 --updated with null and original_cost is updated with the old item_cost. Thus we pick only those
2585 --records for which either item_cost or original_cost are null. If they are both null then we ignore
2586 --those records.
2587 -- Select rows from csd_repair_estimate_lines. Also get the currency code, uom for each line
2588 Cursor cur_getEstimateLines(p_start number, p_end number)
2589 IS
2590 SELECT rel.repair_estimate_line_id estimate_line_id, rel.item_cost item_cost,
2591 rel.original_cost original_cost, ced.currency_code, ced.creation_date ,
2592 ced.unit_of_measure_code, ced.estimate_detail_id, rel.resource_id, rel.rowid
2593 FROM csd_repair_estimate_lines rel, cs_estimate_details ced
2594 WHERE rel.estimate_detail_id = ced.estimate_detail_id
2595 and (
2596 ( rel.item_cost is not null and rel.original_cost is null)
2597 OR ( rel.item_cost is null and rel.original_cost is not null)
2598 )
2599 and rel.repair_Estimate_line_id >= p_start
2600 and rel.repair_Estimate_line_id <= p_end;
2601
2602 -- Get GL currency code
2603 CURSOR cur_getGLCode ( p_org_id NUMBER)
2604 IS
2605 SELECT gl.currency_code
2606 FROM gl_sets_of_books gl, hr_operating_units hr
2607 WHERE hr.set_of_books_id = gl.set_of_books_id
2608 AND hr.organization_id = p_org_id;
2609
2610 --Cursor to get primary_uom_code for the estimate line item.
2611 CURSOR cur_getUOMForEstLineItem(p_estimate_detail_id NUMBER)
2612 IS
2613 SELECT primary_uom_code
2614 FROM mtl_system_items MSI, cs_estimate_details ced
2615 WHERE CED.estimate_detail_id = p_estimate_detail_id
2616 AND MSI.inventory_item_id = CED.inventory_item_id
2617 AND MSI.organization_id = cs_std.get_item_valdn_orgzn_id;
2618
2619 -- Cursor to get the estimate line category for a given estimate line id
2620 CURSOR cur_getBillCategoryType (p_estimate_detail_id number)
2621 IS
2622 SELECT BCAT.billing_category
2623 FROM csd_repair_estimate_lines ESTL,
2624 cs_estimate_details ESTD,
2625 cs_txn_billing_types TXNT,
2626 cs_billing_type_categories BCAT
2627 WHERE ESTL.repair_estimate_line_id = p_estimate_detail_id
2628 AND ESTD.estimate_detail_id = ESTL.estimate_detail_id
2629 AND TXNT.txn_billing_type_id = ESTD.txn_billing_type_id
2630 AND BCAT.billing_type = TXNT.billing_type;
2631
2632 --Cursor to get resource cost for a resource id. We only consider standard/frozen costing type.
2633 CURSOR cur_getResCost(p_bom_resource_id NUMBER,
2634 p_organization_id NUMBER)
2635 IS
2636 SELECT CRC.resource_rate
2637 FROM cst_resource_costs CRC
2638 WHERE CRC.resource_id = p_bom_resource_id
2639 AND CRC.organization_id = p_organization_id
2640 AND CRC.cost_type_id = 1; -- standard/frozen cost
2641
2642 --Cursor to get resource UOM code for the given resource id
2643 CURSOR cur_getResUOMCode (p_bom_resource_id NUMBER)
2644 IS
2645 SELECT BR.unit_of_measure
2646 FROM BOM_RESOURCES BR
2647 WHERE BR.resource_id = p_bom_resource_id;
2648 BEGIN
2649
2650 --Get the slab number for the table
2651 Begin
2652 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIR_ESTIMATE_LINES',
2653 'CSD',
2654 p_slab_number,
2655 v_min,
2656 v_max);
2657 IF v_min is null then
2658 return;
2659 end if;
2660 end;
2661
2662 begin
2663
2664 -- Get the organization id from service validation org profile.
2665 fnd_profile.get('CS_INV_VALIDATION_ORG',l_organization_id);
2666 -- Derive conversion type and max roll days from the profile.
2667 --If these do not exist then default those.
2668 l_conversion_type := FND_PROFILE.value('CSD_CURRENCY_CONVERSION_TYPE');
2669 If (l_conversion_type is null) then
2670 l_conversion_type :='Corporate';
2671 end if;
2672 --Get the max roll days from the profile.
2673 l_max_roll_days := FND_PROFILE.value('CSD_CURRENCY_MAX_ROLL');
2674 If (l_max_roll_Days is null) then
2675 l_max_roll_days := 300;
2676 end if;
2677 -- Get item cost currency code
2678 OPEN cur_getGLCode( l_organization_id);
2679 FETCH cur_getGLCode into l_cost_currency_code;
2680 Close cur_getGLCode;
2681 Exception
2682 WHEN no_data_found then
2683 Close cur_getGLCode;
2684 when others then
2685 Close cur_getGLCode;
2686 End;
2687
2688 OPEN cur_getEstimateLines(v_min, v_max);
2689 LOOP
2690 FETCH cur_getEstimateLines bulk collect into
2691 repair_estimate_line_id_mig,
2692 item_cost_mig,
2693 original_cost_mig,
2694 chg_currency_code_mig ,
2695 chg_creation_date_mig,
2696 chg_uom_code_mig,
2697 estimate_detail_id_mig,
2698 resource_id_mig,
2699 rowid_mig
2700 LIMIT MAX_BUFFER_SIZE;
2701
2702 FOR j in 1..repair_estimate_line_id_mig.count
2703 LOOP
2704 SAVEPOINT CSD_REPAIR_ESTIMATE_LINES;
2705
2706 Begin
2707 --populate l_orig_or_item_cost variable. If item_cost is null then
2708 --l_orig_or_item_cost = original_cost else = item_cost
2709 IF (item_cost_mig(j) IS NULL ) THEN
2710 l_orig_or_item_cost := original_cost_mig(j);
2711 ELSE
2712 l_orig_or_item_cost := item_cost_mig(j);
2713 END IF;
2714
2715 --Check if the chg line uom is same as the item uom.
2716
2717 --Get the primary uom code for the estimate line item.
2718 OPEN cur_getUOMForEstLineItem(repair_estimate_line_id_mig(j));
2719 FETCH cur_getUOMForEstLineItem into l_est_line_uom_code;
2720 CLOSE cur_getUOMForEstLineItem;
2721
2722 if (l_est_line_uom_code <> chg_uom_code_mig(j) ) then
2723 -- If not then stamp item_cost with null and log the message in upg_errors.
2724
2725 Update csd_repair_Estimate_lines
2726 set original_cost = item_cost,
2727 item_cost = null
2728 where repair_estimate_line_id = repair_estimate_line_id_mig(j);
2729 --skip the record
2730 RAISE skip_process;
2731 --log_error(repair_estimate_line_id_mig(j),item_cost_mig(j), l_cost_currency_code);
2732 end if;
2733
2734 -- Check if the charge line is a labor line
2735 OPEN cur_getBillCategoryType(repair_estimate_line_id_mig(j));
2736 FETCH cur_getBillCategoryType into l_billing_category_type;
2737 CLOSE cur_getBillCategoryType;
2738
2739 -- if it is a labor line, find resource and resource cost.
2740 IF ( (l_billing_category_type ='L')
2741 and (resource_id_mig(j) is not null )) THEN
2742 -- get the resource cost
2743 OPEN cur_getResCost(resource_id_mig(j),l_organization_id);
2744 FETCH cur_getResCost into l_res_cost;
2745 CLOSE cur_getResCost;
2746
2747 --get the resource UOM
2748 OPEN cur_getResUOMCode(resource_id_mig(j));
2749 FETCH cur_getResUOMCode into l_res_uom_code;
2750 CLOSE cur_getResUOMCode;
2751 --check if the reosurce uom = charge line uom
2752 if (l_res_uom_code <> chg_uom_code_mig(j) ) then
2753 -- If not then stamp item_cost with null and log the message in upg_errors.
2754 Update csd_repair_Estimate_lines
2755 set original_cost = item_cost,
2756 item_cost = null
2757 where repair_estimate_line_id = repair_estimate_line_id_mig(j);
2758 -- skip the record
2759 raise skip_process;
2760 -- log_error(repair_estimate_line_id_mig(j),item_cost_mig(j), l_cost_currency_code);
2761 else
2762 -- resource and estimate line UOM matched. Stamp item_cost, original_cost with resource_cost
2763 Update csd_repair_Estimate_lines
2764 set original_cost = l_res_cost,
2765 item_cost = l_res_cost
2766 where repair_estimate_line_id = repair_estimate_line_id_mig(j);
2767 --Update the variable l_orig_or_item_cost with l_res_cost so
2768 --that if currency codes are different, we send the updated
2769 --value to the GL API.
2770 l_orig_or_item_cost := l_res_cost;
2771 end if;
2772
2773 END IF;
2774
2775 IF (chg_currency_code_mig(j) <> l_cost_currency_code) THEN
2776
2777 --Call GL API to convert the amount.
2778 GL_CURRENCY_API.CONVERT_CLOSEST_AMOUNT
2779 (
2780 x_from_currency => l_cost_currency_code,
2781 x_to_currency => chg_currency_code_mig(j),
2782 x_conversion_date => chg_creation_date_mig(j),
2783 x_conversion_type => l_conversion_type,
2784 x_user_rate => l_user_rate,
2785 x_amount => l_orig_or_item_cost , --item_cost_mig(j),
2786 x_max_roll_days => l_max_roll_days,
2787 x_converted_amount => x_conv_amount,
2788 x_denominator => l_denominator,
2789 x_numerator => l_numerator,
2790 x_rate => l_rate
2791 );
2792 -- If l_rate is -1 or -2 then conversion did not happen.
2793 -- Update original_cost with the item_cost and item_cost
2794 --with null and save this info in CSD_UPG_ERRORS table.
2795 IF l_rate < 0 then
2796 Update csd_repair_Estimate_lines
2797 set original_cost = item_cost,
2798 item_cost = null
2799 where repair_estimate_line_id = repair_estimate_line_id_mig(j);
2800 --Log the item_cost for the estimate that is being reset to null
2801 raise error_process;
2802 --log_error(repair_estimate_line_id_mig(j),item_cost_mig(j), l_cost_currency_code);
2803
2804 else
2805 -- Update original_cost with the item_cost and item_cost with the converted value since conversion
2806 --went thru fine.
2807 Update csd_repair_Estimate_lines
2808 set original_cost = item_cost,
2809 item_cost = x_conv_amount
2810 where repair_estimate_line_id =
2811 repair_estimate_line_id_mig(j);
2812
2813 IF sql%notfound then
2814 raise error_process;
2815 end if;
2816 end if;
2817 ELSE
2818 -- No need to convert because charge and cost currencies were the same. Just update
2819 -- original_cost with the item_cost so that the row does not get picked during reruns.
2820 Update csd_repair_Estimate_lines
2821 set original_cost = item_cost
2822 where repair_estimate_line_id =
2823 repair_estimate_line_id_mig(j);
2824
2825 IF sql%notfound then
2826 raise error_process;
2827 end if;
2828 END IF;
2829
2830 Exception
2831 when Skip_process then
2832 -- 4/26/04, Shiv Ragunathan,
2833 -- Commented out the following line as this would undo the null updates done to the cost line
2834 -- ROLLBACK to CSD_REPAIR_ESTIMATE_LINES;
2835
2836 -- 4/26/04, Shiv ragunathan, Commented out following line as log_error itself has an error_text
2837 -- v_error_text := substr(sqlerrm,1,1000)||'Estimate_Line_id:'||repair_estimate_line_id_mig(j) ;
2838
2839 log_error(repair_estimate_line_id_mig(j),item_cost_mig(j), l_cost_currency_code);
2840
2841 /* INSERT INTO CSD_UPG_ERRORS
2842 (ORIG_SYSTEM_REFERENCE,
2843 TARGET_SYSTEM_REFERENCE,
2844 ORIG_SYSTEM_REFERENCE_ID,
2845 UPGRADE_DATETIME,
2846 ERROR_MESSAGE,
2847 MIGRATION_PHASE)
2848 VALUES('CSD_REPAIR_ESTIMATE_LINES',
2849 'CSD_REPAIR_ESTIMATE_LINES',
2850 repair_estimate_line_id_mig(j),
2851 sysdate,
2852 v_error_text,
2853 '11.5.10');*/
2854
2855 -- 4/26/04, Shiv Ragunathan, Introduced following exception handling to handle case where process should
2856 -- stop immediately
2857
2858 WHEN error_process THEN
2859 v_error_text := substr(sqlerrm,1,1000)||'Estimate_Line_id:'||repair_estimate_line_id_mig(j) ;
2860
2861 INSERT INTO CSD_UPG_ERRORS
2862 (ORIG_SYSTEM_REFERENCE,
2863 TARGET_SYSTEM_REFERENCE,
2864 ORIG_SYSTEM_REFERENCE_ID,
2865 UPGRADE_DATETIME,
2866 ERROR_MESSAGE,
2867 MIGRATION_PHASE)
2868 VALUES('CSD_REPAIR_ESTIMATE_LINES',
2869 'CSD_REPAIR_ESTIMATE_LINES',
2870 repair_estimate_line_id_mig(j),
2871 sysdate,
2872 v_error_text,
2873 '11.5.10');
2874
2875 commit;
2876
2877 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_ESTIMATE_LINES cost data. '|| v_error_text);
2878
2879
2880 END;
2881 END LOOP;
2882
2883 --
2884 -- End API Body
2885 --
2886
2887
2888 COMMIT;
2889 EXIT WHEN cur_getEstimateLines%notfound;
2890 END LOOP;
2891 IF cur_getEstimateLines%isopen then
2892 close cur_getEstimateLines;
2893 end if;
2894 END CSD_Cost_data_mig3;
2895 /*-------------------------------------------------------------------------------*/
2896 /* procedure name: LOG_ERROR */
2897 /* description : procedure for logging errors while migrating */
2898 /* csd_repair_estimate_lines table cost data */
2899 /* from 11.5.9 to 11.5.10 */
2900 /* */
2901 /* This procedure will log the item_cost in CSD_UPG_ERRORS table */
2902 /*-------------------------------------------------------------------------------*/
2903 procedure log_Error( p_estimate_line_id number,p_item_cost number, p_cost_currency_code varchar2)
2904 IS
2905 v_error_text varchar2(2000);
2906 BEGIN
2907 --Log the item_cost for the estimate that is being reset to null
2908 v_error_text := substr(sqlerrm,1,1000)||'Estimate_Line_id:'||p_estimate_line_id
2909 || 'item_cost:'|| p_item_cost ||p_cost_currency_code ;
2910
2911 INSERT INTO CSD_UPG_ERRORS
2912 (ORIG_SYSTEM_REFERENCE,
2913 TARGET_SYSTEM_REFERENCE,
2914 ORIG_SYSTEM_REFERENCE_ID,
2915 UPGRADE_DATETIME,
2916 ERROR_MESSAGE,
2917 MIGRATION_PHASE)
2918 VALUES('CSD_REPAIR_ESTIMATE_LINES',
2919 'CSD_REPAIR_ESTIMATE_LINES',
2920 p_estimate_line_id,
2921 sysdate,
2922 v_error_text,
2923 '11.5.10');
2924
2925 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2926 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2927 'CSD.PLSQL.CSD_COST_DATA_MIG3.ADD',
2928 v_error_text);
2929 end if;
2930
2931 END;-- log_error;
2932
2933
2934 /*------------------------------------------------------------------------*/
2935 /* procedure name: CSD_REPAIR_HISTORY_MIG3 */
2936 /* description : procedure for migrating CSD_REPAIR_HISTORY table data */
2937 /* from 11.5.9 to 11.5.10 */
2938 /* Created : vkjain on SEPT-30-2003 */
2939 /* */
2940 /* Here are the details for the migration - */
2941 /* Event Code (New field(s) populated) Comments
2942 /* RR Receiving Org Name (paramc3) Using receiving transactions
2943 /* Id to determine values.
2944 /*
2945 /* RSC Receiving Subinv Name(paramc1)Using receiving transactions
2946 /* Id to determine values.
2947 /*
2948 /* JS <None> Job Name, Item Name,
2949 /* Quantity allocated, Group Id
2950 /* and Concurrent Request Number
2951 /* fields not populated unable
2952 /* to determine values.
2953 /* The event code will be
2954 /* renamed to JSU.
2955 /*
2956 /* TC Task Name (paramc7) Unable to determine values
2957 /* for the other new fields.
2958 /*
2959 /* TOC Task Name (paramc7) Unable to determine values
2960 /* for the other new fields.
2961 /*
2962 /* TSC Task Name (paramc7) Unable to determine values
2963 /* for the other new fields.
2964 /*
2965 /* PS Shipping Org Name (paramc3), Using delivery detail Id
2966 /* Shipping SubinvName (paramc4) to determine values.
2967 /*
2968 /* A <None> Event Code renamed to ESU.
2969 /* Estimate total field not
2970 /* populated unable to
2971 /* determine value.
2972 /*
2973 /* R <None> Event Code renamed to ESU.
2974 /* Estimate total field not
2975 /* populated unable to
2976 /* determine value.
2977 /*
2978 /* Points to note
2979 /*
2980 /* 1. New events do not need data migration effort.
2981 /* 2. JC is the only event that exists in 11.5.9 and has new fields for
2982 /* 11.5.10 but does not appear in the list above. This is because we are
2983 /* unable to determine the value for the new field 'Quantity Allocated'.
2984 /* 3. As a pre-upgrade step, we expect the users to fully complete all the
2985 /* pending wip jobs and run the update program.
2986 /* */
2987 /* */
2988 /*------------------------------------------------------------------------*/
2989
2990 PROCEDURE csd_repair_history_mig3(p_slab_number IN NUMBER)
2991 IS
2992
2993 -- Table type definitions
2994
2995 -- Shiv Ragunathan, 12/10/03, Changed VARRAY to TABLE, as prior to
2996 -- oracle 9.0.1, rowid is not recognized as a supported datatype
2997 -- for VARRAY, due to which we get PLS-00531 error at compile
2998 -- time.( This was due to a PLSQL bug, fixed in versions 9.0.1
2999 -- and beyond ). Changed VartabType as well to be consistent.
3000
3001 -- TYPE RowidTabType IS VARRAY(1000) OF ROWID;
3002 -- TYPE VarTabType IS VARRAY(1000) OF VARCHAR2(240);
3003
3004
3005 TYPE RowidTabType IS TABLE OF ROWID INDEX by Binary_Integer;
3006 Type VarTabType IS TABLE OF VARCHAR2(240) INDEX by Binary_Integer;
3007
3008
3009
3010 -- Following variables will be used for the
3011 -- the event code 'PS'.
3012 shipping_org_name_arr VarTabType;
3013 shipping_subinv_name_arr VarTabType;
3014 rowid_arr RowidTabtype;
3015
3016 -- Stores the minimum and maximum repair history id
3017 -- values for the slab.
3018 v_min NUMBER;
3019 v_max NUMBER;
3020
3021 -- Stores the array size for each iteration.
3022 l_array_size NUMBER;
3023 v_error_text VARCHAR2(2000);
3024
3025 -- The buffer size limits the fetch size to the
3026 -- constant value.
3027 MAX_BUFFER_SIZE CONSTANT NUMBER := 500;
3028
3029 -- The following exception will be thrown to stop
3030 -- further processing.
3031 STOP_PROCESS EXCEPTION;
3032
3033 -- The following cursor gets the desired information from
3034 -- deliveries and HR for the event 'Shipment Completed'.
3035 CURSOR c_get_PS_event_details IS
3036 SELECT HIST.rowid,
3037 HROU.name,
3038 WSH.subinventory
3039 FROM CSD_REPAIR_HISTORY HIST,
3040 WSH_DELIVERY_DETAILS WSH,
3041 HR_ALL_ORGANIZATION_UNITS_VL HROU
3042 WHERE HIST.EVENT_CODE = 'PS'
3043 AND HIST.REPAIR_HISTORY_ID >= v_min
3044 AND HIST.REPAIR_HISTORY_ID <= v_max
3045 AND HIST.PARAMN1 IS NOT NULL
3046 AND WSH.delivery_detail_id = HIST.paramn1
3047 AND HROU.organization_id = WSH.organization_id;
3048
3049 BEGIN
3050
3051 -- Establish a save point for the procedure.
3052 SAVEPOINT CSD_REPAIR_HISTORY_SP;
3053
3054 -- Get the Slab Number for the table
3055 BEGIN
3056 CSD_MIG_SLABS_PKG.GET_TABLE_SLABS('CSD_REPAIR_HISTORY',
3057 'CSD',
3058 p_slab_number,
3059 v_min,
3060 v_max);
3061
3062 IF v_min IS NULL THEN
3063 RETURN;
3064 END IF;
3065 END;
3066
3067 -- Error message text to label the processing event.
3068 v_error_text := ',while processing event code ''RR''';
3069
3070 -- Processing event 'RR'. We simply use an update
3071 -- statement to update all lines.
3072 -- We update the field with organization_name.
3073 UPDATE CSD_REPAIR_HISTORY HIST
3074 SET HIST.PARAMC3 = ( SELECT hrou.name
3075 FROM hr_all_organization_units_vl HROU,
3076 RCV_TRANSACTIONS RCV
3077 WHERE RCV.transaction_id = HIST.paramn1
3078 AND HROU.organization_id = RCV.organization_id
3079 )
3080 WHERE HIST.EVENT_CODE = 'RR'
3081 AND HIST.REPAIR_HISTORY_ID >= v_min
3082 AND HIST.REPAIR_HISTORY_ID <= v_max
3083 AND HIST.PARAMN1 IS NOT NULL;
3084
3085 -- Error message text to label the processing event.
3086 v_error_text := ',while processing event code ''RSC''';
3087
3088 -- Processing event 'RSC'. We simply use an update
3089 -- statement to update all lines.
3090 -- We update the field with subinventory.
3091 UPDATE CSD_REPAIR_HISTORY HIST
3092 SET HIST.PARAMC1 = ( SELECT rcv.subinventory
3093 FROM RCV_TRANSACTIONS RCV
3094 WHERE RCV.transaction_id = HIST.paramn1
3095 )
3096 WHERE HIST.EVENT_CODE = 'RSC'
3097 AND HIST.REPAIR_HISTORY_ID >= v_min
3098 AND HIST.REPAIR_HISTORY_ID <= v_max
3099 AND HIST.PARAMN1 IS NOT NULL;
3100
3101 -- Error message text to label the processing event.
3102 v_error_text := ',while processing event code ''JSU''';
3103 -- sangigup
3104 -- Need to move group_id from paramc5 to paramn6
3105 -- We use an update statement to update all lines
3106 -- Pre 11.5 we used to append group_id with 'CSD'. Now, we remove 'CSD' prefix before
3107 --updating the table. Or else the update will fail.
3108 UPDATE CSD_REPAIR_HISTORY HIST
3109 SET HIST.paramn6 =
3110 nvl( to_number(decode(instr(hist.paramc5,'CSD',1),0,hist.paramc5, substr(hist.paramc5,4,length(hist.paramc5)) ) ),''),
3111
3112 --nvl(to_number(hist.paramc5),''),
3113 hist.paramc5 = NULL
3114 WHERE HIST.EVENT_CODE = 'JS'
3115 AND HIST.REPAIR_HISTORY_ID >= v_min
3116 AND HIST.REPAIR_HISTORY_ID <= v_max;
3117 --sangigup
3118 -- Processing event 'JS'. We simply use an update
3119 -- statement to update all lines.
3120 -- We update the event code to 'JSU'.
3121 UPDATE CSD_REPAIR_HISTORY HIST
3122 SET HIST.EVENT_CODE = 'JSU'
3123 WHERE HIST.EVENT_CODE = 'JS'
3124 AND HIST.REPAIR_HISTORY_ID >= v_min
3125 AND HIST.REPAIR_HISTORY_ID <= v_max;
3126
3127 -- Error message text to label the processing event.
3128 v_error_text := ',while processing event code ''TC,TOC,TSC''';
3129
3130 -- Processing event "TC, TOC and TSC". We simply use an update
3131 -- statement to update all lines.
3132 -- We update the field task name.
3133 UPDATE CSD_REPAIR_HISTORY HIST
3134 SET HIST.PARAMC7 = ( SELECT task_name
3135 FROM JTF_TASKS_VL
3136 WHERE task_id = HIST.paramn1
3137 )
3138 WHERE HIST.EVENT_CODE IN ('TC', 'TOC', 'TSC')
3139 AND HIST.REPAIR_HISTORY_ID >= v_min
3140 AND HIST.REPAIR_HISTORY_ID <= v_max
3141 AND HIST.PARAMN1 IS NOT NULL;
3142
3143 -- Error message text to label the processing event.
3144 v_error_text := ',while processing event code ''ESU''';
3145
3146 -- Processing event "A and R". We simply use an update
3147 -- statement to update all lines.
3148 -- We update the event code to 'ESU'.
3149 UPDATE CSD_REPAIR_HISTORY HIST
3150 SET HIST.EVENT_CODE = 'ESU'
3151 WHERE HIST.EVENT_CODE IN ('A', 'R')
3152 AND HIST.REPAIR_HISTORY_ID >= v_min
3153 AND HIST.REPAIR_HISTORY_ID <= v_max;
3154
3155 -- Error message text to label the processing event.
3156 v_error_text := ',while processing event code ''PS''';
3157
3158 -- Processing event 'PS'. We use a cursor to fetch the
3159 -- desired values in BULK. We also use BULK update for
3160 -- updating all eligible lines.
3161 -- We update the fields - shipping org name and shipping subinvtory.
3162 OPEN c_get_PS_event_details;
3163
3164 LOOP
3165 -- Get all rows where repair_history_id is between v_min and
3166 -- v_max for the event 'PS'.
3167 FETCH c_get_PS_event_details
3168 BULK COLLECT
3169 INTO rowid_arr,
3170 shipping_org_name_arr,
3171 shipping_subinv_name_arr
3172 LIMIT MAX_BUFFER_SIZE;
3173
3174 l_array_size := rowid_arr.COUNT; -- Number of VARRAY elements
3175
3176 -- BULK update for all lines
3177 FORALL i IN 1..l_array_size
3178 UPDATE CSD_REPAIR_HISTORY
3179 SET paramc3 = shipping_org_name_arr(i),
3180 paramc4 = shipping_subinv_name_arr(i)
3181 WHERE rowid = rowid_arr(i);
3182
3183 EXIT WHEN c_get_PS_event_details%NOTFOUND;
3184 END LOOP;
3185
3186 IF c_get_PS_event_details%ISOPEN THEN
3187 CLOSE c_get_PS_event_details;
3188 END IF;
3189
3190 COMMIT;
3191
3192 EXCEPTION
3193 WHEN OTHERS THEN
3194 ROLLBACK TO CSD_REPAIR_HISTORY_SP;
3195 IF c_get_PS_event_details%ISOPEN THEN
3196 CLOSE c_get_PS_event_details;
3197 END IF;
3198 v_error_text := 'Encountered fatal error ' ||
3199 SQLCODE || substr(sqlerrm, 1, 1000)||
3200 v_error_text || -- ', while processing event code ''<EVENT_CODE>''
3201 'for the repair history id between ' ||
3202 v_min || ' and ' || v_max;
3203
3204 INSERT INTO CSD_UPG_ERRORS
3205 (ORIG_SYSTEM_REFERENCE,
3206 TARGET_SYSTEM_REFERENCE,
3207 ORIG_SYSTEM_REFERENCE_ID,
3208 UPGRADE_DATETIME,
3209 ERROR_MESSAGE,
3210 MIGRATION_PHASE)
3211 VALUES ('CSD_REPAIR_HISTORY',
3212 'CSD_REPAIR_HISTORY',
3213 NULL,
3214 sysdate,
3215 v_error_text,
3216 '11.5.10');
3217 COMMIT;
3218 -- We should throw exception to stall the process
3219 -- as we do not know what went wrong.
3220 -- 12/1/03, sragunat, Commenting out the following line,
3221 -- as do not want the migration to fail due to an error here
3222 -- Later, if needed, can uncomment it after further
3223 -- investigation
3224
3225 -- RAISE STOP_PROCESS;
3226
3227
3228 raise_application_error( -20000, 'Error while migrating CSD_REPAIR_HISTORY table data. ' || v_error_text);
3229
3230
3231 END csd_repair_history_mig3;
3232
3233
3234 /*------------------------------------------------------------------------*/
3235 /* procedure name: CSD_PRODUCT_TRANS_MIG3(p_slab_Number Number)
3236 /* description : procedure for migrating CSD_PRODUCT_TRANSACTIONS data */
3237 /* from 11.5.9 to 11.5.10 */
3238 /* Created : saupadhy SEPT-30-2003 */
3239 /* */
3240 /* Here are the details for the migration - */
3241 /* Prod Txn Status (New field(s) populated) Comments
3242 /* RECEIVED Quantity_Received, */
3243 /* source_serial_number */
3244 /* source_instance_id */
3245 /* sub_inventory */
3246 /* lot_Number */
3247 /* locator_id */
3248 /* SHIPPED Quantity_Shipped */
3249 /* source_serial_number */
3250 /* source_instance_id */
3251 /* non_source_serial_number */
3252 /* non_source_instance_id */
3253 /* sub_inventory */
3254 /* lot_Number */
3255 /* locator_id */
3256 /* BOOKED action_type is 'RMA' and order line qty is > 1 */
3257 /* Only qty that is captured in csd_repair_history is */
3258 /* updated in csd_product_txns table */
3259 /*RELEASED action_type is 'SHIP' and order line qty is > 1 */
3260 /* Only qty that is captured in csd_repair_history is */
3261 /* updated in csd_product_txns table. */
3262 /************************************************************************/
3263 procedure CSD_PRODUCT_TRANS_MIG3(p_slab_Number Number) IS
3264
3265 TYPE NumTabType IS TABLE OF NUMBER
3266 INDEX by Binary_Integer;
3267 v_repair_line_id NumTabType;
3268 v_product_trans_id NumTabType ;
3269 v_Estimate_Detail_Id NumTabType ;
3270 v_Order_Header_Id NumTabType ;
3271 v_Order_Line_Id NumTabType ;
3272 v_Order_Line_Qty NumTabType ;
3273 v_Transacted_Qty NumTabType ;
3274 v_Inventory_Item_Id NumTabType ;
3275 v_Ship_To_Org_Id NumTabType ;
3276 v_Ship_From_Org_Id NumTabType ;
3277 v_serial_Number_control_code NumTabType ;
3278 v_lot_control_code NumTabType ;
3279 v_Locator_Id NumTabType ;
3280 v_Source_Instance_Id NumTabType ;
3281 v_Non_Source_Instance_Id NumTabType ;
3282 v_Quantity_Received NumTabType ;
3283 v_Quantity_Shipped NumTabType ;
3284 v_Quantity_Required NumTabType ;
3285 v_customer_product_id NumTabType ;
3286 v_Location_Control_Code NumTabType ;
3287
3288
3289 TYPE RowidTabType IS TABLE OF ROWID
3290 INDEX by Binary_Integer;
3291 v_rowid RowidTabtype;
3292
3293 TYPE VCharTabType IS TABLE OF VARCHAR2(30)
3294 INDEX by Binary_Integer;
3295 v_ActionType VCharTabType ;
3296 v_ActionCode VCharTabType ;
3297 v_comms_Nls_trackable_flag VCharTabType ;
3298 v_Source_Serial_Number VCharTabType ;
3299 v_Non_SOurce_Serial_Number VCharTabType ;
3300 --Bug: 3622825 Commenting following columns
3301 -- v_Sub_Inventory VCharTabType ;
3302 -- v_Lot_Number VCharTabType ;
3303 v_Prod_Txn_Status VCharTabType ;
3304 v_repair_type_ref VCharTabType ;
3305 v_serial_number VCharTabType ;
3306 v_Shipped_Serial_Number VCharTabType ;
3307
3308
3309 -- Flag to track multiple Sub Inventory, Lot Number, Locator_id
3310 -- These variable are no more used so commenting them. 3742767
3311 -- l_One_SubInventory Varchar2(1) ;
3312 -- l_One_LocationId Varchar2(1) ;
3313 -- l_One_LotNumber Varchar2(1) ;
3314 -- l_SubInventory Varchar2(30);
3315 l_locator_id Number ;
3316 -- l_Lot_Number Varchar2(30) ; 3742767
3317 l_Quantity Number ;
3318
3319
3320 -- Declare local variables
3321 l_Table_Name Varchar2(30) := 'CSD_PRODUCT_TRANSACTIONS' ;
3322 l_Module Varchar2(30) := 'CSD' ;
3323 l_start_slab Number ;
3324 l_End_Slab Number ;
3325 l_error_text VARCHAR2(2000);
3326 l_Array_Size Number ;
3327 l_Procedure_Event Varchar2(100);
3328
3329 MAX_BUFFER_SIZE CONSTANT NUMBER := 500;
3330
3331 error_process_excep EXCEPTION;
3332 -- 1.Make sure that main cursor does not pick up once processed records.
3333 -- This can be made sure by checking that newly introduced columns has null values
3334 -- while selecting records for processing
3335 -- 2. Should pick up all the records i.e that are recieived, shipped, released or in
3336 -- any status
3337 -- and cpt.quantity_received is NULL
3338 -- and cpt.quantity_shipped is NULL
3339 -- and cpt.source_instance_id is Null
3340 -- and cpt.non_source_instance_id is NUll
3341 -- and cpt.source_serial_number is Null
3342 -- and cpt.non_source_serial_number is null
3343 -- and cpt.locator_id is null
3344
3345 -- Added NULL columns to initialize the collection
3346 Cursor Prod_txns_cur( p_Start_Slab Number, p_End_Slab Number) IS
3347 Select cpt.rowid,
3348 cpt.product_transaction_Id ,
3349 cpt.Action_TYpe,
3350 cpt.Action_Code,
3351 cpt.estimate_detail_id,
3352 cpt.repair_line_id,
3353 cpt.Prod_txn_Status,
3354 oola.header_id ,
3355 oola.line_id ,
3356 (oola.ordered_quantity - Nvl(oola.cancelled_quantity,0) ) Line_quantity,
3357 oola.shipped_quantity Transacted_Qty,
3358 oola.inventory_item_id ,
3359 oola.ship_to_org_id ,
3360 oola.ship_from_org_id,
3361 msi.serial_Number_control_code,
3362 msi.comms_Nl_trackable_flag ,
3363 msi.lot_control_code ,
3364 msi.location_control_code ,
3365 ced.quantity_required ,
3366 ced.customer_product_id ,
3367 ced.serial_number,
3368 cpt.shipped_serial_number,
3369 crt.repair_type_ref,
3370 NULL, -- locator_id
3371 NULL, -- source_instance_id
3372 NULL, -- non_source_instance_id
3373 NULL, -- quantity_received
3374 NULL, -- Quantity_shipped
3375 NULL, -- source_serial_number
3376 NULL -- non_source_serial_number
3377 From csd_product_transactions cpt ,
3378 csd_repairs cr ,
3379 csd_repair_types_b crt,
3380 cs_estimate_details ced ,
3381 oe_order_headers_all ooha,
3382 oe_order_lines_all oola ,
3383 mtl_system_items_b msi
3384 Where cpt.product_transaction_id >= p_Start_Slab
3385 and cpt.product_transaction_id <= p_End_Slab
3386 and cpt.repair_line_id = cr.repair_line_id
3387 and cr.repair_type_id = crt.repair_type_id
3388 and cpt.estimate_detail_id = ced.estimate_detail_id
3389 and cpt.quantity_received is NULL
3390 and cpt.quantity_shipped is NULL
3391 and cpt.source_instance_id is Null
3392 and cpt.non_source_instance_id is NUll
3393 and cpt.source_serial_number is Null
3394 and cpt.non_source_serial_number is null
3395 and cpt.locator_id is null
3396 and ced.order_header_id = ooha.header_id
3397 and ooha.header_id = oola.header_id
3398 and ced.order_line_id = oola.line_id
3399 and oola.inventory_item_id = msi.inventory_item_id
3400 and oola.ship_from_org_id = msi.organization_id ;
3401
3402 -- Define a cursor which gets all receiving transaction records for parent order line id
3403 Cursor Rcv_transactions_cur (p_order_header_Id Number, p_Order_Line_Id Number) IS
3404 Select rcvt.transaction_id, rcvt.quantity, rcvt.locator_id
3405 From Rcv_Transactions Rcvt
3406 Where rcvt.Transaction_Type = 'DELIVER'
3407 and rcvt.oe_order_header_id = p_order_header_Id
3408 and rcvt.oe_order_line_id in ( Select line_id from oe_order_lines_all
3409 start with line_id = p_Order_Line_Id
3410 connect by prior line_id = split_from_line_id ) ;
3411
3412 -- Define a cursor which gets receiving transactions records for given order line id
3413 -- Following cursor definition is used when order line quantity is 1
3414 Cursor Single_Rcv_transactions_cur (p_order_header_Id Number, p_Order_Line_Id Number) IS
3415 Select rcvt.transaction_id, rcvt.quantity, rcvt.locator_id
3416 From Rcv_Transactions Rcvt
3417 Where rcvt.Transaction_Type = 'DELIVER'
3418 and rcvt.oe_order_header_id = p_order_header_Id
3419 and rcvt.oe_order_line_id = p_Order_Line_Id ;
3420
3421 -- Define a cursor which gets all delivery details records for parent order line id
3422 -- Included wsh_serial_numbers table in from clause as serial_number may not be captured
3423 -- in wsh_delivery_details.
3424 Cursor Delivery_details_cur (p_order_header_Id Number, p_Order_Line_Id Number) IS
3425 Select wdt.delivery_detail_id, wdt.shipped_quantity,wdt.locator_id ,
3426 Nvl(wdt.serial_number, wsn.fm_serial_number) serial_number
3427 From wsh_delivery_details wdt,
3428 Wsh_Serial_Numbers wsn
3429 Where wdt.Released_Status in ( 'C', 'I')
3430 and wdt.source_header_id = p_order_header_Id
3431 and wdt.source_line_id in ( Select line_id from oe_order_lines_all
3432 start with line_id = p_Order_Line_Id
3433 connect by prior line_id = split_from_line_id )
3434 and wdt.delivery_detail_id = wsn.delivery_detail_id(+);
3435
3436 -- Define a cursor which gets delivery details records for given order line id
3437 -- Following cursor definition is used when order line quantity is 1
3438 Cursor Single_Delivery_details_cur (p_order_header_Id Number, p_Order_Line_Id Number) IS
3439 Select wdt.delivery_detail_id, wdt.shipped_quantity, wdt.locator_id ,
3440 Nvl(wdt.serial_number, wsn.fm_serial_number) serial_number
3441 From wsh_delivery_details wdt,
3442 wsh_serial_numbers wsn
3443 Where wdt.Released_Status in ( 'C', 'I')
3444 and wdt.source_header_id = p_order_header_Id
3445 and wdt.source_line_id = p_Order_Line_Id
3446 and wdt.delivery_detail_id = wsn.delivery_detail_id(+);
3447
3448 BEGIN
3449 -- Dbms_Output.Put_line('Procedure :csd_product_trans_mig: Begin ');
3450 -- Dbms_Output.Put_line('Table Name is :' || l_Table_Name ) ;
3451 -- Dbms_Output.Put_line('Module Name is :' || l_Module ) ;
3452 -- Do we need to Verify if P_Slab_Number is NUll
3453 -- Get the Slab Number for the table
3454 l_procedure_Event := 'At the begining of the procedure';
3455 BEGIN
3456
3457 CSD_MIG_SLABS_PKG.Get_Table_Slabs(p_table_name => l_Table_Name ,
3458 p_module => l_Module ,
3459 p_slab_number => p_Slab_Number,
3460 x_start_slab => l_Start_Slab ,
3461 x_end_slab => l_End_Slab );
3462 -- Migration script for CSD_Product_Transactions
3463 -- Dbms_Output.Put_line('Start Slab :' || l_Start_Slab );
3464 -- Dbms_Output.Put_line('End Slab :' || l_End_Slab );
3465 -- Return if l_Start_Slab variable has null value
3466 IF l_Start_Slab IS NULL OR l_End_Slab IS NULL THEN
3467 RETURN;
3468 END IF;
3469 END;
3470 l_procedure_Event := 'After calling csd_mig_slabs_pkg.get_table_slabs procedure' ;
3471
3472 -- Migration script for CSD_Product_Transactions
3473
3474 OPEN Prod_txns_cur(l_Start_Slab, l_End_Slab);
3475 LOOP
3476 Begin
3477 -- Every fetch it will atmost fetch MAX_BUFFER_SIZE number of records.
3478 -- Loops until all the records fetched by cursor are processed.
3479
3480 FETCH Prod_txns_cur BULK COLLECT
3481 INTO v_rowid ,
3482 v_product_trans_Id ,
3483 v_ActionType,
3484 v_ActionCode,
3485 v_estimate_detail_id,
3486 v_repair_line_id,
3487 v_Prod_Txn_Status,
3488 v_order_header_id ,
3489 v_order_line_id ,
3490 v_order_Line_qty,
3491 v_Transacted_Qty,
3492 v_inventory_item_id ,
3493 v_ship_to_org_id ,
3494 v_ship_from_org_id,
3495 v_serial_Number_control_code,
3496 v_comms_Nls_trackable_flag ,
3497 v_lot_control_code ,
3498 v_Location_Control_Code,
3499 v_Quantity_Required,
3500 v_customer_product_id,
3501 v_serial_number,
3502 v_shipped_serial_number,
3503 v_repair_type_ref,
3504 v_locator_id,
3505 v_source_instance_id,
3506 v_non_source_instance_id,
3507 v_quantity_received,
3508 v_Quantity_shipped,
3509 v_source_serial_number,
3510 v_non_source_serial_number
3511 LIMIT MAX_BUFFER_SIZE;
3512
3513 -- Loop through each of the arrays to get values for other variables
3514 l_procedure_Event := 'After fetching rows from main cursor';
3515
3516 l_Array_size := v_Product_trans_id.Count ;
3517 If l_Array_Size = 0 Then
3518 Exit;
3519 End If;
3520 FOR j IN 1..l_Array_Size
3521 LOOP
3522 Begin
3523 SavePoint Update_prod_Txns ;
3524 l_procedure_Event := 'Begining of processing fetched rows' ;
3525 -- Assign Null Value to all varialbes that are used for migrating data to 11.5.10 release
3526 -- This is to make sure that if a record is skipped for what ever reasons, its corresponding
3527 -- variables are assigned null value.
3528 /************
3529 No need to initialize to Null they are initialize to null in the begining
3530 v_Locator_Id(j) := Null;
3531 v_Source_Serial_Number(j) := Null;
3532 v_Source_Instance_Id(j) := Null;
3533 v_Non_Source_Serial_Number(j) := Null;
3534 v_Non_Source_Instance_Id(j) := Null;
3535 v_Quantity_Shipped(j) := Null;
3536 v_Quantity_Received(j) := Null;
3537 ************/
3538
3539 If v_ActionType(j) in ('RMA','WALK_IN_RECEIPT') Then
3540 l_procedure_Event := 'Processing RMA record';
3541 -- Note for RMA line following columns will always have Null value
3542 -- v_Non_SOurce_Serial_Number(j)
3543 -- v_Non_Source_Instance_Id(j)
3544 -- v_Quantity_Shipped(j)
3545 -- Check if item is serial Number controlled or not
3546 If v_Serial_Number_Control_Code(j) = 1 Then
3547 l_procedure_Event := 'Processing RMA non serial record';
3548 -- Get all receiving transaction records for given order_header_id and order_line_id
3549 If (v_Quantity_Required(j) = 1) and (V_Prod_txn_status(j) = 'RECEIVED' )Then
3550 l_procedure_Event := 'Processing RMA non serial qty 1 and received';
3551 For Rcv_txn_rec In Single_Rcv_transactions_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3552 v_Quantity_Received(j) := Rcv_txn_rec.quantity ;
3553 v_Locator_Id(j) := Rcv_txn_rec.locator_id ;
3554 End Loop ;
3555 Else
3556 l_procedure_Event := 'Processing RMA non serial qty > 1 or not received';
3557 -- Initialize following variables
3558 l_locator_id := NUll ;
3559 l_Quantity := NUll ;
3560 -- Check Product Transaction Status , if it is 'RECEIVED' then
3561 -- Quantity Received is total quantity received against that RMA
3562 -- Else Quantity Received is what is recorded in csd_repair_history table
3563 If v_Prod_txn_Status(j) = 'RECEIVED' Then
3564 l_procedure_Event := 'Processing RMA non serial qty > 1 and received';
3565 For Rcv_txn_rec In Rcv_transactions_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3566 -- Cumulate quantity value for all lines split from parent line id
3567 v_Quantity_Received(j) := Nvl(v_Quantity_Received(j),0) + Rcv_txn_rec.Quantity ;
3568 v_Locator_id(j) := Rcv_Txn_Rec.Locator_Id;
3569 End Loop ;
3570 Else
3571 l_procedure_Event := 'Processing RMA non serial qty > 1 or not received';
3572 For Rcv_txn_rec In Rcv_transactions_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3573 -- Check if current receving transaction id record information is updated
3574 -- in csd_repair_history table
3575 Begin
3576 Select Quantity
3577 Into l_Quantity
3578 From csd_repair_history
3579 Where event_code = 'RR'
3580 and paramn1 = rcv_txn_rec.transaction_id
3581 and repair_line_id = V_Repair_Line_Id(j);
3582 Exception
3583 When No_Data_Found Then
3584 l_Quantity := 0 ;
3585 End;
3586 V_locator_id(j) := Rcv_txn_rec.locator_id ;
3587 v_Quantity_Received(j) := Nvl(v_Quantity_Received(j),0) + l_Quantity ;
3588 End Loop ;
3589 End If; -- v_Prod_txn_Status(j) = 'RECEIVED'
3590 End If; -- v_Quantity_Required(j) = 1 Then
3591
3592 -- Check if Item is IB Trackable, if so then source_instance_id column is populated with
3593 -- customer_product_id information
3594 If v_comms_Nls_trackable_flag(j) = 'Y' Then
3595 v_Source_Instance_Id(j) := v_Customer_Product_Id(j);
3596 Else
3597 v_Source_Instance_Id(j) := Null;
3598 End If;
3599 Elsif (v_Serial_Number_Control_Code(j) <> 1) and (V_Prod_txn_status(j) = 'RECEIVED') Then
3600 Begin
3601 -- To fix bug 3842957/4140451 saupadhy
3602 -- In pre 11i RMA returns were handled by inventory module not receiving module.
3603 -- In 11i this functionality is handled by PO module.During migration from pre 11i
3604 -- to 11i, PO creates lines in rcv_Transactions and rcv_serial_transactions for those
3605 -- records that exist in SO_RMA interface table. There is a possibility that customer
3606 -- might have deleted records from SO_RMA interface table after those records are
3607 -- successfully received in inventory. In such cases there will be no record created
3608 -- in rcv_transactions table.So Depot has changed the logic for finding
3609 -- serial number for recevied items, it looks for serial number information in
3610 -- rcv_transactions table but if the record does not exist it looks for the info in
3611 -- csd_repairs table, if the record is not found there too then Depot will assign
3612 -- blank values to source serial number column.
3613 -- This strategy will help upgrade process not to stop because of missing records in
3614 -- rcv_transactions table.
3615 -- Item is serial controlled
3616 l_procedure_Event := 'Delivery info for serial and received item';
3617 Begin
3618 Select rcvt.quantity, rcvt.locator_id , rst.serial_num
3619 Into v_Quantity_Received(j), v_Locator_Id(j),v_Source_Serial_Number(j)
3620 From Rcv_Transactions Rcvt ,
3621 Rcv_Serial_Transactions rst
3622 Where rcvt.Transaction_Type = 'DELIVER'
3623 and rcvt.oe_order_header_id = v_order_header_id(j)
3624 and rcvt.oe_order_line_id = v_order_line_id(j)
3625 and rcvt.transaction_id = rst.transaction_id
3626 and rownum = 1;
3627 Exception
3628 When No_Data_Found Then
3629 -- To fix bug 3842957/4140451 saupadhy
3630 -- look for serial number information in csd_repairs table,
3631 -- As this case will arise only for pre 11i transactions and in pre 11i
3632 -- Depot creates RO only after an item is received, if the item is serial
3633 -- controlled, Depot captures serial number information in csd_repairs table.
3634 V_Quantity_Received(j) := 1;
3635 v_Locator_ID(j) := Null;
3636 Begin
3637 Select Serial_Number Into V_Source_Serial_Number(j)
3638 From CSD_Repairs
3639 Where Repair_line_Id = V_Repair_Line_ID(j);
3640 Exception
3641 When No_Data_Found THen
3642 -- Assign Null value to Serial Number array and move on.
3643 v_Source_Serial_Number(j) := Null;
3644 End;
3645 End;
3646
3647 -- If Item is IB Trackable Then get IB Ref Number from csi_item_instances
3648 -- To fix bug 3842957/4140451 saupadhy
3649 -- Added check for not null value for source_Serial_Number
3650 If v_comms_Nls_trackable_flag(j) = 'Y'
3651 and v_Source_Serial_Number(j) is Not Null Then
3652 Begin
3653 l_procedure_Event := 'IB info for serial and received item';
3654 select instance_id
3655 Into v_Source_Instance_Id(j)
3656 from csi_item_instances
3657 where inventory_item_id = v_inventory_item_id(j)
3658 and serial_number = v_Source_Serial_Number(j) ;
3659 -- Following statement is incorrect and not required so commenting it.
3660 -- and inv_master_organization_id = cs_std.get_item_valdn_orgzn_id;
3661 ---??????? 3742767
3662 Exception
3663 When No_Data_Found Then
3664 V_Source_Instance_Id(j) := V_Customer_Product_id(j);
3665 End;
3666 Else
3667 v_Source_Instance_Id(j) := Null;
3668 End If;
3669
3670 Exception
3671 When Others Then
3672 Raise error_process_Excep ;
3673 End ;
3674 Elsif (v_Serial_Number_Control_Code(j) <> 1) and (V_Prod_txn_status(j) <> 'RECEIVED') Then
3675 v_Source_Instance_Id(j) := v_Customer_Product_Id(j);
3676 -- v_Quantity_Received(j) := Null;
3677 -- v_Locator_Id(j) := Null;
3678 v_Source_Serial_Number(j) := v_Serial_Number(j);
3679 End If;
3680 Elsif V_ActionType(j)in ('SHIP','WALK_IN_ISSUE') Then
3681 -- Check if item is serial Number controlled or not
3682 If v_Serial_Number_Control_Code(j) = 1 Then
3683
3684 -- Get all receiving transaction records for given order_header_id and order_line_id
3685 If (v_Quantity_Required(j) = 1) and (V_Prod_txn_status(j) = 'SHIPPED' ) Then
3686
3687 For Delv_Detl_rec In Single_Delivery_Details_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3688 v_Quantity_shipped(j) := Delv_Detl_rec.Shipped_quantity ;
3689 v_Locator_Id(j) := Delv_Detl_rec.locator_id ;
3690 End Loop ;
3691 Else
3692 -- Initialize following variables
3693 l_Locator_Id := NUll ;
3694 l_Quantity := NUll ;
3695 -- Check Product Transaction Status , if it is 'SHIPPED' then
3696 -- Quantity shipped is total quantity shipped against that SO
3697 -- Else Quantity shipped is what is recorded in csd_repair_history table
3698 If v_Prod_txn_Status(j) = 'SHIPPED' Then
3699 For Delv_Detl_rec In Delivery_Details_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3700 -- Cumulate quantity value for all lines split from parent line id
3701 v_Quantity_Shipped(j) := Nvl(v_Quantity_Shipped(j),0) + Delv_Detl_rec.Shipped_Quantity ;
3702 v_locator_id(j) := Delv_Detl_rec.locator_id ;
3703 End Loop ;
3704 -- Since Item is Non-Serialized, Source instance Id will be always NULL
3705 --
3706 Else
3707 For Delv_Detl_rec In Delivery_Details_cur (v_order_header_id(j), v_order_line_id(j)) Loop
3708 -- Check if current delivery_detail_id record information is updated
3709 -- in csd_repair_history table
3710 Begin
3711 l_procedure_Event := 'Getting Quantity from repair history table';
3712 Select Quantity
3713 Into l_Quantity
3714 From csd_repair_history
3715 Where event_code = 'PS'
3716 and paramn1 = Delv_Detl_rec.Delivery_Detail_id
3717 and repair_line_id = v_Repair_Line_Id(j);
3718 Exception
3719 When No_Data_Found Then
3720 l_Quantity := 0 ;
3721 End;
3722 If l_Quantity > 0 Then
3723 -- Add l_Quantity to existing quantity value in v_quantity_shipped variable
3724 v_Quantity_Shipped(j) := Nvl(v_Quantity_Shipped(j),0) + l_Quantity ;
3725 v_locator_id(j) := Delv_Detl_rec.locator_id ;
3726 End If; -- If l_Quantity > 0 Then
3727 End Loop ;
3728 End If; -- v_Prod_txn_Status(j) = 'SHIPPED'
3729 End If; -- v_Quantity_Required(j) = 1 Then
3730
3731 -- Check if Item is IB Trackable, if so then source_instance_id column is populated with
3732 -- customer_product_id information
3733 If v_comms_Nls_trackable_flag(j) = 'Y' Then
3734 -- Check if Repair Type Ref is in Advace Exchange, Exchange, Replacement
3735 if v_repair_type_ref(j) in ('AE','E','R') Then
3736 v_Non_Source_instance_id(j) := v_Customer_Product_Id(j) ;
3737 /******************************* 3742767
3738 Following code is not required as item is non serialized
3739 Begin
3740 select Serial_Number
3741 Into v_Non_Source_Serial_Number(j)
3742 from csi_item_instances
3743 where inventory_item_id = v_inventory_item_id(j)
3744 and instance_id = v_Customer_Product_Id(j)
3745 and inv_master_organization_Id = cs_std.get_item_valdn_orgzn_id;
3746
3747 v_Non_Source_instance_id(j) := v_Customer_Product_Id(j) ;
3748 Exception
3749 When No_Data_Found Then
3750 -- v_Non_Source_Serial_Number(j):= Null;
3751 v_Non_Source_instance_id(j) := v_Customer_Product_Id(j) ;
3752 -- v_Source_Instance_id(j) := Null ;
3753 -- v_Source_Serial_Number(j) := Null;
3754 End;
3755 *************** 3742767****************/
3756 -- 3742767 Else statment is commented as there will be no value for
3757 -- source_instance_id column in case ship line , since item is
3758 -- non-serial controlled
3759 -- Else
3760 -- v_Non_Source_Instance_id(j) := Null ;
3761 -- v_Non_Source_Serial_Number(j) := Null;
3762 -- v_Source_Serial_Number(j) := Null
3763 -- v_Source_Instance_Id(j) := v_Customer_Product_Id(j);
3764 End If ;
3765 End If;
3766 Elsif (v_Serial_Number_Control_Code(j) <> 1) and (V_Prod_txn_status(j) = 'SHIPPED') Then
3767 Begin
3768 -- Item is serial controlled
3769
3770 l_procedure_Event := ' Getting Shipping details for serial controlled item' ;
3771 Select wdt.shipped_quantity, wdt.locator_id ,
3772 Nvl(wdt.serial_number, wsn.fm_serial_number) Serial_number
3773 Into v_Quantity_Shipped(j), v_Locator_Id(j) , v_Source_Serial_Number(j)
3774 From wsh_delivery_details wdt,
3775 wsh_serial_numbers wsn
3776 Where wdt.Released_Status in ( 'C', 'I')
3777 and wdt.source_header_id = v_order_header_id(j)
3778 and wdt.source_line_id = v_order_line_id(j)
3779 and wdt.delivery_detail_id = wsn.delivery_detail_id(+)
3780 and rownum = 1;
3781
3782 -- If Item is IB Trackable Then get Corresponding IB Ref Number from csi_item_instances
3783 If v_comms_Nls_trackable_flag(j) = 'Y' Then
3784 Begin
3785 l_procedure_Event := 'Getting IB info for a serial item-Ship line';
3786 select instance_id
3787 Into v_Source_Instance_Id(j)
3788 from csi_item_instances
3789 where inventory_item_id = v_inventory_item_id(j)
3790 and serial_number = v_Source_Serial_Number(j) ;
3791 Exception
3792 When No_Data_Found Then
3793 v_Source_Instance_Id(j) := Null;
3794 End;
3795
3796 -- Get Non_source Column Names
3797 if v_repair_type_ref(j) in ('AE','E','R') Then
3798 Begin
3799 select Serial_Number
3800 Into v_Non_Source_Serial_Number(j)
3801 from csi_item_instances
3802 where inventory_item_id = v_inventory_item_id(j)
3803 and instance_id = v_Customer_Product_Id(j);
3804 -- and inv_master_organization_Id = cs_std.get_item_valdn_orgzn_id;
3805 -- Commented above line 3742767
3806 v_Non_Source_instance_id(j) := v_Customer_Product_Id(j) ;
3807 Exception
3808 When No_Data_Found Then
3809 v_Non_Source_Serial_Number(j):= Null;
3810 v_Non_Source_instance_id(j) := v_Customer_Product_Id(j) ;
3811 End;
3812 End If ;
3813 End If;
3814
3815 Exception
3816 When Others Then
3817 Raise error_process_Excep ;
3818 End ;
3819 End If; --v_Serial_Number_Control_Code(j) = 1
3820
3821 End if; --V_Action_Type in ('SHIP','WALK_IN_ISSUE')
3822 Exception
3823 WHEN error_process_Excep THEN
3824 l_error_text := substr(sqlerrm, 1, 1000) || 'Product Transaction Id:'||
3825 v_product_trans_id(j);
3826 ROLLBACK TO Update_prod_Txns;
3827
3828 INSERT INTO CSD_UPG_ERRORS
3829 (ORIG_SYSTEM_REFERENCE,
3830 TARGET_SYSTEM_REFERENCE,
3831 ORIG_SYSTEM_REFERENCE_ID,
3832 UPGRADE_DATETIME,
3833 ERROR_MESSAGE,
3834 MIGRATION_PHASE)
3835 VALUES ('CSD_PRODUCT_TRANSACTIONS',
3836 'CSD_PRODUCT_TRANSACTIONS',
3837 v_Product_Trans_id(j),
3838 sysdate,
3839 l_error_text,
3840 '11.5.10');
3841
3842 commit;
3843
3844 raise_application_error( -20000, 'Error while migrating CSD_PRODUCT_TRANSACTIONS. '|| l_error_text);
3845
3846 End;
3847 End Loop; -- j IN 1..l_Array_Size.COUNT
3848 -- Now use bulck collect to update csd_product_transactions_table
3849 ForAll i in 1..v_product_trans_Id.COUNT
3850 Update Csd_Product_Transactions
3851 Set source_serial_number = v_Source_Serial_Number(i) ,
3852 source_instance_id = v_source_instance_id(i) ,
3853 non_source_serial_number = v_non_source_serial_number(i) ,
3854 non_source_instance_id = v_non_source_instance_id(i) ,
3855 locator_id = v_locator_id(i) ,
3856 -- sub_inventory = v_sub_inventory(i), bug#3622825
3857 -- Lot_Number = v_Lot_Number(i), bug#3622825
3858 Quantity_Received = v_Quantity_Received(i) ,
3859 Quantity_Shipped = v_Quantity_Shipped(i),
3860 last_update_date = sysdate,
3861 last_updated_by = fnd_global.user_id,
3862 last_update_login = fnd_global.login_id
3863 Where rowid = v_rowid(i)
3864 And ( v_Source_serial_number(i) is not null
3865 Or v_Source_Instance_id(i) is Not Null
3866 Or V_Non_Source_Serial_Number(i) is Not Null
3867 or V_Non_Source_Instance_id(i) is Not Null
3868 or V_Quantity_Shipped(i) is Not Null
3869 or V_Quantity_Received(i) is Not Null
3870 or V_Locator_Id(i) is Not Null );
3871
3872 EXCEPTION
3873 When Others Then
3874 ROLLBACK TO Update_prod_Txns;
3875 l_error_text := 'Event :' || l_procedure_event ||' -Error Code :' || sqlcode || 'Error Message:' || substr(sqlerrm, 1, 1000) ;
3876
3877 INSERT INTO CSD_UPG_ERRORS
3878 (ORIG_SYSTEM_REFERENCE,
3879 TARGET_SYSTEM_REFERENCE,
3880 ORIG_SYSTEM_REFERENCE_ID,
3881 UPGRADE_DATETIME,
3882 ERROR_MESSAGE,
3883 MIGRATION_PHASE)
3884 VALUES ('CSD_PRODUCT_TRANSACTIONS',
3885 'CSD_PRODUCT_TRANSACTIONS',
3886 NULL,
3887 sysdate,
3888 l_error_text,
3889 '11.5.10');
3890
3891 commit;
3892
3893 raise_application_error( -20000, 'Error while migrating CSD_PRODUCT_TRANSACTIONS. '|| l_error_text);
3894
3895
3896 END;
3897
3898 EXIT WHEN Prod_txns_cur%NOTFOUND ;
3899
3900 END LOOP;
3901
3902 IF Prod_txns_cur%ISOPEN THEN
3903 CLOSE Prod_txns_cur;
3904 END IF;
3905 COMMIT;
3906 Exception
3907 When Others Then
3908 l_error_text := 'Error Code :' || sqlcode || 'Error Message:' || substr(sqlerrm, 1, 1000) ;
3909 -- Rollback the changes
3910 ROLLBACK;
3911
3912 INSERT INTO CSD_UPG_ERRORS
3913 (ORIG_SYSTEM_REFERENCE,
3914 TARGET_SYSTEM_REFERENCE,
3915 ORIG_SYSTEM_REFERENCE_ID,
3916 UPGRADE_DATETIME,
3917 ERROR_MESSAGE,
3918 MIGRATION_PHASE)
3919 VALUES ('CSD_PRODUCT_TRANSACTIONS',
3920 'CSD_PRODUCT_TRANSACTIONS',
3921 NULL,
3922 sysdate,
3923 l_error_text,
3924 '11.5.10');
3925
3926 Commit;
3927 raise_application_error( -20000, 'Event:' || l_Procedure_Event || '-Error while migrating CSD_PRODUCT_TRANSACTIONS. '|| l_error_text);
3928
3929 END csd_product_trans_mig3;
3930
3931 END CSD_Migrate_From_115X_PKG3;