[Home] [Help]
PACKAGE BODY: APPS.GME_SUPPLY_RES_PVT
Source
1 PACKAGE BODY GME_SUPPLY_RES_PVT AS
2 /* $Header: GMEORESB.pls 120.9.12020000.2 2012/07/26 16:23:11 gmurator ship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMEORESB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM Production |
13 | as a source of supply for Reservations |
14 | |
15 | |
16 | HISTORY |
17 | Aug-18-2003 Liping Gao Created |
18 | Archana Mundhe Bug 5763818 Modified the code to use constants |
19 | that are added to gme_common_pvt instead of using the hardcoded |
20 | WF event names such as 'oracle.apps.gme...' |
21 | srpuri FP Bug 7422975 Added the condition to convert the transaction |
22 | quantity to the secondary only when the secondary uom code is not null |
23 | srpuri FP Bug 8343517 Modified procedure |
24 | transfer_reservation_to_inv. Assigned l_mmt_rec.transfer_lpn_id to |
25 | l_to_rsv_rec.lpn_id so that lpn is coorectly stamped on the transfer|
26 | Reservation. |
27 |
28 | 18-Oct-2011 G. Muratore Bug 12805400 |
29 | Put in check for indivisible item when creating SO reservation. |
30 | PROCEDURE: transfer_reservation_to_inv |
31 | |
32 | 15-Feb-2012 G. Muratore Bug 13630492 |
33 | Allow reservations to be transferred from fpo to batch for other |
34 | batch demand. PROCEDURE create_reservation_from_FPO |
35 +=========================================================================+
36 API Name : GME_SUPPLY_RES_PVT
37 Type : Private
38 Function : This package contains Private procedures used for change management
39 of reservations placed against OPM Production as a source of supply
40 Pre-reqs : N/A
41 Parameters: Per function
42
43 Current Vers : 1.0
44
45 */
46
47 G_PKG_NAME CONSTANT VARCHAR2(30):='GME_SUPPLY_RES_PVT';
48 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
49
50 PROCEDURE create_reservation_from_FPO
51 (
52 P_FPO_batch_id IN NUMBER
53 , P_New_batch_id IN NUMBER
54 , X_return_status OUT NOCOPY VARCHAR2
55 , X_msg_count OUT NOCOPY NUMBER
56 , X_msg_data OUT NOCOPY VARCHAR2
57 ) IS
58
59 l_api_name CONSTANT VARCHAR2 (30) := 'create_reservation_from_FPO';
60 l_fpo_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
61 l_batch_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
62 l_fpo_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
63 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
64 l_fpo_rsv_count NUMBER;
65
66 l_batch_line_id NUMBER;
67 l_fpo_batch_line_id NUMBER;
68 l_whse_code VARCHAR2(5);
69 l_planned_qty NUMBER;
70 l_fpo_qty NUMBER;
71 l_planned_qty2 NUMBER;
72 l_res_count NUMBER;
73 l_avg_qty NUMBER;
74 l_avg_qty2 NUMBER;
75
76 l_inventory_item_id NUMBER;
77 l_reserved_qty NUMBER;
78 l_reserved_qty2 NUMBER;
79 l_remaining_qty NUMBER;
80 l_organization_id NUMBER;
81 l_plan_cmplt_date date;
82 l_new_reservation_id NUMBER;
83
84 l_msg_data VARCHAR2(2000);
85 l_msg_count NUMBER;
86 l_api_return_status VARCHAR2(1);
87 l_api_error_code NUMBER;
88 l_api_error_msg VARCHAR2(100);
89
90 -- Bug 13630492 - Transfer reservations for batches also.
91 Cursor check_whse (p_batch_line_id IN NUMBER) IS
92 Select distinct organization_id
93 From mtl_reservations
94 Where supply_source_line_id = p_batch_line_id
95 and supply_source_type_id = 5
96 -- and demand_source_type_id = inv_reservation_global.g_source_type_oe
97 and demand_source_type_id IN (inv_reservation_global.g_source_type_oe, gme_common_pvt.g_txn_source_type) -- Bug 13630492
98 and reservation_quantity<>0
99 ;
100
101 Cursor get_batch_line (p_batch_id IN NUMBER) IS
102 Select material_detail_id
103 , inventory_item_id
104 , plan_qty
105 From gme_material_details
106 where batch_id = p_batch_id
107 and line_type <> -1 -- not ingredient
108 ;
109
110 Cursor get_res_for_whse (p_organization_id IN NUMBER
111 , p_batch_line_id IN NUMBER) IS
112 Select *
113 From mtl_reservations
114 Where supply_source_line_id = p_batch_line_id
115 and supply_source_type_id = 5
116 and demand_source_type_id = inv_reservation_global.g_source_type_oe
117 and organization_id = p_organization_id
118 and reservation_quantity <> 0
119 Order by requirement_date
120 ;
121
122 Cursor get_res_count(p_batch_line_id IN NUMBER
123 ) Is
124 Select count(1)
125 From mtl_reservations
126 Where supply_source_line_id = p_batch_line_id
127 --and organization_id = p_organization_id
128 and supply_source_type_id = 5
129 and reservation_quantity <> 0
130 ;
131
132 Cursor get_new_batch_line (p_batch_id IN NUMBER
133 , p_item_id IN NUMBER) is
134 Select material_detail_id
135 , plan_qty
136 From gme_material_details
137 Where batch_id = p_batch_id
138 and inventory_item_id = p_item_id
139 and line_type <> -1
140 ;
141 Cursor get_new_batch_cmpt_date (p_batch_id IN NUMBER) is
142 Select plan_cmplt_date
143 From gme_batch_header
144 where batch_id = p_batch_id;
145
146 BEGIN
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148 IF g_debug <= gme_debug.g_log_procedure THEN
149 gme_debug.put_line ('Entering api '
150 || g_pkg_name || '.'
151 || l_api_name);
152 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' FPO batch_id '||p_fpo_batch_id);
153 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' BATCH batch_id '||p_new_batch_id);
154 END IF;
155
156 /* loop through all the product lines in the batch */
157 For batch_line in get_batch_line(p_FPO_batch_id) Loop
158 l_fpo_batch_line_id := batch_line.material_detail_id ;
159 l_inventory_item_id := batch_line.inventory_item_id ;
160 l_fpo_qty := batch_line.plan_qty;
161 IF g_debug <= gme_debug.g_log_procedure THEN
162 gme_debug.put_line(g_pkg_name||'.'||l_api_name|| ' FPO batch_line_id '||l_fpo_batch_line_id);
163 END IF;
164 /* check reservation exist or not */
165 Open get_res_count(l_FPO_batch_line_id);
166 Fetch get_res_count into l_res_count;
167 Close get_res_count;
168
169 IF g_debug <= gme_debug.g_log_procedure THEN
170 gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' FPO reservation cound '||l_res_count);
171 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' NEW batch_line_id '||l_batch_line_id);
172 END IF;
173
174 IF l_res_count = 0 THEN
175 goto next_batch_line;
176 END IF;
177
178 /* get the new batch_line_id for the created batch */
179 Open get_new_batch_line(p_new_batch_id, l_inventory_item_id);
180 Fetch get_new_batch_line Into l_batch_line_id, l_planned_qty;
181 Close get_new_batch_line;
182
183 /* loop to see if different whse may have exist -- only one org in R12*/
184 For each_whse in check_whse(l_fpo_batch_line_id) Loop
185 l_organization_id := each_whse.organization_id;
186
187 IF g_debug <= gme_debug.g_log_procedure THEN
188 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' reservation org id '||l_organization_id);
189 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' batch inventory_item_id '||l_inventory_item_id);
190 END IF;
191
192 Open get_new_batch_cmpt_date (p_new_batch_id) ;
193 Fetch get_new_batch_cmpt_date Into l_plan_cmplt_date;
194 Close get_new_batch_cmpt_date;
195 /* process the reservations made in this whse */
196
197 l_fpo_rsv_rec.organization_id := l_organization_id;
198 l_fpo_rsv_rec.inventory_item_id := l_inventory_item_id;
199 l_fpo_rsv_rec.supply_source_type_id := 5 ;--inv_reservation_global.g_source_type_wip;
200 l_fpo_rsv_rec.supply_source_header_id := p_fpo_batch_id;
201 l_fpo_rsv_rec.supply_source_line_id := l_fpo_batch_line_id;
202 inv_reservation_pub.query_reservation
203 (
204 p_api_version_number => 1.0
205 , p_init_msg_lst => fnd_api.g_false
206 , x_return_status => x_return_status
207 , x_msg_count => x_msg_count
208 , x_msg_data => x_msg_data
209 , p_query_input => l_fpo_rsv_rec
210 , p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc
211 , x_mtl_reservation_tbl => l_fpo_rsv_tbl
212 , x_mtl_reservation_tbl_count => l_fpo_rsv_count
213 , x_error_code => l_api_error_code
214 );
215 IF x_return_status = fnd_api.g_ret_sts_error THEN
216 RAISE fnd_api.g_exc_error;
217 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
218 RAISE fnd_api.g_exc_unexpected_error;
219 END IF;
220
221 IF g_debug <= gme_debug.g_log_procedure THEN
222 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' fpo reservation count '||l_fpo_rsv_count);
223 END IF;
224
225 l_remaining_qty := l_planned_qty;
226 for i in 1..l_fpo_rsv_count Loop
227 l_fpo_rsv_rec := l_fpo_rsv_tbl(i);
228 /* check the batch planned cplt date with the scheduled_ship_date
229 * if the date is out, skip this record
230 */
231 IF g_debug <= gme_debug.g_log_procedure THEN
232 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' build res record for the new batch');
233 END IF;
234 /*IF l_fpo_rsv_rec.requirement_date> l_plan_cmplt_date THEN
235 Goto next_res_line;
236 END IF;
237 */
238 EXIT WHEN l_remaining_qty <= 0 ;
239 /* build reservation rec for the new batch line */
240 IF l_fpo_rsv_rec.reservation_quantity <= l_planned_qty THEN
241 l_reserved_qty := l_fpo_rsv_rec.reservation_quantity;
242 l_reserved_qty2 := l_fpo_rsv_rec.secondary_reservation_quantity;
243 ELSE -- R12 restriting over reserve
244 l_reserved_qty := l_planned_qty;
245 IF l_fpo_rsv_rec.secondary_uom_code is not null
246 and l_fpo_rsv_rec.reservation_uom_code <> l_fpo_rsv_rec.secondary_uom_code
247 THEN
248 l_reserved_qty2 := inv_convert.inv_um_convert(
249 item_id => l_inventory_item_id
250 , precision => NULL
251 , from_quantity => l_reserved_qty
252 , from_unit => l_fpo_rsv_rec.reservation_uom_code
253 , to_unit => l_fpo_rsv_rec.secondary_uom_code
254 , from_name => NULL
255 , to_name => NULL);
256 Elsif l_fpo_rsv_rec.reservation_uom_code = l_fpo_rsv_rec.secondary_uom_code
257 Then
258 l_reserved_qty2 := l_reserved_qty;
259 end if;
260 END IF;
261
262 l_batch_rsv_rec := l_fpo_rsv_rec;
263
264 l_batch_rsv_rec.reservation_id := NULL; -- New reservation
265 l_batch_rsv_rec.lpn_id := NULL;
266 l_batch_rsv_rec.subinventory_id := NULL;
267 l_batch_rsv_rec.lot_number_id := NULL;
268 l_batch_rsv_rec.supply_source_header_id := p_new_batch_id;
269 l_batch_rsv_rec.supply_source_line_id := l_batch_line_id;
270 l_batch_rsv_rec.reservation_quantity := l_reserved_qty;
271 l_batch_rsv_rec.primary_reservation_quantity := l_reserved_qty;
272 l_batch_rsv_rec.secondary_reservation_quantity := l_reserved_qty2;
273 IF l_fpo_rsv_rec.reservation_uom_code <> l_fpo_rsv_rec.primary_uom_code
274 THEN
275 l_batch_rsv_rec.primary_reservation_quantity := inv_convert.inv_um_convert(
276 item_id => l_inventory_item_id
277 , precision => NULL
278 , from_quantity => l_reserved_qty
279 , from_unit => l_fpo_rsv_rec.reservation_uom_code
280 , to_unit => l_fpo_rsv_rec.primary_uom_code
281 , from_name => NULL
282 , to_name => NULL);
283 end if;
284
285 IF g_debug <= gme_debug.g_log_procedure THEN
286 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty '||l_reserved_qty);
287 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' new reserved qty2 '||l_reserved_qty2);
288 END IF;
289
290 inv_reservation_pub.transfer_reservation
291 (p_api_version_number => 1.0,
292 p_init_msg_lst => fnd_api.g_false,
293 x_return_status => x_return_status,
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_original_rsv_rec => l_fpo_rsv_tbl(i),
297 p_to_rsv_rec => l_batch_rsv_rec,
298 p_original_serial_number => l_dummy_sn ,
299 p_to_serial_number => l_dummy_sn ,
300 p_validation_flag => fnd_api.g_false,
301 x_to_reservation_id => l_new_reservation_id
302 );
303 IF x_return_status = fnd_api.g_ret_sts_error THEN
304 RAISE fnd_api.g_exc_error;
305 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
306 RAISE fnd_api.g_exc_unexpected_error;
307 END IF;
308 l_remaining_qty := l_remaining_qty - l_reserved_qty;
309 <<next_res_line>>
310 null;
311 END Loop;
312 END LOOP;
313 <<next_batch_line>>
314 null;
315 END loop;
316 /* notify the CSR*/
317 GME_SUPPLY_RES_PVT.notify_CSR
318 ( P_Batch_id => p_new_batch_id
319 , P_fpo_id => p_fpo_batch_id
320 , P_organization_id => l_organization_id
321 , P_action_code => 'CONVERT_FPO'
322 , X_return_status => x_return_status
323 , X_msg_cont => x_msg_count
324 , X_msg_data => x_msg_data );
325
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 /* Get message count and data*/
330 FND_MSG_PUB.count_and_get
331 ( p_count => x_msg_count
332 , p_data => x_msg_data
333 );
334 IF g_debug <= gme_debug.g_log_procedure THEN
335 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Expected');
336 END IF;
337 WHEN OTHERS THEN
338 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
339
340 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
341 , 'convert_FPO'
342 );
343 /* Get message count and data*/
344 FND_MSG_PUB.count_and_get
345 ( p_count => x_msg_count
346 , p_data => x_msg_data
347 );
348 IF g_debug <= gme_debug.g_log_procedure THEN
349 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'u EXCEPTION: Others');
350 END IF;
351
352 END create_reservation_from_FPO;
353
354 PROCEDURE notify_CSR
355 (
356 P_Batch_id IN NUMBER default null
357 , P_FPO_id IN NUMBER default null
358 , P_Batch_line_id IN NUMBER default null
359 , P_So_line_id IN NUMBER default null
360 , P_batch_trans_id IN NUMBER default null
361 , P_organization_id IN NUMBER default null
362 , P_action_code IN VARCHAR2
363 , X_return_status OUT NOCOPY VARCHAR2
364 , X_msg_cont OUT NOCOPY NUMBER
365 , X_msg_data OUT NOCOPY VARCHAR2
366 ) IS
367 l_api_name CONSTANT VARCHAR2 (30) := 'Notify_CSR';
368 l_csr_id NUMBER;
369
370 l_batch_id NUMBER;
371 l_so_header_id NUMBER;
372 l_so_line_id NUMBER;
373 l_action_code VARCHAR2(200);
374 l_whse_code VARCHAR2(4);
375 l_batch_type NUMBER;
376 l_no_of_staged_alloc NUMBER;
377 l_no_of_unstaged_alloc NUMBER;
378 l_last_updated_by NUMBER;
379 l_created_by NUMBER;
380 l_session_id NUMBER;
381 l_batch_line_id NUMBER;
382 l_organization_id NUMBER;
383 l_old_header_id NUMBER;
384 l_new_header_id NUMBER;
385 l_lot_number VARCHAR2(80);
386
387 l_parameter_list wf_parameter_list_t :=wf_parameter_list_t( );
388
389
390 CURSOR So_line_id_for_batch(p_batch_id IN NUMBER) Is
391 SELECT Distinct orig_demand_source_line_id
392 FROM mtl_reservations
393 WHERE supply_source_header_id = p_batch_id
394 and supply_source_type_id = 5
395 and demand_source_type_id = 2
396 and reservation_quantity <> 0;
397
398 CURSOR Get_batch_type(p_batch_id IN NUMBER) IS
399 SELECT batch_type
400 FROM gme_batch_header
401 WHERE batch_id = p_batch_id;
402
403
404 CURSOR So_line_id_for_batch_line(p_batch_line_id IN NUMBER) Is
405 SELECT Distinct orig_demand_source_line_id
406 FROM mtl_reservations
407 WHERE supply_source_line_id = p_batch_line_id
408 and supply_source_type_id = 5
409 and demand_source_type_id = 2
410 and reservation_quantity <> 0;
411
412 CURSOR get_batch_id_for_line(p_batch_line_id IN NUMBER) Is
413 SELECT gl.batch_id,gh.batch_type
414 FROM gme_material_details gl,
415 gme_batch_header gh
416 WHERE gl.material_detail_id = p_batch_line_id
417 and gl.batch_id = gh.batch_id;
418
419 CURSOR CSR_for_so_line(p_so_line_id IN NUMBER) IS
420 SELECT last_updated_by, created_by,header_id
421 FROM oe_order_lines_all
422 WHERE line_id = p_so_line_id;
423
424 -- nsinghi perf bug#5212566. Re-written the 2 cursors to ensure that there is no FTS on mtl_reservations.
425 /*
426 Cursor check_mul_line_id1 (p_user_id IN number
427 , p_batch_id IN NUMBER) IS
428 Select distinct orig_demand_source_line_id
429 From mtl_reservations
430 Where created_by = p_user_id
431 and orig_supply_source_header_id = p_batch_id
432 and demand_source_type_id = 2
433 and orig_supply_source_type_id = 5;
434
435 Cursor check_mul_line_id2 (p_user_id IN number
436 , p_batch_id IN NUMBER
437 , p_batch_line_id IN NUMBER) IS
438 Select distinct orig_demand_source_line_id
439 From mtl_reservations
440 Where created_by = p_user_id
441 and orig_supply_source_header_id = p_batch_id
442 and orig_supply_source_line_id = p_batch_line_id
443 and demand_source_type_id = 2
444 and orig_supply_source_type_id = 5;
445 */
446 Cursor check_mul_line_id1 (p_user_id IN number
447 , p_batch_id IN NUMBER) IS
448 SELECT DISTINCT orig_demand_source_line_id
449 FROM mtl_reservations mr, gme_batch_header gbh
450 WHERE mr.created_by = p_user_id
451 AND mr.orig_supply_source_header_id = p_batch_id
452 AND mr.orig_supply_source_header_id = gbh.batch_id
453 AND mr.organization_id = gbh.organization_id
454 AND mr.demand_source_type_id = 2
455 AND mr.orig_supply_source_type_id = 5;
456
457 Cursor check_mul_line_id2 (p_user_id IN number
458 , p_batch_id IN NUMBER
459 , p_batch_line_id IN NUMBER) IS
460 SELECT DISTINCT orig_demand_source_line_id
461 FROM mtl_reservations mr, gme_material_details gmd
462 WHERE mr.created_by = p_user_id
463 AND mr.orig_supply_source_header_id = p_batch_id
464 AND mr.orig_supply_source_line_id = p_batch_line_id
465 AND mr.inventory_item_id = gmd.inventory_item_id
466 AND mr.organization_id = gmd.organization_id
467 AND gmd.batch_id = mr.orig_supply_source_header_id
468 AND gmd.material_detail_id = mr.orig_supply_source_line_id
469 AND mr.demand_source_type_id = 2
470 AND mr.orig_supply_source_type_id = 5;
471
472 -- MakeToOrder BEGIN
473 CURSOR Get_pending_lot(p_batch_id IN NUMBER, p_batch_line_id IN NUMBER) IS
474 SELECT lot_number
475 FROM gme_pending_product_lots
476 WHERE batch_id = p_batch_id and material_detail_id = p_batch_line_id;
477 -- MakeToOrder BEGIN
478
479 BEGIN
480
481 x_return_status := FND_API.G_RET_STS_SUCCESS;
482
483 IF g_debug <= gme_debug.g_log_procedure THEN
484 gme_debug.put_line ('Entering api '
485 || g_pkg_name || '.'
486 || l_api_name);
487 END IF;
488 l_session_id := USERENV('sessionid');
489 l_batch_line_id := p_batch_line_id ;
490 l_batch_id := p_batch_id;
491 l_organization_id := p_organization_id;
492
493 /* will send the work flow */
494 IF p_batch_id is not null THEN
495 IF g_debug <= gme_debug.g_log_procedure THEN
496 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_id is'|| p_batch_id);
497 END IF;
498 OPEN so_line_id_for_batch(p_batch_id);
499 FETCH so_line_id_for_batch INTO l_so_line_id;
500 IF(so_line_id_for_batch%NOTFOUND) THEN
501 CLOSE so_line_id_for_batch;
502 IF g_debug <= gme_debug.g_log_procedure THEN
503 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch%NOTFOUND, returning from Notify CSR');
504 END IF;
505 RETURN;
506 END IF;
507
508 CLOSE so_line_id_for_batch;
509
510 OPEN get_batch_type(p_batch_id);
511 FETCH get_batch_type INTO l_batch_type;
512 IF(get_batch_type%NOTFOUND) THEN
513 CLOSE get_batch_type;
514 IF g_debug <= gme_debug.g_log_procedure THEN
515 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_type%NOTFOUND, returning from Notify CSR');
516 END IF;
517 RETURN;
518 END IF;
519
520 CLOSE get_batch_type;
521
522 END IF;
523
524 IF p_batch_line_id is not null THEN
525 IF g_debug <= gme_debug.g_log_procedure THEN
526 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Batch_line_id is '|| p_batch_line_id);
527 END IF;
528 OPEN so_line_id_for_batch_line(p_batch_line_id);
529 FETCH so_line_id_for_batch_line INTO l_so_line_id;
530 IF(so_line_id_for_batch_line%NOTFOUND) THEN
531 CLOSE so_line_id_for_batch_line;
532 IF g_debug <= gme_debug.g_log_procedure THEN
533 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' so_line_id_for_batch_line%NOTFOUND, returning from Notify CSR');
534 END IF;
535 RETURN;
536 END IF;
537 CLOSE so_line_id_for_batch_line;
538
539 OPEN get_batch_id_for_line(p_batch_line_id);
540 FETCH get_batch_id_for_line INTO l_batch_id,l_batch_type;
541 IF(get_batch_id_for_line%NOTFOUND) THEN
542 CLOSE get_batch_id_for_line;
543 IF g_debug <= gme_debug.g_log_procedure THEN
544 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' get_batch_id_for_line%NOTFOUND, returning from Notify CSR');
545 END IF;
546 RETURN;
547 END IF;
548 CLOSE get_batch_id_for_line;
549
550 END IF;
551
552 IF g_debug <= gme_debug.g_log_procedure THEN
553 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : so_line_id is'|| l_so_line_id);
554 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_organization_id is'|| l_organization_id);
555 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : p_action_code is'|| p_action_code);
556 END IF;
557
558 l_action_code := p_action_code;
559
560 IF(p_action_code = 'CANCEL') THEN
561
562 IF(l_batch_type = 10) THEN
563 l_action_code := 'CANCEL_FPO';
564 ELSE
565 l_action_code := 'CANCEL_BATCH';
566 END IF;
567 END IF;
568
569 IF(p_action_code = 'CMPLT_DATE_CHANGE') THEN
570 l_action_code := 'PLAN_COMPL_DATE_CHANGED';
571 END IF;
572
573 IF g_debug <= gme_debug.g_log_procedure THEN
574 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_action_code is'|| l_action_code);
575 END IF;
576
577 OPEN CSR_for_so_line(l_so_line_id);
578 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
579 IF(CSR_for_so_line%NOTFOUND) THEN
580 CLOSE CSR_for_so_line;
581 IF g_debug <= gme_debug.g_log_procedure THEN
582 gme_debug.put_line(g_pkg_name||'.'||l_api_name ||' CSR_for_so_line%NOTFOUND, returning from Notify CSR');
583 END IF;
584 RETURN;
585 END IF;
586 CLOSE CSR_for_so_line;
587 IF g_debug <= gme_debug.g_log_procedure THEN
588 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_last_updated_by is '||l_last_updated_by);
589 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Business Event......');
590 END IF;
591
592 -- MakeToOrder BEGIN
593 IF p_action_code = 'NEW_BATCH_CREATED' THEN
594 IF g_debug <= gme_debug.g_log_procedure THEN
595 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Retrieve any pending lot for line '||p_batch_line_id);
596 END IF;
597 OPEN Get_pending_lot(p_batch_id ,p_batch_line_id );
598 FETCH Get_pending_lot INTO l_lot_number;
599 IF(Get_pending_lot%NOTFOUND) THEN
600 IF g_debug <= gme_debug.g_log_procedure THEN
601 gme_debug.put_line(g_pkg_name||'.'||l_api_name || ' no pending lot exists for line '|| p_batch_line_id);
602 END IF;
603 END IF;
604
605 CLOSE Get_pending_lot;
606 END IF;
607 -- MakeToOrder END
608
609 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
610 , p_approver => l_last_updated_by
611 , p_so_header_id=> l_so_header_id
612 , p_so_line_id => l_so_line_id
613 , p_batch_id => l_batch_id
614 , p_batch_line_id => NULL
615 , p_fpo_id => p_fpo_id
616 , p_organization_id => l_organization_id
617 , p_action_code => l_action_code );
618 */
619 wf_log_pkg.wf_debug_flag:=TRUE;
620 wf_event.AddParameterToList('SESSION_ID', l_session_id,l_parameter_list);
621 wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
622 wf_event.AddParameterToList('SO_HEADER_ID',l_so_header_id ,l_parameter_list);
623 wf_event.AddParameterToList('SO_LINE_ID',l_so_line_id ,l_parameter_list);
624 wf_event.AddParameterToList('BATCH_ID',l_batch_id ,l_parameter_list);
625 wf_event.AddParameterToList('BATCH_LINE_ID',p_batch_line_id ,l_parameter_list);
626 wf_event.AddParameterToList('FPO_ID',p_fpo_id ,l_parameter_list);
627 wf_event.AddParameterToList('ORGANIZATION_ID',l_organization_id ,l_parameter_list);
628 wf_event.AddParameterToList('ACTION_CODE',l_action_code ,l_parameter_list);
629 wf_event.AddParameterToList('LOT_NO',l_lot_number,l_parameter_list);
630 IF g_debug <= gme_debug.g_log_procedure THEN
631 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : raising business event ');
632 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : session_id '|| l_session_id );
633 End if;
634 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
635 p_event_key => l_session_id,
636 p_parameters => l_parameter_list);
637
638 IF(l_last_updated_by <> l_created_by) THEN
639
640 IF g_debug <= gme_debug.g_log_procedure THEN
641 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
642 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
643 END IF;
644
645 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
646 , p_approver => l_created_by
647 , p_so_header_id=> l_so_header_id
648 , p_so_line_id => l_so_line_id
649 , p_batch_id => l_batch_id
650 , p_batch_line_id => NULL
651 , p_fpo_id => NULL
652 , p_organization_id => l_organization_id
653 , p_action_code => l_action_code );
654 */
655 wf_event.AddParameterToList('APPROVER',l_created_by ,l_parameter_list);
656 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
657 p_event_key => l_session_id,
658 p_parameters => l_parameter_list);
659 END IF;
660 /* check to see when FPO converts to batches, the CSR for each new batch will get a notification*/
661 /* check to see if the same user has multiple sales lines for the reservations */
662 /* for each sales order or header_id, one notification is sent */
663 IF nvl(l_batch_line_id,0) = 0 THEN
664 for mul_line in check_mul_line_id1 (l_last_updated_by, l_batch_id ) Loop
665 l_old_header_id := l_so_header_id ;
666 l_so_line_id := mul_line.orig_demand_source_line_id ;
667 /* Get the Order and Line Information */
668 OPEN CSR_for_so_line(l_so_line_id);
669 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
670 CLOSE CSR_for_so_line;
671 IF l_new_header_id <> l_old_header_id THEN
672 l_so_header_id := l_new_header_id;
673 l_old_header_id := l_new_header_id;
674
675 IF g_debug <= gme_debug.g_log_procedure THEN
676 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
677 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
678 END IF;
679 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
680 , p_approver => l_last_updated_by
681 , p_so_header_id=> l_so_header_id
682 , p_so_line_id => l_so_line_id
683 , p_batch_id => l_batch_id
684 , p_batch_line_id => NULL
685 , p_fpo_id => NULL
686 , p_organization_id => l_organization_id
687 , p_action_code => l_action_code );
688 */
689 wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
690 wf_event.AddParameterToList('SO_LINE_ID',l_so_line_id ,l_parameter_list);
691 wf_event.AddParameterToList('SO_HEADER_ID',l_so_header_id ,l_parameter_list);
692 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
693 p_event_key => l_session_id,
694 p_parameters => l_parameter_list);
695
696 IF(l_last_updated_by <> l_created_by) THEN
697
698 IF g_debug <= gme_debug.g_log_procedure THEN
699 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
700 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
701 END IF;
702
703 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
704 , p_approver => l_created_by
705 , p_so_header_id=> l_so_header_id
706 , p_so_line_id => l_so_line_id
707 , p_batch_id => l_batch_id
708 , p_batch_line_id => NULL
709 , p_fpo_id => NULL
710 , p_organization_id => l_organization_id
711 , p_action_code => l_action_code );
712 */
713 wf_event.AddParameterToList('APPROVER',l_created_by ,l_parameter_list);
714 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
715 p_event_key => l_session_id,
716 p_parameters => l_parameter_list);
717 END IF;
718 END IF;
719 END LOOP;
720 Else
721 for mul_line in check_mul_line_id2 (l_last_updated_by, l_batch_id, l_batch_line_id ) Loop
722 l_old_header_id := l_so_header_id ;
723 l_so_line_id := mul_line.orig_demand_source_line_id ;
724 OPEN CSR_for_so_line(l_so_line_id);
725 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
726 CLOSE CSR_for_so_line;
727 IF l_new_header_id <> l_old_header_id THEN
728 l_so_header_id := l_new_header_id;
729 l_old_header_id := l_new_header_id;
730
731 IF g_debug <= gme_debug.g_log_procedure THEN
732 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
733 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
734 END IF;
735 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
736 , p_approver => l_last_updated_by
737 , p_so_header_id=> l_so_header_id
738 , p_so_line_id => l_so_line_id
739 , p_batch_id => l_batch_id
740 , p_batch_line_id => NULL
741 , p_fpo_id => NULL
742 , p_organization_id => l_organization_id
743 , p_action_code => l_action_code );
744 */
745 wf_event.AddParameterToList('APPROVER',l_last_updated_by ,l_parameter_list);
746 wf_event.AddParameterToList('SO_LINE_ID',l_so_line_id ,l_parameter_list);
747 wf_event.AddParameterToList('SO_HEADER_ID',l_so_header_id ,l_parameter_list);
748 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
749 p_event_key => l_session_id,
750 p_parameters => l_parameter_list);
751
752 IF(l_last_updated_by <> l_created_by) THEN
753
754 IF g_debug <= gme_debug.g_log_procedure THEN
755 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Notify CSR : l_created_by is '||l_created_by);
756 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Initiating the Workflow......');
757 END IF;
758
759 /*GME_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
760 , p_approver => l_created_by
761 , p_so_header_id=> l_so_header_id
762 , p_so_line_id => l_so_line_id
763 , p_batch_id => l_batch_id
764 , p_batch_line_id => NULL
765 , p_fpo_id => NULL
766 , p_organization_id => l_organization_id
767 , p_action_code => l_action_code );
768 */
769 wf_event.AddParameterToList('APPROVER',l_created_by ,l_parameter_list);
770 wf_event.raise(p_event_name => gme_common_pvt.G_BATCH_RESERVATIONS,
771 p_event_key => l_session_id,
772 p_parameters => l_parameter_list);
773 END IF;
774 END IF;
775 END LOOP;
776 END IF;
777 l_parameter_list.DELETE;
778
779
780 IF g_debug <= gme_debug.g_log_procedure THEN
781 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'Exiting Notify_CSR .............');
782 END IF;
783
784 EXCEPTION
785
786 WHEN OTHERS THEN
787 IF g_debug <= gme_debug.g_log_procedure THEN
788 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'WARNING.... In Others Exception in Notify CSR');
789 gme_debug.put_line(g_pkg_name||'.'||l_api_name || 'sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
790 END IF;
791
792 END notify_CSR;
793
794
795 PROCEDURE transfer_reservation_to_inv (
796 p_matl_dtl_rec IN gme_material_details%ROWTYPE
797 ,p_transaction_id IN NUMBER
798 ,x_message_count OUT NOCOPY NUMBER
799 ,x_message_list OUT NOCOPY VARCHAR2
800 ,x_return_status OUT NOCOPY VARCHAR2)
801 IS
802 l_api_name CONSTANT VARCHAR2 (30) := 'transfer_reservation_to_inv';
803 l_return_status VARCHAR2 (1);
804 l_msg_count NUMBER;
805 l_msg_data VARCHAR2 (2000);
806 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
807 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
808 l_to_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
809 l_rsv_rec_inq inv_reservation_global.mtl_reservation_rec_type;
810 l_mmt_rec mtl_material_transactions%ROWTYPE;
811 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
812 l_rsv_count NUMBER;
813 l_rsv_id NUMBER;
814 l_error_code NUMBER;
815 l_qty_to_transfer NUMBER := 0;
816 l_transfer_quantity NUMBER := 0;
817 l_transfer_primary NUMBER := 0;
818 l_transfer_secondary NUMBER := 0;
819 l_yield_txn_counter NUMBER;
820 l_transfer_complete VARCHAR2 (1) := 'N';
821 l_SO_RSV_exist VARCHAR2 (1) := 'Y';
822 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
823 -- ======================================================================
824 l_txn_quantity NUMBER;
825 l_txn_primary NUMBER;
826 l_txn_secondary NUMBER;
827 l_txn_lot VARCHAR2 (80);
828 l_primary_relieved NUMBER;
829 l_secondary_relieved NUMBER;
830 l_primary_remain NUMBER;
831 l_secondary_remain NUMBER;
832 -- ======================================================================
833
834 -- Bug 12805400
835 l_lot_divisible_flag VARCHAR2(1);
836 l_item_rec mtl_system_items%ROWTYPE;
837 error_get_item EXCEPTION;
838
839 transfer_reservation_err EXCEPTION;
840 update_reservation_err EXCEPTION;
841 query_reservation_error EXCEPTION;
842 uom_conversion_error EXCEPTION;
843 get_trans_err EXCEPTION;
844 notify_CSR_err EXCEPTION;
845 BEGIN
846 IF g_debug <= gme_debug.g_log_procedure THEN
847 gme_debug.put_line ('Entering api '
848 || g_pkg_name || '.'
849 || l_api_name);
850 END IF;
851
852 x_return_status := fnd_api.g_ret_sts_success;
853
854 IF g_debug <= gme_debug.g_log_statement THEN
855 gme_debug.put_line
856 ( g_pkg_name
857 || '.'
858 || l_api_name
859 || ' Retrieve data for transaction id => '
860 || p_transaction_id);
861 END IF;
862
863 GME_TRANSACTIONS_PVT.get_transactions (p_transaction_id => p_transaction_id
864 ,x_mmt_rec => l_mmt_rec
865 ,x_mmln_tbl => l_mmln_tbl
866 ,x_return_status => x_return_status);
867
868 IF x_return_status <> fnd_api.g_ret_sts_success THEN
869 IF (g_debug <= gme_debug.g_log_statement) THEN
870 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
871 END IF;
872 RAISE get_trans_err;
873 END IF;
874
875 -- For each production yield generated, we need to transfer the reservation supply to secure it.
876 -- =============================================================================================
877 IF NVL(l_mmln_tbl.count,0) > 0 THEN
878 l_yield_txn_counter := l_mmln_tbl.count;
879 ELSE
880 l_yield_txn_counter := 1;
881 END IF;
882 IF g_debug <= gme_debug.g_log_statement THEN
883 gme_debug.put_line
884 ( g_pkg_name
885 || '.'
886 || l_api_name
887 || ' Looping through transaction yield for a count of => '
888 || l_yield_txn_counter);
889 END IF;
890
891 FOR x in 1..l_yield_txn_counter LOOP
892 IF l_SO_RSV_exist = 'N' THEN
893 IF g_debug <= gme_debug.g_log_statement THEN
894 gme_debug.put_line
895 ( g_pkg_name
896 || '.'
897 || l_api_name
898 || 'No reservations outstanding for this supply line so exit now');
899 END IF;
900 EXIT;
901 END IF;
902 IF NVL(l_mmln_tbl.count,0) > 0 THEN
903 -- If mmli is populated, use the lot level data
904 IF g_debug <= gme_debug.g_log_statement THEN
905 gme_debug.put_line
906 ( g_pkg_name
907 || '.'
908 || l_api_name
909 || ' Using lot level transaction data, lot is => '
910 || l_mmln_tbl(x).lot_number);
911 END IF;
912 l_txn_quantity := l_mmln_tbl(x).transaction_quantity;
913 l_txn_primary := l_mmln_tbl(x).primary_quantity;
914 l_txn_secondary:= l_mmln_tbl(x).secondary_transaction_quantity;
915 l_txn_lot := l_mmln_tbl(x).lot_number;
916 ELSE
917 -- Use the transaction level data
918 IF g_debug <= gme_debug.g_log_statement THEN
919 gme_debug.put_line
920 ( g_pkg_name
921 || '.'
922 || l_api_name
923 || ' Item is NOT lot controlled ');
924 END IF;
925 l_txn_quantity := l_mmt_rec.transaction_quantity;
926 l_txn_primary := l_mmt_rec.primary_quantity;
927 l_txn_secondary:= l_mmt_rec.secondary_transaction_quantity;
928 l_txn_lot := NULL;
929 END IF;
930
931 IF g_debug <= gme_debug.g_log_statement THEN
932 gme_debug.put_line
933 ( g_pkg_name
934 || '.'
935 || l_api_name
936 || ' primary transaction quantity to be processed is '
937 || l_txn_primary );
938 END IF;
939 l_transfer_complete := 'N';
940 -- LOOP until either:
941 -- a) The transaction quantity has been fully transferred from PROD supply to INV supply
942 -- OR
943 -- b) There are no reservations against PROD supply to transfer
944 -- =======================================================================================
945 WHILE l_transfer_complete = 'N' LOOP
946 -- Query for reserved sales demand against this material detail line.
947 -- If reservations exist, we will transfer the supply source from production to inventory
948 -- Otherwise, if no sales demand exists, nothing more to do.
949 -- =========================================================
950
951 GME_SUPPLY_RES_PVT.query_prod_supply_reservations
952 (p_matl_dtl_rec => p_matl_dtl_rec
953 ,x_mtl_reservation_tbl => l_rsv_tbl
954 ,x_mtl_reservation_tbl_count => l_rsv_count
955 ,x_msg_count => x_message_count
956 ,x_msg_data => x_message_list
957 ,x_return_status => x_return_status);
958
959 IF g_debug <= gme_debug.g_log_statement THEN
960 gme_debug.put_line
961 ( g_pkg_name
962 || '.'
963 || l_api_name
964 || 'Return status from query_prod_supply_reservations is '
965 || x_return_status);
966 gme_debug.put_line
967 ( g_pkg_name
968 || '.'
969 || l_api_name
970 || ' number of reservations is '
971 || l_rsv_count);
972 END IF;
973
974 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
975 IF g_debug <= gme_debug.g_log_statement THEN
976 gme_debug.put_line
977 ( g_pkg_name
978 || '.'
979 || l_api_name
980 || 'Error is :'
981 || x_message_list);
982 END IF;
983 RAISE query_reservation_error;
984 END IF;
985
986 -- if there are no reservations then there is no work to do so exit now
987 -- ====================================================================
988 IF NVL(l_rsv_count,0) = 0 THEN
989 IF g_debug <= gme_debug.g_log_statement THEN
990 gme_debug.put_line
991 ( g_pkg_name
992 || '.'
993 || l_api_name
994 || 'No SO reservation demand so EXIT now ');
995 END IF;
996 l_transfer_complete := 'Y';
997 EXIT;
998 END IF;
999
1000 l_rsv_rec := l_rsv_tbl (1);
1001
1002 -- We will transfer the reserved quantity OR the transaction quantity; whichever is the smaller
1003 -- ================================================================================================
1004 IF g_debug <= gme_debug.g_log_statement THEN
1005 gme_debug.put_line
1006 ( g_pkg_name
1007 || '.'
1008 || l_api_name
1009 || ' Transaction primary quantity is '
1010 || l_txn_primary
1011 || ' Reservation quantity in primary is '
1012 || l_rsv_rec.primary_reservation_quantity );
1013 END IF;
1014
1015 -- Bug 12805400 - Put in check for indivisible item.
1016 gme_material_detail_pvt.get_item_rec
1017 (p_org_id => l_rsv_rec.organization_id
1018 ,p_item_id => l_rsv_rec.inventory_item_id
1019 ,x_item_rec => l_item_rec
1020 ,x_return_status => l_return_status);
1021
1022 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1023 x_return_status := l_return_status;
1024 RAISE error_get_item;
1025 END IF;
1026
1027 l_lot_divisible_flag := 'Y';
1028 IF l_item_rec.lot_control_code = 2 AND NVL(l_item_rec.lot_divisible_flag,'Y') = 'N' THEN
1029 l_lot_divisible_flag := 'N';
1030 END IF;
1031
1032 IF l_txn_primary <= l_rsv_rec.primary_reservation_quantity THEN
1033 l_transfer_primary := l_txn_primary;
1034 l_transfer_secondary := l_txn_secondary;
1035 ELSE
1036 -- Bug 12805400 - Put in check for indivisible item.
1037 -- Update the reservation for indivisible items to avoid qty issues.
1038 -- Take the whole transaction qty if the item is indivisible.
1039 IF l_lot_divisible_flag = 'N' THEN
1040 update mtl_reservations
1041 set reservation_quantity = l_txn_quantity,
1042 primary_reservation_quantity = l_txn_primary,
1043 secondary_reservation_quantity = l_txn_secondary
1044 where reservation_id = l_rsv_rec.reservation_id;
1045
1046 l_transfer_primary := l_txn_primary;
1047 l_transfer_secondary := l_txn_secondary;
1048 ELSE
1049 l_transfer_primary := l_rsv_rec.primary_reservation_quantity;
1050 l_transfer_secondary := l_rsv_rec.secondary_reservation_quantity;
1051 -- If necessary, Compute the secondary quantity allowing for lot specific conversions
1052 -- because we are moving from a high level reservation to a detailed reservation.
1053 -- ==================================================================================
1054
1055 /* Bug#7422975 Added the bwlo conidion to convert the secondary
1056 quantity only when the secondary uom is not null */
1057
1058 IF l_txn_lot IS NOT NULL and l_rsv_rec.secondary_uom_code IS NOT NULL THEN
1059 l_transfer_secondary := inv_convert.inv_um_convert(
1060 item_id => l_rsv_rec.inventory_item_id
1061 , lot_number => l_txn_lot
1062 , organization_id => l_rsv_rec.organization_id
1063 , PRECISION => 5
1064 , from_quantity => l_transfer_primary
1065 , from_unit => l_rsv_rec.primary_uom_code
1066 , to_unit => l_rsv_rec.secondary_uom_code
1067 , from_name => NULL -- from uom name
1068 , to_name => NULL -- to uom name
1069 );
1070 IF g_debug <= gme_debug.g_log_statement THEN
1071 gme_debug.put_line
1072 ( g_pkg_name
1073 || '.'
1074 || l_api_name
1075 || ' After UOM conversion the secondary qty is '
1076 || l_transfer_secondary );
1077 END IF;
1078
1079 IF l_transfer_secondary = -99999 THEN
1080 -- conversion failed
1081 RAISE uom_conversion_error;
1082 END IF;
1083 END IF;
1084 END IF; -- IF l_lot_divisible_flag = 'N'
1085 END IF;
1086
1087 IF g_debug <= gme_debug.g_log_statement THEN
1088 gme_debug.put_line
1089 ( g_pkg_name
1090 || '.'
1091 || l_api_name
1092 || ' primary quantity to be reserved against INV is'
1093 || l_transfer_primary );
1094 END IF;
1095 -- Set the qty in the reservation UOM if this matches the primary or secondary
1096 -- else set it to null and the Reservations API will compute it
1097 -- ============================================================================
1098 IF l_rsv_rec.primary_uom_code = l_rsv_rec.reservation_uom_code THEN
1099 l_transfer_quantity := l_transfer_primary;
1100 ELSIF l_rsv_rec.secondary_uom_code = l_rsv_rec.reservation_uom_code THEN
1101 l_transfer_quantity := l_transfer_secondary;
1102 ELSE
1103 l_transfer_quantity := NULL;
1104 END IF;
1105 IF g_debug <= gme_debug.g_log_statement THEN
1106 gme_debug.put_line
1107 ( g_pkg_name
1108 || '.'
1109 || l_api_name
1110 || ' This is the primary quantity we are about to transfer => '
1111 || l_transfer_primary
1112 || ' This is the secondary quantity we are about to transfer =>'
1113 || l_transfer_secondary );
1114 END IF;
1115
1116 -- Ensure that the yield inventory is reservable ahead of attempting to transfer
1117 -- =============================================================================
1118
1119 IF inv_detail_util_pvt.is_sub_loc_lot_reservable
1120 ( p_organization_id => l_rsv_rec.organization_id
1121 , p_inventory_item_id => l_rsv_rec.inventory_item_id
1122 , p_subinventory_code => l_mmt_rec.subinventory_code
1123 , p_locator_id => l_mmt_rec.locator_id
1124 , p_lot_number => l_txn_lot) THEN
1125 -- Target Inventory IS RESERVABLE
1126 -- Transfer the reservation supply source from JOB to INVENTORY
1127 -- Use the detailing outlined on the COMPLETION transaction
1128 -- ============================================================
1129 IF g_debug <= gme_debug.g_log_statement THEN
1130 gme_debug.put_line
1131 ( g_pkg_name
1132 || '.'
1133 || l_api_name
1134 || ' This inventory yield IS reservable so proceed with transfer ');
1135 END IF;
1136 l_to_rsv_rec := l_rsv_rec;
1137 l_to_rsv_rec.reservation_id := NULL; -- New reservation
1138 l_to_rsv_rec.revision := l_mmt_rec.revision;
1139 l_to_rsv_rec.subinventory_code := l_mmt_rec.subinventory_code;
1140 l_to_rsv_rec.locator_id := l_mmt_rec.locator_id;
1141 l_to_rsv_rec.lot_number := l_txn_lot;
1142 l_to_rsv_rec.lpn_id := l_mmt_rec.transfer_lpn_id; -- Bug 8343517
1143 l_to_rsv_rec.subinventory_id := NULL;
1144 l_to_rsv_rec.lot_number_id := NULL;
1145 l_to_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
1146 l_to_rsv_rec.supply_source_header_id := NULL;
1147 l_to_rsv_rec.supply_source_line_id := NULL;
1148 l_to_rsv_rec.supply_source_name := NULL;
1149 l_to_rsv_rec.supply_source_line_detail := NULL;
1150 l_to_rsv_rec.reservation_quantity := l_transfer_quantity;
1151 l_to_rsv_rec.primary_reservation_quantity := l_transfer_primary;
1152 l_to_rsv_rec.secondary_reservation_quantity := l_transfer_secondary;
1153
1154 IF (g_debug <= gme_debug.g_log_statement) THEN
1155 gme_debug.put_line ('Calling inv_reservation_pub.transfer_reservation');
1156 END IF;
1157
1158 inv_reservation_pub.Transfer_Reservation
1159 ( p_api_version_number => 1.0
1160 , p_init_msg_lst => FND_API.G_TRUE
1161 , x_return_status => x_return_status
1162 , x_msg_count => x_message_count
1163 , x_msg_data => x_message_list
1164 , p_original_rsv_rec => l_rsv_rec
1165 , p_to_rsv_rec => l_to_rsv_rec
1166 , p_original_serial_number => l_dummy_sn
1167 , p_to_serial_number => l_dummy_sn
1168 , p_validation_flag => FND_API.G_FALSE
1169 , x_to_reservation_id => l_rsv_id);
1170
1171 IF (g_debug <= gme_debug.g_log_statement) THEN
1172 gme_debug.put_line ('After Calling inv_reservation_pub.transfer_reservation status is '||x_return_status);
1173 END IF;
1174
1175 IF (l_return_status IN
1176 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1177 RAISE transfer_reservation_err;
1178 END IF;
1179 ELSE
1180 -- Target Inventory IS NOT RESERVABLE so we cannot transfer the reservation
1181 -- Need to debit the original SUPPLY reservation and notify sales staff is there is sales impact
1182 -- =============================================================================================
1183 IF g_debug <= gme_debug.g_log_statement THEN
1184 gme_debug.put_line
1185 ( g_pkg_name
1186 || '.'
1187 || l_api_name
1188 || ' This inventory yield IS NOT reservable so CANNOT transfer resv supply for line_id '
1189 || l_rsv_rec.supply_source_line_id);
1190 END IF;
1191 -- If demand is from sales, issue Notification Advising that Reservation Transfer is not possible
1192 -- ==============================================================================================
1193 IF l_rsv_rec.demand_source_type_id = inv_reservation_global.g_source_type_oe THEN
1194 GME_SUPPLY_RES_PVT.notify_CSR
1195 ( P_Batch_id => NULL
1196 , P_Batch_line_id => l_rsv_rec.supply_source_line_id
1197 , P_So_line_id => l_rsv_rec.demand_source_line_id
1198 , P_batch_trans_id => NULL
1199 , P_organization_id => p_matl_dtl_rec.organization_id
1200 , P_action_code => 'NON_RSV_STATUS'
1201 , X_return_status => x_return_status
1202 , X_msg_cont => x_message_count
1203 , X_msg_data => x_message_list );
1204
1205 IF g_debug <= gme_debug.g_log_procedure THEN
1206 gme_debug.put_line ( g_pkg_name || '.'
1207 || l_api_name
1208 || ' after calling notify_CSR status is '
1209 || x_return_status );
1210 END IF;
1211
1212 IF (x_return_status IN (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1213 RAISE notify_CSR_err;
1214 END IF;
1215
1216 END IF;
1217
1218 IF g_debug <= gme_debug.g_log_statement THEN
1219 gme_debug.put_line
1220 ( g_pkg_name
1221 || '.'
1222 || l_api_name
1223 || ' Invoke relieve reservation for quantity of '
1224 || l_transfer_primary );
1225 END IF;
1226 inv_reservation_pub.relieve_reservation
1227 ( p_api_version_number => 1.0
1228 , p_init_msg_lst => fnd_api.g_true
1229 , x_return_status => x_return_status
1230 , x_msg_count => x_message_count
1231 , x_msg_data => x_message_list
1232 , p_rsv_rec => l_rsv_rec
1233 , p_primary_relieved_quantity => l_transfer_primary
1234 , p_secondary_relieved_quantity => l_transfer_secondary
1235 , p_relieve_all => fnd_api.g_false
1236 , p_original_serial_number => l_dummy_sn -- no serial control
1237 , p_validation_flag => fnd_api.g_true
1238 , x_primary_relieved_quantity => l_primary_relieved
1239 , x_secondary_relieved_quantity => l_secondary_relieved
1240 , x_primary_remain_quantity => l_primary_remain
1241 , x_secondary_remain_quantity => l_secondary_remain
1242 );
1243
1244 IF g_debug <= gme_debug.g_log_procedure THEN
1245 gme_debug.put_line ( g_pkg_name || '.'
1246 || l_api_name
1247 || ' after calling relieve_reservation status is '
1248 || x_return_status );
1249 END IF;
1250
1251 IF (x_return_status IN
1252 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1253 RAISE update_reservation_err;
1254 END IF;
1255 END IF; -- End of handling for non reservable inventory
1256
1257 -- Quantity has processed
1258 -- Is there outstanding quantity to process for this particular txn quantity
1259 -- ==========================================================================
1260 IF l_txn_primary - l_transfer_primary <= 0 THEN
1261 l_transfer_complete := 'Y';
1262 ELSE
1263 l_txn_primary := l_txn_primary - l_transfer_primary;
1264 l_txn_secondary := l_txn_secondary - l_transfer_secondary;
1265 END IF;
1266 END LOOP; -- WHILE l_l_transfer_complete = 'N'
1267 END LOOP; -- Transaction Quantity Processing
1268
1269 IF g_debug <= gme_debug.g_log_procedure THEN
1270 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN uom_conversion_error THEN
1275 x_return_status := fnd_api.g_ret_sts_error;
1276 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1277 fnd_message.set_token ('ITEM_NO', l_rsv_rec.inventory_item_id);
1278 fnd_message.set_token ('FROM_UOM', l_rsv_rec.primary_uom_code);
1279 fnd_message.set_token ('TO_UOM', l_rsv_rec.secondary_uom_code);
1280 IF (g_debug <= gme_debug.g_log_error) THEN
1281 gme_debug.put_line
1282 ( 'unit of measure conversion error for item '
1283 || l_rsv_rec.inventory_item_id
1284 || 'from unit '
1285 || l_rsv_rec.primary_uom_code
1286 || 'to unit '
1287 || l_rsv_rec.secondary_uom_code);
1288 END IF;
1289 WHEN transfer_reservation_err THEN
1290 IF (g_debug <= gme_debug.g_log_error) THEN
1291 gme_debug.put_line
1292 ( 'terminating due to error in inv_reservation_pub.transfer_reservation ');
1293 gme_debug.put_line ('error message is ' || x_message_list);
1294 END IF;
1295 WHEN update_reservation_err THEN
1296 IF (g_debug <= gme_debug.g_log_error) THEN
1297 gme_debug.put_line
1298 ( 'terminating due to error in inv_reservation_pub.update_reservation ');
1299 gme_debug.put_line ('error message is ' || x_message_list);
1300 END IF;
1301 WHEN query_reservation_error THEN
1302 IF (g_debug <= gme_debug.g_log_error) THEN
1303 gme_debug.put_line
1304 ( 'terminating due to error querying reservations ');
1305 gme_debug.put_line ('error message is ' || x_message_list);
1306 END IF;
1307 WHEN notify_CSR_err THEN
1308 IF (g_debug <= gme_debug.g_log_error) THEN
1309 gme_debug.put_line
1310 ( 'terminating due to error invoking notify_CSR ');
1311 END IF;
1312 WHEN get_trans_err THEN
1313 IF (g_debug <= gme_debug.g_log_error) THEN
1314 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
1315 END IF;
1316 WHEN error_get_item THEN
1317 IF (g_debug <= gme_debug.g_log_error) THEN
1318 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_item_rec');
1319 END IF;
1320
1321 WHEN OTHERS THEN
1322 IF g_debug <= gme_debug.g_log_unexpected THEN
1323 gme_debug.put_line ( 'When others exception in '
1324 || g_pkg_name
1325 || '.'
1326 || l_api_name
1327 || ' Error is '
1328 || SQLERRM);
1329 END IF;
1330
1331 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1332 x_return_status := fnd_api.g_ret_sts_unexp_error;
1333
1334 END transfer_reservation_to_inv;
1335
1336 PROCEDURE query_prod_supply_reservations(
1337 p_matl_dtl_rec IN gme_material_details%ROWTYPE
1338 ,x_mtl_reservation_tbl OUT NOCOPY inv_reservation_global.mtl_reservation_tbl_type
1339 ,x_mtl_reservation_tbl_count OUT NOCOPY NUMBER
1340 ,x_msg_count OUT NOCOPY NUMBER
1341 ,x_msg_data OUT NOCOPY VARCHAR2
1342 ,x_return_status OUT NOCOPY VARCHAR2)
1343 IS
1344 l_api_name CONSTANT VARCHAR2 (30) := 'query_prod_supply_reservations';
1345 l_return_status VARCHAR2 (1);
1346 l_msg_count NUMBER;
1347 l_msg_data VARCHAR2 (2000);
1348 l_total_primary_demand NUMBER;
1349
1350 l_rsv_rec_inq inv_reservation_global.mtl_reservation_rec_type;
1351 l_error_code NUMBER;
1352 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1353 -- ======================================================================
1354 query_reservation_error EXCEPTION;
1355 BEGIN
1356 IF g_debug <= gme_debug.g_log_procedure THEN
1357 gme_debug.put_line ('Entering api '
1358 || g_pkg_name || '.'
1359 || l_api_name);
1360 END IF;
1361
1362 x_return_status := fnd_api.g_ret_sts_success;
1363
1364 -- If material_detail_id supplied,retrieve the reservations against this supply line
1365 -- =================================================================================
1366 IF p_matl_dtl_rec.material_detail_id is NOT NULL THEN
1367 l_rsv_rec_inq.organization_id := p_matl_dtl_rec.organization_id;
1368 l_rsv_rec_inq.inventory_item_id := p_matl_dtl_rec.inventory_item_id;
1369 l_rsv_rec_inq.supply_source_type_id := 5;
1370 l_rsv_rec_inq.supply_source_header_id := p_matl_dtl_rec.batch_id;
1371 l_rsv_rec_inq.supply_source_line_id := p_matl_dtl_rec.material_detail_id;
1372 -- l_rsv_rec_inq.demand_source_type_id := 2;
1373
1374 IF g_debug <= gme_debug.g_log_statement THEN
1375 gme_debug.put_line
1376 ( g_pkg_name
1377 || '.'
1378 || l_api_name
1379 || ' Calling inv_reservation_pub.query_reservation for reservations against PROD supply for line'
1380 || p_matl_dtl_rec.material_detail_id);
1381 END IF;
1382 -- If batch_id only supplied,retrieve the reservations against this supply source
1383 -- ==============================================================================
1384
1385 ELSE
1386 l_rsv_rec_inq.supply_source_type_id := 5;
1387 l_rsv_rec_inq.supply_source_header_id := p_matl_dtl_rec.batch_id;
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 || ' Calling inv_reservation_pub.query_reservation for reservations against PROD supply for batce'
1395 || p_matl_dtl_rec.batch_id);
1396 END IF;
1397 END IF;
1398
1399 inv_reservation_pub.query_reservation
1400 (p_api_version_number => 1.0
1401 ,p_init_msg_lst => fnd_api.g_false
1402 ,x_return_status => x_return_status
1403 ,x_msg_count => x_msg_count
1404 ,x_msg_data => x_msg_data
1405 ,p_query_input => l_rsv_rec_inq
1406 ,p_lock_records => fnd_api.g_false
1407 ,p_sort_by_req_date => inv_reservation_global.g_query_req_date_asc
1408 ,x_mtl_reservation_tbl => x_mtl_reservation_tbl
1409 ,x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count
1410 ,x_error_code => l_error_code);
1411
1412 IF g_debug <= gme_debug.g_log_statement THEN
1413 gme_debug.put_line
1414 ( g_pkg_name
1415 || '.'
1416 || l_api_name
1417 || 'Return status from inv_reservation_pub.query_reservation is '
1418 || l_return_status
1419 || ' Error code is '
1420 || l_error_code);
1421 END IF;
1422
1423 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1424 RAISE query_reservation_error;
1425 END IF;
1426
1427 IF g_debug <= gme_debug.g_log_statement THEN
1428 gme_debug.put_line
1429 ( g_pkg_name
1430 || '.'
1431 || l_api_name
1432 || ' Number of reservations against this production supply line is '
1433 || x_mtl_reservation_tbl_count);
1434 END IF;
1435
1436 IF g_debug <= gme_debug.g_log_procedure THEN
1437 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1438 END IF;
1439
1440 EXCEPTION
1441 WHEN query_reservation_error THEN
1442 IF (g_debug <= gme_debug.g_log_error) THEN
1443 gme_debug.put_line
1444 ( 'inv_reservation_pub.query_reservation returns '
1445 || x_return_status);
1446 gme_debug.put_line ('error message is ' || x_msg_data);
1447 END IF;
1448
1449 WHEN OTHERS THEN
1450 IF g_debug <= gme_debug.g_log_unexpected THEN
1451 gme_debug.put_line ( 'When others exception in '
1452 || g_pkg_name
1453 || '.'
1454 || l_api_name
1455 || ' Error is '
1456 || SQLERRM);
1457 END IF;
1458
1459 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1460 x_return_status := fnd_api.g_ret_sts_unexp_error;
1461
1462 END query_prod_supply_reservations;
1463
1464 PROCEDURE relieve_prod_supply_resv (
1465 p_matl_dtl_rec IN gme_material_details%ROWTYPE
1466 ,x_msg_count OUT NOCOPY NUMBER
1467 ,x_msg_data OUT NOCOPY VARCHAR2
1468 ,x_return_status OUT NOCOPY VARCHAR2)
1469 IS
1470 l_api_name CONSTANT VARCHAR2 (30) := 'relieve_prod_supply_resv';
1471 l_rsv_count NUMBER;
1472
1473 l_primary_to_unreserve NUMBER;
1474 l_secondary_to_unreserve NUMBER;
1475 l_total_primary_demand NUMBER;
1476 l_available_quantity NUMBER;
1477 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1478 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1479 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1480 l_primary_relieved NUMBER;
1481 l_secondary_relieved NUMBER;
1482 l_primary_remain NUMBER;
1483 l_secondary_remain NUMBER;
1484 -- ======================================================================
1485 query_reservation_error EXCEPTION;
1486 update_reservation_err EXCEPTION;
1487 delete_reservation_err EXCEPTION;
1488 notify_CSR_err EXCEPTION;
1489 BEGIN
1490 IF g_debug <= gme_debug.g_log_procedure THEN
1491 gme_debug.put_line ('Entering api '
1492 || g_pkg_name || '.'
1493 || l_api_name
1494 || ' material_detail_id '
1495 || p_matl_dtl_rec.material_detail_id);
1496 END IF;
1497
1498 x_return_status := fnd_api.g_ret_sts_success;
1499
1500 IF p_matl_dtl_rec.line_type not in (1,2) THEN
1501 RETURN;
1502 END IF;
1503
1504 GME_SUPPLY_RES_PVT.query_prod_supply_reservations
1505 (p_matl_dtl_rec => p_matl_dtl_rec
1506 ,x_mtl_reservation_tbl => l_rsv_tbl
1507 ,x_mtl_reservation_tbl_count => l_rsv_count
1508 ,x_msg_count => x_msg_count
1509 ,x_msg_data => x_msg_data
1510 ,x_return_status => x_return_status);
1511
1512 IF g_debug <= gme_debug.g_log_statement THEN
1513 gme_debug.put_line
1514 ( g_pkg_name
1515 || '.'
1516 || l_api_name
1517 || 'Return status from query_prod_supply_reservations is '
1518 || x_return_status);
1519 gme_debug.put_line
1520 ( g_pkg_name
1521 || '.'
1522 || l_api_name
1523 || ' number of reservations is '
1524 || l_rsv_count);
1525 END IF;
1526
1527 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1528 IF g_debug <= gme_debug.g_log_statement THEN
1529 gme_debug.put_line
1530 ( g_pkg_name
1531 || '.'
1532 || l_api_name
1533 || 'Error is :'
1534 || x_msg_data);
1535 END IF;
1536 RAISE query_reservation_error;
1537 END IF;
1538
1539 IF NVL(l_rsv_count,0) <= 0 THEN
1540 IF g_debug <= gme_debug.g_log_statement THEN
1541 gme_debug.put_line
1542 ( g_pkg_name
1543 || '.'
1544 || l_api_name
1545 || ' No reservations against material detail line as a source of supply so RETURN now ');
1546 END IF;
1547 RETURN;
1548 END IF;
1549
1550 -- Compute the total reservational demand against this material detail line.
1551 -- =========================================================================
1552 FOR I IN 1..l_rsv_tbl.COUNT LOOP
1553 l_total_primary_demand := NVL(l_total_primary_demand,0) + l_rsv_tbl(I).primary_reservation_quantity;
1554 END LOOP;
1555
1556 -- Determine the available supply quantity in the primary UOM
1557 -- ==========================================================
1558 l_available_quantity := NVL(p_matl_dtl_rec.wip_plan_qty, p_matl_dtl_rec.plan_qty) - p_matl_dtl_rec.actual_qty;
1559 IF l_rsv_tbl(1).primary_uom_code <> p_matl_dtl_rec.dtl_um THEN
1560 l_available_quantity := inv_convert.inv_um_convert
1561 (item_id => p_matl_dtl_rec.inventory_item_id
1562 ,precision => 5
1563 ,from_quantity => l_available_quantity
1564 ,from_unit => p_matl_dtl_rec.dtl_um
1565 ,to_unit => l_rsv_tbl(1).primary_uom_code
1566 ,from_name => NULL
1567 ,to_name => NULL);
1568 END IF;
1569 -- Compare supply and demand. If supply meets/exceeds demand, there is nothing to do
1570 -- =================================================================================
1571 IF g_debug <= gme_debug.g_log_statement THEN
1572 gme_debug.put_line
1573 ( g_pkg_name
1574 || '.'
1575 || l_api_name
1576 || ' Current demand figure => '
1577 || l_total_primary_demand
1578 || ' Current supply figure => '
1579 || l_available_quantity);
1580 END IF;
1581 IF l_available_quantity >= l_total_primary_demand THEN
1582 IF g_debug <= gme_debug.g_log_statement THEN
1583 gme_debug.put_line
1584 ( g_pkg_name
1585 || '.'
1586 || l_api_name
1587 || ' Supply exceeds Demand so no adjustments required; return now ');
1588 END IF;
1589 RETURN;
1590 END IF;
1591
1592 l_primary_to_unreserve := l_total_primary_demand - l_available_quantity;
1593
1594 FOR I IN 1..l_rsv_tbl.COUNT LOOP
1595
1596 l_rsv_rec := l_rsv_tbl(I);
1597 IF g_debug <= gme_debug.g_log_procedure THEN
1598 gme_debug.put_line ( g_pkg_name || '.'
1599 || l_api_name
1600 || ' primary reserved qty '
1601 || l_rsv_rec.primary_reservation_quantity);
1602 END IF;
1603
1604 -- Notify the sales department that reservations are being relieved
1605 -- ================================================================
1606 GME_SUPPLY_RES_PVT.notify_CSR
1607 ( P_Batch_id => p_matl_dtl_rec.batch_id
1608 , P_Batch_line_id => p_matl_dtl_rec.material_detail_id
1609 , P_So_line_id => l_rsv_rec.demand_source_line_id
1610 , P_batch_trans_id => NULL
1611 , P_organization_id => p_matl_dtl_rec.organization_id
1612 , P_action_code => 'REDUCE_PLANNED_QTY'
1613 , X_return_status => x_return_status
1614 , X_msg_cont => x_msg_count
1615 , X_msg_data => x_msg_data );
1616
1617 IF g_debug <= gme_debug.g_log_procedure THEN
1618 gme_debug.put_line ( g_pkg_name || '.'
1619 || l_api_name
1620 || ' after calling notify_CSR status is '
1621 || x_return_status );
1622 END IF;
1623
1624 IF (x_return_status IN
1625 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1626 RAISE notify_CSR_err;
1627 END IF;
1628
1629 IF (l_rsv_rec.primary_reservation_quantity <= l_primary_to_unreserve) THEN
1630 IF g_debug <= gme_debug.g_log_procedure THEN
1631 gme_debug.put_line ( g_pkg_name || '.'
1632 || l_api_name
1633 || ' about to invoke delete_reservation ');
1634 END IF;
1635
1636 inv_reservation_pub.delete_reservation
1637 ( p_api_version_number => 1.0
1638 , p_init_msg_lst => fnd_api.g_true
1639 , x_return_status => x_return_status
1640 , x_msg_count => x_msg_count
1641 , x_msg_data => x_msg_data
1642 , p_rsv_rec => l_rsv_rec
1643 , p_serial_number => l_dummy_sn
1644 );
1645
1646 IF g_debug <= gme_debug.g_log_procedure THEN
1647 gme_debug.put_line ( g_pkg_name || '.'
1648 || l_api_name
1649 || ' after calling delete_reservation status is '
1650 || x_return_status );
1651 END IF;
1652
1653
1654 IF (x_return_status IN
1655 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1656 RAISE delete_reservation_err;
1657 END IF;
1658
1659 l_primary_to_unreserve := l_primary_to_unreserve -
1660 l_rsv_rec.primary_reservation_quantity;
1661
1662
1663 ELSE -- res rec qty > l_primary_to_unreserve
1664
1665 IF l_rsv_rec.secondary_uom_code is not NULL THEN
1666 l_secondary_to_unreserve := inv_convert.inv_um_convert(
1667 item_id => l_rsv_rec.inventory_item_id
1668 , lot_number => l_rsv_rec.lot_number
1669 , organization_id => l_rsv_rec.organization_id
1670 , precision => NULL
1671 , from_quantity => l_primary_to_unreserve
1672 , from_unit => l_rsv_rec.primary_uom_code
1673 , to_unit => l_rsv_rec.secondary_uom_code
1674 , from_name => NULL
1675 , to_name => NULL);
1676 END IF;
1677
1678 IF g_debug <= gme_debug.g_log_procedure THEN
1679 gme_debug.put_line ( g_pkg_name || '.'
1680 || l_api_name
1681 || 'qty to relieve => '
1682 || l_primary_to_unreserve
1683 || ' secondary qty to relieve => '
1684 || l_secondary_to_unreserve
1685 || ' call relieve_reservation ');
1686 END IF;
1687
1688 inv_reservation_pub.relieve_reservation
1689 ( p_api_version_number => 1.0
1690 , p_init_msg_lst => fnd_api.g_true
1691 , x_return_status => x_return_status
1692 , x_msg_count => x_msg_count
1693 , x_msg_data => x_msg_data
1694 , p_rsv_rec => l_rsv_rec
1695 , p_primary_relieved_quantity => l_primary_to_unreserve
1696 , p_secondary_relieved_quantity => l_secondary_to_unreserve
1697 , p_relieve_all => fnd_api.g_false
1698 , p_original_serial_number => l_dummy_sn -- no serial control
1699 , p_validation_flag => fnd_api.g_true
1700 , x_primary_relieved_quantity => l_primary_relieved
1701 , x_secondary_relieved_quantity => l_secondary_relieved
1702 , x_primary_remain_quantity => l_primary_remain
1703 , x_secondary_remain_quantity => l_secondary_remain
1704 );
1705
1706 IF g_debug <= gme_debug.g_log_procedure THEN
1707 gme_debug.put_line ( g_pkg_name || '.'
1708 || l_api_name
1709 || ' after calling relieve_reservation status is '
1710 || x_return_status );
1711 END IF;
1712
1713 IF (x_return_status IN
1714 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1715 RAISE update_reservation_err;
1716 END IF;
1717
1718 l_primary_to_unreserve := 0;
1719
1720 END IF;
1721
1722 IF (l_primary_to_unreserve <= 0) THEN
1723 -- job done so exit the loop
1724 EXIT;
1725 END IF;
1726
1727 END LOOP;
1728
1729 IF g_debug <= gme_debug.g_log_procedure THEN
1730 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1731 END IF;
1732 EXCEPTION
1733 WHEN delete_reservation_err THEN
1734 IF (g_debug <= gme_debug.g_log_error) THEN
1735 gme_debug.put_line
1736 ( 'inv_reservation_pub.delete_reservation failure ');
1737 gme_debug.put_line ('error message is ' || x_msg_data);
1738 END IF;
1739 WHEN update_reservation_err THEN
1740 IF (g_debug <= gme_debug.g_log_error) THEN
1741 gme_debug.put_line
1742 ( 'inv_reservation_pub.relieve_reservation failure ');
1743 gme_debug.put_line ('error message is ' || x_msg_data);
1744 END IF;
1745 WHEN notify_CSR_err THEN
1746 IF (g_debug <= gme_debug.g_log_error) THEN
1747 gme_debug.put_line
1748 ( 'inv_reservation_pub.update_reservation failure ');
1749 gme_debug.put_line ('error message is ' || x_msg_data);
1750 END IF;
1751
1752
1753 WHEN OTHERS THEN
1754 IF g_debug <= gme_debug.g_log_unexpected THEN
1755 gme_debug.put_line ( 'When others exception in '
1756 || g_pkg_name
1757 || '.'
1758 || l_api_name
1759 || ' Error is '
1760 || SQLERRM);
1761 END IF;
1762
1763 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1764 x_return_status := fnd_api.g_ret_sts_unexp_error;
1765
1766 END relieve_prod_supply_resv;
1767
1768 PROCEDURE delete_prod_supply_resv (
1769 p_matl_dtl_rec IN gme_material_details%ROWTYPE
1770 ,x_msg_count OUT NOCOPY NUMBER
1771 ,x_msg_data OUT NOCOPY VARCHAR2
1772 ,x_return_status OUT NOCOPY VARCHAR2)
1773 IS
1774 l_api_name CONSTANT VARCHAR2 (30) := 'delete_prod_supply_resv';
1775 l_rsv_count NUMBER;
1776
1777 l_matl_dtl_rec gme_material_details%ROWTYPE;
1778 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1779 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1780 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1781 -- ======================================================================
1782 query_reservation_error EXCEPTION;
1783 update_reservation_err EXCEPTION;
1784 delete_reservation_err EXCEPTION;
1785 matl_fetch_error EXCEPTION;
1786 notify_CSR_err EXCEPTION;
1787 BEGIN
1788 IF g_debug <= gme_debug.g_log_procedure THEN
1789 gme_debug.put_line ('Entering api '
1790 || g_pkg_name || '.'
1791 || l_api_name
1792 || ' material_detail_id '
1793 || p_matl_dtl_rec.material_detail_id);
1794 END IF;
1795
1796 x_return_status := fnd_api.g_ret_sts_success;
1797
1798 -- The line may not have been fully populated so retrieve it if necessary
1799 -- ======================================================================
1800 IF p_matl_dtl_rec.line_type is NULL THEN
1801 IF (NOT (gme_material_details_dbl.fetch_row (p_matl_dtl_rec
1802 ,l_matl_dtl_rec) ) ) THEN
1803 RAISE matl_fetch_error;
1804 END IF;
1805 ELSE
1806 l_matl_dtl_rec := p_matl_dtl_rec;
1807 END IF;
1808
1809 IF NVL(p_matl_dtl_rec.line_type,1) not in (1,2) THEN
1810 RETURN;
1811 END IF;
1812
1813 GME_SUPPLY_RES_PVT.query_prod_supply_reservations
1814 (p_matl_dtl_rec => l_matl_dtl_rec
1815 ,x_mtl_reservation_tbl => l_rsv_tbl
1816 ,x_mtl_reservation_tbl_count => l_rsv_count
1817 ,x_msg_count => x_msg_count
1818 ,x_msg_data => x_msg_data
1819 ,x_return_status => x_return_status);
1820
1821 IF g_debug <= gme_debug.g_log_statement THEN
1822 gme_debug.put_line
1823 ( g_pkg_name
1824 || '.'
1825 || l_api_name
1826 || 'Return status from query_prod_supply_reservations is '
1827 || x_return_status);
1828 gme_debug.put_line
1829 ( g_pkg_name
1830 || '.'
1831 || l_api_name
1832 || ' number of reservations is '
1833 || l_rsv_count);
1834 END IF;
1835
1836 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1837 IF g_debug <= gme_debug.g_log_statement THEN
1838 gme_debug.put_line
1839 ( g_pkg_name
1840 || '.'
1841 || l_api_name
1842 || 'Error is :'
1843 || x_msg_data);
1844 END IF;
1845 RAISE query_reservation_error;
1846 END IF;
1847
1848 IF NVL(l_rsv_count,0) <= 0 THEN
1849 IF g_debug <= gme_debug.g_log_statement THEN
1850 gme_debug.put_line
1851 ( g_pkg_name
1852 || '.'
1853 || l_api_name
1854 || ' No reservations against material detail line as a source of supply so RETURN now ');
1855 END IF;
1856 RETURN;
1857 END IF;
1858
1859 -- Delete the reservations against this this material detail line.
1860 -- =========================================================================
1861 FOR I IN 1..l_rsv_tbl.COUNT LOOP
1862
1863 l_rsv_rec := l_rsv_tbl(I);
1864 -- Set up notifccation ahead of deleting the reservation
1865 -- This will advise the sales representative of the deletion
1866 -- =========================================================
1867
1868 GME_SUPPLY_RES_PVT.notify_CSR
1869 ( P_Batch_id => l_rsv_rec.supply_source_header_id
1870 , P_Batch_line_id => l_rsv_rec.supply_source_line_id
1871 , P_So_line_id => l_rsv_rec.demand_source_line_id
1872 , P_batch_trans_id => NULL
1873 , P_organization_id => l_rsv_rec.organization_id
1874 , P_action_code => 'CANCEL_BATCH'
1875 , X_return_status => x_return_status
1876 , X_msg_cont => x_msg_count
1877 , X_msg_data => x_msg_data );
1878
1879 IF g_debug <= gme_debug.g_log_procedure THEN
1880 gme_debug.put_line ( g_pkg_name || '.'
1881 || l_api_name
1882 || ' after calling notify_CSR status is '
1883 || x_return_status );
1884 END IF;
1885
1886 IF (x_return_status IN
1887 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1888 RAISE notify_CSR_err;
1889 END IF;
1890
1891 IF g_debug <= gme_debug.g_log_procedure THEN
1892 gme_debug.put_line ( g_pkg_name || '.'
1893 || l_api_name
1894 || ' about to invoke delete_reservation for reservation id '
1895 || l_rsv_rec.reservation_id);
1896 END IF;
1897
1898 inv_reservation_pub.delete_reservation
1899 ( p_api_version_number => 1.0
1900 , p_init_msg_lst => fnd_api.g_true
1901 , x_return_status => x_return_status
1902 , x_msg_count => x_msg_count
1903 , x_msg_data => x_msg_data
1904 , p_rsv_rec => l_rsv_rec
1905 , p_serial_number => l_dummy_sn
1906 );
1907
1908 IF g_debug <= gme_debug.g_log_procedure THEN
1909 gme_debug.put_line ( g_pkg_name || '.'
1910 || l_api_name
1911 || ' after calling delete_reservation status is '
1912 || x_return_status );
1913 END IF;
1914
1915
1916 IF (x_return_status IN
1917 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
1918 RAISE delete_reservation_err;
1919 END IF;
1920
1921 END LOOP;
1922 IF g_debug <= gme_debug.g_log_procedure THEN
1923 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
1924 END IF;
1925
1926 EXCEPTION
1927 WHEN delete_reservation_err THEN
1928 IF (g_debug <= gme_debug.g_log_error) THEN
1929 gme_debug.put_line
1930 ( 'inv_reservation_pub.delete_reservation failure ');
1931 gme_debug.put_line ('error message is ' || x_msg_data);
1932 END IF;
1933 WHEN notify_CSR_err THEN
1934 IF (g_debug <= gme_debug.g_log_error) THEN
1935 gme_debug.put_line
1936 ( 'gme_supply_res_pvt.notify_CSR failure ');
1937 gme_debug.put_line ('error message is ' || x_msg_data);
1938 END IF;
1939 WHEN matl_fetch_error THEN
1940 x_return_status := fnd_api.g_ret_sts_error;
1941 WHEN OTHERS THEN
1942 IF g_debug <= gme_debug.g_log_unexpected THEN
1943 gme_debug.put_line ( 'When others exception in '
1944 || g_pkg_name
1945 || '.'
1946 || l_api_name
1947 || ' Error is '
1948 || SQLERRM);
1949 END IF;
1950
1951 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1952 x_return_status := fnd_api.g_ret_sts_unexp_error;
1953
1954 END delete_prod_supply_resv;
1955
1956 PROCEDURE delete_batch_prod_supply_resv (
1957 p_batch_header_rec IN gme_batch_header%ROWTYPE
1958 ,x_msg_count OUT NOCOPY NUMBER
1959 ,x_msg_data OUT NOCOPY VARCHAR2
1960 ,x_return_status OUT NOCOPY VARCHAR2)
1961 IS
1962 l_api_name CONSTANT VARCHAR2 (30) := 'delete_prod_supply_resv';
1963 l_rsv_count NUMBER;
1964
1965 l_matl_dtl_rec gme_material_details%ROWTYPE;
1966 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1967 l_rsv_tbl inv_reservation_global.mtl_reservation_tbl_type;
1968 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
1969 -- ======================================================================
1970 query_reservation_error EXCEPTION;
1971 update_reservation_err EXCEPTION;
1972 delete_reservation_err EXCEPTION;
1973 matl_fetch_error EXCEPTION;
1974 notify_CSR_err EXCEPTION;
1975 BEGIN
1976 IF g_debug <= gme_debug.g_log_procedure THEN
1977 gme_debug.put_line ('Entering api '
1978 || g_pkg_name || '.'
1979 || l_api_name
1980 || ' batch_id => '
1981 || p_batch_header_rec.batch_id);
1982 END IF;
1983
1984 x_return_status := fnd_api.g_ret_sts_success;
1985
1986 -- Query all the reservations agains this batch as a source of supply
1987 l_matl_dtl_rec.batch_id := p_batch_header_rec.batch_id;
1988 GME_SUPPLY_RES_PVT.query_prod_supply_reservations
1989 (p_matl_dtl_rec => l_matl_dtl_rec
1990 ,x_mtl_reservation_tbl => l_rsv_tbl
1991 ,x_mtl_reservation_tbl_count => l_rsv_count
1992 ,x_msg_count => x_msg_count
1993 ,x_msg_data => x_msg_data
1994 ,x_return_status => x_return_status);
1995
1996 IF g_debug <= gme_debug.g_log_statement THEN
1997 gme_debug.put_line
1998 ( g_pkg_name
1999 || '.'
2000 || l_api_name
2001 || 'Return status from query_prod_supply_reservations is '
2002 || x_return_status);
2003 gme_debug.put_line
2004 ( g_pkg_name
2005 || '.'
2006 || l_api_name
2007 || ' number of reservations is '
2008 || l_rsv_count);
2009 END IF;
2010
2011 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2012 IF g_debug <= gme_debug.g_log_statement THEN
2013 gme_debug.put_line
2014 ( g_pkg_name
2015 || '.'
2016 || l_api_name
2017 || 'Error is :'
2018 || x_msg_data);
2019 END IF;
2020 RAISE query_reservation_error;
2021 END IF;
2022
2023 IF NVL(l_rsv_count,0) <= 0 THEN
2024 IF g_debug <= gme_debug.g_log_statement THEN
2025 gme_debug.put_line
2026 ( g_pkg_name
2027 || '.'
2028 || l_api_name
2029 || ' No reservations against batch as a source of supply so RETURN now ');
2030 END IF;
2031 RETURN;
2032 END IF;
2033
2034 -- Set up notifications ahead of deleting the reservation
2035 -- This will advise the sales representative of the deletion
2036 -- =========================================================
2037
2038 GME_SUPPLY_RES_PVT.notify_CSR
2039 ( P_Batch_id => p_batch_header_rec.batch_id
2040 , P_Batch_line_id => NULL
2041 , P_So_line_id => NULL
2042 , P_batch_trans_id => NULL
2043 , P_organization_id => l_rsv_rec.organization_id
2044 , P_action_code => 'CANCEL_BATCH'
2045 , X_return_status => x_return_status
2046 , X_msg_cont => x_msg_count
2047 , X_msg_data => x_msg_data );
2048
2049 IF g_debug <= gme_debug.g_log_procedure THEN
2050 gme_debug.put_line ( g_pkg_name || '.'
2051 || l_api_name
2052 || ' after calling notify_CSR status is '
2053 || x_return_status );
2054 END IF;
2055
2056 -- Delete the reservations against this this batch
2057 -- ===============================================
2058 FOR I IN 1..l_rsv_tbl.COUNT LOOP
2059
2060 l_rsv_rec := l_rsv_tbl(I);
2061 -- Set up notifications ahead of deleting the reservation
2062 -- This will advise the sales representative of the deletion
2063 -- =========================================================
2064
2065 IF (x_return_status IN
2066 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
2067 RAISE notify_CSR_err;
2068 END IF;
2069
2070 IF g_debug <= gme_debug.g_log_procedure THEN
2071 gme_debug.put_line ( g_pkg_name || '.'
2072 || l_api_name
2073 || ' about to invoke delete_reservation for reservation id '
2074 || l_rsv_rec.reservation_id);
2075 END IF;
2076
2077 inv_reservation_pub.delete_reservation
2078 ( p_api_version_number => 1.0
2079 , p_init_msg_lst => fnd_api.g_true
2080 , x_return_status => x_return_status
2081 , x_msg_count => x_msg_count
2082 , x_msg_data => x_msg_data
2083 , p_rsv_rec => l_rsv_rec
2084 , p_serial_number => l_dummy_sn
2085 );
2086
2087 IF g_debug <= gme_debug.g_log_procedure THEN
2088 gme_debug.put_line ( g_pkg_name || '.'
2089 || l_api_name
2090 || ' after calling delete_reservation status is '
2091 || x_return_status );
2092 END IF;
2093
2094
2095 IF (x_return_status IN
2096 (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) ) THEN
2097 RAISE delete_reservation_err;
2098 END IF;
2099
2100 END LOOP;
2101 IF g_debug <= gme_debug.g_log_procedure THEN
2102 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
2103 END IF;
2104
2105 EXCEPTION
2106 WHEN delete_reservation_err THEN
2107 IF (g_debug <= gme_debug.g_log_error) THEN
2108 gme_debug.put_line
2109 ( 'inv_reservation_pub.delete_reservation failure ');
2110 gme_debug.put_line ('error message is ' || x_msg_data);
2111 END IF;
2112 WHEN notify_CSR_err THEN
2113 IF (g_debug <= gme_debug.g_log_error) THEN
2114 gme_debug.put_line
2115 ( 'gme_supply_res_pvt.notify_CSR failure ');
2116 gme_debug.put_line ('error message is ' || x_msg_data);
2117 END IF;
2118 WHEN matl_fetch_error THEN
2119 x_return_status := fnd_api.g_ret_sts_error;
2120 WHEN OTHERS THEN
2121 IF g_debug <= gme_debug.g_log_unexpected THEN
2122 gme_debug.put_line ( 'When others exception in '
2123 || g_pkg_name
2124 || '.'
2125 || l_api_name
2126 || ' Error is '
2127 || SQLERRM);
2128 END IF;
2129
2130 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2131 x_return_status := fnd_api.g_ret_sts_unexp_error;
2132
2133 END delete_batch_prod_supply_resv;
2134
2135 END GME_SUPPLY_RES_PVT;