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