[Home] [Help]
PACKAGE BODY: APPS.GMI_LOTS_PUB
Source
1 PACKAGE BODY GMI_LOTS_PUB AS
2 --$Header: GMIPLOTB.pls 115.11 2002/10/30 20:25:20 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 --| GMIPLOTB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMI_LOTS_PUB |
14 --| |
15 --| DESCRIPTION |
16 --| This package contains all APIs related to the Business Object Lot/ |
17 --| Sub-Lot |
18 --| |
19 --| CONTENTS |
20 --| Create_Lot |
21 --| Validate_Lot |
22 --| |
23 --| HISTORY |
24 --| 17-FEB-1999 M.Godfrey Upgrade to R11 |
25 --| 20/AUG/1999 H.Verdding Bug 951828 Change GMS package Calls to GMA |
26 --| 02/JAN/2000 Liz Enstone Bug1159923 Change message name from SY_ to IC|
27 --| 21/DEC/2001 K. RajaSekhar Reddy BUG#2158123 |
28 --| Modified the code to set the dates correctly in |
29 --| GMI_LOTS_PUB.Create procedure |
30 --| 29-OCT-2002 J.DiIorio Bug#2643440 11.5.1J - added nocopy. |
31 --| Removed fnd_miss from date comparisons. |
32 --+==========================================================================+
33 -- Body end of comments
34
35 -- Global variables
36 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_LOTS_PUB';
37 IC$DEFAULT_LOT VARCHAR2(255);
38
39 -- Api start of comments
40 --+==========================================================================+
41 --| PROCEDURE NAME |
42 --| Create_Lot |
43 --| |
44 --| TYPE |
45 --| Public |
46 --| |
47 --| USAGE |
48 --| Create a new Inventory Lot |
49 --| |
50 --| DESCRIPTION |
51 --| This procedure creates a new inventory Lot |
52 --| |
53 --| PARAMETERS |
54 --| p_api_version IN NUMBER - Api Version |
55 --| p_init_msg_list IN VARCHAR2 - Message Initialization Ind. |
56 --| p_commit IN VARCHAR2 - Commit Indicator |
57 --| p_validation_level IN VARCHAR2 - Validation Level Indicator |
58 --| p_lot_rec IN lot_rec_typ - Lot Master details |
59 --| x_return_status OUT VARCHAR2 - Return Status |
60 --| x_msg_count OUT NUMBER - Number of messages |
61 --| x_msg_data OUT VARCHAR2 - Messages in encoded format |
62 --| |
63 --| RETURNS |
64 --| None |
65 --| |
66 --| HISTORY |
67 --| H.Verdding Changed User Name Validation To Use p_lot_rec |
68 --| Instead Of l_lot_rec |
69 --| 17-AUG-99 H.Verdding B959447 |
70 --| Part 1 - Moved Uppercase column Conversion |
71 --| To start of file |
72 --| Part 3/4 - Changed Get Shipvend_id logic |
73 --| 21-DEC-01 K. RajaSekhar Reddy BUG#2158123 |
74 --| Modified the code to create the Retest Date, |
75 --| Expire Date and Expaction Dates correctly. |
76 --+==========================================================================+
77 PROCEDURE Create_Lot
78 ( p_api_version IN NUMBER
82 , p_lot_rec IN lot_rec_typ
79 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
80 , p_commit IN VARCHAR2 := FND_API.G_FALSE
81 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
83 , x_return_status OUT NOCOPY VARCHAR2
84 , x_msg_count OUT NOCOPY NUMBER
85 , x_msg_data OUT NOCOPY VARCHAR2
86 )
87 IS
88 l_api_name CONSTANT VARCHAR2 (30) := 'Create_Lot';
89 l_api_version CONSTANT NUMBER := 2.0;
90 l_lot_id ic_lots_mst.lot_id%TYPE;
91 l_shipvend_id ic_lots_mst.shipvend_id%TYPE;
92 l_msg_count NUMBER;
93 l_msg_data VARCHAR2(2000);
94 l_return_status VARCHAR2(1);
95 l_user_name fnd_user.user_name%TYPE;
96 l_user_id fnd_user.user_id%TYPE;
97 l_ic_item_mst_rec ic_item_mst%ROWTYPE;
98 l_ic_lots_mst_rec ic_lots_mst%ROWTYPE;
99 l_ic_lots_cpg_rec ic_lots_cpg%ROWTYPE;
100 l_lot_rec lot_rec_typ;
101 l_po_vend_mst_rec po_vend_mst%ROWTYPE;
102 l_ic_item_cpg_rec ic_item_cpg%ROWTYPE;
103
104 BEGIN
105
106 -- Standard Start OF API savepoint
107 SAVEPOINT Create_Lot;
108 -- Standard call to check for call compatibility.
109 IF NOT FND_API.Compatible_API_CALL ( l_api_version
110 , p_api_version
111 , l_api_name
112 , G_PKG_NAME
113 )
114 THEN
115 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117 -- Initialize message list if p_int_msg_list is set TRUE.
118 IF FND_API.to_boolean(p_init_msg_list)
119 THEN
120 FND_MSG_PUB.Initialize;
121 END IF;
122
123 -- Initialize API return status to sucess
124 x_return_status :=FND_API.G_RET_STS_SUCCESS;
125
126 -- Ensure Upper-case columns are converted
127 -- H.Verdding B959447 - Part1
128 l_lot_rec := p_lot_rec;
129 l_lot_rec.item_no := UPPER(l_lot_rec.item_no);
130 l_lot_rec.lot_no := UPPER(l_lot_rec.lot_no);
131 l_lot_rec.sublot_no := UPPER(l_lot_rec.sublot_no);
132 l_lot_rec.qc_grade := UPPER(l_lot_rec.qc_grade);
133 l_lot_rec.expaction_code := UPPER(l_lot_rec.expaction_code);
134 l_lot_rec.user_name := UPPER(l_lot_rec.user_name);
135
136 -- Populate WHO columns
137 GMA_GLOBAL_GRP.Get_who( p_user_name => p_lot_rec.user_name
138 , x_user_id => l_user_id
139 );
140
141 IF l_user_id = 0
142 THEN
143 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_USER_NAME');
144 FND_MESSAGE.SET_TOKEN('USER_NAME',l_user_name);
145 FND_MSG_PUB.Add;
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149 -- Get required system constants
150
151 IC$DEFAULT_LOT := FND_PROFILE.Value_Specific( name => 'IC$DEFAULT_LOT'
152 , user_id => l_user_id
153 );
154 IF (IC$DEFAULT_LOT IS NULL)
155 THEN
156 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_CONSTANT');
157 FND_MESSAGE.SET_TOKEN('CONSTANT_NAME','IC$DEFAULT_LOT');
158 FND_MSG_PUB.Add;
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162
163 -- Get item details
164
165 GMI_GLOBAL_GRP.Get_Item ( p_item_no => l_lot_rec.item_no
166 , x_ic_item_mst => l_ic_item_mst_rec
167 , x_ic_item_cpg => l_ic_item_cpg_rec
168 );
169
170 IF (l_ic_item_mst_rec.item_id < 0)
171 THEN
172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
173 ELSIF (l_ic_item_mst_rec.item_id = 0) OR
174 (l_ic_item_mst_rec.delete_mark = 1)
175 THEN
176 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
177 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_lot_rec.item_no);
178 FND_MSG_PUB.Add;
179 RAISE FND_API.G_EXC_ERROR;
180 ELSIF (l_ic_item_mst_rec.noninv_ind = 1) AND
181 (p_lot_rec.lot_no <> 'NEWITEM')
182 THEN
183 FND_MESSAGE.SET_NAME('GMI','IC_API_NONINV_ITEM_NO');
184 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_lot_rec.item_no);
185 FND_MSG_PUB.Add;
186 RAISE FND_API.G_EXC_ERROR;
187 ELSIF (l_ic_item_mst_rec.inactive_ind = 1) AND
188 (p_lot_rec.lot_no <> 'NEWITEM')
189 THEN
190 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_ITEM_NO');
191 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_lot_rec.item_no);
192 FND_MSG_PUB.Add;
193 RAISE FND_API.G_EXC_ERROR;
194 END IF;
195
196 -- Set up defaults for hold and maturity days
197 IF l_ic_item_cpg_rec.ic_hold_days IS NULL
198 THEN
199 l_ic_item_cpg_rec.ic_hold_days :=0;
200 END IF;
201
202 IF l_ic_item_cpg_rec.ic_matr_days IS NULL
203 THEN
204 l_ic_item_cpg_rec.ic_matr_days :=0;
205 END IF;
206
207
208 -- If creating default lot (i.e. being call from create_item API) then
209 -- bypass defaults and validation.
210
211 IF (p_lot_rec.lot_no = 'NEWITEM')
212 THEN
213 l_lot_rec.lot_no := IC$DEFAULT_LOT;
214 l_lot_id := 0;
215 ELSE
216 -- Set up default values for required where fields have been left blank
217 -- QC Grade
218 IF (l_lot_rec.qc_grade = ' ' OR
219 l_lot_rec.qc_grade IS NULL) AND
220 (l_ic_item_mst_rec.grade_ctl = 1)
221 THEN
222 l_lot_rec.qc_grade :=l_ic_item_mst_rec.qc_grade;
223 END IF;
224
225 --Expaction Code
226 IF (l_lot_rec.expaction_code = ' ' OR l_lot_rec.expaction_code IS NULL)
227 THEN
228 l_lot_rec.expaction_code := l_ic_item_mst_rec.expaction_code;
232 IF (l_lot_rec.expire_date IS NULL)
229 END IF;
230
231 -- Expire Date
233 THEN
234 --BEGIN BUG#2158123 12/21/2001 RajaSekhar
235 IF (l_ic_item_mst_rec.grade_ctl = 1)
236 THEN
237 l_lot_rec.expire_date := l_lot_rec.lot_created +
238 NVL(l_ic_item_mst_rec.shelf_life,0);
239 --END BUG#2158123
240 ELSE
241 l_lot_rec.expire_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
242 END IF;
243 END IF;
244
245 --Expaction Date
246 IF (l_lot_rec.expaction_date IS NULL) OR (l_lot_rec.expaction_code IS NULL)
247 THEN
248 --BEGIN BUG#2158123 12/21/2001 RajaSekhar
249 IF (l_ic_item_mst_rec.grade_ctl = 1)
250 THEN
251 l_lot_rec.expaction_date := l_lot_rec.expire_date +
252 NVL(l_ic_item_mst_rec.expaction_interval,0);
253 --END BUG#2158123
254 ELSE
255 l_lot_rec.expaction_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
256 END IF;
257 END IF;
258
259 --Retest Date
260 IF (l_lot_rec.retest_date IS NULL)
261 THEN
262 --BEGIN BUG#2158123 12/21/2001 RajaSekhar
263 IF (l_ic_item_mst_rec.grade_ctl = 1)
264 THEN
265 l_lot_rec.retest_date := l_lot_rec.lot_created +
266 NVL(l_ic_item_mst_rec.retest_interval,0);
267 --END BUG#2158123
268 ELSE
269 l_lot_rec.retest_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
270 END IF;
271 END IF;
272
273 -- Ic_Matr_Date
274 IF (l_lot_rec.ic_matr_date IS NULL)
275 THEN
276 l_lot_rec.ic_matr_date := l_lot_rec.lot_created +
277 l_ic_item_cpg_rec.ic_matr_days;
278 END IF;
279
280 -- Ic_Hold_Date
281 IF (l_lot_rec.ic_hold_date IS NULL)
282 THEN
283 l_lot_rec.ic_hold_date := l_lot_rec.lot_created +
284 l_ic_item_cpg_rec.ic_hold_days;
285 END IF;
286
287 -- Perform Validation
288
289 GMI_LOTS_PUB.Validate_Lot ( p_api_version => 2.0
290 , p_init_msg_list => FND_API.G_FALSE
291 , p_validation_level =>FND_API.G_VALID_LEVEL_FULL
292 , p_lot_rec => l_lot_rec
293 , p_item_rec => l_ic_item_mst_rec
294 , x_return_status => l_return_status
295 , x_msg_count => l_msg_count
296 , x_msg_data => l_msg_data
297 );
298
299 -- If errors were found then raise exception
300 x_return_status := l_return_status;
301 IF (l_return_status = FND_API.G_RET_STS_ERROR)
302 THEN
303 RAISE FND_API.G_EXC_ERROR;
304 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
305 THEN
306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
307 END IF;
308
309 -- If no errors were found then proceed with the lot create
310
311 -- First get the surrogate key (lot_id) for the lot
312 SELECT gem5_lot_id_s.nextval INTO l_lot_id FROM dual;
313 IF (l_lot_id <= 0)
314 THEN
315 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
316 FND_MESSAGE.SET_TOKEN('SKEY','lot_id');
317 FND_MSG_PUB.Add;
318 RAISE FND_API.G_EXC_ERROR;
319 END IF;
320
321 -- End of default lot condition
322 END IF;
323
324 -- Get shipvend_id
325 -- H.Verdding B959447- Changed Logic for getting shipvend_id
326 IF (p_lot_rec.shipvendor_no <> ' ' OR p_lot_rec.shipvendor_no IS NOT NULL)
327 THEN
328 l_shipvend_id :=GMI_VALID_GRP.Validate_shipvendor_no(p_lot_rec.shipvendor_no);
329 ELSE
330 l_shipvend_id :=NULL;
331 END IF;
332
333 -- Set up PL/SQL record and insert lot into ic_lots_mst
334
335 l_ic_lots_mst_rec.item_id := l_ic_item_mst_rec.item_id;
336 l_ic_lots_mst_rec.lot_no := l_lot_rec.lot_no;
337 l_ic_lots_mst_rec.sublot_no := l_lot_rec.sublot_no;
338 l_ic_lots_mst_rec.lot_id := l_lot_id;
339 l_ic_lots_mst_rec.lot_desc := l_lot_rec.lot_desc;
340 l_ic_lots_mst_rec.qc_grade := l_lot_rec.qc_grade;
341 l_ic_lots_mst_rec.expaction_code := l_lot_rec.expaction_code;
342 l_ic_lots_mst_rec.expaction_date := l_lot_rec.expaction_date;
343 l_ic_lots_mst_rec.lot_created := l_lot_rec.lot_created;
344 l_ic_lots_mst_rec.expire_date := l_lot_rec.expire_date;
345 l_ic_lots_mst_rec.retest_date := l_lot_rec.retest_date;
346 l_ic_lots_mst_rec.strength := l_lot_rec.strength;
347 l_ic_lots_mst_rec.inactive_ind := l_lot_rec.inactive_ind;
348 l_ic_lots_mst_rec.origination_type := l_lot_rec.origination_type;
349 l_ic_lots_mst_rec.vendor_lot_no := l_lot_rec.vendor_lot_no;
350 l_ic_lots_mst_rec.shipvend_id := l_shipvend_id;
351 l_ic_lots_mst_rec.creation_date := SYSDATE;
352 l_ic_lots_mst_rec.last_update_date := SYSDATE;
353 l_ic_lots_mst_rec.created_by := l_user_id;
354 l_ic_lots_mst_rec.last_updated_by := l_user_id;
355 l_ic_lots_mst_rec.last_update_login :=TO_NUMBER(FND_PROFILE.Value(
356 'LOGIN_ID'));
357 l_ic_lots_mst_rec.trans_cnt := 1;
358 l_ic_lots_mst_rec.delete_mark := 0;
359 l_ic_lots_mst_rec.text_code := NULL;
360 l_ic_lots_mst_rec.attribute1 := UPPER(l_lot_rec.attribute1);
361 l_ic_lots_mst_rec.attribute2 := UPPER(l_lot_rec.attribute2);
362 l_ic_lots_mst_rec.attribute3 := UPPER(l_lot_rec.attribute3);
363 l_ic_lots_mst_rec.attribute4 := UPPER(l_lot_rec.attribute4);
367 l_ic_lots_mst_rec.attribute8 := UPPER(l_lot_rec.attribute8);
364 l_ic_lots_mst_rec.attribute5 := UPPER(l_lot_rec.attribute5);
365 l_ic_lots_mst_rec.attribute6 := UPPER(l_lot_rec.attribute6);
366 l_ic_lots_mst_rec.attribute7 := UPPER(l_lot_rec.attribute7);
368 l_ic_lots_mst_rec.attribute9 := UPPER(l_lot_rec.attribute9);
369 l_ic_lots_mst_rec.attribute10 := UPPER(l_lot_rec.attribute10);
370 l_ic_lots_mst_rec.attribute11 := UPPER(l_lot_rec.attribute11);
371 l_ic_lots_mst_rec.attribute12 := UPPER(l_lot_rec.attribute12);
372 l_ic_lots_mst_rec.attribute13 := UPPER(l_lot_rec.attribute13);
373 l_ic_lots_mst_rec.attribute14 := UPPER(l_lot_rec.attribute14);
374 l_ic_lots_mst_rec.attribute15 := UPPER(l_lot_rec.attribute15);
375 l_ic_lots_mst_rec.attribute16 := UPPER(l_lot_rec.attribute16);
376 l_ic_lots_mst_rec.attribute17 := UPPER(l_lot_rec.attribute17);
377 l_ic_lots_mst_rec.attribute18 := UPPER(l_lot_rec.attribute18);
378 l_ic_lots_mst_rec.attribute19 := UPPER(l_lot_rec.attribute19);
379 l_ic_lots_mst_rec.attribute20 := UPPER(l_lot_rec.attribute20);
380 l_ic_lots_mst_rec.attribute21 := UPPER(l_lot_rec.attribute21);
381 l_ic_lots_mst_rec.attribute22 := UPPER(l_lot_rec.attribute22);
382 l_ic_lots_mst_rec.attribute23 := UPPER(l_lot_rec.attribute23);
383 l_ic_lots_mst_rec.attribute24 := UPPER(l_lot_rec.attribute24);
384 l_ic_lots_mst_rec.attribute25 := UPPER(l_lot_rec.attribute25);
385 l_ic_lots_mst_rec.attribute26 := UPPER(l_lot_rec.attribute26);
386 l_ic_lots_mst_rec.attribute27 := UPPER(l_lot_rec.attribute27);
387 l_ic_lots_mst_rec.attribute28 := UPPER(l_lot_rec.attribute28);
388 l_ic_lots_mst_rec.attribute29 := UPPER(l_lot_rec.attribute29);
389 l_ic_lots_mst_rec.attribute30 := UPPER(l_lot_rec.attribute30);
390 l_ic_lots_mst_rec.attribute_category := UPPER(l_lot_rec.attribute_category);
391
392
393 IF NOT GMI_LOTS_PVT.insert_ic_lots_mst(l_ic_lots_mst_rec)
394 THEN
395 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
396 END IF;
397
398 -- Set up PL/SQL record and insert lot into ic_lots_cpg
399
400 l_ic_lots_cpg_rec.item_id := l_ic_item_mst_rec.item_id;
401 l_ic_lots_cpg_rec.lot_id := l_lot_id;
402 l_ic_lots_cpg_rec.ic_matr_date := l_lot_rec.ic_matr_date;
403 l_ic_lots_cpg_rec.ic_hold_date := l_lot_rec.ic_hold_date;
404 l_ic_lots_cpg_rec.created_by := l_user_id;
405 l_ic_lots_cpg_rec.creation_date := SYSDATE;
406 l_ic_lots_cpg_rec.last_update_date := SYSDATE;
407 l_ic_lots_cpg_rec.last_updated_by := l_user_id;
408 l_ic_lots_cpg_rec.last_update_login :=TO_NUMBER(FND_PROFILE.Value(
409 'LOGIN_ID'));
410
411 IF NOT GMI_LOTS_PVT.insert_ic_lots_cpg(l_ic_lots_cpg_rec)
412 THEN
413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414 END IF;
415
416 -- END of API Body
417
418 -- Standard Check of p_commit.
419 IF FND_API.to_boolean(p_commit)
420 THEN
421 COMMIT WORK;
422 END IF;
423
424 -- Success message
425 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_CREATED');
426 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_lot_rec.item_no);
427 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_rec.lot_no);
428 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_lot_rec.sublot_no);
429 FND_MSG_PUB.Add;
430 -- Standard Call to get message count and if count is 1,
431 -- get message info.
432
433 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
434 , p_data => x_msg_data
435 );
436
437 EXCEPTION
438 WHEN FND_API.G_EXC_ERROR THEN
439 ROLLBACK TO Create_Lot;
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
442 , p_data => x_msg_data
443 );
444
445 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
446 ROLLBACK TO Create_Lot;
447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
448 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
449 , p_data => x_msg_data
450 );
451
452 WHEN OTHERS THEN
453 ROLLBACK TO Create_Lot;
454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
455 -- IF FND_MSG_PUB.check_msg_level
456 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
457 -- THEN
458
459 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
460 , l_api_name
461 );
462 -- END IF;
463 FND_MSG_PUB.Count_AND_GET ( p_count => x_msg_count
464 , p_data => x_msg_data
465 );
466 END Create_Lot;
467
468 -- Api start of comments
469 --+==========================================================================+
470 --| PROCEDURE NAME |
471 --| Validate_Lot |
472 --| |
473 --| TYPE |
474 --| Public |
475 --| |
476 --| USAGE |
477 --| Performs all validation functions associated with creation of a new |
478 --| inventory lot |
479 --| |
480 --| DESCRIPTION |
484 --| PARAMETERS |
481 --| This procedure validates all data associated with creation of a new |
482 --| inventory lot |
483 --| |
485 --| p_api_version IN NUMBER - Api Version |
486 --| p_init_msg_list IN VARCHAR2 - Message Initialization Ind. |
487 --| p_commit IN VARCHAR2 - Commit Indicator |
488 --| p_validation_level IN VARCHAR2 - Validation Level Indicator |
489 --| p_lot_rec IN lot_rec_typ - Lot Master details |
490 --| p_item_rec IN item_rec_typ - Item Master details |
491 --| x_return_status OUT VARCHAR2 - Return Status |
492 --| x_msg_count OUT NUMBER - Number of messages |
493 --| x_msg_data OUT VARCHAR2 - Messages in encoded format |
494 --| |
495 --| RETURNS |
496 --| None |
497 --| |
498 --| HISTORY |
499 --| |
500 --+==========================================================================+
501 PROCEDURE Validate_Lot
502 ( p_api_version IN NUMBER
503 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
504 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
505 , p_lot_rec IN lot_rec_typ
506 , p_item_rec IN ic_item_mst%ROWTYPE
507 , x_return_status OUT NOCOPY VARCHAR2
508 , x_msg_count OUT NOCOPY NUMBER
509 , x_msg_data OUT NOCOPY VARCHAR2
510 )
511 IS
512 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Lot';
513 l_api_version CONSTANT NUMBER := 2.0;
514 l_msg_count NUMBER;
515 l_msg_data VARCHAR2(2000);
516 l_return_status VARCHAR2(1);
517 l_item_no ic_item_mst.item_no%TYPE;
518 l_lot_no ic_lots_mst.lot_no%TYPE;
519 l_sublot_no ic_lots_mst.sublot_no%TYPE;
520 l_qc_grade ic_lots_mst.qc_grade%TYPE;
521 l_expaction_code ic_lots_mst.expaction_code%TYPE;
522 l_user_name fnd_user.user_name%TYPE;
523
524 BEGIN
525
526 -- Standard call to check for call compatibility.
527 IF NOT FND_API.Compatible_API_CALL ( l_api_version
528 , p_api_version
529 , l_api_name
530 , G_PKG_NAME
531 )
532 THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535
536 -- Initialize message list if p_int_msg_list is set TRUE.
537 IF FND_API.to_boolean(p_init_msg_list)
538 THEN
539 FND_MSG_PUB.Initialize;
540 END IF;
541 -- Initialize API return status to sucess
542 x_return_status := FND_API.G_RET_STS_SUCCESS;
543 --
544
545 -- Ensure Upper-case columns are converted
546
547 l_item_no := UPPER(p_lot_rec.item_no);
548 l_lot_no := UPPER(p_lot_rec.lot_no);
549 l_sublot_no := UPPER(p_lot_rec.sublot_no);
550 l_qc_grade := UPPER(p_lot_rec.qc_grade);
551 l_expaction_code := UPPER(p_lot_rec.expaction_code);
552 l_user_name := UPPER(p_lot_rec.user_name);
553
554 --Check to see if item is lot Controlled
555 IF (p_item_rec.lot_ctl <> 1)
556 THEN
557 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_NOT_LOT_CTL');
558 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
559 FND_MSG_PUB.Add;
560 RAISE FND_API.G_EXC_ERROR;
561 END IF;
562
563 -- Check that lot number has been supplied and is not same value
564 -- as IC$DEFAULT_LOT
565 IF (l_lot_no = ' ' OR l_lot_no = IC$DEFAULT_LOT OR l_lot_no IS NULL)
566 THEN
567 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_NO');
568 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
569 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
570 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
571 FND_MSG_PUB.Add;
572 RAISE FND_API.G_EXC_ERROR;
573 END IF;
574
575 --Check to see if item is Sublot Controlled
576
577 IF (p_item_rec.sublot_ctl <> 1 AND
578 l_sublot_no <> ' ' AND
579 l_sublot_no IS NOT NULL)
580 THEN
581 FND_MESSAGE.SET_NAME('GMI','IC_API_SUBLOT_NOT_REQD');
582 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
583 FND_MSG_PUB.Add;
584 RAISE FND_API.G_EXC_ERROR;
585 END IF;
586
587 --Check that lot number and sublot number do not exist for item number
588 IF GMI_VALID_GRP.Validate_lot_no ( l_item_no
589 , l_lot_no
590 , l_sublot_no
591 )
592 THEN
593 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_ALREADY_EXISTS');
594 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
595 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
596 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
597 FND_MSG_PUB.Add;
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600
601 --Check to see if item is Grade Controlled
602 IF (p_item_rec.grade_ctl <> 1 AND
603 p_lot_rec.qc_grade <> ' ' AND
604 p_lot_rec.qc_grade IS NOT NULL)
605 THEN
606 FND_MESSAGE.SET_NAME('GMI','IC_API_LOT_QC_GRADE_NOT_REQD');
607 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
611 RAISE FND_API.G_EXC_ERROR;
608 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
609 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
610 FND_MSG_PUB.Add;
612 END IF;
613
614 --Validate QC Grade
615 IF NOT GMI_VALID_GRP.Validate_qc_grade ( p_lot_rec.qc_grade
616 , p_item_rec.grade_ctl
617 )
618 THEN
619 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_QC_GRADE');
620 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
621 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
622 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
623 FND_MSG_PUB.Add;
624 RAISE FND_API.G_EXC_ERROR;
625 END IF;
626
627 --Validate Expaction Code
628 IF (NOT GMI_VALID_GRP.Validate_expaction_code( p_lot_rec.expaction_code
629 , p_item_rec.grade_ctl
630 ) AND
631 p_lot_rec.expaction_code <> ' ')
632 THEN
633 FND_MESSAGE.SET_NAME('GMI','IC_API_INV_LOT_EXPACTION_CODE');
634 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
635 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
636 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
637 FND_MSG_PUB.Add;
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 --Validate Expire Date
642 IF (TRUNC(p_lot_rec.expire_date, 'DD') < TRUNC(p_lot_rec.lot_created, 'DD'))
643 THEN
644 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPIRE_DATE');
645 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
646 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
647 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
648 FND_MSG_PUB.Add;
649 RAISE FND_API.G_EXC_ERROR;
650 END IF;
651
652 --Validate Retest Date
653 IF (TRUNC(p_lot_rec.retest_date, 'DD') < TRUNC(p_lot_rec.lot_created, 'DD'))
654 THEN
655 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_RETEST_DATE');
656 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
657 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
658 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
659 FND_MSG_PUB.Add;
660 RAISE FND_API.G_EXC_ERROR;
661 END IF;
662
663 --Validate Expaction Date
664 IF (TRUNC(p_lot_rec.expaction_date,'DD') < TRUNC(p_lot_rec.lot_created,'DD'))
665 THEN
666 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_EXPACTION_DATE');
667 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
668 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
669 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
670 FND_MSG_PUB.Add;
671 RAISE FND_API.G_EXC_ERROR;
672 END IF;
673
674 --Validate Strength
675 IF NOT GMI_VALID_GRP.Validate_strength(p_lot_rec.strength)
676 THEN
677 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_STRENGTH');
678 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
679 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
680 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
681 FND_MSG_PUB.Add;
682 RAISE FND_API.G_EXC_ERROR;
683 END IF;
684
685 --Validate Inactive Indicator
686 IF NOT GMI_VALID_GRP.Validate_inactive_ind(p_lot_rec.inactive_ind)
687 THEN
688 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_INACTIVE');
689 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
690 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
691 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
692 FND_MSG_PUB.Add;
693 RAISE FND_API.G_EXC_ERROR;
694 END IF;
695
696 --Validate Origination Type
697 --HAM IF NOT GMI_VALID_GRP.Validate_origination_type(p_lot_rec.origination_type)
698 IF GMI_VALID_GRP.Validate_origination_type(p_lot_rec.origination_type)
699 THEN
700 NULL;
701 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_ORIG_TYPE');
702 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
703 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
704 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
705 FND_MSG_PUB.Add;
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708
709 --Validate Shipvendor Number
710 IF (p_lot_rec.shipvendor_no = ' ' OR p_lot_rec.shipvendor_no IS NULL)
711 THEN
712 NULL;
713 ELSIF
714 GMI_VALID_GRP.Validate_shipvendor_no(p_lot_rec.shipvendor_no) = 0 THEN
715 FND_MESSAGE.SET_NAME('GMI','IC_API_INV_LOT_SHIPVENDOR_NO');
716 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
717 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
718 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
719 FND_MSG_PUB.Add;
720 RAISE FND_API.G_EXC_ERROR;
721 END IF;
722
723 --Validate Maturity date (CPG)
724 IF (p_lot_rec.ic_matr_date < p_lot_rec.lot_created)
725 THEN
726 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_MATR_DATE');
727 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
728 FND_MSG_PUB.Add;
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731
732 --Validate Hold release date (CPG)
733 IF (p_lot_rec.ic_hold_date < p_lot_rec.lot_created)
734 THEN
735 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_HOLD_DATE');
736 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
737 FND_MSG_PUB.Add;
738 RAISE FND_API.G_EXC_ERROR;
739 END IF;
740
741 EXCEPTION
742
743 WHEN FND_API.G_EXC_ERROR THEN
744 x_return_status := FND_API.G_RET_STS_ERROR;
745 FND_MSG_PUB.Count_AND_GET
746 ( p_count => x_msg_count,
747 p_data => x_msg_data
748 );
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
751 FND_MSG_PUB.Count_AND_GET
752 ( p_count => x_msg_count,
753 p_data => x_msg_data
754 );
755 WHEN OTHERS THEN
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 FND_MSG_PUB.Add_Exc_Msg
758 ( G_PKG_NAME ,
759 l_api_name
760 );
761 FND_MSG_PUB.Count_AND_GET
762 ( p_count => x_msg_count,
763 p_data => x_msg_data
764 );
765 END Validate_Lot;
766
767 END GMI_LOTS_PUB;