[Home] [Help]
PACKAGE BODY: APPS.PER_FR_ELECTION
Source
1 package body per_fr_election as
2 /* $Header: pefrphmm.pkb 115.6 2003/08/04 03:17:59 abhaduri ship $ */
3 Procedure process(errbuf OUT NOCOPY VARCHAR2,
4 retcode OUT NOCOPY NUMBER,
5 p_company_id IN NUMBER,
6 p_establishment_id IN NUMBER,
7 p_reporting_est_id IN NUMBER,
8 p_effective_date IN VARCHAR2) is
9 l_effective_date date;
10 --
11 l_est_head_title varchar2(1000);
12 l_est_head_underline varchar2(1000);
13 l_est_head_rec varchar2(1000);
14 l_est_head_log varchar2(1000);
15 --
16 l_person_title varchar2(1000);
17 l_person_underline varchar2(1000);
18 l_person_rec varchar2(1000);
19 l_person_log varchar2(1000);
20 --
21 l_rep_est_title varchar2(1000);
22 l_rep_est_underline varchar2(1000);
23 --
24 l_start_rec varchar2(1000);
25 l_est_footer_rec varchar2(1000);
26 l_end_rec varchar2(1000);
27 --
28 l_person_count number;
29 l_error_count number;
30 l_warning_count number;
31 l_error_flag varchar2(30);
32 l_warning_flag varchar2(30);
33 --
34 REPORTING_SIRET_LEN number := 14;
35 SIRET_LEN number := 14;
36 NAF_CODE_LEN number := 4;
37 ESTAB_NAME_LEN number := 50;
38 ADDRESS_LINE1_LEN number := 32;
39 ADDRESS_LINE2_LEN number := 32;
40 INSEE_CODE_LEN number := 5;
41 TOWN_LEN number := 26;
42 POSTAL_CODE_LEN number := 5;
43 CITY_LEN number := 26;
44 EMPLOYER_TYPE_LEN number := 1;
45 SS_NUMBER_LEN number := 13;
46 SS_KEY_LEN number := 2;
47 DATE_OF_BIRTH_LEN number := 6;
48 DEPARTMENT_OF_BIRTH_LEN number := 2;
49 PLACE_OF_BIRTH_LEN number := 26;
50 BIRTH_NAME_LEN number := 30;
51 FIRST_NAME_LEN number := 20;
52 LAST_NAME_LEN number := 30;
53 ELECTORAL_COLLEGE_LEN number := 1;
54 SECTION_LEN number := 1;
55 VOTING_PLACE_LEN number := 1;
56 --
57 REPORTING_SIRET varchar2(80);
58 SIRET varchar2(80);
59 NAF_CODE varchar2(80);
60 ESTAB_NAME varchar2(80);
61 ADDRESS_LINE2 varchar2(80);
62 ADDRESS_LINE1 varchar2(80);
63 INSEE_CODE varchar2(80);
64 TOWN varchar2(80);
65 POSTAL_CODE varchar2(80);
66 CITY varchar2(80);
67 EMPLOYER_TYPE varchar2(80);
68 SS_NUMBER varchar2(80);
69 SS_KEY varchar2(80);
70 DATE_OF_BIRTH varchar2(80);
71 DEPARTMENT_OF_BIRTH varchar2(80);
72 PLACE_OF_BIRTH varchar2(80);
73 BIRTH_NAME varchar2(80);
74 FIRST_NAME varchar2(80);
75 LAST_NAME varchar2(80);
76 ELECTORAL_COLLEGE varchar2(80);
77 SECTION varchar2(80);
78 VOTING_PLACE varchar2(80);
79 --
80 REPORTING_SIRET_LOG number;
81 SIRET_LOG number;
82 NAF_CODE_LOG number;
83 ESTAB_NAME_LOG number;
84 ADDRESS_LINE2_LOG number;
85 ADDRESS_LINE1_LOG number;
86 INSEE_CODE_LOG number;
87 TOWN_LOG number;
88 POSTAL_CODE_LOG number;
89 CITY_LOG number;
90 EMPLOYER_TYPE_LOG number;
91 SS_NUMBER_LOG number;
92 SS_KEY_LOG number;
93 DATE_OF_BIRTH_LOG number;
94 DEPARTMENT_OF_BIRTH_LOG number;
95 PLACE_OF_BIRTH_LOG number;
96 BIRTH_NAME_LOG number;
97 FIRST_NAME_LOG number;
98 LAST_NAME_LOG number;
99 ELECTORAL_COLLEGE_LOG number;
100 SECTION_LOG number;
101 VOTING_PLACE_LOG number;
102 --
103 Cursor get_reporting_establishment is
104 Select ORG_INFORMATION2 SIRET
105 From hr_organization_information
106 Where organization_id = p_reporting_est_id
107 and org_information_context = 'FR_ESTAB_INFO';
108 --
109 Cursor get_establishments is
110 Select rpad(substr(ORG.NAME,1,ESTAB_NAME_LEN),ESTAB_NAME_LEN,' ') ESTAB_NAME
111 , ORG.ORGANIZATION_ID ESTABLISHMENT_ID
112 , EST.ORG_INFORMATION2 SIRET
113 , rpad(nvl(substr(EST.ORG_INFORMATION3,1,NAF_CODE_LEN),' '),NAF_CODE_LEN,' ') NAF_CODE
114 , decode(EST.ORG_INFORMATION7,'INDUSTRY','1',
115 'COMMERCE','2',
116 'AGRICULTURAL','3',
117 'MANAGEMENT','5','4') SECTION
118 , rpad(nvl(substr(LOC.ADDRESS_LINE_1,1,ADDRESS_LINE1_LEN),' '),ADDRESS_LINE1_LEN,' ') ADDRESS_LINE1
122 , rpad(nvl(substr(LOC.POSTAL_CODE,1,POSTAL_CODE_LEN),' '),POSTAL_CODE_LEN,' ') POSTAL_CODE
119 , rpad(nvl(substr(LOC.ADDRESS_LINE_2,1,ADDRESS_LINE2_LEN),' '),ADDRESS_LINE2_LEN,' ') ADDRESS_LINE2
120 , rpad(nvl(substr(LOC.REGION_2,1,INSEE_CODE_LEN),' '),INSEE_CODE_LEN,' ') INSEE_CODE
121 , rpad(nvl(substr(LOC.REGION_3,1,TOWN_LEN),' '),TOWN_LEN,' ') TOWN
123 , rpad(nvl(substr(LOC.TOWN_OR_CITY,1,CITY_LEN),' '),CITY_LEN,' ') CITY
124 From hr_organization_information est
125 , hr_all_organization_units org
126 , hr_locations loc
127 Where ((p_establishment_id is not null
128 And est.organization_id = p_establishment_id)
129 Or (p_establishment_id is null
130 And to_number(org_information1) = p_company_id))
131 And est.org_information_context = 'FR_ESTAB_INFO'
132 And org.organization_id = est.organization_id
133 And org.location_id = loc.location_id (+)
134 order by ORG.NAME;
135 --
136 Cursor get_person(p_establishment_id number) is
137 Select
138 rpad(nvl(substr(per.NATIONAL_IDENTIFIER,1,SS_NUMBER_LEN),' '),SS_NUMBER_LEN,' ') SS_NUMBER
139 ,rpad(nvl(substr(per.NATIONAL_IDENTIFIER,SS_NUMBER_LEN+1,SS_KEY_LEN),' '),SS_KEY_LEN,' ') SS_KEY
140 , per.DATE_OF_BIRTH DATE_OF_BIRTH
141 , rpad(nvl(substr(nvl(Per.PER_INFORMATION1,per.last_name),1,BIRTH_NAME_LEN) ,' '),BIRTH_NAME_LEN,' ') BIRTH_NAME
142 , rpad(nvl(substr(Per.REGION_OF_BIRTH,1,DEPARTMENT_OF_BIRTH_LEN) ,' '),DEPARTMENT_OF_BIRTH_LEN,' ') DEPARTMENT_OF_BIRTH
143 , rpad(nvl(substr(Per.TOWN_OF_BIRTH,1,PLACE_OF_BIRTH_LEN) ,' '),PLACE_OF_BIRTH_LEN,' ') PLACE_OF_BIRTH
144 , rpad(nvl(substr(Per.FIRST_NAME,1,FIRST_NAME_LEN),' '),FIRST_NAME_LEN,' ') FIRST_NAME
145 , rpad(nvl(substr(Per.LAST_NAME,1,LAST_NAME_LEN),' '),LAST_NAME_LEN,' ') LAST_NAME
146 , nvl(SCL.SEGMENT2 ,' ') EMPLOYEE_CATEGORY
147 -- Modified to select contract as 'active' for CWK
148 , decode(asg.assignment_type, 'C','A-ACTIVE', nvl(CON.STATUS ,' ')) CONTRACT_STATUS
149 , rpad(nvl(substr(ADDR.ADDRESS_LINE1,1,ADDRESS_LINE1_LEN),' '),ADDRESS_LINE1_LEN,' ') ADDRESS_LINE1
150 , rpad(nvl(substr(ADDR.ADDRESS_LINE2,1,ADDRESS_LINE2_LEN),' '),ADDRESS_LINE2_LEN,' ') ADDRESS_LINE2
151 , rpad(nvl(substr(ADDR.REGION_2,1,INSEE_CODE_LEN),' '),INSEE_CODE_LEN,' ') INSEE_CODE
152 , rpad(nvl(substr(ADDR.REGION_3,1,TOWN_LEN),' '),TOWN_LEN,' ') TOWN
153 , rpad(nvl(substr(ADDR.POSTAL_CODE,1,POSTAL_CODE_LEN),' '),POSTAL_CODE_LEN,' ') POSTAL_CODE
154 , rpad(nvl(substr(ADDR.TOWN_OR_CITY,1,CITY_LEN),' '),CITY_LEN,' ') CITY
155 , nvl(Pei.PEI_INFORMATION1,' ') EXCLUDE_FLAG
156 , nvl(substr(Pei.PEI_INFORMATION2,1,1),'1') ELECTORAL_COLLEGE
157 , nvl(substr(Pei.PEI_INFORMATION3,1,1),'1') VOTING_PLACE
158 from per_all_people_f per
159 , per_all_assignments_f asg
160 , per_assignment_status_types ast
161 , hr_soft_coding_keyflex scl
162 , per_contracts_f con
163 , per_addresses addr
164 , per_people_extra_info pei
165 where asg.establishment_id = p_establishment_id
166 and asg.primary_flag = 'Y'
167 and l_effective_date
168 between asg.effective_start_date and asg.effective_end_date
169 -- modified to include CWK's assignment
170 and asg.assignment_type in ('E','C')
171 and asg.assignment_status_type_id = ast.assignment_status_type_id
172 -- modified to include CWK's assignment status
173 and ast.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'ACTIVE_CWK', 'SUSP_CWK_ASG')
174 and asg.contract_id = con.contract_id(+)
175 and l_effective_date
176 between nvl(con.effective_start_date,l_effective_date)
177 and nvl(con.effective_end_date,l_effective_date)
178 and asg.person_id = per.person_id
179 and l_effective_date
180 between per.effective_start_date and per.effective_end_date
181 and asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
182 and per.person_id = addr.person_id (+)
183 and addr.primary_flag (+) = 'Y'
184 and per.person_id = pei.person_id(+)
185 and pei.pei_information_category(+) = 'FR_WORKERS_ELECTION'
186 order by per.FULL_NAME;
187 --
188 function decode_lookup(p_lookup_type varchar2
189 ,p_lookup_code varchar2) return varchar2 is
190 begin
191 return p_lookup_code;
192 end;
193 --
194 Procedure mandatory_check(p_field varchar2,p_value varchar2) is
195 Begin
196 If replace(p_value,' ','') is null then
197 Fnd_message.set_name('PER'
198 ,'HR_7449_PLK_COLUMN_NULL');
199 Fnd_message.set_token('COLUMN_NAME'
200 ,p_field);
201 fnd_file.put_line(FND_FILE.LOG,
202 l_error_flag ||': '|| fnd_message.get);
203 l_error_count := l_error_count + 1;
204 end if;
205 end;
206 --
207 Procedure naf_code_check(p_field varchar2,p_value varchar2) is
208 Begin
209 If substr(p_value,1,1) between '1' and '9'
210 and substr(p_value,2,1) between '1' and '9'
211 and substr(p_value,3,1) between '1' and '9'
212 and substr(p_value,4,1) between 'A' and 'Z' then
213 null;
214 else
215 Fnd_message.set_name('PER'
216 ,'HR_7944_CHECK_FMT_BAD_FORMAT');
217 --
218 fnd_file.put_line(FND_FILE.LOG,
219 l_warning_flag ||': '|| fnd_message.get ||' : '||p_field);
220 l_warning_count := l_warning_count + 1;
221 end if;
222 end;
223 --
224 Procedure election_data_check(p_field varchar2
225 ,p_election_college varchar2
226 ,p_voting_place varchar2) is
227 Begin
228 If p_election_college = '2' and p_voting_place = '2' then
229 Fnd_message.set_name('PER','HR_7944_CHECK_FMT_BAD_FORMAT');
230 --
234 end if;
231 fnd_file.put_line(FND_FILE.LOG,
232 l_warning_flag ||': '|| fnd_message.get ||' : '||p_field);
233 l_warning_count := l_warning_count + 1;
235 end;
236 --
237 begin
238 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
239 --
240 l_error_flag := decode_lookup('FR_WORKERS_ELECTION','ERROR');
241 l_warning_flag := decode_lookup('FR_WORKERS_ELECTION','WARNING');
242 l_error_count := 0;
243 l_warning_count := 0;
244 --
245 --
246 -- Reporting Establishment Data
247 REPORTING_SIRET := decode_lookup('FR_WORKERS_ELECTION','REPORTING_SIRET');
248 --
249 REPORTING_SIRET_LOG := greatest(length(REPORTING_SIRET),REPORTING_SIRET_LEN);
250 --
251 -- Establishment Heading Data
252 SIRET := decode_lookup('FR_WORKERS_ELECTION','SIRET');
253 NAF_CODE := decode_lookup('FR_WORKERS_ELECTION','NAF_CODE');
254 ESTAB_NAME := decode_lookup('FR_WORKERS_ELECTION','ESTAB_NAME');
255 ADDRESS_LINE2 := decode_lookup('FR_WORKERS_ELECTION','ADDRESS_LINE2');
256 ADDRESS_LINE1 := decode_lookup('FR_WORKERS_ELECTION','ADDRESS_LINE1');
257 INSEE_CODE := decode_lookup('FR_WORKERS_ELECTION','INSEE_CODE');
258 TOWN := decode_lookup('FR_WORKERS_ELECTION','TOWN');
259 POSTAL_CODE := decode_lookup('FR_WORKERS_ELECTION','POSTAL_CODE');
260 CITY := decode_lookup('FR_WORKERS_ELECTION','CITY');
261 EMPLOYER_TYPE := decode_lookup('FR_WORKERS_ELECTION','EMPLOYER_TYPE');
262 --
263 SIRET_LOG := greatest(length(SIRET),SIRET_LEN);
264 NAF_CODE_LOG := greatest(length(NAF_CODE),NAF_CODE_LEN);
265 ESTAB_NAME_LOG := greatest(length(ESTAB_NAME),ESTAB_NAME_LEN);
266 ADDRESS_LINE2_LOG := greatest(length(ADDRESS_LINE2),ADDRESS_LINE2_LEN);
267 ADDRESS_LINE1_LOG := greatest(length(ADDRESS_LINE1),ADDRESS_LINE1_LEN);
268 INSEE_CODE_LOG := greatest(length(INSEE_CODE),INSEE_CODE_LEN);
269 TOWN_LOG := greatest(length(TOWN),TOWN_LEN);
270 POSTAL_CODE_LOG := greatest(length(POSTAL_CODE),POSTAL_CODE_LEN);
271 CITY_LOG := greatest(length(CITY),CITY_LEN);
272 EMPLOYER_TYPE_LOG := greatest(length(EMPLOYER_TYPE),EMPLOYER_TYPE_LEN);
273 --
274 -- Person Heading Data
275 SS_NUMBER := decode_lookup('FR_WORKERS_ELECTION','SS_NUMBER');
276 SS_KEY := decode_lookup('FR_WORKERS_ELECTION','SS_KEY');
277 DATE_OF_BIRTH := decode_lookup('FR_WORKERS_ELECTION','DATE_OF_BIRTH');
278 DEPARTMENT_OF_BIRTH := decode_lookup('FR_WORKERS_ELECTION','DEPARTMENT_OF_BIRTH');
279 PLACE_OF_BIRTH := decode_lookup('FR_WORKERS_ELECTION','PLACE_OF_BIRTH');
280 BIRTH_NAME := decode_lookup('FR_WORKERS_ELECTION','BIRTH_NAME');
281 FIRST_NAME := decode_lookup('FR_WORKERS_ELECTION','FIRST_NAME');
282 LAST_NAME := decode_lookup('FR_WORKERS_ELECTION','LAST_NAME');
283 ELECTORAL_COLLEGE := decode_lookup('FR_WORKERS_ELECTION','ELECTORAL_COLLEGE');
284 SECTION := decode_lookup('FR_WORKERS_ELECTION','SECTION');
285 VOTING_PLACE := decode_lookup('FR_WORKERS_ELECTION','VOTING_PLACE');
286 --
287 SS_NUMBER_LOG := greatest(length(SS_NUMBER),SS_NUMBER_LEN);
288 SS_KEY_LOG := greatest(length(SS_KEY),SS_KEY_LEN);
289 DATE_OF_BIRTH_LOG := greatest(length(DATE_OF_BIRTH),DATE_OF_BIRTH_LEN);
290 DEPARTMENT_OF_BIRTH_LOG := greatest(length(DEPARTMENT_OF_BIRTH),DEPARTMENT_OF_BIRTH_LEN);
291 PLACE_OF_BIRTH_LOG := greatest(length(PLACE_OF_BIRTH),PLACE_OF_BIRTH_LEN);
292 BIRTH_NAME_LOG := greatest(length(BIRTH_NAME),BIRTH_NAME_LEN);
293 FIRST_NAME_LOG := greatest(length(FIRST_NAME),FIRST_NAME_LEN);
294 LAST_NAME_LOG := greatest(length(LAST_NAME),LAST_NAME_LEN);
295 ELECTORAL_COLLEGE_LOG := greatest(length(ELECTORAL_COLLEGE),ELECTORAL_COLLEGE_LEN);
296 SECTION_LOG := greatest(length(SECTION),SECTION_LEN);
297 VOTING_PLACE_LOG := greatest(length(VOTING_PLACE),VOTING_PLACE_LEN);
298 --
299 l_rep_est_title := rpad(REPORTING_SIRET,REPORTING_SIRET_LOG,' ');
300 l_rep_est_underline := rpad('-',REPORTING_SIRET_LOG,'-');
301 --
302 l_est_head_title := rpad(SIRET,SIRET_LOG,' ') || ' ' ||
303 rpad(NAF_CODE,NAF_CODE_LOG,' ') || ' ' ||
304 rpad(ESTAB_NAME,ESTAB_NAME_LOG,' ') || ' ' ||
305 rpad(ADDRESS_LINE2,ADDRESS_LINE2_LOG,' ') || ' ' ||
306 rpad(ADDRESS_LINE1,ADDRESS_LINE1_LOG,' ') || ' ' ||
307 rpad(INSEE_CODE,INSEE_CODE_LOG,' ') || ' ' ||
308 rpad(TOWN,TOWN_LOG,' ') || ' ' ||
309 rpad(POSTAL_CODE,POSTAL_CODE_LOG,' ') || ' ' ||
310 rpad(CITY,CITY_LOG,' ') || ' ' ||
311 rpad(EMPLOYER_TYPE,EMPLOYER_TYPE_LOG,' ');
312
313 --
314 l_est_head_underline := rpad('-',SIRET_LOG,'-') || ' ' ||
315 rpad('-',NAF_CODE_LOG,'-') || ' ' ||
316 rpad('-',ESTAB_NAME_LOG,'-') || ' ' ||
317 rpad('-',ADDRESS_LINE2_LOG,'-') || ' ' ||
318 rpad('-',ADDRESS_LINE1_LOG,'-') || ' ' ||
319 rpad('-',INSEE_CODE_LOG,'-') || ' ' ||
320 rpad('-',TOWN_LOG,'-') || ' ' ||
321 rpad('-',POSTAL_CODE_LOG,'-') || ' ' ||
322 rpad('-',CITY_LOG,'-') || ' ' ||
323 rpad('-',EMPLOYER_TYPE_LOG,'-');
324 --
325 l_person_title := rpad(SS_NUMBER,SS_NUMBER_LOG,' ') || ' ' ||
326 rpad(SS_KEY,SS_KEY_LOG,' ') || ' ' ||
327 rpad(DATE_OF_BIRTH,DATE_OF_BIRTH_LOG,' ') || ' ' ||
328 rpad(DEPARTMENT_OF_BIRTH,DEPARTMENT_OF_BIRTH_LOG,' ') || ' ' ||
329 rpad(PLACE_OF_BIRTH,PLACE_OF_BIRTH_LOG,' ') || ' ' ||
330 rpad(BIRTH_NAME,BIRTH_NAME_LOG,' ') || ' ' ||
331 rpad(FIRST_NAME,FIRST_NAME_LOG,' ') || ' ' ||
332 rpad(LAST_NAME,LAST_NAME_LOG,' ') || ' ' ||
333 rpad(ADDRESS_LINE2,ADDRESS_LINE2_LOG,' ') || ' ' ||
334 rpad(ADDRESS_LINE1,ADDRESS_LINE1_LOG,' ') || ' ' ||
338 rpad(CITY,CITY_LOG,' ') || ' ' ||
335 rpad(INSEE_CODE,INSEE_CODE_LOG,' ') || ' ' ||
336 rpad(TOWN,TOWN_LOG,' ') || ' ' ||
337 rpad(POSTAL_CODE,POSTAL_CODE_LOG,' ') || ' ' ||
339 rpad(ELECTORAL_COLLEGE,ELECTORAL_COLLEGE_LOG,' ') || ' ' ||
340 rpad(SECTION,SECTION_LOG,' ') || ' ' ||
341 rpad(VOTING_PLACE,VOTING_PLACE_LOG,' ');
342 --
343 l_person_underline := rpad('-',SS_NUMBER_LOG,'-') || ' ' ||
344 rpad('-',SS_KEY_LOG,'-') || ' ' ||
345 rpad('-',DATE_OF_BIRTH_LOG,'-') || ' ' ||
346 rpad('-',DEPARTMENT_OF_BIRTH_LOG,'-') || ' ' ||
347 rpad('-',PLACE_OF_BIRTH_LOG,'-') || ' ' ||
348 rpad('-',BIRTH_NAME_LOG,'-') || ' ' ||
349 rpad('-',FIRST_NAME_LOG,'-') || ' ' ||
350 rpad('-',LAST_NAME_LOG,'-') || ' ' ||
351 rpad('-',ADDRESS_LINE2_LOG,'-') || ' ' ||
352 rpad('-',ADDRESS_LINE1_LOG,'-') || ' ' ||
353 rpad('-',INSEE_CODE_LOG,'-') || ' ' ||
354 rpad('-',TOWN_LOG,'-') || ' ' ||
355 rpad('-',POSTAL_CODE_LOG,'-') || ' ' ||
356 rpad('-',CITY_LOG,'-') || ' ' ||
357 rpad('-',ELECTORAL_COLLEGE_LOG,'-') || ' ' ||
358 rpad('-',SECTION_LOG,'-') || ' ' ||
359 rpad('-',VOTING_PLACE_LOG,'-');
360 --
361 --
362 For rep_est in get_reporting_establishment loop
363 l_start_rec := '0000000000000000000' ||
364 '000' ||
365 rpad(' ',1,' ') ||
366 substr(rep_est.SIRET,1,14) ||
367 rpad(' ',527,' ');
368 Fnd_file.put_line(FND_FILE.OUTPUT,l_start_rec);
369
370 Fnd_file.put_line(FND_FILE.LOG,null);
371 Fnd_file.put_line(FND_FILE.LOG,l_rep_est_title);
372 Fnd_file.put_line(FND_FILE.LOG,l_rep_est_underline);
373 Fnd_file.put_line(FND_FILE.LOG, rpad(substr(rep_est.SIRET,1,14),REPORTING_SIRET_LOG,' '));
374
375 -- Mandatory Validation
376 -- reporting establishment SIRET
377
378 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','SIRET')
379 , substr(rep_est.SIRET,1,14));
380 End loop;
381
382 --
383 --
384 For est in get_establishments loop
385 --
386 L_est_head_rec := est.SIRET ||
387 rpad(' ',2,' ') ||
388 rpad(' ',3,' ') ||
389 '020' ||
390 est.SIRET ||
391 est.NAF_CODE ||
392 rpad(' ',56,' ') ||
393 est.ESTAB_NAME ||
394 est.ADDRESS_LINE2 ||
395 rpad(' ',1,' ') ||
396 est.ADDRESS_LINE1 ||
397 est.INSEE_CODE ||
398 rpad(' ',1,' ') ||
399 est.TOWN ||
400 est.POSTAL_CODE ||
401 rpad(' ',1,' ') ||
402 est.CITY ||
403 rpad(' ',86,' ') ||
404 rpad(' ',2,' ') ||
405 rpad(' ',201,' ');
406 --
407 l_est_head_log := rpad(est.SIRET,SIRET_LOG,' ') || ' ' ||
408 rpad(est.NAF_CODE,NAF_CODE_LOG,' ') || ' ' ||
409 rpad(est.ESTAB_NAME,ESTAB_NAME_LOG,' ') || ' ' ||
410 rpad(est.ADDRESS_LINE2,ADDRESS_LINE2_LOG,' ') || ' ' ||
411 rpad(est.ADDRESS_LINE1,ADDRESS_LINE1_LOG,' ') || ' ' ||
412 rpad(est.INSEE_CODE,INSEE_CODE_LOG,' ') || ' ' ||
413 rpad(est.TOWN,TOWN_LOG,' ') || ' ' ||
414 rpad(est.POSTAL_CODE,POSTAL_CODE_LOG,' ') || ' ' ||
415 rpad(est.CITY,CITY_LOG,' ') || ' ' ||
416 rpad(' ',EMPLOYER_TYPE_LOG,' ');
417 --
418 --
419 -- process employees for this establishment (see below)
420
421 l_person_count := 0;
422
423 For per in get_person(est.establishment_id) loop
424
425
426 -- check that the person meets the criteria
427 -- 1. Active or Suspended employee or CWK assignment - handled in cursor
428 -- 2. Person is 16 before 29th March 2002
429 -- 3. Either contract status is Active
430 -- OR employee category is DIRECTOR
431 -- 4. Person is not explicitly excluded
432
433
434 if (months_between(l_effective_date-1 , nvl(per.DATE_OF_BIRTH,to_date('01/01/1900','dd/mm/yyyy')))/12 >= 16
435 and (substr(nvl(per.CONTRACT_STATUS,'XX'),1,2) = 'A-'
436 or nvl(per.EMPLOYEE_CATEGORY,'XXXXXX') = 'DIRECTOR')
437 and nvl(per.EXCLUDE_FLAG,'X') <> 'Y') then
438
439 -- Output the establishment header record as long as the estab
440 -- has at least one employee meeting the criteria
441
442 if l_person_count = 0 then -- 1st person being processed
443 Fnd_file.put_line(FND_FILE.OUTPUT,l_est_head_rec);
444
445 Fnd_file.put_line(FND_FILE.LOG,null);
446 Fnd_file.put_line(FND_FILE.LOG,l_est_head_title);
447 Fnd_file.put_line(FND_FILE.LOG,l_est_head_underline);
448 Fnd_file.put_line(FND_FILE.LOG,l_est_head_log);
449
453 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','NAF_CODE')
450 -- Perform validation for the establishment
451 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','SIRET')
452 , est.SIRET);
454 , est.NAF_CODE);
455 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','ESTAB_NAME')
456 , est.ESTAB_NAME);
457 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','ADDRESS_LINE1')
458 , est.ADDRESS_LINE1);
459 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','INSEE_CODE')
460 , est.INSEE_CODE);
461 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','TOWN')
462 , est.TOWN);
463 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','POSTAL_CODE')
464 , est.POSTAL_CODE);
465
466 naf_code_check(decode_lookup('FR_WORKERS_ELECTION','NAF_CODE')
467 , est.NAF_CODE);
468
469
470 Fnd_file.put_line(FND_FILE.LOG,null);
471 Fnd_file.put_line(FND_FILE.LOG,l_person_title);
472 Fnd_file.put_line(FND_FILE.LOG,l_person_underline);
473
474 End if;
475
476 l_person_rec :=
477 est.SIRET
478 || rpad(' ',2,' ')
479 || rpad(' ',3,' ')
480 || '200'
481 || rpad(' ',10,' ')
482 || per.SS_NUMBER
483 || per.SS_KEY
484 || nvl(to_char(per.DATE_OF_BIRTH,'DDMMYY'),' ')
485 || per.DEPARTMENT_OF_BIRTH
486 || rpad(' ',3,' ')
487 || per.PLACE_OF_BIRTH
488 || rpad(' ',3,' ')
489 || Per.BIRTH_NAME
490 || Per.FIRST_NAME
491 || Per.LAST_NAME
492 || Per.ADDRESS_LINE2
493 || rpad(' ',1,' ')
494 || Per.ADDRESS_LINE1
495 || Per.INSEE_CODE
496 || rpad(' ',1,' ')
497 || Per.TOWN
498 || Per.POSTAL_CODE
499 || rpad(' ',1,' ')
500 || Per.CITY
501 || rpad(' ',232,' ')
502 || Per.ELECTORAL_COLLEGE
503 || Est.SECTION
504 || Per.VOTING_PLACE
505 || rpad(' ',33,' ');
506
507 l_person_log :=
508 rpad(per.SS_NUMBER,SS_NUMBER_LOG,' ') || ' ' ||
509 rpad(per.SS_KEY,SS_KEY_LOG,' ') || ' ' ||
510 rpad(nvl(to_char(per.DATE_OF_BIRTH,'DDMMYY'),' '),DATE_OF_BIRTH_LOG,' ') || ' ' ||
511 rpad(per.DEPARTMENT_OF_BIRTH,DEPARTMENT_OF_BIRTH_LOG,' ') || ' ' ||
512 rpad(per.PLACE_OF_BIRTH,PLACE_OF_BIRTH_LOG,' ') || ' ' ||
513 rpad(Per.BIRTH_NAME,BIRTH_NAME_LOG,' ') || ' ' ||
514 rpad(Per.FIRST_NAME,FIRST_NAME_LOG,' ') || ' ' ||
515 rpad(Per.LAST_NAME,LAST_NAME_LOG,' ') || ' ' ||
516 rpad(per.ADDRESS_LINE2,ADDRESS_LINE2_LOG,' ') || ' ' ||
517 rpad(per.ADDRESS_LINE1,ADDRESS_LINE1_LOG,' ') || ' ' ||
518 rpad(per.INSEE_CODE,INSEE_CODE_LOG,' ') || ' ' ||
519 rpad(per.TOWN,TOWN_LOG,' ') || ' ' ||
520 rpad(per.POSTAL_CODE,POSTAL_CODE_LOG,' ') || ' ' ||
521 rpad(per.CITY,CITY_LOG,' ') || ' ' ||
522 rpad(Per.ELECTORAL_COLLEGE,ELECTORAL_COLLEGE_LOG,' ') || ' ' ||
523 rpad(Est.SECTION,SECTION_LOG,' ') || ' ' ||
524 rpad(Per.VOTING_PLACE,VOTING_PLACE_LOG,' ');
525
526 Fnd_file.put_line(FND_FILE.OUTPUT,l_person_rec);
527
528 Fnd_file.put_line(FND_FILE.LOG,l_person_log);
529
530 -- Perform mandataory checks for people
531
532 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','SS_NUMBER')
533 , per.SS_NUMBER);
534 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','SS_KEY')
535 , per.SS_KEY);
536 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','DATE_OF_BIRTH')
537 , to_char(per.DATE_OF_BIRTH,'DDMMYY'));
538 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','BIRTH_DEPT')
539 , per.DEPARTMENT_OF_BIRTH);
540 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','BIRTH_PLACE')
541 , per.PLACE_OF_BIRTH);
542 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','FIRST_NAME')
543 , Per.FIRST_NAME);
544 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','LAST_NAME')
545 , Per.LAST_NAME);
546 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','ADDRESS_LINE1')
547 , per.ADDRESS_LINE1);
548 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','INSEE_CODE')
549 , per.INSEE_CODE);
550 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','TOWN')
551 , per.TOWN);
552 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','POSTAL_CODE')
553 , per.POSTAL_CODE);
554 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','ELECTORAL_COLLEGE')
555 , Per.ELECTORAL_COLLEGE);
556 mandatory_check(decode_lookup('FR_WORKERS_ELECTION','VOTING_PLACE')
557 , per.VOTING_PLACE);
558
559 election_data_check(decode_lookup('FR_WORKERS_ELECTION','WORKERS_ELECTION_DATA')
560 ,Per.ELECTORAL_COLLEGE
561 ,per.VOTING_PLACE);
562 --
563 l_person_count := l_person_count + 1;
564
565 End if; -- check person meets criteria
566
567 End loop; -- Person Loop
568
569
570 -- Only print the footer if the establishment has > 0 people
571 If l_person_count > 0 then
572
573 l_est_footer_rec := Substr(est.SIRET,1,14)
574 || rpad(' ',2,' ')
575 || rpad(' ',3,' ')
576 || '300'
577 || rpad(' ',312,' ')
578 || lpad(to_char(l_person_count),6,'0')
579 || rpad(' ',224,' ');
580
581 Fnd_file.put_line(FND_FILE.OUTPUT, l_est_footer_rec);
582
583 End if;
584
585
586 end loop; -- Establishment Loop
587
588
589 l_end_rec := rpad('9',19,'9')
590 || '990'
591 || rpad(' ',542,' ');
592
593 Fnd_file.put_line(FND_FILE.OUTPUT, l_end_rec);
594
595 fnd_file.put_line(FND_FILE.LOG,null);
596 fnd_file.put_line(FND_FILE.LOG,decode_lookup('FR_WORKERS_ELECTION','WARNING') || ' : ' ||
597 to_char(l_warning_count));
598 fnd_file.put_line(FND_FILE.LOG,decode_lookup('FR_WORKERS_ELECTION','ERROR') || ' : ' ||
599 to_char(l_error_count));
600
601 retcode := 0;
602 end;
603 end;