1 PACKAGE BODY PER_ES_ORG_INFO AS
2 /* $Header: peesorgp.pkb 120.1.12010000.2 2008/12/16 11:44:03 parusia ship $ */
3
4 -----------------------------------------------------------------------------------------
5 -- PROCEDURE create_es_org_info
6 -----------------------------------------------------------------------------------------
7
8 PROCEDURE create_es_org_info(p_org_info_type_code VARCHAR2
9 ,p_org_information1 VARCHAR2
10 ,p_org_information2 VARCHAR2
11 ,p_org_information3 VARCHAR2
12 ,p_org_information4 VARCHAR2
13 ,p_org_information5 VARCHAR2
14 ,p_org_information6 VARCHAR2
15 ,p_org_information7 VARCHAR2
16 ,p_org_information8 VARCHAR2
17 ,p_organization_id NUMBER
18 ,p_effective_date DATE
19 ) is
20 CURSOR get_business_group is
21 SELECT business_group_id
22 FROM hr_all_organization_units
23 WHERE organization_id=p_organization_id;
24 --
25 CURSOR csr_chk_province_code(c_prov_code VARCHAR2) IS
26 SELECT 'Y' FROM dual
27 WHERE EXISTS (SELECT /*+ ORDERED */ NULL from hr_lookups
28 WHERE LOOKUP_TYPE = 'ES_PROVINCE_CODES'
29 AND LOOKUP_CODE = c_prov_code);
30 --
31 CURSOR csr_chk_contribution_ac_type(c_business_group_id NUMBER
32 ,c_org_info1 VARCHAR2
33 ,c_org_info2 VARCHAR2
34 ,c_effective_date DATE) IS
35 SELECT 'Y'
36 FROM hr_organization_information hoi,hr_all_organization_units hou
37 WHERE hoi.org_information_context = 'ES_WORK_CENTER_DETAILS'
38 AND hou.organization_id = hoi.organization_id
39 AND org_information4 = c_org_info2
40 AND org_information1 <> c_org_info1
41 AND hou.business_group_id = c_business_group_id
42 AND c_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
43 AND EXISTS (SELECT 1 FROM hr_organization_information hoi1
44 WHERE hoi1.org_information1 = 'ES_WORK_CENTER'
45 AND hoi1.org_information_context = 'CLASS'
46 AND hoi1.organization_id = hoi.organization_id
47 AND hoi1.org_information2 = 'Y');
48 --
49 l_code VARCHAR2(2);
50 l_chk VARCHAR2(1);
51
52 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
53 --
54 BEGIN
55 --
56 -- Added for GSI Bug 5472781
57 --
58 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
59 --
60 OPEN get_business_group;
61 FETCH get_business_group into l_business_group_id;
62 CLOSE get_business_group;
63 --
64 IF p_org_info_type_code='ES_STATUTORY_INFO' THEN
65 hr_es_utility.validate_cif(p_org_information5);
66 hr_es_utility.unique_cif(null,p_org_information5,l_business_group_id,p_effective_date);
67 hr_es_utility.validate_cac(p_org_information8);
68 hr_es_utility.unique_cac(null,p_org_info_type_code,p_org_information8,l_business_group_id,p_effective_date);
69 END IF;
70 --
71 -- Bug 7508536
72 -- 1) Allow sharing of CAC numbers at the following levels -
73 -- a) Amongst Work Centres
74 -- b) Between Work Centre and Legal Employer.
75 IF p_org_info_type_code = 'ES_WORK_CENTER_DETAILS' THEN
76 hr_es_utility.validate_cac(p_org_information1);
77 -- hr_es_utility.unique_cac(null,p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
78 --
79 --
80 -- Bug 7508536
81 -- 2) Allow sharing of Contribution Account Types
82 /* l_chk := 'N';
83 OPEN csr_chk_contribution_ac_type(l_business_group_id,p_org_information1,p_org_information4,p_effective_date);
84 FETCH csr_chk_contribution_ac_type into l_chk;
85 CLOSE csr_chk_contribution_ac_type;
86 --
87 IF l_chk = 'Y' THEN
88 hr_utility.set_message(800, 'HR_ES_CAT_UNIQUE_ERROR');
89 hr_utility.raise_error;
90 END IF;
91 */
92 --
93 END IF;
94 --
95 -- Validation for Natural Disater dates
96 --
97 IF p_org_info_type_code = 'ES_WC_NATURAL_DISASTER' THEN
98 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
99 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
100 hr_utility.set_message(800, 'HR_ES_NAT_DIS_DATE_VALIDATION');
101 hr_utility.raise_error;
102 END IF;
103 END IF;
104 END IF;
105 --
106 -- Validation for Natural Disater dates
107 --
108 IF p_org_info_type_code = 'ES_WC_SHUTDOWN' THEN
109 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
110 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
111 hr_utility.set_message(800, 'HR_ES_SD_DATE_VALIDATION');
112 hr_utility.raise_error;
113 END IF;
114 END IF;
115 END IF;
116 --
117 -- Validation for Partial Unemployment dates
118 --
119 IF p_org_info_type_code = 'ES_WC_PARTIAL_UNEMPLOYMENT' THEN
120 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
121 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
122 hr_utility.set_message(800, 'HR_ES_PAR_UE_DATE_VALIDATION');
123 hr_utility.raise_error;
124 END IF;
125 END IF;
126 IF (p_org_information5 = 'GROSS_PAY' ) AND (p_org_information6 IS NULL) THEN
127 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
128 hr_utility.raise_error;
129 END IF;
130 END IF;
131 --
132 -- Validation for Contribution Exempt Situation dates
133 --
134 IF p_org_info_type_code='ES_CONTRIB_EXEMPT' THEN
135 IF (p_org_information2 IS NOT NULL) AND (p_org_information3 IS NOT NULL) THEN
136 IF fnd_date.canonical_to_date(p_org_information2) > fnd_date.canonical_to_date(p_org_information3) THEN
137 hr_utility.set_message(800, 'HR_ES_CON_EXMT_DATE_VALIDATION');
138 hr_utility.raise_error;
139 END IF;
140 END IF;
141 END IF;
142 --
143 -- Validation for Temporary Disability Management Deduction dates
144 --
145 IF p_org_info_type_code='ES_TEMP_DISABILITY_MGT' THEN
146 IF (p_org_information2 IS NOT NULL) AND (p_org_information3 IS NOT NULL) THEN
147 IF fnd_date.canonical_to_date(p_org_information2) > fnd_date.canonical_to_date(p_org_information3) THEN
148 hr_utility.set_message(800, 'HR_ES_TEMP_DIS_DATE_VALIDATION');
149 hr_utility.raise_error;
150 END IF;
151 END IF;
152 END IF;
153 --
154 IF p_org_info_type_code in('ES_WORK_CENTER_REF','ES_SECTION_REF') THEN
155 hr_es_utility.validate_wc_sec_ref(p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
156 END IF;
157
158 IF p_org_info_type_code in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS') then
159 hr_es_utility.unique_ss(null,p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
160 END IF;
161 --
162 -- Validation for Benefit Uplift Formulas -- Employer level.
163 --
164 IF p_org_info_type_code = 'ES_BENEFIT_UPLIFT' THEN
165 IF (p_org_information2 = 'GROSS_PAY' ) AND (p_org_information3 IS NULL) THEN
166 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
167 hr_utility.raise_error;
168 END IF;
169 IF (p_org_information5 IS NOT NULL) AND (p_org_information6 IS NOT NULL) THEN
170 IF fnd_date.canonical_to_date(p_org_information5) > fnd_date.canonical_to_date(p_org_information6) THEN
171 hr_utility.set_message(800, 'HR_ES_BU_DATE_VALIDATION');
172 hr_utility.raise_error;
173 END IF;
174 END IF;
175 END IF;
176 --
177 -- Validation for Tax Office Code and Tax Administration Code.
178 --
179 IF p_org_info_type_code = 'ES_TAX_OFFICE_DETAILS' OR
180 p_org_info_type_code = 'ES_TAX_ADMIN_DETAILS' THEN
181 l_code := substr(p_org_information1,1,2);
182 l_chk := 'N';
183 --
184 OPEN csr_chk_province_code(l_code);
185 FETCH csr_chk_province_code into l_chk;
186 CLOSE csr_chk_province_code;
187 --
188 IF l_chk <> 'Y' THEN
189 hr_utility.set_message(800, 'HR_ES_INVALID_TAX_CODE');
190 hr_utility.raise_error;
191 END IF;
192 END IF;
193 --
194 END IF;
195 --
196 END create_es_org_info;
197 -----------------------------------------------------------------------------------------
198 -- PROCEDURE update_es_org_info
199 -----------------------------------------------------------------------------------------
200
201 PROCEDURE update_es_org_info(p_org_info_type_code VARCHAR2
202 ,p_org_information1 VARCHAR2
203 ,p_org_information2 VARCHAR2
204 ,p_org_information3 VARCHAR2
205 ,p_org_information4 VARCHAR2
206 ,p_org_information5 VARCHAR2
207 ,p_org_information6 VARCHAR2
208 ,p_org_information7 VARCHAR2
209 ,p_org_information8 VARCHAR2
210 ,p_org_information_id NUMBER
211 ,p_effective_date DATE
212 ) IS
213 --
214 CURSOR get_business_group is
215 SELECT business_group_id
216 FROM hr_all_organization_units hou
217 ,hr_organization_information hoi
218 WHERE hoi.org_information_id = p_org_information_id
219 AND hoi.organization_id = hou.organization_id;
220 --
221 CURSOR csr_chk_province_code(c_prov_code VARCHAR2) IS
222 SELECT 'Y' FROM dual
223 WHERE EXISTS (SELECT /*+ ORDERED */ NULL from hr_lookups
224 WHERE LOOKUP_TYPE = 'ES_PROVINCE_CODES'
225 AND LOOKUP_CODE = c_prov_code);
226 --
227 CURSOR csr_chk_contribution_ac_type(c_business_group_id NUMBER
228 ,c_org_info1 VARCHAR2
229 ,c_org_info2 VARCHAR2
230 ,c_effective_date DATE) IS
231 SELECT 'Y'
232 FROM hr_organization_information hoi,hr_all_organization_units hou
233 WHERE hoi.org_information_context = 'ES_WORK_CENTER_DETAILS'
234 AND hou.organization_id = hoi.organization_id
235 AND org_information4 = c_org_info2
236 AND org_information1 <> c_org_info1
237 AND hou.business_group_id = c_business_group_id
238 AND c_effective_date <= nvl(hou.date_to,to_Date('4712/12/31','YYYY/MM/DD'))
239 AND EXISTS (SELECT 1 FROM hr_organization_information hoi1
240 WHERE hoi1.org_information1 = 'ES_WORK_CENTER'
241 AND hoi1.org_information_context = 'CLASS'
242 AND hoi1.organization_id = hoi.organization_id
243 AND hoi1.org_information2 = 'Y');
244 --
245 l_code VARCHAR2(2);
246 l_chk VARCHAR2(1);
250 --
247 l_business_group_id hr_all_organization_units.business_group_id%TYPE;
248 --
249 BEGIN
251 -- Added for GSI Bug 5472781
252 --
253 IF hr_utility.chk_product_install('Oracle Human Resources', 'ES') THEN
254 --
255 --
256 OPEN get_business_group;
257 FETCH get_business_group into l_business_group_id;
258 CLOSE get_business_group;
259 --
260 IF p_org_info_type_code = 'ES_STATUTORY_INFO' THEN
261 IF p_org_information5 IS NOT NULL THEN
262 hr_es_utility.validate_cif(p_org_information5);
263 hr_es_utility.unique_cif(p_org_information_id,p_org_information5,l_business_group_id,p_effective_date);
264 END IF;
265 IF p_org_information8 IS NOT NULL THEN
266 hr_es_utility.validate_cac(p_org_information8);
267 hr_es_utility.unique_cac(p_org_information_id,p_org_info_type_code,p_org_information8,l_business_group_id,p_effective_date);
268 END IF;
269 END IF;
270 IF p_org_info_type_code = 'ES_WORK_CENTER_DETAILS' THEN
271 -- Bug 7508536
272 -- 1) Allow sharing of CAC numbers at the following levels -
273 -- a) Amongst Work Centres
274 -- b) Between Work Centre and Legal Employer.
275 IF p_org_information1 IS NOT NULL THEN
276 hr_es_utility.validate_cac(p_org_information1);
277 -- hr_es_utility.unique_cac(p_org_information_id,p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
278 END IF;
279 --
280 -- Bug 7508536
281 -- 2) Allow sharing of Contribution Account Types
282 /*l_chk := 'N';
283 OPEN csr_chk_contribution_ac_type(l_business_group_id,p_org_information1,p_org_information4,p_effective_date);
284 FETCH csr_chk_contribution_ac_type into l_chk;
285 CLOSE csr_chk_contribution_ac_type;
286 --
287 IF l_chk = 'Y' THEN
288 hr_utility.set_message(800, 'HR_ES_CAT_UNIQUE_ERROR');
289 hr_utility.raise_error;
290 END IF;
291 */
292 --
293 END IF;
294 --
295 -- Validation for Natural Disater dates
296 --
297 IF p_org_info_type_code = 'ES_WC_NATURAL_DISASTER' THEN
298 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
299 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
300 hr_utility.set_message(800, 'HR_ES_NAT_DIS_DATE_VALIDATION');
301 hr_utility.raise_error;
302 END IF;
303 END IF;
304 END IF;
305 --
306 -- Validation for Natural Disater dates
307 --
308 IF p_org_info_type_code = 'ES_WC_SHUTDOWN' THEN
309 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
310 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
311 hr_utility.set_message(800, 'HR_ES_SD_DATE_VALIDATION');
312 hr_utility.raise_error;
313 END IF;
314 END IF;
315 END IF;
316 --
317 -- Validation for Partial Unemployment dates
318 --
319 IF p_org_info_type_code = 'ES_WC_PARTIAL_UNEMPLOYMENT' THEN
320 IF (p_org_information1 IS NOT NULL) AND (p_org_information2 IS NOT NULL) THEN
321 IF fnd_date.canonical_to_date(p_org_information1) > fnd_date.canonical_to_date(p_org_information2) THEN
322 hr_utility.set_message(800, 'HR_ES_PAR_UE_DATE_VALIDATION');
323 hr_utility.raise_error;
324 END IF;
325 END IF;
326 IF (p_org_information5 = 'GROSS_PAY' ) AND (p_org_information6 IS NULL) THEN
327 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
328 hr_utility.raise_error;
329 END IF;
330 END IF;
331 --
332 -- Validation for Contribution Exempt Situation dates
333 --
334 IF p_org_info_type_code = 'ES_CONTRIB_EXEMPT' THEN
335 IF (p_org_information2 IS NOT NULL) AND (p_org_information3 IS NOT NULL) THEN
336 IF fnd_date.canonical_to_date(p_org_information2) > fnd_date.canonical_to_date(p_org_information3) THEN
337 hr_utility.set_message(800, 'HR_ES_CON_EXMT_DATE_VALIDATION');
338 hr_utility.raise_error;
339 END IF;
340 END IF;
341 END IF;
342 --
343 -- Validation for Temporary Disability Management Deduction dates
344 --
345 IF p_org_info_type_code='ES_TEMP_DISABILITY_MGT' THEN
346 IF (p_org_information2 IS NOT NULL) AND (p_org_information3 IS NOT NULL) THEN
347 IF fnd_date.canonical_to_date(p_org_information2) > fnd_date.canonical_to_date(p_org_information3) THEN
348 hr_utility.set_message(800, 'HR_ES_TEMP_DIS_DATE_VALIDATION');
349 hr_utility.raise_error;
350 END IF;
351 END IF;
352 END IF;
353 --
354 IF p_org_info_type_code in('ES_WORK_CENTER_REF','ES_SECTION_REF') THEN
355 hr_es_utility.validate_wc_sec_ref(p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
356 END IF;
357 --
358 IF p_org_info_type_code in('ES_SS_PROVINCE_DETAILS','ES_SS_OFFICE_DETAILS') then
359 hr_es_utility.unique_ss(p_org_information_id,p_org_info_type_code,p_org_information1,l_business_group_id,p_effective_date);
360 END IF;
361 --
362 -- Validation for Benefit Uplift Formulas -- Employer level.
363 --
364 IF p_org_info_type_code = 'ES_BENEFIT_UPLIFT' THEN
365 IF (p_org_information2 = 'GROSS_PAY' ) AND (p_org_information3 IS NULL) THEN
366 hr_utility.set_message(800, 'HR_ES_BU_RATE_FORMULA_MISSING');
367 hr_utility.raise_error;
368 END IF;
369 IF (p_org_information5 IS NOT NULL) AND (p_org_information6 IS NOT NULL) THEN
370 IF fnd_date.canonical_to_date(p_org_information5) > fnd_date.canonical_to_date(p_org_information6) THEN
371 hr_utility.set_message(800, 'HR_ES_BU_DATE_VALIDATION');
372 hr_utility.raise_error;
373 END IF;
374 END IF;
375 END IF;
376 --
377 -- Validation for Tax Office Code and Tax Administration Code.
378 --
379 IF p_org_info_type_code = 'ES_TAX_OFFICE_DETAILS' OR
380 p_org_info_type_code = 'ES_TAX_ADMIN_DETAILS' THEN
381 l_code := substr(p_org_information1,1,2);
382 l_chk := 'N';
383 --
384 OPEN csr_chk_province_code(l_code);
385 FETCH csr_chk_province_code into l_chk;
386 CLOSE csr_chk_province_code;
387 --
388 IF l_chk <> 'Y' THEN
389 hr_utility.set_message(800, 'HR_ES_INVALID_TAX_CODE');
390 hr_utility.raise_error;
391 END IF;
392 END IF;
393 --
394 END IF;
395 --
396 END update_es_org_info;
397
398 END per_es_org_info;
[Home] [Help]