[Home] [Help]
PACKAGE BODY: APPS.HR_DE_GENERAL
Source
1 PACKAGE BODY HR_DE_GENERAL AS
2 /* $Header: pedegenr.pkb 115.17 2003/02/24 16:02:03 rmakhija noship $ */
3 --
4 FUNCTION get_three_digit_code(p_legislation_code in varchar2)
5 RETURN varchar2 is
6 cursor csr_three_digit_code (l_legislation_code in varchar2) is
7 select uci.value
8 from pay_user_column_instances_f uci, pay_user_rows_f ur,
9 pay_user_tables ut
10 where ut.user_table_name = 'HR_DE_COUNTRY_CODE'
11 and ur.row_low_range_or_name = l_legislation_code
12 and ur.user_table_id = ut.user_table_id
13 and ur.user_row_id = uci.user_row_id;
14
15 l_three_digit_code pay_user_column_instances_f.value%type;
16 BEGIN
17 open csr_three_digit_code(p_legislation_code);
18 fetch csr_three_digit_code into l_three_digit_code;
19 if csr_three_digit_code%found then
20 return (l_three_digit_code);
21 else
22 return null;
23 end if;
24 close csr_three_digit_code;
25 --
26 EXCEPTION
27 when no_data_found then
28 null;
29 END get_three_digit_code;
30 --
31 PROCEDURE get_social_insurance_globals(
32 p_business_group_id in number
33 ,p_effective_date in date
34 ,o_hlth_ins_contrib_insig_pct out nocopy number
35 ,o_pens_ins_contrib_insig_pct out nocopy number
36 ,o_spcl_care_ins_pct out nocopy number
37 ,o_pens_ins_pect out nocopy number
38 ,o_unemp_ins_pect out nocopy number
39 ,o_hlth_ins_mon_gross_contrib out nocopy number
40 ,o_pens_ins_mon_gross_contrib_w out nocopy number
41 ,o_pens_ins_mon_gross_contrib_e out nocopy number
42 ,o_minr_ins_mon_gross_contrib_w out nocopy number
43 ,o_minr_ins_mon_gross_contrib_e out nocopy number
44 ,o_hlth_ins_contrib_insigph_pct out nocopy number
45 ,o_pens_ins_contrib_insigph_pct out nocopy number
46 ,o_tax_contrib_insig_pct out nocopy number
47 ,o_tax_contrib_insigph_pct out nocopy number
48 ,o_pvt_hlth_ins_min_mon_gross out nocopy number
49 )
50 is
51
52 Begin
53
54 Begin
55 o_hlth_ins_contrib_insig_pct := hruserdt.get_table_value(
56 p_bus_group_id => p_business_group_id
57 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
58 ,p_col_name => 'Value'
59 ,p_row_value => 'HI_C_INSIG_P'
60 ,p_effective_date => p_effective_date);
61 Exception
62 When NO_DATA_FOUND THEN
63 o_hlth_ins_contrib_insig_pct := null;
64 End;
65
66 Begin
67
68 o_pens_ins_contrib_insig_pct := hruserdt.get_table_value(
69 p_bus_group_id => p_business_group_id
70 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
71 ,p_col_name => 'Value'
72 ,p_row_value => 'PI_C_INSIG_P'
73 ,p_effective_date => p_effective_date);
74 Exception
75 When NO_DATA_FOUND THEN
76 o_pens_ins_contrib_insig_pct := null;
77 End;
78
79
80 Begin
81 o_spcl_care_ins_pct := hruserdt.get_table_value(
82 p_bus_group_id => p_business_group_id
83 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
84 ,p_col_name => 'Value'
85 ,p_row_value => 'SCI_P'
86 ,p_effective_date => p_effective_date);
87 Exception
88 When NO_DATA_FOUND THEN
89 o_pens_ins_contrib_insig_pct := null;
90 End;
91
92
93
94 Begin
95 o_pens_ins_pect := hruserdt.get_table_value(
96 p_bus_group_id => p_business_group_id
97 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
98 ,p_col_name => 'Value'
99 ,p_row_value => 'PI_P'
100 ,p_effective_date => p_effective_date);
101 Exception
102 When NO_DATA_FOUND THEN
103 o_pens_ins_pect := null;
104 End;
105
106
107 Begin
108 o_unemp_ins_pect := hruserdt.get_table_value(
109 p_bus_group_id => p_business_group_id
110 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
111 ,p_col_name => 'Value'
112 ,p_row_value => 'UI_P'
113 ,p_effective_date => p_effective_date);
114 Exception
115 When NO_DATA_FOUND THEN
116 o_pens_ins_pect := null;
117 End;
118
119
120 Begin
121 o_hlth_ins_mon_gross_contrib := hruserdt.get_table_value(
122 p_bus_group_id => p_business_group_id
123 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
124 ,p_col_name => 'Value'
125 ,p_row_value => 'HI_MG_L'
126 ,p_effective_date => p_effective_date);
127
128 Exception
129 When NO_DATA_FOUND THEN
130 o_hlth_ins_mon_gross_contrib := null;
131 End;
132
133 Begin
134 o_pens_ins_mon_gross_contrib_w := hruserdt.get_table_value(
135 p_bus_group_id => p_business_group_id
136 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
137 ,p_col_name => 'Value'
138 ,p_row_value => 'PI_MG_L_W'
139 ,p_effective_date => p_effective_date);
140 Exception
141 When NO_DATA_FOUND THEN
142 o_hlth_ins_mon_gross_contrib := null;
143 End;
144
145
146 Begin
147 o_pens_ins_mon_gross_contrib_e := hruserdt.get_table_value(
148 p_bus_group_id => p_business_group_id
149 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
150 ,p_col_name => 'Value'
151 ,p_row_value => 'PI_MG_L_E'
152 ,p_effective_date => p_effective_date);
153 Exception
154 When NO_DATA_FOUND THEN
155 o_pens_ins_mon_gross_contrib_e := null;
156 End;
157
158 Begin
159 o_minr_ins_mon_gross_contrib_w := hruserdt.get_table_value(
160 p_bus_group_id => p_business_group_id
161 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
162 ,p_col_name => 'Value'
163 ,p_row_value => 'MI_MG_L_W'
164 ,p_effective_date => p_effective_date);
165 Exception
166 When NO_DATA_FOUND THEN
167 o_minr_ins_mon_gross_contrib_w := null;
168 End;
169
170
171 Begin
172 o_minr_ins_mon_gross_contrib_e := hruserdt.get_table_value(
173 p_bus_group_id => p_business_group_id
174 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
175 ,p_col_name => 'Value'
176 ,p_row_value => 'MI_MG_L_E'
177 ,p_effective_date => p_effective_date);
178
179 Exception
180 When NO_DATA_FOUND THEN
181 o_minr_ins_mon_gross_contrib_e := null;
182 End;
183 --
184
185 Begin
186 o_hlth_ins_contrib_insigph_pct := hruserdt.get_table_value(
187 p_bus_group_id => p_business_group_id
188 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
189 ,p_col_name => 'Value'
190 ,p_row_value => 'HI_C_INSIGPH_P'
191 ,p_effective_date => p_effective_date);
192 Exception
193 When NO_DATA_FOUND THEN
194 o_hlth_ins_contrib_insigph_pct := null;
195 End;
196
197 Begin
198 o_pens_ins_contrib_insigph_pct := hruserdt.get_table_value(
199 p_bus_group_id => p_business_group_id
200 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
201 ,p_col_name => 'Value'
202 ,p_row_value => 'PI_C_INSIGPH_P'
203 ,p_effective_date => p_effective_date);
204 Exception
205 When NO_DATA_FOUND THEN
206 o_pens_ins_contrib_insigph_pct := null;
207 End;
208
209 Begin
210 o_tax_contrib_insig_pct := hruserdt.get_table_value(
211 p_bus_group_id => p_business_group_id
212 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
213 ,p_col_name => 'Value'
214 ,p_row_value => 'TAX_C_INSIG_P'
215 ,p_effective_date => p_effective_date);
216 Exception
217 When NO_DATA_FOUND THEN
218 o_tax_contrib_insig_pct := null;
219 End;
220
221 Begin
222 o_tax_contrib_insigph_pct := hruserdt.get_table_value(
223 p_bus_group_id => p_business_group_id
224 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
225 ,p_col_name => 'Value'
226 ,p_row_value => 'TAX_C_INSIGPH_P'
227 ,p_effective_date => p_effective_date);
228 Exception
229 When NO_DATA_FOUND THEN
230 o_tax_contrib_insigph_pct := null;
231 End;
232
233 Begin
234 o_pvt_hlth_ins_min_mon_gross := hruserdt.get_table_value(
235 p_bus_group_id => p_business_group_id
236 ,p_table_name => 'HR_DE_SOC_INS_CONSTANTS'
237 ,p_col_name => 'Value'
238 ,p_row_value => 'PI_ELIG_MG_MIN'
239 ,p_effective_date => p_effective_date);
240 Exception
241 When NO_DATA_FOUND THEN
242 o_pvt_hlth_ins_min_mon_gross := null;
243 End;
244
245
246 Exception
247 WHEN OTHERS THEN
248 o_hlth_ins_contrib_insig_pct := null;
249 o_pens_ins_contrib_insig_pct := null;
250 o_spcl_care_ins_pct := null;
251 o_pens_ins_pect := null;
252 o_unemp_ins_pect := null;
253 o_hlth_ins_mon_gross_contrib := null;
254 o_pens_ins_mon_gross_contrib_w := null;
255 o_pens_ins_mon_gross_contrib_e := null;
256 o_minr_ins_mon_gross_contrib_w := null;
257 o_minr_ins_mon_gross_contrib_e := null;
258 o_hlth_ins_contrib_insigph_pct := null;
259 o_pens_ins_contrib_insigph_pct := null;
260 o_tax_contrib_insig_pct := null;
261 o_tax_contrib_insigph_pct := null;
262 o_pvt_hlth_ins_min_mon_gross := null;
263
264 End get_social_insurance_globals;
265
266 Function business_group_currency
267 (p_business_group_id in hr_organization_units.business_group_id%type)
268 return fnd_currencies.currency_code%type is
269
270 v_currency_code fnd_currencies.currency_code%type;
271
272 cursor currency_code
273 (c_business_group_id hr_organization_units.business_group_id%type) is
274 select fcu.currency_code
275 from hr_organization_information hoi,
276 hr_organization_units hou,
277 fnd_currencies fcu
278 where hou.business_group_id = c_business_group_id
279 and hou.organization_id = hoi.organization_id
280 and hoi.org_information_context = 'Business Group Information'
281 and fcu.issuing_territory_code = hoi.org_information9;
282
283 begin
284 open currency_code (p_business_group_id);
285 fetch currency_code into v_currency_code;
286 close currency_code;
287
288 return v_currency_code;
289 end business_group_currency;
290
291 --
292 --
293 function get_tax_office_details (p_organization_id in integer) return varchar2 is
294
295 tax_office_number varchar2(4);
296 tax_office_name varchar2(240);
297
298 BEGIN
299 -- no checks on date_from and to are required as we want to retieve the name of the tax office even
300 -- if it is no longer in use.
301
302 If p_organization_id is null THEN
303 return null;
304 END IF;
305
306 select tax_info.org_information1,
307 org.name
308 into tax_office_number,
309 tax_office_name
310 from hr_organization_information tax_info ,
311 hr_organization_units org
312 where tax_info.organization_id = org.organization_id
313 and tax_info.organization_id = p_organization_id
314 and TAX_INFO.org_information_context = 'DE_TAX_OFFICE_INFO';
315
316 Return tax_office_number || tax_office_name;
317
318 EXCEPTION WHEN NO_DATA_FOUND THEN
319 return null;
320 END;
321 --
322 --
323 -- Function to return a value from a user table i.e. a user column instance.
324 --
325 FUNCTION get_uci
326 (p_effective_date DATE
327 ,p_user_table_id NUMBER
328 ,p_user_row_id NUMBER
329 ,p_user_column_name VARCHAR2) RETURN VARCHAR2 IS
330 --
331 CURSOR c_uci_value
332 (p_effective_date DATE
333 ,p_user_table_id NUMBER
334 ,p_user_row_id NUMBER
335 ,p_user_column_name VARCHAR2) IS
336 SELECT value
337 FROM pay_user_column_instances_f uci
338 ,pay_user_columns uc
339 WHERE uc.user_table_id = p_user_table_id
340 AND uc.user_column_name = p_user_column_name
341 AND uci.user_row_id = p_user_row_id
342 AND uci.user_column_id = uc.user_column_id
343 AND p_effective_date BETWEEN uci.effective_start_date
344 AND uci.effective_end_date;
345 --
346 l_uci_value pay_user_column_instances_f.value%TYPE := NULL;
347 BEGIN
348 --
349 OPEN c_uci_value
350 (p_effective_date => p_effective_date
351 ,p_user_table_id => p_user_table_id
352 ,p_user_row_id => p_user_row_id
353 ,p_user_column_name => p_user_column_name);
354 FETCH c_uci_value INTO l_uci_value;
355 CLOSE c_uci_value;
356 --
357 RETURN l_uci_value;
358 END get_uci;
359
360 function get_org_name (p_org_id in number) return varchar2 is
361 p_org_name_out varchar2(240);
362 begin
363
364 select name into p_org_name_out
365 from hr_all_organization_units
366 where organization_id = p_org_id;
367
368 return p_org_name_out;
369
370 EXCEPTION
371 When no_data_found then return
372 null;
373
374 end get_org_name;
375
376 function max_tax_info_date (p_element_entry_id in varchar2) return date is
377 l_max_effective_start_date date;
378
379 begin
380
381 IF p_element_entry_id is null THEN
382 return null;
383 END IF;
384
385 select max(effective_start_date)
386 into l_max_effective_start_date
387 from pay_element_entries_f
388 where element_entry_id = p_element_entry_id;
389
390 return l_max_effective_start_date;
391
392 EXCEPTION WHEN NO_DATA_FOUND THEN
393 return null;
394
395 end max_tax_info_date;
396 --
397 FUNCTION get_end_reason_no(p_end_reason_id in number)
398 RETURN number is
399 CURSOR c1(p_end_reason_id in number) IS
400 SELECT end_reason_number FROM pqh_de_ins_end_reasons
401 WHERE ins_end_reason_id = p_end_reason_id;
402 l_end_reason_no PQH_DE_INS_END_REASONS.END_REASON_NUMBER%TYPE;
403 --
404 BEGIN
405 OPEN c1(p_end_reason_id);
406 FETCH c1 INTO l_end_reason_no ;
407 IF c1%FOUND THEN
408 return (l_end_reason_no);
409 else return(null);
410 End if;
411 Close c1;
412 --
413 END get_end_reason_no;
414 --
415 --
416 FUNCTION get_end_reason_desc(p_end_reason_id in number)
417 RETURN varchar2 is
418 CURSOR c1(p_end_reason_id in number) IS
419 SELECT end_reason_description FROM pqh_de_ins_end_reasons
420 WHERE ins_end_reason_id = p_end_reason_id;
421 l_end_reason_desc PQH_DE_INS_END_REASONS.END_REASON_DESCRIPTION%TYPE;
422 --
423 BEGIN
424 OPEN c1(p_end_reason_id);
425 FETCH c1 INTO l_end_reason_desc;
426 IF c1%FOUND then
427 return (l_end_reason_desc);
428 else return(null);
429 End IF;
430 Close c1;
431 --
432 END get_end_reason_desc;
433 --
434
435 END hr_de_general;