[Home] [Help]
PACKAGE BODY: APPS.PA_USER_ATTR_PUB
Source
4 -- Global constant
1 PACKAGE BODY PA_USER_ATTR_PUB AS
2 /* $Header: PAUATTPB.pls 120.1.12010000.2 2009/06/12 06:00:39 snizam ship $ */
3
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_USER_ATTR_PUB';
6 G_ATTR_GROUP_TYPE CONSTANT VARCHAR2(30) := 'PA_PROJ_ATTR_GROUP_TYPE';
7
8 -- API name : COPY_USER_ATTRS_DATA
9 -- Type : Public
10 -- Pre-reqs : None.
11 PROCEDURE COPY_USER_ATTRS_DATA
12 ( p_api_version IN NUMBER := 1.0
13 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
14 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
15 ,p_debug_mode IN VARCHAR2 := 'N'
16 ,p_object_id_from IN NUMBER
17 ,p_object_id_to IN NUMBER
18 ,p_object_type IN VARCHAR2
19 ,x_return_status OUT NOCOPY VARCHAR2
20 ,x_errorcode OUT NOCOPY NUMBER
21 ,x_msg_count OUT NOCOPY NUMBER
22 ,x_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_api_name CONSTANT VARCHAR(30) := 'Copy_User_Attrs_Data';
26 l_api_version CONSTANT NUMBER := 1.0;
27
28 l_orig_proj_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
29 l_new_proj_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
30 l_orig_task_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
31 l_new_task_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
32
33 CURSOR get_project_id(c_task_id NUMBER)
34 IS
35 SELECT project_id
36 FROM PA_PROJ_ELEMENTS
37 WHERE proj_element_id = c_task_id;
38
39 CURSOR get_proj_elements(c_project_id NUMBER)
40 IS
41 SELECT proj_element_id
42 FROM PA_PROJ_ELEMENTS
43 WHERE project_id = c_project_id;
44
45 CURSOR get_new_proj_element_id(c_old_proj_element_id NUMBER, c_old_project_id NUMBER, c_new_project_id NUMBER)
46 IS
47 SELECT ppe2.proj_element_id
48 FROM PA_PROJ_ELEMENTS ppe, PA_PROJ_ELEMENTS ppe2
49 WHERE ppe.project_id = c_old_project_id
50 AND ppe.proj_element_id = c_old_proj_element_id
51 AND ppe.element_number = ppe2.element_number
52 AND ppe2.project_id = c_new_project_id;
53
54 CURSOR get_categories(c_project_id NUMBER)
55 IS
56 SELECT DISTINCT limiting_value
57 FROM pa_project_copy_overrides
58 WHERE project_id = c_project_id
59 AND field_name = 'CLASSIFICATION';
60
61 CURSOR check_category_removed(c_category VARCHAR2, c_old_project_id NUMBER, c_new_project_id NUMBER)
62 IS
63 SELECT class_category
64 FROM PA_PROJECT_CLASSES
65 WHERE project_id = c_old_project_id
66 AND class_category = c_category
67 AND class_category NOT IN
68 (SELECT class_category
69 FROM PA_PROJECT_CLASSES
70 where project_id = c_new_project_id);
71
72 CURSOR get_codes(c_category VARCHAR2, c_old_project_id NUMBER, c_new_project_id NUMBER)
73 IS
74 SELECT class_code
75 FROM PA_PROJECT_CLASSES
76 WHERE project_id = c_old_project_id
77 AND class_category = c_category
78 MINUS
79 SELECT class_code
80 FROM PA_PROJECT_CLASSES
81 WHERE project_id = c_new_project_id
82 AND class_category = c_category;
83
84 CURSOR get_category_id(c_category VARCHAR2)
85 IS
86 SELECT class_category_id
87 FROM PA_CLASS_CATEGORIES
88 WHERE class_category = c_category;
89
90 CURSOR get_code_id(c_category VARCHAR2, c_code VARCHAR2)
91 IS
92 SELECT class_code_id
93 FROM PA_CLASS_CODES
94 WHERE class_category = c_category
95 AND class_code = c_code;
96
97
98 l_project_id_from NUMBER;
99 l_project_id_to NUMBER;
100 l_old_proj_element_id NUMBER;
101 l_new_proj_element_id NUMBER;
102 l_category VARCHAR2(30);
103 l_code VARCHAR2(30);
104 l_deleted_category VARCHAR2(30);
105 l_category_id NUMBER;
106 l_code_id NUMBER;
107
108 l_return_status VARCHAR2(1);
109 l_error_msg_code VARCHAR2(250);
110 l_msg_count NUMBER;
111 l_msg_data VARCHAR2(250);
112 l_data VARCHAR2(250);
113 l_msg_index_out NUMBER;
114 l_errorcode NUMBER;
115
116 BEGIN
117 pa_debug.init_err_stack('PA_USER_ATTR_PUB.Copy_User_Attrs_Data');
118
119 if (p_debug_mode = 'Y') then
120 pa_debug.debug('PA_USER_ATTR_PUB.Copy_User_Attrs_Data BEGIN');
121 end if;
122
123 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
124 raise FND_API.G_EXC_UNEXPECTED_ERROR;
125 end if;
126
127 if p_commit = FND_API.G_TRUE then
128 savepoint copy_user_attrs_data;
129 end if;
130
131 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
132 FND_MSG_PUB.initialize;
136
133 end if;
134
135 if p_object_type = 'PA_PROJECTS' then
137 l_orig_proj_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJECT_ID', p_object_id_from));
138 l_new_proj_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJECT_ID', p_object_id_to));
139
140 l_orig_task_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJ_ELEMENT_ID', NULL));
141 l_new_task_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJ_ELEMENT_ID', NULL));
142
143 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data (
144 p_api_version => 1.0
145 ,p_application_id => 275
146 ,p_object_name => 'PA_PROJECTS'
147 ,p_old_pk_col_value_pairs => l_orig_proj_pk_value_pairs
148 ,p_old_dtlevel_col_value_pairs => l_orig_task_pk_value_pairs
149 ,p_new_pk_col_value_pairs => l_new_proj_pk_value_pairs
150 ,p_new_dtlevel_col_value_pairs => l_new_task_pk_value_pairs
151 ,p_commit => FND_API.G_FALSE
152 ,x_return_status => l_return_status
153 ,x_errorcode => l_errorcode
154 ,x_msg_count => l_msg_count
155 ,x_msg_data => l_msg_data );
156
157 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
158 x_msg_count := FND_MSG_PUB.count_msg;
159 if x_msg_count = 1 then
160 pa_interface_utils_pub.get_messages
161 (p_encoded => FND_API.G_TRUE,
162 p_msg_index => 1,
163 p_msg_count => l_msg_count,
164 p_msg_data => l_msg_data,
165 p_data => l_data,
166 p_msg_index_out => l_msg_index_out);
167 x_msg_data := l_data;
168 end if;
169 raise FND_API.G_EXC_ERROR;
170 end if;
171
172 OPEN get_proj_elements(p_object_id_from);
173 LOOP
174 FETCH get_proj_elements INTO l_old_proj_element_id;
175 EXIT WHEN get_proj_elements%NOTFOUND;
176
177 OPEN get_new_proj_element_id(l_old_proj_element_id, p_object_id_from, p_object_id_to);
178 FETCH get_new_proj_element_id INTO l_new_proj_element_id;
179 if get_new_proj_element_id%FOUND THEN
180 l_orig_task_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJ_ELEMENT_ID', l_old_proj_element_id));
181 l_new_task_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('PROJ_ELEMENT_ID', l_new_proj_element_id));
182
183 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data (
184 p_api_version => 1.0
185 ,p_application_id => 275
186 ,p_object_name => 'PA_PROJECTS'
187 ,p_old_pk_col_value_pairs => l_orig_proj_pk_value_pairs
188 ,p_old_dtlevel_col_value_pairs => l_orig_task_pk_value_pairs
189 ,p_new_pk_col_value_pairs => l_new_proj_pk_value_pairs
190 ,p_new_dtlevel_col_value_pairs => l_new_task_pk_value_pairs
191 ,p_commit => FND_API.G_FALSE
192 ,x_return_status => l_return_status
193 ,x_errorcode => l_errorcode
194 ,x_msg_count => l_msg_count
195 ,x_msg_data => l_msg_data );
196
197 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
198 x_msg_count := FND_MSG_PUB.count_msg;
199 if x_msg_count = 1 then
200 pa_interface_utils_pub.get_messages
201 (p_encoded => FND_API.G_TRUE,
202 p_msg_index => 1,
203 p_msg_count => l_msg_count,
204 p_msg_data => l_msg_data,
205 p_data => l_data,
206 p_msg_index_out => l_msg_index_out);
207 x_msg_data := l_data;
208 end if;
209 raise FND_API.G_EXC_ERROR;
210 end if;
211
212 end if;
213 CLOSE get_new_proj_element_id;
214
215 END LOOP;
216 CLOSE get_proj_elements;
217
218 -- Get all of the class categories included as quick entry overrides
219 -- For each class category, find out which codes were in the source but are no longer
220 -- in the destination
221 OPEN get_categories(p_object_id_from);
222 LOOP
223 FETCH get_categories INTO l_category;
224 EXIT WHEN get_categories%NOTFOUND;
225
226 OPEN check_category_removed(l_category, p_object_id_from, p_object_id_to);
227 FETCH check_category_removed INTO l_deleted_category;
228 if check_category_removed%FOUND THEN
229 OPEN get_category_id(l_category);
230 FETCH get_category_id INTO l_category_id;
231 CLOSE get_category_id;
232
233 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
234 p_validate_only => FND_API.G_FALSE
235 ,p_project_id => p_object_id_to
236 ,p_old_classification_id => l_category_id
237 ,p_classification_type => 'CLASS_CATEGORY'
238 ,x_return_status => l_return_status
239 ,x_msg_count => l_msg_count
240 ,x_msg_data => l_msg_data );
241
242 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
243 x_msg_count := FND_MSG_PUB.count_msg;
244 if x_msg_count = 1 then
245 pa_interface_utils_pub.get_messages
246 (p_encoded => FND_API.G_TRUE,
247 p_msg_index => 1,
248 p_msg_count => l_msg_count,
249 p_msg_data => l_msg_data,
253 end if;
250 p_data => l_data,
251 p_msg_index_out => l_msg_index_out);
252 x_msg_data := l_data;
254 raise FND_API.G_EXC_ERROR;
255 end if;
256 end if;
257 CLOSE check_category_removed;
258
259 OPEN get_codes(l_category, p_object_id_from, p_object_id_to);
260 LOOP
261 FETCH get_codes INTO l_code;
262 EXIT WHEN get_codes%NOTFOUND;
263
264 OPEN get_code_id(l_category, l_code);
265 FETCH get_code_id INTO l_code_id;
266 CLOSE get_code_id;
267
268 PA_USER_ATTR_PUB.DELETE_USER_ATTRS_DATA (
269 p_validate_only => FND_API.G_FALSE
270 ,p_project_id => p_object_id_to
271 ,p_old_classification_id => l_code_id
272 ,p_classification_type => 'CLASS_CODE'
273 ,x_return_status => l_return_status
274 ,x_msg_count => l_msg_count
275 ,x_msg_data => l_msg_data );
276
277 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
278 x_msg_count := FND_MSG_PUB.count_msg;
279 if x_msg_count = 1 then
280 pa_interface_utils_pub.get_messages
281 (p_encoded => FND_API.G_TRUE,
282 p_msg_index => 1,
283 p_msg_count => l_msg_count,
284 p_msg_data => l_msg_data,
285 p_data => l_data,
286 p_msg_index_out => l_msg_index_out);
287 x_msg_data := l_data;
288 end if;
289 raise FND_API.G_EXC_ERROR;
290 end if;
291
292 END LOOP;
293 CLOSE get_codes;
294
295 END LOOP;
296 CLOSE get_categories;
297
298 end if;
299
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 if p_commit = FND_API.G_TRUE then
303 commit work;
304 end if;
305
306 if (p_debug_mode = 'Y') then
307 pa_debug.debug('PA_USER_ATTR_PUB.Copy_User_Attrs_Data END');
308 end if;
309
310 EXCEPTION
311 when FND_API.G_EXC_ERROR then
312 if p_commit = FND_API.G_TRUE then
313 rollback to copy_user_attrs_data;
314 end if;
315 x_errorcode := l_errorcode;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 when FND_API.G_EXC_UNEXPECTED_ERROR then
318 if p_commit = FND_API.G_TRUE then
319 rollback to copy_user_attrs_data;
320 end if;
321 x_errorcode := l_errorcode;
322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
323 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
324 p_procedure_name => 'Copy_User_Attrs_Data',
325 p_error_text => SUBSTRB(SQLERRM,1,240));
326 when OTHERS then
327 if p_commit = FND_API.G_TRUE then
328 rollback to copy_user_attrs_data;
329 end if;
330 x_errorcode := l_errorcode;
331 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
332 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
333 p_procedure_name => 'Copy_User_Attrs_Data',
334 p_error_text => SUBSTRB(SQLERRM,1,240));
335 raise;
336 END COPY_USER_ATTRS_DATA;
337
338
339 -- API name : DELETE_USER_ATTRS_DATA
340 -- Type : Public
341 -- Pre-reqs : None.
342
343 PROCEDURE DELETE_USER_ATTRS_DATA
344 ( p_api_version IN NUMBER := 1.0
345 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
346 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
347 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
348 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
349 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
350 ,p_debug_mode IN VARCHAR2 := 'N'
351 ,p_project_id IN NUMBER
352 ,p_proj_element_id IN NUMBER DEFAULT NULL
353 ,p_old_classification_id IN NUMBER
354 ,p_new_classification_id IN NUMBER DEFAULT NULL
355 ,p_classification_type IN VARCHAR2
356 ,x_return_status OUT NOCOPY VARCHAR2
357 ,x_msg_count OUT NOCOPY NUMBER
358 ,x_msg_data OUT NOCOPY VARCHAR2
359 )
360 IS
361 l_api_name CONSTANT VARCHAR(30) := 'Delete_User_Attrs_Data';
362 l_api_version CONSTANT NUMBER := 1.0;
363
364 l_return_status VARCHAR2(1);
365 l_error_msg_code VARCHAR2(250);
366 l_msg_count NUMBER;
367 l_msg_data VARCHAR2(250);
368 l_data VARCHAR2(250);
369 l_msg_index_out NUMBER;
370
371 BEGIN
372 pa_debug.init_err_stack('PA_USER_ATTR_PUB.Delete_User_Attrs_Data');
373
374 if (p_debug_mode = 'Y') then
375 pa_debug.debug('PA_USER_ATTR_PUB.Delete_User_Attrs_Data BEGIN');
376 end if;
377
378 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
379 raise FND_API.G_EXC_UNEXPECTED_ERROR;
380 end if;
381
382 if p_commit = FND_API.G_TRUE then
383 savepoint delete_user_attrs_data;
384 end if;
385
386 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
387 FND_MSG_PUB.initialize;
388 end if;
389
390 PA_USER_ATTR_PVT.DELETE_USER_ATTRS_DATA (
391 p_commit => FND_API.G_FALSE
395 ,p_debug_mode => p_debug_mode
392 ,p_validate_only => p_validate_only
393 ,p_validation_level => p_validation_level
394 ,p_calling_module => p_calling_module
396 ,p_project_id => p_project_id
397 ,p_proj_element_id => p_proj_element_id
398 ,p_old_classification_id => p_old_classification_id
399 ,p_new_classification_id => p_new_classification_id
400 ,p_classification_type => p_classification_type
401 ,x_return_status => l_return_status
402 ,x_msg_count => l_msg_count
403 ,x_msg_data => l_msg_data );
404
405 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
406 x_msg_count := FND_MSG_PUB.count_msg;
407 if x_msg_count = 1 then
408 pa_interface_utils_pub.get_messages
409 (p_encoded => FND_API.G_TRUE,
410 p_msg_index => 1,
411 p_msg_count => l_msg_count,
412 p_msg_data => l_msg_data,
413 p_data => l_data,
414 p_msg_index_out => l_msg_index_out);
415 x_msg_data := l_data;
416 end if;
417 raise FND_API.G_EXC_ERROR;
418 end if;
419
420 x_return_status := FND_API.G_RET_STS_SUCCESS;
421
422 if p_commit = FND_API.G_TRUE then
423 commit work;
424 end if;
425
426 if (p_debug_mode = 'Y') then
427 pa_debug.debug('PA_USER_ATTR_PUB.Delete_User_Attrs_Data END');
428 end if;
429
430 EXCEPTION
431 when FND_API.G_EXC_ERROR then
432 if p_commit = FND_API.G_TRUE then
433 rollback to delete_user_attrs_data;
434 end if;
435 x_return_status := FND_API.G_RET_STS_ERROR;
436 when FND_API.G_EXC_UNEXPECTED_ERROR then
437 if p_commit = FND_API.G_TRUE then
438 rollback to delete_user_attrs_data;
439 end if;
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
442 p_procedure_name => 'Delete_User_Attrs_Data',
443 p_error_text => SUBSTRB(SQLERRM,1,240));
444 when OTHERS then
445 if p_commit = FND_API.G_TRUE then
446 rollback to delete_user_attrs_data;
447 end if;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
450 p_procedure_name => 'Delete_User_Attrs_Data',
451 p_error_text => SUBSTRB(SQLERRM,1,240));
452 raise;
453 END DELETE_USER_ATTRS_DATA;
454
455
456
457 -- API name : CHECK_DELETE_ASSOC_OK
458 -- Type : Public
459 -- Pre-reqs : None.
460
461 PROCEDURE CHECK_DELETE_ASSOC_OK
462 ( p_api_version IN NUMBER := 1.0
463 ,p_association_id IN NUMBER
464 ,p_classification_code IN VARCHAR2
465 ,p_data_level IN VARCHAR2
466 ,p_attr_group_id IN NUMBER
467 ,p_application_id IN NUMBER
468 ,p_attr_group_type IN VARCHAR2
469 ,p_attr_group_name IN VARCHAR2
470 ,p_enabled_code IN VARCHAR2
471 ,x_ok_to_delete OUT NOCOPY VARCHAR2
472 ,x_return_status OUT NOCOPY VARCHAR2
473 ,x_errorcode OUT NOCOPY NUMBER
474 ,x_msg_count OUT NOCOPY NUMBER
475 ,x_msg_data OUT NOCOPY VARCHAR2
476 )
477 IS
478 l_api_name CONSTANT VARCHAR(30) := 'Check_Delete_Assoc_Ok';
479 l_api_version CONSTANT NUMBER := 1.0;
480
481 l_ok_to_delete VARCHAR2(250);
485 l_msg_data VARCHAR2(250);
482 l_return_status VARCHAR2(1);
483 l_errorcode NUMBER;
484 l_msg_count NUMBER;
486 l_data VARCHAR2(250);
487 l_msg_index_out NUMBER;
488
489 l_dummy VARCHAR2(1);
490 l_assoc_date DATE;
491 l_max_ext_date DATE;
492
493 CURSOR exists_in_ext_tbl
494 IS
495 SELECT 'Y'
496 FROM PA_PROJECTS_ERP_EXT_B
497 WHERE attr_group_id = p_attr_group_id;
498
499 CURSOR get_assoc_date
500 IS
501 SELECT creation_date
502 FROM EGO_OBJ_AG_ASSOCS_B
503 WHERE association_id = p_association_id;
504
505 CURSOR get_max_ext_date
506 IS
507 SELECT max(creation_date)
508 FROM PA_PROJECTS_ERP_EXT_B
509 WHERE attr_group_id = p_attr_group_id;
510
511 BEGIN
512
513 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
514 raise FND_API.G_EXC_UNEXPECTED_ERROR;
515 end if;
516
517 OPEN exists_in_ext_tbl;
518 FETCH exists_in_ext_tbl INTO l_dummy;
519
520 if exists_in_ext_tbl%NOTFOUND then
521 x_ok_to_delete := FND_API.G_TRUE;
522 else
523 OPEN get_assoc_date;
524 FETCH get_assoc_date INTO l_assoc_date;
525 CLOSE get_assoc_date;
526
527 OPEN get_max_ext_date;
528 FETCH get_max_ext_date INTO l_max_ext_date;
529 CLOSE get_max_ext_date;
530
531 if l_assoc_date > l_max_ext_date then
532 x_ok_to_delete := FND_API.G_TRUE;
533 else
534 x_ok_to_delete := FND_API.G_FALSE;
535 PA_UTILS.Add_Message( p_app_short_name => 'PA'
536 ,p_msg_name => 'PA_EXT_CANT_DEL_ASSOC');
537
538 end if;
539 end if;
540
541 l_msg_count := FND_MSG_PUB.count_msg;
542 if l_msg_count > 0 then
543 x_msg_count := l_msg_count;
544 if x_msg_count = 1 then
545 pa_interface_utils_pub.get_messages
546 (p_encoded => FND_API.G_TRUE,
547 p_msg_index => 1,
548 p_msg_count => l_msg_count,
549 p_msg_data => l_msg_data,
550 p_data => l_data,
551 p_msg_index_out => l_msg_index_out);
552 x_msg_data := l_data;
553 end if;
554 x_return_status := FND_API.G_RET_STS_ERROR;
555 else
556 x_return_status := FND_API.G_RET_STS_SUCCESS;
557 end if;
558
559 EXCEPTION
560 when OTHERS then
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
563 p_procedure_name => 'Check_Delete_Assoc_Ok',
564 p_error_text => SUBSTRB(SQLERRM,1,240));
565 x_ok_to_delete := FND_API.G_FALSE;
566 raise;
567 END CHECK_DELETE_ASSOC_OK;
568
569
570
571 -- API name : DELETE_ALL_USER_ATTRS_DATA
572 -- Type : Public
573 -- Pre-reqs : None.
574
575 PROCEDURE DELETE_ALL_USER_ATTRS_DATA
576 ( p_api_version IN NUMBER := 1.0
577 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
578 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
579 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
580 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
581 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
582 ,p_debug_mode IN VARCHAR2 := 'N'
583 ,p_project_id IN NUMBER
584 ,p_proj_element_id IN NUMBER DEFAULT NULL
585 ,x_return_status OUT NOCOPY VARCHAR2
586 ,x_msg_count OUT NOCOPY NUMBER
587 ,x_msg_data OUT NOCOPY VARCHAR2
588 )
589 IS
590 l_api_name CONSTANT VARCHAR(30) := 'Delete_All_User_Attrs_Data';
591 l_api_version CONSTANT NUMBER := 1.0;
592
593 l_return_status VARCHAR2(1);
594 l_errorcode NUMBER;
595 l_msg_count NUMBER;
596 l_msg_data VARCHAR2(250);
597 l_data VARCHAR2(250);
598 l_msg_index_out NUMBER;
599
600 BEGIN
601 pa_debug.init_err_stack('PA_USER_ATTR_PUB.Delete_All_User_Attrs_Data');
602
603 if (p_debug_mode = 'Y') then
604 pa_debug.debug('PA_USER_ATTR_PUB.Delete_All_User_Attrs_Data BEGIN');
605 end if;
606
607 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
608 raise FND_API.G_EXC_UNEXPECTED_ERROR;
609 end if;
610
611 if p_commit = FND_API.G_TRUE then
612 savepoint delete_all_user_attrs_data;
613 end if;
614
615 if FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) then
616 FND_MSG_PUB.initialize;
617 end if;
618
619 PA_USER_ATTR_PVT.DELETE_ALL_USER_ATTRS_DATA (
620 p_commit => FND_API.G_FALSE
621 ,p_validate_only => p_validate_only
622 ,p_validation_level => p_validation_level
623 ,p_calling_module => p_calling_module
624 ,p_debug_mode => p_debug_mode
625 ,p_project_id => p_project_id
626 ,p_proj_element_id => p_proj_element_id
627 ,x_return_status => l_return_status
628 ,x_msg_count => l_msg_count
629 ,x_msg_data => l_msg_data );
630
631 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
632 x_msg_count := FND_MSG_PUB.count_msg;
633 if x_msg_count = 1 then
634 pa_interface_utils_pub.get_messages
635 (p_encoded => FND_API.G_TRUE,
636 p_msg_index => 1,
637 p_msg_count => l_msg_count,
638 p_msg_data => l_msg_data,
639 p_data => l_data,
640 p_msg_index_out => l_msg_index_out);
641 x_msg_data := l_data;
642 end if;
643 raise FND_API.G_EXC_ERROR;
644 end if;
645
646 if p_commit = FND_API.G_TRUE then
647 commit work;
648 end if;
649
650 x_return_status := FND_API.G_RET_STS_SUCCESS;
651
652 if (p_debug_mode = 'Y') then
653 pa_debug.debug('PA_USER_ATTR_PUB.Delete_All_User_Attrs_Data END');
654 end if;
655
656 EXCEPTION
657 when FND_API.G_EXC_ERROR then
658 if p_commit = FND_API.G_TRUE then
659 rollback to delete_all_user_attrs_data;
660 end if;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 when FND_API.G_EXC_UNEXPECTED_ERROR then
663 if p_commit = FND_API.G_TRUE then
664 rollback to delete_all_user_attrs_data;
665 end if;
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
667 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
668 p_procedure_name => 'Delete_All_User_Attrs_Data',
669 p_error_text => SUBSTRB(SQLERRM,1,240));
670 when OTHERS then
671 if p_commit = FND_API.G_TRUE then
672 rollback to delete_all_user_attrs_data;
673 end if;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
676 p_procedure_name => 'Delete_All_User_Attrs_Data',
677 p_error_text => SUBSTRB(SQLERRM,1,240));
678 raise;
679 END DELETE_ALL_USER_ATTRS_DATA;
680
681
682 -- API name : Process_User_Attrs_Data
683 -- Type : Public
684 -- Pre-reqs : None.
685 -- Description : This API is a wrapper for the EGO API
686 -- EGO_USER_ATTRS_DATA_PUB.Process_User_Attr_Data
687 -- It performs the following operations:
688 -- 1. transpose data from the PA data structure
689 -- to a format that is understood by the EGO API
690 -- 2. Call the EGO api and return the results
691 PROCEDURE Process_User_Attrs_Data
692 ( p_api_version IN NUMBER := 1.0
693 , p_object_name IN VARCHAR2 := 'PA_PROJECTS'
694 , p_ext_attr_data_table IN PA_PROJECT_PUB.PA_EXT_ATTR_TABLE_TYPE
695 , p_project_id IN NUMBER := 0
696 , p_structure_type IN VARCHAR2 := 'FINANCIAL'
697 , p_entity_id IN NUMBER := NULL
698 , p_entity_index IN NUMBER := NULL
699 , p_entity_code IN VARCHAR2 := NULL
700 , p_debug_mode IN VARCHAR2 := 'N'
701 , p_debug_level IN NUMBER := 0
702 , p_init_error_handler IN VARCHAR2 := FND_API.G_FALSE
703 , p_write_to_concurrent_log IN VARCHAR2 := FND_API.G_FALSE
704 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
705 , p_log_errors IN VARCHAR2 := FND_API.G_FALSE
706 , p_commit IN VARCHAR2 := FND_API.G_FALSE
707 , x_failed_row_id_list OUT NOCOPY VARCHAR2
708 , x_return_status OUT NOCOPY VARCHAR2
709 , x_errorcode OUT NOCOPY NUMBER
710 , x_msg_count OUT NOCOPY NUMBER
711 , x_msg_data OUT NOCOPY VARCHAR2)
712 IS
713
714 l_api_name CONSTANT VARCHAR(30) := 'Process_User_Attrs_Data';
715 l_api_version CONSTANT NUMBER := 1.0;
716 i NUMBER;
717 attr_rec PA_PROJECT_PUB.PA_EXT_ATTR_ROW_TYPE;
718 p_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
719 p_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
720 p_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
721 p_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
722 l_prev_loop_row_identifier NUMBER;
723 l_at_start_of_row BOOLEAN;
724
725 l_failed_row_id_list VARCHAR2(32767);
726 l_return_status VARCHAR2(1);
727 l_errorcode NUMBER;
728 l_msg_count NUMBER;
729 l_msg_data VARCHAR2(1000);
730 l_data VARCHAR2(250);
731 l_msg_index_out NUMBER;
732 l_proj_elem_id NUMBER;
733
734 BEGIN
735 pa_debug.init_err_stack('PA_USER_ATTR_PUB.Process_User_Attrs_Data');
736
737 SAVEPOINT PROCESS_USER_ATTRS_DATA_PUB;
738
739 if (p_debug_mode = 'Y') then
740 pa_debug.debug('PA_USER_ATTR_PUB.Process_User_Attrs_Data BEGIN');
741 end if;
742
743 if not FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
744 raise FND_API.G_EXC_UNEXPECTED_ERROR;
745 end if;
746
747 i := p_ext_attr_data_table.first;
748
749 if (p_debug_mode = 'Y') then
750 pa_debug.debug('Project Id = ' || to_char(p_project_id));
751 end if;
752
753 ------------------------------------------------------------------
754 -- Initialization phase: --
755 -- 1. Build arrays for the Primary Key columns and the --
756 -- Classification Code columns --
757 -- 2. We also build Attr Row and Attr Data tables --
758 ------------------------------------------------------------------
759 p_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
760 EGO_COL_NAME_VALUE_PAIR_OBJ('PROJECT_ID', p_project_id)
761 );
762
766
763 p_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
764 p_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
765
767 ------------------------------------------------------------------
768 -- Loop through pl/sql table to build data structures which --
769 -- will be passed in as parameters to the PLM API --
770 ------------------------------------------------------------------
771 WHILE i IS NOT NULL LOOP
772 attr_rec := p_ext_attr_data_table(i);
773
774 -----------------------------------------------------
775 -- Figure out whether we're now starting a new row --
776 -----------------------------------------------------
777 l_at_start_of_row := (l_prev_loop_row_identifier IS NULL OR
778 l_prev_loop_row_identifier <> attr_rec.ROW_IDENTIFIER);
779
780 ---------------------------------------------------
781 -- Build an Attr Row Object for each logical row --
782 ---------------------------------------------------
783 IF (l_at_start_of_row) THEN
784
785 --------------------------------------------
786 -- Resolve PROJ_ELEMENT_ID, if necessary --
787 --------------------------------------------
788 l_proj_elem_id := NULL;
789 IF (attr_rec.PROJ_ELEMENT_ID IS NULL) THEN
790 IF (attr_rec.PROJ_ELEMENT_REFERENCE IS NOT NULL) THEN
791 PA_PROJECT_PVT.Convert_pm_taskref_to_id_all
792 ( p_pa_project_id => p_project_id
793 , p_structure_type => p_structure_type
794 , p_pm_task_reference => attr_rec.PROJ_ELEMENT_REFERENCE
798 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
795 , p_out_task_id => l_proj_elem_id
796 , p_return_status => l_return_status );
797
799 THEN
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR)
802 THEN
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805 END IF;
806 ELSE
807 l_proj_elem_id := attr_rec.PROJ_ELEMENT_ID;
808 END IF;
809
810 p_attributes_row_table.EXTEND();
811 p_attributes_row_table(p_attributes_row_table.LAST) := EGO_USER_ATTR_ROW_OBJ(
812 attr_rec.ROW_IDENTIFIER
813 ,attr_rec.ATTR_GROUP_ID
814 ,275
815 ,G_ATTR_GROUP_TYPE
816 ,attr_rec.ATTR_GROUP_INT_NAME
817 ,null
818 ,l_proj_elem_id
819 ,null
820 ,null
821 ,null
822 ,null
823 ,attr_rec.TRANSACTION_TYPE
824 );
825
826 IF (p_debug_mode = 'Y') then
827 pa_debug.debug('Build Row Object:');
828 pa_debug.debug('Row Identifier =' || attr_rec.ROW_IDENTIFIER);
829 pa_debug.debug('ATTR_GROUP_ID =' || attr_rec.ATTR_GROUP_ID);
830 pa_debug.debug('ATTR_GROUP_INT_NAME =' || attr_rec.ATTR_GROUP_INT_NAME);
831 pa_debug.debug('PROJ_ELEMENT_ID =' || l_proj_elem_id);
832 pa_debug.debug('TRANSACTION_TYPE =' || attr_rec.TRANSACTION_TYPE);
833 END IF;
834 END IF;
835
836 ---------------------------------------------------------------
837 -- Add an Attr Data object to the Attr Data table every time --
838 ---------------------------------------------------------------
839 p_attributes_data_table.EXTEND();
840 p_attributes_data_table(p_attributes_data_table.LAST) := EGO_USER_ATTR_DATA_OBJ(
841 attr_rec.ROW_IDENTIFIER
842 ,attr_rec.ATTR_INT_NAME
843 ,attr_rec.ATTR_VALUE_STR
844 ,attr_rec.ATTR_VALUE_NUM
845 ,attr_rec.ATTR_VALUE_DATE
846 ,attr_rec.ATTR_DISP_VALUE
847 ,null -- ATTR_UNIT_OF_MEASURE
848 ,attr_rec.USER_ROW_IDENTIFIER
849 );
850 IF (p_debug_mode = 'Y') then
851 pa_debug.debug('Build Data Object:');
852 pa_debug.debug('Row Identifier =' || attr_rec.ROW_IDENTIFIER);
853 pa_debug.debug('ATTR_INT_NAME =' || attr_rec.ATTR_INT_NAME);
854 pa_debug.debug('ATTR_VALUE_STR =' || attr_rec.ATTR_VALUE_STR);
855 pa_debug.debug('ATTR_VALUE_NUM =' || attr_rec.ATTR_VALUE_NUM);
856 pa_debug.debug('ATTR_VALUE_DATE =' || attr_rec.ATTR_VALUE_DATE);
857 pa_debug.debug('ATTR_DISP_VALUE =' || attr_rec.ATTR_DISP_VALUE);
858 END IF;
859 ------------------------------------------------------
860 -- Update these variables for the next loop through --
861 ------------------------------------------------------
862 l_prev_loop_row_identifier := attr_rec.ROW_IDENTIFIER;
863 i := p_ext_attr_data_table.next(i);
864
865 END LOOP;
866
867 -------------------------------------------------------------------------
868 -- Since we are done looping and constructing the pl/sql tables,we are --
869 -- ready to process the data we've collected for this project instance --
870 -------------------------------------------------------------------------
871 if (p_debug_mode = 'Y') then
872 pa_debug.debug('>> EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data');
873 end if;
874 p_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(); --Bug 7688888
875 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data
876 (
877 p_api_version => 1.0
878 ,p_object_name => p_object_name
879 ,p_attributes_row_table => p_attributes_row_table
880 ,p_attributes_data_table => p_attributes_data_table
881 ,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
882 ,p_add_errors_to_fnd_stack => FND_API.G_TRUE
883 ,p_class_code_name_value_pairs => p_class_code_name_value_pairs --Bug 7688888
884 ,p_entity_id => p_entity_id
885 ,p_entity_index => p_entity_index
886 ,p_entity_code => p_entity_code
887 ,p_debug_level => p_debug_level
888 ,p_commit => p_commit
889 ,p_log_errors => FND_API.G_TRUE
890 ,x_failed_row_id_list => l_failed_row_id_list
891 ,x_return_status => l_return_status
892 ,x_errorcode => l_errorcode
893 ,x_msg_count => l_msg_count
897 pa_debug.debug('>> EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data');
894 ,x_msg_data => l_msg_data
895 );
896 if (p_debug_mode = 'Y') then
898 pa_debug.debug('Return Status = ' || l_return_status);
899 pa_debug.debug('Message Count = ' || l_msg_count);
900 end if;
901
902 -- process error status/messages
903 x_failed_row_id_list := l_failed_row_id_list;
904 x_return_status := l_return_status;
905 x_errorcode := l_errorcode;
906
907 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
908 x_msg_count := FND_MSG_PUB.count_msg;
909 if x_msg_count = 1 then
910 pa_interface_utils_pub.get_messages
911 (p_encoded => FND_API.G_TRUE,
912 p_msg_index => 1,
913 p_msg_count => l_msg_count,
914 p_msg_data => l_msg_data,
915 p_data => l_data,
916 p_msg_index_out => l_msg_index_out);
917 x_msg_data := l_data;
918 end if;
919 raise FND_API.G_EXC_ERROR;
920 end if;
921
922 if p_commit = FND_API.G_TRUE then
923 commit work;
924 end if;
925
926 x_return_status := FND_API.G_RET_STS_SUCCESS;
927
928 EXCEPTION
929 when FND_API.G_EXC_ERROR then
930 if p_commit = FND_API.G_TRUE then
931 rollback to PROCESS_USER_ATTRS_DATA_PUB;
932 end if;
933 x_return_status := FND_API.G_RET_STS_ERROR;
934 when FND_API.G_EXC_UNEXPECTED_ERROR then
935 if p_commit = FND_API.G_TRUE then
936 rollback to PROCESS_USER_ATTRS_DATA_PUB;
937 end if;
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
940 p_procedure_name => 'PROCESS_USER_ATTRS_DATA',
941 p_error_text => SUBSTRB(SQLERRM,1,240));
942 when OTHERS then
943 if p_commit = FND_API.G_TRUE then
944 rollback to PROCESS_USER_ATTRS_DATA_PUB;
945 end if;
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
947 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_USER_ATTR_PUB',
948 p_procedure_name => 'PROCESS_USER_ATTRS_DATA',
949 p_error_text => SUBSTRB(SQLERRM,1,240));
950 raise;
951
952 END Process_User_Attrs_Data;
953
954
955 -- API name : Check_Class_Assoc_Exists
956 -- Type : Public
957 -- Pre-reqs : None.
958
959 PROCEDURE CHECK_CLASS_ASSOC_EXISTS
960 ( P_ROW_ID IN VARCHAR2
961 ,P_NEW_CLASS_CATEGORY IN VARCHAR2 DEFAULT NULL
962 ,P_NEW_CLASS_CODE IN VARCHAR2 DEFAULT NULL
963 ,P_MODE IN VARCHAR2
964 ,X_ASSOC_EXISTS OUT NOCOPY VARCHAR2
965 )
966 IS
967 CURSOR C1
968 IS
969 SELECT class_category, class_code
970 FROM PA_PROJECT_CLASSES
971 WHERE rowid = p_row_id;
972
973 l_class_category VARCHAR2(30);
974 l_class_category_id NUMBER;
975 l_new_class_category_id NUMBER;
976 l_class_code VARCHAR2(30);
977 l_class_code_id NUMBER;
978 l_new_class_code_id NUMBER;
979 l_check_cat_assoc VARCHAR2(1);
980 l_check_new_cat_assoc VARCHAR2(1);
981 l_check_code_assoc VARCHAR2(1);
982 l_check_new_code_assoc VARCHAR2(1);
983
984 CURSOR get_class_category_id(c_class_category VARCHAR2)
985 IS
986 SELECT class_category_id
987 FROM PA_CLASS_CATEGORIES
988 WHERE class_category = c_class_category;
989
990 CURSOR get_class_code_id(c_class_category VARCHAR2, c_class_code VARCHAR2)
991 IS
992 SELECT class_code_id
993 FROM PA_CLASS_CODES
994 WHERE class_category = c_class_category
995 AND class_code = c_class_code;
996
997 CURSOR check_cat_assoc(c_class_category_id NUMBER)
998 IS
999 SELECT 'Y'
1000 FROM DUAL
1001 WHERE EXISTS
1002 (SELECT classification_code
1003 FROM EGO_OBJ_AG_ASSOCS_B assocs,
1004 FND_OBJECTS obj
1005 WHERE assocs.classification_code = 'CLASS_CATEGORY:'||to_char(c_class_category_id)
1006 AND assocs.object_id = obj.object_id
1007 AND obj.obj_name = 'PA_PROJECTS');
1008
1009 CURSOR check_code_assoc(c_class_code_id NUMBER)
1010 IS
1011 SELECT 'Y'
1012 FROM DUAL
1013 WHERE EXISTS
1014 (SELECT classification_code
1015 FROM EGO_OBJ_AG_ASSOCS_B assocs,
1016 FND_OBJECTS obj
1017 WHERE assocs.classification_code = 'CLASS_CODE:'||to_char(c_class_code_id)
1018 AND assocs.object_id = obj.object_id
1019 AND obj.obj_name = 'PA_PROJECTS');
1020
1021 BEGIN
1022
1023 x_assoc_exists := 'N';
1024
1025 if p_mode = 'DELETE' then
1026 OPEN C1;
1027 FETCH C1 INTO l_class_category, l_class_code;
1028 CLOSE C1;
1029
1030 OPEN get_class_category_id(l_class_category);
1031 FETCH get_class_category_id INTO l_class_category_id;
1032 CLOSE get_class_category_id;
1033
1034 OPEN get_class_code_id(l_class_category, l_class_code);
1035 FETCH get_class_code_id INTO l_class_code_id;
1036 CLOSE get_class_code_id;
1037
1038 OPEN check_cat_assoc(l_class_category_id);
1039 FETCH check_cat_assoc INTO l_check_cat_assoc;
1040 if check_cat_assoc%FOUND then
1041 x_assoc_exists := 'Y';
1042 CLOSE check_cat_assoc;
1043 return;
1044 end if;
1045
1046 OPEN check_code_assoc(l_class_code_id);
1047 FETCH check_code_assoc INTO l_check_code_assoc;
1051 return;
1048 if check_code_assoc%FOUND then
1049 x_assoc_exists := 'Y';
1050 CLOSE check_code_assoc;
1052 end if;
1053
1054 elsif p_mode = 'UPDATE' then
1055 OPEN C1;
1056 FETCH C1 INTO l_class_category, l_class_code;
1057 CLOSE C1;
1058
1059 if (l_class_category = p_new_class_category) AND (l_class_code = p_new_class_code) then
1060 return;
1061 else
1062 OPEN get_class_category_id(l_class_category);
1063 FETCH get_class_category_id INTO l_class_category_id;
1064 CLOSE get_class_category_id;
1065
1066 OPEN get_class_category_id(p_new_class_category);
1067 FETCH get_class_category_id INTO l_new_class_category_id;
1068 CLOSE get_class_category_id;
1069
1070 OPEN get_class_code_id(l_class_category, l_class_code);
1071 FETCH get_class_code_id INTO l_class_code_id;
1072 CLOSE get_class_code_id;
1073
1074 OPEN get_class_code_id(p_new_class_category, p_new_class_code);
1075 FETCH get_class_code_id INTO l_new_class_code_id;
1076 CLOSE get_class_code_id;
1077
1078 OPEN check_cat_assoc(l_class_category_id);
1079 FETCH check_cat_assoc INTO l_check_cat_assoc;
1080 if check_cat_assoc%FOUND then
1081 x_assoc_exists := 'Y';
1082 CLOSE check_cat_assoc;
1083 return;
1084 end if;
1085 CLOSE check_cat_assoc;
1086
1087 OPEN check_cat_assoc(l_new_class_category_id);
1088 FETCH check_cat_assoc INTO l_check_new_cat_assoc;
1089 if check_cat_assoc%FOUND then
1090 x_assoc_exists := 'Y';
1091 CLOSE check_cat_assoc;
1092 return;
1093 end if;
1094 CLOSE check_cat_assoc;
1095
1096 OPEN check_code_assoc(l_class_code_id);
1097 FETCH check_code_assoc INTO l_check_code_assoc;
1098 if check_code_assoc%FOUND then
1099 x_assoc_exists := 'Y';
1100 CLOSE check_code_assoc;
1101 return;
1102 end if;
1103 CLOSE check_code_assoc;
1104
1105 OPEN check_code_assoc(l_new_class_code_id);
1106 FETCH check_code_assoc INTO l_check_new_code_assoc;
1107 if check_code_assoc%FOUND then
1108 x_assoc_exists := 'Y';
1109 CLOSE check_code_assoc;
1110 return;
1111 end if;
1112 CLOSE check_code_assoc;
1113 end if;
1114
1115 end if;
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 x_assoc_exists := 'N';
1120
1121 END CHECK_CLASS_ASSOC_EXISTS;
1122
1123
1124 -- API name : Check_PT_Assoc_Exists
1125 -- Type : Public
1126 -- Pre-reqs : None.
1127
1128 PROCEDURE CHECK_PT_ASSOC_EXISTS
1129 ( P_PROJECT_ID IN NUMBER
1130 ,P_NEW_PROJECT_TYPE IN VARCHAR2
1131 ,X_ASSOC_EXISTS OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134
1135 CURSOR check_pt_assoc(c_project_type_id NUMBER)
1136 IS
1137 SELECT 'Y'
1138 FROM DUAL
1139 WHERE EXISTS
1140 (SELECT classification_code
1141 FROM EGO_OBJ_AG_ASSOCS_B assocs,
1142 FND_OBJECTS obj
1143 WHERE assocs.classification_code = 'PROJECT_TYPE:'||to_char(c_project_type_id)
1144 AND assocs.object_id = obj.object_id
1145 AND obj.obj_name = 'PA_PROJECTS');
1146
1147 CURSOR get_old_project_type_id
1148 IS
1149 SELECT PTT.project_type_id
1150 FROM pa_project_types PTT, pa_projects_all PPA
1151 WHERE PPA.project_id = p_project_id
1152 AND PPA.project_type = PTT.project_type;
1153
1154 CURSOR get_new_project_type_id
1155 IS
1156 SELECT project_type_id
1157 FROM pa_project_types
1158 WHERE project_type = p_new_project_type;
1159
1160
1161 l_old_project_type_id NUMBER;
1162 l_new_project_type_id NUMBER;
1163 l_check_old_pt_assoc VARCHAR2(1);
1164 l_check_new_pt_assoc VARCHAR2(1);
1165
1166 BEGIN
1167 x_assoc_exists := 'N';
1168
1169 OPEN get_old_project_type_id;
1170 FETCH get_old_project_type_id INTO l_old_project_type_id;
1171 CLOSE get_old_project_type_id;
1172
1173 OPEN get_new_project_type_id;
1174 FETCH get_new_project_type_id INTO l_new_project_type_id;
1175 CLOSE get_new_project_type_id;
1176
1177 if l_old_project_type_id = l_new_project_type_id then
1178 return;
1179 else
1180 OPEN check_pt_assoc(l_old_project_type_id);
1181 FETCH check_pt_assoc INTO l_check_old_pt_assoc;
1182 if check_pt_assoc%FOUND then
1183 x_assoc_exists := 'Y';
1184 CLOSE check_pt_assoc;
1185 return;
1186 end if;
1187 CLOSE check_pt_assoc;
1188
1189 OPEN check_pt_assoc(l_new_project_type_id);
1190 FETCH check_pt_assoc INTO l_check_new_pt_assoc;
1191 if check_pt_assoc%FOUND then
1192 x_assoc_exists := 'Y';
1193 CLOSE check_pt_assoc;
1194 return;
1195 end if;
1196 CLOSE check_pt_assoc;
1197
1198 end if;
1199
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 x_assoc_exists := 'N';
1203
1204 END CHECK_PT_ASSOC_EXISTS;
1205
1206
1207 END PA_USER_ATTR_PUB;