[Home] [Help]
PACKAGE BODY: APPS.PA_TASK_MANAGER
Source
1 package body PA_TASK_MANAGER as
2 /* $Header: PATMUPGB.pls 120.1 2005/08/08 04:25:58 avaithia noship $ */
3
4
5 /* Function to get the profile option value */
6
7 function get_profile_value (p_name IN VARCHAR2) return VARCHAR2 IS
8
9 l_value FND_PROFILE_OPTION_VALUES.PROFILE_OPTION_VALUE%TYPE;
10
11 begin
12
13 /** If the profile option cannot be found, the out buffer is set to NULL
14 ** Since a profile value can never be set to NULL,
15 ** if this returns a NULL you know the profile doesn't exist. **/
16
17 fnd_profile.get(p_name, l_value);
18 return nvl(l_value, 'N');
19
20 end get_profile_value;
21
22
23 /* Procedure to check the break periods if the task manager is existing as a project member */
24
25 procedure validate_member_exists ( p_project_id IN NUMBER,
26 p_task_manager_person_id IN NUMBER,
27 p_proj_role_id IN NUMBER,
28 p_start_date_active IN DATE,
29 p_end_date_active IN DATE,
30 p_project_end_date IN DATE) IS
31
32 CURSOR c_member_end_date (p_project_id IN NUMBER,
33 p_task_manager_person_id IN NUMBER) IS
34 select 1
35 from pa_project_parties pp
36 where pp.project_id = p_project_id
37 and resource_source_id = p_task_manager_person_id
38 and project_role_id = p_proj_role_id
39 and pp.end_date_active is NULL;
40
41 CURSOR c_proj_member_exists (p_project_id IN NUMBER,
42 p_task_manager_person_id IN NUMBER) IS
43 select start_date_active, end_date_active
44 from pa_project_parties
45 where project_id = p_project_id
46 and resource_source_id = p_task_manager_person_id
47 and project_role_id = p_proj_role_id
48 order by start_date_active; /* Important to process in order. Please do not remove. */
49
50
51
52 l_member_end_date_active DATE;
53 l_dummy1 NUMBER;
54
55 l_start_date_active DATE;
56 l_end_date_active DATE;
57 l_project_end_date DATE;
58 l_create_project_member VARCHAR2(1);
59 l_min_member_start_date DATE;
60 l_end_date_active_tmp DATE;
61 v_null_char VARCHAR2(1);
62 x_return_status VARCHAR2(255);
63 x_msg_count NUMBER;
64 x_msg_data VARCHAR2(2000);
65 x_project_party_id NUMBER;
66 x_resource_id NUMBER;
67 l_wf_item_type VARCHAR2(30);
68 l_wf_type VARCHAR2(30);
69 l_wf_party_process VARCHAR2(30);
70 l_assignment_id NUMBER;
71 l_msg_index_out NUMBER;
72
73 l_debug_mode VARCHAR2(1);
74 l_tmp_str fnd_new_messages.message_text%TYPE;
75
76 begin
77
78 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
79 l_debug_mode := NVL(l_debug_mode, 'N');
80 l_tmp_str := fnd_message.get_string('PA', 'PA_TM_CR_PROJ_MEMBER');
81
82 l_start_date_active := p_start_date_active;
83 l_end_date_active := p_end_date_active;
84 l_project_end_date := p_project_end_date;
85
86 select max(end_date_active)
87 into l_member_end_date_active
88 from pa_project_parties
89 where project_id = p_project_id
90 and resource_source_id = p_task_manager_person_id
91 and project_role_id = p_proj_role_id;
92
93 open c_member_end_date(p_project_id, p_task_manager_person_id);
94 fetch c_member_end_date into l_dummy1;
95 if c_member_end_date%FOUND then
96 l_member_end_date_active := NULL;
97 close c_member_end_date;
98 else
99 close c_member_end_date;
100 end if;
101
102 if (l_member_end_date_active is NULL) then /* l_end_date_active is NULL or NOT NULL both conditions are included */
103
104 l_create_project_member := 'Y';
105 for l_proj_member_exists_rec in c_proj_member_exists (p_project_id, p_task_manager_person_id) LOOP
106 if l_proj_member_exists_rec.end_date_active is NULL then
107 if (l_start_date_active >= l_proj_member_exists_rec.start_date_active) then
108 l_create_project_member := 'N';
109
110 if l_debug_mode = 'Y' then
111 tm_log('6 The person is existing as a project member and the period is overlapping');
112 end if;
113
114 EXIT;
115 elsif l_end_date_active is null then
116 l_end_date_active := l_proj_member_exists_rec.start_date_active - 1;
117 end if;
118 end if;
119 END LOOP;
120
121 if l_create_project_member = 'Y' then
122
123 select min(start_date_active)
124 into l_min_member_start_date
125 from pa_project_parties
126 where project_id = p_project_id
127 and resource_source_id = p_task_manager_person_id
128 and project_role_id = p_proj_role_id;
129
130 if (l_start_date_active < l_min_member_start_date) then
131
132 l_end_date_active_tmp := l_min_member_start_date - 1;
133
134 if l_debug_mode = 'Y' then
135 tm_log('7 The person is existing as a project member. ');
136 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
137 end if;
138
139 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
140 p_api_version => 1.0
141 , p_init_msg_list => FND_API.G_TRUE
142 , p_commit => FND_API.G_FALSE
143 , p_validate_only => FND_API.G_FALSE
144 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
145 , p_debug_mode => 'Y'
146 , p_object_id => p_project_id
147 , p_OBJECT_TYPE => 'PA_PROJECTS'
148 , p_project_role_id => p_proj_role_id
149 , p_project_role_type => NULL
150 , p_RESOURCE_TYPE_ID => 101
151 , p_resource_source_id => p_task_manager_person_id
152 , p_resource_name => v_null_char
153 , p_start_date_active => l_start_date_active
154 , p_scheduled_flag => 'N'
155 , p_calling_module => 'FORM'
156 , p_project_id => p_project_id
157 , p_project_end_date => l_project_end_date
158 , p_end_date_active => l_end_date_active_tmp
159 , x_project_party_id => x_project_party_id
160 , x_resource_id => x_resource_id
161 , x_wf_item_type => l_wf_item_type
162 , x_wf_type => l_wf_type
163 , x_wf_process => l_wf_party_process
164 , x_assignment_id => l_assignment_id
165 , x_return_status => x_return_status
166 , x_msg_count => x_msg_count
167 , x_msg_data => x_msg_data);
168 /* Code added for Bug#2701884, starts here */
169 IF l_debug_mode = 'Y' then
170 if x_msg_count = 0 then
171 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
172 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
173 end if;
174 END IF;
175 /* Code added for Bug#2701884, ends here */
176
177 l_start_date_active := l_min_member_start_date;
178
179 IF l_debug_mode = 'Y' then
180 FOR I IN 1 .. X_MSG_COUNT LOOP
181 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
182 ,p_msg_index => x_msg_count
183 ,p_data => x_msg_data
184 ,p_msg_index_out => l_msg_index_out);
185 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
186 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
187 END LOOP;
188 END IF;
189
190 end if;
191
192 for l_proj_member_exists_rec in c_proj_member_exists (p_project_id, p_task_manager_person_id) LOOP
193
194 if (l_start_date_active < l_proj_member_exists_rec.start_date_active) then
195 if (l_end_date_active = (l_proj_member_exists_rec.start_date_active - 1)) then
196 l_end_date_active_tmp := l_proj_member_exists_rec.start_date_active - 1;
197 else
198 l_end_date_active_tmp := l_end_date_active;
199 end if;
200
201 if l_debug_mode = 'Y' then
202 tm_log('8 The person is existing as a project member. ');
203 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
204 end if;
205
206 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
207 p_api_version => 1.0
208 , p_init_msg_list => FND_API.G_TRUE
209 , p_commit => FND_API.G_FALSE
210 , p_validate_only => FND_API.G_FALSE
211 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
212 , p_debug_mode => 'Y'
213 , p_object_id => p_project_id
214 , p_OBJECT_TYPE => 'PA_PROJECTS'
215 , p_project_role_id => p_proj_role_id
216 , p_project_role_type => NULL
217 , p_RESOURCE_TYPE_ID => 101
218 , p_resource_source_id => p_task_manager_person_id
219 , p_resource_name => v_null_char
220 , p_start_date_active => l_start_date_active
221 , p_scheduled_flag => 'N'
222 , p_calling_module => 'FORM'
223 , p_project_id => p_project_id
224 , p_project_end_date => l_project_end_date
225 , p_end_date_active => l_end_date_active_tmp
226 , x_project_party_id => x_project_party_id
227 , x_resource_id => x_resource_id
228 , x_wf_item_type => l_wf_item_type
229 , x_wf_type => l_wf_type
230 , x_wf_process => l_wf_party_process
231 , x_assignment_id => l_assignment_id
232 , x_return_status => x_return_status
233 , x_msg_count => x_msg_count
234 , x_msg_data => x_msg_data);
235 /* Code added for Bug#2701884, starts here */
236 IF l_debug_mode = 'Y' then
237 if x_msg_count = 0 then
238 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
239 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
240 end if;
241 END IF;
242 /* Code added for Bug#2701884, ends here */
243
244 l_start_date_active := l_proj_member_exists_rec.start_date_active;
245
246 IF l_debug_mode = 'Y' then
247 FOR I IN 1 .. X_MSG_COUNT LOOP
248 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
249 ,p_msg_index => x_msg_count
250 ,p_data => x_msg_data
251 ,p_msg_index_out => l_msg_index_out);
252 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
253 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
254 END LOOP;
255 END IF;
256
257 end if;
258
259 if (l_proj_member_exists_rec.start_date_active between l_start_date_active and l_end_date_active
260 or l_proj_member_exists_rec.end_date_active between l_start_date_active and l_end_date_active) then
261
262 l_start_date_active := l_proj_member_exists_rec.end_date_active + 1;
263
264 end if;
265 END LOOP;
266
267 if (l_end_date_active - l_start_date_active) >= 0 then
268
269 if l_debug_mode = 'Y' then
270 tm_log('9 The person is existing as a project member. ');
271 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active);
272 end if;
273
274 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
275 p_api_version => 1.0
276 , p_init_msg_list => FND_API.G_TRUE
277 , p_commit => FND_API.G_FALSE
278 , p_validate_only => FND_API.G_FALSE
279 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
280 , p_debug_mode => 'Y'
281 , p_object_id => p_project_id
282 , p_OBJECT_TYPE => 'PA_PROJECTS'
283 , p_project_role_id => p_proj_role_id
284 , p_project_role_type => NULL
285 , p_RESOURCE_TYPE_ID => 101
286 , p_resource_source_id => p_task_manager_person_id
287 , p_resource_name => v_null_char
288 , p_start_date_active => l_start_date_active
289 , p_scheduled_flag => 'N'
290 , p_calling_module => 'FORM'
291 , p_project_id => p_project_id
292 , p_project_end_date => l_project_end_date
293 , p_end_date_active => l_end_date_active
294 , x_project_party_id => x_project_party_id
295 , x_resource_id => x_resource_id
296 , x_wf_item_type => l_wf_item_type
297 , x_wf_type => l_wf_type
298 , x_wf_process => l_wf_party_process
299 , x_assignment_id => l_assignment_id
300 , x_return_status => x_return_status
301 , x_msg_count => x_msg_count
302 , x_msg_data => x_msg_data);
303
304 IF l_debug_mode = 'Y' then
305 FOR I IN 1 .. X_MSG_COUNT LOOP
306 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
307 ,p_msg_index => x_msg_count
308 ,p_data => x_msg_data
309 ,p_msg_index_out => l_msg_index_out);
310 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
311 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
312 END LOOP;
313 /* Code added for Bug#2701884, starts here */
314 if x_msg_count = 0 then
315 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
316 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active);
317 end if;
318 /* Code added for Bug#2701884, ends here */
319 END IF;
320
321 end if;
322 end if;
323 elsif (l_member_end_date_active is NOT NULL and l_end_date_active is NOT NULL) then
324
325 select min(start_date_active)
326 into l_min_member_start_date
327 from pa_project_parties
328 where project_id = p_project_id
329 and resource_source_id = p_task_manager_person_id
330 and project_role_id = p_proj_role_id;
331
332 if (l_start_date_active < l_min_member_start_date) then
333
334 l_end_date_active_tmp := l_min_member_start_date - 1;
335
336 if l_debug_mode = 'Y' then
337 tm_log('10 The person is existing as a project member. ');
338 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
339 end if;
340
341 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
342 p_api_version => 1.0
343 , p_init_msg_list => FND_API.G_TRUE
344 , p_commit => FND_API.G_FALSE
345 , p_validate_only => FND_API.G_FALSE
346 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
347 , p_debug_mode => 'Y'
348 , p_object_id => p_project_id
349 , p_OBJECT_TYPE => 'PA_PROJECTS'
350 , p_project_role_id => p_proj_role_id
351 , p_project_role_type => NULL
352 , p_RESOURCE_TYPE_ID => 101
353 , p_resource_source_id => p_task_manager_person_id
354 , p_resource_name => v_null_char
355 , p_start_date_active => l_start_date_active
356 , p_scheduled_flag => 'N'
357 , p_calling_module => 'FORM'
358 , p_project_id => p_project_id
359 , p_project_end_date => l_project_end_date
360 , p_end_date_active => l_end_date_active_tmp
361 , x_project_party_id => x_project_party_id
362 , x_resource_id => x_resource_id
363 , x_wf_item_type => l_wf_item_type
364 , x_wf_type => l_wf_type
365 , x_wf_process => l_wf_party_process
366 , x_assignment_id => l_assignment_id
367 , x_return_status => x_return_status
368 , x_msg_count => x_msg_count
369 , x_msg_data => x_msg_data);
370
371 /* Code added for Bug#2701884, starts here */
372 IF l_debug_mode = 'Y' then
373 if x_msg_count = 0 then
374 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
375 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
376 end if;
377 END IF;
378 /* Code added for Bug#2701884, ends here */
379
380 l_start_date_active := l_min_member_start_date;
381
382 IF l_debug_mode = 'Y' then
383 FOR I IN 1 .. X_MSG_COUNT LOOP
384 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
385 ,p_msg_index => x_msg_count
386 ,p_data => x_msg_data
387 ,p_msg_index_out => l_msg_index_out);
388 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
389 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
390 END LOOP;
391 END IF;
392
393 end if;
394
395 for l_proj_member_exists_rec in c_proj_member_exists (p_project_id,
396 p_task_manager_person_id) LOOP
397
398 if (l_start_date_active < l_proj_member_exists_rec.start_date_active) then
399
400 l_end_date_active_tmp := l_proj_member_exists_rec.start_date_active - 1;
401
402 if l_debug_mode = 'Y' then
403 tm_log('11 The person is existing as a project member. ');
404 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
405 end if;
406
407 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
408 p_api_version => 1.0
409 , p_init_msg_list => FND_API.G_TRUE
410 , p_commit => FND_API.G_FALSE
411 , p_validate_only => FND_API.G_FALSE
412 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
413 , p_debug_mode => 'Y'
414 , p_object_id => p_project_id
415 , p_OBJECT_TYPE => 'PA_PROJECTS'
416 , p_project_role_id => p_proj_role_id
417 , p_project_role_type => NULL
418 , p_RESOURCE_TYPE_ID => 101
419 , p_resource_source_id => p_task_manager_person_id
420 , p_resource_name => v_null_char
421 , p_start_date_active => l_start_date_active
422 , p_scheduled_flag => 'N'
423 , p_calling_module => 'FORM'
424 , p_project_id => p_project_id
425 , p_project_end_date => l_project_end_date
426 , p_end_date_active => l_end_date_active_tmp
427 , x_project_party_id => x_project_party_id
428 , x_resource_id => x_resource_id
429 , x_wf_item_type => l_wf_item_type
430 , x_wf_type => l_wf_type
431 , x_wf_process => l_wf_party_process
432 , x_assignment_id => l_assignment_id
433 , x_return_status => x_return_status
434 , x_msg_count => x_msg_count
435 , x_msg_data => x_msg_data);
436
437 /* Code added for Bug#2701884, starts here */
438 IF l_debug_mode = 'Y' then
439 if x_msg_count = 0 then
440 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
441 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
442 end if;
443 END IF;
444 /* Code added for Bug#2701884, ends here */
445
446 l_start_date_active := l_proj_member_exists_rec.start_date_active;
447
448 IF l_debug_mode = 'Y' then
449 FOR I IN 1 .. X_MSG_COUNT LOOP
450 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
451 ,p_msg_index => x_msg_count
452 ,p_data => x_msg_data
453 ,p_msg_index_out => l_msg_index_out);
454 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
455 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
456 END LOOP;
457 END IF;
458
459 end if;
460
461 if (l_proj_member_exists_rec.start_date_active between l_start_date_active and l_end_date_active
462 or l_proj_member_exists_rec.end_date_active between l_start_date_active and l_end_date_active) then
463
464 l_start_date_active := l_proj_member_exists_rec.end_date_active + 1;
465
466 end if;
467 END LOOP;
468
469 if (l_end_date_active - l_start_date_active) >= 0 then
470
471 if l_debug_mode = 'Y' then
472 tm_log('12 The person is existing as a project member. ');
473 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active);
474 end if;
475
476 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
477 p_api_version => 1.0
478 , p_init_msg_list => FND_API.G_TRUE
479 , p_commit => FND_API.G_FALSE
480 , p_validate_only => FND_API.G_FALSE
481 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
482 , p_debug_mode => 'Y'
483 , p_object_id => p_project_id
484 , p_OBJECT_TYPE => 'PA_PROJECTS'
485 , p_project_role_id => p_proj_role_id
486 , p_project_role_type => NULL
487 , p_RESOURCE_TYPE_ID => 101
488 , p_resource_source_id => p_task_manager_person_id
489 , p_resource_name => v_null_char
490 , p_start_date_active => l_start_date_active
491 , p_scheduled_flag => 'N'
492 , p_calling_module => 'FORM'
493 , p_project_id => p_project_id
494 , p_project_end_date => l_project_end_date
495 , p_end_date_active => l_end_date_active
496 , x_project_party_id => x_project_party_id
497 , x_resource_id => x_resource_id
498 , x_wf_item_type => l_wf_item_type
499 , x_wf_type => l_wf_type
500 , x_wf_process => l_wf_party_process
501 , x_assignment_id => l_assignment_id
502 , x_return_status => x_return_status
503 , x_msg_count => x_msg_count
504 , x_msg_data => x_msg_data);
505
506 IF l_debug_mode = 'Y' then
507 FOR I IN 1 .. X_MSG_COUNT LOOP
508 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
509 ,p_msg_index => x_msg_count
510 ,p_data => x_msg_data
511 ,p_msg_index_out => l_msg_index_out);
512 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
513 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
514 END LOOP;
515 /* Code added for Bug#2701884, starts here */
516 if x_msg_count = 0 then
517 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
518 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active);
519 end if;
520 /* Code added for Bug#2701884, ends here * /
521 END IF;
522
523 end if;
524
525 elsif (l_member_end_date_active is NOT NULL and l_end_date_active is NULL) then
526
527 select min(start_date_active)
528 into l_min_member_start_date
529 from pa_project_parties
530 where project_id = p_project_id
531 and resource_source_id = p_task_manager_person_id
532 and project_role_id = p_proj_role_id;
533
534 if (l_start_date_active < l_min_member_start_date) then
535
536 l_end_date_active_tmp := l_min_member_start_date - 1;
537
538 if l_debug_mode = 'Y' then
539 tm_log('13 The person is existing as a project member. ');
540 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
541 end if;
542
543 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
544 p_api_version => 1.0
545 , p_init_msg_list => FND_API.G_TRUE
546 , p_commit => FND_API.G_FALSE
547 , p_validate_only => FND_API.G_FALSE
548 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
549 , p_debug_mode => 'Y'
550 , p_object_id => p_project_id
551 , p_OBJECT_TYPE => 'PA_PROJECTS'
552 , p_project_role_id => p_proj_role_id
553 , p_project_role_type => NULL
554 , p_RESOURCE_TYPE_ID => 101
555 , p_resource_source_id => p_task_manager_person_id
556 , p_resource_name => v_null_char
557 , p_start_date_active => l_start_date_active
558 , p_scheduled_flag => 'N'
559 , p_calling_module => 'FORM'
560 , p_project_id => p_project_id
561 , p_project_end_date => l_project_end_date
562 , p_end_date_active => l_end_date_active_tmp
563 , x_project_party_id => x_project_party_id
564 , x_resource_id => x_resource_id
565 , x_wf_item_type => l_wf_item_type
566 , x_wf_type => l_wf_type
567 , x_wf_process => l_wf_party_process
568 , x_assignment_id => l_assignment_id
569 , x_return_status => x_return_status
570 , x_msg_count => x_msg_count
571 , x_msg_data => x_msg_data);
572
573 /* Code added for Bug#2701884, starts here */
574 IF l_debug_mode = 'Y' then
575 if x_msg_count = 0 then
576 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
577 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
578 end if;
579 END IF;
580 /* Code added for Bug#2701884, ends here */
581
582 l_start_date_active := l_min_member_start_date;
583
584 IF l_debug_mode = 'Y' then
585 FOR I IN 1 .. X_MSG_COUNT LOOP
586 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
587 ,p_msg_index => x_msg_count
588 ,p_data => x_msg_data
589 ,p_msg_index_out => l_msg_index_out);
590 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
591 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
592 END LOOP;
593 END IF;
594
595 end if;
596
597 for l_proj_member_exists_rec in c_proj_member_exists (p_project_id, p_task_manager_person_id) LOOP
598
599 if (l_start_date_active < l_proj_member_exists_rec.start_date_active) then
600
601 l_end_date_active_tmp := l_proj_member_exists_rec.start_date_active - 1;
602
603 if l_debug_mode = 'Y' then
604 tm_log('14 The person is existing as a project member. ');
605 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active_tmp);
606 end if;
607
608 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
609 p_api_version => 1.0
610 , p_init_msg_list => FND_API.G_TRUE
611 , p_commit => FND_API.G_FALSE
612 , p_validate_only => FND_API.G_FALSE
613 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
614 , p_debug_mode => 'Y'
615 , p_object_id => p_project_id
616 , p_OBJECT_TYPE => 'PA_PROJECTS'
617 , p_project_role_id => p_proj_role_id
618 , p_project_role_type => NULL
619 , p_RESOURCE_TYPE_ID => 101
620 , p_resource_source_id => p_task_manager_person_id
621 , p_resource_name => v_null_char
622 , p_start_date_active => l_start_date_active
623 , p_scheduled_flag => 'N'
624 , p_calling_module => 'FORM'
625 , p_project_id => p_project_id
626 , p_project_end_date => l_project_end_date
627 , p_end_date_active => l_end_date_active_tmp
628 , x_project_party_id => x_project_party_id
629 , x_resource_id => x_resource_id
630 , x_wf_item_type => l_wf_item_type
631 , x_wf_type => l_wf_type
632 , x_wf_process => l_wf_party_process
633 , x_assignment_id => l_assignment_id
634 , x_return_status => x_return_status
635 , x_msg_count => x_msg_count
636 , x_msg_data => x_msg_data);
637
638 /* Code added for Bug#2701884, starts here */
639 IF l_debug_mode = 'Y' then
640 if x_msg_count = 0 then
641 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
642 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active_tmp);
643 end if;
644 END IF;
645 /* Code added for Bug#2701884, ends here */
646
647 l_start_date_active := l_proj_member_exists_rec.start_date_active;
648
649 IF l_debug_mode = 'Y' then
650 FOR I IN 1 .. X_MSG_COUNT LOOP
651 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
652 ,p_msg_index => x_msg_count
653 ,p_data => x_msg_data
654 ,p_msg_index_out => l_msg_index_out);
655 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
656 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
657 END LOOP;
658 END IF;
659
660 end if;
661
662 if (l_proj_member_exists_rec.start_date_active >= l_start_date_active
663 or l_proj_member_exists_rec.end_date_active >= l_start_date_active) then
664
665 l_start_date_active := l_proj_member_exists_rec.end_date_active + 1;
666
667 end if;
668 END LOOP;
669
670 if l_debug_mode = 'Y' then
671 tm_log('15 The person is existing as a project member. ');
672 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active);
673 end if;
674
675 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
676 p_api_version => 1.0
677 , p_init_msg_list => FND_API.G_TRUE
678 , p_commit => FND_API.G_FALSE
679 , p_validate_only => FND_API.G_FALSE
680 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
681 , p_debug_mode => 'Y'
682 , p_object_id => p_project_id
683 , p_OBJECT_TYPE => 'PA_PROJECTS'
684 , p_project_role_id => p_proj_role_id
685 , p_project_role_type => NULL
686 , p_RESOURCE_TYPE_ID => 101
687 , p_resource_source_id => p_task_manager_person_id
688 , p_resource_name => v_null_char
689 , p_start_date_active => l_start_date_active
690 , p_scheduled_flag => 'N'
691 , p_calling_module => 'FORM'
692 , p_project_id => p_project_id
693 , p_project_end_date => l_project_end_date
694 , p_end_date_active => l_end_date_active
695 , x_project_party_id => x_project_party_id
696 , x_resource_id => x_resource_id
697 , x_wf_item_type => l_wf_item_type
698 , x_wf_type => l_wf_type
699 , x_wf_process => l_wf_party_process
700 , x_assignment_id => l_assignment_id
701 , x_return_status => x_return_status
702 , x_msg_count => x_msg_count
703 , x_msg_data => x_msg_data);
704
705 IF l_debug_mode = 'Y' then
706 FOR I IN 1 .. X_MSG_COUNT LOOP
707 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
708 ,p_msg_index => x_msg_count
709 ,p_data => x_msg_data
710 ,p_msg_index_out => l_msg_index_out);
711 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
712 tm_out(p_project_id, p_task_manager_person_id, x_msg_data);
713 END LOOP;
714 /* Code added for Bug#2701884, starts here */
715 if x_msg_count = 0 then
716 tm_out(p_project_id, p_task_manager_person_id, l_tmp_str);
717 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active);
718 end if;
719 /* Code added for Bug#2701884, ends here */
720 END IF;
721 end if;
722 end if;
723 end validate_member_exists;
724
725
726 /* Main Procedure to upgrade the task managers as project members */
727
728 procedure upgrade_task_manager ( errbuf OUT NOCOPY VARCHAR2, /*Added Nocopy for 4537865 */
729 retcode OUT NOCOPY VARCHAR2, /*Added Nocopy for 4537865 */
730 p_project_num_from IN VARCHAR2,
731 p_project_num_to IN VARCHAR2,
732 p_project_role IN VARCHAR2,
733 p_project_org IN NUMBER,
734 p_project_type IN VARCHAR2) IS
735
736 CURSOR c_selprojs IS
737 select p.project_id
738 from pa_projects_all p
739 where p.segment1 between p_project_num_from and p_project_num_to
740 and p.carrying_out_organization_id = nvl(p_project_org, p.carrying_out_organization_id)
741 and p.project_type = nvl(p_project_type, p.project_type);
742
743 CURSOR c_seltaskmgrs (p_project_id IN NUMBER) IS
744 select distinct t.task_manager_person_id
745 from pa_tasks t
746 where t.project_id = p_project_id
747 and t.task_manager_person_id is not null;
748
749 CURSOR c_task_end_date_active (p_project_id IN NUMBER,
750 p_task_manager_person_id IN NUMBER) IS
751 select 1
752 from pa_tasks t
753 where t.project_id = p_project_id
754 and t.task_manager_person_id = p_task_manager_person_id
755 and t.completion_date is NULL;
756
757 CURSOR c_proj_member (p_project_id IN NUMBER,
758 p_task_manager_person_id IN NUMBER) IS
759 select 1
760 from pa_project_parties
761 where project_id = p_project_id
762 and resource_source_id = p_task_manager_person_id
763 and project_role_id = p_project_role;
764
765 l_start_date pa_tasks.start_date%TYPE;
766 l_dummy NUMBER;
767 l_dummy1 NUMBER;
768 l_mgr_start_date DATE;
769 l_mgr_end_date DATE;
770 v_null_char VARCHAR2(1);
771 x_return_status VARCHAR2(255);
772 x_msg_count NUMBER;
773 x_msg_data VARCHAR2(2000);
774 x_project_party_id NUMBER;
775 x_resource_id NUMBER;
776 l_wf_item_type VARCHAR2(30);
777 l_wf_type VARCHAR2(30);
778 l_wf_party_process VARCHAR2(30);
779 l_assignment_id NUMBER;
780 l_project_start_date DATE;
781 l_project_end_date DATE;
782
783 l_start_date_active DATE;
784 l_end_date_active DATE;
785 l_mgr_end_date_active DATE;
786 l_create_project_member VARCHAR2(1);
787 l_member_end_date_active DATE;
788 l_role_type pa_project_role_types.project_role_type%TYPE;
789 l_proj_counter NUMBER;
790
791 l_msg_index_out NUMBER;
792
793 l_debug_mode VARCHAR2(1);
794 l_tmp_str fnd_new_messages.message_text%TYPE; /* Bug#2701884 */
795
796 BEGIN
797
798 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
799 l_debug_mode := NVL(l_debug_mode, 'N');
800
801 l_tmp_str := fnd_message.get_string('PA', 'PA_TM_CR_PROJ_MEMBER'); /* Bug#2701884 */
802
803 -- if l_debug_mode = 'Y' then /* Commented for the 2701884 */
804 tm_log('l_debug_mode: '||l_debug_mode);
805 tm_log('Profile PA_TM_PROJ_MEMBER value: '||get_profile_value('PA_TM_PROJ_MEMBER'));
806 -- end if; /* Commented for the 2701884 */
807
808 select project_role_type
809 into l_role_type
810 from pa_project_role_types
811 where project_role_id = p_project_role;
812
813 /* This program should run only if the profile
814 'PA: Task Managers restricted to Project Members' is set to yes. */
815
816
817 IF get_profile_value('PA_TM_PROJ_MEMBER') = 'Y' THEN
818
819 print_output(p_project_num_from,
820 p_project_num_to,
821 l_role_type,
822 p_project_org,
823 p_project_type);
824
825 l_proj_counter := 0;
826
827 for l_selprojs_rec in c_selprojs LOOP
828
829 l_proj_counter := l_proj_counter + 1;
830
831 PA_PROJECT_PARTIES_UTILS.GET_PROJECT_DATES(p_project_id => l_selprojs_rec.project_id,
832 x_project_start_date => l_project_start_date,
833 x_project_end_date => l_project_end_date,
834 x_return_status => x_return_status);
835 if l_debug_mode = 'Y' then
836 tm_log('project_id: '||l_selprojs_rec.project_id);
837 end if;
838
839 for l_seltaskmgrs_rec in c_seltaskmgrs(l_selprojs_rec.project_id) LOOP
840
841 /* can be converted as a sub procedure, starts here */
842 select min(nvl(t.start_date,l_project_start_date)), max(completion_date)
843 into l_start_date_active, l_end_date_active
844 from pa_tasks t
845 where t.project_id = l_selprojs_rec.project_id
846 and t.task_manager_person_id = l_seltaskmgrs_rec.task_manager_person_id;
847
848 open c_task_end_date_active(l_selprojs_rec.project_id,
849 l_seltaskmgrs_rec.task_manager_person_id);
850 fetch c_task_end_date_active into l_dummy1;
851 if c_task_end_date_active%FOUND then
852 l_end_date_active := NULL;
853 close c_task_end_date_active;
854 else
855 close c_task_end_date_active;
856 end if;
857 /* can be converted as a sub procedure, ends here */
858
859 if l_debug_mode = 'Y' then
860 tm_log('task_manager_person_id: '||l_seltaskmgrs_rec.task_manager_person_id );
861 tm_log('Start Date Active : '||l_start_date_active);
862 tm_log('End Date Active : '||l_end_date_active);
863 end if;
864
865 open c_proj_member(l_selprojs_rec.project_id, l_seltaskmgrs_rec.task_manager_person_id);
866 fetch c_proj_member into l_dummy;
867
868 if c_proj_member%NOTFOUND then
869 close c_proj_member;
870
871 if l_debug_mode = 'Y' then
872 tm_log('5 The person is not existing as a project member.' );
873 tm_log('Creating the project member for the period '||l_start_date_active||' to '||l_end_date_active);
874 end if;
875
876
877 PA_PROJECT_PARTIES_PUB.CREATE_PROJECT_PARTY(
878 p_api_version => 1.0
879 , p_init_msg_list => FND_API.G_TRUE
880 , p_commit => FND_API.G_FALSE
881 , p_validate_only => FND_API.G_FALSE
882 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
883 , p_debug_mode => 'Y'
884 , p_object_id => l_selprojs_rec.project_id
885 , p_OBJECT_TYPE => 'PA_PROJECTS'
886 , p_project_role_id => p_project_role
887 , p_project_role_type => NULL
888 , p_RESOURCE_TYPE_ID => 101
889 , p_resource_source_id => l_seltaskmgrs_rec.task_manager_person_id
890 , p_resource_name => v_null_char
891 , p_start_date_active => l_start_date_active
892 , p_scheduled_flag => 'N'
893 , p_calling_module => 'FORM'
894 , p_project_id => l_selprojs_rec.project_id
895 , p_project_end_date => l_project_end_date
896 , p_end_date_active => l_end_date_active
897 , x_project_party_id => x_project_party_id
898 , x_resource_id => x_resource_id
899 , x_wf_item_type => l_wf_item_type
900 , x_wf_type => l_wf_type
901 , x_wf_process => l_wf_party_process
902 , x_assignment_id => l_assignment_id
903 , x_return_status => x_return_status
904 , x_msg_count => x_msg_count
905 , x_msg_data => x_msg_data);
906
907 IF l_debug_mode = 'Y' then
908 FOR I IN 1 .. X_MSG_COUNT LOOP
909 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_FALSE
910 ,p_msg_index => x_msg_count
911 ,p_data => x_msg_data
912 ,p_msg_index_out => l_msg_index_out);
913 tm_log('*** ERROR MESSAGE ***: '||x_msg_data);
914 tm_out(l_selprojs_rec.project_id, l_seltaskmgrs_rec.task_manager_person_id, x_msg_data);
915 END LOOP;
916 /* Code added for Bug#2701884, starts here */
917 if x_msg_count = 0 then
918 tm_out(l_selprojs_rec.project_id, l_seltaskmgrs_rec.task_manager_person_id, l_tmp_str);
919 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||l_start_date_active||' to '||l_end_date_active);
920 end if;
921 /* Code added for Bug#2701884, ends here */
922 END IF;
923
924 else /* if c_proj_member%NOTFOUND */
925 close c_proj_member;
926
927 if l_debug_mode = 'Y' then
928 tm_log('The person is existing as a project member. Validating the periods.');
929 end if;
930
931 validate_member_exists ( p_project_id => l_selprojs_rec.project_id,
932 p_task_manager_person_id => l_seltaskmgrs_rec.task_manager_person_id,
933 p_proj_role_id => p_project_role,
934 p_start_date_active => l_start_date_active,
935 p_end_date_active => l_end_date_active,
936 p_project_end_date => l_project_end_date);
937
938 end if;
939
940 END LOOP; /* for l_seltaskmgrs_rec in c_seltaskmgrs(l_selprojs_rec.project_id) */
941
942 if l_proj_counter = 100 then
943 COMMIT;
944 end if;
945
946 END LOOP; /* for l_selprojs_rec in c_selprojs */
947 COMMIT;
948
949 END IF; /* if get_profile_value('PA_TM_PROJ_MEMBER') = 'Y' */
950
951 EXCEPTION
952
953 when no_data_found then
954 tm_log('in exception, when no_data_found');
955 tm_log('SQLCODE:'||SQLCODE);
956 tm_log('SQLERRM:'||SQLERRM);
957
958 when others then
959 tm_log('in exception, when others');
960 tm_log('SQLCODE:'||SQLCODE);
961 tm_log('SQLERRM:'||SQLERRM);
962
963 end upgrade_task_manager;
964
965
966 /* procedure to print the debug messages in the log file */
967
968 procedure tm_log (p_message IN VARCHAR2) IS
969 begin
970 FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
971 exception
972 when others then
973 raise;
974 end tm_log;
975
976
977 /* procedure to print the data in the output report for the concurrent process */
978
979 procedure tm_out ( p_project_id IN NUMBER,
980 p_task_manager_person_id IN NUMBER,
981 p_message IN VARCHAR2) IS
982 l_proj_num pa_projects_all.segment1%TYPE;
983 l_emp_name pa_employees_res_v.employee_name%TYPE;
984 len_msg NUMBER;
985 l_count_msg NUMBER;
986 x NUMBER;
987 l_message VARCHAR2(1000);
988
989 begin
990 select segment1
991 into l_proj_num
992 from pa_projects_all
993 where project_id = p_project_id;
994
995 select employee_name
996 into l_emp_name
997 from pa_employees_res_v
998 where person_id = p_task_manager_person_id;
999
1000 select length(p_message)
1001 into len_msg
1002 from dual;
1003
1004 tm_log('len_msg:'||len_msg);
1005
1006 if len_msg > 60 then
1007 x := 60;
1008 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(l_proj_num,30,' ')||' '||rpad(l_emp_name,30,' ')||' '||substr(p_message,1,x));
1009 l_count_msg := floor(len_msg/60);
1010
1011 for i in 2..l_count_msg loop
1012 tm_log('i:'||i);
1013 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||substr(p_message, ((i-1)*60)+1, 60));
1014 x := i*60;
1015 end loop;
1016
1017 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(' ',30,' ')||' '||rpad(' ',30,' ')||' '||substr(p_message,x+1,len_msg));
1018 else
1019 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' '||rpad(l_proj_num,30,' ')||' '||rpad(l_emp_name,30,' ')||' '||p_message);
1020 end if;
1021
1022 end tm_out;
1023
1024
1025 /* procedure to print the header info in the output report for the concurrent process */
1026
1027 procedure print_output (p_project_num_from IN VARCHAR2,
1028 p_project_num_to IN VARCHAR2,
1029 p_project_role IN VARCHAR2,
1030 p_project_org IN NUMBER,
1031 p_project_type IN VARCHAR2) IS
1032
1033 l_sob_id NUMBER;
1034 l_sob_name VARCHAR2(30);
1035 l_tmp_str VARCHAR2(132);
1036 l_tmp_str2 VARCHAR2(132);
1037 l_tmp_str3 VARCHAR2(132);
1038 l_tblock VARCHAR2(132);
1039
1040 begin
1041
1042 SELECT IMP.Set_Of_Books_ID
1043 INTO l_sob_id
1044 FROM PA_Implementations IMP;
1045
1046 SELECT SUBSTRB(GL.Name, 1, 30)
1047 INTO l_sob_name
1048 FROM GL_Sets_Of_Books GL
1049 WHERE GL.Set_Of_Books_ID = l_sob_id;
1050
1051 l_tmp_str := fnd_message.get_string('PA', 'PA_TM_DATE');
1052
1053 SELECT ' '||rpad(l_sob_name,30,' ')||lpad(l_tmp_str,75,' ')||sysdate
1054 INTO l_tblock
1055 FROM DUAL;
1056 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1057 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_tblock);
1058 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 2);
1059
1060 l_tmp_str := fnd_message.get_string('PA', 'PA_TM_RPT_HDR');
1061
1062 SELECT lpad(l_tmp_str,66+length(l_tmp_str)/2,' ')
1063 INTO l_tblock
1064 FROM DUAL;
1065
1066 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_tblock);
1067 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 2);
1068
1069
1070 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------------------------------------');
1071 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1072
1073 l_tmp_str := fnd_message.get_string('PA', 'PA_TM_PROJ_NUM');
1074 l_tmp_str := ' '||rpad(l_tmp_str, 30, ' ');
1075
1076 l_tmp_str2 := fnd_message.get_string('PA', 'PA_TM_TASK_MGR');
1077 l_tmp_str2 := rpad(l_tmp_str2, 30, ' ');
1078
1079 l_tmp_str3 := fnd_message.get_string('PA', 'PA_TM_REASON');
1080 l_tmp_str3 := rpad(l_tmp_str3, 60, ' ');
1081
1082 SELECT l_tmp_str||' '||l_tmp_str2||' '||l_tmp_str3
1083 INTO l_tblock
1084 FROM DUAL;
1085
1086 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, l_tblock);
1087
1088 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1089 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------------------------------------------------------------');
1090 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1091
1092 end;
1093
1094
1095 END PA_TASK_MANAGER;