[Home] [Help]
PACKAGE BODY: APPS.PER_IT_QH_TAX
Source
1 PACKAGE BODY per_it_qh_tax as
2 /* $Header: peitqhtx.pkb 120.0 2005/05/31 10:29:03 appldev noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' per_it_qh_tax.';
7 --
8 procedure update_it_tax_data
9 (p_rec in out nocopy per_qh_tax_query.taxrec
10 ,p_person_id in per_all_people_f.person_id%type
11 ,p_assignment_id in per_all_assignments_f.assignment_id%type
12 ,p_legislation_code in varchar2
13 ,p_effective_date in date
14 ) is
15 l_proc varchar2(72) := g_package||'update_it_tax_data';
16 l_cagr_segment1 per_cagr_grades_def.segment1%type;
17 l_cagr_segment2 per_cagr_grades_def.segment2%type;
18 l_cagr_segment3 per_cagr_grades_def.segment3%type;
19 l_concat_segments hr_soft_coding_keyflex.concatenated_segments%type;
20 l_new_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%type;
21 l_old_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%type;
22 l_collective_agreement_id per_coll_agree_grades_v.collective_agreement_id%type;
23 l_d_grade_type_name per_coll_agree_grades_v.d_grade_type_name%type;
24 l_business_group_id per_coll_agree_grades_v.business_group_id%type;
25 l_dynamic_insert_allowed per_coll_agree_grades_v.dynamic_insert_allowed%type;
26 l_id_flex_num fnd_id_flex_structures_vl.id_flex_num%type;
27 --
28 begin
29 --
30 hr_utility.set_location('Entering:'|| l_proc, 10);
31 --
32 if p_legislation_code='IT' then
33 --
34 Begin
35 If p_rec.tax_field1 is not null Then
36 update per_all_people_f
37 set per_information2 = p_rec.tax_field1
38 where person_id = p_person_id
39 and p_effective_date between effective_start_date and effective_end_date;
40 End If;
41 select id_flex_num into l_id_flex_num
42 from fnd_id_flex_structures_vl
43 where id_flex_structure_name = 'IT_CAGR'
44 and id_flex_code = 'CAGR';
45 EXCEPTION
46 when no_data_found then
47 null;
48 End;
49 hr_kflex_utility.ins_or_sel_keyflex_comb
50 (p_appl_short_name => 'PER'
51 ,p_flex_code => 'CAGR'
52 ,p_flex_num => l_id_flex_num
53 ,p_segment1 => p_rec.tax_field3
54 ,p_segment2 => p_rec.tax_field4
55 ,p_segment3 => p_rec.tax_field5
56 ,p_ccid => l_new_cagr_grade_def_id
57 ,p_concat_segments_out => l_concat_segments);
58 Begin
59 select cagr_grade_def_id into l_old_cagr_grade_def_id
60 from per_all_assignments_f
61 where person_id = p_person_id
62 and assignment_id = p_assignment_id
63 and cagr_id_flex_num = l_id_flex_num
64 and p_effective_date between effective_start_date and effective_end_date
65 and rownum = 1;
66 if l_old_cagr_grade_def_id <> l_new_cagr_grade_def_id then
67 if(p_rec.tax_field6 is not null) then ---bug 3878097
68 update per_all_assignments_f
69 set cagr_grade_def_id = l_new_cagr_grade_def_id,
70 collective_agreement_id = p_rec.tax_field6,
71 cagr_id_flex_num = l_id_flex_num
72 where person_id = p_person_id
73 and assignment_id = p_assignment_id
74 and cagr_id_flex_num = l_id_flex_num
75 and p_effective_date between effective_start_date and effective_end_date;
76 else ---bug 3878097
77 update per_all_assignments_f
78 set cagr_grade_def_id = null, -- added to not populate id_flex_num if no collective agreement id is found
79 collective_agreement_id = p_rec.tax_field6,
80 cagr_id_flex_num = null
81 where person_id = p_person_id
82 and assignment_id = p_assignment_id
83 and cagr_id_flex_num = l_id_flex_num
84 and p_effective_date between effective_start_date and effective_end_date;
85 end if; ---bug 3878097
86 end if;
87 EXCEPTION
88 when no_data_found then
89 if(p_rec.tax_field6 is not null) then ---bug 3878097
90 update per_all_assignments_f
91 set cagr_grade_def_id = l_new_cagr_grade_def_id,
92 collective_agreement_id = p_rec.tax_field6,
93 cagr_id_flex_num = l_id_flex_num
94 where person_id = p_person_id
95 and assignment_id = p_assignment_id
96 and p_effective_date between effective_start_date and effective_end_date;
97 else ---bug 3878097
98 update per_all_assignments_f
99 set cagr_grade_def_id = null,
100 collective_agreement_id = p_rec.tax_field6,
101 cagr_id_flex_num = null
102 where person_id = p_person_id
103 and assignment_id = p_assignment_id
104 and p_effective_date between effective_start_date and effective_end_date;
105 end if; ---bug 3878097
106 End;
107 End if;
108 --
109 hr_utility.set_location('Leaving:'|| l_proc, 1000);
110 --
111 end update_it_tax_data;
112 --
113 --
114
115 procedure it_tax_query
116 (p_rec in out nocopy per_qh_tax_query.taxrec
117 ,p_person_id in per_all_people_f.person_id%type
118 ,p_assignment_id in per_all_assignments_f.assignment_id%type
119 ,p_legislation_code in varchar2
120 ,p_effective_date in date
121 ) is
122 l_proc varchar2(72) := g_package||'it_tax_query';
123 l_cagr_segment1 per_cagr_grades_def.segment1%type;
124 l_cagr_segment2 per_cagr_grades_def.segment2%type;
125 l_cagr_segment3 per_cagr_grades_def.segment3%type;
126 l_new_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%type;
127 l_old_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%type;
128 l_id_flex_num fnd_id_flex_structures_vl.id_flex_num%type;
129 --
130 begin
131 --
132 if p_legislation_code='IT' then
133 hr_utility.set_location('Entering:'|| l_proc, 10);
134 --
135 Begin
136 select per_information2 into p_rec.tax_field1
137 from per_all_people_f
138 where person_id = p_person_id
139 and p_effective_date between effective_start_date and effective_end_date
140 and rownum = 1;
141 EXCEPTION
142 when no_data_found then
143 null;
144 End;
145 Begin
146 select id_flex_num into l_id_flex_num
147 from fnd_id_flex_structures_vl
148 where id_flex_structure_name = 'IT_CAGR'
149 and id_flex_code = 'CAGR';
150 EXCEPTION
151 when no_data_found then
152 null;
153 End;
154 Begin
155 select cagr_grade_def_id into l_old_cagr_grade_def_id
156 from per_all_assignments_f
157 where person_id = p_person_id
158 and assignment_id = p_assignment_id
159 and cagr_id_flex_num = l_id_flex_num
160 and p_effective_date between effective_start_date and effective_end_date
161 and rownum = 1;
162
163 select segment1, segment2, segment3 into l_cagr_segment1, l_cagr_segment2, l_cagr_segment3
164 from per_cagr_grades_def
165 where cagr_grade_def_id = l_old_cagr_grade_def_id
166 and id_flex_num = l_id_flex_num
167 and rownum = 1;
168 p_rec.tax_field3 := l_cagr_segment1;
169 p_rec.tax_field4 := l_cagr_segment2;
170 p_rec.tax_field5 := l_cagr_segment3;
171 EXCEPTION
172 when no_data_found then
173 null;
174 End;
175 end if;
176 --
177 hr_utility.set_location('Leaving:'|| l_proc, 1000);
178 --
179 end it_tax_query;
180
181 end per_it_qh_tax;