1 PACKAGE WSH_TRIPS_ACTIONS as
2 /* $Header: WSHTRACS.pls 120.2.12000000.1 2007/01/16 05:51:31 appldev ship $ */
3
4 -- trip stops are created 10 minutes apart
5 C_TEN_MINUTES CONSTANT NUMBER := 1/144;
6 C_TEN_SECONDS CONSTANT NUMBER := 1/8640;
7
8
9 TYPE cs_trip_rec_type IS RECORD (
10 Trip_id wsh_trips.trip_id%TYPE,
11 Trip_name wsh_trips.name%TYPE,
12 Planned_flag wsh_trips.planned_flag%TYPE,
13 Status_code wsh_trips.status_code%TYPE,
14 Carrier_id wsh_trips.carrier_id%TYPE,
15 Mode_of_transport wsh_trips.mode_of_transport%TYPE,
16 Service_level wsh_trips.service_level%TYPE,
17 Ship_method_code wsh_trips.ship_method_code%TYPE,
18 --Track_only_flag wsh_trips.track_only_flag%TYPE,
19 Consignee_car_ac_no wsh_trips.consignee_carrier_ac_no%TYPE,
20 Freight_terms_code wsh_trips.freight_terms_code%TYPE,
21 Load_tender_status wsh_trips.load_tender_status%TYPE,
22 Lane_id wsh_trips.lane_id%TYPE,
23 Rank_id wsh_trips.rank_id%TYPE);
24
25 TYPE cs_trip_tab_type IS TABLE OF cs_trip_rec_type INDEX BY BINARY_INTEGER;
26
27 TYPE cs_stop_rec_type IS RECORD (
28 STOP_ID wsh_trip_stops.stop_id%TYPE
29 , TRIP_ID wsh_trip_stops.trip_id%TYPE
30 --To handle dummy locations #DUM_LOC(S)
31 , STOP_LOCATION_ID wsh_trip_stops.stop_location_id%TYPE
32 , STATUS_CODE wsh_trip_stops.STATUS_CODE%TYPE
33 , STOP_SEQUENCE_NUMBER wsh_trip_stops.STOP_SEQUENCE_NUMBER%TYPE
34 , PLANNED_ARRIVAL_DATE wsh_trip_stops.PLANNED_ARRIVAL_DATE%TYPE
35 , PLANNED_DEPARTURE_DATE wsh_trip_stops.PLANNED_DEPARTURE_DATE%TYPE
36 , ACTUAL_ARRIVAL_DATE wsh_trip_stops.ACTUAL_ARRIVAL_DATE%TYPE
37 , ACTUAL_DEPARTURE_DATE wsh_trip_stops.ACTUAL_DEPARTURE_DATE%TYPE
38 --DUM_LOC(S)
39 , PHYSICAL_LOCATION_ID wsh_trip_stops.PHYSICAL_LOCATION_ID%TYPE
40 --#DUM_LOC(E)
41 , PHYSICAL_STOP_ID wsh_trip_stops.PHYSICAL_STOP_ID%TYPE
42 , pick_up_weight wsh_trip_stops.pick_up_weight%TYPE
43 , weight_uom_code wsh_trip_stops.weight_uom_code%TYPE
44 , pick_up_volume wsh_trip_stops.pick_up_volume%TYPE
45 , volume_uom_code wsh_trip_stops.volume_uom_code%TYPE
46 );
47
48 TYPE cs_stop_tab_type IS TABLE OF cs_stop_rec_type INDEX BY BINARY_INTEGER;
49
50 -- SSN change
51 FUNCTION Get_Stop_Seq_Mode return NUMBER;
52
53 -- PROCEDURE Get_Trip_Defaults
54 -- This procedure will return a record of trip defaults used for trip confirmation
55
56 PROCEDURE Get_Trip_Defaults(p_trip_id in NUMBER,
57 p_trip_name in VARCHAR2 DEFAULT NULL,
58 x_def_rec IN OUT NOCOPY WSH_TRIPS_GRP.default_parameters_rectype,
59 x_return_Status OUT NOCOPY varchar2 ) ;
60
61 --
62 -- Procedure: Confirm_Trip
63 -- Parameters: p_trip_id, p_action_flag, p_intransit_flag,
64 -- p_close_flag, p_stage_del_flag, p_report_set_id,
65 -- p_ship_method, p_actual_dep_date, p_bol_flag, p_defer_interface_flag,
66 -- x_return_status
67 -- Description: Selects all the Stops for the Trip, if the Trip is passed
68 -- and calls Confirm_Stop for each Stop.
69
70
71 PROCEDURE Confirm_Trip (
72 p_trip_id IN NUMBER,
73 p_action_flag IN VARCHAR2,
74 p_intransit_flag IN VARCHAR2,
75 p_close_flag IN VARCHAR2,
76 p_stage_del_flag IN VARCHAR2,
77 p_report_set_id IN NUMBER,
78 p_ship_method IN VARCHAR2,
79 p_actual_dep_date IN DATE,
80 p_bol_flag IN VARCHAR2,
81 p_defer_interface_flag IN VARCHAR2,
82 p_mbol_flag IN VARCHAR2, -- Added MBOL flag
83 x_return_status OUT NOCOPY VARCHAR2) ;
84
85
86
87 --
88 -- Procedure: Plan
89 -- Parameters: p_trip_rows, p_action, x_return_status
90 -- Description: Sets the planned flag of the trip to 'Y'or 'N' after
91 -- performing plan/unplan action checks. P_action has the values
92 -- - 'PLAN'
93 -- - 'UNPLAN'
94
95 PROCEDURE Plan(
96 p_trip_rows IN wsh_util_core.id_tab_type,
97 p_action IN VARCHAR2,
98 x_return_status OUT NOCOPY VARCHAR2);
99
100
101 --
102 -- Procedure: Change_Status
103 -- Parameters: p_trip_rows, p_status_code, x_return_status
104 -- Description: Sets the status code of the trip to p_status_code after
105 -- performing status code checks.
106 -- p_trip_rows - a table of trip_ids
107
108 PROCEDURE Change_Status (
109 p_trip_id IN NUMBER,
110 p_status_code IN VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2);
112
113 --
114 -- Procedure: Autocreate_Trip_multi
115 -- New procedure which gets the ignore_for_planning flag
116 -- and groups them on the basis of that.
117 -- Parameters: p_del_rows, x_trip_ids, x_trip_names, x_return_status
118 -- Description: Autocreates trip from a table of deliveries
119 -- p_del_rows - a table of delivery_ids
120 -- p_entity - indicates the entity on which the action is
121 -- being performed. It can be either
122 -- 'D' : delivery
123 -- 'L' : Line
124 -- x_trip_ids - the autocreated trip ids
125 -- x_trip_names - the autocreated trip names
126
127 PROCEDURE autocreate_trip_multi(
128 p_del_rows IN wsh_util_core.id_tab_type,
129 p_entity IN VARCHAR2 DEFAULT 'D',
130 x_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
131 x_trip_names OUT NOCOPY wsh_util_core.Column_Tab_Type,
132 x_return_status OUT NOCOPY VARCHAR2);
133
134 --
135 -- Procedure: Autocreate_Trip
136 -- Parameters: p_del_rows, x_trip_id, x_trip_name, x_return_status
137 -- Description: Autocreates trip from a table of deliveries
138 -- p_del_rows - a table of delivery_ids
139 -- p_entity - indicates the entity on which the action is
140 -- being performed. It can be either
141 -- 'D' : delivery
142 -- 'L' : Line
143 -- x_trip_id - the autocreated trip id
144 -- x_trip_name - the autocreated trip name
145 -- NOTE: Trip Stop/Delivery Leg sequences are not generated automatically
146
147 PROCEDURE autocreate_trip(
148 p_del_rows IN wsh_util_core.id_tab_type,
149 p_entity IN VARCHAR2 DEFAULT 'D',
150 x_trip_id OUT NOCOPY NUMBER,
151 x_trip_name OUT NOCOPY VARCHAR2,
152 x_return_status OUT NOCOPY VARCHAR2,
153 p_sc_pickup_date IN DATE DEFAULT NULL,
154 p_sc_dropoff_date IN DATE DEFAULT NULL);
155
156
157
158 --
159 -- Procedure: Autocreate_Del_Trip
160 -- Parameters: p_line_rows, p_init_flag, p_max_detail_commit,
161 -- p_del_rows, x_trip_id, x_trip_name, x_return_status
162 -- Description: Autocreates trip from a table of delivery details
163 -- Calls Autocreate_deliveries to create deliveries and then
164 -- Autocreate_trip to create trip, stops and delivery legs
165 -- p_line_rows - a table of delivery_detail_ids
166 -- p_org_rows - a table of organization_ids. If this table is not available to pass
167 -- - then pass a dummy value in. the table will get regenerated when
168 -- - calling WSH_DELIVERY_AUTOCREATE.autocreate_del_across_orgs
169 -- x_trip_id - the autocreated trip id
170 -- x_trip_name - the autocreated trip name
171 -- NOTE: Trip Stop/Delivery Leg sequences are not generated automatically
172
173 --Compatibility Changes - removed trip_id, trip_name and added x_trip_rows
174 PROCEDURE autocreate_del_trip(
175 p_line_rows IN wsh_util_core.id_tab_type,
176 p_org_rows IN wsh_util_core.id_tab_type,
177 p_max_detail_commit IN NUMBER := 1000,
178 x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
179 x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
180 x_return_status OUT NOCOPY VARCHAR2);
181
182 --
183 -- Procedure: Assign_Trip (for delivery details)
184 -- Parameters: p_line_rows, p_trip_id, p_del_rows, x_return_status
185 -- Description: Autocreates deliveries from a table of delivery details
186 -- and then assigns these deliveries to the trip
187 -- p_line_rows - table of delivery_detail_ids
188 -- p_trip_id - the trip id to assign details to
189 -- x_del_rows - table of autocreated deliveries
190 -- NOTE: Trip Stop/Delivery Leg sequences are not generated automatically
191
192 PROCEDURE assign_trip(
193 p_line_rows IN wsh_util_core.id_tab_type,
194 p_trip_id IN NUMBER,
195 x_del_rows OUT NOCOPY wsh_util_core.id_tab_type,
196 x_return_status OUT NOCOPY VARCHAR2);
197
198 --
199 -- Procedure: Assign_Trip (for deliveries)
200 -- Description: Assigns a table of deliveries to a trip
201 -- p_del_rows - a table of delivery_ids
202 -- p_trip_id - the trip id to assign deliveries to
203 -- p_pickup_stop_id - the stop id to pickup deliveries (optional)
204 -- p_dropoff_stop_id - the stop id to dropoff deliveries (optional)
205 -- p_pickup_location_id - the stop location id to pickup deliveries (optional)
206 -- p_dropoff_location_id - the stop location id to dropoff deliveries (optional)
207
208 -- NOTE: Trip Stop/Delivery Leg sequences are not generated automatically
209 /* H integration - for sequence number added parameters */
210
211
212 PROCEDURE assign_trip(
213 p_del_rows IN wsh_util_core.id_tab_type,
214 p_trip_id IN NUMBER,
215 p_pickup_stop_id IN NUMBER := NULL,
216 p_pickup_stop_seq IN NUMBER := NULL,
217 p_dropoff_stop_id IN NUMBER := NULL,
218 p_dropoff_stop_seq IN NUMBER := NULL,
219 p_pickup_location_id IN NUMBER := NULL,
220 p_dropoff_location_id IN NUMBER := NULL,
221 p_pickup_arr_date IN DATE := to_date(NULL),
222 p_pickup_dep_date IN DATE := to_date(NULL),
223 p_dropoff_arr_date IN DATE := to_date(NULL),
224 p_dropoff_dep_date IN DATE := to_date(NULL),
225 x_return_status OUT VARCHAR2,
226 p_caller IN VARCHAR2 DEFAULT NULL);
227
228
229
230 --
231 -- Procedure: Trip_Weight_Volume
232 -- Parameters: p_trip_rows, p_start_departure_date,x_return_status
233 -- Description: Calculates stop wt/vol for multiple trips
234 -- p_trip_rows - the trip ids to calculate wt/vol
235 -- p_override_flag - automatically updates the trip wt/vol
236 -- p_calc_wv_if_frozen - 'Y' if Manually entered W/V can be overridden
237 -- p_start_departure_date - the departure date from which point
238 -- onward all stops wt/vol is calculated
239 -- p_calc_del_wv - If value is 'Y', the W/V will be recalculated using delivery W/V apis
240 -- Else W/V on delivery will be used
241 -- x_return_status - this returns an error if all trips had errors.
242 -- p_suppress_errors - 'Y': do not return error for #2 and #3 below (bug 2366163)
243 -- Each trip can have errors if
244 -- 1) no trip stops are found
245 -- 2) any trip stop does not have uom info
246 -- 3) delivery wt/vol calculation fails
247
248 PROCEDURE Trip_weight_volume(
249 p_trip_rows IN wsh_util_core.id_tab_type,
250 p_override_flag IN VARCHAR2,
251 p_calc_wv_if_frozen IN VARCHAR2 DEFAULT 'Y',
252 p_start_departure_date IN DATE,
253 p_calc_del_wv IN VARCHAR2 DEFAULT 'Y',
254 x_return_status OUT NOCOPY VARCHAR2,
255 p_suppress_errors IN VARCHAR2 DEFAULT NULL,
256 p_caller IN VARCHAR2 DEFAULT NULL);
257
258
259
260 --
261 -- Procedure: Validate_Stop_Sequence
262 -- Parameters: p_trip_id, x_return_status
263 -- Description: Validate sequence of a trip based on planned dates
264 -- p_trip_id - the trip id to validate stop sequences
265 -- x_return_status - this returns an error if the sequence is invalid
266
267 PROCEDURE Validate_Stop_Sequence(
268 p_trip_id IN NUMBER,
269 x_return_status OUT NOCOPY VARCHAR2);
270
271 --
272 -- Procedure: Check_Unassign_Trip
273 -- Parameters: p_del_rows, x_trip_rows, x_return_status
274 -- Description: Validate if the deliveries can be unassigned together and
275 -- returns a trip_id
276 -- x_return_status - this returns an error if the sequence is invalid
277
278 PROCEDURE Check_Unassign_Trip(
279 p_del_rows IN wsh_util_core.id_tab_type,
280 x_trip_rows OUT NOCOPY wsh_util_core.id_tab_type,
281 x_return_status OUT NOCOPY VARCHAR2);
282
283
284 --
285 -- Procedure: Unassign_Trip
286 -- Parameters: p_del_rows, p_trip_id, x_return_status
287 -- Description: Unassigns deliveries from a trip by calling the
288 -- wsh_delivery_legs_actions.unassign_deliveries procedure
289
290 PROCEDURE Unassign_Trip(
291 p_del_rows IN wsh_util_core.id_tab_type,
292 p_trip_id IN NUMBER,
293 x_return_status OUT NOCOPY VARCHAR2);
294
295
296 -- J-IB-NPARIKH-{
297 PROCEDURE changeStatus
298 (
299 p_in_rec IN WSH_TRIP_VALIDATIONS.ChgStatus_in_rec_type,
300 x_return_status OUT NOCOPY VARCHAR2
301 ) ;
302 -- J-IB-NPARIKH-}
303
304 -- J: W/V Changes
305
306 -- Start of comments
307 -- API name : calc_stop_fill_percent
308 -- Type : Public
309 -- Pre-reqs : None.
310 -- Function : Calculates the fill% of stop with specified W/V info
311 -- Parameters :
312 -- IN:
313 -- p_stop_id IN NUMBER Required
314 -- p_gross_weight IN NUMBER
315 -- Gross Wt. of the stop
316 -- p_volume IN NUMBER
317 -- Volume of the stop
318 -- OUT:
319 -- x_stop_fill_percent OUT NUMBER
320 -- gives the calculated fill%
321 -- x_return_status OUT VARCHAR2 Required
322 -- give the return status of API
323 -- Version : 1.0
324 -- End of comments
325
326 PROCEDURE calc_stop_fill_percent(
327 p_stop_id IN NUMBER,
328 p_gross_weight IN NUMBER,
329 p_volume IN NUMBER,
330 x_stop_fill_percent OUT NOCOPY NUMBER,
331 x_return_status OUT NOCOPY VARCHAR2);
332
333 -- Start of comments
334 -- API name : Fte_Load_Tender
335 -- Type : Public
336 -- Pre-reqs : None.
337 -- Function : Calls the FTE API to check for any change in Stop Info
338 -- Parameters :
339 -- IN:
340 -- p_stop_id IN NUMBER Required
341 -- p_gross_weight IN NUMBER
342 -- Gross Wt. of the stop
343 -- p_net_weight IN NUMBER
344 -- Net Wt. of the stop
345 -- p_volume IN NUMBER
346 -- Volume of the stop
347 -- p_fill_percent IN NUMBER
348 -- Fill Percent of the stop
349 -- OUT:
350 -- x_return_status OUT VARCHAR2 Required
351 -- give the return status of API
352 -- Version : 1.0
353 -- End of comments
354
355 PROCEDURE Fte_Load_Tender(
356 p_stop_id IN NUMBER,
357 p_gross_weight IN NUMBER,
358 p_net_weight IN NUMBER,
359 p_volume IN NUMBER,
360 p_fill_percent IN NUMBER,
361 x_return_status OUT NOCOPY VARCHAR2);
362
363 PROCEDURE generateRoutingResponse
364 (
368 );
365 p_action_prms IN WSH_TRIPS_GRP.action_parameters_rectype,
366 p_rec_attr_tab IN WSH_TRIPS_PVT.Trip_Attr_Tbl_Type,
367 x_return_status OUT NOCOPY VARCHAR2
369
370 -- bug 3516052
371 PROCEDURE reset_stop_planned_dates
372 ( p_trip_id IN NUMBER,
373 p_caller IN VARCHAR2,
374 x_return_status OUT NOCOPY VARCHAR2);
375
376 PROCEDURE reset_stop_planned_dates
377 ( p_trip_ids IN wsh_util_core.id_tab_type,
378 p_caller IN VARCHAR2,
379 x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
380 x_return_status OUT NOCOPY VARCHAR2);
381
382
383 PROCEDURE Handle_Internal_Stops
384 ( p_trip_ids IN wsh_util_core.id_tab_type,
385 p_caller IN VARCHAR2,
386 x_success_trip_ids OUT NOCOPY wsh_util_core.id_tab_type,
387 x_return_status OUT NOCOPY VARCHAR2);
388
389 PROCEDURE PROCESS_CARRIER_SELECTION (
390 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
391 p_trip_id_tab IN wsh_util_core.id_tab_type,
392 p_caller IN VARCHAR2 DEFAULT NULL, -- WSH_FSTRX / WSH_PUB / WSH_GROUP/ FTE
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2,
395 x_return_status OUT NOCOPY VARCHAR2);
396
397
398 --Set to trip_id for which WSH_FTE_INTEGRATION.Rate_Trip api has been called.
399 g_rate_trip_id NUMBER;
400
401 PROCEDURE Remove_Consolidation(
402 p_trip_id_tab IN wsh_util_core.id_tab_type,
403 p_unassign_all IN VARCHAR2,
404 p_caller IN VARCHAR2,
405 x_return_status OUT NOCOPY VARCHAR2);
406
407
408 END WSH_TRIPS_ACTIONS;
[Home] [Help]