eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_DK_PR_LE

Source


1 PACKAGE BODY PAY_DK_PR_LE AS
2 /* $Header: pydkprle.pkb 120.13 2012/01/19 09:07:59 rpahune ship $ */
3 
4 
5 	g_debug   boolean   :=  hr_utility.debug_enabled;
6 	l_business_group_id		NUMBER;
7 	/* variables to store the input values*/
8 	l_archive		VARCHAR2(3);
9 	l_from_date		DATE;
10 	l_to_date		DATE;
11 	l_effective_date	DATE;
12 	l_payroll_id		NUMBER;
13 	l_legal_employer_id NUMBER ;
14 	l_legal_employer_name VARCHAR2(240);
15 	l_element_set_id     NUMBER;
16 	g_err_num	     NUMBER;
17 
18 ------------------------------------------------------------------------------------------------
19 /* GET PARAMETER */
20 FUNCTION GET_PARAMETER(
21 	 p_parameter_string IN VARCHAR2
22 	,p_token            IN VARCHAR2
23 	,p_segment_number   IN NUMBER default NULL ) RETURN VARCHAR2
24  IS
25 	   l_parameter  pay_payroll_actions.legislative_parameters%TYPE:=NULL;
26 	   l_start_pos  NUMBER;
27 	   l_delimiter  VARCHAR2(1):=' ';
28 
29 BEGIN
30 IF g_debug THEN
31 	  hr_utility.set_location(' Entering Function GET_PARAMETER',10);
32 END IF;
33 
34 	 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
35 
36 	 IF l_start_pos = 0 THEN
37 		l_delimiter := '|';
38 		l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
39 	 END IF;
40 
41  IF l_start_pos <> 0 THEN
42 	l_start_pos := l_start_pos + length(p_token||'=');
43 	l_parameter := substr(p_parameter_string, l_start_pos,
44 			  instr(p_parameter_string||' ', l_delimiter,l_start_pos) - l_start_pos);
45 
46 	 IF p_segment_number IS NOT NULL THEN
47 		l_parameter := ':'||l_parameter||':';
48 		l_parameter := substr(l_parameter,
49 		instr(l_parameter,':',1,p_segment_number)+1,
50 		instr(l_parameter,':',1,p_segment_number+1) -1
51 		- instr(l_parameter,':',1,p_segment_number));
52 	END IF;
53 END IF;
54 
55    RETURN l_parameter;
56 IF g_debug THEN
57 	      hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
58 END IF;
59 
60  END GET_PARAMETER;
61 
62 ------------------------------------------------------------------------------------------------
63 /* GET ALL PARAMETERS */
64 PROCEDURE GET_ALL_PARAMETERS(
65  		 p_payroll_action_id	IN           NUMBER
66 		,p_business_group_id    OUT  NOCOPY  NUMBER
67 		,p_effective_date	OUT  NOCOPY  DATE
68 		,p_archive		OUT  NOCOPY  VARCHAR2
69 		,p_element_set_id    OUT  NOCOPY  NUMBER
70 		,p_legal_employer_id OUT NOCOPY NUMBER
71 		,p_payroll_id		OUT  NOCOPY  NUMBER
72 		,p_fromdate		OUT NOCOPY DATE
73 		,p_todate		OUT NOCOPY DATE
74 		)IS
75 
76 
77 	CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
78 	SELECT
79     	fnd_date.canonical_to_date(PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'FROM_DATE'))
80 		,fnd_date.canonical_to_date(PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'TO_DATE'))
81 	  	,PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
82 	  	,PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_ID')
83 		,PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'ELEMENT_SET_ID')
84 		,PAY_DK_PR_LE.GET_PARAMETER(legislative_parameters,'ARCHIVE')
85 		,effective_date
86 		,business_group_id
87 	FROM  pay_payroll_actions
88 	WHERE payroll_action_id = p_payroll_action_id;
89 
90 BEGIN
91 
92 	 OPEN csr_parameter_info (p_payroll_action_id);
93 	 FETCH csr_parameter_info  INTO	p_fromdate,p_todate,p_payroll_id ,p_legal_employer_id,
94      p_element_set_id,p_archive,
95 	 p_effective_date ,p_business_group_id;
96 	 CLOSE csr_parameter_info;
97 IF g_debug THEN
98       hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
99 END IF;
100 
101  END GET_ALL_PARAMETERS;
102 
103 -------------------------------------------------------------------------------------------------------------------------
104 /*FUNCTION TO GET DEFINED BALANCE ID*/
105 FUNCTION GET_DEFINED_BALANCE_ID
106   (p_input_value_id	    IN  VARCHAR2
107   ,p_dbi_suffix     		IN  VARCHAR2)
108    RETURN NUMBER IS
109   l_defined_balance_id 		NUMBER;
110 
111 BEGIN
112 
113 SELECT
114     defined_balance_id
115 INTO
116 	l_defined_balance_id
117 FROM
118 (
119 SELECT
120 	pdb.defined_balance_id defined_balance_id
121 FROM
122 	pay_defined_balances      pdb
123 	,pay_balance_types         pbt
124 	,pay_balance_dimensions    pbd
125 WHERE
126 	pbd.database_item_suffix = p_dbi_suffix
127 	AND    (pbd.legislation_code = 'DK' OR pbt.business_group_id = l_business_group_id)
128     AND     pbt.input_value_id = p_input_value_id
129 	AND    (pbt.legislation_code = 'DK' OR pbt.business_group_id = l_business_group_id)
130 	AND    pdb.balance_type_id = pbt.balance_type_id
131 	AND    pdb.balance_dimension_id = pbd.balance_dimension_id
132 	AND    (pdb.legislation_code = 'DK' OR pbt.business_group_id = l_business_group_id)
133 )
134 WHERE ROWNUM < 2;
135 l_defined_balance_id := NVL(l_defined_balance_id,0);
136 RETURN l_defined_balance_id ;
137 
138 EXCEPTION WHEN OTHERS THEN
139 RETURN NULL ;
140 
141 END get_defined_balance_id;
142 -------------------------------------------------------------------------------------------------------------------------
143 /*Funtion to get the costed code- Bug Fix 4962281*/
144 FUNCTION COSTED_CODE
145 	(p_run_result_id IN NUMBER
146 	,p_input_value_id IN NUMBER)
147 	RETURN VARCHAR2  IS
148 
149 l_costed_code VARCHAR2(250);
150 
151 CURSOR csr_costed_code IS
152 SELECT
153 pcak.concatenated_segments cost_code
154 FROM
155 pay_costs pc
156 ,pay_cost_allocation_keyflex pcak
157 WHERE
158 	NVL (pc.distributed_input_value_id, pc.input_value_id) = p_input_value_id
159 	AND pc.run_result_id = p_run_result_id
160 	AND pc.balance_or_cost       = 'C'
161 	AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id;
162 BEGIN
163 OPEN csr_costed_code;
164 FETCH csr_costed_code INTO l_costed_code;
165 CLOSE csr_costed_code;
166 
167 RETURN l_costed_code;
168 
169 END COSTED_CODE;
170 ------------------------------------------------------------------------------------------------
171 
172  /* RANGE CODE */
173  PROCEDURE RANGE_CODE (pactid    IN    NUMBER
174 		      ,sqlstr    OUT   NOCOPY VARCHAR2)
175  IS
176 
177 -- Variable declarations
178 
179 	l_count			NUMBER := 0;
180 	l_action_info_id	NUMBER;
181 	l_ovn			NUMBER;
182 	l_element_set_name VARCHAR2(240);
183 	l_payroll_name VARCHAR2(240);
184 	l_business_group_name VARCHAR2(240);
185 
186 --cursor to check current archive exists
187 cursor csr_count is
188 select count(*)
189 from   pay_action_information
190 where  action_information_category = 'EMEA REPORT DETAILS'
191 and    action_information1         = 'PYDKPRLEA'
192 and    action_context_id           = pactid;
193 
194 /*Bug fix 4961408-Cursor to get the element ytd*/
195 CURSOR csr_pr_ele_ytd
196 	(p_ele_type_id NUMBER
197 	,p_payroll_id NUMBER
198 	,p_legal_employer_id NUMBER
199 	,p_from_date DATE
200 	,p_to_date DATE)
201 IS
202  SELECT
203 	SUM (pay_balance_pkg.get_value(pay_dk_pr_le.get_defined_balance_id
204         (pivf.input_value_id,'_ASG_RUN')
205         	,paa1.assignment_action_id)) ele_ytd
206 FROM
207 	per_all_assignments_f  paaf
208 	,pay_all_payrolls_f ppf
209 	,pay_assignment_actions paa1
210 	,pay_assignment_actions paa2
211 	,pay_payroll_actions ppa1
212 	,pay_payroll_actions ppa2
213 	,pay_run_results prr
214 	,pay_input_values_f pivf
215 	,pay_element_types_f petf
216 	,pay_action_interlocks  pai
217 
218 WHERE
219 	paaf.business_group_id = l_business_group_id -- BG Check
220 	AND ppf.payroll_id = p_payroll_id
221 	AND petf.element_type_id = p_ele_type_id
222 	AND paa1.tax_unit_id = p_legal_employer_id
223 	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
224 	AND  paaf.payroll_id = ppf.payroll_id
225 	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
226 	AND paa1.assignment_id = paaf.assignment_id
227 	AND paa1.action_status = 'C'  -- Completed
228 
229 	--for payroll actions
230 	AND ppa1.date_earned between p_from_date and p_to_date --date condition
231 	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
232 	AND ppa1.payroll_action_id = paa1.payroll_action_id
233 
234 	--for prepayments
235 	AND    paa2.action_status           IN ('C','S') -- 10229494
236 	AND    paa2.assignment_action_id    = pai.locking_action_id
237 	AND    paa2.payroll_action_id       = ppa2.payroll_action_id
238 	AND    ppa2.action_type            IN ('P','U')
239 	AND    ppa2.date_earned between p_from_date and p_to_date
240 
241 	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
242 	/*date check*/
243 	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
244 	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
245 	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date
246 
247 		-- for run results
248 	AND prr.assignment_action_id = paa1.assignment_action_id
249 
250 	AND petf.element_type_id = prr.element_type_id
251 	AND pivf.element_type_id = prr.element_type_id
252 	AND pivf.name='Pay Value' ;
253 
254 /* Cursor to get details if element set is null -Bug fix 4968059*/
255 CURSOR csr_pr_all_ele(p_payroll_action_id    NUMBER)
256 IS
257 SELECT
258 	organization_id
259 	,organization_name
260 	,tax_unit_id
261 	,legal_employer_name
262 	,payroll_name
263 	,payroll_id
264 	,ele_type_id
265 	,ele_proc_prior
266 	,ele_name
267 	,input_value_id
268 	,date_earned
269 	,costed_code
270 	,SUM(pay_value) pay_value
271 --	,SUM(balance_amount) balance_amount
272 FROM
273 (
274 SELECT
275 	paaf.organization_id organization_id
276 	,haou2.NAME organization_name
277 	,paa1.assignment_action_id	 asg_act_id
278 	,paa1.tax_unit_id	 tax_unit_id
279 	,haou1.NAME legal_employer_name
280 	,ppf.payroll_name payroll_name
281 	,ppf.payroll_id payroll_id
282 	,petf.element_type_id ele_type_id
283 	,petf.processing_priority ele_proc_prior
284     ,NVL(petf.reporting_name,petf.element_name) ele_name
285 	,pay_dk_pr_le.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
286 	,pivf.input_value_id  input_value_id
287 	,to_char(ppa1.date_earned,'DD-MON-RRRR')    date_earned
288 	,pay_balance_pkg.get_value(pay_dk_pr_le.get_defined_balance_id
289         (pivf.input_value_id,'_ASG_RUN')
290         	,paa1.assignment_action_id) pay_value
291 /*	,pay_balance_pkg.get_value(pay_dk_pr_le.get_defined_balance_id
292         (pivf.input_value_id,'_ASG_YTD'),
293         	paa1.assignment_action_id) balance_amount*/
294 FROM
295 	per_all_assignments_f  paaf
296 	,pay_assignment_actions paa1
297 	,pay_assignment_actions paa2
298 	,pay_run_results prr
299 	,pay_input_values_f pivf
300 	,pay_element_types_f petf
301 	,pay_all_payrolls_f ppf
302 	,pay_payroll_actions ppa1
303 	,pay_payroll_actions ppa2
304 	,hr_all_organization_units haou1
305 	,hr_all_organization_units haou2
306 	,pay_action_interlocks  pai
307 
308 WHERE
309 	paaf.business_group_id = l_business_group_id -- BG Check
310 	AND paa1.tax_unit_id = nvl(l_legal_employer_id,paa1.tax_unit_id)
311 	AND ppf.payroll_id = nvl(l_payroll_id,ppf.payroll_id)
312 	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
313 	AND  paaf.payroll_id = ppf.payroll_id
314 	AND paa1.tax_unit_id= haou1.organization_id
315 	AND paaf.organization_id = haou2.organization_id
316 	AND paa1.assignment_id = paaf.assignment_id
317 	AND paa1.action_status = 'C'  -- Completed
318 
319 	--for payroll actions
320 	AND ppa1.payroll_action_id = paa1.payroll_action_id
321 	AND ppa1.date_earned between l_from_date and l_to_date --date condition
322 	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
323 
324 	--for prepayments
325 	AND    paa2.action_status           IN ('C','S') -- 10229494
326 	AND    paa2.assignment_action_id    = pai.locking_action_id
327 	AND    paa2.payroll_action_id       = ppa2.payroll_action_id
328 	AND    ppa2.action_type            IN ('P','U')
329 	AND    ppa2.date_earned between l_from_date and l_to_date
330 
331 	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
332 	/*date check*/
333 	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
334 	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
335 	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date
336 
337 	-- for run results
338 	AND prr.assignment_action_id = paa1.assignment_action_id
339 
340 	AND petf.element_type_id = prr.element_type_id
341 	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
342 	AND petf.element_name <> 'Tax'    --To exclude Tax
343     AND petf.element_name <> 'Mileage Claim' -- To exclude Mileage Claim
344 	AND pivf.element_type_id = prr.element_type_id
345 	AND pivf.name='Pay Value')
346 
347 	GROUP BY
348 	organization_id
349 	,organization_name
350 	,tax_unit_id
351 	,legal_employer_name
352 	,payroll_name
353 	,payroll_id
354 	,ele_type_id
355 	,ele_proc_prior
356 	,ele_name
357 	,input_value_id
358 	,date_earned
359 	,costed_code
360 ORDER BY tax_unit_id,payroll_id,organization_id,ele_proc_prior,ele_type_id;
361 
362 /* Cursor to get details if element set is specified*/
363 CURSOR csr_pr_ele_set(p_payroll_action_id    NUMBER)
364 IS
365 SELECT
366 	organization_id
367 	,organization_name
368 	,tax_unit_id
369 	,legal_employer_name
370 	,payroll_name
371 	,payroll_id
372 	,ele_type_id
373 	,ele_proc_prior
374 	,ele_name
375 	,input_value_id
376 	,date_earned
377 	,costed_code
378 	,SUM(pay_value) pay_value
379 --	,SUM(balance_amount) balance_amount
380 FROM
381 (
382 SELECT
383 	paaf.organization_id organization_id
384 	,haou2.NAME organization_name
385 	,paa1.assignment_action_id	 asg_act_id
386 	,paa1.tax_unit_id	 tax_unit_id
387 	,haou1.NAME legal_employer_name
388 	,ppf.payroll_name payroll_name
389 	,ppf.payroll_id payroll_id
390 	,petf.element_type_id ele_type_id
391 	,petf.processing_priority ele_proc_prior
392     ,NVL(petf.reporting_name,petf.element_name) ele_name
393 	,pay_dk_pr_le.costed_code(prr.run_result_id,pivf.input_value_id) costed_code
394 	,pivf.input_value_id  input_value_id
395 	,to_char(ppa1.date_earned,'DD-MON-RRRR')    date_earned
396 	,pay_balance_pkg.get_value(pay_dk_pr_le.get_defined_balance_id
397         (pivf.input_value_id,'_ASG_RUN')
398         	,paa1.assignment_action_id) pay_value
399 /*	,pay_balance_pkg.get_value(pay_dk_pr_le.get_defined_balance_id
400         (pivf.input_value_id,'_ASG_YTD'),
401         	paa1.assignment_action_id) balance_amount*/
402 FROM
403 	per_all_assignments_f  paaf
404 	,pay_assignment_actions paa1
405 	,pay_assignment_actions paa2
406 	,pay_run_results prr
407 	,pay_input_values_f pivf
408 	,pay_element_types_f petf
409 	,pay_all_payrolls_f ppf
410 	,pay_payroll_actions ppa1
411 	,pay_payroll_actions ppa2
412 	,pay_element_set_members pesm
413 	,hr_all_organization_units haou1
414 	,hr_all_organization_units haou2
415 	,pay_action_interlocks  pai
416 
417 WHERE
418 	paaf.business_group_id = l_business_group_id -- BG Check
419 	AND pesm.element_set_id = l_element_set_id
420 	AND paa1.tax_unit_id = nvl(l_legal_employer_id,paa1.tax_unit_id)
421 	AND ppf.payroll_id = nvl(l_payroll_id,ppf.payroll_id)
422 	AND  paaf.assignment_status_type_id = 1 -- to check for active assignments
423 	AND  paaf.payroll_id = ppf.payroll_id
424 	AND paa1.tax_unit_id= haou1.organization_id
425 	AND paaf.organization_id = haou2.organization_id
426 	AND paa1.assignment_id = paaf.assignment_id
427 	AND paa1.action_status = 'C'  -- Completed
428 
429 	--for payroll actions
430 	AND ppa1.payroll_action_id = paa1.payroll_action_id
431 	AND ppa1.date_earned between l_from_date and l_to_date --date condition
432 	AND ppa1.action_type IN ('R','Q')  -- Payroll Run or Quickpay Run
433 
434 	--for prepayments
435 	AND    paa2.action_status           IN ('C','S') -- 10229494
436 	AND    paa2.assignment_action_id    = pai.locking_action_id
437 	AND    paa2.payroll_action_id       = ppa2.payroll_action_id
438 	AND    ppa2.action_type            IN ('P','U')
439 	AND    ppa2.date_earned between l_from_date and l_to_date
440 
441 	AND paa1.ASSIGNMENT_ACTION_ID = pai.locked_action_id
442 
443 	-- for run results
444 	AND prr.assignment_action_id = paa1.assignment_action_id
445 	/*date check*/
446 	AND    ppa2.date_earned between paaf.effective_start_date and paaf.effective_end_date
447 	AND    ppa2.date_earned between ppf.effective_start_date and ppf.effective_end_date
448 	AND    ppa2.date_earned between pivf.effective_start_date and pivf.effective_end_date
449 
450 	AND prr.element_type_id = pesm.element_type_id
451 	AND petf.element_type_id = prr.element_type_id
452 	AND (petf.legislation_code = 'DK' OR petf.business_group_id = l_business_group_id)
453 	AND petf.element_name <> 'Tax'    --To exclude Tax
454     AND petf.element_name <> 'Mileage Claim' -- To exclude Mileage Claim
455 	AND pivf.element_type_id = prr.element_type_id
456 	AND pivf.name='Pay Value')
457 
458 	GROUP BY
459 	organization_id
460 	,organization_name
461 	,tax_unit_id
462 	,legal_employer_name
463 	,payroll_name
464 	,payroll_id
465 	,ele_type_id
466 	,ele_proc_prior
467 	,ele_name
468 	,input_value_id
469 	,date_earned
470 	,costed_code
471 ORDER BY tax_unit_id,payroll_id,organization_id,ele_proc_prior,ele_type_id;
472 
473 
474 l_actid			NUMBER;
475 l_asgid			NUMBER := -999;
476 l_year_start    DATE ;
477 l_ele_ytd       NUMBER ;
478 --------------------------------------------------------------------------------
479 
480 BEGIN
481  IF g_debug THEN
482       hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
483 END IF;
484 
485 
486  -- the sql string to return
487  sqlstr := 'SELECT DISTINCT person_id
488 	FROM  per_people_f ppf
489 	     ,pay_payroll_actions ppa
490 	WHERE ppa.payroll_action_id = :payroll_action_id
491 	AND   ppa.business_group_id = ppf.business_group_id
492 	ORDER BY ppf.person_id';
493 
494   -- fetch the input parameter values
495  PAY_DK_PR_LE.GET_ALL_PARAMETERS(
496 		 pactid
497 		,l_business_group_id
498 		,l_effective_date
499 		,l_archive
500 		,l_element_set_id
501  		,l_legal_employer_id
502 		,l_payroll_id
503 		,l_from_date
504 		,l_to_date) ;
505 
506  -- check if we have to archive again
507  IF  (l_archive = 'Y')   THEN
508 
509    -- check if record for current archive exists
510    OPEN csr_count;
511    FETCH csr_count INTO l_count;
512    CLOSE csr_count;
513 
514 
515    -- archive Report Details only if no record exists
516   IF (l_count < 1) THEN
517 
518 ---to fetch the element set name
519    BEGIN
520 	SELECT pes.element_set_name INTO
521 		l_element_set_name
522 	FROM
523 		pay_element_sets pes
524 	WHERE pes.element_set_id=l_element_set_id;
525   EXCEPTION
526 	WHEN OTHERS THEN
527 	NULL;
528    END;
529 
530 --to fetch the payroll name
531 BEGIN
532 	SELECT ppf.payroll_name INTO l_payroll_name
533 	FROM
534 	-- Replaced view  pay_payrolls_f with table pay_all_payrolls_f for bug fix 5231458
535 	--pay_payrolls_f ppf
536 	pay_all_payrolls_f ppf
537 	WHERE ppf.payroll_id=l_payroll_id;
538 EXCEPTION
539 	WHEN OTHERS THEN
540 	NULL;
541 END;
542 
543 --to fetch the organization name
544 BEGIN
545 	SELECT haou.name INTO l_business_group_name
546 	FROM hr_all_organization_units haou
547 	WHERE haou.organization_id=l_business_group_id;
548 EXCEPTION
549 	WHEN OTHERS THEN
550 	NULL;
551 END;
552 
553 BEGIN
554 	SELECT haou.name INTO l_legal_employer_name
555 	FROM hr_all_organization_units haou
556 	WHERE haou.organization_id=l_legal_employer_id;
557 EXCEPTION
558 	WHEN OTHERS THEN
559 	NULL;
560 END;
561 
562 
563 
564 -- Archive the REPORT DETAILS
565 
566 	pay_action_information_api.create_action_information (
567 	 p_action_information_id        => l_action_info_id	-- out parameter
568 	,p_object_version_number        => l_ovn		-- out parameter
569 	,p_action_context_id            => pactid		-- context id = payroll action id (of Archive)
570 	,p_action_context_type          => 'PA'			-- context type
571 	,p_effective_date               => l_effective_date	-- Date of running the archive
572 	,p_action_information_category  => 'EMEA REPORT DETAILS' -- Information Category
573 	,p_tax_unit_id                  => NULL			-- Legal Employer ID
574 	,p_jurisdiction_code            => NULL			-- Tax Municipality ID
575 	,p_action_information1          => 'PYDKPRLEA'	-- Conc Prg Short Name
576 	,p_action_information2          => l_business_group_id  	-- Business Group ID
577 	,p_action_information3          => l_from_date		-- Reporting from date
578 	,p_action_information4          => l_to_date		-- Reporting to date
579 	,p_action_information5          => l_business_group_name	--Business Group Name
580 	,p_action_information6          => l_payroll_name	--payroll name
581 	,p_action_information7          => l_legal_employer_name -- Legal Employer Name
582 	,p_action_information8          => l_element_set_name);	-- element set name
583 
584    END IF; -- l_count < 1
585 
586 l_year_start:= TO_DATE ('01/01'||TO_CHAR(l_from_date,'YYYY'),'DD/MM/YYYY');
587 
588 IF (l_element_set_id IS NULL) THEN
589 FOR csr_rec IN csr_pr_all_ele(pactid) LOOP
590 IF (csr_rec.pay_value > 0) THEN -- to archive only elements which have pay value > 0
591 	/*Bug fix 4961408*/
592 	OPEN csr_pr_ele_ytd(csr_rec.ele_type_id
593 			    ,csr_rec.payroll_id
594 			    ,csr_rec.tax_unit_id
595 			    ,l_year_start
596 			    ,csr_rec.date_earned);
597 	FETCH csr_pr_ele_ytd INTO l_ele_ytd;
598 	CLOSE csr_pr_ele_ytd;
599 
600   -- Creating Archive Entries
601   pay_action_information_api.create_action_information (
602 
603 	 p_action_information_id        => l_action_info_id		-- out parameter
604 	,p_object_version_number        => l_ovn			-- out parameter
605 	,p_action_context_id            => pactid      			-- context id = assignment action id (of Archive)
606 	,p_action_context_type          => 'PA'				-- context type
607 	,p_effective_date               => l_effective_date		-- Date of running the archive
608 	,p_assignment_id	        => NULL			        -- Assignment ID
609 	,p_action_information_category  => 'EMEA REPORT INFORMATION'	-- Information Category
610 	,p_tax_unit_id                  => csr_rec.tax_unit_id		-- Legal Employer ID
611 	,p_jurisdiction_code            => NULL			        -- Tax Municipality ID
612 	,p_action_information1          => 'PYDKPRLEA'		        --Con Program Short Name
613 	,p_action_information2          => csr_rec.payroll_id		-- PayrollID
614 	,p_action_information3          => csr_rec.payroll_name		-- Payroll Name
615 	,p_action_information4          => csr_rec.legal_employer_name	-- Legal Employer Name
616 	,p_action_information5          => csr_rec.organization_id	-- Organization ID
617 	,p_action_information6          => csr_rec.organization_name	-- Organization Name
618 	,p_action_information7          =>  csr_rec.ele_type_id		-- element type id
619 	,p_action_information8          =>  csr_rec.ele_proc_prior	-- processing priority
620 	,p_action_information9          =>  csr_rec.ele_name		-- element name
621 	,p_action_information10         => csr_rec.date_earned		-- date_earned
622 	,p_action_information11         =>  csr_rec.costed_code		-- Costed Value
623 	/*Storing in Canonical format to fix issues due to varying numeric formats*/
624 	,p_action_information12         => fnd_number.number_to_canonical(csr_rec.pay_value) -- Pay Value
625 	,p_action_information13         => fnd_number.number_to_canonical(l_ele_ytd)         -- element YTD
626 	);
627 
628 END IF ;
629 END LOOP;
630 
631 ELSIF (l_element_set_id IS NOT NULL) THEN
632 FOR csr_rec IN csr_pr_ele_set(pactid) LOOP
633 IF (csr_rec.pay_value > 0) THEN -- to archive only elements which have pay value > 0
634   -- Creating Archive Entries
635 		/*Bug fix 4961408*/
636 	OPEN csr_pr_ele_ytd(csr_rec.ele_type_id
637 			    ,csr_rec.payroll_id
638 			    ,csr_rec.tax_unit_id
639 			    ,l_year_start
640 			    ,csr_rec.date_earned);
641 	FETCH csr_pr_ele_ytd INTO l_ele_ytd;
642 	CLOSE csr_pr_ele_ytd;
643 
644   pay_action_information_api.create_action_information (
645 
646 	 p_action_information_id        => l_action_info_id		-- out parameter
647 	,p_object_version_number        => l_ovn			-- out parameter
648 	,p_action_context_id            => pactid      			-- context id = assignment action id (of Archive)
649 	,p_action_context_type          => 'PA'				-- context type
650 	,p_effective_date               => l_effective_date		-- Date of running the archive
651 	,p_assignment_id	        => NULL			-- Assignment ID
652 	,p_action_information_category  => 'EMEA REPORT INFORMATION'	-- Information Category
653 	,p_tax_unit_id                  => csr_rec.tax_unit_id		-- Legal Employer ID
654 	,p_jurisdiction_code            => NULL			-- Tax Municipality ID
655 	,p_action_information1          => 'PYDKPRLEA'		 --Con Program Short Name
656 	,p_action_information2          => csr_rec.payroll_id		-- PayrollID
657 	,p_action_information3          => csr_rec.payroll_name		-- Payroll Name
658 	,p_action_information4          => csr_rec.legal_employer_name	-- Legal Employer Name
659 	,p_action_information5          => csr_rec.organization_id	-- Organization ID
660 	,p_action_information6          => csr_rec.organization_name	-- Organization Name
661 	,p_action_information7          =>  csr_rec.ele_type_id		-- element type id
662 	,p_action_information8          =>  csr_rec.ele_proc_prior		-- processing priority
663 	,p_action_information9          =>  csr_rec.ele_name		-- element name
664 	,p_action_information10         => csr_rec.date_earned		-- date_earned
665 	,p_action_information11         =>  csr_rec.costed_code		-- Costed Value
666 	/*Storing in Canonical format to fix issues due to varying numeric formats*/
667 	,p_action_information12         => fnd_number.number_to_canonical(csr_rec.pay_value) -- Pay Value
668 	,p_action_information13         => fnd_number.number_to_canonical(l_ele_ytd)         -- element YTD
669 	);
670 END IF ;
671 END LOOP;
672 END IF ;
673 END IF; -- l_archive = 'Y'
674 ---------------------------------------------------------------------------
675 
676 IF g_debug THEN
677       hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
678  END IF;
679  END RANGE_CODE;
680 
681 -------------------------------------------------------------------------------------------------------------------------
682  /* INITIALIZATION CODE */
683  PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
684  IS
685 
686  BEGIN
687  IF g_debug THEN
688       hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
689  END IF;
690 	NULL;
691 IF g_debug THEN
692       hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
693 END IF;
694 exception when others then
695  g_err_num := SQLCODE;
696   IF g_debug THEN
697       hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
698 END IF;
699 
700  END INITIALIZATION_CODE;
701 
702 ------------------------------------------------------------------------------------------------
703  /* ASSIGNMENT ACTION CODE */
704 PROCEDURE ASSIGNMENT_ACTION_CODE
705  (p_payroll_action_id     IN NUMBER
706  ,p_start_person          IN NUMBER
707  ,p_end_person            IN NUMBER
708  ,p_chunk                 IN NUMBER)
709  IS
710 
711 BEGIN
712 IF g_debug THEN
713       hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
714 END IF;
715  END ASSIGNMENT_ACTION_CODE;
716 
717 ------------------------------------------------------------------------------------------------
718  /* ARCHIVE CODE */
719 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
720 		      ,p_effective_date    IN DATE)
721  IS
722 BEGIN
723 	 IF g_debug THEN
724 			hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
725 	 END IF;
726 	 IF g_debug THEN
727 			hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
728 	 END IF;
729 END ARCHIVE_CODE;
730 
731 -------------------------------------------------------------------------------------------------------------------------
732 
733 PROCEDURE POPULATE_DETAILS(p_payroll_action_id in VARCHAR2 ,
734 		  			       p_template_name in VARCHAR2 ,
735 						   p_xml OUT NOCOPY CLOB ) IS
736 l_employer hr_all_organization_units.name%type;
737 l_orgnumber hr_organization_information.org_information1%type;
738 l_from_date DATE;
739 l_to_date DATE;
740 -- For bug fix 5231458
741 --l_payroll_action_id varchar2(150);
742 l_payroll_action_id NUMBER;
743 l_archiver varchar2(150);
744 l_ele_type_id NUMBER:=-999;
745 l_flag NUMBER :=0;
746 l_leg_emp_id NUMBER :=-999 ;
747 l_payroll_id NUMBER :=-999;
748 l_org_id NUMBER := -999;
749 l_tot_ele_run NUMBER := 0;
750 l_tot_ele_ytd NUMBER :=0;
751 l_tot_org_ele_run NUMBER := 0;
752 l_tot_org_ele_ytd NUMBER :=0;
753 l_tot_le_ele_run NUMBER := 0;
754 l_tot_le_ele_ytd NUMBER :=0;
755 --l_prev_date_earned VARCHAR2(30):= '-999';
756 l_IANA_charset VARCHAR2 (50);
757 
758 CURSOR csr_pr_le
759 IS
760 SELECT
761 	pai1.action_information3||' to '||pai1.action_information4 report_period
762 	,pai2.action_information2 payroll_id
763 	,pai2.action_information3 payroll_name
764 	,pai2.tax_unit_id leg_emp_id
765 	,pai2.action_information4 leg_emp_name
766 	,pai2.action_information5 org_id
767 	,pai2.action_information6 org_name
768 	,pai2.action_information7 ele_type_id
769 	,pai2.action_information8 ele_proc_prior
770 	,pai2.action_information9 ele_name
771 	,pai2.action_information10 date_earned
772 	,pai2.action_information11 costed_code
773 	,fnd_number.canonical_to_number(pai2.action_information12) pay_value
774 	,fnd_number.canonical_to_number(pai2.action_information13) balance_amount
775 FROM
776 	pay_action_information pai1
777 	,pay_action_information pai2
778 WHERE
779         -- Removing to_char for bug fix 5231458
780 	--TO_CHAR(pai1.action_context_id)=l_payroll_action_id
781         pai1.action_context_id = l_payroll_action_id
782 	AND pai1.action_information_category='EMEA REPORT DETAILS'
783 	--AND TO_CHAR(pai2.action_context_id)= l_payroll_action_id
784 	AND pai2.action_context_id = l_payroll_action_id
785 	AND pai2.action_information_category='EMEA REPORT INFORMATION'
786 ORDER BY
787 	TO_NUMBER(pai2.action_information2)
788 	,pai2.tax_unit_id
789 	,TO_NUMBER(pai2.action_information5)
790 	,TO_NUMBER(pai2.action_information8)
791 	,TO_NUMBER(pai2.action_information7)
792 	,fnd_date.string_to_date(pai2.action_information10,'dd-mon-rrrr');
793 
794 BEGIN
795 IF p_payroll_action_id  IS NULL THEN
796 	BEGIN
797 	SELECT
798 		payroll_action_id
799 	INTO
800 		l_payroll_action_id
801 	FROM
802 		pay_payroll_actions ppa,
803 		fnd_conc_req_summary_v fcrs,
804 		fnd_conc_req_summary_v fcrs1
805 	WHERE
806 		fcrs.request_id = FND_GLOBAL.CONC_REQUEST_ID
807 		AND fcrs.priority_request_id = fcrs1.priority_request_id
808 		AND ppa.request_id between fcrs1.request_id  and fcrs.request_id
809 		AND ppa.request_id = fcrs1.request_id;
810 	EXCEPTION
811 	WHEN others THEN
812 		NULL;
813 	END ;
814 ELSE
815 	l_payroll_action_id:=p_payroll_action_id;
816 END IF;
817 
818 l_IANA_charset :=PAY_DK_GENERAL.get_IANA_charset ;
819 /*Setting the Character Set Dynamically*/
820 --p_xml := '<?xml version = "1.0" encoding = "UTF-8"?>';
821 p_xml := '<?xml version = "1.0" encoding = "'||l_IANA_charset||'"?>';
822 p_xml:=p_xml || '<START>';
823 
824 FOR csr_rec IN csr_pr_le LOOP
825 
826 IF (csr_rec.payroll_id <> l_payroll_id) THEN
827   IF (csr_pr_le%rowcount <> 1) THEN
828     p_xml:=p_xml || '<Tot_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_run,2),'999G999G990D99')),' ') || '</Tot_ele_run>';
829     p_xml:=p_xml || '<Tot_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_ytd,2),'999G999G990D99')),' ') || '</Tot_ele_ytd>';
830     l_tot_org_ele_run := l_tot_org_ele_run + l_tot_ele_run;
831     l_tot_org_ele_ytd := l_tot_org_ele_ytd + l_tot_ele_ytd;
832     l_tot_ele_run := 0;
833     l_tot_ele_ytd := 0;
834 	p_xml:= p_xml || '</ELEMENT_RECORD>';
835 	p_xml:=p_xml || '<Tot_org_ele_run>' || NVL(TRIM(TO_CHAR(ROUND (l_tot_org_ele_run,2),'999G999G990D99')),' ')|| '</Tot_org_ele_run>';
836     p_xml:=p_xml || '<Tot_org_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_org_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_org_ele_ytd>';
837     l_tot_le_ele_run := l_tot_le_ele_run + l_tot_org_ele_run;
838     l_tot_le_ele_ytd := l_tot_le_ele_ytd + l_tot_org_ele_ytd;
839     l_tot_org_ele_run := 0;
840     l_tot_org_ele_ytd := 0;
841 	p_xml := p_xml ||'</ORGANIZATION_RECORD>';
842 	p_xml:=p_xml || '<Tot_le_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_run,2),'999G999G990D99')),' ')|| '</Tot_le_ele_run>';
843     p_xml:=p_xml || '<Tot_le_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_le_ele_ytd>';
844     l_tot_le_ele_run := 0;
845     l_tot_le_ele_ytd := 0;
846 	p_xml := p_xml ||'</LEGAL_EMPLOYER_RECORD>';
847 	p_xml := p_xml ||'</PAYROLL_RECORD>';
848 	p_xml := p_xml ||'<PAYROLL_RECORD>';
849 	p_xml := p_xml ||'<LEGAL_EMPLOYER_RECORD>';
850 	p_xml := p_xml ||'<ORGANIZATION_RECORD>';
851 	p_xml:=p_xml || '<Report_Period>' || csr_rec.report_period || '</Report_Period>';
852 	p_xml:=p_xml || '<Legal_Employer>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.leg_emp_name) || '</Legal_Employer>';
853 	p_xml:=p_xml || '<Payroll_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.payroll_name) || '</Payroll_Name>';
854 	p_xml:= p_xml || '<ELEMENT_RECORD>';
855  END IF;
856 END IF;
857 IF (csr_rec.leg_emp_id <> l_leg_emp_id AND csr_rec.payroll_id = l_payroll_id) THEN
858   IF (csr_pr_le%rowcount <> 1) THEN
859     p_xml:=p_xml || '<Tot_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_run,2),'999G999G990D99')),' ') || '</Tot_ele_run>';
860     p_xml:=p_xml || '<Tot_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_ytd,2),'999G999G990D99')),' ') || '</Tot_ele_ytd>';
861      l_tot_org_ele_run := l_tot_org_ele_run + l_tot_ele_run;
862     l_tot_org_ele_ytd := l_tot_org_ele_ytd + l_tot_ele_ytd;
863      l_tot_ele_run := 0;
864     l_tot_ele_ytd := 0;
865 	p_xml:= p_xml || '</ELEMENT_RECORD>';
866 	p_xml:=p_xml || '<Tot_org_ele_run>' || NVL(TRIM(TO_CHAR(ROUND (l_tot_org_ele_run,2),'999G999G990D99')),' ')|| '</Tot_org_ele_run>';
867     p_xml:=p_xml || '<Tot_org_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_org_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_org_ele_ytd>';     l_tot_le_ele_run := l_tot_le_ele_run + l_tot_org_ele_run;
868     l_tot_le_ele_ytd := l_tot_le_ele_ytd + l_tot_org_ele_ytd;
869     l_tot_org_ele_run := 0;
870     l_tot_org_ele_ytd := 0;
871 	p_xml := p_xml ||'</ORGANIZATION_RECORD>';
872 	p_xml:=p_xml || '<Tot_le_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_run,2),'999G999G990D99')),' ')|| '</Tot_le_ele_run>';
873     p_xml:=p_xml || '<Tot_le_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_le_ele_ytd>';
874     l_tot_le_ele_run := 0;
875     l_tot_le_ele_ytd := 0;
876 	p_xml:= p_xml || '</LEGAL_EMPLOYER_RECORD>';
877 	p_xml:= p_xml || '<LEGAL_EMPLOYER_RECORD>';
878 	p_xml := p_xml ||'<ORGANIZATION_RECORD>';
879 	p_xml:=p_xml || '<Report_Period>' || csr_rec.report_period || '</Report_Period>';
880 	p_xml:=p_xml || '<Legal_Employer>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.leg_emp_name) || '</Legal_Employer>';
881 	p_xml:=p_xml || '<Payroll_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.payroll_name) || '</Payroll_Name>';
882 	p_xml:= p_xml || '<ELEMENT_RECORD>';
883   END IF;
884 END IF;
885 
886 IF (csr_rec.org_id <> l_org_id AND csr_rec.leg_emp_id = l_leg_emp_id
887 	AND csr_rec.payroll_id = l_payroll_id) THEN
888   IF (csr_pr_le%rowcount <> 1) THEN
889     p_xml:=p_xml || '<Tot_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_run,2),'999G999G990D99')),' ') || '</Tot_ele_run>';
890     p_xml:=p_xml || '<Tot_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_ytd,2),'999G999G990D99')),' ') || '</Tot_ele_ytd>';
891     l_tot_org_ele_run := l_tot_org_ele_run + l_tot_ele_run;
892     l_tot_org_ele_ytd := l_tot_org_ele_ytd + l_tot_ele_ytd;
893     l_tot_ele_run := 0;
894     l_tot_ele_ytd := 0;
895 	p_xml:= p_xml || '</ELEMENT_RECORD>';
896 	p_xml:=p_xml || '<Tot_org_ele_run>' || NVL(TRIM(TO_CHAR(ROUND (l_tot_org_ele_run,2),'999G999G990D99')),' ')|| '</Tot_org_ele_run>';
897     p_xml:=p_xml || '<Tot_org_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_org_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_org_ele_ytd>';    l_tot_le_ele_run := l_tot_le_ele_run + l_tot_org_ele_run;
898     l_tot_le_ele_ytd := l_tot_le_ele_ytd + l_tot_org_ele_ytd;
899     l_tot_org_ele_run := 0;
900     l_tot_org_ele_ytd := 0;
901 	p_xml := p_xml ||'</ORGANIZATION_RECORD>';
902 	p_xml := p_xml ||'<ORGANIZATION_RECORD>';
903 	p_xml:=p_xml || '<Report_Period>' || csr_rec.report_period || '</Report_Period>';
904 	p_xml:=p_xml || '<Legal_Employer>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.leg_emp_name) || '</Legal_Employer>';
905 	p_xml:=p_xml || '<Payroll_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.payroll_name) || '</Payroll_Name>';
906 	p_xml:= p_xml || '<ELEMENT_RECORD>';
907   END IF;
908 END IF;
909 
910 IF (csr_pr_le%ROWCOUNT = 1) THEN
911 	p_xml := p_xml ||'<PAYROLL_RECORD>';
912 	p_xml:= p_xml || '<LEGAL_EMPLOYER_RECORD>';
913 	p_xml := p_xml ||'<ORGANIZATION_RECORD>';
914 	p_xml:=p_xml || '<Report_Period>' || csr_rec.report_period || '</Report_Period>';
915 	p_xml:=p_xml || '<Legal_Employer>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.leg_emp_name) || '</Legal_Employer>';
916 	p_xml:=p_xml || '<Payroll_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.payroll_name) || '</Payroll_Name>';
917 	p_xml:= p_xml || '<ELEMENT_RECORD>';
918 END IF;
919 
920 IF( l_ele_type_id <> csr_rec.ele_type_id
921 	AND csr_rec.org_id = l_org_id AND csr_rec.leg_emp_id = l_leg_emp_id
922 	AND csr_rec.payroll_id = l_payroll_id)
923 THEN
924     p_xml:=p_xml || '<Tot_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_run,2),'999G999G990D99')),' ') || '</Tot_ele_run>';
925     p_xml:=p_xml || '<Tot_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_ytd,2),'999G999G990D99')),' ') || '</Tot_ele_ytd>';
926     l_tot_org_ele_run := l_tot_org_ele_run + l_tot_ele_run;
927     l_tot_org_ele_ytd := l_tot_org_ele_ytd + l_tot_ele_ytd;
928      l_tot_ele_run := 0;
929     l_tot_ele_ytd := 0;
930 	p_xml:= p_xml || '</ELEMENT_RECORD>';
931 	p_xml:= p_xml || '<ELEMENT_RECORD>';
932 END IF;
933 	p_xml:= p_xml || '<ELEMENT_RECORD_PER_RUN>';
934 	p_xml:=p_xml || '<Element_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.ele_name) || '</Element_Name>';
935 	p_xml:=p_xml || '<Organization_Name>' || hr_dk_utility.REPLACE_SPECIAL_CHARS(csr_rec.org_name) || '</Organization_Name>';
936 	p_xml:=p_xml || '<Date_Earned>' || csr_rec.date_earned || '</Date_Earned>';
937 	/*Bug fix - 4961779*/
938 	/*Bug fix-4961408 As seperate cursor csr_pr_ele_ytd has been written this code is no more required*/
939 	/*IF (l_prev_date_earned = csr_rec.date_earned OR l_prev_date_earned = '-999') THEN
940 	    l_tot_ele_ytd := l_tot_ele_ytd + NVL(csr_rec.balance_amount,0);
941 	ELSIF (l_prev_date_earned <> csr_rec.date_earned) THEN
942 	    l_tot_ele_ytd :=NVL(csr_rec.balance_amount,0);
943 	END IF ;
944 	l_prev_date_earned := csr_rec.date_earned;*/
945 	p_xml:=p_xml || '<Costed_code>' || NVL(csr_rec.costed_code,' ') || '</Costed_code>';
946 	p_xml:=p_xml || '<Pay_Value>' || NVL(TRIM(TO_CHAR(ROUND(csr_rec.pay_value,2),'999G999G990D99')),' ')|| '</Pay_Value>';
947 	p_xml:=p_xml || '<Balance_Amount>' || NVL(TRIM(TO_CHAR(ROUND(csr_rec.balance_amount,2),'999G999G990D99')),' ') || '</Balance_Amount>';
948 	p_xml:= p_xml || '</ELEMENT_RECORD_PER_RUN>';
949 
950 l_tot_ele_run := l_tot_ele_run +  NVL(csr_rec.pay_value,0);
951 l_tot_ele_ytd := NVL(csr_rec.balance_amount,0); -- To get the last balance value which will be the total for that element.
952 l_ele_type_id := csr_rec.ele_type_id;
953 l_org_id:=csr_rec.org_id;
954 l_leg_emp_id:=csr_rec.leg_emp_id;
955 l_payroll_id:=csr_rec.payroll_id;
956 l_flag :=1;
957 END LOOP;
958 IF (l_flag = 1) THEN
959     p_xml:=p_xml || '<Tot_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_run,2),'999G999G990D99')),' ') || '</Tot_ele_run>';
960     p_xml:=p_xml || '<Tot_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_ele_ytd,2),'999G999G990D99')),' ') || '</Tot_ele_ytd>';
961     l_tot_org_ele_run := l_tot_org_ele_run + l_tot_ele_run;
962     l_tot_org_ele_ytd := l_tot_org_ele_ytd + l_tot_ele_ytd;
963     l_tot_ele_run := 0;
964     l_tot_ele_ytd := 0;
965 	p_xml:=p_xml || '</ELEMENT_RECORD>';
966 	p_xml:=p_xml || '<Tot_org_ele_run>' || NVL(TRIM(TO_CHAR(ROUND (l_tot_org_ele_run,2),'999G999G990D99')),' ')|| '</Tot_org_ele_run>';
967     p_xml:=p_xml || '<Tot_org_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_org_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_org_ele_ytd>';    l_tot_le_ele_run := l_tot_le_ele_run + l_tot_org_ele_run;
968     l_tot_le_ele_ytd := l_tot_le_ele_ytd + l_tot_org_ele_ytd;
969     l_tot_org_ele_run := 0;
970     l_tot_org_ele_ytd := 0;
971 	p_xml := p_xml ||'</ORGANIZATION_RECORD>';
972 	p_xml:=p_xml || '<Tot_le_ele_run>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_run,2),'999G999G990D99')),' ')|| '</Tot_le_ele_run>';
973         p_xml:=p_xml || '<Tot_le_ele_ytd>' || NVL(TRIM(TO_CHAR(ROUND(l_tot_le_ele_ytd,2),'999G999G990D99')),' ')|| '</Tot_le_ele_ytd>';
974     l_tot_le_ele_run := 0;
975     l_tot_le_ele_ytd := 0;
976 	p_xml:= p_xml || '</LEGAL_EMPLOYER_RECORD>';
977 	p_xml:=p_xml || '</PAYROLL_RECORD>';
978 	p_xml:=p_xml || '</START>';
979 ELSIF (l_flag = 0) THEN
980 p_xml:=p_xml||'<PAYROLL_RECORD><LEGAL_EMPLOYER_RECORD><ORGANIZATION_RECORD><ELEMENT_RECORD><ELEMENT_RECORD_PER_RUN></ELEMENT_RECORD_PER_RUN></ELEMENT_RECORD></ORGANIZATION_RECORD></LEGAL_EMPLOYER_RECORD></PAYROLL_RECORD></START>';
981 END IF;
982 END POPULATE_DETAILS;
983 END PAY_DK_PR_LE;