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