[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.3.12020000.7 2013/03/22 09:18:07 smeduri ship $ */
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 ,p_report_type out NOCOPY VARCHAR2 -- Bug#9579261 fix
256 ,p_employee_id out NOCOPY NUMBER -- 14260836
257 ) IS
258
259 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
260 SELECT PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
261 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
262 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'ARCHIVE')
263 ,effective_date
264 ,business_group_id
265 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'REPORT_TYPE') -- Bug#9579261 fix
266 ,PAY_NO_ARC_RSEA_07.GET_PARAMETER(legislative_parameters,'EMPLOYEE_NAME') -- 14260836
267 FROM pay_payroll_actions
268 WHERE payroll_action_id = p_payroll_action_id;
269 --
270 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
271 --
272 BEGIN
273 --
274 OPEN csr_parameter_info (p_payroll_action_id);
275 FETCH csr_parameter_info
276 INTO p_legal_employer_id
277 ,p_local_unit_id
278 ,p_archive
279 ,p_effective_date
280 ,p_business_group_id
281 ,p_report_type -- Bug#9579261 fix
282 ,p_employee_id; --14260836
283 CLOSE csr_parameter_info;
284 --
285 IF g_debug THEN
286 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
287 END IF;
288 END GET_ALL_PARAMETERS;
289 --
290 -- -----------------------------------------------------------------------------
291 -- RANGE CODE
292 -- -----------------------------------------------------------------------------
293 --
294 PROCEDURE RANGE_CODE(p_payroll_action_id IN NUMBER
295 ,p_sql OUT NOCOPY VARCHAR2)
296 IS
297 l_action_info_id NUMBER;
298 l_ovn NUMBER;
299 l_defined_balance_id NUMBER := 0;
300 l_count NUMBER := 0;
301 l_business_group_id NUMBER;
302 l_period VARCHAR2(2);
303 l_year VARCHAR2(4);
304 l_effective_date DATE;
305 l_legal_employer_id NUMBER ;
306 l_local_unit_id NUMBER ;
307 l_archive VARCHAR2(3);
308 l_report_type VARCHAR2(3);
309 l_employee_id NUMBER; --14260836
310 l_assignment_action NUMBER; --14260836
311 l_bal_dim VARCHAR2(50); --14260836
312 l_bal_dim1 VARCHAR2(50); --14260836
313 l_el NUMBER;
314 l_el_b NUMBER;
315 l_el_a NUMBER;
316 l_reporting_start_date DATE;
317 l_reporting_end_date DATE;
318 l_municipal_name VARCHAR2(30);
319 l_zone NUMBER;
320
321 l_municipal_no hr_organization_information.org_information1%TYPE ;
322 l_industry_status hr_organization_information.org_information1%TYPE ;
323 l_nace_code hr_organization_information.org_information1%TYPE ;
324 l_lu_name hr_organization_units.name%TYPE ;
325 l_Witholding_Tax NUMBER;
326 l_sum_tax_value NUMBER;
327 l_sum_tax_value_rep NUMBER; --14260836
328 l_tax_value NUMBER;
329 l_def_bal_id NUMBER;
330 l_fe_fm_amount NUMBER;
331 --
332 l_base_base NUMBER;
333 l_base_amt NUMBER;
334 l_pension_amount NUMBER; -- 16229158
335 l_reimb_base NUMBER;
336 l_reimb_amt NUMBER;
337 l_utl1_base NUMBER;
338 l_utl1_amt NUMBER;
339 l_utr1_base NUMBER;
340 l_utr1_amt NUMBER;
341 l_utl2_base NUMBER;
342 l_utl2_amt NUMBER;
343 l_pension_base NUMBER;
344 l_rate NUMBER; --14260836
345 --
346 TYPE municipaldata IS RECORD(municipalcode VARCHAR2(10));
347 TYPE tmunicipaldata IS TABLE OF municipaldata INDEX BY BINARY_INTEGER;
348 --
349 gmunicipaldata tmunicipaldata ;
350 --
351 l_counter NUMBER;
352 l_status NUMBER;
353 --
354 Cursor csr_LU_Details (csr_v_local_unit_id hr_organization_information.organization_id%TYPE) IS
355 SELECT o1.name lu_name
356 ,hoi2.org_information4 industry_status
357 ,hoi2.org_information2 nace_code
358 ,hoi2.org_information1 org_num
359 ,hoi2.org_information6 municipal_no
360 FROM hr_organization_units o1
361 ,hr_organization_information hoi1
362 ,hr_organization_information hoi2
363 WHERE o1.business_group_id = l_business_group_id
364 AND hoi1.organization_id = o1.organization_id
365 AND hoi1.organization_id = csr_v_local_unit_id
366 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
367 AND hoi1.org_information_context = 'CLASS'
368 AND o1.organization_id = hoi2.organization_id
369 AND hoi2.org_information_context = 'NO_LOCAL_UNIT_DETAILS';
370 --
371 rg_LU_Details csr_LU_Details%rowtype;
372 --
373 Cursor csr_LE_Details (csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
374 SELECT o1.name le_name
375 ,hoi2.org_information1 org_number
376 ,hoi2.org_information2 municipal_no
377 ,hoi2.org_information3 industry_status
378 ,hoi2.org_information4 nace_code
379 -- ,hoi2.org_information5 tax_off
380 FROM hr_organization_units o1
381 ,hr_organization_information hoi1
382 ,hr_organization_information hoi2
383 WHERE o1.business_group_id = l_business_group_id
384 AND hoi1.organization_id = o1.organization_id
385 AND hoi1.organization_id = csr_v_legal_emp_id
386 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
387 AND hoi1.org_information_context = 'CLASS'
388 AND o1.organization_id = hoi2.organization_id
389 AND hoi2.org_information_context = 'NO_LEGAL_EMPLOYER_DETAILS' ;
390 --
391 rg_LE_Details csr_LE_Details%rowtype;
392 --
393 Cursor csr_LE_Contact ( csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
394 SELECT hoi2.org_information2 email
395 ,hoi3.org_information2 phone
396 FROM hr_organization_units o1
397 ,hr_organization_information hoi1
398 ,hr_organization_information hoi2
399 ,hr_organization_information hoi3
400 WHERE o1.business_group_id = l_business_group_id
401 AND hoi1.organization_id = o1.organization_id
402 AND hoi1.organization_id = csr_v_legal_emp_id
403 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
404 AND hoi1.org_information_context = 'CLASS'
405 AND hoi2.organization_id (+) = o1.organization_id
406 AND hoi2.org_information_context(+) = 'ORG_CONTACT_DETAILS'
407 AND hoi2.org_information1(+) = 'EMAIL'
408 AND hoi3.organization_id (+) = o1.organization_id
409 AND hoi3.org_information_context(+) = 'ORG_CONTACT_DETAILS'
410 AND hoi3.org_information1(+) = 'PHONE';
411 --
412 rg_LE_Contact csr_LE_Contact%rowtype;
413 --
414 Cursor csr_LE_addr ( csr_v_legal_emp_id hr_organization_information.organization_id%TYPE) IS
415 SELECT hoi1.address_line_1 address_line_1
416 ,hoi1.address_line_2 address_line_2
417 ,hoi1.address_line_3 address_line_3
418 ,hoi1.postal_code postal_code
419 ,SUBSTR(hlu.meaning, INSTR(hlu.meaning,' ', 1,1), LENGTH(hlu.meaning)-(INSTR(hlu.meaning,' ', 1,1)-1)) postal_office
420 FROM hr_organization_units o1
421 ,hr_locations hoi1
422 ,hr_organization_information hoi2
423 ,hr_lookups hlu
424 WHERE o1.business_group_id = l_business_group_id
425 AND hoi1.location_id = o1.location_id
426 AND hoi2.organization_id = o1.organization_id
427 AND hoi2.organization_id = csr_v_legal_emp_id
428 AND hoi2.org_information1 = 'HR_LEGAL_EMPLOYER'
429 AND hoi2.org_information_context = 'CLASS'
430 AND hlu.lookup_type = 'NO_POSTAL_CODE'
431 AND hlu.enabled_flag = 'Y'
432 AND hlu.lookup_code = hoi1.POSTAL_CODE;
433 --
434 rg_LE_addr csr_LE_addr%rowtype;
435 --
436 CURSOR csr_prepaid_assignments_le(p_payroll_action_id NUMBER,
437 p_legal_employer_id NUMBER,
438 l_start_date DATE,
439 l_end_date DATE) IS
440 SELECT DISTINCT act.assignment_id assignment_id
441 FROM pay_payroll_actions ppa
442 ,pay_payroll_actions appa
443 ,pay_payroll_actions appa2
444 ,pay_assignment_actions act
445 ,pay_assignment_actions act1
446 ,pay_action_interlocks pai
447 ,per_all_assignments_f as1
448 ,hr_soft_coding_keyflex hsck
449 WHERE ppa.payroll_action_id = p_payroll_action_id
450 AND appa.effective_date BETWEEN l_start_date AND l_end_date
451 AND appa.action_type IN ('R','Q')
452 -- Payroll Run or Quickpay Run
453 AND act.payroll_action_id = appa.payroll_action_id
454 AND act.source_action_id IS NULL -- Master Action
455 AND as1.assignment_id = act.assignment_id
456 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
457 AND act.action_status IN ('C','S') -- 10229512
458 AND act.assignment_action_id = pai.locked_action_id
459 AND act1.assignment_action_id = pai.locking_action_id
460 AND act1.action_status IN ('C','S') -- 10229512
461 AND act1.payroll_action_id = appa2.payroll_action_id
462 AND appa2.action_type IN ('P','U')
463 AND appa2.effective_date BETWEEN l_start_date AND l_end_date
464 -- Prepayments or Quickpay Prepayments
465 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
466 AND act.TAX_UNIT_ID = p_legal_employer_id
467 AND hsck.SOFT_CODING_KEYFLEX_ID = as1.SOFT_CODING_KEYFLEX_ID
468 AND EXISTS (SELECT hoi1.organization_id
469 FROM hr_organization_units o1
470 ,hr_organization_information hoi1
471 ,hr_organization_information hoi2
472 ,hr_organization_information hoi3
473 ,hr_organization_information hoi4
474 WHERE hoi1.organization_id = o1.organization_id
475 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
476 AND hoi1.org_information_context = 'CLASS'
477 AND o1.organization_id = hoi2.org_information1
478 AND hoi2.org_information_context ='NO_LOCAL_UNITS'
479 AND hoi2.organization_id = hoi3.organization_id
480 AND hoi3.org_information_context ='CLASS'
481 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
482 aND hoi3.organization_id = p_legal_employer_id
483 AND hoi1.organization_id = hoi4.organization_id
484 AND hoi4.org_information_context ='NO_LOCAL_UNIT_DETAILS'
485 AND hoi4.org_information5 = 'N'
486 AND to_char(hoi1.organization_id) = hsck.segment2 );
487 --
488 CURSOR csr_prepaid_assignments_lu(p_payroll_action_id NUMBER
489 ,p_legal_employer_id NUMBER
490 ,p_local_unit_id NUMBER
491 ,l_start_date DATE
492 ,l_end_date DATE
493 ,p_employee_id NUMBER) IS --14260836
494 SELECT DISTINCT act.assignment_id assignment_id
495 FROM pay_payroll_actions ppa
496 ,pay_payroll_actions appa
497 ,pay_payroll_actions appa2
498 ,pay_assignment_actions act
499 ,pay_assignment_actions act1
500 ,pay_action_interlocks pai
501 ,per_all_assignments_f as1
502 ,hr_soft_coding_keyflex hsck
503 WHERE ppa.payroll_action_id = p_payroll_action_id
504 AND appa.effective_date BETWEEN l_start_date AND l_end_date
505 AND appa.action_type IN ('R','Q')
506 -- Payroll Run or Quickpay Run
507 AND act.payroll_action_id = appa.payroll_action_id
508 AND act.source_action_id IS NULL -- Master Action
509 AND as1.assignment_id = act.assignment_id
510 AND as1.person_id = nvl(p_employee_id,as1.person_id) --14260836
511 AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
512 AND act.action_status IN ('C','S') -- 10229512
513 AND act.assignment_action_id = pai.locked_action_id
514 AND act1.assignment_action_id = pai.locking_action_id
515 AND act1.action_status IN ('C','S') -- 10229512
516 AND act1.payroll_action_id = appa2.payroll_action_id
517 AND appa2.action_type IN ('P','U')
518 AND appa2.effective_date BETWEEN l_start_date AND l_end_date
519 -- Prepayments or Quickpay Prepayments
520 AND hsck.soft_coding_keyflex_id = as1.soft_coding_keyflex_id
521 AND hsck.segment2 = to_char(p_local_unit_id)
522 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
523 AND act.TAX_UNIT_ID = p_legal_employer_id ;
524 --
525 --14260836
526 CURSOR csr_action_type (p_employee_id NUMBER,
527 l_start_date DATE,
528 l_end_date DATE,
529 p_local_unit_id NUMBER,
530 p_legal_employer_id NUMBER) IS
531 SELECT fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) asg_action_id
532 FROM pay_assignment_actions paa,
533 pay_payroll_actions ppa,
534 per_all_assignments_f paaf,
535 hr_soft_coding_keyflex hsck
536 WHERE ppa.payroll_action_id = paa.payroll_action_id
537 and paa.assignment_id = paaf.assignment_id
538 and ppa.action_type in ('R','Q')
539 and paa.source_action_id is null
540 and ppa.effective_date between l_start_date AND l_end_date
541 and paaf.person_id = nvl(p_employee_id,paaf.person_id)
542 and hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
543 and hsck.segment2 = to_char(p_local_unit_id)
544 and paa.tax_unit_id = p_legal_employer_id ;
545 --14260836
546 --
547 CURSOR csr_get_mun_num(p_assignment_id NUMBER
548 ,p_effective_date DATE) IS
549 SELECT eev1.screen_entry_value screen_entry_value
550 FROM per_all_assignments_f asg1
551 ,per_all_assignments_f asg2
552 ,per_all_people_f per
553 ,pay_element_links_f el
554 ,pay_element_types_f et
555 ,pay_input_values_f iv1
556 ,pay_element_entries_f ee
557 ,pay_element_entry_values_f eev1
558 WHERE asg1.assignment_id = p_assignment_id
559 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
560 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date
561 AND p_effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
562 AND per.person_id = asg1.person_id
563 AND asg2.person_id = per.person_id
564 AND asg2.primary_flag = 'Y'
565 AND et.element_name = 'Tax Card'
566 AND et.legislation_code = 'NO'
567 AND iv1.element_type_id = et.element_type_id
568 AND iv1.name = 'Tax Municipality'
569 AND el.business_group_id = per.business_group_id
570 AND el.element_type_id = et.element_type_id
571 AND ee.assignment_id = asg2.assignment_id
572 AND ee.element_link_id = el.element_link_id
573 AND eev1.element_entry_id = ee.element_entry_id
574 AND eev1.input_value_id = iv1.input_value_id
575 AND p_effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
576 AND p_effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
577 --
578 CURSOR csr_get_mun_dtls(p_municipal_no VARCHAR2, l_effective_date DATE) IS
579 SELECT hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'ZONE') zone
580 ,hr_general.decode_lookup('NO_TAX_MUNICIPALITY',
581 hr_de_general.get_uci(l_effective_date,t.user_table_id,r.user_row_id,'MAPPING_ID')) municipal_name
582 FROM pay_user_tables t
583 ,pay_user_rows_f r
584 WHERE t.user_table_name = 'NO_TAX_MUNICIPALITY'
585 AND t.legislation_code = 'NO'
586 AND r.user_table_id = t.user_table_id
587 AND r.row_low_range_or_name = p_municipal_no
588 AND l_effective_date BETWEEN r.effective_start_date AND r.effective_end_date;
589 --
590 rg_get_mun_dtls csr_get_mun_dtls%ROWTYPE;
591 rg_get_mun_dtls1 csr_get_mun_dtls%ROWTYPE; --14260836
592 --
593 CURSOR csr_lu_dtls(p_legal_employer_id NUMBER) IS
594 SELECT hoi1.organization_id lu_id
595 FROM hr_organization_units o1
596 ,hr_organization_information hoi1
597 ,hr_organization_information hoi2
598 ,hr_organization_information hoi3
599 ,hr_organization_information hoi4
600 WHERE hoi1.organization_id = o1.organization_id
601 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
602 AND hoi1.org_information_context = 'CLASS'
603 AND o1.organization_id = hoi2.org_information1
604 AND hoi2.org_information_context ='NO_LOCAL_UNITS'
605 AND hoi2.organization_id = hoi3.organization_id
606 AND hoi3.org_information_context = 'CLASS'
607 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
608 AND hoi3.organization_id = p_legal_employer_id
609 AND hoi1.organization_id = hoi4.organization_id
610 AND hoi4.org_information_context = 'NO_LOCAL_UNIT_DETAILS'
611 AND hoi4.org_information5 = 'N';
612 --
613 CURSOR csr_Local_Unit_EA(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
614 ,p_date_earned DATE) IS
615 SELECT to_number(hoi2.org_information4)
616 FROM hr_organization_units o1
617 ,hr_organization_information hoi1
618 ,hr_organization_information hoi2
619 WHERE o1.business_group_id = l_business_group_id
620 AND hoi1.organization_id = o1.organization_id
621 AND hoi1.organization_id = csr_v_local_unit_id
622 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
623 AND hoi1.org_information_context = 'CLASS'
624 AND o1.organization_id = hoi2.organization_id
625 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
626 AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
627 AND fnd_date.canonical_to_date(hoi2.org_information3);
628 --
629 Cursor csr_Legal_Emp_EA(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
630 ,p_date_earned DATE) IS
631 SELECT to_number(hoi2.org_information4)
632 FROM hr_organization_units o1
633 ,hr_organization_information hoi1
634 ,hr_organization_information hoi2
635 WHERE o1.business_group_id = l_business_group_id
636 AND hoi1.organization_id = o1.organization_id
637 AND hoi1.organization_id = csr_v_legal_emp_id
638 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
639 AND hoi1.org_information_context = 'CLASS'
640 AND o1.organization_id = hoi2.organization_id
641 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
642 AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi2.org_information2)
643 AND fnd_date.canonical_to_date(hoi2.org_information3);
644 --
645 CURSOR csr_Local_Unit_EL(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
646 ,p_date_earned DATE) IS
647 SELECT SUM(hoi2.org_information1) exempt_limit
648 ,SUM(hoi2.org_information4) economic_aid
649 FROM hr_organization_units o1
650 ,hr_organization_information hoi1
651 ,hr_organization_information hoi2
652 WHERE o1.business_group_id = l_business_group_id
653 AND hoi1.organization_id = o1.organization_id
654 AND hoi1.organization_id = csr_v_local_unit_id
655 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
656 AND hoi1.org_information_context = 'CLASS'
657 AND o1.organization_id = hoi2.organization_id
658 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
659 AND trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
660 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
661 --
662 rg_Local_Unit_EL csr_Local_Unit_EL%ROWTYPE;
663 --
664 Cursor csr_Legal_Emp_EL(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
665 ,p_date_earned DATE) IS
666 SELECT SUM(hoi2.org_information1) exempt_limit
667 ,SUM(hoi2.org_information4) economic_aid
668 FROM hr_organization_units o1
669 ,hr_organization_information hoi1
670 ,hr_organization_information hoi2
671 WHERE o1.business_group_id = l_business_group_id
672 AND hoi1.organization_id = o1.organization_id
673 AND hoi1.organization_id = csr_v_legal_emp_id
674 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
675 AND hoi1.org_information_context = 'CLASS'
676 AND o1.organization_id = hoi2.organization_id
677 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
678 AND trunc(p_date_earned,'Y') >= fnd_date.canonical_to_date(hoi2.org_information2)
679 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
680 --
681 rg_Legal_Emp_EL csr_Legal_Emp_EL%ROWTYPE;
682 --
683 CURSOR csr_Local_Unit_EL_after(csr_v_local_unit_id hr_organization_information.organization_id%TYPE
684 ,p_date_earned DATE) IS
685 SELECT SUM(hoi2.org_information4) economic_aid
686 FROM hr_organization_units o1
687 ,hr_organization_information hoi1
688 ,hr_organization_information hoi2
689 WHERE o1.business_group_id = l_business_group_id
690 AND hoi1.organization_id = o1.organization_id
691 AND hoi1.organization_id = csr_v_local_unit_id
692 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
693 AND hoi1.org_information_context = 'CLASS'
694 AND o1.organization_id = hoi2.organization_id
695 AND hoi2.org_information_context = 'NO_NI_EXEMPTION_LIMIT'
696 AND p_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
697 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
698 --
699 rg_Local_Unit_EL_after csr_Local_Unit_EL_after%ROWTYPE;
700 --
701 Cursor csr_Legal_Emp_EL_after(csr_v_legal_emp_id hr_organization_information.organization_id%TYPE
702 ,p_date_earned DATE) IS
703 SELECT SUM(hoi2.org_information4) economic_aid
704 FROM hr_organization_units o1
705 ,hr_organization_information hoi1
706 ,hr_organization_information hoi2
707 WHERE o1.business_group_id = l_business_group_id
708 AND hoi1.organization_id = o1.organization_id
709 AND hoi1.organization_id = csr_v_legal_emp_id
710 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
711 AND hoi1.org_information_context = 'CLASS'
712 AND o1.organization_id = hoi2.organization_id
713 AND hoi2.org_information_context ='NO_NI_EXEMPTION_LIMIT'
714 AND p_date_earned > fnd_date.canonical_to_date(hoi2.org_information2)
715 AND trunc(add_months(p_date_earned,12),'Y') < fnd_date.canonical_to_date(hoi2.org_information3);
716 --
717 rg_Legal_Emp_EL_after csr_Legal_Emp_EL_after%ROWTYPE;
718 --
719 CURSOR csr_global_value (p_global_name VARCHAR2 , p_date_earned DATE) IS
720 SELECT global_value
721 FROM ff_globals_f
722 WHERE global_name = p_global_name
723 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
724 --
725 BEGIN
726 --
727 g_debug:=true;
728 --
729 IF g_debug THEN
730 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
731 END IF;
732 --
733 p_sql :='SELECT DISTINCT person_id
734 FROM per_people_f ppf
735 ,pay_payroll_actions ppa
736 WHERE ppa.payroll_action_id = :payroll_action_id
737 AND ppa.business_group_id = ppf.business_group_id
738 ORDER BY ppf.person_id';
739 --
740 pay_no_arc_rsea_07.get_all_parameters(p_payroll_action_id
741 ,l_business_group_id
742 ,l_legal_employer_id
743 ,l_local_unit_id
744 ,l_effective_date
745 ,l_archive
746 ,l_report_type -- Bug#9579261 fix
747 ,l_employee_id); -- 14260836
748 --
749 l_period:= to_char(ceil(to_number(to_char(l_effective_date,'MM'))/ 2));
750 l_year:=to_char(l_effective_date,'YYYY');
751 l_reporting_end_date := LAST_DAY(TO_DATE(LPAD(l_period*2,2,'0')||l_year,'MMYYYY'));
752 l_reporting_start_date :=ADD_MONTHS( l_reporting_end_date , -2 ) + 1;
753
754 --14260836
755 IF l_employee_id IS NOT NULL THEN
756 --
757 OPEN csr_action_type ( l_employee_id,
758 l_reporting_start_date,
759 l_reporting_END_date,
760 l_local_unit_id,
761 l_legal_employer_id);
762 FETCH csr_action_type INTO l_assignment_action;
763 CLOSE csr_action_type;
764 l_bal_dim := '_PER_TU_LU_BIMONTH';
765 l_bal_dim1 := '_PER_TU_MC_LU_BIMONTH';
766 --
767 ELSE
768 --
769 l_assignment_action := NULL;
770 l_bal_dim := '_TU_LU_BIMONTH';
771 l_bal_dim1 := '_TU_MC_LU_BIMONTH';
772 --
773 END IF;
774 --14260836
775 --
776 ------------------------------ --
777 -- Employer Contribution Section --
778 ------------------------------ --
779 -- Fetching the global value NO_NI_FOREIGN_MARINER_AMOUNT*/
780 OPEN csr_global_value('NO_NI_FOREIGN_MARINER_AMOUNT' , l_reporting_end_date ) ;
781 FETCH csr_global_value INTO l_fe_fm_amount;
782 CLOSE csr_global_value;
783 --
784 IF l_archive = 'Y' THEN
785 --
786 SELECT count(*) INTO l_count
787 FROM pay_action_information
788 WHERE action_context_id = p_payroll_action_id
789 AND action_context_type = 'PA'
790 AND action_information_category = 'EMEA REPORT INFORMATION'
791 AND action_information1 = 'PYNORSEA';
792 --
793 IF l_count < 1 then
794 /* Pick up the details belonging to Legal Employer */
795 OPEN csr_LE_Details(l_legal_employer_id);
796 FETCH csr_LE_Details INTO rg_LE_Details;
797 CLOSE csr_LE_Details;
798 --
799 l_industry_status:= rg_LE_Details.industry_status ;
800 l_nace_code := rg_LE_Details.nace_code ;
801 --
802 IF l_local_unit_id IS NOT NULL THEN
803 /* Pick up the details belonging to Local Unit */
804 OPEN csr_LU_Details( l_local_unit_id);
805 FETCH csr_LU_Details INTO rg_LU_Details;
806 CLOSE csr_LU_Details;
807 --
808 l_lu_name := rg_LU_Details.lu_name;
809 --l_nace_code := rg_LU_Details.nace_code; --14260836
810 l_industry_status := rg_LU_Details.industry_status;
811 --
812 END IF ;
813 /* Pick up the contact details belonging to Legal Employer */
814 OPEN csr_LE_contact(l_legal_employer_id);
815 FETCH csr_LE_contact INTO rg_LE_contact;
816 CLOSE csr_LE_contact;
817 /* Pick up the Address details belonging to Legal Employer */
818 OPEN csr_LE_addr(l_legal_employer_id);
819 FETCH csr_LE_addr INTO rg_LE_addr;
820 CLOSE csr_LE_addr;
821 --
822 pay_balance_pkg.set_context('TAX_UNIT_ID', l_legal_employer_id);
823 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_reporting_end_date));
824 --
825 IF l_local_unit_id IS NOT NULL THEN
826 /* Pick up the Exemption Limit details belonging to Local Unit*/
827 pay_balance_pkg.set_context('LOCAL_UNIT_ID', l_local_unit_id);
828 --
829 OPEN csr_Local_Unit_EA(l_local_unit_id, l_reporting_end_date);
830 FETCH csr_Local_Unit_EA INTO l_el;
831 CLOSE csr_Local_Unit_EA;
832 --
833 OPEN csr_Local_Unit_EL(l_local_unit_id, l_reporting_end_date);
834 FETCH csr_Local_Unit_EL INTO rg_Local_Unit_EL;
835 CLOSE csr_Local_Unit_EL;
836 --
837 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_LU_YTD') ;
838 l_el_a := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
839 l_el_a := nvl(rg_Local_Unit_EL.exempt_limit,0) - nvl(rg_Local_Unit_EL.economic_aid,0) - nvl(l_el_a,0);
840 --
841 OPEN csr_Local_Unit_EL_after( l_local_unit_id , l_reporting_end_date);
842 FETCH csr_Local_Unit_EL_after INTO rg_Local_Unit_EL_after;
843 CLOSE csr_Local_Unit_EL_after;
844 --
845 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', l_bal_dim) ; --14260836
846
847 l_el_b := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE); --14260836
848 l_el_b := l_el_a + nvl(l_el_b,0) + nvl(rg_Local_Unit_EL_after.economic_aid,0);
849 --
850 ELSE
851 /* Pick up the Exemption Limit details belonging to Employer*/
852 OPEN csr_Legal_Emp_EA( l_legal_employer_id , l_reporting_end_date);
853 FETCH csr_Legal_Emp_EA INTO l_el;
854 CLOSE csr_Legal_Emp_EA;
855 --
856 OPEN csr_Legal_Emp_EL( l_legal_employer_id , l_reporting_end_date);
857 FETCH csr_Legal_Emp_EL INTO rg_Legal_Emp_EL;
858 CLOSE csr_Legal_Emp_EL;
859 --
860 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_YTD') ;
861 l_el_a := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
862 l_el_a := nvl(rg_Legal_Emp_EL.exempt_limit,0) - nvl(rg_Legal_Emp_EL.economic_aid,0) - nvl(l_el_a,0);
863 --
864 OPEN csr_Legal_Emp_EL_after( l_legal_employer_id , l_reporting_end_date);
865 FETCH csr_Legal_Emp_EL_after INTO rg_Legal_Emp_EL_after;
866 CLOSE csr_Legal_Emp_EL_after;
867 --
868 l_def_bal_id := get_defined_balance_id('Employer Contribution Exemption Limit Used', '_TU_BIMONTH') ;
869 l_el_b := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
870 l_el_b := l_el_a + nvl(l_el_b,0) + nvl(rg_Legal_Emp_EL_after.economic_aid,0);
871 --
872 END IF;
873 --
874 l_el_a := greatest(l_el_a, 0);
875 l_el_b := greatest(l_el_b, 0);
876 --
877 --14260836
878 /* Getting Munucipatiy Name */
879 OPEN csr_get_mun_dtls(rg_LE_Details.municipal_no, l_reporting_end_date);
880 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls1;
881 CLOSE csr_get_mun_dtls;
882 --14260836
883
884 /* Inserting header details belonging to Employer*/
885 pay_action_information_api.create_action_information (
886 p_action_information_id => l_action_info_id
887 ,p_action_context_id => p_payroll_action_id
888 ,p_action_context_type => 'PA'
889 ,p_object_version_number => l_ovn
890 ,p_effective_date => l_effective_date
891 ,p_source_id => NULL
892 ,p_source_text => NULL
893 ,p_action_information_category => 'EMEA REPORT INFORMATION'
894 ,p_action_information1 => 'PYNORSEA'
895 ,p_action_information2 => l_legal_employer_id
896 ,p_action_information3 => l_period || l_year
897 ,p_action_information4 => rg_LE_Details.org_number
898 ,p_action_information5 => rg_LE_Details.municipal_no
899 ,p_action_information6 => rg_LE_Details.le_name
900 ,p_action_information7 => rg_LE_addr.address_line_1
901 ,p_action_information8 => rg_LE_addr.address_line_2
902 ,p_action_information9 => rg_LE_addr.postal_code
903 ,p_action_information10 => rg_LE_addr.postal_office
904 ,p_action_information11 => rg_LE_Contact.email
905 ,p_action_information12 => rg_LE_Contact.phone
906 ,p_action_information13 => null -- for Tax unit details
907 ,p_action_information14 => null -- for Tax unit details
908 ,p_action_information15 => null -- for Tax unit details
909 ,p_action_information16 => null -- for Tax unit details
910 ,p_action_information17 => l_industry_status
911 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_el,0)) -- Other economic support (Economic Aid)
912 ,p_action_information19 => l_nace_code
913 ,p_action_information20 => rg_LE_addr.address_line_3
914 ,p_action_information21 => fnd_number.number_to_canonical(NVL(l_el_b,0)) -- remaining exemption limit prev rep term
915 ,p_action_information22 => fnd_number.number_to_canonical(NVL(l_el_a,0)) -- remaining exemption limit after rep term
916 ,p_action_information23 => l_report_type -- Bug#9579261 fix
917 ,p_action_information24 => fnd_number.number_to_canonical(NVL(l_fe_fm_amount,0)) -- Bug#9579261 fix
918 ,p_action_information25 => rg_get_mun_dtls1.municipal_name --14260836
919 );
920 --
921 /* Inserting the selection criteria for generating the report*/
922 pay_action_information_api.create_action_information (
923 p_action_information_id => l_action_info_id
924 ,p_action_context_id => p_payroll_action_id
925 ,p_action_context_type => 'PA'
926 ,p_object_version_number => l_ovn
927 ,p_effective_date => l_effective_date
928 ,p_source_id => NULL
929 ,p_source_text => NULL
930 ,p_action_information_category => 'EMEA REPORT DETAILS'
931 ,p_action_information1 => 'PYNORSEA'
932 ,p_action_information2 => rg_LE_Details.le_name
933 ,p_action_information3 => l_lu_name
934 ,p_action_information4 => l_period
935 ,p_action_information5 => l_year
936 );
937 --
938 IF g_debug THEN
939 hr_utility.set_location(' Inside Procedure RANGE_CODE',20);
940 END IF;
941 /* Inserting municipal codes for the Legal Employer in a PL/SQL table */
942 IF l_local_unit_id IS NULL THEN
943 --
944 l_counter := 0;
945 l_status := 0;
946 --
947 FOR prepaid_assignments_le_rec IN csr_prepaid_assignments_le(p_payroll_action_id
948 ,l_legal_employer_id
949 ,l_reporting_start_date
950 ,l_reporting_end_date)
951 LOOP
952 --
953 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id
954 ,l_reporting_start_date)
955 LOOP
956 --
957 IF l_counter > 0 THEN
958 --
959 FOR i IN 1 .. l_counter LOOP
960 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
961 l_status:= 1;
962 EXIT ;
963 END IF;
964 END LOOP;
965 --
966 END IF;
967 --
968 IF l_status= 0 THEN
969 l_counter := l_counter + 1;
970 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
971 END IF;
972 --
973 l_status := 0;
974 --
975 END LOOP;
976 --
977 l_status := 0;
978 --
979 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_le_rec.assignment_id
980 ,l_reporting_end_date)
981 LOOP
982 --
983 IF l_counter > 0 THEN
984 --
985 FOR i IN 1 .. l_counter LOOP
986 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
987 l_status:= 1;
988 EXIT ;
989 END IF;
990 END LOOP;
991 END IF;
992 --
993 IF l_status= 0 THEN
994 l_counter := l_counter + 1;
995 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
996 END IF;
997 --
998 l_status := 0;
999 --
1000 END LOOP;
1001 --
1002 END LOOP;
1003 --
1004 ELSE -- IF LU is specified in parameters
1005 /* Inserting municipal codes for the Local Unit in a PL/SQL table */
1006 l_counter := 0;
1007 l_status := 0;
1008 --
1009 FOR prepaid_assignments_lu_rec IN csr_prepaid_assignments_lu(p_payroll_action_id
1010 ,l_legal_employer_id
1011 ,l_local_unit_id
1012 ,l_reporting_start_date
1013 ,l_reporting_END_date
1014 ,l_employee_id) --14260836
1015 LOOP
1016 --
1017 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id
1018 ,l_reporting_start_date)
1019 LOOP
1020 --
1021 IF l_counter > 0 THEN
1022 FOR i IN 1 .. l_counter LOOP
1023 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
1024 l_status:= 1;
1025 EXIT ;
1026 END IF;
1027 END LOOP;
1028 END IF;
1029 --
1030 IF l_status= 0 THEN
1031 l_counter := l_counter + 1;
1032 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
1033 END IF;
1034 --
1035 l_status := 0;
1036 --
1037 END LOOP;
1038 --
1039 l_status := 0;
1040 --
1041 FOR get_mun_num_rec IN csr_get_mun_num(prepaid_assignments_lu_rec.assignment_id
1042 ,l_reporting_end_date)
1043 LOOP
1044 IF l_counter > 0 THEN
1045 FOR i IN 1 .. l_counter LOOP
1046 IF gmunicipaldata(i).municipalcode = get_mun_num_rec.screen_entry_value THEN
1047 l_status:= 1;
1048 EXIT ;
1049 END IF;
1050 END LOOP;
1051 END IF;
1052 --
1053 IF l_status= 0 THEN
1054 l_counter := l_counter + 1;
1055 gmunicipaldata(l_counter).municipalcode:=get_mun_num_rec.screen_entry_value;
1056 END IF;
1057 --
1058 l_status := 0;
1059 --
1060 END LOOP;
1061 --
1062 END LOOP;
1063 --
1064 END IF ; -- plsql table now contains all the tax municipal codes
1065 --
1066 IF g_debug THEN
1067 hr_utility.set_location(' Inside Procedure RANGE_CODE',40);
1068 END IF;
1069 --
1070 -- ----------------------- --
1071 -- Withholding Tax Section --
1072 -- ----------------------- --
1073 --
1074 /* Setting contexts for balances*/
1075 FOR i IN 1 .. l_counter LOOP
1076 --
1077 l_municipal_no:=gmunicipaldata(i).municipalcode;
1078 --
1079 IF l_municipal_no IS NOT NULL THEN
1080 --
1081 pay_balance_pkg.set_context('SOURCE_TEXT2', l_municipal_no);
1082 --
1083 l_sum_tax_value := 0;
1084 /* Setting municipality details for balances*/
1085 OPEN csr_get_mun_dtls(l_municipal_no, l_reporting_end_date);
1086 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1087 CLOSE csr_get_mun_dtls;
1088 --
1089 l_municipal_name := rg_get_mun_dtls.municipal_name;
1090 /* Fetching balance values related to employer contributions report*/
1091 IF l_local_unit_id IS NULL THEN
1092 FOR lu_dtls_rec IN csr_lu_dtls(l_legal_employer_id)
1093 LOOP
1094 pay_balance_pkg.set_context('LOCAL_UNIT_ID',lu_dtls_rec.lu_id);
1095 l_tax_value := 0;
1096 l_def_bal_id := get_defined_balance_id('Tax','_TU_MC_LU_BIMONTH');
1097 l_tax_value := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1098 l_sum_tax_value := l_sum_tax_value + l_tax_value;
1099 END LOOP;
1100 ELSE
1101 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
1102 l_tax_value := 0;
1103 l_def_bal_id := get_defined_balance_id('Tax',l_bal_dim1); --14260836
1104 l_tax_value := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE); --14260836
1105 l_sum_tax_value := l_sum_tax_value + l_tax_value;
1106 END IF;
1107
1108 l_sum_tax_value := FLOOR(l_sum_tax_value); --14260836
1109 l_sum_tax_value_rep := GREATEST(l_sum_tax_value,0); --14260836
1110 --
1111 pay_action_information_api.create_action_information (
1112 p_action_information_id => l_action_info_id
1113 ,p_action_context_id => p_payroll_action_id
1114 ,p_action_context_type => 'PA'
1115 ,p_object_version_number => l_ovn
1116 ,p_effective_date => l_effective_date
1117 ,p_source_id => NULL
1118 ,p_source_text => NULL
1119 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1120 ,p_action_information1 => 'PYNORSEA-WT'
1121 ,p_action_information2 => l_municipal_no
1122 ,p_action_information3 => l_municipal_name
1123 ,p_action_information4 => fnd_number.number_to_canonical(NVL(l_sum_tax_value,0))
1124 ,p_action_information5 => fnd_number.number_to_canonical(NVL(l_sum_tax_value_rep,0))
1125 );
1126 --
1127 l_municipal_no:=NULL;
1128 --
1129 END IF;
1130 --
1131 END LOOP; -- plsql table for all tax municipal exhausted
1132 --
1133 IF g_debug THEN
1134 hr_utility.set_location(' Inside Procedure RANGE_CODE',60);
1135 END IF;
1136
1137
1138 --
1139 IF l_local_unit_id IS NULL THEN
1140 --
1141 FOR lu_dtls_rec IN csr_lu_dtls(l_legal_employer_id)
1142 LOOP
1143 --
1144 OPEN csr_LU_Details(lu_dtls_rec.lu_id);
1145 FETCH csr_LU_Details INTO rg_LU_Details;
1146 CLOSE csr_LU_Details;
1147 --
1148 OPEN csr_get_mun_dtls(rg_LU_Details.municipal_no, l_reporting_end_date);
1149 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1150 CLOSE csr_get_mun_dtls;
1151 --
1152 l_base_base := 0;
1153 l_base_amt := 0;
1154 l_reimb_base := 0;
1155 l_reimb_amt := 0;
1156 l_utl1_base := 0;
1157 l_utl1_amt := 0;
1158 l_utr1_base := 0;
1159 l_utr1_amt := 0;
1160 l_utl2_base := 0;
1161 l_utl2_amt := 0;
1162 l_pension_amount := 0;
1163 --
1164 pay_balance_pkg.set_context('LOCAL_UNIT_ID',lu_dtls_rec.lu_id);
1165 -- bug 16512355
1166 -- EC Base
1167 /* l_def_bal_id := get_defined_balance_id('Employer Contribution Base','_TU_LU_BIMONTH') ;
1168 l_base_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836 */
1169 --
1170 l_def_bal_id := get_defined_balance_id('Employer Contribution Base 2','_TU_LU_BIMONTH') ;
1171 l_base_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); -- bug 16512355
1172 --
1173 l_def_bal_id := get_defined_balance_id('Employer Contribution','_TU_LU_BIMONTH') ;
1174 l_base_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1175 --
1176 -- Reimbursed from SS
1177 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed Base','_TU_LU_BIMONTH') ;
1178 l_reimb_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1179 --
1180 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1181 l_reimb_base := FLOOR(l_reimb_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1182 --
1183 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1184 l_reimb_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1185 --
1186 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed','_TU_LU_BIMONTH') ;
1187 l_reimb_amt := FLOOR(l_reimb_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1188 --
1189 -- EC Base, Special EC percentage -UTL1
1190 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base','_TU_LU_BIMONTH') ;
1191 l_utl1_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1192 --
1193 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage','_TU_LU_BIMONTH') ;
1194 l_utl1_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1195 --
1196 -- Reimbursed from SS, Special EC Percentage - UTR1
1197 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimb Base','_TU_LU_BIMONTH') ;
1198 l_utr1_base := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1199 --
1200 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Benefit Reimbursed Base','_TU_LU_BIMONTH') ;
1201 l_utr1_base := FLOOR(l_utr1_base + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1202 --
1203 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimbursed','_TU_LU_BIMONTH') ;
1204 l_utr1_amt := pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE);
1205 --
1206 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Benefit Reimbursed','_TU_LU_BIMONTH') ;
1207 l_utr1_amt := FLOOR(l_utr1_amt + pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1208 --
1209 -- EC Base Special monthly amount - UTL2
1210 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Base','_TU_LU_BIMONTH') ;
1211 l_utl2_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)/ l_fe_fm_amount); --14260836
1212 --
1213 l_def_bal_id := get_defined_balance_id('Employer Contribution Special','_TU_LU_BIMONTH') ;
1214 l_utl2_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1215
1216 --
1217 -- EC Base Pension
1218 l_def_bal_id := get_defined_balance_id('Employers Pension Premium','_TU_LU_BIMONTH') ;
1219 l_pension_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, NULL, FALSE)); --14260836
1220 --
1221 --14260836
1222 l_rate := fnd_number.canonical_to_number
1223 (hruserdt.get_table_value
1224 (l_business_group_id,
1225 'NO_NIS_ZONE_RATES',
1226 'NIS Rates',
1227 rg_get_mun_dtls.zone,
1228 l_reporting_end_date
1229 )
1230 );
1231 --14260836
1232 l_pension_amount := l_rate/100 * NVL(l_pension_base,0);
1233 l_base_amt := l_base_amt - l_pension_amount;
1234
1235 pay_action_information_api.create_action_information (
1236 p_action_information_id => l_action_info_id
1237 ,p_action_context_id => p_payroll_action_id
1238 ,p_action_context_type => 'PA'
1239 ,p_object_version_number => l_ovn
1240 ,p_effective_date => l_effective_date
1241 ,p_source_id => NULL
1242 ,p_source_text => NULL
1243 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1244 ,p_action_information1 => 'PYNORSEA-EC'
1245 ,p_action_information2 => l_legal_employer_id
1246 ,p_action_information3 => lu_dtls_rec.lu_id
1247 ,p_action_information4 => rg_LU_Details.org_num
1248 ,p_action_information5 => rg_LU_Details.municipal_no
1249 ,p_action_information6 => rg_get_mun_dtls.municipal_name
1250 ,p_action_information7 => rg_get_mun_dtls.zone
1251 ,p_action_information8 => fnd_number.number_to_canonical(NVL(l_base_base-l_pension_base,0))
1252 ,p_action_information9 => fnd_number.number_to_canonical(NVL(l_base_amt,0))
1253 ,p_action_information10 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_base,0)) --14260836
1254 ,p_action_information11 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_amt,0))
1255 ,p_action_information12 => fnd_number.number_to_canonical(NVL(l_utl1_base,0))
1256 ,p_action_information13 => fnd_number.number_to_canonical(NVL(l_utl1_amt,0))
1257 ,p_action_information14 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_base,0)) --14260836
1258 ,p_action_information15 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_amt,0))
1259 ,p_action_information16 => fnd_number.number_to_canonical(NVL(l_utl2_base,0))
1260 ,p_action_information17 => fnd_number.number_to_canonical(NVL(l_utl2_amt,0))
1261 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_pension_base,0))
1262 ,p_action_information19 => fnd_number.number_to_canonical(l_pension_amount) --0 --place for pension amount --14260836
1263 ,p_action_information20 => fnd_number.number_to_canonical(l_rate) --14260836
1264 );
1265 --
1266 END LOOP;
1267 --
1268 ELSE -- LU Specified
1269 --
1270 OPEN csr_LU_Details(l_local_unit_id);
1271 FETCH csr_LU_Details INTO rg_LU_Details;
1272 CLOSE csr_LU_Details;
1273 --
1274 OPEN csr_get_mun_dtls(rg_LU_Details.municipal_no, l_reporting_end_date);
1275 FETCH csr_get_mun_dtls INTO rg_get_mun_dtls;
1276 CLOSE csr_get_mun_dtls;
1277 --
1278 l_base_base := 0;
1279 l_base_amt := 0;
1280 l_reimb_base := 0;
1281 l_reimb_amt := 0;
1282 l_utl1_base := 0;
1283 l_utl1_amt := 0;
1284 l_utr1_base := 0;
1285 l_utr1_amt := 0;
1286 l_utl2_base := 0;
1287 l_utl2_amt := 0;
1288 l_pension_amount := 0;
1289 --
1290 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id);
1291 -- EC Base
1292 /* l_def_bal_id := get_defined_balance_id('Employer Contribution Base',l_bal_dim) ; --14260836
1293 l_base_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); */ --14260836
1294 --
1295 l_def_bal_id := get_defined_balance_id('Employer Contribution Base 2',l_bal_dim) ; --16512355
1296 l_base_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE));
1297 --
1298 l_def_bal_id := get_defined_balance_id('Employer Contribution',l_bal_dim) ; --14260836
1299 l_base_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1300 --
1301 -- Reimbursed from SS
1302 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed Base',l_bal_dim) ; --14260836
1303 l_reimb_base := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE);
1304 --
1305 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed Base',l_bal_dim) ; --14260836
1306 l_reimb_base := FLOOR(l_reimb_base + pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1307 --
1308 l_def_bal_id := get_defined_balance_id('Employer Contribution Holiday Pay Reimbursed',l_bal_dim) ; --14260836
1309 l_reimb_amt := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE);
1310 --
1311 l_def_bal_id := get_defined_balance_id('Employer Contribution Benefit Reimbursed',l_bal_dim) ; --14260836
1312 l_reimb_amt := FLOOR(l_reimb_amt + pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1313 --
1314 -- EC Base, Special EC percentage -UTL1
1315 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Base',l_bal_dim) ; --14260836
1316 l_utl1_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1317 --
1318 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage',l_bal_dim) ; --14260836
1319 l_utl1_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1320 --
1321 -- Reimbursed from SS, Special EC Percentage - UTR1
1322 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimb Base',l_bal_dim) ; --14260836
1323 l_utr1_base := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE);
1324 --
1325 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Benefit Reimbursed Base',l_bal_dim) ; --14260836
1326 l_utr1_base := FLOOR(l_utr1_base + pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1327 --
1328 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Perc Holiday Pay Reimbursed',l_bal_dim) ; --14260836
1329 l_utr1_amt := pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE);
1330 --
1331 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Percentage Benefit Reimbursed',l_bal_dim) ; --14260836
1332 l_utr1_amt := FLOOR(l_utr1_amt + pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1333 --
1334 -- EC Base Special monthly amount - UTL2
1335 l_def_bal_id := get_defined_balance_id('Employer Contribution Special Base',l_bal_dim) ; --14260836
1336 l_utl2_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE) / l_fe_fm_amount); --14260836
1337 --
1338 l_def_bal_id := get_defined_balance_id('Employer Contribution Special',l_bal_dim) ; --14260836
1339 l_utl2_amt := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1340
1341 -- EC Base Pension
1342 l_def_bal_id := get_defined_balance_id('Employers Pension Premium',l_bal_dim) ; --14260836
1343 l_pension_base := FLOOR(pay_balance_pkg.get_value(l_def_bal_id, l_assignment_action, FALSE)); --14260836
1344 --
1345 --14260836
1346 l_rate := fnd_number.canonical_to_number
1347 (hruserdt.get_table_value
1348 (l_business_group_id,
1349 'NO_NIS_ZONE_RATES',
1350 'NIS Rates',
1351 rg_get_mun_dtls.zone,
1352 l_reporting_end_date
1353 )
1354 );
1355 --14260836
1356 l_pension_amount := l_rate/100 * NVL(l_pension_base,0);
1357 l_base_amt := l_base_amt - l_pension_amount;
1358
1359 pay_action_information_api.create_action_information (
1360 p_action_information_id => l_action_info_id
1361 ,p_action_context_id => p_payroll_action_id
1362 ,p_action_context_type => 'PA'
1363 ,p_object_version_number => l_ovn
1364 ,p_effective_date => l_effective_date
1365 ,p_source_id => NULL
1366 ,p_source_text => NULL
1367 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1368 ,p_action_information1 => 'PYNORSEA-EC'
1369 ,p_action_information2 => l_legal_employer_id
1370 ,p_action_information3 => l_local_unit_id
1371 ,p_action_information4 => rg_LU_Details.org_num
1372 ,p_action_information5 => rg_LU_Details.municipal_no
1373 ,p_action_information6 => rg_get_mun_dtls.municipal_name
1374 ,p_action_information7 => rg_get_mun_dtls.zone
1375 ,p_action_information8 => fnd_number.number_to_canonical(NVL(l_base_base-l_pension_base,0))
1376 ,p_action_information9 => fnd_number.number_to_canonical(NVL(l_base_amt,0))
1377 ,p_action_information10 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_base,0)) --14260836
1378 ,p_action_information11 => fnd_number.number_to_canonical(-1 * NVL(l_reimb_amt,0))
1379 ,p_action_information12 => fnd_number.number_to_canonical(NVL(l_utl1_base,0))
1380 ,p_action_information13 => fnd_number.number_to_canonical(NVL(l_utl1_amt,0))
1381 ,p_action_information14 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_base,0)) --14260836
1382 ,p_action_information15 => fnd_number.number_to_canonical(-1 * NVL(l_utr1_amt,0))
1383 ,p_action_information16 => fnd_number.number_to_canonical(NVL(l_utl2_base,0))
1384 ,p_action_information17 => fnd_number.number_to_canonical(NVL(l_utl2_amt,0))
1385 ,p_action_information18 => fnd_number.number_to_canonical(NVL(l_pension_base,0))
1386 ,p_action_information19 => fnd_number.number_to_canonical(l_pension_amount)--0 --place for pension amount --14260836
1387 ,p_action_information20 => fnd_number.number_to_canonical(l_rate) --14260836
1388 ,p_action_information30 => l_employee_id --14260836
1389 );
1390 --
1391 END IF; -- LU specified
1392 --
1393 END IF; -- Archive = 'Y'
1394 --
1395 END IF; -- Count < 0
1396 --
1397 IF g_debug THEN
1398 hr_utility.set_location(' Leaving Procedure RANGE_CODE',70);
1399 END IF;
1400 --
1401 EXCEPTION
1402 --
1403 WHEN OTHERS THEN
1404 -- Return cursor that selects no rows
1405 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1406 fnd_file.put_line(fnd_file.log,'Error in EC 1'||substr(sqlerrm , 1, 30));
1407 --
1408 END RANGE_CODE;
1409 --
1410 -- -----------------------------------------------------------------------------
1411 -- ASSIGNMENT ACTION CODE
1412 -- -----------------------------------------------------------------------------
1413 --
1414 PROCEDURE ASSG_ACTION_CODE
1415 (p_payroll_action_id IN NUMBER
1416 ,p_start_person IN NUMBER
1417 ,p_end_person IN NUMBER
1418 ,p_chunk IN NUMBER)
1419 IS
1420 BEGIN
1421 --
1422 IF g_debug THEN
1423 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',80);
1424 END IF;
1425 --
1426 IF g_debug THEN
1427 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',90);
1428 END IF;
1429 --
1430 END ASSG_ACTION_CODE;
1431 --
1432 -- -----------------------------------------------------------------------------
1433 -- INITIALIZATION CODE
1434 -- -----------------------------------------------------------------------------
1435 --
1436 PROCEDURE INIT_CODE(p_payroll_action_id IN NUMBER)
1437 IS
1438 BEGIN
1439 --
1440 IF g_debug THEN
1441 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',100);
1442 END IF;
1443 --
1444 IF g_debug THEN
1445 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',110);
1446 END IF;
1447 --
1448 EXCEPTION WHEN OTHERS THEN
1449 g_err_num := SQLCODE;
1450 IF g_debug THEN
1451 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',120);
1452 END IF;
1453 END INIT_CODE;
1454 --
1455 -- -----------------------------------------------------------------------------
1456 -- ARCHIVE CODE
1457 -- -----------------------------------------------------------------------------
1458 --
1459 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
1460 ,p_effective_date IN DATE)
1461 IS
1462 BEGIN
1463 --
1464 IF g_debug THEN
1465 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',130);
1466 END IF;
1467 --
1468 IF g_debug THEN
1469 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',140);
1470 END IF;
1471 --
1472 END ARCHIVE_CODE;
1473 --
1474 -- ------------------------------------------------------ --
1475 -- GET_PDF_REP to generate the xml for pdf report (audit) --
1476 -- ------------------------------------------------------ --
1477 --
1478 PROCEDURE get_pdf_rep
1479 (p_business_group_id IN NUMBER
1480 ,p_payroll_action_id IN VARCHAR2
1481 ,p_template_name IN VARCHAR2
1482 ,p_xml OUT NOCOPY CLOB) IS
1483 --
1484 l_proc_name CONSTANT VARCHAR2(61) := 'get_pdf_rep';
1485 --
1486 CURSOR csr_LEGEMP
1487 (l_payroll_action_id IN NUMBER) IS
1488 SELECT leg_emp.action_information2 le_id
1489 ,leg_emp.action_information3 period_year
1490 ,leg_emp.action_information4 org_num
1491 ,leg_emp.action_information5 municipal_no
1492 ,leg_emp.action_information6 le_name
1493 ,leg_emp.action_information7 ada_line1
1494 ,leg_emp.action_information8 ada_line2
1495 ,leg_emp.action_information9 post_code
1496 ,leg_emp.action_information10 post_off
1497 ,leg_emp.action_information11 email
1498 ,leg_emp.action_information12 phone
1499 ,leg_emp.action_information17 industry_status
1500 ,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
1501 ,leg_emp.action_information19 nace_code
1502 ,leg_emp.action_information20 ada_line3
1503 ,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
1504 ,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
1505 ,leg_emp.action_information23 report_type -- Bug#9579261 fix
1506 ,leg_emp.action_information25 municipal_name --14260836
1507 FROM pay_action_information leg_emp
1508 WHERE leg_emp.action_context_type = 'PA'
1509 AND leg_emp.action_context_id = l_payroll_action_id
1510 AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
1511 AND leg_emp.action_information1 = 'PYNORSEA';
1512 --
1513 rec_LEGEMP csr_LEGEMP%ROWTYPE;
1514 --
1515 CURSOR csr_LU
1516 (l_payroll_action_id IN NUMBER) IS
1517 SELECT lu.action_information4 lu_org_num
1518 ,lu.action_information5 lu_municipal_num
1519 ,lu.action_information6 lu_municipal_name
1520 ,lu.action_information7 lu_zone
1521 ,fnd_number.canonical_to_number(lu.action_information8) ec_base
1522 ,fnd_number.canonical_to_number(lu.action_information9) ec_amt
1523 ,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
1524 ,fnd_number.canonical_to_number(lu.action_information11) reimburse_amt
1525 ,fnd_number.canonical_to_number(lu.action_information12) UTL1_base
1526 ,fnd_number.canonical_to_number(lu.action_information13) UTL1_amt
1527 ,fnd_number.canonical_to_number(lu.action_information14) UTR1_base
1528 ,fnd_number.canonical_to_number(lu.action_information15) UTR1_amt
1529 ,fnd_number.canonical_to_number(lu.action_information16) UTL2_base
1530 ,fnd_number.canonical_to_number(lu.action_information17) UTL2_amt
1531 ,fnd_number.canonical_to_number(lu.action_information18) pension_base
1532 ,fnd_number.canonical_to_number(lu.action_information19) pension_amt --14260836
1533 ,fnd_number.canonical_to_number(lu.action_information20) rate --14260836
1534 FROM pay_action_information lu
1535 WHERE lu.action_context_type = 'PA'
1536 AND lu.action_context_id = l_payroll_action_id
1537 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
1538 AND lu.action_information1 = 'PYNORSEA-EC'
1539 ORDER BY 3;
1540 --
1541 rec_LU csr_LU%ROWTYPE;
1542 --
1543 CURSOR csr_TAX (l_payroll_action_id IN NUMBER) IS
1544 SELECT wt.action_information2 wt_municipal_num
1545 ,wt.action_information3 wt_municipal_name
1546 ,fnd_number.canonical_to_number(wt.action_information5) wt_tax_value --14260836
1547 FROM pay_action_information wt
1548 WHERE wt.action_context_type = 'PA'
1549 AND wt.action_context_id = l_payroll_action_id
1550 AND wt.action_information_category = 'EMEA REPORT INFORMATION'
1551 AND wt.action_information1 = 'PYNORSEA-WT'
1552 ORDER BY 2;
1553 --
1554 rec_TAX csr_TAX%ROWTYPE;
1555 --
1556 l_xml_element_count NUMBER := 1;
1557 l_pension_footnote NUMBER := 0;
1558 l_payroll_action_id NUMBER;
1559 l_ec_base_total NUMBER := 0;
1560 l_ec_amt_total NUMBER := 0;
1561 l_wt_total NUMBER := 0;
1562 --
1563 --14260836
1564 CURSOR csr_amt_grp_by_org_muno
1565 (l_payroll_action_id NUMBER,
1566 p_lu_org_num VARCHAR2,
1567 p_lu_municipal_num VARCHAR2) IS
1568 SELECT
1569 lu.action_information4 lu_org_num
1570 ,lu.action_information5 lu_municipal_num
1571 ,SUM(fnd_number.canonical_to_number(lu.action_information9)+
1572 fnd_number.canonical_to_number(lu.action_information11)+
1573 fnd_number.canonical_to_number(lu.action_information13)+
1574 fnd_number.canonical_to_number(lu.action_information15)+
1575 fnd_number.canonical_to_number(lu.action_information17)+
1576 fnd_number.canonical_to_number(lu.action_information19)) org_muno_grp_amt
1577 FROM pay_action_information lu
1578 WHERE lu.action_context_type = 'PA'
1579 AND lu.action_context_id = l_payroll_action_id
1580 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
1581 AND lu.action_information1 = 'PYNORSEA-EC'
1582 AND lu.action_information4 = p_lu_org_num
1583 AND lu.action_information5 = p_lu_municipal_num
1584 GROUP BY lu.action_information4, lu.action_information5;
1585
1586 rec_amt_grp_by_org_muno csr_amt_grp_by_org_muno%ROWTYPE;
1587 --14260836
1588
1589 BEGIN
1590 hr_utility.set_location('Entering ' || l_proc_name, 10);
1591 g_xml_element_table.DELETE;
1592 --
1593 IF p_payroll_action_id is null then
1594 BEGIN
1595 SELECT payroll_action_id
1596 into l_payroll_action_id
1597 from pay_payroll_actions ppa,
1598 fnd_conc_req_summary_v fcrs,
1599 fnd_conc_req_summary_v fcrs1
1600 WHERE fcrs.request_id = fnd_global.conc_request_id
1601 and fcrs.priority_request_id = fcrs1.priority_request_id
1602 and ppa.request_id between fcrs1.request_id and fcrs.request_id
1603 and ppa.request_id = fcrs1.request_id;
1604 EXCEPTION
1605 WHEN others then
1606 null;
1607 END;
1608 ELSE
1609 l_payroll_action_id := p_payroll_action_id;
1610 END IF;
1611 --
1612 OPEN csr_LEGEMP(l_payroll_action_id);
1613 FETCH csr_LEGEMP INTO rec_LEGEMP;
1614 CLOSE csr_LEGEMP;
1615 --
1616 g_xml_element_table(l_xml_element_count).tagname := 'LEGAL_EMPLOYER';
1617 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1618 l_xml_element_count := l_xml_element_count + 1;
1619 --
1620 g_xml_element_table(l_xml_element_count).tagname := 'TERM';
1621 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,1,1);
1622 l_xml_element_count := l_xml_element_count + 1;
1623 --
1624 g_xml_element_table(l_xml_element_count).tagname := 'PERIOD';
1625 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,2,4);
1626 l_xml_element_count := l_xml_element_count + 1;
1627 --
1628 -- Bug#9579261 fix
1629 g_xml_element_table(l_xml_element_count).tagname := 'REPORT_TYPE_'||rec_LEGEMP.report_type;
1630 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1631 l_xml_element_count := l_xml_element_count + 1;
1632 --Bug#9579261 fix
1633 --14260836
1634 g_xml_element_table(l_xml_element_count).tagname := 'TAX_COLLECTOR';
1635 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.municipal_no||' '||rec_LEGEMP.municipal_name;
1636 l_xml_element_count := l_xml_element_count + 1;
1637 --14260836
1638 g_xml_element_table(l_xml_element_count).tagname := 'ORGANIZATION_NUMBER';
1639 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.org_num;
1640 l_xml_element_count := l_xml_element_count + 1;
1641 --
1642 g_xml_element_table(l_xml_element_count).tagname := 'LE_NAME';
1643 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.le_name;
1644 l_xml_element_count := l_xml_element_count + 1;
1645 --
1646 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE1';
1647 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line1;
1648 l_xml_element_count := l_xml_element_count + 1;
1649 --
1650 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE2';
1651 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line2;
1652 l_xml_element_count := l_xml_element_count + 1;
1653 --
1654 g_xml_element_table(l_xml_element_count).tagname := 'ADD_LINE3';
1655 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.ada_line3;
1656 l_xml_element_count := l_xml_element_count + 1;
1657 --
1658 g_xml_element_table(l_xml_element_count).tagname := 'POST_CODE';
1659 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_code;
1660 l_xml_element_count := l_xml_element_count + 1;
1661 --
1662 g_xml_element_table(l_xml_element_count).tagname := 'POST_OFFICE';
1663 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.post_off;
1664 l_xml_element_count := l_xml_element_count + 1;
1665 --
1666 g_xml_element_table(l_xml_element_count).tagname := 'EMAIL';
1667 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.email;
1668 l_xml_element_count := l_xml_element_count + 1;
1669 --
1670 g_xml_element_table(l_xml_element_count).tagname := 'PHONE';
1671 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.phone;
1672 l_xml_element_count := l_xml_element_count + 1;
1673 --
1674 g_xml_element_table(l_xml_element_count).tagname := 'NACE_CODE';
1675 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.nace_code;
1676 l_xml_element_count := l_xml_element_count + 1;
1677 --
1678 g_xml_element_table(l_xml_element_count).tagname := 'CALC_METHOD_'||rec_LEGEMP.industry_status;
1679 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1680 l_xml_element_count := l_xml_element_count + 1;
1681 --
1682 g_xml_element_table(l_xml_element_count).tagname := 'EXEMPT_LIMIT';
1683 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit,'FM9G999G999G999G990D00');
1684 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit; --14260836
1685 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1686 l_xml_element_count := l_xml_element_count + 1;
1687 --
1688 g_xml_element_table(l_xml_element_count).tagname := 'REMAINING_EXPEMT_PREV';
1689 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit_prev,'FM9G999G999G999G990D00');
1690 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit_prev; --14260836
1691 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1692 l_xml_element_count := l_xml_element_count + 1;
1693 --
1694 g_xml_element_table(l_xml_element_count).tagname := 'REMAINING_EXPEMT_AFTER';
1695 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_LEGEMP.exempt_limit_after,'FM9G999G999G999G990D00');
1696 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit_after; --14260836
1697 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1698 l_xml_element_count := l_xml_element_count + 1;
1699 --
1700 FOR rec_lu IN csr_LU(l_payroll_action_id)
1701 LOOP
1702 --
1703 --14260836
1704 OPEN csr_amt_grp_by_org_muno(l_payroll_action_id, rec_lu.lu_org_num, rec_lu.lu_municipal_num);
1705 FETCH csr_amt_grp_by_org_muno INTO rec_amt_grp_by_org_muno;
1706 CLOSE csr_amt_grp_by_org_muno;
1707
1708 IF rec_amt_grp_by_org_muno.org_muno_grp_amt < 0 THEN
1709 rec_lu.ec_base := 0;
1710 rec_lu.ec_amt := 0;
1711 rec_lu.reimburse_base:= 0;
1712 rec_lu.reimburse_amt := 0;
1713 rec_lu.UTL1_base := 0;
1714 rec_lu.UTL1_amt := 0;
1715 rec_lu.UTR1_base := 0;
1716 rec_lu.UTR1_amt := 0;
1717 rec_lu.UTL2_base := 0;
1718 rec_lu.UTL2_amt := 0;
1719 rec_lu.pension_base := 0;
1720 rec_lu.pension_amt := 0;
1721 END IF;
1722 --14260836
1723
1724 --IF rec_lu.ec_base <> 0 OR rec_lu.ec_amt <> 0 THEN --14260836
1725 --
1726 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1727 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1728 l_xml_element_count := l_xml_element_count + 1;
1729 --
1730 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1731 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1732 l_xml_element_count := l_xml_element_count + 1;
1733 --
1734 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1735 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1736 l_xml_element_count := l_xml_element_count + 1;
1737 --
1738 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1739 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1740 l_xml_element_count := l_xml_element_count + 1;
1741 --
1742 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1743 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1744 l_xml_element_count := l_xml_element_count + 1;
1745 --
1746 g_xml_element_table(l_xml_element_count).tagname := 'EC_REP';
1747 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1748 l_xml_element_count := l_xml_element_count + 1;
1749 --14260836
1750 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
1751 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
1752 l_xml_element_count := l_xml_element_count + 1;
1753 --14260836
1754 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1755 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.ec_base,'FM9G999G999G999G990D00');
1756 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.ec_base; --14260836
1757 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1758 l_xml_element_count := l_xml_element_count + 1;
1759 l_ec_base_total := l_ec_base_total + rec_lu.ec_base;
1760 --
1761 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1762 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.ec_amt,'FM9G999G999G999G990D00');
1763 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.ec_amt; --14260836
1764 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1765 l_xml_element_count := l_xml_element_count + 1;
1766 l_ec_amt_total := l_ec_amt_total + rec_lu.ec_amt;
1767 --
1768 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1769 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1770 l_xml_element_count := l_xml_element_count + 1;
1771 --
1772 --END IF; --14260836
1773 --
1774 --IF rec_lu.reimburse_base <> 0 OR rec_lu.reimburse_amt <> 0 THEN --14260836
1775 --
1776 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1777 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1778 l_xml_element_count := l_xml_element_count + 1;
1779 --
1780 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1781 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1782 l_xml_element_count := l_xml_element_count + 1;
1783 --
1784 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1785 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1786 l_xml_element_count := l_xml_element_count + 1;
1787 --
1788 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1789 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1790 l_xml_element_count := l_xml_element_count + 1;
1791 --
1792 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1793 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1794 l_xml_element_count := l_xml_element_count + 1;
1795 --
1796 g_xml_element_table(l_xml_element_count).tagname := 'REIMBURSE_REP';
1797 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1798 l_xml_element_count := l_xml_element_count + 1;
1799 --14260836
1800 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
1801 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
1802 l_xml_element_count := l_xml_element_count + 1;
1803 --14260836
1804 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1805 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.reimburse_base,'FM9G999G999G999G990D00');
1806 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.reimburse_base; --14260836
1807 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1808 l_xml_element_count := l_xml_element_count + 1;
1809 l_ec_base_total := l_ec_base_total + rec_lu.reimburse_base;
1810 --
1811 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1812 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.reimburse_amt,'FM9G999G999G999G990D00');
1813 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.reimburse_amt; --14260836
1814 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1815 l_xml_element_count := l_xml_element_count + 1;
1816 l_ec_amt_total := l_ec_amt_total + rec_lu.reimburse_amt;
1817 --
1818 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1819 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1820 l_xml_element_count := l_xml_element_count + 1;
1821 --
1822 --END IF; --14260836
1823 --
1824 --IF rec_lu.UTL1_base <> 0 OR rec_lu.UTL1_amt <> 0 THEN --14260836
1825 --
1826 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1827 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1828 l_xml_element_count := l_xml_element_count + 1;
1829 --
1830 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1831 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1832 l_xml_element_count := l_xml_element_count + 1;
1833 --
1834 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1835 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1836 l_xml_element_count := l_xml_element_count + 1;
1837 --
1838 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1839 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1840 l_xml_element_count := l_xml_element_count + 1;
1841 --
1842 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1843 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1844 l_xml_element_count := l_xml_element_count + 1;
1845 --
1846 g_xml_element_table(l_xml_element_count).tagname := 'UTL1_REP';
1847 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1848 l_xml_element_count := l_xml_element_count + 1;
1849 --14260836
1850 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
1851 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
1852 l_xml_element_count := l_xml_element_count + 1;
1853 --14260836
1854 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1855 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL1_base,'FM9G999G999G999G990D00');
1856 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.UTL1_base; --14260836
1857 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1858 l_xml_element_count := l_xml_element_count + 1;
1859 l_ec_base_total := l_ec_base_total + rec_lu.UTL1_base;
1860 --
1861 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1862 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL1_amt,'FM9G999G999G999G990D00');
1863 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.UTL1_amt; --14260836
1864 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1865 l_xml_element_count := l_xml_element_count + 1;
1866 l_ec_amt_total := l_ec_amt_total + rec_lu.UTL1_amt;
1867 --
1868 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1869 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1870 l_xml_element_count := l_xml_element_count + 1;
1871 --
1872 --END IF; --14260836
1873 --
1874 --IF rec_lu.UTR1_base <> 0 OR rec_lu.UTR1_amt <> 0 THEN --14260836
1875 --
1876 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1877 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1878 l_xml_element_count := l_xml_element_count + 1;
1879 --
1880 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1881 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1882 l_xml_element_count := l_xml_element_count + 1;
1883 --
1884 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1885 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1886 l_xml_element_count := l_xml_element_count + 1;
1887 --
1888 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1889 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1890 l_xml_element_count := l_xml_element_count + 1;
1891 --
1892 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1893 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1894 l_xml_element_count := l_xml_element_count + 1;
1895 --
1896 g_xml_element_table(l_xml_element_count).tagname := 'UTR1_REP';
1897 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1898 l_xml_element_count := l_xml_element_count + 1;
1899 --14260836
1900 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
1901 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
1902 l_xml_element_count := l_xml_element_count + 1;
1903 --14260836
1904 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1905 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTR1_base,'FM9G999G999G999G990D00');
1906 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.UTR1_base; --14260836
1907 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1908 l_xml_element_count := l_xml_element_count + 1;
1909 l_ec_base_total := l_ec_base_total + rec_lu.UTR1_base;
1910 --
1911 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1912 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTR1_amt,'FM9G999G999G999G990D00');
1913 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.UTR1_amt; --14260836
1914 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1915 l_xml_element_count := l_xml_element_count + 1;
1916 l_ec_amt_total := l_ec_amt_total + rec_lu.UTR1_amt;
1917 --
1918 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1919 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1920 l_xml_element_count := l_xml_element_count + 1;
1921 --
1922 --END IF; --14260836
1923 --
1924 --IF rec_lu.UTL2_base <> 0 OR rec_lu.UTL2_amt <> 0 THEN --14260836
1925 --
1926 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1927 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1928 l_xml_element_count := l_xml_element_count + 1;
1929 --
1930 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1931 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1932 l_xml_element_count := l_xml_element_count + 1;
1933 --
1934 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1935 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1936 l_xml_element_count := l_xml_element_count + 1;
1937 --
1938 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1939 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1940 l_xml_element_count := l_xml_element_count + 1;
1941 --
1942 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1943 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1944 l_xml_element_count := l_xml_element_count + 1;
1945 --
1946 g_xml_element_table(l_xml_element_count).tagname := 'UTR2_REP';
1947 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1948 l_xml_element_count := l_xml_element_count + 1;
1949 --14260836
1950 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
1951 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
1952 l_xml_element_count := l_xml_element_count + 1;
1953 --14260836
1954 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
1955 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(round(rec_lu.UTL2_base,2),'FM9G999G999G999G990D00'); --14260836
1956 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_lu.UTL2_base,2); --14260836
1957 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1958 l_xml_element_count := l_xml_element_count + 1;
1959 --
1960 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
1961 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.UTL2_amt,'FM9G999G999G999G990D00');
1962 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.UTL2_amt; --14260836
1963 g_xml_element_table(l_xml_element_count).tagtype := 'A';
1964 l_xml_element_count := l_xml_element_count + 1;
1965 l_ec_amt_total := l_ec_amt_total + rec_lu.UTL2_amt;
1966 --
1967 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1968 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
1969 l_xml_element_count := l_xml_element_count + 1;
1970 --
1971 --END IF; --14260836
1972 --
1973 --IF rec_lu.pension_base <> 0 THEN --14260836
1974 --
1975 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
1976 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
1977 l_xml_element_count := l_xml_element_count + 1;
1978 --
1979 g_xml_element_table(l_xml_element_count).tagname := 'LU_ORG_NUM';
1980 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
1981 l_xml_element_count := l_xml_element_count + 1;
1982 --
1983 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NUM';
1984 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
1985 l_xml_element_count := l_xml_element_count + 1;
1986 --
1987 g_xml_element_table(l_xml_element_count).tagname := 'LU_MUNICIPAL_NAME';
1988 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_name;
1989 l_xml_element_count := l_xml_element_count + 1;
1990 --
1991 g_xml_element_table(l_xml_element_count).tagname := 'LU_ZONE';
1992 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_zone;
1993 l_xml_element_count := l_xml_element_count + 1;
1994 --
1995 g_xml_element_table(l_xml_element_count).tagname := 'PENSION_REP';
1996 g_xml_element_table(l_xml_element_count).tagvalue := 'X';
1997 l_xml_element_count := l_xml_element_count + 1;
1998 l_pension_footnote := 1;
1999 --14260836
2000 g_xml_element_table(l_xml_element_count).tagname := 'EC_RATE';
2001 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.rate;
2002 l_xml_element_count := l_xml_element_count + 1;
2003 --14260836
2004 g_xml_element_table(l_xml_element_count).tagname := 'BASE';
2005 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_lu.pension_base,'FM9G999G999G999G990D00');
2006 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.pension_base; --14260836
2007 g_xml_element_table(l_xml_element_count).tagtype := 'A';
2008 l_xml_element_count := l_xml_element_count + 1;
2009 --
2010 g_xml_element_table(l_xml_element_count).tagname := 'AMT';
2011 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.pension_amt; --14260836
2012 l_xml_element_count := l_xml_element_count + 1;
2013 l_ec_base_total := l_ec_base_total + rec_lu.pension_base;
2014 l_ec_amt_total := l_ec_amt_total + rec_lu.pension_amt;
2015 --
2016 g_xml_element_table(l_xml_element_count).tagname := 'EMPLOYER_CONTRIBUTION';
2017 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2018 l_xml_element_count := l_xml_element_count + 1;
2019 --
2020 --END IF; --14260836
2021 --
2022 END LOOP;
2023 --
2024 FOR rec_tax IN csr_TAX(l_payroll_action_id)
2025 LOOP
2026 --
2027 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX';
2028 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2029 l_xml_element_count := l_xml_element_count + 1;
2030 --
2031 g_xml_element_table(l_xml_element_count).tagname := 'WT_MUNICIPAL_NUM';
2032 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_num;
2033 l_xml_element_count := l_xml_element_count + 1;
2034 --
2035 g_xml_element_table(l_xml_element_count).tagname := 'WT_MUNICIPAL_NAME';
2036 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_name;
2037 l_xml_element_count := l_xml_element_count + 1;
2038 --
2039 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX_AMT';
2040 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(rec_tax.wt_tax_value,'FM9G999G999G999G990D00');
2041 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_tax_value; --14260836
2042 g_xml_element_table(l_xml_element_count).tagtype := 'A';
2043 l_xml_element_count := l_xml_element_count + 1;
2044 l_wt_total := l_wt_total + rec_tax.wt_tax_value;
2045 --
2046 g_xml_element_table(l_xml_element_count).tagname := 'WITHHOLDING_TAX';
2047 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2048 l_xml_element_count := l_xml_element_count + 1;
2049 --
2050 END LOOP;
2051 --
2052 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_EC_AMT';
2053 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_ec_amt_total,'FM9G999G999G999G990D00');
2054 g_xml_element_table(l_xml_element_count).tagvalue := l_ec_amt_total; --14260836
2055 g_xml_element_table(l_xml_element_count).tagtype := 'A';
2056 l_xml_element_count := l_xml_element_count + 1;
2057 --
2058 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_EC_BASE';
2059 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_ec_base_total,'FM9G999G999G999G990D00');
2060 g_xml_element_table(l_xml_element_count).tagvalue := l_ec_base_total; --14260836
2061 g_xml_element_table(l_xml_element_count).tagtype := 'A';
2062 l_xml_element_count := l_xml_element_count + 1;
2063 --
2064 g_xml_element_table(l_xml_element_count).tagname := 'TOTAL_WITHHOLDING_TAX';
2065 --g_xml_element_table(l_xml_element_count).tagvalue := to_char(l_wt_total,'FM9G999G999G999G990D00');
2066 g_xml_element_table(l_xml_element_count).tagvalue := l_wt_total; --14260836
2067 g_xml_element_table(l_xml_element_count).tagtype := 'A';
2068 l_xml_element_count := l_xml_element_count + 1;
2069 --
2070 -- Footnote section for Pension Calculated Employer Contribution
2071 IF l_pension_footnote = 1 THEN
2072 g_xml_element_table(l_xml_element_count).tagname := 'FOOT_NOTE';
2073 g_xml_element_table(l_xml_element_count).tagvalue := '*) ' || HR_GENERAL.DECODE_LOOKUP('NO_FORM_LABELS','PENSION_FOOTNOTE');
2074 l_xml_element_count := l_xml_element_count + 1;
2075 END IF;
2076 --
2077 g_xml_element_table(l_xml_element_count).tagname := 'LEGAL_EMPLOYER';
2078 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2079 l_xml_element_count := l_xml_element_count + 1;
2080 --
2081 write_to_clob(p_xml);
2082 --
2083 hr_utility.set_location('Leaving ' || l_proc_name, 1000);
2084 --
2085 END get_pdf_rep;
2086 --
2087 -- ------------------------------------------------- --
2088 -- GET_XML_REP to generate the standard xml extract --
2089 -- ------------------------------------------------- --
2090 --
2091 PROCEDURE get_xml_rep
2092 (p_business_group_id IN NUMBER
2093 ,p_payroll_action_id IN VARCHAR2
2094 ,p_template_name IN VARCHAR2
2095 ,p_xml OUT NOCOPY CLOB) IS
2096 --
2097 l_proc_name CONSTANT VARCHAR2(61) := 'get_xml_rep';
2098 --
2099 CURSOR csr_LEGEMP
2100 (l_payroll_action_id IN NUMBER) IS
2101 SELECT leg_emp.action_information3 period_year
2102 ,leg_emp.action_information4 org_num
2103 ,leg_emp.action_information5 municipal_no
2104 ,leg_emp.action_information6 le_name
2105 ,leg_emp.action_information7 add_line1
2106 ,leg_emp.action_information8 add_line2
2107 ,leg_emp.action_information9 post_code
2108 ,leg_emp.action_information10 post_off
2109 ,leg_emp.action_information17 industry_status
2110 ,fnd_number.canonical_to_number(leg_emp.action_information18) exempt_limit
2111 ,leg_emp.action_information19 nace_code
2112 ,leg_emp.action_information20 add_line3
2113 ,fnd_number.canonical_to_number(leg_emp.action_information21) exempt_limit_prev
2114 ,fnd_number.canonical_to_number(leg_emp.action_information22) exempt_limit_after
2115 ,leg_emp.action_information23 report_type -- Bug#9579261 fix
2116 ,fnd_number.canonical_to_number(leg_emp.action_information24) utl2_base -- Bug#9579261 fix
2117 FROM pay_action_information leg_emp
2118 WHERE leg_emp.action_context_type = 'PA'
2119 AND leg_emp.action_context_id = l_payroll_action_id
2120 AND leg_emp.action_information_category = 'EMEA REPORT INFORMATION'
2121 AND leg_emp.action_information1 = 'PYNORSEA';
2122 --
2123 rec_LEGEMP csr_LEGEMP%ROWTYPE;
2124 --
2125 CURSOR csr_sum_LU
2126 (l_payroll_action_id IN NUMBER) IS
2127 SELECT SUM(fnd_number.canonical_to_number(lu.action_information8)) EC_base_sum
2128 ,SUM(fnd_number.canonical_to_number(lu.action_information10)) REIM_base_sum
2129 ,SUM(fnd_number.canonical_to_number(lu.action_information12)) UTL1_base_sum
2130 ,SUM(fnd_number.canonical_to_number(lu.action_information14)) UTR1_base_sum
2131 ,SUM(fnd_number.canonical_to_number(lu.action_information16)) UTL2_base_sum
2132 ,SUM(fnd_number.canonical_to_number(lu.action_information9))
2133 + SUM(fnd_number.canonical_to_number(lu.action_information11))
2134 + SUM(fnd_number.canonical_to_number(lu.action_information13))
2135 + SUM(fnd_number.canonical_to_number(lu.action_information15))
2136 + SUM(fnd_number.canonical_to_number(lu.action_information17))
2137 + SUM(fnd_number.canonical_to_number(lu.action_information19))amt_sum
2138 FROM pay_action_information lu
2139 WHERE lu.action_context_type = 'PA'
2140 AND lu.action_context_id = l_payroll_action_id
2141 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
2142 AND lu.action_information1 = 'PYNORSEA-EC';
2143 --
2144 rec_sum_LU csr_sum_LU%ROWTYPE;
2145 --
2146 CURSOR csr_LU
2147 (l_payroll_action_id IN NUMBER) IS
2148 SELECT lu.action_information4 lu_org_num
2149 ,lu.action_information5 lu_municipal_num
2150 ,fnd_number.canonical_to_number(lu.action_information8) ec_base
2151 ,fnd_number.canonical_to_number(lu.action_information10) reimburse_base
2152 ,fnd_number.canonical_to_number(lu.action_information18) pension_base_sum
2153 FROM pay_action_information lu
2154 WHERE lu.action_context_type = 'PA'
2155 AND lu.action_context_id = l_payroll_action_id
2156 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
2157 AND lu.action_information1 = 'PYNORSEA-EC'
2158 ORDER BY 3;
2159 --
2160 rec_LU csr_LU%ROWTYPE;
2161 --
2162 CURSOR csr_TAX
2163 (l_payroll_action_id IN NUMBER) IS
2164 SELECT wt.action_information2 wt_municipal_num
2165 ,fnd_number.canonical_to_number(wt.action_information5) wt_tax_value --14260836
2166 FROM pay_action_information wt
2167 WHERE wt.action_context_type = 'PA'
2168 AND wt.action_context_id = l_payroll_action_id
2169 AND wt.action_information_category = 'EMEA REPORT INFORMATION'
2170 AND wt.action_information1 = 'PYNORSEA-WT'
2171 ORDER BY 1;
2172 --
2173 rec_TAX csr_TAX%ROWTYPE;
2174 --
2175 l_xml_element_count NUMBER := 1;
2176 l_payroll_action_id NUMBER;
2177 l_wt_total NUMBER := 0;
2178 --
2179 --14260836
2180 CURSOR csr_sum_neg_to_zero(l_payroll_action_id IN NUMBER)
2181 IS
2182 select
2183 sum(EC_base_sum) EC_base_sum,
2184 sum(REIM_base_sum) REIM_base_sum,
2185 sum(UTL1_base_sum) UTL1_base_sum,
2186 sum(UTR1_base_sum) UTR1_base_sum,
2187 sum(UTL2_base_sum) UTL2_base_sum,
2188 sum(Pension_base_sum) Pension_base_sum,
2189 sum(amt_sum) amt_sum
2190 from
2191 (
2192 select
2193 decode(sign(AMT_SUM),-1,0, EC_base_sum) EC_base_sum,
2194 decode(sign(AMT_SUM),-1,0, REIM_base_sum) REIM_base_sum,
2195 decode(sign(AMT_SUM),-1,0, UTL1_base_sum) UTL1_base_sum,
2196 decode(sign(AMT_SUM),-1,0, UTR1_base_sum) UTR1_base_sum,
2197 decode(sign(AMT_SUM),-1,0, UTL2_base_sum) UTL2_base_sum,
2198 decode(sign(AMT_SUM),-1,0, Pension_base_sum) Pension_base_sum,
2199 decode(sign(AMT_SUM),-1,0, amt_sum) amt_sum
2200 from
2201 ( select lu.action_information4 lu_org_num, lu.action_information5 lu_municipal_num
2202 ,SUM(fnd_number.canonical_to_number(lu.action_information8)) EC_base_sum
2203 ,SUM(fnd_number.canonical_to_number(lu.action_information10)) REIM_base_sum
2204 ,SUM(fnd_number.canonical_to_number(lu.action_information12)) UTL1_base_sum
2205 ,SUM(fnd_number.canonical_to_number(lu.action_information14)) UTR1_base_sum
2206 ,SUM(fnd_number.canonical_to_number(lu.action_information16)) UTL2_base_sum
2207 ,SUM(fnd_number.canonical_to_number(lu.action_information18)) Pension_base_sum
2208 ,SUM(fnd_number.canonical_to_number(lu.action_information9))
2209 + SUM(fnd_number.canonical_to_number(lu.action_information11))
2210 + SUM(fnd_number.canonical_to_number(lu.action_information13))
2211 + SUM(fnd_number.canonical_to_number(lu.action_information15))
2212 + SUM(fnd_number.canonical_to_number(lu.action_information17))
2213 + SUM(fnd_number.canonical_to_number(lu.action_information19))amt_sum
2214 FROM pay_action_information lu
2215 WHERE lu.action_context_type = 'PA'
2216 AND lu.action_context_id = l_payroll_action_id
2217 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
2218 AND lu.action_information1 = 'PYNORSEA-EC'
2219 GROUP BY lu.action_information4, lu.action_information5)
2220 );
2221
2222 rec_sum_neg_to_zero csr_sum_neg_to_zero%ROWTYPE;
2223
2224 CURSOR csr_amt_grp_by_org_muno
2225 (l_payroll_action_id NUMBER,
2226 p_lu_org_num VARCHAR2,
2227 p_lu_municipal_num VARCHAR2) IS
2228 SELECT
2229 lu.action_information4 lu_org_num
2230 ,lu.action_information5 lu_municipal_num
2231 ,SUM(fnd_number.canonical_to_number(lu.action_information9)+
2232 fnd_number.canonical_to_number(lu.action_information11)+
2233 fnd_number.canonical_to_number(lu.action_information13)+
2234 fnd_number.canonical_to_number(lu.action_information15)+
2235 fnd_number.canonical_to_number(lu.action_information17)+
2236 fnd_number.canonical_to_number(lu.action_information19)) org_muno_grp_amt
2237 FROM pay_action_information lu
2238 WHERE lu.action_context_type = 'PA'
2239 AND lu.action_context_id = l_payroll_action_id
2240 AND lu.action_information_category = 'EMEA REPORT INFORMATION'
2241 AND lu.action_information1 = 'PYNORSEA-EC'
2242 AND lu.action_information4 = p_lu_org_num
2243 AND lu.action_information5 = p_lu_municipal_num
2244 GROUP BY lu.action_information4, lu.action_information5;
2245
2246 rec_amt_grp_by_org_muno csr_amt_grp_by_org_muno%ROWTYPE;
2247 --14260836
2248
2249 BEGIN
2250 hr_utility.set_location('Entering ' || l_proc_name, 10);
2251 g_xml_element_table.DELETE;
2252 --
2253 IF p_payroll_action_id is null then
2254 BEGIN
2255 SELECT payroll_action_id
2256 into l_payroll_action_id
2257 from pay_payroll_actions ppa,
2258 fnd_conc_req_summary_v fcrs,
2259 fnd_conc_req_summary_v fcrs1
2260 WHERE fcrs.request_id = fnd_global.conc_request_id
2261 and fcrs.priority_request_id = fcrs1.priority_request_id
2262 and ppa.request_id between fcrs1.request_id and fcrs.request_id
2263 and ppa.request_id = fcrs1.request_id;
2264 EXCEPTION
2265 WHEN others then
2266 null;
2267 END;
2268 ELSE
2269 l_payroll_action_id := p_payroll_action_id;
2270 END IF;
2271 --
2272 OPEN csr_LEGEMP(l_payroll_action_id);
2273 FETCH csr_LEGEMP INTO rec_LEGEMP;
2274 CLOSE csr_LEGEMP;
2275 --
2276 g_xml_element_table(l_xml_element_count).tagname := 'Skjema';
2277 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2278 --g_xml_element_table(l_xml_element_count).tagattrb := 'skjemanummer="669" spesifikasjonsnummer="6168"'; --14260836
2279 g_xml_element_table(l_xml_element_count).tagattrb := 'skjemanummer="669" spesifikasjonsnummer="10454"'; --14260836
2280 l_xml_element_count := l_xml_element_count + 1;
2281 --
2282 g_xml_element_table(l_xml_element_count).tagname := 'Innledning-grp-986';
2283 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2284 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="986"';
2285 l_xml_element_count := l_xml_element_count + 1;
2286 --
2287 g_xml_element_table(l_xml_element_count).tagname := 'Periode-grp-57';
2288 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2289 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="57"';
2290 l_xml_element_count := l_xml_element_count + 1;
2291 -- Bi-Monthly Period
2292 g_xml_element_table(l_xml_element_count).tagname := 'OppgaveTermin-datadef-11819';
2293 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,1,1);
2294 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="11819"';
2295 l_xml_element_count := l_xml_element_count + 1;
2296 -- Year
2297 g_xml_element_table(l_xml_element_count).tagname := 'OppgaveAr-datadef-11236';
2298 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.period_year,2,4);
2299 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="11236"';
2300 l_xml_element_count := l_xml_element_count + 1;
2301 --
2302 -- Bug#9579261 fix start
2303 g_xml_element_table(l_xml_element_count).tagname := 'OppgaveType-datadef-32862';
2304 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.report_type;
2305 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="32862"';
2306 l_xml_element_count := l_xml_element_count + 1;
2307 -- Bug#9579261 fix ends
2308 --
2309 g_xml_element_table(l_xml_element_count).tagname := 'Periode-grp-57';
2310 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2311 l_xml_element_count := l_xml_element_count + 1;
2312 --
2313 --
2314 g_xml_element_table(l_xml_element_count).tagname := 'Skatteoppkrever-grp-989';
2315 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2316 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="989"';
2317 l_xml_element_count := l_xml_element_count + 1;
2318 -- LE Tax Municipality
2319 g_xml_element_table(l_xml_element_count).tagname := 'SkatteoppkreverKommuneNummer-datadef-16513';
2320 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.municipal_no;
2321 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16513"';
2322 l_xml_element_count := l_xml_element_count + 1;
2323 --
2324 g_xml_element_table(l_xml_element_count).tagname := 'Skatteoppkrever-grp-989';
2325 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2326 l_xml_element_count := l_xml_element_count + 1;
2327 --
2328 --
2329 g_xml_element_table(l_xml_element_count).tagname := 'Innsender-grp-56';
2330 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2331 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="56"';
2332 l_xml_element_count := l_xml_element_count + 1;
2333 -- LE Organization Number
2334 g_xml_element_table(l_xml_element_count).tagname := 'RapporteringsenhetOrganisasjonsnummer-datadef-21772';
2335 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.org_num;
2336 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="21772"';
2337 l_xml_element_count := l_xml_element_count + 1;
2338
2339 -- Not Used by Oracle Payroll 26
2340 -- g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverFodselsnummer-datadef-26';
2341 -- g_xml_element_table(l_xml_element_count).tagvalue := NULL;
2342 -- l_xml_element_count := l_xml_element_count + 1;
2343
2344 -- LE Name
2345 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverNavnPreutfylt-datadef-25795';
2346 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.le_name,1,175); --14260836
2347 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25795"';
2348 l_xml_element_count := l_xml_element_count + 1;
2349 -- LE Address
2350 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverAdressePreutfylt-datadef-25796';
2351 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.add_line1 ||' '|| rec_LEGEMP.add_line2
2352 ||' '|| rec_LEGEMP.add_line3,1,500); --14260836
2353 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25796"';
2354 l_xml_element_count := l_xml_element_count + 1;
2355 -- LE Address Post Code
2356 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverPostnummerPreutfylt-datadef-25797';
2357 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.post_code,1,4); --14260836
2358 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25797"';
2359 l_xml_element_count := l_xml_element_count + 1;
2360 -- LE Address Post Office
2361 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverPoststedPreutfylt-datadef-25798';
2362 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.post_off,1,35); --14260836
2363 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="25798"';
2364 l_xml_element_count := l_xml_element_count + 1;
2365 -- Nace Code
2366 g_xml_element_table(l_xml_element_count).tagname := 'OppgavegiverNACEKode-datadef-27602';
2367 g_xml_element_table(l_xml_element_count).tagvalue := substr(rec_LEGEMP.nace_code,1,6); --14260836
2368 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27602"';
2369 l_xml_element_count := l_xml_element_count + 1;
2370 --
2371 g_xml_element_table(l_xml_element_count).tagname := 'Innsender-grp-56';
2372 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2373 l_xml_element_count := l_xml_element_count + 1;
2374 --
2375 --
2376 g_xml_element_table(l_xml_element_count).tagname := 'Innledning-grp-986';
2377 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2378 l_xml_element_count := l_xml_element_count + 1;
2379 --
2380 --
2381 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-5698';
2382 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2383 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="5698"';
2384 l_xml_element_count := l_xml_element_count + 1;
2385 --
2386 g_xml_element_table(l_xml_element_count).tagname := 'Beregningsmate-grp-169';
2387 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2388 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="169"';
2389 l_xml_element_count := l_xml_element_count + 1;
2390 -- Calculation Method
2391 g_xml_element_table(l_xml_element_count).tagname := 'TerminoppgaveBeregningsmate-datadef-27603';
2392 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.industry_status;
2393 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27603"';
2394 l_xml_element_count := l_xml_element_count + 1;
2395 -- Exempt limit from Last Reporting Term
2396 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftBunnfradrag-datadef-16517';
2397 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit_prev;
2398 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16517"';
2399 l_xml_element_count := l_xml_element_count + 1;
2400 --
2401 g_xml_element_table(l_xml_element_count).tagname := 'Beregningsmate-grp-169';
2402 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2403 l_xml_element_count := l_xml_element_count + 1;
2404 --
2405 --
2406 g_xml_element_table(l_xml_element_count).tagname := 'Tilskudd-grp-6712';
2407 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2408 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6712"';
2409 l_xml_element_count := l_xml_element_count + 1;
2410 -- Exempt limit from Last Reporting Term
2411 g_xml_element_table(l_xml_element_count).tagname := 'TilskuddAndreTerminoppgave-datadef-27604';
2412 g_xml_element_table(l_xml_element_count).tagvalue := rec_LEGEMP.exempt_limit;
2413 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27604"';
2414 l_xml_element_count := l_xml_element_count + 1;
2415 --
2416 g_xml_element_table(l_xml_element_count).tagname := 'Tilskudd-grp-6712';
2417 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2418 l_xml_element_count := l_xml_element_count + 1;
2419 --
2420 --
2421 OPEN csr_sum_LU(l_payroll_action_id);
2422 FETCH csr_sum_LU INTO rec_sum_LU;
2423 CLOSE csr_sum_LU;
2424 --
2425 --14260836
2426 OPEN csr_sum_neg_to_zero(l_payroll_action_id);
2427 FETCH csr_sum_neg_to_zero INTO rec_sum_neg_to_zero;
2428 CLOSE csr_sum_neg_to_zero;
2429 --14260836
2430
2431 g_xml_element_table(l_xml_element_count).tagname := 'UTL1-grp-6715';
2432 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2433 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6715"';
2434 l_xml_element_count := l_xml_element_count + 1;
2435 -- Sum of UTL1 Bases
2436 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftUtenlandskGrunnlag-datadef-16518';
2437 g_xml_element_table(l_xml_element_count).tagvalue := nvl(round(ABS(rec_sum_neg_to_zero.UTL1_base_sum)),0); --14260836
2438 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16518"';
2439 l_xml_element_count := l_xml_element_count + 1;
2440 -- Sum of UTR1 Bases
2441 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftUtenlandskRefusjonsgrunnlagSpesifisert-datadef-27612';
2442 g_xml_element_table(l_xml_element_count).tagvalue := nvl(round(ABS(rec_sum_neg_to_zero.UTR1_base_sum)),0); --14260836
2443 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27612"';
2444 l_xml_element_count := l_xml_element_count + 1;
2445 --
2446 g_xml_element_table(l_xml_element_count).tagname := 'UTL1-grp-6715';
2447 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2448 l_xml_element_count := l_xml_element_count + 1;
2449 --
2450 --
2451 g_xml_element_table(l_xml_element_count).tagname := 'UTL2-grp-6716';
2452 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2453 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6716"';
2454 l_xml_element_count := l_xml_element_count + 1;
2455 -- Sum of Months UTL2
2456 g_xml_element_table(l_xml_element_count).tagname := 'AnsattUtenlandskManeder-datadef-16519';
2457 g_xml_element_table(l_xml_element_count).tagvalue := round(ABS(nvl(rec_sum_neg_to_zero.UTL2_base_sum,0))); --14260836
2458 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="16519"';
2459 l_xml_element_count := l_xml_element_count + 1;
2460
2461 -- Bug#9579261 fix start
2462 g_xml_element_table(l_xml_element_count).tagname := 'AnsattUtenlandskManedligSats-datadef-32863';
2463 g_xml_element_table(l_xml_element_count).tagvalue := ABS(nvl(rec_LEGEMP.utl2_base,0));
2464 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="32863"';
2465 l_xml_element_count := l_xml_element_count + 1;
2466 -- Bug#9579261 fix end
2467 --
2468 g_xml_element_table(l_xml_element_count).tagname := 'UTL2-grp-6716';
2469 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2470 l_xml_element_count := l_xml_element_count + 1;
2471 --
2472 --
2473 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-5698';
2474 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2475 l_xml_element_count := l_xml_element_count + 1;
2476 --
2477 --
2478 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-6719';
2479 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2480 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6719"';
2481 l_xml_element_count := l_xml_element_count + 1;
2482 --
2483 FOR rec_lu IN csr_LU(l_payroll_action_id)
2484 LOOP
2485 --
2486 --14260836
2487 OPEN csr_amt_grp_by_org_muno(l_payroll_action_id, rec_lu.lu_org_num, rec_lu.lu_municipal_num);
2488 FETCH csr_amt_grp_by_org_muno INTO rec_amt_grp_by_org_muno;
2489 CLOSE csr_amt_grp_by_org_muno;
2490
2491 IF rec_amt_grp_by_org_muno.org_muno_grp_amt < 0 THEN
2492 rec_lu.ec_base := 0;
2493 rec_lu.reimburse_base:= 0;
2494 END IF;
2495 --14260836
2496
2497 g_xml_element_table(l_xml_element_count).tagname := 'TabellArbeidsgiveravgift-grp-4953';
2498 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2499 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4953"';
2500 l_xml_element_count := l_xml_element_count + 1;
2501 -- LU Org Number
2502 g_xml_element_table(l_xml_element_count).tagname := 'AvgiftsbetalerOrganisasjonsnummer-datadef-27605';
2503 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_org_num;
2504 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27605"';
2505 l_xml_element_count := l_xml_element_count + 1;
2506 -- Not used by Oracle Payroll - 27606
2507 -- g_xml_element_table(l_xml_element_count).tagname := 'AvgiftsbetalerFodselssnummer-datadef-27606';
2508 -- g_xml_element_table(l_xml_element_count).tagvalue := NULL;
2509 -- g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27606"'
2510 -- l_xml_element_count := l_xml_element_count + 1;
2511 -- LU Tax Municipality
2512 g_xml_element_table(l_xml_element_count).tagname := 'KommuneNummer-datadef-5950';
2513 g_xml_element_table(l_xml_element_count).tagvalue := rec_lu.lu_municipal_num;
2514 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="5950"';
2515 l_xml_element_count := l_xml_element_count + 1;
2516 -- EC Base (Normal)
2517 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftGrunnlagSpesifisert-datadef-27607';
2518 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_lu.ec_base));
2519 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27607"';
2520 l_xml_element_count := l_xml_element_count + 1;
2521 -- Reimbursements
2522 g_xml_element_table(l_xml_element_count).tagname := 'RefusjonGrunnlagSpesifisert-datadef-27608';
2523 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_lu.reimburse_base));
2524 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27608"';
2525 l_xml_element_count := l_xml_element_count + 1;
2526 -- Not used by Oracle Payroll - 27611 - set it to zero, till pension built was not in NO loc.
2527 -- SInce NO Pension built is there, will populate accordingly.
2528 g_xml_element_table(l_xml_element_count).tagname := 'PensjonPremieTilskuddSpesifisert-datadef-27611';
2529 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_lu.pension_base_sum));
2530 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27611"';
2531 l_xml_element_count := l_xml_element_count + 1;
2532 --
2533 g_xml_element_table(l_xml_element_count).tagname := 'TabellArbeidsgiveravgift-grp-4953';
2534 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2535 l_xml_element_count := l_xml_element_count + 1;
2536 --
2537 END LOOP;
2538 --
2539 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-6719';
2540 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2541 l_xml_element_count := l_xml_element_count + 1;
2542 --
2543 --
2544 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-6717';
2545 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2546 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6717"';
2547 l_xml_element_count := l_xml_element_count + 1;
2548 --
2549 FOR rec_tax IN csr_TAX(l_payroll_action_id)
2550 LOOP
2551 --
2552 g_xml_element_table(l_xml_element_count).tagname := 'TabellForskuddstrekk-grp-6718';
2553 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2554 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="6718"';
2555 l_xml_element_count := l_xml_element_count + 1;
2556 -- Emplyee Municipal Code
2557 g_xml_element_table(l_xml_element_count).tagname := 'InnberetningspliktigForskuddstrekkKommunenummer-datadef-27615';
2558 g_xml_element_table(l_xml_element_count).tagvalue := rec_tax.wt_municipal_num;
2559 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27615"';
2560 l_xml_element_count := l_xml_element_count + 1;
2561 -- Tax
2562 g_xml_element_table(l_xml_element_count).tagname := 'ForskuddstrekkSpesifisert-datadef-27616';
2563 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_tax.wt_tax_value));
2564 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27616"';
2565 l_xml_element_count := l_xml_element_count + 1;
2566 l_wt_total := l_wt_total + rec_tax.wt_tax_value;
2567 --
2568 g_xml_element_table(l_xml_element_count).tagname := 'TabellForskuddstrekk-grp-6718';
2569 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2570 l_xml_element_count := l_xml_element_count + 1;
2571 --
2572 END LOOP;
2573 --
2574 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-6717';
2575 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2576 l_xml_element_count := l_xml_element_count + 1;
2577 --
2578 --
2579 g_xml_element_table(l_xml_element_count).tagname := 'Resultater-grp-74';
2580 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2581 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="74"';
2582 l_xml_element_count := l_xml_element_count + 1;
2583 --
2584 g_xml_element_table(l_xml_element_count).tagname := 'Kontrollsummer-grp-4909';
2585 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2586 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4909"';
2587 l_xml_element_count := l_xml_element_count + 1;
2588 -- EC Base Total
2589 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftGrunnlag-datadef-27617';
2590 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_sum_neg_to_zero.EC_base_sum)); --14260836
2591 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27617"';
2592 l_xml_element_count := l_xml_element_count + 1;
2593 -- Reimburse Base Total
2594 g_xml_element_table(l_xml_element_count).tagname := 'RefusjonGrunnlag-datadef-27618';
2595 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_sum_neg_to_zero.REIM_base_sum)); --14260836
2596 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27618"';
2597 l_xml_element_count := l_xml_element_count + 1;
2598 -- Pension Base Total (not in place)
2599 g_xml_element_table(l_xml_element_count).tagname := 'PensjonPremieTilskuddSumGrunnlag-datadef-27619';
2600 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_sum_neg_to_zero.Pension_base_sum));--'0';16229158
2601 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="27619"';
2602 l_xml_element_count := l_xml_element_count + 1;
2603 -- Remaining Exemption Limit Total
2604 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftRestFribelop-datadef-21169';
2605 g_xml_element_table(l_xml_element_count).tagvalue := round(rec_LEGEMP.exempt_limit_after);
2606 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="21169"';
2607 l_xml_element_count := l_xml_element_count + 1;
2608 --
2609 g_xml_element_table(l_xml_element_count).tagname := 'Kontrollsummer-grp-4909';
2610 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2611 l_xml_element_count := l_xml_element_count + 1;
2612 --
2613 --
2614 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-4910';
2615 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2616 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4910"';
2617 l_xml_element_count := l_xml_element_count + 1;
2618 -- Sum of all the amounts
2619 g_xml_element_table(l_xml_element_count).tagname := 'ArbeidsgiveravgiftSkyldig-datadef-223';
2620 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(rec_sum_neg_to_zero.amt_sum)); --14260836
2621 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="223"';
2622 l_xml_element_count := l_xml_element_count + 1;
2623 --
2624 g_xml_element_table(l_xml_element_count).tagname := 'Arbeidsgiveravgift-grp-4910';
2625 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2626 l_xml_element_count := l_xml_element_count + 1;
2627 --
2628 --
2629 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-4911';
2630 g_xml_element_table(l_xml_element_count).tagvalue := '_START_';
2631 g_xml_element_table(l_xml_element_count).tagattrb := 'gruppeid="4911"';
2632 l_xml_element_count := l_xml_element_count + 1;
2633 -- Withholding Tax Total
2634 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-datadef-2903';
2635 g_xml_element_table(l_xml_element_count).tagvalue := ABS(round(l_wt_total));
2636 g_xml_element_table(l_xml_element_count).tagattrb := 'orid="2903"';
2637 l_xml_element_count := l_xml_element_count + 1;
2638 --
2639 g_xml_element_table(l_xml_element_count).tagname := 'Forskuddstrekk-grp-4911';
2640 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2641 l_xml_element_count := l_xml_element_count + 1;
2642 --
2643 --
2644 g_xml_element_table(l_xml_element_count).tagname := 'Resultater-grp-74';
2645 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2646 l_xml_element_count := l_xml_element_count + 1;
2647 --
2648 g_xml_element_table(l_xml_element_count).tagname := 'Skjema';
2649 g_xml_element_table(l_xml_element_count).tagvalue := '_END_';
2650 l_xml_element_count := l_xml_element_count + 1;
2651 --
2652 write_to_clob_for_xml(p_xml);
2653 --
2654 hr_utility.set_location('Leaving ' || l_proc_name, 1000);
2655 --
2656 END get_xml_rep;
2657 --
2658 END PAY_NO_ARC_RSEA_07;