[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_DADS_EMP_PKG
Source
1 PACKAGE BODY PAY_FR_DADS_EMP_PKG as
2 /* $Header: pyfrdems.pkb 120.1 2006/03/16 10:29 aparkes noship $ */
3 g_package varchar2(50); -- Global package name
4 g_param_issuing_estab_id per_all_assignments_f.establishment_id%type;
5 g_param_company_id hr_organization_information.organization_id%type;
6 g_param_estab_id hr_organization_information.organization_id%type;
7 g_effective_date Date;
8 g_assign_action_id pay_payroll_actions.payroll_action_id%type;
9 g_param_business_group_id hr_organization_information.organization_id%type;
10 g_param_reference VARCHAR2(100);
11 g_param_start_date Date;
12
13 ----------------------------------
14 -- --
15 --Private procedures
16 ----------------------------------
17 --
18 Function fr_rolling_balance_pro (p_assignment_id in number,
19 p_balance_name in varchar2,
20 p_balance_start_date in date,
21 p_balance_end_date in date) return number;
22
23 PROCEDURE archive_data(p_rubric_name VARCHAR2,
24 p_message_type VARCHAR2,
25 p_id NUMBER,
26 p_lookup_type VARCHAR2,
27 p_file_value VARCHAR2,
28 p_message_text VARCHAR2,
29 p_id2 VARCHAR2,
30 p_rubric_type VARCHAR2);
31 ----------------
32 --Public Procedures
33 ----------------
34
35 procedure execS30_G01_00(p_assact_id IN Number
36 ,p_issuing_estab_id IN Number
37 ,p_org_id IN Number
38 ,p_estab_id IN Number
39 ,p_business_Group_id IN Number
40 ,p_reference IN Varchar2
41 ,p_start_date IN Date
42 ,p_effective_date IN Date)
43 is
44 --
45 Cursor cur_emp_data(p_org_id varchar2) is
46 Select Distinct NATIONAL_IDENTIFIER SS_Number --S30.G01.00.001 SS Number --expections report also needs it
47 , DECODE(SEX, 'F', DECODE(NVL(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), '-1'), '-1', LAST_NAME,NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME))
48 , 'M', NVL(PREVIOUS_LAST_NAME,LAST_NAME), LAST_NAME) Birth_Name--S30.G01.00.002 Birth Name
49 , ppf.first_name first_name
50 , ppf.MIDDLE_NAMES middle_names --Christian_Names--S30.G01.00.003 Christian Names
51 , ppf.KNOWN_AS preferred_name --First Name generally used--S30.G01.00.005
52 , DECODE(SEX, 'F', DECODE(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), LAST_NAME, NULL,LAST_NAME)
53 , 'M', DECODE( PREVIOUS_LAST_NAME, LAST_NAME, NULL, LAST_NAME)) Name--Name S30.G01.00.004/Nickname S30.G01.00.006
54 , ppf.title Title, ppf.sex sex, ppf.Marital_status marital_status--Title S30.G01.00.007
55 , ppf.full_name full_name --used in exception report
56 , pa.address_line2 Complement --Address (complement) S30.G01.00.008.001
57 , pa.address_line1 Street --Nature and name of the street S30.G01.00.008.006
58 , pa.region_2 INSEE_code -- INSEE Code of the town S30.G01.00.008.007
59 , pa.region_3 Small_Town -- Name of the town --S30.G01.00.008.009
60 , pa.postal_code Postal_code --Zip Code S30.G01.00.008.010
61 , upper(pa.town_or_city) Town --Town S30.G01.00.008.012
62 , null Country_Code -- Country Code -- null for the timebeing
63 , ft_tl.territory_short_name Country_name --Country Name S30.G01.00.008.014
64 , Null tot_address --Total Address Code --to be left void for the time being
65 , to_char(ppf.date_of_birth,'DDMMYYYY') Date_of_birth --Date of Birth S30.G01.00.009
66 , ppf.Town_of_birth Town_of_birth --Town of Birth S30.G01.00.010
67 , ppf.region_of_birth region_of_birth --Birth_code --Region_of_birth --Birth Department Code S30.G01.00.011 region_of_birth
68 , ft1_tl.territory_short_name country_of_birth_name --Town of Birth S30.G01.00.010 need this for validation and expections report
69 , ppf.country_of_birth country_of_birth --Town of Birth S30.G01.00.010 need this for validation and expections report
70 , ppf.nationality Nationality -- Nationality S30.G01.00.013.013
71 , ppf.person_id person_id-- used to send as a parameter to s41 structure.
72 , paf.location_id location_id--used to send as a parameter in s41 Structure
73 , paf.assignment_id assignment_id -- assignment_id , p_id
74 , ppf.employee_number employee_number -- this will be the id2 column
75 From per_all_people_f ppf
76 , per_all_assignments_f paf
77 , pay_assignment_actions paa
78 , per_addresses pa
79 , fnd_territories ft
80 , fnd_territories_tl ft_tl
81 , fnd_territories ft1
82 , fnd_territories_tl ft1_tl
83 , hr_organization_information org_est
84 , per_periods_of_service pps
85 Where paa.assignment_action_id = g_assign_action_id
86 And paf.assignment_id = paa.assignment_id
87 And paf.establishment_id = org_est.organization_id
88 And org_est.org_information1 = p_org_id
89 And paf.person_id = ppf.person_id
90 And pps.person_id = paf.person_id
91 And pa.person_id(+) = ppf.person_id
92 And pa.style(+) = 'FR'
93 And ((g_effective_date between paf.effective_start_date and paf.effective_end_date)
94 Or (pps.actual_termination_date
95 between paf.effective_start_date and paf.effective_end_date))
96 And nvl(ft.territory_code, 'FR') = nvl(pa.country, 'FR')
97 And ppf.country_of_birth = ft1.territory_code(+)
98
99 And ft_tl.territory_code (+) = ft.territory_code
100 And ft_tl.language (+) = userenv('LANG')
101
102 And ft1_tl.territory_code (+) = ft1.territory_code
103 And ft1_tl.language (+) = userenv('LANG');
104 --
105 l_emp_rec cur_emp_data%rowtype;
106 l_name VARCHAR2(250);
107 l_person_id per_all_people_f.person_id%type;
108 l_address_id per_addresses.address_id%type;
109 l_location_id per_all_assignments_f.location_id%type;
110
111 p_id Number;
112 l_id2 per_all_people_f.employee_number%type;
113 l_pactid pay_payroll_actions.payroll_action_id%type;
114
115 -- Local Variable Declaration for Exceptions
116 l_value fnd_new_messages.message_text%type;
117 l_error_type hr_lookups.meaning%type;
118 l_error hr_lookups.meaning%type;
119 l_warning hr_lookups.meaning%type;
120 l_ss_number per_all_people_f.NATIONAL_IDENTIFIER%type;
121 l_mandatory varchar2(1);
122 l_conditional varchar2(1);
123 l_optional varchar2(1);
124 l_nationality fnd_lookup_values.meaning%type;
125 --checks whether any employee is there in a establishment
126 l_emp_found boolean;
127
128 Begin
129 g_package := ' pay_fr_dads_emp_pkg.'; -- Global package name
130 -- Initializing the local variables
131 l_mandatory := 'M';
132 l_conditional := 'C';
133 l_optional := 'O';
134 --
135 -- Getting the error messages
136 -- hr_utility.trace_on(null, 'SA_DADS');
137 hr_utility.set_location('S30 Entering:'||p_org_id,1);
138
139 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
140 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
141
142 p_id := p_org_id;
143 --Initialize the globals
144 --
145 g_param_issuing_estab_id := p_issuing_estab_id;
146 g_param_company_id := p_org_id;
147 g_param_estab_id := p_estab_id;
148 g_param_business_group_id := p_business_Group_id;
149 g_param_reference := p_reference;
150 g_param_start_date := p_start_date;
151 g_effective_date := p_effective_date;
152
153 hr_utility.set_location('S30 g_param_issuing_estab_id:'||g_param_issuing_estab_id,1);
154 hr_utility.set_location('S30 g_param_company_id:'||g_param_company_id,1);
155
156 g_assign_action_id := p_assact_id;
157 l_emp_found := FALSE;
158
159 For l_emp_rec In cur_emp_data(p_org_id)
160 Loop
161 Exit when cur_emp_data%notfound;
162 l_id2 := l_emp_rec.employee_number;
163 l_emp_found := TRUE;
164
165 l_ss_number := l_emp_rec.ss_number;
166 IF l_emp_rec.ss_number is null THEN
167 l_error_type := l_error;
168 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
169 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
170 ('FR_DADS_RUBRICS','S30.G01.00.001'), null);
171 ELSIF ( substr(l_emp_rec.ss_number, 1, 1) = '7' or substr(l_emp_rec.ss_number, 1, 1) = '8') THEN
172 l_error_type := l_error;
173 l_value := pay_fr_general.get_payroll_message
174 ('PAY_75181_SS_TEMP','VALUE1:'||l_emp_rec.full_name, 'VALUE2:'|| l_emp_rec.person_id
175 , null);
176 /* This has to be finalised by Heather */
177 ELSIF instr (upper(l_emp_rec.ss_number), 'X') > 0 THEN
178 l_ss_number := replace(l_emp_rec.ss_number, 'X', '9');
179 l_error_type := l_warning;
180 l_value := pay_fr_general.get_payroll_message('PAY_75189_SS_INCMP',
181 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'|| l_emp_rec.ss_number, null);
182 ELSE
183 l_error_type := null;
184 l_value := null;
185 END IF;
186
187 -- SS number
188 archive_data(p_rubric_name => 'S30.G01.00.001'
189 ,p_message_type => l_error_type
190 ,p_id => p_id
191 ,p_lookup_type => Null
192 ,p_file_value => l_ss_number
193 ,p_message_text => l_value
194 ,p_id2 => l_id2
195 ,p_rubric_type => l_mandatory);
196
197 l_person_id := l_emp_rec.person_id;
198 hr_utility.set_location('S30 Person_id:'||l_person_id,112);
199 l_location_id := l_emp_rec.location_id;
200
201 -- Birth name
202 IF l_emp_rec.birth_name is null THEN
203 l_error_type := l_error;
204 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
205 'VALUE1:'||hr_general.decode_lookup
206 ('FR_DADS_RUBRICS','S30.G01.00.002'), null, null);
207 ELSE
208 l_error_type := null;
209 l_value := null;
210 hr_utility.set_location('S30 l_value:'||l_value,113);
211 END IF;
212 archive_data( p_rubric_name => 'S30.G01.00.002'
213 ,p_message_type => l_error_type
214 ,p_id => p_id
215 ,p_lookup_type => Null
216 ,p_file_value => l_emp_rec.birth_name
217 ,p_message_text => l_value
218 ,p_id2 => l_id2
219 ,p_rubric_type => l_mandatory);
220
221 l_name := l_emp_rec.first_name||' '||l_emp_rec.middle_names;
222
223 -- Given Christian names
224 IF ((l_emp_rec.first_name is null)
225 and (l_emp_rec.middle_names is null)) THEN
226 l_error_type := l_error;
227 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA','VALUE1:'
228 ||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
229 ('FR_DADS_RUBRICS','S30.G01.00.003'), null);
230 ELSE
231 l_error_type := null;
232 l_value := null;
233 END IF;
234 archive_data( p_rubric_name => 'S30.G01.00.003'
235 ,p_message_type => l_error_type
236 ,p_id => p_id
237 ,p_lookup_type => Null
238 ,p_file_value => l_name
239 ,p_message_text => l_value
240 ,p_id2 => l_id2
241 ,p_rubric_type => l_mandatory);
242
243 -- Name
244 archive_data( p_rubric_name => 'S30.G01.00.004'
245 ,p_message_type => Null
246 ,p_id => p_id
247 ,p_lookup_type => Null
248 ,p_file_value => l_emp_rec.name
249 ,p_message_text => Null
250 ,p_id2 => l_id2
251 ,p_rubric_type => l_conditional);
252
253
254 If ((l_emp_rec.preferred_name <> l_emp_rec.first_name)
255 And (l_emp_rec.preferred_name Is Not Null)) Then
256 If (l_emp_rec.preferred_name = l_emp_rec.middle_names) Then
257 -- First name generally used
258 archive_data(
259 p_rubric_name => 'S30.G01.00.005'
260 ,p_message_type => Null
261 ,p_id => p_id
262 ,p_lookup_type => Null
263 ,p_file_value => l_emp_rec.preferred_name
264 ,p_message_text => Null
265 ,p_id2 => l_id2
266 ,p_rubric_type => l_conditional);
267 Else
268 -- Nickname
269 archive_data(
270 p_rubric_name => 'S30.G01.00.006'
271 ,p_message_type => Null
272 ,p_id => p_id
273 ,p_lookup_type => Null
274 ,p_file_value => l_emp_rec.preferred_name
275 ,p_message_text => Null
276 ,p_id2 => l_id2
277 ,p_rubric_type => l_conditional);
278 End If;
279 End If;
280
281 If ((l_emp_rec.Title Is not Null)
282 And (l_emp_rec.Title = 'MISS' OR l_emp_rec.Title = 'MR.' OR l_emp_rec.Title = 'MRS.')) Then
283 -- Title
284 archive_data(
285 p_rubric_name => 'S30.G01.00.007'
286 ,p_message_type => Null
287 ,p_id => p_id
288 ,p_lookup_type => 'TITLE'
289 ,p_file_value => l_emp_rec.Title
290 ,p_message_text => Null
291 ,p_id2 => l_id2
292 ,p_rubric_type => l_mandatory);
293 /* First checks whether the title is not null and then the other checks are made.*/
294 Elsif l_emp_rec.Title Is not Null THEN
295 If (l_emp_rec.sex = 'M') Then
296 -- Title
297 archive_data(
298 p_rubric_name => 'S30.G01.00.007'
299 ,p_message_type => Null
300 ,p_id => p_id
301 ,p_lookup_type => 'TITLE'
302 ,p_file_value => 'MR.'
303 ,p_message_text => Null
304 ,p_id2 => l_id2
305 ,p_rubric_type => l_mandatory);
306
307 Elsif (l_emp_rec.marital_status = 'S') Then
308 -- Title
309 archive_data(
310 p_rubric_name => 'S30.G01.00.007'
311 ,p_message_type => Null
312 ,p_id => p_id
313 ,p_lookup_type => 'TITLE'
314 ,p_file_value => 'MISS'
315 ,p_message_text => Null
316 ,p_id2 => l_id2
317 ,p_rubric_type => l_mandatory);
318 Else
319 -- Title
320 archive_data(
321 p_rubric_name => 'S30.G01.00.007'
322 ,p_message_type => Null
323 ,p_id => p_id
324 ,p_lookup_type => 'TITLE'
325 ,p_file_value => 'MRS.'
326 ,p_message_text => Null
327 ,p_id2 => l_id2
328 ,p_rubric_type => l_mandatory);
329 End if;
330 /* If title is null, then error message is archived */
331 ELSIF l_emp_rec.title is null THEN
332 l_error_type := l_error;
333 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
334 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S30.G01.00.007'), null, null);
335 archive_data( p_rubric_name => 'S30.G01.00.007'
336 ,p_message_type => l_error_type
337 ,p_id => p_id
338 ,p_lookup_type => null
339 ,p_file_value => null
340 ,p_message_text => l_value
341 ,p_id2 => l_id2
342 ,p_rubric_type => l_mandatory);
343 End if;
344
345 -- Employee Address Complement
346 archive_data(p_rubric_name => 'S30.G01.00.008.001'
347 ,p_message_type => Null
348 ,p_id => p_id
349 ,p_lookup_type => NULL
350 ,p_file_value => l_emp_rec.Complement
351 ,p_message_text => Null
352 ,p_id2 => l_id2
353 ,p_rubric_type => l_optional);
354
355 -- Employee Address street
356 IF l_emp_rec.street is null THEN
357 l_error_type := l_error;
358 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
359 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
360 ('FR_DADS_RUBRICS','S30.G01.00.008.006'), null);
361 ELSE
362 l_error_type := Null;
363 l_value := Null;
364 END IF;
365 archive_data(p_rubric_name => 'S30.G01.00.008.006'
366 ,p_message_type => l_error_type
367 ,p_id => p_id
368 ,p_lookup_type => NULL
369 ,p_file_value => l_emp_rec.Street
370 ,p_message_text => l_value
371 ,p_id2 => l_id2
372 ,p_rubric_type => l_conditional);
373
374 archive_data(p_rubric_name => 'S30.G01.00.008.007'
375 ,p_message_type => Null
376 ,p_id => p_id
377 ,p_lookup_type => NULL
378 ,p_file_value => l_emp_rec.INSEE_code
379 ,p_message_text => Null
380 ,p_id2 => l_id2
381 ,p_rubric_type => l_conditional);
382
383 -- Town
384 IF upper(l_emp_rec.small_town ) = upper(l_emp_rec.town) THEN
385 l_error_type := l_error;
386 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
387 null, null, null);
388 ELSE
389 l_error_type := Null;
390 l_value := Null;
391 END IF;
392
393
394 archive_data(p_rubric_name => 'S30.G01.00.008.009'
395 ,p_message_type => l_error_type
396 ,p_id => p_id
397 ,p_lookup_type => NULL
398 ,p_file_value => l_emp_rec.small_town
399 ,p_message_text => l_value
400 ,p_id2 => l_id2
401 ,p_rubric_type => l_conditional);
402
403 -- Postal Code
404 IF l_emp_rec.postal_code is null THEN
405 l_error_type := l_error;
406 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
407 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
408 ('FR_DADS_RUBRICS','S30.G01.00.008.010'), null);
409 ELSE
410 l_error_type := Null;
411 l_value := Null;
412 END IF;
413 archive_data(p_rubric_name => 'S30.G01.00.008.010'
414 ,p_message_type => l_error_type
415 ,p_id => p_id
416 ,p_lookup_type => NULL
417 ,p_file_value => l_emp_rec.postal_code
418 ,p_message_text => l_value
419 ,p_id2 => l_id2
420 ,p_rubric_type => l_mandatory);
421
422 -- City
423 IF l_emp_rec.town is null THEN
424 l_error_type := l_error;
425 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
426 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
427 ('FR_DADS_RUBRICS','S30.G01.00.008.012'), null);
428 ELSIF l_emp_rec.town <> upper(l_emp_rec.town) THEN
429 l_error_type := l_error;
430 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
431 null, null, null);
432 ELSE
433 l_error_type := Null;
434 l_value := Null;
435 END IF;
436 archive_data(
437 p_rubric_name => 'S30.G01.00.008.012'
438 ,p_message_type => l_error_type
439 ,p_id => p_id
440 ,p_lookup_type => NULL
441 ,p_file_value => l_emp_rec.town
442 ,p_message_text => l_value
443 ,p_id2 => l_id2
444 ,p_rubric_type => l_mandatory);
445
446 -- Country Code
447 IF l_emp_rec.country_code = 'FR'
448 AND l_emp_rec.country_code is not null THEN
449 l_error_type := l_error;
450 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
451 'VALUE1: '||hr_general.decode_lookup
452 ('FR_DADS_RUBRICS','S30.G01.00.008.013'), null, null);
453 ELSE
454 l_error_type := Null;
455 l_value := Null;
456 END IF;
457
458 archive_data(p_rubric_name => 'S30.G01.00.008.013'
459 ,p_message_type => l_error_type
460 ,p_id => p_id
461 ,p_lookup_type => NULL
462 ,p_file_value => l_emp_rec.country_code
463 ,p_message_text => l_value
464 ,p_id2 => l_id2
465 ,p_rubric_type => l_conditional);
466
467 -- Country Name
468 IF l_emp_rec.country_name is not null and l_emp_rec.country_code = 'FR' THEN
469 l_error_type := l_error;
470 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
471 'VALUE1: '||hr_general.decode_lookup
472 ('FR_DADS_RUBRICS','S30.G01.00.008.014'), null, null);
473 ELSIF l_emp_rec.country_name is null AND l_emp_rec.country_code is not null
474 AND l_emp_rec.country_code <> 'FR' THEN
475 l_error_type := l_error;
476 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
477 'VALUE1:'||hr_general.decode_lookup
478 ('FR_DADS_RUBRICS','S30.G01.00.008.014'), null, null);
479 ELSE
480 l_error_type := Null;
481 l_value := Null;
482 END IF;
483
484 --IF l_emp_rec.country_name is not null and l_emp_rec.country_code <> 'FR' THEN
485 archive_data(p_rubric_name => 'S30.G01.00.008.014'
486 ,p_message_type => l_error_type
487 ,p_id => p_id
488 ,p_lookup_type => NULL
489 ,p_file_value => l_emp_rec.country_name
490 ,p_message_text => l_value
491 ,p_id2 => l_id2
492 ,p_rubric_type => l_conditional);
493 -- End If;
494
495 archive_data(p_rubric_name => 'S30.G01.00.008.015'
496 ,p_message_type => Null
497 ,p_id => p_id
498 ,p_lookup_type => NULL
499 ,p_file_value => l_emp_rec.tot_address
500 ,p_message_text => Null
501 ,p_id2 => l_id2
502 ,p_rubric_type => l_conditional);
503
504 -- Date of birth
505 IF l_emp_rec.date_of_birth is null THEN
506 l_error_type := l_error;
507 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
508 'VALUE1:'||hr_general.decode_lookup
509 ('FR_DADS_RUBRICS','S30.G01.00.009'), null);
510 ELSE
511 l_error_type := Null;
512 l_value := Null;
513 END IF;
514
515 archive_data( p_rubric_name => 'S30.G01.00.009'
516 ,p_message_type => l_error_type
517 ,p_id => p_id
518 ,p_lookup_type => NULL
519 ,p_file_value => l_emp_rec.date_of_birth
520 ,p_message_text => l_value
521 ,p_id2 => l_id2
522 ,p_rubric_type => l_mandatory);
523
524 -- Town of birth
525 IF (l_emp_rec.town_of_birth is null
526 AND l_emp_rec.country_of_birth = 'FR') THEN
527 l_error_type := l_error;
528 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
529 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
530 ('FR_DADS_RUBRICS','S30.G01.00.010'), null);
531 -- #3553740 Deleted the archiving procedure call. Since, it is archived again after this if loop.
532 ELSE
533 l_error_type := Null;
534 l_value := Null;
535 END IF;
536
537 archive_data(p_rubric_name => 'S30.G01.00.010'
538 ,p_message_type => l_error_type
539 ,p_id => p_id
540 ,p_lookup_type => NULL
541 ,p_file_value => l_emp_rec.town_of_birth
542 ,p_message_text => l_value
543 ,p_id2 => l_id2
544 ,p_rubric_type => l_conditional);
545
546 If (l_emp_rec.country_of_birth <> 'FR' And l_emp_rec.country_of_birth Is not Null) Then
547 -- Birth department code
548 /*IF l_emp_rec.region_of_birth is null THEN
549 l_error_type := l_error;
550 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
551 'VALUE1:'||hr_general.decode_lookup
552 ('FR_DADS_RUBRICS','S30.G01.00.011'), null, null);
553 ELSE
554 l_error_type := Null;
555 l_value := Null;
556 END IF;*/
557 l_error_type := Null;
558 l_value := Null;
559
560 archive_data(p_rubric_name => 'S30.G01.00.011'
561 ,p_message_type => l_error_type
562 ,p_id => p_id
563 ,p_lookup_type => 'FR_DADS_BIRTH_DEPT_CODE'
564 ,p_file_value => 99
565 ,p_message_text => l_value
566 ,p_id2 => l_id2
567 ,p_rubric_type => l_conditional);
568
569 Elsif (l_emp_rec.country_of_birth = 'FR') Then
570 -- Birth department code
571 IF (l_emp_rec.region_of_birth is null) THEN
572 l_error_type := l_error;
573 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
574 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
575 ('FR_DADS_RUBRICS','S30.G01.00.011.M'), null);
576 ELSE
577 l_error_type := Null;
578 l_value := Null;
579 END IF;
580 archive_data(
581 p_rubric_name => 'S30.G01.00.011'
582 ,p_message_type => l_error_type
583 ,p_id => p_id
584 ,p_lookup_type =>'FR_DEPARTMENT'
585 ,p_file_value => l_emp_rec.region_of_birth
586 ,p_message_text => l_value
587 ,p_id2 => l_id2
588 ,p_rubric_type => l_mandatory);
589 /* Adding one more validation for checking whether country of birth is null or not */
590 Elsif (l_emp_rec.country_of_birth is null) Then
591 -- Birth department code
592 IF (l_emp_rec.region_of_birth is null) THEN
593 l_error_type := l_error;
594 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
595 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
596 ('FR_DADS_RUBRICS','S30.G01.00.011.M'), null);
597 ELSE
598 l_error_type := Null;
599 l_value := Null;
600 END IF;
601 archive_data(p_rubric_name => 'S30.G01.00.011'
602 ,p_message_type => l_error_type
603 ,p_id => p_id
604 ,p_lookup_type => Null
605 ,p_file_value => Null
606 ,p_message_text => l_value
607 ,p_id2 => l_id2
608 ,p_rubric_type => l_mandatory);
609 End If;
610 -- Birth country
611 IF l_emp_rec.country_of_birth is null THEN
612 l_error_type := l_error;
613 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
614 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
615 ('FR_DADS_RUBRICS','S30.G01.00.012'), null);
616 ELSE
617 l_error_type := Null;
618 l_value := Null;
619 END IF;
620 archive_data(p_rubric_name => 'S30.G01.00.012'
621 ,p_message_type => l_error_type
622 ,p_id => p_id
623 ,p_lookup_type => NULL
624 ,p_file_value => l_emp_rec.country_of_birth_name
625 ,p_message_text => l_value
626 ,p_id2 => l_id2
627 ,p_rubric_type => l_mandatory);
628
629 -- Nationality
630 IF l_emp_rec.nationality is null THEN
631 l_error_type := l_error;
632 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
633 'VALUE1:'||l_emp_rec.full_name, 'VALUE2:'||hr_general.decode_lookup
634 ('FR_DADS_RUBRICS','S30.G01.00.013'), null);
635 ELSE
636 l_error_type := Null;
637 l_value := Null;
638 END IF;
639 l_nationality := hr_general.decode_lookup('NATIONALITY', l_emp_rec.nationality);
640 archive_data(p_rubric_name => 'S30.G01.00.013'
641 ,p_message_type => l_error_type
642 ,p_id => p_id
643 ,p_lookup_type =>'NATIONALITY'
644 ,p_file_value => l_nationality
645 ,p_message_text => l_value
646 ,p_id2 => l_id2
647 ,p_rubric_type => l_mandatory);
648
649 hr_utility.set_location('S30 person:'||l_person_id||':'||l_emp_rec.assignment_id,9999);
650 execS41_G01_00(p_person_id => l_person_id
651 ,p_assignment_id => l_emp_rec.assignment_id
652 ,p_org_id => p_org_id);
653
654 End loop;
655
656 If (l_emp_found = FALSE) Then
657 FND_FILE.PUT_LINE(FND_FILE.LOG, pay_fr_general.get_payroll_message('PAY_75195_DADS',null));
658 End If;
659
660 Exception
661 When others then
662 hr_utility.set_location('S30 sqlerrm:'||sqlerrm,9999);
663 Raise;
664 End EXECS30_G01_00;
665 --
666
667 ----------------------------------
668 -- S41 Structure --
669 ----------------------------------
670 Procedure execS41_G01_00(p_person_id IN NUMBER
671 ,p_assignment_id IN Number
672 ,p_org_id IN varchar2) is
673
674
675 --Cursors for Start and end reason codes
676 --These cursors will take care of change in Employee Category
677 --and Professional Status Code History
678
679
680 Cursor csr_25 Is
681 select scl.segment2 emp_cat
682 , greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
683 ||to_number(to_char(asg.effective_start_date, 'YYYY'))
684 || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
685 ,g_param_start_date) start_date
686 ,least(asg.effective_end_date, g_effective_date) end_date
687 ,paa.assignment_action_id
688 ,org_est.org_information1
689 from per_all_assignments_f asg
690 ,pay_assignment_actions paa
691 ,hr_soft_coding_keyflex scl
692 ,hr_organization_information org_est
693 where paa.assignment_action_id = g_assign_action_id
694 and asg.effective_end_date >= g_param_start_date
695 and asg.effective_start_date <= g_effective_date
696 and asg.assignment_id = paa.assignment_id
697 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
698 and asg.establishment_id = org_est.organization_id
699 and org_est.org_information1 = p_org_id
700 and asg.person_id = p_person_id
701 and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
702 Order By Start_Date;
703
704 Cursor csr_23 (c_end_date date, c_param_start_date date) Is
705 select scl.segment16 prof_code
706 , greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
707 ||to_number(to_char(asg.effective_start_date, 'YYYY'))
708 || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
709 ,c_param_start_date) start_date
710 ,asg.effective_start_date effective_start_date
711 ,least(asg.effective_end_date, c_end_date) end_date
712 ,paa.assignment_action_id
713 ,org_est.org_information1
714 from per_all_assignments_f asg
715 ,pay_assignment_actions paa
716 ,hr_soft_coding_keyflex scl
717 ,hr_organization_information org_est
718 where paa.assignment_action_id = g_assign_action_id
719 and asg.effective_start_date >= c_param_start_date
720 and asg.effective_start_date <= c_end_date
721 and asg.assignment_id = paa.assignment_id
722 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
723 and asg.establishment_id = org_est.organization_id
724 and org_est.org_information1 = p_org_id
725 and asg.person_id = p_person_id
726 and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
727 Order By effective_start_date, Start_Date;
728
729
730 l_csr_25_rec csr_25%ROWTYPE;
731
732 --Complementory Pension Provider change history
733 Cursor Csr_35 Is
734 select distinct pee.entry_information1 pen_cat
735 , greatest(to_date('01-' ||to_number(to_char(pee.effective_start_date, 'MM')) ||'-'
736 ||to_number(to_char(pee.effective_start_date, 'YYYY'))
737 || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
738 ,g_param_start_date) start_date
739 ,greatest(pee.effective_start_date, g_param_start_date) asg_start_date
740 ,least(pee.effective_end_Date, g_effective_date) end_date
741 from pay_element_entries_f pee
742 ,per_all_assignments_f asg
743 ,hr_organization_information org_est
744 ,pay_assignment_actions paa
745 where paa.assignment_action_id = g_assign_action_id
746 and pee.assignment_id = asg.assignment_id
747 and asg.assignment_id = paa.assignment_id
748 and asg.establishment_id = org_est.organization_id
749 and org_est.org_information1 = p_org_id
750 and asg.person_id = p_person_id
751 and pee.entry_information_category = 'FR_PENSION INFORMATION'
752 and pee.effective_start_date <= g_effective_date
753 and pee.effective_start_date >= g_param_start_date
754 order by start_date;
755
756 l_csr_35_rec csr_35%ROWTYPE;
757 --End Complementory Pension Provider change history
758
759 -- Change of Activity or Work Contract
760 --Process type changes
761 --previously the cursor has a join with assignment table.
762 --Now, the cursor has a join with payroll actions table
763 Cursor csr_process (c_start_date date) IS
764 select distinct pac.context_value process_type
765 ,nvl(paa_date.start_date, (ppa.date_earned - to_number(to_char(ppa.date_earned, 'DD')) + 1)) calc_start_date
766 ,nvl(paa_date.end_date, ppa.date_earned) end_date
767 from pay_action_contexts pac
768 ,ff_contexts fc
769 ,pay_assignment_actions paa
770 ,pay_assignment_actions paa_date
771 ,pay_payroll_actions ppa
772 where pac.assignment_id = paa.assignment_id
773 and pac.assignment_action_id = paa.assignment_action_id
774 and fc.context_id = pac.context_id
775 and fc.context_name = 'SOURCE_TEXT'
776 and paa.assignment_id = p_assignment_id
777 and ppa.payroll_action_id = paa.payroll_action_id
778 and paa_date.assignment_id = paa.assignment_id
779 and paa.source_action_id = paa_date.assignment_action_id
780 and ppa.date_earned between c_start_date and g_effective_date
781 order by calc_start_date;
782
783 --Contract Type Changes
784 Cursor csr_contract (c_end_date date, c_param_start_date date) Is
785 select distinct pcf.ctr_information2 contract
786 , pcf.status status
787 , pcf.contract_id contract_id
788 , asg.effective_start_date start_date
789 , least(pcf.effective_end_date, c_end_date) end_date
790 from pay_assignment_actions paa
791 ,per_all_assignments_f asg
792 ,per_contracts_f pcf
793 ,hr_organization_information org_est
794 where paa.assignment_action_id = g_assign_action_id
795 and org_est.org_information1 = p_org_id
796 and asg.person_id = p_person_id
797 and asg.assignment_id = paa.assignment_id
798 and asg.establishment_id = org_est.organization_id
799 and pcf.contract_id = asg.contract_id
800 and asg.effective_start_date <= c_end_date
801 and asg.effective_start_date >= c_param_start_date
802 and asg.effective_start_date between pcf.effective_start_date
803 and pcf.effective_end_date
804 Order by start_date;
805
806
807 --cursor for assignment category
808 Cursor csr_asg_cat (c_end_date date, c_param_start_date date) IS
809 Select asg.employment_category category
810 , greatest(to_date('01-' ||to_number(to_char(asg.effective_start_date, 'MM')) ||'-'
811 ||to_number(to_char(asg.effective_start_date, 'YYYY'))
812 || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
813 ,c_param_start_date) start_date
814 ,asg.effective_start_date asg_start_date
815 ,least(asg.effective_end_date,c_end_date) asg_end_date
816 From pay_assignment_actions paa
817 ,per_all_assignments_f asg
818 ,hr_organization_information org_est
819 where paa.assignment_action_id = g_assign_action_id
820 and asg.effective_start_date <= c_end_date
821 and asg.effective_start_date >= c_param_start_date
822 and asg.assignment_id = paa.assignment_id
823 and asg.establishment_id = org_est.organization_id
824 and org_est.org_information1 = p_org_id
825 and asg.person_id = p_person_id
826 And asg.assignment_id = paa.assignment_id
827 Order by asg_start_date;
828
829
830 --Cursors for Start and end reason codes
831
832 --This cursor will take care of establishment change of the person.
833 --Cursor 19 Establishment Changes
834 -- Mutation in another establishment in the company Get the prior establishmentid
835 Cursor csr_19(p_person_id number) IS
836 (select asg.establishment_id, org_est.org_information1 company_id
837 ,asg.effective_start_date start_date, 'FIRST' type
838 from pay_assignment_actions paa
839 ,per_all_assignments_f asg
840 ,hr_organization_information org_est
841 where paa.assignment_action_id = g_assign_action_id
842 and asg.assignment_id = paa.assignment_id
843 and asg.person_id = p_person_id
844 and asg.effective_end_date >= g_param_start_date-1
845 and asg.effective_start_date <= g_param_start_date-1
846 and asg.establishment_id = org_est.ORGANIZATION_ID
847 and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO')
848 union all
849 -- Get all the changes in company id
850 (select asg.establishment_id, org_est.org_information1 company_id
851 ,asg.effective_start_date start_date, 'HIST' type
852 from pay_assignment_actions paa
853 ,per_all_assignments_f asg
854 ,hr_organization_information org_est
855 where paa.assignment_action_id = g_assign_action_id
856 and asg.assignment_id = paa.assignment_id
857 and asg.person_id = p_person_id
858 and asg.effective_end_date >= g_param_start_date
859 and asg.effective_start_date <= g_effective_date
860 and asg.establishment_id = org_est.ORGANIZATION_ID
861 and org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO')
862 ORDER by type, start_date;
863
864 l_csr_19_rec csr_19%ROWTYPE;
865
866 --Cursors for other data
867 --NIC of the assignment Establishment...
868 Cursor csr_NIC(p_person_id Number, p_org_id Varchar2) IS
869 Select asg.effective_start_date start_date, asg.establishment_id
870 , substr(org_est.org_information2,length(org_est.org_information2)-4, 5)
871 From pay_assignment_actions paa
872 ,per_all_assignments_f asg
873 ,hr_organization_information org_est
874 Where paa.assignment_action_id = g_assign_action_id
875 and asg.assignment_id = paa.assignment_id
876 and asg.person_id = p_person_id
877 And asg.effective_start_date >= g_param_start_date
878 And asg.effective_start_date <= g_effective_date
879 And asg.establishment_id = org_est.ORGANIZATION_ID
880 And org_est.org_information1 = p_org_id
881 And org_est.ORG_INFORMATION_CONTEXT = 'FR_ESTAB_INFO'
882 Order by start_date desc;
883
884 --Or can use the following cursor which fetches work accident along with NIC
885 /*Cursor csr41_estab_data(p_person_id Number, p_org_id Varchar2) IS
886 Select Distinct org_est.org_information3 hrorg
887 ,org_est.org_information4 risk_code_month_hours
888 ,org_est.org_information5 order_number
889 ,org_est.org_information6 section_code
890 ,org_est.org_information7 office_code
891 ,org_est.ORG_INFORMATION_CONTEXT information_context
892 ,substr(org_est.org_information2,length(org_est.org_information2)-4, 5) siret_number
893 -- all Work accident info details
894 ,asg.establishment_id estab_id--
895 ,asg.location_id asg_location
896 ,hla.location_id est_location
897 /* Getting the issuing establishment Name
898 ,hou_issue_estab_tl.name issue_estab_name
899 ,hloc_asg_estab_tl.location_code location_name
900 ,asg_estab_tl.name asg_estab_name
901 From per_all_assignments_f asg
902 ,pay_assignment_actions paa
903 ,hr_organization_information org_est
904 ,hr_all_organization_units hou
905 ,hr_locations_all hla
906 ,hr_locations_all_tl hloc_asg_estab_tl
907 -- Getting the Establishment Name
908 ,hr_all_organization_units_tl hou_issue_estab_tl
909 -- Getting the Assignment Establishment's Name
910 ,hr_all_organization_units asg_estab_tl
911 Where paa.assignment_action_id = g_assign_action_id
912 and asg.person_id = p_person_id
913 And asg.effective_end_date between g_param_start_date and g_effective_date
914 And asg.assignment_id = paa.assignment_id
915 And asg.establishment_id = org_est.ORGANIZATION_ID
916 And hla.location_id(+) = hou.location_id
917 And asg.establishment_id = org_est.ORGANIZATION_ID
918 And hou.organization_id(+) = org_est.ORGANIZATION_ID
919 And org_est.ORG_INFORMATION_CONTEXT In ('FR_ESTAB_WORK_ACCIDENT', 'FR_ESTAB_INFO')
920 --
921 and hloc_asg_estab_tl.location_id(+) = asg.location_id
922 and hloc_asg_estab_tl.language(+) = userenv('LANG')
923 --
924 AND hou_issue_estab_tl.organization_id = org_est.organization_id
925 AND hou_issue_estab_tl.language = userenv('LANG')
926 --
927 AND asg.establishment_id = asg_estab_tl.organization_id (+);
928 */
929
930 Cursor csr41_estab_data(p_effective_date Date) IS
931 Select distinct paa.assignment_action_id
932 ,org_est.org_information3 hrorg
933 ,org_est.org_information4 risk_code_month_hours
934 ,org_est.org_information5 order_number
935 ,org_est.org_information6 section_code
936 ,org_est.org_information7 office_code
937 ,org_est.ORG_INFORMATION_CONTEXT information_context
938 ,substr(org_est.org_information2,length(org_est.org_information2)-4, 5) siret_number
939 ,asg.establishment_id estab_id
940 ,asg.location_id asg_location
941 ,hla.location_id est_location
942 ,hou_issue_estab_tl.name issue_estab_name
943 ,hloc_asg_estab_tl.location_code location_name
944 ,asg_estab_tl.name asg_estab_name
945 From pay_assignment_actions paa
946 ,per_all_assignments_f asg
947 ,hr_organization_information org_est
948 ,hr_locations_all hla
949 ,hr_locations_all_tl hloc_asg_estab_tl
950 ,hr_all_organization_units_tl hou_issue_estab_tl
951 ,hr_all_organization_units asg_estab_tl
952 ,hr_all_organization_units hou
953 Where paa.assignment_action_id = g_assign_action_id
954 And paa.assignment_id = asg.assignment_id
955 And asg.person_id = p_person_id
956 And asg.establishment_id = org_est.ORGANIZATION_ID
957 And hla.location_id(+) = hou.location_id
958 And asg.establishment_id = org_est.ORGANIZATION_ID
959 And hou.organization_id(+) = org_est.ORGANIZATION_ID
960 And org_est.ORG_INFORMATION_CONTEXT In ('FR_ESTAB_WORK_ACCIDENT', 'FR_ESTAB_INFO')
961 and hloc_asg_estab_tl.location_id(+) = asg.location_id
962 and hloc_asg_estab_tl.language(+) = userenv('LANG')
963 AND hou_issue_estab_tl.organization_id = org_est.organization_id
964 AND hou_issue_estab_tl.language(+) = userenv('LANG')
965 AND asg.establishment_id = asg_estab_tl.organization_id (+)
966 and asg.effective_start_date <= p_effective_date;
967
968 l_csr41_estab_data csr41_estab_data%rowtype;
969 --
970
971 --Cursor to fetch all date tracked data of the S41 structure
972 Cursor fetch_date_trk_41(p_effective_date DATE) Is
973 Select pj.Name job_name --job Flex field as given in assedic report
974 , pj.job_definition_id job_definition_id
975 , pj.job_id job_id
976 , pj.job_information1 pcs_code -- Open issue, Now fetching as mentioned in assedic report, issue is closed and all the characters are retreived
977 , decode(pc.type,'APPRENTICESHIP','05','YOUNG_PERSON','06',
978 decode(pc.ctr_information2
979 ,'FIXED_TERM','02','PERMANENT','01',Null)) contract_type --Work Contract Code
980 , pc.type contract_type_1 -- used to validate seasonal worker for activity type code
981 , sck.SEGMENT16 prof_status_code --Professional Status Code
982 , sck.SEGMENT14 border_worker -- used to determine border worker code
983 , sck.SEGMENT12 detache --used to determine border worker code
984 , pc.type act_type--Used for activity type code (Seasonal Worker has not been defined in seed yet)
985 , paf.employee_number person_number -- employee number
986 , asg.normal_hours norm_asg_hours-- Used for Percentage of part_time
987 , asg.frequency asg_frequency-- Used for Percentage of part_time
988 -- added for time analysis
989 , pc.ctr_information13 ctr_frequency -- Used for Percentage of part_time -- need to check whether its monthly or not
990 , pc.ctr_information12 ctr_units -- used in percentage part time calculation
991 , pc.ctr_information11 norm_ctr_hours -- Used for Percentage of part_time
992 --
993 , asg.effective_start_date
994 , asg.employment_category emp_cat -- Used for activity_type_code and percentage of part_time
995 , paf.full_name Name
996 , pca.CAG_INFORMATION1 col_aggr_code --S41.G01.00.016
997 , asg.assignment_id assignment_id -- used for fetch conventional classfication --S41.G01.00.017
998 /* DECODE(SEX, 'F', DECODE(NVL(PER_INFORMATION1, PREVIOUS_LAST_NAME), LAST_NAME, NULL,LAST_NAME) , 'M', DECODE( PREVIOUS_LAST_NAME, LAST_NAME, NULL, LAST_NAME)) Name --Getting the employee full name*/
999 From per_all_assignments_f asg
1000 ,pay_assignment_actions paa
1001 , per_jobs pj
1002 , per_all_people_f paf
1003 , per_contracts_f pc
1004 , HR_SOFT_CODING_KEYFLEX sck
1005 , hr_organization_information org_est
1006 , per_collective_agreements pca
1007 Where paa.assignment_action_id = g_assign_action_id
1008 and asg.person_id = paf.person_id
1009 And asg.person_id = p_person_id
1010 And asg.assignment_id = paa.assignment_id
1011 And org_est.org_information1 = p_org_id
1012 And asg.establishment_id = org_est.organization_id
1013 And p_effective_date between asg.effective_start_date
1014 And asg.effective_end_date
1015 And asg.job_id = pj.job_id(+)
1016 And asg.SOFT_CODING_KEYFLEX_ID = sck.SOFT_CODING_KEYFLEX_ID
1017 And asg.contract_id=pc.contract_id(+)
1018 And asg.COLLECTIVE_AGREEMENT_ID = pca.COLLECTIVE_AGREEMENT_ID(+)
1019 And nvl(pca.CAG_INFORMATION_CATEGORY, 'FR') = 'FR'
1020 Order By asg.effective_start_date Desc;
1021
1022 --l_date_trk_41_rec fetch_date_trk_41%rowtype;
1023 --
1024 --
1025 -- Cursor to fetch the pension information....
1026 --
1027 Cursor fetch_pension_info(p_date Date) Is
1028 Select pef.entry_information1 pension_info--Used in employment cat code
1029 From pay_element_entries_f pef
1030 , pay_element_links_f pel
1031 , pay_element_types_f pet
1032 , per_all_assignments_f asg
1033 , hr_organization_information org_est
1034 ,pay_assignment_actions paa
1035 Where paa.assignment_action_id = g_assign_action_id
1036 and pef.assignment_id = asg.assignment_id
1037 and paa.assignment_id = asg.assignment_id
1038 And asg.person_id = p_person_id
1039 And asg.establishment_id = org_est.organization_id
1040 And org_est.org_information1 = p_org_id
1041 And pef.entry_information_category = 'FR_PENSION INFORMATION'
1042 And p_date Between pef.effective_start_date
1043 And pef.effective_end_date
1044 And pef.element_link_id = pel.element_link_id
1045 And pel.element_type_id = pet.Element_type_id
1046 And pet.element_name = 'FR_PENSION'
1047 Order By pef.effective_start_date Desc;
1048
1049 --
1050 --Cursor to determine assignment location details
1051 Cursor csr_asg_loc(p_asg_location Number) Is
1052 Select hla.address_line_2 complement
1053 ,hla.address_line_1 street_name
1054 ,hla.region_2 insee_code
1055 ,hla.region_3 small_town
1056 ,hla.postal_code zip_code
1057 ,upper(hla.town_or_city) town
1058 ,hla.country country_code
1059 ,ft_tl.territory_short_name country_name
1060 From hr_locations_all hla
1061 ,fnd_territories ft
1062 ,fnd_territories_tl ft_tl
1063 Where location_id = p_asg_location
1064 And ft.territory_code = hla.country
1065 And ft_tl.territory_code (+) = ft.territory_code
1066 And ft_tl.language (+) = userenv('LANG');
1067
1068 l_csr_asg_loc csr_asg_loc%rowtype;
1069
1070 --Cursor to determine dates and leaving reasons of a person
1071 Cursor get_person_dtl IS
1072 Select pps.Date_start start_date
1073 ,pps.Actual_termination_date term_date
1074 ,pps.Final_process_date final_date
1075 ,pps.Leaving_reason leav_reason
1076 From per_periods_of_service pps
1077 ,per_all_assignments_f asg
1078 ,hr_organization_information org_est
1079 ,pay_assignment_actions paa
1080 Where paa.assignment_action_id = g_assign_action_id
1081 and org_est.org_information1 = p_org_id
1082 And pps.person_id = asg.person_id
1083 And asg.person_id = p_person_id
1084 and asg.assignment_id = paa.assignment_id
1085 And asg.establishment_id = org_est.organization_id
1086 And pps.date_start between asg.effective_start_date and asg.effective_end_date;
1087
1088
1089
1090 --Cursor to determine user_table values
1091 --
1092 --
1093 cursor c_assignment_cursor is
1094 select assignment_id
1095 ,greatest(g_param_start_date,effective_start_date) effective_start_date
1096 ,least(g_effective_date, effective_end_date) effective_end_date
1097 from per_all_assignments_f paf
1098 where person_id = p_person_id
1099 And g_effective_date Between effective_start_date And effective_end_date;
1100
1101 c_assignment c_assignment_cursor%rowtype;
1102
1103 --Cursor for multiple employer
1104 Cursor get_multi_emp(p_effective_date DATE) Is
1105 Select pef.element_entry_id element_entry_id
1106 From pay_element_entries_f pef
1107 ,pay_element_links_f pel
1108 ,pay_element_types_f pet
1109 Where pef.assignment_id = p_assignment_id
1110 And pef.element_link_id = pel.element_link_id
1111 And pel.element_type_id = pet.Element_type_id
1112 And pet.element_name = 'FR_MULTIPLE_EMPLOYER'
1113 And p_effective_date Between pet.effective_start_date
1114 And pet.effective_end_date
1115 And p_effective_date Between pef.effective_end_date
1116 And pef.effective_end_date;
1117
1118
1119 --Cursor to fetch Conventional Classification.....S41.G01.00.017
1120
1121 Cursor csr_fetch_conv_class(p_assignment_id per_all_assignments_f.assignment_id%type
1122 ,p_effective_date DATE
1123 ,p_per_id Number)
1124 Is
1125 Select gqual.segment_attribute_type qualifier
1126 ,substr(CAGR.segment1,1,60) conv_classfication
1127 from
1128 per_all_assignments_f asg
1129 ,per_cagr_grades_def cagr
1130 ,fnd_id_flex_segments seg
1131 ,fnd_segment_attribute_values gqual
1132 where asg.assignment_id = p_assignment_id
1133 and asg.cagr_grade_def_id = CAGR.cagr_grade_def_id (+)
1134 and gqual.id_flex_num(+) = CAGR.id_flex_num
1135 and gqual.id_flex_code(+) = 'CAGR'
1136 and gqual.attribute_value(+) = 'Y'
1137 and seg.id_flex_code = 'CAGR'
1138 and seg.id_flex_num = asg.cagr_id_flex_num
1139 and seg.application_id = p_per_id
1140 and gqual.application_id = p_per_id
1141 and seg.application_column_name = gqual.application_column_name
1142 and p_effective_date between asg.effective_start_date and asg.effective_end_date
1143 and gqual.application_column_name = 'SEGMENT1'
1144 and gqual.segment_attribute_type = 'COEFFICIENT'
1145 order by seg.segment_num;
1146
1147 -- Fetch the product Id
1148 cursor csr_get_per_id is
1149 select application_id
1150 from fnd_application
1151 where application_short_name = 'PER';
1152 --
1153 -- New cursor is defined to get all the payroll periods
1154 -- as part of Time Analysis Changes.
1155 Cursor csr_get_per_dates(p_effective_date DATE) Is
1156 select ptp.end_date
1157 ,ptp.start_date
1158 from pay_action_interlocks pai
1159 , pay_assignment_actions paa
1160 , pay_payroll_actions ppa
1161 , per_time_periods ptp
1162 where pai.locking_action_id = g_assign_action_id
1163 and paa.assignment_action_id = pai.locked_action_id
1164 and ppa.payroll_action_id = paa.payroll_action_id
1165 and ptp.payroll_id = ppa.payroll_id
1166 and ptp.start_date > g_param_start_date
1167 and ptp.end_date < p_effective_date
1168 Order By ptp.start_date desc;
1169 --
1170 type t_asg_org is record(
1171 start_date date,
1172 end_date date,
1173 start_reason Varchar2(25),
1174 end_reason Varchar2(25)) ;
1175
1176 Type hist_tab IS TABLE of t_asg_org INDEX BY BINARY_INTEGER;
1177 l_hist hist_tab;
1178
1179 --Local Variables....
1180 -- #3587152
1181 l_ss_base NUMBER;
1182 l_ss_base_retro NUMBER;
1183 l_ss_disabled_base NUMBER;
1184 l_ss_disabled_base_retro NUMBER;
1185 l_ss_excess_base NUMBER;
1186 l_ss_part_time_base NUMBER;
1187 l_ss_part_time_base_retro NUMBER;
1188 l_ss_apprenticeship_base NUMBER;
1189 l_ss_base_above_smic NUMBER;
1190 l_ss_base_above_smic_retro NUMBER;
1191 l_ta_ss_band_retro NUMBER;
1192 l_ta_ss_disabled_band_retro NUMBER;
1193 l_ta_ss_part_time_band_retro NUMBER;
1194 l_ta_ss_band_under_limit NUMBER;
1195 l_ta_ss_band_under_limit_retro NUMBER;
1196 l_ta_ss_band_above_smic_retro NUMBER;
1197 -- #3587152
1198
1199 l_ta_ss_band NUMBER;
1200 l_ta_ss_disabled_band NUMBER;
1201 l_ta_ss_part_time_band NUMBER;
1202 l_subject_to_csg NUMBER;
1203 l_subject_to_csg_excess NUMBER;
1204 l_subject_to_crds NUMBER;
1205 l_subject_to_salary_tax NUMBER;
1206 l_subject_to_salary_tax_excess NUMBER;
1207 l_benefit_food NUMBER;
1208 l_benefit_housing NUMBER;
1209 l_benefit_car NUMBER;
1210 l_benefit_other NUMBER;
1211 l_prof_expense_lump_sums NUMBER;
1212 l_actual_prof_expenses NUMBER;
1213 l_prof_exp_pd_by_comp NUMBER;
1214 l_reimb_nprof_exp NUMBER;
1215 l_taxable_income NUMBER;
1216 l_gross_sbase_sec NUMBER;
1217 l_gross_sbase_sec_sign VARCHAR2(1);
1218 l_cap_ssec_base NUMBER;
1219 l_cap_ssec_base_sign VARCHAR2(1);
1220 l_csg_base NUMBER;
1221 l_csg_base_sign VARCHAR2(1);
1222 l_fiscal_base NUMBER;
1223 l_fiscal_base_sign VARCHAR2(1);
1224 l_fringe_benefits NUMBER;
1225 l_fringe_benefits_sign VARCHAR2(1);
1226 l_professional_expenses NUMBER;
1227 l_professional_expenses_sign VARCHAR2(1);
1228 l_taxable_income_sign VARCHAR2(1);
1229
1230
1231 l_work_hr_org hr_organization_information.org_information3%type;
1232 l_work_risk_code hr_organization_information.org_information4%type;
1233 l_temp_work_risk_code hr_organization_information.org_information4%type;
1234 l_work_order_number hr_organization_information.org_information5%type;
1235 l_work_section_code hr_organization_information.org_information6%type;
1236 l_work_office_code hr_organization_information.org_information7%type;
1237
1238 l_param_start_date DATE;
1239 l_effective_date DATE;
1240
1241 l_payroll_action_id pay_assignment_actions.payroll_action_id%type;
1242 l_counter NUMBER;
1243 l_flag BOOLEAN;
1244 l_097_exists NUMBER;
1245 l_date_start DATE;
1246 l_act_dt DATE;
1247 l_final_date DATE;
1248 l_month NUMBER;
1249 l_year NUMBER;
1250 l_date DATE;
1251 l_multi_employr_code VARCHAR2(10);
1252 l_positive_offset Varchar2(10);
1253 l_job_id number;
1254 l_job_definition_id number;
1255 l_job_name per_jobs.name%type;
1256 l_pcs_code VARCHAR2(50);
1257 l_eff_job_date DATE;
1258 l_leav_reason per_periods_of_service.leaving_reason%type;
1259
1260 l_percentage_part_time NUMBER;
1261 l_emp_month_hours NUMBER;
1262 l_estab_monthly_hours NUMBER;
1263 l_est_location hr_locations_all.location_id%type;
1264 l_asg_location hr_locations_all.location_id%type;
1265 l_bal_start_date DATE;
1266 l_value fnd_new_messages.message_text%type;
1267 l_unused_number NUMBER;
1268 l_first_row BOOLEAN;
1269 l_old_value VARCHAR2(250);
1270 l_old_value_1 VARCHAR2(250);
1271 l_old_coy hr_organization_information.organization_id%type;
1272 l_old_est hr_organization_information.organization_id%type;
1273 l_work_acc_rate pay_user_column_instances_f.value%type;
1274 l_siret_number VARCHAR2(10);
1275 l_estab_id per_all_assignments_f.establishment_id%type;
1276 l_element_entry_id pay_element_entries_f.element_entry_id%type;
1277 l_contract_type VARCHAR2(50);
1278 l_contract_type_1 per_contracts_f.type%type;
1279 l_prof_code HR_SOFT_CODING_KEYFLEX.segment16%type;
1280 l_border_worker HR_SOFT_CODING_KEYFLEX.segment12%type;
1281 l_detache HR_SOFT_CODING_KEYFLEX.segment14%type;
1282 l_act_type per_contracts_f.type%type;
1283 l_norm_hours NUMBER;
1284 l_frequency per_all_assignments_f.frequency%type;
1285 l_employment_cat per_all_assignments_f.employment_category%type;
1286 l_person_number per_all_people_f.employee_number%type;
1287 l_act_typ_val pay_user_column_instances_f.value%type;
1288 l_act_typ_code VARCHAR2(10);
1289 l_pension_info pay_element_entries_f.entry_information1%type;
1290 l_pen_agirc_val pay_user_column_instances_f.value%type;
1291 l_emp_cat_code VARCHAR2(10);
1292 l_col_aggr_code VARCHAR2(4);
1293 l_pension_code NUMBER;
1294 l_subject_to_crds_sign VARCHAR2(1);
1295 l_benefit_food_sign VARCHAR2(1);
1296 l_benefit_housing_sign VARCHAR2(1);
1297 l_benefit_car_sign VARCHAR2(1);
1298 l_benefit_other_sign VARCHAR2(1);
1299 l_prof_expense_lump_sums_sign VARCHAR2(1);
1300 l_actual_prof_expenses_sign VARCHAR2(1);
1301 l_prof_exp_pd_by_comp_sign VARCHAR2(1);
1302 l_reimb_nprof_exp_sign VARCHAR2(1);
1303 l_assignment_id per_all_assignments_f.assignment_id%type;
1304 l_id2 VARCHAR2(30);
1305
1306 --Used in determining reason code 21....
1307 l_21_con_old Varchar2(250); --contract category-- proration is feasible
1308 l_21_con_status per_contracts_f.status%type; --contract status -proration is feasible
1309 l_21_status_old Varchar2(250); --contract status-- proration is feasible
1310 l_21_cat_old Varchar2(250); -- employee category -- proration feasible only if its parttime.
1311 l_21_cipdz_old varchar2(250); --CIPDZ Value -- proration feasible only if its parttime.
1312 l_21_ptype_old Varchar2(250); --Process Type -- proration is feasible.
1313 l_conventional_classification Varchar2(250);
1314 l_per_id Number;
1315 l_cipdz_value varchar2(250);
1316 --Names
1317 l_issue_estab_name hr_all_organization_units_tl.name%type;
1318 l_emp_full_name per_all_people_f.full_name%type;
1319 l_country_code Varchar2(30);
1320 l_location_name hr_locations_all_tl.location_code%type;
1321
1322 --Variable to hold the last payroll run date, which has been executed just before the dads period
1323 c_end_date date;
1324 c_start_date date;
1325
1326 -- Local Variable Declaration for Exceptions
1327 l_error_type hr_lookups.meaning%type;
1328 l_error hr_lookups.meaning%type;
1329 l_warning hr_lookups.meaning%type;
1330 l_asg_estab_name hr_all_organization_units_tl.name%type;
1331 l_mandatory varchar2(1);
1332 l_conditional varchar2(1);
1333 l_optional varchar2(1);
1334
1335 l_id2_num Number;
1336
1337 l_tbl_count Number;
1338
1339 --Variables to hold the values for S41.G01.00.021, S41.G01.00.022, S41.G01.00.023
1340 l_num_hrs Number;
1341 l_num_hrs_emp Number;
1342 l_num_hrs_latest Number;
1343 l_mth_023 varchar2(2);
1344 -- variables added for time analysis changes
1345 l_ctr_frequency per_contracts_f.ctr_information13%type;
1346 l_norm_ctr_hours per_contracts_f.ctr_information11%type;
1347 l_ctr_units per_contracts_f.ctr_information12%type;
1348 l_023_ss_base NUMBER;
1349 l_023_ss_base_retro NUMBER;
1350 l_023_ss_disabled_base NUMBER;
1351 l_023_ss_disabled_base_retro NUMBER;
1352 l_023_ss_excess_base NUMBER;
1353 l_023_ss_part_time_base NUMBER;
1354 l_023_ss_part_time_base_retro NUMBER;
1355 l_023_ss_apprenticeship_base NUMBER;
1356 l_023_ss_base_above_smic NUMBER;
1357 l_023_ss_base_above_smic_retro NUMBER;
1358 l_023_gross_sbase_sec NUMBER;
1359 l_hourly_smic_rate NUMBER;
1360 l_session_id fnd_sessions.session_id%type;
1361
1362 --temp Variables used to sort pl/sql table...
1363 tmp_start_date Date;
1364 tmp_start_reason Varchar2(10);
1365 tmp_end_date Date;
1366 tmp_end_reason Varchar2(10);
1367
1368 k Number;
1369
1370
1371 --Variable to track pension category change...
1372 l_pen_cat_val Varchar2(10);
1373 l_emp_cat_rs_code Varchar2(10);
1374
1375 BEGIN
1376 -- Initializing the local variables
1377 -- #3587152
1378 l_ss_base := 0;
1379 l_ss_base_retro := 0;
1380 l_ss_disabled_base := 0;
1381 l_ss_disabled_base_retro := 0;
1382 l_ss_excess_base := 0;
1383 l_ss_part_time_base := 0;
1384 l_ss_part_time_base_retro := 0;
1385 l_ss_apprenticeship_base := 0;
1386 l_ss_base_above_smic := 0;
1387 l_ss_base_above_smic_retro := 0;
1388 l_ta_ss_band_retro := 0;
1389 l_ta_ss_disabled_band_retro := 0;
1390 l_ta_ss_part_time_band_retro := 0;
1391 l_ta_ss_band_under_limit := 0;
1392 l_ta_ss_band_under_limit_retro := 0;
1393 l_ta_ss_band_above_smic_retro := 0;
1394 -- #3587152
1395
1396 l_ta_ss_band := 0;
1397 l_ta_ss_disabled_band := 0;
1398 l_ta_ss_part_time_band := 0;
1399 l_subject_to_csg := 0;
1400 l_subject_to_csg_excess := 0;
1401 l_subject_to_crds := 0;
1402 l_subject_to_salary_tax := 0;
1403 l_subject_to_salary_tax_excess := 0;
1404 l_benefit_food := 0;
1405 l_benefit_housing := 0;
1406 l_benefit_car := 0;
1407 l_benefit_other := 0;
1408 l_prof_expense_lump_sums := 0;
1409 l_actual_prof_expenses := 0;
1410 l_prof_exp_pd_by_comp := 0;
1411 l_reimb_nprof_exp := 0;
1412 l_taxable_income := 0;
1413 l_gross_sbase_sec := 0;
1414 l_cap_ssec_base := 0;
1415 l_csg_base := 0;
1416 l_fiscal_base := 0;
1417 l_fringe_benefits := 0;
1418 l_professional_expenses := 0;
1419 l_097_exists := 0;
1420 l_mandatory := 'M';
1421 l_conditional := 'C';
1422 l_optional := 'O';
1423 -- variables initialized for time analysis
1424 l_023_ss_base := 0;
1425 l_023_ss_base_retro := 0;
1426 l_023_ss_disabled_base := 0;
1427 l_023_ss_disabled_base_retro := 0;
1428 l_023_ss_excess_base := 0;
1429 l_023_ss_part_time_base := 0;
1430 l_023_ss_part_time_base_retro := 0;
1431 l_023_ss_apprenticeship_base := 0;
1432 l_023_ss_base_above_smic := 0;
1433 l_023_ss_base_above_smic_retro := 0;
1434 l_023_gross_sbase_sec := 0;
1435 l_hourly_smic_rate := 0;
1436 --
1437 hr_utility.set_location('S41 p_assact_id:'||g_assign_action_id,10);
1438 l_hist.DELETE;
1439 l_assignment_id := p_assignment_id;
1440 l_tbl_count := 1;
1441
1442 -- Reson 095 and 097 is fetched from the sql below
1443 -- 095: this is done only when there is a employee who has atd
1444 -- between st and end dates of dads report dates
1445
1446 For get_person_rec In get_person_dtl
1447 Loop
1448 l_date_start := get_person_rec.start_date;
1449 l_act_dt := get_person_rec.term_date;
1450 l_final_date := get_person_rec.final_date;
1451 l_leav_reason := hr_general.decode_lookup('LEAV_REAS', get_person_rec.leav_reason);
1452 End Loop;
1453
1454 hr_utility.set_location('S41 g_param_start_date 97:'||g_param_start_date,10);
1455
1456 If l_date_start < g_param_start_date Then
1457 l_hist(l_tbl_count).start_date := g_param_start_date;
1458 l_hist(l_tbl_count).start_reason := '097';
1459 hr_utility.set_location('S41 g_param_start_date 97:'||g_param_start_date,10);
1460 l_tbl_count := l_tbl_count + 1;
1461 End If;
1462
1463 --end for reason 97
1464
1465 --035:Pension Category change....
1466 -- The following code is changed owing to requirements change
1467 -- mentioned in bug#3285375
1468 -- This will record changes for reason code 25 and reason code 35 is outscoped...
1469
1470 l_first_row := TRUE;
1471 For csr_35_rec in csr_35 Loop
1472 Exit when csr_35%notfound;
1473 if l_first_row = TRUE THEN
1474 -- record the first row for pen cat
1475 l_hist(l_tbl_count).start_date := greatest(csr_35_rec.start_date, l_date_start);
1476 l_hist(l_tbl_count).start_reason := '025';
1477 l_first_row := FALSE;
1478 -- store the comparison value for pen_cat
1479 l_old_value := csr_35_rec.pen_cat;
1480 IF(l_hist(l_tbl_count).start_date <> l_date_start) Then
1481 l_tbl_count := l_tbl_count + 1;
1482 End IF;
1483 elsif l_old_value <> csr_35_rec.pen_cat THEN
1484 -- the stored value has changed - record this in the history
1485 Begin
1486 l_pen_cat_val := hruserdt.get_table_value (
1487 g_param_business_group_id,
1488 'FR_APEC_AGIRC',
1489 'AGIRC' ,
1490 csr_35_rec.pen_cat,
1491 g_effective_date);
1492 Exception
1493 When Others then
1494 hr_utility.set_location('S41 FR_APEC_AGIRC Failed:'||sqlerrm,1113);
1495 End;
1496
1497 If l_pen_cat_val = 'Y' Then
1498 If csr_35_rec.pen_cat = '222' Then -- Article 36
1499 l_emp_cat_rs_code := '02';
1500 Else
1501 l_emp_cat_rs_code := '01';
1502 End If;
1503 Else
1504 l_emp_cat_rs_code := '04';
1505 End If;
1506
1507 If ((l_emp_cat_rs_code = '01')
1508 Or (l_emp_cat_rs_code = '02')
1509 Or (l_emp_cat_rs_code = '04')) Then
1510 l_hist(l_tbl_count).start_date := greatest(csr_35_rec.start_date, l_date_start);
1511 l_hist(l_tbl_count).start_reason := '025';
1512 -- and store the new comparison value
1513 l_old_value := csr_35_rec.pen_cat;
1514 l_tbl_count := l_tbl_count + 1;
1515 end if;
1516 end if;
1517 End loop;--
1518
1519 --Change in employee Category and professional status code
1520 -- The following code is commented owing to requirements change
1521 -- mentioned in bug#3285375
1522 /*l_first_row := TRUE;
1523 For csr_25_rec in csr_25 Loop
1524 Exit when csr_25%notfound;
1525 if l_first_row = TRUE THEN
1526 -- record the first row for emp cat
1527 l_hist(l_tbl_count).start_date := greatest(csr_25_rec.start_date, l_date_start);
1528 l_hist(l_tbl_count).start_reason := '025';
1529 l_first_row := FALSE;
1530 -- store the comparison value for emp_cat
1531 l_old_value := csr_25_rec.emp_cat;
1532 IF(l_hist(l_tbl_count).start_date <> l_date_start) Then
1533 l_tbl_count := l_tbl_count + 1;
1534 End If;
1535 elsif l_old_value <> csr_25_rec.emp_cat THEN
1536 -- the stored value has changed - record this in the history
1537 l_hist(l_tbl_count).start_date := greatest(csr_25_rec.start_date, l_date_start);
1538 l_hist(l_tbl_count).start_reason := '025';
1539 -- and store the new comparison value
1540 l_old_value := csr_25_rec.emp_cat;
1541 l_tbl_count := l_tbl_count + 1;
1542 end if;
1543 End loop;
1544 */
1545 --Get the last but one date for getting the values
1546 BEGIN
1547 select max(effective_start_date)
1548 into l_param_start_date
1549 from per_all_assignments_f
1550 where assignment_id = p_assignment_id
1551 and effective_start_date < g_param_start_date;
1552 IF l_param_start_date IS NULL THEN
1553 l_param_start_date := g_param_start_date;
1554 END IF;
1555 EXCEPTION
1556 WHEN OTHERS THEN
1557 l_param_start_date := g_param_start_date;
1558 END;
1559
1560 --Change in professional status code
1561 l_first_row := TRUE;
1562 IF l_act_dt is not null and l_act_dt <= g_effective_date then
1563 c_end_date := l_act_dt;
1564 else
1565 c_end_date := g_effective_date;
1566 end if;
1567 For csr_23_rec in csr_23 (c_end_date, l_param_start_date) Loop
1568 Exit when csr_23%notfound;
1569 -- Since we are taking the previous values, we don't add the first record of the cursor into the pl/sql table
1570 if l_first_row = TRUE THEN
1571 -- record the first row prof code
1572 -- l_hist(l_tbl_count).start_date := greatest(csr_23_rec.start_date, l_date_start);
1573 -- l_hist(l_tbl_count).start_reason := '023';
1574 -- store the comparison value for prof_code
1575 l_first_row := FALSE;
1576 l_old_value := csr_23_rec.prof_code;
1577 -- IF(l_hist(l_tbl_count).start_date <> l_date_start) Then
1578 -- l_tbl_count := l_tbl_count + 1;
1579 -- End If;
1580 -- Since professional status code is null, it is also added inthe if clause
1581 elsif ((l_old_value <> csr_23_rec.prof_code
1582 OR (l_old_value IS NULL AND csr_23_rec.prof_code IS NOT NULL)
1583 OR (l_old_value IS NOT NULL AND csr_23_rec.prof_code IS NULL))
1584 and csr_23_rec.start_date >= g_param_start_date) THEN
1585 -- the stored value has changed - record this in the history
1586 l_hist(l_tbl_count).start_date := greatest(csr_23_rec.start_date, l_date_start);
1587 l_hist(l_tbl_count).start_reason := '023';
1588 l_old_value := csr_23_rec.prof_code;
1589 l_tbl_count := l_tbl_count + 1;
1590 -- If the start date is before dads period start date, it is just replaced with the old value
1591 elsif ((l_old_value <> csr_23_rec.prof_code
1592 OR (l_old_value IS NULL AND csr_23_rec.prof_code IS NOT NULL)
1593 OR (l_old_value IS NOT NULL AND csr_23_rec.prof_code IS NULL))
1594 and csr_23_rec.start_date < g_param_start_date) THEN
1595 -- the stored value has changed - record this in the history
1596 l_old_value := csr_23_rec.prof_code;
1597 End If;
1598 End loop;
1599 --
1600
1601 -- Assignment Category, Process Type and Contract changes....
1602 -- Need to make changes for the FR_CIPDZ table and check individually for these changes
1603 -- Also for the cursor to look for payroll periods
1604 -- Assignment Category, Process Type and Contract changes....
1605 l_first_row := TRUE;
1606 BEGIN
1607 select max(ppa.date_earned)
1608 into c_start_date
1609 from pay_assignment_actions paa
1610 ,pay_payroll_actions ppa
1611 where paa.assignment_id = p_assignment_id
1612 and ppa.payroll_action_id = paa.payroll_action_id
1613 and ppa.date_earned < g_param_start_date
1614 and ppa.action_status = 'C'
1615 and ppa.action_type In ('R', 'Q');
1616 IF c_start_date is null THEN
1617 c_start_date := g_param_start_date;
1618 END IF;
1619 EXCEPTION
1620 WHEN OTHERS THEN
1621 c_start_date := g_param_start_date;
1622 END;
1623 For csr_process_rec in csr_process (c_start_date) Loop
1624 Exit when csr_process%notfound;
1625 if l_first_row = TRUE THEN
1626 l_21_ptype_old := csr_process_rec.process_type;
1627 l_first_row := FALSE;
1628 elsif l_21_ptype_old <> csr_process_rec.process_type
1629 and csr_process_rec.calc_start_date >= g_param_start_date THEN
1630 -- the stored value has changed - record this in the history
1631 l_hist(l_tbl_count).start_date := greatest(csr_process_rec.calc_start_date, l_date_start);
1632 l_hist(l_tbl_count).start_reason := '021';
1633 -- and store the new comparison value
1634 l_21_ptype_old := csr_process_rec.process_type;
1635 l_tbl_count := l_tbl_count + 1;
1636 elsif l_21_ptype_old <> csr_process_rec.process_type
1637 and csr_process_rec.calc_start_date < g_param_start_date THEN
1638 l_21_ptype_old := csr_process_rec.process_type;
1639 end if;
1640 End loop;
1641
1642 l_first_row := TRUE;
1643 IF l_act_dt is not null and l_act_dt <= g_effective_date then
1644 c_end_date := l_act_dt;
1645 else
1646 c_end_date := g_effective_date;
1647 end if;
1648
1649 For csr_contract_rec in csr_contract (c_end_date, l_param_start_date) Loop
1650 Exit when csr_contract%notfound;
1651 if l_first_row = TRUE THEN
1652 -- record the first row for emp cat
1653 -- l_hist(l_tbl_count).start_date := greatest(csr_contract_rec.start_date, l_date_start);
1654 -- l_hist(l_tbl_count).start_reason := '021';
1655 l_first_row := FALSE;
1656 -- store the comparison value for emp_cat
1657 l_21_con_old := csr_contract_rec.contract_id;
1658 l_21_con_status := csr_contract_rec.status;
1659 -- IF(l_hist(l_tbl_count).start_date <> l_date_start) Then
1660 -- l_tbl_count := l_tbl_count + 1;
1661 -- End If;
1662 elsif l_21_con_old <> csr_contract_rec.contract_id THEN
1663 begin
1664 select status
1665 into l_21_con_status
1666 from per_contracts_f
1667 where contract_id = l_21_con_old
1668 and csr_contract_rec.start_date between effective_Start_date
1669 and effective_end_Date;
1670 exception
1671 when others then
1672 l_21_con_status := NULL;
1673 end;
1674 IF csr_contract_rec.status <> l_21_con_status
1675 AND csr_contract_rec.start_date >= g_param_start_date THEN
1676 -- the stored value has changed - record this in the history
1677 l_hist(l_tbl_count).start_date := csr_contract_rec.start_date;
1678 l_hist(l_tbl_count).start_reason := '021';
1679 -- and store the new comparison value
1680 l_21_con_old := csr_contract_rec.contract_id;
1681 l_21_con_status := csr_contract_rec.status;
1682 l_tbl_count := l_tbl_count + 1;
1683 ELSIF csr_contract_rec.status <> l_21_con_status
1684 AND csr_contract_rec.start_date < g_param_start_date THEN
1685 -- and store the new comparison value
1686 l_21_con_old := csr_contract_rec.contract_id;
1687 end if;
1688 END IF;
1689 End loop;
1690
1691 l_first_row := TRUE;
1692 IF l_act_dt is not null and l_act_dt <= g_effective_date then
1693 c_end_date := l_act_dt;
1694 else
1695 c_end_date := g_effective_date;
1696 end if;
1697 For csr_asg_cat_rec in csr_asg_cat (c_end_date, l_param_start_date) Loop
1698 Exit when csr_asg_cat%notfound;
1699 IF csr_asg_cat_rec.category IS NOT NULL THEN
1700 BEGIN
1701 l_cipdz_value := hruserdt.get_table_value (g_param_business_group_id,
1702 'FR_CIPDZ',
1703 'CIPDZ' ,
1704 csr_asg_cat_rec.category,
1705 csr_asg_cat_rec.asg_end_date);
1706 EXCEPTION
1707 WHEN OTHERS THEN
1708 l_cipdz_value := NULL;
1709 hr_utility.set_location('obtaining cipdz value Failed:'||sqlerrm,50);
1710 END;
1711 ELSE
1712 l_cipdz_value := NULL;
1713 END IF;
1714 -- Since we are taking the previous values, we don't add the first record of the cursor into the pl/sql table
1715 if l_first_row = TRUE THEN
1716 -- record the first row for emp cat
1717 -- If (csr_asg_cat_rec.category <> 'P') Then
1718 -- l_hist(l_tbl_count).start_date := greatest(csr_asg_cat_rec.start_date,l_date_start);
1719 -- Else
1720 -- l_hist(l_tbl_count).start_date := greatest(csr_asg_cat_rec.asg_start_date,l_date_start);
1721 -- End If;
1722
1723 -- l_hist(l_tbl_count).start_reason := '021';
1724 l_first_row := FALSE;
1725 -- store the comparison value for emp_cat
1726 l_21_cat_old := csr_asg_cat_rec.category;
1727 l_21_cipdz_old := l_cipdz_value;
1728 -- l_tbl_count := l_tbl_count + 1;
1729 -- Since assignment category code is null, it is also added inthe if clause
1730 -- When there is change from any other value other than p, then no run proration occurs.
1731 -- Hence, we use payroll month's start date
1732 Else
1733 -- When there is change from any other value to P or from P to any other value, then run proration occurs.
1734 -- Hence, we use assignment start date.
1735 if ((l_21_cat_old <> csr_asg_cat_rec.category
1736 OR (l_21_cat_old IS NULL AND csr_asg_cat_rec.category IS NOT NULL)
1737 OR (l_21_cat_old IS NOT NULL AND csr_asg_cat_rec.category IS NULL)
1738 )
1739 And (substr(l_21_cipdz_old, 1, 1) = 'P'
1740 Or substr(l_cipdz_value, 1, 1) = 'P')
1741 And csr_asg_cat_rec.asg_start_date >= g_param_start_date) THEN
1742 -- the stored value has changed - record this in the history
1743 l_hist(l_tbl_count).start_date := greatest(csr_asg_cat_rec.asg_start_date,l_date_start);
1744 l_hist(l_tbl_count).start_reason := '021';
1745 -- and store the new comparison value
1746 l_21_cat_old := csr_asg_cat_rec.category;
1747 l_21_cipdz_old := l_cipdz_value;
1748 l_tbl_count := l_tbl_count + 1;
1749 elsif ((l_21_cat_old <> csr_asg_cat_rec.category
1750 OR (l_21_cat_old IS NULL AND csr_asg_cat_rec.category IS NOT NULL)
1751 OR (l_21_cat_old IS NOT NULL AND csr_asg_cat_rec.category IS NULL)
1752 )
1753 And (substr(l_21_cipdz_old, 1, 1) <> 'P'
1754 Or substr(l_cipdz_value, 1, 1) <> 'P')
1755 And csr_asg_cat_rec.asg_start_date >= g_param_start_date) THEN
1756 -- the stored value has changed - record this in the history
1757 l_hist(l_tbl_count).start_date := greatest(csr_asg_cat_rec.start_date,l_date_start);
1758 l_hist(l_tbl_count).start_reason := '021';
1759 l_tbl_count := l_tbl_count + 1;
1760 l_21_cat_old := csr_asg_cat_rec.category;
1761 l_21_cipdz_old := l_cipdz_value;
1762 -- If the start date is before dads period start date, it is just replaced with the old value
1763 elsif ((l_21_cat_old <> csr_asg_cat_rec.category
1764 OR (l_21_cat_old IS NULL AND csr_asg_cat_rec.category IS NOT NULL)
1765 OR (l_21_cat_old IS NOT NULL AND csr_asg_cat_rec.category IS NULL))
1766 and csr_asg_cat_rec.asg_start_date < g_param_start_date) THEN
1767 l_21_cat_old := csr_asg_cat_rec.category;
1768 l_21_cipdz_old := l_cipdz_value;
1769 end if;
1770 End if;
1771 End loop;
1772 --
1773
1774 -- Overwrite with 019 (mutation in another estab in the company)
1775 --
1776
1777 BEGIN
1778 open csr_19(p_person_id);
1779 l_old_coy := null;
1780 l_old_est := null;
1781 LOOP
1782 fetch csr_19 into l_csr_19_rec;
1783 exit when csr_19%NOTFOUND;
1784 if l_csr_19_rec.type = 'FIRST' THEN
1785 -- record the company and estab the person was in before the date range
1786 -- this may not exist
1787 l_old_coy := l_csr_19_rec.company_id;
1788 l_old_est := l_csr_19_rec.establishment_id;
1789 elsif l_old_coy is null THEN
1790 -- the asg joined on or after the start date - this is a csr_001 type
1791 -- record the new company and estab and continue
1792 l_old_coy := l_csr_19_rec.company_id;
1793 l_old_est := l_csr_19_rec.establishment_id;
1794 elsif l_old_coy = l_csr_19_rec.company_id and l_old_est <> l_csr_19_rec.establishment_id
1795 THEN
1796 -- the coy has not changed, the estab has, this is a type 19
1797 l_hist(l_tbl_count).start_date := l_csr_19_rec.start_date;
1798 l_hist(l_tbl_count).start_reason := '019';
1799 -- and store the new comparison value
1800 l_old_est := l_csr_19_rec.establishment_id;
1801 l_tbl_count := l_tbl_count + 1;
1802 else -- the company has changed -
1803 l_old_coy := l_csr_19_rec.company_id;
1804 l_old_est := l_csr_19_rec.establishment_id;
1805 end if;
1806 END LOOP;
1807 EXCEPTION
1808 when others then null;
1809 END;
1810 Close csr_19;
1811
1812 If l_date_start >= g_param_start_date Then
1813 l_hist(l_tbl_count).start_date := l_date_start;
1814 l_hist(l_tbl_count).start_reason := '001';
1815 l_tbl_count := l_tbl_count + 1;
1816 hr_utility.set_location('S41 g_param_start_date 01:'||g_param_start_date,10);
1817 End if;
1818
1819 -- First the bubble sorting is done. Then the termination details are added to the last pl/sql record.
1820 hr_utility.set_location('Before Bubble sort' ,8888);
1821
1822 For i in l_hist.first..l_hist.last
1823 Loop
1824 If l_hist.exists(i) Then
1825 hr_utility.set_location('l_hist(i).start_date: '||l_hist(i).start_date,99);
1826 hr_utility.set_location('l_hist(i).start_reason: '||l_hist(i).start_reason,99);
1827 end if;
1828 End LOOP;
1829
1830 --Bubble sort for all the start dates collected from the cursors above....
1831 Begin
1832 For i in l_hist.first..l_hist.last
1833 Loop
1834 For j in l_hist.first..(l_hist.last - i)
1835 Loop
1836 k := l_hist.Next(j);
1837 If (l_hist(k).start_date < l_hist(j).start_date) Then
1838 tmp_start_date := l_hist(j).start_date;
1839 tmp_start_reason := l_hist(j).start_reason;
1840 l_hist(j).start_date := l_hist(k).start_date;
1841 l_hist(j).start_reason := l_hist(k).start_reason;
1842 l_hist(k).start_date := tmp_start_date;
1843 l_hist(k).start_reason := tmp_start_reason;
1844
1845 tmp_end_date := l_hist(j).end_date;
1846 tmp_end_reason := l_hist(j).end_reason;
1847 l_hist(j).end_date := l_hist(k).end_date;
1848 l_hist(j).end_reason := l_hist(k).end_reason;
1849 l_hist(k).end_date := tmp_end_date;
1850 l_hist(k).end_reason := tmp_end_reason;
1851 End If;
1852 End Loop;
1853 End Loop;
1854 Exception
1855 When Others Then
1856 hr_utility.set_location('SORT LOOP:'|| sqlerrm,8888);
1857 End;
1858
1859 -- adjust for termination and other reasons
1860 -- Use l_leav_reason for leaving reason and l_act_date for actual termination date
1861 -- retrived in query for 95 and 97
1862 --
1863 hr_utility.set_location('Fetching termination details' ,8888);
1864 -- l_leav_reason is not a mandatory field. Hence, leave reason not null check is taken off and actual term. date check is kept
1865 If l_act_dt Is Not Null and l_act_dt <= g_effective_date Then
1866 l_counter := l_hist.LAST;
1867 -- Flag is used to check whether the termination details are added to the pl/sql table or not
1868 l_flag := TRUE;
1869 l_tbl_count := l_hist.PRIOR(l_counter);
1870 while l_flag = TRUE AND l_hist.EXISTS(l_counter)
1871 LOOP
1872 IF l_hist.EXISTS(l_tbl_count) THEN
1873 IF l_hist(l_counter).start_date = l_hist(l_tbl_count).start_date THEN
1874 IF (l_hist(l_tbl_count).start_reason <= l_hist(l_counter).start_reason) THEN
1875 l_counter := l_tbl_count;
1876 END IF;
1877 l_tbl_count := l_hist.PRIOR(l_tbl_count);
1878 ELSE
1879 l_hist(l_counter).end_date := l_act_dt;
1880 l_hist(l_counter).end_reason := l_leav_reason;
1881 l_flag := FALSE;
1882 END IF;
1883 ELSE
1884 l_hist(l_counter).end_date := l_act_dt;
1885 l_hist(l_counter).end_reason := l_leav_reason;
1886 l_flag := FALSE;
1887 END IF;
1888 END LOOP;
1889 --
1890 End If;
1891 --
1892 --First delete the lower priority records and then add the end date and end reasons to the records
1893 BEGIN
1894 l_counter := l_hist.LAST;
1895 l_counter := l_hist.PRIOR(l_counter);
1896 While l_counter is not null
1897 LOOP
1898 IF l_hist.EXISTS(l_counter) THEN
1899 If(l_hist.exists(l_hist.next(l_counter))) Then
1900 If (l_hist(l_counter).start_date = l_hist(l_hist.Next(l_counter)).start_date) Then
1901 hr_utility.set_location('ADJUST_HISTORY :'||l_hist(l_counter).start_reason||':'||l_hist(l_hist.Next(l_counter)).start_reason ,5555);
1902 If (l_hist(l_counter).start_reason > l_hist(l_hist.Next(l_counter)).start_reason) Then
1903 l_hist.Delete(l_counter);
1904 hr_utility.set_location('ADJUST_HISTORY DELETED:'||l_counter,6666);
1905 ElsIf (l_hist(l_counter).start_reason <= l_hist(l_hist.Next(l_counter)).start_reason) Then
1906 l_hist.Delete(l_hist.Next(l_counter));
1907 hr_utility.set_location('ADJUST_HISTORY DELETED 1:'||l_counter,6666);
1908 End If;
1909 End If;
1910 End if;
1911 END IF;
1912 l_counter := l_hist.PRIOR(l_counter);
1913 END LOOP;
1914 Exception
1915 When Others Then
1916 hr_utility.set_location('DELETING LOWER PRIORITY RECORDS:'|| sqlerrm,8888);
1917 End;
1918 --
1919 --
1920 --
1921 -- adjust the history...set the end dates for the reasons which are other than
1922 -- termination
1923 hr_utility.set_location('After Bubble sort' ,8888);
1924 Begin
1925 l_counter := l_hist.LAST;
1926 While l_counter is not null
1927 -- This ending reason is related to the next starting reason
1928 LOOP
1929 If (l_hist(l_counter).end_date Is Null) Then
1930 If l_hist.Exists(l_hist.NEXT(l_counter)) Then
1931 If (l_hist(l_hist.NEXT(l_counter)).start_date <> l_date_start) Then
1932 l_hist(l_counter).end_date := l_hist(l_hist.NEXT(l_counter)).start_date - 1;
1933 If ((l_hist(l_counter).end_date < l_hist(l_counter).start_date) Or
1934 (l_hist(l_counter).end_date < g_param_start_date)
1935 ) Then
1936 l_hist(l_counter).end_date := g_effective_date;
1937 End if;
1938 l_hist(l_counter).end_reason := '0'||to_char(l_hist(l_hist.NEXT(l_counter)).start_reason +1);
1939 End if;
1940 Else
1941 l_hist(l_counter).end_date := g_effective_date;
1942 l_hist(l_counter).end_reason := '098';
1943 End If;
1944 End If;
1945 l_counter := l_hist.PRIOR(l_counter);
1946 END LOOP;
1947 Exception
1948 When Others Then
1949 hr_utility.set_location('ADJUST_HISTORY:'|| sqlerrm,8888);
1950 End;
1951
1952 hr_utility.set_location('After History Adjust' ,8888);
1953 --Delete duplicate periods... The Duplication occurs if there are more than one
1954 --Reason code on a given day...Reason code which is lesser takes precedence.
1955 Begin
1956 l_counter := l_hist.LAST;
1957 -- The last record should be deleted if it satisfies the given conditions
1958 IF l_hist.EXISTS(l_counter) THEN
1959 If ((l_hist(l_counter).start_date < l_date_start) Or
1960 (l_hist(l_counter).end_date > g_effective_date) Or
1961 (l_hist(l_counter).end_date is NUll) Or
1962 (l_hist(l_counter).end_date <= l_hist(l_counter).start_date) Or
1963 (l_hist(l_counter).end_date <= g_param_start_date)
1964 ) Then
1965 l_hist.Delete(l_counter);
1966 hr_utility.set_location('ADJUST_HISTORY deleting anamolous records:'||l_counter,5555);
1967 end if;
1968 END IF;
1969
1970 l_counter := l_hist.PRIOR(l_counter);
1971 While l_counter is not null
1972 LOOP
1973 If ((l_hist(l_counter).start_date < l_date_start) Or
1974 (l_hist(l_counter).end_date > g_effective_date) Or
1975 (l_hist(l_counter).end_date is NUll) Or
1976 (l_hist(l_counter).end_date <= l_hist(l_counter).start_date) Or
1977 (l_hist(l_counter).end_date <= g_param_start_date)
1978 ) Then
1979 l_hist.Delete(l_counter);
1980 hr_utility.set_location('ADJUST_HISTORY deleting anamolous records:'||l_counter,5555);
1981 end if;
1982 l_counter := l_hist.PRIOR(l_counter);
1983 END LOOP;
1984
1985 Exception
1986 When Others Then
1987 hr_utility.set_location('DELETE WHERE END DATE IS NOT PROPER:'|| sqlerrm,8888);
1988 End;
1989
1990 -- Checking for Isolated sums
1991 If l_final_date >= g_param_start_date Then
1992 l_month := to_number(to_char(g_param_start_date, 'mm'));
1993 l_year := to_number(to_char(g_param_start_date, 'yyyy'));
1994 l_bal_start_date := to_date('01-' ||l_month ||'-'||l_year || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
1995 l_unused_number := 0;
1996 l_unused_number := fr_rolling_balance_pro (p_assignment_id => l_assignment_id,
1997 p_balance_name => 'FR_ISOLATED_SUMS',
1998 p_balance_start_date => l_bal_start_date,
1999 p_balance_end_date => g_effective_date);
2000 If l_unused_number > 0 Then
2001 l_tbl_count := l_hist.LAST;
2002 IF l_tbl_count IS NULL THEN
2003 l_tbl_count := 1;
2004 ELSE
2005 l_tbl_count := l_tbl_count + 1;
2006 END IF;
2007
2008 l_year := to_number(to_char(g_param_start_date, 'yyyy'));
2009 l_hist(l_tbl_count).start_date := to_date('01-01-'||l_year || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
2010 l_hist(l_tbl_count).start_reason := '095';
2011 -- As per functional doc. end date should also be 0101
2012 l_hist(l_tbl_count).end_date := to_date('01-01-'||l_year || ' 00:00:00', 'dd-mm-yyyy hh24:mi:ss');
2013 l_hist(l_tbl_count).end_reason := '096';
2014 l_tbl_count := l_tbl_count + 1;
2015 hr_utility.set_location('S41 g_param_start_date 01:'||g_param_start_date,10);
2016 End If;
2017 End If;
2018
2019 --
2020 --
2021 -- Getting the error messages
2022 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
2023 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
2024
2025 --
2026 hr_utility.set_location('L_HIST COUNT:'||l_hist.count, 117);
2027
2028 For i in l_hist.first..l_hist.last
2029 Loop
2030 If l_hist.exists(i) Then
2031 hr_utility.set_location('l_hist(i).start_date: '||l_hist(i).start_date,99);
2032 hr_utility.set_location('l_hist(i).start_reason: '||l_hist(i).start_reason,99);
2033 hr_utility.set_location('l_hist(i).end_date: '||l_hist(i).end_date,99);
2034 hr_utility.set_location('l_hist(i).end_reason: '||l_hist(i).end_reason,99);
2035 end if;
2036 End LOOP;
2037
2038 hr_utility.set_location('Before looping in S41 DATA' ,8888);
2039 l_counter := l_hist.FIRST;
2040 l_id2_num := 0;
2041 Begin
2042 While l_counter is not null
2043 LOOP
2044 If l_hist.exists(l_counter) Then
2045 --Need to be edited for exceptions report validation
2046 --Start of the Period for the Situation
2047 l_id2_num := l_id2_num + 1;
2048 l_id2 := p_org_id||l_id2_num;
2049
2050 IF l_hist(l_counter).start_reason = '095'
2051 and l_act_dt >= g_param_start_date THEN
2052 l_param_start_date := l_act_dt;
2053 ELSIF l_hist(l_counter).start_reason = '095'
2054 and l_act_dt < g_param_start_date THEN
2055 l_param_start_date := g_param_start_date;
2056 ELSIF (l_hist(l_counter).start_date Is Not NUll And
2057 l_hist(l_counter).start_date >= g_param_start_date) Then
2058 l_param_start_date := l_hist(l_counter).start_date;
2059 ElsIF l_hist(l_counter).start_date Is NUll Then
2060 l_param_start_date := g_param_start_date;
2061 l_hist(l_counter).start_date := g_param_start_date;
2062 End If;
2063
2064 IF l_hist(l_counter).end_reason = '096' THEN
2065 l_effective_date := g_effective_date;
2066 ELSIF (l_hist(l_counter).end_date Is Not NUll And
2067 l_hist(l_counter).end_date <= g_effective_date) Then
2068 l_effective_date := l_hist(l_counter).end_date;
2069 ElsIF (l_hist(l_counter).end_date Is NUll) Then
2070 l_effective_date := g_effective_date;
2071 l_hist(l_counter).end_date := g_effective_date;
2072 End If;
2073
2074 IF l_hist(l_counter).start_date is null THEN
2075 l_error_type := l_error;
2076 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2077 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.001'),
2078 null, null);
2079 ELSE
2080 l_error_type := null;
2081 l_value := null;
2082 END IF;
2083
2084 archive_data( p_rubric_name => 'S41.G01.00.001'
2085 ,p_message_type => l_error_type
2086 ,p_id => p_org_id
2087 ,p_lookup_type => Null
2088 ,p_file_value => to_char(l_hist(l_counter).start_date,'DDMM')
2089 ,p_message_text => l_value
2090 ,p_id2 => l_id2
2091 ,p_rubric_type => l_mandatory);
2092
2093 -- Reason Code for Start Period
2094 IF l_hist(l_counter).start_reason is null THEN
2095 l_error_type := l_error;
2096 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2097 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.002'),
2098 null, null);
2099 ELSE
2100 l_error_type := null;
2101 l_value := null;
2102 END IF;
2103
2104 archive_data( p_rubric_name => 'S41.G01.00.002'
2105 ,p_message_type => l_error_type
2106 ,p_id => p_org_id
2107 ,p_lookup_type => 'FR_DADS_START_REASON'
2108 ,p_file_value => l_hist(l_counter).start_reason
2109 ,p_message_text => l_value
2110 ,p_id2 => l_id2
2111 ,p_rubric_type => l_mandatory);
2112
2113 --End of the Period for the Situation
2114 IF l_hist(l_counter).start_date is null THEN
2115 l_error_type := l_error;
2116 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2117 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.003'),
2118 null, null);
2119 ELSE
2120 l_error_type := null;
2121 l_value := null;
2122 END IF;
2123 archive_data( p_rubric_name => 'S41.G01.00.003'
2124 ,p_message_type => l_error_type
2125 ,p_id => p_org_id
2126 ,p_lookup_type => Null
2127 ,p_file_value => to_char(l_hist(l_counter).end_date,'DDMM')
2128 ,p_message_text => l_value
2129 ,p_id2 => l_id2
2130 ,p_rubric_type => l_mandatory);
2131
2132 -- Reason Code for End Period
2133 IF l_hist(l_counter).end_reason is null THEN
2134 l_error_type := l_error;
2135 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2136 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.004'),
2137 null, null);
2138 ELSE
2139 l_error_type := null;
2140 l_value := null;
2141 END IF;
2142 archive_data( p_rubric_name => 'S41.G01.00.004'
2143 ,p_message_type => l_error_type
2144 ,p_id => p_org_id
2145 ,p_lookup_type => 'FR_DADS_END_REASON'
2146 ,p_file_value => l_hist(l_counter).end_reason
2147 ,p_message_text => l_value
2148 ,p_id2 => l_id2
2149 ,p_rubric_type => l_mandatory);
2150
2151 --
2152
2153 hr_utility.set_location('S41 csr41_estab_data:'||p_person_id,110);
2154 --Fetch the Data for Estab. NIC and Work Accident related data.
2155 --Need to be edited for exceptions report validation
2156 Begin
2157 For l_csr41_estab_data In csr41_estab_data(l_effective_date)
2158 Loop
2159 hr_utility.set_location('S41 person_id:'||p_person_id,113);
2160 If l_csr41_estab_data.asg_location Is not Null Then
2161 l_asg_location := l_csr41_estab_data.asg_location;
2162 End If;
2163
2164 If l_csr41_estab_data.information_Context = 'FR_ESTAB_INFO' THEN
2165 l_siret_number := l_csr41_estab_data.siret_number ;
2166 l_estab_id := l_csr41_estab_data.estab_id;
2167 l_estab_monthly_hours := l_csr41_estab_data.risk_code_month_hours;
2168 l_asg_estab_name := l_csr41_estab_data.asg_estab_name;
2169 l_issue_estab_name := l_csr41_estab_data.issue_estab_name;
2170 l_est_location := l_csr41_estab_data.est_location;
2171 l_location_name := l_csr41_estab_data.location_name;
2172 Elsif l_csr41_estab_data.information_Context = 'FR_ESTAB_WORK_ACCIDENT' THEN
2173 l_work_hr_org := l_csr41_estab_data.hrorg;
2174 -- #3553829 Assigning the value to a temporary variable
2175 l_temp_work_risk_code := l_csr41_estab_data.risk_code_month_hours;
2176 l_work_order_number :=l_csr41_estab_data.order_number;
2177 l_work_section_code := l_csr41_estab_data.section_code;
2178 l_work_office_code := l_csr41_estab_data.office_code;
2179 End if;
2180
2181 If (l_work_hr_org Is not Null) Then
2182 l_work_risk_code := l_csr41_estab_data.risk_code_month_hours;
2183 End If;
2184
2185 End Loop;
2186 Exception
2187 When Others then
2188 hr_utility.set_location('S41 csr41_estab_data Failed:'||sqlerrm,110);
2189 End;
2190
2191 -- #3553829 If l_work_risk_code does not have a value then, assign the work risk code value that has been stored in temp variable
2192 IF l_work_risk_code IS NULL THEN
2193 l_work_risk_code := l_temp_work_risk_code;
2194 END IF;
2195
2196 hr_utility.set_location('S41 g_param_business_group_id:'||g_param_business_group_id,100);
2197 hr_utility.set_location('S41 l_est_location:'||l_est_location,101);
2198 hr_utility.set_location('S41 l_asg_location:'||l_asg_location,102);
2199 hr_utility.set_location('S41 l_issue_estab_name :'||l_issue_estab_name ,103);
2200 hr_utility.set_location('S41 l_location_name :'||l_location_name ,104);
2201 hr_utility.set_location('S41 l_asg_estab_name :'||l_asg_estab_name ,105);
2202 hr_utility.set_location('S41 l_siret_number :'||l_siret_number,109);
2203 hr_utility.set_location('S41 l_estab_id:'||l_estab_id,108);
2204 hr_utility.set_location('S41 l_estab_monthly_hours:'||l_estab_monthly_hours,107);
2205 hr_utility.set_location('S41 l_work_hr_org:'||l_work_hr_org,106);
2206 hr_utility.set_location('S41 l_work_risk_code:'||l_work_risk_code,110);
2207 hr_utility.set_location('S41 l_work_order_number:'||l_work_order_number,111);
2208 hr_utility.set_location('S41 l_work_section_code:'||l_work_section_code,112);
2209 hr_utility.set_location('S41 l_work_office_code:'||l_work_office_code,113);
2210 hr_utility.set_location('S41 l_work_risk_code:'||l_work_risk_code,114);
2211
2212 begin
2213 --Bug 3756137
2214 If l_work_risk_code = '99999' then
2215 l_work_acc_rate := l_work_risk_code;
2216 --Bug 3756137
2217 elsif l_work_risk_code Is not NULl then
2218 l_work_acc_rate := hruserdt.get_table_value (g_param_business_group_id,
2219 'FR_WORK_ACCIDENT_RATES',
2220 'RATE' ,
2221 l_work_risk_code,
2222 l_effective_date);
2223 End If;
2224 Exception
2225 When Others then
2226 hr_utility.set_location('S41 FR_WORK_ACCIDENT_RATES Failed:'||sqlerrm,110);
2227 end;
2228
2229 -- NIC of the Assignment Establishment
2230 hr_utility.set_location('S41 out of the fitst cursor',114);
2231 IF l_siret_number is null THEN
2232 l_error_type := l_error;
2233 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2234 'VALUE1:'||l_issue_estab_name, 'VALUE2:'||hr_general.decode_lookup
2235 ('FR_DADS_RUBRICS','S41.G01.00.005'), null);
2236 ELSE
2237 l_error_type := null;
2238 l_value := null;
2239 END IF;
2240 hr_utility.set_location('S41 l_siret_number:'||l_siret_number,112);
2241
2242 g_param_estab_id := l_estab_id;
2243
2244 archive_data( p_rubric_name => 'S41.G01.00.005'
2245 ,p_message_type => l_error_type
2246 ,p_id => p_org_id
2247 ,p_lookup_type => Null
2248 ,p_file_value => l_siret_number
2249 ,p_message_text => l_value
2250 ,p_id2 => l_id2
2251 ,p_rubric_type => l_mandatory);
2252
2253 -- Work Accident Section Code
2254 IF l_work_section_code is null THEN
2255 l_error_type := l_error;
2256 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2257 'VALUE1:'||l_issue_estab_name, 'VALUE2:'||hr_general.decode_lookup
2258 ('FR_DADS_RUBRICS','S41.G01.00.025'), null);
2259 ELSIF l_work_section_code = '98' THEN
2260 l_error_type := l_error;
2261 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
2262 'VALUE1:'|| hr_general.decode_lookup
2263 ('FR_DADS_RUBRICS','S41.G01.00.025'), 'VALUE2:'||hr_general.decode_lookup
2264 ('FR_DADS_RUBRICS','S41.G01.00.025.M'), null);
2265 ELSE
2266 l_error_type := null;
2267 l_value := null;
2268 END IF;
2269 hr_utility.set_location('S41 l_work_section_code:'||l_work_section_code,112);
2270 archive_data( p_rubric_name => 'S41.G01.00.025'
2271 ,p_message_type => l_error_type
2272 ,p_id => p_org_id
2273 ,p_lookup_type => Null
2274 ,p_file_value => l_work_section_code
2275 ,p_message_text => l_value
2276 ,p_id2 => l_id2
2277 ,p_rubric_type => l_mandatory);
2278
2279 -- Work Accident Risk Code
2280 IF l_work_risk_code is null THEN
2281 l_error_type := l_error;
2282 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2283 'VALUE1:'||l_issue_estab_name, 'VALUE2:'||hr_general.decode_lookup
2284 ('FR_DADS_RUBRICS','S41.G01.00.026'), null);
2285 -- Bug #3756137
2286 ELSIF length(l_work_risk_code) > 5 then
2287 l_error_type := l_warning;
2288 l_value := pay_fr_general.get_payroll_message('PAY_75095_DADS_WORK_ACC_CODE1',
2289 'VALUE1:'|| l_work_risk_code, null, null);
2290 l_work_risk_code := substr(l_work_risk_code, 1, 5);
2291 ELSIF length(l_work_risk_code) < 5 then
2292 l_error_type := l_warning;
2293 l_value := pay_fr_general.get_payroll_message('PAY_75096_DADS_WORK_ACC_CODE2',
2294 'VALUE1:'|| l_work_risk_code, null, null);
2295 -- Bug #3756137
2296 ELSIF l_work_risk_code = '98888' THEN
2297 l_error_type := l_error;
2298 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
2299 'VALUE1:'|| hr_general.decode_lookup
2300 ('FR_DADS_RUBRICS','S41.G01.00.026'), 'VALUE2:'||hr_general.decode_lookup
2301 ('FR_DADS_RUBRICS','S41.G01.00.025.M'), null);
2302 ELSE
2303 l_error_type := null;
2304 l_value := null;
2305 END IF;
2306 begin
2307 -- Bug #3756137
2308 if l_work_risk_code in ('98888', '99999') then
2309 archive_data( p_rubric_name => 'S41.G01.00.026'
2310 ,p_message_type => l_error_type
2311 ,p_id => p_org_id
2312 ,p_lookup_type => 'FR_DADS_WORK_ACC_RISK_CODE'
2313 ,p_file_value => l_work_risk_code
2314 ,p_message_text => l_value
2315 ,p_id2 => l_id2
2316 ,p_rubric_type => l_mandatory);
2317 else
2318 archive_data( p_rubric_name => 'S41.G01.00.026'
2319 ,p_message_type => l_error_type
2320 ,p_id => p_org_id
2321 ,p_lookup_type => 'FR_WORK_ACCIDENT_RISK_CODE'
2322 ,p_file_value => l_work_risk_code
2323 ,p_message_text => l_value
2324 ,p_id2 => l_id2
2325 ,p_rubric_type => l_mandatory);
2326 end if;
2327 -- Bug #3756137
2328 Exception
2329 When Others then
2330 hr_utility.set_location('S41 FR_WORK_ACCIDENT_RISK_CODE Failed:'||sqlerrm,1112);
2331 end;
2332
2333 -- Office Work Accident Code
2334 IF l_work_office_code is not null AND l_work_office_code <> 'B' THEN
2335 l_error_type := l_error;
2336 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
2337 'VALUE1:'||l_issue_estab_name, 'VALUE2:'||hr_general.decode_lookup
2338 ('FR_DADS_RUBRICS','S41.G01.00.027.M'), null);
2339 ELSE
2340 l_error_type := null;
2341 l_value := null;
2342 END IF;
2343
2344 Begin
2345 archive_data( p_rubric_name => 'S41.G01.00.027'
2346 ,p_message_type => l_error_type
2347 ,p_id => p_org_id
2348 ,p_lookup_type => 'FR_DADS_OFFICE_WA_CODE'
2349 ,p_file_value => l_work_office_code
2350 ,p_message_text => l_value
2351 ,p_id2 => l_id2
2352 ,p_rubric_type => l_conditional);
2353
2354 Exception
2355 When Others then
2356 hr_utility.set_location('S41 FR_DADS_OFFICE_WA_CODE Failed:'||sqlerrm,1112);
2357 end;
2358
2359 -- Work Accident Rate
2360 IF l_work_acc_rate is null THEN
2361 l_error_type := l_error;
2362 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2363 'VALUE1:'||l_issue_estab_name, 'VALUE2:'||hr_general.decode_lookup
2364 ('FR_DADS_RUBRICS','S41.G01.00.028'), null);
2365 ELSE
2366 l_error_type := null;
2367 l_value := null;
2368 END IF;
2369 archive_data( p_rubric_name => 'S41.G01.00.028'
2370 ,p_message_type => l_error_type
2371 ,p_id => p_org_id
2372 ,p_lookup_type => NULL
2373 ,p_file_value => l_work_acc_rate
2374 ,p_message_text => l_value
2375 ,p_id2 => l_id2
2376 ,p_rubric_type => l_mandatory);
2377
2378 --
2379 --Query to fetch code for Multiple employers
2380 -- Query for multiple employers
2381 --Item:S41.G01.00.008
2382 For get_emp_rec in get_multi_emp(l_effective_date)
2383 Loop
2384 l_element_entry_id := get_emp_rec.element_entry_id;
2385 End Loop;
2386
2387 If l_element_entry_id Is Not Null Then
2388 l_multi_employr_code := '02';
2389 Else
2390 l_multi_employr_code := '03';
2391 End If;
2392 --Need to be edited for exceptions report validation
2393 -- Multiple Employers Code
2394 IF l_multi_employr_code is null THEN
2395 l_error_type := l_error;
2396 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2397 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.008'),
2398 null, null);
2399 ELSE
2400 l_error_type := null;
2401 l_value := null;
2402 END IF;
2403 Begin
2404
2405 archive_data( p_rubric_name => 'S41.G01.00.008'
2406 ,p_message_type => l_error_type
2407 ,p_id => p_org_id
2408 ,p_lookup_type => 'FR_DADS_MULTIPLE_EMPLOYER_CODE'
2409 ,p_file_value => l_multi_employr_code
2410 ,p_message_text => l_value
2411 ,p_id2 => l_id2
2412 ,p_rubric_type => l_mandatory);
2413 Exception
2414 When Others then
2415 hr_utility.set_location('S41 FR_DADS_MULTIPLE_EMPLOYER_CODE Failed:'||sqlerrm,1112);
2416 end;
2417
2418 --
2419 -- Positive Offset
2420 --Item:S41.G01.00.009
2421 l_positive_offset := '01';
2422 IF l_positive_offset is null THEN
2423 l_error_type := l_error;
2424 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2425 'VALUE1:'||hr_general.decode_lookup('FR_DADS_RUBRICS','S41.G01.00.009'),
2426 null, null);
2427 ELSE
2428 l_error_type := null;
2429 l_value := null;
2430 END IF;
2431
2432 begin
2433 archive_data( p_rubric_name => 'S41.G01.00.009'
2434 ,p_message_type => l_error_type
2435 ,p_id => p_org_id
2436 ,p_lookup_type => 'FR_DADS_POSITIVE_OFFSET'
2437 ,p_file_value => l_positive_offset
2438 ,p_message_text => l_value
2439 ,p_id2 => l_id2
2440 ,p_rubric_type => l_mandatory);
2441
2442 Exception
2443 When Others then
2444 hr_utility.set_location('S41 FR_DADS_POSITIVE_OFFSET Failed:'||sqlerrm,1112);
2445 end;
2446 hr_utility.set_location('S41 FR_DADS_POSITIVE_OFFSET Inserted',113);
2447 --
2448 --Job Nature and INSCEE PCS Code
2449 --Item:S41.G01.00.010 --following the same as Assedic Attestation Report
2450 --Item:S41.G01.00.011 --Open Issue, Now following the same as Assedic Attestation Report
2451 --Work Contract Code and Professtional Status Code
2452 --Item:S41.G01.00.012
2453 --Item:S41.G01.00.014
2454 --Employee Number
2455 --Item:S41.G01.00.019
2456 l_first_row := True;
2457 Begin
2458 For l_date_trk_41_rec In fetch_date_trk_41(l_effective_date)
2459 Loop
2460 Exit when fetch_date_trk_41%NotFound;
2461 If l_first_row = True Then
2462 l_job_name := l_date_trk_41_rec.job_name ;
2463 /* Bug #3815632 */
2464 l_job_id := l_date_trk_41_rec.job_id;
2465 l_job_definition_id := l_date_trk_41_rec.job_definition_id;
2466 if l_job_definition_id is not null then
2467 l_job_name := per_fr_report_utilities.get_job_names
2468 (p_job_id => l_job_id,
2469 p_job_definition_id => l_job_definition_id,
2470 p_report_name => 'DADS');
2471 end if;
2472 /* Bug #3815632 */
2473 l_pcs_code := l_date_trk_41_rec.pcs_code;
2474 l_contract_type := l_date_trk_41_rec.contract_type;
2475 --
2476 l_contract_type_1 := l_date_trk_41_rec.contract_type_1;
2477 --
2478 l_prof_code := l_date_trk_41_rec.prof_status_code;
2479 l_border_worker := l_date_trk_41_rec.border_worker;
2480 l_detache := l_date_trk_41_rec.detache;
2481 l_act_type := l_date_trk_41_rec.act_type;
2482 l_person_number := l_date_trk_41_rec.person_number;
2483 -- modified for time analysis
2484 l_norm_hours := l_date_trk_41_rec.norm_asg_hours;
2485 l_frequency := l_date_trk_41_rec.asg_frequency;
2486 l_ctr_frequency := l_date_trk_41_rec.ctr_frequency;
2487 l_norm_ctr_hours := l_date_trk_41_rec.norm_ctr_hours;
2488 l_ctr_units := l_date_trk_41_rec.ctr_units;
2489 --
2490 l_employment_cat := l_date_trk_41_rec.emp_cat;
2491 l_emp_full_name := l_date_trk_41_rec.name;
2492 l_col_aggr_code := l_date_trk_41_rec.col_aggr_code;
2493 l_first_row := False;
2494 End if;
2495 End loop;
2496 Exception
2497 When Others Then
2498 hr_utility.set_location('S41 fetch_date_trk_41 Failed:'||sqlerrm,11011);
2499 End;
2500
2501 --Need to be edited for exceptions report validation
2502 -- Job Nature
2503 IF l_job_name is null THEN
2504 l_error_type := l_error;
2505 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2506 'VALUE1:'||l_emp_full_name, 'VALUE2:'||hr_general.decode_lookup
2507 ('FR_DADS_RUBRICS','S41.G01.00.010'), null);
2508 ELSE
2509 l_error_type := null;
2510 l_value := null;
2511 END IF;
2512 archive_data( p_rubric_name => 'S41.G01.00.010'
2513 ,p_message_type => l_error_type
2514 ,p_id => p_org_id
2515 ,p_lookup_type => NULL
2516 ,p_file_value => l_job_name
2517 ,p_message_text => l_value
2518 ,p_id2 => l_id2
2519 ,p_rubric_type => l_mandatory);
2520
2521 hr_utility.set_location('S41 l_job_name:'||l_job_name,113);
2522 -- Category Code
2523 IF l_job_name is not null AND l_pcs_code is null THEN
2524 l_error_type := l_error;
2525 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2526 'VALUE1:'||l_job_name, 'VALUE2:'||hr_general.decode_lookup
2527 ('FR_DADS_RUBRICS','S41.G01.00.011.M'), null);
2528 ELSE
2529 --3311942 call the procedure to get the valid pcs code
2530 per_fr_d2_pkg. get_pcs_code (p_report_qualifier => 'DADS'
2531 ,p_job_name => l_job_name
2532 ,p_pcs_code => l_pcs_code
2533 ,p_effective_date => l_effective_date);
2534 l_error_type := null;
2535 l_value := null;
2536 END IF;
2537 archive_data( p_rubric_name => 'S41.G01.00.011'
2538 ,p_message_type => l_error_type
2539 ,p_id => p_org_id
2540 ,p_lookup_type => NULL
2541 ,p_file_value => l_pcs_code
2542 ,p_message_text => l_value
2543 ,p_id2 => l_id2
2544 ,p_rubric_type => l_mandatory);
2545
2546 hr_utility.set_location('S41 l_pcs_code:'||l_pcs_code,113);
2547 -- Work Contract Code
2548 IF l_contract_type is null THEN
2549 l_error_type := l_error;
2550 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2551 'VALUE1:'||hr_general.decode_lookup
2552 ('FR_DADS_RUBRICS','S41.G01.00.012'), null, null);
2553 ELSE
2554 l_error_type := null;
2555 l_value := null;
2556 END IF;
2557 archive_data( p_rubric_name => 'S41.G01.00.012'
2558 ,p_message_type => l_error_type
2559 ,p_id => p_org_id
2560 ,p_lookup_type => 'FR_DADS_WORK_CONTRACT_CODE'
2561 ,p_file_value => l_contract_type
2562 ,p_message_text => l_value
2563 ,p_id2 => l_id2
2564 ,p_rubric_type => l_mandatory);
2565
2566 hr_utility.set_location('S41 l_contract_type:'||l_contract_type,113);
2567 -- Professional Status Code
2568 IF l_prof_code is null THEN
2569 l_error_type := l_error;
2570 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2571 'VALUE1:'||l_emp_full_name, 'VALUE2:'||hr_general.decode_lookup
2572 ('FR_DADS_RUBRICS','S41.G01.00.014'), null);
2573 ELSE
2574 l_error_type := null;
2575 l_value := null;
2576 END IF;
2577 archive_data( p_rubric_name => 'S41.G01.00.014'
2578 ,p_message_type => l_error_type
2579 ,p_id => p_org_id
2580 ,p_lookup_type => 'FR_DADS_PROF_STATUS_CODE'
2581 ,p_file_value => l_prof_code
2582 ,p_message_text => l_value
2583 ,p_id2 => l_id2
2584 ,p_rubric_type => l_mandatory);
2585
2586 hr_utility.set_location('S41 l_prof_code:'||l_prof_code,113);
2587 archive_data( p_rubric_name => 'S41.G01.00.019'
2588 ,p_message_type => NULL
2589 ,p_id => p_org_id
2590 ,p_lookup_type => Null
2591 ,p_file_value => l_person_number
2592 ,p_message_text => Null
2593 ,p_id2 => l_id2
2594 ,p_rubric_type => l_conditional);
2595
2596 hr_utility.set_location('S41 l_person_number:'||l_person_number,113);
2597 --
2598 l_act_typ_val := NULL;
2599 --Activity Type Code
2600 --Item:S41.G01.00.013
2601 hr_utility.set_location('S41 l_employment_cat:'||l_employment_cat,113);
2602 -- If ((l_employment_cat is not null) and (l_employment_cat <> '06')) Then
2603 If ((l_employment_cat is not null) and (l_contract_type_1 <> 'SEASONAL')) Then
2604 Begin
2605 l_act_typ_val := hruserdt.get_table_value (g_param_business_group_id,
2606 'FR_CIPDZ',
2607 'CIPDZ' ,
2608 l_employment_cat,
2609 l_effective_date);
2610 Exception
2611 When Others then
2612 hr_utility.set_location('S41 l_act_typ_val Failed:'||sqlerrm,1115);
2613 end;
2614
2615
2616 IF substr(l_act_typ_val,1,1) = 'C' Then
2617 l_act_typ_code := '01';
2618 --Bug#3344918
2619 Elsif substr(l_act_typ_val,1,1) = 'P' Then
2620 l_act_typ_code := '02';
2621 Elsif substr(l_act_typ_val,1,1) = 'I' Then
2622 l_act_typ_code := '04';
2623 Elsif substr(l_act_typ_val,1,1) = 'D' Then
2624 l_act_typ_code := '05';
2625 Else
2626 l_act_typ_code := '03';
2627 End If;
2628 -- Elsif l_employment_cat = '06' Then
2629 Elsif l_contract_type_1 = 'SEASONAL' Then
2630 l_act_typ_code := '06';
2631 hr_utility.set_location('S41 l_act_typ_code:'||l_act_typ_code,113);
2632 End If;
2633 -- #3553847 previously the validation was there within the above if statement. Now, it has been taken down.
2634 -- Activity Type Code
2635 IF l_act_typ_code is null THEN
2636 l_error_type := l_error;
2637 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2638 'VALUE1:'||hr_general.decode_lookup
2639 ('FR_DADS_RUBRICS','S41.G01.00.013'), null, null);
2640 ELSE
2641 l_error_type := null;
2642 l_value := null;
2643 END IF;
2644 --
2645
2646 archive_data( p_rubric_name => 'S41.G01.00.013'
2647 ,p_message_type => l_error_type
2648 ,p_id => p_org_id
2649 ,p_lookup_type => 'FR_DADS_ACTIVITY_TYPE_CODE'
2650 ,p_file_value => l_act_typ_code
2651 ,p_message_text => l_value
2652 ,p_id2 => l_id2
2653 ,p_rubric_type => l_mandatory);
2654 --
2655 -- Added for time analysis
2656 If l_ctr_units = 'HOUR' Then
2657 l_frequency := l_ctr_frequency;
2658 l_norm_hours := fnd_number.canonical_to_number(l_norm_ctr_hours);
2659 End If;
2660 --
2661 --Percentage Part Time
2662 --Item:S41.G01.00.020
2663 If (l_act_typ_val Is not Null And substr(l_act_typ_val,1,1) = 'P') Then
2664
2665 If l_frequency <> 'M' Then
2666 l_emp_month_hours := PAY_FR_GENERAL.convert_hours(p_effective_date => l_effective_date
2667 ,p_business_group_id => g_param_business_group_id
2668 ,p_assignment_id => l_assignment_id
2669 ,p_hours => l_norm_hours
2670 ,p_from_freq_code => l_frequency
2671 ,p_to_freq_code => 'M');
2672 Else
2673 l_emp_month_hours := l_norm_hours;
2674 End If;
2675 -- #3542081
2676 l_percentage_part_time := round((l_emp_month_hours/l_estab_monthly_hours) * 10000);
2677 End If;
2678
2679 -- #3553872 previously the validation was there within the above if statement. Now, it has been taken down.
2680 -- Percentage of Part Time
2681 IF l_act_typ_code = '02' AND l_percentage_part_time is null THEN
2682 l_error_type := l_error;
2683 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2684 'VALUE1:'||hr_general.decode_lookup
2685 ('FR_DADS_RUBRICS','S41.G01.00.020'), null, null);
2686 ELSE
2687 l_error_type := null;
2688 l_value := null;
2689 END IF;
2690
2691 archive_data( p_rubric_name => 'S41.G01.00.020'
2692 ,p_message_type => l_error_type
2693 ,p_id => p_org_id
2694 ,p_lookup_type => NULL
2695 ,p_file_value => l_percentage_part_time
2696 ,p_message_text => l_value
2697 ,p_id2 => l_id2
2698 ,p_rubric_type => l_conditional);
2699 hr_utility.set_location('S41 l_percentage_part_time:'||l_percentage_part_time,113);
2700 --
2701 --Employee Category Code
2702 --Item:S41.G01.00.015
2703
2704
2705 For i in fetch_pension_info(l_effective_date)
2706 Loop
2707 l_first_row := True;
2708 Exit When fetch_pension_info%NotFound;
2709 If l_first_row = True Then
2710 l_pension_info := i.pension_info;
2711 l_first_row := False;
2712 End If;
2713 End Loop;
2714
2715
2716 Begin
2717 l_pen_agirc_val := hruserdt.get_table_value (g_param_business_group_id,
2718 'FR_APEC_AGIRC',
2719 'AGIRC' ,
2720 l_pension_info,
2721 l_effective_date);
2722 Exception
2723 When Others then
2724 hr_utility.set_location('S41 FR_APEC_AGIRC Failed:'||sqlerrm,1113);
2725 end;
2726
2727
2728 If l_pen_agirc_val = 'Y' Then
2729 If l_pension_info = '222' Then -- Article 36
2730 l_emp_cat_code := '02';
2731 Else
2732 l_emp_cat_code := '01';
2733 End If;
2734 Else
2735 l_emp_cat_code := '04';
2736 End If;
2737
2738 -- Employee Category Code
2739 IF l_emp_cat_code is null THEN
2740 l_error_type := l_error;
2741 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2742 'VALUE1:'||hr_general.decode_lookup
2743 ('FR_DADS_RUBRICS','S41.G01.00.015'), null, null);
2744 ELSE
2745 l_error_type := null;
2746 l_value := null;
2747 END IF;
2748 archive_data( p_rubric_name => 'S41.G01.00.015'
2749 ,p_message_type => l_error_type
2750 ,p_id => p_org_id
2751 ,p_lookup_type => 'FR_DADS_EMP_CAT_CODE'
2752 ,p_file_value => l_emp_cat_code
2753 ,p_message_text => l_value
2754 ,p_id2 => l_id2
2755 ,p_rubric_type => l_mandatory);
2756
2757 hr_utility.set_location('S41 l_emp_cat_code:'||l_emp_cat_code,114);
2758 --Collective Agreement Code
2759 --Item:S41.G01.00.016
2760 --Hard code it to 301
2761
2762 archive_data( p_rubric_name => 'S41.G01.00.016'
2763 ,p_message_type => NULL
2764 ,p_id => p_org_id
2765 ,p_lookup_type => Null
2766 ,p_file_value => l_col_aggr_code
2767 ,p_message_text => Null
2768 ,p_id2 => l_id2
2769 ,p_rubric_type => l_conditional);
2770
2771 hr_utility.set_location('S41 l_collective_aggremenet_code:'||l_col_aggr_code,114);
2772 --
2773
2774 -- get the PER application ID
2775 --
2776 open csr_get_per_id;
2777 fetch csr_get_per_id into l_per_id;
2778 close csr_get_per_id;
2779 --
2780
2781
2782 --Conventional Classfication
2783 --Item:S41.G01.00.017
2784 --Need to confirm
2785 --
2786 -- Conventional Classification
2787
2788 l_conventional_classification := Null;
2789
2790 For i in csr_fetch_conv_class( l_assignment_id
2791 ,l_effective_date
2792 ,l_per_id)
2793 Loop
2794 If i.qualifier = 'COEFFICIENT' Then
2795 l_conventional_classification := i.conv_classfication;
2796 End If;
2797 End Loop;
2798
2799
2800 IF l_conventional_classification Is Null Then
2801 l_value := null;
2802 archive_data( p_rubric_name => 'S41.G01.00.017'
2803 ,p_message_type => l_error_type
2804 ,p_id => p_org_id
2805 ,p_lookup_type => 'FR_DADS_CAGR_CONV_CLASS'
2806 ,p_file_value => '01'
2807 ,p_message_text => l_value
2808 ,p_id2 => l_id2
2809 ,p_rubric_type => l_mandatory);
2810 Else
2811 archive_data( p_rubric_name => 'S41.G01.00.017'
2812 ,p_message_type => l_error_type
2813 ,p_id => p_org_id
2814 ,p_lookup_type => Null
2815 ,p_file_value => l_conventional_classification
2816 ,p_message_text => l_value
2817 ,p_id2 => l_id2
2818 ,p_rubric_type => l_mandatory);
2819 End If;
2820
2821 --Basic Pension
2822 --Item:S41.G01.00.018
2823 --Hard code it to 200
2824
2825 l_pension_code := 200;
2826
2827 -- Basic Pension Organisation Code
2828
2829 IF l_pension_code is null THEN
2830 l_error_type := l_error;
2831 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2832 'VALUE1:'||hr_general.decode_lookup
2833 ('FR_DADS_RUBRICS','S41.G01.00.018'), null, null);
2834 ELSE
2835 l_error_type := null;
2836 l_value := null;
2837 END IF;
2838 archive_data( p_rubric_name => 'S41.G01.00.018'
2839 ,p_message_type => l_error_type
2840 ,p_id => p_org_id
2841 ,p_lookup_type => Null
2842 ,p_file_value => to_char(l_pension_code)
2843 ,p_message_text => l_value
2844 ,p_id2 => l_id2
2845 ,p_rubric_type => l_mandatory);
2846
2847 hr_utility.set_location('S41 to_char(l_pension_code):'||to_char(l_pension_code),114);
2848 -- #3542645 border worker will be 1 if border worker value is Yes or 2 when detache has some value
2849 IF l_border_worker = 'Y' THEN
2850 l_border_worker := '01';
2851 ELSIF l_detache IS NOT NULL THEN
2852 l_border_worker := '02';
2853 ELSE
2854 l_border_worker := NULL;
2855 END IF;
2856 -- Code for Work Abroad
2857 IF l_border_worker <> '01' AND l_border_worker <> '02' AND l_border_worker is null THEN
2858 l_error_type := l_error;
2859 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
2860 'VALUE1:'||l_issue_estab_name, 'VALUE2: '||hr_general.decode_lookup
2861 ('FR_DADS_RUBRICS','S41.G01.00.034.M'), null);
2862 END IF;
2863
2864 archive_data( p_rubric_name => 'S41.G01.00.034'
2865 ,p_message_type => l_error_type
2866 ,p_id => p_org_id
2867 ,p_lookup_type => 'FR_DADS_WORK_CODE'
2868 ,p_file_value => l_border_worker
2869 ,p_message_text => l_value
2870 ,p_id2 => l_id2
2871 ,p_rubric_type => l_conditional);
2872 --
2873 hr_utility.set_location('S41 l_border_worker:'||l_border_worker,114);
2874 --
2875 -- Time Analysis Changes
2876 -- Item ID : S41.G01.00.021
2877 l_num_hrs := fr_rolling_balance_pro (p_assignment_id ,
2878 'FR_ACTUAL_HRS_WORKED_DADSU',
2879 l_param_start_date,
2880 l_effective_date);
2881
2882 l_value := Null;
2883 archive_data( p_rubric_name => 'S41.G01.00.021'
2884 ,p_message_type => l_error_type
2885 ,p_id => p_org_id
2886 ,p_lookup_type => Null
2887 ,p_file_value => l_num_hrs
2888 ,p_message_text => l_value
2889 ,p_id2 => l_id2
2890 ,p_rubric_type => l_conditional);
2891 --
2892 -- Item ID : S41.G01.00.022
2893 l_num_hrs_emp := fr_rolling_balance_pro (p_assignment_id ,
2894 'FR_CONTRACTUAL_HRS_DADSU',
2895 l_param_start_date,
2896 l_effective_date);
2897
2898 archive_data( p_rubric_name => 'S41.G01.00.022'
2899 ,p_message_type => l_error_type
2900 ,p_id => p_org_id
2901 ,p_lookup_type => Null
2902 ,p_file_value => l_num_hrs_emp
2903 ,p_message_text => l_value
2904 ,p_id2 => l_id2
2905 ,p_rubric_type => l_conditional);
2906 --
2907 -- Item ID : S41.G01.00.023
2908 hr_utility.set_location('p_assignment_id'||p_assignment_id, 22);
2909 hr_utility.set_location('l_param_start_date'||l_param_start_date, 22);
2910 hr_utility.set_location('l_effective_date'||l_effective_date, 22);
2911 hr_utility.set_location('l_act_dt'||l_act_dt, 22);
2912 --
2913 l_num_hrs_latest := fr_rolling_balance_pro
2914 (p_assignment_id ,
2915 'FR_HOURS_PAID_DADSU',
2916 l_param_start_date,
2917 l_effective_date);
2918
2919 hr_utility.set_location('l_num_hrs_latest'||l_num_hrs_latest, 22);
2920
2921 -- need to find the last month where l_num_hrs_latest>= 60
2922 -- if the person has resigned or l_num_hrs_latest < 1200
2923 if (l_num_hrs_latest < 1200) or (l_act_dt < l_effective_date) then
2924 hr_utility.set_location('Find latest month', 22);
2925 /* 4172068 checking whether a record exists in fnd_sessions table */
2926 select count(session_id)
2927 into l_session_id
2928 from fnd_sessions
2929 where session_id = userenv('sessionid');
2930 if l_session_id > 0 then
2931 -- find the SMIC rate
2932 l_hourly_smic_rate := to_number(pay_balance_pkg.run_db_item('FR_HOURLY_SMIC_RATE', null, 'FR'));
2933 else
2934 /* 4172068 insert a record only if there is no record exists in fnd_sessions table */
2935 -- insert a row into fnd_sessions for the DBI value to be retrieved
2936 Insert into fnd_sessions (session_id, effective_date) values(userenv('sessionid'), sysdate);
2937 -- find the SMIC rate
2938 l_hourly_smic_rate := to_number(pay_balance_pkg.run_db_item('FR_HOURLY_SMIC_RATE', null, 'FR'));
2939 -- delete the row from fnd_sessions
2940 Delete from fnd_sessions where session_id = userenv('sessionid');
2941 end if;
2942 /* 4172068 */
2943 --
2944 For i in csr_get_per_dates(l_effective_date)
2945 Loop
2946 l_num_hrs_latest := fr_rolling_balance_pro
2947 (p_assignment_id ,
2948 'FR_HOURS_PAID_DADSU',
2949 i.start_date,
2950 i.end_date);
2951 l_023_ss_base := fr_rolling_balance_pro (p_assignment_id ,
2952 'FR_SS_BASE',
2953 i.start_date,
2954 i.end_date);
2955
2956 l_023_ss_base_retro := fr_rolling_balance_pro (p_assignment_id ,
2957 'FR_SS_BASE_RETRO',
2958 i.start_date,
2959 i.end_date);
2960
2961 l_023_ss_disabled_base := fr_rolling_balance_pro (p_assignment_id ,
2962 'FR_SS_DISABLED_BASE',
2963 i.start_date,
2964 i.end_date);
2965
2966 l_023_ss_disabled_base_retro := fr_rolling_balance_pro (p_assignment_id ,
2967 'FR_SS_DISABLED_BASE_RETRO',
2968 i.start_date,
2969 i.end_date);
2970
2971 l_023_ss_excess_base := fr_rolling_balance_pro (p_assignment_id,
2972 'FR_SS_EXCESS_BASE',
2973 i.start_date,
2974 i.end_date);
2975
2976 l_023_ss_part_time_base := fr_rolling_balance_pro (p_assignment_id ,
2977 'FR_SS_PART_TIME_BASE',
2978 i.start_date,
2979 i.end_date);
2980
2981 l_023_ss_part_time_base_retro := fr_rolling_balance_pro (p_assignment_id ,
2982 'FR_SS_PART_TIME_BASE_RETRO',
2983 i.start_date,
2984 i.end_date);
2985
2986 l_023_ss_apprenticeship_base := fr_rolling_balance_pro (p_assignment_id ,
2987 'FR_SS_APPRENTICESHIP_BASE',
2988 i.start_date,
2989 i.end_date);
2990
2991 l_023_ss_base_above_smic := fr_rolling_balance_pro (p_assignment_id ,
2992 'FR_SS_BASE_ABOVE_SMIC',
2993 i.start_date,
2994 i.end_date);
2995
2996 l_023_ss_base_above_smic_retro := fr_rolling_balance_pro (p_assignment_id ,
2997 'FR_SS_BASE_ABOVE_SMIC_RETRO',
2998 i.start_date,
2999 i.end_date);
3000 l_023_gross_sbase_sec := l_023_ss_base + l_023_ss_base_retro + l_023_ss_disabled_base
3001 + l_023_ss_disabled_base_retro + l_023_ss_excess_base
3002 + l_023_ss_part_time_base + l_023_ss_part_time_base_retro
3003 + l_023_ss_apprenticeship_base + l_023_ss_base_above_smic
3004 + l_023_ss_base_above_smic_retro;
3005
3006 If (l_num_hrs_latest >= 60)
3007 or (l_023_gross_sbase_sec > (l_hourly_smic_rate * 60)) then
3008 l_mth_023 := to_char(i.end_date, 'MM');
3009 End If;
3010 End Loop;
3011 else
3012 hr_utility.set_location('Latest month is null', 22);
3013 l_mth_023 := null;
3014 end if;
3015 hr_utility.set_location('l_mth_023: '||l_mth_023, 22);
3016 archive_data( p_rubric_name => 'S41.G01.00.023'
3017 ,p_message_type => l_error_type
3018 ,p_id => p_org_id
3019 ,p_lookup_type => Null
3020 ,p_file_value => l_mth_023
3021 ,p_message_text => l_value
3022 ,p_id2 => l_id2
3023 ,p_rubric_type => l_conditional);
3024
3025 -- end of Time analysis changes
3026 --
3027 -- Item ID : S41.G01.00.029
3028
3029 l_ss_base := fr_rolling_balance_pro (p_assignment_id ,
3030 'FR_SS_BASE',
3031 l_param_start_date,
3032 l_effective_date);
3033
3034 l_ss_base_retro := fr_rolling_balance_pro (p_assignment_id ,
3035 'FR_SS_BASE_RETRO',
3036 l_param_start_date,
3037 l_effective_date);
3038
3039 l_ss_disabled_base := fr_rolling_balance_pro (p_assignment_id ,
3040 'FR_SS_DISABLED_BASE',
3041 l_param_start_date,
3042 l_effective_date);
3043
3044 l_ss_disabled_base_retro := fr_rolling_balance_pro (p_assignment_id ,
3045 'FR_SS_DISABLED_BASE_RETRO',
3046 l_param_start_date,
3047 l_effective_date);
3048
3049 l_ss_excess_base := fr_rolling_balance_pro (p_assignment_id,
3050 'FR_SS_EXCESS_BASE',
3051 l_param_start_date,
3052 l_effective_date);
3053
3054 l_ss_part_time_base := fr_rolling_balance_pro (p_assignment_id ,
3055 'FR_SS_PART_TIME_BASE',
3056 l_param_start_date,
3057 l_effective_date);
3058
3059 l_ss_part_time_base_retro := fr_rolling_balance_pro (p_assignment_id ,
3060 'FR_SS_PART_TIME_BASE_RETRO',
3061 l_param_start_date,
3062 l_effective_date);
3063
3064 l_ss_apprenticeship_base := fr_rolling_balance_pro (p_assignment_id ,
3065 'FR_SS_APPRENTICESHIP_BASE',
3066 l_param_start_date,
3067 l_effective_date);
3068
3069 l_ss_base_above_smic := fr_rolling_balance_pro (p_assignment_id ,
3070 'FR_SS_BASE_ABOVE_SMIC',
3071 l_param_start_date,
3072 l_effective_date);
3073
3074 l_ss_base_above_smic_retro := fr_rolling_balance_pro (p_assignment_id ,
3075 'FR_SS_BASE_ABOVE_SMIC_RETRO',
3076 l_param_start_date,
3077 l_effective_date);
3078
3079 -- Item ID : S41.G01.00.030.001
3080
3081 l_ta_ss_band := fr_rolling_balance_pro(p_assignment_id ,
3082 'FR_TA_SS_BAND',
3083 l_param_start_date,
3084 l_effective_date);
3085
3086 l_ta_ss_band_retro := fr_rolling_balance_pro(p_assignment_id ,
3087 'FR_TA_SS_BAND_RETRO',
3088 l_param_start_date,
3089 l_effective_date);
3090
3091 l_ta_ss_disabled_band := fr_rolling_balance_pro(p_assignment_id ,
3092 'FR_TA_SS_DISABLED_BAND',
3093 l_param_start_date,
3094 l_effective_date);
3095
3096 l_ta_ss_disabled_band_retro := fr_rolling_balance_pro(p_assignment_id ,
3097 'FR_TA_SS_DISABLED_BAND_RETRO',
3098 l_param_start_date,
3099 l_effective_date);
3100
3101 l_ta_ss_part_time_band := fr_rolling_balance_pro (p_assignment_id ,
3102 'FR_TA_SS_PART_TIME_BAND',
3103 l_param_start_date,
3104 l_effective_date);
3105
3106 l_ta_ss_part_time_band_retro := fr_rolling_balance_pro (p_assignment_id ,
3107 'FR_TA_SS_PART_TIME_BAND_RETRO',
3108 l_param_start_date,
3109 l_effective_date);
3110
3111 l_ta_ss_band_under_limit := fr_rolling_balance_pro (p_assignment_id ,
3112 'FR_TA_SS_BAND_UNDER_LIMIT',
3113 l_param_start_date,
3114 l_effective_date);
3115
3116 l_ta_ss_band_under_limit_retro := fr_rolling_balance_pro (p_assignment_id ,
3117 'FR_TA_SS_BAND_UNDER_LIMIT_RETRO',
3118 l_param_start_date,
3119 l_effective_date);
3120
3121 l_ta_ss_band_above_smic_retro := fr_rolling_balance_pro (p_assignment_id ,
3122 'FR_TA_SS_BAND_ABOVE_SMIC_RETRO',
3123 l_param_start_date,
3124 l_effective_date);
3125
3126
3127 -- Item ID : S41.G01.00.032.001
3128 --Changed it as per bug#3297601
3129 l_subject_to_csg := fr_rolling_balance_pro (p_assignment_id ,
3130 'FR_CSG_BASE',
3131 l_param_start_date,
3132 l_effective_date);
3133
3134 --Changed it as per bug#3297601
3135 l_subject_to_csg_excess := fr_rolling_balance_pro (p_assignment_id ,
3136 'FR_CSG_EXCESS_BASE',
3137 l_param_start_date,
3138 l_effective_date);
3139
3140 -- Item ID : S41.G01.00.033.001
3141
3142 --Changed it as per bug#3297601
3143 l_subject_to_crds := fr_rolling_balance_pro (p_assignment_id ,
3144 'FR_CRDS_BASE',
3145 l_param_start_date,
3146 l_effective_date);
3147
3148 -- Item ID : S41.G01.00.035.001
3149
3150 --Changed it as per bug#3279601
3151 l_subject_to_salary_tax := fr_rolling_balance_pro (p_assignment_id ,
3152 'FR_SALARY_TAX_BASE',
3153 l_param_start_date,
3154 l_effective_date);
3155
3156 --Changed it as per bug#3279601
3157 l_subject_to_salary_tax_excess := fr_rolling_balance_pro (p_assignment_id ,
3158 'FR_SALARY_TAX_EXCESS_BASE',
3159 l_param_start_date,
3160 l_effective_date);
3161
3162 -- Item ID : S41.G01.00.038
3163
3164 l_benefit_food := fr_rolling_balance_pro (p_assignment_id ,
3165 'FR_BENEFIT_FOOD',
3166 l_param_start_date,
3167 l_effective_date);
3168
3169 -- Item ID : S41.G01.00.039
3170
3171 l_benefit_housing := fr_rolling_balance_pro (p_assignment_id ,
3172 'FR_BENEFIT_HOUSING',
3173 l_param_start_date,
3174 l_effective_date);
3175
3176 -- Item ID : S41.G01.00.040
3177
3178 l_benefit_car := fr_rolling_balance_pro (p_assignment_id ,
3179 'FR_BENEFIT_CAR',
3180 l_param_start_date,
3181 l_effective_date);
3182
3183 -- Item ID : S41.G01.00.041
3184
3185 l_benefit_other := fr_rolling_balance_pro (p_assignment_id ,
3186 'FR_BENEFIT_OTHER',
3187 l_param_start_date,
3188 l_effective_date);
3189
3190 -- Item ID : S41.G01.00.045
3191
3192 l_prof_expense_lump_sums := fr_rolling_balance_pro (p_assignment_id ,
3193 'FR_PROF_EXPENSE_LUMP_SUMS',
3194 l_param_start_date,
3195 l_effective_date);
3196
3197 -- Item ID : S41.G01.00.046
3198 l_actual_prof_expenses := fr_rolling_balance_pro (p_assignment_id ,
3199 'FR_ACTUAL_PROF_EXPENSES',
3200 l_param_start_date,
3201 l_effective_date);
3202
3203 -- Item ID : S41.G01.00.047
3204
3205 l_prof_exp_pd_by_comp := fr_rolling_balance_pro (p_assignment_id ,
3206 'FR_PROF_EXPENSES_PD_BY_COMPANY',
3207 l_param_start_date,
3208 l_effective_date);
3209
3210 -- Item ID : S41.G01.00.048
3211 l_reimb_nprof_exp := fr_rolling_balance_pro (p_assignment_id ,
3212 'FR_REIMBURSEMENT_NON_PROF_EXPENSES',
3213 l_param_start_date,
3214 l_effective_date);
3215
3216 -- Item ID : S41.G01.00.063.001
3217 l_taxable_income := fr_rolling_balance_pro (p_assignment_id ,
3218 'FR_TAXABLE_INCOME',
3219 l_param_start_date,
3220 l_effective_date);
3221
3222 hr_utility.set_location('S41 After Rolling_Balance calculation:',115);
3223
3224 -- #3587152 Subject to Social Security balances has been converted to Social Security balances
3225 l_gross_sbase_sec := l_ss_base + l_ss_base_retro + l_ss_disabled_base + l_ss_disabled_base_retro
3226 + l_ss_excess_base + l_ss_part_time_base + l_ss_part_time_base_retro
3227 + l_ss_apprenticeship_base + l_ss_base_above_smic + l_ss_base_above_smic_retro;
3228
3229 hr_utility.set_location('S41 l_gross_sbase_sec:'||l_gross_sbase_sec,116);
3230 -- Gross Social Security Base
3231 IF l_gross_sbase_sec Is Null THEN
3232 l_error_type := l_error;
3233 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3234 'VALUE1:'||hr_general.decode_lookup
3235 ('FR_DADS_RUBRICS','S41.G01.00.029.001'), null, null);
3236 ELSIF l_gross_sbase_sec < 0 THEN
3237 l_gross_sbase_sec_sign := 'N';
3238 l_error_type := l_warning;
3239 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3240 'VALUE1:'||hr_general.decode_lookup
3241 ('FR_DADS_RUBRICS','S41.G01.00.029.001'), null, null);
3242 ELSE
3243 l_error_type := null;
3244 l_value := null;
3245 END IF;
3246 archive_data( p_rubric_name => 'S41.G01.00.029.001'
3247 ,p_message_type => l_error_type
3248 ,p_id => p_org_id
3249 ,p_lookup_type => Null
3250 ,p_file_value => to_char(l_gross_sbase_sec, '9999999990D99')
3251 ,p_message_text => l_value
3252 ,p_id2 => l_id2
3253 ,p_rubric_type => l_mandatory);
3254
3255
3256 -- Item ID : S41.G01.00.029.002
3257 -- Sign
3258 IF l_gross_sbase_sec_sign <> 'N' AND l_gross_sbase_sec_sign is not null THEN
3259 l_error_type := l_error;
3260 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
3261 'VALUE1:', 'VALUE2: '||hr_general.decode_lookup
3262 ('FR_DADS_RUBRICS','S41.G01.00.029.002.M'), null);
3263 ELSE
3264 l_error_type := null;
3265 l_value := null;
3266 END IF;
3267
3268 archive_data( p_rubric_name => 'S41.G01.00.029.002'
3269 ,p_message_type => l_error_type
3270 ,p_id => p_org_id
3271 ,p_lookup_type => Null
3272 ,p_file_value => l_gross_sbase_sec_sign
3273 ,p_message_text => l_value
3274 ,p_id2 => l_id2
3275 ,p_rubric_type => l_conditional);
3276
3277 -- #3587152 Additional balances are added to the given calculation
3278 l_cap_ssec_base := l_ta_ss_band + l_ta_ss_band_retro + l_ta_ss_disabled_band + l_ta_ss_disabled_band_retro
3279 + l_ta_ss_part_time_band + l_ta_ss_part_time_band_retro + l_ta_ss_band_under_limit
3280 + l_ta_ss_band_under_limit_retro + l_ta_ss_band_above_smic_retro;
3281
3282 -- Capped Social Secuirty Base
3283 IF l_cap_ssec_base is null THEN
3284 l_error_type := l_error;
3285 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3286 'VALUE1:'||hr_general.decode_lookup
3287 ('FR_DADS_RUBRICS','S41.G01.00.030.001'), null, null);
3288 ELSIF l_cap_ssec_base < 0 THEN
3289 l_cap_ssec_base_sign := 'N';
3290 l_error_type := l_warning;
3291 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3292 'VALUE1:'||hr_general.decode_lookup
3293 ('FR_DADS_RUBRICS','S41.G01.00.030.001'), null, null);
3294 ELSE
3295 l_error_type := null;
3296 l_value := null;
3297 END IF;
3298 archive_data( p_rubric_name => 'S41.G01.00.030.001'
3299 ,p_message_type => l_error_type
3300 ,p_id => p_org_id
3301 ,p_lookup_type => Null
3302 ,p_file_value => to_char(l_cap_ssec_base, '9999999990D99')
3303 ,p_message_text => l_value
3304 ,p_id2 => l_id2
3305 ,p_rubric_type => l_mandatory);
3306
3307 -- Item ID : S41.G01.00.030.002
3308 -- Sign
3309 IF l_cap_ssec_base_sign <> 'N' AND l_cap_ssec_base_sign is not null THEN
3310 l_error_type := l_error;
3311 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
3312 'VALUE1:', 'VALUE2: '||hr_general.decode_lookup
3313 ('FR_DADS_RUBRICS','S41.G01.00.029.002.M'), null);
3314 ELSE
3315 l_error_type := null;
3316 l_value := null;
3317 END IF;
3318
3319 archive_data( p_rubric_name => 'S41.G01.00.030.002'
3320 ,p_message_type => l_error_type
3321 ,p_id => p_org_id
3322 ,p_lookup_type => Null
3323 ,p_file_value => l_cap_ssec_base_sign
3324 ,p_message_text => l_value
3325 ,p_id2 => l_id2
3326 ,p_rubric_type => l_conditional);
3327
3328 -- CSG Base
3329 l_csg_base := l_subject_to_csg + l_subject_to_csg_excess;
3330 IF l_csg_base is null THEN
3331 l_error_type := l_error;
3332 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3333 'VALUE1:'||hr_general.decode_lookup
3334 ('FR_DADS_RUBRICS','S41.G01.00.032.001'), null, null);
3335 ELSIF l_csg_base < 0 THEN
3336 l_csg_base_sign := 'N';
3337 l_error_type := l_warning;
3338 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3339 'VALUE1:'||hr_general.decode_lookup
3340 ('FR_DADS_RUBRICS','S41.G01.00.032.001'), null, null);
3341 ELSE
3342 l_error_type := null;
3343 l_value := null;
3344 END IF;
3345 l_csg_base := l_subject_to_csg + l_subject_to_csg_excess;
3346
3347 archive_data( p_rubric_name => 'S41.G01.00.032.001'
3348 ,p_message_type => l_error_type
3349 ,p_id => p_org_id
3350 ,p_lookup_type => Null
3351 ,p_file_value => to_char(l_csg_base, '9999999990D99')
3352 ,p_message_text => l_value
3353 ,p_id2 => l_id2
3354 ,p_rubric_type => l_mandatory);
3355
3356 -- Item ID : S41.G01.00.032.002
3357
3358 -- Sign
3359 IF l_csg_base_sign <> 'N' AND l_csg_base_sign is not null THEN
3360 l_error_type := l_error;
3361 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
3362 'VALUE1:', 'VALUE2: '||hr_general.decode_lookup
3363 ('FR_DADS_RUBRICS','S41.G01.00.029.002.M'), null);
3364 ELSE
3365 l_error_type := null;
3366 l_value := null;
3367 END IF;
3368 archive_data( p_rubric_name => 'S41.G01.00.032.002'
3369 ,p_message_type => l_error_type
3370 ,p_id => p_org_id
3371 ,p_lookup_type => Null
3372 ,p_file_value => l_csg_base_sign
3373 ,p_message_text => l_value
3374 ,p_id2 => l_id2
3375 ,p_rubric_type => l_conditional);
3376
3377 -- #3587152 CRDS Base does includes FR_CSG_EXCESS_BASE also
3378 l_subject_to_crds := l_subject_to_crds + l_subject_to_csg_excess;
3379
3380 -- CRDS Base
3381 IF l_subject_to_crds is null THEN
3382 l_error_type := l_error;
3383 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3384 'VALUE1:'||hr_general.decode_lookup
3385 ('FR_DADS_RUBRICS','S41.G01.00.033.001'), null, null);
3386 ELSIF l_subject_to_crds < 0 THEN
3387 l_subject_to_crds_sign := 'N';
3388 l_error_type := l_warning;
3389 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3390 'VALUE1:'||hr_general.decode_lookup
3391 ('FR_DADS_RUBRICS','S41.G01.00.033.001'), null, null);
3392 ELSE
3393 l_error_type := null;
3394 l_value := null;
3395 END IF;
3396 archive_data( p_rubric_name => 'S41.G01.00.033.001'
3397 ,p_message_type => l_error_type
3398 ,p_id => p_org_id
3399 ,p_lookup_type => Null
3400 ,p_file_value => to_char(l_subject_to_crds, '9999999990D99')
3401 ,p_message_text => l_value
3402 ,p_id2 => l_id2
3403 ,p_rubric_type => l_mandatory);
3404
3405
3406 -- Item ID : S41.G01.00.033.002
3407 -- Sign
3408 IF l_subject_to_crds_sign <> 'N' AND l_subject_to_crds_sign is not null THEN
3409 l_error_type := l_error;
3410 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
3411 'VALUE1:', 'VALUE2: '||hr_general.decode_lookup
3412 ('FR_DADS_RUBRICS','S41.G01.00.029.002.M'), null);
3413 ELSE
3414 l_error_type := null;
3415 l_value := null;
3416 END IF;
3417 archive_data( p_rubric_name => 'S41.G01.00.033.002'
3418 ,p_message_type => l_error_type
3419 ,p_id => p_org_id
3420 ,p_lookup_type => Null
3421 ,p_file_value => l_subject_to_crds_sign
3422 ,p_message_text => l_value
3423 ,p_id2 => l_id2
3424 ,p_rubric_type => l_conditional);
3425
3426 -- Fiscal Base
3427 l_fiscal_base := l_subject_to_salary_tax +l_subject_to_salary_tax_excess;
3428 IF l_fiscal_base is null THEN
3429 l_error_type := l_error;
3430 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3431 'VALUE1:'||hr_general.decode_lookup
3432 ('FR_DADS_RUBRICS','S41.G01.00.035.001'), null, null);
3433 ELSIF l_fiscal_base < 0 THEN
3434 l_fiscal_base_sign := 'N';
3435 l_error_type := l_warning;
3436 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3437 'VALUE1:'||hr_general.decode_lookup
3438 ('FR_DADS_RUBRICS','S41.G01.00.035.001'), null, null);
3439 ELSE
3440 l_error_type := null;
3441 l_value := null;
3442 END IF;
3443
3444 archive_data( p_rubric_name => 'S41.G01.00.035.001'
3445 ,p_message_type => l_error_type
3446 ,p_id => p_org_id
3447 ,p_lookup_type => Null
3448 ,p_file_value => to_char(l_fiscal_base, '9999999990D99')
3449 ,p_message_text => l_value
3450 ,p_id2 => l_id2
3451 ,p_rubric_type => l_mandatory);
3452
3453 -- Item ID : S41.G01.00.035.002
3454 -- Sign
3455 IF l_fiscal_base_sign <> 'N' AND l_fiscal_base_sign is not null THEN
3456 l_error_type := l_error;
3457 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
3458 'VALUE1:', 'VALUE2: '||hr_general.decode_lookup
3459 ('FR_DADS_RUBRICS','S41.G01.00.029.002.M'), null);
3460 ELSE
3461 l_error_type := null;
3462 l_value := null;
3463 END IF;
3464
3465 archive_data( p_rubric_name => 'S41.G01.00.035.002'
3466 ,p_message_type => l_error_type
3467 ,p_id => p_org_id
3468 ,p_lookup_type => Null
3469 ,p_file_value => l_fiscal_base_sign
3470 ,p_message_text => l_value
3471 ,p_id2 => l_id2
3472 ,p_rubric_type => l_conditional);
3473
3474 -- Item ID : S41.G01.00.038
3475
3476 IF l_benefit_food > 0 THEN
3477 l_benefit_food_sign := 'N';
3478 archive_data( p_rubric_name => 'S41.G01.00.038'
3479 ,p_message_type => NULL
3480 ,p_id => p_org_id
3481 ,p_lookup_type => 'FR_DADS_FRINGE_BENEFITS'
3482 ,p_file_value => l_benefit_food_sign
3483 ,p_message_text => Null
3484 ,p_id2 => l_id2
3485 ,p_rubric_type => l_conditional);
3486 END IF;
3487
3488 -- Item ID : S41.G01.00.039
3489
3490 IF l_benefit_housing > 0 THEN
3491 l_benefit_housing_sign := 'L';
3492 archive_data( p_rubric_name => 'S41.G01.00.039'
3493 ,p_message_type => NULL
3494 ,p_id => p_org_id
3495 ,p_lookup_type => 'FR_DADS_FRINGE_BENEFITS'
3496 ,p_file_value => l_benefit_housing_sign
3497 ,p_message_text => Null
3498 ,p_id2 => l_id2
3499 ,p_rubric_type => l_conditional);
3500 END IF;
3501
3502 -- Item ID : S41.G01.00.040
3503
3504 IF l_benefit_car > 0 THEN
3505 l_benefit_car_sign := 'V';
3506 archive_data( p_rubric_name => 'S41.G01.00.040'
3507 ,p_message_type => NULL
3508 ,p_id => p_org_id
3509 ,p_lookup_type => 'FR_DADS_FRINGE_BENEFITS'
3510 ,p_file_value => l_benefit_car_sign
3511 ,p_message_text => Null
3512 ,p_id2 => l_id2
3513 ,p_rubric_type => l_conditional);
3514
3515 END IF;
3516
3517 -- Item ID : S41.G01.00.041
3518
3519 IF l_benefit_other > 0 THEN
3520 l_benefit_other_sign := 'A';
3521 archive_data( p_rubric_name => 'S41.G01.00.041'
3522 ,p_message_type => NULL
3523 ,p_id => p_org_id
3524 ,p_lookup_type => 'FR_DADS_FRINGE_BENEFITS'
3525 ,p_file_value => l_benefit_other_sign
3526 ,p_message_text => Null
3527 ,p_id2 => l_id2
3528 ,p_rubric_type => l_conditional);
3529
3530 END IF;
3531
3532 -- Item ID : S41.G01.00.037.001
3533
3534 l_fringe_benefits := l_benefit_food + l_benefit_housing + l_benefit_car
3535 + l_benefit_other;
3536
3537 IF (l_fringe_benefits <> 0
3538 And l_fringe_benefits Is Not Null) THEN
3539 archive_data( p_rubric_name => 'S41.G01.00.037.001'
3540 ,p_message_type => NULL
3541 ,p_id => p_org_id
3542 ,p_lookup_type => NULL
3543 ,p_file_value => to_char(l_fringe_benefits, '9999999990D99')
3544 ,p_message_text => Null
3545 ,p_id2 => l_id2
3546 ,p_rubric_type => l_conditional);
3547 END IF;
3548
3549 -- Item ID : S41.G01.00.037.002
3550
3551 IF l_fringe_benefits < 0 THEN
3552 l_fringe_benefits_sign := 'N';
3553 archive_data( p_rubric_name => 'S41.G01.00.037.002'
3554 ,p_message_type => NULL
3555 ,p_id => p_org_id
3556 ,p_lookup_type => Null
3557 ,p_file_value => l_fringe_benefits_sign
3558 ,p_message_text => Null
3559 ,p_id2 => l_id2
3560 ,p_rubric_type => l_conditional);
3561
3562 END IF;
3563
3564 -- Item ID : S41.G01.00.045
3565
3566 IF l_prof_expense_lump_sums > 0 THEN
3567 l_prof_expense_lump_sums_sign := 'F';
3568 archive_data( p_rubric_name => 'S41.G01.00.045'
3569 ,p_message_type => NULL
3570 ,p_id => p_org_id
3571 ,p_lookup_type => 'FR_DADS_PROF_EXPENSE'
3572 ,p_file_value => l_prof_expense_lump_sums_sign
3573 ,p_message_text => Null
3574 ,p_id2 => l_id2
3575 ,p_rubric_type => l_conditional);
3576
3577 END IF;
3578
3579 -- Item ID : S41.G01.00.046
3580
3581 IF l_actual_prof_expenses > 0 THEN
3582 l_actual_prof_expenses_sign := 'R';
3583 archive_data( p_rubric_name => 'S41.G01.00.046'
3584 ,p_message_type => NULL
3585 ,p_id => p_org_id
3586 ,p_lookup_type => 'FR_DADS_PROF_EXPENSE'
3587 ,p_file_value => l_actual_prof_expenses_sign
3588 ,p_message_text => Null
3589 ,p_id2 => l_id2
3590 ,p_rubric_type => l_conditional);
3591
3592 END IF;
3593
3594 -- Item ID : S41.G01.00.047
3595
3596 IF l_prof_exp_pd_by_comp > 0 THEN
3597 l_prof_exp_pd_by_comp_sign := 'P';
3598 archive_data( p_rubric_name => 'S41.G01.00.047'
3599 ,p_message_type => NULL
3600 ,p_id => p_org_id
3601 ,p_lookup_type => 'FR_DADS_PROF_EXPENSE'
3602 ,p_file_value => l_prof_exp_pd_by_comp_sign
3603 ,p_message_text => Null
3604 ,p_id2 => l_id2
3605 ,p_rubric_type => l_conditional);
3606
3607 END IF;
3608
3609 -- Item ID : S41.G01.00.048
3610
3611 IF l_reimb_nprof_exp > 0 THEN
3612 l_reimb_nprof_exp_sign := 'D';
3613 archive_data( p_rubric_name => 'S41.G01.00.048'
3614 ,p_message_type => NULL
3615 ,p_id => p_org_id
3616 ,p_lookup_type => 'FR_DADS_PROF_EXPENSE'
3617 ,p_file_value => l_reimb_nprof_exp_sign
3618 ,p_message_text => Null
3619 ,p_id2 => l_id2
3620 ,p_rubric_type => l_conditional);
3621
3622 END IF;
3623
3624 -- Item ID : S41.G01.00.044
3625
3626 l_professional_expenses := l_prof_expense_lump_sums + l_actual_prof_expenses +
3627 l_prof_exp_pd_by_comp +l_reimb_nprof_exp;
3628
3629 IF (l_professional_expenses <> 0
3630 And l_professional_expenses Is Not Null) THEN
3631 archive_data( p_rubric_name => 'S41.G01.00.044'
3632 ,p_message_type => NULL
3633 ,p_id => p_org_id
3634 ,p_lookup_type => Null
3635 ,p_file_value => to_char(l_professional_expenses, '99999999990D99')
3636 ,p_message_text => Null
3637 ,p_id2 => l_id2
3638 ,p_rubric_type => l_conditional);
3639 END IF;
3640
3641 -- Item ID : S41.G01.00.063.001
3642 -- Activity Salary
3643 IF l_taxable_income is null THEN
3644 l_error_type := l_error;
3645 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3646 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3647 'S41.G01.00.063.001'), null, null);
3648 ELSIF l_taxable_income_sign = 'N' THEN
3649 l_error_type := l_warning;
3650 l_value := pay_fr_general.get_payroll_message('PAY_75188_VAL_NEG',
3651 'VALUE1:'||hr_general.decode_lookup
3652 ('FR_DADS_RUBRICS','S41.G01.00.063.001'), null, null);
3653 ELSE
3654 l_error_type := null;
3655 l_value := null;
3656 END IF;
3657 archive_data( p_rubric_name => 'S41.G01.00.063.001'
3658 ,p_message_type => l_error_type
3659 ,p_id => p_org_id
3660 ,p_lookup_type => Null
3661 ,p_file_value => to_char(l_taxable_income, '99999999990D99')
3662 ,p_message_text => l_value
3663 ,p_id2 => l_id2
3664 ,p_rubric_type => l_mandatory);
3665
3666
3667 IF l_asg_location is null THEN
3668 -- #3553797 Added employee full name in the token. Previously assignment establishment name was there in the token.
3669 l_error_type := l_error;
3670 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
3671 'VALUE1:'||l_emp_full_name, 'VALUE2: '||hr_general.decode_lookup
3672 ('FR_DADS_RUBRICS','S41.G01.00.060.006.M'), null);
3673 archive_data( p_rubric_name => 'S41.G01.00.060.006'
3674 ,p_message_type => l_error_type
3675 ,p_id => p_org_id
3676 ,p_lookup_type => Null
3677 ,p_file_value => null
3678 ,p_message_text => l_value
3679 ,p_id2 => l_id2
3680 ,p_rubric_type => l_conditional);
3681 ELSE
3682 -- Have commented because there is specific validation when both the locations are not equal
3683 -- ELSIf (l_asg_location Is not Null And l_est_location <> l_asg_location) Then
3684 --Cursor for get location details of assignment--
3685 Open csr_asg_loc(l_asg_location);
3686 Fetch csr_asg_loc Into l_csr_asg_loc;
3687 Close csr_asg_loc;
3688 --Checking whether both the locations are equal or not
3689 If l_asg_location Is not Null Then
3690 --Complement
3691 archive_data( p_rubric_name => 'S41.G01.00.060.001'
3692 ,p_message_type => NULL
3693 ,p_id => p_org_id
3694 ,p_lookup_type => Null
3695 ,p_file_value => l_csr_asg_loc.complement
3696 ,p_message_text => Null
3697 ,p_id2 => l_id2
3698 ,p_rubric_type => l_conditional);
3699
3700 -- Nature and name of the street
3701 IF ((l_est_location <> l_asg_location) AND l_csr_asg_loc.street_name is null) THEN
3702 l_error_type := l_error;
3703 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
3704 'VALUE1:'||l_location_name, 'VALUE2:'||hr_general.decode_lookup
3705 ('FR_DADS_RUBRICS','S41.G01.00.060.006'), null);
3706 ELSIF ((l_est_location = l_asg_location) AND l_csr_asg_loc.street_name is not null) THEN
3707 l_error_type := l_error;
3708 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3709 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3710 'S41.G01.00.060.006'), null, null);
3711 ELSE
3712 l_error_type := null;
3713 l_value := null;
3714 END IF;
3715 --Nature and name of street
3716 archive_data( p_rubric_name => 'S41.G01.00.060.006'
3717 ,p_message_type => l_error_type
3718 ,p_id => p_org_id
3719 ,p_lookup_type => Null
3720 ,p_file_value => l_csr_asg_loc.street_name
3721 ,p_message_text => l_value
3722 ,p_id2 => l_id2
3723 ,p_rubric_type => l_conditional);
3724
3725 --INSEE Code of Town
3726 archive_data( p_rubric_name => 'S41.G01.00.060.007'
3727 ,p_message_type => NULL
3728 ,p_id => p_org_id
3729 ,p_lookup_type => Null
3730 ,p_file_value => l_csr_asg_loc.insee_code
3731 ,p_message_text => Null
3732 ,p_id2 => l_id2
3733 ,p_rubric_type => l_conditional);
3734
3735 --Name of the town
3736 IF ((l_est_location = l_asg_location) AND l_csr_asg_loc.small_town is not null) THEN
3737 l_error_type := l_error;
3738 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3739 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3740 'S41.G01.00.060.009'), null, null);
3741 ELSIF ((l_est_location <> l_asg_location)
3742 AND upper(l_csr_asg_loc.small_town) = upper(l_csr_asg_loc.town)) THEN
3743 l_error_type := l_error;
3744 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
3745 null, null, null);
3746 ELSE
3747 l_error_type := null;
3748 l_value := null;
3749 END IF;
3750 archive_data( p_rubric_name => 'S41.G01.00.060.009'
3751 ,p_message_type => l_error_type
3752 ,p_id => p_org_id
3753 ,p_lookup_type => Null
3754 ,p_file_value => l_csr_asg_loc.small_town
3755 ,p_message_text => l_value
3756 ,p_id2 => l_id2
3757 ,p_rubric_type => l_conditional);
3758
3759 --Zip Code
3760 IF ((l_est_location <> l_asg_location) AND l_csr_asg_loc.zip_code is null) THEN
3761 l_error_type := l_error;
3762 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
3763 'VALUE1:'||l_location_name, 'VALUE2:'||hr_general.decode_lookup
3764 ('FR_DADS_RUBRICS','S41.G01.00.060.010'), null);
3765 ELSIF ((l_est_location = l_asg_location) AND l_csr_asg_loc.zip_code is not null) THEN
3766 l_error_type := l_error;
3767 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3768 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3769 'S41.G01.00.060.010'), null, null);
3770 ELSE
3771 l_error_type := null;
3772 l_value := null;
3773 END IF;
3774 archive_data( p_rubric_name => 'S41.G01.00.060.010'
3775 ,p_message_type => l_error_type
3776 ,p_id => p_org_id
3777 ,p_lookup_type => Null
3778 ,p_file_value => l_csr_asg_loc.zip_code
3779 ,p_message_text => l_value
3780 ,p_id2 => l_id2
3781 ,p_rubric_type => l_mandatory);
3782
3783 --Town
3784 IF ((l_est_location <> l_asg_location) AND l_csr_asg_loc.town is null) THEN
3785 l_error_type := l_error;
3786 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
3787 'VALUE1:'||l_location_name, 'VALUE2:'||hr_general.decode_lookup
3788 ('FR_DADS_RUBRICS','S41.G01.00.060.012'), null);
3789 ELSIF ((l_est_location <> l_asg_location)
3790 AND l_csr_asg_loc.town <> upper(l_csr_asg_loc.town)) THEN
3791 l_error_type := l_error;
3792 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
3793 null, null, null);
3794 ELSIF ((l_est_location = l_asg_location) AND l_csr_asg_loc.town is not null) THEN
3795 l_error_type := l_error;
3796 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3797 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3798 'S41.G01.00.060.012'), null, null);
3799 ELSE
3800 l_error_type := null;
3801 l_value := null;
3802 END IF;
3803 archive_data(p_rubric_name => 'S41.G01.00.060.012'
3804 ,p_message_type => l_error_type
3805 ,p_id => p_org_id
3806 ,p_lookup_type => Null
3807 ,p_file_value => l_csr_asg_loc.town
3808 ,p_message_text => l_value
3809 ,p_id2 => l_id2
3810 ,p_rubric_type => l_mandatory);
3811
3812 --Country Code
3813 /* -- Country Code
3814 IF ((l_est_location = l_asg_location) AND l_country_code is not null) THEN
3815 l_error_type := l_error;
3816 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3817 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3818 'S41.G01.00.060.013'), null, null);
3819 ELSIF ((l_est_location <> l_asg_location) AND l_country_code = 'FR') THEN
3820 l_error_type := l_error;
3821 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
3822 'VALUE1: '||hr_general.decode_lookup
3823 ('FR_DADS_RUBRICS','S41.G01.00.060.013'), null, null);
3824 ELSE
3825 l_error_type := null;
3826 l_value := null;
3827 END IF;--COMMENT*/
3828 archive_data( p_rubric_name => 'S41.G01.00.060.013'
3829 ,p_message_type => NULL
3830 ,p_id => p_org_id
3831 ,p_lookup_type => Null
3832 ,p_file_value => Null
3833 ,p_message_text => Null
3834 ,p_id2 => l_id2
3835 ,p_rubric_type => l_conditional);
3836
3837 --Country Name
3838 IF ((l_est_location = l_asg_location) AND l_csr_asg_loc.country_name is not null) THEN
3839 l_error_type := l_error;
3840 l_value := pay_fr_general.get_payroll_message('PAY_75180_OMIT_VAL',
3841 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
3842 'S41.G01.00.060.014'), null, null);
3843 ELSIF (l_est_location <> l_asg_location) THEN
3844 IF l_csr_asg_loc.country_name is not null and l_country_code = 'FR' THEN
3845 l_error_type := l_error;
3846 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
3847 'VALUE1: '||hr_general.decode_lookup
3848 ('FR_DADS_RUBRICS','S41.G01.00.060.014'), null, null);
3849 ELSIF l_csr_asg_loc.country_name is null AND l_country_code is not null
3850 AND l_csr_asg_loc.country_code <> 'FR' THEN
3851 l_error_type := l_error;
3852 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
3853 'VALUE1:'||hr_general.decode_lookup
3854 ('FR_DADS_RUBRICS','S41.G01.00.060.014'), null, null);
3855 ELSE
3856 l_error_type := null;
3857 l_value := null;
3858 END IF;
3859 END IF;
3860 archive_data( p_rubric_name => 'S41.G01.00.060.014'
3861 ,p_message_type => l_error_type
3862 ,p_id => p_org_id
3863 ,p_lookup_type => Null
3864 ,p_file_value => l_csr_asg_loc.country_name
3865 ,p_message_text => l_value
3866 ,p_id2 => l_id2
3867 ,p_rubric_type => l_conditional);
3868
3869 --Total Address
3870 archive_data( p_rubric_name => 'S41.G01.00.060.015'
3871 ,p_message_type => NULL
3872 ,p_id => p_org_id
3873 ,p_lookup_type => Null --Item:S41.G01.00.019
3874 ,p_file_value => NULL
3875 ,p_message_text => Null
3876 ,p_id2 => l_id2
3877 ,p_rubric_type => l_optional);
3878 END IF;
3879 End If;
3880
3881 hr_utility.set_location('SAI S41 Leaving S41',116);
3882 hr_utility.set_location('S41 Reason end_reason:'||l_hist(l_counter).end_reason,50);
3883 hr_utility.set_location('S41 Reason end_date:'||l_hist(l_counter).end_date,50);
3884 hr_utility.set_location('S41 Reason start_reason:'||l_hist(l_counter).start_reason,51);
3885 hr_utility.set_location('S41 Reason start_date:'||l_hist(l_counter).start_date,52);
3886 End if;
3887 l_counter := l_hist.NEXT(l_counter);
3888 END LOOP;
3889 Exception
3890 when others then
3891 hr_utility.set_location('S41 Reason Codes:'||SQLERRM,109);
3892 End;
3893 l_hist.DELETE;
3894 hr_utility.set_location('S41 XXXXX Reached the end of the personid:'||p_person_id,7777);
3895 Exception
3896 When others then
3897 hr_utility.set_location('S41 sqlerrm:'||sqlerrm,1111);
3898 l_hist.DELETE;
3899 Raise;
3900
3901 END execS41_G01_00;
3902 --
3903
3904 --
3905
3906 Function fr_rolling_balance_pro (p_assignment_id in number,
3907 p_balance_name in varchar2,
3908 p_balance_start_date in date,
3909 p_balance_end_date in date) return number
3910 IS
3911 Cursor csr_pro_def_bal_id IS
3912 SELECT pdb.defined_balance_id
3913 FROM pay_balance_types pbt,
3914 pay_balance_dimensions pbd,
3915 pay_defined_balances pdb
3916 WHERE pdb.balance_type_id = pbt.balance_type_id
3917 AND pdb.balance_dimension_id = pbd.balance_dimension_id
3918 AND pbt.balance_name = p_balance_name
3919 AND pbd.database_item_suffix = '_ASG_PRO_RUN'
3920 AND pdb.legislation_code = 'FR';
3921 --
3922 --
3923 Cursor get_assign_actions IS
3924 select ppa.payroll_action_id,
3925 nvl(paa_pro.end_date, ppa.date_earned) date_earned,
3926 to_number(substr(max(lpad(paa.action_sequence,15,'0')||
3927 paa.assignment_action_id),16)) assignment_action_id
3928 from pay_assignment_actions paa,
3929 pay_assignment_actions paa_pro,
3930 pay_payroll_actions ppa
3931 where paa.assignment_id = p_assignment_id
3932 and paa_pro.assignment_action_id (+) = paa.source_action_id
3933 and paa_pro.end_date(+) is not null
3934 and paa.end_date is null
3935 and ppa.payroll_action_id = paa.payroll_action_id
3936 and ppa.action_status = 'C'
3937 /* exclude reversal results and reversed run results */
3938 and ppa.action_type In ('R', 'Q', 'I')
3939 and NOT EXISTS
3940 (SELECT NULL
3941 FROM pay_payroll_actions RPACT
3942 , pay_assignment_actions RASSACT
3943 , pay_action_interlocks RINTLK
3944 where paa.assignment_action_id = RINTLK.locked_action_id
3945 and RINTLK.locking_action_id = RASSACT.assignment_action_id
3946 and RPACT.payroll_action_id = RASSACT.payroll_action_id
3947 and RPACT.action_type = 'V')
3948 and ((paa_pro.end_date between p_balance_start_date and p_balance_end_date)
3949 or (paa_pro.end_date is null and
3950 ppa.date_earned between p_balance_start_date and p_balance_end_date))
3951 and (ppa.action_type = 'I' or paa.source_action_id is not null)
3952 group by ppa.payroll_action_id, nvl(paa_pro.end_date, ppa.date_earned);
3953
3954 l_defined_balance_id number;
3955 l_value number;
3956 l_proc varchar2(72);
3957
3958 BEGIN
3959 l_value := 0;
3960 l_proc := g_package||'.fr_rolling_balance';
3961 hr_utility.set_location('Entering:'|| l_proc,10);
3962 open csr_pro_def_bal_id;
3963 fetch csr_pro_def_bal_id into l_defined_balance_id;
3964 close csr_pro_def_bal_id;
3965 --
3966 For get_assign_rec In get_assign_actions
3967 Loop
3968 Begin
3969 l_value := l_value +pay_balance_pkg.get_value(p_defined_balance_id => l_defined_balance_id
3970 ,p_assignment_action_id => get_assign_rec.assignment_action_id);
3971 EXCEPTION
3972 WHEN NO_DATA_FOUND THEN
3973 l_value := 0;
3974 END;
3975 hr_utility.set_location(' BAL VAL='||l_value, 60);
3976 end loop;
3977 hr_utility.set_location(' FINAL BAL VAL='||l_value, 60);
3978 hr_utility.set_location(' Leaving:'||l_proc, 70);
3979 return l_value;
3980
3981 END fr_rolling_balance_pro ;
3982
3983 PROCEDURE archive_data(p_rubric_name VARCHAR2,
3984 p_message_type VARCHAR2,
3985 p_id NUMBER,
3986 p_lookup_type VARCHAR2,
3987 p_file_value VARCHAR2,
3988 p_message_text VARCHAR2,
3989 p_id2 VARCHAR2,
3990 p_rubric_type VARCHAR2) IS
3991 l_user_value Varchar2(100);
3992 l_file_value Varchar2(100);
3993 l_action_info_id pay_action_information.action_information_id%TYPE;
3994 l_ovn pay_action_information.object_version_number%TYPE;
3995 begin
3996
3997 hr_utility.set_location('p_message_type:'||p_message_type,1234);
3998 If p_message_type IS NULL Then
3999 hr_utility.set_location('p_message_type1:'||p_message_type,1234);
4000 hr_utility.set_location('archive_data:'||p_rubric_name,555);
4001 l_file_value := p_file_value;
4002 hr_utility.set_location('l_file_value:'||l_file_value,555);
4003 If p_lookup_type Is Not Null Then
4004 l_user_value := hr_general.decode_lookup(p_lookup_type, p_file_value);
4005 If p_rubric_name = 'S30.G01.00.007' Then
4006 If l_file_value = 'MR.' Then
4007 l_file_Value := '01';
4008 Elsif l_file_value = 'MRS.' Then
4009 l_file_Value := '02';
4010 Elsif l_file_value = 'MISS' Then
4011 l_file_Value := '03';
4012 End If;
4013 End if;
4014 Else
4015 l_user_value := Null;
4016 End if;
4017 hr_utility.set_location('ARCHIVE_DATA:'||l_user_value,555);
4018
4019 If p_rubric_name = 'S41.G01.00.005' Then
4020 pay_action_information_api.create_action_information( p_action_information_id => l_action_info_id
4021 ,p_action_context_id => g_assign_action_id
4022 ,p_action_context_type => 'AAP'
4023 ,p_object_version_number => l_ovn
4024 ,p_action_information_category => 'FR_DADS_FILE_DATA'
4025 ,p_action_information1 => p_rubric_name
4026 ,p_action_information2 => Null
4027 ,p_action_information3 => p_id
4028 ,p_action_information4 => l_file_value
4029 ,p_action_information5 => l_user_value
4030 ,p_action_information6 => Null
4031 ,p_action_information7 => g_param_estab_id
4032 ,p_action_information8 => p_id2
4033 ,p_action_information9 => p_rubric_type);
4034 Else
4035 pay_action_information_api.create_action_information( p_action_information_id => l_action_info_id
4036 ,p_action_context_id => g_assign_action_id
4037 ,p_action_context_type => 'AAP'
4038 ,p_object_version_number => l_ovn
4039 ,p_action_information_category => 'FR_DADS_FILE_DATA'
4040 ,p_action_information1 => p_rubric_name
4041 ,p_action_information2 => Null
4042 ,p_action_information3 => p_id
4043 ,p_action_information4 => l_file_value
4044 ,p_action_information5 => l_user_value
4045 ,p_action_information6 => Null
4046 ,p_action_information8 => p_id2
4047 ,p_action_information9 => p_rubric_type);
4048 End if;
4049 Elsif (p_message_type = hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W')) Then
4050 hr_utility.set_location('p_message_type2:'||p_message_type,1234);
4051 pay_action_information_api.create_action_information( p_action_information_id => l_action_info_id
4052 ,p_action_context_id => g_assign_action_id
4053 ,p_action_context_type => 'AAP'
4054 ,p_object_version_number => l_ovn
4055 ,p_action_information_category => 'FR_DADS_FILE_DATA'
4056 ,p_action_information1 => p_rubric_name
4057 ,p_action_information2 => p_message_type
4058 ,p_action_information3 => p_id
4059 ,p_action_information4 => p_file_value
4060 ,p_action_information5 => Null
4061 ,p_action_information6 => p_message_text
4062 ,p_action_information8 => p_id2
4063 ,p_action_information9 => p_rubric_type);
4064
4065 Elsif (p_message_type = hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E')) Then
4066 hr_utility.set_location('p_message_type3:'||p_message_type,1234);
4067 pay_action_information_api.create_action_information( p_action_information_id => l_action_info_id
4068 ,p_action_context_id => g_assign_action_id
4069 ,p_action_context_type => 'AAP'
4070 ,p_object_version_number => l_ovn
4071 ,p_action_information_category => 'FR_DADS_FILE_DATA'
4072 ,p_action_information1 => p_rubric_name
4073 ,p_action_information2 => p_message_type
4074 ,p_action_information3 => p_id
4075 ,p_action_information4 => null
4076 ,p_action_information5 => null
4077 ,p_action_information6 => p_message_text
4078 ,p_action_information8 => p_id2
4079 ,p_action_information9 => p_rubric_type);
4080 End If;
4081 Exception
4082 When others then
4083 hr_utility.set_location('ARCHIVE_DATA EXP:'||p_rubric_name||':'||sqlerrm,556);
4084 End archive_data;
4085
4086 --
4087
4088 End PAY_FR_DADS_EMP_PKG;