[Home] [Help]
PACKAGE BODY: APPS.RCV_GML_CONV_MIG
Source
1 PACKAGE BODY RCV_GML_CONV_MIG AS
2 /* $Header: RCVMGGMB.pls 120.8.12020000.2 2013/03/12 08:48:57 shalchen ship $ */
3 /*===========================================================================
4 -- PROCEDURE:
5 -- RCV_MIG_GML_DATA
6 --
7 -- DESCRIPTION:
8 -- This PL/SQL procedure is used to all the RCV entities for Inv Convergence
9 -- project. Main Procedure that calls the other 3 procedures.
10 --
11 -- PARAMETERS:
12 -- None
13 --
14 -- SYNOPSIS:
15 -- rcv_mig_gml_data;
16 --
17 -- HISTORY
18 -- P. Bamb 10-May-2005 Created
19 --=========================================================================== */
20 Procedure rcv_mig_gml_data IS
21
22 BEGIN
23
24
25 --Call proc to update lots with the latest migrated lot number in rcv_lot_Transactions.
26 Update_rcv_lot_transactions;
27
28 --Call proc to update secondary unit of measure and secondary quantity in rcv_supply.
29 Update_rcv_supply;
30
31 --Call proc to update lots with migrated lot number in rcv_lots_supply.
32 Update_rcv_lots_supply;
33
34 END rcv_mig_gml_data;
35
36
37 /*===========================================================================
38 -- PROCEDURE:
39 -- update_rcv_lot_transactions
40 --
41 -- DESCRIPTION:
45 -- None
42 -- This PL/SQL procedure is used to Update RCV_LOT_TRANSACTIONS for LOT_NUM.
43 --
44 -- PARAMETERS:
46 --
47 -- SYNOPSIS:
48 -- update_rcv_lot_transactions;
49 --
50 -- HISTORY
51 -- P. Bamb 10-May-2005 Created
52 -- KBAVADEK 10-MAR-2011 Bug#11670689.Modified sql in cursor CR_GET_TRX_LOTS
53 -- for performance issue
54 -- ASATPUTE 22-Jun-2011 Bug 12591131 Update only those transctions that
55 -- contain lots that were ALREADY migrated
56 --=========================================================================== */
57 PROCEDURE update_rcv_lot_transactions IS
58
59 /* Fix for Bug#11670689. Added hints in select and subquery clause */
60
61 -- Cursor CR_GET_TRX_LOTS IS
62 -- SELECT /*+ parallel(rlt) */ rlt.rowid,
63 -- rlt.transaction_id transaction_id,
64 -- rlt.source_transaction_id source_transaction_id,
65 -- rt.SHIPMENT_HEADER_ID shipment_header_id,
66 -- rlt.SHIPMENT_LINE_ID shipment_line_id,
67 -- rlt.lot_num lot_num,
68 -- rlt.sublot_num,
69 -- rlt.item_id,
70 -- rt.organization_id organization_id,
71 -- rt.subinventory subinventory,
72 -- rt.locator_id locator_id,
73 -- rlt.correction_transaction_id
74 -- FROM rcv_transactions rt ,
75 -- rcv_lot_transactions rlt,
76 -- mtl_parameters mp
77 -- WHERE rlt.lot_transaction_type = 'TRANSACTION'
78 -- and rlt.source_transaction_id = rt.transaction_id
79 -- and (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
80 -- and rt.organization_id = mp.organization_id
81 -- and mp.process_enabled_flag = 'Y'
82 -- and not exists
83 -- (SELECT /*+ push_subq no_unnest */ 'x'
84 -- FROM GML_RCV_LOTS_MIGRATION glm
85 -- WHERE table_name = 'RCV_LOT_TRANSACTIONS'
86 -- AND glm.source_transaction_id = rlt.source_transaction_id
87 -- AND glm.transaction_id = rlt.transaction_id
88 -- AND glm.correction_transaction_id = rlt.correction_transaction_id);
89 --
90 /* Bug 12591131
91 * Instead of updating ALL the receiving transactions - update only a
92 * subset i.e. If a lot was ALREADY migrated (for some other considerations) then
93 * only update the corresponding receiving transactions, therefore a join with
94 * mig tables. Also get the ODM lot number from mig table and use it in update */
95
96 Cursor CR_GET_TRX_LOTS IS
97 SELECT /*+ parallel(rlt) */ rlt.rowid,
98 rlt.transaction_id transaction_id,
99 rlt.source_transaction_id source_transaction_id,
100 rt.SHIPMENT_HEADER_ID shipment_header_id,
101 rlt.SHIPMENT_LINE_ID shipment_line_id,
102 rlt.lot_num lot_num,
103 rlt.sublot_num,
104 mlot.lot_number,
105 rlt.item_id,
106 rt.organization_id organization_id,
107 rt.subinventory subinventory,
108 rt.locator_id locator_id,
109 rlt.correction_transaction_id
110 FROM
111 ic_item_mst_b_mig mitm,
112 ic_lots_mst_mig mlot,
113 rcv_transactions rt ,
114 rcv_lot_transactions rlt,
115 mtl_parameters mp
116 WHERE
117 mitm.organization_id = mlot.organization_id
118 AND mitm.item_id = mlot.item_id
119 AND rlt.item_id = mitm.inventory_item_id
120 AND rlt.lot_num = mlot.lot_number
121 AND rt.organization_id = mlot.organization_id
122 AND rt.organization_id = mitm.organization_id
123 AND rlt.lot_transaction_type = 'TRANSACTION'
124 AND rlt.source_transaction_id = rt.transaction_id
125 AND (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
126 AND rt.organization_id = mp.organization_id
127 AND mlot.lot_number is NOT NULL
128 AND mp.process_enabled_flag = 'Y'
129 AND NOT EXISTS
130 (SELECT /*+ push_subq no_unnest */ 'x'
131 FROM GML_RCV_LOTS_MIGRATION glm
132 WHERE table_name = 'RCV_LOT_TRANSACTIONS'
133 AND glm.source_transaction_id = rlt.source_transaction_id
134 AND glm.transaction_id = rlt.transaction_id
135 AND glm.correction_transaction_id = rlt.correction_transaction_id);
136
137 /* Bug 12591131
138 * Instead of updating ALL the receiving transactions - update only a
139 * subset i.e. If a lot was ALREADY migrated (for some other considerations) then
140 * only update the corresponding receiving transactions, therefore a join with
141 * mig tables. Also get the ODM lot number from mig table and use it in update */
142 /* CURSOR CR_GET_SHIP_LOTS IS
143 SELECT rlt.rowid,
144 rlt.transaction_id transaction_id,
145 rsl.SHIPMENT_HEADER_ID shipment_header_id,
146 rsl.SHIPMENT_LINE_ID shipment_line_id,
147 rlt.lot_num lot_num,
148 rlt.sublot_num,
149 rlt.item_id,
150 rsl.to_organization_id organization_id,
151 rsl.to_subinventory subinventory,
152 rsl.locator_id locator_id,
153 rlt.correction_transaction_id,
154 rlt.source_transaction_id
155 FROM rcv_lot_transactions rlt ,
156 rcv_shipment_lines rsl,
157 mtl_parameters mp
158 WHERE rlt.lot_transaction_type = 'SHIPMENT'
159 and rsl.shipment_line_id = rlt.shipment_line_id
160 and (rlt.sublot_num <> '-1' or rlt.sublot_num IS NULL)
161 and rsl.to_organization_id = mp.organization_id
162 and mp.process_enabled_flag = 'Y'
163 and not exists
164 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
165 WHERE table_name = 'RCV_LOT_TRANSACTIONS'
166 And glm.shipment_line_id = rlt.shipment_line_id);
167 */
168
169 CURSOR CR_GET_SHIP_LOTS IS
170 SELECT rlt.rowid,
174 rlt.lot_num lot_num,
171 rlt.transaction_id transaction_id,
172 rsl.SHIPMENT_HEADER_ID shipment_header_id,
173 rsl.SHIPMENT_LINE_ID shipment_line_id,
175 rlt.sublot_num,
176 mlot.lot_number,
177 rlt.item_id,
178 rsl.to_organization_id organization_id,
179 rsl.to_subinventory subinventory,
180 rsl.locator_id locator_id,
181 rlt.correction_transaction_id,
182 rlt.source_transaction_id
183 FROM
184 ic_lots_mst_mig mlot,
185 ic_item_mst_b_mig mitm,
186 rcv_lot_transactions rlt ,
187 rcv_shipment_lines rsl,
188 mtl_parameters mp
189 WHERE
190 mitm.organization_id = mlot.organization_id
191 AND mitm.item_id = mlot.item_id
192 AND rlt.item_id = mitm.inventory_item_id
193 AND rlt.lot_num = mlot.lot_number
194 AND rsl.to_organization_id = mlot.organization_id
195 AND rsl.to_organization_id = mitm.organization_id
196 AND rlt.lot_transaction_type = 'SHIPMENT'
197 AND rsl.shipment_line_id = rlt.shipment_line_id
198 AND (rlt.sublot_num <> '-1' or rlt.sublot_num IS NULL)
199 AND rsl.to_organization_id = mp.organization_id
200 AND mlot.lot_number is NOT NULL
201 AND mp.process_enabled_flag = 'Y'
202 AND not exists
203 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
204 WHERE table_name = 'RCV_LOT_TRANSACTIONS'
205 And glm.shipment_line_id = rlt.shipment_line_id);
206
207 l_lot_num VARCHAR2(80);
208 l_parent_lot_num VARCHAR2(80);
209 l_count NUMBER;
210 cr_rec CR_GET_TRX_LOTS%ROWTYPE;
211 cr_rec1 CR_GET_SHIP_LOTS%ROWTYPE;
212 l_errm VARCHAR2(2000);
213 BEGIN
214
215 FOR cr_rec in cr_get_trx_lots LOOP
216 BEGIN
217 /* Bug 12591131
218 * No need to call API - Now we do not expect to cause lot migration during this
219 * procedure. As well the ODM lot number is obtained in the cursor
220 *
221 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
222 P_MIGRATION_RUN_ID => 1,
223 P_INVENTORY_ITEM_ID => cr_rec.item_id,
224 P_LOT_NO => cr_rec.lot_num,
225 P_SUBLOT_NO => cr_rec.sublot_num,
226 P_ORGANIZATION_ID => cr_rec.organization_id,
227 P_LOCATOR_ID => cr_rec.locator_id,
228 P_COMMIT => 'Y',
229 X_LOT_NUMBER => l_lot_num,
230 X_PARENT_LOT_NUMBER => l_parent_lot_num,
231 X_FAILURE_COUNT => l_count
232 );
233
234 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
235 -- Subinventory and Locator
236 --For any errors raise exception rcv_lot_transactions_data;
237
238 Update rcv_lot_transactions
239 set LOT_NUM = l_lot_num
240 where rowid = cr_rec.rowid;
241 */
242
243 Update rcv_lot_transactions
244 set LOT_NUM = cr_rec.lot_number
245 where rowid = cr_rec.rowid;
246
247
248 INSERT INTO GML_RCV_LOTS_MIGRATION
249 ( TABLE_NAME,
250 TRANSACTION_ID,
251 SOURCE_TRANSACTION_ID,
252 SHIPMENT_LINE_ID,
253 CORRECTION_TRANSACTION_ID,
254 CREATED_BY,
255 CREATION_DATE,
256 LAST_UPDATED_BY,
257 LAST_UPDATE_DATE)
258 VALUES ( 'RCV_LOT_TRANSACTIONS',
259 cr_rec.transaction_id,
260 cr_rec.source_transaction_id,
261 cr_rec.shipment_line_id,
262 cr_rec.correction_transaction_id,
263 1,
264 sysdate,
265 1,
266 sysdate);
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 l_errm := sqlerrm;
271 insert into gml_po_mig_errors
272 (migration_type,po_header_id,po_line_id,line_location_id,
273 transaction_id, shipment_header_id,shipment_line_id,
274 column_name,table_name,error_message,
275 creation_date,last_update_date)
276 values ('CONVERGENCE',NULL, NULL, NULL,
277 cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
278 'LOT_NUM','RCV_LOT_TRANSACTIONS',
279 'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);
280 END;
281 END LOOP;
282
283 COMMIT;
284
285
286 FOR cr_rec1 in cr_get_ship_lots LOOP
287 BEGIN
288 /* Bug 12591131
289 * * No need to call API - Now we do not expect to cause lot migration during
290 * this
291 * * procedure. As well the ODM lot number is obtained in the cursor
292 * *
293 *
294 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
295 P_MIGRATION_RUN_ID => 1,
296 P_INVENTORY_ITEM_ID => cr_rec1.item_id,
297 P_LOT_NO => cr_rec1.lot_num,
298 P_SUBLOT_NO => cr_rec1.sublot_num,
299 P_ORGANIZATION_ID => cr_rec1.organization_id,
300 P_LOCATOR_ID => cr_rec1.locator_id,
301 P_COMMIT => 'Y',
302 X_LOT_NUMBER => l_lot_num,
303 X_PARENT_LOT_NUMBER => l_parent_lot_num,
304 X_FAILURE_COUNT => l_count
305 );
306
307 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
311 UPDATE rcv_lot_transactions
308 -- Subinventory and Locator
309 --For any errors raise exception rcv_lot_transactions_data;
310
312 SET LOT_NUM = l_lot_num
313 where rowid = cr_rec1.rowid;
314 */
315 UPDATE rcv_lot_transactions
316 SET LOT_NUM = cr_rec1.lot_number
317 where rowid = cr_rec1.rowid;
318
319 INSERT INTO GML_RCV_LOTS_MIGRATION
320 ( TABLE_NAME,
321 TRANSACTION_ID,
322 SOURCE_TRANSACTION_ID,
323 SHIPMENT_LINE_ID,
324 CORRECTION_TRANSACTION_ID,
325 CREATED_BY,
326 CREATION_DATE,
327 LAST_UPDATED_BY,
328 LAST_UPDATE_DATE)
329 VALUES ( 'RCV_LOT_TRANSACTIONS',
330 cr_rec1.transaction_id,
331 cr_rec1.source_transaction_id,
332 cr_rec1.shipment_line_id,
333 cr_rec1.correction_transaction_id,
334 1,
335 sysdate,
336 1,
337 sysdate);
338
339 EXCEPTION
340 WHEN OTHERS THEN
341 l_errm := sqlerrm;
342 insert into gml_po_mig_errors
343 (migration_type,po_header_id,po_line_id,line_location_id,
344 transaction_id, shipment_header_id,shipment_line_id,
345 column_name,table_name,error_message,
346 creation_date,last_update_date)
347 values ('CONVERGENCE',NULL, NULL, NULL,
348 cr_rec1.transaction_id, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
349 'LOT_NUM','RCV_LOT_TRANSACTIONS',
350 'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);
351 END;
352 END LOOP;
353
354 COMMIT;
355 END Update_rcv_lot_transactions;
356
357
358 /*===========================================================================
359 -- PROCEDURE:
360 -- update_rcv_supply
361 --
362 -- DESCRIPTION:
363 -- This PL/SQL procedure is used to Update RCV_SUPPLY for secondary_quantity
364 -- and secondary_unit_of_measure
365 --
366 -- PARAMETERS:
367 -- None
368 --
369 -- SYNOPSIS:
370 -- update_rcv_supply;
371 --
372 -- HISTORY
373 -- P. Bamb 10-May-2005 Created
374 --=========================================================================== */
375 PROCEDURE update_rcv_supply IS
376
377 CURSOR cr_get_supply IS
378 SELECT rs.rowid,
379 rs.quantity,
380 rs.Unit_of_measure,
381 rs.Secondary_quantity,
382 rs.Secondary_unit_of_measure,
383 msi.secondary_uom_code,
384 rs.To_organization_id,
385 rs.To_subinventory,
386 rs.To_locator_id,
387 rs.Item_id,
388 rs.po_header_id ,
389 rs.po_line_id ,
390 rs.po_line_location_id ,
391 rs.shipment_header_id ,
392 rs.shipment_line_id ,
393 rs.rcv_transaction_id
394 FROM rcv_supply rs ,
395 mtl_system_items_b msi,
396 mtl_parameters mp
397 WHERE nvl(rs.quantity,0) <> 0
398 AND rs.secondary_quantity is null
399 AND rs.item_id = msi.INVENTORY_ITEM_ID
400 AND msi.ORGANIZATION_ID = to_organization_id
401 AND msi.tracking_quantity_ind = 'PS'
402 AND rs.to_organization_id = mp.organization_id
403 AND mp.process_enabled_flag = 'Y';
404
405 l_secondary_unit_of_measure VARCHAR2(25);
406 l_secondary_quantity NUMBER;
407 rcv_supply_data_err EXCEPTION;
408
409 cr_rec cr_get_supply%ROWTYPE;
410 l_errm VARCHAR2(2000);
411
412
413 BEGIN
414
415 FOR cr_rec IN cr_get_supply LOOP
416 BEGIN
417
418 SELECT UNIT_OF_MEASURE
419 INTO l_secondary_unit_of_measure
420 FROM MTL_UNITS_OF_MEASURE
421 WHERE UOM_CODE = cr_rec.secondary_uom_code;
422
423
424 l_secondary_quantity := INV_CONVERT.inv_um_convert(
425 item_id => cr_rec.item_id,
426 precision => 6,
427 from_quantity => cr_rec.quantity,
428 from_unit => NULL,
429 to_unit => NULL,
430 from_name => cr_rec.unit_of_measure ,
431 to_name => l_secondary_unit_of_measure ); --Bug# 5584581
432 IF l_secondary_quantity <=0 THEN
433 raise rcv_supply_data_err;
434 End If;
435
436 UPDATE rcv_supply
437 SET secondary_quantity = l_secondary_quantity,
438 secondary_unit_of_measure = l_secondary_unit_of_measure
439 WHERE rowid = cr_rec.rowid;
440
441 EXCEPTION
442 WHEN rcv_supply_data_err Then
443 insert into gml_po_mig_errors
444 (migration_type,po_header_id,po_line_id,line_location_id,
445 transaction_id, shipment_header_id,shipment_line_id,
446 column_name,table_name,error_message,
447 creation_date,last_update_date)
448 values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
449 cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
450 'SECONDARY_QUANTITY','RCV_SUPPLY',
451 'ERROR DERIVING SECONDARY_QUANTITY FROM QUANTITY',sysdate,sysdate);
452 WHEN OTHERS Then
453 l_errm := sqlerrm;
454 insert into gml_po_mig_errors
458 creation_date,last_update_date)
455 (migration_type,po_header_id,po_line_id,line_location_id,
456 transaction_id, shipment_header_id,shipment_line_id,
457 column_name,table_name,error_message,
459 values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
460 cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
461 'SECONDARY_QUANTITY','RCV_SUPPLY',
462 'WHEN OTHERS IN DERIVING SECONDARY_QUANTITY FROM QUANTITY-'||substr(l_errm,1,1925),sysdate,sysdate);
463 END;
464 END LOOP;
465 Commit;
466
467 END Update_rcv_supply;
468
469 /*===========================================================================
470 -- PROCEDURE:
471 -- update_rcv_lots_supply
472 --
473 -- DESCRIPTION:
474 -- This PL/SQL procedure is used to Update RCV_LOTS_SUPPLY for LOT_NUM
475 --
476 -- PARAMETERS:
477 -- None
478 --
479 -- SYNOPSIS:
480 -- update_rcv_lots_supply;
481 --
482 -- HISTORY
483 -- P. Bamb 10-May-2005 Created
484 --=========================================================================== */
485 PROCEDURE update_rcv_lots_supply IS
486
487 CURSOR CR_GET_LOT_SUPPLY IS
488 SELECT rls.rowid,
489 rls.transaction_id transaction_id,
490 rt.SHIPMENT_HEADER_ID shipment_header_id,
491 rls.SHIPMENT_LINE_ID shipment_line_id,
492 rls.lot_num lot_num,
493 rls.sublot_num,
494 rt.organization_id organization_id,
495 rt.subinventory subinventory,
496 rt.locator_id locator_id,
497 rls.reason_code,
498 rsl.item_id
499 FROM rcv_transactions rt ,
500 rcv_lots_supply rls,
501 rcv_shipment_lines rsl,
502 mtl_parameters mp
503 WHERE rls.supply_type_code = 'RECEIVING'
504 and rls.transaction_id = rt.transaction_id
505 and (rls.sublot_num <> '-1' or rls.sublot_num IS NULL)
506 AND rt.organization_id = mp.organization_id
507 AND rt.shipment_header_id = rsl.shipment_header_id
508 AND rt.shipment_line_id = rsl.shipment_line_id
509 AND mp.process_enabled_flag = 'Y'
510 and not exists
511 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
512 WHERE table_name = 'RCV_LOTS_SUPPLY'
513 and glm.transaction_id = rls.transaction_id
514 and glm.shipment_line_id = rls.shipment_line_id)
515 --ignore fulfilled supply rows
516 AND (rls.shipment_line_id,rls.lot_num) IN
517 (SELECT rl.shipment_line_id,rl.lot_num
518 FROM rcv_lots_supply rl
519 WHERE rl.supply_type_code = 'RECEIVING'
520 GROUP BY rl.shipment_line_id,rl.lot_num
521 HAVING SUM(rl.quantity) > 0)
522 ORDER BY rls.transaction_id;
523
524 CURSOR CR_GET_LOT_SUPPLY_S IS
525 SELECT rlt.rowid,
526 rsl.SHIPMENT_HEADER_ID shipment_header_id,
527 rsl.SHIPMENT_LINE_ID shipment_line_id,
528 rlt.lot_num lot_num,
529 rlt.sublot_num,
530 rsl.item_id,
531 rsl.to_organization_id organization_id,
532 rsl.to_subinventory subinventory,
533 rsl.locator_id locator_id,
534 rlt.reason_code reason_code,
535 rlt.transaction_id
536 from rcv_lots_supply rlt ,
537 rcv_shipment_lines rsl,
538 mtl_parameters mp
539 WHERE rlt.supply_type_code = 'SHIPMENT'
540 and rsl.shipment_line_id = rlt.shipment_line_id
541 and (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
542 AND mp.organization_id = rsl.to_organization_id
543 AND mp.process_enabled_flag = 'Y'
544 --ignore fulfilled supply rows
545 AND (rlt.shipment_line_id,rlt.lot_num) IN
546 (SELECT rl.shipment_line_id,rl.lot_num
547 FROM rcv_lots_supply rl
548 WHERE rl.supply_type_code = 'SHIPMENT'
549 GROUP BY rl.shipment_line_id,rl.lot_num
550 HAVING SUM(rl.quantity) > 0)
551 /*and not exists
552 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
553 WHERE table_name = 'RCV_LOTS_SUPPLY'
554 And glm.shipment_line_id = rls.shipment_line_id)*/
555 ORDER BY rsl.shipment_line_id
556 FOR UPDATE OF LOT_NUM;
557
558 l_lot_num VARCHAR2(80);
559 l_parent_lot_num VARCHAR2(80);
560 l_count NUMBER;
561
562 l_reason_id NUMBER;
563 rcv_lot_supply_data_err EXCEPTION;
564
565 cr_rec CR_GET_LOT_SUPPLY%ROWTYPE;
566 cr_rec1 CR_GET_LOT_SUPPLY_S%ROWTYPE;
567 l_errm VARCHAR2(2000);
568
569 BEGIN
570
571 FOR cr_rec IN cr_get_lot_supply LOOP
572 BEGIN
573 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
574 P_MIGRATION_RUN_ID => 1,
575 P_INVENTORY_ITEM_ID => cr_rec.item_id,
576 P_LOT_NO => cr_rec.lot_num,
577 P_SUBLOT_NO => cr_rec.sublot_num,
578 P_ORGANIZATION_ID => cr_rec.organization_id,
579 P_LOCATOR_ID => cr_rec.locator_id,
580 P_COMMIT => 'Y',
581 X_LOT_NUMBER => l_lot_num,
582 X_PARENT_LOT_NUMBER => l_parent_lot_num,
583 X_FAILURE_COUNT => l_count
584 );
585
586 IF cr_rec.reason_code IS NOT NULL THEN
587 Select reason_id
588 into l_reason_id
589 from mtl_transaction_reasons
590 where reason_name = cr_rec.reason_code;
591 END IF;
592
593 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
594 -- Subinventory and Locator
595 --For any errors raise exception rcv_lot_transactions_data;
596
597 UPDATE rcv_lots_supply
598 SET LOT_NUM = l_lot_num,
599 REASON_ID = l_reason_id
600 WHERE rowid = cr_rec.rowid;
601
602 INSERT INTO GML_RCV_LOTS_MIGRATION
603 ( TABLE_NAME,
604 TRANSACTION_ID,
605 SOURCE_TRANSACTION_ID,
606 SHIPMENT_LINE_ID,
607 CORRECTION_TRANSACTION_ID,
608 CREATED_BY,
609 CREATION_DATE,
610 LAST_UPDATED_BY,
611 LAST_UPDATE_DATE)
612 VALUES ( 'RCV_LOTS_SUPPLY',
613 cr_rec.transaction_id,
614 NULL,
615 cr_rec.shipment_line_id,
616 NULL,
617 1,
618 sysdate,
619 1,
620 sysdate);
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 l_errm := sqlerrm;
625 insert into gml_po_mig_errors
626 (migration_type,po_header_id,po_line_id,line_location_id,
627 transaction_id, shipment_header_id,shipment_line_id,
628 column_name,table_name,error_message,
629 creation_date,last_update_date)
630 values ('CONVERGENCE',NULL, NULL, NULL,
631 cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
632 'LOT_NUM','RCV_LOT_SUPPLY',
633 'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);
634 END;
635 END LOOP;
636 COMMIT;
637
638
639 FOR cr_rec1 IN cr_get_lot_supply_s LOOP
640 BEGIN
641 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
642 P_MIGRATION_RUN_ID => 1,
643 P_INVENTORY_ITEM_ID => cr_rec1.item_id,
644 P_LOT_NO => cr_rec1.lot_num,
645 P_SUBLOT_NO => cr_rec1.sublot_num,
646 P_ORGANIZATION_ID => cr_rec1.organization_id,
647 P_LOCATOR_ID => cr_rec1.locator_id,
648 P_COMMIT => 'Y',
649 X_LOT_NUMBER => l_lot_num,
650 X_PARENT_LOT_NUMBER => l_parent_lot_num,
651 X_FAILURE_COUNT => l_count
652 );
653
654 IF cr_rec1.reason_code IS NOT NULL THEN
655 Select reason_id
656 into l_reason_id
657 from mtl_transaction_reasons
658 where reason_name = cr_rec1.reason_code;
659 END IF;
660
661 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
662 -- Subinventory and Locator
663 --For any errors raise exception rcv_lot_transactions_data;
664
665 UPDATE rcv_lots_supply
666 SET LOT_NUM = l_lot_num,
667 REASON_ID = l_reason_id
668 WHERE rowid = cr_rec1.rowid;
669
670 INSERT INTO GML_RCV_LOTS_MIGRATION
671 ( TABLE_NAME,
672 TRANSACTION_ID,
673 SOURCE_TRANSACTION_ID,
674 SHIPMENT_LINE_ID,
675 CORRECTION_TRANSACTION_ID,
676 CREATED_BY,
677 CREATION_DATE,
678 LAST_UPDATED_BY,
679 LAST_UPDATE_DATE)
680 VALUES ( 'RCV_LOTS_SUPPLY',
681 cr_rec1.transaction_id,
682 NULL,
683 cr_rec1.shipment_line_id,
684 NULL,
685 1,
686 sysdate,
687 1,
688 sysdate);
689
690 EXCEPTION
691 --WHEN rcv_lot_supply_data_err THEN
692 WHEN OTHERS THEN
693 l_errm := sqlerrm;
694 insert into gml_po_mig_errors
695 (migration_type,po_header_id,po_line_id,line_location_id,
696 transaction_id, shipment_header_id,shipment_line_id,
697 column_name,table_name,error_message,
698 creation_date,last_update_date)
699 values ('CONVERGENCE',NULL, NULL, NULL,
700 NULL, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
701 'LOT_NUM','RCV_LOT_SUPPLY',
702 'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);
703 END;
704 END LOOP;
705 COMMIT;
706 END Update_rcv_lots_supply;
707
708 END RCV_GML_CONV_MIG;
709