1 PACKAGE WSH_TRIP_VALIDATIONS as
2 /* $Header: WSHTRVLS.pls 120.1 2007/01/05 00:20:23 anxsharm noship $ */
3
4 -----------------------------------------------------------------------------
5 --
6 -- Procedure: Check_Plan
7 -- Parameters: trip_id, x_return_status
8 -- Description: Checks for Plan action pre-requisites which are
9 -- - at least two stops are assigned
10 -- - Vehicle or Ship Method information is specified
11 -- - Stop sequences are valid
12 -- - If trip has vehicle information then vehicle is not over/under filled at any stop [warning]
13 -- - At least one delivery is assigned to trip [warning]
14 -- NOTE: Planning of a trip would automatically update weight/volume
15 -- information for stops and deliveries if they are not already specified.
16 --
17 -----------------------------------------------------------------------------
18
19 PROCEDURE Check_Plan ( p_trip_id IN NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 p_caller IN VARCHAR2 DEFAULT NULL);
22
23
24
25
26 -----------------------------------------------------------------------------
27 --
28 -- Procedure: Check_Unplan
29 -- Parameters: trip_id, x_return_status
30 -- Description: Checks for Unplan action pre-requisites which are
31 -- - Trip status is not CLOSED
32 -- - Trip is planned
33 --
34 -----------------------------------------------------------------------------
35
36 PROCEDURE Check_Unplan ( p_trip_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2);
38
39
40
41
42 -----------------------------------------------------------------------------
43 --
44 -- Procedure: Check_Trip_Close
45 -- Parameters: trip_id, x_return_status
46 -- Description: Checks for Trip Close action pre-requisites which are
47 -- - Trip status is OPEN or IN-TRANSIT
48 -- - If trip status is IN-TRANSIT then last stop status is ARRIVED or CLOSED [warning]
49 --
50 -----------------------------------------------------------------------------
51
52 PROCEDURE Check_Trip_Close ( p_trip_id IN NUMBER,
53 x_return_status OUT NOCOPY VARCHAR2);
54
55
56
57
58 -----------------------------------------------------------------------------
59 --
60 -- Procedure: Check_Trip_Delete
61 -- Parameters: trip_id, x_return_status
62 -- Description: Checks for Trip Delete action pre-requisites which are
63 -- - Trip status is OPEN
64 -- - No deliveries are assigned to trip [warning]
65 -- - No freight costs are attached to trip [warning]
66 --
67 -----------------------------------------------------------------------------
68
69 PROCEDURE Check_Trip_Delete ( p_trip_id IN NUMBER,
70 x_return_status OUT NOCOPY VARCHAR2,
71 p_caller IN VARCHAR2 DEFAULT NULL);
72
73
74
75
76 -----------------------------------------------------------------------------
77 --
78 -- Procedure: Check_Change_Carrier
79 -- Parameters: trip_id, x_return_status
80 -- Description: Checks for Change Carrier action pre-requisites which are
81 -- Trip status is OPEN
82 -- If GROUP_BY_CARRIER_FLAG is set for any delivery on trip then Ship Method for deliveries and delivery details on this trip is not specified
83 --
84 -----------------------------------------------------------------------------
85
86 PROCEDURE Check_Change_Carrier ( p_trip_id IN NUMBER,
87 x_return_status OUT NOCOPY VARCHAR2);
88
89
90
91
92 -----------------------------------------------------------------------------
93 --
94 -- Procedure: Check_Stop_Arrive
95 -- Parameters: stop_id, x_linked_stop_id, x_return_status
96 -- Description: Checks for Arrive action pre-requisites which are
97 -- (these prerequisites need to be updated) --wr
98 -- - Actual Arrival Date is specified ( if date is not specified
99 -- then default the current date in Actual Arrival Date)
100 -- - Not the first stop on a trip
101 -- - Previous stop on this trip is CLOSED [warning]
102 -- NOTE: this warning allows the user to Close All Previous Stops, Ignore or Cancel.
103 --
104 -----------------------------------------------------------------------------
105
106 PROCEDURE Check_Stop_Arrive ( p_stop_id IN NUMBER,
107 x_linked_stop_id OUT NOCOPY NUMBER, --wr
108 x_return_status OUT NOCOPY VARCHAR2);
109
110
111
112
113 -----------------------------------------------------------------------------
114 --
115 -- Procedure: Check_Stop_Close
116 -- Parameters: stop_id, x_return_status
117 -- Description: Checks for Stop Close action pre-requisites which are
118 -- - Actual Arrival Date and Actual Departure Date are specified
119 -- (if first or last trip stop on a trip then only one of the
120 -- two dates need to be specified)
121 -- - Previous stop on this trip is CLOSED (except for the first trip stop) [warning]
122 --
123 -----------------------------------------------------------------------------
124
125 PROCEDURE Check_Stop_Close ( p_stop_id IN NUMBER,
126 x_return_status OUT NOCOPY VARCHAR2,
127 p_caller IN VARCHAR2 DEFAULT NULL);
128
129
130
131
132 -----------------------------------------------------------------------------
133 --
134 -- Procedure: Check_Assign_Trip
135 -- Parameters: stop_id, x_return_status
136 -- Description: Checks for Assign Trip action pre-requisites which are
137 -- - Trip status is not CLOSED
138 -- - Trip is not planned
139 -- - If trip has Vehicle information then vehicle is not over/under filled at this and subsequent stops [warning]
140 -- NOTE: The above three rules apply to both trip to be unassigned from and trip to be assigned to
141 -- - Stop with the same location does not exist for the trip to be assigned to [warning]
142 --
143 -----------------------------------------------------------------------------
144
145 PROCEDURE Check_Assign_Trip ( p_stop_id IN NUMBER,
146 p_trip_id IN NUMBER,
147 x_return_status OUT NOCOPY VARCHAR2);
148
149
150
151
152 -----------------------------------------------------------------------------
153 --
154 -- Procedure: Check_Delete_Stop
155 -- Parameters: stop_id, x_return_status, p_trip_flag
156 -- Description: Checks for Delete Stop action pre-requisites which are
157 -- - Stop status is OPEN
158 -- - Trip status is not CLOSED
159 -- - Trip is not planned
160 -- - No deliveries are assigned to this stop [warning]
161 -- NOTE: p_trip_flag is used to restrict delivery checks to only pickup deliveries
162 --
163 -----------------------------------------------------------------------------
164
165 PROCEDURE Check_Stop_Delete ( p_stop_id IN NUMBER,
166 x_return_status OUT NOCOPY VARCHAR2,
167 p_trip_flag IN VARCHAR2 DEFAULT 'N',
168 p_caller IN VARCHAR2 DEFAULT NULL);
169
170 -----------------------------------------------------------------------------
171 --
172 -- Procedure: Get_Disabled_List
173 -- Parameters: stop_id, x_return_status, p_trip_flag
174 -- Description: Get the disabled columns/fields in a trip
175 --
176 -----------------------------------------------------------------------------
177
178 PROCEDURE Get_Disabled_List (
179 p_trip_id IN NUMBER,
180 p_list_type IN VARCHAR2,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type,
183 x_msg_count OUT NOCOPY NUMBER,
184 x_msg_data OUT NOCOPY VARCHAR2,
185 p_caller IN VARCHAR2 DEFAULT NULL --public api changes
186 );
187
188
189 -----------------------------------------------------------------------------
190 --
191 -- Procedure: Valdiate Planned Trip
192 -- Parameters: p_stop_id, x_return_status, p_stop_sequence_number
193 -- Description: Get the trip status for update of stop sequence
194 --
195 -----------------------------------------------------------------------------
196 PROCEDURE validate_planned_trip
197 (p_stop_id IN NUMBER,
198 p_stop_sequence_number IN NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2
200 );
201
202
203 --Harmonizing Project
204 TYPE TripActionsRec IS RECORD(
205 status_code WSH_TRIPS.STATUS_CODE%TYPE,
206 planned_flag WSH_TRIPS.PLANNED_FLAG%TYPE,
207 load_tender_status WSH_TRIPS.LOAD_TENDER_STATUS%TYPE,
208 message_name VARCHAR2(2000),
209 caller VARCHAR2(100),
210 action_not_allowed VARCHAR2(100),
211 shipments_type_flag VARCHAR2(30),
212 ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE); -- OTM R12, glog proj
213
214 TYPE TripActionsTabType IS TABLE of TripActionsRec INDEX BY BINARY_INTEGER;
215
216 TYPE trip_rec_type IS RECORD(
217 trip_id NUMBER,
218 organization_id NUMBER,
219 status_code VARCHAR2(32000),
220 planned_flag VARCHAR2(32000),
221 load_tender_status VARCHAR2(32000),
222 lane_id NUMBER,
223 shipments_type_flag VARCHAR2(30),
224 ignore_for_planning WSH_TRIPS.IGNORE_FOR_PLANNING%TYPE); -- OTM R12, glog proj
225
226 TYPE trip_rec_tab_type IS TABLE OF trip_rec_type INDEX BY BINARY_INTEGER;
227
228 PROCEDURE Is_Action_Enabled(
229 p_trip_rec_tab IN trip_rec_tab_type,
230 p_action IN VARCHAR2,
231 p_caller IN VARCHAR2,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_valid_ids OUT NOCOPY wsh_util_core.id_tab_type,
234 x_error_ids OUT NOCOPY wsh_util_core.id_tab_type,
235 x_valid_index_tab OUT NOCOPY wsh_util_core.id_tab_type);
236
237
238 PROCEDURE Validate_Arrive_after_trip(
239 p_trip_id IN NUMBER,
240 p_arr_after_trip_id IN OUT NOCOPY NUMBER,
241 p_arr_after_trip_name IN VARCHAR2,
242 x_return_status OUT NOCOPY VARCHAR2
243 );
244
245 PROCEDURE Validate_Consol_Allowed(
246 p_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
247 p_db_trip_info IN WSH_TRIPS_PVT.trip_rec_type,
248 x_return_status OUT NOCOPY VARCHAR2);
249
250 --
251 -- Overloaded procedure
252 --
253 PROCEDURE Get_Disabled_List (
254 p_trip_rec IN WSH_TRIPS_PVT.trip_rec_type
255 , p_in_rec IN WSH_TRIPS_GRP.TripInRecType
256 , x_return_status OUT NOCOPY VARCHAR2
257 , x_msg_count OUT NOCOPY NUMBER
258 , x_msg_data OUT NOCOPY VARCHAR2
259 , x_trip_rec OUT NOCOPY WSH_TRIPS_PVT.trip_rec_type
260 );
261
262
263 PROCEDURE Init_Trip_Actions_Tbl (
264 p_action IN VARCHAR2
265 , x_Trip_actions_tab OUT NOCOPY TripActionsTabType
266 , x_return_status OUT NOCOPY VARCHAR2
267 );
268
269 --Harmonizing Project
270
271 --anxsharm for Load Tender Project
272 /*
273 -----------------------------------------------------------------------------
274 PROCEDURE : Get Trip Calc Wtvol
275 PARAMETERS : p_tab_id - entity id
276 p_entity - entity name -DELIVERY,TRIP,TRIP_STOP,DELIVERY_DETAIL
277 p_action_code - action code for each action
278 p_phase - 1 for Before the action is performed, 2 for after.
279 x_trip_id_tab - Table of Trip ids
280 x_return_status - Return Status
281 DESCRIPTION : This procedure finds the trip for each entity on the basis
282 of p_entity.After the trip is determined, calculate the
283 weight/volume for the trip.
284 ------------------------------------------------------------------------------
285 */
286
287 PROCEDURE Get_Trip_Calc_Wtvol
288 (p_tab_id IN wsh_util_core.id_tab_type,
289 p_entity IN VARCHAR2,
290 p_action_code IN VARCHAR2,
291 p_phase IN NUMBER,
292 x_trip_id_tab IN OUT NOCOPY wsh_util_core.id_tab_type,
293 x_return_status OUT NOCOPY VARCHAR2
294 );
295
296 --End for Load Tender Project
297
298 -- J-IB-NPARIKH-{
299 --
300 TYPE ChgStatus_in_rec_type
301 IS RECORD
302 (
303 trip_id NUMBER,
304 name VARCHAR2(30),
305 new_status_code VARCHAR2(30),
306 put_messages BOOLEAN DEFAULT TRUE,
307 manual_flag VARCHAR2(10),
308 caller VARCHAR2(32767),
309 actual_date DATE,
310 stop_id NUMBER,
311 linked_stop_id NUMBER
312 );
313 --
314 --
315 PROCEDURE check_Close
316 (
317 p_in_rec IN ChgStatus_in_rec_type,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_allowed OUT NOCOPY VARCHAR2
320 ) ;
321 --
322 --
323 PROCEDURE check_inTransit
324 (
325 p_in_rec IN ChgStatus_in_rec_type,
326 x_return_status OUT NOCOPY VARCHAR2,
327 x_allowed OUT NOCOPY VARCHAR2
328 ) ;
329 --
330 FUNCTION has_outbound_deliveries
331 (
332 p_trip_id IN NUMBER,
333 p_stop_id IN NUMBER DEFAULT NULL
334 )
335 RETURN VARCHAR2;
336 --
337 FUNCTION has_inbound_deliveries
338 (
339 p_trip_id IN NUMBER,
340 p_stop_id IN NUMBER DEFAULT NULL
341 )
342 RETURN VARCHAR2;
343 --
344 FUNCTION has_mixed_deliveries
345 (
346 p_trip_id IN NUMBER,
347 p_stop_id IN NUMBER DEFAULT NULL
348 )
349 RETURN VARCHAR2;
350 --
351 PROCEDURE Validate_Trip_status
352 (
353 p_trip_id IN NUMBER,
354 p_action IN VARCHAR2,
355 x_return_status OUT NOCOPY VARCHAR2
356 );
357 --
358 -- J-IB-NPARIKH-}
359 --
360
361 -- bug 3516052
362 PROCEDURE Validate_Stop_Dates
363 (
364 p_trip_id IN NUMBER,
365 x_return_status OUT NOCOPY VARCHAR2,
366 p_caller IN VARCHAR2 DEFAULT NULL
367 );
368
369 END WSH_TRIP_VALIDATIONS;
[Home] [Help]