1 PACKAGE BODY pa_assignment_utils AS
2 -- $Header: PARAUTLB.pls 120.10.12010000.2 2008/09/23 17:22:56 jcgeorge ship $
3
4 --
5 -- PROCEDURE
6 -- Check_Status_Is_In_use
7 -- PURPOSE
8 -- This procedure Checks whether a given status is used in
9 -- Assignments and assignment schedules
10 -- HISTORY
11 -- 16-JUL-2000 R. Krishnamurthy Created
12 --
13 li_message_level NUMBER := 1;
14
15 PROCEDURE check_status_is_in_use
16 ( p_status_code IN pa_project_statuses.project_status_code%TYPE
17 ,x_in_use_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
19 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
20 BEGIN
21 pa_debug.init_err_stack ('pa_assignment_utils.check_status_is_in_use');
22 x_error_message_code := NULL;
23 x_in_use_flag := 'N';
24 x_return_status := FND_API.G_RET_STS_SUCCESS;
25
26 --change due to performance reason
27 /*
28 SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
29 INTO x_in_use_flag, x_error_message_code
30 FROM dual
31 WHERE EXISTS
32 (SELECT 'x' FROM pa_project_assignments
33 WHERE status_code = p_status_code)
34 OR EXISTS
35 (SELECT 'x' FROM pa_schedules
36 WHERE status_code = p_status_code)
37 OR EXISTS
38 (SELECT 'x' FROM pa_project_assignments
39 WHERE apprvl_status_code = p_status_code);
40
41 SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
42 INTO x_in_use_flag, x_error_message_code
43 FROM pa_project_assignments ppa,
44 pa_schedules ps
45 WHERE
46 ( ( ppa.status_code = p_status_code)
47 OR ( ps.status_code = p_status_code)
48 OR ( ppa.apprvl_status_code = p_status_code))
49 AND rownum = 1;
50 */
51
52 BEGIN
53 SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
54 INTO x_in_use_flag, x_error_message_code
55 FROM pa_project_assignments
56 WHERE status_code = p_status_code
57 AND rownum = 1;
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 null;
61 END;
62
63 BEGIN
64 IF x_in_use_flag = 'N' THEN
65 SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
66 INTO x_in_use_flag, x_error_message_code
67 FROM pa_project_assignments
68 WHERE apprvl_status_code = p_status_code
69 AND rownum = 1;
70 END IF;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 null;
74 END;
75
76 BEGIN
77 IF x_in_use_flag = 'N' THEN
78 SELECT 'Y', 'PA_STATUS_EXIST_IN_ASGMT'
79 INTO x_in_use_flag, x_error_message_code
80 FROM pa_schedules
81 WHERE status_code = p_status_code
82 AND rownum = 1;
83 END IF;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 null;
87 END;
88
89 pa_debug.reset_err_stack;
90 EXCEPTION
91 WHEN NO_DATA_FOUND THEN
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93 x_in_use_flag := 'N';
94
95 -- 4537865 : RESET Out params to proper values
96 -- Make x_error_message_code as NULL as in this case we consider return status as 'S'
97
98 x_error_message_code := NULL ;
99
100 -- 4537865 : End
101
102 pa_debug.reset_err_stack;
103 WHEN OTHERS THEN
104 -- 4537865 : RESET Out params to proper values
105 x_error_message_code := SQLCODE ;
106 x_in_use_flag := NULL ;
107 -- 4537865 : End
108
109 fnd_msg_pub.add_exc_msg
110 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
111 p_procedure_name => pa_debug.g_err_stack );
112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
113 RAISE;
114 END check_status_is_in_use;
115
116 --
117 -- PROCEDURE
118 -- Validate_Asgmt_Competency
119 -- PURPOSE
120 -- This procedure validates the competencies for an assignment
121 -- HISTORY
122 -- 17-JUL-2000 R. Krishnamurthy Created
123 --
124 -- 27-Jul-2001 Vijay Ranganathan Changed API Validate_Asgmt_Competency
125 -- to get project business group
126 -- BUG: 1904822
127 PROCEDURE Validate_Asgmt_Competency
128 ( p_project_id IN pa_projects_all.project_id%TYPE
129 ,p_assignment_id IN pa_project_assignments.assignment_id%TYPE
130 ,p_competence_id IN per_competences.competence_id%TYPE
131 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
132 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
133 l_comp_bg_id NUMBER := 0;
134 l_proj_bg_id NUMBER := 0;
135 CURSOR l_bg_csr IS
136 SELECT business_group_id FROM per_competences
137 WHERE competence_id = p_competence_id;
138
139 BEGIN
140 pa_debug.init_err_stack ('pa_assignment_utils.Validate_Asgmt_Competency');
141 OPEN l_bg_csr;
142 FETCH l_bg_csr INTO l_comp_bg_id;
143 IF l_bg_csr%NOTFOUND THEN
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 x_error_message_code := 'PA_COMPETENCY_INVALID_AMBIGOUS';
146 CLOSE l_bg_csr;
147 pa_debug.reset_err_stack;
148 RETURN;
149 ELSE
150 CLOSE l_bg_csr;
151 END IF;
152
153 --BUG: 1904822 Get project business group id instead of from pa_implementations
154 IF ( l_comp_bg_id IS NOT NULL
155 AND l_comp_bg_id <> pa_project_utils2.Get_project_business_group ( p_project_id)) THEN
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 x_error_message_code := 'PA_ASGMT_COMPETENCY_INVALID';
158 pa_debug.reset_err_stack;
159 RETURN;
160 END IF;
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162 pa_debug.reset_err_stack;
163 EXCEPTION
164 WHEN OTHERS THEN
165 -- 4537865 : RESET Out params to proper values
166 x_error_message_code := SQLCODE ;
167 -- 4537865 : End
168
169 fnd_msg_pub.add_exc_msg
170 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
171 p_procedure_name => pa_debug.g_err_stack );
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
173 RAISE;
174 END Validate_Asgmt_Competency;
175
176 -- PROCEDURE
177 -- Get_Def_Asgmt_Statuses
178 -- This procedure returns the default assignment statuses
179 -- 17-JUL-2000 R. Krishnamurthy Created
180
181 PROCEDURE Get_Def_Asgmt_Statuses
182 (x_starting_oa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
183 x_starting_sa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
184 x_starting_fa_status OUT NOCOPY pa_project_statuses.project_status_code%TYPE, --File.Sql.39 bug 4440895
185 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
186 x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
187
188 BEGIN
189 pa_debug.init_err_stack ('pa_assignment_utils.Get_Def_Asgmt_Statuses');
190 x_starting_oa_status := fnd_profile.value ('DEF_OA_STARTING_STATUS');
191 x_starting_sa_status := fnd_profile.value ('DEF_SA_STARTING_STATUS');
192 x_starting_fa_status := fnd_profile.value ('DEF_FA_STATUS');
193 -- While it is ok for the other two statuses to be not defined,
194 -- an installation must always have a default filled status defined
195 -- in order for the assignments to be marked as filled whenever
196 -- an open assignment is filled with a resource
197
198 IF x_starting_fa_status IS NULL THEN
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 x_error_message_code := 'PA_NO_DEF_FA_STATUS';
201 ELSE
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203 END IF;
204 pa_debug.reset_err_stack;
205 EXCEPTION
206 WHEN OTHERS THEN
207
208 -- 4537865 : RESET OUT params to proper values
209 x_starting_oa_status := NULL ;
210 x_starting_sa_status := NULL ;
211 x_starting_fa_status := NULL ;
212 x_error_message_code := SQLCODE;
213 -- 4537865 :ENd
214
215 fnd_msg_pub.add_exc_msg
216 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
217 p_procedure_name => pa_debug.g_err_stack );
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
219 RAISE;
220 END Get_Def_Asgmt_Statuses;
221
222 -- FUNCTION
223 -- Get_project_id
224 -- This function returns the project id for a given assignment
225
226 -- 17-JUL-2000 R. Krishnamurthy Created
227 FUNCTION Get_Project_Id (p_assignment_id IN NUMBER) RETURN NUMBER IS
228 l_project_id NUMBER ;
229 BEGIN
230 pa_debug.init_err_stack ('pa_assignment_utils.Get_Project_Id');
231 SELECT project_id
232 INTO l_project_id
233 FROM pa_project_assignments
234 WHERE assignment_id = p_assignment_id ;
235 pa_debug.reset_err_stack;
236 RETURN l_project_id ;
237 EXCEPTION
238 WHEN NO_DATA_FOUND THEN
239 RETURN NULL;
240 WHEN OTHERS THEN
241 fnd_msg_pub.add_exc_msg
242 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
243 p_procedure_name => pa_debug.g_err_stack );
244 RAISE;
245 END Get_Project_Id;
246
247 -- This function returns whether a given assignment status is
248 -- a confirmed status or not
249 -- 18-JUL-2000 R. Krishnamurthy Created
250 FUNCTION Is_Confirmed_Status
251 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
252 p_status_type IN pa_project_statuses.status_type%TYPE )
253 return VARCHAR2 IS
254 BEGIN
255 RETURN check_input_system_status (
256 p_status_code,
257 p_status_type,
258 'STAFFED_ASGMT_CONF');
259 EXCEPTION
260 WHEN OTHERS THEN
261 RAISE;
262 END Is_Confirmed_Status ;
263
264 -- This function returns whether a given assignment status is
265 -- a Provisional status or not
266 -- 18-JUL-2000 R. Krishnamurthy Created
267 FUNCTION Is_Provisional_Status
268 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
269 p_status_type IN pa_project_statuses.status_type%TYPE )
270 return VARCHAR2 IS
271 BEGIN
272 RETURN check_input_system_status (
273 p_status_code,
274 p_status_type,
275 'STAFFED_ASGMT_PROV');
276 EXCEPTION
277 WHEN OTHERS THEN
278 RAISE;
279 END Is_provisional_status;
280
281 -- This function returns whether a given assignment status is
282 -- a Filled status or not
283 -- 18-JUL-2000 R. Krishnamurthy Created
284 FUNCTION Is_Asgmt_Filled
285 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
286 p_status_type IN pa_project_statuses.status_type%TYPE )
287 return VARCHAR2 IS
288 BEGIN
289 RETURN check_input_system_status (
290 p_status_code,
291 p_status_type,
292 'OPEN_ASGMT_FILLED');
293 EXCEPTION
294 WHEN OTHERS THEN
295 RAISE;
296 END Is_Asgmt_Filled ;
297
298 -- This function returns whether a given assignment status is
299 -- an Open status or not
300 -- 18-JUL-2000 R. Krishnamurthy Created
301 FUNCTION Is_Asgmt_In_Open_Status
302 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
303 p_status_type IN pa_project_statuses.status_type%TYPE )
304 return VARCHAR2 IS
305 BEGIN
306 RETURN check_input_system_status (
307 p_status_code,
308 p_status_type,
309 'OPEN_ASGMT');
310 EXCEPTION
311 WHEN OTHERS THEN
312 RAISE;
313 END Is_Asgmt_In_Open_Status ;
314
315 -- This function returns whether a given open assignment status is
316 -- a cancelled status or not
317 -- 18-JUL-2000 R. Krishnamurthy Created
318 FUNCTION Is_Open_Asgmt_Cancelled
319 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
320 p_status_type IN pa_project_statuses.status_type%TYPE )
321 return VARCHAR2 IS
322 BEGIN
323 RETURN check_input_system_status (
324 p_status_code,
325 p_status_type,
326 'OPEN_ASGMT_CANCEL');
327 EXCEPTION
328 WHEN OTHERS THEN
329 RAISE;
330 END Is_Open_Asgmt_Cancelled ;
331
332 -- This function returns whether a given staffed assignment status is
333 -- a cancelled status or not
334 -- 18-JUL-2000 R. Krishnamurthy Created
335 FUNCTION Is_Staffed_Asgmt_Cancelled
336 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
337 p_status_type IN pa_project_statuses.status_type%TYPE )
338 return VARCHAR2 IS
339 BEGIN
340 RETURN check_input_system_status (
341 p_status_code,
342 p_status_type,
343 'STAFFED_ASGMT_CANCEL');
344 EXCEPTION
345 WHEN OTHERS THEN
346 RAISE;
347 END Is_Staffed_Asgmt_Cancelled ;
348
349 -- This function returns whether a given status is
350 -- has the specified system status
351 -- 18-JUL-2000 R. Krishnamurthy Created
352 FUNCTION Check_input_system_status
353 (p_status_code IN pa_project_statuses.project_status_code%TYPE ,
354 p_status_type IN pa_project_statuses.status_type%TYPE ,
355 p_in_system_status_code IN pa_project_statuses.project_system_status_code%TYPE)
356 RETURN VARCHAR2 IS
357 l_ret_val VARCHAR2(1);
358 BEGIN
359 SELECT DECODE (project_system_status_code,
360 p_in_system_status_code,'Y','N')
361 INTO l_ret_val
362 FROM pa_project_statuses
363 WHERE project_status_code = p_status_code
364 AND status_type = p_status_type;
365 RETURN l_ret_val;
366 EXCEPTION
367 WHEN OTHERS THEN
368 RAISE;
369 END check_input_system_status ;
370
371 PROCEDURE Check_proj_Assignments_Exist
372 (p_project_id IN NUMBER
373 ,x_assignments_exist_flag OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
374 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
375 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
376 BEGIN
377 pa_debug.init_err_stack ('pa_assignment_utils.Check_proj_Assignments_Exist');
378 x_assignments_exist_flag := 'N';
379 x_error_message_code := NULL;
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 --Change due to Performance Reason
383 /*
384 SELECT 'Y', 'PA_PROJ_ASSIGNMENTS_EXIST'
385 INTO x_assignments_exist_flag, x_error_message_code
386 FROM dual
387 WHERE EXISTS
388 (SELECT 'x' FROM pa_project_assignments
389 WHERE project_id = p_project_id);
390 */
391 SELECT 'Y', 'PA_PROJ_ASSIGNMENTS_EXIST'
392 INTO x_assignments_exist_flag, x_error_message_code
393 FROM pa_project_assignments
394 WHERE project_id = p_project_id
395 AND rownum=1;
396
397 pa_debug.reset_err_stack;
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 x_return_status := FND_API.G_RET_STS_SUCCESS;
401 x_assignments_exist_flag := 'N';
402 WHEN OTHERS THEN
403 -- 4537865 : Start : RESET OUT PARAMS to proper values
404 x_assignments_exist_flag := NULL ;
405 x_error_message_code := SQLCODE ;
406 -- 4537865 : End
407
408 fnd_msg_pub.add_exc_msg
409 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
410 p_procedure_name => pa_debug.g_err_stack );
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
412 RAISE;
413 END check_proj_assignments_exist;
414
418 ,x_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
415 PROCEDURE Check_Assignment_Number_Or_Id( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
416 ,p_assignment_number IN pa_project_assignments.assignment_number%TYPE
417 ,p_check_id_flag IN VARCHAR2 := 'A'
419 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
420 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
421 CURSOR c_ids IS
422 SELECT assignment_id
423 FROM pa_project_assignments
424 WHERE assignment_number = p_assignment_number;
425
426 l_id_found_flag VARCHAR2(1);
427 l_current_id pa_project_assignments.assignment_id%TYPE;
428 l_num_ids NUMBER;
429 BEGIN
430 pa_debug.init_err_stack ('pa_assignment_utils.check_assignment_number_or_id');
431 IF p_assignment_id IS NOT NULL AND p_assignment_id <> FND_API.G_MISS_NUM THEN
432 IF p_check_id_flag = 'Y' THEN
433 SELECT assignment_id
434 INTO x_assignment_id
435 FROM pa_project_assignments
436 WHERE assignment_number = p_assignment_number;
437 ELSIF p_check_id_flag = 'N' THEN
438 x_assignment_id := p_assignment_id;
439 ELSIF p_check_id_flag = 'A' THEN
440 IF (p_assignment_number IS NULL) THEN
441 -- Return a null ID since the name is null.
442 x_assignment_id := NULL;
443 ELSE
444 -- Find the ID which matches the Name passed
445 OPEN c_ids;
446 LOOP
447 FETCH c_ids INTO l_current_id;
448 EXIT WHEN c_ids%NOTFOUND;
449 IF (l_current_id = p_assignment_id) THEN
450 l_id_found_flag := 'Y';
451 x_assignment_id := p_assignment_id;
452 END IF;
453 END LOOP;
454 l_num_ids := c_ids%ROWCOUNT;
455 CLOSE c_ids;
456
457 IF (l_num_ids = 0) THEN
458 -- No IDs for name
459 RAISE NO_DATA_FOUND;
460 ELSIF (l_num_ids = 1) THEN
461 -- Since there is only one ID for the name use it.
462 x_assignment_id := l_current_id;
463 ELSIF (l_id_found_flag = 'N') THEN
464 -- More than one ID for the name and none of the IDs matched
465 -- the ID passed in.
466 RAISE TOO_MANY_ROWS;
467 END IF;
468 END IF;
469 END IF;
470 ELSE
471 IF (p_assignment_number IS NOT NULL) THEN
472 SELECT assignment_id
473 INTO x_assignment_id
474 FROM pa_project_assignments
475 WHERE assignment_number = p_assignment_number;
476 ELSE
477 x_assignment_id := NULL;
478 END IF;
479 END IF;
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481 pa_debug.reset_err_stack;
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 x_error_message_code := 'PA_ASGN_NUMBER_INV_AMBIGOUS';
486 x_assignment_id := NULL;
487 WHEN TOO_MANY_ROWS THEN
488 x_assignment_id := NULL;
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 x_error_message_code := 'PA_ASGN_NUMBER_INV_AMBIGOUS';
491 WHEN OTHERS THEN
492 -- 4537865 : Start : RESET OUT PARAMS to proper values
493 x_error_message_code := SQLCODE;
494 -- 4537865 : End
495
496 fnd_msg_pub.add_exc_msg
497 (p_pkg_name => 'PA_ASSIGNMENT_UTILS',
498 p_procedure_name => pa_debug.g_err_stack );
499 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
500 x_assignment_id := NULL;
501 RAISE;
502 END Check_Assignment_Number_Or_Id;
503
504 PROCEDURE Check_STF_PriorityName_Or_Code (p_staffing_priority_code IN pa_project_assignments.staffing_priority_code%TYPE
505 ,p_staffing_priority_name IN pa_lookups.meaning%TYPE
506 ,p_check_id_flag IN VARCHAR2
507 ,x_staffing_priority_code OUT NOCOPY pa_project_assignments.staffing_priority_code%TYPE --File.Sql.39 bug 4440895
508 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
509 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
510 BEGIN
511 pa_debug.init_err_stack ('pa_assignment_utils.Check_STF_PriorityName_Or_Code');
512
513 IF p_staffing_priority_code IS NOT NULL AND p_staffing_priority_code<>FND_API.G_MISS_CHAR THEN
514 IF p_check_id_flag = 'Y' THEN
515 SELECT lookup_code
516 INTO x_staffing_priority_code
517 FROM pa_lookups
518 WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
519 AND lookup_code = p_staffing_priority_code;
520 ELSE
521 x_staffing_priority_code := p_staffing_priority_code;
522
523 END IF;
527 FROM pa_lookups
524 ELSE
525 SELECT lookup_code
526 INTO x_staffing_priority_code
528 WHERE lookup_type = 'STAFFING_PRIORITY_CODE'
529 AND meaning = p_staffing_priority_name;
530 END IF;
531
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533
534 EXCEPTION
535 WHEN NO_DATA_FOUND THEN
536 x_return_status := FND_API.G_RET_STS_ERROR;
537 x_error_message_code := 'PA_STF_PRIORITY_INVALID';
538 WHEN TOO_MANY_ROWS THEN
539 x_return_status := FND_API.G_RET_STS_ERROR;
540 x_error_message_code := 'PA_STF_PRIORITY_INVALID';
541 WHEN OTHERS THEN
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
543 -- 4537865 : Start
544 x_staffing_priority_code := p_staffing_priority_code ;
545 x_error_message_code := SQLCODE ;
546 -- 4537865 : End
547 END Check_STF_PriorityName_Or_Code;
548
549 --
550 --Possible values for return: 'Roll On', 'Roll Off', 'Pending Approval'
551 --
552 --Use the dates passed in to decided if the assignment is rolling on, or rolling off or pending approval.
553
554 FUNCTION get_role_activity_text (p_assignment_id IN NUMBER,
555 p_start_date IN DATE,
556 p_end_date IN DATE,
557 p_apprvl_status_code IN VARCHAR2,
558 p_num_of_weeks IN NUMBER) RETURN VARCHAR2
559 IS
560
561 l_lookup_code pa_lookups.lookup_code%TYPE;
562 l_meaning pa_lookups.meaning%TYPE;
563 l_today_date pa_project_assignments.start_date%TYPE;
564
565 CURSOR get_meaning IS
566 SELECT meaning
567 FROM pa_lookups
568 WHERE lookup_code = l_lookup_code
569 AND lookup_type = 'TEAM_ROLE_ACTIVITY_TYPE';
570
571 BEGIN
572
573 l_today_date := TRUNC (sysdate);
574
575 IF p_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted THEN
576 l_lookup_code := 'PENDING';
577 ELSIF ( l_today_date <= p_start_date) AND (l_today_date >= (p_start_date - p_num_of_weeks*7)) THEN
578 l_lookup_code := 'ROLL_ON';
579 ELSIF (l_today_date <= p_end_date) AND (l_today_date >= (p_end_date - p_num_of_weeks*7)) THEN
580 l_lookup_code := 'ROLL_OFF';
581 END IF;
582
583 IF l_lookup_code IS NOT NULL THEN
584 OPEN get_meaning;
585 FETCH get_meaning INTO l_meaning;
586 CLOSE get_meaning;
587 END IF;
588
589 RETURN l_meaning;
590
591
592 END get_role_activity_text;
593
594
595 --
596 --Possible values for return: start_date, end_date
597 --
598 --IF assignment rolling on, then return start_date
599 --IF assignment rolling off, then return end_date
600 --IF pending approval, then return start_date
601
602 FUNCTION get_role_activity_date (p_assignment_id IN NUMBER,
603 p_start_date IN DATE,
604 p_end_date IN DATE,
605 p_apprvl_status_code IN VARCHAR2,
606 p_num_of_weeks IN NUMBER) RETURN DATE
607 IS
608
609 l_date DATE;
610 l_today_date pa_project_assignments.start_date%TYPE;
611
612 BEGIN
613
614 l_today_date := TRUNC(sysdate);
615
616 IF p_apprvl_status_code = PA_ASSIGNMENT_APPROVAL_PUB.g_submitted THEN
617 l_date := p_start_date;
618 ELSIF (l_today_date <= p_start_date) AND (l_today_date >= (p_start_date - p_num_of_weeks*7)) THEN
619 l_date := p_start_date;
620 ELSIF (l_today_date <= p_end_date) AND (l_today_date >= (p_end_date - p_num_of_weeks*7)) THEN
621 l_date := p_end_date;
622 END IF;
623
624 RETURN l_date;
625
626 END get_role_activity_date;
627
628 PROCEDURE Add_Message(p_app_short_name IN VARCHAR2,
629 p_msg_name IN VARCHAR2,
630 p_token1 IN VARCHAR2 DEFAULT NULL,
631 p_value1 IN VARCHAR2 DEFAULT NULL)
632 IS
633
634 l_message_text FND_NEW_MESSAGES.message_text%TYPE;
635
636 /* 2708879 - Added two conditions for application id and language code for the cursor get_message below */
637 CURSOR get_message IS
638 SELECT message_text
639 FROM fnd_new_messages
640 WHERE message_name = p_msg_name
641 and application_id = 275
642 and language_code = userenv('LANG');
643
644 CURSOR get_team_template_name IS
645 SELECT team_template_name
646 FROM pa_team_templates
647 WHERE team_template_id = g_team_template_id;
648
649 BEGIN
650
651 --when applying a team template to a project, multiple team templates containing
652 --multiple assignments will be created.
653 --if the team_template_name and/or team_role_name global variables are set and there
654 --are any validation errors, then the team_template_name and assignment_name will
655 --be prepended to the error message.
656 --If the globals are NULL then the message will appear normally (nothing prepended).
657 IF pa_assignment_utils.g_team_template_name_token IS NULL AND pa_assignment_utils.g_team_role_name_token IS NULL THEN
658
659 IF p_token1 IS NULL THEN
660 --message text appear with no prepended values.
664 --message text appear with no prepended values.
661 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
662 p_msg_name => p_msg_name);
663 ELSE
665 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
666 p_msg_name => p_msg_name,
667 p_token1 => p_token1,
668 p_value1 => p_value1);
669 END IF;
670
671 ELSIF pa_assignment_utils.g_team_template_id IS NOT NULL THEN
672
673 --get the message text
674 OPEN get_message;
675 FETCH get_message INTO l_message_text;
676 CLOSE get_message;
677
678 --if the team_template_id token is set but the name is not then get the name.
679 --the apply_team_template API does not have the team_template_name but does have the
680 --id, so in this way we only get the name if required.
681 IF g_team_template_name_token IS NULL THEN
682 OPEN get_team_template_name;
683 FETCH get_team_template_name INTO g_team_template_name_token;
684 CLOSE get_team_template_name;
685 END IF;
686
687 --if the team template name token value is set and
688 --the team role name token value is not set then this a team template level
689 --error, so the message will be displayed in the format:
690 --"Team Template Name: Error Message"
691 --both the team template name and error message are tokens.
692 IF pa_assignment_utils.g_team_role_name_token IS NULL THEN
693
694 IF p_token1 IS NULL THEN
695 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
696 p_msg_name => 'PA_TEAM_TEMP_ERR_MSG',
697 p_token1 => 'TEAM_TEMPLATE_NAME',
698 p_value1 => pa_assignment_utils.g_team_template_name_token,
699 p_token2 => 'ERROR_MESSAGE',
700 p_value2 => l_message_text);
701 ELSE
702 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
703 p_msg_name => 'PA_TEAM_TEMP_ERR_MSG',
704 p_token1 => 'TEAM_TEMPLATE_NAME',
705 p_value1 => pa_assignment_utils.g_team_template_name_token,
706 p_token2 => 'ERROR_MESSAGE',
707 p_value2 => l_message_text,
708 p_token3 => p_token1,
709 p_value3 => p_value1);
710 END IF;
711
712 --if the team template name token value is set and
713 --the team role name token value is set then this a requirement level which occurs when
714 --the team template is being applied to a project.
715 --The message will be displayed in the format:
716 --"Team Template Name: Team Role Name: Error Message"
717 --The team template name, team role name, and error message are tokens.
718 ELSIF pa_assignment_utils.g_team_role_name_token IS NOT NULL THEN
719
720 IF pa_assignment_utils.g_team_role_name_token IS NULL THEN
721 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
722 p_msg_name => 'PA_TEAM_TEMP_ROLE_ERR_MSG',
723 p_token1 => 'TEAM_TEMPLATE_NAME',
724 p_value1 => pa_assignment_utils.g_team_template_name_token,
725 p_token2 => 'TEAM_ROLE_NAME',
726 p_value2 => pa_assignment_utils.g_team_role_name_token,
727 p_token3 => 'ERROR_MESSAGE',
728 p_value3 => l_message_text);
729 ELSE
730 PA_UTILS.Add_Message(p_app_short_name => p_app_short_name,
731 p_msg_name => 'PA_TEAM_TEMP_ROLE_ERR_MSG',
732 p_token1 => 'TEAM_TEMPLATE_NAME',
733 p_value1 => pa_assignment_utils.g_team_template_name_token,
734 p_token2 => 'TEAM_ROLE_NAME',
735 p_value2 => pa_assignment_utils.g_team_role_name_token,
736 p_token3 => 'ERROR_MESSAGE',
737 p_value3 => l_message_text,
738 p_token4 => p_token1,
739 p_value4 => p_value1);
740 END IF;
741
742 END IF;
743
744 END IF;
745
746 END Add_Message;
747
748
749 FUNCTION is_asgmt_allow_stus_ctl_check(p_asgmt_status_code IN pa_project_statuses.project_status_code%TYPE,
750 p_project_id IN pa_projects_all.project_id%TYPE,
751 p_add_message IN VARCHAR2)
752 RETURN VARCHAR2 IS
753
754 CURSOR get_status_info IS
755 SELECT proj.project_status_code, ps.project_status_name, ps2.project_system_status_code, ps2.project_status_name
756 FROM pa_projects_all proj,
757 pa_project_statuses ps,
758 pa_project_statuses ps2
759 WHERE project_id = p_project_id
760 AND proj.project_status_code = ps.project_status_code
761 AND ps2.project_status_code = p_asgmt_status_code;
762
763 l_project_status_code pa_project_statuses.project_status_code%TYPE;
767 l_allow_asgmt VARCHAR2(1);
764 l_project_status_name pa_project_statuses.project_status_name%TYPE;
765 l_asgmt_system_status_code pa_project_statuses.project_system_status_code%TYPE;
766 l_asgmt_status_name pa_project_statuses.project_status_name%TYPE;
768 l_status_control_code pa_project_status_controls.action_code%TYPE;
769
770 BEGIN
771
772 OPEN get_status_info;
773 FETCH get_status_info INTO l_project_status_code
774 , l_project_status_name
775 , l_asgmt_system_status_code
776 , l_asgmt_status_name;
777 CLOSE get_status_info;
778
779 IF l_asgmt_system_status_code = 'STAFFED_ASGMT_CONF' THEN
780
781 l_status_control_code := 'PROJ_ASSIGN_RESOURCES';
782
783 ELSIF l_asgmt_system_status_code = 'STAFFED_ASGMT_PROV' THEN
784
785 l_status_control_code := 'PROJ_PROVISIONAL_ASSIGN';
786
787 ELSIF l_asgmt_system_status_code = 'STAFFED_ASGMT_CANCEL' THEN
788
789 RETURN 'Y';
790
791 END IF;
792
793 l_allow_asgmt := PA_PROJECT_UTILS.Check_prj_stus_action_allowed
794 ( x_project_status_code => l_project_status_code
795 ,x_action_code => l_status_control_code);
796
797 IF p_add_message = 'Y' AND l_allow_asgmt = 'N' THEN
798
799 PA_UTILS.Add_Message( p_app_short_name => 'PA'
800 ,p_msg_name => 'PA_ASGN_STUS_NOT_FOR_PROJ_STUS'
801 ,p_token1 => 'PROJ_STATUS'
802 ,p_value1 => l_project_status_name
803 ,p_token2 => 'ASGN_STATUS'
804 ,p_value2 => l_asgmt_status_name);
805
806 END IF;
807
808 RETURN l_allow_asgmt;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 --
813 -- Set the excetption Message and the stack
814 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_ASSIGNMENT_UTILS.is_asgmt_allow_stus_ctl_check'
815 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
816 --
817 RAISE; -- This is optional depending on the needs
818
819 END is_asgmt_allow_stus_ctl_check;
820
821
822 --
823 -- PROCEDURE
824 -- Get_Person_Asgmt
825 -- PURPOSE
826 -- This procedure returns the assignment with the given
827 -- person, in the given project and with the given date
828 -- PARAMETERS
829 -- p_person_id IN - mandatory
830 -- p_project_id IN - mandatory
831 -- p_ei_date IN - mandatory
832 -- x_assignment_name IN OUT - may not be passed in
833 -- x_assignment_id OUT - NULL if not found or multiple found
834 -- x_return_status OUT - S if single assignment found
835 -- E if not found or multiple found
836 -- U otherwise
837 -- x_error_message_code OUT
838 --
839 PROCEDURE Get_Person_Asgmt
840 ( p_person_id IN pa_resources_denorm.person_id%TYPE
841 ,p_project_id IN pa_project_assignments.project_id%TYPE
842 ,p_ei_date IN DATE
843 ,x_assignment_name IN OUT NOCOPY pa_project_assignments.assignment_name%TYPE --File.Sql.39 bug 4440895
844 ,x_assignment_id OUT NOCOPY pa_project_assignments.assignment_id%TYPE --File.Sql.39 bug 4440895
845 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
846 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
847
848 l_row_count NUMBER := 0;
849 l_found boolean := FALSE;
850 l_assignment_id pa_project_assignments.assignment_id%TYPE;
851 l_assignment_name pa_project_assignments.assignment_name%TYPE;
852
853 CURSOR get_assignment_with_name IS
854 SELECT assignment_id, assignment_name
855 FROM pa_proj_assignments_actuals_v
856 WHERE project_id = p_project_id
857 AND person_id = p_person_id
858 AND p_ei_date between start_date and end_date
859 AND assignment_name = x_assignment_name
860 ORDER BY assignment_start_date DESC;
861
862 CURSOR get_assignment_without_name IS
863 SELECT assignment_id, assignment_name
864 FROM pa_proj_assignments_actuals_v
865 WHERE project_id = p_project_id
866 AND person_id = p_person_id
867 AND p_ei_date between start_date and end_date
868 ORDER BY assignment_start_date DESC;
869
870 BEGIN
871
872 x_return_status := FND_API.G_RET_STS_SUCCESS;
873 --dbms_output.put_line ('Assignment Name is ' || x_assignment_name);
874
875 IF x_assignment_name IS NOT NULL AND x_assignment_name <> FND_API.G_MISS_CHAR THEN
876
877 IF p_person_id = g_person_id_w_name AND p_project_id = g_project_id_w_name AND p_ei_date = g_ei_date_w_name AND x_assignment_name = g_in_asgmt_name THEN
878 l_found := TRUE;
879 --x_assignment_name := x_assignment_name;
880 x_assignment_id := g_assignment_id_w_name;
881 --dbms_output.put_line ('Flow 1. Reading cache value');
882 ELSE
883 l_found := FALSE;
884 OPEN get_assignment_with_name;
885 LOOP
886 FETCH get_assignment_with_name INTO l_assignment_id, l_assignment_name;
887 EXIT WHEN get_assignment_with_name%NOTFOUND;
888 END LOOP;
892 IF l_row_count <> 0 THEN
889 l_row_count := get_assignment_with_name%ROWCOUNT;
890 CLOSE get_assignment_with_name;
891
893 g_person_id_w_name := p_person_id;
894 g_project_id_w_name := p_project_id;
895 g_ei_date_w_name := p_ei_date;
896 g_in_asgmt_name := l_assignment_name;
897 g_assignment_id_w_name := l_assignment_id;
898 END IF;
899
900 END IF;
901
902 ELSE
903
904 IF p_person_id = g_person_id_wo_name AND p_project_id = g_project_id_wo_name AND p_ei_date = g_ei_date_wo_name THEN
905 l_found := TRUE;
906 x_assignment_name := g_out_asgmt_name;
907 x_assignment_id := g_assignment_id_wo_name;
908 --dbms_output.put_line ('Flow 2. Reading cache value');
909 ELSE
910 l_found := FALSE;
911 OPEN get_assignment_without_name;
912 LOOP
913 FETCH get_assignment_without_name INTO l_assignment_id, l_assignment_name;
914 EXIT WHEN get_assignment_without_name%NOTFOUND;
915 END LOOP;
916 l_row_count := get_assignment_without_name%ROWCOUNT;
917 CLOSE get_assignment_without_name;
918
919 IF l_row_count <> 0 THEN
920 g_person_id_wo_name := p_person_id;
921 g_project_id_wo_name := p_project_id;
922 g_ei_date_wo_name := p_ei_date;
923 g_assignment_id_wo_name := l_assignment_id;
924 g_out_asgmt_name := l_assignment_name;
925 END IF;
926 END IF;
927
928 END IF;
929
930 IF NOT l_found THEN
931 IF l_row_count = 0 THEN
932 g_person_id_w_name := NULL;
933 g_project_id_w_name := NULL;
934 g_ei_date_w_name := NULL;
935 g_person_id_wo_name := NULL;
936 g_project_id_wo_name := NULL;
937 g_ei_date_wo_name := NULL;
938
939 g_in_asgmt_name := NULL;
940 g_assignment_id_w_name := NULL;
941 g_assignment_id_wo_name := NULL;
942
943 RAISE no_data_found;
944 ELSE
945 x_assignment_id := l_assignment_id;
946 x_assignment_name := l_assignment_name;
947 END IF;
948 END IF;
949
950 x_return_status := FND_API.G_RET_STS_SUCCESS;
951
952 EXCEPTION
953
954 WHEN NO_DATA_FOUND THEN
955 x_assignment_id := NULL;
956 x_assignment_name := NULL;
957 x_return_status := FND_API.G_RET_STS_ERROR;
958 x_error_message_code := 'PA_NO_ASSIGNMENT';
959 WHEN OTHERS THEN
960 x_assignment_id := NULL;
961 x_assignment_name := NULL;
962 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
963 RAISE;
964 END Get_Person_Asgmt;
965
966 ------------------------------------------------------------------------------------
967 --
968 -- FUNCTION : Get_Assignment_Measures
969 -- DESCRIPTION: This function gets the capacity hours of the resource who is working
970 -- on the project. Also sets the global variables
971 -- g_prvisional_hours - provisional hours of the assignment
972 -- g_confirmed_hours - confirmed hours of the assignment
973 -- NOTE: This Function is solely used in discoverer report TR2
974 -- Workbook: Team Role Details Worksheet: Assignment Details
975 ------------------------------------------------------------------------------------
976 FUNCTION Get_Assignment_Measures
977 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
978 ,p_resource_id IN pa_project_assignments.resource_id%TYPE
979 ,p_asgn_effort IN pa_project_assignments.assignment_effort%TYPE
980 ,p_asgn_start_date IN pa_project_assignments.start_date%TYPE
981 ,p_asgn_end_date IN pa_project_assignments.end_date%TYPE
982 ,p_multiple_status_flag IN pa_project_assignments.multiple_status_flag%TYPE)
983 RETURN NUMBER
984 IS
985 l_res_capacity_hrs NUMBER;
986 l_provisional_flag VARCHAR2(1);
987 BEGIN
988 SELECT sum(capacity_quantity)
989 INTO l_res_capacity_hrs
990 FROM pa_forecast_items
991 WHERE forecast_item_type = 'U'
992 AND delete_flag = 'N'
993 AND resource_id = p_resource_id
994 AND item_date BETWEEN p_asgn_start_date AND p_asgn_end_date;
995
996 IF l_res_capacity_hrs IS NULL THEN
997 l_res_capacity_hrs := 0;
998 END IF;
999
1000 IF (p_multiple_status_flag = 'Y') THEN
1001 SELECT sum(item_quantity)
1002 INTO g_provisional_hours
1003 FROM pa_forecast_items
1004 WHERE forecast_item_type = 'A'
1005 AND delete_flag = 'N'
1006 AND provisional_flag = 'Y'
1007 AND assignment_id = p_assignment_id;
1008 ELSE
1009 SELECT provisional_flag
1010 INTO l_provisional_flag
1011 FROM pa_forecast_items
1012 WHERE forecast_item_type = 'A'
1013 AND delete_flag = 'N'
1014 AND provisional_flag = 'Y'
1015 AND assignment_id = p_assignment_id
1016 AND resource_id = p_resource_id
1017 AND item_date = p_asgn_start_date;
1018
1019 IF l_provisional_flag = 'Y' THEN
1020 g_provisional_hours := p_asgn_effort;
1021 ELSE
1022 g_provisional_hours := 0;
1026 g_confirmed_hours := p_asgn_effort - g_provisional_hours;
1023 END IF;
1024 END IF;
1025
1027
1028 RETURN l_res_capacity_hrs;
1029
1030 EXCEPTION
1031 WHEN NO_DATA_FOUND THEN
1032 g_provisional_hours := 0;
1033 g_confirmed_hours := p_asgn_effort;
1034 RETURN l_res_capacity_hrs;
1035 END Get_Assignment_Measures;
1036
1037 ------------------------------------------------------------------------------------
1038 --
1039 -- FUNCTION : Get_Asgn_Provisional_Hours
1040 -- DESCRIPTION: This function gets the provisional hours of the assignment from
1041 -- the global variable g_prvisional_hours
1042 -- NOTE: This Function is solely used in discoverer report TR2
1043 -- Workbook: Team Role Details Worksheet: Assignment Details
1044 ------------------------------------------------------------------------------------
1045 FUNCTION Get_Asgn_Provisional_Hours
1046 RETURN NUMBER
1047 IS
1048 BEGIN
1049 RETURN g_provisional_hours;
1050 END Get_Asgn_Provisional_Hours;
1051
1052 ------------------------------------------------------------------------------------
1053 --
1054 -- FUNCTION : Get_Asgn_Confirmed_Hours
1055 -- DESCRIPTION: This function gets the confirmed hours of the assignment from
1056 -- the global variable g_confirmed_hours
1057 -- NOTE: This Function is solely used in discoverer report TR2
1058 -- Workbook: Team Role Details Worksheet: Assignment Details
1059 ------------------------------------------------------------------------------------
1060 FUNCTION Get_Asgn_Confirmed_Hours
1061 RETURN NUMBER
1062 IS
1063 BEGIN
1064 RETURN g_confirmed_hours;
1065 END Get_Asgn_Confirmed_Hours;
1066
1067
1068 ------------------------------------------------------------------------------
1069 -- PROCEDURE
1070 -- Get Default Staffing Owner
1071 -- PURPOSE
1072 -- This procedure returns the default team role
1073 -- staffing owner given the project_id and exp_org_id
1074 -- HISTORY
1075 -- 29-APR-2003 shyugen Created
1076 -- 10-JAN-2005 anigam Bug 4103207.Added a condition in the cursor 'get_team_member' to check that current date lies between
1077 -- effective_start_date and effective_end_date. And commented of the condition ROWNUM =1
1078 ------------------------------------------------------------------------------
1079 PROCEDURE Get_Default_Staffing_Owner
1080 ( p_project_id IN pa_projects_all.project_id%TYPE
1081 ,p_exp_org_id IN pa_project_assignments.expenditure_org_id%TYPE := NULL
1082 ,x_person_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1083 ,x_person_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1084 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1085 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1086
1087 l_person_id NUMBER := null;
1088 l_person_name PER_PEOPLE_F.full_name%TYPE := null;
1089 l_exp_org_id pa_project_assignments.expenditure_org_id%TYPE := null;
1090
1091 CURSOR get_team_member(c_project_role_id NUMBER) IS
1092 SELECT pp.resource_source_id, res.full_name
1093 FROM pa_project_parties pp
1094 ,per_all_people_f res
1095 WHERE pp.project_role_id = c_project_role_id
1096 AND pp.resource_type_id = 101 -- Bug 4752052 - added to improve performance
1097 AND TRUNC(sysdate) between TRUNC(pp.start_date_active) and TRUNC(nvl(pp.end_date_active, sysdate))
1098 AND pp.project_id = p_project_id
1099 AND pp.resource_source_id = res.person_id
1100 AND trunc(SYSDATE) BETWEEN res.effective_start_date AND res.effective_end_date --added for bug 4103207
1101 AND (res.current_employee_flag = 'Y' OR res.current_npw_flag = 'Y')-- Added for bug 4938392
1102 and pp.object_type = 'PA_PROJECTS'
1103 and pp.object_id = p_project_id; -- Bug Ref # 6802604
1104 --AND ROWNUM=1; Commented for bug 4103207
1105
1106
1107 BEGIN
1108
1109 IF p_exp_org_id IS NULL THEN
1110 SELECT carrying_out_organization_id INTO l_exp_org_id
1111 FROM pa_projects_all
1112 WHERE project_id = p_project_id;
1113 ELSE
1114 l_exp_org_id := p_exp_org_id;
1115 END IF;
1116
1117 -- 1. Check if Project Staffing Owner exists
1118 OPEN get_team_member(8);
1119 FETCH get_team_member INTO l_person_id, l_person_name;
1120 CLOSE get_team_member;
1121
1122 -- 2. If not, check if Primary Resource Contact exists
1123 IF (l_person_id IS NULL or l_person_name IS NULL) AND l_exp_org_id IS NOT NULL THEN
1124 l_person_id := PA_RESOURCE_UTILS.Get_Org_Prim_Contact_id(l_exp_org_id, 'PA_PRM_RES_PRMRY_CONTACT');
1125 l_person_name := PA_RESOURCE_UTILS.Get_Org_Prim_Contact_Name(l_exp_org_id, 'PA_PRM_RES_PRMRY_CONTACT');
1126 END IF;
1127
1128 -- 3. If not, check if Project Manager exists
1129 IF l_person_id IS NULL or l_person_name IS NULL THEN
1130 OPEN get_team_member(1);
1131 FETCH get_team_member INTO l_person_id, l_person_name;
1132 CLOSE get_team_member;
1133 END IF;
1134
1135 x_person_id := l_person_id;
1136 x_person_name := l_person_name;
1137 x_return_status := 'S';
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 x_return_status := 'S';
1142 x_person_id := null;
1143 x_person_name := null;
1144
1145 END Get_Default_Staffing_Owner;
1146
1147
1148 ------------------------------------------------------------------------------
1149 -- PROCEDURE
1153 -- staffing owners for a team role
1150 -- Get All Staffing Owner
1151 -- PURPOSE
1152 -- This procedure returns the project and team role
1154 -- HISTORY
1155 -- 29-APR-2003 shyugen Created
1156 ------------------------------------------------------------------------------
1157 PROCEDURE Get_All_Staffing_Owners
1158 ( p_assignment_id IN pa_project_assignments.assignment_id%TYPE
1159 ,p_project_id IN pa_projects_all.project_id%TYPE
1160 ,x_person_id_tbl OUT NOCOPY system.pa_num_tbl_type --File.Sql.39 bug 4440895
1161 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1162 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1163
1164
1165 l_person_id_tbl system.pa_num_tbl_type;
1166
1167 CURSOR get_all_staffing_owners IS
1168 SELECT resource_source_id
1169 FROM pa_project_parties ,
1170 per_all_people_f ppf
1171 WHERE project_role_id = 8
1172 AND resource_type_id = 101 -- Bug 4752052 - added to improve performance
1173 AND TRUNC ( sysdate ) BETWEEN TRUNC ( start_date_active ) AND TRUNC ( NVL ( end_date_active, sysdate ) )
1174 AND project_id = p_project_id
1175 AND object_type = 'PA_PROJECTS' -- Bug Ref # 6802604
1176 AND object_id = p_project_id -- Bug Ref # 6802604
1177 AND ppf.person_id = resource_source_id -- Bug Ref # 6802697
1178 AND TRUNC ( sysdate ) BETWEEN effective_start_date AND effective_end_date
1179 AND ( ppf.current_employee_flag = 'Y' OR ppf.current_npw_flag = 'Y' )
1180 UNION ALL
1181 SELECT staffing_owner_person_id
1182 FROM pa_project_assignments,
1183 per_all_people_f ppf
1184 WHERE assignment_id = p_assignment_id
1185 AND ppf.person_id = staffing_owner_person_id -- Bug Ref # 6802697
1186 AND TRUNC ( sysdate ) BETWEEN effective_start_date AND effective_end_date
1187 AND ( ppf.current_employee_flag = 'Y' OR ppf.current_npw_flag = 'Y' ) ;
1188
1189 BEGIN
1190
1191 OPEN get_all_staffing_owners;
1192 FETCH get_all_staffing_owners BULK COLLECT INTO l_person_id_tbl;
1193 CLOSE get_all_staffing_owners;
1194
1195 x_person_id_tbl := l_person_id_tbl;
1196 x_return_status := 'S';
1197
1198 EXCEPTION
1199 WHEN OTHERS THEN
1200 x_return_status := 'S';
1201 x_person_id_tbl := null;
1202 -- 4537865 : Start
1203 x_error_message_code := NULL ;
1204 -- 4537865 :End
1205 END Get_All_Staffing_Owners;
1206
1207 ------------------------------------------------------------------------------
1208 -- PROCEDURE
1209 -- Associate Planning Resource
1210 -- PURPOSE
1211 -- This procedure finds and associate planning resource to
1212 -- existing Team Roles when Resource List is changed on
1213 -- the Workplan
1214 -- HISTORY
1215 -- 11-MAR-2004 shyugen Created
1216 ------------------------------------------------------------------------------
1217 PROCEDURE Associate_Planning_Resources
1218 ( p_project_id IN NUMBER
1219 ,p_old_resource_list_id IN NUMBER
1220 ,p_new_resource_list_id IN NUMBER
1221 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1222 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1223 ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1224 IS
1225
1226
1227
1228 TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1229 TYPE var30_table_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
1230 TYPE var80_table_type IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
1231
1232 l_msg_index_out NUMBER;
1233
1234 l_asgmt_res_format_id NUMBER := NULL;
1235 l_req_res_format_id NUMBER := NULL;
1236 l_proj_rec_ver_num NUMBER := NULL;
1237
1238 l_assignment_id_tbl number_table_type;
1239 l_res_list_member_id_tbl number_table_type;
1240
1241 l_resource_source_id_tbl number_table_type;
1242 l_fcst_job_id_tbl number_table_type;
1243 l_exp_org_id_tbl number_table_type;
1244 l_expenditure_type_tbl var30_table_type;
1245 l_project_role_id_tbl number_table_type;
1246 l_person_type_tbl var30_table_type;
1247 l_assignment_name_tbl var80_table_type;
1248
1249
1250 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1251
1252 cursor get_proj_rec_ver_num is
1253 select record_version_number
1254 from pa_projects_all
1255 where project_id = p_project_id;
1256
1257 -- for FP-M, person_type is not tracked on a requirement
1258
1259 cursor get_req_res_list_member(c_req_res_format_id NUMBER) is
1260 select asgn.assignment_id,
1261 asgn.fcst_job_id,
1262 asgn.expenditure_organization_id,
1263 asgn.expenditure_type,
1264 asgn.project_role_id,
1265 asgn.assignment_name
1266 from pa_project_assignments asgn,
1267 pa_project_statuses ps
1268 where asgn.project_id = p_project_id
1269 and asgn.assignment_type = 'OPEN_ASSIGNMENT'
1270 and asgn.status_code = ps.project_status_code(+)
1271 and (ps.project_system_status_code = 'OPEN_ASGMT'
1272 OR ps.project_system_status_code IS NULL);
1273 -- for FP-M, only 2 person types are supported in PA: CWK and EMP
1274 -- A given person can be in multiple person types. However, person cannot
1275 -- be both CWK and EMP at the same time.
1276
1277 -- Bug 4221383: Cursor modified to get correct org_id, job_id and person_type
1281 aaf.job_id, --asgn.fcst_job_id,
1278 cursor get_asgmt_res_list_member(c_asgmt_res_format_id NUMBER) is
1279 select asgn.assignment_id,
1280 rta.person_id,
1282 rd.resource_organization_id, --asgn.expenditure_organization_id,
1283 asgn.expenditure_type,
1284 asgn.project_role_id,
1285 decode(peo.current_employee_flag, 'Y', 'EMP', 'CWK'), --ppt.system_person_type,
1286 asgn.assignment_name
1287 from pa_project_assignments asgn,
1288 pa_project_statuses ps,
1289 per_person_type_usages_f ptuf,
1290 per_person_types ppt,
1291 per_all_assignments_f aaf,
1292 pa_resource_txn_attributes rta,
1293 pa_resources_denorm rd,
1294 per_all_people_f peo
1295 where asgn.project_id = p_project_id
1296 and asgn.resource_id = rta.resource_id
1297 and rta.person_id = aaf.person_id
1298 and asgn.start_date between aaf.effective_start_date AND aaf.effective_end_date
1299 and asgn.assignment_type <> 'OPEN_ASSIGNMENT'
1300 and asgn.status_code = ps.project_status_code(+)
1301 and ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
1302 and rta.person_id = ptuf.person_id
1303 and ptuf.person_type_id = ppt.person_type_id
1304 and ppt.system_person_type in ('CWK', 'EMP')
1305 and asgn.start_date between ptuf.effective_start_date AND ptuf.effective_end_date
1306 and asgn.start_date between rd.resource_effective_start_date AND rd.resource_effective_end_date
1307 and rd.resource_id = asgn.resource_id
1308 and aaf.assignment_type in ('C','E')
1309 and aaf.primary_flag = 'Y'
1310 and peo.person_id = aaf.person_id
1311 and asgn.start_date between peo.effective_start_date AND peo.effective_end_date;
1312 /*select asgn.assignment_id,
1313 rta.person_id,
1314 asgn.fcst_job_id,
1315 asgn.expenditure_organization_id,
1316 asgn.expenditure_type,
1317 asgn.project_role_id,
1318 ppt.system_person_type,
1319 asgn.assignment_name
1320 from pa_project_assignments asgn,
1321 pa_project_statuses ps,
1322 per_person_type_usages_f ptuf,
1323 per_person_types ppt,
1324 per_all_assignments_f aaf,
1325 pa_resource_txn_attributes rta
1326 where asgn.project_id = p_project_id
1327 and asgn.resource_id = rta.resource_id
1328 and rta.person_id = aaf.person_id
1329 and asgn.start_date between aaf.effective_start_date AND aaf.effective_end_date
1330 and asgn.assignment_type <> 'OPEN_ASSIGNMENT'
1331 and asgn.status_code = ps.project_status_code(+)
1332 and ps.project_system_status_code <> 'STAFFED_ASGMT_CANCEL'
1333 and rta.person_id = ptuf.person_id
1334 and ptuf.person_type_id = ppt.person_type_id
1335 and ppt.system_person_type in ('CWK', 'EMP')
1336 and asgn.start_date between ptuf.effective_start_date AND ptuf.effective_end_date;
1337 */
1338 cursor team_role_exists is
1339 SELECT 'T'
1340 from pa_project_assignments
1341 where resource_list_member_id is not null
1342 and project_id = p_project_id
1343 and rownum = 1;
1344
1345 l_team_role_exists VARCHAR2(1):= 'F';
1346 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N'); -- 5345129
1347
1348 BEGIN
1349 IF l_debug_mode = 'Y' THEN -- 5345129
1350 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENT_UTILS.Associate_Planning_Resources'
1351 ,x_msg => 'old_pls_id='||p_old_resource_list_id||
1352 'new_pls_id='||p_new_resource_list_id
1353 ,x_log_level => li_message_level);
1354 END IF;
1355
1356 -- Get the default resource formats for team role creation/update
1357 -- if the resource list has been changed on the workplan
1358 IF NOT(p_old_resource_list_id is null and p_new_resource_list_id is null) AND
1359 NOT(p_old_resource_list_id is not null AND
1360 p_new_resource_list_id is not null AND
1361 p_old_resource_list_id = p_new_resource_list_id) THEN
1362
1363 /* Bug 3647692
1364 -- Disallow workplan resource list from changing if
1365 -- there exist project team roles already associated to
1366 -- planning resources in the resource list
1367 OPEN team_role_exists;
1368 FETCH team_role_exists INTO l_team_role_exists;
1369 CLOSE team_role_exists;
1370
1371 IF l_team_role_exists = 'T' THEN
1372 PA_UTILS.Add_Message(p_app_short_name => 'PA',
1373 p_msg_name => 'PA_NO_UP_RL_TR');
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 RETURN;
1376 END IF;
1377 */
1378 IF p_new_resource_list_id is not null THEN
1379 PA_PLANNING_RESOURCE_UTILS.Get_Res_Format_for_Team_Role(
1380 p_resource_list_id => p_new_resource_list_id
1381 ,x_asgmt_res_format_id => l_asgmt_res_format_id
1382 ,x_req_res_format_id => l_req_res_format_id
1383 ,x_return_status => l_return_status);
1384 --dbms_output.put_line ('x_return_status ' || l_return_status);
1385 --dbms_output.put_line ('Assignment res format ' || l_asgmt_res_format_id);
1386 --dbms_output.put_line ('Requirement res format ' || l_req_res_format_id);
1387 IF l_debug_mode = 'Y' THEN -- 5345129
1388 pa_debug.write(x_module => 'pa.plsql.PA_ASSIGNMENT_UTILS.Associate_Planning_Resources'
1389 ,x_msg => 'asgmt_format_id='||l_asgmt_res_format_id||
1390 'req_format_id='||l_req_res_format_id
1391 ,x_log_level => li_message_level);
1392 END IF;
1393
1397 END IF;
1394 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1395 x_return_status := l_return_status;
1396 RETURN;
1398
1399 ELSE
1400 l_asgmt_res_format_id := null;
1401 l_req_res_format_id := null;
1402
1403 END IF;
1404
1405 OPEN get_proj_rec_ver_num;
1406 FETCH get_proj_rec_ver_num INTO l_proj_rec_ver_num;
1407 CLOSE get_proj_rec_ver_num;
1408
1409 -- store default formats
1410 pa_resource_setup_pvt.UPDATE_ADDITIONAL_STAFF_INFO
1411 ( p_init_msg_list => FND_API.G_FALSE
1412 ,p_validate_only => FND_API.G_FALSE
1413 ,p_project_id => p_project_id
1414 ,p_record_version_number => l_proj_rec_ver_num
1415 ,p_proj_req_res_format_id => l_req_res_format_id
1416 ,p_proj_asgmt_res_format_id => l_asgmt_res_format_id
1417 ,x_return_status => l_return_status
1418 ,x_msg_count => x_msg_count
1419 ,x_msg_data => x_msg_data );
1420
1421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1422 x_return_status := l_return_status;
1423 RETURN;
1424 END IF;
1425
1426 -- get all requirements and corresponding planning resource
1427 OPEN get_req_res_list_member(l_req_res_format_id);
1428 FETCH get_req_res_list_member BULK COLLECT INTO
1429 l_assignment_id_tbl,
1430 l_fcst_job_id_tbl,
1431 l_exp_org_id_tbl,
1432 l_expenditure_type_tbl,
1433 l_project_role_id_tbl,
1434 l_assignment_name_tbl;
1435 CLOSE get_req_res_list_member;
1436
1437 --dbms_output.put_line ('Assignments to be updated' || l_assignment_id_tbl.COUNT);
1438
1439 -- update planning resource on Project team roles only because
1440 -- resource list cannot be changed on the workplan once task
1441 -- assignment has been created.
1442 IF l_assignment_id_tbl.COUNT > 0 THEN
1443
1444 FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
1445
1446 IF l_req_res_format_id IS NOT NULL THEN
1447 l_res_list_member_id_tbl(j) := Pa_Planning_Resource_Utils.Derive_Resource_List_Member(p_project_id,
1448 l_req_res_format_id,
1449 NULL,
1450 l_fcst_job_id_tbl(j),
1451 l_exp_org_id_tbl(j),
1452 l_expenditure_type_tbl(j),
1453 NULL,
1454 l_project_role_id_tbl(j),
1455 NULL,
1456 l_assignment_name_tbl(j));
1457 ELSE
1458 l_res_list_member_id_tbl(j) := NULL;
1459 END IF;
1460 END LOOP;
1461
1462 FORALL i IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1463 UPDATE pa_project_assignments
1464 SET resource_list_member_id = l_res_list_member_id_tbl(i),
1465 record_version_number = nvl(record_version_number,0) + 1
1466 WHERE assignment_id = l_assignment_id_tbl(i);
1467
1468 END IF;
1469
1470 -- get all assignment and corresponding planning resource
1471 OPEN get_asgmt_res_list_member(l_asgmt_res_format_id);
1472 FETCH get_asgmt_res_list_member BULK COLLECT INTO
1473 l_assignment_id_tbl,
1474 l_resource_source_id_tbl,
1475 l_fcst_job_id_tbl,
1476 l_exp_org_id_tbl,
1477 l_expenditure_type_tbl,
1478 l_project_role_id_tbl,
1479 l_person_type_tbl,
1480 l_assignment_name_tbl;
1481 CLOSE get_asgmt_res_list_member;
1482
1483 -- dbms_output.put_line ('Requirements to be updated' || l_assignment_id_tbl.COUNT);
1484
1485 -- update planning resource on Project team roles only because
1486 -- resource list cannot be changed on the workplan once task
1487 -- assignment has been created.
1488 IF l_assignment_id_tbl.COUNT > 0 THEN
1489
1490 FOR j IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST LOOP
1491
1492 IF l_asgmt_res_format_id IS NOT NULL THEN
1493 l_res_list_member_id_tbl(j) := Pa_Planning_Resource_Utils.Derive_Resource_List_Member(p_project_id,
1494 l_asgmt_res_format_id,
1495 l_resource_source_id_tbl(j),
1496 l_fcst_job_id_tbl(j),
1497 l_exp_org_id_tbl(j),
1498 l_expenditure_type_tbl(j),
1499 NULL,
1500 l_project_role_id_tbl(j),
1501 l_person_type_tbl(j),
1502 l_assignment_name_tbl(j));
1503 ELSE
1504 l_res_list_member_id_tbl(j) := NULL;
1505 END IF;
1506 END LOOP;
1507
1508 FORALL i IN l_assignment_id_tbl.FIRST .. l_assignment_id_tbl.LAST
1512 WHERE assignment_id = l_assignment_id_tbl(i);
1509 UPDATE pa_project_assignments
1510 SET resource_list_member_id = l_res_list_member_id_tbl(i),
1511 record_version_number = nvl(record_version_number, 0) + 1
1513
1514 END IF;
1515
1516 END IF; -- resource list changed
1517
1518 x_return_status := l_return_status;
1519
1520 x_msg_count := FND_MSG_PUB.Count_Msg;
1521
1522 IF x_msg_count = 1 THEN
1523 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1524 ,p_msg_index => 1
1525 ,p_data => x_msg_data
1526 ,p_msg_index_out => l_msg_index_out
1527 );
1528 END IF;
1529 -- 4537865 : Included Exception block
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1533 x_msg_count := 1 ;
1534 x_msg_data := SUBSTRB(SQLERRM,1,240);
1535
1536 Fnd_Msg_Pub.add_exc_msg
1537 ( p_pkg_name => 'PA_ASSIGNMENT_UTILS'
1538 , p_procedure_name => 'ASSOCIATE_PLANNING_RESOURCES'
1539 , p_error_text => x_msg_data);
1540
1541 RAISE;
1542 END Associate_Planning_Resources;
1543
1544 /* Added new function to check if team role is associated with multiple assignement
1545 or requirement for bug 3724780*/
1546
1547 FUNCTION Get_multi_team_role_flag
1548
1549 RETURN VARCHAR2 IS
1550
1551 l_flag varchar2(1) ;
1552
1553 BEGIN
1554
1555 l_flag := 'N';
1556
1557 l_flag := PA_TASK_ASSIGNMENT_UTILS.p_multi_asgmt_req_flag;
1558
1559 RETURN l_flag;
1560
1561 EXCEPTION
1562
1563 WHEN OTHERS THEN
1564
1565 NULL;
1566
1567 RETURN l_flag;
1568
1569 END;
1570
1571 /* the query logic must be same as pa_task_assignment_utils.Get_Team_Role */
1572
1573 FUNCTION Get_project_assignment_id
1574 (p_resource_list_member_id IN NUMBER,
1575 p_project_id IN NUMBER) RETURN NUMBER IS
1576
1577 Cursor C_ASMT_ID IS
1578 select distinct pap.assignment_id
1579 from pa_project_assignments pap, pa_project_statuses stat
1580 where pap.resource_list_member_id = p_resource_list_member_id
1581 and pap.project_id = p_project_id
1582 and pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+)
1583 and nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not in ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');
1584
1585 l_asmt_id NUMBER;
1586
1587 BEGIN
1588 IF p_resource_list_member_id IS NOT NULL THEN
1589 OPEN C_ASMT_ID;
1590 Fetch C_ASMT_ID INTO l_asmt_id;
1591 CLOSE C_ASMT_ID;
1592 RETURN l_asmt_id;
1593 ELSE
1594 RETURN null;
1595 END IF;
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 RETURN null;
1599 END;
1600
1601 /* the query logic must be same as pa_task_assignment_utils.Get_Team_Role */
1602 FUNCTION Get_project_assignment_type
1603 (p_resource_list_member_id IN NUMBER,
1604 p_project_id IN NUMBER) RETURN VARCHAR2 IS
1605
1606 Cursor C_ASMT_TYPE IS
1607 select distinct pap.assignment_type
1608 from pa_project_assignments pap, pa_project_statuses stat
1609 where pap.resource_list_member_id = p_resource_list_member_id
1610 and pap.project_id = p_project_id
1611 and pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+)
1612 and nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not in ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');
1613
1614 l_asmt_type VARCHAR2(30);
1615
1616 BEGIN
1617 IF p_resource_list_member_id IS NOT NULL THEN
1618 OPEN C_ASMT_TYPE;
1619 Fetch C_ASMT_TYPE INTO l_asmt_type;
1620 CLOSE C_ASMT_TYPE;
1621 RETURN l_asmt_type;
1622 ELSE
1623 RETURN null;
1624 END IF;
1625 EXCEPTION
1626 WHEN OTHERS THEN
1627 RETURN null;
1628 END;
1629
1630
1631
1632
1633 FUNCTION Check_Res_Format_Used_For_TR(p_res_format_id IN NUMBER, p_resource_list_id IN NUMBER)
1634 RETURN VARCHAR2
1635 IS
1636 l_format_used_flag VARCHAR2(1);
1637 BEGIN
1638 SELECT 'Y'
1639 INTO l_format_used_flag
1640 FROM pa_projects_all pa,
1641 pa_proj_fp_options pfo
1642 WHERE (pa.proj_req_res_format_id = p_res_format_id OR pa.proj_asgmt_res_format_id = p_res_format_id)
1643 AND pa.project_id = pfo.project_id
1644 AND pfo.cost_resource_list_id = p_resource_list_id
1645 AND pfo.fin_plan_type_id = (SELECT fin_plan_type_id
1646 FROM pa_fin_plan_types_b
1647 WHERE use_for_workplan_flag = 'Y')
1648 AND pfo.fin_plan_option_level_code = 'PLAN_TYPE'
1649 AND rownum = 1;
1650
1651 RETURN l_format_used_flag;
1652 EXCEPTION
1653 WHEN OTHERS THEN
1654 RETURN 'N';
1655 END;
1656
1657 FUNCTION Get_single_submitted_status(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER)
1658 RETURN VARCHAR2 IS
1659 Cursor C_Team_Role_Count IS
1660 select count(*) from pa_project_assignments pa
1661 where pa.project_id = p_project_id
1665 select count(*) from pa_project_assignments pa
1662 and pa.resource_list_member_id = p_resource_list_member_id;
1663
1664 Cursor C_Submitted_Count IS
1666 where pa.project_id = p_project_id
1667 and pa.resource_list_member_id = p_resource_list_member_id
1668 and pa.APPRVL_STATUS_CODE = 'ASGMT_APPRVL_SUBMITTED';
1669
1670 l_team_role_count NUMBER;
1671 l_submitted_count NUMBER;
1672 l_single_submitted_flag VARCHAR2(1) := 'N';
1673
1674 BEGIN
1675
1676 OPEN C_Team_Role_Count;
1677 Fetch C_Team_Role_Count INTO l_team_role_count;
1678 CLOSE C_Team_Role_Count;
1679
1680 IF l_team_role_count = 1 THEN
1681
1682 OPEN C_Submitted_Count;
1683 Fetch C_Submitted_Count INTO l_submitted_count;
1684 CLOSE C_Submitted_Count;
1685
1686 IF l_submitted_count = 1 THEN
1687 l_single_submitted_flag := 'Y';
1688 END IF;
1689
1690 END IF;
1691
1692 return l_single_submitted_flag;
1693
1694 EXCEPTION WHEN OTHERS THEN
1695
1696 RETURN 'N';
1697 END;
1698
1699 ------------------------------------------------------------------------------
1700 -- FUNCTION
1701 --
1702 -- PURPOSE
1703 -- This function checks if the resource list member_id has task assignment's beyond team role dates
1704 -- HISTORY
1705 -- 09-15-2004 jraj Created
1706 ------------------------------------------------------------------------------
1707 FUNCTION Get_At_Risk_Status(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER, p_budget_version_id IN NUMBER, p_start IN VARCHAR2)
1708 RETURN VARCHAR2 IS
1709
1710 Cursor C_At_Risk_Start IS
1711 select ra.schedule_start_date, pa.start_date, pa.end_date
1712 from pa_resource_assignments ra , pa_project_assignments pa
1713 where pa.assignment_id = ra.project_assignment_id
1714 and ra.project_id = p_project_id
1715 and ra.budget_version_id = p_budget_version_id
1716 and ra.resource_list_member_id = p_resource_list_member_id;
1717 C_At_Risk_Start_Rec C_At_Risk_Start%ROWTYPE;
1718
1719 Cursor C_At_Risk_End IS
1720 select ra.schedule_end_date, pa.end_date, pa.start_date
1721 from pa_resource_assignments ra , pa_project_assignments pa
1722 where pa.assignment_id = ra.project_assignment_id
1723 and ra.project_id = p_project_id
1724 and ra.budget_version_id = p_budget_version_id
1725 and ra.resource_list_member_id = p_resource_list_member_id;
1726 C_At_Risk_End_Rec C_At_Risk_End%ROWTYPE;
1727
1728 l_risk_status VARCHAR2(1) := 'N';
1729
1730 BEGIN
1731
1732 IF p_start = 'Y' THEN
1733
1734 OPEN C_At_Risk_Start;
1735 Fetch C_At_Risk_Start INTO C_At_Risk_Start_rec;
1736
1737
1738 IF (C_At_Risk_Start_Rec.start_date > C_At_Risk_Start_Rec.schedule_start_date) OR
1739 (C_At_Risk_Start_Rec.end_date < C_At_Risk_Start_Rec.schedule_start_date) THEN
1740
1741 l_risk_status := 'Y';
1742
1743 END IF;
1744
1745 WHILE C_At_Risk_Start%FOUND LOOP
1746 Fetch C_At_Risk_Start INTO C_At_Risk_Start_rec;
1747
1748
1749 IF (C_At_Risk_Start_Rec.start_date > C_At_Risk_Start_Rec.schedule_start_date) OR
1750 (C_At_Risk_Start_Rec.end_date < C_At_Risk_Start_Rec.schedule_start_date) THEN
1751
1752 l_risk_status := 'Y';
1753
1754 END IF;
1755
1756 END LOOP;
1757
1758 CLOSE C_At_Risk_Start;
1759
1760 ELSIF p_start = 'N' THEN
1761 OPEN C_At_Risk_End;
1762 Fetch C_At_Risk_End INTO C_At_Risk_End_rec;
1763
1764 IF (C_At_Risk_End_Rec.end_date < C_At_Risk_End_Rec.schedule_end_date) OR
1765 (C_At_Risk_End_Rec.start_date > C_At_Risk_End_Rec.schedule_end_date) THEN
1766
1767 l_risk_status := 'Y';
1768
1769 END IF;
1770
1771 WHILE C_At_Risk_End%FOUND LOOP
1772 Fetch C_At_Risk_End INTO C_At_Risk_End_rec;
1773 IF (C_At_Risk_End_Rec.end_date < C_At_Risk_End_Rec.schedule_end_date) OR
1774 (C_At_Risk_End_Rec.start_date > C_At_Risk_End_Rec.schedule_end_date) THEN
1775
1776 l_risk_status := 'Y';
1777
1778 END IF;
1779 END LOOP;
1780 CLOSE C_At_Risk_End;
1781 END IF;
1782
1783
1784
1785 return l_risk_status;
1786
1787 EXCEPTION WHEN OTHERS THEN
1788
1789 RETURN 'N';
1790 END;
1791
1792
1793
1794 FUNCTION Get_Team_Role_Start(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER)
1795 RETURN DATE IS
1796 Cursor C_Team_Role_Start IS
1797
1798 select min(pap.start_date) team_role_start
1799 from pa_project_assignments pap, pa_project_statuses stat
1800 where
1801 pap.resource_list_member_id = p_resource_list_member_id
1802 and pap.project_id = p_project_id
1803 and
1804 pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+) and
1805 nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not in
1806 ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');
1807 C_Team_Role_Start_Rec C_Team_Role_Start%ROWTYPE;
1808
1809
1810 BEGIN
1811 OPEN C_Team_Role_Start;
1812 FETCH C_Team_Role_Start INTO C_Team_Role_Start_Rec;
1813 CLOSE C_Team_Role_Start;
1814
1815 RETURN C_Team_Role_Start_Rec.team_role_start;
1816 EXCEPTION WHEN OTHERS THEN
1817 RETURN to_date(NULL);
1818 END;
1819
1820
1821
1822 FUNCTION Get_Team_Role_End(p_project_id IN NUMBER, p_resource_list_member_id IN NUMBER)
1823 RETURN DATE IS
1824 Cursor C_Team_Role_End IS
1825 select max(pap.End_date) team_role_End
1826 from pa_project_assignments pap, pa_project_statuses stat
1827 where
1828 pap.resource_list_member_id = p_resource_list_member_id
1829 and pap.project_id = p_project_id
1830 and
1831 pap.STATUS_CODE = stat.PROJECT_STATUS_CODE (+) and
1832 nvl(stat.PROJECT_SYSTEM_STATUS_CODE, '-1') not in
1833 ('OPEN_ASGMT_CANCEL','STAFFED_ASGMT_CANCEL', 'OPEN_ASGMT_FILLED');
1834 C_Team_Role_End_Rec C_Team_Role_End%ROWTYPE;
1835
1836
1837 BEGIN
1838 OPEN C_Team_Role_End;
1839 FETCH C_Team_Role_End INTO C_Team_Role_End_Rec;
1840 CLOSE C_Team_Role_End;
1841
1842 RETURN C_Team_Role_End_Rec.team_role_End;
1843 EXCEPTION WHEN OTHERS THEN
1844 RETURN to_date(NULL);
1845 END;
1846
1847 -- 4363092 Added following function for MOAC Changes
1848 -- returns default org_id
1849
1850 FUNCTION Get_Dft_Info
1851 RETURN NUMBER IS
1852
1853 l_dflt_ou hr_operating_units.organization_id%TYPE;
1854 l_ou_count NUMBER;
1855 l_dflt_ou_name hr_operating_units.name%TYPE;
1856
1857 l_proj_imp_flag VARCHAR2(1) := 'N';
1858
1859 Cursor c_imp_ous IS
1860 select org_id
1861 from pa_implementations where rownum = 1;
1862
1863 Cursor c_check_proj_imp(p_org_id NUMBER ) IS
1864 select 'Y'
1865 from pa_implementations
1866 where org_id = p_org_id ;
1867
1868 l_ou_id hr_operating_units.organization_id%TYPE;
1869
1870 BEGIN
1871
1872 PA_MOAC_UTILS.GET_DEFAULT_OU
1873 (
1874 p_product_code => 'PA',
1875 p_default_org_id => l_dflt_ou,
1876 p_default_ou_name => l_dflt_ou_name,
1877 p_ou_count => l_ou_count
1878 );
1879
1880 IF l_dflt_ou IS NOT NULL THEN
1881
1882 OPEN c_check_proj_imp(l_dflt_ou);
1883 FETCH c_check_proj_imp INTO l_proj_imp_flag;
1884 CLOSE c_check_proj_imp;
1885
1886 IF l_proj_imp_flag = 'Y' THEN
1887 l_ou_id := l_dflt_ou;
1888 END IF;
1889
1890 END IF;
1891
1892 IF l_dflt_ou IS NULL OR l_proj_imp_flag = 'N' THEN
1893
1894 OPEN c_imp_ous;
1895 FETCH c_imp_ous INTO l_ou_id;
1896 CLOSE c_imp_ous;
1897
1898 END IF;
1899
1900 RETURN l_ou_id;
1901
1902 END Get_Dft_Info;
1903
1904 END PA_ASSIGNMENT_UTILS;
[Home] [Help]