[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_PARTIES_PUB
Source
1 package body PA_PROJECT_PARTIES_PUB as
2 /* $Header: PARPPPMB.pls 120.5 2008/04/18 14:28:00 kmaddi 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
557 BEGIN
558 --- Standard call to check for call compatibility
559 if (p_debug_mode = 'Y') then
560 pa_debug.debug('Delete_project_party: Checking he api version number.');
561 end if;
562
563 if NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
564 l_api_name, 'PA_PROJECT_PARTIES_PUB') then
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 end if;
567
568 ------dbms_output.put_line('Before initializing the stack');
569 pa_debug.init_err_stack('Delete_project_party_pub');
570
571 --- Initialize the message stack if required
572
573 if (p_debug_mode = 'Y') then
574 pa_debug.debug('Delete_project_party: Initializing message stack.');
575 end if;
576
577 if FND_API.to_boolean(nvl(p_init_msg_list,fnd_api.G_TRUE)) then
578 fnd_msg_pub.initialize;
579 end if;
580
581 ------dbms_output.put_line('After initializing the stack');
582
583
584 x_return_status := FND_API.G_RET_STS_SUCCESS;
585
586
587 if (p_debug_mode = 'Y') then
588 pa_debug.debug('Delete_project_party : Before delete from pa_project_players ');
589 end if;
590
591 if p_scheduled_flag = 'Y' and p_calling_module = 'PROJECT_MEMBER' then
592 -- call delete assignment api
593 PA_ASSIGNMENTS_PUB.Delete_Assignment
594 ( p_assignment_id => p_assignment_id
595 ,p_assignment_type => 'STAFFED_ASSIGNMENT'
596 ,p_record_version_number => p_assign_record_version_number
597 ,p_commit => p_commit
598 ,p_validate_only => FND_API.G_FALSE
599 ,p_init_msg_list => FND_API.G_TRUE
600 ,x_return_status => x_return_status
601 ,x_msg_count => x_msg_count
602 ,x_msg_data => x_msg_data
603 );
604 else
605 PA_PROJECT_PARTIES_PVT.DELETE_PROJECT_PARTY( p_commit => p_commit,
606 p_validate_only => p_validate_only,
607 p_validation_level => p_validation_level,
608 p_debug_mode => p_debug_mode,
609 p_record_version_number => p_record_version_number,
610 p_calling_module => p_calling_module,
611 p_project_id => p_project_id,
612 p_project_party_id => p_project_party_id,
613 p_scheduled_flag => p_scheduled_flag,
614 p_assignment_id => p_assignment_id,
615 p_assign_record_version_number => p_assign_record_version_number,
616 p_mgr_validation_type => p_mgr_validation_type,
617 x_return_status => x_return_status,
618 x_msg_count => x_msg_count,
619 x_msg_data => x_msg_data);
620 end if;
621
622 fnd_msg_pub.count_and_get(p_count => x_msg_count,
623 p_data => x_msg_data);
624
625 IF x_msg_count = 1 THEN
626 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
627 ,p_msg_index => 1
628 ,p_data => x_msg_data
629 ,p_msg_index_out => l_msg_index_out
630 );
631 END IF;
632
633 pa_debug.reset_err_stack;
634
635 EXCEPTION
636
637 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 fnd_message.set_name('PA','PA_UNEXPECTED_ERROR');
640 fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PUB');
641 fnd_message.set_token('PROCEDURE_NAME','DELETE_PROJECT_PARTY');
642 fnd_msg_pub.add();
643 fnd_msg_pub.count_and_get(p_count => x_msg_count,
644 p_data => x_msg_data);
645 raise;
646
647 WHEN NO_DATA_FOUND THEN
648
649 if (p_debug_mode = 'Y') then
650 pa_debug.debug('Delete_project_party : Exception NO_DATA_FOUND ');
651 end if;
652
653 fnd_message.set_name('PA', 'PA_XC_NO_DATA_FOUND');
654 x_return_status := FND_API.G_RET_STS_ERROR;
655
656 WHEN OTHERS then
657
658 if (p_debug_mode = 'Y') then
659 pa_debug.debug('Delete_project_party : Exception OTHERS ');
660 end if;
661
662 if(SQLCODE = -54) then
663 FND_MESSAGE.Set_Name('PA', 'PA_XC_ROW_ALREADY_LOCKED');
664 FND_MESSAGE.Set_token('ENTITY', 'PA_PROJECT_PARTIES');
665 FND_MESSAGE.Set_token('PROJECT',to_char(P_PROJECT_ID));
666 FND_MESSAGE.Set_token('TASK',NULL);
667 x_msg_data := FND_MESSAGE.get;
668 x_return_status := FND_API.G_RET_STS_ERROR;
669 else
670 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
671 p_procedure_name => pa_debug.g_err_stack,
672 p_error_text => SUBSTRB(SQLERRM,1,240));
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 end if;
675
676 END DELETE_PROJECT_PARTY;
677
678
679 -- API name : get_key_member_start_date
680 -- Type : Public
681 -- Pre-reqs : None.
682 -- Parameters :
683 -- p_project_id IN NUMBER REQUIRED
684 --
685 -- History
686 --
687 -- 28-MAY-2002 anlee Created
688 --
689 --
690 -- Purpose
691 -- This API is used to calculate the key member start date
692 -- based on the project start date.
693 -- It is called in CREATE_PROJECT_PARTY, and is used to
694 -- default key member start dates when a project is created.
695 -- The implemented functionality is as follows:
696 --
697 -- IF project_start date <= sysdate
698 -- return project start date
699 --
700 -- IF project start date > sysdate
701 -- return sysdate
702 --
703 -- This function may be modified if the logic for defaulting
704 -- key member start date at project creation time needs to
705 -- be changed.
706 -- Changes made for the bug 2686120
707 -- Added a new parameter p_person_id to the function to change the
708 -- defaulting mechanism.
709 -- If the project start date is greater than the sysdate
710 -- then default the key member start date to the employee start date.
711 FUNCTION GET_KEY_MEMBER_START_DATE (p_project_id IN NUMBER)
712
713
714
715
716 return DATE
717 IS
718
719 /* Bug 2636791 - We will be selecting start date
720 by calling PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE API*/
721 /*
722 CURSOR date_csr IS
723 SELECT start_date from pa_projects_all
724 WHERE project_id = p_project_id;
725 */
726
727
728 l_project_start_date DATE := NULL;
729
730 BEGIN
731 /*
732 OPEN date_csr;
733 FETCH date_csr INTO l_project_start_date;
734 CLOSE date_csr;
735 */
736
737
738 l_project_start_date := PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(p_project_id);
739
740 if l_project_start_date is not NULL then
741 if l_project_start_date <= trunc(sysdate) then
742 return l_project_start_date;
743 else
744 return trunc(sysdate);
745 end if;
746 end if;
747
748 return trunc(sysdate);
749
750 EXCEPTION
751 WHEN OTHERS THEN
752 return trunc(sysdate);
753
754 END GET_KEY_MEMBER_START_DATE;
755
756 /*=============================================================================
757 This api is used as a wrapper API to CREATE_PROJECT_PARTY
758 ==============================================================================*/
759
760 PROCEDURE CREATE_PROJECT_PARTY_WRP( p_api_version IN NUMBER := 1.0,
761 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
762 p_commit IN VARCHAR2 := FND_API.G_FALSE,
763 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
764 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
765 p_debug_mode IN VARCHAR2 default 'N',
766 p_object_id IN NUMBER := FND_API.G_MISS_NUM,
767 p_object_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
768 p_project_role_id IN NUMBER := FND_API.G_MISS_NUM,
769 p_project_role_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
770 p_resource_type_id IN NUMBER := 101, --EMPLOYEE
771 p_resource_source_id IN NUMBER := FND_API.G_MISS_NUM,
772 p_resource_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
773 p_start_date_active IN DATE := FND_API.G_MISS_DATE,/*Added for bug2774759*/
774 p_scheduled_flag IN VARCHAR2 := 'N',
775 p_calling_module IN VARCHAR2,
776 p_project_id IN NUMBER := FND_API.G_MISS_NUM,
777 p_project_end_date IN DATE := FND_API.G_MISS_DATE,
778 p_mgr_validation_type IN VARCHAR2 default 'FORM',/*Added for bug 2111806*/
779 p_end_date_active IN OUT NOCOPY DATE,
780 x_project_party_id OUT NOCOPY NUMBER,
781 x_resource_id OUT NOCOPY NUMBER,
782 x_assignment_id OUT NOCOPY NUMBER,
783 x_wf_type OUT NOCOPY VARCHAR2,
784 x_wf_item_type OUT NOCOPY VARCHAR2,
785 x_wf_process OUT NOCOPY VARCHAR2,
786 x_return_status OUT NOCOPY VARCHAR2,
787 x_msg_count OUT NOCOPY NUMBER,
788 x_msg_data OUT NOCOPY VARCHAR2) IS
789
790
791 l_project_role_id NUMBER; --used here
792 l_key_members pa_project_pub.project_role_tbl_type; --used here
793 l_debug_mode varchar2(1) := 'N';
794 l_data VARCHAR2(2000);
795 l_msg_data VARCHAR2(2000);
796 l_msg_index_out NUMBER;
797 BEGIN
798
799 SAVEPOINT create_project_party_wrp;
800 if p_debug_mode = 'Y' then
801 l_debug_mode:='Y';
802 end if;
803 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
804 l_debug_mode := NVL(l_debug_mode, 'N');
805
806 if (l_debug_mode = 'Y') then
807 pa_debug.debug('create_project_party-wrp: Begin');
808 end if;
809
810 if (l_debug_mode = 'Y') then
811 pa_debug.debug('Create_project_party: Initializing message stack.');
812 end if;
813 IF l_debug_mode = 'Y' THEN
814 pa_debug.set_curr_function( p_function => 'CREATE_PROJECT_PARTY_WRP'
815 ,p_debug_mode => l_debug_mode);
816 END IF;
817 x_return_status := FND_API.G_RET_STS_SUCCESS;
818 x_msg_count := 0;
819
820 /* get the project_role_id */
821 if p_project_role_id is null or p_project_role_id = FND_API.G_MISS_NUM then
822 l_project_role_id := pa_project_parties_utils.get_project_role_id(p_project_role_type,p_calling_module);
823 else
824 l_project_role_id := p_project_role_id;
825 end if;
826
827 IF l_project_role_id = 1 then
828
829 /* call pa_project_check_pvt.check_for_one_manager_pvt */
830 /*
831 If a project manager is sought to be created, then check whether
832 there is already a project manager for the project. If so, check
833 whether this is the same person. If not,then check the start and
834 end dates for the existing manager and update the end date of the existing manager to either
835 (a) new manager's start date -1 or (b) sysdate -1
836 (being done in check_for_one_manager);
837 */
838
839
840 l_key_members(1).project_role_type := 'PROJECT MANAGER';
841 l_key_members(1).person_id := p_resource_source_id;
842 l_key_members(1).start_date := p_start_date_active;
843 l_key_members(1).end_date := p_end_date_active;
844
845 pa_project_check_pvt.check_for_one_manager_pvt
846 (p_project_id => p_project_id
847 ,p_person_id => p_resource_source_id
848 ,p_key_members => l_key_members
849 ,p_start_date => p_start_date_active
850 ,p_end_date => p_end_date_active
851 ,p_return_status => x_return_status);
852
853 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
854 RAISE FND_API.G_EXC_ERROR;
855 END IF;
856 End If; --If project_role_id = 1
857 /* calling CREATE_PROJECT_PARTY unconditionally for all the key members
858 This flow is similar to AMG API. Instead of calling add_key_members , we
859 are directly calling create_project_party, after checking if a PM is being
860 terminated */
861 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
862 p_validate_only => p_validate_only
863 , p_object_id => p_object_id
864 , p_OBJECT_TYPE => p_OBJECT_TYPE
865 , p_project_role_id => p_project_role_id
866 , p_project_role_type => p_project_role_type
867 , p_RESOURCE_TYPE_ID => p_RESOURCE_TYPE_ID
868 , p_resource_source_id => p_resource_source_id
869 , p_resource_name => p_resource_name
870 , p_start_date_active => p_start_date_active
871 , p_scheduled_flag => p_scheduled_flag
872 , p_calling_module => p_calling_module
873 , p_project_id => p_project_id -- p_project_id
874 , p_project_end_date => p_project_end_date
875 , p_end_date_active => p_end_date_active
876 , p_mgr_validation_type => p_mgr_validation_type
877 , x_project_party_id => x_project_party_id -- x_project_party_id
878 , x_resource_id => x_resource_id -- x_resource_id
879 , x_wf_item_type => x_wf_item_type
880 , x_wf_type => x_wf_type
881 , x_wf_process => x_wf_process
882 , x_assignment_id => x_assignment_id
883 , x_return_status => x_return_status -- x_return_status
884 , x_msg_count => x_msg_count -- x_msg_count
885 , x_msg_data => x_msg_data -- x_msg_data
886 );
887 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) Then
888 x_return_status := x_return_status;
889 x_msg_count := x_msg_count;
890 raise FND_API.G_EXC_ERROR;
891 END IF;
892 /* end if call create_project_party */
893 IF l_debug_mode = 'Y' THEN
894 PA_DEBUG.Reset_Curr_Function;
895 END IF;
896
897 EXCEPTION
898 WHEN FND_API.G_EXC_ERROR
899 THEN
900 rollback to create_project_party_wrp;
901
902 --setting all the OUT and IN OUT parameters to null
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 p_end_date_active := null;
905 x_project_party_id := null;
906 x_resource_id := null;
907 x_assignment_id := null;
908 x_wf_type := null;
909 x_wf_item_type := null;
910 x_wf_process := null;
911 x_msg_count := Fnd_Msg_Pub.count_msg;
912
913
914 IF x_msg_count = 1 AND x_msg_data IS NULL
915 THEN
916 Pa_Interface_Utils_Pub.get_messages
917 ( p_encoded => Fnd_Api.G_TRUE
918 , p_msg_index => 1
919 , p_msg_count => x_msg_count
920 , p_msg_data => l_msg_data
921 , p_data => l_data
922 , p_msg_index_out => l_msg_index_out);
923 x_msg_data := l_data;
924 END IF;
925 WHEN OTHERS THEN
926 rollback to create_project_party_wrp;
927
928 --setting all the OUT and IN OUT parameters to null
929 x_return_status := fnd_api.g_ret_sts_unexp_error;
930 p_end_date_active := null;
931 x_project_party_id := null;
932 x_resource_id := null;
933 x_assignment_id := null;
934 x_wf_type := null;
935 x_wf_item_type := null;
936 x_wf_process := null;
937 x_msg_count := 1;
938 x_msg_data := SUBSTRB(SQLERRM,1,240);
939
940 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PUB',
941 p_procedure_name => 'CREATE_PROJECT_PARTY_WRP',
942 p_error_text => x_msg_data);
943
944 END CREATE_PROJECT_PARTY_WRP;
945
946 end;
947