[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_GRP as
2: /* $Header: WSHDDGPB.pls 120.28.12010000.2 2008/09/15 12:58:13 gbhargav ship $ */
3:
4: -- standard global constants
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_DELIVERY_DETAILS_GRP';
1: PACKAGE BODY WSH_DELIVERY_DETAILS_GRP as
2: /* $Header: WSHDDGPB.pls 120.28.12010000.2 2008/09/15 12:58:13 gbhargav ship $ */
3:
4: -- standard global constants
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_DELIVERY_DETAILS_GRP';
6: p_message_type CONSTANT VARCHAR2(1) := 'E';
7: c_wms_code_present VARCHAR2(1) := 'Y';
8:
9: -- anxsharm for Load Tender
42: wdd.picked_quantity2,
43: wdd.pickable_flag,
44: wda.delivery_id,
45: wda.parent_delivery_detail_id
46: FROM wsh_delivery_details wdd,
47: wsh_delivery_assignments_v wda
48: WHERE wdd.delivery_detail_id = p_detail_id
49: AND wdd.delivery_detail_id = wda.delivery_detail_id;
50:
209: -- deliveryMerge
210: CURSOR c_get_assigned_delivery (c_delivery_detail_id NUMBER) IS
211: SELECT wda.delivery_id
212: FROM wsh_delivery_assignments_v wda,
213: wsh_delivery_details wdd
214: WHERE wda.delivery_detail_id = c_delivery_detail_id
215: AND wda.delivery_detail_id = wdd.delivery_detail_id
216: AND wdd.container_flag = 'N'
217: AND NVL(wdd.line_direction,'O') in ('O','IO');
404: wsh_debug_sv.logmsg(l_module_name, 'Calling program unit WSH_DLVB_COMMON_ACTIONS.UNASSIGN_DETAILS', wsh_debug_sv.c_proc_level);
405: END IF;
406:
407: /* Patchset I : Harmonization Project Begin
408: Instead of the old call to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Multiple_Details We now have call to wsh_dlvb_common_actions.unassign_details */
409: -- deliveryMerge
410:
411: i := l_tabofdeldets.FIRST;
412: WHILE i is not NULL LOOP
605:
606: WHEN OTHERS THEN
607: ROLLBACK TO detail_to_delivery_grp;
608: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
609: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_GRP.DETAIL_TO_DELIVERY');
610: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data,
611: p_encoded => fnd_api.g_false);
612:
613: IF l_debug_on THEN
667: END IF;
668:
669:
670: IF l_debug_on THEN
671: wsh_debug_sv.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_GRP.DETAIL_TO_DELIVERY', wsh_debug_sv.c_proc_level);
672: END IF;
673:
674: DETAIL_TO_DELIVERY
675: (
693: EXCEPTION
694:
695: WHEN OTHERS THEN
696: x_return_status := wsh_util_core.g_ret_sts_unexp_error;
697: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_GRP.DETAIL_TO_DELIVERY');
698: fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data,
699: p_encoded => fnd_api.g_false);
700:
701: IF l_debug_on THEN
748: NVL(received_quantity, shipped_quantity),
749: NVL(received_quantity2, shipped_quantity2),
750: NVL(picked_quantity, requested_quantity),
751: NVL(picked_quantity2, requested_quantity2)
752: FROM wsh_delivery_details
753: WHERE delivery_detail_id = c_delivery_detail_id;
754:
755: wsh_invalid_split_qty EXCEPTION;
756: wsh_invalid_split_qty2 EXCEPTION; /* Added for OPM. NC - 03/19/01 */
777:
778: --
779: CURSOR det_cur(p_del_det_id NUMBER) IS
780: SELECT inventory_item_id, organization_id, requested_quantity_uom, top_model_line_id
781: FROM wsh_delivery_details
782: WHERE delivery_detail_id = p_del_det_id;
783: BEGIN
784: --
785: l_debug_on := wsh_debug_interface.g_debug;
911:
912: END IF;
913:
914: IF l_debug_on THEN
915: wsh_debug_sv.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_ACTIONS.SPLIT_DELIVERY_DETAILS', wsh_debug_sv.c_proc_level);
916: END IF;
917:
918: --
919: -- HW added p_converted_flag
916: END IF;
917:
918: --
919: -- HW added p_converted_flag
920: wsh_delivery_details_actions.split_delivery_details(
921: p_from_detail_id => p_from_detail_id,
922: p_req_quantity => x_split_quantity,
923: p_req_quantity2 => x_split_quantity2,
924: p_manual_split => p_manual_split,
1047: WHEN OTHERS THEN
1048: ROLLBACK TO split_line_grp;
1049: x_return_status := fnd_api.g_ret_sts_unexp_error;
1050: wsh_util_core.add_message(x_return_status, l_module_name);
1051: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_GRP.Split_Line');
1052:
1053: fnd_msg_pub.count_and_get(
1054: p_count => x_msg_count,
1055: p_data => x_msg_data,
1136: l_org NUMBER;
1137:
1138: cursor get_org (p_detail_id in number)is
1139: select organization_id
1140: from wsh_delivery_details
1141: where delivery_detail_id = p_detail_id;
1142: --
1143: l_debug_on BOOLEAN;
1144: --
1491: END IF;
1492: ROLLBACK TO UPDATE_SHIPPING_ATTR_GRP;
1493: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1494: WSH_UTIL_CORE.add_message (x_return_status);
1495: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Update_Shipping_Attributes');
1496: FND_MSG_PUB.Count_And_Get
1497: ( p_count => x_msg_count,
1498: p_data => x_msg_data,
1499: p_encoded => FND_API.G_FALSE
1572:
1573: /*
1574: EXCEPTION
1575: WHEN others then
1576: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_GRP.Get_Detail_Status');
1577: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1578: WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
1579: if x_msg_count > 1 then
1580: x_msg_data := l_msg_summary || l_msg_details;
1979: WHEN OTHERS THEN
1980: ROLLBACK TO AUTOCREATE_DEL_GRP;
1981: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1982: wsh_util_core.add_message(x_return_status, l_module_name);
1983: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Autocreate_Deliveries');
1984: --
1985: -- Start of code for Bugfix 4070732
1986: --
1987: IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name) THEN
2189: WHEN OTHERS THEN
2190: ROLLBACK TO AUTOCREATE_TRIP_GRP;
2191: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2192: wsh_util_core.add_message(x_return_status, l_module_name);
2193: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Autocreate_Del_Trip');
2194: FND_MSG_PUB.Count_And_Get
2195: (
2196: p_count => x_msg_count,
2197: p_data => x_msg_data,
2331: , organization_id
2332: , requested_quantity_uom
2333: , nvl(line_direction,'O') line_direction -- J-IB-NPARIKH
2334: , released_status
2335: FROM wsh_delivery_details
2336: WHERE delivery_detail_id = p_del_det_id;
2337:
2338: -- HW Harmonization project for OPM. Need to get organization_id
2339: -- HW OPMCONV - 1) Added retrieval of UOM2 -
2339: -- HW OPMCONV - 1) Added retrieval of UOM2 -
2340: -- - 2) Changed name to det_line
2341: CURSOR det_line(l_del_det_id NUMBER) IS
2342: SELECT nvl(line_direction,'O') line_direction, requested_quantity_uom2
2343: FROM wsh_delivery_details
2344: WHERE delivery_detail_id = l_del_det_id;
2345: -- HW OPM end
2346:
2347: --
2348: CURSOR cycle_count_cur(p_del_det_id NUMBER) IS
2349: SELECT released_status, requested_quantity, requested_quantity2,
2350: picked_quantity, picked_quantity2 , shipped_quantity , shipped_quantity2,
2351: organization_id, inventory_item_id
2352: FROM wsh_delivery_details
2353: WHERE delivery_detail_id = p_del_det_id;
2354:
2355: --
2356: CURSOR del_cur(p_dlvy_id NUMBER) IS
2367: -- deliveryMerge
2368:
2369: CURSOR get_delivery(p_del_det_id NUMBER) IS
2370: SELECT wda.delivery_id
2371: FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
2372: WHERE wdd.delivery_detail_id = p_del_det_id
2373: AND wdd.delivery_detail_id = wda.delivery_detail_id
2374: AND wdd.container_flag = 'N'
2375: AND wdd.source_code = 'OE'
2828:
2829: -- we need to send only the valid records to lock procedure
2830: -- Build the table using the valid index output from Is_Action_Enabled
2831: IF l_debug_on THEN
2832: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.LOCK_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
2833: END IF;
2834: IF NOT ( p_action_prms.caller = 'WSH_FSTRX'
2835: AND p_action_prms.action_code = 'DELETE'
2836: ) THEN --BUG 4354579
2833: END IF;
2834: IF NOT ( p_action_prms.caller = 'WSH_FSTRX'
2835: AND p_action_prms.action_code = 'DELETE'
2836: ) THEN --BUG 4354579
2837: wsh_delivery_details_pkg.Lock_Delivery_Details(
2838: p_rec_attr_tab => p_rec_attr_tab,
2839: p_caller => p_action_prms.caller,
2840: p_valid_index_tab => l_valid_index_tab,
2841: x_valid_ids_tab => l_valid_ids_tab,
3143: l_index := l_id_tab.NEXT(l_index);
3144: END LOOP;
3145: --
3146: IF l_debug_on THEN
3147: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3148: END IF;
3149: --
3150: WSH_DELIVERY_DETAILS_GRP.detail_to_delivery(
3151: p_api_version => l_api_version,
3146: IF l_debug_on THEN
3147: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.DETAIL_TO_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
3148: END IF;
3149: --
3150: WSH_DELIVERY_DETAILS_GRP.detail_to_delivery(
3151: p_api_version => l_api_version,
3152: p_init_msg_list => FND_API.G_FALSE,
3153: p_commit => FND_API.G_FALSE,
3154: p_validation_level => l_validation_level,
3277:
3278: -- HW Harmonization project forOPM. Added p_manual_split parameter
3279: -- HW added p_converted_flag
3280: IF l_debug_on THEN
3281: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.SPLIT_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
3282: END IF;
3283: WSH_DELIVERY_DETAILS_GRP.split_line(
3284: p_api_version => l_api_version,
3285: p_init_msg_list => FND_API.G_FALSE,
3279: -- HW added p_converted_flag
3280: IF l_debug_on THEN
3281: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.SPLIT_LINE',WSH_DEBUG_SV.C_PROC_LEVEL);
3282: END IF;
3283: WSH_DELIVERY_DETAILS_GRP.split_line(
3284: p_api_version => l_api_version,
3285: p_init_msg_list => FND_API.G_FALSE,
3286: p_commit => FND_API.G_FALSE,
3287: p_validation_level => l_validation_level,
3309: --
3310: ELSIF p_action_prms.action_code = 'AUTOCREATE-DEL' THEN
3311: --
3312: IF l_debug_on THEN
3313: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DELIVERIES',WSH_DEBUG_SV.C_PROC_LEVEL);
3314: END IF;
3315: --
3316: --Compatibility Changes
3317: IF wsh_util_core.fte_is_installed = 'Y' AND l_cc_line_groups.COUNT>0 THEN
3348: IF l_debug_on THEN
3349: wsh_debug_sv.log(l_module_name,'id_tab_temp count ',l_id_tab_temp.COUNT);
3350: END IF;
3351:
3352: WSH_DELIVERY_DETAILS_GRP.Autocreate_Deliveries(
3353: p_api_version_number => l_api_version,
3354: p_init_msg_list => FND_API.G_FALSE,
3355: p_commit => FND_API.G_FALSE,
3356: p_caller => p_action_prms.caller,
3439: END IF;
3440:
3441: ELSE
3442:
3443: WSH_DELIVERY_DETAILS_GRP.Autocreate_Deliveries(
3444: p_api_version_number => l_api_version,
3445: p_init_msg_list => FND_API.G_FALSE,
3446: p_commit => FND_API.G_FALSE,
3447: p_caller => p_action_prms.caller,
3485: --
3486: ELSIF p_action_prms.action_code = 'AUTOCREATE-TRIP' THEN
3487: --
3488: IF l_debug_on THEN
3489: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DEL_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
3490: END IF;
3491: --
3492: WSH_DELIVERY_DETAILS_GRP.Autocreate_del_trip(
3493: p_api_version_number => l_api_version,
3488: IF l_debug_on THEN
3489: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_GRP.AUTOCREATE_DEL_TRIP',WSH_DEBUG_SV.C_PROC_LEVEL);
3490: END IF;
3491: --
3492: WSH_DELIVERY_DETAILS_GRP.Autocreate_del_trip(
3493: p_api_version_number => l_api_version,
3494: p_init_msg_list => FND_API.G_FALSE,
3495: p_commit => FND_API.G_FALSE,
3496: x_return_status => l_return_status,
4595: IF cycle_count_cur%ISOPEN THEN
4596: Close cycle_count_cur;
4597: END IF;
4598: wsh_util_core.add_message(x_return_status, l_module_name);
4599: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action');
4600: --
4601: -- K LPN CONV. rv
4602: --
4603: IF WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED = 'Y'
4886: WHEN OTHERS THEN
4887: ROLLBACK TO CREATE_UPDATE_DEL_DETAIL_GRP;
4888: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4889: wsh_util_core.add_message(x_return_status, l_module_name);
4890: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail');
4891: FND_MSG_PUB.Count_And_Get
4892: (
4893: p_count => x_msg_count,
4894: p_data => x_msg_data,
4950: l_lpn_table WMS_Data_Type_Definitions_pub.LPNTableType;
4951: l_container_info_rec WSH_GLBL_VAR_STRCT_GRP.ContInfoRectype;
4952: l_container_rec WSH_GLBL_VAR_STRCT_GRP.Delivery_Details_Rec_Type;
4953: l_cont_dummy_tab WSH_UTIL_CORE.id_tab_type;
4954: l_del_assg_rec WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
4955: l_assignment_dummy_tab WSH_UTIL_CORE.id_tab_type;
4956: l_wms_enabled BOOLEAN;
4957: l_return_status VARCHAR2(2);
4958: l_number_of_warnings NUMBER := 0;
5431: p_return_status => l_return_status,
5432: x_num_warnings => l_number_of_warnings,
5433: x_num_errors => l_number_of_errors
5434: );
5435: WSH_DELIVERY_DETAILS_PKG.create_delivery_details_bulk
5436: ( p_delivery_details_info => l_container_rec,
5437: p_num_of_rec => p_detail_IN_rec.quantity,
5438: p_container_info_rec => l_container_info_rec,
5439: x_return_status => l_return_status,
5463: x_num_warnings => l_number_of_warnings,
5464: x_num_errors => l_number_of_errors
5465: );
5466:
5467: WSH_DELIVERY_DETAILS_PKG.create_deliv_assignment_bulk
5468: ( p_delivery_assignments_info => l_del_assg_rec,
5469: p_num_of_rec => p_detail_IN_rec.quantity,
5470: p_dd_id_tab => l_cont_dummy_tab,
5471: x_da_id_tab => l_assignment_dummy_tab,
5543: p_return_status => l_return_status,
5544: x_num_warnings => l_number_of_warnings,
5545: x_num_errors => l_number_of_errors
5546: );
5547: WSH_DELIVERY_DETAILS_PKG.create_delivery_details_bulk
5548: ( p_delivery_details_info => l_container_rec,
5549: p_num_of_rec => 1,
5550: p_container_info_rec => l_container_info_rec,
5551: x_return_status => l_return_status,
5575: x_num_warnings => l_number_of_warnings,
5576: x_num_errors => l_number_of_errors
5577: );
5578:
5579: WSH_DELIVERY_DETAILS_PKG.create_deliv_assignment_bulk
5580: ( p_delivery_assignments_info => l_del_assg_rec,
5581: p_num_of_rec => 1,
5582: p_dd_id_tab => l_cont_dummy_tab,
5583: x_da_id_tab => l_assignment_dummy_tab,
5691: WSH_WMS_LPN_GRP.g_call_group_api := l_orig_value;
5692: rollback to s_create_containers_DDGPB;
5693: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5694: wsh_util_core.add_message(x_return_status, l_module_name);
5695: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Create_containers');
5696: IF l_debug_on THEN
5697: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5698: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5699: END IF;
5737: l_new_detail_id NUMBER;
5738: l_dummy_rowid VARCHAR2(32767);
5739: l_dummy_assgn_rowid VARCHAR2(32767);
5740: l_delivery_assignment_id NUMBER;
5741: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
5742: l_item_type VARCHAR2(30);
5743:
5744: l_validation_level NUMBER;
5745:
5889: END IF; --}
5890:
5891:
5892: IF l_debug_on THEN
5893: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
5894: END IF;
5895: --
5896: WSH_DELIVERY_DETAILS_PKG.create_delivery_details(
5897: p_delivery_details_info => p_detail_info_tab(l_index),
5892: IF l_debug_on THEN
5893: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
5894: END IF;
5895: --
5896: WSH_DELIVERY_DETAILS_PKG.create_delivery_details(
5897: p_delivery_details_info => p_detail_info_tab(l_index),
5898: x_rowid => l_dummy_rowid,
5899: x_delivery_detail_id => l_new_detail_id,
5900: x_return_status => l_return_status);
5923: l_delivery_assignments_info.parent_delivery_detail_id := NULL;
5924:
5925:
5926: IF l_debug_on THEN
5927: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
5928: END IF;
5929: --
5930: WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Assignments(
5931: p_delivery_assignments_info => l_delivery_assignments_info,
5926: IF l_debug_on THEN
5927: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.CREATE_DELIVERY_ASSIGNMENTS',WSH_DEBUG_SV.C_PROC_LEVEL);
5928: END IF;
5929: --
5930: WSH_DELIVERY_DETAILS_PKG.Create_Delivery_Assignments(
5931: p_delivery_assignments_info => l_delivery_assignments_info,
5932: x_rowid => l_dummy_assgn_rowid,
5933: x_delivery_assignment_id => l_delivery_assignment_id,
5934: x_return_status => l_return_status);
5989: --OTM R12, after loop call split with all the data
5990: IF (l_counter > 1) THEN
5991:
5992: IF l_debug_on THEN
5993: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split',WSH_DEBUG_SV.C_PROC_LEVEL);
5994: END IF;
5995:
5996: WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split(
5997: p_detail_tab => l_delivery_detail_tab,
5992: IF l_debug_on THEN
5993: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split',WSH_DEBUG_SV.C_PROC_LEVEL);
5994: END IF;
5995:
5996: WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split(
5997: p_detail_tab => l_delivery_detail_tab,
5998: p_item_quantity_uom_tab => l_item_quantity_uom_tab,
5999: x_return_status => l_return_status);
6000:
5998: p_item_quantity_uom_tab => l_item_quantity_uom_tab,
5999: x_return_status => l_return_status);
6000:
6001: IF l_debug_on THEN
6002: WSH_DEBUG_SV.logmsg(l_module_name, 'return status from WSH_DELIVERY_DETAILS_SPLITTER.tms_delivery_detail_split: ' || l_return_status);
6003: END IF;
6004:
6005: -- we will not fail based on l_return_status here because
6006: -- we do not want to stop the flow
6076:
6077: ROLLBACK to CREATE_DEL_DETAIL_GRP;
6078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6079: wsh_util_core.add_message(x_return_status, l_module_name);
6080: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Create_Delivery_Detail');
6081: FND_MSG_PUB.Count_And_Get
6082: (
6083: p_count => x_msg_count,
6084: p_data => x_msg_data,
6155: l_new_detail_id NUMBER;
6156: l_dummy_rowid NUMBER;
6157: l_dummy_assgn_rowid NUMBER;
6158: l_delivery_assignment_id NUMBER;
6159: l_delivery_assignments_info WSH_DELIVERY_DETAILS_PKG.Delivery_Assignments_Rec_Type;
6160: l_required_field VARCHAR2(32767);
6161:
6162: l_validation_level NUMBER;
6163: l_org_id NUMBER;
6200: subinventory,
6201: lot_number,
6202: released_status,
6203: requested_quantity_uom
6204: FROM wsh_delivery_details
6205: WHERE delivery_detail_id = x_detail_info_tab(p_counter).delivery_detail_id;
6206: -- AND source_code = p_source_code;
6207:
6208: l_old_delivery_detail_rec c_original_line%ROWTYPE;
6846:
6847: ROLLBACK to VALIDATE_DEL_DETAIL_GRP;
6848: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6849: wsh_util_core.add_message(x_return_status, l_module_name);
6850: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Validate_Delivery_Detail');
6851:
6852: FND_MSG_PUB.Count_And_Get
6853: (
6854: p_count => x_msg_count,
6882: IS
6883:
6884: cursor lock_delivery_details(p_del_det IN NUMBER) is
6885: select delivery_detail_id
6886: from wsh_delivery_details
6887: where delivery_detail_id = p_del_det
6888: for update nowait;
6889:
6890:
6959: END IF;
6960:
6961:
6962: IF l_debug_on THEN
6963: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
6964: END IF;
6965: --
6966: wsh_delivery_details_pkg.Update_Delivery_Details(
6967: p_rowid => p_detail_info_tab(l_index).rowid,
6962: IF l_debug_on THEN
6963: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_PKG.UPDATE_DELIVERY_DETAILS',WSH_DEBUG_SV.C_PROC_LEVEL);
6964: END IF;
6965: --
6966: wsh_delivery_details_pkg.Update_Delivery_Details(
6967: p_rowid => p_detail_info_tab(l_index).rowid,
6968: p_delivery_details_info => p_detail_info_tab(l_index),
6969: x_return_status => l_return_status);
6970:
7168: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7169: WSH_WMS_LPN_GRP.G_CALLBACK_REQUIRED := 'Y';
7170: WSH_WMS_LPN_GRP.g_update_to_container := 'N';
7171: wsh_util_core.add_message(x_return_status, l_module_name);
7172: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Update_Delivery_Detail');
7173:
7174: FND_MSG_PUB.Count_And_Get
7175: (
7176: p_count => x_msg_count,
7376:
7377: ROLLBACK to CANCEL_DEL_DETAIL_GRP;
7378: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7379: wsh_util_core.add_message(x_return_status, l_module_name);
7380: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Delete_Delivery_Detail');
7381: FND_MSG_PUB.Count_And_Get
7382: (
7383: p_count => x_msg_count,
7384: p_data => x_msg_data,
7447: -- HW OPMCONV - Increased length to 80
7448: l_lot VARCHAR2(80);
7449: l_result BOOLEAN;
7450: l_quantity NUMBER;
7451: l_inv_controls wsh_delivery_details_inv.inv_control_flag_rec;
7452: l_ship_method_name VARCHAR2(32767);
7453: -- HW Harmonization project for OPM. Added l_process flag variable
7454: -- HW OPMCONV - Removed OPM variables
7455:
7615:
7616: l_org_id := p_original_rec.organization_id;
7617:
7618: /***************Material Status Impact***********/
7619: WSH_DELIVERY_DETAILS_INV.get_trx_type_id(
7620: p_source_line_id => p_in_detail_rec.source_line_id,
7621: p_source_code => p_in_detail_rec.source_code,
7622: x_transaction_type_id => l_trx_type_id,
7623: x_return_status => l_return_status);
7670: OR p_validation_tab(wsh_actions_levels.c_serial_num_lvl) = 1
7671: THEN
7672: --(
7673: IF l_debug_on THEN
7674: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS', WSH_DEBUG_SV.c_proc_level);
7675: END IF;
7676:
7677: wsh_delivery_details_inv.fetch_inv_controls(
7678: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
7673: IF l_debug_on THEN
7674: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.FETCH_INV_CONTROLS', WSH_DEBUG_SV.c_proc_level);
7675: END IF;
7676:
7677: wsh_delivery_details_inv.fetch_inv_controls(
7678: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
7679: p_inventory_item_id => p_original_rec.inventory_item_id,
7680: p_organization_id => l_org_id,
7681: p_subinventory => l_subinventory,
8030: IF (p_validation_tab(wsh_actions_levels.c_sub_inv_lvl) = 1) THEN
8031: IF (x_detail_rec.subinventory <> fnd_api.g_miss_char) THEN
8032: IF l_debug_on THEN
8033: WSH_DEBUG_SV.log(l_module_name, 'Subinventory', x_detail_rec.subinventory);
8034: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SUBINVENTORY', WSH_DEBUG_SV.c_proc_level);
8035: END IF;
8036:
8037: --
8038: wsh_delivery_details_inv.validate_subinventory(
8034: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SUBINVENTORY', WSH_DEBUG_SV.c_proc_level);
8035: END IF;
8036:
8037: --
8038: wsh_delivery_details_inv.validate_subinventory(
8039: p_subinventory => x_detail_rec.subinventory,
8040: p_organization_id => l_org_id,
8041: p_inventory_item_id => p_original_rec.inventory_item_id,
8042: p_transaction_type_id => l_trx_type_id,
8091: END IF;
8092: ELSE
8093: IF l_debug_on THEN
8094: WSH_DEBUG_SV.log(l_module_name, 'Revision', x_detail_rec.revision);
8095: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_REVISION', WSH_DEBUG_SV.c_proc_level);
8096: END IF;
8097:
8098: wsh_delivery_details_inv.validate_revision(
8099: p_revision => x_detail_rec.revision,
8094: WSH_DEBUG_SV.log(l_module_name, 'Revision', x_detail_rec.revision);
8095: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_REVISION', WSH_DEBUG_SV.c_proc_level);
8096: END IF;
8097:
8098: wsh_delivery_details_inv.validate_revision(
8099: p_revision => x_detail_rec.revision,
8100: p_organization_id => l_org_id,
8101: p_inventory_item_id => p_original_rec.inventory_item_id,
8102: x_return_status => l_return_status,
8152: ELSE
8153:
8154: IF l_debug_on THEN
8155: WSH_DEBUG_SV.log(l_module_name, 'Locator Id', x_detail_rec.locator_id);
8156: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_LOCATOR', WSH_DEBUG_SV.c_proc_level);
8157: END IF;
8158: wsh_delivery_details_inv.validate_locator(
8159: p_locator_id => x_detail_rec.locator_id,
8160: p_organization_id => l_org_id,
8154: IF l_debug_on THEN
8155: WSH_DEBUG_SV.log(l_module_name, 'Locator Id', x_detail_rec.locator_id);
8156: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_LOCATOR', WSH_DEBUG_SV.c_proc_level);
8157: END IF;
8158: wsh_delivery_details_inv.validate_locator(
8159: p_locator_id => x_detail_rec.locator_id,
8160: p_organization_id => l_org_id,
8161: p_inventory_item_id => p_original_rec.inventory_item_id,
8162: p_subinventory => l_subinventory,
8217: END IF;
8218: ELSE
8219: -- bug 4766908 get the reservable_flag
8220: IF p_original_rec.released_status = 'Y' THEN
8221: l_reservable_flag := WSH_DELIVERY_DETAILS_INV.get_reservable_flag(
8222: x_item_id => p_original_rec.inventory_item_id,
8223: x_organization_id => p_original_rec.organization_id,
8224: x_pickable_flag => p_original_rec.pickable_flag);
8225: END IF;
8224: x_pickable_flag => p_original_rec.pickable_flag);
8225: END IF;
8226: IF (nvl(l_reservable_flag, 'Y') = 'Y') THEN
8227: IF l_debug_on THEN
8228: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_LOT_NUMBER', WSH_DEBUG_SV.c_proc_level);
8229: END IF;
8230:
8231: wsh_delivery_details_inv.validate_lot_number(
8232: p_lot_number => x_detail_rec.lot_number,
8227: IF l_debug_on THEN
8228: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_LOT_NUMBER', WSH_DEBUG_SV.c_proc_level);
8229: END IF;
8230:
8231: wsh_delivery_details_inv.validate_lot_number(
8232: p_lot_number => x_detail_rec.lot_number,
8233: p_organization_id => l_org_id,
8234: p_inventory_item_id => p_original_rec.inventory_item_id,
8235: p_revision => l_revision,
8708: -- x_detail_rec.serial_number <> p_original_rec.serial_number)
8709: THEN
8710:
8711: IF l_debug_on THEN
8712: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
8713: WSH_DEBUG_SV.log(l_module_name,'Case1',p_original_rec.serial_number);
8714: END IF;
8715: wsh_delivery_details_inv.unmark_serial_number(
8716: p_delivery_detail_id => p_in_detail_rec.delivery_detail_id,
8711: IF l_debug_on THEN
8712: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
8713: WSH_DEBUG_SV.log(l_module_name,'Case1',p_original_rec.serial_number);
8714: END IF;
8715: wsh_delivery_details_inv.unmark_serial_number(
8716: p_delivery_detail_id => p_in_detail_rec.delivery_detail_id,
8717: p_serial_number_code => l_inv_controls.serial_code,
8718: p_serial_number => p_original_rec.serial_number,
8719: p_transaction_temp_id => NULL,
8741: -- derive serial number from MSNT for this transaction temp id
8742: -- compare with x_detail_rec.serial_number
8743:
8744: IF l_debug_on THEN
8745: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
8746: WSH_DEBUG_SV.log(l_module_name,'Case2',p_original_rec.transaction_temp_id);
8747: END IF;
8748: wsh_delivery_details_inv.unmark_serial_number(
8749: p_delivery_detail_id => p_in_detail_rec.delivery_detail_id,
8744: IF l_debug_on THEN
8745: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UNMARK_SERIAL_NUMBER',WSH_DEBUG_SV.C_PROC_LEVEL);
8746: WSH_DEBUG_SV.log(l_module_name,'Case2',p_original_rec.transaction_temp_id);
8747: END IF;
8748: wsh_delivery_details_inv.unmark_serial_number(
8749: p_delivery_detail_id => p_in_detail_rec.delivery_detail_id,
8750: p_serial_number_code => l_inv_controls.serial_code,
8751: p_serial_number => NULL,
8752: p_transaction_temp_id => p_original_rec.transaction_temp_id,
8778: IF l_det_ser_range_tab(l_det_index).to_serial_number IS NOT NULL THEN -- bug 1578114 condition added
8779: IF l_debug_on THEN
8780: WSH_DEBUG_SV.log(l_module_name, 'To Serial Number', l_det_ser_range_tab(l_det_index).to_serial_number);
8781: WSH_DEBUG_SV.log(l_module_name, 'Transaction Id', l_transaction_id);
8782: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SERIAL_RANGE', WSH_DEBUG_SV.c_proc_level);
8783: END IF;
8784:
8785: --
8786: -- Validates the Range of Serial Number
8783: END IF;
8784:
8785: --
8786: -- Validates the Range of Serial Number
8787: wsh_delivery_details_inv.validate_serial_range(
8788: p_from_serial_number => l_det_ser_range_tab(l_det_index).from_serial_number,
8789: p_to_serial_number => l_det_ser_range_tab(l_det_index).to_serial_number,
8790: p_lot_number => l_lot,
8791: p_organization_id => l_org_id,
8819: p_msg_data => l_msg_data
8820: );
8821: ELSE
8822: IF l_debug_on THEN
8823: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SERIAL', WSH_DEBUG_SV.c_proc_level);
8824: END IF;
8825:
8826: --
8827: wsh_delivery_details_inv.validate_serial(
8823: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.VALIDATE_SERIAL', WSH_DEBUG_SV.c_proc_level);
8824: END IF;
8825:
8826: --
8827: wsh_delivery_details_inv.validate_serial(
8828: p_serial_number => l_det_ser_range_tab(l_det_index).from_serial_number,
8829: p_lot_number => l_lot,
8830: p_organization_id => l_org_id,
8831: p_inventory_item_id => p_original_rec.inventory_item_id,
8867: )
8868: AND l_det_ser_range_tab(l_det_index).to_serial_number IS NOT NULL THEN -- bug 1578114 condition added
8869: IF l_debug_on THEN
8870: WSH_DEBUG_SV.log(l_module_name, 'Transaction id created', l_transaction_id);
8871: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.CREATE_DYNAMIC_SERIAL_RANGE', WSH_DEBUG_SV.c_proc_level);
8872: END IF;
8873:
8874: --
8875: -- Creates/Validates the Range of Serial Number
8872: END IF;
8873:
8874: --
8875: -- Creates/Validates the Range of Serial Number
8876: wsh_delivery_details_inv.create_dynamic_serial_range(
8877: p_from_number => l_det_ser_range_tab(l_det_index).from_serial_number,
8878: p_to_number => l_det_ser_range_tab(l_det_index).to_serial_number,
8879: -- Earlier l_transaction_id was being passed to p_delivery_detail below (frontport from 1159) - jckwok
8880: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
8897: p_msg_data => l_msg_data
8898: );
8899: ELSE
8900: IF l_debug_on THEN
8901: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.CREATE_DYNAMIC_SERIAL', WSH_DEBUG_SV.c_proc_level);
8902: END IF;
8903:
8904: --
8905: wsh_delivery_details_inv.create_dynamic_serial(
8901: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.CREATE_DYNAMIC_SERIAL', WSH_DEBUG_SV.c_proc_level);
8902: END IF;
8903:
8904: --
8905: wsh_delivery_details_inv.create_dynamic_serial(
8906: p_from_number => l_det_ser_range_tab(l_det_index).from_serial_number,
8907: p_to_number => l_det_ser_range_tab(l_det_index).from_serial_number,
8908: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
8909: p_source_line_id => p_original_rec.source_line_id,
9064: --{
9065: IF l_transaction_id IS NOT NULL
9066: THEN
9067: -- (
9068: wsh_delivery_details_inv.mark_serial_number(
9069: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
9070: p_serial_number => NULL,
9071: p_transaction_temp_id => l_transaction_id,
9072: x_return_status => l_return_status
9081: AND p_in_detail_rec.serial_number <> FND_API.G_MISS_CHAR
9082: THEN
9083: --
9084: IF l_debug_on THEN
9085: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.MARK_SERIAL_NUMBER', WSH_DEBUG_SV.c_proc_level);
9086: END IF;
9087:
9088: --
9089: wsh_delivery_details_inv.mark_serial_number(
9085: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling program unit WSH_DELIVERY_DETAILS_INV.MARK_SERIAL_NUMBER', WSH_DEBUG_SV.c_proc_level);
9086: END IF;
9087:
9088: --
9089: wsh_delivery_details_inv.mark_serial_number(
9090: p_delivery_detail_id => x_detail_rec.delivery_detail_id,
9091: p_serial_number => p_in_detail_rec.serial_number,
9092: p_transaction_temp_id => l_transaction_id,
9093: x_return_status => l_return_status
9109: -- newly generated transaction_temp_id in the scenario where
9110: -- only the serial attributes are updated and not the serial #
9111: --
9112: IF l_debug_on THEN
9113: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UPDATE_MARKED_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
9114: END IF;
9115: --
9116: WSH_DELIVERY_DETAILS_INV.Update_Marked_Serial(
9117: p_from_serial_number => p_original_rec.serial_number,
9112: IF l_debug_on THEN
9113: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_DETAILS_INV.UPDATE_MARKED_SERIAL',WSH_DEBUG_SV.C_PROC_LEVEL);
9114: END IF;
9115: --
9116: WSH_DELIVERY_DETAILS_INV.Update_Marked_Serial(
9117: p_from_serial_number => p_original_rec.serial_number,
9118: p_to_serial_number => NULL, -- always pass NULL for single serial number
9119: p_inventory_item_id => p_original_rec.inventory_item_id,
9120: p_organization_id => l_org_id,
9217: WHEN OTHERS THEN
9218: ROLLBACK TO validate_det_line_grp;
9219: x_return_status := fnd_api.g_ret_sts_unexp_error;
9220: wsh_util_core.add_message(x_return_status, l_module_name);
9221: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_GRP.Validate_Detail_Line');
9222: fnd_msg_pub.count_and_get(
9223: p_count => x_msg_count,
9224: p_data => x_msg_data,
9225: p_encoded => fnd_api.g_false);
9424: WHEN OTHERS THEN
9425: ROLLBACK TO VALIDATE_DET_CONTAINER_GRP;
9426: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
9427: wsh_util_core.add_message(x_return_status, l_module_name);
9428: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Validate_Detail_Container');
9429: FND_MSG_PUB.Count_And_Get
9430: (
9431: p_count => x_msg_count,
9432: p_data => x_msg_data,
9760: WHEN OTHERS THEN
9761: ROLLBACK TO VALIDATE_DET_COMMON_GRP;
9762: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9763: wsh_util_core.add_message(x_return_status, l_module_name);
9764: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Validate_Detail_Common');
9765: FND_MSG_PUB.Count_And_Get
9766: (
9767: p_count => x_msg_count,
9768: p_data => x_msg_data,
9876:
9877: WHEN OTHERS THEN
9878: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
9879: wsh_util_core.add_message(x_return_status, l_module_name);
9880: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Get_Serial_Quantity');
9881: --
9882:
9883: IF l_debug_on THEN
9884: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
10699: WHEN OTHERS THEN
10700: ROLLBACK TO CREATE_UPDATE_DEL_DETAIL_GRP2;
10701: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
10702: wsh_util_core.add_message(x_return_status, l_module_name);
10703: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Create_Update_Delivery_Detail');
10704: --
10705: -- Start code for Bugfix 4070732
10706: --
10707: IF upper(WSH_UTIL_CORE.G_START_OF_SESSION_API) = upper(l_api_session_name)
10871:
10872: WHEN OTHERS THEN
10873: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
10874: wsh_util_core.add_message(x_return_status, l_module_name);
10875: WSH_UTIL_CORE.default_handler('WSH_DELIVERY_DETAILS_GRP.Get_Carton_Grouping');
10876: --
10877: IF l_debug_on THEN
10878: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
10879: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10879: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
10880: END IF;
10881: END Get_Carton_Grouping;
10882:
10883: END WSH_DELIVERY_DETAILS_GRP;