[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_PARTIES_PUB
Source
1 package body PA_PROJECT_PARTIES_PUB as
2 /* $Header: PARPPPMB.pls 120.5.12010000.3 2009/09/08 07:25:19 anuragar ship $ */
3
4 PROCEDURE CREATE_PROJECT_PARTY( p_api_version IN NUMBER := 1.0,
5 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
6 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_project_role_id IN NUMBER := FND_API.G_MISS_NUM,
13 p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
14 p_resource_type_id IN NUMBER := 101, --EMPLOYEE
15 p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM,
16 p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
17 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
18 p_scheduled_flag IN VARCHAR2 := 'N',
19 p_calling_module IN VARCHAR2,
20 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
21 p_project_end_date IN DATE := FND_API.G_MISS_DATE,
22 p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
23 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
24 x_project_party_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_resource_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
26 x_assignment_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
27 x_wf_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
28 x_wf_item_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
29 x_wf_process OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
30 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
33
34 l_error_occured VARCHAR2(1) := 'N';
35 x_assignment_action VARCHAR2(20) := 'NOACTION';
36 l_api_version NUMBER := 1.0;
37 l_api_name VARCHAR2(30) := 'create_project_party';
38 l_resource_id NUMBER;
39 l_project_id NUMBER;
40 l_project_party_id NUMBER;
41 l_resource_source_id NUMBER;
42 l_resource_type_id NUMBER;
43 l_project_role_id NUMBER;
44 l_error_message_code fnd_new_messages.message_name%TYPE;
45 l_record_version_number NUMBER := 1;
46 l_project_start_date DATE;
47 l_project_end_date DATE;
48 l_start_date_active DATE;
49 l_msg_index_out NUMBER;
50 x_resource_type_id pa_resources.resource_type_id%type; -- added for bug 2636791
51 x_role_party_class pa_project_role_types_b.role_party_class%type; /* Added for Bug 2876924 */
52 l_key_member_start_date DATE; -- Added for bug 2686120
53 l_check_id_flag VARCHAR2(1); -- Added for bug 4947618
54 l_end_date_active DATE;--Bug 6935585
55 BEGIN
56
57 --dbms_output.put_line('Check1');
58
59 SAVEPOINT create_project_party;
60
61 --- Standard call to check for call compatibility
62 if (p_debug_mode = 'Y') then
63 pa_debug.debug('create_project_party: Checking the api version number.');
64 end if;
65
66 if NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
67 l_api_name, 'PA_PROJECT_PARTIES_PUB') then
68 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
69 end if;
70
71 ------dbms_output.put_line('Before initializing the stack');
72
73 --- Initialize the message stack if required
74
75 if (p_debug_mode = 'Y') then
76 pa_debug.debug('Create_project_party: Initializing message stack.');
77 end if;
78 pa_debug.init_err_stack('Create_project_party_pub');
79
80 if FND_API.to_boolean(nvl(p_init_msg_list,FND_API.G_TRUE)) then
81 fnd_msg_pub.initialize;
82 end if;
83
84 ------dbms_output.put_line('After initializing the stack');
85
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87
88 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
89 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
90 else
91 l_project_role_id := p_project_role_id;
92 end if;
93 --dbms_output.put_line('Check2');
94 /* if p_resource_source_id is null or p_resource_source_id = FND_API.G_MISS_NUM then
95 l_resource_source_id := pa_project_parties_utils.get_resource_source_id(p_resource_name);
96 else
97 l_resource_source_id := p_resource_source_id;
98 end if; */
99
100
101 if p_project_id is not null and p_project_id <> FND_API.G_MISS_NUM then
102 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_DATES(p_project_id => p_project_id,
103 x_project_start_date => l_project_start_date,
104 x_project_end_date => l_project_end_date,
105 x_return_status => x_return_status);
106 end if;
107 --dbms_output.put_line('Check3');
108 if p_project_id is null or p_project_id = FND_API.G_MISS_NUM then
109 l_project_id := null;
110 else
111 l_project_id := p_project_id;
112 end if;
113
114 /* Bug 2636791 - changes begin */
115
116 /* Following code is added to implement the logic :-
117 -> If project has a past start date, key member start date should
118 default to the project start date.
119 -> If project has a future start date, key member start date should
120 default to the sysdate.
121 */
122
123 if p_project_id is not null and p_project_id <> FND_API.G_MISS_NUM then
124 if l_project_start_date is not NULL THEN
125 if l_project_start_date <= trunc(sysdate) then
126 l_project_start_date := l_project_start_date;
127 else
128 l_project_start_date := trunc(sysdate);
129 end if;
130 else
131 l_project_start_date := trunc(sysdate);
132 end if;
133 end if;
134
135 /* Following code commented to implement the new logic as mentioned above
136 if p_start_date_active is null or p_start_date_active = FND_API.G_MISS_DATE then
137 l_start_date_active := trunc(sysdate);
138 else
139 l_start_date_active := p_start_date_active;
140 end if;
141 */
142
143 /* if p_resource_source_id is null or p_resource_source_id = FND_API.G_MISS_NUM then
144 l_resource_source_id := pa_project_parties_utils.get_resource_source_id(p_resource_name);
145 else
146 l_resource_source_id := p_resource_source_id;
147 end if; */
148
149 /* Commented the code for the bug 2686120*/
150 /*if p_start_date_active is null or p_start_date_active = FND_API.G_MISS_DATE then
151 l_start_date_active := l_project_start_date;
152 else
153 l_start_date_active := p_start_date_active;
154 end if;*/
155
156
157 /* Modified for the bug 2686120 changed the parameter to get the values of start date.*/
158
159 l_key_member_start_date := PA_RESOURCE_UTILS.GET_PERSON_START_DATE(p_resource_source_id);
160
161 /*Commented the below code for the bug 2910972*/
162
163 /*IF p_start_date_active is null OR
164 p_start_date_active = FND_API.G_MISS_DATE
165 THEN
166 IF l_key_member_start_date > sysdate THEN
167 l_start_date_active := l_key_member_start_date;
168 ELSE
169 l_start_date_active := GET_KEY_MEMBER_START_DATE(p_project_id);
170 END IF;
171 ELSE
172 l_start_date_active := p_start_date_active;
173 END IF;*/
174
175 /*Added the below code for the bug 2910972*/
176
177 IF p_start_date_active is null OR
178 p_start_date_active = FND_API.G_MISS_DATE
179 THEN
180 IF l_key_member_start_date > sysdate THEN
181 l_start_date_active := l_key_member_start_date;
182 ELSE
183 l_start_date_active := GET_KEY_MEMBER_START_DATE(p_project_id);
184 IF l_start_date_active < l_key_member_start_date THEN
185 l_start_date_active := l_key_member_start_date;
186 END IF;
187 END IF;
188 ELSE
189 l_start_date_active := p_start_date_active;
190 END IF;
191
192 /*Added till here for bug 2910972*/
193
194 /* Bug 2636791 - changes end */
195
196 /* Bug 2636791 - changes for add organization start date.
197 On adding a new organization to a project
198 start date should be sysdate*/
199 /* Bug 3116962 : Added a IF clause to check if the project_role_id is valid or not. Accordingly the SELECT statement below will execute. */
200 IF l_project_role_id <> -999 AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
201 SELECT resource_type_id into x_resource_type_id
202 FROM pa_resource_types
203 WHERE resource_type_code='HZ_PARTY';
204
205 /* Bug 2876924 - For external people start date should not be defaulted to sysdate.
206 resource type id of external people is same as that of org. ie 112.
207 Hence including a check that role party class should not be 'PERSON'
208 before start date is defaulted to sysdate */
209
210 select role_party_class into x_role_party_class
211 from pa_project_role_types_b
212 where project_role_id = l_project_role_id;
213
214 END IF; /* Bug 3116962 */
215
216 l_end_date_active := p_end_date_active; --Bug 6935585
217
218 if p_resource_type_id = x_resource_type_id and x_role_party_class <> 'PERSON' Then
219 l_start_date_active := trunc(sysdate);
220 --Changes done to set end_date_active to null when new organization gets added using copy
221 -- project flow
222 l_end_date_active := null; --Bug 6935585
223 end if;
224
225 /* Start of code for Bug 4947618 */
226
227 IF p_calling_module = 'PROJECT_MEMBER' THEN
228 l_check_id_flag := 'Y';
229 ELSE
230 l_check_id_flag := PA_STARTUP.G_Check_ID_Flag;
231 END IF;
232
233 /* End of code for Bug 4947618 */
234
235 --dbms_output.put_line('Check4');
236 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id ( p_resource_id => p_resource_source_id
237 ,p_resource_type_id => p_resource_type_id
238 ,p_resource_name => p_resource_name
239 ,p_check_id_flag => l_check_id_flag --Bug 4947618 PA_STARTUP.G_Check_ID_Flag
240 ,p_date => l_start_date_active
241 ,x_resource_id => l_resource_source_id
242 ,x_resource_type_id => l_resource_type_id
243 ,x_return_status => x_return_status
244 ,x_error_message_code => l_error_message_code)
245 ;
246
247 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
248 fnd_message.set_name('PA',l_error_message_code);
249 fnd_msg_pub.add;
250 end if;
251
252 --dbms_output.put_line('Check5 role_id='||l_project_role_id||', resource_source_id='||l_resource_source_id);
253 -- dbms_output.put_line('Return Status '||x_return_status);
254
255 if l_project_role_id <> -999 and l_resource_source_id <> -999 and x_return_status = FND_API.G_RET_STS_SUCCESS then
256 --dbms_output.put_line('Check6');
257 PA_PROJECT_PARTIES_PVT.CREATE_PROJECT_PARTY( p_commit => p_commit,
258 p_validate_only => p_validate_only,
259 p_validation_level => p_validation_level,
260 p_debug_mode => p_debug_mode,
261 p_object_id => p_object_id,
262 p_OBJECT_TYPE => p_object_type,
263 p_RESOURCE_TYPE_ID => l_resource_type_id,
264 p_project_role_id => l_project_role_id,
265 p_resource_source_id => l_resource_source_id,
266 p_start_date_active => l_start_date_active,
267 p_scheduled_flag => p_scheduled_flag,
268 p_calling_module => p_calling_module,
269 p_project_id => l_project_id,
270 p_project_end_date => l_project_end_date,
271 p_end_date_active => l_end_date_active, --Bug 6935585 p_end_date_active,
272 p_mgr_validation_type => p_mgr_validation_type,
273 x_project_party_id => x_project_party_id,
274 x_resource_id => x_resource_id,
275 x_assignment_id => x_assignment_id,
276 x_wf_type => x_wf_type,
277 x_wf_item_type => x_wf_item_type,
278 x_wf_process => x_wf_type,
279 x_return_status => x_return_status,
280 x_msg_count => x_msg_count,
281 x_msg_data => x_msg_data);
282 else
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 end if;
285
286 fnd_msg_pub.count_and_get(p_count => x_msg_count,
287 p_data => x_msg_data);
288 --dbms_output.put_line('Check7');
289 IF x_msg_count = 1 THEN
290 --dbms_output.put_line('Check8');
291 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
292 ,p_msg_index => 1
293 ,p_data => x_msg_data
294 ,p_msg_index_out => l_msg_index_out
295 );
296 END IF;
297 --dbms_output.put_line('Check9');
298 pa_debug.reset_err_stack;
299
300 EXCEPTION
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 --dbms_output.put_line('Check10');
303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
304 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
305 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
306 fnd_message.set_token('PROCEDURE_NAME','CREATE_PROJECT_PARTY');
307 fnd_msg_pub.add();
308 fnd_msg_pub.count_and_get(p_count => x_msg_count,
309 p_data => x_msg_data);
310 raise;
311
312 WHEN OTHERS THEN
313 rollback to create_project_party;
314 x_return_status := fnd_api.g_ret_sts_error;
315 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
316 p_procedure_name => pa_debug.G_err_stack,
317 p_error_text => SUBSTRB(SQLERRM,1,240));
318 raise;
319
320 END CREATE_PROJECT_PARTY;
321
322 PROCEDURE UPDATE_PROJECT_PARTY( p_api_version IN NUMBER := 1.0,
323 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
324 p_commit IN VARCHAR2 := FND_API.G_FALSE,
325 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
326 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
327 p_debug_mode IN VARCHAR2 default 'N',
328 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
329 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
330 p_project_role_id IN NUMBER,
331 p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
332 p_resource_type_id IN NUMBER := 101, --EMPLOYEE
333 p_resource_source_id IN NUMBER,
334 p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
335 p_resource_id IN NUMBER := FND_API.G_MISS_NUM,
336 p_start_date_active IN DATE,
337 p_scheduled_flag IN VARCHAR2 := 'N',
338 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
339 p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
340 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
341 p_project_end_date IN DATE := FND_API.G_MISS_DATE,
342 p_project_party_id IN NUMBER,
343 p_assignment_id IN NUMBER := 0,
344 p_assign_record_version_number IN NUMBER := 0,
345 p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
346 p_end_date_active IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
347 x_assignment_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
348 x_wf_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
349 x_wf_item_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
350 x_wf_process OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
351 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
352 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
353 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
354
355 l_error_occured VARCHAR2(1) := 'N';
356 x_assignment_action VARCHAR2(20) := 'NOACTION';
357 l_api_version NUMBER := 1.0;
358 l_api_name VARCHAR2(30) := 'Update_project_party';
359 l_record_version_number NUMBER;
360 l_project_party_id NUMBER;
361 l_project_id NUMBER;
362 l_resource_source_id NUMBER;
363 l_resource_type_id NUMBER;
364 l_project_role_id NUMBER;
365 l_project_start_date DATE;
366 l_project_end_date DATE;
367 l_start_date_active DATE;
368 l_msg_index_out NUMBER;
369 l_error_message_code fnd_new_messages.message_name%TYPE;
370 l_check_id_flag VARCHAR2(1); -- Added for bug 4947618
371
372 BEGIN
373 SAVEPOINT update_project_party;
374 --- Standard call to check for call compatibility
375 if (p_debug_mode = 'Y') then
376 pa_debug.debug('Update_project_party: Checking he api version number.');
377 end if;
378
379 if NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
380 l_api_name, 'PA_PROJECT_PARTIES_PUB') then
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 end if;
383
384 ------dbms_output.put_line('Before initializing the stack');
385 pa_debug.init_err_stack('Update_project_party_pub');
386
387 --- Initialize the message stack if required
388
389 if (p_debug_mode = 'Y') then
390 pa_debug.debug('Update_project_party: Initializing message stack.');
391 end if;
392
393 if FND_API.to_boolean(nvl(p_init_msg_list,FND_API.G_TRUE)) then
394 fnd_msg_pub.initialize;
395 end if;
396
397 ------dbms_output.put_line('After initializing the stack');
398
399 x_return_status := FND_API.G_RET_STS_SUCCESS;
400
401 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
402 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
403 else
404 l_project_role_id := p_project_role_id;
405 end if;
406
407 /* if p_resource_source_id is null or p_resource_source_id = FND_API.G_MISS_NUM then
408 l_resource_source_id := pa_project_parties_utils.get_resource_source_id(p_resource_name);
409 else
410 l_resource_source_id := p_resource_source_id;
411 end if; */
412
413 if p_project_id is not null and p_project_id <> FND_API.G_MISS_NUM then
414 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_DATES(p_project_id => p_project_id,
415 x_project_start_date => l_project_start_date,
416 x_project_end_date => l_project_end_date,
417 x_return_status => x_return_status);
418 end if;
419
420 if p_project_id is null or p_project_id = FND_API.G_MISS_NUM then
421 l_project_id := null;
422 else
423 l_project_id := p_project_id;
424 end if;
425
426
427 if p_start_date_active is null or p_start_date_active = FND_API.G_MISS_DATE then
428 --- if l_project_start_date is not null then
429 --- l_start_date_active := l_project_start_date;
430 --- else
431 l_start_date_active := trunc(sysdate);
432 --- end if;
433 else
434 l_start_date_active := p_start_date_active;
435 end if;
436
437 /* Start of code for Bug 4947618 */
438
439 IF p_calling_module = 'PROJECT_MEMBER' THEN
440 l_check_id_flag :='Y';
441 ELSE
442 l_check_id_flag := PA_STARTUP.G_Check_ID_Flag;
443 END IF;
444
445 /* End of code for Bug 4947618 */
446
447 PA_RESOURCE_UTILS.Check_ResourceName_Or_Id ( p_resource_id => p_resource_source_id
448 ,p_resource_type_id => p_resource_type_id
449 ,p_resource_name => p_resource_name
450 ,p_check_id_flag => l_check_id_flag --bug 4947618 PA_STARTUP.G_Check_ID_Flag
451 ,p_date => l_start_date_active
452 ,x_resource_id => l_resource_source_id
453 ,x_resource_type_id => l_resource_type_id
454 ,x_return_status => x_return_status
455 ,x_error_message_code => l_error_message_code);
456
457 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
458 fnd_message.set_name('PA',l_error_message_code);
459 fnd_msg_pub.add;
460 end if;
461
462 if l_project_role_id <> -999 and l_resource_source_id <> -999 and x_return_status = FND_API.G_RET_STS_SUCCESS then
463 PA_PROJECT_PARTIES_PVT.UPDATE_PROJECT_PARTY( p_commit => p_commit,
464 p_validate_only => p_validate_only,
465 p_validation_level => p_validation_level,
466 p_debug_mode => p_debug_mode,
467 p_object_id => p_object_id,
468 p_OBJECT_TYPE => p_object_type,
469 p_project_role_id => l_project_role_id,
470 p_resource_type_id => l_resource_type_id,
471 p_resource_source_id => l_resource_source_id,
472 p_resource_id => p_resource_id,
473 p_start_date_active => l_start_date_active,
474 p_scheduled_flag => p_scheduled_flag,
475 p_record_version_number => p_record_version_number,
476 p_calling_module => p_calling_module,
477 p_project_id => l_project_id,
478 p_project_end_date => l_project_end_date,
479 p_project_party_id => p_project_party_id,
480 p_assignment_id => p_assignment_id,
481 p_assign_record_version_number => p_assign_record_version_number,
482 p_end_date_active => p_end_date_active,
483 p_mgr_validation_type => p_mgr_validation_type,
484 x_assignment_id => x_assignment_id,
485 x_wf_type => x_wf_type,
486 x_wf_item_type => x_wf_item_type,
487 x_wf_process => x_wf_type,
488 x_return_status => x_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data);
491 else
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 end if;
494
495 fnd_msg_pub.count_and_get(p_count => x_msg_count,
496 p_data => x_msg_data);
497
498 IF x_msg_count = 1 THEN
499 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
500 ,p_msg_index => 1
501 ,p_data => x_msg_data
502 ,p_msg_index_out => l_msg_index_out
503 );
504 END IF;
505
506 pa_debug.reset_err_stack;
507
508 EXCEPTION
509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
512 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
513 fnd_message.set_token('PROCEDURE_NAME','UPDATE_PROJECT_PARTY');
514 fnd_msg_pub.add();
515 fnd_msg_pub.count_and_get(p_count => x_msg_count,
516 p_data => x_msg_data);
517 raise;
518
519
520 WHEN OTHERS THEN
521 rollback to update_project_party;
522 x_return_status := fnd_api.g_ret_sts_error;
523 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
524 p_procedure_name => pa_debug.g_err_stack,
525 p_error_text => SUBSTRB(SQLERRM,1,240));
526 raise;
527
528 end update_project_party;
529
530
531 PROCEDURE DELETE_PROJECT_PARTY( p_api_version IN NUMBER := 1.0,
532 /* modified the default value for p_init_msg_list from FND_API.G_TRUE to FND_API.G_FALSE, for
533 the bug#1851096 */
534 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
535 p_commit IN VARCHAR2 := FND_API.G_FALSE,
536 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
537 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
538 p_debug_mode IN VARCHAR2 default 'N',
539 p_record_version_number IN NUMBER := FND_API.G_MISS_NUM,
540 p_calling_module IN VARCHAR2 := FND_API.G_MISS_CHAR,
541 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
542 p_project_party_id IN NUMBER := FND_API.G_MISS_NUM,
543 p_scheduled_flag IN VARCHAR2 default 'N',
544 p_assignment_id IN NUMBER := 0,
545 p_assign_record_version_number IN NUMBER := 0,
546 p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
547 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
548 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
549 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
550
551 l_project_id NUMBER := 0;
552 API_ERROR EXCEPTION;
553 l_api_version NUMBER := 1.0;
554 l_api_name VARCHAR2(30) := 'delete_project_party';
555 l_msg_index_out NUMBER;
556 l_task_cnt NUMBER := 0;
557 --Changes for 8726175
558 cursor task_cnt_crsr is
559 select count(pt.task_id) from pa_tasks pt where project_id=p_project_id and task_manager_person_id =
560 (select resource_source_id from pa_project_parties where project_party_id=p_project_party_id
561 and project_id=p_project_id)
562 and exists
563 (select 1 from pa_progress_rollup where project_id=pt.project_id and proj_element_id = pt.task_id
564 and structure_version_id is null
565 );
566 BEGIN
567 --- Standard call to check for call compatibility
568 if (p_debug_mode = 'Y') then
569 pa_debug.debug('Delete_project_party: Checking he api version number.');
570 end if;
571
572 if NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
573 l_api_name, 'PA_PROJECT_PARTIES_PUB') then
574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
575 end if;
576
577 ------dbms_output.put_line('Before initializing the stack');
578 pa_debug.init_err_stack('Delete_project_party_pub');
579
580 --- Initialize the message stack if required
581
582 if (p_debug_mode = 'Y') then
583 pa_debug.debug('Delete_project_party: Initializing message stack.');
584 end if;
585
586 if FND_API.to_boolean(nvl(p_init_msg_list,fnd_api.G_TRUE)) then
587 fnd_msg_pub.initialize;
588 end if;
589
590 ------dbms_output.put_line('After initializing the stack');
591
592
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594 --changes for 8726175 starts
595 open task_cnt_crsr;
596 fetch task_cnt_crsr into l_task_cnt;
597 if l_task_cnt > 0
598 then
599 FND_MESSAGE.Set_Name('PA', 'PA_PARTY_PROGR_TASK');
600 fnd_msg_pub.add();
601 fnd_msg_pub.count_and_get(p_count => x_msg_count,
602 p_data => x_msg_data);
603 x_return_status := FND_API.G_RET_STS_ERROR;
604
605 else
606 if (p_debug_mode = 'Y') then
607 pa_debug.debug('Delete_project_party : Before delete from pa_project_players ');
608 end if;
609
610 if p_scheduled_flag = 'Y' and p_calling_module = 'PROJECT_MEMBER' then
611 -- call delete assignment api
612 PA_ASSIGNMENTS_PUB.Delete_Assignment
613 ( p_assignment_id => p_assignment_id
614 ,p_assignment_type => 'STAFFED_ASSIGNMENT'
615 ,p_record_version_number => p_assign_record_version_number
616 ,p_commit => p_commit
617 ,p_validate_only => FND_API.G_FALSE
618 ,p_init_msg_list => FND_API.G_TRUE
619 ,x_return_status => x_return_status
620 ,x_msg_count => x_msg_count
621 ,x_msg_data => x_msg_data
622 );
623 else
624 PA_PROJECT_PARTIES_PVT.DELETE_PROJECT_PARTY( p_commit => p_commit,
625 p_validate_only => p_validate_only,
626 p_validation_level => p_validation_level,
627 p_debug_mode => p_debug_mode,
628 p_record_version_number => p_record_version_number,
629 p_calling_module => p_calling_module,
630 p_project_id => p_project_id,
631 p_project_party_id => p_project_party_id,
632 p_scheduled_flag => p_scheduled_flag,
633 p_assignment_id => p_assignment_id,
634 p_assign_record_version_number => p_assign_record_version_number,
635 p_mgr_validation_type => p_mgr_validation_type,
636 x_return_status => x_return_status,
637 x_msg_count => x_msg_count,
638 x_msg_data => x_msg_data);
639 end if;
640
641 fnd_msg_pub.count_and_get(p_count => x_msg_count,
642 p_data => x_msg_data);
643
644 IF x_msg_count = 1 THEN
645 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
646 ,p_msg_index => 1
647 ,p_data => x_msg_data
648 ,p_msg_index_out => l_msg_index_out
649 );
650 END IF;
651 end if; --changes for 8726175 ends
652
653 pa_debug.reset_err_stack;
654
655 EXCEPTION
656
657 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
659 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
660 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
661 fnd_message.set_token('PROCEDURE_NAME','DELETE_PROJECT_PARTY');
662 fnd_msg_pub.add();
663 fnd_msg_pub.count_and_get(p_count => x_msg_count,
664 p_data => x_msg_data);
665 raise;
666
667 WHEN NO_DATA_FOUND THEN
668
669 if (p_debug_mode = 'Y') then
670 pa_debug.debug('Delete_project_party : Exception NO_DATA_FOUND ');
671 end if;
672
673 fnd_message.set_name('PA', 'PA_XC_NO_DATA_FOUND');
674 x_return_status := FND_API.G_RET_STS_ERROR;
675
676 WHEN OTHERS then
677
678 if (p_debug_mode = 'Y') then
679 pa_debug.debug('Delete_project_party : Exception OTHERS ');
680 end if;
681
682 if(SQLCODE = -54) then
683 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
684 FND_MESSAGE.Set_token('ENTITY', 'PA_PROJECT_PARTIES');
685 FND_MESSAGE.Set_token('PROJECT',to_char(P_PROJECT_ID));
686 FND_MESSAGE.Set_token('TASK',NULL);
687 x_msg_data := FND_MESSAGE.get;
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 else
690 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
691 p_procedure_name => pa_debug.g_err_stack,
692 p_error_text => SUBSTRB(SQLERRM,1,240));
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 end if;
695
696 END DELETE_PROJECT_PARTY;
697
698
699 -- API name : get_key_member_start_date
700 -- Type : Public
701 -- Pre-reqs : None.
702 -- Parameters :
703 -- p_project_id IN NUMBER REQUIRED
704 --
705 -- History
706 --
707 -- 28-MAY-2002 anlee Created
708 --
709 --
710 -- Purpose
711 -- This API is used to calculate the key member start date
712 -- based on the project start date.
713 -- It is called in CREATE_PROJECT_PARTY, and is used to
714 -- default key member start dates when a project is created.
715 -- The implemented functionality is as follows:
716 --
717 -- IF project_start date <= sysdate
718 -- return project start date
719 --
720 -- IF project start date > sysdate
721 -- return sysdate
722 --
723 -- This function may be modified if the logic for defaulting
724 -- key member start date at project creation time needs to
725 -- be changed.
726 -- Changes made for the bug 2686120
727 -- Added a new parameter p_person_id to the function to change the
728 -- defaulting mechanism.
729 -- If the project start date is greater than the sysdate
730 -- then default the key member start date to the employee start date.
731 FUNCTION GET_KEY_MEMBER_START_DATE (p_project_id IN NUMBER)
732
733
734
735
736 return DATE
737 IS
738
739 /* Bug 2636791 - We will be selecting start date
740 by calling PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE API*/
741 /*
742 CURSOR date_csr IS
743 SELECT start_date from pa_projects_all
744 WHERE project_id = p_project_id;
745 */
746
747
748 l_project_start_date DATE := NULL;
749
750 BEGIN
751 /*
752 OPEN date_csr;
753 FETCH date_csr INTO l_project_start_date;
754 CLOSE date_csr;
755 */
756
757
758 l_project_start_date := PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(p_project_id);
759
760 if l_project_start_date is not NULL then
761 if l_project_start_date <= trunc(sysdate) then
762 return l_project_start_date;
763 else
764 return trunc(sysdate);
765 end if;
766 end if;
767
768 return trunc(sysdate);
769
770 EXCEPTION
771 WHEN OTHERS THEN
772 return trunc(sysdate);
773
774 END GET_KEY_MEMBER_START_DATE;
775
776 /*=============================================================================
777 This api is used as a wrapper API to CREATE_PROJECT_PARTY
778 ==============================================================================*/
779
780 PROCEDURE CREATE_PROJECT_PARTY_WRP( p_api_version IN NUMBER := 1.0,
781 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
782 p_commit IN VARCHAR2 := FND_API.G_FALSE,
783 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
784 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
785 p_debug_mode IN VARCHAR2 default 'N',
786 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
787 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
788 p_project_role_id IN NUMBER := FND_API.G_MISS_NUM,
789 p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
790 p_resource_type_id IN NUMBER := 101, --EMPLOYEE
791 p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM,
792 p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
793 p_start_date_active IN DATE := FND_API.G_MISS_DATE,/*Added for bug2774759*/
794 p_scheduled_flag IN VARCHAR2 := 'N',
795 p_calling_module IN VARCHAR2,
796 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
797 p_project_end_date IN DATE := FND_API.G_MISS_DATE,
798 p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
799 p_end_date_active IN OUT NOCOPY DATE,
800 x_project_party_id OUT NOCOPY NUMBER,
801 x_resource_id OUT NOCOPY NUMBER,
802 x_assignment_id OUT NOCOPY NUMBER,
803 x_wf_type OUT NOCOPY VARCHAR2,
804 x_wf_item_type OUT NOCOPY VARCHAR2,
805 x_wf_process OUT NOCOPY VARCHAR2,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_count OUT NOCOPY NUMBER,
808 x_msg_data OUT NOCOPY VARCHAR2) IS
809
810
811 l_project_role_id NUMBER; --used here
812 l_key_members pa_project_pub.project_role_tbl_type; --used here
813 l_debug_mode varchar2(1) := 'N';
814 l_data VARCHAR2(2000);
815 l_msg_data VARCHAR2(2000);
816 l_msg_index_out NUMBER;
817 BEGIN
818
819 SAVEPOINT create_project_party_wrp;
820 if p_debug_mode = 'Y' then
821 l_debug_mode:='Y';
822 end if;
823 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
824 l_debug_mode := NVL(l_debug_mode, 'N');
825
826 if (l_debug_mode = 'Y') then
827 pa_debug.debug('create_project_party-wrp: Begin');
828 end if;
829
830 if (l_debug_mode = 'Y') then
831 pa_debug.debug('Create_project_party: Initializing message stack.');
832 end if;
833 IF l_debug_mode = 'Y' THEN
834 pa_debug.set_curr_function( p_function => 'CREATE_PROJECT_PARTY_WRP'
835 ,p_debug_mode => l_debug_mode);
836 END IF;
837 x_return_status := FND_API.G_RET_STS_SUCCESS;
838 x_msg_count := 0;
839
840 /* get the project_role_id */
841 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
842 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
843 else
844 l_project_role_id := p_project_role_id;
845 end if;
846
847 IF l_project_role_id = 1 then
848
849 /* call pa_project_check_pvt.check_for_one_manager_pvt */
850 /*
851 If a project manager is sought to be created, then check whether
852 there is already a project manager for the project. If so, check
853 whether this is the same person. If not,then check the start and
854 end dates for the existing manager and update the end date of the existing manager to either
855 (a) new manager's start date -1 or (b) sysdate -1
856 (being done in check_for_one_manager);
857 */
858
859
860 l_key_members(1).project_role_type := 'PROJECT MANAGER';
861 l_key_members(1).person_id := p_resource_source_id;
862 l_key_members(1).start_date := p_start_date_active;
863 l_key_members(1).end_date := p_end_date_active;
864
865 pa_project_check_pvt.check_for_one_manager_pvt
866 (p_project_id => p_project_id
867 ,p_person_id => p_resource_source_id
868 ,p_key_members => l_key_members
869 ,p_start_date => p_start_date_active
870 ,p_end_date => p_end_date_active
871 ,p_return_status => x_return_status);
872
873 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
874 RAISE FND_API.G_EXC_ERROR;
875 END IF;
876 End If; --If project_role_id = 1
877 /* calling CREATE_PROJECT_PARTY unconditionally for all the key members
878 This flow is similar to AMG API. Instead of calling add_key_members , we
879 are directly calling create_project_party, after checking if a PM is being
880 terminated */
881 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
882 p_validate_only => p_validate_only
883 , p_object_id => p_object_id
884 , p_OBJECT_TYPE => p_OBJECT_TYPE
885 , p_project_role_id => p_project_role_id
886 , p_project_role_type => p_project_role_type
887 , p_RESOURCE_TYPE_ID => p_RESOURCE_TYPE_ID
888 , p_resource_source_id => p_resource_source_id
889 , p_resource_name => p_resource_name
890 , p_start_date_active => p_start_date_active
891 , p_scheduled_flag => p_scheduled_flag
892 , p_calling_module => p_calling_module
893 , p_project_id => p_project_id -- p_project_id
894 , p_project_end_date => p_project_end_date
895 , p_end_date_active => p_end_date_active
896 , p_mgr_validation_type => p_mgr_validation_type
897 , x_project_party_id => x_project_party_id -- x_project_party_id
898 , x_resource_id => x_resource_id -- x_resource_id
899 , x_wf_item_type => x_wf_item_type
900 , x_wf_type => x_wf_type
901 , x_wf_process => x_wf_process
902 , x_assignment_id => x_assignment_id
903 , x_return_status => x_return_status -- x_return_status
904 , x_msg_count => x_msg_count -- x_msg_count
905 , x_msg_data => x_msg_data -- x_msg_data
906 );
907 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) Then
908 x_return_status := x_return_status;
909 x_msg_count := x_msg_count;
910 raise FND_API.G_EXC_ERROR;
911 END IF;
912 /* end if call create_project_party */
913 IF l_debug_mode = 'Y' THEN
914 PA_DEBUG.Reset_Curr_Function;
915 END IF;
916
917 EXCEPTION
918 WHEN FND_API.G_EXC_ERROR
919 THEN
920 rollback to create_project_party_wrp;
921
922 --setting all the OUT and IN OUT parameters to null
923 x_return_status := FND_API.G_RET_STS_ERROR;
924 p_end_date_active := null;
925 x_project_party_id := null;
926 x_resource_id := null;
927 x_assignment_id := null;
928 x_wf_type := null;
929 x_wf_item_type := null;
930 x_wf_process := null;
931 x_msg_count := Fnd_Msg_Pub.count_msg;
932
933
934 IF x_msg_count = 1 AND x_msg_data IS NULL
935 THEN
936 Pa_Interface_Utils_Pub.get_messages
937 ( p_encoded => Fnd_Api.G_TRUE
938 , p_msg_index => 1
939 , p_msg_count => x_msg_count
940 , p_msg_data => l_msg_data
941 , p_data => l_data
942 , p_msg_index_out => l_msg_index_out);
943 x_msg_data := l_data;
944 END IF;
945 WHEN OTHERS THEN
946 rollback to create_project_party_wrp;
947
948 --setting all the OUT and IN OUT parameters to null
949 x_return_status := fnd_api.g_ret_sts_unexp_error;
950 p_end_date_active := null;
951 x_project_party_id := null;
952 x_resource_id := null;
953 x_assignment_id := null;
954 x_wf_type := null;
955 x_wf_item_type := null;
956 x_wf_process := null;
957 x_msg_count := 1;
958 x_msg_data := SUBSTRB(SQLERRM,1,240);
959
960 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
961 p_procedure_name => 'CREATE_PROJECT_PARTY_WRP',
962 p_error_text => x_msg_data);
963
964 END CREATE_PROJECT_PARTY_WRP;
965
966 end;
967