[Home] [Help]
PACKAGE BODY: APPS.GMI_GLOBAL_GRP
Source
1 PACKAGE BODY GMI_GLOBAL_GRP AS
2 -- $Header: GMIGGBLB.pls 115.5 2002/10/25 18:14:30 jdiiorio gmigapib.pls $
3 -- Body start of comments
4 --+==========================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+==========================================================================+
9 --| FILE NAME |
10 --| GMIGGBLB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMI_GLOBAL_GRP |
14 --| |
15 --| DESCRIPTION |
16 --| This Package contains global Inventory procedures |
17 --| |
18 --| CONTENTS |
19 --| |
20 --| Get_Item |
21 --| Get_Lot |
22 --| Get_Warehouse |
23 --| Get_Loct_inv |
24 --| Get_Um |
25 --| Get_Lot_Inv |
26 --| |
27 --| HISTORY |
28 --| 01-OCT-1998 M.Godfrey Created |
29 --| 16-AUG-1999 Liz Enstone B965832(3) Remove query on |
30 --| IC_LOTS_CPG |
31 --| 25-OCT-2002 Joe DiIorio Bug#2643330 - added nocopy. |
32 --+==========================================================================+
33 -- Body end of comments
34
35 -- Proc start of comments
36 --+=========================================================================+
37 --| PROCEDURE NAME |
38 --| Get_Item |
39 --| |
40 --| USAGE |
41 --| Used to retrieve item master details |
42 --| |
43 --| DESCRIPTION |
44 --| This procedure is used to retrieve all details from ic_item_mst |
45 --| |
46 --| PARAMETERS |
47 --| p_item_no IN VARCHAR2(32) - Item number to be retrieved |
48 --| x_ic_item_mst OUT RECORD - Record containing ic_item_mst |
49 --| x_ic_item_cpg OUT RECORD - Record containing ic_item_cpg |
50 --| |
51 --| HISTORY |
52 --| 01-OCT-1998 M.Godfrey Created |
53 --+=========================================================================+
54 -- Proc end of comments
55 PROCEDURE Get_Item
56 ( p_item_no IN ic_item_mst.item_no%TYPE
57 , x_ic_item_mst OUT NOCOPY ic_item_mst%ROWTYPE
58 , x_ic_item_cpg OUT NOCOPY ic_item_cpg%ROWTYPE
59 )
60 IS
61 CURSOR ic_item_mst_c1 IS
62 SELECT
63 *
64 FROM
65 ic_item_mst
66 WHERE
67 item_no = p_item_no;
68
69 CURSOR ic_item_cpg_c1(v_item_id ic_item_mst.item_id%TYPE) IS
70 SELECT
71 *
72 FROM
73 ic_item_cpg
74 WHERE
75 item_id = v_item_id;
76
77 l_ic_item_mst ic_item_mst%ROWTYPE;
78
79 BEGIN
80
81 OPEN ic_item_mst_c1;
82
83 FETCH ic_item_mst_c1 INTO l_ic_item_mst;
84
85 IF (ic_item_mst_c1%NOTFOUND)
86 THEN
87 x_ic_item_mst.item_id := 0;
88 ELSE
89 x_ic_item_mst := l_ic_item_mst;
90 OPEN ic_item_cpg_c1(l_ic_item_mst.item_id);
91 FETCH ic_item_cpg_c1 INTO x_ic_item_cpg;
92
93 IF (ic_item_cpg_c1%NOTFOUND)
94 THEN
95 x_ic_item_cpg.ic_matr_days := 0;
96 x_ic_item_cpg.ic_hold_days := 0;
97 END IF;
98 CLOSE ic_item_cpg_c1;
99 END IF;
100
101 CLOSE ic_item_mst_c1;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 RAISE;
106
107 END Get_Item;
108
109 --+=========================================================================+
110 --| PROCEDURE NAME |
111 --| Get_Lot |
112 --| |
113 --| USAGE |
114 --| Used to retrieve lot master details |
115 --| |
116 --| DESCRIPTION |
117 --| This procedure is used to retrieve all details from ic_lots_mst |
118 --| |
119 --| PARAMETERS |
120 --| p_item_id IN NUMBER - Item ID of lot to be retrieved |
121 --| p_lot_no IN VARCHAR2(32) - Lot number of lot to be retrieved |
122 --| p_sublot_no IN VARCHAR2(32) - Sublot number to be retrieved |
123 --| x_ic_lots_mst OUT RECORD - Record containing ic_lots_mst |
124 --| x_ic_lots_cpg OUT RECORD - Record containing ic_lots_cpg |
125 --| |
126 --| HISTORY |
127 --| 01-OCT-1998 M.Godfrey Created |
128 --+=========================================================================+
129 PROCEDURE Get_Lot
130 ( p_item_id IN ic_lots_mst.item_id%TYPE
131 , p_lot_no IN ic_lots_mst.lot_no%TYPE
132 , p_sublot_no IN ic_lots_mst.sublot_no%TYPE
133 , x_ic_lots_mst OUT NOCOPY ic_lots_mst%ROWTYPE
134 , x_ic_lots_cpg OUT NOCOPY ic_lots_cpg%ROWTYPE
135 )
136 IS
137 CURSOR ic_lots_mst_c1 IS
138 SELECT
139 *
140 FROM
141 ic_lots_mst
142 WHERE
143 lot_no = p_lot_no
144 AND ( sublot_no = p_sublot_no OR
145 sublot_no is NULL)
146 AND item_id = p_item_id;
147
148 CURSOR ic_lots_cpg_c1(v_lot_id ic_lots_mst.lot_id%TYPE) IS
149 SELECT
150 *
151 FROM
152 ic_lots_cpg
153 WHERE
154 lot_id = v_lot_id;
155
156 l_ic_lots_mst ic_lots_mst%ROWTYPE;
157
158 BEGIN
159
160
161 OPEN ic_lots_mst_c1;
162
163 FETCH ic_lots_mst_c1 INTO l_ic_lots_mst;
164 IF (ic_lots_mst_c1%NOTFOUND)
165 THEN
166 x_ic_lots_mst.lot_id := -1;
167 ELSE
168 x_ic_lots_mst := l_ic_lots_mst;
169 --B965832(3) Get rid of this select
170 --OPEN ic_lots_cpg_c1(l_ic_lots_mst.lot_id);
171 --FETCH ic_lots_cpg_c1 INTO x_ic_lots_cpg;
172 --IF (ic_lots_cpg_c1%NOTFOUND)
173 --THEN
174 -- x_ic_lots_mst.lot_id := -1;
175 -- END IF;
176 -- CLOSE ic_lots_cpg_c1;
177 --B965832(3) End
178 END IF;
179
180 CLOSE ic_lots_mst_c1;
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 RAISE;
185
186 END Get_Lot;
187
188 --+=========================================================================+
189 --| PROCEDURE NAME |
190 --| Get_warehouse |
191 --| |
192 --| USAGE |
193 --| Used to retrieve warehouse details |
194 --| |
195 --| DESCRIPTION |
196 --| This procedure is used to retrieve all details from ic_whse_mst |
197 --| |
198 --| PARAMETERS |
199 --| p_item_no IN VARCHAR2(32) - Warehouse code to be retrieved |
200 --| x_ic_whse_mst OUT RECORD - Record containing ic_whse_mst |
201 --| |
202 --| HISTORY |
203 --| 01-OCT-1998 M.Godfrey Created |
204 --+=========================================================================+
205 PROCEDURE Get_Warehouse
206 ( p_whse_code IN ic_whse_mst.whse_code%TYPE
207 , x_ic_whse_mst OUT NOCOPY ic_whse_mst%ROWTYPE
208 )
209 IS
210 CURSOR ic_whse_mst_c1 IS
211 SELECT
212 *
213 FROM
214 ic_whse_mst
215 WHERE
216 whse_code = p_whse_code;
217
218 BEGIN
219
220 OPEN ic_whse_mst_c1;
221
222 FETCH ic_whse_mst_c1 INTO x_ic_whse_mst;
223
224 IF (ic_whse_mst_c1%NOTFOUND)
225 THEN
226 x_ic_whse_mst.whse_code := NULL;
227 END IF;
228
229 CLOSE ic_whse_mst_c1;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 RAISE;
234
235 END Get_Warehouse;
236
237 --+=========================================================================+
238 --| PROCEDURE NAME |
239 --| Get_loct_inv |
240 --| |
241 --| USAGE |
242 --| Used to retrieve location inventory details |
243 --| |
244 --| DESCRIPTION |
245 --| This procedure is used to retrieve all details from ic_loct_inv |
246 --| |
247 --| PARAMETERS |
248 --| p_item_id IN NUMBER - Item ID |
249 --| p_whse_code IN VARCHAR2(4) - Warehouse code |
250 --| p_lot_id IN NUMBER - Lot ID |
251 --| p_location IN VARCHAR2(4) - Location code |
252 --| p_delete_mark IN NUMBER - Delete marker (Default 0) |
253 --| x_ic_loct_inv IN RECORD - Record containing ic_loct_inv details |
254 --| |
255 --| HISTORY |
256 --| 01-OCT-1998 M.Godfrey Created |
257 --+=========================================================================+
258 PROCEDURE Get_Loct_inv
259 ( p_item_id IN ic_loct_inv.item_id%TYPE
260 , p_whse_code IN ic_loct_inv.whse_code%TYPE
261 , p_lot_id IN ic_loct_inv.lot_id%TYPE
262 , p_location IN ic_loct_inv.location%TYPE
263 , p_delete_mark IN ic_loct_inv.delete_mark%TYPE
264 , x_ic_loct_inv OUT NOCOPY ic_loct_inv%ROWTYPE
265 )
266 IS
267 CURSOR ic_loct_inv_c1 IS
268 SELECT
269 *
270 FROM
271 ic_loct_inv
272 WHERE
273 item_id = p_item_id
274 AND whse_code = p_whse_code
275 AND lot_id = p_lot_id
276 AND location = p_location
277 AND delete_mark = p_delete_mark;
278
279 BEGIN
280
281 OPEN ic_loct_inv_c1;
282
283 FETCH ic_loct_inv_c1 INTO x_ic_loct_inv;
284
285 IF (ic_loct_inv_c1%NOTFOUND)
286 THEN
287 x_ic_loct_inv.item_id := 0;
288 END IF;
289
290 CLOSE ic_loct_inv_c1;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 RAISE;
295
296 END Get_Loct_inv;
297
298 --+=========================================================================+
299 --| PROCEDURE NAME |
300 --| Get_Um |
301 --| |
302 --| USAGE |
303 --| Used to retrieve unit of measure details |
304 --| |
305 --| DESCRIPTION |
306 --| This procedure is used to retrieve all details from sy_uoms_mst |
307 --| and sy_uoms_typ |
308 --| |
309 --| PARAMETERS |
310 --| p_um_code IN VARCHAR2(4) - Unit of measure code to be retrieved |
311 --| x_sy_uoms_mst OUT RECORD - Record containing sy_uoms_mst details |
312 --| x_sy_uoms_typ OUT RECORD - Record containing sy_uoms_typ details |
313 --| x_error_code OUT NUMBER - Error code returned |
314 --| |
315 --| HISTORY |
316 --| 01-OCT-1998 M.Godfrey Created |
317 --+=========================================================================+
318 PROCEDURE Get_Um
319 ( p_um_code IN sy_uoms_mst.um_code%TYPE
320 , x_sy_uoms_mst OUT NOCOPY sy_uoms_mst%ROWTYPE
321 , x_sy_uoms_typ OUT NOCOPY sy_uoms_typ%ROWTYPE
322 , x_error_code OUT NOCOPY NUMBER
323 )
324 IS
325 CURSOR sy_uoms_mst_c1 IS
326 SELECT
327 *
328 FROM
329 sy_uoms_mst
330 WHERE
331 um_code = p_um_code
332 AND delete_mark = 0;
333
334 CURSOR sy_uoms_typ_c1(v_um_type sy_uoms_typ.um_type%TYPE) IS
335 SELECT
336 *
337 FROM
338 sy_uoms_typ
339 WHERE
340 um_type = v_um_type
341 AND delete_mark = 0;
342
343 l_sy_uoms_mst sy_uoms_mst%ROWTYPE;
344
345 BEGIN
346
347 x_error_code := 0;
348
349 OPEN sy_uoms_mst_c1;
350
351 FETCH sy_uoms_mst_c1 INTO l_sy_uoms_mst;
352
353 IF (sy_uoms_mst_c1%NOTFOUND)
354 THEN
355 x_error_code := -1;
356 ELSE
357 x_sy_uoms_mst := l_sy_uoms_mst;
358 OPEN sy_uoms_typ_c1(l_sy_uoms_mst.um_type);
359 FETCH sy_uoms_typ_c1 INTO x_sy_uoms_typ;
360
361 IF (sy_uoms_typ_c1%NOTFOUND)
362 THEN
363 x_error_code := -2;
364 END IF;
365 CLOSE sy_uoms_typ_c1;
366 END IF;
367
368 CLOSE sy_uoms_mst_c1;
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 RAISE;
373
374 END Get_Um;
375
376 --+=========================================================================+
377 --| PROCEDURE NAME |
378 --| Get_Lot_inv |
382 --| |
379 --| |
380 --| USAGE |
381 --| Used to retrieve lot inventory on-hand values |
383 --| DESCRIPTION |
384 --| This procedure is used to retrieve all details from ic_lot_inv |
385 --| for a given lot / sublot |
386 --| |
387 --| PARAMETERS |
388 --| p_item_id IN NUMBER - Item ID |
389 --| p_lot_id IN NUMBER - Lot ID |
390 --| p_delete_mark IN NUMBER - Delete marker (Default 0) |
391 --| x_lot_onhand OUT NUMBER - lot on-hand quantity |
392 --| |
393 --| HISTORY |
394 --| 01-OCT-1998 M.Godfrey Created |
395 --+=========================================================================+
396 PROCEDURE Get_Lot_inv
397 ( p_item_id IN ic_loct_inv.item_id%TYPE
398 , p_lot_id IN ic_loct_inv.lot_id%TYPE
399 , p_delete_mark IN ic_loct_inv.delete_mark%TYPE
400 , x_lot_onhand OUT NOCOPY NUMBER
401 )
402 IS
403 CURSOR ic_lot_inv_c1 IS
404 SELECT
405 SUM(loct_onhand)
406 FROM
407 ic_loct_inv
408 WHERE
409 item_id = p_item_id
410 AND lot_id = p_lot_id
411 AND delete_mark = p_delete_mark;
412
413 BEGIN
414
415 OPEN ic_lot_inv_c1;
416
417 FETCH ic_lot_inv_c1 INTO x_lot_onhand;
418
419 IF (ic_lot_inv_c1%NOTFOUND)
420 THEN
421 x_lot_onhand :=0;
422 END IF;
423
424 CLOSE ic_lot_inv_c1;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 RAISE;
429
430 END Get_Lot_inv;
431
432 END GMI_GLOBAL_GRP;