1 PACKAGE WSH_TRIP_STOPS_GRP as
2 /* $Header: WSHSTGPS.pls 120.1.12000000.1 2007/01/16 05:50:43 appldev ship $ */
3
4 c_sdebug CONSTANT NUMBER := wsh_debug_sv.c_level1;
5 c_debug CONSTANT NUMBER := wsh_debug_sv.c_level2;
6
7 --===================
8 -- PUBLIC VARS
9 --===================
10
11 TYPE Trip_Stop_Pub_Rec_Type IS RECORD (
12 STOP_ID NUMBER DEFAULT 9.99E125,
13 TRIP_ID NUMBER DEFAULT 9.99E125,
14 TRIP_NAME VARCHAR2(30) DEFAULT chr(0),
15 STOP_LOCATION_ID NUMBER DEFAULT 9.99E125,
16 STOP_LOCATION_CODE VARCHAR2(20) DEFAULT chr(0),
17 PLANNED_ARRIVAL_DATE DATE DEFAULT TO_DATE('1','j'),
18 PLANNED_DEPARTURE_DATE DATE DEFAULT TO_DATE('1','j'),
19 ACTUAL_ARRIVAL_DATE DATE DEFAULT TO_DATE('1','j'),
20 ACTUAL_DEPARTURE_DATE DATE DEFAULT TO_DATE('1','j'),
21 DEPARTURE_GROSS_WEIGHT NUMBER DEFAULT 9.99E125,
22 DEPARTURE_NET_WEIGHT NUMBER DEFAULT 9.99E125,
23 WEIGHT_UOM_CODE VARCHAR2(3) DEFAULT chr(0),
24 WEIGHT_UOM_DESC VARCHAR2(25) DEFAULT chr(0),
25 DEPARTURE_VOLUME NUMBER DEFAULT 9.99E125,
26 VOLUME_UOM_CODE VARCHAR2(3) DEFAULT chr(0),
27 VOLUME_UOM_DESC VARCHAR2(25) DEFAULT chr(0),
28 DEPARTURE_SEAL_CODE VARCHAR2(30) DEFAULT chr(0),
29 DEPARTURE_FILL_PERCENT NUMBER DEFAULT 9.99E125,
30 STOP_SEQUENCE_NUMBER NUMBER DEFAULT 9.99E125,
31 LOCK_STOP_ID NUMBER DEFAULT 9.99E125,
32 STATUS_CODE VARCHAR2(2) DEFAULT chr(0),
33 PENDING_INTERFACE_FLAG VARCHAR2(1) DEFAULT chr(0),
34 TRANSACTION_HEADER_ID NUMBER DEFAULT 9.99E125,
35 WSH_LOCATION_ID NUMBER DEFAULT 9.99E125,
36 TRACKING_DRILLDOWN_FLAG VARCHAR2(1) DEFAULT chr(0),
37 TRACKING_REMARKS VARCHAR2(1) DEFAULT chr(0),
38 CARRIER_EST_DEPARTURE_DATE DATE DEFAULT TO_DATE('1','j'),
39 CARRIER_EST_ARRIVAL_DATE DATE DEFAULT TO_DATE('1','j'),
40 LOADING_START_DATETIME DATE DEFAULT TO_DATE('1','j'),
41 LOADING_END_DATETIME DATE DEFAULT TO_DATE('1','j'),
42 UNLOADING_START_DATETIME DATE DEFAULT TO_DATE('1','j'),
43 UNLOADING_END_DATETIME DATE DEFAULT TO_DATE('1','j'),
44 TP_ATTRIBUTE_CATEGORY VARCHAR2(150) DEFAULT chr(0),
45 TP_ATTRIBUTE1 VARCHAR2(150) DEFAULT chr(0),
46 TP_ATTRIBUTE2 VARCHAR2(150) DEFAULT chr(0),
47 TP_ATTRIBUTE3 VARCHAR2(150) DEFAULT chr(0),
48 TP_ATTRIBUTE4 VARCHAR2(150) DEFAULT chr(0),
49 TP_ATTRIBUTE5 VARCHAR2(150) DEFAULT chr(0),
50 TP_ATTRIBUTE6 VARCHAR2(150) DEFAULT chr(0),
51 TP_ATTRIBUTE7 VARCHAR2(150) DEFAULT chr(0),
52 TP_ATTRIBUTE8 VARCHAR2(150) DEFAULT chr(0),
53 TP_ATTRIBUTE9 VARCHAR2(150) DEFAULT chr(0),
54 TP_ATTRIBUTE10 VARCHAR2(150) DEFAULT chr(0),
55 TP_ATTRIBUTE11 VARCHAR2(150) DEFAULT chr(0),
56 TP_ATTRIBUTE12 VARCHAR2(150) DEFAULT chr(0),
57 TP_ATTRIBUTE13 VARCHAR2(150) DEFAULT chr(0),
58 TP_ATTRIBUTE14 VARCHAR2(150) DEFAULT chr(0),
59 TP_ATTRIBUTE15 VARCHAR2(150) DEFAULT chr(0),
60 ATTRIBUTE_CATEGORY VARCHAR2(150) DEFAULT chr(0),
61 ATTRIBUTE1 VARCHAR2(150) DEFAULT chr(0),
62 ATTRIBUTE2 VARCHAR2(150) DEFAULT chr(0),
63 ATTRIBUTE3 VARCHAR2(150) DEFAULT chr(0),
64 ATTRIBUTE4 VARCHAR2(150) DEFAULT chr(0),
65 ATTRIBUTE5 VARCHAR2(150) DEFAULT chr(0),
66 ATTRIBUTE6 VARCHAR2(150) DEFAULT chr(0),
67 ATTRIBUTE7 VARCHAR2(150) DEFAULT chr(0),
68 ATTRIBUTE8 VARCHAR2(150) DEFAULT chr(0),
69 ATTRIBUTE9 VARCHAR2(150) DEFAULT chr(0),
70 ATTRIBUTE10 VARCHAR2(150) DEFAULT chr(0),
71 ATTRIBUTE11 VARCHAR2(150) DEFAULT chr(0),
72 ATTRIBUTE12 VARCHAR2(150) DEFAULT chr(0),
73 ATTRIBUTE13 VARCHAR2(150) DEFAULT chr(0),
74 ATTRIBUTE14 VARCHAR2(150) DEFAULT chr(0),
75 ATTRIBUTE15 VARCHAR2(150) DEFAULT chr(0),
76 CREATION_DATE DATE DEFAULT TO_DATE('1','j'),
77 CREATED_BY NUMBER DEFAULT 9.99E125,
78 LAST_UPDATE_DATE DATE DEFAULT TO_DATE('1','j'),
79 LAST_UPDATED_BY NUMBER DEFAULT 9.99E125,
80 LAST_UPDATE_LOGIN NUMBER DEFAULT 9.99E125,
81 PROGRAM_APPLICATION_ID NUMBER DEFAULT 9.99E125,
82 PROGRAM_ID NUMBER DEFAULT 9.99E125,
83 PROGRAM_UPDATE_DATE DATE DEFAULT TO_DATE('1','j'),
84 REQUEST_ID NUMBER DEFAULT 9.99E125);
85
86 --Harmonizing Project -I
87 TYPE stopInRecType is RECORD(
88 caller VARCHAR2(32767),
89 phase NUMBER,
90 action_code VARCHAR2(32767));
91
92
93 --bug 2796095
94 TYPE Stop_Wt_Vol_Rec_Type IS RECORD (
95 STOP_ID NUMBER,
96 DEPARTURE_GROSS_WEIGHT NUMBER,
97 DEPARTURE_NET_WEIGHT NUMBER,
98 DEPARTURE_VOLUME NUMBER,
99 DEPARTURE_FILL_PERCENT NUMBER);
100
101 TYPE Stop_Wt_Vol_tab_type IS TABLE OF Stop_Wt_Vol_Rec_Type INDEX BY BINARY_INTEGER;
102 --bug 2796095
103
104 TYPE stopOutRecType IS RECORD (
105 parameter1 VARCHAR2(32767) DEFAULT chr(0),
106 rowid VARCHAR2(32767),
107 stop_id NUMBER
108 );
109
110 TYPE stopActionInRecType
111 IS
112 RECORD
113 (
114 action_code VARCHAR2(32767),
115 actual_date DATE DEFAULT FND_API.G_MISS_DATE,
116 defer_interface_flag VARCHAR2(1) DEFAULT 'Y'
117 );
118
119 TYPE action_parameters_rectype IS RECORD (
120 caller VARCHAR2(500)
121 ,phase NUMBER
122 ,action_code VARCHAR2(500)
123 ,stop_action VARCHAR2(30)
124 ,organization_id NUMBER
125 ,actual_date DATE
126 ,defer_interface_flag VARCHAR2(500)
127 ,report_set_id NUMBER
128 ,override_flag VARCHAR2(500)
129 );
130
131 TYPE default_parameters_rectype IS RECORD (
132 status_code wsh_trip_stops.status_code%TYPE
133 ,date_field VARCHAR2(500)
134 ,defer_interface_flag VARCHAR2(500)
135 ,status_name VARCHAR2(500)
136 ,stop_action VARCHAR2(500)
137 );
138 --Harmonization Project
139 TYPE stopActionOutRecType
140 IS
141 RECORD
142 (
143 result_id_tab wsh_util_core.id_tab_type,
144 valid_ids_tab wsh_util_core.id_tab_type,
145 selection_issue_flag VARCHAR2(1)
146 );
147 --===================
148 -- NEW PROCEDURES
149 --===================
150
151 --========================================================================
152 -- PROCEDURE : Create_Update_Stop PUBLIC
153 --
154 -- PARAMETERS: p_api_version_number known api versionerror buffer
155 -- p_init_msg_list FND_API.G_TRUE to reset list
156 -- x_return_status return status
157 -- x_msg_count number of messages in the list
158 -- x_msg_data text of messages
159 -- p_stop_info Attributes for the stop entity
160 -- p_stop_IN_rec Input Attributes for the stop entity
161 -- p_stop_OUT_rec Output Attributes for the stop entity
162 -- VERSION : current version 1.0
163 -- initial version 1.0
164 -- COMMENT : Creates or updates a record in wsh_trip_stops table with information
165 -- specified in p_stop_info. Use p_trip_id, p_trip_name, p_stop_location_id,
166 -- p_stop_location_code or p_planned_dep_date to update these values
167 -- on an existing stop.These are part of p_stop_info.
168 --========================================================================
169 PROCEDURE Create_Update_Stop_New
170 ( p_api_version_number IN NUMBER,
171 p_init_msg_list IN VARCHAR2,
172 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
173 x_return_status OUT NOCOPY VARCHAR2,
174 x_msg_count OUT NOCOPY NUMBER,
175 x_msg_data OUT NOCOPY VARCHAR2,
176 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
177 p_stop_IN_rec IN stopInRecType,
178 x_stop_OUT_rec OUT NOCOPY stopOutRecType);
179
180 --========================================================================
181 -- PROCEDURE : Stop_Action PUBLIC
182 --
183 -- PARAMETERS: p_api_version_number known api version error number
184 -- p_init_msg_list FND_API.G_TRUE to reset list
185 -- x_return_status return status
186 -- x_msg_count number of messages in the list
187 -- x_msg_data text of messages
188 -- p_stop_info Attributes for the stop entity
189 -- p_stop_IN_rec Input Attributes for the stop entity
190 -- p_stop_OUT_rec Output Attributes for the stop entity
191 -- p_action_code Stop action code. Valid action codes are
192 -- 'PLAN','UNPLAN',
193 -- 'ARRIVE','CLOSE'
194 -- 'PICK-RELEASE'
195 -- 'DELETE'
196 -- VERSION : current version 1.0
197 -- initial version 1.0
198 -- COMMENT : This procedure is used to perform an action specified in p_action_code
199 -- on an existing stop identified by p_stop_id or a unique combination of
200 -- trip_id/trip_name, stop_location_id/stop_location_code or planned_departure_date.
201 --
202 --========================================================================
203
204 PROCEDURE Stop_Action_New
205 ( p_api_version_number IN NUMBER,
206 p_init_msg_list IN VARCHAR2,
207 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_msg_count OUT NOCOPY NUMBER,
210 x_msg_data OUT NOCOPY VARCHAR2,
211 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
212 p_stop_IN_rec IN stopActionInRecType,
213 x_stop_OUT_rec OUT NOCOPY stopActionOutRecType);
214
215 --===================
216 -- OLD PROCEDURES
217 --===================
218
219 --========================================================================
220 -- PROCEDURE : Create_Update_Stop PUBLIC
221 --
222 -- PARAMETERS: p_api_version_number known api versionerror buffer
223 -- p_init_msg_list FND_API.G_TRUE to reset list
224 -- x_return_status return status
225 -- x_msg_count number of messages in the list
226 -- x_msg_data text of messages
227 -- p_stop_info Attributes for the stop entity
228 -- p_trip_id Trip id for update
229 -- p_trip_name Trip name for update
230 -- p_stop_location_id Stop location id for update
231 -- p_stop_location_code Stop location code for update
232 -- p_planned_dep_date Planned departure date for update
233 -- x_stop_id - stop id of new stop
234 -- VERSION : current version 1.0
235 -- initial version 1.0
236 -- COMMENT : Creates or updates a record in wsh_trip_stops table with information
237 -- specified in p_stop_info. Use p_trip_id, p_trip_name, p_stop_location_id,
238 -- p_stop_location_code or p_planned_dep_date to update these values
239 -- on an existing stop.
240 --========================================================================
241 PROCEDURE Create_Update_Stop
242 ( p_api_version_number IN NUMBER,
243 p_init_msg_list IN VARCHAR2,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 p_action_code IN VARCHAR2,
248 p_stop_info IN OUT NOCOPY WSH_TRIP_STOPS_GRP.Trip_Stop_Pub_Rec_Type,
249 p_trip_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
250 p_trip_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
251 p_stop_location_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
252 p_stop_location_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
253 p_planned_dep_date IN DATE DEFAULT FND_API.G_MISS_DATE,
254 x_stop_id OUT NOCOPY NUMBER);
255
256
257 --========================================================================
258 -- PROCEDURE : Stop_Action PUBLIC
259 --
260 -- PARAMETERS: p_api_version_number known api version error number
261 -- p_init_msg_list FND_API.G_TRUE to reset list
262 -- x_return_status return status
263 -- x_msg_count number of messages in the list
264 -- x_msg_data text of messages
265 -- p_action_code Stop action code. Valid action codes are
266 -- 'PLAN','UNPLAN',
267 -- 'ARRIVE','CLOSE'
268 -- 'PICK-RELEASE'
269 -- 'DELETE'
270 -- p_stop_id Stop identifier
271 -- p_trip_id Stop identifier - trip id it belongs to
272 -- p_trip_name Stop identifier - trip name it belongs to
273 -- p_stop_location_id Stop identifier - stop location id
274 -- p_stop_location_code Stop identifier - stop location code
275 -- p_planned_dep_date Stop identifier - stop planned dep date
276 -- p_actual_date Actual arrival/departure date of the stop
277 -- VERSION : current version 1.0
278 -- initial version 1.0
279 -- COMMENT : This procedure is used to perform an action specified in p_action_code
280 -- on an existing stop identified by p_stop_id or a unique combination of
281 -- trip_id/trip_name, stop_location_id/stop_location_code or planned_departure_date.
282 --
283 --========================================================================
284
285 PROCEDURE Stop_Action
286 ( p_api_version_number IN NUMBER,
287 p_init_msg_list IN VARCHAR2,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291 p_action_code IN VARCHAR2,
292 p_stop_id IN NUMBER DEFAULT NULL,
293 p_trip_id IN NUMBER DEFAULT NULL,
294 p_trip_name IN VARCHAR2 DEFAULT NULL,
295 p_stop_location_id IN NUMBER DEFAULT NULL,
296 p_stop_location_code IN VARCHAR2 DEFAULT NULL,
297 p_planned_dep_date IN DATE DEFAULT NULL,
298 p_actual_date IN DATE DEFAULT NULL,
299 p_defer_interface_flag IN VARCHAR2 DEFAULT 'Y');
300
301
302 PROCEDURE Stop_Action
303 ( p_api_version_number IN NUMBER,
304 p_init_msg_list IN VARCHAR2,
305 p_commit IN VARCHAR2,
306 p_action_prms IN action_parameters_rectype,
307 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
308 x_stop_out_rec OUT NOCOPY stopActionOutRecType,
309 x_def_rec OUT NOCOPY default_parameters_rectype,
310 x_return_status OUT NOCOPY VARCHAR2,
311 x_msg_count OUT NOCOPY NUMBER,
312 x_msg_data OUT NOCOPY VARCHAR2);
313
314
315
316 --Harmonizing Project -I
317 TYPE stop_out_tab_type IS TABLE OF StopOutRecType INDEX BY BINARY_INTEGER;
318
319 PROCEDURE CREATE_UPDATE_STOP(
320 p_api_version_number IN NUMBER,
321 p_init_msg_list IN VARCHAR2,
322 p_commit IN VARCHAR2,
323 p_in_rec IN stopInRecType,
324 p_rec_attr_tab IN WSH_TRIP_STOPS_PVT.Stop_Attr_Tbl_Type,
325 x_stop_out_tab OUT NOCOPY stop_out_tab_type,
326 x_return_status OUT NOCOPY VARCHAR2,
330 );
327 x_msg_count OUT NOCOPY NUMBER,
328 x_msg_data OUT NOCOPY VARCHAR2,
329 x_stop_wt_vol_out_tab OUT NOCOPY Stop_Wt_Vol_tab_type --bug 2796095
331
332 --Harmonizing Project -I
333
334 -- API to get Stop Details
335 PROCEDURE get_stop_details_pvt
336 (p_stop_id IN NUMBER,
337 x_stop_rec OUT NOCOPY WSH_TRIP_STOPS_PVT.TRIP_STOP_REC_TYPE,
338 x_return_status OUT NOCOPY VARCHAR2);
339
340
341 END WSH_TRIP_STOPS_GRP;
[Home] [Help]