[Home] [Help]
PACKAGE BODY: APPS.GML_BATCH_OM_RES_PVT
Source
1 PACKAGE body GML_BATCH_OM_RES_PVT AS
2 /* $Header: GMLORESB.pls 115.33 2004/06/11 20:03:49 nchekuri noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMLOUTLB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | reservation. |
14 | |
15 | |
16 | HISTORY |
17 | Aug-18-2003 Liping Gao Created |
18 +=========================================================================+
19 API Name : GML_BATCH_OM_RES_PVT
20 Type : Private
21 Function : This package contains Private Utilities procedures used to
22 OPM reservation for a batch.
23 Pre-reqs : N/A
24 Parameters: Per function
25
26 Current Vers : 1.0
27
28 */
29
30 G_PKG_NAME CONSTANT VARCHAR2(30):='GML_BATCH_OM_RES_PVT';
31
32 PROCEDURE build_trans_rec
33 (
34 p_trans_row IN ic_tran_pnd%rowtype
35 , x_trans_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
36 );
37 PROCEDURE build_res_rec
38 (
39 p_res_row IN gml_batch_so_reservations%rowtype
40 , x_res_rec OUT NOCOPY GML_BATCH_OM_UTIL.gme_om_reservation_rec
41 ) ;
42 PROCEDURE PRINT_DEBUG
43 (
44 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
45 ,p_routine IN VARCHAR2
46 ) ;
47
48 PROCEDURE create_reservation_from_FPO
49 (
50 P_FPO_batch_id IN NUMBER
51 , P_New_batch_id IN NUMBER
52 , X_return_status OUT NOCOPY VARCHAR2
53 , X_msg_cont OUT NOCOPY NUMBER
54 , X_msg_data OUT NOCOPY VARCHAR2
55 ) IS
56
57 l_so_line_rec GML_BATCH_OM_UTIL.so_line_rec;
58 l_batch_line_rec GML_BATCH_OM_UTIL.batch_line_rec;
59 l_rule_rec GML_BATCH_OM_UTIL.gme_om_rule_rec;
60 l_reservation_rec GML_BATCH_OM_UTIL.gme_om_reservation_rec;
61 l_batch_line_id NUMBER;
62 l_fpo_batch_line_id NUMBER;
63 l_whse_code VARCHAR2(5);
64 l_planned_qty NUMBER;
65 l_planned_qty2 NUMBER;
66 l_res_count NUMBER;
67 l_avg_qty NUMBER;
68 l_avg_qty2 NUMBER;
69 l_item_id NUMBER;
70 l_reserved_qty NUMBER;
71 l_reserved_qty2 NUMBER;
72 l_remaining_qty NUMBER;
73 l_plan_cmplt_date date;
74
75 Cursor check_whse (p_batch_line_id IN NUMBER) IS
76 Select distinct whse_code
77 From gml_batch_so_reservations
78 Where batch_line_id = p_batch_line_id;
79
80 Cursor get_batch_line (p_batch_id IN NUMBER) IS
81 Select material_detail_id
82 , item_id
83 From gme_material_details
84 where batch_id = p_batch_id
85 and line_type <> -1 -- not ingredient
86 ;
87
88 Cursor get_res_for_whse (p_whse_code IN VARCHAR2
89 , p_batch_line_id IN NUMBER) IS
90 Select *
91 From gml_batch_so_reservations
92 Where batch_line_id = p_batch_line_id
93 and whse_code = p_whse_code
94 and delete_mark = 0
95 and reserved_qty <> 0
96 Order by scheduled_ship_date
97 , shipment_priority
98 ;
99
100 Cursor get_planned_qty (p_batch_line_id IN NUMBER
101 , p_whse_code IN VARCHAR2) is
102 Select abs(sum(trans_qty)), abs(sum(trans_qty2))
103 From ic_tran_pnd
104 Where line_id = p_batch_line_id
105 and whse_code = p_whse_code
106 and doc_type = 'PROD'
107 and delete_mark = 0
108 and completed_ind = 0
109 ;
110 Cursor get_res_count(p_batch_line_id IN NUMBER
111 , p_whse_code IN VARCHAR2) Is
112 Select count(1)
113 From gml_batch_so_reservations
114 Where batch_line_id = p_batch_line_id
115 and whse_code = p_whse_code
116 and delete_mark = 0
117 and reserved_qty <> 0
118 ;
119
120 Cursor get_new_batch_line (p_batch_id IN NUMBER
121 , p_item_id IN NUMBER) is
122 Select material_detail_id
123 From gme_material_details
124 Where batch_id = p_batch_id
125 and item_id = p_item_id
126 and line_type <> -1
127 ;
128 Cursor get_new_batch_cmpt_date (p_batch_id IN NUMBER) is
129 Select plan_cmplt_date
130 From gme_batch_header
131 where batch_id = p_batch_id;
132
133 BEGIN
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135 GMI_reservation_Util.PrintLn(' create_reservation_from_FPO ');
136 /* loop through all the product lines in the batch */
137 For batch_line in get_batch_line(p_FPO_batch_id) Loop
138 l_fpo_batch_line_id := batch_line.material_detail_id ;
139 l_item_id := batch_line.item_id ;
140 GMI_reservation_Util.PrintLn(' FPO batch_line_id '||l_fpo_batch_line_id);
141 /* check reservation exist or not */
142 IF NOT GML_BATCH_OM_UTIL.check_reservation
143 (
144 P_Batch_line_id => l_fpo_batch_line_id
145 , X_return_status => x_return_status
146 , X_msg_cont => x_msg_cont
147 , X_msg_data => x_msg_data
148 )
149 THEN
150 goto next_batch_line;
151 END IF;
152
153 /* get the new batch_line_id for the created batch */
154 Open get_new_batch_line(p_new_batch_id, l_item_id);
155 Fetch get_new_batch_line Into l_batch_line_id;
156 Close get_new_batch_line;
157
158 GMI_reservation_Util.PrintLn(' NEW batch_line_id '||l_batch_line_id);
159 /* loop to see if different whse may have exist */
160 For each_whse in check_whse(l_fpo_batch_line_id) Loop
161 l_whse_code := each_whse.whse_code;
162 l_so_line_rec.whse_code := each_whse.whse_code;
163 l_batch_line_rec.batch_line_id := l_fpo_batch_line_id;
164 GMI_reservation_Util.PrintLn(' reservation whse '||l_whse_code);
165
166 GML_BATCH_OM_UTIL.get_rule
167 (
168 P_so_line_rec => l_so_line_rec
169 , P_batch_line_rec => l_batch_line_rec
170 , X_gme_om_rule_rec => l_rule_rec
171 , X_return_status => x_return_status
172 , X_msg_cont => x_msg_cont
173 , X_msg_data => x_msg_data
174 );
175 /* get the qty for the newly created batch */
176 Open get_planned_qty( l_batch_line_id, l_whse_code);
177 Fetch get_planned_qty
178 Into l_planned_qty
179 , l_planned_qty2
180 ;
181 Close get_planned_qty;
182 GMI_reservation_Util.PrintLn(' NEW batch Planned qty '||l_planned_qty);
183
184 Open get_new_batch_cmpt_date (p_new_batch_id) ;
185 Fetch get_new_batch_cmpt_date Into l_plan_cmplt_date;
186 Close get_new_batch_cmpt_date;
187 /* check the rule */
188 IF l_rule_rec.allocation_priority = 2 THEN -- evenly distributed
189 Open get_res_count( l_fpo_batch_line_id, l_whse_code);
190 Fetch get_res_count Into l_res_count;
191 Close get_res_count;
192 IF l_res_count <> 0 THEN
193 l_avg_qty := l_planned_qty / l_res_count;
194 l_avg_qty2 := l_planned_qty2 / l_res_count;
195 END IF;
196 END IF;
197 /* process the reservations made in this whse */
198 l_remaining_qty := l_planned_qty;
199 for each_rec in get_res_for_whse(l_whse_code
200 , l_fpo_batch_line_id)
201 Loop
202 /* check the batch planned cplt date with the scheduled_ship_date
203 * if the date is out, skip this record
204 */
205 GMI_reservation_Util.PrintLn(' build res record for the new batch');
206 IF each_rec.scheduled_ship_date > l_plan_cmplt_date THEN
207 Goto next_res_line;
208 END IF;
209 EXIT WHEN l_remaining_qty <= 0 ;
210 /* build reservation rec for the new batch line */
211 IF each_rec.reserved_qty < l_planned_qty THEN
212 l_reserved_qty := each_rec.reserved_qty;
213 l_reserved_qty2 := each_rec.reserved_qty2;
214 ELSE
215 l_reserved_qty := l_planned_qty;
216 l_reserved_qty2 := l_planned_qty2;
217 END IF;
218
219 build_res_rec( each_rec, l_reservation_rec);
220
221 l_reservation_rec.batch_id := p_NEW_batch_id;
222 l_reservation_rec.rule_id := l_rule_rec.rule_id;
223 l_reservation_rec.batch_line_id := l_batch_line_id;
224 l_reservation_rec.batch_type := 0;
225 IF l_rule_rec.allocation_priority = 2 THEN -- evenly distributed
226 l_reserved_qty := l_avg_qty ;
227 l_reserved_qty2 := l_avg_qty2 ;
228 END IF;
229 l_reservation_rec.reserved_qty := l_reserved_qty;
230 l_reservation_rec.reserved_qty2 := l_reserved_qty2;
231 GMI_reservation_Util.PrintLn(' new reserved qty '||l_reserved_qty);
232
233 GML_BATCH_OM_UTIL.insert_reservation
234 (
235 P_Gme_om_reservation_rec => l_reservation_rec
236 , X_return_status => x_return_status
237 , X_msg_cont => x_msg_cont
238 , X_msg_data => x_msg_data
239 );
240 l_remaining_qty := l_remaining_qty - l_reserved_qty;
241 /* update the fpo reservation records */
242 Update gml_batch_so_reservations
243 Set reserved_qty = reserved_qty - l_reserved_qty
244 , reserved_qty2 = reserved_qty2 - l_reserved_qty2
245 Where batch_res_id = each_rec.batch_res_id;
246 <<next_res_line>>
247 null;
248 END Loop;
249 END LOOP;
250 <<next_batch_line>>
251 null;
252 END loop;
253 EXCEPTION
254 WHEN FND_API.G_EXC_ERROR THEN
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 /* Get message count and data*/
257 FND_MSG_PUB.count_and_get
258 ( p_count => x_msg_cont
259 , p_data => x_msg_data
260 );
261 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Expected');
262 WHEN OTHERS THEN
263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
264
265 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
266 , 'convert_FPO'
267 );
268 /* Get message count and data*/
269 FND_MSG_PUB.count_and_get
270 ( p_count => x_msg_cont
271 , p_data => x_msg_data
272 );
273 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Others');
274
275 END create_reservation_from_FPO;
276
277 PROCEDURE create_allocations
278 (
279 P_batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
280 , P_gme_om_rule_rec IN GML_BATCH_OM_UTIL.gme_om_rule_rec
281 , P_Gme_trans_row IN ic_tran_pnd%rowtype
282 , X_return_status OUT NOCOPY VARCHAR2
283 , X_msg_cont OUT NOCOPY NUMBER
284 , X_msg_data OUT NOCOPY VARCHAR2
285 ) IS
286
287 l_so_line_rec GML_BATCH_OM_UTIL.so_line_rec;
288 l_rule_rec GML_BATCH_OM_UTIL.gme_om_rule_rec;
289 l_history_rec GML_BATCH_OM_UTIL.alloc_history_rec;
290 l_gme_trans_row ic_tran_pnd%rowtype;
291 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
292 l_dft_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
293 l_tran_row IC_TRAN_PND%ROWTYPE;
294 l_res_count NUMBER;
295 l_avg_qty NUMBER;
296 l_avg_qty2 NUMBER;
297 l_remaining_alloc_qty NUMBER;
298 l_remaining_alloc_qty2 NUMBER;
299 l_go_ahead NUMBER;
300 l_orderable NUMBER;
301 l_status_ctl NUMBER;
302 l_dft_trans_id NUMBER;
303 l_alloc_done NUMBER;
304 l_over_alloc NUMBER;
305 l_total_alloc NUMBER;
306 l_total_alloc2 NUMBER;
307 l_total_req NUMBER;
308 l_total_req2 NUMBER;
309 l_lot_status VARCHAR2(10);
310 l_prod_whse VARCHAR2(10);
311
312 Cursor find_reservations (p_batch_line_id IN NUMBER
313 , p_whse_code IN VARCHAR ) is
314 Select res.scheduled_ship_date
315 , res.shipment_priority
316 , res.batch_res_id
317 , res.reserved_qty
318 , res.reserved_qty2
319 , res.batch_line_id
320 , res.so_line_id
321 , res.delivery_detail_id
322 , res.batch_id
323 From gml_batch_so_reservations res
324 , wsh_delivery_details wdd
325 Where res.batch_line_id = p_batch_line_id
326 and res.delete_mark = 0
327 and res.reserved_qty <> 0
328 and res.whse_code = p_whse_code
329 and res.delivery_detail_id = wdd.delivery_detail_id
330 and wdd.released_status in ('B', 'R', 'S') -- dont bother looking at the ones staged
331 Union
332 Select res.scheduled_ship_date
333 , res.shipment_priority
334 , res.batch_res_id
335 , res.reserved_qty
336 , res.reserved_qty2
337 , res.batch_line_id
338 , res.so_line_id
339 , res.delivery_detail_id
340 , res.batch_id
341 From gml_batch_so_reservations res
342 , oe_order_lines_all ol
343 Where res.batch_line_id = p_batch_line_id
344 and res.delete_mark = 0
345 and res.reserved_qty <> 0
346 and res.whse_code = p_whse_code
347 and res.so_line_id = ol.line_id
348 and ol.booked_flag = 'N'
349 Order by 1
350 , 2
351 ;
352 Cursor check_status_ctl(p_item_id IN NUMBER) IS
353 Select status_ctl
354 From ic_item_mst
355 Where item_id = p_item_id;
356
357 Cursor check_alloc_qty (p_so_line_id IN NUMBER) is
358 Select abs(sum(trans_qty)), abs(sum(trans_qty2))
359 From ic_Tran_pnd
360 Where line_id = p_so_line_id
361 and doc_type = 'OMSO'
362 and delete_mark = 0
363 and (lot_id <> 0 or location <> GMI_Reservation_Util.G_DEFAULT_LOCT)
364 and completed_ind = 0
365 ;
366 BEGIN
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368 GMI_RESERVATION_UTIL.println('Create Allocations ');
369 l_rule_rec := p_gme_om_rule_rec;
370 l_gme_trans_row := p_gme_trans_row;
371 l_prod_whse := l_gme_trans_row.whse_code;
372
373 GMI_RESERVATION_UTIL.println(' Production whse code is '|| l_prod_whse);
374
375 IF l_rule_rec.rule_id is null THEN
376 l_so_line_rec.whse_code := p_gme_trans_row.whse_code;
377 GML_BATCH_OM_UTIL.get_rule
378 (
379 P_so_line_rec => l_so_line_rec
380 , P_batch_line_rec => p_batch_line_rec
381 , X_gme_om_rule_rec => l_rule_rec
382 , X_return_status => x_return_status
383 , X_msg_cont => x_msg_cont
384 , X_msg_data => x_msg_data
385 );
386 END IF;
387 IF l_rule_rec.allocation_priority = 2 THEN /* distribute evenly*/
388 GMI_RESERVATION_UTIL.println('Create Allocations: distribute evenly');
389 Select count(1)
390 Into l_res_count
391 From gml_batch_so_reservations
392 Where batch_line_id = p_batch_line_rec.batch_line_id
393 and delete_mark = 0
394 and reserved_qty <> 0
395 and whse_code = l_gme_trans_row.whse_code -- beta testing
396 ;
397 IF l_res_count <> 0 THEN
398 l_avg_qty := abs(l_gme_trans_row.trans_qty) / l_res_count;
399 l_avg_qty2 := abs(l_gme_trans_row.trans_qty2) / l_res_count;
400 END IF;
401 END IF;
402 /* query all the reservation rec for this batch line*/
403 l_remaining_alloc_qty := abs(l_gme_trans_row.trans_qty);
404 l_remaining_alloc_qty2 := abs(l_gme_trans_row.trans_qty2);
405 For res_rec in find_reservations(p_batch_line_rec.batch_line_id, l_prod_whse) Loop
406 EXIT WHEN l_remaining_alloc_qty <= 0 ;
407
408 /* build history rec */
409 l_history_rec.Batch_id := res_rec.batch_id;
410 l_history_rec.Batch_line_id := res_rec.batch_line_id;
411 l_history_rec.So_line_id := res_rec.so_line_id;
412 l_history_rec.Batch_res_id := res_rec.batch_res_id;
413 l_history_rec.Batch_trans_id := l_gme_trans_row.trans_id;
414 l_history_rec.trans_id := null;
415 l_history_rec.Whse_code := l_gme_trans_row.whse_code;
416 l_history_rec.Reserved_qty := res_rec.reserved_qty;
417 l_history_rec.Reserved_qty2 := res_rec.reserved_qty2;
418 l_history_rec.Trans_um := l_gme_trans_row.trans_um;
419 l_history_rec.Trans_um2 := l_gme_trans_row.trans_um2;
420 l_history_rec.rule_id := l_rule_rec.rule_id;
421
422 l_go_ahead := 1;
423 /* check default lot */
424 GMI_RESERVATION_UTIL.find_default_lot
425 ( x_return_status => x_return_status,
426 x_msg_count => x_msg_cont,
427 x_msg_data => x_msg_data,
428 x_reservation_id => l_dft_trans_id,
429 p_line_id => res_rec.so_line_id
430 );
431 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
432 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
433 FND_MESSAGE.SET_NAME('GML', 'GML_NO_DFLT_TRAN');
434 l_history_rec.failure_reason := FND_MESSAGE.GET;
435 l_go_ahead := 0;
436 END IF;
437 IF nvl(l_dft_trans_id,0) = 0 THEN
438 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
439 FND_MESSAGE.SET_NAME('GML', 'GML_NO_DFLT_TRAN');
440 l_history_rec.failure_reason := FND_MESSAGE.GET;
441 l_go_ahead := 0;
442 END IF;
443 l_dft_tran_rec.trans_id := l_dft_trans_id ;
444 IF NOT GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_dft_tran_rec, l_dft_tran_rec ) THEN
445 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
446 FND_MESSAGE.SET_NAME('GML', 'GML_NO_DFLT_TRAN');
447 l_history_rec.failure_reason := FND_MESSAGE.GET;
448 l_go_ahead := 0;
449 END IF;
450 /* build the trans_rec */
451 IF l_go_ahead = 1 THEN
452 GMI_RESERVATION_UTIL.println('Create Allocations: building trans rec ');
453 l_tran_rec := l_dft_tran_rec;
454 GMI_RESERVATION_UTIL.println('Create Allocations: tran.line_id'||l_tran_rec.line_id);
455 PRINT_DEBUG (l_tran_rec,'before build Transaction');
456 build_trans_rec
457 (
458 p_trans_row => p_gme_trans_row
459 , x_trans_rec => l_tran_rec
460 );
461 IF l_rule_rec.allocation_priority = 2 THEN /* distribute evenly*/
462 l_tran_rec.trans_qty := -1 * l_avg_qty;
463 l_tran_rec.trans_qty2 := -1 * l_avg_qty2;
464 ELSE
465 IF res_rec.reserved_qty <= abs(l_remaining_alloc_qty) THEN
466 l_tran_rec.trans_qty := -1 * res_rec.reserved_qty;
467 l_tran_rec.trans_qty2 := -1 * res_rec.reserved_qty2;
468 Else
469 l_tran_rec.trans_qty := -1 * l_remaining_alloc_qty;
470 l_tran_rec.trans_qty2 := -1 * l_remaining_alloc_qty2;
471 END IF;
472 l_over_alloc := 0;
473 /* check the over_pick_enabled and the total allocated qty to see if over allocate
474 * then
475 * 1) adjust the trans_qty and trans_qty2
476 * 2) set l_over_alloc = 1
477 */
478 IF nvl(fnd_profile.value('WSH_OVERPICK_ENABLED'),'N') = 'N' THEN
479 Open check_alloc_qty (res_rec.so_line_id);
480 Fetch check_alloc_qty Into l_total_alloc, l_total_alloc2;
481 Close check_alloc_qty;
482 l_total_req := l_total_alloc + abs(l_dft_tran_rec.trans_qty) ;
483 l_total_req2 := l_total_alloc2 + abs(l_dft_tran_rec.trans_qty2) ;
484 IF abs(l_tran_rec.trans_qty) > (l_total_req - l_total_alloc) THEN
485 GMI_RESERVATION_UTIL.println('Create alloc: over pick is not allowed, adjust qty');
486 /* allocate what is left in the dflt */
487 l_tran_rec.trans_qty := -1 * (l_total_req - l_total_alloc) ;
488 l_tran_rec.trans_qty2 := -1 * (l_total_req2 - l_total_alloc2) ;
489 l_over_alloc := 1;
490 GMI_RESERVATION_UTIL.println('Create alloc: trans qty '||l_tran_rec.trans_qty);
491 GMI_RESERVATION_UTIL.println('Create alloc: trans qty2 '||l_tran_rec.trans_qty2);
492 END IF;
493 IF l_tran_rec.trans_qty >= 0 THEN /* already fulfilled */
494 FND_MESSAGE.SET_NAME('GML', 'GML_OVER_ALLOC_NOT_ALLOWED');
495 l_history_rec.failure_reason := FND_MESSAGE.GET;
496 l_go_ahead := 0;
497 END IF;
498 END IF;
499 END IF;
500 PRINT_DEBUG (l_tran_rec,'after build Transaction');
501 END IF;
502
503 /* check lot status if status controled */
504 Open check_status_ctl(l_gme_trans_row.item_id);
505 Fetch check_status_ctl Into l_status_ctl;
506 Close check_status_ctl;
507 IF l_status_ctl <> 0 THEN
508 IF l_gme_trans_row.lot_status is null THEN
509 FND_MESSAGE.SET_NAME('GML', 'GML_PROD_NULL_STS');
510 --FND_MESSAGE.SET_TOKEN('LOT_STS', l_gme_trans_row.lot_status);
511 l_history_rec.failure_reason := FND_MESSAGE.GET;
512 GMI_RESERVATION_UTIL.println('Create alloc: Lot status ctl with gme trans status is null');
513 l_go_ahead := 0;
514 ELSE
515 l_lot_status := l_gme_trans_row.lot_status ;
516 IF nvl(p_batch_line_rec.release_type,0) = 20 THEN
517 GMI_RESERVATION_UTIL.println('Create alloc: Regenerate allocations');
518 -- It is a regenerate (set it to value of 20 in regenerate, internal use )
519 /* check the lot_status in ic_loct_inv, where the status may have been changed from original setting*/
520 Select lot_status
521 Into l_lot_status
522 From ic_loct_inv
523 Where lot_id = l_gme_trans_row.lot_id
524 AND location = l_gme_trans_row.location
525 AND whse_code = l_gme_trans_row.whse_code
526 AND item_id = l_gme_trans_row.item_id
527 And delete_mark = 0
528 ;
529 END IF;
530 GMI_RESERVATION_UTIL.println('Create alloc: Lot status '|| l_lot_status);
531 Select order_proc_ind
532 Into l_orderable
533 From ic_lots_sts
534 Where lot_status = l_lot_status;
535 IF l_orderable = 0 THEN
536 FND_MESSAGE.SET_NAME('GML', 'GML_LOT_STS_NOT_ORD');
537 FND_MESSAGE.SET_TOKEN('LOT_STS', l_gme_trans_row.lot_status);
538 l_history_rec.failure_reason := FND_MESSAGE.GET;
539 l_go_ahead := 0;
540 GMI_RESERVATION_UTIL.println('Create alloc: Lot status is NOT orderable, OMSO is not created');
541 GML_GME_API_PVT.g_not_to_delete := 1;
542 GMI_RESERVATION_UTIL.println('Create alloc: global g_not_to_delete '||GML_GME_API_PVT.g_not_to_delete);
543
544 GML_GME_API_PVT.g_not_to_notify := 1;
545
546 ELSE
547 /* set the lot_status for trans */
548 l_tran_rec.lot_status := l_lot_status;
549 l_go_ahead := 1;
550 END IF;
551 END IF;
552 END IF;
553
554 /* assign line_detail_id for the trans */
555 l_tran_rec.line_detail_id := res_rec.delivery_detail_id;
556 l_alloc_done := 0;
557
558 IF l_go_ahead = 1 THEN
559 GMI_TRANS_ENGINE_PUB.create_pending_transaction
560 (p_api_version => 1.0,
561 p_init_msg_list => FND_API.G_TRUE,
562 p_commit => FND_API.G_FALSE,
563 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
564 p_tran_rec => l_tran_rec,
565 x_tran_row => l_tran_row,
566 x_return_status => x_return_status,
567 x_msg_count => x_msg_cont,
568 x_msg_data => x_msg_data
569 );
570 IF x_return_status = fnd_api.g_ret_sts_success Then
571 GMI_reservation_Util.PrintLn('create_allocation, Success');
572 l_history_rec.trans_id := l_tran_row.trans_id;
573 l_alloc_done := 1;
574 ELSE
575 GMI_reservation_Util.PrintLn('create_allocation, alloc creation error');
576 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
577 FND_MESSAGE.Set_Token('WHERE', 'Check rules');
578 FND_MSG_PUB.ADD;
579 l_history_rec.failure_reason := 'all stack of msgs';
580 END IF;
581 GMI_RESERVATION_UTIL.balance_default_lot
582 ( p_ic_default_rec => l_dft_tran_rec
583 , p_opm_item_id => l_dft_tran_rec.item_id
584 , x_return_status => x_return_status
585 , x_msg_count => x_msg_cont
586 , x_msg_data => x_msg_data
587 );
588 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
589 THEN
590 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
591 FND_MESSAGE.SET_NAME('GML', 'GML_CANNOT_BAL_DFLT_TRAN');
592 FND_MESSAGE.SET_TOKEN('LOT_STS', l_gme_trans_row.lot_status);
593 l_history_rec.failure_reason := FND_MESSAGE.GET;
594 END IF;
595 END IF;
596 IF nvl(p_batch_line_rec.release_type,0) = 20 THEN
597 /* IF regenerate, set the current history to delete */
598 Update gml_batch_so_alloc_history
599 set delete_mark = 1
600 Where batch_res_id = res_rec.batch_res_id
601 And batch_trans_id = l_gme_trans_row.trans_id
602 And failure_reason is not null;
603 END IF;
604 /* insert history record */
605 GML_BATCH_OM_UTIL.insert_alloc_history
606 (
607 P_alloc_history_rec => l_history_rec
608 , X_return_status => x_return_status
609 , X_msg_cont => x_msg_cont
610 , X_msg_data => x_msg_data
611 );
612
613 l_remaining_alloc_qty := l_remaining_alloc_qty - abs(l_tran_rec.trans_qty);
614 l_remaining_alloc_qty2 := l_remaining_alloc_qty2 - abs(l_tran_rec.trans_qty2);
615 IF l_alloc_done = 1 THEN
616 /* update the reservation record */
617 Update gml_batch_so_reservations
618 Set allocated_ind = 1
619 , reserved_qty = reserved_qty - abs(l_tran_row.trans_qty)
620 , reserved_qty2 = reserved_qty2 - abs(l_tran_row.trans_qty2)
621 Where batch_res_id = res_rec.batch_res_id;
622 /* delete the reservation record if over allocated */
623 IF l_over_alloc = 1 THEN
624 Update gml_batch_so_reservations
625 Set delete_mark = 1
626 Where batch_res_id = res_rec.batch_res_id;
627 END IF;
628 END IF;
629 END loop;
630
631 EXCEPTION
632 WHEN FND_API.G_EXC_ERROR THEN
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 /* Get message count and data*/
635 FND_MSG_PUB.count_and_get
636 ( p_count => x_msg_cont
637 , p_data => x_msg_data
638 );
639 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Expected');
640 WHEN OTHERS THEN
641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
642
643 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
644 , 'create_allocations'
645 );
646 /* Get message count and data*/
647 FND_MSG_PUB.count_and_get
648 ( p_count => x_msg_cont
649 , p_data => x_msg_data
650 );
651 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT u EXCEPTION: Others');
652
653 END create_allocations;
654
655 PROCEDURE cancel_alloc_for_trans
656 (
657 P_Batch_trans_id IN NUMBER default null
658 , X_return_status OUT NOCOPY VARCHAR2
659 , X_msg_cont OUT NOCOPY NUMBER
660 , X_msg_data OUT NOCOPY VARCHAR2
661 ) IS
662
663 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
664 l_dft_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
665 l_tran_row IC_TRAN_PND%ROWTYPE;
666 l_trans_id NUMBER;
667
668 Cursor find_nonstgd_alloc_for_trans (P_batch_trans_id IN NUMBER) IS
669 Select ic.trans_id
670 From ic_tran_pnd ic
671 , gml_batch_so_alloc_history his
672 Where his.batch_trans_id = p_batch_trans_id
673 and his.trans_id = ic.trans_id
674 and ic.line_id = his.line_id
675 and ic.staged_ind = 0
676 and ic.delete_mark = 0
677 and ic.doc_type = 'OMSO'
678 ;
679
680 BEGIN
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 /* this would remove all the un-staged trans */
683 GMI_RESERVATION_UTIL.PrintLn('Cancel_alloc_for_Trans ');
684 GMI_RESERVATION_UTIL.PrintLn(' Batch trans id '|| p_batch_trans_id);
685 IF p_batch_trans_id is not null THEN
686 /* find out the unstaged trans converted from this batch trans line */
687 for alloc_rec in find_nonstgd_alloc_for_trans(p_batch_trans_id) Loop
688 /* call gmi api to delete this trans */
689 l_trans_id := alloc_rec.trans_id;
690 l_tran_rec.trans_id := l_trans_id;
691 GMI_RESERVATION_UTIL.PrintLn(' Deleting trans_id '||l_trans_id);
692 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_tran_rec, l_tran_rec ) THEN
693 GMI_TRANS_ENGINE_PUB.delete_pending_transaction
694 (p_api_version => 1.0,
695 p_init_msg_list => FND_API.G_TRUE,
696 p_commit => FND_API.G_FALSE,
697 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
698 p_tran_rec => l_tran_rec,
699 x_tran_row => l_tran_row,
700 x_return_status => x_return_status,
701 x_msg_count => x_msg_cont,
702 x_msg_data => x_msg_data
703 );
704 IF x_return_status <> fnd_api.g_ret_sts_success Then
705 GMI_reservation_Util.PrintLn('Delete OMSO trans for Batch trans');
706 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
707 FND_MESSAGE.Set_Token('WHERE', 'Check rules');
708 FND_MSG_PUB.ADD;
709 END IF;
710 END IF;
711 l_dft_tran_rec.line_id := l_tran_rec.line_id;
712 l_dft_tran_rec.trans_id := null;
713 GMI_RESERVATION_UTIL.find_default_lot
714 ( x_return_status => x_return_status,
715 x_msg_count => x_msg_cont,
716 x_msg_data => x_msg_data,
717 x_reservation_id => l_dft_tran_rec.trans_id,
718 p_line_id => l_tran_rec.line_id
719 );
720
721 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_dft_tran_rec, l_dft_tran_rec ) THEN
722 GMI_RESERVATION_UTIL.balance_default_lot
723 ( p_ic_default_rec => l_dft_tran_rec
724 , p_opm_item_id => l_dft_tran_rec.item_id
725 , x_return_status => x_return_status
726 , x_msg_count => x_msg_cont
727 , x_msg_data => x_msg_data
728 );
729 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
730 THEN
731 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
732 END IF;
733 END IF;
734 END loop;
735 END IF;
736 END cancel_alloc_for_trans;
737
738 PROCEDURE cancel_alloc_for_batch
739 (
740 P_Batch_id IN NUMBER default null
741 , X_return_status OUT NOCOPY VARCHAR2
742 , X_msg_cont OUT NOCOPY NUMBER
743 , X_msg_data OUT NOCOPY VARCHAR2
744 ) IS
745
746 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
747 l_dft_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
748 l_tran_row IC_TRAN_PND%ROWTYPE;
749 l_batch_trans_id NUMBER;
750
751 Cursor find_nonstgd_alloc_for_batch (P_batch_id IN NUMBER) IS
752 Select ic.trans_id
753 From ic_tran_pnd ic
754 , gml_batch_so_alloc_history his
755 Where his.batch_id = p_batch_id
756 and his.trans_id = ic.trans_id
757 and ic.line_id = his.line_id
758 and ic.staged_ind = 0
759 and ic.completed_ind = 0
760 and ic.delete_mark = 0
761 and ic.doc_type = 'OMSO'
762 ;
763
764 BEGIN
765 x_return_status := FND_API.G_RET_STS_SUCCESS;
766 /* this would remove all the un-staged trans */
767 IF p_batch_id is not null THEN
768 /* find out the unstaged trans for this batch line */
769 for alloc_rec in find_nonstgd_alloc_for_batch(p_batch_id) Loop
770 l_tran_rec.trans_id := alloc_rec.trans_id;
771 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION(
772 p_api_version => 1.0
773 ,p_init_msg_list => FND_API.G_TRUE
774 ,p_commit => FND_API.G_FALSE
775 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
776 ,p_tran_rec => l_tran_rec
777 ,x_tran_row => l_tran_row
778 ,x_return_status => x_return_status
779 ,x_msg_count => x_msg_cont
780 ,x_msg_data => x_msg_data);
781 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d After DELETE_PENDING_TRANSACTION.');
782 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
783 THEN
784 GMI_reservation_Util.PrintLn('(opm_dbg) in PVT d ERROR: Returned by Delete_Transaction().');
785 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
786 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION');
787 FND_MESSAGE.Set_Token('WHERE', 'Delete_Reservation');
788 FND_MSG_PUB.Add;
789 RAISE FND_API.G_EXC_ERROR;
790 END IF;
791
792 GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans
793 (
794 P_Batch_trans_id => l_batch_trans_id
795 , X_return_status => X_return_status
796 , X_msg_cont => X_msg_cont
797 , X_msg_data => X_msg_data
798 );
799
800 /* call gmi api to delete this trans */
801 end loop;
802 END IF;
803 END cancel_alloc_for_batch;
804
805 PROCEDURE cancel_alloc_for_batch_line
806 (
807 P_Batch_line_id IN NUMBER default null
808 , X_return_status OUT NOCOPY VARCHAR2
809 , X_msg_cont OUT NOCOPY NUMBER
810 , X_msg_data OUT NOCOPY VARCHAR2
811 ) IS
812
813 l_batch_trans_id NUMBER;
814
815 Cursor find_nonstgd_alloc_for_batch (P_batch_line_id IN NUMBER) IS
816 Select ic.trans_id
817 From ic_tran_pnd ic
818 , gml_batch_so_alloc_history his
819 Where his.batch_line_id = p_batch_line_id
820 and his.trans_id = ic.trans_id
821 and ic.line_id = his.line_id
822 and ic.staged_ind = 0
823 and ic.delete_mark = 0
824 and ic.doc_type = 'OMSO'
825 ;
826
827 BEGIN
828 x_return_status := FND_API.G_RET_STS_SUCCESS;
829 /* this would remove all the un-staged trans */
830 IF p_batch_line_id is not null THEN
831 /* find out the unstaged trans for this batch line */
832 for alloc_rec in find_nonstgd_alloc_for_batch(p_batch_line_id) Loop
833 l_batch_trans_id := alloc_rec.trans_id;
834 GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans
835 (
836 P_Batch_trans_id => l_batch_trans_id
837 , X_return_status => X_return_status
838 , X_msg_cont => X_msg_cont
839 , X_msg_data => X_msg_data
840 );
841 /* call gmi api to delete this trans */
842 end loop;
843 END IF;
844 END cancel_alloc_for_batch_line;
845
846 PROCEDURE cancel_res_for_batch_line
847 (
848 P_Batch_line_id IN NUMBER default null
849 , P_whse_code IN VARCHAR2 default null
850 , X_return_status OUT NOCOPY VARCHAR2
851 , X_msg_cont OUT NOCOPY NUMBER
852 , X_msg_data OUT NOCOPY VARCHAR2
853 ) IS
854
855 l_batch_trans_id NUMBER;
856
857 BEGIN
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859 GMI_reservation_Util.PrintLn('cancel_res_for_batch_line: batch_line_id '||p_batch_line_id);
860 GMI_reservation_Util.PrintLn('cancel_res_for_batch_line: whse_code '||p_whse_code);
861 /* this would remove all the un-staged trans */
862 IF p_batch_line_id is not null and p_whse_code is null THEN
863 Update gml_batch_so_reservations
864 Set delete_mark = 1
865 Where batch_line_id = p_batch_line_id
866 and delete_mark = 0;
867 IF SQL%NOTFOUND THEN
868 GMI_reservation_Util.PrintLn('cancel_res_for_batch_line: no reservations');
869 END IF;
870 END IF;
871 IF p_batch_line_id is not null and p_whse_code is not null THEN
872 Update gml_batch_so_reservations
873 Set delete_mark = 1
874 Where batch_line_id = p_batch_line_id
875 and whse_code = p_whse_code
876 and delete_mark = 0;
877 IF SQL%NOTFOUND THEN
878 GMI_reservation_Util.PrintLn('cancel_res_for_batch_line: no reservations');
879 END IF;
880 END IF;
881 END cancel_res_for_batch_line;
882
883 PROCEDURE cancel_res_for_so_line
884 (
885 P_so_line_id IN NUMBER default null
886 , X_return_status OUT NOCOPY VARCHAR2
887 , X_msg_cont OUT NOCOPY NUMBER
888 , X_msg_data OUT NOCOPY VARCHAR2
889 ) IS
890
891 l_batch_trans_id NUMBER;
892
893 BEGIN
894 x_return_status := FND_API.G_RET_STS_SUCCESS;
895 GMI_reservation_Util.PrintLn('cancel_res_for_so_line: so_line_id '||p_so_line_id);
896 /* this would remove all the un-staged trans */
897 IF p_so_line_id is not null THEN
898 Update gml_batch_so_reservations
899 Set delete_mark = 1
900 Where so_line_id = p_so_line_id
901 and delete_mark = 0;
902 IF SQL%NOTFOUND THEN
903 GMI_reservation_Util.PrintLn('cancel_res_for_so_line: no reservations');
904 END IF;
905 END IF;
906 END cancel_res_for_so_line;
907
908 PROCEDURE cancel_res_for_batch
909 (
910 P_Batch_id IN NUMBER default null
911 , X_return_status OUT NOCOPY VARCHAR2
912 , X_msg_cont OUT NOCOPY NUMBER
913 , X_msg_data OUT NOCOPY VARCHAR2
914 ) IS
915
916 BEGIN
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 GMI_reservation_Util.PrintLn('cancel_res_for_batch: batch_id '||p_batch_id);
919 /* this would remove all the un-staged trans */
920 IF p_batch_id is not null THEN
921 Update gml_batch_so_reservations
922 Set delete_mark = 1
923 Where batch_id = p_batch_id
924 and delete_mark = 0;
925 IF SQL%NOTFOUND THEN
926 GMI_reservation_Util.PrintLn('cancel_res_for_batch: no reservations');
927 END IF;
928 END IF;
929 END cancel_res_for_batch;
930
931 PROCEDURE cancel_batch
932 (
933 P_Batch_id IN NUMBER
934 , X_return_status OUT NOCOPY VARCHAR2
935 , X_msg_cont OUT NOCOPY NUMBER
936 , X_msg_data OUT NOCOPY VARCHAR2
937 ) IS
938 BEGIN
939 x_return_status := FND_API.G_RET_STS_SUCCESS;
940 /*
941 Remove all outstanding reservations and allocations
942 When batch is in 'WIP, only terminate can occur. Anything completed remians
943 so NO allocations would be removed.
944 */
945 GML_BATCH_OM_RES_PVT.cancel_res_for_batch
946 (
947 P_Batch_id => p_batch_id
948 , X_return_status => x_return_status
949 , X_msg_cont => x_msg_cont
950 , X_msg_data => x_msg_data
951 );
952 /*GML_BATCH_OM_RES_PVT.cancel_alloc_for_batch
953 (
954 P_Batch_id => p_batch_id
955 , X_return_status => x_return_status
956 , X_msg_cont => x_msg_cont
957 , X_msg_data => x_msg_data
958 );
959 */
960 END cancel_batch;
961
962 PROCEDURE notify_CSR
963 (
964 P_Batch_id IN NUMBER default null
965 , P_Batch_line_id IN NUMBER default null
966 , P_So_line_id IN NUMBER default null
967 , P_batch_trans_id IN NUMBER default null
968 , P_whse_code IN VARCHAR2 default null
969 , P_action_code IN VARCHAR2
970 , X_return_status OUT NOCOPY VARCHAR2
971 , X_msg_cont OUT NOCOPY NUMBER
972 , X_msg_data OUT NOCOPY VARCHAR2
973 ) IS
974 l_csr_id NUMBER;
975
976 l_batch_id NUMBER;
977 l_so_header_id NUMBER;
978 l_so_line_id NUMBER;
979 l_action_code VARCHAR2(200);
980 l_whse_code VARCHAR2(4);
981 l_batch_type NUMBER;
982 l_no_of_staged_alloc NUMBER;
983 l_no_of_unstaged_alloc NUMBER;
984 l_last_updated_by NUMBER;
985 l_created_by NUMBER;
986 l_session_id NUMBER;
987 l_batch_line_id NUMBER;
988 l_old_header_id NUMBER;
989 l_new_header_id NUMBER;
990
991
992 CURSOR So_line_id_for_batch(p_batch_id IN NUMBER) Is
993 SELECT Distinct so_line_id
994 FROM gml_batch_so_reservations
995 WHERE batch_id = p_batch_id
996 and delete_mark = 0
997 and reserved_qty <> 0;
998
999 CURSOR Get_batch_type(p_batch_id IN NUMBER) IS
1000 SELECT batch_type
1001 FROM gme_batch_header
1002 WHERE batch_id = p_batch_id;
1003
1004
1005 CURSOR So_line_id_for_batch_line(p_batch_line_id IN NUMBER) Is
1006 SELECT Distinct so_line_id
1007 FROM gml_batch_so_reservations
1008 WHERE batch_line_id = p_batch_line_id
1009 and delete_mark = 0
1010 and reserved_qty <> 0;
1011
1012 CURSOR get_batch_id_for_line(p_batch_line_id IN NUMBER) Is
1013 SELECT gl.batch_id,gh.batch_type
1014 FROM gme_material_details gl,
1015 gme_batch_header gh
1016 WHERE gl.material_detail_id = p_batch_line_id
1017 and gl.batch_id = gh.batch_id;
1018
1019
1020
1021 Cursor so_line_id_for_batch_trans (p_batch_trans_id IN NUMBER) IS
1022 Select distinct ictran.line_id
1023 From ic_tran_pnd ictran
1024 , gml_batch_so_alloc_history his
1025 Where his.batch_trans_id = p_batch_trans_id
1026 and his.trans_id = ictran.trans_id
1027 and ictran.doc_type = 'OMSO'
1028 and ictran.delete_mark = 0
1029 and ictran.staged_ind = 0
1030 and ictran.completed_ind = 0
1031 ;
1032
1033 Cursor get_batch_id_for_trans (p_batch_trans_id IN NUMBER) IS
1034 SELECT batch_id
1035 ,batch_type
1036 ,batch_line_id
1037 FROM gml_batch_so_alloc_history
1038 WHERE batch_trans_id = p_batch_trans_id;
1039
1040
1041 CURSOR CSR_for_so_line(p_so_line_id IN NUMBER) IS
1042 SELECT last_updated_by, created_by,header_id
1043 FROM oe_order_lines_all
1044 WHERE line_id = p_so_line_id;
1045
1046 Cursor find_nonstgd_alloc_for_trans (P_batch_trans_id IN NUMBER) IS
1047 Select count(*)
1048 From ic_tran_pnd ic
1049 , gml_batch_so_alloc_history his
1050 Where his.batch_trans_id = p_batch_trans_id
1051 and his.trans_id = ic.trans_id
1052 and ic.line_id = his.line_id
1053 and ic.staged_ind = 0
1054 and ic.delete_mark = 0
1055 and ic.doc_type = 'OMSO'
1056 ;
1057
1058 Cursor find_staged_alloc_for_trans (P_batch_trans_id IN NUMBER) IS
1059 Select count(*)
1060 From ic_tran_pnd ic
1061 , gml_batch_so_alloc_history his
1062 Where his.batch_trans_id = p_batch_trans_id
1063 and his.trans_id = ic.trans_id
1064 and ic.line_id = his.line_id
1065 and ic.staged_ind = 1
1066 and ic.delete_mark = 0
1067 and ic.doc_type = 'OMSO'
1068 ;
1069 Cursor check_mul_line_id1 (p_user_id IN number
1070 , p_batch_id IN NUMBER) IS
1071 Select distinct so_line_id
1072 From gml_batch_so_reservations
1073 Where created_by = p_user_id
1074 and batch_id = p_batch_id;
1075
1076 Cursor check_mul_line_id2 (p_user_id IN number
1077 , p_batch_id IN NUMBER
1078 , p_batch_line_id IN NUMBER) IS
1079 Select distinct so_line_id
1080 From gml_batch_so_reservations
1081 Where created_by = p_user_id
1082 and batch_id = p_batch_id
1083 and batch_line_id = p_batch_line_id;
1084
1085 BEGIN
1086
1087 x_return_status := FND_API.G_RET_STS_SUCCESS;
1088
1089 l_session_id := USERENV('sessionid');
1090 l_batch_line_id := p_batch_line_id ;
1091 l_batch_id := p_batch_id;
1092 l_whse_code := p_whse_code;
1093
1094 /* will send the work flow */
1095 GMI_RESERVATION_UTIL.PrintLn('Entering Notify_ CSR ...........');
1096
1097 IF p_batch_id is not null THEN
1098
1099 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : Batch_id is'|| p_batch_id);
1100 OPEN so_line_id_for_batch(p_batch_id);
1101 FETCH so_line_id_for_batch INTO l_so_line_id;
1102 IF(so_line_id_for_batch%NOTFOUND) THEN
1103 CLOSE so_line_id_for_batch;
1104 GMI_RESERVATION_UTIL.PrintLn(' so_line_id_for_batch%NOTFOUND, returning from Notify CSR');
1105 RETURN;
1106 END IF;
1107
1108 CLOSE so_line_id_for_batch;
1109
1110 OPEN get_batch_type(p_batch_id);
1111 FETCH get_batch_type INTO l_batch_type;
1112 IF(get_batch_type%NOTFOUND) THEN
1113 CLOSE get_batch_type;
1114 GMI_RESERVATION_UTIL.PrintLn(' get_batch_type%NOTFOUND, returning from Notify CSR');
1115 RETURN;
1116 END IF;
1117
1118 CLOSE get_batch_type;
1119
1120 END IF;
1121
1122 IF p_batch_line_id is not null THEN
1123 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : Batch_line_id is '|| p_batch_line_id);
1124 OPEN so_line_id_for_batch_line(p_batch_line_id);
1125 FETCH so_line_id_for_batch_line INTO l_so_line_id;
1126 IF(so_line_id_for_batch_line%NOTFOUND) THEN
1127 CLOSE so_line_id_for_batch_line;
1128 GMI_RESERVATION_UTIL.PrintLn(' so_line_id_for_batch_line%NOTFOUND, returning from Notify CSR');
1129 RETURN;
1130 END IF;
1131 CLOSE so_line_id_for_batch_line;
1132
1133 OPEN get_batch_id_for_line(p_batch_line_id);
1134 FETCH get_batch_id_for_line INTO l_batch_id,l_batch_type;
1135 IF(get_batch_id_for_line%NOTFOUND) THEN
1136 CLOSE get_batch_id_for_line;
1137 GMI_RESERVATION_UTIL.PrintLn(' get_batch_id_for_line%NOTFOUND, returning from Notify CSR');
1138 RETURN;
1139 END IF;
1140 CLOSE get_batch_id_for_line;
1141
1142 END IF;
1143 IF p_batch_trans_id is not null THEN
1144
1145 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : Batch_trans_id is '|| p_batch_trans_id);
1146
1147 OPEN so_line_id_for_batch_trans(p_batch_trans_id);
1148 FETCH so_line_id_for_batch_trans INTO l_so_line_id;
1149 IF(so_line_id_for_batch_trans%NOTFOUND) THEN
1150 CLOSE so_line_id_for_batch_trans;
1151 GMI_RESERVATION_UTIL.PrintLn(' so_line_id_for_batch_trans%NOTFOUND, returning from Notify CSR');
1152 RETURN;
1153 END IF;
1154 CLOSE so_line_id_for_batch_trans;
1155
1156 OPEN get_batch_id_for_trans(p_batch_trans_id);
1157 FETCH get_batch_id_for_trans INTO l_batch_id,l_batch_type, l_batch_line_id;
1158 IF(get_batch_id_for_trans%NOTFOUND) THEN
1159 CLOSE get_batch_id_for_trans;
1160 GMI_RESERVATION_UTIL.PrintLn(' get_batch_id_for_trans%NOTFOUND, returning from Notify CSR');
1161 RETURN;
1162 END IF;
1163
1164 CLOSE get_batch_id_for_trans;
1165
1166 END IF;
1167
1168 IF p_so_line_id is not null THEN
1169 /* send notification to all CSRs for this so_line_rec */
1170 null;
1171 END IF;
1172
1173
1174 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : so_line_id is'|| l_so_line_id);
1175 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : p_whse_code is'|| l_whse_code);
1176 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : p_action_code is'|| p_action_code);
1177
1178 l_action_code := p_action_code;
1179
1180 IF(p_action_code = 'CANCEL') THEN
1181
1182 IF(l_batch_type = 10) THEN
1183 l_action_code := 'CANCEL_FPO';
1184 ELSE
1185 l_action_code := 'CANCEL_BATCH';
1186 END IF;
1187 END IF;
1188
1189 IF(p_action_code = 'CMPLT_DATE_CHANGE') THEN
1190 l_action_code := 'PLAN_COMPL_DATE_CHANGED';
1191 END IF;
1192
1193 IF(p_action_code = 'WHSE_CHANGED') THEN
1194
1195 OPEN find_nonstgd_alloc_for_trans(p_batch_trans_id);
1196 FETCH find_nonstgd_alloc_for_trans INTO l_no_of_unstaged_alloc;
1197 IF(find_nonstgd_alloc_for_trans%NOTFOUND) THEN
1198 CLOSE find_nonstgd_alloc_for_trans;
1199 GMI_RESERVATION_UTIL.PrintLn(' find_nonstgd_alloc_for_trans%NOTFOUND, returning from Notify CSR');
1200 RETURN;
1201 END IF;
1202 CLOSE find_nonstgd_alloc_for_trans;
1203
1204 IF (l_no_of_unstaged_alloc >= 1) THEN
1205 l_action_code := 'CHANGE_PROD_WHSE_UNSTAGED_ALLO';
1206 ELSE
1207 OPEN find_staged_alloc_for_trans(p_batch_trans_id);
1208 FETCH find_staged_alloc_for_trans INTO l_no_of_staged_alloc;
1209 IF(find_staged_alloc_for_trans%NOTFOUND) THEN
1210 CLOSE find_staged_alloc_for_trans;
1211 GMI_RESERVATION_UTIL.PrintLn(' find_staged_alloc_for_trans%NOTFOUND, returning from Notify CSR');
1212 RETURN;
1213 END IF;
1214
1215 CLOSE find_nonstgd_alloc_for_trans;
1216
1217 IF(l_no_of_staged_alloc >= 1) THEN
1218 l_action_code := 'CHANGE_PROD_WHSE_STAGED_ALLOC';
1219 END IF;
1220
1221 END IF;
1222
1223 END IF;
1224
1225
1226 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_action_code is'|| l_action_code);
1227
1228 OPEN CSR_for_so_line(l_so_line_id);
1229 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_so_header_id;
1230 IF(CSR_for_so_line%NOTFOUND) THEN
1231 CLOSE CSR_for_so_line;
1232 GMI_RESERVATION_UTIL.PrintLn(' CSR_for_so_line%NOTFOUND, returning from Notify CSR');
1233 RETURN;
1234 END IF;
1235 CLOSE CSR_for_so_line;
1236 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_last_updated_by is '||l_last_updated_by);
1237 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1238 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1239 , p_approver => l_last_updated_by
1240 , p_so_header_id=> l_so_header_id
1241 , p_so_line_id => l_so_line_id
1242 , p_batch_id => l_batch_id
1243 , p_batch_line_id => NULL
1244 , p_whse_code => l_whse_code
1245 , p_action_code => l_action_code );
1246
1247 IF(l_last_updated_by <> l_created_by) THEN
1248
1249 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
1250 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1251
1252 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1253 , p_approver => l_created_by
1254 , p_so_header_id=> l_so_header_id
1255 , p_so_line_id => l_so_line_id
1256 , p_batch_id => l_batch_id
1257 , p_batch_line_id => NULL
1258 , p_whse_code => l_whse_code
1259 , p_action_code => l_action_code );
1260 END IF;
1261
1262 /* check to see if the same user has multiple sales lines for the reservations */
1263 /* for each sales order or header_id, one notification is sent */
1264 IF nvl(l_batch_line_id,0) = 0 THEN
1265 for mul_line in check_mul_line_id1 (l_last_updated_by, l_batch_id ) Loop
1266 l_old_header_id := l_so_header_id ;
1267 l_so_line_id := mul_line.so_line_id ;
1268 /* Get the Order and Line Information */
1269 OPEN CSR_for_so_line(l_so_line_id);
1270 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
1271 CLOSE CSR_for_so_line;
1272 IF l_new_header_id <> l_old_header_id THEN
1273 l_so_header_id := l_new_header_id;
1274 l_old_header_id := l_new_header_id;
1275
1276 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
1277 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1278 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1279 , p_approver => l_last_updated_by
1280 , p_so_header_id=> l_so_header_id
1281 , p_so_line_id => l_so_line_id
1282 , p_batch_id => l_batch_id
1283 , p_batch_line_id => NULL
1284 , p_whse_code => l_whse_code
1285 , p_action_code => l_action_code );
1286
1287 IF(l_last_updated_by <> l_created_by) THEN
1288
1289 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
1290 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1291
1292 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1293 , p_approver => l_created_by
1294 , p_so_header_id=> l_so_header_id
1295 , p_so_line_id => l_so_line_id
1296 , p_batch_id => l_batch_id
1297 , p_batch_line_id => NULL
1298 , p_whse_code => l_whse_code
1299 , p_action_code => l_action_code );
1300 END IF;
1301 END IF;
1302 END LOOP;
1303 Else
1304 for mul_line in check_mul_line_id2 (l_last_updated_by, l_batch_id, l_batch_line_id ) Loop
1305 l_old_header_id := l_so_header_id ;
1306 l_so_line_id := mul_line.so_line_id ;
1307 OPEN CSR_for_so_line(l_so_line_id);
1308 FETCH CSR_for_so_line INTO l_last_updated_by,l_created_by, l_new_header_id ;
1309 CLOSE CSR_for_so_line;
1310 IF l_new_header_id <> l_old_header_id THEN
1311 l_so_header_id := l_new_header_id;
1312 l_old_header_id := l_new_header_id;
1313
1314 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : Multiple sales orders, header_id'||l_so_header_id);
1315 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1316 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1317 , p_approver => l_last_updated_by
1318 , p_so_header_id=> l_so_header_id
1319 , p_so_line_id => l_so_line_id
1320 , p_batch_id => l_batch_id
1321 , p_batch_line_id => NULL
1322 , p_whse_code => l_whse_code
1323 , p_action_code => l_action_code );
1324
1325 IF(l_last_updated_by <> l_created_by) THEN
1326
1327 GMI_RESERVATION_UTIL.PrintLn('Notify CSR : l_created_by is '||l_created_by);
1328 GMI_RESERVATION_UTIL.PrintLn('Initiating the Workflow......');
1329
1330 GML_BATCH_WORKFLOW_PKG.Init_wf( p_session_id => l_session_id
1331 , p_approver => l_created_by
1332 , p_so_header_id=> l_so_header_id
1333 , p_so_line_id => l_so_line_id
1334 , p_batch_id => l_batch_id
1335 , p_batch_line_id => NULL
1336 , p_whse_code => l_whse_code
1337 , p_action_code => l_action_code );
1338 END IF;
1339 END IF;
1340 END LOOP;
1341 END IF;
1342
1343
1344 GMI_RESERVATION_UTIL.PrintLn('Exiting Notify_CSR .............');
1345
1346 EXCEPTION
1347
1348 WHEN OTHERS THEN
1349 GMI_RESERVATION_UTIL.PrintLn('WARNING.... In Others Exception in Notify CSR');
1350 GMI_RESERVATION_UTIL.println('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1351
1352 END notify_CSR;
1353
1354 PROCEDURE regenerate_alloc
1355 (
1356 P_alloc_history_rec IN GML_BATCH_OM_UTIL.alloc_history_rec
1357 , x_return_status OUT NOCOPY VARCHAR2
1358 ) IS
1359 l_batch_line_rec GML_BATCH_OM_UTIL.batch_line_rec ;
1360 l_gme_om_rule_rec GML_BATCH_OM_UTIL.gme_om_rule_rec;
1361 l_Gme_trans_row ic_tran_pnd%rowtype;
1362 l_omso_trans_id NUMBER;
1363 l_msg_cont NUMBER;
1364 l_msg_data VARCHAR2(300);
1365 BEGIN
1366 x_return_status := FND_API.G_RET_STS_SUCCESS;
1367 l_batch_line_rec.batch_line_id := p_alloc_history_rec.batch_line_id;
1368 l_gme_om_rule_rec.rule_id := null;
1369 Select *
1370 Into l_gme_trans_row
1371 From ic_tran_pnd
1372 Where trans_id = p_alloc_history_rec.batch_trans_id;
1373 IF SQL%NOTFOUND THEN
1374 GMI_reservation_Util.PrintLn('regenerate_alloc: no gme_trans');
1375 END IF;
1376 /* set release type = 20, internal use */
1377 l_batch_line_rec.release_type := 20;
1378 GML_BATCH_OM_RES_PVT.create_allocations
1379 (
1380 P_batch_line_rec => l_batch_line_rec
1381 , P_gme_om_rule_rec => l_gme_om_rule_rec
1382 , P_Gme_trans_row => l_gme_trans_row
1383 , X_return_status => x_return_status
1384 , X_msg_cont => l_msg_cont
1385 , X_msg_data => l_msg_data
1386 ) ;
1387
1388 IF x_return_status = fnd_api.g_ret_sts_success Then
1389 /* delete the history record because new history records are created */
1390 update gml_batch_so_alloc_history
1391 set delete_mark = 1
1392 Where alloc_rec_id = p_alloc_history_rec.alloc_rec_id;
1393
1394 /* NC Bug#3470056 Call pick confirm if the flag is set */
1395 IF l_gme_om_rule_rec.auto_pick_confirm = 'Y' THEN
1396 GMI_RESERVATION_UTIL.println('Allocation is successful. Pickconfirm');
1397 /* get the mo line id for the source line */
1398 GML_BATCH_OM_RES_PVT.pick_confirm
1399 (
1400 P_batch_line_rec => l_batch_line_rec
1401 , P_Gme_trans_row => l_gme_trans_row
1402 , X_return_status => x_return_status
1403 , X_msg_cont => l_msg_cont
1404 , X_msg_data => l_msg_data
1405 );
1406
1407 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)
1408 THEN
1409 GMI_RESERVATION_UTIL.PrintLn('WARNING : Pick Confirm Returned error after re-generating the allocations');
1410 END IF;
1411
1412 END IF;
1413
1414 ELSE
1415 GMI_reservation_Util.PrintLn('In Else part after GML_BATCH_OM_RES_PVT.create_allocations returned a status other than success In regenerate_alloc');
1416 GMI_reservation_Util.PrintLn('OM_UTIL, checking rule failure');
1417 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
1418 FND_MESSAGE.Set_Token('WHERE', 'Check rules');
1419 FND_MSG_PUB.ADD;
1420 RAISE FND_API.G_EXC_ERROR;
1421 END IF;
1422
1423 GML_GME_API_PVT.g_not_to_notify := 1;
1424 END regenerate_alloc;
1425
1426 PROCEDURE process_om_reservations
1427 (
1428 p_from_batch_id IN NUMBER default null
1429 , P_batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
1430 , P_Gme_trans_row IN ic_tran_pnd%rowtype
1431 , P_batch_action IN VARCHAR2
1432 , x_return_status OUT NOCOPY VARCHAR2
1433 ) IS
1434 BEGIN
1435 x_return_status := FND_API.G_RET_STS_SUCCESS;
1436 null;
1437 END process_om_reservations;
1438
1439 PROCEDURE split_reservations
1440 ( p_old_delivery_detail_id IN NUMBER
1441 , p_new_delivery_detail_id IN NUMBER
1442 , p_old_source_line_id IN NUMBER
1443 , p_new_source_line_id IN NUMBER
1444 , p_qty_to_split IN NUMBER
1445 , p_qty2_to_split IN NUMBER
1446 , p_orig_qty IN NUMBER
1447 , p_orig_qty2 IN NUMBER
1448 , p_action IN VARCHAR2
1449 , x_return_status OUT NOCOPY VARCHAR2
1450 , x_msg_count OUT NOCOPY NUMBER
1451 , x_msg_data OUT NOCOPY VARCHAR2
1452 ) IS
1453 l_fulfilled_qty NUMBER;
1454 l_qty_to_fulfil NUMBER;
1455 l_qty2_to_fulfil NUMBER;
1456 l_so_line_rec GML_BATCH_OM_UTIL.so_line_rec;
1457 l_batch_line_rec GML_BATCH_OM_UTIL.batch_line_rec;
1458 l_reservation_rec GML_BATCH_OM_UTIL.gme_om_reservation_rec;
1459
1460 cursor c_reservations IS
1461 SELECT reserved_qty
1462 , reserved_qty2
1463 , batch_res_id
1464 FROM gml_batch_so_reservations
1465 WHERE so_line_id = p_old_source_line_id
1466 AND delivery_detail_id = p_old_delivery_detail_id
1467 AND delete_mark = 0
1468 AND reserved_qty <> 0
1469 ORDER BY 1 ; /* the smaller qty is at the top, keep in mind it is neg */
1470 /* or should consider the alloc rules */
1471 cursor c_reservations1 IS -- Not booked
1472 SELECT reserved_qty
1473 , reserved_qty2
1474 , batch_res_id
1475 FROM gml_batch_so_reservations
1476 WHERE so_line_id = p_old_source_line_id
1477 AND delete_mark = 0
1478 AND reserved_qty <> 0
1479 ORDER BY 1; /* the smaller qty is at the top, keep in mind it is neg */
1480 /* or should consider the alloc rules */
1481 res_rec c_reservations%rowtype;
1482
1483 BEGIN
1484 x_return_status := FND_API.G_RET_STS_SUCCESS;
1485 GMI_reservation_Util.PrintLn('Split Reservations for GME');
1486 /* check the reservations, if non exists, exit */
1487 IF p_old_source_line_id is not null THEN
1488 IF NOT GML_BATCH_OM_UTIL.check_reservation
1489 (
1490 P_so_line_id => p_old_source_line_id
1491 , X_return_status => x_return_status
1492 , X_msg_cont => x_msg_count
1493 , X_msg_data => x_msg_data
1494 )
1495 THEN
1496 GMI_reservation_Util.PrintLn('Split Reservations: No reservations found for so line ');
1497 return;
1498 END IF;
1499 END IF;
1500 IF p_old_delivery_detail_id is not null THEN
1501 IF NOT GML_BATCH_OM_UTIL.check_reservation
1502 (
1503 P_delivery_detail_id => p_old_delivery_detail_id
1504 , X_return_status => x_return_status
1505 , X_msg_cont => x_msg_count
1506 , X_msg_data => x_msg_data
1507 )
1508 THEN
1509 GMI_reservation_Util.PrintLn('Split Reservations: No reservations found for wdd line ');
1510 return;
1511 END IF;
1512 END IF;
1513 GMI_RESERVATION_UTIL.Println(' p_old_delivery_detail_id '||p_old_delivery_detail_id);
1514 GMI_RESERVATION_UTIL.Println(' p_new_delivery_detail_id '||p_new_delivery_detail_id);
1515 GMI_RESERVATION_UTIL.Println(' p_old_source_line_id '||p_old_source_line_id);
1516 GMI_RESERVATION_UTIL.Println(' p_new_source_line_id '||p_new_source_line_id);
1517
1518 IF p_action = 'B' THEN -- Back ordering or staging
1519 Update gml_batch_so_reservations
1520 Set so_line_id = p_new_source_line_id
1521 Where so_line_id = p_old_source_line_id
1522 And delete_mark = 0
1523 And reserved_qty <> 0;
1524 END IF;
1525 IF p_action = 'O' THEN -- all others, just to split
1526 /* this used to be for NONcontroled items or non inv*/
1527
1528 /*IF (p_new_delivery_detail_id is NOT NULL) AND (p_old_source_line_id is NOT NULL) AND
1529 (p_new_source_line_id is NOT NULL) AND (p_old_source_line_id <> p_new_source_line_id) THEN
1530 Update gml_batch_so_reservtions
1531 Set so_line_id = p_new_source_line_id
1532 Where so_line_id = p_old_source_line_id
1533 and delivery_detail_id = p_new_delivery_detail_id
1534 and delte_mark = 0
1535 and reserved_qty <> 0;
1536 GMI_RESERVATION_UTIL.PrintLn('Updated Here');
1537 END IF;*/
1538 -- all GME reservations now are for controlled items only
1539 l_fulfilled_qty := 0;
1540 l_qty_to_fulfil := p_orig_qty - p_qty_to_split;
1541 l_qty2_to_fulfil := p_orig_qty2 - p_qty2_to_split;
1542 --l_qty_to_fulfil := p_qty_to_split;
1543 --l_qty2_to_fulfil := p_qty2_to_split;
1544
1545 GMI_RESERVATION_UTIL.Println('in split_res, qty to split '||p_qty_to_split);
1546 GMI_RESERVATION_UTIL.Println('in split_res, qty2 to split '||p_qty2_to_split);
1547 GMI_RESERVATION_UTIL.Println('in split_res, qty to fulfil '||l_qty_to_fulfil);
1548 IF p_old_delivery_detail_id is null THEN -- not booked
1549 Open c_reservations1;
1550 ELSE
1551 Open c_reservations;
1552 END IF;
1553 Loop
1554 -- for res_rec in c_reservations Loop
1555 IF p_old_delivery_detail_id is null THEN -- not booked
1556 Fetch c_reservations1 into res_rec;
1557 ELSE
1558 Fetch c_reservations into res_rec;
1559 END IF;
1560 EXIT WHEN res_rec.batch_res_id is null;
1561 IF p_old_delivery_detail_id is null THEN -- not booked
1562 EXIT WHEN c_reservations1%NOTFOUND;
1563 ELSE
1564 EXIT WHEN c_reservations%NOTFOUND;
1565 END IF;
1566 IF abs(res_rec.reserved_qty) < l_qty_to_fulfil THEN
1567 /* do nothing for the res */
1568 GMI_RESERVATION_UTIL.Println('in split_res, keep trans the same for batch_res_id '||res_rec.batch_res_id);
1569 GMI_RESERVATION_UTIL.Println('in split_res, reserved_qty '||res_rec.reserved_qty);
1570 l_qty_to_fulfil := l_qty_to_fulfil - abs(res_rec.reserved_qty);
1571 l_qty2_to_fulfil := l_qty2_to_fulfil - abs(res_rec.reserved_qty2);
1572 ELSIF res_rec.reserved_qty > l_qty_to_fulfil AND l_qty_to_fulfil > 0 THEN
1573 update gml_batch_so_reservations
1574 set reserved_qty = l_qty_to_fulfil
1575 , reserved_qty2 = l_qty2_to_fulfil
1576 Where batch_res_id = res_rec.batch_res_id;
1577
1578 GMI_RESERVATION_UTIL.Println('in split_res, split res '||res_rec.batch_res_id);
1579 GMI_RESERVATION_UTIL.Println('in split_res, reserved_qty '||res_rec.reserved_qty);
1580 l_so_line_rec.so_line_id := null;
1581 l_batch_line_rec.batch_line_id := null;
1582 l_reservation_rec.batch_res_id := res_rec.batch_res_id;
1583 /* create a new res for the new wdd, and new line_id if applicable */
1584 GML_BATCH_OM_UTIL.query_reservation
1585 (
1586 P_So_line_rec => l_so_line_rec
1587 , P_Batch_line_rec => l_batch_line_rec
1588 , P_Gme_om_reservation_rec => l_reservation_rec
1589 , X_return_status => x_return_status
1590 , X_msg_cont => x_msg_count
1591 , X_msg_data => x_msg_data
1592 ) ;
1593 l_reservation_rec.batch_res_id := null;
1594 l_reservation_rec.reserved_qty := res_rec.reserved_qty - l_qty_to_fulfil;
1595 l_reservation_rec.reserved_qty2 := res_rec.reserved_qty2 - l_qty2_to_fulfil;
1596 l_reservation_rec.so_line_id := p_new_source_line_id;
1597 l_reservation_rec.delivery_detail_id := p_new_delivery_detail_id;
1598 GML_BATCH_OM_UTIL.insert_reservation
1599 (
1600 P_Gme_om_reservation_rec => l_reservation_rec
1601 , X_return_status => x_return_status
1602 , X_msg_cont => x_msg_count
1603 , X_msg_data => x_msg_data
1604 ) ;
1605 /* qty filfilled*/
1606 l_qty_to_fulfil := 0;
1607 l_qty2_to_fulfil := 0;
1608 ELSIF l_qty_to_fulfil <= 0 THEN
1609 GMI_RESERVATION_UTIL.Println('in split_res, update res '||res_rec.batch_res_id);
1610 GMI_RESERVATION_UTIL.Println('in split_res, reserved_qty '||res_rec.reserved_qty);
1611 -- simply update the rest with the new wdd id and new line_id
1612 update gml_batch_so_reservations
1613 set delivery_detail_id = p_new_delivery_detail_id
1614 , so_line_id = p_new_source_line_id
1615 Where batch_res_id = res_rec.batch_res_id;
1616 END IF;
1617 END LOOP;
1618 IF p_old_delivery_detail_id is null THEN -- not booked
1619 Close c_reservations1;
1620 ELSE
1621 Close c_reservations;
1622 END IF;
1623 END IF;
1624
1625 EXCEPTION
1626 WHEN FND_API.G_EXC_ERROR THEN
1627 x_return_status := FND_API.G_RET_STS_ERROR;
1628 /* Get message count and data*/
1629 FND_MSG_PUB.count_and_get
1630 ( p_count => x_msg_count
1631 , p_data => x_msg_data
1632 );
1633 GMI_reservation_Util.PrintLn('Split reservations: EXCEPTION: Expected');
1634 WHEN OTHERS THEN
1635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1636
1637 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1638 , 'Split_reservations'
1639 );
1640 /* Get message count and data*/
1641 FND_MSG_PUB.count_and_get
1642 ( p_count => x_msg_count
1643 , p_data => x_msg_data
1644 );
1645 GMI_reservation_Util.PrintLn('Split reservations EXCEPTION: Others');
1646
1647 END split_reservations;
1648
1649 PROCEDURE split_reservations_from_om
1650 ( p_old_source_line_id IN NUMBER
1651 , p_new_source_line_id IN NUMBER
1652 , p_qty_to_split IN NUMBER -- remaining qty to the old line_id
1653 , p_qty2_to_split IN NUMBER -- remaining qty2 to the old line_id
1654 , p_orig_qty IN NUMBER
1655 , p_orig_qty2 IN NUMBER
1656 , x_return_status OUT NOCOPY VARCHAR2
1657 , x_msg_count OUT NOCOPY NUMBER
1658 , x_msg_data OUT NOCOPY VARCHAR2
1659 )
1660 IS
1661 BEGIN
1662 x_return_status := FND_API.G_RET_STS_SUCCESS;
1663 GML_BATCH_OM_RES_PVT.split_reservations
1664 ( p_old_delivery_detail_id => null
1665 , p_new_delivery_detail_id => null
1666 , p_old_source_line_id => p_old_source_line_id
1667 , p_new_source_line_id => p_new_source_line_id
1668 , p_qty_to_split => p_orig_qty - p_qty_to_split -- oppsite from OM
1669 , p_qty2_to_split => p_orig_qty - p_qty2_to_split
1670 , p_orig_qty => p_orig_qty
1671 , p_orig_qty2 => p_orig_qty2
1672 , p_action => 'O'
1673 , x_return_status => x_return_status
1674 , x_msg_count => x_msg_count
1675 , x_msg_data => x_msg_data
1676 ) ;
1677
1678 END split_reservations_from_om;
1679
1680 PROCEDURE check_gmeres_for_so_line
1681 ( p_so_line_id IN NUMBER
1682 , p_delivery_detail_id IN NUMBER
1683 , x_return_status OUT NOCOPY VARCHAR2
1684 ) IS
1685 l_msg_cont NUMBER;
1686 l_msg_data VARCHAR2(300);
1687 BEGIN
1688 IF NOT GML_BATCH_OM_UTIL.check_reservation
1689 (
1690 p_so_line_id => p_so_line_id
1691 , X_return_status => x_return_status
1692 , X_msg_cont => l_msg_cont
1693 , X_msg_data => l_msg_data
1694 )
1695 THEN
1696 return;
1697 END IF;
1698
1699 update gml_batch_so_reservations
1700 set delivery_detail_id = p_delivery_detail_id
1701 Where so_line_id = p_so_line_id
1702 and delete_mark = 0;
1703
1704 END check_gmeres_for_so_line;
1705
1706 PROCEDURE pick_confirm
1707 (
1708 P_batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
1709 , P_Gme_trans_row IN ic_tran_pnd%rowtype
1710 , X_return_status OUT NOCOPY VARCHAR2
1711 , X_msg_cont OUT NOCOPY NUMBER
1712 , X_msg_data OUT NOCOPY VARCHAR2
1713 ) IS
1714
1715 l_mo_line_id NUMBER;
1716 l_delivery_detail_id NUMBER;
1717 l_detailed_qty NUMBER;
1718 l_detailed_qty2 NUMBER;
1719 l_qty_um VARCHAR2(5);
1720 l_qty_um2 VARCHAR2(5);
1721
1722 Cursor get_wdd_id (p_batch_trans_id IN NUMBER) IS
1723 Select line_detail_id
1724 From ic_tran_pnd
1725 Where delete_mark = 0
1726 and trans_id in
1727 (Select trans_id
1728 From gml_batch_so_alloc_history
1729 Where batch_trans_id = p_batch_trans_id
1730 and delete_mark = 0
1731 )
1732 ;
1733
1734 Cursor get_mo_line_id (p_batch_line_id IN NUMBER ) IS
1735 Select distinct wdd.move_order_line_id
1736 From wsh_delivery_details wdd
1737 , ic_tran_pnd ictran
1738 Where wdd.delivery_detail_id = ictran.line_detail_id
1739 and ictran.delete_mark = 0
1740 and ictran.trans_id in
1741 (Select trans_id
1742 From gml_batch_so_alloc_history
1743 Where batch_line_id = p_batch_line_id
1744 and delete_mark = 0
1745 )
1746 ;
1747
1748 BEGIN
1749 l_mo_line_id := 0;
1750 /* get the move order line id */
1751 for mo_id_rec in get_mo_line_id(p_batch_line_rec.batch_line_id) loop
1752 l_mo_line_id := mo_id_rec.move_order_line_id;
1753 GMI_RESERVATION_UTIL.println('Pick confirm move order line id '|| l_mo_line_id);
1754 IF nvl(l_mo_line_id, 0) <> 0 THEN
1755 GMI_MOVE_ORDER_LINE_UTIL.Line_Pick_Confirm
1756 ( p_mo_line_id => l_mo_line_id
1757 , p_init_msg_list => 1
1758 , p_move_order_type => 3
1759 , x_delivered_qty => l_detailed_qty
1760 , x_qty_UM => l_qty_um
1761 , x_delivered_qty2 => l_detailed_qty2
1762 , x_qty_UM2 => l_qty_um2
1763 , x_return_status => x_return_status
1764 , x_msg_count => x_msg_cont
1765 , x_msg_data => x_msg_data
1766 );
1767 IF x_return_status <> fnd_api.g_ret_sts_success Then
1768 GMI_reservation_Util.PrintLn('pick confirm failed ');
1769 FND_MESSAGE.SET_NAME('GMI','GMI_PICK_CONFIRM');
1770 FND_MESSAGE.Set_Token('WHERE', 'pick confirm ');
1771 FND_MSG_PUB.ADD;
1772 --RAISE FND_API.G_EXC_ERROR;
1773 END IF;
1774 END IF;
1775 end loop;
1776 END pick_confirm ;
1777
1778 PROCEDURE build_trans_rec
1779 (
1780 p_trans_row IN ic_tran_pnd%rowtype
1781 , x_trans_rec IN OUT NOCOPY GMI_TRANS_ENGINE_PUB.ictran_rec
1782 ) IS
1783 BEGIN
1784 x_trans_rec.trans_id := null;
1785 x_trans_rec.item_id := p_trans_row.item_id;
1786 x_trans_rec.co_code := p_trans_row.co_code;
1787 x_trans_rec.orgn_code := p_trans_row.orgn_code;
1788 x_trans_rec.whse_code := p_trans_row.whse_code;
1789 x_trans_rec.lot_id := p_trans_row.lot_id;
1790 x_trans_rec.location := p_trans_row.location;
1791 x_trans_rec.doc_type := 'OMSO';
1792 x_trans_rec.reason_code := null;
1793 x_trans_rec.trans_date := sysdate;
1794 x_trans_rec.qc_grade := p_trans_row.qc_grade;
1795 x_trans_rec.lot_status := p_trans_row.lot_status;
1796 x_trans_rec.trans_um := p_trans_row.trans_um;
1797 x_trans_rec.trans_um2 := p_trans_row.trans_um2;
1798 x_trans_rec.staged_ind := 0;
1799
1800 END build_trans_rec;
1801
1802 PROCEDURE build_res_rec
1803 (
1804 p_res_row IN gml_batch_so_reservations%rowtype
1805 , x_res_rec OUT NOCOPY GML_BATCH_OM_UTIL.gme_om_reservation_rec
1806 ) IS
1807 BEGIN
1808 x_res_rec.batch_res_id := null;
1809 x_res_rec.item_id := p_res_row.item_id;
1810 x_res_rec.whse_code := p_res_row.whse_code;
1811 x_res_rec.order_id := p_res_row.order_id;
1812 x_res_rec.so_line_id := p_res_row.so_line_id;
1813 x_res_rec.delivery_detail_id := p_res_row.delivery_detail_id;
1814 x_res_rec.mo_line_id := p_res_row.mo_line_id;
1815 x_res_rec.uom1 := p_res_row.qty_uom;
1816 x_res_rec.uom2 := p_res_row.qty2_uom;
1817 x_res_rec.sched_ship_date := p_res_row.scheduled_ship_date;
1818
1819 END build_res_rec;
1820
1821 PROCEDURE PRINT_DEBUG
1822 (
1823 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
1824 ,p_routine IN VARCHAR2
1825 )
1826 IS
1827 BEGIN
1828
1829 GMI_RESERVATION_UTIL.println(' *** Called From -> ' || p_routine );
1830 GMI_RESERVATION_UTIL.println(' TRANS_ID -> ' || p_tran_rec.trans_id);
1831 GMI_RESERVATION_UTIL.println(' ITEM_ID -> ' || p_tran_rec.item_id);
1832 GMI_RESERVATION_UTIL.println(' LINE_ID -> ' || p_tran_rec.line_id);
1833 GMI_RESERVATION_UTIL.println(' CO_CODE -> ' || p_tran_rec.co_code);
1834 GMI_RESERVATION_UTIL.println(' ORGN_CODE -> ' || p_tran_rec.orgn_code);
1835 GMI_RESERVATION_UTIL.println(' WHSE_CODE -> ' || p_tran_rec.whse_code);
1836 GMI_RESERVATION_UTIL.println(' LOT_ID -> ' || p_tran_rec.lot_id);
1837 GMI_RESERVATION_UTIL.println(' LOCATION -> ' || p_tran_rec.location);
1838 GMI_RESERVATION_UTIL.println(' DOC_ID -> ' || p_tran_rec.doc_id);
1839 GMI_RESERVATION_UTIL.println(' DOC_TYPE -> ' || p_tran_rec.doc_type);
1840 GMI_RESERVATION_UTIL.println(' DOC_LINE -> ' || p_tran_rec.doc_line);
1841 GMI_RESERVATION_UTIL.println(' LINE_TYPE -> ' || p_tran_rec.line_type);
1842 GMI_RESERVATION_UTIL.println(' REAS_CODE -> ' || p_tran_rec.reason_code);
1843 GMI_RESERVATION_UTIL.println(' TRANS_DATE -> ' || p_tran_rec.trans_date);
1844 GMI_RESERVATION_UTIL.println(' TRANS_QTY -> ' || p_tran_rec.trans_qty);
1845 GMI_RESERVATION_UTIL.println(' TRANS_QTY2 -> ' || p_tran_rec.trans_qty2);
1846 GMI_RESERVATION_UTIL.println(' QC_GRADE -> ' || p_tran_rec.qc_grade);
1847 GMI_RESERVATION_UTIL.println(' LOT_STATUS -> ' || p_tran_rec.lot_status);
1848 GMI_RESERVATION_UTIL.println(' TRANS_STAT -> ' || p_tran_rec.trans_stat);
1849 GMI_RESERVATION_UTIL.println(' TRANS_UM -> ' || p_tran_rec.trans_um);
1850 GMI_RESERVATION_UTIL.println(' TRANS_UM2 -> ' || p_tran_rec.trans_um2);
1851 GMI_RESERVATION_UTIL.println(' USER_ID -> ' || p_tran_rec.user_id);
1852 GMI_RESERVATION_UTIL.println(' TEXT_CODE -> ' || p_tran_rec.text_code);
1853 GMI_RESERVATION_UTIL.println(' NON_INV -> ' || p_tran_rec.non_inv);
1854 GMI_RESERVATION_UTIL.println(' STAGED_IND -> ' || p_tran_rec.staged_ind);
1855
1856 END PRINT_DEBUG;
1857
1858 END GML_BATCH_OM_RES_PVT;