1 PACKAGE BODY PA_USER_ATTR_PVT AS
2 /* $Header: PAUATTVB.pls 115.3 2003/07/07 22:09:17 anlee noship $ */
3
4
5 -- API name : DELETE_USER_ATTRS_DATA
6 -- Type : Public
7 -- Pre-reqs : None.
8
9 PROCEDURE DELETE_USER_ATTRS_DATA
10 ( p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
11 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
12 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
13 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
14 ,p_debug_mode IN VARCHAR2 := 'N'
15 ,p_project_id IN NUMBER
16 ,p_proj_element_id IN NUMBER DEFAULT NULL
17 ,p_old_classification_id IN NUMBER
18 ,p_new_classification_id IN NUMBER DEFAULT NULL
19 ,p_classification_type IN VARCHAR2
20 ,x_return_status OUT NOCOPY VARCHAR2
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_return_status VARCHAR2(1);
26 l_error_msg_code VARCHAR2(250);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(250);
29 l_data VARCHAR2(250);
30 l_msg_index_out NUMBER;
31
32 l_attr_group_id NUMBER;
33 l_data_level VARCHAR2(30);
34 l_dummy VARCHAR2(1);
35 l_dummy2 VARCHAR2(1);
36 l_proj_element_id NUMBER;
37
38 CURSOR get_deleted_attr_grps1
39 IS
40 SELECT assocs.attr_group_id, assocs.data_level
41 FROM EGO_OBJ_AG_ASSOCS_B assocs,
42 FND_OBJECTS obj
43 WHERE assocs.classification_code = p_classification_type||':'||to_char(p_old_classification_id)
44 AND assocs.object_id = obj.object_id
45 AND obj.obj_name = 'PA_PROJECTS'
46 MINUS
47 SELECT assocs.attr_group_id, assocs.data_level
48 FROM EGO_OBJ_AG_ASSOCS_B assocs,
49 FND_OBJECTS obj
50 WHERE assocs.classification_code = p_classification_type||':'||to_char(p_new_classification_id)
51 AND assocs.object_id = obj.object_id
52 AND obj.obj_name = 'PA_PROJECTS';
53
54 CURSOR get_deleted_attr_grps2
55 IS
56 SELECT assocs.attr_group_id, assocs.data_level
57 FROM EGO_OBJ_AG_ASSOCS_B assocs,
58 FND_OBJECTS obj
59 WHERE assocs.classification_code = p_classification_type||':'||to_char(p_old_classification_id)
60 AND assocs.object_id = obj.object_id
61 AND obj.obj_name = 'PA_PROJECTS';
62
63 CURSOR exists_in_other_drivers(c_attr_group_id NUMBER)
64 IS
65 SELECT 'Y'
66 FROM DUAL
67 WHERE c_attr_group_id IN
68 (SELECT assocs.attr_group_id
69 FROM EGO_OBJ_AG_ASSOCS_B assocs,
70 PA_PROJECTS_ALL ppa,
71 PA_PROJECT_TYPES ppt,
72 FND_OBJECTS obj
73 WHERE ppa.project_id = p_project_id
74 AND ppa.project_type = ppt.project_type
75 AND assocs.classification_code = 'PROJECT_TYPE:'||to_char(ppt.project_type_id)
76 AND assocs.data_level = 'PROJECT_LEVEL'
77 AND assocs.object_id = obj.object_id
78 AND obj.obj_name = 'PA_PROJECTS'
79 UNION
80 SELECT DISTINCT assocs.attr_group_id
81 FROM EGO_OBJ_AG_ASSOCS_B assocs,
82 PA_PROJECT_CLASSES ppc,
83 PA_CLASS_CATEGORIES pcc,
84 FND_OBJECTS obj
85 WHERE ppc.project_id = p_project_id
86 AND ppc.class_category = pcc.class_category
87 AND assocs.classification_code = 'CLASS_CATEGORY:'||to_char(pcc.class_category_id)
88 AND assocs.data_level = 'PROJECT_LEVEL'
89 AND assocs.object_id = obj.object_id
90 AND obj.obj_name = 'PA_PROJECTS'
91 UNION
92 SELECT assocs.attr_group_id
93 FROM EGO_OBJ_AG_ASSOCS_B assocs,
94 PA_PROJECT_CLASSES ppc,
95 PA_CLASS_CODES pcc,
96 FND_OBJECTS obj
97 WHERE ppc.project_id = p_project_id
98 AND ppc.class_category = pcc.class_category
99 AND ppc.class_code = pcc.class_code
100 AND assocs.classification_code = 'CLASS_CODE:'||to_char(pcc.class_code_id)
101 AND assocs.data_level = 'PROJECT_LEVEL'
102 AND assocs.object_id = obj.object_id
103 AND obj.obj_name = 'PA_PROJECTS');
104
105 CURSOR exists_in_other_task_drivers(c_attr_group_id NUMBER, c_proj_element_id NUMBER)
106 IS
107 SELECT 'Y'
108 FROM DUAL
109 WHERE c_attr_group_id IN
110 (SELECT assocs.attr_group_id
111 FROM EGO_OBJ_AG_ASSOCS_B assocs,
112 PA_PROJECTS_ALL ppa,
113 PA_PROJECT_TYPES ppt,
114 FND_OBJECTS obj
115 WHERE ppa.project_id = p_project_id
116 AND ppa.project_type = ppt.project_type
117 AND assocs.classification_code = 'PROJECT_TYPE:'||to_char(ppt.project_type_id)
118 AND assocs.data_level = 'TASK_LEVEL'
119 AND assocs.object_id = obj.object_id
120 AND obj.obj_name = 'PA_PROJECTS'
121 UNION
122 SELECT DISTINCT assocs.attr_group_id
123 FROM EGO_OBJ_AG_ASSOCS_B assocs,
124 PA_PROJECT_CLASSES ppc,
125 PA_CLASS_CATEGORIES pcc,
126 FND_OBJECTS obj
127 WHERE ppc.project_id = p_project_id
128 AND ppc.class_category = pcc.class_category
129 AND assocs.classification_code = 'CLASS_CATEGORY:'||to_char(pcc.class_category_id)
130 AND assocs.data_level = 'TASK_LEVEL'
131 AND assocs.object_id = obj.object_id
132 AND obj.obj_name = 'PA_PROJECTS'
133 UNION
134 SELECT assocs.attr_group_id
135 FROM EGO_OBJ_AG_ASSOCS_B assocs,
136 PA_PROJECT_CLASSES ppc,
137 PA_CLASS_CODES pcc,
138 FND_OBJECTS obj
139 WHERE ppc.project_id = p_project_id
140 AND ppc.class_category = pcc.class_category
141 AND ppc.class_code = pcc.class_code
142 AND assocs.classification_code = 'CLASS_CODE:'||to_char(pcc.class_code_id)
143 AND assocs.data_level = 'TASK_LEVEL'
144 AND assocs.object_id = obj.object_id
145 AND obj.obj_name = 'PA_PROJECTS'
146 UNION
147 SELECT assocs.attr_group_id
148 FROM EGO_OBJ_AG_ASSOCS_B assocs,
149 PA_PROJ_ELEMENTS ppe,
150 FND_OBJECTS obj
151 WHERE ppe.project_id = p_project_id
152 AND ppe.proj_element_id = c_proj_element_id
153 AND assocs.classification_code = 'TASK_TYPE:'||to_char(ppe.type_id)
154 AND assocs.object_id = obj.object_id
155 AND obj.obj_name = 'PA_PROJECTS');
156
157 CURSOR get_proj_elements
158 IS
159 SELECT proj_element_id
160 FROM PA_PROJ_ELEMENTS
161 WHERE project_id = p_project_id;
162
163 BEGIN
164 if (p_debug_mode = 'Y') then
165 pa_debug.debug('PA_USER_ATTR_PVT.Delete_User_Attrs_Data BEGIN');
166 end if;
167
168 if p_commit = FND_API.G_TRUE then
169 savepoint delete_user_attrs_data_pvt;
170 end if;
171
172 if p_classification_type IN ('PROJECT_TYPE', 'CLASS_CATEGORY', 'CLASS_CODE') then
173 if p_new_classification_id is not NULL then
174 OPEN get_deleted_attr_grps1;
175 LOOP
176 FETCH get_deleted_attr_grps1 INTO l_attr_group_id, l_data_level;
177 EXIT WHEN get_deleted_attr_grps1%NOTFOUND;
178
179
180 if l_data_level = 'PROJECT_LEVEL' then
181 OPEN exists_in_other_drivers(l_attr_group_id);
182 FETCH exists_in_other_drivers INTO l_dummy;
183
184 if exists_in_other_drivers%NOTFOUND then
185 l_dummy := 'N';
186 if p_validate_only <> FND_API.G_TRUE then
187 DELETE FROM PA_PROJECTS_ERP_EXT_B
188 WHERE PROJECT_ID = p_project_id
189 AND PROJ_ELEMENT_ID is NULL
190 AND ATTR_GROUP_ID = l_attr_group_id;
191
192 DELETE FROM PA_PROJECTS_ERP_EXT_TL
193 WHERE PROJECT_ID = p_project_id
194 AND PROJ_ELEMENT_ID is NULL
195 AND ATTR_GROUP_ID = l_attr_group_id;
196 end if;
197 end if;
198
199 CLOSE exists_in_other_drivers;
200 end if;
201
202 if l_data_level = 'TASK_LEVEL' then
203 OPEN get_proj_elements;
204 LOOP
205 FETCH get_proj_elements INTO l_proj_element_id;
206 EXIT WHEN get_proj_elements%NOTFOUND;
207
208 OPEN exists_in_other_task_drivers(l_attr_group_id, l_proj_element_id);
209 FETCH exists_in_other_task_drivers INTO l_dummy2;
210
211 if exists_in_other_task_drivers%NOTFOUND then
212 if p_validate_only <> FND_API.G_TRUE then
213 DELETE FROM PA_PROJECTS_ERP_EXT_B
214 WHERE PROJECT_ID = p_project_id
215 AND PROJ_ELEMENT_ID = l_proj_element_id
216 AND ATTR_GROUP_ID = l_attr_group_id;
217
218 DELETE FROM PA_PROJECTS_ERP_EXT_TL
219 WHERE PROJECT_ID = p_project_id
220 AND PROJ_ELEMENT_ID = l_proj_element_id
221 AND ATTR_GROUP_ID = l_attr_group_id;
222 end if;
223 end if;
224
225 CLOSE exists_in_other_task_drivers;
226 END LOOP;
227 CLOSE get_proj_elements;
228 end if;
229
230 END LOOP;
231 CLOSE get_deleted_attr_grps1;
232 else
233 OPEN get_deleted_attr_grps2;
234 LOOP
235 FETCH get_deleted_attr_grps2 INTO l_attr_group_id, l_data_level;
236 EXIT WHEN get_deleted_attr_grps2%NOTFOUND;
237
238 if l_data_level = 'PROJECT_LEVEL' then
239 OPEN exists_in_other_drivers(l_attr_group_id);
240 FETCH exists_in_other_drivers INTO l_dummy;
241
242 if exists_in_other_drivers%NOTFOUND then
243 l_dummy := 'N';
244 if p_validate_only <> FND_API.G_TRUE then
245 DELETE FROM PA_PROJECTS_ERP_EXT_B
246 WHERE PROJECT_ID = p_project_id
247 AND PROJ_ELEMENT_ID is NULL
248 AND ATTR_GROUP_ID = l_attr_group_id;
249
250 DELETE FROM PA_PROJECTS_ERP_EXT_TL
251 WHERE PROJECT_ID = p_project_id
252 AND PROJ_ELEMENT_ID is NULL
253 AND ATTR_GROUP_ID = l_attr_group_id;
254 end if;
255 end if;
256
257 CLOSE exists_in_other_drivers;
258 end if;
259
260 if l_data_level = 'PA_TASKS' then
261 OPEN get_proj_elements;
262 LOOP
263 FETCH get_proj_elements INTO l_proj_element_id;
264 EXIT WHEN get_proj_elements%NOTFOUND;
265
266 OPEN exists_in_other_task_drivers(l_attr_group_id, l_proj_element_id);
267 FETCH exists_in_other_task_drivers INTO l_dummy2;
268
269 if exists_in_other_task_drivers%NOTFOUND then
270 if p_validate_only <> FND_API.G_TRUE then
271 DELETE FROM PA_PROJECTS_ERP_EXT_B
272 WHERE PROJECT_ID = p_project_id
273 AND PROJ_ELEMENT_ID = l_proj_element_id
274 AND ATTR_GROUP_ID = l_attr_group_id;
275
276 DELETE FROM PA_PROJECTS_ERP_EXT_TL
277 WHERE PROJECT_ID = p_project_id
278 AND PROJ_ELEMENT_ID = l_proj_element_id
279 AND ATTR_GROUP_ID = l_attr_group_id;
280 end if;
281 end if;
282
283 CLOSE exists_in_other_task_drivers;
284 END LOOP;
285 CLOSE get_proj_elements;
286 end if;
287
288 END LOOP;
289 CLOSE get_deleted_attr_grps2;
290 end if;
291
292 elsif p_classification_type = 'TASK_TYPE' then
293 OPEN get_deleted_attr_grps1;
294 LOOP
295 FETCH get_deleted_attr_grps1 INTO l_attr_group_id, l_data_level;
296 EXIT WHEN get_deleted_attr_grps1%NOTFOUND;
297
298 OPEN exists_in_other_task_drivers(l_attr_group_id, p_proj_element_id);
299 FETCH exists_in_other_task_drivers INTO l_dummy;
300
301 if exists_in_other_task_drivers%NOTFOUND then
302 if p_validate_only <> FND_API.G_TRUE then
303 DELETE FROM PA_PROJECTS_ERP_EXT_B
304 WHERE PROJECT_ID = p_project_id
305 AND PROJ_ELEMENT_ID = p_proj_element_id
306 AND ATTR_GROUP_ID = l_attr_group_id;
307
308 DELETE FROM PA_PROJECTS_ERP_EXT_TL
309 WHERE PROJECT_ID = p_project_id
310 AND PROJ_ELEMENT_ID = p_proj_element_id
311 AND ATTR_GROUP_ID = l_attr_group_id;
312 end if;
313 end if;
314
315 CLOSE exists_in_other_task_drivers;
316 END LOOP;
317
318 CLOSE get_deleted_attr_grps1;
319 end if;
320
321 x_return_status := FND_API.G_RET_STS_SUCCESS;
322
323 if p_commit = FND_API.G_TRUE then
324 commit work;
325 end if;
326
327 if (p_debug_mode = 'Y') then
328 pa_debug.debug('PA_USER_ATTR_PVT.Delete_User_Attrs_Data END');
329 end if;
330
331 EXCEPTION
332 when FND_API.G_EXC_ERROR then
333 if p_commit = FND_API.G_TRUE then
334 rollback to delete_user_attrs_data_pvt;
335 end if;
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 when FND_API.G_EXC_UNEXPECTED_ERROR then
338 if p_commit = FND_API.G_TRUE then
339 rollback to delete_user_attrs_data_pvt;
340 end if;
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
342 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PVT',
343 p_procedure_name => 'Delete_User_Attrs_Data',
344 p_error_text => SUBSTRB(SQLERRM,1,240));
345 when OTHERS then
346 if p_commit = FND_API.G_TRUE then
347 rollback to delete_user_attrs_data_pvt;
348 end if;
349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
350 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PVT',
351 p_procedure_name => 'Delete_User_Attrs_Data',
352 p_error_text => SUBSTRB(SQLERRM,1,240));
353 raise;
354 END DELETE_USER_ATTRS_DATA;
355
356
357 PROCEDURE DELETE_ALL_USER_ATTRS_DATA
358 ( p_api_version IN NUMBER := 1.0
359 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
360 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
361 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
362 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
363 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
364 ,p_debug_mode IN VARCHAR2 := 'N'
365 ,p_project_id IN NUMBER
366 ,p_proj_element_id IN NUMBER DEFAULT NULL
367 ,x_return_status OUT NOCOPY VARCHAR2
368 ,x_msg_count OUT NOCOPY NUMBER
369 ,x_msg_data OUT NOCOPY VARCHAR2
370 )
371 IS
372 l_return_status VARCHAR2(1);
373 l_error_msg_code VARCHAR2(250);
374 l_msg_count NUMBER;
375 l_msg_data VARCHAR2(250);
376 l_data VARCHAR2(250);
377 l_msg_index_out NUMBER;
378
379 BEGIN
380 if (p_debug_mode = 'Y') then
381 pa_debug.debug('PA_USER_ATTR_PVT.Delete_All_User_Attrs_Data BEGIN');
382 end if;
383
384 if p_commit = FND_API.G_TRUE then
385 savepoint delete_all_user_attrs_data_pvt;
386 end if;
387
388 if p_validate_only <> FND_API.G_TRUE then
389 if p_proj_element_id is NULL then
390 DELETE FROM PA_PROJECTS_ERP_EXT_B
391 WHERE PROJECT_ID = p_project_id;
392
393 DELETE FROM PA_PROJECTS_ERP_EXT_TL
394 WHERE PROJECT_ID = p_project_id;
395 else
396 DELETE FROM PA_PROJECTS_ERP_EXT_B
397 WHERE PROJECT_ID = p_project_id
398 AND PROJ_ELEMENT_ID = p_proj_element_id;
399
400 DELETE FROM PA_PROJECTS_ERP_EXT_TL
401 WHERE PROJECT_ID = p_project_id
402 AND PROJ_ELEMENT_ID = p_proj_element_id;
403 end if;
404 end if;
405
406 x_return_status := FND_API.G_RET_STS_SUCCESS;
407
408 if p_commit = FND_API.G_TRUE then
409 commit work;
410 end if;
411
412 if (p_debug_mode = 'Y') then
413 pa_debug.debug('PA_USER_ATTR_PVT.Delete_All_User_Attrs_Data END');
414 end if;
415
416 EXCEPTION
417 when FND_API.G_EXC_ERROR then
418 if p_commit = FND_API.G_TRUE then
419 rollback to delete_all_user_attrs_data_pvt;
420 end if;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 when FND_API.G_EXC_UNEXPECTED_ERROR then
423 if p_commit = FND_API.G_TRUE then
424 rollback to delete_all_user_attrs_data_pvt;
425 end if;
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
427 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PVT',
428 p_procedure_name => 'Delete_All_User_Attrs_Data',
429 p_error_text => SUBSTRB(SQLERRM,1,240));
430 when OTHERS then
431 if p_commit = FND_API.G_TRUE then
432 rollback to delete_all_user_attrs_data_pvt;
433 end if;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PVT',
436 p_procedure_name => 'Delete_All_User_Attrs_Data',
437 p_error_text => SUBSTRB(SQLERRM,1,240));
438 raise;
439 END DELETE_ALL_USER_ATTRS_DATA;
440
441 END PA_USER_ATTR_PVT;
[Home] [Help]