[Home] [Help]
1: PACKAGE BODY WSH_DELIVERY_DETAILS_UTILITIES as
2: /* $Header: WSHDDUTB.pls 120.9.12020000.2 2012/07/04 10:28:39 suppal ship $ */
3:
4:
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_UTILITIES';
1: PACKAGE BODY WSH_DELIVERY_DETAILS_UTILITIES as
2: /* $Header: WSHDDUTB.pls 120.9.12020000.2 2012/07/04 10:28:39 suppal ship $ */
3:
4:
5: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_DELIVERY_DETAILS_UTILITIES';
6:
7: --Forward Declaration
8: -- TPW - Distributed Organization Changes
9: PROCEDURE Validate_Delivery_Line (
38: PROCEDURE Auto_Assign_Deliveries(
39: p_line_rows IN OUT NOCOPY wsh_delivery_autocreate.grp_attr_tab_type,
40: p_group_by_header IN VARCHAR2,
41: p_check_fte_compatibility IN VARCHAR2,
42: x_assigned_rows OUT NOCOPY WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl,
43: x_unassigned_rows OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
44: x_appended_del_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
45: x_return_status OUT NOCOPY VARCHAR2) IS
46:
120: WHERE d.delivery_id = t.id
121: AND NOT EXISTS (
122: SELECT /*+ no_unnest */ WDA.delivery_detail_id
123: FROM wsh_delivery_assignments_v WDA,
124: wsh_delivery_details WDD
125: WHERE WDA.delivery_detail_id = WDD.delivery_detail_id
126: AND WDA.delivery_id = t.id
127: AND WDA.delivery_id is not NULL
128: AND WDD.source_code <> 'OE'
415: l_action_prms.caller := 'WSH_DLMG';
416:
417:
418: -- assign delivery details of this sub-group to the delivery
419: WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action(
420: p_api_version_number => 1.0,
421: p_init_msg_list => FND_API.G_FALSE,
422: p_commit => FND_API.G_FALSE,
423: x_return_status => l_return_status,
429: x_defaults => l_defaults,
430: x_action_out_rec => l_action_out_rec);
431:
432: IF l_debug_on THEN
433: WSH_DEBUG_SV.log(l_module_name,'Return status from WSH_DELIVERY_DETAILS_GRP.Delivery_Detail_Action '|| l_return_status);
434: END IF;
435:
436: -- OTM R12 : update delivery
437: l_tms_update := 'N';
952: -- if the cursor is still open then close the cursor
953: IF c_get_deliveries%ISOPEN THEN
954: CLOSE c_get_deliveries;
955: END IF;
956: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_UTILITIES.Auto_Assign_Deliveries');
957: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
958: IF l_debug_on THEN
959: WSH_DEBUG_SV.pop(l_module_name);
960: END IF;
1016: p_group_by_header IN VARCHAR2,
1017: p_commit IN VARCHAR2,
1018: p_lock_rows IN VARCHAR2,
1019: p_check_fte_compatibility IN VARCHAR2,
1020: x_appended_det_tbl OUT NOCOPY WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl,
1021: x_unappended_det_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
1022: x_appended_del_tbl OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
1023: x_return_status OUT NOCOPY VARCHAR2) IS
1024:
1046: wdd.source_code,
1047: wdd.lpn_id,
1048: wsp.appending_limit,
1049: wdd.ignore_for_planning --bugfix 7164767
1050: FROM wsh_delivery_details wdd,
1051: wsh_delivery_assignments_v wda,
1052: wsh_shipping_parameters wsp
1053: WHERE wdd.delivery_detail_id = c_detail_id
1054: AND wda.delivery_detail_id = wdd.delivery_detail_id
1064:
1065: l_detail_info WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1066:
1067: l_unassigned_rows WSH_UTIL_CORE.Id_Tab_Type;
1068: l_assgined_rows WSH_DELIVERY_DETAILS_UTILITIES.delivery_assignment_rec_tbl;
1069: l_index NUMBER;
1070: l_delivery_detail_id NUMBER;
1071:
1072: l_group_info WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1151: CLOSE get_line_details_check;
1152: -- these are the lines that will be grouped
1153: IF p_lock_rows = FND_API.G_TRUE THEN
1154: -- lock the delivery detail record
1155: select delivery_detail_id into l_delivery_detail_id from wsh_delivery_details where
1156: delivery_detail_id = p_delivery_detail_tbl(l_index) for update nowait;
1157: END IF;
1158: IF NVL(p_append_flag, l_appending_limit) = 'N' and WSH_PICK_LIST.G_BATCH_ID is NULL THEN
1159: x_unappended_det_tbl(x_unappended_det_tbl.count+1) := l_detail_info(l_index).entity_id;
1296: CLOSE get_line_details_check;
1297: END IF;
1298:
1299: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1300: wsh_util_core.default_handler('WSH_DELIVERY_DETAILS_UTILITIES.Append_to_Deliveries');
1301: IF l_debug_on THEN
1302: wsh_debug_sv.log(l_module_name, 'Unexpected error has occured. Oracle error message is ' || SQLERRM);
1303: wsh_debug_sv.pop(l_module_name, 'EXCEPTION:OTHERS');
1304: END IF;
1341: src_requested_quantity_uom,
1342: requested_quantity,
1343: requested_quantity_uom,
1344: inventory_item_id
1345: from wsh_delivery_details
1346: where source_code= p_source_code
1347: and released_status not in ( 'D', 'C' )
1348: and shipment_batch_id is not null
1349: and source_line_id = c_source_line_id
1351:
1352: CURSOR c_open_detail_qty (c_source_line_id IN NUMBER)
1353: IS
1354: select nvl(sum(requested_quantity), 0)
1355: from wsh_delivery_details
1356: where source_code = p_source_code
1357: and released_status in ( 'R', 'X', 'B' ) -- Check with klr
1358: and source_line_id = c_source_line_id
1359: and shipment_batch_id is null
1446: x_return_status => l_return_status );
1447:
1448: --
1449: IF l_debug_on THEN
1450: WSH_DEBUG_SV.log(l_module_name, 'Return Status of WSH_DELIVERY_DETAILS_PUB.Split_Line', l_return_status);
1451: END IF;
1452: --
1453:
1454: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1607: IS
1608: CURSOR c_delivery_line_info(c_line_id NUMBER)
1609: IS
1610: select wdd.*
1611: from wsh_delivery_details wdd
1612: where wdd.source_line_id = c_line_id
1613: and wdd.source_code = 'OE'
1614: and wdd.released_status not in ( 'D', 'C' )
1615: and wdd.shipment_batch_id is not null
2166: -- TPW - Distributed Organization Changes - End
2167:
2168:
2169:
2170: END WSH_DELIVERY_DETAILS_UTILITIES;
2171:
2172: