[Home] [Help]
PACKAGE BODY: APPS.RCV_GML_CONV_MIG
Source
1 PACKAGE BODY RCV_GML_CONV_MIG AS
2 /* $Header: RCVMGGMB.pls 120.6 2006/10/05 17:45:58 pbamb noship $ */
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:
42 -- This PL/SQL procedure is used to Update RCV_LOT_TRANSACTIONS for LOT_NUM.
43 --
44 -- PARAMETERS:
45 -- None
46 --
47 -- SYNOPSIS:
48 -- update_rcv_lot_transactions;
49 --
50 -- HISTORY
51 -- P. Bamb 10-May-2005 Created
52 --=========================================================================== */
53 PROCEDURE update_rcv_lot_transactions IS
54
55 Cursor CR_GET_TRX_LOTS IS
56 SELECT rlt.rowid,
57 rlt.transaction_id transaction_id,
58 rlt.source_transaction_id source_transaction_id,
59 rt.SHIPMENT_HEADER_ID shipment_header_id,
60 rlt.SHIPMENT_LINE_ID shipment_line_id,
61 rlt.lot_num lot_num,
62 rlt.sublot_num,
63 rlt.item_id,
64 rt.organization_id organization_id,
65 rt.subinventory subinventory,
66 rt.locator_id locator_id,
67 rlt.correction_transaction_id
68 FROM rcv_transactions rt ,
69 rcv_lot_transactions rlt,
70 mtl_parameters mp
71 WHERE rlt.lot_transaction_type = 'TRANSACTION'
72 and rlt.source_transaction_id = rt.transaction_id
73 and (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
74 and rt.organization_id = mp.organization_id
75 and mp.process_enabled_flag = 'Y'
76 and not exists
77 (SELECT 'x'
78 FROM GML_RCV_LOTS_MIGRATION glm
79 WHERE table_name = 'RCV_LOT_TRANSACTIONS'
80 AND glm.source_transaction_id = rlt.source_transaction_id
81 AND glm.transaction_id = rlt.transaction_id
82 AND glm.correction_transaction_id = rlt.correction_transaction_id);
83
84 CURSOR CR_GET_SHIP_LOTS IS
85 SELECT rlt.rowid,
86 rlt.transaction_id transaction_id,
87 rsl.SHIPMENT_HEADER_ID shipment_header_id,
88 rsl.SHIPMENT_LINE_ID shipment_line_id,
89 rlt.lot_num lot_num,
90 rlt.sublot_num,
91 rlt.item_id,
92 rsl.to_organization_id organization_id,
93 rsl.to_subinventory subinventory,
94 rsl.locator_id locator_id,
95 rlt.correction_transaction_id,
96 rlt.source_transaction_id
97 FROM rcv_lot_transactions rlt ,
98 rcv_shipment_lines rsl,
99 mtl_parameters mp
100 WHERE rlt.lot_transaction_type = 'SHIPMENT'
101 and rsl.shipment_line_id = rlt.shipment_line_id
102 and (rlt.sublot_num <> '-1' or rlt.sublot_num IS NULL)
103 and rsl.to_organization_id = mp.organization_id
104 and mp.process_enabled_flag = 'Y'
105 and not exists
106 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
107 WHERE table_name = 'RCV_LOT_TRANSACTIONS'
108 And glm.shipment_line_id = rlt.shipment_line_id);
109
110 l_lot_num VARCHAR2(80);
111 l_parent_lot_num VARCHAR2(80);
112 l_count NUMBER;
113 cr_rec CR_GET_TRX_LOTS%ROWTYPE;
114 cr_rec1 CR_GET_SHIP_LOTS%ROWTYPE;
115 l_errm VARCHAR2(2000);
116 BEGIN
117
118 FOR cr_rec in cr_get_trx_lots LOOP
119 BEGIN
120
121 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
122 P_MIGRATION_RUN_ID => 1,
123 P_INVENTORY_ITEM_ID => cr_rec.item_id,
124 P_LOT_NO => cr_rec.lot_num,
125 P_SUBLOT_NO => cr_rec.sublot_num,
126 P_ORGANIZATION_ID => cr_rec.organization_id,
127 P_LOCATOR_ID => cr_rec.locator_id,
128 P_COMMIT => 'Y',
129 X_LOT_NUMBER => l_lot_num,
130 X_PARENT_LOT_NUMBER => l_parent_lot_num,
131 X_FAILURE_COUNT => l_count
132 );
133
134 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
135 -- Subinventory and Locator
136 --For any errors raise exception rcv_lot_transactions_data;
137
138 Update rcv_lot_transactions
139 set LOT_NUM = l_lot_num
140 where rowid = cr_rec.rowid;
141
142
143
144 INSERT INTO GML_RCV_LOTS_MIGRATION
145 ( TABLE_NAME,
146 TRANSACTION_ID,
147 SOURCE_TRANSACTION_ID,
148 SHIPMENT_LINE_ID,
149 CORRECTION_TRANSACTION_ID,
150 CREATED_BY,
151 CREATION_DATE,
152 LAST_UPDATED_BY,
153 LAST_UPDATE_DATE)
154 VALUES ( 'RCV_LOT_TRANSACTIONS',
155 cr_rec.transaction_id,
156 cr_rec.source_transaction_id,
157 cr_rec.shipment_line_id,
158 cr_rec.correction_transaction_id,
159 1,
160 sysdate,
161 1,
162 sysdate);
163
164 EXCEPTION
165 WHEN OTHERS THEN
166 l_errm := sqlerrm;
167 insert into gml_po_mig_errors
168 (migration_type,po_header_id,po_line_id,line_location_id,
169 transaction_id, shipment_header_id,shipment_line_id,
170 column_name,table_name,error_message,
171 creation_date,last_update_date)
172 values ('CONVERGENCE',NULL, NULL, NULL,
173 cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
174 'LOT_NUM','RCV_LOT_TRANSACTIONS',
175 'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);
176 END;
177 END LOOP;
178
179 COMMIT;
180
181
182 FOR cr_rec1 in cr_get_ship_lots LOOP
183 BEGIN
184 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
185 P_MIGRATION_RUN_ID => 1,
186 P_INVENTORY_ITEM_ID => cr_rec1.item_id,
187 P_LOT_NO => cr_rec1.lot_num,
188 P_SUBLOT_NO => cr_rec1.sublot_num,
189 P_ORGANIZATION_ID => cr_rec1.organization_id,
190 P_LOCATOR_ID => cr_rec1.locator_id,
191 P_COMMIT => 'Y',
192 X_LOT_NUMBER => l_lot_num,
193 X_PARENT_LOT_NUMBER => l_parent_lot_num,
194 X_FAILURE_COUNT => l_count
195 );
196
197 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
198 -- Subinventory and Locator
199 --For any errors raise exception rcv_lot_transactions_data;
200
201 UPDATE rcv_lot_transactions
202 SET LOT_NUM = l_lot_num
203 where rowid = cr_rec1.rowid;
204
205 INSERT INTO GML_RCV_LOTS_MIGRATION
206 ( TABLE_NAME,
207 TRANSACTION_ID,
208 SOURCE_TRANSACTION_ID,
209 SHIPMENT_LINE_ID,
210 CORRECTION_TRANSACTION_ID,
211 CREATED_BY,
212 CREATION_DATE,
213 LAST_UPDATED_BY,
214 LAST_UPDATE_DATE)
215 VALUES ( 'RCV_LOT_TRANSACTIONS',
216 cr_rec1.transaction_id,
217 cr_rec1.source_transaction_id,
218 cr_rec1.shipment_line_id,
219 cr_rec1.correction_transaction_id,
220 1,
221 sysdate,
222 1,
223 sysdate);
224
225 EXCEPTION
226 WHEN OTHERS THEN
227 l_errm := sqlerrm;
228 insert into gml_po_mig_errors
229 (migration_type,po_header_id,po_line_id,line_location_id,
230 transaction_id, shipment_header_id,shipment_line_id,
231 column_name,table_name,error_message,
232 creation_date,last_update_date)
233 values ('CONVERGENCE',NULL, NULL, NULL,
234 cr_rec1.transaction_id, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
235 'LOT_NUM','RCV_LOT_TRANSACTIONS',
236 'ERROR DERIVING NEW LOT NUM-'||substr(l_errm,1,1970),sysdate,sysdate);
237 END;
238 END LOOP;
239
240 COMMIT;
241 END Update_rcv_lot_transactions;
242
243
244 /*===========================================================================
245 -- PROCEDURE:
246 -- update_rcv_supply
247 --
248 -- DESCRIPTION:
249 -- This PL/SQL procedure is used to Update RCV_SUPPLY for secondary_quantity
250 -- and secondary_unit_of_measure
251 --
252 -- PARAMETERS:
253 -- None
254 --
255 -- SYNOPSIS:
256 -- update_rcv_supply;
257 --
258 -- HISTORY
259 -- P. Bamb 10-May-2005 Created
260 --=========================================================================== */
261 PROCEDURE update_rcv_supply IS
262
263 CURSOR cr_get_supply IS
264 SELECT rs.rowid,
265 rs.quantity,
266 rs.Unit_of_measure,
267 rs.Secondary_quantity,
268 rs.Secondary_unit_of_measure,
269 msi.secondary_uom_code,
270 rs.To_organization_id,
271 rs.To_subinventory,
272 rs.To_locator_id,
273 rs.Item_id,
274 rs.po_header_id ,
275 rs.po_line_id ,
276 rs.po_line_location_id ,
277 rs.shipment_header_id ,
278 rs.shipment_line_id ,
279 rs.rcv_transaction_id
280 FROM rcv_supply rs ,
281 mtl_system_items_b msi,
282 mtl_parameters mp
283 WHERE nvl(rs.quantity,0) <> 0
284 AND rs.secondary_quantity is null
285 AND rs.item_id = msi.INVENTORY_ITEM_ID
286 AND msi.ORGANIZATION_ID = to_organization_id
287 AND msi.tracking_quantity_ind = 'PS'
288 AND rs.to_organization_id = mp.organization_id
289 AND mp.process_enabled_flag = 'Y';
290
291 l_secondary_unit_of_measure VARCHAR2(25);
292 l_secondary_quantity NUMBER;
293 rcv_supply_data_err EXCEPTION;
294
295 cr_rec cr_get_supply%ROWTYPE;
296 l_errm VARCHAR2(2000);
297
298
299 BEGIN
300
301 FOR cr_rec IN cr_get_supply LOOP
302 BEGIN
303
304 SELECT UNIT_OF_MEASURE
305 INTO l_secondary_unit_of_measure
306 FROM MTL_UNITS_OF_MEASURE
307 WHERE UOM_CODE = cr_rec.secondary_uom_code;
308
309
310 l_secondary_quantity := INV_CONVERT.inv_um_convert(
311 item_id => cr_rec.item_id,
312 precision => 6,
313 from_quantity => cr_rec.quantity,
314 from_unit => NULL,
315 to_unit => NULL,
316 from_name => cr_rec.unit_of_measure ,
317 to_name => l_secondary_unit_of_measure ); --Bug# 5584581
318 IF l_secondary_quantity <=0 THEN
319 raise rcv_supply_data_err;
320 End If;
321
322 UPDATE rcv_supply
323 SET secondary_quantity = l_secondary_quantity,
324 secondary_unit_of_measure = l_secondary_unit_of_measure
325 WHERE rowid = cr_rec.rowid;
326
327 EXCEPTION
328 WHEN rcv_supply_data_err Then
329 insert into gml_po_mig_errors
330 (migration_type,po_header_id,po_line_id,line_location_id,
331 transaction_id, shipment_header_id,shipment_line_id,
332 column_name,table_name,error_message,
333 creation_date,last_update_date)
334 values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
335 cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
336 'SECONDARY_QUANTITY','RCV_SUPPLY',
337 'ERROR DERIVING SECONDARY_QUANTITY FROM QUANTITY',sysdate,sysdate);
338 WHEN OTHERS Then
339 l_errm := sqlerrm;
340 insert into gml_po_mig_errors
341 (migration_type,po_header_id,po_line_id,line_location_id,
342 transaction_id, shipment_header_id,shipment_line_id,
343 column_name,table_name,error_message,
344 creation_date,last_update_date)
345 values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.po_line_location_id,
346 cr_rec.rcv_transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
347 'SECONDARY_QUANTITY','RCV_SUPPLY',
348 'WHEN OTHERS IN DERIVING SECONDARY_QUANTITY FROM QUANTITY-'||substr(l_errm,1,1925),sysdate,sysdate);
349 END;
350 END LOOP;
351 Commit;
352
353 END Update_rcv_supply;
354
355 /*===========================================================================
356 -- PROCEDURE:
357 -- update_rcv_lots_supply
358 --
359 -- DESCRIPTION:
360 -- This PL/SQL procedure is used to Update RCV_LOTS_SUPPLY for LOT_NUM
361 --
362 -- PARAMETERS:
363 -- None
364 --
365 -- SYNOPSIS:
366 -- update_rcv_lots_supply;
367 --
368 -- HISTORY
369 -- P. Bamb 10-May-2005 Created
370 --=========================================================================== */
371 PROCEDURE update_rcv_lots_supply IS
372
373 CURSOR CR_GET_LOT_SUPPLY IS
374 SELECT rls.rowid,
375 rls.transaction_id transaction_id,
376 rt.SHIPMENT_HEADER_ID shipment_header_id,
377 rls.SHIPMENT_LINE_ID shipment_line_id,
378 rls.lot_num lot_num,
379 rls.sublot_num,
380 rt.organization_id organization_id,
381 rt.subinventory subinventory,
382 rt.locator_id locator_id,
383 rls.reason_code,
384 rsl.item_id
385 FROM rcv_transactions rt ,
386 rcv_lots_supply rls,
387 rcv_shipment_lines rsl,
388 mtl_parameters mp
389 WHERE rls.supply_type_code = 'RECEIVING'
390 and rls.transaction_id = rt.transaction_id
391 and (rls.sublot_num <> '-1' or rls.sublot_num IS NULL)
392 AND rt.organization_id = mp.organization_id
393 AND rt.shipment_header_id = rsl.shipment_header_id
394 AND rt.shipment_line_id = rsl.shipment_line_id
395 AND mp.process_enabled_flag = 'Y'
396 and not exists
397 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
398 WHERE table_name = 'RCV_LOTS_SUPPLY'
399 and glm.transaction_id = rls.transaction_id
400 and glm.shipment_line_id = rls.shipment_line_id)
401 ORDER BY rls.transaction_id;
402
403 CURSOR CR_GET_LOT_SUPPLY_S IS
404 SELECT rlt.rowid,
405 rsl.SHIPMENT_HEADER_ID shipment_header_id,
406 rsl.SHIPMENT_LINE_ID shipment_line_id,
407 rlt.lot_num lot_num,
408 rlt.sublot_num,
409 rsl.item_id,
410 rsl.to_organization_id organization_id,
411 rsl.to_subinventory subinventory,
412 rsl.locator_id locator_id,
413 rlt.reason_code reason_code,
414 rlt.transaction_id
415 from rcv_lots_supply rlt ,
416 rcv_shipment_lines rsl,
417 mtl_parameters mp
418 WHERE rlt.supply_type_code = 'SHIPMENT'
419 and rsl.shipment_line_id = rlt.shipment_line_id
420 and (rlt.sublot_num <> '-1' or rlt.sublot_num is NULL)
421 AND mp.organization_id = rsl.to_organization_id
422 AND mp.process_enabled_flag = 'Y'
423 /*and not exists
424 (SELECT 'x' from GML_RCV_LOTS_MIGRATION glm
425 WHERE table_name = 'RCV_LOTS_SUPPLY'
426 And glm.shipment_line_id = rls.shipment_line_id)*/
427 ORDER BY rsl.shipment_line_id
428 FOR UPDATE OF LOT_NUM;
429
430 l_lot_num VARCHAR2(80);
431 l_parent_lot_num VARCHAR2(80);
432 l_count NUMBER;
433
437 cr_rec CR_GET_LOT_SUPPLY%ROWTYPE;
434 l_reason_id NUMBER;
435 rcv_lot_supply_data_err EXCEPTION;
436
438 cr_rec1 CR_GET_LOT_SUPPLY_S%ROWTYPE;
439 l_errm VARCHAR2(2000);
440
441 BEGIN
442
443 FOR cr_rec IN cr_get_lot_supply LOOP
444 BEGIN
445 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
446 P_MIGRATION_RUN_ID => 1,
447 P_INVENTORY_ITEM_ID => cr_rec.item_id,
448 P_LOT_NO => cr_rec.lot_num,
449 P_SUBLOT_NO => cr_rec.sublot_num,
450 P_ORGANIZATION_ID => cr_rec.organization_id,
451 P_LOCATOR_ID => cr_rec.locator_id,
452 P_COMMIT => 'Y',
453 X_LOT_NUMBER => l_lot_num,
454 X_PARENT_LOT_NUMBER => l_parent_lot_num,
455 X_FAILURE_COUNT => l_count
456 );
457
458 IF cr_rec.reason_code IS NOT NULL THEN
459 Select reason_id
460 into l_reason_id
461 from mtl_transaction_reasons
462 where reason_name = cr_rec.reason_code;
463 END IF;
464
465 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
466 -- Subinventory and Locator
467 --For any errors raise exception rcv_lot_transactions_data;
468
469 UPDATE rcv_lots_supply
470 SET LOT_NUM = l_lot_num,
471 REASON_ID = l_reason_id
472 WHERE rowid = cr_rec.rowid;
473
474 INSERT INTO GML_RCV_LOTS_MIGRATION
475 ( TABLE_NAME,
476 TRANSACTION_ID,
477 SOURCE_TRANSACTION_ID,
478 SHIPMENT_LINE_ID,
479 CORRECTION_TRANSACTION_ID,
480 CREATED_BY,
481 CREATION_DATE,
482 LAST_UPDATED_BY,
483 LAST_UPDATE_DATE)
484 VALUES ( 'RCV_LOTS_SUPPLY',
485 cr_rec.transaction_id,
486 NULL,
487 cr_rec.shipment_line_id,
488 NULL,
489 1,
490 sysdate,
491 1,
492 sysdate);
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 l_errm := sqlerrm;
497 insert into gml_po_mig_errors
498 (migration_type,po_header_id,po_line_id,line_location_id,
499 transaction_id, shipment_header_id,shipment_line_id,
500 column_name,table_name,error_message,
501 creation_date,last_update_date)
502 values ('CONVERGENCE',NULL, NULL, NULL,
503 cr_rec.transaction_id, cr_rec.shipment_header_id, cr_rec.shipment_line_id,
504 'LOT_NUM','RCV_LOT_SUPPLY',
505 'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);
506 END;
507 END LOOP;
508 COMMIT;
509
510
511 FOR cr_rec1 IN cr_get_lot_supply_s LOOP
512 BEGIN
513 INV_OPM_LOT_MIGRATION.GET_ODM_LOT(
514 P_MIGRATION_RUN_ID => 1,
515 P_INVENTORY_ITEM_ID => cr_rec1.item_id,
516 P_LOT_NO => cr_rec1.lot_num,
517 P_SUBLOT_NO => cr_rec1.sublot_num,
518 P_ORGANIZATION_ID => cr_rec1.organization_id,
519 P_LOCATOR_ID => cr_rec1.locator_id,
520 P_COMMIT => 'Y',
521 X_LOT_NUMBER => l_lot_num,
522 X_PARENT_LOT_NUMBER => l_parent_lot_num,
523 X_FAILURE_COUNT => l_count
524 );
525
526 IF cr_rec1.reason_code IS NOT NULL THEN
527 Select reason_id
528 into l_reason_id
529 from mtl_transaction_reasons
530 where reason_name = cr_rec1.reason_code;
531 END IF;
532
533 --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
534 -- Subinventory and Locator
535 --For any errors raise exception rcv_lot_transactions_data;
536
537 UPDATE rcv_lots_supply
538 SET LOT_NUM = l_lot_num,
539 REASON_ID = l_reason_id
540 WHERE rowid = cr_rec1.rowid;
541
542 INSERT INTO GML_RCV_LOTS_MIGRATION
543 ( TABLE_NAME,
544 TRANSACTION_ID,
545 SOURCE_TRANSACTION_ID,
546 SHIPMENT_LINE_ID,
547 CORRECTION_TRANSACTION_ID,
548 CREATED_BY,
549 CREATION_DATE,
550 LAST_UPDATED_BY,
551 LAST_UPDATE_DATE)
552 VALUES ( 'RCV_LOTS_SUPPLY',
553 cr_rec1.transaction_id,
554 NULL,
555 cr_rec1.shipment_line_id,
556 NULL,
557 1,
558 sysdate,
559 1,
560 sysdate);
561
562 EXCEPTION
563 --WHEN rcv_lot_supply_data_err THEN
564 WHEN OTHERS THEN
565 l_errm := sqlerrm;
566 insert into gml_po_mig_errors
567 (migration_type,po_header_id,po_line_id,line_location_id,
568 transaction_id, shipment_header_id,shipment_line_id,
569 column_name,table_name,error_message,
570 creation_date,last_update_date)
571 values ('CONVERGENCE',NULL, NULL, NULL,
572 NULL, cr_rec1.shipment_header_id, cr_rec1.shipment_line_id,
573 'LOT_NUM','RCV_LOT_SUPPLY',
574 'ERROR DERIVING LOT_NUM-'||substr(l_errm,1,1975),sysdate,sysdate);
575 END;
576 END LOOP;
577 COMMIT;
578 END Update_rcv_lots_supply;
579
580 END RCV_GML_CONV_MIG;
581