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