[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_PROFILES_PUB
Source
1 PACKAGE BODY PA_ROLE_PROFILES_PUB AS
2 -- $Header: PARPRPPB.pls 120.1 2005/08/19 16:59:18 mwasowic noship $
3 --
4 -- PROCEDURE
5 -- Add_Default_Profile
6 -- PURPOSE
7 -- This procedure adds new default profile.
8
9 PROCEDURE Add_Default_Profile
10 ( p_business_group_id IN NUMBER DEFAULT NULL,
11 p_business_group_name IN VARCHAR2 DEFAULT NULL,
12 p_organization_id IN NUMBER DEFAULT NULL,
13 p_organization_name IN VARCHAR2 DEFAULT NULL,
14 p_job_id IN NUMBER DEFAULT NULL,
15 p_job_name IN VARCHAR2 DEFAULT NULL,
16 p_position_id IN NUMBER DEFAULT NULL,
17 p_position_name IN VARCHAR2 DEFAULT NULL,
18 p_profile_name IN VARCHAR2,
19 p_description IN VARCHAR2,
20 p_effective_start_date IN DATE,
21 p_effective_end_date IN DATE DEFAULT NULL,
22 p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
23 p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
24 p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
25 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
26 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
27 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
28
29 l_business_group_id NUMBER;
30 l_organization_id NUMBER;
31 l_job_id NUMBER;
32 l_position_id NUMBER;
33 l_profile_id NUMBER;
34 l_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
35 l_exists VARCHAR2(1) := 'N';
36 l_error_msg_code VARCHAR2(500);
37 l_return_status VARCHAR2(1);
38 l_prev_profile_id NUMBER := NULL;
39 l_msg_index_out NUMBER := 0;
40 l_end_date DATE := NULL;
41 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
42
43 BEGIN
44 IF l_enable_log = 'Y' THEN
45 PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Add_Default_Profile');
46 END IF;
47
48 -- Clear the global PL/SQL message table
49 FND_MSG_PUB.initialize;
50
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52
53 IF p_effective_end_date IS NOT NULL THEN
54 IF p_effective_start_date > p_effective_end_date THEN
55 pa_utils.add_message (p_app_short_name => 'PA',
56 p_msg_name => 'PA_INVALID_ASGMT_DATES');
57 RAISE FND_API.G_EXC_ERROR;
58 END IF;
59 END IF;
60
61 PA_ROLE_PROFILES_UTILS.Check_Business_Level_Attrs
62 ( p_business_group_id => p_business_group_id,
63 p_business_group_name => p_business_group_name,
64 p_organization_id => p_organization_id,
65 p_organization_name => p_organization_name,
66 p_job_id => p_job_id,
67 p_job_name => p_job_name,
68 p_position_id => p_position_id,
69 p_position_name => p_position_name,
70 x_business_group_id => l_business_group_id,
71 x_organization_id => l_organization_id,
72 x_job_id => l_job_id,
73 x_position_id => l_position_id,
74 x_return_status => l_return_status,
75 x_msg_count => x_msg_count,
76 x_msg_data => x_msg_data ) ;
77
78 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
79 RAISE FND_API.G_EXC_ERROR;
80 END IF;
81
82 --Next, validate profile lines
83
84 PA_ROLE_PROFILES_UTILS.Validate_Profile_Lines
85 ( p_role_id_tbl => p_role_id_tbl,
86 p_role_name_tbl => p_role_name_tbl,
87 p_weighting_tbl => p_weighting_tbl,
88 x_role_id_tbl => l_role_id_tbl,
89 x_return_status => l_return_status,
90 x_msg_count => x_msg_count,
91 x_msg_data => x_msg_data );
92
93 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
94 RAISE FND_API.G_EXC_ERROR;
95 END IF;
96
97 --dbms_output.put_line ('l_business_group_id ' || l_business_group_id);
98 --dbms_output.put_line ('l_org_id ' || l_organization_id);
99 --dbms_output.put_line ('l_pos_id ' || l_position_id);
100 --dbms_output.put_line ('l_jobid ' || l_job_id);
101 --dbms_output.put_line ('p_effective_start_date ' || p_effective_start_date);
102 --dbms_output.put_line ('effective_end_date ' || p_effective_end_date);
103
104 BEGIN
105 SELECT 'Y'
106 INTO l_exists
107 FROM pa_role_profiles
108 WHERE resource_id IS NULL
109 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
110 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
111 AND NVL(job_id, -1) = NVL(l_job_id, -1)
112 AND NVL(position_id, -1) = NVL(l_position_id, -1)
113 AND TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
114 AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
115 AND rownum = 1;
116
117 pa_utils.add_message (p_app_short_name => 'PA',
118 p_msg_name => 'PA_PROFILE_DATE_CONFLICT');
119 RAISE FND_API.G_EXC_ERROR;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 null;
123 END;
124
125 BEGIN
126 --Check if p_start_date is in between any start_date, end_date combination
127 SELECT 'Y'
128 INTO l_exists
129 FROM pa_role_profiles
130 WHERE resource_id IS NULL
131 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
132 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
133 AND NVL(job_id, -1) = NVL(l_job_id, -1)
134 AND NVL(position_id, -1) = NVL(l_position_id, -1)
135 AND TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
136 AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date));
137
138 pa_utils.add_message (p_app_short_name => 'PA',
139 p_msg_name => 'PA_PROFILE_DATE_CONFLICT');
140 RAISE FND_API.G_EXC_ERROR;
141
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 --dbms_output.put_line ('1.0');
145 null;
146 END;
147
148
149 --dbms_output.put_line ('3.1');
150 -- Check if the previous profile is en-dated
151
152 BEGIN
153 SELECT profile_id,
154 effective_end_date
155 INTO l_prev_profile_id,
156 l_end_date
157 FROM pa_role_profiles
158 WHERE effective_start_date = (SELECT max(effective_start_date)
159 FROM pa_role_profiles
160 WHERE resource_id IS NULL
161 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
162 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
163 AND NVL(job_id, -1) = NVL(l_job_id, -1)
164 AND NVL(position_id, -1) = NVL(l_position_id, -1)
165 AND TRUNC(effective_start_date) < TRUNC(p_effective_start_date)
166 AND effective_end_date IS NULL)
167 AND resource_id IS NULL
168 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
169 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
170 AND NVL(job_id, -1) = NVL(l_job_id, -1)
171 AND NVL(position_id, -1) = NVL(l_position_id, -1);
172
173 --dbms_output.put_line ('l_prev_profile_id ' || l_prev_profile_id);
174 -- End-date previous profile as its end date is null
175 UPDATE pa_role_profiles
176 SET effective_end_date = p_effective_start_date - 1
177 WHERE profile_id = l_prev_profile_id;
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 null;
182 END;
183
184 --dbms_output.put_line ('4');
185
186 PA_ROLE_PROFILES_PKG.Insert_Row1
187 ( p_profile_name => p_profile_name,
188 p_description => p_description,
189 p_effective_start_date => p_effective_start_date,
190 p_effective_end_date => p_effective_end_date,
191 p_profile_type_code => 'ACTUAL',
192 p_business_group_id => l_business_group_id,
193 p_organization_id => l_organization_id,
194 p_job_id => l_job_id,
195 p_position_id => l_position_id,
196 x_profile_id => l_profile_id,
197 x_return_status => l_return_status);
198
199 FOR i IN 1..l_role_id_tbl.count LOOP
200 PA_ROLE_PROFILES_PKG.Insert_Row2
201 ( p_profile_id => l_profile_id,
202 p_project_role_id => l_role_id_tbl(i),
203 p_role_weighting => p_weighting_tbl(i),
204 x_return_status => l_return_status);
205 END LOOP;
206
207 -- pa_role_profile_lines table does not need sequence
208
209 EXCEPTION
210 WHEN FND_API.G_EXC_ERROR THEN
211 x_return_status := FND_API.G_RET_STS_ERROR;
212 x_msg_count := FND_MSG_PUB.Count_Msg;
213
214 IF x_msg_count = 1 THEN
215 pa_interface_utils_pub.get_messages (p_encoded => FND_API.G_TRUE,
216 p_msg_index => 1,
217 p_data => x_msg_data,
218 p_msg_index_out => l_msg_index_out );
219 END IF;
220 WHEN OTHERS THEN
221 RAISE;
222 END Add_Default_Profile;
223
224 --
225 -- PROCEDURE
226 -- Update_Default_Profile
227 -- PURPOSE
228 -- This procedure updates an existing default profile.
229
230 PROCEDURE Update_Default_Profile
231 ( p_profile_id IN NUMBER,
232 p_business_group_id IN NUMBER DEFAULT NULL,
233 p_business_group_name IN VARCHAR2 DEFAULT NULL,
234 p_organization_id IN NUMBER DEFAULT NULL,
235 p_organization_name IN VARCHAR2 DEFAULT NULL,
236 p_job_id IN NUMBER DEFAULT NULL,
237 p_job_name IN VARCHAR2 DEFAULT NULL,
238 p_position_id IN NUMBER DEFAULT NULL,
239 p_position_name IN VARCHAR2 DEFAULT NULL,
240 p_profile_name IN VARCHAR2,
241 p_description IN VARCHAR2,
242 p_effective_start_date IN DATE,
243 p_effective_end_date IN DATE DEFAULT NULL,
244 p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
245 p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
246 p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
247 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
248 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
249 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
250
251 l_business_group_id NUMBER;
252 l_organization_id NUMBER;
253 l_job_id NUMBER;
254 l_position_id NUMBER;
255 l_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
256 l_exists VARCHAR2(1) := 'N';
257 l_error_msg_code VARCHAR2(500);
258 l_return_status VARCHAR2(1);
259 l_prev_profile_id NUMBER := NULL;
260 l_msg_index_out NUMBER := 0;
261 l_end_date DATE := NULL;
262 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
263
264 BEGIN
265 IF l_enable_log = 'Y' THEN
266 PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Update_Default_Profile');
267 END IF;
268
269 -- Clear the global PL/SQL message table
270 FND_MSG_PUB.initialize;
271
272 x_return_status := FND_API.G_RET_STS_SUCCESS;
273
274 IF p_effective_end_date IS NOT NULL THEN
275 IF p_effective_start_date > p_effective_end_date THEN
276 pa_utils.add_message (p_app_short_name => 'PA',
277 p_msg_name => 'PA_INVALID_ASGMT_DATES');
278 RAISE FND_API.G_EXC_ERROR;
279 END IF;
280 END IF;
281
282 PA_ROLE_PROFILES_UTILS.Check_Business_Level_Attrs
283 ( p_business_group_id => p_business_group_id,
284 p_business_group_name => p_business_group_name,
285 p_organization_id => p_organization_id,
286 p_organization_name => p_organization_name,
287 p_job_id => p_job_id,
288 p_job_name => p_job_name,
289 p_position_id => p_position_id,
290 p_position_name => p_position_name,
291 x_business_group_id => l_business_group_id,
292 x_organization_id => l_organization_id,
293 x_job_id => l_job_id,
294 x_position_id => l_position_id,
295 x_return_status => l_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data ) ;
298
299 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 --Next, validate profile lines
304
305 PA_ROLE_PROFILES_UTILS.Validate_Profile_Lines
306 ( p_role_id_tbl => p_role_id_tbl,
307 p_role_name_tbl => p_role_name_tbl,
308 p_weighting_tbl => p_weighting_tbl,
309 x_role_id_tbl => l_role_id_tbl,
310 x_return_status => l_return_status,
311 x_msg_count => x_msg_count,
312 x_msg_data => x_msg_data );
313
314 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
315 RAISE FND_API.G_EXC_ERROR;
316 END IF;
317
318 --dbms_output.put_line ('l_business_group_id ' || l_business_group_id);
319 --dbms_output.put_line ('l_org_id ' || l_organization_id);
320 --dbms_output.put_line ('l_pos_id ' || l_position_id);
321 --dbms_output.put_line ('l_jobid ' || l_job_id);
322 --dbms_output.put_line ('p_effective_start_date ' || p_effective_start_date);
323 --dbms_output.put_line ('effective_end_date ' || p_effective_end_date);
324
325 BEGIN
326 SELECT 'Y'
327 INTO l_exists
328 FROM pa_role_profiles
329 WHERE resource_id IS NULL
330 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
331 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
332 AND NVL(job_id, -1) = NVL(l_job_id, -1)
333 AND NVL(position_id, -1) = NVL(l_position_id, -1)
334 AND TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
335 AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
336 AND profile_id <> p_profile_id
337 AND rownum = 1;
338
339 pa_utils.add_message (p_app_short_name => 'PA',
340 p_msg_name => 'PA_PROFILE_DATE_CONFLICT');
341 RAISE FND_API.G_EXC_ERROR;
342 EXCEPTION
343 WHEN NO_DATA_FOUND THEN
344 null;
345 END;
346
347 BEGIN
348 --Check if p_start_date is in between any start_date, end_date combination
349 SELECT 'Y'
350 INTO l_exists
351 FROM pa_role_profiles
352 WHERE resource_id IS NULL
353 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
354 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
355 AND NVL(job_id, -1) = NVL(l_job_id, -1)
356 AND NVL(position_id, -1) = NVL(l_position_id, -1)
357 AND TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
358 AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date))
359 AND profile_id <> p_profile_id;
360
361 pa_utils.add_message (p_app_short_name => 'PA',
362 p_msg_name => 'PA_PROFILE_DATE_CONFLICT');
363 RAISE FND_API.G_EXC_ERROR;
364
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 --dbms_output.put_line ('1.0');
368 null;
369 END;
370
371 --dbms_output.put_line ('3.1');
372 -- Check if the previous profile is en-dated
373
374 BEGIN
375 SELECT profile_id,
376 effective_end_date
377 INTO l_prev_profile_id,
378 l_end_date
379 FROM pa_role_profiles
380 WHERE effective_start_date = (SELECT max(effective_start_date)
381 FROM pa_role_profiles
382 WHERE resource_id IS NULL
383 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
384 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
385 AND NVL(job_id, -1) = NVL(l_job_id, -1)
386 AND NVL(position_id, -1) = NVL(l_position_id, -1)
387 AND TRUNC(effective_start_date) < TRUNC(p_effective_start_date)
388 AND effective_end_date IS NULL
389 AND profile_id <> p_profile_id)
390 AND resource_id IS NULL
391 AND NVL(business_group_id, -1) = NVL(l_business_group_id, -1)
392 AND NVL(organization_id, -1) = NVL(l_organization_id, -1)
393 AND NVL(job_id, -1) = NVL(l_job_id, -1)
394 AND NVL(position_id, -1) = NVL(l_position_id, -1)
395 AND profile_id <> p_profile_id;
396
397 --dbms_output.put_line ('l_prev_profile_id ' || l_prev_profile_id);
398 -- End-date previous profile as its end date is null
399 UPDATE pa_role_profiles
400 SET effective_end_date = p_effective_start_date - 1
401 WHERE profile_id = l_prev_profile_id;
402
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 null;
406 END;
407
408 --dbms_output.put_line ('4');
409
410 -- Update current profile
411 PA_ROLE_PROFILES_PKG.Update_Row
412 ( p_profile_id => p_profile_id,
413 p_profile_name => p_profile_name,
414 p_description => p_description,
415 p_effective_start_date => p_effective_start_date,
416 p_effective_end_date => p_effective_end_date,
417 p_business_group_id => l_business_group_id,
418 p_organization_id => l_organization_id,
419 p_job_id => l_job_id,
420 p_position_id => l_position_id,
421 x_return_status => l_return_status);
422
423 -- Delete all roles in the pa_role_profile_lines table
424 DELETE FROM pa_role_profile_lines
425 WHERE profile_id = p_profile_id;
426
427 -- Insert roles into the pa_role_profile_lines table
428 FOR i IN 1..l_role_id_tbl.count LOOP
429 PA_ROLE_PROFILES_PKG.Insert_Row2
430 ( p_profile_id => p_profile_id,
431 p_project_role_id => l_role_id_tbl(i),
432 p_role_weighting => p_weighting_tbl(i),
433 x_return_status => l_return_status);
434 END LOOP;
435
436 -- pa_role_profile_lines table does not need sequence
437
438 EXCEPTION
439 WHEN FND_API.G_EXC_ERROR THEN
440 x_return_status := FND_API.G_RET_STS_ERROR;
441 x_msg_count := FND_MSG_PUB.Count_Msg;
442
443 IF x_msg_count = 1 THEN
444 pa_interface_utils_pub.get_messages (p_encoded => FND_API.G_TRUE,
445 p_msg_index => 1,
446 p_data => x_msg_data,
447 p_msg_index_out => l_msg_index_out );
448 END IF;
449 WHEN OTHERS THEN
450 RAISE;
451 END Update_Default_Profile;
452
453 PROCEDURE Delete_Profile
454 ( p_profile_id IN NUMBER,
455 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
456 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
457 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
458
459 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
460
461 BEGIN
462 x_return_status := FND_API.G_RET_STS_SUCCESS;
463 x_msg_count := 0;
464 x_msg_data := null;
465
466 DELETE FROM pa_role_profiles
467 WHERE profile_id = p_profile_id;
468
469 DELETE FROM pa_role_profile_lines
470 WHERE profile_id = p_profile_id;
471 EXCEPTION
472 WHEN OTHERS THEN
473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
474 RAISE;
475 End Delete_Profile;
476
477 PROCEDURE Create_Profile_for_Resource
478 ( p_resource_id IN NUMBER,
479 p_resource_start_date IN DATE,
480 p_resource_end_date IN DATE,
481 p_business_group_id IN NUMBER,
482 p_organization_id IN NUMBER,
483 p_job_id IN NUMBER,
484 p_position_id IN NUMBER,
485 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
486 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
487 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
488
489 l_profile_id NUMBER := -1;
490 l_res_profile_id NUMBER;
491 l_max_weight NUMBER := 0;
492 l_business_group_id NUMBER := NVL(p_business_group_id, -1);
493 l_organization_id NUMBER := NVL(p_organization_id, -1);
494 l_job_id NUMBER := NVL(p_job_id, -1);
495 l_position_id NUMBER := NVL(p_position_id, -1);
496 l_profile_name VARCHAR2(80);
497 l_description VARCHAR2(250);
498 l_profile_type_code VARCHAR2(30);
499 l_profile_start_date DATE;
500 l_profile_end_date DATE;
501 l_end_date DATE := p_resource_end_date;
502 l_return_status VARCHAR2(1);
503 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
504
505 TYPE number_tbl IS TABLE OF NUMBER
506 INDEX BY BINARY_INTEGER;
507
508 l_role_id_tbl number_tbl;
509 l_weighting_tbl number_tbl;
510
511 CURSOR cur IS
512 SELECT (decode(position_id, null, 0, 4)
513 + decode(job_id, null, 0, 3)
514 + decode(organization_id, null, 0, 2)
515 + decode(business_group_id, null, 0, 1)) AS weight,
516 profile_id
517 FROM pa_role_profiles
518 WHERE resource_id IS NULL
519 AND NVL(position_id,l_position_id) = l_position_id
520 AND NVL(job_id, l_job_id) = l_job_id
521 AND NVL(organization_id,l_organization_id) = l_organization_id
522 AND NVL(business_group_id,l_business_group_id) = l_business_group_id
523 AND TRUNC(p_resource_start_date) BETWEEN TRUNC(effective_start_date)
524 AND NVL(TRUNC(effective_end_date), TRUNC(p_resource_start_date));
525
526 c1 cur%ROWTYPE;
527
528 BEGIN
529 x_return_status := FND_API.G_RET_STS_SUCCESS;
530
531 FOR c1 in cur LOOP
532 --dbms_output.put_line ('profile_id: ' || c1.profile_id);
533 --dbms_output.put_line ('profile_weighting: ' || c1.weight);
534
535 IF l_max_weight < c1.weight THEN
536 l_max_weight := c1.weight;
537 l_profile_id := c1.profile_id;
538 END IF;
539 END LOOP;
540
541 --dbms_output.put_line ('l_profile_id: ' || l_profile_id);
542
543 IF l_profile_id <> -1 THEN
544 SELECT profile_name,
545 description,
546 effective_start_date,
547 effective_end_date,
548 profile_type_code
549 INTO l_profile_name,
550 l_description,
551 l_profile_start_date,
552 l_profile_end_date,
553 l_profile_type_code
554 FROM pa_role_profiles
555 WHERE profile_id = l_profile_id;
556
557 -- resource_start_date will always be prior to profile_start_date
558 -- Need to check end_date of the new resource profile
559
560 IF l_profile_end_date IS NULL THEN
561 l_end_date := p_resource_end_date;
562 ELSIF l_profile_end_date < p_resource_end_date THEN
563 l_end_date := l_profile_end_date;
564 END IF;
565
566 --dbms_output.put_line ('l_end_date: ' || l_end_date);
567
568 PA_ROLE_PROFILES_PKG.Insert_Row1
569 ( p_profile_name => l_profile_name,
570 p_description => l_description,
571 p_effective_start_date => p_resource_start_date,
572 p_effective_end_date => l_end_date,
573 p_profile_type_code => 'ACTUAL',
574 p_approval_status_code => PA_ASSIGNMENT_APPROVAL_PUB.g_approved,
575 p_resource_id => p_resource_id,
576 x_profile_id => l_res_profile_id,
577 x_return_status => l_return_status);
578
579 SELECT project_role_id,
580 role_weighting
581 BULK COLLECT INTO l_role_id_tbl,
582 l_weighting_tbl
583 FROM pa_role_profile_lines
584 WHERE profile_id = l_profile_id;
585
586 FOR i IN 1..l_role_id_tbl.count LOOP
587 PA_ROLE_PROFILES_PKG.Insert_Row2
588 ( p_profile_id => l_res_profile_id,
589 p_project_role_id => l_role_id_tbl(i),
590 p_role_weighting => l_weighting_tbl(i),
591 x_return_status => l_return_status);
592 END LOOP;
593 END IF;
594
595 END Create_Profile_for_Resource;
596
597 END PA_ROLE_PROFILES_PUB;