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