1 Package Body pqh_rla_bus as
2 /* $Header: pqrlarhi.pkb 115.3 2003/02/12 00:43:55 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rla_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_rule_attribute_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_rule_attribute_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- The cursor csr_sec_group joins pqh_rule_sets,
28 -- pqh_rule_attributes and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , pqh_rule_attributes rla
36 , pqh_rule_sets rst
37 where rla.rule_attribute_id = p_rule_attribute_id
38 and pbg.business_group_id = rst.business_group_id
39 and rst.rule_set_id = rla.rule_set_id;
40 --
41 -- Declare local variables
42 --
43 l_security_group_id number;
44 l_proc varchar2(72) := g_package||'set_security_group_id';
45 --
46 begin
47 --
48 hr_utility.set_location('Entering:'|| l_proc, 10);
49 --
50 -- Ensure that all the mandatory parameter are not null
51 --
52 hr_api.mandatory_arg_error
53 (p_api_name => l_proc
54 ,p_argument => 'rule_attribute_id'
55 ,p_argument_value => p_rule_attribute_id
56 );
57 --
58 open csr_sec_grp;
59 fetch csr_sec_grp into l_security_group_id;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'RULE_ATTRIBUTE_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_rule_attribute_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 -- Added joins between pqh_rule_sets,
99 -- pqh_rule_attributes and PER_BUSINESS_GROUPS
100 -- so that the legislation_code for
101 -- the current business group context can be derived.
102
103 cursor csr_leg_code is
104 select pbg.legislation_code
105 from per_business_groups pbg
106 , pqh_rule_attributes rla
107 , pqh_rule_sets rst
108 where rla.rule_attribute_id = p_rule_attribute_id
109 and pbg.business_group_id = rst.business_group_id
110 and rst.rule_set_id = rla.rule_set_id;
111 --
112 -- Declare local variables
113 --
114 l_legislation_code varchar2(150);
115 l_proc varchar2(72) := g_package||'return_legislation_code';
116 --
117 Begin
118 --
119 hr_utility.set_location('Entering:'|| l_proc, 10);
120 --
121 -- Ensure that all the mandatory parameter are not null
122 --
123 hr_api.mandatory_arg_error
124 (p_api_name => l_proc
125 ,p_argument => 'rule_attribute_id'
126 ,p_argument_value => p_rule_attribute_id
127 );
128 --
129 if ( nvl(pqh_rla_bus.g_rule_attribute_id, hr_api.g_number)
130 = p_rule_attribute_id) then
131 --
132 -- The legislation code has already been found with a previous
133 -- call to this function. Just return the value in the global
134 -- variable.
135 --
136 l_legislation_code := pqh_rla_bus.g_legislation_code;
137 hr_utility.set_location(l_proc, 20);
138 else
139 --
140 -- The ID is different to the last call to this function
141 -- or this is the first call to this function.
142 --
143 open csr_leg_code;
144 fetch csr_leg_code into l_legislation_code;
145 --
146 if csr_leg_code%notfound then
147 --
148 -- The primary key is invalid therefore we must error
149 --
150 close csr_leg_code;
151 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
152 fnd_message.raise_error;
153 end if;
154 hr_utility.set_location(l_proc,30);
155 --
156 -- Set the global variables so the values are
157 -- available for the next call to this function.
158 --
159 close csr_leg_code;
160 pqh_rla_bus.g_rule_attribute_id := p_rule_attribute_id;
161 pqh_rla_bus.g_legislation_code := l_legislation_code;
162 end if;
163 hr_utility.set_location(' Leaving:'|| l_proc, 40);
164 return l_legislation_code;
165 end return_legislation_code;
166 --
167 -- ----------------------------------------------------------------------------
168 -- |-----------------------< chk_non_updateable_args >------------------------|
169 -- ----------------------------------------------------------------------------
170 -- {Start Of Comments}
171 --
172 -- Description:
173 -- This procedure is used to ensure that non updateable attributes have
174 -- not been updated. If an attribute has been updated an error is generated.
175 --
176 -- Pre Conditions:
177 -- g_old_rec has been populated with details of the values currently in
178 -- the database.
179 --
180 -- In Arguments:
181 -- p_rec has been populated with the updated values the user would like the
182 -- record set to.
183 --
184 -- Post Success:
185 -- Processing continues if all the non updateable attributes have not
186 -- changed.
187 --
188 -- Post Failure:
189 -- An application error is raised if any of the non updatable attributes
190 -- have been altered.
191 --
192 -- {End Of Comments}
193 -- ----------------------------------------------------------------------------
194 Procedure chk_non_updateable_args
195 (p_rec in pqh_rla_shd.g_rec_type
196 ) IS
197 --
198 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
199 --
200 Begin
201 --
202 -- Only proceed with the validation if a row exists for the current
203 -- record in the HR Schema.
204 --
205 IF NOT pqh_rla_shd.api_updating
206 (p_rule_attribute_id => p_rec.rule_attribute_id
207 ,p_object_version_number => p_rec.object_version_number
208 ) THEN
209 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
210 fnd_message.set_token('PROCEDURE ', l_proc);
211 fnd_message.set_token('STEP ', '5');
212 fnd_message.raise_error;
213 END IF;
214 --
215 End chk_non_updateable_args;
216 --
217 -- ----------------------------------------------------------------------------
218 -- |-----------------------< chk_unique_attributes >------------------------|
219 -- ----------------------------------------------------------------------------
220 -- {Start Of Comments}
221 --
222 -- Description:
223 -- This procedure is used to ensure duplicate attributes are not saved
224 -- for the same rule_set_id, operation_code, and attribute_values.
225 --
226 -- Post Failure:
227 -- An application error is raised if duplicate records are found.
228 --
229 -- {End Of Comments}
230 -- ----------------------------------------------------------------------------
231 Procedure chk_unique_attributes
232 (p_rec in pqh_rla_shd.g_rec_type
233 ) IS
234 --
235 l_proc varchar2(72) := g_package || 'chk_unique_attributes';
236 --
237 cnt number;
238 Begin
239 hr_utility.set_location(' Entering:'|| l_proc, 10);
240 select count(*) into cnt from pqh_rule_attributes where
241 rule_set_id = p_rec.rule_set_id
242 and
243 rule_attribute_id <> nvl(p_rec.rule_attribute_id, 0)
244 and
245 ((attribute_code is null and p_rec.attribute_code is null) or (attribute_code = p_rec.attribute_code))
246 and
247 ((operation_code is null and p_rec.operation_code is null) or (operation_code = p_rec.operation_code))
248 and
249 ((attribute_value is null and p_rec.attribute_value is null) or (attribute_value = p_rec.attribute_value));
250
251 if cnt > 0 then
252 hr_utility.set_message(8302, 'PQH_CBR_UNIQUE_ATTRIBUTES');
253 hr_utility.raise_error;
254 end if;
255 --
256 hr_utility.set_location(' Leaving:'|| l_proc, 20);
257 End chk_unique_attributes;
258
259 --
260 -- ----------------------------------------------------------------------------
261 -- |-----------------------< chk_attribute_datatype >------------------------|
262 -- ----------------------------------------------------------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- This procedure is used to ensure attributes of type number
267 -- cannot be saved with values which are alpha-numeric. Only numbers should
268 -- be able to save.
269 --
270 -- Post Failure:
271 -- An application error is raised if characters which are not numbers are
272 -- found.
273 --
274 -- {End Of Comments}
275 -- ----------------------------------------------------------------------------
276 Procedure chk_attribute_datatype
277 (p_rec in pqh_rla_shd.g_rec_type
278 ) IS
279 --
280 l_proc varchar2(72) := g_package || 'chk_attribute_datatype';
281 --
282 att_val number;
283 att_type varchar2(10);
284 cnt number;
285 Begin
286 select count(*) into cnt from pqh_attributes where attribute_id = to_number(p_rec.attribute_code);
287 if cnt = 1 then
288 select column_type into att_type from pqh_attributes where
289 attribute_id = to_number(p_rec.attribute_code);
290 --
291 -- to_number is able to handle only upto 126 chars. so making this logic to go beyond 126.
292 --
293 if att_type = 'N' then
294 if nvl(length(p_rec.attribute_value), 0) > 120 then
295 select to_number(substr(p_rec.attribute_value, 1, 120)) into att_val from dual;
296 select to_number(substr(p_rec.attribute_value, 120)) into att_val from dual;
297 else
298 select to_number(p_rec.attribute_value) into att_val from dual;
299 end if;
300 end if;
301 else
302 hr_utility.set_message(8302, 'PQH_ATTRIBUTE_NOT_FOUND');
303 hr_utility.raise_error;
304 end if;
305 exception when others then
306 hr_utility.set_message(8302, 'PQH_CBR_NUMBER_ATTRIBUTE');
307 hr_utility.raise_error;
308 End chk_attribute_datatype;
309
310 --
311 -- ----------------------------------------------------------------------------
312 -- |---------------------------< insert_validate >----------------------------|
313 -- ----------------------------------------------------------------------------
314 Procedure insert_validate
315 (p_rec in pqh_rla_shd.g_rec_type
316 ) is
317 --
318 l_proc varchar2(72) := g_package||'insert_validate';
319 --
320 Begin
321 hr_utility.set_location('Entering:'||l_proc, 5);
322 --
323 -- Call all supporting business operations
324 --
325 --
326 -- commented out the follwing line because rule sets are not
327 -- business group sensitive. --01/18/2002 rpasapul.
328 --pqh_rst_bus.set_security_group_id(p_rec.rule_set_id, null);
329 --
330 -- Validate Dependent Attributes
331 --
332 --
333 --
334 -- Check for duplicate attributes.
335 --
336 chk_unique_attributes
337 (p_rec => p_rec
338 );
339 --
340 -- Check for data type match for attribute values
341 --
342 chk_attribute_datatype
343 (p_rec => p_rec
344 );
345
346 hr_utility.set_location(' Leaving:'||l_proc, 10);
347 End insert_validate;
348 --
349 -- ----------------------------------------------------------------------------
350 -- |---------------------------< update_validate >----------------------------|
351 -- ----------------------------------------------------------------------------
352 Procedure update_validate
353 (p_rec in pqh_rla_shd.g_rec_type
354 ) is
355 --
356 l_proc varchar2(72) := g_package||'update_validate';
357 --
358 Begin
359 hr_utility.set_location('Entering:'||l_proc, 5);
360 --
361 -- Call all supporting business operations
362 --
363 --
364 -- Commented out the follwoing line because rule sets are not
365 -- business group sensitive --01/18/2003 rpasapul.
366 -- pqh_rst_bus.set_security_group_id(p_rec.rule_set_id, null);
367 --
368 -- Validate Dependent Attributes
369 --
370 chk_non_updateable_args
371 (p_rec => p_rec
372 );
373 --
374 -- Check for duplicate attributes.
375 --
376 chk_unique_attributes
377 (p_rec => p_rec
378 );
379 --
380 -- Check for data type match for attribute values
381 --
382 chk_attribute_datatype
383 (p_rec => p_rec
384 );
385 --
386 hr_utility.set_location(' Leaving:'||l_proc, 10);
387 End update_validate;
388 --
389 -- ----------------------------------------------------------------------------
393 (p_rec in pqh_rla_shd.g_rec_type
390 -- |---------------------------< delete_validate >----------------------------|
391 -- ----------------------------------------------------------------------------
392 Procedure delete_validate
394 ) is
395 --
396 l_proc varchar2(72) := g_package||'delete_validate';
397 --
398 Begin
399 hr_utility.set_location('Entering:'||l_proc, 5);
400 --
401 -- Call all supporting business operations
402 --
403 hr_utility.set_location(' Leaving:'||l_proc, 10);
404 End delete_validate;
405 --
406 end pqh_rla_bus;
[Home] [Help]