[Home] [Help]
152:
153: procedure populate_lot_migration is
154: BEGIN
155:
156: -- Remove any rows that no longer exist in the ic_loct_inv table.
157: -- This can happen if the user fixes the issue by changing the lot status to
158: -- the same value for all warehouse locations.
159:
160: DELETE FROM ic_lots_mst_mig
175: (item_id, lot_id, whse_mapping_code, whse_code, location, status) NOT IN (
176: SELECT inv.item_id, inv.lot_id,
177: DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code),
178: inv.whse_code, inv.location, inv.lot_status -- lot with diff status
179: FROM ic_loct_inv inv, ic_item_mst_b i, ic_whse_mst w
180: WHERE
181: inv.item_id = i.item_id AND
182: inv.whse_code = w.whse_code AND
183: i.lot_ctl = 1 AND
183: i.lot_ctl = 1 AND
184: inv.loct_onhand <> 0 AND
185: EXISTS (
186: SELECT 1
187: FROM ic_loct_inv inv2, ic_whse_mst w2
188: WHERE
189: inv2.whse_code = w2.whse_code AND
190: inv.item_id = inv2.item_id AND
191: inv.lot_id = inv2.lot_id AND
205: (item_id, lot_id, whse_mapping_code, whse_code, location, status) in (
206: SELECT inv.item_id, inv.lot_id,
207: DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code),
208: inv.whse_code, inv.location, inv.lot_status
209: FROM ic_loct_inv inv, ic_item_mst_b i, ic_whse_mst w
210: WHERE
211: inv.item_id = i.item_id AND
212: inv.whse_code = w.whse_code AND
213: i.lot_ctl = 1 AND
213: i.lot_ctl = 1 AND
214: inv.loct_onhand <> 0 AND
215: EXISTS (
216: SELECT 1
217: FROM ic_loct_inv inv2, ic_whse_mst w2
218: WHERE
219: inv2.whse_code = w2.whse_code AND
220: inv.item_id = inv2.item_id AND
221: inv.lot_id = inv2.lot_id AND
227: inv.lot_status <> inv2.lot_status AND
228: inv2.loct_onhand <> 0)));
229:
230:
231: -- Insert any new records that have been created in the ic_loct_inv table and
232: -- may be candidate for multiple lot status case. This can happen if User
233: -- created new inventory for a lot in a warehouse location or changed the
234: -- lot status of the existing lot in a warehouse location.
235:
273: WHERE item_id = inv.item_id AND lot_id = inv.lot_id AND
274: whse_code = inv.whse_code AND additional_status_lot = 0 AND
275: rownum = 1) mig_status,
276: DECODE(i.sublot_ctl, 1, DECODE(l.sublot_no, NULL, NULL, l.lot_no)) parent_lot
277: FROM ic_loct_inv inv, ic_item_mst_b i, ic_lots_mst l, ic_whse_mst w
278: WHERE
279: inv.whse_code = w.whse_code AND
280: inv.item_id = i.item_id AND
281: i.lot_ctl = 1 AND
283: inv.lot_id = l.lot_id AND
284: inv.loct_onhand <> 0 AND
285: EXISTS (
286: SELECT 1
287: FROM ic_loct_inv inv2, ic_whse_mst w2
288: WHERE
289: inv2.whse_code = w2.whse_code AND
290: inv.item_id = inv2.item_id AND
291: inv.lot_id = inv2.lot_id AND