1 PACKAGE BODY pay_ae_iv_migrate_pkg AS
2 /* $Header: payaeivmigr.pkb 120.2.12000000.1 2007/02/16 08:45:49 abppradh noship $ */
3
4 ------------------------------------------------------------------------
5 ------------------------------------------------------------------------
6 -- Procedure update_iv_si_element
7 -- This procedure is used to migrate input value for SI Exception of SI Adjustment element
8 -- to Exception input value for SI element.
9 ------------------------------------------------------------------------
10 ------------------------------------------------------------------------
11
12 PROCEDURE update_iv_si_element
13 (errbuf OUT NOCOPY VARCHAR2
14 ,retcode OUT NOCOPY VARCHAR2
15 ,p_business_group_id IN NUMBER) IS
16
17
18 /* Check if the entry for BG exists in pay_patch_status table */
19 CURSOR csr_pay_patch_status (l_number NUMBER) IS
20 SELECT PATCH_NUMBER
21 FROM PAY_PATCH_STATUS
22 WHERE PATCH_NUMBER = L_NUMBER
23 AND PATCH_NAME = 'AE_IV_MIGRATION'
24 AND STATUS = 'C'
25 AND LEGISLATION_CODE = 'AE';
26
27 /* Get the element type id for SI and SI Adjustment element's id */
28 CURSOR csr_get_ele_id (l_ele_name VARCHAR2, l_date date) IS
29 SELECT element_type_id
30 FROM pay_element_types_f ele
31 WHERE ele.element_name = l_ele_name
32 AND ele.legislation_code = 'AE'
33 /*AND l_date between ele.effective_start_date and ele.effective_end_date*/;
34
35
36 /* Get input value id for input on SI and SI Adjustment element */
37 CURSOR csr_get_iv_id (l_element_type_id number, l_date date) IS
38 SELECT input_value_id
39 FROM pay_input_values_f piv
40 WHERE piv.element_type_id = l_element_type_id
41 AND piv.name = 'Exception Type'
42 AND piv.legislation_code = 'AE'
43 /*AND l_date between piv.effective_start_date and piv.effective_end_date*/;
44
45 /* Get input value id for Pay value for SI Element */
46 CURSOR csr_get_py_id (l_element_type_id number, l_date date) IS
47 SELECT input_value_id
48 FROM pay_input_values_f piv
49 WHERE piv.element_type_id = l_element_type_id
50 AND piv.name = 'Pay Value'
51 AND piv.legislation_code = 'AE'
52 /*AND l_date between piv.effective_start_date and piv.effective_end_date*/;
53
54 /* Get all the primary assignment ids for the BG */
55 CURSOR csr_get_asg_ids (l_bg_id NUMBER, l_date DATE) IS
56 SELECT assignment_id
57 FROM per_all_assignments_f paf
58 WHERE paf.business_group_id = l_bg_id
59 AND paf.primary_flag = 'Y'
60 /*AND l_date between paf.effective_start_date and paf.effective_end_date*/;
61
62 /* Get the element entries for SI and SI Adjustment element */
63 CURSOR csr_get_ele_entries (l_si_et NUMBER, l_asg_id NUMBER) IS
64 SELECT pee.element_entry_id , pee.effective_start_date,pee.effective_end_date
65 FROM pay_element_entries_f pee
66 WHERE pee.element_type_id = l_si_et
67 AND pee.assignment_id = l_asg_id
68 Order by effective_start_date asc;
69
70
71 /* Get all the columns for element entry for SI element */
72 CURSOR csr_get_si_ee_all (l_si_et NUMBER, l_asg_id NUMBER) IS
73 SELECT *
74 FROM pay_element_entries_f
75 WHERE element_type_id = l_si_et
76 AND assignment_id = l_asg_id
77 ORDER BY effective_start_date ASC;
78
79 rec_ele_entry csr_get_si_ee_all%ROWTYPE;
80
81 CURSOR csr_get_max_ovn (l_si_et NUMBER,l_asg_id NUMBER) IS
82 SELECT max(pee.object_version_number)
83 FROM pay_element_entries_f pee
84 WHERE pee.element_type_id = l_si_et
85 AND pee.assignment_id = l_asg_id;
86
87 /* Get the screen entry value for SI Adjustment Exception type value */
88 CURSOR csr_get_sia_entry_value (l_sia_exc_iv_id NUMBER, l_date DATE , l_sia_eeid NUMBER ) IS
89 SELECT peev.screen_entry_value
90 FROM pay_element_entry_values_f peev
91 WHERE peev.element_entry_id = l_sia_eeid
92 AND peev.input_value_id = l_sia_exc_iv_id
93 AND l_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
94
95 /* Get the screen entry value for SI Elements Exception type value for current date */
96 CURSOR csr_get_si_entry_value (l_si_exc_iv_id NUMBER, l_date DATE , l_si_eeid NUMBER ) IS
97 SELECT peev.screen_entry_value
98 FROM pay_element_entry_values_f peev
99 WHERE peev.element_entry_id = l_si_eeid
100 AND peev.input_value_id = l_si_exc_iv_id
101 AND l_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
102
103 /* Check if Exception type exists on SI element */
104 CURSOR csr_check_exc_on_si (L_SI_EE NUMBER ,l_si_exc_id NUMBER) IS
105 SELECT element_entry_value_id
106 FROM PAY_ELEMENT_ENTRY_VALUES_F
107 WHERE ELEMENT_ENTRY_ID = L_SI_EE
108 AND INPUT_VALUE_ID = l_si_exc_id;
109
110 l_status NUMBER;
111 l_effective_date DATE;
112 l_si_ele_id NUMBER;
113 l_sia_ele_id NUMBER;
114 l_si_iv_id NUMBER;
115 l_sia_iv_id NUMBER;
116 l_assignment_id NUMBER;
117 l_si_ee_id NUMBER;
118 l_sia_ee_id NUMBER;
119 l_sia_eev VARCHAR2(30);
120 l_si_eev VARCHAR2(30);
121 l_mg VARCHAR2(10);
122 l_bg_id NUMBER;
123 l_si_ovn NUMBER;
124 L_EFFECTIVE_START_DATE DATE;
125 L_EFFECTIVE_END_DATE DATE;
126 L_UPDATE_WARNING BOOLEAN;
127 l_max_ovn NUMBER;
128 l_t_sia_exc_val VARCHAR2(30);
129
130 L_T_SIA_EE_ID NUMBER;
131 L_T_SIA_START_DATE DATE;
132 L_T_SIA_END_DATE DATE;
133
134 l_si_ee_start_date date;
135 l_si_ee_end_date date;
136 l_found varchar2(10);
137 l_f_ee_id number;
138 L_F_EEV_ID number;
139 L_F_2_EEV_ID NUMBER;
140 l_si_py_id number;
141 L_F_EE2_ID NUMBER;
142
143 l_t_eff_date DATE;
144
145
146 L_DATETRACK_UPDATE_MODE varchar2(240);
147 L_SI_OBJECT_VERSION_NUMBER number;
148 L_OBJECT_VERSION_NUMBER number;
149 L_SI_EFFECTIVE_START_DATE date;
150 L_SI_EFFECTIVE_END_DATE date;
151 l_temp_exc_on_si NUMBER;
152
153 TYPE rec_sia_ele IS RECORD
154 (element_entry_id NUMBER
155 ,effective_start_date DATE
156 ,effective_end_date DATE
157 ,exception_value VARCHAR2(30));
158
159 TYPE t_rec_sia_ele IS TABLE OF rec_sia_ele INDEX BY BINARY_INTEGER;
160
161 BEGIN
162
163 l_status := null;
164
165 OPEN csr_pay_patch_status (p_business_group_id);
166 FETCH csr_pay_patch_status INTO l_status;
167 CLOSE csr_pay_patch_status;
168
169 /* IF l_status <> p_business_group_id THEN*/
170 IF l_status IS NULL THEN
171
172 l_bg_id := p_business_group_id;
173 l_effective_date := to_date('01-11-2007','DD-MM-YYYY');
174
175 INSERT INTO PAY_PATCH_STATUS
176 (ID,
177 PATCH_NUMBER,
178 PATCH_NAME,
179 STATUS,
180 APPLIED_DATE,
181 LEGISLATION_CODE)
182 SELECT
183 pay_patch_status_s.nextval,
184 p_business_group_id,
185 'AE_IV_MIGRATION',
186 'C',
187 sysdate,
188 'AE'
189 FROM DUAL;
190
191 l_bg_id := p_business_group_id;
192 l_effective_date := to_date('01-11-2007','DD-MM-YYYY');
193
194 OPEN csr_get_ele_id ('Social Insurance',l_effective_date);
195 FETCH csr_get_ele_id INTO l_si_ele_id;
196 CLOSE csr_get_ele_id;
197
198 OPEN csr_get_ele_id ('Social Insurance Adjustment', l_effective_date);
199 FETCH csr_get_ele_id INTO l_sia_ele_id;
200 CLOSE csr_get_ele_id;
201
202 OPEN csr_get_iv_id (l_si_ele_id,l_effective_date);
203 FETCH csr_get_iv_id INTO l_si_iv_id;
204 CLOSE csr_get_iv_id;
205
206 OPEN csr_get_py_id(l_si_ele_id,l_effective_date);
207 FETCH csr_get_py_id INTO l_si_py_id;
208 CLOSE csr_get_py_id;
209
210 OPEN csr_get_iv_id (l_sia_ele_id,l_effective_date);
211 FETCH csr_get_iv_id INTO l_sia_iv_id;
212 CLOSE csr_get_iv_id;
213
214 OPEN csr_get_asg_ids (p_business_group_id,l_effective_date);
215
216 LOOP
217 l_assignment_id := null;
218 l_si_ee_id := null;
219 l_sia_ee_id := null;
220 l_sia_eev := null;
221 l_si_eev := null;
222 l_si_ovn := null;
223 l_effective_start_date := null;
224 l_effective_end_date := null;
225 l_update_warning := null;
226 l_mg := null;
227 l_found := 'N';
228 l_f_ee_id := null;
229 L_F_EE2_ID := null;
230 L_SI_EFFECTIVE_START_DATE := null;
231 L_SI_EFFECTIVE_END_DATE := null;
232 l_t_eff_date := null;
233 l_temp_exc_on_si := NULL;
234
235 FETCH csr_get_asg_ids INTO l_assignment_id;
236 EXIT WHEN csr_get_asg_ids%NOTFOUND;
237
238 /* Get the element entry id for SI element */
239
240 /*OPEN csr_get_ele_entries (l_si_ele_id,l_assignment_id);
241 FETCH csr_get_ele_entries INTO l_si_ee_id, l_si_ee_start_date,l_si_ee_end_date;
242 CLOSE csr_get_ele_entries;*/
243
244 OPEN csr_get_si_ee_all (l_si_ele_id,l_assignment_id);
245 FETCH csr_get_si_ee_all into rec_ele_entry ;
246 CLOSE csr_get_si_ee_all;
247
248 l_si_ee_id := rec_ele_entry.element_entry_id;
249 l_si_ee_start_date := rec_ele_entry.effective_start_date;
250 l_si_ee_end_date := rec_ele_entry.effective_end_date;
251 L_SI_OBJECT_VERSION_NUMBER := rec_ele_entry.object_version_number;
252
253 /* Check if the SI element already has run results for Exception type */
254
255 OPEN csr_check_exc_on_si (l_si_ee_id,l_si_iv_id);
256 FETCH csr_check_exc_on_si INTO l_temp_exc_on_si;
257 CLOSE csr_check_exc_on_si;
258
259 IF l_temp_exc_on_si is null AND (l_si_ee_id IS NOT NULL AND
260 l_si_ee_start_date is not null AND l_si_ee_end_date is not null) then
261
262
263 /* Insert an entry value for Exception type on SI element */
264
265 INSERT INTO PAY_ELEMENT_ENTRY_VALUES_F
266 ( ELEMENT_ENTRY_VALUE_ID
267 , EFFECTIVE_START_DATE
268 , EFFECTIVE_END_DATE
269 , INPUT_VALUE_ID
270 , ELEMENT_ENTRY_ID
271 , SCREEN_ENTRY_VALUE
272 ) Values
273 (pay_element_entry_values_s.nextval
274 , l_si_ee_start_date
275 , l_si_ee_end_date
276 , l_si_iv_id
277 , l_si_ee_id
278 , '');
279
280 /* Get all the records for SI Adjustment element */
281
282 OPEN csr_get_ele_entries (l_sia_ele_id, l_assignment_id);
283
284 LOOP
285 l_t_sia_ee_id := null;
286 l_t_sia_start_date := null;
287 l_t_sia_end_date := null;
288 l_t_sia_exc_val := null;
289 L_F_EEV_ID := null;
290
291 FETCH csr_get_ele_entries INTO l_t_sia_ee_id,l_t_sia_start_date,l_t_sia_end_date ;
292 EXIT WHEN csr_get_ele_entries%NOTFOUND;
293
294
295
296 OPEN csr_get_sia_entry_value(l_sia_iv_id,l_t_sia_start_date,l_t_sia_ee_id);
297 FETCH csr_get_sia_entry_value INTO l_t_sia_exc_val;
298 CLOSE csr_get_sia_entry_value;
299
300
301 /* Initial value of OBJECT VERSION NUMBER */
302 L_OBJECT_VERSION_NUMBER := L_SI_OBJECT_VERSION_NUMBER;
303
304 l_t_eff_date := nvl(L_SI_EFFECTIVE_START_DATE,l_si_ee_start_date);
305
306 IF l_si_ee_id IS NOT NULL THEN
307
308 L_DATETRACK_UPDATE_MODE := 'UPDATE';
309
310 /* Update the SI element entry and entry values with exception type value */
311 HR_ENTRY_API.update_element_entry
312 (
313 -- Update Mode
314 p_dt_update_mode => L_DATETRACK_UPDATE_MODE,
315 -- Date on which change is taking place
316 p_session_date => l_t_sia_start_date,
320 p_element_entry_id => l_si_ee_id,
317 -- Check to see if the entry is being updated
318 p_check_for_update => 'N' ,
319 -- Element Entry Table
321 p_input_value_id1 => l_si_py_id,
322 p_input_value_id2 => l_si_iv_id,
323 p_entry_value1 => '',
324 p_entry_value2 => get_lookup_meaning('AE_SI_EXCEPTION_TYPES',l_t_sia_exc_val),
325 p_override_user_ent_chk => 'N'
326 )
327 ;
328
329 /* If SIA is end dated, then end date the SI exception record */
330
331 IF l_t_sia_end_date <> to_date('31-12-4712','DD-MM-YYYY') then
332
333 HR_ENTRY_API.update_element_entry
334 (
335 -- Update Mode
336 p_dt_update_mode => L_DATETRACK_UPDATE_MODE,
337 -- Date on which change is taking place
338 p_session_date => l_t_sia_end_date+1,
339 -- Check to see if the entry is being updated
340 p_check_for_update => 'N' ,
341 -- Element Entry Table
342 p_element_entry_id => l_si_ee_id,
343 p_input_value_id1 => l_si_py_id,
344 p_input_value_id2 => l_si_iv_id,
345 p_entry_value1 => '',
346 p_entry_value2 => '',
347 p_override_user_ent_chk => 'N'
348 )
349 ;
350 END IF;
351
352 END IF;
353
354
355 END LOOP;
356 CLOSE csr_get_ele_entries;
357 END IF;
358
359
360 END LOOP;
361 CLOSE csr_get_asg_ids;
362 ELSE
363 hr_utility.set_message(800, 'HR_377443_AE_IV_MIG_RUN');
364 hr_utility.raise_error;
365 END IF;
366
367
368 END update_iv_si_element;
369
370 ------------------------------------------------------------------------
371 -----------------------------------------------------------------
372 FUNCTION get_lookup_meaning
373 (p_lookup_type varchar2
374 ,p_lookup_code varchar2)
375 RETURN VARCHAR2 IS
376 CURSOR csr_lookup IS
377 select meaning
378 from hr_lookups
379 where lookup_type = p_lookup_type
380 and lookup_code = p_lookup_code;
381 l_meaning hr_lookups.meaning%type;
382 BEGIN
383 OPEN csr_lookup;
384 FETCH csr_lookup INTO l_Meaning;
385 CLOSE csr_lookup;
386 RETURN l_meaning;
387 END get_lookup_meaning;
388 -----------------------------------------------------------------
389 ------------------------------------------------------------------------
390
391 END pay_ae_iv_migrate_pkg;
392
[Home] [Help]