[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_DADS_F_PKG
Source
1 package body PAY_FR_DADS_F_PKG as
2 /* $Header: pyfrdadf.pkb 120.0 2005/05/29 04:59 appldev noship $ */
3 -------------------------------------------------------------------------------
4 -- PROCEDURE PROCESS (Main procedure)
5 -------------------------------------------------------------------------------
6 Procedure PROCESS(errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 P_BUSINESS_GROUP_ID IN NUMBER,
9 P_ISSUING_ESTABLISHMENT IN NUMBER,
10 P_DADS_REFERENCE IN VARCHAR2,
11 P_REPORT_TYPE IN VARCHAR2,
12 P_DUMMY IN VARCHAR2, --Added for enabling/disabling P_SUBMISSION_TYPE
13 P_DECLARATION_NATURE IN VARCHAR2,
14 P_DECLARATION_TYPE IN VARCHAR2,
15 P_REPORT_INCLUSIONS IN VARCHAR2,
16 P_SORT_ORDER_1 IN VARCHAR2,
17 P_SORT_ORDER_2 IN VARCHAR2,
18 P_SUBMISSION_TYPE IN VARCHAR2) is
19 begin
20 --
21 Fnd_file.put_line(FND_FILE.OUTPUT,'OUTPUT');
22 fnd_file.put_line(FND_FILE.OUTPUT, 'p_business_group_id '|| p_business_group_id);
23 fnd_file.put_line(FND_FILE.OUTPUT, 'p_issuing_establishment '|| p_issuing_establishment);
24 fnd_file.put_line(FND_FILE.OUTPUT, 'p_dads_reference '|| p_dads_reference);
25 fnd_file.put_line(FND_FILE.OUTPUT, 'p_report_type '|| p_report_type);
26 fnd_file.put_line(FND_FILE.OUTPUT, 'p_declaration_nature '|| p_declaration_nature);
27 fnd_file.put_line(FND_FILE.OUTPUT, 'p_declaration_type '|| p_declaration_type);
28 fnd_file.put_line(FND_FILE.OUTPUT, 'p_report_inclusions '|| p_report_inclusions);
29 --#3553620 printing the sort_order meaning instead of sort_order code
30 IF p_sort_order_1 IS NULL THEN
31 fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_1 '|| p_sort_order_1);
32 ELSE
33 fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_1 '||hr_general.decode_lookup
34 ('FR_DADS_SORT_ORDER',p_sort_order_1));
35 END IF;
36 IF p_sort_order_2 IS NULL THEN
37 fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_2 '|| p_sort_order_2);
38 ELSE
39 fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_2 '||hr_general.decode_lookup
40 ('FR_DADS_SORT_ORDER',p_sort_order_2));
41 END IF;
42 -- fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_1 '|| p_sort_order_1);
43 -- fnd_file.put_line(FND_FILE.OUTPUT, 'p_sort_order_2 '|| p_sort_order_2);
44 fnd_file.put_line(FND_FILE.OUTPUT, 'p_submission_type '|| p_submission_type);
45 --
46 IF p_report_type ='F' OR p_report_type = 'R' THEN
47 write_user_file_report(P_BUSINESS_GROUP_ID => P_BUSINESS_GROUP_ID ,
48 P_ISSUING_ESTABLISHMENT => P_ISSUING_ESTABLISHMENT,
49 P_DADS_REFERENCE => P_DADS_REFERENCE ,
50 P_REPORT_TYPE => P_REPORT_TYPE,
51 P_DECLARATION_NATURE => P_DECLARATION_NATURE,
52 P_DECLARATION_TYPE => P_DECLARATION_TYPE,
53 P_REPORT_INCLUSIONS => P_REPORT_INCLUSIONS ,
54 P_SORT_ORDER_1 => P_SORT_ORDER_1,
55 P_SORT_ORDER_2 => P_SORT_ORDER_2 ,
56 P_SUBMISSION_TYPE => P_SUBMISSION_TYPE);
57 ELSIF p_report_type = 'E' THEN
58 control_proc (P_BUSINESS_GROUP_ID,
59 P_ISSUING_ESTABLISHMENT,
60 P_DADS_REFERENCE,
61 P_REPORT_TYPE,
62 P_DECLARATION_NATURE,
63 P_DECLARATION_TYPE,
64 P_REPORT_INCLUSIONS,
65 P_SORT_ORDER_1,
66 P_SORT_ORDER_2);
67 END IF;
68 --
69 retcode := 0;
70 --
71 EXCEPTION
72 WHEN OTHERS THEN raise;
73 --
74 end PROCESS;
75 --
76 -------------------------------------------------------------------------------
77 -- PROCEDURE WRITE_USER_FILE_DATA (to write user or file report)
78 -------------------------------------------------------------------------------
79 --
80 PROCEDURE write_user_file_report(P_BUSINESS_GROUP_ID IN NUMBER,
81 P_ISSUING_ESTABLISHMENT IN NUMBER,
82 P_DADS_REFERENCE IN VARCHAR2,
83 P_REPORT_TYPE IN VARCHAR2,
84 P_DECLARATION_NATURE IN VARCHAR2,
85 P_DECLARATION_TYPE IN VARCHAR2,
86 P_REPORT_INCLUSIONS IN VARCHAR2,
87 P_SORT_ORDER_1 IN VARCHAR2,
88 P_SORT_ORDER_2 IN VARCHAR2,
89 P_SUBMISSION_TYPE IN VARCHAR2)
90 IS
91 --
92 TYPE ref_cursor_type IS REF CURSOR;
93 ref_csr_asg_action ref_cursor_type;
94 l_asg_action_id number;
95 --
96 l_payroll_action_id number;
97 l_sort_rubric1 varchar2(30);
98 l_sort_rubric2 varchar2(30);
99 l_s30_select varchar2(1000);
100 l_s30_where varchar2(1000);
101 l_s30_cond_from varchar2(1000);
102 l_s30_cond_where varchar2(1000);
103 l_s30_order_by varchar2(100);
104 l_s30_cond_order_by varchar2(100);
105 --
106 l_header_issue_estab varchar2(50);
107 l_cre_estab_text hr_lookups.meaning%type;
108 l_s10_cre_estab varchar2(50);
109 l_s10_heading_text hr_lookups.meaning%type;
110 l_issuing_estab_text hr_lookups.meaning%type;
111 --
112 l_s20_heading_text hr_lookups.meaning%type;
113 l_comp_text hr_lookups.meaning%type;
114 l_hq_text hr_lookups.meaning%type;
115 l_fisc_text hr_lookups.meaning%type;
116 l_comp_name varchar2(100);
117 l_hq_name varchar2(100);
118 l_fisc_name varchar2(100);
119 l_s20_cre_estab varchar2(50);
120 --
121 l_s30_heading_text hr_lookups.meaning%type;
122 l_header_emp_number varchar2(50);
123 l_header_emp_title varchar2(50);
124 l_header_first_name varchar2(100);
125 l_header_last_name varchar2(100);
126 l_emp_name_text hr_lookups.meaning%type;
127 l_emp_number_text hr_lookups.meaning%type;
128 --
129 l_s41_heading_text hr_lookups.meaning%type;
130 l_emp_estab_text hr_lookups.meaning%type;
131 l_start_date_text hr_lookups.meaning%type;
132 l_end_date_text hr_lookups.meaning%type;
133 l_emp_header_estab_name varchar2(100);
134 l_header_start_period varchar2(50);
135 l_header_end_period varchar2(50);
136 --
137 l_s80_heading_text hr_lookups.meaning%type;
138 l_s80_header_estab_name varchar2(100);
139 l_s80_estab_text hr_lookups.meaning%type;
140 --
141 l_s90_heading_text hr_lookups.meaning%type;
142
143 -- Cursor for fetching the payroll action
144 Cursor csr_payroll_action is
145 Select action_context_id
146 from pay_action_information
147 where action_context_type = 'PA'
148 and action_information_category = 'FR_DADS_FILE_DATA'
149 and action_information1 = 'S10.G01.00.004'
150 and action_information4 = p_dads_reference;
151 --
152 -- Cursor for fetching S10 values
153 Cursor csr_get_S10_rec(c_payroll_action_id number) IS
154 Select action_information1 rubric_code,
155 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
156 action_information4 file_value,
157 action_information5 user_value,
158 action_information9 usage
159 from pay_action_information
160 where action_context_id = c_payroll_action_id
161 and action_context_type = 'PA'
162 and action_information_category = 'FR_DADS_FILE_DATA'
163 and action_information1 like 'S10%'
164 and action_information3 = p_issuing_establishment
165 order by action_information1;
166 --
167 -- Cursor for fetching the number of S20 records
168 -- sorted alphabetically by company name
169 Cursor csr_count_comp(c_payroll_action_id number) is
170 Select distinct action_information3 company_id,
171 action_information4 comp_name
172 from pay_action_information
173 where action_information1 = 'S20.G01.00.002'
174 and action_context_id = c_payroll_action_id
175 and action_information_category = 'FR_DADS_FILE_DATA'
176 and action_context_type = 'PA'
177 order by action_information4;
178 --
179 -- Cursor for fetching S20 records
180 Cursor csr_get_S20_data(c_payroll_action_id number,
181 c_company_id number)is
182 Select action_information1 rubric_code,
183 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
184 action_information4 file_value,
185 action_information5 user_value,
186 action_information7 extra_info,
187 action_information9 usage
188 from pay_action_information
189 where action_context_id = c_payroll_action_id
190 and action_context_type = 'PA'
191 and action_information_category = 'FR_DADS_FILE_DATA'
192 and action_information1 like 'S20%'
193 and action_information3 = c_company_id
194 order by action_information1;
195 --
196 -- Cursor for fetching S30 records
197 Cursor csr_get_s30_data(c_asg_action_id number,
198 c_company_id number)is
199 Select action_information1 rubric_code,
200 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
201 action_information4 file_value,
202 action_information5 user_value,
203 action_information9 usage
204 from pay_action_information
205 where action_context_id = c_asg_action_id
206 and action_context_type = 'AAP'
207 and action_information_category = 'FR_DADS_FILE_DATA'
208 and action_information1 like 'S30%'
209 and action_information3 = c_company_id
210 order by action_information1;
211 --
212 -- Cursor for fetching number of S41 records
213 -- for each S30 record
214 Cursor csr_count_s41(c_asg_action_id number,
215 c_company_id number) is
216 Select distinct action_information8 ID2
217 from pay_action_information
218 where action_context_id = c_asg_action_id
219 and action_context_type = 'AAP'
220 and action_information_category = 'FR_DADS_FILE_DATA'
221 and action_information1 like 'S41%'
222 and action_information3 = c_company_id
223 order by action_information8;
224 --
225 -- Cursor for fetching s41 records
226 Cursor csr_get_s41_data(c_asg_action_id number,
227 c_company_id number,
228 c_s41_id2 number) is
229 Select action_information1 rubric_code,
230 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
231 action_information4 file_value,
232 action_information5 user_value,
233 action_information9 usage
234 from pay_action_information
235 where action_context_id = c_asg_action_id
236 and action_context_type = 'AAP'
237 and action_information_category = 'FR_DADS_FILE_DATA'
238 and action_information1 like 'S41%'
239 and action_information3 = c_company_id
240 and action_information8 = c_s41_id2
241 order by action_information1;
242 --
243 -- Cursor to fetch number of INSEE establishments
244 Cursor csr_count_s80(c_payroll_action_id number) is
245 select distinct action_information3 estab_id
246 from pay_action_information
247 where action_context_id = c_payroll_action_id
248 and action_context_type = 'PA'
249 and action_information_category = 'FR_DADS_FILE_DATA'
250 and action_information1 like 'S80%';
251 --
252 -- Cursor to get S80 data
253 Cursor csr_get_s80_data(c_payroll_action_id number,
254 c_estab_id number)is
255 Select action_information1 rubric_code,
256 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
257 action_information4 file_value,
258 action_information5 user_value,
259 action_information9 usage
260 from pay_action_information
261 where action_context_id = c_payroll_action_id
262 and action_context_type = 'PA'
263 and action_information_category = 'FR_DADS_FILE_DATA'
264 and action_information1 like 'S80%'
265 and action_information3 = c_estab_id
266 order by action_information1;
267 --
268 -- Cursor to get S90 data
269 Cursor csr_get_s90_data(c_payroll_action_id number)is
270 Select action_information1 rubric_code,
271 hr_general.decode_lookup ('FR_DADS_RUBRICS',action_information1) rubric_meaning,
272 action_information4 file_value,
273 action_information5 user_value,
274 action_information9 usage
275 from pay_action_information
276 where action_context_id = c_payroll_action_id
277 and action_context_type = 'PA'
278 and action_information_category = 'FR_DADS_FILE_DATA'
279 and action_information1 like 'S90%'
280 order by action_information1;
281 --
282 -- Cursor for S10 header
283 Cursor csr_s10_header_data(c_payroll_action_id number) is
284 Select issue.action_information4 issue_estab,
285 cre.action_information5 cre_estab_name
286 from pay_action_information issue,
287 pay_action_information cre
288 where issue.action_context_id = c_payroll_action_id
289 and issue.action_context_type = 'PA'
290 and issue.action_information_category = 'FR_DADS_FILE_DATA'
291 and issue.action_information1 = 'S10.G01.00.002'
292 --
293 and cre.action_context_id(+) = issue.action_context_id
294 and cre.action_context_type(+) = 'PA'
295 and cre.action_information_category(+) = 'FR_DADS_REPORT_DATA'
296 and cre.action_information1(+) = 'S10'
297 and cre.action_information3(+) = P_ISSUING_ESTABLISHMENT;
298 --
299 -- Cursor for S20 header
300 Cursor csr_s20_header_data(c_payroll_action_id number,
301 c_company_id number) is
302 Select comp.action_information4 comp_name,
303 nvl(cre_s20.action_information5,null) cre_name,
304 nvl(hq.action_information7,null) hq_name,
305 nvl(fisc.action_information4,null) fisc_name
306 from pay_action_information comp,
307 pay_action_information cre_s20,
308 pay_action_information hq,
309 pay_action_information fisc
310 where comp.action_context_id = c_payroll_action_id
311 and comp.action_context_type = 'PA'
312 and comp.action_information_category = 'FR_DADS_FILE_DATA'
313 and comp.action_information1 = 'S20.G01.00.002'
314 and comp.action_information3 = c_company_id
315 --
316 and cre_s20.action_context_id(+) = comp.action_context_id
317 and cre_s20.action_context_type(+) = 'PA'
318 and cre_s20.action_information_category(+) = 'FR_DADS_REPORT_DATA'
319 and cre_s20.action_information1 (+)= 'S20'
320 and cre_s20.action_information3(+) = comp.action_information3
321 --
322 and hq.action_context_id(+) = comp.action_context_id
323 and hq.action_context_type(+) = 'PA'
324 and hq.action_information_category(+) = 'FR_DADS_FILE_DATA'
325 and hq.action_information1 (+)= 'S20.G01.00.008'
326 and hq.action_information3(+) = comp.action_information3
327 --
328 and fisc.action_context_id(+) = comp.action_context_id
329 and fisc.action_context_type(+) = 'PA'
330 and fisc.action_information_category(+) = 'FR_DADS_FILE_DATA'
331 and fisc.action_information1(+)='S20.G01.00.011'
332 and fisc.action_information3(+) = comp.action_information3;
333 --
334 -- Cursor for S30 header
335 Cursor csr_s30_header_data(c_asg_action_id number,
336 c_company_id number) is
337 Select nvl(title.action_information5,null) emp_title,
338 nvl(title.action_information8 ,null) emp_number,
339 nvl(first_name.action_information4,null) emp_first,
340 nvl(last_name.action_information4,null) emp_last
341 from pay_action_information title,
342 pay_action_information first_name,
343 pay_action_information last_name
344 where title.action_context_id = c_asg_action_id
345 and title.action_context_type = 'AAP'
346 and title.action_information_category = 'FR_DADS_FILE_DATA'
347 and title.action_information1 = 'S30.G01.00.007'
348 and title.action_information3 = c_company_id
349 --
350 and first_name.action_context_id = c_asg_action_id
351 and first_name.action_context_type = 'AAP'
352 and first_name.action_information_category = 'FR_DADS_FILE_DATA'
353 and first_name.action_information1 = 'S30.G01.00.003'
354 and first_name.action_information3 = c_company_id
355 --
356 and last_name.action_context_id = c_asg_action_id
357 and last_name.action_context_type = 'AAP'
358 and last_name.action_information_category = 'FR_DADS_FILE_DATA'
359 and last_name.action_information1 = 'S30.G01.00.004'
360 and last_name.action_information3 = c_company_id;
361 --
362 -- Cursor for S41 header
363 cursor csr_s41_header_data(c_asg_action_id number,
364 c_company_id number,
365 c_s41_id2 number) is
366 Select hou_tl_estab.name emp_estab,
367 start_period.action_information4 period_start,
368 end_period.action_information4 period_end
369 from pay_action_information estab,
370 pay_action_information start_period,
371 pay_action_information end_period,
372 hr_all_organization_units_tl hou_tl_estab
373 where estab.action_context_id = c_asg_action_id
374 and estab.action_context_type = 'AAP'
375 and estab.action_information_category = 'FR_DADS_FILE_DATA'
376 and estab.action_information1 = 'S41.G01.00.005'
377 and estab.action_information3 = c_company_id
378 and estab.action_information8 = c_s41_id2
379 and hou_tl_estab.organization_id(+) = estab.action_information7
380 and hou_tl_estab.language(+) = userenv('LANG')
381 --
382 and start_period.action_context_id = c_asg_action_id
383 and start_period.action_context_type = 'AAP'
384 and start_period.action_information_category = 'FR_DADS_FILE_DATA'
385 and start_period.action_information1 = 'S41.G01.00.001'
386 and start_period.action_information3 = c_company_id
387 and start_period.action_information8 = c_s41_id2
388 --
389 and end_period.action_context_id = c_asg_action_id
390 and end_period.action_context_type = 'AAP'
391 and end_period.action_information_category = 'FR_DADS_FILE_DATA'
392 and end_period.action_information1 = 'S41.G01.00.003'
393 and end_period.action_information3 = c_company_id
394 and end_period.action_information8 = c_s41_id2;
395 --
396 -- Cursor for S80 header
397 Cursor csr_s80_header_data(c_payroll_action_id number,
398 c_estab_id number)is
399 Select action_information4 estab_name
400 from pay_action_information
401 where action_context_id = c_payroll_action_id
402 and action_context_type = 'PA'
403 and action_information_category = 'FR_DADS_FILE_DATA'
404 and action_information1 = 'S80.G01.00.002'
405 and action_information3 = c_estab_id;
406 --
407 -- Procedure for writing into files
408 PROCEDURE write_into_file (p_report_type varchar2,
409 p_rubric_code varchar2,
410 p_rubric_desc varchar2,
411 p_file_value varchar2,
412 p_user_value varchar2) is
413 --
414 BEGIN
415 --
416 fnd_file.put(fnd_file.output, p_rubric_code);
417 IF p_report_type = 'F' THEN
418 -- write the file value
419 fnd_file.put(fnd_file.output, ' '||p_file_value);
420 ELSIF p_report_type ='R' THEN
421 -- put the rubric description
422 fnd_file.put(fnd_file.output, ' '||p_rubric_desc);
423 --Bug 3756137
424 if p_rubric_code <> 'S41.G01.00.026' or p_file_value <> '99999' then
425 -- write the file value
426 fnd_file.put(fnd_file.output, ' '||p_file_value);
427 end if;
428 --Bug 3756137
429 IF p_user_value IS NOT NULL THEN
430 -- write the user value
431 fnd_file.put(fnd_file.output, ' '||p_user_value);
432 END IF;
433 END IF;
434 -- insert a line after each rubric
435 fnd_file.new_line(fnd_file.output, 1);
436 --
437 --
438 EXCEPTION
439 WHEN OTHERS THEN raise;
440 --
441 END write_into_file;
442 --
443 -- Function for returning sort rubrics
444 FUNCTION get_sort_rubrics(p_sort_order VARCHAR2) return varchar2
445 IS
446 BEGIN
447 --
448 IF p_sort_order = '31' THEN
449 -- return rubric for zip code
450 RETURN 'S30.G01.00.008.010';
451 ELSIF p_sort_order = '32' THEN
452 -- return rubric for name
453 RETURN 'S30.G01.00.004';
454 ELSIF p_sort_order = '33' THEN
455 -- return rubric for ss number
456 RETURN 'S30.G01.00.001';
457 ELSIF p_sort_order = '34' THEN
458 -- return rubric for ss number
459 -- to pick up the id2 col for emp number
460 RETURN 'S30.G01.00.001';
461 END IF;
462 --
463 --
464 EXCEPTION
465 WHEN OTHERS THEN raise;
466 --
467 END get_sort_rubrics;
468 --
469 BEGIN
470 -- fetch the sort order rubrics
471 l_sort_rubric1 := get_sort_rubrics(p_sort_order_1);
472 -- As p_sort_order_2 is not a mandatory parameter
473 IF p_sort_order_2 IS NOT NULL THEN
474 l_sort_rubric2 := get_sort_rubrics(p_sort_order_2);
475 END IF;
476 -- fetch the payroll action
477 OPEN csr_payroll_action;
478 FETCH csr_payroll_action INTO l_payroll_action_id;
479 CLOSE csr_payroll_action;
480 -- Check for report inclusions
481 IF P_REPORT_INCLUSIONS ='ORG' OR P_REPORT_INCLUSIONS='ALL' THEN
482 -- WRITE S10 DATA
483 -- print header for S10
484 OPEN csr_s10_header_data(l_payroll_action_id);
485 FETCH csr_s10_header_data INTO l_header_issue_estab, l_s10_cre_estab;
486 CLOSE csr_s10_header_data;
487 -- get lookup meanings
488 l_s10_heading_text :=hr_general.decode_lookup('FR_DADS_HEADINGS','S10_ISSUE_ESTAB_INFO');
489 l_issuing_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S10_ISSUE_ESTAB_NAME');
490 l_cre_estab_text:= hr_general.decode_lookup('FR_DADS_HEADINGS','S10_CRE_ESTAB_NAME');
491 --
492 fnd_file.new_line(fnd_file.output,2);
493 fnd_file.put_line(fnd_file.output,l_s10_heading_text);
494 fnd_file.put_line(fnd_file.output,l_issuing_estab_text ||' : '||l_header_issue_estab);
495 fnd_file.put_line(fnd_file.output,l_cre_estab_text||' : '||l_s10_cre_estab);
496 fnd_file.new_line(fnd_file.output, 1);
497 --
498 -- fetch the values for s10
499 FOR get_s10_rec IN csr_get_S10_rec(l_payroll_action_id) LOOP
500 -- print only mandatory or non-null non-mandatory fields
501 IF get_s10_rec.usage ='M'
502 OR (get_s10_rec.usage <>'M' AND get_s10_rec.file_value IS NOT NULL)
503 THEN
504 -- Choose the correct archived send code
505 IF get_s10_rec.rubric_code <> 'S10.G01.00.010'
506 OR (get_s10_rec.rubric_code = 'S10.G01.00.010'
507 AND substr(get_s10_rec.file_value,1,4) = substr(P_SUBMISSION_TYPE,1,4)) THEN
508 -- write the values of S10
509 write_into_file (p_report_type => p_report_type,
510 p_rubric_code => get_s10_rec.rubric_code,
511 p_rubric_desc => get_s10_rec.rubric_meaning,
512 p_file_value => get_s10_rec.file_value,
513 p_user_value => get_s10_rec.user_value);
514 --
515 END IF;
516 -- end of check for usage
517 END IF;
518 END LOOP;
519 -- END OF WRITING S10 DATA
520 END IF; --end of check for report inclusions
521 -- fetch the number of companies archived for the issuing estab
522 FOR comp_rec IN csr_count_comp(l_payroll_action_id) LOOP
523 -- Check for report inclusions
524 IF P_REPORT_INCLUSIONS ='ORG' OR P_REPORT_INCLUSIONS='ALL' THEN
525 -- WRITE S20 DATA
526 -- print header for S20
527 -- get lookup meanings
528 l_s20_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_COMP_INFO');
529 l_comp_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_COMP_NAME');
530 l_hq_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_HQ_ESTAB_NAME');
531 l_fisc_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_FISC_ESTAB_NAME');
532 l_cre_estab_text:= hr_general.decode_lookup('FR_DADS_HEADINGS','S10_CRE_ESTAB_NAME');
533 -- get cursor values
534 OPEN csr_s20_header_data(l_payroll_action_id,comp_rec.company_id);
535 FETCH csr_s20_header_data INTO l_comp_name, l_s20_cre_estab, l_hq_name, l_fisc_name;
536 CLOSE csr_s20_header_data;
537 -- write the header into the file
538 fnd_file.new_line(fnd_file.output,2);
539 fnd_file.put_line(fnd_file.output,l_s20_heading_text);
540 fnd_file.put_line(fnd_file.output,l_comp_text||' : '||l_comp_name);
541 fnd_file.put_line(fnd_file.output,l_hq_text||' : '||l_hq_name);
542 fnd_file.put_line(fnd_file.output,l_fisc_text||' : '||l_fisc_name);
543 fnd_file.put_line(fnd_file.output,l_cre_estab_text||' : '||l_s20_cre_estab);
544 fnd_file.new_line(fnd_file.output,1);
545 --
546 -- Fetch S20 data
547 FOR S20_data_rec IN csr_get_S20_data(l_payroll_action_id,
548 comp_rec.company_id) LOOP
549 -- print only mandatory or non-null non-mandatory fields
550 IF S20_data_rec.usage ='M'
551 OR (S20_data_rec.usage <>'M' AND S20_data_rec.file_value IS NOT NULL)
552 THEN
553 -- Choose the correct periodicity code
554 IF S20_data_rec.rubric_code = 'S20.G01.00.018' THEN
555 --
556 IF substr(S20_data_rec.extra_info,1,4) = substr(P_SUBMISSION_TYPE,1,4) THEN
557 --
558 -- Write S20 data to the report
559 write_into_file (p_report_type => p_report_type,
560 p_rubric_code => S20_data_rec.rubric_code,
561 p_rubric_desc => S20_data_rec.rubric_meaning,
562 p_file_value => S20_data_rec.file_value,
563 p_user_value => S20_data_rec.user_value);
564 --
565 END IF;
566 --
567 ELSIF S20_data_rec.rubric_code = 'S20.G01.00.004.001' THEN
568 -- Write the value as obtained from report parameter
569 write_into_file (p_report_type => p_report_type,
570 p_rubric_code => S20_data_rec.rubric_code,
571 p_rubric_desc => S20_data_rec.rubric_meaning,
572 p_file_value => p_declaration_nature,
573 p_user_value => hr_general.decode_lookup('FR_DADS_DECL_NATURE_CODE',p_declaration_nature));
574 --
575 ELSIF S20_data_rec.rubric_code = 'S20.G01.00.004.002' THEN
576 -- Write the value as obtained from report parameter
577 write_into_file (p_report_type => p_report_type,
578 p_rubric_code => S20_data_rec.rubric_code,
579 p_rubric_desc => S20_data_rec.rubric_meaning,
580 p_file_value => p_declaration_type,
581 p_user_value => hr_general.decode_lookup('FR_DADS_DECL_TYPE_CODE',p_declaration_type));
582 --
583 ELSIF S20_data_rec.rubric_code = 'S20.G01.00.006' THEN
584 -- check the declaration type
585 IF p_declaration_type = 53 THEN -- if the type is 'correction'
586 -- Write the calendar year to which salaries are attached
587 write_into_file (p_report_type => p_report_type,
588 p_rubric_code => S20_data_rec.rubric_code,
589 p_rubric_desc => S20_data_rec.rubric_meaning,
590 p_file_value => S20_data_rec.file_value,
591 p_user_value => S20_data_rec.user_value);
592 --
593 END IF;
594 --
595 ELSE
596 -- Write S20 data to the report
597 write_into_file (p_report_type => p_report_type,
598 p_rubric_code => S20_data_rec.rubric_code,
599 p_rubric_desc => S20_data_rec.rubric_meaning,
600 p_file_value => S20_data_rec.file_value,
601 p_user_value => S20_data_rec.user_value);
602 --
603 END IF;
604 --
605 END IF;
606 -- end of check for usage
607 END LOOP;
608 END IF;
609 -- Exclude S30 and S41 if declaration nature code is '05'
610 IF p_declaration_nature <> '05'
611 -- check for report inclusions
612 AND (P_REPORT_INCLUSIONS= 'EMP' OR P_REPORT_INCLUSIONS= 'ALL') THEN
613 -- build up the query conditionally
614 l_s30_select := 'Select pasac.assignment_action_id
615 from pay_assignment_actions pasac,
616 pay_action_information pacinfo_1';
617 l_s30_where :=' where pasac.payroll_action_id = '||l_payroll_action_id||'
618 and pacinfo_1.action_context_id = pasac.assignment_action_id
619 and pacinfo_1.action_context_type =''AAP''
620 and pacinfo_1.action_information1 = '|| '''' ||l_sort_rubric1|| ''''||'
621 and pacinfo_1.action_information3 = '||comp_rec.company_id||'';
622 -- As p_sort_order_2 is not a mandatory parameter
623 IF p_sort_order_2 IS NOT NULL THEN
624 l_s30_cond_from := ' ,pay_action_information pacinfo_2';
625 l_s30_cond_where := 'and pacinfo_2.action_context_id = pasac.assignment_action_id
626 and pacinfo_2.action_context_type =''AAP''
627 and pacinfo_2.action_information1 = '|| '''' ||l_sort_rubric2|| ''''||'
628 and pacinfo_2.action_information3 = '||comp_rec.company_id||'';
629 IF p_sort_order_2 = '34' THEN
630 l_s30_cond_order_by := ',pacinfo_2.action_information8';
631 ELSE
632 l_s30_cond_order_by := ',pacinfo_2.action_information4';
633 END IF;
634 ELSE
635 l_s30_cond_from := '';
636 l_s30_cond_where := '';
637 l_s30_cond_order_by := '';
638 END IF;
639 IF p_sort_order_1 = '34' THEN
640 l_s30_order_by := ' order by pacinfo_1.action_information8';
641 ELSE
642 l_s30_order_by := ' order by pacinfo_1.action_information4';
643 END IF;
644 -- fetch assignment action ids for this payroll action ids and loop
645 OPEN ref_csr_asg_action FOR l_s30_select||l_s30_cond_from||l_s30_where||l_s30_cond_where||l_s30_order_by||l_s30_cond_order_by;
646 LOOP
647 FETCH ref_csr_asg_action INTO l_asg_action_id;
648 EXIT WHEN ref_csr_asg_action%NOTFOUND;
649 -- WRITE S30 DATA
650 -- print header for S30
651 -- get the lookup values
652 l_s30_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_INFO');
653 l_emp_name_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_NAME');
654 l_emp_number_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_NUMBER');
655 -- fetch the header values
656 OPEN csr_s30_header_data(l_asg_action_id,
657 comp_rec.company_id);
658 FETCH csr_s30_header_data INTO l_header_emp_title,
659 l_header_emp_number,
660 l_header_first_name,
661 l_header_last_name;
662 CLOSE csr_s30_header_data;
663 -- write the header lines for s30
664 fnd_file.new_line(fnd_file.output,2);
665 fnd_file.put_line(fnd_file.output,l_s30_heading_text);
666 fnd_file.put_line(fnd_file.output,l_emp_name_text||' : '||l_header_emp_title||' '||l_header_first_name||' '||l_header_last_name);
667 fnd_file.put_line(fnd_file.output,l_emp_number_text||' : '||l_header_emp_number);
668 fnd_file.new_line(fnd_file.output, 1);
669 --
670 -- fetch s30 data archived for this company and assignment action
671 FOR S30_data_rec IN csr_get_S30_data(l_asg_action_id,
672 comp_rec.company_id) LOOP
673 -- print only mandatory or non-null non-mandatory fields
674 IF S30_data_rec.usage ='M'
675 OR (S30_data_rec.usage <>'M' AND S30_data_rec.file_value IS NOT NULL)
676 THEN
677 -- Write S30 data to the report
678 write_into_file (p_report_type => p_report_type,
679 p_rubric_code => S30_data_rec.rubric_code,
680 p_rubric_desc => S30_data_rec.rubric_meaning,
681 p_file_value => S30_data_rec.file_value,
682 p_user_value => S30_data_rec.user_value);
683
684 --
685 END IF;
686 -- end of check for usage
687 END LOOP;
688 -- fetch the number of s41 records archived for each s30
689 FOR count_s41_rec IN csr_count_s41(l_asg_action_id,
690 comp_rec.company_id) LOOP
691 -- WRITE S41 DATA
692 -- print header for S41
693 -- get lookup values
694 l_s41_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_FISCAL_INFO');
695 l_emp_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_EMP_ESTAB');
696 l_start_date_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_START_DATE');
697 l_end_date_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_END_DATE');
698 -- fetch header values
699 OPEN csr_s41_header_data(l_asg_action_id,
700 comp_rec.company_id,
701 count_s41_rec.id2);
702 FETCH csr_s41_header_data INTO l_emp_header_estab_name,
703 l_header_start_period,
704 l_header_end_period;
705 CLOSE csr_s41_header_data;
706 -- Write the header lines
707 fnd_file.new_line(fnd_file.output,2);
708 fnd_file.put_line(fnd_file.output,l_s41_heading_text);
709 fnd_file.put_line(fnd_file.output,l_emp_name_text||' : '||l_header_emp_title||' '||l_header_first_name||' '||l_header_last_name);
710 fnd_file.put_line(fnd_file.output,l_emp_number_text||' : '||l_header_emp_number);
711 fnd_file.put_line(fnd_file.output,l_emp_estab_text||' : '||l_emp_header_estab_name);
712 fnd_file.put_line(fnd_file.output,l_start_date_text||' : '||l_header_start_period);
713 fnd_file.put_line(fnd_file.output,l_end_date_text||' : '||l_header_end_period);
714 fnd_file.new_line(fnd_file.output, 1);
715 --
716 -- fetch S41 records
717 FOR s41_data_rec IN csr_get_s41_data(l_asg_action_id,
718 comp_rec.company_id,
719 count_s41_rec.id2) LOOP
720 -- print only mandatory or non-null non-mandatory fields
721 IF S41_data_rec.usage ='M'
722 OR (S41_data_rec.usage <>'M' AND S41_data_rec.file_value IS NOT NULL)
723 THEN
724 -- Exclude rubrics selectively for decl code '02'
725 IF p_declaration_nature <>2 OR
726 (p_declaration_nature = '02' AND
727 substr(S41_data_rec.rubric_code,13,2)
728 NOT IN (29,30,32,33,35,37,42,44,49,52,66)) THEN
729 -- Write S41 data into report
730 write_into_file (p_report_type => p_report_type,
731 p_rubric_code => S41_data_rec.rubric_code,
732 p_rubric_desc => S41_data_rec.rubric_meaning,
733 p_file_value => S41_data_rec.file_value,
734 p_user_value => S41_data_rec.user_value);
735 --
736 END IF;
737 --
738 END IF;
739 -- End of check for usage
740 END LOOP;
741 -- END OF WRITING S41 DATA
742 -- end loop for number of s41 records
743 END LOOP;
744 -- END OF WRITING S30 DATA
745 -- end loop for assignment actions
746 END LOOP;
747 -- end of exclusion for '05' decl nature code
748 END IF;
749 -- END OF WRITING S20 DATA
750 -- end loop for companies archived
751 END LOOP;
752 --
753 -- Get number of INSEE establishments archived
754 FOR count_s80_rec IN csr_count_s80(l_payroll_action_id) LOOP
755 -- Check for report inclusions
756 IF P_REPORT_INCLUSIONS= 'ALL' THEN
757 -- WRITE S80 DATA
758 -- print header for S80
759 -- get lookup values
760 l_s80_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S80_ESTAB_INFO');
761 l_s80_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S80_ESTAB_NAME');
762 -- fetch header data
763 OPEN csr_s80_header_data(l_payroll_action_id,count_s80_rec.estab_id);
764 FETCH csr_s80_header_data INTO l_s80_header_estab_name;
765 CLOSE csr_s80_header_data;
766 -- write header lines for s80
767 fnd_file.new_line(fnd_file.output,2);
768 fnd_file.put_line(fnd_file.output,l_s80_heading_text);
769 fnd_file.put_line(fnd_file.output,l_s80_estab_text||' : '||l_s80_header_estab_name);
770 fnd_file.new_line(fnd_file.output, 1);
771 --
772 -- Get S80 data
773 FOR s80_data_rec IN csr_get_s80_data(l_payroll_action_id,
774 count_s80_rec.estab_id)LOOP
775 -- print only mandatory or non-null non-mandatory fields
776 IF S80_data_rec.usage ='M'
777 OR (S80_data_rec.usage <>'M' AND S80_data_rec.file_value IS NOT NULL)
778 THEN
779 -- write s80 data
780 write_into_file (p_report_type => p_report_type,
781 p_rubric_code => S80_data_rec.rubric_code,
782 p_rubric_desc => S80_data_rec.rubric_meaning,
783 p_file_value => S80_data_rec.file_value,
784 p_user_value => S80_data_rec.user_value);
785 --
786 END IF;
787 -- end of check for usage
788 END LOOP;
789 --
790 END IF;
791 -- END OF WRITING S80 DATA
792 END LOOP;
793 -- WRITE S90 DATA
794 -- print header for S90
795 l_s90_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S90_TOTALS');
796 fnd_file.new_line(fnd_file.output,2);
797 fnd_file.put_line(fnd_file.output,l_s90_heading_text);
798 fnd_file.new_line(fnd_file.output, 1);
799 --
800 -- Get s90 data
801 FOR s90_data_rec IN csr_get_s90_data(l_payroll_action_id) LOOP
802 IF p_declaration_nature = '02' THEN
803 -- Check for report inclusions
804 IF P_REPORT_INCLUSIONS= 'ALL' THEN
805 -- print rubric data selectively
806 IF S90_data_rec.rubric_code <> 'S90.G01.00.009' THEN
807 -- print only mandatory or non-null non-mandatory fields
808 IF S90_data_rec.usage ='M'
809 OR (S90_data_rec.usage <>'M' AND S90_data_rec.file_value IS NOT NULL)
810 THEN
811 -- Write S90 data into report
812 write_into_file (p_report_type => p_report_type,
813 p_rubric_code => S90_data_rec.rubric_code,
814 p_rubric_desc => S90_data_rec.rubric_meaning,
815 p_file_value => S90_data_rec.file_value,
816 p_user_value => S90_data_rec.user_value);
817 END IF;
818 -- end of check for usage
819 END IF;
820 --
821 END IF;
822 --
823 END IF;
824 --
825 END LOOP;
826 -- END OF WRITING S90 DATA
827 --
828 EXCEPTION
829 WHEN OTHERS THEN raise;
830 --
831 END write_user_file_report;
832 -------------------------------------------------------------------------------
833 --Exceptions Report Procedure
834 ------------------------------------------------------------------------------
835 Procedure control_proc (P_BUSINESS_GROUP_ID IN NUMBER,
836 P_ISSUING_ESTABLISHMENT IN NUMBER,
837 P_DADS_REFERENCE IN VARCHAR2,
838 P_REPORT_TYPE IN VARCHAR2,
839 P_DECLARATION_NATURE IN VARCHAR2,
840 P_DECLARATION_TYPE IN VARCHAR2,
841 P_REPORT_INCLUSIONS IN VARCHAR2,
842 P_SORT_ORDER_1 IN VARCHAR2,
843 P_SORT_ORDER_2 IN VARCHAR2)
844 IS
845 --
846 TYPE ref_cursor_type IS REF CURSOR;
847 ref_csr_asg_action ref_cursor_type;
848 l_asg_action_id number;
849 --
850 l_payroll_action_id number;
851 l_sort_rubric1 varchar2(30);
852 l_sort_rubric2 varchar2(30);
853 l_s30_select varchar2(1000);
854 l_s30_where varchar2(1000);
855 l_s30_cond_from varchar2(1000);
856 l_s30_cond_where varchar2(1000);
857 l_s30_order_by varchar2(100);
858 l_s30_cond_order_by varchar2(100);
859 --
860 l_header_issue_estab varchar2(50);
861 l_cre_estab_text hr_lookups.meaning%type;
862 l_s10_cre_estab varchar2(50);
863 l_s10_heading_text hr_lookups.meaning%type;
864 l_issuing_estab_text hr_lookups.meaning%type;
865 --
866 l_s20_heading_text hr_lookups.meaning%type;
867 l_comp_text hr_lookups.meaning%type;
868 l_hq_text hr_lookups.meaning%type;
869 l_fisc_text hr_lookups.meaning%type;
870 l_comp_name varchar2(100);
871 l_hq_name varchar2(100);
872 l_fisc_name varchar2(100);
873 l_s20_cre_estab varchar2(50);
874 --
875 l_s30_heading_text hr_lookups.meaning%type;
876 l_header_emp_number varchar2(50);
877 l_header_emp_title varchar2(50);
878 l_header_first_name varchar2(100);
879 l_header_last_name varchar2(100);
880 l_emp_name_text hr_lookups.meaning%type;
881 l_emp_number_text hr_lookups.meaning%type;
882 --
883 l_s41_heading_text hr_lookups.meaning%type;
884 l_emp_estab_text hr_lookups.meaning%type;
885 l_start_date_text hr_lookups.meaning%type;
886 l_end_date_text hr_lookups.meaning%type;
887 l_emp_header_estab_name varchar2(100);
888 l_header_start_period varchar2(50);
889 l_header_end_period varchar2(50);
890 --
891 l_s80_heading_text hr_lookups.meaning%type;
892 l_s80_header_estab_name varchar2(100);
893 l_s80_estab_text hr_lookups.meaning%type;
894 --
895 l_s90_heading_text hr_lookups.meaning%type;
896 --
897 -- To store the number of employees and companies in an establishment
898 l_total_employees number;
899 l_total_companies number;
900
901 l_value fnd_new_messages.message_text%type;
902 --
903 -- Cursor for fetching the payroll action
904 Cursor csr_payroll_action is
905 Select action_context_id
906 from pay_action_information
907 where action_context_type = 'PA'
908 and action_information_category = 'FR_DADS_FILE_DATA'
909 and action_information1 = 'S10.G01.00.004'
910 and action_information4 = p_dads_reference;
911 --
912 -- Cursor for fetching S10 values
913 Cursor csr_get_S10_rec(c_payroll_action_id number) IS
914 Select action_information1 rubric_code,
915 action_information2 error_warning,
916 action_information6 error_warning_message
917 from pay_action_information
918 where action_context_id = c_payroll_action_id
919 and action_context_type = 'PA'
920 and action_information_category = 'FR_DADS_FILE_DATA'
921 and action_information1 like 'S10%'
922 and action_information3 = p_issuing_establishment
923 and action_information6 is not null
924 order by action_information1;
925 --
926 -- Cursor for fetching the number of S20 records
927 -- sorted alphabetically by company name
928 Cursor csr_count_comp(c_payroll_action_id number) is
929 Select distinct action_information3 company_id,
930 action_information4 comp_name
931 from pay_action_information
932 where action_information1 = 'S20.G01.00.002'
933 and action_context_id = c_payroll_action_id
934 and action_information_category = 'FR_DADS_FILE_DATA'
935 and action_context_type = 'PA'
936 order by action_information4;
937 --
938 -- Cursor for fetching S20 records
939 Cursor csr_get_S20_data(c_payroll_action_id number,
940 c_company_id number)is
941 Select action_information1 rubric_code,
942 action_information2 error_warning,
943 action_information6 error_warning_message
944 from pay_action_information
945 where action_context_id = c_payroll_action_id
946 and action_context_type = 'PA'
947 and action_information_category = 'FR_DADS_FILE_DATA'
948 and action_information1 like 'S20%'
949 and action_information3 = c_company_id
950 and action_information6 is not null
951 order by action_information1;
952 --
953 -- Cursor for fetching S30 records
954 Cursor csr_get_s30_data(c_asg_action_id number,
955 c_company_id number)is
956 Select action_information1 rubric_code,
957 action_information2 error_warning,
958 action_information6 error_warning_message
959 from pay_action_information
960 where action_context_id = c_asg_action_id
961 and action_context_type = 'AAP'
962 and action_information_category = 'FR_DADS_FILE_DATA'
963 and action_information1 like 'S30%'
964 and action_information3 = c_company_id
965 and action_information6 is not null
966 order by action_information1;
967 --
968 -- Cursor for fetching number of S41 records
969 -- for each S30 record
970 Cursor csr_count_s41(c_asg_action_id number,
971 c_company_id number) is
972 Select distinct action_information8 ID2
973 from pay_action_information
974 where action_context_id = c_asg_action_id
975 and action_context_type = 'AAP'
976 and action_information_category = 'FR_DADS_FILE_DATA'
977 and action_information1 like 'S41%'
978 and action_information3 = c_company_id
979 order by action_information8;
980 --
981 -- Cursor for fetching s41 records
982 Cursor csr_get_s41_data(c_asg_action_id number,
983 c_company_id number,
984 c_s41_id2 number) is
985 Select action_information1 rubric_code,
986 action_information2 error_warning,
987 action_information6 error_warning_message
988 from pay_action_information
989 where action_context_id = c_asg_action_id
990 and action_context_type = 'AAP'
991 and action_information_category = 'FR_DADS_FILE_DATA'
992 and action_information1 like 'S41%'
993 and action_information3 = c_company_id
994 and action_information8 = c_s41_id2
995 and action_information6 is not null
996 order by action_information1;
997 -- Cursor to fetch number of INSEE establishments
998 Cursor csr_count_s80(c_payroll_action_id number) is
999 select distinct action_information3 estab_id
1000 from pay_action_information
1001 where action_context_id = c_payroll_action_id
1002 and action_context_type = 'PA'
1003 and action_information_category = 'FR_DADS_FILE_DATA'
1004 and action_information1 like 'S80%';
1005 --
1006 -- Cursor to get S80 data
1007 Cursor csr_get_s80_data(c_payroll_action_id number,
1008 c_estab_id number)is
1009 Select action_information1 rubric_code,
1010 action_information2 error_warning,
1011 action_information6 error_warning_message
1012 from pay_action_information
1013 where action_context_id = c_payroll_action_id
1014 and action_context_type = 'PA'
1015 and action_information_category = 'FR_DADS_FILE_DATA'
1016 and action_information1 like 'S80%'
1017 and action_information3 = c_estab_id
1018 and action_information6 is not null
1019 order by action_information1;
1020 --
1021 -- Cursor to get S90 data
1022 Cursor csr_get_s90_data(c_payroll_action_id number)is
1023 Select action_information1 rubric_code,
1024 action_information2 error_warning,
1025 action_information6 error_warning_message
1026 from pay_action_information
1027 where action_context_id = c_payroll_action_id
1028 and action_context_type = 'PA'
1029 and action_information_category = 'FR_DADS_FILE_DATA'
1030 and action_information1 like 'S90%'
1031 and action_information6 is not null
1032 order by action_information1;
1033 --
1034 -- Cursor for S10 header
1035 Cursor csr_s10_header_data(c_payroll_action_id number) is
1036 Select issue.action_information4 issue_estab,
1037 cre.action_information5 cre_estab_name
1038 from pay_action_information issue,
1039 pay_action_information cre
1040 where issue.action_context_id = c_payroll_action_id
1041 and issue.action_context_type = 'PA'
1042 and issue.action_information_category = 'FR_DADS_FILE_DATA'
1043 and issue.action_information1 = 'S10.G01.00.002'
1044 --
1045 and cre.action_context_id(+) = issue.action_context_id
1046 and cre.action_context_type(+) = 'PA'
1047 and cre.action_information_category(+) = 'FR_DADS_REPORT_DATA'
1048 and cre.action_information1(+) = 'S10'
1049 and cre.action_information3(+) = P_ISSUING_ESTABLISHMENT;
1050 --
1051 -- Cursor for S20 header
1052 Cursor csr_s20_header_data(c_payroll_action_id number,
1053 c_company_id number) is
1054 Select comp.action_information4 comp_name,
1055 nvl(cre_s20.action_information5,null) cre_name,
1056 nvl(hq.action_information7,null) hq_name,
1057 nvl(fisc.action_information4,null) fisc_name
1058 from pay_action_information comp,
1059 pay_action_information cre_s20,
1060 pay_action_information hq,
1061 pay_action_information fisc
1062 where comp.action_context_id = c_payroll_action_id
1063 and comp.action_context_type = 'PA'
1064 and comp.action_information_category = 'FR_DADS_FILE_DATA'
1065 and comp.action_information1 = 'S20.G01.00.002'
1066 and comp.action_information3 = c_company_id
1067 --
1068 and cre_s20.action_context_id(+) = comp.action_context_id
1069 and cre_s20.action_context_type(+) = 'PA'
1070 and cre_s20.action_information_category(+) = 'FR_DADS_REPORT_DATA'
1071 and cre_s20.action_information1 (+)= 'S20'
1072 and cre_s20.action_information3(+) = comp.action_information3
1073 --
1074 and hq.action_context_id(+) = comp.action_context_id
1075 and hq.action_context_type(+) = 'PA'
1076 and hq.action_information_category(+) = 'FR_DADS_FILE_DATA'
1077 and hq.action_information1 (+)= 'S20.G01.00.008'
1078 and hq.action_information3(+) = comp.action_information3
1079 --
1080 and fisc.action_context_id(+) = comp.action_context_id
1081 and fisc.action_context_type(+) = 'PA'
1082 and fisc.action_information_category(+) = 'FR_DADS_FILE_DATA'
1083 and fisc.action_information1(+)='S20.G01.00.011'
1084 and fisc.action_information3(+) = comp.action_information3;
1085 --
1086 -- Cursor for S30 header
1087 Cursor csr_s30_header_data(c_asg_action_id number,
1088 c_company_id number) is
1089 Select nvl(title.action_information5,null) emp_title,
1090 nvl(title.action_information8 ,null) emp_number,
1091 nvl(first_name.action_information4,null) emp_first,
1092 nvl(last_name.action_information4,null) emp_last
1093 from pay_action_information title,
1094 pay_action_information first_name,
1095 pay_action_information last_name
1096 where title.action_context_id = c_asg_action_id
1097 and title.action_context_type = 'AAP'
1098 and title.action_information_category = 'FR_DADS_FILE_DATA'
1099 and title.action_information1 = 'S30.G01.00.007'
1100 and title.action_information3 = c_company_id
1101 --
1102 and first_name.action_context_id = c_asg_action_id
1103 and first_name.action_context_type = 'AAP'
1104 and first_name.action_information_category = 'FR_DADS_FILE_DATA'
1105 and first_name.action_information1 = 'S30.G01.00.003'
1106 and first_name.action_information3 = c_company_id
1107 --
1108 and last_name.action_context_id = c_asg_action_id
1109 and last_name.action_context_type = 'AAP'
1110 and last_name.action_information_category = 'FR_DADS_FILE_DATA'
1111 and last_name.action_information1 = 'S30.G01.00.004'
1112 and last_name.action_information3 = c_company_id;
1113 --
1114 -- Cursor for S41 header
1115 cursor csr_s41_header_data(c_asg_action_id number,
1116 c_company_id number,
1117 c_s41_id2 number) is
1118 Select hou_tl_estab.name emp_estab,
1119 start_period.action_information4 period_start,
1120 end_period.action_information4 period_end
1121 from pay_action_information estab,
1122 pay_action_information start_period,
1123 pay_action_information end_period,
1124 hr_all_organization_units_tl hou_tl_estab
1125 where estab.action_context_id = c_asg_action_id
1126 and estab.action_context_type = 'AAP'
1127 and estab.action_information_category = 'FR_DADS_FILE_DATA'
1128 and estab.action_information1 = 'S41.G01.00.005'
1129 and estab.action_information3 = c_company_id
1130 and estab.action_information8 = c_s41_id2
1131 and hou_tl_estab.organization_id(+) = estab.action_information7
1132 and hou_tl_estab.language(+) = userenv('LANG')
1133 --
1134 and start_period.action_context_id = c_asg_action_id
1135 and start_period.action_context_type = 'AAP'
1136 and start_period.action_information_category = 'FR_DADS_FILE_DATA'
1137 and start_period.action_information1 = 'S41.G01.00.001'
1138 and start_period.action_information3 = c_company_id
1139 and start_period.action_information8 = c_s41_id2
1140 --
1141 and end_period.action_context_id = c_asg_action_id
1142 and end_period.action_context_type = 'AAP'
1143 and end_period.action_information_category = 'FR_DADS_FILE_DATA'
1144 and end_period.action_information1 = 'S41.G01.00.003'
1145 and end_period.action_information3 = c_company_id
1146 and end_period.action_information8 = c_s41_id2;
1147 --
1148 -- Cursor for S80 header
1149 Cursor csr_s80_header_data(c_payroll_action_id number,
1150 c_estab_id number)is
1151 Select action_information4 estab_name
1152 from pay_action_information
1153 where action_context_id = c_payroll_action_id
1154 and action_context_type = 'PA'
1155 and action_information_category = 'FR_DADS_FILE_DATA'
1156 and action_information1 = 'S80.G01.00.002'
1157 and action_information3 = c_estab_id;
1158 --
1159 -- Procedure for writing into files
1160 PROCEDURE write_into_file (p_rubric_code varchar2,
1161 p_error_warning varchar2,
1162 p_error_warning_message varchar2) is
1163 --
1164 BEGIN
1165 --
1166 hr_utility.set_location('Entering the procedure Write into file',1);
1167
1168 -- Print the error message
1169 fnd_file.put_line(fnd_file.output,p_error_warning||':'||p_rubric_code||':'||p_error_warning_message);
1170 -- insert a line after each rubric
1171 fnd_file.new_line(fnd_file.output, 1);
1172
1173 hr_utility.set_location('leaving write into file',3);
1174
1175 END write_into_file;
1176 --
1177
1178 -- Function for returning sort rubrics
1179 FUNCTION get_sort_rubrics(p_sort_order VARCHAR2) return varchar2
1180 IS
1181 BEGIN
1182 --
1183 IF p_sort_order = '31' THEN
1184 -- return rubric for zip code
1185 RETURN 'S30.G01.00.008.010';
1186 ELSIF p_sort_order = '32' THEN
1187 -- return rubric for name
1188 RETURN 'S30.G01.00.004';
1189 ELSIF p_sort_order = '33' THEN
1190 -- return rubric for ss number
1191 RETURN 'S30.G01.00.001';
1192 ELSIF p_sort_order = '34' THEN
1193 -- return rubric for ss number
1194 -- to pick up the id2 col for emp number
1195 RETURN 'S30.G01.00.001';
1196 END IF;
1197 return null;
1198 --
1199 END get_sort_rubrics;
1200 --
1201 BEGIN
1202 -- fetch the sort order rubrics
1203 --hr_utility.trace_on (null, 'AY_FOR_TRACE');
1204 hr_utility.set_location('Entered the procedure control_proc',1);
1205 l_sort_rubric1 := get_sort_rubrics(p_sort_order_1);
1206 -- As p_sort_order_2 is not a mandatory parameter
1207 IF p_sort_order_2 IS NOT NULL THEN
1208 l_sort_rubric2 := get_sort_rubrics(p_sort_order_2);
1209 END IF;
1210
1211 -- Retreive the error message for Declaration Type Code
1212 IF (P_DECLARATION_NATURE = '02' OR P_DECLARATION_NATURE = '01') AND
1213 P_DECLARATION_TYPE = '55' THEN
1214 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
1215 'VALUE1:'||hr_general.decode_lookup
1216 ('FR_DADS_RUBRICS','S20.G01.00.004.001'),
1217 'VALUE2:'||hr_general.decode_lookup
1218 ('FR_DADS_RUBRICS','S20.G01.00.004.002'), null);
1219 ELSIF P_DECLARATION_NATURE = '05' AND P_DECLARATION_TYPE <> '55' THEN
1220 l_value := pay_fr_general.get_payroll_message('PAY_75191_INCOMPAT_DATA',
1221 'VALUE1:'||hr_general.decode_lookup
1222 ('FR_DADS_RUBRICS','S20.G01.00.004.001'),
1223 'VALUE2:'||hr_general.decode_lookup
1224 ('FR_DADS_RUBRICS','S20.G01.00.004.002'), null);
1225 ELSE
1226 l_value := null;
1227 END IF;
1228
1229 -- fetch the payroll action
1230 hr_utility.set_location('Retreived Sort Orders ',2);
1231 OPEN csr_payroll_action;
1232 FETCH csr_payroll_action INTO l_payroll_action_id;
1233 CLOSE csr_payroll_action;
1234 -- Check for report inclusions
1235 IF P_REPORT_INCLUSIONS ='ORG' OR P_REPORT_INCLUSIONS='ALL' THEN
1236 hr_utility.set_location('Entering S10 ',3);
1237 -- WRITE S10 DATA
1238 -- print header for S10
1239 OPEN csr_s10_header_data(l_payroll_action_id);
1240 FETCH csr_s10_header_data INTO l_header_issue_estab, l_s10_cre_estab;
1241 CLOSE csr_s10_header_data;
1242 -- get lookup meanings
1243 l_s10_heading_text :=hr_general.decode_lookup('FR_DADS_HEADINGS','S10_ISSUE_ESTAB_INFO');
1244 l_issuing_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S10_ISSUE_ESTAB_NAME');
1245 l_cre_estab_text:= hr_general.decode_lookup('FR_DADS_HEADINGS','S10_CRE_ESTAB_NAME');
1246 --
1247 fnd_file.new_line(fnd_file.output,2);
1248 fnd_file.put_line(fnd_file.output,l_s10_heading_text);
1249 fnd_file.put_line(fnd_file.output,l_issuing_estab_text ||' : '||l_header_issue_estab);
1250 fnd_file.put_line(fnd_file.output,l_cre_estab_text||' : '||l_s10_cre_estab);
1251 fnd_file.new_line(fnd_file.output,1);
1252 hr_utility.set_location('Printing the header ',4);
1253 -- fetch the values for s10
1254 FOR get_s10_rec IN csr_get_S10_rec(l_payroll_action_id) LOOP
1255 -- write the values of S10
1256 write_into_file (get_s10_rec.rubric_code,
1257 get_s10_rec.error_warning,
1258 get_s10_rec.error_warning_message);
1259 --
1260 END LOOP;
1261 -- END OF WRITING S10 DATA
1262 --
1263 END IF; --end of check for report inclusions
1264 -- Initialize the total number of employees and total number of companies to zero
1265 l_total_employees := 0;
1266 l_total_companies := 0;
1267 -- fetch the number of companies archived for the issuing estab
1268 FOR comp_rec IN csr_count_comp(l_payroll_action_id) LOOP
1269 --#3300005 Incrementing the number of companies by 1
1270 l_total_companies := l_total_companies + 1;
1271 -- Check for report inclusions
1272 IF P_REPORT_INCLUSIONS ='ORG' OR P_REPORT_INCLUSIONS='ALL' THEN
1273 hr_utility.set_location('Entered into the company procedure ',5);
1274 -- WRITE S20 DATA
1275 -- print header for S20
1276 -- get lookup meanings
1277 l_s20_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_COMP_INFO');
1278 l_comp_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_COMP_NAME');
1279 l_hq_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_HQ_ESTAB_NAME');
1280 l_fisc_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S20_FISC_ESTAB_NAME');
1281 l_cre_estab_text:= hr_general.decode_lookup('FR_DADS_HEADINGS','S10_CRE_ESTAB_NAME');
1282 -- get cursor values
1283 OPEN csr_s20_header_data(l_payroll_action_id,comp_rec.company_id);
1284 FETCH csr_s20_header_data INTO l_comp_name, l_s20_cre_estab, l_hq_name, l_fisc_name;
1285 CLOSE csr_s20_header_data;
1286 hr_utility.set_location('Printing the S20 header values',6);
1287 -- write the header into the file
1288 fnd_file.new_line(fnd_file.output,2);
1289 fnd_file.put_line(fnd_file.output,l_s20_heading_text);
1290 fnd_file.put_line(fnd_file.output,l_comp_text||' : '||l_comp_name);
1291 fnd_file.put_line(fnd_file.output,l_hq_text||' : '||l_hq_name);
1292 fnd_file.put_line(fnd_file.output,l_fisc_text||' : '||l_fisc_name);
1293 fnd_file.put_line(fnd_file.output,l_cre_estab_text||' : '||l_s20_cre_estab);
1294 fnd_file.new_line(fnd_file.output,1);
1295 --
1296 hr_utility.set_location('Printing the S20 error values',7);
1297 -- Fetch S20 data
1298 FOR S20_data_rec IN csr_get_S20_data(l_payroll_action_id,
1299 comp_rec.company_id) LOOP
1300 IF S20_data_rec.rubric_code = 'S20.G01.00.004.002' and l_value is not null THEN
1301 write_into_file (S20_data_rec.rubric_code,
1302 S20_data_rec.error_warning,
1303 l_value);
1304 ELSIF S20_data_rec.rubric_code = 'S20.G01.00.006' THEN
1305 -- check the declaration type
1306 IF p_declaration_type = 53 THEN -- if the type is 'correction'
1307 -- Write the calendar year to which salaries are attached
1308 write_into_file (S20_data_rec.rubric_code,
1309 S20_data_rec.error_warning,
1310 S20_data_rec.error_warning_message);
1311 --
1312 END IF;
1313 -- The Declaration Type Code must not be printed in the Exceptions Report
1314 ELSIF S20_data_rec.rubric_code <> 'S20.G01.00.004.002' THEN
1315 -- Write S20 data to the report
1316 write_into_file (S20_data_rec.rubric_code,
1317 S20_data_rec.error_warning,
1318 S20_data_rec.error_warning_message);
1319 --
1320 END IF;
1321 END LOOP;
1322 END IF;
1323 -- Exclude S30 and S41 if declaration nature code is '05'
1324 IF p_declaration_nature <> '05'
1325 -- check for report inclusions
1326 AND (P_REPORT_INCLUSIONS= 'EMP' OR P_REPORT_INCLUSIONS= 'ALL') THEN
1327 -- build up the query conditionally
1328 l_s30_select := 'Select pasac.assignment_action_id
1329 from pay_assignment_actions pasac,
1330 pay_action_information pacinfo_1';
1331 l_s30_where :=' where pasac.payroll_action_id = '||l_payroll_action_id||'
1332 and pacinfo_1.action_context_id = pasac.assignment_action_id
1333 and pacinfo_1.action_context_type =''AAP''
1334 and pacinfo_1.action_information1 = '|| '''' ||l_sort_rubric1|| ''''||'
1335 and pacinfo_1.action_information3 = '||comp_rec.company_id||'';
1336 -- As p_sort_order_2 is not a mandatory parameter
1337 IF p_sort_order_2 IS NOT NULL THEN
1338 l_s30_cond_from := ' ,pay_action_information pacinfo_2';
1339 l_s30_cond_where := 'and pacinfo_2.action_context_id = pasac.assignment_action_id
1340 and pacinfo_2.action_context_type =''AAP''
1341 and pacinfo_2.action_information1 = '|| '''' ||l_sort_rubric2|| ''''||'
1342 and pacinfo_2.action_information3 = '||comp_rec.company_id||'';
1343 IF p_sort_order_2 = '34' THEN
1344 l_s30_cond_order_by := ',pacinfo_2.action_information8';
1345 ELSE
1346 l_s30_cond_order_by := ',pacinfo_2.action_information4';
1347 END IF;
1348 ELSE
1349 l_s30_cond_from := '';
1350 l_s30_cond_where := '';
1351 l_s30_cond_order_by := '';
1352 END IF;
1353 IF p_sort_order_1 = '34' THEN
1354 l_s30_order_by := ' order by pacinfo_1.action_information8';
1355 ELSE
1356 l_s30_order_by := ' order by pacinfo_1.action_information4';
1357 END IF;
1358 -- fetch assignment action ids for this payroll action ids and loop
1359 OPEN ref_csr_asg_action FOR l_s30_select||l_s30_cond_from||l_s30_where||l_s30_cond_where||l_s30_order_by||l_s30_cond_order_by;
1360 LOOP
1361 FETCH ref_csr_asg_action INTO l_asg_action_id;
1362 EXIT WHEN ref_csr_asg_action%NOTFOUND;
1363 -- Increment the number of employee by one
1364 l_total_employees := l_total_employees + 1;
1365 -- WRITE S30 DATA
1366 -- print header for S30
1367 -- get the lookup values
1368 l_s30_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_INFO');
1369 l_emp_name_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_NAME');
1370 l_emp_number_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S30_EMP_NUMBER');
1371 -- fetch the header values
1372 OPEN csr_s30_header_data(l_asg_action_id,
1373 comp_rec.company_id);
1374 FETCH csr_s30_header_data INTO l_header_emp_title,
1375 l_header_emp_number,
1376 l_header_first_name,
1377 l_header_last_name;
1378 CLOSE csr_s30_header_data;
1379 -- write the header lines for s30
1380 fnd_file.new_line(fnd_file.output,2);
1381 fnd_file.put_line(fnd_file.output,l_s30_heading_text);
1382 fnd_file.put_line(fnd_file.output,l_emp_name_text||' : '||l_header_emp_title||' '||l_header_first_name||' '||l_header_last_name);
1383 fnd_file.put_line(fnd_file.output,l_emp_number_text||' : '||l_header_emp_number);
1384 fnd_file.new_line(fnd_file.output,1);
1385 --
1386 -- fetch s30 data archived for this company and assignment action
1387 FOR S30_data_rec IN csr_get_S30_data(l_asg_action_id,
1388 comp_rec.company_id) LOOP
1389 -- Write S30 data to the report
1390 write_into_file (S30_data_rec.rubric_code,
1391 S30_data_rec.error_warning,
1392 S30_data_rec.error_warning_message);
1393 --
1394 END LOOP;
1395 -- fetch the number of s41 records archived for each s30
1396 FOR count_s41_rec IN csr_count_s41(l_asg_action_id,
1397 comp_rec.company_id) LOOP
1398 -- WRITE S41 DATA
1399 -- print header for S41
1400 -- get lookup values
1401 l_s41_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_FISCAL_INFO');
1402 l_emp_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_EMP_ESTAB');
1403 l_start_date_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_START_DATE');
1404 l_end_date_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S41_END_DATE');
1405 -- fetch header values
1406 OPEN csr_s41_header_data(l_asg_action_id,
1407 comp_rec.company_id,
1408 count_s41_rec.id2);
1409 FETCH csr_s41_header_data INTO l_emp_header_estab_name,
1410 l_header_start_period,
1411 l_header_end_period;
1412 CLOSE csr_s41_header_data;
1413 -- Write the header lines
1414 fnd_file.new_line(fnd_file.output,2);
1415 fnd_file.put_line(fnd_file.output,l_s41_heading_text);
1416 fnd_file.put_line(fnd_file.output,l_emp_name_text||' : '||l_header_emp_title||' '||l_header_first_name||' '||l_header_last_name);
1417 fnd_file.put_line(fnd_file.output,l_emp_number_text||' : '||l_header_emp_number);
1418 fnd_file.put_line(fnd_file.output,l_emp_estab_text||' : '||l_emp_header_estab_name);
1419 fnd_file.put_line(fnd_file.output,l_start_date_text||' : '||l_header_start_period);
1420 fnd_file.put_line(fnd_file.output,l_end_date_text||' : '||l_header_end_period);
1421 fnd_file.new_line(fnd_file.output, 1);
1422 --
1423 -- fetch S41 records
1424 FOR s41_data_rec IN csr_get_s41_data(l_asg_action_id,
1425 comp_rec.company_id,
1426 count_s41_rec.id2) LOOP
1427 -- Exclude rubrics selectively for decl code '02'
1428 -- Exclude rubrics selectively for decl code '02'
1429 IF p_declaration_nature <>2 OR
1430 (p_declaration_nature = '02' AND
1431 substr(S41_data_rec.rubric_code,13,2)
1432 NOT IN (29,30,32,33,35,37,42,44,49,52,66)) THEN
1433 -- Write S41 data into report
1434 write_into_file (S41_data_rec.rubric_code,
1435 S41_data_rec.error_warning,
1436 S41_data_rec.error_warning_message);
1437 --
1438 END IF;
1439 --
1440 END LOOP;
1441 -- END OF WRITING S41 DATA
1442 -- end loop for number of s41 records
1443 END LOOP;
1444 -- END OF WRITING S30 DATA
1445 -- end loop for assignment actions
1446 END LOOP;
1447 -- #3300005 Printing the warning message, when there are no employees for the given company
1448 IF l_total_employees = 0 THEN
1449 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_fr_general.get_payroll_message('PAY_75195_DADS', 'VALUE1:'||l_comp_name));
1450 ELSE
1451 l_total_employees := 0;
1452 END IF;
1453 -- end of exclusion for '05' decl nature code
1454 END IF;
1455 -- END OF WRITING S20 DATA
1456 -- end loop for companies archived
1457 END LOOP;
1458 --
1459 -- #3300005 Printing the warning message when there are no companies in the establishment
1460 IF l_total_companies = 0 THEN
1461 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, pay_fr_general.get_payroll_message('PAY_75198_DADS_NO_COMPANY','VALUE1:'||l_header_issue_estab));
1462 END IF;
1463 -- Get number of INSEE establishments archived
1464 FOR count_s80_rec IN csr_count_s80(l_payroll_action_id) LOOP
1465 -- Check for report inclusions
1466 IF P_REPORT_INCLUSIONS= 'ALL' THEN
1467 -- WRITE S80 DATA
1468 -- print header for S80
1469 -- get lookup values
1470 l_s80_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S80_ESTAB_INFO');
1471 l_s80_estab_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S80_ESTAB_NAME');
1472 -- fetch header data
1473 OPEN csr_s80_header_data(l_payroll_action_id,count_s80_rec.estab_id);
1474 FETCH csr_s80_header_data INTO l_s80_header_estab_name;
1475 CLOSE csr_s80_header_data;
1476 -- write header lines for s80
1477 fnd_file.new_line(fnd_file.output,2);
1478 fnd_file.put_line(fnd_file.output,l_s80_heading_text);
1479 fnd_file.put_line(fnd_file.output,l_s80_estab_text||' : '||l_s80_header_estab_name);
1480 fnd_file.new_line(fnd_file.output, 1);
1481 --
1482 -- Get S80 data
1483 FOR s80_data_rec IN csr_get_s80_data(l_payroll_action_id,
1484 count_s80_rec.estab_id)LOOP
1485 -- write s80 data
1486 write_into_file (S80_data_rec.rubric_code,
1487 S80_data_rec.error_warning,
1488 S80_data_rec.error_warning_message);
1489 --
1490 END LOOP;
1491 --
1492 END IF;
1493 -- END OF WRITING S80 DATA
1494 END LOOP;
1495 -- WRITE S90 DATA
1496 -- print header for S90
1497 l_s90_heading_text := hr_general.decode_lookup('FR_DADS_HEADINGS','S90_TOTALS');
1498 fnd_file.new_line(fnd_file.output,2);
1499 fnd_file.put_line(fnd_file.output,l_s90_heading_text);
1500 fnd_file.new_line(fnd_file.output,1);
1501 --
1502 -- Get s90 data
1503 FOR s90_data_rec IN csr_get_s90_data(l_payroll_action_id) LOOP
1504 IF p_declaration_nature = '02' THEN
1505 -- Check for report inclusions
1506 IF P_REPORT_INCLUSIONS= 'ALL' THEN
1507 -- print rubric data selectively
1508 IF S90_data_rec.rubric_code <> 'S90.G01.00.009' THEN
1509 -- Write S90 data into report
1510 write_into_file (S90_data_rec.rubric_code,
1511 S90_data_rec.error_warning,
1512 S90_data_rec.error_warning_message);
1513 END IF;
1514 --
1515 END IF;
1516 --
1517 END IF;
1518 --
1519 END LOOP;
1520 -- END OF WRITING S90 DATA
1521 --
1522 EXCEPTION
1523 WHEN OTHERS THEN raise;
1524 --
1525 END control_proc;
1526 --
1527 end pay_fr_dads_f_pkg;