[Home] [Help]
PACKAGE BODY: APPS.INV_MWB_CG_TRANSFER
Source
1 PACKAGE BODY INV_MWB_CG_TRANSFER AS
2 /* $Header: INVCGTRB.pls 120.2 2005/10/10 12:53:54 methomas noship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'INV_MWB_CG_TRANSFER';
5
6
7 PROCEDURE mdebug(msg in varchar2) IS
8
9 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
10 BEGIN
11 -- dbms_output.put_line(msg);
12 null;
13 END;
14
15 PROCEDURE TRANSFER(
16 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
17 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
18 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
19 X_TRANSACTION_HEADER_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
20 P_IS_REVISION_CONTROLLED IN VARCHAR2,
21 P_IS_LOT_CONTROLLED IN VARCHAR2,
22 P_IS_SERIAL_CONTROLLED IN VARCHAR2,
23 P_ORG_ID IN NUMBER,
24 P_INVENTORY_ITEM_ID IN NUMBER,
25 P_REVISION IN VARCHAR2,
26 P_SUBINVENTORY IN VARCHAR2,
27 P_LOCATOR_ID IN NUMBER,
28 P_LPN_ID IN NUMBER,
29 P_LOT_NUMBER IN VARCHAR2,
30 P_EXP_DATE IN DATE,
31 P_SERIAL_NUMBER IN VARCHAR2,
32 P_ONHAND IN NUMBER,
33 P_AVAILABILITY IN NUMBER,
34 P_UOM IN VARCHAR2,
35 P_PRIMARY_UOM IN VARCHAR2,
36 P_COSTGROUP_ID IN NUMBER,
37 P_XFR_COSTGROUP_ID IN NUMBER,
38 P_USER_ID IN NUMBER
39 ) IS
40 L_RETURN_STATUS NUMBER := 0;
41 L_PROC_MSG VARCHAR2(2000);
42 L_TRANSACTION_TEMP_ID NUMBER;
43 L_SERIAL_TRANSACTION_TEMP_ID NUMBER;
44 L_PRIMARY_QUANTITY NUMBER := P_ONHAND;
45 L_EXP_DATE DATE := P_EXP_DATE;
46 L_ONHAND NUMBER := P_ONHAND;
47
48 /*
49 ** For Cost Group Transfer
50 ** TRANSACTION_TYPE_ID = 86
51 ** TRANSACTION_SOURCE_TYPE_ID = 13
52 ** TRANSACTION_ACTION = 55
53 */
54
55 L_TRANSACTION_TYPE_ID NUMBER := 86;
56 L_TRANSACTION_SOURCE_TYPE_ID NUMBER := 13;
57 L_TRANSACTION_ACTION_ID NUMBER := 55;
58
59 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
60 BEGIN
61
62 IF P_IS_SERIAL_CONTROLLED = 'Y' THEN
63 L_ONHAND := 1;
64 END IF;
65
66 IF P_UOM <> P_PRIMARY_UOM THEN
67
68 L_PRIMARY_QUANTITY := INV_CONVERT.INV_UM_CONVERT(
69 ITEM_ID => P_INVENTORY_ITEM_ID,
70 PRECISION => NULL,
71 FROM_QUANTITY => L_ONHAND,
72 FROM_UNIT => P_UOM,
73 TO_UNIT => P_PRIMARY_UOM,
74 FROM_NAME => NULL,
75 TO_NAME => NULL
76 );
77
78 END IF;
79
80 /*
81 LINE_INSERT_TRX creates a transaction_temp_id for a transaction.
82
83 X_TRANSACTION_HEADER_ID is an IN/OUT parameter representing the
84 transaction header id used for the transaction.
85
86 When X_TRANSACTION_HEADER_ID is passed as null, LINE_INSERT_TRX
87 uses the newly created transaction_temp_id as the transaction header id
88 for the transaction.
89
90 When X_TRANSACTION_HEADER_ID is passed a value, LINE_INSERT_TRX
91 uses this value as the transaction header id for the transaction.
92
93 So for the first transaction in the group, X_TRANSACTION_HEADER_ID is
94 passed as null to LINE_INSERT_TRX. For subsequent calls, the value is
95 available and is used.
96 */
97
98
99 SAVEPOINT TRX_BEGIN;
100
101 L_RETURN_STATUS := INV_TRX_UTIL_PUB.INSERT_LINE_TRX(
102 p_trx_hdr_id => X_TRANSACTION_HEADER_ID,
103 p_item_id => P_INVENTORY_ITEM_ID,
104 p_revision => P_REVISION,
105 p_org_id => P_ORG_ID,
106 p_trx_action_id => L_TRANSACTION_ACTION_ID,
107 p_subinv_code => P_SUBINVENTORY ,
108 p_tosubinv_code => NULL,
109 p_locator_id => P_LOCATOR_ID,
110 p_tolocator_id => NULL,
111 p_xfr_org_id => NULL,
112 p_trx_type_id => L_TRANSACTION_TYPE_ID,
113 p_trx_src_type_id => L_TRANSACTION_SOURCE_TYPE_ID,
114 p_trx_qty => L_ONHAND,
115 p_pri_qty => L_PRIMARY_QUANTITY,
116 p_uom => P_UOM,
117 p_date => SYSDATE,
118 p_reason_id => NULL,
119 p_user_id => P_USER_ID,
120 p_frt_code => NULL,
121 p_ship_num => NULL,
122 p_dist_id => NULL,
123 p_way_bill => NULL,
124 p_exp_arr => NULL,
125 p_cost_group => P_COSTGROUP_ID,
126 p_from_lpn_id => P_LPN_ID,
127 p_cnt_lpn_id => NULL,
128 p_xfr_lpn_id => P_LPN_ID,
129 p_trx_src_id => NULL,
130 x_trx_tmp_id => L_TRANSACTION_TEMP_ID,
131 x_proc_msg => L_PROC_MSG,
132 p_xfr_cost_group => P_XFR_COSTGROUP_ID,
133 p_completion_trx_id => NULL,
134 p_flow_schedule => NULL,
135 p_trx_cost => NULL
136 );
137
138 IF L_RETURN_STATUS <> 0 THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141
142 IF P_IS_LOT_CONTROLLED = 'Y' AND P_IS_SERIAL_CONTROLLED <> 'Y' THEN
143
144 IF L_EXP_DATE IS NULL THEN
145
146 SELECT EXPIRATION_DATE
147 INTO L_EXP_DATE
148 FROM MTL_LOT_NUMBERS_ALL_V
149 WHERE ORGANIZATION_ID = P_ORG_ID
150 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
151 AND LOT_NUMBER = P_LOT_NUMBER;
152
153 END IF;
154
155 L_RETURN_STATUS := INV_TRX_UTIL_PUB.INSERT_LOT_TRX(
156 p_trx_tmp_id => L_TRANSACTION_TEMP_ID,
157 p_user_id => P_USER_ID,
158 p_lot_number => P_LOT_NUMBER,
159 p_trx_qty => L_ONHAND,
160 p_pri_qty => L_PRIMARY_QUANTITY,
161 x_ser_trx_id => L_SERIAL_TRANSACTION_TEMP_ID,
162 x_proc_msg => L_PROC_MSG,
163 p_exp_date => L_EXP_DATE
164 );
165
166 IF L_RETURN_STATUS <> 0 THEN
167
168 FND_MESSAGE.SET_NAME('INV','INV_LOT_COMMIT_FAILURE');
169 FND_MSG_PUB.ADD;
170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
171
172 END IF;
173
174 END IF; -- for Only Lot Controlled Items
175
176 IF P_IS_LOT_CONTROLLED = 'Y' AND P_IS_SERIAL_CONTROLLED = 'Y' THEN
177
178 L_RETURN_STATUS := INV_TRX_UTIL_PUB.INSERT_LOT_TRX(
179 p_trx_tmp_id => L_TRANSACTION_TEMP_ID,
180 p_user_id => P_USER_ID,
181 p_lot_number => P_LOT_NUMBER,
182 p_trx_qty => 1,
183 p_pri_qty => 1,
184 x_ser_trx_id => L_SERIAL_TRANSACTION_TEMP_ID,
185 x_proc_msg => L_PROC_MSG,
186 p_exp_date => NULL
187 );
188
189 IF L_RETURN_STATUS <> 0 THEN
190
191 FND_MESSAGE.SET_NAME('INV','INV_LOT_COMMIT_FAILURE');
192 FND_MSG_PUB.ADD;
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
194
195 END IF;
196
197 L_RETURN_STATUS := INV_TRX_UTIL_PUB.INSERT_SER_TRX(
198 p_trx_tmp_id => L_SERIAL_TRANSACTION_TEMP_ID,
199 p_user_id => P_USER_ID,
200 p_fm_ser_num => P_SERIAL_NUMBER,
201 p_to_ser_num => P_SERIAL_NUMBER,
202 x_proc_msg => L_PROC_MSG
203 );
204
205 IF L_RETURN_STATUS <> 0 THEN
206
207 FND_MESSAGE.SET_NAME('INV','INV_SERIAL_COMMIT_FAILURE');
208 FND_MSG_PUB.ADD;
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210
211 END IF;
212
213 END IF; -- for Lot and Serial Controlled Items
214
215 IF P_IS_LOT_CONTROLLED <> 'Y' AND P_IS_SERIAL_CONTROLLED = 'Y' THEN
216
217 L_RETURN_STATUS := INV_TRX_UTIL_PUB.INSERT_SER_TRX(
218 p_trx_tmp_id => L_TRANSACTION_TEMP_ID,
219 p_user_id => P_USER_ID,
220 p_fm_ser_num => P_SERIAL_NUMBER,
221 p_to_ser_num => P_SERIAL_NUMBER,
222 x_proc_msg => L_PROC_MSG
223 );
224
225 IF L_RETURN_STATUS <> 0 THEN
226
227 FND_MESSAGE.SET_NAME('INV','INV_SERIAL_COMMIT_FAILURE');
228 FND_MSG_PUB.ADD;
229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230
231 END IF;
232
233 END IF; -- Only Serial Controlled Items
234
235 IF X_TRANSACTION_HEADER_ID IS NULL THEN
236 X_TRANSACTION_HEADER_ID := L_TRANSACTION_TEMP_ID;
237 END IF;
238
239 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
240
241 EXCEPTION
242
243 WHEN FND_API.G_EXC_ERROR THEN
244
245 ROLLBACK TO TRX_BEGIN;
246 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
247 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250
251 ROLLBACK TO TRX_BEGIN;
252 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
253 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
254
255 WHEN OTHERS THEN
256
257 ROLLBACK TO TRX_BEGIN;
258 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
259 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
260 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'TRANSFER');
261 END IF;
262 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
263
264 END TRANSFER;
265
266 PROCEDURE VALIDATE(
267 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
268 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
269 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
270 X_ONHAND IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
271 X_AVAILABILITY IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
272 P_IS_REVISION_CONTROLLED IN VARCHAR2,
273 P_IS_LOT_CONTROLLED IN VARCHAR2,
274 P_IS_SERIAL_CONTROLLED IN VARCHAR2,
275 P_ORG_ID IN NUMBER,
276 P_INVENTORY_ITEM_ID IN NUMBER,
277 P_REVISION IN VARCHAR2,
278 P_SUBINVENTORY IN VARCHAR2,
279 P_LOCATOR_ID IN NUMBER,
280 P_LPN_ID IN NUMBER,
281 P_LOT_NUMBER IN VARCHAR2,
282 P_SERIAL_NUMBER IN VARCHAR2,
283 P_IS_LPN_REQUIRED IN VARCHAR2,
284 P_COST_GROUP_ID IN NUMBER
285 ) IS
286
287 L_RETURN_STATUS VARCHAR2(1);
288 L_MSG_DATA VARCHAR2(2000);
289 L_MSG_COUNT NUMBER;
290 L_CG_ID NUMBER := P_COST_GROUP_ID;
291 L_LPN_QOH NUMBER;
292 L_QOH NUMBER;
293 L_PQOH NUMBER := 0;
294 L_RQOH NUMBER;
295 L_QR NUMBER;
296 L_QS NUMBER;
297 L_ATT NUMBER;
298 L_ATR NUMBER;
299 L_IS_LOT_CONTROLLED BOOLEAN := FALSE;
300 L_IS_REVISION_CONTROLLED BOOLEAN := FALSE;
301 L_IS_SERIAL_CONTROLLED BOOLEAN := FALSE;
302 L_V_IS_LOT_CONTROLLED VARCHAR2(5) := 'FALSE';
303 L_V_IS_REVISION_CONTROLLED VARCHAR2(5) := 'FALSE';
304 L_V_IS_SERIAL_CONTROLLED VARCHAR2(5) := 'FALSE';
305 L_TREE_ID NUMBER;
306 L_PROJ_ID NUMBER;
307 /*
308 ** For Cost Group Transfer
309 ** TRANSACTION_TYPE_ID = 86
310 ** TRANSACTION_SOURCE_TYPE_ID = 13
311 ** TRANSACTION_ACTION = 55
312 */
313
314 L_TRANSACTION_TYPE_ID NUMBER := 86;
315 L_TRANSACTION_SOURCE_TYPE_ID NUMBER := 13;
316
317 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
318 BEGIN
319
320 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
321
322 IF P_IS_REVISION_CONTROLLED = 'Y' THEN
323 L_IS_REVISION_CONTROLLED := TRUE;
324 L_V_IS_REVISION_CONTROLLED := 'TRUE';
325 END IF;
326
327 IF P_IS_SERIAL_CONTROLLED = 'Y' THEN
328 L_IS_SERIAL_CONTROLLED := TRUE;
329 L_V_IS_SERIAL_CONTROLLED := 'TRUE';
330 END IF;
331
332 IF P_IS_LOT_CONTROLLED = 'Y' THEN
333 L_IS_LOT_CONTROLLED := TRUE;
334 L_V_IS_LOT_CONTROLLED := 'TRUE';
335 END IF;
336
337 IF P_ORG_ID IS NULL OR P_INVENTORY_ITEM_ID IS NULL OR P_SUBINVENTORY IS NULL OR
338 P_LOCATOR_ID IS NULL OR P_COST_GROUP_ID IS NULL
339 THEN
340
341 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_CGU_REQ');
342 FND_MSG_PUB.ADD;
343 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
344
345 END IF;
346
347 IF P_IS_REVISION_CONTROLLED = 'Y' AND P_REVISION IS NULL THEN
348
349 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_TRN_EXP');
350 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_REV'));
351 FND_MSG_PUB.ADD;
352 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
353
354 END IF;
355
356 IF P_IS_LOT_CONTROLLED = 'Y' AND P_LOT_NUMBER IS NULL THEN
357
358 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_TRN_EXP');
359 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_LOT'));
360 FND_MSG_PUB.ADD;
361 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
362
363 END IF;
364
365 IF P_IS_SERIAL_CONTROLLED = 'Y' AND P_SERIAL_NUMBER IS NULL THEN
366
367 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_TRN_EXP');
368 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_SER'));
369 FND_MSG_PUB.ADD;
370 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
371
372 END IF;
373
374 IF P_IS_LPN_REQUIRED = 'Y' AND P_LPN_ID IS NULL THEN
375
376 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_TRN_EXP');
377 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_LPN'));
378 FND_MSG_PUB.ADD;
379 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
380
381 END IF;
382
383 /* Bug no 2925346 to disallow the cost group transfers
384 for the cost group material*/
385 IF (X_RETURN_STATUS NOT LIKE 'E')
386 THEN
387 SELECT segment19 INTO L_PROJ_ID FROM MTL_ITEM_LOCATIONS
388 WHERE INVENTORY_LOCATION_ID=p_locator_id;
389
390 IF L_PROJ_ID IS NOT NULL
391 THEN
392
393 FND_MESSAGE.SET_NAME('INV','INV_PRJ_CG_XFR_DISALLOWED');
394 fnd_msg_pub.add;
395 X_RETURN_STATUS:=FND_API.G_RET_STS_ERROR;
396 END IF;
397 END IF;
398
399 IF P_ORG_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL AND
400 P_SUBINVENTORY IS NOT NULL THEN
401
402 L_RETURN_STATUS := INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE(
403 'TRUE',
404 NULL,
405 L_TRANSACTION_TYPE_ID,
406 NULL,
407 NULL,
408 P_ORG_ID,
409 P_INVENTORY_ITEM_ID,
410 P_SUBINVENTORY,
411 NULL,
412 NULL,
413 NULL,
414 'Z'
415 );
416 IF L_RETURN_STATUS = 'N' THEN
417
418 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_STATUS_NA');
419 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('INV','INV_SUBINV'));
420 FND_MSG_PUB.ADD;
421 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
422
423 END IF;
424
425 END IF; -- Subinventory Status Check
426
427 IF P_ORG_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL AND
428 P_SUBINVENTORY IS NOT NULL AND P_LOCATOR_ID IS NOT NULL THEN
429
430 L_RETURN_STATUS := INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE(
431 'TRUE',
432 NULL,
433 L_TRANSACTION_TYPE_ID,
434 NULL,
435 NULL,
436 P_ORG_ID,
437 P_INVENTORY_ITEM_ID,
438 P_SUBINVENTORY,
439 P_LOCATOR_ID,
440 NULL,
441 NULL,
442 'L'
443 );
444 IF L_RETURN_STATUS = 'N' THEN
445
446 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_STATUS_NA');
447 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_LOC'));
448 FND_MSG_PUB.ADD;
449 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
450
451 END IF;
452
453 END IF; -- Locator Status Check.
454
455 IF P_ORG_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL AND
456 P_LOT_NUMBER IS NOT NULL THEN
457
458 L_RETURN_STATUS := INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE(
459 'TRUE',
460 NULL,
461 L_TRANSACTION_TYPE_ID,
462 NULL,
463 NULL,
464 P_ORG_ID,
465 P_INVENTORY_ITEM_ID,
466 NULL,
467 NULL,
468 P_LOT_NUMBER,
469 NULL,
470 'O'
471 );
472 IF L_RETURN_STATUS = 'N' THEN
473
474 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_STATUS_NA');
475 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_LOT'));
476 FND_MSG_PUB.ADD;
477 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
478
479 END IF;
480
481 END IF; -- Lot Number Status Check.
482
483 IF P_ORG_ID IS NOT NULL AND P_INVENTORY_ITEM_ID IS NOT NULL AND
484 P_SERIAL_NUMBER IS NOT NULL THEN
485
486 L_RETURN_STATUS := INV_MATERIAL_STATUS_GRP.IS_STATUS_APPLICABLE(
487 'TRUE',
488 NULL,
489 L_TRANSACTION_TYPE_ID,
490 NULL,
491 NULL,
492 P_ORG_ID,
493 P_INVENTORY_ITEM_ID,
494 P_SUBINVENTORY,
495 P_LOCATOR_ID,
496 P_LOT_NUMBER,
497 P_SERIAL_NUMBER,
498 'A'
499 );
500 IF L_RETURN_STATUS = 'N' THEN
501
502 FND_MESSAGE.SET_NAME('WMS','WMS_ATT_STATUS_NA');
503 FND_MESSAGE.SET_TOKEN('TOKEN',FND_MESSAGE.GET_STRING('WMS','WMS_SER'));
504 FND_MSG_PUB.ADD;
505 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
506
507 END IF;
508
509 END IF; -- Serial Number Status Check.
510
511 IF X_RETURN_STATUS = FND_API.G_RET_STS_SUCCESS THEN
512
513 IF X_AVAILABILITY IS NULL THEN
514 /*Changed from serial and not controlled to only check if serial controlled bug3405867*/
515 IF L_IS_SERIAL_CONTROLLED THEN --AND NOT L_IS_LOT_CONTROLLED THEN
516 /*Bug fix 3405867 ends*/
517
518 /*
519 ** For serial controlled items always return 1 as onhand and available quantity
520 */
521
522 L_ATT := 1;
523 L_QOH := 1;
524
525 ELSIF P_IS_LPN_REQUIRED = 'Y' THEN
526
527 L_RETURN_STATUS := INV_TXN_VALIDATIONS.GET_IMMEDIATE_LPN_ITEM_QTY(
528 p_lpn_id => P_LPN_ID,
529 p_organization_id => P_ORG_ID,
530 p_source_type_id => L_TRANSACTION_SOURCE_TYPE_ID,
531 p_inventory_item_id => P_INVENTORY_ITEM_ID,
532 p_revision => P_REVISION,
533 p_locator_id => P_LOCATOR_ID,
534 p_subinventory_code => P_SUBINVENTORY,
535 p_lot_number => P_LOT_NUMBER,
536 p_is_revision_control => L_V_IS_REVISION_CONTROLLED,
537 p_is_serial_control => L_V_IS_SERIAL_CONTROLLED,
538 p_is_lot_control => L_V_IS_LOT_CONTROLLED,
539 x_transactable_qty => L_ATT,
540 x_qoh => L_LPN_QOH,
541 x_lpn_onhand => L_QOH,
542 x_return_msg => L_MSG_DATA
543 );
544 IF L_RETURN_STATUS = 'N' THEN
545
546 L_ATT := 0;
547 L_QOH := X_ONHAND;
548 FND_MESSAGE.SET_NAME('INV','INV_ERROR_FIND_LPN_QTY');
549 FND_MSG_PUB.ADD;
550 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
551
552 END IF;
553
554 ELSE
555
556 IF P_LOCATOR_ID IS NULL THEN
557 L_CG_ID := NULL;
558 END IF;
559 /* added as part of bug fix 2460413 */
560 /* INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES( */
561
562 /* Clear the quantity tree cache first to get the right values */
563 INV_QUANTITY_TREE_PUB.CLEAR_QUANTITY_CACHE();
564
565 /* CREATE THE QUANTITY TREE */
566 INV_QUANTITY_TREE_PVT.CREATE_TREE(p_api_version_number => 1.0,
567 x_return_status => L_RETURN_STATUS,
568 x_msg_count => L_MSG_COUNT,
569 x_msg_data => L_MSG_DATA,
570 p_organization_id => P_ORG_ID,
571 p_inventory_item_id => P_INVENTORY_ITEM_ID,
572 p_tree_mode => 3,
573 p_is_revision_control => L_IS_REVISION_CONTROLLED,
574 p_is_lot_control => L_IS_LOT_CONTROLLED,
575 p_is_serial_control => L_IS_SERIAL_CONTROLLED,
576 x_tree_id => L_TREE_ID
577 );
578
579 INV_QUANTITY_TREE_PVT.QUERY_TREE(
580 p_api_version_number => 1.0,
581 x_return_status => L_RETURN_STATUS,
582 x_msg_count => L_MSG_COUNT,
583 x_msg_data => L_MSG_DATA,
584 p_tree_id =>L_TREE_ID,
585 p_revision => P_REVISION,
586 p_lot_number => P_LOT_NUMBER,
587 p_subinventory_code => P_SUBINVENTORY,
588 p_locator_id => P_LOCATOR_ID,
589 p_cost_group_id => L_CG_ID,
590 p_transfer_subinventory_code =>P_SUBINVENTORY, -- Bug 2269454
591 x_qoh => L_QOH,
592 x_pqoh => L_PQOH,
593 x_rqoh => L_RQOH,
594 x_qr => L_QR,
595 x_qs => L_QS,
596 x_att => L_ATT,
597 x_atr => L_ATR
598 );
599 /* packed quantity should not be considered into onhand for loose items */
600 L_QOH:=NVL(L_QOH,0)-NVL(L_PQOH,0);
601 /* end of bug fix 2460413 */
602
603 IF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
604
605 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
606
607 END IF;
608
609 END IF; -- Is Serial controlled / Is LPN Required
610
611 IF NVL(L_ATT,0) <> L_QOH THEN
612
613 FND_MESSAGE.SET_NAME('WMS','WMS_COMMINGLE_WARN');
614 FND_MSG_PUB.ADD;
615 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
616
617 END IF;
618
619 END IF; -- Availability is null
620
621 END IF; -- Success so far.
622
623 X_AVAILABILITY := NVL(L_ATT,0);
624 X_ONHAND := NVL(L_QOH,0);
625
626 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
627
628 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
629
630 END IF;
631
632 EXCEPTION
633
634 WHEN FND_API.G_EXC_ERROR THEN
635
636 X_AVAILABILITY := NVL(X_AVAILABILITY,0);
637 X_ONHAND := NVL(X_ONHAND,0);
638 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
639 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
640
641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
642
643 X_AVAILABILITY := NVL(X_AVAILABILITY,0);
644 X_ONHAND := NVL(X_ONHAND,0);
645 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
646 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
647
648 WHEN OTHERS THEN
649
650 X_AVAILABILITY := NVL(X_AVAILABILITY,0);
651 X_ONHAND := NVL(X_ONHAND,0);
652 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
653 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
654 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'VALIDATE');
655 END IF;
656 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
657
658 END VALIDATE;
659
660 PROCEDURE UPDATE_QUANTITY_TREE(
661 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
662 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
663 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
664 P_IS_LOT_CONTROLLED IN VARCHAR2,
665 P_IS_SERIAL_CONTROLLED IN VARCHAR2,
666 P_IS_LPN_REQUIRED IN VARCHAR2,
667 P_ORG_ID IN NUMBER,
668 P_INVENTORY_ITEM_ID IN NUMBER,
669 P_REVISION IN VARCHAR2,
670 P_SUBINVENTORY IN VARCHAR2,
671 P_LOCATOR_ID IN NUMBER,
672 P_LOT_NUMBER IN VARCHAR2,
673 P_ONHAND IN NUMBER,
674 P_UOM IN VARCHAR2,
675 P_PRIMARY_UOM IN VARCHAR2,
676 P_COSTGROUP_ID IN NUMBER,
677 P_XFR_COSTGROUP_ID IN NUMBER
678 ) IS
679
680 L_MSG_COUNT NUMBER;
681 L_MSG_DATA VARCHAR2(2000);
682 L_RETURN_STATUS VARCHAR2(1);
683 L_PRIMARY_QUANTITY NUMBER;
684 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
685 L_LOT_NUMBER VARCHAR2(80);
686 L_COST_GROUP_ID NUMBER;
687 L_QOH NUMBER;
688 L_ATT NUMBER;
689 L_CONTAINERIZED NUMBER;
690
691 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
692 BEGIN
693
694 L_PRIMARY_QUANTITY := INV_CONVERT.INV_UM_CONVERT(
695 ITEM_ID => P_INVENTORY_ITEM_ID,
696 PRECISION => NULL,
697 FROM_QUANTITY => P_ONHAND,
698 FROM_UNIT => P_UOM,
699 TO_UNIT => P_PRIMARY_UOM,
700 FROM_NAME => NULL,
701 TO_NAME => NULL
702 );
703
704 IF P_IS_LOT_CONTROLLED = 'Y' THEN
705
706 L_LOT_NUMBER := P_LOT_NUMBER;
707
708 ELSE
709
710 L_LOT_NUMBER := NULL;
711
712 END IF;
713
714 IF P_IS_SERIAL_CONTROLLED = 'Y' THEN
715
716 L_PRIMARY_QUANTITY := 1;
717
718 END IF;
719
720 IF P_IS_LPN_REQUIRED = 'Y' THEN
721
722 L_CONTAINERIZED := 1;
723
724 ELSE
725
726 L_CONTAINERIZED := 0;
727
728 END IF;
729
730 INV_ITEM_INQ.UPDATE_QUANTITY(
731 P_ORGANIZATION_ID => P_ORG_ID,
732 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
733 P_REVISION => P_REVISION,
734 P_LOT_NUMBER => L_LOT_NUMBER,
735 P_SUBINVENTORY_CODE => P_SUBINVENTORY ,
736 P_LOCATOR_ID => P_LOCATOR_ID,
737 P_COST_GROUP_ID => P_COSTGROUP_ID,
738 P_PRIMARY_QUANTITY => ( -1 * L_PRIMARY_QUANTITY ),
739 P_CONTAINERIZED => L_CONTAINERIZED,
740 X_QOH => L_QOH,
741 X_ATT => L_ATT,
742 X_RETURN_STATUS => L_RETURN_STATUS,
743 X_MSG_DATA => L_MSG_DATA,
744 X_MSG_COUNT => L_MSG_COUNT
745 );
746
747 INV_ITEM_INQ.UPDATE_QUANTITY(
748 P_ORGANIZATION_ID => P_ORG_ID,
749 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
750 P_REVISION => P_REVISION,
751 P_LOT_NUMBER => L_LOT_NUMBER,
752 P_SUBINVENTORY_CODE => P_SUBINVENTORY ,
753 P_LOCATOR_ID => P_LOCATOR_ID,
754 P_COST_GROUP_ID => P_XFR_COSTGROUP_ID,
755 P_PRIMARY_QUANTITY => L_PRIMARY_QUANTITY,
756 P_CONTAINERIZED => L_CONTAINERIZED,
757 X_QOH => L_QOH,
758 X_ATT => L_ATT,
759 X_RETURN_STATUS => L_RETURN_STATUS,
760 X_MSG_DATA => L_MSG_DATA,
761 X_MSG_COUNT => L_MSG_COUNT
762 );
763
764 IF L_RETURN_STATUS <> 'S' THEN
765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 END IF;
767
768 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
769
770
771 EXCEPTION
772
773 WHEN FND_API.G_EXC_ERROR THEN
774
775 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
776 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
777
778 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
779
780 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
781 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
782
783 WHEN OTHERS THEN
784
785 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
786 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
787 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'TRANSFER');
788 END IF;
789 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
790
791 END UPDATE_QUANTITY_TREE;
792
793 END INV_MWB_CG_TRANSFER;