1 PACKAGE BODY PER_IE_ORG_INFO AS
2 /* $Header: peieorgp.pkb 120.5 2008/02/14 13:59:35 knadhan noship $ */
3
4 --Procedure to validate that PAYE references defined at BG level are unique.
5 PROCEDURE validate_uniqueness(p_org_info_id NUMBER
6 ,p_business_group_id VARCHAR2
7 ,p_org_information2 VARCHAR2
8 ,p_effective_date DATE
9 ) is
10
11 CURSOR get_unique is
12 SELECT 'x', hoi.org_information2
13 FROM hr_organization_information hoi,
14 hr_all_organization_units hou
15 WHERE hoi.org_information_context ='IE_ORG_INFORMATION'
16 AND hou.organization_id = hoi.organization_id
17 AND hoi.org_information2 = p_org_information2
18 AND hou.business_group_id =p_business_group_id
19 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
20 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
21 AND exists (select 1 from hr_organization_information hoi1
22 where hoi1.org_information1 = 'HR_BG'
23 and hoi1.org_information_context = 'CLASS'
24 and hoi1.organization_id = hoi.organization_id
25 and hoi1.org_information2='Y');
26
27
28
29 l_check_flag varchar2(1);
33
30 l_paye_ref_no hr_organization_information.org_information2%type;
31
32 BEGIN
34 /* Added for GSI Bug 5472781 */
35 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
36
37 l_check_flag := null;
38
39 open get_unique;
40 fetch get_unique into l_check_flag, l_paye_ref_no;
41 close get_unique;
42
43 if l_check_flag = 'x' then
44 hr_utility.set_message(800,'HR_IE_PAYE_UNIQUE_ERROR');
45 hr_utility.raise_error;
46 end if;
47
48 END IF; /* Added for GSI Bug 5472781 */
49
50 END validate_uniqueness;
51
52 -- Changes for 4369280
53 --Procedure to validate that PAYE references defined at Legal Employer are unique in the BG.
54 PROCEDURE validate_employer(p_org_info_id NUMBER
55 ,p_business_group_id VARCHAR2
56 ,p_org_information2 VARCHAR2
57 ,p_effective_date DATE
58 ) is
59
60 CURSOR get_unique is
61 SELECT 'x', hoi.org_information2
62 FROM hr_organization_information hoi,
63 hr_all_organization_units hou
64 WHERE hoi.org_information_context ='IE_EMPLOYER_INFO'
65 AND hou.organization_id = hoi.organization_id
66 AND hoi.org_information2 = p_org_information2
67 AND hou.business_group_id =p_business_group_id
68 AND (p_org_info_id IS NULL OR hoi.org_information_id <> p_org_info_id)
69 AND p_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
70 AND exists (select 1 from hr_organization_information hoi1
71 where hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
72 and hoi1.org_information_context = 'CLASS'
73 and hoi1.organization_id = hoi.organization_id
74 and hoi1.org_information2='Y');
75
76
77
78 l_check_flag varchar2(1);
79 l_paye_ref_no hr_organization_information.org_information2%type;
80
81 BEGIN
82
83 /* Added for GSI Bug 5472781 */
84 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
85
86 l_check_flag := null;
87
88 open get_unique;
89 fetch get_unique into l_check_flag, l_paye_ref_no;
90 close get_unique;
91
92 if l_check_flag = 'x' then
93 hr_utility.set_message(800,'HR_IE_PAYE_UNIQUE_ERROR');
94 hr_utility.raise_error;
95 end if;
96
97 END IF; /* Added for GSI Bug 5472781 */
98
99 END validate_employer;
100
101 -- New Procedure Added to validate the CBR Number for IE EHECS Report.
102 PROCEDURE PROC_CBR_NO(p_cbr_no in varchar2) is
103 l_total number(25):=0;
104 l_original_total number(25):=0;
105 l_count number(10):=8;
106 l_check_digit VARCHAR2(10);
107 l_trace number(2):=1;
108 l_flag boolean:=TRUE;
109 l_last_digit char(1);
110 cursor c_cbr_no is select cbr from (select substr(p_cbr_no ,level-0,1) cbr
111 from dual
112 connect by level<length(p_cbr_no));
113 l_cbr_no c_cbr_no%rowtype;
114
115 begin
116 if length(p_cbr_no)<>10 then
117 hr_utility.set_message(800,'HR_IE_CBR_VALIDATION');
118 hr_utility.raise_error;
119 end if;
120
121 OPEN c_cbr_no;
122 FETCH c_cbr_no INTO l_cbr_no;
123 LOOP
124 EXIT WHEN c_cbr_no%NOTFOUND;
125
126 if(l_trace=1 and l_cbr_no.cbr<>'E') then
127 l_flag:=false;
128 hr_utility.set_message(800,'HR_IE_CBR_VALIDATION');
129 hr_utility.raise_error;
130 end if;
131
132 if(l_trace=2 and l_cbr_no.cbr<>'N') then
133 l_flag:=false;
134 hr_utility.set_message(800,'HR_IE_CBR_VALIDATION');
135 hr_utility.raise_error;
136 end if;
137
138 IF(l_trace>=3) then
139 if(l_cbr_no.cbr in ('1','2','3','4','5','6','7','8','9','0')) then
140 l_original_total:=l_original_total+to_number(l_cbr_no.cbr)*l_count;
141 l_count:=l_count-1;
142 ELSE
143 hr_utility.set_message(800,'HR_IE_CBR_VALIDATION');
144 hr_utility.raise_error;
145 end if;
146 end if;
147 l_trace:=l_trace+1;
148 FETCH c_cbr_no INTO l_cbr_no;
149 end loop;
150
151 l_total:=(trunc(l_original_total/11))*11-l_original_total;
152
153 l_check_digit:=11-abs(l_total);
154 if(l_check_digit='10') then
155 l_check_digit:=0;
156 elsif(l_check_digit='11') then
157 l_check_digit:='-';
158 end if;
159 l_last_digit:=substr(p_cbr_no,length(p_cbr_no ),1);
160
161 if(l_last_digit<>l_check_digit) then
162 hr_utility.set_message(800,'HR_IE_CBR_VALIDATION');
163 hr_utility.raise_error;
164 end if;
165
166 end proc_cbr_no;
167 --
168
169
170 PROCEDURE CREATE_IE_ORG_INFO(p_org_info_type_code VARCHAR2
171 ,p_org_information2 VARCHAR2
172 ,p_org_information3 VARCHAR2
173 ,p_organization_id NUMBER
174 ,p_effective_date DATE
175 ) is
176
177 CURSOR get_business_group is
178 SELECT business_group_id
179 from hr_all_organization_units
180 where organization_id=p_organization_id;
181
182 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
183 BEGIN
184
185 /* Added for GSI Bug 5472781 */
189 FETCH get_business_group into l_business_group_id;
186 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
187
188 OPEN get_business_group;
190 CLOSE get_business_group;
191
192
193 IF p_org_info_type_code = 'IE_ORG_INFORMATION' THEN
194 IF p_org_information2 is not null THEN
195 validate_uniqueness(null,l_business_group_id,p_org_information2,p_effective_date);
196 END IF;
197 ELSIF p_org_info_type_code = 'IE_EMPLOYER_INFO' THEN
198 IF p_org_information2 is not null THEN
199 validate_employer(null,l_business_group_id,p_org_information2,p_effective_date);
200 END IF;
201 ELSIF p_org_info_type_code = 'IE_EHECS' THEN
202 IF p_org_information3 is not null THEN
203 proc_cbr_no(p_org_information3);
204 END IF;
205 END IF;
206
207 END IF; /* Added for GSI Bug 5472781 */
208
209 END CREATE_IE_ORG_INFO;
210
211
212 PROCEDURE UPDATE_IE_ORG_INFO(p_org_info_type_code VARCHAR2
213 ,p_org_information2 VARCHAR2
214 ,p_org_information3 VARCHAR2
215 ,p_org_information_id NUMBER
216 ,p_effective_date DATE
217 ) is
218
219 CURSOR get_business_group is
220 SELECT business_group_id
221 from hr_all_organization_units hou,hr_organization_information hoi
222 where hoi.org_information_id=p_org_information_id
223 and hoi.organization_id=hou.organization_id;
224
225 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
226 BEGIN
227
228 /* Added for GSI Bug 5472781 */
229 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
230
231 OPEN get_business_group;
232 FETCH get_business_group into l_business_group_id;
233 CLOSE get_business_group;
234
235 IF p_org_info_type_code = 'IE_ORG_INFORMATION' THEN
236 -- Changed to check whether org_information2 is updated 4369280
237 IF (p_org_information2 is not null and p_org_information2 <> hr_api.g_varchar2) THEN
238 validate_uniqueness(p_org_information_id,l_business_group_id,p_org_information2,p_effective_date);
239 END IF;
240 ELSIF p_org_info_type_code = 'IE_EMPLOYER_INFO' THEN
241 IF (p_org_information2 <> hr_api.g_varchar2) THEN
242 validate_employer(p_org_information_id,l_business_group_id,p_org_information2,p_effective_date);
243 END IF;
244 ELSIF p_org_info_type_code = 'IE_EHECS' THEN
245 IF (p_org_information3 <> hr_api.g_varchar2) THEN
246 proc_cbr_no(p_org_information3);
247 END IF;
248 END IF;
249
250 END IF; /* Added for GSI Bug 5472781 */
251
252 END UPDATE_IE_ORG_INFO;
253
254
255 PROCEDURE CREATE_IE_ASG_INFO(P_PERSON_ID NUMBER
256 ,P_PAYROLL_ID NUMBER
257 ,p_organization_id NUMBER
258 ,P_EFFECTIVE_DATE DATE)
259
260 is
261 BEGIN
262 NULL;
263
264 END CREATE_IE_ASG_INFO;
265
266 PROCEDURE UPDATE_IE_ASG_INFO(P_ASSIGNMENT_ID NUMBER
267 ,P_PAYROLL_ID NUMBER
268 ,p_organization_id NUMBER
269 ,P_EFFECTIVE_DATE DATE)
270
271 is
272 CURSOR get_business_group is
273 SELECT business_group_id
274 from hr_all_organization_units
275 where organization_id=p_organization_id;
276
277 cursor csr_chk_er_change(p_payroll_id number,
278 p_segment4 varchar2,
279 p_assignment_id number,
280 p_effective_date date) IS
281
282 select 1
283 from dual
284 where exists(select NULL
285 from per_all_assignments_f paa,
286 pay_all_payrolls_f pap,
287 hr_soft_coding_keyflex scl
288 where paa.payroll_id = pap.payroll_id
289 and p_effective_date not between paa.effective_start_date and paa.effective_end_date
290 and paa.assignment_id = p_assignment_id
291 and pap.payroll_id <> p_payroll_id
292 and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
293 and scl.segment4 <> p_segment4
294 and paa.effective_start_date <= pap.effective_end_date
295 and paa.effective_end_date >= pap.effective_start_date
296 );
297
298 cursor csr_get_paye_ref(p_payroll_id number,
299 p_business_group_id number,
300 p_effective_date date) is
301 select sck.segment4
302 from hr_soft_coding_keyflex sck
303 ,pay_all_payrolls_f pay
304 where pay.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
305 and pay.payroll_id = p_payroll_id
306 and P_EFFECTIVE_DATE between pay.effective_start_date and pay.effective_end_date
307 and pay.business_group_id=p_business_group_id;
308
309 l_old_paye_ref hr_soft_coding_keyflex.segment4%TYPE;
310 l_new_paye_ref hr_soft_coding_keyflex.segment4%TYPE;
311 l_prim_payroll_id per_all_assignments_f.payroll_id%TYPE;
312 l_new_payroll_id per_all_assignments_f.payroll_id%TYPE;
313 l_person_id per_all_people_f.person_id%type;
314 l_assignment_id per_all_assignments_f.assignment_id%type;
315 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
316 l_er_exists number;
317
318 BEGIN
319
320 /* Added for GSI Bug 5472781 */
321 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
322
323 l_er_exists := 0;
324 OPEN get_business_group;
325 FETCH get_business_group into l_business_group_id;
326 CLOSE get_business_group;
327
328 l_assignment_id := p_assignment_id;
329 l_new_payroll_id := P_PAYROLL_ID;
330
331
332 open csr_get_paye_ref(l_new_payroll_id,l_business_group_id,p_effective_date);
333 fetch csr_get_paye_ref into l_new_paye_ref;
334 close csr_get_paye_ref;
335
336 OPEN csr_chk_er_change(l_new_payroll_id,l_new_paye_ref,l_assignment_id,p_effective_date);
337 FETCH csr_chk_er_change into l_er_exists;
338 CLOSE csr_chk_er_change;
339
340 IF(l_er_exists = 1) THEN
341
342 hr_utility.set_message(800,'HR_IE_ASG_PAYE_DIFF_ERROR');
343 hr_utility.raise_error;
344 END IF;
345
346 END IF; /* Added for GSI Bug 5472781 */
347
348 END UPDATE_IE_ASG_INFO;
349
350 -------------------------
351
352 END PER_IE_ORG_INFO;
[Home] [Help]