[Home] [Help]
PACKAGE BODY: APPS.GMIVILC
Source
1 PACKAGE BODY GMIVILC AS
2 /* $Header: GMIVILCB.pls 115.15 2003/10/10 18:07:30 jdiiorio ship $ */
3 /* +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | GMIVILCB.pls |
10 | |
11 | PACKAGE NAME |
12 | GMIVILC |
13 | |
14 | DESCRIPTION |
15 | This package contains private code for Item/Lot/Sublot Uom conversion |
16 | |
17 | CONTENTS |
18 | Validate_Lot_Conversion |
19 | |
20 | HISTORY |
21 | |
22 | 02-May-2001 A. Mundhe Bug 1741321 - Added code to validate primary and |
23 | secondary UOM. |
24 | |
25 | 03-Jul-2002 A. Mundhe Bug 2446245 - Modified the code such that |
26 | typefactor rev is not recalculated unnecessarily |
27 | causing decimal precision issues. |
28 | 15-Apr-2003 J. DiIorio Bug 2880585 - Added conversion check to not allow|
29 | conversion if any transactions exist. |
30 | 10-Oct-2003 J. DiIorio Bug 3161462 - Altered cur_get_lotid to check also|
31 | on sublot value. |
32 +==========================================================================+
33 */
34 /* Global variables */
35 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVILC';
36
37 /* +==========================================================================+
38 | PROCEDURE NAME |
39 | Validate_Lot_Conversion |
40 | |
41 | TYPE |
42 | Private |
43 | |
44 | USAGE |
45 | Validate an item conversion record and set up the row for insertion |
46 | into the database |
47 | |
48 | PARAMETERS |
49 | p_api_version IN NUMBER - API Version |
50 | p_init_msg_list IN VARCHAR2 - Msg List initialization Ind |
51 | p_commit IN VARCHAR2 - Commit Indicator |
52 | p_validation_level IN VARCHAR2 - Validation Level indicator |
53 | x_return_status OUT VARCHAR2 - return Status |
54 | x_msg_count OUT NUMBER - Number of Messages returned |
55 | x_msg_data OUT VARCHAR2 - Messages in encoded format |
56 | p_item_cnv_rec IN item_cnv_rec_typ - Item Conversion details |
57 | |
58 | RETURNS |
59 | None |
60 | |
61 | HISTORY |
62 | |
63 | 02-May-2001 A. Mundhe Bug 1741321 - Added code to validate primary and |
64 | secondary UOM. |
65 | |
66 | 03-Jul-2002 A. Mundhe Bug 2446245 - Modified the code such that |
67 | typefactor rev is not recalculated unnecessarily |
68 | causing decimal precision issues. |
69 | 11-Nov-2002 J. DiIorio Bug 2643440 - 11.5.1J - added nocopy. |
70 +==========================================================================+
71 */
72 PROCEDURE Validate_Lot_Conversion
73 ( p_api_version IN NUMBER
74 , p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
75 , p_item_cnv_rec IN GMIGAPI.conv_rec_typ
76 , p_ic_item_mst_row IN ic_item_mst%ROWTYPE
77 , p_ic_lots_mst_row IN ic_lots_mst%ROWTYPE
78 , x_ic_item_cnv_row OUT NOCOPY ic_item_cnv%ROWTYPE
79 , x_return_status OUT NOCOPY VARCHAR2
80 , x_msg_count OUT NOCOPY NUMBER
81 , x_msg_data OUT NOCOPY VARCHAR2
82 )
83 IS
84 l_api_name CONSTANT VARCHAR2 (30) :='Validate Conversion';
85 l_factor NUMBER;
86 l_type_factorrev NUMBER;
87 l_from_type sy_uoms_mst.um_code%TYPE;
88 l_from_std sy_uoms_mst.um_code%TYPE;
89 l_to_type sy_uoms_mst.um_code%TYPE;
90 l_to_std sy_uoms_mst.um_code%TYPE;
91 l_item_type sy_uoms_mst.um_code%TYPE;
92 l_item_std sy_uoms_mst.um_code%TYPE;
93 l_rec_from_um sy_uoms_mst.um_code%TYPE;
94 l_rec_to_um sy_uoms_mst.um_code%TYPE;
95
96 l_um_type sy_uoms_mst.um_type%TYPE;
97 l_from_um sy_uoms_mst.um_code%TYPE;
98 l_to_um sy_uoms_mst.um_code%TYPE;
99 l_from_std_um sy_uoms_mst.um_code%TYPE;
100 l_to_std_um sy_uoms_mst.um_code%TYPE;
101 l_count NUMBER;
102
103
104 /* Bug#2880585 - New cursors for insert checks */
105
106
107 X_count NUMBER;
108 x_lot_id NUMBER;
109
110 CURSOR Cur_get_lotid IS
111 SELECT lot_id
112 FROM ic_lots_mst
113 WHERE item_id = p_ic_item_mst_row.item_id and
114 lot_no = p_item_cnv_rec.lot_no and
115 sublot_no = p_item_cnv_rec.sublot_no;
116
117 CURSOR Cur_trans_cmp IS
118 SELECT count(*)
119 FROM ic_tran_cmp
120 WHERE item_id = p_ic_item_mst_row.item_id and
121 lot_id = x_lot_id;
122
123 CURSOR Cur_trans_pnd IS
124 SELECT count(*)
125 FROM ic_tran_pnd
126 WHERE item_id = p_ic_item_mst_row.item_id and
127 lot_id = x_lot_id;
128
129 CURSOR Cur_journal IS
130 SELECT count(1)
131 FROM ic_jrnl_mst m, ic_adjs_jnl a
132 WHERE m.journal_id = a.journal_id
133 AND a.item_id = p_ic_item_mst_row.item_id
134 AND a.lot_id = x_lot_id;
135
136
137 BEGIN
138
139 /* Initialize API return status to sucess */
140 x_return_status :=FND_API.G_RET_STS_SUCCESS;
141
142 /* Ensure Upper-case columns are converted */
143
144
145
146 l_rec_to_um :=p_item_cnv_rec.to_uom;
147 l_rec_from_um :=p_item_cnv_rec.from_uom;
148
149 IF p_ic_item_mst_row.item_id = 0 OR
150 p_ic_item_mst_row.delete_mark = 1
151 THEN
152 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
153 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_cnv_rec.item_no);
154 FND_MSG_PUB.Add;
155 RAISE FND_API.G_EXC_ERROR;
156 ELSIF p_ic_item_mst_row.inactive_ind = 1
157 AND GMIGUTL.IC$API_ALLOW_INACTIVE = 0
158 THEN
159 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_ITEM_NO');
160 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_cnv_rec.item_no);
161 FND_MSG_PUB.Add;
162 x_return_status :=FND_API.G_RET_STS_ERROR;
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 IF
167 p_ic_lots_mst_row.delete_mark = 1 OR
168 p_ic_lots_mst_row.inactive_ind = 1 AND GMIGUTL.IC$API_ALLOW_INACTIVE = 0
169 THEN
170 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_NO');
171 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
172 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
173 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
174 FND_MSG_PUB.Add;
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177
178 -- Bug 1741321
179 -- Validate Primary Unit of Measure
180
181 IF NOT GMA_VALID_GRP.Validate_um(l_rec_from_um)
182 THEN
183 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
184 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_cnv_rec.item_no);
185 FND_MESSAGE.SET_TOKEN('UOM',l_rec_from_um);
186 FND_MSG_PUB.Add;
187 RAISE FND_API.G_EXC_ERROR;
188 END IF;
189
190 -- Bug 1741321
191 -- Validate Secondary Unit of Measure
192
193 IF NOT GMA_VALID_GRP.Validate_um(l_rec_to_um)
194 THEN
195 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_UOM');
196 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_item_cnv_rec.item_no);
197 FND_MESSAGE.SET_TOKEN('UOM',l_rec_to_um);
198 FND_MSG_PUB.Add;
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201
202 /* Check that conversion factor is positive value */
203 -- Bug 2446245
204 -- Initialize type_factor and type_factorrev.
205 l_factor := p_item_cnv_rec.type_factor;
206 l_type_factorrev := 1 / p_item_cnv_rec.type_factor;
207
208 IF (l_factor <= 0)
209 THEN
210 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_TYPE_FACTOR');
211 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
212 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
213 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
214 FND_MSG_PUB.Add;
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 /* Get UOM details of the from Unit of Measure. If this retrieval fails */
219 /* we'll get an exception as there's no point in continuing. */
220
221 SELECT from_type.um_type, from_type.std_um,
222 to_type.um_type, to_type.std_um,
223 item_type.um_type, item_type.std_um
224 INTO l_from_type, l_from_std, l_to_type, l_to_std,
225 l_item_type, l_item_std
226 FROM
227 sy_uoms_typ from_type,
228 sy_uoms_mst from_std,
229 sy_uoms_typ to_type,
230 sy_uoms_mst to_std,
231 sy_uoms_typ item_type,
232 sy_uoms_mst item_std
233 WHERE
234 from_type.um_type=from_std.um_type AND
235 from_std.um_code=l_rec_from_um AND
236 from_type.delete_mark=0 AND
237 from_std.delete_mark=0 AND
238 item_type.um_type=item_std.um_type AND
239 item_std.um_code=p_ic_item_mst_row.item_um AND
240 item_type.delete_mark=0 AND
241 item_std.delete_mark=0 AND
242 to_type.um_type=to_std.um_type AND
243 to_std.um_code=l_rec_to_um AND
244 to_type.delete_mark=0 AND
245 to_std.delete_mark=0;
246
247 /* Check that the from UoM type differs from the to UoM type. If not then */
248 /* error and exit */
249 IF l_from_type = l_to_type
250 THEN
251 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_ITEM_UOM_MISMATCH');
252 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
253 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
254 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
255 FND_MSG_PUB.Add;
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258
259 /* Check that the unit of measure type of the from_uom or the to_uom */
260 /* is the same as the unit of measure type of the item primary uom. */
261 /* Adjust conversion factor as appropriate. */
262
263 IF (l_item_std = l_from_std)
264 THEN
265 l_factor := 1 / l_factor;
266 l_type_factorrev := p_item_cnv_rec.type_factor;
267 l_um_type := l_to_type;
268 l_from_um := l_rec_from_um;
269 l_from_std_um := l_from_std;
270 l_to_um := l_rec_to_um;
271 l_to_std_um := l_to_std;
272 ELSIF (l_item_std = l_to_std)
273 THEN
274 /* we must use reciprocal, and swap 'to' and 'from' */
275 /* Bug 2446245 */
276 /* Calculate type_factorrev only as type_factor is already initialized. */
277 l_factor := p_item_cnv_rec.type_factor;
278 l_type_factorrev := 1 / p_item_cnv_rec.type_factor;
279 l_um_type := l_from_type;
280 l_from_um := l_rec_to_um;
281 l_to_um := l_rec_from_um;
282 l_to_std_um := l_from_std;
283 l_from_std_um := l_to_std;
284 ELSE
285 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_ITEM_UOM_MISMATCH');
286 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
287 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
288 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
289 FND_MSG_PUB.Add;
290 RAISE FND_API.G_EXC_ERROR;
291 END IF;
292
293 /* Now we have all the data required to calculate the conversion */
294 /* factor from the standard unit of measure of the unit of measure */
295 /* type of the item primary unit of measure to the standard unit */
296 /* of measure of the unit of measure type of the from_uom. */
297
298 /* First convert from from_uom to std_um for item_um uom type */
299 /* if required */
300
301 IF (l_from_um <> l_from_std_um)
302 THEN
303
304 l_factor := GMICUOM.uom_conversion(pitem_id => p_ic_item_mst_row.item_id
305 ,plot_id => p_ic_lots_mst_row.lot_id
306 ,pcur_qty => l_factor
307 ,pcur_uom => l_from_um
308 ,pnew_uom => l_from_std_um
309 ,patomic => 0
310 );
311 IF (l_factor < 0)
312 THEN
313 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_LOT_UOM_FAILED');
314 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
315 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
316 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
317 FND_MESSAGE.SET_TOKEN('UM1', l_from_um);
318 FND_MESSAGE.SET_TOKEN('UM2', l_from_std_um);
319 FND_MSG_PUB.Add;
320 RAISE FND_API.G_EXC_ERROR;
321 END IF;
322
323 l_type_factorrev := 1 / l_factor;
324
325 END IF;
326
327 /* Next convert from to_uom to std_um for to_uom uom type */
328 /* if required */
329
330 IF (l_rec_to_um <> l_to_std_um)
331 THEN
332
333 l_factor := GMICUOM.uom_conversion(pitem_id => p_ic_item_mst_row.item_id
334 ,plot_id => p_ic_lots_mst_row.lot_id
338 ,patomic => 0
335 ,pcur_qty => l_factor
336 ,pcur_uom => l_to_std_um
337 ,pnew_uom => l_to_um
339 );
340 IF (l_factor < 0)
341 THEN
342 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_LOT_UOM_FAILED');
343 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
344 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
345 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
346 FND_MESSAGE.SET_TOKEN('UM1', l_to_um);
347 FND_MESSAGE.SET_TOKEN('UM2', l_to_std_um);
348 FND_MSG_PUB.Add;
349 RAISE FND_API.G_EXC_ERROR;
350 END IF;
351
352 l_type_factorrev := 1 / l_factor;
353
354 END IF;
355
356 /* Bug 2446245 - Commented and moved this line up in the code. */
357 /* This line of code was causing the decimal precision issue by */
358 /* unnecessarily recalculating the factor.
359 /* l_type_factorrev := 1 / l_factor; */
360
361
362 /* Bug 2880585 - Check for transaction activity before */
363 /* creating a new conversion. */
364
365 IF (p_ic_lots_mst_row.lot_id > 0) THEN
366 OPEN Cur_get_lotid;
367 FETCH Cur_get_lotid INTO X_lot_id;
368 CLOSE Cur_get_lotid;
369 OPEN Cur_trans_cmp;
370 FETCH Cur_trans_cmp INTO X_count;
371 CLOSE Cur_trans_cmp;
372 IF (X_count = 0) THEN
373 OPEN Cur_trans_pnd;
374 FETCH Cur_trans_pnd INTO X_count;
375 CLOSE Cur_trans_pnd;
376 IF (X_count = 0) THEN
377 OPEN Cur_journal;
378 FETCH Cur_journal INTO X_count;
379 CLOSE Cur_journal;
380 END IF;
381 END IF;
382 IF (X_count > 0) THEN
383 FND_MESSAGE.SET_NAME('GMI','GMI_LOTCONV_TRANSACTIONS_EXIST');
384 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
385 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
386 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
387 FND_MESSAGE.SET_TOKEN('UM1', l_to_um);
388 FND_MESSAGE.SET_TOKEN('UM2', l_to_std_um);
389 FND_MSG_PUB.Add;
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392 END IF;
393
394
395 /* Set up PL/SQL record for insertion into IC_ITEM_CNV */
396
397 x_ic_item_cnv_row.item_id := p_ic_item_mst_row.item_id;
398 x_ic_item_cnv_row.lot_id := p_ic_lots_mst_row.lot_id;
399 x_ic_item_cnv_row.um_type := l_um_type;
400 x_ic_item_cnv_row.type_factor := l_factor;
401 x_ic_item_cnv_row.last_update_date := SYSDATE;
402 x_ic_item_cnv_row.last_updated_by := GMIGUTL.DEFAULT_USER_ID;
403 x_ic_item_cnv_row.trans_cnt := 1;
404 x_ic_item_cnv_row.delete_mark := 0;
405 x_ic_item_cnv_row.text_code := NULL;
406 x_ic_item_cnv_row.creation_date := SYSDATE;
407 x_ic_item_cnv_row.created_by := GMIGUTL.DEFAULT_USER_ID;
408 x_ic_item_cnv_row.type_factorrev := l_type_factorrev;
409 x_ic_item_cnv_row.last_update_login := GMIGUTL.DEFAULT_LOGIN;
410
411
412 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
413 , p_data => x_msg_data
414 );
415
416 EXCEPTION
417
418 WHEN NO_DATA_FOUND
419 THEN
420 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_UOM');
421 FND_MESSAGE.SET_TOKEN('UOM', p_item_cnv_rec.from_uom);
422 FND_MESSAGE.SET_TOKEN('ITEM_NO', p_item_cnv_rec.item_no);
423 FND_MESSAGE.SET_TOKEN('LOT_NO', p_item_cnv_rec.lot_no);
424 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', p_item_cnv_rec.sublot_no);
425 FND_MSG_PUB.Add;
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
428 , p_data => x_msg_data
429 );
430
431 WHEN FND_API.G_EXC_ERROR THEN
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
434 , p_data => x_msg_data
435 );
436
437 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
440 , p_data => x_msg_data
441 );
442 WHEN OTHERS THEN
443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
444 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
445 , l_api_name
446 );
447 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
448 , p_data => x_msg_data
449 );
450 END Validate_Lot_Conversion;
451
452 END GMIVILC;