[Home] [Help]
138: -- PROCEDURE:
139: -- populate_lot_migration
140: --
141: -- DESCRIPTION:
142: -- This PL/SQL procedure is used to creates data in ic_lots_mst_mig table.
143: --
144: --
145: -- PARAMETERS:
146: --
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
161: WHERE
162: migrated_ind = 0 AND
163: (item_id, lot_id, whse_mapping_code) NOT IN ( -- No deletion if any single location
164: -- in the inventory org is migrated
162: migrated_ind = 0 AND
163: (item_id, lot_id, whse_mapping_code) NOT IN ( -- No deletion if any single location
164: -- in the inventory org is migrated
165: SELECT item_id, lot_id, whse_mapping_code
166: FROM ic_lots_mst_mig
167: WHERE migrated_ind = 1) AND
168: (item_id, lot_id, whse_mapping_code) IN ( -- All whse and locations for the inv org
169: -- if any location's status was changed
170: SELECT item_id, lot_id, whse_mapping_code
167: WHERE migrated_ind = 1) AND
168: (item_id, lot_id, whse_mapping_code) IN ( -- All whse and locations for the inv org
169: -- if any location's status was changed
170: SELECT item_id, lot_id, whse_mapping_code
171: FROM ic_lots_mst_mig
172: WHERE
173: migrated_ind = 0 AND
174: -- If the lot status or warehouse mapping changed since last
175: (item_id, lot_id, whse_mapping_code, whse_code, location, status) NOT IN (
198: inv2.loct_onhand <> 0))) AND
199: (item_id, lot_id, organization_id, whse_code, location) NOT IN ( -- Except for the ones which have been updated
200: -- by the user AND ARE STILL VALID
201: SELECT item_id, lot_id, whse_mapping_code, whse_code, location
202: FROM ic_lots_mst_mig
203: WHERE
204: user_updated_ind = 1 AND
205: (item_id, lot_id, whse_mapping_code, whse_code, location, status) in (
206: SELECT inv.item_id, inv.lot_id,
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:
236: INSERT INTO ic_lots_mst_mig (
237: ITEM_ID,
238: LOT_ID,
239: ORGANIZATION_ID,
240: WHSE_MAPPING_CODE,
268: first_value(inv.lot_status) OVER -- Status of lot with the most balance
269: (PARTITION BY i.item_no, l.lot_no, l.sublot_no,
270: DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code)
271: ORDER BY inv.loct_onhand desc) first_status,
272: (SELECT status FROM ic_lots_mst_mig
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
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
297: inv.lot_status <> inv2.lot_status AND
298: inv2.loct_onhand <> 0))
299: WHERE -- Check if row already exists in the mig table
300: (item_id, lot_id, whse_code, location, lot_status) NOT IN (
301: SELECT item_id, lot_id, whse_code, location, status FROM ic_lots_mst_mig);
302:
303: EXCEPTION
304: WHEN OTHERS THEN
305: RAISE;
346: FUNCTION get_lot_no(p_lot_id NUMBER) RETURN VARCHAR2 IS
347: l_lot_no VARCHAR2(100);
348: BEGIN
349: SELECT lot_no||'-'||sublot_no INTO l_lot_no
350: FROM ic_lots_mst
351: WHERE lot_id = p_lot_id;
352: RETURN (l_lot_no);
353: END get_lot_no;
354: