1 PACKAGE BODY gma_migration_pub AS
2 /* $Header: GMAPMIGB.pls 120.1 2006/04/10 12:53:48 txdaniel noship $ */
3
4 /*====================================================================
5 -- PROCEDURE:
6 -- Check_Organization_Dependents
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to set the active indicator for the
10 -- organization based on its usage.
11 --
12 -- PARAMETERS:
13 -- P_orgn_code Organization Code
14 -- P_update Update sy_orgn_mst - 'Y' - Yes, 'N' - No
15 -- X_active_ind Out variable for active indicator,
16 -- 0 - If organization is not active
17 -- 1 - If the organization is active
18 -- SYNOPSIS:
19 -- Check_Organization_Dependents (p_orgn_code => l_orgn_code,
20 -- p_update => 'Y',
21 -- x_active_ind => l_active_ind);
22 --
23 -- HISTORY
24 --====================================================================*/
25 PROCEDURE Check_Organization_Dependents (P_orgn_code VARCHAR2,
26 P_update VARCHAR2,
27 X_active_ind OUT NOCOPY NUMBER) IS
28 CURSOR Cur_check_formula IS
29 SELECT 1
30 FROM sys.dual
31 WHERE EXISTS (SELECT 1
32 FROM fm_form_mst_b
33 WHERE orgn_code = P_orgn_code);
34
35 CURSOR Cur_check_recipe IS
36 SELECT 1
37 FROM sys.dual
38 WHERE EXISTS (SELECT 1
39 FROM gmd_recipes_b
40 WHERE owner_orgn_code = P_orgn_code);
41
42 CURSOR Cur_check_validity IS
43 SELECT 1
44 FROM sys.dual
45 WHERE EXISTS (SELECT 1
46 FROM gmd_recipe_validity_rules
47 WHERE orgn_code = P_orgn_code);
48
49 CURSOR Cur_check_tech_param IS
50 SELECT 1
51 FROM sys.dual
52 WHERE EXISTS (SELECT 1
53 FROM lm_tech_hdr
54 WHERE orgn_code = P_orgn_code);
55
56 CURSOR Cur_check_batch IS
57 SELECT 1
58 FROM sys.dual
59 WHERE EXISTS (SELECT 1
60 FROM pm_btch_hdr
61 WHERE plant_code = P_orgn_code);
62
63 CURSOR Cur_check_item_cost IS
64 SELECT 1
65 FROM sys.dual
66 WHERE EXISTS (SELECT 1
67 FROM gl_item_cst
68 WHERE orgn_code = P_orgn_code);
69
70 l_exists NUMBER(5);
71 ORG_EXISTS EXCEPTION;
72 BEGIN
73 OPEN Cur_check_formula;
74 FETCH Cur_check_formula INTO l_exists;
75 IF Cur_check_formula%FOUND THEN
76 CLOSE Cur_check_formula;
77 RAISE ORG_EXISTS;
78 END IF;
79 CLOSE Cur_check_formula;
80
81 OPEN Cur_check_recipe;
82 FETCH Cur_check_recipe INTO l_exists;
83 IF Cur_check_recipe%FOUND THEN
84 CLOSE Cur_check_recipe;
85 RAISE ORG_EXISTS;
86 END IF;
87 CLOSE Cur_check_recipe;
88
89 OPEN Cur_check_validity;
90 FETCH Cur_check_validity INTO l_exists;
91 IF Cur_check_validity%FOUND THEN
92 CLOSE Cur_check_validity;
93 RAISE ORG_EXISTS;
94 END IF;
95 CLOSE Cur_check_validity;
96
97 OPEN Cur_check_tech_param;
98 FETCH Cur_check_tech_param INTO l_exists;
99 IF Cur_check_tech_param%FOUND THEN
100 CLOSE Cur_check_tech_param;
101 RAISE ORG_EXISTS;
102 END IF;
103 CLOSE Cur_check_tech_param;
104
105 OPEN Cur_check_batch;
106 FETCH Cur_check_batch INTO l_exists;
107 IF Cur_check_batch%FOUND THEN
108 CLOSE Cur_check_batch;
109 RAISE ORG_EXISTS;
110 END IF;
111 CLOSE Cur_check_batch;
112
113 OPEN Cur_check_item_cost;
114 FETCH Cur_check_item_cost INTO l_exists;
115 IF Cur_check_item_cost%FOUND THEN
116 CLOSE Cur_check_item_cost;
117 RAISE ORG_EXISTS;
118 END IF;
119 CLOSE Cur_check_item_cost;
120
121 IF P_update = 'Y' THEN
122 UPDATE sy_orgn_mst
123 SET active_ind = 0
124 WHERE orgn_code = P_orgn_code;
125 END IF;
126 X_active_ind := 1;
127 EXCEPTION
128 WHEN ORG_EXISTS THEN
129 IF P_update = 'Y' THEN
130 UPDATE sy_orgn_mst
131 SET active_ind = 1
132 WHERE orgn_code = P_orgn_code;
133 END IF;
134 X_active_ind := 1;
135 END check_organization_dependents;
136
137 /*====================================================================
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 --
147 -- SYNOPSIS:
148 -- populate_lot_migration;
149 --
150 -- HISTORY
151 --====================================================================*/
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
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
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
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 (
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
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
192 -- Compare the balances within the mapped org
193 DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code) =
194 DECODE(w2.subinventory_ind_flag, 'Y', w2.orgn_code, w2.whse_code) AND
195 -- Same locations for whse mapped as subinventory will be created as diff locators.
196 inv.whse_code||inv.location <> inv2.whse_code||inv2.location AND
197 inv.lot_status <> inv2.lot_status AND
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,
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
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
222 -- Compare the balances within the mapped org
223 DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code) =
224 DECODE(w2.subinventory_ind_flag, 'Y', w2.orgn_code, w2.whse_code) AND
225 -- Same locations for whse mapped as subinventory will be created as diff locators.
226 inv.whse_code||inv.location <> inv2.whse_code||inv2.location 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
236 INSERT INTO ic_lots_mst_mig (
237 ITEM_ID,
238 LOT_ID,
239 ORGANIZATION_ID,
240 WHSE_MAPPING_CODE,
241 WHSE_CODE,
242 LOCATION,
243 STATUS,
244 PARENT_LOT_NUMBER,
245 LOT_NUMBER,
246 MIGRATED_IND,
247 ADDITIONAL_STATUS_LOT,
248 USER_UPDATED_IND,
249 CREATION_DATE,
250 CREATED_BY,
251 LAST_UPDATE_DATE,
252 LAST_UPDATED_BY,
253 LAST_UPDATE_LOGIN)
254 SELECT item_id, lot_id, NULL, whse_mapping_code, whse_code, location, lot_status, parent_lot,
255 lot_no ||
256 DECODE (sublot_no, NULL, NULL,
257 (SELECT lot_sublot_delimiter FROM gmi_migration_parameters)) ||
258 sublot_no ||
259 DECODE (lot_status, nvl(mig_status, first_status), NULL, '-' || lot_status) lot_number,
260 0 MIGRATED_IND,
261 DECODE (lot_status, first_status, 0, 1) ADDITIONAL_STATUS_LOT,
262 0 USER_UPDATED_IND ,
263 sysdate, 0, sysdate, 0, NULL
264 FROM (
265 SELECT i.item_id, l.lot_id, l.lot_no, l.sublot_no, w.organization_id,
266 DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code) whse_mapping_code,
267 inv.whse_code, inv.location, inv.lot_status,
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
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
282 inv.item_id = l.item_id 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
292 -- Compare the balances within the mapped org
293 DECODE(w.subinventory_ind_flag, 'Y', w.orgn_code, w.whse_code) =
294 DECODE(w2.subinventory_ind_flag, 'Y', w2.orgn_code, w2.whse_code) AND
295 -- Same locations for whse mapped as subinventory will be created as diff locators.
296 inv.whse_code||inv.location <> inv2.whse_code||inv2.location 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;
306 COMMIT;
307 end populate_lot_migration;
308
309
310 /*====================================================================
311 -- PROCEDURE:
312 -- get_item_no
313 --
314 -- DESCRIPTION:
315 -- This procedure returns the item no for the passed in item id
316 --
317 -- PARAMETERS:
318 --
319 --
320 -- HISTORY
321 -- Thomas Daniel - Created - 04/10/06
322 --====================================================================*/
323 FUNCTION get_item_no(p_item_id NUMBER) RETURN VARCHAR2 IS
324 l_item_no VARCHAR2(80);
325 BEGIN
326 SELECT item_no INTO l_item_no
327 FROM ic_item_mst
328 WHERE item_id = p_item_id;
329 RETURN (l_item_no);
330 END get_item_no;
331
332 /*====================================================================
333 -- PROCEDURE:
334 -- get_lot_no
335 --
336 -- DESCRIPTION:
337 -- This procedure returns the lot and sublot no for the passed in lot id
338 --
339 -- PARAMETERS:
340 --
341 --
342 -- HISTORY
343 -- Thomas Daniel - Created - 04/10/06
344 --====================================================================*/
345
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
355 /*====================================================================
356 -- PROCEDURE:
357 -- get_orgn_code
358 --
359 -- DESCRIPTION:
360 -- This procedure returns the organization for the passed in warehouse
361 --
362 -- PARAMETERS:
363 --
364 --
365 -- HISTORY
366 -- Thomas Daniel - Created - 04/10/06
367 --====================================================================*/
368
369 FUNCTION get_orgn_code (p_whse_code VARCHAR2) RETURN VARCHAR2 IS
370 l_orgn_code VARCHAR2(4);
371 l_subinventory_ind VARCHAR2(1);
372 l_mtl_organization_id NUMBER(15);
373 l_organization_code VARCHAR2(10);
374 BEGIN
375 SELECT subinventory_ind_flag, orgn_code, mtl_organization_id
376 INTO l_subinventory_ind, l_orgn_code, l_mtl_organization_id
377 FROM ic_whse_mst
378 WHERE whse_code = p_whse_code;
379 IF NVL(l_subinventory_ind, 'N') = 'N' THEN
380 SELECT organization_code INTO l_organization_code
381 FROM mtl_parameters
382 WHERE organization_id = l_mtl_organization_id;
383 ELSE
384 l_organization_code := l_orgn_code;
385 END IF;
386 RETURN (l_organization_code);
387 END get_orgn_code;
388
389
390 END;
[Home] [Help]