[Home] [Help]
PACKAGE BODY: APPS.FTE_DELIVERY_ACTIVITY
Source
1 PACKAGE BODY FTE_DELIVERY_ACTIVITY AS
2 /* $Header: FTEDLACB.pls 120.1 2005/06/03 16:24:22 appldev $ */
3
4 --===================
5 -- TYPES
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_DELIVERY_ACTIVITES';
8
9
10 --===================
11 -- PROCEDURES
12 --===================
13
14
15
16 PROCEDURE ADD_HISTORY(
17
18 p_delivery_id IN NUMBER,
19 p_delivery_leg_id IN NUMBER,
20 p_trip_id IN NUMBER,
21 p_activity_date IN DATE,
22 p_activity_type IN VARCHAR2,
23 p_request_id IN NUMBER,
24 p_action_by IN NUMBER,
25 p_action_by_name IN VARCHAR2,
26 p_remarks IN VARCHAR2,
27 p_result_status IN VARCHAR2,
28 p_initial_status IN VARCHAR2,
29 p_carrier_id IN NUMBER,
30 p_mode_of_transport IN VARCHAR2,
31 p_service_level IN VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_error_msg OUT NOCOPY VARCHAR2,
34 x_error_tkn OUT NOCOPY VARCHAR2) IS
35
36 l_return_status VARCHAR2(1) := 'S';
37 l_error_msg VARCHAR2(1000) := null;
38 l_error_tkn VARCHAR2(1000) := null;
39
40 s_activity_id NUMBER;
41
42 BEGIN
43
44 select wsh_delivery_leg_activities_s.nextval into s_activity_id from dual;
45
46 insert into wsh_delivery_leg_activities
50 (s_activity_id, p_delivery_leg_id, p_activity_date, p_activity_type, sysdate, -1,
47 (ACTIVITY_ID, DELIVERY_LEG_ID, ACTIVITY_DATE, ACTIVITY_TYPE, CREATION_DATE, CREATED_BY,
48 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
49 ACTION_BY, ACTION_BY_NAME, REMARKS, RESULT_STATUS, INITIAL_STATUS, TRIP_ID,CARRIER_ID,MODE_OF_TRANSPORT,SERVICE_LEVEL) values
51 sysdate, -1, 1,
52 p_action_by, p_action_by_name, p_remarks, p_result_status, p_initial_status, p_trip_id,p_carrier_id,p_mode_of_transport,p_service_level);
53
54
55 x_return_status := l_return_status;
56 x_error_msg := l_error_msg;
57 x_error_tkn := l_error_tkn;
58
59
60
61 EXCEPTION
62
63 WHEN OTHERS THEN
64 x_return_Status := 'E';
65 x_error_msg := SQLERRM;
66 x_error_tkn := 'ADD_HISTORY';
67
68 END ADD_HISTORY;
69
70 PROCEDURE ADD_HISTORY(
71
72 p_trip_id IN NUMBER,
73 p_activity_date IN DATE,
74 p_activity_type IN VARCHAR2,
75 p_request_id IN NUMBER,
76 p_action_by IN NUMBER,
77 p_action_by_name IN VARCHAR2,
78 p_remarks IN VARCHAR2,
79 p_result_status IN VARCHAR2,
80 p_initial_status IN VARCHAR2,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_error_msg OUT NOCOPY VARCHAR2,
83 x_error_tkn OUT NOCOPY VARCHAR2) IS
84
85 l_return_status VARCHAR2(1) := 'S';
86 l_error_msg VARCHAR2(1000) := null;
87 l_error_tkn VARCHAR2(1000) := null;
88
89 s_activity_id NUMBER;
90
91 cursor c_dlegs(p_trip_id NUMBER) is
92 select wdl.delivery_id delivery_id, wdl.delivery_leg_id delivery_leg_id ,
93 wt.carrier_id carrier_id, wt.mode_of_transport mode_of_transport , wt.service_level service_level
94 from wsh_delivery_legs wdl, wsh_trip_stops wts,wsh_trips wt
95 where wdl.pick_up_stop_id = wts.stop_id
96 and wts.trip_id = p_trip_id
97 and wt.trip_id= p_trip_id;
98
99 BEGIN
100
101
102 FOR c_rec IN c_dlegs(p_trip_id) LOOP
103
104 ADD_HISTORY(c_rec.delivery_id, c_rec.delivery_leg_id, p_trip_id,
105 p_activity_date, p_activity_type, p_request_id,
106 p_action_by, p_action_by_name, p_remarks,
107 p_result_status, p_initial_status,
108 c_rec.carrier_id,c_rec.mode_of_transport,c_rec.service_level,
109 l_return_status, l_error_msg, l_error_tkn);
110
111
112 IF l_return_status <> 'S' THEN EXIT; END IF;
113
114 END LOOP;
115
116 x_return_status := l_return_status;
117 x_error_msg := l_error_msg;
118 x_error_tkn := l_error_tkn;
119
120 END ADD_HISTORY;
121
122 -- Rel 12
123
124
125 PROCEDURE ADD_HISTORY(
126 p_init_msg_list IN VARCHAR2,
127 p_trip_id IN NUMBER,
128 p_delivery_leg_activity_rec IN delivery_leg_activity_rec,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2)
132 IS
133 --{ Local variables
134
135 l_api_name CONSTANT VARCHAR2(30) := 'ADD_HISTORY';
136 l_api_version CONSTANT NUMBER := 1.0;
137 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
138 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||
139 l_api_name;
140
141
142 l_return_status VARCHAR2(32767);
143 l_msg_count NUMBER;
144 l_msg_data VARCHAR2(32767);
145 l_number_of_warnings NUMBER;
146 l_number_of_errors NUMBER;
147
148 s_activity_id NUMBER;
149
150 -- Cursor
151 CURSOR c_dlegs(c_trip_id NUMBER) is
152 SELECT wdl.delivery_id delivery_id, wdl.delivery_leg_id delivery_leg_id ,
153 wt.carrier_id carrier_id, wt.mode_of_transport mode_of_transport ,
154 wt.service_level service_level, wt.wf_item_key, wt.rank_id
155 FROM wsh_delivery_legs wdl, wsh_trip_stops wts,wsh_trips wt
156 WHERE wdl.pick_up_stop_id = wts.stop_id
157 AND wts.trip_id = c_trip_id
158 AND wt.trip_id= c_trip_id ;
159 --}
160
161 BEGIN
162
163
164 SAVEPOINT ADD_HISTORY_PUB;
165 IF l_debug_on THEN
166 WSH_DEBUG_SV.push(l_module_name);
167 END IF;
168
169 IF FND_API.to_Boolean( p_init_msg_list )
170 THEN
171 FND_MSG_PUB.initialize;
172 END IF;
173
174 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
175 x_msg_count := 0;
176 x_msg_data := 0;
177 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
178 l_number_of_warnings := 0;
179 l_number_of_errors := 0;
180
181 dbms_output.put_line( ' Testing 12 ' || p_delivery_leg_activity_rec.activity_type);
182 dbms_output.put_line( ' Trip Id ' || p_trip_id);
183
184
185 FOR c_rec IN c_dlegs(p_trip_id) LOOP
186
187 SELECT wsh_delivery_leg_activities_s.nextval into s_activity_id FROM DUAL;
188
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.logmsg(l_module_name,'Inserting',WSH_DEBUG_SV.C_EXCEP_LEVEL);
191 END IF;
192
193 dbms_output.put_line( ' Testing 1212 ' || p_delivery_leg_activity_rec.activity_type);
194
195
196 INSERT INTO wsh_delivery_leg_activities
197 ( ACTIVITY_ID,
198 DELIVERY_LEG_ID,
199 ACTIVITY_DATE,
200 ACTIVITY_TYPE,
201 CREATION_DATE,
202 CREATED_BY,
203 LAST_UPDATE_DATE,
204 LAST_UPDATED_BY,
205 LAST_UPDATE_LOGIN,
206 ACTION_BY,
207 ACTION_BY_NAME,
208 REMARKS,
209 RESULT_STATUS,
210 INITIAL_STATUS,
211 TRIP_ID,
212 CARRIER_ID,
213 MODE_OF_TRANSPORT,
214 SERVICE_LEVEL,
215 RANK_ID,
216 RANK_VERSION,
217 WF_ITEM_KEY)
218 VALUES
219 ( s_activity_id,
220 c_rec.delivery_leg_id,
221 SYSDATE,
222 p_delivery_leg_activity_rec.activity_type,
223 SYSDATE,
224 FND_GLOBAL.USER_ID, -- created by
225 SYSDATE, -- last update date
226 FND_GLOBAL.USER_ID, -- last updatd by
227 FND_GLOBAL.USER_ID, -- last upd login
228 p_delivery_leg_activity_rec.action_by,
229 p_delivery_leg_activity_rec.action_by_name,
230 p_delivery_leg_activity_rec.remarks,
231 p_delivery_leg_activity_rec.result_status,
232 p_delivery_leg_activity_rec.initial_status,
233 p_delivery_leg_activity_rec.trip_id,
234 c_rec.carrier_id,
235 c_rec.mode_of_transport,
236 c_rec.service_level,
237 p_delivery_leg_activity_rec.rank_id,
238 p_delivery_leg_activity_rec.rank_version,
239 p_delivery_leg_activity_rec.wf_item_key);
240
241
242 END LOOP;
243
244
245 -- Standard call to get message count and if count is 1,get message info.
246 --
247 FND_MSG_PUB.Count_And_Get
248 (
249 p_count => x_msg_count,
250 p_data => x_msg_data,
251 p_encoded => FND_API.G_FALSE
252 );
253
254
255 --
256 --
257
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.pop(l_module_name);
260 END IF;
261
262 --}
263 EXCEPTION
264 --{
265 WHEN FND_API.G_EXC_ERROR THEN
266 ROLLBACK TO ADD_HISTORY_PUB;
267 x_return_status := FND_API.G_RET_STS_ERROR ;
268 FND_MSG_PUB.Count_And_Get
269 (
270 p_count => x_msg_count,
271 p_data => x_msg_data,
272 p_encoded => FND_API.G_FALSE
273 );
274 IF l_debug_on THEN
275 WSH_DEBUG_SV.logmsg(l_module_name,'Error Occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
276 WSH_DEBUG_SV.pop(l_module_name);
277 END IF;
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 ROLLBACK TO ADD_HISTORY_PUB;
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
281 FND_MSG_PUB.Count_And_Get
282 (
283 p_count => x_msg_count,
284 p_data => x_msg_data,
285 p_encoded => FND_API.G_FALSE
286 );
287 IF l_debug_on THEN
288 WSH_DEBUG_SV.logmsg(l_module_name,'Unxepected Error Occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
289 WSH_DEBUG_SV.pop(l_module_name);
290 END IF;
291 WHEN OTHERS THEN
292 ROLLBACK TO ADD_HISTORY_PUB;
293 wsh_util_core.default_handler('FTE_DELIVERY_ACTIVITY.ADD_HISTORY');
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 FND_MSG_PUB.Count_And_Get
296 (
297 p_count => x_msg_count,
298 p_data => x_msg_data,
299 p_encoded => FND_API.G_FALSE
300 );
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.logmsg(l_module_name,'Other error Occured',WSH_DEBUG_SV.C_EXCEP_LEVEL);
303 WSH_DEBUG_SV.pop(l_module_name);
304 END IF;
305 --}
306 END ADD_HISTORY;
307
308 END FTE_DELIVERY_ACTIVITY;