1 PACKAGE BODY PA_PROJ_STRUC_MAPPING_UTILS AS
2 /* $Header: PAPSMPUB.pls 120.8 2011/01/07 05:56:55 smijosep 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 -- Bug 9662785
159 CURSOR c_get_all_object_id
160 IS
161 SELECT object_id_to1 object_id
162 FROM pa_object_relationships
163 WHERE relationship_type ='S'
164 AND relationship_subtype ='TASK_TO_TASK'
165 START WITH object_id_from1 = p_task_version_id_WP
166 AND object_type_from = 'PA_TASKS'
167 AND relationship_type = 'S'
168 CONNECT BY object_id_from1 = PRIOR object_id_to1
169 AND object_type_from = PRIOR object_type_to
170 AND relationship_type = PRIOR relationship_type
171 UNION
172 SELECT object_id_from1 object_id
173 FROM pa_object_relationships
174 WHERE relationship_type = 'S'
175 AND relationship_subtype = 'TASK_TO_TASK'
176 START WITH object_id_to1 = p_task_version_id_WP
177 AND object_type_to = 'PA_TASKS'
178 AND relationship_type = 'S'
179 CONNECT BY Object_id_to1 = PRIOR object_id_from1
180 AND object_type_to = PRIOR object_type_from
181 AND relationship_type = PRIOR relationship_type
182 UNION
183 SELECT p_task_version_id_WP
184 FROM DUAL;
185
186 --This Cursor will return 'X' if any mapping already exists for the passed workplan task id
187 CURSOR c_dup_mapping_exists (l_from_task_id NUMBER)
188 IS
189 SELECT 'X'
190 FROM dual
191 WHERE EXISTS
192 (
193 SELECT NULL
194 FROM PA_OBJECT_RELATIONSHIPS
195 WHERE relationship_type ='M'
196 AND object_id_from1 = l_from_task_id
197 );
198
199 BEGIN
200
201
202 x_msg_count := 0;
203 x_return_status := FND_API.G_RET_STS_SUCCESS;
204 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
205
206 IF l_debug_mode = 'Y' THEN
207 PA_DEBUG.set_curr_function( p_function => 'CHECK_CREATE_MAPPING_OK',
208 p_debug_mode => l_debug_mode );
209 END IF;
210
211 IF l_debug_mode = 'Y' THEN
212 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Printing Input parameters';
213 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
214 l_debug_level3);
215
216 Pa_Debug.WRITE(g_module_name,'p_task_version_id_WP'||':'||p_task_version_id_WP,
217 l_debug_level3);
218
219 Pa_Debug.WRITE(g_module_name,'p_task_version_id_FP'||':'||p_task_version_id_FP,
220 l_debug_level3);
221 END IF;
222
223
224 IF l_debug_mode = 'Y' THEN
225 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Validating Business rule: Financial task is lowest';
226 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
227 l_debug_level3);
228 END IF;
229
230 -- Check for FP TASK to be lowest
231 IF (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id_FP) = 'N')
232 THEN
233 --Raise en error and populate message
234 x_error_message_code := 'PA_PS_NOT_LOWEST_FINTASK';
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 END IF;
237
238 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
239 return;
240 END IF;
241
242
243 IF (l_debug_mode = 'Y') THEN
244
245 Pa_Debug.g_err_stage:= 'PA_PROJ_STRUC_MAPPING_UTILS : CHECK_CREATE_MAPPING_OK : Validating Business rule: Duplicate mapping exists for WP task';
246 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage, l_debug_level3);
247 END IF;
248
249
250 --This loop checks for if any mapping already exists either on upper or lower ladder for the passed WP task id
251 FOR wp_rec IN c_get_all_object_id LOOP
252
253 OPEN c_dup_mapping_exists ( wp_rec.object_id );
254 FETCH c_dup_mapping_exists INTO l_dup_map;
255 CLOSE c_dup_mapping_exists;
256
257 IF (l_dup_map = 'X')
258 THEN
259 x_error_message_code := 'PA_PS_DUP_MAP_EXISTS';
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 EXIT;
262 END IF;
263 END LOOP;
264
265 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
266 return;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271
272 x_error_message_code := SQLCODE ; -- RESET OUT PARAM x_error_message_code : 4537865
273
274 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
275 x_msg_count := 1;
276 x_msg_data := SQLERRM;
277
278 IF c_get_all_object_id%ISOPEN THEN
279 CLOSE c_get_all_object_id;
280 END IF;
281
282 Fnd_Msg_Pub.add_exc_msg
283 ( p_pkg_name => 'PA_PROJ_STRUC_MAPPING_UTILS'
284 ,p_procedure_name => 'CHECK_CREATE_MAPPING_OK'
285 ,p_error_text => x_msg_data);
286
287 IF l_debug_mode = 'Y' THEN
288 Pa_Debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
289 Pa_Debug.WRITE(g_module_name,Pa_Debug.g_err_stage,
290 l_debug_level5);
291 Pa_Debug.reset_curr_function;
292 END IF;
293 RAISE;
294
295 END CHECK_CREATE_MAPPING_OK;
296
297 -- Procedure : PARSE_NAMES
298 -- Type : Public Function
299 -- Purpose : This function will return a pl/sql table of tokens separated by delimiter which are passed as input to this function
300
301 -- NOTE : This parses the input string for tokens separted by delimiter and puts each token in PL/SQL table,
302 -- : which is finally returned by the function
303
304
305 -- Parameters Type Required Description and Purpose
306 -- --------------------------- ------ -------- --------------------------------------------------------
307 -- p_wPlan VARCHAR2 Yes String to be parsed
308 -- p_delim VARCHAR2 Yes Delimiter character or string
309
310 FUNCTION PARSE_NAMES
311 (
312 p_wPlan IN VARCHAR2
313 , p_delim IN VARCHAR2
314
315 ) RETURN PA_PROJ_STRUC_MAPPING_UTILS.TASK_NAME_TABLE_TYPE
316 IS
317
318 l_workplan_name_table_type PA_PROJ_STRUC_MAPPING_UTILS.TASK_NAME_TABLE_TYPE;
319 l_str_length NUMBER;
320 l_start_pos NUMBER := 1;
321 l_curr_pos NUMBER;
322 l_counter NUMBER := 0;
323
324 BEGIN
325 IF ( p_wPlan IS NOT NULL AND p_delim IS NOT NULL )
326 THEN
327 --Get the length of string
328 l_str_length := LENGTH ( p_wPlan );
329
330 LOOP
331 -- get the first postion of delimiter character
332 l_curr_pos := INSTR(p_wPlan,p_delim,l_start_pos);
333
334 IF l_curr_pos = 0
335 THEN
336 --This is to get last token
337 l_workplan_name_table_type(l_counter):= SUBSTR( p_wPlan,l_start_pos);
338
339 l_counter := l_counter + 1;
340 ELSE
341 --This is to get other tokens
342 l_workplan_name_table_type(l_counter) := SUBSTR( p_wPlan,l_start_pos,l_curr_pos - l_start_pos);
343 l_counter := l_counter + 1;
344 END IF;
345
346 IF l_curr_pos = 0
347 THEN
348 EXIT;
349 ELSE
350 l_start_pos := l_curr_pos + 1;
351 END IF;
352 END LOOP;
353
354 ELSE
355 RAISE Invalid_Arg_Exc_WP;
356
357 END IF;
358 return l_workplan_name_table_type;
359
360 EXCEPTION
361
362 WHEN Invalid_Arg_Exc_WP THEN
363 return l_workplan_name_table_type;
364
365 WHEN OTHERS THEN
366 return l_workplan_name_table_type;
367
368 END PARSE_NAMES;
369
370 -- Procedure : GET_TASK_NAME_FROM_VERSION
371 -- Type : Public Function
372 -- Purpose : To get the task name of given task version id
373
374 -- NOTE :
375
376
377 -- Parameters Type Required Description and Purpose
378 -- --------------------------- ------ -------- --------------------------------------------------------
379 -- p_task_version_id NUMBER Yes task version id of the task whose name is to be found
380
381 FUNCTION GET_TASK_NAME_FROM_VERSION
382 ( p_task_version_id IN NUMBER
383 ) RETURN VARCHAR2
384 IS
385
386 l_task_name pa_proj_elements.name%TYPE;
387
388 CURSOR c_get_task_name IS
389 SELECT projelem.name
390 FROM pa_proj_elements projelem
391 , pa_proj_element_versions elemver
392 WHERE elemver.element_version_id = p_task_version_id
393 AND elemver.proj_element_id = projelem.proj_element_id
394 AND elemver.object_type = 'PA_TASKS'
395 AND projelem.object_type = 'PA_TASKS'
396 AND elemver.project_id = projelem.project_id;
397
398 BEGIN
399
400 -- Bug Fix 5611948.
401 -- The cursor is uncoditionally getting executed even when the passed in value is null.
402 -- This is causing numerous executions especially during the temp table population where
403 -- this is getting called. It is very much possible that the mapped task id is null.
404 -- In order to avoid the numerous executions we can make code change at the calling point to
405 -- see if the id is null. if null we dont call this. another approach is to make the change
406 -- in the core so if more than one calling point is there then we can minimize the code changes
407 -- as this will return null if the passed in id is null.
408
409 IF p_task_version_id IS NULL THEN
410
411 l_task_name := NULL;
412 return l_task_name;
413
414 END IF;
415
416
417 OPEN c_get_task_name;
418 FETCH c_get_task_name INTO l_task_name;
419 CLOSE c_get_task_name;
420
421 return l_task_name;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 l_task_name := null;
426 return l_task_name;
427 END GET_TASK_NAME_FROM_VERSION;
428
429 FUNCTION GET_MAPPED_FIN_TASK_VERSION_ID
430 (p_element_version_id IN NUMBER
431 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
432 IS
433 cursor C1 (evid number) is
434 select object_id_from1,object_id_to1
435 from pa_object_relationships
436 where relationship_type='S'
437 and object_type_to='PA_TASKS'
438 connect by prior object_id_from1 = object_id_to1
439 and prior relationship_type = relationship_type -- Bug # 4621730.
440 start with object_id_to1 = C1.evid;
441
442 l_mapped_fin_task_version_id NUMBER;
443 l_proj_element_id NUMBER;
444 l_c1rec C1%ROWTYPE;
445 BEGIN
446 l_mapped_fin_task_version_id := NULL;
447 if (p_structure_sharing_code = 'SPLIT_MAPPING') then
448 for l_c1rec in C1(p_element_version_id)
449 LOOP
450 BEGIN -- Added exception block for Bug# 6411931
451 select object_id_to1 into l_mapped_fin_task_version_id
452 from pa_object_relationships
453 where relationship_type='M'
454 and object_type_from='PA_TASKS'
455 and object_type_to='PA_TASKS'
456 and object_id_from1 = l_c1rec.object_id_to1;
457 EXCEPTION
458 WHEN NO_DATA_FOUND THEN
459 null;
460 END;
461
462 if l_mapped_fin_task_version_id IS NOT NULL then
463 EXIT;
464 end if;
465 END LOOP;
466 elsif (p_structure_sharing_code = 'SHARE_PARTIAL') then
467 for l_c1rec in C1(p_element_version_id)
468 LOOP
469 select proj_element_id into l_proj_element_id
470 from pa_proj_element_versions
471 where element_version_id = l_c1rec.object_id_to1;
472 if (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_proj_element_id) = 'Y') then
473 l_mapped_fin_task_version_id := l_c1rec.object_id_to1;
474 end if;
475 if l_mapped_fin_task_version_id IS NOT NULL then
476 EXIT;
477 end if;
478 END LOOP;
479 end if;
480 return (l_mapped_fin_task_version_id);
481 EXCEPTION
482 WHEN OTHERS THEN
483 l_mapped_fin_task_version_id := NULL;
484 return l_mapped_fin_task_version_id;
485 END GET_MAPPED_FIN_TASK_VERSION_ID;
486
487
488 FUNCTION GET_MAPPED_FIN_TASK_ID
489 (p_element_version_id IN NUMBER
490 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
491 IS
492 l_mapped_fin_task_version_id NUMBER;
493 l_mapped_fin_task_id NUMBER;
494 BEGIN
495 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
496 select proj_element_id into l_mapped_fin_task_id
497 from pa_proj_element_versions
498 where element_version_id = l_mapped_fin_task_version_id;
499 return (l_mapped_fin_task_id);
500 EXCEPTION
501 WHEN OTHERS THEN
502 l_mapped_fin_task_id := NULL;
503 return l_mapped_fin_task_id;
504 END GET_MAPPED_FIN_TASK_ID;
505
506
507 FUNCTION GET_MAPPED_FIN_TASK_NAME
508 (p_element_version_id IN NUMBER
509 ,p_structure_sharing_code IN VARCHAR2) RETURN VARCHAR2
510 IS
511 l_mapped_fin_task_version_id NUMBER;
512 l_mapped_fin_task_name pa_proj_elements.name%TYPE; --Bug8443049:BIG WORDS DO NOT APPEAR IN THE FIELD FINANCIAL TASK MAPPED IN WBS
513 BEGIN
514 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
515 l_mapped_fin_task_name := GET_TASK_NAME_FROM_VERSION(l_mapped_fin_task_version_id);
516 return (l_mapped_fin_task_name);
517 EXCEPTION
518 WHEN OTHERS THEN
519 l_mapped_fin_task_name := NULL;
520 return l_mapped_fin_task_name;
521 END GET_MAPPED_FIN_TASK_NAME;
522
523 FUNCTION GET_MAPPED_STRUCT_VER_ID
524 (p_element_version_id IN NUMBER
525 ,p_structure_sharing_code IN VARCHAR2) RETURN NUMBER
526 IS
527 l_mapped_fin_task_version_id NUMBER;
528 l_mapped_structure_version_id NUMBER;
529 BEGIN
530 l_mapped_fin_task_version_id := GET_MAPPED_FIN_TASK_VERSION_ID(p_element_version_id,p_structure_sharing_code);
531 select parent_structure_version_id into l_mapped_structure_version_id
532 from pa_proj_element_versions
533 where element_version_id = l_mapped_fin_task_version_id;
534 return (l_mapped_structure_version_id);
535 EXCEPTION
536 WHEN OTHERS THEN
537 l_mapped_structure_version_id := NULL;
538 return l_mapped_structure_version_id;
539 END GET_MAPPED_STRUCT_VER_ID;
540
541 --Added by rtarway to get mapped wkp task names
542 FUNCTION GET_MAPPED_WKP_TASK_NAMES
543 (
544 p_mapped_fin_task_version_id IN NUMBER
545 ,p_project_id IN NUMBER
546 ) RETURN VARCHAR2
547 IS
548 CURSOR C_get_mapped_wkp_task_names IS
549 select ppe.name
550 from
551 pa_proj_elements ppe,
552 pa_proj_element_versions ppev,
553 pa_object_relationships por_mapping
554 where
555 ppe.proj_element_id=ppev.proj_element_id
556 and
557 ppe.project_id = ppev.project_id
558 and
559 ppev.project_id = p_project_id
560 and
561 ppev.element_version_id = por_mapping.object_id_from1
562 and
563 por_mapping.object_id_to1 = p_mapped_fin_task_version_id
564 and
565 por_mapping.relationship_type = 'M';
566
567 l_mapped_wkp_task_names VARCHAR2(10000);
568
569 BEGIN
570 l_mapped_wkp_task_names := '';
571 for l_rec in C_get_mapped_wkp_task_names loop
572
573 l_mapped_wkp_task_names := l_mapped_wkp_task_names||l_rec.name||',';
574
575 end loop;
576
577 --strip last comma
578 l_mapped_wkp_task_names := rtrim(l_mapped_wkp_task_names, ',');
579
580 return l_mapped_wkp_task_names;
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 l_mapped_wkp_task_names := NULL;
585 return l_mapped_wkp_task_names;
586 END GET_MAPPED_WKP_TASK_NAMES;
587
588
589 --Added by rtarway to get mapped wkp task ids
590 FUNCTION GET_MAPPED_WKP_TASK_IDS
591 (
592 p_mapped_fin_task_version_id IN NUMBER
593 ,p_project_id IN NUMBER
594 ) RETURN VARCHAR2
595 IS
596 CURSOR C_get_mapped_wkp_task_Ids IS
597 select ppe.proj_element_id
598 from
599 pa_proj_elements ppe,
600 pa_proj_element_versions ppev,
601 pa_object_relationships por_mapping
602 where
603 ppe.proj_element_id=ppev.proj_element_id
604 and
605 ppe.project_id = ppev.project_id
606 and
607 ppev.project_id = p_project_id
608 and
609 ppev.element_version_id = por_mapping.object_id_from1
610 and
611 por_mapping.object_id_to1 = p_mapped_fin_task_version_id
612 and
613 por_mapping.relationship_type = 'M';
614
615 l_mapped_wkp_task_ids VARCHAR2(10000);
616
617 BEGIN
618 l_mapped_wkp_task_ids := '';
619 for l_rec in C_get_mapped_wkp_task_Ids loop
620
621 l_mapped_wkp_task_ids := l_mapped_wkp_task_ids||l_rec.proj_element_id||',';
622
623 end loop;
624
625 --strip last comma
626 l_mapped_wkp_task_ids := rtrim(l_mapped_wkp_task_ids, ',');
627
628 return l_mapped_wkp_task_ids;
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 l_mapped_wkp_task_ids := NULL;
633 return l_mapped_wkp_task_ids;
634 END GET_MAPPED_WKP_TASK_IDS;
635
636 --Added by rtarway to get mapped wkp task ids
637 FUNCTION GET_MAPPED_FIN_TASK_ID_AMG
638 (
639 p_mapped_wkp_task_version_id IN NUMBER
640 ,p_project_id IN NUMBER
641 ) RETURN NUMBER
642 IS
643 CURSOR C_get_mapped_fin_task_Id IS
644 select ppe.proj_element_id
645 from
646 pa_proj_elements ppe,
647 pa_proj_element_versions ppev,
648 pa_object_relationships por_mapping
649 where
650 ppe.proj_element_id=ppev.proj_element_id
651 and
652 ppe.project_id = ppev.project_id
653 and
654 ppev.project_id = p_project_id
655 and
656 ppev.element_version_id = por_mapping.object_id_to1
657 and
658 por_mapping.object_id_from1 = p_mapped_wkp_task_version_id
659 and
660 por_mapping.relationship_type = 'M';
661
662 l_mapped_fin_task_id NUMBER;
663
664 BEGIN
665 l_mapped_fin_task_id := NULL;
666
667 OPEN C_get_mapped_fin_task_Id;
668 FETCH C_get_mapped_fin_task_Id INTO l_mapped_fin_task_id;
669 CLOSE C_get_mapped_fin_task_Id;
670
671 return l_mapped_fin_task_id;
672
673 EXCEPTION
674 WHEN OTHERS THEN
675 l_mapped_fin_task_id := NULL;
676 return l_mapped_fin_task_id;
677 END GET_MAPPED_FIN_TASK_ID_AMG;
678
679 --Added by rtarway to get mapped wkp task ids
680 FUNCTION GET_MAPPED_FIN_TASK_NAME_AMG
681 (
682 p_mapped_wkp_task_version_id IN NUMBER
683 ,p_project_id IN NUMBER
684 ) RETURN VARCHAR2
685 IS
686 CURSOR C_get_mapped_fin_task_name IS
687 select ppe.name
688 from
689 pa_proj_elements ppe,
690 pa_proj_element_versions ppev,
691 pa_object_relationships por_mapping
692 where
693 ppe.proj_element_id=ppev.proj_element_id
694 and
695 ppe.project_id = ppev.project_id
696 and
697 ppev.project_id = p_project_id
698 and
699 ppev.element_version_id = por_mapping.object_id_to1
700 and
701 por_mapping.object_id_from1 = p_mapped_wkp_task_version_id
702 and
703 por_mapping.relationship_type = 'M';
704
705 l_mapped_fin_task_name VARCHAR2(240);
706
707 BEGIN
708 l_mapped_fin_task_name := NULL;
709 OPEN C_get_mapped_fin_task_name;
710 FETCH C_get_mapped_fin_task_name INTO l_mapped_fin_task_name;
711 CLOSE C_get_mapped_fin_task_name;
712
713 return l_mapped_fin_task_name;
714
715 EXCEPTION
716 WHEN OTHERS THEN
717 l_mapped_fin_task_name := NULL;
718 return l_mapped_fin_task_name;
719 END GET_MAPPED_FIN_TASK_NAME_AMG;
720
721 END PA_PROJ_STRUC_MAPPING_UTILS;
[Home] [Help]