[Home] [Help]
267: NULL,
268: NULL,
269: NULL,
270: NULL -- X-dock, is_xdocked_flag
271: FROM wsh_new_deliveries wnd
272: WHERE wnd.delivery_id = p_del_id;
273:
274: i NUMBER;
275: l_grouping_attributes grp_attr_rec_type;
588:
589: END Create_Hash;
590:
591: -- This API will create a hash based on the grouping attributes of the delivery
592: -- and update the hash_value and hash string in wsh_new_deliveries.
593: -- p_delivery_id : Delivery that needs hash value and string to be updated.
594:
595: Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
596: x_return_status OUT NOCOPY varchar2) IS
591: -- This API will create a hash based on the grouping attributes of the delivery
592: -- and update the hash_value and hash string in wsh_new_deliveries.
593: -- p_delivery_id : Delivery that needs hash value and string to be updated.
594:
595: Procedure Create_Update_Hash(p_delivery_rec IN OUT NOCOPY WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type,
596: x_return_status OUT NOCOPY varchar2) IS
597:
598: l_grp_attr_tab grp_attr_tab_type;
599: l_tmp_grp_attr_tab grp_attr_tab_type;
1019: p_ship_method_code in varchar2,
1020: p_organization_id in number ,
1021: p_ship_method_grp_flag in varchar2 ) is
1022: select delivery_id
1023: from wsh_new_deliveries wnd
1024: where wnd.hash_value = p_hash_value
1025: and wnd.hash_string = p_hash_string
1026: and wnd.organization_id = p_organization_id
1027: and (NVL(wnd.planned_flag, 'N') = 'N')
1046: p_organization_id in number,
1047: p_customer_id in number ,
1048: p_ship_method_grp_flag in varchar2) is
1049: select delivery_id
1050: from wsh_new_deliveries wnd
1051: where wnd.hash_value = p_hash_value
1052: and wnd.hash_string = p_hash_string
1053: and wnd.organization_id = p_organization_id
1054: and wnd.customer_id = p_customer_id
1072: p_ship_method_code in varchar2,
1073: p_ship_method_grp_flag in varchar2
1074: ) is
1075: select delivery_id
1076: from wsh_new_deliveries wnd
1077: where wnd.hash_value = p_hash_value
1078: and wnd.hash_string = p_hash_string
1079: and wnd.batch_id = p_batch_id
1080: and (NVL(wnd.planned_flag, 'N') = 'N')
1087:
1088: --BUG 3383843
1089: CURSOR c_dlvy_for_cont(p_organization_id NUMBER, p_ship_from_loc_id NUMBER) IS
1090: select delivery_id
1091: from wsh_new_deliveries d
1092: where d.status_code IN ('OP','SA')
1093: and d.planned_flag = 'N'
1094: and NVL(p_ship_from_loc_id, nvl(d.initial_pickup_location_id, -1))
1095: = nvl(d.initial_pickup_location_id, -1)
1403: ELSIF p_action_rec.output_format_type = 'SQL_STRING' THEN
1404: -- Return the string and the variables that need to be bound.
1405:
1406: l_query_string := 'select delivery_id '||
1407: 'from wsh_new_deliveries wnd '||
1408: 'where wnd.hash_value = :p_hash_value '||
1409: 'and wnd.hash_string = :p_hash_string '||
1410: 'and wnd.status_code = ''OP'' ';
1411:
1647: --
1648: -----------------------------------------------------------------------------
1649:
1650: PROCEDURE Autonomous_Create_Delivery (
1651: p_delivery_info IN wsh_new_deliveries_pvt.delivery_rec_type,
1652: x_rowid OUT NOCOPY VARCHAR2,
1653: x_delivery_id OUT NOCOPY NUMBER,
1654: x_delivery_name OUT NOCOPY VARCHAR2,
1655: x_return_status OUT NOCOPY VARCHAR2)
1671: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1672:
1673: IF l_debug_on THEN
1674: WSH_DEBUG_SV.push(l_module_name);
1675: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1676: END IF;
1677: WSH_NEW_DELIVERIES_PVT.Create_Delivery(p_delivery_info, x_rowid, x_delivery_id, x_delivery_name, x_return_status);
1678: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1679: IF l_debug_on THEN
1673: IF l_debug_on THEN
1674: WSH_DEBUG_SV.push(l_module_name);
1675: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
1676: END IF;
1677: WSH_NEW_DELIVERIES_PVT.Create_Delivery(p_delivery_info, x_rowid, x_delivery_id, x_delivery_name, x_return_status);
1678: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1679: IF l_debug_on THEN
1680: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY ');
1681: END IF;
1676: END IF;
1677: WSH_NEW_DELIVERIES_PVT.Create_Delivery(p_delivery_info, x_rowid, x_delivery_id, x_delivery_name, x_return_status);
1678: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1679: IF l_debug_on THEN
1680: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY ');
1681: END IF;
1682: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1683: IF l_debug_on THEN
1684: WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
1680: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY ');
1681: END IF;
1682: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
1683: IF l_debug_on THEN
1684: WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
1685: END IF;
1686: RAISE OTHERS;
1687: ELSE
1688: IF l_debug_on THEN
1685: END IF;
1686: RAISE OTHERS;
1687: ELSE
1688: IF l_debug_on THEN
1689: WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED ERROR');
1690: WSH_DEBUG_SV.pop(l_module_name);
1691: END IF;
1692: ROLLBACK;
1693: RETURN;
1701: IF l_debug_on THEN
1702: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
1703: END IF;
1704: wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
1705: (p_table_name => 'WSH_NEW_DELIVERIES',
1706: p_primary_id => x_delivery_id,
1707: x_return_status => x_return_status);
1708:
1709: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1789: p_carrier_id in number,
1790: p_mode_of_transport in varchar2,
1791: p_service_level in varchar2) is
1792: select delivery_id, name, rowid
1793: from wsh_new_deliveries wnd
1794: where wnd.hash_value = p_hash_value
1795: and wnd.hash_string = p_hash_string
1796: and wnd.batch_id = p_batch_id
1797: and (NVL(wnd.planned_flag, 'N') = 'N')
1801: and NVL(NVL(wnd.mode_of_transport, p_mode_of_transport), -1) = NVL(NVL(p_mode_of_transport, wnd.mode_of_transport), -1)
1802: and wnd.status_code in ('OP', 'SA');
1803:
1804: l_group_info grp_attr_tab_type;
1805: l_delivery_info wsh_new_deliveries_pvt.delivery_rec_type;
1806: l_rowid VARCHAR2(30);
1807: l_delivery_id NUMBER;
1808: l_delivery_name wsh_new_deliveries.name%TYPE;
1809: l_weight_uom_code VARCHAR2(10);
1804: l_group_info grp_attr_tab_type;
1805: l_delivery_info wsh_new_deliveries_pvt.delivery_rec_type;
1806: l_rowid VARCHAR2(30);
1807: l_delivery_id NUMBER;
1808: l_delivery_name wsh_new_deliveries.name%TYPE;
1809: l_weight_uom_code VARCHAR2(10);
1810: l_volume_uom_code VARCHAR2(10);
1811:
1812: l_assigned_flag VARCHAR2(1) := 'N';
2037: END IF;
2038: --
2039: FND_MESSAGE.SET_NAME('WSH', 'WSH_DET_ASSIGNED_DEL');
2040: FND_MESSAGE.SET_TOKEN('DET_NAME', l_attr_tab(i).entity_id);
2041: FND_MESSAGE.SET_TOKEN('DEL_NAME', WSH_NEW_DELIVERIES_PVT.get_name(l_attr_tab(i).DELIVERY_ID));
2042: wsh_util_core.add_message(WSH_UTIL_CORE.G_RET_STS_ERROR, l_module_name);
2043:
2044: l_assigned_flag := 'Y';
2045:
2197: --}
2198: ELSE
2199: --{ non-parallel-pick case
2200: IF l_debug_on THEN
2201: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2202: END IF;
2203: wsh_new_deliveries_pvt.create_delivery(l_delivery_info, l_rowid, l_delivery_id, l_delivery_name, x_return_status);
2204: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2205: IF l_debug_on THEN
2199: --{ non-parallel-pick case
2200: IF l_debug_on THEN
2201: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY',WSH_DEBUG_SV.C_PROC_LEVEL);
2202: END IF;
2203: wsh_new_deliveries_pvt.create_delivery(l_delivery_info, l_rowid, l_delivery_id, l_delivery_name, x_return_status);
2204: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2205: IF l_debug_on THEN
2206: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY FOR '||P_LINE_ROWS ( I ) );
2207: END IF;
2202: END IF;
2203: wsh_new_deliveries_pvt.create_delivery(l_delivery_info, l_rowid, l_delivery_id, l_delivery_name, x_return_status);
2204: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2205: IF l_debug_on THEN
2206: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY FOR '||P_LINE_ROWS ( I ) );
2207: END IF;
2208: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2209: IF l_debug_on THEN
2210: WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
2206: WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR IN WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY FOR '||P_LINE_ROWS ( I ) );
2207: END IF;
2208: IF (x_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
2209: IF l_debug_on THEN
2210: WSH_DEBUG_SV.logmsg(l_module_name,'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED UNEXPECTED ERROR');
2211: END IF;
2212: raise others;
2213: ELSE
2214: IF l_debug_on THEN
2211: END IF;
2212: raise others;
2213: ELSE
2214: IF l_debug_on THEN
2215: WSH_DEBUG_SV.logmsg(l_module_name, 'PROC WSH_NEW_DELIVERIES_PVT.CREATE_DELIVERY RETURNED ERROR');
2216: END IF;
2217: raise e_return_excp;
2218: END IF;
2219: END IF;
2227: IF l_debug_on THEN
2228: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_FLEXFIELD_UTILS.WRITE_DFF_ATTRIBUTES',WSH_DEBUG_SV.C_PROC_LEVEL);
2229: END IF;
2230: wsh_flexfield_utils.WRITE_DFF_ATTRIBUTES
2231: (p_table_name => 'WSH_NEW_DELIVERIES',
2232: p_primary_id => l_delivery_id,
2233: x_return_status => x_return_status);
2234:
2235: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3144: END IF;
3145:
3146: --OTM R12, allow delete of 'NS' deliveries
3147: IF (l_gc3_is_installed = 'Y') THEN
3148: DELETE FROM wsh_new_deliveries wnd
3149: WHERE batch_id = p_batch_id
3150: AND NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
3151: = WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
3152: AND NOT EXISTS (
3146: --OTM R12, allow delete of 'NS' deliveries
3147: IF (l_gc3_is_installed = 'Y') THEN
3148: DELETE FROM wsh_new_deliveries wnd
3149: WHERE batch_id = p_batch_id
3150: AND NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
3151: = WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
3152: AND NOT EXISTS (
3153: SELECT 'x'
3154: FROM wsh_delivery_assignments wda
3147: IF (l_gc3_is_installed = 'Y') THEN
3148: DELETE FROM wsh_new_deliveries wnd
3149: WHERE batch_id = p_batch_id
3150: AND NVL(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)
3151: = WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT
3152: AND NOT EXISTS (
3153: SELECT 'x'
3154: FROM wsh_delivery_assignments wda
3155: WHERE wda.delivery_id = wnd.delivery_id
3155: WHERE wda.delivery_id = wnd.delivery_id
3156: AND wda.delivery_id IS NOT NULL);
3157: --END OTM R12
3158: ELSE
3159: DELETE FROM wsh_new_deliveries wnd
3160: WHERE batch_id = p_batch_id
3161: AND NOT EXISTS (
3162: SELECT 'x'
3163: FROM wsh_delivery_assignments wda
3820: -- x_return_status: status.
3821: **/
3822:
3823: PROCEDURE Autocreate_Consol_Delivery(
3824: p_del_attributes_tab IN WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type,
3825: p_caller IN VARCHAR2,
3826: p_trip_prefix IN VARCHAR2,
3827: x_parent_del_id OUT NOCOPY NUMBER,
3828: x_parent_trip_id OUT NOCOPY NUMBER,
3831:
3832: CURSOR c_trip_info(p_delivery_id IN NUMBER) IS
3833: SELECT s1.trip_id,
3834: NVL(d.ignore_for_planning, 'N') --OTM R12, delivery ignore same as trip's
3835: FROM wsh_delivery_legs l, wsh_trip_stops s1, wsh_new_deliveries d
3836: WHERE l.delivery_id = p_delivery_id
3837: AND d.delivery_id = l.delivery_id
3838: AND s1.stop_id = l.pick_up_stop_id
3839: AND s1.stop_location_id = d.initial_pickup_location_id;
3853: l_trip_id NUMBER;
3854: l_trip_id_temp NUMBER;
3855: -- l_trip_info_rec_tab is table of c_trip_info%rowtype index by binary_integer;
3856:
3857: l_del_attributes WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
3858: l_del_tab WSH_UTIL_CORE.id_tab_type;
3859: l_trip_del_tab WSH_UTIL_CORE.id_tab_type;
3860: l_intermediate_loc_tab WSH_UTIL_CORE.id_tab_type;
3861: l_pickup_stop_id NUMBER;
4163:
4164: -- Create the delivery
4165:
4166: IF l_debug_on THEN
4167: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_new_deliveries_pvt.create_delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
4168: END IF;
4169:
4170: wsh_new_deliveries_pvt.create_delivery(l_del_attributes, l_rowid, l_delivery_id, l_delivery_name, l_return_status);
4171:
4166: IF l_debug_on THEN
4167: WSH_DEBUG_SV.logmsg(l_module_name,'wsh_new_deliveries_pvt.create_delivery',WSH_DEBUG_SV.C_PROC_LEVEL);
4168: END IF;
4169:
4170: wsh_new_deliveries_pvt.create_delivery(l_del_attributes, l_rowid, l_delivery_id, l_delivery_name, l_return_status);
4171:
4172: wsh_util_core.api_post_call
4173: (
4174: p_return_status => l_return_status,