[Home] [Help]
95: (wda.delivery_id IS NULL
96: )
97: OR
98: (exists ( select 'x'
99: from wsh_new_deliveries wnd
100: where wnd.delivery_id = wda.delivery_id
101: AND wnd.planned_flag = 'N'
102: AND wnd.status_code IN ('OP', 'SA')
103: AND wnd.delivery_id <> NVL(x_plan_delivery_id, 0)
276:
277:
278: PROCEDURE copy_delivery_record(
279: p_plan_delivery_rec IN plan_delivery_rec_type,
280: x_delivery_attrs_rec OUT NOCOPY wsh_new_deliveries_pvt.delivery_rec_type,
281: x_return_status OUT NOCOPY VARCHAR2
282: );
283:
284: PROCEDURE copy_trip_record(
1684: FROM wsh_trips_interface wti,
1685: wsh_trip_stops_interface wtsi,
1686: wsh_del_legs_interface wdli,
1687: wsh_new_del_interface wndi,
1688: wsh_new_deliveries wnd
1689: WHERE wti.group_id = p_group_id
1690: AND wti.interface_action_code = G_TP_RELEASE_CODE
1691: AND wti.trip_interface_id = wtsi.trip_interface_id
1692: AND wtsi.stop_interface_id = wdli.pick_up_stop_interface_id
2047: x_return_status => l_return_status);
2048: IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
2049: stamp_interface_error(
2050: p_group_id => x_context.group_id,
2051: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2052: p_entity_interface_id => idel.delivery_interface_id,
2053: p_message_name => 'WSH_TP_F_CONVERT_LOC', --!!! new message
2054: p_token_1_name => 'PLAN_DEL_NUM',
2055: p_token_1_value => idel.tp_delivery_number,
2069: OR idel.wsh_ultimate_do_loc_id <> idel.ultimate_dropoff_location_id) THEN
2070:
2071: stamp_interface_error(
2072: p_group_id => x_context.group_id,
2073: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2074: p_entity_interface_id => idel.delivery_interface_id,
2075: p_message_name => 'WSH_TP_F_DEL_DIFF_ATTR',
2076: p_token_1_name => 'DELIVERY_NAME',
2077: p_token_1_value => idel.wsh_name,
2095: IF idel.delivery_id IS NOT NULL THEN
2096: IF idel.status_code NOT IN ('OP', 'SA') THEN
2097: stamp_interface_error(
2098: p_group_id => x_context.group_id,
2099: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2100: p_entity_interface_id => idel.delivery_interface_id,
2101: p_message_name => 'WSH_TP_F_DEL_NOT_OPEN',
2102: p_token_1_name => 'DELIVERY_NAME',
2103: p_token_1_value => idel.wsh_name,
2112:
2113: IF idel.ignore_for_planning = 'Y' THEN
2114: stamp_interface_error(
2115: p_group_id => x_context.group_id,
2116: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2117: p_entity_interface_id => idel.delivery_interface_id,
2118: p_message_name => 'WSH_TP_F_DEL_IGNORED',
2119: p_token_1_name => 'DELIVERY_NAME',
2120: p_token_1_value => idel.wsh_name,
2131: RETURN;
2132: END IF;
2133:
2134: BEGIN
2135: wsh_new_deliveries_pvt.lock_dlvy_no_compare(
2136: p_delivery_id => idel.delivery_id);
2137: EXCEPTION
2138: WHEN OTHERS THEN
2139: stamp_interface_error(
2137: EXCEPTION
2138: WHEN OTHERS THEN
2139: stamp_interface_error(
2140: p_group_id => x_context.group_id,
2141: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2142: p_entity_interface_id => idel.delivery_interface_id,
2143: p_message_name => 'WSH_TP_F_NO_LOCK_DEL',
2144: p_token_1_name => 'DELIVERY_NAME',
2145: p_token_1_value => idel.wsh_name,
2158: EXCEPTION
2159: WHEN OTHERS THEN
2160: stamp_interface_error(
2161: p_group_id => x_context.group_id,
2162: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2163: p_entity_interface_id => idel.delivery_interface_id,
2164: p_message_name => 'WSH_TP_F_NO_LOCK_DEL_CONTENTS',
2165: p_token_1_name => 'DELIVERY_NAME',
2166: p_token_1_value => idel.wsh_name,
2376: -- and its contents cannot be changed.
2377: -- That means we cannot assign this candidate to the mapped delivery.
2378: stamp_interface_error(
2379: p_group_id => x_context.group_id,
2380: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2381: p_entity_interface_id => iline.dd_interface_id,
2382: p_message_name => 'WSH_TP_I_LINE_NOT_ASSIGNED',
2383: p_token_1_name => 'DETAIL_ID',
2384: p_token_1_value => candidate.delivery_detail_id,
2479: -- Lock its delivery if it will be unassigned
2480: IF candidate.delivery_id IS NOT NULL
2481: AND candidate.delivery_id <> NVL(x_plan_deliveries(l_target_delivery_index).delivery_id,0) THEN
2482: BEGIN
2483: wsh_new_deliveries_pvt.lock_dlvy_no_compare(
2484: p_delivery_id => candidate.delivery_id);
2485: EXCEPTION
2486: WHEN OTHERS THEN
2487: stamp_interface_error(
2489: p_entity_table_name => 'WSH_DEL_DETAILS_INTERFACE',
2490: p_entity_interface_id => iline.dd_interface_id,
2491: p_message_name => 'WSH_TP_I_NO_LOCK_DEL',
2492: p_token_1_name => 'DELIVERY_NAME',
2493: p_token_1_value => WSH_NEW_DELIVERIES_PVT.get_name(candidate.delivery_id),
2494: x_errors_tab => x_errors_tab,
2495: x_return_status => l_return_status);
2496: GOTO next_candidate;
2497: END;
2903: EXCEPTION
2904: WHEN OTHERS THEN
2905: stamp_interface_error(
2906: p_group_id => x_context.group_id,
2907: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
2908: p_entity_interface_id => x_plan_details(x_track_conts(l_cont_index).plan_dd_index).dd_interface_id,
2909: p_message_name => 'WSH_TP_F_NO_LOCK_LPN_CONTENTS',
2910: p_token_1_name => 'CONTAINER_NAME',
2911: p_token_1_value => wsh_container_utilities.get_cont_name(x_track_conts(l_cont_index).topmost_cont_id),
3361: p_entity_table_name => 'WSH_DEL_DETAILS_INTERFACE',
3362: p_entity_interface_id => x_used_details(l_new_index).dd_interface_id,
3363: p_message_name => 'WSH_TP_F_FIRM_DEL_UNUSED',
3364: p_token_1_name => 'DELIVERY_NAME',
3365: p_token_1_value => WSH_NEW_DELIVERIES_PVT.get_name(
3366: x_plan_deliveries(l_target_delivery_index).delivery_id),
3367: p_token_2_name => 'PLAN_TRIP_NUM',
3368: p_token_2_value => get_plan_trip_num(x_context),
3369: p_token_3_name => 'PLAN_DEL_NUM',
3778: p_entity_interface_id => x_plan_deliveries(l_plan_del_index).del_interface_id,
3779: p_message_name => 'WSH_TP_F_NO_LOCK_LEGS',
3780: p_token_1_name => 'DELIVERY_NAME',
3781: p_token_1_value =>
3782: WSH_NEW_DELIVERIES_PVT.get_name(x_plan_deliveries(l_plan_del_index).delivery_id),
3783: x_errors_tab => x_errors_tab,
3784: x_return_status => l_return_status);
3785: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
3786: IF l_debug_on THEN
4268: p_entity_table_name => 'WSH_TRIPS_INTERFACE',
4269: p_entity_interface_id => x_plan_deliveries(l_plan_del_index).del_interface_id,
4270: p_message_name => 'WSH_TP_F_DEL_FIRM_LEG_DIFF',
4271: p_token_1_name => 'DELIVERY_NAME',
4272: p_token_1_value => wsh_new_deliveries_pvt.get_name(x_plan_deliveries(l_plan_del_index).delivery_id),
4273: p_token_2_name => 'PLAN_TRIP_NUM',
4274: p_token_2_value => x_plan_trips(1).tp_trip_number,
4275: x_errors_tab => x_errors_tab,
4276: x_return_status => l_return_status);
4299: p_entity_table_name => 'WSH_NEW_DEL_INTERFACE',
4300: p_entity_interface_id => x_plan_deliveries(l_plan_del_index).del_interface_id,
4301: p_message_name => 'WSH_TP_F_TRIP_FIRM_NO_UNASSIGN',
4302: p_token_1_name => 'DELIVERY_NAME',
4303: p_token_1_value => wsh_new_deliveries_pvt.get_name(x_plan_deliveries(l_plan_del_index).delivery_id),
4304: p_token_2_name => 'PLAN_TRIP_NUM',
4305: p_token_2_value => x_plan_trips(1).tp_trip_number,
4306: x_errors_tab => x_errors_tab,
4307: x_return_status => l_return_status);
4702: l_sd_dd_count NUMBER := 0;
4703: l_skip_step BOOLEAN;
4704: l_is_inbound BOOLEAN;
4705:
4706: l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
4707: l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
4708: l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
4709: l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
4710: l_del_in_rec WSH_DELIVERIES_GRP.DEL_IN_REC_TYPE;
4729: -- Checks if the delivery has wv_frozen flag set to 'Y'
4730: -- Used when unassigning from delivery not in plan.
4731: cursor c_check_del_wv_frozen(p_delivery_id in NUMBER) IS
4732: select initial_pickup_location_id
4733: from wsh_new_deliveries
4734: where wv_frozen_flag = 'Y'
4735: and delivery_id = p_delivery_id;
4736:
4737: -- Checks if the stop has wv_frozen flag set to 'Y'.
5957: END IF;
5958:
5959: DECLARE
5960: -- list of deliveries to become contents firmed.
5961: l_cf_dels WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
5962: -- list of deliveries to become routing and contents firmed.
5963: l_rcf_dels WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
5964: l_discard_return_status VARCHAR2(1);
5965: BEGIN --{
5959: DECLARE
5960: -- list of deliveries to become contents firmed.
5961: l_cf_dels WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
5962: -- list of deliveries to become routing and contents firmed.
5963: l_rcf_dels WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
5964: l_discard_return_status VARCHAR2(1);
5965: BEGIN --{
5966:
5967: l_index := x_plan_deliveries.FIRST;
6646: wdd.ship_from_location_id,
6647: wdd.earliest_pickup_date,
6648: wdd.latest_dropoff_date
6649: from wsh_delivery_details wdd,
6650: wsh_new_deliveries wnd,
6651: wsh_delivery_assignments_v wda
6652: where wdd.delivery_detail_id = wda.delivery_detail_id
6653: and wda.delivery_id = wnd.delivery_id
6654: and wnd.delivery_id is not null
6722: l_message_name VARCHAR2(30);
6723: l_msg_data VARCHAR2(32767);
6724: l_msg_count NUMBER;
6725:
6726: l_del_attrs WSH_NEW_DELIVERIES_PVT.delivery_attr_tbl_type;
6727: l_del_action_prms WSH_DELIVERIES_GRP.action_parameters_rectype;
6728: l_del_action_rec WSH_DELIVERIES_GRP.delivery_action_out_rec_type;
6729: l_del_defaults WSH_DELIVERIES_GRP.default_parameters_rectype;
6730:
7056:
7057: IF dd.earliest_pickup_date > l_initial_pu_date THEN
7058: -- Log exception.
7059: IF l_delivery_name is null THEN
7060: l_delivery_name := wsh_new_deliveries_pvt.get_name(x_plan_deliveries(i).delivery_id);
7061: END IF;
7062: FND_MESSAGE.SET_NAME('WSH', 'WSH_TP_EARLY_PICKUP');
7063: FND_MESSAGE.SET_TOKEN('SHIP_DATE',FND_DATE.DATE_TO_DISPLAYDT(l_initial_pu_date));
7064: FND_MESSAGE.SET_TOKEN('PICK_DATE',FND_DATE.DATE_TO_DISPLAYDT(dd.earliest_pickup_date));
7086:
7087: IF dd.latest_dropoff_date < l_final_do_date THEN
7088: -- Log exception.
7089: IF l_delivery_name is null THEN
7090: l_delivery_name := wsh_new_deliveries_pvt.get_name(x_plan_deliveries(i).delivery_id);
7091: END IF;
7092: FND_MESSAGE.SET_NAME('WSH', 'WSH_TP_LATE_DROPOFF');
7093: FND_MESSAGE.SET_TOKEN('DROP_DATE',FND_DATE.DATE_TO_DISPLAYDT(l_final_do_date));
7094: FND_MESSAGE.SET_TOKEN('DELIVER_DATE',FND_DATE.DATE_TO_DISPLAYDT(dd.latest_dropoff_date));
7938: IF l_found THEN
7939: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7940: stamp_interface_error(
7941: p_group_id => x_context.group_id,
7942: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
7943: p_entity_interface_id => x_plan_deliveries(p_delivery_index).del_interface_id,
7944: p_message_name => 'WSH_TP_F_DEL_LINE', -- new message
7945: p_token_1_name => 'PLAN_DEL_NUM',
7946: p_token_1_value => x_plan_deliveries(p_delivery_index).tp_delivery_number,
7964: IF l_plan_lpn_count = 0 THEN
7965: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7966: stamp_interface_error(
7967: p_group_id => x_context.group_id,
7968: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
7969: p_entity_interface_id => x_plan_deliveries(p_delivery_index).del_interface_id,
7970: p_message_name => 'WSH_TP_F_NO_PLAN_LPNS', -- new message
7971: p_token_1_name => 'PLAN_DEL_NUM',
7972: p_token_1_value => x_plan_deliveries(p_delivery_index).tp_delivery_number,
7995: IF NOT l_found THEN
7996: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
7997: stamp_interface_error(
7998: p_group_id => x_context.group_id,
7999: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
8000: p_entity_interface_id => x_plan_deliveries(p_delivery_index).del_interface_id,
8001: p_message_name => 'WSH_TP_F_NEW_LPN', -- new message
8002: p_token_1_name => 'PLAN_DEL_NUM',
8003: p_token_1_value => x_plan_deliveries(p_delivery_index).tp_delivery_number,
8021: IF l_found AND l_plan_lpn_count <> l_te_lpn_count THEN
8022: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8023: stamp_interface_error(
8024: p_group_id => x_context.group_id,
8025: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
8026: p_entity_interface_id => x_plan_deliveries(p_delivery_index).del_interface_id,
8027: p_message_name => 'WSH_TP_F_LPN_MISMATCH', -- new message
8028: p_token_1_name => 'PLAN_DEL_NUM',
8029: p_token_1_value => x_plan_deliveries(p_delivery_index).tp_delivery_number,
8061: -- probably error due to lock on LPN.
8062: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
8063: stamp_interface_error(
8064: p_group_id => x_context.group_id,
8065: p_entity_table_name => 'WSH_NEW_DELIVERIES_INTERFACE',
8066: p_entity_interface_id => x_plan_deliveries(p_delivery_index).del_interface_id,
8067: p_message_name => 'WSH_TP_F_DANGL_LPN_NO_LOCK', -- new message
8068: p_token_1_name => 'PLAN_DEL_NUM',
8069: p_token_1_value => x_plan_deliveries(p_delivery_index).tp_delivery_number,
8311: p_entity_table_name => 'WSH_NEW_DEL_INTERFACE',
8312: p_entity_interface_id => x_plan_deliveries(i).del_interface_id,
8313: p_message_name => 'WSH_TP_F_PLAN_DEL_NOT_MATCH',
8314: p_token_1_name => 'DELIVERY_NAME',
8315: p_token_1_value => WSH_NEW_DELIVERIES_PVT.get_name(
8316: x_plan_deliveries(i).delivery_id),
8317: p_token_2_name => 'PLAN_TRIP_NUM',
8318: p_token_2_value => get_plan_trip_num(x_context),
8319: p_token_3_name => 'PLAN_DEL_NUM',
8897: d.organization_id,
8898: l.pick_up_stop_id,
8899: l.drop_off_stop_id
8900: FROM wsh_delivery_legs l,
8901: wsh_new_deliveries d
8902: WHERE l.pick_up_stop_id = p_stop_id
8903: AND d.delivery_id = l.delivery_id
8904: UNION
8905: SELECT l.delivery_id,
8906: d.organization_id,
8907: l.pick_up_stop_id,
8908: l.drop_off_stop_id
8909: FROM wsh_delivery_legs l,
8910: wsh_new_deliveries d
8911: WHERE l.drop_off_stop_id = p_stop_id
8912: AND d.delivery_id = l.delivery_id;
8913:
8914: l_trip_count NUMBER;
9181:
9182: IF l_delivery_matches = 'N' THEN
9183: -- Lock the delivery.
9184: BEGIN
9185: wsh_new_deliveries_pvt.lock_dlvy_no_compare(
9186: p_delivery_id => leg.delivery_id);
9187: EXCEPTION
9188: WHEN OTHERS THEN
9189: l_tmp_entity_name := WSH_NEW_DELIVERIES_PVT.Get_Name(leg.delivery_id);
9185: wsh_new_deliveries_pvt.lock_dlvy_no_compare(
9186: p_delivery_id => leg.delivery_id);
9187: EXCEPTION
9188: WHEN OTHERS THEN
9189: l_tmp_entity_name := WSH_NEW_DELIVERIES_PVT.Get_Name(leg.delivery_id);
9190: stamp_interface_error(
9191: p_group_id => x_context.group_id,
9192: p_entity_table_name => 'WSH_TRIP_INTERFACE',
9193: p_entity_interface_id => x_plan_trips(i).trip_interface_id,
9631: -- transforms plan data structure into group api data structure for delivery.
9632: --
9633: PROCEDURE copy_delivery_record(
9634: p_plan_delivery_rec IN plan_delivery_rec_type,
9635: x_delivery_attrs_rec OUT NOCOPY wsh_new_deliveries_pvt.delivery_rec_type,
9636: x_return_status OUT NOCOPY VARCHAR2
9637: ) IS
9638: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'COPY_DELIVERY_RECORD';
9639: --
9657: vendor_id,
9658: party_id,
9659: shipping_control,
9660: shipment_direction
9661: FROM wsh_new_deliveries
9662: WHERE delivery_id = x_delivery_id;
9663:
9664: l_group_flags wsh_delivery_autocreate.group_by_flags_rec_type;
9665: l_return_status VARCHAR2(1);