[Home] [Help]
PACKAGE BODY: APPS.GML_GME_API_PVT
Source
1 PACKAGE body GML_GME_API_PVT AS
2 /* $Header: GMLFGMEB.pls 120.0 2005/05/25 16:37:22 appldev noship $
3 +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIURSVS.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private APIs relating to OPM |
13 | reservation. |
14 | |
15 | |
16 | HISTORY |
17 | Aug-18-2003 Liping Gao Created |
18 +=========================================================================+
19 API Name : GML_GME_API_PVT
20 Type : Private
21 Function : This package contains Private API 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_PROD_YIELDED Number(5) := 0; -- Product has not been yielded
31 G_BATCH_ID Number(15):= 0; -- Batch id for the session
32 G_BATCH_LINE_ID Number(15):= 0; -- Batch line id for the session
33
34 PROCEDURE process_om_reservations
35 (
36 P_from_batch_id IN NUMBER default null
37 , P_batch_line_rec IN GML_BATCH_OM_UTIL.batch_line_rec
38 , P_Gme_trans_row IN ic_tran_pnd%rowtype
39 , P_batch_action IN VARCHAR2
40 , x_return_status OUT NOCOPY VARCHAR2
41 ) IS
42 l_so_line_rec GML_BATCH_OM_UTIL.so_line_rec ;
43 l_rule_rec GML_BATCH_OM_UTIL.gme_om_rule_rec ;
44 l_history_rec GML_BATCH_OM_UTIL.alloc_history_rec;
45 ll_history_rec GML_BATCH_OM_UTIL.alloc_history_rec;
46 l_batch_line_rec GML_BATCH_OM_UTIL.batch_line_rec ;
47 l_batch_id NUMBER;
48 l_batch_line_id NUMBER;
49 l_batch_trans_id NUMBER;
50 l_msg_cont NUMBER;
51 l_msg_data VARCHAR2(300);
52 l_old_gme_trans_row ic_tran_pnd%rowtype;
53 l_new_gme_trans_row ic_tran_pnd%rowtype;
54 l_tran_row ic_tran_pnd%rowtype;
55 l_res_qty NUMBER;
56 l_res_qty2 NUMBER;
57 l_trans_qty NUMBER;
58 l_trans_qty2 NUMBER;
59 l_planned_qty NUMBER;
60 l_planned_qty2 NUMBER;
61 l_whse_code VARCHAR2(5);
62 l_trans_id NUMBER;
63 l_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
64 l_dft_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
65 l_notify NUMBER;
66 l_update_history NUMBER;
67 l_return_status VARCHAR2(30);
68
69 Cursor get_trans_row (p_trans_id IN NUMBER) is
70 Select *
71 from ic_tran_pnd
72 where trans_id = p_trans_id;
73
74 Cursor check_res_whse (p_batch_line_id IN NUMBER) is
75 Select distinct whse_code
76 From gml_batch_so_reservations
77 Where batch_line_id = p_batch_line_id;
78
79 Cursor get_res_qty(p_batch_line_id IN NUMBER
80 , p_whse_code IN VARCHAR2) is
81 Select sum(reserved_qty), sum(reserved_qty2)
82 From gml_batch_so_reservations
83 Where batch_line_id = p_batch_line_id
84 and whse_code = p_whse_code
85 and delete_mark = 0;
86
87 Cursor get_planned_qty (p_batch_line_id IN NUMBER
88 , p_whse_code IN VARCHAR2) is
89 Select abs(sum(nvl(trans_qty,0))), abs(sum(nvl(trans_qty2,0)))
90 From ic_tran_pnd
91 Where line_id = p_batch_line_id
92 and whse_code = p_whse_code
93 and doc_type in ('PROD', 'FPO')
94 and delete_mark = 0
95 and completed_ind = 0;
96
97 Cursor get_trans_id (P_batch_trans_id IN NUMBER) IS
98 Select ic.trans_id
99 From ic_tran_pnd ic
100 , gml_batch_so_alloc_history his
101 Where his.batch_trans_id = p_batch_trans_id
102 and his.trans_id = ic.trans_id
103 and ic.line_id = his.line_id
104 and ic.staged_ind = 0
105 and ic.delete_mark = 0
106 and ic.doc_type = 'OMSO'
107 ;
108
109 Cursor get_total_OMSO (p_gme_trans_id IN NUMBER) IS
110 Select abs(sum(trans_qty)),abs(sum(trans_qty2))
111 From ic_tran_pnd ic
112 , gml_batch_so_alloc_history his
113 Where his.batch_trans_id = p_gme_trans_id
114 and his.trans_id = ic.trans_id
115 and his.delete_mark = 0
116 and ic.delete_mark = 0
117 and ic.completed_ind = 0
118 ;
119
120 Cursor get_history_id (p_gme_trans_id in NUMBER) IS
121 Select alloc_rec_id
122 From gml_batch_so_alloc_history
123 Where batch_trans_id = p_gme_trans_id
124 and delete_mark = 0
125 ;
126
127 Cursor get_res_for_batch_line(p_batch_line_id In NUMBER) is
128 Select scheduled_ship_date
129 From gml_batch_so_reservations
130 Where batch_line_id = p_batch_line_id
131 and delete_mark = 0
132 and reserved_qty <> 0
133 ;
134
135 Cursor get_lnid_for_batch(p_batch_id In NUMBER) is
136 Select distinct batch_line_id
137 From gml_batch_so_reservations
138 Where batch_id = p_batch_id
139 and delete_mark = 0
140 and reserved_qty <> 0
141 ;
142
143 BEGIN
144 x_return_status := fnd_api.g_ret_sts_success;
145 l_notify := 0;
146 l_update_history := 0;
147
148 GMI_RESERVATION_UTIL.println('--- Process_OM_reservations --- ');
149 GMI_RESERVATION_UTIL.println('Action code '||p_batch_action);
150 GMI_RESERVATION_UTIL.println('p_from_batch_id'||p_from_batch_id);
151 GMI_RESERVATION_UTIL.println('p_batch_line.batch_id '||p_batch_line_rec.batch_id);
152 GMI_RESERVATION_UTIL.println('p_batch_line.batch_line_id '||p_batch_line_rec.batch_line_id);
153 GMI_RESERVATION_UTIL.println('p_batch_line.trans_id '||p_batch_line_rec.trans_id);
154 GMI_RESERVATION_UTIL.println('p_gme_trans_row.trans_id '||p_gme_trans_row.trans_id);
155 GMI_RESERVATION_UTIL.println('g_batch_id '||g_batch_id);
156 GMI_RESERVATION_UTIL.println('g_batch_line_id '||g_batch_line_id);
157 l_batch_line_rec := p_batch_line_rec;
158 l_batch_id := p_batch_line_rec.batch_id;
159 l_batch_line_id := p_batch_line_rec.batch_line_id;
160 l_batch_trans_id := p_batch_line_rec.trans_id;
161 l_new_gme_trans_row := p_gme_trans_row;
162
163 IF g_batch_id = 0 THEN
164 g_batch_id := l_batch_id ; -- initialize the gloable variable
165 END IF;
166 IF g_batch_line_id = 0 THEN
167 g_batch_line_id := l_batch_line_id ; -- initialize the gloable variable
168 END IF;
169
170 IF l_batch_id <> g_batch_id OR l_batch_line_id <> g_batch_line_id THEN
171 GMI_RESERVATION_UTIL.println('reset global ');
172 g_batch_id := l_batch_id;
173 g_batch_line_id := l_batch_line_id;
174 g_prod_yielded := 0; -- Batch initiation
175 GML_GME_API_PVT.g_not_to_delete := 0; -- always delete reservation when availability is 0
176 END IF;
177
178 IF p_from_batch_id is not null THEN
179 l_batch_id := p_from_batch_id;
180 END IF;
181
182 /* check the reservations, if non exists, exit */
183 IF l_batch_line_id is not null THEN
184 IF NOT GML_BATCH_OM_UTIL.check_reservation
185 (
186 P_Batch_line_id => p_batch_line_rec.batch_line_id
187 , X_return_status => x_return_status
188 , X_msg_cont => l_msg_cont
189 , X_msg_data => l_msg_data
190 )
191 THEN
192 return;
193 END IF;
194 END IF;
195 IF l_batch_id is not null THEN
196 IF NOT GML_BATCH_OM_UTIL.check_reservation
197 (
198 P_Batch_id => l_batch_id
199 , X_return_status => x_return_status
200 , X_msg_cont => l_msg_cont
201 , X_msg_data => l_msg_data
202 )
203 THEN
204 return;
205 END IF;
206 END IF;
207
208 -- Batch level actions
209 IF p_batch_action = 'CONVERT' THEN
210 GMI_RESERVATION_UTIL.PrintLn(' Batch level actions: CONVERT');
211 /* FPO convert to batches */
212 IF p_from_batch_id is null THEN
213 GMI_RESERVATION_UTIL.println('PRocess_om_reservations ...FPO batch_id is null');
214 return;
215 END IF;
216 GML_BATCH_OM_RES_PVT.create_reservation_from_FPO
217 (
218 P_FPO_batch_id => p_from_batch_id
219 , P_New_batch_id => p_batch_line_rec.batch_id
220 , X_return_status => x_return_status
221 , X_msg_cont => l_msg_cont
222 , X_msg_data => l_msg_data
223 );
224 return;
225 END IF;
226
227 IF p_batch_action = 'DELETE' THEN
228 /* 1) cancel-terminate batch -- batch level done here
229 * 2) reserval batch at the trans level -- batch line level blocks below
230 */
231 IF p_batch_line_rec.batch_id is not null
232 and p_batch_line_rec.batch_line_id is null
233 and p_batch_line_rec.trans_id is null
234 THEN
235 GMI_RESERVATION_UTIL.PrintLn(' Batch level actions: DELETE');
236 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....Calling GML_BATCH_OM_RES_PVT.cancel_batch');
237 For res in get_lnid_for_batch(p_batch_line_rec.batch_id) Loop
238 l_batch_line_rec.batch_line_id := res.batch_line_id;
239 /* get the rule for the batch line */
240 GML_BATCH_OM_UTIL.get_rule
241 (
242 P_so_line_rec => l_so_line_rec
243 , P_batch_line_rec => l_batch_line_rec
244 , X_gme_om_rule_rec => l_rule_rec
245 , X_return_status => x_return_status
246 , X_msg_cont => l_msg_cont
247 , X_msg_data => l_msg_data
248 );
249 IF l_rule_rec.order_notification = 'Y' THEN
250 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....Calling GML_BATCH_OM_RES_PVT.notify_CSR');
251 GML_BATCH_OM_RES_PVT.notify_CSR
252 (
253 P_Batch_line_id => res.batch_line_id
254 , P_action_code => 'CANCEL'
255 , X_return_status => x_return_status
256 , X_msg_cont => l_msg_cont
257 , X_msg_data => l_msg_data
258 );
259 END IF;
260 END LOOP;
261
262 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....After GML_BATCH_OM_RES_PVT.cancel_batch');
263 GML_BATCH_OM_RES_PVT.cancel_batch
264 (
265 P_Batch_id => p_batch_line_rec.batch_id
266 , X_return_status => x_return_status
267 , X_msg_cont => l_msg_cont
268 , X_msg_data => l_msg_data
269 );
270 return;
271 END IF;
272 END IF;
273
274 IF p_batch_line_rec.batch_id is not null
275 And p_batch_line_rec.batch_line_id is null THEN
276 IF p_batch_action = 'NOTIFY_CSR' THEN
277 GMI_RESERVATION_UTIL.PrintLn(' Batch level actions: NOTIFY_CSR');
278 /* check to see the planned complt date */
279 For res in get_lnid_for_batch(p_batch_line_rec.batch_id) Loop
280 l_batch_line_rec.batch_line_id := res.batch_line_id;
281 /* get the rule for the batch line */
282 GML_BATCH_OM_UTIL.get_rule
283 (
284 P_so_line_rec => l_so_line_rec
285 , P_batch_line_rec => l_batch_line_rec
286 , X_gme_om_rule_rec => l_rule_rec
287 , X_return_status => x_return_status
288 , X_msg_cont => l_msg_cont
289 , X_msg_data => l_msg_data
290 );
291 IF l_rule_rec.order_notification = 'Y' THEN
292 For res_line in get_res_for_batch_line(res.batch_line_id) Loop
293 IF p_batch_line_rec.cmplt_date > res_line.scheduled_ship_date THEN
294 GML_BATCH_OM_RES_PVT.notify_CSR
295 (
296 P_batch_id => p_batch_line_rec.batch_id
297 , P_action_code => 'CMPLT_DATE_CHANGE'
298 , X_return_status => x_return_status
299 , X_msg_cont => l_msg_cont
300 , X_msg_data => l_msg_data
301 );
302 END IF;
303 END LOOP;
304 END IF;
305 END LOOP;
306 return;
307 END IF;
308 END IF;
309
310 -- Batch line level or trans level actions
311 /* get the rule for this batch line */
312 l_so_line_rec.so_line_id := null;
313 /* always use the whse from the gme trans */
314 l_so_line_rec.whse_code := p_gme_trans_row.whse_code;
315 GML_BATCH_OM_UTIL.get_rule
316 (
317 P_so_line_rec => l_so_line_rec
318 , P_batch_line_rec => p_batch_line_rec
319 , X_gme_om_rule_rec => l_rule_rec
320 , X_return_status => x_return_status
321 , X_msg_cont => l_msg_cont
322 , X_msg_data => l_msg_data
323 );
324 IF l_rule_rec.order_notification = 'Y' THEN
325 l_notify := 1;
326 END IF;
327 /* based on the p_batch_action code, do the appropriate calls */
328 IF p_batch_action = 'COMPLETE' THEN
329 /* action at batch side is either ADDC or UPDP with completed_ind = 1
330 * We will create OMSO allocations based on the reservations */
331 GML_BATCH_OM_RES_PVT.create_allocations
332 (
333 P_batch_line_rec => p_batch_line_rec
334 , P_gme_om_rule_rec => l_rule_rec
335 , P_Gme_trans_row => p_gme_trans_row
336 , X_return_status => x_return_status
337 , X_msg_cont => l_msg_cont
338 , X_msg_data => l_msg_data
339 );
340 /* if rule says so
341 pick confirm is done per batch not per trans
342 Moved pick confirm to UPDP where UPDP is called from GME per batch consolidation
343 */
344 g_prod_yielded := 1;
345 /* set g_not_to_delete to 1 if convertion is waiting for the lot status */
346 END IF;
347 IF p_batch_action = 'DELETE' THEN
348 /* actions at the batch side could be
349 * 1) cancel-terminate batch -- batch level, above
350 * 2) reserval batch at the trans level
351 */
352 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations...... IF p_batch_action = DELETE');
353 IF l_rule_rec.order_notification = 'Y' THEN
354 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....Calling GML_BATCH_OM_RES_PVT.notify_CSR');
355 GML_BATCH_OM_RES_PVT.notify_CSR
356 (
357 P_Batch_trans_id => p_batch_line_rec.trans_id
358 , P_action_code => 'CANCEL'
359 , X_return_status => x_return_status
360 , X_msg_cont => l_msg_cont
361 , X_msg_data => l_msg_data
362 );
363 END IF;
364 IF p_batch_line_rec.trans_id is not null THEN
365 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....IF p_gme_trans_row.trans_id is not null');
366 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....Calling GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans');
367 GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans
368 (
369 P_Batch_trans_id => p_batch_line_rec.trans_id
370 , X_return_status => x_return_status
371 , X_msg_cont => l_msg_cont
372 , X_msg_data => l_msg_data
373 );
374 END IF;
375 return;
376 END IF;
377 IF p_batch_action = 'NOTIFY_CSR' THEN
378 /* p_batch_line_rec.trans_id will be passed as the from trans_id
379 * p_gme_trans_row.trans_id will be passed as the new (to be updated) trans_id
380 */
381 IF p_batch_line_rec.trans_id is not null THEN
382 GMI_RESERVATION_UTIL.println('Fetch old gme trans '||p_batch_line_rec.trans_id);
383 Open get_trans_row(p_batch_line_rec.trans_id);
384 Fetch get_trans_row
385 Into l_old_gme_trans_row;
386 Close get_trans_row;
387 END IF;
388 /* if p_batch_line_rec.batch_line_id is passed, meaning UPDP at GME
389 * 1) planned qty is changed
390 * 2) update pending to complete
391 */
392 IF p_batch_line_rec.batch_line_id is not null THEN
393 GMI_RESERVATION_UTIL.println('batch line level');
394 /* 1) Reduce planned qty or any pending transactions change
395 -- no actions for increasing qty
396 * the trans_row would be pending
397 */
398 IF p_gme_trans_row.trans_id is null THEN -- Pending transactions
399 GMI_RESERVATION_UTIL.println('UPDP, pending trans is changed');
400 For whse_rec in check_res_whse(p_batch_line_rec.batch_line_id) LOOP
401 l_whse_code := whse_rec.whse_code;
402 Open get_res_qty(l_batch_line_id, l_whse_code);
403 Fetch get_res_qty
404 Into l_res_qty, l_res_qty2;
405 Close get_res_qty;
406 GMI_RESERVATION_UTIL.println('UPDP, total res qty '|| l_res_qty);
407
408 Open get_planned_qty(l_batch_line_id, l_whse_code);
409 Fetch get_planned_qty
410 Into l_planned_qty, l_planned_qty2;
411 Close get_planned_qty;
412
413 GMI_RESERVATION_UTIL.println('UPDP, total planned qty '|| nvl(l_planned_qty,0));
414 GMI_RESERVATION_UTIL.println(' global g_not_to_notify '||GML_GME_API_PVT.g_not_to_notify);
415 IF (l_res_qty > nvl(l_planned_qty,0) * (1+ l_rule_rec.allocation_tolerance/100))
416 and l_notify = 1
417 and GML_GME_API_PVT.g_not_to_notify <> 1
418 THEN
419 /* pending qty has been reduced
420 * check the tolerance
421 */
422 GMI_RESERVATION_UTIL.println('UPDP, planned qty is changed');
423 GMI_RESERVATION_UTIL.println('Process_om_reservations...UPDP Pending Qty is reduced');
424 GMI_RESERVATION_UTIL.println('Process_om_reservations...Pending Qty is reduced Calling GML_BATCH_OM_RES_PVT.notify_CSR');
425 GML_BATCH_OM_RES_PVT.notify_CSR
426 (
427 P_batch_line_id => p_batch_line_rec.batch_line_id
428 , P_whse_code => l_whse_code
429 , P_action_code => 'REDUCE_PLANNED_QTY'
430 , X_return_status => x_return_status
431 , X_msg_cont => l_msg_cont
432 , X_msg_data => l_msg_data
433 );
434 /* if planned qty becomes 0, delete all the res for this whse */
435 GMI_RESERVATION_UTIL.println(' global g_not_to_delete '||GML_GME_API_PVT.g_not_to_delete);
436 IF (nvl(l_planned_qty,0) <= 0 AND GML_GME_API_PVT.g_not_to_delete = 0) THEN
437 GML_BATCH_OM_RES_PVT.cancel_res_for_batch_line
438 (
439 P_Batch_line_id => p_batch_line_rec.batch_line_id
440 , P_whse_code => l_whse_code
441 , X_return_status => x_return_status
442 , X_msg_cont => l_msg_cont
443 , X_msg_data => l_msg_data
444 ) ;
445 END IF;
446 END IF;
447 END LOOP;
448 END IF;
449 IF p_gme_trans_row.trans_id is not null
450 and l_old_gme_trans_row.completed_ind = 0
451 and p_gme_trans_row.completed_ind = 1
452 THEN
453 GMI_RESERVATION_UTIL.println('UPDP, update pending to complete');
454 GML_BATCH_OM_RES_PVT.create_allocations
455 (
456 P_batch_line_rec => p_batch_line_rec
457 , P_gme_om_rule_rec => l_rule_rec
458 , P_Gme_trans_row => p_gme_trans_row
459 , X_return_status => x_return_status
460 , X_msg_cont => l_msg_cont
461 , X_msg_data => l_msg_data
462 );
463
464 g_prod_yielded := 1;
465
466 END IF;
467
468 GMI_RESERVATION_UTIL.println(' Global value g_prod_yielded '||g_prod_yielded);
469 /* Pick confirm is here after consolidation at GME side */
470 IF l_rule_rec.auto_pick_confirm = 'Y' AND g_prod_yielded = 1 THEN
471 GMI_RESERVATION_UTIL.println('Product yielded. Now Pickconfirm');
472 /* get the mo line id for the source line */
473 GML_BATCH_OM_RES_PVT.pick_confirm
474 (
475 P_batch_line_rec => p_batch_line_rec
476 , P_Gme_trans_row => p_gme_trans_row
477 , X_return_status => x_return_status
478 , X_msg_cont => l_msg_cont
479 , X_msg_data => l_msg_data
480 );
481 END IF;
482 END IF; -- batch line level
483 IF p_batch_line_rec.trans_id is not null
484 and p_gme_trans_row.trans_id is not null THEN
485 GMI_RESERVATION_UTIL.println('transaction level');
486 IF l_old_gme_trans_row.completed_ind = 1
487 and l_new_gme_trans_row.completed_ind = 1
488 THEN
489 GMI_RESERVATION_UTIL.println('UPDC, update the completed transaction');
490 /* 1) if qty is increased, new OMSO should be created for the newly added qty
491 */
492 GMI_RESERVATION_UTIL.println('UPDC, old trans_qty '|| l_old_gme_trans_row.trans_qty);
493 GMI_RESERVATION_UTIL.println('UPDC, new trans_qty '|| l_new_gme_trans_row.trans_qty);
494 GMI_RESERVATION_UTIL.println('UPDC, old lot_id '|| l_old_gme_trans_row.lot_id);
495 GMI_RESERVATION_UTIL.println('UPDC, new lot_id '|| l_new_gme_trans_row.lot_id);
496 GMI_RESERVATION_UTIL.println('UPDC, old location '|| l_old_gme_trans_row.location);
497 GMI_RESERVATION_UTIL.println('UPDC, new location '|| l_new_gme_trans_row.location);
498 GMI_RESERVATION_UTIL.println('UPDC, old whse_code '|| l_old_gme_trans_row.whse_code);
499 GMI_RESERVATION_UTIL.println('UPDC, new whse_code '|| l_new_gme_trans_row.whse_code);
500 IF abs(l_new_gme_trans_row.trans_qty) > abs(l_old_gme_trans_row.trans_qty) THEN
501 GMI_RESERVATION_UTIL.println(' New qty added');
502 Open get_total_OMSO(l_old_gme_trans_row.trans_id);
503 Fetch get_total_OMSO Into l_trans_qty, l_trans_qty2;
504 Close get_total_OMSO;
505 GMI_RESERVATION_UTIL.println(' Total OMSO trans_qty '||l_trans_qty);
506 GMI_RESERVATION_UTIL.println(' Total OMSO trans_qty2 '||l_trans_qty2);
507 l_new_gme_trans_row.trans_qty := -1 * (abs(l_new_gme_trans_row.trans_qty)
508 - abs(l_trans_qty));
509 l_new_gme_trans_row.trans_qty2 := -1 * (abs(l_new_gme_trans_row.trans_qty2)
510 - abs(l_trans_qty2));
511 GML_BATCH_OM_RES_PVT.create_allocations
512 (
513 P_batch_line_rec => p_batch_line_rec
514 , P_gme_om_rule_rec => l_rule_rec
515 , P_Gme_trans_row => l_new_gme_trans_row
516 , X_return_status => x_return_status
517 , X_msg_cont => l_msg_cont
518 , X_msg_data => l_msg_data
519 );
520 /* update history ? */
521 /* increasing qty is to create neg orginal qty and create new trans with the new qty*/
522 l_update_history := 1;
523 g_prod_yielded := 1;
524
525 ELSIF abs(l_new_gme_trans_row.trans_qty) < abs(l_old_gme_trans_row.trans_qty) THEN
526 /* qty is reduced, backflash */
527 GMI_RESERVATION_UTIL.println(' Process_om_reservations completed batch qty reduced');
528 /* notify CSR */
529 l_notify := 1;
530 GMI_RESERVATION_UTIL.println(' Process_om_reservations completed batch qty reduced calling GML_BATCH_OM_RES_PVT.notify_CSR');
531 GML_BATCH_OM_RES_PVT.notify_CSR
532 (
533 P_batch_trans_id => p_batch_line_rec.trans_id
534 , P_action_code => 'REDUCE_QTY_ON_COMPLETED_BATCH'
535 , X_return_status => l_return_status
536 , X_msg_cont => l_msg_cont
537 , X_msg_data => l_msg_data
538 );
539
540 /* decreasing qty is to create neg orginal qty and create new trans with the new qty*/
541 l_update_history := 1;
542 END IF;
543
544 /* 2) lot_id is changed
545 */
546 IF l_new_gme_trans_row.lot_id <> l_old_gme_trans_row.lot_id THEN
547 GMI_RESERVATION_UTIL.println('Lot is changed from '|| l_old_gme_trans_row.lot_id);
548 GMI_RESERVATION_UTIL.println(' to '|| l_new_gme_trans_row.lot_id);
549 For trans_rec in get_trans_id(l_batch_trans_id) LOOP
550 l_trans_id := trans_rec.trans_id;
551 l_tran_rec.trans_id := l_trans_id;
552 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_tran_rec, l_tran_rec ) THEN
553 l_tran_rec.lot_id := l_new_gme_trans_row.lot_id;
554 GMI_RESERVATION_UTIL.println(' Changing lot_id for trans_id '|| l_trans_id);
555 GMI_TRANS_ENGINE_PUB.update_pending_transaction
556 (p_api_version => 1.0,
557 p_init_msg_list => FND_API.G_TRUE,
558 p_commit => FND_API.G_FALSE,
559 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
560 p_tran_rec => l_tran_rec,
561 x_tran_row => l_tran_row,
562 x_return_status => x_return_status,
563 x_msg_count => l_msg_cont,
564 x_msg_data => l_msg_data
565 );
566 IF x_return_status <> fnd_api.g_ret_sts_success Then
567 GMI_reservation_Util.PrintLn('update complete trans, alloc creation error');
568 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
569 FND_MESSAGE.Set_Token('WHERE', 'Check rules');
570 FND_MSG_PUB.ADD;
571 END IF;
572 /* no need
573 l_dft_tran_rec.line_id := l_tran_rec.line_id;
574 l_dft_tran_rec.trans_id := null;
575 GMI_RESERVATION_UTIL.balance_default_lot
576 ( p_ic_default_rec => l_dft_tran_rec
577 , p_opm_item_id => l_dft_tran_rec.item_id
578 , x_return_status => x_return_status
579 , x_msg_count => x_msg_cont
580 , x_msg_data => x_msg_data
581 );
582 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
583 THEN
584 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
585 END IF;
586 */
587 l_update_history := 1;
588 END IF;
589 END LOOP;
590 l_notify := 1;
591 END IF;
592 /* 3) location is changed
593 */
594 IF l_new_gme_trans_row.location <> l_old_gme_trans_row.location THEN
595 GMI_RESERVATION_UTIL.println('Location is changed from '|| l_old_gme_trans_row.location);
596 GMI_RESERVATION_UTIL.println(' to '|| l_new_gme_trans_row.location);
597 For trans_rec in get_trans_id(l_batch_trans_id) LOOP
598 l_trans_id := trans_rec.trans_id;
599 l_tran_rec.trans_id := l_trans_id;
600 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_tran_rec, l_tran_rec ) THEN
601 l_tran_rec.location := l_new_gme_trans_row.location;
602 GMI_RESERVATION_UTIL.println(' Changing location for trans_id '|| l_trans_id);
603 GMI_TRANS_ENGINE_PUB.update_pending_transaction
604 (p_api_version => 1.0,
605 p_init_msg_list => FND_API.G_TRUE,
606 p_commit => FND_API.G_FALSE,
607 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
608 p_tran_rec => l_tran_rec,
609 x_tran_row => l_tran_row,
610 x_return_status => x_return_status,
611 x_msg_count => l_msg_cont,
612 x_msg_data => l_msg_data
613 );
614 IF x_return_status <> fnd_api.g_ret_sts_success Then
615 GMI_reservation_Util.PrintLn('update complete trans, alloc creation error');
616 FND_MESSAGE.SET_NAME('GMI','GMI_QTY_RSV_NOT_FOUND');
617 FND_MESSAGE.Set_Token('WHERE', 'Check rules');
618 FND_MSG_PUB.ADD;
619 END IF;
620 END IF;
621 END LOOP;
622 l_notify := 1;
623 l_update_history := 1;
624 END IF;
625 /* 4) whse_code is changed
626 */
627 IF l_new_gme_trans_row.whse_code <> l_old_gme_trans_row.whse_code THEN
628 GMI_RESERVATION_UTIL.println('Process_Om_reservations .....Whse is Changed');
629 GMI_RESERVATION_UTIL.println('Process_Om_reservations .....Completed batch tran is deleted');
630 GMI_RESERVATION_UTIL.println('Process_Om_reservations .........Before deleting OMSO trans');
631 GMI_RESERVATION_UTIL.println('Process_om_Reservations.... First Notify CSR');
632 l_notify := 1;
633 /* Send a notification */
634 GML_BATCH_OM_RES_PVT.notify_CSR
635 (
636 P_batch_trans_id => l_batch_trans_id
637 , P_action_code => 'WHSE_CHANGED'
638 , X_return_status => x_return_status
639 , X_msg_cont => l_msg_cont
640 , X_msg_data => l_msg_data
641 );
642
643 GMI_RESERVATION_UTIL.println(' Deleting OMSO trans');
644 GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans
645 (
646 P_Batch_trans_id => l_batch_trans_id
647 , X_return_status => X_return_status
648 , X_msg_cont => l_msg_cont
649 , X_msg_data => l_msg_data
650 );
651
652 /* we don't change the history because old trans are deleted and still
653 * linked with the old gme trans */
654 END IF;
655
656 /* 5) trans_date is changed
657 * check the scheduled ship date, if over, notify
658 */
659
660 /* balance the default lot */
661 /* notify the CSR */
662 /*
663 IF l_notify = 1 THEN
664 GMI_RESERVATION_UTIL.println('Notifications to CSR ');
665 GML_BATCH_OM_RES_PVT.notify_CSR
666 (
667 P_batch_trans_id => p_batch_line_rec.trans_id
668 , X_return_status => x_return_status
669 , X_msg_cont => l_msg_cont
670 , X_msg_data => l_msg_data
671 );
672 END IF;
673 */
674 /* the old trans is deleted and replaced by the new trans
675 * need to update the history table to keep the link alive
676 */
677 /* just do the update */
678 /* update gml_batch_so_alloc_history
679 Set batch_trans_id = l_new_gme_trans_row.trans_id
680 Where batch_trans_id = l_old_gme_trans_row.trans_id
681 and nvl(trans_id,0) <> 0
682 and delete_mark = 0
683 ;
684 */
685 -- insert new record with new info
686 IF l_update_history = 1 THEN
687 for his_rec in get_history_id (l_old_gme_trans_row.trans_id) LOOP
688 /* delete the old history record */
689 update gml_batch_so_alloc_history
690 Set delete_mark = 1
691 Where alloc_rec_id = his_rec.alloc_rec_id
692 ;
693 GMI_RESERVATION_UTIL.println(' delete history.alloc_rec_id '||his_rec.alloc_rec_id);
694 GMI_RESERVATION_UTIL.println(' old batch_trans_id '||l_old_gme_trans_row.trans_id);
695 l_history_rec.alloc_rec_id := his_rec.alloc_rec_id;
696 GML_BATCH_OM_UTIL.query_alloc_history
697 (
698 P_alloc_history_rec => l_history_rec
699 , X_return_status => x_return_status
700 , X_msg_cont => l_msg_cont
701 , X_msg_data => l_msg_data
702 ) ;
703 GMI_RESERVATION_UTIL.println(' new history.batch_trans_id '||l_history_rec.batch_trans_id);
704 l_history_rec.alloc_rec_id := null;
705 l_history_rec.batch_trans_id := l_new_gme_trans_row.trans_id;
706 l_history_rec.location := l_new_gme_trans_row.location;
707 l_history_rec.lot_id := l_new_gme_trans_row.lot_id;
708 GMI_RESERVATION_UTIL.println(' for batch_res_id '||l_history_rec.batch_res_id);
709 GML_BATCH_OM_UTIL.insert_alloc_history
710 (
711 P_alloc_history_rec => l_history_rec
712 , X_return_status => x_return_status
713 , X_msg_cont => l_msg_cont
714 , X_msg_data => l_msg_data
715 );
716 END LOOP;
717 END IF;
718 END IF; -- UPDC changed trans is still a complted trans
719 IF l_old_gme_trans_row.completed_ind = 1
720 and l_new_gme_trans_row.completed_ind = 0
721 THEN
722 /* uncomplete the completed trans */
723 GMI_RESERVATION_UTIL.println('UPDP, update complete to pending');
724 GML_BATCH_OM_RES_PVT.cancel_alloc_for_trans
725 (
726 P_Batch_trans_id => l_old_gme_trans_row.trans_id
727 , X_return_status => X_return_status
728 , X_msg_cont => l_msg_cont
729 , X_msg_data => l_msg_data
730 );
731 IF l_rule_rec.order_notification = 'Y' THEN
732 GMI_RESERVATION_UTIL.PrintLn(' In Process_om_reservations....Canceling the PROD transaction');
733 GML_BATCH_OM_RES_PVT.notify_CSR
734 (
735 P_Batch_trans_id => p_batch_line_rec.trans_id
736 , P_action_code => 'CANCEL'
737 , X_return_status => x_return_status
738 , X_msg_cont => l_msg_cont
739 , X_msg_data => l_msg_data
740 );
741 END IF;
742 END IF; -- update complted to pending
743 END IF; -- trans level
744 END IF;
745
746 END process_om_reservations;
747
748 END GML_GME_API_PVT;