[Home] [Help]
PACKAGE BODY: APPS.PA_RELATIONSHIP_PUB
Source
1 package body PA_RELATIONSHIP_PUB as
2 /*$Header: PAXRELPB.pls 120.6.12010000.4 2008/11/07 05:25:53 rballamu 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 begin
1709
1710 savepoint UPDATE_PROGRAM_GROUPS;
1711
1712 l_parent_group := p_parent_group;
1713 l_parent_level := p_parent_level;
1714 l_parent_project := p_parent_project;
1715 l_child_group := p_child_group;
1716 l_child_level := p_child_level;
1717 l_child_project := p_child_project;
1718
1719 l_new_assoc_parent := null;
1720 l_new_assoc_child := null;
1721
1722 if (l_parent_group is not null) then
1723
1724 update PA_PROJ_ELEMENT_VERSIONS
1725 set PRG_GROUP = l_parent_group,
1726 PRG_LEVEL = l_parent_level
1727 where OBJECT_TYPE = 'PA_STRUCTURES' and
1728 PROJECT_ID = l_parent_project and
1729 (PRG_GROUP is null or PRG_LEVEL is null);
1730
1731 end if;
1732
1733 if (l_child_group is not null) then
1734
1735 update PA_PROJ_ELEMENT_VERSIONS
1736 set PRG_GROUP = l_child_group,
1737 PRG_LEVEL = l_child_level
1738 where OBJECT_TYPE = 'PA_STRUCTURES' and
1739 PROJECT_ID = l_child_project and
1740 (PRG_GROUP is null or PRG_LEVEL is null);
1741
1742 end if;
1743
1744 select OBJECT_ID_FROM1
1745 into l_actual_task_version_id
1746 from PA_OBJECT_RELATIONSHIPS
1747 where OBJECT_TYPE_FROM = 'PA_TASKS' and
1748 OBJECT_TYPE_TO = 'PA_TASKS' and
1749 RELATIONSHIP_TYPE = 'S' and
1750 OBJECT_ID_TO1 = p_parent_task_version_id;
1751
1752 if (p_operation_type = 'ADD') then
1753
1754 if (l_parent_group = l_child_group) then
1755
1756 update PA_PROJ_ELEMENT_VERSIONS
1757 set PRG_GROUP = l_parent_group,
1758 PRG_LEVEL = l_parent_level,
1759 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
1760 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
1761 l_actual_task_version_id) and
1762 OBJECT_TYPE = 'PA_TASKS';
1763
1764 if (l_parent_level < l_child_level) then
1765
1766 -- check if LF or LW link already exists
1767
1768 select PARENT_STRUCTURE_VERSION_ID
1769 into l_parent_structure_version_id
1770 from PA_PROJ_ELEMENT_VERSIONS
1771 where ELEMENT_VERSION_ID = p_parent_task_version_id;
1772
1773 select count(*)
1774 into l_count
1775 from PA_OBJECT_RELATIONSHIPS
1776 where RELATIONSHIP_TYPE = p_relationship_type and
1777 OBJECT_TYPE_FROM = 'PA_TASKS' and
1778 OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1779 OBJECT_ID_FROM2 = l_parent_project and
1780 OBJECT_ID_TO2 = l_child_project and
1781 OBJECT_ID_FROM1 in (select
1782 ver.ELEMENT_VERSION_ID
1783 from
1784 PA_PROJ_ELEMENT_VERSIONS ver
1785 where
1786 ver.PARENT_STRUCTURE_VERSION_ID
1787 = l_parent_structure_version_id) and
1788 OBJECT_ID_TO1 = p_child_structure_version_id and
1789 not(OBJECT_ID_FROM1 = p_parent_task_version_id and
1790 OBJECT_ID_TO1 = p_child_structure_version_id) and
1791 ROWNUM = 1;
1792
1793 if (l_count > 0) then
1794 rollback to UPDATE_PROGRAM_GROUPS;
1795 return -1;
1796 end if;
1797
1798 select count(*)
1799 into l_count
1800 from PA_OBJECT_RELATIONSHIPS
1801 where RELATIONSHIP_TYPE in ('LF', 'LW') and
1802 OBJECT_TYPE_FROM = 'PA_TASKS' and
1803 OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1804 OBJECT_ID_FROM2 = l_parent_project and
1805 OBJECT_ID_TO2 = l_child_project and
1806 not(OBJECT_ID_FROM1 = p_parent_task_version_id and
1807 OBJECT_ID_TO1 = p_child_structure_version_id) and
1808 ROWNUM = 1;
1809
1810 elsif (l_parent_level >= l_child_level) then
1811
1812 l_count := 0;
1813
1814 end if;
1815
1816 if (l_count = 0) then
1817
1818 -- represent program group hierarchy in the form of a directed graph
1819
1820 insert into PA_PROJ_LEVELS_TMP
1821 (
1822 FROM_ID,
1823 TO_ID,
1824 FROM_LEVEL,
1825 TO_LEVEL,
1826 DIRECTION,
1827 ATTRIBUTE1,
1828 ATTRIBUTE2,
1829 ATTRIBUTE3,
1830 ATTRIBUTE4,
1831 ATTRIBUTE5,
1832 ATTRIBUTE6
1833 )
1834 select /*+ ordered
1835 index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
1836 index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
1837 distinct
1838 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
1839 'UP', rel.OBJECT_ID_TO2) FROM_ID,
1840 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
1841 'UP', rel.OBJECT_ID_FROM2) TO_ID,
1842 -1 FROM_LEVEL,
1843 -1 TO_LEVEL,
1844 decode(invert.INVERT_ID, 'DOWN', 'D',
1845 'UP', 'U') DIRECTION,
1846 decode(invert.INVERT_ID,
1847 'DOWN',
1848 decode(rel.OBJECT_ID_FROM2,
1849 l_parent_project,
1850 decode(OBJECT_ID_TO2,
1851 l_child_project,
1852 'NEW_ASSOCIATION_DOWN',
1853 'X'),
1854 'X'),
1855 'UP',
1856 decode(rel.OBJECT_ID_FROM2,
1857 l_parent_project,
1858 decode(OBJECT_ID_TO2,
1859 l_child_project,
1860 'NEW_ASSOCIATION_UP',
1861 'X'),
1862 'X'),
1863 'X') ATTRIBUTE1,
1864 null ATTRIBUTE2,
1865 null ATTRIBUTE3,
1866 null ATTRIBUTE4,
1867 null ATTRIBUTE5,
1868 null ATTRIBUTE6
1869 from
1870 PA_PROJ_ELEMENT_VERSIONS ver,
1871 PA_OBJECT_RELATIONSHIPS rel,
1872 pa_proj_structure_types ppst,
1873 pa_proj_elem_ver_structure ppevs,
1874 pa_projects_all ppa,
1875 (
1876 select 'DOWN' INVERT_ID from dual union all
1877 select 'UP' INVERT_ID from dual
1878 ) invert
1879 where
1880 ver.OBJECT_TYPE = 'PA_TASKS' and
1881 ver.PRG_GROUP = l_parent_group and
1882 ppa.project_id = ver.project_id and
1883 rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
1884 rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
1885 rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1886 rel.RELATIONSHIP_TYPE in ('LF', 'LW')
1887 AND ver.parent_structure_version_id = ppevs.element_version_id
1888 AND ppevs.proj_element_id = ppst.proj_element_id
1889 AND ( (ppst.structure_type_id = 1 and (ppevs.latest_eff_published_flag = 'Y' or ppevs.status_code = 'STRUCTURE_WORKING') )
1890 or ( ppst.structure_type_id = 6 and ppa.structure_sharing_code not in ('SHARE_FULL', 'SHARE_PARTIAL' ) )); -- added last two conditions for bug 7409918
1891
1892
1893 select
1894 count(*)
1895 into
1896 l_count
1897 from
1898 (
1899 select
1900 FROM_ID,
1901 TO_ID
1902 from
1903 PA_PROJ_LEVELS_TMP
1904 group by
1905 FROM_ID,
1906 TO_ID
1907 having
1908 count(*) > 1
1909 );
1910
1911 if (l_count > 0) then
1912 rollback to UPDATE_PROGRAM_GROUPS;
1913 return -1;
1914 end if;
1915
1916 -- check relaxed acyclic rule
1917
1918 begin
1919
1920 for leaf_node in
1921 (
1922 select
1923 distinct
1924 tmp1.TO_ID PROJECT_ID
1925 from
1926 PA_PROJ_LEVELS_TMP tmp1
1927 start with
1928 tmp1.ATTRIBUTE1 = 'NEW_ASSOCIATION_DOWN'
1929 connect by
1930 tmp1.DIRECTION = 'D' and
1931 tmp1.FROM_ID = prior tmp1.TO_ID and
1932 tmp1.TO_ID <> prior tmp1.FROM_ID
1933 minus
1934 select
1935 distinct
1936 tmp2.FROM_ID PROJECT_ID
1937 from
1938 PA_PROJ_LEVELS_TMP tmp2
1939 where
1940 tmp2.DIRECTION = 'D'
1941 ) loop
1942
1943 select
1944 count(*)
1945 into
1946 l_count
1947 from
1948 (
1949 select
1950 tmp3.TO_ID
1951 from
1952 PA_PROJ_LEVELS_TMP tmp3
1953 start with
1954 tmp3.FROM_ID = leaf_node.PROJECT_ID
1955 connect by
1956 tmp3.DIRECTION = 'U' and
1957 tmp3.FROM_ID = prior tmp3.TO_ID and
1958 tmp3.TO_ID <> prior tmp3.FROM_ID
1959 group by
1960 tmp3.TO_ID
1961 having
1962 count(*) > 1
1963 )
1964 where
1965 ROWNUM = 1;
1966
1967 if (l_count > 0) then
1968 rollback to UPDATE_PROGRAM_GROUPS;
1969 return -1;
1970 end if;
1971
1972 end loop;
1973
1974 exception when others then
1975
1976 rollback to UPDATE_PROGRAM_GROUPS;
1977 return -1;
1978
1979 end;
1980
1981 end if;
1982
1983 if (l_parent_level >= l_child_level) then
1984
1985 -- adjust hierarchy levels
1986
1987 update
1988 PA_PROJ_LEVELS_TMP tmp4
1989 set
1990 tmp4.FROM_LEVEL = 1
1991 where
1992 tmp4.FROM_LEVEL <> 1 and
1993 tmp4.FROM_ID in
1994 (
1995 select
1996 tmp3.PROJECT_ID
1997 from
1998 (
1999 select
2000 tmp2.PROJECT_ID,
2001 tmp2.PROJECT_LEVEL
2002 from
2003 (
2004 select
2005 distinct
2006 tmp1.TO_ID PROJECT_ID,
2007 LEVEL PROJECT_LEVEL
2008 from
2009 PA_PROJ_LEVELS_TMP tmp1
2010 start with
2011 tmp1.DIRECTION = 'U'
2012 connect by
2013 tmp1.DIRECTION = 'U' and
2014 tmp1.FROM_ID = prior tmp1.TO_ID and
2015 tmp1.TO_ID <> prior tmp1.FROM_ID
2016 ) tmp2
2017 order by
2018 tmp2.PROJECT_LEVEL desc
2019 ) tmp3
2020 where
2021 ROWNUM = 1
2022 );
2023
2024 l_count := sql%rowcount;
2025
2026 update
2027 PA_PROJ_LEVELS_TMP
2028 set
2029 TO_LEVEL = 1
2030 where
2031 TO_LEVEL <> 1 and
2032 TO_ID in
2033 (
2034 select
2035 tmp1.FROM_ID
2036 from
2037 PA_PROJ_LEVELS_TMP tmp1
2038 where
2039 tmp1.FROM_LEVEL = 1
2040 );
2041
2042 while (l_count > 0) loop
2043
2044 l_count := 0;
2045
2046 update
2047 PA_PROJ_LEVELS_TMP tmp
2048 set
2049 tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
2050 where
2051 tmp.FROM_LEVEL <> -1 and
2052 tmp.TO_LEVEL <> -1 and
2053 tmp.DIRECTION = 'D' and
2054 tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
2055
2056 l_count := l_count + sql%rowcount;
2057
2058 update
2059 PA_PROJ_LEVELS_TMP tmp
2060 set
2061 tmp.TO_LEVEL = decode(tmp.DIRECTION,
2062 'U', tmp.FROM_LEVEL - 1,
2063 'D', tmp.FROM_LEVEL + 1)
2064 where
2065 tmp.FROM_LEVEL <> -1 and
2066 tmp.TO_LEVEL = -1;
2067
2068 l_count := l_count + sql%rowcount;
2069
2070 update
2071 PA_PROJ_LEVELS_TMP tmp2
2072 set
2073 tmp2.TO_LEVEL =
2074 (
2075 select
2076 max(tmp1.TO_LEVEL)
2077 from
2078 PA_PROJ_LEVELS_TMP tmp1
2079 where
2080 tmp1.TO_ID = tmp2.TO_ID
2081 )
2082 where
2083 tmp2.TO_LEVEL <>
2084 (
2085 select
2086 max(tmp1.TO_LEVEL)
2087 from
2088 PA_PROJ_LEVELS_TMP tmp1
2089 where
2090 tmp1.TO_ID = tmp2.TO_ID
2091 );
2092
2093 update
2094 PA_PROJ_LEVELS_TMP tmp2
2095 set
2096 tmp2.FROM_LEVEL =
2097 (
2098 select
2099 tmp1.TO_LEVEL
2100 from
2101 PA_PROJ_LEVELS_TMP tmp1
2102 where
2103 tmp1.TO_ID = tmp2.FROM_ID and
2104 tmp1.TO_LEVEL <> -1 and
2105 ROWNUM = 1
2106 )
2107 where
2108 tmp2.FROM_LEVEL <>
2109 (
2110 select
2111 tmp1.TO_LEVEL
2112 from
2113 PA_PROJ_LEVELS_TMP tmp1
2114 where
2115 tmp1.TO_ID = tmp2.FROM_ID and
2116 tmp1.TO_LEVEL <> -1 and
2117 ROWNUM = 1
2118 );
2119
2120 end loop;
2121
2122 update
2123 PA_PROJ_ELEMENT_VERSIONS ver
2124 set
2125 ver.PRG_LEVEL =
2126 (
2127 select
2128 tmp.TO_LEVEL
2129 from
2130 PA_PROJ_LEVELS_TMP tmp
2131 where
2132 tmp.TO_ID = ver.PROJECT_ID and
2133 tmp.TO_LEVEL <> -1 and
2134 ROWNUM = 1
2135 )
2136 where
2137 ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2138 ver.PRG_GROUP = l_parent_group and
2139 ver.PROJECT_ID in
2140 (
2141 select
2142 distinct
2143 tmp.TO_ID
2144 from
2145 PA_PROJ_LEVELS_TMP tmp
2146 ) and
2147 ver.PRG_LEVEL <>
2148 (
2149 select
2150 tmp.TO_LEVEL
2151 from
2152 PA_PROJ_LEVELS_TMP tmp
2153 where
2154 tmp.TO_ID = ver.PROJECT_ID and
2155 tmp.TO_LEVEL <> -1 and
2156 ROWNUM = 1
2157 );
2158
2159 end if;
2160
2161 else -- l_parent_group <> l_child_group
2162
2163 if (l_parent_group is null) then
2164 l_parent_level := 1;
2165 l_new_assoc_parent := l_parent_project;
2166 end if;
2167
2168 if (l_child_group is null) then
2169 l_child_level := 1;
2170 l_new_assoc_child := l_child_project;
2171 end if;
2172
2173 if (l_parent_level < l_child_level) then
2174
2175 l_level_adjustment := l_child_level - l_parent_level - 1;
2176
2177 update PA_PROJ_ELEMENT_VERSIONS
2178 set PRG_GROUP = l_child_group,
2179 PRG_LEVEL = l_child_level - 1,
2180 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2181 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2182 l_actual_task_version_id) and
2183 OBJECT_TYPE = 'PA_TASKS';
2184
2185 if (l_parent_group is null) then
2186
2187 l_parent_group := l_child_group;
2188
2189 update PA_PROJ_ELEMENT_VERSIONS
2190 set PRG_GROUP = l_child_group,
2191 PRG_LEVEL = l_parent_level + l_level_adjustment
2192 where PROJECT_ID = l_parent_project and
2193 OBJECT_TYPE = 'PA_STRUCTURES';
2194
2195 else
2196
2197 update PA_PROJ_ELEMENT_VERSIONS
2198 set PRG_GROUP = l_child_group,
2199 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2200 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2201 PRG_GROUP = l_parent_group;
2202
2203 end if;
2204
2205 elsif (l_parent_level >= l_child_level) then
2206
2207 l_level_adjustment := l_parent_level - l_child_level + 1;
2208
2209 if (l_parent_group is null and l_child_group is null) then
2210
2211 select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
2212 into l_parent_group
2213 from DUAL;
2214
2215 update PA_PROJ_ELEMENT_VERSIONS
2216 set PRG_GROUP = l_parent_group,
2217 PRG_LEVEL = l_parent_level,
2218 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2219 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2220 l_actual_task_version_id) and
2221 OBJECT_TYPE = 'PA_TASKS';
2222
2223 l_child_group := l_parent_group;
2224
2225 update PA_PROJ_ELEMENT_VERSIONS
2226 set PRG_GROUP = l_parent_group,
2227 PRG_LEVEL = decode(PROJECT_ID, l_parent_project,
2228 l_parent_level,
2229 l_child_level +
2230 l_level_adjustment)
2231 where PROJECT_ID in (l_parent_project,
2232 l_child_project) and
2233 OBJECT_TYPE = 'PA_STRUCTURES';
2234
2235 elsif (l_child_group is null) then
2236
2237 update PA_PROJ_ELEMENT_VERSIONS
2238 set PRG_GROUP = l_parent_group,
2239 PRG_LEVEL = l_parent_level,
2240 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2241 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2242 l_actual_task_version_id) and
2243 OBJECT_TYPE = 'PA_TASKS';
2244
2245 l_child_group := l_parent_group;
2246
2247 update PA_PROJ_ELEMENT_VERSIONS
2248 set PRG_GROUP = l_parent_group,
2249 PRG_LEVEL = l_child_level + l_level_adjustment
2250 where PROJECT_ID = l_child_project and
2251 OBJECT_TYPE = 'PA_STRUCTURES';
2252
2253 elsif (l_parent_group is null) then
2254
2255 update PA_PROJ_ELEMENT_VERSIONS
2256 set PRG_GROUP = l_child_group,
2257 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2258 where PRG_GROUP = l_child_group and
2259 OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS');
2260
2261 update PA_PROJ_ELEMENT_VERSIONS
2262 set PRG_GROUP = l_child_group,
2263 PRG_LEVEL = l_child_level + l_level_adjustment - 1,
2264 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2265 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2266 l_actual_task_version_id) and
2267 OBJECT_TYPE = 'PA_TASKS';
2268
2269 l_parent_group := l_child_group;
2270
2271 update PA_PROJ_ELEMENT_VERSIONS
2272 set PRG_GROUP = l_child_group,
2273 PRG_LEVEL = l_child_level + l_level_adjustment - 1
2274 where PROJECT_ID = l_parent_project and
2275 OBJECT_TYPE = 'PA_STRUCTURES';
2276
2277 else
2278
2279 update PA_PROJ_ELEMENT_VERSIONS
2280 set PRG_GROUP = l_parent_group,
2281 PRG_LEVEL = l_parent_level,
2282 PRG_COUNT = nvl(PRG_COUNT, 0) + 1
2283 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2284 l_actual_task_version_id) and
2285 OBJECT_TYPE = 'PA_TASKS';
2286
2287 update PA_PROJ_ELEMENT_VERSIONS
2288 set PRG_GROUP = l_parent_group,
2289 PRG_LEVEL = PRG_LEVEL + l_level_adjustment
2290 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2291 PRG_GROUP = l_child_group;
2292
2293 end if;
2294
2295 end if;
2296
2297 end if;
2298
2299 elsif (p_operation_type = 'DROP') then
2300
2301 if (l_parent_group is null or
2302 l_parent_level is null or
2303 l_parent_project is null or
2304 l_child_group is null or
2305 l_child_level is null or
2306 l_child_project is null or
2307 p_relationship_type is null or
2308 l_parent_group <> l_child_group) then
2309 rollback to UPDATE_PROGRAM_GROUPS;
2310 return -2;
2311 end if;
2312
2313 -- represent program group hierarchy in the form of a directed graph
2314
2315 insert into PA_PROJ_LEVELS_TMP
2316 (
2317 FROM_ID,
2318 TO_ID,
2319 FROM_LEVEL,
2320 TO_LEVEL,
2321 DIRECTION,
2322 ATTRIBUTE1,
2323 ATTRIBUTE2,
2324 ATTRIBUTE3,
2325 ATTRIBUTE4,
2326 ATTRIBUTE5,
2327 ATTRIBUTE6
2328 )
2329 select /*+ ordered
2330 index(ver, PA_PROJ_ELEMENT_VERSIONS_N5)
2331 index(rel, PA_OBJECT_RELATIONSHIPS_U2) use_nl(rel) */
2332 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
2333 'UP', rel.OBJECT_ID_TO2) FROM_ID,
2334 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
2335 'UP', rel.OBJECT_ID_FROM2) TO_ID,
2336 -1 FROM_LEVEL,
2337 -1 TO_LEVEL,
2338 decode(invert.INVERT_ID, 'DOWN', 'D',
2339 'UP', 'U') DIRECTION,
2340 decode(invert.INVERT_ID,
2341 'DOWN',
2342 decode(rel.OBJECT_ID_FROM2,
2343 l_parent_project,
2344 decode(OBJECT_ID_TO2,
2345 l_child_project,
2346 'DROPPED_ASSOCIATION_DOWN',
2347 'X'),
2348 'X'),
2349 'UP',
2350 decode(rel.OBJECT_ID_FROM2,
2351 l_parent_project,
2352 decode(OBJECT_ID_TO2,
2353 l_child_project,
2354 'DROPPED_ASSOCIATION_UP',
2355 'X'),
2356 'X'),
2357 'X') ATTRIBUTE1,
2358 count(*) ATTRIBUTE2,
2359 'X' ATTRIBUTE3,
2360 null ATTRIBUTE4,
2361 null ATTRIBUTE5,
2362 null ATTRIBUTE6
2363 from
2364 PA_PROJ_ELEMENT_VERSIONS ver,
2365 PA_OBJECT_RELATIONSHIPS rel,
2366 (
2367 select 'DOWN' INVERT_ID from dual union all
2368 select 'UP' INVERT_ID from dual
2369 ) invert
2370 where
2371 ver.OBJECT_TYPE = 'PA_TASKS' and
2372 ver.PRG_GROUP = l_parent_group and
2373 rel.OBJECT_TYPE_FROM = 'PA_TASKS' and
2374 rel.OBJECT_ID_FROM1 = ver.ELEMENT_VERSION_ID and
2375 rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
2376 rel.RELATIONSHIP_TYPE in ('LF', 'LW')
2377 group by
2378 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_FROM2,
2379 'UP', rel.OBJECT_ID_TO2),
2380 decode(invert.INVERT_ID, 'DOWN', rel.OBJECT_ID_TO2,
2381 'UP', rel.OBJECT_ID_FROM2),
2382 decode(invert.INVERT_ID, 'DOWN', 'D',
2383 'UP', 'U'),
2384 decode(invert.INVERT_ID,
2385 'DOWN',
2386 decode(rel.OBJECT_ID_FROM2,
2387 l_parent_project,
2388 decode(OBJECT_ID_TO2,
2389 l_child_project,
2390 'DROPPED_ASSOCIATION_DOWN',
2391 'X'),
2392 'X'),
2393 'UP',
2394 decode(rel.OBJECT_ID_FROM2,
2395 l_parent_project,
2396 decode(OBJECT_ID_TO2,
2397 l_child_project,
2398 'DROPPED_ASSOCIATION_UP',
2399 'X'),
2400 'X'),
2401 'X');
2402
2403 update PA_PROJ_ELEMENT_VERSIONS
2404 set PRG_GROUP = decode(PRG_COUNT, 1, null, PRG_GROUP),
2405 PRG_LEVEL = decode(PRG_COUNT, 1, null, PRG_LEVEL),
2406 PRG_COUNT = decode(PRG_COUNT, 1, null, PRG_COUNT - 1)
2407 where ELEMENT_VERSION_ID in (p_parent_task_version_id,
2408 l_actual_task_version_id) and
2409 OBJECT_TYPE = 'PA_TASKS';
2410
2411 select
2412 tmp.ATTRIBUTE2
2413 into
2414 l_count
2415 from
2416 PA_PROJ_LEVELS_TMP tmp
2417 where
2418 tmp.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN';
2419
2420 if (l_count = 1) then
2421
2422 -- check whether or not removing this association divides the group
2423
2424 update
2425 PA_PROJ_LEVELS_TMP tmp2
2426 set
2427 tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
2428 where
2429 tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
2430 'DROPPED_ASSOCIATION_UP') and
2431 tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
2432 exists
2433 (
2434 select
2435 1
2436 from
2437 PA_PROJ_LEVELS_TMP tmp1
2438 where
2439 tmp1.ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' and
2440 (tmp2.FROM_ID = tmp1.TO_ID or
2441 tmp2.TO_ID = tmp1.TO_ID)
2442 );
2443
2444 while (sql%rowcount > 0) loop
2445
2446 update
2447 PA_PROJ_LEVELS_TMP tmp2
2448 set
2449 tmp2.ATTRIBUTE3 = 'CHILD_SUBGROUP'
2450 where
2451 tmp2.ATTRIBUTE1 not in ('DROPPED_ASSOCIATION_DOWN',
2452 'DROPPED_ASSOCIATION_UP') and
2453 tmp2.ATTRIBUTE3 <> 'CHILD_SUBGROUP' and
2454 exists
2455 (
2456 select
2457 1
2458 from
2459 PA_PROJ_LEVELS_TMP tmp1
2460 where
2461 tmp1.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
2462 (tmp2.FROM_ID = tmp1.TO_ID or
2463 tmp2.TO_ID = tmp1.TO_ID)
2464 );
2465
2466 end loop;
2467
2468 select
2469 count(*)
2470 into
2471 l_count
2472 from
2473 PA_PROJ_LEVELS_TMP tmp
2474 where
2475 tmp.TO_ID = l_parent_project and
2476 tmp.ATTRIBUTE3 = 'CHILD_SUBGROUP' and
2477 ROWNUM = 1;
2478
2479 if (l_count > 0) then
2480
2481 -- This is the last association between the two projects but the
2482 -- group will not be divided into two sub-groups.
2483
2484 if (l_parent_level = l_child_level - 1) then
2485
2486 -- adjust hierarchy levels
2487
2488 update
2489 PA_PROJ_LEVELS_TMP tmp4
2490 set
2491 tmp4.FROM_LEVEL = 1
2492 where
2493 tmp4.FROM_LEVEL <> 1 and
2494 tmp4.FROM_ID in
2495 (
2496 select
2497 tmp3.PROJECT_ID
2498 from
2499 (
2500 select
2501 tmp2.PROJECT_ID,
2502 tmp2.PROJECT_LEVEL
2503 from
2504 (
2505 select
2506 distinct
2507 tmp1.TO_ID PROJECT_ID,
2508 LEVEL PROJECT_LEVEL
2509 from
2510 PA_PROJ_LEVELS_TMP tmp1
2511 start with
2512 tmp1.DIRECTION = 'U' and
2513 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP'
2514 connect by
2515 tmp1.DIRECTION = 'U' and
2516 tmp1.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2517 tmp1.FROM_ID = prior tmp1.TO_ID and
2518 tmp1.TO_ID <> prior tmp1.FROM_ID
2519 ) tmp2
2520 order by
2521 tmp2.PROJECT_LEVEL desc
2522 ) tmp3
2523 where
2524 ROWNUM = 1
2525 );
2526
2527 l_count := sql%rowcount;
2528
2529 update
2530 PA_PROJ_LEVELS_TMP
2531 set
2532 TO_LEVEL = 1
2533 where
2534 TO_LEVEL <> 1 and
2535 TO_ID in
2536 (
2537 select
2538 tmp1.FROM_ID
2539 from
2540 PA_PROJ_LEVELS_TMP tmp1
2541 where
2542 tmp1.FROM_LEVEL = 1
2543 );
2544
2545 while (l_count > 0) loop
2546
2547 l_count := 0;
2548
2549 update
2550 PA_PROJ_LEVELS_TMP tmp
2551 set
2552 tmp.TO_LEVEL = tmp.FROM_LEVEL + 1
2553 where
2554 tmp.FROM_LEVEL <> -1 and
2555 tmp.TO_LEVEL <> -1 and
2556 tmp.DIRECTION = 'D' and
2557 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2558 tmp.FROM_LEVEL + 1 > tmp.TO_LEVEL;
2559
2560 l_count := l_count + sql%rowcount;
2561
2562 update
2563 PA_PROJ_LEVELS_TMP tmp
2564 set
2565 tmp.TO_LEVEL = decode(tmp.DIRECTION,
2566 'U', tmp.FROM_LEVEL - 1,
2567 'D', tmp.FROM_LEVEL + 1)
2568 where
2569 tmp.FROM_LEVEL <> -1 and
2570 tmp.TO_LEVEL = -1 and
2571 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2572 tmp.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN';
2573
2574 l_count := l_count + sql%rowcount;
2575
2576 update
2577 PA_PROJ_LEVELS_TMP tmp2
2578 set
2579 tmp2.TO_LEVEL =
2580 (
2581 select
2582 max(tmp1.TO_LEVEL)
2583 from
2584 PA_PROJ_LEVELS_TMP tmp1
2585 where
2586 tmp1.TO_ID = tmp2.TO_ID
2587 )
2588 where
2589 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2590 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2591 tmp2.TO_LEVEL <>
2592 (
2593 select
2594 max(tmp1.TO_LEVEL)
2595 from
2596 PA_PROJ_LEVELS_TMP tmp1
2597 where
2598 tmp1.TO_ID = tmp2.TO_ID
2599 );
2600
2601 update
2602 PA_PROJ_LEVELS_TMP tmp2
2603 set
2604 tmp2.FROM_LEVEL =
2605 (
2606 select
2607 tmp1.TO_LEVEL
2608 from
2609 PA_PROJ_LEVELS_TMP tmp1
2610 where
2611 tmp1.TO_ID = tmp2.FROM_ID and
2612 tmp1.TO_LEVEL <> -1 and
2613 ROWNUM = 1
2614 )
2615 where
2616 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_UP' and
2617 tmp2.ATTRIBUTE1 <> 'DROPPED_ASSOCIATION_DOWN' and
2618 tmp2.FROM_LEVEL <>
2619 (
2620 select
2621 tmp1.TO_LEVEL
2622 from
2623 PA_PROJ_LEVELS_TMP tmp1
2624 where
2625 tmp1.TO_ID = tmp2.FROM_ID and
2626 tmp1.TO_LEVEL <> -1 and
2627 ROWNUM = 1
2628 );
2629
2630 end loop;
2631
2632 update
2633 PA_PROJ_ELEMENT_VERSIONS ver
2634 set
2635 ver.PRG_LEVEL =
2636 (
2637 select
2638 tmp.TO_LEVEL
2639 from
2640 PA_PROJ_LEVELS_TMP tmp
2641 where
2642 tmp.TO_ID = ver.PROJECT_ID and
2643 tmp.TO_LEVEL <> -1 and
2644 ROWNUM = 1
2645 )
2646 where
2647 ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2648 ver.PRG_GROUP = l_parent_group and
2649 ver.PROJECT_ID in
2650 (
2651 select
2652 distinct
2653 tmp.TO_ID
2654 from
2655 PA_PROJ_LEVELS_TMP tmp
2656 ) and
2657 ver.PRG_LEVEL <>
2658 (
2659 select
2660 tmp.TO_LEVEL
2661 from
2662 PA_PROJ_LEVELS_TMP tmp
2663 where
2664 tmp.TO_ID = ver.PROJECT_ID and
2665 tmp.TO_LEVEL <> -1 and
2666 ROWNUM = 1
2667 );
2668
2669 end if;
2670
2671 elsif (l_count = 0) then
2672
2673 -- This is the last association between the two projects and the
2674 -- group will be divided into two sub-groups.
2675
2676 select PA_PROJ_ELEMENT_VERSIONS_S1.NEXTVAL
2677 into l_child_group
2678 from DUAL;
2679
2680 -- stamp the newly created program group
2681
2682 update PA_PROJ_ELEMENT_VERSIONS ver
2683 set ver.PRG_GROUP = l_child_group
2684 where ver.OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2685 ver.PRG_GROUP = l_parent_group and
2686 ver.PROJECT_ID in
2687 (
2688 select
2689 tmp.TO_ID
2690 from
2691 PA_PROJ_LEVELS_TMP tmp
2692 where
2693 ATTRIBUTE1 = 'DROPPED_ASSOCIATION_DOWN' or
2694 ATTRIBUTE3 = 'CHILD_SUBGROUP'
2695 );
2696
2697 -- readjust program levels so the new groups have shallowest level 1
2698
2699 select PRG_GROUP,
2700 PRG_LEVEL
2701 into l_program_group,
2702 l_level_adjustment
2703 from (select PRG_GROUP,
2704 PRG_LEVEL - 1 PRG_LEVEL
2705 from (select PRG_GROUP,
2706 min(PRG_LEVEL) PRG_LEVEL
2707 from PA_PROJ_ELEMENT_VERSIONS
2708 where OBJECT_TYPE = 'PA_STRUCTURES' and
2709 PRG_GROUP in (l_parent_group,
2710 l_child_group)
2711 group by PRG_GROUP)
2712 order by PRG_LEVEL desc)
2713 where ROWNUM = 1;
2714
2715 if (l_level_adjustment > 0) then
2716
2717 update PA_PROJ_ELEMENT_VERSIONS
2718 set PRG_LEVEL = PRG_LEVEL - l_level_adjustment
2719 where OBJECT_TYPE in ('PA_STRUCTURES', 'PA_TASKS') and
2720 PRG_GROUP = l_program_group;
2721
2722 end if;
2723
2724 end if;
2725
2726 end if;
2727
2728 end if;
2729
2730 delete from PA_PROJ_LEVELS_TMP;
2731
2732 l_last_update_date := sysdate;
2733 l_last_updated_by := FND_GLOBAL.USER_ID;
2734 l_creation_date := sysdate;
2735 l_created_by := FND_GLOBAL.USER_ID;
2736 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2737
2738 insert into PA_PJI_PROJ_EVENTS_LOG
2739 (
2740 EVENT_TYPE,
2741 EVENT_ID,
2742 EVENT_OBJECT,
2743 OPERATION_TYPE,
2744 STATUS,
2745 ATTRIBUTE1,
2746 LAST_UPDATE_DATE,
2747 LAST_UPDATED_BY,
2748 CREATION_DATE,
2749 CREATED_BY,
2750 LAST_UPDATE_LOGIN
2751 )
2752 values
2753 (
2754 'PRG_CHANGE',
2755 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2756 l_parent_group,
2757 'I',
2758 'X',
2759 l_child_group,
2760 l_last_update_date,
2761 l_last_updated_by,
2762 l_creation_date,
2763 l_created_by,
2764 l_last_update_login
2765 );
2766
2767 if (l_new_assoc_parent is not null) then
2768
2769 insert into PA_PJI_PROJ_EVENTS_LOG
2770 (
2771 EVENT_TYPE,
2772 EVENT_ID,
2773 EVENT_OBJECT,
2774 OPERATION_TYPE,
2775 STATUS,
2776 ATTRIBUTE1,
2777 LAST_UPDATE_DATE,
2778 LAST_UPDATED_BY,
2779 CREATION_DATE,
2780 CREATED_BY,
2781 LAST_UPDATE_LOGIN
2782 )
2783 values
2784 (
2785 'PRG_CHANGE',
2786 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2787 -1,
2788 'I',
2789 'X',
2790 l_new_assoc_parent,
2791 l_last_update_date,
2792 l_last_updated_by,
2793 l_creation_date,
2794 l_created_by,
2795 l_last_update_login
2796 );
2797
2798 end if;
2799
2800 if (l_new_assoc_child is not null) then
2801
2802 insert into PA_PJI_PROJ_EVENTS_LOG
2803 (
2804 EVENT_TYPE,
2805 EVENT_ID,
2806 EVENT_OBJECT,
2807 OPERATION_TYPE,
2808 STATUS,
2809 ATTRIBUTE1,
2810 LAST_UPDATE_DATE,
2811 LAST_UPDATED_BY,
2812 CREATION_DATE,
2813 CREATED_BY,
2814 LAST_UPDATE_LOGIN
2815 )
2816 values
2817 (
2818 'PRG_CHANGE',
2819 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
2820 -1,
2821 'I',
2822 'X',
2823 l_new_assoc_child,
2824 l_last_update_date,
2825 l_last_updated_by,
2826 l_creation_date,
2827 l_created_by,
2828 l_last_update_login
2829 );
2830
2831 end if;
2832
2833 return 0;
2834
2835 exception when others then
2836
2837 rollback to UPDATE_PROGRAM_GROUPS;
2838
2839 raise;
2840
2841 end UPDATE_PROGRAM_GROUPS;
2842
2843 end PA_RELATIONSHIP_PUB;