[Home] [Help]
PACKAGE BODY: APPS.GME_RESERVATIONS_PVT
Source
1 PACKAGE BODY gme_reservations_pvt AS
2 /* $Header: GMEVRSVB.pls 120.24.12010000.1 2008/07/25 10:31:51 appldev ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_RESERVATIONS_PVT';
5
6 /*************************************************************************************************/
7 /* Oracle Process Manufacturing Process Execution APIs */
8 /* */
9 /* File Name: GMEVRSVB.pls */
10 /* Contents: GME reservation related procedures. */
11 /* HISTORY */
12 /* SivakumarG Bug#4604943 29-MAR-2006 */
13 /* Created new procedure validate_mtl_for_reservation to take care of validations. And all */
14 /* validations in create_material_reservation,auto_detail_line will be take care of by this proc*/
15 /* the validation code in auto_detail_batch is removed as it will be taken care in */
16 /* auto_detail_line procedure */
17 /* Pawan Kumar bug 5139060 */
18 /* Made changes in create_batch_reservation to pass Suggestions mode (S) in the called by param */
19 /* to assess the total unreserved quantity */
20 /* Pawan kumar bug 5294184 */
21 /* add gme_common_pvt.g_rule_based_resv_horizon in auto_detail_batch */
22 /* Swapna K bug: 6665408 28-DEC-2007 */
23 /* Added call to procedure inv_quantity_tree_pub.clear_quantity_cache in the procedure */
24 /* auto_detail_line to clear the quantity cache and recreate it again */
25 /*************************************************************************************************/
26 PROCEDURE get_reservations_msca (
27 p_organization_id IN NUMBER
28 ,p_batch_id IN NUMBER
29 ,p_material_detail_id IN NUMBER
30 ,p_subinventory_code IN VARCHAR2
31 ,p_locator_id IN NUMBER
32 ,p_lot_number IN VARCHAR2
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_error_msg OUT NOCOPY VARCHAR2
35 ,x_rsrv_cursor OUT NOCOPY g_msca_resvns)
36 IS
37 l_date_format VARCHAR2 (100);
38 l_api_name VARCHAR2 (50) := 'get_reservations_msca';
39 BEGIN
40 x_return_status := fnd_api.g_ret_sts_success;
41 x_error_msg := ' ';
42 fnd_profile.get ('ICX_DATE_FORMAT_MASK', l_date_format);
43
44 OPEN x_rsrv_cursor
45 FOR
46 SELECT mr.reservation_id
47 ,TO_CHAR (mr.requirement_date, l_date_format)
48 ,mr.primary_uom_code, mr.reservation_uom_code
49 ,NVL (mr.reservation_quantity, 0)
50 ,NVL (mr.primary_reservation_quantity, 0)
51 ,mr.subinventory_code, mr.subinventory_id, mr.locator_id
52 ,mr.lot_number, mr.lot_number_id
53 ,NVL (mr.detailed_quantity, 0)
54 ,NVL (mr.secondary_detailed_quantity, 0)
55 ,NVL (mr.secondary_reservation_quantity, 0)
56 ,mr.secondary_uom_code, mr.inventory_item_id
57 ,loc.concatenated_segments
58 FROM mtl_reservations mr, wms_item_locations_kfv loc
59 WHERE mr.organization_id = p_organization_id
60 AND mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
61 AND mr.demand_source_header_id = p_batch_id
62 AND mr.demand_source_line_id = p_material_detail_id
63 AND NVL (mr.subinventory_code, '1') =
64 NVL (p_subinventory_code, '1')
65 AND NVL (mr.locator_id, -1) = NVL (p_locator_id, -1)
66 AND mr.organization_id = loc.organization_id(+)
67 AND mr.subinventory_code = loc.subinventory_code(+)
68 AND mr.locator_id = loc.inventory_location_id(+)
69 AND (p_lot_number IS NULL OR mr.lot_number = p_lot_number)
70 AND NOT EXISTS (SELECT 1
71 FROM mtl_material_transactions_temp
72 WHERE reservation_id = mr.reservation_id)
73 ORDER BY mr.requirement_date;
74 EXCEPTION
75 WHEN OTHERS THEN
76 IF g_debug <= gme_debug.g_log_unexpected THEN
77 gme_debug.put_line ( 'When others exception in '
78 || g_pkg_name
79 || '.'
80 || l_api_name
81 || ' Error is '
82 || SQLERRM);
83 END IF;
84
85 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
86 x_return_status := fnd_api.g_ret_sts_unexp_error;
87 x_error_msg := fnd_message.get;
88 END get_reservations_msca;
89
90 PROCEDURE create_batch_reservations (
91 p_batch_id IN NUMBER
92 ,p_timefence IN NUMBER DEFAULT 1000
93 ,x_return_status OUT NOCOPY VARCHAR2)
94 IS
95 l_api_name CONSTANT VARCHAR2 (30) := 'create_batch_reservations';
96 l_return_status VARCHAR2 (1);
97 l_mtl_dtl_rec gme_material_details%ROWTYPE;
98 l_resv_qty NUMBER DEFAULT NULL;
99 line_resvn_err EXCEPTION;
100 get_open_qty_err EXCEPTION;
101
102 CURSOR cur_reservable_ings
103 IS
104 SELECT d.*
105 FROM gme_material_details d, mtl_system_items i
106 WHERE d.batch_id = p_batch_id
107 AND d.line_type = -1
108 AND d.line_type = -1
109 AND ( NVL (p_timefence, 0) = 0
110 OR (d.material_requirement_date < SYSDATE + p_timefence) )
111 AND i.inventory_item_id = d.inventory_item_id
112 AND i.organization_id = d.organization_id
113 AND i.reservable_type = 1
114 AND d.phantom_type = 0
115 AND (i.lot_control_code < 2
116 OR i.lot_control_code > 1 AND i.lot_divisible_flag = 'Y')
117 ORDER BY d.line_no;
118 BEGIN
119 IF g_debug <= gme_debug.g_log_procedure THEN
120 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
121 || l_api_name);
122 END IF;
123
124 x_return_status := fnd_api.g_ret_sts_success;
125
126 FOR get_rec IN cur_reservable_ings LOOP
127 l_mtl_dtl_rec := get_rec;
128
129 IF (NVL (p_timefence, 0) <>
130 NVL (gme_common_pvt.g_reservation_timefence, 0) ) THEN
131 --Pawan Kumar as done in create_material_reservations
132 -- for bug 5139060
133 -- Use Suggestions mode (S) in the called by param to assess the total
134 -- unreserved quantity
135 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => l_mtl_dtl_rec
136 ,p_called_by => 'S'
137 ,x_open_qty => l_resv_qty
138 ,x_return_status => l_return_status);
139
140 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
141 RAISE get_open_qty_err;
142 END IF;
143 ELSE
144 l_resv_qty := l_mtl_dtl_rec.plan_qty;
145 END IF;
146 IF (g_debug <= gme_debug.g_log_unexpected) THEN
147 gme_debug.put_line ( g_pkg_name
148 || '.'
149 || l_api_name
150 || 'material_detail_id:'
151 || l_mtl_dtl_rec.material_detail_id
152 || ' has open qty of '
153 || l_resv_qty);
154 END IF;
155
156 IF (NVL (l_resv_qty, 0) > 0) THEN
157 create_material_reservation (p_matl_dtl_rec => l_mtl_dtl_rec
158 ,p_resv_qty => l_resv_qty
159 ,x_return_status => l_return_status);
160
161 IF (g_debug <= gme_debug.g_log_unexpected) THEN
162 gme_debug.put_line ( g_pkg_name
163 || '.'
164 || l_api_name
165 || ' create_material_reservation returns status of '
166 || l_return_status);
167 END IF;
168
169 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
170 RAISE line_resvn_err;
171 END IF;
172 END IF;
173 END LOOP;
174
175 IF g_debug <= gme_debug.g_log_procedure THEN
176 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
177 END IF;
178 EXCEPTION
179 WHEN get_open_qty_err THEN
180 x_return_status := l_return_status;
181 WHEN line_resvn_err THEN
182 x_return_status := l_return_status;
183 WHEN OTHERS THEN
184 IF g_debug <= gme_debug.g_log_unexpected THEN
185 gme_debug.put_line ( 'When others exception in '
186 || g_pkg_name
187 || '.'
188 || l_api_name
189 || ' Error is '
190 || SQLERRM);
191 END IF;
192
193 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
194 x_return_status := fnd_api.g_ret_sts_unexp_error;
195 END create_batch_reservations;
196
197 PROCEDURE create_material_reservation (
198 p_matl_dtl_rec IN gme_material_details%ROWTYPE
199 ,p_resv_qty IN NUMBER DEFAULT NULL
200 ,p_sec_resv_qty IN NUMBER DEFAULT NULL
201 ,p_resv_um IN VARCHAR2 DEFAULT NULL
202 ,p_subinventory IN VARCHAR2 DEFAULT NULL
203 ,p_locator_id IN NUMBER DEFAULT NULL
204 ,p_lot_number IN VARCHAR2 DEFAULT NULL
205 ,x_return_status OUT NOCOPY VARCHAR2)
206 IS
207 l_api_name CONSTANT VARCHAR2 (30) := 'create_material_reservation';
208 l_return_status VARCHAR2 (1);
209 l_msg_count NUMBER;
210 l_msg_data VARCHAR2 (2000);
211 l_qty_reserved NUMBER;
212 l_reservation_id NUMBER;
213 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
214 l_in_serial_num inv_reservation_global.serial_number_tbl_type;
215 l_out_serial_num inv_reservation_global.serial_number_tbl_type;
216
217 --Bug#4604943
218 invalid_mtl_for_rsrv EXCEPTION;
219 create_resvn_err EXCEPTION;
220
221 BEGIN
222 IF g_debug <= gme_debug.g_log_procedure THEN
223 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
224 || l_api_name);
225 gme_debug.put_line ('input value p_resv_qty => ' || p_resv_qty);
226 gme_debug.put_line ('input value p_sec_resv_qty => ' || p_sec_resv_qty);
227 gme_debug.put_line ('input value plan_qty => ' || p_matl_dtl_rec.plan_qty);
228 gme_debug.put_line ('input value resv_um => ' || p_resv_um );
229 gme_debug.put_line ('input value release_type => ' || p_matl_dtl_rec.release_type );
230 END IF;
231
232 x_return_status := fnd_api.g_ret_sts_success;
233
234 --Bug#4604943 Begin validate the batch and material line
235 validate_mtl_for_reservation(
236 p_material_detail_rec => p_matl_dtl_rec
237 ,x_return_status => l_return_status );
238 IF l_return_status <> fnd_api.g_ret_sts_success THEN
239 RAISE invalid_mtl_for_rsrv;
240 END IF;
241 --Bug#4604943 End
242 l_rsv_rec.requirement_date := p_matl_dtl_rec.material_requirement_date;
243 l_rsv_rec.organization_id := p_matl_dtl_rec.organization_id;
244 l_rsv_rec.inventory_item_id := p_matl_dtl_rec.inventory_item_id;
245 l_rsv_rec.demand_source_type_id := gme_common_pvt.g_txn_source_type;
246 l_rsv_rec.demand_source_header_id := p_matl_dtl_rec.batch_id;
247 l_rsv_rec.demand_source_line_id := p_matl_dtl_rec.material_detail_id;
248 l_rsv_rec.reservation_uom_code := NVL (p_resv_um, p_matl_dtl_rec.dtl_um);
249 l_rsv_rec.reservation_quantity :=
250 NVL (p_resv_qty, p_matl_dtl_rec.plan_qty);
251 l_rsv_rec.secondary_reservation_quantity := p_sec_resv_qty;
252 l_rsv_rec.revision := p_matl_dtl_rec.revision;
253 l_rsv_rec.subinventory_code := p_subinventory;
254 l_rsv_rec.locator_id := p_locator_id;
255 l_rsv_rec.lot_number := p_lot_number;
256 l_rsv_rec.lpn_id := NULL;
257 l_rsv_rec.demand_source_name := NULL;
258 l_rsv_rec.demand_source_delivery := NULL;
259 l_rsv_rec.primary_uom_code := NULL;
260 l_rsv_rec.primary_uom_id := NULL;
261 l_rsv_rec.secondary_uom_code := NULL;
262 l_rsv_rec.secondary_uom_id := NULL;
263 l_rsv_rec.reservation_uom_id := NULL;
264 l_rsv_rec.ship_ready_flag := NULL;
265 l_rsv_rec.attribute_category := NULL;
266 l_rsv_rec.attribute1 := NULL;
267 l_rsv_rec.attribute2 := NULL;
268 l_rsv_rec.attribute3 := NULL;
269 l_rsv_rec.attribute4 := NULL;
270 l_rsv_rec.attribute5 := NULL;
271 l_rsv_rec.attribute6 := NULL;
272 l_rsv_rec.attribute7 := NULL;
273 l_rsv_rec.attribute8 := NULL;
274 l_rsv_rec.attribute9 := NULL;
275 l_rsv_rec.attribute10 := NULL;
276 l_rsv_rec.attribute11 := NULL;
277 l_rsv_rec.attribute12 := NULL;
278 l_rsv_rec.attribute13 := NULL;
279 l_rsv_rec.attribute14 := NULL;
280 l_rsv_rec.attribute15 := NULL;
281 l_rsv_rec.subinventory_id := NULL;
282 l_rsv_rec.lot_number_id := NULL;
283 l_rsv_rec.pick_slip_number := NULL;
284 l_rsv_rec.primary_reservation_quantity := NULL;
285 l_rsv_rec.detailed_quantity := NULL;
286 l_rsv_rec.secondary_detailed_quantity := NULL;
287 l_rsv_rec.autodetail_group_id := NULL;
288 l_rsv_rec.external_source_code := NULL;
289 l_rsv_rec.external_source_line_id := NULL;
290 l_rsv_rec.supply_source_type_id :=
291 inv_reservation_global.g_source_type_inv;
292 l_rsv_rec.supply_source_header_id := NULL;
293 l_rsv_rec.supply_source_line_id := NULL;
294 l_rsv_rec.supply_source_name := NULL;
295 l_rsv_rec.supply_source_line_detail := NULL;
296
297 IF (g_debug <= gme_debug.g_log_statement) THEN
298 gme_debug.put_line ('Calling inv_reservation_pub.create_reservation');
299 END IF;
300
301 -- nsinghi Bug5176319. Commented p_force_reservation_flag parameter. As per inv team, onhand could be -ve
302 -- before reservation, and hence this parameter should not be used.
303
304 inv_reservation_pub.create_reservation
305 (p_api_version_number => 1.0
306 ,p_init_msg_lst => fnd_api.g_false
307 ,x_return_status => l_return_status
308 ,x_msg_count => l_msg_count
309 ,x_msg_data => l_msg_data
310 ,p_rsv_rec => l_rsv_rec
311 ,p_serial_number => l_in_serial_num
312 ,x_serial_number => l_out_serial_num
313 ,p_partial_reservation_flag => fnd_api.g_true
314 -- ,p_force_reservation_flag => fnd_api.g_true
315 ,p_validation_flag => fnd_api.g_true
316 ,x_quantity_reserved => l_qty_reserved
317 ,x_reservation_id => l_reservation_id
318 ,p_partial_rsv_exists => TRUE);
319
320 IF (g_debug <= gme_debug.g_log_unexpected) THEN
321 gme_debug.put_line ( g_pkg_name
322 || '.'
323 || l_api_name
324 || ' inv_reservation_pub.create_reservation returns status of '
325 || l_return_status
326 || ' for material_detail_id '
327 || p_matl_dtl_rec.material_detail_id
328 || ' qty reserved IS '
329 || l_qty_reserved );
330 END IF;
331 IF (l_return_status IN
332 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
333 RAISE create_resvn_err;
334 END IF;
335
336 IF g_debug <= gme_debug.g_log_procedure THEN
337 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
338 END IF;
339 EXCEPTION
340 WHEN create_resvn_err THEN
341 IF (g_debug <= gme_debug.g_log_error) THEN
342 gme_debug.put_line
343 ( 'inv_reservation_pub.create_reservation returns '
344 || l_return_status);
345 gme_debug.put_line ('error message is ' || l_msg_data);
346 END IF;
347
348 x_return_status := l_return_status;
349 --Bug#4604943 just pass the actual return status from validate procedure
350 WHEN invalid_mtl_for_rsrv THEN
351 x_return_status := l_return_status;
352 WHEN OTHERS THEN
353 IF g_debug <= gme_debug.g_log_unexpected THEN
354 gme_debug.put_line ( 'When others exception in '
355 || g_pkg_name
356 || '.'
357 || l_api_name
358 || ' Error is '
359 || SQLERRM);
360 END IF;
361
362 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
363 x_return_status := fnd_api.g_ret_sts_unexp_error;
364 END create_material_reservation;
365
366 PROCEDURE get_material_reservations (
367 p_organization_id IN NUMBER
368 ,p_batch_id IN NUMBER
369 ,p_material_detail_id IN NUMBER
370 ,p_dispense_ind IN VARCHAR2
371 ,x_return_status OUT NOCOPY VARCHAR2
372 ,x_reservations_tbl OUT NOCOPY gme_common_pvt.reservations_tab)
373 IS
374 l_api_name CONSTANT VARCHAR2 (30) := 'get_material_reservations';
375 l_msg_count NUMBER;
376 l_msg_data VARCHAR2(32767);
377 l_return_status VARCHAR2 (10);
378 CURSOR cur_reservations (
379 v_org_id NUMBER
380 ,v_batch_id NUMBER
381 ,v_material_detail_id NUMBER)
382 IS
383 SELECT mr.*
384 FROM mtl_reservations mr
385 WHERE organization_id = v_org_id
386 AND demand_source_type_id = gme_common_pvt.g_txn_source_type
387 AND demand_source_header_id = v_batch_id
388 AND demand_source_line_id = v_material_detail_id
389 AND NOT EXISTS (SELECT 1
390 FROM mtl_material_transactions_temp
391 WHERE reservation_id = mr.reservation_id)
392 ORDER BY mr.requirement_date, mr.reservation_id; -- nsinghi bug#5176319. Add mr.reservation_id in order by clause.
393 error_dispense_mat EXCEPTION;
394 BEGIN
395 IF g_debug <= gme_debug.g_log_procedure THEN
396 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
397 || l_api_name);
398 END IF;
399
400 x_return_status := fnd_api.g_ret_sts_success;
401
402 IF p_dispense_ind = 'Y' THEN
403
404 gmo_dispense_grp.GET_MATERIAL_DISPENSE_DATA (p_api_version => 1.0,
405 p_init_msg_list => 'F',
406 x_return_status => l_return_status,
407 x_msg_count => l_msg_count,
408 x_msg_data => l_msg_data,
409 p_material_detail_id => p_material_detail_id,
410 x_dispense_data => x_reservations_tbl
411 );
412 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
413 RAISE error_dispense_mat;
414 END IF;
415
416 ELSE
417 OPEN cur_reservations (p_organization_id
418 ,p_batch_id
419 ,p_material_detail_id);
420
421 FETCH cur_reservations
422 BULK COLLECT INTO x_reservations_tbl;
423
424 CLOSE cur_reservations;
425 END IF ;
426 IF g_debug <= gme_debug.g_log_procedure THEN
427 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
428 END IF;
429 EXCEPTION
430 WHEN error_dispense_mat THEN
431 x_return_status := l_return_status;
432 WHEN OTHERS THEN
433 IF g_debug <= gme_debug.g_log_unexpected THEN
434 gme_debug.put_line ( 'When others exception in '
435 || g_pkg_name
436 || '.'
437 || l_api_name
438 || ' Error is '
439 || SQLERRM);
440 END IF;
441
442 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
443 x_return_status := fnd_api.g_ret_sts_unexp_error;
444 END get_material_reservations;
445
446 FUNCTION reservation_fully_specified (
447 p_reservation_rec IN mtl_reservations%ROWTYPE
448 ,p_item_location_control IN NUMBER
449 ,p_item_restrict_locators IN NUMBER)
450 RETURN NUMBER
451 IS
452 x_reservation_type NUMBER := 0;
453 l_eff_locator_control NUMBER;
454 l_return_status VARCHAR2 (10);
455 l_api_name CONSTANT VARCHAR2 (30) := 'reservation_fully_specified';
456 BEGIN
457 IF g_debug <= gme_debug.g_log_procedure THEN
458 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
459 || l_api_name);
460 END IF;
461
462 IF (p_reservation_rec.subinventory_code IS NOT NULL) THEN
463 IF (p_reservation_rec.locator_id IS NOT NULL) THEN
464 x_reservation_type := 1; -- its DLR
465 ELSE
466 l_eff_locator_control :=
467 gme_common_pvt.eff_locator_control
468 (p_organization_id => p_reservation_rec.organization_id
469 ,p_org_control => gme_common_pvt.g_org_locator_control
470 ,p_subinventory => p_reservation_rec.subinventory_code
471 ,p_item_control => p_item_location_control
472 ,p_item_loc_restrict => p_item_restrict_locators
473 ,p_action => gme_common_pvt.g_ing_issue_txn_action);
474
475 IF (l_eff_locator_control = 1) THEN
476 x_reservation_type := 1; -- its DLR
477 ELSE
478 x_reservation_type := 2; -- its PLR
479 END IF;
480 END IF;
481 ELSIF ( p_reservation_rec.revision IS NOT NULL
482 OR p_reservation_rec.lot_number IS NOT NULL) THEN
483 x_reservation_type := 2; -- its PLR
484 END IF;
485
486 IF g_debug <= gme_debug.g_log_procedure THEN
487 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
488 END IF;
489
490 RETURN x_reservation_type;
491 EXCEPTION
492 WHEN OTHERS THEN
493 IF g_debug <= gme_debug.g_log_unexpected THEN
494 gme_debug.put_line ( 'When others exception in '
495 || g_pkg_name
496 || '.'
497 || l_api_name
498 || ' Error is '
499 || SQLERRM);
500 END IF;
501
502 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
503 RETURN -1;
504 END reservation_fully_specified;
505
506 PROCEDURE convert_partial_to_dlr (
507 p_reservation_rec IN mtl_reservations%ROWTYPE
508 ,p_material_dtl_rec IN gme_material_details%ROWTYPE
509 ,p_item_rec IN mtl_system_items%ROWTYPE
510 ,p_qty_check IN VARCHAR2 := fnd_api.g_false
511 ,x_reservation_rec OUT NOCOPY mtl_reservations%ROWTYPE
512 ,x_return_status OUT NOCOPY VARCHAR2)
513 IS
514 l_api_name CONSTANT VARCHAR2 (30) := 'convert_partial_to_dlr';
515 l_eff_locator_control NUMBER;
516 BEGIN
517 IF g_debug <= gme_debug.g_log_procedure THEN
518 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
519 || l_api_name);
520 END IF;
521
522 x_return_status := fnd_api.g_ret_sts_success;
523 x_reservation_rec := p_reservation_rec;
524
525 IF ( p_item_rec.lot_control_code > 1
526 AND x_reservation_rec.lot_number IS NULL) THEN
527 x_return_status := 'F';
528 RETURN;
529 END IF;
530
531 IF (p_reservation_rec.subinventory_code IS NULL) THEN
532 IF (p_material_dtl_rec.subinventory IS NOT NULL) THEN
533 x_reservation_rec.subinventory_code :=
534 p_material_dtl_rec.subinventory;
535 ELSE
536 x_return_status := 'F';
537 RETURN;
538 END IF;
539 END IF;
540 /* Bug 5441643 Added NVL condition for location control code*/
541 l_eff_locator_control :=
542 gme_common_pvt.eff_locator_control
543 (p_organization_id => x_reservation_rec.organization_id
544 ,p_org_control => gme_common_pvt.g_org_locator_control
545 ,p_subinventory => x_reservation_rec.subinventory_code
546 ,p_item_control => NVL(p_item_rec.location_control_code,1)
547 ,p_item_loc_restrict => p_item_rec.restrict_locators_code
548 ,p_action => gme_common_pvt.g_ing_issue_txn_action);
549
550 IF (l_eff_locator_control <> 1 AND p_reservation_rec.locator_id IS NULL) THEN
551 /* Bug 5441643 Added NVL condition for location control code*/
552 IF (NVL(p_item_rec.location_control_code,1) <> 1) THEN
553 IF ( p_material_dtl_rec.locator_id IS NOT NULL
554 AND p_material_dtl_rec.subinventory =
555 x_reservation_rec.subinventory_code) THEN
556 x_reservation_rec.locator_id := p_material_dtl_rec.locator_id;
557 ELSE
558 x_return_status := 'F';
559 RETURN;
560 END IF;
561 END IF;
562 END IF;
563
564 IF (p_qty_check = fnd_api.g_true) THEN
565 --QUERY TREE FOR ATT WITH RESVN DETAILS AND COMPARE QTY
566 NULL;
567 END IF;
568 EXCEPTION
569 WHEN OTHERS THEN
570 IF g_debug <= gme_debug.g_log_unexpected THEN
571 gme_debug.put_line ( 'When others exception in '
572 || g_pkg_name
573 || '.'
574 || l_api_name
575 || ' Error is '
576 || SQLERRM);
577 END IF;
578
579 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
580 x_return_status := fnd_api.g_ret_sts_unexp_error;
581 END convert_partial_to_dlr;
582
583 PROCEDURE delete_batch_reservations (
584 p_organization_id IN NUMBER
585 ,p_batch_id IN NUMBER
586 ,x_return_status OUT NOCOPY VARCHAR2)
587 IS
588 l_api_name CONSTANT VARCHAR2 (30) := 'delete_batch_reservations';
589 l_return_status VARCHAR2 (1);
590 delete_resvn_error EXCEPTION;
591
592 CURSOR cur_batch_materials
593 IS
594 SELECT d.material_detail_id, d.batch_id, d.organization_id
595 FROM gme_material_details d, mtl_system_items_b i
596 WHERE d.organization_id = p_organization_id
597 AND d.batch_id = p_batch_id
598 AND d.line_type = gme_common_pvt.g_line_type_ing
599 AND i.organization_id = d.organization_id
600 AND i.inventory_item_id = d.inventory_item_id
601 AND i.reservable_type = 1;
602 BEGIN
603 IF g_debug <= gme_debug.g_log_procedure THEN
604 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
605 || l_api_name);
606 END IF;
607
608 x_return_status := fnd_api.g_ret_sts_success;
609
610 FOR get_rec IN cur_batch_materials LOOP
611 gme_reservations_pvt.delete_material_reservations
612 (p_organization_id => get_rec.organization_id
613 ,p_batch_id => get_rec.batch_id
614 ,p_material_detail_id => get_rec.material_detail_id
615 ,x_return_status => l_return_status);
616
617 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
618 RAISE delete_resvn_error;
619 END IF;
620 END LOOP;
621
622 IF g_debug <= gme_debug.g_log_procedure THEN
623 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
624 END IF;
625 EXCEPTION
626 WHEN delete_resvn_error THEN
627 x_return_status := l_return_status;
628 WHEN OTHERS THEN
629 IF g_debug <= gme_debug.g_log_unexpected THEN
630 gme_debug.put_line ( 'When others exception in '
631 || g_pkg_name
632 || '.'
633 || l_api_name
634 || ' Error is '
635 || SQLERRM);
636 END IF;
637
638 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
639 x_return_status := fnd_api.g_ret_sts_unexp_error;
640 END delete_batch_reservations;
641
642 PROCEDURE delete_material_reservations (
643 p_organization_id IN NUMBER
644 ,p_batch_id IN NUMBER
645 ,p_material_detail_id IN NUMBER
646 ,x_return_status OUT NOCOPY VARCHAR2)
647 IS
648 l_api_name CONSTANT VARCHAR2 (30) := 'delete_material_reservations';
649 l_return_status VARCHAR2 (1);
650 l_rsv_tbl gme_common_pvt.reservations_tab;
651 del_resvn_error EXCEPTION;
652 BEGIN
653 IF g_debug <= gme_debug.g_log_procedure THEN
654 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
655 || l_api_name);
656 END IF;
657
658 IF (g_debug <= gme_debug.g_log_statement) THEN
659 gme_debug.put_line ('p_organization_id = ' || p_organization_id);
660 gme_debug.put_line ('p_batch_id = ' || p_batch_id);
661 gme_debug.put_line ('p_material_detail_id = ' || p_material_detail_id);
662 END IF;
663
664 x_return_status := fnd_api.g_ret_sts_success;
665 gme_reservations_pvt.get_material_reservations
666 (p_organization_id => p_organization_id
667 ,p_batch_id => p_batch_id
668 ,p_material_detail_id => p_material_detail_id
669 ,x_return_status => x_return_status
670 ,x_reservations_tbl => l_rsv_tbl);
671
672 FOR i IN 1 .. l_rsv_tbl.COUNT LOOP
673 gme_reservations_pvt.delete_reservation
674 (p_reservation_id => l_rsv_tbl (i).reservation_id
675 ,x_return_status => l_return_status);
676
677 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
678 RAISE del_resvn_error;
679 END IF;
680 END LOOP;
681
682 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
683 RAISE del_resvn_error;
684 END IF;
685
686 IF g_debug <= gme_debug.g_log_procedure THEN
687 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
688 END IF;
689 EXCEPTION
690 WHEN del_resvn_error THEN
691 x_return_status := l_return_status;
692 WHEN OTHERS THEN
693 IF g_debug <= gme_debug.g_log_unexpected THEN
694 gme_debug.put_line ( 'When others exception in '
695 || g_pkg_name
696 || '.'
697 || l_api_name
698 || ' Error is '
699 || SQLERRM);
700 END IF;
701
702 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
703 x_return_status := fnd_api.g_ret_sts_unexp_error;
704 END delete_material_reservations;
705
706 PROCEDURE delete_reservation (
707 p_reservation_id IN NUMBER
708 ,x_return_status OUT NOCOPY VARCHAR2)
709 IS
710 l_api_name CONSTANT VARCHAR2 (30) := 'delete_reservation';
711 l_return_status VARCHAR2 (1);
712 l_msg_count NUMBER;
713 l_msg_data VARCHAR2 (2000);
714 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
715 l_serial_number inv_reservation_global.serial_number_tbl_type;
716 del_resvn_error EXCEPTION;
717 BEGIN
718 IF g_debug <= gme_debug.g_log_procedure THEN
719 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
720 || l_api_name);
721 END IF;
722
723 IF (g_debug <= gme_debug.g_log_statement) THEN
724 gme_debug.put_line ('p_reservation_id = ' || p_reservation_id);
725 END IF;
726
727 x_return_status := fnd_api.g_ret_sts_success;
728 l_rsv_rec.reservation_id := p_reservation_id;
729
730 IF (g_debug <= gme_debug.g_log_statement) THEN
731 gme_debug.put_line ('Calling inv_reservation_pub.delete_reservation');
732 END IF;
733
734 inv_reservation_pub.delete_reservation
735 (p_api_version_number => 1.0
736 ,p_init_msg_lst => fnd_api.g_false
737 ,x_return_status => l_return_status
738 ,x_msg_count => l_msg_count
739 ,x_msg_data => l_msg_data
740 ,p_rsv_rec => l_rsv_rec
741 ,p_serial_number => l_serial_number);
742
743 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
744 RAISE del_resvn_error;
745 END IF;
746
747 IF g_debug <= gme_debug.g_log_procedure THEN
748 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
749 END IF;
750 EXCEPTION
751 WHEN del_resvn_error THEN
752 IF (g_debug <= gme_debug.g_log_error) THEN
753 gme_debug.put_line
754 ( 'inv_reservation_pub.delete_reservation returns '
755 || l_return_status);
756 gme_debug.put_line ('error message is ' || l_msg_data);
757 END IF;
758
759 x_return_status := l_return_status;
760 WHEN OTHERS THEN
761 IF g_debug <= gme_debug.g_log_unexpected THEN
762 gme_debug.put_line ( 'When others exception in '
763 || g_pkg_name
764 || '.'
765 || l_api_name
766 || ' Error is '
767 || SQLERRM);
768 END IF;
769
770 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
771 x_return_status := fnd_api.g_ret_sts_unexp_error;
772 END delete_reservation;
773
774 PROCEDURE get_reservation_dtl_qty (
775 p_reservation_rec IN mtl_reservations%ROWTYPE
776 ,p_uom_code IN VARCHAR2
777 ,x_qty OUT NOCOPY NUMBER
778 ,x_return_status OUT NOCOPY VARCHAR2)
779 IS
780 l_api_name CONSTANT VARCHAR2 (30) := 'get_reservation_dtl_qty';
781 l_item_no VARCHAR2 (2000);
782 l_from_uom VARCHAR2 (3);
783 l_to_uom VARCHAR2 (3);
784 l_temp_qty NUMBER;
785
786 CURSOR cur_item_no (v_inventory_item_id NUMBER, v_org_id NUMBER)
787 IS
788 SELECT concatenated_segments
789 FROM mtl_system_items_kfv
790 WHERE inventory_item_id = v_inventory_item_id
791 AND organization_id = v_org_id;
792
793 uom_conv_error EXCEPTION;
794 BEGIN
795 IF (p_reservation_rec.reservation_uom_code = p_uom_code) THEN
796 l_temp_qty := p_reservation_rec.reservation_quantity;
797 ELSIF (p_reservation_rec.primary_uom_code = p_uom_code) THEN
798 l_temp_qty := p_reservation_rec.primary_reservation_quantity;
799 ELSIF (p_reservation_rec.secondary_uom_code = p_uom_code) THEN
800 l_temp_qty := p_reservation_rec.secondary_reservation_quantity;
801 ELSE
802 l_temp_qty :=
803 inv_convert.inv_um_convert
804 (item_id => p_reservation_rec.inventory_item_id
805 ,lot_number => p_reservation_rec.lot_number
806 ,organization_id => p_reservation_rec.organization_id
807 ,PRECISION => gme_common_pvt.g_precision
808 ,from_quantity => p_reservation_rec.reservation_quantity
809 ,from_unit => p_reservation_rec.reservation_uom_code
810 ,to_unit => p_uom_code
811 ,from_name => NULL
812 ,to_name => NULL);
813
814 IF (l_temp_qty < 0) THEN
815 OPEN cur_item_no (p_reservation_rec.inventory_item_id
816 ,p_reservation_rec.organization_id);
817
818 FETCH cur_item_no
819 INTO l_item_no;
820
821 CLOSE cur_item_no;
822
823 l_from_uom := p_reservation_rec.reservation_uom_code;
824 l_to_uom := p_uom_code;
825 RAISE uom_conv_error;
826 END IF;
827 END IF;
828
829 x_qty := l_temp_qty;
830 EXCEPTION
831 WHEN uom_conv_error THEN
832 x_return_status := fnd_api.g_ret_sts_error;
833 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
834 fnd_message.set_token ('ITEM_NO', l_item_no);
835 fnd_message.set_token ('FROM_UOM', l_from_uom);
836 fnd_message.set_token ('TO_UOM', l_to_uom);
837 WHEN OTHERS THEN
838 IF g_debug <= gme_debug.g_log_unexpected THEN
839 gme_debug.put_line ( 'When others exception in '
840 || g_pkg_name
841 || '.'
842 || l_api_name
843 || ' Error is '
844 || SQLERRM);
845 END IF;
846
847 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
848 x_return_status := fnd_api.g_ret_sts_unexp_error;
849 END get_reservation_dtl_qty;
850
851 PROCEDURE get_reserved_qty (
852 p_mtl_dtl_rec IN gme_material_details%ROWTYPE
853 ,p_supply_sub_only IN VARCHAR2 DEFAULT 'F'
854 ,x_reserved_qty OUT NOCOPY NUMBER
855 ,x_return_status OUT NOCOPY VARCHAR2)
856 IS
857 l_api_name CONSTANT VARCHAR2 (30) := 'get_reserved_qty';
858 l_mtl_dtl_rec gme_material_details%ROWTYPE;
859 l_resv_tbl gme_common_pvt.reservations_tab;
860 l_temp_qty NUMBER;
861 l_return_status VARCHAR2 (1);
862
863 CURSOR cur_get_resvns
864 IS
865 SELECT mr.*
866 FROM mtl_reservations mr
867 WHERE mr.demand_source_type_id = gme_common_pvt.g_txn_source_type
868 AND mr.demand_source_header_id = l_mtl_dtl_rec.batch_id
869 AND mr.demand_source_line_id = l_mtl_dtl_rec.material_detail_id
870 AND ( (p_supply_sub_only = fnd_api.g_false)
871 OR (mr.subinventory_code = l_mtl_dtl_rec.subinventory) )
872 AND NOT EXISTS (SELECT 1
873 FROM mtl_material_transactions_temp
874 WHERE reservation_id = mr.reservation_id);
875
876 matl_fetch_error EXCEPTION;
877 get_resv_qty_error EXCEPTION;
878 BEGIN
879 IF g_debug <= gme_debug.g_log_procedure THEN
880 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
881 || l_api_name);
882 END IF;
883
884 x_return_status := fnd_api.g_ret_sts_success;
885 x_reserved_qty := 0;
886
887 IF (p_mtl_dtl_rec.inventory_item_id IS NULL
888 OR p_mtl_dtl_rec.dtl_um IS NULL
889 OR p_mtl_dtl_rec.batch_id IS NULL
890 OR (p_supply_sub_only = fnd_api.g_true AND p_mtl_dtl_rec.subinventory IS NULL)) THEN
891 IF (NOT (gme_material_details_dbl.fetch_row (p_mtl_dtl_rec
892 ,l_mtl_dtl_rec) ) ) THEN
893 RAISE matl_fetch_error;
894 END IF;
895 ELSE
896 l_mtl_dtl_rec := p_mtl_dtl_rec;
897 END IF;
898
899 OPEN cur_get_resvns;
900
901 FETCH cur_get_resvns
902 BULK COLLECT INTO l_resv_tbl;
903
904 CLOSE cur_get_resvns;
905
906 FOR i IN 1 .. l_resv_tbl.COUNT LOOP
907 get_reservation_dtl_qty (p_reservation_rec => l_resv_tbl (i)
908 ,p_uom_code => l_mtl_dtl_rec.dtl_um
909 ,x_qty => l_temp_qty
910 ,x_return_status => l_return_status);
911
912 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
913 RAISE get_resv_qty_error;
914 END IF;
915
916 x_reserved_qty := x_reserved_qty + l_temp_qty;
917 END LOOP;
918
919 IF g_debug <= gme_debug.g_log_procedure THEN
920 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
921 END IF;
922 EXCEPTION
923 WHEN matl_fetch_error THEN
924 x_return_status := fnd_api.g_ret_sts_error;
925 WHEN get_resv_qty_error THEN
926 x_return_status := l_return_status;
927 WHEN OTHERS THEN
928 IF g_debug <= gme_debug.g_log_unexpected THEN
929 gme_debug.put_line ( 'When others exception in '
930 || g_pkg_name
931 || '.'
932 || l_api_name
933 || ' Error is '
934 || SQLERRM);
935 END IF;
936
937 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
938 x_return_status := fnd_api.g_ret_sts_unexp_error;
939 END get_reserved_qty;
940
941 PROCEDURE relieve_reservation (
942 p_reservation_id IN NUMBER
943 ,p_prim_relieve_qty IN NUMBER
944 ,x_return_status OUT NOCOPY VARCHAR2)
945 IS
946 l_api_name CONSTANT VARCHAR2 (30) := 'relieve_reservation';
947 l_return_status VARCHAR2 (1);
948 l_msg_count NUMBER;
949 l_prim_relieve_qty NUMBER;
950 l_prim_remain_qty NUMBER;
951 l_msg_data VARCHAR2 (2000);
952 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
953 l_serial_number inv_reservation_global.serial_number_tbl_type;
954 relieve_resvn_error EXCEPTION;
955 BEGIN
956 IF g_debug <= gme_debug.g_log_procedure THEN
957 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
958 || l_api_name);
959 END IF;
960
961 x_return_status := fnd_api.g_ret_sts_success;
962 l_rsv_rec.reservation_id := p_reservation_id;
963
964 IF g_debug <= gme_debug.g_log_statement THEN
965 gme_debug.put_line
966 ( g_pkg_name
967 || '.'
968 || l_api_name
969 || ':Calling inv_reservation_pub.relieve_reservation with reservation_id = '
970 || p_reservation_id
971 || ' relieve_qty '
972 || p_prim_relieve_qty);
973 END IF;
974
975 inv_reservation_pub.relieve_reservation
976 (p_api_version_number => 1.0
977 ,p_init_msg_lst => fnd_api.g_false
978 ,x_return_status => l_return_status
979 ,x_msg_count => l_msg_count
980 ,x_msg_data => l_msg_data
981 ,p_rsv_rec => l_rsv_rec
982 ,p_primary_relieved_quantity => p_prim_relieve_qty
983 ,p_relieve_all => fnd_api.g_false
984 ,p_original_serial_number => l_serial_number
985 ,p_validation_flag => fnd_api.g_true
986 ,x_primary_relieved_quantity => l_prim_relieve_qty
987 ,x_primary_remain_quantity => l_prim_remain_qty);
988
989 IF g_debug <= gme_debug.g_log_statement THEN
990 gme_debug.put_line
991 ( g_pkg_name
992 || '.'
993 || l_api_name
994 || 'Return status from inv_reservation_pub.relieve_reservation is '
995 || l_return_status);
996 gme_debug.put_line ( g_pkg_name
997 || '.'
998 || l_api_name
999 || 'Error is :'
1000 || l_msg_data);
1001 END IF;
1002
1003 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1004 RAISE relieve_resvn_error;
1005 END IF;
1006
1007 IF g_debug <= gme_debug.g_log_procedure THEN
1008 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1009 END IF;
1010 EXCEPTION
1011 WHEN relieve_resvn_error THEN
1012 x_return_status := l_return_status;
1013 WHEN OTHERS THEN
1014 IF g_debug <= gme_debug.g_log_unexpected THEN
1015 gme_debug.put_line ( 'When others exception in '
1016 || g_pkg_name
1017 || '.'
1018 || l_api_name
1019 || ' Error is '
1020 || SQLERRM);
1021 END IF;
1022
1023 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1024 x_return_status := fnd_api.g_ret_sts_unexp_error;
1025 END relieve_reservation;
1026
1027 /* Pass only values needed to be updated reservation ID is required */
1028 PROCEDURE update_reservation (
1029 p_reservation_id IN NUMBER
1030 ,p_revision IN VARCHAR2 DEFAULT NULL
1031 ,p_subinventory IN VARCHAR2 DEFAULT NULL
1032 ,p_locator_id IN NUMBER DEFAULT NULL
1033 ,p_lot_number IN VARCHAR2 DEFAULT NULL
1034 ,p_new_qty IN NUMBER DEFAULT NULL
1035 ,p_new_sec_qty IN NUMBER DEFAULT NULL
1036 ,p_new_uom IN VARCHAR2 DEFAULT NULL
1037 ,p_new_date IN DATE DEFAULT NULL
1038 ,x_return_status OUT NOCOPY VARCHAR2)
1039 IS
1040 l_api_name CONSTANT VARCHAR2 (30) := 'update_reservation';
1041 l_return_status VARCHAR2 (1);
1042 l_msg_count NUMBER;
1043 l_msg_data VARCHAR2 (2000);
1044 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1045 l_orig_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1046 l_serial_number inv_reservation_global.serial_number_tbl_type;
1047 update_resvn_error EXCEPTION;
1048 query_resvn_error EXCEPTION;
1049 BEGIN
1050 IF g_debug <= gme_debug.g_log_procedure THEN
1051 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1052 || l_api_name);
1053 END IF;
1054
1055 x_return_status := fnd_api.g_ret_sts_success;
1056 query_reservation (p_reservation_id => p_reservation_id
1057 ,x_reservation_rec => l_orig_rsv_rec
1058 ,x_return_status => l_return_status);
1059
1060 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1061 RAISE query_resvn_error;
1062 END IF;
1063
1064 l_rsv_rec.reservation_id := p_reservation_id;
1065 l_rsv_rec.revision := p_revision;
1066 l_rsv_rec.subinventory_code := p_subinventory;
1067 l_rsv_rec.locator_id := p_locator_id;
1068 l_rsv_rec.lot_number := p_lot_number;
1069 l_rsv_rec.reservation_quantity := p_new_qty;
1070 l_rsv_rec.secondary_reservation_quantity := p_new_sec_qty;
1071 l_rsv_rec.reservation_uom_code := p_new_uom;
1072 l_rsv_rec.requirement_date := p_new_date;
1073
1074 IF g_debug <= gme_debug.g_log_statement THEN
1075 gme_debug.put_line
1076 ( g_pkg_name
1077 || '.'
1078 || l_api_name
1079 || ':Calling inv_reservation_pub.update_reservation with reservation_id = '
1080 || p_reservation_id);
1081 END IF;
1082
1083 inv_reservation_pub.update_reservation
1084 (p_api_version_number => 1.0
1085 ,p_init_msg_lst => fnd_api.g_false
1086 ,x_return_status => l_return_status
1087 ,x_msg_count => l_msg_count
1088 ,x_msg_data => l_msg_data
1089 ,p_original_rsv_rec => l_orig_rsv_rec
1090 ,p_to_rsv_rec => l_rsv_rec
1091 ,p_original_serial_number => l_serial_number
1092 ,p_to_serial_number => l_serial_number
1093 ,p_validation_flag => fnd_api.g_true
1094 ,p_check_availability => fnd_api.g_true);
1095
1096 IF g_debug <= gme_debug.g_log_statement THEN
1097 gme_debug.put_line
1098 ( g_pkg_name
1099 || '.'
1100 || l_api_name
1101 || 'Return status from inv_reservation_pub.update_reservation is '
1102 || l_return_status);
1103 gme_debug.put_line ( g_pkg_name
1104 || '.'
1105 || l_api_name
1106 || 'Error is :'
1107 || l_msg_data);
1108 END IF;
1109
1110 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1111 RAISE update_resvn_error;
1112 END IF;
1113
1114 IF g_debug <= gme_debug.g_log_procedure THEN
1115 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1116 END IF;
1117 EXCEPTION
1118 WHEN query_resvn_error THEN
1119 x_return_status := l_return_status;
1120 WHEN update_resvn_error THEN
1121 x_return_status := l_return_status;
1122 WHEN OTHERS THEN
1123 IF g_debug <= gme_debug.g_log_unexpected THEN
1124 gme_debug.put_line ( 'When others exception in '
1125 || g_pkg_name
1126 || '.'
1127 || l_api_name
1128 || ' Error is '
1129 || SQLERRM);
1130 END IF;
1131
1132 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1133 x_return_status := fnd_api.g_ret_sts_unexp_error;
1134 END update_reservation;
1135
1136 /* Pass reservation ID to be queried */
1137 PROCEDURE query_reservation (
1138 p_reservation_id IN NUMBER
1139 ,x_reservation_rec OUT NOCOPY inv_reservation_global.mtl_reservation_rec_type
1140 ,x_return_status OUT NOCOPY VARCHAR2)
1141 IS
1142 l_api_name CONSTANT VARCHAR2 (30) := 'query_reservation';
1143 l_return_status VARCHAR2 (1);
1144 l_error_code NUMBER;
1145 l_rsv_count NUMBER;
1146 l_msg_count NUMBER;
1147 l_msg_data VARCHAR2 (2000);
1148 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1149 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1150 l_serial_number inv_reservation_global.serial_number_tbl_type;
1151 update_resvn_error EXCEPTION;
1152 BEGIN
1153 IF g_debug <= gme_debug.g_log_procedure THEN
1154 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1155 || l_api_name);
1156 END IF;
1157
1158 x_return_status := fnd_api.g_ret_sts_success;
1159 l_rsv_rec.reservation_id := p_reservation_id;
1160
1161 IF g_debug <= gme_debug.g_log_statement THEN
1162 gme_debug.put_line
1163 ( g_pkg_name
1164 || '.'
1165 || l_api_name
1166 || ':Calling inv_reservation_pub.query_reservation with reservation_id = '
1167 || p_reservation_id);
1168 END IF;
1169
1170 inv_reservation_pub.query_reservation
1171 (p_api_version_number => 1.0
1172 ,p_init_msg_lst => fnd_api.g_false
1173 ,x_return_status => l_return_status
1174 ,x_msg_count => l_msg_count
1175 ,x_msg_data => l_msg_data
1176 ,p_query_input => l_rsv_rec
1177 ,p_lock_records => fnd_api.g_false
1178 ,p_sort_by_req_date => inv_reservation_global.g_query_no_sort
1179 ,p_cancel_order_mode => inv_reservation_global.g_cancel_order_no
1180 ,x_mtl_reservation_tbl => l_rsv_tbl
1181 ,x_mtl_reservation_tbl_count => l_rsv_count
1182 ,x_error_code => l_error_code);
1183
1184 IF g_debug <= gme_debug.g_log_statement THEN
1185 gme_debug.put_line
1186 ( g_pkg_name
1187 || '.'
1188 || l_api_name
1189 || 'Return status from inv_reservation_pub.query_reservation is '
1190 || l_return_status);
1191 gme_debug.put_line ( g_pkg_name
1192 || '.'
1193 || l_api_name
1194 || 'Error is :'
1195 || l_msg_data);
1196 END IF;
1197
1198 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1199 RAISE update_resvn_error;
1200 END IF;
1201
1202 x_reservation_rec := l_rsv_tbl (1);
1203
1204 IF g_debug <= gme_debug.g_log_procedure THEN
1205 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1206 END IF;
1207 EXCEPTION
1208 WHEN update_resvn_error THEN
1209 x_return_status := l_return_status;
1210 WHEN OTHERS THEN
1211 IF g_debug <= gme_debug.g_log_unexpected THEN
1212 gme_debug.put_line ( 'When others exception in '
1213 || g_pkg_name
1214 || '.'
1215 || l_api_name
1216 || ' Error is '
1217 || SQLERRM);
1218 END IF;
1219
1220 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1221 x_return_status := fnd_api.g_ret_sts_unexp_error;
1222 END query_reservation;
1223
1224 FUNCTION pending_reservations_exist (
1225 p_organization_id IN NUMBER
1226 ,p_batch_id IN NUMBER
1227 ,p_material_detail_id IN NUMBER)
1228 RETURN BOOLEAN
1229 IS
1230 l_api_name CONSTANT VARCHAR2 (30) := 'pending_reservations_exist';
1231 l_temp NUMBER;
1232
1233 CURSOR cur_reservations (
1234 v_org_id NUMBER
1235 ,v_batch_id NUMBER
1236 ,v_material_detail_id NUMBER)
1237 IS
1238 SELECT 1
1239 FROM DUAL
1240 WHERE EXISTS (
1241 SELECT 1
1242 FROM mtl_reservations mr
1243 WHERE organization_id = v_org_id
1244 AND demand_source_type_id =
1245 gme_common_pvt.g_txn_source_type
1246 AND demand_source_header_id = v_batch_id
1247 AND demand_source_line_id = v_material_detail_id
1248 AND NOT EXISTS (
1249 SELECT 1
1250 FROM mtl_material_transactions_temp
1251 WHERE reservation_id =
1252 mr.reservation_id) );
1253 BEGIN
1254 IF g_debug <= gme_debug.g_log_procedure THEN
1255 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1256 || l_api_name);
1257 END IF;
1258
1259 OPEN cur_reservations (p_organization_id
1260 ,p_batch_id
1261 ,p_material_detail_id);
1262
1263 FETCH cur_reservations
1264 INTO l_temp;
1265
1266 CLOSE cur_reservations;
1267
1268 IF (NVL (l_temp, 0) = 1) THEN
1269 RETURN TRUE;
1270 ELSE
1271 RETURN FALSE;
1272 END IF;
1273
1274 IF g_debug <= gme_debug.g_log_procedure THEN
1275 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1276 END IF;
1277 EXCEPTION
1278 WHEN OTHERS THEN
1279 IF g_debug <= gme_debug.g_log_unexpected THEN
1280 gme_debug.put_line ( 'When others exception in '
1281 || g_pkg_name
1282 || '.'
1283 || l_api_name
1284 || ' Error is '
1285 || SQLERRM);
1286 END IF;
1287
1288 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1289 RETURN FALSE;
1290 END pending_reservations_exist;
1291
1292 PROCEDURE convert_dtl_reservation (
1293 p_reservation_rec IN mtl_reservations%ROWTYPE
1294 ,p_material_details_rec IN gme_material_details%ROWTYPE
1295 ,p_qty_convert IN NUMBER := NULL
1296 ,x_message_count OUT NOCOPY NUMBER
1297 ,x_message_list OUT NOCOPY VARCHAR2
1298 ,x_return_status OUT NOCOPY VARCHAR2)
1299 IS
1300 l_api_name CONSTANT VARCHAR2 (30) := 'CONVERT_DTL_RESERVATION';
1301 error_unexpected EXCEPTION;
1302 fetch_error EXCEPTION;
1303 validation_error EXCEPTION;
1304 create_transaction_error EXCEPTION;
1305 detail_reservation_error EXCEPTION;
1306 uom_conversion_failure EXCEPTION;
1307 l_item_rec mtl_system_items%ROWTYPE;
1308 l_reservation_rec mtl_reservations%ROWTYPE;
1309 l_qty_convert NUMBER;
1310 l_rsv_type NUMBER;
1311 l_return_status VARCHAR2 (1);
1312 l_actual_qty NUMBER;
1313
1314 CURSOR cur_fetch_item (v_org_id NUMBER, v_inventory_item_id NUMBER)
1315 IS
1316 SELECT *
1317 FROM mtl_system_items_b
1318 WHERE organization_id = v_org_id
1319 AND inventory_item_id = v_inventory_item_id;
1320 BEGIN
1321 IF g_debug <= gme_debug.g_log_procedure THEN
1322 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1323 || l_api_name);
1324 END IF;
1325
1326 /* Set the return status to success initially */
1327 x_return_status := fnd_api.g_ret_sts_success;
1328
1329 /* Retrieve item row */
1330 OPEN cur_fetch_item (p_material_details_rec.organization_id
1331 ,p_material_details_rec.inventory_item_id);
1332
1333 FETCH cur_fetch_item
1334 INTO l_item_rec;
1335
1336 IF cur_fetch_item%NOTFOUND THEN
1337 CLOSE cur_fetch_item;
1338
1339 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1340 ,'TABLE_NAME'
1341 ,'MTL_SYSTEM_ITEMS');
1342
1343 IF g_debug <= gme_debug.g_log_statement THEN
1344 gme_debug.put_line
1345 ( g_pkg_name
1346 || '.'
1347 || l_api_name
1348 || ' Retrieval failure against mtl_system_items using id of '
1349 || p_material_details_rec.inventory_item_id);
1350 END IF;
1351
1352 RAISE fetch_error;
1353 END IF;
1354
1355 CLOSE cur_fetch_item;
1356
1357 /* Ensure the item is transaction enabled */
1358 IF l_item_rec.mtl_transactions_enabled_flag <> 'Y' THEN
1359 gme_common_pvt.log_message ('GME_ITEM_NOT_TRANSACTABLE');
1360 RAISE validation_error;
1361 END IF;
1362
1363 gme_common_pvt.g_move_to_temp := fnd_api.g_false;
1364 /* Verify that the reservation is fully detailed */
1365 l_rsv_type :=
1366 gme_reservations_pvt.reservation_fully_specified
1367 (p_reservation_rec => p_reservation_rec
1368 ,p_item_location_control => NVL(l_item_rec.location_control_code,1)
1369 ,p_item_restrict_locators => l_item_rec.restrict_locators_code);
1370
1371 IF g_debug <= gme_debug.g_log_statement THEN
1372 gme_debug.put_line
1373 ( g_pkg_name
1374 || '.'
1375 || l_api_name
1376 || ' Return rsv_type from gme_reservations_pvt.reservation_fully_specified is '
1377 || TO_CHAR (l_rsv_type) );
1378 END IF;
1379
1380 IF l_rsv_type = -1 THEN
1381 gme_common_pvt.log_message ('GME_RSV_DETAIL_REQUIRED');
1382 RAISE error_unexpected;
1383 END IF;
1384
1385 IF l_rsv_type IN (0, 2) THEN -- Not fully detailed
1386 /* Detail the reservation to sub, locator level where possible */
1387 /* There must be sufficient inventory to support the reservation */
1388 gme_reservations_pvt.convert_partial_to_dlr
1389 (p_reservation_rec => p_reservation_rec
1390 ,p_material_dtl_rec => p_material_details_rec
1391 ,p_item_rec => l_item_rec
1392 ,x_reservation_rec => l_reservation_rec
1393 ,x_return_status => x_return_status);
1394
1395 IF g_debug <= gme_debug.g_log_statement THEN
1396 gme_debug.put_line
1397 ( g_pkg_name
1398 || '.'
1399 || l_api_name
1400 || ' Return status from gme_reservations_pvt.convert_partial_to_dlr is '
1401 || x_return_status);
1402 END IF;
1403
1404 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1405 gme_common_pvt.log_message ('GME_RSV_DETAIL_REQUIRED');
1406 RAISE detail_reservation_error;
1407 END IF;
1408 ELSE -- fully detailed
1409 l_reservation_rec := p_reservation_rec;
1410 END IF;
1411
1412 /* Next phase is to create the inventory transaction and relieve the reservation */
1413 l_actual_qty := NVL (p_material_details_rec.actual_qty, 0);
1414 /* If p_qty_convert is null, need to pass in the full reservation_qty */
1415 /* but this must be expressed in terms of gme_material_details.dtl_um */
1416 l_qty_convert := p_qty_convert;
1417
1418 IF l_qty_convert IS NULL THEN
1419 IF g_debug <= gme_debug.g_log_statement THEN
1420 gme_debug.put_line ( g_pkg_name
1421 || '.'
1422 || l_api_name
1423 || ' deternine quantity to convert');
1424 END IF;
1425
1426 IF p_material_details_rec.dtl_um =
1427 l_reservation_rec.reservation_uom_code THEN
1428 l_qty_convert := l_reservation_rec.reservation_quantity;
1429 ELSIF p_material_details_rec.dtl_um =
1430 l_reservation_rec.primary_uom_code THEN
1431 l_qty_convert := l_reservation_rec.primary_reservation_quantity;
1432 ELSE
1433 l_qty_convert :=
1434 inv_convert.inv_um_convert
1435 (item_id => l_reservation_rec.inventory_item_id
1436 ,organization_id => l_reservation_rec.organization_id
1437 ,lot_number => l_reservation_rec.lot_number
1438 ,PRECISION => gme_common_pvt.g_precision
1439 ,from_quantity => l_reservation_rec.primary_reservation_quantity
1440 ,from_unit => l_reservation_rec.primary_uom_code
1441 ,to_unit => p_material_details_rec.dtl_um
1442 ,from_name => NULL
1443 ,to_name => NULL);
1444
1445 IF (l_qty_convert < 0) THEN
1446 IF g_debug <= gme_debug.g_log_statement THEN
1447 gme_debug.put_line
1448 ( g_pkg_name
1449 || '.'
1450 || l_api_name
1451 || ' UOM Conversion fail from '
1452 || l_reservation_rec.primary_uom_code
1453 || ' to '
1454 || p_material_details_rec.dtl_um
1455 || ' for qty '
1456 || l_reservation_rec.primary_reservation_quantity);
1457 END IF;
1458
1459 RAISE uom_conversion_failure;
1460 END IF;
1461 END IF;
1462 END IF;
1463
1464 IF g_debug <= gme_debug.g_log_statement THEN
1465 gme_debug.put_line
1466 ( g_pkg_name
1467 || '.'
1468 || l_api_name
1469 || ' Invoking build_and_create_transaction with consume_qty of '
1470 || l_qty_convert
1471 || ' Subinventory is '
1472 || l_reservation_rec.subinventory_code
1473 || ' lot_divisible flag is '
1474 || l_item_rec.lot_divisible_flag);
1475 END IF;
1476
1477 gme_release_batch_pvt.build_and_create_transaction
1478 (p_rsrv_rec => l_reservation_rec
1479 ,p_lot_divisible_flag => l_item_rec.lot_divisible_flag
1480 ,p_mtl_dtl_rec => p_material_details_rec
1481 ,p_trans_date => SYSDATE
1482 ,p_consume_qty => l_qty_convert
1483 ,p_secondary_uom_code => l_item_rec.secondary_uom_code
1484 ,x_actual_qty => l_actual_qty
1485 ,x_return_status => x_return_status);
1486
1487 IF g_debug <= gme_debug.g_log_statement THEN
1488 gme_debug.put_line
1489 ( g_pkg_name
1490 || '.'
1491 || l_api_name
1492 || ' Return status from gme_release_batch_pvt.build_and_create_transaction is '
1493 || x_return_status);
1494 END IF;
1495
1496 IF x_return_status NOT IN ('T', fnd_api.g_ret_sts_success) THEN
1497 RAISE create_transaction_error;
1498 END IF;
1499
1500 IF g_debug <= gme_debug.g_log_statement THEN
1501 gme_debug.put_line
1502 ( g_pkg_name
1503 || '.'
1504 || l_api_name
1505 || ' build_and_create_transaction returns actial_qty of '
1506 || l_actual_qty);
1507 gme_debug.put_line ( ' Completed private layer '
1508 || l_api_name
1509 || ' at '
1510 || TO_CHAR (SYSDATE, 'MM/DD/YYYY HH24:MI:SS') );
1511 END IF;
1512 EXCEPTION
1513 WHEN error_unexpected OR uom_conversion_failure OR fetch_error OR validation_error OR create_transaction_error OR detail_reservation_error THEN
1514 x_return_status := fnd_api.g_ret_sts_error;
1515 gme_common_pvt.count_and_get (x_count => x_message_count
1516 ,p_encoded => fnd_api.g_false
1517 ,x_data => x_message_list);
1518 WHEN OTHERS THEN
1519 IF g_debug <= gme_debug.g_log_unexpected THEN
1520 gme_debug.put_line ( g_pkg_name
1521 || '.'
1522 || l_api_name
1523 || ':'
1524 || ' When others exception:'
1525 || SQLERRM);
1526 END IF;
1527
1528 x_return_status := fnd_api.g_ret_sts_unexp_error;
1529 gme_common_pvt.count_and_get (x_count => x_message_count
1530 ,p_encoded => fnd_api.g_false
1531 ,x_data => x_message_list);
1532 END convert_dtl_reservation;
1533
1534 PROCEDURE auto_detail_line (
1535 p_material_details_rec IN gme_material_details%ROWTYPE
1536 ,x_return_status OUT NOCOPY VARCHAR2)
1537 IS
1538 l_api_name CONSTANT VARCHAR2 (30) := 'auto_detail_line';
1539 l_item_rec mtl_system_items_b%ROWTYPE;
1540 l_rsv_rec mtl_reservations%ROWTYPE;
1541 l_rsv_tbl gme_common_pvt.reservations_tab;
1542 l_reservations_tbl inv_reservation_global.mtl_reservation_tbl_type;
1543 l_rsv_type NUMBER;
1544 l_open_qty NUMBER;
1545 l_msg_count NUMBER;
1546 l_msg_data VARCHAR2 (2000);
1547 l_count NUMBER := 1;
1548 l_return_status VARCHAR2 (1);
1549 l_suggestion_list_rec_type wms_rule_extn_pvt.g_suggestion_list_rec_type;
1550 l_trolin_rec_type inv_move_order_pub.trolin_rec_type;
1551 l_trolin_rec mtl_txn_request_lines%ROWTYPE;
1552 reservable_type_error EXCEPTION;
1553 retrieve_res_fail EXCEPTION;
1554 item_fetch_fail EXCEPTION;
1555 fetch_error EXCEPTION;
1556 relieve_res_error EXCEPTION;
1557 open_qty_error EXCEPTION;
1558 update_mo_fail EXCEPTION;
1559 create_suggestions_err EXCEPTION;
1560 error_unexpected EXCEPTION;
1561 create_reservation_err EXCEPTION;
1562 --Bug#4604943
1563 invalid_mtl_for_rsrv EXCEPTION;
1564
1565
1566 CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER)
1567 IS
1568 SELECT *
1569 FROM mtl_system_items_b
1570 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
1571
1572 CURSOR cur_get_mo_rec (v_move_order_line_id NUMBER)
1573 IS
1574 SELECT *
1575 FROM mtl_txn_request_lines
1576 WHERE line_id = v_move_order_line_id;
1577 BEGIN
1578 IF g_debug <= gme_debug.g_log_procedure THEN
1579 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1580 || l_api_name);
1581 END IF;
1582
1583 x_return_status := fnd_api.g_ret_sts_success;
1584
1585 IF g_debug <= gme_debug.g_log_statement THEN
1586 gme_debug.put_line ( g_pkg_name
1587 || '.'
1588 || l_api_name
1589 || 'Verify reservable_type ');
1590 END IF;
1591
1592 --Bug#4604943 Begin validate the batch and material line
1593 validate_mtl_for_reservation(
1594 p_material_detail_rec => p_material_details_rec
1595 ,x_return_status => l_return_status );
1596 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1597 RAISE invalid_mtl_for_rsrv;
1598 END IF;
1599 -- Pawan kumar made changes for 5294184
1600
1601 IF NOT (p_material_details_rec.material_requirement_date < SYSDATE +
1602 NVL(gme_common_pvt.g_rule_based_resv_horizon,10000)) THEN
1603 gme_common_pvt.log_message ('GME_NO_ING_TIME');
1604 RETURN ;
1605 END IF;
1606
1607 --Bug#4604943 End
1608 -- Check for any ingredient failing in reservation category
1609
1610 OPEN cur_get_item_rec (p_material_details_rec.inventory_item_id
1611 ,p_material_details_rec.organization_id);
1612
1613 FETCH cur_get_item_rec
1614 INTO l_item_rec;
1615
1616 IF cur_get_item_rec%NOTFOUND THEN
1617 CLOSE cur_get_item_rec;
1618
1619 gme_common_pvt.log_message ('PM_INVALID_ITEM');
1620
1621 IF g_debug <= gme_debug.g_log_statement THEN
1622 gme_debug.put_line ( g_pkg_name
1623 || '.'
1624 || l_api_name
1625 || ' item fetch fail for '
1626 || p_material_details_rec.inventory_item_id);
1627 END IF;
1628
1629 RAISE item_fetch_fail;
1630 END IF;
1631
1632 CLOSE cur_get_item_rec;
1633
1634 IF NVL (l_item_rec.reservable_type, 1) <> 1 /*Reservable*/ THEN
1635 RAISE reservable_type_error;
1636 END IF;
1637
1638 gme_reservations_pvt.get_material_reservations
1639 (p_organization_id => p_material_details_rec.organization_id
1640 ,p_batch_id => p_material_details_rec.batch_id
1641 ,p_material_detail_id => p_material_details_rec.material_detail_id
1642 ,x_return_status => x_return_status
1643 ,x_reservations_tbl => l_rsv_tbl);
1644
1645 IF g_debug <= gme_debug.g_log_statement THEN
1646 gme_debug.put_line ( g_pkg_name
1647 || '.'
1648 || l_api_name
1649 || ' get_material_reservations returns '
1650 || x_return_status);
1651 gme_debug.put_line ( g_pkg_name
1652 || '.'
1653 || l_api_name
1654 || ' No. of reservations is '
1655 || l_rsv_tbl.COUNT);
1656 END IF;
1657
1658 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1659 RAISE retrieve_res_fail;
1660 END IF;
1661
1662 WHILE l_count <= l_rsv_tbl.COUNT LOOP
1663 l_rsv_rec := l_rsv_tbl (l_count);
1664 /* Verify that the reservation is fully detailed */
1665 /* Bug 5441643 Added NVL condition for location control code*/
1666 l_rsv_type :=
1667 gme_reservations_pvt.reservation_fully_specified
1668 (p_reservation_rec => l_rsv_rec
1669 ,p_item_location_control => NVL(l_item_rec.location_control_code,1)
1670 ,p_item_restrict_locators => l_item_rec.restrict_locators_code);
1671
1672 IF g_debug <= gme_debug.g_log_statement THEN
1673 gme_debug.put_line
1674 ( g_pkg_name
1675 || '.'
1676 || l_api_name
1677 || ' Return rsv_type from gme_reservations_pvt.reservation_fully_specified is '
1678 || TO_CHAR (l_rsv_type) );
1679 END IF;
1680
1681 IF l_rsv_type = -1 THEN
1682 gme_common_pvt.log_message ('GME_RSV_DETAIL_REQUIRED');
1683 RAISE error_unexpected;
1684 END IF;
1685
1686 IF l_rsv_type = 0 /*HLT*/ THEN
1687 gme_reservations_pvt.relieve_reservation
1688 (p_reservation_id => l_rsv_rec.reservation_id
1689 ,p_prim_relieve_qty => l_rsv_rec.primary_reservation_quantity
1690 ,x_return_status => x_return_status);
1691
1692 IF g_debug <= gme_debug.g_log_statement THEN
1693 gme_debug.put_line
1694 ( g_pkg_name
1695 || '.'
1696 || l_api_name
1697 || ' Return status from relieve_reservation is '
1698 || x_return_status);
1699 END IF;
1700
1701 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1702 RAISE relieve_res_error;
1703 END IF;
1704 END IF;
1705
1706 l_count := l_count + 1;
1707 END LOOP;
1708
1709 /* Assess the open quantity */
1710 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => p_material_details_rec
1711 ,p_called_by => 'R'
1712 ,x_open_qty => l_open_qty
1713 ,x_return_status => x_return_status);
1714
1715 IF g_debug <= gme_debug.g_log_statement THEN
1716 gme_debug.put_line ( g_pkg_name
1717 || '.'
1718 || l_api_name
1719 || ' get_open_qty returns open qty of '
1720 || l_open_qty);
1721 END IF;
1722
1723 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1724 RAISE open_qty_error;
1725 END IF;
1726
1727 /* If there is no open quantity, return here */
1728 IF l_open_qty <= 0 THEN
1729 IF g_debug <= gme_debug.g_log_statement THEN
1730 gme_debug.put_line ( g_pkg_name
1731 || '.'
1732 || l_api_name
1733 || 'No open quantity so RETURN now');
1734 END IF;
1735
1736 RETURN;
1737 END IF;
1738
1739 /* Update the Move Order */
1740 gme_move_orders_pvt.update_move_order_lines
1741 (p_batch_id => p_material_details_rec.batch_id
1742 ,p_material_detail_id => p_material_details_rec.material_detail_id
1743 ,p_new_qty => l_open_qty
1744 ,p_new_date => NULL
1745 ,p_invis_move_line_id => p_material_details_rec.move_order_line_id
1746 ,x_return_status => x_return_status);
1747
1748 IF g_debug <= gme_debug.g_log_statement THEN
1749 gme_debug.put_line ( g_pkg_name
1750 || '.'
1751 || l_api_name
1752 || ' Return from update_move_order_lines is '
1753 || x_return_status);
1754 END IF;
1755
1756 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1757 RAISE update_mo_fail;
1758 END IF;
1759
1760 IF g_debug <= gme_debug.g_log_statement THEN
1761 gme_debug.put_line
1762 ( g_pkg_name
1763 || '.'
1764 || l_api_name
1765 || ' Invoke wms_rule_extn_pvt.suggest_reservations for mo line_id => '
1766 || p_material_details_rec.move_order_line_id);
1767 END IF;
1768
1769 OPEN cur_get_mo_rec (p_material_details_rec.move_order_line_id);
1770
1771 FETCH cur_get_mo_rec INTO l_trolin_rec;
1772
1773 IF cur_get_mo_rec%NOTFOUND THEN
1774 CLOSE cur_get_mo_rec;
1775
1776 gme_common_pvt.log_message ('GME_NO_DATA_FOUND','TABLE_NAME','MTL_TXN_REQUEST_LINES');
1777
1778 IF g_debug <= gme_debug.g_log_statement THEN
1779 gme_debug.put_line ( g_pkg_name
1780 || '.'
1781 || l_api_name
1782 || ' move order line fetch fail for id'
1783 || p_material_details_rec.move_order_line_id);
1784 END IF;
1785 RAISE fetch_error;
1786 END IF;
1787 CLOSE cur_get_mo_rec;
1788
1789 l_trolin_rec_type.attribute1 := l_trolin_rec.attribute1;
1790 l_trolin_rec_type.attribute2 := l_trolin_rec.attribute2 ;
1791 l_trolin_rec_type.attribute3 := l_trolin_rec.attribute3 ;
1792 l_trolin_rec_type.attribute4 := l_trolin_rec.attribute4 ;
1793 l_trolin_rec_type.attribute5 := l_trolin_rec.attribute5 ;
1794 l_trolin_rec_type.attribute6 := l_trolin_rec.attribute6 ;
1795 l_trolin_rec_type.attribute7 := l_trolin_rec.attribute7 ;
1796 l_trolin_rec_type.attribute8 := l_trolin_rec.attribute8 ;
1797 l_trolin_rec_type.attribute9 := l_trolin_rec.attribute9 ;
1798 l_trolin_rec_type.attribute10 := l_trolin_rec.attribute10;
1799 l_trolin_rec_type.attribute11 := l_trolin_rec.attribute11;
1800 l_trolin_rec_type.attribute12 := l_trolin_rec.attribute12;
1801 l_trolin_rec_type.attribute13 := l_trolin_rec.attribute13;
1802 l_trolin_rec_type.attribute14 := l_trolin_rec.attribute14;
1803 l_trolin_rec_type.attribute15 := l_trolin_rec.attribute15;
1804 l_trolin_rec_type.attribute_category := l_trolin_rec.attribute_category;
1805 l_trolin_rec_type.created_by := l_trolin_rec.created_by;
1806 l_trolin_rec_type.creation_date := l_trolin_rec.creation_date;
1807 l_trolin_rec_type.date_required := l_trolin_rec.date_required;
1808 l_trolin_rec_type.from_locator_id := l_trolin_rec.from_locator_id;
1809 l_trolin_rec_type.from_subinventory_code := l_trolin_rec.from_subinventory_code;
1810 l_trolin_rec_type.from_subinventory_id := l_trolin_rec.from_subinventory_id;
1811 l_trolin_rec_type.header_id := l_trolin_rec.header_id;
1812 l_trolin_rec_type.inventory_item_id := l_trolin_rec.inventory_item_id;
1813 l_trolin_rec_type.last_updated_by := l_trolin_rec.last_updated_by;
1814 l_trolin_rec_type.last_update_date := l_trolin_rec.last_update_date;
1815 l_trolin_rec_type.last_update_login := l_trolin_rec.last_update_login;
1816 l_trolin_rec_type.line_id := l_trolin_rec.line_id;
1817 l_trolin_rec_type.line_number := l_trolin_rec.line_number;
1818 l_trolin_rec_type.line_status := l_trolin_rec.line_status;
1819 l_trolin_rec_type.lot_number := l_trolin_rec.lot_number;
1820 l_trolin_rec_type.organization_id := l_trolin_rec.organization_id;
1821 l_trolin_rec_type.program_application_id := l_trolin_rec.program_application_id;
1822 l_trolin_rec_type.program_id := l_trolin_rec.program_id;
1823 l_trolin_rec_type.program_update_date := l_trolin_rec.program_update_date;
1824 l_trolin_rec_type.project_id := l_trolin_rec.project_id;
1825 l_trolin_rec_type.quantity := l_trolin_rec.quantity;
1826 l_trolin_rec_type.quantity_delivered := l_trolin_rec.quantity_delivered;
1827 l_trolin_rec_type.quantity_detailed := l_trolin_rec.quantity_detailed;
1828 l_trolin_rec_type.reason_id := l_trolin_rec.reason_id;
1829 l_trolin_rec_type.reference := l_trolin_rec.reference;
1830 l_trolin_rec_type.reference_id := l_trolin_rec.reference_id;
1831 l_trolin_rec_type.reference_type_code := l_trolin_rec.reference_type_code;
1832 l_trolin_rec_type.request_id := l_trolin_rec.request_id;
1833 l_trolin_rec_type.revision := l_trolin_rec.revision;
1834 l_trolin_rec_type.serial_number_end := l_trolin_rec.serial_number_end;
1835 l_trolin_rec_type.serial_number_start := l_trolin_rec.serial_number_start;
1836 l_trolin_rec_type.status_date := l_trolin_rec.status_date;
1837 l_trolin_rec_type.task_id := l_trolin_rec.task_id;
1838 l_trolin_rec_type.to_account_id := l_trolin_rec.to_account_id;
1839 l_trolin_rec_type.to_locator_id := l_trolin_rec.to_locator_id;
1840 l_trolin_rec_type.to_subinventory_code := l_trolin_rec.to_subinventory_code;
1841 l_trolin_rec_type.to_subinventory_id := l_trolin_rec.to_subinventory_id;
1842 l_trolin_rec_type.transaction_header_id := l_trolin_rec.transaction_header_id;
1843 l_trolin_rec_type.transaction_type_id := l_trolin_rec.transaction_type_id;
1844 l_trolin_rec_type.txn_source_id := l_trolin_rec.txn_source_id;
1845 l_trolin_rec_type.txn_source_line_id := l_trolin_rec.txn_source_line_id;
1846 l_trolin_rec_type.txn_source_line_detail_id := l_trolin_rec.txn_source_line_detail_id;
1847 l_trolin_rec_type.transaction_source_type_id := l_trolin_rec.transaction_source_type_id;
1848 l_trolin_rec_type.primary_quantity := l_trolin_rec.primary_quantity;
1849 l_trolin_rec_type.to_organization_id := l_trolin_rec.to_organization_id;
1850 l_trolin_rec_type.pick_strategy_id := l_trolin_rec.pick_strategy_id;
1851 l_trolin_rec_type.put_away_strategy_id := l_trolin_rec.put_away_strategy_id;
1852 l_trolin_rec_type.uom_code := l_trolin_rec.uom_code;
1853 l_trolin_rec_type.unit_number := l_trolin_rec.unit_number;
1854 l_trolin_rec_type.ship_to_location_id := l_trolin_rec.ship_to_location_id;
1855 l_trolin_rec_type.from_cost_group_id := l_trolin_rec.from_cost_group_id;
1856 l_trolin_rec_type.to_cost_group_id := l_trolin_rec.to_cost_group_id;
1857 l_trolin_rec_type.lpn_id := l_trolin_rec.lpn_id;
1858 l_trolin_rec_type.to_lpn_id := l_trolin_rec.to_lpn_id;
1859 l_trolin_rec_type.pick_methodology_id := l_trolin_rec.pick_methodology_id;
1860 l_trolin_rec_type.container_item_id := l_trolin_rec.container_item_id;
1861 l_trolin_rec_type.carton_grouping_id := l_trolin_rec.carton_grouping_id;
1862 --l_trolin_rec_type.return_status := l_trolin_rec.return_status;
1863 --l_trolin_rec_type.db_flag := l_trolin_rec.db_flag;
1864 --l_trolin_rec_type.operation := l_trolin_rec.operation;
1865 l_trolin_rec_type.inspection_status := l_trolin_rec.inspection_status;
1866 l_trolin_rec_type.wms_process_flag := l_trolin_rec.wms_process_flag;
1867 l_trolin_rec_type.pick_slip_number := l_trolin_rec.pick_slip_number;
1868 l_trolin_rec_type.pick_slip_date := l_trolin_rec.pick_slip_date;
1869 l_trolin_rec_type.ship_set_id := l_trolin_rec.ship_set_id;
1870 l_trolin_rec_type.ship_model_id := l_trolin_rec.ship_model_id;
1871 l_trolin_rec_type.model_quantity := l_trolin_rec.model_quantity;
1872 l_trolin_rec_type.required_quantity := l_trolin_rec.required_quantity;
1873 l_trolin_rec_type.secondary_quantity := l_trolin_rec.secondary_quantity;
1874 --l_trolin_rec_type.secondary_uom := l_trolin_rec.secondary_uom;
1875 l_trolin_rec_type.secondary_quantity_detailed := l_trolin_rec.secondary_quantity_detailed;
1876 l_trolin_rec_type.secondary_quantity_delivered := l_trolin_rec.secondary_quantity_delivered;
1877 l_trolin_rec_type.grade_code := l_trolin_rec.grade_code;
1878 l_trolin_rec_type.secondary_required_quantity := l_trolin_rec.secondary_required_quantity;
1879
1880 IF g_debug <= gme_debug.g_log_statement THEN
1881 gme_debug.put_line ( g_pkg_name
1882 || '.'
1883 || l_api_name
1884 || ' about to invoke SUGGEST_RESERVATIONS for required quantity of '
1885 ||l_trolin_rec_type.required_quantity);
1886 END IF;
1887
1888 wms_rule_extn_pvt.suggest_reservations(
1889 p_api_version => 1.0
1890 , p_init_msg_list => FND_API.G_TRUE
1891 , p_commit => FND_API.G_FALSE
1892 , p_validation_level => 100
1893 , x_return_status => x_return_status
1894 , x_msg_count => l_msg_count
1895 , x_msg_data => l_msg_data
1896 , p_transaction_temp_id => p_material_details_rec.move_order_line_id
1897 , p_allow_partial_pick => FND_API.G_TRUE
1898 , p_suggest_serial => FND_API.G_FALSE
1899 , p_mo_line_rec => l_trolin_rec_type
1900 , p_demand_source_header_id=> p_material_details_rec.batch_id
1901 , p_demand_source_line_id => p_material_details_rec.material_detail_id
1902 , p_demand_source_type => 5
1903 , p_demand_source_name => NULL
1904 , p_requirement_date => p_material_details_rec.material_requirement_date
1905 , p_suggestions => l_suggestion_list_rec_type);
1906
1907 IF g_debug <= gme_debug.g_log_statement THEN
1908 gme_debug.put_line ( g_pkg_name
1909 || '.'
1910 || l_api_name
1911 || ' call to SUGGEST_RESERVATIONS returns '
1912 || x_return_status);
1913 gme_debug.put_line ( g_pkg_name
1914 || '.'
1915 || l_api_name
1916 || ' suggest_reservations mesg '
1917 || l_msg_data);
1918 END IF;
1919
1920 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1921 RAISE create_suggestions_err;
1922 END IF;
1923
1924 /* Create a High Level Reservation for any outstanding open quantity */
1925 /* start by determining what the outstanding open quantity is */
1926 gme_common_pvt.get_open_qty (p_mtl_dtl_rec => p_material_details_rec
1927 ,p_called_by => 'R'
1928 ,x_open_qty => l_open_qty
1929 ,x_return_status => x_return_status);
1930
1931 IF g_debug <= gme_debug.g_log_statement THEN
1932 gme_debug.put_line
1933 ( g_pkg_name
1934 || '.'
1935 || l_api_name
1936 || ' after detailing get_open_qty returns open qty of '
1937 || l_open_qty);
1938 END IF;
1939
1940 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1941 RAISE open_qty_error;
1942 END IF;
1943
1944 /* Attempt to create a high level reservation (at organization level) for the outstanding qty */
1945 /* However there may be no inventory left to do this successfully so accept error status */
1946 IF l_open_qty > 0 THEN
1947 IF g_debug <= gme_debug.g_log_statement THEN
1948 gme_debug.put_line ( g_pkg_name
1949 || '.'
1950 || l_api_name
1951 || ' attempting to create a HLR for the outstanding qty '
1952 || l_open_qty );
1953 END IF;
1954 gme_reservations_pvt.create_material_reservation
1955 (p_matl_dtl_rec => p_material_details_rec
1956 ,p_resv_qty => l_open_qty
1957 ,x_return_status => l_return_status);
1958
1959 IF g_debug <= gme_debug.g_log_statement THEN
1960 gme_debug.put_line ( g_pkg_name
1961 || '.'
1962 || l_api_name
1963 || ' create_material_reservation returns '
1964 || x_return_status);
1965 END IF;
1966
1967 /* may not be sufficient inventory to create a balancing HLR so accept an error return */
1968 IF l_return_status NOT IN
1969 (fnd_api.g_ret_sts_success, fnd_api.g_ret_sts_error) THEN
1970 x_return_status := l_return_status;
1971 RAISE create_reservation_err;
1972 END IF;
1973 END IF;
1974
1975 /* bug: 6665408 Skommine Added call to clear_quantity_cache procedure
1976 to recreate the quantity tree after the reservations are done */
1977 inv_quantity_tree_pub.clear_quantity_cache;
1978
1979
1980 IF g_debug <= gme_debug.g_log_statement THEN
1981 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1982 END IF;
1983 EXCEPTION
1984 WHEN reservable_type_error OR retrieve_res_fail OR
1985 item_fetch_fail OR fetch_error OR
1986 relieve_res_error OR open_qty_error OR update_mo_fail OR
1987 create_suggestions_err OR error_unexpected OR create_reservation_err THEN
1988 IF g_debug <= gme_debug.g_log_unexpected THEN
1989 gme_debug.put_line ( 'Exiting due to error exception in '
1990 || g_pkg_name
1991 || '.'
1992 || l_api_name);
1993 END IF;
1994 --Bug#4604943
1995 WHEN invalid_mtl_for_rsrv THEN
1996 x_return_status := l_return_status;
1997 WHEN OTHERS THEN
1998 IF g_debug <= gme_debug.g_log_unexpected THEN
1999 gme_debug.put_line ( 'When others exception in '
2000 || g_pkg_name
2001 || '.'
2002 || l_api_name
2003 || ' Error is '
2004 || SQLERRM);
2005 END IF;
2006
2007 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2008 x_return_status := fnd_api.g_ret_sts_unexp_error;
2009 END auto_detail_line;
2010
2011 PROCEDURE auto_detail_batch(p_batch_rec IN GME_BATCH_HEADER%ROWTYPE,
2012 p_timefence IN NUMBER DEFAULT 100000,
2013 x_return_status OUT NOCOPY VARCHAR2) IS
2014
2015 l_valid_mat BOOLEAN;
2016 l_mat_status NUMBER;
2017 l_step_status NUMBER;
2018 l_step_id NUMBER;
2019 l_return_status VARCHAR2(1);
2020 l_found NUMBER := 0;
2021 l_api_name CONSTANT VARCHAR2(30) := 'auto_detail_batch' ;
2022
2023 auto_detail_failure EXCEPTION;
2024 -- Pawan kumar made changes for 5294184- add gme_common_pvt.g_rule_based_resv_horizon
2025 /* Retrieve Ingredient rows */
2026 CURSOR cur_get_reservable_ings
2027 IS
2028 SELECT d.*
2029 FROM gme_material_details d,mtl_system_items_b i
2030 WHERE d.batch_id = p_batch_rec.batch_id
2031 AND d.line_type = -1
2032 AND d.material_requirement_date < SYSDATE + NVL(gme_common_pvt.g_rule_based_resv_horizon,p_timefence)
2033 AND i. inventory_item_id = d.inventory_item_id
2034 AND i.organization_id = d.organization_id
2035 AND i.reservable_type = 1
2036 ORDER BY d.line_no;
2037
2038 BEGIN
2039 IF g_debug <= gme_debug.g_log_procedure THEN
2040 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2041 END IF;
2042
2043 x_return_status := FND_API.G_RET_STS_SUCCESS;
2044
2045 IF g_debug <= gme_debug.g_log_statement THEN
2046 gme_debug.put_line(g_pkg_name||'.'||l_api_name||'gme_common_pvt.g_rule_based_resv_horizon
2047 is '||gme_common_pvt.g_rule_based_resv_horizon);
2048 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Retrieve Material Detail lines for batch_id '||p_batch_rec.batch_id);
2049 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Timefence is '||p_timefence);
2050 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch Status is '||p_batch_rec.batch_status);
2051
2052 END IF;
2053
2054 FOR get_matl_rec IN cur_get_reservable_ings LOOP
2055 l_found := 1 ;
2056 /* Bug#4604943 moved the validation code to procedure validate_mtl_for_reservation
2057 As the validation will be done in auto_detail_line procedure there is no need to the
2058 validation again over here
2059 commented the following IF condition also
2060 */
2061 --IF l_valid_mat THEN
2062
2063 /* Invoke auto detail line to create detailed reservations for each of the ingredient lines */
2064 IF g_debug <= gme_debug.g_log_statement THEN
2065 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' ********************* Ingredient Line Processing **************************');
2066 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoke auto detail for material detail id '||get_matl_rec.material_detail_id);
2067 END IF;
2068
2069 /*calling auto detail line */
2070 gme_reservations_pvt.auto_detail_line(p_material_details_rec => get_matl_rec
2071 ,x_return_status => l_return_status);
2072
2073 IF g_debug <= gme_debug.g_log_statement THEN
2074 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Call to auto detail line returns '||x_return_status);
2075 END IF;
2076
2077 /* Bug#4604943 just checking for unexpected error similar to create_batch_reservations */
2078 IF (l_return_status = fnd_api.g_ret_sts_unexp_error) THEN
2079 RAISE auto_detail_failure;
2080 END IF;
2081
2082 --END IF;
2083 END LOOP;
2084 -- Pawan kumar made changes for 5294184
2085 IF l_found = 0 THEN
2086 gme_common_pvt.log_message ('GME_NO_ING_TIME');
2087 END IF;
2088 IF g_debug <= gme_debug.g_log_statement THEN
2089 gme_debug.put_line('Exiting api '||g_pkg_name||'.'||l_api_name);
2090 END IF;
2091
2092 EXCEPTION
2093 WHEN auto_detail_failure THEN
2094 IF g_debug <= gme_debug.g_log_unexpected THEN
2095 gme_debug.put_line('Exiting due to error exception in '||g_pkg_name||'.'||l_api_name);
2096 END IF;
2097 x_return_status := l_return_status;
2098 WHEN OTHERS THEN
2099 IF g_debug <= gme_debug.g_log_unexpected THEN
2100 gme_debug.put_line('When others exception in '||g_pkg_name||'.'||l_api_name||' Error is ' || SQLERRM);
2101 END IF;
2102 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2104 END auto_detail_batch;
2105
2106 --Bug#4604943 created the following procedure
2107 PROCEDURE validate_mtl_for_reservation(
2108 p_material_detail_rec IN GME_MATERIAL_DETAILS%ROWTYPE,
2109 x_return_status OUT NOCOPY VARCHAR2) IS
2110
2111 l_api_name VARCHAR2(30) := 'VALIDATE_MTL_FOR_RESERVATION';
2112 l_batch_header_rec gme_batch_header%ROWTYPE;
2113 l_step_id NUMBER;
2114 l_step_status NUMBER;
2115
2116 fetch_failure EXCEPTION;
2117 demand_line_error EXCEPTION;
2118 batch_status_error EXCEPTION;
2119 BEGIN
2120 IF g_debug <= gme_debug.g_log_procedure THEN
2121 gme_debug.put_line('Entering api '||g_pkg_name||'.'||l_api_name);
2122 END IF;
2123
2124 x_return_status := fnd_api.g_ret_sts_success;
2125
2126 l_batch_header_rec.batch_id := p_material_detail_rec.batch_id;
2127 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec, l_batch_header_rec)) THEN
2128 RAISE fetch_failure;
2129 END IF;
2130
2131 IF l_batch_header_rec.batch_status = gme_common_pvt.g_batch_pending THEN
2132 --pending batch just return
2133 IF g_debug <= gme_debug.g_log_statement THEN
2134 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is Pending status');
2135 END IF;
2136 RETURN;
2137 ELSIF l_batch_header_rec.batch_status = gme_common_pvt.g_batch_wip THEN
2138 /* In WIP Bathes, do not create reservations for automatic and automatic by step if assoc step is not released*/
2139 IF p_material_detail_rec.release_type IN (gme_common_pvt.g_mtl_manual_release,gme_common_pvt.g_mtl_incremental_release) THEN
2140 IF g_debug <= gme_debug.g_log_statement THEN
2141 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is manual/incremental');
2142 END IF;
2143 RETURN;
2144 ELSIF p_material_detail_rec.release_type = gme_common_pvt.g_mtl_autobystep_release THEN
2145 /* if automatic by step then check step status */
2146 IF g_debug <= gme_debug.g_log_statement THEN
2147 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is Autoby step');
2148 END IF;
2149 IF NOT gme_common_pvt.get_assoc_step(p_material_detail_rec.material_detail_id,l_step_id,l_step_status) THEN
2150 RAISE demand_line_error;
2151 ELSIF l_step_id IS NOT NULL AND NVL(l_step_status,-1) <> gme_common_pvt.g_step_pending THEN
2152 RAISE demand_line_error;
2153 END IF;
2154 ELSE
2155 IF g_debug <= gme_debug.g_log_statement THEN
2156 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Batch is in WIP and material line is automatic');
2157 END IF;
2158 RAISE demand_line_error;
2159 END IF; /*end of validations for WIP Batch*/
2160 ELSE
2161 RAISE batch_status_error;
2162 END IF; /* outer most if */
2163
2164 IF g_debug <= gme_debug.g_log_procedure THEN
2165 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2166 END IF;
2167 EXCEPTION
2168 WHEN fetch_failure THEN
2169 x_return_status := fnd_api.g_ret_sts_error;
2170 WHEN demand_line_error THEN
2171 gme_common_pvt.log_message('GME_INVALID_DEMAND_LINE');
2172 x_return_status := fnd_api.g_ret_sts_error;
2173 WHEN batch_status_error THEN
2174 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS','PROCESS','RESERVATIONS');
2175 x_return_status := fnd_api.g_ret_sts_error;
2176 WHEN OTHERS THEN
2177 IF g_debug <= gme_debug.g_log_unexpected THEN
2178 gme_debug.put_line ( 'When others exception in '
2179 || g_pkg_name
2180 || '.'
2181 || l_api_name
2182 || ' Error is '
2183 || SQLERRM);
2184 END IF;
2185
2186 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2187 x_return_status := fnd_api.g_ret_sts_unexp_error;
2188 END validate_mtl_for_reservation;
2189 END gme_reservations_pvt;