[Home] [Help]
PACKAGE: APPS.WSH_TRIP_STOPS_PVT
Source
1 PACKAGE WSH_TRIP_STOPS_PVT AS
2 /* $Header: WSHSTTHS.pls 120.1.12010000.1 2008/07/29 06:18:49 appldev ship $ */
3
4 -- OTM R12, glog project
5 -- Declare Constants for the possible values of tms_interface_flag
6 -- at Stop Level
7 -- The Code and Description are
8 -- NS -- Not to be Sent
9 -- ASP -- Actual Shipment in Process
10 -- ASR -- Actual Shipment Request
11 -- CMP -- Completed
12 C_TMS_NOT_TO_BE_SENT CONSTANT VARCHAR2(2) := 'NS';
13 C_TMS_COMPLETED CONSTANT VARCHAR2(3) := 'CMP';
14 C_TMS_ACTUAL_IN_PROCESS CONSTANT VARCHAR2(3) := 'ASP';
15 C_TMS_ACTUAL_REQUEST CONSTANT VARCHAR2(3) := 'ASR';
16 -- end of OTM R12, glog proj
17
18 --
19 -- Type: trip_stop_rec_type
20 -- Definition: In sync with the table definition for trip stops
21 -- Use: In table handlers, calling packages
22
23
24 TYPE trip_stop_rec_type IS RECORD (
25 STOP_ID NUMBER,
26 TRIP_ID NUMBER,
27 STOP_LOCATION_ID NUMBER,
28 STATUS_CODE VARCHAR2(2),
29 STOP_SEQUENCE_NUMBER NUMBER,
30 PLANNED_ARRIVAL_DATE DATE,
31 PLANNED_DEPARTURE_DATE DATE,
32 ACTUAL_ARRIVAL_DATE DATE,
33 ACTUAL_DEPARTURE_DATE DATE,
34 DEPARTURE_GROSS_WEIGHT NUMBER,
35 DEPARTURE_NET_WEIGHT NUMBER,
36 WEIGHT_UOM_CODE VARCHAR2(3),
37 DEPARTURE_VOLUME NUMBER,
38 VOLUME_UOM_CODE VARCHAR2(3),
39 DEPARTURE_SEAL_CODE VARCHAR2(30),
40 DEPARTURE_FILL_PERCENT NUMBER,
41 TP_ATTRIBUTE_CATEGORY VARCHAR2(150),
42 TP_ATTRIBUTE1 VARCHAR2(150),
43 TP_ATTRIBUTE2 VARCHAR2(150),
44 TP_ATTRIBUTE3 VARCHAR2(150),
45 TP_ATTRIBUTE4 VARCHAR2(150),
46 TP_ATTRIBUTE5 VARCHAR2(150),
47 TP_ATTRIBUTE6 VARCHAR2(150),
48 TP_ATTRIBUTE7 VARCHAR2(150),
49 TP_ATTRIBUTE8 VARCHAR2(150),
50 TP_ATTRIBUTE9 VARCHAR2(150),
51 TP_ATTRIBUTE10 VARCHAR2(150),
52 TP_ATTRIBUTE11 VARCHAR2(150),
53 TP_ATTRIBUTE12 VARCHAR2(150),
54 TP_ATTRIBUTE13 VARCHAR2(150),
55 TP_ATTRIBUTE14 VARCHAR2(150),
56 TP_ATTRIBUTE15 VARCHAR2(150),
57 ATTRIBUTE_CATEGORY VARCHAR2(150),
58 ATTRIBUTE1 VARCHAR2(150),
59 ATTRIBUTE2 VARCHAR2(150),
60 ATTRIBUTE3 VARCHAR2(150),
61 ATTRIBUTE4 VARCHAR2(150),
62 ATTRIBUTE5 VARCHAR2(150),
63 ATTRIBUTE6 VARCHAR2(150),
64 ATTRIBUTE7 VARCHAR2(150),
65 ATTRIBUTE8 VARCHAR2(150),
66 ATTRIBUTE9 VARCHAR2(150),
67 ATTRIBUTE10 VARCHAR2(150),
68 ATTRIBUTE11 VARCHAR2(150),
69 ATTRIBUTE12 VARCHAR2(150),
70 ATTRIBUTE13 VARCHAR2(150),
71 ATTRIBUTE14 VARCHAR2(150),
72 ATTRIBUTE15 VARCHAR2(150),
73 CREATION_DATE DATE,
74 CREATED_BY NUMBER,
75 LAST_UPDATE_DATE DATE,
76 LAST_UPDATED_BY NUMBER,
77 LAST_UPDATE_LOGIN NUMBER,
78 PROGRAM_APPLICATION_ID NUMBER,
79 PROGRAM_ID NUMBER,
80 PROGRAM_UPDATE_DATE DATE,
81 REQUEST_ID NUMBER,
82 /* H Integration: datamodel changes wrudge */
83 WSH_LOCATION_ID NUMBER,
84 TRACKING_DRILLDOWN_FLAG VARCHAR2(1),
85 TRACKING_REMARKS VARCHAR2(4000),
86 CARRIER_EST_DEPARTURE_DATE DATE,
87 CARRIER_EST_ARRIVAL_DATE DATE,
88 LOADING_START_DATETIME DATE,
89 LOADING_END_DATETIME DATE,
90 UNLOADING_START_DATETIME DATE,
91 UNLOADING_END_DATETIME DATE,
92 /* I Harmonization: Non Database Columns Added rvishnuv */
93 ROWID VARCHAR2(4000),
94 TRIP_NAME VARCHAR2(30),
95 STOP_LOCATION_CODE wsh_locations.UI_LOCATION_CODE%TYPE,
96 WEIGHT_UOM_DESC VARCHAR2(25),
97 VOLUME_UOM_DESC VARCHAR2(25),
98 LOCK_STOP_ID NUMBER,
99 PENDING_INTERFACE_FLAG VARCHAR2(1),
100 TRANSACTION_HEADER_ID NUMBER,
101 /* J Inbound Logistics new columns jckwok */
102 SHIPMENTS_TYPE_FLAG VARCHAR2(30),
103 -- J: W/V Changes
104 WV_FROZEN_FLAG VARCHAR2(1),
105 /* J TP/TL ttrichy*/
106 WKEND_LAYOVER_STOPS NUMBER,
107 WKDAY_LAYOVER_STOPS NUMBER,
108 TP_STOP_ID NUMBER,
109 PHYSICAL_STOP_ID NUMBER,
110 PHYSICAL_LOCATION_ID NUMBER,
111 TMS_INTERFACE_FLAG WSH_TRIP_STOPS.TMS_INTERFACE_FLAG%TYPE --OTM R12, glog proj
112 );
113
114 --
115 -- Procedure: Create_Trip_Stop
116 -- Parameters: Trip_Stop_info; rowid, stop_id
117 -- and x_return_status as OUT
118 -- Description: This procedure will create a trip. It will
119 -- return to the use the trip_id and generates a name if
120 -- trip name is not specified.
121 --
122 TYPE Stop_Attr_Tbl_Type is TABLE of trip_stop_rec_type index by binary_integer;
123
124 PROCEDURE Create_Trip_Stop(
125 p_trip_stop_info IN trip_stop_rec_type,
126 x_rowid OUT NOCOPY VARCHAR2,
127 x_stop_id OUT NOCOPY NUMBER,
128 x_return_status OUT NOCOPY VARCHAR2
129 );
130
131 --
132 -- Procedure: Delete_Trip_Stop
133 -- Parameters: Row_id,trip_id and validate_flag IN, x_return_status OUT
134 -- Description: This procedure will delete a trip. If rowid is not null
135 -- trip_stop_id is found; trip_stop_id is then used to
136 -- delete trip.
137 -- validate_flag - 'Y' calls check_stop_delete procedure
138 --
139
140 procedure Delete_Trip_Stop(
141 p_rowid IN VARCHAR2,
142 p_stop_id IN NUMBER,
143 x_return_status OUT NOCOPY VARCHAR2,
144 p_validate_flag IN VARCHAR2 DEFAULT 'Y',
145 p_caller IN VARCHAR2 DEFAULT NULL
146 );
147
148
149 --
150 -- Procedure: Update_Trip_Stop
151 -- Parameters: Rowid, Trip_Stop_info IN; x_return_status OUT
152 -- Description: This procedure will update a trip.
153 --
154
155 procedure Update_Trip_Stop(
156 p_rowid IN VARCHAR2,
157 p_stop_info IN trip_stop_rec_type,
158 x_return_status OUT NOCOPY VARCHAR2
159 );
160
161 --
162 -- Procedure: Lock_Trip_Stop
163 -- Parameters: Rowid, Trip_Stop_info IN; x_return_status OUT
164 -- Description: This procedure will lock a trip row after checking
165 -- to see if all attributes remain the same
166 --
167
168 procedure Lock_Trip_Stop(
169 p_rowid IN VARCHAR2,
170 p_stop_info IN trip_stop_rec_type
171 );
172
173 --
174 -- Procedure: Populate_Record
175 -- Parameters: Stop id as IN, Stop Record info and return status as OUT
176 -- Description: This procedure will populate a Stop Record.
177 --
178
179 Procedure Populate_Record(
180 p_stop_id IN NUMBER,
181 x_stop_info OUT NOCOPY trip_stop_rec_type,
182 x_return_status OUT NOCOPY VARCHAR2);
183
184 --
185 -- Function: Get_Name
186 -- Parameters: p_stop_id - Id for stop
187 -- Description: This procedure will return Stop Location Name for a Stop Id
188 --
189
190 FUNCTION Get_Name
191 (p_stop_id IN NUMBER,
192 p_caller IN VARCHAR2 DEFAULT 'WSH'
193 ) RETURN VARCHAR2;
194
195
196 -----------------------------------------------------------------------------
197 --
198 -- Procedure: Get_Disabled_List
199 -- Parameters: stop_id, x_return_status, p_trip_flag
200 -- Description: Get the disabled columns/fields in a trip stop
201 --
202 -----------------------------------------------------------------------------
203
204 PROCEDURE Get_Disabled_List (
205 p_stop_id IN NUMBER,
206 p_parent_entity_id IN NUMBER ,
207 p_list_type IN VARCHAR2,
208 x_return_status OUT NOCOPY VARCHAR2,
209 x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 p_caller IN VARCHAR2 DEFAULT NULL --3509004:public api changes
213 );
214
215
216 --
217 -- Procedure: Lock_Trip_Stop Wrapper
218 -- Parameters: A table of all attributes of a Trip Stop Record,
219 -- Caller in
220 -- Return_Status,Valid_index_id_tab out
221 -- Description: This procedure will lock multiple Trip Stops.
222
223 procedure Lock_Trip_Stop(
224 p_rec_attr_tab IN Stop_Attr_Tbl_Type,
225 p_caller IN VARCHAR2,
226 p_valid_index_tab IN wsh_util_core.id_tab_type,
227 x_valid_ids_tab OUT NOCOPY wsh_util_core.id_tab_type,
228 x_return_status OUT NOCOPY VARCHAR2
229 );
230
231 PROCEDURE lock_trip_stop_no_compare (p_stop_id IN NUMBER);
232
233 --OTM R12, glog proj, new procedure added
234 --
235 -- Procedure: Update_TMS_Interface_Flag
236 -- Parameters:
237 -- IN
238 -- p_stop_id_tab - Table of Stop ids
239 -- p_tms_interface_flag_tab - Table of Tms Interface flag
240 -- OUT
241 -- x_return_status - return status
242 --
243 -- Description: This procedure will update tms_interface_flag
244 -- at Stop level, based on the input Stop id and
245 -- Tms Interface flag value
246 --
247 PROCEDURE Update_TMS_interface_flag
248 (p_stop_id_tab IN WSH_UTIL_CORE.ID_TAB_TYPE,
249 p_tms_interface_flag_tab IN WSH_UTIL_CORE.COLUMN_TAB_TYPE,
250 x_return_status OUT NOCOPY VARCHAR2);
251
252 END WSH_TRIP_STOPS_PVT;