1 PACKAGE BODY PA_PROJ_STRUC_MAPPING_UTILS AS
2 /* $Header: PAPSMPUB.pls 120.6 2007/11/28 11:41:23 vgovvala ship $ */
3
4 g_module_name VARCHAR2(100) := 'PA_PROJ_STRUC_MAPPING_UTILS';
5 Invalid_Arg_Exc_WP Exception;
6 -- Procedure : CHECK_TASK_HAS_MAPPING
7 -- Type : Public Function
8 -- Purpose : The function will check whether the mapping exists for the passed financial task_id. Returns 'Y'
9 -- : if mapping exists
10 -- NOTE : This first checks whether it is shared or not.
11 -- : If its not shared, we check in PA_OBJECT_RELATIONSHIPS for the passed project elem id
12 -- : if any relationship exists with type 'M'.
13 -- : If there is any row, 'Y' is returned else 'N' is returned
14 -- Assumptions : 1. This API will return always N in case of shared structure as mapping is not
15 -- possible in shared structure.
16 -- : 2. Financial task has just one version always
17
18 -- Parameters Type Required Description and Purpose
19 -- --------------------------- ------ -------- --------------------------------------------------------
20 -- p_project_id NUMBER Yes Task_Id for which mapping needs to be checked.
21 -- p_proj_element_id NUMBER Yes Returns to calling program whether mapping exists for the task or not.
22
23 FUNCTION Check_Task_Has_Mapping
24 (
25 p_project_id IN NUMBER
26 , p_proj_element_id IN NUMBER
27 )
28 RETURN VARCHAR2
29 IS
30 l_is_fin_task BOOLEAN;
31 l_elem_version_id NUMBER;
32 l_num_mapping VARCHAR2(1);
33 l_mapping_exists VARCHAR2(1) := 'N'; -- This is the return value, if mapping exists , it is set to 'Y'
34 l_shared VARCHAR2(1) := 'N'; -- This will be set to 'Y' only if sharing is enabled
35
36 -- This cursor fetches the element version Ids for the passed element id, of the type PA_TASKS
37 CURSOR c_get_element_version_id
38 IS
39 SELECT element_version_id
40 FROM PA_PROJ_ELEMENT_VERSIONS elver
41 WHERE elver.proj_element_id = p_proj_element_id
42 AND elver.object_type='PA_TASKS'
43 AND elver.project_id = p_project_id;
44
45 -- This cursor will select 'X' if any version of FP task exists in PA_OBJECT_RELATIONSHIPS
46 -- for Mapping type relation and passed task ver id
47
48 CURSOR c_mapping_exists (l_elem_version_id NUMBER)
49 IS
50 SELECT 'X'
51 FROM DUAL
52 WHERE EXISTS
53 ( SELECT NULL
54 FROM PA_OBJECT_RELATIONSHIPS
55 WHERE OBJECT_ID_TO1 = l_elem_version_id
56 AND relationship_type = 'M'
57 );
58
59 BEGIN
60 l_mapping_exists := 'N';
61 l_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(p_project_id);
62
63 IF l_shared = 'Y' THEN
64 -- In case of sharing, no need to check for mapping as mapping is not possible
65 return l_mapping_exists;
66 END IF;
67
68 -- Checking mapping exists
69 -- Check whether the task id passed is Financial Task or WorkPlan Task
70 -- This is done additionally so that this API can also be called for Workplan task
71 IF ( p_proj_element_id IS NOT NULL AND p_project_id IS NOT NULL ) THEN
72 IF (Pa_Proj_Elements_Utils.CHECK_IS_FINANCIAL_TASK(p_proj_element_id) <> 'Y') THEN
73 return l_mapping_exists;
74 END IF;
75
76 OPEN c_get_element_version_id;
77 FETCH c_get_element_version_id into l_elem_version_id;
78 CLOSE c_get_element_version_id;
79
80 OPEN c_mapping_exists (l_elem_version_id);
81 FETCH c_mapping_exists into l_num_mapping;
82 CLOSE c_mapping_exists;
83
84 IF (l_num_mapping = 'X') THEN
85 l_mapping_exists := 'Y';
86
87 ELSE
88 l_mapping_exists := 'N';
89 END IF;
90
91 END IF;
92 return l_mapping_exists;
93
94 EXCEPTION
95 WHEN OTHERS THEN
96
97
98 IF c_get_element_version_id%ISOPEN THEN
99 CLOSE c_get_element_version_id;
100 END IF;
101
102 IF c_mapping_exists%ISOPEN THEN
103 CLOSE c_mapping_exists;
104 END IF;
105 l_mapping_exists := NULL;
106 return l_mapping_exists;
107
108 END Check_Task_Has_Mapping;
109
110 -- Procedure : CHECK_CREATE_MAPPING_OK
111 -- Type : Public Procedure
112 -- Purpose : This procedure will check whether the mapping can be created for the passed task_id
113
114 -- NOTE : It first checks whether the financial task is lowest financial task or not.
115 -- : It then checks if a mapping already exists for on upper or lower ladder for the passed WP task id.
116
117 -- Assumptions : 1. If a summary workplan task is selected then only
118 -- : the summary task will have the link to define mappings.
119 -- :
120 -- : 2. If a summary task is selected for mapping then neither its children nor its parent
121 -- : up in the hierarchy till root can be selected for mapping.
122
123 -- Parameters Type Required Description and Purpose
124 -- --------------------------- ------ -------- --------------------------------------------------------
125 -- p_task_version_id_WP NUMBER Yes Element Version ID of from WP task
126 -- p_task_version_id_FP NUMBER Yes Element Version ID of from FP task
127
128 PROCEDURE CHECK_CREATE_MAPPING_OK
129
130 (
131 p_api_version IN NUMBER := 1.0
132 , p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
133 , p_debug_mode IN VARCHAR2 := 'N'
134 , p_task_version_id_WP IN NUMBER
135 , p_task_version_id_FP IN NUMBER
136 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
137 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
138 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
139 , x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
140
141 )
142 IS
143
144 l_msg_count NUMBER := 0;
145 l_data VARCHAR2(2000);
146 l_msg_data VARCHAR2(2000);
147 l_msg_index_out NUMBER;
148 l_debug_mode VARCHAR2(1);
149 l_dup_map VARCHAR2(1);
150
151
152 l_debug_level2 CONSTANT NUMBER := 2;
153 l_debug_level3 CONSTANT NUMBER := 3;
154 l_debug_level4 CONSTANT NUMBER := 4;
155 l_debug_level5 CONSTANT NUMBER := 5;
156
157 -- This cursor selects all task ids in the parent ladder and lower level ladder for the passed workplan task id.
158 CURSOR c_get_all_object_id
159 IS
160 SELECT object_id_to1 object_id
161 FROM pa_object_relationships
162 WHERE relationship_type ='S'
163 AND relationship_subtype ='TASK_TO_TASK'
164 START WITH object_id_from1 = p_task_version_id_WP
165 CONNECT BY object_id_from1 = PRIOR object_id_to1
166 UNION
167 SELECT object_id_from1 object_id
168 FROM pa_object_relationships
169 WHERE relationship_type = 'S'
170 AND relationship_subtype = 'TASK_TO_TASK'
171 START WITH object_id_to1 = p_task_version_id_WP
172 CONNECT BY Object_id_to1 = PRIOR object_id_from1
173 UNION
174 SELECT p_task_version_id_WP
175 FROM DUAL;
176
177 --This Cursor will return 'X' if any mapping already exists for the passed workplan task id
178 CURSOR c_dup_mapping_exists (l_from_task_id NUMBER)
179 IS
180 SELECT 'X'
181 FROM dual
182 WHERE EXISTS
183 (
184 SELECT NULL
185 FROM PA_OBJECT_RELATIONSHIPS
186 WHERE relationship_type ='M'
187 AND object_id_from1 = l_from_task_id
188 );
189
190 BEGIN
191
192
193 x_msg_count := 0;
194 x_return_status := FND_API.G_RET_STS_SUCCESS;
195 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
196
197 IF l_debug_mode = 'Y' THEN
198 PA_DEBUG.set_curr_function( p_function => 'CHECK_CREATE_MAPPING_OK',
199 p_debug_mode => l_debug_mode );
200 END IF;
201
202 IF l_debug_mode = 'Y' THEN
203 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Printing Input parameters';
204 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
205 l_debug_level3);
206
207 Pa_Debug.WRITE(g_module_name,'p_task_version_id_WP'||':'||p_task_version_id_WP,
208 l_debug_level3);
209
210 Pa_Debug.WRITE(g_module_name,'p_task_version_id_FP'||':'||p_task_version_id_FP,
211 l_debug_level3);
212 END IF;
213
214
215 IF l_debug_mode = 'Y' THEN
216 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Validating Business rule: Financial task is lowest';
217 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
218 l_debug_level3);
219 END IF;
220
221 -- Check for FP TASK to be lowest
222 IF (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id_FP) = 'N')
223 THEN
224 --Raise en error and populate message
225 x_error_message_code := 'PA_PS_NOT_LOWEST_FINTASK';
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 END IF;
228
229 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
230 return;
231 END IF;
232
233
234 IF (l_debug_mode = 'Y') THEN
235
236 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Validating Business rule: Duplicate mapping exists for WP task';
237 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
238 END IF;
239
240
241 --This loop checks for if any mapping already exists either on upper or lower ladder for the passed WP task id
242 FOR wp_rec IN c_get_all_object_id LOOP
243
244 OPEN c_dup_mapping_exists ( wp_rec.object_id );
245 FETCH c_dup_mapping_exists INTO l_dup_map;
246 CLOSE c_dup_mapping_exists;
247
248 IF (l_dup_map = 'X')
249 THEN
250 x_error_message_code := 'PA_PS_DUP_MAP_EXISTS';
251 x_return_status := FND_API.G_RET_STS_ERROR;
252 EXIT;
253 END IF;
254 END LOOP;
255
256 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
257 return;
258 END IF;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262
263 x_error_message_code := SQLCODE ; -- RESET OUT PARAM x_error_message_code : 4537865
264
265 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
266 x_msg_count := 1;
267 x_msg_data := SQLERRM;
268
269 IF c_get_all_object_id%ISOPEN THEN
270 CLOSE c_get_all_object_id;
271 END IF;
272
273 Fnd_Msg_Pub.add_exc_msg
274 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_UTILS'
275 ,p_procedure_name => 'CHECK_CREATE_MAPPING_OK'
276 ,p_error_text => x_msg_data);
277
278 IF l_debug_mode = 'Y' THEN
279 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
280 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
281 l_debug_level5);
282 Pa_Debug.reset_curr_function;
283 END IF;
284 RAISE;
285
286 END CHECK_CREATE_MAPPING_OK;
287
288 -- Procedure : PARSE_NAMES
289 -- Type : Public Function
290 -- Purpose : This function will return a pl/sql table of tokens separated by delimiter which are passed as input to this function
291
292 -- NOTE : This parses the input string for tokens separted by delimiter and puts each token in PL/SQL table,
293 -- : which is finally returned by the function
294
295
296 -- Parameters Type Required Description and Purpose
297 -- --------------------------- ------ -------- --------------------------------------------------------
298 -- p_wPlan VARCHAR2 Yes String to be parsed
299 -- p_delim VARCHAR2 Yes Delimiter character or string
300
301 FUNCTION PARSE_NAMES
302 (
303 p_wPlan IN VARCHAR2
304 , p_delim IN VARCHAR2
305
306 ) RETURN PA_PROJ_STRUC_MAPPING_UTILS.TASK_NAME_TABLE_TYPE
307 IS
308
309 l_workplan_name_table_type PA_PROJ_STRUC_MAPPING_UTILS.TASK_NAME_TABLE_TYPE;
310 l_str_length NUMBER;
311 l_start_pos NUMBER := 1;
312 l_curr_pos NUMBER;
313 l_counter NUMBER := 0;
314
315 BEGIN
316 IF ( p_wPlan IS NOT NULL AND p_delim IS NOT NULL )
317 THEN
318 --Get the length of string
319 l_str_length := LENGTH ( p_wPlan );
320
321 LOOP
322 -- get the first postion of delimiter character
323 l_curr_pos := INSTR(p_wPlan,p_delim,l_start_pos);
324
325 IF l_curr_pos = 0
326 THEN
327 --This is to get last token
328 l_workplan_name_table_type(l_counter):= SUBSTR( p_wPlan,l_start_pos);
329
330 l_counter := l_counter + 1;
331 ELSE
332 --This is to get other tokens
333 l_workplan_name_table_type(l_counter) := SUBSTR( p_wPlan,l_start_pos,l_curr_pos - l_start_pos);
334 l_counter := l_counter + 1;
335 END IF;
336
337 IF l_curr_pos = 0
338 THEN
339 EXIT;
340 ELSE
341 l_start_pos := l_curr_pos + 1;
342 END IF;
343 END LOOP;
344
345 ELSE
346 RAISE Invalid_Arg_Exc_WP;
347
348 END IF;
349 return l_workplan_name_table_type;
350
351 EXCEPTION
352
353 WHEN Invalid_Arg_Exc_WP THEN
354 return l_workplan_name_table_type;
355
356 WHEN OTHERS THEN
357 return l_workplan_name_table_type;
358
359 END PARSE_NAMES;
360
361 -- Procedure : GET_TASK_NAME_FROM_VERSION
362 -- Type : Public Function
363 -- Purpose : To get the task name of given task version id
364
365 -- NOTE :
366
367
368 -- Parameters Type Required Description and Purpose
369 -- --------------------------- ------ -------- --------------------------------------------------------
370 -- p_task_version_id NUMBER Yes task version id of the task whose name is to be found
371
372 FUNCTION GET_TASK_NAME_FROM_VERSION
373 ( p_task_version_id IN NUMBER
374 ) RETURN VARCHAR2
375 IS
376
377 l_task_name pa_proj_elements.name%TYPE;
378
379 CURSOR c_get_task_name IS
380 SELECT projelem.name
381 FROM pa_proj_elements projelem
382 , pa_proj_element_versions elemver
383 WHERE elemver.element_version_id = p_task_version_id
384 AND elemver.proj_element_id = projelem.proj_element_id
385 AND elemver.object_type = 'PA_TASKS'
386 AND projelem.object_type = 'PA_TASKS'
387 AND elemver.project_id = projelem.project_id;
388
389 BEGIN
390
391 -- Bug Fix 5611948.
392 -- The cursor is uncoditionally getting executed even when the passed in value is null.
393 -- This is causing numerous executions especially during the temp table population where
394 -- this is getting called. It is very much possible that the mapped task id is null.
395 -- In order to avoid the numerous executions we can make code change at the calling point to
396 -- see if the id is null. if null we dont call this. another approach is to make the change
397 -- in the core so if more than one calling point is there then we can minimize the code changes
398 -- as this will return null if the passed in id is null.
399
400 IF p_task_version_id IS NULL THEN
401
402 l_task_name := NULL;
403 return l_task_name;
404
405 END IF;
406
407
408 OPEN c_get_task_name;
409 FETCH c_get_task_name INTO l_task_name;
410 CLOSE c_get_task_name;
411
412 return l_task_name;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 l_task_name := null;
417 return l_task_name;
418 END GET_TASK_NAME_FROM_VERSION;
419
420 FUNCTION GET_MAPPED_FIN_TASK_VERSION_ID
421 (p_element_version_id IN NUMBER
422 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
423 IS
424 cursor C1 (evid number) is
425 select object_id_from1,object_id_to1
426 from pa_object_relationships
427 where relationship_type='S'
428 and object_type_to='PA_TASKS'
429 connect by prior object_id_from1 = object_id_to1
430 and prior relationship_type = relationship_type -- Bug # 4621730.
431 start with object_id_to1 = C1.evid;
432
433 l_mapped_fin_task_version_id NUMBER;
434 l_proj_element_id NUMBER;
435 l_c1rec C1%ROWTYPE;
436 BEGIN
437 l_mapped_fin_task_version_id := NULL;
438 if (p_structure_sharing_code = 'SPLIT_MAPPING') then
439 for l_c1rec in C1(p_element_version_id)
440 LOOP
441 BEGIN -- Added exception block for Bug# 6411931
442 select object_id_to1 into l_mapped_fin_task_version_id
443 from pa_object_relationships
444 where relationship_type='M'
445 and object_type_from='PA_TASKS'
446 and object_type_to='PA_TASKS'
447 and object_id_from1 = l_c1rec.object_id_to1;
448 EXCEPTION
449 WHEN NO_DATA_FOUND THEN
450 null;
451 END;
452
453 if l_mapped_fin_task_version_id IS NOT NULL then
454 EXIT;
455 end if;
456 END LOOP;
457 elsif (p_structure_sharing_code = 'SHARE_PARTIAL') then
458 for l_c1rec in C1(p_element_version_id)
459 LOOP
460 select proj_element_id into l_proj_element_id
461 from pa_proj_element_versions
462 where element_version_id = l_c1rec.object_id_to1;
463 if (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') then
464 l_mapped_fin_task_version_id := l_c1rec.object_id_to1;
465 end if;
466 if l_mapped_fin_task_version_id IS NOT NULL then
467 EXIT;
468 end if;
469 END LOOP;
470 end if;
471 return (l_mapped_fin_task_version_id);
472 EXCEPTION
473 WHEN OTHERS THEN
474 l_mapped_fin_task_version_id := NULL;
475 return l_mapped_fin_task_version_id;
476 END GET_MAPPED_FIN_TASK_VERSION_ID;
477
478
479 FUNCTION GET_MAPPED_FIN_TASK_ID
480 (p_element_version_id IN NUMBER
481 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
482 IS
483 l_mapped_fin_task_version_id NUMBER;
484 l_mapped_fin_task_id NUMBER;
485 BEGIN
486 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
487 select proj_element_id into l_mapped_fin_task_id
488 from pa_proj_element_versions
489 where element_version_id = l_mapped_fin_task_version_id;
490 return (l_mapped_fin_task_id);
491 EXCEPTION
492 WHEN OTHERS THEN
493 l_mapped_fin_task_id := NULL;
494 return l_mapped_fin_task_id;
495 END GET_MAPPED_FIN_TASK_ID;
496
497
498 FUNCTION GET_MAPPED_FIN_TASK_NAME
499 (p_element_version_id IN NUMBER
500 ,p_structure_sharing_code IN VARCHAR2) RETURN VARCHAR2
501 IS
502 l_mapped_fin_task_version_id NUMBER;
503 l_mapped_fin_task_name VARCHAR2(30);
504 BEGIN
505 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
506 l_mapped_fin_task_name := GET_TASK_NAME_FROM_VERSION(l_mapped_fin_task_version_id);
507 return (l_mapped_fin_task_name);
508 EXCEPTION
509 WHEN OTHERS THEN
510 l_mapped_fin_task_name := NULL;
511 return l_mapped_fin_task_name;
512 END GET_MAPPED_FIN_TASK_NAME;
513
514 FUNCTION GET_MAPPED_STRUCT_VER_ID
515 (p_element_version_id IN NUMBER
516 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
517 IS
518 l_mapped_fin_task_version_id NUMBER;
519 l_mapped_structure_version_id NUMBER;
520 BEGIN
521 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
522 select parent_structure_version_id into l_mapped_structure_version_id
523 from pa_proj_element_versions
524 where element_version_id = l_mapped_fin_task_version_id;
525 return (l_mapped_structure_version_id);
526 EXCEPTION
527 WHEN OTHERS THEN
528 l_mapped_structure_version_id := NULL;
529 return l_mapped_structure_version_id;
530 END GET_MAPPED_STRUCT_VER_ID;
531
532 --Added by rtarway to get mapped wkp task names
533 FUNCTION GET_MAPPED_WKP_TASK_NAMES
534 (
535 p_mapped_fin_task_version_id IN NUMBER
536 ,p_project_id IN NUMBER
537 ) RETURN VARCHAR2
538 IS
539 CURSOR C_get_mapped_wkp_task_names IS
540 select ppe.name
541 from
542 pa_proj_elements ppe,
543 pa_proj_element_versions ppev,
544 pa_object_relationships por_mapping
545 where
546 ppe.proj_element_id=ppev.proj_element_id
547 and
548 ppe.project_id = ppev.project_id
549 and
550 ppev.project_id = p_project_id
551 and
552 ppev.element_version_id = por_mapping.object_id_from1
553 and
554 por_mapping.object_id_to1 = p_mapped_fin_task_version_id
555 and
556 por_mapping.relationship_type = 'M';
557
558 l_mapped_wkp_task_names VARCHAR2(10000);
559
560 BEGIN
561 l_mapped_wkp_task_names := '';
562 for l_rec in C_get_mapped_wkp_task_names loop
563
564 l_mapped_wkp_task_names := l_mapped_wkp_task_names||l_rec.name||',';
565
566 end loop;
567
568 --strip last comma
569 l_mapped_wkp_task_names := rtrim(l_mapped_wkp_task_names, ',');
570
571 return l_mapped_wkp_task_names;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 l_mapped_wkp_task_names := NULL;
576 return l_mapped_wkp_task_names;
577 END GET_MAPPED_WKP_TASK_NAMES;
578
579
580 --Added by rtarway to get mapped wkp task ids
581 FUNCTION GET_MAPPED_WKP_TASK_IDS
582 (
583 p_mapped_fin_task_version_id IN NUMBER
584 ,p_project_id IN NUMBER
585 ) RETURN VARCHAR2
586 IS
587 CURSOR C_get_mapped_wkp_task_Ids IS
588 select ppe.proj_element_id
589 from
590 pa_proj_elements ppe,
591 pa_proj_element_versions ppev,
592 pa_object_relationships por_mapping
593 where
594 ppe.proj_element_id=ppev.proj_element_id
595 and
596 ppe.project_id = ppev.project_id
597 and
598 ppev.project_id = p_project_id
599 and
600 ppev.element_version_id = por_mapping.object_id_from1
601 and
602 por_mapping.object_id_to1 = p_mapped_fin_task_version_id
603 and
604 por_mapping.relationship_type = 'M';
605
606 l_mapped_wkp_task_ids VARCHAR2(10000);
607
608 BEGIN
609 l_mapped_wkp_task_ids := '';
610 for l_rec in C_get_mapped_wkp_task_Ids loop
611
612 l_mapped_wkp_task_ids := l_mapped_wkp_task_ids||l_rec.proj_element_id||',';
613
614 end loop;
615
616 --strip last comma
617 l_mapped_wkp_task_ids := rtrim(l_mapped_wkp_task_ids, ',');
618
619 return l_mapped_wkp_task_ids;
620
621 EXCEPTION
622 WHEN OTHERS THEN
623 l_mapped_wkp_task_ids := NULL;
624 return l_mapped_wkp_task_ids;
625 END GET_MAPPED_WKP_TASK_IDS;
626
627 --Added by rtarway to get mapped wkp task ids
628 FUNCTION GET_MAPPED_FIN_TASK_ID_AMG
629 (
630 p_mapped_wkp_task_version_id IN NUMBER
631 ,p_project_id IN NUMBER
632 ) RETURN NUMBER
633 IS
634 CURSOR C_get_mapped_fin_task_Id IS
635 select ppe.proj_element_id
636 from
637 pa_proj_elements ppe,
638 pa_proj_element_versions ppev,
639 pa_object_relationships por_mapping
640 where
641 ppe.proj_element_id=ppev.proj_element_id
642 and
643 ppe.project_id = ppev.project_id
644 and
645 ppev.project_id = p_project_id
646 and
647 ppev.element_version_id = por_mapping.object_id_to1
648 and
649 por_mapping.object_id_from1 = p_mapped_wkp_task_version_id
650 and
651 por_mapping.relationship_type = 'M';
652
653 l_mapped_fin_task_id NUMBER;
654
655 BEGIN
656 l_mapped_fin_task_id := NULL;
657
658 OPEN C_get_mapped_fin_task_Id;
659 FETCH C_get_mapped_fin_task_Id INTO l_mapped_fin_task_id;
660 CLOSE C_get_mapped_fin_task_Id;
661
662 return l_mapped_fin_task_id;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666 l_mapped_fin_task_id := NULL;
667 return l_mapped_fin_task_id;
668 END GET_MAPPED_FIN_TASK_ID_AMG;
669
670 --Added by rtarway to get mapped wkp task ids
671 FUNCTION GET_MAPPED_FIN_TASK_NAME_AMG
672 (
673 p_mapped_wkp_task_version_id IN NUMBER
674 ,p_project_id IN NUMBER
675 ) RETURN VARCHAR2
676 IS
677 CURSOR C_get_mapped_fin_task_name IS
678 select ppe.name
679 from
680 pa_proj_elements ppe,
681 pa_proj_element_versions ppev,
682 pa_object_relationships por_mapping
683 where
684 ppe.proj_element_id=ppev.proj_element_id
685 and
686 ppe.project_id = ppev.project_id
687 and
688 ppev.project_id = p_project_id
689 and
690 ppev.element_version_id = por_mapping.object_id_to1
691 and
692 por_mapping.object_id_from1 = p_mapped_wkp_task_version_id
693 and
694 por_mapping.relationship_type = 'M';
695
696 l_mapped_fin_task_name VARCHAR2(240);
697
698 BEGIN
699 l_mapped_fin_task_name := NULL;
700 OPEN C_get_mapped_fin_task_name;
701 FETCH C_get_mapped_fin_task_name INTO l_mapped_fin_task_name;
702 CLOSE C_get_mapped_fin_task_name;
703
704 return l_mapped_fin_task_name;
705
706 EXCEPTION
707 WHEN OTHERS THEN
708 l_mapped_fin_task_name := NULL;
709 return l_mapped_fin_task_name;
710 END GET_MAPPED_FIN_TASK_NAME_AMG;
711
712 END PA_PROJ_STRUC_MAPPING_UTILS;
[Home] [Help]