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