DBA Data[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;