1 Package body hr_sg_aei_leg_hook as
2 /* $Header: pesglhae.pkb 120.0.12000000.2 2007/03/29 05:39:00 jalin noship $ */
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
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 End hr_sg_aei_leg_hook;
[Home] [Help]