[Home] [Help]
PACKAGE BODY: APPS.PAY_EEI_BUS
Source
1 Package Body pay_eei_bus as
2 /* $Header: pyeeirhi.pkb 120.11.12020000.2 2013/02/06 13:17:37 kskoduri ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_eei_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_element_type_extra_info_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_element_type_extra_info_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_element_type_extra_info eei
30 , pay_element_types_f pet
31 where eei.element_type_extra_info_id = p_element_type_extra_info_id
32 and pbg.business_group_id = pet.business_group_id
33 and pet.element_type_id = eei.element_type_id;
34
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'element_type_extra_info_id'
50 ,p_argument_value => p_element_type_extra_info_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 fnd_message.raise_error;
64 --
65 end if;
66 close csr_sec_grp;
67 --
68 -- Set the security_group_id in CLIENT_INFO
69 --
70 hr_api.set_security_group_id
71 (p_security_group_id => l_security_group_id
72 );
73 --
74 hr_utility.set_location(' Leaving:'|| l_proc, 20);
75 --
76 end set_security_group_id;
77 --
78 -- ---------------------------------------------------------------------------
79 -- |---------------------< return_legislation_code >-------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 Function return_legislation_code
83 (p_element_type_extra_info_id in number
84 )
85 Return Varchar2 Is
86 --
87 -- Declare cursor
88 --
89 cursor csr_leg_code is
90 select pbg.legislation_code
91 from per_business_groups pbg
92 , pay_element_type_extra_info eei
93 , pay_element_types_f pet
94 where eei.element_type_extra_info_id = p_element_type_extra_info_id
95 and pbg.business_group_id = pet.business_group_id
96 and pet.element_type_id = eei.element_type_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'element_type_extra_info_id'
112 ,p_argument_value => p_element_type_extra_info_id
113 );
114 --
115 if ( nvl(pay_eei_bus.g_element_type_extra_info_id, hr_api.g_number)
116 = p_element_type_extra_info_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pay_eei_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
145 close csr_leg_code;
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
146 pay_eei_bus.g_element_type_extra_info_id := p_element_type_extra_info_id;
147 pay_eei_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------------< chk_ddf >----------------------------------|
155 -- ----------------------------------------------------------------------------
156 --
157 -- Description:
158 -- Validates all the Developer Descriptive Flexfield values.
159 --
160 -- Prerequisites:
161 -- All other columns have been validated. Must be called as the
162 -- last step from insert_validate and update_validate.
163 --
164 -- In Arguments:
165 -- p_rec
166 --
167 -- Post Success:
168 -- If the Developer Descriptive Flexfield structure column and data values
169 -- are all valid this procedure will end normally and processing will
170 -- continue.
171 --
172 -- Post Failure:
173 -- If the Developer Descriptive Flexfield structure column value or any of
174 -- the data values are invalid then an application error is raised as
175 -- a PL/SQL exception.
176 --
177 -- Access Status:
178 -- Internal Row Handler Use Only.
179 --
180 -- ----------------------------------------------------------------------------
181 procedure chk_ddf
182 (p_rec in pay_eei_shd.g_rec_type
183 ) is
184 --
185 l_proc varchar2(72) := g_package || 'chk_ddf';
186 --
187 begin
188 hr_utility.set_location('Entering:'||l_proc,10);
189 --
190 if ((p_rec.element_type_extra_info_id is not null) and (
191 nvl(pay_eei_shd.g_old_rec.information_type, hr_api.g_varchar2) <>
192 nvl(p_rec.information_type, hr_api.g_varchar2) or
193 nvl(pay_eei_shd.g_old_rec.eei_information_category, hr_api.g_varchar2) <>
194 nvl(p_rec.eei_information_category, hr_api.g_varchar2) or
195 nvl(pay_eei_shd.g_old_rec.eei_information1, hr_api.g_varchar2) <>
196 nvl(p_rec.eei_information1, hr_api.g_varchar2) or
197 nvl(pay_eei_shd.g_old_rec.eei_information2, hr_api.g_varchar2) <>
198 nvl(p_rec.eei_information2, hr_api.g_varchar2) or
199 nvl(pay_eei_shd.g_old_rec.eei_information3, hr_api.g_varchar2) <>
200 nvl(p_rec.eei_information3, hr_api.g_varchar2) or
201 nvl(pay_eei_shd.g_old_rec.eei_information4, hr_api.g_varchar2) <>
202 nvl(p_rec.eei_information4, hr_api.g_varchar2) or
203 nvl(pay_eei_shd.g_old_rec.eei_information5, hr_api.g_varchar2) <>
204 nvl(p_rec.eei_information5, hr_api.g_varchar2) or
205 nvl(pay_eei_shd.g_old_rec.eei_information6, hr_api.g_varchar2) <>
206 nvl(p_rec.eei_information6, hr_api.g_varchar2) or
207 nvl(pay_eei_shd.g_old_rec.eei_information7, hr_api.g_varchar2) <>
208 nvl(p_rec.eei_information7, hr_api.g_varchar2) or
209 nvl(pay_eei_shd.g_old_rec.eei_information8, hr_api.g_varchar2) <>
210 nvl(p_rec.eei_information8, hr_api.g_varchar2) or
211 nvl(pay_eei_shd.g_old_rec.eei_information9, hr_api.g_varchar2) <>
212 nvl(p_rec.eei_information9, hr_api.g_varchar2) or
213 nvl(pay_eei_shd.g_old_rec.eei_information10, hr_api.g_varchar2) <>
214 nvl(p_rec.eei_information10, hr_api.g_varchar2) or
215 nvl(pay_eei_shd.g_old_rec.eei_information11, hr_api.g_varchar2) <>
216 nvl(p_rec.eei_information11, hr_api.g_varchar2) or
217 nvl(pay_eei_shd.g_old_rec.eei_information12, hr_api.g_varchar2) <>
218 nvl(p_rec.eei_information12, hr_api.g_varchar2) or
219 nvl(pay_eei_shd.g_old_rec.eei_information13, hr_api.g_varchar2) <>
220 nvl(p_rec.eei_information13, hr_api.g_varchar2) or
221 nvl(pay_eei_shd.g_old_rec.eei_information14, hr_api.g_varchar2) <>
222 nvl(p_rec.eei_information14, hr_api.g_varchar2) or
223 nvl(pay_eei_shd.g_old_rec.eei_information15, hr_api.g_varchar2) <>
224 nvl(p_rec.eei_information15, hr_api.g_varchar2) or
225 nvl(pay_eei_shd.g_old_rec.eei_information16, hr_api.g_varchar2) <>
226 nvl(p_rec.eei_information16, hr_api.g_varchar2) or
227 nvl(pay_eei_shd.g_old_rec.eei_information17, hr_api.g_varchar2) <>
228 nvl(p_rec.eei_information17, hr_api.g_varchar2) or
229 nvl(pay_eei_shd.g_old_rec.eei_information18, hr_api.g_varchar2) <>
230 nvl(p_rec.eei_information18, hr_api.g_varchar2) or
231 nvl(pay_eei_shd.g_old_rec.eei_information19, hr_api.g_varchar2) <>
232 nvl(p_rec.eei_information19, hr_api.g_varchar2) or
233 nvl(pay_eei_shd.g_old_rec.eei_information20, hr_api.g_varchar2) <>
234 nvl(p_rec.eei_information20, hr_api.g_varchar2) or
235 nvl(pay_eei_shd.g_old_rec.eei_information21, hr_api.g_varchar2) <>
236 nvl(p_rec.eei_information21, hr_api.g_varchar2) or
237 nvl(pay_eei_shd.g_old_rec.eei_information22, hr_api.g_varchar2) <>
238 nvl(p_rec.eei_information22, hr_api.g_varchar2) or
239 nvl(pay_eei_shd.g_old_rec.eei_information23, hr_api.g_varchar2) <>
240 nvl(p_rec.eei_information23, hr_api.g_varchar2) or
241 nvl(pay_eei_shd.g_old_rec.eei_information24, hr_api.g_varchar2) <>
242 nvl(p_rec.eei_information24, hr_api.g_varchar2) or
243 nvl(pay_eei_shd.g_old_rec.eei_information25, hr_api.g_varchar2) <>
244 nvl(p_rec.eei_information25, hr_api.g_varchar2) or
245 nvl(pay_eei_shd.g_old_rec.eei_information26, hr_api.g_varchar2) <>
246 nvl(p_rec.eei_information26, hr_api.g_varchar2) or
247 nvl(pay_eei_shd.g_old_rec.eei_information27, hr_api.g_varchar2) <>
248 nvl(p_rec.eei_information27, hr_api.g_varchar2) or
249 nvl(pay_eei_shd.g_old_rec.eei_information28, hr_api.g_varchar2) <>
250 nvl(p_rec.eei_information28, hr_api.g_varchar2) or
254 nvl(p_rec.eei_information30, hr_api.g_varchar2) ))
251 nvl(pay_eei_shd.g_old_rec.eei_information29, hr_api.g_varchar2) <>
252 nvl(p_rec.eei_information29, hr_api.g_varchar2) or
253 nvl(pay_eei_shd.g_old_rec.eei_information30, hr_api.g_varchar2) <>
255 or (p_rec.element_type_extra_info_id is null) then
256 --
257 -- Only execute the validation if absolutely necessary:
258 -- a) During update, the structure column value or any
259 -- of the attribute values have actually changed.
260 -- b) During insert.
261 --
262
263 hr_dflex_utility.ins_or_upd_descflex_attribs
264 (p_appl_short_name => 'PAY'
265 ,p_descflex_name => 'Extra Element Info DDF'
266 ,p_attribute_category => p_rec.eei_information_category
267 ,p_attribute1_name => 'EEI_INFORMATION1'
268 ,p_attribute1_value => p_rec.eei_information1
269 ,p_attribute2_name => 'EEI_INFORMATION2'
270 ,p_attribute2_value => p_rec.eei_information2
271 ,p_attribute3_name => 'EEI_INFORMATION3'
272 ,p_attribute3_value => p_rec.eei_information3
273 ,p_attribute4_name => 'EEI_INFORMATION4'
274 ,p_attribute4_value => p_rec.eei_information4
275 ,p_attribute5_name => 'EEI_INFORMATION5'
276 ,p_attribute5_value => p_rec.eei_information5
277 ,p_attribute6_name => 'EEI_INFORMATION6'
278 ,p_attribute6_value => p_rec.eei_information6
279 ,p_attribute7_name => 'EEI_INFORMATION7'
280 ,p_attribute7_value => p_rec.eei_information7
281 ,p_attribute8_name => 'EEI_INFORMATION8'
282 ,p_attribute8_value => p_rec.eei_information8
283 ,p_attribute9_name => 'EEI_INFORMATION9'
284 ,p_attribute9_value => p_rec.eei_information9
285 ,p_attribute10_name => 'EEI_INFORMATION10'
286 ,p_attribute10_value => p_rec.eei_information10
287 ,p_attribute11_name => 'EEI_INFORMATION11'
288 ,p_attribute11_value => p_rec.eei_information11
289 ,p_attribute12_name => 'EEI_INFORMATION12'
290 ,p_attribute12_value => p_rec.eei_information12
291 ,p_attribute13_name => 'EEI_INFORMATION13'
292 ,p_attribute13_value => p_rec.eei_information13
293 ,p_attribute14_name => 'EEI_INFORMATION14'
294 ,p_attribute14_value => p_rec.eei_information14
295 ,p_attribute15_name => 'EEI_INFORMATION15'
296 ,p_attribute15_value => p_rec.eei_information15
297 ,p_attribute16_name => 'EEI_INFORMATION16'
298 ,p_attribute16_value => p_rec.eei_information16
299 ,p_attribute17_name => 'EEI_INFORMATION17'
300 ,p_attribute17_value => p_rec.eei_information17
301 ,p_attribute18_name => 'EEI_INFORMATION18'
302 ,p_attribute18_value => p_rec.eei_information18
303 ,p_attribute19_name => 'EEI_INFORMATION19'
304 ,p_attribute19_value => p_rec.eei_information19
305 ,p_attribute20_name => 'EEI_INFORMATION20'
306 ,p_attribute20_value => p_rec.eei_information20
307 ,p_attribute21_name => 'EEI_INFORMATION21'
308 ,p_attribute21_value => p_rec.eei_information21
309 ,p_attribute22_name => 'EEI_INFORMATION22'
310 ,p_attribute22_value => p_rec.eei_information22
311 ,p_attribute23_name => 'EEI_INFORMATION23'
312 ,p_attribute23_value => p_rec.eei_information23
313 ,p_attribute24_name => 'EEI_INFORMATION24'
314 ,p_attribute24_value => p_rec.eei_information24
315 ,p_attribute25_name => 'EEI_INFORMATION25'
316 ,p_attribute25_value => p_rec.eei_information25
317 ,p_attribute26_name => 'EEI_INFORMATION26'
318 ,p_attribute26_value => p_rec.eei_information26
319 ,p_attribute27_name => 'EEI_INFORMATION27'
320 ,p_attribute27_value => p_rec.eei_information27
321 ,p_attribute28_name => 'EEI_INFORMATION28'
322 ,p_attribute28_value => p_rec.eei_information28
323 ,p_attribute29_name => 'EEI_INFORMATION29'
324 ,p_attribute29_value => p_rec.eei_information29
325 ,p_attribute30_name => 'EEI_INFORMATION30'
326 ,p_attribute30_value => p_rec.eei_information30
327 );
328 end if;
329 --
330 hr_utility.set_location(' Leaving:'||l_proc,20);
331 end chk_ddf;
332 --
333 -- ----------------------------------------------------------------------------
334 -- |------------------------------< chk_df >----------------------------------|
335 -- ----------------------------------------------------------------------------
336 --
337 -- Description:
338 -- Validates all the Descriptive Flexfield values.
339 --
340 -- Prerequisites:
341 -- All other columns have been validated. Must be called as the
342 -- last step from insert_validate and update_validate.
343 --
344 -- In Arguments:
345 -- p_rec
346 --
347 -- Post Success:
348 -- If the Descriptive Flexfield structure column and data values are
349 -- all valid this procedure will end normally and processing will
350 -- continue.
351 --
352 -- Post Failure:
353 -- If the Descriptive Flexfield structure column value or any of
357 -- Access Status:
354 -- the data values are invalid then an application error is raised as
355 -- a PL/SQL exception.
356 --
358 -- Internal Row Handler Use Only.
359 --
360 -- ----------------------------------------------------------------------------
361 procedure chk_df
362 (p_rec in pay_eei_shd.g_rec_type
363 ) is
364 --
365 l_proc varchar2(72) := g_package || 'chk_df';
366 --
367 begin
368 hr_utility.set_location('Entering:'||l_proc,10);
369 --
370 if ((p_rec.element_type_extra_info_id is not null) and (
371 nvl(pay_eei_shd.g_old_rec.eei_attribute_category, hr_api.g_varchar2) <>
372 nvl(p_rec.eei_attribute_category, hr_api.g_varchar2) or
373 nvl(pay_eei_shd.g_old_rec.eei_attribute1, hr_api.g_varchar2) <>
374 nvl(p_rec.eei_attribute1, hr_api.g_varchar2) or
375 nvl(pay_eei_shd.g_old_rec.eei_attribute2, hr_api.g_varchar2) <>
376 nvl(p_rec.eei_attribute2, hr_api.g_varchar2) or
377 nvl(pay_eei_shd.g_old_rec.eei_attribute3, hr_api.g_varchar2) <>
378 nvl(p_rec.eei_attribute3, hr_api.g_varchar2) or
379 nvl(pay_eei_shd.g_old_rec.eei_attribute4, hr_api.g_varchar2) <>
380 nvl(p_rec.eei_attribute4, hr_api.g_varchar2) or
381 nvl(pay_eei_shd.g_old_rec.eei_attribute5, hr_api.g_varchar2) <>
382 nvl(p_rec.eei_attribute5, hr_api.g_varchar2) or
383 nvl(pay_eei_shd.g_old_rec.eei_attribute6, hr_api.g_varchar2) <>
384 nvl(p_rec.eei_attribute6, hr_api.g_varchar2) or
385 nvl(pay_eei_shd.g_old_rec.eei_attribute7, hr_api.g_varchar2) <>
386 nvl(p_rec.eei_attribute7, hr_api.g_varchar2) or
387 nvl(pay_eei_shd.g_old_rec.eei_attribute8, hr_api.g_varchar2) <>
388 nvl(p_rec.eei_attribute8, hr_api.g_varchar2) or
389 nvl(pay_eei_shd.g_old_rec.eei_attribute9, hr_api.g_varchar2) <>
390 nvl(p_rec.eei_attribute9, hr_api.g_varchar2) or
391 nvl(pay_eei_shd.g_old_rec.eei_attribute10, hr_api.g_varchar2) <>
392 nvl(p_rec.eei_attribute10, hr_api.g_varchar2) or
393 nvl(pay_eei_shd.g_old_rec.eei_attribute11, hr_api.g_varchar2) <>
394 nvl(p_rec.eei_attribute11, hr_api.g_varchar2) or
395 nvl(pay_eei_shd.g_old_rec.eei_attribute12, hr_api.g_varchar2) <>
396 nvl(p_rec.eei_attribute12, hr_api.g_varchar2) or
397 nvl(pay_eei_shd.g_old_rec.eei_attribute13, hr_api.g_varchar2) <>
398 nvl(p_rec.eei_attribute13, hr_api.g_varchar2) or
399 nvl(pay_eei_shd.g_old_rec.eei_attribute14, hr_api.g_varchar2) <>
400 nvl(p_rec.eei_attribute14, hr_api.g_varchar2) or
401 nvl(pay_eei_shd.g_old_rec.eei_attribute15, hr_api.g_varchar2) <>
402 nvl(p_rec.eei_attribute15, hr_api.g_varchar2) or
403 nvl(pay_eei_shd.g_old_rec.eei_attribute16, hr_api.g_varchar2) <>
404 nvl(p_rec.eei_attribute16, hr_api.g_varchar2) or
405 nvl(pay_eei_shd.g_old_rec.eei_attribute17, hr_api.g_varchar2) <>
406 nvl(p_rec.eei_attribute17, hr_api.g_varchar2) or
407 nvl(pay_eei_shd.g_old_rec.eei_attribute18, hr_api.g_varchar2) <>
408 nvl(p_rec.eei_attribute18, hr_api.g_varchar2) or
409 nvl(pay_eei_shd.g_old_rec.eei_attribute19, hr_api.g_varchar2) <>
410 nvl(p_rec.eei_attribute19, hr_api.g_varchar2) or
411 nvl(pay_eei_shd.g_old_rec.eei_attribute20, hr_api.g_varchar2) <>
412 nvl(p_rec.eei_attribute20, hr_api.g_varchar2) ))
413 or (p_rec.element_type_extra_info_id is null) then
414 --
415 -- Only execute the validation if absolutely necessary:
416 -- a) During update, the structure column value or any
417 -- of the attribute values have actually changed.
418 -- b) During insert.
419 --
420 hr_dflex_utility.ins_or_upd_descflex_attribs
421 (p_appl_short_name => 'PAY'
422 ,p_descflex_name => 'Extra Element Info Details DF'
423 ,p_attribute_category => p_rec.eei_attribute_category
424 ,p_attribute1_name => 'EEI_ATTRIBUTE1'
425 ,p_attribute1_value => p_rec.eei_attribute1
426 ,p_attribute2_name => 'EEI_ATTRIBUTE2'
427 ,p_attribute2_value => p_rec.eei_attribute2
428 ,p_attribute3_name => 'EEI_ATTRIBUTE3'
429 ,p_attribute3_value => p_rec.eei_attribute3
430 ,p_attribute4_name => 'EEI_ATTRIBUTE4'
431 ,p_attribute4_value => p_rec.eei_attribute4
432 ,p_attribute5_name => 'EEI_ATTRIBUTE5'
433 ,p_attribute5_value => p_rec.eei_attribute5
434 ,p_attribute6_name => 'EEI_ATTRIBUTE6'
435 ,p_attribute6_value => p_rec.eei_attribute6
436 ,p_attribute7_name => 'EEI_ATTRIBUTE7'
437 ,p_attribute7_value => p_rec.eei_attribute7
438 ,p_attribute8_name => 'EEI_ATTRIBUTE8'
439 ,p_attribute8_value => p_rec.eei_attribute8
440 ,p_attribute9_name => 'EEI_ATTRIBUTE9'
441 ,p_attribute9_value => p_rec.eei_attribute9
442 ,p_attribute10_name => 'EEI_ATTRIBUTE10'
443 ,p_attribute10_value => p_rec.eei_attribute10
444 ,p_attribute11_name => 'EEI_ATTRIBUTE11'
445 ,p_attribute11_value => p_rec.eei_attribute11
446 ,p_attribute12_name => 'EEI_ATTRIBUTE12'
447 ,p_attribute12_value => p_rec.eei_attribute12
448 ,p_attribute13_name => 'EEI_ATTRIBUTE13'
449 ,p_attribute13_value => p_rec.eei_attribute13
450 ,p_attribute14_name => 'EEI_ATTRIBUTE14'
451 ,p_attribute14_value => p_rec.eei_attribute14
452 ,p_attribute15_name => 'EEI_ATTRIBUTE15'
453 ,p_attribute15_value => p_rec.eei_attribute15
454 ,p_attribute16_name => 'EEI_ATTRIBUTE16'
458 ,p_attribute18_name => 'EEI_ATTRIBUTE18'
455 ,p_attribute16_value => p_rec.eei_attribute16
456 ,p_attribute17_name => 'EEI_ATTRIBUTE17'
457 ,p_attribute17_value => p_rec.eei_attribute17
459 ,p_attribute18_value => p_rec.eei_attribute18
460 ,p_attribute19_name => 'EEI_ATTRIBUTE19'
461 ,p_attribute19_value => p_rec.eei_attribute19
462 ,p_attribute20_name => 'EEI_ATTRIBUTE20'
463 ,p_attribute20_value => p_rec.eei_attribute20
464 );
465 end if;
466 --
467 hr_utility.set_location(' Leaving:'||l_proc,20);
468 end chk_df;
469 --
470 -- ----------------------------------------------------------------------------
471 -- |-----------------------< chk_non_updateable_args >------------------------|
472 -- ----------------------------------------------------------------------------
473 -- {Start Of Comments}
474 --
475 -- Description:
476 -- This procedure is used to ensure that non updateable attributes have
477 -- not been updated. If an attribute has been updated an error is generated.
478 --
479 -- Pre Conditions:
480 -- g_old_rec has been populated with details of the values currently in
481 -- the database.
482 --
483 -- In Arguments:
484 -- p_rec has been populated with the updated values the user would like the
485 -- record set to.
486 --
487 -- Post Success:
488 -- Processing continues if all the non updateable attributes have not
489 -- changed.
490 --
491 -- Post Failure:
492 -- An application error is raised if any of the non updatable attributes
493 -- have been altered.
494 --
495 -- {End Of Comments}
496 -- ----------------------------------------------------------------------------
497 Procedure chk_non_updateable_args
498 (p_rec in pay_eei_shd.g_rec_type
499 ) IS
500 --
501 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
502 l_error EXCEPTION;
503 l_argument varchar2(30);
504 --
505 Begin
506 --
507 -- Only proceed with the validation if a row exists for the current
508 -- record in the HR Schema.
509 --
510 IF NOT pay_eei_shd.api_updating
511 (p_element_type_extra_info_id => p_rec.element_type_extra_info_id
512 ,p_object_version_number => p_rec.object_version_number
513 ) THEN
514 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
515 fnd_message.set_token('PROCEDURE ', l_proc);
516 fnd_message.set_token('STEP ', '5');
517 fnd_message.raise_error;
518 END IF;
519 --
520 --
521 EXCEPTION
522 WHEN l_error THEN
523 hr_api.argument_changed_error
524 (p_api_name => l_proc
525 ,p_argument => l_argument);
526 WHEN OTHERS THEN
527 RAISE;
528 End chk_non_updateable_args;
529 --
530 -- ----------------------------------------------------------------------------
531 -- |---------------------------< chk_qualifier >------------------------------|
532 -- ----------------------------------------------------------------------------
533 Procedure chk_qualifier
534 (p_pay_source_value in varchar2,
535 p_qualifier in varchar2,
536 p_element_type_id in number
537 ) is
538 --
539 l_proc varchar2(72) := g_package||'chk_qualifier';
540 l_exists varchar2(10);
541 l_business_group_id pay_rates.business_group_id%type ;
542
543 cursor c_input_value is
544 select 'Y'
545 from pay_input_values_f paf
546 where element_type_id = p_element_type_id
547 and upper(name) = upper(p_qualifier)
548 and (( paf.business_group_id is not null
549 and paf.business_group_id = nvl(l_business_group_id,paf.business_group_id))
550 or ( paf.legislation_code is not null
551 and paf.business_group_id is null)
552 or ( paf.legislation_code is null and paf.business_group_id is null )) ;
553
554 cursor c_global is
555 select 'Y'
556 from ff_globals_f fg
557 where upper(fg.global_name) = upper(p_qualifier)
558 and (( fg.business_group_id is not null
559 and fg.business_group_id = nvl(l_business_group_id,fg.business_group_id))
560 or ( fg.legislation_code is not null
561 and fg.business_group_id is null)
562 or ( fg.legislation_code is null and fg.business_group_id is null )) ;
563
564
565 cursor c_grade_rate is
566 select 'Y'
567 from pay_rates pr
568 where upper(name) = upper(p_qualifier)
569 and rate_type = 'G'
570 and pr.business_group_id = nvl(l_business_group_id,pr.business_group_id) ;
571
572 cursor c_grade_rate_like is
573 select 'Y'
574 from pay_rates pr
575 where upper(name) like upper(p_qualifier)
576 and rate_type = 'G'
577 and pr.business_group_id = nvl(l_business_group_id,pr.business_group_id);
578
579
580 cursor c_spinal_point is
581 select 'Y'
582 from pay_rates pr
583 where upper(name) = upper(p_qualifier)
584 and rate_type = 'SP'
585 and pr.business_group_id = nvl(l_business_group_id,pr.business_group_id) ;
586
587 cursor c_spinal_point_like is
588 select 'Y'
589 from pay_rates pr
590 where upper(name) like upper(p_qualifier)
591 and rate_type = 'SP'
592 and pr.business_group_id = nvl(l_business_group_id,pr.business_group_id);
593
594 cursor c_get_bus_grp_id is
595 select business_group_id
599 -- Add Cursors to check the entered Rate type and element Name.
596 from pay_element_types_f pet
597 where pet.element_type_id = p_element_type_id ;
598
600
601 CURSOR c_element_name IS
602 SELECT 'Y'
603 FROM PAY_ELEMENT_TYPES_F pet
604 WHERE UPPER(pet.element_name) = UPPER(p_qualifier)
605 AND (( pet.business_group_id is not null
606 and pet.business_group_id = nvl(l_business_group_id,
607 pet.business_group_id))
608 or ( pet.legislation_code is not null
609 and pet.business_group_id is null)
610 or ( pet.legislation_code is null
611 and pet.business_group_id is null )) ;
612
613 CURSOR c_rate_type IS
614 SELECT 'Y'
615 FROM hr_lookups hrl
616 WHERE hrl.lookup_type = 'PQP_RATE_TYPE'
617 and UPPER(hrl.meaning) = UPPER(p_qualifier)
618 and hrl.enabled_flag = 'Y' ;
619 --
620 -- Added cursor to check that a valid user table is entered for "User Table Value"
621 -- Pay Source Value
622 -- Added for bug 4555325
623 CURSOR c_user_table_name IS
624 SELECT 'Y'
625 FROM pay_user_tables put
626 where upper(user_table_name)=UPPER(p_qualifier)
627 and (( put.business_group_id is not null
628 and put.business_group_id = nvl(l_business_group_id,put.business_group_id))
629 or ( put.legislation_code is not null
630 and put.business_group_id is null)
631 or ( put.legislation_code is null and put.business_group_id is null )) ;
632
633 Begin
634 --
635 hr_utility.set_location('Entering:'||l_proc, 5);
636
637 --
638 open c_get_bus_grp_id ;
639 fetch c_get_bus_grp_id into l_business_group_id;
640
641 if l_business_group_id is null then
642 --
643 l_business_group_id := fnd_global.per_business_group_id ;
644 --
645 end if;
646
647 close c_get_bus_grp_id ;
648
649 --
650
651 if p_pay_source_value = 'IV' then
652 --
653 open c_input_value;
654 fetch c_input_value into l_exists;
655 close c_input_value;
656
657 if l_exists is null then
658 --
659 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
660 hr_utility.raise_error;
661 --
662 end if;
663 --
664 elsif p_pay_source_value = 'GV' then
665 --
666 open c_global;
667 fetch c_global into l_exists;
668 close c_global;
669
670 if l_exists is null then
671 --
672 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
673 hr_utility.raise_error;
674 --
675 end if;
676 --
677 elsif p_pay_source_value = 'GR' then
678 --
679 open c_grade_rate;
680 fetch c_grade_rate into l_exists;
681 --
682 if c_grade_rate%rowcount = 0 then
683 --
684 open c_grade_rate_like ;
685 fetch c_grade_rate_like into l_exists ;
686 close c_grade_rate_like ;
687 --
688 end if;
689 --
690 close c_grade_rate;
691
692 if l_exists is null then
693 --
694 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
695 hr_utility.raise_error;
696 --
697 end if;
698 --
699 elsif p_pay_source_value = 'SP' then
700 --
701 open c_spinal_point;
702 fetch c_spinal_point into l_exists;
703 --
704 if c_spinal_point%rowcount = 0 then
705 --
706 open c_spinal_point_like ;
707 fetch c_spinal_point_like into l_exists ;
708 close c_spinal_point_like ;
709 --
710 end if;
711 --
712 close c_spinal_point;
713
714 if l_exists is null then
715 --
716 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
717 hr_utility.raise_error;
718 --
719 end if;
720 --
721 elsif p_pay_source_value = 'RT' then
722 --
723 open c_rate_type ;
724 fetch c_rate_type into l_exists ;
725 close c_rate_type ;
726
727 if l_exists is null then
728 --
729 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
730 hr_utility.raise_error;
731 --
732 end if;
733 --
734 elsif p_pay_source_value = 'EN' then
735 --
736 open c_element_name ;
737 fetch c_element_name into l_exists ;
738 close c_element_name ;
739
740 if l_exists is null then
741 --
742 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
743 hr_utility.raise_error;
744 --
745 end if;
746 --
747 -- Added for bug 4555325
748 elsif p_pay_source_value = 'TV' then
749 OPEN c_user_table_name;
750 FETCH c_user_table_name into l_exists;
751 CLOSE c_user_table_name;
752
753 if l_exists is null then
754 hr_utility.set_message(8303, 'PQP_230515_INVALID_QUALIFIER');
755 hr_utility.raise_error;
756 end if;
757 -- end for bug 4555325
758 end if;
759
760 hr_utility.set_location('Entering:'||l_proc, 10);
761 --
762 end chk_qualifier;
763 --
764 --
765 -- ----------------------------------------------------------------------------
769 ( p_input_Value in varchar2
766 -- |---------------------------< chk_input_value >------------------------------|
767 -- ----------------------------------------------------------------------------
768 Procedure chk_input_value
770 ,p_element_type_id in number
771 ) is
772 l_proc varchar2(72) := g_package||'chk_input_value';
773 l_exists varchar2(1) ;
774 CURSOR c_input_value IS
775 SELECT 'Y'
776 FROM PAY_INPUT_VALUES_F
777 WHERE element_type_id = p_element_type_id
778 AND UPPER(name) = UPPER(p_input_value) ;
779 begin
780
781 open c_input_value ;
782 fetch c_input_value into l_exists ;
783 close c_input_value ;
784
785 if l_exists is null then
786 --
787 hr_utility.set_message(8303, 'PQP_230021_INVALID_INPUT_VALUE');
788 hr_utility.raise_error;
789 --
790 end if;
791
792 end chk_input_value ;
793 -- ----------------------------------------------------------------------------
794 -- |------------------------< chk_mandatory_params >--------------------------|
795 -- ----------------------------------------------------------------------------
796 -- p_eei_information2 - Pay Source Value
797 -- p_eei_information7 - % or Factor
798 -- p_eei_information8 - % or Factor Value
799 -- p_eei_information9 - % or Factor Input Value
800 -- p_eei_information10- Linked to Assignment
801
802 -- For bug 4555325
803 -- p_eei_information16 - Table Value - Column Name Source
804 -- p_eei_information17 - Table Value - Column Name Qualifier
805 -- p_eei_information18 - Table Value - Row Name Source
806 -- p_eei_information19 - Table Value - Row Name Qualifier
807
808 Procedure chk_mandatory_params
809 ( p_eei_information2 IN VARCHAR2
810 ,p_eei_information7 IN VARCHAR2
811 ,p_eei_information8 IN NUMBER
812 ,p_eei_information9 IN VARCHAR2
813 ,p_eei_information10 IN VARCHAR2
814 -- added for bug 4555325
815 ,p_eei_information3 IN VARCHAR2
816 ,p_eei_information16 IN VARCHAR2
817 ,p_eei_information17 IN VARCHAR2
818 ,p_eei_information18 IN VARCHAR2
819 ,p_eei_information19 IN VARCHAR2
820 ,p_element_type_id IN NUMBER
821 ) is
822 l_proc varchar2(72) := g_package||'chk_mandatory_params';
823
824 -- bug 4555325
825 CURSOR c_get_column_name is
826 select 'Y'
827 from pay_user_columns puc, pay_user_tables put
828 where puc.user_table_id = put.user_table_id
829 and upper(put.user_table_name)= upper(p_eei_information3)
830 and upper(puc.user_column_name) = upper(p_eei_information17);
831
832 CURSOR c_get_row_name is
833 select 'Y'
834 from PAY_USER_ROWS_F pur, pay_user_tables put, fnd_sessions ses
835 where ses.session_id = userenv('SESSIONID')
836 and pur.user_table_id = put.user_table_id
837 and upper(put.user_table_name)= upper(p_eei_information3)
838 and upper(pur.ROW_LOW_RANGE_OR_NAME) = upper(p_eei_information19)
839 and ses.effective_date between pur.effective_start_date
840 and pur.effective_end_date;
841
842 CURSOR c_input_value(p_input_value varchar2) IS
843 SELECT 'Y'
844 FROM PAY_INPUT_VALUES_F
845 WHERE element_type_id = p_element_type_id
846 AND UPPER(name) = UPPER(p_input_value) ;
847
848 l_exists varchar2(1);
849
850 -- end bug 4555325
851
852 begin
853
854 hr_utility.set_location('Entering:'||l_proc,10);
855
856 if p_eei_information2 not in ('RT','EN') then
857
858 -- % or Factor Value OR % or Factor Input Value
859 -- should be entered only when Pay Source Value
860 -- is Element Name or Rate Type
861
862 if p_eei_information8 is not null or
863 p_eei_information9 is not null then
864 hr_utility.set_message(8303, 'PQP_230017_CALC_TYPE');
865 hr_utility.raise_error;
866 end if;
867
868 else
869
870 -- If Pay Source Value is in Element Name or Rate Type
871
872 -- % or Factor Type is Mandatory
873 if p_eei_information7 is null then
874 hr_utility.set_message(8303, 'PQP_230018_CALC_TYPE_MAND');
875 hr_utility.raise_error;
876 end if;
877
878 -- When the Pay Source Value is either Rate Type or Element Name
879 -- then either Calculation Value or Input Value Name should be entered.
880 -- ( please note that only one should be entered )
881 if p_eei_information8 is null and
882 p_eei_information9 is null then
883 hr_utility.set_message(8303, 'PQP_230019_CALC_VAL_OR_INPUT');
884 hr_utility.raise_error;
885 end if;
886 if p_eei_information8 is not null and
887 p_eei_information9 is not null then
888 hr_utility.set_message(8303, 'PQP_230019_CALC_VAL_OR_INPUT');
889 hr_utility.raise_error;
890 end if;
891 end if;
892
893 -- Validate that Input Value Name entered is correct
894 if p_eei_information9 is not null then
895 pay_eei_bus.chk_input_value(p_input_Value => p_eei_information9
896 ,p_element_type_id => p_element_type_id);
897 end if;
898
899 -- If Pay Source Value is other than Input Value then
900 -- Link to Assignment is Mandatory.
901 if p_eei_information2 <> 'IV' then
902 if p_eei_information10 is null then
903 hr_utility.set_message(8303, 'PQP_230020_ASSIGN_MAND');
904 hr_utility.raise_error;
908 IF p_eei_information2 = 'TV' then
905 end if;
906 end if;
907 -- Added validation for bug 4555325
909 if p_eei_information16 is null or p_eei_information17 is null or
910 p_eei_information18 is null or p_eei_information19 is null then
911 hr_utility.set_message(8303, 'PQP_230399_USER_COLUMN_ROW_VAL');
912 hr_utility.raise_error;
913 end if;
914 -- validate column values
915 IF p_eei_information16 = 'DN' then
916 open c_get_column_name;
917 FETCH c_get_column_name into l_exists;
918 CLOSE c_get_column_name;
919 IF l_exists is null THEN
920 hr_utility.set_message(8303, 'PQP_230445_INVAL_USER_COL_NAME');
921 hr_utility.set_message_token('COLUMN_NAME',p_eei_information17);
922 hr_utility.set_message_token('TABLE_NAME',p_eei_information3);
923 fnd_message.raise_error;
924 END IF;
925 else
926 -- note cant use the this procedure as message displayed is incorrect.
927 open c_input_value(p_eei_information17) ;
928 fetch c_input_value into l_exists ;
929 close c_input_value ;
930 if l_exists is null then
931 hr_utility.set_message(8303, 'PQP_230256_INVAL_USER_COL_INP');
932 hr_utility.raise_error;
933 end if;
934
935 end if;
936 -- validate row values.
937 l_exists := null;
938 IF p_eei_information18 = 'DN' then
939 OPEN c_get_row_name;
940 FETCH c_get_row_name into l_exists;
941 CLOSE c_get_row_name;
942 IF l_exists is null then
943 hr_utility.set_message(8303, 'PQP_230296_INVAL_USER_ROW_NAME');
944 hr_utility.set_message_token('ROW_NAME',p_eei_information19);
945 hr_utility.set_message_token('TABLE_NAME',p_eei_information3);
946 hr_utility.raise_error;
947 END IF;
948 else
949 open c_input_value(p_eei_information19) ;
950 fetch c_input_value into l_exists ;
951 close c_input_value ;
952 if l_exists is null then
953 hr_utility.set_message(8303, 'PQP_230443_INVAL_USER_ROW_INP');
954 hr_utility.raise_error;
955 end if;
956 end if;
957
958 END IF;
959 IF p_eei_information2 <> 'TV' THEN
960 -- Table Value - Column Name Qualifier and
961 -- Table Value - Row Name Qualifier is mandatory only if
962 -- Pay source is User Table
963
964 if p_eei_information17 is not null or
965 p_eei_information19 is not null then
966 hr_utility.set_message(8303, 'PQP_230453_TV_VALUE');
967 hr_utility.raise_error;
968 end if;
969 END IF;
970 -- End bug 4555325
971
972 hr_utility.set_location('Leaving:'||l_proc,20);
973
974 end chk_mandatory_params;
975 -- ----------------------------------------------------------------------------
976 -- |---------------------------< chk_duplicate_rate_type >--------------------|
977 -- ----------------------------------------------------------------------------
978 Procedure chk_duplicate_rate_type
979 (p_element_type_extra_info_id in number,
980 p_element_type_id in number,
981 p_rate_type in varchar2
982 ) is
983 --
984 l_proc varchar2(72) := g_package||'chk_duplicate_rate_type';
985 l_result number;
986
987 cursor c_duplicate is
988 select 1
989 from pay_element_type_extra_info
990 where element_type_id = p_element_type_id
991 and element_type_extra_info_id <> nvl(p_element_type_extra_info_id, -1)
992 and information_type = 'PQP_UK_RATE_TYPE'
993 and eei_information1 = p_rate_type;
994
995 begin
996 --
997 hr_utility.set_location('Entering:'||l_proc, 5);
998
999 open c_duplicate;
1000 fetch c_duplicate into l_result;
1001 close c_duplicate;
1002
1003 if l_result = 1 then
1004 --
1005 hr_utility.set_message(8303, 'PQP_230516_DUPLICATE_RATE_TYPE');
1006 hr_utility.raise_error;
1007 --
1008 end if;
1009
1010 hr_utility.set_location('Leaving:'||l_proc, 10);
1011 --
1012 end chk_duplicate_rate_type;
1013 --
1014 -- ----------------------------------------------------------------------------
1015 -- |---------------------------< chk_duplicate_element_code >--------------------|
1016 -- ----------------------------------------------------------------------------
1017 Procedure chk_duplicate_element_code
1018 (p_element_type_id in number
1019 ,p_element_code in varchar2
1020 ,p_information_type in varchar2
1021 ) is
1022 --
1023 l_proc varchar2(72) := g_package||'chk_duplicate_element_code';
1024 l_result number;
1025 l_is_retro number;
1026 l_is_advanced_retro_enabled number;
1027 /*
1028 cursor c_duplicate is
1029 select 1
1030 from pay_element_type_extra_info
1031 where element_type_id <> p_element_type_id
1032 and information_type = p_information_type
1033 and upper(eei_information2) = upper(p_element_code);
1034 */
1035
1036 cursor c_is_advanced_retro_enabled is
1037 select 1
1038 from pay_legislation_rules
1039 where legislation_code = 'GB'
1040 and rule_type like 'ADVANCED_RETRO'
1041 and rule_mode = 'Y';
1042
1043
1044 cursor c_duplicate is
1045 select 1
1046 from pay_element_type_extra_info petei,pay_element_types_f pet
1047 where petei.element_type_id <> p_element_type_id
1048 and information_type = p_information_type
1049 and upper(eei_information2) = upper(p_element_code)
1050 and petei.element_type_id not in ((select element_type_id
1051 from pay_element_types_f
1052 where retro_summ_ele_id = p_element_type_id)
1053 union
1054 (select retro_summ_ele_id
1058 and pet.element_type_id=petei.element_type_id
1055 from pay_element_types_f
1056 where element_type_id = p_element_type_id
1057 and retro_summ_ele_id IS NOT NULL ))
1059 and rownum =1;
1060
1061
1062 cursor c_duplicate_advanced_retro is
1063 select 1
1064 from pay_element_type_extra_info petei,pay_element_types_f pet
1065 where petei.element_type_id <> p_element_type_id
1066 and information_type = p_information_type
1067 and upper(eei_information2) = upper(p_element_code)
1068 and petei.element_type_id not in ((select prcu.creator_id
1069 from pay_element_span_usages pesu, pay_retro_component_usages prcu
1070 where pesu.retro_element_type_id = p_element_type_id
1071 and pesu.retro_component_usage_id = prcu.retro_component_usage_id
1072 and prcu.creator_type = 'ET')
1073 union
1074 (select pesu.retro_element_type_id
1075 from pay_element_span_usages pesu, pay_retro_component_usages prcu
1076 where prcu.creator_id = p_element_type_id
1077 and pesu.retro_component_usage_id = prcu.retro_component_usage_id
1078 and prcu.creator_type = 'ET'))
1079 and pet.element_type_id=petei.element_type_id
1080 and rownum =1;
1081
1082
1083 /*
1084 cursor c_is_retro_ele is
1085 select 1
1086 from pay_element_types_f
1087 where retro_summ_ele_id = p_element_type_id;
1088 */
1089
1090 cursor c_element_code_exists
1091 (
1092 p_info_type VARCHAR2
1093 )
1094 IS
1095 select 1
1096 from pay_element_type_extra_info
1097 where element_type_id = p_element_type_id
1098 and information_type = p_info_type;
1099 begin
1100 --
1101 hr_utility.set_location('Entering:'||l_proc, 5);
1102
1103
1104 open c_is_advanced_retro_enabled;
1105 fetch c_is_advanced_retro_enabled into l_is_advanced_retro_enabled;
1106 IF c_is_advanced_retro_enabled%NOTFOUND
1107 THEN
1108 l_is_advanced_retro_enabled := 0;
1109 END IF;
1110 close c_is_advanced_retro_enabled;
1111
1112 IF l_is_advanced_retro_enabled <> 1
1113 THEN
1114
1115 open c_duplicate;
1116 fetch c_duplicate into l_result;
1117 close c_duplicate;
1118
1119 if l_result = 1 then
1120 --
1121 if p_information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO' then
1122 hr_utility.set_message(8303, 'PQP_230501_DUP_ALLOWANCE_CODE');
1123 elsif p_information_type = 'PQP_GB_PENSERV_BONUS_INFO' then
1124 hr_utility.set_message(8303, 'PQP_230502_DUP_BONUS_CODE');
1125 end if;
1126
1127 hr_utility.raise_error;
1128 --
1129 end if;
1130 ELSE
1131
1132 open c_duplicate_advanced_retro;
1133 fetch c_duplicate_advanced_retro into l_result;
1134 close c_duplicate_advanced_retro;
1135
1136 if l_result = 1 then
1137 --
1138 if p_information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO' then
1139 hr_utility.set_message(8303, 'PQP_230501_DUP_ALLOWANCE_CODE');
1140 elsif p_information_type = 'PQP_GB_PENSERV_BONUS_INFO' then
1141 hr_utility.set_message(8303, 'PQP_230502_DUP_BONUS_CODE');
1142 end if;
1143
1144 hr_utility.raise_error;
1145 --
1146 end if;
1147
1148 END IF;
1149
1150
1151 -- check if other information type exist.
1152 if p_information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO' then
1153 -- check if the element has Bonus info
1154 OPEN c_element_code_exists
1155 (
1156 p_info_type => 'PQP_GB_PENSERV_BONUS_INFO'
1157 );
1158 FETCH c_element_code_exists INTO l_result;
1159 CLOSE c_element_code_exists;
1160
1161 IF l_result = 1 THEN
1162 hr_utility.set_message(8303, 'PQP_230227_ELEMENT_CODE_EXIST');
1163 hr_utility.set_message_token('TOKEN1','Bonus');
1164 hr_utility.set_message_token('TOKEN2','Allowance');
1165 hr_utility.raise_error;
1166 END IF;
1167
1168 elsif p_information_type = 'PQP_GB_PENSERV_BONUS_INFO' then
1169 -- check if the element has Bonus info
1170 OPEN c_element_code_exists
1171 (
1172 p_info_type => 'PQP_GB_PENSERV_ALLOWANCE_INFO'
1173 );
1174 FETCH c_element_code_exists INTO l_result;
1175 CLOSE c_element_code_exists;
1176
1177 IF l_result = 1 THEN
1178 hr_utility.set_message(8303, 'PQP_230227_ELEMENT_CODE_EXIST');
1179 hr_utility.set_message_token('TOKEN1','Allowance');
1180 hr_utility.set_message_token('TOKEN2','Bonus');
1181 hr_utility.raise_error;
1182 END IF;
1183
1184 end if;
1185
1186 hr_utility.set_location('Leaving:'||l_proc, 10);
1187 --
1188 end chk_duplicate_element_code;
1189 --
1190
1191 -- ----------------------------------------------------------------------------
1192 -- |---------------------------< chk_allow_spread_bonus_chk >--------------------|
1193 -- ----------------------------------------------------------------------------
1194 Procedure chk_allow_spread_bonus_chk
1195 (p_element_type_id in number
1196 ,p_spread_bonus_flag in varchar2
1197 ) is
1198
1199 --
1200 l_proc varchar2(72) := g_package||'chk_allow_spread_bonus_chk';
1201 l_result number;
1202
1206 SELECT 1
1203
1204 CURSOR is_ele_spread_bonus
1205 IS
1207 FROM pay_element_types_f pet
1208 ,pay_input_values_f piv
1209 WHERE piv.element_type_id = pet.element_type_id
1210 AND pet.element_type_id = p_element_type_id
1211 AND (pet.processing_type = 'N'
1212 AND UPPER(piv.NAME) = UPPER('CLAIM DATE'))
1213 AND rownum = 1;
1214
1215
1216 begin
1217 --
1218 hr_utility.set_location('Entering:'||l_proc, 5);
1219
1220 IF p_spread_bonus_flag = 'Y'
1221 THEN
1222
1223 open is_ele_spread_bonus;
1224 fetch is_ele_spread_bonus into l_result;
1225 close is_ele_spread_bonus;
1226
1227 IF l_result IS NULL
1228 THEN
1229 hr_utility.set_message(8303, 'PQP_230591_INV_SPREAD_BONUS');
1230 hr_utility.raise_error;
1231 END IF;
1232 END IF;
1233
1234 hr_utility.set_location('Leaving:'||l_proc, 10);
1235 --
1236 end chk_allow_spread_bonus_chk;
1237 --
1238 -- ----------------------------------------------------------------------------
1239 -- |---------------------------< chk_valid_bonus_elements >--------------------|
1240 -- ----------------------------------------------------------------------------
1241 PROCEDURE chk_valid_bonus_elements
1242 (p_element_type_id in number
1243 )
1244 IS
1245
1246 --
1247 l_proc varchar2(72) := g_package||'chk_valid_bonus_elements';
1248
1249 l_element_name pay_element_types_f.element_name%TYPE;
1250 l_processing_type pay_element_types_f.processing_type%TYPE;
1251 l_uom pay_input_values_f.uom%TYPE;
1252
1253 CURSOR csr_get_element_info
1254 IS
1255 SELECT pet.element_name
1256 ,pet.processing_type
1257 ,piv.uom
1258 FROM pay_element_types_f pet,
1259 pay_input_values_f piv
1260 WHERE pet.element_type_id = p_element_type_id
1261 AND pet.element_type_id = piv.element_type_id (+)
1262 AND upper(piv.NAME (+)) = 'CLAIM DATE';
1263
1264
1265 BEGIN
1266 --
1267 hr_utility.set_location('Entering:'||l_proc, 5);
1268
1269 OPEN csr_get_element_info;
1270 FETCH csr_get_element_info INTO l_element_name,l_processing_type,l_uom;
1271 CLOSE csr_get_element_info;
1272
1273 IF upper(l_processing_type) = 'R' THEN
1274 hr_utility.set_message(8303, 'PQP_230495_REC_BON_ELEMENT');
1275 hr_utility.raise_error;
1276 ELSIF l_uom IS NULL THEN
1277 -- no claim date
1278 hr_utility.set_message(8303, 'PQP_230496_BON_NO_CLAIM_DATE');
1279 hr_utility.raise_error;
1280 ELSIF l_uom <> 'D' THEN
1281 -- unit of measure is not date
1282 hr_utility.set_message(8303, 'PQP_230497_BON_INV_CLAIM_DATE');
1283 hr_utility.raise_error;
1284 END IF;
1285
1286 hr_utility.set_location('Leaving:'||l_proc, 10);
1287 --
1288 END chk_valid_bonus_elements;
1289
1290 -- ----------------------------------------------------------------------------
1291 -- |---------------------------< insert_validate >----------------------------|
1292 -- ----------------------------------------------------------------------------
1293 Procedure insert_validate
1294 (p_rec in pay_eei_shd.g_rec_type
1295 ) is
1296 --
1297 cursor row_type(p_element_type_id number)
1298 is
1299 select business_group_id
1300 from pay_element_types_f
1301 where element_type_id = p_element_type_id
1302 and business_group_id is not null
1303 and rownum = 1;
1304 --
1305 l_bg pay_element_types_f.business_group_id%type;
1306 l_proc varchar2(72) := g_package||'insert_validate';
1307 --
1308 Begin
1309 hr_utility.set_location('Entering:'||l_proc, 5);
1310 --
1311 -- Set the profile value for 'PAY_ELEMENT_TYPE_ID' for the purpose of valuesets used for payroll simulation
1312 fnd_profile.put('PAY_ELEMENT_TYPE_ID',p_rec.element_type_id);
1313 --
1314 -- Call all supporting business operations
1315 -- Bug 3041843
1316 --
1317 open row_type(p_rec.element_type_id);
1318 fetch row_type into l_bg;
1319 if row_type%found then
1320 --
1321 close row_type;
1322 pay_etp_bus.set_security_group_id
1323 (p_element_type_id => p_rec.element_type_id);
1324 else
1325 close row_type;
1326 --
1327 -- if no row is found them must be seeded row, so hard code the
1328 -- security group. This is not a satisfactory solution.
1329 --
1330 hr_api.set_security_group_id(p_security_group_id => 0);
1331 --
1332 end if;
1333 --
1334 pay_eei_bus.chk_ddf(p_rec);
1335 --
1336 pay_eei_bus.chk_df(p_rec);
1337 --
1338
1339 if (p_rec.information_type = 'PQP_UK_ELEMENT_ATTRIBUTION') then
1340 --
1341 pay_eei_bus.chk_qualifier(p_pay_source_value => p_rec.eei_information2,
1342 p_qualifier => p_rec.eei_information3,
1343 p_element_type_id => p_rec.element_type_id );
1344
1345
1346
1347 pay_eei_bus.chk_mandatory_params(
1348 p_eei_information2 => p_rec.eei_information2
1349 ,p_eei_information7 => p_rec.eei_information7
1350 ,p_eei_information8 => p_rec.eei_information8
1351 ,p_eei_information9 => p_rec.eei_information9
1352 ,p_eei_information10 => p_rec.eei_information10
1353 -- added for bug 4555325
1354 ,p_eei_information3 => p_rec.eei_information3
1355 ,p_eei_information16 => p_rec.eei_information16
1359 ,p_element_type_id => p_rec.element_type_id
1356 ,p_eei_information17 => p_rec.eei_information17
1357 ,p_eei_information18 => p_rec.eei_information18
1358 ,p_eei_information19 => p_rec.eei_information19
1360 ) ;
1361 --
1362 end if;
1363
1364 if (p_rec.information_type = 'PQP_UK_RATE_TYPE') then
1365 --
1366 pay_eei_bus.chk_duplicate_rate_type (
1367 p_element_type_extra_info_id => p_rec.element_type_extra_info_id,
1368 p_element_type_id => p_rec.element_type_id,
1369 p_rate_type => p_rec.eei_information1
1370 );
1371 --
1372 end if;
1373
1374 /*
1375 if (p_rec.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO') OR
1376 (p_rec.information_type = 'PQP_GB_PENSERV_BONUS_INFO')then
1377 --
1378 pay_eei_bus.chk_duplicate_element_code (
1379 p_element_type_id => p_rec.element_type_id
1380 ,p_element_code => p_rec.eei_information2
1381 ,p_information_type => p_rec.information_type
1382 );
1383 --
1384 end if;
1385 */
1386 if (p_rec.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO') then
1387 --
1388 pay_eei_bus.chk_duplicate_element_code (
1389 p_element_type_id => p_rec.element_type_id
1390 ,p_element_code => p_rec.eei_information2
1391 ,p_information_type => p_rec.information_type
1392 );
1393
1394 pay_eei_bus.chk_allow_spread_bonus_chk (
1395 p_element_type_id => p_rec.element_type_id
1396 ,p_spread_bonus_flag => p_rec.eei_information6
1397 );
1398
1399 elsif (p_rec.information_type = 'PQP_GB_PENSERV_BONUS_INFO') then
1400 --
1401 pay_eei_bus.chk_duplicate_element_code (
1402 p_element_type_id => p_rec.element_type_id
1403 ,p_element_code => p_rec.eei_information2
1404 ,p_information_type => p_rec.information_type
1405 );
1406
1407 pay_eei_bus.chk_valid_bonus_elements (
1408 p_element_type_id => p_rec.element_type_id
1409 );
1410 --
1411 end if;
1412
1413
1414
1415 --
1416 hr_utility.set_location(' Leaving:'||l_proc, 10);
1417 End insert_validate;
1418 --
1419 -- ----------------------------------------------------------------------------
1420 -- |---------------------------< update_validate >----------------------------|
1421 -- ----------------------------------------------------------------------------
1422 Procedure update_validate
1423 (p_rec in pay_eei_shd.g_rec_type
1424 ) is
1425 --
1426 cursor row_type(p_element_type_id number)
1427 is
1428 select business_group_id
1429 from pay_element_types_f
1430 where element_type_id = p_element_type_id
1431 and business_group_id is not null
1432 and rownum = 1;
1433 --
1434 l_bg pay_element_types_f.business_group_id%type;
1435 l_proc varchar2(72) := g_package||'update_validate';
1436 --
1437 Begin
1438 hr_utility.set_location('Entering:'||l_proc, 5);
1439 --
1440 -- Set the profile value for 'PAY_ELEMENT_TYPE_ID' for the purpose of valuesets used for payroll simulation
1441 fnd_profile.put('PAY_ELEMENT_TYPE_ID',p_rec.element_type_id);
1442 --
1443 -- Call all supporting business operations
1444 --
1445 open row_type(p_rec.element_type_id);
1446 fetch row_type into l_bg;
1447 if row_type%found then
1448 --
1449 close row_type;
1450 pay_etp_bus.set_security_group_id
1451 (p_element_type_id => p_rec.element_type_id);
1452 else
1453 close row_type;
1454 --
1455 -- if no row is found them must be seeded row, so hard code the
1456 -- security group. This is not a satisfactory solution.
1457 --
1458 hr_api.set_security_group_id(p_security_group_id => 0);
1459 --
1460 end if;
1461 --
1462 chk_non_updateable_args
1463 (p_rec => p_rec
1464 );
1465 --
1466 --
1467 pay_eei_bus.chk_ddf(p_rec);
1468 --
1469 pay_eei_bus.chk_df(p_rec);
1470 --
1471
1472 if (p_rec.information_type = 'PQP_UK_ELEMENT_ATTRIBUTION') then
1473 --
1474 pay_eei_bus.chk_qualifier(p_pay_source_value => p_rec.eei_information2,
1475 p_qualifier => p_rec.eei_information3,
1476 p_element_type_id => p_rec.element_type_id);
1477 --
1478
1479 pay_eei_bus.chk_mandatory_params(
1480 p_eei_information2 => p_rec.eei_information2
1481 ,p_eei_information7 => p_rec.eei_information7
1482 ,p_eei_information8 => p_rec.eei_information8
1483 ,p_eei_information9 => p_rec.eei_information9
1484 ,p_eei_information10 => p_rec.eei_information10
1485 -- added for bug 4555325
1486 ,p_eei_information3 => p_rec.eei_information3
1487 ,p_eei_information16 => p_rec.eei_information16
1488 ,p_eei_information17 => p_rec.eei_information17
1489 ,p_eei_information18 => p_rec.eei_information18
1490 ,p_eei_information19 => p_rec.eei_information19
1491 ,p_element_type_id => p_rec.element_type_id
1492 ) ;
1493
1494 end if;
1495 if (p_rec.information_type = 'PQP_UK_RATE_TYPE') then
1496 --
1497 pay_eei_bus.chk_duplicate_rate_type (
1498 p_element_type_extra_info_id => p_rec.element_type_extra_info_id,
1499 p_element_type_id => p_rec.element_type_id,
1500 p_rate_type => p_rec.eei_information1
1501 );
1502 --
1503 end if;
1504
1505 /*
1506 if (p_rec.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO') OR
1507 (p_rec.information_type = 'PQP_GB_PENSERV_BONUS_INFO')then
1508 --
1509 pay_eei_bus.chk_duplicate_element_code (
1510 p_element_type_id => p_rec.element_type_id
1511 ,p_element_code => p_rec.eei_information2
1512 ,p_information_type => p_rec.information_type
1513 );
1514 --
1515 end if;
1516 */
1517
1518 if (p_rec.information_type = 'PQP_GB_PENSERV_ALLOWANCE_INFO') then
1519 --
1520 pay_eei_bus.chk_duplicate_element_code (
1521 p_element_type_id => p_rec.element_type_id
1522 ,p_element_code => p_rec.eei_information2
1523 ,p_information_type => p_rec.information_type
1524 );
1525
1526 pay_eei_bus.chk_allow_spread_bonus_chk (
1527 p_element_type_id => p_rec.element_type_id
1528 ,p_spread_bonus_flag => p_rec.eei_information6
1529 );
1530
1531 elsif (p_rec.information_type = 'PQP_GB_PENSERV_BONUS_INFO') then
1532 --
1533 pay_eei_bus.chk_duplicate_element_code (
1534 p_element_type_id => p_rec.element_type_id
1535 ,p_element_code => p_rec.eei_information2
1536 ,p_information_type => p_rec.information_type
1537 );
1538 pay_eei_bus.chk_valid_bonus_elements (
1539 p_element_type_id => p_rec.element_type_id
1540 );
1541 --
1542 end if;
1543
1544 --
1545 hr_utility.set_location(' Leaving:'||l_proc, 10);
1546 End update_validate;
1547 --
1548 -- ----------------------------------------------------------------------------
1549 -- |---------------------------< delete_validate >----------------------------|
1550 -- ----------------------------------------------------------------------------
1551 Procedure delete_validate
1552 (p_rec in pay_eei_shd.g_rec_type
1553 ) is
1554 --
1555 l_proc varchar2(72) := g_package||'delete_validate';
1556 --
1557 Begin
1558 hr_utility.set_location('Entering:'||l_proc, 5);
1559 --
1560 -- Call all supporting business operations
1561 --
1562 hr_utility.set_location(' Leaving:'||l_proc, 10);
1563 End delete_validate;
1564 ---
1565 end pay_eei_bus;