1 PACKAGE BODY pa_role_job_bg_utils AS
2 -- $Header: PAXRJBUB.pls 115.3 2003/08/25 19:01:25 ramurthy ship $
3
4 --
5 -- PROCEDURE
6 -- check_dup_job_bg_defaults
7 -- PURPOSE
8 -- This procedure checks to see that the same Business Group
9 -- has not already had job defaults specified for a given role.
10 -- HISTORY
11 -- 17-Jul-2003 Ranjana Murthy - Created
12 --
13 -- PROCEDURE check_dup_job_bg_defaults
14 -- This procedure checks if the role already has job defaults for the given
15 -- business group - a role can only have one set of job defaults
16 -- for a business group.
17 -- It will be called from the private api before inserting
18 -- a new record into the role job defaults table or before updating
19 -- an existing record
20
21 PROCEDURE check_dup_job_bg_defaults(
22 p_role_job_bg_id IN NUMBER
23 ,p_project_role_id IN NUMBER
24 ,p_business_group_id IN NUMBER
25 ,p_return_status OUT NOCOPY VARCHAR2
26 ,p_error_message_code OUT NOCOPY VARCHAR2) IS
27
28 cursor c_exists is
29 select 'Y'
30 from pa_role_job_bgs
31 where project_role_id = p_project_role_id
32 and nvl(business_group_id, -99) = nvl(p_business_group_id, -99)
33 and role_job_bg_id <> nvl(p_role_job_bg_id, -99);
34
35 l_dummy VARCHAR2(1) ;
36
37 BEGIN
38 -- hr_utility.trace_on(NULL, 'RMDUP');
39 --hr_utility.trace('start');
40 --hr_utility.trace('P_ROLE_JOB_BG_ID IS : ' || P_ROLE_JOB_BG_ID);
41 --hr_utility.trace('P_PROJECT_ROLE_ID IS : ' || P_PROJECT_ROLE_ID);
42 --hr_utility.trace('P_BUSINESS_GROUP_ID IS : ' || P_BUSINESS_GROUP_ID);
43
44 OPEN c_exists;
45 FETCH c_exists into l_dummy;
46 IF c_exists%NOTFOUND THEN
47 p_return_status := fnd_api.g_ret_sts_success;
48 ELSE
49 p_return_status := fnd_api.g_ret_sts_error;
50 p_error_message_code := 'PA_DUP_ROLE_JOB_BG';
51 END IF;
52 CLOSE c_exists;
53 EXCEPTION
54 WHEN OTHERS THEN
55 CLOSE c_exists;
56 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
57 p_error_message_code := SQLCODE;
58 END check_dup_job_bg_defaults;
59
60 -- These functions are called from the view PA_PROJECT_ROLE_TYPES_VL
61 -- to get the job defaults based on the profile values for CBGA and
62 -- HR BG ID.
63
64 FUNCTION get_job_id(p_project_role_id IN NUMBER) return NUMBER IS
65
66 l_job_id NUMBER;
67 l_bg_id NUMBER;
68
69 BEGIN
70
71 -- Using HR Profile BG ID for now - not sure where all this
72 -- is used; this may need to change to go off of implementation options.
73
74 IF PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' THEN
75 l_bg_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
76 ELSE
77 l_bg_id := -1;
78 END IF;
79
80 BEGIN
81
82 SELECT job_id
83 into l_job_id
84 FROM pa_role_job_bgs
85 WHERE project_role_id = p_project_role_id
86 AND nvl(business_group_id, -1) = l_bg_id;
87
88 EXCEPTION
89 WHEN NO_DATA_FOUND THEN
90 l_job_id := NULL;
91 END;
92
93 RETURN l_job_id;
94
95 END get_job_id;
96
97 --
98 FUNCTION get_min_job_level(p_project_role_id IN NUMBER) return NUMBER IS
99
100 l_min_job_level NUMBER;
101 l_bg_id NUMBER;
102
103 BEGIN
104
105 -- Using HR Profile BG ID for now - not sure where all this
106 -- is used; this may need to change to go off of implementation options.
107
108 IF PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' THEN
109 l_bg_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
110 ELSE
111 l_bg_id := -1;
112 END IF;
113
114 BEGIN
115
116 SELECT min_job_level
117 into l_min_job_level
118 FROM pa_role_job_bgs
119 WHERE project_role_id = p_project_role_id
120 AND nvl(business_group_id, -1) = l_bg_id;
121
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 l_min_job_level := NULL;
125 END;
126
127 RETURN l_min_job_level;
128
129 END get_min_job_level;
130
131 --
132 FUNCTION get_max_job_level(p_project_role_id IN NUMBER) return NUMBER IS
133
134 l_max_job_level NUMBER;
135 l_bg_id NUMBER;
136
137 BEGIN
138
139 -- Using HR Profile BG ID for now - not sure where all this
140 -- is used; this may need to change to go off of implementation options.
141
142 IF PA_CROSS_BUSINESS_GRP.IsCrossBGProfile = 'N' THEN
143 l_bg_id := fnd_profile.value('PER_BUSINESS_GROUP_ID');
144 ELSE
145 l_bg_id := -1;
146 END IF;
147
148 BEGIN
149
150 SELECT max_job_level
151 into l_max_job_level
152 FROM pa_role_job_bgs
153 WHERE project_role_id = p_project_role_id
154 AND nvl(business_group_id, -1) = l_bg_id;
155
156 EXCEPTION
157 WHEN NO_DATA_FOUND THEN
158 l_max_job_level := NULL;
159 END;
160
161 RETURN l_max_job_level;
162
163 END get_max_job_level;
164
165
166 end pa_role_job_bg_utils ;
[Home] [Help]