[Home] [Help]
PACKAGE: APPS.WSH_TP_RELEASE_INT
Source
1 PACKAGE WSH_TP_RELEASE_INT AUTHID CURRENT_USER as
2 /* $Header: WSHTPRES.pls 120.0 2005/05/26 18:29:00 appldev noship $ */
3
4
5 TYPE interface_errors_tab_type IS TABLE OF WSH_INTERFACE_ERRORS%ROWTYPE INDEX BY BINARY_INTEGER;
6
7 TYPE plan_detail_rec_type IS RECORD (
8 dd_interface_id NUMBER,
9 delivery_detail_id NUMBER,
10 tp_delivery_detail_id NUMBER,
11 mapped_quantity NUMBER,
12 mapped_quantity_uom WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY_UOM%TYPE,
13 map_split_flag VARCHAR2(1),
14 released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE,
15 move_order_line_id NUMBER,
16 line_direction WSH_DELIVERY_DETAILS.LINE_DIRECTION%TYPE,
17 source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE,
18 source_header_id NUMBER,
19 source_line_set_id NUMBER,
20 source_line_id NUMBER,
21 po_shipment_line_id NUMBER, -- scope for mapping inbound/drop details
22 ship_set_id NUMBER,
23 top_model_line_id NUMBER,
24 ato_line_id NUMBER,
25 ship_model_complete_flag WSH_DELIVERY_DETAILS.SHIP_MODEL_COMPLETE_FLAG%TYPE,
26 ship_from_location_id NUMBER,
27 ship_to_location_id NUMBER,
28 organization_id NUMBER,
29 customer_id NUMBER,
30 fob_code WSH_DELIVERY_DETAILS.FOB_CODE%TYPE,
31 freight_terms_code WSH_DELIVERY_DETAILS.FREIGHT_TERMS_CODE%TYPE,
32 intmed_ship_to_location_id NUMBER,
33 ship_method_code WSH_DELIVERY_DETAILS.SHIP_METHOD_CODE%TYPE,
34 mode_of_transport WSH_DELIVERY_DETAILS.MODE_OF_TRANSPORT%TYPE,
35 service_level WSH_DELIVERY_DETAILS.SERVICE_LEVEL%TYPE,
36 carrier_id NUMBER,
37 shipping_control WSH_DELIVERY_DETAILS.SHIPPING_CONTROL%TYPE,
38 vendor_id WSH_DELIVERY_DETAILS.VENDOR_ID%TYPE,
39 party_id WSH_DELIVERY_DETAILS.PARTY_ID%TYPE,
40 topmost_cont_id NUMBER,
41 current_delivery_id NUMBER,
42 target_delivery_index NUMBER,
43 wv_frozen_flag WSH_DELIVERY_DETAILS.WV_FROZEN_FLAG%TYPE
44 );
45 TYPE plan_detail_tab_type IS TABLE OF plan_detail_rec_type INDEX BY BINARY_INTEGER;
46
47
48
49
50
51 TYPE plan_delivery_rec_type IS RECORD (
52 del_interface_id NUMBER,
53 delivery_id NUMBER,
54 tp_delivery_number WSH_NEW_DELIVERIES.TP_DELIVERY_NUMBER%TYPE,
55 tp_plan_name WSH_NEW_DELIVERIES.TP_PLAN_NAME%TYPE,
56 planned_flag WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE,
57 wsh_planned_flag WSH_NEW_DELIVERIES.PLANNED_FLAG%TYPE,
58 initial_pickup_location_id NUMBER,
59 ultimate_dropoff_location_id NUMBER,
60 initial_pickup_date DATE,
61 ultimate_dropoff_date DATE,
62 ship_method_code WSH_NEW_DELIVERIES.SHIP_METHOD_CODE%TYPE,
63 mode_of_transport WSH_NEW_DELIVERIES.MODE_OF_TRANSPORT%TYPE,
64 service_level WSH_NEW_DELIVERIES.SERVICE_LEVEL%TYPE,
65 freight_terms_code WSH_NEW_DELIVERIES.FREIGHT_TERMS_CODE%TYPE,
66 name WSH_NEW_DELIVERIES.NAME%TYPE,
67 loading_sequence WSH_NEW_DELIVERIES.LOADING_SEQUENCE%TYPE,
68 loading_order_flag WSH_NEW_DELIVERIES.LOADING_ORDER_FLAG%TYPE,
69 fob_location_id WSH_NEW_DELIVERIES.FOB_LOCATION_ID%TYPE,
70 waybill WSH_NEW_DELIVERIES.WAYBILL%TYPE,
71 currency_code WSH_NEW_DELIVERIES.CURRENCY_CODE%TYPE,
72 party_id WSH_NEW_DELIVERIES.PARTY_ID%TYPE,
73 shipping_control WSH_NEW_DELIVERIES.SHIPPING_CONTROL%TYPE,
74 vendor_id WSH_NEW_DELIVERIES.VENDOR_ID%TYPE,
75 organization_id NUMBER,
79 carrier_id NUMBER,
76 customer_id NUMBER,
77 fob_code WSH_DELIVERY_DETAILS.FOB_CODE%TYPE,
78 intmed_ship_to_location_id NUMBER,
80 shipment_direction WSH_NEW_DELIVERIES.SHIPMENT_DIRECTION%TYPE,
81 additional_shipment_info WSH_NEW_DELIVERIES.ADDITIONAL_SHIPMENT_INFO%TYPE,
82 gross_weight WSH_NEW_DELIVERIES.GROSS_WEIGHT%TYPE,
83 net_weight WSH_NEW_DELIVERIES.NET_WEIGHT%TYPE,
84 weight_uom_code WSH_NEW_DELIVERIES.WEIGHT_UOM_CODE%TYPE,
85 volume WSH_NEW_DELIVERIES.VOLUME%TYPE,
86 volume_uom_code WSH_NEW_DELIVERIES.VOLUME_UOM_CODE%TYPE,
87 pooled_ship_to_location_id WSH_NEW_DELIVERIES.POOLED_SHIP_TO_LOCATION_ID%TYPE,
88 dock_code WSH_NEW_DELIVERIES.DOCK_CODE%TYPE,
89 ilines_count NUMBER, -- count interface lines in plan
90 lines_count NUMBER, -- count delivery lines mapped
91 s_lines_count NUMBER, -- count delivery lines released to warehouse
92 dangling_conts_count NUMBER, -- count dangling topmost containers
93 wms_org_flag VARCHAR2(1),
94 assign_details_count NUMBER,
95 leg_base_index NUMBER,
96 wv_frozen_flag WSH_NEW_DELIVERIES.WV_FROZEN_FLAG%TYPE,
97 physical_ultimate_do_loc_id NUMBER -- internal org location to drop off
98 );
99 TYPE plan_delivery_tab_type IS TABLE OF plan_delivery_rec_type INDEX BY BINARY_INTEGER;
100
101
102
103 TYPE plan_leg_rec_type IS RECORD (
104 leg_interface_id NUMBER,
105 delivery_leg_id NUMBER,
106 del_interface_id NUMBER,
107 delivery_index NUMBER,
108 pickup_stop_index NUMBER,
109 dropoff_stop_index NUMBER,
110 trip_index NUMBER
111 );
112 TYPE plan_leg_tab_type IS TABLE OF plan_leg_rec_type INDEX BY BINARY_INTEGER;
113
114
115
116 TYPE plan_stop_rec_type IS RECORD (
117 stop_interface_id NUMBER,
118 stop_id NUMBER,
119 tp_stop_id NUMBER,
120 trip_index NUMBER,
121 stop_location_id NUMBER,
122 stop_sequence_number NUMBER,
123 planned_arrival_date DATE,
124 planned_departure_date DATE,
125 departure_gross_weight NUMBER,
126 departure_net_weight NUMBER,
127 weight_uom_code WSH_TRIP_STOPS.WEIGHT_UOM_CODE%TYPE,
128 departure_volume NUMBER,
129 volume_uom_code WSH_TRIP_STOPS.VOLUME_UOM_CODE%TYPE,
130 departure_seal_code WSH_TRIP_STOPS.DEPARTURE_SEAL_CODE%TYPE,
131 departure_fill_percent NUMBER,
132 wkend_layover_stops WSH_TRIP_STOPS.wkend_layover_stops%TYPE,
133 wkday_layover_stops WSH_TRIP_STOPS.wkday_layover_stops%TYPE,
134 shipments_type_flag WSH_TRIP_STOPS.shipments_type_flag%TYPE,
135 wv_frozen_flag WSH_TRIP_STOPS.wv_frozen_flag%TYPE,
136 internal_do_count NUMBER, -- count of internal customer location drop-offs
137 external_pd_count NUMBER, -- count of pickup/dropoff activities at this physical stop
138 wsh_physical_location_id NUMBER -- if populated, plan stop has been mapped to a TE internal stop
139 );
140 TYPE plan_stop_tab_type IS TABLE OF plan_stop_rec_type INDEX BY BINARY_INTEGER;
141
142
143
144 TYPE plan_trip_rec_type IS RECORD (
145 trip_interface_id NUMBER,
146 trip_id NUMBER,
147 tp_plan_name WSH_TRIPS.TP_PLAN_NAME%TYPE,
148 tp_trip_number WSH_TRIPS.TP_TRIP_NUMBER%TYPE,
149 planned_flag WSH_TRIPS.PLANNED_FLAG%TYPE,
150 wsh_planned_flag WSH_TRIPS.PLANNED_FLAG%TYPE,
151 stop_base_index NUMBER,
152 name WSH_TRIPS.NAME%TYPE,
153 vehicle_item_id WSH_TRIPS.VEHICLE_ITEM_ID%TYPE,
154 vehicle_organization_id WSH_TRIPS.VEHICLE_ORGANIZATION_ID%TYPE,
155 vehicle_num_prefix WSH_TRIPS.VEHICLE_NUM_PREFIX%TYPE,
156 vehicle_number WSH_TRIPS.VEHICLE_NUMBER%TYPE,
157 carrier_id WSH_TRIPS.CARRIER_ID%TYPE,
158 ship_method_code WSH_TRIPS.SHIP_METHOD_CODE%TYPE,
159 route_id WSH_TRIPS.ROUTE_ID%TYPE,
160 routing_instructions WSH_TRIPS.ROUTING_INSTRUCTIONS%TYPE,
161 service_level WSH_TRIPS.SERVICE_LEVEL%TYPE,
162 mode_of_transport WSH_TRIPS.MODE_OF_TRANSPORT%TYPE,
163 freight_terms_code WSH_TRIPS.FREIGHT_TERMS_CODE%TYPE,
164 seal_code WSH_TRIPS.SEAL_CODE%TYPE,
165 shipments_type_flag WSH_TRIPS.SHIPMENTS_TYPE_FLAG%TYPE,
166 consolidation_allowed WSH_TRIPS.CONSOLIDATION_ALLOWED%TYPE,
167 schedule_id WSH_TRIPS.SCHEDULE_ID%TYPE,
168 route_lane_id WSH_TRIPS.ROUTE_LANE_ID%TYPE,
169 lane_id WSH_TRIPS.LANE_ID%TYPE,
170 booking_number WSH_TRIPS.BOOKING_NUMBER%TYPE,
171 vessel WSH_TRIPS.VESSEL%TYPE,
172 voyage_number WSH_TRIPS.VOYAGE_NUMBER%TYPE,
173 port_of_loading WSH_TRIPS.PORT_OF_LOADING%TYPE,
174 port_of_discharge WSH_TRIPS.PORT_OF_DISCHARGE%TYPE,
175 carrier_contact_id WSH_TRIPS.CARRIER_CONTACT_ID%TYPE,
176 shipper_wait_time WSH_TRIPS.SHIPPER_WAIT_TIME%TYPE,
180 linked_stop_count NUMBER -- count of physical stops with both internal drop offs and external activities
177 wait_time_uom WSH_TRIPS.WAIT_TIME_UOM%TYPE,
178 carrier_response WSH_TRIPS.CARRIER_RESPONSE%TYPE,
179 operator WSH_TRIPS.OPERATOR%TYPE,
181 );
182 TYPE plan_trip_tab_type IS TABLE OF plan_trip_rec_type INDEX BY BINARY_INTEGER;
183
184
185 TYPE used_detail_rec_type IS RECORD (
186 delivery_detail_id NUMBER,
187 dd_interface_id NUMBER,
188 available_quantity NUMBER,
189 available_quantity_uom WSH_DELIVERY_DETAILS.REQUESTED_QUANTITY_UOM%TYPE,
190 current_delivery_id NUMBER,
191 topmost_cont_id NUMBER,
192 target_delivery_index NUMBER,
193 track_cont_content_found BOOLEAN,
194 released_status VARCHAR2(1),
195 move_order_line_id NUMBER,
196 split_count NUMBER,
197 need_unassignment BOOLEAN,
198 organization_id NUMBER,
199 line_direction WSH_DELIVERY_DETAILS.LINE_DIRECTION%TYPE
200 );
201 TYPE used_details_tab_type IS TABLE OF used_detail_rec_type INDEX BY BINARY_INTEGER;
202
203
204
205 TYPE track_cont_rec_type IS RECORD(
206 topmost_cont_id NUMBER,
207 line_dd_interface_id NUMBER,
208 plan_dd_index NUMBER, -- inbound/drop: index to x_plan_details for first line
209 current_delivery_id NUMBER,
210 target_delivery_index NUMBER,
211 lines_staged BOOLEAN,
212 cont_content_base_index NUMBER,
213 lpn_id NUMBER,
214 released_status VARCHAR2(1),
215 source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE,
216 organization_id NUMBER
217 );
218 TYPE track_cont_tab_type IS TABLE OF track_cont_rec_type INDEX BY BINARY_INTEGER;
219
220
221
222 TYPE track_cont_content_rec_type IS RECORD(
223 track_cont_index NUMBER,
224 delivery_detail_id NUMBER
225 );
226 TYPE track_cont_content_tab_type IS TABLE OF track_cont_content_rec_type INDEX BY BINARY_INTEGER;
227
228
229
230 TYPE delivery_unassign_rec IS RECORD (
231 delivery_detail_id NUMBER,
232 delivery_id NUMBER,
233 organization_id NUMBER,
234 container_flag VARCHAR2(1),
235 lines_staged BOOLEAN,
236 wms_org_flag VARCHAR2(1),
237 source_code WSH_DELIVERY_DETAILS.SOURCE_CODE%TYPE,
238 released_status WSH_DELIVERY_DETAILS.RELEASED_STATUS%TYPE,
239 lpn_id NUMBER,
240 plan_dd_index NUMBER, -- index to x_plan_details for plan line
241 plan_del_index NUMBER, -- index to x_plan_deliveries for plan delivery
242 wv_frozen_flag WSH_NEW_DELIVERIES.WV_FROZEN_FLAG%TYPE,
243 initial_pickup_location_id WSH_NEW_DELIVERIES.INITIAL_PICKUP_LOCATION_ID%TYPE
244 );
245 TYPE delivery_unassign_tab_type IS TABLE OF delivery_unassign_rec INDEX BY BINARY_INTEGER;
246
247
248
249 TYPE trip_unassign_rec IS RECORD (
250 delivery_id NUMBER,
251 organization_id NUMBER,
252 trip_id NUMBER,
253 trip_index NUMBER,
254 delivery_leg_id NUMBER,
255 pickup_stop_id NUMBER,
256 dropoff_stop_id NUMBER
257 );
258 TYPE trip_unassign_tab_type IS TABLE OF trip_unassign_rec INDEX BY BINARY_INTEGER;
259
260
261 TYPE obsoleted_stop_rec IS RECORD (
262 trip_id NUMBER,
263 stop_id NUMBER
264 );
265 TYPE obsoleted_stop_tab_type IS TABLE OF obsoleted_stop_rec INDEX BY BINARY_INTEGER;
266
267
268 TYPE context_rec_type IS RECORD (
269 group_id NUMBER,
270 wms_in_group BOOLEAN,
271 wv_exception_details wsh_util_core.id_tab_type,
272 wv_exception_dels wsh_util_core.id_tab_type,
273 wv_exception_stops wsh_util_core.id_tab_type,
274 auto_tender_flag VARCHAR2(1),
275 linked_trip_count NUMBER -- count of trips having linked stops
276 );
277
278
279
280 --
281 -- Procedure: release_plan
282 -- Parameters:
283 -- p_group_ids list of group_ids to process their
284 -- WSH_TRIPS_INTERFACE records and
285 -- their associated tables' records.
286 -- p_commit_flag Y - commit changes; N - do not commit
287 -- x_return_status return status
288 --
289 -- Description:
290 -- Reconciliate shipping data with the transportation
291 -- plan populated in the WSH and FTE interface tables.
292 --
293 --
294 PROCEDURE release_plan(
295 p_group_ids IN WSH_TP_RELEASE_GRP.id_tab_type,
296 p_commit_flag IN VARCHAR2,
297 x_return_status OUT NOCOPY VARCHAR2);
298
299
300 --
301 -- Procedure: purge_interface_tables
302 -- Parameters:
303 -- p_group_ids list of group_ids to purge their
304 -- WSH_TRIPS_INTERFACE records and
308 -- x_return_status return status
305 -- their associated tables' records.
306 -- WSH_INTERFACE_ERRORS will be purged.
307 -- p_commit_flag Y - commit changes; N - do not commit
309 --
310 -- Description:
311 -- Delete the records from WSH and FTE interface tables.
312 --
313 PROCEDURE purge_interface_tables(
314 p_group_ids IN WSH_TP_RELEASE_GRP.id_tab_type,
315 p_commit_flag IN VARCHAR2,
316 x_return_status OUT NOCOPY VARCHAR2);
317
318
319
320 --
321 -- Procedure: stamp_interface_error
322 -- Parameters:
323 -- p_group_id group identifier where the error is found
324 -- p_entity_table_name entity table where the error is found
325 -- p_entity_interface_id record where the error is found
326 -- p_message_name message name identifying the error
327 -- p_message_appl message application name (NULL means 'WSH')
328 -- p_message_text optional text for output to the user-
329 -- p_token_1_name optional token 1 name
330 -- p_token_1_value optional token 1 value
331 -- p_token_2_name optional token 2 name
332 -- p_token_2_value optional token 2 value
333 -- p_token_3_name optional token 3 name
334 -- p_token_3_value optional token 3 value
335 -- p_token_4_name optional token 4 name
336 -- p_token_4_value optional token 4 value
337 -- p_errors_tab list of errors to insert into wsh_interface_errors at the end
338 -- x_return_status return status
339 --
340 -- Description:
341 -- puts the error information into the list p_errors_tab
342 --
343 PROCEDURE stamp_interface_error(
344 p_group_id IN NUMBER,
345 p_entity_table_name IN VARCHAR2,
346 p_entity_interface_id IN NUMBER,
347 p_message_name IN VARCHAR2,
348 p_message_appl IN VARCHAR2 DEFAULT NULL,
349 p_message_text IN VARCHAR2 DEFAULT NULL,
350 p_token_1_name IN VARCHAR2 DEFAULT NULL,
351 p_token_1_value IN VARCHAR2 DEFAULT NULL,
352 p_token_2_name IN VARCHAR2 DEFAULT NULL,
353 p_token_2_value IN VARCHAR2 DEFAULT NULL,
354 p_token_3_name IN VARCHAR2 DEFAULT NULL,
355 p_token_3_value IN VARCHAR2 DEFAULT NULL,
356 p_token_4_name IN VARCHAR2 DEFAULT NULL,
357 p_token_4_value IN VARCHAR2 DEFAULT NULL,
358 x_errors_tab IN OUT NOCOPY interface_errors_tab_type,
359 x_return_status OUT NOCOPY VARCHAR2);
360
361
362
363 END WSH_TP_RELEASE_INT;