[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_API_PKG
Source
1 PACKAGE BODY INV_LOT_API_PKG
2 /* $Header: INVVLTPB.pls 120.9.12020000.5 2013/04/17 12:56:23 ptian ship $ */
3 AS
4
5 -- Global constant holding the package name
6 g_pkg_name CONSTANT VARCHAR2 ( 30 ) := 'INV_LOT_API_PKG';
7
8
9 PROCEDURE print_debug ( p_err_msg VARCHAR2, p_level NUMBER DEFAULT 1)
10 IS
11 BEGIN
12 IF (g_debug = 1) THEN
13 inv_mobile_helper_functions.tracelog (
14 p_err_msg => p_err_msg,
15 p_module => 'INV_LOT_API_PKG',
16 p_level => p_level
17 );
18
19 END IF;
20 END print_debug;
21
22 PROCEDURE Check_Item_Attributes(
23 x_return_status OUT NOCOPY VARCHAR2
24 , x_msg_count OUT NOCOPY NUMBER
25 , x_msg_data OUT NOCOPY VARCHAR2
26 , x_lot_cont OUT NOCOPY BOOLEAN
27 , x_child_lot_cont OUT NOCOPY BOOLEAN
28 , p_inventory_item_id IN NUMBER
29 , p_organization_id IN NUMBER
30 )
31 IS
32
33 /* Cursor definition to check whether item is a valid and it's lot, child lot controlled */
34 CURSOR c_chk_msi_attr IS
35 SELECT lot_control_code,
36 child_lot_flag
37 FROM mtl_system_items
38 WHERE inventory_item_id = p_inventory_item_id
39 AND organization_id = p_organization_id;
40
41 l_chk_msi_attr_rec c_chk_msi_attr%ROWTYPE;
42
43 BEGIN
44
45 x_return_status := fnd_api.g_ret_sts_success;
46
47 /******************* START Item validation ********************/
48
49 /* Check item attributes in Mtl_system_items Table */
50 OPEN c_chk_msi_attr ;
51 FETCH c_chk_msi_attr INTO l_chk_msi_attr_rec;
52
53 IF c_chk_msi_attr%NOTFOUND THEN
54 CLOSE c_chk_msi_attr;
55 IF (g_debug = 1) THEN
56 print_debug('Item not found. Invalid item. Please re-enter.', 9);
57 END IF;
58
59 x_lot_cont := FALSE ;
60 x_child_lot_cont := FALSE ;
61 x_return_status := fnd_api.g_ret_sts_error;
62
63 fnd_message.set_name('INV', 'INV_INVALID_ITEM');
64 fnd_msg_pub.ADD;
65 RAISE fnd_api.g_exc_error;
66 ELSE
67 CLOSE c_chk_msi_attr;
68
69 /* If not lot controlled then error out */
70 IF (l_chk_msi_attr_rec.lot_control_code = 1) THEN
71 x_lot_cont := FALSE ;
72 IF g_debug = 1 THEN
73 print_debug('Check_Item_Attributes. Item is not lot controlled ', 9);
74 END IF;
75 ELSE
76 x_lot_cont := TRUE ;
77 IF g_debug = 1 THEN
78 print_debug('Check_Item_Attributes. Item is lot controlled ', 9);
79 END IF;
80 END IF; /* l_chk_msi_attr_rec.lot_control_code = 1 */
81
82 /* If not child lot enabled and p_parent_lot_number IS NOT NULL then error out */
83 IF (l_chk_msi_attr_rec.child_lot_flag = 'N' ) THEN
84 x_child_lot_cont := FALSE ;
85 IF g_debug = 1 THEN
86 print_debug('Check_Item_Attributes. Item is not child lot enabled ', 9);
87 END IF;
88 ELSE
89 x_child_lot_cont := TRUE ;
90 IF g_debug = 1 THEN
91 print_debug('Check_Item_Attributes. Item is child lot enabled ', 9);
92 END IF;
93 END IF; /* l_chk_msi_attr_rec.child_lot_flag = 'N' */
94
95
96 END IF;
97
98 /******************* End Item validation ********************/
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 x_return_status := fnd_api.g_ret_sts_error;
102 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
103 if( x_msg_count > 1 ) then
104 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
105 end if;
106 print_debug('In Check_Item_Attributes, No data found ' || SQLERRM, 9);
107 WHEN fnd_api.g_exc_error THEN
108 x_return_status := fnd_api.g_ret_sts_error;
109 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
110 if( x_msg_count > 1 ) then
111 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
112 end if;
113 print_debug('In Check_Item_Attributes, g_exc_error ' || SQLERRM, 9);
114 WHEN fnd_api.g_exc_unexpected_error THEN
115 x_return_status := fnd_api.g_ret_sts_unexp_error;
116 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
117 if( x_msg_count > 1 ) then
118 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
119 end if;
120 print_debug('In Check_Item_Attributes, g_exc_unexpected_error ' || SQLERRM, 9);
121 WHEN OTHERS THEN
122 x_return_status := fnd_api.g_ret_sts_unexp_error;
123 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
124 if( x_msg_count > 1 ) then
125 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
126 end if;
127 print_debug('In Check_Item_Attributes, Others ' || SQLERRM, 9);
128
129 END Check_Item_Attributes;
130
131
132 --14192172
133 PROCEDURE Populate_Lot_Records (
134 x_return_status OUT NOCOPY VARCHAR2
135 , x_msg_count OUT NOCOPY NUMBER
136 , x_msg_data OUT NOCOPY VARCHAR2
137 , x_child_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
138 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
139 , p_copy_lot_attribute_flag IN VARCHAR2
140 , p_source IN NUMBER
141 , p_api_version IN NUMBER
142 , p_init_msg_list IN VARCHAR2
143 , p_commit IN VARCHAR2
144 )
145 IS
146 BEGIN
147 --call over load procedure
148 Populate_Lot_Records (
149 x_return_status => x_return_status
150 , x_msg_count => x_msg_count
151 , x_msg_data => x_msg_data
152 , x_child_lot_rec => x_child_lot_rec
153 , p_lot_rec => p_lot_rec
154 , p_copy_lot_attribute_flag => p_copy_lot_attribute_flag
155 , p_source => p_source
156 , p_api_version => p_api_version
157 , p_init_msg_list => p_init_msg_list
158 , p_commit => p_commit
159 , p_caculate_flag => null
160 );
161
162 END Populate_Lot_Records;
163
164 PROCEDURE Populate_Lot_Records (
165 x_return_status OUT NOCOPY VARCHAR2
166 , x_msg_count OUT NOCOPY NUMBER
167 , x_msg_data OUT NOCOPY VARCHAR2
168 , x_child_lot_rec OUT NOCOPY MTL_LOT_NUMBERS%ROWTYPE
169 , p_lot_rec IN MTL_LOT_NUMBERS%ROWTYPE
170 , p_copy_lot_attribute_flag IN VARCHAR2
171 , p_source IN NUMBER
172 , p_api_version IN NUMBER
173 , p_init_msg_list IN VARCHAR2
174 , p_commit IN VARCHAR2
175 , p_caculate_flag IN VARCHAR2 --14192172
176 )
177 IS
178
179 /* Cursor definition to check if Lot UOM Conversion is needed */
180 CURSOR c_lot_uom_conv(cp_organization_id NUMBER) IS
181 SELECT copy_lot_attribute_flag,
182 lot_number_generation
183 FROM mtl_parameters
184 WHERE organization_id = cp_organization_id;
185
186 l_lot_uom_conv c_lot_uom_conv%ROWTYPE ;
187
188 /* Cursor definition to check lot existence in Mtl_Lot_Numbers Table */
189 CURSOR c_chk_lot_exists(cp_lot_number mtl_lot_numbers.lot_number%TYPE,cp_inventory_item_id NUMBER, cp_organization_id NUMBER) IS
190 SELECT lot_number
191 FROM mtl_lot_numbers
192 WHERE lot_number = cp_lot_number AND
193 inventory_item_id = cp_inventory_item_id AND
194 organization_id = cp_organization_id ;
195
196 l_chk_lot_rec c_chk_lot_exists%ROWTYPE;
197
198 CURSOR c_parent_lot_attr (cp_lot_number mtl_lot_numbers.lot_number%TYPE) IS
199 SELECT *
200 FROM mtl_lot_numbers
201 WHERE lot_number = cp_lot_number ;
202
203 l_parent_lot_attr c_parent_lot_attr%ROWTYPE ;
204
205 l_copy_lot_attribute_flag VARCHAR2(1) ;
206 l_parent_exists_flag VARCHAR2(1) ;
207 l_return_status VARCHAR2(1) ;
208 l_msg_data VARCHAR2(2000) ;
209 l_msg_count NUMBER ;
210 l_api_version NUMBER;
211 l_init_msg_list VARCHAR2(100);
212 l_commit VARCHAR2(100);
213 l_source NUMBER;
214 l_child_lot_rec mtl_lot_numbers%ROWTYPE ;
215 l_mtl_gen_obj_no NUMBER ;
216
217 l_lot_cont BOOLEAN ;
218 l_child_lot_cont BOOLEAN ;
219
220 l_caculate_flag VARCHAR2(10);
221
222 BEGIN
223
224 SAVEPOINT inv_pop_lot ;
225
226 l_source := p_source ;
227 l_api_version := 1.0;
228 l_init_msg_list := fnd_api.g_false;
229 l_commit := fnd_api.g_false;
230
231
232 /******************* START Item validation ********************/
233
234 l_lot_cont := FALSE ;
235 l_child_lot_cont := FALSE ;
236
237 check_item_attributes
238 (
239 x_return_status => l_return_status
240 , x_msg_count => l_msg_count
241 , x_msg_data => l_msg_data
242 , x_lot_cont => l_lot_cont
243 , x_child_lot_cont => l_child_lot_cont
244 , p_inventory_item_id => p_lot_rec.inventory_item_id
245 , p_organization_id => p_lot_rec.organization_id
246 ) ;
247
248 IF g_debug = 1 THEN
249 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
250 END IF;
251
252 IF l_return_status = fnd_api.g_ret_sts_error THEN
253 IF g_debug = 1 THEN
254 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
255 END IF;
256 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
257 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
258 FND_MSG_PUB.ADD;
259 RAISE fnd_api.g_exc_error;
260 END IF;
261
262 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
263 IF g_debug = 1 THEN
264 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
265 END IF;
266 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
267 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
268 fnd_msg_pub.ADD;
269 RAISE fnd_api.g_exc_unexpected_error;
270 END IF;
271
272 IF (l_lot_cont = FALSE) THEN
273 IF g_debug = 1 THEN
274 print_debug(' Item is not lot controlled ', 9);
275 END IF;
276 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
277 fnd_msg_pub.ADD;
278 x_return_status := fnd_api.g_ret_sts_error;
279 RAISE fnd_api.g_exc_error;
280 END IF ;
281
282 IF (l_child_lot_cont = FALSE AND p_lot_rec.parent_lot_number IS NOT NULL) THEN
283
284 IF g_debug = 1 THEN
285 print_debug(' Item is not Child lot controlled ', 9);
286 END IF;
287 fnd_message.set_name('INV', 'INV_ITEM_CLOT_DISABLE_EXP');
288 fnd_msg_pub.ADD;
289 x_return_status := fnd_api.g_ret_sts_error;
290 RAISE fnd_api.g_exc_error;
291 END IF ;
292 /******************* End Item validation ********************/
293
294
295 /* Verify p_copy_lot_attribute_flag and populate l_copy_lot_attribute_flag */
296
297 IF p_copy_lot_attribute_flag IS NULL THEN
298 -- Based on the value of Mtl_parameters.lot_number_generation retrieve the copy_lot_attribute_flag either
299 -- from Mtl_System_Items or from Mtl_Parameters and set l_copy_lot_attribute_flag.
300
301 /* Check needed for Lot UOM conversion */
302 OPEN c_lot_uom_conv (p_lot_rec.organization_id) ;
303 FETCH c_lot_uom_conv INTO l_lot_uom_conv ;
304
305 IF c_lot_uom_conv%FOUND THEN
306 -- Possible values for mtl_parameters.lot_number_generation are:
307 -- 1 At organization level
308 -- 3 User defined
309 -- 2 At item level
310
311 IF l_lot_uom_conv.lot_number_generation = 1 THEN
312 l_copy_lot_attribute_flag := NVL(l_lot_uom_conv.copy_lot_attribute_flag,'N') ;
313 ELSIF l_lot_uom_conv.lot_number_generation IN (2,3) THEN
314 SELECT copy_lot_attribute_flag INTO l_copy_lot_attribute_flag
315 FROM mtl_system_items
316 WHERE inventory_item_id = p_lot_rec.inventory_item_id
317 AND organization_id = p_lot_rec.organization_id;
318 END IF;
319
320 END IF ;
321 CLOSE c_lot_uom_conv ;
322
323 ELSE
324 l_copy_lot_attribute_flag := p_copy_lot_attribute_flag ;
325 END IF;
326
327
328 l_parent_exists_flag := NULL ;
329 -- Check for existence of p_lot_rec.parent_lot_number in Mtl_Lot_Numbers and set p_parent_exists_flag.
330 OPEN c_chk_lot_exists(p_lot_rec.parent_lot_number,p_lot_rec.INVENTORY_ITEM_ID,p_lot_rec.ORGANIZATION_ID);
331 FETCH c_chk_lot_exists INTO l_chk_lot_rec;
332
333 IF c_chk_lot_exists%FOUND THEN
334 /* Parent lot exists in Mtl_Lot_Numbers Table. */
335 l_parent_exists_flag := 'Y' ;
336
337 ELSIF c_chk_lot_exists%NOTFOUND AND p_lot_rec.parent_lot_number IS NOT NULL THEN
338 /* Parent lot DOES NOT exist in Mtl_Lot_Numbers Table. */
339 l_parent_exists_flag := 'N' ;
340 END IF;
341 CLOSE c_chk_lot_exists;
342
343 x_child_lot_rec := p_lot_rec ;
344
345 IF l_copy_lot_attribute_flag = 'Y' AND l_parent_exists_flag = 'Y' THEN
346 OPEN c_parent_lot_attr(p_lot_rec.parent_lot_number) ;
347 FETCH c_parent_lot_attr INTO l_parent_lot_attr ;
348
349 IF c_parent_lot_attr%FOUND THEN
350
351 -- Bug 4115021- For OPM inventory convergence added sampling event id to mtl_lot_numbers table.
352 x_child_lot_rec.sampling_event_id := NVL ( x_child_lot_rec.sampling_event_id , l_parent_lot_attr.sampling_event_id ) ;
353 x_child_lot_rec.grade_code := NVL ( x_child_lot_rec.grade_code , l_parent_lot_attr.grade_code ) ;
354 x_child_lot_rec.origination_type := NVL ( x_child_lot_rec.origination_type , l_parent_lot_attr.origination_type ) ;
355 x_child_lot_rec.origination_date := NVL ( x_child_lot_rec.origination_date , l_parent_lot_attr.origination_date ) ;
356 x_child_lot_rec.expiration_date := NVL ( x_child_lot_rec.expiration_date , l_parent_lot_attr.expiration_date ) ;
357 x_child_lot_rec.retest_date := NVL ( x_child_lot_rec.retest_date , l_parent_lot_attr.retest_date ) ;
358 x_child_lot_rec.expiration_action_date := NVL ( x_child_lot_rec.expiration_action_date , l_parent_lot_attr.expiration_action_date ) ;
359 x_child_lot_rec.expiration_action_code := NVL ( x_child_lot_rec.expiration_action_code , l_parent_lot_attr.expiration_action_code ) ;
360 x_child_lot_rec.hold_date := NVL ( x_child_lot_rec.hold_date , l_parent_lot_attr.hold_date ) ;
361 x_child_lot_rec.maturity_date := NVL ( x_child_lot_rec.maturity_date , l_parent_lot_attr.maturity_date ) ;
362 x_child_lot_rec.disable_flag := NVL ( x_child_lot_rec.disable_flag , l_parent_lot_attr.disable_flag ) ;
363 x_child_lot_rec.attribute_category := NVL ( x_child_lot_rec.attribute_category , l_parent_lot_attr.attribute_category ) ;
364 x_child_lot_rec.lot_attribute_category := NVL ( x_child_lot_rec.lot_attribute_category , l_parent_lot_attr.lot_attribute_category ) ;
365 x_child_lot_rec.date_code := NVL ( x_child_lot_rec.date_code , l_parent_lot_attr.date_code ) ;
366 x_child_lot_rec.status_id := NVL ( x_child_lot_rec.status_id , l_parent_lot_attr.status_id ) ;
367 x_child_lot_rec.change_date := NVL ( x_child_lot_rec.change_date , l_parent_lot_attr.change_date ) ;
368 x_child_lot_rec.age := NVL ( x_child_lot_rec.age , l_parent_lot_attr.age ) ;
369 x_child_lot_rec.retest_date := NVL ( x_child_lot_rec.retest_date , l_parent_lot_attr.retest_date ) ;
370 x_child_lot_rec.maturity_date := NVL ( x_child_lot_rec.maturity_date , l_parent_lot_attr.maturity_date ) ;
371 x_child_lot_rec.item_size := NVL ( x_child_lot_rec.item_size , l_parent_lot_attr.item_size ) ;
372 x_child_lot_rec.color := NVL ( x_child_lot_rec.color , l_parent_lot_attr.color ) ;
373 x_child_lot_rec.volume := NVL ( x_child_lot_rec.volume , l_parent_lot_attr.volume ) ;
374 x_child_lot_rec.volume_uom := NVL ( x_child_lot_rec.volume_uom , l_parent_lot_attr.volume_uom ) ;
375 x_child_lot_rec.place_of_origin := NVL ( x_child_lot_rec.place_of_origin , l_parent_lot_attr.place_of_origin ) ;
376 x_child_lot_rec.best_by_date := NVL ( x_child_lot_rec.best_by_date , l_parent_lot_attr.best_by_date ) ;
377 x_child_lot_rec.length := NVL ( x_child_lot_rec.length , l_parent_lot_attr.length ) ;
378 x_child_lot_rec.length_uom := NVL ( x_child_lot_rec.length_uom , l_parent_lot_attr.length_uom ) ;
379 x_child_lot_rec.recycled_content := NVL ( x_child_lot_rec.recycled_content , l_parent_lot_attr.recycled_content ) ;
380 x_child_lot_rec.thickness := NVL ( x_child_lot_rec.thickness , l_parent_lot_attr.thickness ) ;
381 x_child_lot_rec.thickness_uom := NVL ( x_child_lot_rec.thickness_uom , l_parent_lot_attr.thickness_uom ) ;
382 x_child_lot_rec.width := NVL ( x_child_lot_rec.width , l_parent_lot_attr.width ) ;
383 x_child_lot_rec.width_uom := NVL ( x_child_lot_rec.width_uom , l_parent_lot_attr.width_uom ) ;
384 x_child_lot_rec.territory_code := NVL ( x_child_lot_rec.territory_code , l_parent_lot_attr.territory_code ) ;
385 x_child_lot_rec.supplier_lot_number := NVL ( x_child_lot_rec.supplier_lot_number , l_parent_lot_attr.supplier_lot_number ) ;
386 x_child_lot_rec.vendor_name := NVL ( x_child_lot_rec.vendor_name , l_parent_lot_attr.vendor_name ) ;
387 x_child_lot_rec.lot_attribute_category := NVL ( x_child_lot_rec.lot_attribute_category , l_parent_lot_attr.lot_attribute_category ) ;
388 x_child_lot_rec.attribute_category := NVL ( x_child_lot_rec.attribute_category , l_parent_lot_attr.attribute_category ) ;
389 x_child_lot_rec.attribute1 := NVL ( x_child_lot_rec.attribute1 , l_parent_lot_attr.attribute1 ) ;
390 x_child_lot_rec.attribute2 := NVL ( x_child_lot_rec.attribute2 , l_parent_lot_attr.attribute2 ) ;
391 x_child_lot_rec.attribute3 := NVL ( x_child_lot_rec.attribute3 , l_parent_lot_attr.attribute3 ) ;
392 x_child_lot_rec.attribute4 := NVL ( x_child_lot_rec.attribute4 , l_parent_lot_attr.attribute4 ) ;
393 x_child_lot_rec.attribute5 := NVL ( x_child_lot_rec.attribute5 , l_parent_lot_attr.attribute5 ) ;
394 x_child_lot_rec.attribute6 := NVL ( x_child_lot_rec.attribute6 , l_parent_lot_attr.attribute6 ) ;
395 x_child_lot_rec.attribute7 := NVL ( x_child_lot_rec.attribute7 , l_parent_lot_attr.attribute7 ) ;
396 x_child_lot_rec.attribute8 := NVL ( x_child_lot_rec.attribute8 , l_parent_lot_attr.attribute8 ) ;
397 x_child_lot_rec.attribute9 := NVL ( x_child_lot_rec.attribute9 , l_parent_lot_attr.attribute9 ) ;
398 x_child_lot_rec.attribute10 := NVL ( x_child_lot_rec.attribute10 , l_parent_lot_attr.attribute10 ) ;
399 x_child_lot_rec.attribute11 := NVL ( x_child_lot_rec.attribute11 , l_parent_lot_attr.attribute11 ) ;
400 x_child_lot_rec.attribute12 := NVL ( x_child_lot_rec.attribute12 , l_parent_lot_attr.attribute12 ) ;
401 x_child_lot_rec.attribute13 := NVL ( x_child_lot_rec.attribute13 , l_parent_lot_attr.attribute13 ) ;
402 x_child_lot_rec.attribute14 := NVL ( x_child_lot_rec.attribute14 , l_parent_lot_attr.attribute14 ) ;
403 x_child_lot_rec.attribute15 := NVL ( x_child_lot_rec.attribute15 , l_parent_lot_attr.attribute15 ) ;
404 x_child_lot_rec.c_attribute1 := NVL ( x_child_lot_rec.c_attribute1 , l_parent_lot_attr.c_attribute1 ) ;
405 x_child_lot_rec.c_attribute2 := NVL ( x_child_lot_rec.c_attribute2 , l_parent_lot_attr.c_attribute2 ) ;
406 x_child_lot_rec.c_attribute3 := NVL ( x_child_lot_rec.c_attribute3 , l_parent_lot_attr.c_attribute3 ) ;
407 x_child_lot_rec.c_attribute4 := NVL ( x_child_lot_rec.c_attribute4 , l_parent_lot_attr.c_attribute4 ) ;
408 x_child_lot_rec.c_attribute5 := NVL ( x_child_lot_rec.c_attribute5 , l_parent_lot_attr.c_attribute5 ) ;
409 x_child_lot_rec.c_attribute6 := NVL ( x_child_lot_rec.c_attribute6 , l_parent_lot_attr.c_attribute6 ) ;
410 x_child_lot_rec.c_attribute7 := NVL ( x_child_lot_rec.c_attribute7 , l_parent_lot_attr.c_attribute7 ) ;
411 x_child_lot_rec.c_attribute8 := NVL ( x_child_lot_rec.c_attribute8 , l_parent_lot_attr.c_attribute8 ) ;
412 x_child_lot_rec.c_attribute9 := NVL ( x_child_lot_rec.c_attribute9 , l_parent_lot_attr.c_attribute9 ) ;
413 x_child_lot_rec.c_attribute10 := NVL ( x_child_lot_rec.c_attribute10 , l_parent_lot_attr.c_attribute10 ) ;
414 x_child_lot_rec.c_attribute11 := NVL ( x_child_lot_rec.c_attribute11 , l_parent_lot_attr.c_attribute11 ) ;
415 x_child_lot_rec.c_attribute12 := NVL ( x_child_lot_rec.c_attribute12 , l_parent_lot_attr.c_attribute12 ) ;
416 x_child_lot_rec.c_attribute13 := NVL ( x_child_lot_rec.c_attribute13 , l_parent_lot_attr.c_attribute13 ) ;
417 x_child_lot_rec.c_attribute14 := NVL ( x_child_lot_rec.c_attribute14 , l_parent_lot_attr.c_attribute14 ) ;
418 x_child_lot_rec.c_attribute15 := NVL ( x_child_lot_rec.c_attribute15 , l_parent_lot_attr.c_attribute15 ) ;
419 x_child_lot_rec.c_attribute16 := NVL ( x_child_lot_rec.c_attribute16 , l_parent_lot_attr.c_attribute16 ) ;
420 x_child_lot_rec.c_attribute17 := NVL ( x_child_lot_rec.c_attribute17 , l_parent_lot_attr.c_attribute17 ) ;
421 x_child_lot_rec.c_attribute18 := NVL ( x_child_lot_rec.c_attribute18 , l_parent_lot_attr.c_attribute18 ) ;
422 x_child_lot_rec.c_attribute19 := NVL ( x_child_lot_rec.c_attribute19 , l_parent_lot_attr.c_attribute19 ) ;
423 x_child_lot_rec.c_attribute20 := NVL ( x_child_lot_rec.c_attribute20 , l_parent_lot_attr.c_attribute20 ) ;
424 x_child_lot_rec.d_attribute1 := NVL ( x_child_lot_rec.d_attribute1 , l_parent_lot_attr.d_attribute1 ) ;
425 x_child_lot_rec.d_attribute2 := NVL ( x_child_lot_rec.d_attribute2 , l_parent_lot_attr.d_attribute2 ) ;
426 x_child_lot_rec.d_attribute3 := NVL ( x_child_lot_rec.d_attribute3 , l_parent_lot_attr.d_attribute3 ) ;
427 x_child_lot_rec.d_attribute4 := NVL ( x_child_lot_rec.d_attribute4 , l_parent_lot_attr.d_attribute4 ) ;
428 x_child_lot_rec.d_attribute5 := NVL ( x_child_lot_rec.d_attribute5 , l_parent_lot_attr.d_attribute5 ) ;
429 x_child_lot_rec.d_attribute6 := NVL ( x_child_lot_rec.d_attribute6 , l_parent_lot_attr.d_attribute6 ) ;
430 x_child_lot_rec.d_attribute7 := NVL ( x_child_lot_rec.d_attribute7 , l_parent_lot_attr.d_attribute7 ) ;
431 x_child_lot_rec.d_attribute8 := NVL ( x_child_lot_rec.d_attribute8 , l_parent_lot_attr.d_attribute8 ) ;
432 x_child_lot_rec.d_attribute9 := NVL ( x_child_lot_rec.d_attribute9 , l_parent_lot_attr.d_attribute9 ) ;
433 x_child_lot_rec.d_attribute10 := NVL ( x_child_lot_rec.d_attribute10 , l_parent_lot_attr.d_attribute10 ) ;
434 x_child_lot_rec.n_attribute1 := NVL ( x_child_lot_rec.n_attribute1 , l_parent_lot_attr.n_attribute1 ) ;
435 x_child_lot_rec.n_attribute2 := NVL ( x_child_lot_rec.n_attribute2 , l_parent_lot_attr.n_attribute2 ) ;
436 x_child_lot_rec.n_attribute3 := NVL ( x_child_lot_rec.n_attribute3 , l_parent_lot_attr.n_attribute3 ) ;
437 x_child_lot_rec.n_attribute4 := NVL ( x_child_lot_rec.n_attribute4 , l_parent_lot_attr.n_attribute4 ) ;
438 x_child_lot_rec.n_attribute5 := NVL ( x_child_lot_rec.n_attribute5 , l_parent_lot_attr.n_attribute5 ) ;
439 x_child_lot_rec.n_attribute6 := NVL ( x_child_lot_rec.n_attribute6 , l_parent_lot_attr.n_attribute6 ) ;
440 x_child_lot_rec.n_attribute7 := NVL ( x_child_lot_rec.n_attribute7 , l_parent_lot_attr.n_attribute7 ) ;
441 x_child_lot_rec.n_attribute8 := NVL ( x_child_lot_rec.n_attribute8 , l_parent_lot_attr.n_attribute8 ) ;
442 x_child_lot_rec.n_attribute9 := NVL ( x_child_lot_rec.n_attribute9 , l_parent_lot_attr.n_attribute9 ) ;
443 x_child_lot_rec.n_attribute10 := NVL ( x_child_lot_rec.n_attribute10 , l_parent_lot_attr.n_attribute10 ) ;
444 /*Bug#5523811 defaulting the values for fields curl_wrnkl_fold, description and vendor_id
445 from the parent lot*/
446 x_child_lot_rec.curl_wrinkle_fold := NVL ( x_child_lot_rec.curl_wrinkle_fold , l_parent_lot_attr.curl_wrinkle_fold ) ;
447 x_child_lot_rec.description := NVL ( x_child_lot_rec.description , l_parent_lot_attr.description ) ;
448 x_child_lot_rec.vendor_id := NVL ( x_child_lot_rec.vendor_id , l_parent_lot_attr.vendor_id ) ;
449 END IF ;
450 CLOSE c_parent_lot_attr ;
451
452 ELSE
453 l_caculate_flag := p_cacuLate_flag;
454
455 IF Nvl(l_caculate_flag,'T') = 'T' THEN --14192172,16604687
456
457 /* Default Missing Attributes from Item Master by calling Set_Msi_Default_Attr API */
458 Inv_Lot_Api_Pkg.Set_Msi_Default_Attr(
459 p_lot_rec => x_child_lot_rec
460 , x_return_status => l_return_status
461 , x_msg_count => l_msg_count
462 , x_msg_data => l_msg_data
463 ) ;
464
465 IF g_debug = 1 THEN
466 print_debug('Program Inv_Lot_Api_Pkg.Set_Msi_Default_Attr return ' || l_return_status, 9);
467 END IF;
468 IF l_return_status = fnd_api.g_ret_sts_error THEN
469 IF g_debug = 1 THEN
470 print_debug('Program Inv_Lot_Api_Pkg.Set_Msi_Default_Attr has failed with a user defined exception', 9);
471 END IF;
472 RAISE fnd_api.g_exc_error;
473 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
474 IF g_debug = 1 THEN
475 print_debug('Program Inv_Lot_Api_Pkg.Set_Msi_Default_Attr has failed with a Unexpected exception', 9);
476 END IF;
477 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
478 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_Lot_Api_Pkg.Set_Msi_Default_Attr');
479 FND_MSG_PUB.ADD;
480 RAISE fnd_api.g_exc_unexpected_error;
481 END IF;
482 END IF;
483
484 END IF ;
485
486 /* Validate naming convention for the child lot by calling Inv_lot_api_pub.VALIDATE_CHILD_LOT API */
487 IF (x_child_lot_rec.parent_lot_number IS NOT NULL) THEN
488 Inv_lot_api_pub.Validate_Child_Lot (
489 p_api_version => l_api_version
490 , p_init_msg_list => l_init_msg_list
491 , p_commit => l_commit
492 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
493 , p_organization_id => x_child_lot_rec.organization_id
494 , p_inventory_item_id => x_child_lot_rec.inventory_item_id
495 , p_parent_lot_number => x_child_lot_rec.parent_lot_number
496 , p_child_lot_number => x_child_lot_rec.lot_number
497 , x_return_status => l_return_status
498 /* Bug#5197732 passed the variables and types */
499 , x_msg_count => l_msg_count
500 , x_msg_data => l_msg_data
501 /*, x_msg_count => l_msg_data
502 , x_msg_data => l_msg_count */
503 ) ;
504
505 IF g_debug = 1 THEN
506 print_debug('Program Inv_lot_api_pub.VALIDATE_CHILD_LOT return ' || l_return_status, 9);
507 END IF;
508 IF l_return_status = fnd_api.g_ret_sts_error THEN
509 IF g_debug = 1 THEN
510 print_debug('Program Inv_lot_api_pub.VALIDATE_CHILD_LOT has failed with a user defined exception', 9);
511 END IF;
512 RAISE fnd_api.g_exc_error;
513 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
514 IF g_debug = 1 THEN
515 print_debug('Program Inv_lot_api_pub.VALIDATE_CHILD_LOT has failed with a Unexpected exception', 9);
516 END IF;
517 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
518 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pub.VALIDATE_CHILD_LOT');
519 FND_MSG_PUB.ADD;
520 RAISE fnd_api.g_exc_unexpected_error;
521 END IF;
522 END IF ; --parent lot check
523 /* Validate parent lot record x_parent_lot_rec by calling VALIDATE_LOT_ATTRIBUTES API */
524 INV_LOT_API_PKG.Validate_Lot_Attributes (
525 x_lot_rec => x_child_lot_rec
526 , p_source => l_source
527 , x_return_status => l_return_status
528 , x_msg_data => l_msg_data
529 , x_msg_count => l_msg_count
530 ) ;
531 IF g_debug = 1 THEN
532 print_debug('Program INV_LOT_API_PKG.VALIDATE_LOT_ATTRIBUTES return ' || l_return_status, 9);
533 END IF;
534 IF l_return_status = fnd_api.g_ret_sts_error THEN
535 IF g_debug = 1 THEN
536 print_debug('Program INV_LOT_API_PKG.VALIDATE_LOT_ATTRIBUTES has failed with a user defined exception', 9);
537 END IF;
538 RAISE fnd_api.g_exc_error;
539 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
540 IF g_debug = 1 THEN
541 print_debug('Program INV_LOT_API_PKG.VALIDATE_LOT_ATTRIBUTES has failed with a Unexpected exception', 9);
542 END IF;
543 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
544 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_API_PKG.VALIDATE_LOT_ATTRIBUTES');
545 FND_MSG_PUB.ADD;
546 RAISE fnd_api.g_exc_unexpected_error;
547 END IF;
548
549
550 print_debug('Program INV_LOT_API_PKG.Populate_Lot_Records Ends ' , 9);
551
552 EXCEPTION
553 WHEN NO_DATA_FOUND THEN
554 x_return_status := fnd_api.g_ret_sts_error;
555 ROLLBACK TO inv_pop_lot ;
556 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
557 if( x_msg_count > 1 ) then
558 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
559 end if;
560 print_debug('In No data found Populate_Lot_Records ' || SQLERRM, 9);
561 WHEN fnd_api.g_exc_error THEN
562 x_return_status := fnd_api.g_ret_sts_error;
563 ROLLBACK TO inv_pop_lot ;
564 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
565 if( x_msg_count > 1 ) then
566 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
567 end if;
568 print_debug('In g_exc_error Populate_Lot_Records ' || SQLERRM, 9);
569 WHEN fnd_api.g_exc_unexpected_error THEN
570 x_return_status := fnd_api.g_ret_sts_unexp_error;
571 ROLLBACK TO inv_pop_lot ;
572 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
573 if( x_msg_count > 1 ) then
574 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
575 end if;
576 print_debug('In g_exc_unexpected_error Populate_Lot_Records ' || SQLERRM, 9);
577 WHEN OTHERS THEN
578 x_return_status := fnd_api.g_ret_sts_unexp_error;
579 ROLLBACK TO inv_pop_lot ;
580 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
581 if( x_msg_count > 1 ) then
582 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
583 end if;
584 print_debug('In others Populate_Lot_Records ' || SQLERRM, 9);
585
586 END Populate_Lot_Records;
587
588
589
590
591 PROCEDURE Set_Msi_Default_Attr (
592 p_lot_rec IN OUT NOCOPY mtl_lot_numbers%ROWTYPE
593 , x_return_status OUT NOCOPY VARCHAR2
594 , x_msg_count OUT NOCOPY NUMBER
595 , x_msg_data OUT NOCOPY VARCHAR2
596 )
597 IS
598
599 CURSOR c_get_dft_attr ( cp_inventory_item_id NUMBER, cp_organization_id NUMBER ) IS
600 SELECT grade_control_flag
601 , default_grade
602 , shelf_life_code
603 , shelf_life_days
604 , expiration_action_code
605 , expiration_action_interval
606 , retest_interval
607 , maturity_days
608 , hold_days
609 , default_lot_status_id --bug10257769
610 FROM mtl_system_items_b
611 WHERE organization_id = cp_organization_id
612 AND inventory_item_id = cp_inventory_item_id;
613
614 -- nsinghi bug#5209065 rework START. If existing lot,
615 -- fetch the lot attributes and assign those, otherwise default from item.
616 CURSOR c_get_lot_attr ( cp_inventory_item_id NUMBER, cp_organization_id NUMBER, cp_lot_number VARCHAR2 ) IS
617 SELECT grade_code
618 , expiration_date
619 , expiration_action_code
620 , expiration_action_date
621 , origination_date
622 , origination_type
623 , retest_date
624 , maturity_date
625 , hold_date
626 FROM mtl_lot_numbers
627 WHERE organization_id = cp_organization_id
628 AND inventory_item_id = cp_inventory_item_id
629 AND lot_number = cp_lot_number;
630
631 l_get_lot_attr_rec c_get_lot_attr%ROWTYPE;
632 l_new_lot BOOLEAN;
633 -- nsinghi bug#5209065 rework END.
634
635 -- nsinghi bug 5209065 START
636 CURSOR cur_get_mti_rec (c_mti_hdr_id NUMBER) IS
637 SELECT * FROM mtl_transactions_interface
638 WHERE transaction_interface_id = c_mti_hdr_id;
639
640 CURSOR cur_get_mtli_rec (c_mtli_hdr_id ROWID) IS
641 SELECT * FROM mtl_transaction_lots_interface
642 WHERE ROWID = c_mtli_hdr_id;
643
644 CURSOR cur_get_mmtt_rec (c_mmtt_hdr_id NUMBER) IS
645 SELECT * FROM mtl_material_transactions_temp
646 WHERE transaction_header_id = c_mmtt_hdr_id;
647
648 CURSOR cur_get_mtlt_rec (c_mtlt_hdr_id ROWID) IS
649 SELECT * FROM mtl_transaction_lots_temp
650 WHERE ROWID = c_mtlt_hdr_id;
651
652 l_mti_txn_id NUMBER;
653 l_mmtt_txn_id NUMBER;
654 l_mtli_txn_id ROWID;
655 l_mtlt_txn_id ROWID;
656 l_mti_txn_rec MTL_TRANSACTIONS_INTERFACE%ROWTYPE;
657 l_mtli_txn_rec MTL_TRANSACTION_LOTS_INTERFACE%ROWTYPE;
658 l_mmtt_txn_rec MTL_MATERIAL_TRANSACTIONS_TEMP%ROWTYPE;
659 l_mtlt_txn_rec MTL_TRANSACTION_LOTS_TEMP%ROWTYPE;
660 l_lot_expiration_date DATE;
661 -- nsinghi bug 5209065 END
662
663 l_get_dft_attr_rec c_get_dft_attr%ROWTYPE;
664
665 l_return_status VARCHAR2(1);
666 l_msg_count NUMBER;
667 l_msg_data VARCHAR2(3000);
668 l_lot_cont BOOLEAN ;
669 l_child_lot_cont BOOLEAN ;
670
671 l_allow_different_status NUMBER; --bug10257769
672
673 BEGIN
674
675 /******************* START Item validation ********************/
676
677 l_lot_cont := FALSE ;
678 l_child_lot_cont := FALSE ;
679
680 check_item_attributes
681 (
682 x_return_status => l_return_status
683 , x_msg_count => l_msg_count
684 , x_msg_data => l_msg_data
685 , x_lot_cont => l_lot_cont
686 , x_child_lot_cont => l_child_lot_cont
687 , p_inventory_item_id => p_lot_rec.inventory_item_id
688 , p_organization_id => p_lot_rec.organization_id
689 ) ;
690
691 IF g_debug = 1 THEN
692 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
693 END IF;
694
695 IF l_return_status = fnd_api.g_ret_sts_error THEN
696 IF g_debug = 1 THEN
697 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
698 END IF;
699 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
700 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
701 FND_MSG_PUB.ADD;
702 RAISE fnd_api.g_exc_error;
703 END IF;
704
705 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
706 IF g_debug = 1 THEN
707 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
708 END IF;
709 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
710 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
711 fnd_msg_pub.ADD;
712 RAISE fnd_api.g_exc_unexpected_error;
713 END IF;
714
715 IF (l_lot_cont = FALSE) THEN
716 IF g_debug = 1 THEN
717 print_debug(' Item is not lot controlled ', 9);
718 END IF;
719 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
720 fnd_msg_pub.ADD;
721 x_return_status := fnd_api.g_ret_sts_error;
722 RAISE fnd_api.g_exc_error;
723 END IF ;
724
725 IF (l_child_lot_cont = FALSE AND p_lot_rec.parent_lot_number IS NOT NULL) THEN
726
727 IF g_debug = 1 THEN
728 print_debug(' Item is not Child lot controlled ', 9);
729 END IF;
730 fnd_message.set_name('INV', 'INV_ITEM_CLOT_DISABLE_EXP');
731 fnd_msg_pub.ADD;
732 x_return_status := fnd_api.g_ret_sts_error;
733 RAISE fnd_api.g_exc_error;
734 END IF ;
735
736 /******************* End Item validation ********************/
737
738 -- nsinghi bug#5209065 rework START.
739 l_new_lot := FALSE;
740 OPEN c_get_lot_attr(p_lot_rec.inventory_item_id,p_lot_rec.organization_id, p_lot_rec.lot_number);
741 FETCH c_get_lot_attr INTO l_get_lot_attr_rec;
742 IF c_get_lot_attr%NOTFOUND THEN
743 l_new_lot := TRUE;
744 END IF;
745 CLOSE c_get_lot_attr;
746 IF (NOT l_new_lot) THEN
747 IF l_get_lot_attr_rec.grade_code IS NOT NULL AND p_lot_rec.grade_code IS NULL THEN
748 p_lot_rec.grade_code := l_get_lot_attr_rec.grade_code;
749 END IF;
750 IF l_get_lot_attr_rec.expiration_date IS NOT NULL AND p_lot_rec.expiration_date IS NULL THEN
751 p_lot_rec.expiration_date := l_get_lot_attr_rec.expiration_date;
752 END IF;
753 IF l_get_lot_attr_rec.expiration_action_code IS NOT NULL AND p_lot_rec.expiration_action_code IS NULL THEN
754 p_lot_rec.expiration_action_code := l_get_lot_attr_rec.expiration_action_code;
755 END IF;
756 IF l_get_lot_attr_rec.expiration_action_date IS NOT NULL AND p_lot_rec.expiration_action_date IS NULL THEN
757 p_lot_rec.expiration_action_date := l_get_lot_attr_rec.expiration_action_date;
758 END IF;
759 IF l_get_lot_attr_rec.origination_date IS NOT NULL AND p_lot_rec.origination_date IS NULL THEN
760 p_lot_rec.origination_date := l_get_lot_attr_rec.origination_date;
761 END IF;
762 IF l_get_lot_attr_rec.origination_type IS NOT NULL AND p_lot_rec.origination_type IS NULL THEN
763 p_lot_rec.origination_type := l_get_lot_attr_rec.origination_type;
764 END IF;
765 IF l_get_lot_attr_rec.retest_date IS NOT NULL AND p_lot_rec.retest_date IS NULL THEN
766 p_lot_rec.retest_date := l_get_lot_attr_rec.retest_date;
767 END IF;
768 IF l_get_lot_attr_rec.maturity_date IS NOT NULL AND p_lot_rec.maturity_date IS NULL THEN
769 p_lot_rec.maturity_date := l_get_lot_attr_rec.maturity_date;
770 END IF;
771 IF l_get_lot_attr_rec.hold_date IS NOT NULL AND p_lot_rec.hold_date IS NULL THEN
772 p_lot_rec.hold_date := l_get_lot_attr_rec.hold_date;
773 END IF;
774 END IF;
775 -- nsinghi bug#5209065 rework END.
776
777 /*Get default information from Mtl_System_Item */
778 OPEN c_get_dft_attr(p_lot_rec.inventory_item_id,p_lot_rec.organization_id);
779 FETCH c_get_dft_attr INTO l_get_dft_attr_rec;
780 CLOSE c_get_dft_attr;
781
782 /* Grade */
783 IF l_get_dft_attr_rec.grade_control_flag = 'Y' AND p_lot_rec.grade_code IS NULL THEN
784 p_lot_rec.grade_code := l_get_dft_attr_rec.default_grade;
785 END IF;
786 /* Origination Type */
787 IF p_lot_rec.origination_type IS NULL THEN
788 p_lot_rec.origination_type := 6; -- Origination Type OTHER = 6
789 END IF;
790
791 /* Origination Date */
792 IF p_lot_rec.origination_date IS NOT NULL THEN
793
794 /* Expiration Date */
795 IF p_lot_rec.expiration_date IS NULL AND l_get_dft_attr_rec.shelf_life_code = 2 THEN -- Item shelf life days
796 -- nsinghi bug 5209065 START. This API defaults the expiration date. If MMTT or MTI table has data
797 -- and header id is set, we call the custom lot expiration API to get expiration date.
798 l_mti_txn_id := inv_calculate_exp_date.get_txn_id (p_table => 1);
799 l_mtli_txn_id := inv_calculate_exp_date.get_lot_txn_id (p_table => 1);
800
801 l_mmtt_txn_id := inv_calculate_exp_date.get_txn_id (p_table => 2);
802 l_mtlt_txn_id := inv_calculate_exp_date.get_lot_txn_id (p_table => 2);
803
804 IF l_mti_txn_id <> -1 AND l_mtli_txn_id <> '-1' THEN
805
806 OPEN cur_get_mti_rec (l_mti_txn_id);
807 FETCH cur_get_mti_rec INTO l_mti_txn_rec;
808 CLOSE cur_get_mti_rec;
809
810 OPEN cur_get_mtli_rec (l_mtli_txn_id);
811 FETCH cur_get_mtli_rec INTO l_mtli_txn_rec;
812 CLOSE cur_get_mtli_rec;
813
814 inv_calculate_exp_date.reset_header_id;
815
816 inv_calculate_exp_date.get_lot_expiration_date(
817 p_mtli_lot_rec => l_mtli_txn_rec
818 ,p_mti_trx_rec => l_mti_txn_rec
819 ,p_mtlt_lot_rec => l_mtlt_txn_rec
820 ,p_mmtt_trx_rec => l_mmtt_txn_rec
821 ,p_table => 1
822 ,x_lot_expiration_date => l_lot_expiration_date
823 ,x_return_status => l_return_status);
824
825 IF l_return_status <> fnd_api.g_ret_sts_success THEN
826 IF g_debug = 1 THEN
827 print_debug('Program inv_calculate_exp_date.get_lot_expiration_date has failed with a Unexpected exception', 9);
828 END IF;
829 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
830 FND_MESSAGE.SET_TOKEN('PROG_NAME','inv_calculate_exp_date.get_lot_expiration_date');
831 fnd_msg_pub.ADD;
832 RAISE fnd_api.g_exc_unexpected_error;
833 END IF;
834 IF g_debug = 1 THEN
835 print_debug('l_lot_expiration_date (1) '||l_lot_expiration_date, 9);
836 END IF;
837 p_lot_rec.expiration_date := l_lot_expiration_date;
838
839 ELSIF l_mmtt_txn_id <> -1 AND l_mtlt_txn_id <> '-1' THEN
840
841 OPEN cur_get_mmtt_rec (l_mmtt_txn_id);
842 FETCH cur_get_mmtt_rec INTO l_mmtt_txn_rec;
843 CLOSE cur_get_mmtt_rec;
844
845 OPEN cur_get_mtlt_rec (l_mtlt_txn_id);
846 FETCH cur_get_mtlt_rec INTO l_mtlt_txn_rec;
847 CLOSE cur_get_mtlt_rec;
848
849 inv_calculate_exp_date.get_lot_expiration_date(
850 p_mtli_lot_rec => l_mtli_txn_rec
851 ,p_mti_trx_rec => l_mti_txn_rec
852 ,p_mtlt_lot_rec => l_mtlt_txn_rec
853 ,p_mmtt_trx_rec => l_mmtt_txn_rec
854 ,p_table => 2
855 ,x_lot_expiration_date => l_lot_expiration_date
856 ,x_return_status => l_return_status);
857
858 IF l_return_status <> fnd_api.g_ret_sts_success THEN
859 IF g_debug = 1 THEN
860 print_debug('Program inv_calculate_exp_date.get_lot_expiration_date has failed with a Unexpected exception', 9);
861 END IF;
862 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
863 FND_MESSAGE.SET_TOKEN('PROG_NAME','inv_calculate_exp_date.get_lot_expiration_date');
864 fnd_msg_pub.ADD;
865 RAISE fnd_api.g_exc_unexpected_error;
866 END IF;
867 IF g_debug = 1 THEN
868 print_debug('l_lot_expiration_date (2) '||l_lot_expiration_date, 9);
869 END IF;
870 p_lot_rec.expiration_date := l_lot_expiration_date;
871
872 ELSE
873 p_lot_rec.expiration_date := p_lot_rec.origination_date + l_get_dft_attr_rec.shelf_life_days;
874 END IF;
875 -- nsinghi bug 5209065 END
876 END IF;
877
878 /* Retest Date */
879 IF p_lot_rec.retest_date IS NULL THEN
880 p_lot_rec.retest_date := p_lot_rec.origination_date + l_get_dft_attr_rec.retest_interval;
881 END IF;
882
883 /* Shelf Life Code */
884 IF NVL (l_get_dft_attr_rec.shelf_life_code, -1) <> 1 THEN -- No shelf life control
885
886 /* Expiration Action Date */
887 IF p_lot_rec.expiration_action_date IS NULL THEN
888 p_lot_rec.expiration_action_date := p_lot_rec.expiration_date + l_get_dft_attr_rec.expiration_action_interval ;
889 END IF;
890
891 /* Expiration Action Code */
892 IF p_lot_rec.expiration_action_code IS NULL THEN
893 p_lot_rec.expiration_action_code := l_get_dft_attr_rec.expiration_action_code ;
894 END IF;
895
896 END IF; /* Shelf Life Code */
897
898 /* Hold Date */
899 IF p_lot_rec.hold_date IS NULL THEN
900 p_lot_rec.hold_date := p_lot_rec.origination_date + l_get_dft_attr_rec.hold_days;
901 END IF;
902
903 /* Maturity Date */
904 IF p_lot_rec.maturity_date IS NULL THEN
905 p_lot_rec.maturity_date := p_lot_rec.origination_date + l_get_dft_attr_rec.maturity_days;
906 END IF;
907
908 END IF ; /* Origination Date */
909
910 --bug10257769 if 'Allow Different Lot Status' set as Yes, lot status will be default from the destination
911 select nvl(allow_different_status,2)
912 into l_allow_different_status
913 from mtl_parameters
914 where organization_id = p_lot_rec.organization_id;
915 print_debug('allow different status: '||l_allow_different_status,9);
916
917 if l_allow_different_status =1 then
918 p_lot_rec.status_id := l_get_dft_attr_rec.default_lot_status_id;
919 print_debug('lot status '||p_lot_rec.status_id);
920 end if;
921 --end10257769
922
923 EXCEPTION
924 WHEN NO_DATA_FOUND THEN
925 x_return_status := fnd_api.g_ret_sts_error;
926 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
927 if( x_msg_count > 1 ) then
928 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
929 end if;
930 print_debug('In Set_Msi_Default_Attr, No data found ' || SQLERRM, 9);
931 WHEN fnd_api.g_exc_error THEN
932 x_return_status := fnd_api.g_ret_sts_error;
933 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
934 if( x_msg_count > 1 ) then
935 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
936 end if;
937 print_debug('In Set_Msi_Default_Attr, g_exc_error ' || SQLERRM, 9);
938 WHEN fnd_api.g_exc_unexpected_error THEN
939 x_return_status := fnd_api.g_ret_sts_unexp_error;
940 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
941 if( x_msg_count > 1 ) then
942 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
943 end if;
944 print_debug('In Set_Msi_Default_Attr, g_exc_unexpected_error ' || SQLERRM, 9);
945 WHEN OTHERS THEN
946 x_return_status := fnd_api.g_ret_sts_unexp_error;
947 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
948 if( x_msg_count > 1 ) then
949 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
950 end if;
951 print_debug('In Set_Msi_Default_Attr, Others ' || SQLERRM, 9);
952
953 END Set_Msi_Default_Attr ;
954
955
956 PROCEDURE Validate_Lot_Attributes(
957 x_return_status OUT NOCOPY VARCHAR2
958 , x_msg_count OUT NOCOPY NUMBER
959 , x_msg_data OUT NOCOPY VARCHAR2
960 , x_lot_rec IN OUT NOCOPY Mtl_Lot_Numbers%ROWTYPE
961 , p_source IN NUMBER
962 ) IS
963
964 /* Shelf life code constants */
965 no_shelf_life_control CONSTANT NUMBER := 1;
966 item_shelf_life_days CONSTANT NUMBER := 2;
967 user_defined_exp_date CONSTANT NUMBER := 4;
968 l_mtl_gen_obj_no NUMBER ;
969
970 /*Program variables declaration */
971 l_lot_control_code mtl_system_items_b.lot_control_code%TYPE;
972 l_chk_lot_uniqueness BOOLEAN;
973 l_shelf_life_days mtl_system_items.shelf_life_days%TYPE;
974 l_shelf_life_code mtl_system_items.shelf_life_code%TYPE;
975 l_expiration_date mtl_lot_numbers.expiration_date%TYPE;
976 l_wms_installed VARCHAR2(5);
977 l_lot_number_uniqueness NUMBER;
978 l_return_status VARCHAR2(1);
979 l_msg_count NUMBER;
980 l_msg_data VARCHAR2(3000);
981 l_status NUMBER;
982 l_lot_attribute_category mtl_lot_numbers.lot_attribute_category%TYPE;
983 l_inv_attributes_tbl inv_lot_api_pub.char_tbl;
984 l_c_attributes_tbl inv_lot_api_pub.char_tbl;
985 l_n_attributes_tbl inv_lot_api_pub.number_tbl;
986 l_d_attributes_tbl inv_lot_api_pub.date_tbl;
987
988 /* Index variables for looping through the input tables*/
989 l_attr_index NUMBER;
990 l_c_attr_index NUMBER;
991 l_n_attr_index NUMBER;
992 l_d_attr_index NUMBER;
993
994 p_attributes_tbl inv_lot_api_pub.char_tbl ;
995 p_c_attributes_tbl inv_lot_api_pub.char_tbl ;
996 p_n_attributes_tbl inv_lot_api_pub.number_tbl ;
997 p_d_attributes_tbl inv_lot_api_pub.date_tbl ;
998
999 p_inventory_item_id mtl_lot_numbers.inventory_item_id%TYPE ;
1000 p_organization_id mtl_lot_numbers.organization_id%TYPE ;
1001 p_lot_number mtl_lot_numbers.lot_number%TYPE ;
1002 p_expiration_date mtl_lot_numbers.expiration_date%TYPE ;
1003 p_disable_flag mtl_lot_numbers.disable_flag%TYPE ;
1004 p_attribute_category mtl_lot_numbers.attribute_category%TYPE ;
1005 p_lot_attribute_category mtl_lot_numbers.lot_attribute_category%TYPE ;
1006 p_grade_code mtl_lot_numbers.grade_code%TYPE ;
1007 p_origination_date mtl_lot_numbers.origination_date%TYPE ;
1008 p_date_code mtl_lot_numbers.date_code%TYPE ;
1009 p_status_id mtl_lot_numbers.status_id%TYPE ;
1010 p_change_date mtl_lot_numbers.change_date%TYPE ;
1011 p_age mtl_lot_numbers.age%TYPE ;
1012 p_retest_date mtl_lot_numbers.retest_date%TYPE ;
1013 p_maturity_date mtl_lot_numbers.maturity_date%TYPE ;
1014 p_item_size mtl_lot_numbers.item_size%TYPE ;
1015 p_color mtl_lot_numbers.color%TYPE ;
1016 p_volume mtl_lot_numbers.volume%TYPE ;
1017 p_volume_uom mtl_lot_numbers.volume_uom%TYPE ;
1018 p_place_of_origin mtl_lot_numbers.place_of_origin%TYPE ;
1019 p_best_by_date mtl_lot_numbers.best_by_date%TYPE ;
1020 p_length mtl_lot_numbers.length%TYPE ;
1021 p_length_uom mtl_lot_numbers.length_uom%TYPE ;
1022 p_recycled_content mtl_lot_numbers.recycled_content%TYPE ;
1023 p_thickness mtl_lot_numbers.thickness%TYPE ;
1024 p_thickness_uom mtl_lot_numbers.thickness_uom%TYPE ;
1025 p_width mtl_lot_numbers.width%TYPE ;
1026 p_width_uom mtl_lot_numbers.width_uom%TYPE ;
1027 p_territory_code mtl_lot_numbers.territory_code%TYPE ;
1028 p_supplier_lot_number mtl_lot_numbers.supplier_lot_number%TYPE ;
1029 p_vendor_name mtl_lot_numbers.vendor_name%TYPE ;
1030 p_parent_lot_number mtl_lot_numbers.parent_lot_number%TYPE ;
1031 p_origination_type mtl_lot_numbers.origination_type%TYPE ;
1032 p_expiration_action_code mtl_lot_numbers.expiration_action_code%TYPE ;
1033 p_expiration_action_date mtl_lot_numbers.expiration_action_date%TYPE ;
1034 p_hold_date mtl_lot_numbers.hold_date%TYPE ;
1035
1036 l_lot_cont BOOLEAN ;
1037 l_child_lot_cont BOOLEAN ;
1038 l_lot_status_flag VARCHAR2(1) ;
1039 l_def_lot_status NUMBER ;
1040
1041 BEGIN
1042 SAVEPOINT inv_val_lot;
1043 x_return_status := fnd_api.g_ret_sts_success;
1044
1045 p_inventory_item_id := x_lot_rec.inventory_item_id ;
1046 p_organization_id := x_lot_rec.organization_id ;
1047 p_lot_number := x_lot_rec.lot_number ;
1048 p_parent_lot_number := x_lot_rec.parent_lot_number ;
1049 p_grade_code := x_lot_rec.grade_code ;
1050 p_expiration_date := x_lot_rec.expiration_date ;
1051 p_origination_type := x_lot_rec.origination_type ;
1052 p_origination_date := x_lot_rec.origination_date ;
1053 p_expiration_action_code := x_lot_rec.expiration_action_code ;
1054 p_expiration_action_date := x_lot_rec.expiration_action_date ;
1055 p_hold_date := x_lot_rec.hold_date ;
1056 p_retest_date := x_lot_rec.retest_date ;
1057 p_maturity_date := x_lot_rec.maturity_date ;
1058 p_disable_flag := x_lot_rec.disable_flag ;
1059 p_attribute_category := x_lot_rec.attribute_category ;
1060 p_lot_attribute_category := x_lot_rec.lot_attribute_category ;
1061 p_date_code := x_lot_rec.date_code ;
1062 p_status_id := x_lot_rec.status_id ;
1063 p_change_date := x_lot_rec.change_date ;
1064 p_age := x_lot_rec.age ;
1065 p_item_size := x_lot_rec.item_size ;
1066 p_color := x_lot_rec.color ;
1067 p_volume := x_lot_rec.volume ;
1068 p_volume_uom := x_lot_rec.volume_uom ;
1069 p_place_of_origin := x_lot_rec.place_of_origin ;
1070 p_best_by_date := x_lot_rec.best_by_date ;
1071 p_length := x_lot_rec.length ;
1072 p_length_uom := x_lot_rec.length_uom ;
1073 p_recycled_content := x_lot_rec.recycled_content ;
1074 p_thickness := x_lot_rec.thickness ;
1075 p_thickness_uom := x_lot_rec.thickness_uom ;
1076 p_width := x_lot_rec.width ;
1077 p_width_uom := x_lot_rec.width_uom ;
1078 p_territory_code := x_lot_rec.territory_code ;
1079 p_supplier_lot_number := x_lot_rec.supplier_lot_number ;
1080 p_vendor_name := x_lot_rec.vendor_name ;
1081 p_attributes_tbl(1) := x_lot_rec.attribute1 ;
1082 p_attributes_tbl(2) := x_lot_rec.attribute2 ;
1083 p_attributes_tbl(3) := x_lot_rec.attribute3 ;
1084 p_attributes_tbl(4) := x_lot_rec.attribute4 ;
1085 p_attributes_tbl(5) := x_lot_rec.attribute5 ;
1086 p_attributes_tbl(6) := x_lot_rec.attribute6 ;
1087 p_attributes_tbl(7) := x_lot_rec.attribute7 ;
1088 p_attributes_tbl(8) := x_lot_rec.attribute8 ;
1089 p_attributes_tbl(9) := x_lot_rec.attribute9 ;
1090 p_attributes_tbl(10) := x_lot_rec.attribute10 ;
1091 p_attributes_tbl(11) := x_lot_rec.attribute11 ;
1092 p_attributes_tbl(12) := x_lot_rec.attribute12 ;
1093 p_attributes_tbl(13) := x_lot_rec.attribute13 ;
1094 p_attributes_tbl(14) := x_lot_rec.attribute14 ;
1095 p_attributes_tbl(15) := x_lot_rec.attribute15 ;
1096 p_c_attributes_tbl(1) := x_lot_rec.c_attribute1 ;
1097 p_c_attributes_tbl(2) := x_lot_rec.c_attribute2 ;
1098 p_c_attributes_tbl(3) := x_lot_rec.c_attribute3 ;
1099 p_c_attributes_tbl(4) := x_lot_rec.c_attribute4 ;
1100 p_c_attributes_tbl(5) := x_lot_rec.c_attribute5 ;
1101 p_c_attributes_tbl(6) := x_lot_rec.c_attribute6 ;
1102 p_c_attributes_tbl(7) := x_lot_rec.c_attribute7 ;
1103 p_c_attributes_tbl(8) := x_lot_rec.c_attribute8 ;
1104 p_c_attributes_tbl(9) := x_lot_rec.c_attribute9 ;
1105 p_c_attributes_tbl(10) := x_lot_rec.c_attribute10 ;
1106 p_c_attributes_tbl(11) := x_lot_rec.c_attribute11 ;
1107 p_c_attributes_tbl(12) := x_lot_rec.c_attribute12 ;
1108 p_c_attributes_tbl(13) := x_lot_rec.c_attribute13 ;
1109 p_c_attributes_tbl(14) := x_lot_rec.c_attribute14 ;
1110 p_c_attributes_tbl(15) := x_lot_rec.c_attribute15 ;
1111 p_c_attributes_tbl(16) := x_lot_rec.c_attribute16 ;
1112 p_c_attributes_tbl(17) := x_lot_rec.c_attribute17 ;
1113 p_c_attributes_tbl(18) := x_lot_rec.c_attribute18 ;
1114 p_c_attributes_tbl(19) := x_lot_rec.c_attribute19 ;
1115 p_c_attributes_tbl(20) := x_lot_rec.c_attribute20 ;
1116 p_n_attributes_tbl(1) := x_lot_rec.n_attribute1 ;
1117 p_n_attributes_tbl(2) := x_lot_rec.n_attribute2 ;
1118 p_n_attributes_tbl(3) := x_lot_rec.n_attribute3 ;
1119 p_n_attributes_tbl(4) := x_lot_rec.n_attribute4 ;
1120 p_n_attributes_tbl(5) := x_lot_rec.n_attribute5 ;
1121 p_n_attributes_tbl(6) := x_lot_rec.n_attribute6 ;
1122 p_n_attributes_tbl(7) := x_lot_rec.n_attribute7 ;
1123 p_n_attributes_tbl(8) := x_lot_rec.n_attribute8 ;
1124 p_n_attributes_tbl(9) := x_lot_rec.n_attribute9 ;
1125 p_n_attributes_tbl(10) := x_lot_rec.n_attribute10 ;
1126 p_d_attributes_tbl(1) := x_lot_rec.d_attribute1 ;
1127 p_d_attributes_tbl(2) := x_lot_rec.d_attribute2 ;
1128 p_d_attributes_tbl(3) := x_lot_rec.d_attribute3 ;
1129 p_d_attributes_tbl(4) := x_lot_rec.d_attribute4 ;
1130 p_d_attributes_tbl(5) := x_lot_rec.d_attribute5 ;
1131 p_d_attributes_tbl(6) := x_lot_rec.d_attribute6 ;
1132 p_d_attributes_tbl(7) := x_lot_rec.d_attribute7 ;
1133 p_d_attributes_tbl(8) := x_lot_rec.d_attribute8 ;
1134 p_d_attributes_tbl(9) := x_lot_rec.d_attribute9 ;
1135 p_d_attributes_tbl(10) := x_lot_rec.d_attribute10 ;
1136
1137 -- Populate the set of local variables as mentioned in the previous point.
1138
1139 IF g_debug = 1 THEN
1140 print_debug(p_err_msg=>'Start Validate_Lot_Attributes', p_level=>9);
1141 print_debug(p_err_msg => 'The value of the input parametsrs are :', p_level => 9);
1142 print_debug(p_err_msg => 'The value of the INVENTORY_ITEM_ID : ' || p_inventory_item_id, p_level => 9);
1143 print_debug(p_err_msg => 'The value of ORGANIZATION_ID :' || p_organization_id, p_level => 9);
1144 print_debug(p_err_msg => 'The value of LOT_NUMBER :' || p_lot_number, p_level => 9);
1145 print_debug(p_err_msg => 'The value of the EXPIRATION_DATE :' || p_expiration_date, p_level => 9);
1146 print_debug(p_err_msg => 'The value of the DISABLE_FLAG :' || p_disable_flag, p_level => 9);
1147 print_debug(p_err_msg => 'The value of the ATTRIBUTE_CATEGORY :' || p_attribute_category, p_level => 9);
1148 print_debug(p_err_msg => 'The value of the LOT_ATTRIBUTE_CATEGORY :' || p_lot_attribute_category, p_level => 9);
1149 print_debug(p_err_msg => 'The value of the GRADE_CODE :' || p_grade_code, p_level => 9);
1150 print_debug(p_err_msg => 'The value of the ORIGINATION_DATE :' || p_origination_date, p_level => 9);
1151 print_debug(p_err_msg => 'The value of the ORIGINATION_TYPE :' || p_origination_type, p_level => 9);
1152 print_debug(p_err_msg => 'The value of the DATE_CODE :' || p_date_code, p_level => 9);
1153 print_debug(p_err_msg => 'The value of the STATUS_ID :' || p_status_id, p_level => 9);
1154 print_debug(p_err_msg => 'The value of the CHANGE_DATE :' || p_change_date, p_level => 9);
1155 print_debug(p_err_msg => 'The value of the AGE :' || p_age, p_level => 9);
1156 print_debug(p_err_msg => 'The value of the RETEST_DATE :' || p_retest_date, p_level => 9);
1157 print_debug(p_err_msg => 'The value of the MATURITY_DATE :' || p_maturity_date, p_level => 9);
1158 print_debug(p_err_msg => 'The value of the ITEM_SIZE :' || p_item_size, p_level => 9);
1159 print_debug(p_err_msg => 'The value of COLOR :' || p_color, p_level => 9);
1160 print_debug(p_err_msg => 'The value of VOLUME :' || p_volume, p_level => 9);
1161 print_debug(p_err_msg => 'The value of VOLUME_UOM :' || p_volume_uom, p_level => 9);
1162 print_debug(p_err_msg => 'The value of PLACE_OF_ORIGIN :' || p_place_of_origin, p_level => 9);
1163 print_debug(p_err_msg => 'The value of BEST_BY_DATE :' || p_best_by_date, p_level => 9);
1164 print_debug(p_err_msg => 'The value of LENGTH :' || p_length, p_level => 9);
1165 print_debug(p_err_msg => 'The value of LENGTH_UOM:' || p_length_uom, p_level => 9);
1166 print_debug(p_err_msg => 'The value of RECYCLED_CONTENT :' || p_recycled_content, p_level => 9);
1167 print_debug(p_err_msg => 'The value of THICKNESS :' || p_thickness, p_level => 9);
1168 print_debug(p_err_msg => 'The value of THICKNESS_UOM :' || p_thickness_uom, p_level => 9);
1169 print_debug(p_err_msg => 'The value of WIDTH :' || p_width, p_level => 9);
1170 print_debug(p_err_msg => 'The value of WIDTH_UOM :' || p_width_uom, p_level => 9);
1171 print_debug(p_err_msg => 'The value of Territory Code :' || p_territory_code, p_level => 9);
1172 print_debug(p_err_msg => 'The value of VENDOR_NAME :' || p_vendor_name, p_level => 9);
1173 print_debug(p_err_msg => 'The value of SUPPLIER_LOT_NUMBER :' || p_supplier_lot_number, p_level => 9);
1174 print_debug(p_err_msg => 'The value of P_SOURCE :' || p_source, p_level => 9);
1175 END IF;
1176
1177
1178 /******************* START Item validation ********************/
1179
1180 l_lot_cont := FALSE ;
1181 l_child_lot_cont := FALSE ;
1182
1183 check_item_attributes
1184 (
1185 x_return_status => l_return_status
1186 , x_msg_count => l_msg_count
1187 , x_msg_data => l_msg_data
1188 , x_lot_cont => l_lot_cont
1189 , x_child_lot_cont => l_child_lot_cont
1190 , p_inventory_item_id => p_inventory_item_id
1191 , p_organization_id => p_organization_id
1192 ) ;
1193
1194 IF g_debug = 1 THEN
1195 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
1196 END IF;
1197
1198 IF l_return_status = fnd_api.g_ret_sts_error THEN
1199 IF g_debug = 1 THEN
1200 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
1201 END IF;
1202 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
1203 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
1204 FND_MSG_PUB.ADD;
1205 RAISE fnd_api.g_exc_error;
1206 END IF;
1207
1208 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1209 IF g_debug = 1 THEN
1210 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
1211 END IF;
1212 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1213 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
1214 fnd_msg_pub.ADD;
1215 RAISE fnd_api.g_exc_unexpected_error;
1216 END IF;
1217
1218 IF (l_lot_cont = FALSE) THEN
1219 IF g_debug = 1 THEN
1220 print_debug(' Item is not lot controlled ', 9);
1221 END IF;
1222 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
1223 fnd_msg_pub.ADD;
1224 x_return_status := fnd_api.g_ret_sts_error;
1225 RAISE fnd_api.g_exc_error;
1226 END IF ;
1227
1228 IF (l_child_lot_cont = FALSE AND p_parent_lot_number IS NOT NULL) THEN
1229
1230 IF g_debug = 1 THEN
1231 print_debug(' Item is not Child lot controlled ', 9);
1232 END IF;
1233 fnd_message.set_name('INV', 'INV_ITEM_CLOT_DISABLE_EXP');
1234 fnd_msg_pub.ADD;
1235 x_return_status := fnd_api.g_ret_sts_error;
1236 RAISE fnd_api.g_exc_error;
1237 END IF ;
1238 /******************* End Item validation ********************/
1239
1240
1241 l_expiration_date := p_expiration_date ;
1242
1243 /*We should not be validating for expiration date for isfm as Lot is not created*/
1244 IF p_source NOT IN(osfm_form_no_validate, osfm_open_interface, osfm_form_validate) THEN
1245 /* Check for the shelf life code for the inventory item passed.One message to be added */
1246 BEGIN
1247 SELECT shelf_life_days
1248 , shelf_life_code
1249 INTO l_shelf_life_days
1250 , l_shelf_life_code
1251 FROM mtl_system_items
1252 WHERE inventory_item_id = p_inventory_item_id
1253 AND organization_id = p_organization_id;
1254 EXCEPTION
1255 WHEN NO_DATA_FOUND THEN
1256 fnd_message.set_name('INV', 'INV_SHELF_LIFE_ERROR');
1257 fnd_message.set_token('INV', 'ITEM');
1258 fnd_msg_pub.ADD;
1259
1260 IF g_debug = 1 THEN
1261 print_debug('Unable to fetch shelf life code for the inventory item passed', 9);
1262 END IF;
1263
1264 RAISE NO_DATA_FOUND;
1265 END;
1266
1267 IF g_debug = 1 THEN
1268 print_debug('shelf life obtained successfully ', 9);
1269 END IF;
1270
1271 /* We have to derive the EXPIRATION_DATE based on the items shelf life code.
1272 Shelf life code can have the following values
1273 1 -No control
1274 2- Shelf Life Days
1275 4-User Defined.
1276 Two MESSAGE TO BE ADDED
1277 */
1278
1279 IF l_shelf_life_code = item_shelf_life_days THEN
1280 IF g_debug = 1 THEN
1281 print_debug('Shelf_life code is of type ITEM_SHELF_LIFE_DAYS', 9);
1282 END IF;
1283
1284 SELECT SYSDATE + l_shelf_life_days
1285 INTO l_expiration_date
1286 FROM DUAL;
1287
1288 IF TRUNC(l_expiration_date) <> trunc(p_expiration_date) THEN
1289 fnd_message.set_name('INV', 'INV_EXP_DATE_NOT_CONSIDER');
1290 fnd_msg_pub.ADD;
1291 IF g_debug = 1 THEN
1292 print_debug('Expiration will not be considered for shelf_life code of type ITEM_SHELF_LIFE_DAYS', 9);
1293 END IF;
1294 RAISE fnd_api.g_exc_error;
1295 END IF;
1296
1297
1298 ELSIF l_shelf_life_code = user_defined_exp_date THEN
1299 IF g_debug = 1 THEN
1300 print_debug('Shelf_life code is of type USER_DEFINED_EXP_DATE', 9);
1301 END IF;
1302
1303 IF p_expiration_date IS NULL THEN
1304 fnd_message.set_name('INV', 'INV_LOT_EXPREQD');
1305 fnd_msg_pub.ADD;
1306
1307 IF g_debug = 1 THEN
1308 print_debug('The value of expiration date is required ', 9);
1309 END IF;
1310 RAISE fnd_api.g_exc_error;
1311
1312 ELSE
1313 l_expiration_date := p_expiration_date;
1314 END IF;
1315
1316
1317 ELSE
1318 IF g_debug = 1 THEN
1319 print_debug('Shelf_life code is of type NO_SHELF_LIFE_CONTROL', 9);
1320 END IF;
1321
1322 fnd_message.set_name('INV', 'INV_EXP_DATE_NOT_CONSIDER');
1323 fnd_msg_pub.ADD;
1324 END IF; /* l_shelf_life_code = item_shelf_life_days */
1325 END IF; /*p_source NOT IN(OSFM_FORM,OSFM_OPEN_INTERFACE)*/
1326
1327
1328 IF G_WMS_INSTALLED IS NULL THEN
1329 IF (inv_install.adv_inv_installed(NULL) = TRUE) THEN
1330 G_wms_installed := 'TRUE';
1331 ELSE
1332 G_WMS_INSTALLED := 'FALSE';
1333 END IF;
1334 END IF;
1335
1336 l_wms_installed := G_WMS_INSTALLED;
1337
1338 IF l_wms_installed = 'TRUE' THEN
1339 INV_LOT_SEL_ATTR.GET_CONTEXT_CODE(l_lot_attribute_category, p_organization_id, p_inventory_item_id, 'Lot Attributes');
1340
1341
1342 IF p_lot_attribute_category IS NOT NULL AND
1343 (nvl(l_lot_attribute_category, p_lot_attribute_category) <> p_lot_attribute_category) THEN
1344 FND_MESSAGE.SET_NAME('INV','INV_WRONG_CONTEXT');
1345 FND_MSG_PUB.ADD;
1346 RAISE fnd_api.g_exc_error;
1347 END IF;
1348 END IF ;
1349
1350 BEGIN
1351 SELECT lot_status_enabled , default_lot_status_id
1352 INTO l_lot_status_flag , l_def_lot_status
1353 FROM mtl_system_items
1354 WHERE organization_id = p_organization_id
1355 AND inventory_item_id = p_inventory_item_id ;
1356
1357 IF p_status_id IS NOT NULL THEN
1358 l_status := p_status_id;
1359 ELSE
1360 IF l_lot_status_flag = 'Y' THEN
1361 l_status := l_def_lot_status ;
1362 ELSE
1363 l_status := NULL;
1364 END IF ;
1365 END IF;
1366 EXCEPTION
1367 WHEN NO_DATA_FOUND THEN
1368 IF g_debug = 1 THEN
1369 print_debug('Unable to fetch status code for the inventory item passed', 9);
1370 END IF;
1371 RAISE NO_DATA_FOUND;
1372 END ;
1373
1374 IF g_debug = 1 THEN
1375 print_debug('Calling validate_lot_Attr_in_param', 9);
1376 END IF;
1377
1378
1379 /* If the call is from a valid source as osfm form, no need to validate the data*/
1380 INV_LOT_API_PUB.VALIDATE_LOT_ATTR_IN_PARAM(
1381 x_return_status => l_return_status
1382 , x_msg_count => l_msg_count
1383 , x_msg_data => l_msg_data
1384 , p_inventory_item_id => p_inventory_item_id
1385 , p_organization_id => p_organization_id
1386 , p_lot_number => p_lot_number
1387 , p_attribute_category => p_attribute_category
1388 , p_lot_attribute_category => nvl(l_lot_attribute_category, p_lot_attribute_category)
1389 , p_attributes_tbl => p_attributes_tbl
1390 , p_c_attributes_tbl => p_c_attributes_tbl
1391 , p_n_attributes_tbl => p_n_attributes_tbl
1392 , p_d_attributes_tbl => p_d_attributes_tbl
1393 , p_wms_is_installed => l_wms_installed
1394 , p_source => p_source
1395 , p_disable_flag => p_disable_flag
1396 , p_grade_code => p_grade_code
1397 , p_origination_date => p_origination_date
1398 , p_date_code => p_date_code
1399 , p_change_date => p_change_date
1400 , p_age => p_age
1401 , p_retest_date => p_retest_date
1402 , p_maturity_date => p_maturity_date
1403 , p_item_size => p_item_size
1404 , p_color => p_color
1405 , p_volume => p_volume
1406 , p_volume_uom => p_volume_uom
1407 , p_place_of_origin => p_place_of_origin
1408 , p_best_by_date => p_best_by_date
1409 , p_length => p_length
1410 , p_length_uom => p_length_uom
1411 , p_recycled_content => p_recycled_content
1412 , p_thickness => p_thickness
1413 , p_thickness_uom => p_thickness_uom
1414 , p_width => p_width
1415 , p_width_uom => p_width_uom
1416 , p_territory_code => p_territory_code
1417 , p_supplier_lot_number => p_supplier_lot_number
1418 , p_vendor_name => p_vendor_name
1419 );
1420
1421
1422 IF g_debug = 1 THEN
1423 print_debug('Program VALIDATE_LOT_ATTR_IN_PARAM return ' || l_return_status, 9);
1424 END IF;
1425 IF l_return_status = fnd_api.g_ret_sts_error THEN
1426 IF g_debug = 1 THEN
1427 print_debug('Program VALIDATE_LOT_ATTR_IN_PARAM has failed with a user defined exception', 9);
1428 END IF;
1429 RAISE fnd_api.g_exc_error;
1430 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1431 IF g_debug = 1 THEN
1432 print_debug('Program VALIDATE_LOT_ATTR_IN_PARAM has failed with a Unexpected exception', 9);
1433 END IF;
1434 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1435 FND_MESSAGE.SET_TOKEN('PROG_NAME','VALIDATE_LOT_ATTR_IN_PARAM');
1436 fnd_msg_pub.ADD;
1437 RAISE fnd_api.g_exc_unexpected_error;
1438 END IF;
1439
1440
1441 Validate_Additional_Attr(
1442 x_return_status => l_return_status
1443 , x_msg_count => l_msg_count
1444 , x_msg_data => l_msg_data
1445 , p_inventory_item_id => p_inventory_item_id
1446 , p_organization_id => p_organization_id
1447 , p_lot_number => p_lot_number
1448 , p_retest_date => p_retest_date
1449 , p_maturity_date => p_maturity_date
1450 , p_source => p_source
1451 , p_grade_code => p_grade_code
1452 , p_origination_date => p_origination_date
1453 , p_parent_lot_number => p_parent_lot_number
1454 , p_origination_type => p_origination_type
1455 , p_expiration_action_code => p_expiration_action_code
1456 , p_expiration_action_date => p_expiration_action_date
1457 , p_expiration_date => p_expiration_date
1458 , p_hold_date => p_hold_date
1459 );
1460
1461 IF g_debug = 1 THEN
1462 print_debug('Program VALIDATE_ADDITIONAL_ATTR return ' || l_return_status, 9);
1463 END IF;
1464 IF l_return_status = fnd_api.g_ret_sts_error THEN
1465 IF g_debug = 1 THEN
1466 print_debug('Program VALIDATE_ADDITIONAL_ATTR has failed with a user defined exception', 9);
1467 END IF;
1468 RAISE fnd_api.g_exc_error;
1469 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1470 IF g_debug = 1 THEN
1471 print_debug('Program VALIDATE_ADDITIONAL_ATTR has failed with a Unexpected exception', 9);
1472 END IF;
1473 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1474 FND_MESSAGE.SET_TOKEN('PROG_NAME','VALIDATE_ADDITIONAL_ATTR');
1475 fnd_msg_pub.ADD;
1476 RAISE fnd_api.g_exc_unexpected_error;
1477 END IF;
1478
1479
1480 /* Fetch data from the input tabels and fill the appropriate records*/
1481 IF p_attributes_tbl.COUNT > 0 THEN
1482 l_inv_attributes_tbl := p_attributes_tbl;
1483 END IF;
1484
1485 IF p_c_attributes_tbl.COUNT > 0 THEN
1486 l_c_attributes_tbl := p_c_attributes_tbl;
1487 END IF;
1488
1489 IF p_n_attributes_tbl.COUNT > 0 THEN
1490 l_n_attributes_tbl := p_n_attributes_tbl;
1491 END IF;
1492
1493 IF p_d_attributes_tbl.COUNT > 0 THEN
1494 l_d_attributes_tbl := p_d_attributes_tbl;
1495 END IF;
1496
1497 FOR l_attr_index IN 1 .. 15 LOOP
1498 IF NOT l_inv_attributes_tbl.EXISTS(l_attr_index) THEN
1499 l_inv_attributes_tbl(l_attr_index) := NULL;
1500 END IF;
1501 END LOOP;
1502
1503 FOR l_c_attr_index IN 1 .. 20 LOOP
1504 IF NOT l_c_attributes_tbl.EXISTS(l_c_attr_index) THEN
1505 l_c_attributes_tbl(l_c_attr_index) := NULL;
1506 END IF;
1507 END LOOP;
1508
1509 FOR l_n_attr_index IN 1 .. 10 LOOP
1510 IF NOT l_n_attributes_tbl.EXISTS(l_n_attr_index) THEN
1511 l_n_attributes_tbl(l_n_attr_index) := NULL;
1512 END IF;
1513 END LOOP;
1514
1515 FOR l_d_attr_index IN 1 .. 10 LOOP
1516 IF NOT l_d_attributes_tbl.EXISTS(l_d_attr_index) THEN
1517 l_d_attributes_tbl(l_d_attr_index) := NULL;
1518 END IF;
1519 END LOOP;
1520
1521
1522 IF( g_debug = 1 ) THEN
1523 print_debug('Populate OUT lot record parameter', 9);
1524 END IF;
1525
1526 -- SELECT MTL_GEN_OBJECT_ID_S.NEXTVAL INTO l_mtl_gen_obj_no FROM dual;
1527
1528 -- Replace the INSERT INTO mtl_lot_numbers statement with population of OUT lot record parameter.
1529
1530 x_lot_rec.inventory_item_id := p_inventory_item_id;
1531 x_lot_rec.organization_id := p_organization_id;
1532 x_lot_rec.lot_number := p_lot_number;
1533 x_lot_rec.expiration_date := l_expiration_date;
1534 x_lot_rec.disable_flag := p_disable_flag;
1535 x_lot_rec.attribute_category := p_attribute_category;
1536 x_lot_rec.lot_attribute_category := nvl(L_lot_attribute_category, p_lot_attribute_category);
1537 x_lot_rec.attribute1 := l_inv_attributes_tbl(1);
1538 x_lot_rec.attribute2 := l_inv_attributes_tbl(2);
1539 x_lot_rec.attribute3 := l_inv_attributes_tbl(3);
1540 x_lot_rec.attribute4 := l_inv_attributes_tbl(4);
1541 x_lot_rec.attribute5 := l_inv_attributes_tbl(5);
1542 x_lot_rec.attribute6 := l_inv_attributes_tbl(6);
1543 x_lot_rec.attribute7 := l_inv_attributes_tbl(7);
1544 x_lot_rec.attribute8 := l_inv_attributes_tbl(8);
1545 x_lot_rec.attribute9 := l_inv_attributes_tbl(9);
1546 x_lot_rec.attribute10 := l_inv_attributes_tbl(10);
1547 x_lot_rec.attribute11 := l_inv_attributes_tbl(11);
1548 x_lot_rec.attribute12 := l_inv_attributes_tbl(12);
1549 x_lot_rec.attribute13 := l_inv_attributes_tbl(13);
1550 x_lot_rec.attribute14 := l_inv_attributes_tbl(14);
1551 x_lot_rec.attribute15 := l_inv_attributes_tbl(15);
1552 x_lot_rec.c_attribute1 := l_c_attributes_tbl(1);
1553 x_lot_rec.c_attribute2 := l_c_attributes_tbl(2);
1554 x_lot_rec.c_attribute3 := l_c_attributes_tbl(3);
1555 x_lot_rec.c_attribute4 := l_c_attributes_tbl(4);
1556 x_lot_rec.c_attribute5 := l_c_attributes_tbl(5);
1557 x_lot_rec.c_attribute6 := l_c_attributes_tbl(6);
1558 x_lot_rec.c_attribute7 := l_c_attributes_tbl(7);
1559 x_lot_rec.c_attribute8 := l_c_attributes_tbl(8);
1560 x_lot_rec.c_attribute9 := l_c_attributes_tbl(9);
1561 x_lot_rec.c_attribute10 := l_c_attributes_tbl(10);
1562 x_lot_rec.c_attribute11 := l_c_attributes_tbl(11);
1563 x_lot_rec.c_attribute12 := l_c_attributes_tbl(12);
1564 x_lot_rec.c_attribute13 := l_c_attributes_tbl(13);
1565 x_lot_rec.c_attribute14 := l_c_attributes_tbl(14);
1566 x_lot_rec.c_attribute15 := l_c_attributes_tbl(15);
1567 x_lot_rec.c_attribute16 := l_c_attributes_tbl(16);
1568 x_lot_rec.c_attribute17 := l_c_attributes_tbl(17);
1569 x_lot_rec.c_attribute18 := l_c_attributes_tbl(18);
1570 x_lot_rec.c_attribute19 := l_c_attributes_tbl(19);
1571 x_lot_rec.c_attribute20 := l_c_attributes_tbl(20);
1572 x_lot_rec.n_attribute1 := l_n_attributes_tbl(1);
1573 x_lot_rec.n_attribute2 := l_n_attributes_tbl(2);
1574 x_lot_rec.n_attribute3 := l_n_attributes_tbl(3);
1575 x_lot_rec.n_attribute4 := l_n_attributes_tbl(4);
1576 x_lot_rec.n_attribute5 := l_n_attributes_tbl(5);
1577 x_lot_rec.n_attribute6 := l_n_attributes_tbl(6);
1578 x_lot_rec.n_attribute7 := l_n_attributes_tbl(7);
1579 x_lot_rec.n_attribute8 := l_n_attributes_tbl(8);
1580 x_lot_rec.n_attribute9 := l_n_attributes_tbl(9);
1581 x_lot_rec.n_attribute10 := l_n_attributes_tbl(10);
1582 x_lot_rec.d_attribute1 := l_d_attributes_tbl(1);
1583 x_lot_rec.d_attribute2 := l_d_attributes_tbl(2);
1584 x_lot_rec.d_attribute3 := l_d_attributes_tbl(3);
1585 x_lot_rec.d_attribute4 := l_d_attributes_tbl(4);
1586 x_lot_rec.d_attribute5 := l_d_attributes_tbl(5);
1587 x_lot_rec.d_attribute6 := l_d_attributes_tbl(6);
1588 x_lot_rec.d_attribute7 := l_d_attributes_tbl(7);
1589 x_lot_rec.d_attribute8 := l_d_attributes_tbl(8);
1590 x_lot_rec.d_attribute9 := l_d_attributes_tbl(9);
1591 x_lot_rec.d_attribute10 := l_d_attributes_tbl(10);
1592 x_lot_rec.grade_code := p_grade_code;
1593 x_lot_rec.origination_date := p_origination_date;
1594 x_lot_rec.date_code := p_date_code;
1595 x_lot_rec.status_id := l_status;
1596 x_lot_rec.change_date := p_change_date;
1597 x_lot_rec.age := p_age;
1598 x_lot_rec.retest_date := p_retest_date;
1599 x_lot_rec.maturity_date := p_maturity_date;
1600 x_lot_rec.item_size := p_item_size;
1601 x_lot_rec.color := p_color;
1602 x_lot_rec.volume := p_volume;
1603 x_lot_rec.volume_uom := p_volume_uom;
1604 x_lot_rec.place_of_origin := p_place_of_origin;
1605 x_lot_rec.best_by_date := p_best_by_date;
1606 x_lot_rec.LENGTH := p_length;
1607 x_lot_rec.length_uom := p_length_uom;
1608 x_lot_rec.recycled_content := p_recycled_content;
1609 x_lot_rec.thickness := p_thickness;
1610 x_lot_rec.thickness_uom := p_thickness_uom;
1611 x_lot_rec.width := p_width;
1612 x_lot_rec.width_uom := p_width_uom;
1613 x_lot_rec.territory_code := p_territory_code;
1614 x_lot_rec.supplier_lot_number := p_supplier_lot_number;
1615 x_lot_rec.vendor_name := p_vendor_name;
1616 x_lot_rec.creation_date := SYSDATE;
1617 x_lot_rec.created_by := fnd_global.user_id;
1618 x_lot_rec.last_update_date := SYSDATE;
1619 x_lot_rec.last_updated_by := fnd_global.user_id;
1620 x_lot_rec.parent_lot_number := p_parent_lot_number;
1621 x_lot_rec.origination_type := p_origination_type;
1622 x_lot_rec.expiration_action_code := p_expiration_action_code;
1623 x_lot_rec.expiration_action_date := p_expiration_action_date;
1624 x_lot_rec.hold_date := p_hold_date;
1625
1626 print_debug('End of the program Validate_Lot_Attributes. Program has completed successfully ', 9);
1627
1628 EXCEPTION
1629 WHEN NO_DATA_FOUND THEN
1630 x_return_status := fnd_api.g_ret_sts_error;
1631 ROLLBACK TO inv_val_lot ;
1632 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1633 if( x_msg_count > 1 ) then
1634 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
1635 end if;
1636 print_debug('In No data found Validate_Lot_Attributes ' || SQLERRM, 9);
1637 WHEN fnd_api.g_exc_error THEN
1638 x_return_status := fnd_api.g_ret_sts_error;
1639 ROLLBACK TO inv_val_lot ;
1640 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1641 if( x_msg_count > 1 ) then
1642 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
1643 end if;
1644 print_debug('In g_exc_error Validate_Lot_Attributes ' || SQLERRM, 9);
1645 WHEN fnd_api.g_exc_unexpected_error THEN
1646 x_return_status := fnd_api.g_ret_sts_unexp_error;
1647 ROLLBACK TO inv_val_lot ;
1648 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1649 if( x_msg_count > 1 ) then
1650 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
1651 end if;
1652 print_debug('In g_exc_unexpected_error Validate_Lot_Attributes ' || SQLERRM, 9);
1653 WHEN OTHERS THEN
1654 x_return_status := fnd_api.g_ret_sts_unexp_error;
1655 ROLLBACK TO inv_val_lot ;
1656 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
1657 if( x_msg_count > 1 ) then
1658 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
1659 end if;
1660 print_debug('In others Validate_Lot_Attributes ' || SQLERRM, 9);
1661
1662 END Validate_Lot_Attributes ;
1663
1664
1665 PROCEDURE Validate_Additional_Attr(
1666 x_return_status OUT NOCOPY VARCHAR2
1667 , x_msg_count OUT NOCOPY NUMBER
1668 , x_msg_data OUT NOCOPY VARCHAR2
1669 , p_inventory_item_id IN NUMBER
1670 , p_organization_id IN NUMBER
1671 , p_lot_number IN VARCHAR2
1672 , p_source IN NUMBER
1673 , p_grade_code IN VARCHAR2
1674 , p_retest_date IN DATE
1675 , p_maturity_date IN DATE
1676 , p_parent_lot_number IN VARCHAR2
1677 , p_origination_date IN DATE
1678 , p_origination_type IN NUMBER
1679 , p_expiration_action_code IN VARCHAR2
1680 , p_expiration_action_date IN DATE
1681 , p_expiration_date IN DATE
1682 , p_hold_date IN DATE
1683 ) IS
1684
1685 /* Parent lot validation logic */
1686 CURSOR c_get_lot_record IS
1687 SELECT *
1688 FROM mtl_lot_numbers
1689 WHERE lot_number = p_lot_number
1690 AND inventory_item_id = p_inventory_item_id
1691 AND organization_id = p_organization_id;
1692
1693 l_lot_record c_get_lot_record%ROWTYPE;
1694
1695 CURSOR c_get_item_info IS
1696 SELECT *
1697 FROM mtl_system_items
1698 WHERE organization_id = p_organization_id
1699 AND inventory_item_id = p_inventory_item_id ;
1700
1701 l_item_info c_get_item_info%ROWTYPE;
1702
1703
1704 l_return_status VARCHAR2(1);
1705 l_msg_count NUMBER;
1706 l_msg_data VARCHAR2(3000);
1707 l_api_version NUMBER;
1708 l_init_msg_list VARCHAR2(100);
1709 l_commit VARCHAR2(100);
1710 res BOOLEAN ;
1711
1712 l_lot_cont BOOLEAN ;
1713 l_child_lot_cont BOOLEAN ;
1714
1715 BEGIN
1716 x_return_status := fnd_api.g_ret_sts_success;
1717 SAVEPOINT val_lot_attr_information;
1718
1719 l_api_version := 1.0;
1720 l_init_msg_list := fnd_api.g_false;
1721 l_commit := fnd_api.g_false;
1722
1723 /******************* START Item validation ********************/
1724
1725 l_lot_cont := FALSE ;
1726 l_child_lot_cont := FALSE ;
1727
1728 check_item_attributes
1729 (
1730 x_return_status => l_return_status
1731 , x_msg_count => l_msg_count
1732 , x_msg_data => l_msg_data
1733 , x_lot_cont => l_lot_cont
1734 , x_child_lot_cont => l_child_lot_cont
1735 , p_inventory_item_id => p_inventory_item_id
1736 , p_organization_id => p_organization_id
1737 ) ;
1738
1739 IF g_debug = 1 THEN
1740 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
1741 END IF;
1742
1743 IF l_return_status = fnd_api.g_ret_sts_error THEN
1744 IF g_debug = 1 THEN
1745 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
1746 END IF;
1747 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
1748 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
1749 FND_MSG_PUB.ADD;
1750 RAISE fnd_api.g_exc_error;
1751 END IF;
1752
1753 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1754 IF g_debug = 1 THEN
1755 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
1756 END IF;
1757 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1758 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
1759 fnd_msg_pub.ADD;
1760 RAISE fnd_api.g_exc_unexpected_error;
1761 END IF;
1762
1763 IF (l_lot_cont = FALSE) THEN
1764 IF g_debug = 1 THEN
1765 print_debug(' Item is not lot controlled ', 9);
1766 END IF;
1767 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
1768 fnd_msg_pub.ADD;
1769 x_return_status := fnd_api.g_ret_sts_error;
1770 RAISE fnd_api.g_exc_error;
1771 END IF ;
1772
1773 IF (l_child_lot_cont = FALSE AND p_parent_lot_number IS NOT NULL) THEN
1774
1775 IF g_debug = 1 THEN
1776 print_debug(' Item is not Child lot controlled ', 9);
1777 END IF;
1778 fnd_message.set_name('INV', 'INV_ITEM_CLOT_DISABLE_EXP');
1779 fnd_msg_pub.ADD;
1780 x_return_status := fnd_api.g_ret_sts_error;
1781 RAISE fnd_api.g_exc_error;
1782 END IF ;
1783 /******************* End Item validation ********************/
1784
1785 /******************* START Parent lot validation logic ********************/
1786 /* Get Lot Information*/
1787 OPEN c_get_lot_record ;
1788 FETCH c_get_lot_record INTO l_lot_record;
1789
1790 /* Check Lot */
1791 IF c_get_lot_record%NOTFOUND THEN
1792 /* New child Lot*/
1793 CLOSE c_get_lot_record;
1794 /* Check Child Lot Naming convention */
1795 IF (p_parent_lot_number IS NOT NULL) THEN
1796 Inv_lot_api_pub.validate_child_lot (
1797 x_return_status => l_return_status
1798 , x_msg_count => l_msg_count
1799 , x_msg_data => l_msg_data
1800 , p_api_version => l_api_version
1801 , p_init_msg_list => l_init_msg_list
1802 , p_commit => l_commit
1803 , p_organization_id => p_organization_id
1804 , p_inventory_item_id => p_inventory_item_id
1805 , p_parent_lot_number => p_parent_lot_number
1806 , p_child_lot_number => p_lot_number
1807 ) ;
1808
1809 IF g_debug = 1 THEN
1810 print_debug('Program Inv_lot_api_pub.VALIDATE_CHILD_LOT return ' || l_return_status, 9);
1811 END IF;
1812
1813 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1814 IF g_debug = 1 THEN
1815 print_debug('Program Inv_lot_api_pub.VALIDATE_CHILD_LOT has failed with a Unexpected exception', 9);
1816 END IF;
1817 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1818 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pub.VALIDATE_CHILD_LOT');
1819 fnd_msg_pub.ADD;
1820 RAISE fnd_api.g_exc_unexpected_error;
1821 END IF;
1822
1823 IF l_return_status = fnd_api.g_ret_sts_error THEN
1824 IF g_debug = 1 THEN
1825 print_debug('Invalid child lot Naming convention', 9);
1826 END IF;
1827
1828 fnd_message.set_name('INV', 'INV_INVALID_CHILD_LOT_EXP') ;
1829 fnd_msg_pub.ADD;
1830 RAISE fnd_api.g_exc_error;
1831 END IF;
1832 END IF ; -- parent_lot check
1833 ELSE
1834 CLOSE c_get_lot_record;
1835 /* Check Parent Lot, then default the correct Parent Lot */
1836 IF l_lot_record.parent_lot_number IS NOT NULL THEN
1837 IF l_lot_record.parent_lot_number <> p_parent_lot_number THEN
1838 IF g_debug = 1 THEN
1839 print_debug('Invalid relationship between parent and child lots', 9);
1840 END IF;
1841
1842 fnd_message.set_name('INV', 'INV_INVALID_PARENT_LOT_EXP') ;
1843 fnd_msg_pub.ADD;
1844 RAISE fnd_api.g_exc_error;
1845 END IF;
1846 END IF;
1847 END IF; /* Check Lot */
1848 /******************* END Parent lot validation logic ********************/
1849
1850
1851 /******************* START Origination Type validation logic ********************/
1852
1853 res := FALSE ;
1854 res := Inv_Lot_Attr_Pub.validate_origination_type (
1855 p_origination_type => p_origination_type
1856 , x_return_status => l_return_status
1857 , x_msg_count => l_msg_count
1858 , x_msg_data => l_msg_data
1859 ) ;
1860
1861 IF res = FALSE THEN
1862 IF g_debug = 1 THEN
1863 print_debug('Invalid Origination Type value '|| p_origination_type, 9);
1864 END IF;
1865 RAISE fnd_api.g_exc_error;
1866 END IF;
1867 IF g_debug = 1 THEN
1868 print_debug('Program Inv_Lot_Attr_Pub.validate_origination_type ' || l_return_status, 9);
1869 END IF;
1870 IF l_return_status = fnd_api.g_ret_sts_error THEN
1871 IF g_debug = 1 THEN
1872 print_debug('Program INV_LOT_ATTR_PUB.validate_origination_type has failed with a user defined exception', 9);
1873 END IF;
1874 RAISE fnd_api.g_exc_error;
1875 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1876 IF g_debug = 1 THEN
1877 print_debug('Program INV_LOT_ATTR_PUB.validate_origination_type has failed with a Unexpected exception', 9);
1878 END IF;
1879 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1880 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
1881 FND_MSG_PUB.ADD;
1882 RAISE fnd_api.g_exc_unexpected_error;
1883 END IF;
1884
1885
1886
1887 /******************* END Origination Type validation logic ********************/
1888
1889 /******************* START Grade Code validation logic ********************/
1890
1891 res := FALSE ;
1892 res := Inv_Lot_Attr_Pub.validate_grade_code(
1893 p_grade_code => p_grade_code
1894 , p_org_id => p_organization_id
1895 , p_inventory_item_id => p_inventory_item_id
1896 , p_grade_control_flag => NULL
1897 , x_return_status => l_return_status
1898 , x_msg_count => l_msg_count
1899 , x_msg_data => l_msg_data
1900 );
1901
1902 IF res = FALSE THEN
1903 IF g_debug = 1 THEN
1904 print_debug('Invalid Grade Code value '|| p_grade_code, 9);
1905 END IF;
1906 RAISE fnd_api.g_exc_error;
1907 END IF;
1908 IF g_debug = 1 THEN
1909 print_debug('Program Inv_Lot_Attr_Pub.validate_grade_code ' || l_return_status, 9);
1910 END IF;
1911 IF l_return_status = fnd_api.g_ret_sts_error THEN
1912 IF g_debug = 1 THEN
1913 print_debug('Program INV_LOT_ATTR_PUB.validate_grade_code has failed with a user defined exception', 9);
1914 END IF;
1915 RAISE g_exc_error;
1916 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1917 IF g_debug = 1 THEN
1918 print_debug('Program INV_LOT_ATTR_PUB.validate_grade_code has failed with a Unexpected exception', 9);
1919 END IF;
1920 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1921 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
1922 FND_MSG_PUB.ADD;
1923 RAISE fnd_api.g_exc_unexpected_error;
1924 END IF;
1925
1926
1927 /******************* END Grade Code validation logic ********************/
1928
1929 /******************* START Expiration Action Code validation logic ********************/
1930
1931 res := FALSE ;
1932 res := Inv_Lot_Attr_Pub.validate_exp_action_code(
1933 p_expiration_action_code => p_expiration_action_code
1934 , p_org_id => p_organization_id
1935 , p_inventory_item_id => p_inventory_item_id
1936 , p_shelf_life_code => NULL
1937 , x_return_status => l_return_status
1938 , x_msg_count => l_msg_count
1939 , x_msg_data => l_msg_data
1940 ) ;
1941
1942 IF res = FALSE THEN
1943 IF g_debug = 1 THEN
1944 print_debug('Invalid Expiration Action Code value '|| p_expiration_action_code, 9);
1945 END IF;
1946 RAISE fnd_api.g_exc_error;
1947 END IF;
1948 IF g_debug = 1 THEN
1949 print_debug('Program Inv_Lot_Attr_Pub.validate_exp_action_code ' || l_return_status, 9);
1950 END IF;
1951 IF l_return_status = fnd_api.g_ret_sts_error THEN
1952 IF g_debug = 1 THEN
1953 print_debug('Program INV_LOT_ATTR_PUB.validate_exp_action_code has failed with a user defined exception', 9);
1954 END IF;
1955 RAISE fnd_api.g_exc_error;
1956 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1957 IF g_debug = 1 THEN
1958 print_debug('Program INV_LOT_ATTR_PUB.validate_exp_action_code has failed with a Unexpected exception', 9);
1959 END IF;
1960 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
1961 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
1962 FND_MSG_PUB.ADD;
1963 RAISE fnd_api.g_exc_unexpected_error;
1964 END IF;
1965
1966
1967
1968 /******************* END Expiration Action Code validation logic ********************/
1969
1970 /******************* START Expiration Action Date validation logic ********************/
1971
1972 OPEN c_get_item_info ;
1973 FETCH c_get_item_info INTO l_item_info;
1974
1975 IF c_get_item_info%FOUND THEN
1976 res := FALSE ;
1977
1978 IF l_item_info.expiration_action_interval IS NOT NULL
1979 AND l_item_info.expiration_action_interval > 0 THEN
1980
1981 res := Inv_Lot_Attr_Pub. validate_exp_action_date(
1982 p_expiration_action_date => p_expiration_action_date
1983 , p_expiration_date => p_expiration_date
1984 , x_return_status => l_return_status
1985 , x_msg_count => l_msg_count
1986 , x_msg_data => l_msg_data
1987 ) ;
1988
1989 IF res = FALSE THEN
1990 IF g_debug = 1 THEN
1991 print_debug('Invalid Expiration Action Date value '|| p_expiration_action_date, 9);
1992 END IF;
1993 RAISE fnd_api.g_exc_error;
1994 END IF;
1995 IF g_debug = 1 THEN
1996 print_debug('Program Inv_Lot_Attr_Pub.validate_exp_action_date ' || l_return_status, 9);
1997 END IF;
1998 IF l_return_status = fnd_api.g_ret_sts_error THEN
1999 IF g_debug = 1 THEN
2000 print_debug('Program INV_LOT_ATTR_PUB.validate_exp_action_date has failed with a user defined exception', 9);
2001 END IF;
2002 RAISE fnd_api.g_exc_error;
2003 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2004 IF g_debug = 1 THEN
2005 print_debug('Program INV_LOT_ATTR_PUB.validate_exp_action_date has failed with a Unexpected exception', 9);
2006 END IF;
2007 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2008 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
2009 FND_MSG_PUB.ADD;
2010 RAISE fnd_api.g_exc_unexpected_error;
2011 END IF;
2012 END IF ; -- Check for positive expiration action interval
2013 END IF ; -- Cursor If
2014 CLOSE c_get_item_info;
2015
2016 /******************* END Expiration Action Date validation logic ********************/
2017
2018 /******************* START Perform Date validation logic ********************/
2019 IF (p_origination_date IS NOT NULL) THEN
2020
2021 /******************* START Retest Date validation logic ********************/
2022
2023 res := FALSE ;
2024 res := Inv_Lot_Attr_Pub.validate_retest_date(
2025 p_retest_date => p_retest_date
2026 , p_origination_date => p_origination_date
2027 , x_return_status => l_return_status
2028 , x_msg_count => l_msg_count
2029 , x_msg_data => l_msg_data
2030 );
2031
2032 IF res = FALSE THEN
2033 IF g_debug = 1 THEN
2034 print_debug('Invalid Retest Date value '|| p_retest_date, 9);
2035 END IF;
2036 RAISE fnd_api.g_exc_error;
2037 END IF;
2038 IF g_debug = 1 THEN
2039 print_debug('Program Inv_Lot_Attr_Pub.validate_retest_date ' || l_return_status, 9);
2040 END IF;
2041 IF l_return_status = fnd_api.g_ret_sts_error THEN
2042 IF g_debug = 1 THEN
2043 print_debug('Program INV_LOT_ATTR_PUB.validate_retest_date has failed with a user defined exception', 9);
2044 END IF;
2045 RAISE fnd_api.g_exc_error;
2046 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2047 IF g_debug = 1 THEN
2048 print_debug('Program INV_LOT_ATTR_PUB.validate_retest_date has failed with a Unexpected exception', 9);
2049 END IF;
2050 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2051 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
2052 FND_MSG_PUB.ADD;
2053 RAISE fnd_api.g_exc_unexpected_error;
2054 END IF;
2055
2056
2057
2058 /******************* END Retest Date validation logic ********************/
2059
2060 /******************* START Maturity Date validation logic ********************/
2061
2062 res := FALSE ;
2063 res := Inv_Lot_Attr_Pub.validate_maturity_date(
2064 p_maturity_date => p_maturity_date
2065 , p_origination_date => p_origination_date
2066 , x_return_status => l_return_status
2067 , x_msg_count => l_msg_count
2068 , x_msg_data => l_msg_data
2069 );
2070
2071 IF res = FALSE THEN
2072 IF g_debug = 1 THEN
2073 print_debug('Invalid Maturity Date value '|| p_maturity_date, 9);
2074 END IF;
2075 RAISE fnd_api.g_exc_error;
2076 END IF;
2077 IF g_debug = 1 THEN
2078 print_debug('Program Inv_Lot_Attr_Pub.validate_maturity_date ' || l_return_status, 9);
2079 END IF;
2080 IF l_return_status = fnd_api.g_ret_sts_error THEN
2081 IF g_debug = 1 THEN
2082 print_debug('Program INV_LOT_ATTR_PUB.validate_maturity_date has failed with a user defined exception', 9);
2083 END IF;
2084 RAISE fnd_api.g_exc_error;
2085 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2086 IF g_debug = 1 THEN
2087 print_debug('Program INV_LOT_ATTR_PUB.validate_maturity_date has failed with a Unexpected exception', 9);
2088 END IF;
2089 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2090 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
2091 FND_MSG_PUB.ADD;
2092 RAISE fnd_api.g_exc_unexpected_error;
2093 END IF;
2094
2095
2096
2097 /******************* END Maturity Date validation logic ********************/
2098
2099 /******************* START Hold Date validation logic ********************/
2100
2101 res := FALSE ;
2102 res := Inv_Lot_Attr_Pub.validate_hold_date(
2103 p_hold_date => p_hold_date
2104 , p_origination_date => p_origination_date
2105 , x_return_status => l_return_status
2106 , x_msg_count => l_msg_count
2107 , x_msg_data => l_msg_data
2108 ) ;
2109
2110 IF res = FALSE THEN
2111 IF g_debug = 1 THEN
2112 print_debug('Invalid Hold Date value '|| p_hold_date, 9);
2113 END IF;
2114 RAISE fnd_api.g_exc_error;
2115 END IF;
2116 IF g_debug = 1 THEN
2117 print_debug('Program Inv_Lot_Attr_Pub.validate_hold_date ' || l_return_status, 9);
2118 END IF;
2119 IF l_return_status = fnd_api.g_ret_sts_error THEN
2120 IF g_debug = 1 THEN
2121 print_debug('Program INV_LOT_ATTR_PUB.validate_hold_date has failed with a user defined exception', 9);
2122 END IF;
2123 RAISE fnd_api.g_exc_error;
2124 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2125 IF g_debug = 1 THEN
2126 print_debug('Program INV_LOT_ATTR_PUB.validate_hold_date has failed with a Unexpected exception', 9);
2127 END IF;
2128 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2129 FND_MESSAGE.SET_TOKEN('PROG_NAME','INV_LOT_ATTR_PUB. ');
2130 FND_MSG_PUB.ADD;
2131 RAISE fnd_api.g_exc_unexpected_error;
2132 END IF;
2133
2134
2135 /******************* END Hold Date validation logic ********************/
2136 END IF;
2137 /******************* END Perform Date validation logic ********************/
2138
2139 EXCEPTION
2140 WHEN NO_DATA_FOUND THEN
2141 x_return_status := fnd_api.g_ret_sts_error;
2142 ROLLBACK TO val_lot_attr_information;
2143 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2144 if( x_msg_count > 1 ) then
2145 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2146 end if;
2147 print_debug('In No data found Validate_Additional_Attr ' || SQLERRM, 9);
2148 WHEN fnd_api.g_exc_error THEN
2149 x_return_status := fnd_api.g_ret_sts_error;
2150 ROLLBACK TO val_lot_attr_information;
2151 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2152 if( x_msg_count > 1 ) then
2153 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2154 end if;
2155 print_debug('In g_exc_error Validate_Additional_Attr ' || SQLERRM, 9);
2156 WHEN fnd_api.g_exc_unexpected_error THEN
2157 x_return_status := fnd_api.g_ret_sts_unexp_error;
2158 ROLLBACK TO val_lot_attr_information;
2159 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2160 if( x_msg_count > 1 ) then
2161 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2162 end if;
2163 print_debug('In g_exc_unexpected_error Validate_Additional_Attr ' || SQLERRM, 9);
2164 WHEN OTHERS THEN
2165 x_return_status := fnd_api.g_ret_sts_unexp_error;
2166 ROLLBACK TO val_lot_attr_information;
2167 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2168 if( x_msg_count > 1 ) then
2169 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2170 end if;
2171 print_debug('In others Validate_Additional_Attr ' || SQLERRM, 9);
2172
2173 END Validate_Additional_Attr;
2174
2175
2176
2177 PROCEDURE Delete_Lot(
2178 x_return_status OUT NOCOPY VARCHAR2
2179 , x_msg_count OUT NOCOPY NUMBER
2180 , x_msg_data OUT NOCOPY VARCHAR2
2181 , p_inventory_item_id IN NUMBER
2182 , p_organization_id IN NUMBER
2183 , p_lot_number IN VARCHAR2
2184
2185 ) IS
2186
2187 CURSOR c_lot_rec IS
2188 SELECT *
2189 FROM mtl_lot_numbers
2190 WHERE inventory_item_id = p_inventory_item_id
2191 AND organization_id = p_organization_id
2192 AND lot_number = p_lot_number ;
2193
2194 l_lot_rec c_lot_rec%ROWTYPE ;
2195
2196 CURSOR c_gen_rec(cp_gen_obj_id NUMBER) IS
2197 SELECT *
2198 FROM mtl_object_genealogy
2199 WHERE object_id = cp_gen_obj_id ;
2200
2201 l_gen_rec c_gen_rec%ROWTYPE ;
2202 l_gen_obj_id NUMBER ;
2203
2204 CURSOR c_uom_conv_rec IS
2205 SELECT *
2206 FROM mtl_lot_uom_class_conversions
2207 WHERE lot_number = p_lot_number ;
2208
2209 l_uom_conv_rec c_uom_conv_rec%ROWTYPE ;
2210
2211 l_return_status VARCHAR2(1);
2212 l_msg_count NUMBER;
2213 l_msg_data VARCHAR2(3000);
2214 l_lot_cont BOOLEAN ;
2215 l_child_lot_cont BOOLEAN ;
2216
2217 BEGIN
2218
2219 SAVEPOINT inv_delete_lot ;
2220 x_return_status := fnd_api.g_ret_sts_success;
2221
2222 /*Basic Validations - Start*/
2223 IF p_organization_id IS NULL THEN
2224 IF g_debug = 1 THEN
2225 print_debug('Value for mandatory field organization id cannot be null.', 9);
2226 END IF;
2227 fnd_message.set_name('INV', 'INV_NULL_ORG_EXP') ;
2228 fnd_msg_pub.ADD;
2229 RAISE fnd_api.g_exc_error;
2230 END IF ;
2231
2232 IF p_inventory_item_id IS NULL THEN
2233 IF g_debug = 1 THEN
2234 print_debug('Value for mandatory field inventory item id cannot be null.', 9);
2235 END IF;
2236 fnd_message.set_name('INV', 'INV_INVALID_ITEM') ;
2237 fnd_msg_pub.ADD;
2238 RAISE fnd_api.g_exc_error;
2239 END IF ;
2240
2241 IF p_lot_number IS NULL THEN
2242 IF g_debug = 1 THEN
2243 print_debug('Value for mandatory field Lot Number cannot be null', 9);
2244 END IF;
2245 fnd_message.set_name('INV', 'INV_NULL_CLOT_EXP');
2246 fnd_msg_pub.ADD;
2247 RAISE fnd_api.g_exc_error;
2248 END IF;
2249 /*Basic Validations - End*/
2250
2251 /******************* START Item validation ********************/
2252
2253 l_lot_cont := FALSE ;
2254 l_child_lot_cont := FALSE ;
2255
2256 check_item_attributes
2257 (
2258 x_return_status => l_return_status
2259 , x_msg_count => l_msg_count
2260 , x_msg_data => l_msg_data
2261 , x_lot_cont => l_lot_cont
2262 , x_child_lot_cont => l_child_lot_cont
2263 , p_inventory_item_id => p_inventory_item_id
2264 , p_organization_id => p_organization_id
2265 ) ;
2266
2267 IF g_debug = 1 THEN
2268 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes return ' || l_return_status, 9);
2269 END IF;
2270
2271 IF l_return_status = fnd_api.g_ret_sts_error THEN
2272 IF g_debug = 1 THEN
2273 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with error', 9);
2274 END IF;
2275 FND_MESSAGE.SET_NAME('INV', 'INV_PROGRAM_ERROR') ;
2276 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
2277 FND_MSG_PUB.ADD;
2278 RAISE fnd_api.g_exc_error;
2279 END IF;
2280
2281 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2282 IF g_debug = 1 THEN
2283 print_debug('Program Inv_lot_api_pkg.Check_Item_Attributes has failed with a Unexpected exception', 9);
2284 END IF;
2285 FND_MESSAGE.SET_NAME('INV','INV_PROGRAM_ERROR');
2286 FND_MESSAGE.SET_TOKEN('PROG_NAME','Inv_lot_api_pkg.Check_Item_Attributes');
2287 fnd_msg_pub.ADD;
2288 RAISE fnd_api.g_exc_unexpected_error;
2289 END IF;
2290
2291 IF (l_lot_cont = FALSE) THEN
2292 IF g_debug = 1 THEN
2293 print_debug(' Item is not lot controlled ', 9);
2294 END IF;
2295 fnd_message.set_name('INV', 'INV_NO_LOT_CONTROL');
2296 fnd_msg_pub.ADD;
2297 x_return_status := fnd_api.g_ret_sts_error;
2298 RAISE g_exc_error;
2299 END IF ;
2300
2301 /******************* End Item validation ********************/
2302
2303
2304 OPEN c_lot_rec ;
2305 FETCH c_lot_rec INTO l_lot_rec ;
2306
2307 IF c_lot_rec%FOUND THEN
2308
2309 l_gen_obj_id := l_lot_rec.gen_object_id ;
2310
2311 DELETE FROM mtl_lot_numbers
2312 WHERE inventory_item_id = p_inventory_item_id
2313 AND organization_id = p_organization_id
2314 AND lot_number = p_lot_number ;
2315
2316 CLOSE c_lot_rec ;
2317
2318 ELSE
2319 CLOSE c_lot_rec ;
2320 RAISE NO_DATA_FOUND ;
2321 END IF;
2322
2323 IF g_debug = 1 THEN
2324 print_debug('Delete_Lot. After deleting Lot Record', 9);
2325 END IF;
2326
2327 OPEN c_gen_rec (l_gen_obj_id) ;
2328 FETCH c_gen_rec INTO l_gen_rec ;
2329
2330 IF c_gen_rec%FOUND THEN
2331 DELETE FROM mtl_object_genealogy
2332 WHERE object_id = l_gen_obj_id ;
2333 END IF;
2334 CLOSE c_gen_rec ;
2335
2336 IF g_debug = 1 THEN
2337 print_debug('Delete_Lot. After deleting Geneology Record', 9);
2338 END IF;
2339
2340 OPEN c_uom_conv_rec ;
2341 FETCH c_uom_conv_rec INTO l_uom_conv_rec ;
2342
2343 IF c_uom_conv_rec%FOUND THEN
2344 DELETE FROM mtl_lot_uom_class_conversions
2345 WHERE lot_number = p_lot_number ;
2346 END IF;
2347
2348 CLOSE c_uom_conv_rec ;
2349 IF g_debug = 1 THEN
2350 print_debug('Delete_Lot. After deleting UOM Conversion Record', 9);
2351 END IF;
2352
2353 IF g_debug = 1 THEN
2354 print_debug('End of the program Delete_Lot. Program has completed successfully ', 9);
2355 END IF;
2356
2357 EXCEPTION
2358 WHEN NO_DATA_FOUND THEN
2359 x_return_status := fnd_api.g_ret_sts_error;
2360 ROLLBACK TO inv_delete_lot;
2361 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2362 if( x_msg_count > 1 ) then
2363 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2364 end if;
2365 print_debug('In Delete_Lot, No data found ' || SQLERRM, 9);
2366 WHEN fnd_api.g_exc_error THEN
2367 x_return_status := fnd_api.g_ret_sts_error;
2368 ROLLBACK TO inv_delete_lot;
2369 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2370 if( x_msg_count > 1 ) then
2371 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2372 end if;
2373 print_debug('In Delete_Lot, g_exc_error ' || SQLERRM, 9);
2374 WHEN fnd_api.g_exc_unexpected_error THEN
2375 x_return_status := fnd_api.g_ret_sts_unexp_error;
2376 ROLLBACK TO inv_delete_lot;
2377 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2378 if( x_msg_count > 1 ) then
2379 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2380 end if;
2381 print_debug('In Delete_Lot, g_exc_unexpected_error ' || SQLERRM, 9);
2382 WHEN OTHERS THEN
2383 x_return_status := fnd_api.g_ret_sts_unexp_error;
2384 ROLLBACK TO inv_delete_lot;
2385 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
2386 if( x_msg_count > 1 ) then
2387 x_msg_data := fnd_msg_pub.get(x_msg_count, FND_API.G_FALSE);
2388 end if;
2389 print_debug('In Delete_Lot, Others ' || SQLERRM, 9);
2390
2391 END Delete_Lot;
2392
2393 /** INVCONV ANTHIYAG 04-Nov-2004 Start **/
2394
2395 FUNCTION Check_Existing_Lot_Db
2396 (
2397 p_org_id IN NUMBER
2398 ,p_inventory_item_id IN NUMBER
2399 ,p_lot_number IN VARCHAR2
2400 ) RETURN BOOLEAN
2401 IS
2402 PRAGMA AUTONOMOUS_TRANSACTION;
2403 l_exists NUMBER := 0;
2404 BEGIN
2405 IF p_org_id IS NOT NULL THEN
2406 BEGIN
2407 SELECT count('1')
2408 INTO l_exists
2409 FROM mtl_lot_numbers
2410 WHERE inventory_item_id = p_inventory_item_id
2411 AND organization_id = p_org_id
2412 AND lot_number = p_lot_number
2413 AND ROWNUM = 1;
2414 EXCEPTION
2415 WHEN no_data_found THEN
2416 l_exists := 0;
2417 END;
2418 IF NVL(l_exists,0) = 0 THEN
2419 RETURN FALSE;
2420 ELSE
2421 RETURN TRUE;
2422 END IF;
2423 ELSE
2424 RETURN FALSE;
2425 END IF;
2426 END CHECK_EXISTING_LOT_DB;
2427
2428 /** INVCONV ANTHIYAG 04-Nov-2004 End **/
2429
2430 END INV_LOT_API_PKG ;