[Home] [Help]
PACKAGE BODY: APPS.INV_OPM_LOT_MIGRATION
Source
1 PACKAGE BODY INV_OPM_Lot_Migration AS
2 /* $Header: INVLTMGB.pls 120.21.12020000.2 2012/07/03 08:47:27 shalchen ship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | INVLTMGB.pls |
10 | |
11 | TYPE |
12 | |
13 | PACKAGE NAME |
14 | INV_OPM_Lot_Migration |
15 | |
16 | DESCRIPTION |
17 | This package contains the procedure used for lot migration for OPM |
18 | convergence project. These procedure are meant for migration only. |
19 | |
20 | Contents |
21 | get_ODM_lot |
22 | |
23 | HISTORY |
24 | Created - Jatinder Gogna - 3/22/05 |
25 | |
26 | Jatinder - 11/30/06 - Use correct balance status from ic_lots_mst_mig |
27 | B5690654 |
28 | |
29 | Kedar Bavadekar 04/20/10 - Populate vendor_id, name and lot number |
30 | for Bug#9600386 in procedure |
31 | migrate_OPM_lot_to_ODM |
32 | ASATPUTE 06/22/2011 Bug 12591257 Copy the vendor_lot_no even if there |
33 | is NO shipvend_id |
34 | Shalchen 06/27/2012 Bug 14203528 comment the assignment statement to |
35 | vendor_id in procedure migrate_OPM_lot_to_ODM |
36 +==========================================================================+
37 */
38
39 /* Global variables */
40 g_init_mig PLS_INTEGER;
41 g_lot_id NUMBER;
42 g_item_id NUMBER;
43 g_whse_code VARCHAR2(4);
44 g_orgn_code VARCHAR2(4);
45 g_location VARCHAR2(16);
46 g_organization_id NUMBER;
47 g_lot_number VARCHAR2(80);
48 g_parent_lot_number VARCHAR2(80);
49 g_desc_flex_conflict PLS_INTEGER;
50 g_attribute_context PLS_INTEGER;
51 g_attribute1 PLS_INTEGER;
52 g_attribute2 PLS_INTEGER;
53 g_attribute3 PLS_INTEGER;
54 g_attribute4 PLS_INTEGER;
55 g_attribute5 PLS_INTEGER;
56 g_attribute6 PLS_INTEGER;
57 g_attribute7 PLS_INTEGER;
58 g_attribute8 PLS_INTEGER;
59 g_attribute9 PLS_INTEGER;
60 g_attribute10 PLS_INTEGER;
61 g_attribute11 PLS_INTEGER;
62 g_attribute12 PLS_INTEGER;
63 g_attribute13 PLS_INTEGER;
64 g_attribute14 PLS_INTEGER;
65 g_attribute15 PLS_INTEGER;
66 g_attribute16 PLS_INTEGER;
67 g_attribute17 PLS_INTEGER;
68 g_attribute18 PLS_INTEGER;
69 g_attribute19 PLS_INTEGER;
70 g_attribute20 PLS_INTEGER;
71 g_attribute21 PLS_INTEGER;
72 g_attribute22 PLS_INTEGER;
73 g_attribute23 PLS_INTEGER;
74 g_attribute24 PLS_INTEGER;
75 g_attribute25 PLS_INTEGER;
76 g_attribute26 PLS_INTEGER;
77 g_attribute27 PLS_INTEGER;
78 g_attribute28 PLS_INTEGER;
79 g_attribute29 PLS_INTEGER;
80 g_attribute30 PLS_INTEGER;
81
82
83 /*====================================================================
84 -- PROCEDURE:
85 -- migrate_OPM_lot_to_ODM
86 --
87 -- DESCRIPTION:
88 -- Internal routine to migrate OPM lots to Oracle inventory.
89 -- This procedure should not be called on its own. Call the
90 -- get_ODM_lot procedure instead.
91 --
92 -- PARAMETERS:
93 --
94 -- SYNOPSIS:
95 --
96 -- HISTORY
97 -- Jatinder Gogna - Created - 03/25/05
98 -- RLNAGARA Bug 6607319 Status is passed as NULL for Material Status Migration ME
99 -- KBAVADEK Bug#9600386. Populate vedor id, name and lot information
100 -- ASATPUTE 06/22/2011 Bug 12591257 Copy the vendor_lot_no even if there is NO shipvend_id
101 -- Shalchen Bug #14203528 comment the assignment statement to vendor_id which will
102 cause the vendor info can not migrated to discrete.
103 --====================================================================*/
104
105 PROCEDURE migrate_OPM_lot_to_ODM
106 ( p_migration_run_id IN NUMBER
107 , p_item_id IN NUMBER
108 , p_lot_id IN NUMBER
109 , p_organization_id IN NUMBER
110 , p_whse_code IN VARCHAR2
111 , p_location IN VARCHAR2
112 , p_lot_status IN VARCHAR2
113 , p_commit IN VARCHAR2
114 , x_lot_number IN OUT NOCOPY VARCHAR2
115 , x_parent_lot_number IN OUT NOCOPY VARCHAR2
116 , x_failure_count OUT NOCOPY NUMBER
117 ) IS
118 PRAGMA AUTONOMOUS_TRANSACTION;
119 l_lot_rec mtl_lot_numbers%ROWTYPE;
120 o_lot_rec mtl_lot_numbers%ROWTYPE;
121 o_item_rec INV_ITEM_API.Item_rec_type;
122 -- l_error_tbl INV_ITEM_API.Error_tbl_type;
123 l_opm_lot ic_lots_mst%ROWTYPE;
124 l_inventory_item_id NUMBER;
125 l_count NUMBER;
126 l_lot_number VARCHAR2(80);
127 l_parent_lot_number VARCHAR2(80);
128 l_status_ctl NUMBER;
129 l_default_status VARCHAR2(4);
130 l_lot_status VARCHAR2(4);
131 l_whse_orgn_code VARCHAR2(4);
132 l_status_id NUMBER;
133 l_maturity_date DATE;
134 l_hold_date DATE;
135
136 l_return_status VARCHAR2(10);
137 l_msg_count NUMBER;
138 l_msg_data VARCHAR2(2000);
139 l_field_name VARCHAR(50);
140 l_field_value VARCHAR(50);
141 v_rowid varchar2(1000);
142 i PLS_INTEGER;
143
144 BEGIN
145 x_failure_count := 0;
146 -- Get the OPM Lot Master Details
147 BEGIN
148 SELECT * INTO l_opm_lot
149 FROM ic_lots_mst
150 WHERE item_id = p_item_id and
151 lot_id = p_lot_id;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 -- Log error
155 -- dbms_output.put_line ('Invalid Lot id : '||to_char(p_lot_id));
156 GMA_COMMON_LOGGING.gma_migration_central_log (
157 p_run_id => p_migration_run_id,
158 p_log_level => FND_LOG.LEVEL_ERROR,
159 p_message_token => 'GMI_MIG_INVALID_LOT_ID',
160 p_table_name => 'IC_LOTS_MST',
161 p_context => 'LOTS',
162 p_param1 => INV_GMI_Migration.lot(p_lot_id),
163 p_param2 => INV_GMI_Migration.item(p_item_id),
164 p_param3 => NULL,
165 p_param4 => NULL,
166 p_param5 => NULL,
167 p_db_error => NULL,
168 p_app_short_name => 'GMI');
169 x_failure_count := x_failure_count + 1;
170 RETURN;
171 END;
172
173 -- Check if the item has already been migrated to discrete
174 BEGIN
175 INV_OPM_Item_Migration.get_ODM_item (
176 p_migration_run_id => p_migration_run_id,
177 p_item_id => p_item_id,
178 p_organization_id => p_organization_id,
179 p_mode => NULL,
180 p_commit => FND_API.G_TRUE,
181 x_inventory_item_id => l_inventory_item_id,
182 x_failure_count => x_failure_count);
183 IF (x_failure_count > 0) THEN
184 -- Log Error
185 -- dbms_output.put_line ('Item migration failed for Item id : '||to_char(p_item_id));
186 GMA_COMMON_LOGGING.gma_migration_central_log (
187 p_run_id => p_migration_run_id,
188 p_log_level => FND_LOG.LEVEL_ERROR,
189 p_message_token => 'GMI_MIG_ITEM_MIG_FAILED',
190 p_table_name => 'IC_LOTS_MST',
191 p_context => 'LOTS',
192 p_param1 => INV_GMI_Migration.org(p_organization_id),
193 p_param2 => INV_GMI_Migration.item(p_item_id),
194 p_param3 => NULL,
195 p_param4 => NULL,
196 p_param5 => NULL,
197 p_db_error => NULL,
198 p_app_short_name => 'GMI');
199 x_failure_count := x_failure_count + 1;
200 RETURN;
201 END IF;
202 END;
203
204
205 -- Set local variables based upon OPM lot master definition
206 BEGIN
207 -- Get lot numbers for discrete
208 l_lot_status := p_lot_status;
209 IF (x_lot_number is NULL) THEN
210 l_field_name := 'GMI Migration Parameters';
211 l_field_value := NULL;
212 SELECT l_opm_lot.lot_no || DECODE (l_opm_lot.sublot_no, NULL, NULL,
213 (SELECT lot_sublot_delimiter FROM gmi_migration_parameters)) ||
214 l_opm_lot.sublot_no,
215 DECODE(sublot_ctl, 1, DECODE(l_opm_lot.sublot_no, NULL, NULL,
216 l_opm_lot.lot_no)),
217 status_ctl, lot_status
218 INTO x_lot_number, x_parent_lot_number, l_status_ctl, l_default_status
219 FROM ic_item_mst_b
220 WHERE
221 item_id = p_item_id;
222
223 -- Get the inventory status for the lot
224 IF (l_status_ctl = 1) THEN
225 BEGIN
226 SELECT lot_status
227 INTO l_lot_status
228 FROM ic_loct_inv
229 WHERE
230 item_id = p_item_id and
231 lot_id = p_lot_id and
232 whse_code = p_whse_code and
233 rownum = 1;
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 NULL;
237 END;
238 END IF;
239 -- insert it into ic_lots_mst_mig table
240 IF (l_lot_status is NULL) THEN
241 l_lot_status := l_default_status;
242 END IF;
243 INSERT into ic_lots_mst_mig (
244 item_id,
245 lot_id,
246 organization_id,
247 whse_code,
248 location,
249 status,
250 parent_lot_number,
251 lot_number,
252 migrated_ind,
253 additional_status_lot,
254 user_updated_ind,
255 creation_date,
256 created_by,
257 last_update_date,
258 last_updated_by,
259 last_update_login)
260 VALUES (
261 p_item_id,
262 p_lot_id,
263 p_organization_id,
264 p_whse_code,
265 p_location, --rlnagara 2 Material Status Migration ME - dont know why NULL was passed here even though we had location value.
266 l_lot_status, --rlnagara 2 Material Status Migration ME - dont know why NULL was passed here even though we had lot status value.
267 x_parent_lot_number,
268 x_lot_number,
269 0,
270 0,
271 -1,
272 sysdate,
273 0,
274 sysdate,
275 0,
276 NULL);
277
278 END IF;
279
280 -- Get CPG fields
281 l_field_name := 'Maturity and Hold Days';
282 l_field_value := to_char(p_lot_id);
283 BEGIN
284 SELECT ic_matr_date, ic_hold_date
285 INTO l_maturity_date, l_hold_date
286 FROM ic_lots_cpg
287 WHERE
288 item_id = p_item_id AND
289 lot_id = p_lot_id;
290 EXCEPTION
291 WHEN NO_DATA_FOUND THEN
292 NULL;
293 END;
294
295
296 /* rlnagara 2 Material Status Migration ME - As we are passing status_id as NULL while creating the l_lot_rec, below code is not needed.
297 -- Get status id
298 -- Jatinder - 11/30/06 - Use correct balance status from ic_lots_mst_mig B5690654
299 l_status_id := NULL;
300 IF (l_lot_status IS NOT NULL) THEN
301 l_field_name := 'Lot Status Id';
302 l_field_value := l_lot_status;
303 l_field_name := 'Lot Status';
304 SELECT status_id
305 INTO l_status_id
306 FROM ic_lots_sts
307 WHERE
308 lot_status = l_lot_status;
309 END IF;
310 */
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 -- dbms_output.put_line ('Could not find '||l_field_name||' for '||l_field_value);
315 GMA_COMMON_LOGGING.gma_migration_central_log (
316 p_run_id => p_migration_run_id,
317 p_log_level => FND_LOG.LEVEL_ERROR,
318 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
319 p_table_name => 'IC_LOTS_MST',
320 p_context => 'LOTS',
321 p_param1 => l_field_name,
322 p_param2 => l_field_value,
323 p_param3 => NULL,
324 p_param4 => NULL,
325 p_param5 => NULL,
326 p_db_error => NULL,
327 p_app_short_name => 'GMI');
328 x_failure_count := x_failure_count + 1;
329 RETURN;
330 END;
331
332 -- Prepare the data for the Lot creation
333 l_lot_rec.INVENTORY_ITEM_ID := l_inventory_item_id;
334 l_lot_rec.ORGANIZATION_ID := p_organization_id;
335 l_lot_rec.LOT_NUMBER := x_lot_number;
336 l_lot_rec.EXPIRATION_DATE := l_opm_lot.expire_date;
337 l_lot_rec.DISABLE_FLAG := NULL;
338 IF (l_opm_lot.inactive_ind = 1 or l_opm_lot.delete_mark = 1) THEN
339 l_lot_rec.DISABLE_FLAG := 1;
340 END IF;
341 l_lot_rec.REQUEST_ID := NULL;
342 l_lot_rec.PROGRAM_APPLICATION_ID := NULL;
343 l_lot_rec.PROGRAM_ID := NULL;
344 l_lot_rec.PROGRAM_UPDATE_DATE := NULL;
345 l_lot_rec.GEN_OBJECT_ID := NULL;
346 l_lot_rec.DESCRIPTION := l_opm_lot.lot_desc;
347
348 /* Start for fix for Bug#9600386 */
349
350 l_lot_rec.VENDOR_ID := NULL;
351 l_lot_rec.VENDOR_NAME := NULL;
352 l_lot_rec.SUPPLIER_LOT_NUMBER := NULL;
353 -- Bug 12591257 Copy the vendor_lot_no even if there is NO shipvend_id
354 -- It was found that in BOTH 11i and R12 Vendor name (i.e. ID) is NOT
355 -- mandatory, customers were found to have records with ONLY
356 -- vendor_lot_no column populated with shipvend_id being NULL
357
358 l_lot_rec.SUPPLIER_LOT_NUMBER := l_opm_lot.vendor_lot_no ;
359
360 IF (l_opm_lot.shipvend_id is not null ) THEN
361 BEGIN
362
363 SELECT of_vendor_id,
364 vendor_name
365 INTO l_lot_rec.vendor_id,
366 l_lot_rec.vendor_name
367 FROM PO_VEND_MST
368 WHERE vendor_id = l_opm_lot.shipvend_id ;
369
370
371 IF (l_lot_rec.vendor_id is null ) THEN
372
373 GMA_COMMON_LOGGING.gma_migration_central_log (
374 p_run_id => p_migration_run_id,
375 p_log_level => FND_LOG.LEVEL_PROCEDURE,
376 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
377 p_table_name => 'IC_LOTS_MST',
378 p_context => 'LOTS',
379 p_param1 => 'Supplier Id/Name' ,
380 p_param2 => x_lot_number,
381 p_param3 => NULL,
382 p_param4 => NULL,
383 p_param5 => NULL,
384 p_db_error => NULL,
385 p_app_short_name => 'GMI');
386 END IF ;
387
388 EXCEPTION
389
390 WHEN NO_DATA_FOUND then
391
392 GMA_COMMON_LOGGING.gma_migration_central_log (
393 p_run_id => p_migration_run_id,
394 p_log_level => FND_LOG.LEVEL_PROCEDURE,
395 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
396 p_table_name => 'IC_LOTS_MST',
397 p_context => 'LOTS',
398 p_param1 => 'Supplier Id/Name' ,
399 p_param2 => x_lot_number,
400 p_param3 => NULL,
401 p_param4 => NULL,
402 p_param5 => NULL,
403 p_db_error => NULL,
404 p_app_short_name => 'GMI');
405 END ;
406 END IF ;
407
408
409 /* End for fix for Bug#9600386 */
410
411 l_lot_rec.GRADE_CODE := l_opm_lot.qc_grade;
412 l_lot_rec.ORIGINATION_DATE := l_opm_lot.lot_created;
413 l_lot_rec.DATE_CODE := NULL;
414 l_lot_rec.STATUS_ID := NULL; --RLNAGARA Material Status Migration ME - Status is not passed.
415 l_lot_rec.CHANGE_DATE := NULL;
416 l_lot_rec.AGE := NULL;
417 --BEGIN BUG #14203528
418 --comment the assignment statement to vendor_id which will cause the vendor info can not migrated to discrete.
419 --l_lot_rec.VENDOR_ID := NULL;
420 --END BUG #14203528
421 l_lot_rec.TERRITORY_CODE := NULL;
422 l_lot_rec.PARENT_LOT_NUMBER := x_parent_lot_number;
423 l_lot_rec.ORIGINATION_TYPE := l_opm_lot.origination_type;
424 l_lot_rec.EXPIRATION_ACTION_CODE := l_opm_lot.expaction_code;
425 IF (l_opm_lot.expaction_date < l_lot_rec.ORIGINATION_DATE) THEN
426 l_lot_rec.EXPIRATION_ACTION_DATE := l_lot_rec.ORIGINATION_DATE;
427 ELSE
428 l_lot_rec.EXPIRATION_ACTION_DATE := l_opm_lot.expaction_date;
429 END IF;
430 IF (l_opm_lot.retest_date < l_lot_rec.ORIGINATION_DATE) THEN
431 l_lot_rec.RETEST_DATE := l_lot_rec.ORIGINATION_DATE;
432 ELSE
433 l_lot_rec.RETEST_DATE := l_opm_lot.retest_date;
434 END IF;
435 IF (l_hold_date < l_lot_rec.ORIGINATION_DATE) THEN
436 l_lot_rec.HOLD_DATE := l_lot_rec.ORIGINATION_DATE;
437 ELSE
438 l_lot_rec.HOLD_DATE := l_hold_date;
439 END IF;
440 IF (l_maturity_date < l_lot_rec.ORIGINATION_DATE) THEN
441 l_lot_rec.MATURITY_DATE := l_lot_rec.ORIGINATION_DATE;
442 ELSE
443 l_lot_rec.MATURITY_DATE := l_maturity_date;
444 END IF;
445 -- l_lot_rec.INVENTORY_ATP_CODE := NULL; -- ????
446 -- l_lot_rec.RESERVABLE_TYPE := NULL; -- ????
447 -- l_lot_rec.AVAILABILITY_TYPE := NULL; -- ????
448
449
450 IF (g_attribute_context = 1 and l_lot_rec.ATTRIBUTE_CATEGORY is NULL) THEN
451 l_lot_rec.ATTRIBUTE_CATEGORY := l_opm_lot.attribute_category;
452 END IF;
453 IF (g_attribute1 = 1 and l_lot_rec.attribute1 is NULL) THEN l_lot_rec.attribute1 := l_opm_lot.attribute1; END IF;
454 IF (g_attribute2 = 1 and l_lot_rec.attribute2 is NULL) THEN l_lot_rec.attribute2 := l_opm_lot.attribute2; END IF;
455 IF (g_attribute3 = 1 and l_lot_rec.attribute3 is NULL) THEN l_lot_rec.attribute3 := l_opm_lot.attribute3; END IF;
456 IF (g_attribute4 = 1 and l_lot_rec.attribute4 is NULL) THEN l_lot_rec.attribute4 := l_opm_lot.attribute4; END IF;
457 IF (g_attribute5 = 1 and l_lot_rec.attribute5 is NULL) THEN l_lot_rec.attribute5 := l_opm_lot.attribute5; END IF;
458 IF (g_attribute6 = 1 and l_lot_rec.attribute6 is NULL) THEN l_lot_rec.attribute6 := l_opm_lot.attribute6; END IF;
459 IF (g_attribute7 = 1 and l_lot_rec.attribute7 is NULL) THEN l_lot_rec.attribute7 := l_opm_lot.attribute7; END IF;
460 IF (g_attribute8 = 1 and l_lot_rec.attribute8 is NULL) THEN l_lot_rec.attribute8 := l_opm_lot.attribute8; END IF;
461 IF (g_attribute9 = 1 and l_lot_rec.attribute9 is NULL) THEN l_lot_rec.attribute9 := l_opm_lot.attribute9; END IF;
462 IF (g_attribute10 = 1 and l_lot_rec.attribute10 is NULL) THEN l_lot_rec.attribute10 := l_opm_lot.attribute10; END IF;
463 IF (g_attribute11 = 1 and l_lot_rec.attribute11 is NULL) THEN l_lot_rec.attribute11 := l_opm_lot.attribute11; END IF;
464 IF (g_attribute12 = 1 and l_lot_rec.attribute12 is NULL) THEN l_lot_rec.attribute12 := l_opm_lot.attribute12; END IF;
465 IF (g_attribute13 = 1 and l_lot_rec.attribute13 is NULL) THEN l_lot_rec.attribute13 := l_opm_lot.attribute13; END IF;
466 IF (g_attribute14 = 1 and l_lot_rec.attribute14 is NULL) THEN l_lot_rec.attribute14 := l_opm_lot.attribute14; END IF;
467 IF (g_attribute15 = 1 and l_lot_rec.attribute15 is NULL) THEN l_lot_rec.attribute15 := l_opm_lot.attribute15; END IF;
468
469 l_lot_rec.CREATION_DATE := SYSDATE;
470 l_lot_rec.CREATED_BY := l_opm_lot.created_by;
471 l_lot_rec.LAST_UPDATE_DATE := SYSDATE;
472 l_lot_rec.LAST_UPDATED_BY := l_opm_lot.last_updated_by;
473 l_lot_rec.LAST_UPDATE_LOGIN := -1;
474
475 -- Check if the lot already exists
476 SELECT count(*)
477 INTO l_count
478 FROM mtl_lot_numbers
479 WHERE
480 organization_id = p_organization_id AND
481 inventory_item_id = l_inventory_item_id AND
482 lot_number = x_lot_number;
483
484 IF (l_count > 0) THEN
485 -- Log error (warning)
486 -- dbms_output.put_line ('Lot already exists for org id, item id, lot num : '||to_char(p_organization_id)||', '||to_char(l_inventory_item_id)||', '||x_lot_number);
487 GMA_COMMON_LOGGING.gma_migration_central_log (
488 p_run_id => p_migration_run_id,
489 p_log_level => FND_LOG.LEVEL_PROCEDURE,
490 p_message_token => 'GMI_MIG_LOT_ALREADY_EXISTS',
491 p_table_name => 'IC_LOTS_MST',
492 p_context => 'LOTS',
493 p_param1 => INV_GMI_Migration.org(p_organization_id),
494 p_param2 => INV_GMI_Migration.ditem(p_organization_id,l_inventory_item_id),
495 p_param3 => x_lot_number,
496 p_param4 => NULL,
497 p_param5 => NULL,
498 p_db_error => NULL,
499 p_app_short_name => 'GMI');
500
501 /* Start for fix for Bug#9600386 */
502
503 UPDATE MTL_LOT_NUMBERS
504 SET VENDOR_ID = l_lot_rec.vendor_id,
505 VENDOR_NAME = l_lot_rec.vendor_name,
506 SUPPLIER_LOT_NUMBER = l_opm_lot.vendor_lot_no
507 WHERE organization_id = p_organization_id AND
508 inventory_item_id = l_inventory_item_id AND
509 lot_number = x_lot_number AND
510 (VENDOR_ID IS NULL AND
511 VENDOR_NAME IS NULL AND
512 SUPPLIER_LOT_NUMBER IS NULL ) ;
513
514 /* End for fix for Bug#9600386 */
515
516 ELSE -- migrate the lot
517 -- Call the API to create the lot
518
519 INV_LOT_API_PUB.Create_Inv_lot (
520 x_return_status => l_return_status,
521 x_msg_count => l_msg_count,
522 x_msg_data => l_msg_data,
523 x_row_id => v_rowid,
524 x_lot_rec => o_lot_rec,
525 p_lot_rec => l_lot_rec,
526 p_source => NULL,
527 p_api_version => 1.0,
528 p_origin_txn_id => NULL);
529
530 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
531 -- Log Error
532 x_failure_count := x_failure_count + 1;
533 FOR i in 1..l_msg_count LOOP
534 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
535 GMA_COMMON_LOGGING.gma_migration_central_log (
536 p_run_id => p_migration_run_id,
537 p_log_level => FND_LOG.LEVEL_ERROR,
538 p_message_token => 'GMI_UNEXPECTED_ERROR',
539 p_table_name => 'IC_LOTS_MST',
540 p_context => 'LOTS',
541 p_token1 => 'ERROR',
542 p_param1 => fnd_msg_pub.get_detail(i, NULL),
543 p_param2 => NULL,
544 p_param3 => NULL,
545 p_param4 => NULL,
546 p_param5 => NULL,
547 p_db_error => NULL,
548 p_app_short_name => 'GMI');
549 END LOOP;
550 ROLLBACK;
551 RETURN;
552 END IF;
553
554 -- Lot created successfully
555 g_lot_number := x_lot_number;
556 g_parent_lot_number := x_parent_lot_number;
557 END IF;
558
559 IF p_whse_code IS NOT NULL THEN
560 SELECT orgn_code INTO l_whse_orgn_code
561 FROM ic_whse_mst
562 WHERE whse_code = p_whse_code;
563 END IF;
564
565 UPDATE ic_lots_mst_mig
566 SET
567 organization_id = p_organization_id,
568 migrated_ind = 1,
569 last_update_date = sysdate,
570 last_updated_by = 0
571 WHERE
572 ( organization_id = p_organization_id OR
573 (organization_id IS NULL AND -- whse mapped to subinventory
574 whse_mapping_code = l_whse_orgn_code) OR
575 (organization_id IS NULL AND nvl(whse_mapping_code, ' ') <> l_whse_orgn_code AND
576 whse_code = p_whse_code)) AND -- whse not a subinventory
577 lot_number = g_lot_number and
578 item_id = p_item_id and -- Added this to use index.
579 lot_id = p_lot_id; -- Added this to use index.
580
581 -- Autonomous transaction commit
582 IF (p_commit <> FND_API.G_FALSE) THEN
583 COMMIT;
584 ELSE
585 ROLLBACK;
586 END IF;
587 EXCEPTION
588 WHEN OTHERS THEN
589 ROLLBACK;
590 RAISE;
591 END;
592
593 /*====================================================================
594 -- PROCEDURE:
595 -- validate_desc_flex_definition
596 --
597 -- DESCRIPTION:
598 -- This PL/SQL procedure is used to validate the conflict
599 -- in desc flexfield usage for discrete and OPM Lots.
600 --
601 -- PARAMETERS:
602 -- P_migration_run_id - id to use to right to migration log
603 --
604 -- SYNOPSIS:
605 -- validate_desc_flex_definition(p_migartion_id => l_migration_id);
606 --
607 -- HISTORY
608 -- Jatinder Gogna - Created - 03/25/05
609 -- Archana Mundhe Bug 5720001
610 -- Replaced token GMI_MIG_DFLEX_CONTEXT_CONFLICT with GMI_MIG_DFLEX_COL_CONFLICT.
611 --====================================================================*/
612
613 PROCEDURE validate_desc_flex_definition
614 (p_migration_run_id IN NUMBER) IS
615
616 CURSOR c_get_desc_felx_col_conflict IS
617
618 SELECT col.descriptive_flex_context_code,
619 col.application_column_name,
620 col.end_user_column_name
621 FROM fnd_descr_flex_column_usages col,
622 fnd_descr_flex_contexts cont
623 WHERE
624 col.application_id = 551 and
625 col.descriptive_flexfield_name = 'LOTS_FLEX' and
626 col.enabled_flag = 'Y' and
627 col.application_id = cont.application_id and
628 col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
629 col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
630 cont.enabled_flag = 'Y' and
631 col.application_column_name in (
632 SELECT col2.application_column_name
633 FROM fnd_descr_flex_column_usages col2,
634 fnd_descr_flex_contexts cont2
635 WHERE
636 col2.application_id = 401 and
637 col2.descriptive_flexfield_name = 'MTL_LOT_NUMBERS' and
638 col2.enabled_flag = 'Y' and
639 col.application_id = cont2.application_id and
640 col.descriptive_flexfield_name = cont2.descriptive_flexfield_name and
641 col.descriptive_flex_context_code = cont2.descriptive_flex_context_code and
642 cont2.enabled_flag = 'Y' );
643
644 CURSOR c_get_opm_desc_flex_cols IS
645
646 SELECT col.descriptive_flex_context_code,
647 col.application_column_name,
648 col.end_user_column_name
649 FROM fnd_descr_flex_column_usages col,
650 fnd_descr_flex_contexts cont
651 WHERE
652 col.application_id = 551 and
653 col.descriptive_flexfield_name = 'LOTS_FLEX' and
654 col.enabled_flag = 'Y' and
655 col.application_id = cont.application_id and
656 col.descriptive_flexfield_name = cont.descriptive_flexfield_name and
657 col.descriptive_flex_context_code = cont.descriptive_flex_context_code and
658 cont.enabled_flag = 'Y';
659
660 l_opm_context VARCHAR2(30);
661 l_odm_context VARCHAR2(30);
662 BEGIN
663 g_desc_flex_conflict := 0;
664 BEGIN
665 SELECT cont.descriptive_flex_context_code
666 INTO l_opm_context
667 FROM fnd_descr_flex_contexts cont
668 WHERE cont.application_id = 551 and
669 cont.descriptive_flexfield_name = 'LOTS_FLEX' and
670 cont.enabled_flag = 'Y' and
671 cont.global_flag = 'N' and
672 rownum = 1;
673 g_attribute_context := 1;
674 EXCEPTION
675 WHEN NO_DATA_FOUND THEN
676 NULL;
677 END;
678 BEGIN
679 SELECT cont.descriptive_flex_context_code
680 INTO l_odm_context
681 FROM fnd_descr_flex_contexts cont
682 WHERE cont.application_id = 401 and
683 cont.descriptive_flexfield_name = 'MTL_LOT_NUMBERS' and
684 cont.enabled_flag = 'Y' and
685 cont.global_flag = 'N' and
686 rownum = 1;
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 NULL;
690 END;
691 IF (l_opm_context is not NULL and l_odm_context is not NULL) THEN
692 g_desc_flex_conflict := 1;
693 -- Log Error
694 -- dbms_output.put_line ('Desc flexfield conflict. OPM context: '|| l_opm_context ||', ODM context: '|| l_odm_context);
695 GMA_COMMON_LOGGING.gma_migration_central_log (
696 p_run_id => p_migration_run_id,
697 p_log_level => FND_LOG.LEVEL_ERROR,
698 p_message_token => 'GMI_MIG_DFLEX_COL_CONFLICT', -- Bug 5720001
699 p_table_name => 'IC_LOTS_MST',
700 p_context => 'LOTS',
701 p_param1 => l_opm_context,
702 p_param2 => l_odm_context,
703 p_param3 => NULL,
704 p_param4 => NULL,
705 p_param5 => NULL,
706 p_db_error => NULL,
707 p_app_short_name => 'GMI');
708 END IF;
709
710 -- Check if any OPM item decsriptive flexfield column is used in
711 -- Discrete item flexfield
712
713 FOR conflict_columns in c_get_desc_felx_col_conflict LOOP
714 -- If we are here, that means we have a conflict
715 g_desc_flex_conflict := 1;
716
717 -- dbms_output.put_line ('Desc flexfield column conflict.');
718 GMA_COMMON_LOGGING.gma_migration_central_log (
719 p_run_id => p_migration_run_id,
720 p_log_level => FND_LOG.LEVEL_ERROR,
721 p_message_token => 'GMI_MIG_DFLEX_COL_CONFLICT', -- Bug 5720001
722 p_table_name => 'IC_LOTS_MST',
723 p_context => 'LOTS',
724 p_param1 => conflict_columns.descriptive_flex_context_code,
725 p_param2 => conflict_columns.end_user_column_name,
726 p_param3 => conflict_columns.application_column_name,
727 p_param4 => NULL,
728 p_param5 => NULL,
729 p_db_error => NULL,
730 p_app_short_name => 'GMI');
731 END LOOP;
732
733 -- If no conflict is found, set the control variable for item migration
734 FOR opm_desc_cols in c_get_opm_desc_flex_cols LOOP
735
736 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE1') THEN g_attribute1 := 1; END IF;
737 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE2') THEN g_attribute2 := 1; END IF;
738 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE3') THEN g_attribute3 := 1; END IF;
739 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE4') THEN g_attribute4 := 1; END IF;
740 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE5') THEN g_attribute5 := 1; END IF;
741 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE6') THEN g_attribute6 := 1; END IF;
742 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE7') THEN g_attribute7 := 1; END IF;
743 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE8') THEN g_attribute8 := 1; END IF;
744 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE9') THEN g_attribute9 := 1; END IF;
745 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE10') THEN g_attribute10 := 1; END IF;
746 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE11') THEN g_attribute11 := 1; END IF;
747 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE12') THEN g_attribute12 := 1; END IF;
748 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE13') THEN g_attribute13 := 1; END IF;
749 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE14') THEN g_attribute14 := 1; END IF;
750 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE15') THEN g_attribute15 := 1; END IF;
751 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE16') THEN g_attribute16 := 1; END IF;
752 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE17') THEN g_attribute17 := 1; END IF;
753 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE18') THEN g_attribute18 := 1; END IF;
754 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE19') THEN g_attribute19 := 1; END IF;
755 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE20') THEN g_attribute20 := 1; END IF;
756 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE21') THEN g_attribute21 := 1; END IF;
757 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE22') THEN g_attribute22 := 1; END IF;
758 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE23') THEN g_attribute23 := 1; END IF;
759 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE24') THEN g_attribute24 := 1; END IF;
760 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE25') THEN g_attribute25 := 1; END IF;
761 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE26') THEN g_attribute26 := 1; END IF;
762 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE27') THEN g_attribute27 := 1; END IF;
763 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE28') THEN g_attribute28 := 1; END IF;
764 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE29') THEN g_attribute29 := 1; END IF;
765 IF (opm_desc_cols.application_column_name = 'ATTRIBUTE30') THEN g_attribute30 := 1; END IF;
766 END LOOP;
767 END;
768
769 /*====================================================================
770 -- PROCEDURE:
771 -- get_ODM_lot
772 --
773 -- DESCRIPTION:
774 -- This PL/SQL procedure is used to get the lot number for
775 -- an OPM lot. If the OPM lot is not migrated, it will migrate the
776 -- the lot and return the discrete lot number.
777 --
778 -- PARAMETERS:
779 -- P_migration_run_id - id to use to right to migration log
780 -- p_inventory_item_id - Discrete item id.
781 -- p_lot_no - OPM Lot No
782 -- p_sublot_no - OPM SubLot No
783 -- p_organization_id - Inventory organization of the lot in Oracle
784 -- Inventory. Lot will be migrated to this organization.
785 -- p_locator_id - Locator ( corresponding to OPM loction) where this
786 -- lot exists.
787 -- p_commit - flag to indicate if commit should be performed.
788 -- x_lot_number - Discrete Lot Number
789 -- x_parent_lot_number - Discrete Parent Lot Number
790 -- x_failure_count - Number of exceptions occurred.
791 --
792 -- SYNOPSIS:
793 -- get_ODM_lot( p_migartion_id => l_migration_id,
794 -- p_inventory_item_id => l_inventory_item_id,
795 -- p_lot_no => l_lot_no,
796 -- p_sublot_no => l_sublot_no,
797 -- p_organization_id => l_organization_id,
798 -- p_locator_id => l_locator_id,
799 -- p_commit => FND_API.G_TRUE,
800 -- x_lot_number => l_lot_number,
801 -- x_parent_lot_number => l_parent_lot_number,
802 -- x_failure_count => l_failure_count);
803 --
804 -- HISTORY
805 -- Jatinder Gogna - Created - 03/25/05
806 --====================================================================*/
807
808 PROCEDURE get_ODM_lot
809 ( p_migration_run_id IN NUMBER
810 , p_inventory_item_id IN NUMBER
811 , p_lot_no IN VARCHAR2
812 , p_sublot_no IN VARCHAR2
813 , p_organization_id IN NUMBER
814 , p_locator_id IN NUMBER
815 , p_commit IN VARCHAR2
816 , x_lot_number OUT NOCOPY VARCHAR2
817 , x_parent_lot_number OUT NOCOPY VARCHAR2
818 , x_failure_count OUT NOCOPY NUMBER) IS
819
820 l_item_id NUMBER;
821 l_lot_id NUMBER;
822 l_loc_organization_id NUMBER;
823 l_whse_code VARCHAR2(4);
824 l_location_whse VARCHAR2(4);
825 l_location VARCHAR2(16);
826 l_field_name VARCHAR(50);
827 l_field_value VARCHAR(50);
828 BEGIN
829 x_failure_count := 0;
830 BEGIN
831 l_field_name := 'Matching OPM Item';
832 l_field_value := to_char(p_organization_id) ||', '||to_char(p_inventory_item_id);
833 SELECT i.item_id
834 INTO l_item_id
835 FROM ic_item_mst_b i, mtl_system_items_b d
836 WHERE
837 d.organization_id = p_organization_id AND
838 d.inventory_item_id = p_inventory_item_id AND
839 d.segment1 = i.item_no;
840
841 l_field_name := 'OPM Lot id';
842 l_field_value := p_lot_no;
843 SELECT lot_id
844 INTO l_lot_id
845 FROM ic_lots_mst
846 WHERE
847 item_id = l_item_id AND
848 lot_no = p_lot_no AND
849 nvl(sublot_no, ' ') = nvl(p_sublot_no, ' ');
850
851 l_field_name := 'Migrated Warehouse';
852 l_field_value := p_organization_id;
853
854 -- 5412510 - Added ROWNUM as there can be multiple whse for the id
855 -- when whse is migrated as the subinventory
856 -- The whse is later used to get the organization_id back.
857 SELECT whse_code
858 INTO l_whse_code
859 FROM ic_whse_mst
860 WHERE
861 organization_id = p_organization_id AND
862 migrated_ind = 1 AND
863 ROWNUM = 1;
864
865 IF (p_locator_id is not NULL) THEN
866 l_field_name := 'Migrated warehouse locations';
867 l_field_value := p_locator_id;
868
869 SELECT l.whse_code, l.location, w.organization_id
870 INTO l_location_whse, l_location, l_loc_organization_id
871 FROM ic_loct_mst l, ic_whse_mst w
872 WHERE
873 inventory_location_id = p_locator_id AND
874 l.whse_code = w.whse_code;
875
876 -- If multiple warehouses were mapped to an organization, choose the one
877 -- for the locator used in the call.
878 IF l_loc_organization_id = p_organization_id THEN
879 l_whse_code := l_location_whse;
880 END IF;
881
882 IF (l_location_whse <> l_whse_code) THEN
883 -- Log Error
884 -- dbms_output.put_line ('Warehouse for the orgnization and loctor id do not match. org id, org_whse, locator id, whse :'||to_char(p_organization_id)||', '||l_whse_code||', '||to_char(p_locator_id)||', '||l_location_whse);
885 GMA_COMMON_LOGGING.gma_migration_central_log (
886 p_run_id => p_migration_run_id,
887 p_log_level => FND_LOG.LEVEL_ERROR,
888 p_message_token => 'GMI_MIG_DIFF_ORG_LOC_WHSE',
889 p_table_name => 'IC_LOTS_MST',
890 p_context => 'LOTS',
891 p_param1 => INV_GMI_Migration.org(p_organization_id),
892 p_param2 => l_whse_code,
893 p_param3 => to_char(p_locator_id),
894 p_param4 => l_location_whse,
895 p_param5 => NULL,
896 p_db_error => NULL,
897 p_app_short_name => 'GMI');
898 x_failure_count := x_failure_count + 1;
899 RETURN;
900 END IF;
901 END IF;
902
903 -- Call the main routine
904 INV_OPM_Lot_Migration.get_ODM_lot(
905 p_migration_run_id => p_migration_run_id,
906 p_item_id => l_item_id,
907 p_lot_id => l_lot_id,
908 p_whse_code => l_whse_code,
909 p_orgn_code => NULL,
910 p_location => l_location,
911 p_commit => p_commit,
912 x_lot_number => x_lot_number,
913 x_parent_lot_number => x_parent_lot_number,
914 x_failure_count => x_failure_count);
915 EXCEPTION
916 WHEN NO_DATA_FOUND THEN
917 -- dbms_output.put_line ('Could not find '||l_field_name||' for '||l_field_value);
918 GMA_COMMON_LOGGING.gma_migration_central_log (
919 p_run_id => p_migration_run_id,
920 p_log_level => FND_LOG.LEVEL_ERROR,
921 p_message_token => 'GMI_MIG_NO_DATA_FOR_FIELD',
922 p_table_name => 'IC_LOTS_MST',
923 p_context => 'LOTS',
924 p_param1 => l_field_name,
925 p_param2 => l_field_value,
926 p_param3 => NULL,
927 p_param4 => NULL,
928 p_param5 => NULL,
929 p_db_error => NULL,
930 p_app_short_name => 'GMI');
931 x_failure_count := x_failure_count + 1;
932 RETURN;
933 END;
934 END;
935
936 /*====================================================================
937 -- PROCEDURE:
938 -- get_ODM_lot
939 --
940 -- DESCRIPTION:
941 -- This PL/SQL procedure is used to get the lot number for
942 -- an OPM lot. If the OPM lot is not migrated, it will migrate the
943 -- the lot and return the discrete lot number.
944 --
945 -- PARAMETERS:
946 -- P_migration_run_id - id to use to right to migration log
947 -- p_item_id - OPM item id.
948 -- p_lot_no - OPM Lot No
949 -- p_sublot_no - OPM SubLot No
950 -- p_whse_code - OPM warehouse for the lot. Lot will be migrated to
951 -- the organization created for the OPM warehouse.
952 -- If p_whse_code is specified then p_orgn_code is
953 -- ignored.
954 -- p_orgn_code - OPM organization for the lot. Lot will be migrated to
955 -- the organization created for the OPM organization.
956 -- p_location - OPM location where the lot exist.
957 -- p_get_parent_only - A value of 1 indicate to only return the parent lot.
958 -- A value of 0 indicate to return both lot and the parent lot.
959 -- p_commit - flag to indicate if commit should be performed.
960 -- x_lot_number - Discrete Lot Number
961 -- x_parent_lot_number - Discrete Parent Lot Number
962 -- x_failure_count - Number of exceptions occurred.
963 --
964 -- SYNOPSIS:
965 -- get_ODM_lot( p_migartion_id => l_migration_id,
966 -- p_item_id => l_item_id,
967 -- p_lot_no => l_lot_no,
968 -- p_sublot_no => l_sublot_no,
969 -- p_whse_code => l_whse_code,
970 -- p_orgn_code => l_orgn_code,
971 -- p_location => l_location,
972 -- p_get_parent_only => 0,
973 -- p_commit => 'Y',
974 -- x_lot_number => l_lot_number,
975 -- x_parent_lot_number => l_parent_lot_number,
976 -- x_failure_count => l_failure_count);
977 --
978 -- HISTORY
979 -- Jatinder Gogna - Created - 03/25/05
980 --====================================================================*/
981
982 PROCEDURE get_ODM_lot
983 ( p_migration_run_id IN NUMBER
984 , p_item_id IN NUMBER
985 , p_lot_no IN VARCHAR2
986 , p_sublot_no IN VARCHAR2
987 , p_whse_code IN VARCHAR2
988 , p_orgn_code IN VARCHAR2
989 , p_location IN VARCHAR2
990 , p_get_parent_only IN NUMBER
991 , p_commit IN VARCHAR2
992 , x_lot_number OUT NOCOPY VARCHAR2
993 , x_parent_lot_number OUT NOCOPY VARCHAR2
994 , x_failure_count OUT NOCOPY NUMBER) IS
995
996 l_lot_id NUMBER;
997 l_count NUMBER;
998 BEGIN
999 x_failure_count := 0;
1000 IF (p_get_parent_only <> 1) THEN
1001 BEGIN
1002 SELECT lot_id
1003 INTO l_lot_id
1004 FROM ic_lots_mst
1005 WHERE
1006 item_id = p_item_id AND
1007 lot_no = p_lot_no AND
1008 nvl(sublot_no, ' ') = nvl(p_sublot_no, ' ');
1009
1010 INV_OPM_Lot_Migration.get_ODM_lot(
1011 p_migration_run_id => p_migration_run_id,
1012 p_item_id => p_item_id,
1013 p_lot_id => l_lot_id,
1014 p_whse_code => p_whse_code,
1015 p_orgn_code => p_orgn_code,
1016 p_location => p_location,
1017 p_commit => p_commit,
1018 x_lot_number => x_lot_number,
1019 x_parent_lot_number => x_parent_lot_number,
1020 x_failure_count => x_failure_count);
1021 EXCEPTION
1022 WHEN NO_DATA_FOUND THEN
1023 -- Log error
1024 -- dbms_output.put_line ('Invalid OPM lot. Item id, lot no, sublot no : '||to_char(p_item_id)||', '||p_lot_no||', '||p_sublot_no);
1025 GMA_COMMON_LOGGING.gma_migration_central_log (
1026 p_run_id => p_migration_run_id,
1027 p_log_level => FND_LOG.LEVEL_ERROR,
1028 p_message_token => 'GMI_MIG_INVALID_LOT',
1029 p_table_name => 'IC_LOTS_MST',
1030 p_context => 'LOTS',
1031 p_param1 => p_lot_no,
1032 p_param2 => p_sublot_no,
1033 p_param3 => INV_GMI_Migration.item(p_item_id),
1034 p_param4 => NULL,
1035 p_param5 => NULL,
1036 p_db_error => NULL,
1037 p_app_short_name => 'GMI');
1038 x_failure_count := x_failure_count + 1;
1039 RETURN;
1040
1041 END;
1042 ELSE -- Get the parent lot only
1043 SELECT count(*)
1044 INTO l_count
1045 FROM ic_lots_mst_mig
1046 WHERE
1047 item_id = p_item_id AND
1048 whse_code = p_whse_code AND
1049 parent_lot_number = p_lot_no AND
1050 migrated_ind = 1;
1051 IF (l_count = 0) THEN
1052 -- Log error
1053 -- dbms_output.put_line ('No parent lot found for item id, whse_code, lot no : '||to_char(p_item_id)||', '||p_whse_code||', '||p_lot_no);
1054 GMA_COMMON_LOGGING.gma_migration_central_log (
1055 p_run_id => p_migration_run_id,
1056 p_log_level => FND_LOG.LEVEL_ERROR,
1057 p_message_token => 'GMI_MIG_NO_PARENT_LOT',
1058 p_table_name => 'IC_LOTS_MST',
1059 p_context => 'LOTS',
1060 p_param1 => INV_GMI_Migration.item(p_item_id),
1061 p_param2 => p_whse_code,
1062 p_param3 => p_lot_no,
1063 p_param4 => NULL,
1064 p_param5 => NULL,
1065 p_db_error => NULL,
1066 p_app_short_name => 'GMI');
1067 x_failure_count := x_failure_count + 1;
1068 RETURN;
1069 ELSE
1070 x_parent_lot_number := p_lot_no;
1071 RETURN;
1072 END IF;
1073 END IF;
1074 END;
1075
1076 /*====================================================================
1077 -- PROCEDURE:
1078 -- get_ODM_lot
1079 --
1080 -- DESCRIPTION:
1081 -- This PL/SQL procedure is used to get the lot number for
1082 -- an OPM lot. If the OPM lot is not migrated, it will migrate the
1083 -- the lot and return the discrete lot number.
1084 --
1085 -- PARAMETERS:
1086 -- P_migration_run_id - id to use to right to migration log
1087 -- p_item_id - OPM item id.
1088 -- p_lot_id - OPM Lot id
1089 -- p_whse_code - OPM warehouse for the lot. Lot will be migrated to
1090 -- the organization created for the OPM warehouse.
1091 -- If p_whse_code is specified then p_orgn_code is
1092 -- ignored.
1093 -- p_orgn_code - OPM organization for the lot. Lot will be migrated to
1094 -- the organization created for the OPM organization.
1095 -- p_location - OPM location where the lot exist.
1096 -- p_commit - flag to indicate if commit should be performed.
1097 -- x_lot_number - Discrete Lot Number
1098 -- x_parent_lot_number - Discrete Parent Lot Number
1099 -- x_failure_count - Number of exceptions occurred.
1100 -- p_organization_id - If the organization_id for lot is different from the
1101 -- organization_id where the warehouse is migrated, specify the
1102 -- organziation_id here, else leave it NULL.
1103 --
1104 -- SYNOPSIS:
1105 -- get_ODM_lot( p_migartion_id => l_migration_id,
1106 -- p_item_id => l_item_id,
1107 -- p_lot_id => l_lot_id,
1108 -- p_sublot_no => l_sublot_no,
1109 -- p_whse_code => l_whse_code,
1110 -- p_orgn_code => l_orgn_code,
1111 -- p_location => l_location,
1112 -- p_commit => 'Y',
1113 -- x_lot_number => l_lot_number,
1114 -- x_parent_lot_number => l_parent_lot_number,
1115 -- x_failure_count => l_failure_count);
1116 --
1117 -- HISTORY
1118 -- Jatinder Gogna - Created - 03/25/05
1119 --====================================================================*/
1120
1121 PROCEDURE get_ODM_lot
1122 ( p_migration_run_id IN NUMBER
1123 , p_item_id IN NUMBER
1124 , p_lot_id IN NUMBER
1125 , p_whse_code IN VARCHAR2
1126 , p_orgn_code IN VARCHAR2
1127 , p_location IN VARCHAR2
1128 , p_commit IN VARCHAR2
1129 , x_lot_number OUT NOCOPY VARCHAR2
1130 , x_parent_lot_number OUT NOCOPY VARCHAR2
1131 , x_failure_count OUT NOCOPY NUMBER
1132 , p_organization_id IN NUMBER DEFAULT NULL
1133 ) IS
1134 l_migrated_ind PLS_INTEGER;
1135 l_whse_migrated_ind PLS_INTEGER;
1136 l_orgn_migrated_ind PLS_INTEGER;
1137 l_organization_id NUMBER;
1138 l_lot_status VARCHAR2(4);
1139 l_count PLS_INTEGER;
1140 i PLS_INTEGER;
1141 l_msg_count NUMBER;
1142 l_msg_data VARCHAR2(2000);
1143 BEGIN
1144 x_failure_count := 0;
1145 -- Validate input parameters
1146 IF (p_item_id < 1 or p_item_id is NULL
1147 or p_lot_id < 1 or p_lot_id is NULL
1148 or ( p_whse_code is NULL and p_orgn_code is NULL)
1149 or ( p_orgn_code is not NULL and p_location is not NULL)
1150 or ( p_organization_id is not NULL and p_location is not NULL)) THEN
1151 -- Log validation error
1152 -- dbms_output.put_line ('Invalid parameters');
1153 GMA_COMMON_LOGGING.gma_migration_central_log (
1154 p_run_id => p_migration_run_id,
1155 p_log_level => FND_LOG.LEVEL_ERROR,
1156 p_message_token => 'GMI_MIG_INVALID_PARAMS',
1157 p_table_name => 'IC_LOTS_MST',
1158 p_context => 'LOTS',
1159 p_param1 => NULL,
1160 p_param2 => NULL,
1161 p_param3 => NULL,
1162 p_param4 => NULL,
1163 p_param5 => NULL,
1164 p_db_error => NULL,
1165 p_app_short_name => 'GMI');
1166 x_failure_count := x_failure_count + 1;
1167 RETURN;
1168 END IF;
1169
1170 -- See if the value for the lot is already cached
1171 IF (g_item_id = p_item_id and
1172 g_lot_id = p_lot_id and
1173 nvl(g_whse_code, ' ') = nvl(p_whse_code, ' ') and
1174 nvl(g_orgn_code, ' ') = nvl(p_orgn_code, ' ') and
1175 nvl(g_organization_id, 0) = nvl(p_organization_id, 0) and
1176 nvl(g_location, ' ') = nvl(p_location, ' ') and
1177 g_lot_number is NOT NULL ) THEN
1178 x_lot_number := g_lot_number;
1179 x_parent_lot_number := g_parent_lot_number;
1180 RETURN;
1181 END IF;
1182
1183 -- Check for flexfield conflicts
1184 IF (g_desc_flex_conflict is NULL) THEN
1185 validate_desc_flex_definition (p_migration_run_id);
1186 END IF;
1187
1188 IF (g_desc_flex_conflict = 1) THEN
1189 -- Log error
1190 -- No need to log any meesages as they were logged by the previous call
1191 x_failure_count := x_failure_count + 1;
1192 RETURN;
1193 END IF;
1194
1195 -- Get the organization_id for the warehouse
1196 IF (p_organization_id IS NULL) THEN
1197 IF (p_whse_code IS NOT NULL) THEN
1198 BEGIN
1199 SELECT organization_id, migrated_ind
1200 INTO l_organization_id, l_whse_migrated_ind
1201 FROM ic_whse_mst
1202 WHERE
1203 whse_code = p_whse_code;
1204
1205 IF (l_organization_id is NULL or l_whse_migrated_ind = 0) THEN
1206 -- Log error
1207 -- dbms_output.put_line ('Warehouse not migrated : '||p_whse_code);
1208 GMA_COMMON_LOGGING.gma_migration_central_log (
1209 p_run_id => p_migration_run_id,
1210 p_log_level => FND_LOG.LEVEL_ERROR,
1211 p_message_token => 'GMI_MIG_WHSE_NOT_MIGRATED',
1212 p_table_name => 'IC_LOTS_MST',
1213 p_context => 'LOTS',
1214 p_param1 => p_whse_code,
1215 p_param2 => NULL,
1216 p_param3 => NULL,
1217 p_param4 => NULL,
1218 p_param5 => NULL,
1219 p_db_error => NULL,
1220 p_app_short_name => 'GMI');
1221 x_failure_count := x_failure_count + 1;
1222 RETURN;
1223 END IF;
1224 EXCEPTION
1225 WHEN NO_DATA_FOUND THEN
1226 -- Log error
1227 -- dbms_output.put_line ('Invalid warehouse : '||p_whse_code);
1228 GMA_COMMON_LOGGING.gma_migration_central_log (
1229 p_run_id => p_migration_run_id,
1230 p_log_level => FND_LOG.LEVEL_ERROR,
1231 p_message_token => 'GMI_MIG_INVALID_WHSE',
1232 p_table_name => 'IC_LOTS_MST',
1233 p_context => 'LOTS',
1234 p_param1 => p_whse_code,
1235 p_param2 => NULL,
1236 p_param3 => NULL,
1237 p_param4 => NULL,
1238 p_param5 => NULL,
1239 p_db_error => NULL,
1240 p_app_short_name => 'GMI');
1241 x_failure_count := x_failure_count + 1;
1242 RETURN;
1243 END;
1244 ELSE -- p_whse_code is NULL
1245 BEGIN
1246 SELECT organization_id, migrated_ind
1247 INTO l_organization_id, l_orgn_migrated_ind
1248 FROM sy_orgn_mst_b
1249 WHERE
1250 orgn_code = p_orgn_code;
1251
1252 IF (l_organization_id is NULL or l_orgn_migrated_ind = 0) THEN
1253 -- Log error
1254 -- dbms_output.put_line ('Organization not migrated : '||p_orgn_code);
1255 GMA_COMMON_LOGGING.gma_migration_central_log (
1256 p_run_id => p_migration_run_id,
1257 p_log_level => FND_LOG.LEVEL_ERROR,
1258 p_message_token => 'GMI_MIG_ORGN_NOT_MIGRATED',
1259 p_table_name => 'IC_LOTS_MST',
1260 p_context => 'LOTS',
1261 p_param1 => p_orgn_code,
1262 p_param2 => NULL,
1263 p_param3 => NULL,
1264 p_param4 => NULL,
1265 p_param5 => NULL,
1266 p_db_error => NULL,
1267 p_app_short_name => 'GMI');
1268 x_failure_count := x_failure_count + 1;
1269 RETURN;
1270 END IF;
1271 EXCEPTION
1272 WHEN NO_DATA_FOUND THEN
1273 -- Log error
1274 -- dbms_output.put_line ('Invalid organization : '||p_orgn_code);
1275 GMA_COMMON_LOGGING.gma_migration_central_log (
1276 p_run_id => p_migration_run_id,
1277 p_log_level => FND_LOG.LEVEL_ERROR,
1278 p_message_token => 'GMI_MIG_INVALID_ORGN',
1279 p_table_name => 'IC_LOTS_MST',
1280 p_context => 'LOTS',
1281 p_param1 => p_orgn_code,
1282 p_param2 => NULL,
1283 p_param3 => NULL,
1284 p_param4 => NULL,
1285 p_param5 => NULL,
1286 p_db_error => NULL,
1287 p_app_short_name => 'GMI');
1288 x_failure_count := x_failure_count + 1;
1289 RETURN;
1290 END;
1291 END IF;
1292 ELSE
1293 l_organization_id := p_organization_id;
1294 END IF;
1295
1296 -- Check the value in ic_lots_mst_mig table
1297 BEGIN
1298 g_item_id := p_item_id;
1299 g_lot_id := p_lot_id;
1300 g_whse_code := p_whse_code;
1301 g_orgn_code := p_orgn_code;
1302 g_location := p_location;
1303 g_organization_id := l_organization_id;
1304 g_lot_number := NULL;
1305 g_parent_lot_number := NULL;
1306 l_migrated_ind := -1;
1307
1308 IF (g_location is not NULL) THEN
1309 BEGIN
1310 SELECT lot_number, parent_lot_number, status, nvl(migrated_ind, 0)
1311 INTO g_lot_number, g_parent_lot_number, l_lot_status, l_migrated_ind
1312 FROM ic_lots_mst_mig
1313 WHERE
1314 item_id = g_item_id AND
1315 lot_id = g_lot_id AND
1316 whse_code = g_whse_code AND -- only OPM whse
1317 location = g_location AND
1318 ROWNUM = 1;
1319 EXCEPTION
1320 WHEN NO_DATA_FOUND THEN
1321 NULL;
1322 END;
1323 END IF;
1324
1325 IF (g_location is NULL or l_migrated_ind = -1) THEN
1326
1327 SELECT lot_number, parent_lot_number, status, migrated_ind
1328 INTO g_lot_number, g_parent_lot_number, l_lot_status, l_migrated_ind
1329 FROM ic_lots_mst_mig
1330 WHERE
1331 item_id = g_item_id AND
1332 lot_id = g_lot_id AND
1333 organization_id = g_organization_id AND -- for OPM whse or orgn
1334 additional_status_lot = 0 AND
1335 ROWNUM = 1;
1336 END IF;
1337
1338 x_lot_number := g_lot_number;
1339 x_parent_lot_number := g_parent_lot_number;
1340 IF (l_migrated_ind = 1) THEN
1341 RETURN;
1342 END IF;
1343 EXCEPTION
1344 WHEN NO_DATA_FOUND THEN
1345 NULL;
1346 END;
1347
1348 -- This lot needs migration
1349
1350 IF (l_migrated_ind <> 1) THEN
1351 migrate_OPM_lot_to_ODM (
1352 p_migration_run_id,
1353 p_item_id,
1354 p_lot_id,
1355 l_organization_id,
1356 p_whse_code,
1357 p_location,
1358 l_lot_status,
1359 p_commit,
1360 x_lot_number,
1361 x_parent_lot_number,
1362 x_failure_count);
1363 IF (x_failure_count > 0) THEN
1364 -- dbms_output.put_line ('OPM Lot migration failed. item id, lot id, whse, location : '||to_char(p_item_id)||', '||to_char(p_lot_id)||', '||p_whse_code||', '||p_location);
1365 GMA_COMMON_LOGGING.gma_migration_central_log (
1366 p_run_id => p_migration_run_id,
1367 p_log_level => FND_LOG.LEVEL_ERROR,
1368 p_message_token => 'GMI_MIG_LOT_MIG_FAILED',
1369 p_table_name => 'IC_LOTS_MST',
1370 p_context => 'LOTS',
1371 p_param1 => INV_GMI_Migration.item(p_item_id),
1372 p_param2 => INV_GMI_Migration.lot(p_lot_id),
1373 p_param3 => p_whse_code,
1374 p_param4 => p_location,
1375 p_param5 => NULL,
1376 p_db_error => NULL,
1377 p_app_short_name => 'GMI');
1378 x_failure_count := x_failure_count + 1;
1379 RETURN;
1380 END IF;
1381 END IF;
1382
1383 EXCEPTION
1384 WHEN FND_API.G_EXC_ERROR THEN
1385 x_failure_count := x_failure_count + 1;
1386 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1387 FOR i in 1..l_msg_count LOOP
1388 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1389 GMA_COMMON_LOGGING.gma_migration_central_log (
1390 p_run_id => P_migration_run_id,
1391 p_log_level => FND_LOG.LEVEL_ERROR,
1392 p_message_token => 'GMI_UNEXPECTED_ERROR',
1393 p_table_name => 'IC_LOTS_MST',
1394 p_context => 'LOTS',
1395 p_token1 => 'ERROR',
1396 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1397 p_param2 => NULL,
1398 p_param3 => NULL,
1399 p_param4 => NULL,
1400 p_param5 => NULL,
1401 p_db_error => NULL,
1402 p_app_short_name => 'GMI');
1403 END LOOP;
1404
1405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1406 x_failure_count := x_failure_count + 1;
1407 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1408 FOR i in 1..l_msg_count LOOP
1409 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1410 GMA_COMMON_LOGGING.gma_migration_central_log (
1411 p_run_id => P_migration_run_id,
1412 p_log_level => FND_LOG.LEVEL_ERROR,
1413 p_message_token => 'GMI_UNEXPECTED_ERROR',
1414 p_table_name => 'IC_LOTS_MST',
1415 p_context => 'LOTS',
1416 p_token1 => 'ERROR',
1417 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1418 p_param2 => NULL,
1419 p_param3 => NULL,
1420 p_param4 => NULL,
1421 p_param5 => NULL,
1422 p_db_error => NULL,
1423 p_app_short_name => 'GMI');
1424 END LOOP;
1425
1426 WHEN OTHERS THEN
1427 x_failure_count := x_failure_count + 1;
1428 FND_MSG_PUB.Count_AND_GET (p_count => l_msg_count, p_data => l_msg_data);
1429 FOR i in 1..l_msg_count LOOP
1430 -- dbms_output.put_line (substr(fnd_msg_pub.get_detail(i, NULL),1,255));
1431 GMA_COMMON_LOGGING.gma_migration_central_log (
1432 p_run_id => P_migration_run_id,
1433 p_log_level => FND_LOG.LEVEL_ERROR,
1434 p_message_token => 'GMI_UNEXPECTED_ERROR',
1435 p_table_name => 'IC_LOTS_MST',
1436 p_context => 'LOTS',
1437 p_token1 => 'ERROR',
1438 p_param1 => fnd_msg_pub.get_detail(i, NULL),
1439 p_param2 => NULL,
1440 p_param3 => NULL,
1441 p_param4 => NULL,
1442 p_param5 => NULL,
1443 p_db_error => NULL,
1444 p_app_short_name => 'GMI');
1445 END LOOP;
1446 -- dbms_output.put_line (substr(SQLERRM,1,255));
1447 GMA_COMMON_LOGGING.gma_migration_central_log (
1448 p_run_id => P_migration_run_id,
1449 p_log_level => FND_LOG.LEVEL_ERROR,
1450 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1451 p_table_name => 'IC_LOTS_MST',
1452 p_context => 'LOTS',
1453 p_param1 => NULL,
1454 p_param2 => NULL,
1455 p_param3 => NULL,
1456 p_param4 => NULL,
1457 p_param5 => NULL,
1458 p_db_error => SQLERRM,
1459 p_app_short_name => 'GMA');
1460
1461 END;
1462 END INV_OPM_Lot_Migration;