1 PACKAGE BODY PA_ROLE_PROFILES_UTILS AS
2 -- $Header: PARPRPUB.pls 120.1 2005/08/19 16:59:25 mwasowic noship $
3 --
4 -- PROCEDURE
5 -- Check_BusGroup_Name_Or_Id
6 -- PURPOSE
7 -- This procedure checks if the busiess group id
8 -- and name combination is valid. If id does not
9 -- match with name, retrieve and return the right id.
10
11 PROCEDURE Check_BusGroup_Name_Or_Id
12 ( p_business_group_id IN NUMBER,
13 p_business_group_name IN VARCHAR2,
14 p_check_id_flag IN VARCHAR2 DEFAULT NULL,
15 x_business_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
16 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
17 x_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
18
19 l_current_id NUMBER := NULL;
20 l_num_ids NUMBER := 0;
21 l_id_found_flag VARCHAR(1) := 'N';
22 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
23
24 CURSOR c_ids IS
25 SELECT business_group_id
26 FROM per_business_groups
27 WHERE name = p_business_group_name;
28 BEGIN
29 IF l_enable_log = 'Y' THEN
30 pa_debug.init_err_stack ('pa_role_profiles_utils.Check_BusGroup_Name_Or_Id');
31 END IF;
32
33 IF p_business_group_id IS NOT NULL THEN
34 IF p_business_group_name IS NOT NULL THEN
35 OPEN c_ids;
36 LOOP
37 FETCH c_ids INTO l_current_id;
38 EXIT WHEN c_ids%NOTFOUND;
39 IF l_current_id = p_business_group_id THEN
40 l_id_found_flag := 'Y';
41 x_business_group_id := p_business_group_id;
42 END IF;
43 END LOOP;
44 l_num_ids := c_ids%ROWCOUNT;
45 CLOSE c_ids;
46
47 IF l_num_ids = 0 THEN
48 RAISE NO_DATA_FOUND;
49 ELSIF l_num_ids = 1 THEN
50 x_business_group_id := l_current_id;
51 ELSIF l_id_found_flag = 'N' THEN
52 RAISE TOO_MANY_ROWS;
53 END IF;
54 END IF;
55 ELSE
56 IF p_business_group_name IS NOT NULL THEN
57 SELECT business_group_id
58 INTO x_business_group_id
59 FROM per_business_groups
60 WHERE name = p_business_group_name;
61 ELSE x_business_group_id := NULL;
62 END IF;
63 END IF;
64
65 x_return_status:= FND_API.G_RET_STS_SUCCESS;
66 x_error_msg_code := NULL;
67 IF l_enable_log = 'Y' THEN
68 pa_debug.reset_err_stack;
69 END IF;
70
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 --dbms_output.put_line ('No data exists');
74 x_business_group_id := NULL;
75 x_return_status:= FND_API.G_RET_STS_ERROR;
76 x_error_msg_code:= 'PA_BUS_GROUP_INVALID';
77 WHEN TOO_MANY_ROWS THEN
78 --dbms_output.put_line ('Too Many Rows');
79 x_business_group_id := NULL;
80 x_return_status:= FND_API.G_RET_STS_ERROR;
81 x_error_msg_code:= 'PA_BUS_GROUP_NOT_UNIQUE';
82 WHEN OTHERS THEN
83 x_business_group_id := NULL;
84 fnd_msg_pub.add_exc_msg
85 (p_pkg_name => 'PA_ROLE_PROFILES_UTILS',
86 p_procedure_name => pa_debug.g_err_stack );
87 x_error_msg_code:= FND_API.G_RET_STS_UNEXP_ERROR;
88 RAISE;
89
90 END Check_BusGroup_Name_Or_Id;
91
92 --
93 -- PROCEDURE
94 -- Check_Position_Name_Or_Id
95 -- PURPOSE
96 -- This procedure checks if the position id
97 -- and name combination is valid. If id does not
98 -- match with the name, retrieve and return the right id.
99
100 PROCEDURE Check_Position_Name_Or_Id
101 ( p_position_id IN NUMBER,
102 p_position_name IN VARCHAR2,
103 p_check_id_flag IN VARCHAR2 DEFAULT NULL,
104 x_position_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
105 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
106 x_error_msg_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
107
108 l_current_id NUMBER := NULL;
109 l_num_ids NUMBER := 0;
110 l_id_found_flag VARCHAR(1) := 'N';
111 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
112
113 CURSOR c_ids IS
114 SELECT position_id
115 FROM per_positions
116 WHERE name = p_position_name;
117 BEGIN
118 IF l_enable_log = 'Y' THEN
119 pa_debug.init_err_stack ('pa_role_profiles_utils.Check_Position_Name_Or_Id');
120 END IF;
121
122 IF p_position_id IS NOT NULL THEN
123 IF p_position_name IS NOT NULL THEN
124 OPEN c_ids;
125 LOOP
126 FETCH c_ids INTO l_current_id;
127 EXIT WHEN c_ids%NOTFOUND;
128 IF l_current_id = p_position_id THEN
129 l_id_found_flag := 'Y';
130 x_position_id := p_position_id;
131 END IF;
132 END LOOP;
133 l_num_ids := c_ids%ROWCOUNT;
134 CLOSE c_ids;
135
136 IF l_num_ids = 0 THEN
137 RAISE NO_DATA_FOUND;
138 ELSIF l_num_ids = 1 THEN
139 x_position_id := l_current_id;
140 ELSIF l_id_found_flag = 'N' THEN
141 RAISE TOO_MANY_ROWS;
142 END IF;
143 END IF;
144 ELSE
145 IF p_position_name IS NOT NULL THEN
146 SELECT position_id
147 INTO x_position_id
148 FROM per_positions
149 WHERE name = p_position_name;
150 ELSE x_position_id := NULL;
151 END IF;
152 END IF;
153
154 x_return_status:= FND_API.G_RET_STS_SUCCESS;
155 x_error_msg_code := NULL;
156 IF l_enable_log = 'Y' THEN
157 pa_debug.reset_err_stack;
158 END IF;
159
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 x_position_id := NULL;
163 x_return_status:= FND_API.G_RET_STS_ERROR;
164 x_error_msg_code:= 'PA_POSITION_INVALID';
165 WHEN TOO_MANY_ROWS THEN
166 x_position_id := NULL;
167 x_return_status:= FND_API.G_RET_STS_ERROR;
168 x_error_msg_code:= 'PA_POSITION_NOT_UNIQUE';
169 WHEN OTHERS THEN
170 x_position_id := NULL;
171 fnd_msg_pub.add_exc_msg
172 (p_pkg_name => 'PA_ROLE_PROFILES_UTILS',
173 p_procedure_name => pa_debug.g_err_stack );
174 x_error_msg_code:= FND_API.G_RET_STS_UNEXP_ERROR;
175 RAISE;
176
177 END Check_Position_Name_Or_Id;
178
179 --
180 -- PROCEDURE
181 -- Check_Business_Level_Attrs
182 -- PURPOSE
183 -- This procedure checks each business attributes
184 -- name/id combination. It also validates
185 -- organization against business group,
186 -- job against business group,
187 -- position against business group/organization/job.
188
189 PROCEDURE Check_Business_Level_Attrs
190 ( p_business_group_id IN NUMBER,
191 p_business_group_name IN VARCHAR2,
192 p_organization_id IN NUMBER,
193 p_organization_name IN VARCHAR2,
194 p_job_id IN NUMBER,
195 p_job_name IN VARCHAR2,
196 p_position_id IN NUMBER,
197 p_position_name IN VARCHAR2,
198 x_business_group_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
199 x_organization_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
200 x_job_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
201 x_position_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
202 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
203 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
204 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
205
206 l_return_status VARCHAR2(1);
207 l_error_msg_code VARCHAR2(500);
208 l_exists VARCHAR(1);
209 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
210
211 BEGIN
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213 --FND_MSG_PUB.initialize;
214
215 IF p_business_group_name IS NULL
216 AND p_organization_name IS NULL
217 AND p_job_name IS NULL
218 AND p_position_name IS NULL THEN
219 PA_UTILS.Add_Message ( p_app_short_name => 'PA',
220 p_msg_name => 'PA_PROFILE_BUS_LEVEL_MISSING' );
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223
224 IF p_business_group_name IS NOT NULL THEN
225 Check_BusGroup_Name_Or_Id
226 ( p_business_group_id => p_business_group_id,
227 p_business_group_name => p_business_group_name,
228 x_business_group_id => x_business_group_id,
229 x_return_status => l_return_status,
230 x_error_msg_code => l_error_msg_code );
231
232 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
233 PA_UTILS.Add_Message ( p_app_short_name => 'PA',
234 p_msg_name => l_error_msg_code );
235 END IF;
236 END IF;
237
238 IF p_organization_name IS NOT NULL THEN
239 pa_hr_org_utils.Check_OrgName_Or_Id
240 ( p_organization_id => p_organization_id,
241 p_organization_name => p_organization_name,
242 p_check_id_flag => 'A',
243 x_organization_id => x_organization_id,
244 x_return_status => l_return_status,
245 x_error_msg_code => l_error_msg_code );
246
247 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
248 PA_UTILS.Add_Message ( p_app_short_name => 'PA',
249 p_msg_name => l_error_msg_code );
250 END IF;
251 END IF;
252
253 IF p_job_name IS NOT NULL THEN
254 pa_job_utils.Check_JobName_Or_Id
255 ( p_job_id => p_job_id,
256 p_job_name => p_job_name,
257 p_check_id_flag => 'A',
258 x_job_id => x_job_id,
259 x_return_status => l_return_status,
260 x_error_message_code => l_error_msg_code );
261
262 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
263 PA_UTILS.Add_Message ( p_app_short_name => 'PA',
264 p_msg_name => l_error_msg_code );
265 END IF;
266 END IF;
267
268 --dbms_output.put_line('job_id ' || x_job_id);
269
270 IF p_position_name IS NOT NULL THEN
271 Check_Position_Name_Or_Id
272 ( p_position_id => p_position_id,
273 p_position_name => p_position_name,
274 x_position_id => x_position_id,
275 x_return_status => l_return_status,
276 x_error_msg_code => l_error_msg_code );
277 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
278 PA_UTILS.Add_Message ( p_app_short_name => 'PA',
279 p_msg_name => l_error_msg_code );
280 END IF;
281 END IF;
282
283 --dbms_output.put_line ('no of errors in the stack ' || FND_MSG_PUB.Count_Msg);
284
285 IF FND_MSG_PUB.Count_Msg > 0 THEN
286 RAISE FND_API.G_EXC_ERROR;
287 END IF;
288
289 --The following code checks if organization, job, position belong to the business group
290 --and also if position belongs to the organization and job.
291 IF x_organization_id IS NOT NULL THEN
292 BEGIN
293 SELECT 'Y'
294 INTO l_exists
295 FROM HR_ORGANIZATION_UNITS
296 WHERE organization_id = x_organization_id
297 AND business_group_id = NVL(x_business_group_id, business_group_id);
298
299 EXCEPTION
300 WHEN NO_DATA_FOUND THEN
301 pa_utils.add_message (p_app_short_name => 'PA',
302 p_msg_name => 'PA_ORG_NOT_QUALIFIED');
303 END;
304 END IF;
305
306 IF x_job_id IS NOT NULL THEN
307 BEGIN
308 SELECT 'Y'
309 INTO l_exists
310 FROM PER_JOBS
311 WHERE job_id = x_job_id
312 AND business_group_id = NVL(x_business_group_id, business_group_id);
313
314 EXCEPTION
315 WHEN NO_DATA_FOUND THEN
316 pa_utils.add_message (p_app_short_name => 'PA',
317 p_msg_name => 'PA_JOB_NOT_QUALIFIED');
318 END;
319 END IF;
320
321 IF x_position_id IS NOT NULL THEN
322 BEGIN
323 SELECT 'Y'
324 INTO l_exists
325 FROM PER_POSITIONS
326 WHERE business_group_id = NVL(x_business_group_id, business_group_id)
327 AND organization_id = NVL(x_organization_id, organization_id)
328 AND job_id = NVL(x_job_id, job_id)
329 AND position_id = x_position_id;
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 pa_utils.add_message (p_app_short_name => 'PA',
334 p_msg_name => 'PA_POSITION_NOT_QUALIFIED');
335 END;
336 END IF;
337
338 IF FND_MSG_PUB.Count_Msg > 0 THEN
339 RAISE FND_API.G_EXC_ERROR;
340 END IF;
341
342 EXCEPTION
343 WHEN FND_API.G_EXC_ERROR THEN
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 x_msg_count := FND_MSG_PUB.Count_Msg;
346 WHEN OTHERS THEN
347 RAISE;
348 END Check_Business_Level_Attrs;
349
350 --
351 -- PROCEDURE
352 -- Validate_Profile_Lines
353 -- PURPOSE
354 -- This procedure checks the roles and their weightings.
355 --
356
357 PROCEDURE Validate_Profile_Lines
358 ( p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
359 p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
360 p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
361 x_role_id_tbl OUT NOCOPY SYSTEM.PA_NUM_TBL_TYPE,
362 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
363 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
364 x_msg_data OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
365
366 l_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE := p_role_id_tbl;
367 l_return_status VARCHAR2(1);
368 l_error_msg_code VARCHAR2(500);
369 l_sum NUMBER := 0;
370 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
371 BEGIN
372
373 x_return_status := FND_API.G_RET_STS_SUCCESS;
374
375 IF l_role_id_tbl.count <= 0 THEN
376 PA_UTILS.Add_Message (p_app_short_name => 'PA',
377 p_msg_name => 'PA_PROFILE_NUM_ROLES_ZERO');
378 RAISE FND_API.G_EXC_ERROR;
379 ELSE
380 FOR i IN 1..l_role_id_tbl.count LOOP
381 IF l_role_id_tbl(i) = 0 THEN
382 l_role_id_tbl(i) := NULL;
383 END IF;
384
385 IF p_weighting_tbl(i) <= 0 THEN
386 PA_UTILS.Add_Message (p_app_short_name => 'PA',
387 p_msg_name => 'PA_WEIGHTING_NOT_POSITIVE');
388 RAISE FND_API.G_EXC_ERROR;
389 END IF;
390
391 pa_role_utils.Check_Role_Name_Or_Id
392 (p_role_id => l_role_id_tbl(i),
393 p_role_name => p_role_name_tbl(i),
394 p_check_id_flag => 'A',
395 x_role_id => l_role_id_tbl(i),
396 x_return_status => l_return_status,
397 x_error_message_code => l_error_msg_code);
398
399 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
400 PA_UTILS.Add_Message (p_app_short_name => 'PA',
401 p_msg_name => l_error_msg_code);
402 RAISE FND_API.G_EXC_ERROR;
403 END IF;
404
405 l_sum := l_sum + p_weighting_tbl(i);
406 END LOOP;
407 END IF;
408
409 IF l_sum <> 100 THEN
410 PA_UTILS.Add_Message (p_app_short_name => 'PA',
411 p_msg_name => 'PA_INCORRECT_SUM');
412 RAISE FND_API.G_EXC_ERROR;
413 END IF;
414
415 IF FND_MSG_PUB.Count_Msg > 0 THEN -- One or more roles are invalid
416 RAISE FND_API.G_EXC_ERROR;
417 END IF;
418
419 FOR i IN 1..l_role_id_tbl.count LOOP
420 FOR j IN i+1..l_role_id_tbl.count LOOP
421 IF l_role_id_tbl(i) = l_role_id_tbl(j) THEN
422 PA_UTILS.Add_Message (p_app_short_name => 'PA',
423 p_msg_name => 'PA_DUPLICATE_ROLES');
424 RAISE FND_API.G_EXC_ERROR;
425 END IF;
426 END LOOP;
427 END LOOP;
428
429 x_role_id_tbl := l_role_id_tbl;
430
431 EXCEPTION
432 WHEN FND_API.G_EXC_ERROR THEN
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 x_msg_count := FND_MSG_PUB.Count_Msg;
435 WHEN OTHERS THEN
436 RAISE;
437 END Validate_Profile_Lines;
438
439 END PA_ROLE_PROFILES_UTILS;
[Home] [Help]