1 package body py_gb_asg as
2 /* $Header: pygbasg.pkb 120.1 2005/06/30 06:43:13 tukumar noship $ */
3
4 PROCEDURE payroll_transfer (p_assignment_id IN NUMBER) is
5 -- if an assignment transfers from one payroll to another then the
6 -- assignment latest balances must be deleted. Also, the person
7 -- latest balances must be deleted for the person who owns this
8 -- assignment. This is called from perwsema_leg.payroll_transfer.
9 --
10 -- Use sysdate to get single datetrack row.
11 cursor csr_person_details (c_assignment_id in number) is
12 select person_id
13 from per_all_assignments_f
14 where assignment_id = c_assignment_id
15 and sysdate between
16 effective_start_date and effective_end_date;
17 --
18 l_person_id number;
19 --
20 BEGIN
21 --
22 hr_utility.set_location('payroll_transfer',10);
23 delete from pay_assignment_latest_balances
24 where assignment_id = p_assignment_id;
25 --
26 hr_utility.set_location('payroll_transfer',20);
27 open csr_person_details(p_assignment_id);
28 fetch csr_person_details into l_person_id;
29 close csr_person_details;
30 --
31 if l_person_id is not null then
32 hr_utility.set_location('payroll_transfer',30);
33 delete from pay_person_latest_balances
34 where person_id = l_person_id;
35 end if;
36 --
37 end payroll_transfer;
38 --
39 -------------------------------------------------------------------------
40 -- PROCEDURE delete_per_latest_balances
41 -- This procedure deletes the person level balances where required.
42 -- All rows should be deleted from the person latest balances table
43 -- for the parameter person given certain events to ensure that the
44 -- Aggregated PAYE figures are correct, and to ensure that the correct
45 -- values are displayed in the application forms.
46 -------------------------------------------------------------------------
47 --
48 PROCEDURE delete_per_latest_balances
49 (p_person_id number,
50 p_assignment_id number,
51 p_effective_date date) is
52 --
53 -- There are 2 events, a termination of multi assignment or a creation of
54 -- a new multi assignment that will trigger the person level latest balances
55 -- to be trashed. These only need to be trashed where there are multiple
56 -- assignments and the people have Aggregated PAYE (DDF set on person).
57 -- Note this is called at assignment level hence must check for multis.
58 --
59 cursor csr_person_details (c_person_id in number,
60 c_effective_date in date) is
61 select per_information10
62 from per_all_people_f
63 where person_id = c_person_id
64 and c_effective_date between
65 effective_start_date and effective_end_date;
66 --
67 cursor csr_multi_assignments (c_person_id in number,
68 c_effective_date in date) is
69 select count(paf.assignment_id)
70 from per_all_assignments_f paf
71 where person_id = c_person_id
72 and c_effective_date between
73 paf.effective_start_date and paf.effective_end_date;
74 --
75 l_aggregated_flag varchar2(1);
76 l_num_assgt number;
77 l_function_name varchar2(25) := 'delete_per_latest_bal';
78 --
79 begin
80 --
81 open csr_multi_assignments(p_person_id, p_effective_date);
82 fetch csr_multi_assignments into l_num_assgt;
83 close csr_multi_assignments;
84 --
85 hr_utility.set_location(l_function_name,10);
86 if l_num_assgt > 1 then
87 -- more than one asgt at this point in time, check
88 -- aggregated PAYE flag.
89 open csr_person_details(p_person_id, p_effective_date);
90 fetch csr_person_details into l_aggregated_flag;
91 close csr_person_details;
92 --
93 hr_utility.set_location(l_function_name,20);
94 if upper(l_aggregated_flag) = 'Y' then
95 hr_utility.set_location(l_function_name,30);
96 BEGIN
97 DELETE from pay_person_latest_balances
98 WHERE person_id = p_person_id;
99 END;
100 end if;
101 end if;
102 --
103 end delete_per_latest_balances;
104 --
105 end py_gb_asg;
[Home] [Help]