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