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