[Home] [Help]
PACKAGE BODY: APPS.FTE_MOVES_PVT
Source
1 PACKAGE BODY FTE_MOVES_PVT AS
2 /* $Header: FTEMVTHB.pls 120.0 2005/05/26 17:08:04 appldev noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FTE_MOVES_PVT';
8
9 --========================================================================
10 -- PROCEDURE : CREATE_MOVE
11 --
12 -- PARAMETERS: p_init_msg_list
13 -- p_move_info Attributes for the trip moves entity
14 -- x_return_status Return status of API
15 -- COMMENT :
16 --========================================================================
17
18 PROCEDURE CREATE_MOVE(
19 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
20 p_move_info IN MOVE_REC_TYPE,
21 x_move_id OUT NOCOPY NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2
23 ) IS
24
25 CURSOR get_next_move IS
26 SELECT fte_moves_s.nextval
27 FROM sys.dual;
28
29 l_temp_id NUMBER;
30 EMPTY_MOVE_TYPE_CODE EXCEPTION;
31 EMPTY_PLANNED_FLAG EXCEPTION;
32
33 BEGIN
34
35 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
36
37 IF FND_API.to_Boolean( p_init_msg_list )
38 THEN
39 FND_MSG_PUB.initialize;
40 END IF;
41
42
43 if (p_move_info.move_type_code IS NULL or
44 p_move_info.move_type_code = FND_API.G_MISS_CHAR) then
45 RAISE EMPTY_MOVE_TYPE_CODE;
46 end if;
47
48
49 if (p_move_info.PLANNED_FLAG IS null or
50 p_move_info.PLANNED_FLAG = FND_API.G_MISS_CHAR) then
51 RAISE EMPTY_PLANNED_FLAG;
52 end if;
53
54 OPEN get_next_move;
55 FETCH get_next_move INTO x_move_id;
56 CLOSE get_next_move;
57
58 insert into FTE_MOVES
59 (
60 MOVE_ID ,
61 MOVE_TYPE_CODE ,
62 LANE_ID ,
63 SERVICE_LEVEL,
64 PLANNED_FLAG,
65 TP_PLAN_NAME,
66 CM_TRIP_NUMBER,
67 CREATION_DATE ,
68 CREATED_BY ,
69 LAST_UPDATE_DATE ,
70 LAST_UPDATED_BY ,
71 LAST_UPDATE_LOGIN ,
72 PROGRAM_APPLICATION_ID ,
73 PROGRAM_ID ,
74 PROGRAM_UPDATE_DATE ,
75 REQUEST_ID
76 )
77 values
78 (
79 x_move_id,
80 p_move_info.MOVE_TYPE_CODE,
81 decode(p_move_info.LANE_ID,FND_API.G_MISS_NUM,NULL,p_move_info.LANE_ID),
82 decode(p_move_info.SERVICE_LEVEL,FND_API.G_MISS_CHAR,NULL,p_move_info.SERVICE_LEVEL),
83 p_move_info.PLANNED_FLAG,
84 decode(p_move_info.TP_PLAN_NAME,FND_API.G_MISS_CHAR,NULL,p_move_info.TP_PLAN_NAME),
85 decode(p_move_info.CM_TRIP_NUMBER,FND_API.G_MISS_NUM,NULL,p_move_info.CM_TRIP_NUMBER),
86 decode(p_move_info.creation_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_move_info.creation_date),
87 decode(p_move_info.created_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_move_info.created_by),
88 decode(p_move_info.last_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE, p_move_info.last_update_date),
89 decode(p_move_info.last_updated_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_move_info.last_updated_by),
90 decode(p_move_info.last_update_login,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.LOGIN_ID,p_move_info.last_update_login),
91 decode(p_move_info.program_application_id,NULL,FND_GLOBAL.PROG_APPL_ID,FND_API.G_MISS_NUM,FND_GLOBAL.PROG_APPL_ID,p_move_info.program_application_id),
92 decode(p_move_info.program_id,NULL,FND_GLOBAL.CONC_PROGRAM_ID,FND_API.G_MISS_NUM, FND_GLOBAL.CONC_PROGRAM_ID,p_move_info.program_id),
93 decode(p_move_info.program_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_move_info.program_update_date),
94 decode(p_move_info.request_id,NULL,FND_GLOBAL.CONC_REQUEST_ID,FND_API.G_MISS_NUM,FND_GLOBAL.CONC_REQUEST_ID, p_move_info.request_id)
95 );
96
97
98
99 EXCEPTION
100 WHEN EMPTY_MOVE_TYPE_CODE THEN
101 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_TYPE_CODE_MISSING');
102 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
103 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
104 WHEN EMPTY_PLANNED_FLAG THEN
105 FND_MESSAGE.SET_NAME('FTE', 'FTE_MOVE_PLANNED_FLAG_MISSING');
106 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
107 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
108 WHEN others THEN
109 wsh_util_core.default_handler('FTE_MOVES.CREATE_MOVES');
110 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
111 END CREATE_MOVE;
112
113
114 --========================================================================
115 -- PROCEDURE : Update_MOVES
116 --
117 -- PARAMETERS: p_move_info Attributes for the trip entity
118 -- x_return_status Return status of API
119 -- COMMENT : Updates trip record with p_trip_info information
120 --========================================================================
121
122 PROCEDURE UPDATE_MOVE(
123 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
124 p_move_info IN move_rec_type,
125 x_return_status OUT NOCOPY VARCHAR2
126 ) IS
127
128
129 BEGIN
130
131 IF FND_API.to_Boolean( p_init_msg_list )
132 THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135
136 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
137
138
139 UPDATE FTE_MOVES SET
140 MOVE_TYPE_CODE =decode(p_move_info.MOVE_TYPE_CODE,
141 NULL,MOVE_TYPE_CODE,
142 FND_API.G_MISS_CHAR,MOVE_TYPE_CODE,
143 p_move_info.MOVE_TYPE_CODE),
144 LANE_ID =decode(p_move_info.LANE_ID,
145 NULL,LANE_ID,
146 FND_API.G_MISS_NUM,NULL,
147 p_move_info.LANE_ID),
148 SERVICE_LEVEL =decode(p_move_info.SERVICE_LEVEL,
149 NULL,SERVICE_LEVEL,
150 FND_API.G_MISS_CHAR,NULL,
151 p_move_info.SERVICE_LEVEL),
152 PLANNED_FLAG =decode(p_move_info.PLANNED_FLAG,
153 NULL,PLANNED_FLAG,
154 FND_API.G_MISS_CHAR,PLANNED_FLAG,
155 p_move_info.PLANNED_FLAG),
156 TP_PLAN_NAME =decode(p_move_info.TP_PLAN_NAME,
157 NULL,TP_PLAN_NAME,
158 FND_API.G_MISS_CHAR,NULL,
159 p_move_info.TP_PLAN_NAME),
160 CM_TRIP_NUMBER =decode(p_move_info.CM_TRIP_NUMBER,
161 NULL,CM_TRIP_NUMBER,
162 FND_API.G_MISS_NUM,NULL,
163 p_move_info.CM_TRIP_NUMBER),
164 last_update_date = decode(p_move_info.last_update_date,
165 NULL,SYSDATE,
166 FND_API.G_MISS_DATE,SYSDATE,
167 p_move_info.last_update_date),
168 last_updated_by = decode(p_move_info.last_updated_by,
169 NULL,FND_GLOBAL.USER_ID,
170 FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,
171 p_move_info.last_updated_by),
172 last_update_login = decode(p_move_info.last_update_login,
173 NULL,FND_GLOBAL.LOGIN_ID,
174 FND_API.G_MISS_NUM, FND_GLOBAL.LOGIN_ID,
175 p_move_info.last_update_login),
176 program_application_id = decode(p_move_info.program_application_id,
177 NULL,program_application_id,
178 FND_API.G_MISS_NUM,FND_GLOBAL.PROG_APPL_ID,
179 p_move_info.program_application_id),
180 program_id = decode(p_move_info.program_id,
181 NULL,program_id,FND_API.G_MISS_NUM,
182 FND_GLOBAL.CONC_PROGRAM_ID,
183 p_move_info.program_id),
184 program_update_date = decode(p_move_info.program_update_date,
185 NULL,program_update_date,
186 FND_API.G_MISS_DATE,SYSDATE,
187 p_move_info.program_update_date),
188 request_id = decode(p_move_info.request_id,
189 NULL,request_id,FND_API.G_MISS_NUM,
190 FND_GLOBAL.CONC_REQUEST_ID,p_move_info.request_id)
191 WHERE move_id = p_move_info.move_id;
192
193 IF (SQL%NOTFOUND) THEN
194 RAISE no_data_found;
195 END IF;
196
197 EXCEPTION
198 WHEN no_data_found THEN
199 FND_MESSAGE.SET_NAME('FTE','FTE_MOVE_NOT_FOUND');
200 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
201 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
202 WHEN others THEN
203 wsh_util_core.default_handler('FTE_MOVES_PVT.UPDATE_MOVES');
204 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
205
206 END UPDATE_MOVE;
207
208
209 PROCEDURE DELETE_MOVE(
210 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
211 p_move_id IN NUMBER,
212 p_validate_flag IN VARCHAR2 DEFAULT 'Y',
213 x_return_status OUT NOCOPY VARCHAR2
214 ) IS
215
216
217 BEGIN
218
219 IF FND_API.to_Boolean( p_init_msg_list )
220 THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223
224 x_return_Status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
225
226 DELETE FROM fte_moves
227 WHERE move_id = p_move_id;
228
229 IF (SQL%NOTFOUND) THEN
230 FND_MESSAGE.SET_NAME('FTE','FTE_MOVE_NOT_FOUND');
231 FND_MESSAGE.SET_TOKEN('MOVE_ID', p_move_id);
232 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
233 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
234 END IF;
235
236 -- delete corresponding entries from FTE_TRIP_MOVES
237 DELETE FROM FTE_TRIP_MOVES
238 WHERE MOVE_ID = p_move_id;
239
240
241 EXCEPTION
242 WHEN others THEN
243 wsh_util_core.default_handler('FTE_MOVES_PVT.DELETE_MOVE');
244 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
245
246 END DELETE_MOVE;
247
248
249 PROCEDURE MARK_MOVE_REPRICE_FLAG(
250 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
251 p_MOVE_id IN NUMBER,
252 x_return_status OUT NOCOPY VARCHAR2,
253 x_msg_count OUT NOCOPY VARCHAR2,
254 x_msg_data OUT NOCOPY VARCHAR2
255 ) IS
256
257
258 cursor get_trips_from_move_cur( c_move_id NUMBER) is
259 SELECT trip_id
260 FROM fte_trip_moves
261 WHERE move_id = c_move_id;
262
263
264 l_return_status VARCHAR2(32767);
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(32767);
267 l_number_of_warnings NUMBER;
268 l_number_of_errors NUMBER;
269
270 l_entity_ids WSH_UTIL_CORE.id_tab_type;
271 idx NUMBER;
272
273 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'MARK_MOVE_REPRICE_FLAG';
274 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
275
276 BEGIN
277
278 SAVEPOINT MARK_MOVE_REPRICE_FLAG_PUB;
279
280
281 IF FND_API.to_Boolean( p_init_msg_list )
282 THEN
283 FND_MSG_PUB.initialize;
284 END IF;
285
286 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
287 x_msg_count := 0;
288 x_msg_data := 0;
289 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
290 l_number_of_warnings := 0;
291 l_number_of_errors := 0;
292
293 IF l_debug_on THEN
294 wsh_debug_sv.push(l_module_name);
295 END IF;
296
297 idx := 1;
298 FOR get_trips_from_move_rec IN get_trips_from_move_cur(p_MOVE_id)
299 LOOP
300 --{
301 l_entity_ids(idx) := get_trips_from_move_rec.trip_id;
302 idx := idx+1;
303 --}
304 END LOOP;
305 -- END OF
306 --
307 --
308 IF l_debug_on THEN
309 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:27 ',WSH_DEBUG_SV.C_PROC_LEVEL);
310 END IF;
311
312 IF get_trips_from_move_cur%ISOPEN THEN
313 CLOSE get_trips_from_move_cur;
314 END IF;
315
316
317
318 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required(
319 p_entity_type => 'TRIP',
320 p_entity_ids => l_entity_ids,
321 x_return_status => l_return_status);
322
323 wsh_util_core.api_post_call(
324 p_return_status =>l_return_status,
325 x_num_warnings =>l_number_of_warnings,
326 x_num_errors =>l_number_of_errors);
327
328 IF l_number_of_errors > 0
329 THEN
330 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
331 ELSIF l_number_of_warnings > 0
332 THEN
333 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
334 ELSE
335 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
336 END IF;
337
338 FND_MSG_PUB.Count_And_Get
339 (
340 p_count => x_msg_count,
341 p_data => x_msg_data,
342 p_encoded => FND_API.G_FALSE
343 );
344
345 IF l_debug_on THEN
346 WSH_DEBUG_SV.pop(l_module_name);
347 END IF;
348
349
350
351 EXCEPTION
352 WHEN FND_API.G_EXC_ERROR THEN
353 ROLLBACK TO MARK_MOVE_REPRICE_FLAG_PUB;
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 FND_MSG_PUB.Count_And_Get
356 (
357 p_count => x_msg_count,
358 p_data => x_msg_data,
359 p_encoded => FND_API.G_FALSE
360 );
361
362 IF l_debug_on THEN
363 WSH_DEBUG_SV.logmsg(l_module_name,' Error Occured ' ||
364 x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
365 WSH_DEBUG_SV.pop(l_module_name);
366 END IF;
367
368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369 ROLLBACK TO MARK_MOVE_REPRICE_FLAG_PUB;
370 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
371 FND_MSG_PUB.Count_And_Get
372 (
373 p_count => x_msg_count,
374 p_data => x_msg_data,
375 p_encoded => FND_API.G_FALSE
376 );
377 IF l_debug_on THEN
378 WSH_DEBUG_SV.logmsg(l_module_name,' Unexpected error Occured ' ||
379 x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
380 WSH_DEBUG_SV.pop(l_module_name);
381 END IF;
382
383 WHEN OTHERS THEN
384 IF l_debug_on THEN
385 WSH_DEBUG_SV.logmsg(l_module_name,'P1DEBUG:13 ',WSH_DEBUG_SV.C_PROC_LEVEL);
386 END IF;
387
388 ROLLBACK TO MARK_MOVE_REPRICE_FLAG_PUB;
389 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
390 FND_MSG_PUB.Count_And_Get
391 (
392 p_count => x_msg_count,
393 p_data => x_msg_data,
394 p_encoded => FND_API.G_FALSE
395 );
396 IF l_debug_on THEN
397 WSH_DEBUG_SV.logmsg(l_module_name,' SQL Error Occured ' ||
398 SQLCODE||' '||SQLERRM, WSH_DEBUG_SV.C_PROC_LEVEL);
399 WSH_DEBUG_SV.pop(l_module_name);
400 END IF;
401
402
403 END MARK_MOVE_REPRICE_FLAG;
404
405
406 --
407 --
408 END FTE_MOVES_PVT;