[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_MIGRATE_TIME_ANALYSIS
Source
1 PACKAGE BODY PAY_FR_MIGRATE_TIME_ANALYSIS as
2 /* $Header: pyfrmgta.pkb 120.0 2005/05/29 05:04:11 appldev noship $ */
3 Procedure Migrate(errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY NUMBER,
5 p_business_group_id IN NUMBER) Is
6
7 Cursor csr_get_all_asg IS
8 Select distinct asg.assignment_id
9 From per_all_assignments_f asg
10 Where asg.business_group_id = p_business_group_id
11 order by asg.assignment_id;
12
13 Cursor csr_get_asg_datetrk(c_assignment_id number)IS
14 Select asg.effective_start_date
15 ,asg.effective_end_date
16 ,asg.normal_hours
17 ,asg.frequency
18 ,scl.segment15 work_days
19 ,asg.person_id
20 ,full_name
21 From per_all_assignments_f asg
22 ,hr_soft_coding_keyflex scl
23 ,per_all_people_f per
24 Where asg.business_group_id = p_business_group_id
25 and asg.assignment_id = c_assignment_id
26 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
27 and asg.person_id = per.person_id
28 Order by asg.effective_start_date;
29
30 Cursor csr_get_all_contr(c_person_id number) is
31 Select distinct contract_id
32 from per_contracts_f
33 where business_group_id = p_business_group_id
34 and person_id = c_person_id ;
35
36 Cursor csr_all_contr_date(c_contract_id number,
37 c_previous_start_date date,
38 c_asg_start_date date) is
39 Select contract_id,
40 reference,
41 type,
42 status,
43 effective_start_date,
44 effective_end_date,
45 person_id,
46 object_version_number
47 from per_contracts_f
48 where business_group_id = p_business_group_id
49 and contract_id = c_contract_id
50 and effective_start_date
51 between c_previous_start_date and c_asg_start_date
52 order by effective_start_date desc;
53
54 -- Cursor for checking if rows exist in pay_patch_status
55 Cursor csr_migr_script_run is
56 Select count(*)
57 from pay_patch_status
58 where patch_number = p_business_group_id
59 and patch_name = 'WORKING TIME'
60 and legislation_code = 'FR';
61
62 l_count_asg Number;
63 l_count_contr_date Number;
64 l_flg_same_date VARCHAR2(3);
65 l_prev_frequency VARCHAR2(15);
66 l_prev_work_days NUMBER;
67 l_prev_num_hours NUMBER;
68 l_prev_end_date DATE;
69 l_prev_start_date DATE;
70 l_effective_start_date DATE;
71 l_effective_end_date DATE;
72 l_fixed_time VARCHAR2(3);
73 l_frequency VARCHAR2(10);
74 l_units VARCHAR2(10);
75 l_amount_time NUMBER;
76 l_ctr_ref VARCHAR2(80);
77 l_obj_version_number number;
78 l_script_run NUMBER;
79 l_disp_start_date VARCHAR2(30);
80 l_disp_end_date VARCHAR2(30);
81 l_disp_fixed_time VARCHAR2(10);
82 l_disp_units VARCHAR2(20);
83 l_disp_frequency VARCHAR2(30);
84 l_disp_canon_amt VARCHAR2(15);
85 l_head_full_name VARCHAR2(30);
86 l_head_ctr_ref VARCHAR2(30);
87 l_head_start_date VARCHAR2(30);
88 l_head_end_date VARCHAR2(30);
89 l_head_fixed_time VARCHAR2(30);
90 l_head_amount VARCHAR2(30);
91 l_head_units VARCHAR2(30);
92 l_head_frequency VARCHAR2(30);
93 l_head_wrk_days VARCHAR2(50);
94 l_head_asg_hrs VARCHAR2(50);
95 l_head_asg_freq VARCHAR2(50);
96 --
97 Begin
98 -- Initializing variables
99 l_fixed_time := 'N';
100 l_frequency := 'NA';
101 l_units := 'NA';
102 /*
103 Identify employees with no data entered in the Working Hours and Frequency fields on the Standard Conditions Tab.
104 Set the new Fixed Working Time field to "No" for these employees and produce a listing of the employees that this has been done to.
105 */
106 -- Check to see if the program has already been run for this business group
107 OPEN csr_migr_script_run;
108 FETCH csr_migr_script_run INTO l_script_run;
109 IF l_script_run = 0 THEN
110 -- Update the required values
111 -- Assign values to teh heading variables
112 l_head_full_name := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'FULL_NAME');
113 l_head_ctr_ref := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'CTR_REFERENCE');
114 l_head_start_date := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'CTR_START_DATE');
115 l_head_end_date := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'CTR_END_DATE');
116 l_head_fixed_time := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'FIXED_WORKING_TIME');
117 l_head_amount := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'FIXED_TIME_AMT');
118 l_head_units := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'FIXED_TIME_UNIT');
119 l_head_frequency := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'FIXED_TIME_FREQUENCY');
120 l_head_wrk_days := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'WORKING_TIME_YEAR');
121 l_head_asg_hrs := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'ASG_NORMAL_HR');
122 l_head_asg_freq := hr_general.decode_lookup('FR_TIME_MIGR_HEADINGS', 'ASG_FREQUENCY');
123 --
124 -- Print the heading for the columns
125 -- Presently using the following format to display data in the log file
126 -- Full Name Ctr reference Ctr_start_date Ctr_end_date Fixed_working_Time Amount Units Frequency Asg work days/yr Asg hours Asg freq
127 FND_FILE.PUT(FND_FILE.LOG, l_head_full_name||' '||l_head_ctr_ref||' '||l_head_start_date||' '||l_head_end_date||' '||l_head_fixed_time);
128 FND_FILE.PUT(FND_FILE.LOG,' '||l_head_amount||' '||l_head_units||' '||l_head_frequency||' '||l_head_wrk_days||' '||l_head_asg_hrs);
129 FND_FILE.PUT(FND_FILE.LOG,' '||l_head_asg_freq);
130 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
131 FND_FILE.PUT_LINE(FND_FILE.LOG, '=====================================================================================================');
132 --
133 -- Get all assignments and persons for the business group
134 -- and loop through them
135 hr_utility.set_location('Getting all assignments',22);
136 FOR asg_person_rec IN csr_get_all_asg LOOP
137 --
138 l_count_asg :=0;
139 -- get the date track changes for each assignment
140 hr_utility.set_location('getting date track data for assignments', 22);
141 FOR asg_mod_rec IN csr_get_asg_datetrk(asg_person_rec.assignment_id) LOOP
142 --
143 hr_utility.set_location('Name is :'||asg_mod_rec.full_name, 22);
144 --
145 l_count_asg := l_count_asg +1;
146 -- if it is the first row for the assignment
147 hr_utility.set_location('l_count_asg is'||l_count_asg, 22);
148 IF l_count_asg =1
149 -- or the changes are for time
150 OR (l_prev_work_days <> to_number(asg_mod_rec.work_days) OR
151 l_prev_frequency <> asg_mod_rec.frequency OR
152 l_prev_num_hours <> asg_mod_rec.normal_hours)THEN
153 -- Assign values to variables according to data
154 hr_utility.set_location('Assigning values to variables from asg data',22);
155 If asg_mod_rec.Work_days Is Null Then
156 If asg_mod_rec.Normal_hours Is Null then
157 l_fixed_time := 'N';
158 l_frequency := 'NA';
159 l_amount_time := null;
160 l_units := 'NA';
161 Elsif asg_mod_rec.Normal_hours Is not Null then
162 l_fixed_time := 'Y';
163 l_frequency := asg_mod_rec.frequency;
164 l_amount_time := asg_mod_rec.normal_hours;
165 l_units := 'HOUR';
166 If l_frequency not in('M', 'W') Then
167 l_amount_time := pay_fr_general.convert_hours
168 (p_effective_date => last_day(sysdate)
169 ,p_business_group_id => p_business_group_id
170 ,p_assignment_id => asg_person_rec.assignment_id
171 ,p_hours => asg_mod_rec.normal_hours
172 ,p_from_freq_code => l_frequency
173 ,p_to_freq_code => 'M');
174
175 l_frequency := 'M';
176 End If;
177 End If;
178 Else
179 l_fixed_time := 'Y';
180 l_frequency := 'Y';
181 l_units := 'DAY';
182 l_amount_time := to_number(asg_mod_rec.work_days);
183 End if;
184 -- For all the contracts of this person
185 hr_utility.set_location('getting all contracts for the person', 22);
186 -- convert the value to canonical format(NLS issue)
187 l_disp_canon_amt := fnd_number.number_to_canonical(l_amount_time);
188 FOR person_contr_rec IN csr_get_all_contr(asg_mod_rec.person_id) LOOP
189 l_count_contr_date :=0;
190 -- IF rows are present
191 -- Initialize the previous end date as start of time
192 l_prev_end_date := hr_general.start_of_time;
193 --
194 -- Loop thru' all rows
195 hr_utility.set_location('asg_mod_rec.effective_start_date is: '||asg_mod_rec.effective_start_date,22);
196 hr_utility.set_location('getting date tracked data for contracts', 22);
197 hr_utility.set_location('person_contr_rec.contract_id is: '||person_contr_rec.contract_id,22);
198 hr_utility.set_location('l_prev_end_date is: '||l_prev_end_date, 22);
199 --
200 FOR contr_date_rec IN csr_all_contr_date(person_contr_rec.contract_id,
201 l_prev_end_date,
202 asg_mod_rec.effective_start_date) LOOP
203 --
204 l_count_contr_date := l_count_contr_date +1;
205 l_obj_version_number := contr_date_rec.object_version_number;
206 --
207 hr_utility.set_location('l_count_contr_date is :'||l_count_contr_date,22);
208 hr_utility.set_location('contr_date_rec.effective_start_date is: '||contr_date_rec.effective_start_date,22);
209 --
210 -- Check for matching effective dates for the first contract row
211 IF l_count_contr_date = 1
212 OR asg_mod_rec.effective_start_date = contr_date_rec.effective_start_date
213 THEN
214 hr_utility.set_location('Updating in correction mode',22);
215 -- update in 'CORRECTION' mode
216 hr_contract_api.update_contract
217 (P_VALIDATE => false,
218 P_CONTRACT_ID => contr_date_rec.contract_id,
219 P_EFFECTIVE_START_DATE => l_effective_start_date,
220 P_EFFECTIVE_END_DATE => l_effective_end_date,
221 P_OBJECT_VERSION_NUMBER => l_obj_version_number,
222 P_PERSON_ID => contr_date_rec.person_id,
223 P_REFERENCE => contr_date_rec.reference,
224 P_TYPE => contr_date_rec.type,
225 P_STATUS => contr_date_rec.status,
226 P_CTR_INFORMATION10 => l_fixed_time,
227 P_CTR_INFORMATION11 => l_disp_canon_amt,
228 P_CTR_INFORMATION12 => l_units,
229 P_CTR_INFORMATION13 => l_frequency,
230 P_EFFECTIVE_DATE => contr_date_rec.effective_start_date,
231 P_DATETRACK_MODE => 'CORRECTION');
232 --
233 l_prev_end_date := l_effective_end_date;
234 l_ctr_ref := contr_date_rec.reference;
235 --
236 ELSE
237 -- insert a row in 'UPDATE' mode
238 hr_utility.set_location('Inserting a row in correction mode',22);
239 hr_contract_api.update_contract
240 (P_VALIDATE => false,
241 P_CONTRACT_ID => contr_date_rec.contract_id,
242 P_EFFECTIVE_START_DATE => l_effective_start_date,
243 P_EFFECTIVE_END_DATE => l_effective_end_date,
244 P_OBJECT_VERSION_NUMBER => l_obj_version_number,
245 P_PERSON_ID => contr_date_rec.person_id,
246 P_REFERENCE => contr_date_rec.reference,
247 P_TYPE => contr_date_rec.type,
248 P_STATUS => contr_date_rec.status,
249 P_CTR_INFORMATION10 => l_fixed_time,
250 P_CTR_INFORMATION11 => l_disp_canon_amt,
251 P_CTR_INFORMATION12 => l_units,
252 P_CTR_INFORMATION13 => l_frequency,
253 P_EFFECTIVE_DATE => asg_mod_rec.effective_start_date,
254 P_DATETRACK_MODE => 'UPDATE');
255 --
256 l_prev_end_date := l_effective_end_date;
257 l_ctr_ref := contr_date_rec.reference;
258 --
259 END IF;
260 hr_utility.set_location('End of if for contr-asg date match',22);
261 END LOOP;-- end loop for within date contracts
262 --
263 hr_utility.set_location('l_count_contr_date before the row check'||l_count_contr_date,22);
264 -- If rows are not present
265 IF l_count_contr_date =0 THEN
266 -- insert a row in 'UPDATE' mode
267 hr_utility.set_location('Before the before date loop start', 22);
268 -- With some values the same as the previous datetracked row
269 FOR contr_befdate_rec IN csr_all_contr_date(person_contr_rec.contract_id,
270 l_prev_start_date,
271 l_prev_end_date) LOOP
272 --
273 l_count_contr_date := l_count_contr_date +1;
274 l_obj_version_number := contr_befdate_rec.object_version_number;
275 hr_utility.set_location('Inserting a row where none exist', 22);
276 --
277 hr_contract_api.update_contract
278 (P_VALIDATE => false,
279 P_CONTRACT_ID => contr_befdate_rec.contract_id,
280 P_EFFECTIVE_START_DATE => l_effective_start_date,
281 P_EFFECTIVE_END_DATE => l_effective_end_date,
282 P_OBJECT_VERSION_NUMBER => l_obj_version_number,
283 P_PERSON_ID => contr_befdate_rec.person_id,
284 P_REFERENCE => contr_befdate_rec.reference,
285 P_TYPE => contr_befdate_rec.type,
286 P_STATUS => contr_befdate_rec.status,
287 P_CTR_INFORMATION10 => l_fixed_time,
288 P_CTR_INFORMATION11 => l_disp_canon_amt,
289 P_CTR_INFORMATION12 => l_units,
290 P_CTR_INFORMATION13 => l_frequency,
291 P_EFFECTIVE_DATE => asg_mod_rec.effective_start_date,
292 P_DATETRACK_MODE => 'UPDATE');
293 --
294 l_prev_end_date:= l_effective_end_date;
295 l_ctr_ref := contr_befdate_rec.reference;
296 --
297 IF l_count_contr_date =1 THEN
298 EXIT;
299 END IF;
300 --
301 END LOOP;
302 -- end loop for before date contracts
303 hr_utility.set_location('Exiting loop for before date contracts',22);
304 END IF;
305 -- End if for count rows
306 -- Assigning values for writing into log files
307 l_disp_start_date := fnd_date.date_to_displaydt(DATEVAL=> l_effective_start_date);
308 l_disp_end_date := fnd_date.date_to_displaydt(DATEVAL=> l_effective_end_date);
309 l_disp_fixed_time:= hr_general.decode_lookup('YES_NO',l_fixed_time);
310 l_disp_units:= hr_general.decode_lookup('FR_FIXED_TIME_UNITS',l_units);
311 l_disp_frequency:= hr_general.decode_lookup('FR_FIXED_TIME_FREQUENCY',l_frequency);
312
313 -- log the modified data
314 FND_FILE.PUT(FND_FILE.LOG, asg_mod_rec.full_name||' '||l_ctr_ref||' '||l_disp_start_date||' '||l_disp_end_date);
315 FND_FILE.PUT(FND_FILE.LOG, ' '||l_disp_fixed_time||' '||l_disp_canon_amt||' '||l_disp_units||' '||l_disp_frequency);
316 FND_FILE.PUT(FND_FILE.LOG, ' '||asg_mod_rec.work_days||' '||to_char(asg_mod_rec.normal_hours)||' '||asg_mod_rec.frequency);
317 FND_FILE.NEW_LINE(FND_FILE.LOG,2);
318 --
319 hr_utility.set_location('Written into log file, exiting loop for contracts',22);
320 END LOOP; --end of loop for contracts
321 END IF;-- end of if for asg changes
322 -- Assign values to variables
323 l_prev_frequency := asg_mod_rec.frequency;
324 l_prev_work_days := to_number(asg_mod_rec.work_days);
325 l_prev_num_hours := asg_mod_rec.normal_hours;
326 l_prev_start_date := asg_mod_rec.effective_start_date;
327 END LOOP; -- End loop for date tracked changes
328 END LOOP;-- End loop for assignments
329 --
330 -- Insert a row into pay_patch_status
331 INSERT INTO pay_patch_status
332 (id
333 ,patch_number
334 ,patch_name
335 ,phase
336 ,applied_date
337 ,legislation_code)
338 SELECT
339 pay_patch_status_s.nextval
340 ,p_business_group_id
341 ,'WORKING TIME'
342 ,Null
343 ,sysdate
344 ,'FR'
345 FROM dual;
346 --
347 END IF; -- end if for checking if migration script has already been run
348 CLOSE csr_migr_script_run;
349 --
350 Exception
351 When others then
352 hr_utility.set_location('Error:PAY_FR_MIGRATE_TIME_ANALYSIS.migrate',9999);
353 Raise;
354 End Migrate;
355 End PAY_FR_MIGRATE_TIME_ANALYSIS;