[Home] [Help]
PACKAGE BODY: APPS.GMI_SHIPPING_UTIL
Source
1 PACKAGE BODY GMI_Shipping_Util AS
2 /* $Header: GMIUSHPB.pls 120.0 2005/05/25 15:53:56 appldev noship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIUSHPS.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains private utilities relating to OPM |
13 | shipping. |
14 | |
15 | HISTORY |
16 | B1495550 15-Nov-2000 hwahdani Changed NONE by IC$DEFAULT_LOCT |
17 | B1504749 21-Nov-2000 odaboval Changed the CLOSE cursor management. |
18 | B1504749 05-Dec-2000 odaboval Added where clause on released_status. |
19 | B1577035 12-Jan-2001 KYH Set staged_ind = 0 in backorder scenario |
20 | B1503309 27-MAR-2001 Hverddin Changed Logic to calculate |
21 | l_reduction_factor |
22 | B172755 04/09/01 HW Need to add sublot_no is NULL to sql statement |
23 | when sublot_number is null because sublot_no is |
24 | part of the key |
25 | B1826752 06/08/01 HW Issue with clearing out lot information |
26 | when updating wsh_delivery_details. |
27 | in GMI_APPLY_BACKORDER_UPDATES |
28 | B1854224 06/27/01 HW close the comment for bug 1826752 in |
29 | GMI_APPLY_BACKORDER_UPDATES |
30 | B2547509 12/06/01 Uday Phadtare Adding the message returned by |
31 | Inventory Engine in the log file after calling |
32 | UPDATE_PENDING_TO_COMPLETED. |
33 | Also added code to change the trans_date to sysdate |
34 | if the date is in closed period and also undelete |
35 | the transaction if it is deleted by mistake. |
36 | V. Ajay Kumar 09-JAN-2003 BUG#2736088 |
37 | Removed the reference to "apps". |
38 | B2775197 01/29/03 Uday Phadtare Do not complete the transaction if the |
39 | inventory is going negative and the profile, allow |
40 | neg inv is not equal to 1. |
41 | |
42 | Hasan Wahdani 10/2003 3206991 Added an overloading procedure with a |
43 | different parameters. WSH module passes a table of |
44 | trips stops in WSH.J and prior to that, only stop_id |
45 | was being passed. |
46 | |
47 | Hasan Wahdani 12/2003 Removed the Overloading procedure and replaced it |
48 | with GMI_UPDATE_ORDER.process_order (GMIUSITB.pls) |
49 | due to compilation issues |
50 | |
51 | Hasan Wahdani 02/2004 BUG: 3434884 GSCC issue to get proper Schema |
52 | Names. |
53 | Hasan Wahdani 02/2004 BUG: 3385851 Added for Pushkar |
54 | Hasan Wahdani 02/2004 BUG: HW 3388186 Added a new procedure |
55 | UPDATE_NEW_LINE_DETAIL_ID. See comments |
56 | by procedure |
57 +=========================================================================+
58 */
59 PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
60 ( p_shipping_line IN wsh_delivery_details%ROWTYPE
61 , p_actual_ship_date IN DATE
62 , x_return_status OUT NOCOPY VARCHAR2
63 , x_msg_count OUT NOCOPY NUMBER
64 , x_msg_data OUT NOCOPY VARCHAR2
65 );
66
67 FUNCTION GMI_TRANS_DATE(p_trans_date date, p_orgn_code VARCHAR2, p_whse_code VARCHAR2) RETURN DATE;
68
69 FUNCTION INVENTORY_GOING_NEG(p_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec) return BOOLEAN;
70
71 procedure check_loct_ctl (
72 p_inventory_item_id IN NUMBER
73 ,p_mtl_organization_id IN NUMBER
74 ,x_ctl_ind OUT NOCOPY VARCHAR2) ;
75
76 INVENTORY_NEG_WARNING EXCEPTION;
77
78 PROCEDURE GMI_CREATE_BACKORDER_TXN
79 ( p_shipping_line IN wsh_delivery_details%ROWTYPE
80 , x_return_status OUT NOCOPY VARCHAR2
81 , x_msg_count OUT NOCOPY NUMBER
82 , x_msg_data OUT NOCOPY VARCHAR2
83 )
84 IS
85 l_old_transaction_row ic_tran_pnd%ROWTYPE;
86 l_new_transaction_row ic_tran_pnd%ROWTYPE;
87 l_line_id ic_tran_pnd.line_id%TYPE;
88 l_item_id ic_tran_pnd.item_id%TYPE;
89 l_location ic_tran_pnd.location%TYPE;
90 l_lot_id ic_tran_pnd.lot_id%TYPE;
91 l_reduction_factor NUMBER;
92 l_delta_trans_qty2 NUMBER;
93 l_trans_id NUMBER;
94 l_staged_ind NUMBER;
95 l_lock_status BOOLEAN;
96
97 CURSOR default_transaction_c IS
98 SELECT trans_id, staged_ind
99 FROM ic_tran_pnd
100 WHERE doc_type='OMSO'
101 AND line_id = p_shipping_line.source_line_id
102 -- AND item_id = l_item_id -- REMOVED for bug 3403418
103 AND lot_id = 0
104 AND location = l_location
105 AND completed_ind = 0
106 AND delete_mark = 0
107 ORDER BY staged_ind;
108
109 CURSOR get_opm_transaction_c
110 IS
111 SELECT trans_id
112 FROM ic_tran_pnd
113 WHERE doc_type='OMSO'
114 AND line_id = p_shipping_line.source_line_id
115 -- AND item_id = l_item_id -- REMOVED for bug 3403418
116 AND lot_id = l_lot_id
117 AND location = l_location
118 AND completed_ind = 0
119 AND staged_ind = 1
120 AND delete_mark = 0
121 ORDER BY trans_id;
122 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
123 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
124
125
126 BEGIN
127
128 /* Standard Start OF API savepoint */
129
130 SAVEPOINT process_backorders;
131 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
132
133
134 GMI_RESERVATION_UTIL.println('Start Of OPM BackOrder Routine');
135
136 /* Try and retrieve the original transaction. To do this we need */
137 /* to determine a number of keys so that we can locate the correct */
138 /* row in ic_tran_pnd. If any of these retrievals fails there is no */
139 /* point in continuing, so let the exception raised take over. */
140
141 GMI_reservation_Util.PrintLn('Find OPM Item ID');
142
143 SELECT iim.item_id INTO l_item_id
144 FROM ic_item_mst iim,
145 mtl_system_items msi
146 WHERE msi.inventory_item_id = p_shipping_line.inventory_item_id
147 AND msi.organization_id = p_shipping_line.organization_id
148 AND msi.segment1 = iim.item_no;
149
150 IF p_shipping_line.locator_id IS NULL
151 THEN
152 /* hwahdani BUG#:1495550 get proper value of default location from profile */
153 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
154 ELSE
155 SELECT location INTO l_location
156 FROM ic_loct_mst
157 WHERE inventory_location_id = p_shipping_line.locator_id;
158 END IF;
159
160 IF p_shipping_line.lot_number IS NULL
161 THEN
162 l_lot_id := 0;
163 ELSIF p_shipping_line.sublot_number IS NULL
164 -- HW BUG#:1727553 added sublot_no is NULL
165 THEN
166 SELECT lot_id INTO l_lot_id
167 FROM ic_lots_mst
168 WHERE item_id = l_item_id
169 AND lot_no = p_shipping_line.lot_number
170 AND sublot_no IS NULL ;
171 ELSE
172 SELECT lot_id INTO l_lot_id
173 FROM ic_lots_mst
174 WHERE item_id = l_item_id
175 AND lot_no = p_shipping_line.lot_number
176 AND sublot_no = p_shipping_line.sublot_number;
177 END IF;
178
179 /* With the above retrievals successfully done we can */
180 /* try to locate the transaction we need. Again, if this fails */
181 /* we cannot proceed so let the exception raised take over. */
182
183 GMI_reservation_Util.PrintLn('Find OPM Original Transaction');
184
185 /* Could Select More than one Line With Matching Keys */
186 /* We do not care which matching record we select */
187 /* since we are ordering by trans_id, therefore */
188 /* Exit after First Select. */
189
190 GMI_RESERVATION_UTIL.println('LINE_ID => ' || p_shipping_line.source_line_id);
191 GMI_RESERVATION_UTIL.println('ITEM_ID => ' || l_item_id);
192 GMI_RESERVATION_UTIL.println('LOT_ID => ' || l_lot_id);
193 GMI_RESERVATION_UTIL.println('Location => ' || l_location);
194
195 OPEN get_opm_transaction_c;
196 LOOP
197 FETCH get_opm_transaction_c INTO l_old_transaction_rec.trans_id;
198 IF get_opm_transaction_c%NOTFOUND THEN
199 /* B1504749, 21-Nov-2000 odaboval : added CLOSE cursor here. */
200 --CLOSE get_opm_transaction_c;
201
202 GMI_RESERVATION_UTIL.println('OPM Transaction Not Found');
203 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205 EXIT; /* Exit after First Select */
206 END LOOP;
207
208 --CLOSE get_opm_transaction_c;
209
210 GMI_RESERVATION_UTIL.println('TRANS_ID => ' || l_old_transaction_rec.trans_id);
211 GMI_RESERVATION_UTIL.println('Cycle_count => ' || p_shipping_line.cycle_count_quantity);
212
213 IF get_opm_transaction_c%NOTFOUND THEN
214 CLOSE get_opm_transaction_c;
215 GMI_RESERVATION_UTIL.println('l_return_status => ' || x_return_status);
216 ELSE
217 CLOSE get_opm_transaction_c;
218 /* original hwahdani IF ( l_lot_id = 0 and l_location is NULL) THEN */
219 /* BUG#:1495550 check if l_location is not NULL */
220 IF ( l_lot_id = 0 and l_location = FND_PROFILE.VALUE('IC$DEFAULT_LOCT'))
221 THEN
222 GMI_reservation_Util.PrintLn('Default Transaction Is Not LOT/LOC controlled');
223 GMI_reservation_Util.PrintLn('Action is => Revert staged_ind to zero');
224 /* BUG 1577035 BEGIN Set staged_ind = 0 in backorder scenario
225 ============================================================= */
226 /* BUG 1575873 the qty becomes 0 when back order is created, the
227 default qty should be the cycle count qty */
228 UPDATE ic_tran_pnd
229 SET staged_ind = 0,
230 trans_qty = -1 * p_shipping_line.cycle_count_quantity,
231 trans_qty2 = -1 * p_shipping_line.cycle_count_quantity2
232 WHERE trans_id = l_old_transaction_rec.trans_id;
233
234 IF SQL%NOTFOUND THEN
235 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
236 FND_MESSAGE.Set_Token('BY_PROC', 'Update default staged indicator');
237 FND_MESSAGE.Set_Token('WHERE', 'GMI_Create_Backorder_Txn');
238 FND_MSG_PUB.Add;
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241 /* BUG 1577035 END
242 ================== */
243 ELSE
244
245 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
246 (l_old_transaction_rec, l_old_transaction_rec )
247 THEN
248 /* We have the transaction we need. */
249 /* Before Completing any transactions lets Lock The Rows. */
250 /* Calling OPM Lock Inventory Routine. */
251 -- PK Bug 3527599 No need to lock IC_LOCT_INV when deleting pending txn.
252
253 IF ABS(l_old_transaction_rec.trans_qty) = p_shipping_line.cycle_count_quantity
254 THEN
255 GMI_RESERVATION_UTIL.println('BackOrder Full Staged Qty');
256
257 /* Delete Original Tranaction */
258
259 GMI_RESERVATION_UTIL.println('Delete Original Transaction');
260
261 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
262 ( 1
263 , FND_API.G_FALSE
264 , FND_API.G_FALSE
265 , FND_API.G_VALID_LEVEL_FULL
266 , l_old_transaction_rec
267 , l_old_transaction_row
268 , x_return_status
269 , x_msg_count
270 , x_msg_data
271 );
272
273 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
274 THEN
275 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278
279 /* I am Assuming That There Will Always Be A Default Transaction. */
280 /* So Lets Get It. */
281
282 /* Set the value of location for Default Transaction */
283 /* Needs to be replaced with profile check in future. */
284
285 /* hwahdani BUG#:1495550 get proper value of default location from profile */
286 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
287
288 OPEN default_transaction_c;
289 FETCH default_transaction_c INTO l_trans_id, l_staged_ind;
290 IF default_transaction_c%NOTFOUND THEN
291 Close default_transaction_c;
292 GMI_RESERVATION_UTIL.println('Default Transaction Not Found');
293 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294 END IF;
295 Close default_transaction_c;
296
297 /* Set Trans Id To fetch */
298
299 l_old_transaction_rec.trans_id := l_trans_id;
300
301 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
302 (l_old_transaction_rec, l_old_transaction_rec )
303 THEN
304 GMI_RESERVATION_UTIL.println('Found Transaction ID => '|| l_old_transaction_rec.trans_id);
305
306 l_new_transaction_rec := l_old_transaction_rec;
307 l_new_transaction_rec.trans_qty := -1 * (p_shipping_line.cycle_count_quantity + ABS(l_old_transaction_rec.trans_qty));
308 l_new_transaction_rec.trans_qty2 := -1 * (p_shipping_line.cycle_count_quantity2 + ABS(l_old_transaction_rec.trans_qty2));
309
310 PRINT_DEBUG (l_new_transaction_rec,' Update Default Transaction');
311
312 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
313 ( 1
314 , FND_API.G_FALSE
315 , FND_API.G_FALSE
316 , FND_API.G_VALID_LEVEL_FULL
317 , l_new_transaction_rec
318 , l_new_transaction_row
319 , x_return_status
320 , x_msg_count
321 , x_msg_data
322 );
323
324 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
325 THEN
326 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
328 END IF;
329 END IF;
330
331 ELSE
332
333 GMI_RESERVATION_UTIL.println('Start Of OPM Partial BackOrder Routine');
334
335 /* We are creating a partial back order. In a similar way */
336 /* to above, reverse out the existing allocations and then */
337 /* post a new one with amounts reduced by the back order */
338 /* quantity. */
339
340
341
342 GMI_RESERVATION_UTIL.println('Backout Original Staged Qty ' || l_old_transaction_rec.trans_qty);
343
344 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
345 ( 1
346 , FND_API.G_FALSE
347 , FND_API.G_FALSE
348 , FND_API.G_VALID_LEVEL_FULL
349 , l_old_transaction_rec
350 , l_old_transaction_row
351 , x_return_status
352 , x_msg_count
353 , x_msg_data
354 );
355
356 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
357 THEN
358 GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
360 END IF;
361
362 /* Reduce the existing transaction quantity by the amount */
363 /* being back-ordered (any trans_qty2 figures are reduced */
364 /* in proportion) and then write out the new transaction for */
365 /* the amount being Left At Staging. */
366
367
368 l_new_transaction_rec := l_old_transaction_rec;
369
370 /* Bug 1503309 Start New l_reduction_factor Logic */
371
372 IF l_new_transaction_rec.trans_qty2 is NULL THEN
373 l_reduction_factor := NULL;
374 ELSE
375 l_reduction_factor := ABS(l_new_transaction_rec.trans_qty2)/ ABS(l_new_transaction_rec.trans_qty);
376 END IF;
377 GMI_RESERVATION_UTIL.println('Reduction Factor => ' || l_reduction_factor);
378 l_new_transaction_rec.trans_qty := -1 * (ABS(l_new_transaction_rec.trans_qty) - p_shipping_line.cycle_count_quantity);
379 l_new_transaction_rec.trans_qty2 := -1 * (ABS(l_new_transaction_rec.trans_qty)) * l_reduction_factor;
380 /* Bug 1503309 End of New l_reduction_factor Logic */
381
382 /* NC Added line_detail_id Bug#1675561 */
383 l_new_transaction_rec.line_detail_id := p_shipping_line.delivery_detail_id;
384
385 GMI_RESERVATION_UTIL.println('line_detail_id ' || p_shipping_line.delivery_detail_id);
386 GMI_RESERVATION_UTIL.println('Back Qty ' || p_shipping_line.cycle_count_quantity);
387 GMI_RESERVATION_UTIL.println('Back Qty2 ' || p_shipping_line.cycle_count_quantity2);
388 GMI_RESERVATION_UTIL.println('Write New Staged Qty ' || l_new_transaction_rec.trans_qty);
389 GMI_RESERVATION_UTIL.println('Write New Staged Qty2 ' || l_new_transaction_rec.trans_qty2);
390
391 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
392 ( 1
393 , FND_API.G_FALSE
394 , FND_API.G_FALSE
395 , FND_API.G_VALID_LEVEL_FULL
396 , l_new_transaction_rec
397 , l_new_transaction_row
398 , x_return_status
399 , x_msg_count
400 , x_msg_data
401 );
402
403 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
404 THEN
405 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 END IF;
408
409 /* We now need to sort out the transaction for the default lot. */
410 /* There are three cases to consider and the cursor will retrieve */
411 /* the characteristics of the row we need (if it exists) */
412
413 /* a) If an unstaged default lot transaction exists, increase the */
414 /* quantities in it by the back order quantities */
415 /* otherwise */
416 /* b) If a staged default lot transaction exists, create a new */
417 /* unstaged transaction for the backorder quantities */
418 /* otherwise */
419 /* c) If no default transaction exists, create the same transaction */
420 /* as would have been created in 'b'. */
421
422 /* Set the value of location for Default Transaction */
423 /* Needs to be replaced with profile check in future. */
424
425 /* hwahdani BUG#:1495550 get proper value of default location from profile */
426 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
427
428 OPEN default_transaction_c;
429 FETCH default_transaction_c INTO l_trans_id, l_staged_ind;
430 IF default_transaction_c%NOTFOUND
431 OR l_staged_ind = 1
432 THEN
433 CLOSE default_transaction_c;
434 /* We need to create the default, unstaged row */
435
436 gmi_reservation_util.println('Creating Default Row Transaction For Back Ordered Qty');
437
438 l_new_transaction_rec.trans_id := NULL;
439 l_new_transaction_rec.trans_qty := - p_shipping_line.cycle_count_quantity;
440 l_new_transaction_rec.trans_qty2:= - p_shipping_line.cycle_count_quantity2;
441 l_new_transaction_rec.lot_id := 0;
442 l_new_transaction_rec.location := 'NONE';
443 l_new_transaction_rec.staged_ind := 0;
444
445 PRINT_DEBUG (l_old_transaction_rec,'Create Default Back Order');
446
447 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
448 ( 1
449 , FND_API.G_FALSE
450 , FND_API.G_FALSE
451 , FND_API.G_VALID_LEVEL_FULL
452 , l_new_transaction_rec
453 , l_new_transaction_row
454 , x_return_status
455 , x_msg_count
456 , x_msg_data
457 );
458
459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
460 THEN
461 /* B1504749, 21-Nov-2000 odaboval : removed CLOSE cursor here. */
462 /* CLOSE default_transaction_c; */
463
464 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
465 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
466 END IF;
467
468 ELSE
469 CLOSE default_transaction_c;
470 /* We need to increase the amounts in the existing transaction */
471 l_old_transaction_rec.trans_id := l_trans_id;
472
473 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
474 (l_old_transaction_rec, l_old_transaction_rec )
475 THEN
476 l_old_transaction_rec.trans_qty := -1 * (ABS(l_old_transaction_rec.trans_qty) + p_shipping_line.cycle_count_quantity);
477
478 l_old_transaction_rec.trans_qty2 := -1 * (ABS(l_old_transaction_rec.trans_qty2) + p_shipping_line.cycle_count_quantity2);
479
480 PRINT_DEBUG (l_old_transaction_rec,'Update Default Back Order');
481
482 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
483 ( 1
484 , FND_API.G_FALSE
485 , FND_API.G_FALSE
486 , FND_API.G_VALID_LEVEL_FULL
487 , l_old_transaction_rec
488 , l_old_transaction_row
489 , x_return_status
490 , x_msg_count
491 , x_msg_data
492 );
493
494 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
495 THEN
496 /* B1504749, 21-Nov-2000 odaboval : removed CLOSE cursor here. */
497 /* CLOSE default_transaction_c; */
498
499 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
501 END IF;
502 END IF;
503 END IF;
504
505 END IF;
506 ELSE
507 GMI_RESERVATION_UTIL.println('could not locate the original transaction');
508 /* We could not locate the original transaction. */
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 END IF; /* Non-loc or location controlled */
513 END IF;
514
515 EXCEPTION
516 WHEN OTHERS
517 THEN
518 rollback to process_backorders;
519 x_return_status := FND_API.G_RET_STS_ERROR;
520 /* B1504749, 21-Nov-2000 odaboval : removed CLOSE cursor here. */
521 /* CLOSE default_transaction_c; */
522 END GMI_CREATE_BACKORDER_TXN;
523
524 /* this unreserve would delete the trans for this trans_id
525 and balancing the default lot*/
526 PROCEDURE unreserve_inv
527 ( p_trans_id IN NUMBER
528 , x_return_status OUT NOCOPY VARCHAR2)
529 IS
530 l_transaction_row ic_tran_pnd%ROWTYPE;
531 l_line_id ic_tran_pnd.line_id%TYPE;
532 l_item_id ic_tran_pnd.item_id%TYPE;
533 l_location ic_tran_pnd.location%TYPE;
534 l_lot_id ic_tran_pnd.lot_id%TYPE;
535 l_reduction_factor NUMBER;
536 l_delta_trans_qty2 NUMBER;
537 l_trans_id NUMBER;
538 l_staged_ind NUMBER;
539 l_lock_status BOOLEAN;
540 l_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
541 l_default_trans_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
542 l_msg_count number;
543 l_msg_data varchar2(3000);
544
545 CURSOR default_transaction_c IS
546 SELECT trans_id
547 FROM ic_tran_pnd
548 WHERE doc_type='OMSO'
549 AND line_id = l_transaction_rec.line_id
550 -- AND item_id = l_item_id -- REMOVED for bug 3403418
551 AND lot_id = 0
552 AND location = l_location
553 AND completed_ind = 0
554 AND delete_mark = 0
555 ORDER BY staged_ind;
556
557 BEGIN
558
559 /* Standard Start OF API savepoint */
560
561 GMI_RESERVATION_UTIL.println('deleting trans ');
562 SAVEPOINT process_backorders;
563 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
564
565 l_transaction_rec.trans_id := p_trans_id;
566 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
567 (l_transaction_rec, l_transaction_rec )
568 THEN
569 GMI_reservation_Util.PrintLn('Find OPM Item ID');
570
571 l_item_id := l_transaction_rec.item_id;
572 l_location := l_transaction_rec.location;
573 GMI_RESERVATION_UTIL.println('LINE_ID => ' || l_transaction_rec.line_id);
574 GMI_RESERVATION_UTIL.println('ITEM_ID => ' || l_item_id);
575 GMI_RESERVATION_UTIL.println('LOT_ID => ' || l_lot_id);
576 GMI_RESERVATION_UTIL.println('Location => ' || l_location);
577
578 -- PK Bug 3527599 No need to lock IC_LOCT_INV when deleting pending txn.
579
580 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
581 ( 1
582 , FND_API.G_FALSE
583 , FND_API.G_FALSE
584 , FND_API.G_VALID_LEVEL_FULL
585 , l_transaction_rec
586 , l_transaction_row
587 , x_return_status
588 , l_msg_count
589 , l_msg_data
590 );
591
592 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
593 THEN
594 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597
598 END IF;
599 /* hwahdani BUG#:1495550 get proper value of default location from profile */
600 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
601
602 GMI_RESERVATION_UTIL.find_default_lot
603 ( x_return_status => x_return_status,
604 x_msg_count => l_msg_count,
605 x_msg_data => l_msg_data,
606 x_reservation_id => l_trans_id,
607 p_line_id => l_transaction_rec.line_id
608 );
609
610 IF nvl(l_trans_id,0) = 0 THEN
611 GMI_RESERVATION_UTIL.println('Default Transaction Not Found');
612 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
613 /* create a default lot, in case we are backordering */
614 l_transaction_rec.location := l_location;
615 l_transaction_rec.lot_id := 0;
616 l_transaction_rec.trans_qty := 0;
617 l_transaction_rec.trans_qty2 := 0;
618 l_transaction_rec.staged_ind := 0;
619 l_transaction_rec.line_detail_id := null;
620 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
621 ( p_api_version => 1.0
622 , p_init_msg_list => FND_API.G_FALSE
623 , p_commit => FND_API.G_FALSE
624 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
625 , p_tran_rec => l_transaction_rec
626 , x_tran_row => l_transaction_row
627 , x_return_status => x_return_status
628 , x_msg_count => l_msg_count
629 , x_msg_data => l_msg_data
630 );
631 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
632 THEN
633 GMI_RESERVATION_UTIL.PrintLn('Error returned by creating pending default lot');
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636 l_trans_id := l_transaction_row.trans_id;
637 GMI_RESERVATION_UTIL.println('created Default Transaction trans_id '||l_trans_id);
638 END IF;
639
640 l_transaction_rec.trans_id := l_trans_id;
641
642 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
643 (l_transaction_rec, l_transaction_rec )
644 THEN
645 /* balance the default lot */
646 GMI_RESERVATION_UTIL.balance_default_lot
647 ( p_ic_default_rec => l_transaction_rec
648 , p_opm_item_id => l_transaction_rec.item_id
649 , x_return_status => x_return_status
650 , x_msg_count => l_msg_count
651 , x_msg_data => l_msg_data
652 );
653 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
654 THEN
655 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 END IF;
658 END IF;
659
660 EXCEPTION
661 WHEN OTHERS
662 THEN
663 rollback to process_backorders;
664 x_return_status := FND_API.G_RET_STS_ERROR;
665 END unreserve_inv;
666
667 -- this is obsoleted once the WSH had made changes for backorder, new int, omchanges
668 PROCEDURE GMI_UPDATE_SHIPMENT_TXN
669 ( p_shipping_line IN wsh_delivery_details%ROWTYPE
670 , p_actual_ship_date IN DATE
671 , x_return_status OUT NOCOPY VARCHAR2
672 , x_msg_count OUT NOCOPY NUMBER
673 , x_msg_data OUT NOCOPY VARCHAR2
674 )
675 IS
676
677 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
678 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
679 l_old_transaction_row ic_tran_pnd%ROWTYPE;
680 l_new_transaction_row ic_tran_pnd%ROWTYPE;
681 l_line_id ic_tran_pnd.line_id%TYPE;
682 l_item_id ic_tran_pnd.item_id%TYPE;
683 l_location ic_tran_pnd.location%TYPE;
684 l_lot_id ic_tran_pnd.lot_id%TYPE;
685 l_delta_trans_qty2 NUMBER;
686 l_delta_trans_qty1 NUMBER;
687 l_trans_id NUMBER;
688 l_lock_status BOOLEAN;
689 /* NC Added the following parameters. Bug#1675561 */
690 l_line_detail_id NUMBER;
691 l_trans_count NUMBER;
692 l_loct_ctl NUMBER;
693 l_whse_ctl NUMBER;
694 l_lot_ctl NUMBER;
695 l_src_qty NUMBER;
696 l_cnt_trans NUMBER;
697
698 CURSOR default_transaction_c
699 IS
700 SELECT trans_id
701 FROM ic_tran_pnd
702 WHERE doc_type='OMSO'
703 AND line_id = p_shipping_line.source_line_id
704 -- AND item_id = l_item_id -- REMOVED for bug 3403418
705 AND lot_id = 0
706 AND location = l_location
707 AND completed_ind = 0
708 AND delete_mark = 0;
709 /* LG overpicking backorder */
710 /* since the default lot is shared for non controled items,
711 here we just have to find out the line and complete the qty as needed*/
712 -- AND staged_ind = 1;
713
714 CURSOR get_opm_transaction_c
715 IS
716 SELECT trans_id
717 FROM ic_tran_pnd
718 WHERE doc_type='OMSO'
719 AND line_id = p_shipping_line.source_line_id
720 -- AND item_id = l_item_id -- REMOVED for bug 3403418
721 AND lot_id = l_lot_id
722 AND location = l_location
723 AND completed_ind = 0
724 AND staged_ind = 1
725 AND delete_mark = 0
726 /* temporary fix for 1794681 */
727 /* Have to back this temp fix out because staging is not working. If split shipping line 3 and 7 for
728 the original qty of 10, there is no way that the invnetory could be found Since this issue is an
729 internal issue, before the complete fix for delivery detail in ic_tran_pnd, leave the issue
730 as it is for now, this is a bigger issue for corrugated */
731 --AND trans_qty >= -1 * ABS(p_shipping_line.requested_quantity + 0.000005)
732 --AND trans_qty <= -1 * ABS(p_shipping_line.requested_quantity - 0.000005)
733 ORDER BY trans_id;
734
735 /* NC Added the following two cursors. Bug#1675561 */
736 CURSOR get_opm_trans_count
737 IS
738 SELECT count(*)
739 FROM ic_tran_pnd
740 WHERE doc_type = 'OMSO'
741 AND line_id = p_shipping_line.source_line_id
742 -- AND item_id = l_item_id -- REMOVED for bug 3403418
743 AND lot_id = l_lot_id
744 AND location = l_location
745 AND completed_ind = 0
746 AND staged_ind = 1
747 AND delete_mark = 0;
748 -- ORDER BY trans_id; -- REMOVED for bug 3403418
749
750 CURSOR get_opm_transaction_c2
751 IS
752 SELECT trans_id
753 FROM ic_tran_pnd
754 WHERE doc_type='OMSO'
755 AND line_id = p_shipping_line.source_line_id
756 AND line_detail_id = p_shipping_line.delivery_detail_id
757 AND delete_mark = 0
758 ORDER BY trans_id;
759
760 /* B2547509 Added following cursor */
761 CURSOR get_opm_transaction_cnt
762 IS
763 SELECT count(*)
764 FROM ic_tran_pnd itp
765 WHERE doc_type ='OMSO'
766 AND line_id = p_shipping_line.source_line_id
767 AND line_detail_id = p_shipping_line.delivery_detail_id
768 AND delete_mark = 1
769 AND exists (select 1
770 from wsh_delivery_Details
771 where line_id = p_shipping_line.source_line_id
772 and delivery_detail_id = p_shipping_line.delivery_detail_id
773 and shipped_quantity = (-1)*itp.trans_qty);
774
775 CURSOR get_whse IS
776 Select loct_ctl
777 From ic_whse_mst
778 Where mtl_organization_id = p_shipping_line.organization_id;
779
780 --BEGIN BUG#2736088 V. Ajay Kumar
781 --Removed the refence to "apps".
782 -- HW BUG#3434884. Need to pass schema name
783 Cursor check_wsh (l_schema VARCHAR2) IS
784 Select object_name
785 From all_objects
786 Where object_name = 'WSH_USA_INV_PVT'
787 AND object_type = 'PACKAGE BODY'
788 AND OWNER = l_schema;
789
790 --END BUG#2736088
791
792
793 l_dummy VARCHAR2(30);
794
795 l_ship_qty_above NUMBER;
796 l_ship_qty_below NUMBER;
797 l_allowneginv NUMBER;
798 -- HW bug # 3434884
799 l_ret BOOLEAN;
800 l_schema VARCHAR2(30);
801
802
803 BEGIN
804 /* Standard Start OF API savepoint */
805
806 SAVEPOINT process_shipments;
807 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
808 /* Bug 2775197 */
809
810 -- HW BUG 3434884 - GSCC issue. Need to get proper Schema Name
811
812 select oracle_username
813 into l_schema
814 from fnd_oracle_userid
815 where read_only_flag = 'U';
816
817
818 gmi_reservation_util.println('Value of schema name is '||l_schema);
819 -- HW 3385851. Added this fix for Pushkar
820 BEGIN
821 l_allowneginv := nvl(fnd_profile.value('IC$ALLOWNEGINV'),0);
822 EXCEPTION
823 WHEN OTHERS THEN
824 gmi_reservation_util.println('Error in reading PROFILE: Allow Negative Inventory');
825 l_allowneginv := 0;
826 END;
827 -- end of 3385851
828 GMI_RESERVATION_UTIL.println('Start Of OPM Inventory Interface Routine');
829 GMI_RESERVATION_UTIL.println('delivery_detail_id '||p_shipping_line.delivery_detail_id);
830
831 /* check to see if WSH G is installed, the file WSHUSAIB.pls is new introduced in G
832 so check the object exsits or not would do*/
833 -- HW BUG 3434884- Added parameter l_schema parameter
834 Open check_wsh(l_schema);
835 Fetch check_wsh INTO l_dummy;
836 IF check_wsh%FOUND THEN
837 GMI_RESERVATION_UTIL.println('calling GMI_UPDATE_SHIPMENT_TXN_NEW');
838 GMI_UPDATE_SHIPMENT_TXN_NEW
839 ( p_shipping_line => p_shipping_line
840 , p_actual_ship_date => p_actual_ship_date
841 , x_return_status => x_return_status
842 , x_msg_count => x_msg_count
843 , x_msg_data => x_msg_data
844 );
845 GMI_RESERVATION_UTIL.println('Finished calling GMI_Shipping_Util.GMI_UPDATE _SHIPMENT_TXN');
846 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
847 THEN
848 GMI_RESERVATION_UTIL.println('Error Could Not Complete');
849 Close check_wsh; /* B2886561 close cursor before exception */
850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
851 END IF;
852 IF check_wsh%ISOPEN THEN
853 Close check_wsh;
854 END IF;
855
856 RETURN;
857
858 END IF;
859 Close check_wsh;
860
861 /* Try and retrieve the original transaction. To do this we need */
862 /* to determine a number of keys so that we can locate the correct */
863 /* row in ic_tran_pnd. If any of these retrievals fails there is no */
864 /* point in continuing, so let the exception raised take over. */
865
866 SELECT iim.item_id, iim.lot_ctl, iim.loct_ctl INTO l_item_id,l_lot_ctl, l_loct_ctl
867 FROM ic_item_mst iim,
868 mtl_system_items msi
869 WHERE msi.inventory_item_id = p_shipping_line.inventory_item_id
870 AND msi.organization_id = p_shipping_line.organization_id
871 AND msi.segment1 = iim.item_no;
872
873 IF p_shipping_line.locator_id IS NULL
874 THEN
875 /* hwahdani BUG#:1495550 get proper value of default location from profile */
876 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
877 ELSE
878 SELECT location INTO l_location
879 FROM ic_loct_mst
880 WHERE inventory_location_id = p_shipping_line.locator_id;
881 END IF;
882
883 GMI_RESERVATION_UTIL.println('LOT_NUMBER => ' || p_shipping_line.lot_number);
884 GMI_RESERVATION_UTIL.println('SUBLOT_NUMBER => ' || p_shipping_line.sublot_number);
885
886 IF p_shipping_line.lot_number IS NULL
887 THEN
888 l_lot_id := 0;
889 ELSIF p_shipping_line.sublot_number IS NULL
890 /*HW BUG#:1727553 added sublot_no is NULL */
891 THEN
892 SELECT lot_id INTO l_lot_id
893 FROM ic_lots_mst
894 WHERE item_id = l_item_id
895 AND lot_no = p_shipping_line.lot_number
896 AND sublot_no IS NULL ;
897 ELSE
898 SELECT lot_id INTO l_lot_id
899 FROM ic_lots_mst
900 WHERE item_id = l_item_id
901 AND lot_no = p_shipping_line.lot_number
902 AND sublot_no = p_shipping_line.sublot_number;
903 END IF;
904
905 /* With the above retrievals successfully done we can */
906 /* try to locate the transaction we need. Again, if this fails */
907 /* we cannot proceed so let the exception raised take over. */
908
909 GMI_RESERVATION_UTIL.println('OPM Trying To Retrieve the Correct old Transaction');
910 GMI_RESERVATION_UTIL.println('LINE_ID => ' || p_shipping_line.source_line_id);
911 GMI_RESERVATION_UTIL.println('ITEM_ID => ' || l_item_id);
912 GMI_RESERVATION_UTIL.println('LOT_ID => ' || l_lot_id);
913 GMI_RESERVATION_UTIL.println('Location => ' || l_location);
914
915 /* NC - Added the following code. Bug#1675561 and Bug#1794681 */
916 Open get_whse;
917 Fetch get_whse into l_whse_ctl;
918 Close get_whse;
919
920 IF l_lot_ctl = 0 and (l_loct_ctl * l_whse_ctl) =0 THEN
921 OPEN default_transaction_c;
922 FETCH default_transaction_c INTO l_old_transaction_rec.trans_id;
923 CLOSE default_transaction_c;
924 ELSE
925 OPEN get_opm_trans_count;
926 FETCH get_opm_trans_count into l_trans_count;
927 CLOSE get_opm_trans_count;
928
929 IF l_trans_count = 1 THEN
930 OPEN get_opm_transaction_c;
931 FETCH get_opm_transaction_c INTO l_old_transaction_rec.trans_id;
932 CLOSE get_opm_transaction_c;
933
934 ELSIF l_trans_count <> 1 THEN
935 OPEN get_opm_transaction_c2;
936 LOOP
937 FETCH get_opm_transaction_c2 INTO l_old_transaction_rec.trans_id;
938 IF get_opm_transaction_c2%NOTFOUND THEN
939 Close get_opm_transaction_c2;
940 GMI_RESERVATION_UTIL.println('OPM Transaction Not Found: get_opm_transaction_c2');
941 /* Begin B2547509 */
942 /* Check if this transaction is deleted by chance */
943 OPEN get_opm_transaction_cnt;
944 FETCH get_opm_transaction_cnt into l_cnt_trans;
945 CLOSE get_opm_transaction_cnt;
946 IF (l_cnt_trans > 1) THEN
947 GMI_RESERVATION_UTIL.println('Multiple deleted transactions found - Manual updates are necessary');
948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
949 ELSIF (l_cnt_trans = 1) THEN
950 /* update the transaction's delete mark to 0 and proceed */
951 UPDATE ic_tran_pnd
952 SET delete_mark = 0
953 WHERE doc_type = 'OMSO'
954 AND line_id = p_shipping_line.source_line_id
955 AND line_detail_id = p_shipping_line.delivery_detail_id
956 AND delete_mark = 1
957 returning trans_id into l_old_transaction_rec.trans_id;
958 GMI_RESERVATION_UTIL.println('Undeleted transaction '||to_char(l_old_transaction_rec.trans_id));
959 ELSE
960 GMI_RESERVATION_UTIL.println('Transaction for the line_id/line_detail_id not found or');
961 GMI_RESERVATION_UTIL.println(' the shipped quantity and transaction quantity do not match');
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 END IF;
964 /* End B2547509 */
965 END IF;
966
967 EXIT; /* Exit after First Select */
968 END LOOP;
969 /* B2886561 check if cursor is open before closing */
970 IF get_opm_transaction_c2%ISOPEN THEN
971 CLOSE get_opm_transaction_c2;
972 END IF;
973
974 /*ELSE
975 GMI_RESERVATION_UTIL.println('OPM Transaction Not Found');
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;*/
977 END IF;
978 END IF;
979
980
981 GMI_RESERVATION_UTIL.println('Retrieve OPM Transaction => ' ||l_old_transaction_rec.trans_id );
982 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
983 (l_old_transaction_rec, l_old_transaction_rec )
984 THEN
985
986 l_src_qty := GMI_RESERVATION_UTIL.Get_Opm_converted_qty
987 (
988 p_apps_item_id => p_shipping_line.inventory_item_id,
989 p_organization_id => p_shipping_line.organization_id,
990 p_apps_from_uom => p_shipping_line.src_requested_quantity_uom,
991 p_apps_to_uom => p_shipping_line.requested_quantity_uom,
992 p_original_qty => p_shipping_line.src_requested_quantity
993 ) ;
994 l_ship_qty_above := p_shipping_line.ship_tolerance_above * abs(l_src_qty) /100;
995 l_ship_qty_below := p_shipping_line.ship_tolerance_below * abs(l_src_qty) /100;
996 GMI_RESERVATION_UTIL.println('shipping tolerance above %' ||p_shipping_line.ship_tolerance_above);
997 GMI_RESERVATION_UTIL.println('shipping tolerance below %' ||p_shipping_line.ship_tolerance_below);
998 GMI_RESERVATION_UTIL.println('shipping tolerance above qty' ||l_ship_qty_above);
999 GMI_RESERVATION_UTIL.println('shipping tolerance below qty' ||l_ship_qty_below);
1000
1001 /* We have the transaction we need. See if we are shipping within */
1002 /* the permitted tolerance. If we are, then we reverse out the */
1003 /* existing transaction, insert a new transaction for the */
1004 /* shipped amount and then complete it. If the quantity exactly */
1005 /* matches the transaction quantity, simply complete the one */
1006 /* which already exists. */
1007 /* Before Completing any transactions lets Lock The Rows. */
1008 /* Calling OPM Lock Inventory Routine. */
1009
1010 -- PK Bug 3527599 Moving Lock_Inventory call from here to just before
1011 -- UPDATE_PENDING_TO_COMPLETED call.
1012
1013 GMI_RESERVATION_UTIL.println('Correct Transaction Found');
1014 PRINT_DEBUG (l_old_transaction_rec,'FETCH RECORD');
1015
1016 /* Note That OPM Trans Qtys Are Negative and that the shipped qtys */
1017 /* Are Positive. Therefore For Checks Convert the Trans_qty to a positive */
1018 /* Value Using ABS() rather than ( -1 * variable). */
1019
1020 GMI_RESERVATION_UTIL.println('Shipped Qty => ' || p_shipping_line.shipped_quantity);
1021 GMI_RESERVATION_UTIL.println('Transaction Qty => ' || ABS(l_old_transaction_rec.trans_qty));
1022
1023
1024 PRINT_DEBUG (l_old_transaction_rec,'Original Transaction');
1025
1026 IF p_shipping_line.shipped_quantity = ABS(l_src_qty)
1027 THEN
1028
1029 GMI_RESERVATION_UTIL.println('Ship Qty = Trans Qty');
1030 GMI_RESERVATION_UTIL.println('Completing existing transaction');
1031 PRINT_DEBUG (l_old_transaction_rec,'COMPLETE RECORD');
1032
1033 /* Need To Update The Actual Shipment Date */
1034
1035 /* l_old_transaction_rec.trans_date := p_actual_ship_date; */
1036 /* Bug 2547509 */
1037 l_old_transaction_rec.trans_date := GMI_TRANS_DATE (p_actual_ship_date,
1038 l_old_transaction_rec.orgn_code,
1039 l_old_transaction_rec.whse_code);
1040 /* Bug 2775197 */
1041 IF l_allowneginv <> 1 THEN
1042 IF INVENTORY_GOING_NEG(l_old_transaction_rec) THEN
1043 GMI_RESERVATION_UTIL.println('Profile GMI: Allow Negative Inventory = '||to_char(l_allowneginv));
1044 GMI_RESERVATION_UTIL.println('WARNING:Inventory going negative. Transaction not completed for Trans ID '||
1045 to_char(l_old_transaction_rec.trans_id));
1046 rollback to process_shipments;
1047 RAISE INVENTORY_NEG_WARNING;
1048 END IF;
1049 END IF;
1050
1051 /* NC Added line_detail_id. Bug#1675561 */
1052
1053 -- PK Bug 3527599 Moving Lock_Inventory call here from above.
1054 GMI_reservation_Util.PrintLn('Attempt to Lock Inventory');
1055
1056 GMI_Locks.Lock_Inventory
1057 (
1058 i_item_id => l_old_transaction_rec.item_id
1059 , i_whse_code => l_old_transaction_rec.whse_code
1060 , i_lot_id => l_old_transaction_rec.lot_id
1061 , i_lot_status => l_old_transaction_rec.lot_status
1062 , i_location => l_old_transaction_rec.location
1063 , o_lock_status => l_lock_status
1064 );
1065
1066 IF (l_lock_status = FALSE) THEN
1067 GMI_reservation_Util.PrintLn('Lock_Inventory Failed');
1068 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1069 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
1070 FND_MESSAGE.Set_Token('WHERE', 'GMI_UPDATE_SHIPMENT_TXN');
1071 FND_MSG_PUB.Add;
1072 RAISE FND_API.G_EXC_ERROR;
1073 END IF;
1074
1075 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
1076 ( 1
1077 , FND_API.G_FALSE
1078 , FND_API.G_FALSE
1079 , FND_API.G_VALID_LEVEL_FULL
1080 , l_old_transaction_rec
1081 , l_old_transaction_row
1082 , x_return_status
1083 , x_msg_count
1084 , x_msg_data
1085 );
1086
1087 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1088 THEN
1089 GMI_RESERVATION_UTIL.println('Error Could Not Complete');
1090 GMI_RESERVATION_UTIL.println('Inv Eng Msg:'||x_msg_data);
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092 END IF;
1093
1094 ELSIF p_shipping_line.shipped_quantity <= ABS(l_src_qty)
1095 + l_ship_qty_above
1096 AND p_shipping_line.shipped_quantity >= ABS(l_src_qty)
1097 - l_ship_qty_below
1098 THEN
1099
1100 GMI_RESERVATION_UTIL.println('Shipping Within Tolerance');
1101
1102 GMI_RESERVATION_UTIL.println('Delete Original Transaction');
1103 PRINT_DEBUG (l_old_transaction_rec, 'DELETE RECORD');
1104
1105 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
1106 ( 1
1107 , FND_API.G_FALSE
1108 , FND_API.G_FALSE
1109 , FND_API.G_VALID_LEVEL_FULL
1110 , l_old_transaction_rec
1111 , l_old_transaction_row
1112 , x_return_status
1113 , x_msg_count
1114 , x_msg_data
1115 );
1116
1117 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1118 THEN
1119 GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
1120 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1121 END IF;
1122
1123 l_new_transaction_rec := l_old_transaction_rec;
1124 l_new_transaction_rec.trans_qty2:= -1 * p_shipping_line.shipped_quantity2;
1125 l_new_transaction_rec.trans_qty := -1 * p_shipping_line.shipped_quantity;
1126 l_new_transaction_rec.trans_id := NULL;
1127
1128 /* NC Added line_detail_id. Bug1675561 */
1129 l_new_transaction_rec.line_detail_id := p_shipping_line.delivery_detail_id;
1130
1131
1132 GMI_RESERVATION_UTIL.println('Write New Transaction');
1133 PRINT_DEBUG (l_new_transaction_rec,'CREATE NEW');
1134
1135 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
1136 ( 1
1137 , FND_API.G_FALSE
1138 , FND_API.G_FALSE
1139 , FND_API.G_VALID_LEVEL_FULL
1140 , l_new_transaction_rec
1141 , l_new_transaction_row
1142 , x_return_status
1143 , x_msg_count
1144 , x_msg_data
1145 );
1146
1147 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1148 THEN
1149 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
1150 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1151 END IF;
1152
1153 l_new_transaction_rec.trans_id := l_new_transaction_row.trans_id;
1154
1155 /* Need To Update The Actual Shipment Date */
1156
1157 /* l_new_transaction_rec.trans_date := p_actual_ship_date; */
1158 /* Bug 2547509 */
1159 l_new_transaction_rec.trans_date := GMI_TRANS_DATE (p_actual_ship_date,
1160 l_new_transaction_rec.orgn_code,
1161 l_new_transaction_rec.whse_code);
1162 /* Bug 2775197 */
1163 IF l_allowneginv <> 1 THEN
1164 IF INVENTORY_GOING_NEG(l_new_transaction_rec) THEN
1165 GMI_RESERVATION_UTIL.println('Profile GMI: Allow Negative Inventory = '||to_char(l_allowneginv));
1166 GMI_RESERVATION_UTIL.println('WARNING:Inventory going negative. Transaction not completed for Trans ID '||
1167 to_char(l_new_transaction_rec.trans_id));
1168 rollback to process_shipments;
1169 RAISE INVENTORY_NEG_WARNING;
1170 END IF;
1171 END IF;
1172
1173 GMI_RESERVATION_UTIL.println('Update New Transaction => Completed');
1174
1175 PRINT_DEBUG (l_new_transaction_rec,'UPDATE TO COMPLETE');
1176
1177 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
1178 ( 1
1179 , FND_API.G_FALSE
1180 , FND_API.G_FALSE
1181 , FND_API.G_VALID_LEVEL_FULL
1182 , l_new_transaction_rec
1183 , l_new_transaction_row
1184 , x_return_status
1185 , x_msg_count
1186 , x_msg_data
1187 );
1188
1189 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1190 THEN
1191 GMI_RESERVATION_UTIL.println('Error Could Not Complete');
1192 GMI_RESERVATION_UTIL.println('Inv Eng Msg:'||x_msg_data);
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195 GMI_RESERVATION_UTIL.find_default_lot
1196 ( x_return_status => x_return_status,
1197 x_msg_count => x_msg_count,
1198 x_msg_data => x_msg_data,
1199 x_reservation_id => l_trans_id,
1200 p_line_id => l_new_transaction_rec.line_id
1201 );
1202 IF nvl(l_trans_id,0) > 0 THEN -- bug 2124600
1203 l_new_transaction_rec.trans_id := l_trans_id;
1204 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
1205 (l_new_transaction_rec, l_new_transaction_rec )
1206 THEN
1207 l_new_transaction_rec.trans_qty := 0;
1208 l_new_transaction_rec.trans_qty2 := 0;
1209
1210 GMI_RESERVATION_UTIL.println('shipping within tolerance ');
1211 PRINT_DEBUG (l_new_transaction_rec,' 0 out Default Transaction');
1212
1213 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
1214 ( 1
1215 , FND_API.G_FALSE
1216 , FND_API.G_FALSE
1217 , FND_API.G_VALID_LEVEL_FULL
1218 , l_new_transaction_rec
1219 , l_new_transaction_row
1220 , x_return_status
1221 , x_msg_count
1222 , x_msg_data
1223 );
1224 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1225 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
1226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1227 END IF;
1228 END IF;
1229 END IF;
1230 ELSIF p_shipping_line.shipped_quantity >
1231 ABS(l_src_qty) + l_ship_qty_above
1232 THEN
1233 GMI_RESERVATION_UTIL.println('Shipping More Than Tolerance');
1234 /* Complain. We are not allowed to overship */
1235 NULL;
1236 ELSE
1237 /* the split of inv for lot controled item is done in ship confirm */
1238 /* so this portion of the code is only applicable for non-controled items or over pick */
1239 IF l_lot_ctl = 0 and l_loct_ctl * l_whse_ctl = 0 THEN
1240 GMI_RESERVATION_UTIL.println('Creating Partial Shipment');
1241
1242 l_new_transaction_rec := l_old_transaction_rec;
1243
1244 GMI_RESERVATION_UTIL.println('Delete Original Transaction');
1245 PRINT_DEBUG (l_old_transaction_rec,'Delete Original');
1246
1247 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
1248 ( 1
1249 , FND_API.G_FALSE
1250 , FND_API.G_FALSE
1251 , FND_API.G_VALID_LEVEL_FULL
1252 , l_old_transaction_rec
1253 , l_old_transaction_row
1254 , x_return_status
1255 , x_msg_count
1256 , x_msg_data
1257 );
1258
1259 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1260 THEN
1261 GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
1262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1263 END IF;
1264
1265 /* Create a new transaction for the shipped quantity */
1266
1267 l_new_transaction_rec.trans_qty2:= -1 * p_shipping_line.shipped_quantity2;
1268 l_new_transaction_rec.trans_qty := -1 * p_shipping_line.shipped_quantity;
1269 l_new_transaction_rec.trans_id := NULL;
1270 /* LG backorder, it is always trun the staged as 1 for lot controled items,
1271 not quite so for non controled, thus this is neccessary */
1272 l_new_transaction_rec.staged_ind := 1;
1273
1274 /* NC - Added line_detail_id. Bug1675561 */
1275 l_new_transaction_rec.line_detail_id := p_shipping_line.delivery_detail_id;
1276
1277
1278 GMI_RESERVATION_UTIL.println('Create New Transaction For Shipped Qty');
1279 PRINT_DEBUG (l_new_transaction_rec,'Create NEW');
1280
1281 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
1282 ( 1
1283 , FND_API.G_FALSE
1284 , FND_API.G_FALSE
1285 , FND_API.G_VALID_LEVEL_FULL
1286 , l_new_transaction_rec
1287 , l_new_transaction_row
1288 , x_return_status
1289 , x_msg_count
1290 , x_msg_data
1291 );
1292
1293 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1294 THEN
1295 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298
1299 l_new_transaction_rec.trans_id := l_new_transaction_row.trans_id;
1300 /* Need To Update The Actual Shipment Date */
1301 /* l_new_transaction_rec.trans_date := p_actual_ship_date; */
1302 /* Bug 2547509 */
1303 l_new_transaction_rec.trans_date := GMI_TRANS_DATE (p_actual_ship_date,
1304 l_new_transaction_rec.orgn_code,
1305 l_new_transaction_rec.whse_code);
1306 /* Bug 2775197 */
1307 IF l_allowneginv <> 1 THEN
1308 IF INVENTORY_GOING_NEG(l_new_transaction_rec) THEN
1309 GMI_RESERVATION_UTIL.println('Profile GMI: Allow Negative Inventory = '||to_char(l_allowneginv));
1310 GMI_RESERVATION_UTIL.println('WARNING:Inventory going negative. Transaction not completed for Trans ID '||
1311 to_char(l_new_transaction_rec.trans_id));
1312 rollback to process_shipments;
1313 RAISE INVENTORY_NEG_WARNING;
1314 END IF;
1315 END IF;
1316
1317 GMI_RESERVATION_UTIL.println('Update New Transaction to Completed');
1318 PRINT_DEBUG (l_new_transaction_rec,'UPDATE NEW');
1319
1320 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
1321 ( 1
1322 , FND_API.G_FALSE
1323 , FND_API.G_FALSE
1324 , FND_API.G_VALID_LEVEL_FULL
1325 , l_new_transaction_rec
1326 , l_new_transaction_row
1327 , x_return_status
1328 , x_msg_count
1329 , x_msg_data
1330 );
1331
1332 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1333 THEN
1334 GMI_RESERVATION_UTIL.println('Error : Cannot Complete the transaction.');
1335 GMI_RESERVATION_UTIL.println('Inv Eng Msg:'||x_msg_data);
1336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1337 END IF;
1338
1339 GMI_RESERVATION_UTIL.println('Handle Split');
1340 GMI_RESERVATION_UTIL.println('OLD QTY = > ' || l_old_transaction_rec.trans_qty);
1341 GMI_RESERVATION_UTIL.println('NEW QTY = > ' || l_new_transaction_rec.trans_qty);
1342 GMI_RESERVATION_UTIL.println('BACK QTY = > ' || p_shipping_line.cycle_count_quantity);
1343
1344 /* We now need to sort out the transaction for the default lot. */
1345 /* There are two cases to consider and the cursor will retrieve */
1346 /* the characteristics of the row we need (if it exists) */
1347
1348 /* a) If a staged default lot transaction exists, create a new */
1349 /* staged transaction for the residual quantities */
1350 /* otherwise */
1351 /* c) If no default transaction exists, create the same transaction */
1352 /* as would have been created in 'b'. */
1353
1354 l_delta_trans_qty1:= ABS(l_old_transaction_rec.trans_qty) -
1355 ABS(l_new_transaction_rec.trans_qty);
1356
1357 l_delta_trans_qty2:= ABS(l_old_transaction_rec.trans_qty2) -
1358 ABS(l_new_transaction_rec.trans_qty2);
1359
1360 /* Added Following Logic To Get Correct TXN Qty's */
1361
1362 IF l_delta_trans_qty1 > 0 THEN
1363 l_delta_trans_qty1 := l_delta_trans_qty1 * -1;
1364 l_delta_trans_qty2 := l_delta_trans_qty2 * -1;
1365 END IF;
1366
1367 /* End Logic To Get Correct TXN Qty's */
1368 GMI_RESERVATION_UTIL.println('delta Qty = > ' || l_delta_trans_qty1);
1369 GMI_RESERVATION_UTIL.println('delta Qty2 = > ' || l_delta_trans_qty2);
1370
1371 /* get the defualt trans */
1372 OPEN default_transaction_c;
1373 FETCH default_transaction_c INTO l_trans_id;
1374 IF default_transaction_c%NOTFOUND
1375 THEN
1376 /* B1504749, 21-Nov-2000 odaboval : added CLOSE cursor here. */
1377 CLOSE default_transaction_c;
1378
1379 GMI_RESERVATION_UTIL.println('Create New Transaction For Staged');
1380 /* We need to create the default, staged row */
1381
1382 l_new_transaction_rec.trans_id := NULL;
1383 l_new_transaction_rec.trans_qty := l_delta_trans_qty1;
1384 l_new_transaction_rec.trans_qty2:= l_delta_trans_qty2;
1385 /* LG overpicking backorder , only non controled split would happen here
1386 and it would for back order only */
1387 l_new_transaction_rec.staged_ind := 0;
1388
1389 l_new_transaction_rec.line_detail_id := null;
1390
1391
1392 PRINT_DEBUG (l_new_transaction_rec,'CREATE NEW Staged');
1393
1394 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
1395 ( 1
1396 , FND_API.G_FALSE
1397 , FND_API.G_FALSE
1398 , FND_API.G_VALID_LEVEL_FULL
1399 , l_new_transaction_rec
1400 , l_new_transaction_row
1401 , x_return_status
1402 , x_msg_count
1403 , x_msg_data
1404 );
1405
1406 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1407 THEN
1408 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
1409 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1410 END IF;
1411
1412 GMI_RESERVATION_UTIL.println('created trans_id='||l_new_transaction_row.trans_id);
1413 GMI_RESERVATION_UTIL.println('and its staged_ind='||l_new_transaction_row.staged_ind);
1414
1415 ELSE
1416 /* B1504749, 21-Nov-2000 odaboval : added CLOSE cursor here. */
1417 CLOSE default_transaction_c;
1418
1419 /* We need to increase the amounts in the existing transaction */
1420 l_old_transaction_rec.trans_id := l_trans_id;
1421 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
1422 (l_old_transaction_rec, l_old_transaction_rec )
1423 THEN
1424 l_old_transaction_rec.trans_qty := l_old_transaction_rec.trans_qty +
1425 l_delta_trans_qty1;
1426 l_old_transaction_rec.trans_qty2:= l_old_transaction_rec.trans_qty2 +
1427 l_delta_trans_qty2;
1428
1429 GMI_RESERVATION_UTIL.println('Update Default For Staged');
1430
1431 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
1432 ( 1
1433 , FND_API.G_FALSE
1434 , FND_API.G_FALSE
1435 , FND_API.G_VALID_LEVEL_FULL
1436 , l_old_transaction_rec
1437 , l_old_transaction_row
1438 , x_return_status
1439 , x_msg_count
1440 , x_msg_data
1441 );
1442
1443 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1444 THEN
1445 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
1446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1447 END IF;
1448 END IF;
1449 END IF;
1450 ELSE
1451 /* over picking but shipped less than picked */
1452 /* keep in mind that the inv split is done in ship confirm */
1453 /* do 2 things here, complete the inv with the shipped qty
1454 then balance the default */
1455 GMI_RESERVATION_UTIL.println('overpicked, but shipping < picked ');
1456 l_new_transaction_rec := l_old_transaction_rec;
1457 l_new_transaction_rec.trans_qty2:= -1 * p_shipping_line.shipped_quantity2;
1458 l_new_transaction_rec.trans_qty := -1 * p_shipping_line.shipped_quantity;
1459 /* l_new_transaction_rec.trans_date := p_actual_ship_date; */
1460 /* Bug 2547509 */
1461 l_new_transaction_rec.trans_date := GMI_TRANS_DATE (p_actual_ship_date,
1462 l_new_transaction_rec.orgn_code,
1463 l_new_transaction_rec.whse_code);
1464 /* Bug 2775197 */
1465 IF l_allowneginv <> 1 THEN
1466 IF INVENTORY_GOING_NEG(l_new_transaction_rec) THEN
1467 GMI_RESERVATION_UTIL.println('Profile GMI: Allow Negative Inventory = '||to_char(l_allowneginv));
1468 GMI_RESERVATION_UTIL.println('WARNING:Inventory going negative. Transaction not completed for Trans ID '||
1469 to_char(l_new_transaction_rec.trans_id));
1470 rollback to process_shipments;
1471 RAISE INVENTORY_NEG_WARNING;
1472 END IF;
1473 END IF;
1474
1475 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
1476 ( 1
1477 , FND_API.G_FALSE
1478 , FND_API.G_FALSE
1479 , FND_API.G_VALID_LEVEL_FULL
1480 , l_new_transaction_rec
1481 , l_new_transaction_row
1482 , x_return_status
1483 , x_msg_count
1484 , x_msg_data
1485 );
1486
1487 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1488 THEN
1489 GMI_RESERVATION_UTIL.println('Error : Cannot Complete the transaction.');
1490 GMI_RESERVATION_UTIL.println('Inv Eng Msg:'||x_msg_data);
1491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1492 END IF;
1493 /* find the default transaction */
1494 OPEN default_transaction_c;
1495 FETCH default_transaction_c INTO l_trans_id;
1496 CLOSE default_transaction_c;
1497 l_new_transaction_rec.trans_id := l_trans_id;
1498 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
1499 (l_new_transaction_rec, l_new_transaction_rec )
1500 THEN
1501 GMI_RESERVATION_UTIL.println('balancing default lot.');
1502 GMI_RESERVATION_UTIL.balance_default_lot
1503 ( p_ic_default_rec => l_new_transaction_rec
1504 , p_opm_item_id => l_new_transaction_rec.item_id
1505 , x_return_status => x_return_status
1506 , x_msg_count => x_msg_count
1507 , x_msg_data => x_msg_data
1508 );
1509 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1510 THEN
1511 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
1512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1513 END IF;
1514 END IF;
1515 END IF;
1516
1517 /* B1504749, 21-Nov-2000 odaboval : removed CLOSE cursor here. */
1518 /* CLOSE default_transaction_c; */
1519 END IF;
1520 ELSE
1521 GMI_RESERVATION_UTIL.println('could not locate the original transaction');
1522 /* We could not locate the original transaction. */
1523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1524 END IF;
1525
1526 EXCEPTION
1527
1528 WHEN INVENTORY_NEG_WARNING THEN
1529 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1530
1531 WHEN NO_DATA_FOUND THEN
1532 rollback to process_shipments;
1533 x_return_status := FND_API.G_RET_STS_ERROR;
1534 GMI_RESERVATION_UTIL.Println('Raised No Data Found');
1535
1536 WHEN OTHERS
1537 THEN
1538 rollback to process_shipments;
1539 x_return_status := FND_API.G_RET_STS_ERROR;
1540 GMI_RESERVATION_UTIL.Println('Raised When Others');
1541
1542 /* B1504749, 21-Nov-2000 odaboval : removed CLOSE cursor here. */
1543 /* CLOSE default_transaction_c; */
1544 END GMI_UPDATE_SHIPMENT_TXN;
1545
1546 /* rewritten after OM changes from wsh */
1547 PROCEDURE GMI_UPDATE_SHIPMENT_TXN_new
1548 ( p_shipping_line IN wsh_delivery_details%ROWTYPE
1549 , p_actual_ship_date IN DATE
1550 , x_return_status OUT NOCOPY VARCHAR2
1551 , x_msg_count OUT NOCOPY NUMBER
1552 , x_msg_data OUT NOCOPY VARCHAR2
1553 )
1554 IS
1555
1556 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1557 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1558 l_def_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
1559 l_old_transaction_row ic_tran_pnd%ROWTYPE;
1560 l_new_transaction_row ic_tran_pnd%ROWTYPE;
1561 l_line_id ic_tran_pnd.line_id%TYPE;
1562 l_item_id ic_tran_pnd.item_id%TYPE;
1563 l_location ic_tran_pnd.location%TYPE;
1564 l_lot_id ic_tran_pnd.lot_id%TYPE;
1565 l_delta_trans_qty2 NUMBER;
1566 l_delta_trans_qty1 NUMBER;
1567 l_trans_id NUMBER;
1568 l_lock_status BOOLEAN;
1569 l_line_detail_id NUMBER;
1570 l_loct_ctl NUMBER;
1571 l_whse_ctl NUMBER;
1572 l_lot_ctl NUMBER;
1573 l_default_trans_id NUMBER;
1574 l_cnt_trans NUMBER;
1575
1576 /* this cursor should include the default lot for the non ctl items
1577 The split happens for the interface in split_trans */
1578 CURSOR get_opm_transaction_c
1579 IS
1580 SELECT trans_id
1581 FROM ic_tran_pnd
1582 WHERE doc_type='OMSO'
1583 AND line_id = p_shipping_line.source_line_id
1584 AND line_detail_id = p_shipping_line.delivery_detail_id
1585 AND delete_mark = 0
1586 ORDER BY trans_id;
1587
1588 /* B2547509 Added following cursor */
1589 CURSOR get_opm_transaction_cnt
1590 IS
1591 SELECT count(*)
1592 FROM ic_tran_pnd itp
1593 WHERE doc_type ='OMSO'
1594 AND line_id = p_shipping_line.source_line_id
1595 AND line_detail_id = p_shipping_line.delivery_detail_id
1596 AND delete_mark = 1
1597 AND exists (select 1
1598 from wsh_delivery_Details
1599 where line_id = p_shipping_line.source_line_id
1600 and delivery_detail_id = p_shipping_line.delivery_detail_id
1601 and shipped_quantity = (-1)*itp.trans_qty);
1602
1603 l_ship_qty_above NUMBER;
1604 l_ship_qty_below NUMBER;
1605 l_allowneginv NUMBER;
1606
1607 BEGIN
1608 /* Standard Start OF API savepoint */
1609
1610 SAVEPOINT process_shipments;
1611 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1612
1613 GMI_RESERVATION_UTIL.println('Start Of OPM Inventory Interface Routine');
1614 GMI_RESERVATION_UTIL.println('delivery_detail_id '||p_shipping_line.delivery_detail_id);
1615 GMI_RESERVATION_UTIL.println('shipped_qty '||p_shipping_line.shipped_quantity);
1616
1617 OPEN get_opm_transaction_c;
1618 FETCH get_opm_transaction_c INTO l_old_transaction_rec.trans_id;
1619 IF get_opm_transaction_c%NOTFOUND THEN
1620 Close get_opm_transaction_c;
1621 GMI_RESERVATION_UTIL.println('OPM Transaction Not Found: get_opm_transaction_c');
1622 /* Begin B2547509 */
1623 /* Check if this transaction is deleted by chance */
1624 OPEN get_opm_transaction_cnt;
1625 FETCH get_opm_transaction_cnt into l_cnt_trans;
1626 CLOSE get_opm_transaction_cnt;
1627 IF (l_cnt_trans > 1) THEN
1628 GMI_RESERVATION_UTIL.println('Multiple deleted transactions found - Manual updates are necessary');
1629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1630 ELSIF (l_cnt_trans = 1) THEN
1631 /* update the transaction's delete mark to 0 and proceed */
1632 UPDATE ic_tran_pnd
1633 SET delete_mark = 0
1634 WHERE doc_type = 'OMSO'
1635 AND line_id = p_shipping_line.source_line_id
1636 AND line_detail_id = p_shipping_line.delivery_detail_id
1637 AND delete_mark = 1
1638 returning trans_id into l_old_transaction_rec.trans_id;
1639 GMI_RESERVATION_UTIL.println('Undeleted transaction '||to_char(l_old_transaction_rec.trans_id));
1640 ELSE
1641 GMI_RESERVATION_UTIL.println('Transaction for the line_id/line_detail_id not found or');
1642 GMI_RESERVATION_UTIL.println(' the shipped quantity and transaction quantity do not match');
1643 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1644 END IF;
1645 /* End B2547509 */
1646 END IF;
1647 /* B2886561 check if cursor is open before closing */
1648 IF get_opm_transaction_c%ISOPEN THEN
1649 CLOSE get_opm_transaction_c;
1650 END IF;
1651
1652 GMI_RESERVATION_UTIL.println('Retrieve OPM Transaction => ' ||l_old_transaction_rec.trans_id );
1653 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
1654 (l_old_transaction_rec, l_old_transaction_rec )
1655 THEN
1656 -- PK Bug 3527599 Moving Lock_Inventory call from here to just before
1657 -- UPDATE_PENDING_TO_COMPLETED call.
1658
1659 GMI_RESERVATION_UTIL.println('Correct Transaction Found');
1660
1661 GMI_RESERVATION_UTIL.println('Shipped Qty => ' || p_shipping_line.shipped_quantity);
1662 GMI_RESERVATION_UTIL.println('Transaction Qty => ' || ABS(l_old_transaction_rec.trans_qty));
1663 GMI_RESERVATION_UTIL.println('Completing existing transaction');
1664
1665 l_new_transaction_rec := l_old_transaction_rec;
1666 l_new_transaction_rec.trans_qty2:= -1 * p_shipping_line.shipped_quantity2;
1667 l_new_transaction_rec.trans_qty := -1 * p_shipping_line.shipped_quantity;
1668 /* l_new_transaction_rec.trans_date := p_actual_ship_date; */
1669 /* Bug 2547509 */
1670 l_new_transaction_rec.trans_date := GMI_TRANS_DATE (p_actual_ship_date,
1671 l_new_transaction_rec.orgn_code,
1672 l_new_transaction_rec.whse_code);
1673 /* Bug 2775197 */
1674 -- HW 3385851. Added this fix for Pushkar
1675 BEGIN
1676 l_allowneginv := nvl(fnd_profile.value('IC$ALLOWNEGINV'),0);
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 gmi_reservation_util.println('Error in reading PROFILE: Allow Negative Inventory');
1680 l_allowneginv := 0;
1681 END;
1682 -- end of 3385851
1683 IF l_allowneginv <> 1 THEN
1684 IF INVENTORY_GOING_NEG(l_new_transaction_rec) THEN
1685 GMI_RESERVATION_UTIL.println('Profile GMI: Allow Negative Inventory = '||to_char(l_allowneginv));
1686 GMI_RESERVATION_UTIL.println('WARNING:Inventory going negative. Transaction not completed for Trans ID '||
1687 to_char(l_new_transaction_rec.trans_id));
1688 rollback to process_shipments;
1689 RAISE INVENTORY_NEG_WARNING;
1690 END IF;
1691 END IF;
1692
1693 -- PK Bug 3527599 Moving Lock_Inventory call here from above.
1694 GMI_reservation_Util.PrintLn('Attempt to Lock Inventory');
1695 GMI_Locks.Lock_Inventory
1696 (
1697 i_item_id => l_old_transaction_rec.item_id
1698 , i_whse_code => l_old_transaction_rec.whse_code
1699 , i_lot_id => l_old_transaction_rec.lot_id
1700 , i_lot_status => l_old_transaction_rec.lot_status
1701 , i_location => l_old_transaction_rec.location
1702 , o_lock_status => l_lock_status
1703 );
1704
1705 IF (l_lock_status = FALSE) THEN
1706 GMI_reservation_Util.PrintLn('Lock_Inventory Failed');
1707 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
1708 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Locks.Lock_Inventory');
1709 FND_MESSAGE.Set_Token('WHERE', 'GMI_UPDATE_SHIPMENT_TXN');
1710 FND_MSG_PUB.Add;
1711 RAISE FND_API.G_EXC_ERROR;
1712 END IF;
1713
1714 PRINT_DEBUG (l_new_transaction_rec,'COMPLETE RECORD');
1715
1716 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TO_COMPLETED
1717 ( 1
1718 , FND_API.G_FALSE
1719 , FND_API.G_FALSE
1720 , FND_API.G_VALID_LEVEL_FULL
1721 , l_new_transaction_rec
1722 , l_new_transaction_row
1723 , x_return_status
1724 , x_msg_count
1725 , x_msg_data
1726 );
1727
1728 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1729 THEN
1730 GMI_RESERVATION_UTIL.println('Error Could Not Complete');
1731 GMI_RESERVATION_UTIL.println('Inv Eng Msg:'||x_msg_data);
1732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1733 END IF;
1734 /* only need to balance default lot for overpickiing */
1735 GMI_RESERVATION_UTIL.println('find_deafult_lot ');
1736 /* find the default transaction */
1737 GMI_RESERVATION_UTIL.find_default_lot
1738 ( x_return_status => x_return_status,
1739 x_msg_count => x_msg_count,
1740 x_msg_data => x_msg_data,
1741 x_reservation_id => l_trans_id,
1742 p_line_id => l_new_transaction_rec.line_id
1743 );
1744 /* because OM int is done first, there might not be a default lot associated with this line_id*/
1745 IF nvl(l_trans_id,0) > 0 THEN -- no balancing if no default exist
1746 l_new_transaction_rec.trans_id := l_trans_id;
1747 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
1748 (l_new_transaction_rec, l_new_transaction_rec )
1749 THEN
1750 l_ship_qty_above := p_shipping_line.ship_tolerance_above * abs(l_old_transaction_rec.trans_qty) /100;
1751 l_ship_qty_below := p_shipping_line.ship_tolerance_below * abs(l_old_transaction_rec.trans_qty) /100;
1752 GMI_RESERVATION_UTIL.println('shipping tolerance above %' ||p_shipping_line.ship_tolerance_above/100);
1753 GMI_RESERVATION_UTIL.println('shipping tolerance below %' ||p_shipping_line.ship_tolerance_below/100);
1754 GMI_RESERVATION_UTIL.println('shipping tolerance above qty' ||l_ship_qty_above);
1755 GMI_RESERVATION_UTIL.println('shipping tolerance below qty' ||l_ship_qty_below);
1756 /* if ship within tolerance, 0 out the default lot */
1757 IF p_shipping_line.shipped_quantity <= ABS(p_shipping_line.requested_quantity )
1758 + l_ship_qty_above
1759 AND p_shipping_line.shipped_quantity >= ABS(p_shipping_line.requested_quantity)
1760 - l_ship_qty_below
1761 THEN
1762 l_new_transaction_rec.trans_qty := 0;
1763 l_new_transaction_rec.trans_qty2 := 0;
1764
1765 GMI_RESERVATION_UTIL.println('shipping within tolerance ');
1766 PRINT_DEBUG (l_new_transaction_rec,' 0 out Default Transaction');
1767
1768 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
1769 ( 1
1770 , FND_API.G_FALSE
1771 , FND_API.G_FALSE
1772 , FND_API.G_VALID_LEVEL_FULL
1773 , l_new_transaction_rec
1774 , l_new_transaction_row
1775 , x_return_status
1776 , x_msg_count
1777 , x_msg_data
1778 );
1779 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1780 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783 ELSE
1784 GMI_RESERVATION_UTIL.println('balancing default lot.');
1785 GMI_RESERVATION_UTIL.balance_default_lot
1786 ( p_ic_default_rec => l_new_transaction_rec
1787 , p_opm_item_id => l_new_transaction_rec.item_id
1788 , x_return_status => x_return_status
1789 , x_msg_count => x_msg_count
1790 , x_msg_data => x_msg_data
1791 );
1792 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1793 THEN
1794 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
1795 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1796 END IF;
1797 END IF;
1798 END IF;
1799 END IF;
1800 END IF;
1801
1802 EXCEPTION
1803
1804 WHEN INVENTORY_NEG_WARNING THEN
1805 RAISE;
1806
1807 WHEN NO_DATA_FOUND THEN
1808 rollback to process_shipments;
1809 x_return_status := FND_API.G_RET_STS_ERROR;
1810 GMI_RESERVATION_UTIL.Println('Raised No Data Found');
1811
1812 WHEN OTHERS
1813 THEN
1814 rollback to process_shipments;
1815 x_return_status := FND_API.G_RET_STS_ERROR;
1816 GMI_RESERVATION_UTIL.Println('Raised When Others');
1817
1818 END GMI_UPDATE_SHIPMENT_TXN_new;
1819
1820 PROCEDURE GMI_APPLY_BACKORDER_UPDATES
1821 ( p_original_source_line_id IN NUMBER
1822 , p_source_line_id IN NUMBER
1823 , p_action_flag IN VARCHAR2
1824 , x_return_status OUT NOCOPY VARCHAR2
1825 , x_msg_count OUT NOCOPY NUMBER
1826 , x_msg_data OUT NOCOPY VARCHAR2
1827 )
1828 IS
1829
1830 -- HW BUG#: 1826752
1831 l_delivery_detail_id NUMBER;
1832 l_source_line_id NUMBER;
1833 l_released_status VARCHAR2(5);
1834 l_lot_number VARCHAR2(32);
1835 l_sublot_number VARCHAR2(32);
1836
1837 Cursor get_bad_delivery IS
1838 Select delivery_detail_id , source_line_id,
1839 released_status,lot_number,sublot_number
1840 From wsh_delivery_details
1841 Where source_line_id = p_source_line_id
1842 AND released_status NOT IN ('C','Y');
1843 -- HW END OF BUG#:1826752
1844
1845
1846
1847 Cursor get_move_order IS
1848 SELECT move_order_line_id, released_status
1849 FROM wsh_delivery_details
1850 WHERE source_line_id = p_source_line_id
1851 AND released_status = 'S';
1852 /* odaboval : Oct-2000, in the where_clause, taken the */
1853 /* p_source_line_id instead of p_original_source_line_id, in order to */
1854 /* get the right line. */
1855 /* WHERE source_line_id = p_original_source_line_id */
1856
1857 l_move_order get_move_order%rowtype;
1858
1859 BEGIN
1860
1861 /* Let's First Update all the move order lines ( backordered and */
1862 /* Not Pick Confirmed). */
1863
1864 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1865
1866 GMI_RESERVATION_UTIL.println('In GMI BACKORDER UPDATE');
1867 GMI_RESERVATION_UTIL.println('P_source_line_id => ' || p_source_line_id);
1868 GMI_RESERVATION_UTIL.println('Original SOURCE => ' || p_original_source_line_id );
1869
1870 Open get_move_order;
1871
1872 LOOP
1873 FETCH get_move_order into l_move_order;
1874 EXIT WHEN get_move_order%NOTFOUND;
1875
1876 GMI_RESERVATION_UTIL.println('Mo_ID=' || l_move_order.move_order_line_id ||', txn_source_line_id='||p_source_line_id ||', Status='|| l_move_order.released_status);
1877
1878 Update IC_TXN_REQUEST_LINES
1879 SET TXN_SOURCE_LINE_ID = p_source_line_id
1880 WHERE line_id = l_move_order.move_order_line_id;
1881
1882 IF (SQL%NOTFOUND) THEN
1883 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1884 GMI_RESERVATION_UTIL.println('Error In Updating MO LINE');
1885 Close get_move_order; /* B2886561 close cursor before exception */
1886 RAISE NO_DATA_FOUND;
1887 END IF;
1888
1889 END LOOP;
1890 IF get_move_order%ISOPEN THEN
1891 Close get_move_order;
1892 END IF;
1893
1894 -- HW BUG#:1826752
1895 open get_bad_delivery ;
1896
1897 LOOP
1898 FETCH get_bad_delivery into l_delivery_detail_id,
1899 l_source_line_id, l_released_status,l_lot_number,l_sublot_number;
1900 EXIT WHEN get_bad_delivery%NOTFOUND;
1901 GMI_RESERVATION_UTIL.println('Clearing out information for:');
1902 GMI_RESERVATION_UTIL.println('++++++++++ ');
1903 GMI_RESERVATION_UTIL.println('Delivery_detail_id '||l_delivery_detail_id);
1904 GMI_RESERVATION_UTIL.println('Source_line_id '||l_source_line_id);
1905 GMI_RESERVATION_UTIL.println('Released_status '||l_released_status);
1906 GMI_RESERVATION_UTIL.println('Lot_number '||l_lot_number);
1907 GMI_RESERVATION_UTIL.println('Sublot_number '||l_sublot_number);
1908 GMI_RESERVATION_UTIL.println('++++++++++ ');
1909 END LOOP;
1910 CLOSE get_bad_delivery;
1911 -- HW END OF BUG#:1826752
1912
1913
1914 /* When a backorder line has been created in oe_order_line_all, */
1915 /* the outstanding pending transactions must be updated to point */
1916 /* to the new line_id. */
1917 /* The shipping line must be updated to align with backorder */
1918 /* default lot transactions (cycle count quantity) */
1919
1920 /* B1504749, 5-Dec-2000 odaboval : added released_status clause */
1921 -- HW BUG#:1854224 closed the comment properly
1922 /* HW BUG#:1826752 exclude shipped (C) and staged (Y) */
1923 UPDATE wsh_delivery_details
1924 SET locator_id = NULL,
1925 lot_number = NULL,
1926 sublot_number = NULL
1927 WHERE source_line_id = p_source_line_id
1928 AND released_status NOT IN ('Y','C')
1929 AND p_action_flag = 'B';
1930 /* HAM RE_CHECK */
1931 /* and ship_confirm_action_flag = 'B'; */
1932 IF (SQL%NOTFOUND)
1933 THEN
1934 GMI_RESERVATION_UTIL.println('No Update in wsh_delivery_details, line_id='||p_source_line_id);
1935
1936 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1937 -- RAISE NO_DATA_FOUND;
1938 END IF;
1939
1940 GMI_RESERVATION_UTIL.println('New Line Id => '|| p_source_line_id );
1941 GMI_RESERVATION_UTIL.println('Orig Line Id => '|| p_original_source_line_id);
1942
1943
1944
1945 /* this only happens for non control item where the shared default lot is not good */
1946 /*IF p_action_flag = 'B' THEN
1947 UPDATE ic_tran_pnd
1948 SET line_id = p_source_line_id ,
1949 staged_ind = 0
1950 WHERE line_id = p_original_source_line_id and
1951 doc_type = 'OMSO' and
1952 completed_ind = 0 and
1953 delete_mark = 0;
1954 ELSE*/
1955 UPDATE ic_tran_pnd
1956 SET line_id = p_source_line_id
1957 WHERE line_id = p_original_source_line_id and
1958 doc_type = 'OMSO' and
1959 completed_ind = 0 and
1960 delete_mark = 0;
1961 --END IF;
1962 IF (SQL%NOTFOUND) THEN
1963 /* bug 1783859 */
1964 /*for a pure back order line, this trans may not exsit, no error should be returned*/
1965 GMI_RESERVATION_UTIL.println('No Update in ic_tran_pnd, line_id='||p_source_line_id);
1966 --x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1967 --RAISE NO_DATA_FOUND;
1968 END IF;
1969
1970
1971 GMI_RESERVATION_UTIL.println('At the end of GMI_Apply_BacKOrder_Updated, No Error.');
1972
1973 EXCEPTION
1974 WHEN NO_DATA_FOUND
1975 THEN
1976 GMI_RESERVATION_UTIL.println('No Data Found raised');
1977 WHEN OTHERS
1978 THEN
1979 x_return_status := FND_API.G_RET_STS_ERROR;
1980 GMI_RESERVATION_UTIL.println('RAISE WHEN OTHERS');
1981 END GMI_APPLY_BACKORDER_UPDATES;
1982
1983 /* NC Added the following two procedures. Bug#1675561 */
1984 PROCEDURE UPDATE_OPM_TRANSACTION
1985 ( p_old_delivery_detail_id IN NUMBER,
1986 p_lot_number IN VARCHAR2,
1987 p_sublot_number IN VARCHAR2,
1988 p_organization_id IN NUMBER,
1989 p_inventory_item_id IN NUMBER,
1990 p_old_source_line_id IN NUMBER,
1991 p_locator_id IN NUMBER,
1992 p_new_delivery_detail_id IN NUMBER,
1993 p_old_req_quantity IN NUMBER,
1994 p_old_req_quantity2 IN NUMBER,
1995 p_req_quantity IN NUMBER,
1996 p_req_quantity2 IN NUMBER DEFAULT NULL,
1997 x_return_status OUT NOCOPY VARCHAR2,
1998 x_msg_count OUT NOCOPY NUMBER,
1999 x_msg_data OUT NOCOPY VARCHAR2)
2000 IS
2001 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2002 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2003 l_old_transaction_row ic_tran_pnd%ROWTYPE ;
2004 l_new_transaction_row ic_tran_pnd%ROWTYPE;
2005 l_line_id ic_tran_pnd.line_id%TYPE;
2006 l_item_id ic_tran_pnd.item_id%TYPE;
2007 l_location ic_tran_pnd.location%TYPE;
2008 l_lot_id ic_tran_pnd.lot_id%TYPE;
2009 l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
2010 l_reduction_factor NUMBER;
2011 l_delta_trans_qty2 NUMBER;
2012 l_trans_id NUMBER;
2013 l_staged_ind NUMBER;
2014 l_lot_ctl NUMBER;
2015 l_loct_ctl NUMBER;
2016 l_whse_ctl NUMBER;
2017 l_lock_status BOOLEAN;
2018 l_count NUMBER;
2019
2020 /* Begin Enhancement 2320442 - Lakshmi Swamy */
2021 l_noninv_ind NUMBER;
2022
2023 /* Commented out the following cursor and rewrote it */
2024
2025 /* CURSOR is_only_default IS
2026 Select opm.lot_ctl
2027 , opm.loct_ctl
2028 , whse.loct_ctl
2029 From ic_item_mst opm
2030 , mtl_system_items mtl
2031 , ic_whse_mst whse
2032 Where mtl.inventory_item_id = p_inventory_item_id
2033 and mtl.organization_id = p_organization_id
2034 and mtl.segment1 = opm.item_no
2035 and whse.mtl_organization_id = p_organization_id; */
2036
2037 CURSOR is_only_default IS
2038 Select noninv_ind
2039 From ic_item_mst opm,mtl_system_items mtl
2040 Where mtl.inventory_item_id = p_inventory_item_id
2041 and mtl.organization_id = p_organization_id
2042 and mtl.segment1 = opm.item_no;
2043
2044 /* End Enhancement 2320442 - Lakshmi Swamy */
2045
2046 CURSOR fetch_opm_transaction
2047 ( p_old_delievery_detail_id NUMBER,
2048 p_old_source_line_id NUMBER
2049 ) IS
2050 SELECT trans_id from
2051 ic_tran_pnd
2052 WHERE line_id = p_old_source_line_id
2053 AND line_detail_id = p_old_delivery_detail_id
2054 AND completed_ind = 0
2055 AND delete_mark = 0
2056 AND staged_ind = 1;
2057
2058 BEGIN
2059 SAVEPOINT update_txn;
2060
2061 /* First if the item is non inv or non controled, it only has default
2062 no split for a default transaction */
2063
2064 /* Begin Enhancement 2320442, 2901317 - Lakshmi Swamy */
2065
2066 /* OPEN is_only_default;
2067 FETCH is_only_default into l_lot_ctl, l_loct_ctl,l_whse_ctl;
2068 CLOSE is_only_default;
2069
2070 IF (l_lot_ctl = 0 AND (l_loct_ctl * l_whse_ctl ) = 0) THEN
2071 RETURN;
2072 END IF; */
2073
2074 /* OPEN is_only_default;
2075 FETCH is_only_default into l_noninv_ind;
2076 CLOSE is_only_default;
2077
2078 IF (l_noninv_ind = 1) THEN
2079 RETURN;
2080 END IF; */
2081
2082 /* End Enhancement 2320442, 2901317 - Lakshmi Swamy */
2083
2084 SELECT iim.item_id INTO l_item_id
2085 FROM ic_item_mst iim,
2086 mtl_system_items msi
2087 WHERE msi.inventory_item_id = p_inventory_item_id
2088 AND msi.organization_id = p_organization_id
2089 AND msi.segment1 = iim.item_no;
2090
2091 IF p_locator_id IS NULL
2092 THEN
2093 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
2094 ELSE
2095 SELECT location INTO l_location
2096 FROM ic_loct_mst
2097 WHERE inventory_location_id = p_locator_id;
2098 END IF;
2099
2100 GMI_RESERVATION_UTIL.println('LOT_NUMBER => ' || p_lot_number);
2101 GMI_RESERVATION_UTIL.println('SUBLOT_NUMBER => ' || p_sublot_number);
2102
2103 IF p_lot_number IS NULL
2104 THEN
2105 l_lot_id := 0;
2106 ELSIF p_sublot_number IS NULL
2107 THEN
2108 SELECT lot_id INTO l_lot_id
2109 FROM ic_lots_mst
2110 WHERE item_id = l_item_id
2111 AND lot_no = p_lot_number
2112 AND sublot_no IS NULL ;
2113 ELSE
2114 SELECT lot_id INTO l_lot_id
2115 FROM ic_lots_mst
2116 WHERE item_id = l_item_id
2117 AND lot_no = p_lot_number
2118 AND sublot_no = p_sublot_number;
2119 END IF;
2120
2121 /* With the above retrievals successfully done we can */
2122 /* try to locate the transaction we need. Again, if this fails */
2123 /* we cannot proceed so let the exception raised take over. */
2124
2125 GMI_RESERVATION_UTIL.println('Retrieving OLD TRX in UPDATE_OPM_TRANSACTION ');
2126 GMI_RESERVATION_UTIL.println('LINE_ID => ' || p_old_source_line_id);
2127 GMI_RESERVATION_UTIL.println('LINE_DETAIL_ID => ' || p_old_delivery_detail_id);
2128 GMI_RESERVATION_UTIL.println('ITEM_ID => ' || l_item_id);
2129 GMI_RESERVATION_UTIL.println('LOT_ID => ' || l_lot_id);
2130 GMI_RESERVATION_UTIL.println('Location => ' || l_location);
2131
2132 OPEN fetch_opm_transaction(p_old_delivery_detail_id,
2133 p_old_source_line_id
2134 );
2135 FETCH fetch_opm_transaction INTO l_old_transaction_rec.trans_id;
2136 IF(fetch_opm_transaction%NOTFOUND) THEN
2137 CLOSE fetch_opm_transaction;
2138 GMI_RESERVATION_UTIL.println('Transaction Not Found in UPDATE_OPM_TRXS');
2139 RAISE NO_DATA_FOUND;
2140 END IF;
2141
2142 CLOSE fetch_opm_transaction;
2143
2144 GMI_RESERVATION_UTIL.println('Retrieve OPM Transaction => ' ||l_old_transaction_rec.trans_id );
2145 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND (l_old_transaction_rec, l_old_transaction_rec )
2146 THEN
2147 GMI_reservation_Util.printLn('OPM Transaction found in OPM_UPDATE_TRXS');
2148 -- PK Bug 3527599 No need to lock IC_LOCT_INV when updating / creating pending txn.
2149
2150 GMI_RESERVATION_UTIL.println('Correct Transaction Found');
2151
2152 PRINT_DEBUG (l_old_transaction_rec,'FETCH RECORD');
2153
2154 l_old_transaction_rec.trans_qty := -1 * (p_old_req_quantity - p_req_quantity);
2155 l_old_transaction_rec.trans_qty2 := -1 * (p_old_req_quantity2 - p_req_quantity2);
2156
2157 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
2158 ( 1
2159 , FND_API.G_FALSE
2160 , FND_API.G_FALSE
2161 , FND_API.G_VALID_LEVEL_FULL
2162 , l_old_transaction_rec
2163 , l_old_transaction_row
2164 , x_return_status
2165 , x_msg_count
2166 , x_msg_data
2167 );
2168
2169 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2170 THEN
2171 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
2172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2173 END IF;
2174
2175 l_new_transaction_rec := l_old_transaction_rec;
2176 l_new_transaction_rec.line_detail_id := p_new_delivery_detail_id;
2177 l_new_transaction_rec.trans_id := NULL;
2178 l_new_transaction_rec.trans_qty := -1 * (p_req_quantity);
2179 l_new_transaction_rec.trans_qty2 := -1 * (p_req_quantity2);
2180
2181 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
2182 ( 1
2183 , FND_API.G_FALSE
2184 , FND_API.G_FALSE
2185 , FND_API.G_VALID_LEVEL_FULL
2186 , l_new_transaction_rec
2187 , l_new_transaction_row
2188 , x_return_status
2189 , x_msg_count
2190 , x_msg_data
2191 );
2192
2193 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
2194 THEN
2195 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 END IF;
2198
2199 END IF;
2200
2201 EXCEPTION
2202 WHEN NO_DATA_FOUND THEN
2203 rollback to update_txn;
2204 x_return_status := FND_API.G_RET_STS_ERROR;
2205 RAISE NO_DATA_FOUND;
2206 GMI_RESERVATION_UTIL.Println('Raised When No Data Found in UPDATE_OPM_TRANSACTION');
2207
2208 WHEN OTHERS THEN
2209 rollback to update_txn;
2210 x_return_status := FND_API.G_RET_STS_ERROR;
2211 GMI_RESERVATION_UTIL.Println('Raised When Others in UPDATE_OPM_TRANSACTION');
2212 END UPDATE_OPM_TRANSACTION;
2213
2214 PROCEDURE UPDATE_OPM_IC_TRAN_PND
2215 ( p_delivery_detail_id IN NUMBER,
2216 p_trans_id IN NUMBER,
2217 p_staged_flag IN NUMBER) IS
2218
2219 BEGIN
2220 IF ( p_delivery_detail_id IS NOT NULL AND p_trans_id is not NULL) THEN
2221
2222 IF(p_staged_flag = 0) THEN
2223 UPDATE ic_tran_pnd
2224 SET line_detail_id = p_delivery_detail_id
2225 WHERE trans_id = p_trans_id ;
2226 ELSIF (p_staged_flag = 1) THEN
2227 UPDATE ic_tran_pnd
2228 SET line_detail_id = p_delivery_detail_id,
2229 staged_ind = 1
2230 WHERE trans_id = p_trans_id ;
2231 END IF;
2232
2233 END IF;
2234
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 GMI_RESERVATION_UTIL.println('*** In update_opm_ic_tran_pnd');
2238 GMI_RESERVATION_UTIL.println('*** When Others exception raised');
2239 END UPDATE_OPM_IC_TRAN_PND;
2240
2241 PROCEDURE PRINT_DEBUG
2242 (
2243 p_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
2244 ,p_routine IN VARCHAR2
2245 )
2246 IS
2247 BEGIN
2248
2249 GMI_RESERVATION_UTIL.println(' *** Called From -> ' || p_routine );
2250 GMI_RESERVATION_UTIL.println(' TRANS_ID -> ' || p_tran_rec.trans_id);
2251 GMI_RESERVATION_UTIL.println(' ITEM_ID -> ' || p_tran_rec.item_id);
2252 GMI_RESERVATION_UTIL.println(' LINE_ID -> ' || p_tran_rec.line_id);
2253 GMI_RESERVATION_UTIL.println(' CO_CODE -> ' || p_tran_rec.co_code);
2254 GMI_RESERVATION_UTIL.println(' ORGN_CODE -> ' || p_tran_rec.orgn_code);
2255 GMI_RESERVATION_UTIL.println(' WHSE_CODE -> ' || p_tran_rec.whse_code);
2256 GMI_RESERVATION_UTIL.println(' LOT_ID -> ' || p_tran_rec.lot_id);
2257 GMI_RESERVATION_UTIL.println(' LOCATION -> ' || p_tran_rec.location);
2258 GMI_RESERVATION_UTIL.println(' DOC_ID -> ' || p_tran_rec.doc_id);
2259 GMI_RESERVATION_UTIL.println(' DOC_TYPE -> ' || p_tran_rec.doc_type);
2260 GMI_RESERVATION_UTIL.println(' DOC_LINE -> ' || p_tran_rec.doc_line);
2261 GMI_RESERVATION_UTIL.println(' LINE_TYPE -> ' || p_tran_rec.line_type);
2262 GMI_RESERVATION_UTIL.println(' REAS_CODE -> ' || p_tran_rec.reason_code);
2263 GMI_RESERVATION_UTIL.println(' TRANS_DATE -> ' || p_tran_rec.trans_date);
2264 GMI_RESERVATION_UTIL.println(' TRANS_QTY -> ' || p_tran_rec.trans_qty);
2265 GMI_RESERVATION_UTIL.println(' TRANS_QTY2 -> ' || p_tran_rec.trans_qty2);
2266 GMI_RESERVATION_UTIL.println(' QC_GRADE -> ' || p_tran_rec.qc_grade);
2267 GMI_RESERVATION_UTIL.println(' LOT_STATUS -> ' || p_tran_rec.lot_status);
2268 GMI_RESERVATION_UTIL.println(' TRANS_STAT -> ' || p_tran_rec.trans_stat);
2269 GMI_RESERVATION_UTIL.println(' TRANS_UM -> ' || p_tran_rec.trans_um);
2270 GMI_RESERVATION_UTIL.println(' TRANS_UM2 -> ' || p_tran_rec.trans_um2);
2271 GMI_RESERVATION_UTIL.println(' USER_ID -> ' || p_tran_rec.user_id);
2272 GMI_RESERVATION_UTIL.println(' TEXT_CODE -> ' || p_tran_rec.text_code);
2273 GMI_RESERVATION_UTIL.println(' NON_INV -> ' || p_tran_rec.non_inv);
2274 GMI_RESERVATION_UTIL.println(' STAGED_IND -> ' || p_tran_rec.staged_ind);
2275
2276 END PRINT_DEBUG;
2277
2278 /* this procedure creates a row in rcv_transaction_interface and calls the
2279 reveiving transaction processer
2280 */
2281
2282 PROCEDURE create_rcv_transaction
2283 ( p_shipping_line IN wsh_delivery_details%ROWTYPE
2284 , p_trip_stop_rec IN wsh_trip_stops%ROWTYPE
2285 , p_group_id IN NUMBER
2286 , x_return_status OUT NOCOPY VARCHAR2
2287 , x_msg_count OUT NOCOPY NUMBER
2288 , x_msg_data OUT NOCOPY VARCHAR2)
2289 IS
2290
2291 l_delivery_id number := NULL;
2292 l_trx_source_type_id number := NULL;
2293 l_trx_action_id number := NULL;
2294 l_trx_type_code number := NULL;
2295 l_error_code number := NULL;
2296 l_error_text varchar2(2000) := NULL;
2297 l_ultimate_dropoff_date DATE := NULL;
2298 l_waybill varchar2(30) := NULL;
2299 l_total_ship_qty number := NULL;
2300 l_total_req_qty number := NULL;
2301 l_req_distribution_id NUMBER := NULL;
2302 l_transfer_subinventory VARCHAR2(10) := NULL;
2303 l_source_code VARCHAR2(40) := NULL;
2304 l_transfer_organization NUMBER := NULL;
2305 l_ship_to_location_id NUMBER := NULL;
2306 l_requisition_line_id NUMBER :=NULL;
2307 x_error_code VARCHAR2(240) := NULL;
2308 l_sales_order_id NUMBER := NULL;
2309 l_account NUMBER := NULL;
2310 l_group_id NUMBER := NULL;
2311 rc NUMBER := NULL;
2312 l_transaction_id NUMBER := NULL;
2313 l_charge_account_id NUMBER;
2314 l_accrual_account_id NUMBER;
2315 l_detail_rec wsh_delivery_details%ROWTYPE;
2316 l_trip_stop_rec wsh_trip_stops%ROWTYPE;
2317 l_item_desc VARCHAR2(240) := NULL;
2318 l_unit_of_measure VARCHAR2(25);
2319 l_secondary_unit_of_measure VARCHAR2(25);
2320 l_locator_id NUMBER;
2321 l_subinventory VARCHAR2(10) := NULL;
2322 l_ctl_ind VARCHAR2(1) := 'N';
2323 l_del_ship_qty NUMBER; -- B3925583
2324 l_del_ship_qty_uom VARCHAR2(25); -- B3925583
2325 CURSOR c_order_line_info(c_order_line_id number) is
2326 SELECT source_document_type_id
2327 , source_document_id
2328 , source_document_line_id
2329 from oe_order_lines_all
2330 where line_id = c_order_line_id;
2331 l_order_line_info c_order_line_info%ROWTYPE;
2332
2333 CURSOR c_delivery_info is
2334 SELECT a.delivery_id
2335 , d.waybill
2336 , d.ultimate_dropoff_date
2337 from wsh_delivery_assignments a,
2338 wsh_delivery_details dd,
2339 wsh_new_deliveries d
2340 where a.delivery_detail_id = dd.delivery_detail_id
2341 and d.delivery_id = a.delivery_id
2342 and dd.delivery_detail_id = p_shipping_line.delivery_detail_id
2343 and NVL(dd.container_flag, 'N') = 'N';
2344
2345 CURSOR c_po_info(c_po_line_id number, c_source_document_id number) is
2346 SELECT destination_type_code,
2347 destination_subinventory,
2348 source_organization_id,
2349 destination_organization_id,
2350 deliver_to_location_id,
2351 pl.requisition_line_id,
2352 pd.distribution_id
2353 from po_requisition_lines_all pl,
2354 po_req_distributions_all pd
2355 where pl.requisition_line_id = c_po_line_id
2356 and pl.requisition_header_id = c_source_document_id
2357 and pl.requisition_line_id = pd.requisition_line_id;
2358 l_po_info c_po_info%ROWTYPE;
2359
2360 Cursor get_item_desc IS
2361 Select description
2362 From mtl_system_items
2363 Where inventory_item_id = p_shipping_line.inventory_item_id
2364 and organization_id = p_shipping_line.organization_id;
2365
2366 CURSOR c_mtl_interorg_parameters (c_from_organization_id NUMBER , c_to_organization_id NUMBER) IS
2367 SELECT intransit_type
2368 FROM mtl_interorg_parameters
2369 WHERE from_organization_id = c_from_organization_id AND
2370 to_organization_id = c_to_organization_id;
2371
2372 Cursor get_default_loct (p_org_id number) IS
2373 Select locator_id
2374 , subinventory_code
2375 From MTL_ITEM_LOC_DEFAULTS
2376 Where inventory_item_id = p_shipping_line.inventory_item_id
2377 and organization_id = p_org_id;
2378
2379 Cursor get_default_sub (p_org_id number) IS
2380 Select subinventory_code
2381 From MTL_ITEM_SUB_DEFAULTS
2382 Where inventory_item_id = p_shipping_line.inventory_item_id
2383 and organization_id = p_org_id;
2384
2385 l_intransit_type NUMBER;
2386
2387 BEGIN
2388 l_detail_rec := p_shipping_line;
2389 l_trip_stop_rec := p_trip_stop_rec;
2390 OPEN c_order_line_info(l_detail_rec.source_line_id);
2391 FETCH c_order_line_info into l_order_line_info;
2392 if (c_order_line_info%NOTFOUND) THEN
2393 CLOSE c_order_line_info;
2394 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2395 GMI_RESERVATION_UTIL.Println('Sales order not valid');
2396 return;
2397 END if;
2398 CLOSE c_order_line_info;
2399
2400 IF (l_order_line_info.source_document_type_id <> 10) THEN /* internal order */
2401 RETURN;
2402 ELSE
2403 GMI_RESERVATION_UTIL.Println('This line is part of an OPM internal order');
2404
2405 OPEN c_delivery_info;
2406 FETCH c_delivery_info
2407 into l_delivery_id
2408 , l_waybill
2409 , l_ultimate_dropoff_date
2410 ;
2411 if (c_delivery_info%NOTFOUND) THEN
2412 CLOSE c_delivery_info;
2413 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2414 GMI_RESERVATION_UTIL.Println('Delivery Detail is not assigned to any delivery');
2415 return;
2416 -- raise NOT_ASSIGNED_TO_DEL_ERROR;
2417 END if;
2418 CLOSE c_delivery_info;
2419
2420 /* only for internal purchase orders, we need to fetch the po info */
2421 OPEN c_po_info(l_order_line_info.source_document_line_id,
2422 l_order_line_info.source_document_id);
2423 FETCH c_po_info into l_po_info;
2424 if c_po_info%NOTFOUND then
2425 CLOSE c_po_info;
2426 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2427 WSH_UTIL_CORE.Println('Requisition line not found');
2428 return;
2429 end if;
2430 CLOSE c_po_info;
2431 GMI_RESERVATION_UTIL.Println('Requisition line id:' || l_order_line_info.source_document_line_id);
2432 GMI_RESERVATION_UTIL.Println('Destination_type_code:' ||l_po_info.destination_type_code );
2433 GMI_RESERVATION_UTIL.Println('Source organization id:' ||l_po_info.source_organization_id );
2434 GMI_RESERVATION_UTIL.Println('Destination organization id:' ||l_po_info.destination_organization_id );
2435
2436 l_transfer_subinventory := l_po_info.destination_subinventory;
2437 l_transfer_organization := l_po_info.destination_organization_id;
2438 l_requisition_line_id := l_po_info.requisition_line_id;
2439 l_ship_to_location_id := l_po_info.deliver_to_location_id;
2440 l_req_distribution_id := l_po_info.distribution_id;
2441
2442 IF (l_po_info.destination_type_code = 'EXPENSE') THEN
2443 GMI_RESERVATION_UTIL.println('Store issue');
2444 l_trx_source_type_id := 8;
2445 l_trx_action_id := 1;
2446 l_trx_type_code := 34 /* Store_issue */;
2447 ELSIF (l_po_info.destination_type_code = 'INVENTORY') AND
2448 (l_po_info.source_organization_id = l_po_info.destination_organization_id) THEN
2449 GMI_RESERVATION_UTIL.println('Sub inventory transfer');
2450 l_trx_source_type_id := 8;
2451 l_trx_action_id := 2;
2452 l_trx_type_code := 50 /* Subinv_xfer */;
2453 ELSIF (l_po_info.destination_organization_id <> l_po_info.source_organization_id) THEN
2454 /* Bug 2137423, check mtl_interorg_parameters to decide transaction codes */
2455 OPEN c_mtl_interorg_parameters( l_po_info.source_organization_id,
2456 l_po_info.destination_organization_id);
2457 FETCH c_mtl_interorg_parameters INTO l_intransit_type;
2458 IF c_mtl_interorg_parameters%NOTFOUND THEN
2459 /* default to intransit */
2460 GMI_RESERVATION_UTIL.println('In transit shipment');
2461 l_trx_source_type_id := 8;
2462 l_trx_action_id := 21;
2463 l_trx_type_code := 62; /* intransit_shpmnt */
2464 ELSE
2465 IF l_intransit_type = 1 THEN
2466 GMI_RESERVATION_UTIL.println('In direct shipment');
2467 l_trx_source_type_id := 8;
2468 l_trx_action_id := 3;
2469 l_trx_type_code := 54; /* direct shipment */
2470 ELSE
2471 GMI_RESERVATION_UTIL.println('In transit shipment');
2472 l_trx_source_type_id := 8;
2473 l_trx_action_id := 21;
2474 l_trx_type_code := 62; /* intransit_shpmnt */
2475 END IF;
2476 END IF;
2477 CLOSE c_mtl_interorg_parameters;
2478 END IF;
2479 --If for direct receipt then
2480 IF l_trx_action_id = 3 THEN
2481 --check if item is location controlled
2482 --bug 3380763
2483 check_loct_ctl
2484 ( p_inventory_item_id => p_shipping_line.inventory_item_id
2485 ,p_mtl_organization_id => l_po_info.destination_organization_id
2486 ,x_ctl_ind => l_ctl_ind
2487 ) ;
2488 --if yes then get default subinv and locator from MTL_ITEM_LOC_DEFAULTS
2489 IF l_ctl_ind = 'Y' THEN
2490 Open get_default_loct(l_po_info.destination_organization_id) ;
2491 Fetch get_default_loct into l_locator_id, l_subinventory;
2492 IF get_default_loct%NOTFOUND THEN
2493 /* check to see the item is location ctl, if not, raise error */
2494 GMI_RESERVATION_UTIL.println('Item and the dest Org are location controlled,
2495 but no default location setup');
2496 FND_MESSAGE.Set_Name('GMI','GMI_LOCATION_ERROR');
2497 FND_MESSAGE.Set_Token('BY_PROC', 'NO DESTINATION DEFAULT_LOC SETUP');
2498 FND_MESSAGE.Set_Token('WHERE', 'GMI_Create_rcv_trans');
2499 FND_MSG_PUB.Add;
2500 Close get_default_loct; /* B2886561 close cursor before exception */
2501 /* Bug #3415847 punkumar,commeting out the exception so that it does not stop insertion of record in RTI
2502 RAISE FND_API.G_EXC_ERROR;
2503 */
2504 END IF;
2505 --esle if item not location cotrolled then get def subinv from MTL_ITEM_SUB_DEFAULTS
2506 ELSIF l_ctl_ind = 'N' THEN
2507 Open get_default_sub(l_po_info.destination_organization_id) ;
2508 Fetch get_default_sub into l_subinventory;
2509 IF get_default_sub%NOTFOUND THEN
2510 /* check to see the item is location ctl, if not, raise error */
2511 GMI_RESERVATION_UTIL.println('no default subinventory setup for direct receipt');
2512 FND_MESSAGE.Set_Name('GMI','GMI_LOCATION_ERROR');
2513 FND_MESSAGE.Set_Token('BY_PROC', 'NO DESTINATION DEFAULT_SUB SETUP');
2514 FND_MESSAGE.Set_Token('WHERE', 'GMI_Create_rcv_trans');
2515 FND_MSG_PUB.Add;
2516 Close get_default_sub; /* B2886561 close cursor before exception */
2517 /* Bug #3415847 punkumar,commeting out the exception so that it does not stop insertion of record in RTI
2518 RAISE FND_API.G_EXC_ERROR;
2519 */
2520 END IF;
2521 END IF;
2522 --Close cursors.
2523 IF get_default_loct%ISOPEN THEN
2524 Close get_default_loct;
2525 END IF;
2526 IF get_default_sub%ISOPEN THEN
2527 Close get_default_sub;
2528 END IF;
2529 END IF;
2530
2531 --l_account := PO_REQ_DIST_SV1.get_dist_account( l_requisition_line_id ) ; -- Bug 1610178
2532
2533 /*if ( l_account = -11 ) then
2534 wsh_util_core.println ( 'Error: More than one Distribution accounts ' || l_account );
2535 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2536 return;
2537 elsif ( l_account is null ) then
2538 wsh_util_core.println ( 'Error: Cannot get Distribution account ' || l_account );
2539 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING ;
2540 return;
2541 end if ;
2542
2543 wsh_util_core.println ( 'Distribution account is ' || l_account );
2544 */
2545 if (l_detail_rec.source_code = 'OE') then
2546 if (WSH_SHIP_CONFIRM_ACTIONS.ont_source_code is NULL) then
2547 WSH_SHIP_CONFIRM_ACTIONS.ont_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
2548 end if;
2549 l_source_code := WSH_SHIP_CONFIRM_ACTIONS.ont_source_code;
2550 else
2551 l_source_code := 'OKE';
2552 end if;
2553 GMI_RESERVATION_UTIL.get_OPM_account
2554 (
2555 v_dest_org_id => l_po_info.destination_organization_id,
2556 v_apps_item_id => l_detail_rec.inventory_item_id,
2557 v_vendor_site_id => l_detail_rec.org_id ,
2558 x_cc_id => l_charge_account_id,
2559 x_ac_id => l_accrual_account_id
2560 );
2561
2562 GMI_RESERVATION_UTIL.println('Source_code:'|| l_source_code);
2563 GMI_RESERVATION_UTIL.println('charge_account_id:'|| l_charge_account_id);
2564
2565 Open get_item_desc;
2566 Fetch get_item_desc INTO l_item_desc;
2567 Close get_item_desc;
2568
2569 GMI_RESERVATION_UTIL.println('Inserting Detail ' || l_detail_rec.delivery_detail_id || ' into RCV_TRANSACTIONS_INTERFACE ');
2570 select RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL into l_transaction_id from dual;
2571 l_group_id := p_group_id;
2572
2573 l_secondary_unit_of_measure := null;
2574
2575 /*bug 2316449 - Fetch the 25 char Unit of Measure from mtl_units_of_measure for both the UOMs*/
2576 If l_detail_rec.requested_quantity_uom is not null then
2577 select UNIT_OF_MEASURE
2578 into l_unit_of_measure
2579 from mtl_units_of_measure
2580 where uom_code =l_detail_rec.requested_quantity_uom;
2581 End if;
2582
2583 If l_detail_rec.requested_quantity_uom2 is not null then
2584 select UNIT_OF_MEASURE
2585 into l_secondary_unit_of_measure
2586 from mtl_units_of_measure
2587 where uom_code =l_detail_rec.requested_quantity_uom2 ;
2588 End if;
2589
2590 -- PK Begin Bug 3925583
2591
2592 l_del_ship_qty := l_detail_rec.shipped_quantity;
2593 l_del_ship_qty_uom := l_detail_rec.src_requested_quantity_uom;
2594
2595 IF (l_detail_rec.requested_quantity_uom2 is not null) AND ( l_detail_rec.src_requested_quantity_uom = l_detail_rec.src_requested_quantity_uom2) THEN
2596 l_del_ship_qty := l_detail_rec.shipped_quantity2;
2597 l_del_ship_qty_uom := l_detail_rec.src_requested_quantity_uom2;
2598 l_unit_of_measure := l_secondary_unit_of_measure;
2599
2600 END IF;
2601 -- PK End Bug 3925583
2602 -- PK Also changed values to these local variable for Insert into rcv_transactions_interface
2603
2604 GMI_RESERVATION_UTIL.println('transaction_id ' || l_transaction_id);
2605 GMI_RESERVATION_UTIL.println('group_id ' || l_group_id);
2606 GMI_RESERVATION_UTIL.println('interface_source_code ' || l_source_code);
2607 GMI_RESERVATION_UTIL.println('interface_source_line_id ' || l_detail_rec.source_line_id);
2608 GMI_RESERVATION_UTIL.println('last_update_date ' || sysdate);
2609 GMI_RESERVATION_UTIL.println('last_updated_by ' || FND_GLOBAL.user_id);
2610 GMI_RESERVATION_UTIL.println('creation_date ' || sysdate);
2611 GMI_RESERVATION_UTIL.println('created_by ' || FND_GLOBAL.user_id);
2612 GMI_RESERVATION_UTIL.println('item_id ' || l_detail_rec.inventory_item_id);
2613 GMI_RESERVATION_UTIL.println('item_revision ' || l_detail_rec.revision);
2614 GMI_RESERVATION_UTIL.println('from_org_id ' || l_detail_rec.organization_id);
2615 GMI_RESERVATION_UTIL.println('to_org_id ' || l_transfer_organization);
2616 GMI_RESERVATION_UTIL.println('intrasit_owning_org_id ' || l_transfer_organization);
2617 GMI_RESERVATION_UTIL.println('quantity ' || l_del_ship_qty);
2618 GMI_RESERVATION_UTIL.println('unit_of_measure ' || l_detail_rec.requested_quantity_uom);
2619 GMI_RESERVATION_UTIL.println('uom_code ' || l_del_ship_qty_uom);
2620 GMI_RESERVATION_UTIL.println('secondary_quantity ' || l_detail_rec.shipped_quantity2);
2621 GMI_RESERVATION_UTIL.println('secondary_uom_code ' || l_detail_rec.requested_quantity_uom2);
2622 GMI_RESERVATION_UTIL.println('secondary_unit_of_measure ' || l_secondary_unit_of_measure);
2623 GMI_RESERVATION_UTIL.println('primary_qty ' || null);
2624 GMI_RESERVATION_UTIL.println('primary_uom ' || null);
2625 GMI_RESERVATION_UTIL.println('transaction_type ' || 'SHIP');
2626 GMI_RESERVATION_UTIL.println('transaction_date ' || l_trip_stop_rec.actual_departure_date);
2627 GMI_RESERVATION_UTIL.println('shipment_num ' || l_delivery_id);
2628 GMI_RESERVATION_UTIL.println('freight_carrier_code ' || null);
2629 GMI_RESERVATION_UTIL.println('transfer_cost ' || null);
2630 GMI_RESERVATION_UTIL.println('transportation_cost ' || null);
2631 GMI_RESERVATION_UTIL.println('transportation_account ' || null);
2632 GMI_RESERVATION_UTIL.println('number_of_containers ' || null);
2633 GMI_RESERVATION_UTIL.println('waybill ' || l_waybill);
2634 GMI_RESERVATION_UTIL.println('inventory_transaction_id ' || null);
2635 GMI_RESERVATION_UTIL.println('destination_type_code ' || l_po_info.destination_type_code);
2636 --GMI_RESERVATION_UTIL.println('transaction_action_id ' || decode(l_trx_action_id,2,'DELIVER',3,'DELIVER',1,'DELIVER','SHIP'));
2637 GMI_RESERVATION_UTIL.println('receipt_source_code ' || 'INTERNAL ORDER');
2638 GMI_RESERVATION_UTIL.println('source_document_code ' || 'REQ');
2639 GMI_RESERVATION_UTIL.println('processing_status_code ' || 'RUNNING');
2640 GMI_RESERVATION_UTIL.println('transaction_status_code ' || 'PENDING');
2641 GMI_RESERVATION_UTIL.println('processing_code_mode ' || 'ONLINE');
2642 GMI_RESERVATION_UTIL.println('from_subinventory ' || l_detail_rec.subinventory);
2643 GMI_RESERVATION_UTIL.println('subinventory ' || l_transfer_subinventory);
2644 GMI_RESERVATION_UTIL.println('locator_id ' || l_detail_rec.locator_id);
2645 GMI_RESERVATION_UTIL.println('category_id ' || null);
2646 GMI_RESERVATION_UTIL.println('expected_receipt_date ' || l_ultimate_dropoff_date);
2647 GMI_RESERVATION_UTIL.println('currency_code ' || null);
2648 GMI_RESERVATION_UTIL.println('currency_conversion_rate ' || null);
2649 GMI_RESERVATION_UTIL.println('currency_conversion_date ' || SYSDATE);
2650 GMI_RESERVATION_UTIL.println('currency_conversion_type ' || null);
2651 GMI_RESERVATION_UTIL.println('ussgl_transaction_code ' || null);
2652 GMI_RESERVATION_UTIL.println('ship_to_location_id ' || l_ship_to_location_id);
2653 GMI_RESERVATION_UTIL.println('requisition_line_id ' || l_requisition_line_id);
2654 GMI_RESERVATION_UTIL.println('req_distribution_id ' || l_req_distribution_id);
2655 GMI_RESERVATION_UTIL.println('item_description ' || l_item_desc);
2656 GMI_RESERVATION_UTIL.println('shipped_date ' || l_trip_stop_rec.actual_departure_date);
2657 GMI_RESERVATION_UTIL.println('routing_header_id ' || null);
2658 GMI_RESERVATION_UTIL.println('reason_id ' || null);
2659 GMI_RESERVATION_UTIL.println('movement_id ' || null);
2660 GMI_RESERVATION_UTIL.println('transfer_percentage ' || null);
2661
2662 /* Bug # 3363725 , punkumar , modified insert sql to insert delivery_detail_id in comments column */
2663
2664 INSERT INTO RCV_TRANSACTIONS_INTERFACE
2665 (
2666 INTERFACE_TRANSACTION_ID,
2667 GROUP_ID,
2668 INTERFACE_SOURCE_CODE,
2669 INTERFACE_SOURCE_LINE_ID,
2670 LAST_UPDATE_DATE,
2671 LAST_UPDATED_BY,
2672 CREATION_DATE,
2673 CREATED_BY,
2674 ITEM_ID,
2675 ITEM_REVISION,
2676 FROM_ORGANIZATION_ID,
2677 TO_ORGANIZATION_ID,
2678 INTRANSIT_OWNING_ORG_ID,
2679 QUANTITY,
2680 UNIT_OF_MEASURE,
2681 UOM_CODE,
2682 SECONDARY_QUANTITY,
2683 SECONDARY_UOM_CODE,
2684 SECONDARY_UNIT_OF_MEASURE,
2685 PRIMARY_QUANTITY,
2686 PRIMARY_UNIT_OF_MEASURE,
2687 TRANSACTION_TYPE,
2688 TRANSACTION_DATE,
2689 SHIPMENT_NUM,
2690 FREIGHT_CARRIER_CODE,
2691 TRANSFER_COST,
2692 TRANSPORTATION_COST,
2693 TRANSPORTATION_ACCOUNT_ID,
2694 NUM_OF_CONTAINERS,
2695 WAYBILL_AIRBILL_NUM,
2696 INV_TRANSACTION_ID,
2697 DESTINATION_TYPE_CODE,
2698 AUTO_TRANSACT_CODE,
2699 RECEIPT_SOURCE_CODE,
2700 SOURCE_DOCUMENT_CODE,
2701 PROCESSING_STATUS_CODE,
2702 TRANSACTION_STATUS_CODE,
2703 PROCESSING_MODE_CODE,
2704 FROM_SUBINVENTORY,
2705 SUBINVENTORY,
2706 LOCATOR_ID,
2707 CATEGORY_ID,
2708 EXPECTED_RECEIPT_DATE,
2709 CURRENCY_CODE,
2710 CURRENCY_CONVERSION_RATE,
2711 CURRENCY_CONVERSION_DATE,
2712 CURRENCY_CONVERSION_TYPE,
2713 USSGL_TRANSACTION_CODE,
2714 SHIP_TO_LOCATION_ID,
2715 REQUISITION_LINE_ID,
2716 REQ_DISTRIBUTION_ID,
2717 ITEM_DESCRIPTION,
2718 SHIPPED_DATE,
2719 ROUTING_HEADER_ID,
2720 REASON_ID,
2721 MOVEMENT_ID,
2722 TRANSFER_PERCENTAGE,
2723 CHARGE_ACCOUNT_ID,
2724 DOCUMENT_SHIPMENT_LINE_NUM ,
2725 COMMENTS
2726 )
2727 VALUES
2728 (
2729 l_transaction_id, /* interface_transaction_id*/
2730 l_group_id, /* group_id*/
2731 l_source_code, /* interface_source_code */
2732 l_detail_rec.source_line_id, /* interface_source_line_id */
2733 sysdate, /* last_update_date */
2734 FND_GLOBAL.user_id, /* last_updated_by */
2735 sysdate, /* creation_date */
2736 FND_GLOBAL.user_id, /* created_by */
2737 l_detail_rec.inventory_item_id, /* item_id */
2738 l_detail_rec.revision, /* item_revision */
2739 l_detail_rec.organization_id, /* from_organization_id */
2740 l_transfer_organization, /* to_organization_id */
2741 l_transfer_organization, /* intransit_owning_org_id */
2742 l_del_ship_qty, /* quantity */
2743 l_unit_of_measure, /* unit_of_measure */
2744 l_del_ship_qty_uom, /* uom_code */
2745 l_detail_rec.shipped_quantity2, /* secondary_quantity */
2746 l_detail_rec.requested_quantity_uom2, /* secondary_uom_code */
2747 l_secondary_unit_of_measure, /* secondary_unit_of_measure */
2748 null, /* primary qty*/
2749 null, /* primary uom*/
2750 'SHIP', /* transaction_type ?*/
2751 l_trip_stop_rec.actual_departure_date, /* transaction_date */
2752 l_delivery_id, /* shipment_num */
2753 null, /* freight_carrier_code */
2754 null, /* transfer_cost */
2755 null, /* transportation_cost */
2756 null, /* transportation_account_id */
2757 null, /* number_of_containers */
2758 l_waybill, /* waybill_airbill_number*/
2759 null, /* inventory_transaction_id */
2760 l_po_info.destination_type_code, /* destination_type_code */
2761 decode(l_trx_action_id,1, 'DELIVER', 2,'DELIVER',3,'DELIVER','SHIP'), /*auto_transact_code*/
2762 'INTERNAL ORDER', /* Receipt_source_code*/
2763 'REQ', /* source_document_code*/
2764 'RUNNING', /* processing_status_code */
2765 'PENDING', /* transaction_status_code */
2766 'ONLINE', /* processing_code_mode */
2767 l_detail_rec.subinventory, /* from_subinventory*/
2768 decode(l_trx_action_id, 3, l_subinventory,l_transfer_subinventory), /* subinventory*/
2769 decode(l_trx_action_id, 3, l_locator_id, l_detail_rec.locator_id) , /* locator_id*/
2770 null, /* category_id*/
2771 l_ultimate_dropoff_date, /* expected_receipt_date */
2772 null, /* currency_code */
2773 null, /* currency_convertion_rate */
2774 SYSDATE, /* currency_convertion_date */
2775 null, /* currency_convertion_type */
2776 null, /* ussgl_transaction_code */
2777 l_ship_to_location_id, /* ship_to_location_id */
2778 l_requisition_line_id, /* requisition_line_id */
2779 l_req_distribution_id, /* req_distribution_id */
2780 l_item_desc, /* item_description */
2781 l_trip_stop_rec.actual_departure_date, /* shipped_date */
2782 null, /* routing_header_id */
2783 null, /* reason_id */
2784 null, /* movement_id */
2785 null, /* transfer_percentage */
2786 l_charge_account_id, /* charge_account_id */
2787 l_detail_rec.delivery_detail_id , /*DOCUMENT_SHIPMENT_LINE_NUM*/
2788 'OPM WDD:' || to_char(l_detail_rec.delivery_detail_id )
2789 );
2790 --decode(l_trx_action_id,3,l_transfer_organization,l_detail_rec.organization_id), /* from_organization_id */
2791 --decode(l_trx_action_id,3,l_detail_rec.organization_id,l_transfer_organization), /* to_organization_id */
2792 GMI_reservation_Util.PrintLn(
2793 'in create_rcv_transaction: for inserting in RCV_TRANSACTIONS_INTERFACE, sqlcode is '||SQLCODE||'.');
2794 End if;
2795 EXCEPTION
2796 WHEN OTHERS THEN
2797 GMI_reservation_Util.PrintLn(
2798 'in create_rcv_transaction: for inserting in RCV_TRANSACTIONS_INTERFACE, sqlcode is '||SQLCODE||'.');
2799
2800 END create_rcv_transaction;
2801
2802 /* this procedure process_OPM_orders is created for internal orders only *
2803 * Please see bug 1788352 */
2804
2805
2806 PROCEDURE process_OPM_orders(p_stop_id IN NUMBER
2807 ,x_return_status OUT NOCOPY VARCHAR2
2808 ) IS
2809 l_completion_status VARCHAR2(30) := 'NORMAL';
2810
2811 l_delivery_id number;
2812 request_id number;
2813 l_error_code number;
2814 l_msg_count number;
2815 l_msg_data varchar2(3000);
2816 l_error_text varchar2(2000);
2817 l_transaction_header_id number ;
2818 l_return_status varchar2(30);
2819 l_if_internal number ;
2820
2821 CURSOR pickup_deliveries IS
2822 SELECT dg.delivery_id , st.transaction_header_id
2823 FROM wsh_delivery_legs dg,
2824 wsh_new_deliveries dl,
2825 wsh_trip_stops st
2826 WHERE st.stop_id = dg.pick_up_stop_id AND
2827 st.stop_id = p_stop_id AND
2828 st.stop_location_id = dl.initial_pickup_location_id AND
2829 dg.delivery_id = dl.delivery_id ;
2830
2831 -- Bug 3764091 Added NOT EXISTS to the cursor below so that wdd which is already synchronized to PO
2832 -- side is not inserted again in rcv_transactions_interface.
2833 CURSOR c_detail_in_delivery is
2834 SELECT dd.delivery_detail_id, dd.source_line_id
2835 FROM wsh_delivery_details dd, wsh_delivery_assignments da
2836 WHERE dd.delivery_detail_id = da.delivery_detail_id
2837 AND da.delivery_id = l_delivery_id
2838 AND NVL(dd.inv_interfaced_flag , 'N') = 'Y'
2839 AND dd.container_flag = 'N'
2840 AND dd.source_code = 'OE'
2841 AND NOT EXISTS(select 1
2842 from rcv_shipment_lines rsl, oe_order_lines_all oel
2843 where oel.line_id = dd.source_line_id
2844 and rsl.requisition_line_id = oel.source_document_line_id
2845 and rsl.comments = 'OPM WDD:'||to_char(dd.delivery_detail_id));
2846
2847 l_detail_in_delivery c_detail_in_delivery%ROWTYPE;
2848
2849 CURSOR c_order_line_info(c_order_line_id number) is
2850 SELECT source_document_type_id
2851 , source_document_id
2852 , source_document_line_id
2853 , ship_from_org_id
2854 from oe_order_lines_all
2855 where line_id = c_order_line_id;
2856
2857 l_order_line_info c_order_line_info%ROWTYPE;
2858 rc NUMBER := NULL;
2859 l_group_id NUMBER := 0;
2860
2861 CURSOR c_details_for_interface(p_del_detail_id number) is
2862 SELECT * from wsh_delivery_details
2863 where delivery_detail_id = p_del_detail_id
2864 and container_flag = 'N';
2865 l_detail_rec c_details_for_interface%ROWTYPE;
2866
2867 CURSOR c_trip_stop (c_trip_stop_id NUMBER ) IS
2868 SELECT * FROM WSH_TRIP_STOPS
2869 WHERE STOP_ID = c_trip_stop_id;
2870 l_trip_stop_rec c_trip_stop%ROWTYPE;
2871
2872 BEGIN
2873
2874 l_if_internal := 0 ;
2875
2876
2877 GMI_RESERVATION_UTIL.Println ('in GMI process_OPM_orders for stop '||p_stop_id );
2878
2879 FOR del IN pickup_deliveries LOOP
2880 l_delivery_id := del.delivery_id;
2881 GMI_RESERVATION_UTIL.Println ('in GMI found delivery' || l_delivery_id);
2882 OPEN c_detail_in_delivery ;
2883 LOOP
2884 FETCH c_detail_in_delivery into l_detail_in_delivery;
2885 EXIT WHEN c_detail_in_delivery%NOTFOUND;
2886
2887 OPEN c_order_line_info(l_detail_in_delivery.source_line_id);
2888 FETCH c_order_line_info into l_order_line_info;
2889 if (c_order_line_info%NOTFOUND) THEN
2890 CLOSE c_order_line_info;
2891 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2892 GMI_RESERVATION_UTIL.Println('Warning, Sales order not valid');
2893 CLOSE c_detail_in_delivery; /* B2886561 close this cursor before return */
2894 return;
2895 END if;
2896 CLOSE c_order_line_info;
2897
2898 IF (l_order_line_info.source_document_type_id = 10
2899 and INV_GMI_RSV_BRANCH.Process_Branch(l_order_line_info.ship_from_org_id) )
2900 THEN /* internal order */
2901 l_if_internal := 1; -- internal order
2902
2903 IF nvl(l_group_id,0) = 0 THEN
2904 /* only do this once */
2905 select RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_group_id FROM DUAL;
2906 END IF;
2907 OPEN c_details_for_interface(l_detail_in_delivery.delivery_detail_id);
2908 FETCH c_details_for_interface into l_detail_rec;
2909 if c_details_for_interface%NOTFOUND then
2910 CLOSE c_details_for_interface;
2911 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2912 WSH_UTIL_CORE.Println('Warning, Delivery Detail ' ||l_detail_in_delivery.delivery_detail_id ||' not found');
2913 IF c_detail_in_delivery%ISOPEN THEN /* B2886561 close this cursor before return */
2914 CLOSE c_detail_in_delivery;
2915 END IF;
2916 return;
2917 end if;
2918 CLOSE c_details_for_interface;
2919
2920 OPEN c_trip_stop(p_stop_id);
2921 FETCH c_trip_stop into l_trip_stop_rec;
2922 if c_trip_stop%NOTFOUND then
2923 CLOSE c_trip_stop;
2924 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2925 WSH_UTIL_CORE.Println('Warning, Trip Stop '|| p_stop_id ||' not found');
2926 IF c_detail_in_delivery%ISOPEN THEN /* B2886561 close this cursor before return */
2927 CLOSE c_detail_in_delivery;
2928 END IF;
2929 return;
2930 end if;
2931 oe_debug_pub.add('Found Trip Stop '|| p_stop_id);
2932 CLOSE c_trip_stop;
2933
2934 GMI_RESERVATION_UTIL.Println ('found internal order line in this delivery ' || l_delivery_id);
2935 -- internal orders, insert into rcv_transactions_interface
2936 Oe_Debug_Pub.Add('This line is part of an internal order');
2937 GMI_SHIPPING_UTIL.create_rcv_transaction
2938 ( p_shipping_line => l_detail_rec
2939 , p_trip_stop_rec => l_trip_stop_rec
2940 , p_group_id => l_group_id
2941 , x_return_status => l_return_status
2942 , x_msg_count => l_msg_count
2943 , x_msg_data => l_msg_data
2944 );
2945 oe_debug_pub.add('Finished calling GMI_Shipping_Util.create_rcv_transaction');
2946 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2947 WSH_UTIL_CORE.println('Warning ...');
2948 WSH_UTIL_CORE.println('Failed GMI_Shipping_Util.create_rcv_transaction for delivery ID '
2949 || l_detail_rec.DELIVERY_DETAIL_ID );
2950 x_return_status := l_return_status;
2951 END IF;
2952
2953 END IF;
2954 END LOOP;
2955 CLOSE c_detail_in_delivery;
2956 END LOOP;
2957
2958
2959 IF ( l_if_internal = 1 ) THEN
2960 rc := fnd_request.submit_request(
2961 'PO',
2962 'RVCTP',
2963 null,
2964 null,
2965 false,
2966 'IMMEDIATE',
2967 l_group_id,
2968 fnd_global.local_chr(0),
2969 NULL, NULL, NULL, NULL, NULL, NULL,
2970 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2971 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2972 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2973 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2974 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2975 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2976 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2977 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2978 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2979 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2980 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2981 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2982 NULL, NULL, NULL, NULL, NULL, NULL, NULL);
2983 END IF;
2984
2985 END process_OPM_orders;
2986
2987
2988
2989 PROCEDURE MATCH_LINES IS
2990 --BEGIN BUG#2736088 V. Ajay Kumar
2991 --Removed the reference to "apps".
2992 CURSOR c_get_lines
2993 IS
2994 SELECT
2995 mp1.organization_code ORG, mp2.organization_code WHSE
2996 , soh.order_number SO_NUMBER
2997 , sol.line_number LINE_NO
2998 , sol.line_id LINE_ID
2999 , sol.ordered_quantity ORDER_QTY
3000 , wdd.inventory_item_id ITEM_ID
3001 , wdd.organization_id SHIP_ORG
3002 , wdd.delivery_detail_id SHIP_ID
3003 , wdd.SHIPPED_QUANTITY SHIP_QTY
3004 , wdd.locator_id LOCATOR_ID
3005 , wdd.lot_number
3006 , wdd.sublot_number
3007 , wdd.released_status SHIP_STATUS
3008 FROM wsh_delivery_details wdd
3009 , oe_order_headers_all soh
3010 , oe_order_lines_all sol
3011 , mtl_parameters mp1
3012 , mtl_parameters mp2
3013 WHERE wdd.source_line_id = sol.line_id
3014 AND mp1.organization_id = sol.org_id
3015 AND mp2.organization_id = sol.ship_from_org_id
3016 AND sol.flow_status_code = 'AWAITING_SHIPPING'
3017 AND wdd.released_status IN ('C','Y')
3018 AND soh.header_id = sol.header_id
3019 AND NVL(wdd.oe_interfaced_flag,'N') <> 'Y'
3020 -- AND NVL(wdd.inv_interfaced_flag,'N') = 'N'
3021 -- AND NVL(wts.PENDING_INTERFACE_FLAG,'N') <> 'Y'
3022 ORDER BY 1, 2 ,3,4;
3023 --END BUG#2736088
3024
3025
3026 CURSOR get_opm_transaction_c ( p_line in number,
3027 p_item_id in number,
3028 p_lot_id in number,
3029 p_location in varchar
3030 )
3031 IS SELECT trans_id, line_detail_id
3032 FROM ic_tran_pnd
3033 WHERE doc_type='OMSO'
3034 AND line_id = p_line
3035 AND item_id = p_item_id
3036 AND lot_id = p_lot_id
3037 AND location = p_location
3038 AND staged_ind = 1
3039 AND line_detail_id is null
3040 AND delete_mark = 0;
3041
3042 CURSOR c_get_item_info ( pitem_id in number, porg_id in number)
3043 IS
3044 SELECT iim.item_id
3045 FROM ic_item_mst iim,
3046 mtl_system_items msi
3047 WHERE msi.inventory_item_id = pitem_id
3048 AND msi.organization_id = porg_id
3049 AND msi.segment1 = iim.item_no;
3050
3051 l_default_location VARCHAR2(30);
3052 /* these lines are pick released but yet to be staged, therefore all the inv info in wdd
3053 does not exist yet*/
3054
3055 --BEGIN BUG#2736088 V. Ajay Kumar
3056 --Removed the reference to "apps".
3057 Cursor get_unstaged_lines IS
3058 SELECT
3059 mp1.organization_code ORG, mp2.organization_code WHSE
3060 , soh.order_number SO_NUMBER
3061 , sol.line_number LINE_NO
3062 , sol.line_id LINE_ID
3063 , sol.ordered_quantity ORDER_QTY
3064 , wdd.inventory_item_id ITEM_ID
3065 , wdd.organization_id SHIP_ORG
3066 , wdd.delivery_detail_id SHIP_ID
3067 , wdd.SHIPPED_QUANTITY SHIP_QTY
3068 FROM ic_txn_request_lines mo
3069 , wsh_delivery_details wdd
3070 , oe_order_headers_all soh
3071 , oe_order_lines_all sol
3072 , mtl_parameters mp1
3073 , mtl_parameters mp2
3074 Where soh.header_id = sol.header_id
3075 and sol.line_id = mo.txn_source_line_id
3076 AND sol.flow_status_code = 'AWAITING_SHIPPING'
3077 AND mp1.organization_id = sol.org_id
3078 AND mp2.organization_id = sol.ship_from_org_id
3079 and mo.line_status in (3,7)
3080 and mo.line_id = wdd.move_order_line_id
3081 and wdd.released_status = 'S'
3082 ;
3083 --END BUG#2736088
3084 Cursor count_trans_unstaged( p_line_id IN NUMBER
3085 , l_default_location IN VARCHAR2)
3086 IS
3087 SELECT
3088 count(*)
3089 From ic_tran_pnd
3090 Where line_id = p_line_id
3091 And doc_type = 'OMSO'
3092 And line_detail_id is null
3093 And delete_mark = 0
3094 And staged_ind = 0
3095 And completed_ind = 0
3096 And (lot_id <> 0 or location <> l_default_location ) -- NON default
3097 ;
3098
3099 --BEGIN BUG#2736088 V. Ajay Kumar
3100 --Removed the reference to "apps".
3101 Cursor count_unstaged_wdd_for_mo (p_line_id IN NUMBER)
3102 IS
3103 Select count(*)
3104 FROM ic_txn_request_lines mo
3105 , wsh_delivery_details wdd
3106 Where mo.txn_source_line_id = p_line_id
3107 and mo.line_status in (3,7)
3108 and mo.line_id = wdd.move_order_line_id
3109 and mo.txn_source_line_id = wdd.source_line_id
3110 and wdd.released_status = 'S'
3111 ;
3112
3113 l_init_msg_list VARCHAR2(255) := FND_API.G_TRUE;
3114 --END BUG#2736088
3115 l_return_status VARCHAR2(1);
3116 l_msg_count NUMBER;
3117 l_msg_data VARCHAR2(2000);
3118 l_required_lines NUMBER;
3119 l_total_lines NUMBER;
3120 l_qty_found NUMBER;
3121 l_updates NUMBER;
3122 l_old_trans NUMBER;
3123 l_new_trans NUMBER;
3124 l_not_found NUMBER;
3125 l_short_qty NUMBER;
3126 l_xtra_qty NUMBER;
3127 v_outputfile UTL_FILE.FILE_TYPE;
3128 l_db_name VARCHAR2(10);
3129 l_old_response VARCHAR2(10);
3130 l_location VARCHAR2(10);
3131 l_trans_id NUMBER;
3132 l_item_id NUMBER;
3133 l_line_detail_id NUMBER;
3134 l_lot_id NUMBER;
3135 NON_OPM_WHSE EXCEPTION;
3136 l_non_opm_whse NUMBER;
3137 l_trans_updated NUMBER;
3138 l_detail_exists NUMBER;
3139 l_trans_notfound NUMBER;
3140 l_time VARCHAR2(10);
3141 l_trans_count NUMBER;
3142 l_wdd_count NUMBER;
3143 /*
3144 l_DIR Varchar2(255) := nvl(fnd_profile.value
3145 ('OE_DEBUG_LOG_DIRECTORY'), '/tmp');
3146 */
3147
3148 /*
3149 If /tmp OR the dir specified in the profile itself is not one of the values for database parameter 'utl_file_dir' this will fail . Added the following cursor toget the default directory NC - 11/26/01
3150 */
3151
3152 CURSOR get_log_file_location IS
3153 SELECT NVL( SUBSTR( value, 1, instr( value, ',')-1), value)
3154 FROM v$parameter
3155 WHERE name = 'utl_file_dir';
3156
3157 l_FILE Varchar2(255) := NULL;
3158 l_DIR Varchar2(255) := NULL;
3159 CANNOT_OPEN EXCEPTION;
3160
3161
3162 BEGIN
3163
3164 SELECT TO_CHAR(SYSDATE,'SSSSS') INTO l_time
3165 FROM DUAL;
3166 l_FILE := 'MATCHLINES'||l_time;
3167 --DBMS_OUTPut.disable;
3168 -- DBMS_OUTPut.enable(1000000);
3169
3170 /* Getting the log file location from 'utl_file_dir' parameter itself
3171 is not fool proof , for it could have values like '*' . But in this
3172 case, FOPEN would fail and OTHERS exception should catch it.
3173 */
3174
3175 OPEN get_log_file_location;
3176 FETCH get_log_file_location into l_DIR;
3177 CLOSE get_log_file_location;
3178
3179 v_outputfile := UTL_FILE.FOPEN(l_DIR,l_FILE,'W');
3180
3181 /* NC - added 11/26/01 . Say fopen did not fail,there still could be
3182 a problem with the file handler.
3183 */
3184 IF NOT UTL_FILE.IS_OPEN(v_outputfile) THEN
3185 raise CANNOT_OPEN;
3186 END IF;
3187
3188 select name into l_db_name
3189 from v$database;
3190
3191 UTL_FILE.putf(v_outputfile,'\n***************************************\n');
3192 UTL_FILE.putf(v_outputfile,'******* DATABASE - %s \n',l_db_name);
3193 UTL_FILE.putf(v_outputfile,'******* RUN DATE - %s \n',TO_CHAR(SYSDATE,'DD-MM-YY HH24:MI:SS'));
3194 UTL_FILE.putf(v_outputfile,'******************************************\n');
3195
3196 UTL_FILE.putf(v_outputfile,'\n INITIALIZE MSG STACK\n');
3197 --BEGIN BUG#2736088 V. Ajay Kumar
3198 --Removed the reference to "apps"
3199 FND_MSG_PUB.INITIALIZE;
3200 --END BUG#2736088
3201 l_required_lines :=0;
3202 l_non_opm_whse :=0;
3203 l_trans_updated :=0;
3204 l_trans_notfound :=0;
3205 l_detail_exists :=0;
3206
3207 FOR lines IN c_get_lines LOOP
3208 l_required_lines := l_required_lines + 1;
3209 IF l_old_response <> lines.org THEN
3210 UTL_FILE.putf(v_outputfile,'\n ****** START RESPONSE ******\n');
3211 END IF;
3212
3213 UTL_FILE.putf(v_outputfile,'\n RESP => %s',lines.org);
3214 UTL_FILE.putf(v_outputfile,'\n WHSE => %s',lines.WHSE);
3215 UTL_FILE.putf(v_outputfile,'\n ORDER NO => %s',lines.SO_NUMBER);
3216 UTL_FILE.putf(v_outputfile,'\n LINE NO => %s',lines.LINE_NO);
3217 UTL_FILE.putf(v_outputfile,'\n ORDER QTY => %s',lines.ORDER_QTY);
3218 UTL_FILE.putf(v_outputfile,'\n SOURCE LINE ID => %s\n',lines.LINE_ID);
3219
3220 BEGIN
3221 IF ( NOT INV_GMI_RSV_BRANCH.Process_Branch
3222 (p_organization_id => lines.ship_org )
3223 ) THEN
3224 RAISE NON_OPM_WHSE;
3225 ELSE
3226 -- Find Matching Transactions
3227 OPEN c_get_item_info( lines.item_id, lines.ship_org);
3228 FETCH c_get_item_info INTO l_item_id;
3229 IF c_get_item_info%NOTFOUND THEN
3230 UTL_FILE.putf(v_outputfile,'\n ITEM DETAILS NOT FOUND\n');
3231 RAISE NO_DATA_FOUND;
3232 END IF;
3233 CLOSE c_get_item_info;
3234 IF lines.locator_id IS NULL
3235 THEN
3236 l_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
3237 ELSE
3238 SELECT location INTO l_location
3239 FROM ic_loct_mst
3240 WHERE inventory_location_id = lines.locator_id;
3241 END IF;
3242 IF lines.lot_number IS NULL
3243 THEN
3244 l_lot_id := 0;
3245 ELSIF lines.sublot_number IS NULL
3246 THEN
3247 SELECT lot_id INTO l_lot_id
3248 FROM ic_lots_mst
3249 WHERE item_id = l_item_id
3250 AND lot_no = lines.lot_number
3251 AND sublot_no IS NULL ;
3252 ELSE
3253 SELECT lot_id INTO l_lot_id
3254 FROM ic_lots_mst
3255 WHERE item_id = l_item_id
3256 AND lot_no = lines.lot_number
3257 AND sublot_no = lines.sublot_number;
3258 END IF;
3259 OPEN get_opm_transaction_c(lines.line_id,
3260 l_item_id, l_lot_id,l_location);
3261 LOOP
3262 FETCH get_opm_transaction_c INTO l_trans_id, l_line_detail_id;
3263 IF get_opm_transaction_c%NOTFOUND THEN
3264 UTL_FILE.putf(v_outputfile,'\n ***** ERROR ******');
3265 UTL_FILE.putf(v_outputfile,'\n OPM TRANSACTION NOT FOUND ');
3266 UTL_FILE.putf(v_outputfile,'\n LINE_ID %s',lines.line_id);
3267 UTL_FILE.putf(v_outputfile,'\n ITEM_ID %s',l_item_id);
3268 UTL_FILE.putf(v_outputfile,'\n LOT_ID %s',l_lot_id);
3269 UTL_FILE.putf(v_outputfile,'\n LOCAT %s',l_location);
3270 UTL_FILE.putf(v_outputfile,'\n WHSE %s', lines.SHIP_ORG);
3271 UTL_FILE.putf(v_outputfile,'\n ***** ERROR ******\n');
3272 l_trans_notfound := l_trans_notfound +1;
3273 EXIT;
3274 ELSE
3275 UTL_FILE.putf(v_outputfile,' MATCHING TRANS = %s',l_trans_id);
3276 UTL_FILE.putf(v_outputfile,',LINE DETAIL ID = %s\n',l_line_detail_id);
3277 -- Check if Line detail id NOT populated
3278 IF l_line_detail_id is NULL THEN
3279 -- Okay Now Perform Updates
3280 UPDATE IC_TRAN_PND
3281 SET LINE_DETAIL_ID = lines.SHIP_ID
3282 WHERE TRANS_ID = l_trans_id;
3283 l_trans_updated := l_trans_updated +1;
3284 ELSE
3285 -- Line Detail Id Already Populated.
3286 l_detail_exists := l_detail_exists +1;
3287 END IF;
3288 END IF;
3289 EXIT; /* Exit after First Select */
3290 END LOOP;
3291 CLOSE get_opm_transaction_c;
3292 END IF; /* For OPM WHSE CHECK */
3293
3294 EXCEPTION
3295 WHEN NO_DATA_FOUND THEN
3296 UTL_FILE.putf(v_outputfile,'\n NO DATA FOUND\n');
3297 CLOSE c_get_item_info;
3298 WHEN NON_OPM_WHSE THEN
3299 UTL_FILE.putf(v_outputfile,'\n NON OPM SHIPMENT \n');
3300 l_non_opm_whse := l_non_opm_whse + 1;
3301 END; /* inner begin */
3302 l_old_response := lines.org;
3303 END LOOP; /* FOR ALL ORDERS */
3304
3305 /* find out the offset lines for unstaged */
3306 l_default_location := FND_PROFILE.VALUE('IC$DEFAULT_LOCT');
3307 FOR lines IN get_unstaged_lines LOOP
3308 l_required_lines := l_required_lines + 1;
3309 IF l_old_response <> lines.org THEN
3310 UTL_FILE.putf(v_outputfile,'\n ****** unstaged shipping lines ******\n');
3311 END IF;
3312 BEGIN
3313 IF ( NOT INV_GMI_RSV_BRANCH.Process_Branch
3314 (p_organization_id => lines.ship_org )
3315 ) THEN
3316 RAISE NON_OPM_WHSE;
3317 ELSE
3318 Open count_trans_unstaged( lines.line_id, l_default_location);
3319 Fetch count_trans_unstaged Into l_trans_count;
3320 Close count_trans_unstaged;
3321 If l_trans_count <> 0 THEN
3322 UTL_FILE.putf(v_outputfile,'\n RESP => %s',lines.org);
3323 UTL_FILE.putf(v_outputfile,'\n WHSE => %s',lines.WHSE);
3324 UTL_FILE.putf(v_outputfile,'\n ORDER NO => %s',lines.SO_NUMBER);
3325 UTL_FILE.putf(v_outputfile,'\n LINE NO => %s',lines.LINE_NO);
3326 UTL_FILE.putf(v_outputfile,'\n ORDER QTY => %s',lines.ORDER_QTY);
3327 UTL_FILE.putf(v_outputfile,'\n SOURCE LINE ID => %s\n',lines.LINE_ID);
3328 Open count_unstaged_wdd_for_mo(lines.line_id);
3329 Fetch count_unstaged_wdd_for_mo Into l_wdd_count;
3330 Close count_unstaged_wdd_for_mo;
3331 IF l_wdd_count = 1 THEN
3332 /* get the dd_id for this wdd */
3333
3334 --BEGIN BUG#2736088 V. Ajay Kumar
3335 --Removed the reference to "apps".
3336
3337 Select wdd.delivery_detail_id
3338 Into l_line_detail_id
3339 FROM ic_txn_request_lines mo
3340 , wsh_delivery_details wdd
3341 Where mo.txn_source_line_id = lines.line_id
3342 and mo.line_status in (3,7)
3343 and mo.line_id = wdd.move_order_line_id
3344 and mo.txn_source_line_id = wdd.source_line_id
3345 and wdd.released_status = 'S';
3346 --END BUG#2736088
3347
3348 /* find out all the trans and update them to the wdd dd_id */
3349 Update ic_tran_pnd
3350 Set line_detail_id = l_line_detail_id
3351 Where line_id = lines.line_id
3352 And doc_type = 'OMSO'
3353 And line_detail_id is null
3354 And delete_mark = 0
3355 And staged_ind = 0
3356 And completed_ind = 0
3357 And (lot_id <> 0 or location <> l_default_location ) -- NON default
3358 ;
3359 ELSIF l_wdd_count > 1 THEN
3360 UTL_FILE.putf(v_outputfile,'\n ***********Order Number '|| lines.so_number||
3361 'needs manual attention \n');
3362 END IF;
3363 END IF;
3364 END IF;
3365 EXCEPTION
3366 WHEN NO_DATA_FOUND THEN
3367 UTL_FILE.putf(v_outputfile,'\n NO DATA FOUND\n');
3368 CLOSE c_get_item_info;
3369 WHEN NON_OPM_WHSE THEN
3370 UTL_FILE.putf(v_outputfile,'\n NON OPM SHIPMENT \n');
3371 l_non_opm_whse := l_non_opm_whse + 1;
3372 END; /* inner begin */
3373 l_old_response := lines.org;
3374 END LOOP; /* FOR ALL ORDERS */
3375
3376 UTL_FILE.putf(v_outputfile,'\n ************ SUMMARY *********** \n');
3377 UTL_FILE.putf(v_outputfile,'\n TOTAL LINES PROCESSED => %s\n',l_required_lines);
3378 UTL_FILE.putf(v_outputfile,'\n TOTAL OPM PROCESSED => %s\n',l_required_lines - l_non_opm_whse);
3379 UTL_FILE.putf(v_outputfile,'\n TOTAL TRANS NOT FOUND => %s\n',l_trans_notfound);
3380 UTL_FILE.putf(v_outputfile,'\n TOTAL TRANS UPDATED => %s\n',l_trans_updated);
3381 UTL_FILE.putf(v_outputfile,'\n TOTAL LINE DETAIL TRANS => %s\n',l_detail_exists);
3382 UTL_FILE.putf(v_outputfile,'\n ********************************* \n');
3383 UTL_FILE.FCLOSE(v_outputfile);
3384
3385 EXCEPTION
3386
3387 WHEN CANNOT_OPEN THEN
3388 --DBMS_OUTPUT.enable(10000);
3389 --dbms_output.put_line('Can not open utl_file');
3390 null;
3391 WHEN OTHERS THEN
3392 --DBMS_OUTPUT.enable(10000);
3393 --dbms_output.put_line('In others exception : procedure match_lines');
3394 null;
3395 END MATCH_LINES;
3396
3397 /* new procedure split_opm_trans
3398 This is a generic routine called by split_records in shipping then
3399 Split_Detail_INT
3400 if the line is staged, it would call update_opm_transactions
3401 if not it would call split_trans
3402 */
3403 /* NOTE : NC - 11/2/01 commented the first parameter and redeclared this
3404 record type in the spec(GMIUSHPS.pls). This needs to be uncommented
3405 and the record type needs to be deleted from the spec when OM changes
3406 are incorporated */
3407
3408 PROCEDURE split_opm_trans
3409 ( p_old_delivery_detail_id IN NUMBER,
3410 p_released_status IN VARCHAR2,
3411 p_lot_number IN VARCHAR2,
3412 p_sublot_number IN VARCHAR2,
3413 p_organization_id IN NUMBER,
3414 p_inventory_item_id IN NUMBER,
3415 p_old_source_line_id IN NUMBER,
3416 p_locator_id IN NUMBER,
3417 p_old_req_quantity IN NUMBER,
3418 p_old_req_quantity2 IN NUMBER,
3419 p_new_delivery_detail_id IN NUMBER,
3420 p_qty_to_split IN NUMBER,
3421 p_qty2_to_split IN NUMBER,
3422 x_return_status OUT NOCOPY VARCHAR2,
3423 x_msg_count OUT NOCOPY NUMBER,
3424 x_msg_data OUT NOCOPY VARCHAR2)
3425 IS
3426 l_new_delivery_detail_id NUMBER;
3427 BEGIN
3428 SAVEPOINT split_txn;
3429 l_new_delivery_detail_id := p_new_delivery_detail_id;
3430 IF ( p_released_status = 'Y' )
3431 THEN
3432 oe_debug_pub.add('Calling update_opm_transaction.',2);
3433 GMI_RESERVATION_UTIL.println('splitting the OPM inv, update_opm_transaction', 'opm.log');
3434
3435 GMI_SHIPPING_UTIL.update_opm_transaction(
3436 p_old_delivery_detail_id => p_old_delivery_detail_id,
3437 p_lot_number => p_lot_number,
3438 p_sublot_number => p_sublot_number,
3439 p_organization_id => p_organization_id,
3440 p_inventory_item_id => p_inventory_item_id,
3441 p_old_source_line_id => p_old_source_line_id,
3442 p_locator_id => p_locator_id,
3443 p_new_delivery_detail_id => l_new_delivery_detail_id,
3444 p_old_req_quantity => p_old_req_quantity,
3445 p_old_req_quantity2 => p_old_req_quantity2,
3446 p_req_quantity => p_qty_to_split,
3447 p_req_quantity2 => NVL(p_qty2_to_split,0),
3448 x_return_status => x_return_status,
3449 x_msg_count => x_msg_count,
3450 x_msg_data => x_msg_data );
3451 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3453 END IF;
3454 oe_debug_pub.add('Done calling update_opm_transaction',2);
3455 ELSE /* not staged */
3456 GMI_SHIPPING_UTIL.split_trans
3457 ( p_old_delivery_detail_id => p_old_delivery_detail_id,
3458 p_new_delivery_detail_id => l_new_delivery_detail_id,
3459 p_old_source_line_id => p_old_source_line_id,
3460 p_new_source_line_id => p_old_source_line_id,
3461 p_qty_to_split => p_qty_to_split,
3462 p_qty2_to_split => NVL(p_qty2_to_split,0),
3463 x_return_status => x_return_status,
3464 x_msg_count => x_msg_count,
3465 x_msg_data => x_msg_data
3466 );
3467 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3469 END IF;
3470 oe_debug_pub.add('Done calling split_trans',2);
3471
3472 END IF;
3473
3474 EXCEPTION
3475 WHEN NO_DATA_FOUND THEN
3476 rollback to update_txn;
3477 x_return_status := FND_API.G_RET_STS_ERROR;
3478 RAISE NO_DATA_FOUND;
3479 GMI_RESERVATION_UTIL.Println('Raised When No Data Found in split_opm_trans');
3480
3481 WHEN OTHERS THEN
3482 rollback to split_txn;
3483 x_return_status := FND_API.G_RET_STS_ERROR;
3484 GMI_RESERVATION_UTIL.Println('Raised When Others in split_opm_trans');
3485 END split_opm_trans;
3486
3487 /* this procedure fulfills the trans for the old dd and updates the rest of
3488 trans for the new dd
3489 in the process, split the trans if neccessary
3490 */
3491 PROCEDURE split_trans
3492 ( p_old_delivery_detail_id IN NUMBER,
3493 p_new_delivery_detail_id IN NUMBER,
3494 p_old_source_line_id IN NUMBER,
3495 p_new_source_line_id IN NUMBER,
3496 p_qty_to_split IN NUMBER,
3497 p_qty2_to_split IN NUMBER,
3498 x_return_status OUT NOCOPY VARCHAR2,
3499 x_msg_count OUT NOCOPY NUMBER,
3500 x_msg_data OUT NOCOPY VARCHAR2)
3501 IS
3502 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3503 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3504 l_old_transaction_row ic_tran_pnd%ROWTYPE ;
3505 l_new_transaction_row ic_tran_pnd%ROWTYPE;
3506 l_trans_id ic_tran_pnd.trans_id%TYPE;
3507 l_item_id ic_tran_pnd.item_id%TYPE;
3508 l_location ic_tran_pnd.location%TYPE;
3509 l_lot_id ic_tran_pnd.lot_id%TYPE;
3510 l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
3511 l_new_delivery_detail_id NUMBER;
3512 l_source_line_id NUMBER;
3513 l_fulfilled_qty NUMBER;
3514 l_qty_to_fulfil NUMBER;
3515 l_qty2_to_fulfil NUMBER;
3516 l_orig_qty NUMBER;
3517 l_orig_qty2 NUMBER;
3518 l_lot_ctl NUMBER;
3519 l_loct_ctl NUMBER;
3520 l_whse_ctl NUMBER;
3521 l_doc_id NUMBER;
3522 l_inventory_item_id NUMBER;
3523 l_organization_id NUMBER;
3524 l_whse_code VARCHAR2(5);
3525 l_released_status VARCHAR2(5);
3526 l_noninv_ind NUMBER; /* Bug 2901317 */
3527 l_orig_txn_exists NUMBER; /* Bug 2985470 */
3528
3529
3530 cursor c_reservations IS
3531 SELECT trans_id, doc_id
3532 FROM ic_tran_pnd
3533 WHERE line_id = p_old_source_line_id
3534 AND line_detail_id = p_old_delivery_detail_id
3535 AND delete_mark = 0
3536 AND doc_type = 'OMSO'
3537 AND trans_qty <> 0
3538 ORDER BY staged_ind desc
3539 ,trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
3540 /* or should consider the alloc rules */
3541 CURSOR get_original_qty IS
3542 Select requested_quantity
3543 , requested_quantity2
3544 , released_status
3545 , source_line_id
3546 , inventory_item_id
3547 , organization_id
3548 From wsh_delivery_details
3549 Where delivery_detail_id = p_old_delivery_detail_id;
3550
3551 Cursor Get_item_info IS
3552 Select ic.item_id
3553 , ic.lot_ctl
3554 , ic.loct_ctl
3555 , ic.noninv_ind /* Bug 2901317 */
3556 From ic_item_mst ic
3557 , mtl_system_items mtl
3558 Where ic.item_no = mtl.segment1
3559 and mtl.inventory_item_id = l_inventory_item_id
3560 and mtl.organization_id = l_organization_id;
3561
3562 Cursor nonctl_reservation IS
3563 SELECT trans_id
3564 FROM ic_tran_pnd
3565 WHERE line_id = p_old_source_line_id
3566 AND line_detail_id = p_old_delivery_detail_id
3567 AND delete_mark = 0
3568 AND doc_type = 'OMSO'
3569 AND staged_ind = 1
3570 AND trans_qty <> 0;
3571
3572 BEGIN
3573 GMI_RESERVATION_UTIL.Println(' in split_trans');
3574 GMI_RESERVATION_UTIL.Println(' p_old_delivery_detail_id '||p_old_delivery_detail_id);
3575 GMI_RESERVATION_UTIL.Println(' p_new_delivery_detail_id '||p_new_delivery_detail_id);
3576 GMI_RESERVATION_UTIL.Println(' p_old_source_line_id '||p_old_source_line_id);
3577 GMI_RESERVATION_UTIL.Println(' p_new_source_line_id '||p_new_source_line_id);
3578 l_fulfilled_qty := 0;
3579
3580 Open get_original_qty;
3581 Fetch get_original_qty
3582 Into l_orig_qty
3583 , l_orig_qty2
3584 , l_released_status
3585 , l_source_line_id
3586 , l_inventory_item_id
3587 , l_organization_id
3588 ;
3589 Close get_original_qty;
3590
3591 /* get lot_ctl and loct_ctl */
3592 Open get_item_info;
3593 Fetch get_item_info
3594 Into l_item_id
3595 , l_lot_ctl
3596 , l_loct_ctl
3597 , l_noninv_ind /* Bug 2901317 */
3598 ;
3599 Close get_item_info;
3600
3601 /* get whse loct_ctl */
3602 Select loct_ctl
3603 Into l_whse_ctl
3604 From ic_whse_mst
3605 Where mtl_organization_id = l_organization_id;
3606
3607 l_qty_to_fulfil := l_orig_qty - p_qty_to_split;
3608 l_qty2_to_fulfil := l_orig_qty2 - p_qty2_to_split;
3609
3610 GMI_RESERVATION_UTIL.Println('in split_trans');
3611 GMI_RESERVATION_UTIL.Println('in split_trans, relased_status '||l_released_status);
3612 GMI_RESERVATION_UTIL.Println('in split_trans, qty to split'||p_qty_to_split);
3613 GMI_RESERVATION_UTIL.Println('in split_trans, qty2 to split'||p_qty2_to_split);
3614 GMI_RESERVATION_UTIL.Println('in split_trans, qty to fulfil'||l_qty_to_fulfil);
3615 IF l_released_status = 'B' THEN /* back orders*/
3616 IF (l_noninv_ind = 1 OR (l_lot_ctl = 0 AND l_loct_ctl * l_whse_ctl = 0)) THEN
3617 -- null;
3618 /* Bug2901317 */
3619 OPEN nonctl_reservation;
3620 FETCH nonctl_reservation INTO l_trans_id;
3621 CLOSE nonctl_reservation;
3622 /* End Bug2901317 */
3623 ELSE
3624 GMI_RESERVATION_UTIL.Println('in split_trans, updating the trans for new line_id');
3625 OPEN c_reservations;
3626 LOOP
3627 FETCH c_reservations INTO l_trans_id, l_doc_id;
3628 EXIT WHEN c_reservations%NOTFOUND;
3629 Update ic_tran_pnd
3630 Set line_id = p_new_source_line_id
3631 Where trans_id = l_trans_id;
3632 GMI_RESERVATION_UTIL.Println('l_trans_id is '||l_trans_id);
3633 END LOOP;
3634 END IF;
3635
3636 /* update the default lot to the new line_id */
3637 oe_debug_pub.add('Going to update ic_tranPnd with new line_id :'||p_new_source_line_id
3638 ||' for trans_id '||l_trans_id,2);
3639 IF l_trans_id > 0 THEN
3640 Update ic_tran_pnd
3641 Set line_id = p_new_source_line_id
3642 Where trans_id = l_trans_id;
3643 END IF;
3644
3645 oe_debug_pub.add('Going to find old default lot in split_reservation',2);
3646 GMI_RESERVATION_UTIL.find_default_lot
3647 ( x_return_status => x_return_status,
3648 x_msg_count => x_msg_count,
3649 x_msg_data => x_msg_data,
3650 x_reservation_id => l_trans_id,
3651 p_line_id => p_old_source_line_id
3652 );
3653 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3654 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
3655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3656 END IF;
3657
3658 l_old_transaction_rec.trans_id := l_trans_id;
3659 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3660 (l_old_transaction_rec, l_old_transaction_rec )
3661 THEN
3662 l_orig_txn_exists := 1; --Bug3149635
3663 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_old_source_line_id);
3664 GMI_RESERVATION_UTIL.balance_default_lot
3665 ( p_ic_default_rec => l_old_transaction_rec
3666 , p_opm_item_id => l_old_transaction_rec.item_id
3667 , x_return_status => x_return_status
3668 , x_msg_count => x_msg_count
3669 , x_msg_data => x_msg_data
3670 );
3671 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3672 THEN
3673 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
3674 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675 END IF;
3676 ELSE
3677 l_orig_txn_exists := 0; --Bug3149635
3678 END IF;
3679
3680 oe_debug_pub.add('Going to find new default lot in split_reservation',2);
3681
3682 GMI_RESERVATION_UTIL.find_default_lot
3683 ( x_return_status => x_return_status,
3684 x_msg_count => x_msg_count,
3685 x_msg_data => x_msg_data,
3686 x_reservation_id => l_trans_id,
3687 p_line_id => p_new_source_line_id
3688 );
3689
3690 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
3691 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
3692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3693 END IF;
3694
3695 --Bug3149635 (added AND condition below)
3696 IF (nvl(l_trans_id, 0) = 0 AND l_orig_txn_exists = 1 ) THEN
3697 l_old_transaction_rec.trans_id := null;
3698 l_old_transaction_rec.line_id := p_new_source_line_id;
3699 l_old_transaction_rec.line_detail_id := null;
3700 -- create a new default
3701 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION(
3702 p_api_version => 1.0
3703 ,p_init_msg_list => FND_API.G_FALSE
3704 ,p_commit => FND_API.G_FALSE
3705 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
3706 ,p_tran_rec => l_old_transaction_rec
3707 ,x_tran_row => l_new_transaction_row
3708 ,x_return_status => x_return_status
3709 ,x_msg_count => x_msg_count
3710 ,x_msg_data => x_msg_data);
3711
3712 GMI_reservation_Util.PrintLn('created new default lot with trans_id '||l_new_transaction_row.trans_id);
3713 GMI_reservation_Util.PrintLn('created new default lot with line_detail_id '||l_new_transaction_row.line_detail_id);
3714 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
3715 THEN
3716 GMI_reservation_Util.PrintLn('(opm_dbg) in Create_Empty_Default_Lot (Create DefaultLot): Error returned by GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION.');
3717 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
3718 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
3719 FND_MESSAGE.Set_Token('WHERE', 'Create_Empty_Default_Lot');
3720 FND_MSG_PUB.Add;
3721 RAISE FND_API.G_EXC_ERROR;
3722 END IF;
3723 l_old_transaction_rec.trans_id := l_new_transaction_row.trans_id;
3724 ELSE
3725 l_old_transaction_rec.trans_id := l_trans_id;
3726 END IF;
3727 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3728 (l_old_transaction_rec, l_old_transaction_rec )
3729 THEN
3730 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_new_source_line_id);
3731 GMI_RESERVATION_UTIL.balance_default_lot
3732 ( p_ic_default_rec => l_old_transaction_rec
3733 , p_opm_item_id => l_old_transaction_rec.item_id
3734 , x_return_status => x_return_status
3735 , x_msg_count => x_msg_count
3736 , x_msg_data => x_msg_data
3737 );
3738 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3739 THEN
3740 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
3741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3742 END IF;
3743 END IF;
3744 /* split reservations if neccessary */
3745 GML_BATCH_OM_RES_PVT.split_reservations
3746 ( p_old_delivery_detail_id => p_old_delivery_detail_id
3747 , p_new_delivery_detail_id => p_new_delivery_detail_id
3748 , p_old_source_line_id => p_old_source_line_id
3749 , p_new_source_line_id => p_new_source_line_id
3750 , p_qty_to_split => p_qty_to_split
3751 , p_qty2_to_split => p_qty2_to_split
3752 , p_orig_qty => l_orig_qty
3753 , p_orig_qty2 => l_orig_qty2
3754 , p_action => 'B'
3755 , x_return_status => x_return_status
3756 , x_msg_count => x_msg_count
3757 , x_msg_data => x_msg_data
3758 ) ;
3759 ELSE
3760 IF (l_noninv_ind = 1 OR (l_lot_ctl = 0 AND l_loct_ctl * l_whse_ctl = 0)) THEN
3761 -- null;
3762 /* Bug2901317 */
3763 IF (p_new_delivery_detail_id is NOT NULL) AND (p_old_source_line_id is NOT NULL) AND
3764 (p_new_source_line_id is NOT NULL) AND (p_old_source_line_id <> p_new_source_line_id) THEN
3765 Update ic_tran_pnd
3766 Set line_id = p_new_source_line_id
3767 Where line_id = p_old_source_line_id
3768 and line_detail_id = p_new_delivery_detail_id;
3769 GMI_RESERVATION_UTIL.PrintLn('Updated Here');
3770 END IF;
3771 /* End Bug2901317 */
3772 ELSE
3773 OPEN c_reservations;
3774 LOOP
3775 FETCH c_reservations INTO l_trans_id, l_doc_id;
3776 EXIT WHEN c_reservations%NOTFOUND;
3777
3778 l_old_transaction_rec.trans_id := l_trans_id;
3779
3780 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3781 (l_old_transaction_rec, l_old_transaction_rec )
3782 THEN
3783 GMI_RESERVATION_UTIL.Println('got trans for trans_id '||l_trans_id);
3784 GMI_RESERVATION_UTIL.Println('l_qty_to_fulfil '||l_qty_to_fulfil);
3785 GMI_RESERVATION_UTIL.Println('l_qty2_to_fulfil '||l_qty2_to_fulfil);
3786 IF abs(l_old_transaction_rec.trans_qty) < l_qty_to_fulfil THEN
3787 /* do nothing for the tran */
3788 GMI_RESERVATION_UTIL.Println('in split_trans, keep trans the same for trans_id '||l_trans_id);
3789 GMI_RESERVATION_UTIL.Println('in split_trans, trans_qty '||l_old_transaction_rec.trans_qty);
3790 l_qty_to_fulfil := l_qty_to_fulfil - abs(l_old_transaction_rec.trans_qty);
3791 l_qty2_to_fulfil := l_qty2_to_fulfil - abs(l_old_transaction_rec.trans_qty2);
3792 ELSIF abs(l_old_transaction_rec.trans_qty) > l_qty_to_fulfil
3793 AND l_qty_to_fulfil > 0 THEN
3794 /* not sure why this */
3795 --l_old_transaction_rec.trans_qty := -1 * (l_qty_to_fulfil);
3796 --l_old_transaction_rec.trans_qty2 := -1 * (l_qty2_to_fulfil);
3797
3798 update ic_tran_pnd
3799 set trans_qty = -1 * l_qty_to_fulfil
3800 , trans_qty2 = -1 * l_qty2_to_fulfil
3801 Where trans_id = l_trans_id;
3802
3803 /* create a new trans for the new wdd, and new line_id if applicable */
3804 l_new_transaction_rec := l_old_transaction_rec;
3805 l_new_transaction_rec.line_detail_id := p_new_delivery_detail_id;
3806 l_new_transaction_rec.trans_id := NULL;
3807 l_new_transaction_rec.trans_qty := -1 * (abs(l_new_transaction_rec.trans_qty)
3808 - l_qty_to_fulfil);
3809 l_new_transaction_rec.trans_qty2 := -1 * (abs(l_new_transaction_rec.trans_qty2)
3810 - l_qty2_to_fulfil);
3811 l_new_transaction_rec.line_id := p_new_source_line_id;
3812
3813 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
3814 ( 1
3815 , FND_API.G_FALSE
3816 , FND_API.G_FALSE
3817 , FND_API.G_VALID_LEVEL_FULL
3818 , l_new_transaction_rec
3819 , l_new_transaction_row
3820 , x_return_status
3821 , x_msg_count
3822 , x_msg_data
3823 );
3824
3825 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3826 THEN
3827 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
3828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3829 END IF;
3830 /* qty filfilled*/
3831 l_qty_to_fulfil := 0;
3832 l_qty2_to_fulfil := 0;
3833 ELSIF l_qty_to_fulfil <= 0 THEN
3834 /* simply update the rest with the new wdd id and new line_id */
3835 update ic_tran_pnd
3836 set line_detail_id = p_new_delivery_detail_id
3837 , line_id = p_new_source_line_id
3838 Where trans_id = l_trans_id;
3839 END IF;
3840 END IF;
3841 END LOOP;
3842 CLOSE c_reservations;
3843 /* need to balance default lot for both new sol and old sol */
3844 GMI_RESERVATION_UTIL.find_default_lot
3845 ( x_return_status => x_return_status,
3846 x_msg_count => x_msg_count,
3847 x_msg_data => x_msg_data,
3848 x_reservation_id => l_trans_id,
3849 p_line_id => p_old_source_line_id
3850 );
3851 l_orig_txn_exists := 0; -- B2985470
3852 IF l_trans_id > 0 THEN -- if it does not exist, don't bother
3853 l_old_transaction_rec.trans_id := l_trans_id;
3854 -- B2985470 old line has txn new should also have one.
3855 l_orig_txn_exists := 1; -- B2985470
3856
3857 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3858 (l_old_transaction_rec, l_old_transaction_rec )
3859 THEN
3860 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_old_source_line_id);
3861 GMI_RESERVATION_UTIL.balance_default_lot
3862 ( p_ic_default_rec => l_old_transaction_rec
3863 , p_opm_item_id => l_old_transaction_rec.item_id
3864 , x_return_status => x_return_status
3865 , x_msg_count => x_msg_count
3866 , x_msg_data => x_msg_data
3867 );
3868 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3869 THEN
3870 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
3871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3872 END IF;
3873 END IF;
3874 END IF;
3875 GMI_RESERVATION_UTIL.find_default_lot
3876 ( x_return_status => x_return_status,
3877 x_msg_count => x_msg_count,
3878 x_msg_data => x_msg_data,
3879 x_reservation_id => l_trans_id,
3880 p_line_id => p_new_source_line_id
3881 );
3882 IF l_trans_id > 0 AND p_new_source_line_id <> p_old_source_line_id
3883 THEN -- if it does not exist, don't bother
3884 l_old_transaction_rec.trans_id := l_trans_id;
3885
3886 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3887 (l_old_transaction_rec, l_old_transaction_rec )
3888 THEN
3889 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_new_source_line_id);
3890 GMI_RESERVATION_UTIL.balance_default_lot
3891 ( p_ic_default_rec => l_old_transaction_rec
3892 , p_opm_item_id => l_old_transaction_rec.item_id
3893 , x_return_status => x_return_status
3894 , x_msg_count => x_msg_count
3895 , x_msg_data => x_msg_data
3896 );
3897 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3898 THEN
3899 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
3900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3901 END IF;
3902 END IF;
3903 -- Begin Bug 2985470
3904 ELSIF (l_orig_txn_exists = 1 AND p_new_source_line_id <> p_old_source_line_id) THEN -- need to create txn for split line.
3905 /* create a new trans for the new wdd, and new line_id if applicable */
3906 l_new_transaction_rec := l_old_transaction_rec;
3907 l_new_transaction_rec.trans_id := NULL;
3908 -- p_qty_to_split are in primary UOM
3909 l_new_transaction_rec.trans_qty := -1 * (abs(p_qty_to_split));
3910 l_new_transaction_rec.trans_qty2 := -1 * (abs(p_qty2_to_split));
3911 l_new_transaction_rec.line_id := p_new_source_line_id;
3912
3913 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
3914 ( 1
3915 , FND_API.G_FALSE
3916 , FND_API.G_FALSE
3917 , FND_API.G_VALID_LEVEL_FULL
3918 , l_new_transaction_rec
3919 , l_new_transaction_row
3920 , x_return_status
3921 , x_msg_count
3922 , x_msg_data
3923 );
3924
3925 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3926 THEN
3927 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
3928 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3929 END IF;
3930 /* qty filfilled*/
3931 l_qty_to_fulfil := 0;
3932 l_qty2_to_fulfil := 0;
3933
3934 -- PK added
3935 GMI_RESERVATION_UTIL.find_default_lot
3936 ( x_return_status => x_return_status,
3937 x_msg_count => x_msg_count,
3938 x_msg_data => x_msg_data,
3939 x_reservation_id => l_trans_id,
3940 p_line_id => p_new_source_line_id
3941 );
3942 IF l_trans_id > 0 AND p_new_source_line_id <> p_old_source_line_id
3943 THEN -- if it does not exist, don't bother
3944 l_old_transaction_rec.trans_id := l_trans_id;
3945
3946 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3947 (l_old_transaction_rec, l_old_transaction_rec )
3948 THEN
3949 GMI_RESERVATION_UTIL.PrintLn('balancing default lot for line_id '|| p_new_source_line_id);
3950 GMI_RESERVATION_UTIL.balance_default_lot
3951 ( p_ic_default_rec => l_old_transaction_rec
3952 , p_opm_item_id => l_old_transaction_rec.item_id
3953 , x_return_status => x_return_status
3954 , x_msg_count => x_msg_count
3955 , x_msg_data => x_msg_data
3956 );
3957 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3958 THEN
3959 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
3960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3961 END IF;
3962 END IF;
3963 END IF;
3964
3965 -- End Bug 2985470
3966 END IF;
3967 END IF;
3968 /* split reservations if neccessary */
3969 GML_BATCH_OM_RES_PVT.split_reservations
3970 ( p_old_delivery_detail_id => p_old_delivery_detail_id
3971 , p_new_delivery_detail_id => p_new_delivery_detail_id
3972 , p_old_source_line_id => p_old_source_line_id
3973 , p_new_source_line_id => p_new_source_line_id
3974 , p_qty_to_split => p_qty_to_split
3975 , p_qty2_to_split => p_qty2_to_split
3976 , p_orig_qty => l_orig_qty
3977 , p_orig_qty2 => l_orig_qty2
3978 , p_action => 'O'
3979 , x_return_status => x_return_status
3980 , x_msg_count => x_msg_count
3981 , x_msg_data => x_msg_data
3982 ) ;
3983 END IF;
3984
3985 END split_trans;
3986
3987 /* this procedure is called in confirm_deliveries for non ctl items*/
3988 /* For Enhancement 2320442, code in following procedure commented out
3989 for non-controlled inventory items - Lakshmi Swamy */
3990 /* Bug 2901317 - Following procedure commented out for non-inv items */
3991
3992 procedure check_non_ctl (
3993 p_delivery_detail_id IN NUMBER
3994 ,p_shipped_quantity IN NUMBER
3995 ,p_shipped_quantity2 IN NUMBER
3996 ,x_return_status OUT NOCOPY VARCHAR2
3997 )
3998 IS
3999 l_old_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4000 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4001 l_old_transaction_row ic_tran_pnd%ROWTYPE ;
4002 l_new_transaction_row ic_tran_pnd%ROWTYPE;
4003 l_trans_id ic_tran_pnd.trans_id%TYPE;
4004 l_item_id ic_tran_pnd.item_id%TYPE;
4005 l_location ic_tran_pnd.location%TYPE;
4006 l_lot_id ic_tran_pnd.lot_id%TYPE;
4007 l_line_detail_id wsh_delivery_details.delivery_detail_id%TYPE;
4008 l_source_line_id NUMBER;
4009 l_inventory_item_id NUMBER;
4010 l_organization_id NUMBER;
4011 l_whse_code VARCHAR2(5);
4012 l_released_status VARCHAR2(5);
4013 l_msg_count NUMBER;
4014 l_msg_data VARCHAR2(255);
4015 l_ctl_ind VARCHAR2(1);
4016 l_been_there NUMBER;
4017 l_get_trans NUMBER;
4018 l_noninv_ind NUMBER;
4019
4020 Cursor get_wdd_info IS
4021 Select source_line_id
4022 , organization_id
4023 , inventory_item_id
4024 From wsh_delivery_details
4025 Where delivery_detail_id = p_delivery_detail_id;
4026
4027 cursor c_reservations IS
4028 SELECT trans_id
4029 FROM ic_tran_pnd
4030 WHERE line_id = l_source_line_id
4031 AND line_detail_id = p_delivery_detail_id
4032 AND delete_mark = 0
4033 AND doc_type = 'OMSO'
4034 AND trans_qty <> 0;
4035
4036 Cursor Get_item_info IS
4037 Select ic.noninv_ind
4038 From ic_item_mst ic
4039 , mtl_system_items mtl
4040 Where ic.item_no = mtl.segment1
4041 and mtl.inventory_item_id = l_inventory_item_id
4042 and mtl.organization_id = l_organization_id;
4043
4044 BEGIN
4045
4046 RETURN; /* Bug 2901317 and Enhancement 2320442 */
4047 GMI_RESERVATION_UTIL.println('in check_non_ctl NON ctl item');
4048 Open get_wdd_info;
4049 Fetch get_wdd_info
4050 Into l_source_line_id
4051 , l_organization_id
4052 , l_inventory_item_id
4053 ;
4054 Close get_wdd_info;
4055
4056 Open Get_item_info;
4057 Fetch Get_item_info INTO l_noninv_ind;
4058 Close Get_item_info;
4059
4060 IF (l_noninv_ind = 1) THEN /* Following code only for non-inventory items - Bug2462993 */
4061 l_been_there := 0;
4062 Open c_reservations;
4063 Fetch c_reservations Into l_trans_id;
4064 IF c_reservations%NOTFOUND THEN
4065 l_trans_id := 0;
4066 END IF;
4067 Close c_reservations;
4068 IF l_trans_id <> 0 THEN -- already ship confirmed, then reopened
4069 l_been_there := 1;
4070 update ic_tran_pnd
4071 set delete_mark=1
4072 Where trans_id=l_trans_id;
4073 END IF;
4074
4075 GMI_RESERVATION_UTIL.find_default_lot
4076 ( x_return_status => x_return_status,
4077 x_msg_count => l_msg_count,
4078 x_msg_data => l_msg_data,
4079 x_reservation_id => l_trans_id,
4080 p_line_id => l_source_line_id
4081 );
4082 l_get_trans := 0;
4083 l_old_transaction_rec.trans_id := l_trans_id;
4084 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4085 (l_old_transaction_rec, l_old_transaction_rec )
4086 THEN
4087 l_get_trans := 1;
4088 END IF;
4089 IF l_been_there = 1 AND l_get_trans = 1 THEN
4090 /* need to balance defualt lot */
4091 GMI_RESERVATION_UTIL.balance_default_lot
4092 ( p_ic_default_rec => l_old_transaction_rec
4093 , p_opm_item_id => l_old_transaction_rec.item_id
4094 , x_return_status => x_return_status
4095 , x_msg_count => l_msg_count
4096 , x_msg_data => l_msg_data
4097 );
4098 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4099 THEN
4100 GMI_RESERVATION_UTIL.PrintLn('Error returned by balancing default lot');
4101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4102 END IF;
4103 l_get_trans := 0;
4104 /* need to fetch it again */
4105 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
4106 (l_old_transaction_rec, l_old_transaction_rec )
4107 THEN
4108 l_get_trans := 1;
4109 END IF;
4110 END IF;
4111
4112 IF l_get_trans = 1 THEN
4113 GMI_RESERVATION_UTIL.println('will update ictranpndNON ctl item','opm.log');
4114 IF p_shipped_quantity >= abs(l_old_transaction_rec.trans_qty) THEN -- ship the whole thing and more
4115 /* this line is no longer default trans */
4116 l_old_transaction_rec.trans_qty := -1 * (p_shipped_quantity);
4117 l_old_transaction_rec.trans_qty2 := -1 * (p_shipped_quantity2);
4118 update ic_tran_pnd
4119 set trans_qty = l_old_transaction_rec.trans_qty
4120 , trans_qty2 = l_old_transaction_rec.trans_qty2
4121 , line_detail_id = p_delivery_detail_id
4122 , staged_ind = 1
4123 Where trans_id = l_trans_id;
4124
4125 ELSE -- partial ship
4126 GMI_RESERVATION_UTIL.println('split the default lot ','opm.log');
4127 update ic_tran_pnd
4128 set trans_qty = -1 * p_shipped_quantity
4129 , trans_qty2 = -1 * p_shipped_quantity2
4130 , line_detail_id = p_delivery_detail_id
4131 , staged_ind = 1
4132 Where trans_id = l_trans_id;
4133 /* create a new trans with the un shipped qtys for the default trans*/
4134 l_new_transaction_rec := l_old_transaction_rec;
4135 l_new_transaction_rec.trans_id := NULL;
4136 l_new_transaction_rec.trans_qty := -1 * (abs(l_new_transaction_rec.trans_qty)
4137 - p_shipped_quantity);
4138 l_new_transaction_rec.trans_qty2 := -1 * (abs(l_new_transaction_rec.trans_qty2)
4139 - p_shipped_quantity2);
4140 l_new_transaction_rec.line_detail_id := null;
4141
4142 GMI_RESERVATION_UTIL.println('create trans '||l_trans_id );
4143 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
4144 ( 1
4145 , FND_API.G_FALSE
4146 , FND_API.G_FALSE
4147 , FND_API.G_VALID_LEVEL_FULL
4148 , l_new_transaction_rec
4149 , l_new_transaction_row
4150 , x_return_status
4151 , l_msg_count
4152 , l_msg_data
4153 );
4154
4155 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4156 THEN
4157 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
4158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4159 END IF;
4160 GMI_RESERVATION_UTIL.println('create trans '||l_new_transaction_row.trans_id );
4161 END IF;
4162 END IF;
4163 END IF;
4164 END check_non_ctl;
4165
4166 Procedure unreserve_delivery_detail
4167 ( p_delivery_detail_id IN NUMBER
4168 , p_quantity_to_unreserve IN NUMBER
4169 , p_quantity_to_unreserve2 IN NUMBER default NULL
4170 , p_unreserve_mode IN VARCHAR2
4171 , x_return_status OUT NOCOPY VARCHAR2
4172 ) IS
4173 l_trans_id NUMBER;
4174 l_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4175 l_transaction_row ic_tran_pnd%ROWTYPE;
4176 l_new_transaction_row ic_tran_pnd%ROWTYPE;
4177 l_new_transaction_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
4178 x_msg_count NUMBER;
4179 x_msg_data VARCHAR2(3000);
4180 l_trans_qty NUMBER;
4181 l_trans_qty2 NUMBER;
4182 l_quantity_to_unreserve NUMBER;
4183 l_quantity_to_unreserve2 NUMBER;
4184 l_lock_status BOOLEAN;
4185
4186 cursor c_get_opm_txn is
4187 Select trans_id
4188 , trans_qty
4189 , trans_qty2
4190 From ic_tran_pnd
4191 Where line_detail_id = p_delivery_detail_id
4192 --and line_id = p_source_line_id
4193 and doc_type='OMSO'
4194 and delete_mark = 0;
4195
4196 BEGIN
4197
4198 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4199
4200 OPEN c_get_opm_txn;
4201 GMI_RESERVATION_UTIL.println('unreserve wdd ' || p_delivery_detail_id);
4202 --GMI_RESERVATION_UTIL.println('unreserve line_id ' || p_source_line_id);
4203 l_quantity_to_unreserve := p_quantity_to_unreserve;
4204 l_quantity_to_unreserve2 := p_quantity_to_unreserve2;
4205 --LOOP
4206 FETCH c_get_opm_txn
4207 into l_trans_id
4208 , l_trans_qty
4209 , l_trans_qty2
4210 ;
4211 CLOSE c_get_opm_txn;
4212 --EXIT WHEN c_get_opm_txn%NOTFOUND;
4213 l_transaction_rec.trans_id := l_trans_id;
4214 IF nvl(l_trans_id, 0) <> 0 THEN
4215 IF ( p_unreserve_mode = 'UNRESERVE') then
4216 IF abs(l_trans_qty) <= l_quantity_to_unreserve THEN
4217 GMI_SHIPPING_UTIL.unreserve_inv
4218 ( p_trans_id => l_trans_id
4219 , x_return_status => x_return_status
4220 );
4221 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
4222 THEN
4223 GMI_RESERVATION_UTIL.println('Error returned by Delete_Pending_Transaction');
4224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4225 END IF;
4226 ELSE
4227 GMI_RESERVATION_UTIL.println('update the ic_tran_pnd');
4228 Update ic_tran_pnd
4229 Set trans_qty = -1 * (abs(l_trans_qty) - l_quantity_to_unreserve)
4230 , trans_qty2 = -1 * (abs(l_trans_qty2) - l_quantity_to_unreserve2)
4231 Where trans_id = l_trans_id;
4232 END IF;
4233 ELSE -- p_unreserve_mode = 'CYCLE_COUNT'
4234 update ic_tran_pnd
4235 set staged_ind = 0
4236 Where trans_id = l_trans_id;
4237 END IF; -- of mode
4238 END IF; -- l_trans_id
4239 --END LOOP;
4240
4241 EXCEPTION
4242 WHEN OTHERS THEN
4243 x_return_status := FND_API.G_RET_STS_ERROR;
4244
4245 END unreserve_delivery_detail;
4246
4247 /* Bug 2547509. Function to validate the trans_date. Returns sysdate if date is in closed period */
4248
4249 FUNCTION GMI_TRANS_DATE (p_trans_date date, p_orgn_code VARCHAR2, p_whse_code VARCHAR2) return DATE IS
4250 v_retval NUMBER;
4251 l_trans_date date;
4252 BEGIN
4253 l_trans_date := p_trans_date;
4254 v_retval := GMICCAL.trans_date_validate(p_trans_date,
4255 p_orgn_code,
4256 p_whse_code);
4257 IF (v_retval IN (-23, -25)) THEN
4258 /* -23 = Date is within a closed Inventory calendar period */
4259 /* -25 = Warehouse has been closed for the period */
4260 l_trans_date := SYSDATE;
4261 GMI_RESERVATION_UTIL.println('Completing existing transaction in current open inventory period '||
4262 to_char(l_trans_date));
4263 END IF;
4264 return l_trans_date;
4265 END GMI_TRANS_DATE;
4266
4267
4268 /* Bug 2775197. Function to check if inventory is going negative. Returns TRUE if inventory is going negative */
4269
4270 FUNCTION INVENTORY_GOING_NEG(p_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec) return BOOLEAN IS
4271 l_retval BOOLEAN := FALSE;
4272 l_noninv_ind NUMBER;
4273 l_onhand_qty NUMBER;
4274 l_allow_negative_inv NUMBER ;
4275
4276 CURSOR Cur_non_inv IS
4277 SELECT noninv_ind
4278 FROM ic_item_mst
4279 WHERE item_id = p_tran_rec.item_id;
4280
4281 CURSOR Cur_get_onhand_qty(p_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec) IS
4282 SELECT loct_onhand as qty
4283 FROM ic_loct_inv
4284 WHERE item_id = p_tran_rec.item_id
4285 AND whse_code = p_tran_rec.whse_code
4286 AND lot_id = p_tran_rec.lot_id
4287 AND location = p_tran_rec.location;
4288
4289 BEGIN
4290 OPEN Cur_non_inv;
4291 FETCH Cur_non_inv INTO l_noninv_ind;
4292 CLOSE Cur_non_inv;
4293
4294 IF l_noninv_ind = 1 THEN
4295 return FALSE;
4296 END IF;
4297
4298 OPEN Cur_get_onhand_qty(p_tran_rec);
4299 FETCH Cur_get_onhand_qty INTO l_onhand_qty;
4300 IF Cur_get_onhand_qty%FOUND THEN
4301 IF ABS(p_tran_rec.trans_qty) > l_onhand_qty THEN
4302 l_retval := TRUE;
4303 ELSE
4304 l_retval := FALSE;
4305 END IF;
4306 ELSE
4307 l_retval := TRUE;
4308 END IF;
4309 CLOSE Cur_get_onhand_qty;
4310
4311 return l_retval;
4312
4313 END INVENTORY_GOING_NEG;
4314
4315 --Check if item and warehouse comination is location control
4316 -- procedure added for bug 3380763
4317 procedure check_loct_ctl (
4318 p_inventory_item_id IN NUMBER
4319 ,p_mtl_organization_id IN NUMBER
4320 ,x_ctl_ind OUT NOCOPY VARCHAR2)
4321 IS
4322 l_item_id ic_tran_pnd.item_id%TYPE;
4323 l_loct_ctl NUMBER;
4324 l_whse_ctl NUMBER;
4325
4326 Cursor Get_item_info IS
4327 SELECT iim.item_id, iim.loct_ctl
4328 FROM ic_item_mst iim,
4329 mtl_system_items msi
4330 WHERE msi.inventory_item_id = p_inventory_item_id
4331 AND msi.organization_id = p_mtl_organization_id
4332 AND msi.segment1 = iim.item_no;
4333
4334
4335 BEGIN
4336 /* get lot_ctl and loct_ctl */
4337 GMI_reservation_Util.PrintLn('check_loct_ctl for item_id '|| p_inventory_item_id
4338 ||' for org '||p_mtl_organization_id);
4339 Open get_item_info;
4340 Fetch get_item_info
4341 Into l_item_id
4342 , l_loct_ctl
4343 ;
4344 Close get_item_info;
4345
4346 /* get whse loct_ctl */
4347 Select loct_ctl
4348 Into l_whse_ctl
4349 From ic_whse_mst
4350 Where mtl_organization_id = p_mtl_organization_id;
4351
4352 IF (l_loct_ctl * l_whse_ctl) = 0 THEN
4353 x_ctl_ind := 'N';
4354 ElSE
4355 x_ctl_ind := 'Y';
4356 END IF;
4357 GMI_reservation_Util.PrintLn('check_loct_ctl returning '|| x_ctl_ind);
4358 End check_loct_ctl;
4359
4360 -- HW 3388186
4361 -- This procedure is introduced because of WSH Consolidate backorder Line Project in 11510
4362 -- p_cons_dd_id Consolidated delivery_detail_id
4363 -- p_old_dd_ids Old delivery_detail_ids that were consolidated
4364
4365 -- This procedure will take old delivery details,source line id
4366 -- and match with the old ones to update the inventory transactions
4367 -- with new delivery_detail_id
4368 -- This procedure is called from WSHDDSPB.pls (11510), procedure: Backorder
4369 -- This procedure will be called when Consolidated Backorder Line
4370 -- is checked in Global Parameter under Shipping > Setup and
4371 -- Action is Cycle Count All
4372
4373 PROCEDURE UPDATE_NEW_LINE_DETAIL_ID
4374 ( p_cons_dd_id IN NUMBER
4375 , p_old_dd_ids IN WSH_UTIL_CORE.Id_Tab_Type
4376 , x_return_status OUT NOCOPY VARCHAR2
4377 )
4378 IS
4379
4380 -- Get the eligible records from inventory
4381 CURSOR GET_IC_RECORDS (l_source_line_id IN NUMBER
4382 , l_dd_id IN NUMBER)IS
4383 SELECT line_id,line_detail_id
4384 FROM IC_TRAN_PND IC
4385 WHERE IC.line_id = l_source_line_id
4386 AND ic.line_detail_id = l_dd_id
4387 AND IC.staged_ind = 0
4388 AND IC.trans_qty <> 0
4389 AND IC.doc_type='OMSO'
4390 AND IC.delete_mark = 0 ;
4391
4392 -- Find the proper source_line_Id from WSH using
4393 -- the consolidated dd_id
4394 CURSOR find_new_source_line (l_dd_id NUMBER) IS
4395 SELECT WDD.source_line_id
4396 FROM WSH_DELIVERY_DETAILS WDD
4397 WHERE WDD.delivery_detail_id = l_dd_id
4398 AND WDD.container_flag = 'N'
4399 AND WDD.source_code = 'OE'
4400 AND WDD.released_status='B';
4401
4402 -- local variables
4403 l_line_id NUMBER;
4404 l_ic_dd NUMBER;
4405 l_new_source_line_id NUMBER ;
4406 l_wdd_id NUMBER;
4407
4408
4409 BEGIN
4410
4411 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4412 gmi_reservation_util.println('In GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
4413 gmi_reservation_util.println('Value of p_cons_dd_id is '||p_cons_dd_id);
4414
4415 -- Find the source_line_id associated with the new consolidated
4416 -- backorder line from WSH
4417
4418 OPEN find_new_source_line( p_cons_dd_id);
4419
4420 FETCH find_new_source_line into l_new_source_line_id;
4421 IF ( find_new_source_line%NOTFOUND) THEN
4422 CLOSE find_new_source_line;
4423 gmi_reservation_util.println('Can not find source_line from WSH_DELIVERY_DETAILS for source_line'||p_cons_dd_id);
4424 x_return_status := FND_API.G_RET_STS_ERROR;
4425 RETURN;
4426 END IF;
4427 CLOSE find_new_source_line;
4428
4429 IF ( l_new_source_line_id is NOT NULL ) THEN -- source_line_id is found
4430 gmi_reservation_util.println('Found new line_id and it is '||l_new_source_line_id);
4431
4432 -- Loop through the old delivery_detail_ids to update them
4433 -- with the new values
4434 for i IN p_old_dd_ids.FIRST .. p_old_dd_ids.LAST LOOP --{
4435 l_wdd_id := p_old_dd_ids(i);
4436
4437 -- Get source_line and original line_detail_id from IC_TRAN_PND
4438
4439 OPEN GET_IC_RECORDS(l_new_source_line_id, l_wdd_id);
4440 FETCH GET_IC_RECORDS into l_line_id,l_ic_dd;
4441
4442 gmi_reservation_util.println('Value of l_line_id is '||l_new_source_line_id);
4443 gmi_reservation_util.println('Value of l_icc_dd is '||l_ic_dd);
4444 gmi_reservation_util.println('Value of old dd is '||p_old_dd_ids(i));
4445
4446
4447 -- Make sure values are not null and match old dd_id in IC_TRAN_PND
4448 IF ( ( nvl(l_line_id, 0) <> 0) AND ( p_old_dd_ids(i) = l_ic_dd )
4449 AND ( ( nvl(l_ic_dd, 0) <> 0) )) THEN
4450
4451 -- Update Inventoryu with new delivery_detail_id
4452 gmi_reservation_util.println('Update ic_tran_pnd with new line_detail_id '||p_cons_dd_id);
4453 UPDATE IC_TRAN_PND IC
4454 SET IC.line_detail_id = p_cons_dd_id
4455 WHERE IC.line_detail_id = l_ic_dd
4456 AND IC.line_id = l_line_id ;
4457
4458 IF (SQL%NOTFOUND) THEN
4459 GMI_RESERVATION_UTIL.println('Error In Updating IC_TRAN_PND');
4460 RAISE NO_DATA_FOUND;
4461 END IF; -- of error updating
4462
4463 END IF; -- of line_id and old_dd_id
4464
4465 IF GET_IC_RECORDS%ISOPEN THEN
4466 CLOSE GET_IC_RECORDS;
4467 END IF;
4468
4469 END LOOP; -- of old_dd_ids
4470 ELSE
4471 gmi_reservation_util.println('Cannot find source_line_id');
4472 END IF; -- of source_line_id
4473
4474
4475
4476
4477 -- If any cursors are open, close them!
4478 IF find_new_source_line%ISOPEN THEN
4479 CLOSE find_new_source_line;
4480 END IF;
4481
4482 IF GET_IC_RECORDS%ISOPEN THEN
4483 CLOSE GET_IC_RECORDS;
4484 END IF;
4485
4486
4487 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4488
4489 gmi_reservation_util.println('Done calling GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
4490
4491 EXCEPTION
4492
4493 WHEN NO_DATA_FOUND THEN
4494 x_return_status := FND_API.G_RET_STS_ERROR;
4495 RAISE NO_DATA_FOUND;
4496 GMI_RESERVATION_UTIL.Println('Raised When No Data Found in GMI_SHIPPING_UTIL.UPDATE_NEW_LINE_DETAIL_ID');
4497
4498 WHEN OTHERS THEN
4499
4500 IF GET_IC_RECORDS%ISOPEN THEN
4501 CLOSE GET_IC_RECORDS;
4502 END IF;
4503
4504 IF find_new_source_line%ISOPEN THEN
4505 CLOSE find_new_source_line;
4506 END IF;
4507
4508 x_return_status := FND_API.G_RET_STS_ERROR;
4509
4510 END UPDATE_NEW_LINE_DETAIL_ID;
4511
4512
4513 END GMI_Shipping_Util;