[Home] [Help]
PACKAGE BODY: APPS.GMI_MOVE_ORDER_LINE_UTIL
Source
1 PACKAGE BODY GMI_MOVE_ORDER_LINE_UTIL AS
2 /* $Header: GMIUMOLB.pls 120.0 2005/05/25 16:13:10 appldev noship $ */
3 /* ===========================================================================
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 ===========================================================================
8 | FILENAME |
9 | GMIUMOLB.pls |
10 | |
11 | DESCRIPTION |
12 | |
13 | Body of package GMI_Move_order_line_Util |
14 | |
15 | NOTES |
16 | |
17 | HISTORY |
18 | |
19 | 21-Apr-00 Created |
20 | May-2000 odab added : |
21 | - Line_Auto_Detail |
22 | - Line_Pick_Confirm |
23 | - Lock_Mo_Line |
24 | B1513119 odaboval 22-Nov-2000 : added the grouping rule queries. |
25 | |
26 | 26-SEP-01 Hverddin Added Conc Request API |
27 | - AUTO_ALLOC_CONFIRM_SRS |
28 | |
29 | |
30 | HW BUG#:2296620 Added code to support ship sets functionality | |
31 |
32 ===========================================================================
33 */
34
35 /* Global constant holding the package name */
36
37 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_MOVE_ORDER_LINE_UTIL';
38
39
40 /* Function Convert_Miss_To_Null */
41
42 PROCEDURE debug(p_message IN VARCHAR2,
43 p_module IN VARCHAR2) IS
44 BEGIN
45 inv_pick_wave_pick_confirm_pub.tracelog(p_message, p_module);
46 END;
47 FUNCTION Convert_Miss_To_Null
48 (
49 p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
50 )
51 RETURN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
52 IS
53 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec := p_mo_line_rec;
54 BEGIN
55 /* =======================================================================
56 Raise a temporary error, for Dummy calls
57 ======================================================================= */
58 /* FND_MESSAGE.SET_NAME('GMI','GMI_RSV_UNAVAILABLE');
59 OE_MSG_PUB.Add;
60 RAISE FND_API.G_EXC_ERROR;
61 */
62
63 IF l_mo_line_rec.attribute1 = FND_API.G_MISS_CHAR THEN
64 l_mo_line_rec.attribute1 := NULL;
65 END IF;
66
67 IF l_mo_line_rec.attribute10 = FND_API.G_MISS_CHAR THEN
68 l_mo_line_rec.attribute10 := NULL;
69 END IF;
70
71 IF l_mo_line_rec.attribute11 = FND_API.G_MISS_CHAR THEN
72 l_mo_line_rec.attribute11 := NULL;
73 END IF;
74
75 IF l_mo_line_rec.attribute12 = FND_API.G_MISS_CHAR THEN
76 l_mo_line_rec.attribute12 := NULL;
77 END IF;
78
79 IF l_mo_line_rec.attribute13 = FND_API.G_MISS_CHAR THEN
80 l_mo_line_rec.attribute13 := NULL;
81 END IF;
82
83 IF l_mo_line_rec.attribute14 = FND_API.G_MISS_CHAR THEN
84 l_mo_line_rec.attribute14 := NULL;
85 END IF;
86
87 IF l_mo_line_rec.attribute15 = FND_API.G_MISS_CHAR THEN
88 l_mo_line_rec.attribute15 := NULL;
89 END IF;
90
91 IF l_mo_line_rec.attribute2 = FND_API.G_MISS_CHAR THEN
92 l_mo_line_rec.attribute2 := NULL;
93 END IF;
94
95 IF l_mo_line_rec.attribute3 = FND_API.G_MISS_CHAR THEN
96 l_mo_line_rec.attribute3 := NULL;
97 END IF;
98
99 IF l_mo_line_rec.attribute4 = FND_API.G_MISS_CHAR THEN
100 l_mo_line_rec.attribute4 := NULL;
101 END IF;
102
103 IF l_mo_line_rec.attribute5 = FND_API.G_MISS_CHAR THEN
104 l_mo_line_rec.attribute5 := NULL;
105 END IF;
106
107 IF l_mo_line_rec.attribute6 = FND_API.G_MISS_CHAR THEN
108 l_mo_line_rec.attribute6 := NULL;
109 END IF;
110
111 IF l_mo_line_rec.attribute7 = FND_API.G_MISS_CHAR THEN
112 l_mo_line_rec.attribute7 := NULL;
113 END IF;
114
115 IF l_mo_line_rec.attribute8 = FND_API.G_MISS_CHAR THEN
116 l_mo_line_rec.attribute8 := NULL;
117 END IF;
118
119 IF l_mo_line_rec.attribute9 = FND_API.G_MISS_CHAR THEN
120 l_mo_line_rec.attribute9 := NULL;
121 END IF;
122
123 IF l_mo_line_rec.attribute_category = FND_API.G_MISS_CHAR THEN
124 l_mo_line_rec.attribute_category := NULL;
125 END IF;
126
127 IF l_mo_line_rec.created_by = FND_API.G_MISS_NUM THEN
128 l_mo_line_rec.created_by := NULL;
129 END IF;
130
131 IF l_mo_line_rec.creation_date = FND_API.G_MISS_DATE THEN
132 l_mo_line_rec.creation_date := NULL;
133 END IF;
134
135 IF l_mo_line_rec.date_required = FND_API.G_MISS_DATE THEN
136 l_mo_line_rec.date_required := NULL;
137 END IF;
138
139 IF l_mo_line_rec.from_locator_id = FND_API.G_MISS_NUM THEN
140 l_mo_line_rec.from_locator_id := NULL;
141 END IF;
142
143 IF l_mo_line_rec.from_subinventory_code = FND_API.G_MISS_CHAR THEN
144 l_mo_line_rec.from_subinventory_code := NULL;
145 END IF;
146
147 IF l_mo_line_rec.from_subinventory_id = FND_API.G_MISS_NUM THEN
148 l_mo_line_rec.from_subinventory_id := NULL;
149 END IF;
150
151 IF l_mo_line_rec.header_id = FND_API.G_MISS_NUM THEN
152 l_mo_line_rec.header_id := NULL;
153 END IF;
154
155 IF l_mo_line_rec.inventory_item_id = FND_API.G_MISS_NUM THEN
156 l_mo_line_rec.inventory_item_id := NULL;
157 END IF;
158
159 IF l_mo_line_rec.last_updated_by = FND_API.G_MISS_NUM THEN
160 l_mo_line_rec.last_updated_by := NULL;
161 END IF;
162
163 IF l_mo_line_rec.last_update_date = FND_API.G_MISS_DATE THEN
164 l_mo_line_rec.last_update_date := NULL;
165 END IF;
166
167 IF l_mo_line_rec.last_update_login = FND_API.G_MISS_NUM THEN
168 l_mo_line_rec.last_update_login := NULL;
169 END IF;
170
171 IF l_mo_line_rec.line_id = FND_API.G_MISS_NUM THEN
172 l_mo_line_rec.line_id := NULL;
173 END IF;
174
175 IF l_mo_line_rec.line_number = FND_API.G_MISS_NUM THEN
176 l_mo_line_rec.line_number := NULL;
177 END IF;
178
179 IF l_mo_line_rec.line_status = FND_API.G_MISS_NUM THEN
180 l_mo_line_rec.line_status := NULL;
181 END IF;
182
183
184 IF l_mo_line_rec.organization_id = FND_API.G_MISS_NUM THEN
185 l_mo_line_rec.organization_id := NULL;
186 END IF;
187
188 IF l_mo_line_rec.program_application_id = FND_API.G_MISS_NUM THEN
189 l_mo_line_rec.program_application_id := NULL;
190 END IF;
191
192 IF l_mo_line_rec.program_id = FND_API.G_MISS_NUM THEN
193 l_mo_line_rec.program_id := NULL;
194 END IF;
195
196 IF l_mo_line_rec.program_update_date = FND_API.G_MISS_DATE THEN
197 l_mo_line_rec.program_update_date := NULL;
198 END IF;
199
200 /* IF l_mo_line_rec.project_id = FND_API.G_MISS_NUM THEN
201 l_mo_line_rec.project_id := NULL;
202 END IF;
203 */
204
205 IF l_mo_line_rec.quantity = FND_API.G_MISS_NUM THEN
206 l_mo_line_rec.quantity := NULL;
207 END IF;
208
209 IF l_mo_line_rec.quantity_delivered = FND_API.G_MISS_NUM THEN
210 l_mo_line_rec.quantity_delivered := NULL;
211 END IF;
212
213 IF l_mo_line_rec.quantity_detailed = FND_API.G_MISS_NUM THEN
214 l_mo_line_rec.quantity_detailed := NULL;
215 END IF;
216
217 IF l_mo_line_rec.reason_id = FND_API.G_MISS_NUM THEN
218 l_mo_line_rec.reason_id := NULL;
219 END IF;
220
221 IF l_mo_line_rec.reference = FND_API.G_MISS_CHAR THEN
222 l_mo_line_rec.reference := NULL;
223 END IF;
224
225 IF l_mo_line_rec.reference_id = FND_API.G_MISS_NUM THEN
226 l_mo_line_rec.reference_id := NULL;
227 END IF;
228
229 IF l_mo_line_rec.reference_type_code = FND_API.G_MISS_NUM THEN
230 l_mo_line_rec.reference_type_code := NULL;
231 END IF;
232
233 IF l_mo_line_rec.request_id = FND_API.G_MISS_NUM THEN
234 l_mo_line_rec.request_id := NULL;
235 END IF;
236
237 /*
238 IF l_mo_line_rec.revision = FND_API.G_MISS_CHAR THEN
239 l_mo_line_rec.revision := NULL;
240 END IF;
241
242 IF l_mo_line_rec.serial_number_end = FND_API.G_MISS_CHAR THEN
243 l_mo_line_rec.serial_number_end := NULL;
244 END IF;
245
246 IF l_mo_line_rec.serial_number_start = FND_API.G_MISS_CHAR THEN
247 l_mo_line_rec.serial_number_start := NULL;
248 END IF;
249
250 IF l_mo_line_rec.task_id = FND_API.G_MISS_NUM THEN
251 l_mo_line_rec.task_id := NULL;
252 END IF;
253
254 IF l_mo_line_rec.lot_number = FND_API.G_MISS_CHAR THEN
255 l_mo_line_rec.lot_number := NULL;
256 END IF;
257 */
258
259 IF l_mo_line_rec.status_date = FND_API.G_MISS_DATE THEN
260 l_mo_line_rec.status_date := NULL;
261 END IF;
262
263 IF l_mo_line_rec.to_account_id = FND_API.G_MISS_NUM THEN
264 l_mo_line_rec.to_account_id := NULL;
265 END IF;
266
267 IF l_mo_line_rec.to_locator_id = FND_API.G_MISS_NUM THEN
268 l_mo_line_rec.to_locator_id := NULL;
269 END IF;
270
271 IF l_mo_line_rec.to_subinventory_code = FND_API.G_MISS_CHAR THEN
272 l_mo_line_rec.to_subinventory_code := NULL;
273 END IF;
274
275 IF l_mo_line_rec.to_subinventory_id = FND_API.G_MISS_NUM THEN
276 l_mo_line_rec.to_subinventory_id := NULL;
277 END IF;
278
279 IF l_mo_line_rec.transaction_header_id = FND_API.G_MISS_NUM THEN
280 l_mo_line_rec.transaction_header_id := NULL;
281 END IF;
282
283 IF l_mo_line_rec.uom_code = FND_API.G_MISS_CHAR THEN
284 l_mo_line_rec.uom_code := NULL;
285 END IF;
286
287 IF l_mo_line_rec.transaction_type_id = FND_API.G_MISS_NUM THEN
288 l_mo_line_rec.transaction_type_id := NULL;
289 END IF;
290
291 IF l_mo_line_rec.transaction_source_type_id = FND_API.G_MISS_NUM THEN
292 l_mo_line_rec.transaction_source_type_id := NULL;
293 END IF;
294
295 IF l_mo_line_rec.txn_source_id = FND_API.G_MISS_NUM THEN
296 l_mo_line_rec.txn_source_id := NULL;
297 END IF;
298
299 IF l_mo_line_rec.txn_source_line_id = FND_API.G_MISS_NUM THEN
300 l_mo_line_rec.txn_source_line_id := NULL;
301 END IF;
302
303 IF l_mo_line_rec.txn_source_line_detail_id = FND_API.G_MISS_NUM THEN
304 l_mo_line_rec.txn_source_line_detail_id := NULL;
305 END IF;
306
307 IF l_mo_line_rec.to_organization_id = FND_API.G_MISS_NUM THEN
308 l_mo_line_rec.to_organization_id := NULL;
309 END IF;
310
311 IF l_mo_line_rec.primary_quantity = FND_API.G_MISS_NUM THEN
312 l_mo_line_rec.primary_quantity := NULL;
313 END IF;
314
315 IF l_mo_line_rec.pick_strategy_id = FND_API.G_MISS_NUM THEN
316 l_mo_line_rec.pick_strategy_id := NULL;
317 END IF;
318
319 IF l_mo_line_rec.put_away_strategy_id = FND_API.G_MISS_NUM THEN
320 l_mo_line_rec.put_away_strategy_id := NULL;
321 END IF;
322
323 IF l_mo_line_rec.lot_no = FND_API.G_MISS_CHAR THEN
324 l_mo_line_rec.lot_no := NULL;
325 END IF;
326
327 IF l_mo_line_rec.sublot_no = FND_API.G_MISS_CHAR THEN
328 l_mo_line_rec.sublot_no := NULL;
329 END IF;
330
331 IF l_mo_line_rec.qc_grade = FND_API.G_MISS_CHAR THEN
332 l_mo_line_rec.qc_grade := NULL;
333 END IF;
334
335 IF l_mo_line_rec.secondary_quantity = FND_API.G_MISS_NUM THEN
336 l_mo_line_rec.secondary_quantity:= NULL;
337 END IF;
338
339 IF l_mo_line_rec.secondary_uom_code= FND_API.G_MISS_CHAR THEN
340 l_mo_line_rec.secondary_uom_code:= NULL;
341 END IF;
342
343 IF l_mo_line_rec.secondary_quantity_delivered = FND_API.G_MISS_NUM THEN
344 l_mo_line_rec.secondary_quantity_delivered:= NULL;
345 END IF;
346
347 IF l_mo_line_rec.secondary_quantity_detailed = FND_API.G_MISS_NUM THEN
348 l_mo_line_rec.secondary_quantity_detailed:= NULL;
349 END IF;
350
351 -- HW BUG#:2296620
352 IF l_mo_line_rec.ship_set_id = FND_API.G_MISS_NUM THEN
353 l_mo_line_rec.ship_set_id:= NULL;
354 END IF;
355
356 RETURN l_mo_line_rec;
357
358
359 END Convert_Miss_To_Null;
360
361 /* Procedure Update_Row */
362
363 PROCEDURE Update_Row
364 ( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
365 )
366 IS
367 BEGIN
368
369 UPDATE IC_TXN_REQUEST_LINES
370 SET ATTRIBUTE1 = p_mo_line_rec.attribute1
371 , ATTRIBUTE10 = p_mo_line_rec.attribute10
372 , ATTRIBUTE11 = p_mo_line_rec.attribute11
373 , ATTRIBUTE12 = p_mo_line_rec.attribute12
374 , ATTRIBUTE13 = p_mo_line_rec.attribute13
375 , ATTRIBUTE14 = p_mo_line_rec.attribute14
376 , ATTRIBUTE15 = p_mo_line_rec.attribute15
377 , ATTRIBUTE2 = p_mo_line_rec.attribute2
378 , ATTRIBUTE3 = p_mo_line_rec.attribute3
379 , ATTRIBUTE4 = p_mo_line_rec.attribute4
380 , ATTRIBUTE5 = p_mo_line_rec.attribute5
381 , ATTRIBUTE6 = p_mo_line_rec.attribute6
382 , ATTRIBUTE7 = p_mo_line_rec.attribute7
383 , ATTRIBUTE8 = p_mo_line_rec.attribute8
384 , ATTRIBUTE9 = p_mo_line_rec.attribute9
385 , ATTRIBUTE_CATEGORY = p_mo_line_rec.attribute_category
386 , CREATED_BY = p_mo_line_rec.created_by
387 , CREATION_DATE = p_mo_line_rec.creation_date
388 , DATE_REQUIRED = p_mo_line_rec.date_required
389 , FROM_LOCATOR_ID = p_mo_line_rec.from_locator_id
390 , FROM_SUBINVENTORY_CODE = p_mo_line_rec.from_subinventory_code
391 , FROM_SUBINVENTORY_ID = p_mo_line_rec.from_subinventory_id
392 , HEADER_ID = p_mo_line_rec.header_id
393 , INVENTORY_ITEM_ID = p_mo_line_rec.inventory_item_id
394 , LAST_UPDATED_BY = p_mo_line_rec.last_updated_by
395 , LAST_UPDATE_DATE = p_mo_line_rec.last_update_date
396 , LAST_UPDATE_LOGIN = p_mo_line_rec.last_update_login
397 , LINE_ID = p_mo_line_rec.line_id
398 , LINE_NUMBER = p_mo_line_rec.line_number
399 , LINE_STATUS = p_mo_line_rec.line_status
400 /* , LOT_NUMBER = p_mo_line_rec.lot_number */
401 , ORGANIZATION_ID = p_mo_line_rec.organization_id
402 , PROGRAM_APPLICATION_ID = p_mo_line_rec.program_application_id
403 , PROGRAM_ID = p_mo_line_rec.program_id
404 , PROGRAM_UPDATE_DATE = p_mo_line_rec.program_update_date
405 /* , PROJECT_ID = p_mo_line_rec.project_id */
406 , QUANTITY = p_mo_line_rec.quantity
407 , QUANTITY_DELIVERED = p_mo_line_rec.quantity_delivered
408 , QUANTITY_DETAILED = p_mo_line_rec.quantity_detailed
409 , REASON_ID = p_mo_line_rec.reason_id
410 , REFERENCE = p_mo_line_rec.reference
411 , REFERENCE_ID = p_mo_line_rec.reference_id
412 , REFERENCE_TYPE_CODE = p_mo_line_rec.reference_type_code
413 , REQUEST_ID = p_mo_line_rec.request_id
414 /* , REVISION = p_mo_line_rec.revision */
415 /* , SERIAL_NUMBER_END = p_mo_line_rec.serial_number_end */
416 /* , SERIAL_NUMBER_START = p_mo_line_rec.serial_number_start */
417 , STATUS_DATE = p_mo_line_rec.status_date
418 /* , TASK_ID = p_mo_line_rec.task_id */
419 , TO_ACCOUNT_ID = p_mo_line_rec.to_account_id
420 , TO_LOCATOR_ID = p_mo_line_rec.to_locator_id
421 , TO_SUBINVENTORY_CODE = p_mo_line_rec.to_subinventory_code
422 , TO_SUBINVENTORY_ID = p_mo_line_rec.to_subinventory_id
423 , TRANSACTION_HEADER_ID = p_mo_line_rec.transaction_header_id
424 , UOM_CODE = p_mo_line_rec.uom_code
425 , TRANSACTION_TYPE_ID = p_mo_line_rec.transaction_type_id
426 , TRANSACTION_SOURCE_TYPE_ID = p_mo_line_rec.transaction_source_type_id
427 , TXN_SOURCE_ID = p_mo_line_rec.txn_source_id
428 , TXN_SOURCE_LINE_ID = p_mo_line_rec.txn_source_line_id
429 , TXN_SOURCE_LINE_DETAIL_ID = p_mo_line_rec.txn_source_line_detail_id
430 , TO_ORGANIZATION_ID = p_mo_line_rec.to_organization_id
431 , PRIMARY_QUANTITY = p_mo_line_rec.primary_quantity
432 , PICK_STRATEGY_ID = p_mo_line_rec.pick_strategy_id
433 , PUT_AWAY_STRATEGY_ID = p_mo_line_rec.put_away_strategy_id
434 , LOT_NO = p_mo_line_rec.lot_no
435 , SUBLOT_NO = p_mo_line_rec.sublot_no
436 , QC_GRADE = p_mo_line_rec.qc_grade
437 , SECONDARY_QUANTITY = p_mo_line_rec.secondary_quantity
438 , SECONDARY_UOM_CODE = p_mo_line_rec.secondary_uom_code
439 , SECONDARY_QUANTITY_DELIVERED =p_mo_line_rec.secondary_quantity_delivered
440 , SECONDARY_QUANTITY_DETAILED = p_mo_line_rec.secondary_quantity_detailed
441 -- HW BUG#:2296620 added ship_set_id
442 , SHIP_SET_ID = p_mo_line_rec.ship_set_id
443 WHERE LINE_ID = p_mo_line_rec.line_id
444 ;
445 gmi_reservation_util.println('Done updating ic_txn_requ_lines');
446
447 EXCEPTION
448
449 WHEN OTHERS THEN
450
451 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
452 THEN
453 FND_MSG_PUB.Add_Exc_Msg
454 ( G_PKG_NAME
455 , 'Update_Row'
456 );
457 END IF;
458
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460
461 END Update_Row;
462
463 /* Procedure Update_Row_Status */
464
465 PROCEDURE Update_Row_Status
466 ( p_line_id IN Number,
467 p_status IN Number
468 )
469 IS
470 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
471 BEGIN
472 l_mo_line_rec := GMI_MOVE_ORDER_LINE_util.Query_Row( p_line_id );
473 l_mo_line_rec.Line_Status := p_status;
474 l_mo_line_rec.last_update_date := SYSDATE;
475 l_mo_line_rec.last_updated_by := FND_GLOBAL.USER_ID;
476 l_mo_line_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
477
478 GMI_MOVE_ORDER_LINE_UTIL.Update_Row(l_mo_line_rec);
479
480 EXCEPTION
481
482 WHEN OTHERS THEN
483
484 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
485 THEN
486 FND_MSG_PUB.Add_Exc_Msg
487 ( G_PKG_NAME
488 , 'Update_Row_Status'
489 );
490 END IF;
491
492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
493
494 END Update_Row_Status;
495
496 /* Procedure Insert_Row */
497
498 PROCEDURE Insert_Row
499 ( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
500 )
501 IS
502 err_num NUMBER;
503 err_msg VARCHAR2(100);
504 BEGIN
505
506 gmi_reservation_util.println('In insert row in move order line');
507
508 WSH_UTIL_CORE.Println(' Line Insert DML');
509 WSH_UTIL_CORE.Println(' Line id =>' || p_mo_line_rec.line_id );
510 gmi_reservation_util.Println(' Line id =>' || p_mo_line_rec.line_id );
511 WSH_UTIL_CORE.Println(' Line number =>' || p_mo_line_rec.line_number );
512 WSH_UTIL_CORE.Println(' Header id =>' || p_mo_line_rec.header_id );
513 WSH_UTIL_CORE.Println(' Inventory Item Id =>' || p_mo_line_rec.inventory_item_id );
514 WSH_UTIL_CORE.Println(' date required =>' || p_mo_line_rec.date_required );
515 WSH_UTIL_CORE.Println(' Line Status =>' || p_mo_line_rec.line_status );
516 WSH_UTIL_CORE.Println(' Requested qty =>' || p_mo_line_rec.quantity );
517 WSH_UTIL_CORE.Println(' Detailed qty =>' || p_mo_line_rec.quantity_detailed );
518
519 INSERT INTO IC_TXN_REQUEST_LINES
520 ( ATTRIBUTE1
521 , ATTRIBUTE10
522 , ATTRIBUTE11
523 , ATTRIBUTE12
524 , ATTRIBUTE13
525 , ATTRIBUTE14
526 , ATTRIBUTE15
527 , ATTRIBUTE2
528 , ATTRIBUTE3
529 , ATTRIBUTE4
530 , ATTRIBUTE5
531 , ATTRIBUTE6
532 , ATTRIBUTE7
533 , ATTRIBUTE8
534 , ATTRIBUTE9
535 , ATTRIBUTE_CATEGORY
536 , CREATED_BY
537 , CREATION_DATE
538 , DATE_REQUIRED
539 , FROM_LOCATOR_ID
540 , FROM_SUBINVENTORY_CODE
541 , FROM_SUBINVENTORY_ID
542 , HEADER_ID
543 , INVENTORY_ITEM_ID
544 , LAST_UPDATED_BY
545 , LAST_UPDATE_DATE
546 , LAST_UPDATE_LOGIN
547 , LINE_ID
548 , LINE_NUMBER
549 , LINE_STATUS
550 , ORGANIZATION_ID
551 , PROGRAM_APPLICATION_ID
552 , PROGRAM_ID
553 , PROGRAM_UPDATE_DATE
554 , QUANTITY
555 , QUANTITY_DELIVERED
556 , QUANTITY_DETAILED
557 , REASON_ID
558 , REFERENCE
559 , REFERENCE_ID
560 , REFERENCE_TYPE_CODE
561 , REQUEST_ID
562 , STATUS_DATE
563 , TO_ACCOUNT_ID
564 , TO_LOCATOR_ID
565 , TO_SUBINVENTORY_CODE
566 , TO_SUBINVENTORY_ID
567 , TRANSACTION_HEADER_ID
568 , UOM_CODE
569 , TRANSACTION_TYPE_ID
570 , TRANSACTION_SOURCE_TYPE_ID
571 , TXN_SOURCE_ID
572 , TXN_SOURCE_LINE_ID
573 , TXN_SOURCE_LINE_DETAIL_ID
574 , TO_ORGANIZATION_ID
575 , PRIMARY_QUANTITY
576 , PICK_STRATEGY_ID
577 , PUT_AWAY_STRATEGY_ID
578 , LOT_NO
579 , SUBLOT_NO
580 , QC_GRADE
581 , SECONDARY_QUANTITY
582 , SECONDARY_UOM_CODE
583 , SECONDARY_QUANTITY_DELIVERED
584 , SECONDARY_QUANTITY_DETAILED
585 --HW BUG#:2296620 added ship_set_id
586 , SHIP_SET_ID
587 )
588 VALUES
589 ( p_mo_line_rec.attribute1
590 , p_mo_line_rec.attribute10
591 , p_mo_line_rec.attribute11
592 , p_mo_line_rec.attribute12
593 , p_mo_line_rec.attribute13
594 , p_mo_line_rec.attribute14
595 , p_mo_line_rec.attribute15
596 , p_mo_line_rec.attribute2
597 , p_mo_line_rec.attribute3
598 , p_mo_line_rec.attribute4
599 , p_mo_line_rec.attribute5
600 , p_mo_line_rec.attribute6
601 , p_mo_line_rec.attribute7
602 , p_mo_line_rec.attribute8
603 , p_mo_line_rec.attribute9
604 , p_mo_line_rec.attribute_category
605 , p_mo_line_rec.created_by
606 , p_mo_line_rec.creation_date
607 , p_mo_line_rec.date_required
608 , p_mo_line_rec.from_locator_id
609 , p_mo_line_rec.from_subinventory_code
610 , p_mo_line_rec.from_subinventory_id
611 , p_mo_line_rec.header_id
612 , p_mo_line_rec.inventory_item_id
613 , p_mo_line_rec.last_updated_by
614 , p_mo_line_rec.last_update_date
615 , p_mo_line_rec.last_update_login
616 , p_mo_line_rec.line_id
617 , p_mo_line_rec.line_number
618 , p_mo_line_rec.line_status
619 , p_mo_line_rec.organization_id
620 , p_mo_line_rec.program_application_id
621 , p_mo_line_rec.program_id
622 , p_mo_line_rec.program_update_date
623 , p_mo_line_rec.quantity
624 , p_mo_line_rec.quantity_delivered
625 , p_mo_line_rec.quantity_detailed
626 , p_mo_line_rec.reason_id
627 , p_mo_line_rec.reference
628 , p_mo_line_rec.reference_id
629 , p_mo_line_rec.reference_type_code
630 , p_mo_line_rec.request_id
631 , p_mo_line_rec.status_date
632 , p_mo_line_rec.to_account_id
633 , p_mo_line_rec.to_locator_id
634 , p_mo_line_rec.to_subinventory_code
635 , p_mo_line_rec.to_subinventory_id
636 , p_mo_line_rec.transaction_header_id
637 , p_mo_line_rec.uom_code
638 , p_mo_line_rec.transaction_type_id
639 , p_mo_line_rec.transaction_source_type_id
640 , p_mo_line_rec.txn_source_id
641 , p_mo_line_rec.txn_source_line_id
642 , p_mo_line_rec.txn_source_line_detail_id
643 , p_mo_line_rec.to_organization_id
644 , p_mo_line_rec.primary_quantity
645 , p_mo_line_rec.pick_strategy_id
646 , p_mo_line_rec.put_away_strategy_id
647 , p_mo_line_rec.lot_no
648 , p_mo_line_rec.sublot_no
649 , p_mo_line_rec.qc_grade
650 , p_mo_line_rec.secondary_quantity
651 , p_mo_line_rec.secondary_uom_code
652 , p_mo_line_rec.secondary_quantity_delivered
653 , p_mo_line_rec.secondary_quantity_detailed
654 -- HW BUG#:2296620 added ship_set_id
655 , p_mo_line_rec.ship_set_id
656 );
657
658 gmi_reservation_util.println('End of insert row in move order lines');
659
660 EXCEPTION
661
662 WHEN OTHERS THEN
663
664 err_num :=SQLCODE;
665 err_msg :=SUBSTR(SQLERRM,1 ,100);
666
667 WSH_UTIL_CORE.Println(' Line Insert Error => ' || err_num || err_msg);
668 gmi_reservation_util.Println(' Line Insert Error => ' || err_num || err_msg);
669
670 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
671 THEN
672 FND_MSG_PUB.Add_Exc_Msg
673 ( G_PKG_NAME
674 , 'Insert_Row'
675 );
676 END IF;
677
678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679
680 END Insert_Row;
681
682 /* Procedure Delete_Row */
683
684 PROCEDURE Delete_Row
685 ( p_line_id IN NUMBER
686 )
687 IS
688 BEGIN
689
690 DELETE FROM IC_TXN_REQUEST_LINES
691 WHERE LINE_ID = p_line_id
692 ;
693
694
695 EXCEPTION
696
697 WHEN OTHERS THEN
698
699 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
700 THEN
701 FND_MSG_PUB.Add_Exc_Msg
702 ( G_PKG_NAME
703 , 'Delete_Row'
704 );
705 END IF;
706
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708
709 END Delete_Row;
710
711 /* Function Get_Lines */
712
713 FUNCTION Get_Lines
714 ( p_header_id IN NUMBER
715 ) RETURN GMI_Move_Order_GLOBAL.mo_line_tbl
716 IS
717 BEGIN
718
719 RETURN Query_Rows
720 ( p_header_id => p_header_id
721 );
722
723
724 END Get_Lines;
725
726 /* Function Query_Row */
727
728 FUNCTION Query_Row
729 ( p_line_id IN NUMBER
730 ) RETURN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
731 IS
732 BEGIN
733
734 RETURN Query_Rows
735 ( p_line_id => p_line_id
736 )(1);
737
738
739 END Query_Row;
740
741 /* Function Query_Rows */
742 -- HW BUG#:2643440, removed intitalization of G_MISS_XXX
743 -- to p_line_id and p_header_id
744
745 FUNCTION Query_Rows
746 ( p_line_id IN NUMBER default NULL
747 , p_header_id IN NUMBER default NULL
748
749 ) RETURN GMI_MOVE_ORDER_GLOBAL.mo_line_tbl
750 IS
751 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
752 l_mo_line_tbl GMI_MOVE_ORDER_GLOBAL.mo_line_tbl;
753
754
755 CURSOR l_mo_line_csr IS
756 SELECT ATTRIBUTE1
757 , ATTRIBUTE10
758 , ATTRIBUTE11
759 , ATTRIBUTE12
760 , ATTRIBUTE13
761 , ATTRIBUTE14
762 , ATTRIBUTE15
763 , ATTRIBUTE2
764 , ATTRIBUTE3
765 , ATTRIBUTE4
766 , ATTRIBUTE5
767 , ATTRIBUTE6
768 , ATTRIBUTE7
769 , ATTRIBUTE8
770 , ATTRIBUTE9
771 , ATTRIBUTE_CATEGORY
772 , CREATED_BY
773 , CREATION_DATE
774 , DATE_REQUIRED
775 , FROM_LOCATOR_ID
776 , FROM_SUBINVENTORY_CODE
777 , FROM_SUBINVENTORY_ID
778 , HEADER_ID
779 , INVENTORY_ITEM_ID
780 , LAST_UPDATED_BY
781 , LAST_UPDATE_DATE
782 , LAST_UPDATE_LOGIN
783 , LINE_ID
784 , LINE_NUMBER
785 , LINE_STATUS
786 /* , LOT_NUMBER */
787 , ORGANIZATION_ID
788 , PROGRAM_APPLICATION_ID
789 , PROGRAM_ID
790 , PROGRAM_UPDATE_DATE
791 /* , PROJECT_ID */
792 , QUANTITY
793 , QUANTITY_DELIVERED
794 , QUANTITY_DETAILED
795 , REASON_ID
796 , REFERENCE
797 , REFERENCE_ID
798 , REFERENCE_TYPE_CODE
799 , REQUEST_ID
800 /* , REVISION */
801 /* , SERIAL_NUMBER_END */
802 /* , SERIAL_NUMBER_START */
803 , STATUS_DATE
804 /* , TASK_ID */
805 , TO_ACCOUNT_ID
806 , TO_LOCATOR_ID
807 , TO_SUBINVENTORY_CODE
808 , TO_SUBINVENTORY_ID
809 , TRANSACTION_HEADER_ID
810 , UOM_CODE
811 , TRANSACTION_TYPE_ID
812 , TRANSACTION_SOURCE_TYPE_ID
813 , TXN_SOURCE_ID
814 , TXN_SOURCE_LINE_ID
815 , TXN_SOURCE_LINE_DETAIL_ID
816 , TO_ORGANIZATION_ID
817 , PRIMARY_QUANTITY
818 , PICK_STRATEGY_ID
819 , PUT_AWAY_STRATEGY_ID
820 , LOT_NO
821 , SUBLOT_NO
822 , QC_GRADE
823 , SECONDARY_QUANTITY
824 , SECONDARY_UOM_CODE
825 , SECONDARY_QUANTITY_DELIVERED
826 , SECONDARY_QUANTITY_DETAILED
827 -- HW BUG#:2296620 added ship_set_id
828 , SHIP_SET_ID
829 FROM IC_TXN_REQUEST_LINES
830 WHERE ( LINE_ID = p_line_id
831 )
832 OR ( HEADER_ID = p_header_id
833 );
834
835
836 BEGIN
837
838 -- HW BUG#:2643440. Removed the AND condition to check for FND_API.G_MISS_NUM
839 -- for both p_line_id and p_header_id
840 IF
841 (p_line_id IS NOT NULL)
842 AND
843 (p_header_id IS NOT NULL)
844 THEN
845 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
846 THEN
847 FND_MSG_PUB.Add_Exc_Msg
848 ( G_PKG_NAME
849 , 'Query Rows'
850 , 'Keys are mutually exclusive: line_id = '|| p_line_id || ', header_id = '|| p_header_id
851 );
852 END IF;
853
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855
856 END IF;
857
858
859 /* Loop over fetched records */
860
861 FOR l_implicit_rec IN l_mo_line_csr LOOP
862
863 l_mo_line_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
864 l_mo_line_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
865 l_mo_line_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
866 l_mo_line_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
867 l_mo_line_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
868 l_mo_line_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
869 l_mo_line_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
870 l_mo_line_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
871 l_mo_line_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
872 l_mo_line_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
873 l_mo_line_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
874 l_mo_line_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
875 l_mo_line_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
876 l_mo_line_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
877 l_mo_line_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
878 l_mo_line_rec.attribute_category := l_implicit_rec.ATTRIBUTE_CATEGORY;
879 l_mo_line_rec.created_by := l_implicit_rec.CREATED_BY;
880 l_mo_line_rec.creation_date := l_implicit_rec.CREATION_DATE;
881 l_mo_line_rec.date_required := l_implicit_rec.DATE_REQUIRED;
882 l_mo_line_rec.from_locator_id := l_implicit_rec.FROM_LOCATOR_ID;
883 l_mo_line_rec.from_subinventory_code := l_implicit_rec.FROM_SUBINVENTORY_CODE;
884 l_mo_line_rec.from_subinventory_id := l_implicit_rec.FROM_SUBINVENTORY_ID;
885 l_mo_line_rec.header_id := l_implicit_rec.HEADER_ID;
886 l_mo_line_rec.inventory_item_id := l_implicit_rec.INVENTORY_ITEM_ID;
887 l_mo_line_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
888 l_mo_line_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
889 l_mo_line_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
890 l_mo_line_rec.line_id := l_implicit_rec.LINE_ID;
891 l_mo_line_rec.line_number := l_implicit_rec.LINE_NUMBER;
892 l_mo_line_rec.line_status := l_implicit_rec.LINE_STATUS;
893 /* l_mo_line_rec.lot_number := l_implicit_rec.LOT_NUMBER; */
894 l_mo_line_rec.organization_id := l_implicit_rec.ORGANIZATION_ID;
895 l_mo_line_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
896 l_mo_line_rec.program_id := l_implicit_rec.PROGRAM_ID;
897 l_mo_line_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
898 /* l_mo_line_rec.project_id := l_implicit_rec.PROJECT_ID; */
899 l_mo_line_rec.quantity := l_implicit_rec.QUANTITY;
900 l_mo_line_rec.quantity_delivered := l_implicit_rec.QUANTITY_DELIVERED;
901 l_mo_line_rec.quantity_detailed := l_implicit_rec.QUANTITY_DETAILED;
902 l_mo_line_rec.reason_id := l_implicit_rec.REASON_ID;
903 l_mo_line_rec.reference := l_implicit_rec.REFERENCE;
904 l_mo_line_rec.reference_id := l_implicit_rec.REFERENCE_ID;
905 l_mo_line_rec.reference_type_code := l_implicit_rec.REFERENCE_TYPE_CODE;
906 l_mo_line_rec.request_id := l_implicit_rec.REQUEST_ID;
907 /* l_mo_line_rec.revision := l_implicit_rec.REVISION; */
908 /* l_mo_line_rec.serial_number_end := l_implicit_rec.SERIAL_NUMBER_END; */
909 /* l_mo_line_rec.serial_number_start := l_implicit_rec.SERIAL_NUMBER_START; */
910 l_mo_line_rec.status_date := l_implicit_rec.STATUS_DATE;
911 /* l_mo_line_rec.task_id := l_implicit_rec.TASK_ID; */
912 l_mo_line_rec.to_account_id := l_implicit_rec.TO_ACCOUNT_ID;
913 l_mo_line_rec.to_locator_id := l_implicit_rec.TO_LOCATOR_ID;
914 l_mo_line_rec.to_subinventory_code := l_implicit_rec.TO_SUBINVENTORY_CODE;
915 l_mo_line_rec.to_subinventory_id := l_implicit_rec.TO_SUBINVENTORY_ID;
916 l_mo_line_rec.transaction_header_id := l_implicit_rec.TRANSACTION_HEADER_ID;
917 l_mo_line_rec.uom_code := l_implicit_rec.UOM_CODE;
918 l_mo_line_rec.transaction_type_id := l_implicit_rec.TRANSACTION_TYPE_ID;
919 l_mo_line_rec.transaction_source_type_id := l_implicit_rec.TRANSACTION_SOURCE_TYPE_ID;
920 l_mo_line_rec.txn_source_id := l_implicit_rec.TXN_SOURCE_ID;
921 l_mo_line_rec.txn_source_line_id := l_implicit_rec.TXN_SOURCE_LINE_ID;
922 l_mo_line_rec.txn_source_line_detail_id := l_implicit_rec.TXN_SOURCE_LINE_DETAIL_ID;
923 l_mo_line_rec.to_organization_id := l_implicit_rec.TO_ORGANIZATION_ID;
924 l_mo_line_rec.primary_quantity := l_implicit_rec.PRIMARY_QUANTITY;
925 l_mo_line_rec.pick_strategy_id := l_implicit_rec.PICK_STRATEGY_ID;
926 l_mo_line_rec.put_away_strategy_id := l_implicit_rec.PUT_AWAY_STRATEGY_ID;
927 l_mo_line_rec.lot_no := l_implicit_rec.lot_no;
928 l_mo_line_rec.sublot_no := l_implicit_rec.sublot_no;
929 l_mo_line_rec.qc_grade := l_implicit_rec.qc_grade;
930 l_mo_line_rec.secondary_quantity := l_implicit_rec.secondary_quantity;
931 l_mo_line_rec.secondary_uom_code := l_implicit_rec.secondary_uom_code;
932 l_mo_line_rec.secondary_quantity_delivered := l_implicit_rec.secondary_quantity_delivered;
933 l_mo_line_rec.secondary_quantity_detailed :=l_implicit_rec.secondary_quantity_detailed;
934 -- HW BUG#:2296620 added ship_set_id
935 l_mo_line_rec.ship_set_id := l_implicit_rec.ship_set_id ;
936 l_mo_line_tbl(l_mo_line_tbl.COUNT + 1) := l_mo_line_rec;
937
938 END LOOP;
939
940
941 /* PK sent and no rows found */
942
943 -- HW BUG#:2643440. Removed the AND condition to check for FND_API.G_MISS_NUM
944 -- for p_line_id
945 IF
946 (p_line_id IS NOT NULL)
947 AND
948 (l_mo_line_tbl.COUNT = 0)
949 THEN
950 RAISE NO_DATA_FOUND;
951 END IF;
952
953
954 /* Return fetched table */
955
956 RETURN l_mo_line_tbl;
957
958 EXCEPTION
959
960 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
961
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963
964 WHEN OTHERS THEN
965
966 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
967 THEN
968 FND_MSG_PUB.Add_Exc_Msg
969 ( G_PKG_NAME
970 , 'Query_Rows'
971 );
972 END IF;
973
974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975
976 END Query_Rows;
977
978 /* Procedure lock_Row */
979
980 PROCEDURE Lock_Row
981 ( x_return_status OUT NOCOPY VARCHAR2
982 , p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
983 , x_mo_line_rec OUT NOCOPY GMI_MOVE_ORDER_GLOBAL.mo_line_rec
984 )
985 IS
986 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
987
988 CURSOR c_lock_mol( mo_line_id IN NUMBER) IS
989 SELECT ATTRIBUTE1
990 , ATTRIBUTE10
991 , ATTRIBUTE11
992 , ATTRIBUTE12
993 , ATTRIBUTE13
994 , ATTRIBUTE14
995 , ATTRIBUTE15
996 , ATTRIBUTE2
997 , ATTRIBUTE3
998 , ATTRIBUTE4
999 , ATTRIBUTE5
1000 , ATTRIBUTE6
1001 , ATTRIBUTE7
1002 , ATTRIBUTE8
1003 , ATTRIBUTE9
1004 , ATTRIBUTE_CATEGORY
1005 , CREATED_BY
1006 , CREATION_DATE
1007 , DATE_REQUIRED
1008 , FROM_LOCATOR_ID
1009 , FROM_SUBINVENTORY_CODE
1010 , FROM_SUBINVENTORY_ID
1011 , HEADER_ID
1012 , INVENTORY_ITEM_ID
1013 , LAST_UPDATED_BY
1014 , LAST_UPDATE_DATE
1015 , LAST_UPDATE_LOGIN
1016 , LINE_ID
1017 , LINE_NUMBER
1018 , LINE_STATUS
1019 , ORGANIZATION_ID
1020 , PROGRAM_APPLICATION_ID
1021 , PROGRAM_ID
1022 , PROGRAM_UPDATE_DATE
1023 , QUANTITY
1024 , QUANTITY_DELIVERED
1025 , QUANTITY_DETAILED
1026 , REASON_ID
1027 , REFERENCE
1028 , REFERENCE_ID
1029 , REFERENCE_TYPE_CODE
1030 , REQUEST_ID
1031 , STATUS_DATE
1032 , TO_ACCOUNT_ID
1033 , TO_LOCATOR_ID
1034 , TO_SUBINVENTORY_CODE
1035 , TO_SUBINVENTORY_ID
1036 , TRANSACTION_HEADER_ID
1037 , UOM_CODE
1038 , TRANSACTION_TYPE_ID
1039 , TRANSACTION_SOURCE_TYPE_ID
1040 , TXN_SOURCE_ID
1041 , TXN_SOURCE_LINE_ID
1042 , TXN_SOURCE_LINE_DETAIL_ID
1043 , TO_ORGANIZATION_ID
1044 , PRIMARY_QUANTITY
1045 , PICK_STRATEGY_ID
1046 , PUT_AWAY_STRATEGY_ID
1047 , LOT_NO
1048 , SUBLOT_NO
1049 , QC_GRADE
1050 , SECONDARY_QUANTITY
1051 , SECONDARY_UOM_CODE
1052 , SECONDARY_QUANTITY_DELIVERED
1053 , SECONDARY_QUANTITY_DETAILED
1054 -- HW BUG#:2296620 added ship_set_id
1055 , SHIP_SET_ID
1056 FROM IC_TXN_REQUEST_LINES
1057 WHERE LINE_ID = mo_line_id
1058 FOR UPDATE NOWAIT;
1059
1060 BEGIN
1061
1062 x_return_status := FND_API.G_RET_STS_SUCCESS;
1063 GMI_Reservation_Util.PrintLn('(opm_dbg) Entering Lock_Row : line_id='||p_mo_line_rec.line_id);
1064
1065 OPEN c_lock_mol(p_mo_line_rec.line_id);
1066 GMI_Reservation_Util.PrintLn('(opm_dbg) Entering Lock_Row :');
1067 FETCH c_lock_mol
1068 INTO l_mo_line_rec.attribute1
1069 , l_mo_line_rec.attribute10
1070 , l_mo_line_rec.attribute11
1071 , l_mo_line_rec.attribute12
1072 , l_mo_line_rec.attribute13
1073 , l_mo_line_rec.attribute14
1074 , l_mo_line_rec.attribute15
1075 , l_mo_line_rec.attribute2
1076 , l_mo_line_rec.attribute3
1077 , l_mo_line_rec.attribute4
1078 , l_mo_line_rec.attribute5
1079 , l_mo_line_rec.attribute6
1080 , l_mo_line_rec.attribute7
1081 , l_mo_line_rec.attribute8
1082 , l_mo_line_rec.attribute9
1083 , l_mo_line_rec.attribute_category
1084 , l_mo_line_rec.created_by
1085 , l_mo_line_rec.creation_date
1086 , l_mo_line_rec.date_required
1087 , l_mo_line_rec.from_locator_id
1088 , l_mo_line_rec.from_subinventory_code
1089 , l_mo_line_rec.from_subinventory_id
1090 , l_mo_line_rec.header_id
1091 , l_mo_line_rec.inventory_item_id
1092 , l_mo_line_rec.last_updated_by
1093 , l_mo_line_rec.last_update_date
1094 , l_mo_line_rec.last_update_login
1095 , l_mo_line_rec.line_id
1096 , l_mo_line_rec.line_number
1097 , l_mo_line_rec.line_status
1098 /* , l_mo_line_rec.lot_number */
1099 , l_mo_line_rec.organization_id
1100 , l_mo_line_rec.program_application_id
1101 , l_mo_line_rec.program_id
1102 , l_mo_line_rec.program_update_date
1103 /* , l_mo_line_rec.project_id */
1104 , l_mo_line_rec.quantity
1105 , l_mo_line_rec.quantity_delivered
1106 , l_mo_line_rec.quantity_detailed
1107 , l_mo_line_rec.reason_id
1108 , l_mo_line_rec.reference
1109 , l_mo_line_rec.reference_id
1110 , l_mo_line_rec.reference_type_code
1111 , l_mo_line_rec.request_id
1112 /* , l_mo_line_rec.revision */
1113 /* , l_mo_line_rec.serial_number_end */
1114 /* , l_mo_line_rec.serial_number_start */
1115 , l_mo_line_rec.status_date
1116 /* , l_mo_line_rec.task_id */
1117 , l_mo_line_rec.to_account_id
1118 , l_mo_line_rec.to_locator_id
1119 , l_mo_line_rec.to_subinventory_code
1120 , l_mo_line_rec.to_subinventory_id
1121 , l_mo_line_rec.transaction_header_id
1122 , l_mo_line_rec.uom_code
1123 , l_mo_line_rec.transaction_type_id
1124 , l_mo_line_rec.transaction_source_type_id
1125 , l_mo_line_rec.txn_source_id
1126 , l_mo_line_rec.txn_source_line_id
1127 , l_mo_line_rec.txn_source_line_detail_id
1128 , l_mo_line_rec.to_organization_id
1129 , l_mo_line_rec.primary_quantity
1130 , l_mo_line_rec.pick_strategy_id
1131 , l_mo_line_rec.put_away_strategy_id
1132 , l_mo_line_rec.lot_no
1133 , l_mo_line_rec.sublot_no
1134 , l_mo_line_rec.qc_grade
1135 , l_mo_line_rec.secondary_quantity
1136 , l_mo_line_rec.secondary_uom_code
1137 , l_mo_line_rec.secondary_quantity_delivered
1138 , l_mo_line_rec.secondary_quantity_detailed
1139 -- HW BUG#:2296620 added ship_set_id
1140 , l_mo_line_rec.ship_set_id;
1141
1142 GMI_Reservation_Util.PrintLn('(opm_dbg) in Lock_Row : after select.');
1143 /* Row locked. Compare IN attributes to DB attributes. */
1144 IF ( c_lock_mol%NOTFOUND
1145 OR SQLCODE = -54 )
1146 THEN
1147 CLOSE c_lock_mol;
1148 GMI_Reservation_Util.PrintLn('(opm_dbg) Lock_Row : the MO_line is locked for line_id='||p_mo_line_rec.line_id||', SQL_CODE='||SQLCODE);
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151 CLOSE c_lock_mol;
1152
1153 x_mo_line_rec := l_mo_line_rec;
1154
1155 /* Set return status */
1156
1157 x_return_status := FND_API.G_RET_STS_SUCCESS;
1158 x_mo_line_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1159
1160 EXCEPTION
1161
1162 WHEN NO_DATA_FOUND THEN
1163 GMI_Reservation_Util.PrintLn('(opm_dbg) excp Lock_Row : the MO_line is NOTFOUND line_id='||p_mo_line_rec.line_id||', SQL_CODE='||SQLCODE);
1164
1165 x_return_status := FND_API.G_RET_STS_ERROR;
1166 x_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1167
1168 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1169 THEN
1170
1171 FND_MESSAGE.SET_NAME('INV','OE_LOCK_ROW_DELETED');
1172 FND_MSG_PUB.Add;
1173
1174 END IF;
1175 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1176
1177 GMI_Reservation_Util.PrintLn('(opm_dbg) excp Lock_Row : the MO_line is locked for line_id='||p_mo_line_rec.line_id||', SQL_CODE='||SQLCODE);
1178 x_return_status := '54';
1179 x_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1180
1181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1182 THEN
1183
1184 FND_MESSAGE.SET_NAME('INV','OE_LOCK_ROW_ALREADY_LOCKED');
1185 FND_MSG_PUB.Add;
1186
1187 END IF;
1188 WHEN OTHERS THEN
1189 GMI_Reservation_Util.PrintLn('(opm_dbg) excp Lock_Row : the MO_line is OTHERS line_id='||p_mo_line_rec.line_id||', SQL_CODE='||SQLCODE);
1190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1191
1192 x_mo_line_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193
1194 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1195 THEN
1196 FND_MSG_PUB.Add_Exc_Msg
1197 ( G_PKG_NAME
1198 , 'Lock_Row'
1199 );
1200 END IF;
1201
1202 END Lock_Row;
1203
1204 /* Function Get_Values */
1205 /*
1206
1207 FUNCTION Get_Values
1208 ( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
1209 , p_old_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec :=
1210 INV_Move_Order_PUB.G_MISS_mo_line_rec
1211 ) RETURN INV_Move_Order_PUB.Trolin_Val_Rec_Type
1212 IS
1213 l_trolin_val_rec INV_Move_Order_PUB.Trolin_Val_Rec_Type;
1214 BEGIN
1215
1216 IF p_mo_line_rec.from_locator_id IS NOT NULL AND
1217 p_mo_line_rec.from_locator_id <> FND_API.G_MISS_NUM AND
1218 NOT INV_GLOBALS.Equal(p_mo_line_rec.from_locator_id,
1219 p_old_mo_line_rec.from_locator_id)
1220 THEN
1221 l_trolin_val_rec.from_locator := INV_Id_To_Value.From_Locator
1222 ( p_from_locator_id => p_mo_line_rec.from_locator_id
1223 );
1224 END IF;
1225
1226 -- IF p_mo_line_rec.from_subinventory_code IS NOT NULL AND
1227 -- p_mo_line_rec.from_subinventory_code <> FND_API.G_MISS_CHAR AND
1228 -- NOT INV_GLOBALS.Equal(p_mo_line_rec.from_subinventory_code,
1229 -- p_old_mo_line_rec.from_subinventory_code)
1230 -- THEN
1231 -- l_trolin_val_rec.from_subinventory := INV_Id_To_Value.From_Subinventory
1232 -- ( p_from_subinventory_code => p_mo_line_rec.from_subinventory_code
1233 -- );
1234 -- END IF; -- Generated
1235 -- Line 2167
1236 IF p_mo_line_rec.from_subinventory_id IS NOT NULL AND
1237 p_mo_line_rec.from_subinventory_id <> FND_API.G_MISS_NUM AND
1238 NOT INV_GLOBALS.Equal(p_mo_line_rec.from_subinventory_id,
1239 p_old_mo_line_rec.from_subinventory_id)
1240 THEN
1241 l_trolin_val_rec.from_subinventory := INV_Id_To_Value.From_Subinventory
1242 ( p_from_subinventory_id => p_mo_line_rec.from_subinventory_id
1243 );
1244 -- ( p_from_subinventory_id => p_mo_line_rec.from_subinventory_id
1245 -- );
1246 END IF;
1247
1248 IF p_mo_line_rec.header_id IS NOT NULL AND
1249 p_mo_line_rec.header_id <> FND_API.G_MISS_NUM AND
1250 NOT INV_GLOBALS.Equal(p_mo_line_rec.header_id,
1251 p_old_mo_line_rec.header_id)
1252 THEN
1253 l_trolin_val_rec.header := INV_Id_To_Value.Header
1254 ( p_header_id => p_mo_line_rec.header_id
1255 );
1256 END IF;
1257
1258 IF p_mo_line_rec.inventory_item_id IS NOT NULL AND
1259 p_mo_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1260 NOT INV_GLOBALS.Equal(p_mo_line_rec.inventory_item_id,
1261 p_old_mo_line_rec.inventory_item_id)
1262 THEN
1263 l_trolin_val_rec.inventory_item := INV_Id_To_Value.Inventory_Item
1264 ( p_inventory_item_id => p_mo_line_rec.inventory_item_id
1265 );
1266 END IF;
1267
1268 IF p_mo_line_rec.line_id IS NOT NULL AND
1269 p_mo_line_rec.line_id <> FND_API.G_MISS_NUM AND
1270 NOT INV_GLOBALS.Equal(p_mo_line_rec.line_id,
1271 p_old_mo_line_rec.line_id)
1272 THEN
1273 l_trolin_val_rec.line := INV_Id_To_Value.Line
1274 ( p_line_id => p_mo_line_rec.line_id
1275 );
1276 END IF;
1277
1278 IF p_mo_line_rec.organization_id IS NOT NULL AND
1279 p_mo_line_rec.organization_id <> FND_API.G_MISS_NUM AND
1280 NOT INV_GLOBALS.Equal(p_mo_line_rec.organization_id,
1281 p_old_mo_line_rec.organization_id)
1282 THEN
1283 l_trolin_val_rec.organization := INV_Id_To_Value.Organization
1284 ( p_organization_id => p_mo_line_rec.organization_id
1285 );
1286 END IF;
1287
1288 IF p_mo_line_rec.to_organization_id IS NOT NULL AND
1289 p_mo_line_rec.to_organization_id <> FND_API.G_MISS_NUM AND
1290 NOT INV_GLOBALS.Equal(p_mo_line_rec.to_organization_id,
1291 p_old_mo_line_rec.to_organization_id)
1292 THEN
1293 l_trolin_val_rec.to_organization := INV_Id_To_Value.To_Organization
1294 ( p_to_organization_id => p_mo_line_rec.to_organization_id
1295 );
1296 END IF;
1297
1298 IF p_mo_line_rec.project_id IS NOT NULL AND
1299 p_mo_line_rec.project_id <> FND_API.G_MISS_NUM AND
1300 NOT INV_GLOBALS.Equal(p_mo_line_rec.project_id,
1301 p_old_mo_line_rec.project_id)
1302 THEN
1303 l_trolin_val_rec.project := INV_Id_To_Value.Project
1304 ( p_project_id => p_mo_line_rec.project_id
1305 );
1306 END IF;
1307
1308 IF p_mo_line_rec.reason_id IS NOT NULL AND
1309 p_mo_line_rec.reason_id <> FND_API.G_MISS_NUM AND
1310 NOT INV_GLOBALS.Equal(p_mo_line_rec.reason_id,
1311 p_old_mo_line_rec.reason_id)
1312 THEN
1313 l_trolin_val_rec.reason := INV_Id_To_Value.Reason
1314 ( p_reason_id => p_mo_line_rec.reason_id
1315 );
1316 END IF;
1317
1318 IF p_mo_line_rec.reference_id IS NOT NULL AND
1319 p_mo_line_rec.reference_id <> FND_API.G_MISS_NUM AND
1320 NOT INV_GLOBALS.Equal(p_mo_line_rec.reference_id,
1321 p_old_mo_line_rec.reference_id)
1322 THEN
1323 l_trolin_val_rec.reference := INV_Id_To_Value.Reference
1324 ( p_reference_id => p_mo_line_rec.reference_id
1325 );
1326 END IF;
1327
1328 IF p_mo_line_rec.reference_type_code IS NOT NULL AND
1329 p_mo_line_rec.reference_type_code <> FND_API.G_MISS_NUM AND
1330 NOT INV_GLOBALS.Equal(p_mo_line_rec.reference_type_code,
1331 p_old_mo_line_rec.reference_type_code)
1332 THEN
1333 l_trolin_val_rec.reference_type := INV_Id_To_Value.Reference_Type
1334 ( p_reference_type_code => p_mo_line_rec.reference_type_code
1335 );
1336 END IF;
1337
1338 IF p_mo_line_rec.task_id IS NOT NULL AND
1339 p_mo_line_rec.task_id <> FND_API.G_MISS_NUM AND
1340 NOT INV_GLOBALS.Equal(p_mo_line_rec.task_id,
1341 p_old_mo_line_rec.task_id)
1342 THEN
1343 l_trolin_val_rec.task := INV_Id_To_Value.Task
1344 ( p_task_id => p_mo_line_rec.task_id
1345 );
1346 END IF;
1347
1348 IF p_mo_line_rec.to_account_id IS NOT NULL AND
1349 p_mo_line_rec.to_account_id <> FND_API.G_MISS_NUM AND
1350 NOT INV_GLOBALS.Equal(p_mo_line_rec.to_account_id,
1351 p_old_mo_line_rec.to_account_id)
1352 THEN
1353 l_trolin_val_rec.to_account := INV_Id_To_Value.To_Account
1354 ( p_to_account_id => p_mo_line_rec.to_account_id
1355 );
1356 END IF;
1357
1358 IF p_mo_line_rec.to_locator_id IS NOT NULL AND
1359 p_mo_line_rec.to_locator_id <> FND_API.G_MISS_NUM AND
1360 NOT INV_GLOBALS.Equal(p_mo_line_rec.to_locator_id,
1361 p_old_mo_line_rec.to_locator_id)
1362 THEN
1363 l_trolin_val_rec.to_locator := INV_Id_To_Value.To_Locator
1364 ( p_to_locator_id => p_mo_line_rec.to_locator_id
1365 );
1366 END IF;
1367
1368 -- IF p_mo_line_rec.to_subinventory_code IS NOT NULL AND
1369 -- p_mo_line_rec.to_subinventory_code <> FND_API.G_MISS_CHAR AND
1370 -- NOT INV_GLOBALS.Equal(p_mo_line_rec.to_subinventory_code,
1371 -- p_old_mo_line_rec.to_subinventory_code)
1372 -- THEN
1373 -- l_trolin_val_rec.to_subinventory := INV_Id_To_Value.To_Subinventory
1374 -- ( p_to_subinventory_code => p_mo_line_rec.to_subinventory_code
1375 -- );
1376 -- END IF; -- Generated
1377
1378 IF p_mo_line_rec.to_subinventory_id IS NOT NULL AND
1379 p_mo_line_rec.to_subinventory_id <> FND_API.G_MISS_NUM AND
1380 NOT INV_GLOBALS.Equal(p_mo_line_rec.to_subinventory_id,
1381 p_old_mo_line_rec.to_subinventory_id)
1382 THEN
1383 l_trolin_val_rec.to_subinventory := INV_Id_To_Value.To_Subinventory
1384 ( p_to_subinventory_id => p_mo_line_rec.to_subinventory_id
1385 );
1386 END IF;
1387
1388 IF p_mo_line_rec.transaction_header_id IS NOT NULL AND
1389 p_mo_line_rec.transaction_header_id <> FND_API.G_MISS_NUM AND
1390 NOT INV_GLOBALS.Equal(p_mo_line_rec.transaction_header_id,
1391 p_old_mo_line_rec.transaction_header_id)
1392 THEN
1393 l_trolin_val_rec.transaction_header := INV_Id_To_Value.Transaction_Header
1394 ( p_transaction_header_id => p_mo_line_rec.transaction_header_id
1395 );
1396 END IF;
1397
1398 IF p_mo_line_rec.transaction_type_id IS NOT NULL AND
1399 p_mo_line_rec.transaction_type_id <> FND_API.G_MISS_NUM AND
1400 NOT INV_GLOBALS.Equal(p_mo_line_rec.transaction_type_id,
1401 p_old_mo_line_rec.transaction_type_id)
1402 THEN
1403 l_trolin_val_rec.transaction_type := INV_Id_To_Value.Transaction_type
1404 ( p_transaction_type_id => p_mo_line_rec.transaction_type_id
1405 );
1406 END IF;
1407
1408 IF p_mo_line_rec.uom_code IS NOT NULL AND
1409 p_mo_line_rec.uom_code <> FND_API.G_MISS_CHAR AND
1410 NOT INV_GLOBALS.Equal(p_mo_line_rec.uom_code,
1411 p_old_mo_line_rec.uom_code)
1412 THEN
1413 l_trolin_val_rec.uom := INV_Id_To_Value.Uom
1414 ( p_uom_code => p_mo_line_rec.uom_code
1415 );
1416 END IF;
1417
1418 RETURN l_trolin_val_rec;
1419
1420 END Get_Values;
1421
1422 -- Function Get_Ids
1423
1424 FUNCTION Get_Ids
1425 ( p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
1426 , p_trolin_val_rec IN INV_Move_Order_PUB.Trolin_Val_Rec_Type
1427 ) RETURN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
1428 IS
1429 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
1430 BEGIN
1431
1432 -- initialize return_status.
1433
1434 l_mo_line_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1435
1436 -- initialize l_mo_line_rec.
1437
1438 l_mo_line_rec := p_mo_line_rec;
1439
1440 IF p_trolin_val_rec.from_locator <> FND_API.G_MISS_CHAR
1441 THEN
1442
1443 IF p_mo_line_rec.from_locator_id <> FND_API.G_MISS_NUM THEN
1444
1445 l_mo_line_rec.from_locator_id := p_mo_line_rec.from_locator_id;
1446
1447 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1448 THEN
1449
1450 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1451 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','from_locator');
1452 FND_MSG_PUB.Add;
1453
1454 END IF;
1455
1456 ELSE
1457
1458 l_mo_line_rec.from_locator_id := INV_Value_To_Id.from_locator
1459 ( p_organizatoin_id => p_mo_line_rec.organization_id,
1460 p_from_locator => p_trolin_val_rec.from_locator
1461 );
1462
1463 IF l_mo_line_rec.from_locator_id = FND_API.G_MISS_NUM THEN
1464 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1465 END IF;
1466
1467 END IF;
1468
1469 END IF;
1470
1471 IF p_trolin_val_rec.from_subinventory <> FND_API.G_MISS_CHAR
1472 THEN
1473
1474 IF p_mo_line_rec.from_subinventory_id <> FND_API.G_MISS_NUM THEN
1475
1476 l_mo_line_rec.from_subinventory_id := p_mo_line_rec.from_subinventory_id;
1477
1478 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1479 THEN
1480
1481 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1482 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','from_subinventory');
1483 FND_MSG_PUB.Add;
1484
1485 END IF;
1486
1487 ELSE
1488
1489 l_mo_line_rec.from_subinventory_id := INV_Value_To_Id.from_subinventory
1490 ( p_organization_id => p_mo_line_rec.organization_id,
1491 p_from_subinventory => p_trolin_val_rec.from_subinventory
1492 );
1493
1494 IF l_mo_line_rec.from_subinventory_id = FND_API.G_MISS_NUM THEN
1495 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1496 END IF;
1497
1498 END IF;
1499
1500 END IF;
1501
1502 IF p_trolin_val_rec.header <> FND_API.G_MISS_CHAR
1503 THEN
1504
1505 IF p_mo_line_rec.header_id <> FND_API.G_MISS_NUM THEN
1506
1507 l_mo_line_rec.header_id := p_mo_line_rec.header_id;
1508
1509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1510 THEN
1511
1512 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1513 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','header');
1514 FND_MSG_PUB.Add;
1515
1516 END IF;
1517
1518 ELSE
1519
1520 l_mo_line_rec.header_id := INV_Value_To_Id.header
1521 ( p_header => p_trolin_val_rec.header
1522 );
1523
1524 IF l_mo_line_rec.header_id = FND_API.G_MISS_NUM THEN
1525 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1526 END IF;
1527
1528 END IF;
1529
1530 END IF;
1531
1532 IF p_trolin_val_rec.inventory_item <> FND_API.G_MISS_CHAR
1533 THEN
1534
1535 IF p_mo_line_rec.inventory_item_id <> FND_API.G_MISS_NUM THEN
1536
1537 l_mo_line_rec.inventory_item_id := p_mo_line_rec.inventory_item_id;
1538
1539 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1540 THEN
1541
1542 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1543 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_item');
1544 FND_MSG_PUB.Add;
1545
1546 END IF;
1547
1548 ELSE
1549
1550 l_mo_line_rec.inventory_item_id := INV_Value_To_Id.inventory_item
1551 ( p_organization_id => p_mo_line_rec.organization_id,
1552 p_inventory_item => p_trolin_val_rec.inventory_item
1553 );
1554
1555 IF l_mo_line_rec.inventory_item_id = FND_API.G_MISS_NUM THEN
1556 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1557 END IF;
1558
1559 END IF;
1560
1561 END IF;
1562
1563 IF p_trolin_val_rec.line <> FND_API.G_MISS_CHAR
1564 THEN
1565
1566 IF p_mo_line_rec.line_id <> FND_API.G_MISS_NUM THEN
1567
1568 l_mo_line_rec.line_id := p_mo_line_rec.line_id;
1569
1570 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1571 THEN
1572
1573 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1574 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line');
1575 FND_MSG_PUB.Add;
1576
1577 END IF;
1578
1579 ELSE
1580
1581 l_mo_line_rec.line_id := INV_Value_To_Id.line
1582 ( p_line => p_trolin_val_rec.line
1583 );
1584
1585 IF l_mo_line_rec.line_id = FND_API.G_MISS_NUM THEN
1586 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1587 END IF;
1588
1589 END IF;
1590
1591 END IF;
1592
1593 IF p_trolin_val_rec.organization <> FND_API.G_MISS_CHAR
1594 THEN
1595
1596 IF p_mo_line_rec.organization_id <> FND_API.G_MISS_NUM THEN
1597
1598 l_mo_line_rec.organization_id := p_mo_line_rec.organization_id;
1599
1600 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1601 THEN
1602
1603 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1604 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','organization');
1605 FND_MSG_PUB.Add;
1606
1607 END IF;
1608
1609 ELSE
1610
1611 l_mo_line_rec.organization_id := INV_Value_To_Id.organization
1612 ( p_organization => p_trolin_val_rec.organization
1613 );
1614
1615 IF l_mo_line_rec.organization_id = FND_API.G_MISS_NUM THEN
1616 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1617 END IF;
1618
1619 END IF;
1620
1621 END IF;
1622
1623 IF p_trolin_val_rec.to_organization <> FND_API.G_MISS_CHAR
1624 THEN
1625
1626 IF p_mo_line_rec.to_organization_id <> FND_API.G_MISS_NUM THEN
1627
1628 l_mo_line_rec.to_organization_id := p_mo_line_rec.to_organization_id;
1629
1630 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1631 THEN
1632
1633 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1634 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_organization');
1635 FND_MSG_PUB.Add;
1636
1637 END IF;
1638
1639 ELSE
1640
1641 l_mo_line_rec.to_organization_id := INV_Value_To_Id.to_organization
1642 ( p_to_organization => p_trolin_val_rec.to_organization
1643 );
1644
1645 IF l_mo_line_rec.to_organization_id = FND_API.G_MISS_NUM THEN
1646 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1647 END IF;
1648
1649 END IF;
1650
1651 END IF;
1652 IF p_trolin_val_rec.project <> FND_API.G_MISS_CHAR
1653 THEN
1654
1655 IF p_mo_line_rec.project_id <> FND_API.G_MISS_NUM THEN
1656
1657 l_mo_line_rec.project_id := p_mo_line_rec.project_id;
1658
1659 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1660 THEN
1661
1662 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1663 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','project');
1664 FND_MSG_PUB.Add;
1665
1666 END IF;
1667
1668 ELSE
1669
1670 l_mo_line_rec.project_id := INV_Value_To_Id.project
1671 ( p_project => p_trolin_val_rec.project
1672 );
1673
1674 IF l_mo_line_rec.project_id = FND_API.G_MISS_NUM THEN
1675 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1676 END IF;
1677
1678 END IF;
1679
1680 END IF;
1681
1682 IF p_trolin_val_rec.reason <> FND_API.G_MISS_CHAR
1683 THEN
1684
1685 IF p_mo_line_rec.reason_id <> FND_API.G_MISS_NUM THEN
1686
1687 l_mo_line_rec.reason_id := p_mo_line_rec.reason_id;
1688
1689 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1690 THEN
1691
1692 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1693 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reason');
1694 FND_MSG_PUB.Add;
1695
1696 END IF;
1697
1698 ELSE
1699
1700 l_mo_line_rec.reason_id := INV_Value_To_Id.reason
1701 ( p_reason => p_trolin_val_rec.reason
1702 );
1703
1704 IF l_mo_line_rec.reason_id = FND_API.G_MISS_NUM THEN
1705 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1706 END IF;
1707
1708 END IF;
1709
1710 END IF;
1711
1712 IF p_trolin_val_rec.reference <> FND_API.G_MISS_CHAR
1713 THEN
1714
1715 IF p_mo_line_rec.reference_id <> FND_API.G_MISS_NUM THEN
1716
1717 l_mo_line_rec.reference_id := p_mo_line_rec.reference_id;
1718
1719 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1720 THEN
1721
1722 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1723 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reference');
1724 FND_MSG_PUB.Add;
1725
1726 END IF;
1727
1728 ELSE
1729
1730 l_mo_line_rec.reference_id := INV_Value_To_Id.reference
1731 ( p_reference => p_trolin_val_rec.reference
1732 );
1733
1734 IF l_mo_line_rec.reference_id = FND_API.G_MISS_NUM THEN
1735 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1736 END IF;
1737
1738 END IF;
1739
1740 END IF;
1741
1742 IF p_trolin_val_rec.reference_type <> FND_API.G_MISS_CHAR
1743 THEN
1744
1745 IF p_mo_line_rec.reference_type_code <> FND_API.G_MISS_NUM THEN
1746
1747 l_mo_line_rec.reference_type_code := p_mo_line_rec.reference_type_code;
1748
1749 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1750 THEN
1751
1752 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1753 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reference_type');
1754 FND_MSG_PUB.Add;
1755
1756 END IF;
1757
1758 ELSE
1759
1760 l_mo_line_rec.reference_type_code := INV_Value_To_Id.reference_type
1761 ( p_reference_type => p_trolin_val_rec.reference_type
1762 );
1763
1764 IF l_mo_line_rec.reference_type_code = FND_API.G_MISS_NUM THEN
1765 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1766 END IF;
1767
1768 END IF;
1769
1770 END IF;
1771
1772 IF p_trolin_val_rec.task <> FND_API.G_MISS_CHAR
1773 THEN
1774
1775 IF p_mo_line_rec.task_id <> FND_API.G_MISS_NUM THEN
1776
1777 l_mo_line_rec.task_id := p_mo_line_rec.task_id;
1778
1779 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1780 THEN
1781
1782 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1783 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','task');
1784 FND_MSG_PUB.Add;
1785
1786 END IF;
1787
1788 ELSE
1789
1790 l_mo_line_rec.task_id := INV_Value_To_Id.task
1791 ( p_task => p_trolin_val_rec.task
1792 );
1793
1794 IF l_mo_line_rec.task_id = FND_API.G_MISS_NUM THEN
1795 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1796 END IF;
1797
1798 END IF;
1799
1800 END IF;
1801
1802 IF p_trolin_val_rec.to_account <> FND_API.G_MISS_CHAR
1803 THEN
1804
1805 IF p_mo_line_rec.to_account_id <> FND_API.G_MISS_NUM THEN
1806
1807 l_mo_line_rec.to_account_id := p_mo_line_rec.to_account_id;
1808
1809 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1810 THEN
1811
1812 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1813 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_account');
1814 FND_MSG_PUB.Add;
1815
1816 END IF;
1817
1818 ELSE
1819
1820 l_mo_line_rec.to_account_id := INV_Value_To_Id.to_account
1821 ( p_organization_id => p_mo_line_rec.organization_id,
1822 p_to_account => p_trolin_val_rec.to_account
1823 );
1824
1825 IF l_mo_line_rec.to_account_id = FND_API.G_MISS_NUM THEN
1826 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1827 END IF;
1828
1829 END IF;
1830
1831 END IF;
1832
1833 IF p_trolin_val_rec.to_locator <> FND_API.G_MISS_CHAR
1834 THEN
1835
1836 IF p_mo_line_rec.to_locator_id <> FND_API.G_MISS_NUM THEN
1837
1838 l_mo_line_rec.to_locator_id := p_mo_line_rec.to_locator_id;
1839
1840 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1841 THEN
1842
1843 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1844 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_locator');
1845 FND_MSG_PUB.Add;
1846
1847 END IF;
1848
1849 ELSE
1850
1851 l_mo_line_rec.to_locator_id := INV_Value_To_Id.to_locator
1852 ( p_to_locator => p_trolin_val_rec.to_locator
1853 );
1854
1855 IF l_mo_line_rec.to_locator_id = FND_API.G_MISS_NUM THEN
1856 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1857 END IF;
1858
1859 END IF;
1860
1861 END IF;
1862
1863 IF p_trolin_val_rec.to_subinventory <> FND_API.G_MISS_CHAR
1864 THEN
1865
1866 IF p_mo_line_rec.to_subinventory_code <> FND_API.G_MISS_CHAR THEN
1867
1868 l_mo_line_rec.to_subinventory_code := p_mo_line_rec.to_subinventory_code;
1869
1870 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1871 THEN
1872
1873 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1874 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_subinventory');
1875 FND_MSG_PUB.Add;
1876
1877 END IF;
1878
1879 ELSE
1880
1881 l_mo_line_rec.to_subinventory_code := INV_Value_To_Id.to_subinventory
1882 ( p_organization_id => p_mo_line_rec.organization_id,
1883 p_to_subinventory => p_trolin_val_rec.to_subinventory
1884 );
1885
1886 IF l_mo_line_rec.to_subinventory_code = FND_API.G_MISS_CHAR THEN
1887 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1888 END IF;
1889
1890 END IF;
1891
1892 END IF;
1893 -- Line2839
1894 IF p_trolin_val_rec.to_subinventory <> FND_API.G_MISS_CHAR
1895 THEN
1896
1897 IF p_mo_line_rec.to_subinventory_id <> FND_API.G_MISS_NUM THEN
1898
1899 l_mo_line_rec.to_subinventory_id := p_mo_line_rec.to_subinventory_id;
1900
1901 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1902 THEN
1903
1904 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1905 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','to_subinventory');
1906 FND_MSG_PUB.Add;
1907
1908 END IF;
1909
1910 ELSE
1911
1912 l_mo_line_rec.to_subinventory_id := INV_Value_To_Id.to_subinventory
1913 ( p_organization_id => p_mo_line_rec.organization_id,
1914 p_to_subinventory => p_trolin_val_rec.to_subinventory
1915 );
1916
1917 IF l_mo_line_rec.to_subinventory_id = FND_API.G_MISS_NUM THEN
1918 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1919 END IF;
1920
1921 END IF;
1922
1923 END IF;
1924
1925 IF p_trolin_val_rec.transaction_header <> FND_API.G_MISS_CHAR
1926 THEN
1927
1928 IF p_mo_line_rec.transaction_header_id <> FND_API.G_MISS_NUM THEN
1929
1930 l_mo_line_rec.transaction_header_id := p_mo_line_rec.transaction_header_id;
1931
1932 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1933 THEN
1934
1935 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1936 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_header');
1937 FND_MSG_PUB.Add;
1938
1939 END IF;
1940
1941 ELSE
1942
1943 l_mo_line_rec.transaction_header_id := INV_Value_To_Id.transaction_header
1944 ( p_transaction_header => p_trolin_val_rec.transaction_header
1945 );
1946
1947 IF l_mo_line_rec.transaction_header_id = FND_API.G_MISS_NUM THEN
1948 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1949 END IF;
1950
1951 END IF;
1952
1953 END IF;
1954 IF p_trolin_val_rec.transaction_type <> FND_API.G_MISS_CHAR
1955 THEN
1956
1957 IF p_mo_line_rec.transaction_type_id <> FND_API.G_MISS_NUM THEN
1958
1959 l_mo_line_rec.transaction_type_id := p_mo_line_rec.transaction_type_id;
1960
1961 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1962 THEN
1963
1964 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1965 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_type');
1966 FND_MSG_PUB.Add;
1967
1968 END IF;
1969
1970 ELSE
1971
1972 l_mo_line_rec.transaction_type_id := INV_Value_To_Id.transaction_type
1973 ( p_transaction_type => p_trolin_val_rec.transaction_type
1974 );
1975
1976 IF l_mo_line_rec.transaction_type_id = FND_API.G_MISS_NUM THEN
1977 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
1978 END IF;
1979
1980 END IF;
1981
1982 END IF;
1983 -- Line2899
1984 IF p_trolin_val_rec.uom <> FND_API.G_MISS_CHAR
1985 THEN
1986
1987 IF p_mo_line_rec.uom_code <> FND_API.G_MISS_CHAR THEN
1988
1989 l_mo_line_rec.uom_code := p_mo_line_rec.uom_code;
1990
1991 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1992 THEN
1993
1994 FND_MESSAGE.SET_NAME('INV','FND_BOTH_VAL_AND_ID_EXIST');
1995 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','uom');
1996 FND_MSG_PUB.Add;
1997
1998 END IF;
1999
2000 ELSE
2001 -- Line2917
2002 l_mo_line_rec.uom_code := INV_Value_To_Id.uom
2003 ( p_uom => p_trolin_val_rec.uom
2004 );
2005
2006 IF l_mo_line_rec.uom_code = FND_API.G_MISS_CHAR THEN
2007 l_mo_line_rec.return_status := FND_API.G_RET_STS_ERROR;
2008 END IF;
2009
2010 END IF;
2011
2012 END IF;
2013
2014 RETURN l_mo_line_rec;
2015
2016 END Get_Ids;
2017
2018 */
2019
2020 PROCEDURE Line_Auto_Detail
2021 ( p_mo_line_id IN NUMBER
2022 , p_init_msg_list IN NUMBER
2023 , p_transaction_header_id IN NUMBER
2024 , p_transaction_mode IN NUMBER
2025 , p_move_order_type IN NUMBER
2026 , p_allow_delete IN VARCHAR2 DEFAULT NULL
2027 , x_number_of_rows OUT NOCOPY NUMBER
2028 , x_qc_grade OUT NOCOPY VARCHAR2
2029 , x_detailed_qty OUT NOCOPY NUMBER
2030 , x_qty_UM OUT NOCOPY VARCHAR2
2031 , x_detailed_qty2 OUT NOCOPY NUMBER
2032 , x_qty_UM2 OUT NOCOPY VARCHAR2
2033 , x_return_status OUT NOCOPY VARCHAR2
2034 , x_msg_count OUT NOCOPY NUMBER
2035 , x_msg_data OUT NOCOPY VARCHAR2
2036 )
2037 IS
2038 l_api_version_number CONSTANT NUMBER := 1.0;
2039 l_init_msg_list VARCHAR2(255) := FND_API.G_TRUE;
2040 l_api_name CONSTANT VARCHAR2(30) := 'Line_Auto_Detail';
2041 l_detailed_qty NUMBER := 0;
2042 l_ser_index NUMBER;
2043 l_expiration_date DATE;
2044 x_success NUMBER;
2045 l_transfer_to_location NUMBER;
2046 l_lot_number NUMBER;
2047 l_locator_id NUMBER;
2048 l_transaction_temp_id NUMBER;
2049 l_transaction_header_id NUMBER;
2050 l_subinventory_code VARCHAR2(30);
2051 l_transaction_quantity NUMBER;
2052 l_primary_quantity NUMBER;
2053 l_inventory_item_id NUMBER;
2054 l_temp_id NUMBER;
2055 l_serial_number VARCHAR2(30);
2056
2057 l_mtl_reservation INV_RESERVATION_GLOBAL.MTL_RESERVATION_TBL_TYPE;
2058 l_mo_hdr_rec GMI_Move_Order_Global.mo_hdr_rec;
2059 l_mo_line_tbl GMI_Move_Order_Global.mo_line_tbl;
2060 l_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
2061 ll_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
2062 l_default_lot_index NUMBER;
2063
2064 l_pick_release_status INV_PICK_RELEASE_PUB.INV_RELEASE_STATUS_Tbl_Type;
2065 l_return_status VARCHAR2(1);
2066 l_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
2067 l_mold_tbl_temp INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
2068 l_message VARCHAR2(2000);
2069 l_count NUMBER;
2070 l_from_serial_number VARCHAR2(30);
2071 l_to_serial_number VARCHAR2(30);
2072 l_detail_rec_count NUMBER;
2073 l_success NUMBER;
2074 l_auto_pick_flag VARCHAR2(1);
2075 l_request_number VARCHAR2(80);
2076 l_commit VARCHAR2(1);
2077 l_p_allow_delete VARCHAR2(3);
2078
2079 /* Default Rules : */
2080 l_ps_mode VARCHAR2(1);
2081 l_default_autodetail VARCHAR2(1);
2082 l_default_autocreate_del VARCHAR2(1);
2083 l_use_header_flag VARCHAR2(1);
2084 l_default_to_sub VARCHAR2(10);
2085 l_default_to_loc NUMBER;
2086 l_pick_seq_rule_id NUMBER;
2087 l_pick_grouping_rule_id NUMBER;
2088 l_default_pickconfirm VARCHAR2(1);
2089
2090 CURSOR get_default_params(v_org_id IN NUMBER) IS
2091 SELECT NVL(PRINT_PICK_SLIP_MODE, 'E'),
2092 AUTODETAIL_PR_FLAG,
2093 NVL(AUTOCREATE_DELIVERIES_FLAG, 'N'),
2094 NVL(AUTOCREATE_DEL_ORDERS_FLAG, 'Y'),
2095 DEFAULT_STAGE_SUBINVENTORY,
2096 DEFAULT_STAGE_LOCATOR_ID,
2097 PICK_SEQUENCE_RULE_ID,
2098 PICK_GROUPING_RULE_ID
2099 FROM WSH_SHIPPING_PARAMETERS
2100 WHERE ORGANIZATION_ID = v_org_id;
2101
2102 CURSOR get_default_confirm(v_org_id IN NUMBER) IS
2103 SELECT DECODE(MO_PICK_CONFIRM_REQUIRED, 2, 'Y', 'N')
2104 FROM MTL_PARAMETERS
2105 WHERE ORGANIZATION_ID = v_org_id;
2106
2107
2108 BEGIN
2109
2110 GMI_Reservation_Util.PrintLn('(opm_dbg) Entering Line_Auto_Detail.');
2111
2112 /* Init status : */
2113 x_return_status := FND_API.G_RET_STS_SUCCESS;
2114
2115
2116 IF (p_init_msg_list = 1)
2117 THEN
2118 FND_MSG_PUB.Initialize;
2119 END IF;
2120
2121 GMI_Reservation_Util.PrintLn('(opm_dbg) Line_Auto_Detail after init Mesg. mo_type='||p_move_order_type||', mo_line_id='||p_mo_line_id);
2122 /* check what is the move order type. If it's a non pick wave mo, */
2123 /* call the pick engine, otherwise, call the pick release api */
2124 /* call directed pick and put away api */
2125 IF ( p_move_order_type = 3 )
2126 THEN
2127 /* Get The Move Order line (1 line) */
2128 l_mo_line_tbl(1) := GMI_Move_Order_Line_Util.Query_Row( p_mo_line_id);
2129
2130 l_mo_line_rec.line_id := p_mo_line_id;
2131
2132 GMI_Move_Order_Line_Util.Lock_Row(
2133 p_mo_line_rec => l_mo_line_rec
2134 , x_mo_line_rec => ll_mo_line_rec
2135 , x_return_status => x_return_status);
2136
2137 IF ( x_return_status = '54' )
2138 THEN
2139 GMI_Reservation_Util.PrintLn('(opm_dbg) Line_Auto_Detail : the MO is locked for line_id='||p_mo_line_id);
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 END IF;
2142
2143 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_header_id ='||l_mo_line_tbl(1).header_id||', schedule_ship_date(date_required)='||l_mo_line_tbl(1).date_required);
2144 /* Get The Move Order header */
2145 l_mo_hdr_rec := GMI_Move_Order_Header_Util.Query_Row( l_mo_line_tbl(1).header_id);
2146
2147 /* B1513119 23-Nov-2000 odaboval : Added the grouping rules */
2148 /* Get defaults for organization */
2149 OPEN get_default_params(l_mo_line_tbl(1).organization_id);
2150 FETCH get_default_params
2151 INTO l_ps_mode,
2152 l_default_autodetail,
2153 l_default_autocreate_del,
2154 l_use_header_flag,
2155 l_default_to_sub,
2156 l_default_to_loc,
2157 l_pick_seq_rule_id,
2158 l_pick_grouping_rule_id;
2159 CLOSE get_default_params;
2160
2161 OPEN get_default_confirm(l_mo_line_tbl(1).organization_id);
2162 FETCH get_default_confirm
2163 INTO l_default_pickconfirm;
2164 CLOSE get_default_confirm;
2165
2166 l_mo_hdr_rec.grouping_rule_id := l_pick_grouping_rule_id;
2167
2168 /*
2169 BEGIN
2170 select auto_pick_confirm_flag
2171 into l_auto_pick_flag
2172 from wsh_picking_batches
2173 where name = l_request_number;
2174
2175 IF ( l_auto_pick_flag is null ) THEN
2176 l_auto_pick_flag := 'Y';
2177 END IF;
2178 GMI_Reservation_Util.PrintLn('(opm_dbg) l_auto_pick_flag is ' || l_auto_pick_flag);
2179 EXCEPTION
2180 WHEN no_data_found THEN
2181 fnd_message.set_name('INV', 'INV_AUTO_PICK_CONFIRM_PARAM');
2182 fnd_msg_pub.add;
2183 raise fnd_api.g_exc_unexpected_error;
2184 END;
2185 */
2186 SAVEPOINT GMI_Before_Pick_Release;
2187 /* IF ( l_auto_pick_flag = 'Y') THEN
2188 l_commit := FND_API.G_TRUE;
2189 ELSE
2190 */
2191 l_commit := FND_API.G_FALSE;
2192 /* END IF; */
2193
2194
2195
2196 l_p_allow_delete := p_allow_delete;
2197 GMI_Reservation_Util.PrintLn('(opm_dbg) l_p_allow_delete = ' || l_p_allow_delete) ;
2198 GMI_Reservation_Util.PrintLn('(opm_dbg) Before calling Auto_detail ='||l_mo_line_tbl(1).header_id);
2199 GMI_Pick_Release_PUB.Auto_Detail(
2200 p_api_version => 1.0,
2201 p_init_msg_lst => FND_API.G_FALSE,
2202 p_commit => l_commit,
2203 p_mo_hdr_rec => l_mo_hdr_rec,
2204 p_mo_line_tbl => l_mo_line_tbl,
2205 p_grouping_rule_id => l_mo_hdr_rec.grouping_rule_id,
2206 p_allow_delete => l_p_allow_delete,
2207 x_pick_release_status => l_pick_release_status,
2208 x_return_status => l_return_status,
2209 x_msg_data => x_msg_data,
2210 x_msg_count => x_msg_count);
2211
2212 GMI_Reservation_Util.PrintLn('(opm_dbg) l_return_status from GMI_pick_release_pub.Auto_detail is ' || l_return_status);
2213 /* Message('l_return_status from GMI_pick_release_pub.Auto_detail is ' || l_return_status); */
2214
2215 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2216 GMI_Reservation_Util.PrintLn('return error');
2217 /* fnd_msg_pub.count_and_get(p_count => l_count,
2218 p_data => l_message,
2219 p_encoded => 'F');
2220 if( l_count = 0) then
2221 GMI_Reservation_Util.PrintLn('(opm_dbg) no message return');
2222 else
2223 for I in 1..l_count LOOP
2224 l_message := fnd_msg_pub.get(I, 'F');
2225 GMI_Reservation_Util.PrintLn(l_message);
2226 end LOOP;
2227 end if;
2228 */
2229
2230 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2231 end if;
2232
2233 GMI_Reservation_Util.PrintLn('(opm_dbg) l_prick_release_status.count='||l_pick_release_status.count);
2234
2235 x_number_of_rows := 0;
2236 IF ( l_pick_release_status.count > 0 ) THEN
2237 FOR l_index IN 1..l_pick_release_status.count LOOP
2238 GMI_Reservation_Util.PrintLn('(opm_dbg) detail record loop pick_return_status=' || l_pick_release_status(l_index).return_status);
2239 x_number_of_rows := x_number_of_rows + l_pick_release_status(l_index).detail_rec_count;
2240 IF (l_pick_release_status(l_index).return_status <> FND_API.G_RET_STS_SUCCESS)
2241 THEN
2242 l_return_status := l_pick_release_status(l_index).return_status;
2243 END IF;
2244 END LOOP;
2245 GMI_Reservation_Util.PrintLn('(opm_dbg) Transaction row count=' || x_number_of_rows);
2246 END IF;
2247 GMI_Reservation_Util.PrintLn('(opm_dbg) after Checking the Pick_Release_rectype NO Error');
2248
2249
2250 if( l_detail_rec_count > 0 and l_auto_pick_flag = 'Y') then
2251 /* comment this out since it will take a long time to do
2252 pick release if we wait this print pick slip
2253 */
2254 commit;
2255 GMI_Reservation_Util.PrintLn('(opm_dbg) auto pick confirm');
2256
2257 /* odab Keep this for the Pick Slip
2258 for l_index in 1..l_trolin_tbl.count LOOP
2259 GMI_Reservation_Util.PrintLn('get mold');
2260 l_mold_tbl := INV_MO_LINE_DETAIL_UTIL.query_rows(
2261 p_line_id => l_trolin_tbl(l_index).line_id);
2262 l_mold_tbl_temp := l_mold_tbl;
2263 if( l_mold_tbl.count = 0 ) then
2264 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2265 fnd_message.set_name('INV', 'INV_PICK_RELEASE_ERROR');
2266 fnd_msg_pub.add;
2267 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2268 else
2269 GMI_Reservation_Util.PrintLn('number of mold record is ' || l_mold_tbl.count);
2270 GMI_Reservation_Util.PrintLn('calling pick confirm');
2271 INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm(
2272 p_api_version_number => 1.0,
2273 p_init_msg_list => FND_API.G_FALSE,
2274 p_commit => FND_API.G_TRUE,
2275 x_return_status => l_return_status,
2276 x_msg_count => x_msg_count,
2277 x_msg_data => x_msg_data,
2278 p_move_order_type => p_move_order_type,
2279 p_transaction_mode => 1,
2280 p_trolin_tbl => l_trolin_tbl,
2281 p_mold_tbl => l_mold_tbl,
2282 x_mmtt_tbl => l_mold_tbl,
2283 x_trolin_tbl => l_trolin_tbl);
2284 GMI_Reservation_Util.PrintLn('after pick confirm with return status = ' || l_return_status);
2285 GMI_Reservation_Util.PrintLn('l_return_status = ' || l_return_status);
2286 if( l_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2287 l_success := 0;
2288 GMI_Reservation_Util.PrintLn('rollback changes');
2289 GMI_Reservation_Util.PrintLn('l_mold_tbl_temp.count = ' || l_mold_tbl_temp.count);
2290 for l_index in 1..l_mold_tbl_temp.count LOOP
2291 GMI_Reservation_Util.PrintLn('calling delete details');
2292 INV_Replenish_Detail_PUB.Delete_Details(
2293 p_transaction_temp_id => l_mold_tbl_temp(l_index).transaction_temp_id,
2294 p_move_order_line_id => l_mold_tbl_temp(l_index).move_order_line_id,
2295 p_reservation_id => l_mold_tbl_temp(l_index).reservation_id,
2296 p_transaction_quantity => l_mold_tbl_temp(l_index).transaction_quantity,
2297 p_primary_trx_qty => l_mold_tbl_temp(l_index).primary_quantity,
2298 x_return_status => l_return_status,
2299 x_msg_data => x_msg_data,
2300 x_msg_count => x_msg_count);
2301 GMI_Reservation_Util.PrintLn('after detele details with return status ' || l_return_status);
2302 if( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
2303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2304 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2305 RAISE FND_API.G_EXC_ERROR;
2306 END IF;
2307 update mtl_txn_request_lines
2308 set quantity_detailed = quantity_detailed - l_mold_tbl_temp(l_index).transaction_quantity,
2309 quantity_delivered = quantity_delivered - l_mold_tbl_temp(l_index).transaction_quantity
2310 where line_id = l_mold_tbl_temp(l_index).move_order_line_id;
2311 end loop;
2312 update mtl_txn_request_lines
2313 set line_status = 7
2314 where line_id = p_mo_line_id;
2315 commit;
2316 else
2317 l_success := 1;
2318 end if;
2319 x_return_status := l_return_status;
2320 GMI_Reservation_Util.PrintLn('return status is ' || l_return_status);
2321 end if;
2322 x_return_status := l_return_status;
2323 end loop;
2324 odab Keep this for the Pick Slip */
2325
2326 END IF;
2327 END IF;
2328
2329 /* =============================================================================
2330 Prepare to retrieve the return value :
2331 Need the default lot index,
2332 and if the item is loct_ctl or lot_ctl
2333 ============================================================================ */
2334 GMI_Reservation_Util.Get_DefaultLot_from_ItemCtl
2335 ( p_organization_id => l_mo_line_tbl(1).organization_id
2336 , p_inventory_item_id => l_mo_line_tbl(1).inventory_item_id
2337 , x_default_lot_index => l_default_lot_index
2338 , x_return_status => x_return_status
2339 , x_msg_count => x_msg_count
2340 , x_msg_data => x_msg_data);
2341
2342
2343
2344 /* ==================================================================
2345 Set the Returned values from the GMI_Reservation_Util.ic_tran_rec_tbl
2346 ================================================================== */
2347 x_detailed_qty := 0;
2348 x_detailed_qty2 := 0;
2349 FOR l_count IN 1..GMI_Reservation_Util.ic_tran_rec_tbl.COUNT
2350 LOOP
2351 IF (l_count <> l_default_lot_index)
2352 THEN
2353 x_detailed_qty := x_detailed_qty - GMI_Reservation_Util.ic_tran_rec_tbl(l_count).trans_qty;
2354 x_qty_UM := GMI_Reservation_Util.ic_tran_rec_tbl(l_count).trans_UM;
2355 x_detailed_qty2 := x_detailed_qty2 - GMI_Reservation_Util.ic_tran_rec_tbl(l_count).trans_qty2;
2356 x_qty_UM2 := GMI_Reservation_Util.ic_tran_rec_tbl(l_count).trans_UM2;
2357 x_qc_grade := GMI_Reservation_Util.ic_tran_rec_tbl(l_count).qc_grade;
2358 END IF;
2359 END LOOP;
2360
2361 /* ==================================================================
2362 Set the Returned values from the GMI_Reservation_Util.ic_tran_rec_tbl
2363 ================================================================== */
2364 GMI_Reservation_Util.PrintLn('In Reallocate : default_lot_index='||l_default_lot_index||', detail_qty='||x_detailed_qty||', mo_line_id='||l_mo_line_tbl(1).line_id);
2365
2366 update ic_txn_request_lines
2367 set quantity_detailed = x_detailed_qty
2368 , secondary_quantity_detailed = x_detailed_qty2
2369 where line_id = l_mo_line_tbl(1).line_id;
2370
2371 l_success := 1;
2372 if( l_success = 1 ) then
2373 x_return_status := FND_API.G_RET_STS_SUCCESS;
2374 else
2375 raise FND_API.G_EXC_ERROR;
2376 end if;
2377
2378
2379 EXCEPTION
2380 WHEN FND_API.G_EXC_ERROR THEN
2381 x_return_status := FND_API.G_RET_STS_ERROR;
2382 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2383
2384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2385 IF (x_return_status = '54')
2386 THEN
2387 x_return_status := '54' ;
2388 ELSE
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2390 END IF;
2391
2392 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2393
2394 WHEN OTHERS THEN
2395
2396 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2397 THEN
2398 FND_MSG_PUB.Add_Exc_Msg
2399 ( G_PKG_NAME
2400 , 'Line_Details_PUB'
2401 );
2402 END IF;
2403 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2404
2405 END Line_Auto_Detail;
2406
2407 PROCEDURE Line_Pick_Confirm
2408 ( p_mo_line_id IN NUMBER
2409 , p_init_msg_list IN NUMBER
2410 , p_move_order_type IN NUMBER
2411 , x_delivered_qty OUT NOCOPY NUMBER
2412 , x_qty_UM OUT NOCOPY VARCHAR2
2413 , x_delivered_qty2 OUT NOCOPY NUMBER
2414 , x_qty_UM2 OUT NOCOPY VARCHAR2
2415 , x_return_status OUT NOCOPY VARCHAR2
2416 , x_msg_count OUT NOCOPY NUMBER
2417 , x_msg_data OUT NOCOPY VARCHAR2
2418 )
2419 IS
2420 l_api_version_number CONSTANT NUMBER := 1.0;
2421 l_init_msg_list VARCHAR2(255) := FND_API.G_TRUE;
2422 l_api_name CONSTANT VARCHAR2(30) := 'Line_Pick_Confirm';
2423 x_success NUMBER;
2424
2425 l_mo_hdr_rec GMI_Move_Order_Global.mo_hdr_rec;
2426 l_mo_line_tbl GMI_Move_Order_Global.mo_line_tbl;
2427 -- HW OPM changes for NOCOPY
2428 ll_mo_line_tbl GMI_Move_Order_Global.mo_line_tbl;
2429 l_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
2430 ll_mo_line_rec GMI_Move_Order_Global.mo_line_rec;
2431
2432 l_return_status VARCHAR2(1);
2433 l_grouping_rule_id NUMBER;
2434 l_count NUMBER;
2435 l_detail_rec_count NUMBER;
2436 l_success NUMBER;
2437 l_request_number VARCHAR2(80);
2438 l_commit VARCHAR2(1);
2439
2440
2441 BEGIN
2442
2443 gmi_reservation_util.println('In line_pick_confirm and line_id is '||p_mo_line_id);
2444 /* Init status : */
2445 x_return_status := FND_API.G_RET_STS_SUCCESS;
2446
2447 IF (p_init_msg_list = 1)
2448 THEN
2449 FND_MSG_PUB.Initialize;
2450 END IF;
2451
2452 /* check what is the move order type. If it's a non pick wave mo,
2453 call the pick engine, otherwise, call the pick release api
2454 call directed pick and put away api
2455 */
2456 IF ( p_move_order_type = 3 )
2457 THEN
2458 gmi_reservation_util.println('Move Order type is 3');
2459 /* Get The Move Order line (1 line) */
2460 gmi_reservation_util.println('Going to query the row with id = '||p_mo_line_id);
2461 l_mo_line_tbl(1) := GMI_Move_Order_Line_Util.Query_Row( p_mo_line_id);
2462
2463
2464 l_mo_line_rec.line_id := p_mo_line_id;
2465 gmi_reservation_util.println('Value of l_mo_line_rec.line_id using to lock row is '||l_mo_line_rec.line_id);
2466 GMI_Move_Order_Line_Util.Lock_Row(
2467 p_mo_line_rec => l_mo_line_rec
2468 , x_mo_line_rec => ll_mo_line_rec
2469 , x_return_status => x_return_status);
2470
2471 IF ( x_return_status = '54' )
2472 THEN
2473 GMI_Reservation_Util.PrintLn('(opm_dbg) Line_Pick_Confirm : the MO is locked for line_id='||p_mo_line_id);
2474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2475 END IF;
2476
2477
2478 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_header_id ='||l_mo_line_tbl(1).header_id);
2479 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_line_tbl.COUNT ='||l_mo_line_tbl.COUNT);
2480
2481 SAVEPOINT GMI_Before_Pick_Confirm;
2482 l_commit := FND_API.G_FALSE;
2483
2484 GMI_Reservation_Util.PrintLn('(opm_dbg) Before calling Pick_Confirm ='||l_mo_line_tbl(1).header_id);
2485 /* NC 11/14/02 changed the call from GMI_Pick_Wave_Confirm_PUB
2486 to GMI_Pick_Wave_Confirm_PVT. enhancement # 2557029 */
2487
2488 GMI_Pick_Wave_Confirm_PVT.Pick_Confirm(
2489 p_api_version_number => 1.0,
2490 p_init_msg_lst => FND_API.G_FALSE,
2491 p_validation_flag => FND_API.G_VALID_LEVEL_FULL,
2492 p_commit => l_commit,
2493 p_mo_line_tbl => l_mo_line_tbl,
2494 x_mo_line_tbl => ll_mo_line_tbl,
2495 x_return_status => l_return_status,
2496 x_msg_data => x_msg_data,
2497 x_msg_count => x_msg_count);
2498
2499
2500 GMI_Reservation_Util.PrintLn('(opm_dbg) l_return_status from GMI_pick_wave_Confirm_pub.Pick_Confirm is ' || l_return_status);
2501 GMI_Reservation_Util.PrintLn('(opm_dbg) mo_line.count=' || l_mo_line_tbl.count);
2502 /* Message('l_return_status from GMI_pick_release_pub.Auto_detail is ' || l_return_status); */
2503
2504 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS )
2505 THEN
2506 GMI_Reservation_Util.PrintLn('return error');
2507 FND_MESSAGE.Set_Name('GMI','PICK_CONFIRM_ERROR');
2508 FND_MESSAGE.Set_Token('WHERE', 'AFTER_CALL_PICK_CONFIRM');
2509 FND_MESSAGE.Set_Token('WHAT', 'UnexpectedError');
2510 FND_MSG_PUB.Add;
2511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2512 END IF;
2513
2514 IF ( l_mo_line_tbl.count = 0 )
2515 THEN
2516 GMI_Reservation_Util.PrintLn('return error');
2517 FND_MESSAGE.Set_Name('GMI','PICK_CONFIRM_ERROR');
2518 FND_MESSAGE.Set_Token('WHERE', 'MO_LINE_COUNT_0');
2519 FND_MESSAGE.Set_Token('WHAT', 'UnexpectedError');
2520 FND_MSG_PUB.Add;
2521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2522 END IF;
2523
2524 END IF;
2525
2526
2527 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value');
2528 /* ==================================================================
2529 Set the Returned values from the GMI_Reservation_Util.ic_tran_rec_tbl
2530 ================================================================== */
2531 x_delivered_qty := l_mo_line_tbl(1).quantity_delivered;
2532 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 1');
2533 x_qty_UM := l_mo_line_tbl(1).uom_code;
2534 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 2');
2535 x_delivered_qty2 := l_mo_line_tbl(1).secondary_quantity_delivered;
2536 GMI_Reservation_Util.PrintLn('(opm_dbg) Before setting the return value 3');
2537 x_qty_UM2 := l_mo_line_tbl(1).secondary_uom_code;
2538
2539 GMI_Reservation_Util.PrintLn('(opm_dbg) End of GMI_pick_wave_Confirm_pub.Pick_Confirm, l_return_status is ' || l_return_status);
2540
2541
2542 EXCEPTION
2543 WHEN FND_API.G_EXC_ERROR THEN
2544 /* ROLLBACK TO SAVEPOINT GMI_Before_Pick_Confirm; */
2545
2546 x_return_status := FND_API.G_RET_STS_ERROR;
2547 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2548
2549 WHEN OTHERS THEN
2550 /* ROLLBACK TO SAVEPOINT GMI_Before_Pick_Confirm; */
2551
2552 IF (x_return_status = '54')
2553 THEN
2554 x_return_status := '54' ;
2555 ELSE
2556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2557 END IF;
2558
2559 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2560 THEN
2561 FND_MSG_PUB.Add_Exc_Msg
2562 ( G_PKG_NAME
2563 , 'Line_Pick_Confirm');
2564 END IF;
2565 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data);
2566
2567 END Line_Pick_Confirm;
2568
2569
2570 PROCEDURE AUTO_ALLOC_CONFIRM_SRS
2571 (
2572 errbuf OUT NOCOPY VARCHAR2,
2573 retcode OUT NOCOPY VARCHAR2,
2574 p_pick_confirm IN VARCHAR2,
2575 p_whse_code IN VARCHAR2,
2576 p_from_order_num IN NUMBER,
2577 p_to_order_num IN NUMBER,
2578 p_item_num IN VARCHAR2,
2579 p_cust_num IN VARCHAR2,
2580 p_from_ship_date IN VARCHAR2,
2581 p_to_ship_date IN VARCHAR2,
2582 p_log_level IN NUMBER
2583 )
2584 IS
2585
2586 c_order_separator CONSTANT VARCHAR2(100) :=
2587 '========================================================================';
2588
2589 c_event_separator CONSTANT VARCHAR2(100) :=
2590 '------------------------------------------------------------------------';
2591
2592 l_init_msg_list VARCHAR2(255) := FND_API.G_TRUE;
2593 l_detailed_qty NUMBER := 0;
2594 l_mo_line_id NUMBER;
2595 l_inv_item_id NUMBER;
2596 l_txn_source_line_id NUMBER;
2597 l_number_rows NUMBER;
2598 l_detailed_qty2 NUMBER;
2599 l_qty_um VARCHAR2(30);
2600 l_order_number VARCHAR2(30);
2601 l_line_number NUMBER;
2602 l_ship_date VARCHAR2(30);
2603 l_qty_um2 VARCHAR2(30);
2604 l_qc_grade VARCHAR2(30);
2605 l_return_status VARCHAR2(1);
2606 l_whse_code VARCHAR2(4);
2607 l_msg_count NUMBER;
2608 l_item_id NUMBER;
2609 l_required_lines NUMBER;
2610 l_total_lines NUMBER;
2611 l_sucess_pick NUMBER;
2612 l_pick_fail NUMBER;
2613 l_sucess_alloc NUMBER;
2614 l_manual_lines NUMBER;
2615 l_nostock NUMBER;
2616 l_nostock_ind NUMBER;
2617 l_no_class NUMBER;
2618 l_alloc_class VARCHAR(8);
2619 l_qty_onhand NUMBER;
2620 l_qty_req NUMBER;
2621 l_qty_det NUMBER;
2622 l_qty_delivered NUMBER;
2623 l_date_required DATE;
2624 l_msg_data VARCHAR2(2000);
2625 l_message VARCHAR2(2000);
2626 l_response VARCHAR2(300);
2627 l_item_no VARCHAR2(30);
2628 l_completion_status NUMBER;
2629 l_temp BOOLEAN;
2630 l_org_id NUMBER;
2631 NO_LINES_FOUND EXCEPTION;
2632
2633 G_PICK_CONFIRM VARCHAR2(1);
2634 G_ORG_ID NUMBER;
2635 G_INV_ITEM_ID NUMBER;
2636 G_FROM_ORDER_NUM NUMBER;
2637 G_TO_ORDER_NUM NUMBER;
2638 G_SHIP_TO_ID NUMBER;
2639 G_FROM_SHIP_DATE VARCHAR2(11);
2640 G_TO_SHIP_DATE VARCHAR2(11);
2641 G_NORMAL NUMBER :=0;
2642 G_WARNING NUMBER :=1;
2643 G_COMP_STATUS NUMBER;
2644
2645 Cursor get_order_info( p_source_line_id IN NUMBER)
2646 IS
2647 Select h.order_number, d.line_number, d.schedule_ship_date,h.name
2648 From oe_order_headers_all h
2649 , oe_order_lines_all d
2650 , hr_operating_units h
2651 Where h.header_id = d.header_id
2652 and h.organization_id = d.org_id
2653 and d.line_id = p_source_line_id;
2654
2655 cursor c_inv_info IS
2656 SELECT NVL(SUM(LOCT_ONHAND),0) - NVL(SUM(ABS(COMMIT_QTY)),0)
2657 FROM IC_ITEM_INV_V
2658 WHERE item_id = l_item_id
2659 AND whse_code = l_whse_code;
2660
2661 cursor c_item_info IS
2662 SELECT i.item_id, i.item_no,i.alloc_class
2663 FROM IC_ITEM_MST i, MTL_SYSTEM_ITEMS m
2664 WHERE m.organization_id = l_org_id
2665 And m.inventory_item_id = l_inv_item_id
2666 AND i.item_no = m.segment1;
2667
2668
2669 -- Cusror Fetches to retrieve Internal Id's
2670
2671 CURSOR c_get_org_id ( l_whse_code in VARCHAR)
2672 IS
2673 SELECT MTL_ORGANIZATION_ID
2674 FROM IC_WHSE_MST
2675 WHERE WHSE_CODE = l_whse_code;
2676
2677 CURSOR c_get_inv_item_id ( l_org_id in NUMBER,l_item_no In VARCHAR)
2678 IS
2679 SELECT m.inventory_item_id
2680 FROM IC_ITEM_MST i, MTL_SYSTEM_ITEMS m
2681 WHERE m.organization_id = l_org_id
2682 AND m.segment1 = l_item_no;
2683
2684 CURSOR c_get_ship_to_id ( l_cust_num IN VARCHAR, l_whse_code IN VARCHAR)
2685 IS
2686 SELECT C.OF_SHIP_TO_SITE_USE_ID
2687 FROM OP_CUST_MST C, IC_WHSE_MST I, SY_ORGN_MST S
2688 WHERE S.ORGN_CODE = i.ORGN_CODE
2689 AND S.CO_CODE = C.CO_CODE
2690 AND I.WHSE_CODE = l_whse_code
2691 AND CUST_NO = l_cust_num;
2692
2693 -- Line Selection Criteria
2694 -- Cursors
2695
2696 CURSOR C_get_lines_count
2697 IS
2698 SELECT COUNT(1)
2699 FROM ic_txn_request_lines mo,
2700 oe_order_lines_all l,
2701 oe_order_headers_all h,
2702 oe_transaction_types_all ta,
2703 oe_transaction_types_tl tt
2704 WHERE l.header_id = h.header_id
2705 AND l.line_id = mo.txn_source_line_id
2706 AND tt.transaction_type_id = ta.transaction_type_id
2707 AND ta.transaction_type_id = h.order_type_id
2708 AND ta.org_id = h.org_id
2709 AND mo.line_status <> 5
2710 AND NVL(mo.quantity_delivered,0) < mo.quantity
2711 AND tt.language = userenv('LANG')
2712 AND nvl(l.cancelled_flag,'N') = 'N'
2713 AND l.ship_from_org_id = G_ORG_ID
2714 AND h.order_number between --B2593897
2715 NVL(G_FROM_ORDER_NUM,h.order_number) AND NVL(G_TO_ORDER_NUM,h.order_number)
2716 AND NVL(G_SHIP_TO_ID,l.ship_to_org_id) = l.ship_to_org_id
2717 AND TRUNC(l.schedule_ship_date)
2718 between NVL(G_FROM_SHIP_DATE,TRUNC(l.schedule_ship_date))
2719 AND NVL(G_TO_SHIP_DATE,TRUNC(l.schedule_ship_date))
2720 AND l.inventory_item_id = NVL(G_INV_ITEM_ID, l.inventory_item_id) --B2594191
2721 ;
2722
2723
2724 CURSOR C_get_lines
2725 IS
2726 SELECT
2727 h.order_number,
2728 mo.line_id,
2729 mo.organization_id,
2730 mo.txn_source_line_id,
2731 mo.date_required,
2732 mo.quantity,
2733 mo.quantity_detailed,
2734 mo.quantity_delivered,
2735 mo.inventory_item_id
2736 FROM ic_txn_request_lines mo,
2737 oe_order_lines_all l,
2738 oe_order_headers_all h,
2739 oe_transaction_types_all ta,
2740 oe_transaction_types_tl tt
2741 WHERE l.header_id = h.header_id
2742 AND l.line_id = mo.txn_source_line_id
2743 AND tt.transaction_type_id = ta.transaction_type_id
2744 AND ta.transaction_type_id = h.order_type_id
2745 AND ta.org_id = h.org_id
2746 AND mo.line_status <> 5
2747 AND NVL(mo.quantity_delivered,0) < mo.quantity
2748 AND tt.language = userenv('LANG')
2749 AND nvl(l.cancelled_flag,'N') = 'N'
2750 AND l.ship_from_org_id = G_ORG_ID
2751 AND h.order_number between --B2593897
2752 NVL(G_FROM_ORDER_NUM,h.order_number) AND NVL(G_TO_ORDER_NUM,h.order_number)
2753 AND NVL(G_SHIP_TO_ID,l.ship_to_org_id) = l.ship_to_org_id
2754 AND TRUNC(l.schedule_ship_date)
2755 between NVL(G_FROM_SHIP_DATE,TRUNC(l.schedule_ship_date))
2756 AND NVL(G_TO_SHIP_DATE,TRUNC(l.schedule_ship_date))
2757 AND l.inventory_item_id = NVL(G_INV_ITEM_ID, l.inventory_item_id) --B2594191
2758
2759 ORDER BY 1,2;
2760
2761 BEGIN
2762
2763 IF p_log_level <> FND_API.G_MISS_NUM THEN -- log level fix
2764 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
2765 OE_DEBUG_PUB.DEBUG_ON;
2766 OE_DEBUG_PUB.SETDEBUGLEVEL(p_log_level);
2767 WSH_UTIL_CORE.Set_Log_Level(p_log_level);
2768 END IF;
2769
2770 G_COMP_STATUS := G_NORMAL;
2771
2772
2773 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2774
2775 WSH_UTIL_CORE.PRINTMSG;
2776 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
2777 WSH_UTIL_CORE.PRINTMSG(' Pick Confirm => ' || p_pick_confirm );
2778 WSH_UTIL_CORE.PRINTMSG(' Inventory Org => ' || p_whse_code);
2779 WSH_UTIL_CORE.PRINTMSG(' From Order Number => ' || p_from_order_num );
2780 WSH_UTIL_CORE.PRINTMSG(' To Order Number => ' || p_to_order_num );
2781 WSH_UTIL_CORE.PRINTMSG(' Item Number => ' || p_item_num );
2782 WSH_UTIL_CORE.PRINTMSG(' Customer Number => ' || p_cust_num );
2783 WSH_UTIL_CORE.PRINTMSG(' From Ship Date => ' || p_from_ship_date );
2784 WSH_UTIL_CORE.PRINTMSG(' To Ship Date => ' || p_to_ship_date );
2785 WSH_UTIL_CORE.PRINTMSG(' Debug Level => ' || p_log_level);
2786
2787
2788 -- Lets Generate the Internal Id's For Parameters
2789 -- If the value is NULL we do nothing.
2790 -- We will always default p_pick_confirm to be N
2791 -- P_whse_code must be passed, all the other parameters
2792 -- are optional.
2793
2794 OPEN c_get_org_id(p_whse_code);
2795 FETCH c_get_org_id INTO G_ORG_ID;
2796 CLOSE c_get_org_id;
2797
2798
2799 -- Set Pick Confirm Option
2800
2801 G_PICK_CONFIRM := p_pick_confirm;
2802
2803 IF p_from_order_num IS NULL THEN
2804 G_FROM_ORDER_NUM := NULL;
2805 ELSE
2806 G_FROM_ORDER_NUM := p_from_order_num;
2807 END IF;
2808
2809 IF p_to_order_num IS NULL THEN
2810 G_TO_ORDER_NUM := NULL;
2811 ELSE
2812 G_TO_ORDER_NUM := p_to_order_num;
2813 END IF;
2814
2815 IF p_from_ship_date IS NULL THEN
2816 G_FROM_SHIP_DATE := NULL;
2817 ELSE
2818 G_FROM_SHIP_DATE := p_from_ship_date;
2819 END IF;
2820
2821 IF p_to_ship_date IS NULL THEN
2822 G_TO_SHIP_DATE := NULL;
2823 ELSE
2824 G_TO_SHIP_DATE := p_to_ship_date;
2825 END IF;
2826
2827
2828 IF p_cust_num IS NULL THEN
2829 G_SHIP_TO_ID := NULL;
2830 ELSE
2831 OPEN c_get_ship_to_id ( p_cust_num, p_whse_code);
2832 FETCH c_get_ship_to_id into G_SHIP_TO_ID;
2833 CLOSE c_get_ship_to_id;
2834 END IF;
2835
2836
2837 IF p_item_num is NULL THEN
2838 G_INV_ITEM_ID := NULL;
2839 ELSE
2840 OPEN c_get_inv_item_id ( G_ORG_ID, p_item_num);
2841 FETCH c_get_inv_item_id INTO G_INV_ITEM_ID;
2842 CLOSE c_get_inv_item_id;
2843 END IF;
2844
2845
2846 WSH_UTIL_CORE.PRINTLN( ' G_ORG_ID => ' || G_ORG_ID);
2847 WSH_UTIL_CORE.PRINTLN( ' G_INV_ITEM_ID => ' || G_INV_ITEM_ID);
2848 WSH_UTIL_CORE.PRINTLN( ' G_FROM_ORDER_NUM => ' || G_FROM_ORDER_NUM);
2849 WSH_UTIL_CORE.PRINTLN( ' G_TO_ORDER_NUM => ' || G_TO_ORDER_NUM);
2850 WSH_UTIL_CORE.PRINTLN( ' G_SHIP_TO_ID => ' || G_SHIP_TO_ID);
2851
2852
2853 FND_MSG_PUB.INITIALIZE;
2854 l_required_lines :=0;
2855 l_total_lines :=0;
2856 l_sucess_pick :=0;
2857 l_pick_fail :=0;
2858 l_sucess_alloc :=0;
2859 l_nostock :=0;
2860 l_no_class :=0;
2861 l_manual_lines :=0;
2862
2863
2864 -- Determine How Many Lines We are Likely to Process
2865 -- Given Input Parameters
2866
2867 OPEN c_get_lines_count;
2868 FETCH c_get_lines_count INTO l_total_lines;
2869 CLOSE c_get_lines_count;
2870
2871 IF l_total_lines = 0 THEN
2872 RAISE NO_LINES_FOUND;
2873 ELSE
2874 WSH_UTIL_CORE.PRINTMSG( ' Lines Needed To Process => ' || l_total_lines);
2875 WSH_UTIL_CORE.PRINTMSG;
2876 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
2877 END IF;
2878
2879 OPEN C_get_lines;
2880 LOOP
2881 FETCH c_get_lines INTO
2882 l_order_number, l_mo_line_id ,l_org_id, l_txn_source_line_id,
2883 l_date_required,l_qty_req,l_qty_det,l_qty_delivered,l_inv_item_id;
2884
2885 EXIT WHEN c_get_lines%NOTFOUND;
2886
2887 l_required_lines := l_required_lines + 1;
2888 WSH_UTIL_CORE.PRINTMSG;
2889 WSH_UTIL_CORE.PRINTMSG(c_order_separator);
2890 WSH_UTIL_CORE.PRINTMSG(' PROCESSING LINE => '|| l_required_lines);
2891
2892 WSH_UTIL_CORE.PRINTLN(' HAM DEBUGGGGGGG');
2893
2894 Open get_order_info(l_txn_source_line_id);
2895 Fetch get_order_info
2896 into l_order_number,l_line_number,l_ship_date, l_response;
2897 Close get_order_info;
2898
2899 WSH_UTIL_CORE.PRINTMSG;
2900 WSH_UTIL_CORE.PRINTMSG( ' Order Number => ' || l_order_number);
2901 WSH_UTIL_CORE.PRINTMSG( ' Responsibility=> ' || l_response);
2902 WSH_UTIL_CORE.PRINTMSG( ' Line Number => ' || l_line_number);
2903 WSH_UTIL_CORE.PRINTMSG( ' Order Line Id => ' || l_txn_source_line_id);
2904 WSH_UTIL_CORE.PRINTMSG( ' Sch Ship Date => ' || l_ship_date);
2905 WSH_UTIL_CORE.PRINTMSG( ' Date Required => ' || l_date_required);
2906 WSH_UTIL_CORE.PRINTMSG( ' Whse Code => ' || p_whse_code);
2907 WSH_UTIL_CORE.PRINTMSG( ' Move Line Id => ' || l_mo_line_id);
2908 WSH_UTIL_CORE.PRINTMSG( ' Qty Required => ' || l_qty_req);
2909 WSH_UTIL_CORE.PRINTMSG( ' Qty Detailed => ' || l_qty_det);
2910 WSH_UTIL_CORE.PRINTMSG( ' Qty Delivered => ' || l_qty_delivered);
2911
2912 -- Check if Item Has Allocation Class Associated
2913 -- If no class then item can not be auto allocated
2914
2915
2916 -- Use Cursor For alloc Class
2917 OPEN c_item_info;
2918 FETCH c_item_info into l_item_id, l_item_no, l_alloc_class;
2919 CLOSE c_item_info;
2920
2921 IF l_alloc_class = NULL THEN
2922
2923 l_no_class := l_no_class +1;
2924
2925 WSH_UTIL_CORE.PRINTMSG( ' Item No => ' || l_item_no || ' Has No Allocvation Class');
2926
2927 ELSE
2928
2929
2930 WSH_UTIL_CORE.PRINTMSG;
2931 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
2932 WSH_UTIL_CORE.PRINTMSG( ' START ALLOCATION ');
2933
2934 l_nostock_ind :=0;
2935
2936
2937 -- B2497472 EMC
2938 -- As per enhanced auto allocation, allow call to Line_Auto_Detail
2939 -- regardless of whether lines are fully allocated.
2940 /*
2941 IF l_qty_req = l_qty_det THEN
2942 WSH_UTIL_CORE.PRINTMSG( ' Move Order Is Fully Alocated');
2943 l_return_status := FND_API.G_RET_STS_SUCCESS;
2944 ELSE
2945 l_return_status := FND_API.G_RET_STS_SUCCESS;
2946 */
2947
2948
2949 GMI_Reservation_Util.PrintLn('(opm_dbg) in alloc engine b4 call to LINE_AUTO_DETAIL');
2950
2951 GMI_Move_Order_Line_Util.Line_Auto_Detail
2952 ( p_mo_line_id => l_mo_line_id
2953 , p_init_msg_list => 1
2954 , p_transaction_header_id => 0
2955 , p_transaction_mode => 1
2956 , p_move_order_type => 3
2957 , p_allow_delete => NULL
2958 , x_number_of_rows => l_number_rows
2959 , x_qc_grade => l_qc_grade
2960 , x_detailed_qty => l_detailed_qty
2961 , x_qty_UM => l_qty_um
2962 , x_detailed_qty2 => l_detailed_qty2
2963 , x_qty_UM2 => l_qty_um2
2964 , x_return_status => l_return_status
2965 , x_msg_count => l_msg_count
2966 , x_msg_data => l_msg_data
2967 );
2968
2969 GMI_Reservation_Util.PrintLn('(opm_dbg) in alloc engine after call to LINE_AUTO_DETAIL');
2970
2971 WSH_UTIL_CORE.PRINTMSG(' Quantity Detailed => ' || NVL(l_detailed_qty,0));
2972
2973 IF nvl(l_detailed_qty,0) = 0 OR ( l_detailed_qty = l_qty_det ) THEN
2974
2975 WSH_UTIL_CORE.PRINTMSG;
2976 WSH_UTIL_CORE.PRINTMSG(' CHECK STOCK INFORMATION');
2977 WSH_UTIL_CORE.PRINTMSG(' ITEM => '|| l_item_no);
2978 WSH_UTIL_CORE.PRINTMSG(' ITEM ID => '|| l_item_id);
2979 WSH_UTIL_CORE.PRINTMSG(' WHSE => '|| p_whse_code);
2980
2981 OPEN c_inv_info;
2982 FETCH c_inv_info into l_qty_onhand;
2983 CLOSE c_inv_info;
2984
2985 WSH_UTIL_CORE.PRINTMSG(' ONHAND BALANCE => ' || l_qty_onhand);
2986
2987 IF l_qty_onhand <= 0 THEN
2988
2989 l_nostock := l_nostock + 1;
2990 l_nostock_ind :=1;
2991
2992 END IF;
2993
2994 END IF; /* End Of No Stock Check */
2995
2996 -- END IF; /* End Of Detail qty Check */ end B2497472
2997
2998 IF l_return_status <> 'S' THEN
2999
3000 WSH_UTIL_CORE.PRINTMSG;
3001 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3002 WSH_UTIL_CORE.PRINTMSG( ' ALLOCATION ERROR ');
3003 WSH_UTIL_CORE.PRINTMSG;
3004
3005 WSH_UTIL_CORE.PRINTMSG( ' Return Status => ' || l_return_status);
3006 WSH_UTIL_CORE.PRINTMSG( ' MSG count => ' || l_msg_count);
3007 -- FOR i in 1..l_msg_count LOOP
3008 l_message := fnd_msg_pub.get(1,'F');
3009 l_message := replace(l_message,fnd_global.local_chr(0),' ');
3010
3011
3012 WSH_UTIL_CORE.PRINTMSG( ' Error => ' || l_message);
3013 -- END LOOP;
3014
3015 WSH_UTIL_CORE.PRINTMSG;
3016 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3017 WSH_UTIL_CORE.PRINTMSG;
3018 FND_MSG_PUB.INITIALIZE;
3019 l_completion_status := G_WARNING;
3020
3021 ELSE
3022
3023 IF l_nostock_ind = 0 THEN
3024
3025 WSH_UTIL_CORE.PRINTMSG;
3026 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3027 WSH_UTIL_CORE.PRINTMSG( ' ALLOCATION SUCCESSFUL');
3028
3029 -- added commit
3030 commit;
3031
3032 WSH_UTIL_CORE.PRINTMSG(' ALLOC COMMIT ');
3033 l_completion_status := G_NORMAL;
3034 l_sucess_alloc := l_sucess_alloc +1;
3035
3036 IF G_PICK_CONFIRM = 'Y' THEN
3037
3038 l_return_status := FND_API.G_RET_STS_SUCCESS;
3039 l_return_status := FND_API.G_RET_STS_SUCCESS;
3040
3041 WSH_UTIL_CORE.PRINTMSG( ' START PICK CONFIRMATION');
3042 WSH_UTIL_CORE.PRINTMSG;
3043
3044 GMI_Move_Order_Line_Util. Line_Pick_Confirm
3045 ( p_mo_line_id => l_mo_line_id
3046 , p_init_msg_list => 1
3047 , p_move_order_type => 3
3048 , x_delivered_qty => l_detailed_qty
3049 , x_qty_UM => l_qty_um
3050 , x_delivered_qty2 => l_detailed_qty2
3051 , x_qty_UM2 => l_qty_um2
3052 , x_return_status => l_return_status
3053 , x_msg_count => l_msg_count
3054 , x_msg_data => l_msg_data
3055 );
3056
3057
3058 IF l_return_status <> 'S' THEN
3059 WSH_UTIL_CORE.PRINTMSG;
3060 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3061 WSH_UTIL_CORE.PRINTMSG(' PICK CONFIRM ERROR');
3062 WSH_UTIL_CORE.PRINTMSG(' Return Status => ' || l_return_status);
3063 WSH_UTIL_CORE.PRINTMSG(' MSG count => ' || l_msg_count);
3064 l_message := fnd_msg_pub.get(1,'F');
3065 l_message := replace(l_message,fnd_global.local_chr(0),' ');
3066 WSH_UTIL_CORE.PRINTMSG(' Error => ' || l_message);
3067 FND_MSG_PUB.INITIALIZE;
3068 WSH_UTIL_CORE.PRINTMSG;
3069 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3070 l_completion_status := G_WARNING;
3071 ELSE
3072
3073 IF nvl(l_detailed_qty,0) = nvl(l_qty_delivered,0) THEN
3074
3075 WSH_UTIL_CORE.PRINTMSG;
3076 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3077 WSH_UTIL_CORE.PRINTMSG(' PICK CONFIRM FAILURE ');
3078
3079 l_pick_fail := l_pick_fail +1;
3080 l_completion_status := G_WARNING;
3081 ELSE
3082
3083 WSH_UTIL_CORE.PRINTMSG;
3084 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3085 WSH_UTIL_CORE.PRINTMSG(' PICK CONFIRM SUCCESSFUL ');
3086 l_completion_status := G_NORMAL;
3087
3088 l_sucess_pick := l_sucess_pick + 1 ;
3089
3090 -- added commit
3091 commit;
3092 END IF; /* End Check Detailed Qty */
3093
3094 END IF ; /* PICK CONFIRM TEST */
3095 ELSE
3096 WSH_UTIL_CORE.PRINTMSG;
3097 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3098 WSH_UTIL_CORE.PRINTMSG(' PICK CONFIRM NOT SELECTED ');
3099 END IF; /* PICK CONFIRM SELECTION */
3100
3101 END IF ; /* For Sucessful Allocation */
3102
3103 END IF ; /* For Auto Detail */
3104
3105 END IF ; /* For Alloc Class */
3106
3107
3108 -- Add logic to determine the completion status
3109 -- Only update if value is greater than current
3110
3111 IF l_completion_status > G_COMP_STATUS THEN
3112 G_COMP_STATUS := l_completion_status;
3113 END IF;
3114
3115 END LOOP;
3116 CLOSE c_get_lines;
3117
3118
3119 l_manual_lines := l_total_lines - ( l_nostock + l_sucess_alloc + l_no_class);
3120
3121 WSH_UTIL_CORE.PRINTMSG;
3122 WSH_UTIL_CORE.PRINTMSG( ' ************* SUMMARY ************ ');
3123 WSH_UTIL_CORE.PRINTMSG( ' Total Lines Need => ' || l_total_lines);
3124 WSH_UTIL_CORE.PRINTMSG( ' Total Lines Processed => ' || l_required_lines);
3125 WSH_UTIL_CORE.PRINTMSG( ' No Stock lines => ' || l_nostock);
3126 WSH_UTIL_CORE.PRINTMSG( ' No Alloc Rules Lines => ' || l_no_class);
3127 WSH_UTIL_CORE.PRINTMSG( ' Require Manual Allocation => ' || l_manual_lines);
3128 WSH_UTIL_CORE.PRINTMSG( ' Pick Confirm Failure => ' || l_pick_fail);
3129 WSH_UTIL_CORE.PRINTMSG;
3130 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3131 WSH_UTIL_CORE.PRINTMSG( ' Sucessful Allocations => ' || l_sucess_alloc);
3132 WSH_UTIL_CORE.PRINTMSG( ' Sucessful Pick Confirms => ' || l_sucess_pick);
3133 WSH_UTIL_CORE.PRINTMSG;
3134
3135
3136 IF G_COMP_STATUS = G_NORMAL THEN
3137 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL','');
3138 ELSE
3139 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','');
3140 END IF;
3141
3142 EXCEPTION
3143
3144 WHEN NO_LINES_FOUND THEN
3145 WSH_UTIL_CORE.PRINTMSG;
3146 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3147 WSH_UTIL_CORE.PRINTMSG(' NO LINES FOUND FOR ' );
3148 WSH_UTIL_CORE.PRINTMSG(' WHSE CODE => ' || p_whse_code);
3149 WSH_UTIL_CORE.PRINTMSG(' ORDER NUM => ' || p_from_order_num);
3150 WSH_UTIL_CORE.PRINTMSG(' ITEM NUM => ' || p_item_num);
3151 WSH_UTIL_CORE.PRINTMSG(' CUSTOMER => ' || p_cust_num);
3152 WSH_UTIL_CORE.PRINTMSG(' FROM DATE => ' || p_from_ship_date);
3153 WSH_UTIL_CORE.PRINTMSG(' TO DATE => ' || p_to_ship_date);
3154 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING','');
3155
3156 WHEN OTHERS THEN
3157 WSH_UTIL_CORE.PRINTMSG;
3158 WSH_UTIL_CORE.PRINTMSG(c_event_separator);
3159 WSH_UTIL_CORE.PRINTMSG(' ERR NUM => ' || SQLERRM);
3160 WSH_UTIL_CORE.PRINTMSG(' ERR MSG => ' || SQLCODE);
3161 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','');
3162
3163 END AUTO_ALLOC_CONFIRM_SRS;
3164
3165 --Procedure
3166 -- Cancel_Move_Order_Line
3167 --Description
3168 -- This procedure is called from Shipping when a delivery line that
3169 -- is released to warehouse is cancelled. This procedure will
3170 -- cancel the move order line. Cancelling a move order line is not
3171 -- the same as closing a move order line. The user does not have
3172 -- access to a closed move order line - it's as if the line has been
3173 -- deleted. A cancelled move order line can still be transacted from
3174 -- the move order forms. However, a cancelled move order line no
3175 -- longer has a corresponding record in Wsh_delivery_details, or a
3176 -- reservation tied to that move order. A cancelled move order line
3177 -- is simply an inventory transaction, moving material from one subinventory
3178 -- to another. The allocations still exist for the move order line.
3179 -- This procedure updates the status on the move order line to 8 and
3180 -- the required quantity to 0.
3181 -- The detailed_quantity column on the reservation is decremented as
3182 -- necessary. The reservation id on the allocation is deleted.
3183 -- If WMS is installed, this procedure will delete any tasks
3184 -- that are not yet dispatched.
3185 -- Parametrs
3186 -- p_line_id: The move order line id to be cancelled
3187 -- p_delete_reservations: 'Y' or 'N'
3188 -- If passed as 'Y', this procedure deletes/reduces quantity on
3189 -- reservations. Shipping will pass with 'Y' when the quantity
3190 -- on the sales order line is reduced, and a delivery detail is
3191 -- deleted as a result.
3192 -- p_txn_source_line_Id: The sales order line id. If this
3193 -- parameter is not passed in, we get it from the delivery detail.
3194 --
3195 /* for OPM, there is no concept of required_quantity, the quantity in the
3196 sense is the required qty and would be passed to shipping.
3197 OPM inventory might be different where a cancelled move order line can not
3198 be transacted in move order form. User can NOT move material from one lot
3199 to another by accessing the move order line.
3200 IF p_delete_reservations: 'Y' The inv transaction is simply
3201 deleted and qtys are returned to the default lot
3202 IF p_delete_reservations: 'N' The inv transaction is kept for the delivery line
3203 as if user has allocated them in order pad
3204
3205 since the move order line is calceled, user has to pick release the line again
3206 in order to transact
3207
3208 OPM does not support WMS yet. So this case is not considered
3209 */
3210
3211 PROCEDURE Cancel_Move_Order_Line(
3212 x_return_status OUT NOCOPY VARCHAR2
3213 ,x_msg_count OUT NOCOPY NUMBER
3214 ,x_msg_data OUT NOCOPY VARCHAR2
3215 ,p_line_id IN NUMBER
3216 ,p_delivery_detail_id IN NUMBER
3217 ,p_delete_reservations IN VARCHAR2
3218 ,p_txn_source_line_id IN NUMBER DEFAULT NULL)
3219
3220 IS
3221
3222 l_quantity NUMBER;
3223 l_quantity2 NUMBER;
3224 l_quantity_detailed NUMBER;
3225 l_quantity2_detailed NUMBER;
3226 l_deleted_quantity NUMBER;
3227 l_deleted_quantity2 NUMBER;
3228 l_quantity_to_delete NUMBER;
3229 l_quantity2_to_delete NUMBER;
3230 l_line_status NUMBER;
3231 l_organization_id NUMBER;
3232 l_ship_from_org_id NUMBER;
3233 l_return_status VARCHAR2(1);
3234 l_delete_reservations VARCHAR2(1);
3235 l_reservation_id NUMBER;
3236 l_primary_quantity NUMBER;
3237 l_txn_source_line_id NUMBER;
3238 l_error_code NUMBER;
3239 l_trans_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3240 l_trans_row ic_tran_pnd%ROWTYPE;
3241
3242 cursor c_line_info IS
3243 SELECT quantity
3244 , secondary_quantity
3245 , NVL(quantity_detailed, 0)
3246 , NVL(secondary_quantity_detailed, 0)
3247 , organization_id
3248 FROM ic_txn_request_lines
3249 WHERE line_id = p_line_id
3250 FOR UPDATE;
3251
3252 cursor c_txn_source_line IS
3253 SELECT source_line_id
3254 , organization_id
3255 FROM wsh_delivery_details
3256 WHERE move_order_line_id IS NOT NULL
3257 AND delivery_detail_id = p_delivery_detail_id
3258 AND move_order_line_id = p_line_id
3259 AND released_status = 'S';
3260
3261 cursor c_reservations IS
3262 SELECT trans_id
3263 FROM ic_tran_pnd
3264 WHERE line_id = l_txn_source_line_id
3265 AND line_detail_id = p_delivery_detail_id /* should have this filed populated since mo line*/
3266 AND staged_ind = 0
3267 AND delete_mark = 0
3268 AND doc_type = 'OMSO'
3269 AND trans_qty <> 0
3270 AND (lot_id > 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
3271 -- Bug 3962760 - exclude the default transaction.
3272 ORDER BY trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
3273 /* or should consider the alloc rules */
3274 BEGIN
3275 GMI_Reservation_Util.PrintLn('Entering Cancel_Move_order_line and line_id is '||p_line_id);
3276 --SAVEPOINT CANCELMO_SP;
3277
3278 l_deleted_quantity := 0;
3279
3280 /*IF p_delete_reservations IS NULL OR
3281 p_delete_reservations <> 'Y' THEN
3282 l_delete_reservations := 'N';
3283 gmi_reservation_util.println('L_delete is set to N');
3284 ELSE
3285 l_delete_reservations := 'Y';
3286 gmi_reservation_util.println('L_delete is set to Y');
3287 END IF;*/
3288 /* for OPM delete is always Y*/
3289 --l_delete_reservations := 'Y';
3290 l_delete_reservations := p_delete_reservations;
3291 GMI_Reservation_Util.PrintLn('Delete_rsvs = ' || l_delete_reservations);
3292
3293 gmi_reservation_util.println('Going to call open_C_line info in cancel_move_order ');
3294 --query mo line info
3295 OPEN c_line_info;
3296 FETCH c_line_info
3297 INTO l_quantity
3298 ,l_quantity2
3299 ,l_quantity_detailed
3300 ,l_quantity2_detailed
3301 ,l_organization_id;
3302 IF c_line_info%NOTFOUND THEN
3303 GMI_Reservation_Util.PrintLn('Error: Could not find mo line');
3304 RAISE fnd_api.g_exc_error;
3305 END IF;
3306 CLOSE c_line_info;
3307
3308 /* if the delivery_detail is deleted in shipping, all the rsv with it should be deleted */
3309 IF l_delete_reservations = 'Y'
3310 --AND l_quantity >= l_quantity_detailed
3311 THEN
3312 l_quantity_to_delete := l_quantity_detailed;
3313 l_quantity2_to_delete := l_quantity2_detailed;
3314 GMI_Reservation_Util.PrintLn('Qty to delete = ' || l_quantity_to_delete);
3315
3316 -- we query by the sales order line id. If that value is not
3317 -- passed in, we need to get it from shipping table
3318 If p_txn_source_line_id IS NOT NULL Then
3319 l_txn_source_line_id := p_txn_source_line_id;
3320 Else
3321 OPEN c_txn_source_line;
3322 FETCH c_txn_source_line
3323 INTO l_txn_source_line_id
3324 , l_organization_id;
3325 if c_txn_source_line%NOTFOUND then
3326 CLOSE c_txn_source_line;
3327 RAISE no_data_found;
3328 end if;
3329 CLOSE c_txn_source_line;
3330 End If;
3331 GMI_Reservation_Util.PrintLn('Src line id = ' || l_txn_source_line_id);
3332
3333 OPEN c_reservations;
3334 LOOP
3335 EXIT WHEN l_quantity_to_delete <= 0;
3336
3337 FETCH c_reservations INTO l_reservation_id;
3338 EXIT WHEN c_reservations%NOTFOUND;
3339
3340 l_trans_rec.trans_id := l_reservation_id;
3341 GMI_Reservation_Util.PrintLn('Rsv id = ' || l_reservation_id);
3342
3343 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3344 (l_trans_rec, l_trans_rec )
3345 THEN
3346 GMI_Reservation_Util.PrintLn('trans_qty = ' || l_trans_rec.trans_qty);
3347 IF abs(l_trans_rec.trans_qty) <= l_quantity_to_delete THEN
3348 -- if so, simply delete the rsv , will balance default later
3349 GMI_Reservation_Util.PrintLn('set delete trans' );
3350 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
3351 ( 1
3352 , FND_API.G_FALSE
3353 , FND_API.G_FALSE
3354 , FND_API.G_VALID_LEVEL_FULL
3355 , l_trans_rec
3356 , l_trans_row
3357 , x_return_status
3358 , x_msg_count
3359 , x_msg_data
3360 );
3361 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3362 THEN
3363 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
3364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3365 END IF;
3366 l_deleted_quantity := l_deleted_quantity + abs(l_trans_rec.trans_qty);
3367 l_deleted_quantity2 := l_deleted_quantity2 + abs(l_trans_rec.trans_qty2);
3368 ELSE
3369 GMI_Reservation_Util.PrintLn('update ic_tran_pnd ' );
3370 l_trans_rec.trans_qty := -1 * (abs(l_trans_rec.trans_qty) - l_quantity_to_delete);
3371 l_trans_rec.trans_qty2 := -1 * (abs(l_trans_rec.trans_qty2) - l_quantity2_to_delete);
3372 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
3373 ( 1
3374 , FND_API.G_FALSE
3375 , FND_API.G_FALSE
3376 , FND_API.G_VALID_LEVEL_FULL
3377 , l_trans_rec
3378 , l_trans_row
3379 , x_return_status
3380 , x_msg_count
3381 , x_msg_data
3382 );
3383 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3384 THEN
3385 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
3386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3387 END IF;
3388
3389 l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
3390 l_deleted_quantity2 := l_deleted_quantity2 + l_quantity2_to_delete;
3391 END IF;
3392 END IF;
3393 l_quantity_to_delete := l_quantity_to_delete - abs(l_trans_rec.trans_qty);
3394 l_quantity2_to_delete := l_quantity2_to_delete - abs(l_trans_rec.trans_qty2);
3395 END LOOP;
3396 CLOSE c_reservations;
3397 GMI_RESERVATION_UTIL.find_default_lot
3398 ( x_return_status => x_return_status,
3399 x_msg_count => x_msg_count,
3400 x_msg_data => x_msg_data,
3401 x_reservation_id => l_reservation_id,
3402 p_line_id => l_txn_source_line_id
3403 );
3404
3405 IF nvl(l_reservation_id,0) > 0 THEN -- no balancing if no default exist
3406 l_trans_rec.trans_id := l_reservation_id;
3407 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3408 (l_trans_rec, l_trans_rec )
3409 THEN
3410 Select ship_from_org_id
3411 Into l_ship_from_org_id
3412 From oe_order_lines_all
3413 Where line_id = l_txn_source_line_id;
3414
3415 Select mtl_organization_id
3416 Into l_organization_id
3417 From ic_whse_mst
3418 Where whse_code = l_trans_rec.whse_code;
3419
3420 GMI_Reservation_Util.PrintLn('om line ship_from_org_id '||l_ship_from_org_id );
3421 GMI_Reservation_Util.PrintLn('trans organization_id'||l_organization_id);
3422 IF l_ship_from_org_id = l_organization_id THEN
3423 GMI_RESERVATION_UTIL.balance_default_lot
3424 ( p_ic_default_rec => l_trans_rec
3425 , p_opm_item_id => l_trans_rec.item_id
3426 , x_return_status => x_return_status
3427 , x_msg_count => x_msg_count
3428 , x_msg_data => x_msg_data
3429 );
3430 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3431 THEN
3432 GMI_RESERVATION_UTIL.PrintLn('cancle move order Error returned by balancing default lot');
3433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3434 END IF;
3435 ELSE
3436 /* delete this default lot, and created a new one */
3437 GMI_Reservation_Util.PrintLn('delete trans trans_id'||l_trans_rec.trans_id);
3438 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
3439 ( 1
3440 , FND_API.G_FALSE
3441 , FND_API.G_FALSE
3442 , FND_API.G_VALID_LEVEL_FULL
3443 , l_trans_rec
3444 , l_trans_row
3445 , x_return_status
3446 , x_msg_count
3447 , x_msg_data
3448 );
3449
3450 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3451 THEN
3452 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
3453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3454 END IF;
3455
3456 Select whse_code
3457 Into l_trans_rec.whse_code
3458 From ic_whse_mst
3459 Where mtl_organization_id = l_ship_from_org_id
3460 and rownum = 1; -- just in case, should only have 1 row
3461
3462 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
3463 ( 1
3464 , FND_API.G_FALSE
3465 , FND_API.G_FALSE
3466 , FND_API.G_VALID_LEVEL_FULL
3467 , l_trans_rec
3468 , l_trans_row
3469 , x_return_status
3470 , x_msg_count
3471 , x_msg_data
3472 );
3473
3474 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3475 THEN
3476 GMI_RESERVATION_UTIL.println('Error returned by Create_Pending_Transaction');
3477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3478 END IF;
3479 l_trans_rec.trans_id := l_trans_row.trans_id;
3480 GMI_Reservation_Util.PrintLn('created trans trans_id'||l_trans_rec.trans_id);
3481 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3482 (l_trans_rec, l_trans_rec )
3483 THEN
3484 GMI_RESERVATION_UTIL.balance_default_lot
3485 ( p_ic_default_rec => l_trans_rec
3486 , p_opm_item_id => l_trans_rec.item_id
3487 , x_return_status => x_return_status
3488 , x_msg_count => x_msg_count
3489 , x_msg_data => x_msg_data
3490 );
3491 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3492 THEN
3493 GMI_RESERVATION_UTIL.PrintLn('cancle move order Error returned by balancing default lot');
3494 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3495 END IF;
3496 END IF;
3497 END IF;
3498 END IF;
3499 END IF;
3500 ELSE
3501 --l_deleted_quantity := requested_qty;
3502 null;
3503 END IF;
3504
3505 -- Reduce move order quantity to only include existing allocations
3506 IF l_quantity > l_quantity_detailed THEN
3507 l_quantity := l_quantity_detailed;
3508 END IF;
3509
3510 -- If no allocations exist, close move order line
3511 IF l_quantity <= 0 OR
3512 l_quantity_detailed <= 0 THEN
3513 l_quantity := 0;
3514 l_quantity2 := 0;
3515 l_line_status := 5;
3516 GMI_Reservation_Util.PrintLn('No allocations. Closing MO line');
3517 ELSE
3518 -- If all of the quantity for the move order line was deleted,
3519 -- close the move order line
3520 If l_deleted_quantity >= l_quantity Then
3521 l_quantity := 0;
3522 l_quantity2 := 0;
3523 l_line_status := 5;
3524 GMI_Reservation_Util.PrintLn('Closing MO Line');
3525 Else
3526 l_quantity := l_quantity - l_deleted_quantity;
3527 l_quantity2 := l_quantity2 - l_deleted_quantity2;
3528 l_line_status := 9;
3529 GMI_Reservation_Util.PrintLn('Canceling MO Line New qty = ' || l_quantity);
3530 GMI_Reservation_Util.PrintLn('Canceling MO Line New qty2 = ' || l_quantity2);
3531 End If;
3532 END IF;
3533 -- Update line status, quantity, and required_quantity
3534 UPDATE ic_txn_request_lines
3535 SET quantity = l_quantity
3536 ,secondary_quantity = l_quantity2
3537 ,line_status = l_line_status
3538 WHERE line_id = p_line_id;
3539
3540 x_return_status := fnd_api.g_ret_sts_success;
3541 GMI_Reservation_Util.PrintLn('Return status = ' || x_return_status);
3542
3543
3544
3545 EXCEPTION
3546 WHEN fnd_api.g_exc_error THEN
3547 ROLLBACK TO CANCELMO_SP;
3548 x_return_status := fnd_api.g_ret_sts_error;
3549 GMI_Reservation_Util.PrintLn('Return status = ' || x_return_status);
3550 WHEN fnd_api.g_exc_unexpected_error THEN
3551 ROLLBACK TO CANCELMO_SP;
3552 x_return_status := fnd_api.g_ret_sts_unexp_error;
3553 GMI_Reservation_Util.PrintLn('Return status = ' || x_return_status);
3554 WHEN OTHERS THEN
3555 ROLLBACK TO CANCELMO_SP;
3556 x_return_status := fnd_api.g_ret_sts_unexp_error;
3557 GMI_Reservation_Util.PrintLn('Other error in Cancel_Move_Order_line');
3558 GMI_Reservation_Util.PrintLn('Error Code = ' || SQLCODE);
3559 GMI_Reservation_Util.PrintLn('Error Msg:' || SQLERRM);
3560 GMI_Reservation_Util.PrintLn('Return status = ' || x_return_status);
3561 END Cancel_Move_Order_Line;
3562
3563 --Procedure
3564 -- Reduce_Move_Order_Quantity
3565 --Description
3566 -- This procedure is called from Shipping when the quantity on a
3567 -- sales order line is reduced, leading to the quantity on a delivery
3568 -- detail being reduced. This procedure reduces the quantity
3569 -- column on the move order line by p_reduction_quantity. The required
3570 -- quantity is the quantity needed by shipping to fulfill the sales order.
3571 -- Any quantity transacted for this move order line in excess of the
3572 -- required_quantity will be moved to staging, but will not be
3573 -- reserved or shipped to the customer. Since the
3574 -- sales order line quantity has been reduced, the reservation quantity
3575 -- for the sales order should also be reduced. Some reservations are
3576 -- reduced here, and some are reduced in Finalize_Pick_Confirm
3577 -- (INVVTROB.pls).
3578 -- If WMS is installed, undispatched tasks may be deleted, since these
3579 -- tasks are no longer necessary.
3580 -- Parameters
3581 -- p_line_id: The move order line id to be cancelled
3582 -- p_reduction_quantity: How much to reduce the required
3583 -- quantity by
3584 -- p_txn_source_line_Id: The sales order line id. If this
3585 -- parameter is not passed in, we get it from the delivery detail.
3586 --
3587 /* for OPM, there is no concept of required_quantity, the quantity in the
3588 sense is the required qty and would be passed to shipping. So when user cancels
3589 qty in OM, the delivery detail(s) would be reduced by the p_reduction_quantity
3590 as well as the move order column quantity.
3591 if the p_reduction_quantity <= (quantity-quantity_detailed), no need to remove
3592 any inv transactions
3593 else
3594 need to remove inv trans qtys by the amount of p_reduction_quantity -
3595 quantity-quantity_detailed
3596 OPM does not support WMS yet. So this case is not considered
3597 */
3598 PROCEDURE Reduce_Move_Order_Quantity(
3599 x_return_status OUT NOCOPY VARCHAR2
3600 ,x_msg_count OUT NOCOPY NUMBER
3601 ,x_msg_data OUT NOCOPY VARCHAR2
3602 ,p_line_id IN NUMBER
3603 ,p_delivery_detail_id IN NUMBER
3604 ,p_reduction_quantity IN NUMBER
3605 ,p_reduction_quantity2 IN NUMBER
3606 ,p_txn_source_line_id IN NUMBER DEFAULT NULL)
3607 IS
3608 l_quantity NUMBER;
3609 l_quantity2 NUMBER;
3610 l_quantity_detailed NUMBER;
3611 l_quantity2_detailed NUMBER;
3612 l_organization_id NUMBER;
3613 l_transaction_temp_id NUMBER;
3614 l_task_qty NUMBER;
3615 l_return_status VARCHAR2(1);
3616 l_deleted_quantity NUMBER;
3617 l_deleted_quantity2 NUMBER;
3618 l_reservation_id NUMBER;
3619 l_primary_quantity NUMBER;
3620 l_rsv_count NUMBER;
3621 l_quantity_to_delete NUMBER;
3622 l_quantity2_to_delete NUMBER;
3623 l_txn_source_line_id NUMBER;
3624 l_reduction_quantity NUMBER;
3625 l_error_code NUMBER;
3626 l_mo_uom_code VARCHAR2(3);
3627 l_primary_uom_code VARCHAR2(3);
3628 l_inventory_Item_id NUMBER;
3629 l_prim_quantity_to_delete NUMBER;
3630 l_remaining_quantity NUMBER;
3631 l_trans_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
3632 l_trans_row ic_tran_pnd%ROWTYPE;
3633
3634 cursor c_line_info IS
3635 SELECT quantity
3636 , secondary_quantity
3637 , NVL(quantity_detailed, 0)
3638 , NVL(secondary_quantity_detailed, 0)
3639 , organization_id
3640 , inventory_item_Id
3641 , uom_code
3642 FROM ic_txn_request_lines
3643 WHERE line_id = p_line_id
3644 FOR UPDATE;
3645
3646 cursor c_primary_uom IS
3647 SELECT primary_uom_code
3648 FROM mtl_system_items
3649
3650 WHERE organization_Id = l_organization_id
3651 AND inventory_Item_Id = l_inventory_Item_Id;
3652
3653 cursor c_txn_source_line IS
3654 SELECT source_line_id
3655 FROM wsh_delivery_details
3656 WHERE move_order_line_id IS NOT NULL
3657 AND move_order_line_id = p_line_id
3658 AND released_status = 'S';
3659
3660 cursor c_reservations IS
3661 SELECT trans_id
3662 FROM ic_tran_pnd
3663 WHERE line_id = l_txn_source_line_id
3664 AND (line_detail_id = p_delivery_detail_id
3665 or nvl(line_detail_id, -1 ) = -1 )
3666 AND staged_ind = 0
3667 AND delete_mark = 0
3668 AND doc_type = 'OMSO'
3669 AND trans_qty <> 0
3670 AND (lot_id > 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT)
3671 -- Bug 3962760 - exclude the default transaction.
3672 ORDER BY line_detail_id desc,
3673 trans_qty desc; /* the smaller qty is at the top, keep in mind it is neg */
3674 /* should we do it by the alloc rules? auto-fifo, fefo? */
3675
3676 cursor find_default_lot IS
3677 SELECT trans_id
3678 FROM ic_tran_pnd
3679 WHERE line_id = l_txn_source_line_id
3680 AND doc_type = 'OMSO'
3681 AND delete_mark = 0
3682 AND (lot_id = 0 OR location <> GMI_RESERVATION_UTIL.G_DEFAULT_LOCT);
3683
3684 BEGIN
3685 GMI_Reservation_Util.PrintLn('entering reduce move order ');
3686 gmi_reservation_util.println('Value of p_line_id is '||p_line_id);
3687 gmi_reservation_util.println('Value of p_txn_source_line_id is '||p_txn_source_line_id);
3688 gmi_reservation_util.println('Value of p_delivery_detail_id is is '||p_delivery_detail_id);
3689 gmi_reservation_util.println('Value of p_reduction_quantity is '||p_reduction_quantity);
3690
3691 --SAVEPOINT REDUCEMO_SP;
3692
3693 IF p_reduction_quantity <= 0 THEN
3694 RETURN;
3695 END IF;
3696
3697 l_deleted_quantity := 0;
3698 l_deleted_quantity2 := 0;
3699
3700 --query mo line info
3701 OPEN c_line_info;
3702 FETCH c_line_info
3703 INTO l_quantity
3704 , l_quantity2
3705 ,l_quantity_detailed
3706 ,l_quantity2_detailed
3707 ,l_organization_id
3708 ,l_inventory_Item_id
3709 ,l_mo_uom_code;
3710 IF c_line_info%NOTFOUND THEN
3711 GMI_Reservation_Util.PrintLn('Move order line not found');
3712
3713 RAISE fnd_api.g_exc_error;
3714 END IF;
3715
3716 CLOSE c_line_info;
3717
3718
3719 l_reduction_quantity := p_reduction_quantity;
3720 l_remaining_quantity := l_quantity - l_quantity_detailed;
3721 l_quantity_to_delete := -l_reduction_quantity + l_remaining_quantity;
3722 l_quantity2_to_delete := -p_reduction_quantity2 + (l_quantity2 - l_quantity2_detailed);
3723
3724 gmi_reservation_util.println('Value of l_reduction_quantity is '||l_reduction_quantity);
3725 gmi_reservation_util.println('Value of l_remaining_quantity is '||l_remaining_quantity);
3726 gmi_reservation_util.println('Value of l_quantity_to_delete is '||l_quantity_to_delete);
3727
3728 -- Call Cancel MO Line when reduction qu antity is greater than
3729 -- required quantity or quantity
3730 IF l_reduction_quantity >= l_quantity THEN
3731 gmi_reservation_util.println('Going to call cancel_move_order_line in reduce');
3732 cancel_move_order_line(
3733 x_return_status => l_return_status
3734 ,x_msg_count => x_msg_count
3735 ,x_msg_data => x_msg_data
3736 ,p_line_id => p_line_id
3737 ,p_delivery_detail_id => p_delivery_detail_id
3738 ,p_delete_reservations => 'Y'
3739 ,p_txn_source_line_id => p_txn_source_line_id);
3740
3741 IF l_return_status = fnd_api.g_ret_sts_error Then
3742 gmi_reservation_util.println('Error coming back from cancel_move order in reduce_move_qty');
3743 RAISE fnd_api.g_exc_error;
3744 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error Then
3745 gmi_reservation_util.println('Error coming back from cancel_move but this is different');
3746 RAISE fnd_api.g_exc_unexpected_error;
3747 END IF;
3748 END IF;
3749
3750 /* need to delete or reduce some of the inv transactions*/
3751 IF l_quantity_to_delete > 0 THEN
3752 /* move order uom should be always primary uom */
3753 /*If l_primary_uom_code <> l_mo_uom_code Then
3754 null;
3755 l_prim_quantity_to_delete :=
3756 GMI_Reservation_Util.get_opm_converted_qty(
3757 p_apps_item_id => l_inventory_item_id,
3758 p_organization_id => l_organization_id,
3759 p_apps_from_uom => l_uom_code
3760 p_apps_to_uom => c.requested_quantity_uom,
3761 p_original_qty => p_changed_attributes(l_counter).ordered_quantity);
3762
3763 Else
3764 l_prim_quantity_to_delete := l_quantity_to_delete;
3765 End If;*/
3766
3767 -- HW No need to close this cursor. It was never opened
3768 -- CLOSE c_txn_source_line;
3769 -- we query by the sales order line id. If that value is not
3770 -- passed in, we need to get it from shipping table
3771 If p_txn_source_line_id IS NOT NULL Then
3772 gmi_reservation_util.println('Assigning value for l_txn_source_line_id');
3773 l_txn_source_line_id := p_txn_source_line_id;
3774 Else
3775 gmi_reservation_util.println('Going to fetch c_txn_source_line');
3776 OPEN c_txn_source_line;
3777 FETCH c_txn_source_line INTO l_txn_source_line_id;
3778 if c_txn_source_line%NOTFOUND then
3779 CLOSE c_txn_source_line;
3780 GMI_Reservation_Util.PrintLn('Did not find any sales order line');
3781 RAISE no_data_found;
3782 end if;
3783 CLOSE c_txn_source_line;
3784 End If;
3785
3786 OPEN c_reservations;
3787 LOOP
3788 EXIT WHEN l_quantity_to_delete <= 0;
3789
3790 FETCH c_reservations INTO l_reservation_id;
3791 EXIT WHEN c_reservations%NOTFOUND;
3792
3793 l_trans_rec.trans_id := l_reservation_id;
3794 gmi_reservation_util.println('Going to fetch record from ic in reduce_move');
3795 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3796 (l_trans_rec, l_trans_rec )
3797 THEN
3798 GMI_Reservation_Util.PrintLn('trans_qty = ' || l_trans_rec.trans_qty);
3799 /* may have to consider indivisible */
3800 IF abs(l_trans_rec.trans_qty) <= l_quantity_to_delete THEN
3801 -- if so, simply delete the rsv , will balance default later
3802 gmi_reservation_util.println('Going to delete tran in reduceMove');
3803 GMI_TRANS_ENGINE_PUB.DELETE_PENDING_TRANSACTION
3804 ( 1
3805 , FND_API.G_FALSE
3806 , FND_API.G_FALSE
3807 , FND_API.G_VALID_LEVEL_FULL
3808 , l_trans_rec
3809 , l_trans_row
3810 , x_return_status
3811 , x_msg_count
3812 , x_msg_data
3813 );
3814 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3815 THEN
3816 GMI_RESERVATION_UTIL.PrintLn('Error returned by Delete_Pending_Transaction');
3817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3818 END IF;
3819 l_deleted_quantity := l_deleted_quantity + abs(l_trans_rec.trans_qty);
3820 l_deleted_quantity2:= l_deleted_quantity2 + abs(l_trans_rec.trans_qty2);
3821 ELSE
3822 gmi_reservation_util.println('In ELSE and will be updateing ic_tran');
3823 l_trans_rec.trans_qty := -1 * (abs(l_trans_rec.trans_qty) - l_quantity_to_delete);
3824 l_trans_rec.trans_qty2:= -1 * (abs(l_trans_rec.trans_qty2) - l_quantity2_to_delete);
3825 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION
3826 ( 1
3827 , FND_API.G_FALSE
3828 , FND_API.G_FALSE
3829 , FND_API.G_VALID_LEVEL_FULL
3830 , l_trans_rec
3831 , l_trans_row
3832 , x_return_status
3833 , x_msg_count
3834 , x_msg_data
3835 );
3836 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3837 THEN
3838 GMI_RESERVATION_UTIL.println('Error returned by Update_Pending_Transaction');
3839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3840 END IF;
3841 l_deleted_quantity := l_deleted_quantity + l_quantity_to_delete;
3842 l_deleted_quantity2 := l_deleted_quantity2 + l_quantity2_to_delete;
3843 END IF;
3844 END IF;
3845 l_quantity_to_delete := l_quantity_to_delete - l_deleted_quantity;
3846 l_quantity2_to_delete := l_quantity2_to_delete - l_deleted_quantity2;
3847 END LOOP;
3848 CLOSE c_reservations;
3849
3850 GMI_RESERVATION_UTIL.find_default_lot
3851 ( x_return_status => x_return_status,
3852 x_msg_count => x_msg_count,
3853 x_msg_data => x_msg_data,
3854 x_reservation_id => l_reservation_id,
3855 p_line_id => l_txn_source_line_id
3856 );
3857
3858 IF nvl(l_reservation_id,0) > 0 THEN -- no balancing if no default exist
3859 l_trans_rec.trans_id := l_reservation_id;
3860 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
3861 (l_trans_rec, l_trans_rec )
3862 THEN
3863 GMI_RESERVATION_UTIL.balance_default_lot
3864 ( p_ic_default_rec => l_trans_rec
3865 , p_opm_item_id => l_trans_rec.item_id
3866 , x_return_status => x_return_status
3867 , x_msg_count => x_msg_count
3868 , x_msg_data => x_msg_data
3869 );
3870 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3871 THEN
3872 GMI_RESERVATION_UTIL.PrintLn('cancle move order Error returned by balancing default lot');
3873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3874 END IF;
3875 END IF;
3876 END IF;
3877 END IF;
3878
3879 gmi_reservation_util.println('Value of p_line_id before update is '||p_line_id);
3880 l_quantity := l_quantity - l_reduction_quantity;
3881 l_quantity2 := l_quantity2 - p_reduction_quantity2;
3882
3883 GMI_Reservation_Util.PrintLn('updating move order line with qty '||l_quantity);
3884 gmi_reservation_util.println('For line id '||p_line_id);
3885 -- Update line status, quantity, and required_quantity
3886 UPDATE ic_txn_request_lines
3887 SET quantity = l_quantity
3888 , primary_quantity = l_quantity
3889 , secondary_quantity = l_quantity2
3890 , quantity_detailed = quantity_detailed - l_deleted_quantity
3891 , secondary_quantity_detailed = secondary_quantity_detailed - l_deleted_quantity2
3892 WHERE line_id = p_line_id;
3893
3894 x_return_status := fnd_api.g_ret_sts_success;
3895
3896 EXCEPTION
3897 WHEN fnd_api.g_exc_error THEN
3898 --ROLLBACK TO REDUCEMO_SP;
3899 x_return_status := fnd_api.g_ret_sts_error;
3900 WHEN OTHERS THEN
3901 --ROLLBACK TO REDUCEMO_SP;
3902 GMI_Reservation_Util.PrintLn('Others error' || Sqlerrm);
3903 x_return_status := fnd_api.g_ret_sts_unexp_error;
3904 END Reduce_Move_Order_Quantity;
3905
3906 --Update_Txn_Source_Line
3907 --
3908 -- This procedure updates the move order line indicated by p_line_id
3909 -- with a new transaction source line id (p_new_source_line_id).
3910 -- It also updates all of the allocation lines with the new source line id.
3911 -- This procedure is called from Shipping when the delivery detail is split
3912 -- after pick release has occurred, but before pick confirm.
3913 -- logic is the same as inv file INVVTROB.pls
3914
3915 PROCEDURE update_txn_source_line
3916 ( p_line_id IN NUMBER
3917 , p_new_source_line_id IN NUMBER
3918 ) IS
3919 BEGIN
3920 UPDATE ic_txn_request_lines
3921 SET txn_source_line_id = p_new_source_line_id
3922 WHERE line_id = p_line_id;
3923
3924 /*UPDATE mtl_material_transactions_temp
3925 SET trx_source_line_id = p_new_source_line_id
3926 WHERE move_order_line_id = p_line_id;*/
3927 END update_txn_source_line;
3928
3929
3930 END GMI_MOVE_ORDER_LINE_UTIL;