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