1 PACKAGE BODY PA_PROJECT_PARTIES_UTILS AS
2 /* $Header: PARPPU2B.pls 120.12 2011/12/06 21:14:11 skkoppul ship $ */
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 FUNCTION VALIDATE_DELETE_PARTY_OK (p_project_id IN NUMBER,
7 p_project_party_id IN NUMBER) RETURN VARCHAR2 IS
8
9 BEGIN
10
11 RETURN 'Y';
12
13 END VALIDATE_DELETE_PARTY_OK;
14
15 FUNCTION ACTIVE_PARTY ( p_start_date_active IN DATE,
16 p_end_date_active IN DATE) RETURN VARCHAR2 IS
17
18 BEGIN
19 IF( (SYSDATE BETWEEN p_start_date_active AND p_end_date_active) OR
20 (p_start_date_active <= SYSDATE AND p_end_date_active IS NULL)) THEN
21 RETURN 'Y';
22 ELSE
23 RETURN 'N';
24 END IF;
25 END ACTIVE_PARTY;
26
27
28 PROCEDURE GET_PROJECT_DATES (p_project_id IN NUMBER,
29 x_project_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
30 x_project_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
31 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
32 BEGIN
33 x_return_status := FND_API.G_RET_STS_SUCCESS;
34
35 /* Bug 2636791 - changes begin */
36 /* Commenting this query and selecting start date from PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE */
37 /* select start_date, completion_date
38 into x_project_start_date, x_project_end_date
39 from pa_projects_all
40 where project_id = p_project_id; */
41
42 SELECT PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(p_project_id), completion_date
43 INTO x_project_start_date, x_project_end_date
44 FROM pa_projects_all
45 WHERE project_id = p_project_id;
46
47 /* Bug 2636791 - changes end */
48
49 EXCEPTION WHEN OTHERS THEN
50 fnd_message.set_name('PA','PA_NO_PROJECT_ID');
51 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
52 fnd_message.set_token('PROCEDURE_NAME','GET_PROJECT_DATES');
53 fnd_msg_pub.ADD;
54 x_return_status := FND_API.G_RET_STS_ERROR;
55 END;
56
57
58 PROCEDURE VALIDATE_PROJECT_PARTY( p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
59 p_debug_mode IN VARCHAR2 DEFAULT 'N',
60 p_object_id IN NUMBER,
61 p_OBJECT_TYPE IN VARCHAR2,
62 p_project_role_id IN NUMBER,
63 p_resource_type_id IN NUMBER DEFAULT 101,
64 p_resource_source_id IN NUMBER,
65 p_start_date_active IN DATE,
66 p_scheduled_flag IN VARCHAR2,
67 p_record_version_number IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
68 p_calling_module IN VARCHAR2,
69 p_action IN VARCHAR2,
70 p_project_id IN NUMBER,
71 p_project_end_date IN DATE,
72 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
73 p_project_party_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
74 x_call_overlap IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
75 x_assignment_action IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
76 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
77
78 l_error_occured VARCHAR2(1) := 'N';
79 l_project_party_id NUMBER;
80 l_record_version_number NUMBER;
81 l_end_date_active DATE; --Bug 4565156 NOCOPY changes
82
83 CURSOR c_duplicate_customer_org IS
84 SELECT 'Y'
85 FROM pa_project_parties ppp,
86 pa_project_role_types_b r1,
87 pa_project_role_types_b r2
88 WHERE r1.project_role_id = p_project_role_id
89 AND r1.role_party_class = 'CUSTOMER'
90 AND ppp.object_id = p_object_id
91 AND ppp.object_type = p_object_type
92 AND ppp.resource_type_id = 112
93 AND ppp.resource_source_id = p_resource_source_id
94 AND r2.project_role_id = ppp.project_role_id
95 AND r2.role_party_class = 'CUSTOMER'
96 AND ROWNUM=1;
97
98 l_dummy VARCHAR2(1);
99
100
101 BEGIN
102 x_return_status := FND_API.G_RET_STS_SUCCESS;
103 pa_debug.set_err_stack('Validate_project_party');
104
105 l_end_date_active := p_end_date_active; --Bug 4565156 NOCOPY changes
106
107 --MT OrgRole changes: ext people are not schedulable
108 IF p_resource_type_id = 112 AND p_scheduled_flag = 'Y' THEN
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 fnd_message.set_name('PA','PA_NO_SCHEDULE_HZ_PARTY');
111 fnd_msg_pub.ADD;
112 RETURN;
113 END IF;
114 --/MT
115
116 /*Code Addition for bug 2983546 -- Ext people are not allowed as project Managers */
117
118 IF p_resource_type_id = 112 AND p_project_role_id=1 THEN
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 fnd_message.set_name('PA','PA_NO_EXT_MANAGER');
121 fnd_msg_pub.ADD;
122 RETURN;
123 END IF;
124
125 /*Code Addition ends - 2983546*/
126
127
128 --- call to check all mandatory fields are given
129 IF (p_debug_mode = 'Y') THEN
130 pa_debug.debug('Validate_project_party: Calling check_mandatory_fields.');
131 END IF;
132 pa_project_parties_utils.check_mandatory_fields(p_project_role_id => p_project_role_id,
133 p_resource_type_id => p_resource_type_id,
134 p_resource_source_id => p_resource_source_id,
135 p_start_date_active => p_start_date_active,
136 p_end_date_active => p_end_date_active,
137 p_project_end_date => p_project_end_date,
138 p_scheduled_flag => p_scheduled_flag,
139 x_error_occured => l_error_occured);
140 IF (l_error_occured = 'Y') THEN
141 x_return_status := FND_API.G_RET_STS_ERROR;
142 END IF;
143
144
145
146 --dbms_output.put_line('After checking the mandatory errors'||x_return_status);
147
148 -- Bug 2671210. Check to validate role-party combination.
149 --- call to validate role party combination
150 IF (p_debug_mode = 'Y') THEN
151 pa_debug.debug('Validate_role_party: Calling Validate_role_party.');
152 END IF;
153
154 pa_project_parties_utils.Validate_role_party(p_project_role_id => p_project_role_id,
155 p_resource_type_id => p_resource_type_id,
156 p_resource_source_id => p_resource_source_id,
157 x_error_occured => l_error_occured);
158
159 IF (l_error_occured = 'Y') THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 END IF;
162 -- bug2671210 ended.
163
164 --- call to validate the start and end dates for the person
165 IF (p_debug_mode = 'Y') THEN
166 pa_debug.debug('Validate_project_party: Calling validate_dates.');
167 END IF;
168 pa_project_parties_utils.validate_dates(p_start_date_active => p_start_date_active,
169 p_end_date_active => p_end_date_active,
170 x_error_occured => l_error_occured);
171
172 IF (l_error_occured = 'Y') THEN
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 END IF;
175
176 /* Start of Bug 3096700 */
177 /* Adding a validation for checking if the project role start date lies between specified start active and end active date */
178 l_dummy := 'N';
179 BEGIN
180 SELECT 'Y'
181 INTO l_dummy
182 FROM pa_project_role_types_b
183 WHERE project_role_id = p_project_role_id
184 AND p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,p_start_date_active)
185 AND (p_end_date_active IS NULL
186 OR p_end_date_active BETWEEN start_date_active AND NVL(end_date_active,p_end_date_active));
187 EXCEPTION
188 WHEN NO_DATA_FOUND THEN
189 x_return_status := FND_API.G_RET_STS_ERROR;
190 fnd_message.set_name('PA','PA_ROLE_INACTIVE');
191 fnd_msg_pub.ADD;
192 END;
193 /* End of 3096700 */
194 --dbms_output.put_line('after validating dates'||x_return_status);
195 x_assignment_action := 'NOACTION';
196
197 IF (p_calling_module NOT IN ('EXCHANGE','FORM')) THEN
198
199 IF (p_action = 'INSERT' OR pa_project_parties_utils.get_scheduled_flag(p_project_party_id, p_record_version_number) <> p_scheduled_flag) THEN
200
201 IF p_resource_type_id = 112 THEN
202 IF (p_debug_mode = 'Y') THEN
203 pa_debug.debug('Validate_project_party: Looking for customer org duplicate.');
204 END IF;
205
206 OPEN c_duplicate_customer_org;
207 FETCH c_duplicate_customer_org INTO l_dummy;
208 IF c_duplicate_customer_org%FOUND THEN
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 fnd_message.set_name('PA','PA_DUPLICATE_CUSTOMER_ORG');
211 fnd_msg_pub.ADD;
212 CLOSE c_duplicate_customer_org;
213 RETURN;
214 END IF;
215 CLOSE c_duplicate_customer_org;
216 END IF;
217
218
219 IF (p_debug_mode = 'Y') THEN
220 pa_debug.debug('Validate_project_party: Checking for schedule flag.');
221 END IF;
222
223 IF p_scheduled_flag = 'Y' THEN
224 IF VALIDATE_SCHEDULE_ALLOWED(p_project_role_id) = 'Y' THEN
225 --dbms_output.put_line('schedule is allowed for this role');
226 x_assignment_action := 'CREATE';
227 ELSE
228 --dbms_output.put_line('schedule is not allowed for this role');
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 fnd_message.set_name('PA','PA_NO_SCHEDULE_ALLOWED');
231 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
232 --fnd_message.set_token('PROCEDURE_NAME','VALIDATE_PROJECT_PARTY');
233 fnd_msg_pub.ADD;
234 END IF;
235 ELSE
236 IF (p_debug_mode = 'Y') THEN
237 pa_debug.debug('Validate_project_party: No schedule required.');
238 END IF;
239
240 --dbms_output.put_line('no assignment required');
241 x_assignment_action := 'NOACTION';
242 END IF;
243
244 IF p_scheduled_flag = 'N' AND p_action = 'UPDATE' THEN
245 IF (p_debug_mode = 'Y') THEN
246 pa_debug.debug('Validate_project_party: Need to delete schedule.');
247 END IF;
248
249 --dbms_output.put_line('need to delete assignment');
250 x_assignment_action := 'DELETE';
251 END IF;
252 END IF;
253
254 IF x_assignment_action = 'CREATE' OR (p_action = 'INSERT' AND p_calling_module <> 'PROJECT_MEMBER') THEN
255 --dbms_output.put_line('trying to get person id');
256 IF (p_debug_mode = 'Y') THEN
257 pa_debug.debug('Validate_project_party: Getting the project party id.');
258 END IF;
259 pa_project_parties_utils.get_person_party_id(p_object_type => p_object_type,
260 p_object_id => p_object_id,
261 p_project_role_id => p_project_role_id,
262 p_resource_type_id => p_resource_type_id,
263 p_resource_source_id => p_resource_source_id,
264 p_start_date_active => p_start_date_active,
265 p_end_date_active => p_end_date_active,
266 x_project_party_id => l_project_party_id,
267 x_record_version_number => l_record_version_number);
268
269 --dbms_output.put_line('person id'||to_char(l_project_party_id));
270 IF l_project_party_id <> -999 THEN
271 x_call_overlap := 'N';
272 IF pa_project_parties_utils.get_scheduled_flag(l_project_party_id, l_record_version_number) <> 'Y' THEN
273 p_project_party_id := l_project_party_id;
274 p_record_version_number := l_record_version_number;
275 ELSE
276 --dbms_output.put_line('cannot create duplicate record');
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 fnd_message.set_name('PA','PA_XC_TOO_MANY_OMGRS');
279 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
280 --fnd_message.set_token('PROCEDURE_NAME','VALIDATE_PROJECT_PARTY');
281 fnd_msg_pub.ADD;
282 END IF;
283 END IF;
284 END IF;
285
286 END IF;
287
288 IF x_call_overlap = 'Y' OR p_action = 'UPDATE' OR p_calling_module = 'EXCHANGE' THEN
289
290 IF p_project_role_id = 1 THEN -- hard coded for Project Manager
291 IF (p_debug_mode = 'Y') THEN
292 pa_debug.debug('Validate_project_party: Calling validate_no_overlap_manager.');
293 END IF;
294 pa_project_parties_utils.validate_no_overlap_manager(p_object_type => p_object_type,
295 p_object_id => p_object_id,
296 p_project_role_id => p_project_role_id,
297 p_project_party_id => p_project_party_id,
298 p_start_date_active => p_start_date_active,
299 p_end_date_active => p_end_date_active,
300 x_error_occured => l_error_occured);
301 IF (l_error_occured = 'Y') THEN
302 x_return_status := FND_API.G_RET_STS_ERROR;
303 END IF;
304 --dbms_output.put_line('validating manager');
305 ELSE
306 IF (p_debug_mode = 'Y') THEN
307 pa_debug.debug('Validate_project_party: Calling validate_person_not_overlapped.');
308 END IF;
309 pa_project_parties_utils.validate_person_not_overlapped(p_object_type => p_object_type,
310 p_object_id => p_object_id,
311 p_project_role_id => p_project_role_id,
312 p_project_party_id => p_project_party_id,
313 p_resource_type_id => p_resource_type_id,
314 p_resource_source_id => p_resource_source_id,
315 p_start_date_active => p_start_date_active,
316 p_end_date_active => p_end_date_active,
317 x_error_occured => l_error_occured);
318 IF (l_error_occured = 'Y') THEN
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 END IF;
321 --dbms_output.put_line('validating overlap');
322 END IF;
323 END IF;
324
325 EXCEPTION WHEN OTHERS THEN
326 p_end_date_active := l_end_date_active; --Bug 4565156 NOCOPY changes
327 x_return_status := fnd_api.g_ret_sts_unexp_error;
328 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
329 p_procedure_name => 'VALIDATE_PROJECT_PARTY',
330 p_error_text => SUBSTRB(SQLERRM,1,240));
331 RAISE;
332
333 END VALIDATE_PROJECT_PARTY;
334
335
336 FUNCTION GET_SCHEDULED_FLAG(p_project_party_id IN NUMBER,
337 p_record_version_number IN NUMBER) RETURN VARCHAR2 IS
338 l_scheduled_flag VARCHAR2(1);
339 BEGIN
340 pa_debug.set_err_stack('Get_scheduled_flag');
341 SELECT scheduled_flag INTO l_scheduled_flag
342 FROM pa_project_parties
343 WHERE project_party_id = NVL(p_project_party_id,-999)
344 AND record_version_number = NVL(p_record_version_number,record_version_number);
345
346 pa_debug.reset_err_stack;
347 RETURN l_scheduled_flag;
348
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 fnd_message.set_name('PA','PA_NO_SCHEDULE_ALLOWED');
352 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
353 --fnd_message.set_token('FUNCTION_NAME','GET_SCHEDULED_FLAG');
354 fnd_msg_pub.ADD;
355 RETURN 'X';
356 WHEN OTHERS THEN
357 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
358 p_procedure_name => pa_debug.g_err_stack,
359 p_error_text => SUBSTRB(SQLERRM,1,240));
360 RAISE;
361 END GET_SCHEDULED_FLAG;
362
363
364 FUNCTION VALIDATE_SCHEDULE_ALLOWED(p_project_role_id IN NUMBER) RETURN VARCHAR2 IS
365 x_sch_flag VARCHAR2(1) := 'N';
366 BEGIN
367
368 pa_debug.set_err_stack('Validate_scheduled_allowed');
369 x_sch_flag := pa_role_utils.get_schedulable_flag(p_role_id => p_project_role_id);
370
371 pa_debug.reset_err_stack;
372
373 RETURN x_sch_flag;
374
375 EXCEPTION WHEN OTHERS THEN
376 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
377 p_procedure_name => pa_debug.g_err_stack,
378 p_error_text => SUBSTRB(SQLERRM,1,240));
379 RAISE;
380 END VALIDATE_SCHEDULE_ALLOWED;
381
382
383 PROCEDURE GET_PERSON_PARTY_ID(p_object_type IN VARCHAR2,
384 p_object_id IN NUMBER,
385 p_project_role_id IN NUMBER,
386 p_resource_type_id IN NUMBER DEFAULT 101,
387 p_resource_source_id IN NUMBER,
388 p_start_date_active IN DATE,
389 p_end_date_active IN DATE,
390 x_project_party_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
391 x_record_version_number OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
392 BEGIN
393 pa_debug.set_err_stack('Get_person_party_id');
394 x_project_party_id := -999;
395
396 SELECT project_party_id, record_version_number INTO x_project_party_id, x_record_version_number
397 FROM pa_project_parties
398 WHERE object_type = p_object_type
399 AND object_id = p_object_id
400 AND project_role_id = p_project_role_id
401 AND resource_type_id = p_resource_type_id
402 AND resource_source_id = p_resource_source_id
403 AND start_date_active = TRUNC(p_start_date_active)
404 AND end_date_active = TRUNC(p_end_date_active);
405
406 pa_debug.reset_err_stack;
407
408 EXCEPTION WHEN NO_DATA_FOUND THEN
409 x_project_party_id := -999;
410 WHEN OTHERS THEN
411 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
412 p_procedure_name => pa_debug.g_err_stack,
413 p_error_text => SUBSTRB(SQLERRM,1,240));
414 RAISE;
415 END GET_PERSON_PARTY_ID;
416
417
418 PROCEDURE CHECK_MANDATORY_FIELDS(p_project_Role_id IN NUMBER,
419 p_resource_type_id IN NUMBER DEFAULT 101,
420 p_resource_source_id IN NUMBER,
421 p_start_date_active IN DATE,
422 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
423 p_project_end_date IN DATE,
424 p_scheduled_flag IN VARCHAR2,
425 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
426
427 BEGIN
428 pa_debug.set_err_stack('Check_mandatory_fields');
429 IF p_project_Role_id IS NULL THEN
430 fnd_message.set_name('PA','PA_XC_NO_ROLE_TYPE');
431 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
432 --fnd_message.set_token('PROCEDURE_NAME','CHECK_MANDATORY_FIELDS');
433 fnd_msg_pub.ADD;
434 --dbms_output.put_line('PA_XC_NO_ROLE_TYPE');
435 x_error_occured := 'Y';
436 END IF;
437
438 IF p_resource_source_id IS NULL THEN
439 fnd_message.set_name('PA','PA_XC_NO_PERSON_ID');
440 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
441 --fnd_message.set_token('PROCEDURE_NAME','CHECK_MANDATORY_FIELDS');
442 fnd_msg_pub.ADD;
443 --dbms_output.put_line('PA_XC_NO_PERSON_ID');
444 x_error_occured := 'Y';
445 END IF;
446
447 IF p_start_date_active IS NULL THEN
448 fnd_message.set_name('PA','PA_START_DATE_IS_MISSING');
449 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
450 --fnd_message.set_token('PROCEDURE_NAME','CHECK_MANDATORY_FIELDS');
451 fnd_msg_pub.ADD;
452 --dbms_output.put_line('PA_START_DATE_IS_MISSING');
453 x_error_occured := 'Y';
454 END IF;
455
456 IF p_end_date_active IS NULL THEN
457 IF (p_scheduled_flag = 'Y' AND pa_project_parties_utils.validate_schedule_allowed(p_project_role_id)='Y') THEN
458 IF p_project_end_date IS NULL OR p_project_end_date < p_start_date_active THEN
459 fnd_message.set_name('PA','PA_END_DATE_IS_MISSING');
460 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
461 --fnd_message.set_token('PROCEDURE_NAME','CHECK_MANDATORY_FIELDS');
462 fnd_msg_pub.ADD;
463 x_error_occured := 'Y';
464 --dbms_output.put_line('PA_END_DATE_IS_MISSING');
465 ELSE
466 p_end_date_active := p_project_end_date;
467 END IF;
468
469 END IF;
470
471 END IF;
472 pa_debug.reset_err_stack;
473
474 END CHECK_MANDATORY_FIELDS;
475
476
477 PROCEDURE VALIDATE_DATES( p_start_date_active IN DATE,
478 p_end_date_active IN DATE,
479 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
480 BEGIN
481
482 pa_debug.set_err_stack('Validate_dates');
483 IF p_end_date_active IS NOT NULL THEN
484 IF (p_end_date_active < p_start_date_active) THEN
485 fnd_message.set_name('PA','PA_SU_INVALID_DATES');
486 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
487 --fnd_message.set_token('PROCEDURE_NAME',pa_debug.g_err_stack);
488 fnd_msg_pub.ADD;
489 x_error_occured := 'Y';
490 END IF;
491 END IF;
492 pa_debug.reset_err_stack;
493
494 END VALIDATE_DATES;
495
496
497 PROCEDURE VALIDATE_NO_OVERLAP_MANAGER( p_object_type IN VARCHAR2,
498 p_object_id IN NUMBER,
499 p_project_role_id IN NUMBER,
500 p_project_party_id IN NUMBER,
501 p_start_date_active IN DATE,
502 p_end_date_active IN DATE,
503 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
504 l_error_occured VARCHAR2(1);
505 BEGIN
506 pa_debug.set_err_stack('Validate_no_overlap_manager');
507
508 SELECT 'Y' INTO l_error_occured
509 FROM pa_project_parties
510 WHERE object_type = p_object_type
511 AND object_id = p_object_id
512 AND project_role_id = p_project_role_id
513 AND project_party_id <> NVL(p_project_party_id,-999)
514 AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
515 OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
516 OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
517 OR NVL(end_Date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
518 OR (p_start_date_active > start_date_active AND end_date_active IS NULL));
519
520 x_error_occured := l_error_occured;
521 fnd_message.set_name('PA','PA_PR_TOO_MANY_MGRS');
522 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
523 --fnd_message.set_token('PROCEDURE_NAME','VALIDATE_NO_OVERLAP_MANAGER');
524 fnd_msg_pub.ADD;
525 --dbms_output.put_line('here');
526 pa_debug.reset_err_stack;
527
528 EXCEPTION
529 WHEN NO_DATA_FOUND THEN
530 x_error_occured := 'N';
531 WHEN TOO_MANY_ROWS THEN
532 fnd_message.set_name('PA','PA_PR_TOO_MANY_MGRS');
533 fnd_msg_pub.ADD;
534 x_error_occured := 'Y';
535 WHEN OTHERS THEN
536 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
537 p_procedure_name => pa_debug.g_err_stack,
538 p_error_text => SUBSTRB(SQLERRM,1,240));
539 RAISE;
540 END VALIDATE_NO_OVERLAP_MANAGER;
541
542
543 PROCEDURE VALIDATE_PERSON_NOT_OVERLAPPED( p_object_type IN VARCHAR2,
544 p_object_id IN NUMBER,
545 p_project_role_id IN NUMBER,
546 p_project_party_id IN NUMBER,
547 p_resource_type_id IN NUMBER DEFAULT 101,
548 p_resource_source_id IN NUMBER,
549 p_start_date_active IN DATE,
550 p_end_date_active IN DATE,
551 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
552 l_error_occured VARCHAR2(1);
553 BEGIN
554 pa_debug.set_err_stack('Validate_person_not_overlapped');
555
556 SELECT 'Y' INTO l_error_occured
557 FROM pa_project_parties
558 WHERE object_type = p_object_type
559 AND object_id = p_object_id
560 AND project_role_id = p_project_role_id
561 AND resource_type_id = p_resource_type_id
562 AND resource_source_id = p_resource_source_id
563 AND project_party_id <> NVL(p_project_party_id,-999)
564 AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
565 OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
566 OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
567 OR NVL(end_date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
568 OR (p_start_date_active > start_date_active AND end_date_active IS NULL));
569
570 x_error_occured := l_error_occured;
571 --dbms_output.put_line(l_error_occured);
572 fnd_message.set_name('PA','PA_XC_TOO_MANY_OMGRS');
573 --fnd_message.set_token('PKG_NAME',to_char(p_project_role_id));
574 --fnd_message.set_token('PROCEDURE_NAME',to_char(p_resource_source_id));
575 fnd_msg_pub.ADD;
576 pa_debug.reset_err_stack;
577 EXCEPTION WHEN NO_DATA_FOUND THEN
578 x_error_occured := 'N';
579 WHEN TOO_MANY_ROWS THEN
580 fnd_message.set_name('PA','PA_XC_TOO_MANY_OMGRS');
581 fnd_msg_pub.ADD;
582 x_error_occured := 'Y';
583 WHEN OTHERS THEN
584 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
585 p_procedure_name => pa_debug.g_err_stack,
586 p_error_text => SUBSTRB(SQLERRM,1,240));
587 RAISE;
588
589 END VALIDATE_PERSON_NOT_OVERLAPPED;
590
591
592 FUNCTION get_project_role_id(p_project_role_type IN VARCHAR2,
593 p_calling_module IN VARCHAR2) RETURN NUMBER IS
594 l_project_role_id NUMBER;
595 BEGIN
596 IF p_project_role_type IS NOT NULL THEN
597 BEGIN
598 SELECT project_role_id INTO l_project_role_id
599 FROM pa_project_role_types_vl
600 WHERE (meaning = p_project_role_type AND p_calling_module <> 'FORM')
601 OR (project_role_type = p_project_role_type AND p_calling_module = 'FORM');
602 RETURN l_project_role_id;
603 EXCEPTION WHEN NO_DATA_FOUND THEN
604 fnd_message.set_name('PA','PA_XC_NO_ROLE_TYPE');
605 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
606 --fnd_message.set_token('PROCEDURE_NAME','GET_PROJECT_ROLE_ID');
607 fnd_msg_pub.ADD;
608 RETURN -999;
609 END;
610 ELSE
611 fnd_message.set_name('PA','PA_XC_NO_ROLE_TYPE');
612 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
613 --fnd_message.set_token('PROCEDURE_NAME','GET_PROJECT_ROLE_ID');
614 fnd_msg_pub.ADD;
615 RETURN -999;
616 END IF;
617 END;
618
619 FUNCTION get_resource_source_id(p_resource_name IN VARCHAR2) RETURN NUMBER IS
620 l_resource_id NUMBER;
621 BEGIN
622 IF p_resource_name IS NOT NULL THEN
623 BEGIN
624 SELECT person_id INTO l_resource_id
625 FROM pa_employees
626 WHERE full_name = p_resource_name
627 AND active = '*';
628 RETURN l_resource_id;
629 EXCEPTION WHEN NO_DATA_FOUND THEN
630 fnd_message.set_name('PA','PA_XC_NO_PERSON_ID');
631 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
632 --fnd_message.set_token('PROCEDURE_NAME','GET_RESOURCE_SOURCE_ID');
633 fnd_msg_pub.ADD;
634 RETURN -999;
635 WHEN TOO_MANY_ROWS THEN
636 fnd_message.set_name('PA','PA_TOO_MANY_PERSONS');
637 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
638 --fnd_message.set_token('PROCEDURE_NAME','GET_RESOURCE_SOURCE_ID');
639 fnd_msg_pub.ADD;
640 RETURN -999;
641 END;
642 ELSE
643 fnd_message.set_name('PA','PA_XC_NO_PERSON_ID');
644 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_UTILS');
645 --fnd_message.set_token('PROCEDURE_NAME','GET_RESOURCE_SOURCE_ID');
646 fnd_msg_pub.ADD;
647 RETURN -999;
648 END IF;
649 END;
650 -------------
651 FUNCTION ENABLE_EDIT_LINK(p_project_id IN NUMBER,
652 p_scheduled_flag IN VARCHAR2,
653 p_assignment_id IN NUMBER) RETURN VARCHAR2 IS
654
655 BEGIN
656 IF p_scheduled_flag = 'Y' THEN
657 RETURN 'S';
658 ELSE
659 RETURN 'T';
660 END IF;
661 END;
662
663 FUNCTION get_grant_id(p_project_party_id IN NUMBER) RETURN RAW IS
664 l_grant_id RAW(16);
665
666 BEGIN
667 pa_debug.set_err_stack('get_grant_id');
668 SELECT grant_id INTO l_grant_id
669 FROM pa_project_parties
670 WHERE project_party_id = p_project_party_id;
671 pa_debug.reset_err_stack;
672
673 RETURN l_grant_id;
674
675 EXCEPTION WHEN NO_DATA_FOUND THEN
676 RETURN NULL;
677 WHEN OTHERS THEN
678 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
679 p_procedure_name => pa_debug.g_err_stack,
680 p_error_text => SUBSTRB(SQLERRM,1,240));
681 RAISE;
682 END;
683
684 PROCEDURE GET_CURR_PROJ_MGR_DETAILS(p_project_id IN NUMBER,
685 x_manager_person_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
686 x_manager_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
687 x_project_party_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
688 x_project_role_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
689 x_project_role_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
690 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
691 x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
692 BEGIN
693 x_return_status := FND_API.G_RET_STS_SUCCESS;
694
695 pa_debug.set_err_stack('get_current_project_manager_details');
696
697 BEGIN
698 SELECT ppp.resource_source_id,
699 ppp.project_party_id,
700 ppp.project_role_id,
701 pprt.meaning,
702 pe.full_name
703 INTO x_manager_person_id,
704 x_project_party_id,
705 x_project_role_id,
706 x_project_role_name,
707 x_manager_name
708 FROM pa_project_parties ppp,
709 pa_project_role_types pprt,
710 per_all_people_f pe
711 WHERE ppp.project_id = p_project_id
712 AND ppp.project_role_id = 1
713 AND ppp.project_role_id = pprt.project_role_id
714 AND ppp.resource_type_id = 101
715 AND ppp.resource_source_id = pe.person_id
716 AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
717 AND pe.effective_end_date
718 AND ppp.object_type = 'PA_PROJECTS'
719 AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);
720
721 EXCEPTION WHEN NO_DATA_FOUND THEN
722 SELECT ppp.resource_source_id,
723 ppp.project_party_id,
724 ppp.project_role_id,
725 pprt.meaning,
726 pe.full_name
727 INTO x_manager_person_id,
728 x_project_party_id,
729 x_project_role_id,
730 x_project_role_name,
731 x_manager_name
732 FROM pa_project_parties ppp,
733 pa_project_role_types pprt,
734 per_all_people_f pe
735 WHERE ppp.project_id = p_project_id
736 AND ppp.project_role_id = 1
737 AND ppp.project_role_id = pprt.project_role_id
738 AND ppp.resource_type_id = 101
739 AND ppp.resource_source_id = pe.person_id
740 AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
741 AND pe.effective_end_date
742 AND ppp.object_type = 'PA_PROJECTS'
743 AND ppp.start_date_active > TRUNC(SYSDATE)
744 AND ppp.start_date_active = (SELECT MIN(ppp1.start_date_active)
745 FROM pa_project_parties ppp1
746 WHERE ppp1.project_id = p_project_id
747 AND ppp1.project_role_id = 1
748 AND ppp1.start_date_active > TRUNC(SYSDATE));
749 END;
750
751 pa_debug.reset_err_stack;
752 EXCEPTION
753 WHEN NO_DATA_FOUND THEN
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 x_error_message_code := 'PA_NO_PROJ_MGR_EXISTS';
756 WHEN OTHERS THEN
757 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
758 p_procedure_name => pa_debug.g_err_stack,
759 p_error_text => SUBSTRB(SQLERRM,1,240));
760 RAISE;
761 END GET_CURR_PROJ_MGR_DETAILS;
762
763
764 FUNCTION get_customer_project_party_id (
765 p_project_id IN NUMBER,
766 p_customer_id IN NUMBER) RETURN NUMBER
767
768 IS
769 ret NUMBER;
770 BEGIN
771 SELECT project_party_id
772 INTO ret
773 FROM pa_project_parties p,
774 pa_project_role_types_b r,
775 pa_customers_v c
776 WHERE r.role_party_class = 'CUSTOMER'
777 AND p.project_role_id = r.project_role_id
778 AND p.project_id = p_project_id
779 AND p.resource_type_id = 112
780 AND c.party_id = p.resource_source_id
781
782 AND c.customer_id = p_customer_id;
783
784 RETURN ret;
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787 RETURN NULL;
788 END get_customer_project_party_id;
789
790 PROCEDURE VALIDATE_ROLE_PARTY( p_project_role_id IN NUMBER,
791 p_resource_type_id IN NUMBER DEFAULT 101,
792 p_resource_source_id IN NUMBER,
793 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
794
795 l_party_type VARCHAR2(30) := 'PERSON';
796 l_role_party_class pa_project_role_types_b.role_party_class%TYPE;
797 BEGIN
798 pa_debug.set_err_stack('Validate_role_party');
799
800 -- Check if the resource is a Person/Organization.
801 IF p_resource_type_id = 112 THEN
802 SELECT party_type
803 INTO l_party_type
804 FROM hz_parties
805 WHERE party_id = p_resource_source_id;
806 END IF;
807
808 -- Check if the role is for Person/Organization.
809 SELECT role_party_class
810 INTO l_role_party_class
811 FROM pa_project_role_types_b
812 WHERE project_role_id = p_project_role_id;
813
814 x_error_occured := 'N';
815
816 IF (l_role_party_class = 'PERSON' AND l_party_type <> 'PERSON') OR
817 (l_role_party_class <> 'PERSON' AND l_party_type = 'PERSON') THEN
818 fnd_message.set_name('PA','PA_XC_NO_ROLE_TYPE');
819 fnd_msg_pub.ADD;
820 x_error_occured := 'Y';
821 END IF;
822
823 pa_debug.reset_err_stack;
824 EXCEPTION
825 WHEN OTHERS THEN
826 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_UTILS',
827 p_procedure_name => pa_debug.g_err_stack,
828 p_error_text => SUBSTRB(SQLERRM,1,240));
829 RAISE;
830 END VALIDATE_ROLE_PARTY;
831
832
833 FUNCTION GET_PROJECT_MANAGER( p_project_id IN NUMBER)
834 RETURN NUMBER
835 IS
836
837 CURSOR C1 (c_as_of_date DATE) IS
838 Select PPP.RESOURCE_SOURCE_ID
839 FROM PA_PROJECT_PARTIES PPP ,
840 --PA_PROJECT_ROLE_TYPES PPRT --bug 4004821
841 PA_PROJECT_ROLE_TYPES_B PPRT
842 WHERE
843 PPP.PROJECT_ID = p_project_id
844 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
845 AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
846 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
847 AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
848 AND NVL(trunc(PPP.end_date_active),c_as_of_date);
849
850 l_return_value NUMBER(10);
851 l_project_finish_date DATE;
852 l_project_start_date DATE;
853
854 BEGIN
855
856 PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID := null;
857
858 l_project_finish_date := PA_PROJECT_DATES_UTILS.Get_Project_Finish_Date(p_project_id);
859
860 -- return project manager as of project finish date for past projects
861 IF l_project_finish_date < sysdate THEN
862 OPEN C1 (l_project_finish_date);
863 FETCH C1 INTO l_return_value;
864 CLOSE C1;
865 ELSE
866 -- Bug 4361712
867 l_project_start_date := PA_PROJECT_DATES_UTILS.Get_Project_Start_Date(p_project_id);
868
869 -- return project manager as of project start date for future projects
870 IF l_project_start_date > sysdate THEN
871 OPEN C1 (l_project_start_date);
872 FETCH C1 INTO l_return_value;
873 CLOSE C1;
874 -- return project manager as of today for current projects
875 ELSE
876 OPEN C1 (sysdate);
877 FETCH C1 INTO l_return_value;
878 CLOSE C1;
879 END IF;
880
881 END IF;
882
883 PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID := l_return_value;
884 RETURN l_return_value;
885
886 END;
887
888 FUNCTION GET_PROJECT_MANAGER_NAME
889 RETURN VARCHAR2
890 IS
891
892 CURSOR C1(c_person_id NUMBER)
893 IS
894 Select full_name
895 FROM per_all_people_f
896 WHERE PERSON_ID = c_person_id
897 AND trunc(SYSDATE) between trunc(effective_start_date) and trunc(effective_end_date); -- Bug 3283351
898
899 l_return_value VARCHAR2(250);
900 BEGIN
901
902 IF PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID is not null THEN
903
904 OPEN C1(PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID);
905 FETCH C1 INTO l_return_value;
906 CLOSE C1;
907 END IF;
908
909 RETURN l_return_value;
910 END;
911
912 FUNCTION GET_PROJECT_MANAGER_NAME( p_project_id IN NUMBER)
913 RETURN VARCHAR2
914 IS
915
916 CURSOR C1 (c_as_of_date DATE) IS
917 Select ppf.full_name
918 FROM PA_PROJECT_PARTIES PPP,
919 --PA_PROJECT_ROLE_TYPES PPRT, --bug 4004821
920 PA_PROJECT_ROLE_TYPES_B PPRT,
921 per_all_people_f PPF
922 WHERE
923 PPP.PROJECT_ID = p_project_id
924 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
925 AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
926 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
927 AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
928 AND NVL(trunc(PPP.end_date_active),c_as_of_date)
929 AND ppf.person_id = ppp.resource_source_id
930 AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date); -- Added for bug 3283351
931
932 l_return_value VARCHAR2(250);
933 l_project_finish_date DATE;
934 l_project_start_date DATE;
935
936 BEGIN
937
938 l_project_finish_date := PA_PROJECT_DATES_UTILS.Get_Project_Finish_Date(p_project_id);
939
940 -- return project manager as of project finish date for past projects
941 IF l_project_finish_date < sysdate THEN
942 OPEN C1 (l_project_finish_date);
943 FETCH C1 INTO l_return_value;
944 CLOSE C1;
945 ELSE
946
947 -- 4361712
948 l_project_start_date := PA_PROJECT_DATES_UTILS.Get_Project_Start_Date(p_project_id);
949
950 -- return project manager as of project start date for future projects
951 IF l_project_start_date > sysdate THEN
952 OPEN C1 (l_project_start_date);
953 FETCH C1 INTO l_return_value;
954 CLOSE C1;
955 -- return project manager as of today for current projects
956 ELSE
957 OPEN C1 (sysdate);
958 FETCH C1 INTO l_return_value;
959 CLOSE C1;
960 END IF;
961
962 END IF;
963
964 RETURN l_return_value;
965 END;
966
967 /* Added the following API for bug #2111806.
968 This API will check if a Project Manager is available for the entire
969 duration of a Project.
970 */
971
972 PROCEDURE VALIDATE_MANAGER_DATE_RANGE( p_mode IN VARCHAR2,
973 p_project_id IN NUMBER,
974 x_start_no_mgr_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
975 x_end_no_mgr_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
976 x_error_occured OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
977 l_error_occured VARCHAR2(50);
978 l_proj_status pa_projects_all.project_status_code%TYPE;
979 l_proj_type_class pa_project_types_all.project_type_class_code%TYPE;
980 l_proj_type pa_project_types_all.project_type%TYPE; -- Added for bug#5098966
981 l_proj_start_date pa_projects_all.start_date%TYPE;
982 l_proj_end_date pa_projects_all.completion_date%TYPE;
983 l_person_id pa_project_players.person_id%TYPE;
984 l_party_id pa_project_parties.project_party_id%TYPE;
985 l_start_date pa_project_players.start_date_active%TYPE;
986 l_end_date pa_project_players.end_date_active%TYPE;
987 l_end_date_old pa_project_players.end_date_active%TYPE;
988
989 /* Changing the query to base table to improve performance instead of pa_projects_v view. Bug no. 4940945*/
990 CURSOR c_prj IS
991 select ps.project_system_status_code
992 ,type.project_type_class_code
993 ,proj.project_type -- Added for bug#5098966
994 ,trunc(proj.start_date)
995 ,trunc(proj.completion_date)
996 from pa_projects_all proj
997 ,pa_project_statuses ps
998 ,pa_project_types type
999 where proj.project_status_code = ps.project_status_code
1000 and proj.project_type = type.project_type
1001 and proj.project_id = p_project_id
1002 and ps.STATUS_TYPE = 'PROJECT';
1003
1004 /* changes end for Bug 4940945 */
1005
1006 /* commented by sunkalya for fix bug#4636169
1007 CURSOR c_project_players IS
1008 select person_id
1009 ,start_date_active
1010 ,end_date_active
1011 from pa_project_players
1012 where project_id = p_project_id
1013 and project_role_type='PROJECT MANAGER'
1014 order by start_date_active;
1015
1016 CURSOR c_project_parties IS
1017 select project_party_id
1018 ,start_date_active
1019 ,end_date_active
1020 from pa_project_parties
1021 where project_id = p_project_id
1022 and project_role_id = 1
1023 order by start_date_active;
1024 */ --End of commenting by sunkalya for bug#4636169
1025
1026 BEGIN
1027
1028 pa_debug.set_err_stack('Validate Manager Date Range');
1029
1030 IF p_project_id IS NOT NULL THEN
1031 OPEN c_prj;
1032 -- Added l_proj_type for bug#5098966
1033 FETCH c_prj INTO l_proj_status, l_proj_type_class, l_proj_type, l_proj_start_date, l_proj_end_date;
1034 CLOSE c_prj;
1035 END IF;
1036
1037 -- begin changes for bug 4636169 by sunkalya
1038 DECLARE
1039 CURSOR c_project_players IS
1040 select person_id
1041 ,trunc(start_date_active)
1042 ,trunc(end_date_active)
1043 from pa_project_players
1044 where project_id = p_project_id
1045 and project_role_type='PROJECT MANAGER'
1046 AND NOT (
1047 exists(
1048 SELECT 'y' FROM dual WHERE trunc(start_date_active) > trunc(l_proj_end_date)
1049 )
1050 OR
1051 exists(
1052 SELECT 'y' FROM dual WHERE trunc(end_date_active) < trunc(l_proj_start_date)
1053 )
1054 )
1055 order by start_date_active;
1056
1057 CURSOR c_project_parties IS
1058 select project_party_id
1059 ,trunc(start_date_active)
1060 ,trunc(end_date_active)
1061 from pa_project_parties
1062 where project_id = p_project_id
1063 and project_role_id = 1
1064 AND NOT (
1065 exists(
1066 SELECT 'y' FROM dual WHERE trunc(start_date_active) > trunc(l_proj_end_date)
1067 )
1068 OR
1069 exists(
1070 SELECT 'y' FROM dual WHERE trunc(end_date_active) < trunc(l_proj_start_date)
1071 )
1072 )
1073 order by start_date_active;
1074
1075 BEGIN --This begin added for Bug#4636169 by sunkalya
1076
1077 /* Do the checking for the Manager date only for an Approved Contract Type project. */
1078 IF (nvl(l_proj_type_class,'NONE') = 'CONTRACT' AND
1079 nvl(l_proj_status,'UNAPPROVED') = 'APPROVED') AND
1080 nvl(l_proj_type, 'AWARD_PROJECT') <> 'AWARD_PROJECT' THEN -- Added for bug#5098966
1081
1082
1083 OPEN c_project_players;
1084 OPEN c_project_parties;
1085 LOOP
1086 IF p_mode = 'AMG' THEN
1087 IF P_DEBUG_MODE = 'Y' THEN
1088 pa_debug.write('VALIDATE_MANAGER_DATE_RANGE: ','Mode is AMG',3 );
1089 END IF;
1090 FETCH c_project_players INTO l_person_id,l_start_date,l_end_date;
1091 ELSIF p_mode = 'SS' THEN
1092 IF P_DEBUG_MODE = 'Y' THEN
1093 pa_debug.write('VALIDATE_MANAGER_DATE_RANGE: ','Mode is SS',3 );
1094 END IF;
1095 FETCH c_project_parties INTO l_party_id,l_start_date,l_end_date;
1096 END IF;
1097
1098 IF (l_proj_end_date is null) THEN
1099
1100 IF (( p_mode = 'AMG' and c_project_players%NOTFOUND and l_end_date_old is not null) OR
1101 ( p_mode = 'SS' and c_project_parties%NOTFOUND and l_end_date_old is not null))THEN
1102 x_start_no_mgr_date := l_end_date_old+1;
1103 x_end_no_mgr_date := l_proj_end_date;
1104 l_error_occured := 'PA_PR_NO_MGR_DATE_RANGE';
1105 EXIT;
1106 END IF;
1107 IF (l_start_date > l_proj_start_date) THEN
1108 x_start_no_mgr_date := l_proj_start_date;
1109 x_end_no_mgr_date := l_start_date-1;
1110 l_error_occured := 'PA_PR_NO_MGR_DATE_RANGE';
1111 EXIT;
1112 ELSE
1113 IF (l_end_date is null) THEN
1114 EXIT ;
1115 ELSE
1116 l_proj_start_date :=l_end_date + 1;
1117 l_end_date_old :=l_end_date;
1118 END IF;
1119 END IF;
1120 END IF;
1121
1122 IF (l_proj_end_date is not null) THEN
1123 IF (( p_mode = 'AMG' and c_project_players%NOTFOUND and l_end_date_old <> l_proj_end_date) OR
1124 ( p_mode = 'SS' and c_project_parties%NOTFOUND and l_end_date_old <> l_proj_end_date)) THEN
1125 x_start_no_mgr_date := l_end_date_old+1;
1126 x_end_no_mgr_date := l_proj_end_date;
1127 l_error_occured := 'PA_PR_NO_MGR_DATE_RANGE';
1128 EXIT;
1129 END IF;
1130 --bug 9543613 - start
1131 IF (l_start_date is null and l_end_date is null) THEN
1132 x_start_no_mgr_date := l_proj_start_date;
1133 x_end_no_mgr_date := l_proj_end_date;
1134 l_error_occured := 'PA_PR_NO_MGR_DATE_RANGE';
1135 EXIT;
1136 ELSE
1137 --bug 9543613 - end
1138 IF (l_start_date > l_proj_start_date) THEN
1139 x_start_no_mgr_date := l_proj_start_date;
1140 x_end_no_mgr_date := l_start_date-1;
1141 l_error_occured := 'PA_PR_NO_MGR_DATE_RANGE';
1142 EXIT;
1143 ELSE
1144 IF (l_end_date is null or l_end_date >=l_proj_end_date) THEN
1145 EXIT ;
1146 ELSE
1147 IF (l_end_date <l_proj_end_date) THEN
1148 l_proj_start_date :=l_end_date + 1;
1149 l_end_date_old := l_end_date;
1150 END IF;
1151 END IF;
1152 END IF;
1153 --bug 9543613 - start
1154 END IF;
1155 --bug 9543613 - end
1156 END IF;
1157 END LOOP;
1158
1159 CLOSE c_project_players;
1160 CLOSE c_project_parties;
1161 END IF;
1162
1163 IF P_DEBUG_MODE = 'Y' THEN
1164 pa_debug.write('VALIDATE_MANAGER_DATE_RANGE: ','l_error_occured - '||l_error_occured,3 );
1165 END IF;
1166 x_error_occured := l_error_occured;
1167 pa_debug.reset_err_stack;
1168
1169 EXCEPTION
1170 WHEN NO_DATA_FOUND THEN
1171 x_error_occured := 'N';
1172 WHEN OTHERS THEN
1173 x_error_occured := 'Y';
1174 IF c_project_players%ISOPEN THEN
1175 CLOSE c_project_players;
1176 END IF;
1177 IF c_project_parties%ISOPEN THEN
1178 CLOSE c_project_parties;
1179 END IF;
1180 fnd_msg_pub.add_exc_msg(p_pkg_name => 'VALIDATE_MANAGER_DATE_RANGE',
1181 p_procedure_name => pa_debug.g_err_stack,
1182 p_error_text => SUBSTRB(SQLERRM,1,240));
1183 RAISE;
1184
1185 END; --This END is for the BEGIN added by sunkalya for Bug#4636169
1186 END VALIDATE_MANAGER_DATE_RANGE;
1187
1188
1189 /* Added the following API for bug #2111806.
1190 This API will check if atleast one Project Manager exists
1191 for the Project if it is an Approved Contract Project.
1192 */
1193
1194 PROCEDURE VALIDATE_ONE_MANAGER_EXISTS( p_project_id IN NUMBER,
1195 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1196 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1197 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1198 /* Changing the query to base tables instaed of pa_projects_v view to improve perfromance. Bug 4940945 */
1199 CURSOR c_prj IS
1200 select ps.project_system_status_code
1201 ,type.project_type_class_code
1202 from pa_projects_all proj
1203 ,pa_project_statuses ps
1204 ,pa_project_types type
1205 where proj.project_status_code = ps.project_status_code
1206 and proj.project_type=type.project_type
1207 and proj.project_id = p_project_id
1208 and ps.STATUS_TYPE ='PROJECT';
1209 /* changes end for Bug 4940945 */
1210
1211 CURSOR c_prj_count IS
1212 select count(*)
1213 from pa_project_parties
1214 where project_id = p_project_id
1215 and project_role_id = 1;
1216
1217 l_proj_status pa_projects_all.project_status_code%TYPE;
1218 l_proj_type_class pa_project_types_all.project_type_class_code%TYPE;
1219 l_prj_mgr_count NUMBER := 0;
1220 l_start_no_mgr_date DATE;
1221 l_end_no_mgr_date DATE;
1222
1223
1224 BEGIN
1225
1226 pa_debug.set_err_stack('VALIDATE_ONE_MANAGER_EXISTS');
1227 x_msg_count := 0;
1228 x_return_status := FND_API.G_RET_STS_SUCCESS;
1229
1230 /* Do the checking for the Manager date only for an Approved Contract Type project. */
1231 IF p_project_id IS NOT NULL THEN
1232 OPEN c_prj;
1233 FETCH c_prj INTO l_proj_status, l_proj_type_class;
1234 CLOSE c_prj;
1235 END IF;
1236
1237 /* The check has to be done only for an Approved Contract Type project. */
1238 IF (nvl(l_proj_type_class,'NONE') = 'CONTRACT' AND
1239 nvl(l_proj_status,'UNAPPROVED') = 'APPROVED') THEN
1240 IF P_DEBUG_MODE = 'Y' THEN
1241 pa_debug.write('VALIDATE_ONE_MANAGER_EXISTS: ','This is an Approved Contract Project',3 );
1242 END IF;
1243 OPEN c_prj_count;
1244 FETCH c_prj_count INTO l_prj_mgr_count;
1245 CLOSE c_prj_count;
1246 IF l_prj_mgr_count = 0 THEN
1247 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1248 pa_utils.add_message
1249 ( p_app_short_name => 'PA'
1250 ,p_msg_name => 'PA_PR_INSUF_PROJ_MGR'
1251 );
1252 x_return_status := FND_API.G_RET_STS_ERROR;
1253 x_msg_count := 1;
1254 x_msg_data := 'PA_PR_INSUF_PROJ_MGR';
1255 END IF;
1256 END IF;
1257 END IF;
1258 pa_debug.reset_err_stack;
1259
1260 EXCEPTION
1261
1262 WHEN OTHERS THEN
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1264 x_msg_count := 1;
1265 x_msg_data := SQLERRM;
1266 FND_MSG_PUB.add_exc_msg
1267 ( p_pkg_name => 'PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS'
1268 ,p_procedure_name => pa_debug.G_Err_Stack );
1269 IF P_DEBUG_MODE = 'Y' THEN
1270 pa_debug.write('VALIDATE_ONE_MANAGER_EXISTS: ', SQLERRM, 3);
1271 pa_debug.write('VALIDATE_ONE_MANAGER_EXISTS: ', pa_debug.G_Err_Stack, 3);
1272 END IF;
1273 pa_debug.reset_err_stack;
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275
1276 END VALIDATE_ONE_MANAGER_EXISTS;
1277
1278 -- API name : get_current_project_manager
1279 -- Type : Public
1280 -- Pre-reqs : None.
1281 -- Parameters :
1282 -- p_project_id IN NUMBER
1283 -- Return : NUMBER
1284 -- Details: This function is created so as to return the project manager who is
1285 -- active on the project as on the sysdate.
1286 -- History
1287 --
1288 -- 23-May-2005 adarora - Created
1289 --
1290 FUNCTION GET_CURRENT_PROJECT_MANAGER ( p_project_id IN NUMBER)
1291 RETURN NUMBER
1292 IS
1293
1294 CURSOR C1 (c_as_of_date DATE) IS
1295 Select PPP.RESOURCE_SOURCE_ID
1296 FROM PA_PROJECT_PARTIES PPP ,
1297 --PA_PROJECT_ROLE_TYPES PPRT --bug 4004821
1298 PA_PROJECT_ROLE_TYPES_B PPRT
1299 WHERE
1300 PPP.PROJECT_ID = p_project_id
1301 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1302 AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
1303 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1304 AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
1305 AND NVL(trunc(PPP.end_date_active),c_as_of_date);
1306
1307 l_return_value NUMBER(10);
1308 BEGIN
1309
1310 PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID := null;
1311
1312 OPEN C1 (sysdate);
1313 FETCH C1 INTO l_return_value;
1314 CLOSE C1;
1315
1316
1317 PA_PROJECT_PARTIES_UTILS.G_PROJECT_MANAGER_ID := l_return_value;
1318 RETURN l_return_value;
1319
1320 END GET_CURRENT_PROJECT_MANAGER;
1321
1322 -- API name : GET_CURRENT_PROJ_MANAGER_NAME
1323 -- Type : Public
1324 -- Pre-reqs : None.
1325 -- Parameters :
1326 -- p_project_id IN NUMBER
1327 -- Return : VARCHAR2
1328 -- Details: This function is created so as to return the project manager name who is
1329 -- active on the project as on the sysdate.
1330 -- History
1331 --
1332 -- 23-May-2005 adarora - Created
1333 --
1334
1335 FUNCTION GET_CURRENT_PROJ_MANAGER_NAME( p_project_id IN NUMBER)
1336 RETURN VARCHAR2
1337 IS
1338
1339 CURSOR C1 (c_as_of_date DATE) IS
1340 Select ppf.full_name
1341 FROM PA_PROJECT_PARTIES PPP,
1342 --PA_PROJECT_ROLE_TYPES PPRT, --bug 4004821
1343 PA_PROJECT_ROLE_TYPES_B PPRT,
1344 per_all_people_f PPF
1345 WHERE
1346 PPP.PROJECT_ID = p_project_id
1347 AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
1348 AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
1349 AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
1350 AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
1351 AND NVL(trunc(PPP.end_date_active),c_as_of_date)
1352 AND ppf.person_id = ppp.resource_source_id
1353 AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date) -- Added for bug 3283351
1354 and ppp.object_type = 'PA_PROJECTS' --Added for Bug 10071170
1355 and ppp.object_id = p_project_id; --Added for Bug 10071170
1356
1357 l_return_value VARCHAR2(250);
1358
1359 BEGIN
1360
1361
1362
1363 OPEN C1 (sysdate);
1364 FETCH C1 INTO l_return_value;
1365 CLOSE C1;
1366
1367 RETURN l_return_value;
1368 END GET_CURRENT_PROJ_MANAGER_NAME;
1369
1370 END PA_PROJECT_PARTIES_UTILS;
1371
[Home] [Help]