[Home] [Help]
PACKAGE BODY: APPS.PERFRPHR
Source
1 PACKAGE BODY PERFRPHR AS
2 /* $Header: perfrphr.pkb 120.2 2006/07/31 07:18:45 nmuthusa noship $ */
3
4 /* ------------------------------------------------------------------------- */
5 /* CONVERT_UPPERCASE-Converts French Accents to english uppercase characters */
6 /* ------------------------------------------------------------------------- */
7 FUNCTION convert_uppercase(p_input_string varchar2)
8 RETURN varchar2 IS
9 --
10 l_output_string varchar2(2000);
11
12 -- converts the french accented characters to American English
13 -- in uppercase, used for direct deposit mag tape data
14 cursor c_uppercase(cp_input_string varchar2) is
15 select
16 replace(
17 replace(
18 replace(
19 replace(
20 replace(
21 replace(
22 replace(
23 replace(
24 replace(
25 replace(
26 replace(
27 replace(
28 replace(
29 replace(
30 replace(convert(upper(cp_input_string),'UTF8'),
31 utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
32 ),
33 utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
34 ),
35 utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
36 ),
37 utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
38 ),
39 utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
40 ),
41 utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
42 ),
43 utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
44 ),
45 utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
46 ),
47 utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
48 ),
49 utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
50 ),
51 utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
52 ),
53 utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
54 ),
55 utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
56 ),
57 utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
58 ),
59 utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
60 )
61 from dual;
62
63 begin
64
65 open c_uppercase(p_input_string);
66 fetch c_uppercase into l_output_string;
67 if c_uppercase%NOTFOUND then
68 l_output_string := p_input_string;
69 end if;
70 close c_uppercase;
71
72 return l_output_string;
73
74 end convert_uppercase;
75
76 ----------------------------
77 FUNCTION get_emp_total (p_effective_date IN DATE,
78 p_est_id IN NUMBER,
79 p_ent_id IN NUMBER,
80 p_sex IN VARCHAR2,
81 p_udt_column IN VARCHAR2,
82 p_include_suspended IN VARCHAR2) RETURN NUMBER IS
83 --
84 CURSOR c_get_total IS
85 SELECT COUNT(asg.assignment_id)
86 FROM per_all_assignments_f asg,
87 per_assignment_status_types ast,
88 per_person_types_v pt,
89 per_all_people_f peo
90 WHERE asg.establishment_id IN (SELECT hr2.organization_id
91 FROM hr_organization_information hr1,
92 hr_organization_information hr2
93 WHERE hr1.organization_id = p_ent_id
94 AND hr1.org_information1 = hr2.org_information1
95 AND hr1.org_information_context = hr2.org_information_context
96 AND hr1.org_information_context = 'FR_ESTAB_INFO'
97 AND p_ent_id IS NOT NULL
98 UNION
99 SELECT NVL(p_est_id,0)
100 FROM DUAL
101 WHERE p_ent_id IS NULL)
102 AND NVL(peo.sex,'X') = NVL(p_sex,NVL(peo.sex,'X'))
103 AND asg.person_id = peo.person_id
104 AND ((ast.per_system_status = 'ACTIVE_ASSIGN' AND p_include_suspended = 'N') OR
105 (ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND p_include_suspended = 'Y'))
106 AND asg.assignment_status_type_id = ast.assignment_status_type_id
107 AND asg.primary_flag = 'Y'
108 AND peo.person_type_id = pt.person_type_id
109 AND 'Y' = pefrusdt.get_table_value(peo.business_group_id
110 ,'FR_USER_PERSON_TYPE'
111 ,p_udt_column
112 ,pt.user_person_type
113 ,p_effective_date)
114 AND p_effective_date >= asg.effective_start_date
115 AND p_effective_date <= asg.effective_end_date
116 AND p_effective_date >= peo.effective_start_date
117 AND p_effective_date <= peo.effective_end_date
118 AND (LEAST(asg.effective_end_date,peo.effective_end_date) > p_effective_date
119 OR EXISTS (SELECT null
120 FROM per_all_assignments_f asg2,
121 per_assignment_status_types ast2,
122 per_person_types_v pt2,
123 per_all_people_f peo2
124 WHERE asg2.establishment_id IN (SELECT hr2.organization_id
125 FROM hr_organization_information hr1,
126 hr_organization_information hr2
127 WHERE hr1.organization_id = p_ent_id
128 AND hr1.org_information1 = hr2.org_information1
129 AND hr1.org_information_context = hr2.org_information_context
130 AND hr1.org_information_context = 'FR_ESTAB_INFO'
131 AND p_ent_id IS NOT NULL
132 UNION
133 SELECT NVL(p_est_id,0)
134 FROM DUAL
135 WHERE p_ent_id IS NULL)
136 AND NVL(peo2.sex,'X') = NVL(p_sex,NVL(peo2.sex,'X'))
137 AND asg2.person_id = peo.person_id
138 AND asg2.person_id = peo2.person_id
139 AND ((ast2.per_system_status = 'ACTIVE_ASSIGN' AND p_include_suspended = 'N') OR
140 (ast2.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND p_include_suspended = 'Y'))
141 AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
142 AND asg2.primary_flag = 'Y'
143 AND peo2.person_type_id = pt2.person_type_id
144 AND 'Y' = pefrusdt.get_table_value(peo2.business_group_id
145 ,'FR_USER_PERSON_TYPE'
146 ,p_udt_column
147 ,pt2.user_person_type
148 ,p_effective_date)
149 AND p_effective_date+1 >= asg2.effective_start_date
150 AND p_effective_date+1 <= asg2.effective_end_date
151 AND p_effective_date+1 >= peo2.effective_start_date
152 AND p_effective_date+1 <= peo2.effective_end_date)
153 );
154 --
155 l_total NUMBER:=0;
156 --
157 BEGIN
158 --
159 OPEN c_get_total;
160 FETCH c_get_total INTO l_total;
161 CLOSE c_get_total;
162 --
163 RETURN l_total;
164 --
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 CLOSE c_get_total;
168 RETURN(0);
169 end get_emp_total;
170 ----------------------------
171 PROCEDURE run_pre_hire (errbuf OUT NOCOPY VARCHAR2,
172 retcode OUT NOCOPY NUMBER,
173 p_business_group_id IN NUMBER,
174 p_establishment_id IN NUMBER,
175 p_person_id IN NUMBER,
176 p_contact_name IN VARCHAR2,
177 p_contact_telephone IN VARCHAR2,
178 p_fax IN VARCHAR2,
179 p_email_address IN VARCHAR2,
180 p_dads IN VARCHAR2,
181 p_pmf5 IN VARCHAR2,
182 --p_prem IN VARCHAR2,
183 p_date IN VARCHAR2,
184 p_batch IN VARCHAR2,
185 p_acknowledgement IN VARCHAR2) IS
186 --
187 l_error BOOLEAN;
188 l_text VARCHAR2(2967);
189 l_error_text VARCHAR2(300);
190 l_trace VARCHAR2(80);
191 l_ent_total NUMBER;
192 l_est_total NUMBER;
193 l_french VARCHAR2(80);
194 l_nationality VARCHAR2(80);
195 l_fullname per_all_people_f.full_name%type;
196 l_medical VARCHAR2(80);
197 l_nationality_type VARCHAR2(80);
198 l_naf_meaning VARCHAR2(80);
199 l_coun_of_birth VARCHAR2(80);
200 l_urssaf_code VARCHAR2(80);
201 l_from_date DATE;
202 l_probation_period VARCHAR2(30);
203 l_working_hours VARCHAR2(30);
204 -- modified for numerical value error (bug#4106045)
205 l_units_in_days NUMBER;
206 l_units_in_weeks NUMBER;
207 --
208 l_identifier VARCHAR2(80);
209 l_date DATE;
210 l_monthly_hours VARCHAR2(80);
211 --
212 CURSOR c_country (b_country_code IN VARCHAR2) IS
213 SELECT UPPER(territory_short_name)
214 FROM fnd_territories_vl
215 WHERE territory_code = b_country_code;
216 --
217 CURSOR c_from_date (b_period_id IN NUMBER) IS
218 SELECT date_start
219 FROM per_periods_of_service
220 WHERE period_of_service_id = b_period_id;
221 --
222 CURSOR c_appl_date (b_appl_id IN NUMBER) IS
223 SELECT projected_hire_date
224 FROM per_applications
225 WHERE application_id = b_appl_id;
226 --
227 CURSOR c_urssaf_code (b_est_id IN NUMBER) IS
228 SELECT UPPER(hoi2.org_information1)
229 FROM hr_organization_information hoi,
230 hr_organization_information hoi2
231 WHERE hoi2.org_information_context = 'FR_URSSAF_CENTR_INFO'
232 AND hoi2.organization_id = hoi.org_information1
233 AND hoi.org_information_context = 'FR_ESTAB_URSSAF'
234 AND hoi.organization_id = b_est_id;
235 --
236 CURSOR c_app IS
237 SELECT /*+index(OI1 HR_ORGANIZATION_INFORMATIO_FK2)*/
238 ass.assignment_id ass_id,
239 ass.establishment_id est_id,
240 ass.organization_id org_id,
241 ass.person_id person_id,
242 ass.period_of_service_id,
243 ass.application_id,
244 UPPER(ass.time_normal_start) start_time,
245 UPPER(OI1.ORG_INFORMATION2) siret,
246 OI1.ORG_INFORMATION4 monthly_hours,
247 UPPER(OI1.ORG_INFORMATION3) naf_code,
248 UPPER(ou1.name) est_name,
249 UPPER(loc.address_line_1) loc_address1,
250 UPPER(loc.address_line_2) loc_address2,
251 -- Added for bug #5240132
252 UPPER(loc.address_line_3) loc_address3,
253 --
254 UPPER(loc.postal_code) loc_postal,
255 UPPER(loc.town_or_city) loc_town,
256 loc.telephone_number_1 loc_telephone,
257 UPPER(NVL(peo.per_information1,peo.last_name)) nom_de_naiss,
258 peo.last_name nom_marital,
259 UPPER(peo.first_name) app_first_name,
260 peo.full_name app_full_name,
261 peo.sex app_sex,
262 peo.national_identifier app_ni,
263 DECODE(ass.period_of_service_id,
264 NULL,peo.applicant_number,peo.employee_number) app_employee_number,
265 peo.date_of_birth app_dob,
266 peo.nationality app_nat_code,
267 peo.title app_tit,
268 peo.region_of_birth app_dept_code,
269 UPPER(peo.town_of_birth) app_town_birth,
270 peo.country_of_birth,
271 adr.address_line1 app_address1,
272 adr.address_line2 app_address2,
273 -- Added for bug #5240132
274 adr.address_line3 app_address3,
275 --
276 adr.postal_code app_pos_code,
277 adr.town_or_city app_town,
278 NVL(con.ctr_information6,to_char(ass.probation_period)) app_prob_period,
279 DECODE(con.ctr_information6,NULL,ass.probation_unit
280 ,con.ctr_information7) app_prob_unit,
281 -- modified for numerical value error (bug#4106045)
282 decode(con.ctr_information12, 'HOUR', con.ctr_information13, ass.frequency) frequency,
283 decode(con.ctr_information12, 'HOUR', con.ctr_information11, ass.normal_hours) app_hours,
284 --
285 -- modifying for bug#4083763
286 --DECODE(con.ctr_information2,'FIXED_TERM','D','PERMANENT','I') app_contract_cat,
287 DECODE(con.ctr_information2,'FIXED_TERM','1','PERMANENT','2','NEW_HIRE_CONTRACT','4', decode(OI1.ORG_INFORMATION8, 'Y', '3')) app_contract_cat,
288 --
289 TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY') proposed_end_date,
290 DECODE(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),NULL
291 ,TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY')
292 ,TO_CHAR(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),'DDMMYYYY')) con_end_date,
293 ext.rowid row_id,
294 ext.aei_information4 app_titulair,
295 ext.aei_information5 app_status_before_hire,
296 ext.aei_information6 app_training_level,
297 ext.aei_information7 app_proposed_monthly_salary,
298 ext.aei_information2 app_request_ni,
299 ext.aei_information8 app_assedic,
300 ext.aei_information9 app_pt_exon,
301 ext.aei_information10 app_inform_mt,
302 ext.aei_information1,
303 DECODE(ext.aei_information13,'FRF','F','EUR','E') ccy_code,
304 DECODE(ext.aei_information10,'Y',p_contact_name,' ') app_contact_name, -- work initiative contract
305 DECODE(ext.aei_information10,'Y',p_contact_telephone,' ') app_contact_telephone, -- work initiative contract
306 SUBSTR(job.job_information1,1,1) app_emp_code,
307 SUBSTR(job.job_information1,2,2) app_qual_code,
308 -- Added for bug #5240132
309 OI1.org_information20 app_corres_location_id
310 --
311 FROM per_addresses adr,
312 per_contracts_f con,
313 per_jobs job,
314 hr_locations_all loc,
315 hr_ALL_ORGANIZATION_UNITS OU1,
316 HR_ORGANIZATION_INFORMATION OI1,
317 per_assignment_extra_info ext,
318 per_all_assignments_f ass,
319 per_all_people_f peo
320 WHERE adr.person_id(+) = peo.person_id
321 AND adr.primary_flag(+) = 'Y'
322 AND con.contract_id(+) = ass.contract_id
323 AND l_date BETWEEN con.effective_start_date(+)
324 AND con.effective_end_date(+)
325 AND job.job_id(+) = ass.job_id
326 AND loc.location_id(+) = ou1.location_id
327 AND ass.establishment_id = ou1.organization_id
328 AND ext.aei_information11 = 'Y'
329 AND ass.assignment_id = ext.assignment_id
330 AND ext.information_type = 'FR_HIRING_DECLARATION'
331 AND ass.person_id = peo.person_id
332 AND l_date BETWEEN peo.effective_start_date AND peo.effective_end_date
333 and ass.establishment_id = p_establishment_id
334 AND (l_date BETWEEN ass.effective_start_date AND ass.effective_end_date)
335 and OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
336 AND OI1.ORG_INFORMATION_CONTEXT (+) = 'FR_ESTAB_INFO'
337 ORDER BY peo.last_name;
338
339
340
341 CURSOR c_app_by_person IS
342 SELECT /*+no_index(ass PER_ASSIGNMENTS_F_FK20)
343 index(OI1 HR_ORGANIZATION_INFORMATIO_FK2) */
344 ass.assignment_id ass_id,
345 ass.establishment_id est_id,
346 ass.organization_id org_id,
347 ass.person_id person_id,
348 ass.period_of_service_id,
349 ass.application_id,
350 UPPER(ass.time_normal_start) start_time,
351 UPPER(OI1.ORG_INFORMATION2) siret,
352 OI1.ORG_INFORMATION4 monthly_hours,
353 UPPER(OI1.ORG_INFORMATION3) naf_code,
354 UPPER(ou1.name) est_name,
355 UPPER(loc.address_line_1) loc_address1,
356 UPPER(loc.address_line_2) loc_address2,
357 -- Added for bug #5240132
358 UPPER(loc.address_line_3) loc_address3,
359 --
360 UPPER(loc.postal_code) loc_postal,
361 UPPER(loc.town_or_city) loc_town,
362 loc.telephone_number_1 loc_telephone,
363 UPPER(NVL(peo.per_information1,peo.last_name)) nom_de_naiss,
364 peo.last_name nom_marital,
365 UPPER(peo.first_name) app_first_name,
366 peo.full_name app_full_name,
367 peo.sex app_sex,
368 peo.national_identifier app_ni,
369 DECODE(ass.period_of_service_id,
370 NULL,peo.applicant_number,peo.employee_number) app_employee_number,
371 peo.date_of_birth app_dob,
372 peo.nationality app_nat_code,
373 peo.title app_tit,
374 peo.region_of_birth app_dept_code,
375 UPPER(peo.town_of_birth) app_town_birth,
376 peo.country_of_birth,
377 adr.address_line1 app_address1,
378 adr.address_line2 app_address2,
379 -- Added for bug #5240132
380 adr.address_line3 app_address3,
381 --
382 adr.postal_code app_pos_code,
383 adr.town_or_city app_town,
384 NVL(con.ctr_information6,to_char(ass.probation_period)) app_prob_period,
385 DECODE(con.ctr_information6,NULL,ass.probation_unit
386 ,con.ctr_information7) app_prob_unit,
387 -- modified for numerical value error (bug#4106045)
388 decode(con.ctr_information12, 'HOUR', con.ctr_information13, ass.frequency) frequency,
389 decode(con.ctr_information12, 'HOUR', con.ctr_information11, ass.normal_hours) app_hours,
390 --
391 -- modifying for bug#4083763
392 --DECODE(con.ctr_information2,'FIXED_TERM','D','PERMANENT','I') app_contract_cat,
393 DECODE(con.ctr_information2,'FIXED_TERM','1','PERMANENT','2','NEW_HIRE_CONTRACT','4', decode(OI1.ORG_INFORMATION8, 'Y', '3')) app_contract_cat,
394 --
395 TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY') proposed_end_date,
396 DECODE(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),NULL
397 ,TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY')
398 ,TO_CHAR(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),'DDMMYYYY')) con_end_date,
399 ext.rowid row_id,
400 ext.aei_information4 app_titulair,
401 ext.aei_information5 app_status_before_hire,
402 ext.aei_information6 app_training_level,
403 ext.aei_information7 app_proposed_monthly_salary,
404 ext.aei_information2 app_request_ni,
405 ext.aei_information8 app_assedic,
406 ext.aei_information9 app_pt_exon,
407 ext.aei_information10 app_inform_mt,
408 ext.aei_information1,
409 DECODE(ext.aei_information13,'FRF','F','EUR','E') ccy_code,
410 DECODE(ext.aei_information10,'Y',p_contact_name,' ') app_contact_name, -- work initiative contract
411 DECODE(ext.aei_information10,'Y',p_contact_telephone,' ') app_contact_telephone, -- work initiative contract
412 SUBSTR(job.job_information1,1,1) app_emp_code,
413 SUBSTR(job.job_information1,2,2) app_qual_code,
414 -- Added for bug #5240132
415 OI1.org_information20 app_corres_location_id
416 --
417 FROM per_addresses adr,
418 per_contracts_f con,
419 per_jobs job,
420 hr_locations_all loc,
421 hr_ALL_ORGANIZATION_UNITS OU1,
422 HR_ORGANIZATION_INFORMATION OI1,
423 per_assignment_extra_info ext,
424 per_all_assignments_f ass,
425 per_all_people_f peo
426 WHERE adr.person_id(+) = peo.person_id
427 AND adr.primary_flag(+) = 'Y'
428 AND con.contract_id(+) = ass.contract_id
429 AND l_date BETWEEN con.effective_start_date(+)
430 AND con.effective_end_date(+)
431 AND job.job_id(+) = ass.job_id
432 AND loc.location_id(+) = ou1.location_id
433 AND ass.establishment_id = ou1.organization_id
434 AND ext.aei_information11 = 'Y'
435 AND ass.assignment_id = ext.assignment_id
436 AND ext.information_type = 'FR_HIRING_DECLARATION'
437 AND ass.person_id = peo.person_id
438 AND l_date BETWEEN peo.effective_start_date AND peo.effective_end_date
439 and peo.person_id = p_person_id
440 AND (l_date BETWEEN ass.effective_start_date AND ass.effective_end_date)
441 and OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
442 AND OI1.ORG_INFORMATION_CONTEXT (+) = 'FR_ESTAB_INFO'
443 ORDER BY peo.last_name;
444
445 -- Added for bug #5240132
446 CURSOR c_estab_corres_addr (corres_loc_id IN NUMBER) IS
447 SELECT address_line_1,
448 address_line_2,
449 postal_code,
450 town_or_city,
451 telephone_number_1,
452 telephone_number_2
453 FROM hr_locations_all
454 WHERE location_id = corres_loc_id;
455 --
456 r_app c_app%ROWTYPE;
457 -- Added for bug #5240132
458 r_estab_addr c_estab_corres_addr%ROWTYPE;
459 --
460 ----------------------------
461 FUNCTION get_alternate_lookup (p_table_name IN VARCHAR2,
462 p_col_name IN VARCHAR2,
463 p_row_value IN VARCHAR2) RETURN VARCHAR2 IS
464 --
465 l_value VARCHAR2(80);
466 --
467 BEGIN
468 --
469 l_value := hruserdt.get_table_value
470 (p_bus_group_id => p_business_group_id
471 ,p_table_name => p_table_name
472 ,p_col_name => p_col_name
473 ,p_row_value => p_row_value
474 ,p_effective_date => l_date);
475
476 RETURN l_value;
477 --
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 RETURN NULL;
481 END get_alternate_lookup;
482 ----------------------------
483 ----------------------------
484 PROCEDURE write_error (p_field IN VARCHAR2) IS
485 --
486 BEGIN
487 --
488 l_trace := 'Writing to Error Log2';
489 --
490 IF l_error THEN
491 NULL;
492 ELSE
493 -- Set up error text for this person
494 fnd_message.set_name('PER','PER_74855_ASS_INV_RECORD');
495 fnd_message.set_token('FULL_NAME',l_fullname);
496 l_error_text := fnd_message.get;
497 fnd_file.put_line(fnd_file.log,null);
498 fnd_file.put_line(fnd_file.log,rpad('-',200,'-'));
499 fnd_file.put_line(FND_FILE.LOG,NULL);
500 fnd_file.put_line(FND_FILE.LOG,REPLACE(l_error_text,fnd_global.local_chr(13),NULL));
501 END IF;
502 --
503 fnd_file.put_line(FND_FILE.LOG,REPLACE(p_field,fnd_global.local_chr(13),NULL));
504 --
505 l_error := TRUE;
506 --
507 END write_error;
508 ----------------------------
509 PROCEDURE write_report (p_app in c_app%rowtype,
510 p_first_time in varchar2) IS
511 --
512 procedure write_column (p_column in varchar2) is
513 begin
514 fnd_file.put(FND_FILE.LOG,upper(p_column)||': ');
515 end write_column;
516 --
517 procedure write_field (p_field in varchar2) is
518 begin
519 fnd_file.put(FND_FILE.LOG,p_field||' ');
520 end write_field;
521 --
522 procedure seperator is
523 begin
524 fnd_file.put_line(fnd_file.log,null);
525 fnd_file.put_line(fnd_file.log,rpad('-',200,'-'));
526 end seperator;
527 --
528 procedure write_header is
529 begin
530 fnd_file.put_line(FND_FILE.LOG,NULL);
531 seperator;
532 fnd_file.put_line(FND_FILE.LOG,NULL);
533 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','HEADER'));
534 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_IDENT_EMT'));
535 write_field(l_identifier);
536 write_column(fnd_message.get_string('PER','PER_74856_NULL_URSSAF'));
537 write_field(l_urssaf_code);
538 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_AR_PAR_LOT'));
539 write_field(REPLACE(p_batch,'Y','O'));
540 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_FAX_AR_LOT'));
541 write_field(p_fax);
542 fnd_file.put_line(FND_FILE.LOG,NULL);
543 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_SUP_AR_LOT'));
544 write_field(p_acknowledgement);
545 write_column(fnd_message.get_string('PER','PER_74858_NULL_EMAIL'));
546 write_field(p_email_address);
547 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','D_CREATION'));
548 write_field(TO_CHAR(sysdate,'DDMMYYYY'));
549 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','H_CREATION'));
550 write_field(TO_CHAR(sysdate,'HHMI'));
551 fnd_file.put_line(FND_FILE.LOG,NULL);
552 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','EMPLOYER'));
553 write_column(fnd_message.get_string('PER','PER_74860_NULL_SIRET'));
554 write_field(p_app.siret);
555 write_column(fnd_message.get_string('PER','PER_74859_NULL_NAF'));
556 write_field(p_app.naf_code);
557 write_column(fnd_message.get_string('PER','PER_74856_NULL_URSSAF'));
558 write_field(l_urssaf_code);
559 fnd_file.put_line(FND_FILE.LOG,NULL);
560 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_RAISON_SOC_1'));
561 write_field(p_app.est_name);
562 write_column(fnd_message.get_string('PER','PER_74861_NULL_ADDRESS1'));
563 write_field(p_app.loc_address1);
564 fnd_file.put_line(FND_FILE.LOG,NULL);
565 write_column(fnd_message.get_string('PER','PER_74862_NULL_ADDRESS2'));
566 write_field(p_app.loc_address2);
567 write_column(fnd_message.get_string('PER','PER_74863_NULL_POST_CODE'));
568 write_field(p_app.loc_postal);
569 fnd_file.put_line(FND_FILE.LOG,NULL);
570 -- Added for bug #5240132
571 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_CITY_EMP'));
572 write_field(p_app.loc_town);
573 --
574 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_TEL_EMP'));
575 write_field(p_app.loc_telephone);
576 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_FAX_EMP'));
577 write_field(p_fax);
578 -- Added for bug #5240132
579 fnd_file.put_line(FND_FILE.LOG,NULL);
580 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','CORRESPOND_ADR'));
581 write_column(fnd_message.get_string('PER','PER_74861_NULL_ADDRESS1'));
582 write_field(r_estab_addr.address_line_1);
583 write_column(fnd_message.get_string('PER','PER_74862_NULL_ADDRESS2'));
584 write_field(r_estab_addr.address_line_2);
585 fnd_file.put_line(FND_FILE.LOG,NULL);
586 write_column(fnd_message.get_string('PER','PER_74863_NULL_POST_CODE'));
587 write_field(r_estab_addr.postal_code);
588 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_CITY_EMP'));
589 write_field(r_estab_addr.town_or_city);
590 fnd_file.put_line(FND_FILE.LOG,NULL);
591 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_TEL_EMP'));
592 write_field(r_estab_addr.telephone_number_1);
593 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_FAX_EMP'));
594 write_field(r_estab_addr.telephone_number_2);
595 --
596 end write_header;
597 --
598 BEGIN
599 IF p_first_time = 'Y' THEN
600 l_trace := 'Writing report header';
601 write_header;
602 ELSE
603 l_trace := 'Writing report body';
604 fnd_file.put_line(FND_FILE.LOG,NULL);
605 seperator;
606 fnd_file.put_line(FND_FILE.LOG,NULL);
607 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','EMPLOYEE'));
608 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','R_DOSSIER'));
609 write_field(p_app.app_employee_number);
610 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_NOM_PATRO_SAL'));
611 write_field(p_app.nom_de_naiss);
612 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_NOM_EPX_SAL'));
613 write_field(p_app.nom_marital);
614 fnd_file.put_line(FND_FILE.LOG,NULL);
615 write_column(fnd_message.get_string('PER','PER_74865_NULL_FIRST_NAME'));
616 write_field(p_app.app_first_name);
617 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_SEXE_SAL'));
618 write_field(p_app.app_sex);
619 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_SECU_SOC'));
620 write_field(p_app.app_ni);
621 fnd_file.put_line(FND_FILE.LOG,NULL);
622 write_column(fnd_message.get_string('PER','PER_74866_NULL_DOB'));
623 write_field(p_app.app_dob);
624 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_NATION_SAL'));
625 write_field(l_nationality);
626 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_LIEU_NAISS_SAL'));
627 write_field(NVL(p_app.app_town_birth,l_coun_of_birth));
628 fnd_file.put_line(FND_FILE.LOG,NULL);
629 write_column(fnd_message.get_string('PER','PER_74867_NULL_COUNTRY'));
630 write_field(l_coun_of_birth);
631 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_ADR_SAL_1'));
632 write_field(p_app.app_address1);
633 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_ADR_SAL_2'));
634 write_field(p_app.app_address2);
635 fnd_file.put_line(FND_FILE.LOG,NULL);
636 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_POST_SAL'));
637 write_field(p_app.app_pos_code);
638 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_BUR_DIST_SAL'));
639 write_field(p_app.app_town);
640 write_column(fnd_message.get_string('PER','PER_74868_NULL_HIRE_DATE'));
641 write_field(l_from_date);
642 write_column(fnd_message.get_string('PER','PER_74883_START_TIME'));
643 write_field(p_app.start_time);
644 fnd_file.put_line(FND_FILE.LOG,NULL);
645 fnd_file.put_line(FND_FILE.LOG,'---------------');
646 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','OTHER'));
647 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_MT_DCL'));
648 write_field(l_medical);
649 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_EFFECTIF_ETAB'));
650 write_field(l_est_total);
651 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_EFFECTIF_ENTR'));
652 write_field(l_ent_total);
653 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_EMPLOI'));
654 write_field(p_app.app_emp_code);
655 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_FORMATION'));
656 write_field(p_app.app_training_level);
657 fnd_file.put_line(FND_FILE.LOG,NULL);
658 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_QUALIF'));
659 write_field(p_app.app_qual_code);
660 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_AV_EMBAUCHE'));
661 write_field(p_app.app_status_before_hire);
662 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_JJ_ESSAI'));
663 write_field(l_probation_period);
664 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_HH_HEBDO'));
665 write_field(l_working_hours);
666 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','C_TYPE_CONTRAT'));
667 write_field(p_app.app_contract_cat);
668 fnd_file.put_line(FND_FILE.LOG,NULL);
669 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','D_FIN_CDD'));
670 write_field(p_app.con_end_date);
671 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','D_NON_EMBAUCHE'));
672 write_field(p_app.proposed_end_date);
673 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_SALAIRE_MM'));
674 write_field(to_char(ROUND(p_app.app_proposed_monthly_salary)));
675 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_HH_TRAVAIL'));
676 write_field(l_monthly_hours);
677 fnd_file.put_line(FND_FILE.LOG,NULL);
678 fnd_file.put_line(FND_FILE.LOG,'---------------');
679 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','REGISTRATION'));
680 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_AGENT_TITU'));
681 write_field(p_app.app_titulair);
682 fnd_file.put_line(FND_FILE.LOG,NULL);
683 fnd_file.put_line(FND_FILE.LOG,'---------------');
684 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','ADMIN'));
685 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_PREMIERE_EMB'));
686 write_field('N');
687 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_CERTIF'));
688 write_field(REPLACE(p_dads,'Y','O'));
689 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_CHOM'));
690 write_field(p_app.app_assedic);
691 fnd_file.put_line(FND_FILE.LOG,NULL);
692 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_IMMA'));
693 write_field(p_app.app_request_ni);
694 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_MT'));
695 write_field(p_app.app_inform_mt);
696 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_PMF5'));
697 write_field(REPLACE(p_pmf5,'Y','O'));
698 fnd_file.put_line(FND_FILE.LOG,NULL);
699 -- Removed for bug#4083763
700 --write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_PREM'));
701 --write_field(REPLACE(p_prem,'Y','O'));
702 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','I_TP'));
703 write_field(p_app.app_pt_exon);
704 fnd_file.put_line(FND_FILE.LOG,NULL);
705 fnd_file.put_line(FND_FILE.LOG,'---------------');
706 fnd_file.put_line(FND_FILE.LOG,hr_general.decode_lookup('FR_DUE_FIELDS','CONTACT'));
707 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','L_NOM_RESP'));
708 write_field(p_contact_name);
709 write_column(hr_general.decode_lookup('FR_DUE_FIELDS','N_TEL_RESP'));
710 write_field(p_contact_telephone);
711 END IF;
712 END write_report;
713 ----------------------------
714 PROCEDURE validate_header IS
715 --
716 l_error VARCHAR2(100);
717 --
718 BEGIN
719 --
720 IF l_urssaf_code IS NULL THEN
721 write_error(p_field => fnd_message.get_string('PER','PER_74856_NULL_URSSAF'));
722 END IF;
723 IF p_acknowledgement = 'E' AND p_email_address IS NULL THEN
724 write_error(p_field => fnd_message.get_string('PER','PER_74858_NULL_EMAIL'));
725 END IF;
726 --
727 END validate_header;
728 ----------------------------
729 PROCEDURE validate_record (p_app IN c_app%ROWTYPE) IS
730 --
731 BEGIN
732 --
733 IF p_app.siret IS NULL THEN
734 write_error(p_field => fnd_message.get_string('PER','PER_74860_NULL_SIRET'));
735 END IF;
736 --
737 IF p_app.naf_code IS NULL THEN
738 write_error(p_field => fnd_message.get_string('PER','PER_74859_NULL_NAF'));
739 END IF;
740 --
741 IF p_app.loc_address1 IS NULL THEN
742 write_error(p_field => fnd_message.get_string('PER','PER_74861_NULL_ADDRESS1'));
743 END IF;
744 --
745 IF p_app.loc_postal IS NULL THEN
746 write_error(p_field => fnd_message.get_string('PER','PER_74863_NULL_POST_CODE'));
747 END IF;
748 --
749 IF p_app.loc_town IS NULL THEN
750 write_error(p_field => fnd_message.get_string('PER','PER_74864_NULL_TOWN'));
751 END IF;
752 --
753 IF p_app.app_first_name IS NULL THEN
754 write_error(p_field => fnd_message.get_string('PER','PER_74865_NULL_FIRST_NAME'));
755 END IF;
756 --
757 IF p_app.app_dob IS NULL THEN
758 write_error(p_field => fnd_message.get_string('PER','PER_74866_NULL_DOB'));
759 END IF;
760 --
761 IF l_coun_of_birth IS NULL THEN
762 write_error(p_field => fnd_message.get_string('PER','PER_74867_NULL_COUNTRY'));
763 END IF;
764 --
765 IF l_from_date IS NULL THEN
766 write_error(p_field => fnd_message.get_string('PER','PER_74868_NULL_HIRE_DATE'));
767 END IF;
768 --
769 IF p_app.start_time IS NULL THEN
770 write_error(p_field => fnd_message.get_string('PER','PER_74883_START_TIME'));
771 END IF;
772 --
773 IF p_app.app_nat_code IS NULL THEN
774 write_error(p_field => hr_general.decode_lookup('FR_DUE_FIELDS','L_NATION_SAL'));
775 END IF;
776 --
777 -- Added for bug#4083763
778 -- as L_IDENT_EMT is manadatory
779 IF l_identifier IS NULL THEN
780 write_error(p_field => hr_general.decode_lookup('FR_DUE_FIELDS','L_IDENT_EMT'));
781 END IF;
782 --
783 END validate_record;
784 ----------------------------
785 /********************-----*==*==*----*************************/
786 /********************---*==MAIN==*---*************************/
787 /********************-----*==*==*----*************************/
788 BEGIN
789 --
790 l_date := fnd_date.canonical_to_date(p_date);
791 IF p_person_id IS NULL AND p_establishment_id IS NULL THEN
792 l_trace := 'Writing to error log1';
793 fnd_file.put_line(FND_FILE.LOG,REPLACE(fnd_message.get_string('PER','PER_74857_NULL_PERSON_ESTAB'),fnd_global.local_chr(13),NULL));
794 l_error := TRUE;
795 END IF;
796 --
797 --FOR r_app IN c_app LOOP
798 --
799 /*** Previous cursor was split into two cursors depending upon p_person_id ***/
800 /*** Bug 2662236 ***/
801
802 IF p_person_id IS NULL THEN
803 OPEN c_app;
804 ELSE
805 OPEN c_app_by_person;
806 END IF;
807
808 LOOP
809 IF c_app%ISOPEN THEN
810 FETCH c_app INTO r_app;
811 EXIT WHEN c_app%NOTFOUND;
812 ELSIF c_app_by_person%ISOPEN THEN
813 FETCH c_app_by_person INTO r_app;
814 EXIT WHEN c_app_by_person%NOTFOUND;
815 END IF;
816 -- Added for bug #5240132
817 -- Get correspondence address of employer
818 OPEN c_estab_corres_addr(r_app.app_corres_location_id);
819 FETCH c_estab_corres_addr INTO r_estab_addr;
820 CLOSE c_estab_corres_addr;
821 --
822 /* Reset variables */
823 l_error := FALSE;
824 l_ent_total := 0;
825 l_est_total := 0;
826 l_from_date := NULL;
827 l_coun_of_birth := NULL;
828 l_fullname := r_app.app_full_name;
829 --
830 IF (c_app%ISOPEN AND c_app%ROWCOUNT = 1) OR
831 (c_app_by_person%ISOPEN AND c_app_by_person%ROWCOUNT = 1) THEN
832 --
833 l_identifier := 'ORACLEHRMS';
834 --
835 /* Get the URSSAF code for the establishment */
836 OPEN c_urssaf_code(r_app.est_id);
837 FETCH c_urssaf_code INTO l_urssaf_code;
838 CLOSE c_urssaf_code;
839 --
840 /*If NAF code is more than 4 chars then strip out any non-alphanumerics*/
841 IF length(r_app.naf_code) > 4 THEN
842 r_app.naf_code := '*'||UPPER(r_app.naf_code);
843 r_app.naf_code := REPLACE(TRANSLATE(r_app.naf_code,TRANSLATE(r_app.naf_code,'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789','*'),'*'),'*',null);
844 END IF;
845 --
846 l_trace := 'Validating Header';
847 validate_header;
848 --
849 IF l_error THEN
850 exit; --stop processing as header is invalid
851 END IF;
852 --
853 write_report(r_app,'Y');
854 l_trace := 'Writing header complete';
855 --
856 END IF;
857 --
858 /* Work out from which date to start counting(the actual hire date) from */
859 IF r_app.period_of_service_id IS NULL THEN
860 OPEN c_appl_date(r_app.application_id);
861 FETCH c_appl_date INTO l_from_date;
862 CLOSE c_appl_date;
863 ELSE
864 OPEN c_from_date(r_app.period_of_service_id);
865 FETCH c_from_date INTO l_from_date;
866 CLOSE c_from_date;
867 END IF;
868 --
869 l_trace := 'Calculating establishment totals';
870 /* Get the totals for enterprise and establishment */
871 l_est_total := perfrphr.get_emp_total(p_effective_date => l_from_date-1,
872 p_est_id => r_app.est_id,
873 p_udt_column => 'INCLUDE_DUE');
874 l_ent_total := perfrphr.get_emp_total(p_effective_date => l_from_date-1,
875 p_ent_id => r_app.est_id,
876 p_udt_column => 'INCLUDE_DUE');
877 --
878 /* Get the country of birth */
879 OPEN c_country(r_app.country_of_birth);
880 FETCH c_country INTO l_coun_of_birth;
881 CLOSE c_country;
882 --
883 l_trace := 'Validating Record';
884 /* Validate the record */
885 validate_record(p_app => r_app); -- check for nulls;
886 --
887 IF l_error THEN -- If no error write the record, otherwise go to next record
888 null;
889 ELSE
890 --
891 /* Get the medical code */
892 l_medical := hr_general.decode_lookup(p_lookup_type => 'FR_WORK_MEDICAL_SERVICE',
893 p_lookup_code => r_app.aei_information1);
894 --
895 IF l_medical IS NULL THEN
896 l_medical := ' ';
897 ELSE
898 l_medical := 'MT'||r_app.aei_information1;
899 END IF;
900 --
901 /* Get the nationality */
902 l_nationality := hr_general.decode_lookup(p_lookup_type => 'NATIONALITY',
903 p_lookup_code => r_app.app_nat_code);
904 --
905 /* Get the nationality type */
906 l_trace := 'Nationality - Alternate Lookup';
907 l_french := get_alternate_lookup
908 (p_table_name => 'NATIONALITY'
909 ,p_col_name => 'DUE_NATIONALITY'
910 ,p_row_value => r_app.app_nat_code);
911 --
912 IF l_french = 'FR' THEN
913 l_nationality_type := 'F';
914 ELSIF l_french = 'EU' THEN
915 l_nationality_type := 'E';
916 ELSIF l_french IS NULL AND l_nationality IS NULL THEN
917 l_nationality_type := ' ';
918 ELSE
919 l_nationality_type := 'A';
920 END IF;
921 --
922 /* Work out how many days make up a week/month/year then multiply that by the probation period value */
923 l_trace := 'Calculating probation period';
924 -- modified for numerical value error (bug#4106045)
925 l_units_in_days := fnd_number.canonical_to_number(get_alternate_lookup
926 (p_table_name => 'QUALIFYING_UNITS'
927 ,p_col_name => 'DUE_UNITS_IN_DAYS'
928 ,p_row_value => r_app.app_prob_unit));
929 --
930 IF l_units_in_days IS NULL THEN
931 l_probation_period := NULL;
932 ELSE
933 -- modified for numerical value error (bug#4106045)
934 l_probation_period := fnd_number.number_to_canonical(r_app.app_prob_period*l_units_in_days);
935 END IF;
936 --
937 /* Work out how many weeks make up a day/week/month/year then multiply that by the normal working hours value */
938 l_trace := 'Calculating normal working hours';
939 -- modified for numerical value error (bug#4106045)
940 l_units_in_weeks := fnd_number.canonical_to_number(get_alternate_lookup
941 (p_table_name => 'FREQUENCY'
942 ,p_col_name => 'DUE_FREQUENCY_IN_WEEKS'
943 ,p_row_value => r_app.frequency));
944 --
945 IF l_units_in_weeks IS NULL THEN
946 l_working_hours := NULL;
947 ELSE
948 -- modified for numerical value error (bug#4106045)
949 l_working_hours := fnd_number.number_to_canonical(round(r_app.app_hours*l_units_in_weeks));
950 END IF;
951 --
952 /*Get the NAF meaning*/
953 l_naf_meaning := hr_general.decode_lookup(p_lookup_type => 'FR_NAF_CODE',
954 p_lookup_code => r_app.naf_code);
955 --
956 /*Get rid of characters from start_time field*/
957 r_app.start_time := REPLACE(TRANSLATE(r_app.start_time,TRANSLATE(r_app.start_time,'0123456789','*'),'*'),'*',null);
958 --
959 /* Change all the Y's to O's - hard-coded translation for y (yes) */
960 r_app.app_inform_mt := REPLACE(r_app.app_inform_mt,'Y','O');
961 r_app.app_request_ni := REPLACE(r_app.app_request_ni,'Y','O');
962 r_app.app_assedic := REPLACE(r_app.app_assedic,'Y','O');
963 r_app.app_titulair := REPLACE(r_app.app_titulair,'Y','O');
964 --
965 l_trace := 'Monthly Hours';
966 /* Round the monthly hours worked to nearest integer*/
967 l_monthly_hours := ROUND(fnd_number.canonical_to_number(r_app.monthly_hours));
968 --
969 l_trace := 'Building Output String';
970 /* Build up the text string for output */
971 l_text := RPAD(l_identifier,14) -- Identification
972 ||RPAD('DUE',4) -- Text
973 ||RPAD(l_urssaf_code,3) -- Code of URSSAF centre
974 ||RPAD(REPLACE(p_batch,'Y','O'),1) -- I_AR_PAR_LOT
975 ||RPAD(NVL(p_fax,' '),11) -- Employers fax number
976 ||RPAD(p_acknowledgement,1) -- Type of Acknowledgement
977 ||RPAD(NVL(p_email_address,' '),30) -- Employers email address
978 ||RPAD('912',8) -- Length of message
979 ||RPAD('100',3) -- Message version
980 ||RPAD(' ',2) -- Batch Process Error Code filled by URSSAF
981 ||RPAD(r_app.ass_id,15) -- User defined assignment id
982 ||RPAD(TO_CHAR(l_date,'DDMMYYYY'),15) -- User defined l_date
983 ||RPAD(r_app.person_id,14) -- User defined Person Id
984 ||TO_CHAR(sysdate,'DDMMYYYY') -- Creation Date
985 ||TO_CHAR(sysdate,'HHMI') -- Creation Hour
986 ||RPAD(l_urssaf_code,3) -- Code of URSSAF attached to establishment
987 ||RPAD(r_app.siret,14)
988 ||'M' -- Moral (or Physical) Employer
989 ||RPAD(convert_uppercase(r_app.est_name),32)
990 ||RPAD(r_app.naf_code,4)
991 ||RPAD(convert_uppercase(r_app.loc_address1),32)
992 -- Modifying for bug #5240132
993 --||RPAD(convert_uppercase(NVL(r_app.loc_address2,' ')),32)
994 || RPAD(convert_uppercase(NVL((r_app.loc_address2||' ' || r_app.loc_address3),' ') ),32)
995 --
996 ||RPAD(r_app.loc_postal,5)
997 ||RPAD(convert_uppercase(r_app.loc_town),27)
998 ||RPAD(convert_uppercase(r_app.nom_de_naiss),32)
999 ||RPAD(convert_uppercase(r_app.app_first_name),32)
1000 ||RPAD(NVL(r_app.app_ni,' '),13)
1001 ||RPAD(TO_CHAR(r_app.app_dob,'DDMMYYYY'),8)
1002 ||RPAD(convert_uppercase(NVL(r_app.app_town_birth,l_coun_of_birth)),24)
1003 ||RPAD(convert_uppercase(l_coun_of_birth),20)
1004 ||RPAD(TO_CHAR(l_from_date,'DDMMYYYY'),8)
1005 ||RPAD(r_app.start_time,4)
1006 ||RPAD(' ',5) -- R_DOSSIER Employee Number
1007 ||RPAD(' ',2) --Acknowledgement of receipt return code (from URSSAF)
1008 ||RPAD(NVL(l_naf_meaning,' '),70) --Type of activity of company
1009 ||RPAD(' ',4)
1010 ||RPAD(' ',4) --Title
1011 ||RPAD(' ',32) --Employers Name
1012 ||RPAD(NVL(r_app.loc_telephone,' '),11)
1013 ||RPAD(NVL(p_fax,' '),11) --Fax
1014 -- Modifying for bug #5240132
1015 --||RPAD(' ',32) --Employer correspondance address line1
1016 ||RPAD(convert_uppercase(NVL(r_estab_addr.address_line_1,' ')),32)
1017 --||RPAD(' ',32) --Employer correspondance address line2
1018 ||RPAD(convert_uppercase(NVL(r_estab_addr.address_line_2,' ')),32)
1019 --||RPAD(' ',5) --Employer correspondance post code
1020 ||RPAD(NVL(r_estab_addr.postal_code,' '),5)
1021 --||RPAD(' ',27) --Employer correspondance distribution centre
1022 ||RPAD(convert_uppercase(NVL(r_estab_addr.town_or_city,' ')),27)
1023 --
1024 ||'I' --Fixed value 'I' for email transmission
1025 ||RPAD(' ',38) --Reserved by URSSAF
1026 ||RPAD(convert_uppercase(r_app.nom_marital),32)
1027 ||SUBSTR(r_app.app_sex,1,1)
1028 ||RPAD(convert_uppercase(NVL(r_app.app_address1,' ')),32)
1029 -- Modifying for bug #5240132
1030 --||RPAD(convert_uppercase(NVL(r_app.app_address2,' ')),32)
1031 || RPAD(convert_uppercase(NVL((r_app.app_address2||' ' || r_app.app_address3),' ') ),32)
1032 --
1033 ||RPAD(NVL(r_app.app_pos_code,' '),5)
1034 ||RPAD(convert_uppercase(NVL(r_app.app_town,' ')),27) --Distribution Centre
1035 ||RPAD(NVL(r_app.app_dept_code,' '),2)
1036 ||RPAD(convert_uppercase(NVL(l_nationality,' ')),32)
1037 ||RPAD(NVL(r_app.app_training_level,' '),1)
1038 ||RPAD(NVL(r_app.app_emp_code,' '),1)
1039 ||RPAD(NVL(r_app.app_qual_code,' '),1)
1040 ||RPAD(NVL(r_app.app_contract_cat,' '),1)
1041 ||RPAD(NVL(r_app.con_end_date,' '),8)
1042 ||RPAD(NVL(r_app.proposed_end_date,' '),8)
1043 ||RPAD(NVL(l_working_hours,' '),3)
1044 ||RPAD(NVL(l_probation_period,' '),3)
1045 ||RPAD(NVL(to_char(ROUND(r_app.app_proposed_monthly_salary)),' '),6)
1046 ||RPAD(NVL(r_app.app_contact_name,' '),32)
1047 ||RPAD(NVL(r_app.app_contact_telephone,' '),11)
1048 ||RPAD(NVL(r_app.app_status_before_hire,' '),1)
1049 ||RPAD(l_medical,10)
1050 ||RPAD(NVL(l_monthly_hours,' '),4) -- Hours worked in a month
1051 ||RPAD(' ',1) -- Reserved by URSSAF
1052 ||RPAD(l_est_total,5)
1053 ||RPAD(l_ent_total,5)
1054 ||l_nationality_type
1055 ||RPAD(NVL(r_app.app_titulair,' '),1)
1056 ||'M' -- Working Time Unit (Fixed): Month(M)
1057 ||RPAD(NVL(r_app.ccy_code,' '),1) -- Currency code for monthly salary
1058 ||RPAD(' ',2) -- Reserved by URSSAF
1059 ||'N' -- Indicates whether first employee within company
1060 ||RPAD(' ',1) -- Reserved by URSSAF
1061 ||RPAD(NVL(REPLACE(p_dads,'Y','O'),' '),1) -- I_certif
1062 ||RPAD(' ',1) -- Reserved by URSSAF
1063 ||RPAD(NVL(r_app.app_assedic,' '),1) -- I_CHOM
1064 ||RPAD(NVL(r_app.app_request_ni,' '),1) -- I_IMMA
1065 ||RPAD(NVL(r_app.app_inform_mt,' '),1) -- I_MT
1066 ||RPAD(NVL(REPLACE(p_pmf5,'Y','O'),' '),1) -- I_PMF5
1067 -- Removed for bug#4083763
1068 --||RPAD(NVL(REPLACE(p_prem,'Y','O'),' '),1) -- I_PREM
1069 ||RPAD(' ',1) -- for replacing the above column
1070 --
1071 ||RPAD(NVL(r_app.app_pt_exon,' '),1) -- I_TP
1072 ||RPAD(' ',11); -- Reserved by URSSAF
1073 --
1074 fnd_file.put_line(FND_FILE.OUTPUT,l_text);
1075 --
1076 write_report(r_app,'N');
1077 --
1078 l_trace := 'Updating per_assignment_extra_info';
1079 UPDATE per_assignment_extra_info
1080 SET aei_information11 = 'N',
1081 aei_information12 = fnd_date.date_to_canonical(sysdate)
1082 WHERE rowid = r_app.row_id;
1083 --
1084 END IF; --l_error
1085 --
1086 END LOOP;
1087 IF c_app%ISOPEN THEN
1088 CLOSE c_app;
1089 ELSIF c_app_by_person%ISOPEN THEN
1090 CLOSE c_app_by_person;
1091 END IF;
1092
1093 --
1094 retcode := 0 ;
1095 --
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 retcode:=2;
1099 errbuf := l_trace||' '||SQLERRM;
1100 ROLLBACK;
1101 END run_pre_hire;
1102
1103 end PERFRPHR;