[Home] [Help]
PACKAGE BODY: APPS.PA_RELATIONSHIP_PUB
Source
1 package body PA_RELATIONSHIP_PUB as
2 /*$Header: PAXRELPB.pls 120.6.12010000.5 2009/06/15 13:50:14 kmaddi ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PA_RELATIONSHIP_PUB';
5
6 -- API name : Create_Relationship
7 -- Type : Public Procedure
8 -- Pre-reqs : None
9 -- Return Value : N/A
10 -- Parameters
11 -- p_api_version IN NUMBER := 1.0
12 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
13 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
14 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
15 -- p_validation_level IN VARCHAR2 := 100
16 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
17 -- p_debug_mode IN VARCHAR2 := 'N'
18 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
19 -- p_project_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
20 -- p_project_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
21 -- p_structure_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
22 -- p_structure_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
23 -- p_structure_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
24 -- p_structure_version_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
25 -- p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
26 -- p_task_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
27 -- p_project_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
28 -- p_project_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
29 -- p_structure_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
30 -- p_structure_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
31 -- p_structure_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
32 -- p_structure_version_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
33 -- p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
34 -- p_task_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
35 -- p_structure_type IN VARCHAR2
36 -- p_initiating_element IN VARCHAR2
37 -- p_link_to_latest_structure_ver IN VARCHAR2 := 'N'
38 -- p_relationship_type IN VARCHAR2
39 -- p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
40 -- p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
41 -- p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
42 -- x_object_relationship_id OUT NUMBER
43 -- x_return_status OUT VARCHAR2
44 -- x_msg_count OUT NUMBER
45 -- x_msg_data OUT VARCHAR2
46 --
47 -- History
48 --
49 -- 25-JUN-01 HSIU -Created
50 --
51 --
52
53
54 procedure Create_Relationship
55 (
56 p_api_version IN NUMBER := 1.0
57 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
58 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
59 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
60 ,p_validation_level IN VARCHAR2 := 100
61 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
62 ,p_debug_mode IN VARCHAR2 := 'N'
63 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
64 ,p_project_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
65 ,p_project_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
66 ,p_structure_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
67 ,p_structure_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
68 ,p_structure_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
69 ,p_structure_version_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
70 ,p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
71 ,p_task_name_from IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
72 ,p_project_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
73 ,p_project_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
74 ,p_structure_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
75 ,p_structure_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
76 ,p_structure_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
77 ,p_structure_version_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
78 ,p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
79 ,p_task_name_to IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
80 ,p_structure_type IN VARCHAR2
81 ,p_initiating_element IN VARCHAR2
82 ,p_link_to_latest_structure_ver IN VARCHAR2 := 'N'
83 ,p_relationship_type IN VARCHAR2
84 ,p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
85 ,p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
86 ,p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
87 ,p_weighting_percentage IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
88 ,x_object_relationship_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
89 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
90 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
91 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
92 )
93 IS
94 l_api_name CONSTANT VARCHAR(30) := 'CREATE_RELATIONSHIP';
95 l_api_version CONSTANT NUMBER := 1.0;
96
97 l_return_status VARCHAR2(1);
98 l_msg_count NUMBER;
99 l_msg_data VARCHAR2(250);
100 l_data VARCHAR2(250);
101 l_msg_index_out NUMBER;
102 l_error_msg_code VARCHAR2(250);
103
104 l_object_relationship_id NUMBER;
105
106 l_project_id_from NUMBER;
107 l_structure_id_from NUMBER;
108 l_struc_ver_id_from NUMBER;
109 l_task_ver_id_from NUMBER;
110 l_project_id_to NUMBER;
111 l_structure_id_to NUMBER;
112 l_struc_ver_id_to NUMBER;
113 l_task_ver_id_to NUMBER;
114
115 BEGIN
116 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.CREATE_RELATIONSHIP');
117
118 IF (p_debug_mode = 'Y') THEN
119 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_RELATIONSHIP begin');
120 END IF;
121
122 IF (p_commit = FND_API.G_TRUE) THEN
123 savepoint create_relationship;
124 END IF;
125
126 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
127 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
128 END IF;
129
130 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
131 FND_MSG_PUB.initialize;
132 END IF;
133
134
135 --name to id conversion
136 IF ((p_project_name_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
137 (p_project_name_from IS NOT NULL)) OR
138 ((p_project_id_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
139 (p_project_id_from IS NOT NULL)) THEN
140 PA_PROJ_ELEMENTS_UTILS.Project_Name_Or_Id(
141 p_project_name => p_project_name_from
142 ,p_project_id => p_project_id_from
143 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
144 ,x_project_id => l_project_id_from
145 ,x_return_status => l_return_status
146 ,x_error_msg_code => l_error_msg_code
147 );
148 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
149 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
150 END IF;
151 END IF;
152
153
154 IF ((p_project_name_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
155 (p_project_name_to IS NOT NULL)) OR
156 ((p_project_id_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
157 (p_project_id_to IS NOT NULL)) THEN
158 PA_PROJ_ELEMENTS_UTILS.Project_Name_Or_Id(
159 p_project_name => p_project_name_to
160 ,p_project_id => p_project_id_to
161 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
162 ,x_project_id => l_project_id_to
163 ,x_return_status => l_return_status
164 ,x_error_msg_code => l_error_msg_code
165 );
166 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
167 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
168 END IF;
169 END IF;
170
171 IF ((p_structure_name_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
172 (p_structure_name_from IS NOT NULL)) OR
173 ((p_structure_id_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
174 (p_structure_id_from IS NOT NULL)) THEN
175 PA_PROJECT_STRUCTURE_UTILS.Structure_Name_Or_Id(
176 p_project_id => l_project_id_from
177 ,p_structure_id => p_structure_id_from
178 ,p_structure_name => p_structure_name_from
179 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
180 ,x_structure_id => l_structure_id_from
181 ,x_return_status => l_return_status
182 ,x_error_message_code => l_error_msg_code
183 );
184 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
186 END IF;
187 END IF;
188
189 IF ((p_structure_name_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
190 (p_structure_name_to IS NOT NULL)) OR
191 ((p_structure_id_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
192 (p_structure_id_to IS NOT NULL)) THEN
193 PA_PROJECT_STRUCTURE_UTILS.Structure_Name_Or_Id(
194 p_project_id => l_project_id_to
195 ,p_structure_id => p_structure_id_to
196 ,p_structure_name => p_structure_name_to
197 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
198 ,x_structure_id => l_structure_id_to
199 ,x_return_status => l_return_status
200 ,x_error_message_code => l_error_msg_code
201 );
202 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
203 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
204 END IF;
205 END IF;
206
207 IF ((p_structure_version_name_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
208 (p_structure_version_name_from IS NOT NULL)) OR
209 ((p_structure_version_id_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
210 (p_structure_version_id_from IS NOT NULL)) THEN
211 -- error_msg('name -> '||p_structure_version_name_from);
212 -- error_msg('id -> '||p_structure_version_id_from);
213 PA_PROJECT_STRUCTURE_UTILS.Structure_Version_Name_Or_Id
214 (
215 p_structure_id => l_structure_id_from
216 ,p_structure_version_name => p_structure_version_name_from
217 ,p_structure_version_id => p_structure_version_id_from
218 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
219 ,x_structure_version_id => l_struc_ver_id_from
220 ,x_return_status => l_return_status
221 ,x_error_message_code => l_error_msg_code
222 );
223 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
224 -- error_msg('structure version from ');
225 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
226 END IF;
227 END IF;
228
229 IF ((p_structure_version_name_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
230 (p_structure_version_name_to IS NOT NULL)) OR
231 ((p_structure_version_id_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
232 (p_structure_version_id_to IS NOT NULL)) THEN
233 PA_PROJECT_STRUCTURE_UTILS.Structure_Version_Name_Or_Id
234 (
235 p_structure_id => l_structure_id_to
236 ,p_structure_version_name => p_structure_version_name_to
237 ,p_structure_version_id => p_structure_version_id_to
238 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
239 ,x_structure_version_id => l_struc_ver_id_to
240 ,x_return_status => l_return_status
241 ,x_error_message_code => l_error_msg_code
242 );
243 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
244 -- error_msg('structure version to ');
245 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
246 END IF;
247 END IF;
248
249 IF ((p_task_name_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
250 (p_task_name_from IS NOT NULL)) OR
251 ((p_task_version_id_from <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
252 (p_task_version_id_from IS NOT NULL)) THEN
253 PA_PROJ_ELEMENTS_UTILS.TASK_VER_NAME_OR_ID(
254 p_task_name => p_task_name_from
255 ,p_task_version_id => p_task_version_id_from
256 ,p_structure_version_id => l_struc_ver_id_from
257 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
258 ,x_task_version_id => l_task_ver_id_from
259 ,x_return_status => l_return_status
260 ,x_error_msg_code => l_error_msg_code
261 );
262
263 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
264 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
265 END IF;
266 END IF;
267
268 IF ((p_task_name_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
269 (p_task_name_to IS NOT NULL)) OR
270 ((p_task_version_id_to <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
271 (p_task_version_id_to IS NOT NULL)) THEN
272 PA_PROJ_ELEMENTS_UTILS.TASK_VER_NAME_OR_ID(
273 p_task_name => p_task_name_to
274 ,p_task_version_id => p_task_version_id_to
275 ,p_structure_version_id => l_struc_ver_id_to
276 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
277 ,x_task_version_id => l_task_ver_id_to
278 ,x_return_status => l_return_status
279 ,x_error_msg_code => l_error_msg_code
280 );
281
282 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
283 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
284 END IF;
285 END IF;
286
287 x_msg_count := FND_MSG_PUB.count_msg;
288 IF x_msg_count > 0 THEN
289 If x_msg_count > 1 then
290 pa_interface_utils_pub.get_messages
291 (p_encoded => FND_API.G_TRUE,
292 p_msg_index => 1,
293 p_msg_count => l_msg_count,
294 p_msg_data => l_msg_data,
295 p_data => l_data,
296 p_msg_index_out => l_msg_index_out);
297 x_msg_data := l_data;
298 END IF;
299 raise FND_API.G_EXC_ERROR;
300 END IF;
301
302
303 PA_RELATIONSHIP_PVT.Create_Relationship(
304 p_api_version => p_api_version
305 ,p_init_msg_list => p_init_msg_list
306 ,p_commit => p_commit
307 ,p_validate_only => p_validate_only
308 ,p_validation_level => p_validation_level
309 ,p_calling_module => p_calling_module
310 ,p_debug_mode => p_debug_mode
311 ,p_max_msg_count => p_max_msg_count
312 ,p_project_id_from => l_project_id_from
313 ,p_structure_id_from => l_structure_id_from
314 ,p_structure_version_id_from => l_struc_ver_id_from
315 ,p_task_version_id_from => l_task_ver_id_from
316 ,p_project_id_to => l_project_id_to
317 ,p_structure_id_to => l_structure_id_to
318 ,p_structure_version_id_to => l_struc_ver_id_to
319 ,p_task_version_id_to => l_task_ver_id_to
320 ,p_structure_type => p_structure_type
321 ,p_initiating_element => p_initiating_element
322 ,p_link_to_latest_structure_ver => p_link_to_latest_structure_ver
323 ,p_relationship_type => p_relationship_type
324 ,p_relationship_subtype => p_relationship_subtype
325 ,p_lag_day => p_lag_day
326 ,p_priority => p_priority
327 ,p_weighting_percentage => p_weighting_percentage
328 ,x_object_relationship_id => l_object_relationship_id
329 ,x_return_status => l_return_status
330 ,x_msg_count => l_msg_count
331 ,x_msg_data => l_msg_data
332 );
333
334 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
335 x_msg_count := FND_MSG_PUB.count_msg;
336 IF x_msg_count = 1 then
337 pa_interface_utils_pub.get_messages
338 (p_encoded => FND_API.G_TRUE,
339 p_msg_index => 1,
340 p_msg_count => l_msg_count,
341 p_msg_data => l_msg_data,
342 p_data => l_data,
343 p_msg_index_out => l_msg_index_out);
344 x_msg_data := l_data;
345 END IF;
346 raise FND_API.G_EXC_ERROR;
347 END IF;
348
349 x_object_relationship_id := l_object_relationship_id;
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 IF (p_commit = FND_API.G_TRUE) THEN
353 COMMIT;
354 END IF;
355
356 IF (p_debug_mode = 'Y') THEN
357 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_RELATIONSHIP END');
358 END IF;
359
360 EXCEPTION
361 when FND_API.G_EXC_ERROR then
362 if p_commit = FND_API.G_TRUE then
363 rollback to create_relationship;
364 end if;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 when FND_API.G_EXC_UNEXPECTED_ERROR then
367 if p_commit = FND_API.G_TRUE then
368 rollback to create_relationship;
369 end if;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
372 p_procedure_name => 'CREATE_RELATIONSHIP',
373 p_error_text => SUBSTRB(SQLERRM,1,240));
374 when OTHERS then
375 if p_commit = FND_API.G_TRUE then
376 rollback to create_relationship;
377 end if;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
380 p_procedure_name => 'CREATE_RELATIONSHIP',
381 p_error_text => SUBSTRB(SQLERRM,1,240));
382 raise;
383 END CREATE_RELATIONSHIP;
384
385
386
387 -- API name : Delete_Relationship
388 -- Type : Public Procedure
389 -- Pre-reqs : None
390 -- Return Value : N/A
391 -- Parameters
392 -- p_api_version IN NUMBER := 1.0
393 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
394 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
395 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
396 -- p_validation_level IN VARCHAR2 := 100
397 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
398 -- p_debug_mode IN VARCHAR2 := 'N'
399 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
400 -- p_object_relationship_id IN NUMBER
401 -- p_record_version_number IN NUMBER
402 -- x_return_status OUT VARCHAR2
403 -- x_msg_count OUT NUMBER
404 -- x_msg_data OUT VARCHAR2
405 --
406 -- History
407 --
408 -- 25-JUN-01 HSIU -Created
409 --
410 --
411
412
413 procedure Delete_Relationship
414 (
415 p_api_version IN NUMBER := 1.0
416 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
417 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
418 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
419 ,p_validation_level IN VARCHAR2 := 100
420 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
421 ,p_debug_mode IN VARCHAR2 := 'N'
422 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
423 ,p_object_relationship_id IN NUMBER
424 ,p_record_version_number IN NUMBER
425 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
426 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
427 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
428 )
429 IS
430 l_api_name CONSTANT VARCHAR(30) := 'DELETE_RELATIONSHIP';
431 l_api_version CONSTANT NUMBER := 1.0;
432
433 l_return_status VARCHAR2(1);
434 l_msg_count NUMBER;
435 l_msg_data VARCHAR2(250);
436 l_data VARCHAR2(250);
437 l_msg_index_out NUMBER;
438 l_error_msg_code VARCHAR2(250);
439
440 BEGIN
441 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP');
442
443 IF (p_debug_mode = 'Y') THEN
444 pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_RELATIONSHIP begin');
445 END IF;
446
447 IF (p_commit = FND_API.G_TRUE) THEN
448 savepoint delete_relationship;
449 END IF;
450
451 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453 END IF;
454
455 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
456 FND_MSG_PUB.initialize;
457 END IF;
458
459 PA_RELATIONSHIP_PVT.Delete_Relationship(
460 p_api_version => p_api_version
461 ,p_init_msg_list => p_init_msg_list
462 ,p_commit => p_commit
463 ,p_validate_only => p_validate_only
464 ,p_validation_level => p_validation_level
465 ,p_calling_module => p_calling_module
466 ,p_debug_mode => p_debug_mode
467 ,p_max_msg_count => p_max_msg_count
468 ,p_object_relationship_id => p_object_relationship_id
469 ,p_record_version_number => p_record_version_number
470 ,x_return_status => l_return_status
471 ,x_msg_count => l_msg_count
472 ,x_msg_data => l_msg_data
473 );
474
475 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
476 x_msg_count := FND_MSG_PUB.count_msg;
477 IF x_msg_count = 1 then
478 pa_interface_utils_pub.get_messages
479 (p_encoded => FND_API.G_TRUE,
480 p_msg_index => 1,
481 p_msg_count => l_msg_count,
482 p_msg_data => l_msg_data,
483 p_data => l_data,
484 p_msg_index_out => l_msg_index_out);
485 x_msg_data := l_data;
486 END IF;
487 raise FND_API.G_EXC_ERROR;
488 END IF;
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 EXCEPTION
493 when FND_API.G_EXC_ERROR then
494 if p_commit = FND_API.G_TRUE then
495 rollback to delete_relationship;
496 end if;
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 when FND_API.G_EXC_UNEXPECTED_ERROR then
499 if p_commit = FND_API.G_TRUE then
500 rollback to delete_relationship;
501 end if;
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
504 p_procedure_name => 'DELETE_RELATIONSHIP',
505 p_error_text => SUBSTRB(SQLERRM,1,240));
506 when OTHERS then
507 if p_commit = FND_API.G_TRUE then
508 rollback to delete_relationship;
509 end if;
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
512 p_procedure_name => 'DEKETE_RELATIONSHIP',
513 p_error_text => SUBSTRB(SQLERRM,1,240));
514 raise;
515 END;
516
517
518 -- API name : Create_Dependency
519 -- Type : Public Procedure
520 -- Pre-reqs : None
521 -- Return Value : N/A
522 -- Parameters
523 -- p_api_version IN NUMBER := 1.0
524 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
525 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
526 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
527 -- p_validation_level IN VARCHAR2 := 100
528 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
529 -- p_debug_mode IN VARCHAR2 := 'N'
530 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
531 -- p_src_proj_id IN NUMBER := NULL
532 -- p_src_task_ver_id IN NUMBER := NULL
533 -- p_dest_proj_name IN VARCHAR2 := NULL
534 -- p_dest_proj_id IN NUMBER := NULL
535 -- P_dest_task_name IN VARCHAR2 := NULL
536 -- P_dest_task_id IN NUMBER := NULL
537 -- P_type IN VARCHAR2 := 'FS'
538 -- P_lag_days IN NUMBER := 0
539 -- p_comments IN VARCHAR2 := NULL
540 -- x_return_status OUT VARCHAR2
541 -- x_msg_count OUT NUMBER
542 -- x_msg_data OUT VARCHAR2
543 --
544 -- History
545 --
546 -- 10-dec-03 Maansari -Created
547 --
548 -- FPM bug 3301192
549 --
550
551 procedure Create_dependency
552 (
553 p_api_version IN NUMBER := 1.0
554 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
555 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
556 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
557 ,p_validation_level IN VARCHAR2 := 100
558 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
559 ,p_debug_mode IN VARCHAR2 := 'N'
560 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
561 ,p_src_proj_id IN NUMBER := NULL
562 ,p_src_task_ver_id IN NUMBER := NULL
563 ,p_dest_proj_name IN VARCHAR2 := NULL
564 ,p_dest_proj_id IN NUMBER := NULL
565 ,p_dest_task_name IN VARCHAR2 := NULL
566 ,p_dest_task_ver_id IN NUMBER := NULL
567 ,p_type IN VARCHAR2 := 'FS'
568 ,p_lag_days IN NUMBER := 0
569 ,p_comments IN VARCHAR2 := NULL
570 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
571 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
572 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
573 )
574 IS
575 l_api_name CONSTANT VARCHAR(30) := 'CREATE_DEPENDENCY';
576 l_api_version CONSTANT NUMBER := 1.0;
577
578 l_return_status VARCHAR2(1);
579 l_msg_count NUMBER;
580 l_msg_data VARCHAR2(250);
581 l_data VARCHAR2(250);
582 l_msg_index_out NUMBER;
583 l_error_msg_code VARCHAR2(250);
584
585 l_dest_proj_id NUMBER;
586 l_dest_task_ver_id NUMBER;
587 l_structure_ver_id NUMBER;
588
589 l_work_structure_ver_id NUMBER; /* working structure version */
590 l_lp_structure_ver_id NUMBER; /* latest published structrue version */
591 l_src_proj_ve VARCHAR2(1); /* source project versioning enabled flag */
592 l_dest_proj_ve VARCHAR2(1); /* destination project versioning enabled flag */
593 l_work_dest_task_ver_id NUMBER; /* destination working task version */
594
595
596 CURSOR get_src_str_ver_id
597 IS
598 SELECT parent_structure_version_id
599 FROM pa_proj_element_versions
600 WHERE project_id = p_src_proj_id
601 AND element_version_id = p_src_task_ver_id
602 AND object_type = 'PA_TASKS';
603
604 BEGIN
605 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.CREATE_DEPENDENCY');
606
607 IF (p_debug_mode = 'Y') THEN
608 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_DEPENDENCY begin');
609 END IF;
610
611 IF (p_commit = FND_API.G_TRUE) THEN
612 savepoint create_dependency;
613 END IF;
614
615 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
616 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617 END IF;
618
619 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
620 FND_MSG_PUB.initialize;
621 END IF;
622
623 IF p_src_proj_id IS NULL
624 THEN
625 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_SRC_PROJ_NULL');
626 RAISE FND_API.G_EXC_ERROR;
627 END IF;
628
629 IF p_src_task_ver_id IS NULL
630 THEN
631 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_SRC_TASK_VER_NULL');
632 RAISE FND_API.G_EXC_ERROR;
633 END IF;
634
635 --project name to id conversion
636 IF ((p_dest_proj_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
637 (p_dest_proj_name IS NOT NULL)) OR
638 ((p_dest_proj_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
639 (p_dest_proj_id IS NOT NULL)) THEN
640 PA_PROJ_ELEMENTS_UTILS.Project_Name_Or_Id(
641 p_project_name => p_dest_proj_name
642 ,p_project_id => p_dest_proj_id
643 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
644 ,x_project_id => l_dest_proj_id
645 ,x_return_status => l_return_status
646 ,x_error_msg_code => l_error_msg_code
647 );
648 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
649 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
650 END IF;
651 ELSE
652 --Throw a message and stop further processing
653 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_DEST_PROJ_NULL');
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656
657 --If the dependency is created within the structure then get the structure ver of the
658 --source task otherwise get the structure ver as follows
659 -- get latest publsihed struccture ver id
660 -- if latest publsihed is not avialable then get the current working version.
661 IF p_src_proj_id = l_dest_proj_id
662 THEN
663 OPEN get_src_str_ver_id;
664 FETCH get_src_str_ver_id INTO l_structure_ver_id;
665 CLOSE get_src_str_ver_id;
666
667 IF l_structure_ver_id IS NULL
668 THEN
669 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_SRC_PROJ_TSK_INV');
670 RAISE FND_API.G_EXC_ERROR;
671 END IF;
672 ELSE
673 l_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(l_dest_proj_id);
674 IF l_structure_ver_id IS NULL
675 THEN
676 l_structure_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_current_working_ver_id(l_dest_proj_id);
677 END IF;
678 END IF;
679
680 --task name to id conversion
681 IF ((p_dest_task_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
682 (p_dest_task_name IS NOT NULL)) OR
683 ((p_dest_task_ver_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
684 (p_dest_task_ver_id IS NOT NULL)) THEN
685
686 PA_PROJ_ELEMENTS_UTILS.task_Ver_Name_Or_Id
687 (
688 p_task_name => p_dest_task_name
689 ,p_task_version_id => p_dest_task_ver_id
690 ,p_structure_version_id => l_structure_ver_id
691 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
692 ,x_task_version_id => l_dest_task_ver_id
693 ,x_return_status => l_return_status
694 ,x_error_msg_code => l_error_msg_code
695 ) ;
696
697 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
698 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
699 END IF;
700 ELSE
701 --Throw a message and stop further processing
702 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_DEST_TASK_NULL');
703 RAISE FND_API.G_EXC_ERROR;
704 END IF;
705
706 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
707 x_msg_count := FND_MSG_PUB.count_msg;
708 IF x_msg_count = 1 then
709 pa_interface_utils_pub.get_messages
710 (p_encoded => FND_API.G_TRUE,
711 p_msg_index => 1,
712 p_msg_count => l_msg_count,
713 p_msg_data => l_msg_data,
714 p_data => l_data,
715 p_msg_index_out => l_msg_index_out);
716 x_msg_data := l_data;
717 END IF;
718 raise FND_API.G_EXC_ERROR;
719 END IF;
720
721 --Call private create dependency API here.
722
723 PA_RELATIONSHIP_PVT.Create_Dependency
724 (
725 p_api_version => p_api_version
726 ,p_init_msg_list => p_init_msg_list
727 ,p_commit => p_commit
728 ,p_validate_only => p_validate_only
729 ,p_validation_level => p_validation_level
730 ,p_calling_module => p_calling_module
731 ,p_debug_mode => p_debug_mode
732 ,p_max_msg_count => p_max_msg_count
733 ,p_src_proj_id => p_src_proj_id
734 ,p_src_task_ver_id => p_src_task_ver_id
735 ,p_dest_proj_id => l_dest_proj_id
736 ,p_dest_task_ver_id => l_dest_task_ver_id
737 ,p_type => p_type
738 ,p_lag_days => p_lag_days
739 ,p_comments => p_comments
740 ,x_return_status => l_return_status
741 ,x_msg_count => l_msg_count
742 ,x_msg_data => l_msg_data
743 );
744
745 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
746 x_msg_count := FND_MSG_PUB.count_msg;
747 IF x_msg_count = 1 then
748 pa_interface_utils_pub.get_messages
749 (p_encoded => FND_API.G_TRUE,
750 p_msg_index => 1,
751 p_msg_count => l_msg_count,
752 p_msg_data => l_msg_data,
753 p_data => l_data,
754 p_msg_index_out => l_msg_index_out);
755 x_msg_data := l_data;
756 END IF;
757 raise FND_API.G_EXC_ERROR;
758 END IF;
759
760 x_return_status := FND_API.G_RET_STS_SUCCESS;
761
762 IF (p_commit = FND_API.G_TRUE) THEN
763 COMMIT;
764 END IF;
765
766 IF (p_debug_mode = 'Y') THEN
767 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_DEPENDENCY END');
768 END IF;
769
770 EXCEPTION
771 when FND_API.G_EXC_ERROR then
772 if p_commit = FND_API.G_TRUE then
773 rollback to create_dependency;
774 end if;
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 when FND_API.G_EXC_UNEXPECTED_ERROR then
777 if p_commit = FND_API.G_TRUE then
778 rollback to create_dependency;
779 end if;
780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
781 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
782 p_procedure_name => 'CREATE_DEPENDENCY',
783 p_error_text => SUBSTRB(SQLERRM,1,240));
784 when OTHERS then
785 if p_commit = FND_API.G_TRUE then
786 rollback to create_dependency;
787 end if;
788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
789 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
790 p_procedure_name => 'CREATE_DEPENDENCY',
791 p_error_text => SUBSTRB(SQLERRM,1,240));
792 raise;
793 END Create_Dependency;
794
795 -- API name : Update_Dependency
796 -- Type : Public Procedure
797 -- Pre-reqs : None
798 -- Return Value : N/A
799 -- Parameters
800 -- p_api_version IN NUMBER := 1.0
801 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
802 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
803 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
804 -- p_validation_level IN VARCHAR2 := 100
805 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
806 -- p_debug_mode IN VARCHAR2 := 'N'
807 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
808 -- p_task_version_id IN NUMBER := NULL
809 -- p_type IN VARCHAR2 := NULL
810 -- p_lag_days IN NUMBER := NULL
811 -- p_comments IN VARCHAR2 := NULL
812 -- p_record_version_number IN NUMBER
813 -- x_return_status OUT VARCHAR2
814 -- x_msg_count OUT NUMBER
815 -- x_msg_data OUT VARCHAR2
816 --
817 -- History
818 --
819 -- 10-dec-03 Maansari -Created
820 --
821 -- FPM bug 3301192
822 --
823
824 procedure Update_dependency
825 (
826 p_api_version IN NUMBER := 1.0
827 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
828 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
829 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
830 ,p_validation_level IN VARCHAR2 := 100
831 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
832 ,p_debug_mode IN VARCHAR2 := 'N'
833 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
834 ,p_task_version_id IN NUMBER := NULL
835 ,p_src_task_version_id IN NUMBER := NULL
836 ,p_type IN VARCHAR2 := NULL
837 ,p_lag_days IN NUMBER := NULL
838 ,p_comments IN VARCHAR2 := NULL
839 ,p_record_version_number IN NUMBER
840 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
841 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
842 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
843 )
844 IS
845 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_DEPENDENCY';
846 l_api_version CONSTANT NUMBER := 1.0;
847
848 l_return_status VARCHAR2(1);
849 l_msg_count NUMBER;
850 l_msg_data VARCHAR2(250);
851 l_data VARCHAR2(250);
852 l_msg_index_out NUMBER;
853 l_error_msg_code VARCHAR2(250);
854 BEGIN
855 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY');
856
857 IF (p_debug_mode = 'Y') THEN
858 pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY begin');
859 END IF;
860
861 IF (p_commit = FND_API.G_TRUE) THEN
862 savepoint update_dependency;
863 END IF;
864
865 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 END IF;
868
869 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
870 FND_MSG_PUB.initialize;
871 END IF;
872
873 --Call private update dependency API here.
874 PA_RELATIONSHIP_PVT.Update_Dependency
875 (
876 p_api_version => p_api_version
877 ,p_init_msg_list => p_init_msg_list
878 ,p_commit => p_commit
879 ,p_validate_only => p_validate_only
880 ,p_validation_level => p_validation_level
881 ,p_calling_module => p_calling_module
882 ,p_debug_mode => p_debug_mode
883 ,p_max_msg_count => p_max_msg_count
884 ,p_task_version_id => p_task_version_id
885 ,p_src_task_version_id => p_src_task_version_id
886 ,p_type => p_type
887 ,p_lag_days => p_lag_days
888 ,p_comments => p_comments
889 ,p_record_version_number => p_record_version_number
890 ,x_return_status => l_return_status
891 ,x_msg_count => l_msg_count
892 ,x_msg_data => l_msg_data
893 );
894
895 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
896 x_msg_count := FND_MSG_PUB.count_msg;
897 IF x_msg_count = 1 then
898 pa_interface_utils_pub.get_messages
899 (p_encoded => FND_API.G_TRUE,
900 p_msg_index => 1,
901 p_msg_count => l_msg_count,
902 p_msg_data => l_msg_data,
903 p_data => l_data,
904 p_msg_index_out => l_msg_index_out);
905 x_msg_data := l_data;
906 END IF;
907 raise FND_API.G_EXC_ERROR;
908 END IF;
909
910 x_return_status := FND_API.G_RET_STS_SUCCESS;
911
912 IF (p_commit = FND_API.G_TRUE) THEN
913 COMMIT;
914 END IF;
915
916 IF (p_debug_mode = 'Y') THEN
917 pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_DEPENDENCY END');
918 END IF;
919
920 EXCEPTION
921 when FND_API.G_EXC_ERROR then
922 if p_commit = FND_API.G_TRUE then
923 rollback to update_dependency;
924 end if;
925 x_return_status := FND_API.G_RET_STS_ERROR;
926 when FND_API.G_EXC_UNEXPECTED_ERROR then
927 if p_commit = FND_API.G_TRUE then
928 rollback to update_dependency;
929 end if;
930 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
931 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
932 p_procedure_name => 'UPDATE_DEPENDENCY',
933 p_error_text => SUBSTRB(SQLERRM,1,240));
934 when OTHERS then
935 if p_commit = FND_API.G_TRUE then
936 rollback to update_dependency;
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_RELATIONSHIOP_PUB',
940 p_procedure_name => 'UPDATE_DEPENDENCY',
941 p_error_text => SUBSTRB(SQLERRM,1,240));
942 raise;
943 END Update_Dependency;
944
945 -- API name : Delete_Dependency
946 -- Type : Public Procedure
947 -- Pre-reqs : None
948 -- Return Value : N/A
949 -- Parameters
950 -- p_api_version IN NUMBER := 1.0
951 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
952 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
953 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
954 -- p_validation_level IN VARCHAR2 := 100
955 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
956 -- p_debug_mode IN VARCHAR2 := 'N'
957 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
958 -- p_object_relationship_id IN NUMBER := NULL
959 -- x_return_status OUT VARCHAR2
960 -- x_msg_count OUT NUMBER
961 -- x_msg_data OUT VARCHAR2
962 --
963 -- History
964 --
965 -- 10-dec-03 Maansari -Created
966 --
967 -- FPM bug 3301192
968 --
969
970 procedure Delete_Dependency
971 (
972 p_api_version IN NUMBER := 1.0
973 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
974 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
975 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
976 ,p_validation_level IN VARCHAR2 := 100
977 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
978 ,p_debug_mode IN VARCHAR2 := 'N'
979 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
980 ,p_object_relationship_id IN NUMBER := NULL
981 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
982 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
983 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
984 )
985 IS
986 l_api_name CONSTANT VARCHAR(30) := 'DELETE_DEPENDENCY';
987 l_api_version CONSTANT NUMBER := 1.0;
988
989 l_return_status VARCHAR2(1);
990 l_msg_count NUMBER;
991 l_msg_data VARCHAR2(250);
992 l_data VARCHAR2(250);
993 l_msg_index_out NUMBER;
994 l_error_msg_code VARCHAR2(250);
995 BEGIN
996 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY');
997
998 IF (p_debug_mode = 'Y') THEN
999 pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY begin');
1000 END IF;
1001
1002 IF (p_commit = FND_API.G_TRUE) THEN
1003 savepoint delete_dependency;
1004 END IF;
1005
1006 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1008 END IF;
1009
1010 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1011 FND_MSG_PUB.initialize;
1012 END IF;
1013
1014 --Call private delete dependency API here.
1015
1016 PA_RELATIONSHIP_PVT.Delete_Dependency
1017 (
1018 p_api_version => p_api_version
1019 ,p_init_msg_list => p_init_msg_list
1020 ,p_commit => p_commit
1021 ,p_validate_only => p_validate_only
1022 ,p_validation_level => p_validation_level
1023 ,p_calling_module => p_calling_module
1024 ,p_debug_mode => p_debug_mode
1025 ,p_max_msg_count => p_max_msg_count
1026 ,p_object_relationship_id => p_object_relationship_id
1027 ,x_return_status => l_return_status
1028 ,x_msg_count => l_msg_count
1029 ,x_msg_data => l_msg_data
1030 );
1031
1032 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1033 x_msg_count := FND_MSG_PUB.count_msg;
1034 IF x_msg_count = 1 then
1035 pa_interface_utils_pub.get_messages
1036 (p_encoded => FND_API.G_TRUE,
1037 p_msg_index => 1,
1038 p_msg_count => l_msg_count,
1039 p_msg_data => l_msg_data,
1040 p_data => l_data,
1041 p_msg_index_out => l_msg_index_out);
1042 x_msg_data := l_data;
1043 END IF;
1044 raise FND_API.G_EXC_ERROR;
1045 END IF;
1046
1047 x_return_status := FND_API.G_RET_STS_SUCCESS;
1048
1049 IF (p_commit = FND_API.G_TRUE) THEN
1050 COMMIT;
1051 END IF;
1052
1053 IF (p_debug_mode = 'Y') THEN
1054 pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_DEPENDENCY END');
1055 END IF;
1056 EXCEPTION
1057 when FND_API.G_EXC_ERROR then
1058 if p_commit = FND_API.G_TRUE then
1059 rollback to delete_dependency;
1060 end if;
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 when FND_API.G_EXC_UNEXPECTED_ERROR then
1063 if p_commit = FND_API.G_TRUE then
1064 rollback to delete_dependency;
1065 end if;
1066 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1067 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
1068 p_procedure_name => 'DELETE_DEPENDENCY',
1069 p_error_text => SUBSTRB(SQLERRM,1,240));
1070 when OTHERS then
1071 if p_commit = FND_API.G_TRUE then
1072 rollback to delete_dependency;
1073 end if;
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
1076 p_procedure_name => 'DELETE_DEPENDENCY',
1077 p_error_text => SUBSTRB(SQLERRM,1,240));
1078 raise;
1079 END Delete_Dependency;
1080
1081
1082 -- API name : Create_Subproject_Association
1083 -- Type : Public Procedure
1084 -- Pre-reqs : None
1085 -- Return Value : N/A
1086 -- Parameters
1087 -- p_api_version IN NUMBER := 1.0
1088 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1089 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1090 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1091 -- p_validation_level IN VARCHAR2 := 100
1092 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1093 -- p_debug_mode IN VARCHAR2 := 'N'
1094 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1095 -- p_src_proj_id IN NUMBER
1096 -- p_task_ver_id IN NUMBER
1097 -- p_dest_proj_id IN NUMBER := NULL
1098 -- p_dest_proj_name IN VARCHAR2
1099 -- p_comment IN VARCHAR2
1100 -- x_return_status OUT VARCHAR2
1101 -- x_msg_count OUT NUMBER
1102 -- x_msg_data OUT VARCHAR2
1103 --
1104 -- History
1105 --
1106 -- 20-Feb-04 Smukka -Created
1107 --
1108 -- FPM bug 3450684
1109 --
1110 PROCEDURE create_subproject_association(
1111 p_api_version IN NUMBER := 1.0
1112 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1113 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1114 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1115 ,p_validation_level IN VARCHAR2 := 100
1116 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1117 ,p_debug_mode IN VARCHAR2 := 'N'
1118 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1119 ,p_src_proj_id IN NUMBER
1120 ,p_task_ver_id IN NUMBER
1121 ,p_dest_proj_id IN NUMBER
1122 ,p_dest_proj_name IN VARCHAR2 := NULL
1123 ,p_comment IN VARCHAR2
1124 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1125 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1126 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1127 IS
1128 --
1129 --
1130 -- Project_id = p_src_proj_id
1131 -- Task_Version_Id = p_src_task_version_id
1132 -- subProject_id = p_dest_proj_id
1133 -- subPorject_Name = p_dest_proj_name
1134 -- Comments = p_comments
1135 --
1136 --
1137 l_api_version CONSTANT NUMBER := 1.0;
1138 l_api_name CONSTANT VARCHAR(30) := 'CREATE_SUBPROJECT_ASSOCIATION';
1139 l_error_msg_code VARCHAR2(250);
1140 l_data VARCHAR2(250);
1141 l_msg_count NUMBER;
1142 l_msg_data VARCHAR2(250);
1143 l_msg_index_out NUMBER;
1144 l_return_status VARCHAR2(1);
1145
1146 l_dest_proj_id NUMBER:=0;
1147 l_src_proj_id NUMBER:=0;
1148 BEGIN
1149 --
1150 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.CREATE_SUBPROJECT_ASSOCIATION');
1151 --
1152 IF (p_debug_mode = 'Y') THEN
1153 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_SUBPROJECT_ASSOCIATION begin');
1154 END IF;
1155 --
1156 IF (p_commit = FND_API.G_TRUE) THEN
1157 savepoint create_subproject_association;
1158 END IF;
1159 --
1160 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1162 END IF;
1163 --
1164 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1165 FND_MSG_PUB.initialize;
1166 END IF;
1167 --
1168 --
1169 -- For destination Project
1170 IF ((p_dest_proj_name <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR) AND
1171 (p_dest_proj_name IS NOT NULL)) OR
1172 ((p_dest_proj_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
1173 (p_dest_proj_id IS NOT NULL)) THEN
1174 PA_PROJ_ELEMENTS_UTILS.Project_Name_Or_Id(
1175 p_project_name => p_dest_proj_name
1176 ,p_project_id => p_dest_proj_id
1177 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
1178 ,x_project_id => l_dest_proj_id
1179 ,x_return_status => l_return_status
1180 ,x_error_msg_code => l_error_msg_code
1181 );
1182 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1183 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
1184 END IF;
1185 ELSE
1186 --Throw a message and stop further processing
1187 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_DEST_PROJ_NULL');
1188 RAISE FND_API.G_EXC_ERROR;
1189 END IF;
1190 --
1191 --
1192 PA_RELATIONSHIP_PVT.create_subproject_association(
1193 p_api_version => p_api_version,
1194 p_init_msg_list => p_init_msg_list,
1195 p_validate_only => p_validate_only,
1196 p_validation_level => p_validation_level,
1197 p_calling_module => p_calling_module,
1198 p_commit => p_commit,
1199 p_debug_mode => p_debug_mode,
1200 p_max_msg_count => p_max_msg_count,
1201 p_src_proj_id => p_src_proj_id,
1202 p_task_ver_id => p_task_ver_id,
1203 p_dest_proj_id => l_dest_proj_id,
1204 p_dest_proj_name => p_dest_proj_name,
1205 p_comment => p_comment,
1206 x_return_status => l_return_status,
1207 x_msg_count => x_msg_count,
1208 x_msg_data => x_msg_data);
1209 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1210 x_msg_count := FND_MSG_PUB.count_msg;
1211 IF x_msg_count = 1 then
1212 pa_interface_utils_pub.get_messages
1213 (p_encoded => FND_API.G_TRUE,
1214 p_msg_index => 1,
1215 p_msg_count => l_msg_count,
1216 p_msg_data => l_msg_data,
1217 p_data => l_data,
1218 p_msg_index_out => l_msg_index_out);
1219 x_msg_data := l_data;
1220 END IF;
1221 raise FND_API.G_EXC_ERROR;
1222 END IF;
1223 --
1224 x_return_status := FND_API.G_RET_STS_SUCCESS;
1225 --
1226 IF (p_commit = FND_API.G_TRUE) THEN
1227 COMMIT;
1228 END IF;
1229 --
1230 IF (p_debug_mode = 'Y') THEN
1231 pa_debug.debug('PA_RELATIONSHIP_PUB.CREATE_SUBPROJECT_ASSOCIATION END');
1232 END IF;
1233 --
1234 EXCEPTION
1235 when FND_API.G_EXC_ERROR then
1236 if p_commit = FND_API.G_TRUE then
1237 rollback to create_subproject_association;
1238 end if;
1239 x_return_status := FND_API.G_RET_STS_ERROR;
1240 when FND_API.G_EXC_UNEXPECTED_ERROR then
1241 if p_commit = FND_API.G_TRUE then
1242 rollback to create_subproject_association;
1243 end if;
1244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1245 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
1246 p_procedure_name => 'CREATE_SUBPROJECT_ASSOCIATION',
1247 p_error_text => SUBSTRB(SQLERRM,1,240));
1248 when OTHERS then
1249 if p_commit = FND_API.G_TRUE then
1250 rollback to create_subproject_association;
1251 end if;
1252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1253 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
1254 p_procedure_name => 'CREATE_SUBPROJECT_ASSOCIATION',
1255 p_error_text => SUBSTRB(SQLERRM,1,240));
1256 raise;
1257 END create_subproject_association;
1258 --
1259 --
1260 --
1261 -- API name : Update_Subproject_Association
1262 -- Type : Public Procedure
1263 -- Pre-reqs : None
1264 -- Return Value : N/A
1265 -- Parameters
1266 -- p_api_version IN NUMBER := 1.0
1267 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1268 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1269 -- p_validation_level IN VARCHAR2 := 100
1270 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1271 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1272 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1273 -- p_debug_mode IN VARCHAR2 := 'N'
1274 -- p_object_relationship_id IN NUMBER
1275 -- p_record_version_number IN NUMBER
1276 -- p_comment IN VARCHAR2
1277 -- x_return_status OUT VARCHAR2
1278 -- x_msg_count OUT NUMBER
1279 -- x_msg_data OUT VARCHAR2
1280 --
1281 -- History
1282 --
1283 -- 20-Feb-04 Smukka -Created
1284 -- -Created this procedure for subproject association
1285 --
1286 -- FPM bug 3450684
1287 --
1288 --
1289 Procedure Update_Subproject_Association(p_api_version IN NUMBER := 1.0,
1290 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1291 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
1292 p_validation_level IN VARCHAR2 := 100,
1293 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
1294 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1295 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1296 p_debug_mode IN VARCHAR2 := 'N',
1297 p_object_relationship_id IN NUMBER,
1298 p_record_version_number IN NUMBER,
1299 p_comment IN VARCHAR2,
1300 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1301 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1302 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1303 IS
1304 --
1305 l_api_version CONSTANT NUMBER := 1.0;
1306 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_SUBPROJECT_ASSOCIATION';
1307 l_error_msg_code VARCHAR2(250);
1308 l_data VARCHAR2(250);
1309 l_msg_count NUMBER;
1310 l_msg_data VARCHAR2(250);
1311 l_msg_index_out NUMBER;
1312 l_return_status VARCHAR2(1);
1313 --
1314 l_dest_proj_id NUMBER:=0;
1315 l_src_proj_id NUMBER:=0;
1316 --
1317 BEGIN
1318 --
1319 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION');
1320 --
1321 IF (p_debug_mode = 'Y') THEN
1322 pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION begin');
1323 END IF;
1324 --
1325 IF (p_commit = FND_API.G_TRUE) THEN
1326 savepoint Update_Subproject_Association;
1327 END IF;
1328 --
1329 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1331 END IF;
1332 --
1333 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1334 FND_MSG_PUB.initialize;
1335 END IF;
1336 --
1337 --
1338 PA_RELATIONSHIP_PVT.Update_Subproject_Association
1339 (p_api_version => p_api_version,
1340 p_init_msg_list => p_init_msg_list,
1341 p_validate_only => p_validate_only,
1342 p_validation_level => p_validation_level,
1343 p_calling_module => p_calling_module,
1344 p_max_msg_count => p_max_msg_count,
1345 p_commit => p_commit,
1346 p_debug_mode => p_debug_mode,
1347 p_object_relationship_id => p_object_relationship_id,
1348 p_record_version_number => p_record_version_number,
1349 p_comment => p_comment,
1350 x_return_status => l_return_status,
1351 x_msg_count => x_msg_count,
1352 x_msg_data => x_msg_data);
1353 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1354 x_msg_count := FND_MSG_PUB.count_msg;
1355 IF x_msg_count = 1 then
1356 pa_interface_utils_pub.get_messages
1357 (p_encoded => FND_API.G_TRUE,
1358 p_msg_index => 1,
1359 p_msg_count => l_msg_count,
1360 p_msg_data => l_msg_data,
1361 p_data => l_data,
1362 p_msg_index_out => l_msg_index_out);
1363 x_msg_data := l_data;
1364 END IF;
1365 raise FND_API.G_EXC_ERROR;
1366 END IF;
1367 --
1368 x_return_status := FND_API.G_RET_STS_SUCCESS;
1369 --
1370 IF (p_commit = FND_API.G_TRUE) THEN
1371 COMMIT;
1372 END IF;
1373 --
1374 IF (p_debug_mode = 'Y') THEN
1375 pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION END');
1376 END IF;
1377 --
1378 EXCEPTION
1379 when FND_API.G_EXC_ERROR then
1380 if p_commit = FND_API.G_TRUE then
1381 rollback to Update_Subproject_Association;
1382 end if;
1383 x_return_status := FND_API.G_RET_STS_ERROR;
1384 when FND_API.G_EXC_UNEXPECTED_ERROR then
1385 if p_commit = FND_API.G_TRUE then
1386 rollback to Update_Subproject_Association;
1387 end if;
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1389 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
1390 p_procedure_name => 'UPDATE_SUBPROJECT_ASSOCIATION',
1391 p_error_text => SUBSTRB(SQLERRM,1,240));
1392 when OTHERS then
1393 if p_commit = FND_API.G_TRUE then
1394 rollback to Update_Subproject_Association;
1395 end if;
1396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1397 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
1398 p_procedure_name => 'UPDATE_SUBPROJECT_ASSOCIATION',
1399 p_error_text => SUBSTRB(SQLERRM,1,240));
1400 raise;
1401 END Update_Subproject_Association;
1402 --
1403 --
1404 --
1405 -- API name : Delete_SubProject_Association
1406 -- Type : Public Procedure
1407 -- Pre-reqs : None
1408 -- Return Value : N/A
1409 -- Parameters
1410 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1411 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1412 -- p_validation_level IN VARCHAR2 := 100
1413 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1414 -- p_debug_mode IN VARCHAR2 := 'N'
1415 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1416 -- p_object_relationships_id IN NUMBER
1417 -- p_record_version_number IN NUMBER
1418 -- x_return_status OUT VARCHAR2
1419 -- x_msg_count OUT NUMBER
1420 -- x_msg_data OUT VARCHAR2
1421 --
1422 -- History
1423 --
1424 -- 20-Feb-04 Smukka -Created
1425 -- -Created this procedure for subproject association
1426 --
1427 -- FPM bug 3450684
1428 --
1429 --
1430 Procedure Delete_SubProject_Association(p_api_version IN NUMBER := 1.0,
1431 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1432 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1433 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
1434 p_validation_level IN VARCHAR2 := 100,
1435 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
1436 p_debug_mode IN VARCHAR2 := 'N',
1437 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
1438 p_object_relationships_id IN NUMBER,
1439 p_record_version_number IN NUMBER,
1440 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1441 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1442 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1443 IS
1444 --
1445 l_api_version CONSTANT NUMBER := 1.0;
1446 l_api_name CONSTANT VARCHAR(30) := 'DELETE_SUBPROJECT_ASSOCIATION';
1447 l_error_msg_code VARCHAR2(250);
1448 l_data VARCHAR2(250);
1449 l_msg_count NUMBER;
1450 l_msg_data VARCHAR2(250);
1451 l_msg_index_out NUMBER;
1452 l_return_status VARCHAR2(1);
1453 --
1454 l_dest_proj_id NUMBER:=0;
1455 l_src_proj_id NUMBER:=0;
1456 --
1457 BEGIN
1458 --
1459 pa_debug.init_err_stack ('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION');
1460 --
1461 IF (p_debug_mode = 'Y') THEN
1462 pa_debug.debug('PA_RELATIONSHIP_PUB.UPDATE_SUBPROJECT_ASSOCIATION begin');
1463 END IF;
1464 --
1465 IF (p_commit = FND_API.G_TRUE) THEN
1466 savepoint Delete_SubProject_Association;
1467 END IF;
1468 --
1469 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name, g_pkg_name) then
1470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1471 END IF;
1472 --
1473 IF FND_API.TO_BOOLEAN(nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
1474 FND_MSG_PUB.initialize;
1475 END IF;
1476 --
1477 --
1478 PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
1479 p_commit => p_commit,
1480 p_validate_only => p_validate_only,
1481 p_validation_level => p_validation_level,
1482 p_calling_module => p_calling_module,
1483 p_debug_mode => p_debug_mode,
1484 p_max_msg_count => p_max_msg_count,
1485 p_object_relationships_id => p_object_relationships_id,
1486 p_record_version_number => p_record_version_number,
1487 x_return_status => l_return_status,
1488 x_msg_count => x_msg_count,
1489 x_msg_data => x_msg_data);
1490 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1491 x_msg_count := FND_MSG_PUB.count_msg;
1492 IF x_msg_count = 1 then
1493 pa_interface_utils_pub.get_messages
1494 (p_encoded => FND_API.G_TRUE,
1495 p_msg_index => 1,
1496 p_msg_count => l_msg_count,
1497 p_msg_data => l_msg_data,
1498 p_data => l_data,
1499 p_msg_index_out => l_msg_index_out);
1500 x_msg_data := l_data;
1501 END IF;
1502 raise FND_API.G_EXC_ERROR;
1503 END IF;
1504 --
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506 --
1507 IF (p_commit = FND_API.G_TRUE) THEN
1508 COMMIT;
1509 END IF;
1510 --
1511 IF (p_debug_mode = 'Y') THEN
1512 pa_debug.debug('PA_RELATIONSHIP_PUB.DELETE_SUBPROJECT_ASSOCIATION END');
1513 END IF;
1514 --
1515 EXCEPTION
1516 when FND_API.G_EXC_ERROR then
1517 if p_commit = FND_API.G_TRUE then
1518 rollback to Delete_SubProject_Association;
1519 end if;
1520 x_return_status := FND_API.G_RET_STS_ERROR;
1521 when FND_API.G_EXC_UNEXPECTED_ERROR then
1522 if p_commit = FND_API.G_TRUE then
1523 rollback to Delete_SubProject_Association;
1524 end if;
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1526 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PUB',
1527 p_procedure_name => 'DELETE_SUBPROJECT_ASSOCIATION',
1528 p_error_text => SUBSTRB(SQLERRM,1,240));
1529 when OTHERS then
1530 if p_commit = FND_API.G_TRUE then
1531 rollback to Delete_SubProject_Association;
1532 end if;
1533 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1534 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_PUB',
1535 p_procedure_name => 'DELETE_SUBPROJECT_ASSOCIATION',
1536 p_error_text => SUBSTRB(SQLERRM,1,240));
1537 raise;
1538 END Delete_SubProject_Association;
1539 --
1540 --
1541
1542
1543 -- -----------------------------------------------------
1544 -- function UPDATE_PROGRAM_GROUPS
1545 --
1546 -- p_operation_type = 'ADD' ==> This API must be called after the
1547 -- association row has been added in
1548 -- PA_OBJECT_RELATIONSHIPS
1549 --
1550 -- p_operation_type = 'DROP' ==> This API must be called before the
1551 -- association row has been removed
1552 -- from PA_OBJECT_RELATIONSHIPS
1553 --
1554 -- After this API looks up the association information it calls the
1555 -- other API UPDATE_PROGRAM_GROUPS with the relevant parameters.
1556 --
1557 -- History
1558 -- 12-MAR-2004 SVERMETT Created
1559 --
1560 -- -----------------------------------------------------
1561 function UPDATE_PROGRAM_GROUPS (p_object_relationship_id in number,
1562 p_operation_type in varchar2)
1563 return number is
1564
1565 l_parent_task_version_id number;
1566 l_parent_group number;
1567 l_parent_level number;
1568 l_parent_project number;
1569 l_child_structure_version_id number;
1570 l_child_group number;
1571 l_child_level number;
1572 l_child_project number;
1573 l_relationship_type varchar2(10);
1574
1575 begin
1576
1577 select
1578 PARENT_TASK_VERSION_ID,
1579 PARENT_GROUP,
1580 PARENT_LEVEL,
1581 PARENT_PROJECT,
1582 CHILD_STRUCTURE_VERSION_ID,
1583 CHILD_GROUP,
1584 CHILD_LEVEL,
1585 CHILD_PROJECT,
1586 RELATIONSHIP_TYPE
1587 into
1588 l_parent_task_version_id,
1589 l_parent_group,
1590 l_parent_level,
1591 l_parent_project,
1592 l_child_structure_version_id,
1593 l_child_group,
1594 l_child_level,
1595 l_child_project,
1596 l_relationship_type
1597 from
1598 (
1599 select /*+ index(rel, PA_OBJECT_RELATIONSHIPS_U1)
1600 index(ver1, PA_PROJ_ELEMENT_VERSIONS_N3)
1601 index(ver2, PA_PROJ_ELEMENT_VERSIONS_N3) */
1602 rel.OBJECT_ID_FROM1 PARENT_TASK_VERSION_ID,
1603 ver1.PRG_GROUP PARENT_GROUP,
1604 ver1.PRG_LEVEL PARENT_LEVEL,
1605 rel.OBJECT_ID_FROM2 PARENT_PROJECT,
1606 rel.OBJECT_ID_TO1 CHILD_STRUCTURE_VERSION_ID,
1607 ver2.PRG_GROUP CHILD_GROUP,
1608 ver2.PRG_LEVEL CHILD_LEVEL,
1609 rel.OBJECT_ID_TO2 CHILD_PROJECT,
1610 rel.RELATIONSHIP_TYPE RELATIONSHIP_TYPE
1611 from
1612 PA_OBJECT_RELATIONSHIPS rel,
1613 PA_PROJ_ELEMENT_VERSIONS ver1,
1614 PA_PROJ_ELEMENT_VERSIONS ver2
1615 where
1616 rel.OBJECT_RELATIONSHIP_ID = p_object_relationship_id and
1617 ver1.PROJECT_ID = rel.OBJECT_ID_FROM2 and
1618 ver1.OBJECT_TYPE = 'PA_STRUCTURES' and
1619 ver2.PROJECT_ID = rel.OBJECT_ID_TO2 and
1620 ver2.OBJECT_TYPE = 'PA_STRUCTURES'
1621 group by
1622 rel.OBJECT_ID_FROM1,
1623 ver1.PRG_GROUP,
1624 ver1.PRG_LEVEL,
1625 rel.OBJECT_ID_FROM2,
1626 rel.OBJECT_ID_TO1,
1627 ver2.PRG_GROUP,
1628 ver2.PRG_LEVEL,
1629 rel.OBJECT_ID_TO2,
1630 rel.RELATIONSHIP_TYPE
1631 order by
1632 ver1.PRG_GROUP,
1633 ver1.PRG_LEVEL,
1634 ver2.PRG_GROUP,
1635 ver2.PRG_LEVEL
1636 )
1637 where
1638 ROWNUM = 1;
1639
1640 return UPDATE_PROGRAM_GROUPS (l_parent_task_version_id,
1641 l_parent_group,
1642 l_parent_level,
1643 l_parent_project,
1644 l_child_structure_version_id,
1645 l_child_group,
1646 l_child_level,
1647 l_child_project,
1648 l_relationship_type,
1649 p_operation_type);
1650
1651 end UPDATE_PROGRAM_GROUPS;
1652
1653
1654 -- -----------------------------------------------------
1655 -- function UPDATE_PROGRAM_GROUPS
1656 --
1657 -- return: 0 = successful level / group propagation
1658 -- return: -1 = cycle exists during 'ADD' operation type
1659 -- return: -2 = association does not exist during 'DROP' operation
1660 --
1661 -- *** This API assumes that initially no associations exist and
1662 -- *** that associations are added one at a time in serial.
1663 --
1664 -- History
1665 -- 12-MAR-2004 SVERMETT Created
1666 -- 24-JUN-2005 SVERMETT Modified to support the relaxed acyclic rule
1667 -- (old) acyclic rule:
1668 -- No cycle may exist in a program hierarchy.
1669 -- (new) relaxed acyclic rule:
1670 -- A project may not roll up into a program
1671 -- via more than one path.
1672 --
1673 -- -----------------------------------------------------
1674 function UPDATE_PROGRAM_GROUPS (p_parent_task_version_id in number,
1675 p_parent_group in number,
1676 p_parent_level in number,
1677 p_parent_project in number,
1678 p_child_structure_version_id in number,
1679 p_child_group in number,
1680 p_child_level in number,
1681 p_child_project in number,
1682 p_relationship_type in varchar2,
1683 p_operation_type in varchar2)
1684 return number is
1685
1686 l_parent_structure_version_id number;
1687 l_program_group number;
1688 l_parent_group number;
1689 l_parent_level number;
1690 l_parent_project number;
1691 l_child_group number;
1692 l_child_level number;
1693 l_child_project number;
1694 l_level_adjustment number;
1695 l_count number;
1696
1697 l_actual_task_version_id number;
1698
1699 l_last_update_date date;
1700 l_last_updated_by number;
1701 l_creation_date date;
1702 l_created_by number;
1703 l_last_update_login number;
1704
1705 l_new_assoc_parent number;
1706 l_new_assoc_child number;
1707
1708 --Bug 6778370
1709 l_hier_count number;
1710 l_subgrp_exist varchar2(1);
1711
1712 begin
1713
1714 savepoint UPDATE_PROGRAM_GROUPS;
1715
1716 l_parent_group := p_parent_group;
1717 l_parent_level := p_parent_level;
1718 l_parent_project := p_parent_project;
1719 l_child_group := p_child_group;
1720 l_child_level := p_child_level;
1721 l_child_project := p_child_project;
1722
1723 l_new_assoc_parent := null;
1724 l_new_assoc_child := null;
1725
1726 if (l_parent_group is not null) then
1727
1728 update PA_PROJ_ELEMENT_VERSIONS
1729 set PRG_GROUP = l_parent_group,
1730 PRG_LEVEL = l_parent_level
1731 where OBJECT_TYPE = 'PA_STRUCTURES' and
1732 PROJECT_ID = l_parent_project and
1733 (PRG_GROUP is null or PRG_LEVEL is null);
1734
1735 end if;
1736
1737 if (l_child_group is not null) then
1738
1739 update PA_PROJ_ELEMENT_VERSIONS
1740 set PRG_GROUP = l_child_group,
1741 PRG_LEVEL = l_child_level
1742 where OBJECT_TYPE = 'PA_STRUCTURES' and
1743 PROJECT_ID = l_child_project and
1744 (PRG_GROUP is null or PRG_LEVEL is null);
1745
1746 end if;
1747
1748 select OBJECT_ID_FROM1
1749 into l_actual_task_version_id
1750 from PA_OBJECT_RELATIONSHIPS
1751 where OBJECT_TYPE_FROM = 'PA_TASKS' and
1752 OBJECT_TYPE_TO = 'PA_TASKS' and
1753 RELATIONSHIP_TYPE = 'S' and
1754 OBJECT_ID_TO1 = p_parent_task_version_id;
1755
1756 if (p_operation_type = 'ADD') then
1757
1758 if (l_parent_group = l_child_group) then
1759
1760 update PA_PROJ_ELEMENT_VERSIONS
1761 set PRG_GROUP = l_parent_group,
1762 PRG_LEVEL = l_parent_level,
1763 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
1764 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
1765 l_actual_task_version_id) and
1766 OBJECT_TYPE = 'PA_TASKS';
1767
1768 if (l_parent_level < l_child_level) then
1769
1770 -- check if LF or LW link already exists
1771
1772 select PARENT_STRUCTURE_VERSION_ID
1773 into l_parent_structure_version_id
1774 from PA_PROJ_ELEMENT_VERSIONS
1775 where ELEMENT_VERSION_ID = p_parent_task_version_id;
1776
1777 select count(*)
1778 into l_count
1779 from PA_OBJECT_RELATIONSHIPS
1780 where RELATIONSHIP_TYPE = p_relationship_type and
1781 OBJECT_TYPE_FROM = 'PA_TASKS' and
1782 OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1783 OBJECT_ID_FROM2 = l_parent_project and
1784 OBJECT_ID_TO2 = l_child_project and
1785 OBJECT_ID_FROM1 in (select
1786 ver.ELEMENT_VERSION_ID
1787 from
1788 PA_PROJ_ELEMENT_VERSIONS ver
1789 where
1790 ver.PARENT_STRUCTURE_VERSION_ID
1791 = l_parent_structure_version_id) and
1792 OBJECT_ID_TO1 = p_child_structure_version_id and
1793 not(OBJECT_ID_FROM1 = p_parent_task_version_id and
1794 OBJECT_ID_TO1 = p_child_structure_version_id) and
1795 ROWNUM = 1;
1796
1797 if (l_count > 0) then
1798 rollback to UPDATE_PROGRAM_GROUPS;
1799 return -1;
1800 end if;
1801
1802 select count(*)
1803 into l_count
1804 from PA_OBJECT_RELATIONSHIPS
1805 where RELATIONSHIP_TYPE in ('LF', 'LW') and
1806 OBJECT_TYPE_FROM = 'PA_TASKS' and
1807 OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1808 OBJECT_ID_FROM2 = l_parent_project and
1809 OBJECT_ID_TO2 = l_child_project and
1810 not(OBJECT_ID_FROM1 = p_parent_task_version_id and
1811 OBJECT_ID_TO1 = p_child_structure_version_id) and
1812 ROWNUM = 1;
1813
1814 elsif (l_parent_level >= l_child_level) then
1815
1816 l_count := 0;
1817
1818 end if;
1819
1820 if (l_count = 0) then
1821
1822 -- represent program group hierarchy in the form of a directed graph
1823
1824 insert into PA_PROJ_LEVELS_TMP
1825 (
1826 FROM_ID,
1827 TO_ID,
1828 FROM_LEVEL,
1829 TO_LEVEL,
1830 DIRECTION,
1831 ATTRIBUTE1,
1832 ATTRIBUTE2,
1833 ATTRIBUTE3,
1834 ATTRIBUTE4,
1835 ATTRIBUTE5,
1836 ATTRIBUTE6
1837 )
1838 select /*+ ordered
1839 index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
1840 index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
1841 distinct
1842 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
1843 'UP', rel.OBJECT_ID_TO2) FROM_ID,
1844 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
1845 'UP', rel.OBJECT_ID_FROM2) TO_ID,
1846 -1 FROM_LEVEL,
1847 -1 TO_LEVEL,
1848 decode(invert.INVERT_ID, 'DOWN', 'D',
1849 'UP', 'U') DIRECTION,
1850 decode(invert.INVERT_ID,
1851 'DOWN',
1852 decode(rel.OBJECT_ID_FROM2,
1853 l_parent_project,
1854 decode(OBJECT_ID_TO2,
1855 l_child_project,
1856 'NEW_ASSOCIATION_DOWN',
1857 'X'),
1858 'X'),
1859 'UP',
1860 decode(rel.OBJECT_ID_FROM2,
1861 l_parent_project,
1862 decode(OBJECT_ID_TO2,
1863 l_child_project,
1864 'NEW_ASSOCIATION_UP',
1865 'X'),
1866 'X'),
1867 'X') ATTRIBUTE1,
1868 null ATTRIBUTE2,
1869 null ATTRIBUTE3,
1870 null ATTRIBUTE4,
1871 null ATTRIBUTE5,
1872 null ATTRIBUTE6
1873 from
1874 PA_PROJ_ELEMENT_VERSIONS ver,
1875 PA_OBJECT_RELATIONSHIPS rel,
1876 pa_proj_structure_types ppst,
1877 pa_proj_elem_ver_structure ppevs,
1878 pa_projects_all ppa,
1879 (
1880 select 'DOWN' INVERT_ID from dual union all
1881 select 'UP' INVERT_ID from dual
1882 ) invert
1883 where
1884 ver.OBJECT_TYPE = 'PA_TASKS' and
1885 ver.PRG_GROUP = l_parent_group and
1886 ppa.project_id = ver.project_id and
1887 rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
1888 rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
1889 rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1890 rel.RELATIONSHIP_TYPE in ('LF', 'LW')
1891 AND ver.parent_structure_version_id = ppevs.element_version_id
1892 AND ppevs.proj_element_id = ppst.proj_element_id
1893 AND ( (ppst.structure_type_id = 1 and (ppevs.latest_eff_published_flag = 'Y' or ppevs.status_code = 'STRUCTURE_WORKING') )
1894 or ( ppst.structure_type_id = 6 and ppa.structure_sharing_code not in ('SHARE_FULL', 'SHARE_PARTIAL' ) )); -- added last two conditions for bug 7409918
1895
1896
1897 select
1898 count(*)
1899 into
1900 l_count
1901 from
1902 (
1903 select
1904 FROM_ID,
1905 TO_ID
1906 from
1907 PA_PROJ_LEVELS_TMP
1908 group by
1909 FROM_ID,
1910 TO_ID
1911 having
1912 count(*) > 1
1913 );
1914
1915 if (l_count > 0) then
1916 rollback to UPDATE_PROGRAM_GROUPS;
1917 return -1;
1918 end if;
1919
1920 -- check relaxed acyclic rule
1921
1922 begin
1923
1924 for leaf_node in
1925 (
1926 select
1927 distinct
1928 tmp1.TO_ID PROJECT_ID
1929 from
1930 PA_PROJ_LEVELS_TMP tmp1
1931 start with
1932 tmp1.ATTRIBUTE1 = 'NEW_ASSOCIATION_DOWN'
1933 connect by
1934 tmp1.DIRECTION = 'D' and
1935 tmp1.FROM_ID = prior tmp1.TO_ID and
1936 tmp1.TO_ID <> prior tmp1.FROM_ID
1937 minus
1938 select
1939 distinct
1940 tmp2.FROM_ID PROJECT_ID
1941 from
1942 PA_PROJ_LEVELS_TMP tmp2
1943 where
1944 tmp2.DIRECTION = 'D'
1945 ) loop
1946
1947 select
1948 count(*)
1949 into
1950 l_count
1951 from
1952 (
1953 select
1954 tmp3.TO_ID
1955 from
1956 PA_PROJ_LEVELS_TMP tmp3
1957 start with
1958 tmp3.FROM_ID = leaf_node.PROJECT_ID
1959 connect by
1960 tmp3.DIRECTION = 'U' and
1961 tmp3.FROM_ID = prior tmp3.TO_ID and
1962 tmp3.TO_ID <> prior tmp3.FROM_ID
1963 group by
1964 tmp3.TO_ID
1965 having
1966 count(*) > 1
1967 )
1968 where
1969 ROWNUM = 1;
1970
1971 if (l_count > 0) then
1972 rollback to UPDATE_PROGRAM_GROUPS;
1973 return -1;
1974 end if;
1975
1976 end loop;
1977
1978 exception when others then
1979
1980 rollback to UPDATE_PROGRAM_GROUPS;
1981 return -1;
1982
1983 end;
1984
1985 end if;
1986
1987 if (l_parent_level >= l_child_level) then
1988
1989 -- adjust hierarchy levels
1990
1991 update
1992 PA_PROJ_LEVELS_TMP tmp4
1993 set
1994 tmp4.FROM_LEVEL = 1
1995 where
1996 tmp4.FROM_LEVEL <> 1 and
1997 tmp4.FROM_ID in
1998 (
1999 select
2000 tmp3.PROJECT_ID
2001 from
2002 (
2003 select
2004 tmp2.PROJECT_ID,
2005 tmp2.PROJECT_LEVEL
2006 from
2007 (
2008 select
2009 distinct
2010 tmp1.TO_ID PROJECT_ID,
2011 LEVEL PROJECT_LEVEL
2012 from
2013 PA_PROJ_LEVELS_TMP tmp1
2014 start with
2015 tmp1.DIRECTION = 'U'
2016 connect by
2017 tmp1.DIRECTION = 'U' and
2018 tmp1.FROM_ID = prior tmp1.TO_ID and
2019 tmp1.TO_ID <> prior tmp1.FROM_ID
2020 ) tmp2
2021 order by
2022 tmp2.PROJECT_LEVEL desc
2023 ) tmp3
2024 where
2025 ROWNUM = 1
2026 );
2027
2028 l_count := sql%rowcount;
2029
2030 update
2031 PA_PROJ_LEVELS_TMP
2032 set
2033 TO_LEVEL = 1
2034 where
2035 TO_LEVEL <> 1 and
2036 TO_ID in
2037 (
2038 select
2039 tmp1.FROM_ID
2040 from
2041 PA_PROJ_LEVELS_TMP tmp1
2042 where
2043 tmp1.FROM_LEVEL = 1
2044 );
2045
2046 while (l_count > 0) loop
2047
2048 l_count := 0;
2049
2050 update
2051 PA_PROJ_LEVELS_TMP tmp
2052 set
2053 tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
2054 where
2055 tmp.FROM_LEVEL <> -1 and
2056 tmp.TO_LEVEL <> -1 and
2057 tmp.DIRECTION = 'D' and
2058 tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
2059
2060 l_count := l_count + sql%rowcount;
2061
2062 update
2063 PA_PROJ_LEVELS_TMP tmp
2064 set
2065 tmp.TO_LEVEL = decode(tmp.DIRECTION,
2066 'U', tmp.FROM_LEVEL - 1,
2067 'D', tmp.FROM_LEVEL + 1)
2068 where
2069 tmp.FROM_LEVEL <> -1 and
2070 tmp.TO_LEVEL = -1;
2071
2072 l_count := l_count + sql%rowcount;
2073
2074 update
2075 PA_PROJ_LEVELS_TMP tmp2
2076 set
2077 tmp2.TO_LEVEL =
2078 (
2079 select
2080 max(tmp1.TO_LEVEL)
2081 from
2082 PA_PROJ_LEVELS_TMP tmp1
2083 where
2084 tmp1.TO_ID = tmp2.TO_ID
2085 )
2086 where
2087 tmp2.TO_LEVEL <>
2088 (
2089 select
2090 max(tmp1.TO_LEVEL)
2091 from
2092 PA_PROJ_LEVELS_TMP tmp1
2093 where
2094 tmp1.TO_ID = tmp2.TO_ID
2095 );
2096
2097 update
2098 PA_PROJ_LEVELS_TMP tmp2
2099 set
2100 tmp2.FROM_LEVEL =
2101 (
2102 select
2103 tmp1.TO_LEVEL
2104 from
2105 PA_PROJ_LEVELS_TMP tmp1
2106 where
2107 tmp1.TO_ID = tmp2.FROM_ID and
2108 tmp1.TO_LEVEL <> -1 and
2109 ROWNUM = 1
2110 )
2111 where
2112 tmp2.FROM_LEVEL <>
2113 (
2114 select
2115 tmp1.TO_LEVEL
2116 from
2117 PA_PROJ_LEVELS_TMP tmp1
2118 where
2119 tmp1.TO_ID = tmp2.FROM_ID and
2120 tmp1.TO_LEVEL <> -1 and
2121 ROWNUM = 1
2122 );
2123
2124 end loop;
2125
2126 update
2127 PA_PROJ_ELEMENT_VERSIONS ver
2128 set
2129 ver.PRG_LEVEL =
2130 (
2131 select
2132 tmp.TO_LEVEL
2133 from
2134 PA_PROJ_LEVELS_TMP tmp
2135 where
2136 tmp.TO_ID = ver.PROJECT_ID and
2137 tmp.TO_LEVEL <> -1 and
2138 ROWNUM = 1
2139 )
2140 where
2141 ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2142 ver.PRG_GROUP = l_parent_group and
2143 ver.PROJECT_ID in
2144 (
2145 select
2146 distinct
2147 tmp.TO_ID
2148 from
2149 PA_PROJ_LEVELS_TMP tmp
2150 ) and
2151 ver.PRG_LEVEL <>
2152 (
2153 select
2154 tmp.TO_LEVEL
2155 from
2156 PA_PROJ_LEVELS_TMP tmp
2157 where
2158 tmp.TO_ID = ver.PROJECT_ID and
2159 tmp.TO_LEVEL <> -1 and
2160 ROWNUM = 1
2161 );
2162
2163 end if;
2164
2165 else -- l_parent_group <> l_child_group
2166
2167 if (l_parent_group is null) then
2168 l_parent_level := 1;
2169 l_new_assoc_parent := l_parent_project;
2170 end if;
2171
2172 if (l_child_group is null) then
2173 l_child_level := 1;
2174 l_new_assoc_child := l_child_project;
2175 end if;
2176
2177 if (l_parent_level < l_child_level) then
2178
2179 l_level_adjustment := l_child_level - l_parent_level - 1;
2180
2181 update PA_PROJ_ELEMENT_VERSIONS
2182 set PRG_GROUP = l_child_group,
2183 PRG_LEVEL = l_child_level - 1,
2184 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2185 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2186 l_actual_task_version_id) and
2187 OBJECT_TYPE = 'PA_TASKS';
2188
2189 if (l_parent_group is null) then
2190
2191 l_parent_group := l_child_group;
2192
2193 update PA_PROJ_ELEMENT_VERSIONS
2194 set PRG_GROUP = l_child_group,
2195 PRG_LEVEL = l_parent_level + l_level_adjustment
2196 where PROJECT_ID = l_parent_project and
2197 OBJECT_TYPE = 'PA_STRUCTURES';
2198
2199 else
2200
2201 update PA_PROJ_ELEMENT_VERSIONS
2202 set PRG_GROUP = l_child_group,
2203 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2204 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2205 PRG_GROUP = l_parent_group;
2206
2207 end if;
2208
2209 elsif (l_parent_level >= l_child_level) then
2210
2211 l_level_adjustment := l_parent_level - l_child_level + 1;
2212
2213 if (l_parent_group is null and l_child_group is null) then
2214
2215 select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
2216 into l_parent_group
2217 from DUAL;
2218
2219 update PA_PROJ_ELEMENT_VERSIONS
2220 set PRG_GROUP = l_parent_group,
2221 PRG_LEVEL = l_parent_level,
2222 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2223 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2224 l_actual_task_version_id) and
2225 OBJECT_TYPE = 'PA_TASKS';
2226
2227 l_child_group := l_parent_group;
2228
2229 update PA_PROJ_ELEMENT_VERSIONS
2230 set PRG_GROUP = l_parent_group,
2231 PRG_LEVEL = decode(PROJECT_ID, l_parent_project,
2232 l_parent_level,
2233 l_child_level +
2234 l_level_adjustment)
2235 where PROJECT_ID in (l_parent_project,
2236 l_child_project) and
2237 OBJECT_TYPE = 'PA_STRUCTURES';
2238
2239 elsif (l_child_group is null) then
2240
2241 update PA_PROJ_ELEMENT_VERSIONS
2242 set PRG_GROUP = l_parent_group,
2243 PRG_LEVEL = l_parent_level,
2244 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2245 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2246 l_actual_task_version_id) and
2247 OBJECT_TYPE = 'PA_TASKS';
2248
2249 l_child_group := l_parent_group;
2250
2251 update PA_PROJ_ELEMENT_VERSIONS
2252 set PRG_GROUP = l_parent_group,
2253 PRG_LEVEL = l_child_level + l_level_adjustment
2254 where PROJECT_ID = l_child_project and
2255 OBJECT_TYPE = 'PA_STRUCTURES';
2256
2257 elsif (l_parent_group is null) then
2258
2259 update PA_PROJ_ELEMENT_VERSIONS
2260 set PRG_GROUP = l_child_group,
2261 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2262 where PRG_GROUP = l_child_group and
2263 OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS');
2264
2265 update PA_PROJ_ELEMENT_VERSIONS
2266 set PRG_GROUP = l_child_group,
2267 PRG_LEVEL = l_child_level + l_level_adjustment - 1,
2268 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2269 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2270 l_actual_task_version_id) and
2271 OBJECT_TYPE = 'PA_TASKS';
2272
2273 l_parent_group := l_child_group;
2274
2275 update PA_PROJ_ELEMENT_VERSIONS
2276 set PRG_GROUP = l_child_group,
2277 PRG_LEVEL = l_child_level + l_level_adjustment - 1
2278 where PROJECT_ID = l_parent_project and
2279 OBJECT_TYPE = 'PA_STRUCTURES';
2280
2281 else
2282
2283 update PA_PROJ_ELEMENT_VERSIONS
2284 set PRG_GROUP = l_parent_group,
2285 PRG_LEVEL = l_parent_level,
2286 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2287 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2288 l_actual_task_version_id) and
2289 OBJECT_TYPE = 'PA_TASKS';
2290
2291 update PA_PROJ_ELEMENT_VERSIONS
2292 set PRG_GROUP = l_parent_group,
2293 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2294 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2295 PRG_GROUP = l_child_group;
2296
2297 end if;
2298
2299 end if;
2300
2301 end if;
2302
2303 elsif (p_operation_type = 'DROP') then
2304
2305 if (l_parent_group is null or
2306 l_parent_level is null or
2307 l_parent_project is null or
2308 l_child_group is null or
2309 l_child_level is null or
2310 l_child_project is null or
2311 p_relationship_type is null or
2312 l_parent_group <> l_child_group) then
2313 rollback to UPDATE_PROGRAM_GROUPS;
2314 return -2;
2315 end if;
2316
2317 -- represent program group hierarchy in the form of a directed graph
2318
2319 insert into PA_PROJ_LEVELS_TMP
2320 (
2321 FROM_ID,
2322 TO_ID,
2323 FROM_LEVEL,
2324 TO_LEVEL,
2325 DIRECTION,
2326 ATTRIBUTE1,
2327 ATTRIBUTE2,
2328 ATTRIBUTE3,
2329 ATTRIBUTE4,
2330 ATTRIBUTE5,
2331 ATTRIBUTE6
2332 )
2333 select /*+ ordered
2334 index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
2335 index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
2336 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
2337 'UP', rel.OBJECT_ID_TO2) FROM_ID,
2338 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
2339 'UP', rel.OBJECT_ID_FROM2) TO_ID,
2340 -1 FROM_LEVEL,
2341 -1 TO_LEVEL,
2342 decode(invert.INVERT_ID, 'DOWN', 'D',
2343 'UP', 'U') DIRECTION,
2344 decode(invert.INVERT_ID,
2345 'DOWN',
2346 decode(rel.OBJECT_ID_FROM2,
2347 l_parent_project,
2348 decode(OBJECT_ID_TO2,
2349 l_child_project,
2350 'DROPPED_ASSOCIATION_DOWN',
2351 'X'),
2352 'X'),
2353 'UP',
2354 decode(rel.OBJECT_ID_FROM2,
2355 l_parent_project,
2356 decode(OBJECT_ID_TO2,
2357 l_child_project,
2358 'DROPPED_ASSOCIATION_UP',
2359 'X'),
2360 'X'),
2361 'X') ATTRIBUTE1,
2362 count(*) ATTRIBUTE2,
2363 'X' ATTRIBUTE3,
2364 null ATTRIBUTE4,
2365 null ATTRIBUTE5,
2366 null ATTRIBUTE6
2367 from
2368 PA_PROJ_ELEMENT_VERSIONS ver,
2369 PA_OBJECT_RELATIONSHIPS rel,
2370 (
2371 select 'DOWN' INVERT_ID from dual union all
2372 select 'UP' INVERT_ID from dual
2373 ) invert
2374 where
2375 ver.OBJECT_TYPE = 'PA_TASKS' and
2376 ver.PRG_GROUP = l_parent_group and
2377 rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
2378 rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
2379 rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
2380 rel.RELATIONSHIP_TYPE in ('LF', 'LW')
2381 group by
2382 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
2383 'UP', rel.OBJECT_ID_TO2),
2384 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
2385 'UP', rel.OBJECT_ID_FROM2),
2386 decode(invert.INVERT_ID, 'DOWN', 'D',
2387 'UP', 'U'),
2388 decode(invert.INVERT_ID,
2389 'DOWN',
2390 decode(rel.OBJECT_ID_FROM2,
2391 l_parent_project,
2392 decode(OBJECT_ID_TO2,
2393 l_child_project,
2394 'DROPPED_ASSOCIATION_DOWN',
2395 'X'),
2396 'X'),
2397 'UP',
2398 decode(rel.OBJECT_ID_FROM2,
2399 l_parent_project,
2400 decode(OBJECT_ID_TO2,
2401 l_child_project,
2402 'DROPPED_ASSOCIATION_UP',
2403 'X'),
2404 'X'),
2405 'X');
2406
2407 update PA_PROJ_ELEMENT_VERSIONS
2408 set PRG_GROUP = decode(PRG_COUNT, 1, null, PRG_GROUP),
2409 PRG_LEVEL = decode(PRG_COUNT, 1, null, PRG_LEVEL),
2410 PRG_COUNT = decode(PRG_COUNT, 1, null, PRG_COUNT - 1)
2411 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2412 l_actual_task_version_id) and
2413 OBJECT_TYPE = 'PA_TASKS';
2414
2415 select
2416 tmp.ATTRIBUTE2
2417 into
2418 l_count
2419 from
2420 PA_PROJ_LEVELS_TMP tmp
2421 where
2422 tmp.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN';
2423
2424 if (l_count = 1) then
2425
2426 -- check whether or not removing this association divides the group
2427
2428 update
2429 PA_PROJ_LEVELS_TMP tmp2
2430 set
2431 tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
2432 where
2433 tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
2434 'DROPPED_ASSOCIATION_UP') and
2435 tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
2436 ( TMP2.FROM_ID in (select TMP1.TO_ID from PA_PROJ_LEVELS_TMP TMP1 WHERE TMP1.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN')
2437 or
2438 TMP2.TO_ID in (select TMP1.TO_ID from PA_PROJ_LEVELS_TMP TMP1 WHERE TMP1.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN')
2439 );
2440 /* commented for bug 6778370
2441 exists
2442 (
2443 select
2444 1
2445 from
2446 PA_PROJ_LEVELS_TMP tmp1
2447 where
2448 tmp1.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' and
2449 (tmp2.FROM_ID = tmp1.TO_ID or
2450 tmp2.TO_ID = tmp1.TO_ID)
2451 ); */
2452
2453 while (sql%rowcount > 0) loop
2454
2455 update
2456 PA_PROJ_LEVELS_TMP tmp2
2457 set
2458 tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
2459 where
2460 tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
2461 'DROPPED_ASSOCIATION_UP') and
2462 tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
2463 ( tmp2.FROM_ID in (select tmp1.TO_ID from PA_PROJ_LEVELS_TMP tmp1 where tmp1.ATTRIBUTE3 = 'CHILD_SUBGROUP')
2464 or
2465 tmp2.TO_ID in (select tmp1.TO_ID from PA_PROJ_LEVELS_TMP tmp1 where tmp1.ATTRIBUTE3 = 'CHILD_SUBGROUP')
2466 );
2467 /* commented for bug 6778370
2468 exists
2469 (
2470 select
2471 1
2472 from
2473 PA_PROJ_LEVELS_TMP tmp1
2474 where
2475 tmp1.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
2476 (tmp2.FROM_ID = tmp1.TO_ID or
2477 tmp2.TO_ID = tmp1.TO_ID)
2478 ); */
2479
2480 end loop;
2481
2482 select
2483 count(*)
2484 into
2485 l_count
2486 from
2487 PA_PROJ_LEVELS_TMP tmp
2488 where
2489 tmp.TO_ID = l_parent_project and
2490 tmp.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
2491 ROWNUM = 1;
2492
2493 if (l_count > 0) then
2494
2495 -- This is the last association between the two projects but the
2496 -- group will not be divided into two sub-groups.
2497
2498 if (l_parent_level = l_child_level - 1) then
2499
2500 --Bug 6778370
2501 -- adjust hierarchy levels
2502 l_hier_count := 0 ;
2503 select /*+ NO_USE_NL(tmp, tmp1) */ count(*)
2504 into l_hier_count
2505 from PA_PROJ_LEVELS_TMP tmp
2506 where tmp.TO_ID in
2507 (select tmp1.from_id
2508 from PA_PROJ_LEVELS_TMP tmp1
2509 where tmp1.DIRECTION = 'U' and
2510 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
2511 )
2512 and tmp.DIRECTION = 'U'
2513 and tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
2514 and rownum = 1;
2515
2516 If l_hier_count >0 then
2517
2518
2519 update
2520 PA_PROJ_LEVELS_TMP tmp4
2521 set
2522 tmp4.FROM_LEVEL = 1
2523 where
2524 tmp4.FROM_LEVEL <> 1 and
2525 tmp4.FROM_ID =
2526 (
2527 select
2528 tmp3.PROJECT_ID
2529 from
2530 (
2531 select
2532 tmp2.PROJECT_ID,
2533 tmp2.PROJECT_LEVEL
2534 from
2535 (
2536 select
2537 distinct
2538 tmp1.TO_ID PROJECT_ID,
2539 LEVEL PROJECT_LEVEL
2540 from
2541 PA_PROJ_LEVELS_TMP tmp1
2542 start with
2543 tmp1.DIRECTION = 'U' and
2544 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
2545 and exists /* bug 6778370 */
2546 (
2547 select 1 from
2548 PA_PROJ_LEVELS_TMP tmp5
2549 where tmp1.to_id = tmp5.from_id and
2550 tmp5.DIRECTION = 'U' and
2551 tmp5.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
2552 )
2553 connect by
2554 tmp1.DIRECTION = 'U' and
2555 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2556 tmp1.FROM_ID = prior tmp1.TO_ID and
2557 tmp1.TO_ID <> prior tmp1.FROM_ID
2558 ) tmp2
2559 order by
2560 tmp2.PROJECT_LEVEL desc
2561 ) tmp3
2562 where
2563 ROWNUM = 1
2564 );
2565
2566 else /* bug 6778370 */
2567 update
2568 PA_PROJ_LEVELS_TMP tmp4
2569 set
2570 tmp4.FROM_LEVEL = 1
2571 where
2572 tmp4.FROM_LEVEL <> 1 and
2573 tmp4.FROM_ID = ( select tmp1.to_id from
2574 PA_PROJ_LEVELS_TMP tmp1
2575 where tmp1.DIRECTION = 'U' and
2576 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2577 rownum = 1
2578 );
2579
2580 end if;
2581
2582 l_count := sql%rowcount;
2583
2584 update
2585 PA_PROJ_LEVELS_TMP
2586 set
2587 TO_LEVEL = 1
2588 where
2589 TO_LEVEL <> 1 and
2590 TO_ID in
2591 (
2592 select
2593 tmp1.FROM_ID
2594 from
2595 PA_PROJ_LEVELS_TMP tmp1
2596 where
2597 tmp1.FROM_LEVEL = 1
2598 );
2599
2600 while (l_count > 0) loop
2601
2602 l_count := 0;
2603
2604 update
2605 PA_PROJ_LEVELS_TMP tmp
2606 set
2607 tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
2608 where
2609 tmp.FROM_LEVEL <> -1 and
2610 tmp.TO_LEVEL <> -1 and
2611 tmp.DIRECTION = 'D' and
2612 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2613 tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
2614
2615 l_count := l_count + sql%rowcount;
2616
2617 update
2618 PA_PROJ_LEVELS_TMP tmp
2619 set
2620 tmp.TO_LEVEL = decode(tmp.DIRECTION,
2621 'U', tmp.FROM_LEVEL - 1,
2622 'D', tmp.FROM_LEVEL + 1)
2623 where
2624 tmp.FROM_LEVEL <> -1 and
2625 tmp.TO_LEVEL = -1 and
2626 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2627 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN';
2628
2629 l_count := l_count + sql%rowcount;
2630
2631 update
2632 PA_PROJ_LEVELS_TMP tmp2
2633 set
2634 tmp2.TO_LEVEL =
2635 nvl((
2636 select
2637 max(tmp1.TO_LEVEL)
2638 from
2639 PA_PROJ_LEVELS_TMP tmp1
2640 where
2641 tmp1.TO_ID = tmp2.TO_ID
2642 ),tmp2.TO_LEVEL)
2643 where
2644 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2645 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2646 tmp2.TO_LEVEL is not null;
2647 /* tmp2.TO_LEVEL <>
2648 (
2649 select
2650 max(tmp1.TO_LEVEL)
2651 from
2652 PA_PROJ_LEVELS_TMP tmp1
2653 where
2654 tmp1.TO_ID = tmp2.TO_ID
2655 ); commented for bug 6778370*/
2656
2657 update
2658 PA_PROJ_LEVELS_TMP tmp2
2659 set
2660 tmp2.FROM_LEVEL =
2661 nvl((
2662 select
2663 tmp1.TO_LEVEL
2664 from
2665 PA_PROJ_LEVELS_TMP tmp1
2666 where
2667 tmp1.TO_ID = tmp2.FROM_ID and
2668 tmp1.TO_LEVEL <> -1 and
2669 ROWNUM = 1
2670 ),tmp2.FROM_LEVEL)
2671 where
2672 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2673 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2674 tmp2.FROM_LEVEL is not null;
2675 /*
2676 tmp2.FROM_LEVEL <>
2677 (
2678 select
2679 tmp1.TO_LEVEL
2680 from
2681 PA_PROJ_LEVELS_TMP tmp1
2682 where
2683 tmp1.TO_ID = tmp2.FROM_ID and
2684 tmp1.TO_LEVEL <> -1 and
2685 ROWNUM = 1
2686 ); commented for bug 6778370 */
2687
2688 end loop;
2689
2690 update
2691 PA_PROJ_ELEMENT_VERSIONS ver
2692 set
2693 ver.PRG_LEVEL =
2694 (
2695 select
2696 tmp.TO_LEVEL
2697 from
2698 PA_PROJ_LEVELS_TMP tmp
2699 where
2700 tmp.TO_ID = ver.PROJECT_ID and
2701 tmp.TO_LEVEL <> -1 and
2702 ROWNUM = 1
2703 )
2704 where
2705 ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2706 ver.PRG_GROUP = l_parent_group and
2707 ver.PROJECT_ID in
2708 (
2709 select
2710 distinct
2711 tmp.TO_ID
2712 from
2713 PA_PROJ_LEVELS_TMP tmp
2714 ) and
2715 ver.PRG_LEVEL <>
2716 (
2717 select
2718 tmp.TO_LEVEL
2719 from
2720 PA_PROJ_LEVELS_TMP tmp
2721 where
2722 tmp.TO_ID = ver.PROJECT_ID and
2723 tmp.TO_LEVEL <> -1 and
2724 ROWNUM = 1
2725 );
2726
2727 end if;
2728
2729 elsif (l_count = 0) then
2730
2731 -- This is the last association between the two projects and the
2732 -- group will be divided into two sub-groups.
2733
2734 select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
2735 into l_child_group
2736 from DUAL;
2737
2738 -- stamp the newly created program group
2739 -- Bug 6778370
2740 begin
2741 select 'Y' into l_subgrp_exist
2742 from dual
2743 where exists ( select 1 from PA_PROJ_LEVELS_TMP tmp
2744 where
2745 ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' or
2746 tmp.ATTRIBUTE3 = 'CHILD_SUBGROUP');
2747 EXCEPTION WHEN NO_DATA_FOUND THEN
2748 l_subgrp_exist := 'N';
2749 end;
2750
2751 -- Bug 6778370
2752 If nvl(l_subgrp_exist,'N') = 'Y' then
2753
2754 update PA_PROJ_ELEMENT_VERSIONS ver
2755 set ver.PRG_GROUP = l_child_group
2756 where ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2757 ver.PRG_GROUP = l_parent_group and
2758 ver.PROJECT_ID in
2759 (
2760 select
2761 tmp.TO_ID
2762 from
2763 PA_PROJ_LEVELS_TMP tmp
2764 where
2765 ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' or
2766 ATTRIBUTE3 = 'CHILD_SUBGROUP'
2767 );
2768 end if;
2769 -- readjust program levels so the new groups have shallowest level 1
2770
2771 select PRG_GROUP,
2772 PRG_LEVEL
2773 into l_program_group,
2774 l_level_adjustment
2775 from (select PRG_GROUP,
2776 PRG_LEVEL - 1 PRG_LEVEL
2777 from (select PRG_GROUP,
2778 min(PRG_LEVEL) PRG_LEVEL
2779 from PA_PROJ_ELEMENT_VERSIONS
2780 where OBJECT_TYPE = 'PA_STRUCTURES' and
2781 PRG_GROUP in (l_parent_group,
2782 l_child_group)
2783 group by PRG_GROUP)
2784 order by PRG_LEVEL desc)
2785 where ROWNUM = 1;
2786
2787 if (l_level_adjustment > 0) then
2788
2789 update PA_PROJ_ELEMENT_VERSIONS
2790 set PRG_LEVEL = PRG_LEVEL - l_level_adjustment
2791 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2792 PRG_GROUP = l_program_group;
2793
2794 end if;
2795
2796 end if;
2797
2798 end if;
2799
2800 end if;
2801
2802 delete from PA_PROJ_LEVELS_TMP;
2803
2804 l_last_update_date := sysdate;
2805 l_last_updated_by := FND_GLOBAL.USER_ID;
2806 l_creation_date := sysdate;
2807 l_created_by := FND_GLOBAL.USER_ID;
2808 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2809
2810 insert into PA_PJI_PROJ_EVENTS_LOG
2811 (
2812 EVENT_TYPE,
2813 EVENT_ID,
2814 EVENT_OBJECT,
2815 OPERATION_TYPE,
2816 STATUS,
2817 ATTRIBUTE1,
2818 LAST_UPDATE_DATE,
2819 LAST_UPDATED_BY,
2820 CREATION_DATE,
2821 CREATED_BY,
2822 LAST_UPDATE_LOGIN
2823 )
2824 values
2825 (
2826 'PRG_CHANGE',
2827 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2828 l_parent_group,
2829 'I',
2830 'X',
2831 l_child_group,
2832 l_last_update_date,
2833 l_last_updated_by,
2834 l_creation_date,
2835 l_created_by,
2836 l_last_update_login
2837 );
2838
2839 if (l_new_assoc_parent is not null) then
2840
2841 insert into PA_PJI_PROJ_EVENTS_LOG
2842 (
2843 EVENT_TYPE,
2844 EVENT_ID,
2845 EVENT_OBJECT,
2846 OPERATION_TYPE,
2847 STATUS,
2848 ATTRIBUTE1,
2849 LAST_UPDATE_DATE,
2850 LAST_UPDATED_BY,
2851 CREATION_DATE,
2852 CREATED_BY,
2853 LAST_UPDATE_LOGIN
2854 )
2855 values
2856 (
2857 'PRG_CHANGE',
2858 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2859 -1,
2860 'I',
2861 'X',
2862 l_new_assoc_parent,
2863 l_last_update_date,
2864 l_last_updated_by,
2865 l_creation_date,
2866 l_created_by,
2867 l_last_update_login
2868 );
2869
2870 end if;
2871
2872 if (l_new_assoc_child is not null) then
2873
2874 insert into PA_PJI_PROJ_EVENTS_LOG
2875 (
2876 EVENT_TYPE,
2877 EVENT_ID,
2878 EVENT_OBJECT,
2879 OPERATION_TYPE,
2880 STATUS,
2881 ATTRIBUTE1,
2882 LAST_UPDATE_DATE,
2883 LAST_UPDATED_BY,
2884 CREATION_DATE,
2885 CREATED_BY,
2886 LAST_UPDATE_LOGIN
2887 )
2888 values
2889 (
2890 'PRG_CHANGE',
2891 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2892 -1,
2893 'I',
2894 'X',
2895 l_new_assoc_child,
2896 l_last_update_date,
2897 l_last_updated_by,
2898 l_creation_date,
2899 l_created_by,
2900 l_last_update_login
2901 );
2902
2903 end if;
2904
2905 return 0;
2906
2907 exception when others then
2908
2909 rollback to UPDATE_PROGRAM_GROUPS;
2910
2911 raise;
2912
2913 end UPDATE_PROGRAM_GROUPS;
2914
2915 end PA_RELATIONSHIP_PUB;