1 Package body hr_sg_aei_leg_hook as
2 /* $Header: pesglhae.pkb 120.5 2011/03/24 02:18:02 jalin ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := 'hr_sg_aei_leg_hook.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |-----------------------< chk_ir8s_c_valid>-------------------------|
13 -- ----------------------------------------------------------------------------
14 -- Description:
15 -- Verify that the segment values are valid for certain conditions
16 -- Added for SG Payroll specific situations.
17 --
18 -- Pre Conditions:
19 --
20 --
21 -- In Parameters:
22 -- assignment_id, information_type, aei_information1, aei_information2,
23 -- aei_information3, aei_information4, aei_information5, aei_information6,
24 -- aei_information7, aei_information8, aei_information9, aei_information10
25 -- aei_information11
26 --
27 -- Post Success:
28 -- Processing continues.
29 --
30 -- Post Failure:
31 -- An application error will be raised and processing is terminated.
32 --
33 -- Access Status:
34 -- Internal Table Handler Use Only.
35 -- ----------------------------------------------------------------------------
36 Procedure chk_ir8s_c_valid ( p_assignment_id number,
37 p_information_type varchar2,
38 p_aei_information1 varchar2,
39 p_aei_information2 varchar2,
40 p_aei_information3 varchar2,
41 p_aei_information4 varchar2,
42 p_aei_information5 varchar2,
43 p_aei_information6 varchar2,
44 p_aei_information7 varchar2,
45 p_aei_information8 varchar2,
46 p_aei_information9 varchar2,
47 p_aei_information10 varchar2,
48 p_aei_information11 varchar2) is
49 --
50 l_proc varchar2(100) := g_package||'chk_ir8s_c_valid';
51 l_invalid_record varchar(1) NULL;
52 --
53 CURSOR ir8s_c_invalid_records
54 (p_assignment_id number,
55 p_information_type varchar2,
56 p_aei_information1 varchar2) is
57 SELECT 'X'
58 FROM per_assignment_extra_info
59 WHERE assignment_id = p_assignment_id
60 AND information_type = p_information_type
61 AND aei_information1 = p_aei_information1
62 HAVING count(*) > 3;
63
64 --
65 Begin
66 --
67 hr_utility.set_location('Entering:'||l_proc, 10);
68 --
69 if p_information_type = 'HR_CPF_CLAIMED_SG' then
70
71 -- check if it has more than 3 claim details entered per basic year
72 open ir8s_c_invalid_records
73 (p_assignment_id,
74 p_information_type,
75 p_aei_information1);
76 fetch ir8s_c_invalid_records into l_invalid_record;
77 if ir8s_c_invalid_records%found then
78 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_RECORDS_INVALID');
79 fnd_message.raise_error;
80
81 hr_utility.set_location(l_proc, 20);
82
83 end if;
84 close ir8s_c_invalid_records;
85
86
87 -- Check if any the following has been entered, Additional Wages, Payment
88 -- for additional wages from date, Payment for additional wages to date
89 -- and pay date for additional wages
90
91 if ((nvl(p_aei_information2, 0) <> 0) and /* Bug 7415444 */
92 (p_aei_information3 is null or
93 p_aei_information4 is null or
94 p_aei_information5 is null)) or
95 ((p_aei_information3 is not null) and
96 (nvl(p_aei_information2, 0) = 0 or
97 p_aei_information4 is null or
98 p_aei_information5 is null)) or
99 ((p_aei_information4 is not null) and
100 (nvl(p_aei_information2, 0) = 0 or
101 p_aei_information3 is null or
102 p_aei_information5 is null)) or
103 ((p_aei_information5 is not null) and
104 (nvl(p_aei_information2, 0) = 0 or
105 p_aei_information3 is null or
106 p_aei_information4 is null)) then
107
108 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_ADD_WAGES_INVALID');
109 fnd_message.raise_error;
110
111 hr_utility.set_location(l_proc, 30);
112
113 -- check if Period To Date of Additional Wages is greater then
114 -- Period From Date of Additional Wages
115 -- Modified the condition for Bug# 3249303
116
117 elsif fnd_date.canonical_to_date(p_aei_information4) <= fnd_date.canonical_to_date(p_aei_information3) then
118 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID2');
119 fnd_message.set_token('DATE1', 'Period To Date of Additional Wages');
120 fnd_message.set_token('DATE2', 'Period From Date of Additional Wages');
121 fnd_message.raise_error;
122
123 hr_utility.set_location(l_proc, 40);
124
125 -- Check Pay date for additional wages must be greater or equal to
126 -- Payment for additional wages to date
127
128 elsif fnd_date.canonical_to_date(p_aei_information5) < fnd_date.canonical_to_date(p_aei_information4) then
129 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID');
130 fnd_message.set_token('DATE1', 'Pay Date of Additional Wages');
131 fnd_message.set_token('DATE2', 'Period To Date of Additional Wages');
132 fnd_message.raise_error;
133
134 hr_utility.set_location(l_proc, 50);
135
136 -- Check if Date of refund on employer CPF contribution is entered then
137 -- ensure Pay date for additional wages must be entered
138
139 elsif p_aei_information8 is not null and
140 p_aei_information5 is null then
141 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
142 fnd_message.set_token('FIELD1', 'Date of Refund of Employer Amount');
143 fnd_message.set_token('FIELD2', 'Pay Date of Additional Wages');
144 fnd_message.raise_error;
145
146 hr_utility.set_location(l_proc, 60);
147
148 -- Check if ER CPF contribution on amount of refund is entered then ensure
149 -- Date of refund on ER CPF contribution on amount of refund must be entered
150 elsif nvl(p_aei_information6, 0) <> 0 and p_aei_information8 is null then
151 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
152 fnd_message.set_token('FIELD1', 'Amount of Refund of Employer Contribution');
153 fnd_message.set_token('FIELD2', 'Date of Refund of Employer Amount');
154 fnd_message.raise_error;
155
156 hr_utility.set_location(l_proc, 70);
157
158 -- Check if Interset paid on ER CPF contribution on amount of refund
159 -- is entered then ensure Date of refund on ER CPF contribution on amount
160 -- of refund must be entered
161
162 elsif nvl(p_aei_information7, 0) <> 0 and p_aei_information8 is null then
163 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
164 fnd_message.set_token('FIELD1', 'Interest on Refund of Employer Contribution');
165 fnd_message.set_token('FIELD2', 'Date of Refund of Employer Amount');
166 fnd_message.raise_error;
167
168 hr_utility.set_location(l_proc, 80);
169
170 -- Check if Date of refund on ER CPF contribution on amount of refund is
171 -- entered, then ensure ER CPF contribution on amount of refund or
172 -- Interest paid on ER CPF contribution on amount of refund must be entered
173
174 elsif ((p_aei_information8 is not null) and
175 (nvl(p_aei_information6, 0) = 0 and nvl(p_aei_information7, 0) =0)) then
176 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_CPF_INVALID');
177 fnd_message.set_token('FIELD1', 'Amount of Refund of Employer Contribution');
178 fnd_message.set_token('FIELD2', 'Interest on Refund of Employer Contribution');
179 fnd_message.set_token('FIELD3', 'Date of Refund of Employer Amount');
180 fnd_message.raise_error;
181
182 hr_utility.set_location(l_proc, 90);
183
184 -- Check date of refund on ER CPF Contribution on amount of refund must
185 -- be greater or equal to Pay date for additional wages
186
187 elsif fnd_date.canonical_to_date(p_aei_information8) < fnd_date.canonical_to_date(p_aei_information5) then
188 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID');
189 fnd_message.set_token('DATE1', 'Date of Refund of Employer Amount');
190 fnd_message.set_token('DATE2', 'Pay Date of Additional Wages');
191 fnd_message.raise_error;
192
193 hr_utility.set_location(l_proc, 100);
194
195 -- Check if Date of refund on employee CPF contribution is entered then
196 -- ensure Pay date for additional wages must be entered
197
198 elsif p_aei_information11 is not null and
199 p_aei_information5 is null then
200 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
201 fnd_message.set_token('FIELD1', 'Date of Refund of Employee Amount');
202 fnd_message.set_token('FIELD2', 'Pay Date of Additional Wages');
203 fnd_message.raise_error;
204
205 hr_utility.set_location(l_proc, 110);
206
207 -- Check if ER CPF contribution on amount of refund is entered then ensure
208 -- Date of refund on ER CPF contribution on amount of refund must be entered
209
210 elsif nvl(p_aei_information9, 0) <> 0 and p_aei_information11 is null then
211 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
212 fnd_message.set_token('FIELD1', 'Amount of Refund of Employee Contribution');
213 fnd_message.set_token('FIELD2', 'Date of Refund of Employee Amount');
214 fnd_message.raise_error;
215
216 hr_utility.set_location(l_proc, 120);
217
218 -- Check if Interset paid on EE CPF contribution on amount of refund
219 -- is entered then ensure Date of refund on EE CPF contribution on amount
220 -- of refund must be entered
221
222 elsif nvl(p_aei_information10, 0) <> 0 and p_aei_information11 is null then
223 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID1');
224 fnd_message.set_token('FIELD1', 'Interest on Refund of Employee Contribution');
225 fnd_message.set_token('FIELD2', 'Date of Refund of Employee Amount');
226 fnd_message.raise_error;
227
228 hr_utility.set_location(l_proc, 130);
229
230 -- Check if Date of refund on EE CPF contribution on amount of refund is
231 -- entered, then ensure EE CPF contribution on amount of refund or
232 -- Interest paid on EE CPF contribution on amount of refund must be entered
233
234 elsif ((p_aei_information11 is not null) and
235 (nvl(p_aei_information9, 0) = 0 and nvl(p_aei_information10, 0) = 0)) then
236 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_CPF_INVALID');
237 fnd_message.set_token('FIELD1', 'Amount of Refund of Employee Contribution');
238 fnd_message.set_token('FIELD2', 'Interest on Refund of Employee Contribution');
239 fnd_message.set_token('FIELD3', 'Date of Refund of Employee Amount');
240 fnd_message.raise_error;
241
242 hr_utility.set_location(l_proc, 140);
243
244 -- Check date of refund on EE CPF Contribution on amount of refund must
245 -- be greater or equal to Pay date for additional wages
246
247 elsif fnd_date.canonical_to_date(p_aei_information11) < fnd_date.canonical_to_date(p_aei_information5) then
248 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID');
249 fnd_message.set_token('DATE1', 'Date of Refund of Employee Amount');
250 fnd_message.set_token('DATE2', 'Pay Date of Additional Wages');
251 fnd_message.raise_error;
252
253 hr_utility.set_location(l_proc, 150);
254
255 -- Check all dates are in basis year
256 -- Bug 5960714, this validation check should not include Date of refund
257 -- p_aei_information8 &p_aei_information11
258 elsif nvl(to_char(fnd_date.canonical_to_date(p_aei_information3), 'YYYY'),
259 p_aei_information1) <> p_aei_information1 or
260 nvl(to_char(fnd_date.canonical_to_date(p_aei_information4), 'YYYY'),
261 p_aei_information1) <> p_aei_information1 or
262 nvl(to_char(fnd_date.canonical_to_date(p_aei_information5), 'YYYY'),
263 p_aei_information1) <> p_aei_information1 then
264
265 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_SAME_YEAR');
266 fnd_message.raise_error;
267
268 hr_utility.set_location(l_proc, 160);
269
270 end if;
271 end if;
272 --
273 hr_utility.set_location(' Leaving:'||l_proc, 170);
274 --
275 End chk_ir8s_c_valid;
276 --
277 --
278 -- ----------------------------------------------------------------------------
279 -- |-----------------------< chk_multi_csn_valid>-----------------------------|
280 -- ----------------------------------------------------------------------------
281 -- Description:
282 -- Verify that the segment values are valid for certain conditions
283 -- Added for SG Payroll specific situations.
284 --
285 -- Pre Conditions:
286 --
287 --
288 -- In Parameters:
289 -- assignment_id, information_type, aei_information1, aei_information2,
290 -- aei_information3
291 --
292 -- Post Success:
293 -- Processing continues.
294 --
295 -- Post Failure:
296 -- An application error will be raised and processing is terminated.
297 --
298 -- Access Status:
299 -- Internal Table Handler Use Only.
300 -- Bug 10634286, added
301 -- ----------------------------------------------------------------------------
302 Procedure chk_multi_csn_valid ( p_assignment_id number,
303 p_information_type varchar2,
304 p_aei_information1 varchar2,
305 p_aei_information2 varchar2,
306 p_aei_information3 varchar2
307 ) is
308 --
309 l_proc varchar2(100) := g_package||'chk_multi_csn_valid';
310 l_invalid_record varchar(1) NULL;
311 --
312 CURSOR multi_csn_dup_records
313 (p_assignment_id number,
314 p_information_type varchar2,
315 p_aei_information1 varchar2,
316 p_aei_information2 varchar2,
317 p_aei_information3 varchar2) is
318 SELECT 'X'
319 FROM per_assignment_extra_info
320 WHERE assignment_id = p_assignment_id
321 AND information_type = p_information_type
322 AND aei_information1 = p_aei_information1
323 AND ((fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) between fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00'))
324 OR fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00')) between fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00')))
325 OR (fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) between fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00'))
326 OR fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00')) between fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00'))))
327 HAVING count(*) > 1;
328
329 CURSOR multi_csn_dup_records_p
330 (p_assignment_id number,
331 p_information_type varchar2,
332 p_aei_information1 varchar2,
333 p_aei_information2 varchar2,
334 p_aei_information3 varchar2) is
335 SELECT 'X'
336 FROM per_assignment_extra_info
337 WHERE assignment_id = p_assignment_id
338 AND information_type = p_information_type
339 AND substr(aei_information1,11,3) = substr(p_aei_information1,11,3)
340 AND ((fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) between fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00'))
341 OR fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00')) between fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00')))
342 OR (fnd_date.canonical_to_date(NVL(aei_information2,'1900/01/01 00:00:00')) between fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00'))
346 --
343 OR fnd_date.canonical_to_date(NVL(aei_information3,'4712/12/31 00:00:00')) between fnd_date.canonical_to_date(NVL(p_aei_information2,'1900/01/01 00:00:00')) and fnd_date.canonical_to_date(NVL(p_aei_information3,'4712/12/31 00:00:00'))))
344 HAVING count(*) > 1;
345
347 Begin
348 --
349 hr_utility.set_location('Entering:'||l_proc, 10);
350 --
351 if p_information_type = 'HR_MULTI_CSN_SG' then
352
353 -- check if it has duplicate CSN entered
354 open multi_csn_dup_records
355 (p_assignment_id,
356 p_information_type,
357 p_aei_information1,
358 p_aei_information2,
359 p_aei_information3);
360 fetch multi_csn_dup_records into l_invalid_record;
361 if multi_csn_dup_records%found then
362 fnd_message.set_name('PER','PER_7901_SYS_DUPLICATE_RECORDS');
363 fnd_message.raise_error;
364
365 hr_utility.set_location(l_proc, 20);
366 close multi_csn_dup_records;
367 else
368
369 open multi_csn_dup_records_p
370 (p_assignment_id,
371 p_information_type,
372 p_aei_information1,
373 p_aei_information2,
374 p_aei_information3);
375 fetch multi_csn_dup_records_p into l_invalid_record;
376 if multi_csn_dup_records_p%found then
377 fnd_message.set_name('PAY','HR_78429_SG_CSN_SAME_PAY');
378 fnd_message.raise_error;
379
380 hr_utility.set_location(l_proc, 30);
381 end if;
382 close multi_csn_dup_records_p;
383 end if;
384 close multi_csn_dup_records;
385
386 if fnd_date.canonical_to_date(p_aei_information3) < fnd_date.canonical_to_date(p_aei_information2) then
387 fnd_message.set_name('PAY', 'HR_SG_IR8S_C_DATES_INVALID');
388 fnd_message.set_token('DATE1', 'End Date');
389 fnd_message.set_token('DATE2', 'Start Date');
390 fnd_message.raise_error;
391 end if;
392
393 end if;
394 --
395 hr_utility.set_location(' Leaving:'||l_proc, 40);
396 --
397 End chk_multi_csn_valid;
398 --
399 --
400 End hr_sg_aei_leg_hook;
[Home] [Help]