[Home] [Help]
PACKAGE BODY: APPS.PAY_AUD_BUS
Source
1 Package Body pay_aud_bus as
2 /* $Header: pyaudrhi.pkb 115.4 2002/12/09 10:29:32 alogue ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_aud_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_stat_trans_audit_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |--------------------------< chk_person_id >------------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 procedure chk_person_id
21 (p_person_id in pay_stat_trans_audit.person_id%TYPE
22 ,p_business_group_id in pay_stat_trans_audit.business_group_id%TYPE
23 ,p_effective_date in pay_stat_trans_audit.transaction_effective_date%TYPE
24 )
25 is
26 --
27 l_exists varchar2(1);
28 l_business_group_id number(15);
29 l_proc varchar2(72) := g_package||'chk_person_id';
30 --
31 cursor csr_get_bus_grp is
32 select ppf.business_group_id
33 from per_people_f ppf
34 where ppf.person_id = p_person_id
35 and p_effective_date between ppf.effective_start_date
36 and ppf.effective_end_date;
37 --
38 --
39 begin
40 hr_utility.set_location('Entering:'|| l_proc, 1);
41 --
42 -- Check mandatory parameters have been set
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'person_id'
47 ,p_argument_value => p_person_id
48 );
49 --
50 hr_utility.set_location(l_proc, 2);
51 --
52 -- Check that person business group is the same as
53 -- the transaction business group
54 --
55 open csr_get_bus_grp;
56 fetch csr_get_bus_grp into l_business_group_id;
57 if l_business_group_id <> p_business_group_id then
58 close csr_get_bus_grp;
59 hr_utility.set_message(801, 'PAY_289127_TRANS_BG_NOT_PER_BG');
60 hr_utility.raise_error;
61 end if;
62 close csr_get_bus_grp;
63 --
64 hr_utility.set_location(' Leaving:'|| l_proc, 3);
65 end chk_person_id;
66 -- ----------------------------------------------------------------------------
67 -- |------< chk_assignment_id >------|
68 -- ----------------------------------------------------------------------------
69 --
70 -- Description
71 -- This procedure validates the assignment_id with the following checks:
72 -- - the assignment_id exists in PER_ASSIGNMENTS_F
73 -- - the assignment's business group must match the tax record's bus grp.
74 -- The record's business_group_id is also validated by checking that it
75 -- matches an existing business_group_id in PER_ASSIGNMENTS_F.
76 --
77 -- Pre-Conditions
78 -- None.
79 --
80 -- In Parameters
81 -- p_assignment_id ID of FK column
82 -- p_business_group_id business group id
83 -- p_effective_date session date
84 --
85 -- Post Success
86 -- Processing continues
87 --
88 -- Post Failure
89 -- Error raised.
90 --
91 -- Access Status
92 -- Internal table handler use only.
93 --
94 Procedure chk_assignment_id
95 (p_assignment_id in pay_stat_trans_audit.assignment_id%TYPE
96 ,p_business_group_id in pay_stat_trans_audit.business_group_id%TYPE
97 ,p_effective_date in pay_stat_trans_audit.transaction_effective_date%TYPE
98 ) is
99 --
100 l_proc varchar2(72) := g_package||'chk_assignment_id';
101 l_dummy varchar2(1);
102 l_business_group_id per_assignments_f.business_group_id%TYPE;
103 --
104 cursor csr_bg_id is
105 select business_group_id
106 from per_assignments_f asg
107 where asg.assignment_id = p_assignment_id
108 and p_effective_date between asg.effective_start_date
109 and asg.effective_end_date;
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'||l_proc,5);
114 --
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'effective_date'
119 ,p_argument_value => p_effective_date
120 );
121 --
122 if (p_assignment_id is not null) then
123 --
124 open csr_bg_id;
125 --
126 fetch csr_bg_id into l_business_group_id;
127 if csr_bg_id%notfound then
128 --
129 close csr_bg_id;
130 --
131 -- raise error as assignment_id not found in per_assignments_f
132 -- table.
133 --
134 hr_utility.set_message(801, 'HR_51746_ASG_INV_ASG_ID');
135 hr_utility.raise_error;
136 --
137 else
138 --
139 if p_business_group_id <> l_business_group_id then
140 --
141 close csr_bg_id;
142 --
143 hr_utility.set_message(801, 'PAY_289128_TRANS_BG_NOT_ASG_BG');
144 hr_utility.raise_error;
145 --
146 end if;
147 --
148 close csr_bg_id;
149 --
150 end if;
151 --
152 end if;
153 --
154 hr_utility.set_location('Leaving:'||l_proc,10);
155 --
156 End chk_assignment_id;
157 -- ---------------------------------------------------------------------------
158 -- |--------------------------< chk_source >------------------------------|
159 -- ---------------------------------------------------------------------------
160 --
161 procedure chk_source
162 (p_source in pay_stat_trans_audit.source1%TYPE
163 ,p_source_type in pay_stat_trans_audit.source1_type%TYPE
164 ,p_effective_date in pay_stat_trans_audit.transaction_effective_date%TYPE
165 )
166 is
167 --
168 l_proc varchar2(72) := g_package||'chk_source';
169 --
170 --
171 begin
172 hr_utility.set_location('Entering:'|| l_proc, 1);
173 --
174 -- Check mandatory parameters have been set
175 --
176 hr_api.mandatory_arg_error
177 (p_api_name => l_proc
178 ,p_argument => 'p_effective_date'
179 ,p_argument_value => p_effective_date
180 );
181 --
182 hr_utility.set_location(l_proc, 2);
183 --
184 -- make sure that the source type is valid
185 if p_source_type is not null then
186 if hr_api.not_exists_in_hr_lookups
187 (p_effective_date => p_effective_date
188 ,p_lookup_type => 'PAY_TRANSACTION_SOURCE_TYPE'
189 ,p_lookup_code => p_source_type
190 ) then
191 hr_utility.set_message(801, 'PAY_289131_AUD_SRC_TYPE_INVAL');
192 hr_utility.raise_error;
193 end if;
194 end if;
195
196 -- make sure if p_source is specified then p_source_type is specified
197 if (p_source is not null) and (p_source_type is null) then
198 hr_utility.set_message(801, 'PAY_289132_AUD_SRC_NO_SRC_TYPE');
199 end if;
200
201 hr_utility.set_location(' Leaving:'|| l_proc, 3);
202 end chk_source;
203 -- ---------------------------------------------------------------------------
204 -- |--------------------------< chk_transaction_type>------------------------|
205 -- ---------------------------------------------------------------------------
206 --
207 procedure chk_transaction_type
208 (p_transaction_type in pay_stat_trans_audit.transaction_type%TYPE
209 ,p_transaction_subtype in pay_stat_trans_audit.transaction_subtype%TYPE
210 ,p_effective_date in pay_stat_trans_audit.transaction_effective_date%TYPE
211 )
212 is
213 --
214 l_proc varchar2(72) := g_package||'chk_transaction_type';
215 --
216 --
217 begin
218 hr_utility.set_location('Entering:'|| l_proc, 1);
219 --
220 -- Check mandatory parameters have been set
221 --
222 hr_api.mandatory_arg_error
223 (p_api_name => l_proc
224 ,p_argument => 'p_effective_date'
225 ,p_argument_value => p_effective_date
226 );
227 --
228 hr_api.mandatory_arg_error
229 (p_api_name => l_proc
230 ,p_argument => 'p_transaction_type'
231 ,p_argument_value => p_transaction_type
232 );
233 hr_utility.set_location(l_proc, 2);
234 --
235 -- make sure that the transaction type is valid
236 if hr_api.not_exists_in_hr_lookups
237 (p_effective_date => p_effective_date
238 ,p_lookup_type => 'PAY_TRANSACTION_TYPE'
239 ,p_lookup_code => p_transaction_type
240 ) then
241 hr_utility.set_message(801, 'PAY_289129_AUD_TRANS_TYPE_INVL');
242 hr_utility.raise_error;
243 end if;
244
245 -- make sure that if a subtype is specified, it is valid
246 if p_transaction_subtype is not null and
247 hr_api.not_exists_in_hr_lookups
248 (p_effective_date => p_effective_date
249 ,p_lookup_type => p_transaction_type
250 ,p_lookup_code => p_transaction_subtype
251 ) then
252 hr_utility.set_message(801, 'PAY_289130_AUD_SUB_TYPE_INVAL');
253 hr_utility.raise_error;
254 end if;
255
256 hr_utility.set_location(' Leaving:'|| l_proc, 3);
257 end chk_transaction_type;
258 -- ---------------------------------------------------------------------------
259 -- |---------------------< return_legislation_code >-------------------------|
260 -- ---------------------------------------------------------------------------
261 --
262 Function return_legislation_code
263 (p_stat_trans_audit_id in number
264 )
265 Return Varchar2 Is
266 --
267 -- Declare cursor
268 --
269 cursor csr_leg_code is
270 select pbg.legislation_code
271 from per_business_groups pbg
272 , pay_stat_trans_audit aud
273 where aud.stat_trans_audit_id = p_stat_trans_audit_id
274 and pbg.business_group_id = aud.business_group_id;
275 --
276 -- Declare local variables
277 --
278 l_legislation_code varchar2(150);
279 l_proc varchar2(72) := g_package||'return_legislation_code';
280 --
281 Begin
282 --
283 hr_utility.set_location('Entering:'|| l_proc, 10);
284 --
285 -- Ensure that all the mandatory parameter are not null
286 --
287 hr_api.mandatory_arg_error
288 (p_api_name => l_proc
289 ,p_argument => 'stat_trans_audit_id'
290 ,p_argument_value => p_stat_trans_audit_id
291 );
292 --
293 if ( nvl(pay_aud_bus.g_stat_trans_audit_id, hr_api.g_number)
294 = p_stat_trans_audit_id) then
295 --
296 -- The legislation code has already been found with a previous
297 -- call to this function. Just return the value in the global
298 -- variable.
299 --
300 l_legislation_code := pay_aud_bus.g_legislation_code;
301 hr_utility.set_location(l_proc, 20);
302 else
303 --
304 -- The ID is different to the last call to this function
305 -- or this is the first call to this function.
306 --
307 open csr_leg_code;
308 fetch csr_leg_code into l_legislation_code;
309 --
310 if csr_leg_code%notfound then
311 --
312 -- The primary key is invalid therefore we must error
313 --
314 close csr_leg_code;
315 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
316 fnd_message.raise_error;
317 end if;
318 hr_utility.set_location(l_proc,30);
319 --
320 -- Set the global variables so the values are
321 -- available for the next call to this function.
322 --
323 close csr_leg_code;
324 pay_aud_bus.g_stat_trans_audit_id := p_stat_trans_audit_id;
325 pay_aud_bus.g_legislation_code := l_legislation_code;
326 end if;
327 hr_utility.set_location(' Leaving:'|| l_proc, 40);
328 return l_legislation_code;
329 end return_legislation_code;
330 --
331 -- ----------------------------------------------------------------------------
332 -- |-----------------------------< chk_ddf >----------------------------------|
333 -- ----------------------------------------------------------------------------
334 --
335 -- Description:
336 -- Validates all the Developer Descriptive Flexfield values.
337 --
338 -- Prerequisites:
339 -- All other columns have been validated. Must be called as the
340 -- last step from insert_validate and update_validate.
341 --
342 -- In Arguments:
343 -- p_rec
344 --
345 -- Post Success:
346 -- If the Developer Descriptive Flexfield structure column and data values
347 -- are all valid this procedure will end normally and processing will
348 -- continue.
349 --
350 -- Post Failure:
351 -- If the Developer Descriptive Flexfield structure column value or any of
352 -- the data values are invalid then an application error is raised as
353 -- a PL/SQL exception.
354 --
355 -- Access Status:
356 -- Internal Row Handler Use Only.
357 --
358 -- ----------------------------------------------------------------------------
359 procedure chk_ddf
360 (p_rec in pay_aud_shd.g_rec_type
361 ) is
362 --
363 l_proc varchar2(72) := g_package || 'chk_ddf';
364 --
365 begin
366 hr_utility.set_location('Entering:'||l_proc,10);
367 --
368 hr_dflex_utility.ins_or_upd_descflex_attribs
369 (p_appl_short_name => 'PAY'
370 ,p_descflex_name => 'PAY_STAT_TRANS_AUDIT_DDF'
371 ,p_attribute_category => p_rec.audit_information_category
372 ,p_attribute1_name => 'AUDIT_INFORMATION1'
373 ,p_attribute1_value => p_rec.audit_information1
374 ,p_attribute2_name => 'AUDIT_INFORMATION2'
375 ,p_attribute2_value => p_rec.audit_information2
376 ,p_attribute3_name => 'AUDIT_INFORMATION3'
377 ,p_attribute3_value => p_rec.audit_information3
378 ,p_attribute4_name => 'AUDIT_INFORMATION4'
379 ,p_attribute4_value => p_rec.audit_information4
380 ,p_attribute5_name => 'AUDIT_INFORMATION5'
381 ,p_attribute5_value => p_rec.audit_information5
382 ,p_attribute6_name => 'AUDIT_INFORMATION6'
383 ,p_attribute6_value => p_rec.audit_information6
384 ,p_attribute7_name => 'AUDIT_INFORMATION7'
385 ,p_attribute7_value => p_rec.audit_information7
386 ,p_attribute8_name => 'AUDIT_INFORMATION8'
387 ,p_attribute8_value => p_rec.audit_information8
388 ,p_attribute9_name => 'AUDIT_INFORMATION9'
389 ,p_attribute9_value => p_rec.audit_information9
390 ,p_attribute10_name => 'AUDIT_INFORMATION10'
391 ,p_attribute10_value => p_rec.audit_information10
392 ,p_attribute11_name => 'AUDIT_INFORMATION11'
393 ,p_attribute11_value => p_rec.audit_information11
394 ,p_attribute12_name => 'AUDIT_INFORMATION12'
395 ,p_attribute12_value => p_rec.audit_information12
396 ,p_attribute13_name => 'AUDIT_INFORMATION13'
397 ,p_attribute13_value => p_rec.audit_information13
398 ,p_attribute14_name => 'AUDIT_INFORMATION14'
399 ,p_attribute14_value => p_rec.audit_information14
403 ,p_attribute16_value => p_rec.audit_information16
400 ,p_attribute15_name => 'AUDIT_INFORMATION15'
401 ,p_attribute15_value => p_rec.audit_information15
402 ,p_attribute16_name => 'AUDIT_INFORMATION16'
404 ,p_attribute17_name => 'AUDIT_INFORMATION17'
405 ,p_attribute17_value => p_rec.audit_information17
406 ,p_attribute18_name => 'AUDIT_INFORMATION18'
407 ,p_attribute18_value => p_rec.audit_information18
408 ,p_attribute19_name => 'AUDIT_INFORMATION19'
409 ,p_attribute19_value => p_rec.audit_information19
410 ,p_attribute20_name => 'AUDIT_INFORMATION20'
411 ,p_attribute20_value => p_rec.audit_information20
412 ,p_attribute21_name => 'AUDIT_INFORMATION21'
413 ,p_attribute21_value => p_rec.audit_information21
414 ,p_attribute22_name => 'AUDIT_INFORMATION22'
415 ,p_attribute22_value => p_rec.audit_information22
416 ,p_attribute23_name => 'AUDIT_INFORMATION23'
417 ,p_attribute23_value => p_rec.audit_information23
418 ,p_attribute24_name => 'AUDIT_INFORMATION24'
419 ,p_attribute24_value => p_rec.audit_information24
420 ,p_attribute25_name => 'AUDIT_INFORMATION25'
421 ,p_attribute25_value => p_rec.audit_information25
422 ,p_attribute26_name => 'AUDIT_INFORMATION26'
426 ,p_attribute28_name => 'AUDIT_INFORMATION28'
423 ,p_attribute26_value => p_rec.audit_information26
424 ,p_attribute27_name => 'AUDIT_INFORMATION27'
425 ,p_attribute27_value => p_rec.audit_information27
427 ,p_attribute28_value => p_rec.audit_information28
428 ,p_attribute29_name => 'AUDIT_INFORMATION29'
429 ,p_attribute29_value => p_rec.audit_information29
430 ,p_attribute30_name => 'AUDIT_INFORMATION30'
431 ,p_attribute30_value => p_rec.audit_information30
432 );
433 --
434 hr_utility.set_location(' Leaving:'||l_proc,20);
435 end chk_ddf;
436 -- ----------------------------------------------------------------------------
437 -- |---------------------------< insert_validate >----------------------------|
438 -- ----------------------------------------------------------------------------
439 Procedure insert_validate
440 (p_effective_date in date
441 ,p_rec in pay_aud_shd.g_rec_type
442 ) is
443 --
444 l_proc varchar2(72) := g_package||'insert_validate';
445 --
446 Begin
447 hr_utility.set_location('Entering:'||l_proc, 5);
448 --
449 -- Call all supporting business operations
450 --
451 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
452 -- Check the transaction type
453 chk_transaction_type(p_transaction_type => p_rec.transaction_type
454 ,p_transaction_subtype => p_rec.transaction_subtype
455 ,p_effective_date => p_rec.transaction_effective_date
456 );
457
458 -- Check the source columns
459 chk_source(p_source => p_rec.source1
460 ,p_source_type => p_rec.source1_type
461 ,p_effective_date => p_rec.transaction_effective_date
462 );
463 chk_source(p_source => p_rec.source2
464 ,p_source_type => p_rec.source2_type
465 ,p_effective_date => p_rec.transaction_effective_date
466 );
467 chk_source(p_source => p_rec.source3
468 ,p_source_type => p_rec.source3_type
469 ,p_effective_date => p_rec.transaction_effective_date
470 );
471 chk_source(p_source => p_rec.source4
472 ,p_source_type => p_rec.source4_type
473 ,p_effective_date => p_rec.transaction_effective_date
474 );
475 chk_source(p_source => p_rec.source5
476 ,p_source_type => p_rec.source5_type
477 ,p_effective_date => p_rec.transaction_effective_date
478 );
479
480 -- Check the foriegn keys
481 chk_assignment_id(p_assignment_id => p_rec.assignment_id
482 ,p_business_group_id => p_rec.business_group_id
483 ,p_effective_date => p_rec.transaction_effective_date
484 );
485
486 chk_person_id(p_person_id => p_rec.person_id
487 ,p_business_group_id => p_rec.business_group_id
488 ,p_effective_date => p_rec.transaction_effective_date
489 );
490
491 --
492 --
493 pay_aud_bus.chk_ddf(p_rec);
494 --
495 hr_utility.set_location(' Leaving:'||l_proc, 10);
496 End insert_validate;
497 --
498 -- ----------------------------------------------------------------------------
499 -- |---------------------------< delete_validate >----------------------------|
500 -- ----------------------------------------------------------------------------
501 Procedure delete_validate
502 (p_rec in pay_aud_shd.g_rec_type
503 ) is
504 --
505 l_proc varchar2(72) := g_package||'delete_validate';
506 --
507 Begin
508 hr_utility.set_location('Entering:'||l_proc, 5);
509 --
510 -- Call all supporting business operations
511 --
512 hr_utility.set_location(' Leaving:'||l_proc, 10);
513 End delete_validate;
514 --
515 end pay_aud_bus;