3:
4: -- Global constant holding the package name
5:
6: G_PKG_NAME CONSTANT VARCHAR2(30) := 'wms_txnrsn_actions_pub';
7: l_g_ret_sts_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_error;
8: l_g_ret_sts_unexp_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_unexp_error;
9: l_g_ret_sts_success CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_success;
10: g_trace_on CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11:
4: -- Global constant holding the package name
5:
6: G_PKG_NAME CONSTANT VARCHAR2(30) := 'wms_txnrsn_actions_pub';
7: l_g_ret_sts_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_error;
8: l_g_ret_sts_unexp_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_unexp_error;
9: l_g_ret_sts_success CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_success;
10: g_trace_on CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11:
12: g_debug NUMBER := 1;
5:
6: G_PKG_NAME CONSTANT VARCHAR2(30) := 'wms_txnrsn_actions_pub';
7: l_g_ret_sts_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_error;
8: l_g_ret_sts_unexp_error CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_unexp_error;
9: l_g_ret_sts_success CONSTANT VARCHAR2(30) := fnd_api.g_ret_sts_success;
10: g_trace_on CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11:
12: g_debug NUMBER := 1;
13: g_module_name VARCHAR2(30) := NULL;
40:
41: PROCEDURE Inadequate_Qty
42: (
43: p_api_version_number IN NUMBER
44: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
45: , p_commit IN VARCHAR2 := FND_API.G_FALSE
46: , x_return_status OUT NOCOPY VARCHAR2
47: , x_msg_count OUT NOCOPY NUMBER
48: , x_msg_data OUT NOCOPY VARCHAR2
41: PROCEDURE Inadequate_Qty
42: (
43: p_api_version_number IN NUMBER
44: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
45: , p_commit IN VARCHAR2 := FND_API.G_FALSE
46: , x_return_status OUT NOCOPY VARCHAR2
47: , x_msg_count OUT NOCOPY NUMBER
48: , x_msg_data OUT NOCOPY VARCHAR2
49: , p_organization_id IN NUMBER
104: l_reason_id:=p_reason_id;
105: l_discrepancy:=1;
106:
107: -- Initialize API return status to success
108: x_return_status := FND_API.G_RET_STS_SUCCESS;
109:
110:
111: IF (l_debug = 1) THEN
112: mdebug('Inside wms_txnrsn_actions_pub.Inadequate Quantity: Before update quantity ');
205:
206: IF l_mso_header_id IS NULL THEN
207: FND_MESSAGE.SET_NAME('INV','INV_COULD_NOT_GET_MSO_HEADER');
208: FND_MSG_PUB.Add;
209: RAISE fnd_api.g_exc_unexpected_error;
210: END IF;
211:
212: -- get data for p_missing_quantity
213: l_missing_quantity := l_transaction_quantity - p_qty_picked;
223: -- Calling Reserve Unconfirmed Quantity API (from INVPPCIB.pls)
224: inv_pick_release_pub.reserve_Unconfirmed_Quantity
225: (
226: p_api_version => 1.0
227: ,p_init_msg_list => fnd_api.g_false
228: ,p_commit => fnd_api.g_false
229: ,x_return_status => l_return_status
230: ,x_msg_count => l_msg_count
231: ,x_msg_data => l_msg_data
224: inv_pick_release_pub.reserve_Unconfirmed_Quantity
225: (
226: p_api_version => 1.0
227: ,p_init_msg_list => fnd_api.g_false
228: ,p_commit => fnd_api.g_false
229: ,x_return_status => l_return_status
230: ,x_msg_count => l_msg_count
231: ,x_msg_data => l_msg_data
232: ,p_missing_quantity => l_missing_quantity
243: IF (l_debug = 1) THEN
244: mdebug ('x_return_status : '|| x_return_status );
245: END IF;
246:
247: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
248: IF (l_debug = 1) THEN
249: mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
250: END IF;
251: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
248: IF (l_debug = 1) THEN
249: mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
250: END IF;
251: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252: END IF;
253:
254: -- for debugging
255: IF (l_debug = 1) THEN
266: -- Log Exception
267:
268: Log_exception
269: ( 1.0
270: , fnd_api.g_false
271: , FND_API.G_false
272: , l_return_status
273: , l_msg_count
274: , l_msg_data
267:
268: Log_exception
269: ( 1.0
270: , fnd_api.g_false
271: , FND_API.G_false
272: , l_return_status
273: , l_msg_count
274: , l_msg_data
275: , l_organization_id
310: END LOOP;
311: END IF;
312:
313:
314: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
315: -- mdebug('FE');
316: FND_MSG_PUB.Add_Exc_Msg
317: ( 'Inadequate Qty'
318: , 'Calling Log Exception'
316: FND_MSG_PUB.Add_Exc_Msg
317: ( 'Inadequate Qty'
318: , 'Calling Log Exception'
319: );
320: RAISE FND_API.G_EXC_ERROR;
321: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
322: IF (l_debug = 1) THEN
323: mdebug('SE');
324: END IF;
317: ( 'Inadequate Qty'
318: , 'Calling Log Exception'
319: );
320: RAISE FND_API.G_EXC_ERROR;
321: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
322: IF (l_debug = 1) THEN
323: mdebug('SE');
324: END IF;
325: FND_MSG_PUB.Add_Exc_Msg
325: FND_MSG_PUB.Add_Exc_Msg
326: ( 'Inadequate Qty'
327: , 'Calling Log Exception'
328: );
329: RAISE FND_API.G_EXC_ERROR;
330: END IF;
331: IF (l_debug = 1) THEN
332: mdebug('end of amins api');
333: END IF;
332: mdebug('end of amins api');
333: END IF;
334:
335: EXCEPTION
336: WHEN FND_API.G_EXC_ERROR THEN
337: x_return_status:=FND_API.G_RET_STS_ERROR;
338: fnd_msg_pub.count_and_get
339: ( p_count => x_msg_count
340: , p_data => x_msg_data
333: END IF;
334:
335: EXCEPTION
336: WHEN FND_API.G_EXC_ERROR THEN
337: x_return_status:=FND_API.G_RET_STS_ERROR;
338: fnd_msg_pub.count_and_get
339: ( p_count => x_msg_count
340: , p_data => x_msg_data
341: );
342:
343:
344: WHEN OTHERS THEN
345:
346: x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
347: fnd_msg_pub.count_and_get
348: ( p_count => x_msg_count
349: , p_data => x_msg_data
350: );
356:
357: PROCEDURE Suggest_alternate_location
358: (
359: p_api_version_number IN NUMBER
360: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
361: , p_commit IN VARCHAR2 := FND_API.G_FALSE
362: , x_return_status OUT NOCOPY VARCHAR2
363: , x_msg_count OUT NOCOPY NUMBER
364: , x_msg_data OUT NOCOPY VARCHAR2
357: PROCEDURE Suggest_alternate_location
358: (
359: p_api_version_number IN NUMBER
360: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
361: , p_commit IN VARCHAR2 := FND_API.G_FALSE
362: , x_return_status OUT NOCOPY VARCHAR2
363: , x_msg_count OUT NOCOPY NUMBER
364: , x_msg_data OUT NOCOPY VARCHAR2
365: , p_organization_id IN NUMBER
374: ) IS
375:
376:
377: l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
378: l_return_status VARCHAR2(10):= FND_API.G_RET_STS_SUCCESS;
379: l_msg_count NUMBER;
380: l_msg_data VARCHAR2(230);
381: l_trohdr_val_rec INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
382: l_commit VARCHAR2(1) := FND_API.G_FALSE;
378: l_return_status VARCHAR2(10):= FND_API.G_RET_STS_SUCCESS;
379: l_msg_count NUMBER;
380: l_msg_data VARCHAR2(230);
381: l_trohdr_val_rec INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
382: l_commit VARCHAR2(1) := FND_API.G_FALSE;
383: l_order_count NUMBER := 1; /* total number of lines */
384:
385: l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
386: l_trolin_rec INV_Move_Order_PUB.trolin_rec_type;
653:
654: inv_pick_release_pub.reserve_Unconfirmed_Quantity
655: (
656: p_api_version => 1.0
657: ,p_init_msg_list => fnd_api.g_false
658: ,p_commit => fnd_api.g_false
659: ,x_return_status => l_return_status
660: ,x_msg_count => l_msg_count
661: ,x_msg_data => l_msg_data
654: inv_pick_release_pub.reserve_Unconfirmed_Quantity
655: (
656: p_api_version => 1.0
657: ,p_init_msg_list => fnd_api.g_false
658: ,p_commit => fnd_api.g_false
659: ,x_return_status => l_return_status
660: ,x_msg_count => l_msg_count
661: ,x_msg_data => l_msg_data
662: ,p_missing_quantity => l_missing_quantity
670: ,p_revision => l_revision
671: ,p_lot_number => l_lot
672: );
673:
674: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
675: mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
676: l_cc_transfer_flag := 'F';
677: l_lot_qty := l_lot_qty + l_missing_quantity;
678: END IF;
691: -- Calling Reserve Unconfirmed Quantity API (from INVPPCIB.pls)
692: inv_pick_release_pub.reserve_Unconfirmed_Quantity
693: (
694: p_api_version => 1.0
695: ,p_init_msg_list => fnd_api.g_false
696: ,p_commit => fnd_api.g_false
697: ,x_return_status => l_return_status
698: ,x_msg_count => l_msg_count
699: ,x_msg_data => l_msg_data
692: inv_pick_release_pub.reserve_Unconfirmed_Quantity
693: (
694: p_api_version => 1.0
695: ,p_init_msg_list => fnd_api.g_false
696: ,p_commit => fnd_api.g_false
697: ,x_return_status => l_return_status
698: ,x_msg_count => l_msg_count
699: ,x_msg_data => l_msg_data
700: ,p_missing_quantity => l_missing_quantity
709: ,p_lot_number => NULL
710: );
711: mdebug ('l_return_status : '|| l_return_status );
712:
713: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
714: mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
715: --RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- ???? should we not raise exception??
716: -- and continue to do the pick release ??? need answers
717: l_cc_transfer_flag := 'F';
711: mdebug ('l_return_status : '|| l_return_status );
712:
713: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
714: mdebug(' inv_pick_release_pub.reserve_Unconfirmed_Quantity failed');
715: --RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- ???? should we not raise exception??
716: -- and continue to do the pick release ??? need answers
717: l_cc_transfer_flag := 'F';
718: l_lot_qty := l_missing_quantity;
719: END IF;
901: WHERE msnt.transaction_temp_id = l_mmtt_id;
902:
903: END IF;
904:
905: l_return_status := FND_API.G_RET_STS_SUCCESS;
906:
907: -- l_move_order_type <> 3 need to create a cycle count reservation
908: -- need to create cycle count reservation for remaining qty in the sub/loc
909: mdebug('before create cycle count reservation');
981: inv_quantity_tree_pub.clear_quantity_cache;
982:
983: inv_quantity_tree_pub.query_quantities
984: ( p_api_version_number => 1.0
985: , p_init_msg_lst => fnd_api.g_false
986: , x_return_status => l_return_status
987: , x_msg_count => l_msg_count
988: , x_msg_data => l_msg_data
989: , p_organization_id => l_organization_id
1010: , x_sqs => l_sqs
1011: , x_satt => l_satt
1012: , x_satr => l_satr
1013: );
1014: IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1015: mdebug('after calling qty tree l_atr:' || l_atr||' l_att: '||l_att);
1016: mdebug('after calling qty tree l_atr:' || l_satr);
1017: mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1018: ELSE
1025: -- ??? should we raise exception????
1026: --FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1027: --FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1028: --FND_MSG_PUB.ADD;
1029: --RAISE FND_API.G_EXC_ERROR;
1030: END IF;
1031: mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1032: mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1033:
1040: if l_atr <> 0 then
1041:
1042: mdebug('Before calling: inv_reservation_pvt.create_reservation');
1043:
1044: l_return_status := FND_API.G_RET_STS_SUCCESS;
1045:
1046: INV_Reservation_pvt.Create_Reservation
1047: (
1048: p_api_version_number => 1.0
1045:
1046: INV_Reservation_pvt.Create_Reservation
1047: (
1048: p_api_version_number => 1.0
1049: , p_init_msg_lst => fnd_api.g_false
1050: , x_return_status => l_return_status
1051: , x_msg_count => l_msg_count
1052: , x_msg_data => l_msg_data
1053: , p_rsv_rec => l_rsv_rec
1052: , x_msg_data => l_msg_data
1053: , p_rsv_rec => l_rsv_rec
1054: , p_serial_number => l_dummy_sn
1055: , x_serial_number => l_dummy_sn
1056: , p_partial_reservation_flag => fnd_api.g_false
1057: , p_force_reservation_flag => fnd_api.g_false
1058: , p_validation_flag => fnd_api.g_false
1059: , x_quantity_reserved => l_qty_succ_reserved
1060: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1053: , p_rsv_rec => l_rsv_rec
1054: , p_serial_number => l_dummy_sn
1055: , x_serial_number => l_dummy_sn
1056: , p_partial_reservation_flag => fnd_api.g_false
1057: , p_force_reservation_flag => fnd_api.g_false
1058: , p_validation_flag => fnd_api.g_false
1059: , x_quantity_reserved => l_qty_succ_reserved
1060: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1061: , x_reservation_id => l_cc_res_id
1054: , p_serial_number => l_dummy_sn
1055: , x_serial_number => l_dummy_sn
1056: , p_partial_reservation_flag => fnd_api.g_false
1057: , p_force_reservation_flag => fnd_api.g_false
1058: , p_validation_flag => fnd_api.g_false
1059: , x_quantity_reserved => l_qty_succ_reserved
1060: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1061: , x_reservation_id => l_cc_res_id
1062: );
1060: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1061: , x_reservation_id => l_cc_res_id
1062: );
1063: -- Return an error if the create reservation call failed
1064: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1065: mdebug('error in create reservation');
1066: l_cc_insert_flag := 'F';
1067: --l_lot_qty := l_lot_qty + l_rsv_rec.primary_reservation_quantity;
1068: ELSE
1082: inv_quantity_tree_pub.clear_quantity_cache;
1083:
1084: inv_quantity_tree_pub.query_quantities
1085: ( p_api_version_number => 1.0
1086: , p_init_msg_lst => fnd_api.g_false
1087: , x_return_status => l_return_status
1088: , x_msg_count => l_msg_count
1089: , x_msg_data => l_msg_data
1090: , p_organization_id => l_organization_id
1111: , x_sqs => l_sqs
1112: , x_satt => l_satt
1113: , x_satr => l_satr
1114: );
1115: IF (l_return_status = fnd_api.g_ret_sts_success ) THEN
1116: mdebug('after calling qty tree l_atr:' || l_atr||' l_att:'||l_att);
1117: mdebug('after calling qty tree l_qoh:' || l_qoh ||' l_rqoh: '||l_rqoh||' l_qr:'||l_qr||' l_qs:'||l_qs);
1118: ELSE
1119: mdebug('calling qty tree API failed ');
1126: -- ??? should we raise exception????
1127: --FND_MESSAGE.set_name('INV', 'INV_INVALID_QUANTITY_TYPE');
1128: --FND_MESSAGE.set_token('ROUTINE', 'INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES');
1129: --FND_MSG_PUB.ADD;
1130: --RAISE FND_API.G_EXC_ERROR;
1131: END IF;
1132: mdebug('after calling quantity tree ARI, l_atr:'||l_atr);
1133: mdebug('after calling quantity tree ARI, l_satr:'||l_satr);
1134:
1138:
1139:
1140: mdebug(' primary_reservation_quantity :'||l_rsv_rec.primary_reservation_quantity);
1141:
1142: l_return_status := FND_API.G_RET_STS_SUCCESS;
1143:
1144: mdebug('Before calling: inv_reservation_pvt.create_reservation');
1145:
1146: if l_atr <> 0 then
1147:
1148: INV_Reservation_pvt.Create_Reservation
1149: (
1150: p_api_version_number => 1.0
1151: , p_init_msg_lst => fnd_api.g_false
1152: , x_return_status => l_return_status
1153: , x_msg_count => l_msg_count
1154: , x_msg_data => l_msg_data
1155: , p_rsv_rec => l_rsv_rec
1154: , x_msg_data => l_msg_data
1155: , p_rsv_rec => l_rsv_rec
1156: , p_serial_number => l_dummy_sn
1157: , x_serial_number => l_dummy_sn
1158: , p_partial_reservation_flag => fnd_api.g_false
1159: , p_force_reservation_flag => fnd_api.g_false
1160: , p_validation_flag => fnd_api.g_false
1161: , x_quantity_reserved => l_qty_succ_reserved
1162: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1155: , p_rsv_rec => l_rsv_rec
1156: , p_serial_number => l_dummy_sn
1157: , x_serial_number => l_dummy_sn
1158: , p_partial_reservation_flag => fnd_api.g_false
1159: , p_force_reservation_flag => fnd_api.g_false
1160: , p_validation_flag => fnd_api.g_false
1161: , x_quantity_reserved => l_qty_succ_reserved
1162: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1163: , x_reservation_id => l_cc_res_id
1156: , p_serial_number => l_dummy_sn
1157: , x_serial_number => l_dummy_sn
1158: , p_partial_reservation_flag => fnd_api.g_false
1159: , p_force_reservation_flag => fnd_api.g_false
1160: , p_validation_flag => fnd_api.g_false
1161: , x_quantity_reserved => l_qty_succ_reserved
1162: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1163: , x_reservation_id => l_cc_res_id
1164: );
1162: , x_secondary_quantity_reserved => l_sec_qty_succ_reserved
1163: , x_reservation_id => l_cc_res_id
1164: );
1165: -- Return an error if the create reservation call failed
1166: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1167: mdebug('error in create reservation');
1168: l_cc_insert_flag := 'F';
1169: ELSE
1170: mdebug('l_qty_succ_reserved :'|| l_qty_succ_reserved);
1182: -- DELETE FROM mtl_transaction_lots_temp mtlt
1183: -- WHERE mtlt.transaction_temp_id = l_mmtt_id;
1184: --end if;
1185:
1186: l_return_status := FND_API.G_RET_STS_SUCCESS;
1187:
1188: select quantity_detailed, line_status
1189: ,nvl(quantity_delivered,0)
1190: into l_old_quantity_detailed
1341: , p_move_order_type => l_move_order_type
1342: , p_serial_flag => l_serial_flag
1343: );
1344:
1345: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1346: mdebug(' inv_replenish_detail_pub.line_details_pub failed');
1347: --ROLLBACK TO SAVEPOINT before_allocation;
1348: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349: END IF;
1344:
1345: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1346: mdebug(' inv_replenish_detail_pub.line_details_pub failed');
1347: --ROLLBACK TO SAVEPOINT before_allocation;
1348: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349: END IF;
1350:
1351: --mdebug('After calling: INV_Replenish_Detail_PUB.Line_Details_PUB ');
1352:
1411: END LOOP;
1412: END IF;
1413:
1414:
1415: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1416: FND_MSG_PUB.Add_Exc_Msg
1417: ( 'Suggest Alt Loc'
1418: , 'Call Pick Release'
1419: );
1416: FND_MSG_PUB.Add_Exc_Msg
1417: ( 'Suggest Alt Loc'
1418: , 'Call Pick Release'
1419: );
1420: RAISE FND_API.G_EXC_ERROR;
1421: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1422: FND_MSG_PUB.Add_Exc_Msg
1423: ( 'Suggest Alt Loc'
1424: , 'Call Pick Release'
1417: ( 'Suggest Alt Loc'
1418: , 'Call Pick Release'
1419: );
1420: RAISE FND_API.G_EXC_ERROR;
1421: ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1422: FND_MSG_PUB.Add_Exc_Msg
1423: ( 'Suggest Alt Loc'
1424: , 'Call Pick Release'
1425: );
1422: FND_MSG_PUB.Add_Exc_Msg
1423: ( 'Suggest Alt Loc'
1424: , 'Call Pick Release'
1425: );
1426: RAISE FND_API.G_EXC_ERROR;
1427: END IF;
1428:
1429: mdebug('before exception section');
1430:
1429: mdebug('before exception section');
1430:
1431: EXCEPTION
1432:
1433: WHEN FND_API.G_EXC_ERROR THEN
1434:
1435: Raise FND_API.G_EXC_ERROR;
1436:
1437: WHEN OTHERS THEN
1431: EXCEPTION
1432:
1433: WHEN FND_API.G_EXC_ERROR THEN
1434:
1435: Raise FND_API.G_EXC_ERROR;
1436:
1437: WHEN OTHERS THEN
1438: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1439: THEN
1441: ( 'INV_Move_Order_PUB'
1442: , 'Create_Move_Orders'
1443: );
1444: END IF;
1445: Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1446:
1447: END Suggest_alternate_location ;
1448:
1449:
1452:
1453: PROCEDURE Log_exception
1454: (
1455: p_api_version_number IN NUMBER
1456: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1457: , p_commit IN VARCHAR2 := FND_API.G_FALSE
1458: , x_return_status OUT NOCOPY VARCHAR2
1459: , x_msg_count OUT NOCOPY NUMBER
1460: , x_msg_data OUT NOCOPY VARCHAR2
1453: PROCEDURE Log_exception
1454: (
1455: p_api_version_number IN NUMBER
1456: , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
1457: , p_commit IN VARCHAR2 := FND_API.G_FALSE
1458: , x_return_status OUT NOCOPY VARCHAR2
1459: , x_msg_count OUT NOCOPY NUMBER
1460: , x_msg_data OUT NOCOPY VARCHAR2
1461: , p_organization_id IN NUMBER
1540: SET reason_id = p_reason_id
1541: WHERE transaction_header_id = p_mmtt_id;
1542: END IF;
1543:
1544: x_return_status := FND_API.G_RET_STS_SUCCESS;
1545:
1546: exception
1547: when others THEN
1548: x_return_status:=FND_API.G_RET_STS_ERROR;
1544: x_return_status := FND_API.G_RET_STS_SUCCESS;
1545:
1546: exception
1547: when others THEN
1548: x_return_status:=FND_API.G_RET_STS_ERROR;
1549:
1550: l_return_err := 'Insert into WMS_Exceptions failed'||
1551: substrb(sqlerrm,1,55);
1552: raise_application_error(-20000,l_return_err);
1662: IF (l_debug = 1) THEN
1663: mdebug('after calling cleanup_task for transaction:'||l_mmtt_temp_id);
1664: END IF;
1665:
1666: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1667:
1668: IF l_debug = 1 THEN
1669: mdebug('cleanup_task (W) :Error occurred while calling cleanup_task');
1670: END IF ;
1667:
1668: IF l_debug = 1 THEN
1669: mdebug('cleanup_task (W) :Error occurred while calling cleanup_task');
1670: END IF ;
1671: RAISE fnd_api.g_exc_error;
1672:
1673: END IF;
1674:
1675: END LOOP;
1693: , x_msg_count =>x_msg_count
1694: , x_msg_data =>x_msg_data );
1695: mdebug('END : ' || l_proc_name );
1696: EXCEPTION
1697: WHEN fnd_api.g_exc_error THEN
1698: x_return_status := l_g_ret_sts_error;
1699: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1700: mdebug('ROLLBACK ' );
1701: ROLLBACK ;
1698: x_return_status := l_g_ret_sts_error;
1699: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1700: mdebug('ROLLBACK ' );
1701: ROLLBACK ;
1702: mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
1703: WHEN OTHERS THEN
1704: x_return_status := l_g_ret_sts_unexp_error;
1705: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1706: mdebug('ROLLBACK ' );
1704: x_return_status := l_g_ret_sts_unexp_error;
1705: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1706: mdebug('ROLLBACK ' );
1707: ROLLBACK ;
1708: mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
1709:
1710:
1711: END;
1712:
1823: FROM mtl_material_transactions_temp t1
1824: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
1825:
1826: BEGIN
1827: x_return_status := fnd_api.g_ret_sts_success;
1828: g_debug := l_debug;
1829: g_module_name := l_proc_name;
1830: l_progress := '110';
1831: IF (l_debug = 1) THEN
1911: l_progress := '220';
1912: IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
1913: wms_workflow_wrappers.wf_wrapper
1914: (p_api_version => 1.0,
1915: p_init_msg_list => fnd_api.g_false,
1916: p_commit => fnd_api.g_false,
1917: p_org_id => l_org_id ,
1918: p_rsn_id => p_qty_rsn_id,
1919: p_calling_program => l_calling_program,
1912: IF (l_debug = 1) THEN mdebug ('l_progress: ' || l_progress ); END IF;
1913: wms_workflow_wrappers.wf_wrapper
1914: (p_api_version => 1.0,
1915: p_init_msg_list => fnd_api.g_false,
1916: p_commit => fnd_api.g_false,
1917: p_org_id => l_org_id ,
1918: p_rsn_id => p_qty_rsn_id,
1919: p_calling_program => l_calling_program,
1920: p_tmp_id => l_txn_temp_id,
1925: x_msg_count => x_msg_count,
1926: x_msg_data => x_msg_data);
1927:
1928: IF (l_debug = 1) THEN mdebug('x_return_status = ' || x_return_status); END IF;
1929: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1930: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1931: fnd_msg_pub.ADD;
1932: RAISE fnd_api.g_exc_unexpected_error;
1933: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1928: IF (l_debug = 1) THEN mdebug('x_return_status = ' || x_return_status); END IF;
1929: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1930: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1931: fnd_msg_pub.ADD;
1932: RAISE fnd_api.g_exc_unexpected_error;
1933: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1934: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1935: fnd_msg_pub.ADD;
1936: RAISE fnd_api.g_exc_error;
1929: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1930: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1931: fnd_msg_pub.ADD;
1932: RAISE fnd_api.g_exc_unexpected_error;
1933: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1934: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1935: fnd_msg_pub.ADD;
1936: RAISE fnd_api.g_exc_error;
1937: END IF;
1932: RAISE fnd_api.g_exc_unexpected_error;
1933: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1934: fnd_message.set_name('WMS', 'WMS_MULT_LPN_ERROR');
1935: fnd_msg_pub.ADD;
1936: RAISE fnd_api.g_exc_error;
1937: END IF;
1938: -- MRANA : added the following 3392471 . : 26-feb-04
1939: -- Cascade delete the current MMTT and WDT That was just processed
1940:
1953: ,p_update_parent => l_update_parent
1954: );
1955: mdebug ('x_return_status ' || x_return_status);
1956:
1957: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1958: IF l_debug = 1 THEN
1959: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
1960: END IF;
1961: RAISE fnd_api.g_exc_error;
1957: IF x_return_status <> fnd_api.g_ret_sts_success THEN
1958: IF l_debug = 1 THEN
1959: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
1960: END IF;
1961: RAISE fnd_api.g_exc_error;
1962: END IF;
1963: -- MRANA : 2/26/04 -- END IF; if Wf does not exist only then the following
1964: --shld be performed
1965: ELSE -- wf <=0
2060: END IF;
2061:
2062: inv_reservation_pub.update_reservation(
2063: p_api_version_number => 1.0
2064: , p_init_msg_lst => fnd_api.g_false
2065: , x_return_status => x_return_status
2066: , x_msg_count => x_msg_count
2067: , x_msg_data => x_msg_data
2068: , p_original_rsv_rec => l_old_upd_resv_rec
2068: , p_original_rsv_rec => l_old_upd_resv_rec
2069: , p_to_rsv_rec => l_new_upd_resv_rec
2070: , p_original_serial_number => l_upd_dummy_sn
2071: , p_to_serial_number => l_upd_dummy_sn
2072: , p_validation_flag => fnd_api.g_true
2073: );
2074:
2075: IF (l_debug = 1) THEN
2076: mdebug('CLEANUP_TASK: return of update_reservation api : ' || x_return_status);
2101: , p_transaction_temp_id => l_txn_temp_id
2102: ,p_update_parent => l_update_parent
2103: );
2104:
2105: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2106: IF l_debug = 1 THEN
2107: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2108: END IF;
2109: RAISE fnd_api.g_exc_error;
2105: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2106: IF l_debug = 1 THEN
2107: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2108: END IF;
2109: RAISE fnd_api.g_exc_error;
2110: END IF;
2111:
2112: IF (l_wf <= 0) or (p_qty_rsn_id <= 0) then
2113: l_progress := '300';
2152: THEN
2153: IF (l_debug = 1) THEN
2154: mdebug('CLEANUP_TASK: Unexpected error occurrend while calling BackOrder API');
2155: END IF;
2156: RAISE fnd_api.g_exc_error;
2157: END IF;
2158:
2159:
2160: ELSIF l_mo_type IN (INV_GLOBALS.G_MOVE_ORDER_REQUISITION, INV_GLOBALS.G_MOVE_ORDER_REPLENISHMENT) THEN
2173: , p_transaction_temp_id => l_txn_temp_id
2174: ,p_update_parent => l_update_parent
2175: );
2176:
2177: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2178: IF l_debug = 1 THEN
2179: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2180: END IF;
2181: RAISE fnd_api.g_exc_error;
2177: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2178: IF l_debug = 1 THEN
2179: mdebug('CLEANUP_TASK: Error occurred while deleting MMTT');
2180: END IF;
2181: RAISE fnd_api.g_exc_error;
2182: END IF;
2183:
2184: --Bug 5162468 for Fill kill zero pick condition
2185: --close the MO line
2196: mdebug('Replenishment Move Order... Closing the Move Order');
2197: END IF;
2198:
2199: INV_MO_ADMIN_PUB.close_line(1.0,'F','F','F',l_mo_line_id,x_msg_count,x_msg_data,l_return_status);
2200: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2201: RAISE FND_API.G_EXC_ERROR;
2202: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2203: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204: END IF;
2197: END IF;
2198:
2199: INV_MO_ADMIN_PUB.close_line(1.0,'F','F','F',l_mo_line_id,x_msg_count,x_msg_data,l_return_status);
2200: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2201: RAISE FND_API.G_EXC_ERROR;
2202: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2203: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204: END IF;
2205:
2198:
2199: INV_MO_ADMIN_PUB.close_line(1.0,'F','F','F',l_mo_line_id,x_msg_count,x_msg_data,l_return_status);
2200: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2201: RAISE FND_API.G_EXC_ERROR;
2202: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2203: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204: END IF;
2205:
2206: END IF;
2199: INV_MO_ADMIN_PUB.close_line(1.0,'F','F','F',l_mo_line_id,x_msg_count,x_msg_data,l_return_status);
2200: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2201: RAISE FND_API.G_EXC_ERROR;
2202: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2203: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2204: END IF;
2205:
2206: END IF;
2207:
2221: END LOOP;
2222:
2223: wms_txnrsn_actions_pub.log_exception(
2224: p_api_version_number => 1.0
2225: , p_init_msg_lst => fnd_api.g_false
2226: , p_commit => fnd_api.g_false
2227: , x_return_status => x_return_status
2228: , x_msg_count => x_msg_count
2229: , x_msg_data => x_msg_data
2222:
2223: wms_txnrsn_actions_pub.log_exception(
2224: p_api_version_number => 1.0
2225: , p_init_msg_lst => fnd_api.g_false
2226: , p_commit => fnd_api.g_false
2227: , x_return_status => x_return_status
2228: , x_msg_count => x_msg_count
2229: , x_msg_data => x_msg_data
2230: , p_organization_id => l_org_id
2238: , p_reason_id => p_qty_rsn_id
2239: , p_discrepancy_type => 1
2240: , p_user_id => p_employee_id);
2241:
2242: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2243: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2244: fnd_msg_pub.ADD;
2245: RAISE fnd_api.g_exc_unexpected_error;
2246: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2241:
2242: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2243: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2244: fnd_msg_pub.ADD;
2245: RAISE fnd_api.g_exc_unexpected_error;
2246: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2247: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2248: fnd_msg_pub.ADD;
2249: RAISE fnd_api.g_exc_error;
2242: IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2243: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2244: fnd_msg_pub.ADD;
2245: RAISE fnd_api.g_exc_unexpected_error;
2246: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2247: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2248: fnd_msg_pub.ADD;
2249: RAISE fnd_api.g_exc_error;
2250: END IF;
2245: RAISE fnd_api.g_exc_unexpected_error;
2246: ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
2247: fnd_message.set_name('WMS', 'WMS_LOG_EXCEPTION_FAIL');
2248: fnd_msg_pub.ADD;
2249: RAISE fnd_api.g_exc_error;
2250: END IF;
2251:
2252: l_progress := '390';
2253:
2270: , p_transaction_temp_id => l_parent_line_id
2271: ,p_update_parent => l_update_parent
2272: );
2273:
2274: IF x_return_status <> fnd_api.g_ret_sts_success THEN
2275: mdebug ('Clean up task in loop for deleting parent line ');
2276: IF l_debug = 1 THEN
2277: mdebug('CLEANUP_TASK: Error occurred while deleting parent line inMMTT');
2278: END IF;
2275: mdebug ('Clean up task in loop for deleting parent line ');
2276: IF l_debug = 1 THEN
2277: mdebug('CLEANUP_TASK: Error occurred while deleting parent line inMMTT');
2278: END IF;
2279: RAISE fnd_api.g_exc_error;
2280: END IF;
2281: END IF; --for parent line IF
2282:
2283: CLOSE c_mmtt_info;
2283: CLOSE c_mmtt_info;
2284: --COMMIT; --???
2285: mdebug('END : ' || l_proc_name );
2286: EXCEPTION
2287: WHEN fnd_api.g_exc_error THEN
2288: x_return_status := l_g_ret_sts_error;
2289: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2290: mdebug('ROLLBACK ' );
2291: ROLLBACK ;
2289: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2290: mdebug('ROLLBACK ' );
2291: ROLLBACK ;
2292: mdebug('l_progress = ' || l_proc_name || ':'|| l_progress);
2293: mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2294: WHEN OTHERS THEN
2295: x_return_status := l_g_ret_sts_unexp_error;
2296: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2297: mdebug('ROLLBACK ' );
2296: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2297: mdebug('ROLLBACK ' );
2298: ROLLBACK ;
2299: mdebug('l_progress = ' || l_proc_name || ':'|| l_progress);
2300: mdebug('RAISE fnd_api.g_exc_error: ' || SQLERRM);
2301:
2302: END cleanup_task;
2303:
2304:
2506: END IF;
2507:
2508: Log_exception(
2509: p_api_version_number => 1.0
2510: , p_init_msg_lst => fnd_api.g_false
2511: , p_commit => fnd_api.g_false
2512: , x_return_status => x_return_status
2513: , x_msg_count => x_msg_count
2514: , x_msg_data => x_msg_data
2507:
2508: Log_exception(
2509: p_api_version_number => 1.0
2510: , p_init_msg_lst => fnd_api.g_false
2511: , p_commit => fnd_api.g_false
2512: , x_return_status => x_return_status
2513: , x_msg_count => x_msg_count
2514: , x_msg_data => x_msg_data
2515: , p_organization_id => p_organization_id
2528: );
2529:
2530: EXCEPTION
2531: WHEN OTHERS THEN
2532: x_return_status:=FND_API.G_RET_STS_ERROR;
2533: l_return_err := 'Insert into WMS_Exceptions failed'|| substrb(sqlerrm,1,55);
2534: raise_application_error(-20000,l_return_err);
2535:
2536: END Log_exception;