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