[Home] [Help]
PACKAGE BODY: APPS.WSH_FOLDER_EXTENSIONS_PVT
Source
1 PACKAGE BODY WSH_FOLDER_EXTENSIONS_PVT as
2 /* $Header: WSHFDEXB.pls 115.7 2004/02/17 01:11:53 ttrichy noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_FOLDER_EXTENSIONS_PVT';
5
6 Procedure Insert_Update_Folder_Ext(
7 p_folder_ext_rec IN folder_ext_rec_type,
8 x_return_status OUT NOCOPY VARCHAR2)
9 IS PRAGMA AUTONOMOUS_TRANSACTION;
10
11 CURSOR c_ext_rec_cur(p_folder_id NUMBER) IS
12 SELECT folder_extension_id
13 FROM wsh_folder_extensions
14 WHERE folder_id = p_folder_id
15 FOR UPDATE NOWAIT;
16
17 l_folder_extension_id NUMBER;
18 --
19 l_debug_on BOOLEAN;
20 --
21 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_UPDATE_FOLDER_EXT';
22 --
23 BEGIN
24
25 -- initialize parameters
26 --
27 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
28 --
29 IF l_debug_on IS NULL
30 THEN
31 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
32 END IF;
33 --
34 IF l_debug_on THEN
35 WSH_DEBUG_SV.push(l_module_name);
36 WSH_DEBUG_SV.log(l_module_name, 'folder id', p_folder_ext_rec.folder_id);
37 WSH_DEBUG_SV.log(l_module_name, 'folder extension id', p_folder_ext_rec.folder_extension_id);
38 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_DLVY_OTHERS', p_folder_ext_rec.DISPLAY_DLVY_OTHERS);
39 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_LINE_OTHERS',p_folder_ext_rec.display_line_others);
40 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_TRIP_OTHERS',p_folder_ext_rec.display_trip_others);
41 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_STOP_OTHERS',p_folder_ext_rec.display_stop_others);
42 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_QM_LINE_OTHERS',p_folder_ext_rec.display_qm_line_others);
43 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_SHIP_CONF_DIALOGUE',p_folder_ext_rec.display_ship_conf_dialogue);
44 WSH_DEBUG_SV.log(l_module_name, 'DISPLAY_TRIP_INFO',p_folder_ext_rec.display_trip_info);
45 WSH_DEBUG_SV.log(l_module_name, 'Userid', p_folder_ext_rec.user_id);
46 END IF;
47 --
48 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
49
50 OPEN c_ext_rec_cur(p_folder_ext_rec.folder_id);
51 FETCH c_ext_rec_cur INTO l_folder_extension_id;
52
53 IF c_ext_rec_cur%FOUND THEN
54
55 UPDATE WSH_FOLDER_EXTENSIONS
56 SET
57 user_id = nvl(p_folder_ext_rec.user_id, FND_GLOBAL.USER_ID),
58 application_id = nvl(p_folder_ext_rec.application_id, 665),
59 display_dlvy_main = nvl(p_folder_ext_rec.display_dlvy_main,'Y'), -- always display
60 display_dlvy_others = nvl(p_folder_ext_rec.display_dlvy_others,'N'),
61 display_line_main = nvl(p_folder_ext_rec.display_line_main,'Y'), -- always display
62 display_line_others = nvl(p_folder_ext_rec.display_line_others,'N'), -- By default is not visible
63 display_trip_main = nvl(p_folder_ext_rec.display_trip_main,'Y'), -- always display
64 display_trip_others = nvl(p_folder_ext_rec.display_trip_others,'N'),
65 display_stop_main = nvl(p_folder_ext_rec.display_stop_main,'Y'), -- always display
66 display_stop_others = nvl(p_folder_ext_rec.display_stop_others,'N'),
67 display_qm_line_main = nvl(p_folder_ext_rec.display_qm_line_main,'Y'), -- always display
68 display_qm_line_others = nvl(p_folder_ext_rec.display_qm_line_others,'N'), -- By default is not visible
69 display_ship_conf_dialogue = nvl(p_folder_ext_rec.display_ship_conf_dialogue,'Y'), -- By default is visible
70 display_trip_conf_dialogue = nvl(p_folder_ext_rec.display_trip_conf_dialogue,'Y'), -- By default is visible
71 display_trip_info = nvl(p_folder_ext_rec.display_trip_info,'Y'), -- By default is visible
72 last_update_date = nvl(p_folder_ext_rec.last_update_date, SYSDATE),
73 last_updated_by = nvl(p_folder_ext_rec.last_updated_by, FND_GLOBAL.USER_ID),
74 last_update_login = nvl(p_folder_ext_rec.last_update_login, FND_GLOBAL.LOGIN_ID)
75 WHERE
76 folder_id = p_folder_ext_rec.folder_id;
77 ELSE
78
79 INSERT INTO WSH_FOLDER_EXTENSIONS
80 (FOLDER_EXTENSION_ID,
81 OBJECT,
82 USER_ID,
83 FOLDER_ID,
84 APPLICATION_ID,
85 DISPLAY_DLVY_MAIN,
86 DISPLAY_DLVY_OTHERS,
87 DISPLAY_LINE_MAIN,
88 DISPLAY_LINE_OTHERS,
89 DISPLAY_TRIP_MAIN,
90 DISPLAY_TRIP_OTHERS,
91 DISPLAY_STOP_MAIN,
92 DISPLAY_STOP_OTHERS,
93 DISPLAY_QM_LINE_MAIN,
94 DISPLAY_QM_LINE_OTHERS,
95 DISPLAY_SHIP_CONF_DIALOGUE,
96 DISPLAY_TRIP_CONF_DIALOGUE,
97 DISPLAY_TRIP_INFO,
98 CREATION_DATE,
99 CREATED_BY,
100 LAST_UPDATE_DATE,
101 LAST_UPDATED_BY,
102 LAST_UPDATE_LOGIN
103 )
104 VALUES
105 (
106 wsh_folder_extension_s.nextval,
107 p_folder_ext_rec.object,
108 nvl(p_folder_ext_rec.user_id,FND_GLOBAL.USER_ID),
109 p_folder_ext_rec.folder_id,
110 nvl(p_folder_ext_rec.application_id,665),
111 nvl(p_folder_ext_rec.display_dlvy_main,'Y'), -- always display
112 nvl(p_folder_ext_rec.display_dlvy_others,'N'),
113 nvl(p_folder_ext_rec.display_line_main,'Y'), -- always display
114 nvl(p_folder_ext_rec.display_line_others,'N'), -- By default is not visible
115 nvl(p_folder_ext_rec.display_trip_main,'Y'), -- always display
116 nvl(p_folder_ext_rec.display_trip_others,'N'),
117 nvl(p_folder_ext_rec.display_stop_main,'Y'), -- always display
118 nvl(p_folder_ext_rec.display_stop_others,'N'),
119 nvl(p_folder_ext_rec.display_qm_line_main,'Y'), -- always display
120 nvl(p_folder_ext_rec.display_qm_line_others,'N'), -- By default is not visible
121 nvl(p_folder_ext_rec.display_ship_conf_dialogue,'Y'), -- By default is visible
122 nvl(p_folder_ext_rec.display_trip_conf_dialogue,'Y'), -- By default is visible
123 nvl(p_folder_ext_rec.display_trip_info,'Y'), -- By default is visible
124 nvl(p_folder_ext_rec.creation_date,SYSDATE),
125 nvl(p_folder_ext_rec.created_by, FND_GLOBAL.USER_ID),
126 nvl(p_folder_ext_rec.last_update_date, SYSDATE),
127 nvl(p_folder_ext_rec.last_updated_by, FND_GLOBAL.USER_ID),
128 nvl(p_folder_ext_rec.last_update_login, FND_GLOBAL.LOGIN_ID)
129 );
130 END IF;
131
132 COMMIT;
133
134 IF l_debug_on THEN
135 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
136 WSH_DEBUG_SV.pop(l_module_name);
137 END IF;
138
139 EXCEPTION
140
141 WHEN others THEN
142 wsh_util_core.default_handler('WSH_FOLDER_EXTENSIONS_PVT.INSERT_UPDATE_FOLDER_EXT',l_module_name);
143 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
144 --
145 IF l_debug_on THEN
146 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
147 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
148 END IF;
149 --
150
151 END Insert_Update_Folder_Ext;
152
153
154 Procedure Delete_Folder_Ext(
155 p_folder_id IN NUMBER,
156 x_return_status OUT NOCOPY VARCHAR2)
157 IS PRAGMA AUTONOMOUS_TRANSACTION;
158 --
159 l_flag VARCHAR2(1) := 'N';
160 l_debug_on BOOLEAN;
161 l_count NUMBER := 0;
162 --
163 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FOLDER_EXT';
164 --
165 BEGIN
166
167 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
168 --
169 IF l_debug_on IS NULL
170 THEN
171 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
172 END IF;
173 --
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.push(l_module_name);
176 WSH_DEBUG_SV.log(l_module_name,'p_folder_id',p_folder_id);
177 --
178 END IF;
179 --
180 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
181
182 -- Only if record has been deleted from fnd_folders, delete it
183 -- from WSH custom table
184
185 SELECT count(*)
186 INTO l_count
187 FROM fnd_folders
188 WHERE folder_id = p_folder_id;
189
190 IF l_count = 0 THEN
191 l_flag := 'N';
192 ELSE
193 l_flag := 'Y';
194 END IF;
195 IF l_debug_on THEN
196 WSH_DEBUG_SV.log(l_module_name,'Flag',l_flag);
197 END IF;
198
199 IF l_flag = 'N' THEN
200
201 DELETE FROM wsh_folder_extensions
202 WHERE folder_id = p_folder_id;
203
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.log(l_module_name,'Folders Deleted-',sql%rowcount);
206 END IF;
207
208 COMMIT;
209 ELSE
210 IF l_debug_on THEN
211 WSH_DEBUG_SV.logmsg(l_module_name,'No Folders Deleted');
212 END IF;
213
214 END IF;
215
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
218 WSH_DEBUG_SV.pop(l_module_name);
219 END IF;
220 --
221 EXCEPTION
222
223 WHEN others THEN
224 wsh_util_core.default_handler('WSH_FOLDER_EXTENSIONS_PVT.DELETE_FOLDER_EXT',l_module_name);
225 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
226 --
227 IF l_debug_on THEN
228 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
229 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
230 END IF;
231 --
232
233 END Delete_Folder_Ext;
234
235 /*-------------------------------------------
236 -- Folder Custom Actions Table Handler
237 --========= INSERT/UPDATE FOLDER CUSTOM ACTIONS==
238 -- There can be multiple records for a folder_id in table
239 -- wsh_folder_custom_actions, so Input needs to be table of records
240 -------------------------------------------*/
241
242 Procedure Insert_Update_Folder_Custom(
243 p_folder_cust_tab IN folder_cust_tab_type,
244 x_return_status OUT NOCOPY VARCHAR2)
245 IS PRAGMA AUTONOMOUS_TRANSACTION;
246
247 CURSOR c_cust_rec_cur(v_folder_id IN NUMBER) IS
248 SELECT folder_id
249 FROM wsh_folder_custom_actions
250 WHERE folder_id = v_folder_id
251 AND rownum = 1;
252 -- FOR UPDATE NOWAIT;
253
254 l_folder_id NUMBER;
255 l_flag VARCHAR2(1);
256 --
257 l_debug_on BOOLEAN;
258 --
259 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_UPDATE_FOLDER_CUSTOM';
260 --
261 l_return_status VARCHAR2(30);
262 BEGIN
263
264 -- initialize parameters
265 --
266 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
267 --
268 IF l_debug_on IS NULL
269 THEN
270 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
271 END IF;
272 --
273 IF l_debug_on THEN
274 WSH_DEBUG_SV.push(l_module_name);
275 WSH_DEBUG_SV.log(l_module_name,'COUNT of records', p_folder_cust_tab.count);
276 END IF;
277 --
278 -- Initialize the Local variables for return status
279 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
280 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
281
282 OPEN c_cust_rec_cur(p_folder_cust_tab(p_folder_cust_tab.FIRST).folder_id);
283 FETCH c_cust_rec_cur
284 INTO l_folder_id;
285 CLOSE c_cust_rec_cur;
286
287 IF l_folder_id IS NULL THEN
288 -- If record does not exist ,then insert new records
289 l_flag := 'I';
290 ELSE
291 -- If record exists ,then update records
292 l_flag := 'U';
293 END IF;
294
295
296 -- Records May exist in wsh_folder_custom_actions for user selected
297 -- buttons,but user can chose to display a totally different set
298 -- of buttons when doing Save-As,so delete existing records and then
299 -- create new for same folder id
300 IF l_flag = 'U' THEN
301 -- Delete all records and then Insert New Records
302 -- No need to check for records in fnd_folders
303 DELETE FROM wsh_folder_custom_actions
304 WHERE folder_id = l_folder_id;
305 END IF;
306
307 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
308 FOR i in 1..p_folder_cust_tab.count
309 LOOP
310 INSERT INTO wsh_folder_custom_actions
311 (ACTION_ID,
312 ACTION_NAME,
313 OBJECT,
314 USER_ENTERED_PROMPT,
315 USER_ID,
316 FOLDER_ID,
317 WIDTH,
318 ACCESS_KEY,
319 DISPLAY_AS_BUTTON_FLAG,
320 DEFAULT_PROMPT,
321 CREATION_DATE,
322 CREATED_BY,
323 LAST_UPDATE_DATE,
324 LAST_UPDATED_BY,
325 LAST_UPDATE_LOGIN
326 )
327 VALUES
328 (p_folder_cust_tab(i).action_id,
329 p_folder_cust_tab(i).action_name,
330 p_folder_cust_tab(i).object,
331 p_folder_cust_tab(i).user_entered_prompt,
332 nvl(p_folder_cust_tab(i).user_id,FND_GLOBAL.USER_ID),
333 p_folder_cust_tab(i).folder_id,
334 p_folder_cust_tab(i).width,
335 p_folder_cust_tab(i).access_key,
336 p_folder_cust_tab(i).display_as_button_flag,
337 p_folder_cust_tab(i).default_prompt,
338 nvl(p_folder_cust_tab(i).creation_date,SYSDATE),
339 nvl(p_folder_cust_tab(i).created_by, FND_GLOBAL.USER_ID),
340 nvl(p_folder_cust_tab(i).last_update_date, SYSDATE),
341 nvl(p_folder_cust_tab(i).last_updated_by, FND_GLOBAL.USER_ID),
342 nvl(p_folder_cust_tab(i).last_update_login, FND_GLOBAL.LOGIN_ID)
343 );
344 END LOOP;
345
346 ELSE
347 x_return_status := l_return_status;
348 END IF;
349
350 COMMIT;
351
352 IF l_debug_on THEN
353 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
354 WSH_DEBUG_SV.pop(l_module_name);
355 END IF;
356
357 EXCEPTION
358
359 WHEN others THEN
360 wsh_util_core.default_handler('WSH_FOLDER_EXTENSIONS_PVT.INSERT_UPDATE_FOLDER_CUSTOM',l_module_name);
361 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
362 --
363 IF l_debug_on THEN
364 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
365 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
366 END IF;
367 --
368
369 END Insert_Update_Folder_Custom;
370
371 --========= DELETE FOLDER CUSTOM ACTIONS=================
372 -- There can be multiple records in wsh_folder_custom_actions
373 -- But they will be deleted by single delete and no need to loop
374 Procedure Delete_Folder_Custom(
375 p_folder_id IN NUMBER,
376 x_return_status OUT NOCOPY VARCHAR2)
377 IS PRAGMA AUTONOMOUS_TRANSACTION;
378 --
379 l_flag VARCHAR2(1) := 'N';
380 l_debug_on BOOLEAN;
381 l_count NUMBER := 0;
382 --
383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_FOLDER_CUSTOM';
384 --
385 BEGIN
386
387 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
388 --
389 IF l_debug_on IS NULL
390 THEN
391 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
392 END IF;
393 --
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.push(l_module_name);
396 WSH_DEBUG_SV.log(l_module_name,'p_folder_id',p_folder_id);
397 --
398 END IF;
399 --
400 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
401
402 DELETE FROM wsh_folder_custom_actions
403 WHERE folder_id = p_folder_id;
404
405 IF sql%NOTFOUND THEN
406 IF l_debug_on THEN
407 WSH_DEBUG_SV.logmsg(l_module_name,'No custom action for Folder Deleted');
408 END IF;
409 END IF;
410
411 IF l_debug_on THEN
412 WSH_DEBUG_SV.log(l_module_name,'Folders Deleted-',sql%rowcount);
413 END IF;
414
415 COMMIT;
416
417 IF l_debug_on THEN
418 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
419 WSH_DEBUG_SV.pop(l_module_name);
420 END IF;
421 --
422 EXCEPTION
423
424 WHEN others THEN
425 wsh_util_core.default_handler('WSH_FOLDER_EXTENSIONS_PVT.DELETE_FOLDER_CUSTOM',l_module_name);
426 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
427 --
428 IF l_debug_on THEN
429 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
430 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
431 END IF;
432 --
433
434 END Delete_Folder_Custom;
435
436
437
438 END WSH_FOLDER_EXTENSIONS_PVT;