[Home] [Help]
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;
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:
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: --
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: --
44: -- PARAMETERS:
45: -- None
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
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)
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: --
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
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:
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
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
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
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);
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;
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:
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:
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,
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:
305: );
306:
307: --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
308: -- Subinventory and Locator
309: --For any errors raise exception rcv_lot_transactions_data;
310:
311: UPDATE rcv_lot_transactions
312: SET LOT_NUM = l_lot_num
313: where rowid = cr_rec1.rowid;
307: --Call INVENTORY API that returns the new Lot number for the item, organization, lot, sublot,
308: -- Subinventory and Locator
309: --For any errors raise exception rcv_lot_transactions_data;
310:
311: UPDATE rcv_lot_transactions
312: SET LOT_NUM = l_lot_num
313: where rowid = cr_rec1.rowid;
314: */
315: UPDATE rcv_lot_transactions
311: UPDATE rcv_lot_transactions
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
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,
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:
351: END;
352: END LOOP;
353:
354: COMMIT;
355: END Update_rcv_lot_transactions;
356:
357:
358: /*===========================================================================
359: -- PROCEDURE:
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
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