[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_ARCHIVE_RSEA
Source
1 PACKAGE BODY PAY_NO_ARCHIVE_RSEA AS
2 /* $Header: pynorsea.pkb 120.1 2010/10/27 07:23:34 vijranga ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_package VARCHAR2(33) := ' PAY_NO_ARCHIVE_RSEA.';
6 g_err_num NUMBER;
7 g_errm VARCHAR2(150);
8
9 -- Function to get defined balance id
10
11 FUNCTION get_defined_balance_id
12 (p_balance_name IN VARCHAR2
13 ,p_dbi_suffix IN VARCHAR2 ) RETURN NUMBER IS
14
15 l_defined_balance_id NUMBER;
16
17 BEGIN
18
19 SELECT pdb.defined_balance_id
20 INTO l_defined_balance_id
21 FROM pay_defined_balances pdb
22 ,pay_balance_types pbt
23 ,pay_balance_dimensions pbd
24 WHERE pbd.database_item_suffix = p_dbi_suffix
25 AND pbd.legislation_code = 'NO'
26 AND pbt.balance_name = p_balance_name
27 AND pbt.legislation_code = 'NO'
28 AND pdb.balance_type_id = pbt.balance_type_id
29 AND pdb.balance_dimension_id = pbd.balance_dimension_id
30 AND pdb.legislation_code = 'NO';
31
32 l_defined_balance_id := NVL(l_defined_balance_id,0);
33
34 RETURN l_defined_balance_id ;
35 END get_defined_balance_id ;
36
37
38
39 /* GET PARAMETER */
40 FUNCTION GET_PARAMETER(
41 p_parameter_string IN VARCHAR2
42 ,p_token IN VARCHAR2
43 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
44 IS
45 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
46 l_start_pos NUMBER;
47 l_delimiter VARCHAR2(1):=' ';
48 l_proc VARCHAR2(40):= g_package||' get parameter ';
49 BEGIN
50 --
51 IF g_debug THEN
52 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
53 END IF;
54 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
55 --
56 IF l_start_pos = 0 THEN
57 l_delimiter := '|';
58 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
59 END IF;
60
61 IF l_start_pos <> 0 THEN
62 l_start_pos := l_start_pos + length(p_token||'=');
63 l_parameter := substr(p_parameter_string,
64 l_start_pos,
65 instr(p_parameter_string||' ',
66 l_delimiter,l_start_pos)
67 - l_start_pos);
68 IF p_segment_number IS NOT NULL THEN
69 l_parameter := ':'||l_parameter||':';
70 l_parameter := substr(l_parameter,
71 instr(l_parameter,':',1,p_segment_number)+1,
72 instr(l_parameter,':',1,p_segment_number+1) -1
73 - instr(l_parameter,':',1,p_segment_number));
74 END IF;
75 END IF;
76 --
77 IF g_debug THEN
78 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
79 END IF;
80
81 RETURN l_parameter;
82
83 END;
84
85 /* GET ALL PARAMETERS */
86 PROCEDURE GET_ALL_PARAMETERS(
87 p_payroll_action_id IN NUMBER
88 ,p_business_group_id OUT NOCOPY NUMBER
89 ,p_legal_employer_id OUT NOCOPY NUMBER
90 ,p_local_unit_id OUT NOCOPY NUMBER
91 -- ,p_period OUT NOCOPY VARCHAR2
92 -- ,p_year OUT NOCOPY VARCHAR2
93 ,p_effective_date OUT NOCOPY DATE
94 ,p_archive OUT NOCOPY VARCHAR2
95 ) IS
96
97 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
98 SELECT PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
99 ,PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
100 -- ,LPAD(PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'PERIOD_RPT'),2,'0')
101 -- ,PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
102 ,PAY_NO_ARCHIVE_RSEA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
103 ,effective_date
104 ,business_group_id
105 FROM pay_payroll_actions
106 WHERE payroll_action_id = p_payroll_action_id;
107 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
108 --
109 BEGIN
110
111 OPEN csr_parameter_info (p_payroll_action_id);
112
113 FETCH csr_parameter_info
114 INTO p_legal_employer_id
115 ,p_local_unit_id
116 -- ,p_period
117 -- ,p_year
118 ,p_archive
119 ,p_effective_date
120 ,p_business_group_id;
121 CLOSE csr_parameter_info;
122 --
123 IF g_debug THEN
124 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
125 END IF;
126 END GET_ALL_PARAMETERS;
127
128 /* RANGE CODE */
129 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
130 ,p_sql OUT NOCOPY VARCHAR2)
131 IS
132
133 l_action_info_id NUMBER;
134 l_ovn NUMBER;
135
136 l_defined_balance_id NUMBER := 0;
137 l_count NUMBER := 0;
138
139 l_business_group_id NUMBER;
140 l_period VARCHAR2(2);
141 l_year VARCHAR2(4);
142 l_canonical_start_date DATE;
143 l_canonical_end_date DATE;
144 l_effective_date DATE;
145 l_legal_employer_id NUMBER ;
146 l_local_unit_id NUMBER ;
147 l_emp_id hr_organization_units.organization_id%TYPE ;
148 l_lu_name hr_organization_units.name%TYPE ;
149 l_business_id hr_organization_information.org_information1%TYPE ;
150 l_archive VARCHAR2(3);
151
152 l_le_name hr_organization_units.name%TYPE ;
153 l_org_number hr_organization_information.org_information1%TYPE ;
154 l_municipal_no hr_organization_information.org_information1%TYPE ;
155 l_industry_status hr_organization_information.org_information1%TYPE ;
156 l_tax_office_name hr_organization_units.name%TYPE ;
157 l_tax_office_id hr_organization_information.organization_id%TYPE ;
158
159 l_address_line_1 hr_locations.address_line_1%TYPE ;
160 l_address_line_2 hr_locations.address_line_2%TYPE ;
161 l_address_line_3 hr_locations.address_line_3%TYPE ;
162 l_postal_code hr_locations.postal_code%TYPE ;
163 l_postal_office hr_locations.postal_code%TYPE ;
164
165 l_phone hr_organization_information.org_information1%TYPE ;
166 l_email hr_organization_information.org_information1%TYPE ;
167
168 l_taddress_line_1 hr_locations.address_line_1%TYPE ;
169 l_taddress_line_2 hr_locations.address_line_2%TYPE ;
170 l_taddress_line_3 hr_locations.address_line_3%TYPE ;
171 l_tpostal_code hr_locations.postal_code%TYPE ;
172 l_tpostal_office hr_locations.postal_code%TYPE ;
173
174 l_reporting_start_date DATE;
175 l_reporting_end_date DATE;
176
177 l_municipal_name VARCHAR2(30);
178 l_zone NUMBER;
179 l_Witholding_Tax NUMBER;
180 l_u_contribution_basis NUMBER;
181 l_o_contribution_basis NUMBER;
182 l_tWitholding_Tax NUMBER;
183 l_tu_contribution_basis NUMBER;
184 l_to_contribution_basis NUMBER;
185 l_u_rate NUMBER;
186 l_o_rate NUMBER;
187 l_u_calc_contribution NUMBER;
188 l_o_calc_contribution NUMBER;
189 l_eWitholding_Tax NUMBER;
190 l_eu_contribution_basis NUMBER;
191 l_eo_contribution_basis NUMBER;
192 l_eu_rate NUMBER;
193 l_eo_rate NUMBER;
194 l_eu_calc_contribution NUMBER;
195 l_eo_calc_contribution NUMBER;
196 l_def_bal_id NUMBER;
197 l_tfe_spr_contribution_basis NUMBER;
198 l_tfe_spr_calc_contribution NUMBER;
199 l_fe_spr_contribution_basis NUMBER;
200 l_fe_spr_calc_contribution NUMBER;
201 l_fe_spr_rate NUMBER;
202 l_fe_fm_amount NUMBER;
203 l_fe_fma_calc_contribution NUMBER;
204 l_tfe_fma_calc_contribution NUMBER;
205 l_to_calc_contribution NUMBER;
206 l_tu_calc_contribution NUMBER;
207 l_emp_contri_el NUMBER;
208 l_t_emp_contri_el NUMBER;
209 l_el NUMBER;
210 l_t_emp_contri_el_bimonth NUMBER;
211 l_emp_contri_el_bimonth NUMBER;
212
213
214 TYPE municipaldata IS RECORD
215 (
216 municipalcode VARCHAR2(10)
217 );
218
219 TYPE tmunicipaldata IS TABLE OF municipaldata
220 INDEX BY BINARY_INTEGER;
221
222 gmunicipaldata tmunicipaldata ;
223
224
225 l_counter NUMBER;
226 l_status NUMBER;
227
228
229 /* Cursors */
230
231 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
232 IS
233 SELECT o1.name , hoi2.ORG_INFORMATION4
234 FROM hr_organization_units o1
235 , hr_organization_information hoi1
236 , hr_organization_information hoi2
237 WHERE o1.business_group_id =l_business_group_id
238 AND hoi1.organization_id = o1.organization_id
239 AND hoi1.organization_id = csr_v_local_unit_id
240 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
241 AND hoi1.org_information_context = 'CLASS'
242 AND o1.organization_id =hoi2.organization_id
243 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNIT_DETAILS';
244
245 rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
246
247 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
248 IS
249 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION2 , hoi2.ORG_INFORMATION3 , hoi2.ORG_INFORMATION5
250 FROM hr_organization_units o1
251 , hr_organization_information hoi1
252 , hr_organization_information hoi2
253 WHERE o1.business_group_id =l_business_group_id
254 AND hoi1.organization_id = o1.organization_id
255 AND hoi1.organization_id = csr_v_legal_emp_id
256 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
257 AND hoi1.org_information_context = 'CLASS'
258 AND o1.organization_id =hoi2.organization_id
259 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LEGAL_EMPLOYER_DETAILS' ;
260
261
262 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
263
264 Cursor csr_Legal_Emp_Contact ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
265 IS
266 SELECT hoi2.ORG_INFORMATION2 email , hoi3.ORG_INFORMATION2 phone
267 FROM hr_organization_units o1
268 , hr_organization_information hoi1
269 , hr_organization_information hoi2
270 , hr_organization_information hoi3
271 WHERE o1.business_group_id =l_business_group_id
272 AND hoi1.organization_id = o1.organization_id
273 AND hoi1.organization_id = csr_v_legal_emp_id
274 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
275 AND hoi1.org_information_context = 'CLASS'
276 AND hoi2.organization_id (+)= o1.organization_id
277 AND hoi2.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
278 AND hoi2.org_information1(+)= 'EMAIL'
279 AND hoi3.organization_id (+)= o1.organization_id
280 AND hoi3.ORG_INFORMATION_CONTEXT (+)='ORG_CONTACT_DETAILS'
281 AND hoi3.org_information1 (+) = 'PHONE' ;
282
283 rg_Legal_Emp_Contact csr_Legal_Emp_Contact%rowtype;
284
285 Cursor csr_Legal_Emp_addr ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
286 IS
287 SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
288 hoi1.POSTAL_CODE , SUBSTR(hlu.MEANING , INSTR(hlu.MEANING,' ', 1,1) , LENGTH(hlu.MEANING) -(INSTR(hlu.MEANING,' ', 1,1) -1) ) POSTAL_OFFICE
289 FROM hr_organization_units o1
290 , hr_locations hoi1
291 ,hr_organization_information hoi2
292 ,hr_lookups hlu
293 WHERE o1.business_group_id = l_business_group_id
294 AND hoi1.location_id = o1.location_id
295 AND hoi2.organization_id = o1.organization_id
296 AND hoi2.organization_id = csr_v_legal_emp_id
297 AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
298 AND hoi2.org_information_context = 'CLASS'
299 AND hlu.lookup_type='NO_POSTAL_CODE'
300 AND hlu.enabled_flag='Y'
301 AND hlu.lookup_code = hoi1.POSTAL_CODE;
302
303
304
305 rg_Legal_Emp_addr csr_Legal_Emp_addr%rowtype;
306
307
308 Cursor csr_Tax_Office_Details ( csr_v_tax_office_id hr_organization_information.ORGANIZATION_ID%TYPE)
309 IS
310 SELECT o1.name , hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
311 hoi1.POSTAL_CODE , SUBSTR(hlu.MEANING , INSTR(hlu.MEANING,' ', 1,1) ) POSTAL_OFFICE
312 FROM hr_organization_units o1
313 , hr_locations hoi1
314 ,hr_organization_information hoi2
315 ,hr_lookups hlu
316 WHERE o1.business_group_id = l_business_group_id
317 AND hoi1.location_id = o1.location_id
318 AND hoi2.organization_id = o1.organization_id
319 AND hoi2.organization_id = csr_v_tax_office_id
320 AND hoi2.org_information1 = 'NO_TAX_OFFICE'
321 AND hoi2.org_information_context = 'CLASS'
322 AND hlu.lookup_type='NO_POSTAL_CODE'
323 AND hlu.enabled_flag='Y'
324 AND hlu.lookup_code = hoi1.POSTAL_CODE;
325
326
327 rg_Tax_Office_Details csr_Tax_Office_Details%rowtype;
328
329 CURSOR csr_prepaid_assignments_le
330 (p_payroll_action_id NUMBER,
331 p_legal_employer_id NUMBER,
332 l_canonical_start_date DATE,
333 l_canonical_end_date DATE)
334 IS
335 SELECT
336 DISTINCT act.assignment_id assignment_id
337 FROM pay_payroll_actions ppa
338 ,pay_payroll_actions appa
339 ,pay_payroll_actions appa2
340 ,pay_assignment_actions act
341 ,pay_assignment_actions act1
342 ,pay_action_interlocks pai
343 ,per_all_assignments_f as1
344 ,hr_soft_coding_keyflex hsck
345 WHERE ppa.payroll_action_id = p_payroll_action_id
346 AND appa.effective_date BETWEEN l_canonical_start_date
347 AND l_canonical_end_date
348 AND appa.action_type IN ('R','Q')
349 -- Payroll Run or Quickpay Run
350 AND act.payroll_action_id = appa.payroll_action_id
351 AND act.source_action_id IS NULL -- Master Action
352 AND as1.assignment_id = act.assignment_id
353 AND ppa.effective_date BETWEEN as1.effective_start_date
354 AND as1.effective_end_date
355 AND act.action_status IN ('C','S') -- 10229512
356 AND act.assignment_action_id = pai.locked_action_id
357 AND act1.assignment_action_id = pai.locking_action_id
358 AND act1.action_status IN ('C','S') -- 10229512
359 AND act1.payroll_action_id = appa2.payroll_action_id
360 AND appa2.action_type IN ('P','U')
361 AND appa2.effective_date BETWEEN l_canonical_start_date
362 AND l_canonical_end_date
363 -- Prepayments or Quickpay Prepayments
364 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
365 AND act.TAX_UNIT_ID = p_legal_employer_id
366 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
367 AND EXISTS
368 ( SELECT hoi1.organization_id
369 FROM hr_organization_units o1
370 , hr_organization_information hoi1
371 , hr_organization_information hoi2
372 , hr_organization_information hoi3
373 , hr_organization_information hoi4
374 WHERE hoi1.organization_id = o1.organization_id
375 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
376 AND hoi1.org_information_context = 'CLASS'
377 AND o1.organization_id = hoi2.org_information1
378 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
379 AND hoi2.organization_id = hoi3.organization_id
380 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
381 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
382 aND hoi3.organization_id = p_legal_employer_id
383 AND hoi1.organization_id = hoi4.organization_id
384 AND hoi4.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNIT_DETAILS'
385 AND hoi4.ORG_INFORMATION5= 'N'
386 AND to_char(hoi1.organization_id) = hsck.segment2 );
387
388
389
390 CURSOR csr_prepaid_assignments_lu(p_payroll_action_id NUMBER,
391 p_legal_employer_id NUMBER,
392 p_local_unit_id NUMBER,
393 l_canonical_start_date DATE,
394 l_canonical_end_date DATE)
395 IS
396 SELECT DISTINCT act.assignment_id assignment_id
397 FROM pay_payroll_actions ppa
398 ,pay_payroll_actions appa
399 ,pay_payroll_actions appa2
400 ,pay_assignment_actions act
401 ,pay_assignment_actions act1
402 ,pay_action_interlocks pai
403 ,per_all_assignments_f as1
404 ,hr_soft_coding_keyflex hsck
405 WHERE ppa.payroll_action_id = p_payroll_action_id
406 AND appa.effective_date BETWEEN l_canonical_start_date
407 AND l_canonical_end_date
408 AND appa.action_type IN ('R','Q')
409 -- Payroll Run or Quickpay Run
410 AND act.payroll_action_id = appa.payroll_action_id
411 AND act.source_action_id IS NULL -- Master Action
412 AND as1.assignment_id = act.assignment_id
413 AND ppa.effective_date BETWEEN as1.effective_start_date
414 AND as1.effective_end_date
415 AND act.action_status IN ('C','S') -- 10229512
416 AND act.assignment_action_id = pai.locked_action_id
417 AND act1.assignment_action_id = pai.locking_action_id
418 AND act1.action_status IN ('C','S') -- 10229512
419 AND act1.payroll_action_id = appa2.payroll_action_id
420 AND appa2.action_type IN ('P','U')
421 AND appa2.effective_date BETWEEN l_canonical_start_date
422 AND l_canonical_end_date
423 -- Prepayments or Quickpay Prepayments
424 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
425 AND hsck.segment2 = TO_CHAR(p_local_unit_id)
426 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
427 AND act.TAX_UNIT_ID = p_legal_employer_id ;
428
429
430 CURSOR csr_get_mun_num(p_assignment_id NUMBER,p_effective_date DATE )
431 IS
432 SELECT eev1.screen_entry_value screen_entry_value
433 FROM per_all_assignments_f asg1
434 ,per_all_assignments_f asg2
435 ,per_all_people_f per
436 ,pay_element_links_f el
437 ,pay_element_types_f et
438 ,pay_input_values_f iv1
439 ,pay_element_entries_f ee
440 ,pay_element_entry_values_f eev1
441 WHERE asg1.assignment_id = p_assignment_id
442 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
443 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
444 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
445 AND per.person_id = asg1.person_id
446 AND asg2.person_id = per.person_id
447 AND asg2.primary_flag = 'Y'
448 AND et.element_name = 'Tax Card'
449 AND et.legislation_code = 'NO'
450 AND iv1.element_type_id = et.element_type_id
451 AND iv1.name = 'Tax Municipality'
452 AND el.business_group_id = per.business_group_id
453 AND el.element_type_id = et.element_type_id
454 AND ee.assignment_id = asg2.assignment_id
455 AND ee.element_link_id = el.element_link_id
456 AND eev1.element_entry_id = ee.element_entry_id
457 AND eev1.input_value_id = iv1.input_value_id
458 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
459 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
460
461
462 CURSOR csr_get_mun_dtls(p_municipal_no VARCHAR2, l_effective_date DATE )
463 IS
464 SELECT hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'ZONE') zone
465 ,hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
466 hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'MAPPING_ID')) municipal_name
467 FROM pay_user_tables t
468 ,pay_user_rows_f r
469 WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
470 AND t.legislation_code = 'NO'
471 AND r.user_table_id = t.user_table_id
472 AND r.row_low_range_or_name = p_municipal_no
473 AND l_effective_date BETWEEN r.effective_start_date AND r.effective_end_date;
474
475 /* SELECT SUBSTR( meaning ,1,1) zone , TRIM(SUBSTR( meaning ,2)) municipal_name
476 FROM hr_lookups
477 WHERE lookup_type='NO_TAX_MUNICIPALITY'
478 AND enabled_flag='Y'
479 AND lookup_code = p_municipal_no;
480 */
481 rg_get_mun_dtls csr_get_mun_dtls%rowtype;
482
483
484 CURSOR csr_lu_dtls(p_legal_employer_id NUMBER )
485 IS
486 SELECT hoi1.organization_id
487 FROM hr_organization_units o1
488 , hr_organization_information hoi1
489 , hr_organization_information hoi2
490 , hr_organization_information hoi3
491 , hr_organization_information hoi4
492 WHERE hoi1.organization_id = o1.organization_id
493 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
494 AND hoi1.org_information_context = 'CLASS'
495 AND o1.organization_id = hoi2.org_information1
496 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
497 AND hoi2.organization_id = hoi3.organization_id
498 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
499 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
500 aND hoi3.organization_id = p_legal_employer_id
501 AND hoi1.organization_id = hoi4.organization_id
502 AND hoi4.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNIT_DETAILS'
503 AND hoi4.ORG_INFORMATION5= 'N';
504
505 CURSOR csr_global_value (p_global_name VARCHAR2 , p_date_earned DATE)
506 IS
507 SELECT global_value
508 FROM ff_globals_f
509 WHERE global_name = p_global_name
510 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
511
512 Cursor csr_Local_Unit_EL ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE, p_date_earned DATE)
513 IS
514 SELECT hoi2.org_information1
515 FROM hr_organization_units o1
516 , hr_organization_information hoi1
517 , hr_organization_information hoi2
518 WHERE o1.business_group_id =l_business_group_id
519 AND hoi1.organization_id = o1.organization_id
520 AND hoi1.organization_id = csr_v_local_unit_id
521 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
522 AND hoi1.org_information_context = 'CLASS'
523 AND o1.organization_id =hoi2.organization_id
524 AND hoi2.ORG_INFORMATION_CONTEXT='NO_NI_EXEMPTION_LIMIT'
525 AND p_date_earned between fnd_date.canonical_to_date(hoi2.org_information2)
526 AND fnd_date.canonical_to_date(hoi2.org_information3);
527
528 rg_Local_Unit_EL csr_Local_Unit_EL%rowtype;
529
530 Cursor csr_Legal_Emp_EL( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE, p_date_earned DATE)
531 IS
532 SELECT hoi2.org_information1
533 FROM hr_organization_units o1
534 , hr_organization_information hoi1
535 , hr_organization_information hoi2
536 WHERE o1.business_group_id =l_business_group_id
537 AND hoi1.organization_id = o1.organization_id
538 AND hoi1.organization_id = csr_v_legal_emp_id
539 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
540 AND hoi1.org_information_context = 'CLASS'
541 AND o1.organization_id =hoi2.organization_id
542 AND hoi2.ORG_INFORMATION_CONTEXT='NO_NI_EXEMPTION_LIMIT'
543 AND p_date_earned between fnd_date.canonical_to_date(hoi2.org_information2)
544 AND fnd_date.canonical_to_date(hoi2.org_information3);
545
546 rg_Legal_Emp_EL csr_Legal_Emp_EL%rowtype;
547
548 /* End of Cursors */
549
550 BEGIN
551
552 g_debug:=true;
553
554 IF g_debug THEN
555 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
556 END IF;
557
558 p_sql := 'SELECT DISTINCT person_id
559 FROM per_people_f ppf
560 ,pay_payroll_actions ppa
561 WHERE ppa.payroll_action_id = :payroll_action_id
562 AND ppa.business_group_id = ppf.business_group_id
563 ORDER BY ppf.person_id';
564
565
566 PAY_NO_ARCHIVE_RSEA.GET_ALL_PARAMETERS(
567 p_payroll_action_id
568 ,l_business_group_id
569 ,l_legal_employer_id
570 ,l_local_unit_id
571 -- ,l_period
572 -- ,l_year
573 ,l_effective_date
574 ,l_archive ) ;
575
576 l_period := to_char(ceil(to_number(to_char(l_effective_date,'MM'))/ 2));
577 l_year := to_char(l_effective_date,'YYYY');
578
579 l_reporting_end_date := LAST_DAY(TO_DATE(LPAD(l_period*2,2,'0')||l_year,'MMYYYY'));
580
581
582 l_reporting_start_date :=ADD_MONTHS( l_reporting_end_date , -2 ) + 1;
583
584 IF l_archive = 'Y' THEN
585
586 SELECT count(*) INTO l_count
587 FROM pay_action_information
588 WHERE action_context_id = p_payroll_action_id
589 AND action_context_type = 'PA'
590 AND action_information_category = 'EMEA REPORT DETAILS'
591 AND action_information1 = 'PYNORSEA';
592
593 IF l_count < 1 then
594
595 /* Pick up the details belonging to Legal Employer */
596
597 OPEN csr_Legal_Emp_Details(l_legal_employer_id);
598 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
599 CLOSE csr_Legal_Emp_Details;
600
601 l_le_name := rg_Legal_Emp_Details.name ;
602 l_org_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
603 l_municipal_no := rg_Legal_Emp_Details.ORG_INFORMATION2 ;
604 l_industry_status := rg_Legal_Emp_Details.ORG_INFORMATION3 ;
605 l_tax_office_id := rg_Legal_Emp_Details.ORG_INFORMATION5 ;
606
607 l_emp_id:=l_legal_employer_id;
608
609
610 IF l_local_unit_id IS NOT NULL THEN
611
612 /* Pick up the details belonging to Local Unit */
613
614 OPEN csr_Local_Unit_Details( l_local_unit_id);
615 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
616 CLOSE csr_Local_Unit_Details;
617
618 l_lu_name :=rg_Local_Unit_Details.name;
619 l_industry_status := rg_Local_Unit_Details.ORG_INFORMATION4 ;
620
621 END IF ;
622
623
624 /* Pick up the contact details belonging to Legal Employer */
625
626 OPEN csr_Legal_Emp_contact( l_legal_employer_id);
627 FETCH csr_Legal_Emp_contact INTO rg_Legal_Emp_contact;
628 CLOSE csr_Legal_Emp_contact;
629
630 l_email := rg_Legal_Emp_Contact .email;
631 l_phone := rg_Legal_Emp_Contact .phone ;
632
633
634 /* Pick up the Address details belonging to Legal Employer */
635
636 OPEN csr_Legal_Emp_addr(l_legal_employer_id);
637 FETCH csr_Legal_Emp_addr INTO rg_Legal_Emp_addr;
638 CLOSE csr_Legal_Emp_addr;
639
640 l_address_line_1 := rg_Legal_Emp_addr.address_line_1 ;
641 l_address_line_2 := rg_Legal_Emp_addr.address_line_2 ;
642 l_address_line_3 := rg_Legal_Emp_addr.address_line_3 ;
643 l_postal_code := rg_Legal_Emp_addr.postal_code ;
644 l_postal_office := rg_Legal_Emp_addr.postal_office ;
645
646 /* Pick up the tax office details belonging to Employer*/
647
648 OPEN csr_Tax_Office_Details( l_tax_office_id );
649 FETCH csr_Tax_Office_Details INTO rg_Tax_Office_Details;
650 CLOSE csr_Tax_Office_Details;
651
652 l_tax_office_name := rg_Tax_Office_Details.name ;
653 l_taddress_line_1 := rg_Tax_Office_Details.ADDRESS_LINE_1 ;
654 l_taddress_line_2 := rg_Tax_Office_Details.ADDRESS_LINE_2;
655 l_taddress_line_3 := rg_Tax_Office_Details.ADDRESS_LINE_3 ;
656 l_tpostal_code := rg_Tax_Office_Details.POSTAL_CODE ;
657 l_tpostal_office := rg_Tax_Office_Details.POSTAL_OFFICE;
658
659 IF l_local_unit_id IS NOT NULL THEN
660
661 /* Pick up the Exemption Limit details belonging to Local Unit*/
662 OPEN csr_Local_Unit_EL( l_local_unit_id , l_reporting_end_date);
663 FETCH csr_Local_Unit_EL INTO rg_Local_Unit_EL;
664 CLOSE csr_Local_Unit_EL;
665 l_el := rg_Local_Unit_EL.ORG_INFORMATION1;
666
667 ELSE
668 /* Pick up the Exemption Limit details belonging to Employer*/
669
670 OPEN csr_Legal_Emp_EL( l_legal_employer_id , l_reporting_end_date);
671 FETCH csr_Legal_Emp_EL INTO rg_Legal_Emp_EL;
672 CLOSE csr_Legal_Emp_EL;
673 l_el := rg_Legal_Emp_EL.ORG_INFORMATION1;
674
675 END IF;
676
677 /* Inserting header details belonging to Employer*/
678
679 pay_action_information_api.create_action_information (
680 p_action_information_id => l_action_info_id
681 ,p_action_context_id => p_payroll_action_id
682 ,p_action_context_type => 'PA'
683 ,p_object_version_number => l_ovn
684 ,p_effective_date => l_effective_date
685 ,p_source_id => NULL
686 ,p_source_text => NULL
687 ,p_action_information_category => 'EMEA REPORT INFORMATION'
688 ,p_action_information1 => 'PYNORSEA'
689 ,p_action_information2 => l_emp_id
690 ,p_action_information3 => l_period||l_year
691 ,p_action_information4 => l_org_number
692 ,p_action_information5 => l_municipal_no
693 ,p_action_information6 => l_le_name
694 ,p_action_information7 => l_address_line_1
695 ,p_action_information8 => l_address_line_2||' '||l_address_line_3
696 ,p_action_information9 => l_postal_code
697 ,p_action_information10 => l_postal_office
698 ,p_action_information11 => l_email
699 ,p_action_information12 => l_phone
700 ,p_action_information13 => l_tax_office_name
701 ,p_action_information14 => l_taddress_line_1
702 ,p_action_information15 => l_taddress_line_2||' '||l_taddress_line_3
703 ,p_action_information16 => l_tpostal_code||' '||l_tpostal_office
704 ,p_action_information17 => l_industry_status
705 ,p_action_information18 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_el,0))
706 ,p_action_information19 => null
707 ,p_action_information20 => null
708 ,p_action_information21 => null
709 ,p_action_information22 => null
710 ,p_action_information23 => null
711 ,p_action_information24 => null
712 ,p_action_information25 => null
713 ,p_action_information26 => null
714 ,p_action_information27 => null
715 ,p_action_information28 => null
716 ,p_action_information29 => null
717 ,p_action_information30 => null );
718
719 IF g_debug THEN
720 hr_utility.set_location(' Inside Procedure RANGE_CODE',20);
721 END IF;
722
723
724
725 /* Inserting the selection criteria for generating the report*/
726
727 pay_action_information_api.create_action_information (
728 p_action_information_id => l_action_info_id
729 ,p_action_context_id => p_payroll_action_id
730 ,p_action_context_type => 'PA'
731 ,p_object_version_number => l_ovn
732 ,p_effective_date => l_effective_date
733 ,p_source_id => NULL
734 ,p_source_text => NULL
735 ,p_action_information_category => 'EMEA REPORT DETAILS'
736 ,p_action_information1 => 'PYNORSEA'
737 ,p_action_information2 => l_le_name
738 ,p_action_information3 => l_lu_name
739 ,p_action_information4 => l_period
740 ,p_action_information5 => l_year
741 ,p_action_information6 => null
742 ,p_action_information7 => null
743 ,p_action_information8 => null
744 ,p_action_information9 => null
745 ,p_action_information10 => null
746 ,p_action_information11 => null
747 ,p_action_information12 => null
748 ,p_action_information13 => null
749 ,p_action_information14 => null
750 ,p_action_information15 => null
751 ,p_action_information16 => null
752 ,p_action_information17 => null
753 ,p_action_information18 => null
754 ,p_action_information19 => null
755 ,p_action_information20 => null
756 ,p_action_information21 => null
757 ,p_action_information22 => null
758 ,p_action_information23 => null
759 ,p_action_information24 => null
760 ,p_action_information25 => null
761 ,p_action_information26 => null
762 ,p_action_information27 => null
763 ,p_action_information28 => null
764 ,p_action_information29 => null
765 ,p_action_information30 => null );
766
767 IF g_debug THEN
768 hr_utility.set_location(' Inside Procedure RANGE_CODE',30);
769 END IF;
770
771
772 /* Inserting municipal codes for the Legal Employer in a PL/SQL table */
773
774 IF l_local_unit_id IS NULL THEN
775
776 l_counter := 0;
777 l_status := 0;
778 FOR prepaid_assignments_le_rec IN csr_prepaid_assignments_le(p_payroll_action_id ,l_legal_employer_id,l_reporting_start_date,l_reporting_END_date)
779 LOOP
780
781 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id ,l_reporting_start_date )
782 LOOP
783
784 IF l_counter > 0 THEN
785 FOR i IN 1 .. l_counter LOOP
786
787 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
788
789 l_status:= 1;
790 EXIT ;
791 END IF;
792
793 END LOOP;
794 END IF;
795
796
797 IF l_status= 0 THEN
798 l_counter := l_counter + 1;
799 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
800
801 END IF;
802
803 l_status := 0;
804
805 END LOOP;
806
807 l_status := 0;
808
809 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id ,l_reporting_end_date )
810 LOOP
811
812 IF l_counter > 0 THEN
813 FOR i IN 1 .. l_counter LOOP
814 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
815 l_status:= 1;
816 EXIT ;
817 END IF;
818 END LOOP;
819 END IF;
820
821 IF l_status= 0 THEN
822 l_counter := l_counter + 1;
823 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
824 END IF;
825
826
827 l_status := 0;
828
829 END LOOP;
830
831
832 END LOOP;
833
834 ELSE
835
836 /* Inserting municipal codes for the Local Unit in a PL/SQL table */
837
838 l_counter := 0;
839 l_status := 0;
840
841 FOR prepaid_assignments_lu_rec IN csr_prepaid_assignments_lu(p_payroll_action_id ,l_legal_employer_id, l_local_unit_id,l_reporting_start_date,l_reporting_END_date)
842 LOOP
843
844 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id ,l_reporting_start_date )
845 LOOP
846
847 IF l_counter > 0 THEN
848 FOR i IN 1 .. l_counter LOOP
849 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
850 l_status:= 1;
851 EXIT ;
852 END IF;
853 END LOOP;
854 END IF;
855
856 IF l_status= 0 THEN
857 l_counter := l_counter + 1;
858 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
859 END IF;
860
861
862 l_status := 0;
863
864 END LOOP;
865
866 l_status := 0;
867
868 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id ,l_reporting_end_date )
869 LOOP
870 IF l_counter > 0 THEN
871 FOR i IN 1 .. l_counter LOOP
872 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
873 l_status:= 1;
874 EXIT ;
875 END IF;
876 END LOOP;
877 END IF;
878
879 IF l_status= 0 THEN
880 l_counter := l_counter + 1;
881 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
882 END IF;
883
884 l_status := 0;
885
886 END LOOP;
887
888 END LOOP;
889
890 END IF ;
891
892 IF g_debug THEN
893 hr_utility.set_location(' Inside Procedure RANGE_CODE',40);
894 END IF;
895
896
897
898 /* Setting contexts for balances*/
899 pay_balance_pkg.set_context('TAX_UNIT_ID',l_legal_employer_id);
900 pay_balance_pkg.set_context('JURISDICTION_CODE',l_municipal_no);
901 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(l_reporting_end_date));
902
903 IF g_debug THEN
904 hr_utility.set_location(' Inside Procedure RANGE_CODE',50);
905 END IF;
906
907
908 FOR i IN 1 .. l_counter LOOP
909
910 l_municipal_no:=gmunicipaldata(i).municipalcode;
911 IF l_municipal_no IS NOT NULL THEN
912
913 /* Setting municipality details for balances*/
914 OPEN csr_get_mun_dtls(l_municipal_no, l_reporting_end_date);
915 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
916 CLOSE csr_get_mun_dtls;
917
918 l_zone := rg_get_mun_dtls.zone ;
919 l_municipal_name := rg_get_mun_dtls.municipal_name;
920
921 /* Initialising values*/
922 l_o_contribution_basis:=0;
923 l_u_contribution_basis:=0;
924 l_u_calc_contribution :=0;
925 l_o_calc_contribution :=0;
926 l_Witholding_Tax:=0;
927 l_fe_spr_contribution_basis:=0;
928 l_fe_spr_calc_contribution:=0;
929 l_fe_fma_calc_contribution :=0;
930 l_emp_contri_el :=0;
931 l_emp_contri_el_bimonth:=0;
932
933 /* Fetching balance values related to employer contributions report*/
934 IF l_local_unit_id IS NULL THEN
935
936 FOR lu_dtls_rec IN csr_lu_dtls(l_legal_employer_id )
937 LOOP
938
939 pay_balance_pkg.set_context('LOCAL_UNIT_ID',lu_dtls_rec.organization_id);
940
941 -- get defined balance ids
942 l_def_bal_id := get_defined_balance_id('Employer Contribution Over 62 Base','_TU_MU_LU_BIMONTH') ;
943 l_to_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
944 l_o_contribution_basis := l_to_contribution_basis + l_o_contribution_basis;
945
946 l_def_bal_id := get_defined_balance_id('Employer Contribution Base','_TU_MU_LU_BIMONTH') ;
947 l_tu_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
948 l_u_contribution_basis := l_tu_contribution_basis + l_u_contribution_basis;
949
950 l_def_bal_id := get_defined_balance_id('Employer Contribution Over 62','_TU_MU_LU_BIMONTH') ;
951 l_to_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
952 l_o_calc_contribution := l_to_calc_contribution + l_o_calc_contribution;
953
954 l_def_bal_id := get_defined_balance_id('Employer Contribution','_TU_MU_LU_BIMONTH') ;
955 l_tu_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
956 l_u_calc_contribution := l_tu_calc_contribution + l_u_calc_contribution;
957
958 l_def_bal_id := get_defined_balance_id('Tax','_TU_MU_LU_BIMONTH') ;
959 l_tWitholding_Tax := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
960 l_Witholding_Tax := l_Witholding_Tax + l_tWitholding_Tax;
961
962 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base','_TU_MU_LU_BIMONTH') ;
963 l_tfe_spr_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
964 l_fe_spr_contribution_basis := l_fe_spr_contribution_basis + l_tfe_spr_contribution_basis;
965
966 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage','_TU_MU_LU_BIMONTH') ;
967 l_tfe_spr_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
968 l_fe_spr_calc_contribution := l_fe_spr_calc_contribution + l_tfe_spr_calc_contribution;
969
970 l_def_bal_id := get_defined_balance_id('Employer Contribution Special','_TU_MU_LU_BIMONTH') ;
971 l_tfe_fma_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
972 l_fe_fma_calc_contribution := l_fe_fma_calc_contribution + l_tfe_fma_calc_contribution;
973
974 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_MU_LU_YTD') ;
975 l_t_emp_contri_el := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
976 l_emp_contri_el := l_t_emp_contri_el + l_emp_contri_el;
977
978 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_MU_LU_BIMONTH') ;
979 l_t_emp_contri_el_bimonth := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
980 l_emp_contri_el_bimonth := l_t_emp_contri_el_bimonth + l_emp_contri_el_bimonth;
981
982 /* Resetting the values*/
983 l_to_contribution_basis:=0;
984 l_tu_contribution_basis:=0;
985 l_to_calc_contribution:=0;
986 l_tu_calc_contribution:=0;
987 l_tWitholding_Tax:=0;
988 l_tfe_spr_contribution_basis:=0;
989 l_tfe_spr_calc_contribution:=0;
990 l_tfe_fma_calc_contribution :=0;
991 l_t_emp_contri_el :=0;
992 l_t_emp_contri_el_bimonth:=0;
993
994 END LOOP;
995
996
997
998 ELSE
999
1000 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
1001
1002 -- get defined balance ids
1003 l_def_bal_id := get_defined_balance_id('Employer Contribution Over 62 Base','_TU_MU_LU_BIMONTH') ;
1004 l_o_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1005
1006 l_def_bal_id := get_defined_balance_id('Employer Contribution Base','_TU_MU_LU_BIMONTH') ;
1007 l_u_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1008
1009 l_def_bal_id := get_defined_balance_id('Employer Contribution Over 62','_TU_MU_LU_BIMONTH') ;
1010 l_to_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1011 l_o_calc_contribution := l_to_calc_contribution + l_o_calc_contribution;
1012
1013 l_def_bal_id := get_defined_balance_id('Employer Contribution','_TU_MU_LU_BIMONTH') ;
1014 l_tu_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1015 l_u_calc_contribution := l_tu_calc_contribution + l_u_calc_contribution;
1016
1017 l_def_bal_id := get_defined_balance_id('Tax','_TU_MU_LU_BIMONTH') ;
1018 l_Witholding_Tax := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1019
1020 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base','_TU_MU_LU_BIMONTH') ;
1021 l_tfe_spr_contribution_basis := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1022 l_fe_spr_contribution_basis := l_fe_spr_contribution_basis + l_tfe_spr_contribution_basis;
1023
1024 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage','_TU_MU_LU_BIMONTH') ;
1025 l_tfe_spr_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1026 l_fe_spr_calc_contribution := l_fe_spr_calc_contribution + l_tfe_spr_calc_contribution;
1027
1028 l_def_bal_id := get_defined_balance_id('Employer Contribution Special','_TU_MU_LU_BIMONTH') ;
1029 l_tfe_fma_calc_contribution := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1030 l_fe_fma_calc_contribution := l_fe_fma_calc_contribution + l_tfe_fma_calc_contribution;
1031
1032 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_MU_LU_YTD') ;
1033 l_t_emp_contri_el := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1034 l_emp_contri_el := l_t_emp_contri_el + l_emp_contri_el;
1035
1036 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_MU_LU_BIMONTH') ;
1037 l_t_emp_contri_el_bimonth := pay_balance_pkg.get_value(l_def_bal_id,NULL,l_legal_employer_id,l_municipal_no,NULL,NULL,NULL,l_reporting_end_date);
1038 l_emp_contri_el_bimonth := l_t_emp_contri_el_bimonth + l_emp_contri_el_bimonth;
1039
1040 END IF;
1041
1042
1043 /* Fetching the global value NO_NI_FOREIGN_SPECIAL_RATE*/
1044 OPEN csr_global_value('NO_NI_FOREIGN_SPECIAL_RATE' , l_reporting_end_date ) ;
1045 FETCH csr_global_value INTO l_fe_spr_rate;
1046 CLOSE csr_global_value;
1047
1048
1049 /* Fetching the global value NO_NI_FOREIGN_MARINER_AMOUNT*/
1050 OPEN csr_global_value('NO_NI_FOREIGN_MARINER_AMOUNT' , l_reporting_end_date ) ;
1051 FETCH csr_global_value INTO l_fe_fm_amount;
1052 CLOSE csr_global_value;
1053
1054 l_u_rate :=0;
1055 l_o_rate :=0;
1056 l_eWitholding_Tax :=0;
1057 l_eu_contribution_basis :=0;
1058 l_eo_contribution_basis :=0;
1059 l_eu_rate :=0;
1060 l_eo_rate :=0;
1061 l_eu_calc_contribution :=0;
1062 l_eo_calc_contribution :=0;
1063
1064
1065 /* Inserting Local unit level data related to employer contributions*/
1066 pay_action_information_api.create_action_information (
1067 p_action_information_id => l_action_info_id
1068 ,p_action_context_id => p_payroll_action_id
1069 ,p_action_context_type => 'PA'
1070 ,p_object_version_number => l_ovn
1071 ,p_effective_date => l_effective_date
1072 ,p_source_id => NULL
1073 ,p_source_text => NULL
1074 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1075 ,p_action_information1 => 'PYNORSEA'
1076 ,p_action_information2 => 'M'
1077 ,p_action_information3 => l_municipal_no
1078 ,p_action_information4 => l_municipal_name
1079 ,p_action_information5 => l_zone
1080 ,p_action_information6 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_Witholding_Tax,0)))
1081 ,p_action_information7 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_contribution_basis,0)))
1082 ,p_action_information8 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_contribution_basis,0)))
1083 ,p_action_information9 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_rate,0))
1084 ,p_action_information10 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_rate,0))
1085 ,p_action_information11 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_u_calc_contribution,0)))
1086 ,p_action_information12 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_o_calc_contribution,0)))
1087 ,p_action_information13 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eWitholding_Tax,0)))
1088 ,p_action_information14 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_contribution_basis,0)))
1089 ,p_action_information15 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_contribution_basis,0)))
1090 ,p_action_information16 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_rate,0))
1091 ,p_action_information17 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_rate,0))
1092 ,p_action_information18 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eu_calc_contribution,0)))
1093 ,p_action_information19 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_eo_calc_contribution ,0)))
1094 ,p_action_information20 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_contribution_basis,0)))
1095 ,p_action_information21 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_calc_contribution,0)))
1096 ,p_action_information22 => FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_spr_rate,0))
1097 ,p_action_information23 => nvl(l_fe_fma_calc_contribution ,0)/ nvl(l_fe_fm_amount,0)
1098 ,p_action_information24 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_fm_amount,0)))
1099 ,p_action_information25 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_fe_fma_calc_contribution ,0)))
1100 ,p_action_information26 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_contri_el,0)))
1101 ,p_action_information27 => ROUND(FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_emp_contri_el_bimonth,0)))
1102 ,p_action_information28 => null
1103 ,p_action_information29 => null
1104 ,p_action_information30 => null );
1105
1106 IF g_debug THEN
1107 hr_utility.set_location(' Inside Procedure RANGE_CODE',60);
1108 END IF;
1109
1110
1111 l_municipal_no:=NULL;
1112
1113
1114 END IF;
1115
1116 END LOOP;
1117
1118
1119
1120 END IF;
1121
1122 END IF;
1123
1124 IF g_debug THEN
1125 hr_utility.set_location(' Leaving Procedure RANGE_CODE',70);
1126 END IF;
1127
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 -- Return cursor that selects no rows
1131 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1132 fnd_file.put_line(fnd_file.log,'Error in EC 1'||substr(sqlerrm , 1, 30));
1133
1134 END RANGE_CODE;
1135
1136 /* ASSIGNMENT ACTION CODE */
1137 PROCEDURE ASSIGNMENT_ACTION_CODE
1138 (p_payroll_action_id IN NUMBER
1139 ,p_start_person IN NUMBER
1140 ,p_end_person IN NUMBER
1141 ,p_chunk IN NUMBER)
1142 IS
1143 BEGIN
1144 IF g_debug THEN
1145 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',80);
1146 END IF;
1147
1148 IF g_debug THEN
1149 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',90);
1150 END IF;
1151
1152
1153 END ASSIGNMENT_ACTION_CODE;
1154
1155
1156 /* INITIALIZATION CODE */
1157 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
1158 IS
1159
1160 BEGIN
1161 IF g_debug THEN
1162 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',100);
1163 END IF;
1164
1165
1166 IF g_debug THEN
1167 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',110);
1168 END IF;
1169
1170 EXCEPTION WHEN OTHERS THEN
1171 g_err_num := SQLCODE;
1172 IF g_debug THEN
1173 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',120);
1174 END IF;
1175 END INITIALIZATION_CODE;
1176
1177 /* ARCHIVE CODE */
1178 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1179 ,p_effective_date IN DATE)
1180 IS
1181
1182 BEGIN
1183 IF g_debug THEN
1184 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',130);
1185 END IF;
1186
1187 IF g_debug THEN
1188 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',140);
1189 END IF;
1190
1191 END ARCHIVE_CODE;
1192 END PAY_NO_ARCHIVE_RSEA;