[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_DADS_ESTAB_COMP
Source
1 PACKAGE BODY pay_fr_dads_estab_comp AS
2 /* $Header: pyfrdesc.pkb 120.1 2006/01/30 05:27:02 aparkes noship $ */
3
4 ------------------------------------------------------------------------
5 -- Archive data procedure---
6 ------------------------------------------------------------------------
7 -- This procedure is to be called for archiving all structures
8 PROCEDURE archive_data(p_payroll_action_id NUMBER,
9 p_rubric_name VARCHAR2,
10 p_message_type VARCHAR2,
11 p_org_id NUMBER,
12 p_lookup_type VARCHAR2,
13 p_file_value VARCHAR2,
14 p_message_text VARCHAR2,
15 p_extra_information VARCHAR2 DEFAULT NULL,
16 p_usage VARCHAR2)
17 IS
18 --
19 l_action_info_id NUMBER;
20 l_ovn NUMBER;
21 l_user_value fnd_lookup_values.meaning%type;
22 --
23 BEGIN
24 --
25 IF p_lookup_type is not null THEN
26 l_user_value := hr_general.decode_lookup(p_lookup_type,p_file_value);
27 END IF;
28 --
29 pay_action_information_api.create_action_information (
30 p_action_information_id => l_action_info_id
31 , p_action_context_id => p_payroll_action_id
32 , p_action_context_type => 'PA'
33 , p_object_version_number => l_ovn
34 , p_action_information_category => 'FR_DADS_FILE_DATA'
35 , p_action_information1 => p_rubric_name
36 , p_action_information2 => p_message_type
37 , p_action_information3 => p_org_id
38 , p_action_information4 => p_file_value
39 , p_action_information5 => l_user_value
40 , p_action_information6 => p_message_text
41 , p_action_information7 => p_extra_information
42 , p_action_information9 => p_usage);
43 --
44 EXCEPTION
45 WHEN OTHERS THEN raise;
46 END archive_data;
47 -------------------------------------------------------------------------------------
48 -- Archive report Data Procedure
49 -------------------------------------------------------------------------------------
50 -- This procedure is to be called from S10 and S20 procedures
51 -- for archiving CRE establishment name in FR_DADS_REPORT_DATA structure
52 PROCEDURE archive_report_data(p_payroll_action_id NUMBER,
53 p_section_name VARCHAR2,
54 p_sort_order NUMBER,
55 p_org_id NUMBER,
56 p_cre_org_name VARCHAR2,
57 p_report_code VARCHAR2)
58 IS
59 --
60 l_action_info_id NUMBER;
61 l_ovn NUMBER;
62 --
63 BEGIN
64 --
65 pay_action_information_api.create_action_information (
66 p_action_information_id => l_action_info_id
67 , p_action_context_id => p_payroll_action_id
68 , p_action_context_type => 'PA'
69 , p_object_version_number => l_ovn
70 , p_action_information_category => 'FR_DADS_REPORT_DATA'
71 , p_action_information1 => p_section_name
72 , p_action_information2 => p_sort_order
73 , p_action_information3 => p_org_id
74 , p_action_information4 => p_report_code
75 , p_action_information5 => p_cre_org_name);
76 --
77 EXCEPTION
78 WHEN OTHERS THEN raise;
79 END archive_report_data;
80 --
81 -----------------------------------------------------------------------------------
82 -- ISSUING ESTABLISHMENT INFORMATION PROCEDURE
83 -----------------------------------------------------------------------------------
84 PROCEDURE S10_00_issue_estab(p_param_reference IN VARCHAR2,
85 p_param_issuing_estab_id IN NUMBER,
86 p_param_business_group_id IN NUMBER,
87 p_payroll_action_id IN NUMBER,
88 p_cre_info_issue OUT NOCOPY g_cre_info_issue%TYPE)
89 IS
90 --
91 l_siren hr_organization_information.org_information1%TYPE ;
92 l_nic varchar2(7) ;
93 l_name hr_all_organization_units_tl.name%TYPE ;
94 l_name_comp hr_all_organization_units_tl.name%TYPE ;
95 l_location hr_all_organization_units.location_id%TYPE ;
96 l_complement hr_locations_all.address_line_2%TYPE ;
97 l_street_and_number hr_locations_all.address_line_1%TYPE ;
98 l_insee hr_locations_all.region_2%TYPE ;
99 l_small_town hr_locations_all.region_3%TYPE ;
100 l_postal_code hr_locations_all.postal_code%TYPE ;
101 l_town hr_locations_all.town_or_city%TYPE ;
102 l_country_code varchar2(30) ;
103 l_country_name fnd_territories.nls_territory%TYPE;
104 l_software varchar2(20) ;
105 l_provider varchar2(10) ;
106 l_version varchar2(20) ;
107 l_service_code varchar2(4) ;
108 l_live_send_code varchar2(4) ;
109 l_test_send_code varchar2(4) ;
110 l_norm varchar2(30) ;
111 l_table_code varchar2(30) ;
112 l_cre_estab_id hr_organization_information.organization_id%TYPE ;
113 l_cre_siren hr_organization_information.org_information1%TYPE ;
114 l_cre_nic varchar2(7) ;
115 l_cre_media hr_organization_information.org_information1%TYPE ;
116 l_address_to_use hr_organization_information.org_information2%TYPE ;
117 l_value fnd_new_messages.message_text%type;
118 l_error_type hr_lookups.meaning%type;
119 l_error hr_lookups.meaning%type;
120 l_warning hr_lookups.meaning%type;
121 l_cre_estab_name hr_all_organization_units_tl.name%TYPE;
122 l_location_name hr_locations_all_tl.location_code%TYPE;
123 --
124 Cursor csr_get_S10_G00 is
125 Select hoi_issue_comp.org_information1 SIREN_issue_estab,
126 substr(hoi_issue_estab.org_information2, length(hoi_issue_estab.org_information2)-4, 5) NIC_issue_estab,
127 hou_issue_estab_tl.name name_issue_estab,
128 hou_issue_estab.location_id location_id,
129 hloc_issue_estab_tl.location_code location_name,
130 --
131 hloc_issue_estab.address_line_2 addr_compl_issue_estab,
132 hloc_issue_estab.address_line_1 addr_nstreet_issue_estab,
133 hloc_issue_estab.region_2 addr_insee_issue_estab,
134 hloc_issue_estab.region_3 addr_town_issue_estab,
135 hloc_issue_estab.postal_code addr_zip_issue_estab,
136 hloc_issue_estab.town_or_city addr_towncity_issue_estab,
137 -- (Afnor codes) addr_cntrycode_issue_estab ,
138 ft_issue_estab.nls_territory addr_cntryname_issue_estab,
139 --
140 '02' Send_code_live,
141 '01' Send_code_test,
142 --
143 hoi_cre_comp_tl.name name_cre_comp,
144 hoi_cre_comp.org_information1 SIREN_cre_estab,
145 substr(hoi_cre_estab.org_information2, length(hoi_cre_estab.org_information2)-4, 5) NIC_cre_estab,
146 hoi_cre_dads.org_information1 type_media_cre_estab,
147 hoi_cre_dads.org_information2 address_cre_estab,
148 hoi_cre_estab.organization_id cre_estab_org_id,
149 hou_cre_estab_tl.name name_cre_estab
150 --
151 From hr_all_organization_units hou_issue_estab,
152 hr_all_organization_units_tl hou_issue_estab_tl,
153 hr_all_organization_units_tl hou_cre_estab_tl,
154 hr_all_organization_units_tl hoi_cre_comp_tl,
155 hr_organization_information hoi_issue_estab_cre,
156 hr_organization_information hoi_issue_estab,
157 hr_organization_information hoi_issue_comp,
158 hr_locations_all hloc_issue_estab,
159 hr_locations_all_tl hloc_issue_estab_tl,
160 --
161 hr_organization_information hoi_cre_estab,
162 hr_organization_information hoi_cre_comp,
163 hr_organization_information hoi_cre_dads,
164 --
165 fnd_territories ft_issue_estab
166 --
167 Where hou_issue_estab.organization_id = p_param_issuing_estab_id
168 and hou_issue_estab.business_group_id = p_param_business_group_id
169 --
170 and hou_issue_estab_tl.organization_id = hou_issue_estab.organization_id
171 and hou_issue_estab_tl.language = userenv('LANG')
172 --
173 and hoi_issue_estab.organization_id(+) = hou_issue_estab.organization_id
174 and hoi_issue_estab.org_information_context(+) = 'FR_ESTAB_INFO'
175 and hoi_issue_comp.organization_id(+) = hoi_issue_estab.org_information1
176 and hoi_issue_comp.org_information_context(+) = 'FR_COMP_INFO'
177 --
178 and hloc_issue_estab.location_id(+)= hou_issue_estab.location_id
179 and hloc_issue_estab.style(+) ='FR'
180 --
181 and ft_issue_estab.territory_code(+) = hloc_issue_estab.country
182 --
183 and hloc_issue_estab_tl.location_id(+) = hou_issue_estab.location_id
184 and hloc_issue_estab_tl.language(+) = userenv('LANG')
185 --
186 and hoi_issue_estab_cre.organization_id(+)= hou_issue_estab.organization_id
187 and hoi_issue_estab_cre.org_information_context(+) = 'FR_DADS_ISSUE_CRE_INFO'
188 and hoi_cre_estab.organization_id(+) = hoi_issue_estab_cre.org_information1
189 and hoi_cre_estab.org_information_context(+) = 'FR_ESTAB_INFO'
190 and hoi_cre_comp.organization_id(+) = hoi_cre_estab.org_information1
191 and hoi_cre_comp.org_information_context(+) = 'FR_COMP_INFO'
192 and hoi_cre_comp_tl.organization_id(+) = hoi_cre_comp.organization_id
193 and hoi_cre_comp_tl.language(+) = userenv('LANG')
194 --
195 and hoi_cre_dads.organization_id(+) = hoi_issue_estab_cre.org_information1
196 and hoi_cre_dads.org_information_context(+) = 'FR_DADS_CRE_INFO'
197 and hou_cre_estab_tl.organization_id(+) = hoi_issue_estab_cre.org_information1
198 and hou_cre_estab_tl.language(+) = userenv('LANG');
199 --
200 BEGIN
201 /* Initialising the local variables */
202 l_software := 'Oracle Payroll';
203 l_provider := 'Oracle';
204 l_version := '11.5.9';
205 l_service_code := '40';
206 l_norm := 'V7R00';
207 l_table_code := 'ISO/IEC 8859';
208 /* End of Initialising the local variables */
209
210 -- Call and fetch cursor
211 OPEN csr_get_S10_G00;
212 FETCH csr_get_S10_G00 INTO l_siren ,
213 l_nic ,
214 l_name ,
215 l_location,
216 l_location_name,
217 l_complement ,
218 l_street_and_number,
219 l_insee ,
220 l_small_town ,
221 l_postal_code,
222 l_town ,
223 l_country_name ,
224 l_live_send_code ,
225 l_test_send_code,
226 l_name_comp,
227 l_cre_siren ,
228 l_cre_nic ,
229 l_cre_media,
230 l_address_to_use,
231 l_cre_estab_id,
232 l_cre_estab_name;
233 CLOSE csr_get_S10_G00;
234 -- Populate table for cre information
235 g_cre_info_issue.cre_estab_id := l_cre_estab_id;
236 g_cre_info_issue.cre_name := l_name_comp;
237 g_cre_info_issue.cre_siren := l_cre_siren;
238 g_cre_info_issue.cre_nic := l_cre_nic ;
239 g_cre_info_issue.cre_media := l_cre_media;
240 g_cre_info_issue.address_to_use := l_address_to_use;
241 --
242 -- Archive CRE estab name
243 IF l_cre_estab_name IS NOT NULL THEN
244 archive_report_data(
245 p_payroll_action_id => p_payroll_action_id,
246 p_section_name => 'S10',
247 p_sort_order => 1,
248 p_org_id => p_param_issuing_estab_id,
249 p_cre_org_name => l_cre_estab_name,
250 p_report_code => 'S10_CRE_ESTAB_NAME');
251 END IF;
252 -- Getting the error messages
253 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
254 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
255 -- SIREN Number
256 IF l_siren is null THEN
257 l_error_type := l_error;
258 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
259 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
260 ('FR_DADS_RUBRICS','S10.G01.00.001.001'), null);
261 ELSE
262 l_error_type := null;
263 l_value := null;
264 END IF;
265 -- Archive SIREN number
266 archive_data(
267 p_payroll_action_id => p_payroll_action_id
268 ,p_rubric_name => 'S10.G01.00.001.001'
269 ,p_message_type => l_error_type
270 ,p_org_id => p_param_issuing_estab_id
271 ,p_lookup_type => null
272 ,p_file_value => l_siren
273 ,p_message_text => l_value
274 ,p_usage => 'M');
275 --
276 --NIC Origin Establishment
277 IF l_nic is null THEN
278 l_error_type := l_error;
279 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
280 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
281 ('FR_DADS_RUBRICS','S10.G01.00.001.002'), null);
282 ELSE
283 l_error_type := null;
284 l_value := null;
285 END IF;
286 -- Archive NIC Origin Establishment
287 archive_data(
288 p_payroll_action_id => p_payroll_action_id
289 ,p_rubric_name => 'S10.G01.00.001.002'
290 ,p_message_type => l_error_type
291 ,p_org_id => p_param_issuing_estab_id
292 ,p_lookup_type => null
293 ,p_file_value => l_nic
294 ,p_message_text => l_value
295 ,p_usage => 'M');
296 --
297 -- Name of the Establishment
298 IF l_name is null THEN
299 l_error_type := l_error;
300 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
301 'VALUE1: '||hr_general.decode_lookup
302 ('FR_DADS_RUBRICS','S10.G01.00.002'), null, null);
303 ELSE
304 l_error_type := null;
305 l_value := null;
306 END IF;
307 -- Archive Name of the Establishment
308 archive_data(
309 p_payroll_action_id => p_payroll_action_id
310 ,p_rubric_name => 'S10.G01.00.002'
311 ,p_message_type => l_error_type
312 ,p_org_id => p_param_issuing_estab_id
313 ,p_lookup_type => null
314 ,p_file_value => l_name
315 ,p_message_text => l_value
316 ,p_usage => 'M');
317 -- First check the location
318 IF l_location is null THEN
319 l_error_type := l_error;
320 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
321 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
322 ('FR_DADS_RUBRICS','S10.G01.00.003.006.M'), null);
323 -- Archive Nature and Name of the Street
324 archive_data(
325 p_payroll_action_id => p_payroll_action_id
326 ,p_rubric_name => 'S10.G01.00.003.006'
327 ,p_message_type => l_error_type
328 ,p_org_id => p_param_issuing_estab_id
329 ,p_lookup_type => null
330 ,p_file_value => l_street_and_number
331 ,p_message_text => l_value
332 ,p_usage => 'C');
333 --
334 -- Archive the remaining mandatory rubrics, i.e. zip code and town_or_city
335 -- Archive zip code with null error and value
336 archive_data(
337 p_payroll_action_id => p_payroll_action_id
338 ,p_rubric_name => 'S10.G01.00.003.010'
339 ,p_message_type => null
340 ,p_org_id => p_param_issuing_estab_id
341 ,p_lookup_type => null
342 ,p_file_value => null
343 ,p_message_text => null
344 ,p_usage => 'M');
345 -- Archive town_or_city with null error and value
346 archive_data(
347 p_payroll_action_id => p_payroll_action_id
348 ,p_rubric_name => 'S10.G01.00.003.012'
349 ,p_message_type => null
350 ,p_org_id => p_param_issuing_estab_id
351 ,p_lookup_type => null
352 ,p_file_value => null
353 ,p_message_text => null
354 ,p_usage => 'M');
355
356 ELSE -- if location is not null
357 -- Archive address complement
358 l_error_type:=null;
359 l_value:=null;
360 archive_data(
361 p_payroll_action_id => p_payroll_action_id
362 ,p_rubric_name => 'S10.G01.00.003.001'
363 ,p_message_type => l_error_type
364 ,p_org_id => p_param_issuing_estab_id
365 ,p_lookup_type => null
366 ,p_file_value => l_complement
367 ,p_message_text => l_value
368 ,p_usage => 'O');
369 -- Nature and Name of the Street
370 IF l_street_and_number is null THEN
371 l_error_type := l_error;
372 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
373 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
374 ('FR_DADS_RUBRICS','S10.G01.00.003.006'), null);
375 ELSE
376 l_error_type := null;
377 l_value := null;
378 END IF;
379 -- Archive Nature and Name of the Street
380 archive_data(
381 p_payroll_action_id => p_payroll_action_id
382 ,p_rubric_name => 'S10.G01.00.003.006'
383 ,p_message_type => l_error_type
384 ,p_org_id => p_param_issuing_estab_id
385 ,p_lookup_type => null
386 ,p_file_value => l_street_and_number
387 ,p_message_text => l_value
388 ,p_usage => 'C');
389 --
390 -- Archive INSEE code
391 l_error_type:=null;
392 l_value:=null;
393 archive_data(
394 p_payroll_action_id => p_payroll_action_id
395 ,p_rubric_name => 'S10.G01.00.003.007'
396 ,p_message_type => l_error_type
397 ,p_org_id => p_param_issuing_estab_id
398 ,p_lookup_type => null
399 ,p_file_value => l_insee
400 ,p_message_text => l_value
401 ,p_usage => 'O');
402 --
403 -- Name of the Town
404 IF upper(l_small_town) = upper(l_town) THEN
405 l_error_type := l_error;
406 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
407 null, null, null);
408 -- making the value of 'TOWN' as null
409 -- for bug 3550604
410 l_small_town := null;
411 ELSE
412 l_error_type := null;
413 l_value := null;
414 END IF;
415 -- Archive name of town
416 archive_data(
417 p_payroll_action_id => p_payroll_action_id
418 ,p_rubric_name => 'S10.G01.00.003.009'
419 ,p_message_type => l_error_type
420 ,p_org_id => p_param_issuing_estab_id
421 ,p_lookup_type => null
422 ,p_file_value => l_small_town
423 ,p_message_text => l_value
424 ,p_usage => 'C');
425 --
426 -- Zip Code
427 IF l_postal_code is null THEN
428 l_error_type := l_error;
429 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
430 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
431 ('FR_DADS_RUBRICS','S10.G01.00.003.010'), null);
432 ELSE
433 l_error_type := null;
434 l_value := null;
435 END IF;
436 -- Archive zip code
437 archive_data(
438 p_payroll_action_id => p_payroll_action_id
439 ,p_rubric_name => 'S10.G01.00.003.010'
440 ,p_message_type => l_error_type
441 ,p_org_id => p_param_issuing_estab_id
442 ,p_lookup_type => null
443 ,p_file_value => l_postal_code
444 ,p_message_text => l_value
445 ,p_usage => 'M');
446 --
447 -- City
448 IF l_town <> upper(l_town) THEN
449 l_error_type := l_error;
450 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
451 null, null, null);
452 ELSIF l_town is null THEN
453 l_error_type := l_error;
454 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
455 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
456 ('FR_DADS_RUBRICS','S10.G01.00.003.012'), null);
457 ELSE
458 l_error_type := null;
459 l_value := null;
460 END IF;
461 -- Archive City
462 archive_data(
463 p_payroll_action_id => p_payroll_action_id
464 ,p_rubric_name => 'S10.G01.00.003.012'
465 ,p_message_type => l_error_type
466 ,p_org_id => p_param_issuing_estab_id
467 ,p_lookup_type => null
468 ,p_file_value => l_town
469 ,p_message_text => l_value
470 ,p_usage => 'M');
471 --
472 /* Afnor Codes
473 -- Country Code
474 IF l_country_code = 'FR' THEN
475 l_error_type := l_error;
476 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
477 'VALUE1: '||hr_general.decode_lookup
478 ('FR_DADS_RUBRICS','S10.G01.00.003.013'), null, null);
479 ELSE
480 l_error_type := null;
481 l_value := null;
482 END IF;
483 -- Archive country code
484 archive_data(
485 p_payroll_action_id => p_payroll_action_id
486 ,p_rubric_name => 'S10.G01.00.003.013'
487 ,p_message_type => l_error_type
488 ,p_org_id => p_param_issuing_estab_id
489 ,p_lookup_type => null
490 ,p_file_value => l_country_code
491 ,p_message_text => l_value
492 ,p_usage =>);
493 --
494 */
495 -- Country Name
496 IF l_country_name is not null and l_country_code = 'FR' THEN
497 l_error_type := l_error;
498 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
499 'VALUE1: '||hr_general.decode_lookup
500 ('FR_DADS_RUBRICS','S10.G01.00.003.014'), null, null);
501 ELSIF l_country_name is null AND l_country_code is not null AND l_country_code <> 'FR' THEN
502 l_error_type := l_error;
503 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
504 'VALUE1: '||hr_general.decode_lookup
505 ('FR_DADS_RUBRICS','S10.G01.00.003.014'), null, null);
506 ELSE
507 l_error_type := null;
508 l_value := null;
509 END IF;
510 -- Archive country name
511 archive_data(
512 p_payroll_action_id => p_payroll_action_id
513 ,p_rubric_name => 'S10.G01.00.003.014'
514 ,p_message_type => l_error_type
515 ,p_org_id => p_param_issuing_estab_id
516 ,p_lookup_type => null
517 ,p_file_value => l_country_name
518 ,p_message_text => l_value
519 ,p_usage => 'C');
520 --
521 END IF; -- Check for location
522 -- Internal Reference
523 IF p_param_reference is null THEN
524 l_error_type := l_error;
525 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
526 'VALUE1: '||hr_general.decode_lookup
527 ('FR_DADS_RUBRICS','S10.G01.00.004'), null, null);
528 ELSE
529 l_error_type := null;
530 l_value := null;
531 END IF;
532 -- Archive internal reference
533 archive_data(
534 p_payroll_action_id => p_payroll_action_id
535 ,p_rubric_name => 'S10.G01.00.004'
536 ,p_message_type => l_error_type
537 ,p_org_id => p_param_issuing_estab_id
538 ,p_lookup_type => null
539 ,p_file_value => p_param_reference
540 ,p_message_text => l_value
541 ,p_usage => 'M');
542 --
543 -- Archive software name
544 l_error_type:=null;
545 l_value:=null;
546 archive_data(
547 p_payroll_action_id => p_payroll_action_id
548 ,p_rubric_name => 'S10.G01.00.005'
549 ,p_message_type => l_error_type
550 ,p_org_id => p_param_issuing_estab_id
551 ,p_lookup_type => null
552 ,p_file_value => l_software
553 ,p_message_text => l_value
554 ,p_usage => 'O');
555 --
556 -- Archive provider name
557 l_error_type:=null;
558 l_value:=null;
559 archive_data(
560 p_payroll_action_id => p_payroll_action_id
561 ,p_rubric_name => 'S10.G01.00.006'
562 ,p_message_type => l_error_type
563 ,p_org_id => p_param_issuing_estab_id
564 ,p_lookup_type => null
565 ,p_file_value => l_provider
566 ,p_message_text => l_value
567 ,p_usage => 'O');
568 --
569 -- Archive version number
570 l_error_type:=null;
571 l_value:=null;
572 archive_data(
573 p_payroll_action_id => p_payroll_action_id
574 ,p_rubric_name => 'S10.G01.00.007'
575 ,p_message_type => l_error_type
576 ,p_org_id => p_param_issuing_estab_id
577 ,p_lookup_type => null
578 ,p_file_value => l_version
579 ,p_message_text => l_value
580 ,p_usage => 'O');
581 --
582 -- Service Code
583 IF l_service_code is null THEN
584 l_error_type := l_error;
585 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
586 'VALUE1: '||hr_general.decode_lookup
587 ('FR_DADS_RUBRICS','S10.G01.00.009'), null, null);
588 ELSE
589 l_error_type := null;
590 l_value := null;
591 END IF;
592 -- Archive service code
593 archive_data(
594 p_payroll_action_id => p_payroll_action_id
595 ,p_rubric_name => 'S10.G01.00.009'
596 ,p_message_type => l_error_type
597 ,p_org_id => p_param_issuing_estab_id
598 ,p_lookup_type => 'FR_DADS_SERVICE_CODE'
599 ,p_file_value => l_service_code
600 ,p_message_text => l_value
601 ,p_usage => 'M');
602 --
603 -- Norm
604 IF l_norm is null THEN
605 l_error_type := l_error;
606 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
607 'VALUE1: '||hr_general.decode_lookup
608 ('FR_DADS_RUBRICS','S10.G01.00.011'), null, null);
609 ELSE
610 l_error_type := null;
611 l_value := null;
612 END IF;
613 -- Archive norm
614 archive_data(
615 p_payroll_action_id => p_payroll_action_id
616 ,p_rubric_name => 'S10.G01.00.011'
617 ,p_message_type => l_error_type
618 ,p_org_id => p_param_issuing_estab_id
619 ,p_lookup_type => null
620 ,p_file_value => l_norm
621 ,p_message_text => l_value
622 ,p_usage => 'M');
623 --
624 -- Character Table Code
625 IF l_table_code is null THEN
626 l_error_type := l_error;
627 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
628 'VALUE1: '||hr_general.decode_lookup
629 ('FR_DADS_RUBRICS','S10.G01.00.012'), null, null);
630 ELSE
631 l_error_type := null;
632 l_value := null;
633 END IF;
634 -- Archive character table code
635 archive_data(
636 p_payroll_action_id => p_payroll_action_id
637 ,p_rubric_name => 'S10.G01.00.012'
638 ,p_message_type => l_error_type
639 ,p_org_id => p_param_issuing_estab_id
640 ,p_lookup_type => null
641 ,p_file_value => l_table_code
642 ,p_message_text => l_value
643 ,p_usage => 'M');
644 --
645 -- Live Send Code
646 IF l_live_send_code is null THEN
647 l_error_type := l_error;
648 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
649 'VALUE1: '||hr_general.decode_lookup
650 ('FR_DADS_RUBRICS','S10.G01.00.010'), null, null);
651 ELSE
652 l_error_type := null;
653 l_value := null;
654 END IF;
655 -- Archive live send code
656 archive_data(
657 p_payroll_action_id => p_payroll_action_id
658 ,p_rubric_name => 'S10.G01.00.010'
659 ,p_message_type => l_error_type
660 ,p_org_id => p_param_issuing_estab_id
661 ,p_lookup_type => 'FR_DADS_SEND_CODE'
662 ,p_file_value => l_live_send_code
663 ,p_message_text => l_value
664 ,p_usage => 'M');
665 --
666 -- Test Send Code
667 IF l_test_send_code is null THEN
668 l_error_type := l_error;
669 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
670 'VALUE1: '||hr_general.decode_lookup
671 ('FR_DADS_RUBRICS','S10.G01.00.010'), null, null);
672 ELSE
673 l_error_type := null;
674 l_value := null;
675 END IF;
676 -- Archive test send code
677 archive_data(
678 p_payroll_action_id => p_payroll_action_id
679 ,p_rubric_name => 'S10.G01.00.010'
680 ,p_message_type => l_error_type
681 ,p_org_id => p_param_issuing_estab_id
682 ,p_lookup_type => 'FR_DADS_SEND_CODE'
683 ,p_file_value => l_test_send_code
684 ,p_message_text => l_value
685 ,p_usage => 'M');
686 --
687 -- SIREN, NIC, Type of Media, Address for CRE
688 IF l_cre_estab_name is not null THEN
689 IF l_cre_siren is null THEN
690 l_error_type := l_error;
691 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
692 'VALUE1: '||l_name_comp, 'VALUE2: '||hr_general.decode_lookup
693 ('FR_DADS_RUBRICS','S10.G01.00.013.001'), null);
694 ELSE
695 l_error_type:= null;
696 l_value:=null;
697 end if;
698 -- Archive CRE information
699 archive_data(
700 p_payroll_action_id => p_payroll_action_id
701 ,p_rubric_name => 'S10.G01.00.013.001'
702 ,p_message_type => l_error_type
703 ,p_org_id => p_param_issuing_estab_id
704 ,p_lookup_type => null
705 ,p_file_value => l_cre_siren
706 ,p_message_text => l_value
707 ,p_usage => 'C');
708 IF l_cre_nic is null THEN
709 l_error_type := l_error;
710 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
711 'VALUE1: '||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
712 ('FR_DADS_RUBRICS','S10.G01.00.013.002'), null);
713 ELSE
714 l_error_type:= null;
715 l_value:=null;
716 END IF;
717 -- For l_cre_nic
718 archive_data(
719 p_payroll_action_id => p_payroll_action_id
720 ,p_rubric_name => 'S10.G01.00.013.002'
721 ,p_message_type => l_error_type
722 ,p_org_id => p_param_issuing_estab_id
723 ,p_lookup_type => null
724 ,p_file_value => l_cre_nic
725 ,p_message_text => l_value
726 ,p_usage => 'C');
727 IF l_cre_media is null THEN
728 l_error_type := l_error;
729 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
730 'VALUE1: '||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
731 ('FR_DADS_RUBRICS','S10.G01.00.014'), null);
732 ELSE
733 l_error_type:= null;
734 l_value:=null;
735 END IF;
736 -- For l_cre_media
737 archive_data(
738 p_payroll_action_id => p_payroll_action_id
739 ,p_rubric_name => 'S10.G01.00.014'
740 ,p_message_type => l_error_type
741 ,p_org_id => p_param_issuing_estab_id
742 ,p_lookup_type => 'FR_DADS_CRE_TYPE_MEDIA'
743 ,p_file_value => l_cre_media
744 ,p_message_text => l_value
745 ,p_usage => 'C');
746 IF l_address_to_use is null AND l_cre_media = '03' THEN
747 l_error_type := l_error;
748 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
749 'VALUE1: '||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
750 ('FR_DADS_RUBRICS','S10.G01.00.015'), null);
751 ELSIF l_cre_media = '05' AND l_address_to_use is not null THEN
752 l_error_type := l_error;
753 l_value := pay_fr_general.get_payroll_message('PAY_75184_CRE_DATA',
754 null, null, null);
755 ELSE
756 l_error_type:= null;
757 l_value :=null;
758 END IF;
759 -- For l_address_to_use
760 archive_data(
761 p_payroll_action_id => p_payroll_action_id
762 ,p_rubric_name => 'S10.G01.00.015'
763 ,p_message_type => l_error_type
764 ,p_org_id => p_param_issuing_estab_id
765 ,p_lookup_type => null
766 ,p_file_value => l_address_to_use
767 ,p_message_text => l_value
768 ,p_usage => 'C');
769 --
770 END IF;
771 --
772 EXCEPTION
773 WHEN OTHERS THEN raise;
774 --
775 END S10_00_issue_estab;
776 --
777 -----------------------------------------------------------------------------------
778 -- ISSUING ESTABLISHMENT PERSON INFORMATION PROCEDURE
779 -----------------------------------------------------------------------------------
780 --
781 PROCEDURE S10_01_issue_person(p_issuing_estab_id IN NUMBER,
782 p_payroll_action_id IN NUMBER)
783 IS
784 --
785 l_person hr_organization_information.org_information1%TYPE ;
786 l_domain_code hr_organization_information.org_information2%TYPE ;
787 l_media_type hr_organization_information.org_information3%TYPE ;
788 l_address_to_use hr_organization_information.org_information4%TYPE ;
789 l_count number;
790 l_name hr_all_organization_units_tl.name%TYPE ;
791 l_value fnd_new_messages.message_text%type;
792 l_error_type hr_lookups.meaning%type;
793 l_error hr_lookups.meaning%type;
794 l_warning hr_lookups.meaning%type;
795 --
796 cursor csr_get_S10_G01 is
797 select hoi_issue_estab_cre.org_information1 person_g01,
798 hou_issue_estab_tl.name name_issue_estab,
799 hoi_issue_estab_cre.org_information2 domain_code_g01,
800 hoi_issue_estab_cre.org_information3 type_media_g01,
801 hoi_issue_estab_cre.org_information4 address_g01
802 from hr_organization_information hoi_issue_estab_cre,
803 hr_all_organization_units_tl hou_issue_estab_tl
804 where hoi_issue_estab_cre.organization_id = p_issuing_estab_id
805 and hoi_issue_estab_cre.org_information_context = 'FR_DADS_CONTACT_INFO'
806 and hou_issue_estab_tl.organization_id = p_issuing_estab_id
807 and hou_issue_estab_tl.language = userenv('LANG');
808 --
809 BEGIN
810 /* Initialising the local variables */
811 l_count :=0;
812 /* End of Initialising the local variables */
813
814 --
815 -- Getting the error messages
816 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
817 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
818 --
819 -- Loop through 3 times as it is a multi-record EIT
820 FOR issue_person_rec in csr_get_S10_G01 LOOP
821 l_count := l_count+1;
822 l_person := issue_person_rec.person_g01;
823 l_name := issue_person_rec.name_issue_estab;
824 l_domain_code := issue_person_rec.domain_code_g01;
825 l_media_type := issue_person_rec.type_media_g01;
826 l_address_to_use := issue_person_rec.address_g01;
827 -- Name of person answering queries
828 IF l_person is null THEN
829 l_error_type := l_error;
830 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
831 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
832 ('FR_DADS_RUBRICS','S10.G01.01.001.M'), null);
833 ELSIF (substr(l_person, 1,2) = 'M.' OR substr(l_person, 1,2) = 'M '
834 OR substr(l_person, 1,2) = 'MR' OR substr(l_person, 1,2) = 'Mr'
835 OR substr(l_person, 1,3) = 'MME' OR substr(l_person, 1,3) = 'Mme'
836 OR substr(l_person, 1,3) = 'MLE' OR substr(l_person, 1,3) = 'Mle') THEN
837 l_error_type := l_warning;
838 l_value := pay_fr_general.get_payroll_message('PAY_75176_NO_TITLE',
839 null, null, null);
840 ELSE
841 l_error_type := null;
842 l_value := null;
843 END IF;
844 -- Archive name of person
845 archive_data(
846 p_payroll_action_id => p_payroll_action_id
847 ,p_rubric_name => 'S10.G01.01.001'
848 ,p_message_type => l_error_type
849 ,p_org_id => p_issuing_estab_id
850 ,p_lookup_type => null
851 ,p_file_value => l_person
852 ,p_message_text => l_value
853 ,p_usage => 'M');
854 --
855 -- Domain Code
856 IF l_domain_code is null THEN
857 l_error_type := l_error;
858 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
859 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
860 ('FR_DADS_RUBRICS','S10.G01.01.002.M'), null);
861 ELSE
862 l_error_type := null;
863 l_value := null;
864 END IF;
865 -- Archive domain code
866 archive_data(
867 p_payroll_action_id => p_payroll_action_id
868 ,p_rubric_name => 'S10.G01.01.002'
869 ,p_message_type => l_error_type
870 ,p_org_id => p_issuing_estab_id
871 ,p_lookup_type => 'FR_DADS_DOMAIN_CODE'
872 ,p_file_value => l_domain_code
873 ,p_message_text => l_value
874 ,p_usage => 'M');
875 --
876 -- Type of Media to use for Enquiries
877 IF l_media_type is null THEN
878 l_error_type := l_error;
879 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
880 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
881 ('FR_DADS_RUBRICS','S10.G01.01.003.M'), null);
882 ELSE
883 l_error_type := null;
884 l_value := null;
885 END IF;
886 -- Archive Type of Media to use for Enquiries
887 archive_data(
888 p_payroll_action_id => p_payroll_action_id
889 ,p_rubric_name => 'S10.G01.01.003'
890 ,p_message_type => l_error_type
891 ,p_org_id => p_issuing_estab_id
892 ,p_lookup_type => 'FR_DADS_CONTACT_TYPE_MEDIA'
893 ,p_file_value => l_media_type
894 ,p_message_text => l_value
895 ,p_usage => 'M');
896 --
897 -- Address to use
898 IF l_address_to_use is null THEN
899 l_error_type := l_error;
900 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
901 'VALUE1:'||l_name, 'VALUE2: '||hr_general.decode_lookup
902 ('FR_DADS_RUBRICS','S10.G01.01.004.M'), null);
903 ELSE
904 l_error_type := null;
905 l_value := null;
906 END IF;
907 -- Archive Address to use
908 archive_data(
909 p_payroll_action_id => p_payroll_action_id
910 ,p_rubric_name => 'S10.G01.01.004'
911 ,p_message_type => l_error_type
912 ,p_org_id => p_issuing_estab_id
913 ,p_lookup_type => null
914 ,p_file_value => l_address_to_use
915 ,p_message_text => l_value
916 ,p_usage => 'M');
917 --
918 IF l_count = 3 THEN
919 EXIT;
920 END IF;
921 END LOOP;
922 --
923 EXCEPTION
924 WHEN OTHERS THEN raise;
925 --
926 END S10_01_issue_person;
927 --
928 --
929 -----------------------------------------------------------------------------------
930 -- COMPANY INFORMATION PROCEDURE
931 -----------------------------------------------------------------------------------
932 -----------------------------------------------------------------------------------
933 PROCEDURE S20_comp_info(p_company_id IN NUMBER,
934 p_cre_info_issue IN g_cre_info_issue%TYPE,
935 p_dads_start_date IN DATE,
936 p_dads_end_date IN DATE,
937 p_payroll_action_id IN NUMBER)
938 IS
939 --
940 l_siren_comp hr_organization_information.org_information1%TYPE;
941 l_name_comp hr_all_organization_units_tl.name%TYPE;
942 l_location hr_all_organization_units.location_id%TYPE;
943 l_location_name hr_locations_all_tl.location_code%TYPE;
944 l_cal_year varchar2(10);
945 l_partition_number varchar2(4);
946 l_currency_type varchar2(5);
947 l_NIC_HQ_estab varchar2(7);
948 l_name_estab hr_all_organization_units_tl.name%TYPE;
949 l_hq_addr_complement hr_locations_all.address_line_2%TYPE;
950 l_hq_addr_nn_street hr_locations_all.address_line_1%TYPE;
951 l_hq_addr_insee_code hr_locations_all.region_2%TYPE;
952 l_hq_addr_town hr_locations_all.region_3%TYPE;
953 l_hq_addr_zip_code hr_locations_all.postal_code%TYPE ;
954 l_hq_addr_town_city hr_locations_all.town_or_city%TYPE;
955 l_hq_addr_country_code varchar2(30);
956 l_hq_addr_country_name fnd_territories.nls_territory%TYPE;
957 l_NIC_fisc_estab varchar2(7);
958 l_fisc_location hr_all_organization_units.location_id%TYPE;
959 l_fisc_location_name hr_locations_all_tl.location_code%TYPE;
960 l_name_fisc_estab hr_all_organization_units_tl.name%TYPE;
961 l_fisc_addr_complement hr_locations_all.address_line_2%TYPE;
962 l_fisc_addr_nn_street hr_locations_all.address_line_1%TYPE;
963 l_fisc_addr_insee_code hr_locations_all.region_2%TYPE ;
964 l_fisc_addr_town hr_locations_all.region_3%TYPE ;
965 l_fisc_addr_zip_code hr_locations_all.postal_code%TYPE ;
966 l_fisc_addr_town_city hr_locations_all.town_or_city%TYPE;
967 l_fisc_addr_country_code varchar2(30);
968 l_fisc_addr_country_name fnd_territories.nls_territory%TYPE;
969 l_cre_siren hr_organization_information.org_information1%TYPE;
970 l_cre_nic varchar2(7);
971 l_cre_media hr_organization_information.org_information1%TYPE;
972 l_cre_estab_id hr_organization_information.organization_id%TYPE;
973 l_cre_estab_name hr_all_organization_units_tl.name%TYPE;
974 l_addr_to_use hr_organization_information.org_information2%TYPE;
975 l_pension_stmt_addr hr_organization_information.org_information1%TYPE;
976 l_pension_stmt_sort1 hr_organization_information.org_information2%TYPE;
977 l_pension_stmt_sort2 hr_organization_information.org_information3%TYPE;
978 l_periodicity_code_live varchar2(5);
979 l_periodicity_code_test varchar2(5);
980 l_hq_estab_id hr_organization_information.organization_id%TYPE;
981 l_fisc_estab_id hr_organization_information.organization_id%TYPE;
982 l_value fnd_new_messages.message_text%type;
983 l_error_type hr_lookups.meaning%type;
984 l_error hr_lookups.meaning%type;
985 l_warning hr_lookups.meaning%type;
986 --
987 -- Cursor for fetching S20 information
988 cursor csr_S20_G01_00(p_company_id number,
989 p_cre_estab_id_chr varchar2) is
990 select hoi_comp.org_information1 siren_comp,
991 hou_comp_tl.name name_comp,
992 hou_hq_estab_tl.name name_hq_estab,
993 hou_hq_estab.location_id location_id,
994 hloc_hq_tl.location_code location_name,
995 to_char(p_dads_end_date,'YYYY') cal_year,
996 hoi_hq_estab.organization_id hq_estab_id,
997 substr(hoi_hq_estab.org_information2,
998 length(hoi_hq_estab.org_information2)-4, 5) NIC_HQ_estab,
999 hloc_hq.address_line_2 hq_addr_complement,
1000 hloc_hq.address_line_1 hq_addr_nn_street,
1001 hloc_hq.region_2 hq_addr_insee_code,
1002 hloc_hq.region_3 hq_addr_town,
1003 hloc_hq.postal_code hq_addr_zip_code,
1004 hloc_hq.town_or_city hq_addr_town_city,/*
1005 (Afnor codes) hq_addr_country_code,*/
1006 ft_hq.nls_territory hq_addr_country_name,
1007 hoi_fisc_estab.organization_id fisc_estab_id,
1008 substr(hoi_fisc_estab.org_information2,
1009 length(hoi_fisc_estab.org_information2)-4, 5) NIC_fisc_estab,
1010 hloc_fisc_estab.location_id fisc_location_id,
1011 hloc_fisc_estab_tl.location_code fisc_location_name,
1012 hou_fisc_estab_tl.name name_fisc_estab,
1013 hloc_fisc_estab.address_line_2 fisc_addr_complement,
1014 hloc_fisc_estab.address_line_1 fisc_addr_nn_street,
1015 hloc_fisc_estab.region_2 fisc_addr_insee_code,
1016 hloc_fisc_estab.region_3 fisc_addr_town,
1017 hloc_fisc_estab.postal_code fisc_addr_zip_code,
1018 hloc_fisc_estab.town_or_city fisc_addr_town_city,/*
1019 (Afnor Codes) fisc_addr_country_code,*/
1020 ft_fisc_estab.nls_territory fisc_addr_country_name,
1021 hoi_cre_estab.organization_id cre_estab_id,
1022 hou_cre_estab_tl.name cre_name,
1023 decode(hoi_comp.org_information17
1024 ,p_cre_estab_id_chr,null
1025 ,hoi_cre_comp.org_information1) cre_siren,
1026 decode(hoi_comp.org_information17
1027 ,p_cre_estab_id_chr,null
1028 ,substr(hoi_cre_estab.org_information2
1029 ,length(hoi_cre_estab.org_information2)-4, 5)) cre_nic,
1030 decode(hoi_comp.org_information17
1031 ,p_cre_estab_id_chr,null
1032 ,hoi_cre_dads.org_information1) cre_media,
1033 decode(hoi_comp.org_information17
1034 ,p_cre_estab_id_chr,null
1035 ,hoi_cre_dads.org_information2) addr_to_use,
1036 hoi_comp_pen_stmt.org_information1 pension_stmt_addr,
1037 hoi_comp_pen_stmt.org_information2 pension_stmt_sort1,
1038 hoi_comp_pen_stmt.org_information3 pension_stmt_sort2,
1039 'AOO' periodicity_code_test
1040 from hr_all_organization_units_tl hou_comp_tl,
1041 hr_all_organization_units_tl hou_hq_estab_tl,
1042 hr_all_organization_units_tl hou_fisc_estab_tl,
1043 hr_all_organization_units_tl hou_cre_estab_tl,
1044 hr_organization_information hoi_comp,
1045 hr_organization_information hoi_comp_pen_stmt,
1046 hr_organization_information hoi_hq_estab,
1047 hr_organization_information hoi_fisc_estab,
1048 hr_all_organization_units hou_hq_estab,
1049 hr_all_organization_units hou_fisc_estab,
1050 hr_locations_all hloc_hq,
1051 hr_locations_all hloc_fisc_estab,
1052 fnd_territories ft_hq,
1053 fnd_territories ft_fisc_estab,
1054 hr_locations_all_tl hloc_hq_tl,
1055 hr_locations_all_tl hloc_fisc_estab_tl,
1056 hr_organization_information hoi_cre_estab,
1057 hr_organization_information hoi_cre_comp,
1058 hr_organization_information hoi_cre_dads
1059 where hou_comp_tl.organization_id = p_company_id
1060 and hou_comp_tl.language = userenv('LANG')
1061 and hoi_comp.organization_id = hou_comp_tl.organization_id
1062 and hoi_comp.org_information_context = 'FR_COMP_INFO'
1063 and hou_hq_estab.organization_id(+) = hoi_hq_estab.organization_id
1064 and hoi_hq_estab.organization_id(+) = hoi_comp.org_information5
1065 and hoi_hq_estab.org_information_context(+) = 'FR_ESTAB_INFO'
1066 and hloc_hq.location_id(+) = hou_hq_estab.location_id
1067 and hloc_hq.style(+) = 'FR'
1068 and ft_hq.territory_code(+) = hloc_hq.country
1069 and hloc_hq_tl.location_id(+) = hou_hq_estab.location_id
1070 and hloc_hq_tl.language(+) = userenv('LANG')
1071 and hou_hq_estab_tl.organization_id(+) = hou_hq_estab.organization_id
1072 and hou_hq_estab_tl.language(+) = userenv('LANG')
1073 and hou_fisc_estab.organization_id(+) = hoi_fisc_estab.organization_id
1074 and hoi_fisc_estab.organization_id(+) = hoi_comp.org_information16
1075 and hoi_fisc_estab.org_information_context(+) = 'FR_ESTAB_INFO'
1076 and hou_fisc_estab_tl.organization_id(+) = hou_fisc_estab.organization_id
1077 and hou_fisc_estab_tl.language(+) = userenv('LANG')
1078 and hloc_fisc_estab.location_id(+) = hou_fisc_estab.location_id
1079 and hloc_fisc_estab.style(+) = 'FR'
1080 and ft_fisc_estab.territory_code(+) = hloc_fisc_estab.country
1081 and hloc_fisc_estab_tl.location_id(+) = hou_fisc_estab.location_id
1082 and hloc_fisc_estab_tl.language(+) = userenv('LANG')
1083 and hoi_cre_estab.organization_id(+) = hoi_comp.org_information17
1084 and hoi_cre_estab.org_information_context(+) = 'FR_ESTAB_INFO'
1085 and hoi_cre_comp.organization_id(+) = hoi_cre_estab.org_information1
1086 and hoi_cre_comp.org_information_context(+) = 'FR_COMP_INFO'
1087 and hou_cre_estab_tl.organization_id(+) = hoi_cre_estab.organization_id
1088 and hou_cre_estab_tl.language(+) = userenv('LANG')
1089 and hoi_cre_dads.organization_id(+) = hoi_cre_estab.organization_id
1090 and hoi_cre_dads.org_information_context(+) = 'FR_DADS_CRE_INFO'
1091 and hoi_comp_pen_stmt.organization_id(+) = hou_comp_tl.organization_id
1092 and hoi_comp_pen_stmt.org_information_context(+) =
1093 'FR_DADS_COMP_PENSION_STMT';
1094 --
1095 BEGIN
1096 /* Initialising the local variables */
1097 l_partition_number := '11';
1098 l_currency_type :='EUR';
1099 /* End of Initialising the local variables */
1100
1101 --
1102 OPEN csr_S20_G01_00(p_company_id, to_char(p_cre_info_issue.cre_estab_id));
1103 FETCH csr_S20_G01_00 INTO l_siren_comp,
1104 l_name_comp,
1105 l_name_estab,
1106 l_location,
1107 l_location_name,
1108 l_cal_year,
1109 l_hq_estab_id,
1110 l_NIC_HQ_estab,
1111 l_hq_addr_complement,
1112 l_hq_addr_nn_street,
1113 l_hq_addr_insee_code,
1114 l_hq_addr_town,
1115 l_hq_addr_zip_code,
1116 l_hq_addr_town_city,
1117 l_hq_addr_country_name,
1118 l_fisc_estab_id,
1119 l_NIC_fisc_estab,
1120 l_fisc_location,
1121 l_fisc_location_name,
1122 l_name_fisc_estab,
1123 l_fisc_addr_complement,
1124 l_fisc_addr_nn_street,
1125 l_fisc_addr_insee_code,
1126 l_fisc_addr_town,
1127 l_fisc_addr_zip_code,
1128 l_fisc_addr_town_city,
1129 l_fisc_addr_country_name,
1130 l_cre_estab_id,
1131 l_cre_estab_name,
1132 l_cre_siren,
1133 l_cre_nic,
1134 l_cre_media,
1135 l_addr_to_use,
1136 l_pension_stmt_addr,
1137 l_pension_stmt_sort1,
1138 l_pension_stmt_sort2,
1139 l_periodicity_code_test;
1140 CLOSE csr_S20_G01_00;
1141 -- Archive CRE estab name
1142 IF l_cre_estab_name IS NOT NULL THEN
1143 archive_report_data(
1144 p_payroll_action_id => p_payroll_action_id,
1145 p_section_name => 'S20',
1146 p_sort_order => 1,
1147 p_org_id => p_company_id,
1148 p_cre_org_name => l_cre_estab_name,
1149 p_report_code => 'S10_CRE_ESTAB_NAME');
1150 END IF;
1151 -- if the fiscal and HQ establishments are same
1152 -- do not archive the address for the fiscal establishment
1153 IF l_fisc_estab_id = l_hq_estab_id THEN
1154 l_fisc_addr_complement := null;
1155 l_fisc_addr_nn_street := null;
1156 l_fisc_addr_insee_code :=null;
1157 l_fisc_addr_town := null;
1158 l_fisc_addr_zip_code := null;
1159 l_fisc_addr_town_city := null;
1160 l_fisc_addr_country_code := null;
1161 l_fisc_addr_country_name := null;
1162 END IF;
1163 -- Fetch periodicity code in case of live file
1164 IF (to_char(p_dads_start_date,'DD/MM') = '01/01' and to_char(p_dads_end_date,'DD/MM') = '31/12')
1165 OR (to_char(p_dads_start_date,'DD/MM') = '01/04' and to_char(p_dads_end_date,'DD/MM') = '31/03')
1166 THEN
1167 l_periodicity_code_live :='A00';
1168 ELSE
1169 l_periodicity_code_live :='EVE';
1170 END IF;
1171 -- Getting the error messages
1172 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
1173 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
1174 -- SIREN Number
1175 IF l_siren_comp is null THEN
1176 l_error_type := l_error;
1177 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1178 'VALUE1:'||l_name_comp, 'VALUE2: '||hr_general.decode_lookup
1179 ('FR_DADS_RUBRICS','S20.G01.00.001'), null);
1180 ELSE
1181 l_error_type := null;
1182 l_value := null;
1183 END IF;
1184 archive_data(
1185 p_payroll_action_id => p_payroll_action_id
1186 ,p_rubric_name => 'S20.G01.00.001'
1187 ,p_message_type => l_error_type
1188 ,p_org_id => p_company_id
1189 ,p_lookup_type => null
1190 ,p_file_value => l_siren_comp
1191 ,p_message_text => l_value
1192 ,p_usage => 'M');
1193 -- Name of Company
1194 IF l_name_comp is null THEN
1195 l_error_type := l_error;
1196 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1197 'VALUE1:'||hr_general.decode_lookup
1198 ('FR_DADS_RUBRICS','S20.G01.00.002'), null, null);
1199 ELSE
1200 l_error_type := null;
1201 l_value := null;
1202 END IF;
1203 archive_data(
1204 p_payroll_action_id => p_payroll_action_id
1205 ,p_rubric_name => 'S20.G01.00.002'
1206 ,p_message_type => l_error_type
1207 ,p_org_id => p_company_id
1208 ,p_lookup_type => null
1209 ,p_file_value => l_name_comp
1210 ,p_message_text => l_value
1211 ,p_usage => 'M');
1212 -- Calendar Year
1213 IF l_cal_year is null THEN
1214 l_error_type := l_error;
1215 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1216 'VALUE1:'||hr_general.decode_lookup
1217 ('FR_DADS_RUBRICS','S20.G01.00.003'), null, null);
1218 ELSE
1219 l_error_type := null;
1220 l_value := null;
1221 END IF;
1222 archive_data(
1223 p_payroll_action_id => p_payroll_action_id
1224 ,p_rubric_name => 'S20.G01.00.003'
1225 ,p_message_type => l_error_type
1226 ,p_org_id => p_company_id
1227 ,p_lookup_type => null
1228 ,p_file_value => l_cal_year
1229 ,p_message_text => l_value
1230 ,p_usage => 'M');
1231
1232 -- The values comes from the concurrent process.
1233 -- Passing in a null value to the archive and
1234 -- removing the exception validation
1235 -- Declaration Nature Code
1236 /*IF l_decl_nature_code is null THEN
1237 l_error_type := l_error;
1238 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1239 'VALUE1:'||hr_general.decode_lookup
1240 ('FR_DADS_RUBRICS','S20.G01.00.004.001'), null, null);
1241 ELSE
1242 l_error_type := null;
1243 l_value := null;
1244 END IF;*/
1245 archive_data(
1246 p_payroll_action_id => p_payroll_action_id
1247 ,p_rubric_name => 'S20.G01.00.004.001'
1248 ,p_message_type => null
1249 ,p_org_id => p_company_id
1250 ,p_lookup_type => null
1251 ,p_file_value => null
1252 ,p_message_text => null
1253 ,p_usage => 'M');
1254
1255 -- Declaration Type Code
1256 /*IF l_decl_type_code is null THEN
1257 l_error_type := l_error;
1258 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1259 'VALUE1:'||hr_general.decode_lookup
1260 ('FR_DADS_RUBRICS','S20.G01.00.004.002'), null, null);
1261 ELSIF (l_decl_nature_code = '02' OR l_decl_nature_code = '01') AND
1262 l_decl_type_code = '55' THEN
1263 l_error_type := l_error;
1264 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
1265 'VALUE1:'||hr_general.decode_lookup
1266 ('FR_DADS_RUBRICS','S20.G01.00.004.001'),
1267 'VALUE2:'||hr_general.decode_lookup
1268 ('FR_DADS_RUBRICS','S20.G01.00.004.002'), null);
1269 ELSIF l_decl_nature_code = '05' AND l_decl_type_code <> '55' THEN
1270 l_error_type := l_error;
1271 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
1272 'VALUE1:'||hr_general.decode_lookup
1273 ('FR_DADS_RUBRICS','S20.G01.00.004.001'),
1274 'VALUE2:'||hr_general.decode_lookup
1275 ('FR_DADS_RUBRICS','S20.G01.00.004.002'), null);
1276 ELSE
1277 l_error_type := null;
1278 l_value := null;
1279 END IF;*/
1280 /* Adding dummy error message and error_type as ERROR,
1281 inorder to retrieve the rubric in Exceptions Report */
1282 l_error_type := l_error;
1283 l_value := 'Dummy Error Message';
1284 archive_data(
1285 p_payroll_action_id => p_payroll_action_id
1286 ,p_rubric_name => 'S20.G01.00.004.002'
1287 ,p_message_type => l_error_type
1288 ,p_org_id => p_company_id
1289 ,p_lookup_type => null
1290 ,p_file_value => null
1291 ,p_message_text => l_value
1292 ,p_usage =>'M');
1293
1294 -- Partition Number
1295 IF l_partition_number is null THEN
1296 l_error_type := l_error;
1297 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1298 'VALUE1:'||hr_general.decode_lookup
1299 ('FR_DADS_RUBRICS','S20.G01.00.005'), null, null);
1300 ELSE
1301 l_error_type := null;
1302 l_value := null;
1303 END IF;
1304 archive_data(
1305 p_payroll_action_id => p_payroll_action_id
1306 ,p_rubric_name => 'S20.G01.00.005'
1307 ,p_message_type => l_error_type
1308 ,p_org_id => p_company_id
1309 ,p_lookup_type => 'FR_DADS_PARTITION_NUMBER'
1310 ,p_file_value => l_partition_number
1311 ,p_message_text => l_value
1312 ,p_usage => 'M');
1313 /* The value is based upon the Declaration type code. Hence it should also
1314 be written where declaration type codes are being archived
1315 -- Calendar Year to which salaries are attached
1316 IF (l_cal_year_sal is null AND
1317 (l_decl_type_code = 52 OR l_decl_type_code = 53)) THEN
1318 l_error_type := l_error;
1319 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1320 'VALUE1:'||hr_general.decode_lookup
1321 ('FR_DADS_RUBRICS','S20.G01.00.003'), null, null);
1322 ELSIF ((l_decl_type_code <> 52 AND l_decl_type_code <> 53) AND
1323 l_cal_year_sal is not null) THEN
1324 l_error_type := l_error;
1325 l_value := pay_fr_general.get_payroll_message('PAY_75183_RBR_SUPP',
1326 null, null, null);
1327 ELSE
1328 l_error_type := null;
1329 l_value := null;
1330 END IF;*/
1331 -- Archiving the value of the calendar year for
1332 -- the item 'Calendar year to which salaries are attached'
1333 -- to be retrieved conditionallly as per declaration type
1334 archive_data(
1335 p_payroll_action_id => p_payroll_action_id
1336 ,p_rubric_name => 'S20.G01.00.006'
1337 ,p_message_type => l_error_type
1338 ,p_org_id => p_company_id
1339 ,p_lookup_type => null
1340 ,p_file_value => l_cal_year
1341 ,p_message_text => l_value
1342 ,p_usage => 'C');
1343
1344 -- Currency Type
1345 IF l_currency_type is null THEN
1346 l_error_type := l_error;
1347 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1348 'VALUE1:'||hr_general.decode_lookup
1349 ('FR_DADS_RUBRICS','S20.G01.00.007'), null, null);
1350 ELSE
1351 l_error_type := null;
1352 l_value := null;
1353 END IF;
1354 archive_data(
1355 p_payroll_action_id => p_payroll_action_id
1356 ,p_rubric_name => 'S20.G01.00.007'
1357 ,p_message_type => l_error_type
1358 ,p_org_id => p_company_id
1359 ,p_lookup_type => null
1360 ,p_file_value => l_currency_type
1361 ,p_message_text => l_value
1362 ,p_usage => 'M');
1363 -- NIC of Headquarters Establishment
1364 IF l_hq_estab_id is null THEN
1365 l_error_type := l_error;
1366 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1367 'VALUE1:'||l_name_comp, 'VALUE2: '||hr_general.decode_lookup
1368 ('FR_DADS_RUBRICS','S20.G01.00.008.M'), null);
1369 ELSIF l_NIC_HQ_estab is null THEN
1370 l_error_type := l_error;
1371 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1372 'VALUE1:'||l_name_estab, 'VALUE2: '||hr_general.decode_lookup
1373 ('FR_DADS_RUBRICS','S20.G01.00.008'), null);
1374 ELSE
1375 l_error_type:=NULL;
1376 l_value:=NULL;
1377 end if;
1378 archive_data(
1379 p_payroll_action_id => p_payroll_action_id
1380 ,p_rubric_name => 'S20.G01.00.008'
1381 ,p_message_type => l_error_type
1382 ,p_org_id => p_company_id
1383 ,p_lookup_type => null
1384 ,p_file_value => l_NIC_HQ_estab
1385 ,p_message_text => l_value
1386 ,p_extra_information => l_name_estab -- to store name of hq estab
1387 ,p_usage => 'M');
1388 IF l_location is null THEN
1389 l_error_type := l_error;
1390 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1391 'VALUE1:'||l_name_estab, 'VALUE2: '||hr_general.decode_lookup
1392 ('FR_DADS_RUBRICS','S20.G01.00.009.006.M'), null);
1393 archive_data(
1394 p_payroll_action_id => p_payroll_action_id
1395 ,p_rubric_name => 'S20.G01.00.009.006'
1396 ,p_message_type => l_error_type
1397 ,p_org_id => p_company_id
1398 ,p_lookup_type => null
1399 ,p_file_value => l_hq_addr_nn_street
1400 ,p_message_text => l_value
1401 ,p_usage => 'C');
1402 -- Archive the remaining mandatory rubrics, i.e. zip code and town_or_city
1403 -- Archive zip code with null error and value
1404 archive_data(
1405 p_payroll_action_id => p_payroll_action_id
1406 ,p_rubric_name => 'S20.G01.00.009.010'
1407 ,p_message_type => null
1408 ,p_org_id => p_company_id
1409 ,p_lookup_type => null
1410 ,p_file_value => null
1411 ,p_message_text => null
1412 ,p_usage => 'M');
1413 -- Archive town_or_city with null error and value
1414 archive_data(
1415 p_payroll_action_id => p_payroll_action_id
1416 ,p_rubric_name => 'S20.G01.00.009.012'
1417 ,p_message_type => null
1418 ,p_org_id => p_company_id
1419 ,p_lookup_type => null
1420 ,p_file_value => null
1421 ,p_message_text => null
1422 ,p_usage => 'M');
1423 --
1424 ELSE
1425 --Address Complement
1426 l_error_type:=NULL;
1427 l_value:=NULL;
1428 archive_data(
1429 p_payroll_action_id => p_payroll_action_id
1430 ,p_rubric_name => 'S20.G01.00.009.001'
1431 ,p_message_type => l_error_type
1432 ,p_org_id => p_company_id
1433 ,p_lookup_type => null
1434 ,p_file_value => l_hq_addr_complement
1435 ,p_message_text => l_value
1436 ,p_usage => 'O');
1437 -- Nature and Name of the Street
1438 IF l_hq_addr_nn_street is null THEN
1439 l_error_type := l_error;
1440 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1441 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
1442 ('FR_DADS_RUBRICS','S20.G01.00.009.006'), null);
1443 ELSE
1444 l_error_type := null;
1445 l_value := null;
1446 END IF;
1447 archive_data(
1448 p_payroll_action_id => p_payroll_action_id
1449 ,p_rubric_name => 'S20.G01.00.009.006'
1450 ,p_message_type => l_error_type
1451 ,p_org_id => p_company_id
1452 ,p_lookup_type => null
1453 ,p_file_value => l_hq_addr_nn_street
1454 ,p_message_text => l_value
1455 ,p_usage => 'C');
1456 -- Insee code of the town
1457 l_error_type:=NULL;
1458 l_value:=NULL;
1459 archive_data(
1460 p_payroll_action_id => p_payroll_action_id
1461 ,p_rubric_name => 'S20.G01.00.009.007'
1462 ,p_message_type => l_error_type
1463 ,p_org_id => p_company_id
1464 ,p_lookup_type => null
1465 ,p_file_value => l_hq_addr_insee_code
1466 ,p_message_text => l_value
1467 ,p_usage => 'O');
1468 -- Name of the Town
1469 IF upper(l_hq_addr_town) = upper(l_hq_addr_town_city) THEN
1470 l_error_type := l_error;
1471 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
1472 null, null, null);
1473 ELSE
1474 l_error_type := null;
1475 l_value := null;
1476 END IF;
1477 archive_data(
1478 p_payroll_action_id => p_payroll_action_id
1479 ,p_rubric_name => 'S20.G01.00.009.009'
1480 ,p_message_type => l_error_type
1481 ,p_org_id => p_company_id
1482 ,p_lookup_type => null
1483 ,p_file_value => l_hq_addr_town
1484 ,p_message_text => l_value
1485 ,p_usage => 'C');
1486 -- Zip Code
1487 IF l_hq_addr_zip_code is null THEN
1488 l_error_type := l_error;
1489 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1490 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
1491 ('FR_DADS_RUBRICS','S20.G01.00.009.010'), null);
1492 ELSE
1493 l_error_type := null;
1494 l_value := null;
1495 END IF;
1496 archive_data(
1497 p_payroll_action_id => p_payroll_action_id
1498 ,p_rubric_name => 'S20.G01.00.009.010'
1499 ,p_message_type => l_error_type
1500 ,p_org_id => p_company_id
1501 ,p_lookup_type => null
1502 ,p_file_value => l_hq_addr_zip_code
1503 ,p_message_text => l_value
1504 ,p_usage => 'M');
1505 -- City
1506 IF l_hq_addr_town_city <> upper(l_hq_addr_town_city) THEN
1507 l_error_type := l_error;
1508 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
1509 null, null, null);
1510 ELSIF l_hq_addr_town_city is null THEN
1511 l_error_type := l_error;
1512 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1513 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
1514 ('FR_DADS_RUBRICS','S20.G01.00.009.012'), null);
1515 ELSE
1516 l_error_type := null;
1517 l_value := null;
1518 END IF;
1519 archive_data(
1520 p_payroll_action_id => p_payroll_action_id
1521 ,p_rubric_name => 'S20.G01.00.009.012'
1522 ,p_message_type => l_error_type
1523 ,p_org_id => p_company_id
1524 ,p_lookup_type => null
1525 ,p_file_value => l_hq_addr_town_city
1526 ,p_message_text => l_value
1527 ,p_usage => 'M');
1528
1529 /* Afnor Codes
1530 -- Country Code
1531 IF l_hq_addr_country_code = 'FR' THEN
1532 l_error_type := l_error;
1533 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
1534 'VALUE1: '||hr_general.decode_lookup
1535 ('FR_DADS_RUBRICS','S20.G01.00.009.013'), null, null);
1536 ELSE
1537 l_error_type := null;
1538 l_value := null;
1539 END IF;
1540 archive_data(
1541 p_payroll_action_id => p_payroll_action_id
1542 ,p_rubric_name => 'S20.G01.00.009.013'
1543 ,p_message_type => l_error_type
1544 ,p_org_id => p_company_id
1545 ,p_lookup_type => null
1546 ,p_file_value => l_hq_addr_country_code
1547 ,p_message_text => l_value
1548 ,p_usage =>); */
1549
1550 -- Country Name
1551 IF l_hq_addr_country_name is not null and l_hq_addr_country_name = 'FR' THEN
1552 l_error_type := l_error;
1553 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
1554 'VALUE1: '||hr_general.decode_lookup
1555 ('FR_DADS_RUBRICS','S20.G01.00.009.014'), null, null);
1556 ELSIF l_hq_addr_country_name is null AND
1557 (l_hq_addr_country_code is not null AND l_hq_addr_country_code
1558 <> 'FR') THEN
1559 l_error_type := l_error;
1560 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1561 'VALUE1: '||hr_general.decode_lookup
1562 ('FR_DADS_RUBRICS','S20.G01.00.009.014'), null, null);
1563 ELSE
1564 l_error_type := null;
1565 l_value := null;
1566 END IF;
1567 archive_data(
1568 p_payroll_action_id => p_payroll_action_id
1569 ,p_rubric_name => 'S20.G01.00.009.014'
1570 ,p_message_type => l_error_type
1571 ,p_org_id => p_company_id
1572 ,p_lookup_type => null
1573 ,p_file_value => l_hq_addr_country_name
1574 ,p_message_text => l_value
1575 ,p_usage => 'C');
1576 end if;-- check for location
1577 -- SIRET of Fiscal Establishment
1578 IF l_name_fisc_estab is null THEN
1579 l_error_type := l_error;
1580 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1581 'VALUE1:'||l_name_comp, 'VALUE2: '||hr_general.decode_lookup
1582 ('FR_DADS_RUBRICS','S20.G01.00.010.M'), null);
1583 archive_data(
1584 p_payroll_action_id => p_payroll_action_id
1585 ,p_rubric_name => 'S20.G01.00.010'
1586 ,p_message_type => l_error_type
1587 ,p_org_id => p_company_id
1588 ,p_lookup_type => null
1589 ,p_file_value => l_NIC_fisc_estab
1590 ,p_message_text => l_value
1591 ,p_usage => 'M');
1592 -- GO TO S20.G01.00.014.001
1593 ELSE
1594 -- NIC of the Fiscal Establishment
1595 IF l_fisc_estab_id <> l_hq_estab_id and l_NIC_fisc_estab is null THEN
1596 l_error_type := l_error;
1597 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1598 'VALUE1:'||l_name_fisc_estab, 'VALUE2: '||hr_general.decode_lookup
1599 ('FR_DADS_RUBRICS','S20.G01.00.010'), null);
1600 elsif l_fisc_estab_id = l_hq_estab_id and l_NIC_fisc_estab is not null THEN
1601 l_error_type := l_error;
1602 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1603 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1604 'S20.G01.00.010'), null, null);
1605 ELSE
1606 l_error_type := null;
1607 l_value := null;
1608 end if;
1609 archive_data(
1610 p_payroll_action_id => p_payroll_action_id
1611 ,p_rubric_name => 'S20.G01.00.010'
1612 ,p_message_type => l_error_type
1613 ,p_org_id => p_company_id
1614 ,p_lookup_type => null
1615 ,p_file_value => l_NIC_fisc_estab
1616 ,p_message_text => l_value
1617 ,p_usage => 'M');
1618
1619 --Fiscal Establishment Name
1620 IF l_fisc_estab_id = l_hq_estab_id AND l_name_fisc_estab is not null THEN
1621 l_error_type := l_error;
1622 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1623 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1624 'S20.G01.00.011'), null, null);
1625 ELSE
1626 l_error_type := null;
1627 l_value := null;
1628 END IF;
1629 archive_data(
1630 p_payroll_action_id => p_payroll_action_id
1631 ,p_rubric_name => 'S20.G01.00.011'
1632 ,p_message_type => l_error_type
1633 ,p_org_id => p_company_id
1634 ,p_lookup_type => null
1635 ,p_file_value => l_name_fisc_estab
1636 ,p_message_text => l_value
1637 ,p_usage => 'C');
1638
1639 -- Location
1640 IF l_fisc_location is null THEN
1641 l_error_type := l_error;
1642 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1643 'VALUE1:'||l_name_fisc_estab, 'VALUE2: '||hr_general.decode_lookup
1644 ('FR_DADS_RUBRICS','S20.G01.00.012.006.M'), null);
1645 archive_data(
1646 p_payroll_action_id => p_payroll_action_id
1647 ,p_rubric_name => 'S20.G01.00.012.006'
1648 ,p_message_type => l_error_type
1649 ,p_org_id => p_company_id
1650 ,p_lookup_type => null
1651 ,p_file_value => l_fisc_addr_nn_street
1652 ,p_message_text => l_value
1653 ,p_usage => 'C');
1654 -- Archive mandatory rubrics
1655 -- Zip code
1656 archive_data(
1657 p_payroll_action_id => p_payroll_action_id
1658 ,p_rubric_name => 'S20.G01.00.012.010'
1659 ,p_message_type => null
1660 ,p_org_id => p_company_id
1661 ,p_lookup_type => null
1662 ,p_file_value => null
1663 ,p_message_text => null
1664 ,p_usage => 'M');
1665 -- town or city
1666 archive_data(
1667 p_payroll_action_id => p_payroll_action_id
1668 ,p_rubric_name => 'S20.G01.00.012.012'
1669 ,p_message_type => null
1670 ,p_org_id => p_company_id
1671 ,p_lookup_type => null
1672 ,p_file_value => null
1673 ,p_message_text => null
1674 ,p_usage => 'M');
1675 --
1676 ELSE -- Location is not null
1677
1678 --Address Complement
1679 l_error_type:=NULL;
1680 l_value:=NULL;
1681 archive_data(
1682 p_payroll_action_id => p_payroll_action_id
1683 ,p_rubric_name => 'S20.G01.00.012.001'
1684 ,p_message_type => l_error_type
1685 ,p_org_id => p_company_id
1686 ,p_lookup_type => null
1687 ,p_file_value => l_fisc_addr_complement
1688 ,p_message_text => l_value
1689 ,p_usage => 'O');
1690
1691 -- Nature and name of the street
1692 IF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_nn_street is null THEN
1693 l_error_type := l_error;
1694 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1695 'VALUE1:'||l_fisc_location_name, 'VALUE2: '||hr_general.decode_lookup
1696 ('FR_DADS_RUBRICS','S20.G01.00.012.006'), null);
1697 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_nn_street is not null THEN
1698 l_error_type := l_error;
1699 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1700 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1701 'S20.G01.00.012.006'), null, null);
1702 ELSE
1703 l_error_type := null;
1704 l_value := null;
1705 END IF;
1706 archive_data(
1707 p_payroll_action_id => p_payroll_action_id
1708 ,p_rubric_name => 'S20.G01.00.012.006'
1709 ,p_message_type => l_error_type
1710 ,p_org_id => p_company_id
1711 ,p_lookup_type => null
1712 ,p_file_value => l_fisc_addr_nn_street
1713 ,p_message_text => l_value
1714 ,p_usage => 'C');
1715
1716 --insee code
1717 l_error_type := null;
1718 l_value := null;
1719 archive_data(
1720 p_payroll_action_id => p_payroll_action_id
1721 ,p_rubric_name => 'S20.G01.00.012.007'
1722 ,p_message_type => l_error_type
1723 ,p_org_id => p_company_id
1724 ,p_lookup_type => null
1725 ,p_file_value => l_fisc_addr_insee_code
1726 ,p_message_text => l_value
1727 ,p_usage => 'O');
1728
1729 -- Name of the Town
1730 IF l_fisc_estab_id <> l_hq_estab_id AND upper(l_fisc_addr_town) = upper(l_fisc_addr_town_city) THEN
1731 l_error_type := l_error;
1732 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
1733 null, null, null);
1734 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_town is not null THEN
1735 l_error_type := l_error;
1736 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1737 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1738 'S20.G01.00.012.009'), null, null);
1739 ELSE
1740 l_error_type := null;
1741 l_value := null;
1742 END IF;
1743 archive_data(
1744 p_payroll_action_id => p_payroll_action_id
1745 ,p_rubric_name => 'S20.G01.00.012.009'
1746 ,p_message_type => l_error_type
1747 ,p_org_id => p_company_id
1748 ,p_lookup_type => null
1749 ,p_file_value => l_fisc_addr_town
1750 ,p_message_text => l_value
1751 ,p_usage => 'C');
1752
1753 --Zip Code
1754 IF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_zip_code is null THEN
1755 l_error_type := l_error;
1756 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1757 'VALUE1:'||l_fisc_location_name, 'VALUE2: '||hr_general.decode_lookup
1758 ('FR_DADS_RUBRICS','S20.G01.00.012.010'), null);
1759 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_zip_code is not null THEN
1760 l_error_type := l_error;
1761 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1762 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1763 'S20.G01.00.012.010'), null, null);
1764 ELSE
1765 l_error_type := null;
1766 l_value := null;
1767 END IF;
1768 archive_data(
1769 p_payroll_action_id => p_payroll_action_id
1770 ,p_rubric_name => 'S20.G01.00.012.010'
1771 ,p_message_type => l_error_type
1772 ,p_org_id => p_company_id
1773 ,p_lookup_type => null
1774 ,p_file_value => l_fisc_addr_zip_code
1775 ,p_message_text => l_value
1776 ,p_usage => 'M');
1777
1778 --City
1779 IF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_town is null THEN
1780 l_error_type := l_error;
1781 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1782 'VALUE1:'||l_fisc_location_name, 'VALUE2: '||hr_general.decode_lookup
1783 ('FR_DADS_RUBRICS','S20.G01.00.012.012'), null);
1784 ELSIF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_town_city <> upper(l_fisc_addr_town_city) THEN
1785 l_error_type := l_error;
1786 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
1787 null, null, null);
1788 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_town_city is not null THEN
1789 l_error_type := l_error;
1790 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1791 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1792 'S20.G01.00.012.012'), null, null);
1793 ELSE
1794 l_error_type := null;
1795 l_value := null;
1796 END IF;
1797 archive_data(
1798 p_payroll_action_id => p_payroll_action_id
1799 ,p_rubric_name => 'S20.G01.00.012.012'
1800 ,p_message_type => l_error_type
1801 ,p_org_id => p_company_id
1802 ,p_lookup_type => null
1803 ,p_file_value => l_fisc_addr_town_city
1804 ,p_message_text => l_value
1805 ,p_usage => 'M');
1806 /* Afnors Code
1807 -- Country Code
1808
1809 IF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_country_code = 'FR' THEN
1810 l_error_type := l_error;
1811 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
1812 'VALUE1: '||hr_general.decode_lookup
1813 ('FR_DADS_RUBRICS','S20.G01.00.012.013'), null, null);
1814 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_country_code is not null THEN
1815 l_error_type := l_error;
1816 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1817 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
1818 'S20.G01.00.012.013'), null, null);
1819 ELSE
1820 l_error_type := null;
1821 l_value := null;
1822 END IF;
1823 archive_data(
1824 p_payroll_action_id => p_payroll_action_id
1825 ,p_rubric_name => 'S20.G01.00.012.013'
1826 ,p_message_type => l_error_type
1827 ,p_org_id => p_company_id
1828 ,p_lookup_type => null
1829 ,p_file_value => l_fisc_addr_country_code
1830 ,p_message_text => l_value
1831 ,p_usage =>);
1832 */
1833 -- Country Name
1834 IF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_country_name is not null
1835 AND l_fisc_addr_country_code = 'FR' THEN
1836 l_error_type := l_error;
1837 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
1838 'VALUE1: '||hr_general.decode_lookup
1839 ('FR_DADS_RUBRICS','S20.G01.00.012.014'), null, null);
1840 ELSIF l_fisc_estab_id <> l_hq_estab_id AND l_fisc_addr_country_name is null AND
1841 (l_fisc_addr_country_code is not null AND l_fisc_addr_country_code
1842 <> 'FR') THEN
1843 l_error_type := l_error;
1844 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
1845 'VALUE1: '||hr_general.decode_lookup
1846 ('FR_DADS_RUBRICS','S20.G01.00.012.014'), null, null);
1847 ELSIF l_fisc_estab_id = l_hq_estab_id AND l_fisc_addr_country_name is not null THEN
1848 l_error_type := l_error;
1849 l_value := pay_fr_general.get_payroll_message('PAY_75185_COUN_EST',
1850 'VALUE1: '||hr_general.decode_lookup
1851 ('FR_DADS_RUBRICS','S20.G01.00.012.014'), null, null);
1852 ELSE
1853 l_error_type := null;
1854 l_value := null;
1855 END IF;
1856 archive_data(
1857 p_payroll_action_id => p_payroll_action_id
1858 ,p_rubric_name => 'S20.G01.00.012.014'
1859 ,p_message_type => l_error_type
1860 ,p_org_id => p_company_id
1861 ,p_lookup_type => null
1862 ,p_file_value => l_fisc_addr_country_name
1863 ,p_message_text => l_value
1864 ,p_usage => 'C');
1865 END IF;--check for location of fiscal establishment
1866 END IF;--for skipping to s20.G01.00.014.001
1867
1868 -- SIREN, NIC, Type of Media, Address for CRE
1869 IF l_cre_estab_id is not null and l_cre_estab_id <> g_cre_info_issue.cre_estab_id THEN
1870 IF l_cre_siren is NULL then
1871 l_error_type:= l_error;
1872 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1873 'VALUE1:'||l_name_comp, 'VALUE2: '||hr_general.decode_lookup
1874 ('FR_DADS_RUBRICS','S20.G01.00.014.001'), null);
1875 else
1876 l_error_type:=NULL;
1877 l_value :=NULL;
1878 end if;
1879 archive_data(
1880 p_payroll_action_id => p_payroll_action_id
1881 ,p_rubric_name => 'S20.G01.00.014.001'
1882 ,p_message_type => l_error_type
1883 ,p_org_id => p_company_id
1884 ,p_lookup_type => null
1885 ,p_file_value => l_cre_siren
1886 ,p_message_text => l_value
1887 ,p_usage => 'C');
1888
1889 IF l_cre_nic is null THEN
1890 l_error_type := l_error;
1891 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1892 'VALUE1:'||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
1893 ('FR_DADS_RUBRICS','S20.G01.00.014.002'), null);
1894 else
1895 l_error_type:=NULL;
1896 l_value :=NULL;
1897 END IF;
1898 archive_data(
1899 p_payroll_action_id => p_payroll_action_id
1900 ,p_rubric_name => 'S20.G01.00.014.002'
1901 ,p_message_type => l_error_type
1902 ,p_org_id => p_company_id
1903 ,p_lookup_type => null
1904 ,p_file_value => l_cre_nic
1905 ,p_message_text => l_value
1906 ,p_usage => 'C');
1907
1908 IF l_cre_media is null THEN
1909 l_error_type := l_error;
1910 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1911 'VALUE1:'||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
1912 ('FR_DADS_RUBRICS','S20.G01.00.015'), null);
1913 else
1914 l_error_type := NULL;
1915 l_value :=NULL;
1916 end if;
1917 archive_data(
1918 p_payroll_action_id => p_payroll_action_id
1919 ,p_rubric_name => 'S20.G01.00.015'
1920 ,p_message_type => l_error_type
1921 ,p_org_id => p_company_id
1922 ,p_lookup_type => 'FR_DADS_CRE_TYPE_MEDIA'
1923 ,p_file_value => l_cre_media
1924 ,p_message_text => l_value
1925 ,p_usage => 'C');
1926 IF l_addr_to_use is null and l_cre_media='03' THEN
1927 l_error_type:= l_error;
1928 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
1929 'VALUE1:'||l_cre_estab_name, 'VALUE2: '||hr_general.decode_lookup
1930 ('FR_DADS_RUBRICS','S20.G01.00.016'), null);
1931 elsif l_cre_media ='05' and l_addr_to_use is NOT NULL then
1932 l_error_type := l_error;
1933 l_value := pay_fr_general.get_payroll_message('PAY_75184_CRE_DATA',
1934 null, null, null);
1935 ELSE
1936 l_error_type := null;
1937 l_value := null;
1938 END IF;
1939 archive_data(
1940 p_payroll_action_id => p_payroll_action_id
1941 ,p_rubric_name => 'S20.G01.00.016'
1942 ,p_message_type => l_error_type
1943 ,p_org_id => p_company_id
1944 ,p_lookup_type => null
1945 ,p_file_value => l_addr_to_use
1946 ,p_message_text => l_value
1947 ,p_usage => 'C');
1948 ELSIF l_cre_siren is null AND l_cre_nic is null AND l_cre_media is null
1949 AND l_addr_to_use is null THEN
1950 IF g_cre_info_issue.cre_siren is null AND g_cre_info_issue.cre_nic is null
1951 AND g_cre_info_issue.cre_media is null AND g_cre_info_issue.address_to_use is null THEN
1952 l_error_type := l_error;
1953 l_value := pay_fr_general.get_payroll_message('PAY_75186_CRE_DET',
1954 null, null, null);
1955 archive_data(
1956 p_payroll_action_id => p_payroll_action_id
1957 ,p_rubric_name => 'S20.G01.00.014.001'
1958 ,p_message_type => l_error_type
1959 ,p_org_id => p_company_id
1960 ,p_lookup_type => null
1961 ,p_file_value => g_cre_info_issue.cre_siren
1962 ,p_message_text => l_value
1963 ,p_usage => 'C');
1964 END IF;
1965 ELSIF l_cre_siren is not null AND l_cre_nic is not null AND l_cre_media is not null
1966 AND l_addr_to_use is not null then
1967 IF l_cre_estab_id = g_cre_info_issue.cre_estab_id THEN
1968 l_error_type := l_error;
1969 l_value := pay_fr_general.get_payroll_message('PAY_75182_CRE_S10',
1970 null, null, null);
1971 archive_data(
1972 p_payroll_action_id => p_payroll_action_id
1973 ,p_rubric_name => 'S20.G01.00.014.001'
1974 ,p_message_type => l_error_type
1975 ,p_org_id => p_company_id
1976 ,p_lookup_type => null
1977 ,p_file_value => null
1978 ,p_message_text => l_value
1979 ,p_usage => 'C');
1980 END IF;
1981 end if;
1982 l_error_type:=NULL;
1983 l_value:=NULL;
1984 archive_data(
1985 p_payroll_action_id => p_payroll_action_id
1986 ,p_rubric_name => 'S20.G01.00.017.001'
1987 ,p_message_type => l_error_type
1988 ,p_org_id => p_company_id
1989 ,p_lookup_type => 'FR_DADS_COMP_PENSION_ADDR'
1990 ,p_file_value => l_pension_stmt_addr
1991 ,p_message_text => l_value
1992 ,p_usage => 'C');
1993 archive_data(
1994 p_payroll_action_id => p_payroll_action_id
1995 ,p_rubric_name => 'S20.G01.00.017.002'
1996 ,p_message_type => l_error_type
1997 ,p_org_id => p_company_id
1998 ,p_lookup_type => 'FR_DADS_SORT_ORDER'
1999 ,p_file_value => l_pension_stmt_sort1
2000 ,p_message_text => l_value
2001 ,p_usage => 'C');
2002 archive_data(
2003 p_payroll_action_id => p_payroll_action_id
2004 ,p_rubric_name => 'S20.G01.00.017.003'
2005 ,p_message_type => l_error_type
2006 ,p_org_id => p_company_id
2007 ,p_lookup_type => 'FR_DADS_SORT_ORDER'
2008 ,p_file_value => l_pension_stmt_sort2
2009 ,p_message_text => l_value
2010 ,p_usage => 'C');
2011 -- Test Periodicity Code
2012 IF l_periodicity_code_test is null THEN
2013 l_error_type := l_error;
2014 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2015 'VALUE1:'||hr_general.decode_lookup
2016 ('FR_DADS_RUBRICS','S20.G01.00.018'), null, null);
2017 ELSE
2018 l_error_type := null;
2019 l_value := null;
2020 END IF;
2021 archive_data(
2022 p_payroll_action_id => p_payroll_action_id
2023 ,p_rubric_name => 'S20.G01.00.018'
2024 ,p_message_type => l_error_type
2025 ,p_org_id => p_company_id
2026 ,p_lookup_type => 'FR_DADS_PERIOD_CODE'
2027 ,p_file_value => l_periodicity_code_test
2028 ,p_message_text => l_value
2029 ,p_extra_information => '01' -- extra info for retrieving
2030 ,p_usage => 'M');
2031 -- Live Periodicity Code
2032 IF l_periodicity_code_live is null THEN
2033 l_error_type := l_error;
2034 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2035 'VALUE1:'||hr_general.decode_lookup
2036 ('FR_DADS_RUBRICS','S20.G01.00.018'), null, null);
2037 ELSE
2038 l_error_type := null;
2039 l_value := null;
2040 END IF;
2041 archive_data(
2042 p_payroll_action_id => p_payroll_action_id
2043 ,p_rubric_name => 'S20.G01.00.018'
2044 ,p_message_type => l_error_type
2045 ,p_org_id => p_company_id
2046 ,p_lookup_type => 'FR_DADS_PERIOD_CODE'
2047 ,p_file_value => l_periodicity_code_live
2048 ,p_message_text => l_value
2049 ,p_extra_information => '02' -- extra info for retrieving
2050 ,p_usage => 'M');
2051 --
2052 EXCEPTION
2053 WHEN OTHERS THEN raise;
2054 --
2055 END S20_comp_info;
2056 --
2057 -----------------------------------------------------------------------------------
2058 -- INSEE ESTABLISHMENT INFORMATION PROCEDURE
2059 -----------------------------------------------------------------------------------
2060 PROCEDURE S80_insee_estab (p_estab_id IN NUMBER,
2061 p_payroll_action_id IN NUMBER,
2062 p_dads_end_date IN DATE)
2063 IS
2064 --
2065 l_effective_date Date;
2066 l_estab_siren hr_organization_information.org_information1%TYPE ;
2067 l_estab_nic varchar2(7) ;
2068 l_estab_name hr_all_organization_units_tl.name%TYPE ;
2069 l_estab_addr_complement hr_locations_all.address_line_2%TYPE ;
2070 l_estab_addr_nn_street hr_locations_all.address_line_1%TYPE ;
2071 l_estab_addr_insee_code hr_locations_all.region_2%TYPE ;
2072 l_estab_addr_town hr_locations_all.region_3%TYPE ;
2073 l_estab_addr_zip_code hr_locations_all.postal_code%TYPE ;
2074 l_estab_addr_town_city hr_locations_all.town_or_city%TYPE ;
2075 l_estab_addr_country_code varchar2(30) ;
2076 l_estab_addr_country_name fnd_territories.nls_territory%TYPE ;
2077 l_estab_headcount_3112 number ;
2078 l_estab_salary_tax_code hr_organization_information.org_information3%TYPE ;
2079 l_assg_action_id number;
2080 l_value fnd_new_messages.message_text%type;
2081 l_error_type hr_lookups.meaning%type;
2082 l_error hr_lookups.meaning%type;
2083 l_warning hr_lookups.meaning%type;
2084 l_estab_location hr_all_organization_units.location_id%TYPE;
2085 l_location_name hr_locations_all_tl.location_code%TYPE;
2086 l_name_comp hr_all_organization_units_tl.name%TYPE ;
2087 --
2088 -- Cursor for s80 data
2089 cursor csr_S80_G01_00 is
2090 select hoi_estab_comp.org_information1 SIREN_estab,
2091 substr (hoi_estab.org_information2, length(hoi_estab.org_information2)-4,5) NIC_estab,
2092 hou_estab_tl.name name_estab,
2093 hoi_estab_comp_tl.name company_name,
2094 hou_estab.location_id location_id,
2095 hloc_estab_tl.location_code location_name,
2096 --
2097 hloc_estab.address_line_2 addr_compl_estab,
2098 hloc_estab.address_line_1 addr_nstreet_estab,
2099 hloc_estab.region_2 addr_insee_estab,
2100 hloc_estab.region_3 addr_town_estab,
2101 hloc_estab.postal_code addr_zip_estab,
2102 hloc_estab.town_or_city addr_towncity_estab,
2103 -- (Afnor codes) addr_cntrycode_estab ,
2104 ft_estab.nls_territory addr_cntryname_estab,
2105 --
2106 decode(hoi_sal_tax.org_information3,null, '02', '100', '02', '01') estab_salary_tax_code
2107 --
2108 from hr_all_organization_units hou_estab,
2109 hr_all_organization_units_tl hou_estab_tl,
2110 hr_organization_information hoi_estab,
2111 hr_organization_information hoi_estab_comp,
2112 hr_all_organization_units_tl hoi_estab_comp_tl,
2113 --
2114 hr_locations_all hloc_estab,
2115 hr_locations_all_tl hloc_estab_tl,
2116 fnd_territories ft_estab,
2117 --
2118 hr_organization_information hoi_sal_tax
2119 --
2120 where hou_estab.organization_id = p_estab_id
2121 and hou_estab_tl.organization_id = hou_estab.organization_id
2122 and hou_estab_tl.language = userenv('LANG')
2123 --
2124 and hoi_estab_comp_tl.organization_id(+) = hoi_estab_comp.organization_id
2125 and hoi_estab_comp_tl.language(+) = userenv('LANG')
2126 --
2127 and hoi_estab.organization_id(+) = hou_estab_tl.organization_id
2128 and hoi_estab.org_information_context(+) = 'FR_ESTAB_INFO'
2129 and hoi_estab_comp.organization_id(+) = hoi_estab.org_information1
2130 and hoi_estab_comp.org_information_context(+) = 'FR_COMP_INFO'
2131 --
2132 and hloc_estab.location_id(+) = hou_estab.location_id
2133 and hloc_estab.style(+) ='FR'
2134 --
2135 and ft_estab.territory_code(+) = hloc_estab.country
2136 --
2137 and hloc_estab_tl.location_id(+) = hloc_estab.location_id
2138 and hloc_estab_tl.language(+) = userenv('LANG')
2139 --
2140 and hoi_sal_tax.organization_id(+) = hou_estab.organization_id
2141 and hoi_sal_tax.org_information_context(+) = 'FR_ESTAB_SALARY_TAX_LIABILITY';
2142 --
2143 -- Cursor for headcount for 'active' employees
2144 -- Get all employees with their categories and
2145 -- part-time percentage as archived for S41
2146 Cursor csr_emp_headcount(c_effective_date DATE) IS
2147 Select pai_part.action_information4 part_pct
2148 from pay_action_information pai_part,
2149 pay_assignment_actions pasac,
2150 per_all_assignments_f pasg
2151 where pai_part.action_information_category = 'FR_DADS_FILE_DATA'
2152 and pai_part.action_context_id = pasac.assignment_action_id
2153 and pai_part.action_context_type = 'AAP'
2154 and pai_part.action_information1 ='S41.G01.00.020'
2155 and pai_part.action_information8 = (Select max(pai_sub.action_information8)
2156 from pay_action_information pai_sub
2157 where pai_sub.action_information_category = 'FR_DADS_FILE_DATA'
2158 and pai_sub.action_context_type = 'AAP'
2159 and pai_sub.action_context_id = pasac.assignment_action_id
2160 and pai_sub.action_information1 ='S41.G01.00.020')
2161 and pasg.establishment_id = p_estab_id
2162 and c_effective_date between
2163 pasg.effective_start_date and pasg.effective_end_date
2164 and pasg.assignment_id = pasac.assignment_id
2165 and pasac.payroll_action_id = p_payroll_action_id;
2166 --
2167 begin
2168 /* Initialising the local variables */
2169 l_estab_headcount_3112 :=0 ;
2170 /* End of Initialising the local variables */
2171
2172 --
2173 l_effective_date := to_date('31-12-'||to_char(p_dads_end_date, 'YYYY'), 'DD-MM-YYYY');
2174 --
2175 OPEN csr_S80_G01_00;
2176 FETCH csr_S80_G01_00 INTO l_estab_siren,
2177 l_estab_nic,
2178 l_estab_name ,
2179 l_name_comp,
2180 l_estab_location,
2181 l_location_name,
2182 l_estab_addr_complement,
2183 l_estab_addr_nn_street,
2184 l_estab_addr_insee_code,
2185 l_estab_addr_town,
2186 l_estab_addr_zip_code,
2187 l_estab_addr_town_city ,
2188 l_estab_addr_country_name ,
2189 l_estab_salary_tax_code;
2190 CLOSE csr_S80_G01_00;
2191 --
2192 -- Calculate headcount as on 31/12
2193 FOR emp_count_rec IN csr_emp_headcount(l_effective_date) LOOP
2194 IF emp_count_rec.part_pct IS NOT NULL THEN
2195 -- Add the percentage of part-time
2196 -- as obtained from the data archived for S41
2197 l_estab_headcount_3112 := l_estab_headcount_3112 + emp_count_rec.part_pct/10000;
2198 ELSE
2199 -- consider them full time employees and increment by 1
2200 l_estab_headcount_3112 := l_estab_headcount_3112 +1;
2201 END IF;
2202 END LOOP;
2203 -- Validation for S80
2204 -- Getting the error messages
2205 l_error := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'E');
2206 l_warning := hr_general.decode_lookup('FR_DADS_ERROR_TYPE', 'W');
2207 -- SIREN Number
2208 IF l_estab_siren is null THEN
2209 l_error_type := l_error;
2210 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2211 'VALUE1:'||l_name_comp, 'VALUE2:'||hr_general.decode_lookup
2212 ('FR_DADS_RUBRICS','S80.G01.00.001.001'), null);
2213 ELSE
2214 l_error_type := null;
2215 l_value := null;
2216 END IF;
2217 archive_data(
2218 p_payroll_action_id => p_payroll_action_id
2219 ,p_rubric_name => 'S80.G01.00.001.001'
2220 ,p_message_type => l_error_type
2221 ,p_org_id => p_estab_id
2222 ,p_lookup_type => null
2223 ,p_file_value => l_estab_siren
2224 ,p_message_text => l_value
2225 ,p_usage => 'M');
2226 --NIC of the Establishment
2227 IF l_estab_nic is null THEN
2228 l_error_type := l_error;
2229 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2230 'VALUE1:'||l_estab_name, 'VALUE2:'||hr_general.decode_lookup
2231 ('FR_DADS_RUBRICS','S80.G01.00.001.002'), null);
2232 ELSE
2233 l_error_type := null;
2234 l_value := null;
2235 END IF;
2236 archive_data(
2237 p_payroll_action_id => p_payroll_action_id
2238 ,p_rubric_name => 'S80.G01.00.001.002'
2239 ,p_message_type => l_error_type
2240 ,p_org_id => p_estab_id
2241 ,p_lookup_type => null
2242 ,p_file_value => l_estab_nic
2243 ,p_message_text => l_value
2244 ,p_usage => 'M');
2245 --Establishment Name
2246 l_error_type := null;
2247 l_value := null;
2248 archive_data(
2249 p_payroll_action_id => p_payroll_action_id
2250 ,p_rubric_name => 'S80.G01.00.002'
2251 ,p_message_type => l_error_type
2252 ,p_org_id => p_estab_id
2253 ,p_lookup_type => null
2254 ,p_file_value => l_estab_name
2255 ,p_message_text => l_value
2256 ,p_usage => 'C');
2257 --First Check Location
2258 IF l_estab_location is null THEN
2259 l_error_type := l_error;
2260 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2261 'VALUE1:'||l_estab_name, 'VALUE2: '||hr_general.decode_lookup
2262 ('FR_DADS_RUBRICS','S80.G01.00.003.006.M'), null);
2263 archive_data(
2264 p_payroll_action_id => p_payroll_action_id
2265 ,p_rubric_name => 'S80.G01.00.003.006'
2266 ,p_message_type => l_error_type
2267 ,p_org_id => p_estab_id
2268 ,p_lookup_type => null
2269 ,p_file_value => l_estab_addr_nn_street
2270 ,p_message_text => l_value
2271 ,p_usage => 'C');
2272 -- Archive mandatory rubrics
2273 -- Zip code
2274 archive_data(
2275 p_payroll_action_id => p_payroll_action_id
2276 ,p_rubric_name => 'S80.G01.00.003.010'
2277 ,p_message_type => null
2278 ,p_org_id => p_estab_id
2279 ,p_lookup_type => null
2280 ,p_file_value => null
2281 ,p_message_text => null
2282 ,p_usage => 'M');
2283 -- town or city
2284 archive_data(
2285 p_payroll_action_id => p_payroll_action_id
2286 ,p_rubric_name => 'S80.G01.00.003.012'
2287 ,p_message_type => null
2288 ,p_org_id => p_estab_id
2289 ,p_lookup_type => null
2290 ,p_file_value => null
2291 ,p_message_text => null
2292 ,p_usage => 'M');
2293 --
2294 ELSE
2295 --Address Complement
2296 l_error_type:=NULL;
2297 l_value:=NULL;
2298 archive_data(
2299 p_payroll_action_id => p_payroll_action_id
2300 ,p_rubric_name => 'S80.G01.00.003.001'
2301 ,p_message_type => l_error_type
2302 ,p_org_id => p_estab_id
2303 ,p_lookup_type => null
2304 ,p_file_value => l_estab_addr_complement
2305 ,p_message_text => l_value
2306 ,p_usage => 'O');
2307 -- Nature and Name of the Street
2308 IF l_estab_addr_nn_street is null THEN
2309 l_error_type := l_error;
2310 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2311 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
2312 ('FR_DADS_RUBRICS','S80.G01.00.003.006'), null);
2313 ELSE
2314 l_error_type := null;
2315 l_value := null;
2316 END IF;
2317 archive_data(
2318 p_payroll_action_id => p_payroll_action_id
2319 ,p_rubric_name => 'S80.G01.00.003.006'
2320 ,p_message_type => l_error_type
2321 ,p_org_id => p_estab_id
2322 ,p_lookup_type => null
2323 ,p_file_value => l_estab_addr_nn_street
2324 ,p_message_text => l_value
2325 ,p_usage => 'C');
2326 -- Insee code of the town
2327 l_error_type:=NULL;
2328 l_value:=NULL;
2329 archive_data(
2330 p_payroll_action_id => p_payroll_action_id
2331 ,p_rubric_name => 'S80.G01.00.003.007'
2332 ,p_message_type => l_error_type
2333 ,p_org_id => p_estab_id
2334 ,p_lookup_type => null
2335 ,p_file_value => l_estab_addr_insee_code
2336 ,p_message_text => l_value
2337 ,p_usage => 'O');
2338 -- Name of the Town
2339 IF upper(l_estab_addr_town) = upper(l_estab_addr_town_city) THEN
2340 l_error_type := l_error;
2341 l_value := pay_fr_general.get_payroll_message('PAY_75179_TWN_CITY',
2342 null, null, null);
2343 ELSE
2344 l_error_type := null;
2345 l_value := null;
2346 END IF;
2347 archive_data(
2348 p_payroll_action_id => p_payroll_action_id
2349 ,p_rubric_name => 'S80.G01.00.003.009'
2350 ,p_message_type => l_error_type
2351 ,p_org_id => p_estab_id
2352 ,p_lookup_type => null
2353 ,p_file_value => l_estab_addr_town
2354 ,p_message_text => l_value
2355 ,p_usage => 'C');
2356 -- Zip Code
2357 IF l_estab_addr_zip_code is null THEN
2358 l_error_type := l_error;
2359 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2360 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
2361 ('FR_DADS_RUBRICS','S80.G01.00.003.010'), null);
2362 ELSE
2363 l_error_type := null;
2364 l_value := null;
2365 END IF;
2366 archive_data(
2367 p_payroll_action_id => p_payroll_action_id
2368 ,p_rubric_name => 'S80.G01.00.003.010'
2369 ,p_message_type => l_error_type
2370 ,p_org_id => p_estab_id
2371 ,p_lookup_type => null
2372 ,p_file_value => l_estab_addr_zip_code
2373 ,p_message_text => l_value
2374 ,p_usage => 'M');
2375 -- City
2376 IF l_estab_addr_town_city <> upper(l_estab_addr_town_city) THEN
2377 l_error_type := l_error;
2378 l_value := pay_fr_general.get_payroll_message('PAY_75177_CITY_UPR',
2379 null, null, null);
2380 ELSIF l_estab_addr_town_city is null THEN
2381 l_error_type := l_error;
2382 l_value := pay_fr_general.get_payroll_message('PAY_75178_NO_DATA',
2383 'VALUE1:'||l_location_name, 'VALUE2: '||hr_general.decode_lookup
2384 ('FR_DADS_RUBRICS','S80.G01.00.003.012'), null);
2385 ELSE
2386 l_error_type := null;
2387 l_value := null;
2388 END IF;
2389 archive_data(
2390 p_payroll_action_id => p_payroll_action_id
2391 ,p_rubric_name => 'S80.G01.00.003.012'
2392 ,p_message_type => l_error_type
2393 ,p_org_id => p_estab_id
2394 ,p_lookup_type => null
2395 ,p_file_value => l_estab_addr_town_city
2396 ,p_message_text => l_value
2397 ,p_usage => 'M');
2398 /* Afnor Codes
2399 -- Country Code
2400 IF l_estab_addr_country_name = 'FR' THEN
2401 l_error_type := l_error;
2402 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
2403 'VALUE1: '||hr_general.decode_lookup
2404 ('FR_DADS_RUBRICS','S80.G01.00.003.013'), null, null);
2405 ELSE
2406 l_error_type := null;
2407 l_value := null;
2408 END IF;
2409 archive_data(
2410 p_payroll_action_id => p_payroll_action_id
2411 ,p_rubric_name => 'S80.G01.00.003.013'
2412 ,p_message_type => l_error_type
2413 ,p_org_id => p_estab_id
2414 ,p_lookup_type => null
2415 ,p_file_value => l_estab_addr_country_code
2416 ,p_message_text => l_value
2417 ,p_usage =>); */
2418 -- Country Name
2419 IF l_estab_addr_country_name is not null and l_estab_addr_country_code = 'FR' THEN
2420 l_error_type := l_error;
2421 l_value := pay_fr_general.get_payroll_message('PAY_75175_COUN_FRN',
2422 'VALUE1: '||hr_general.decode_lookup
2423 ('FR_DADS_RUBRICS','S80.G01.00.003.014'), null, null);
2424 ELSIF l_estab_addr_country_name is null AND l_estab_addr_country_code is not null
2425 AND l_estab_addr_country_code <> 'FR' THEN
2426 l_error_type := l_error;
2427 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2428 'VALUE1:'||hr_general.decode_lookup
2429 ('FR_DADS_RUBRICS','S80.G01.00.003.014'), null, null);
2430 ELSE
2431 l_error_type := null;
2432 l_value := null;
2433 END IF;
2434 archive_data(
2435 p_payroll_action_id => p_payroll_action_id
2436 ,p_rubric_name => 'S80.G01.00.003.014'
2437 ,p_message_type => l_error_type
2438 ,p_org_id => p_estab_id
2439 ,p_lookup_type => null
2440 ,p_file_value => l_estab_addr_country_name
2441 ,p_message_text => l_value
2442 ,p_usage => 'C');
2443 END IF; -- Checking Location
2444 -- Head Count
2445 IF l_estab_headcount_3112 is null then
2446 l_error_type := l_error;
2447 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2448 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
2449 'S80.G01.00.004'), null, null);
2450 ELSE
2451 l_error_type := null;
2452 l_value := null;
2453 END IF;
2454 archive_data(
2455 p_payroll_action_id => p_payroll_action_id
2456 ,p_rubric_name => 'S80.G01.00.004'
2457 ,p_message_type => l_error_type
2458 ,p_org_id => p_estab_id
2459 ,p_lookup_type => null
2460 ,p_file_value => l_estab_headcount_3112
2461 ,p_message_text => l_value
2462 ,p_usage => 'M');
2463 -- Code Salary Tax
2464 IF l_estab_salary_tax_code is null then
2465 l_error_type := l_error;
2466 l_value := pay_fr_general.get_payroll_message('PAY_75174_NOT_FOUND',
2467 'VALUE1: '||hr_general.decode_lookup ('FR_DADS_RUBRICS',
2468 'S80.G01.00.005'), null, null);
2469 ELSIF l_estab_salary_tax_code <> '01' AND l_estab_salary_tax_code <> '02'
2470 AND l_estab_salary_tax_code is not null THEN
2471 l_error_type := l_error;
2472 l_value := pay_fr_general.get_payroll_message('PAY_75192_VALID_VAL',
2473 'VALUE1: '||l_estab_name, 'VALUE2:'||hr_general.decode_lookup ('FR_DADS_RUBRICS',
2474 'S80.G01.00.005.M'), null);
2475 ELSE
2476 l_error_type := null;
2477 l_value := null;
2478 END IF;
2479 archive_data(
2480 p_payroll_action_id => p_payroll_action_id
2481 ,p_rubric_name => 'S80.G01.00.005'
2482 ,p_message_type => l_error_type
2483 ,p_org_id => p_estab_id
2484 ,p_lookup_type => 'FR_DADS_SAL_TAX_CODE'
2485 ,p_file_value => l_estab_salary_tax_code
2486 ,p_message_text => l_value
2487 ,p_usage => 'M');
2488 --
2489 EXCEPTION
2490 WHEN OTHERS THEN raise;
2491 --
2492 END S80_insee_estab;
2493 --------------------------------------------------------------------------------------------
2494 END;