1 Package Body pay_con_bus as
2 /* $Header: pyconrhi.pkb 115.3 1999/12/03 16:45:29 pkm ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_con_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_contr_history_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- contr_history_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_contr_history_id(p_contr_history_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_contr_history_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pay_con_shd.api_updating
47 (p_contr_history_id => p_contr_history_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_contr_history_id,hr_api.g_number)
52 <> pay_con_shd.g_old_rec.contr_history_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pay_con_shd.constraint_error('PAY_US_CONTRIBUTION_HISTORY_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_contr_history_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pay_con_shd.constraint_error('PAY_US_CONTRIBUTION_HISTORY_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_contr_history_id;
75 -- ----------------------------------------------------------------------------
76 -- |------< valid_date_return >------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- Description
80 -- This procedure is used to check that the From Date and the To_date
81 -- are in the same calender year.
82 --
83 -- Pre Conditions
84 -- None.
85 --
86 -- In Parameters
87 -- p_from_Date From Date of the record being inserted or updated.
88 -- p_to_Date To Date of the record being inserted or updated.
89 --
90 -- Post Success
91 -- Processing continues
92 --
93 -- Post Failure
94 -- Errors handled by the procedure
95 --
96 -- Access Status
97 -- Internal table handler use only.
98 --
99 Procedure valid_date_return
100 (p_from_date in date,
101 p_to_date in date) is
102 l_proc varchar2(72) := g_package||' valid_date_return';
103 begin
104 hr_utility.set_location('Entering:'||l_proc, 5);
105 if (p_from_date is null) then
106 hr_utility.set_message(801, 'HR_7575_ALL_MAN_DATE_FIELD');
107 hr_utility.raise_error;
108 end if;
109 if (p_from_date > p_to_date ) then
110 hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
111 hr_utility.raise_error;
112 end if;
113 -- Compares if the date_from is 01/01/YYYY or not
114 if (trunc(p_from_date) <> trunc(to_date('01/01/' || to_char(trunc(p_from_date),'YYYY'),'DD/MM/YYYY' ))) then
115 hr_utility.set_message(801, 'PAY_6807_CALEND_INVALID_DATE');
116 hr_utility.raise_error;
117 end if;
118 -- Compares if the date_to is 31/12/YYYY or not
119 if (trunc(p_to_date) <> trunc(to_date('31/12/' || to_char(trunc(p_to_date),'YYYY'),'DD/MM/YYYY' ))) then
120 hr_utility.set_message(801, 'PAY_6807_CALEND_INVALID_DATE');
121 hr_utility.raise_error;
122 end if;
123 -- Compares if the calender year of date_From and date_to are same or not
124 if (to_number(to_char(p_from_date,'YYYY')) <>
125 to_number(to_char(p_to_date,'YYYY'))) then
126 hr_utility.set_message(801, 'PAY_6807_CALEND_INVALID_DATE');
127 hr_utility.raise_error;
128 end if;
129 hr_utility.set_location('Leaving:'||l_proc, 10);
130 end valid_date_return;
131 -- ----------------------------------------------------------------------------
132 -- |------< valid_employee_return >------|
133 -- ----------------------------------------------------------------------------
134 --
135 -- Description
136 -- This procedure is used to check if a particluar
137 -- person was a valid employee or not. Furing the calender year of
138 -- From Date given as input parameter.
139 --
140 -- Pre Conditions
141 -- None.
142 --
143 -- In Parameters
144 -- p_employee_id Employee Id of the person
145 -- p_from_Date From Date of the record being inserted or updated.
146 --
147 -- Post Success
148 -- Processing continues
149 --
150 -- Post Failure
151 -- Errors handled by the procedure
152 --
153 -- Access Status
154 -- Internal table handler use only.
155 --
156 Procedure valid_employee_return
157 (p_person_id IN NUMBER,
158 p_from_date IN DATE ,
159 p_business_group_id IN NUMBER) IS
160 l_proc VARCHAR2(72) := g_package||' valid_date_return';
161 l_count NUMBER;
162 begin
163 hr_utility.set_location('Entering:'||l_proc, 5);
164
165 -- following SQL checks if the person of type 'EMP' was valid or not during
166 -- a particular time or not
167
168 select count(*)
169 into l_count
170 from per_people_f ppf,
171 per_person_types ptype
172 where (to_number(to_char(p_from_date,'YYYY')) >=
173 to_number(to_char(ppf.effective_start_date,'YYYY')) and
174 to_number(to_char(p_from_date,'YYYY')) <=
175 to_number(to_char(ppf.effective_end_date,'YYYY')))
176 and ppf.person_type_id = ptype.person_type_id
177 and ppf.person_id = p_person_id
178 and ptype.system_person_type = 'EMP'
179 and ppf.business_group_id = p_business_group_id;
180 if (l_count = 0) then
181 hr_utility.set_message(801, 'HR_7149_BOOKINGS_FLAG_CHANGE');
182 hr_utility.set_message_token('EMP_OR_APL', 'employee');
183 hr_utility.raise_error;
184 end if;
185 hr_utility.set_location('Leaving:'||l_proc, 10);
186 end valid_employee_return;
187 --
188 -- ----------------------------------------------------------------------------
189 -- |-----------------------< valid_contr_type >----------------------|
190 -- ----------------------------------------------------------------------------
191 --
192 -- Description
193 -- This procedure is used to check that the lookup value for
194 -- Contribution Type is valid or not.
195 --
196 -- Pre Conditions
197 -- None.
198 --
199 -- In Parameters
200 -- emp_state_tax_rule_id PK of record being inserted or updated.
201 -- sit_optional_calc_ind Value of lookup code.
202 -- effective_date effective date
203 -- object_version_number Object version number of record being
204 -- inserted or updated.
205 --
206 -- Post Success
207 -- Processing continues
208 --
209 -- Post Failure
210 -- Error handled by procedure
211 --
212 -- Access Status
213 -- Internal table handler use only.
214 --
215 Procedure valid_contr_type
216 (p_lookup_type in varchar2,
217 p_lookup_code in varchar2,
218 p_effective_date in date
219 ) is
220 --
221 l_proc varchar2(72) := g_package||'valid_contr_type';
222 l_api_updating boolean;
223 --
224 Begin
225 --
226 hr_utility.set_location('Entering:'||l_proc, 5);
227 --
228 -- hr_api.mandatory_arg_error
229 -- (p_api_name => l_proc
230 -- ,p_argument => 'contr_type'
231 -- ,p_argument_value => p_effective_date
232 -- );
233 --
234 -- Validate only if attribute is not 'G' (457)
235 --
236 if (p_lookup_code = 'G') then
237 --
238 -- check if value of lookup falls within lookup type.
239 --
240 if hr_api.not_exists_in_hr_lookups
241 (p_lookup_type => p_lookup_type,
242 p_lookup_code => p_lookup_code,
243 p_effective_date => p_effective_date) then
244 --
245 -- raise error as does not exist as lookup
246 --
247 hr_utility.set_message(801,'HR_7209_API_LOOK_INVALID');
248 hr_utility.raise_error;
249 end if;
250 else
251 hr_utility.set_message(801,'HR_7209_API_LOOK_INVALID');
252 hr_utility.raise_error;
253 --
254 end if;
255 hr_utility.set_location('Leaving:'||l_proc,10);
256 --
257 end valid_contr_type;
258
259
260 --
261 -- ----------------------------------------------------------------------------
262 -- |---------------------------< insert_validate >----------------------------|
263 -- ----------------------------------------------------------------------------
264 Procedure insert_validate(p_rec in pay_con_shd.g_rec_type) is
265 --
266 l_proc varchar2(72) := g_package||'insert_validate';
267 --
268 Begin
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 -- Call all supporting business operations
272 --
273 chk_contr_history_id
274 (p_contr_history_id => p_rec.contr_history_id,
275 p_object_version_number => p_rec.object_version_number);
276 --
277 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
278 --
279 valid_date_return(
280 p_from_date => p_rec.date_from,
281 p_to_date => p_rec.date_to);
282
283
284 valid_employee_return(
285 p_person_id => p_rec.person_id ,
286 p_from_date => p_rec.date_from ,
287 p_business_group_id => p_rec.business_group_id);
288
289 valid_contr_type
290 (p_lookup_type => 'US_PRE_TAX_DEDUCTIONS',
291 p_lookup_code => p_rec.contr_type ,
292 p_effective_date => p_rec.date_to );
293
294
295 hr_utility.set_location(' Leaving:'||l_proc, 10);
296 End insert_validate;
297 --
298 -- ----------------------------------------------------------------------------
299 -- |---------------------------< update_validate >----------------------------|
300 -- ----------------------------------------------------------------------------
301 Procedure update_validate(p_rec in pay_con_shd.g_rec_type) is
302 --
303 l_proc varchar2(72) := g_package||'update_validate';
304 --
305 Begin
306 hr_utility.set_location('Entering:'||l_proc, 5);
307 --
308 -- Call all supporting business operations
309 --
310 chk_contr_history_id
311 (p_contr_history_id => p_rec.contr_history_id,
312 p_object_version_number => p_rec.object_version_number);
313 --
314 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
315 --
316 valid_date_return
317 (
318 p_from_date => p_rec.date_from,
319 p_to_date => p_rec.date_to
320 );
321 valid_employee_return
322 (
323 p_person_id => p_rec.person_id ,
324 p_from_date => p_rec.date_from ,
325 p_business_group_id => p_rec.business_group_id
326 );
327
328 hr_utility.set_location(' Leaving:'||l_proc, 10);
329 End update_validate;
330 --
331 -- ----------------------------------------------------------------------------
332 -- |---------------------------< delete_validate >----------------------------|
333 -- ----------------------------------------------------------------------------
334 Procedure delete_validate(p_rec in pay_con_shd.g_rec_type) is
335 --
336 l_proc varchar2(72) := g_package||'delete_validate';
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Call all supporting business operations
342 --
343 hr_utility.set_location(' Leaving:'||l_proc, 10);
344 End delete_validate;
345 --
346 --
347 -- ---------------------------------------------------------------------------
348 -- |---------------------< return_legislation_code >-------------------------|
349 -- ---------------------------------------------------------------------------
350 --
351 function return_legislation_code
352 (p_contr_history_id in number) return varchar2 is
353 --
354 -- Declare cursor
355 --
356 cursor csr_leg_code is
357 select a.legislation_code
358 from per_business_groups a,
359 pay_us_contribution_history b
360 where b.contr_history_id = p_contr_history_id
361 and a.business_group_id = b.business_group_id;
362 --
363 -- Declare local variables
364 --
365 l_legislation_code varchar2(150);
366 l_proc varchar2(72) := g_package||'return_legislation_code';
367 --
368 begin
369 --
373 --
370 hr_utility.set_location('Entering:'|| l_proc, 10);
371 --
372 -- Ensure that all the mandatory parameter are not null
374 hr_api.mandatory_arg_error(p_api_name => l_proc,
375 p_argument => 'contr_history_id',
376 p_argument_value => p_contr_history_id);
377 --
378 open csr_leg_code;
379 --
380 fetch csr_leg_code into l_legislation_code;
381 --
382 if csr_leg_code%notfound then
383 --
384 close csr_leg_code;
385 --
386 -- The primary key is invalid therefore we must error
387 --
388 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
389 hr_utility.raise_error;
390 --
391 end if;
392 --
393 close csr_leg_code;
394 --
395 hr_utility.set_location(' Leaving:'|| l_proc, 20);
396 --
397 return l_legislation_code;
398 --
399 end return_legislation_code;
400 --
401 end pay_con_bus;
[Home] [Help]