[Home] [Help]
PACKAGE BODY: APPS.GMIGUTL
Source
1 PACKAGE BODY GMIGUTL AS
2 /* $Header: GMIGUTLB.pls 120.2 2006/09/13 15:26:13 jgogna noship $ */
3
4 /* +==========================================================================+
5 | Copyright (c) 1998 Oracle Corporation |
6 | Redwood Shores, CA, USA |
7 | All rights reserved. |
8 +==========================================================================+
9 | FILE NAME |
10 | GMIGUTLB.pls |
11 | |
12 | PACKAGE NAME |
13 | GMIGUTL |
14 | |
15 | DESCRIPTION |
16 | This Package contains global inventory utility procedures and |
17 | and variables. |
18 | |
19 | CONTENTS |
20 | |
21 | Get_Item |
22 | Get_Lot |
23 | Get_Warehouse |
24 | Get_Loct_inv |
25 | Get_Um |
26 | Setup |
27 | Get_Transfer |
28 | |
29 | HISTORY |
30 +==========================================================================+
31 */
32 /* +=========================================================================+
33 | PROCEDURE NAME |
34 | Setup |
35 | |
36 | USAGE |
37 | Used to setup global constants etc |
38 | |
39 | PARAMETERS |
40 | p_user_name VARCHAR2 |
41 | |
42 | RETURNS |
43 | Global variables updated |
44 | |
45 | HISTORY |
46 | Joe DiIorio 10/23/2001 11.5.1H BUG#1989860 - Removed Intrastat |
47 | profile retrieval. |
48 | Jalaj Srivastava Bug 2649596 |
49 | Modified the check for default user id. Earlier we were |
50 | checking for the error condition default user id is 0 but |
51 | 0 is a valid user id for user sysadmin. |
52 | Tony Cataldo Bug 2343411 |
53 | Added def lot desc profile option |
54 | Joe DiIorio Bug#2643440 11.5.1J - added nocopy. |
55 +=========================================================================+
56 */
57 FUNCTION Setup (p_user_name IN VARCHAR2) RETURN BOOLEAN
58 IS
59 BEGIN
60
61 DEFAULT_USER := NVL(p_user_name,'OPM');
62
63 API_VERSION := 3.0;
64
65 DEFAULT_LOGIN := TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'));
66
67 GMA_GLOBAL_GRP.Get_who( p_user_name => DEFAULT_USER
68 , x_user_id => DEFAULT_USER_ID
69 );
70
71 /* Removed the check for invalid users. Bug 5529003 */
72 IF (DEFAULT_USER = 'OPM') THEN
73 DEFAULT_USER_ID := -1;
74 END IF;
75
76
77 /* Get required system constants */
78 SY$CPG_INSTALL := FND_PROFILE.Value_Specific( name => 'SY$CPG_INSTALL'
79 , user_id => DEFAULT_USER_ID
80 );
81 IF (SY$CPG_INSTALL IS NULL)
82 THEN
83 SY$CPG_INSTALL := '0';
84 END IF;
85
86 IC$DEFAULT_LOT := FND_PROFILE.Value_Specific( name => 'IC$DEFAULT_LOT'
87 , user_id => DEFAULT_USER_ID
88 );
89 IF (IC$DEFAULT_LOT IS NULL)
90 THEN
91 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
92 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$DEFAULT_LOT');
93 FND_MSG_PUB.Add;
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96
97 /* Bug 2343411 Set up the profile option for the specfic user for def lot desc */
98 IC$DEFAULT_LOT_DESC := FND_PROFILE.Value_Specific( name => 'IC$DEFAULT_LOT_DESC'
99 , user_id => DEFAULT_USER_ID
100 );
101 IF (IC$DEFAULT_LOT_DESC IS NULL)
102 THEN
103 IC$DEFAULT_LOT_DESC := 2;
104 END IF;
105
106 IC$DEFAULT_LOCT := FND_PROFILE.Value_Specific( name => 'IC$DEFAULT_LOCT'
107 , user_id => DEFAULT_USER_ID
108 );
109 IF (IC$DEFAULT_LOCT IS NULL)
110 THEN
111 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
112 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$DEFAULT_LOCT');
113 FND_MSG_PUB.Add;
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116
117 IC$API_ALLOW_INACTIVE := FND_PROFILE.Value_Specific( name=> 'IC$API_ALLOW_INACTIVE'
118 , user_id => DEFAULT_USER_ID
119 );
120 IF (IC$API_ALLOW_INACTIVE IS NULL)
121 THEN
122 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
123 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$API_ALLOW_INACTIVE');
124 FND_MSG_PUB.Add;
125 RAISE FND_API.G_EXC_ERROR;
126 END IF;
127
128 IC$ALLOWNEGINV := FND_PROFILE.Value_Specific( name => 'IC$ALLOWNEGINV'
129 , user_id => DEFAULT_USER_ID
130 );
131 IF (IC$ALLOWNEGINV IS NULL)
132 THEN
133 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
134 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$ALLOWNEGINV');
135 FND_MSG_PUB.Add;
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138
139 IC$MOVEDIFFSTAT := FND_PROFILE.Value_Specific( name => 'IC$MOVEDIFFSTAT'
140 , user_id => DEFAULT_USER_ID
141 );
142 IF (IC$MOVEDIFFSTAT IS NULL)
143 THEN
144 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
145 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$MOVEDIFFSTAT');
146 FND_MSG_PUB.Add;
147 RAISE FND_API.G_EXC_ERROR;
148 END IF;
149
150 /*====================================================================
151 Joe DiIorio 10/23/2001 11.5.1H BUG#1989860 - Removed Intrastat.
152
153 SY$INTRASTAT := FND_PROFILE.Value_Specific( name => 'SY$INTRASTAT'
154 , user_id => DEFAULT_USER_ID
155 );
156 IF (SY$INTRASTAT IS NULL)
157 THEN
158 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
159 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','SY$INTRASTAT');
160 FND_MSG_PUB.Add;
161 RAISE FND_API.G_EXC_ERROR;
162 END IF;
163 ==================================================================*/
164
165 RETURN TRUE;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169 RETURN FALSE;
170 END Setup;
171
172 /* +=========================================================================+
173 | PROCEDURE NAME |
174 | Get_Item |
175 | |
176 | USAGE |
177 | Used to retrieve item master details |
178 | |
179 | DESCRIPTION |
180 | This procedure is used to retrieve all details from ic_item_mst |
181 | |
182 | PARAMETERS |
183 | p_item_no IN ic_item_mst.item_no%TYPE - the key to select upon |
184 | x_ic_item_mst OUT RECORD - Record containing ic_item_mst |
185 | x_ic_item_cpg OUT RECORD - Record containing ic_item_cpg |
186 | |
187 | HISTORY |
188 +=========================================================================+
189 */
190 PROCEDURE Get_Item
191 ( p_item_no IN ic_item_mst.item_no%TYPE
192 , x_ic_item_mst_row OUT NOCOPY ic_item_mst%ROWTYPE
193 , x_ic_item_cpg_row OUT NOCOPY ic_item_cpg%ROWTYPE
194 )
195 IS
196 BEGIN
197
198 x_ic_item_mst_row.item_no := p_item_no;
199
200 IF GMIVDBL.ic_item_mst_select(x_ic_item_mst_row, x_ic_item_mst_row)
201 THEN
202 -- Jatinder - B3158806 Removed the CPG install check.
203 x_ic_item_cpg_row.item_id := x_ic_item_mst_row.item_id;
204 IF GMIVDBL.ic_item_cpg_select(x_ic_item_cpg_row, x_ic_item_cpg_row) THEN
205 RETURN;
206 END IF;
207 RETURN;
208 END IF;
209
210 RAISE FND_API.G_EXC_ERROR;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 x_ic_item_mst_row.item_no := NULL;
215
216 END Get_Item;
217
218 /* +=========================================================================+
219 | PROCEDURE NAME |
220 | Get_Lot |
221 | |
222 | USAGE |
223 | Used to retrieve lot master details |
224 | |
225 | DESCRIPTION |
226 | This procedure is used to retrieve all details from ic_lots_mst |
227 | |
228 | PARAMETERS |
229 | p_item_id IN NUMBER - Item ID of lot to be retrieved |
230 | p_lot_no IN VARCHAR2(32) - Lot number of lot to be retrieved |
231 | p_sublot_no IN VARCHAR2(32) - Sublot number to be retrieved |
232 | x_ic_lots_mst OUT RECORD - Record containing ic_lots_mst |
233 | x_ic_lots_cpg OUT RECORD - Record containing ic_lots_cpg |
234 | |
235 | HISTORY |
236 +=========================================================================+
237 */
238 PROCEDURE Get_Lot
239 ( p_item_id IN ic_lots_mst.item_id%TYPE
240 , p_lot_no IN ic_lots_mst.lot_no%TYPE
241 , p_sublot_no IN ic_lots_mst.sublot_no%TYPE
242 , x_ic_lots_mst_row OUT NOCOPY ic_lots_mst%ROWTYPE
243 , x_ic_lots_cpg_row OUT NOCOPY ic_lots_cpg%ROWTYPE
244 )
245 IS
246 BEGIN
247 x_ic_lots_mst_row.item_id := p_item_id;
248 x_ic_lots_mst_row.lot_no := p_lot_no;
249 x_ic_lots_mst_row.sublot_no := p_sublot_no;
250
251 IF GMIVDBL.ic_lots_mst_select(x_ic_lots_mst_row, x_ic_lots_mst_row)
252 THEN
253 /* Jalaj Srivastava - Bug 3158806. Remove CPG_INSTALL check. */
254 x_ic_lots_cpg_row.item_id := x_ic_lots_mst_row.item_id;
255 x_ic_lots_cpg_row.lot_id := x_ic_lots_mst_row.lot_id;
256 IF GMIVDBL.ic_lots_cpg_select(x_ic_lots_cpg_row, x_ic_lots_cpg_row)
257 THEN
258 RETURN;
259 END IF;
260 RETURN;
261 END IF;
262
263 RAISE FND_API.G_EXC_ERROR;
264
265 EXCEPTION
266 WHEN OTHERS THEN
267 x_ic_lots_mst_row.lot_id := NULL;
268 END Get_Lot;
269
270 /* +=========================================================================+
271 | PROCEDURE NAME |
272 | Get_warehouse |
273 | |
274 | USAGE |
275 | Used to retrieve warehouse details |
276 | |
277 | DESCRIPTION |
278 | This procedure is used to retrieve all details from ic_whse_mst |
279 | |
280 | PARAMETERS |
281 | p_item_no IN VARCHAR2(4) - Warehouse code to be retrieved |
282 | x_ic_whse_mst OUT RECORD - Record containing ic_whse_mst |
283 | |
284 | HISTORY |
285 +=========================================================================+
286 */
287 PROCEDURE Get_Warehouse
288 ( p_whse_code IN ic_whse_mst.whse_code%TYPE
289 , x_ic_whse_mst_row OUT NOCOPY ic_whse_mst%ROWTYPE
290 )
291 IS
292 BEGIN
293 x_ic_whse_mst_row.whse_code := p_whse_code;
294 IF GMIVDBL.ic_whse_mst_select(x_ic_whse_mst_row, x_ic_whse_mst_row)
295 THEN
296 RETURN;
297 END IF;
298
299 RAISE FND_API.G_EXC_ERROR;
300
301 EXCEPTION
302 WHEN OTHERS THEN
303 x_ic_whse_mst_row.whse_code := NULL;
304 END Get_Warehouse;
305
306 /* +=========================================================================+
307 | PROCEDURE NAME |
308 | Get_loct_inv |
309 | |
310 | USAGE |
311 | Used to retrieve location inventory details |
312 | |
313 | DESCRIPTION |
314 | This procedure is used to retrieve all details from ic_loct_inv |
315 | |
316 | PARAMETERS |
317 | p_item_id IN NUMBER - Item ID |
318 | p_whse_code IN VARCHAR2(4) - Warehouse code |
319 | p_lot_id IN NUMBER - Lot ID |
320 | p_location IN VARCHAR2(4) - Location code |
321 | x_ic_loct_inv IN RECORD - Record containing ic_loct_inv details |
322 | |
323 | HISTORY |
327 ( p_item_id IN ic_loct_inv.item_id%TYPE
324 +=========================================================================+
325 */
326 PROCEDURE Get_Loct_inv
328 , p_whse_code IN ic_loct_inv.whse_code%TYPE
329 , p_lot_id IN ic_loct_inv.lot_id%TYPE
330 , p_location IN ic_loct_inv.location%TYPE
331 , x_ic_loct_inv_row OUT NOCOPY ic_loct_inv%ROWTYPE
332 )
333 IS
334 BEGIN
335 x_ic_loct_inv_row.item_id := p_item_id;
336 x_ic_loct_inv_row.whse_code := p_whse_code;
337 x_ic_loct_inv_row.lot_id := p_lot_id;
338 x_ic_loct_inv_row.location := p_location;
339
340 IF GMIVDBL.ic_loct_inv_select(x_ic_loct_inv_row, x_ic_loct_inv_row)
341 THEN
342 RETURN;
343 END IF;
344
345 RAISE FND_API.G_EXC_ERROR;
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 x_ic_loct_inv_row.loct_onhand := NULL;
350
351 END Get_Loct_inv;
352
353 /* +=========================================================================+
354 | PROCEDURE NAME |
355 | Get_Um |
356 | |
357 | USAGE |
358 | Used to retrieve unit of measure details |
359 | |
360 | DESCRIPTION |
361 | This procedure is used to retrieve all details from sy_uoms_mst |
362 | and sy_uoms_typ |
363 | |
364 | PARAMETERS |
365 | p_um_code IN VARCHAR2(4) - Unit of measure code to be retrieved |
366 | x_sy_uoms_mst OUT RECORD - Record containing sy_uoms_mst details |
367 | x_sy_uoms_typ OUT RECORD - Record containing sy_uoms_typ details |
368 | x_error_code OUT NUMBER - Error code returned |
369 | |
370 | HISTORY |
371 +=========================================================================+
372 */
373 PROCEDURE Get_Um
374 ( p_um_code IN sy_uoms_mst.um_code%TYPE
375 , x_sy_uoms_mst_row OUT NOCOPY sy_uoms_mst%ROWTYPE
376 , x_sy_uoms_typ_row OUT NOCOPY sy_uoms_typ%ROWTYPE
377 )
378 IS
379 BEGIN
380 x_sy_uoms_mst_row.um_code := p_um_code;
381 IF GMIVDBL.sy_uoms_mst_select(x_sy_uoms_mst_row, x_sy_uoms_mst_row)
382 THEN
383 x_sy_uoms_typ_row.um_type := x_sy_uoms_mst_row.um_type;
384 IF GMIVDBL.sy_uoms_typ_select (x_sy_uoms_typ_row, x_sy_uoms_typ_row)
385 THEN
386 RETURN;
387 END IF;
388 END IF;
389
390 RAISE FND_API.G_EXC_ERROR;
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 x_sy_uoms_mst_row.um_code := NULL;
395 END Get_Um;
396
397 FUNCTION v_expaction_code
398 ( p_action_code IN qc_actn_mst.action_code%TYPE
399 , x_qc_actn_mst_row OUT NOCOPY qc_actn_mst%ROWTYPE
400 )
401 RETURN BOOLEAN
402 IS
403 BEGIN
404 x_qc_actn_mst_row.action_code := p_action_code;
405 IF GMIVDBL.qc_actn_mst_select(x_qc_actn_mst_row, x_qc_actn_mst_row)
406 THEN
407 IF x_qc_actn_mst_row.delete_mark = 0
408 THEN
409 RETURN TRUE;
410 END IF;
411 END IF;
412
413 RAISE FND_API.G_EXC_ERROR;
414
415 EXCEPTION
416 WHEN OTHERS
417 THEN
418 RETURN FALSE;
419 END v_expaction_code;
420
421
422
423 FUNCTION v_qc_grade
424 ( p_qc_grade IN qc_grad_mst.qc_grade%TYPE
425 , x_qc_grad_mst_row OUT NOCOPY qc_grad_mst%ROWTYPE
426 )
427 RETURN BOOLEAN
428 IS
429 BEGIN
430 x_qc_grad_mst_row.qc_grade := p_qc_grade;
431 IF GMIVDBL.qc_grad_mst_select(x_qc_grad_mst_row, x_qc_grad_mst_row)
432 THEN
433 IF x_qc_grad_mst_row.delete_mark = 0
434 THEN
435 RETURN TRUE;
436 END IF;
437 END IF;
438
439 RAISE FND_API.G_EXC_ERROR;
440
441 EXCEPTION
442 WHEN OTHERS
443 THEN
444 RETURN FALSE;
445 END v_qc_grade;
446
447 FUNCTION v_reason_code
448 ( p_reason_code IN sy_reas_cds.reason_code%TYPE
449 , x_sy_reas_cds_row OUT NOCOPY sy_reas_cds%ROWTYPE
450 )
451 RETURN BOOLEAN
452 IS
453 BEGIN
454 x_sy_reas_cds_row.reason_code := p_reason_code;
455 IF GMIVDBL.sy_reas_cds_select(x_sy_reas_cds_row, x_sy_reas_cds_row)
456 THEN
457 IF x_sy_reas_cds_row.delete_mark = 0
458 THEN
459 RETURN TRUE;
460 END IF;
461 END IF;
462
463 RAISE FND_API.G_EXC_ERROR;
464
465 EXCEPTION
466 WHEN OTHERS
467 THEN
468 RETURN FALSE;
469 END v_reason_code;
470
471 FUNCTION v_ship_vendor
472 ( p_vendor_no IN po_vend_mst.vendor_no%TYPE
473 , x_po_vend_mst_row OUT NOCOPY po_vend_mst%ROWTYPE
474 )
475 RETURN BOOLEAN
476 IS
477 BEGIN
478 x_po_vend_mst_row.vendor_no := p_vendor_no;
479 IF GMIVDBL.po_vend_mst_select(x_po_vend_mst_row, x_po_vend_mst_row)
480 THEN
481 IF x_po_vend_mst_row.delete_mark = 0
482 THEN
483 RETURN TRUE;
484 END IF;
485 END IF;
486
487 RAISE FND_API.G_EXC_ERROR;
488
489 EXCEPTION
490 WHEN OTHERS
491 THEN
492 RETURN FALSE;
493 END v_ship_vendor;
494
495
496 FUNCTION v_lot_status
497 (p_lot_status IN ic_lots_sts.lot_status%TYPE
498 ,x_ic_lots_sts_row OUT NOCOPY ic_lots_sts%ROWTYPE
499 )
500 RETURN BOOLEAN
501 IS
502 BEGIN
503 x_ic_lots_sts_row.lot_status := p_lot_status;
504 IF GMIVDBL.ic_lots_sts_select(x_ic_lots_sts_row, x_ic_lots_sts_row)
505 THEN
506 IF x_ic_lots_sts_row.delete_mark = 0
507 THEN
508 RETURN TRUE;
509 END IF;
510 END IF;
511
512 RAISE FND_API.G_EXC_ERROR;
513
514 EXCEPTION
515 WHEN OTHERS
516 THEN
517 RETURN FALSE;
518 END v_lot_status;
519
520 /*
521 PROCEDURE Get_Transfer
522 ( p_orgn_code IN ic_xfer_mst.orgn_code%TYPE
523 , p_transfer_no IN ic_xfer_mst.transfer_no%TYPE
524 , x_ic_xfer_mst_row OUT NOCOPY ic_xfer_mst%ROWTYPE
525 )
526 IS
527 BEGIN
528 x_ic_xfer_mst_row.orgn_code := p_orgn_code;
529 x_ic_xfer_mst_row.transfer_no := p_transfer_no;
530
531 IF GMIVDBL.ic_xfer_mst_select(x_ic_xfer_mst_row, x_ic_xfer_mst_row)
532 THEN
533 RETURN;
534 END IF;
535
536 RAISE FND_API.G_EXC_ERROR;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 x_ic_xfer_mst_row.transfer_no := NULL;
541 END Get_Transfer;
542
543 */
544
545 END GMIGUTL;