[Home] [Help]
PACKAGE BODY: APPS.PA_RELATIONSHIP_PVT
Source
1 package body PA_RELATIONSHIP_PVT as
2 /*$Header: PAXRELVB.pls 120.20 2011/04/14 07:43:59 bpottipa ship $*/
3
4 -- API name : Create_Relationship
5 -- Type : Private Procedure
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Parameters
9 -- p_api_version IN NUMBER := 1.0
10 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
11 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
12 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
13 -- p_validation_level IN VARCHAR2 := 100
14 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
15 -- p_debug_mode IN VARCHAR2 := 'N'
16 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
17 -- p_project_id_from IN NUMBER
18 -- p_structure_id_from IN NUMBER
19 -- p_structure_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
20 -- p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
21 -- p_project_id_to IN NUMBER
22 -- p_structure_id_to IN NUMBER
23 -- p_structure_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
24 -- p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
25 -- p_structure_type IN VARCHAR2
26 -- p_initiating_element IN VARCHAR2
27 -- p_link_to_latest_structure_ver IN VARCHAR2 := 'N'
28 -- p_relationship_type IN VARCHAR2
29 -- p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
30 -- p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
31 -- p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
32 -- x_object_relationship_id OUT NUMBER
33 -- x_return_status OUT VARCHAR2
34 -- x_msg_count OUT NUMBER
35 -- x_msg_data OUT VARCHAR2
36 --
37 -- History
38 --
39 -- 25-JUN-01 HSIU -Created
40 --
41 --
42
43
44 procedure Create_Relationship
45 (
46 p_api_version IN NUMBER := 1.0
47 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
48 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
49 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
50 ,p_validation_level IN VARCHAR2 := 100
51 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
52 ,p_debug_mode IN VARCHAR2 := 'N'
53 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
54 ,p_project_id_from IN NUMBER
55 ,p_structure_id_from IN NUMBER
56 ,p_structure_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
57 ,p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
58 ,p_project_id_to IN NUMBER
59 ,p_structure_id_to IN NUMBER
60 ,p_structure_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
61 ,p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
62 ,p_structure_type IN VARCHAR2
63 ,p_initiating_element IN VARCHAR2
64 ,p_link_to_latest_structure_ver IN VARCHAR2 := 'N'
65 ,p_relationship_type IN VARCHAR2
66 ,p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
67 ,p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
68 ,p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
69 ,p_weighting_percentage IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
70 ,x_object_relationship_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
71 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
72 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
73 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
74 )
75 IS
76 l_id_from pa_object_relationships.object_id_from1%TYPE;
77 l_id_to pa_object_relationships.object_id_to1%TYPE;
78 l_type_from pa_object_relationships.object_type_from%TYPE;
79 l_type_to pa_object_relationships.object_type_to%TYPE;
80 l_weighting_percentage pa_object_relationships.weighting_percentage%TYPE;
81
82 l_dummy varchar2(1);
83 l_msg_count NUMBER;
84 l_msg_data VARCHAR2(250);
85 l_return_status varchar2(1);
86 l_error_message_code varchar2(250);
87 l_data VARCHAR2(2000);
88 l_msg_index_out NUMBER;
89
90 CURSOR Is_Struc_Type_Valid(c_struc_type VARCHAR2, c_struc_id NUMBER) IS
91 select '1'
92 from pa_proj_structure_types s,
93 pa_structure_types t
94 where s.proj_element_id = c_struc_id
95 and s.structure_type_id = t.structure_type_id
96 and t.structure_type_class_code = c_struc_type;
97
98 CURSOR Get_Element_Id(c_elem_ver_id NUMBER) IS
99 select proj_element_id, object_type
100 from pa_proj_element_versions
101 where element_version_id = c_elem_ver_id;
102
103 CURSOR Get_Parent_Struc_Ver_Id(c_elem_ver_id NUMBER) IS
104 select parent_structure_Version_id
105 from pa_proj_element_versions
106 where element_version_id = c_elem_ver_id;
107
108 CURSOR Get_Top_Task_ID(c_project_id NUMBER, c_structure_id NUMBER) IS
109 select pev.proj_element_id
110 from pa_proj_element_versions pev,
111 pa_proj_element_versions pev2,
112 pa_object_relationships rel
113 where pev2.project_id = c_project_id
114 and pev2.object_type = 'PA_STRUCTURES'
115 and pev2.proj_element_id = c_structure_id
116 and pev2.element_version_id = rel.object_id_from1
117 and rel.relationship_type = 'S'
118 and rel.object_id_to1 = pev.element_version_id;
119
120 CURSOR Get_Latest_Pub_Ver(c_struc_type VARCHAR2, c_project_id NUMBER) IS
121 select pevs.element_version_id
122 from pa_proj_structure_types s,
123 pa_structure_types t,
124 pa_proj_elements pe,
125 pa_proj_elem_ver_structure pevs
126 where pe.object_type = 'PA_STRUCTURES'
127 and pe.project_id = c_project_id
128 and pe.proj_element_id = s.proj_element_id
129 and s.structure_type_id = t.structure_type_id
130 and t.structure_type_class_code = c_struc_type
131 and c_project_id = pevs.project_id
132 and pe.proj_element_id = pevs.proj_element_id
133 and pevs.latest_eff_published_flag = 'Y';
134
135 CURSOR Get_Scheduled_Dates(c_element_version_id NUMBER) IS
136 select a.scheduled_start_date, a.scheduled_finish_date
137 from pa_proj_elem_ver_schedule a, pa_proj_element_versions b
138 where b.element_version_id = c_element_version_id
139 and a.project_id = b.project_id
140 and a.element_version_id = b.element_version_id;
141
142 l_scheduled_start_date DATE;
143 l_scheduled_finish_date DATE;
144
145 l_lastest_pub_ver_id NUMBER;
146 l_structure_id NUMBER;
147 l_parent_struc_ver_id NUMBER;
148 l_task_id NUMBER;
149 l_task_version_id NUMBER;
150 l_pev_schedule_id NUMBER;
151 l_task_name_number VARCHAR2(240);
152 l_peer_or_sub VARCHAR2(30);
153
154 l_object_type VARCHAR2(30);
155 l_element_id NUMBER;
156
157 -- Bug 2955589. Local variables introduced to handle miss char and miss num.
158 l_lag_day pa_object_relationships.lag_day%TYPE;
159 l_priority pa_object_relationships.priority%TYPE;
160
161 BEGIN
162 IF (p_debug_mode = 'Y') THEN
163 pa_debug.debug('PA_RELATIONSHIP_PVT.CREATE_RELATIONSHIP begin');
164 END IF;
165
166 IF (p_commit = FND_API.G_TRUE) THEN
167 savepoint create_relationship_pvt;
168 END IF;
169
170 IF (p_debug_mode = 'Y') THEN
171 pa_debug.debug('Performing validations');
172 END IF;
173
174 --Bug 2955589. Handle miss char for priority.
175 IF p_priority = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR THEN
176 l_priority := NULL;
177 ELSE
178 l_priority := p_priority;
179 END IF;
180
181 --Bug 2955589. Handle miss num for lag_day.
182 IF p_lag_day = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
183 l_lag_day := NULL;
184 ELSE
185 l_lag_day := p_lag_day;
186 END IF;
187
188 --Determine the relationship type
189 IF p_relationship_type = 'L' THEN
190 IF (p_debug_mode = 'Y') THEN
191 pa_debug.debug('creating link relationship');
192 END IF;
193
194 --Check if this is a parent link or child link
195 IF (p_initiating_element = 'FROM') THEN
196 --It is a child link
197
198 IF (p_debug_mode = 'Y') THEN
199 pa_debug.debug('child link');
200 END IF;
201
202 --Determine parent element id
203 IF (p_task_version_id_from IS NULL) THEN
204 l_id_from := p_structure_version_id_from;
205 l_type_from := 'PA_STRUCTURES';
206 ELSE
207 l_id_from := p_task_version_id_from;
208 l_type_from := 'PA_TASKS';
209 END IF;
210 --set structure id for creating task.
211 --dbms_output.put_line('p_structure_id_from = '||p_structure_id_from);
212 l_structure_id := p_structure_id_from;
213
214
215 --Determine child element id
216 IF (p_link_to_latest_structure_ver = 'Y') THEN
217 --Find latest published version for the structure.
218 -- Error if none exist.
219 --dbms_output.put_line('getting latest pub version'||p_structure_type||', '||p_project_id_to);
220 OPEN Get_latest_Pub_Ver(p_structure_type, p_project_id_to);
221 FETCH Get_latest_Pub_Ver into l_lastest_pub_ver_id ;
222 --dbms_output.put_line('struc ver id='||l_lastest_pub_ver_id||', struc id = '||l_structure_id);
223 IF Get_latest_Pub_Ver%NOTFOUND THEN
224 CLOSE Get_latest_Pub_Ver;
225 --dbms_output.put_line('no latest pub version, error');
226 PA_UTILS.ADD_MESSAGE('PA','PA_PS_NO_PUB_VER_EXIST');
227 x_msg_data := 'PA_PS_NO_PUB_VER_EXIST';
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230 l_id_to := l_lastest_pub_ver_id;
231 l_type_to := 'PA_STRUCTURES';
232 CLOSE Get_latest_Pub_Ver;
233
234 ELSE
235 --Check if user entered Structure Name and Structure Version Name
236 If (p_structure_id_to = NULL) THEN
237 PA_UTILS.ADD_MESSAGE('PA','PA_PS_STRUC_NAME_REQ');
238 x_msg_data := 'PA_PS_STRUC_NAME_REQ';
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241
242 --Check if selected structure type matches the structure
243 OPEN Is_Struc_Type_Valid(p_structure_type, p_structure_id_to);
244 FETCH Is_Struc_Type_Valid into l_dummy;
245 IF Is_Struc_Type_Valid%NOTFOUND THEN
246 CLOSE Is_Struc_Type_Valid;
247 PA_UTILS.ADD_MESSAGE('PA','PA_PS_STRUC_TYPE_ID_ERR');
248 x_msg_data := 'PA_PS_STRUC_TYPE_ID_ERR';
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251 CLOSE Is_Struc_Type_Valid;
252
253 IF (p_structure_version_id_to = NULL) THEN
254 PA_UTILS.ADD_MESSAGE('PA','PA_PS_STRUC_VER_NAME_REQ');
255 x_msg_data := 'PA_PS_STRUC_VER_NAME_REQ';
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258
259 IF (p_task_version_id_to IS NULL) THEN
260 l_id_to := p_structure_version_id_to;
261 l_type_to := 'PA_STRUCTURES';
262 ELSE
263 l_id_to := p_task_version_id_to;
264 l_type_to := 'PA_TASKS';
265 END IF;
266
267 END IF;
268
269 ELSE
270 --It is a parent link
271 If (p_debug_mode = 'Y') THEN
272 pa_debug.debug('parent link');
273 END IF;
274
275 --Determine child element id
276 IF (p_task_version_id_to IS NULL) THEN
277 l_id_to := p_structure_version_id_to;
278 l_type_to := 'PA_STRUCTURES';
279 ELSE
280 l_id_to := p_task_version_id_to;
281 l_type_to := 'PA_TASKS';
282 END IF;
283 --set structure id for creating task.
284 l_structure_id := p_structure_id_from;
285
286 --Determine child element id
287 --Check if selected structure type matches the structure
288 OPEN Is_Struc_Type_Valid(p_structure_type, p_structure_id_from);
289 FETCH Is_Struc_Type_Valid into l_dummy;
290 IF Is_Struc_Type_Valid%NOTFOUND THEN
291 CLOSE Is_Struc_Type_Valid;
292 PA_UTILS.ADD_MESSAGE('PA','PA_PS_STRUC_TYPE_ID_ERR');
293 x_msg_data := 'PA_PS_STRUC_TYPE_ID_ERR';
294 RAISE FND_API.G_EXC_ERROR;
295 END IF;
296 CLOSE Is_Struc_Type_Valid;
297
298 --Set the from id
299 IF (p_task_version_id_from IS NULL) THEN
300 l_id_from := p_structure_version_id_from;
301 l_type_from := 'PA_STRUCTURES';
302 ELSE
303 l_id_from := p_task_version_id_from;
304 l_type_from := 'PA_TASKS';
305 END IF;
306
307 END IF;
308
309 --dbms_output.put_line('create_relationship pvt'||l_id_from);
310
311 --Check create link ok
312 PA_RELATIONSHIP_UTILS.Check_Create_Link_Ok(l_id_from
313 ,l_id_to
314 ,l_return_status
315 ,l_error_message_code);
316
317 --dbms_output.put_line('check create linke done, return '||l_return_status);
318 --Modified. When creating links, always create a subtask
319 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
320
321 IF (p_debug_mode = 'Y') THEN
322 pa_debug.debug('need to create new sub task');
323 END IF;
324 l_task_name_number := substr(fnd_date.date_to_canonical(sysdate),0,25);
325
326
327 --get scheduled dates from the linked task/structure
328 OPEN get_scheduled_dates(l_id_to);
329 FETCH get_scheduled_dates into l_scheduled_start_date, l_scheduled_finish_date;
330 IF get_scheduled_dates%NOTFOUND THEN
331 l_scheduled_start_date := sysdate;
332 l_scheduled_finish_date := sysdate;
333 END IF;
334 CLOSE get_scheduled_dates;
335
336 --get parent task info
337 l_peer_or_sub := 'SUB';
338 --dbms_output.put_line('id from = '||l_id_from);
339 OPEN Get_Element_Id(l_id_from);
340 FETCH Get_Element_Id INTO l_element_id, l_object_type;
341 IF Get_Element_Id%NOTFOUND THEN
342 l_element_id := NULL;
343 ELSE
344 --dbms_output.put_line('ref is a structure; project_id = '||p_project_id_from||', struc id = '||l_structure_id);
345 IF l_object_type = 'PA_STRUCTURES' THEN
346 l_peer_or_sub := 'PEER';
347 --If Structure has task, need to use peer and select a task;
348 OPEN Get_Top_Task_Id(p_project_id_from, l_structure_id);
349 FETCH Get_Top_Task_Id into l_element_id;
350 IF Get_Top_Task_Id%NOTFOUND THEN
351 --dbms_output.put_line('top task not found');
352 --Empty structure
353 l_element_id := NULL;
354 END IF;
355 CLOSE Get_Top_Task_Id;
356 END IF;
357 END IF;
358 CLOSE Get_Element_Id;
359 OPEN Get_Parent_Struc_Ver_Id(l_id_from);
360 FETCH Get_Parent_Struc_Ver_Id into l_parent_struc_ver_id;
361 CLOSE Get_Parent_Struc_Ver_Id;
362
363 --need to create a task under the from side.
364 --dbms_output.put_line('Pid = '||p_project_id_from||', l_struc_id = '||l_structure_id||', l_element_id = '||l_element_id||'number(name) = '||substr(l_task_name_number,0,25)||'('||substr(l_task_name_number,0,240)||')');
365 PA_TASK_PUB1.CREATE_TASK
366 ( p_validate_only => FND_API.G_FALSE
367 ,p_object_type => 'PA_TASKS'
368 ,p_project_id => p_project_id_from
369 ,p_structure_id => l_structure_id
370 ,p_ref_task_id => l_element_id
371 ,p_peer_or_sub => l_peer_or_sub
372 ,p_structure_version_id => l_parent_struc_ver_id
373 ,p_task_number => substr(l_task_name_number,0,25)
374 ,p_task_name => substr(l_task_name_number,0,240)
375 ,p_task_manager_id => NULL
376 ,p_task_manager_name => NULL
377 ,p_scheduled_start_date => l_scheduled_start_date
378 ,p_scheduled_finish_date => l_scheduled_finish_date
379 ,p_link_task_flag => 'Y'
380 ,x_task_id => l_task_id
381 ,x_return_status => l_return_status
382 ,x_msg_count => l_msg_count
383 ,x_msg_data => l_msg_data);
384
385 If (p_debug_mode = 'Y') THEN
386 pa_debug.debug('new task id => '||l_task_id);
387 END IF;
388
389 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
390
391 x_msg_count := FND_MSG_PUB.count_msg;
392 if x_msg_count = 1 then
393 pa_interface_utils_pub.get_messages
394 (p_encoded => FND_API.G_TRUE,
395 p_msg_index => 1,
396 p_msg_count => l_msg_count,
397 p_msg_data => l_msg_data,
398 p_data => l_data,
399 p_msg_index_out => l_msg_index_out);
400 x_msg_data := l_data;
401 end if;
402 RAISE FND_API.G_EXC_ERROR;
403 end if;
404
405 --CREATE_TASK_VERSION
406 l_peer_or_sub := 'SUB';
407
408 If (p_debug_mode = 'Y') THEN
409 pa_debug.debug('Create peer or sub => '||l_peer_or_sub);
410 END IF;
411
412 PA_TASK_PUB1.CREATE_TASK_VERSION
413 ( p_validate_only => FND_API.G_FALSE
414 ,p_ref_task_version_id => l_id_from
415 ,p_peer_or_sub => l_peer_or_sub
416 ,p_task_id => l_task_id
417 ,x_task_version_id => l_task_version_id
418 ,x_return_status => l_return_status
419 ,x_msg_count => l_msg_count
420 ,x_msg_data => l_msg_data);
421
422 If (p_debug_mode = 'Y') THEN
423 pa_debug.debug('new task version id => '||l_task_version_id);
424 END IF;
425
426
427 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
428
429 x_msg_count := FND_MSG_PUB.count_msg;
430 if x_msg_count = 1 then
431 pa_interface_utils_pub.get_messages
432 (p_encoded => FND_API.G_TRUE,
433 p_msg_index => 1,
434 p_msg_count => l_msg_count,
435 p_msg_data => l_msg_data,
436 p_data => l_data,
437 p_msg_index_out => l_msg_index_out);
438 x_msg_data := l_data;
439 end if;
440 RAISE FND_API.G_EXC_ERROR;
441 end if;
442
443 if PA_PROJECT_STRUCTURE_UTILS.GET_STRUC_TYPE_FOR_VERSION(p_structure_version_id_from, 'WORKPLAN') = 'Y' then
444 PA_TASK_PUB1.CREATE_SCHEDULE_VERSION
445 ( p_validate_only => FND_API.G_FALSE
446 ,p_element_version_id => l_task_version_id
447 ,p_scheduled_start_date => l_scheduled_start_date
448 ,p_scheduled_end_date => l_scheduled_finish_date
449 ,x_pev_schedule_id => l_pev_schedule_id
450 ,x_return_status => l_return_status
451 ,x_msg_count => l_msg_count
452 ,x_msg_data => l_msg_data );
453
454 If (p_debug_mode = 'Y') THEN
455 pa_debug.debug('new workplan attr for task => '||l_pev_schedule_id);
456 END IF;
457
458 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
459 x_msg_count := FND_MSG_PUB.count_msg;
460 if x_msg_count = 1 then
461 pa_interface_utils_pub.get_messages
462 (p_encoded => FND_API.G_TRUE,
463 p_msg_index => 1,
464 p_msg_count => l_msg_count,
465 p_msg_data => l_msg_data,
466 p_data => l_data,
467 p_msg_index_out => l_msg_index_out);
468 x_msg_data := l_data;
469 end if;
470 RAISE FND_API.G_EXC_ERROR;
471 end if;
472 END IF;
473
474 --Assign new task as the linking object
475 l_id_from := l_task_version_id;
476 l_type_from := 'PA_TASKS';
477
478 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
479 p_user_id => FND_GLOBAL.USER_ID
480 ,p_object_type_from => l_type_from
481 ,p_object_id_from1 => l_id_from
482 ,p_object_id_from2 => NULL
483 ,p_object_id_from3 => NULL
484 ,p_object_id_from4 => NULL
485 ,p_object_id_from5 => NULL
486 ,p_object_type_to => l_type_to
487 ,p_object_id_to1 => l_id_to
488 ,p_object_id_to2 => NULL
489 ,p_object_id_to3 => NULL
490 ,p_object_id_to4 => NULL
491 ,p_object_id_to5 => NULL
492 ,p_relationship_type => p_relationship_type
493 ,p_relationship_subtype => p_relationship_subtype
494 ,p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day.
495 ,p_imported_lag => NULL
496 ,p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority.
497 ,p_pm_product_code => NULL
498 ,x_object_relationship_id => x_object_relationship_id
499 ,x_return_status => x_return_status
500 --FPM changes bug 3301192
501 ,p_comments => null
502 ,p_status_code => null
503 --end FPM changes bug 3301192
504 );
505
506 -- 4537865
507 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
508 RAISE FND_API.G_EXC_ERROR;
509 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
511 END IF;
512 -- End : 4537865
513
514 ELSE
515 PA_UTILS.ADD_MESSAGE('PA',l_error_message_code);
516 x_msg_data := l_error_message_code;
517 RAISE FND_API.G_EXC_ERROR;
518 END IF;
519
520 ELSIF p_relationship_type = 'S' THEN
521 --create relationship for task
522
523 IF (p_debug_mode = 'Y') THEN
524 pa_debug.debug('creating task link');
525 END IF;
526
527 IF (p_task_version_id_from IS NULL) THEN
528 l_id_from := p_structure_version_id_from;
529 l_type_from := 'PA_STRUCTURES';
530 ELSE
531 l_id_from := p_task_version_id_from;
532 l_type_from := 'PA_TASKS';
533 END IF;
534
535 IF (p_task_version_id_to IS NULL) THEN
536 l_id_to := p_structure_version_id_to;
537 l_type_to := 'PA_STRUCTURES';
538 ELSE
539 l_id_to := p_task_version_id_to;
540 l_type_to := 'PA_TASKS';
541 END IF;
542
543 IF (p_weighting_percentage = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM or p_weighting_percentage IS NULL) THEN
544 l_weighting_percentage := NULL;
545 ELSE
546 l_weighting_percentage := p_weighting_percentage;
547 END IF;
548
549 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
550 p_user_id => FND_GLOBAL.USER_ID
551 ,p_object_type_from => l_type_from
552 ,p_object_id_from1 => l_id_from
553 ,p_object_id_from2 => NULL
554 ,p_object_id_from3 => NULL
555 ,p_object_id_from4 => NULL
556 ,p_object_id_from5 => NULL
557 ,p_object_type_to => l_type_to
558 ,p_object_id_to1 => l_id_to
559 ,p_object_id_to2 => NULL
560 ,p_object_id_to3 => NULL
561 ,p_object_id_to4 => NULL
562 ,p_object_id_to5 => NULL
563 ,p_relationship_type => p_relationship_type
564 ,p_relationship_subtype => p_relationship_subtype
565 ,p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day
566 ,p_imported_lag => NULL
567 ,p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority
568 ,p_pm_product_code => NULL
569 ,p_weighting_percentage => l_weighting_percentage
570 ,x_object_relationship_id => x_object_relationship_id
571 ,x_return_status => x_return_status
572 --FPM changes bug 3301192
573 ,p_comments => null
574 ,p_status_code => null
575 --end FPM changes bug 3301192
576 );
577
578 -- 4537865
579 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
580 RAISE FND_API.G_EXC_ERROR;
581 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
582 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
583 END IF;
584 -- End : 4537865
585
586 -- Begin add rtarway FP.M development
587 ELSIF p_relationship_type = 'M'THEN
588 --create mapping for task
589 --l_type_from := 'PA_TASKS';
590 --l_type_to := 'PA_TASKS';
591 IF (p_debug_mode = 'Y') THEN
592 pa_debug.debug('creating task link');
593 END IF;
594
595 IF (p_task_version_id_from IS NOT NULL) THEN
596 l_id_from := p_task_version_id_from;
597 l_type_from := 'PA_TASKS';
598 END IF;
599
600 IF (p_task_version_id_to IS NOT NULL) THEN
601 l_id_to := p_task_version_id_to;
602 l_type_to := 'PA_TASKS';
603 END IF;
604 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
605 p_user_id => FND_GLOBAL.USER_ID
606 , p_object_type_from => l_type_from
607 , p_object_id_from1 => l_id_from
608 , p_object_id_from2 => NULL
609 , p_object_id_from3 => NULL
610 , p_object_id_from4 => NULL
611 , p_object_id_from5 => NULL
612 , p_object_type_to => l_type_to
613 , p_object_id_to1 => l_id_to
614 , p_object_id_to2 => NULL
615 , p_object_id_to3 => NULL
616 , p_object_id_to4 => NULL
617 , p_object_id_to5 => NULL
618 , p_relationship_type=> p_relationship_type
619 , p_relationship_subtype =>NULL
620 , p_lag_day => l_lag_day --Bug 2955589. Use miss num handled local var instead of p_lag_day
621 , p_imported_lag => NULL
622 , p_priority => l_priority --Bug 2955589. Use miss char handled local var instead of p_priority
623 , p_pm_product_code => NULL
624 , p_weighting_percentage => NULL
625 , x_object_relationship_id => x_object_relationship_id
626 , x_return_status => x_return_status
627 , p_comments => null
628 , p_status_code => null
629 );
630
631 -- 4537865
632 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
633 RAISE FND_API.G_EXC_ERROR;
634 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
636 END IF;
637 -- End 4537865
638
639 -- End add rtarway FP.M development
640 END IF;
641
642 IF (p_debug_mode = 'Y') THEN
643 pa_debug.debug('PA_RELATIONSHIP_PVT.CREATE_RELATIONSHIP end');
644 END IF;
645
646 EXCEPTION
647 WHEN FND_API.G_EXC_ERROR THEN
648 IF (p_commit = FND_API.G_TRUE) THEN
649 ROLLBACK to create_relationship_pvt;
650 END IF;
651 x_msg_count := FND_MSG_PUB.count_msg;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 WHEN OTHERS THEN
654 IF (p_commit = FND_API.G_TRUE) THEN
655 ROLLBACK to create_relationship_pvt;
656 END IF;
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 x_msg_count := FND_MSG_PUB.count_msg;
659 --put message
660 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
661 p_procedure_name => 'Create_Relationship',
662 p_error_text => SUBSTRB(SQLERRM,1,240));
663 RAISE;
664 END;
665
666 -- API name : Update_Relationship
667 -- Type : Private Procedure
668 -- Pre-reqs : None
669 -- Return Value : N/A
670 -- Parameters
671 -- p_api_version IN NUMBER := 1.0
672 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
673 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
674 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
675 -- p_validation_level IN VARCHAR2 := 100
676 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
677 -- p_debug_mode IN VARCHAR2 := 'N'
678 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
679 -- p_object_relationship_id IN NUMBER
680 -- p_project_id_from IN NUMBER
681 -- p_structure_id_from IN NUMBER
682 -- p_structure_version_id_from IN NUMBER
683 -- p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
684 -- p_project_id_to IN NUMBER
685 -- p_structure_id_to IN NUMBER
686 -- p_structure_version_id_to IN NUMBER
687 -- p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
688 -- p_relationship_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
689 -- p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
690 -- p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
691 -- p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
692 -- p_record_version_number IN NUMBER
693 -- x_return_status OUT VARCHAR2
694 -- x_msg_count OUT NUMBER
695 -- x_msg_data OUT VARCHAR2
696 --
697 -- History
698 --
699 -- 25-JUN-01 HSIU -Created
700 --
701 --
702
703
704 procedure Update_Relationship
705 (
706 p_api_version IN NUMBER := 1.0
707 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
708 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
709 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
710 ,p_validation_level IN VARCHAR2 := 100
711 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
712 ,p_debug_mode IN VARCHAR2 := 'N'
713 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
714 ,p_object_relationship_id IN NUMBER
715 ,p_project_id_from IN NUMBER
716 ,p_structure_id_from IN NUMBER
717 ,p_structure_version_id_from IN NUMBER
718 ,p_task_version_id_from IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
719 ,p_project_id_to IN NUMBER
720 ,p_structure_id_to IN NUMBER
721 ,p_structure_version_id_to IN NUMBER
722 ,p_task_version_id_to IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
723 ,p_relationship_type IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
724 ,p_relationship_subtype IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
725 ,p_lag_day IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
726 ,p_priority IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
727 ,p_weighting_percentage IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
728 ,p_record_version_number IN NUMBER
729 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
730 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
731 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
732 )
733 IS
734 l_id_from pa_object_relationships.object_id_from1%TYPE;
735 l_id_to pa_object_relationships.object_id_to1%TYPE;
736 l_type_from pa_object_relationships.object_type_from%TYPE;
737 l_type_to pa_object_relationships.object_type_to%TYPE;
738 l_or_id pa_object_relationships.object_relationship_id%TYPE;
739 l_weighting_percentage pa_object_relationships.weighting_percentage%TYPE;
740 BEGIN
741 IF (p_debug_mode = 'Y') THEN
742 pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_RELATIONSHIP begin');
743 END IF;
744
745 IF (p_commit = FND_API.G_TRUE) THEN
746 savepoint update_relationship_pvt;
747 END IF;
748
749 IF (p_debug_mode = 'Y') THEN
750 pa_debug.debug('Performing validations');
751 END IF;
752
753
754 IF (p_task_version_id_from IS NULL) THEN
755 l_id_from := p_structure_version_id_from;
756 l_type_from := 'PA_STRUCTURES';
757 ELSE
758 l_id_from := p_task_version_id_from;
759 l_type_from := 'PA_TASKS';
760 END IF;
761
762 IF (p_task_version_id_to IS NULL) THEN
763 l_id_to := p_structure_version_id_to;
764 l_type_to := 'PA_STRUCTURES';
765 ELSE
766 l_id_to := p_task_version_id_to;
767 l_type_to := 'PA_TASKS';
768 END IF;
769
770 IF (p_weighting_percentage = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM or p_weighting_percentage IS NULL) THEN
771 l_weighting_percentage := NULL;
772 ELSE
773 l_weighting_percentage := p_weighting_percentage;
774 END IF;
775
776 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
777 p_object_relationship_id => p_object_relationship_id
778 ,p_object_type_from => NULL
779 ,p_object_id_from1 => NULL
780 ,p_object_id_from2 => NULL
781 ,p_object_id_from3 => NULL
782 ,p_object_id_from4 => NULL
783 ,p_object_id_from5 => NULL
784 ,p_object_type_to => NULL
785 ,p_object_id_to1 => NULL
786 ,p_object_id_to2 => NULL
787 ,p_object_id_to3 => NULL
788 ,p_object_id_to4 => NULL
789 ,p_object_id_to5 => NULL
790 ,p_record_version_number => p_record_version_number
791 ,p_pm_product_code => NULL
792 ,x_return_status => x_return_status
793 );
794
795 -- 4537865
796 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
797 RAISE FND_API.G_EXC_ERROR;
798 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
800 END IF;
801 -- End 4537865
802
803 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
804 p_user_id => FND_GLOBAL.USER_ID
805 ,p_object_type_from => l_type_from
806 ,p_object_id_from1 => l_id_from
807 ,p_object_id_from2 => NULL
808 ,p_object_id_from3 => NULL
809 ,p_object_id_from4 => NULL
810 ,p_object_id_from5 => NULL
811 ,p_object_type_to => l_type_to
812 ,p_object_id_to1 => l_id_to
813 ,p_object_id_to2 => NULL
814 ,p_object_id_to3 => NULL
815 ,p_object_id_to4 => NULL
816 ,p_object_id_to5 => NULL
817 ,p_relationship_type => p_relationship_type
818 ,p_relationship_subtype => p_relationship_subtype
819 ,p_lag_day => p_lag_day
820 ,p_imported_lag => NULL
821 ,p_priority => p_priority
822 ,p_pm_product_code => NULL
823 ,p_weighting_percentage => l_weighting_percentage
824 ,x_object_relationship_id => l_or_id
825 ,x_return_status => x_return_status
826 --FPM changes bug 3301192
827 ,p_comments => null
828 ,p_status_code => null
829 --end FPM changes bug 3301192
830 );
831
832 -- 4537865
833 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
834 RAISE FND_API.G_EXC_ERROR;
835 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
837 END IF;
838 -- End 4537865
839
840 EXCEPTION
841 WHEN FND_API.G_EXC_ERROR THEN
842 IF (p_commit = FND_API.G_TRUE) THEN
843 ROLLBACK to update_relationship_pvt;
844 END IF;
845 x_msg_count := FND_MSG_PUB.count_msg;
846 x_return_status := FND_API.G_RET_STS_ERROR;
847 WHEN OTHERS THEN
848 IF (p_commit = FND_API.G_TRUE) THEN
849 ROLLBACK to update_relationship_pvt;
850 END IF;
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 x_msg_count := FND_MSG_PUB.count_msg;
853 --put message
854 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
855 p_procedure_name => 'Update_relationship',
856 p_error_text => SUBSTRB(SQLERRM,1,240));
857 RAISE;
858 END UPDATE_RELATIONSHIP;
859
860
861
862 -- API name : Delete_Relationship
863 -- Type : Private Procedure
864 -- Pre-reqs : None
865 -- Return Value : N/A
866 -- Parameters
867 -- p_api_version IN NUMBER := 1.0
868 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
869 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
870 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
871 -- p_validation_level IN VARCHAR2 := 100
872 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
873 -- p_debug_mode IN VARCHAR2 := 'N'
874 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
875 -- p_object_relationship_id IN NUMBER
876 -- p_record_version_number IN NUMBER
877 -- x_return_status OUT VARCHAR2
878 -- x_msg_count OUT NUMBER
879 -- x_msg_data OUT VARCHAR2
880 --
881 -- History
882 --
883 -- 25-JUN-01 HSIU -Created
884 --
885 --
886
887
888 procedure Delete_Relationship
889 (
890 p_api_version IN NUMBER := 1.0
891 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
892 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
893 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
894 ,p_validation_level IN VARCHAR2 := 100
895 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
896 ,p_debug_mode IN VARCHAR2 := 'N'
897 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
898 ,p_object_relationship_id IN NUMBER
899 ,p_record_version_number IN NUMBER
900 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
901 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
902 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
903 )
904 IS
905 CURSOR get_link_task_ver_id IS
906 select object_id_from1, relationship_type
907 from pa_object_relationships
908 where object_relationship_id = p_object_relationship_id;
909 l_link_task_ver get_link_task_ver_id%ROWTYPE;
910 l_task_version_rvn NUMBER;
911
912 l_return_status VARCHAR2(1);
913 l_msg_count NUMBER;
914 l_msg_data VARCHAR2(250);
915 l_data VARCHAR2(250);
916 l_msg_index_out NUMBER;
917
918 BEGIN
919 IF (p_debug_mode = 'Y') THEN
920 pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_RELATIONSHIP begin');
921 END IF;
922
923 IF (p_commit = FND_API.G_TRUE) THEN
924 savepoint delete_relationship_pvt;
925 END IF;
926
927 IF (p_debug_mode = 'Y') THEN
928 pa_debug.debug('Performing validations');
929 END IF;
930
931 OPEN get_link_task_ver_id;
932 FETCH get_link_task_ver_id into l_link_task_ver;
933 CLOSE get_link_task_ver_id;
934
935 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
936 p_object_relationship_id => p_object_relationship_id
937 ,p_object_type_from => NULL
938 ,p_object_id_from1 => NULL
939 ,p_object_id_from2 => NULL
940 ,p_object_id_from3 => NULL
941 ,p_object_id_from4 => NULL
942 ,p_object_id_from5 => NULL
943 ,p_object_type_to => NULL
944 ,p_object_id_to1 => NULL
945 ,p_object_id_to2 => NULL
946 ,p_object_id_to3 => NULL
947 ,p_object_id_to4 => NULL
948 ,p_object_id_to5 => NULL
949 ,p_record_version_number => p_record_version_number
950 ,p_pm_product_code => NULL
951 ,x_return_status => x_return_status
952 );
953
954 -- 4537865
955 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
956 RAISE FND_API.G_EXC_ERROR;
957 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
959 END IF;
960 -- End 4537865
961
962 If (l_link_task_ver.relationship_type = 'L') THEN
963 --need to delete link task if removing links.
964 select record_version_number
965 into l_task_version_rvn
966 from pa_proj_element_versions
967 where element_version_id = l_link_task_ver.object_id_from1;
968
969 PA_TASK_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
970 p_debug_mode => p_debug_mode,
971 p_task_version_id => l_link_task_ver.object_id_from1,
972 p_record_version_number => l_task_version_rvn,
973 x_return_status => l_return_status,
974 x_msg_count => l_msg_count,
975 x_msg_data => l_msg_data);
976 END IF;
977
978 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
979 x_msg_count := FND_MSG_PUB.count_msg;
980 IF x_msg_count = 1 then
981 pa_interface_utils_pub.get_messages
982 (p_encoded => FND_API.G_TRUE,
983 p_msg_index => 1,
984 p_msg_count => l_msg_count,
985 p_msg_data => l_msg_data,
986 p_data => l_data,
987 p_msg_index_out => l_msg_index_out);
988 x_msg_data := l_data;
989 END IF;
990 raise FND_API.G_EXC_ERROR;
991 END IF;
992
993 IF (p_debug_mode = 'Y') THEN
994 pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_RELATIONSHIP end');
995 END IF;
996 EXCEPTION
997 WHEN FND_API.G_EXC_ERROR THEN
998 IF (p_commit = FND_API.G_TRUE) THEN
999 ROLLBACK to delete_relationship_pvt;
1000 END IF;
1001 x_msg_count := FND_MSG_PUB.count_msg;
1002 x_return_status := FND_API.G_RET_STS_ERROR;
1003 WHEN OTHERS THEN
1004 IF (p_commit = FND_API.G_TRUE) THEN
1005 ROLLBACK to delete_relationship_pvt;
1006 END IF;
1007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1008 x_msg_count := FND_MSG_PUB.count_msg;
1009 --put message
1010 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
1011 p_procedure_name => 'Delete_relationship',
1012 p_error_text => SUBSTRB(SQLERRM,1,240));
1013 RAISE;
1014 END DELETE_RELATIONSHIP;
1015
1016 -- API name : Create_Dependency
1017 -- Type : Private Procedure
1018 -- Pre-reqs : None
1019 -- Return Value : N/A
1020 -- Parameters
1021 -- p_api_version IN NUMBER := 1.0
1022 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1023 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1024 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1025 -- p_validation_level IN VARCHAR2 := 100
1026 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1027 -- p_debug_mode IN VARCHAR2 := 'N'
1028 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1029 -- p_src_proj_id IN NUMBER := NULL
1030 -- p_src_task_ver_id IN NUMBER := NULL
1031 -- p_dest_proj_id IN NUMBER := NULL
1032 -- P_dest_task_id IN NUMBER := NULL
1033 -- P_type IN VARCHAR2 := 'FS'
1034 -- P_lag_days IN NUMBER := 0
1035 -- p_comments IN VARCHAR2 := NULL
1036 -- x_return_status OUT VARCHAR2
1037 -- x_msg_count OUT NUMBER
1038 -- x_msg_data OUT VARCHAR2
1039 --
1040 -- History
1041 --
1042 -- 10-dec-03 Maansari -Created
1043 --
1044 -- FPM bug 3301192
1045 --
1046
1047 procedure Create_dependency
1048 (
1049 p_api_version IN NUMBER := 1.0
1050 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1051 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1052 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1053 ,p_validation_level IN VARCHAR2 := 100
1054 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1055 ,p_debug_mode IN VARCHAR2 := 'N'
1056 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1057 ,p_src_proj_id IN NUMBER := NULL
1058 ,p_src_task_ver_id IN NUMBER := NULL
1059 ,p_dest_proj_id IN NUMBER := NULL
1060 ,p_dest_task_ver_id IN NUMBER := NULL
1061 ,p_type IN VARCHAR2 := 'FS'
1062 ,p_lag_days IN NUMBER := 0
1063 ,p_comments IN VARCHAR2 := NULL
1064 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1065 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1066 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1067 )
1068 IS
1069 l_api_name CONSTANT VARCHAR(30) := 'CREATE_DEPENDENCY';
1070 l_api_version CONSTANT NUMBER := 1.0;
1071
1072 l_return_status VARCHAR2(1);
1073 l_msg_count NUMBER;
1074 l_msg_data VARCHAR2(250);
1075 l_data VARCHAR2(250);
1076 l_msg_index_out NUMBER;
1077 l_error_msg_code VARCHAR2(250);
1078
1079 l_dest_proj_id NUMBER;
1080 l_dest_task_ver_id NUMBER;
1081 l_structure_ver_id NUMBER;
1082
1083 l_work_structure_ver_id NUMBER; /* working structure version */
1084 l_lp_structure_ver_id NUMBER; /* latest published structrue version */
1085 l_src_proj_ve VARCHAR2(1); /* source project versioning enabled flag */
1086 l_dest_proj_ve VARCHAR2(1); /* destination project versioning enabled flag */
1087 l_work_dest_task_ver_id NUMBER; /* destination working task version */
1088 l_object_relationship_id NUMBER;
1089 l_src_str_status_code VARCHAR2(30);
1090 l_status_code VARCHAR2(30);
1091
1092 l_lag_days NUMBER;
1093
1094 l_cnt NUMBER; /* created to check given task is summary task or not */
1095
1096 CURSOR get_src_str_status
1097 IS
1098 SELECT status_code
1099 FROM pa_proj_element_versions ppev,
1100 pa_proj_elem_ver_structure ppevs
1101 WHERE ppev.project_id = p_src_proj_id
1102 AND ppev.element_version_id = p_src_task_ver_id
1103 AND ppev.parent_structure_version_id = ppevs.element_version_id
1104 AND ppevs.project_id = ppev.project_id
1105 ;
1106
1107 CURSOR get_dest_task_ver_id
1108 IS
1109 SELECT pev2.element_version_id, 'STRUCTURE_WORKING' status_code
1110 FROM pa_proj_element_versions pev,
1111 pa_proj_elem_ver_structure str,
1112 pa_proj_element_versions pev2
1113 WHERE pev.proj_element_id = pev2.proj_element_id
1114 AND pev.project_id = pev2.project_id
1115 AND pev2.parent_structure_version_id = str.element_version_id
1116 AND pev2.project_id = str.project_id
1117 AND str.current_working_flag = 'Y'
1118 AND str.status_code <> 'STRUCTURE_PUBLISHED'
1119 AND pev.element_version_id = p_dest_task_ver_id
1120 UNION ALL
1121 SELECT pev2.element_version_id, 'STRUCTURE_PUBLISHED' status_code
1122 FROM pa_proj_element_versions pev,
1123 pa_proj_elem_ver_structure str,
1124 pa_proj_element_versions pev2
1125 WHERE pev.proj_element_id = pev2.proj_element_id
1126 AND pev.project_id = pev2.project_id
1127 AND pev2.parent_structure_version_id = str.element_version_id
1128 AND pev2.project_id = str.project_id
1129 AND str.status_code = 'STRUCTURE_PUBLISHED'
1130 AND str.latest_eff_published_flag = 'Y'
1131 AND pev.element_version_id = p_dest_task_ver_id
1132 ;
1133
1134 cursor is_summary_task(c_task_ver_id number) IS
1135 SELECT count(1)
1136 FROM dual
1137 WHERE EXISTS ( SELECT 'x'
1138 FROM pa_object_relationships por
1139 WHERE por.object_id_from1 = c_task_ver_id
1140 AND por.object_type_from = 'PA_TASKS'
1141 AND por.relationship_type = 'S');
1142
1143 l_debug_mode varchar2(1) := 'N'; --BUG 4218977, rtarway
1144 g_module_name varchar2(200) := 'PA_RELATIONSHIP_PVT.CREATE_DEPENDENCY';--BUG 4218977, rtarway
1145
1146 BEGIN
1147
1148 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1149
1150 IF (p_debug_mode = 'Y') THEN
1151 pa_debug.debug('PA_RELATIONSHIP_PVT.CREATE_DEPENDENCY begin');
1152 END IF;
1153
1154 IF (p_commit = FND_API.G_TRUE) THEN
1155 savepoint create_dependency_pvt;
1156 END IF;
1157
1158 --Bug8427713 : WHEN CONVERTING DATA FROM MSP, THE INCORRECT PRED_STRING VALUE IS LOADED
1159 IF p_calling_module = 'SELF_SERVICE' OR p_calling_module = 'AMG' THEN
1160 IF (p_lag_days = NULL) THEN
1161 l_lag_days := 0;
1162 END IF;
1163 l_lag_days := p_lag_days; -- bug 8583608 * 10 * 60 * 8;
1164 ELSE
1165 IF (p_lag_days = NULL) THEN
1166 l_lag_days := 0;
1167 ELSE
1168 l_lag_days := p_lag_days;
1169 END IF;
1170 END IF;
1171
1172 /* Checking source task is summary task or not */
1173 IF (UPPER(PA_PROJECT_STRUCTURE_UTILS.check_dep_on_summary_tk_ok(p_src_proj_id)) <> 'Y') THEN
1174 l_cnt := 0;
1175 BEGIN
1176 OPEN is_summary_task(p_src_task_ver_id);
1177 FETCH is_summary_task into l_cnt;
1178 CLOSE is_summary_task;
1179 /* If single row is returned */
1180 IF NVL(l_cnt,0) <> 0 THEN
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 x_msg_count := 1;
1183 x_msg_data := 'PA_PS_NO_DEP_ON_SUMM';
1184 PA_UTILS.add_message('PA', 'PA_PS_NO_DEP_ON_SUMM');
1185 raise FND_API.G_EXC_ERROR;
1186 END IF;
1187 END;
1188 END IF;
1189
1190
1191 /* Checking destination task is summary task or not */
1192 IF (UPPER(PA_PROJECT_STRUCTURE_UTILS.check_dep_on_summary_tk_ok(p_dest_proj_id)) <> 'Y') THEN
1193 l_cnt := 0;
1194 BEGIN
1195 OPEN is_summary_task(p_dest_task_ver_id);
1196 FETCH IS_SUMMARY_TASK into l_cnt;
1197 close is_summary_task;
1198 /* If single row is returned */
1199 IF NVL(l_cnt,0) <> 0 THEN
1200 x_return_status := FND_API.G_RET_STS_ERROR;
1201 x_msg_count := 1;
1202 x_msg_data := 'PA_PS_NO_DEP_ON_SUMM';
1203 PA_UTILS.add_message('PA', 'PA_PS_NO_DEP_ON_SUMM');
1204 raise FND_API.G_EXC_ERROR;
1205 END IF;
1206 END;
1207 END IF;
1208
1209 --create record in object relationships table
1210 --Added by rtarway, 4218977
1211 IF l_debug_mode = 'Y' THEN
1212 pa_debug.g_err_stage := 'Value of G_OP_VALIDATE_flag'||PA_PROJECT_PUB.G_OP_VALIDATE_FLAG ;
1213 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1214 END IF;
1215
1216 IF p_src_proj_id = p_dest_proj_id
1217 THEN
1218 --If Added by rtarway, 4218977
1219 --Added null check for BUG 4226832, rtarway
1220 IF ( PA_PROJECT_PUB.G_OP_VALIDATE_FLAG is null OR PA_PROJECT_PUB.G_OP_VALIDATE_FLAG = 'Y' ) THEN
1221 /* Checking intra dependency */
1222 BEGIN
1223
1224 PA_RELATIONSHIP_UTILS.check_create_intra_dep_ok(
1225 p_pre_project_id => p_dest_proj_id
1226 ,p_pre_task_ver_id => p_dest_task_ver_id
1227 ,p_project_id => p_src_proj_id
1228 ,p_task_ver_id => p_src_task_ver_id
1229 ,x_return_status => l_return_status
1230 ,x_msg_count => l_msg_count
1231 ,x_msg_data => l_msg_data
1232 );
1233
1234 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1235 x_msg_count := FND_MSG_PUB.count_msg;
1236 IF x_msg_count = 1 then
1237 pa_interface_utils_pub.get_messages
1238 (p_encoded => FND_API.G_TRUE,
1239 p_msg_index => 1,
1240 p_msg_count => l_msg_count,
1241 p_msg_data => l_msg_data,
1242 p_data => l_data,
1243 p_msg_index_out => l_msg_index_out);
1244 x_msg_data := l_data;
1245 END IF;
1246 raise FND_API.G_EXC_ERROR;
1247 END IF;
1248 END;
1249 END IF;
1250
1251 --Create record in relationships table.
1252 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
1253 p_user_id => FND_GLOBAL.USER_ID
1254 ,p_object_type_from => 'PA_TASKS'
1255 ,p_object_id_from1 => p_src_task_ver_id
1256 ,p_object_id_from2 => p_src_proj_id
1257 ,p_object_id_from3 => NULL
1258 ,p_object_id_from4 => NULL
1259 ,p_object_id_from5 => NULL
1260 ,p_object_type_to => 'PA_TASKS'
1261 ,p_object_id_to1 => p_dest_task_ver_id
1262 ,p_object_id_to2 => p_dest_proj_id
1263 ,p_object_id_to3 => NULL
1264 ,p_object_id_to4 => NULL
1265 ,p_object_id_to5 => NULL
1266 ,p_relationship_type => 'D'
1267 ,p_relationship_subtype => p_type
1268 ,p_lag_day => l_lag_days
1269 ,p_imported_lag => NULL
1270 ,p_priority => null
1271 ,p_pm_product_code => NULL
1272 ,x_object_relationship_id => l_object_relationship_id
1273 ,x_return_status => l_return_status
1274 ,p_comments => p_comments
1275 ,p_status_code => null /* not applicable for intra dependency */
1276 );
1277
1278 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1279 x_msg_count := FND_MSG_PUB.count_msg;
1280 IF x_msg_count = 1 then
1281 pa_interface_utils_pub.get_messages
1282 (p_encoded => FND_API.G_TRUE,
1283 p_msg_index => 1,
1284 p_msg_count => l_msg_count,
1285 p_msg_data => l_msg_data,
1286 p_data => l_data,
1287 p_msg_index_out => l_msg_index_out);
1288 x_msg_data := l_data;
1289 END IF;
1290 raise FND_API.G_EXC_ERROR;
1291 END IF;
1292
1293 ELSE
1294
1295 /* Checking inter dependency */
1296 BEGIN
1297 PA_RELATIONSHIP_UTILS.check_create_inter_dep_ok(
1298 p_pre_project_id => p_dest_proj_id
1299 ,p_pre_task_ver_id => p_dest_task_ver_id
1300 ,p_project_id => p_src_proj_id
1301 ,p_task_ver_id => p_src_task_ver_id
1302 ,x_return_status => l_return_status
1303 ,x_msg_count => l_msg_count
1304 ,x_msg_data => l_msg_data
1305 );
1306
1307 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1308 x_msg_count := FND_MSG_PUB.count_msg;
1309 IF x_msg_count = 1 then
1310 pa_interface_utils_pub.get_messages
1311 (p_encoded => FND_API.G_TRUE,
1312 p_msg_index => 1,
1313 p_msg_count => l_msg_count,
1314 p_msg_data => l_msg_data,
1315 p_data => l_data,
1316 p_msg_index_out => l_msg_index_out);
1317 x_msg_data := l_data;
1318 END IF;
1319 raise FND_API.G_EXC_ERROR;
1320 END IF;
1321 END;
1322
1323 l_src_proj_ve := PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_src_proj_id);
1324 l_dest_proj_ve := PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_dest_proj_id);
1325
1326 OPEN get_src_str_status;
1327 FETCH get_src_str_status INTO l_src_str_status_code;
1328 CLOSE get_src_str_status;
1329
1330 FOR get_dest_task_ver_id_rec IN get_dest_task_ver_id LOOP
1331 IF l_src_str_status_code = rtrim(get_dest_task_ver_id_rec.status_code) AND l_src_str_status_code = 'STRUCTURE_WORKING'
1332 THEN
1333 l_status_code := 'UNPUBLISHED';
1334 ELSIF l_src_str_status_code = rtrim(get_dest_task_ver_id_rec.status_code) AND l_src_str_status_code = 'STRUCTURE_PUBLISHED'
1335 THEN
1336 l_status_code := 'PUBLISHED';
1337 ELSE
1338 IF (l_src_str_status_code = 'STRUCTURE_WORKING' AND rtrim(get_dest_task_ver_id_rec.status_code) = 'STRUCTURE_PUBLISHED')
1339 THEN
1340 IF l_dest_proj_ve = 'Y'
1341 THEN
1342 l_status_code := 'PUBLISHED'; /* creating dependency from a working version to published version and dest is versioned.*/
1343 ELSE
1344 l_status_code := 'UNPUBLISHED'; /* creating dependency from a working version to published version and dest is not versioned.*/
1345 END IF;
1346 ELSIF (l_src_str_status_code = 'STRUCTURE_PUBLISHED' AND rtrim(get_dest_task_ver_id_rec.status_code) = 'STRUCTURE_WORKING') --Bug No 3763315
1347 THEN
1348 l_status_code := 'UNPUBLISHED'; /* creating dependency from a working version to published version and dest is versioned.*/
1349 ELSIF (l_src_proj_ve = 'N' AND l_dest_proj_ve = 'N') AND
1350 (l_src_str_status_code = 'STRUCTURE_PUBLISHED' AND rtrim(get_dest_task_ver_id_rec.status_code) = 'STRUCTURE_PUBLISHED')
1351 THEN
1352 l_status_code := 'PUBLISHED'; /* creating dependency from a published version to published version */
1353 END IF;
1354 END IF;
1355
1356 --Create record in relationships table.
1357 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
1358 p_user_id => FND_GLOBAL.USER_ID
1359 ,p_object_type_from => 'PA_TASKS'
1360 ,p_object_id_from1 => p_src_task_ver_id
1361 ,p_object_id_from2 => p_src_proj_id
1362 ,p_object_id_from3 => NULL
1363 ,p_object_id_from4 => NULL
1364 ,p_object_id_from5 => NULL
1365 ,p_object_type_to => 'PA_TASKS'
1366 ,p_object_id_to1 => get_dest_task_ver_id_rec.element_version_id
1367 ,p_object_id_to2 => p_dest_proj_id
1368 ,p_object_id_to3 => NULL
1369 ,p_object_id_to4 => NULL
1370 ,p_object_id_to5 => NULL
1371 ,p_relationship_type => 'D'
1372 ,p_relationship_subtype => p_type
1373 ,p_lag_day => l_lag_days
1374 ,p_imported_lag => NULL
1375 ,p_priority => null
1376 ,p_pm_product_code => NULL
1377 ,x_object_relationship_id => l_object_relationship_id
1378 ,x_return_status => l_return_status
1379 ,p_comments => p_comments
1380 ,p_status_code => l_status_code
1381 );
1382
1383 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1384 x_msg_count := FND_MSG_PUB.count_msg;
1385 IF x_msg_count = 1 then
1386 pa_interface_utils_pub.get_messages
1387 (p_encoded => FND_API.G_TRUE,
1388 p_msg_index => 1,
1389 p_msg_count => l_msg_count,
1390 p_msg_data => l_msg_data,
1391 p_data => l_data,
1392 p_msg_index_out => l_msg_index_out);
1393 x_msg_data := l_data;
1394 END IF;
1395 raise FND_API.G_EXC_ERROR;
1396 END IF;
1397
1398 END LOOP;
1399
1400 END IF;
1401
1402 x_return_status := FND_API.G_RET_STS_SUCCESS;
1403
1404 IF (p_commit = FND_API.G_TRUE) THEN
1405 COMMIT;
1406 END IF;
1407
1408 IF (p_debug_mode = 'Y') THEN
1409 pa_debug.debug('PA_RELATIONSHIP_PVT.CREATE_DEPENDENCY END');
1410 END IF;
1411
1412 EXCEPTION
1413 when FND_API.G_EXC_ERROR then
1414 if p_commit = FND_API.G_TRUE then
1415 rollback to create_dependency_pvt;
1416 end if;
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 when FND_API.G_EXC_UNEXPECTED_ERROR then
1419 if p_commit = FND_API.G_TRUE then
1420 rollback to create_dependency_pvt;
1421 end if;
1422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1423 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1424 p_procedure_name => 'CREATE_DEPENDENCY',
1425 p_error_text => SUBSTRB(SQLERRM,1,240));
1426 when OTHERS then
1427 if p_commit = FND_API.G_TRUE then
1428 rollback to create_dependency_pvt;
1429 end if;
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1432 p_procedure_name => 'CREATE_DEPENDENCY',
1433 p_error_text => SUBSTRB(SQLERRM,1,240));
1434 raise;
1435 END Create_Dependency;
1436
1437 -- API name : Update_Dependency
1438 -- Type : Private Procedure
1439 -- Pre-reqs : None
1440 -- Return Value : N/A
1441 -- Parameters
1442 -- p_api_version IN NUMBER := 1.0
1443 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1444 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1445 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1446 -- p_validation_level IN VARCHAR2 := 100
1447 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1448 -- p_debug_mode IN VARCHAR2 := 'N'
1449 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1450 -- p_task_version_id IN NUMBER := NULL
1451 -- p_type IN VARCHAR2 := NULL
1452 -- p_lag_days IN NUMBER := NULL
1453 -- p_comments IN VARCHAR2 := NULL
1454 -- p_record_version_number IN NUMBER
1455 -- x_return_status OUT VARCHAR2
1456 -- x_msg_count OUT NUMBER
1457 -- x_msg_data OUT VARCHAR2
1458 --
1459 -- History
1460 --
1461 -- 10-dec-03 Maansari -Created
1462 --
1463 -- FPM bug 3301192
1464 --
1465
1466 procedure Update_dependency
1467 (
1468 p_api_version IN NUMBER := 1.0
1469 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1470 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1471 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1472 ,p_validation_level IN VARCHAR2 := 100
1473 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1474 ,p_debug_mode IN VARCHAR2 := 'N'
1475 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1476 ,p_task_version_id IN NUMBER := NULL
1477 ,p_src_task_version_id IN NUMBER := NULL
1478 ,p_type IN VARCHAR2 := NULL
1479 ,p_lag_days IN NUMBER := NULL
1480 ,p_comments IN VARCHAR2 := NULL
1481 ,p_record_version_number IN NUMBER
1482 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1483 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1484 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1485 )
1486 IS
1487 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_DEPENDENCY';
1488 l_api_version CONSTANT NUMBER := 1.0;
1489
1490 l_return_status VARCHAR2(1);
1491 l_msg_count NUMBER;
1492 l_msg_data VARCHAR2(250);
1493 l_data VARCHAR2(250);
1494 l_msg_index_out NUMBER;
1495 l_error_msg_code VARCHAR2(250);
1496
1497 l_lag_days NUMBER;
1498 l_comments VARCHAR2(240);
1499 l_rel_subtype VARCHAR2(30);
1500
1501 CURSOR cur_obj_rel
1502 IS
1503 SELECT *
1504 FROM pa_object_relationships
1505 WHERE object_id_to1 = p_task_version_id
1506 AND object_id_from1 = p_src_task_version_id
1507 AND relationship_type = 'D';
1508 BEGIN
1509
1510 IF (p_debug_mode = 'Y') THEN
1511 pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY begin');
1512 END IF;
1513
1514 IF (p_commit = FND_API.G_TRUE) THEN
1515 savepoint update_dependency_pvt;
1516 END IF;
1517
1518 FOR l_obj_rel_rec IN cur_obj_rel LOOP
1519
1520 IF (l_obj_rel_rec.lag_day IS NULL) OR (p_lag_days <> l_obj_rel_rec.lag_day)
1521 THEN
1522 --Bug8427713 : WHEN CONVERTING DATA FROM MSP, THE INCORRECT PRED_STRING VALUE IS LOADED
1523 IF p_calling_module = 'SELF_SERVICE' OR p_calling_module = 'AMG' THEN
1524 l_lag_days := p_lag_days; -- bug# 8583608 * 10 * 60 * 8;
1525 ELSE
1526 l_lag_days := p_lag_days;
1527 END IF;
1528 ELSE
1529 l_lag_days := l_obj_rel_rec.lag_day;
1530 END IF;
1531
1532 IF (l_obj_rel_rec.relationship_subtype IS NULL) OR (p_type <> l_obj_rel_rec.relationship_subtype)
1533 THEN
1534 l_rel_subtype := p_type;
1535 ELSE
1536 l_rel_subtype := l_obj_rel_rec.relationship_subtype;
1537 END IF;
1538
1539 l_comments := p_comments;
1540
1541 --update record in object relationships table
1542
1543 PA_OBJECT_RELATIONSHIPS_PKG.UPDATE_ROW
1544 ( p_user_id => FND_GLOBAL.USER_ID
1545 ,p_object_relationship_id => l_obj_rel_rec.object_relationship_id
1546 ,p_relationship_type => l_obj_rel_rec.relationship_type
1547 ,p_relationship_subtype => l_rel_subtype
1548 ,p_lag_day => l_lag_days
1549 ,p_priority => l_obj_rel_rec.priority
1550 ,p_pm_product_code => l_obj_rel_rec.pm_product_code
1551 ,p_weighting_percentage => l_obj_rel_rec.weighting_percentage
1552 ,p_comments => l_comments
1553 ,p_status_code => l_obj_rel_rec.status_code
1554 ,p_record_version_number => p_record_version_number
1555 ,x_return_status => l_return_status
1556 );
1557
1558 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1559 x_msg_count := FND_MSG_PUB.count_msg;
1560 IF x_msg_count = 1 then
1561 pa_interface_utils_pub.get_messages
1562 (p_encoded => FND_API.G_TRUE,
1563 p_msg_index => 1,
1564 p_msg_count => l_msg_count,
1565 p_msg_data => l_msg_data,
1566 p_data => l_data,
1567 p_msg_index_out => l_msg_index_out);
1568 x_msg_data := l_data;
1569 END IF;
1570 raise FND_API.G_EXC_ERROR;
1571 END IF;
1572
1573 END LOOP;
1574
1575 x_return_status := FND_API.G_RET_STS_SUCCESS;
1576
1577 IF (p_commit = FND_API.G_TRUE) THEN
1578 COMMIT;
1579 END IF;
1580
1581 IF (p_debug_mode = 'Y') THEN
1582 pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY END');
1583 END IF;
1584
1585 EXCEPTION
1586 when FND_API.G_EXC_ERROR then
1587 if p_commit = FND_API.G_TRUE then
1588 rollback to update_dependency_pvt;
1589 end if;
1590 x_return_status := FND_API.G_RET_STS_ERROR;
1591 when FND_API.G_EXC_UNEXPECTED_ERROR then
1592 if p_commit = FND_API.G_TRUE then
1593 rollback to update_dependency_pvt;
1594 end if;
1595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1596 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1597 p_procedure_name => 'UPDATE_DEPENDENCY',
1598 p_error_text => SUBSTRB(SQLERRM,1,240));
1599 when OTHERS then
1600 if p_commit = FND_API.G_TRUE then
1601 rollback to update_dependency_pvt;
1602 end if;
1603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1604 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1605 p_procedure_name => 'UPDATE_DEPENDENCY',
1606 p_error_text => SUBSTRB(SQLERRM,1,240));
1607 raise;
1608 END Update_Dependency;
1609
1610 -- API name : Delete_Dependency
1611 -- Type : Private Procedure
1612 -- Pre-reqs : None
1613 -- Return Value : N/A
1614 -- Parameters
1615 -- p_api_version IN NUMBER := 1.0
1616 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1617 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
1618 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1619 -- p_validation_level IN VARCHAR2 := 100
1620 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1621 -- p_debug_mode IN VARCHAR2 := 'N'
1622 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1623 -- p_object_relationship_id IN NUMBER := NULL
1624 -- x_return_status OUT VARCHAR2
1625 -- x_msg_count OUT NUMBER
1626 -- x_msg_data OUT VARCHAR2
1627 --
1628 -- History
1629 --
1630 -- 10-dec-03 Maansari -Created
1631 --
1632 -- FPM bug 3301192
1633 --
1634
1635 procedure Delete_Dependency
1636 (
1637 p_api_version IN NUMBER := 1.0
1638 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
1639 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1640 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
1641 ,p_validation_level IN VARCHAR2 := 100
1642 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1643 ,p_debug_mode IN VARCHAR2 := 'N'
1644 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
1645 ,p_object_relationship_id IN NUMBER := NULL
1646 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1647 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1648 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1649 )
1650 IS
1651 l_api_name CONSTANT VARCHAR(30) := 'DELETE_DEPENDENCY';
1652 l_api_version CONSTANT NUMBER := 1.0;
1653
1654 l_return_status VARCHAR2(1);
1655 l_msg_count NUMBER;
1656 l_msg_data VARCHAR2(250);
1657 l_data VARCHAR2(250);
1658 l_msg_index_out NUMBER;
1659 l_error_msg_code VARCHAR2(250);
1660
1661
1662 /* Since the one source can be dependent on multiple destination projects, we need to deltete dependencies only from
1663 one specific destination project. */
1664 /* get the relationship ids of the dependencies between the source and the destination.*/
1665
1666 CURSOR cur_obj_rel
1667 IS
1668 SELECT por2.object_relationship_id, por2.record_version_number
1669 FROM pa_object_relationships por1,
1670 pa_object_relationships por2
1671 WHERE por1.object_relationship_id = p_object_relationship_id
1672 AND por1.relationship_type = 'D'
1673 AND por1.object_id_from1 = por2.object_id_from1
1674 AND por2.object_id_to1 IN (
1675 select ppev1.element_version_id
1676 from pa_proj_element_versions ppev1,
1677 pa_proj_element_versions ppev2
1678 where ppev2.element_version_id = por1.object_id_to1
1679 and ppev2.project_id = ppev1.project_id
1680 and ppev2.proj_element_Id = ppev1.proj_element_id);
1681 --
1682 --Bug No 3494587 Added this cursor to get source structure version id and project id
1683 --for the given relationship id
1684 CURSOR cur_get_struc_det(cp_object_relationship_id NUMBER)
1685 IS
1686 SELECT parent_structure_version_id,project_id
1687 FROM pa_object_relationships por,
1688 pa_proj_element_versions ppev
1689 WHERE por.object_relationship_id = cp_object_relationship_id
1690 AND ppev.element_version_id = por.object_id_from1;
1691 l_project_id NUMBER;
1692 l_struc_ver_id NUMBER;
1693 BEGIN
1694
1695 IF (p_debug_mode = 'Y') THEN
1696 pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_DEPENDENCY begin');
1697 END IF;
1698
1699 IF (p_commit = FND_API.G_TRUE) THEN
1700 savepoint delete_dependency_pvt;
1701 END IF;
1702
1703 --delete record from object relationships table.
1704
1705 FOR cur_obj_rel_rec IN cur_obj_rel LOOP
1706 --
1707 --Bug No 3494587
1708 OPEN cur_get_struc_det(cur_obj_rel_rec.object_relationship_id);
1709 FETCH cur_get_struc_det INTO l_struc_ver_id,l_project_id;
1710 CLOSE cur_get_struc_det;
1711 --
1712 -- Bug No 3494587, added this to code check if the the structure ver is published
1713 -- if the sturcture ver is published then the process should not allow to delete dependency
1714 IF PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_project_id) = 'Y' THEN
1715 IF PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(l_project_id,l_struc_ver_id) = 'Y' THEN
1716 PA_UTILS.ADD_MESSAGE('PA','PA_DEL_DEP_FOR_PUB_STR');
1717 RAISE FND_API.G_EXC_ERROR;
1718 END IF;
1719 END IF;
1720 --
1721 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW (
1722 p_object_relationship_id => cur_obj_rel_rec.object_relationship_id
1723 ,p_object_type_from => null
1724 ,p_object_id_from1 => null
1725 ,p_object_id_from2 => null
1726 ,p_object_id_from3 => null
1727 ,p_object_id_from4 => null
1728 ,p_object_id_from5 => null
1729 ,p_object_type_to => null
1730 ,p_object_id_to1 => null
1731 ,p_object_id_to2 => null
1732 ,p_object_id_to3 => null
1733 ,p_object_id_to4 => null
1734 ,p_object_id_to5 => null
1735 ,p_record_version_number => cur_obj_rel_rec.record_version_number
1736 ,p_pm_product_code => null
1737 ,x_return_status => l_return_status );
1738
1739 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1740 x_msg_count := FND_MSG_PUB.count_msg;
1741 IF x_msg_count = 1 then
1742 pa_interface_utils_pub.get_messages
1743 (p_encoded => FND_API.G_TRUE,
1744 p_msg_index => 1,
1745 p_msg_count => l_msg_count,
1746 p_msg_data => l_msg_data,
1747 p_data => l_data,
1748 p_msg_index_out => l_msg_index_out);
1749 x_msg_data := l_data;
1750 END IF;
1751 raise FND_API.G_EXC_ERROR;
1752 END IF;
1753
1754 END LOOP;
1755
1756 x_return_status := FND_API.G_RET_STS_SUCCESS;
1757
1758 IF (p_commit = FND_API.G_TRUE) THEN
1759 COMMIT;
1760 END IF;
1761
1762 IF (p_debug_mode = 'Y') THEN
1763 pa_debug.debug('PA_RELATIONSHIP_PVT.DELETE_DEPENDENCY END');
1764 END IF;
1765 EXCEPTION
1766 when FND_API.G_EXC_ERROR then
1767 if p_commit = FND_API.G_TRUE then
1768 rollback to delete_dependency_pvt;
1769 end if;
1770 x_return_status := FND_API.G_RET_STS_ERROR;
1771 when FND_API.G_EXC_UNEXPECTED_ERROR then
1772 if p_commit = FND_API.G_TRUE then
1773 rollback to delete_dependency_pvt;
1774 end if;
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1777 p_procedure_name => 'DELETE_DEPENDENCY',
1778 p_error_text => SUBSTRB(SQLERRM,1,240));
1779 when OTHERS then
1780 if p_commit = FND_API.G_TRUE then
1781 rollback to delete_dependency_pvt;
1782 end if;
1783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
1785 p_procedure_name => 'DELETE_DEPENDENCY',
1786 p_error_text => SUBSTRB(SQLERRM,1,240));
1787 raise;
1788 END Delete_Dependency;
1789
1790 -- Added for FP_M changes 3305199
1791 Procedure Copy_Intra_Dependency (
1792 /* Bug #: 3305199 SMukka */
1793 /* Changing data type from PA_PLSQL_DATATYPES.IdTabTyp to SYSTEM.pa_num_tbl_type */
1794 /* P_Source_Ver_Tbl IN PA_PLSQL_DATATYPES.IdTabTyp, */
1795 /* P_Destin_Ver_Tbl IN PA_PLSQL_DATATYPES.IdTabTyp, */
1796 P_Source_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
1797 P_Destin_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
1798 P_source_struc_ver_id IN NUMBER := NULL,
1799 p_dest_struc_ver_id IN NUMBER := NULL,
1800 X_Return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1801 X_Msg_Count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1802 X_Msg_Data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1803 ) IS
1804
1805 l_Found_Flag NUMBER;
1806 l_Object_Task_ID NUMBER;
1807
1808 l_src_proj_id NUMBER;
1809 l_src_task_ver_id NUMBER;
1810 l_dest_proj_id NUMBER;
1811 l_dest_task_ver_id NUMBER;
1812 l_Type VARCHAR2(100);
1813 l_lag_days NUMBER;
1814 l_comments VARCHAR2(240);
1815
1816 l_return_status VARCHAR2(1);
1817 l_msg_count NUMBER;
1818 l_msg_data VARCHAR2(250);
1819 l_data VARCHAR2(250);
1820 l_msg_index_out NUMBER;
1821 l_error_msg_code VARCHAR2(250);
1822
1823 --bug 4019845
1824 CURSOR get_struc_dependency IS
1825 select --a.element_version_id src_task_ver_id,
1826 b.element_version_id dest_task_ver_id,
1827 --c.element_version_id src_pred_ver_id,
1828 d.element_version_id dest_pred_ver_id,
1829 a.project_id,
1830 r.relationship_subtype,
1831 r.lag_day,
1832 r.comments
1833 from pa_proj_element_versions a,
1834 pa_proj_element_versions b,
1835 pa_proj_element_versions c,
1836 pa_proj_element_versions d,
1837 pa_object_relationships r
1838 where a.project_id = b.project_id
1839 and a.proj_element_id = b.proj_element_id
1840 and a.parent_structure_version_id = P_source_struc_ver_id
1841 and b.parent_structure_version_id = p_dest_struc_ver_id
1842 and r.relationship_type = 'D'
1843 and r.object_id_from1 = a.element_version_id
1844 and r.object_id_to1 = c.element_version_id
1845 and r.object_id_from2 = r.object_id_to2
1846 and c.project_id = a.project_id
1847 and c.parent_structure_version_id = p_source_struc_ver_id
1848 and d.project_id = b.project_id
1849 and d.proj_element_id = c.proj_element_id
1850 and d.parent_structure_version_id = p_dest_struc_ver_id;
1851 l_dep_struc_rec get_struc_dependency%ROWTYPE;
1852 --end bug 4019845
1853
1854 CURSOR get_dependency(c_suc_ver_id NUMBER, c_pred_ver_id NUMBER) IS
1855 select * from pa_object_relationships
1856 where relationship_type = 'D'
1857 and object_id_from1 = c_suc_ver_id
1858 and object_id_to1 = c_pred_ver_id
1859 and object_id_from2 = object_id_to2
1860 and object_type_from = 'PA_TASKS'
1861 and object_type_to = 'PA_TASKS';
1862 l_dependency_rec get_dependency%ROWTYPE;
1863
1864 CURSOR get_parent_struc_ver_id(c_elem_ver_id NUMBER) IS
1865 select parent_structure_version_id, project_id
1866 from pa_proj_element_versions
1867 where element_version_id = c_elem_ver_id;
1868 l_parent_ver_id1 NUMBER;
1869 l_parent_ver_id2 NUMBER;
1870 l_project_id1 NUMBER;
1871 l_project_id2 NUMBER;
1872
1873 CURSOR check_intra_dep_exists(c_elem_ver_id NUMBER) IS
1874 select 1
1875 from pa_object_relationships
1876 where relationship_type = 'D'
1877 and object_id_from1 = c_elem_ver_id
1878 and object_id_from2 = object_id_to2
1879 and rownum = 1;
1880 l_dummy NUMBER;
1881
1882 --bug 4153377
1883 l_pred_ver_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1884 l_pred_proj_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1885 l_suc_ver_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1886 l_suc_proj_id_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1887 l_comment_tbl SYSTEM.PA_VARCHAR2_240_TBL_TYPE := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
1888 l_subtype_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1889 l_lag_days_tbl SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1890
1891 CURSOR get_dependency2 IS
1892 select /*+ leading(dt1) use_nl(dt1 rel) */ -- hint added per performance team recommendation for bug 5576900
1893 rel.object_id_from1, dt1.dest_task_ver_id DEST_FROM_ID,
1894 rel.object_id_to1, dt2.dest_task_ver_id DEST_TO_ID,
1895 rel.comments, rel.LAG_DAY, rel.RELATIONSHIP_SUBTYPE
1896 from pa_object_relationships rel,
1897 pa_copy_dep_temp dt1,
1898 pa_copy_dep_temp dt2
1899 where rel.relationship_type = 'D'
1900 and rel.object_id_from1 = dt1.src_task_ver_id
1901 and rel.object_id_to1 = dt2.src_task_ver_id
1902 and rel.object_id_from2 = object_id_to2
1903 and object_type_from = 'PA_TASKS'
1904 and object_type_to = 'PA_TASKS';
1905 l_dep_rec2 get_dependency2%ROWTYPE;
1906 --end bug 4153377
1907
1908 -- Begin fix for Bug # 4354217.
1909
1910 -- Begin Bug # 4354217 : 15-AUG-2005.
1911
1912 -- Bug # 5077599.
1913
1914 -- Bug 9247114
1915 -- Reverted to the old definition of get_dependency3 since the new definition added as part
1916 -- of bug 4947328 did not resolve the original issue, and also resulted in P1 bug 9247114
1917 -- Note that pa_copy_dep_temp will not have all the tasks in the copy tasks context
1918
1919 cursor get_dependency3 is
1920 -- select all predecessor dependencies from the source task to other tasks in the project.
1921 select pcdt.dest_task_ver_id suc_ver_id, rel.object_id_to1 pred_ver_id
1922 , rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
1923 , rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
1924 from pa_object_relationships rel, pa_copy_dep_temp pcdt
1925 where rel.object_id_from1 = pcdt.src_task_ver_id
1926 and rel.relationship_type = 'D'
1927 and rel.object_id_from2 = rel.object_id_to2
1928 and object_type_from = 'PA_TASKS'
1929 and object_type_to = 'PA_TASKS'
1930 -- This condition prevents the creation of intra-project dependencies between a task and any of
1931 -- its sub-tasks.
1932 and rel.object_id_to1 not in (select por.object_id_from1
1933 from pa_object_relationships por
1934 where por.relationship_type = 'S'
1935 and por.object_type_from = 'PA_TASKS'
1936 start with por.object_id_to1 = pcdt.dest_task_ver_id
1937 connect by prior por.object_id_from1 = por.object_id_to1
1938 and prior por.relationship_type = por.relationship_type
1939 union
1940 select por.object_id_to1
1941 from pa_object_relationships por
1942 where por.relationship_type = 'S'
1943 and por.object_type_to = 'PA_TASKS'
1944 start with por.object_id_from1 = pcdt.dest_task_ver_id
1945 connect by prior por.object_id_to1 = por.object_id_from1
1946 and prior por.relationship_type = por.relationship_type)
1947 union all
1948 -- select all successor dependencies from other tasks in the project to the source task.
1949 select rel.object_id_from1 suc_ver_id, pcdt.dest_task_ver_id pred_ver_id
1950 , rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
1951 , rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
1952 from pa_object_relationships rel, pa_copy_dep_temp pcdt
1953 where rel.object_id_to1 = pcdt.src_task_ver_id
1954 and rel.relationship_type = 'D'
1955 and rel.object_id_from2 = rel.object_id_to2
1956 and object_type_from = 'PA_TASKS'
1957 and object_type_to = 'PA_TASKS'
1958 -- This condition prevents the creation of intra-project dependencies between a task and any of \
1959 -- its sub-tasks.
1960 and rel.object_id_from1 not in (select por.object_id_from1
1961 from pa_object_relationships por
1962 where por.relationship_type = 'S'
1963 and por.object_type_from = 'PA_TASKS'
1964 start with por.object_id_to1 = pcdt.dest_task_ver_id
1965 connect by prior por.object_id_from1 = por.object_id_to1
1966 and prior por.relationship_type = por.relationship_type
1967 union
1968 select por.object_id_to1
1969 from pa_object_relationships por
1970 where por.relationship_type = 'S'
1971 and por.object_type_to = 'PA_TASKS'
1972 start with por.object_id_from1 = pcdt.dest_task_ver_id
1973 connect by prior por.object_id_to1 = por.object_id_from1
1974 and prior por.relationship_type = por.relationship_type);
1975
1976
1977
1978
1979 /*
1980 cursor get_dependency3 is
1981 -- select all predecessor dependencies from the source task to other tasks in the project.
1982 select pcdt.dest_task_ver_id suc_ver_id, pcdt2.dest_task_ver_id pred_ver_id
1983 , rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
1984 , rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
1985 from pa_object_relationships rel, pa_copy_dep_temp pcdt, pa_copy_dep_temp pcdt2
1986 where rel.object_id_from1 = pcdt.src_task_ver_id
1987 and rel.relationship_type = 'D'
1988 and rel.object_id_to1 = pcdt2.src_task_ver_id
1989 and rel.object_id_from2 = rel.object_id_to2
1990 and object_type_from = 'PA_TASKS'
1991 and object_type_to = 'PA_TASKS'
1992 -- This condition prevents the creation of intra-project dependencies between a task and any of
1993 -- its sub-tasks.
1994 and pcdt2.dest_task_ver_id not in (select por.object_id_from1
1995 from pa_object_relationships por
1996 where por.relationship_type = 'S'
1997 and por.object_type_from = 'PA_TASKS'
1998 start with por.object_id_to1 = pcdt.dest_task_ver_id
1999 connect by prior por.object_id_from1 = por.object_id_to1
2000 and prior por.relationship_type = por.relationship_type
2001 union
2002 select por.object_id_to1
2003 from pa_object_relationships por
2004 where por.relationship_type = 'S'
2005 and por.object_type_to = 'PA_TASKS'
2006 start with por.object_id_from1 = pcdt.dest_task_ver_id
2007 connect by prior por.object_id_to1 = por.object_id_from1
2008 and prior por.relationship_type = por.relationship_type)
2009 union
2010 -- select all successor dependencies from other tasks in the project to the source task.
2011 select pcdt2.dest_task_ver_id suc_ver_id, pcdt.dest_task_ver_id pred_ver_id
2012 , rel.object_id_from2 suc_proj_id, rel.object_id_to2 pred_proj_id
2013 , rel.relationship_subtype sub_type, rel.lag_day lag_day, rel.comments comments
2014 from pa_object_relationships rel, pa_copy_dep_temp pcdt, pa_copy_dep_temp pcdt2
2015 where rel.object_id_to1 = pcdt.src_task_ver_id
2016 and rel.relationship_type = 'D'
2017 and rel.object_id_from1 = pcdt2.src_task_ver_id
2018 and rel.object_id_from2 = rel.object_id_to2
2019 and object_type_from = 'PA_TASKS'
2020 and object_type_to = 'PA_TASKS'
2021 -- This condition prevents the creation of intra-project dependencies between a task and any of
2022 -- its sub-tasks.
2023 and pcdt2.dest_task_ver_id not in (select por.object_id_from1
2024 from pa_object_relationships por
2025 where por.relationship_type = 'S'
2026 and por.object_type_from = 'PA_TASKS'
2027 start with por.object_id_to1 = pcdt.dest_task_ver_id
2028 connect by prior por.object_id_from1 = por.object_id_to1
2029 and prior por.relationship_type = por.relationship_type
2030 union
2031 select por.object_id_to1
2032 from pa_object_relationships por
2033 where por.relationship_type = 'S'
2034 and por.object_type_to = 'PA_TASKS'
2035 start with por.object_id_from1 = pcdt.dest_task_ver_id
2036 connect by prior por.object_id_to1 = por.object_id_from1
2037 and prior por.relationship_type = por.relationship_type);
2038
2039 -- End of Bug # 5077599.
2040 */
2041
2042 cursor l_cur_all_tasks(c_task_ver_id NUMBER) is
2043 select count(ppev.element_version_id)
2044 from pa_proj_element_versions ppev
2045 where ppev.parent_structure_version_id = (select ppev2.parent_structure_version_id
2046 from pa_proj_element_versions ppev2
2047 where ppev2.element_version_id = c_task_ver_id)
2048 and ppev.object_type = 'PA_TASKS'
2049 and ppev.element_version_id not in (select pcdt.src_task_ver_id
2050 from pa_copy_dep_temp pcdt);
2051
2052 l_count_all_tasks NUMBER := null;
2053
2054 -- End Bug # 4354217 : 15-AUG-2005.
2055
2056 -- End fix for Bug # 4354217.
2057
2058 l_debug_mode VARCHAR2(1); --debug messages added while fixing bug 5067296
2059 BEGIN
2060 Delete from PA_COPY_DEP_TEMP; --Bug#8842950
2061
2062 --debug messages added while fixing bug 5067296
2063 l_debug_mode := NVL(FND_PROFILE.value_specific('PA_DEBUG_MODE',fnd_global.user_id,fnd_global.login_id,275,null,null), 'N');
2064
2065 IF (l_debug_mode = 'Y') THEN
2066 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY Start : Passed Parameters :', x_Log_Level=> 3);
2067 /* These two lines are causing bug 5076461 in publish flow.
2068 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'P_Source_Ver_Tbl.Count='||P_Source_Ver_Tbl.Count, x_Log_Level=> 3);
2069 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'P_Destin_Ver_Tbl.Count='||P_Destin_Ver_Tbl.Count, x_Log_Level=> 3);
2070 */
2071 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'P_source_struc_ver_id='||P_source_struc_ver_id, x_Log_Level=> 3);
2072 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'p_dest_struc_ver_id='||p_dest_struc_ver_id, x_Log_Level=> 3);
2073 END IF;
2074 --debug messages added while fixing bug 5067296
2075
2076
2077 IF (P_source_struc_ver_id IS NULL) THEN
2078
2079 --bug 4153377
2080 --insert mapping ids
2081 --debug messages added while fixing bug 5067296
2082 IF (l_debug_mode = 'Y') THEN
2083 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before BULK insert into PA_COPY_DEP_TEMP table', x_Log_Level=> 3);
2084 END IF;
2085 --debug messages added while fixing bug 5067296
2086 Forall i IN 1..P_Source_Ver_Tbl.Count
2087 INSERT INTO PA_COPY_DEP_TEMP(SRC_TASK_VER_ID, DEST_TASK_VER_ID)
2088 VALUES(p_source_ver_tbl(i), p_destin_ver_tbl(i));
2089
2090 -- Begin fix for Bug # 4354217.
2091
2092 -- Begin Bug # 4354217 : 15-AUG-2005.
2093
2094 -- The cursor get_dependency2 expects all the source tasks from a structure version to be
2095 -- passed into this API along with their destination task versions. The cursor get_dependency2
2096 -- is used to create the same dependencies among the dest_task_ver_ids as exists among
2097 -- the src_task_ver_ids.
2098 -- If only some src_task_ver_id and their corresponding dest_task_ver_id are passed into this API
2099 -- as is the case from the PA_TASK_PUB1.COPY_TASK() API, then we use the cursor get_dependency3 to
2100 -- get all the dependencies for each src_task_ver_id and create the same for the corresponding
2101 -- dest_task_ver_id.
2102
2103 --debug messages added while fixing bug 5067296
2104 IF (l_debug_mode = 'Y') THEN
2105 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before cursor l_cur_all_tasks', x_Log_Level=> 3);
2106 END IF;
2107 --debug messages added while fixing bug 5067296
2108
2109 IF P_Source_Ver_Tbl.Count > 0 --bug 5067296
2110 THEN
2111
2112 open l_cur_all_tasks(p_source_ver_tbl(1));
2113 fetch l_cur_all_tasks into l_count_all_tasks;
2114 close l_cur_all_tasks;
2115 END IF;
2116
2117 --debug messages added while fixing bug 5067296
2118 IF (l_debug_mode = 'Y') THEN
2119 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'After cursor l_cur_all_tasks l_count_all_tasks='||l_count_all_tasks, x_Log_Level=> 3);
2120 END IF;
2121 --debug messages added while fixing bug 5067296
2122
2123 if (NVL(l_count_all_tasks,0) > 0) then
2124
2125 open get_dependency3;
2126
2127 -- End Bug # 4354217 : 15-AUG-2005.
2128
2129 loop
2130
2131 fetch get_dependency3 bulk collect INTO l_suc_ver_id_tbl, l_pred_ver_id_tbl
2132 , l_suc_proj_id_tbl, l_pred_proj_id_tbl
2133 , l_subtype_tbl, l_lag_days_tbl
2134 , l_comment_tbl LIMIT 1000;
2135 exit WHEN get_dependency3%NOTFOUND;
2136
2137 end loop;
2138
2139 close get_dependency3;
2140
2141 else
2142
2143 -- End fix for Bug # 4354217.
2144
2145 --check if tasks has dependency
2146 OPEN get_dependency2;
2147 LOOP
2148 FETCH get_dependency2 INTO l_dep_rec2;
2149 EXIT when get_dependency2%NOTFOUND;
2150
2151 --check if copying to same structure version; bug 3625037
2152 OPEN get_parent_struc_ver_id(l_dep_rec2.DEST_FROM_ID);
2153 FETCH get_parent_struc_ver_id INTO l_parent_ver_id1, l_project_id1;
2154 CLOSE get_parent_struc_ver_id;
2155
2156 OPEN get_parent_struc_ver_id(l_dep_rec2.DEST_TO_ID);
2157 FETCH get_parent_struc_ver_id INTO l_parent_ver_id2, l_project_id2;
2158 CLOSE get_parent_struc_ver_id;
2159
2160 --debug messages added while fixing bug 5067296
2161 IF (l_debug_mode = 'Y') THEN
2162 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'l_parent_ver_id1='||l_parent_ver_id1, x_Log_Level=> 3);
2163 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'l_parent_ver_id2='||l_parent_ver_id2, x_Log_Level=> 3);
2164 END IF;
2165 --debug messages added while fixing bug 5067296
2166
2167 IF (l_parent_ver_id1 = l_parent_ver_id2) THEN
2168 --insert into plsql tbl
2169 l_suc_ver_id_tbl.extend(1);
2170 l_suc_ver_id_tbl(l_suc_ver_id_tbl.count) := l_dep_rec2.DEST_FROM_ID;
2171 l_suc_proj_id_tbl.extend(1);
2172 l_suc_proj_id_tbl(l_suc_proj_id_tbl.count) := l_project_id1;
2173 l_pred_ver_id_tbl.extend(1);
2174 l_pred_ver_id_tbl(l_pred_ver_id_tbl.count) := l_dep_rec2.DEST_TO_ID;
2175 l_pred_proj_id_tbl.extend(1);
2176 l_pred_proj_id_tbl(l_pred_proj_id_tbl.count) := l_project_id2;
2177 l_comment_tbl.extend(1);
2178 l_comment_tbl(l_comment_tbl.count) := l_dep_rec2.comments;
2179 l_subtype_tbl.extend(1);
2180 l_subtype_tbl(l_subtype_tbl.count) := l_dep_rec2.relationship_subtype;
2181 l_lag_days_tbl.extend(1);
2182 l_lag_days_tbl(l_lag_days_tbl.count) := l_dep_rec2.lag_day;
2183 END IF;
2184
2185 END LOOP;
2186 Close get_dependency2;
2187
2188 end if; -- Fix for Bug # 4354217.
2189
2190 ELSE
2191 --use get_struc_dependency to populate table
2192
2193 --debug messages added while fixing bug 5067296
2194 IF (l_debug_mode = 'Y') THEN
2195 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before opening cursor get_struc_dependency', x_Log_Level=> 3);
2196 END IF;
2197 --debug messages added while fixing bug 5067296
2198
2199 OPEN get_struc_dependency;
2200 LOOP
2201 FETCH get_struc_dependency bulk collect INTO l_suc_ver_id_tbl, l_pred_ver_id_tbl, l_pred_proj_id_tbl, l_subtype_tbl,
2202 l_lag_days_tbl, l_comment_tbl LIMIT 1000;
2203 EXIT WHEN get_struc_dependency%NOTFOUND;
2204 END LOOP;
2205 CLOSE get_struc_dependency;
2206
2207 FOR i IN 1..l_pred_proj_id_tbl.count
2208 LOOP
2209 l_suc_proj_id_tbl.extend(1);
2210 l_suc_proj_id_tbl(i) := l_pred_proj_id_tbl(i);
2211 END LOOP;
2212
2213 END IF;
2214
2215 --bulk insert into pa_object_relationships table
2216 --debug messages added while fixing bug 5067296
2217 IF (l_debug_mode = 'Y') THEN
2218 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.COPY_INTRA_DEPENDENCY', x_Msg => 'Before BULK insert into PA_OBJECT_RELATIONSHIPS table', x_Log_Level=> 3);
2219 END IF;
2220 --debug messages added while fixing bug 5067296
2221
2222 FORALL i IN 1..l_suc_ver_id_tbl.COUNT
2223 INSERT INTO PA_OBJECT_RELATIONSHIPS(
2224 OBJECT_RELATIONSHIP_ID
2225 ,CREATED_BY
2226 ,CREATION_DATE
2227 ,LAST_UPDATED_BY
2228 ,LAST_UPDATE_DATE
2229 ,LAST_UPDATE_LOGIN
2230 ,RELATIONSHIP_TYPE
2231 ,OBJECT_TYPE_FROM
2232 ,OBJECT_TYPE_TO
2233 ,OBJECT_ID_FROM1
2234 ,OBJECT_ID_TO1
2235 ,OBJECT_ID_FROM2
2236 ,OBJECT_ID_TO2
2237 ,LAG_DAY
2238 ,RELATIONSHIP_SUBTYPE
2239 ,COMMENTS
2240 ,RECORD_VERSION_NUMBER
2241 )
2242 VALUES (
2243 pa_object_relationships_s.nextval
2244 ,FND_GLOBAL.USER_ID
2245 ,sysdate
2246 ,FND_GLOBAL.USER_ID
2247 ,sysdate
2248 ,FND_GLOBAL.USER_ID
2249 ,'D'
2250 ,'PA_TASKS'
2251 ,'PA_TASKS'
2252 ,l_suc_ver_id_tbl(i)
2253 ,l_pred_ver_id_tbl(i)
2254 ,l_suc_proj_id_tbl(i)
2255 ,l_pred_proj_id_tbl(i)
2256 ,l_lag_days_tbl(i)
2257 ,l_subtype_tbl(i)
2258 ,l_comment_tbl(i)
2259 ,1
2260 );
2261
2262 --end bug 4153377
2263
2264
2265 /*
2266 For i IN 1..P_Source_Ver_Tbl.Count Loop
2267
2268 --bug 3975527
2269 --if dependency exists, then enter second loop
2270 OPEN check_intra_dep_exists(p_source_ver_tbl(i));
2271 FETCH check_intra_dep_exists INTO l_dummy;
2272 IF check_intra_dep_exists%FOUND THEN
2273 --end bug 3975527
2274
2275 For j IN 1..P_Source_Ver_Tbl.Count Loop
2276 -- Fetch the dependency Object Task ID
2277 -- Scan thru all the Source Version Object IDs
2278 OPEN get_dependency(p_source_ver_tbl(i), p_source_ver_tbl(j));
2279 FETCH get_dependency INTO l_dependency_rec;
2280 l_found_flag := 0;
2281 IF (get_dependency%FOUND) THEN
2282 --check if copying to same structure version; bug 3625037
2283 OPEN get_parent_struc_ver_id(p_destin_ver_tbl(i));
2284 FETCH get_parent_struc_ver_id INTO l_parent_ver_id1, l_project_id1;
2285 CLOSE get_parent_struc_ver_id;
2286
2287 OPEN get_parent_struc_ver_id(p_destin_ver_tbl(j));
2288 FETCH get_parent_struc_ver_id INTO l_parent_ver_id2, l_project_id2;
2289 CLOSE get_parent_struc_ver_id;
2290
2291 IF (l_parent_ver_id1 = l_parent_ver_id2) THEN
2292 l_found_flag := 1;
2293 END IF;
2294
2295 END IF;
2296 CLOSE get_dependency;
2297
2298 IF l_found_flag = 1 THEN
2299
2300 PA_Relationship_Pvt.Create_dependency (
2301 p_src_proj_id => l_project_id1
2302 ,p_src_task_ver_id => p_destin_ver_tbl(i)
2303 ,p_dest_proj_id => l_project_id2
2304 ,p_dest_task_ver_id => p_destin_ver_tbl(j)
2305 ,p_type => l_dependency_rec.relationship_subtype
2306 ,p_lag_days => l_dependency_rec.lag_day/(10*60*8)
2307 ,p_comments => l_dependency_rec.comments
2308 ,x_return_status => x_return_status
2309 ,x_msg_count => x_msg_count
2310 ,x_msg_data => x_msg_data
2311 );
2312
2313 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2314 x_msg_count := FND_MSG_PUB.count_msg;
2315 IF x_msg_count = 1 then
2316 pa_interface_utils_pub.get_messages
2317 (p_encoded => FND_API.G_TRUE,
2318 p_msg_index => 1,
2319 p_msg_count => x_msg_count,
2320 p_msg_data => x_msg_data,
2321 p_data => l_data,
2322 p_msg_index_out => l_msg_index_out);
2323 x_msg_data := l_data;
2324 END IF;
2325 raise FND_API.G_EXC_ERROR;
2326 END IF;
2327 END IF; --if found
2328 End Loop;
2329
2330 END IF;
2331 CLOSE check_intra_dep_exists;
2332 --end bug 3975527
2333 -- End of Looping thru predecessor IDs
2334 End Loop;
2335 -- End of Looping thru successor IDs
2336
2337 ELSE
2338 --bug 4019845: publishing changes; copy entire structure version
2339 open get_struc_dependency;
2340 LOOP
2341 FETCH get_struc_dependency INTO l_dep_struc_rec;
2342 EXIT WHEN get_struc_dependency%NOTFOUND;
2343
2344 PA_Relationship_Pvt.Create_dependency (
2345 p_src_proj_id => l_dep_struc_rec.project_id
2346 ,p_src_task_ver_id => l_dep_struc_rec.dest_task_ver_id
2347 ,p_dest_proj_id => l_dep_struc_rec.project_id
2348 ,p_dest_task_ver_id => l_dep_struc_rec.dest_pred_ver_id
2349 ,p_type => l_dep_struc_rec.relationship_subtype
2350 ,p_lag_days => l_dep_struc_rec.lag_day/(10*60*8)
2351 ,p_comments => l_dep_struc_rec.comments
2352 ,x_return_status => x_return_status
2353 ,x_msg_count => x_msg_count
2354 ,x_msg_data => x_msg_data
2355 );
2356
2357 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2358 x_msg_count := FND_MSG_PUB.count_msg;
2359 IF x_msg_count = 1 then
2360 pa_interface_utils_pub.get_messages
2361 (p_encoded => FND_API.G_TRUE,
2362 p_msg_index => 1,
2363 p_msg_count => x_msg_count,
2364 p_msg_data => x_msg_data,
2365 p_data => l_data,
2366 p_msg_index_out => l_msg_index_out);
2367 x_msg_data := l_data;
2368 END IF;
2369 close get_struc_dependency;
2370 raise FND_API.G_EXC_ERROR;
2371 END IF;
2372 END Loop;
2373 close get_struc_dependency;
2374 END IF;
2375 --end bug 4019845
2376 */
2377
2378 x_return_status := FND_API.G_RET_STS_SUCCESS;
2379
2380 EXCEPTION -- 4537865
2381 WHEN FND_API.G_EXC_ERROR THEN
2382 x_msg_count := FND_MSG_PUB.count_msg;
2383 x_return_status := FND_API.G_RET_STS_ERROR;
2384 WHEN OTHERS THEN
2385 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2386 x_msg_count := FND_MSG_PUB.count_msg;
2387 --put message
2388 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
2389 p_procedure_name => 'Copy_Intra_Dependency',
2390 p_error_text => SUBSTRB(SQLERRM,1,240));
2391 RAISE;
2392
2393 End Copy_Intra_Dependency;
2394
2395 /* Bug #: 3305199 SMukka Start of Fix */
2396 /* Commented the following procedure code and rewritten the logic for procedure */
2397 /* Copy_Inter_Project_Dependency */
2398 /*Procedure Copy_Inter_Project_Dependency ( */
2399 /* Bug #: 3305199 SMukka */
2400 /* Changing data type from PA_PLSQL_DATATYPES.IdTabTyp to SYSTEM.pa_num_tbl_type */
2401 /* P_Source_Ver_Tbl IN PA_PLSQL_DATATYPES.IdTabTyp, */
2402 /* P_Destin_Ver_Tbl IN PA_PLSQL_DATATYPES.IdTabTyp, */
2403 /* P_Source_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
2404 P_Destin_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
2405 X_Return_Status OUT VARCHAR2,
2406 X_Msg_Count OUT NUMBER,
2407 X_Msg_Data OUT VARCHAR2
2408 ) IS
2409
2410 l_Found_Flag NUMBER;
2411 l_Object_Task_ID NUMBER;
2412
2413 l_src_proj_id NUMBER;
2414 l_src_task_ver_id NUMBER;
2415 l_dest_proj_id NUMBER;
2416 l_dest_task_ver_id NUMBER;
2417 l_Type VARCHAR2(100);
2418 l_lag_days NUMBER;
2419 l_comments VARCHAR2(240);
2420
2421 l_return_status VARCHAR2(1);
2422 l_msg_count NUMBER;
2423 l_msg_data VARCHAR2(250);
2424 l_data VARCHAR2(250);
2425 l_msg_index_out NUMBER;
2426 l_error_msg_code VARCHAR2(250);
2427
2428 Begin
2429 For i IN 1..P_Source_Ver_Tbl.Count Loop
2430 -- Fetch the dependency Object Task ID
2431 l_Found_Flag := 0;
2432 Begin
2433 Select 1, Object_ID_TO1,
2434 Object_ID_From2, Object_ID_From1, Object_ID_To2, Object_ID_To1,
2435 Relationship_SubType, Lag_Day, Comments
2436 INTO l_Found_Flag, l_Object_Task_ID,
2437 l_src_proj_id, l_src_task_ver_id, l_dest_proj_id, l_dest_task_ver_id,
2438 l_Type, l_lag_days, l_comments
2439 From PA_Object_Relationships
2440 Where RELATIONSHIP_TYPE = 'D'
2441 And OBJECT_ID_TO2 <> OBJECT_ID_FROM2
2442 And OBJECT_ID_FROM1 = P_Source_Ver_Tbl(i);
2443 Exception When No_Data_Found then NULL;
2444 End;
2445
2446 If l_Found_Flag = 1 Then
2447 -- Scan thru all the Source Version Object IDs
2448 For j IN 1..P_Destin_Ver_Tbl.Count Loop
2449 If l_Object_Task_ID = P_Destin_Ver_Tbl(j) Then
2450 PA_Relationship_Pvt.Create_dependency (
2451 p_src_proj_id => l_src_proj_id
2452 ,p_src_task_ver_id => l_src_task_ver_id
2453 ,p_dest_proj_id => l_dest_proj_id
2454 ,p_dest_task_ver_id => l_dest_task_ver_id
2455 ,p_type => l_Type
2456 ,p_lag_days => l_Lag_Days
2457 ,p_comments => l_Comments
2458 ,x_return_status => x_return_status
2459 ,x_msg_count => x_msg_count
2460 ,x_msg_data => x_msg_data
2461 );
2462
2463 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2464 x_msg_count := FND_MSG_PUB.count_msg;
2465 IF x_msg_count = 1 then
2466 pa_interface_utils_pub.get_messages
2467 (p_encoded => FND_API.G_TRUE,
2468 p_msg_index => 1,
2469 p_msg_count => x_msg_count,
2470 p_msg_data => x_msg_data,
2471 p_data => l_data,
2472 p_msg_index_out => l_msg_index_out);
2473 x_msg_data := l_data;
2474 END IF;
2475 raise FND_API.G_EXC_ERROR;
2476 END IF;
2477 End If;
2478 End Loop;
2479 -- End of Looping thru destination IDs
2480 End If;
2481
2482 End Loop;
2483 -- End of Looping thru Source IDs
2484
2485 End Copy_Inter_Project_Dependency;*/
2486
2487
2488 Procedure Copy_Inter_Project_Dependency (
2489 P_Source_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
2490 P_Destin_Ver_Tbl IN SYSTEM.pa_num_tbl_type,
2491 X_Return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2492 X_Msg_Count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2493 X_Msg_Data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2494 ) IS
2495 --
2496 l_Found_Flag NUMBER;
2497 l_Object_Task_ID NUMBER;
2498 --
2499 l_src_proj_id NUMBER;
2500 l_src_task_ver_id NUMBER;
2501 l_dest_proj_id NUMBER;
2502 l_dest_task_ver_id NUMBER;
2503 l_Type VARCHAR2(100);
2504 l_lag_days NUMBER;
2505 l_comments VARCHAR2(240);
2506 --
2507 l_return_status VARCHAR2(1);
2508 l_msg_count NUMBER;
2509 l_msg_data VARCHAR2(250);
2510 l_data VARCHAR2(250);
2511 l_msg_index_out NUMBER;
2512 l_error_msg_code VARCHAR2(250);
2513 --
2514 CURSOR get_dependency(c_suc_ver_id NUMBER) IS
2515 select *
2516 From PA_Object_Relationships
2517 Where RELATIONSHIP_TYPE = 'D'
2518 and object_type_from = 'PA_TASKS'
2519 and object_type_to = 'PA_TASKS'
2520 And OBJECT_ID_TO2 <> OBJECT_ID_FROM2
2521 and object_id_from1 = c_suc_ver_id;
2522 l_dependency_rec get_dependency%ROWTYPE;
2523 --
2524 Begin
2525 For i IN 1..P_Source_Ver_Tbl.Count Loop
2526 -- Scan thru all the Source Version Object IDs
2527 OPEN get_dependency(p_source_ver_tbl(i));
2528 FETCH get_dependency INTO l_dependency_rec;
2529 l_found_flag := 0;
2530 IF (get_dependency%FOUND) THEN
2531 l_found_flag := 1;
2532 END IF;
2533 CLOSE get_dependency;
2534 --
2535 IF l_found_flag = 1 THEN
2536 SELECT project_id
2537 INTO l_src_proj_id
2538 FROM pa_proj_element_versions ppev
2539 WHERE ppev.element_version_id = p_destin_ver_tbl(i);
2540 --
2541 PA_Relationship_Pvt.Create_dependency (
2542 p_src_proj_id => l_src_proj_id
2543 ,p_src_task_ver_id => p_destin_ver_tbl(i)
2544 ,p_dest_proj_id => l_dependency_rec.object_id_to2
2545 ,p_dest_task_ver_id => l_dependency_rec.object_id_to1
2546 ,p_type => l_dependency_rec.relationship_subtype
2547 ,p_lag_days => l_dependency_rec.lag_day/*(10*60*8)*/ --bug 8583608
2548 ,p_comments => l_dependency_rec.comments
2549 ,x_return_status => x_return_status
2550 ,x_msg_count => x_msg_count
2551 ,x_msg_data => x_msg_data
2552 );
2553 -- 4537865 : This is wrong.Check shud be made against x_return_status :
2554 -- IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2555 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2556 x_msg_count := FND_MSG_PUB.count_msg;
2557 IF x_msg_count = 1 then
2558 pa_interface_utils_pub.get_messages
2559 (p_encoded => FND_API.G_TRUE,
2560 p_msg_index => 1,
2561 p_msg_count => x_msg_count,
2562 p_msg_data => x_msg_data,
2563 p_data => l_data,
2564 p_msg_index_out => l_msg_index_out);
2565 x_msg_data := l_data;
2566 END IF;
2567 raise FND_API.G_EXC_ERROR;
2568 END IF;
2569 END IF; --if found
2570 End Loop;
2571 -- End of Looping thru successor IDs
2572 --
2573 x_return_status := FND_API.G_RET_STS_SUCCESS;
2574 --
2575 EXCEPTION -- 4537865
2576 WHEN FND_API.G_EXC_ERROR THEN
2577 x_msg_count := FND_MSG_PUB.count_msg;
2578 x_return_status := FND_API.G_RET_STS_ERROR;
2579 WHEN OTHERS THEN
2580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2581 x_msg_count := FND_MSG_PUB.count_msg;
2582 --put message
2583 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
2584 p_procedure_name => 'Copy_Inter_Project_Dependency',
2585 p_error_text => SUBSTRB(SQLERRM,1,240));
2586 RAISE;
2587
2588 End Copy_Inter_Project_Dependency;
2589 /*Bug :3305199 End Of Fix */
2590
2591
2592 Procedure Publish_Inter_Proj_Dep (
2593 P_Publishing_Struc_Ver_ID IN NUMBER,
2594 P_Previous_Pub_Struc_Ver_ID IN NUMBER,
2595 P_Published_Struc_Ver_ID IN NUMBER,
2596 X_Return_Status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2597 X_Msg_Count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2598 X_Msg_Data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2599 )IS
2600
2601 CURSOR Check_Prev_Ver_Exists IS
2602 Select rel.object_relationship_id, rel.Record_Version_Number
2603 From pa_object_relationships rel,
2604 pa_proj_element_versions ppev1
2605 Where rel.relationship_type = 'D'
2606 and rel.object_id_from1 = ppev1.element_version_id
2607 and rel.object_id_to2 <> rel.object_id_from2
2608 and ppev1.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID;
2609 /* --bug 3970398
2610 Select rel.object_relationship_id, rel.Record_Version_Number
2611 From pa_object_relationships rel,
2612 pa_proj_element_versions ppev1,
2613 pa_proj_element_versions ppev2
2614 Where rel.relationship_type = 'D'
2615 and rel.object_id_from1 = ppev1.element_version_id
2616 and rel.object_id_to1 = ppev2.element_version_id
2617 and rel.object_id_to2 <> rel.object_id_from2
2618 and ppev1.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
2619 -- <PREVIOUS PUBLISHED VERSION ID>
2620 and Not Exists (
2621 Select 1
2622 From pa_object_relationships rel2,
2623 pa_proj_element_versions ppev3,
2624 pa_proj_element_versions ppev4
2625 where rel2.relationship_type = 'D'
2626 and rel2.object_id_to2 <> rel2.object_id_from2
2627 and rel.object_id_from1 = ppev3.element_version_id
2628 and rel.object_id_to1 = ppev4.element_version_id
2629 and rel.object_id_from1 = ppev1.element_version_id
2630 and rel.object_id_to1 = ppev2.element_version_id
2631 and ppev3.parent_structure_version_id = P_Publishing_Struc_Ver_ID);
2632 -- <PUBLISHING STRUCTURE VERSION ID> );
2633 */
2634
2635 -- If previous published version is NULL OR NOT NULL
2636 -- for creating inter project dependency on new published structure
2637 CURSOR Create_Proj_Depend (c_Version_ID NUMBER)IS
2638 select ppev2.element_version_id, ppev2.project_id,
2639 rel1.object_id_to1, rel1.object_id_to2, rel1.lag_day, rel1.comments,
2640 rel1.relationship_subtype
2641 from pa_object_relationships rel1,
2642 pa_proj_element_versions ppev,
2643 pa_proj_element_versions ppev2
2644 where rel1.relationship_type = 'D'
2645 and rel1.object_id_to2 <> rel1.object_id_from2
2646 and rel1.object_id_from1 = ppev.element_version_id
2647 and ppev.project_id = ppev2.project_id
2648 and ppev.proj_element_id = ppev2.proj_element_id
2649 and ppev.parent_structure_version_id = c_Version_ID
2650 -- <PUBLISHING STRUCTURE VERSION ID>
2651 and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID;
2652 -- <PUBLISHED STRUCTURE VERSION ID>
2653
2654 -- If published version is NULL or NOT NULL
2655 -- To Update successors dependencies:
2656 -- Bug 9841023
2657 CURSOR Update_Publ_Ver IS
2658 select distinct rel1.object_id_from1, rel1.object_id_from2 -- Fix for Bug # 4349093.
2659 , ppev2.element_version_id, ppev2.project_id
2660 ,MAX(rel1.lag_day), MAX(rel1.comments), MAX(rel1.relationship_subtype)
2661 -- , rel1.object_relationship_id -- Fix for Bug # 4349093.
2662 -- , rel1.record_version_number
2663 from pa_object_relationships rel1,
2664 pa_proj_element_versions ppev,
2665 pa_proj_element_versions ppev2
2666 where rel1.relationship_type = 'D'
2667 and rel1.object_id_to2 <> rel1.object_id_from2
2668 and rel1.object_id_to1 = ppev.element_version_id
2669 and ppev.project_id = ppev2.project_id
2670 and ppev.proj_element_id = ppev2.proj_element_id
2671 and ppev.parent_structure_version_id IN (P_Publishing_Struc_Ver_ID, P_Previous_Pub_Struc_Ver_ID)
2672 and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID
2673 group by
2674 rel1.object_id_from1, rel1.object_id_from2,
2675 ppev2.element_version_id, ppev2.project_id;
2676 l_del_obj_rel_id NUMBER;
2677
2678 /* --bug 3970398
2679 Select rel1.object_id_from1, rel1.object_id_from2,
2680 rel1.object_id_to1, rel1.object_id_to2,
2681 ppev2.element_version_id,
2682 ppev2.project_id, rel1.lag_day, rel1.comments, rel1.relationship_subtype
2683 from pa_object_relationships rel1,
2684 pa_proj_element_versions ppev,
2685 pa_proj_element_versions ppev2
2686 where rel1.relationship_type = 'D'
2687 and rel1.object_id_to2 <> rel1.object_id_from2
2688 and rel1.object_id_to1 = ppev.element_version_id
2689 and ppev.project_id = ppev2.project_id
2690 and ppev.proj_element_id = ppev2.proj_element_id
2691 and ppev.parent_structure_version_id = c_version_ID
2692 -- <PUBLISHING STRUCTURE VERSION ID>
2693 and ppev2.parent_structure_version_id = P_Published_Struc_Ver_ID;
2694 -- <PUBLISHED STRUCTURE VERSION ID>
2695 */
2696 -- For Update successors dependencies:
2697 -- If published version is NULL, use this SQL
2698 CURSOR Delete_Publ_Ver IS
2699 select rel.object_relationship_id, rel.Record_Version_Number
2700 from pa_object_relationships rel,
2701 pa_proj_element_versions ppev
2702 where rel.relationship_type = 'D'
2703 and rel.object_id_from1 = ppev.element_version_id
2704 and ppev.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
2705 -- <PREVIOUS PUBLISHED VERSION ID, if available>
2706 and rel.object_id_from2 <> rel.object_id_to2
2707 UNION
2708 select rel.object_relationship_id, rel.Record_Version_Number
2709 from pa_object_relationships rel,
2710 pa_proj_element_versions ppev
2711 where rel.relationship_type = 'D'
2712 and rel.object_id_to1 = ppev.element_version_id
2713 and ppev.parent_structure_version_id = P_Previous_Pub_Struc_Ver_ID
2714 -- <PREVIOUS PUBLISHED VERSION ID, if available>
2715 and rel.object_id_from2 <> rel.object_id_to2 ;
2716
2717 l_src_proj_id NUMBER;
2718 l_src_task_ver_id NUMBER;
2719 l_dest_proj_id NUMBER;
2720 l_dest_task_ver_id NUMBER;
2721
2722 l_Relationship_ID NUMBER;
2723 l_Record_Ver_Number NUMBER;
2724
2725 l_Version_ID NUMBER;
2726
2727 l_Element_Ver_ID NUMBER;
2728 l_Project_ID NUMBER;
2729 l_Sub_Type VARCHAR2(100);
2730 l_Lag_Days NUMBER;
2731 l_Comments VARCHAR2(240);
2732 l_Obj_ID_To1 NUMBER;
2733 l_Obj_ID_To2 NUMBER;
2734 l_Obj_ID_From1 NUMBER;
2735 l_Obj_ID_From2 NUMBER;
2736
2737 -- l_msg_count NUMBER;
2738 l_msg_data VARCHAR2(250);
2739 l_data VARCHAR2(250);
2740 l_msg_index_out NUMBER;
2741 l_error_msg_code VARCHAR2(250);
2742
2743 l_Object_Relationship_ID NUMBER;
2744
2745 Begin
2746
2747 --------------------------------- Begin of Step 1
2748 If P_Previous_Pub_Struc_Ver_ID IS NOT NULL Then
2749 Open Check_Prev_Ver_Exists;
2750
2751 LOOP
2752
2753 Fetch Check_Prev_Ver_Exists
2754 INTO l_Relationship_ID, l_Record_Ver_Number;
2755 EXIT when Check_Prev_Ver_Exists%NOTFOUND;
2756 --Close Check_Prev_Ver_Exists;
2757 -- Step 1: Delete Inter project dependencies from prev published version
2758 -- which does not exist in publishing structure
2759 PA_RELATIONSHIP_PVT.Delete_Relationship (
2760 p_object_relationship_id => l_Relationship_ID
2761 ,p_record_version_number => l_Record_Ver_Number
2762 ,x_return_status => x_return_status
2763 ,x_msg_count => x_msg_count
2764 ,x_msg_data => x_msg_data
2765 );
2766 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2767 x_msg_count := FND_MSG_PUB.count_msg;
2768 IF x_msg_count = 1 then
2769 pa_interface_utils_pub.get_messages
2770 (p_encoded => FND_API.G_TRUE,
2771 p_msg_index => 1,
2772 p_msg_count => x_msg_count,
2773 p_msg_data => x_msg_data,
2774 p_data => l_data,
2775 p_msg_index_out => l_msg_index_out);
2776 x_msg_data := l_data;
2777 END IF;
2778 Close Check_Prev_Ver_Exists;
2779 Raise FND_API.G_EXC_ERROR;
2780 END IF;
2781 END LOOP;
2782 Close Check_Prev_Ver_Exists;
2783 End If;
2784 --------------------------------- End of Step 1
2785
2786 --------------------------------- Begin of Step 2
2787 l_Version_ID := P_Publishing_Struc_Ver_ID;
2788 /* --bug 3970398
2789 IF P_Previous_Pub_Struc_Ver_ID IS NULL Then
2790 l_Version_ID := P_Publishing_Struc_Ver_ID;
2791 Else
2792 l_Version_ID := P_Previous_Pub_Struc_Ver_ID;
2793 End IF;
2794 */
2795
2796 Open Create_Proj_Depend(l_Version_ID);
2797 LOOP
2798 Fetch Create_Proj_Depend
2799 INTO l_src_task_ver_id, l_src_proj_id, l_dest_task_ver_id, l_dest_proj_id,
2800 l_Lag_Days, l_Comments, l_Sub_Type;
2801 EXIT when Create_Proj_Depend%NOTFOUND;
2802
2803 l_object_relationship_id := NULL;
2804 PA_Object_Relationships_PKG.Insert_Row(
2805 p_user_id => FND_GLOBAL.USER_ID
2806 ,p_object_type_from => 'PA_TASKS'
2807 ,p_object_id_from1 => l_src_task_ver_id
2808 ,p_object_id_from2 => l_src_proj_id
2809 ,p_object_id_from3 => NULL
2810 ,p_object_id_from4 => NULL
2811 ,p_object_id_from5 => NULL
2812 ,p_object_type_to => 'PA_TASKS'
2813 ,p_object_id_to1 => l_dest_task_ver_id
2814 ,p_object_id_to2 => l_dest_proj_id
2815 ,p_object_id_to3 => NULL
2816 ,p_object_id_to4 => NULL
2817 ,p_object_id_to5 => NULL
2818 ,p_relationship_type => 'D'
2819 ,p_relationship_subtype => l_Sub_Type
2820 ,p_lag_day => l_Lag_Days
2821 ,p_imported_lag => NULL
2822 ,p_priority => Null
2823 ,p_pm_product_code => NULL
2824 ,x_object_relationship_id => l_object_relationship_id
2825 ,p_comments => l_comments
2826 ,p_status_code => 'PUBLISHED'
2827 ,x_return_status => x_return_status
2828 -- ,x_msg_count => x_msg_count
2829 -- ,x_msg_data => x_msg_data
2830 );
2831
2832 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2833 x_msg_count := FND_MSG_PUB.count_msg;
2834 /* IF x_msg_count = 1 then
2835 pa_interface_utils_pub.get_messages
2836 (p_encoded => FND_API.G_TRUE,
2837 p_msg_index => 1,
2838 p_msg_count => x_msg_count,
2839 p_msg_data => x_msg_data,
2840 p_data => l_data,
2841 p_msg_index_out => l_msg_index_out);
2842 x_msg_data := l_data;
2843 END IF; */
2844 Close Create_Proj_Depend;
2845 Raise FND_API.G_EXC_ERROR;
2846 END IF;
2847 END LOOP;
2848 Close Create_Proj_Depend;
2849 --------------------------------- End of Step 2
2850
2851 --------------------------------- Begin of Step 3
2852 /*
2853 IF P_Previous_Pub_Struc_Ver_ID IS NULL Then
2854 l_Version_ID := P_Publishing_Struc_Ver_ID;
2855 Else
2856 l_Version_ID := P_Previous_Pub_Struc_Ver_ID;
2857 End IF;
2858 */
2859
2860 Open Update_Publ_Ver;
2861 LOOP
2862 Fetch Update_Publ_Ver
2863 Into l_Obj_ID_From1, l_Obj_ID_From2, l_Obj_ID_To1, l_Obj_ID_To2,
2864 l_Lag_Days, l_Comments, l_Sub_Type;
2865 -- , l_del_obj_rel_id -- Fix for Bug # 4349093.
2866 -- , l_Record_Ver_Number; -- Bug
2867 /* --bug 3970398
2868 Into l_Obj_ID_From1, l_Obj_ID_From2, l_Obj_ID_To1, l_Obj_ID_To2,
2869 l_src_task_ver_id, l_src_proj_id, -- l_Element_Ver_ID, l_Project_ID,
2870 l_Lag_Days, l_Comments, l_Sub_Type ;
2871 */
2872 EXIT WHEN Update_Publ_Ver%NOTFOUND;
2873 /* --bug 3970398
2874 IF P_Previous_Pub_Struc_Ver_ID IS NULL Then
2875 l_dest_task_ver_id := l_Obj_ID_From1;
2876 l_dest_proj_id := l_Obj_ID_From2;
2877 Else
2878 l_dest_task_ver_id := l_Obj_ID_To1;
2879 l_dest_proj_id := l_Obj_ID_To2;
2880 End IF;
2881 */
2882 l_object_relationship_id := NULL;
2883 PA_Object_Relationships_PKG.Insert_Row(
2884 p_user_id => FND_GLOBAL.USER_ID
2885 ,p_object_type_from => 'PA_TASKS'
2886 ,p_object_id_from1 => l_obj_id_from1
2887 ,p_object_id_from2 => l_obj_id_from2
2888 ,p_object_id_from3 => NULL
2889 ,p_object_id_from4 => NULL
2890 ,p_object_id_from5 => NULL
2891 ,p_object_type_to => 'PA_TASKS'
2892 ,p_object_id_to1 => l_obj_id_to1
2893 ,p_object_id_to2 => l_obj_id_to2
2894 ,p_object_id_to3 => NULL
2895 ,p_object_id_to4 => NULL
2896 ,p_object_id_to5 => NULL
2897 ,p_relationship_type => 'D'
2898 ,p_relationship_subtype => l_Sub_Type
2899 ,p_lag_day => l_Lag_Days
2900 ,p_imported_lag => NULL
2901 ,p_priority => Null
2902 ,p_pm_product_code => NULL
2903 ,x_object_relationship_id => l_object_relationship_id
2904 ,p_comments => l_comments
2905 ,p_status_code => 'PUBLISHED'
2906 ,x_return_status => x_return_status
2907 -- ,x_msg_count => x_msg_count
2908 -- ,x_msg_data => x_msg_data
2909 );
2910
2911 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2912 x_msg_count := FND_MSG_PUB.count_msg;
2913 /* IF x_msg_count = 1 then
2914 pa_interface_utils_pub.get_messages
2915 (p_encoded => FND_API.G_TRUE,
2916 p_msg_index => 1,
2917 p_msg_count => x_msg_count,
2918 p_msg_data => x_msg_data,
2919 p_data => l_data,
2920 p_msg_index_out => l_msg_index_out);
2921 x_msg_data := l_data;
2922 END IF; */
2923 Close Update_Publ_Ver;
2924 Raise FND_API.G_EXC_ERROR;
2925 END IF;
2926
2927 /*
2928 PA_RELATIONSHIP_PVT.Delete_Relationship (
2929 p_object_relationship_id => l_del_obj_rel_id
2930 ,p_record_version_number => l_Record_Ver_Number
2931 ,x_return_status => x_return_status
2932 ,x_msg_count => x_msg_count
2933 ,x_msg_data => x_msg_data
2934 );
2935
2936 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2937 x_msg_count := FND_MSG_PUB.count_msg;
2938 Close Update_Publ_Ver;
2939 Raise FND_API.G_EXC_ERROR;
2940 END IF;
2941 */
2942
2943 END LOOP;
2944 Close Update_Publ_Ver;
2945 --------------------------------- End of Step 3
2946 /*--bug 3970398
2947 Open Delete_Publ_Ver;
2948 Loop
2949 l_Relationship_ID := NULL;
2950 l_Record_Ver_Number := NULL;
2951 Fetch Delete_Publ_Ver
2952 INTO l_Relationship_ID, l_Record_Ver_Number;
2953 Exit When Delete_Publ_Ver%NOTFOUND;
2954 PA_RELATIONSHIP_PVT.Delete_Relationship (
2955 p_object_relationship_id => l_Relationship_ID
2956 ,p_record_version_number => l_Record_Ver_Number
2957 ,x_return_status => x_return_status
2958 ,x_msg_count => x_msg_count
2959 ,x_msg_data => x_msg_data
2960 );
2961
2962 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2963 x_msg_count := FND_MSG_PUB.count_msg;
2964 IF x_msg_count = 1 Then
2965 pa_interface_utils_pub.get_messages
2966 (p_encoded => FND_API.G_TRUE,
2967 p_msg_index => 1,
2968 p_msg_count => x_msg_count,
2969 p_msg_data => x_msg_data,
2970 p_data => l_data,
2971 p_msg_index_out => l_msg_index_out);
2972 x_msg_data := l_data;
2973 END IF;
2974 Raise FND_API.G_EXC_ERROR;
2975 END IF;
2976 End Loop;
2977 Close Delete_Publ_Ver;
2978 */
2979 EXCEPTION -- 4537865
2980 WHEN FND_API.G_EXC_ERROR THEN
2981 x_msg_count := FND_MSG_PUB.count_msg;
2982 x_return_status := FND_API.G_RET_STS_ERROR;
2983 WHEN OTHERS THEN
2984 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2985 x_msg_count := FND_MSG_PUB.count_msg;
2986 --put message
2987 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
2988 p_procedure_name => 'Copy_Intra_Dependency',
2989 p_error_text => SUBSTRB(SQLERRM,1,240));
2990 RAISE;
2991
2992 End Publish_Inter_Proj_Dep;
2993 --
2994 PROCEDURE Insert_Subproject_Association( p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
2995 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
2996 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
2997 ,p_validation_level IN VARCHAR2 := 100
2998 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
2999 ,p_debug_mode IN VARCHAR2 := 'N'
3000 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3001 ,p_src_proj_id IN NUMBER
3002 ,p_src_struc_wp_or_fin IN VARCHAR2
3003 ,p_src_struc_elem_id IN NUMBER
3004 ,p_src_struc_elem_ver_id IN NUMBER
3005 ,p_dest_proj_id IN NUMBER
3006 ,p_dest_struc_elem_id IN NUMBER
3007 ,p_dest_struc_elem_ver_id IN NUMBER
3008 ,p_src_task_elem_id IN NUMBER
3009 ,p_src_task_elem_ver_id IN NUMBER
3010 ,p_lnk_task_name_number IN VARCHAR2 --SMukka
3011 ,p_relationship_type IN VARCHAR2
3012 ,p_comment IN VARCHAR2 := PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR --Bug No 3668113
3013 ,x_lnk_task_elem_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3014 ,x_lnk_task_elem_ver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3015 ,x_object_relationship_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3016 ,x_pev_schedule_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3017 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3018 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
3019 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
3020 )
3021 IS
3022 l_msg_index_out NUMBER;
3023 -- l_msg_count NUMBER;
3024 -- l_msg_data VARCHAR2(250);
3025 l_data VARCHAR2(2000);
3026 l_scheduled_start_date DATE:= sysdate;
3027 l_scheduled_finish_date DATE:= sysdate;
3028 l_upd_prog_grp_status NUMBER:=0;
3029 l_tasks_ver_ids PA_NUM_1000_NUM := PA_NUM_1000_NUM();
3030 l_sharing_code VARCHAR2(30);
3031
3032 --bug 4296915
3033 CURSOR check_child_pub
3034 IS
3035 SELECT 'x'
3036 FROM pa_proj_elem_ver_structure
3037 WHERE project_id=p_dest_proj_id
3038 AND element_version_id = p_dest_struc_elem_ver_id
3039 AND status_code = 'STRUCTURE_PUBLISHED'
3040 ;
3041 l_dummy VARCHAR2(1);
3042 --end bug 4296915
3043
3044 -- Bug # 4329284.
3045
3046 cursor cur_proj_name (c_project_id NUMBER) is
3047 select ppa.name
3048 from pa_projects_all ppa
3049 where ppa.project_id = c_project_id;
3050
3051 l_proj_name VARCHAR2(30);
3052 l_prog_name VARCHAR2(30);
3053
3054 -- Bug # 4329284.
3055
3056 BEGIN
3057 --
3058 IF (p_debug_mode = 'Y') THEN
3059 pa_debug.debug('PA_RELATIONSHIP_PVT.Insert_Subproject_Association begin');
3060 END IF;
3061 --
3062 IF (p_commit = FND_API.G_TRUE) THEN
3063 savepoint Insert_Subproject_Association;
3064 END IF;
3065 --
3066 IF (p_debug_mode = 'Y') THEN
3067 pa_debug.debug('Performing validations');
3068 pa_debug.debug('PA_TASK_PUB1.CREATE_TASK Src Proj Id => '||p_src_proj_id);
3069 pa_debug.debug('PA_TASK_PUB1.CREATE_TASK Src Structure Elem Id=> '||p_src_struc_elem_id);
3070 pa_debug.debug('PA_TASK_PUB1.CREATE_TASK Src Structure Elem Ver Id => '||p_src_struc_elem_ver_id);
3071 pa_debug.debug('Before PA_TASK_PUB1.CREATE_TASK Linking Task Name Number => '||p_lnk_task_name_number);
3072 END IF;
3073 --
3074 /* Creating linking task in the pa_proj_elements table*/
3075 PA_TASK_PUB1.CREATE_TASK
3076 ( p_validate_only => FND_API.G_FALSE
3077 ,p_object_type => 'PA_TASKS'
3078 ,p_project_id => p_src_proj_id
3079 ,p_structure_id => p_src_struc_elem_id --Proj_element_id of the parent structure
3080 ,p_ref_task_id => p_src_task_elem_id --proj_element_id of the ref task
3081 ,p_context => 'LINKED' -- for bug#11928067
3082 ,p_peer_or_sub => 'SUB'
3083 ,p_structure_version_id => p_src_struc_elem_ver_id
3084 ,p_task_number => substr(p_lnk_task_name_number,0,25)
3085 ,p_task_name => substr(p_lnk_task_name_number,0,240)
3086 ,p_task_manager_id => NULL
3087 ,p_task_manager_name => NULL
3088 ,p_link_task_flag => 'Y'
3089 ,x_task_id => x_lnk_task_elem_id
3090 ,x_return_status => x_return_status
3091 ,x_msg_count => x_msg_count
3092 ,x_msg_data => x_msg_data);
3093 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3094 x_msg_count := FND_MSG_PUB.count_msg;
3095 IF x_msg_count = 1 THEN
3096 pa_interface_utils_pub.get_messages
3097 (p_encoded => FND_API.G_TRUE,
3098 p_msg_index => 1,
3099 p_msg_count => x_msg_count,
3100 p_msg_data => x_msg_data,
3101 p_data => l_data,
3102 p_msg_index_out => l_msg_index_out);
3103 x_msg_data := l_data;
3104 END IF;
3105 RAISE FND_API.G_EXC_ERROR;
3106 END IF;
3107 --
3108 --Added the following code
3109 -- Modified from substr(x_lnk_task_elem_id,-1,1) to x_lnk_task_elem_id for bug #4480013
3110 UPDATE PA_PROJ_ELEMENTS
3111 SET ELEMENT_NUMBER = substr(p_lnk_task_name_number,0,25)||x_lnk_task_elem_id
3112 WHERE PROJ_ELEMENT_ID = x_lnk_task_elem_id;
3113 IF SQL%NOTFOUND THEN
3114 x_return_status:=FND_API.G_RET_STS_ERROR;
3115 END IF;
3116 --
3117 IF (p_debug_mode = 'Y') THEN
3118 pa_debug.debug('After Call To PA_TASK_PUB1.CREATE_TASK Return Status => '||x_return_status);
3119 pa_debug.debug('After Call To PA_TASK_PUB1.CREATE_TASK => '||x_lnk_task_elem_id);
3120 pa_debug.debug('PA_TASK_PUB1.CREATE_TASK_VERSION Src Structure Elem Id=> '||p_src_task_elem_ver_id);
3121 pa_debug.debug('PA_TASK_PUB1.CREATE_TASK_VERSION Linking Task Elem Id => '||x_lnk_task_elem_id);
3122 END IF;
3123 --
3124 /* Creating linking task in the pa_proj_element_versions and pa_object_relationships table */
3125 /* This API call create task in pa_proj_element_versions and creates relationship between */
3126 /* linking task and its parent task in the pa_object_relationships table */
3127 PA_TASK_PUB1.CREATE_TASK_VERSION
3128 ( p_validate_only => FND_API.G_FALSE
3129 ,p_validation_level => 0
3130 ,p_ref_task_version_id => p_src_task_elem_ver_id
3131 ,p_peer_or_sub => 'SUB'
3132 ,p_task_id => x_lnk_task_elem_id
3133 ,x_task_version_id => x_lnk_task_elem_ver_id
3134 ,x_return_status => x_return_status
3135 ,x_msg_count => x_msg_count
3136 ,x_msg_data => x_msg_data);
3137 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3138 x_msg_count := FND_MSG_PUB.count_msg;
3139 IF x_msg_count = 1 THEN
3140 pa_interface_utils_pub.get_messages
3141 (p_encoded => FND_API.G_TRUE,
3142 p_msg_index => 1,
3143 p_msg_count => x_msg_count,
3144 p_msg_data => x_msg_data,
3145 p_data => l_data,
3146 p_msg_index_out => l_msg_index_out);
3147 x_msg_data := l_data;
3148 END IF;
3149 RAISE FND_API.G_EXC_ERROR;
3150 END IF;
3151 --
3152 IF (p_debug_mode = 'Y') THEN
3153 pa_debug.debug('After Call To PA_TASK_PUB1.CREATE_TASK_VERSION Return Status => '||x_return_status);
3154 pa_debug.debug('After Call To PA_TASK_PUB1.CREATE_TASK_VERSION Linking Task Elem Id => '||x_lnk_task_elem_id);
3155 pa_debug.debug('After Call To PA_TASK_PUB1.CREATE_TASK_VERSION Linking Task Elem Ver Id=> '||x_lnk_task_elem_ver_id);
3156 pa_debug.debug('PA_TASK_PUB1.Create_Schedule_Version Linking Task Elem Ver Id=> '||x_lnk_task_elem_ver_id);
3157 END IF;
3158 --
3159 --bug 4279634
3160 --set chargeable to N
3161 l_sharing_code := PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(p_src_proj_id);
3162
3163 -- Begin fix for Bug # 4490532.
3164 -- Modifications to allow the collection of progress on those tasks in the parent project
3165 -- that have sub-projects linked to them.
3166
3167 /* Begin commenting out the code to set the chargeable_flag to 'N'.
3168
3169 IF (l_sharing_code = 'SHARE_FULL')
3170 OR (l_sharing_code = 'SHARE_PARTIAL') THEN
3171 IF PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_src_proj_id) = 'Y' THEN
3172 --IF no publishing version, set flag to N
3173 IF 'N' = PA_PROJECT_STRUCTURE_UTILS.CHECK_PUBLISHED_VER_EXISTS(p_src_proj_id, p_src_struc_elem_id) THEN
3174 UPDATE PA_TASKS
3175 SET
3176 CHARGEABLE_FLAG = 'N',
3177 RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
3178 last_updated_by = FND_GLOBAL.USER_ID,
3179 last_update_login = FND_GLOBAL.USER_ID,
3180 last_update_date = sysdate
3181 WHERE TASK_ID = p_src_task_elem_id;
3182 END IF;
3183 ELSE
3184 --set flag to N
3185 UPDATE PA_TASKS
3186 SET
3187 CHARGEABLE_FLAG = 'N',
3188 RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
3189 last_updated_by = FND_GLOBAL.USER_ID,
3190 last_update_login = FND_GLOBAL.USER_ID,
3191 last_update_date = sysdate
3192 WHERE TASK_ID = p_src_task_elem_id;
3193 END IF;
3194 ELSE --not share, check if financial only
3195 IF p_src_struc_wp_or_fin = 'FINANCIAL' THEN
3196 --set flag to N
3197 UPDATE PA_TASKS
3198 SET
3199 CHARGEABLE_FLAG = 'N',
3200 RECORD_VERSION_NUMBER = nvl(RECORD_VERSION_NUMBER,0)+1,
3201 last_updated_by = FND_GLOBAL.USER_ID,
3202 last_update_login = FND_GLOBAL.USER_ID,
3203 last_update_date = sysdate
3204 WHERE TASK_ID = p_src_task_elem_id;
3205 END IF;
3206 END IF;
3207
3208 End commenting out the code to set the chargeable_flag to 'N'. */
3209
3210 -- End fix for Bug # 4490532.
3211
3212 --end bug 4279634
3213
3214 /* Create recrod into work pa_proj_elem_ver_schedule table for workplan structure only*/
3215 IF p_src_struc_wp_or_fin = 'WORKPLAN' THEN
3216 PA_TASK_PUB1.Create_Schedule_Version
3217 ( p_validate_only =>FND_API.G_FALSE
3218 ,p_element_version_id =>x_lnk_task_elem_ver_id --task version of linking task
3219 ,p_scheduled_start_date =>l_scheduled_start_date
3220 ,p_scheduled_end_date =>l_scheduled_finish_date
3221 ,x_pev_schedule_id =>x_pev_schedule_id
3222 ,x_return_status =>x_return_status
3223 ,x_msg_count =>x_msg_count
3224 ,x_msg_data =>x_msg_data
3225 );
3226 IF (p_debug_mode = 'Y') THEN
3227 pa_debug.debug('new workplan attr for task after call to PA_TASK_PUB1.Create_Schedule_Version=> '||x_pev_schedule_id);
3228 END IF;
3229 --
3230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3231 x_msg_count := FND_MSG_PUB.count_msg;
3232 IF x_msg_count = 1 THEN
3233 pa_interface_utils_pub.get_messages
3234 (p_encoded => FND_API.G_TRUE,
3235 p_msg_index => 1,
3236 p_msg_count => x_msg_count,
3237 p_msg_data => x_msg_data,
3238 p_data => l_data,
3239 p_msg_index_out => l_msg_index_out);
3240 x_msg_data := l_data;
3241 END IF;
3242 RAISE FND_API.G_EXC_ERROR;
3243 END IF;
3244 END IF;
3245 --
3246 IF (p_debug_mode = 'Y') THEN
3247 pa_debug.debug('After Call To PA_TASK_PUB1.Create_Schedule_Version Return Status => '||x_return_status);
3248 pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Linking Task Elem Ver Id => '||x_lnk_task_elem_ver_id);
3249 pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Src Proj Id=> '||p_src_proj_id);
3250 pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Dest Struc Elem Ver Id=> '||p_dest_struc_elem_ver_id);
3251 pa_debug.debug('PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Dest Proj Id=> '||p_dest_proj_id);
3252 END IF;
3253 --
3254 /* This API call create relationship between linking task and destination structure vesion */
3255 /* in the pa_object_relationships table */
3256 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
3257 p_user_id => FND_GLOBAL.USER_ID
3258 ,p_object_type_from => 'PA_TASKS'
3259 ,p_object_id_from1 => x_lnk_task_elem_ver_id
3260 ,p_object_id_from2 => p_src_proj_id
3261 ,p_object_id_from3 => NULL
3262 ,p_object_id_from4 => NULL
3263 ,p_object_id_from5 => NULL
3264 ,p_object_type_to => 'PA_STRUCTURES'
3265 ,p_object_id_to1 => p_dest_struc_elem_ver_id
3266 ,p_object_id_to2 => p_dest_proj_id
3267 ,p_object_id_to3 => NULL
3268 ,p_object_id_to4 => NULL
3269 ,p_object_id_to5 => NULL
3270 ,p_relationship_type => p_relationship_type
3271 ,p_relationship_subtype => NULL
3272 ,p_lag_day => NULL
3273 ,p_imported_lag => NULL
3274 ,p_priority => NULL
3275 ,p_pm_product_code => NULL
3276 ,x_object_relationship_id => x_object_relationship_id
3277 ,x_return_status => x_return_status
3278 -- ,p_comments => null
3279 ,p_comments => p_comment --Bug No 3668113
3280 ,p_status_code => null
3281 );
3282 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3283 x_msg_count := FND_MSG_PUB.count_msg;
3284 IF x_msg_count = 1 THEN
3285 pa_interface_utils_pub.get_messages
3286 (p_encoded => FND_API.G_TRUE,
3287 p_msg_index => 1,
3288 p_msg_count => x_msg_count,
3289 p_msg_data => x_msg_data,
3290 p_data => l_data,
3291 p_msg_index_out => l_msg_index_out);
3292 x_msg_data := l_data;
3293 END IF;
3294 RAISE FND_API.G_EXC_ERROR;
3295 END IF;
3296 --
3297 /* 4541039
3298 --bug 4238036
3299 IF p_src_struc_wp_or_fin = 'WORKPLAN' THEN
3300 l_tasks_ver_ids.extend(1);
3301 l_tasks_ver_ids(1) := p_src_task_elem_ver_id;
3302
3303 --bug 4296915 do not rollup from working to working structure version.
3304 IF p_dest_struc_elem_ver_id IS NOT NULL
3305 THEN
3306 OPEN check_child_pub;
3307 FETCH check_child_pub INTO l_dummy;
3308 IF check_child_pub%FOUND
3309 THEN
3310 --end bug 4296915
3311 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject(
3312 p_debug_mode => p_debug_mode,
3313 p_element_versions => l_tasks_ver_ids,
3314 x_return_status => x_return_status,
3315 x_msg_count => x_msg_count,
3316 x_msg_data => x_msg_data
3317 );
3318
3319 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3320 x_msg_count := FND_MSG_PUB.count_msg;
3321 IF x_msg_count = 1 THEN
3322 pa_interface_utils_pub.get_messages
3323 (p_encoded => FND_API.G_TRUE,
3324 p_msg_index => 1,
3325 p_msg_count => x_msg_count,
3326 p_msg_data => x_msg_data,
3327 p_data => l_data,
3328 p_msg_index_out => l_msg_index_out);
3329 x_msg_data := l_data;
3330 END IF;
3331 RAISE FND_API.G_EXC_ERROR;
3332 END IF;
3333 --bug 4296915
3334 END IF;
3335 CLOSE check_child_pub;
3336 END IF;
3337 --bug 4296915
3338
3339 END IF;
3340 --end bug 4238036
3341 end bug 4541039 */
3342
3343 --Bug No 3450684
3344 BEGIN
3345 IF p_validation_level > 0 THEN
3346 l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(x_object_relationship_id,
3347 'ADD');
3348 IF l_upd_prog_grp_status < 0 THEN
3349
3350 -- Bug # 4329284.
3351
3352 open cur_proj_name(p_src_proj_id);
3353 fetch cur_proj_name into l_prog_name;
3354 close cur_proj_name;
3355
3356 open cur_proj_name(p_dest_proj_id);
3357 fetch cur_proj_name into l_proj_name;
3358 close cur_proj_name;
3359
3360 -- Bug # 4329284.
3361
3362 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3363 RAISE FND_API.G_EXC_ERROR;
3364 END IF;
3365 IF (p_debug_mode = 'Y') THEN
3366 pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
3367 END IF;
3368 END IF;
3369 EXCEPTION
3370
3371 -- Begin fix for Bug # 4485908.
3372
3373 WHEN FND_API.G_EXC_ERROR THEN
3374
3375 RAISE FND_API.G_EXC_ERROR;
3376
3377 -- End fix for Bug # 4485908.
3378
3379 WHEN OTHERS THEN
3380 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
3381 p_procedure_name => 'Insert_Subproject_Association',
3382 p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
3383 RAISE FND_API.G_EXC_ERROR;
3384 END;
3385 --
3386 IF (p_debug_mode = 'Y') THEN
3387 pa_debug.debug('After Call To PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW Return Status => '||x_return_status);
3388 END IF;
3389 --
3390 x_return_status := FND_API.G_RET_STS_SUCCESS;
3391 --
3392 IF (p_commit = FND_API.G_TRUE) THEN
3393 COMMIT;
3394 END IF;
3395 --
3396 IF (p_debug_mode = 'Y') THEN
3397 pa_debug.debug('PA_RELATIONSHIP_PVT.Insert_Subproject_Association END');
3398 END IF;
3399 --
3400 EXCEPTION
3401 WHEN FND_API.G_EXC_ERROR THEN
3402 IF (p_commit = FND_API.G_TRUE) THEN
3403 ROLLBACK to Insert_Subproject_Association;
3404 END IF;
3405 -- RESET OUT PARAMS 4537865
3406 x_lnk_task_elem_id := NULL ;
3407 x_lnk_task_elem_ver_id := NULL ;
3408 x_object_relationship_id := NULL ;
3409 x_pev_schedule_id := NULL;
3410
3411 x_msg_count := FND_MSG_PUB.count_msg;
3412 x_return_status := FND_API.G_RET_STS_ERROR;
3413 WHEN OTHERS THEN
3414 IF (p_commit = FND_API.G_TRUE) THEN
3415 ROLLBACK to Insert_Subproject_Association;
3416 END IF;
3417 -- RESET OUT PARAMS 4537865
3418 x_lnk_task_elem_id := NULL ;
3419 x_lnk_task_elem_ver_id := NULL ;
3420 x_object_relationship_id := NULL ;
3421 x_pev_schedule_id := NULL;
3422
3423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3424 x_msg_count := FND_MSG_PUB.count_msg;
3425 --put message
3426 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
3427 p_procedure_name => 'Insert_Subproject_Association',
3428 p_error_text => SUBSTRB(SQLERRM,1,240));
3429 RAISE;
3430 END Insert_Subproject_Association;
3431 --
3432 --
3433 --
3434 -- API name : Create_Subproject_Association
3435 -- Type : Private Procedure
3436 -- Pre-reqs : None
3437 -- Return Value : N/A
3438 -- Parameters
3439 -- p_api_version IN NUMBER :=1.0
3440 -- p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE
3441 -- p_validate_only IN VARCHAR2 :=FND_API.G_TRUE
3442 -- p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
3443 -- p_calling_module IN VARCHAR2 :='SELF_SERVICE'
3444 -- p_commit IN VARCHAR2 :=FND_API.G_FALSE
3445 -- p_debug_mode IN VARCHAR2 :='N'
3446 -- p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
3447 -- p_src_proj_id IN pa_projects_all.project_id%type
3448 -- p_task_ver_id IN pa_proj_element_versions.element_version_id%type
3449 -- p_dest_proj_id IN pa_projects_all.project_id%type
3450 -- p_dest_proj_name IN pa_projects_all.name%type
3451 -- p_comment IN pa_object_relationships.comments%type
3452 -- x_return_status OUT VARCHAR2
3453 -- x_msg_count OUT NUMBER
3454 -- x_msg_data OUT VARCHAR2
3455 --
3456 -- History
3457 --
3458 -- 20-Feb-04 Smukka -Created
3459 -- -Created this procedure for subproject association
3460 --
3461 -- FPM bug 3450684
3462 --
3463 -- 03-DEC-2008 rkartha Bug#7427161: Modified the declaration of l_task_name with PA_PROJ_ELEMENTS.NAME%TYPE
3464 -- so as to avoid the numeric or value error.
3465 --
3466 Procedure Create_Subproject_Association(p_api_version IN NUMBER :=1.0,
3467 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
3468 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
3469 -- p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
3470 p_validation_level IN VARCHAR2 := 100,
3471 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
3472 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
3473 p_debug_mode IN VARCHAR2 :='N',
3474 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
3475 p_src_proj_id IN pa_projects_all.project_id%type,
3476 p_task_ver_id IN pa_proj_element_versions.element_version_id%type,
3477 p_dest_proj_id IN pa_projects_all.project_id%type,
3478 p_dest_proj_name IN pa_projects_all.name%type,
3479 p_comment IN pa_object_relationships.comments%type,
3480 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
3481 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
3482 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
3483 IS
3484 l_src_proj_sharing_code pa_projects_all.structure_sharing_code%type;
3485 l_dest_proj_sharing_code pa_projects_all.structure_sharing_code%type;
3486 --
3487 l_src_struc_elem_id pa_proj_elements.proj_element_id%type;
3488 l_src_struc_elem_ver_id pa_proj_element_versions.element_version_id%type;
3489 l_src_task_elem_id pa_proj_elements.proj_element_id%type;
3490 l_src_task_financial_flag pa_proj_element_versions.financial_task_flag%type;
3491 --
3492 l_dest_fin_str_ver_id pa_proj_element_versions.element_version_id%type:=0;
3493 l_dest_wp_str_ver_id pa_proj_element_versions.element_version_id%type:=0;
3494 l_dest_wp_struct_element_id pa_proj_elements.proj_element_id%type:=0;
3495 l_dest_fin_struct_element_id pa_proj_elements.proj_element_id%type:=0;
3496 --
3497 l_parent_strucutre_version_id pa_proj_element_versions.element_version_id%type;
3498 --
3499 l_src_str_fin_enable_fl CHAR(1):='N';
3500 l_src_str_wp_enable_fl CHAR(1):='N';
3501 l_row_id VARCHAR2(100);
3502 --
3503 l_lnk_task_elem_id number;
3504 l_lnk_task_elem_ver_id pa_proj_element_versions.element_version_id%type;
3505 l_pev_schedule_id number;
3506 l_task_name_number varchar2(240);
3507 l_msg_count NUMBER;
3508 l_msg_data varchar2(250);
3509 l_data VARCHAR2(2000);
3510 l_msg_index_out NUMBER;
3511 x_object_relationship_id pa_object_relationships.object_relationship_id%type;
3512
3513 l_time_phase1 VARCHAR2(1);
3514 l_time_phase2 VARCHAR2(1);
3515
3516 --bug 4297370
3517 CURSOR cur_period_duration(cp_project_id NUMBER)
3518 IS
3519 SELECT imp.period_set_name pa_period_set_name
3520 ,imp.pa_period_type
3521 , sob.period_set_name gl_period_set_name
3522 , sob.accounted_period_type
3523 FROM
3524 pa_implementations_all imp
3525 , pa_projects_all prj
3526 , gl_sets_of_books sob
3527 WHERE 1=1
3528 AND prj.org_id = imp.org_id --MOAC Changes: Bug 4363092: removed nvl usage with org_id
3529 AND prj.project_id = cp_project_id
3530 AND sob.set_of_books_id = imp.set_of_books_id
3531 ;
3532
3533 l_src_period_duration cur_period_duration%ROWTYPE;
3534 l_dest_period_duration cur_period_duration%ROWTYPE;
3535 --end 4297370
3536
3537 --bug 4370533 --Issue #3
3538 l_dest_published_wp_str_id NUMBER;
3539 --bug 4370533 --Issue #3
3540 l_create_relationship_ok VARCHAR2(1):='Y';--4473103
3541
3542 -- Bug # 4329284.
3543
3544 cursor cur_proj_name (c_project_id NUMBER) is
3545 select ppa.name
3546 from pa_projects_all ppa
3547 where ppa.project_id = c_project_id;
3548
3549 cursor cur_task_name (c_task_ver_id NUMBER) is
3550 select ppe.name
3551 from pa_proj_elements ppe, pa_proj_element_versions ppev
3552 where ppe.project_id = ppev.project_id
3553 and ppe.proj_element_id = ppev.proj_element_id
3554 and ppev.element_version_id = c_task_ver_id;
3555
3556 l_proj_name VARCHAR2(30);
3557 l_prog_name VARCHAR2(30);
3558 -- l_task_name VARCHAR2(30); /* Bug#7427161 */
3559
3560 l_task_name PA_PROJ_ELEMENTS.NAME%TYPE := NULL; /* Bug#7427161 */
3561
3562 -- Bug # 4329284.
3563
3564 BEGIN
3565 IF (p_debug_mode = 'Y') THEN
3566 pa_debug.debug('PA_RELATIONSHIP_PVT.Create_Subproject_Association begin');
3567 END IF;
3568 --
3569 IF (p_commit = FND_API.G_TRUE) THEN
3570 savepoint Create_Subproject_Ass_pvt;
3571 END IF;
3572 --
3573 IF (p_debug_mode = 'Y') THEN
3574 pa_debug.debug('Performing validations');
3575 pa_debug.debug('The value of the passed src proj id=> '||p_src_proj_id);
3576 pa_debug.debug('The value of the passed src task ver id=> '||p_task_ver_id);
3577 pa_debug.debug('The value of the passed dest proj id=>'||p_dest_proj_id);
3578 pa_debug.debug('The value of the passed dest proj name id=> '||p_dest_proj_name);
3579 pa_debug.debug('The value of the passed comments=> '||p_comment);
3580 END IF;
3581 --
3582 -- Check for source structure type
3583 l_src_proj_sharing_code:=PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(p_src_proj_id);
3584 --
3585 --
3586 IF (p_debug_mode = 'Y') THEN
3587 pa_debug.debug('The src project id value => '||p_src_proj_id);
3588 pa_debug.debug('The src project sharing code value => '||l_src_proj_sharing_code);
3589 pa_debug.debug('The value of src task ver id => '||p_task_ver_id);
3590 END IF;
3591 --
3592 -- l_proj_element_id = structure element id
3593 -- l_parent_strucutre_version_id=parent structure version id
3594 --
3595
3596 -- Bug # 4329284.
3597
3598 open cur_proj_name(p_src_proj_id);
3599 fetch cur_proj_name into l_prog_name;
3600 close cur_proj_name;
3601
3602 open cur_proj_name(p_dest_proj_id);
3603 fetch cur_proj_name into l_proj_name;
3604 close cur_proj_name;
3605
3606 open cur_task_name(p_task_ver_id);
3607 fetch cur_task_name into l_task_name;
3608 close cur_task_name;
3609
3610 -- Bug # 4329284.
3611
3612 BEGIN
3613 SELECT ppev2.proj_element_id,
3614 ppev1.parent_structure_version_id,
3615 ppev1.FINANCIAL_TASK_FLAG,
3616 ppev1.proj_element_id
3617 INTO l_src_struc_elem_id,
3618 l_src_struc_elem_ver_id,
3619 l_src_task_financial_flag,
3620 l_src_task_elem_id
3621 FROM pa_proj_element_versions ppev1,
3622 pa_proj_element_versions ppev2
3623 WHERE ppev1.element_version_id = p_task_ver_id
3624 AND ppev1.object_type = 'PA_TASKS'
3625 AND ppev1.project_id = p_src_proj_id
3626 AND ppev2.element_version_id = ppev1.parent_structure_version_id
3627 AND ppev2.project_id = ppev2.project_id
3628 AND ppev2.object_type = 'PA_STRUCTURES';
3629 EXCEPTION
3630 WHEN OTHERS THEN
3631 RAISE;
3632 END;
3633 --
3634 IF (p_debug_mode = 'Y') THEN
3635 pa_debug.debug('The src structure elem id => '||l_src_struc_elem_id);
3636 pa_debug.debug('The src strcuture elem version id => '||l_src_struc_elem_ver_id);
3637 pa_debug.debug('The value of src task financial flag => '||l_src_task_financial_flag);
3638 pa_debug.debug('The value of src task elem id => '||l_src_task_elem_id);
3639 END IF;
3640 --
3641 /* IF PA_PROJECT_STRUCTURE_UTILS.get_element_struc_type(p_src_proj_id,p_task_ver_id,'PA_TASKS') = 'WORKPLAN' THEN
3642 l_src_str_wp_enable_fl:='Y';
3643 END IF;*/
3644 --
3645 /* IF PA_PROJECT_STRUCTURE_UTILS.get_element_struc_type(p_src_proj_id,p_task_ver_id,'PA_TASKS') = 'FINANCIAL' THEN
3646 l_src_str_fin_enable_fl:='Y';
3647 END IF;*/
3648 --
3649 IF ('Y' = PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure(l_src_struc_elem_id, 'WORKPLAN')) THEN
3650 l_src_str_wp_enable_fl:='Y';
3651 END IF;
3652
3653 IF ('Y' = PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Structure(l_src_struc_elem_id, 'FINANCIAL')) THEN
3654 l_src_str_fin_enable_fl:='Y';
3655 END IF;
3656 --
3657 IF (p_debug_mode = 'Y') THEN
3658 pa_debug.debug('The src str wp enable flag => '||l_src_str_wp_enable_fl);
3659 pa_debug.debug('The src str fin enable flag => '||l_src_str_fin_enable_fl);
3660 END IF;
3661
3662 --Bug 3912783:
3663 IF (PA_RELATIONSHIP_UTILS.Check_proj_currency_identical(p_src_proj_id,p_dest_proj_id) = 'N') THEN
3664 -- PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_PRJ_CURR','PROJ',l_proj_name,'TASK',l_task_name,'PROG',l_prog_name); -- Bug # 4329284.
3665 PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_PRJ_CURR','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4871876.
3666 RAISE FND_API.G_EXC_ERROR;
3667 END IF;
3668 --end bug 3912783
3669
3670 --
3671 -- Check for target structure type
3672 -- Get the latest published structure, if there is one for the given project_id(p_dest_proj_id)
3673 --
3674 l_dest_proj_sharing_code:=PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(p_dest_proj_id);
3675 --
3676 l_dest_fin_str_ver_id:=PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(p_dest_proj_id);
3677 l_dest_wp_str_ver_id:=PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_WP_VERSION(p_dest_proj_id);
3678
3679 --bug 4370533 --Issue #3
3680 l_dest_published_wp_str_id := l_dest_wp_str_ver_id;
3681 --bug 4370533 --Issue #3
3682
3683 --
3684 /* IF l_dest_fin_str_ver_id IS NULL AND l_dest_wp_str_ver_id IS NULL THEN
3685 --get current working wp ver
3686 l_dest_wp_str_ver_id :=PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_dest_proj_id);
3687 --get only version for fin
3688 PA_PROJECT_STRUCTURE_UTILS.Get_Financial_Version(p_dest_proj_id,l_dest_fin_str_ver_id);
3689 END IF;*/
3690
3691 IF l_dest_fin_str_ver_id IS NULL THEN --SMukka added if block
3692 --get only version for fin
3693 PA_PROJECT_STRUCTURE_UTILS.Get_Financial_Version(p_dest_proj_id,l_dest_fin_str_ver_id);
3694
3695 -- Begin fix for Bug # 4426392.
3696
3697 if (l_dest_fin_str_ver_id = -1) then
3698
3699 l_dest_fin_str_ver_id := null;
3700
3701 end if;
3702
3703 -- End fix for Bug # 4426392.
3704
3705 END IF;
3706 --
3707 IF l_dest_wp_str_ver_id IS NULL THEN --SMukka added if block
3708 --get current working wp ver
3709 l_dest_wp_str_ver_id :=PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(p_dest_proj_id);
3710 END IF;
3711 --
3712 IF (p_debug_mode = 'Y') THEN
3713 pa_debug.debug('Dest WP Str Ver Id => '||l_dest_wp_str_ver_id);
3714 END IF;
3715 --
3716 -- IF l_dest_wp_str_ver_id IS NOT NULL THEN --Commented
3717 IF (l_dest_wp_str_ver_id >=0) THEN --SMukka
3718 BEGIN
3719 SELECT proj_element_id
3720 INTO l_dest_wp_struct_element_id
3721 FROM pa_proj_element_versions
3722 WHERE element_version_id = l_dest_wp_str_ver_id;
3723 EXCEPTION
3724 WHEN NO_DATA_FOUND THEN
3725 RAISE;
3726 WHEN OTHERS THEN
3727 RAISE;
3728 END;
3729 IF (p_debug_mode = 'Y') THEN
3730 pa_debug.debug('Dest WP Str element Id => '||l_dest_wp_struct_element_id);
3731 END IF;
3732 END IF;
3733 --
3734 IF (p_debug_mode = 'Y') THEN
3735 pa_debug.debug('Dest FIN Str Ver Id => '||l_dest_fin_str_ver_id);
3736 END IF;
3737 --
3738 -- IF l_dest_fin_str_ver_id IS NOT NULL THEN --SMukka
3739 IF (l_dest_fin_str_ver_id >= 0) THEN --SMukka
3740 BEGIN
3741 SELECT proj_element_id
3742 INTO l_dest_fin_struct_element_id
3743 FROM pa_proj_element_versions
3744 WHERE element_version_id = l_dest_fin_str_ver_id;
3745 EXCEPTION
3746 WHEN NO_DATA_FOUND THEN
3747 RAISE;
3748 WHEN OTHERS THEN
3749 RAISE;
3750 END;
3751 IF (p_debug_mode = 'Y') THEN
3752 pa_debug.debug('Dest FIN Str element Id => '||l_dest_fin_struct_element_id);
3753 END IF;
3754 END IF;
3755 --
3756 -- Create linking task
3757 --
3758 --bug 4272730
3759 IF l_src_str_wp_enable_fl = 'Y' THEN
3760 l_time_phase1 := PA_FIN_PLAN_UTILS.Get_wp_bv_time_phase(l_src_struc_elem_ver_id);
3761 IF l_dest_wp_str_ver_id IS NOT NULL THEN
3762 l_time_phase2 := PA_FIN_PLAN_UTILS.Get_wp_bv_time_phase(l_dest_wp_str_ver_id);
3763 IF (l_time_phase1 <> l_time_phase2) THEN
3764 -- PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_TIME_PHASE','PROJ',l_proj_name,'TASK',l_task_name,'PROG',l_prog_name); -- Bug # 4329284.
3765 PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_TIME_PHASE','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4871876.
3766 RAISE FND_API.G_EXC_ERROR;
3767 END IF;
3768 END IF;
3769 END IF;
3770 --end bug 4272730
3771
3772 --bug 4297370
3773 IF l_src_str_wp_enable_fl = 'Y'
3774 THEN
3775 OPEN cur_period_duration(p_src_proj_id);
3776 FETCH cur_period_duration INTO l_src_period_duration;
3777 CLOSE cur_period_duration;
3778
3779 OPEN cur_period_duration(p_dest_proj_id);
3780 FETCH cur_period_duration INTO l_dest_period_duration;
3781 CLOSE cur_period_duration;
3782
3783 IF l_time_phase1 = 'P' AND l_time_phase2 = 'P'
3784 THEN
3785 IF l_src_period_duration.pa_period_set_name <> l_dest_period_duration.pa_period_set_name OR
3786 l_src_period_duration.pa_period_type <> l_dest_period_duration.pa_period_type
3787 THEN
3788 PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_PA_CAL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3789 RAISE FND_API.G_EXC_ERROR;
3790 END IF;
3791
3792 ELSIF l_time_phase1 = 'G' AND l_time_phase2 = 'G'
3793 THEN
3794 IF l_src_period_duration.gl_period_set_name <> l_dest_period_duration.gl_period_set_name OR
3795 l_src_period_duration.accounted_period_type <> l_dest_period_duration.accounted_period_type
3796 THEN
3797 PA_UTILS.ADD_MESSAGE('PA','PA_PS_LINK_DIFF_GL_CAL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3798 RAISE FND_API.G_EXC_ERROR;
3799 END IF;
3800 END IF;
3801 END IF;
3802 --end bug 4297370
3803
3804 l_task_name_number := substr(fnd_date.date_to_canonical(sysdate),0,25);
3805 --
3806 --
3807 IF (p_debug_mode = 'Y') THEN
3808 pa_debug.debug('Before call to Insert_Subproject_Association');
3809 pa_debug.debug('Before call to ISPA Src Project Id => '||p_src_proj_id);
3810 pa_debug.debug('Before call to ISPA Src Strcuture elem id => '||l_src_struc_elem_id);
3811 pa_debug.debug('Before call to ISPA Src Structure elem version id => '||l_src_struc_elem_ver_id);
3812 pa_debug.debug('Before call to ISPA Src Task elem id => '||l_src_task_elem_id);
3813 pa_debug.debug('Before call to ISPA Src Task elem version id => '||p_task_ver_id);
3814 pa_debug.debug('Before call to ISPA Src Task Financial Flag => '||l_src_task_financial_flag);
3815 pa_debug.debug('Before call to ISPA Dest Project id => '||p_dest_proj_id);
3816 pa_debug.debug('Before call to ISPA Linking Task element id => '||l_lnk_task_elem_id);
3817 pa_debug.debug('Before call to ISPA Linking Task Element Version Id => '||l_lnk_task_elem_ver_id);
3818 pa_debug.debug('Before call to ISPA Linking Task Name Number => '||l_task_name_number);
3819 pa_debug.debug('Before call to ISPA Dest wp Structure Element id => '||l_dest_wp_struct_element_id);
3820 pa_debug.debug('Before call to ISPA Dest wp Strcuture ver id => '||l_dest_wp_str_ver_id);
3821 pa_debug.debug('Before call to ISPA Dest wp Structure Element id => '||l_dest_fin_struct_element_id);
3822 pa_debug.debug('Before call to ISPA Dest wp Strcuture ver id => '||l_dest_fin_str_ver_id);
3823 pa_debug.debug('Before call to ISPA Src Structure WP Enable Flag => '||l_src_str_wp_enable_fl);
3824 pa_debug.debug('Before call to ISPA Dest Structure FIN Enable Flag => '||l_src_str_fin_enable_fl);
3825 END IF;
3826 /* Bug 4473103 : Undone the fix for 3983361 and redo
3827 --
3828 --
3829 --bug 3983361
3830 IF p_validation_level > 0 THEN
3831 IF PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id) = 'N' THEN --SMukka
3832
3833 if (FND_MSG_PUB.count_msg = 0) then -- Fix for Bug # 4256435.
3834
3835 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3836
3837 end if; -- Fix for Bug # 4256435.
3838
3839 RAISE FND_API.G_EXC_ERROR;
3840 END IF;
3841 END IF;
3842 --end bug 3983361
3843 */
3844
3845 -- 4473103 : Begin
3846 IF l_src_str_wp_enable_fl = 'Y' AND l_dest_wp_str_ver_id IS NOT NULL THEN
3847 l_create_relationship_ok := PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id,'WORKPLAN');
3848 IF l_create_relationship_ok = 'N' THEN
3849 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3850 RAISE FND_API.G_EXC_ERROR;
3851 END IF;
3852 END IF;
3853
3854 IF l_src_str_fin_enable_fl = 'Y' AND l_dest_fin_str_ver_id IS NOT NULL AND l_src_task_financial_flag='Y' THEN
3855 l_create_relationship_ok := PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id,'FINANCIAL');
3856 IF l_create_relationship_ok = 'N' THEN
3857 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3858 RAISE FND_API.G_EXC_ERROR;
3859 END IF;
3860 END IF;
3861 -- 4473103 : End
3862
3863 IF l_src_proj_sharing_code = 'SHARE_FULL' AND l_dest_proj_sharing_code = 'SHARE_FULL' THEN
3864 IF (p_debug_mode = 'Y') THEN
3865 pa_debug.debug('Into block where both src and dest proj are SHARE_FULL');
3866 END IF;
3867 /* For workplan */
3868 --Validation for create sub project association
3869 --bug 3716615
3870 IF (p_debug_mode = 'Y') THEN
3871 pa_debug.debug('Before call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
3872 END IF;
3873 /* --bug 3983361
3874 IF p_validation_level > 0 THEN
3875 IF PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id) = 'N' THEN --SMukka
3876 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3877 RAISE FND_API.G_EXC_ERROR;
3878 END IF;
3879 END IF;
3880 */
3881 IF (p_debug_mode = 'Y') THEN
3882 pa_debug.debug('After call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
3883 END IF;
3884 --end bug 3716615
3885 PA_RELATIONSHIP_PVT.Insert_Subproject_Association
3886 ( p_init_msg_list => p_init_msg_list
3887 ,p_commit => p_commit
3888 ,p_validate_only => p_validate_only
3889 ,p_validation_level => p_validation_level
3890 ,p_calling_module => p_calling_module
3891 ,p_debug_mode => p_debug_mode
3892 ,p_max_msg_count => p_max_msg_count
3893 ,p_src_proj_id => p_src_proj_id
3894 ,p_src_struc_wp_or_fin => 'WORKPLAN'
3895 ,p_src_struc_elem_id => l_src_struc_elem_id
3896 ,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
3897 ,p_src_task_elem_id => l_src_task_elem_id
3898 ,p_src_task_elem_ver_id => p_task_ver_id
3899 ,p_dest_proj_id => p_dest_proj_id
3900 ,p_dest_struc_elem_id => l_dest_wp_struct_element_id
3901 ,p_dest_struc_elem_ver_id => l_dest_wp_str_ver_id
3902 ,x_lnk_task_elem_id => l_lnk_task_elem_id
3903 ,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
3904 ,p_lnk_task_name_number => l_task_name_number
3905 ,p_relationship_type => 'LW'
3906 ,p_comment => p_comment --Bug No 3668113
3907 ,x_object_relationship_id => x_object_relationship_id
3908 ,x_pev_schedule_id => l_pev_schedule_id
3909 ,x_return_status => x_return_status
3910 ,x_msg_count => x_msg_count
3911 ,x_msg_data => x_msg_data
3912 );
3913 --
3914 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3915 x_msg_count := FND_MSG_PUB.count_msg;
3916 IF x_msg_count = 1 then
3917 pa_interface_utils_pub.get_messages
3918 (p_encoded => FND_API.G_TRUE,
3919 p_msg_index => 1,
3920 p_msg_count => x_msg_count,
3921 p_msg_data => x_msg_data,
3922 p_data => l_data,
3923 p_msg_index_out => l_msg_index_out);
3924 x_msg_data := l_data;
3925 END IF;
3926 raise FND_API.G_EXC_ERROR;
3927 END IF;
3928 --
3929 /* For Financial */
3930 -- IF l_src_task_financial_flag='Y' THEN --No need to check for fully shared project
3931 IF (p_debug_mode = 'Y') THEN
3932 pa_debug.debug('Into block where both src and dest proj are SHARE_FULL');
3933 pa_debug.debug('Into fin block where both src and dest proj are SHARE_FULL');
3934 END IF;
3935
3936 --bug 3716615
3937 IF (p_debug_mode = 'Y') THEN
3938 pa_debug.debug('Before call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
3939 END IF;
3940 /* --bug 3983361
3941 IF p_validation_level > 0 THEN
3942 IF PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id, 'FINANCIAL') = 'N' THEN --SMukka
3943 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
3944 RAISE FND_API.G_EXC_ERROR;
3945 END IF;
3946 END IF;
3947 */
3948 IF (p_debug_mode = 'Y') THEN
3949 pa_debug.debug('After call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
3950 END IF;
3951 --end bug 3716615
3952
3953 PA_RELATIONSHIP_PVT.Insert_Subproject_Association
3954 ( p_init_msg_list => p_init_msg_list
3955 ,p_commit => p_commit
3956 ,p_validate_only => p_validate_only
3957 ,p_validation_level => p_validation_level
3958 ,p_calling_module => p_calling_module
3959 ,p_debug_mode => p_debug_mode
3960 ,p_max_msg_count => p_max_msg_count
3961 ,p_src_proj_id => p_src_proj_id
3962 ,p_src_struc_wp_or_fin => 'FINANCIAL'
3963 ,p_src_struc_elem_id => l_src_struc_elem_id
3964 ,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
3965 ,p_src_task_elem_id => l_src_task_elem_id
3966 ,p_src_task_elem_ver_id => p_task_ver_id
3967 ,p_dest_proj_id => p_dest_proj_id
3968 ,p_dest_struc_elem_id => l_dest_fin_struct_element_id
3969 ,p_dest_struc_elem_ver_id => l_dest_fin_str_ver_id
3970 ,x_lnk_task_elem_id => l_lnk_task_elem_id
3971 ,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
3972 ,p_lnk_task_name_number => l_task_name_number
3973 ,p_relationship_type => 'LF'
3974 ,p_comment => p_comment --Bug No 3668113
3975 ,x_object_relationship_id => x_object_relationship_id
3976 ,x_pev_schedule_id => l_pev_schedule_id
3977 ,x_return_status => x_return_status
3978 ,x_msg_count => x_msg_count
3979 ,x_msg_data => x_msg_data
3980 );
3981 --
3982 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3983 x_msg_count := FND_MSG_PUB.count_msg;
3984 IF x_msg_count = 1 then
3985 pa_interface_utils_pub.get_messages
3986 (p_encoded => FND_API.G_TRUE,
3987 p_msg_index => 1,
3988 p_msg_count => x_msg_count,
3989 p_msg_data => x_msg_data,
3990 p_data => l_data,
3991 p_msg_index_out => l_msg_index_out);
3992 x_msg_data := l_data;
3993 END IF;
3994 raise FND_API.G_EXC_ERROR;
3995 END IF;
3996 --
3997 --END IF; --financial task flag is Y
3998 ELSE
3999 IF l_dest_wp_str_ver_id IS NOT NULL AND l_src_str_wp_enable_fl = 'Y' THEN
4000 IF (p_debug_mode = 'Y') THEN
4001 pa_debug.debug('Into Else block Where src and dest are WP');
4002 END IF;
4003 --bug 3716615
4004 IF (p_debug_mode = 'Y') THEN
4005 pa_debug.debug('Before call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
4006 END IF;
4007 /* --bug 3983361
4008 IF p_validation_level > 0 THEN
4009 IF PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id) = 'N' THEN --SMukka
4010 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
4011 RAISE FND_API.G_EXC_ERROR;
4012 END IF;
4013 END IF;
4014 */
4015 IF (p_debug_mode = 'Y') THEN
4016 pa_debug.debug('After call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
4017 END IF;
4018 --end bug 3716615
4019
4020 PA_RELATIONSHIP_PVT.Insert_Subproject_Association
4021 ( p_init_msg_list => p_init_msg_list
4022 ,p_commit => p_commit
4023 ,p_validate_only => p_validate_only
4024 ,p_validation_level => p_validation_level
4025 ,p_calling_module => p_calling_module
4026 ,p_debug_mode => p_debug_mode
4027 ,p_max_msg_count => p_max_msg_count
4028 ,p_src_proj_id => p_src_proj_id
4029 ,p_src_struc_wp_or_fin => 'WORKPLAN'
4030 ,p_src_struc_elem_id => l_src_struc_elem_id
4031 ,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
4032 ,p_src_task_elem_id => l_src_task_elem_id
4033 ,p_src_task_elem_ver_id => p_task_ver_id
4034 ,p_dest_proj_id => p_dest_proj_id
4035 ,p_dest_struc_elem_id => l_dest_wp_struct_element_id
4036 ,p_dest_struc_elem_ver_id => l_dest_wp_str_ver_id
4037 ,x_lnk_task_elem_id => l_lnk_task_elem_id
4038 ,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
4039 ,p_lnk_task_name_number => l_task_name_number
4040 ,p_relationship_type => 'LW'
4041 ,p_comment => p_comment --Bug No 3668113
4042 ,x_object_relationship_id => x_object_relationship_id
4043 ,x_pev_schedule_id => l_pev_schedule_id
4044 ,x_return_status => x_return_status
4045 ,x_msg_count => x_msg_count
4046 ,x_msg_data => x_msg_data
4047 );
4048 --
4049 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4050 x_msg_count := FND_MSG_PUB.count_msg;
4051 IF x_msg_count = 1 then
4052 pa_interface_utils_pub.get_messages
4053 (p_encoded => FND_API.G_TRUE,
4054 p_msg_index => 1,
4055 p_msg_count => x_msg_count,
4056 p_msg_data => x_msg_data,
4057 p_data => l_data,
4058 p_msg_index_out => l_msg_index_out);
4059 x_msg_data := l_data;
4060 END IF;
4061 raise FND_API.G_EXC_ERROR;
4062 END IF;
4063 --
4064 END IF; --l_dest_wp_str_ver_id is not null and l_src_str_wp_enable_fl is Y
4065 IF l_dest_fin_str_ver_id IS NOT NULL AND
4066 l_src_task_financial_flag='Y' AND
4067 l_src_str_fin_enable_fl = 'Y' THEN
4068 IF (p_debug_mode = 'Y') THEN
4069 pa_debug.debug('Into Else block Where src and dest are FIN');
4070 END IF;
4071
4072 --bug 3716615
4073 IF (p_debug_mode = 'Y') THEN
4074 pa_debug.debug('Before call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
4075 END IF;
4076 /* --bug 3983361
4077 IF p_validation_level > 0 THEN
4078 IF PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK(p_task_ver_id,p_dest_proj_id, 'FINANCIAL') = 'N' THEN --SMukka
4079 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
4080 RAISE FND_API.G_EXC_ERROR;
4081 END IF;
4082 END IF;
4083 */
4084 IF (p_debug_mode = 'Y') THEN
4085 pa_debug.debug('After call to PA_RELATIONSHIP_UTILS.CREATE_SUB_PROJ_ASSO_OK api');
4086 END IF;
4087 --end bug 3716615
4088
4089 PA_RELATIONSHIP_PVT.Insert_Subproject_Association
4090 ( p_init_msg_list => p_init_msg_list
4091 ,p_commit => p_commit
4092 ,p_validate_only => p_validate_only
4093 ,p_validation_level => p_validation_level
4094 ,p_calling_module => p_calling_module
4095 ,p_debug_mode => p_debug_mode
4096 ,p_max_msg_count => p_max_msg_count
4097 ,p_src_proj_id => p_src_proj_id
4098 ,p_src_struc_wp_or_fin => 'FINANCIAL'
4099 ,p_src_struc_elem_id => l_src_struc_elem_id
4100 ,p_src_struc_elem_ver_id => l_src_struc_elem_ver_id
4101 ,p_src_task_elem_id => l_src_task_elem_id
4102 ,p_src_task_elem_ver_id => p_task_ver_id
4103 ,p_dest_proj_id => p_dest_proj_id
4104 ,p_dest_struc_elem_id => l_dest_fin_struct_element_id
4105 ,p_dest_struc_elem_ver_id => l_dest_fin_str_ver_id
4106 ,x_lnk_task_elem_id => l_lnk_task_elem_id
4107 ,x_lnk_task_elem_ver_id => l_lnk_task_elem_ver_id
4108 ,p_lnk_task_name_number => l_task_name_number
4109 ,p_relationship_type => 'LF'
4110 ,p_comment => p_comment --Bug No 3668113
4111 ,x_object_relationship_id => x_object_relationship_id
4112 ,x_pev_schedule_id => l_pev_schedule_id
4113 ,x_return_status => x_return_status
4114 ,x_msg_count => x_msg_count
4115 ,x_msg_data => x_msg_data
4116 );
4117 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4118 x_msg_count := FND_MSG_PUB.count_msg;
4119 IF x_msg_count = 1 then
4120 pa_interface_utils_pub.get_messages
4121 (p_encoded => FND_API.G_TRUE,
4122 p_msg_index => 1,
4123 p_msg_count => x_msg_count,
4124 p_msg_data => x_msg_data,
4125 p_data => l_data,
4126 p_msg_index_out => l_msg_index_out);
4127 x_msg_data := l_data;
4128 END IF;
4129 raise FND_API.G_EXC_ERROR;
4130 END IF;
4131 END IF; --l_dest_fin_str_ver_id is not null and l_src_task_fin_flag is Y and l_src_str_fin_enable_fl is y
4132 END IF; --src and dest project sharing code are SHARE_FULL
4133 --
4134 IF (p_debug_mode = 'Y') THEN
4135 pa_debug.debug('After call to ISPA Linking Task Elem Id => '||l_lnk_task_elem_id);
4136 pa_debug.debug('After call to ISPA Linking Task Elem Ver Id => '||l_lnk_task_elem_ver_id);
4137 pa_debug.debug('After call to ISPA Object_Relationship_Id => '||x_object_relationship_id);
4138 pa_debug.debug('After call to ISPA WP Attr schedule Id => '||l_pev_schedule_id);
4139 END IF;
4140 --
4141 IF (p_commit = FND_API.G_TRUE) THEN
4142 COMMIT;
4143 END IF;
4144 --
4145
4146 --bug 4370533 --Issue #3
4147 -- set WBS flag dirty for the project
4148 --update only if a workplan is created.
4149
4150 -- Begin fix for Bug # 4409337.
4151
4152 if (
4153 (
4154 (
4155 l_src_proj_sharing_code = 'SHARE_FULL'
4156 or
4157 l_src_proj_sharing_code = 'SHARE_PARTIAL'
4158 )
4159 and
4160 -- Begin Bug # 4573015.
4161 (
4162 l_dest_fin_str_ver_id is not null
4163 or
4164 pa_project_structure_utils.check_struc_ver_published(p_dest_proj_id, l_dest_wp_str_ver_id) = 'Y'
4165 )
4166 -- End Bug # 4573015.
4167 )
4168 or
4169 (
4170 (
4171 l_src_proj_sharing_code = 'SPLIT_MAPPING'
4172 or
4173 l_src_proj_sharing_code = 'SPLIT_NO_MAPPING'
4174 )
4175 and
4176 (
4177 (
4178 l_dest_wp_str_ver_id IS NOT NULL
4179 and
4180 l_src_str_wp_enable_fl = 'Y'
4181 and
4182 pa_project_structure_utils.check_struc_ver_published(p_dest_proj_id, l_dest_wp_str_ver_id) = 'Y'
4183 )
4184 or
4185 (
4186 l_dest_fin_str_ver_id IS NOT NULL
4187 and
4188 l_src_task_financial_flag='Y'
4189 and
4190 l_src_str_fin_enable_fl = 'Y'
4191 and
4192 pa_project_structure_utils.check_struc_ver_published(p_dest_proj_id, l_dest_fin_str_ver_id) = 'Y'
4193 )
4194 )
4195 )
4196 -- Begin Bug # 4573015.
4197 or
4198 (
4199 l_src_str_wp_enable_fl = 'Y'
4200 and
4201 l_src_str_fin_enable_fl = 'N'
4202 and
4203 pa_project_structure_utils.check_struc_ver_published(p_dest_proj_id, l_dest_wp_str_ver_id) = 'Y'
4204 )
4205 or
4206 (
4207 l_src_str_fin_enable_fl = 'Y'
4208 and
4209 l_src_str_wp_enable_fl = 'N'
4210 )
4211 -- End Bug # 4573015.
4212 ) then
4213
4214 /*
4215
4216 IF l_src_str_wp_enable_fl = 'Y' AND l_dest_published_wp_str_id IS NOT NULL
4217 THEN
4218
4219 */
4220
4221 -- End fix for Bug # 4409337.
4222
4223 -- Added If condition for Bug 8889029
4224 -- If user wants to defer the rollup of programs, we need not set the version to dirty as another message will be displayed to run UPPD.
4225
4226 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
4227
4228 PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
4229 (
4230 p_calling_context => 'SELF_SERVICE'
4231 ,p_project_id => p_src_proj_id
4232 ,p_structure_version_id => l_src_struc_elem_ver_id
4233 ,p_update_wbs_flag => 'Y'
4234 ,x_return_status => x_return_status
4235 ,x_msg_count => x_msg_count
4236 ,x_msg_data => x_msg_data);
4237
4238 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4239 x_msg_count := FND_MSG_PUB.count_msg;
4240 IF x_msg_count = 1 then
4241 pa_interface_utils_pub.get_messages
4242 (p_encoded => FND_API.G_TRUE,
4243 p_msg_index => 1,
4244 p_msg_count => x_msg_count,
4245 p_msg_data => x_msg_data,
4246 p_data => l_data,
4247 p_msg_index_out => l_msg_index_out);
4248 x_msg_data := l_data;
4249 END IF;
4250 raise FND_API.G_EXC_ERROR;
4251 end if;
4252 END IF; --Bug#8889029
4253 END IF;
4254 --bug 4370533 --Issue #3
4255
4256 x_return_status := FND_API.G_RET_STS_SUCCESS;
4257 --
4258 IF (p_debug_mode = 'Y') THEN
4259 pa_debug.debug('PA_RELATIONSHIP_PVT.Create_Subproject_Association end');
4260 END IF;
4261 --
4262 EXCEPTION
4263 WHEN FND_API.G_EXC_ERROR THEN
4264 IF (p_commit = FND_API.G_TRUE) THEN
4265 ROLLBACK to Create_Subproject_Ass_pvt;
4266 END IF;
4267 x_msg_count := FND_MSG_PUB.count_msg;
4268 x_return_status := FND_API.G_RET_STS_ERROR;
4269 WHEN OTHERS THEN
4270 IF (p_commit = FND_API.G_TRUE) THEN
4271 ROLLBACK to Create_Subproject_Ass_pvt;
4272 END IF;
4273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4274 x_msg_count := FND_MSG_PUB.count_msg;
4275 --put message
4276 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
4277 p_procedure_name => 'Create_Subproject_Association',
4278 p_error_text => SUBSTRB(SQLERRM,1,240));
4279 RAISE;
4280 END Create_Subproject_Association;
4281 --
4282 --
4283 --
4284 -- API name : Update_Subproject_Association
4285 -- Type : Private Procedure
4286 -- Pre-reqs : None
4287 -- Return Value : N/A
4288 -- Parameters
4289 -- p_api_version IN NUMBER := 1.0
4290 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
4291 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
4292 -- p_validation_level IN VARCHAR2 := 100
4293 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4294 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4295 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
4296 -- p_debug_mode IN VARCHAR2 := 'N'
4297 -- p_object_relationship_id IN NUMBER
4298 -- p_record_version_number IN NUMBER
4299 -- p_comment IN VARCHAR2
4300 -- x_return_status OUT VARCHAR2
4301 -- x_msg_count OUT NUMBER
4302 -- x_msg_data OUT VARCHAR2
4303 --
4304 -- History
4305 --
4306 -- 20-Feb-04 Smukka -Created
4307 -- -Created this procedure for subproject association
4308 --
4309 -- FPM bug 3450684
4310 --
4311 --
4312 Procedure Update_Subproject_Association(p_api_version IN NUMBER := 1.0,
4313 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
4314 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
4315 p_validation_level IN VARCHAR2 := 100,
4316 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
4317 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4318 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4319 p_debug_mode IN VARCHAR2 := 'N',
4320 p_object_relationship_id IN NUMBER,
4321 p_record_version_number IN NUMBER,
4322 p_comment IN VARCHAR2,
4323 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4324 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4325 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4326 IS
4327
4328 -- Bug # 5072032.
4329
4330 cursor l_cur_obj_rel_id(c_object_relationship_id NUMBER) is
4331 select por2.object_relationship_id, por2.record_version_number
4332 from pa_object_relationships por1, pa_object_relationships por2
4333 , pa_object_relationships por3, pa_object_relationships por4
4334 where por1.object_id_to1 = por2.object_id_from1
4335 and por1.relationship_type = 'S'
4336 and por3.object_id_to1 = por4.object_id_from1
4337 and por3.relationship_type = 'S'
4338 and por1.object_id_from1 = por3.object_id_from1
4339 and por2.object_id_from2 = por4.object_id_from2
4340 and por2.object_id_to1 = por4.object_id_to1
4341 and por2.object_id_to2 = por4.object_id_to2
4342 and por2.relationship_type IN ('LW','LF')
4343 and por4.object_relationship_id = c_object_relationship_id;
4344
4345 l_cur_obj_rel_rec l_cur_obj_rel_id%ROWTYPE;
4346
4347 -- Bug # 5072032.
4348
4349
4350 BEGIN
4351 --
4352 IF (p_debug_mode = 'Y') THEN
4353 pa_debug.debug('PA_RELATIONSHIPS_PVT1.UPDATE_SUBPROJECT_ASSOCIATION Begin');
4354 END IF;
4355 --
4356 IF (p_commit = FND_API.G_TRUE) THEN
4357 savepoint update_subproject_ass_pvt;
4358 END IF;
4359 --
4360 IF (p_debug_mode = 'Y') THEN
4361 pa_debug.debug('The value of the passed object_relationship_id=> '||p_object_relationship_id);
4362 pa_debug.debug('The value of the passed comments=> '||p_comment);
4363 END IF;
4364 --
4365
4366 -- Bug # 5072032.
4367
4368 for l_cur_obj_rel_rec in l_cur_obj_rel_id(p_object_relationship_id)
4369 loop
4370 UPDATE pa_object_relationships
4371 SET comments = p_comment
4372 ,record_version_number = (l_cur_obj_rel_rec.record_version_number+1) -- p_record_version_number + 1
4373 WHERE object_relationship_id = l_cur_obj_rel_rec.object_relationship_id -- p_object_relationship_id
4374 and record_version_number = l_cur_obj_rel_rec.record_version_number;
4375 IF SQL%NOTFOUND THEN
4376 fnd_message.set_name('PA','PA_RECORD_CHANGED');
4377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4378 RAISE FND_API.G_EXC_ERROR;
4379 END IF;
4380
4381 end loop;
4382
4383 -- Bug # 5072032.
4384 --
4385 x_return_status := FND_API.G_RET_STS_SUCCESS;
4386 --
4387 IF (p_commit = FND_API.G_TRUE) THEN
4388 COMMIT;
4389 END IF;
4390 --
4391 IF (p_debug_mode = 'Y') THEN
4392 pa_debug.debug('Return status before the end of Update_Subproject_Association=> '||x_return_status);
4393 END IF;
4394 --
4395 IF (p_debug_mode = 'Y') THEN
4396 pa_debug.debug('PA_RELATIONSHIPS_PVT1.UPDATE_SUBPROJECT_ASSOCIATION END');
4397 END IF;
4398 --
4399 EXCEPTION
4400 WHEN FND_API.G_EXC_ERROR THEN
4401 IF (p_commit = FND_API.G_TRUE) THEN
4402 ROLLBACK to update_subproject_ass_pvt;
4403 END IF;
4404 x_msg_count := FND_MSG_PUB.count_msg;
4405 x_return_status := FND_API.G_RET_STS_ERROR;
4406 WHEN OTHERS THEN
4407 IF (p_commit = FND_API.G_TRUE) THEN
4408 ROLLBACK TO update_subproject_ass_pvt;
4409 END IF;
4410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4411 FND_MSG_PUB.add_exc_msg(
4412 p_pkg_name => 'PA_RELATIONSHIPS_PVT1',
4413 p_procedure_name => 'update_subproject_association',
4414 p_error_text => SUBSTRB(SQLERRM,1,240));
4415 ROLLBACK TO update_subproject_association;
4416 RAISE;
4417 END Update_Subproject_Association;
4418 --
4419 --
4420 --
4421 -- API name : Delete_SubProject_Association
4422 -- Type : Private Procedure
4423 -- Pre-reqs : None
4424 -- Return Value : N/A
4425 -- Parameters
4426 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
4427 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
4428 -- p_validation_level IN VARCHAR2 := 100
4429 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4430 -- p_debug_mode IN VARCHAR2 := 'N'
4431 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
4432 -- p_object_relationships_id IN NUMBER
4433 -- p_record_version_number IN NUMBER
4434 -- x_return_status OUT VARCHAR2
4435 -- x_msg_count OUT NUMBER
4436 -- x_msg_data OUT VARCHAR2
4437 --
4438 -- History
4439 --
4440 -- 20-Feb-04 Smukka -Created
4441 -- -Created this procedure for subproject association
4442 --
4443 -- FPM bug 3450684
4444 --
4445 --
4446 PROCEDURE Delete_SubProject_Association(p_commit IN VARCHAR2 := FND_API.G_FALSE,
4447 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
4448 p_validation_level IN VARCHAR2 := 100,
4449 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
4450 p_debug_mode IN VARCHAR2 := 'N',
4451 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4452 p_object_relationships_id IN NUMBER,
4453 p_record_version_number IN NUMBER,
4454 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4455 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4456 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4457 IS
4458 -- Deleting sub-project association
4459 -- Input parameters for this API are
4460 -- object_relationship_id= p_object_relationships_id
4461 l_src_lnk_task_ver_id pa_proj_element_versions.element_version_id%type;
4462 l_dest_str_ver_id pa_proj_element_versions.element_version_id%type;
4463 l_src_proj_id pa_projects_all.project_id%type;
4464 l_dest_proj_id pa_projects_all.project_id%type;
4465 l_src_task_ver_id pa_proj_element_versions.element_version_id%type;
4466 l_task_version_rvn NUMBER;
4467 l_upd_prog_grp_status NUMBER:=0;
4468 --
4469 l_data VARCHAR2(250);
4470 l_msg_index_out NUMBER;
4471 --
4472 CURSOR get_lnk_obj_rel_attr(cp_object_relationships_id NUMBER) IS
4473 SELECT object_id_from1, --src_lnk_task_ver_id
4474 object_id_to1, --dest_str_ver_id
4475 object_id_from2, --src proj_id
4476 object_id_to2 --dest_proj_id
4477 FROM pa_object_relationships
4478 WHERE object_relationship_id = cp_object_relationships_id
4479 AND relationship_type IN ('LW','LF');
4480 get_lnk_obj_rel_attr_rec get_lnk_obj_rel_attr%ROWTYPE;
4481 --
4482 CURSOR get_rec_ver_num(cp_lnk_task_ver_id NUMBER) IS
4483 SELECT record_version_number --task_Version_rvn
4484 FROM pa_proj_element_versions
4485 WHERE element_version_id = cp_lnk_task_ver_id;
4486 get_rec_ver_num_rec get_rec_ver_num%ROWTYPE;
4487
4488 CURSOR get_src_task_ver_id(cp_src_lnk_task_ver_id NUMBER) IS
4489 SELECT object_id_from1 --src_task_ver_id
4490 FROM pa_object_relationships
4491 WHERE object_id_to1 = cp_src_lnk_task_ver_id
4492 AND relationship_type = 'S';
4493 get_src_task_ver_id_rec get_src_task_ver_id%ROWTYPE;
4494 --
4495 CURSOR get_lnk_info(cp_src_project_id NUMBER,
4496 cp_src_Task_ver_id NUMBER,
4497 cp_dest_proj_id NUMBER) IS
4498 SELECT pora.object_relationship_id obj_rel_id,
4499 pora.object_id_to1 lnk_task_ver_id,
4500 porb.object_relationship_id lnk_obj_rel_id,
4501 porb.object_id_to1 lnk_dest_str_ver_id
4502 , porb.record_version_number lnk_record_ver_number -- Bug # 5072032.
4503 FROM pa_proj_element_versions ppev,
4504 pa_object_relationships pora,
4505 pa_object_relationships porb,
4506 pa_proj_elements ppe
4507 WHERE pora.relationship_type = 'S'
4508 AND ppev.project_id = cp_src_project_id
4509 AND pora.OBJECT_ID_FROM1 = cp_src_Task_ver_id
4510 AND pora.object_type_from = 'PA_TASKS'
4511 AND pora.OBJECT_ID_to1 = ppev.ELEMENT_VERSION_ID
4512 AND ppe.proj_element_id = ppev.proj_element_id
4513 AND pora.object_id_to1=porb.object_id_from1
4514 AND porb.object_id_to2 = cp_dest_proj_id
4515 AND porb.object_id_from2 = cp_src_project_id
4516 AND porb.object_type_to = 'PA_STRUCTURES'
4517 AND porb.relationship_type IN ('LW','LF')
4518 AND ppe.link_task_flag = 'Y';
4519 get_lnk_info_rec get_lnk_info%ROWTYPE;
4520 --
4521
4522 --bug 4370533 --Issue #3 delete link
4523 CURSOR cur_src_structure_ver_id(c_src_task_ver_id NUMBER)
4524 IS
4525 SELECT project_id, parent_structure_version_id
4526 FROM pa_proj_element_versions
4527 WHERE element_version_id = c_src_task_ver_id
4528 ;
4529 l_src_structure_ver_id NUMBER;
4530 l_src_project_id NUMBER;
4531 --bug 4370533 --Issue #3
4532
4533 -- Begin fix for Bug # 4385027.
4534
4535 l_tasks_ver_ids PA_NUM_1000_NUM := PA_NUM_1000_NUM();
4536
4537 cursor check_child_pub (c_dest_proj_id NUMBER, c_dest_struc_elem_ver_id NUMBER) is
4538 select 'x'
4539 from pa_proj_elem_ver_structure
4540 where project_id = c_dest_proj_id
4541 and element_version_id = c_dest_struc_elem_ver_id
4542 and status_code = 'STRUCTURE_PUBLISHED';
4543
4544 l_dummy VARCHAR2(1);
4545
4546 -- End fix for Bug # 4385027.
4547
4548 BEGIN
4549 --
4550 IF (p_debug_mode = 'Y') THEN
4551 pa_debug.debug('PA_RELATIONSHIP_PVT.Delete_SubProject_Association begin');
4552 END IF;
4553 --
4554 IF (p_commit = FND_API.G_TRUE) THEN
4555 savepoint delete_subproject_ass_pvt;
4556 END IF;
4557 --
4558 IF (p_debug_mode = 'Y') THEN
4559 pa_debug.debug('Deleting the object_relationships_id => '||p_object_relationships_id);
4560 END IF;
4561 --
4562 -- Get the details for passed object relationship id from pa_object_relationships
4563 OPEN get_lnk_obj_rel_attr(p_object_relationships_id);
4564 FETCH get_lnk_obj_rel_attr INTO get_lnk_obj_rel_attr_rec;
4565 IF get_lnk_obj_rel_attr%NOTFOUND THEN
4566 CLOSE get_lnk_obj_rel_attr;
4567 PA_UTILS.ADD_MESSAGE('PA','PA_NO_RECORD_VERSION_NUMBER');
4568 x_msg_data := 'PA_NO_RECORD_VERSION_NUMBER';
4569 RAISE FND_API.G_EXC_ERROR;
4570 END IF;
4571 --
4572 IF (p_debug_mode = 'Y') THEN
4573 pa_debug.debug('src lnk task ver id value for pass obj rel id=> '||get_lnk_obj_rel_attr_rec.object_id_from1);
4574 pa_debug.debug('dest str ver id value for pass obj rel id=> '||get_lnk_obj_rel_attr_rec.object_id_to1);
4575 pa_debug.debug('src proj id value for pass obj rel id => '||get_lnk_obj_rel_attr_rec.object_id_from2);
4576 pa_debug.debug('dest proj id value for pass obj rel id => '||get_lnk_obj_rel_attr_rec.object_id_to2);
4577 END IF;
4578 --
4579 CLOSE get_lnk_obj_rel_attr;
4580 --
4581 --Getting the src task version details
4582 --
4583 IF (p_debug_mode = 'Y') THEN
4584 pa_debug.debug('Values pass to get_lnk_obj_rel_attr cursor => '||get_lnk_obj_rel_attr_rec.object_id_from1);
4585 END IF;
4586 --
4587 OPEN get_src_task_ver_id(get_lnk_obj_rel_attr_rec.object_id_from1);
4588 FETCH get_src_task_ver_id INTO get_src_task_ver_id_rec;
4589 IF get_src_task_ver_id%NOTFOUND THEN
4590 CLOSE get_src_task_ver_id;
4591 PA_UTILS.ADD_MESSAGE('PA','PA_NO_RECORD_VERSION_NUMBER');
4592 x_msg_data := 'PA_NO_RECORD_VERSION_NUMBER';
4593 RAISE FND_API.G_EXC_ERROR;
4594 END IF;
4595 --
4596 IF (p_debug_mode = 'Y') THEN
4597 pa_debug.debug('the value of src task ver id for passed lnk task ver id=> '||get_src_task_ver_id_rec.object_id_from1);
4598 END IF;
4599 --
4600 CLOSE get_src_task_ver_id;
4601
4602 --bug 4370533 --Issue #3 delete link
4603
4604 OPEN cur_src_structure_ver_id(get_src_task_ver_id_rec.object_id_from1);
4605 FETCH cur_src_structure_ver_id INTO l_src_project_id, l_src_structure_ver_id;
4606 CLOSE cur_src_structure_ver_id;
4607
4608 IF (p_debug_mode = 'Y') THEN
4609 pa_debug.debug('l_src_structure_ver_id='||l_src_structure_ver_id);
4610 END IF;
4611 --bug 4370533 --Issue #3
4612
4613 --
4614 IF (p_debug_mode = 'Y') THEN
4615 pa_debug.debug('Values pass to get_lnk_info cursor => '||get_lnk_obj_rel_attr_rec.object_id_from2);
4616 pa_debug.debug('Values pass to get_lnk_info cursor => '||get_src_task_ver_id_rec.object_id_from1);
4617 pa_debug.debug('Values pass to get_lnk_info cursor => '||get_lnk_obj_rel_attr_rec.object_id_to2);
4618 END IF;
4619 --
4620 OPEN get_lnk_info(get_lnk_obj_rel_attr_rec.object_id_from2,
4621 get_src_task_ver_id_rec.object_id_from1,
4622 get_lnk_obj_rel_attr_rec.object_id_to2);
4623 LOOP
4624 FETCH get_lnk_info INTO get_lnk_info_rec;
4625 IF get_lnk_info%NOTFOUND THEN
4626 CLOSE get_lnk_info;
4627 exit;
4628 END IF;
4629 --Loop thru the above cursor to get the second part of the link
4630 --Bug No 3450684
4631 BEGIN
4632 l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(get_lnk_info_rec.lnk_obj_rel_id,
4633 'DROP');
4634 IF l_upd_prog_grp_status < 0 THEN
4635 PA_UTILS.ADD_MESSAGE('PA','PA_DEL_SUBPROJ_VAL_FAIL');
4636 RAISE FND_API.G_EXC_ERROR;
4637 END IF;
4638 IF (p_debug_mode = 'Y') THEN
4639 pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
4640 END IF;
4641 EXCEPTION
4642
4643 -- Begin fix for Bug # 4485908.
4644
4645 WHEN FND_API.G_EXC_ERROR THEN
4646
4647 RAISE FND_API.G_EXC_ERROR;
4648
4649 -- End fix for Bug # 4485908.
4650
4651 WHEN OTHERS THEN
4652 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
4653 p_procedure_name => 'Delete_SubProject_Association',
4654 p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
4655 RAISE FND_API.G_EXC_ERROR;
4656 END;
4657 --
4658
4659 --PA_RELATIONSHIP_PUB.Delete_Relationship(porb.object_relationship_id);--table handler
4660 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
4661 p_object_relationship_id => get_lnk_info_rec.lnk_obj_rel_id
4662 ,p_object_type_from => NULL
4663 ,p_object_id_from1 => NULL
4664 ,p_object_id_from2 => NULL
4665 ,p_object_id_from3 => NULL
4666 ,p_object_id_from4 => NULL
4667 ,p_object_id_from5 => NULL
4668 ,p_object_type_to => NULL
4669 ,p_object_id_to1 => NULL
4670 ,p_object_id_to2 => NULL
4671 ,p_object_id_to3 => NULL
4672 ,p_object_id_to4 => NULL
4673 ,p_object_id_to5 => NULL
4674 ,p_record_version_number => get_lnk_info_rec.lnk_record_ver_number -- p_record_version_number -- Bug # 5072032.
4675 ,p_pm_product_code => NULL
4676 ,x_return_status => x_return_status
4677 );
4678 --
4679 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4680 x_msg_count := FND_MSG_PUB.count_msg;
4681 IF x_msg_count = 1 then
4682 pa_interface_utils_pub.get_messages
4683 (p_encoded => FND_API.G_TRUE,
4684 p_msg_index => 1,
4685 p_msg_count => x_msg_count,
4686 p_msg_data => x_msg_data,
4687 p_data => l_data,
4688 p_msg_index_out => l_msg_index_out);
4689 x_msg_data := l_data;
4690 END IF;
4691 raise FND_API.G_EXC_ERROR;
4692 END IF;
4693 --
4694 IF (p_debug_mode = 'Y') THEN
4695 pa_debug.debug('Return status after call to PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW=> '||x_return_status);
4696 END IF;
4697 --
4698 -- PA_TASK_PVT1.Delete_Task_Version(pora.object_id_to1);
4699 OPEN get_rec_ver_num(get_lnk_info_rec.lnk_task_ver_id);
4700 FETCH get_rec_ver_num INTO get_rec_ver_num_rec;
4701 IF get_rec_ver_num%NOTFOUND THEN
4702 CLOSE get_rec_ver_num;
4703 PA_UTILS.ADD_MESSAGE('PA','PA_NO_RECORD_VERSION_NUMBER');
4704 x_msg_data := 'PA_NO_RECORD_VERSION_NUMBER';
4705 RAISE FND_API.G_EXC_ERROR;
4706 END IF;
4707 --
4708 PA_TASK_PUB1.DELETE_TASK_VERSION(p_commit => 'N',
4709 p_debug_mode => p_debug_mode,
4710 p_task_version_id => get_lnk_info_rec.lnk_task_ver_id,
4711 p_record_version_number => get_rec_ver_num_rec.record_version_number,
4712 x_return_status => x_return_status,
4713 x_msg_count => x_msg_count,
4714 x_msg_data => x_msg_data);
4715 --
4716 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4717 x_msg_count := FND_MSG_PUB.count_msg;
4718 IF x_msg_count = 1 then
4719 pa_interface_utils_pub.get_messages
4720 (p_encoded => FND_API.G_TRUE,
4721 p_msg_index => 1,
4722 p_msg_count => x_msg_count,
4723 p_msg_data => x_msg_data,
4724 p_data => l_data,
4725 p_msg_index_out => l_msg_index_out);
4726 x_msg_data := l_data;
4727 END IF;
4728 raise FND_API.G_EXC_ERROR;
4729 END IF;
4730 --
4731 IF (p_debug_mode = 'Y') THEN
4732 pa_debug.debug('Return status after call to PA_TASK_PUB1.DELETE_TASK_VERSION=> '||x_return_status);
4733 END IF;
4734 --
4735 CLOSE get_rec_ver_num;
4736 END LOOP;
4737 --
4738
4739 /* bug 4541039
4740 -- Begin fix for Bug # 4385027.
4741
4742 if pa_project_structure_utils.get_struc_type_for_version(l_src_structure_ver_id, 'WORKPLAN') = 'Y' then
4743
4744 l_tasks_ver_ids.extend(1);
4745 l_tasks_ver_ids(1) := get_src_task_ver_id_rec.object_id_from1;
4746
4747 -- do not rollup from working to working structure version.
4748
4749 if get_lnk_obj_rel_attr_rec.object_id_to1 IS NOT NULL then
4750
4751 open check_child_pub(get_lnk_obj_rel_attr_rec.object_id_to2
4752 , get_lnk_obj_rel_attr_rec.object_id_to1);
4753 fetch check_child_pub INTO l_dummy;
4754
4755 if check_child_pub%FOUND then
4756
4757 PA_STRUCT_TASK_ROLLUP_PUB.Rollup_From_Subproject
4758 (p_debug_mode => p_debug_mode
4759 , p_element_versions => l_tasks_ver_ids
4760 , x_return_status => x_return_status
4761 , x_msg_count => x_msg_count
4762 , x_msg_data => x_msg_data);
4763
4764 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4765
4766 x_msg_count := FND_MSG_PUB.count_msg;
4767
4768 if x_msg_count = 1 then
4769
4770 pa_interface_utils_pub.get_messages
4771 (p_encoded => FND_API.G_TRUE
4772 , p_msg_index => 1
4773 , p_msg_count => x_msg_count
4774 , p_msg_data => x_msg_data
4775 , p_data => l_data
4776 , p_msg_index_out => l_msg_index_out);
4777
4778 x_msg_data := l_data;
4779
4780 end if;
4781
4782 raise FND_API.G_EXC_ERROR;
4783
4784 end if;
4785
4786 end if;
4787
4788 close check_child_pub;
4789
4790 end if;
4791
4792 end if;
4793
4794 -- End fix for Bug # 4385027.
4795
4796 --bug 4370533 --Issue #3 delete link
4797 end bug 4541039 */
4798
4799 -- set WBS flag dirty for the project
4800
4801 --Update dirty only if workplan gets deleted.
4802 -- Added If condition for Bug 8889029
4803 -- If user wants to defer the rollup of programs, we need not set the version to dirty as another message will be displayed to run UPPD.
4804 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
4805 IF PA_PROJECT_STRUCTURE_UTILS.GET_STRUC_TYPE_FOR_VERSION(l_src_structure_ver_id, 'WORKPLAN') = 'Y'
4806 THEN
4807 PA_PROJ_TASK_STRUC_PUB.SET_UPDATE_WBS_FLAG
4808 (
4809 p_calling_context => 'SELF_SERVICE'
4810 ,p_project_id => l_src_project_id
4811 ,p_structure_version_id => l_src_structure_ver_id
4812 ,p_update_wbs_flag => 'Y'
4813 ,x_return_status => x_return_status
4814 ,x_msg_count => x_msg_count
4815 ,x_msg_data => x_msg_data);
4816
4817 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
4818 x_msg_count := FND_MSG_PUB.count_msg;
4819 IF x_msg_count = 1 then
4820 pa_interface_utils_pub.get_messages
4821 (p_encoded => FND_API.G_TRUE,
4822 p_msg_index => 1,
4823 p_msg_count => x_msg_count,
4824 p_msg_data => x_msg_data,
4825 p_data => l_data,
4826 p_msg_index_out => l_msg_index_out);
4827 x_msg_data := l_data;
4828 END IF;
4829 raise FND_API.G_EXC_ERROR;
4830 end if;
4831 END IF;
4832 --bug 4370533 --Issue #3
4833 END IF; -- Bug 8889029
4834
4835
4836 x_return_status := FND_API.G_RET_STS_SUCCESS;
4837 --
4838 IF (p_commit = FND_API.G_TRUE) THEN
4839 COMMIT;
4840 END IF;
4841 --
4842 IF (p_debug_mode = 'Y') THEN
4843 pa_debug.debug('Return status before the end of Delete_SubProject_Association=> '||x_return_status);
4844 END IF;
4845 --
4846 IF (p_debug_mode = 'Y') THEN
4847 pa_debug.debug('PA_RELATIONSHIP_PVT.Delete_SubProject_Association end');
4848 END IF;
4849 --
4850 EXCEPTION
4851 WHEN FND_API.G_EXC_ERROR THEN
4852 IF (p_commit = FND_API.G_TRUE) THEN
4853 ROLLBACK to delete_subproject_ass_pvt;
4854 END IF;
4855 x_msg_count := FND_MSG_PUB.count_msg;
4856 x_return_status := FND_API.G_RET_STS_ERROR;
4857 WHEN OTHERS THEN
4858 IF (p_commit = FND_API.G_TRUE) THEN
4859 ROLLBACK to delete_subproject_ass_pvt;
4860 END IF;
4861 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4862 x_msg_count := FND_MSG_PUB.count_msg;
4863 --put message
4864 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
4865 p_procedure_name => 'Delete_SubProject_Association',
4866 p_error_text => SUBSTRB(SQLERRM,1,240));
4867 RAISE;
4868 END Delete_SubProject_Association;
4869 --
4870 --
4871 --
4872 -- History
4873 --
4874 -- 20-Feb-04 Smukka -Created
4875 -- -Created this procedure for subproject association
4876 --
4877 -- FPM bug 3450684
4878 --
4879 --
4880 Procedure Copy_OG_Lnk_For_Subproj_Ass(p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
4881 p_validation_level IN VARCHAR2 := 100,
4882 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
4883 p_debug_mode IN VARCHAR2 := 'N',
4884 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
4885 p_commit IN VARCHAR2 := FND_API.G_FALSE,
4886 p_src_str_version_id IN NUMBER,
4887 p_dest_str_version_id IN NUMBER,
4888 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
4889 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
4890 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
4891 /*PROCEDURE Copy_OG_Lnk_For_Subproj_Ass(p_src_str_version_id IN NUMBER,
4892 p_dest_str_version_id IN NUMBER,
4893 x_return_status OUT VARCHAR2,
4894 x_msg_count OUT NUMBER,
4895 x_msg_data OUT VARCHAR2)*/
4896 IS
4897 l_object_relationship_id NUMBER;
4898 l_return_status VARCHAR2(1);
4899 l_msg_count NUMBER;
4900 l_msg_data VARCHAR2(2000);
4901 API_ERROR EXCEPTION;
4902 --
4903 --
4904 l_pub_str_ver_enable CHAR(1):=NULL;
4905 /* Cursor to get the linking task information present on that src structure version id*/
4906 CURSOR get_linking_task_info(cp_src_str_ver_id NUMBER) IS
4907 SELECT ppev.element_version_id lnk_task_ver_id,
4908 ppe.proj_element_id lnk_task_id
4909 FROM pa_proj_elements ppe,
4910 pa_proj_element_versions ppev
4911 WHERE ppe.proj_element_id = ppev.proj_element_id
4912 AND ppe.link_task_flag = 'Y'
4913 AND ppev.parent_structure_version_id = cp_src_str_ver_id
4914 AND ppe.project_id = ppev.project_id;
4915 get_linking_task_info_rec get_linking_task_info%ROWTYPE;
4916 --
4917 /*This cursor is used to get the relationships that are going out of task version*/
4918 CURSOR get_going_out_lnk_info(cp_src_Task_ver_id NUMBER) IS
4919 SELECT por.object_relationship_id,
4920 por.object_id_to1,
4921 por.object_id_from1,
4922 por.object_id_to2,
4923 por.object_id_from2,
4924 por.relationship_type,
4925 por.record_version_number,
4926 por.object_type_to,
4927 por.object_type_from
4928 FROM pa_object_relationships por
4929 WHERE por.relationship_type in ('LW','LF')
4930 AND por.OBJECT_ID_FROM1 = cp_src_Task_ver_id
4931 AND por.object_type_from = 'PA_TASKS'
4932 AND por.object_type_to = 'PA_STRUCTURES'
4933 AND por.object_id_to2 <> por.object_id_from2;
4934 get_going_out_lnk_info_rec get_going_out_lnk_info%ROWTYPE;
4935 p_src_Task_ver_id NUMBER;
4936 l_new_pub_lnk_task_ver_id NUMBER;
4937 --
4938 l_upd_prog_grp_status NUMBER:=0;
4939 --
4940
4941 CURSOR get_new_pub_lnk_task_ver_id(c_dest_str_version_id NUMBER, c_link_task_id NUMBER) IS
4942 SELECT element_version_id
4943 FROM pa_proj_element_versions
4944 WHERE parent_structure_Version_id = c_dest_str_version_id
4945 AND proj_element_id = c_link_task_id;
4946 BEGIN
4947 --
4948 IF (p_debug_mode = 'Y') THEN
4949 pa_debug.debug('PA_RELATIONSHIP_PVT.Copy_OG_Lnk_For_Subproj_Ass begin');
4950 END IF;
4951 --
4952 IF (p_commit = FND_API.G_TRUE) THEN
4953 savepoint Copy_OG_Lnk_For_Subproj_Ass;
4954 END IF;
4955 --
4956 IF (p_debug_mode = 'Y') THEN
4957 pa_debug.debug('Value of p_src_str_version_id => '||p_src_str_version_id);
4958 pa_debug.debug('Value of p_dest_str_version_id => '||p_dest_str_version_id);
4959 END IF;
4960 --
4961 IF (p_debug_mode = 'Y') THEN
4962 pa_debug.debug('Value of p_src_str_version_id before get_linking_task_info => '||p_src_str_version_id);
4963 END IF;
4964 OPEN get_linking_task_info(p_src_str_version_id);
4965 LOOP
4966 FETCH get_linking_task_info INTO get_linking_task_info_rec;
4967 IF get_linking_task_info%NOTFOUND THEN
4968 EXIT;
4969 END IF;
4970 IF (p_debug_mode = 'Y') THEN
4971 pa_debug.debug('Value of get_linking_task_info_rec lnk_task_ver_id before get_going_out_lnk_info cur => '||get_linking_task_info_rec.lnk_task_ver_id);
4972 END IF;
4973 OPEN get_going_out_lnk_info(get_linking_task_info_rec.lnk_task_ver_id);
4974 LOOP
4975 FETCH get_going_out_lnk_info into get_going_out_lnk_info_rec;
4976 IF get_going_out_lnk_info%NOTFOUND THEN
4977 EXIT;
4978 END IF;
4979 --For Task
4980 /*
4981 BEGIN
4982 SELECT element_version_id
4983 INTO l_new_pub_lnk_task_ver_id
4984 FROM pa_proj_element_versions
4985 WHERE parent_structure_Version_id = p_dest_str_version_id
4986 AND proj_element_id = get_linking_task_info_rec.lnk_task_id;
4987 IF (p_debug_mode = 'Y') THEN
4988 pa_debug.debug('Value of l_new_pub_lnk_task_ver_id after select=> '||l_new_pub_lnk_task_ver_id);
4989 pa_debug.debug('Value of p_dest_str_version_id after select => '||p_dest_str_version_id);
4990 END IF;
4991 EXCEPTION
4992 WHEN OTHERS THEN
4993 RAISE;
4994 END;
4995 */
4996 l_new_pub_lnk_task_ver_id := NULL;
4997 OPEN get_new_pub_lnk_task_ver_id(p_dest_str_version_id, get_linking_task_info_rec.lnk_task_id);
4998 FETCH get_new_pub_lnk_task_ver_id INTO l_new_pub_lnk_task_ver_id;
4999 CLOSE get_new_pub_lnk_task_ver_id;
5000
5001 IF (l_new_pub_lnk_task_ver_id IS NOT NULL) THEN
5002 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
5003 p_user_id => FND_GLOBAL.USER_ID
5004 ,p_object_type_from => 'PA_TASKS'
5005 ,p_object_id_from1 => l_new_pub_lnk_task_ver_id
5006 ,p_object_id_from2 => get_going_out_lnk_info_rec.object_id_from2
5007 ,p_object_id_from3 => NULL
5008 ,p_object_id_from4 => NULL
5009 ,p_object_id_from5 => NULL
5010 ,p_object_type_to => get_going_out_lnk_info_rec.object_type_to
5011 ,p_object_id_to1 => get_going_out_lnk_info_rec.object_id_to1
5012 ,p_object_id_to2 => get_going_out_lnk_info_rec.object_id_to2
5013 ,p_object_id_to3 => NULL
5014 ,p_object_id_to4 => NULL
5015 ,p_object_id_to5 => NULL
5016 ,p_relationship_type => get_going_out_lnk_info_rec.relationship_type
5017 ,p_relationship_subtype => NULL
5018 ,p_lag_day => NULL
5019 ,p_imported_lag => NULL
5020 ,p_priority => NULL
5021 ,p_pm_product_code => NULL
5022 ,x_object_relationship_id => l_object_relationship_id
5023 ,x_return_status => x_return_status
5024 ,p_comments => null
5025 ,p_status_code => null
5026 );
5027
5028 -- 4537865
5029 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
5030 RAISE FND_API.G_EXC_ERROR;
5031 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
5032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
5033 END IF;
5034 -- End 4537865
5035
5036 IF (p_debug_mode = 'Y') THEN
5037 pa_debug.debug('Value of x_object_relationship_id=> '||l_object_relationship_id);
5038 pa_debug.debug('Value of l_new_pub_lnk_task_ver_id=> '||l_new_pub_lnk_task_ver_id);
5039 pa_debug.debug('Value of x_return_status after call to PA_OBJECT_RELATIONSHIPS_PKG INSERT_ROW=> '||x_return_status);
5040 END IF;
5041 --
5042 --Bug No 3450684
5043 BEGIN
5044 l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(l_object_relationship_id,
5045 'ADD');
5046 IF l_upd_prog_grp_status < 0 THEN
5047 PA_UTILS.ADD_MESSAGE('PA','PA_CP_SUBPROJ_VAL_FAIL');
5048 RAISE FND_API.G_EXC_ERROR;
5049 END IF;
5050 IF (p_debug_mode = 'Y') THEN
5051 pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
5052 END IF;
5053 EXCEPTION
5054
5055 -- Begin fix for Bug # 4485908.
5056
5057 WHEN FND_API.G_EXC_ERROR THEN
5058
5059 RAISE FND_API.G_EXC_ERROR;
5060
5061 -- End fix for Bug # 4485908.
5062
5063 WHEN OTHERS THEN
5064 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
5065 p_procedure_name => 'Copy_OG_Lnk_For_Subproj_Ass',
5066 p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
5067 RAISE FND_API.G_EXC_ERROR;
5068 END;
5069 END IF;
5070 --
5071 END LOOP; --end loop for get_going_out_lnk_info cursor
5072 CLOSE get_going_out_lnk_info;
5073 END LOOP; --end loop for get_linking_task_info cursor
5074 CLOSE get_linking_task_info;
5075 --
5076 IF (p_commit = FND_API.G_TRUE) THEN
5077 COMMIT;
5078 END IF;
5079 --
5080 IF (p_debug_mode = 'Y') THEN
5081 pa_debug.debug('Return status before the end of Copy_OG_Lnk_For_Subproj_Ass=> '||x_return_status);
5082 END IF;
5083 --
5084 IF (p_debug_mode = 'Y') THEN
5085 pa_debug.debug('PA_RELATIONSHIP_PVT.Copy_OG_Lnk_For_Subproj_Ass end');
5086 END IF;
5087 --
5088 EXCEPTION
5089 WHEN FND_API.G_EXC_ERROR THEN
5090 IF (p_commit = FND_API.G_TRUE) THEN
5091 ROLLBACK to Copy_OG_Lnk_For_Subproj_Ass;
5092 END IF;
5093 x_msg_count := FND_MSG_PUB.count_msg;
5094 x_return_status := FND_API.G_RET_STS_ERROR;
5095 WHEN OTHERS THEN
5096 IF (p_commit = FND_API.G_TRUE) THEN
5097 ROLLBACK to Copy_OG_Lnk_For_Subproj_Ass;
5098 END IF;
5099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5100 x_msg_count := FND_MSG_PUB.count_msg;
5101 --put message
5102 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
5103 p_procedure_name => 'Copy_OG_Lnk_For_Subproj_Ass',
5104 p_error_text => SUBSTRB(SQLERRM,1,240));
5105 RAISE;
5106 END Copy_OG_Lnk_For_Subproj_Ass;
5107 --
5108 --
5109 --
5110 -- History
5111 --
5112 -- 20-Feb-04 Smukka -Created
5113 -- -Created this procedure for subproject association
5114 --
5115 -- FPM bug 3450684
5116 --
5117 --
5118 PROCEDURE Move_CI_Lnk_For_subproj_step2(p_commit IN VARCHAR2 := FND_API.G_FALSE,
5119 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
5120 p_validation_level IN VARCHAR2 := 100,
5121 p_calling_module IN VARCHAR2 := 'SELF_SERVICE',
5122 p_debug_mode IN VARCHAR2 := 'N',
5123 p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
5124 p_src_str_version_id IN NUMBER,
5125 p_dest_str_version_id IN NUMBER, /*publishing str*/
5126 p_publish_fl IN CHAR,
5127 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5128 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
5129 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5130 IS
5131 --
5132 x_object_relationship_id NUMBER;
5133 -- p_commit VARCHAR2;
5134 -- p_validate_only VARCHAR2;
5135 -- p_validation_level VARCHAR2;
5136 -- p_calling_module VARCHAR2;
5137 -- p_debug VARCHAR2;
5138 -- p_max_msg_count NUMBER;
5139 --
5140 l_return_status VARCHAR2(1);
5141 l_msg_count NUMBER;
5142 l_msg_data VARCHAR2(2000);
5143 -- API_ERROR EXCEPTION;
5144 --
5145 --
5146 /*This cursor is used to get the relationships that are coming in to the structure version id */
5147 /*from working versions */
5148 CURSOR get_coming_in_lnk_info(cp_src_str_ver_id NUMBER) IS
5149 SELECT porb.object_relationship_id,
5150 porb.object_id_to1,
5151 porb.object_id_from1,
5152 porb.object_id_to2,
5153 porb.object_id_from2,
5154 porb.relationship_type,
5155 porb.record_version_number
5156 FROM pa_object_relationships pora,
5157 pa_object_relationships porb
5158 WHERE pora.relationship_type = 'S'
5159 AND pora.object_type_from = 'PA_TASKS'
5160 AND pora.object_id_to1 = porb.object_id_from1
5161 AND pora.object_type_to = porb.object_type_from
5162 AND porb.OBJECT_ID_TO1 = cp_src_str_ver_id
5163 AND porb.object_type_to = 'PA_STRUCTURES'
5164 AND porb.relationship_type IN ('LW','LF');
5165
5166 --commented out: bug 3665487
5167 /*
5168 AND pora.OBJECT_ID_TO1 = cp_src_str_ver_id
5169 AND pora.object_type_from = 'PA_TASKS'
5170 AND pora.OBJECT_ID_from1 = ppev.ELEMENT_VERSION_ID
5171 AND ppe.proj_element_id = ppev.proj_element_id
5172 AND pora.object_id_to1=porb.object_id_from1
5173 AND porb.object_id_to2 <> porb.object_id_from2
5174 AND porb.object_type_to = 'PA_STRUCTURES'
5175 AND porb.relationship_type IN ('LW','LF')
5176 AND ppe.link_task_flag = 'Y';
5177 */
5178
5179 get_coming_in_lnk_info_rec get_coming_in_lnk_info%ROWTYPE;
5180 --
5181 l_move_link_fl VARCHAR2(1):='Y';
5182 l_proj_id NUMBER;
5183 l_pub_str_ver_enable VARCHAR2(1);
5184 l_upd_prog_grp_status NUMBER:=0;
5185 --
5186 CURSOR get_working_ver(c_ver_id NUMBER) IS
5187 Select 1 from pa_proj_element_versions a, pa_proj_elem_ver_structure b
5188 where a.element_version_id = c_ver_id
5189 and a.project_id = b.project_id
5190 and a.parent_structure_version_id = b.element_version_id
5191 and b.status_code <> 'STRUCTURE_PUBLISHED';
5192 l_dummy NUMBER;
5193
5194 -- Bug # 4329284.
5195
5196 cursor cur_proj_name (c_project_id NUMBER) is
5197 select ppa.name
5198 from pa_projects_all ppa
5199 where ppa.project_id = c_project_id;
5200
5201 l_proj_name VARCHAR2(30);
5202 l_prog_name VARCHAR2(30);
5203
5204 -- Bug # 4329284.
5205
5206 BEGIN
5207 --
5208 IF (p_debug_mode = 'Y') THEN
5209 pa_debug.debug('PA_RELATIONSHIP_PVT.Move_CI_Lnk_For_subproj_step2 begin');
5210 END IF;
5211 --
5212 IF (p_commit = FND_API.G_TRUE) THEN
5213 savepoint Move_CI_Lnk_For_subproj_step2;
5214 END IF;
5215
5216 x_return_status := FND_API.G_RET_STS_SUCCESS; -- 4537865
5217 --
5218 /* IF (p_debug_mode = 'Y') THEN
5219 pa_debug.debug('Deleting the object_relationships_id => ');
5220 END IF;*/
5221 --
5222 OPEN get_coming_in_lnk_info(p_src_str_version_id);
5223 LOOP
5224 fetch get_coming_in_lnk_info into get_coming_in_lnk_info_rec;
5225 IF get_coming_in_lnk_info%NOTFOUND THEN
5226 EXIT;
5227 END IF;
5228 l_move_link_fl:='Y';
5229 IF p_publish_fl = 'Y' THEN
5230 /* Will tell if versioning is enabled or not on the pub str*/
5231 SELECT project_id
5232 INTO l_proj_id
5233 FROM pa_proj_element_versions
5234 WHERE element_Version_id = get_coming_in_lnk_info_rec.object_id_from1;
5235 l_pub_str_ver_enable:=PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_proj_id);
5236 IF l_pub_str_ver_enable = 'N' THEN
5237 l_move_link_fl:='Y';
5238 ELSE
5239 --move if linking from working version
5240 OPEN get_working_ver(get_coming_in_lnk_info_rec.object_id_from1);
5241 FETCH get_working_ver INTO l_dummy;
5242 if Get_working_ver%FOUND THEN
5243 l_move_link_fl := 'Y';
5244 else
5245 l_move_link_fl := 'N';
5246 end if;
5247 CLOSE get_working_ver;
5248 END IF;
5249 END IF;
5250 --For Task
5251 IF l_move_link_fl='Y' THEN
5252 x_object_relationship_id := NULL;
5253 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
5254 p_user_id => FND_GLOBAL.USER_ID
5255 ,p_object_type_from => 'PA_TASKS'
5256 ,p_object_id_from1 => get_coming_in_lnk_info_rec.object_id_from1
5257 ,p_object_id_from2 => get_coming_in_lnk_info_rec.object_id_from2
5258 ,p_object_id_from3 => NULL
5259 ,p_object_id_from4 => NULL
5260 ,p_object_id_from5 => NULL
5261 ,p_object_type_to => 'PA_STRUCTURES'
5262 ,p_object_id_to1 => p_dest_str_version_id
5263 ,p_object_id_to2 => get_coming_in_lnk_info_rec.object_id_to2
5264 ,p_object_id_to3 => NULL
5265 ,p_object_id_to4 => NULL
5266 ,p_object_id_to5 => NULL
5267 ,p_relationship_type => get_coming_in_lnk_info_rec.relationship_type
5268 ,p_relationship_subtype => NULL
5269 ,p_lag_day => NULL
5270 ,p_imported_lag => NULL
5271 ,p_priority => NULL
5272 ,p_pm_product_code => NULL
5273 ,x_object_relationship_id => x_object_relationship_id
5274 ,x_return_status => x_return_status
5275 ,p_comments => null
5276 ,p_status_code => null
5277 );
5278 -- 4537865
5279 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
5280 RAISE FND_API.G_EXC_ERROR;
5281 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
5282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
5283 END IF;
5284 -- End 4537865
5285 --
5286 --Bug No 3450684
5287 BEGIN
5288 l_upd_prog_grp_status:=0;
5289 l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(x_object_relationship_id,
5290 'ADD');
5291 IF l_upd_prog_grp_status < 0 THEN
5292
5293 -- Bug # 4329284.
5294
5295 open cur_proj_name(get_coming_in_lnk_info_rec.object_id_from2);
5296 fetch cur_proj_name into l_prog_name;
5297 close cur_proj_name;
5298
5299 open cur_proj_name(get_coming_in_lnk_info_rec.object_id_to2);
5300 fetch cur_proj_name into l_proj_name;
5301 close cur_proj_name;
5302
5303 -- Bug # 4329284.
5304
5305 PA_UTILS.ADD_MESSAGE('PA','PA_CRT_SUBPROJ_VAL_FAIL','PROJ',l_proj_name,'PROG',l_prog_name); -- Bug # 4329284.
5306 RAISE FND_API.G_EXC_ERROR;
5307 END IF;
5308 IF (p_debug_mode = 'Y') THEN
5309 pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
5310 END IF;
5311 EXCEPTION
5312
5313 -- Begin fix for Bug # 4485908.
5314
5315 WHEN FND_API.G_EXC_ERROR THEN
5316
5317 RAISE FND_API.G_EXC_ERROR;
5318
5319 -- End fix for Bug # 4485908.
5320
5321 WHEN OTHERS THEN
5322 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
5323 p_procedure_name => 'Insert_Subproject_Association',
5324 p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
5325 RAISE FND_API.G_EXC_ERROR;
5326 END;
5327
5328 BEGIN
5329 l_upd_prog_grp_status:=0;
5330 l_upd_prog_grp_status:=PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS(get_coming_in_lnk_info_rec.object_relationship_id,
5331 'DROP');
5332 IF l_upd_prog_grp_status < 0 THEN
5333 PA_UTILS.ADD_MESSAGE('PA','PA_MV_DEL_SUBPROJ_VAL_FAIL');
5334 RAISE FND_API.G_EXC_ERROR;
5335 END IF;
5336 IF (p_debug_mode = 'Y') THEN
5337 pa_debug.debug('Return Status PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS=> '||l_upd_prog_grp_status);
5338 END IF;
5339 EXCEPTION
5340
5341 -- Begin fix for Bug # 4485908.
5342
5343 WHEN FND_API.G_EXC_ERROR THEN
5344
5345 RAISE FND_API.G_EXC_ERROR;
5346
5347 -- End fix for Bug # 4485908.
5348
5349 WHEN OTHERS THEN
5350 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
5351 p_procedure_name => 'Move_CI_Lnk_For_subproj_step2',
5352 p_error_text => SUBSTRB('PA_RELATIONSHIP_PUB.UPDATE_PROGRAM_GROUPS:'||SQLERRM,1,240));
5353 RAISE FND_API.G_EXC_ERROR;
5354 END;
5355 --
5356 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
5357 p_object_relationship_id => get_coming_in_lnk_info_rec.object_relationship_id
5358 ,p_object_type_from => NULL
5359 ,p_object_id_from1 => NULL
5360 ,p_object_id_from2 => NULL
5361 ,p_object_id_from3 => NULL
5362 ,p_object_id_from4 => NULL
5363 ,p_object_id_from5 => NULL
5364 ,p_object_type_to => NULL
5365 ,p_object_id_to1 => NULL
5366 ,p_object_id_to2 => NULL
5367 ,p_object_id_to3 => NULL
5368 ,p_object_id_to4 => NULL
5369 ,p_object_id_to5 => NULL
5370 ,p_record_version_number => get_coming_in_lnk_info_rec.record_version_number
5371 ,p_pm_product_code => NULL
5372 ,x_return_status => x_return_status
5373 );
5374 -- 4537865
5375 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
5376 RAISE FND_API.G_EXC_ERROR;
5377 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
5378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
5379 END IF;
5380 -- End 4537865
5381 --
5382 END IF; --End of move_link is Y
5383 END LOOP; --end loop get_coming_in_lnk_info cursor
5384 CLOSE get_coming_in_lnk_info;
5385 --
5386 IF (p_commit = FND_API.G_TRUE) THEN
5387 COMMIT;
5388 END IF;
5389 --
5390 IF (p_debug_mode = 'Y') THEN
5391 pa_debug.debug('Return status before the end of Delete_SubProject_Association=> '||x_return_status);
5392 END IF;
5393 --
5394 IF (p_debug_mode = 'Y') THEN
5395 pa_debug.debug('PA_RELATIONSHIP_PVT.Move_CI_Lnk_For_subproj_step2 end');
5396 END IF;
5397 --
5398 EXCEPTION
5399 WHEN FND_API.G_EXC_ERROR THEN
5400 IF (p_commit = FND_API.G_TRUE) THEN
5401 ROLLBACK to Move_CI_Lnk_For_subproj_step2;
5402 END IF;
5403 x_msg_count := FND_MSG_PUB.count_msg;
5404 x_return_status := FND_API.G_RET_STS_ERROR;
5405 WHEN OTHERS THEN
5406 IF (p_commit = FND_API.G_TRUE) THEN
5407 ROLLBACK to Move_CI_Lnk_For_subproj_step2;
5408 END IF;
5409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5410 x_msg_count := FND_MSG_PUB.count_msg;
5411 --put message
5412 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
5413 p_procedure_name => 'Move_CI_Lnk_For_subproj_step2',
5414 p_error_text => SUBSTRB(SQLERRM,1,240));
5415 RAISE;
5416 END Move_CI_Lnk_For_subproj_step2;
5417 --
5418 --
5419 --
5420 -- History
5421 --
5422 -- 20-Feb-04 Smukka -Created
5423 -- -Created this procedure for subproject association
5424 --
5425 -- FPM bug 3450684
5426 --
5427 --
5428 PROCEDURE Move_CI_Lnk_For_subproj_step1(p_api_version IN NUMBER :=1.0,
5429 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE,
5430 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE,
5431 -- p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL,
5432 p_validation_level IN VARCHAR2 := 100,
5433 p_calling_module IN VARCHAR2 :='SELF_SERVICE',
5434 p_commit IN VARCHAR2 :=FND_API.G_FALSE,
5435 p_debug_mode IN VARCHAR2 :='N',
5436 p_max_msg_count IN NUMBER :=PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
5437 p_src_str_version_id IN NUMBER,
5438 p_pub_str_version_id IN NUMBER, --published str, which is destination
5439 p_last_pub_str_version_id IN NUMBER,
5440 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5441 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
5442 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
5443 IS
5444 BEGIN
5445 --
5446 IF (p_debug_mode = 'Y') THEN
5447 pa_debug.debug('PA_RELATIONSHIP_PVT.Move_CI_Lnk_For_subproj_step1 begin');
5448 END IF;
5449 --
5450 IF (p_commit = FND_API.G_TRUE) THEN
5451 savepoint Move_CI_Lnk_For_subproj_step1;
5452 END IF;
5453 --
5454 /* IF (p_debug_mode = 'Y') THEN
5455 pa_debug.debug('Deleting the object_relationships_id => ');
5456 END IF;*/
5457 --
5458 /*Move all the link coming into the working structure version*/
5459 Move_CI_Lnk_For_subproj_step2(p_src_str_version_id=>p_src_str_version_id,
5460 p_dest_str_version_id=>p_pub_str_version_id,
5461 p_publish_fl=>'N',
5462 x_return_status => x_return_status,
5463 x_msg_count => x_msg_count,
5464 x_msg_data => x_msg_data);
5465 /*Move all the links coming into the last published structure version if there any */
5466 /*The links coming into the last published structure version should be coming*/
5467 /*from structure with versioning disabled */
5468 IF p_last_pub_str_version_id IS NOT NULL THEN
5469 Move_CI_Lnk_For_subproj_step2(p_src_str_version_id=>p_last_pub_str_version_id,
5470 p_dest_str_version_id => p_pub_str_version_id,
5471 p_publish_fl => 'Y',
5472 x_return_status => x_return_status,
5473 x_msg_count => x_msg_count,
5474 x_msg_data => x_msg_data
5475 );
5476 -- 4537865
5477 IF x_return_status = Fnd_Api.G_RET_STS_ERROR THEN
5478 RAISE FND_API.G_EXC_ERROR;
5479 ELSIF x_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR THEN
5480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- To go to WHEN OTHERS Block
5481 END IF;
5482 -- End 4537865
5483
5484 END IF;
5485 --
5486 IF (p_commit = FND_API.G_TRUE) THEN
5487 COMMIT;
5488 END IF;
5489 --
5490 IF (p_debug_mode = 'Y') THEN
5491 pa_debug.debug('Return status before the end of Move_CI_Lnk_For_subproj_step1=> '||x_return_status);
5492 END IF;
5493 --
5494 IF (p_debug_mode = 'Y') THEN
5495 pa_debug.debug('PA_RELATIONSHIP_PVT.Move_CI_Lnk_For_subproj_step1 end');
5496 END IF;
5497 --
5498 EXCEPTION
5499 WHEN FND_API.G_EXC_ERROR THEN
5500 IF (p_commit = FND_API.G_TRUE) THEN
5501 ROLLBACK to Move_CI_Lnk_For_subproj_step1;
5502 END IF;
5503 x_msg_count := FND_MSG_PUB.count_msg;
5504 x_return_status := FND_API.G_RET_STS_ERROR;
5505 WHEN OTHERS THEN
5506 IF (p_commit = FND_API.G_TRUE) THEN
5507 ROLLBACK to Move_CI_Lnk_For_subproj_step1;
5508 END IF;
5509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5510 x_msg_count := FND_MSG_PUB.count_msg;
5511 --put message
5512 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIPS_PVT',
5513 p_procedure_name => 'Move_CI_Lnk_For_subproj_step1',
5514 p_error_text => SUBSTRB(SQLERRM,1,240));
5515 RAISE;
5516 END Move_CI_Lnk_For_subproj_step1;
5517 --
5518 --
5519
5520 -- API name : update_parent_WBS_flag_dirty
5521 -- Type : Private Procedure
5522 -- Pre-reqs : None
5523 -- Return Value : N/A
5524 -- Parameters
5525 -- p_api_version IN NUMBER := 1.0
5526 -- p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5527 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
5528 -- p_validate_only IN VARCHAR2 := FND_API.G_TRUE
5529 -- p_validation_level IN VARCHAR2 := 100
5530 -- p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
5531 -- p_debug_mode IN VARCHAR2 := 'N'
5532 -- p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5533 -- p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5534 -- p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5535 -- x_return_status OUT VARCHAR2
5536 -- x_msg_count OUT NUMBER
5537 -- x_msg_data OUT VARCHAR2
5538 --
5539 -- History
5540 --
5541 -- 13-may-05 Maansari -Created
5542 --
5543 -- Post FPM bug 4370533
5544 --
5545 -- Description
5546 --
5547 -- This API is used to update parent links working version flag to dirty. This is called from process_wbs_updates api in publish mode.
5548
5549 procedure UPDATE_PARENT_WBS_FLAG_DIRTY
5550 (
5551 p_api_version IN NUMBER := 1.0
5552 ,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
5553 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
5554 ,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
5555 ,p_validation_level IN VARCHAR2 := 100
5556 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
5557 ,p_debug_mode IN VARCHAR2 := 'N'
5558 ,p_max_msg_count IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5559 ,p_project_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5560 ,p_structure_version_id IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
5561 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5562 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
5563 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
5564 )
5565 IS
5566 l_api_name CONSTANT VARCHAR(30) := 'UPDATE_PARENT_WBS_FLAG_DIRTY';
5567 l_api_version CONSTANT NUMBER := 1.0;
5568
5569 l_return_status VARCHAR2(1);
5570 l_msg_count NUMBER;
5571 l_msg_data VARCHAR2(250);
5572 l_data VARCHAR2(250);
5573 l_msg_index_out NUMBER;
5574 l_error_msg_code VARCHAR2(250);
5575
5576 l_lag_days NUMBER;
5577 l_comments VARCHAR2(240);
5578 l_rel_subtype VARCHAR2(30);
5579 l_debug_mode VARCHAR2(1);
5580
5581 CURSOR cur_obj_rel
5582 IS
5583 SELECT *
5584 FROM pa_object_relationships
5585 WHERE object_id_to2 = p_project_id
5586 AND object_id_to1 = p_structure_version_id
5587 AND relationship_type = 'LW'; --Financial links should not be specified here bcoz Process WBS updates can be run only for workplan structures.
5588 BEGIN
5589
5590 IF (p_debug_mode = 'Y') THEN
5591 pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_DEPENDENCY begin');
5592 END IF;
5593
5594 IF (p_commit = FND_API.G_TRUE) THEN
5595 savepoint UPDATE_PARENT_WBS_FLAG_DIRTY;
5596 END IF;
5597
5598 x_return_status := FND_API.G_RET_STS_SUCCESS;
5599
5600 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
5601
5602 IF l_debug_mode = 'Y' THEN
5603 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'p_project_id: '||p_project_id, x_Log_Level=> 3);
5604 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'p_structure_version_id: '||p_structure_version_id, x_Log_Level=> 3);
5605 END IF;
5606
5607 FOR cur_obj_rel_rec in cur_obj_rel LOOP
5608 UPDATE pa_proj_elem_ver_structure
5609 SET PROCESS_UPDATE_WBS_FLAG = 'Y',
5610 process_code = 'CPI'
5611 WHERE project_id = cur_obj_rel_rec.object_id_from2
5612 AND element_version_id=(select parent_structure_version_id
5613 FROM pa_proj_element_versions
5614 WHERE project_id=cur_obj_rel_rec.object_id_from2
5615 AND element_version_id= cur_obj_rel_rec.object_id_from1
5616 );
5617 END LOOP;
5618
5619 IF l_debug_mode = 'Y' THEN
5620 pa_debug.write(x_Module=>'PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY', x_Msg => 'Completed', x_Log_Level=> 3);
5621 END IF;
5622
5623 IF (p_commit = FND_API.G_TRUE) THEN
5624 COMMIT;
5625 END IF;
5626
5627 IF (p_debug_mode = 'Y') THEN
5628 pa_debug.debug('PA_RELATIONSHIP_PVT.UPDATE_PARENT_WBS_FLAG_DIRTY END');
5629 END IF;
5630
5631
5632 EXCEPTION
5633 when FND_API.G_EXC_ERROR then
5634 if p_commit = FND_API.G_TRUE then
5635 rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
5636 end if;
5637 x_return_status := FND_API.G_RET_STS_ERROR;
5638 when FND_API.G_EXC_UNEXPECTED_ERROR then
5639 if p_commit = FND_API.G_TRUE then
5640 rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
5641 end if;
5642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5643 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
5644 p_procedure_name => 'UPDATE_PARENT_WBS_FLAG_DIRTY',
5645 p_error_text => SUBSTRB(SQLERRM,1,240));
5646 when OTHERS then
5647 if p_commit = FND_API.G_TRUE then
5648 rollback to UPDATE_PARENT_WBS_FLAG_DIRTY;
5649 end if;
5650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5651 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_PVT',
5652 p_procedure_name => 'UPDATE_PARENT_WBS_FLAG_DIRTY',
5653 p_error_text => SUBSTRB(SQLERRM,1,240));
5654 raise;
5655 END UPDATE_PARENT_WBS_FLAG_DIRTY;
5656
5657 end PA_RELATIONSHIP_PVT;