[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_COMP_USAGE_INTERNAL
Source
1 Package Body pay_retro_comp_usage_internal as
2 /* $Header: pyrcubsi.pkb 120.1 2005/10/04 23:03 pgongada noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := 'pay_retro_comp_usage_internal.';
7 --
8 --
9 --private procedures
10 --
11 --Created for Bug#4075607
12 procedure get_ele_typ_det (p_effective_date in date
13 ,p_element_type_id in number
14 ,x_legislation_code out nocopy varchar2
15 ,x_classification_id out nocopy number
16 ,x_business_group_id out nocopy number);
17
18 --public procedures
19
20 -- ----------------------------------------------------------------------------
21 -- |----------------------< populate_retro_comp_usages >----------------------|
22 -- ----------------------------------------------------------------------------
23 --
24 procedure populate_retro_comp_usages
25 (p_effective_date in date
26 ,p_element_type_id in number
27 ) is
28 --
29 -- Declare cursors and local variables
30 --
31 l_rcu_id number;
32 l_rcu_ovn number;
33 l_esu_id number;
34 l_esu_ovn number;
35
36 cursor csr_base_rcu
37 is
38 --
39 -- Business_group_id and legislation_code should be derived from
40 -- the element type.
41 -- Return no rows if a retro component usage already exists
42 -- for that element type.
43 --
44 select
45 rcu1.retro_component_usage_id
46 ,rcu1.retro_component_id
47 ,rcu1.creator_id
48 ,rcu1.creator_type
49 ,rcu1.default_component
50 ,rcu1.reprocess_type
51 ,etp.business_group_id
52 ,etp.legislation_code
53 from
54 pay_element_types_f etp
55 ,pay_retro_component_usages rcu1
56 where
57 etp.element_type_id = p_element_type_id
58 and p_effective_date between etp.effective_start_date
59 and etp.effective_end_date
60 and rcu1.creator_type = 'EC'
61 and rcu1.creator_id = etp.classification_id
62 and not exists
63 (select null from pay_retro_component_usages rcu2
64 where rcu2.creator_id = etp.element_type_id
65 and rcu2.creator_type = 'ET')
66 ;
67
68 cursor csr_base_esu
69 (p_retro_comp_usage_id in number
70 )
71 is
72 select
73 element_span_usage_id
74 ,time_span_id
75 ,retro_component_usage_id
76 ,adjustment_type
77 ,retro_element_type_id
78 ,business_group_id
79 ,legislation_code
80 from
81 pay_element_span_usages
82 where
83 retro_component_usage_id = p_retro_comp_usage_id
84 ;
85
86 l_proc varchar2(72) := g_package||'populate_retro_comp_usages';
87 begin
88 hr_utility.set_location('Entering:'|| l_proc, 10);
89
90 --
91 -- Validation in addition to Row Handlers
92 --
93
94 --
95 -- Process Logic
96 --
97 -- 1) Populate retro component usages with the rows defined for the
98 -- classification if none is created for the element type.
99 -- Business_group_id and legislation_code should be inherited
100 -- from the element type.
101 -- 2) Populate the child element span usages accordingly.
102 -- Business_group_id and legislation_code should be inherited
103 -- from the element type as well.
104
105 for l_rcu_rec in csr_base_rcu loop
106
107 hr_utility.set_location(l_proc, 20);
108
109 l_rcu_id := null;
110 l_rcu_ovn := null;
111
112 --
113 -- Create the retro component usage for the element type
114 --
115 pay_rcu_ins.ins
116 (p_effective_date => p_effective_date
117 ,p_retro_component_id => l_rcu_rec.retro_component_id
118 ,p_creator_id => p_element_type_id
119 ,p_creator_type => 'ET'
120 ,p_default_component => l_rcu_rec.default_component
121 ,p_reprocess_type => l_rcu_rec.reprocess_type
122 ,p_business_group_id => l_rcu_rec.business_group_id
123 ,p_legislation_code => l_rcu_rec.legislation_code
124 ,p_retro_component_usage_id => l_rcu_id
125 ,p_object_version_number => l_rcu_ovn
126 );
127
128 --
129 -- Create the Element Span Usages
130 --
131 for l_esu_rec in csr_base_esu(l_rcu_rec.retro_component_usage_id) loop
132
133 hr_utility.set_location(l_proc, 30);
134
135 l_esu_id := null;
136 l_esu_ovn := null;
137
138 pay_esu_ins.ins
139 (p_effective_date => p_effective_date
140 ,p_time_span_id => l_esu_rec.time_span_id
141 ,p_retro_component_usage_id => l_rcu_id
142 ,p_retro_element_type_id => l_esu_rec.retro_element_type_id
143 ,p_adjustment_type => l_esu_rec.adjustment_type
144 ,p_business_group_id => l_rcu_rec.business_group_id
145 ,p_legislation_code => l_rcu_rec.legislation_code
146 ,p_element_span_usage_id => l_esu_id
147 ,p_object_version_number => l_esu_ovn
148 );
149
150 end loop;
151
152 end loop;
153
154 hr_utility.set_location(l_proc, 40);
155
156 --
157 -- Set all output arguments
158 --
159
160 hr_utility.set_location('Leaving:'||l_proc, 50);
161
162 end populate_retro_comp_usages;
163
164
165 -- ----------------------------------------------------------------------------
166 -- |---------------------< delete_child_retro_comp_usages >--------------------|
167 -- ----------------------------------------------------------------------------
168 --
169 procedure delete_child_retro_comp_usages
170 (p_effective_date in date
171 ,p_element_type_id in number
172 ) is
173 --
174 -- Declare cursors and local variables
175 --
176 --Bug#4075607. Takes in leg code and business group id as parameters.
177
178 cursor csr_rcu(p_legislation_code varchar2, p_business_group_id number)
179 is
180 select
181 retro_component_usage_id
182 ,object_version_number
183 from
184 pay_retro_component_usages
185 where
186 creator_id = p_element_type_id
187 and nvl(legislation_code, -1) = p_legislation_code
188 and nvl(business_group_id , -1) = p_business_group_id
189 and creator_type = 'ET'
190 ;
191
192 cursor csr_esu
193 (p_retro_component_usage_id in number
194 )
195 is
196 select
197 element_span_usage_id
198 ,object_version_number
199 from
200 pay_element_span_usages
201 where
202 retro_component_usage_id = p_retro_component_usage_id
203 ;
204
205 l_proc varchar2(72) := g_package||'delete_child_retro_comp_usages';
206
207 --Bug#4075607
208 --local variables to hold element type details..
209 l_classification_id pay_element_types_f.classification_id%type;
210 l_business_group_id pay_element_types_f.business_group_id%type;
211 l_legislation_code pay_element_types_f.legislation_code%type;
212 --
213 begin
214 hr_utility.set_location('Entering:'|| l_proc, 10);
215 --
216 -- Validation in addition to Row Handlers
217 --
218
219 --
220 -- Process Logic
221 --
222 --
223 --get element type details
224 --Bug#4075607
225 get_ele_typ_det (p_effective_date => p_effective_date
226 ,p_element_type_id => p_element_type_id
227 ,x_legislation_code => l_legislation_code
228 ,x_classification_id => l_classification_id
229 ,x_business_group_id => l_business_group_id);
230
231 --Bug#4075607. Takes in leg code and business group id as parameters.
232 for l_rcu_rec in csr_rcu(l_legislation_code, l_business_group_id) loop
233
234 --
235 -- Delete Element Span Usages
236 --
237 for l_esu_rec in csr_esu(l_rcu_rec.retro_component_usage_id) loop
238
239 hr_utility.set_location(l_proc, 15);
240 pay_esu_del.del
241 (p_element_span_usage_id => l_esu_rec.element_span_usage_id
242 ,p_object_version_number => l_esu_rec.object_version_number
243 );
244
245 end loop;
246
247 --
248 -- Delete Retro Component Usages
249 --
250 hr_utility.set_location(l_proc, 20);
251
252 pay_rcu_del.del
253 (p_retro_component_usage_id => l_rcu_rec.retro_component_usage_id
254 ,p_object_version_number => l_rcu_rec.object_version_number
255 );
256
257 end loop;
258
259 hr_utility.set_location('Leaving:'||l_proc, 40);
260
261 end delete_child_retro_comp_usages;
262
263 --
264 -- Bug#4075607
265 -- ----------------------------------------------------------------------------
266 -- |----------------------< get_ele_typ_det >----------------------------------|
267 -- ----------------------------------------------------------------------------
268 --
269 procedure get_ele_typ_det (p_effective_date in date
270 ,p_element_type_id in number
271 ,x_legislation_code out nocopy varchar2
272 ,x_classification_id out nocopy number
273 ,x_business_group_id out nocopy number)
274 IS
275 --Bug#4075607
276 --In order to add business group id and legislation code to unique constraint
277 --for pay_element_span_usages and pay_retro_component_usages tables, we need
278 --to check for the both these column values for the above tables to match
279 --with the pay_element_types table values.
280 --
281 Begin
282 --
283 --Fetch element type details......
284 Select nvl(etp.business_group_id, -1)
285 ,nvl(etp.legislation_code, -1)
286 ,etp.classification_id
287 Into
288 x_business_group_id
289 ,x_legislation_code
290 ,x_classification_id
291 From pay_element_types_f etp
292 Where etp.element_type_id = p_element_type_id
293 And p_effective_date between etp.effective_start_date and etp.effective_end_date ;
294
295 End get_ele_typ_det;
296 --
297 --
298 end pay_retro_comp_usage_internal;