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