1 PACKAGE BODY GMIVLOT AS
2 /* $Header: GMIVLOTB.pls 115.29 2004/07/07 14:56:15 jgogna ship $ */
3 /* Global variables */
4 G_PKG_NAME CONSTANT VARCHAR2(30):='GMIVLOT';
5
6
7 /* +==========================================================================+
8 | PROCEDURE NAME |
9 | Validate_Lot |
10 | |
11 | TYPE |
12 | Private |
13 | |
14 | USAGE |
15 | Performs all validation functions associated with creation of a new |
16 | inventory lot |
17 | |
18 | DESCRIPTION |
19 | This procedure validates all data associated with creation of a new |
20 | inventory lot |
21 | |
22 | PARAMETERS |
23 | p_api_version IN NUMBER - Api Version |
24 | p_validation_level IN VARCHAR2 - Validation Level Indicator |
25 | p_lot_rec IN GMIGAPI.lot_rec_typ - Lot Master details |
26 | p_ic_item_mst_row IN ic_item_mst%ROWTYPE |
27 | p_ic_item_cpg_row IN ic_item_cpg%ROWTYPE |
28 | x_ic_lots_mst_row OUT NOCOPY ic_lots_mst%ROWTYPE |
29 | x_ic_lots_cpg_row OUT NOCOPY ic_lots_cpg%ROWTYPE |
30 | x_return_status OUT NOCOPY VARCHAR2 - Return Status |
31 | x_msg_count OUT NOCOPY NUMBER - Number of messages |
32 | x_msg_data OUT NOCOPY VARCHAR2 - Messages in encoded format |
33 | |
34 | RETURNS |
35 | Via 'out' parameters |
36 | |
37 | HISTORY |
38 | 14-May-2000 P.J.Schofield B1294915 |
39 | Major reworking for performance reasons. |
40 | |
41 | |
42 | 03-May-2001 A. Mundhe Bug 1762786 - Assign vendor_lot_no so that it |
43 | is updated in ic_lots_mst. |
44 | |
45 | 25-Oct-2001 A. Mundhe Bug 1886611 - Assign vendor_lot_no without |
46 | validating shipvendor_id. |
47 | 21-DEC-2001 K. RajaSekhar Reddy BUG#2158123 |
48 | Modified the code to create the Retest Date, |
49 | Expire Date and Expaction Dates correctly. |
50 | Jalaj Srivastava 08/20/02 Bug 2520129 |
51 | The lot rec typ record is changed in GMIGAPI to not default lot |
52 | created date to sysdate to make it referencable from forms. Forms |
53 | has a limitation of not acceptinf default values of sysdate for |
54 | record objects. We will default the lot created date here |
55 | from now on. |
56 | Assigned proper value to x_ic_lots_mst_row.lot_created in the |
57 | beginning and used that instead of p_lot_rec.lot_created to check |
58 | the validation for other dates. |
59 | 16-Sep-2002 A. Cataldo Bug 2458413 - Properly initialize expaction |
60 | date for non grd cntl items (lot crt API) and setup deflt|
61 | dates for default trans in lots_mst (item create API). |
62 | 04-Nov-2002 A.Cataldo Bug # 2343411 |
63 | Based on a new profile option, allow the user to control |
64 | what the lot description will be defaulted to when |
65 | creating lots either via screen or APIs. |
66 | 22-FEB-2003 Jalaj Srivastava Bug 2811747 |
67 | error status wold be set as soon as error condition is |
68 | encountered. messages could be already written in |
69 | global message table by calling programs so the check for|
70 | looking whether something is there in the message table |
71 | to set error status is not good. |
72 | 27-AUG-2003 James Bernard Bug 3115930 Modified code so that user can |
73 | create expired lots. |
74 | 25-NOV-2003 RajaSekhar Reddy BUG#3259379 |
75 | Modified code to set the default effective max date |
76 | for retest, expire and expaction dates if item is a |
77 | non grade controlled item and Retest Interval, Shelf Life|
78 | or Expiration Interval are equal to zero. |
79 | 04-DEC-2003 Sastry BUG#3270249 |
80 | Modified code to set the correct timestamp for |
81 | lot_created, creation_date and last_update_date |
82 | for a new lot created while receving. |
83 | 17-MAY-2004 Archana Mundhe Bug 3621870 |
84 | Assign max date to retest,expire and expaction date for |
85 | non grade ctl items. |
86 | 6-JUL-2004 3739308 - changed above interval check to <> 0. Jatinder |
87 +==========================================================================+
88 */
89 PROCEDURE Validate_Lot
90 ( p_api_version IN NUMBER
91 , p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL
92 , p_lot_rec IN GMIGAPI.lot_rec_typ
93 , p_ic_item_mst_row IN ic_item_mst%ROWTYPE
94 , p_ic_item_cpg_row IN ic_item_cpg%ROWTYPE
95 , x_ic_lots_mst_row OUT NOCOPY ic_lots_mst%ROWTYPE
96 , x_ic_lots_cpg_row OUT NOCOPY ic_lots_cpg%ROWTYPE
97 , x_return_status OUT NOCOPY VARCHAR2
98 , x_msg_count OUT NOCOPY NUMBER
99 , x_msg_data OUT NOCOPY VARCHAR2
100 )
101 IS
102 l_api_name CONSTANT VARCHAR2 (30) := 'Validate_Lot';
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(2000);
105 l_return_status VARCHAR2(1);
106 --BUG#3115930 James
107 --Initialized the variables.
108 l_item_no ic_item_mst.item_no%TYPE := p_ic_item_mst_row.item_no;
109 l_lot_no ic_lots_mst.lot_no%TYPE := p_lot_rec.lot_no;
110 l_sublot_no ic_lots_mst.sublot_no%TYPE:= p_lot_rec.sublot_no;
111 l_qc_grade ic_lots_mst.qc_grade%TYPE;
112 l_expaction_code ic_lots_mst.expaction_code%TYPE;
113 l_user_name fnd_user.user_name%TYPE;
114 l_lot_rec GMIGAPI.lot_rec_typ;
115 l_qc_grad_mst_row qc_grad_mst%ROWTYPE;
116 l_qc_actn_mst_row qc_actn_mst%ROWTYPE;
117 l_po_vend_mst_row po_vend_mst%ROWTYPE;
118 l_def_lot_desc NUMBER; /* Bug 2343411 */
119
120 BEGIN
121 /* Standard call to check for call compatibility. */
122 IF NOT FND_API.Compatible_API_CALL ( GMIGUTL.API_VERSION
123 , p_api_version
124 , l_api_name
125 , G_PKG_NAME
126 )
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131 /* Assume things are going to work out...... */
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 /* Initialise output records. Other values will be setup below */
135 /* First set up the audit columns on both rows */
136
137 /* Default creation/update dates if not passed in */
138 /* **************************************************
139 Jalaj Srivastava 08/20/02 Bug 2520129
140 The lot rec typ record is changed in GMIGAPI
141 to not default lot created date to sysdate to
142 make it referencable from forms. Forms has a
143 limitation of not acceptinf default values
144 of sysdate for record objects.
145 We will default the lot created date here
146 from now on.
147 ************************************************** */
148 -- BEGIN BUG#3270249 Sastry
149 -- Modified code so that the timestamp for lot_created, creation_date and
150 -- last_update_date date is correct for a new lot created while receving.
151 -- Jatinder Gogna - 3470841 - Do not default NULL lot_created date to sysdate
152 IF (TRUNC(p_lot_rec.lot_created) = TRUNC(SYSDATE))
153 THEN
154 x_ic_lots_mst_row.lot_created := SYSDATE;
155 ELSE
156 x_ic_lots_mst_row.lot_created := p_lot_rec.lot_created;
157 END IF;
158 x_ic_lots_mst_row.creation_date := SYSDATE;
159 x_ic_lots_mst_row.last_update_date := SYSDATE;
160 --END BUG#3270249
161
162 x_ic_lots_mst_row.created_by := GMIGUTL.DEFAULT_USER_ID;
163 x_ic_lots_mst_row.last_updated_by := GMIGUTL.DEFAULT_USER_ID;
164 x_ic_lots_mst_row.last_update_login:= GMIGUTL.DEFAULT_LOGIN;
165
166 -- Jatinder B3158806 - Removed check for SY$CPG_INSTALL
167 /* Set up values for ic_lots_cpg. */
168 x_ic_lots_cpg_row.item_id := p_ic_item_mst_row.item_id;
169 x_ic_lots_cpg_row.lot_id := NULL;
170 x_ic_lots_cpg_row.created_by := GMIGUTL.DEFAULT_USER_ID;
171 x_ic_lots_cpg_row.last_updated_by := GMIGUTL.DEFAULT_USER_ID;
172 x_ic_lots_cpg_row.creation_date:= x_ic_lots_mst_row.creation_date;
173 x_ic_lots_cpg_row.last_update_date := x_ic_lots_mst_row.last_update_date;
174 x_ic_lots_cpg_row.last_update_login:= GMIGUTL.DEFAULT_LOGIN;
175
176 /* Bug 2343411 - User defined lot description */
177 /* Based on the value of the profile option, populate the lot_desc */
178
179 /* First, lets get the value of the profile option */
180 l_def_lot_desc := GMIGUTL.IC$DEFAULT_LOT_DESC;
181
182 /* Only set up the default lot desc if the user does not pass one in */
183 IF p_lot_rec.lot_desc IS NULL
184 THEN
185 -- If it is set to zero then nothing goes into the lot description
186 IF (l_def_lot_desc = 0)
187 THEN
188 x_ic_lots_mst_row.lot_desc := NULL;
189 -- If it is set to one then item_no goes into the lot description
190 ELSIF (l_def_lot_desc = 1)
191 THEN
192 x_ic_lots_mst_row.lot_desc := p_ic_item_mst_row.item_no;
193 -- If it is set to three then item_no from whse_item goes into the descr
194 ELSIF (l_def_lot_desc = 3)
195 THEN
196 SELECT item_no
197 INTO x_ic_lots_mst_row.lot_desc
198 FROM ic_item_mst
199 WHERE item_id = (SELECT whse_item_id
200 FROM ic_item_mst
201 WHERE item_id = p_ic_item_mst_row.item_id);
202 -- If it is set to 2 (or anything else) then use item_desc for lot desc
203 ELSE
204 x_ic_lots_mst_row.lot_desc := SUBSTRB(p_ic_item_mst_row.item_desc1,1,40);
205 END IF;
206 ELSE /* The user has input a lot desc so use it */
207 x_ic_lots_mst_row.lot_desc := p_lot_rec.lot_desc;
208 END IF;
209 /* Set up the values on ic_lots_mst. */
210
211 -- Bug 1886611
212 -- Assign vendor_lot_no so that it is updated in ic_lots_mst.
213 x_ic_lots_mst_row.vendor_lot_no := p_lot_rec.vendor_lot_no;
214
215 x_ic_lots_mst_row.item_id := p_ic_item_mst_row.item_id;
216 x_ic_lots_mst_row.lot_id := NULL;
217 -- x_ic_lots_mst_row.lot_desc := p_lot_rec.lot_desc; /* Bug 2343411 */
218 x_ic_lots_mst_row.expaction_date := p_lot_rec.expaction_date;
219 x_ic_lots_mst_row.expire_date := p_lot_rec.expire_date;
220 x_ic_lots_mst_row.retest_date := p_lot_rec.retest_date;
221 x_ic_lots_mst_row.strength := p_lot_rec.strength;
222 x_ic_lots_mst_row.inactive_ind := p_lot_rec.inactive_ind;
223 x_ic_lots_mst_row.origination_type := p_lot_rec.origination_type;
224 x_ic_lots_mst_row.trans_cnt := 1;
225 x_ic_lots_mst_row.delete_mark := 0;
226 x_ic_lots_mst_row.text_code := NULL;
227 x_ic_lots_mst_row.attribute1 := UPPER(p_lot_rec.attribute1);
228 x_ic_lots_mst_row.attribute2 := UPPER(p_lot_rec.attribute2);
229 x_ic_lots_mst_row.attribute3 := UPPER(p_lot_rec.attribute3);
230 x_ic_lots_mst_row.attribute4 := UPPER(p_lot_rec.attribute4);
231 x_ic_lots_mst_row.attribute5 := UPPER(p_lot_rec.attribute5);
232 x_ic_lots_mst_row.attribute6 := UPPER(p_lot_rec.attribute6);
233 x_ic_lots_mst_row.attribute7 := UPPER(p_lot_rec.attribute7);
234 x_ic_lots_mst_row.attribute8 := UPPER(p_lot_rec.attribute8);
235 x_ic_lots_mst_row.attribute9 := UPPER(p_lot_rec.attribute9);
236 x_ic_lots_mst_row.attribute10 := UPPER(p_lot_rec.attribute10);
237 x_ic_lots_mst_row.attribute11 := UPPER(p_lot_rec.attribute11);
238 x_ic_lots_mst_row.attribute12 := UPPER(p_lot_rec.attribute12);
239 x_ic_lots_mst_row.attribute13 := UPPER(p_lot_rec.attribute13);
240 x_ic_lots_mst_row.attribute14 := UPPER(p_lot_rec.attribute14);
241 x_ic_lots_mst_row.attribute15 := UPPER(p_lot_rec.attribute15);
242 x_ic_lots_mst_row.attribute16 := UPPER(p_lot_rec.attribute16);
243 x_ic_lots_mst_row.attribute17 := UPPER(p_lot_rec.attribute17);
244 x_ic_lots_mst_row.attribute18 := UPPER(p_lot_rec.attribute18);
245 x_ic_lots_mst_row.attribute19 := UPPER(p_lot_rec.attribute19);
246 x_ic_lots_mst_row.attribute20 := UPPER(p_lot_rec.attribute20);
247 x_ic_lots_mst_row.attribute21 := UPPER(p_lot_rec.attribute21);
248 x_ic_lots_mst_row.attribute22 := UPPER(p_lot_rec.attribute22);
249 x_ic_lots_mst_row.attribute23 := UPPER(p_lot_rec.attribute23);
250 x_ic_lots_mst_row.attribute24 := UPPER(p_lot_rec.attribute24);
251 x_ic_lots_mst_row.attribute25 := UPPER(p_lot_rec.attribute25);
252 x_ic_lots_mst_row.attribute26 := UPPER(p_lot_rec.attribute26);
253 x_ic_lots_mst_row.attribute27 := UPPER(p_lot_rec.attribute27);
254 x_ic_lots_mst_row.attribute28 := UPPER(p_lot_rec.attribute28);
255 x_ic_lots_mst_row.attribute29 := UPPER(p_lot_rec.attribute29);
256 x_ic_lots_mst_row.attribute30 := UPPER(p_lot_rec.attribute30);
257 x_ic_lots_mst_row.attribute_category := UPPER(p_lot_rec.attribute_category);
258
259 IF p_validation_level = FND_API.G_VALID_LEVEL_NONE
260 AND p_lot_rec.lot_no = GMIGUTL.IC$DEFAULT_LOT
261 THEN
262 /* We are creating the default lot for a new item. */
263 x_ic_lots_mst_row.lot_no := GMIGUTL.IC$DEFAULT_LOT;
264 x_ic_lots_mst_row.lot_id := 0;
265 x_ic_lots_mst_row.qc_grade := UPPER(p_ic_item_mst_row.qc_grade);
266 x_ic_lots_mst_row.lot_created := p_ic_item_mst_row.creation_date;
267 -- Bug 2458413 - Need to set up default dates to avoid having them
268 -- being set to the FND_API.G_MISS_DATE
269 x_ic_lots_mst_row.expire_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
270 x_ic_lots_mst_row.retest_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
271 x_ic_lots_mst_row.expaction_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
272
273 -- Jatinder Gogna-B3158806- A row is always inserted into ic_lots_cpg
274 x_ic_lots_cpg_row.lot_id := 0;
275 x_ic_lots_cpg_row.ic_matr_date := NULL;
276 x_ic_lots_cpg_row.ic_hold_date := NULL;
277 ELSE
278 /* Carry out fundamental validation. These errors will prevent any */
279 /* further validation. */
280 IF p_ic_item_mst_row.item_id = 0 OR
281 p_ic_item_mst_row.delete_mark = 1
282 THEN
283 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
284 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_ic_item_mst_row.item_no);
285 FND_MSG_PUB.Add;
286 RAISE FND_API.G_EXC_ERROR;
287 ELSIF p_ic_item_mst_row.inactive_ind = 1 AND
288 GMIGUTL.IC$API_ALLOW_INACTIVE = 0
289 THEN
290 FND_MESSAGE.SET_NAME('GMI','IC_API_INACTIVE_ITEM_NO');
291 FND_MESSAGE.SET_TOKEN('ITEM_NO',p_ic_item_mst_row.item_no);
292 FND_MSG_PUB.Add;
293 RAISE FND_API.G_EXC_ERROR;
294 END IF;
295
296 /* If we're still alive carry on with validation */
297
298 /* Check to see if item is lot Controlled */
299 IF p_ic_item_mst_row.lot_ctl = 0
300 THEN
301 FND_MESSAGE.SET_NAME('GMI','IC_API_ITEM_NOT_LOT_CTL');
302 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
303 FND_MSG_PUB.Add;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 END IF;
306
307 /* Check that lot number has been supplied */
308 IF NVL(p_lot_rec.lot_no,' ') <> ' '
309 THEN
310 /* J. DiIorio 01/19/2001 BUG#1570046 */
311 x_ic_lots_mst_row.lot_no := UPPER(p_lot_rec.lot_no);
312 ELSE
313 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_NO');
314 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
315 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
316 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
317 FND_MSG_PUB.Add;
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 END IF;
320
321 /* If item is not sublot controlled, flatten the sublot number, otherwise */
322 /* use what we are given. */
323
324 IF p_ic_item_mst_row.sublot_ctl = 0
325 THEN
326 x_ic_lots_mst_row.sublot_no:= NULL;
327 ELSE
328 /* J. DiIorio 01/19/2001 BUG#1570046 */
329 x_ic_lots_mst_row.sublot_no:= UPPER(p_lot_rec.sublot_no);
330 END IF;
331
332 /* If item is not QC graded, default qc_grade to null, regardless */
333 /* of what was passed in. If the item is QC graded but no value was */
334 /* given, default the value from the item. If a value was passed in */
335 /* ensure it is a valid grade. */
336
337 IF p_ic_item_mst_row.grade_ctl = 0
338 THEN
339 x_ic_lots_mst_row.qc_grade := NULL;
340 ELSE
341 /* Validate QC Grade */
342 IF NVL(p_lot_rec.qc_grade, p_ic_item_mst_row.qc_grade) = p_ic_item_mst_row.qc_grade
343 THEN
344 x_ic_lots_mst_row.qc_grade := p_ic_item_mst_row.qc_grade;
345 ELSE
346 IF GMIGUTL.v_qc_grade(UPPER(p_lot_rec.qc_grade),l_qc_grad_mst_row)
347 THEN
348 x_ic_lots_mst_row.qc_grade := l_qc_grad_mst_row.qc_grade;
349 ELSE
350 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_QC_GRADE');
351 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
352 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
353 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
354 FND_MSG_PUB.Add;
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 END IF;
357 END IF;
358 END IF;
359
360 /* Validate expiry action code */
361 IF p_lot_rec.expaction_code IS NULL
362 THEN
363 x_ic_lots_mst_row.expaction_code := p_ic_item_mst_row.expaction_code;
364 ELSE
365 IF GMIGUTL.v_expaction_code
366 (UPPER(p_lot_rec.expaction_code), l_qc_actn_mst_row)
367 THEN
368 x_ic_lots_mst_row.expaction_code:=l_qc_actn_mst_row.action_code;
369 ELSE
370 FND_MESSAGE.SET_NAME('GMI','IC_API_INV_LOT_EXPACTION_CODE');
371 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
372 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
373 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
374 FND_MSG_PUB.Add;
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 END IF;
377 END IF;
378
379 /* Validate Expiry Date */
380 IF NVL(p_lot_rec.expire_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE
381 THEN
382 -- Bug 3621870
383 -- Check if its a grade controlled item.
384 -- Assign max date to expire date for non grade ctl items.
385 -- Jatinder Gogna - 3470841 - Compute only if lot_created is specified.
386 IF p_ic_item_mst_row.grade_ctl = 1 OR
387 (p_ic_item_mst_row.grade_ctl = 0 AND p_ic_item_mst_row.shelf_life <> 0)THEN
388 IF p_lot_rec.lot_created is not NULL THEN
389 x_ic_lots_mst_row.expire_date :=
390 x_ic_lots_mst_row.lot_created + NVL(p_ic_item_mst_row.shelf_life,0);
391 END IF;
392 ELSE
393 x_ic_lots_mst_row.expire_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
394 END IF;
395 ELSE
396 -- BEGIN BUG#3115930 James Bernard
397 -- Modified code so that user is now allowed to create expired lots.
398 -- However a message is logged.
399 x_ic_lots_mst_row.expire_date := p_lot_rec.expire_date;
400 IF TRUNC(p_lot_rec.expire_date) < TRUNC(x_ic_lots_mst_row.lot_created) THEN
401 FND_MESSAGE.SET_NAME('GMI','IC_API_PAST_EXPIRE_DATE');
402 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
403 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
404 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
405 FND_MSG_PUB.Add;
406 END IF;
407 -- END BUG#3115930
408 END IF;
409
410
411 /* Validate Retest Date */
412
413 IF NVL(p_lot_rec.retest_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE
414 THEN
415 -- Bug 3621870
416 -- Check if its a grade controlled item.
417 -- Assign max date to retest date for non grade ctl items.
418 -- Jatinder Gogna - 3470841 - Compute only if lot_created is specified.
419 IF p_ic_item_mst_row.grade_ctl = 1 OR
420 (p_ic_item_mst_row.grade_ctl = 0 AND p_ic_item_mst_row.retest_interval <> 0) THEN
421 IF p_lot_rec.lot_created is not NULL THEN
422 x_ic_lots_mst_row.retest_date :=
423 x_ic_lots_mst_row.lot_created + NVL(p_ic_item_mst_row.retest_interval,0);
424 END IF;
425 ELSE
426 x_ic_lots_mst_row.retest_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
427 END IF;
428 ELSE
429 IF TRUNC(p_lot_rec.retest_date) >= TRUNC(x_ic_lots_mst_row.lot_created)
430 THEN
431 x_ic_lots_mst_row.retest_date := p_lot_rec.retest_date;
432 ELSE
433 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_RETEST_DATE');
434 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
435 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
436 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
437 FND_MSG_PUB.Add;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 END IF;
440 END IF;
441
442 /* Validate Expiry Action Date */
443
444 IF NVL(p_lot_rec.expaction_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE
445 THEN
446 -- Bug 3621870
447 -- Check if its a grade controlled item.
448 -- Assign max date to expaction date for non grade ctl items.
449 -- Jatinder Gogna - 3470841 - Compute only if lot_created is specified.
450 IF p_ic_item_mst_row.grade_ctl = 1 OR
451 (p_ic_item_mst_row.grade_ctl = 0 AND p_ic_item_mst_row.expaction_interval <> 0) THEN
452 IF p_lot_rec.lot_created is not NULL THEN
453 x_ic_lots_mst_row.expaction_date :=
454 x_ic_lots_mst_row.expire_date + NVL(p_ic_item_mst_row.expaction_interval,0);
455 END IF;
456 ELSE
457 x_ic_lots_mst_row.expaction_date := GMA_GLOBAL_GRP.SY$MAX_DATE;
458 END IF;
459 ELSE
460 -- BEGIN BUG#3115930 James Bernard
461 -- Modified code so that user is now allowed to create expired lots.
462 -- However a message is logged.
463 x_ic_lots_mst_row.expaction_date := p_lot_rec.expaction_date;
464 IF TRUNC(p_lot_rec.expaction_date) < TRUNC(x_ic_lots_mst_row.lot_created) THEN
465 FND_MESSAGE.SET_NAME('GMI','IC_API_PAST_EXPACTION_DATE');
466 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
467 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
468 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
469 FND_MSG_PUB.Add;
470 END IF;
471 -- END BUG#3115930
472 END IF;
473
474 /* Validate Strength */
475 IF p_lot_rec.strength BETWEEN 0 AND 100
476 THEN
477 x_ic_lots_mst_row.strength := p_lot_rec.strength;
478 ELSE
479 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_STRENGTH');
480 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
481 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
482 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
483 FND_MSG_PUB.Add;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 END IF;
486
487 /* Validate Inactive Indicator */
488 IF p_lot_rec.inactive_ind IN (0,1)
489 THEN
490 x_ic_lots_mst_row.inactive_ind := p_lot_rec.inactive_ind;
491 ELSE
492 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_INACTIVE');
493 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
494 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
495 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
496 FND_MSG_PUB.Add;
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 END IF;
499
500 /* Validate Origination Type */
501
502 IF p_lot_rec.origination_type IN (0,1,2,3)
503 THEN
504 x_ic_lots_mst_row.origination_type := p_lot_rec.origination_type;
505 ELSE
506 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_ORIG_TYPE');
507 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
508 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
509 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
510 FND_MSG_PUB.Add;
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 END IF;
513
514 /* Validate Shipvendor Number */
515 IF NVL(p_lot_rec.shipvendor_no, ' ') = ' '
516 THEN
517 x_ic_lots_mst_row.shipvend_id := NULL;
518 ELSE
519 IF GMIGUTL.v_ship_vendor(p_lot_rec.shipvendor_no,l_po_vend_mst_row)
520 THEN
521 x_ic_lots_mst_row.shipvend_id:= l_po_vend_mst_row.vendor_id;
522 ELSE
523 FND_MESSAGE.SET_NAME('GMI','IC_API_INV_LOT_SHIPVENDOR_NO');
524 FND_MESSAGE.SET_TOKEN('ITEM_NO', l_item_no);
525 FND_MESSAGE.SET_TOKEN('LOT_NO', l_lot_no);
526 FND_MESSAGE.SET_TOKEN('SUBLOT_NO', l_sublot_no);
527 FND_MSG_PUB.Add;
528 x_return_status := FND_API.G_RET_STS_ERROR;
529 END IF;
530 END IF;
531
532
533 /* Jatinder - B3158806- Moved this out of CPG block as this is a standard
534 OPM field now. */
535 /* Validate Hold release date (CPG) */
536 IF NVL(p_lot_rec.ic_hold_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE
537 THEN
538 /* Jalaj Srivastava Bug 3158806
539 hold release date would be populated by the first yielding transaction
540 for non CPG installs */
541 IF p_ic_item_cpg_row.ic_hold_days is not NULL and GMIGUTL.SY$CPG_INSTALL <> 0 THEN
542 x_ic_lots_cpg_row.ic_hold_date :=
543 x_ic_lots_mst_row.lot_created +
544 p_ic_item_cpg_row.ic_hold_days;
545 END IF;
546 ELSE
547 IF (p_lot_rec.ic_hold_date < x_ic_lots_mst_row.lot_created)
548 THEN
549 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_HOLD_DATE');
550 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
551 FND_MSG_PUB.Add;
552 x_return_status := FND_API.G_RET_STS_ERROR;
553 END IF;
554 /* Jalaj Srivastava Bug 3158806
555 Assign hold release date when passed by the user*/
556 x_ic_lots_cpg_row.ic_hold_date := p_lot_rec.ic_hold_date;
557 END IF;
558
559 IF GMIGUTL.SY$CPG_INSTALL <> 0
560 THEN
561 /* Validate Maturity date (CPG) */
562 IF NVL(p_lot_rec.ic_matr_date, FND_API.G_MISS_DATE) = FND_API.G_MISS_DATE
563 THEN
564 x_ic_lots_cpg_row.ic_matr_date :=
565 x_ic_lots_mst_row.creation_date + NVL(p_ic_item_cpg_row.ic_matr_days, 0);
566 ELSE
567 IF (p_lot_rec.ic_matr_date < x_ic_lots_mst_row.lot_created)
568 THEN
569 FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_LOT_MATR_DATE');
570 FND_MESSAGE.SET_TOKEN('ITEM_NO',l_item_no);
571 FND_MSG_PUB.Add;
572 x_return_status := FND_API.G_RET_STS_ERROR;
573 END IF;
574 END IF;
575
576 END IF;
577 END IF;
578
579 FND_MSG_PUB.Count_AND_GET
580 (p_count => x_msg_count, p_data => x_msg_data);
581
582 --Jalaj Srivastava Bug 2811747
583 --commented out below.
584 --this is not needed
585 --it raises a error for debug/informative messages in the global message table
586 --from calling procedures.
587 --we would instead set the error status individually for error cases.
588 /* ***********************************************************************
589 IF x_msg_count > 0
590 THEN
591 x_return_status := FND_API.G_RET_STS_ERROR;
592 END IF;
593 ************************************************************************** */
594
595 EXCEPTION
596
597 WHEN FND_API.G_EXC_ERROR THEN
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 FND_MSG_PUB.Count_AND_GET
600 ( p_count => x_msg_count,
601 p_data => x_msg_data
602 );
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605 FND_MSG_PUB.Count_AND_GET
606 ( p_count => x_msg_count,
607 p_data => x_msg_data
608 );
609 WHEN OTHERS THEN
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 FND_MSG_PUB.Add_Exc_Msg
612 ( G_PKG_NAME ,
613 l_api_name
614 );
615 FND_MSG_PUB.Count_AND_GET
616 ( p_count => x_msg_count,
617 p_data => x_msg_data
618 );
619 END Validate_Lot;
620
621 END GMIVLOT;
[Home] [Help]