[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_ARC_RSEA_07
Source
1 PACKAGE BODY PAY_NO_ARC_RSEA_07 AS
2 /* $Header: pynorse7.pkb 120.1 2007/08/20 09:23:18 kseth noship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 -- Data types.
6 -- -----------------------------------------------------------------------------
7 TYPE t_xml_element_rec IS RECORD
8 (tagname VARCHAR2(100)
9 ,tagvalue VARCHAR2(500)
10 ,tagtype VARCHAR2(1)
11 ,tagattrb VARCHAR2(100));
12 --
13 TYPE t_xml_element_table IS TABLE OF t_xml_element_rec INDEX BY BINARY_INTEGER;
14 --
15 -- -----------------------------------------------------------------------------
16 -- Global variables.
17 -- -----------------------------------------------------------------------------
18 --
19 g_xml_element_table t_xml_element_table;
20 g_debug boolean := hr_utility.debug_enabled;
21 g_package VARCHAR2(33) := ' PAY_NO_ARC_RSEA_07.';
22 g_err_num NUMBER;
23 g_errm VARCHAR2(150);
24 --
25 -- -----------------------------------------------------------------------------
26 -- Get the correct characterset for XML generation
27 -- -----------------------------------------------------------------------------
28 --
29 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
30 CURSOR csr_get_iana_charset IS
31 SELECT tag
32 FROM fnd_lookup_values
33 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
34 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
35 INSTR(USERENV('LANGUAGE'), '.') + 1)
36 AND language = 'US';
37 --
38 lv_iana_charset fnd_lookup_values.tag%type;
39 BEGIN
40 OPEN csr_get_iana_charset;
41 FETCH csr_get_iana_charset INTO lv_iana_charset;
42 CLOSE csr_get_iana_charset;
43 RETURN (lv_iana_charset);
44 END get_IANA_charset;
45 --
46 --
47 -- -----------------------------------------------------------------------------
48 -- Takes XML element from a table and puts them into a CLOB.
49 -- -----------------------------------------------------------------------------
50 --
51 PROCEDURE write_to_clob
52 (p_clob OUT NOCOPY CLOB) IS
53 --
54 l_xml_element_template0 VARCHAR2(20) := '<TAG>VALUE</TAG>';
55 l_xml_element_template1 VARCHAR2(30) := '<TAG><![CDATA[VALUE]]></TAG>';
56 l_xml_element_template2 VARCHAR2(10) := '<TAG>';
57 l_xml_element_template3 VARCHAR2(10) := '</TAG>';
58 l_str1 VARCHAR2(80) ;
59 l_xml_element VARCHAR2(800);
60 l_clob CLOB;
61 --
62 BEGIN
63 --
64 -- l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT> <EOY>';
65 l_str1 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>';
66
67 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
68 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
69 --
70 dbms_lob.writeappend(l_clob, LENGTH(l_str1), l_str1);
71 --
72 IF g_xml_element_table.COUNT > 0 THEN
73 --
74 FOR table_counter IN g_xml_element_table.FIRST .. g_xml_element_table.LAST LOOP
75 --
76 IF g_xml_element_table(table_counter).tagvalue = '_START_' THEN
77 l_xml_element := '<' || g_xml_element_table(table_counter).tagname || '>';
78 ELSIF g_xml_element_table(table_counter).tagvalue = '_END_' THEN
79 l_xml_element := '</' || g_xml_element_table(table_counter).tagname || '>';
80 ELSIF g_xml_element_table(table_counter).tagtype IS NULL THEN
81 --
82 IF g_xml_element_table(table_counter).tagvalue IS NULL THEN
83 --
84 l_xml_element := '<' || g_xml_element_table(table_counter).tagname || '/>';
85 ELSE
86 l_xml_element := '<' || g_xml_element_table(table_counter).tagname ||
87 '><![CDATA[' || g_xml_element_table(table_counter).tagvalue ||
88 ']]></' || g_xml_element_table(table_counter).tagname || '>';
89 END IF;
90 ELSIF g_xml_element_table(table_counter).tagtype = 'A' THEN
91 l_xml_element := '<' || g_xml_element_table(table_counter).tagname ||
92 '>' || g_xml_element_table(table_counter).tagvalue ||
93 '</' || g_xml_element_table(table_counter).tagname || '>';
94 END IF;
95 --
96 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
97 --
98 END LOOP;
99 --
100 END IF;
101 --
102 p_clob := l_clob;
103 --
104 EXCEPTION
105 WHEN OTHERS THEN
106 --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
107 hr_utility.set_location(sqlerrm(sqlcode),110);
108 --
109 END write_to_clob;
110 --
111 --
112 -- -----------------------------------------------------------------------------
113 -- Takes XML element from a table and puts them into a CLOB.
114 -- -----------------------------------------------------------------------------
115 --
116 PROCEDURE write_to_clob_for_xml
117 (p_clob OUT NOCOPY CLOB) IS
118 --
119 l_str1 VARCHAR2(80) ;
120 l_xml_element VARCHAR2(800);
121 l_clob CLOB;
122 EOL VARCHAR2(5);
123 --
124 BEGIN
125 --
126 SELECT
127 fnd_global.local_chr(13) || fnd_global.local_chr(10)
128 INTO EOL
129 FROM dual;
130 --
131 -- l_str1 := '<?xml version="1.0" encoding="UTF-8"?> <ROOT> <EOY>';
132 l_str1 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>';
133
134 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
135 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
136 --
137 dbms_lob.writeappend(l_clob, LENGTH(l_str1), l_str1);
138 --
139 IF g_xml_element_table.COUNT > 0 THEN
140 --
141 FOR table_counter IN g_xml_element_table.FIRST .. g_xml_element_table.LAST LOOP
142 --
143 IF g_xml_element_table(table_counter).tagvalue = '_START_' THEN
144 l_xml_element := '<' || g_xml_element_table(table_counter).tagname ||
145 ' ' || g_xml_element_table(table_counter).tagattrb || '>' || EOL;
146 ELSIF g_xml_element_table(table_counter).tagvalue = '_END_' THEN
147 l_xml_element := '</' || g_xml_element_table(table_counter).tagname || '>' || EOL;
148 ELSIF g_xml_element_table(table_counter).tagattrb IS NOT NULL THEN
149 l_xml_element := '<' || g_xml_element_table(table_counter).tagname || ' '
150 || g_xml_element_table(table_counter).tagattrb || '>'
151 || g_xml_element_table(table_counter).tagvalue ||
152 '</' || g_xml_element_table(table_counter).tagname || '>' || EOL;
153 END IF;
154 --
155 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
156 --
157 END LOOP;
158 --
159 END IF;
160 --
161 p_clob := l_clob;
162 --
163 EXCEPTION
164 WHEN OTHERS THEN
165 --Fnd_file.put_line(FND_FILE.LOG,'## SQLERR ' || sqlerrm(sqlcode));
166 hr_utility.set_location(sqlerrm(sqlcode),110);
167 --
168 END write_to_clob_for_xml;
169 --
170 -- -----------------------------------------------------------------------------
171 -- Function to get defined balance id
172 -- -----------------------------------------------------------------------------
173 --
174 FUNCTION get_defined_balance_id
175 (p_balance_name IN VARCHAR2
176 ,p_dbi_suffix IN VARCHAR2 ) RETURN NUMBER IS
177 --
178 l_defined_balance_id NUMBER;
179 --
180 BEGIN
181 --
182 SELECT pdb.defined_balance_id
183 INTO l_defined_balance_id
184 FROM pay_defined_balances pdb
185 ,pay_balance_types pbt
186 ,pay_balance_dimensions pbd
187 WHERE pbd.database_item_suffix = p_dbi_suffix
188 AND pbd.legislation_code = 'NO'
189 AND pbt.balance_name = p_balance_name
190 AND pbt.legislation_code = 'NO'
191 AND pdb.balance_type_id = pbt.balance_type_id
192 AND pdb.balance_dimension_id = pbd.balance_dimension_id
193 AND pdb.legislation_code = 'NO';
194 --
195 l_defined_balance_id := NVL(l_defined_balance_id,0);
196 RETURN l_defined_balance_id ;
197 --
198 EXCEPTION
199 WHEN OTHERS THEN
200 Return 0;
201 END get_defined_balance_id ;
202 --
203 -- -----------------------------------------------------------------------------
204 -- GET PARAMETER
205 -- -----------------------------------------------------------------------------
206 --
207 FUNCTION GET_PARAMETER(
208 p_parameter_string IN VARCHAR2
209 ,p_token IN VARCHAR2
210 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
211 IS
212 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
213 l_start_pos NUMBER;
214 l_delimiter VARCHAR2(1):=' ';
215 l_proc VARCHAR2(40):= g_package||' get parameter ';
216 BEGIN
217 --
218 IF g_debug THEN
219 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
220 END IF;
221 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
222 --
223 IF l_start_pos = 0 THEN
224 l_delimiter := '|';
225 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
226 END IF;
227 --
228 IF l_start_pos <> 0 THEN
229 l_start_pos := l_start_pos + length(p_token||'=');
230 l_parameter := substr(p_parameter_string, l_start_pos,
231 instr(p_parameter_string||' ',l_delimiter,l_start_pos) - l_start_pos);
232 IF p_segment_number IS NOT NULL THEN
233 l_parameter := ':'||l_parameter||':';
234 l_parameter := substr(l_parameter, instr(l_parameter,':',1,p_segment_number)+1,
235 instr(l_parameter,':',1,p_segment_number+1) -1 - instr(l_parameter,':',1,p_segment_number));
236 END IF;
237 END IF;
238 --
239 IF g_debug THEN
240 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
241 END IF;
242 RETURN l_parameter;
243 END;
244 --
245 -- -----------------------------------------------------------------------------
246 -- GET ALL PARAMETERS
247 -- -----------------------------------------------------------------------------
248 --
249 PROCEDURE GET_ALL_PARAMETERS(p_payroll_action_id IN NUMBER
250 ,p_business_group_id OUT NOCOPY NUMBER
251 ,p_legal_employer_id OUT NOCOPY NUMBER
252 ,p_local_unit_id OUT NOCOPY NUMBER
253 ,p_effective_date OUT NOCOPY DATE
254 ,p_archive OUT NOCOPY VARCHAR2
255 ) IS
256
257 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
258 SELECT PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
259 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
260 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'ARCHIVE')
261 ,effective_date
262 ,business_group_id
263 FROM pay_payroll_actions
264 WHERE payroll_action_id = p_payroll_action_id;
265 --
266 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
267 --
268 BEGIN
269 --
270 OPEN csr_parameter_info (p_payroll_action_id);
271 FETCH csr_parameter_info
272 INTO p_legal_employer_id
273 ,p_local_unit_id
274 ,p_archive
275 ,p_effective_date
276 ,p_business_group_id;
277 CLOSE csr_parameter_info;
278 --
279 IF g_debug THEN
280 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
281 END IF;
282 END GET_ALL_PARAMETERS;
283 --
284 -- -----------------------------------------------------------------------------
285 -- RANGE CODE
286 -- -----------------------------------------------------------------------------
287 --
288 PROCEDURE RANGE_CODE(p_payroll_action_id IN NUMBER
289 ,p_sql OUT NOCOPY VARCHAR2)
290 IS
291 l_action_info_id NUMBER;
292 l_ovn NUMBER;
293 l_defined_balance_id NUMBER := 0;
294 l_count NUMBER := 0;
295 l_business_group_id NUMBER;
296 l_period VARCHAR2(2);
297 l_year VARCHAR2(4);
298 l_effective_date DATE;
299 l_legal_employer_id NUMBER ;
300 l_local_unit_id NUMBER ;
301 l_archive VARCHAR2(3);
302 l_el NUMBER;
303 l_el_b NUMBER;
304 l_el_a NUMBER;
305 l_reporting_start_date DATE;
306 l_reporting_end_date DATE;
307 l_municipal_name VARCHAR2(30);
308 l_zone NUMBER;
309
310 l_municipal_no hr_organization_information.org_information1%TYPE ;
311 l_industry_status hr_organization_information.org_information1%TYPE ;
312 l_nace_code hr_organization_information.org_information1%TYPE ;
313 l_lu_name hr_organization_units.name%TYPE ;
314 l_Witholding_Tax NUMBER;
315 l_sum_tax_value NUMBER;
316 l_tax_value NUMBER;
317 l_def_bal_id NUMBER;
318 l_fe_fm_amount NUMBER;
319 --
320 l_base_base NUMBER;
321 l_base_amt NUMBER;
322 l_reimb_base NUMBER;
323 l_reimb_amt NUMBER;
324 l_utl1_base NUMBER;
325 l_utl1_amt NUMBER;
326 l_utr1_base NUMBER;
327 l_utr1_amt NUMBER;
328 l_utl2_base NUMBER;
329 l_utl2_amt NUMBER;
330 l_pension_base NUMBER;
331 --
332 TYPE municipaldata IS RECORD(municipalcode VARCHAR2(10));
333 TYPE tmunicipaldata IS TABLE OF municipaldata INDEX BY BINARY_INTEGER;
334 --
335 gmunicipaldata tmunicipaldata ;
336 --
337 l_counter NUMBER;
338 l_status NUMBER;
339 --
340 Cursor csr_LU_Details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE) IS
341 SELECT o1.name lu_name
342 ,hoi2.org_information4 industry_status
343 ,hoi2.org_information2 nace_code
344 ,hoi2.org_information1 org_num
345 ,hoi2.org_information6 municipal_no
346 FROM hr_organization_units o1
347 ,hr_organization_information hoi1
348 ,hr_organization_information hoi2
349 WHERE o1.business_group_id = l_business_group_id
350 AND hoi1.organization_id = o1.organization_id
351 AND hoi1.organization_id = csr_v_local_unit_id
352 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
353 AND hoi1.org_information_context = 'CLASS'
354 AND o1.organization_id = hoi2.organization_id
355 AND hoi2.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
356 --
357 rg_LU_Details csr_LU_Details%rowtype;
358 --
359 Cursor csr_LE_Details (csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
360 SELECT o1.name le_name
361 ,hoi2.org_information1 org_number
362 ,hoi2.org_information2 municipal_no
363 ,hoi2.org_information3 industry_status
364 ,hoi2.org_information4 nace_code
365 -- ,hoi2.org_information5 tax_off
366 FROM hr_organization_units o1
367 ,hr_organization_information hoi1
368 ,hr_organization_information hoi2
369 WHERE o1.business_group_id = l_business_group_id
370 AND hoi1.organization_id = o1.organization_id
371 AND hoi1.organization_id = csr_v_legal_emp_id
372 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
373 AND hoi1.org_information_context = 'CLASS'
374 AND o1.organization_id = hoi2.organization_id
375 AND hoi2.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS' ;
376 --
377 rg_LE_Details csr_LE_Details%rowtype;
378 --
379 Cursor csr_LE_Contact ( csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
380 SELECT hoi2.org_information2 email
381 ,hoi3.org_information2 phone
382 FROM hr_organization_units o1
383 ,hr_organization_information hoi1
384 ,hr_organization_information hoi2
385 ,hr_organization_information hoi3
386 WHERE o1.business_group_id = l_business_group_id
387 AND hoi1.organization_id = o1.organization_id
388 AND hoi1.organization_id = csr_v_legal_emp_id
389 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
390 AND hoi1.org_information_context = 'CLASS'
391 AND hoi2.organization_id (+) = o1.organization_id
392 AND hoi2.org_information_context(+) = 'ORG_CONTACT_DETAILS'
393 AND hoi2.org_information1(+) = 'EMAIL'
394 AND hoi3.organization_id (+) = o1.organization_id
395 AND hoi3.org_information_context(+) = 'ORG_CONTACT_DETAILS'
396 AND hoi3.org_information1(+) = 'PHONE';
397 --
398 rg_LE_Contact csr_LE_Contact%rowtype;
399 --
400 Cursor csr_LE_addr ( csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
401 SELECT hoi1.address_line_1 address_line_1
402 ,hoi1.address_line_2 address_line_2
403 ,hoi1.address_line_3 address_line_3
404 ,hoi1.postal_code postal_code
405 ,SUBSTR(hlu.meaning, INSTR(hlu.meaning,' ', 1,1), LENGTH(hlu.meaning)-(INSTR(hlu.meaning,' ', 1,1)-1)) postal_office
406 FROM hr_organization_units o1
407 ,hr_locations hoi1
408 ,hr_organization_information hoi2
409 ,hr_lookups hlu
410 WHERE o1.business_group_id = l_business_group_id
411 AND hoi1.location_id = o1.location_id
412 AND hoi2.organization_id = o1.organization_id
413 AND hoi2.organization_id = csr_v_legal_emp_id
414 AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
415 AND hoi2.org_information_context = 'CLASS'
416 AND hlu.lookup_type = 'NO_POSTAL_CODE'
417 AND hlu.enabled_flag = 'Y'
418 AND hlu.lookup_code = hoi1.POSTAL_CODE;
419 --
420 rg_LE_addr csr_LE_addr%rowtype;
421 --
422 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
423 p_legal_employer_id NUMBER,
424 l_start_date DATE,
425 l_end_date DATE) IS
426 SELECT DISTINCT act.assignment_id assignment_id
427 FROM pay_payroll_actions ppa
428 ,pay_payroll_actions appa
429 ,pay_payroll_actions appa2
430 ,pay_assignment_actions act
431 ,pay_assignment_actions act1
432 ,pay_action_interlocks pai
433 ,per_all_assignments_f as1
434 ,hr_soft_coding_keyflex hsck
435 WHERE ppa.payroll_action_id = p_payroll_action_id
436 AND appa.effective_date BETWEEN l_start_date AND l_end_date
437 AND appa.action_type IN ('R','Q')
438 -- Payroll Run or Quickpay Run
439 AND act.payroll_action_id = appa.payroll_action_id
440 AND act.source_action_id IS NULL -- Master Action
441 AND as1.assignment_id = act.assignment_id
442 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
443 AND act.action_status = 'C' -- Completed
444 AND act.assignment_action_id = pai.locked_action_id
445 AND act1.assignment_action_id = pai.locking_action_id
446 AND act1.action_status = 'C' -- Completed
447 AND act1.payroll_action_id = appa2.payroll_action_id
448 AND appa2.action_type IN ('P','U')
449 AND appa2.effective_date BETWEEN l_start_date AND l_end_date
450 -- Prepayments or Quickpay Prepayments
451 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
452 AND act.TAX_UNIT_ID = p_legal_employer_id
453 AND hsck.SOFT_CODING_KEYFLEX_ID = as1.SOFT_CODING_KEYFLEX_ID
454 AND EXISTS (SELECT hoi1.organization_id
455 FROM hr_organization_units o1
456 ,hr_organization_information hoi1
457 ,hr_organization_information hoi2
458 ,hr_organization_information hoi3
459 ,hr_organization_information hoi4
460 WHERE hoi1.organization_id = o1.organization_id
461 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
462 AND hoi1.org_information_context = 'CLASS'
463 AND o1.organization_id = hoi2.org_information1
464 AND hoi2.org_information_context ='NO_LOCAL_UNITS'
465 AND hoi2.organization_id = hoi3.organization_id
466 AND hoi3.org_information_context ='CLASS'
467 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
468 aND hoi3.organization_id = p_legal_employer_id
469 AND hoi1.organization_id = hoi4.organization_id
470 AND hoi4.org_information_context ='NO_LOCAL_UNIT_DETAILS'
471 AND hoi4.org_information5 = 'N'
472 AND to_char(hoi1.organization_id) = hsck.segment2 );
473 --
474 CURSOR csr_prepaid_assignments_lu(p_payroll_action_id NUMBER
475 ,p_legal_employer_id NUMBER
476 ,p_local_unit_id NUMBER
477 ,l_start_date DATE
478 ,l_end_date DATE) IS
479 SELECT DISTINCT act.assignment_id assignment_id
480 FROM pay_payroll_actions ppa
481 ,pay_payroll_actions appa
482 ,pay_payroll_actions appa2
483 ,pay_assignment_actions act
484 ,pay_assignment_actions act1
485 ,pay_action_interlocks pai
486 ,per_all_assignments_f as1
487 ,hr_soft_coding_keyflex hsck
488 WHERE ppa.payroll_action_id = p_payroll_action_id
489 AND appa.effective_date BETWEEN l_start_date AND l_end_date
490 AND appa.action_type IN ('R','Q')
491 -- Payroll Run or Quickpay Run
492 AND act.payroll_action_id = appa.payroll_action_id
493 AND act.source_action_id IS NULL -- Master Action
494 AND as1.assignment_id = act.assignment_id
495 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
496 AND act.action_status = 'C' -- Completed
497 AND act.assignment_action_id = pai.locked_action_id
498 AND act1.assignment_action_id = pai.locking_action_id
499 AND act1.action_status = 'C' -- Completed
500 AND act1.payroll_action_id = appa2.payroll_action_id
501 AND appa2.action_type IN ('P','U')
502 AND appa2.effective_date BETWEEN l_start_date AND l_end_date
503 -- Prepayments or Quickpay Prepayments
504 AND hsck.soft_coding_keyflex_id = as1.soft_coding_keyflex_id
505 AND hsck.segment2 = to_char(p_local_unit_id)
506 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
507 AND act.TAX_UNIT_ID = p_legal_employer_id ;
508 --
509 CURSOR csr_get_mun_num(p_assignment_id NUMBER
510 ,p_effective_date DATE) IS
511 SELECT eev1.screen_entry_value screen_entry_value
512 FROM per_all_assignments_f asg1
513 ,per_all_assignments_f asg2
514 ,per_all_people_f per
515 ,pay_element_links_f el
516 ,pay_element_types_f et
517 ,pay_input_values_f iv1
518 ,pay_element_entries_f ee
519 ,pay_element_entry_values_f eev1
520 WHERE asg1.assignment_id = p_assignment_id
521 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
522 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
523 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
524 AND per.person_id = asg1.person_id
525 AND asg2.person_id = per.person_id
526 AND asg2.primary_flag = 'Y'
527 AND et.element_name = 'Tax Card'
528 AND et.legislation_code = 'NO'
529 AND iv1.element_type_id = et.element_type_id
530 AND iv1.name = 'Tax Municipality'
531 AND el.business_group_id = per.business_group_id
532 AND el.element_type_id = et.element_type_id
533 AND ee.assignment_id = asg2.assignment_id
534 AND ee.element_link_id = el.element_link_id
535 AND eev1.element_entry_id = ee.element_entry_id
536 AND eev1.input_value_id = iv1.input_value_id
537 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
538 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
539 --
540 CURSOR csr_get_mun_dtls(p_municipal_no VARCHAR2, l_effective_date DATE) IS
541 SELECT hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'ZONE') zone
542 ,hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
543 hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'MAPPING_ID')) municipal_name
544 FROM pay_user_tables t
545 ,pay_user_rows_f r
546 WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
547 AND t.legislation_code = 'NO'
548 AND r.user_table_id = t.user_table_id
549 AND r.row_low_range_or_name = p_municipal_no
550 AND l_effective_date BETWEEN r.effective_start_date AND r.effective_end_date;
551 --
552 rg_get_mun_dtls csr_get_mun_dtls%ROWTYPE;
553 --
554 CURSOR csr_lu_dtls(p_legal_employer_id NUMBER) IS
555 SELECT hoi1.organization_id lu_id
556 FROM hr_organization_units o1
557 ,hr_organization_information hoi1
558 ,hr_organization_information hoi2
559 ,hr_organization_information hoi3
560 ,hr_organization_information hoi4
561 WHERE hoi1.organization_id = o1.organization_id
562 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
563 AND hoi1.org_information_context = 'CLASS'
564 AND o1.organization_id = hoi2.org_information1
565 AND hoi2.org_information_context ='NO_LOCAL_UNITS'
566 AND hoi2.organization_id = hoi3.organization_id
567 AND hoi3.org_information_context = 'CLASS'
568 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
569 AND hoi3.organization_id = p_legal_employer_id
570 AND hoi1.organization_id = hoi4.organization_id
571 AND hoi4.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
572 AND hoi4.org_information5 = 'N';
573 --
574 CURSOR csr_Local_Unit_EA(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
575 ,p_date_earned DATE) IS
576 SELECT to_number(hoi2.org_information4)
577 FROM hr_organization_units o1
578 ,hr_organization_information hoi1
579 ,hr_organization_information hoi2
580 WHERE o1.business_group_id = l_business_group_id
581 AND hoi1.organization_id = o1.organization_id
582 AND hoi1.organization_id = csr_v_local_unit_id
583 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
584 AND hoi1.org_information_context = 'CLASS'
585 AND o1.organization_id = hoi2.organization_id
586 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
587 AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
588 AND fnd_date.canonical_to_date(hoi2.org_information3);
589 --
590 Cursor csr_Legal_Emp_EA(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
591 ,p_date_earned DATE) IS
592 SELECT to_number(hoi2.org_information4)
593 FROM hr_organization_units o1
594 ,hr_organization_information hoi1
595 ,hr_organization_information hoi2
596 WHERE o1.business_group_id = l_business_group_id
597 AND hoi1.organization_id = o1.organization_id
598 AND hoi1.organization_id = csr_v_legal_emp_id
599 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
600 AND hoi1.org_information_context = 'CLASS'
601 AND o1.organization_id = hoi2.organization_id
602 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
603 AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
604 AND fnd_date.canonical_to_date(hoi2.org_information3);
605 --
606 CURSOR csr_Local_Unit_EL(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
607 ,p_date_earned DATE) IS
608 SELECT SUM(hoi2.org_information1) exempt_limit
609 ,SUM(hoi2.org_information4) economic_aid
610 FROM hr_organization_units o1
611 ,hr_organization_information hoi1
612 ,hr_organization_information hoi2
613 WHERE o1.business_group_id = l_business_group_id
614 AND hoi1.organization_id = o1.organization_id
615 AND hoi1.organization_id = csr_v_local_unit_id
616 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
617 AND hoi1.org_information_context = 'CLASS'
618 AND o1.organization_id = hoi2.organization_id
619 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
620 AND trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
621 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
622 --
623 rg_Local_Unit_EL csr_Local_Unit_EL%ROWTYPE;
624 --
625 Cursor csr_Legal_Emp_EL(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
626 ,p_date_earned DATE) IS
627 SELECT SUM(hoi2.org_information1) exempt_limit
628 ,SUM(hoi2.org_information4) economic_aid
629 FROM hr_organization_units o1
630 ,hr_organization_information hoi1
631 ,hr_organization_information hoi2
632 WHERE o1.business_group_id = l_business_group_id
633 AND hoi1.organization_id = o1.organization_id
634 AND hoi1.organization_id = csr_v_legal_emp_id
635 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
636 AND hoi1.org_information_context = 'CLASS'
637 AND o1.organization_id = hoi2.organization_id
638 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
639 AND trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
640 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
641 --
642 rg_Legal_Emp_EL csr_Legal_Emp_EL%ROWTYPE;
643 --
644 CURSOR csr_Local_Unit_EL_after(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
645 ,p_date_earned DATE) IS
646 SELECT SUM(hoi2.org_information4) economic_aid
647 FROM hr_organization_units o1
648 ,hr_organization_information hoi1
649 ,hr_organization_information hoi2
650 WHERE o1.business_group_id = l_business_group_id
651 AND hoi1.organization_id = o1.organization_id
652 AND hoi1.organization_id = csr_v_local_unit_id
653 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
654 AND hoi1.org_information_context = 'CLASS'
655 AND o1.organization_id = hoi2.organization_id
656 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
657 AND p_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
658 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
659 --
660 rg_Local_Unit_EL_after csr_Local_Unit_EL_after%ROWTYPE;
661 --
662 Cursor csr_Legal_Emp_EL_after(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
663 ,p_date_earned DATE) IS
664 SELECT SUM(hoi2.org_information4) economic_aid
665 FROM hr_organization_units o1
666 ,hr_organization_information hoi1
667 ,hr_organization_information hoi2
668 WHERE o1.business_group_id = l_business_group_id
669 AND hoi1.organization_id = o1.organization_id
670 AND hoi1.organization_id = csr_v_legal_emp_id
671 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
672 AND hoi1.org_information_context = 'CLASS'
673 AND o1.organization_id = hoi2.organization_id
674 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
675 AND p_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
676 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
677 --
678 rg_Legal_Emp_EL_after csr_Legal_Emp_EL_after%ROWTYPE;
679 --
680 CURSOR csr_global_value (p_global_name VARCHAR2 , p_date_earned DATE) IS
681 SELECT global_value
682 FROM ff_globals_f
683 WHERE global_name = p_global_name
684 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
685 --
686 BEGIN
687 --
688 g_debug:=true;
689 --
690 IF g_debug THEN
691 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
692 END IF;
693 --
694 p_sql :='SELECT DISTINCT person_id
695 FROM per_people_f ppf
696 ,pay_payroll_actions ppa
697 WHERE ppa.payroll_action_id = :payroll_action_id
698 AND ppa.business_group_id = ppf.business_group_id
699 ORDER BY ppf.person_id';
700 --
701 pay_no_arc_rsea_07.get_all_parameters(p_payroll_action_id
702 ,l_business_group_id
703 ,l_legal_employer_id
704 ,l_local_unit_id
705 ,l_effective_date
706 ,l_archive);
707 --
708 l_period:= to_char(ceil(to_number(to_char(l_effective_date,'MM'))/ 2));
709 l_year:=to_char(l_effective_date,'YYYY');
710 l_reporting_end_date := LAST_DAY(TO_DATE(LPAD(l_period*2,2,'0')||l_year,'MMYYYY'));
711 l_reporting_start_date :=ADD_MONTHS( l_reporting_end_date , -2 ) + 1;
712 --
713 IF l_archive = 'Y' THEN
714 --
715 SELECT count(*) INTO l_count
716 FROM pay_action_information
717 WHERE action_context_id = p_payroll_action_id
718 AND action_context_type = 'PA'
719 AND action_information_category = 'EMEA REPORT INFORMATION'
720 AND action_information1 = 'PYNORSEA';
721 --
722 IF l_count < 1 then
723 /* Pick up the details belonging to Legal Employer */
724 OPEN csr_LE_Details(l_legal_employer_id);
725 FETCH csr_LE_Details INTO rg_LE_Details;
726 CLOSE csr_LE_Details;
727 --
728 l_industry_status:= rg_LE_Details.industry_status ;
729 l_nace_code := rg_LE_Details.nace_code ;
730 --
731 IF l_local_unit_id IS NOT NULL THEN
732 /* Pick up the details belonging to Local Unit */
733 OPEN csr_LU_Details( l_local_unit_id);
734 FETCH csr_LU_Details INTO rg_LU_Details;
735 CLOSE csr_LU_Details;
736 --
737 l_lu_name := rg_LU_Details.lu_name;
738 l_nace_code := rg_LU_Details.nace_code;
739 l_industry_status := rg_LU_Details.industry_status;
740 --
741 END IF ;
742 /* Pick up the contact details belonging to Legal Employer */
743 OPEN csr_LE_contact(l_legal_employer_id);
744 FETCH csr_LE_contact INTO rg_LE_contact;
745 CLOSE csr_LE_contact;
746 /* Pick up the Address details belonging to Legal Employer */
747 OPEN csr_LE_addr(l_legal_employer_id);
748 FETCH csr_LE_addr INTO rg_LE_addr;
749 CLOSE csr_LE_addr;
750 --
751 pay_balance_pkg.set_context('TAX_UNIT_ID', l_legal_employer_id);
752 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_reporting_end_date));
753 --
754 IF l_local_unit_id IS NOT NULL THEN
755 /* Pick up the Exemption Limit details belonging to Local Unit*/
756 pay_balance_pkg.set_context('LOCAL_UNIT_ID', l_local_unit_id);
757 --
758 OPEN csr_Local_Unit_EA(l_local_unit_id, l_reporting_end_date);
759 FETCH csr_Local_Unit_EA INTO l_el;
760 CLOSE csr_Local_Unit_EA;
761 --
762 OPEN csr_Local_Unit_EL(l_local_unit_id, l_reporting_end_date);
763 FETCH csr_Local_Unit_EL INTO rg_Local_Unit_EL;
764 CLOSE csr_Local_Unit_EL;
765 --
766 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_LU_YTD') ;
767 l_el_a := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
768 l_el_a := nvl(rg_Local_Unit_EL.exempt_limit,0) - nvl(rg_Local_Unit_EL.economic_aid,0) - nvl(l_el_a,0);
769 --
770 OPEN csr_Local_Unit_EL_after( l_local_unit_id , l_reporting_end_date);
771 FETCH csr_Local_Unit_EL_after INTO rg_Local_Unit_EL_after;
772 CLOSE csr_Local_Unit_EL_after;
773 --
774 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_LU_BIMONTH') ;
775 l_el_b := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
776 l_el_b := l_el_a + nvl(l_el_b,0) + nvl(rg_Local_Unit_EL_after.economic_aid,0);
777 --
778 ELSE
779 /* Pick up the Exemption Limit details belonging to Employer*/
780 OPEN csr_Legal_Emp_EA( l_legal_employer_id , l_reporting_end_date);
781 FETCH csr_Legal_Emp_EA INTO l_el;
782 CLOSE csr_Legal_Emp_EA;
783 --
784 OPEN csr_Legal_Emp_EL( l_legal_employer_id , l_reporting_end_date);
785 FETCH csr_Legal_Emp_EL INTO rg_Legal_Emp_EL;
786 CLOSE csr_Legal_Emp_EL;
787 --
788 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_YTD') ;
789 l_el_a := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
790 l_el_a := nvl(rg_Legal_Emp_EL.exempt_limit,0) - nvl(rg_Legal_Emp_EL.economic_aid,0) - nvl(l_el_a,0);
791 --
792 OPEN csr_Legal_Emp_EL_after( l_legal_employer_id , l_reporting_end_date);
793 FETCH csr_Legal_Emp_EL_after INTO rg_Legal_Emp_EL_after;
794 CLOSE csr_Legal_Emp_EL_after;
795 --
796 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_BIMONTH') ;
797 l_el_b := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
798 l_el_b := l_el_a + nvl(l_el_b,0) + nvl(rg_Legal_Emp_EL_after.economic_aid,0);
799 --
800 END IF;
801 --
802 l_el_a := greatest(l_el_a, 0);
803 l_el_b := greatest(l_el_b, 0);
804 --
805 /* Inserting header details belonging to Employer*/
806 pay_action_information_api.create_action_information (
807 p_action_information_id => l_action_info_id
808 ,p_action_context_id => p_payroll_action_id
809 ,p_action_context_type => 'PA'
810 ,p_object_version_number => l_ovn
811 ,p_effective_date => l_effective_date
812 ,p_source_id => NULL
813 ,p_source_text => NULL
814 ,p_action_information_category => 'EMEA REPORT INFORMATION'
815 ,p_action_information1 => 'PYNORSEA'
816 ,p_action_information2 => l_legal_employer_id
817 ,p_action_information3 => l_period || l_year
818 ,p_action_information4 => rg_LE_Details.org_number
819 ,p_action_information5 => rg_LE_Details.municipal_no
820 ,p_action_information6 => rg_LE_Details.le_name
821 ,p_action_information7 => rg_LE_addr.address_line_1
822 ,p_action_information8 => rg_LE_addr.address_line_2
823 ,p_action_information9 => rg_LE_addr.postal_code
824 ,p_action_information10 => rg_LE_addr.postal_office
825 ,p_action_information11 => rg_LE_Contact.email
826 ,p_action_information12 => rg_LE_Contact.phone
827 ,p_action_information13 => null -- for Tax unit details
828 ,p_action_information14 => null -- for Tax unit details
829 ,p_action_information15 => null -- for Tax unit details
830 ,p_action_information16 => null -- for Tax unit details
831 ,p_action_information17 => l_industry_status
832 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_el,0)) -- Other economic support (Economic Aid)
833 ,p_action_information19 => l_nace_code
834 ,p_action_information20 => rg_LE_addr.address_line_3
835 ,p_action_information21 => fnd_number.number_to_canonical(NVL(l_el_b,0)) -- remaining exemption limit prev rep term
836 ,p_action_information22 => fnd_number.number_to_canonical(NVL(l_el_a,0)) -- remaining exemption limit after rep term
837 );
838 --
839 /* Inserting the selection criteria for generating the report*/
840 pay_action_information_api.create_action_information (
841 p_action_information_id => l_action_info_id
842 ,p_action_context_id => p_payroll_action_id
843 ,p_action_context_type => 'PA'
844 ,p_object_version_number => l_ovn
845 ,p_effective_date => l_effective_date
846 ,p_source_id => NULL
847 ,p_source_text => NULL
848 ,p_action_information_category => 'EMEA REPORT DETAILS'
849 ,p_action_information1 => 'PYNORSEA'
850 ,p_action_information2 => rg_LE_Details.le_name
851 ,p_action_information3 => l_lu_name
852 ,p_action_information4 => l_period
853 ,p_action_information5 => l_year
854 );
855 --
856 IF g_debug THEN
857 hr_utility.set_location(' Inside Procedure RANGE_CODE',20);
858 END IF;
859 /* Inserting municipal codes for the Legal Employer in a PL/SQL table */
860 IF l_local_unit_id IS NULL THEN
861 --
862 l_counter := 0;
863 l_status := 0;
864 --
865 FOR prepaid_assignments_le_rec IN csr_prepaid_assignments_le(p_payroll_action_id
866 ,l_legal_employer_id
867 ,l_reporting_start_date
868 ,l_reporting_end_date)
869 LOOP
870 --
871 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id
872 ,l_reporting_start_date)
873 LOOP
874 --
875 IF l_counter > 0 THEN
876 --
877 FOR i IN 1 .. l_counter LOOP
878 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
879 l_status:= 1;
880 EXIT ;
881 END IF;
882 END LOOP;
883 --
884 END IF;
885 --
886 IF l_status= 0 THEN
887 l_counter := l_counter + 1;
888 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
889 END IF;
890 --
891 l_status := 0;
892 --
893 END LOOP;
894 --
895 l_status := 0;
896 --
897 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id
898 ,l_reporting_end_date)
899 LOOP
900 --
901 IF l_counter > 0 THEN
902 --
903 FOR i IN 1 .. l_counter LOOP
904 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
905 l_status:= 1;
906 EXIT ;
907 END IF;
908 END LOOP;
909 END IF;
910 --
911 IF l_status= 0 THEN
912 l_counter := l_counter + 1;
913 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
914 END IF;
915 --
916 l_status := 0;
917 --
918 END LOOP;
919 --
920 END LOOP;
921 --
922 ELSE -- IF LU is specified in parameters
923 /* Inserting municipal codes for the Local Unit in a PL/SQL table */
924 l_counter := 0;
925 l_status := 0;
926 --
927 FOR prepaid_assignments_lu_rec IN csr_prepaid_assignments_lu(p_payroll_action_id
928 ,l_legal_employer_id
929 ,l_local_unit_id
930 ,l_reporting_start_date
931 ,l_reporting_END_date)
932 LOOP
933 --
934 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id
935 ,l_reporting_start_date)
936 LOOP
937 --
938 IF l_counter > 0 THEN
939 FOR i IN 1 .. l_counter LOOP
940 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
941 l_status:= 1;
942 EXIT ;
943 END IF;
944 END LOOP;
945 END IF;
946 --
947 IF l_status= 0 THEN
948 l_counter := l_counter + 1;
949 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
950 END IF;
951 --
952 l_status := 0;
953 --
954 END LOOP;
955 --
956 l_status := 0;
957 --
958 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id
959 ,l_reporting_end_date)
960 LOOP
961 IF l_counter > 0 THEN
962 FOR i IN 1 .. l_counter LOOP
963 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
964 l_status:= 1;
965 EXIT ;
966 END IF;
967 END LOOP;
968 END IF;
969 --
970 IF l_status= 0 THEN
971 l_counter := l_counter + 1;
972 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
973 END IF;
974 --
975 l_status := 0;
976 --
977 END LOOP;
978 --
979 END LOOP;
980 --
981 END IF ; -- plsql table now contains all the tax municipal codes
982 --
983 IF g_debug THEN
984 hr_utility.set_location(' Inside Procedure RANGE_CODE',40);
985 END IF;
986 --
987 -- ----------------------- --
988 -- Withholding Tax Section --
989 -- ----------------------- --
990 --
991 /* Setting contexts for balances*/
992 FOR i IN 1 .. l_counter LOOP
993 --
994 l_municipal_no:=gmunicipaldata(i).municipalcode;
995 --
996 IF l_municipal_no IS NOT NULL THEN
997 --
998 pay_balance_pkg.set_context('SOURCE_TEXT2', l_municipal_no);
999 --
1000 l_sum_tax_value := 0;
1001 /* Setting municipality details for balances*/
1002 OPEN csr_get_mun_dtls(l_municipal_no, l_reporting_end_date);
1003 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1004 CLOSE csr_get_mun_dtls;
1005 --
1006 l_municipal_name := rg_get_mun_dtls.municipal_name;
1007 /* Fetching balance values related to employer contributions report*/
1008 IF l_local_unit_id IS NULL THEN
1009 FOR lu_dtls_rec IN csr_lu_dtls(l_legal_employer_id)
1010 LOOP
1011 pay_balance_pkg.set_context('LOCAL_UNIT_ID',lu_dtls_rec.lu_id);
1012 l_tax_value := 0;
1013 l_def_bal_id := get_defined_balance_id('Tax','_TU_MC_LU_BIMONTH');
1014 l_tax_value := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1015 l_sum_tax_value := l_sum_tax_value + l_tax_value;
1016 END LOOP;
1017 ELSE
1018 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
1019 l_tax_value := 0;
1020 l_def_bal_id := get_defined_balance_id('Tax','_TU_MC_LU_BIMONTH');
1021 l_tax_value := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1022 l_sum_tax_value := l_sum_tax_value + l_tax_value;
1023 END IF;
1024 --
1025 pay_action_information_api.create_action_information (
1026 p_action_information_id => l_action_info_id
1027 ,p_action_context_id => p_payroll_action_id
1028 ,p_action_context_type => 'PA'
1029 ,p_object_version_number => l_ovn
1030 ,p_effective_date => l_effective_date
1031 ,p_source_id => NULL
1032 ,p_source_text => NULL
1033 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1034 ,p_action_information1 => 'PYNORSEA-WT'
1035 ,p_action_information2 => l_municipal_no
1036 ,p_action_information3 => l_municipal_name
1037 ,p_action_information4 => fnd_number.number_to_canonical(NVL(l_sum_tax_value,0)) );
1038 --
1039 l_municipal_no:=NULL;
1040 --
1041 END IF;
1042 --
1043 END LOOP; -- plsql table for all tax municipal exhausted
1044 --
1045 IF g_debug THEN
1046 hr_utility.set_location(' Inside Procedure RANGE_CODE',60);
1047 END IF;
1048 --
1049 -- ----------------------------- --
1050 -- Employer Contribution Section --
1051 -- ----------------------------- --
1052 --
1053 -- Fetching the global value NO_NI_FOREIGN_MARINER_AMOUNT*/
1054 OPEN csr_global_value('NO_NI_FOREIGN_MARINER_AMOUNT' , l_reporting_end_date ) ;
1055 FETCH csr_global_value INTO l_fe_fm_amount;
1056 CLOSE csr_global_value;
1057 --
1058 IF l_local_unit_id IS NULL THEN
1059 --
1060 FOR lu_dtls_rec IN csr_lu_dtls(l_legal_employer_id)
1061 LOOP
1062 --
1063 OPEN csr_LU_Details(lu_dtls_rec.lu_id);
1064 FETCH csr_LU_Details INTO rg_LU_Details;
1065 CLOSE csr_LU_Details;
1066 --
1067 OPEN csr_get_mun_dtls(rg_LU_Details.municipal_no, l_reporting_end_date);
1068 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1069 CLOSE csr_get_mun_dtls;
1070 --
1071 l_base_base := 0;
1072 l_base_amt := 0;
1073 l_reimb_base := 0;
1074 l_reimb_amt := 0;
1075 l_utl1_base := 0;
1076 l_utl1_amt := 0;
1077 l_utr1_base := 0;
1078 l_utr1_amt := 0;
1079 l_utl2_base := 0;
1080 l_utl2_amt := 0;
1081 --
1082 pay_balance_pkg.set_context('LOCAL_UNIT_ID',lu_dtls_rec.lu_id);
1083 --
1084 -- EC Base
1085 l_def_bal_id := get_defined_balance_id('Employer Contribution Base','_TU_LU_BIMONTH') ;
1086 l_base_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1087 --
1088 l_def_bal_id := get_defined_balance_id('Employer Contribution','_TU_LU_BIMONTH') ;
1089 l_base_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1090 --
1091 -- Reimbursed from SS
1092 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed Base','_TU_LU_BIMONTH') ;
1093 l_reimb_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1094 --
1095 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1096 l_reimb_base := l_reimb_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1097 --
1098 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1099 l_reimb_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1100 --
1101 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed','_TU_LU_BIMONTH') ;
1102 l_reimb_amt := l_reimb_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1103 --
1104 -- EC Base, Special EC percentage -UTL1
1105 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base','_TU_LU_BIMONTH') ;
1106 l_utl1_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1107 --
1108 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage','_TU_LU_BIMONTH') ;
1109 l_utl1_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1110 --
1111 -- Reimbursed from SS, Special EC Percentage - UTR1
1112 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimb Base','_TU_LU_BIMONTH') ;
1113 l_utr1_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1114 --
1115 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1116 l_utr1_base := l_utr1_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1117 --
1118 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1119 l_utr1_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1120 --
1121 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Benefit Reimbursed','_TU_LU_BIMONTH') ;
1122 l_utr1_amt := l_utr1_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1123 --
1124 -- EC Base Special monthly amount - UTL2
1125 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Base','_TU_LU_BIMONTH') ;
1126 l_utl2_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)/ l_fe_fm_amount;
1127 --
1128 l_def_bal_id := get_defined_balance_id('Employer Contribution Special','_TU_LU_BIMONTH') ;
1129 l_utl2_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1130 --
1131 -- EC Base Pension
1132 l_def_bal_id := get_defined_balance_id('Employers Pension Premium','_TU_LU_BIMONTH') ;
1133 l_pension_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1134 --
1135 pay_action_information_api.create_action_information (
1136 p_action_information_id => l_action_info_id
1137 ,p_action_context_id => p_payroll_action_id
1138 ,p_action_context_type => 'PA'
1139 ,p_object_version_number => l_ovn
1140 ,p_effective_date => l_effective_date
1141 ,p_source_id => NULL
1142 ,p_source_text => NULL
1143 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1144 ,p_action_information1 => 'PYNORSEA-EC'
1145 ,p_action_information2 => l_legal_employer_id
1146 ,p_action_information3 => lu_dtls_rec.lu_id
1147 ,p_action_information4 => rg_LU_Details.org_num
1148 ,p_action_information5 => rg_LU_Details.municipal_no
1149 ,p_action_information6 => rg_get_mun_dtls.municipal_name
1150 ,p_action_information7 => rg_get_mun_dtls.zone
1151 ,p_action_information8 => fnd_number.number_to_canonical(NVL(l_base_base,0))
1152 ,p_action_information9 => fnd_number.number_to_canonical(NVL(l_base_amt,0))
1153 ,p_action_information10 => fnd_number.number_to_canonical(NVL(l_reimb_base,0))
1154 ,p_action_information11 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_amt,0))
1155 ,p_action_information12 => fnd_number.number_to_canonical(NVL(l_utl1_base,0))
1156 ,p_action_information13 => fnd_number.number_to_canonical(NVL(l_utl1_amt,0))
1157 ,p_action_information14 => fnd_number.number_to_canonical(NVL(l_utr1_base,0))
1158 ,p_action_information15 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_amt,0))
1159 ,p_action_information16 => fnd_number.number_to_canonical(NVL(l_utl2_base,0))
1160 ,p_action_information17 => fnd_number.number_to_canonical(NVL(l_utl2_amt,0))
1161 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_pension_base,0))
1162 );
1163 --
1164 END LOOP;
1165 --
1166 ELSE -- LU Specified
1167 --
1168 OPEN csr_LU_Details(l_local_unit_id);
1169 FETCH csr_LU_Details INTO rg_LU_Details;
1170 CLOSE csr_LU_Details;
1171 --
1172 OPEN csr_get_mun_dtls(rg_LU_Details.municipal_no, l_reporting_end_date);
1173 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1174 CLOSE csr_get_mun_dtls;
1175 --
1176 l_base_base := 0;
1177 l_base_amt := 0;
1178 l_reimb_base := 0;
1179 l_reimb_amt := 0;
1180 l_utl1_base := 0;
1181 l_utl1_amt := 0;
1182 l_utr1_base := 0;
1183 l_utr1_amt := 0;
1184 l_utl2_base := 0;
1185 l_utl2_amt := 0;
1186 --
1187 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
1188 -- EC Base
1189 l_def_bal_id := get_defined_balance_id('Employer Contribution Base','_TU_LU_BIMONTH') ;
1190 l_base_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1191 --
1192 l_def_bal_id := get_defined_balance_id('Employer Contribution','_TU_LU_BIMONTH') ;
1193 l_base_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1194 --
1195 -- Reimbursed from SS
1196 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed Base','_TU_LU_BIMONTH') ;
1197 l_reimb_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1198 --
1199 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1200 l_reimb_base := l_reimb_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1201 --
1202 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1203 l_reimb_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1204 --
1205 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed','_TU_LU_BIMONTH') ;
1206 l_reimb_amt := l_reimb_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1207 --
1208 -- EC Base, Special EC percentage -UTL1
1209 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base','_TU_LU_BIMONTH') ;
1210 l_utl1_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1211 --
1212 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage','_TU_LU_BIMONTH') ;
1213 l_utl1_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1214 --
1215 -- Reimbursed from SS, Special EC Percentage - UTR1
1216 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimb Base','_TU_LU_BIMONTH') ;
1217 l_utr1_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1218 --
1219 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1220 l_utr1_base := l_utr1_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1221 --
1222 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1223 l_utr1_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1224 --
1225 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Benefit Reimbursed','_TU_LU_BIMONTH') ;
1226 l_utr1_amt := l_utr1_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1227 --
1228 -- EC Base Special monthly amount - UTL2
1229 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Base','_TU_LU_BIMONTH') ;
1230 l_utl2_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE) / l_fe_fm_amount;
1231 --
1232 l_def_bal_id := get_defined_balance_id('Employer Contribution Special','_TU_LU_BIMONTH') ;
1233 l_utl2_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1234 -- EC Base Pension
1235 l_def_bal_id := get_defined_balance_id('Employers Pension Premium','_TU_LU_BIMONTH') ;
1236 l_pension_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1237 --
1238 pay_action_information_api.create_action_information (
1239 p_action_information_id => l_action_info_id
1240 ,p_action_context_id => p_payroll_action_id
1241 ,p_action_context_type => 'PA'
1242 ,p_object_version_number => l_ovn
1243 ,p_effective_date => l_effective_date
1244 ,p_source_id => NULL
1245 ,p_source_text => NULL
1246 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1247 ,p_action_information1 => 'PYNORSEA-EC'
1248 ,p_action_information2 => l_legal_employer_id
1249 ,p_action_information3 => l_local_unit_id
1250 ,p_action_information4 => rg_LU_Details.org_num
1251 ,p_action_information5 => rg_LU_Details.municipal_no
1252 ,p_action_information6 => rg_get_mun_dtls.municipal_name
1253 ,p_action_information7 => rg_get_mun_dtls.zone
1254 ,p_action_information8 => fnd_number.number_to_canonical(NVL(l_base_base,0))
1255 ,p_action_information9 => fnd_number.number_to_canonical(NVL(l_base_amt,0))
1256 ,p_action_information10 => fnd_number.number_to_canonical(NVL(l_reimb_base,0))
1257 ,p_action_information11 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_amt,0))
1258 ,p_action_information12 => fnd_number.number_to_canonical(NVL(l_utl1_base,0))
1259 ,p_action_information13 => fnd_number.number_to_canonical(NVL(l_utl1_amt,0))
1260 ,p_action_information14 => fnd_number.number_to_canonical(NVL(l_utr1_base,0))
1261 ,p_action_information15 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_amt,0))
1262 ,p_action_information16 => fnd_number.number_to_canonical(NVL(l_utl2_base,0))
1263 ,p_action_information17 => fnd_number.number_to_canonical(NVL(l_utl2_amt,0))
1264 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_pension_base,0))
1265 );
1266 --
1267 END IF; -- LU specified
1268 --
1269 END IF; -- Archive = 'Y'
1270 --
1271 END IF; -- Count < 0
1272 --
1273 IF g_debug THEN
1274 hr_utility.set_location(' Leaving Procedure RANGE_CODE',70);
1275 END IF;
1276 --
1277 EXCEPTION
1278 --
1279 WHEN OTHERS THEN
1280 -- Return cursor that selects no rows
1281 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1282 fnd_file.put_line(fnd_file.log,'Error in EC 1'||substr(sqlerrm , 1, 30));
1283 --
1284 END RANGE_CODE;
1285 --
1286 -- -----------------------------------------------------------------------------
1287 -- ASSIGNMENT ACTION CODE
1288 -- -----------------------------------------------------------------------------
1289 --
1290 PROCEDURE ASSG_ACTION_CODE
1291 (p_payroll_action_id IN NUMBER
1292 ,p_start_person IN NUMBER
1293 ,p_end_person IN NUMBER
1294 ,p_chunk IN NUMBER)
1295 IS
1296 BEGIN
1297 --
1298 IF g_debug THEN
1299 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',80);
1300 END IF;
1301 --
1302 IF g_debug THEN
1303 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',90);
1304 END IF;
1305 --
1306 END ASSG_ACTION_CODE;
1307 --
1308 -- -----------------------------------------------------------------------------
1309 -- INITIALIZATION CODE
1310 -- -----------------------------------------------------------------------------
1311 --
1312 PROCEDURE INIT_CODE(p_payroll_action_id IN NUMBER)
1313 IS
1314 BEGIN
1315 --
1316 IF g_debug THEN
1317 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',100);
1318 END IF;
1319 --
1320 IF g_debug THEN
1321 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',110);
1322 END IF;
1323 --
1324 EXCEPTION WHEN OTHERS THEN
1325 g_err_num := SQLCODE;
1326 IF g_debug THEN
1327 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',120);
1328 END IF;
1329 END INIT_CODE;
1330 --
1331 -- -----------------------------------------------------------------------------
1332 -- ARCHIVE CODE
1333 -- -----------------------------------------------------------------------------
1334 --
1335 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1336 ,p_effective_date IN DATE)
1337 IS
1338 BEGIN
1339 --
1340 IF g_debug THEN
1341 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',130);
1342 END IF;
1343 --
1344 IF g_debug THEN
1345 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',140);
1346 END IF;
1347 --
1348 END ARCHIVE_CODE;
1349 --
1350 -- ------------------------------------------------------ --
1351 -- GET_PDF_REP to generate the xml for pdf report (audit) --
1352 -- ------------------------------------------------------ --
1353 --
1354 PROCEDURE get_pdf_rep
1355 (p_business_group_id IN NUMBER
1356 ,p_payroll_action_id IN VARCHAR2
1357 ,p_template_name IN VARCHAR2
1358 ,p_xml OUT NOCOPY CLOB) IS
1359 --
1360 l_proc_name CONSTANT VARCHAR2(61) := 'get_pdf_rep';
1361 --
1362 CURSOR csr_LEGEMP
1363 (l_payroll_action_id IN NUMBER) IS
1364 SELECT leg_emp.action_information2 le_id
1365 ,leg_emp.action_information3 period_year
1366 ,leg_emp.action_information4 org_num
1367 ,leg_emp.action_information5 municipal_no
1368 ,leg_emp.action_information6 le_name
1369 ,leg_emp.action_information7 ada_line1
1370 ,leg_emp.action_information8 ada_line2
1371 ,leg_emp.action_information9 post_code
1372 ,leg_emp.action_information10 post_off
1373 ,leg_emp.action_information11 email
1374 ,leg_emp.action_information12 phone
1375 ,leg_emp.action_information17 industry_status
1376 ,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
1377 ,leg_emp.action_information19 nace_code
1378 ,leg_emp.action_information20 ada_line3
1379 ,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
1380 ,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
1381 FROM pay_action_information leg_emp
1382 WHERE leg_emp.action_context_type = 'PA'
1383 AND leg_emp.action_context_id = l_payroll_action_id
1384 AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
1385 AND leg_emp.action_information1 = 'PYNORSEA';
1386 --
1387 rec_LEGEMP csr_LEGEMP%ROWTYPE;
1388 --
1389 CURSOR csr_LU
1390 (l_payroll_action_id IN NUMBER) IS
1391 SELECT lu.action_information4 lu_org_num
1392 ,lu.action_information5 lu_municipal_num
1393 ,lu.action_information6 lu_municipal_name
1394 ,lu.action_information7 lu_zone
1395 ,fnd_number.canonical_to_number(lu.action_information8) ec_base
1396 ,fnd_number.canonical_to_number(lu.action_information9) ec_amt
1397 ,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
1398 ,fnd_number.canonical_to_number(lu.action_information11) reimburse_amt
1399 ,fnd_number.canonical_to_number(lu.action_information12) UTL1_base
1400 ,fnd_number.canonical_to_number(lu.action_information13) UTL1_amt
1401 ,fnd_number.canonical_to_number(lu.action_information14) UTR1_base
1402 ,fnd_number.canonical_to_number(lu.action_information15) UTR1_amt
1403 ,fnd_number.canonical_to_number(lu.action_information16) UTL2_base
1404 ,fnd_number.canonical_to_number(lu.action_information17) UTL2_amt
1405 ,fnd_number.canonical_to_number(lu.action_information18) pension_base
1406 FROM pay_action_information lu
1407 WHERE lu.action_context_type = 'PA'
1408 AND lu.action_context_id = l_payroll_action_id
1409 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
1410 AND lu.action_information1 = 'PYNORSEA-EC'
1411 ORDER BY 3;
1412 --
1413 rec_LU csr_LU%ROWTYPE;
1414 --
1415 CURSOR csr_TAX (l_payroll_action_id IN NUMBER) IS
1416 SELECT wt.action_information2 wt_municipal_num
1417 ,wt.action_information3 wt_municipal_name
1418 ,fnd_number.canonical_to_number(wt.action_information4) wt_tax_value
1419 FROM pay_action_information wt
1420 WHERE wt.action_context_type = 'PA'
1421 AND wt.action_context_id = l_payroll_action_id
1422 AND wt.action_information_category = 'EMEA REPORT INFORMATION'
1423 AND wt.action_information1 = 'PYNORSEA-WT'
1424 ORDER BY 2;
1425 --
1426 rec_TAX csr_TAX%ROWTYPE;
1427 --
1428 l_xml_element_count NUMBER := 1;
1429 l_pension_footnote NUMBER := 0;
1430 l_payroll_action_id NUMBER;
1431 l_ec_base_total NUMBER := 0;
1432 l_ec_amt_total NUMBER := 0;
1433 l_wt_total NUMBER := 0;
1434 --
1435 BEGIN
1436 hr_utility.set_location('Entering ' || l_proc_name, 10);
1437 g_xml_element_table.DELETE;
1438 --
1439 IF p_payroll_action_id is null then
1440 BEGIN
1441 SELECT payroll_action_id
1442 into l_payroll_action_id
1443 from pay_payroll_actions ppa,
1444 fnd_conc_req_summary_v fcrs,
1445 fnd_conc_req_summary_v fcrs1
1446 WHERE fcrs.request_id = fnd_global.conc_request_id
1447 and fcrs.priority_request_id = fcrs1.priority_request_id
1448 and ppa.request_id between fcrs1.request_id and fcrs.request_id
1449 and ppa.request_id = fcrs1.request_id;
1450 EXCEPTION
1451 WHEN others then
1452 null;
1453 END;
1454 ELSE
1455 l_payroll_action_id := p_payroll_action_id;
1456 END IF;
1457 --
1458 OPEN csr_LEGEMP(l_payroll_action_id);
1459 FETCH csr_LEGEMP INTO rec_LEGEMP;
1460 CLOSE csr_LEGEMP;
1461 --
1462 g_xml_element_table(l_xml_element_count).tagname := 'LEGAL_EMPLOYER';
1463 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1464 l_xml_element_count := l_xml_element_count + 1;
1465 --
1466 g_xml_element_table(l_xml_element_count).tagname := 'TERM';
1467 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,1,1);
1468 l_xml_element_count := l_xml_element_count + 1;
1469 --
1470 g_xml_element_table(l_xml_element_count).tagname := 'PERIOD';
1471 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,2,4);
1472 l_xml_element_count := l_xml_element_count + 1;
1473 --
1474 g_xml_element_table(l_xml_element_count).tagname := 'ORGANIZATION_NUMBER';
1475 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.org_num;
1476 l_xml_element_count := l_xml_element_count + 1;
1477 --
1478 g_xml_element_table(l_xml_element_count).tagname := 'LE_NAME';
1479 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.le_name;
1480 l_xml_element_count := l_xml_element_count + 1;
1481 --
1482 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE1';
1483 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line1;
1484 l_xml_element_count := l_xml_element_count + 1;
1485 --
1486 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE2';
1487 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line2;
1488 l_xml_element_count := l_xml_element_count + 1;
1489 --
1490 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE3';
1491 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line3;
1492 l_xml_element_count := l_xml_element_count + 1;
1493 --
1494 g_xml_element_table(l_xml_element_count).tagname := 'POST_CODE';
1495 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_code;
1496 l_xml_element_count := l_xml_element_count + 1;
1497 --
1498 g_xml_element_table(l_xml_element_count).tagname := 'POST_OFFICE';
1499 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_off;
1500 l_xml_element_count := l_xml_element_count + 1;
1501 --
1502 g_xml_element_table(l_xml_element_count).tagname := 'EMAIL';
1503 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.email;
1504 l_xml_element_count := l_xml_element_count + 1;
1505 --
1506 g_xml_element_table(l_xml_element_count).tagname := 'PHONE';
1507 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.phone;
1508 l_xml_element_count := l_xml_element_count + 1;
1509 --
1510 g_xml_element_table(l_xml_element_count).tagname := 'NACE_CODE';
1511 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.nace_code;
1512 l_xml_element_count := l_xml_element_count + 1;
1513 --
1514 g_xml_element_table(l_xml_element_count).tagname := 'CALC_METHOD_'||rec_LEGEMP.industry_status;
1515 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1516 l_xml_element_count := l_xml_element_count + 1;
1517 --
1518 g_xml_element_table(l_xml_element_count).tagname := 'EXEMPT_LIMIT';
1519 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit,'FM9G999G999G999G990D00');
1520 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1521 l_xml_element_count := l_xml_element_count + 1;
1522 --
1523 g_xml_element_table(l_xml_element_count).tagname := 'REMAINING_EXPEMT_PREV';
1524 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit_prev,'FM9G999G999G999G990D00');
1525 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1526 l_xml_element_count := l_xml_element_count + 1;
1527 --
1528 g_xml_element_table(l_xml_element_count).tagname := 'REMAINING_EXPEMT_AFTER';
1529 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit_after,'FM9G999G999G999G990D00');
1530 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1531 l_xml_element_count := l_xml_element_count + 1;
1532 --
1533 FOR rec_lu IN csr_LU(l_payroll_action_id)
1534 LOOP
1535 --
1536 IF rec_lu.ec_base <> 0 OR rec_lu.ec_amt <> 0 THEN
1537 --
1538 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1539 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1540 l_xml_element_count := l_xml_element_count + 1;
1541 --
1542 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1543 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1544 l_xml_element_count := l_xml_element_count + 1;
1545 --
1546 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1547 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1548 l_xml_element_count := l_xml_element_count + 1;
1549 --
1550 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1551 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1552 l_xml_element_count := l_xml_element_count + 1;
1553 --
1554 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1555 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1556 l_xml_element_count := l_xml_element_count + 1;
1557 --
1558 g_xml_element_table(l_xml_element_count).tagname := 'EC_REP';
1559 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1560 l_xml_element_count := l_xml_element_count + 1;
1561 --
1562 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1563 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.ec_base,'FM9G999G999G999G990D00');
1564 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1565 l_xml_element_count := l_xml_element_count + 1;
1566 l_ec_base_total := l_ec_base_total + rec_lu.ec_base;
1567 --
1568 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1569 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.ec_amt,'FM9G999G999G999G990D00');
1570 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1571 l_xml_element_count := l_xml_element_count + 1;
1572 l_ec_amt_total := l_ec_amt_total + rec_lu.ec_amt;
1573 --
1574 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1575 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1576 l_xml_element_count := l_xml_element_count + 1;
1577 --
1578 END IF;
1579 --
1580 IF rec_lu.reimburse_base <> 0 OR rec_lu.reimburse_amt <> 0 THEN
1581 --
1582 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1583 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1584 l_xml_element_count := l_xml_element_count + 1;
1585 --
1586 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1587 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1588 l_xml_element_count := l_xml_element_count + 1;
1589 --
1590 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1591 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1592 l_xml_element_count := l_xml_element_count + 1;
1593 --
1594 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1595 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1596 l_xml_element_count := l_xml_element_count + 1;
1597 --
1598 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1599 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1600 l_xml_element_count := l_xml_element_count + 1;
1601 --
1602 g_xml_element_table(l_xml_element_count).tagname := 'REIMBURSE_REP';
1603 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1604 l_xml_element_count := l_xml_element_count + 1;
1605 --
1606 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1607 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.reimburse_base,'FM9G999G999G999G990D00');
1608 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1609 l_xml_element_count := l_xml_element_count + 1;
1610 l_ec_base_total := l_ec_base_total + rec_lu.reimburse_base;
1611 --
1612 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1613 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.reimburse_amt,'FM9G999G999G999G990D00');
1614 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1615 l_xml_element_count := l_xml_element_count + 1;
1616 l_ec_amt_total := l_ec_amt_total + rec_lu.reimburse_amt;
1617 --
1618 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1619 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1620 l_xml_element_count := l_xml_element_count + 1;
1621 --
1622 END IF;
1623 --
1624 IF rec_lu.UTL1_base <> 0 OR rec_lu.UTL1_amt <> 0 THEN
1625 --
1626 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1627 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1628 l_xml_element_count := l_xml_element_count + 1;
1629 --
1630 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1631 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1632 l_xml_element_count := l_xml_element_count + 1;
1633 --
1634 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1635 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1636 l_xml_element_count := l_xml_element_count + 1;
1637 --
1638 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1639 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1640 l_xml_element_count := l_xml_element_count + 1;
1641 --
1642 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1643 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1644 l_xml_element_count := l_xml_element_count + 1;
1645 --
1646 g_xml_element_table(l_xml_element_count).tagname := 'UTL1_REP';
1647 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1648 l_xml_element_count := l_xml_element_count + 1;
1649 --
1650 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1651 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL1_base,'FM9G999G999G999G990D00');
1652 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1653 l_xml_element_count := l_xml_element_count + 1;
1654 l_ec_base_total := l_ec_base_total + rec_lu.UTL1_base;
1655 --
1656 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1657 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL1_amt,'FM9G999G999G999G990D00');
1658 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1659 l_xml_element_count := l_xml_element_count + 1;
1660 l_ec_amt_total := l_ec_amt_total + rec_lu.UTL1_amt;
1661 --
1662 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1663 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1664 l_xml_element_count := l_xml_element_count + 1;
1665 --
1666 END IF;
1667 --
1668 IF rec_lu.UTR1_base <> 0 OR rec_lu.UTR1_amt <> 0 THEN
1669 --
1670 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1671 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1672 l_xml_element_count := l_xml_element_count + 1;
1673 --
1674 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1675 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1676 l_xml_element_count := l_xml_element_count + 1;
1677 --
1678 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1679 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1680 l_xml_element_count := l_xml_element_count + 1;
1681 --
1682 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1683 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1684 l_xml_element_count := l_xml_element_count + 1;
1685 --
1686 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1687 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1688 l_xml_element_count := l_xml_element_count + 1;
1689 --
1690 g_xml_element_table(l_xml_element_count).tagname := 'UTR1_REP';
1691 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1692 l_xml_element_count := l_xml_element_count + 1;
1693 --
1694 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1695 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTR1_base,'FM9G999G999G999G990D00');
1696 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1697 l_xml_element_count := l_xml_element_count + 1;
1698 l_ec_base_total := l_ec_base_total + rec_lu.UTR1_base;
1699 --
1700 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1701 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTR1_amt,'FM9G999G999G999G990D00');
1702 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1703 l_xml_element_count := l_xml_element_count + 1;
1704 l_ec_amt_total := l_ec_amt_total + rec_lu.UTR1_amt;
1705 --
1706 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1707 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1708 l_xml_element_count := l_xml_element_count + 1;
1709 --
1710 END IF;
1711 --
1712 IF rec_lu.UTL2_base <> 0 OR rec_lu.UTL2_amt <> 0 THEN
1713 --
1714 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1715 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1716 l_xml_element_count := l_xml_element_count + 1;
1717 --
1718 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1719 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1720 l_xml_element_count := l_xml_element_count + 1;
1721 --
1722 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1723 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1724 l_xml_element_count := l_xml_element_count + 1;
1725 --
1726 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1727 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1728 l_xml_element_count := l_xml_element_count + 1;
1729 --
1730 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1731 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1732 l_xml_element_count := l_xml_element_count + 1;
1733 --
1734 g_xml_element_table(l_xml_element_count).tagname := 'UTR2_REP';
1735 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1736 l_xml_element_count := l_xml_element_count + 1;
1737 --
1738 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1739 g_xml_element_table(l_xml_element_count).tagvalue := to_char(round(rec_lu.UTL2_base,2));
1740 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1741 l_xml_element_count := l_xml_element_count + 1;
1742 --
1743 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1744 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL2_amt,'FM9G999G999G999G990D00');
1745 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1746 l_xml_element_count := l_xml_element_count + 1;
1747 l_ec_amt_total := l_ec_amt_total + rec_lu.UTL2_amt;
1748 --
1749 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1750 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1751 l_xml_element_count := l_xml_element_count + 1;
1752 --
1753 END IF;
1754 --
1755 IF rec_lu.pension_base <> 0 THEN
1756 --
1757 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1758 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1759 l_xml_element_count := l_xml_element_count + 1;
1760 --
1761 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1762 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1763 l_xml_element_count := l_xml_element_count + 1;
1764 --
1765 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1766 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1767 l_xml_element_count := l_xml_element_count + 1;
1768 --
1769 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1770 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1771 l_xml_element_count := l_xml_element_count + 1;
1772 --
1773 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1774 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1775 l_xml_element_count := l_xml_element_count + 1;
1776 --
1777 g_xml_element_table(l_xml_element_count).tagname := 'PENSION_REP';
1778 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1779 l_xml_element_count := l_xml_element_count + 1;
1780 l_pension_footnote := 1;
1781 --
1782 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1783 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.pension_base,'FM9G999G999G999G990D00');
1784 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1785 l_xml_element_count := l_xml_element_count + 1;
1786 --
1787 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1788 g_xml_element_table(l_xml_element_count).tagvalue := '*)';
1789 l_xml_element_count := l_xml_element_count + 1;
1790 l_ec_base_total := l_ec_base_total + rec_lu.pension_base;
1791 --
1792 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1793 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1794 l_xml_element_count := l_xml_element_count + 1;
1795 --
1796 END IF;
1797 --
1798 END LOOP;
1799 --
1800 FOR rec_tax IN csr_TAX(l_payroll_action_id)
1801 LOOP
1802 --
1803 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX';
1804 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1805 l_xml_element_count := l_xml_element_count + 1;
1806 --
1807 g_xml_element_table(l_xml_element_count).tagname := 'WT_MUNICIPAL_NUM';
1808 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_num;
1809 l_xml_element_count := l_xml_element_count + 1;
1810 --
1811 g_xml_element_table(l_xml_element_count).tagname := 'WT_MUNICIPAL_NAME';
1812 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_name;
1813 l_xml_element_count := l_xml_element_count + 1;
1814 --
1815 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX_AMT';
1816 g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_tax.wt_tax_value,'FM9G999G999G999G990D00');
1817 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1818 l_xml_element_count := l_xml_element_count + 1;
1819 l_wt_total := l_wt_total + rec_tax.wt_tax_value;
1820 --
1821 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX';
1822 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1823 l_xml_element_count := l_xml_element_count + 1;
1824 --
1825 END LOOP;
1826 --
1827 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_EC_AMT';
1828 g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_ec_amt_total,'FM9G999G999G999G990D00');
1829 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1830 l_xml_element_count := l_xml_element_count + 1;
1831 --
1832 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_EC_BASE';
1833 g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_ec_base_total,'FM9G999G999G999G990D00');
1834 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1835 l_xml_element_count := l_xml_element_count + 1;
1836 --
1837 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_WITHHOLDING_TAX';
1838 g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_wt_total,'FM9G999G999G999G990D00');
1839 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1840 l_xml_element_count := l_xml_element_count + 1;
1841 --
1842 -- Footnote section for Pension Calculated Employer Contribution
1843 IF l_pension_footnote = 1 THEN
1844 g_xml_element_table(l_xml_element_count).tagname := 'FOOT_NOTE';
1845 g_xml_element_table(l_xml_element_count).tagvalue := '*) ' || HR_GENERAL.DECODE_LOOKUP('NO_FORM_LABELS','PENSION_FOOTNOTE');
1846 l_xml_element_count := l_xml_element_count + 1;
1847 END IF;
1848 --
1849 g_xml_element_table(l_xml_element_count).tagname := 'LEGAL_EMPLOYER';
1850 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1851 l_xml_element_count := l_xml_element_count + 1;
1852 --
1853 write_to_clob(p_xml);
1854 --
1855 hr_utility.set_location('Leaving ' || l_proc_name, 1000);
1856 --
1857 END get_pdf_rep;
1858 --
1859 -- ------------------------------------------------- --
1860 -- GET_XML_REP to generate the standard xml extract --
1861 -- ------------------------------------------------- --
1862 --
1863 PROCEDURE get_xml_rep
1864 (p_business_group_id IN NUMBER
1865 ,p_payroll_action_id IN VARCHAR2
1866 ,p_template_name IN VARCHAR2
1867 ,p_xml OUT NOCOPY CLOB) IS
1868 --
1869 l_proc_name CONSTANT VARCHAR2(61) := 'get_xml_rep';
1870 --
1871 CURSOR csr_LEGEMP
1872 (l_payroll_action_id IN NUMBER) IS
1873 SELECT leg_emp.action_information3 period_year
1874 ,leg_emp.action_information4 org_num
1875 ,leg_emp.action_information5 municipal_no
1876 ,leg_emp.action_information6 le_name
1877 ,leg_emp.action_information7 add_line1
1878 ,leg_emp.action_information8 add_line2
1879 ,leg_emp.action_information9 post_code
1880 ,leg_emp.action_information10 post_off
1881 ,leg_emp.action_information17 industry_status
1882 ,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
1883 ,leg_emp.action_information19 nace_code
1884 ,leg_emp.action_information20 add_line3
1885 ,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
1886 ,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
1887 FROM pay_action_information leg_emp
1888 WHERE leg_emp.action_context_type = 'PA'
1889 AND leg_emp.action_context_id = l_payroll_action_id
1890 AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
1891 AND leg_emp.action_information1 = 'PYNORSEA';
1892 --
1893 rec_LEGEMP csr_LEGEMP%ROWTYPE;
1894 --
1895 CURSOR csr_sum_LU
1896 (l_payroll_action_id IN NUMBER) IS
1897 SELECT SUM(fnd_number.canonical_to_number(lu.action_information8)) EC_base_sum
1898 ,SUM(fnd_number.canonical_to_number(lu.action_information10)) REIM_base_sum
1899 ,SUM(fnd_number.canonical_to_number(lu.action_information12)) UTL1_base_sum
1900 ,SUM(fnd_number.canonical_to_number(lu.action_information14)) UTR1_base_sum
1901 ,SUM(fnd_number.canonical_to_number(lu.action_information16)) UTL2_base_sum
1902 ,SUM(fnd_number.canonical_to_number(lu.action_information9))
1903 + SUM(fnd_number.canonical_to_number(lu.action_information11))
1904 + SUM(fnd_number.canonical_to_number(lu.action_information13))
1905 + SUM(fnd_number.canonical_to_number(lu.action_information15))
1906 + SUM(fnd_number.canonical_to_number(lu.action_information17)) amt_sum
1907 FROM pay_action_information lu
1908 WHERE lu.action_context_type = 'PA'
1909 AND lu.action_context_id = l_payroll_action_id
1910 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
1911 AND lu.action_information1 = 'PYNORSEA-EC';
1912 --
1913 rec_sum_LU csr_sum_LU%ROWTYPE;
1914 --
1915 CURSOR csr_LU
1916 (l_payroll_action_id IN NUMBER) IS
1917 SELECT lu.action_information4 lu_org_num
1918 ,lu.action_information5 lu_municipal_num
1919 ,fnd_number.canonical_to_number(lu.action_information8) ec_base
1920 ,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
1921 ,fnd_number.canonical_to_number(lu.action_information18) pension_base_sum
1922 FROM pay_action_information lu
1923 WHERE lu.action_context_type = 'PA'
1924 AND lu.action_context_id = l_payroll_action_id
1925 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
1926 AND lu.action_information1 = 'PYNORSEA-EC'
1927 ORDER BY 3;
1928 --
1929 rec_LU csr_LU%ROWTYPE;
1930 --
1931 CURSOR csr_TAX
1932 (l_payroll_action_id IN NUMBER) IS
1933 SELECT wt.action_information2 wt_municipal_num
1934 ,fnd_number.canonical_to_number(wt.action_information4) wt_tax_value
1935 FROM pay_action_information wt
1936 WHERE wt.action_context_type = 'PA'
1937 AND wt.action_context_id = l_payroll_action_id
1938 AND wt.action_information_category = 'EMEA REPORT INFORMATION'
1939 AND wt.action_information1 = 'PYNORSEA-WT'
1940 ORDER BY 1;
1941 --
1942 rec_TAX csr_TAX%ROWTYPE;
1943 --
1944 l_xml_element_count NUMBER := 1;
1945 l_payroll_action_id NUMBER;
1946 l_wt_total NUMBER := 0;
1947 --
1948 BEGIN
1949 hr_utility.set_location('Entering ' || l_proc_name, 10);
1950 g_xml_element_table.DELETE;
1951 --
1952 IF p_payroll_action_id is null then
1953 BEGIN
1954 SELECT payroll_action_id
1955 into l_payroll_action_id
1956 from pay_payroll_actions ppa,
1957 fnd_conc_req_summary_v fcrs,
1958 fnd_conc_req_summary_v fcrs1
1959 WHERE fcrs.request_id = fnd_global.conc_request_id
1960 and fcrs.priority_request_id = fcrs1.priority_request_id
1961 and ppa.request_id between fcrs1.request_id and fcrs.request_id
1962 and ppa.request_id = fcrs1.request_id;
1963 EXCEPTION
1964 WHEN others then
1965 null;
1966 END;
1967 ELSE
1968 l_payroll_action_id := p_payroll_action_id;
1969 END IF;
1970 --
1971 OPEN csr_LEGEMP(l_payroll_action_id);
1972 FETCH csr_LEGEMP INTO rec_LEGEMP;
1973 CLOSE csr_LEGEMP;
1974 --
1975 g_xml_element_table(l_xml_element_count).tagname := 'Skjema';
1976 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1977 g_xml_element_table(l_xml_element_count).tagattrb := 'skjemanummer="669" spesifikasjonsnummer="6168"';
1978 l_xml_element_count := l_xml_element_count + 1;
1979 --
1980 g_xml_element_table(l_xml_element_count).tagname := 'Innledning-grp-986';
1981 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1982 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="986"';
1983 l_xml_element_count := l_xml_element_count + 1;
1984 --
1985 g_xml_element_table(l_xml_element_count).tagname := 'Periode-grp-57';
1986 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1987 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="57"';
1988 l_xml_element_count := l_xml_element_count + 1;
1989 -- Bi-Monthly Period
1990 g_xml_element_table(l_xml_element_count).tagname := 'OppgaveTermin-datadef-11819';
1991 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,1,1);
1992 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="11819"';
1993 l_xml_element_count := l_xml_element_count + 1;
1994 -- Year
1995 g_xml_element_table(l_xml_element_count).tagname := 'OppgaveAr-datadef-11236';
1996 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,2,4);
1997 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="11236"';
1998 l_xml_element_count := l_xml_element_count + 1;
1999 --
2000 g_xml_element_table(l_xml_element_count).tagname := 'Periode-grp-57';
2001 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2002 l_xml_element_count := l_xml_element_count + 1;
2003 --
2004 --
2005 g_xml_element_table(l_xml_element_count).tagname := 'Skatteoppkrever-grp-989';
2006 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2007 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="989"';
2008 l_xml_element_count := l_xml_element_count + 1;
2009 -- LE Tax Municipality
2010 g_xml_element_table(l_xml_element_count).tagname := 'SkatteoppkreverKommuneNummer-datadef-16513';
2011 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.municipal_no;
2012 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16513"';
2013 l_xml_element_count := l_xml_element_count + 1;
2014 --
2015 g_xml_element_table(l_xml_element_count).tagname := 'Skatteoppkrever-grp-989';
2016 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2017 l_xml_element_count := l_xml_element_count + 1;
2018 --
2019 --
2020 g_xml_element_table(l_xml_element_count).tagname := 'Innsender-grp-56';
2021 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2022 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="56"';
2023 l_xml_element_count := l_xml_element_count + 1;
2024 -- LE Organization Number
2025 g_xml_element_table(l_xml_element_count).tagname := 'RapporteringsenhetOrganisasjonsnummer-datadef-21772';
2026 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.org_num;
2027 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="21772"';
2028 l_xml_element_count := l_xml_element_count + 1;
2029 -- Not Used by Oracle Payroll 26
2030 -- g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverFodselsnummer-datadef-26';
2031 -- g_xml_element_table(l_xml_element_count).tagvalue := NULL;
2032 -- l_xml_element_count := l_xml_element_count + 1;
2033 -- LE Name
2034 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverNavnPreutfylt-datadef-25795';
2035 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.le_name;
2036 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25795"';
2037 l_xml_element_count := l_xml_element_count + 1;
2038 -- LE Address
2039 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverAdressePreutfylt-datadef-25796';
2040 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.add_line1 ||' '|| rec_LEGEMP.add_line2
2041 ||' '|| rec_LEGEMP.add_line3;
2042 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25796"';
2043 l_xml_element_count := l_xml_element_count + 1;
2044 -- LE Address Post Code
2045 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverPostnummerPreutfylt-datadef-25797';
2046 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_code;
2047 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25797"';
2048 l_xml_element_count := l_xml_element_count + 1;
2049 -- LE Address Post Office
2050 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverPoststedPreutfylt-datadef-25798';
2051 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_off;
2052 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25798"';
2053 l_xml_element_count := l_xml_element_count + 1;
2054 -- Nace Code
2055 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverNACEKode-datadef-27602';
2056 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.nace_code;
2057 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27602"';
2058 l_xml_element_count := l_xml_element_count + 1;
2059 --
2060 g_xml_element_table(l_xml_element_count).tagname := 'Innsender-grp-56';
2061 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2062 l_xml_element_count := l_xml_element_count + 1;
2063 --
2064 --
2065 g_xml_element_table(l_xml_element_count).tagname := 'Innledning-grp-986';
2066 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2067 l_xml_element_count := l_xml_element_count + 1;
2068 --
2069 --
2070 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-5698';
2071 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2072 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="5698"';
2073 l_xml_element_count := l_xml_element_count + 1;
2074 --
2075 g_xml_element_table(l_xml_element_count).tagname := 'Beregningsmate-grp-169';
2076 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2077 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="169"';
2078 l_xml_element_count := l_xml_element_count + 1;
2079 -- Calculation Method
2080 g_xml_element_table(l_xml_element_count).tagname := 'TerminoppgaveBeregningsmate-datadef-27603';
2081 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.industry_status;
2082 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27603"';
2083 l_xml_element_count := l_xml_element_count + 1;
2084 -- Exempt limit from Last Reporting Term
2085 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftBunnfradrag-datadef-16517';
2086 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit_prev;
2087 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16517"';
2088 l_xml_element_count := l_xml_element_count + 1;
2089 --
2090 g_xml_element_table(l_xml_element_count).tagname := 'Beregningsmate-grp-169';
2091 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2092 l_xml_element_count := l_xml_element_count + 1;
2093 --
2094 --
2095 g_xml_element_table(l_xml_element_count).tagname := 'Tilskudd-grp-6712';
2096 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2097 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6712"';
2098 l_xml_element_count := l_xml_element_count + 1;
2099 -- Exempt limit from Last Reporting Term
2100 g_xml_element_table(l_xml_element_count).tagname := 'TilskuddAndreTerminoppgave-datadef-27604';
2101 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit;
2102 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27604"';
2103 l_xml_element_count := l_xml_element_count + 1;
2104 --
2105 g_xml_element_table(l_xml_element_count).tagname := 'Tilskudd-grp-6712';
2106 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2107 l_xml_element_count := l_xml_element_count + 1;
2108 --
2109 --
2110 OPEN csr_sum_LU(l_payroll_action_id);
2111 FETCH csr_sum_LU INTO rec_sum_LU;
2112 CLOSE csr_sum_LU;
2113 --
2114 g_xml_element_table(l_xml_element_count).tagname := 'UTL1-grp-6715';
2115 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2116 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6715"';
2117 l_xml_element_count := l_xml_element_count + 1;
2118 -- Sum of UTL1 Bases
2119 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftUtenlandskGrunnlag-datadef-16518';
2120 g_xml_element_table(l_xml_element_count).tagvalue := nvl(round(rec_sum_LU.UTL1_base_sum),0);
2121 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16518"';
2122 l_xml_element_count := l_xml_element_count + 1;
2123 -- Sum of UTR1 Bases
2124 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftUtenlandskRefusjonsgrunnlagSpesifisert-datadef-27612';
2125 g_xml_element_table(l_xml_element_count).tagvalue := nvl(round(rec_sum_LU.UTR1_base_sum),0);
2126 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27612"';
2127 l_xml_element_count := l_xml_element_count + 1;
2128 --
2129 g_xml_element_table(l_xml_element_count).tagname := 'UTL1-grp-6715';
2130 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2131 l_xml_element_count := l_xml_element_count + 1;
2132 --
2133 --
2134 g_xml_element_table(l_xml_element_count).tagname := 'UTL2-grp-6716';
2135 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2136 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6716"';
2137 l_xml_element_count := l_xml_element_count + 1;
2138 -- Sum of Months UTL2
2139 g_xml_element_table(l_xml_element_count).tagname := 'AnsattUtenlandskManeder-datadef-16519';
2140 g_xml_element_table(l_xml_element_count).tagvalue := round(nvl(rec_sum_LU.UTL2_base_sum,0));
2141 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16519"';
2142 l_xml_element_count := l_xml_element_count + 1;
2143 --
2144 g_xml_element_table(l_xml_element_count).tagname := 'UTL2-grp-6716';
2145 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2146 l_xml_element_count := l_xml_element_count + 1;
2147 --
2148 --
2149 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-5698';
2150 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2151 l_xml_element_count := l_xml_element_count + 1;
2152 --
2153 --
2154 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-6719';
2155 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2156 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6719"';
2157 l_xml_element_count := l_xml_element_count + 1;
2158 --
2159 FOR rec_lu IN csr_LU(l_payroll_action_id)
2160 LOOP
2161 --
2162 g_xml_element_table(l_xml_element_count).tagname := 'TabellArbeidsgiveravgift-grp-4953';
2163 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2164 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4953"';
2165 l_xml_element_count := l_xml_element_count + 1;
2166 -- LU Org Number
2167 g_xml_element_table(l_xml_element_count).tagname := 'AvgiftsbetalerOrganisasjonsnummer-datadef-27605';
2168 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
2169 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27605"';
2170 l_xml_element_count := l_xml_element_count + 1;
2171 -- Not used by Oracle Payroll - 27606
2172 -- g_xml_element_table(l_xml_element_count).tagname := 'AvgiftsbetalerFodselssnummer-datadef-27606';
2173 -- g_xml_element_table(l_xml_element_count).tagvalue := NULL;
2174 -- g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27606"'
2175 -- l_xml_element_count := l_xml_element_count + 1;
2176 -- LU Tax Municipality
2177 g_xml_element_table(l_xml_element_count).tagname := 'KommuneNummer-datadef-5950';
2178 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
2179 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="5950"';
2180 l_xml_element_count := l_xml_element_count + 1;
2181 -- EC Base (Normal)
2182 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftGrunnlagSpesifisert-datadef-27607';
2183 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_lu.ec_base);
2184 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27607"';
2185 l_xml_element_count := l_xml_element_count + 1;
2186 -- Reimbursements
2187 g_xml_element_table(l_xml_element_count).tagname := 'RefusjonGrunnlagSpesifisert-datadef-27608';
2188 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_lu.reimburse_base);
2189 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27608"';
2190 l_xml_element_count := l_xml_element_count + 1;
2191 -- Not used by Oracle Payroll - 27611 - set it to zero, till pension built was not in NO loc.
2192 -- SInce NO Pension built is there, will populate accordingly.
2193 g_xml_element_table(l_xml_element_count).tagname := 'PensjonPremieTilskuddSpesifisert-datadef-27611';
2194 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_lu.pension_base_sum);
2195 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27611"';
2196 l_xml_element_count := l_xml_element_count + 1;
2197 --
2198 g_xml_element_table(l_xml_element_count).tagname := 'TabellArbeidsgiveravgift-grp-4953';
2199 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2200 l_xml_element_count := l_xml_element_count + 1;
2201 --
2202 END LOOP;
2203 --
2204 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-6719';
2205 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2206 l_xml_element_count := l_xml_element_count + 1;
2207 --
2208 --
2209 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-6717';
2210 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2211 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6717"';
2212 l_xml_element_count := l_xml_element_count + 1;
2213 --
2214 FOR rec_tax IN csr_TAX(l_payroll_action_id)
2215 LOOP
2216 --
2217 g_xml_element_table(l_xml_element_count).tagname := 'TabellForskuddstrekk-grp-6718';
2218 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2219 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6718"';
2220 l_xml_element_count := l_xml_element_count + 1;
2221 -- Emplyee Municipal Code
2222 g_xml_element_table(l_xml_element_count).tagname := 'InnberetningspliktigForskuddstrekkKommunenummer-datadef-27615';
2223 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_num;
2224 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27615"';
2225 l_xml_element_count := l_xml_element_count + 1;
2226 -- Tax
2227 g_xml_element_table(l_xml_element_count).tagname := 'ForskuddstrekkSpesifisert-datadef-27616';
2228 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_tax.wt_tax_value);
2229 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27616"';
2230 l_xml_element_count := l_xml_element_count + 1;
2231 l_wt_total := l_wt_total + rec_tax.wt_tax_value;
2232 --
2233 g_xml_element_table(l_xml_element_count).tagname := 'TabellForskuddstrekk-grp-6718';
2234 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2235 l_xml_element_count := l_xml_element_count + 1;
2236 --
2237 END LOOP;
2238 --
2239 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-6717';
2240 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2241 l_xml_element_count := l_xml_element_count + 1;
2242 --
2243 --
2244 g_xml_element_table(l_xml_element_count).tagname := 'Resultater-grp-74';
2245 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2246 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="74"';
2247 l_xml_element_count := l_xml_element_count + 1;
2248 --
2249 g_xml_element_table(l_xml_element_count).tagname := 'Kontrollsummer-grp-4909';
2250 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2251 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4909"';
2252 l_xml_element_count := l_xml_element_count + 1;
2253 -- EC Base Total
2254 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftGrunnlag-datadef-27617';
2255 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_sum_LU.EC_base_sum);
2256 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27617"';
2257 l_xml_element_count := l_xml_element_count + 1;
2258 -- Reimburse Base Total
2259 g_xml_element_table(l_xml_element_count).tagname := 'RefusjonGrunnlag-datadef-27618';
2260 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_sum_LU.REIM_base_sum);
2261 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27618"';
2262 l_xml_element_count := l_xml_element_count + 1;
2263 -- Pension Base Total (not in place)
2264 g_xml_element_table(l_xml_element_count).tagname := 'PensjonPremieTilskuddSumGrunnlag-datadef-27619';
2265 g_xml_element_table(l_xml_element_count).tagvalue := '0';
2266 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27619"';
2267 l_xml_element_count := l_xml_element_count + 1;
2268 -- Remaining Exemption Limit Total
2269 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftRestFribelop-datadef-21169';
2270 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_LEGEMP.exempt_limit_after);
2271 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="21169"';
2272 l_xml_element_count := l_xml_element_count + 1;
2273 --
2274 g_xml_element_table(l_xml_element_count).tagname := 'Kontrollsummer-grp-4909';
2275 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2276 l_xml_element_count := l_xml_element_count + 1;
2277 --
2278 --
2279 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-4910';
2280 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2281 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4910"';
2282 l_xml_element_count := l_xml_element_count + 1;
2283 -- Sum of all the amounts
2284 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftSkyldig-datadef-223';
2285 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_sum_LU.amt_sum);
2286 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="223"';
2287 l_xml_element_count := l_xml_element_count + 1;
2288 --
2289 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-4910';
2290 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2291 l_xml_element_count := l_xml_element_count + 1;
2292 --
2293 --
2294 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-4911';
2295 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2296 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4911"';
2297 l_xml_element_count := l_xml_element_count + 1;
2298 -- Withholding Tax Total
2299 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-datadef-2903';
2300 g_xml_element_table(l_xml_element_count).tagvalue := round(l_wt_total);
2301 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="2903"';
2302 l_xml_element_count := l_xml_element_count + 1;
2303 --
2304 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-4911';
2305 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2306 l_xml_element_count := l_xml_element_count + 1;
2307 --
2308 --
2309 g_xml_element_table(l_xml_element_count).tagname := 'Resultater-grp-74';
2310 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2311 l_xml_element_count := l_xml_element_count + 1;
2312 --
2313 g_xml_element_table(l_xml_element_count).tagname := 'Skjema';
2314 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2315 l_xml_element_count := l_xml_element_count + 1;
2316 --
2317 write_to_clob_for_xml(p_xml);
2318 --
2319 hr_utility.set_location('Leaving ' || l_proc_name, 1000);
2320 --
2321 END get_xml_rep;
2322 --
2323 END PAY_NO_ARC_RSEA_07;