[Home] [Help]
PACKAGE BODY: APPS.INV_MATERIAL_STATUS_PUB
Source
1 PACKAGE BODY INV_MATERIAL_STATUS_PUB as
2 /* $Header: INVMSPUB.pls 120.5.12020000.5 2013/02/04 11:54:47 rkatoori ship $ */
3
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'INV_MATERIAL_STATUS_PUB';
6
7 PROCEDURE update_status
8 ( p_api_version_number IN NUMBER
9 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
10 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
11 , x_return_status OUT NOCOPY VARCHAR2
12 , x_msg_count OUT NOCOPY NUMBER
13 , x_msg_data OUT NOCOPY VARCHAR2
14 , p_object_type IN VARCHAR2
15 , p_status_rec IN INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type
16 ) IS
17 l_api_version_number CONSTANT NUMBER := 1.0;
18 l_api_name CONSTANT VARCHAR2(30):= 'UPDATE_STATUS';
19 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
20 l_status_rec INV_MATERIAL_STATUS_PUB.mtl_status_update_rec_type;
21 l_msg_count NUMBER;
22 l_msg_data VARCHAR2(240);
23 l_lot_status_enabled VARCHAR2(1);
24 l_default_lot_status_id NUMBER;
25 l_serial_status_enabled VARCHAR2(1);
26 l_default_serial_status_id NUMBER;
27
28 l_default_status_id NUMBER := 0; -- Bug 13731486
29
30 l_old_status_id NUMBER; --Bug 14028205
31
32 -- Bug # 14709830
33 l_sub_fl BOOLEAN DEFAULT TRUE ;
34 l_onhand_fl BOOLEAN DEFAULT TRUE ;
35 l_loc_fl BOOLEAN DEFAULT TRUE ;
36 l_lot_fl BOOLEAN DEFAULT TRUE ;
37 l_ser_fl BOOLEAN DEFAULT TRUE ;
38
39
40 BEGIN
41 -- Standard call to check for call compatibility
42 IF NOT FND_API.Compatible_API_Call
43 ( l_api_version_number
44 , p_api_version_number
45 , l_api_name
46 , G_PKG_NAME
47 )
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51
52 -- Initialize message list.
53 IF fnd_api.to_boolean(p_init_msg_lst) THEN
54 fnd_msg_pub.initialize;
55 END IF;
56
57 -- Bug 13731486
58 if (inv_cache.set_org_rec(p_status_rec.organization_id)) then
59 l_default_status_id := inv_cache.org_rec.default_status_id;
60 end if;
61
62 if p_object_type = 'O' or p_object_type = 'S' then
63 -- check if the item is lot_serial status controlled
64 inv_material_status_grp.get_lot_serial_status_control(
65 p_organization_id => p_status_rec.organization_id
66 ,p_inventory_item_id => p_status_rec.inventory_item_id
67 ,x_return_status => l_return_status
68 ,x_msg_count => l_msg_count
69 ,x_msg_data => l_msg_data
70 ,x_lot_status_enabled => l_lot_status_enabled
71 ,x_default_lot_status_id => l_default_lot_status_id
72 ,x_serial_status_enabled => l_serial_status_enabled
73 ,x_default_serial_status_id => l_default_serial_status_id);
74 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
75 RAISE fnd_api.g_exc_unexpected_error;
76 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
77 RAISE fnd_api.g_exc_error;
78 END IF;
79
80 -- Bug 13731486 : Checking for onhand status enabled org.
81 if (l_lot_status_enabled = 'N' AND p_object_type = 'O' AND l_default_status_id IS NULL) or
82 (l_serial_status_enabled = 'N' AND p_object_type = 'S') then
83 RAISE fnd_api.g_exc_error;
84 end if;
85
86 end if;
87
88 l_status_rec := p_status_rec;
89 INV_MATERIAL_STATUS_PKG.Initialize_status_rec(l_status_rec);
90
91 if p_object_type = 'Z' THEN
92 --Bug 14028205
93 BEGIN
94 SELECT status_id INTO l_old_status_id FROM mtl_secondary_inventories
95 WHERE organization_id = l_status_rec.organization_id
96 and secondary_inventory_name = l_status_rec.zone_code;
97 EXCEPTION
98 WHEN OTHERS THEN
99 l_old_status_id := -1;
100 END;
101
102 /*Added the reservation check on status update for Bug 14709830*/
103 l_sub_fl := INV_MATERIAL_STATUS_PKG.validate_mtstatus(
104 p_old_status_id => l_old_status_id
105 ,p_new_status_id => l_status_rec.status_id
106 ,p_subinventory_code => l_status_rec.zone_code
107 ,p_locator_id => NULL
108 ,p_organization_id => l_status_rec.organization_id
109 ,p_inventory_item_id => l_status_rec.inventory_item_id) ;
110
111
112 IF ( l_sub_fl ) then
113 update mtl_secondary_inventories
114 set status_id = l_status_rec.status_id
115 , last_updated_by = l_status_rec.last_updated_by
116 , last_update_date = l_status_rec.last_update_date
117 , last_update_login = l_status_rec.last_update_login
118 where organization_id = l_status_rec.organization_id
119 and secondary_inventory_name = l_status_rec.zone_code;
120
121 ELSE
122 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_UPD_RESV_FAIL');
123 FND_MSG_PUB.ADD;
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127
128 elsif p_object_type = 'L' THEN
129 --Bug 14028205 Added the select statement
130 BEGIN
131 SELECT status_id INTO l_old_status_id FROM mtl_item_locations
132 WHERE organization_id = l_status_rec.organization_id
133 and inventory_location_id = l_status_rec.locator_id;
134 EXCEPTION
135 WHEN OTHERS THEN
136 l_old_status_id := -1;
137 END;
138
139 /*Added the reservation check on status update for Bug 14709830*/
140 l_loc_fl := INV_MATERIAL_STATUS_PKG.validate_mtstatus(
141 p_old_status_id => l_old_status_id
142 ,p_new_status_id => l_status_rec.status_id
143 ,p_subinventory_code => NULL
144 ,p_locator_id => l_status_rec.locator_id
145 ,p_organization_id => l_status_rec.organization_id
146 ,p_inventory_item_id => l_status_rec.inventory_item_id) ;
147
148
149 IF ( l_loc_fl ) then
150 update mtl_item_locations
151 set status_id = l_status_rec.status_id
152 , last_updated_by = l_status_rec.last_updated_by
153 , last_update_date = l_status_rec.last_update_date
154 , last_update_login = l_status_rec.last_update_login
155 where organization_id = l_status_rec.organization_id
156 and inventory_location_id = l_status_rec.locator_id;
157 ELSE
158 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_UPD_RESV_FAIL');
159 FND_MSG_PUB.ADD;
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 elsif p_object_type = 'O' THEN
164 --Bug 14028205 Added the select statement
165 BEGIN
166 SELECT status_id INTO l_old_status_id FROM mtl_lot_numbers
167 WHERE organization_id = l_status_rec.organization_id
168 and inventory_item_id = l_status_rec.inventory_item_id
169 and lot_number = l_status_rec.lot_number;
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_old_status_id := -1;
173 END;
174
175 /*Added the reservation check on status update for Bug 14709830*/
176 l_lot_fl := INV_MATERIAL_STATUS_PKG.validate_mtstatus(
177 p_old_status_id => l_old_status_id
178 ,p_new_status_id => l_status_rec.status_id
179 ,p_subinventory_code => NULL
180 ,p_locator_id => NULL
181 ,p_organization_id => l_status_rec.organization_id
182 ,p_inventory_item_id => l_status_rec.inventory_item_id
183 ,P_lot_number => l_status_rec.lot_number) ;
184
185
186 IF ( l_lot_fl ) then
187 update mtl_lot_numbers
188 set status_id = l_status_rec.status_id
189 , last_updated_by = l_status_rec.last_updated_by
190 , last_update_date = l_status_rec.last_update_date
191 , last_update_login = l_status_rec.last_update_login
192 where organization_id = l_status_rec.organization_id
193 and inventory_item_id = l_status_rec.inventory_item_id
194 and lot_number = l_status_rec.lot_number;
195 ELSE
196 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_UPD_RESV_FAIL');
197 FND_MSG_PUB.ADD;
198 RAISE FND_API.G_EXC_ERROR;
199 END IF;
200
201 elsif p_object_type = 'S' THEN
202 --Bug 14028205 Added the select statement
203 BEGIN
204 SELECT status_id INTO l_old_status_id FROM mtl_serial_numbers
205 WHERE inventory_item_id = l_status_rec.inventory_item_id
206 and serial_number = l_status_rec.serial_number;
207 EXCEPTION
208 WHEN OTHERS THEN
209 l_old_status_id := -1;
210 END;
211
212 /*Added the reservation check on status update for Bug 14709830*/
213 l_ser_fl := INV_MATERIAL_STATUS_PKG.validate_mtstatus(
214 p_old_status_id => l_old_status_id
215 ,p_new_status_id => l_status_rec.status_id
216 ,p_subinventory_code => NULL
217 ,p_locator_id => NULL
218 ,p_organization_id => l_status_rec.organization_id
219 ,p_inventory_item_id => l_status_rec.inventory_item_id);
220
221
222 IF ( l_ser_fl ) then
223 update mtl_serial_numbers
224 set status_id = l_status_rec.status_id
225 , last_updated_by = l_status_rec.last_updated_by
226 , last_update_date = l_status_rec.last_update_date
227 , last_update_login = l_status_rec.last_update_login
228 where current_organization_id = l_status_rec.organization_id
229 and inventory_item_id = l_status_rec.inventory_item_id
230 and serial_number = l_status_rec.serial_number;
231 if l_status_rec.to_serial_number is not null and
232 l_status_rec.serial_number <> l_status_rec.to_serial_number then
233 update mtl_serial_numbers
234 set status_id = l_status_rec.status_id
235 , last_updated_by = l_status_rec.last_updated_by
236 , last_update_date = l_status_rec.last_update_date
237 , last_update_login = l_status_rec.last_update_login
238 where current_organization_id = l_status_rec.organization_id
239 and inventory_item_id = l_status_rec.inventory_item_id
240 and serial_number > l_status_rec.serial_number and
241 serial_number <= l_status_rec.to_serial_number;
242 end if;
243 ELSE
244 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_UPD_RESV_FAIL');
245 FND_MSG_PUB.ADD;
246 RAISE FND_API.G_EXC_ERROR;
247 END IF;
248
249 -- Start of changes for # 6633612---------------
250 elsif p_object_type = 'H' then
251 -- Need to add code to make sure that not serial controlled one.
252 -- I think as this is in else of serial .. no need to check the serial control again
253
254 -- Bug 10258668
255 -- Modified the following update such that status of all the onhand records for an item can be
256 -- updated if the subinv info is not passed or all the records in a particular subinv will be updated
257 -- if the locator info is not passed. Similarly for lot numbers.
258
259 if l_status_rec.status_id is not null or l_status_rec.status_id <> 0 or l_status_rec.status_id <> -1 THEN
260 --Bug 14028205 Added the select statement
261 BEGIN
262 SELECT status_id INTO l_old_status_id FROM mtl_onhand_quantities_detail
263 WHERE inventory_item_id = l_status_rec.inventory_item_id
264 and organization_id = l_status_rec.organization_id
265 and subinventory_code = nvl(l_status_rec.zone_code, subinventory_code)
266 and nvl(lot_number, '@@@@') = nvl(l_status_rec.lot_number, nvl(lot_number, '@@@@'))
267 and nvl(locator_id, -9999) = nvl(l_status_rec.locator_id, nvl(locator_id, -9999))
268 and nvl(lpn_id, -9999) = nvl(l_status_rec.lpn_id, -9999);
269 EXCEPTION
270 WHEN OTHERS THEN
271 l_old_status_id := -1;
272 END;
273
274 /*Added the reservation check on status update for Bug 14709830*/
275 l_onhand_fl := INV_MATERIAL_STATUS_PKG.validate_mtstatus(
276 p_old_status_id => l_old_status_id
277 ,p_new_status_id => l_status_rec.status_id
278 ,p_subinventory_code => l_status_rec.zone_code
279 ,p_locator_id => l_status_rec.locator_id
280 ,p_organization_id => l_status_rec.organization_id
281 ,p_inventory_item_id => l_status_rec.inventory_item_id
282 ,P_lot_number => l_status_rec.lot_number
283 ,p_lpn_id => l_status_rec.lpn_id) ;
284
285 IF ( l_onhand_fl ) then
286 update mtl_onhand_quantities_detail
287 set status_id = l_status_rec.status_id
288 , last_updated_by = l_status_rec.last_updated_by
289 , last_update_date = l_status_rec.last_update_date
290 , last_update_login = l_status_rec.last_update_login
291 where inventory_item_id = l_status_rec.inventory_item_id
292 and organization_id = l_status_rec.organization_id
293 and subinventory_code = nvl(l_status_rec.zone_code, subinventory_code)
294 and nvl(lot_number, '@@@@') = nvl(l_status_rec.lot_number, nvl(lot_number, '@@@@'))
295 and nvl(locator_id, -9999) = nvl(l_status_rec.locator_id, nvl(locator_id, -9999))
296 and nvl(lpn_id, -9999) = nvl(l_status_rec.lpn_id, -9999);
297 ELSE
298 FND_MESSAGE.SET_NAME('INV', 'INV_STATUS_UPD_RESV_FAIL');
299 FND_MSG_PUB.ADD;
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 end if;
304 -- End of changes for # 6633612---------------
305 else
306 -- Onhand Material Status Support (6633612): Object type is passed as 'Q' from QtyManager.java to
307 -- avoid the update of onhand records. In this case we only want the new record to be inserted into
308 -- the history table.
309 if p_object_type <> 'Q' then
310 l_return_status := fnd_api.g_ret_sts_error;
311 end if;
312 end if;
313
314
315 -- Bug 6798024: For object_type Q, no update statement is executed, hence
316 -- we should not raise no-data-found error.
317 if ((sql%notfound) AND p_object_type <> 'Q') THEN
318 /*Bug#15939164 Added the below code to give a warning message when there exists no onhand to be updated*/
319 IF (p_object_type = 'H') THEN
320 --Dbms_Output.put_line ('before error');
321 FND_MESSAGE.SET_NAME('INV', 'INV_ZERO_ONHAND');
322 FND_MSG_PUB.ADD;
323 x_return_status := g_ret_sts_warning;
324 RETURN;
325 ELSE
326 RAISE No_Data_Found;
327 END IF;
328
329 end if;
330
331 --Bug 14579834 added the if for object_type 'Q'
332 IF ((l_old_status_id<> l_status_rec.status_id) or (p_object_type = 'Q')) THEN
333 -- Insert the update history to the update status history table
334 INV_MATERIAL_STATUS_PKG.Insert_status_history(p_status_rec);
335 END IF;
336
337
338 if( p_commit = FND_API.G_TRUE ) Then
339 commit;
340 end if;
341
342 x_return_status := l_return_status;
343
344 EXCEPTION
345
346 WHEN FND_API.G_EXC_ERROR THEN
347
348 -- Get message count and data
349 FND_MSG_PUB.Count_And_Get
350 ( p_count => x_msg_count
351 , p_data => x_msg_data
352 );
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 x_msg_data := FND_MSG_PUB.GET(X_MSG_COUNT, 'F');
355
356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358
359 -- Get message count and data
360 FND_MSG_PUB.Count_And_Get
361 ( p_count => x_msg_count
362 , p_data => x_msg_data
363 );
364
365 WHEN OTHERS THEN
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
367
368 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
369 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
370 END IF;
371
372 -- Get message count and data
373 FND_MSG_PUB.Count_And_Get
374 ( p_count => x_msg_count
375 , p_data => x_msg_data
376 );
377 END update_status;
378
379 END INV_MATERIAL_STATUS_PUB;