[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_PARTIES_PVT
Source
1 package body PA_PROJECT_PARTIES_PVT as
2 /* $Header: PARPPUTB.pls 120.12.12010000.3 2008/11/30 08:49:36 jcgeorge ship $ */
3
4 P_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 PROCEDURE CREATE_PROJECT_PARTY( p_commit IN VARCHAR2 := FND_API.G_FALSE,
7 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
8 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
9 p_debug_mode IN VARCHAR2 default 'N',
10 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
11 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
12 p_resource_type_id IN NUMBER := 101,
13 p_project_role_id IN NUMBER,
14 p_resource_source_id IN NUMBER,
15 p_start_date_active IN DATE,
16 p_scheduled_flag IN VARCHAR2 := 'N',
17 p_calling_module IN VARCHAR2,
18 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
19 p_project_end_date IN DATE,
20 p_mgr_validation_type IN VARCHAR2 := FND_API.G_MISS_CHAR,/*Added for bug 2111806*/
21 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
22 x_project_party_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
24 x_assignment_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_wf_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
26 x_wf_item_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
27 x_wf_process OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
29 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
30 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
31
32 l_error_occured VARCHAR2(50) := 'N';
33 x_call_overlap VARCHAR2(1) := 'Y';
34 x_assignment_action VARCHAR2(20) := 'NOACTION';
35 l_resource_id NUMBER;
36 l_record_version_number NUMBER := 1;
37 l_project_party_id NUMBER;
38 l_project_id NUMBER;
39 l_grant_id RAW(16); ---------NUMBER;
40 x_assignment_number NUMBER;
41 x_assignment_row_id ROWID;
42 l_unfilled_assignment_id NUMBER;
43 l_valid VARCHAR2(1) := 'N';
44 l_error_msg_code VARCHAR2(255);
45 l_proj_role_name VARCHAR2(80);
46 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
47 l_source_type VARCHAR2(8) := 'PERSON';
48 l_job_schedulable VARCHAR2(1) := 'N';
49 l_date DATE;
50 l_is_valid VARCHAR2(1) := 'N'; /* Added for bug 3234293 */
51 l_return_status VARCHAR2(1000); /* Added for bug 3234293 */
52
53 /* Start of code for bug #2111806 */
54 l_start_no_mgr_date DATE;
55 l_end_no_mgr_date DATE;
56 l_msg_count NUMBER;
57 l_msg_data VARCHAR2(1000);
58 /* End of code for bug #2111806 */
59 l_msg_index_out NUMBER; --Bug 5186830
60 l_data VARCHAR2(2000); --Bug 5186830
61
62 l_is_valid_denorm VARCHAR2(1) := 'N';/* Added for bug 6077424 */
63
64 BEGIN
65 if p_commit = FND_API.G_TRUE then
66 savepoint project_parties;
67 end if;
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 if p_project_id = FND_API.G_MISS_NUM or p_project_id is null then
71 l_project_id := null;
72 else
73 l_project_id := p_project_id;
74 end if;
75
76
77 if p_validation_level > 0 then
78 if (p_debug_mode = 'Y') then
79 IF P_DEBUG_MODE = 'Y' THEN
80 pa_debug.debug('Create_project_party: Calling validate_project_party.');
81 END IF;
82 end if;
83 pa_debug.G_err_stage := 'Calling validate_project_party';
84 pa_project_parties_utils.validate_project_party(
85 p_validation_level,
86 p_debug_mode,
87 p_object_id,
88 p_OBJECT_TYPE,
89 p_project_role_id,
90 p_resource_type_id,
91 p_resource_source_id,
92 p_start_date_active,
93 NVL(p_scheduled_flag, 'N'),
94 l_record_version_number,
95 p_calling_module,
96 'INSERT',
97 l_project_id,
98 p_project_end_date,
99 p_end_date_active,
100 l_project_party_id,
101 x_call_overlap,
102 x_assignment_action,
103 x_return_status);
104 end if;
105
106 --dbms_output.put_line('return :'||x_return_status);
107 --dbms_output.put_line('project_role_id :'||to_char(p_project_role_id));
108
109 If x_return_status = FND_API.G_RET_STS_SUCCESS and not(fnd_api.to_boolean(nvl(p_validate_only,FND_API.G_FALSE))) then
110 --if pa_install.is_prm_licensed() = 'Y' then
111
112 --MT: OrgRole changes
113 IF p_resource_type_id=101 THEN
114
115 /* Added for bug 3234293 - We check if resource exists, call resource pull only if resource does not exist */
116 pa_resource_utils.check_res_exists(
117 P_PERSON_ID => p_resource_source_id,
118 X_VALID => l_is_valid,
119 X_RETURN_STATUS => l_return_status);
120
121 /* Added for bug 6077424*/
122 BEGIN
123 SELECT 'Y' INTO l_is_valid_denorm FROM pa_resources_denorm
124 WHERE person_id = p_resource_source_id
125 AND rownum=1;
126 EXCEPTION
127 WHEN NO_DATA_FOUND THEN
128 l_is_valid_denorm := 'N';
129
130 END;
131 /* End for bug 6077424*/
132
133 -- IF (nvl(p_scheduled_flag, 'N') = 'Y' OR l_is_valid <> 'Y' ) THEN -- 6077424
134 IF ((nvl(p_scheduled_flag, 'N') = 'Y' AND (l_is_valid <> 'Y' OR l_is_valid_denorm = 'N') ))
135 or l_is_valid <> 'Y' THEN /* Added for bug 6077424, Changed for bug 6398283*/
136
137
138 /* End of code added for bug 3234293 */
139
140 pa_debug.G_err_stage := 'Calling create_resource';
141 pa_r_project_resources_pub.create_resource(p_api_version => 1.0,
142 p_init_msg_list => fnd_api.g_false,
143 p_commit => p_commit,
144 p_validate_only => p_validate_only,
145 p_person_id => p_resource_source_id,
146 p_internal => 'Y',
147 p_individual => 'Y',
148 p_check_resource => 'Y',
149 p_resource_type => 'EMPLOYEE',
150 P_SCHEDULED_MEMBER_FLAG => NVL(p_scheduled_flag, 'N'),
151 P_START_DATE => p_start_date_active, -- Bug 5337454
152 x_return_status => x_return_status,
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data,
155 x_resource_id => l_resource_id);
156 --Bug 5186830
157 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
158 RAISE FND_API.G_EXC_ERROR;
159 END IF;
160 /* Added for bug 3234293 - Added this else condition to get the resource id */
161 ELSE
162
163 SELECT resource_id
164 INTO l_resource_id
165 FROM pa_resource_txn_attributes
166 WHERE person_id = p_resource_source_id
167 and rownum=1;
168
169 END IF;
170 /* End of code added for bug 3234293 */
171
172 ELSIF p_resource_type_id=112 THEN
173
174 l_source_type := 'HZ_PARTY';
175
176 /* Added for bug 3234293 - We check if resource exists, call resource pull only if resource does not exist */
177
178 pa_resource_utils.check_res_exists(
179 P_PARTY_ID => p_resource_source_id,
180 X_VALID => l_is_valid,
181 X_RETURN_STATUS => l_return_status);
182
183 IF ((nvl(p_scheduled_flag, 'N') = 'Y') OR (l_is_valid <> 'Y')) THEN
184
185 /* End of code added for bug 3234293 */
186
187 pa_debug.G_err_stage := 'Calling create_resource';
188 pa_r_project_resources_pub.create_resource(p_api_version => 1.0,
189 p_init_msg_list => fnd_api.g_false,
190 p_commit => p_commit,
191 p_validate_only => p_validate_only,
192 p_party_id => p_resource_source_id,
193 p_internal => 'N',
194 p_individual => 'Y',
195 p_resource_type => 'HZ_PARTY',
196 x_return_status => x_return_status,
197 x_msg_count => x_msg_count,
198 x_msg_data => x_msg_data,
199 x_resource_id => l_resource_id);
200 --Bug 5186830
201 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
202 RAISE FND_API.G_EXC_ERROR;
203 END IF;
204 /* Added for bug 3234293 - Added this else condition to get the resource id */
205
206 ELSE
207
208 select resource_id into l_resource_id
209 from pa_resource_txn_attributes
210 where party_id = p_resource_source_id
211 and rownum=1;
212
213 END IF;
214
215 /* End of code added for bug 3234293 */
216
217 END IF;
218 --MT: End OrgRole changes
219
220 if (x_return_status = FND_API.G_RET_STS_SUCCESS and p_scheduled_flag = 'Y') then
221 pa_resource_utils.CHECK_RES_BELONGS_EXPORG(p_resource_id => l_resource_id,
222 x_valid => l_valid,
223 x_return_status => x_return_status,
224 x_error_message_code => l_error_msg_code);
225 if (l_valid <> 'Y') then
226 -- check that the person is allowed to have schedule
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 fnd_message.set_name('PA','PA_NO_SCHEDULABLE_PERSON');
229 fnd_msg_pub.add();
230 end if;
231
232 -- if the res belongs to the expenditure org hierarchy, check if the res's
233 -- job is schedulable
234
235 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
236 l_date :=pa_resource_utils.Get_Resource_Effective_Date(p_resource_id => l_resource_id);
237 l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
238 ( p_person_id => p_resource_source_id
239 ,p_date => l_date );
240 IF l_job_schedulable <> 'Y' THEN
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 fnd_message.set_name('PA','PA_NOT_SCHEDULABLE_JOB');
243 fnd_msg_pub.add();
244 END IF;
245 END IF;
246 end if;
247
248
249 if x_call_overlap = 'N' and x_return_status = FND_API.G_RET_STS_SUCCESS then
250 -- call update api
251 if (p_debug_mode = 'Y') then
252 IF P_DEBUG_MODE = 'Y' THEN
253 pa_debug.debug('Create_project_party: Calling update_row.');
254 END IF;
255 end if;
256
257 x_project_party_id := l_project_party_id;
258 x_resource_id := l_resource_id;
259
260 --dbms_output.put_line('calling update');
261 pa_debug.G_err_stage := 'Calling update_row from create_project_party';
262 PA_PROJECT_PARTIES_PKG.UPDATE_ROW (
263 X_PROJECT_PARTY_ID => l_project_party_id,
264 X_PROJECT_ID => l_project_id,
265 X_RESOURCE_SOURCE_ID => p_resource_source_id,
266 X_RESOURCE_TYPE_ID => p_resource_type_id,
267 X_PROJECT_ROLE_ID => p_project_role_id,
268 X_START_DATE_ACTIVE => trunc(p_start_date_active),
269 X_END_DATE_ACTIVE => trunc(p_end_date_active),
270 X_GRANT_ID => null,
271 X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
272 X_RECORD_VERSION_NUMBER => l_record_version_number,
273 X_LAST_UPDATE_DATE => sysdate,
274 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
275 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
276 X_RETURN_STATUS => x_return_status);
277 if (x_return_status = 'N') then
278 if p_calling_module = 'FORM' then
279 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
280 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
281 --fnd_message.set_token('PROCEDURE_NAME','UPDATE_ROW');
282 fnd_msg_pub.add;
283 else
284 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
285 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
286 --fnd_message.set_token('PROCEDURE_NAME','UPDATE_ROW');
287 fnd_msg_pub.add;
288
289 end if;
290 end if;
291
292 elsif x_return_status = FND_API.G_RET_STS_SUCCESS then
293
294 pa_security_pvt.grant_role(
295 p_project_role_id => p_project_role_id,
296 p_object_name => p_object_type,
297 p_object_key => p_object_id,
298 p_instance_type => 'SET',
299 p_party_id => p_resource_source_id,
300 p_source_type => l_source_type,
301 x_grant_guid => l_grant_id,
302 x_return_status => x_return_status,
303 x_msg_count => x_msg_count,
304 x_msg_data => x_msg_data);
305 l_grant_id := null;
306
307 if x_return_status = FND_API.G_RET_STS_SUCCESS then
308
309 x_resource_id := l_resource_id;
310
311 if (p_debug_mode = 'Y') then
312 IF P_DEBUG_MODE = 'Y' THEN
313 pa_debug.debug('Create_project_party: Calling insert_row.');
314 END IF;
315 end if;
316 pa_debug.G_err_stage := 'Calling insert_row';
317 ----dbms_output.put_line('calling insert');
318 PA_PROJECT_PARTIES_PKG.INSERT_ROW (
319 X_PROJECT_PARTY_ID => x_project_party_id,
320 X_OBJECT_ID => p_object_id,
321 X_OBJECT_TYPE => p_object_type,
322 X_PROJECT_ID => l_project_id,
323 X_RESOURCE_ID => l_resource_id,
324 X_RESOURCE_TYPE_ID => p_resource_type_id,
325 X_RESOURCE_SOURCE_ID => p_resource_source_id,
326 X_PROJECT_ROLE_ID => p_project_role_id,
327 X_START_DATE_ACTIVE => trunc(p_start_date_active),
328 X_END_DATE_ACTIVE => trunc(p_end_date_active),
329 X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
330 X_GRANT_ID => l_grant_id,
331 X_CREATED_BY => FND_GLOBAL.USER_ID,
332 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
333 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID) ;
334 end if;
335 end if;
336
337 if x_return_status = FND_API.G_RET_STS_SUCCESS and x_assignment_action = 'CREATE' and p_calling_module = 'PROJECT_MEMBER' then
338 -- call assignments api
339 -- l_assignment_rec.assignment_name := ;
340 l_assignment_rec.assignment_type := 'STAFFED_ASSIGNMENT';
341 l_assignment_rec.project_id := l_project_id;
342 l_assignment_rec.project_role_id := p_project_role_id;
343 l_assignment_rec.resource_id := l_resource_id;
344 l_assignment_rec.project_party_id := x_project_party_id;
345 l_assignment_rec.start_date := p_start_date_active;
346 l_assignment_rec.end_date := p_end_date_active;
347
348 PA_ASSIGNMENTS_PUB.Create_Assign_with_def
349 ( p_assignment_rec => l_assignment_rec
350 ,p_resource_source_id => p_resource_source_id
351 ,p_validate_only => 'F'
352 ,x_new_assignment_id => x_assignment_id
353 ,x_assignment_number => x_assignment_number
354 ,x_assignment_row_id => x_assignment_row_id
355 ,x_return_status => x_return_status
356 ,x_msg_count => x_msg_count
357 ,x_msg_data => x_msg_data);
358
359 end if;
360
361 --Bug 5856712
362 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
363 RAISE FND_API.G_EXC_ERROR;
364 END IF;
365
366
367 /* Start of code for bug #2111806:
368 Following validation needs to be done only when called from Self Service.
369 In the case of Assignments flow, this API is being called from PL/SQL code
370 with p_calling_module = 'ASSIGNMENT' and so added this check also.
371 Call the check_manager_date_range to check if the Project
372 Manager exists for the complete duration of the Project. */
373
374 IF (p_mgr_validation_type = 'SS' OR p_calling_module = 'ASSIGNMENT') THEN
375 l_error_occured := 'N';
376 PA_PROJECT_PARTIES_UTILS.validate_manager_date_range( p_mode => 'SS'
377 ,p_project_id => l_project_id
378 ,x_start_no_mgr_date => l_start_no_mgr_date
379 ,x_end_no_mgr_date => l_end_no_mgr_date
380 ,x_error_occured => l_error_occured);
381
382 IF l_error_occured = 'PA_PR_NO_MGR_DATE_RANGE' THEN
383 /* If a Manager does not exist for the entire duration of the project */
384 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
385 pa_utils.add_message
386 ( p_app_short_name => 'PA'
387 ,p_msg_name => 'PA_PR_NO_MGR_DATE_RANGE'
388 ,p_token1 => 'START_DATE'
389 ,p_value1 => l_start_no_mgr_date
390 ,p_token2 => 'END_DATE'
391 ,p_value2 => l_end_no_mgr_date
392 );
393 END IF;
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 RETURN;
396 END IF;
397
398 /* Throw an error if there are no Project Managers assigned.
399 This has to be thrown irrespective of whether there are any Key Member records
400 being passed or not. So, it cannot be done in the above IF condition. */
401 IF l_project_id IS NOT NULL THEN
402 PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS( p_project_id => l_project_id
403 ,x_return_status => x_return_status
404 ,x_msg_count => x_msg_count
405 ,x_msg_data => x_msg_data );
406
407 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
408 x_return_status := FND_API.G_RET_STS_ERROR;
409 RETURN;
410 END IF;
411 END IF;
412 END IF;
413 /* End of code for bug #2111806 */
414
415 if fnd_api.to_boolean(nvl(p_commit,FND_API.G_FALSE)) and x_return_status = FND_API.G_RET_STS_SUCCESS then
416 if (p_debug_mode = 'Y') then
417 IF P_DEBUG_MODE = 'Y' THEN
418 pa_debug.debug('Create_project_party: Commiting data.');
419 END IF;
420 end if;
421 commit work;
422 end if;
423 end if;
424 fnd_msg_pub.count_and_get(p_count => x_msg_count,
425 p_data => x_msg_data);
426
427 pa_debug.reset_err_stack;
428
429 EXCEPTION
430 --Bug 5186830
431 WHEN FND_API.G_EXC_ERROR THEN
432
433 l_msg_count := FND_MSG_PUB.count_msg;
434
435 IF l_msg_count = 1 THEN
436 PA_INTERFACE_UTILS_PUB.get_messages
437 (p_encoded => FND_API.G_TRUE
438 ,p_msg_index => 1
439 ,p_msg_count => l_msg_count
440 ,p_msg_data => l_msg_data
441 ,p_data => l_data
442 ,p_msg_index_out => l_msg_index_out);
443 x_msg_data := l_data;
444 x_msg_count := l_msg_count;
445 ELSE
446 x_msg_count := l_msg_count;
447 END IF;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 pa_debug.reset_err_stack;
450
451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
453 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
454 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
455 fnd_message.set_token('PROCEDURE_NAME','CREATE_PROJECT_PARTY');
456 fnd_msg_pub.add();
457 fnd_msg_pub.count_and_get(p_count => x_msg_count,
458 p_data => x_msg_data);
459 raise;
460
461 WHEN OTHERS THEN
462 if p_commit = FND_API.G_TRUE then
463 rollback to project_parties;
464 end if;
465 x_return_status := fnd_api.g_ret_sts_unexp_error;
466 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
467 p_procedure_name => pa_debug.G_err_stack,
468 p_error_text => SUBSTRB(SQLERRM,1,240));
469 raise;
470
471 END CREATE_PROJECT_PARTY;
472
473 PROCEDURE UPDATE_PROJECT_PARTY( p_commit IN VARCHAR2 := FND_API.G_FALSE,
474 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
475 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
476 p_debug_mode IN VARCHAR2 default 'N',
477 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
478 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
479 p_project_role_id IN NUMBER,
480 p_resource_type_id IN NUMBER := 101,
481 p_resource_source_id IN NUMBER,
482 p_resource_id IN NUMBER,
483 p_start_date_active IN DATE,
484 p_scheduled_flag IN VARCHAR2 := 'N',
485 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
486 p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
487 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
488 p_project_end_date IN DATE,
489 p_project_party_id IN NUMBER,
490 p_assignment_id IN NUMBER,
491 p_assign_record_version_number IN NUMBER,
492 p_mgr_validation_type IN VARCHAR2 := FND_API.G_MISS_CHAR,/*Added for bug 2111806*/
493 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
494 x_assignment_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
495 x_wf_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
496 x_wf_item_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
497 x_wf_process OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
498 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
499 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
500 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
501
502 l_error_occured VARCHAR2(50) := 'N';
503 x_call_overlap VARCHAR2(1) := 'Y';
504 x_assignment_action VARCHAR2(20) := 'NOACTION';
505 l_record_version_number NUMBER;
506 l_project_party_id NUMBER := p_project_party_id;
507 l_grant_id RAW(16); ---NUMBER;
508 l_resource_id NUMBER;
509 l_valid VARCHAR2(1) := 'N';
510 l_error_msg_code VARCHAR2(255);
511 x_assignment_number NUMBER;
512 x_assignment_row_id ROWID;
513 l_assignment_rec PA_ASSIGNMENTS_PUB.Assignment_Rec_Type;
514 l_source_type VARCHAR2(8) := 'PERSON';
515 l_job_schedulable VARCHAR2(1) := 'N';
516 l_date DATE;
517 /* Start of code for bug #2111806 */
518 l_start_no_mgr_date DATE;
519 l_end_no_mgr_date DATE;
520 l_msg_count NUMBER;
521 l_msg_data VARCHAR2(1000);
522 l_return_status VARCHAR2(1000);
523 /* End of code for bug #2111806 */
524 l_msg_index_out NUMBER; --Bug 5856712
525 l_data VARCHAR2(2000); --Bug 5856712
526
527 /* Added for Bug 6501489 */
528 /*CURSOR l_staff_assn_exists_csr
529 IS
530 SELECT * FROM pa_project_assignments
531 WHERE project_id = p_project_id
532 AND ASSIGNMENT_TYPE = 'STAFFED_ASSIGNMENT'
533 AND PROJECT_ROLE_ID = 1
534 AND start_date = p_start_date_active
535 AND resource_id = p_resource_id
536 AND APPRVL_STATUS_CODE NOT IN -- This condition added after bug 7023082
537 ('ASGMT_APPRVL_REJECTED','ASGMT_APPRVL_CANCELED');*/
538 --l_staff_assn_exists_rec l_staff_assn_exists_csr%ROWTYPE;
539
540 BEGIN
541 if p_commit = FND_API.G_TRUE then
542 savepoint project_parties;
543 end if;
544 x_return_status := FND_API.G_RET_STS_SUCCESS;
545 --dbms_output.put_line('role id '||to_char(p_project_role_id));
546 --dbms_output.put_line('resource source id '||to_char(p_resource_source_id));
547 --dbms_output.put_line('project party id '||to_char(p_project_party_id));
548 l_record_version_number := p_record_version_number;
549
550 if p_validation_level > 0 then
551 if (p_debug_mode = 'Y') then
552 IF P_DEBUG_MODE = 'Y' THEN
553 pa_debug.debug('Update_project_party: Calling validate_project_party.');
554 END IF;
555 end if;
556 pa_debug.g_err_stage := 'Calling validate_project_party';
557 pa_project_parties_utils.validate_project_party(
558 p_validation_level,
559 p_debug_mode,
560 p_object_id,
561 p_OBJECT_TYPE,
562 p_project_role_id,
563 p_resource_type_id,
564 p_resource_source_id,
565 p_start_date_active,
566 NVL(p_scheduled_flag, 'N'),
567 l_record_version_number,
568 p_calling_module,
569 'UPDATE',
570 p_project_id,
571 p_project_end_date,
572 p_end_date_active,
573 l_project_party_id,
574 x_call_overlap,
575 x_assignment_action,
576 x_return_status);
577 end if;
578
579
580 If x_return_status = FND_API.G_RET_STS_SUCCESS and not(fnd_api.to_boolean(nvl(p_validate_only,FND_API.G_FALSE))) then
581
582 if x_assignment_action = 'CREATE' then
583 -- call assignments api
584
585 IF (p_scheduled_flag = 'Y') THEN
586 pa_resource_utils.CHECK_RES_BELONGS_EXPORG(p_resource_id => p_resource_id,
587 x_valid => l_valid,
588 x_return_status => x_return_status,
589 x_error_message_code => l_error_msg_code);
590 IF (l_valid <> 'Y') then
591 -- check that the person is allowed to have schedule
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 fnd_message.set_name('PA','PA_NO_SCHEDULABLE_PERSON');
594 fnd_msg_pub.add();
595 END IF;
596
597 -- if the res belongs to the expenditure org hierarchy, check if the res's
598 -- job is schedulable
599
600
601 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
602 -- Added for bug 3149239
603 l_date :=pa_resource_utils.Get_Resource_Effective_Date(p_resource_id => p_resource_id);
604 l_job_schedulable := PA_HR_UPDATE_API.check_job_schedulable
605 ( p_person_id => p_resource_source_id
606 ,p_date => l_date );
607
608 IF l_job_schedulable <> 'Y' THEN
609 x_return_status := FND_API.G_RET_STS_ERROR;
610 fnd_message.set_name('PA','PA_NOT_SCHEDULABLE_JOB');
611 fnd_msg_pub.add();
612 END IF;
613 END IF;
614 END IF;
615 if x_return_status = FND_API.G_RET_STS_SUCCESS then
616
617 l_assignment_rec.assignment_type := 'STAFFED_ASSIGNMENT';
618 l_assignment_rec.project_id := p_project_id;
619 l_assignment_rec.project_role_id := p_project_role_id;
620 l_assignment_rec.resource_id := p_resource_id;
621 l_assignment_rec.project_party_id := p_project_party_id;
622 l_assignment_rec.start_date := p_start_date_active;
623 l_assignment_rec.end_date := p_end_date_active;
624
625
626 PA_ASSIGNMENTS_PUB.Create_Assign_with_def
627 ( p_assignment_rec => l_assignment_rec
628 ,p_resource_source_id => p_resource_source_id
629 ,p_validate_only => 'F'
630 ,x_new_assignment_id => x_assignment_id
631 ,x_assignment_number => x_assignment_number
632 ,x_assignment_row_id => x_assignment_row_id
633 ,x_return_status => x_return_status
634 ,x_msg_count => x_msg_count
635 ,x_msg_data => x_msg_data);
636 end if;
637
638 elsif x_assignment_action = 'DELETE' then
639 -- call delete assignments api
640 --MT Only call the api if assignment_id is passed in
641 IF p_assignment_id > 0 THEN
642
643 pa_project_parties_pvt.l_delete_proj_party := 'N';
644
645 PA_ASSIGNMENTS_PUB.Delete_Assignment
646 ( p_assignment_id => p_assignment_id
647 ,p_assignment_type => 'STAFFED_ASSIGNMENT'
648 ,p_record_version_number => p_assign_record_version_number
649 ,p_commit => p_commit
650 ,p_validate_only => FND_API.G_FALSE
651 ,x_return_status => x_return_status
652 ,x_msg_count => x_msg_count
653 ,x_msg_data => x_msg_data);
654
655 END IF;
656
657 pa_project_parties_pvt.l_delete_proj_party := 'Y';
658
659 /* Code added for Bug 6501489 */
660 /*elsif x_assignment_action = 'NOACTION' THEN
661
662 OPEN l_staff_assn_exists_csr;
663 FETCH l_staff_assn_exists_csr INTO l_staff_assn_exists_rec;
664
665 IF l_staff_assn_exists_csr%FOUND
666 THEN
667
668 PA_SCHEDULE_PUB.update_schedule (
669 p_project_id => p_project_id
670 -- ,p_mass_update_flag => FND_API.G_FLASE
671 ,p_exception_type_code => 'CHANGE_DURATION'
672 ,p_record_version_number => l_staff_assn_exists_rec.record_version_number
673 ,p_assignment_id => l_staff_assn_exists_rec.assignment_id
674 ,p_change_start_date => p_start_date_active
675 ,p_change_end_date => p_end_date_active
676 ,p_assignment_status_code => l_staff_assn_exists_rec.status_code
677 ,p_non_working_day_flag => 'N'
678 ,p_called_by_proj_party => 'Y'
679 ,p_commit => p_commit
680 ,p_validate_only => FND_API.G_FALSE--'F'
681 ,x_return_status => x_return_status
682 ,x_msg_count => x_msg_count
683 ,x_msg_data => x_msg_data );
684
685 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
686 Close l_staff_assn_exists_csr;
687 RAISE FND_API.G_EXC_ERROR;
688 END IF;
689
690
691 END IF;
692 Close l_staff_assn_exists_csr;*/
693 end if;
694 /* End of code changes for Bug 6501489 */
695
696 -- call update api
697 if (p_debug_mode = 'Y') then
698 IF P_DEBUG_MODE = 'Y' THEN
699 pa_debug.debug('Update_project_party: Calling update_row.');
700 END IF;
701 end if;
702
703 /* FP-L status-based security
704 l_grant_id := pa_project_parties_utils.get_grant_id(p_project_party_id => p_project_party_id);
705
706 if x_return_status = FND_API.G_RET_STS_SUCCESS and l_grant_id > 0 then
707 -- call fnd_grants
708 IF p_resource_type_id = 112 THEN
709 l_source_type := 'HZ_PARTY';
710 END IF;
711
712 pa_security_pvt.update_role(p_grant_guid => l_grant_id,
713 p_project_role_id_old => p_project_role_id,
714 p_object_name_old => p_object_type,
715 p_object_key_type_old => 'INSTANCE',
716 p_object_key_old => p_object_id,
717 p_party_id_old => p_resource_source_id,
718 p_source_type_old => l_source_type,
719 p_start_date_old => to_date(null),
720 p_start_date_new => p_start_date_active,
721 p_end_date_new => p_end_date_active,
722 x_return_status => x_return_status,
723 x_msg_count => x_msg_count,
724 x_msg_data => x_msg_data
725 );
726
727 end if;
728
729 */
730
731 if x_return_status = FND_API.G_RET_STS_SUCCESS then
732 l_grant_id := null;
733
734 ----dbms_output.put_line('calling update api');
735 pa_debug.g_err_stage := 'Calling Update_row';
736 PA_PROJECT_PARTIES_PKG.UPDATE_ROW (
737 X_PROJECT_PARTY_ID => p_project_party_id,
738 X_PROJECT_ID => p_project_id,
739 X_RESOURCE_SOURCE_ID => p_resource_source_id,
740 X_RESOURCE_TYPE_ID => p_resource_type_id,
741 X_PROJECT_ROLE_ID => p_project_role_id,
742 X_START_DATE_ACTIVE => trunc(p_start_date_active),
743 X_END_DATE_ACTIVE => trunc(p_end_date_active),
744 X_SCHEDULED_FLAG => NVL(p_scheduled_flag, 'N'),
745 X_GRANT_ID => l_grant_id,
746 X_record_version_number => p_record_version_number,
747 X_LAST_UPDATE_DATE => sysdate,
748 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
749 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
750 X_RETURN_STATUS => x_return_status);
751 if (x_return_status = 'N') then
752 x_return_status := FND_API.G_RET_STS_ERROR;
753 if p_calling_module = 'FORM' then
754 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
755 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
756 --fnd_message.set_token('PROCEDURE_NAME','UPDATE_ROW');
757 fnd_msg_pub.add;
758 else
759 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
760 --fnd_message.set_token('PKG_NAME',to_char(p_project_party_id));
761 --fnd_message.set_token('PROCEDURE_NAME',to_char(p_record_version_number));
762 fnd_msg_pub.add;
763
764 end if;
765 end if;
766
767 end if;
768
769 end if;
770
771 --Bug 5856712
772 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
773 RAISE FND_API.G_EXC_ERROR;
774 END IF;
775
776
777 /* Start of code for bug #2111806:
778 Following validation needs to be done only when called from Self Service.
779 In the case of Assignments flow, this API is being called from PL/SQL code
780 with p_calling_module = 'ASSIGNMENT' and so added this check also.
781
782 Call the check_manager_date_range to check if the Project
783 Manager exists for the complete duration of the Project. */
784
785 IF ( p_mgr_validation_type = 'SS' OR p_calling_module = 'ASSIGNMENT') THEN
786 l_error_occured := 'N';
787 PA_PROJECT_PARTIES_UTILS.validate_manager_date_range( p_mode => 'SS'
788 ,p_project_id => p_project_id
789 ,x_start_no_mgr_date => l_start_no_mgr_date
790 ,x_end_no_mgr_date => l_end_no_mgr_date
791 ,x_error_occured => l_error_occured);
792
793 IF l_error_occured = 'PA_PR_NO_MGR_DATE_RANGE' THEN
794 /* If a Manager does not exist for the entire duration of the project */
795 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
796 pa_utils.add_message
797 ( p_app_short_name => 'PA'
798 ,p_msg_name => 'PA_PR_NO_MGR_DATE_RANGE'
799 ,p_token1 => 'START_DATE'
800 ,p_value1 => l_start_no_mgr_date
801 ,p_token2 => 'END_DATE'
802 ,p_value2 => l_end_no_mgr_date
803 );
804 END IF;
805 x_return_status := FND_API.G_RET_STS_ERROR;
806 RETURN;
807 END IF;
808
809 /* Throw an error if there are no Project Managers assigned.
810 This has to be thrown irrespective of whether there are any Key Member records
811 being passed or not. So, it cannot be done in the above IF condition. */
812 IF p_project_id IS NOT NULL THEN
813 PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS( p_project_id => p_project_id
814 ,x_return_status => x_return_status
815 ,x_msg_count => x_msg_count
816 ,x_msg_data => x_msg_data );
817
818 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
819 x_return_status := FND_API.G_RET_STS_ERROR;
820 RETURN;
821 END IF;
822 END IF;
823 END IF;
824 /* End of code for bug #2111806 */
825
826 if fnd_api.to_boolean(nvl(p_commit,fnd_api.G_FALSE)) and x_return_status = FND_API.G_RET_STS_SUCCESS then
827 if (p_debug_mode = 'Y') then
828 IF P_DEBUG_MODE = 'Y' THEN
829 pa_debug.debug('Update_project_party: Commiting data.');
830 END IF;
831 end if;
832 commit work;
833 end if;
834 fnd_msg_pub.count_and_get(p_count => x_msg_count,
835 p_data => x_msg_data);
836
837 pa_debug.reset_err_stack;
838
839 EXCEPTION
840
841 -- bug 5856712
842 WHEN FND_API.G_EXC_ERROR THEN
843
844 l_msg_count := FND_MSG_PUB.count_msg;
845
846 IF l_msg_count = 1 THEN
847 PA_INTERFACE_UTILS_PUB.get_messages
848 (p_encoded => FND_API.G_TRUE
849 ,p_msg_index => 1
850 ,p_msg_count => l_msg_count
851 ,p_msg_data => l_msg_data
852 ,p_data => l_data
853 ,p_msg_index_out => l_msg_index_out);
854 x_msg_data := l_data;
855 x_msg_count := l_msg_count;
856 ELSE
857 x_msg_count := l_msg_count;
858 END IF;
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 pa_debug.reset_err_stack;
861
862 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
864 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
865 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
866 fnd_message.set_token('PROCEDURE_NAME','UPDATE_PROJECT_PARTY');
867 fnd_msg_pub.add();
868 fnd_msg_pub.count_and_get(p_count => x_msg_count,
869 p_data => x_msg_data);
870 raise;
871
872
873 WHEN OTHERS THEN
874 if p_commit = fnd_api.G_TRUE then
875 rollback to project_parties;
876 end if;
877 x_return_status := fnd_api.g_ret_sts_unexp_error;
878 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
879 p_procedure_name => pa_debug.g_err_stack,
880 p_error_text => SUBSTRB(SQLERRM,1,240));
881 fnd_msg_pub.count_and_get(p_count => x_msg_count,
882 p_data => x_msg_data);
883
884 raise;
885
886 end update_project_party;
887
888
889 PROCEDURE DELETE_PROJECT_PARTY( p_commit IN VARCHAR2 := FND_API.G_FALSE,
890 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
891 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
892 p_debug_mode IN VARCHAR2 default 'N',
893 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
894 p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
895 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
896 p_project_party_id IN NUMBER := FND_API.G_MISS_NUM,
897 p_scheduled_flag IN VARCHAR2 := 'N',
898 /* code commented for the bug#1851096, starts here */
899 /* p_assignment_id IN NUMBER := FND_API.G_MISS_NUM,
900 p_assign_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
901 */
902 /* code commented for the bug#1851096, end here */
903 /* code added for the bug#1851096, starts here */
904 p_assignment_id IN NUMBER := 0,
905 p_assign_record_version_number IN NUMBER := 0,
906 /* code added for the bug#1851096, end here */
907 p_mgr_validation_type IN VARCHAR2 := FND_API.G_MISS_CHAR,/*Added for bug 2111806*/
908 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
909 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
910 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
911
912 l_project_id NUMBER;
913 l_grant_id RAW(16); ------NUMBER;
914 API_ERROR EXCEPTION;
915
916 -- 4616302 TCA UPTAKE: HZ_PARTY_RELATIONS IMPACTS
917 -- changed hz_party_relationships usage to hz_relationships
918 -- changed column party_relationship_type usage to relationship_type
919
920 /*
921 CURSOR c_ext_people IS
922 SELECT pp.project_party_id project_party_id,
923 pp.record_version_number record_version_number
924 FROM pa_project_parties po,
925 pa_project_parties pp,
926 hz_party_relationships hzr
927 WHERE po.resource_type_id = 112
928 AND po.project_party_id = p_project_party_id
929 AND pp.resource_type_id = 112
930 AND pp.object_type = po.object_type
931 AND pp.object_id = po.object_id
932 AND hzr.party_relationship_type IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
933 AND hzr.subject_id = pp.resource_source_id
934 AND hzr.object_id = po.resource_source_id;
935 */
936
937 CURSOR c_ext_people IS
938 SELECT pp.project_party_id project_party_id,
939 pp.record_version_number record_version_number
940 FROM pa_project_parties po,
941 pa_project_parties pp,
942 hz_relationships hzr
943 WHERE po.resource_type_id = 112
944 AND po.project_party_id = p_project_party_id
945 AND pp.resource_type_id = 112
946 AND pp.object_type = po.object_type
947 AND pp.object_id = po.object_id
948 AND hzr.relationship_code IN ( 'EMPLOYEE_OF', 'CONTACT_OF')
949 AND hzr.subject_id = pp.resource_source_id
950 AND hzr.object_id = po.resource_source_id
951 AND hzr.object_table_name = 'HZ_PARTIES'
952 AND hzr.subject_type = 'PERSON'
953 AND hzr.subject_table_name = 'HZ_PARTIES';
954
955 -- 4616302 end
956
957 CURSOR c_billing_accounts IS
958 SELECT customer_id, record_version_number
959 FROM pa_project_customers
960 WHERE project_id = p_project_id
961 AND project_party_id = p_project_party_id;
962
963 /* Start of code for bug #2111806 */
964 l_start_no_mgr_date DATE;
965 l_end_no_mgr_date DATE;
966 l_error_occured VARCHAR2(50);
967 l_msg_count NUMBER;
968 l_msg_data VARCHAR2(1000);
969 l_return_status VARCHAR2(1000);
970 /* End of code for bug #2111806 */
971
972 BEGIN
973 if p_commit = FND_API.G_TRUE then
974 savepoint project_parties;
975 end if;
976
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978
979 if (p_debug_mode = 'Y') then
980 IF P_DEBUG_MODE = 'Y' THEN
981 pa_debug.debug('Delete_project_party : Lock Key Members ');
982 END IF;
983 end if;
984
985 --lock the project player
986
987 if (p_debug_mode = 'Y') then
988 IF P_DEBUG_MODE = 'Y' THEN
989 pa_debug.debug('Delete_project_party : Before delete from pa_project_players ');
990 END IF;
991 end if;
992
993 /* Added the following code for bug #2111806:
994 When this API is called during an Assignment deletion, then the p_project_id is not being passed.
995 We require the project_id to call the validate_manager_date_range API.
996 So, fetching the p_project_id based on the p_project_party_id. */
997
998 l_project_id := p_project_id; -- Added for bug 4483205
999
1000 IF (( p_project_id IS NULL OR p_project_id = FND_API.G_MISS_NUM) AND
1001 ( nvl(p_project_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )) THEN
1002
1003 SELECT project_id
1004 INTO l_project_id
1005 FROM pa_project_parties
1006 WHERE project_party_id = p_project_party_id;
1007
1008 END IF;
1009
1010 if (pa_project_parties_pvt.l_delete_proj_party = 'Y') then
1011
1012 if pa_project_parties_utils.validate_delete_party_ok(l_project_id,p_project_party_id) = 'Y' then
1013
1014 --Deleting all external people before deleting the org.
1015 FOR rec IN c_ext_people LOOP
1016 pa_project_parties_pvt.delete_project_party(
1017 p_commit => p_commit,
1018 p_validate_only => p_validate_only,
1019 p_validation_level => p_validation_level,
1020 p_debug_mode => p_debug_mode,
1021 p_record_version_number => rec.record_version_number,
1022 p_calling_module => p_calling_module,
1023 p_project_id => l_project_id,
1024 p_project_party_id => rec.project_party_id,
1025 x_return_status => x_return_status,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data);
1028 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
1029 END LOOP;
1030
1031 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1032 RETURN;
1033 END IF;
1034
1035 FOR rec In c_billing_accounts LOOP
1036 pa_customers_contacts_pub.delete_project_customer(
1037 p_validate_only => p_validate_only,
1038 p_validation_level => p_validation_level,
1039 p_calling_module => p_calling_module,
1040 p_debug_mode => p_debug_mode,
1041 p_project_id => l_project_id,
1042 p_customer_id => rec.customer_id,
1043 p_record_version_number => rec.record_version_number,
1044 x_return_status => x_return_status,
1045 x_msg_count => x_msg_count,
1046 x_msg_data => x_msg_data);
1047 EXIT WHEN x_return_status <> FND_API.G_RET_STS_SUCCESS;
1048 END LOOP;
1049
1050 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1051 RETURN;
1052 END IF;
1053
1054 l_grant_id := pa_project_parties_utils.get_grant_id(p_project_party_id => p_project_party_id);
1055 pa_debug.g_err_stage := 'Calling delete_row';
1056 pa_project_parties_pkg.delete_row(x_project_id => l_project_id,
1057 x_project_party_id => p_project_party_id,
1058 x_record_version_number => p_record_version_number);
1059 end if;
1060
1061 /* FP-L status-based security
1062 ----- if x_return_status = FND_API.G_RET_STS_SUCCESS and pa_install.is_prm_licensed() = 'Y' and l_grant_id > 0 then
1063 if x_return_status = FND_API.G_RET_STS_SUCCESS and l_grant_id > 0 then
1064 pa_security_pvt.revoke_grant(p_grant_guid => l_grant_id,
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data
1068 );
1069
1070 end if;
1071 */
1072
1073 /* Start of code for bug #2111806:
1074 Following validation needs to be done only when called from Self Service.
1075 In the case of Assignments flow, this API is being called from PL/SQL code
1076 with p_calling_module = 'ASSIGNMENT' and so added this check also.
1077
1078 Call the check_manager_date_range to check if the Project
1079 Manager exists for the complete duration of the Project. */
1080
1081 IF ( p_mgr_validation_type = 'SS' OR p_calling_module = 'ASSIGNMENT') THEN
1082 l_error_occured := 'N';
1083 PA_PROJECT_PARTIES_UTILS.validate_manager_date_range( p_mode => 'SS'
1084 ,p_project_id => l_project_id
1085 ,x_start_no_mgr_date => l_start_no_mgr_date
1086 ,x_end_no_mgr_date => l_end_no_mgr_date
1087 ,x_error_occured => l_error_occured);
1088
1089 IF l_error_occured = 'PA_PR_NO_MGR_DATE_RANGE' THEN
1090 /* If a Manager does not exist for the entire duration of the project */
1091 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1092 pa_utils.add_message
1093 ( p_app_short_name => 'PA'
1094 ,p_msg_name => 'PA_PR_NO_MGR_DATE_RANGE'
1095 ,p_token1 => 'START_DATE'
1096 ,p_value1 => l_start_no_mgr_date
1097 ,p_token2 => 'END_DATE'
1098 ,p_value2 => l_end_no_mgr_date
1099 );
1100 END IF;
1101 x_return_status := FND_API.G_RET_STS_ERROR;
1102 RETURN;
1103 END IF;
1104
1105 /* Throw an error if there are no Project Managers assigned.
1106 This has to be thrown irrespective of whether there are any Key Member records
1107 being passed or not. So, it cannot be done in the above IF condition. */
1108 IF p_project_id IS NOT NULL THEN
1109 PA_PROJECT_PARTIES_UTILS.VALIDATE_ONE_MANAGER_EXISTS( p_project_id => l_project_id
1110 ,x_return_status => x_return_status
1111 ,x_msg_count => x_msg_count
1112 ,x_msg_data => x_msg_data );
1113
1114 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 RETURN;
1117 END IF;
1118 END IF;
1119
1120 END IF;
1121 /* End of code for bug #2111806 */
1122
1123 if fnd_api.to_boolean(nvl(p_commit,fnd_api.G_FALSE)) and x_return_status = FND_API.G_RET_STS_SUCCESS then
1124 if (p_debug_mode = 'Y') then
1125 IF P_DEBUG_MODE = 'Y' THEN
1126 pa_debug.debug('Delete_project_party: Commiting data.');
1127 END IF;
1128 end if;
1129 commit work;
1130 end if;
1131
1132 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1133 p_data => x_msg_data);
1134 end if;
1135
1136 pa_debug.reset_err_stack;
1137
1138 EXCEPTION
1139
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1142 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
1143 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PVT');
1144 fnd_message.set_token('PROCEDURE_NAME','DELETE_PROJECT_PARTY');
1145 fnd_msg_pub.add();
1146 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1147 p_data => x_msg_data);
1148 raise;
1149
1150 WHEN NO_DATA_FOUND THEN
1151
1152 if (p_debug_mode = 'Y') then
1153 IF P_DEBUG_MODE = 'Y' THEN
1154 pa_debug.debug('Delete_project_party : Exception NO_DATA_FOUND ');
1155 END IF;
1156 end if;
1157
1158 --fnd_message.set_name('PA', 'PA_XC_NO_DATA_FOUND');
1159 x_return_status := FND_API.G_RET_STS_ERROR;
1160
1161 WHEN TIMEOUT_ON_RESOURCE THEN
1162
1163 if (p_debug_mode = 'Y') then
1164 IF P_DEBUG_MODE = 'Y' THEN
1165 pa_debug.debug('Delete_project_party : Exception TIMEOUT_ON_RESOURCE ');
1166 END IF;
1167 end if;
1168
1169 fnd_message.set_name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
1170 x_return_status := FND_API.G_RET_STS_ERROR;
1171 return;
1172
1173 WHEN OTHERS then
1174 if p_commit = fnd_api.G_TRUE then
1175 rollback to project_parties;
1176 end if;
1177
1178 if (p_debug_mode = 'Y') then
1179 IF P_DEBUG_MODE = 'Y' THEN
1180 pa_debug.debug('Delete_project_party : Exception OTHERS ');
1181 END IF;
1182 end if;
1183
1184 if(SQLCODE = -54) then
1185 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
1186 x_msg_data := FND_MESSAGE.get;
1187 x_return_status := FND_API.G_RET_STS_ERROR;
1188 else
1189 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
1190 p_procedure_name => pa_debug.g_err_stack,
1191 p_error_text => SUBSTRB(SQLERRM,1,240));
1192 x_return_status := FND_API.G_RET_STS_ERROR;
1193 end if;
1194
1195 END DELETE_PROJECT_PARTY;
1196
1197 end;
1198