[Home] [Help]
PACKAGE BODY: APPS.WSH_DATA_PROTECTION
Source
1 PACKAGE BODY WSH_DATA_PROTECTION as
2 /* $Header: WSHUTDPB.pls 120.0 2005/05/26 18:36:34 appldev noship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_DATA_PROTECTION';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8 --
9 -- Procedure: Get_Disabled_List
10 --
11 -- Parameters: p_entity_type - type of entity: DLVB, DLVY, DLEG, STOP, TRIP
12 -- p_entity_id - Id for entity
13 -- p_parent_entity_id - Parent Id for entity:
14 -- DLVY is parent for DLVB and DLEG
15 -- TRIP is parent for STOP
16 -- p_entity_status - Status of entity
17 -- p_entity_planned_state - Planned state of entity
18 -- p_list_type - Type of column names to choose
19 -- 'WSHFSTRX' will return STF field names
20 -- unless p_caller is like FTE%
21 -- x_disabled_list - list of disabled columns
22 -- x_return_status - Status of procedure call
23 -- p_caller - identify caller; FTE% will get table column names
24 --
25 -- Description: This procedure will return a list of disabled columns for
26 -- update restrictions on the form, unless the first element
27 -- has the value 'FULL', in which case the list is as below:
28 -- 'FULL' and list count = 1, means all columns need to
29 -- be disabled
30 -- 'FULL' and list count > 1, means all columns except
31 -- the columns that follow are disabled or "entered."
32 -- '+column_name' (i.e., column name marked by '+')
33 -- means that column_name has "Entered" status,
34 -- which is disabled only if the column has a
35 -- non-NULL value (i.e., enabled only if NULL).
36
37 PROCEDURE Get_Disabled_List (
38 -- Standard parameters
39 p_api_version IN NUMBER,
40 p_init_msg_list IN VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44
45 -- Program Specific Parameters
46 p_entity_type IN VARCHAR2,
47 p_entity_id IN NUMBER,
48 p_parent_entity_id IN NUMBER,
49 p_list_type IN VARCHAR2,
50 x_disabled_list OUT NOCOPY wsh_util_core.column_tab_type,
51 p_caller IN VARCHAR2 DEFAULT NULL
52 )
53 IS
54
55 -- standard version infermation
56 l_api_version CONSTANT NUMBER := 1.0;
57 l_api_name CONSTANT VARCHAR2(30):= 'Get_Disabled_List';
58
59 CURSOR get_delivery_status(x_delivery_id NUMBER) IS
60 SELECT status_code, planned_flag
61 FROM wsh_new_deliveries
62 WHERE delivery_id = x_delivery_id;
63
64 CURSOR has_delivery_details(x_delivery_id NUMBER) IS
65 SELECT delivery_assignment_id
66 FROM wsh_delivery_assignments_v
67 WHERE delivery_id = x_delivery_id
68 AND rownum = 1;
69
70 CURSOR has_legs(x_delivery_id NUMBER) IS
71 SELECT delivery_leg_id
72 FROM wsh_delivery_legs
73 WHERE delivery_id = x_delivery_id
74 AND rownum = 1;
75
76 CURSOR get_trip_status(x_entity_id NUMBER) IS
77 SELECT status_code, planned_flag
78 FROM wsh_trips
79 WHERE trip_id = x_entity_id;
80
81 CURSOR get_stop_status(x_stop_id NUMBER) IS
82 SELECT status_code
83 FROM wsh_trip_stops
84 WHERE stop_id = x_stop_id;
85
86 CURSOR get_leg_status(x_leg_id NUMBER) IS
87 SELECT delivery_id, pick_up_stop_id, drop_off_stop_id
88 FROM wsh_delivery_legs
89 WHERE delivery_leg_id = x_leg_id;
90
91 CURSOR has_pick_up_deliveries(x_stop_id NUMBER) IS
92 SELECT delivery_id
93 FROM wsh_delivery_legs
94 WHERE pick_up_stop_id = x_stop_id
95 AND rownum = 1;
96
97 CURSOR has_drop_off_deliveries(x_stop_id NUMBER) IS
98 SELECT delivery_id
99 FROM wsh_delivery_legs
100 WHERE drop_off_stop_id = x_stop_id
101 AND rownum = 1;
102
103 i NUMBER := 0;
104 dummy_id NUMBER := 0;
105 l_status_code VARCHAR2(10) := NULL;
106 l_planned_flag VARCHAR2(10) := NULL;
107
108 l_pick_up_stop get_stop_status%ROWTYPE;
109 l_drop_off_stop get_stop_status%ROWTYPE;
110 l_delivery_id NUMBER := 0;
111 l_pick_up_stop_id NUMBER := 0;
112 l_drop_off_stop_id NUMBER := 0;
113
114 l_msg_summary VARCHAR2(2000) := NULL;
115 l_msg_details VARCHAR2(4000) := NULL;
116
117 WSH_INV_LIST_TYPE EXCEPTION;
118 WSH_DP_NO_ENTITY EXCEPTION;
119 WSH_DP_NO_STOP EXCEPTION;
120
121 --
122 l_debug_on BOOLEAN;
123 --
124 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISABLED_LIST';
125 --
126 BEGIN
127
128 -- Standard begin of API savepoint
129 --
130 -- Debug Statements
131 --
132 --
133 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
134 --
135 IF l_debug_on IS NULL
136 THEN
137 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
138 END IF;
139 --
140 IF l_debug_on THEN
141 WSH_DEBUG_SV.push(l_module_name);
142 --
143 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
144 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
145 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_TYPE',P_ENTITY_TYPE);
146 WSH_DEBUG_SV.log(l_module_name,'P_ENTITY_ID',P_ENTITY_ID);
147 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_ENTITY_ID',P_PARENT_ENTITY_ID);
148 WSH_DEBUG_SV.log(l_module_name,'P_LIST_TYPE',P_LIST_TYPE);
149 WSH_DEBUG_SV.log(l_module_name,'P_CALLER',P_CALLER);
150 END IF;
151 --
152 SAVEPOINT Log_Exception_PUB;
153 -- Standard call to check for call compatibility.
154 IF NOT FND_API.compatible_api_call( l_api_version,
155 p_api_version,
156 l_api_name,
157 G_PKG_NAME) THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 -- Check p_init_msg_list
162 IF FND_API.to_boolean(p_init_msg_list) THEN
163 FND_MSG_PUB.initialize;
164 END IF;
165
166 -- initialize API return status to success
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 -- clear up the list table
170 x_disabled_list.delete;
171
172 /*
173 ***
174 **** TRIP
175 ***
176 */
177 IF (p_entity_type = 'TRIP') THEN
178 --
179 -- Debug Statements
180 --
181 IF l_debug_on THEN
182 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_VALIDATIONS.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
183 END IF;
184 --
185 WSH_TRIP_VALIDATIONS.Get_Disabled_List(
186 p_trip_id => p_entity_id,
187 p_list_type => p_list_type,
188 x_return_status => x_return_status,
189 x_disabled_list =>x_disabled_list,
190 x_msg_count => x_msg_count,
191 x_msg_data => x_msg_data,
192 p_caller => p_caller
193 );
194
195
196 /*
197 ***
198 **** STOP
199 ***
200 */
201 ELSIF (p_entity_type = 'TRIP STOP') THEN
202 --
203 -- Debug Statements
204 --
205 IF l_debug_on THEN
206 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_TRIP_STOPS_PVT.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
207 END IF;
208 --
209 WSH_TRIP_STOPS_PVT.Get_Disabled_List(
210 p_stop_id => p_entity_id,
211 p_parent_entity_id => p_parent_entity_id,
212 p_list_type => p_list_type,
213 x_return_status => x_return_status,
214 x_disabled_list =>x_disabled_list,
215 x_msg_count => x_msg_count,
216 x_msg_data => x_msg_data,
217 p_caller => p_caller
218 );
219
220 /*
221 ***
222 **** DLEG
223 ***
224 */
225
226 ELSIF (p_entity_type = 'DELIVERY LEG') or
227 (p_entity_type = 'BILL OF LADING') THEN
228 --
229 -- Debug Statements
230 --
231 IF l_debug_on THEN
232 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
233 END IF;
234 --
235 WSH_DELIVERY_LEGS_PVT.Get_Disabled_List(
236 p_delivery_leg_id => p_entity_id,
237 p_parent_entity_id => p_parent_entity_id,
238 p_list_type => p_list_type,
239 x_return_status => x_return_status,
240 x_disabled_list =>x_disabled_list,
241 x_msg_count => x_msg_count,
242 x_msg_data => x_msg_data
243 );
244
245
246 /*
247 ***
248 **** DELIVERY DETAIL
249 ***
250 */
251
252
253
254 ELSIF (p_entity_type = 'DELIVERY DETAIL') THEN
255 --
256 -- Debug Statements
257 --
258 IF l_debug_on THEN
259 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DETAILS_VALIDATIONS.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
260 END IF;
261 --
262 WSH_DETAILS_VALIDATIONS.Get_Disabled_List(
263 p_delivery_detail_id => p_entity_id,
264 p_delivery_id => p_parent_entity_id,
265 p_list_type => p_list_type,
266 x_return_status => x_return_status,
267 x_disabled_list => x_disabled_list,
268 x_msg_count => x_msg_count,
269 x_msg_data => x_msg_data,
270 p_caller => p_caller
271 );
272
273 /*
274 ***
275 **** DELIVERY
276 ***
277 */
278 ELSIF (p_entity_type = 'DELIVERY') THEN
279 --
280 -- Debug Statements
281 --
282 IF l_debug_on THEN
283 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_VALIDATIONS.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
284 END IF;
285 --
286 WSH_DELIVERY_VALIDATIONS.Get_Disabled_List(
287 p_delivery_id => p_entity_id,
288 p_list_type => p_list_type,
289 x_return_status => x_return_status,
290 x_disabled_list => x_disabled_list,
291 x_msg_count => x_msg_count,
292 x_msg_data => x_msg_data,
293 p_caller => p_caller
294 );
295
296 /*
297 ***
298 **** PACK SLIP
299 ***
300 */
301 ELSIF (p_entity_type = 'PACK SLIP') THEN
302 --
303 -- Debug Statements
304 --
305 IF l_debug_on THEN
306 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PACKING_SLIPS_PVT.GET_DISABLED_LIST',WSH_DEBUG_SV.C_PROC_LEVEL);
307 END IF;
308 --
309 WSH_PACKING_SLIPS_PVT.Get_Disabled_List(
310 p_delivery_id => p_entity_id,
311 p_list_type => p_list_type,
312 x_return_status => x_return_status,
313 x_disabled_list => x_disabled_list,
314 x_msg_count => x_msg_count,
315 x_msg_data => x_msg_data
316 );
317
318 END IF;
319
320
321 --
322 -- Debug Statements
323 --
324 IF l_debug_on THEN
325 WSH_DEBUG_SV.pop(l_module_name);
326 END IF;
327 --
328 EXCEPTION
329
330 WHEN WSH_DP_NO_ENTITY THEN
331 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_ENTITY');
332 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
335 if x_msg_count > 1 then
336 x_msg_data := l_msg_summary || l_msg_details;
337 else
338 x_msg_data := l_msg_summary;
339 end if;
340 --
341 -- Debug Statements
342 --
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_ENTITY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
345 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_ENTITY');
346 END IF;
347 --
348 WHEN WSH_DP_NO_STOP THEN
349 FND_MESSAGE.SET_NAME('WSH', 'WSH_DP_NO_STOP');
350 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
351 x_return_status := FND_API.G_RET_STS_ERROR;
352 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
353 if x_msg_count > 1 then
354 x_msg_data := l_msg_summary || l_msg_details;
355 else
356 x_msg_data := l_msg_summary;
357 end if;
358 --
359 -- Debug Statements
360 --
361 IF l_debug_on THEN
362 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_DP_NO_STOP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
363 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_DP_NO_STOP');
364 END IF;
365 --
366 WHEN WSH_INV_LIST_TYPE THEN
367 FND_MESSAGE.SET_NAME('WSH', 'WSH_INV_LIST_TYPE');
368 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type);
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 WSH_UTIL_CORE.get_messages('Y', l_msg_summary, l_msg_details, x_msg_count);
371 if x_msg_count > 1 then
372 x_msg_data := l_msg_summary || l_msg_details;
373 else
374 x_msg_data := l_msg_summary;
375 end if;
376 --
377 -- Debug Statements
378 --
379 IF l_debug_on THEN
380 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_INV_LIST_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
381 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_INV_LIST_TYPE');
382 END IF;
383 --
384 WHEN OTHERS THEN
385 IF get_delivery_status%ISOPEN THEN
386 CLOSE get_delivery_status;
387 END IF;
388 IF has_delivery_details%ISOPEN THEN
389 CLOSE has_delivery_details;
390 END IF;
391 IF has_legs%ISOPEN THEN
392 CLOSE has_legs;
393 END IF;
394
395 IF get_trip_status%ISOPEN THEN
396 CLOSE get_trip_status;
397 END IF;
398
399 FND_MESSAGE.Set_Name('WSH','WSH_UNEXPECTED_ERROR');
400 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
401
402 --
403 -- Debug Statements
404 --
405 IF l_debug_on THEN
406 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
407 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
408 END IF;
409 --
410 END Get_Disabled_List;
411
412
413 END WSH_DATA_PROTECTION;