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