[Home] [Help]
PACKAGE BODY: APPS.PAY_FR_DUCS
Source
1 package body PAY_FR_DUCS as
2 /* $Header: pyfraduc.pkb 120.1 2006/01/27 04:37:54 aparkes noship $ */
3 --
4 -- Globals
5 --
6 type g_org_info_tabtype is table of
7 hr_organization_information.org_information1%TYPE
8 index by binary_integer;
9
10 type g_estab_pens_prov_rectype is record (
11 estab_id hr_organization_information.organization_id%TYPE,
12 pens_provs g_org_info_tabtype);
13
14 g_estab_pens_provs g_estab_pens_prov_rectype;
15
16 g_package constant varchar2(30):= 'pay_fr_ducs';
17
18 g_business_group_id per_business_Groups.business_group_id%TYPE;
19 g_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
20
21 g_company_id hr_all_organization_units.organization_id%TYPE;
22 g_period_type varchar2(60);
23 g_period_start_date date;
24 g_effective_date date;
25 g_english_base varchar2(20) := 'Base';
26 g_english_rate varchar2(20) := 'Rate';
27 g_english_pay_value varchar2(20) := 'Pay Value';
28 g_english_contrib_code varchar2(20) := 'Contribution_Code';
29 g_french_base fnd_lookup_values.meaning%TYPE;
30 g_french_rate fnd_lookup_values.meaning%TYPE;
31 g_french_pay_value fnd_lookup_values.meaning%TYPE;
32 g_french_contrib_code fnd_lookup_values.meaning%TYPE;
33 g_range_person_enh_enabled boolean;
34 --
35 -------------------------------------------------------------------------------
36 -- GET_PARAMETER used in sql to decode legislative parameters
37 -------------------------------------------------------------------------------
38 FUNCTION get_parameter(
39 p_parameter_string in varchar2
40 ,p_token in varchar2
41 ,p_segment_number in number default null ) RETURN varchar2
42 IS
43 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
44 l_start_pos NUMBER;
45 l_delimiter varchar2(1):=' ';
46 l_proc VARCHAR2(60):= g_package||' get parameter ';
47 BEGIN
48 hr_utility.set_location('Entering ' || l_proc, 20);
49 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
50 IF l_start_pos = 0 THEN
51 l_delimiter := '|';
52 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
53 end if;
54 IF l_start_pos <> 0 THEN
55 l_start_pos := l_start_pos + length(p_token||'=');
56 l_parameter := substr(p_parameter_string,
57 l_start_pos,
58 instr(p_parameter_string||' ',
59 l_delimiter,l_start_pos)
60 - l_start_pos);
61 IF p_segment_number IS NOT NULL THEN
62 l_parameter := ':'||l_parameter||':';
63 l_parameter := substr(l_parameter,
64 instr(l_parameter,':',1,p_segment_number)+1,
65 instr(l_parameter,':',1,p_segment_number+1) -1
66 - instr(l_parameter,':',1,p_segment_number));
67 END IF;
68 END IF;
69 hr_utility.set_location('Leaving ' || l_proc, 100);
70 RETURN l_parameter;
71
72 END get_parameter;
73
74 -------------------------------------------------------------------------------
75 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
76 -------------------------------------------------------------------------------
77 PROCEDURE get_all_parameters (p_payroll_action_id in number
78 ,p_business_group_id out nocopy number
79 ,p_company_id out nocopy number
80 ,p_period_type out nocopy varchar2
81 ,p_period_start_date out nocopy date
82 ,p_effective_date out nocopy date
83 ,p_english_base out nocopy varchar2
84 ,p_english_rate out nocopy varchar2
85 ,p_english_pay_value out nocopy varchar2
86 ,p_english_contrib_code out nocopy varchar2
87 ,p_french_base out nocopy varchar2
88 ,p_french_rate out nocopy varchar2
89 ,p_french_pay_value out nocopy varchar2
90 ,p_french_contrib_code out nocopy varchar2) IS
91 --
92 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
93 SELECT fnd_number.canonical_to_number(
94 pay_fr_ducs.get_parameter(legislative_parameters, 'COMPANY_ID'))
95 ,pay_fr_ducs.get_parameter(legislative_parameters, 'PERIOD_TYPE')
96 ,effective_date
97 ,business_group_id
98 FROM pay_payroll_actions
99 WHERE payroll_action_id = p_payroll_action_id;
100 --
101 l_proc VARCHAR2(60):= g_package||' get_all_parameters ';
102
103 BEGIN
104
105 hr_utility.set_location('Entering ' || l_proc, 20);
106
107 OPEN csr_parameter_info (p_payroll_action_id);
108 FETCH csr_parameter_info INTO p_company_id,
109 p_period_type,
110 p_effective_date,
111 p_business_group_id;
112 CLOSE csr_parameter_info;
113
114 p_period_start_date := trunc(p_effective_date,
115 translate(p_period_type,'C','M'));
116 --
117 p_english_base := 'Base';
118 p_english_rate := 'Rate';
119 p_english_pay_value := 'Pay Value';
120 p_english_contrib_code := 'Contribution_Code';
121 --
122 p_french_base := hr_general.decode_lookup('NAME_TRANSLATIONS','BASE');
123 p_french_rate := hr_general.decode_lookup('NAME_TRANSLATIONS','RATE');
124 p_french_pay_value :=
125 hr_general.decode_lookup('NAME_TRANSLATIONS','PAY VALUE');
126 p_french_contrib_code :=
127 hr_general.decode_lookup('NAME_TRANSLATIONS','CONTRIBUTION CODE');
128
129
130 hr_utility.set_location('Leaving ' || l_proc, 100);
131
132 END get_all_parameters;
133 --
134
135 /*--------------------------------------------------------------------------
136 Name : range_code
137 Purpose : This returns the select statement that is used to created the
138 range rows.
139 ------------------------------------------------------------------------*/
140
141 PROCEDURE range_code(p_payroll_action_id in number
142 ,sqlstr out nocopy varchar2) IS
143
144 -- Local Variable
145
146 l_proc VARCHAR2(60) := g_package||' range_cursor ';
147
148 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
149 duplicate EXCEPTION;
150
151 l_year varchar2(10);
152 l_quarter varchar2(10);
153 l_month varchar2(10);
154 l_mm varchar2(12);
155 l_miq varchar2(12);
156 l_period_code varchar2(30);
157 --
158 -- Cursor
159 --
160
161 CURSOR c_existing_archive (p_company_id_chr in varchar2) is
162 SELECT payact.payroll_action_id
163 FROM pay_payroll_actions payact
164 ,pay_action_information ref_actinfo
165 WHERE payact.payroll_action_id = ref_actinfo.action_context_id
166 and ref_actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
167 and ref_actinfo.action_context_type = 'PA'
168 and ref_actinfo.action_information1 = p_company_id_chr
169 and ref_actinfo.action_information2 = l_period_code
170 and payact.business_group_id = g_business_group_id
171 and payact.payroll_action_id <> p_payroll_action_id;
172
173 BEGIN
174
175
176
177 hr_utility.set_location('Entering ' || l_proc,10);
178
179 --
180 -- Load the parameters to the process
181 --
182
183 pay_fr_ducs.get_all_parameters
184 (p_payroll_action_id => p_payroll_action_id
185 ,p_business_group_id => g_business_group_id
186 ,p_company_id => g_company_id
187 ,p_period_type => g_period_type
188 ,p_period_start_date => g_period_start_date
189 ,p_effective_date => g_effective_date
190 ,p_english_base => g_english_base
191 ,p_english_rate => g_english_rate
192 ,p_english_pay_value => g_english_pay_value
193 ,p_english_contrib_code => g_english_contrib_code
194 ,p_french_base => g_french_base
195 ,p_french_rate => g_french_rate
196 ,p_french_pay_value => g_french_pay_value
197 ,p_french_contrib_code => g_french_contrib_code);
198
199 g_payroll_action_id:=p_payroll_action_id;
200
201
202
203 l_year := to_char(g_effective_date,'YYYY');
204 l_quarter := to_char(g_effective_date,'Q');
205 l_month := replace(to_char(g_effective_date,'MONTH'),' ','');
206 l_mm := to_char(g_effective_date,'MM');
207 l_miq := to_char(to_number(l_mm)-(to_number(l_quarter)*3-2)+1);
208
209 IF g_period_type = 'CM' THEN
210 l_period_code := substr(l_year,3,2)||l_quarter||l_miq;
211 ELSE
212 l_period_code := substr(l_year,3,2)||l_quarter||'0';
213 END IF;
214
215 OPEN c_existing_archive(fnd_number.number_to_canonical(g_company_id));
216 FETCH c_existing_archive INTO l_payroll_action_id;
217 IF c_existing_archive%found THEN
218 CLOSE c_existing_archive;
219 RAISE duplicate;
220 END IF;
221 CLOSE c_existing_archive;
222
223
224 hr_utility.set_location('Step ' || l_proc, 30);
225
226 sqlstr := 'SELECT DISTINCT person_id
227 FROM per_people_f ppf
228 ,pay_payroll_actions ppa
229 WHERE ppa.payroll_action_id = :payroll_action_id
230 AND ppa.business_group_id = ppf.business_group_id
231 ORDER BY ppf.person_id';
232
233 hr_utility.set_location('Leaving ' || l_proc,100);
234 EXCEPTION
235
236 WHEN duplicate THEN
237 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,100);
238
239 hr_utility.set_message(801, 'PAY_75086_DUCS_DUPLICATE_ARCH');
240 FND_FILE.PUT_LINE(fnd_file.log,substr(hr_utility.get_message,1,240));
241 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
242
243
244 WHEN OTHERS THEN
245 hr_utility.set_location(' Leaving with EXCEPTION: '||l_proc,100);
246 -- Return cursor that selects no rows
247 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
248 hr_utility.set_location('Quitting ' || l_proc,10);
249
250 END range_code;
251
252 /*--------------------------------------------------------------------------
253 Name : assignment_action_code
254 Purpose : This creates the assignment actions for a specific chunk.
255 Arguments :
256 Notes :
257 --------------------------------------------------------------------------*/
258 PROCEDURE assignment_action_code (p_payroll_action_id in number
259 ,p_start_person_id in number
260 ,p_end_person_id in number
261 ,p_chunk in number) IS
262
263 -- Local Variable
264
265 l_proc VARCHAR2(60):=g_package||'.assignment_action_code ';
266
267 l_actid pay_assignment_actions.assignment_action_id%TYPE;
268
269 --
270 -- Do not process child assignment actions here these will be
271 -- explicitly created in ARCHIVE_CODE
272 --
273 --
274 -- Cursor to retrieve assignment actions
275 -- during the processing period given the person_id range
276 -- including the set of archive records created in earlier archives
277 -- but that will be summed for the reporting period.
278 -- Company Id is a mandatory param for DUCS so filter on leg params
279 -- before joining to establishment info
280 CURSOR csr_assact_by_range(p_company_id_chr in varchar2) is
281 SELECT /*+ ORDERED */ assact.assignment_id
282 , assact.assignment_action_id
283 , assact.tax_unit_id establishment_id
284 , payact.action_type
285 FROM pay_assignment_actions assact
286 , pay_payroll_actions payact
287 , hr_organization_information cmp_check
288 WHERE assact.source_action_id is null
289 AND assact.action_status = 'C'
290 AND assact.payroll_action_id = payact.payroll_action_id
291 AND payact.effective_date between g_period_start_date
292 and g_effective_date
293 AND assact.assignment_id in
294 (select assignment_id
295 from per_all_assignments_f asg
296 where asg.business_group_id+0 = g_business_group_id
297 and asg.person_id between p_start_person_id and p_end_person_id
298 and asg.effective_end_date >= g_period_start_date
299 and asg.effective_start_date <= g_effective_date
300 and asg.period_of_service_id is not null)
301 AND (payact.action_type in ('Q','R','B','I') or
302 (payact.action_type = 'X'
303 and payact.report_type = 'DUCS_ARCHIVE'
304 and payact.report_qualifier = 'FR'
305 and payact.report_category = 'DUCS_ARCHIVE'
306 AND legislative_parameters like '%COMPANY_ID='||
307 p_company_id_chr ||' %'))
308 /* Bug 2309322 Run assg_actions restricted by company */
309 AND assact.tax_unit_id = cmp_check.organization_id
310 AND cmp_check.org_information_context = 'FR_ESTAB_INFO'
311 AND cmp_check.org_information1 = p_company_id_chr;
312 --
313 --
314 -- Cursor to retrieve assignment actions
315 -- during the processing period given the chunk_number,
316 -- including the set of archive records created in earlier archives
317 -- but that will be summed for the reporting period.
318 -- Company Id is a mandatory param for DUCS so filter on leg params
319 -- before joining to establishment info
320 --
321 CURSOR csr_assact_by_chunk(p_company_id_chr in varchar2) is
322 SELECT /*+ ORDERED */ assact.assignment_id
323 , assact.assignment_action_id
324 , assact.tax_unit_id establishment_id
325 , payact.action_type
326 FROM pay_population_ranges pop
327 , per_periods_of_service pos
328 , per_all_assignments_f asg
329 , pay_assignment_actions assact
330 , pay_payroll_actions payact
331 , hr_organization_information cmp_check
332 WHERE pop.payroll_action_id = p_payroll_action_id
333 and pop.chunk_number = p_chunk
334 and asg.business_group_id+0 = g_business_group_id
335 and asg.effective_end_date >= g_period_start_date
336 and asg.effective_start_date <= g_effective_date
337 and asg.period_of_service_id = pos.period_of_service_id
338 and pos.person_id = pop.person_id
339 and assact.source_action_id is null
340 and assact.action_status = 'C'
341 AND assact.payroll_action_id = payact.payroll_action_id
342 AND payact.effective_date between g_period_start_date
343 and g_effective_date
344 AND (asg.effective_start_date,assact.assignment_id) =
345 (select max(asg2.effective_start_date), asg2.assignment_id
346 from per_all_assignments_f asg2
347 where asg.assignment_id = asg2.assignment_id
348 and asg2.effective_end_date >= g_period_start_date
349 and asg2.effective_start_date <= g_effective_date
350 group by asg2.assignment_id)
351 AND (payact.action_type in ('Q','R','B','I') or
352 (payact.action_type = 'X'
353 and payact.report_type = 'DUCS_ARCHIVE'
354 and payact.report_qualifier = 'FR'
355 and payact.report_category = 'DUCS_ARCHIVE'
356 AND legislative_parameters like '%COMPANY_ID='||
357 p_company_id_chr ||' %'))
358 /* Bug 2309322 Run assg_actions restricted by company */
359 AND assact.tax_unit_id = cmp_check.organization_id
360 AND cmp_check.org_information_context = 'FR_ESTAB_INFO'
361 AND cmp_check.org_information1 = p_company_id_chr;
362 --
363 CURSOR csr_locking_archive(p_run_act_id number) is
364 SELECT /*+ ORDERED */ 1
365 FROM pay_action_interlocks plock
366 , pay_assignment_actions assact
367 , pay_action_information actinfo
368 WHERE plock.locked_action_id = p_run_act_id
369 AND plock.locking_action_id = assact.assignment_action_id
370 AND assact.payroll_action_id = actinfo.action_context_id
371 AND actinfo.action_context_type = 'PA'
372 AND actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO';
373 --
374 rec_assact csr_assact_by_range%ROWTYPE;
375 l_num number(1);
376 l_create_act boolean;
377 --
378 BEGIN -- assignment_action_code
379 hr_utility.set_location('Entering ' || l_proc,10);
380
381 if g_payroll_action_id is null
382 or g_payroll_action_id <> p_payroll_action_id
383 then
384 pay_fr_ducs.get_all_parameters
385 (p_payroll_action_id => p_payroll_action_id
386 ,p_business_group_id => g_business_group_id
387 ,p_company_id => g_company_id
388 ,p_period_type => g_period_type
389 ,p_period_start_date => g_period_start_date
390 ,p_effective_date => g_effective_date
391 ,p_english_base => g_english_base
392 ,p_english_rate => g_english_rate
393 ,p_english_pay_value => g_english_pay_value
394 ,p_english_contrib_code => g_english_contrib_code
395 ,p_french_base => g_french_base
396 ,p_french_rate => g_french_rate
397 ,p_french_pay_value => g_french_pay_value
398 ,p_french_contrib_code => g_french_contrib_code);
399 g_payroll_action_id := p_payroll_action_id;
400 g_range_person_enh_enabled := null;
401 end if;
402
403 if g_range_person_enh_enabled is null then
404 g_range_person_enh_enabled :=
405 pay_fr_arc_utl.range_person_enh_enabled(p_payroll_action_id);
406 end if;
407 if g_range_person_enh_enabled then
408 open csr_assact_by_chunk(to_number(g_company_id));
409 else
410 open csr_assact_by_range(to_number(g_company_id));
411 end if;
412 LOOP
413 if csr_assact_by_chunk%ISOPEN then
414 fetch csr_assact_by_chunk into rec_assact;
415 if csr_assact_by_chunk%NOTFOUND then
416 close csr_assact_by_chunk;
417 exit;
418 end if;
419 elsif csr_assact_by_range%ISOPEN then
420 fetch csr_assact_by_range into rec_assact;
421 if csr_assact_by_range%NOTFOUND then
422 close csr_assact_by_range;
423 exit;
424 end if;
425 end if;
426 --
427 if rec_assact.action_type = 'X' then
428 l_create_act := TRUE;
429 else
430 open csr_locking_archive(rec_assact.assignment_action_id);
431 fetch csr_locking_archive into l_num;
432 l_create_act := csr_locking_archive%NOTFOUND;
433 close csr_locking_archive;
434 end if;
435 if l_create_act then
436 -- insert the new assignment action
437 SELECT pay_assignment_actions_s.nextval
438 INTO l_actid
439 FROM dual;
440 hr_nonrun_asact.insact( l_actid
441 , rec_assact.assignment_id
442 , p_payroll_action_id
443 , p_chunk
444 , rec_assact.establishment_id);
445 -- insert the lock on the run/arch action.
446 hr_nonrun_asact.insint(l_actid
447 , rec_assact.assignment_action_id);
448 end if; -- l_create_act
449 END LOOP;
450
451 hr_utility.set_location('Leaving ' || l_proc,100);
452
453 END assignment_action_code; --End of Assignment Action Creation
454
455 /*--------------------------------------------------------------------------
456 Name : archinit
457 Purpose : This sets up the session-static globals used in archive_code
458 Arguments :
459 Notes :
460 --------------------------------------------------------------------------*/
461 PROCEDURE archinit(p_payroll_action_id IN NUMBER) IS
462 l_proc VARCHAR2(60):= g_package||'.archinit';
463 BEGIN
464 hr_utility.set_location('Entering: ' || l_proc,10);
465 if g_payroll_action_id is null
466 or g_payroll_action_id <> p_payroll_action_id
467 then
468 hr_utility.set_location(l_proc,20);
469 pay_fr_ducs.get_all_parameters
470 (p_payroll_action_id => p_payroll_action_id
471 ,p_business_group_id => g_business_group_id
472 ,p_company_id => g_company_id
473 ,p_period_type => g_period_type
474 ,p_period_start_date => g_period_start_date
475 ,p_effective_date => g_effective_date
476 ,p_english_base => g_english_base
477 ,p_english_rate => g_english_rate
478 ,p_english_pay_value => g_english_pay_value
479 ,p_english_contrib_code => g_english_contrib_code
480 ,p_french_base => g_french_base
481 ,p_french_rate => g_french_rate
482 ,p_french_pay_value => g_french_pay_value
483 ,p_french_contrib_code => g_french_contrib_code);
484 g_payroll_action_id := p_payroll_action_id;
485 END IF;
486 hr_utility.set_location(' Leaving: ' || l_proc,99);
487
488 END archinit;
489
490 /*--------------------------------------------------------------------------
491 Name : archive_code
492 Purpose : This creates child assignment actions as necessary
493 and archives the pertinent data for a leaf action.
494
495 Arguments :
496 Notes : Assumes no more than 3 levels in the action hierarchy.
497 --------------------------------------------------------------------------*/
498 PROCEDURE archive_code (p_assignment_action_id in number,
499 p_effective_date in date) IS
500
501
502 -- Local Variable
503
504 l_proc VARCHAR2(60):= g_package||' Archive code ';
505
506 l_child boolean:=false;
507 l_grand_child boolean:=false;
508 l_num number(1);
509 --
510 -- Cursors
511 --
512 CURSOR csr_locked_action_info is
513 SELECT payact.action_type type,
514 locked_assact.assignment_action_id id,
515 locked_assact.tax_unit_id
516 FROM pay_action_interlocks interlock
517 , pay_assignment_actions locked_assact
518 , pay_payroll_actions payact
519 WHERE interlock.locking_action_id = p_assignment_action_id
520 AND interlock.locked_action_id = locked_assact.assignment_action_id
521 AND locked_assact.payroll_action_id = payact.payroll_action_id;
522 --
523 CURSOR csr_locking_reversal (p_run_act_id number) is
524 SELECT 1 /* if the run action is reversed exclude it */
525 FROM pay_action_interlocks rev_interlock
526 , pay_assignment_actions rev_assact
527 , pay_payroll_actions rev_payact
528 WHERE rev_interlock.locked_action_id = p_run_act_id
529 AND rev_interlock.locking_action_id = rev_assact.assignment_action_id
530 AND rev_assact.action_status = 'C'
531 AND rev_payact.payroll_action_id = rev_assact.payroll_action_id
532 AND rev_payact.action_type = 'V'
533 AND rev_payact.action_status = 'C';
534
535 CURSOR csr_run_child is
536 SELECT assact.chunk_number
537 ,runchild.payroll_action_id
538 ,runchild.assignment_action_id
539 ,runchild.assignment_id
540 ,runchild.tax_unit_id
541 ,pay_assignment_actions_s.nextval new_ass_act_id
542 FROM pay_assignment_actions assact
543 ,pay_action_interlocks interlock
544 ,pay_assignment_actions runchild
545 WHERE assact.assignment_action_id = p_assignment_action_id
546 AND interlock.locking_action_id = assact.assignment_action_id
547 AND interlock.locked_action_id = runchild.source_action_id;
548
549 CURSOR csr_grand_child (p_child_action_id in number) is
550 SELECT assact.assignment_action_id
551 ,assact.tax_unit_id
552 ,pay_assignment_actions_s.nextval new_ass_act_id
553 FROM pay_assignment_actions assact
554 WHERE assact.source_action_id = p_child_action_id;
555 --
556 l_locked_action csr_locked_action_info%ROWTYPE;
557 --
558 BEGIN
559
560
561 hr_utility.set_location('Entering ' || l_proc,10);
562
563 open csr_locked_action_info;
564 fetch csr_locked_action_info into l_locked_action;
565 close csr_locked_action_info;
566 if l_locked_action.type <> 'X' then
567
568 --Create child archive assignment action records
569
570 FOR child IN csr_run_child LOOP
571 --
572 l_child := true;
573 l_grand_child :=false;
574 hr_nonrun_asact.insact(lockingactid => child.new_ass_act_id
575 ,assignid => child.assignment_id
576 ,pactid => g_payroll_action_id
577 ,chunk => child.chunk_number
578 ,greid => child.tax_unit_id
579 ,source_act => p_assignment_action_id);
580 --
581 -- insert the lock on the run action.
582 --
583 hr_nonrun_asact.insint(child.new_ass_act_id,child.assignment_action_id);
584
585 --Create grand child archive assignment action records
586 FOR grand_child IN csr_grand_child (child.assignment_action_id)
587 LOOP
588 l_grand_child :=true;
589 hr_nonrun_asact.insact(lockingactid => grand_child.new_ass_act_id
590 ,assignid => child.assignment_id
591 ,pactid => g_payroll_action_id
592 ,chunk => child.chunk_number
593 ,greid => grand_child.tax_unit_id
594 ,source_act => child.new_ass_act_id);
595 --
596 -- insert the lock on the run action.
597 --
598
599 hr_nonrun_asact.insint(grand_child.new_ass_act_id,
600 grand_child.assignment_action_id);
601
602 open csr_locking_reversal (grand_child.assignment_action_id);
603 fetch csr_locking_reversal into l_num;
604 if csr_locking_reversal%NOTFOUND then
605 -- Run the contribution retrieval procedure
606 pay_fr_ducs.retrieve_contributions(grand_child.new_ass_act_id,
607 p_effective_date,
608 grand_child.tax_unit_id);
609 end if; -- csr_locking_reversal%NOTFOUND
610 close csr_locking_reversal;
611 update pay_assignment_actions
612 set action_status = 'C'
613 where assignment_action_id = grand_child.new_ass_act_id;
614 END LOOP; -- grand_child
615 -- Only process the child action if it has no grand child actions
616 IF not l_grand_child THEN
617 open csr_locking_reversal (child.assignment_action_id);
618 fetch csr_locking_reversal into l_num;
619 if csr_locking_reversal%NOTFOUND then
620 -- Run the contribution retrieval procedure
621 pay_fr_ducs.retrieve_contributions(child.new_ass_act_id,
622 p_effective_date,
623 child.tax_unit_id);
624 end if; -- csr_locking_reversal%NOTFOUND
625 close csr_locking_reversal;
626 END IF;
627 update pay_assignment_actions
628 set action_status = 'C'
629 where assignment_action_id = child.new_ass_act_id;
630 END LOOP; -- child
631
632
633 hr_utility.set_location('Step ' || l_proc,20);
634
635 -- Only process the parent action if it has no child actions
636
637 IF not l_child THEN
638 open csr_locking_reversal (l_locked_action.id);
639 fetch csr_locking_reversal into l_num;
640 if csr_locking_reversal%NOTFOUND then
641 pay_fr_ducs.retrieve_contributions(p_assignment_action_id
642 ,p_effective_date
643 ,l_locked_action.tax_unit_id);
644 end if; -- csr_locking_reversal%NOTFOUND
645 close csr_locking_reversal;
646 END IF;
647 end if; -- l_action_type <> 'X'
648 hr_utility.set_location('Leaving ' || l_proc,100);
649
650 END archive_code; -- End of Archive Code
651
652 -------------------------------------------------------------------
653 --Procedure Retreive Contribituions
654 -------------------------------------------------------------------
655
656 PROCEDURE retrieve_contributions(p_assignment_action_id in number
657 ,p_effective_date in date
658 ,p_tax_unit_id in number default null)
659 IS
660
661
662 -- Local Variable
663
664 l_proc VARCHAR2(60):= g_package||' retrieve_contributions ';
665
666
667 l_establishment_id pay_assignment_actions.tax_unit_id%TYPE;
668 l_Order_Number binary_integer;
669
670 l_page_type pay_run_result_values.result_value%TYPE;
671 l_subpage_identifier varchar2(150);
672
673
674 l_action_info_id pay_action_information.action_information_id%TYPE;
675 l_ovn pay_action_information.object_version_number%TYPE;
676
677
678 l_rate_type pay_fr_contribution_usages.RATE_TYPE%TYPE;
679
680 --
681 -- Cursor sums the rates of contributions for common contribution codes
682 -- and bases within an assignment action.
683 --
684
685 CURSOR ccontrib is
686 SELECT decode(substr(contribution_code,1,1),'1','URSSAF'
687 ,'2','ASSEDIC'
688 ,'3','AGIRC'
689 ,'4','ARRCO') contribution_type
690 , contribution_code
691 , base
692 , source_asg_action_id
693 , nvl(process_path,' ') retro_process_path
694 , retro_adjustment_type
695 , sum(rate) rate
696 , sum(pv) pv
697 FROM (
698 SELECT /*+ ORDERED USE_NL(et) INDEX(et PAY_ELEMENT_TYPES_F_PK) */
699 rr.run_result_id
700 , nvl(epd.source_asg_action_id,rr.assignment_action_id) source_asg_action_id
701 , epd.process_path
702 , epd.adjustment_type retro_adjustment_type
703 , max(decode(iv.name,
704 g_english_contrib_code,rrv.result_value,
705 g_french_contrib_code, rrv.result_value)) contribution_code
706 , nvl(max(decode(iv.name,
707 g_english_base, fnd_number.canonical_to_number(rrv.result_value),
708 g_french_base, fnd_number.canonical_to_number(rrv.result_value))),0) base
709 , nvl(max(decode(iv.name,
710 g_english_rate, fnd_number.canonical_to_number(rrv.result_value),
711 g_french_rate, fnd_number.canonical_to_number(rrv.result_value))),0) rate
712 , nvl(max(decode(iv.name,
713 g_english_pay_value, decode(ec.classification_name,'Rebates',-1,1) *
714 fnd_number.canonical_to_number(rrv.result_value),
715 g_french_pay_value, decode(ec.classification_name,'Rebates',-1,1) *
716 fnd_number.canonical_to_number(rrv.result_value))),0) pv
717 FROM pay_action_interlocks ail,
718 pay_run_results rr,
719 pay_element_types_f et,
720 pay_element_classifications ec,
721 pay_input_values_f iv,
722 pay_run_result_values rrv,
723 pay_entry_process_details epd
724 WHERE ail.locking_action_id = p_assignment_action_id
725 AND rr.assignment_action_id = ail.locked_action_id
726 AND rr.element_type_id = et.element_type_id
727 AND et.classification_id = ec.classification_id
728 AND ec.classification_name in
729 ('Statutory EE Deductions'
730 ,'Statutory ER Charges'
731 ,'CSG Non-Deductible'
732 ,'Conventional EE Deductions'
733 ,'Conventional ER Charges'
734 ,'Rebates')
735 AND ec.legislation_code = 'FR'
736 AND g_effective_date between
737 et.effective_start_date and et.effective_end_date
738 AND rr.element_type_id = et.element_type_id
739 AND rrv.run_result_id = rr.run_result_id
740 AND rr.status in ('P','PA')
741 AND rrv.input_value_id = iv.input_value_id
742 AND iv.element_type_id = et.element_type_id
743 AND iv.name in (g_english_base,g_french_base
744 ,g_english_rate,g_french_rate
745 ,g_english_pay_value,g_french_pay_value
746 ,g_english_contrib_code,g_french_contrib_code)
747 AND g_effective_date between
748 iv.effective_start_date and iv.effective_end_date
749 and epd.element_entry_id (+) = rr.element_entry_id
750 and epd.retro_component_id (+) is not null
751 GROUP BY rr.run_result_id,
752 nvl(epd.source_asg_action_id,rr.assignment_action_id),
753 epd.process_path,epd.adjustment_type
754 HAVING max(decode(iv.name,
755 g_english_contrib_code,rrv.result_value,
756 g_french_contrib_code, rrv.result_value)) < '5')
757 --
758 GROUP BY decode(substr(contribution_code,1,1),'1','URSSAF'
759 ,'2','ASSEDIC'
760 ,'3','AGIRC'
761 ,'4','ARRCO')
762 , source_asg_action_id
763 , nvl(process_path,' ')
764 , retro_adjustment_type
765 , contribution_code,base
766 ORDER BY decode(substr(contribution_code,1,1),'1','URSSAF'
767 ,'2','ASSEDIC'
768 ,'3','AGIRC'
769 ,'4','ARRCO')
770 , source_asg_action_id
771 , nvl(process_path,' ')
772 , retro_adjustment_type
773 , contribution_code;
774 --
775 -- Cursor sums the rates of contributions for common contribution codes
776 -- and bases within an assignment action.
777 --
778 CURSOR cassact is
779 SELECT tax_unit_id
780 FROM pay_assignment_actions
781 WHERE assignment_action_id = p_assignment_action_id;
782 --
783 -- Cursor to retrieve the pension provider id
784 --
785 CURSOR cestpens(p_Order_Number varchar2) is
786 SELECT org_information1 -- Org ID of Pension Provider
787 FROM hr_organization_information
788 WHERE organization_id = l_establishment_id
789 and org_information4 = p_Order_Number
790 and org_information_context = 'FR_ESTAB_PE_PRVS';
791 --
792 type t_contrib_rec is record (
793 r ccontrib%ROWTYPE,
794 group_type varchar2(30));
795
796 crec t_contrib_rec; -- Current record
797 prec t_contrib_rec; -- Previous record
798
799 BEGIN
800
801
802 hr_utility.set_location('Entering ' || l_proc,10);
803
804 -- Determine establishment ID
805 IF p_tax_unit_id is null then
806 OPEN cassact;
807 FETCH cassact INTO l_establishment_id;
808 CLOSE cassact;
809 ELSE
810 l_establishment_id := p_tax_unit_id;
811 END IF;
812
813 prec.r.contribution_code := null;
814 crec.group_type :='FULL'; -- will remain FULL for all but URSSAF
815 -- given ordering of ccontrib
816
817 open ccontrib;
818 LOOP
819 fetch ccontrib into crec.r;
820
821 -- Bug 2311582 commented for l_group_type to refer to the previous group_type
822 --l_group_type :='FULL';
823
824 --
825 -- For URSSAF merge "A" rate and Pay Value into "D" records
826 -- N.B. The "A" and "D" rows will be consecutive
827 --
828 IF ccontrib%FOUND and crec.r.contribution_type = 'URSSAF' THEN
829 --
830 IF substr(crec.r.contribution_code,7,1) = 'D'
831 AND prec.r.contribution_code = substr(crec.r.contribution_code,1,6)||'A'
832 AND prec.r.base = crec.r.base
833 AND prec.r.source_asg_action_id = crec.r.source_asg_action_id
834 AND prec.r.retro_process_path = crec.r.retro_process_path
835 THEN
836 crec.r.rate := crec.r.rate + prec.r.rate;
837 crec.r.pv := crec.r.pv + prec.r.pv;
838 prec.r.base := 0;
839 prec.r.pv := 0;
840 END IF;
841
842 -- If the previous code (1st 6 chars) is the same as the current
843 -- code or if the code ends in A then need to use the full
844 -- contribion code for grouping, otherwise use partial code
845
846 -- Bug 2311582 added l_group_type = 'FULL'
847
848 IF (substr(prec.r.contribution_code,1,6)=
849 substr(crec.r.contribution_code,1,6) AND prec.group_type = 'FULL')
850 OR substr(crec.r.contribution_code,7,1) = 'A'
851 THEN
852 crec.group_type := 'FULL';
853 ELSE
854 crec.group_type := 'PARTIAL';
855 END IF;
856 END IF; -- URSSAF processing
857 --
858 IF prec.r.contribution_code is not null THEN
859 -- Not first time through loop so archive the previous record
860 IF prec.r.contribution_type in ('URSSAF','ASSEDIC') THEN
861 l_page_type := prec.r.contribution_type;
862 l_subpage_identifier := fnd_number.number_to_canonical(
863 l_establishment_id);
864 ELSIF prec.r.contribution_type IN ('AGIRC','ARRCO') THEN
865 l_page_type := 'PENSION';
866 -- Determine Company Pension Provider ID for Pension contribs
867 IF g_estab_pens_provs.estab_id is null
868 OR g_estab_pens_provs.estab_id <> l_establishment_id THEN
869 g_estab_pens_provs.estab_id := l_establishment_id;
870 g_estab_pens_provs.pens_provs.delete;
871 END IF;
872 l_Order_Number := to_number(substr(prec.r.contribution_code,2,1));
873 IF NOT g_estab_pens_provs.pens_provs.exists(l_Order_Number) THEN
874 OPEN cestpens(to_char(l_Order_Number));
875 FETCH cestpens INTO g_estab_pens_provs.pens_provs(l_Order_Number);
876 IF cestpens%notfound THEN
877 hr_utility.set_message(801, 'PAY_75087_DUCS_PENS_PROV');
878 FND_FILE.NEW_LINE(fnd_file.log, 1);
879 FND_FILE.PUT_LINE(fnd_file.log,hr_utility.get_message);
880 END IF;
881 CLOSE cestpens;
882 --
883 END IF;
884 l_subpage_identifier := g_estab_pens_provs.pens_provs(l_Order_Number);
885 END IF;
886
887 pay_action_information_api.create_action_information (
888 p_action_information_id => l_action_info_id
889 , p_action_context_id => p_assignment_action_id
890 , p_action_context_type => 'AAP'
891 , p_object_version_number => l_ovn
892 , p_action_information_category => 'FR_DUCS_ACTION_CONTRIB_INFO'
893 , p_action_information1 => l_subpage_identifier
894 , p_action_information2 => l_page_type
895 , p_action_information3 => prec.r.contribution_type
896 , p_action_information4 => prec.r.contribution_code
897 , p_action_information5 => fnd_number.number_to_canonical(prec.r.base)
898 , p_action_information6 => fnd_number.number_to_canonical(prec.r.rate)
899 , p_action_information7 => fnd_number.number_to_canonical(prec.r.pv)
900 , p_action_information8 => prec.group_type);
901
902 END IF;
903 EXIT WHEN ccontrib%NOTFOUND;
904 prec := crec;
905 END LOOP;
906 close ccontrib;
907
908
909 hr_utility.set_location('Leaving ' || l_proc, 100);
910
911 END retrieve_contributions;
912
913 ---------------------------------------------------------------------
914 -- Summary process
915 ---------------------------------------------------------------------
916 ---------------------------------------------------------------------
917 -- FUNCTION split_payment
918 ---------------------------------------------------------------------
919
920 FUNCTION split_payment(
921 p_total_payment in number
922 ,p_payment_type in varchar2
923 ,p_limit in number
924 ,p_remaining_amount in out nocopy number) return number IS
925
926 l_payment number:=0;
927
928 BEGIN
929
930
931 IF p_payment_type = 'REMAINDER' THEN
932 l_payment:=p_remaining_amount;
933 ELSIF p_payment_type = 'AMOUNT' THEN
934 l_payment:=least(p_limit,p_remaining_amount);
935 ELSE
936 l_payment:=least(p_total_payment*p_limit/100,p_remaining_amount);
937 END IF;
938
939 p_remaining_amount := p_remaining_amount - l_payment;
940
941 return (l_payment);
942
943
944 END split_payment;
945
946 ---------------------------------------------------------------------
947 -- PROCEDURE get_lookup
948 ---------------------------------------------------------------------
949
950 PROCEDURE get_lookup(
951 p_lookup_type in varchar2
952 ,p_lookup_code in varchar2
953 ,p_lookup_meaning out nocopy varchar2
954 ,p_lookup_tag out nocopy varchar2) IS
955
956 CURSOR csr_get_lookup IS
957 SELECT meaning,tag
958 FROM fnd_lookup_values
959 WHERE lookup_type=p_lookup_type
960 AND lookup_code=p_lookup_code
961 AND language = userenv('LANG')
962 AND view_application_id = 3;
963
964
965 BEGIN
966
967 OPEN csr_get_lookup;
968 FETCH csr_get_lookup INTO p_lookup_meaning,p_lookup_tag;
969
970 CLOSE csr_get_lookup;
971
972 END get_lookup;
973
974 ---------------------------------------------------------------------
975 -- get_count_emps
976 ---------------------------------------------------------------------
977
978 PROCEDURE get_count_emps(p_payroll_action_id in number
979 ,p_page_identifier in number
980 ,p_page_type in varchar2
981 ,p_contribution_emps out nocopy number
982 ,p_month_end_male out nocopy number
983 ,p_month_end_female out nocopy number
984 ,p_month_end_total out nocopy number
985 ,p_total_actions out nocopy number) IS
986
987
988 l_male_count number:=0;
989 l_female_count number:=0;
990 l_total_count number:=0;
991 l_actions_count number:=0;
992 l_sex varchar2(2);
993 l_page_id_chr pay_action_information.action_information1%TYPE:=
994 fnd_number.number_to_canonical(p_page_identifier);
995
996 CURSOR cur_per IS
997 SELECT distinct paa.person_id
998 FROM pay_action_information pai
999 ,pay_assignment_actions pac
1000 ,per_all_assignments_f paa
1001 WHERE pac.payroll_action_id=p_payroll_action_id
1002 and pai.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
1003 and pai.action_information1 = l_page_id_chr
1004 and pai.action_information2 = p_page_type
1005 and pai.action_context_id =pac.assignment_action_id
1006 and paa.assignment_id=pac.assignment_id;
1007
1008 CURSOR cur_sex(l_person_id number) IS
1009 SELECT per.sex
1010 FROM per_all_people_f per
1011 WHERE per.person_id = l_person_id;
1012
1013 CURSOR cur_asg_count IS
1014 SELECT count(distinct pac.assignment_id),
1015 count(distinct pac.assignment_action_id)
1016 FROM pay_action_information pai
1017 ,pay_assignment_actions pac
1018 WHERE pac.payroll_action_id=p_payroll_action_id
1019 and pai.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
1020 and pai.action_information1 = l_page_id_chr
1021 and pai.action_information2 = p_page_type
1022 and pai.action_context_id =pac.assignment_action_id;
1023
1024 BEGIN
1025
1026 FOR rec_per IN cur_per LOOP
1027
1028 l_total_count:=l_total_count + 1;
1029
1030 OPEN cur_sex(rec_per.person_id);
1031 FETCH cur_sex INTO l_sex;
1032 CLOSE cur_sex;
1033
1034 IF l_sex='M' THEN
1035 l_male_count:=l_male_count + 1;
1036 ELSE
1037 l_female_count:=l_female_count + 1;
1038 END IF;
1039
1040 END LOOP;
1041
1042 OPEN cur_asg_count;
1043 FETCH cur_asg_count INTO p_contribution_emps, p_total_actions;
1044 CLOSE cur_asg_count;
1045
1046 p_month_end_total := l_total_count;
1047 p_month_end_male := l_male_count;
1048 p_month_end_female := l_female_count;
1049
1050
1051 END get_count_emps;
1052 --
1053 ---------------------------------------------------------------------
1054 --PROCEDURE Process_payment
1055 ---------------------------------------------------------------------
1056 PROCEDURE process_payment(
1057 p_name in varchar2
1058 ,p_total_payment in number
1059 ,p_payment1_type in varchar2
1060 ,p_payment1_limit in number
1061 ,p_payment1_value out nocopy number
1062 ,p_payment2_type in varchar2
1063 ,p_payment2_limit in number
1064 ,p_payment2_value out nocopy number
1065 ,p_payment3_type in varchar2
1066 ,p_payment3_limit in number
1067 ,p_payment3_value out nocopy number) IS
1068
1069 -- Local Variable
1070 l_proc VARCHAR2(40):= g_package||' process_payment';
1071
1072 l_remaining_amount number;
1073
1074 BEGIN
1075
1076
1077 l_remaining_amount := p_total_payment;
1078
1079 IF p_payment1_type IS NOT null and
1080 (p_payment1_type = 'REMAINDER' OR
1081 p_payment1_limit IS NOT null) THEN
1082 p_payment1_value := split_payment( p_total_payment
1083 , p_payment1_type
1084 , p_payment1_limit
1085 , l_remaining_amount);
1086
1087
1088 END IF;
1089
1090
1091
1092 IF p_payment2_type IS NOT null and
1093 (p_payment2_type = 'REMAINDER' OR
1094 p_payment2_limit IS NOT null) THEN
1095 p_payment2_value := split_payment( p_total_payment
1096 , p_payment2_type
1097 , p_payment2_limit
1098 , l_remaining_amount);
1099
1100 END IF;
1101
1102 IF p_payment3_type IS NOT null and
1103 (p_payment3_type = 'REMAINDER' OR
1104 p_payment3_limit is not null) THEN
1105 p_payment3_value := split_payment( p_total_payment
1106 , p_payment3_type
1107 , p_payment3_limit
1108 , l_remaining_amount);
1109
1110 END IF;
1111
1112 IF l_remaining_amount > 0 THEN
1113 hr_utility.set_message(801, 'PAY_75088_DUCS_TOTAL_NOT_ALLOC');
1114 hr_utility.set_message_token(801,'ORGANIZATION',p_name);
1115 FND_FILE.NEW_LINE(fnd_file.log, 1);
1116 FND_FILE.PUT_LINE(fnd_file.log,hr_utility.get_message);
1117 END IF;
1118
1119
1120 END process_payment;
1121
1122 ---------------------------------------------------------------------
1123 --PROCEDURE Process_contributions
1124 ---------------------------------------------------------------------
1125 PROCEDURE process_contributions(p_payroll_action_id in number
1126 ,p_page_identifier in number
1127 ,p_page_type in varchar2
1128 ,p_total_contributions out nocopy number) IS
1129
1130
1131 -- Local Variable
1132 l_proc varchar2(40):= g_package||' process_contributions ';
1133
1134 l_total_contrib number;
1135 l_total_payment number;
1136 l_sort1_code varchar2(30);
1137 l_sort1_text1 varchar2(30);
1138 l_sort1_text2 varchar2(30);
1139 l_sort2_code varchar2(30);
1140 l_sort2_text1 varchar2(30);
1141 l_sort2_text2 varchar2(30);
1142 l_organization_id varchar2(30);
1143 l_pension_provider varchar2(30);
1144 l_pension_provider_id varchar2(30);
1145
1146 l_contribution_text pay_action_information.action_information4%TYPE;
1147 l_meaning varchar2(80);
1148 l_tag pay_action_information.action_information4%TYPE;
1149 l_pension_code varchar2(80);
1150 l_pay_value number;
1151
1152 l_action_info_id pay_action_information.action_information_id%TYPE;
1153 l_ovn pay_action_information.object_version_number%TYPE;
1154
1155 --
1156 -- Cursors
1157 --
1158 CURSOR ccontrib_urssaf_assedic is
1159 SELECT /*+ ORDERED */
1160 contrib.action_information1 subpage_identifier
1161 , contrib.action_information3 contribution_type
1162 , substr(contrib.action_information4,1,1)||
1163 translate(substr(contrib.action_information4,2,2), '1234567890',
1164 decode(contrib.action_information8
1165 ,'FULL','1234567890'
1166 ,'PARTIAL','XXXXXXXXXX'))||
1167 substr(contrib.action_information4,4,4) contribution_code
1168 , round(sum(fnd_number.canonical_to_number(contrib.action_information5))) base
1169 , fnd_number.canonical_to_number(contrib.action_information6) rate
1170 , sum(fnd_number.canonical_to_number(contrib.action_information7)) pay_value
1171 , count(distinct assact.assignment_id) number_of_employees
1172 FROM pay_assignment_actions assact
1173 , pay_action_information contrib
1174 WHERE
1175 assact.payroll_action_id in
1176 (SELECT payroll_action_id
1177 FROM pay_payroll_actions payact
1178 , pay_action_information actinfo
1179 WHERE payact.effective_date between g_period_start_date
1180 and g_effective_date
1181 and payact.payroll_action_id = actinfo.action_context_id
1182 and actinfo.action_context_type = 'PA'
1183 and actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
1184 and payact.report_type = 'DUCS_ARCHIVE'
1185 and payact.report_qualifier = 'FR'
1186 and payact.report_category = 'DUCS_ARCHIVE')
1187 and assact.assignment_action_id = contrib.action_context_id
1188 and contrib.action_context_type = 'AAP'
1189 and contrib.action_information1 = to_char(p_page_identifier)
1190 and contrib.action_information2 = p_page_type
1191 and contrib.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
1192 GROUP BY contrib.action_information1
1193 , contrib.action_information3
1194 , substr(contrib.action_information4,1,1)||
1195 translate(substr(contrib.action_information4,2,2), '1234567890',
1196 decode(contrib.action_information8 ,'FULL','1234567890'
1197 ,'PARTIAL','XXXXXXXXXX'))||
1198 substr(contrib.action_information4,4,4)
1199 , fnd_number.canonical_to_number(contrib.action_information6)
1200 ORDER BY contrib.action_information1 ,contrib.action_information3;
1201
1202 CURSOR ccontrib_pension is
1203 SELECT /*+ ORDERED */
1204 contrib.action_information1 subpage_identifier
1205 , contrib.action_information3 contribution_type
1206 , substr(contrib.action_information4,1,1)||
1207 translate(substr(contrib.action_information4,2,2), '1234567890',
1208 decode(contrib.action_information8
1209 ,'FULL','1234567890'
1210 ,'PARTIAL','XXXXXXXXXX'))||
1211 substr(contrib.action_information4,4,4) contribution_code
1212 , round(sum(fnd_number.canonical_to_number(contrib.action_information5))) base
1213 , fnd_number.canonical_to_number(contrib.action_information6) rate
1214 , sum(fnd_number.canonical_to_number(contrib.action_information7)) pay_value
1215 , count(distinct assact.assignment_id) number_of_employees
1216 FROM pay_payroll_actions payact
1217 , pay_assignment_actions assact
1218 , pay_action_information contrib
1219 WHERE assact.payroll_action_id = payact.payroll_action_id
1220 and payact.report_type = 'DUCS_ARCHIVE'
1221 and payact.report_qualifier = 'FR'
1222 and payact.report_category = 'DUCS_ARCHIVE'
1223 and payact.effective_date between g_period_start_date and g_effective_date
1224 and payact.business_group_id = g_business_group_id
1225 and contrib.action_context_type = 'AAP'
1226 and assact.assignment_action_id = contrib.action_context_id
1227 and ((contrib.action_information1 in
1228 (SELECT pens_prv.org_information1
1229 FROM hr_organization_information pens_prv
1230 WHERE pens_prv.org_information_id = p_page_identifier
1231 AND pens_prv.org_information_context = 'FR_COMP_PE_PRVS'))
1232 or
1233 (contrib.action_information1 in
1234 (SELECT fnd_number.number_to_canonical(ind_pens_prv.organization_id)
1235 FROM hr_organization_information ind_pens_prv
1236 , hr_organization_information pens_grp
1237 WHERE pens_grp.org_information_id = p_page_identifier
1238 AND pens_grp.org_information_context = 'FR_COMP_PE_PRVS'
1239 AND ind_pens_prv.org_information3 = pens_grp.org_information1
1240 AND ind_pens_prv.org_information_context = 'FR_PE_PRV_INFO')))
1241 and contrib.action_information2 = p_page_type
1242 and contrib.action_information_category = 'FR_DUCS_ACTION_CONTRIB_INFO'
1243 GROUP BY contrib.action_information1
1244 , contrib.action_information3
1245 , substr(contrib.action_information4,1,1)||
1246 translate(substr(contrib.action_information4,2,2), '1234567890',
1247 decode(contrib.action_information8 ,'FULL','1234567890'
1248 ,'PARTIAL','XXXXXXXXXX'))||
1249 substr(contrib.action_information4,4,4)
1250 , fnd_number.canonical_to_number(contrib.action_information6)
1251 ORDER BY contrib.action_information1, contrib.action_information3;
1252
1253 /**/
1254
1255 BEGIN
1256
1257 l_total_contrib := 0;
1258 l_total_payment := 0;
1259 l_sort1_code := null;
1260 l_sort1_text1 := null;
1261 l_sort1_text2 := null;
1262 l_sort2_code := null;
1263 l_sort2_text1 := null;
1264 l_sort2_text2 := null;
1265 l_organization_id := null;
1266 l_pension_provider := null;
1267 l_pension_provider_id := null;
1268 l_pension_code := null;
1269
1270
1271 IF p_page_type in ('URSSAF','ASSEDIC') then
1272
1273 FOR rec_contr IN ccontrib_urssaf_assedic LOOP
1274
1275
1276
1277 -- Get Contribution Text
1278 IF rec_contr.contribution_type = 'URSSAF' THEN
1279 -- Last 4 chars of contribution code define the text
1280 l_contribution_text := hr_general.decode_lookup('FR_URSSAF_CONTRI_CODE'
1281 ,substr(rec_contr.contribution_code,4,4));
1282
1283 ELSIF rec_contr.contribution_type = 'ASSEDIC' THEN -- ASSEDIC
1284 -- Last 3 chars of contribution code define the text
1285 pay_fr_ducs.get_lookup('FR_ASSEDIC_CONTRI_CODE'
1286 ,substr(rec_contr.contribution_code,5,3)
1287 ,l_meaning
1288 ,l_tag);
1289 l_contribution_text := l_meaning;
1290
1291
1292 l_sort1_code := substr(l_tag,instr(l_tag,'=')+1,instr(l_tag,',')-instr(l_tag,'=',-1)-1);
1293 l_sort1_text1 :=substr(l_tag,instr(l_tag,',')+1,INSTR(l_tag,',',-1,1)-instr(l_tag,',',-1,2)-1);
1294 l_sort1_text2 :=substr(l_tag,instr(l_tag,',',-1)+1);
1295
1296
1297 END IF;
1298
1299 -- Bug 2311582
1300
1301 IF NVL(rec_contr.base,0) <> 0 AND NVL(rec_contr.rate,0) <> 0 THEN
1302 l_pay_value := rec_contr.base * (rec_contr.rate/100);
1303 ELSE
1304 l_pay_value := rec_contr.pay_value;
1305 END IF;
1306
1307 l_pay_value := round(l_pay_value,2);
1308
1309
1310 pay_action_information_api.create_action_information (
1311 p_action_information_id => l_action_info_id
1312 , p_action_context_id => p_payroll_action_id
1313 , p_action_context_type => 'PA'
1314 , p_object_version_number => l_ovn
1315 , p_action_information_category => 'FR_DUCS_CONTRIB_INFO'
1316 , p_action_information1 => p_page_identifier
1317 , p_action_information2 => p_page_type
1318 , p_action_information3 => rec_contr.contribution_code
1319 , p_action_information4 => l_contribution_text
1320 , p_action_information5 => l_sort1_code
1321 , p_action_information6 => l_sort1_text1
1322 , p_action_information7 => l_sort1_text2
1323 , p_action_information8 => l_sort2_code
1324 , p_action_information9 => l_sort2_text1
1325 , p_action_information10 => l_sort2_text2
1326 , p_action_information11 => fnd_number.number_to_canonical(
1327 rec_contr.number_of_employees)
1328 , p_action_information12 => fnd_number.number_to_canonical(
1329 rec_contr.base)
1330 , p_action_information13 => fnd_number.number_to_canonical(
1331 rec_contr.rate)
1332 , p_action_information14 => fnd_number.number_to_canonical(
1333 l_pay_value));
1334
1335 -- Keep running total
1336
1337 l_total_contrib := l_total_contrib + l_pay_value;
1338
1339
1340 END LOOP;
1341
1342
1343 ELSE -- PENSION
1344 -- Concatenate last 5 chars of contrib code with
1345 -- TAG value from FR_EMPLOYEE_PENSION
1346 -- and FR_PENSION_CODE meaning for code of last 2 chars
1347 --
1348 FOR rec_contr IN ccontrib_pension LOOP
1349
1350
1351
1352 pay_fr_ducs.get_lookup('FR_PENSION_CATEGORY'
1353 ,substr(rec_contr.contribution_code,3,3)
1354 ,l_meaning
1355 ,l_tag);
1356 l_tag:=replace(l_tag,'N/C','NON CADRES');
1357
1358 -- if the code exists in FR_USER_PENSION_CODE first
1359 l_pension_code := hr_general.decode_lookup('FR_USER_PENSION_CONTRIB_CODE'
1360 ,substr(rec_contr.contribution_code,6,2));
1361
1362 -- If user code is null then use
1363 IF l_pension_code IS NULL THEN
1364 l_pension_code := hr_general.decode_lookup('FR_PENSION_CONTRI_CODE'
1365 ,substr(rec_contr.contribution_code,6,2));
1366 END IF;
1367
1368
1369 l_contribution_text := substr(rec_contr.contribution_code,3,5) || ' '
1370 || l_tag || ' ' || l_pension_code;
1371
1372
1373
1374 -- if pension provider is different from last row retrieved
1375 -- then get new pension provider details otherwise
1376 -- reuse existing details
1377
1378 IF rec_contr.subpage_identifier = l_pension_provider_id then
1379 NULL;
1380 ELSE
1381 l_pension_provider_id := rec_contr.subpage_identifier;
1382 BEGIN
1383 SELECT org_information1
1384 INTO l_pension_provider
1385 FROM hr_organization_information
1386 WHERE organization_id = l_pension_provider_id
1387 AND org_information_context = 'FR_PE_PRV_INFO';
1388 EXCEPTION
1389 WHEN no_data_found THEN
1390 hr_utility.set_message(801,'PAY_75087_DUCS_PENS_PROV');
1391 FND_FILE.NEW_LINE(fnd_file.log, 1);
1392 FND_FILE.PUT_LINE(fnd_file.log,hr_utility.get_message);
1393 END;
1394 END IF;
1395
1396 l_sort1_code := rec_contr.subpage_identifier;
1397 l_sort1_text2 := l_pension_provider;
1398
1399 l_sort2_code := rec_contr.contribution_type;
1400 l_sort2_text2 := rec_contr.contribution_type;
1401
1402 -- Bug 2311582
1403
1404 IF NVL(rec_contr.base,0) <> 0 AND NVL(rec_contr.rate,0) <> 0 THEN
1405 l_pay_value := rec_contr.base * (rec_contr.rate/100);
1406 ELSE
1407 l_pay_value := rec_contr.pay_value;
1408 END IF;
1409
1410 l_pay_value:=round(l_pay_value,2);
1411
1412 pay_action_information_api.create_action_information (
1413 p_action_information_id => l_action_info_id
1414 , p_action_context_id => p_payroll_action_id
1415 , p_action_context_type => 'PA'
1416 , p_object_version_number => l_ovn
1417 , p_action_information_category => 'FR_DUCS_CONTRIB_INFO'
1418 , p_action_information1 => p_page_identifier
1419 , p_action_information2 => p_page_type
1420 , p_action_information3 => rec_contr.contribution_code
1421 , p_action_information4 => l_contribution_text
1422 , p_action_information5 => l_sort1_code
1423 , p_action_information6 => l_sort1_text1
1424 , p_action_information7 => l_sort1_text2
1425 , p_action_information8 => l_sort2_code
1426 , p_action_information9 => l_sort2_text1
1427 , p_action_information10 => l_sort2_text2
1428 , p_action_information11 => fnd_number.number_to_canonical(
1429 rec_contr.number_of_employees)
1430 , p_action_information12 => fnd_number.number_to_canonical(
1431 rec_contr.base)
1432 , p_action_information13 => fnd_number.number_to_canonical(
1433 rec_contr.rate)
1434 , p_action_information14 => fnd_number.number_to_canonical(
1435 l_pay_value));
1436
1437 l_total_contrib := l_total_contrib + l_pay_value;
1438 END LOOP;
1439
1440 END IF;
1441
1442
1443 p_total_contributions := l_total_contrib;
1444
1445
1446 END process_contributions;
1447
1448 ------------------------------------------------------------------------
1449 --deinitialize_code section
1450 ------------------------------------------------------------------------
1451
1452 PROCEDURE deinitialize_code(p_payroll_action_id in number) IS
1453
1454 l_proc VARCHAR2(60):= g_package||' deinitialize_code ';
1455 duplicate EXCEPTION;
1456
1457 -- Local Variable
1458 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
1459
1460 l_year varchar2(10);
1461 l_quarter varchar2(10);
1462 l_month varchar2(10);
1463 l_mm varchar2(12);
1464 l_miq varchar2(12);
1465 l_date_to varchar2(12);
1466 l_date_from varchar2(12);
1467 l_period_description varchar2(30);
1468 l_period_code varchar2(30);
1469 l_currency varchar2(10);
1470 l_currency_code varchar2(10);
1471 l_currency_number varchar2(10);
1472
1473 l_action_info_id pay_action_information.action_information_id%TYPE;
1474 l_ovn pay_action_information.object_version_number%TYPE;
1475
1476 l_org_information_id number;
1477 l_object_version_number number;
1478
1479 l_payment_1_account hr_organization_information.org_information3%TYPE;
1480 l_payment_1_type hr_organization_information.org_information4%TYPE;
1481 l_payment_1_limit hr_organization_information.org_information5%TYPE;
1482 l_payment_2_account hr_organization_information.org_information6%TYPE;
1483 l_payment_2_type hr_organization_information.org_information7%TYPE;
1484 l_payment_2_limit hr_organization_information.org_information8%TYPE;
1485 l_payment_3_account hr_organization_information.org_information9%TYPE;
1486 l_payment_3_type hr_organization_information.org_information10%TYPE;
1487 l_payment_3_limit hr_organization_information.org_information11%TYPE;
1488 l_advances number;
1489 l_regularization number;
1490 l_payment_1_acc_no varchar2(60);
1491 l_payment_2_acc_no varchar2(60);
1492 l_payment_3_acc_no varchar2(60);
1493
1494 l_total_contributions number;
1495 l_total_payment number;
1496
1497 l_contribution_emps number;
1498 l_month_end_male number;
1499 l_month_end_female number;
1500 l_month_end_total number;
1501 l_total_actions number;
1502
1503 l_payment_1_val number;
1504 l_payment_2_val number;
1505 l_payment_3_val number;
1506
1507 l_Declaration_Due date;
1508 l_Latest_Declaration date;
1509 l_Last_Contribution date;
1510 l_Payment_Date date;
1511
1512 -----------
1513 -- Cursor
1514 -----------
1515
1516 CURSOR c_existing_archive (p_company_id_chr in varchar2) is
1517 SELECT payact.payroll_action_id
1518 FROM pay_payroll_actions payact
1519 ,pay_action_information ref_actinfo
1520 WHERE payact.payroll_action_id = ref_actinfo.action_context_id
1521 and ref_actinfo.action_information_category = 'FR_DUCS_REFERENCE_INFO'
1522 and ref_actinfo.action_context_type = 'PA'
1523 and ref_actinfo.action_information1 = p_company_id_chr
1524 and ref_actinfo.action_information2 = l_period_code
1525 and payact.business_group_id = g_business_group_id
1526 and payact.payroll_action_id <> p_payroll_action_id;
1527
1528
1529 CURSOR csr_company is
1530 SELECT substr(o.name,1,150) company_name
1531 , substr(l.address_line_1,1,150) company_address_line_1
1532 , substr(l.address_line_2,1,150) company_address_line_2
1533 , substr(l.region_3,1,150) company_address_line_3
1534 , l.town_or_city company_address_line_4
1535 , l.telephone_number_1 company_telephone
1536 , l.telephone_number_2 company_fax
1537 , rep_estab_info.org_information2 rep_estab_SIRET
1538 , rep_estab_info.org_information3 rep_estab_NAF
1539 , comp_rep_info.ORG_INFORMATION1 Declaration_Due_Offset
1540 , comp_rep_info.ORG_INFORMATION2 Latest_Declaration_Offset
1541 , comp_rep_info.ORG_INFORMATION3 Last_Contribution_Offset
1542 , comp_rep_info.ORG_INFORMATION4 Payment_Date_Offset
1543 , comp_rep_info.ORG_INFORMATION5 Activities_Ceased_Date
1544 , comp_rep_info.ORG_INFORMATION6 No_Employees_Date
1545 , comp_rep_info.ORG_INFORMATION7 Activities_Suspended
1546 , comp_rep_info.ORG_INFORMATION8 Keep_Account_Open
1547 , comp_rep_info.ORG_INFORMATION9 Administrator_Line_1
1548 , comp_rep_info.ORG_INFORMATION10 Administrator_Line_2
1549 , comp_rep_info.ORG_INFORMATION11 Administrator_Telephone_Number
1550 , comp_rep_info.ORG_INFORMATION12 Administrator_FAX_Number
1551 FROM hr_all_organization_units o
1552 , hr_locations l
1553 , hr_organization_information comp_info
1554 , hr_organization_information rep_estab_info
1555 , hr_organization_information comp_rep_info
1556 WHERE o.organization_id = g_company_id
1557 and o.location_id = l.location_id (+)
1558 and comp_info.organization_id (+) = o.organization_id
1559 and comp_info.org_information_context (+) = 'FR_COMP_INFO'
1560 and rep_estab_info.organization_id (+) =
1561 to_number(comp_info.org_information10)
1562 and rep_estab_info.org_information_context (+) = 'FR_ESTAB_INFO'
1563 and comp_rep_info.organization_id (+) = o.organization_id
1564 and comp_rep_info.org_information_context (+) = 'FR_COMP_REPORTING_INFO';
1565
1566
1567 -- 4312297 Removed to_char on urssaf.organization_id and
1568 -- assedic.organization_id as that was disabling the index and
1569 -- causing FTS.
1570 -- Used hr_locations_all rather than hr_locations
1571 CURSOR csr_estab (p_company_id_chr in varchar2) is
1572 SELECT estab_info.organization_id establishment_id
1573 , estab_info.org_information2 estab_SIRET
1574 , estab_info.org_information3 estab_NAF
1575 --
1576 -- Establishment Reporting Details
1577 --
1578 , estab_rep_info.ORG_INFORMATION1 Activities_Ceased_Date
1579 , estab_rep_info.ORG_INFORMATION2 No_Employees_Date
1580 , estab_rep_info.ORG_INFORMATION3 Activities_Suspended
1581 , estab_rep_info.ORG_INFORMATION4 Keep_Account_Open
1582 --
1583 -- URSSAF Details
1584 --
1585 , urssaf.organization_id urssaf_id
1586 , substr(urssaf.name,1,150) urssaf_name
1587 , estab_urssaf_info.org_information2 estab_urssaf_ID
1588 , estab_urssaf_info.ORG_INFORMATION6 U_Declaration_Due_Offset
1589 , estab_urssaf_info.ORG_INFORMATION7 U_Latest_Declaration_Offset
1590 , estab_urssaf_info.ORG_INFORMATION8 U_Last_Contribution_Offset
1591 , estab_urssaf_info.ORG_INFORMATION9 U_Payment_Date_Offset
1592 , estab_urssaf_info.ORG_INFORMATION10 URSSAF_Payment_1_Account
1593 , estab_urssaf_info.ORG_INFORMATION11 URSSAF_Payment_1_Type
1594 , estab_urssaf_info.ORG_INFORMATION12 URSSAF_Payment_1_Limit
1595 , estab_urssaf_info.ORG_INFORMATION13 URSSAF_Payment_2_Account
1596 , estab_urssaf_info.ORG_INFORMATION14 URSSAF_Payment_2_Type
1597 , estab_urssaf_info.ORG_INFORMATION15 URSSAF_Payment_2_Limit
1598 , estab_urssaf_info.ORG_INFORMATION16 URSSAF_Payment_3_Account
1599 , estab_urssaf_info.ORG_INFORMATION17 URSSAF_Payment_3_Type
1600 , estab_urssaf_info.ORG_INFORMATION18 URSSAF_Payment_3_Limit
1601 , substr(urssaf_loc.address_line_1,1,150) urssaf_address_line_1
1602 , substr(urssaf_loc.address_line_2,1,150) urssaf_address_line_2
1603 , substr(urssaf_loc.region_3,1,150) urssaf_address_line_3
1604 , urssaf_loc.postal_code||' '||urssaf_loc.town_or_city urssaf_address_line_4
1605 --
1606 -- ASSEDIC Details
1607 --
1608 , substr(assedic.name,1,150) assedic_name
1609 , estab_assedic_info.org_information2 estab_ASSEDIC_ID
1610 , estab_assedic_info.ORG_INFORMATION4 A_Declaration_Due_Offset
1611 , estab_assedic_info.ORG_INFORMATION5 A_Latest_Declaration_Offset
1612 , estab_assedic_info.ORG_INFORMATION6 A_Last_Contribution_Offset
1613 , estab_assedic_info.ORG_INFORMATION7 A_Payment_Date_Offset
1614 , estab_assedic_info.ORG_INFORMATION8 ASSEDIC_Payment_1_Account
1615 , estab_assedic_info.ORG_INFORMATION9 ASSEDIC_Payment_1_Type
1616 , estab_assedic_info.ORG_INFORMATION10 ASSEDIC_Payment_1_Limit
1617 , estab_assedic_info.ORG_INFORMATION11 ASSEDIC_Payment_2_Account
1618 , estab_assedic_info.ORG_INFORMATION12 ASSEDIC_Payment_2_Type
1619 , estab_assedic_info.ORG_INFORMATION13 ASSEDIC_Payment_2_Limit
1620 , estab_assedic_info.ORG_INFORMATION14 ASSEDIC_Payment_3_Account
1621 , estab_assedic_info.ORG_INFORMATION15 ASSEDIC_Payment_3_Type
1622 , estab_assedic_info.ORG_INFORMATION16 ASSEDIC_Payment_3_Limit
1623 , substr(assedic_loc.address_line_1,1,150) assedic_address_line_1
1624 , substr(assedic_loc.address_line_2,1,150) assedic_address_line_2
1625 , substr(assedic_loc.region_3,1,150) assedic_address_line_3
1626 , assedic_loc.postal_code||' '||assedic_loc.town_or_city assedic_address_line_4
1627 FROM hr_organization_information estab_info
1628 , hr_organization_information estab_urssaf_info
1629 , hr_organization_information estab_assedic_info
1630 , hr_organization_information estab_rep_info
1631 , hr_all_organization_units urssaf
1632 , hr_all_organization_units assedic
1633 , hr_locations_all urssaf_loc
1634 , hr_locations_all assedic_loc
1635 WHERE estab_info.org_information1 = p_company_id_chr
1636 and estab_info.org_information_context = 'FR_ESTAB_INFO'
1637 --
1638 -- Get the URSSAF details
1639 --
1640 and estab_info.organization_id = estab_urssaf_info.organization_id (+)
1641 and estab_urssaf_info.org_information_context (+) = 'FR_ESTAB_URSSAF'
1642 and estab_urssaf_info.org_information1 = urssaf.organization_id(+)
1643 and urssaf.location_id = urssaf_loc.location_id (+)
1644 --
1645 -- Get the ASSEDIC details
1646 --
1647 and estab_info.organization_id = estab_assedic_info.organization_id (+)
1648 and estab_assedic_info.org_information_context (+) = 'FR_ESTAB_ASSEDIC'
1649 and estab_assedic_info.org_information1 = assedic.organization_id(+)
1650 and assedic.location_id = assedic_loc.location_id (+)
1651 --
1652 -- Get the Establishment Reporting details
1653 --
1654 and estab_info.organization_id = estab_rep_info.organization_id (+)
1655 and estab_rep_info.org_information_context (+) = 'FR_ESTAB_REPORTING_INFO';
1656
1657 --
1658 -- 3612082 Removed to_char on pens_prov.organization_id as it was disabling
1659 -- the index and causing FTS. Used ORDERED hint to ensure org_info
1660 -- is visited 1st hence org_information1 is numeric prior to any
1661 -- implicit conversion to number.
1662 -- Used hr_locations_all rather than hr_locations
1663 CURSOR csr_comp_pension_prvs is
1664 SELECT /*+ ORDERED */
1665 pens_prov_info.org_information_id comp_pens_prov_id
1666 , pens_prov.organization_id pens_prov_id
1667 , substr(pens_prov.name,1,150) name
1668 , substr(pens_loc.address_line_1,1,150) address_line_1
1669 , substr(pens_loc.address_line_2,1,150) address_line_2
1670 , substr(pens_loc.region_3,1,150) address_line_3
1671 , pens_loc.postal_code||' '||pens_loc.town_or_city address_line_4
1672 , pens_prov_info.ORG_INFORMATION3 Declaration_Due_Offset
1673 , pens_prov_info.ORG_INFORMATION4 Latest_Declaration_Offset
1674 , pens_prov_info.ORG_INFORMATION5 Last_Contribution_Offset
1675 , pens_prov_info.ORG_INFORMATION6 Payment_Date_Offset
1676 , pens_prov_info.ORG_INFORMATION7 Payment_1_Account
1677 , pens_prov_info.ORG_INFORMATION8 Payment_1_Type
1678 , pens_prov_info.ORG_INFORMATION9 Payment_1_Limit
1679 , pens_prov_info.ORG_INFORMATION10 Payment_2_Account
1680 , pens_prov_info.ORG_INFORMATION11 Payment_2_Type
1681 , pens_prov_info.ORG_INFORMATION12 Payment_2_Limit
1682 , pens_prov_info.ORG_INFORMATION13 Payment_3_Account
1683 , pens_prov_info.ORG_INFORMATION14 Payment_3_Type
1684 , pens_prov_info.ORG_INFORMATION15 Payment_3_Limit
1685 FROM hr_organization_information pens_prov_info
1686 , hr_all_organization_units pens_prov
1687 , hr_locations_all pens_loc
1688 WHERE pens_prov_info.organization_id = g_company_id
1689 and pens_prov_info.org_information_context = 'FR_COMP_PE_PRVS'
1690 and pens_prov_info.org_information1 = pens_prov.organization_id
1691 and pens_prov.location_id = pens_loc.location_id (+);
1692
1693 --
1694 -- Cursor to retrieve Bank Info
1695 --
1696 CURSOR cbank_info(l_org_method_id number) is
1697 SELECT ea.segment2 || ea.segment3 || replace(ea.segment5,'-','')
1698 FROM pay_org_payment_methods_f opm
1699 , pay_external_accounts ea
1700 WHERE opm.org_payment_method_id = l_org_method_id
1701 AND opm.external_account_id = ea.external_account_id
1702 AND g_effective_date between opm.effective_start_date
1703 and opm.effective_end_date;
1704
1705 --
1706 -- Cursor retrieve existing payroll archive records storing the payment options
1707 --
1708
1709 CURSOR cpayment
1710 (p_page_identifier number
1711 ,p_page_type varchar2) is
1712 SELECT org_information7 payment_1_account
1713 , org_information8 payment_1_type
1714 , org_information9 payment_1_limit
1715 , org_information10 payment_2_account
1716 , org_information11 payment_2_type
1717 , org_information12 payment_2_limit
1718 , org_information13 payment_3_account
1719 , org_information14 payment_3_type
1720 , org_information15 payment_3_limit
1721 , fnd_number.canonical_to_number(org_information16) advances
1722 , fnd_number.canonical_to_number(org_information17) regularisation
1723 FROM hr_organization_information
1724 WHERE organization_id = g_company_id
1725 and org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
1726 and org_information2 = p_page_identifier
1727 and org_information4 = p_page_type;
1728
1729
1730
1731 BEGIN
1732
1733 hr_utility.set_location('Entering ' || l_proc, 20);
1734 if g_payroll_action_id is null
1735 or g_payroll_action_id <> p_payroll_action_id
1736 then
1737 pay_fr_ducs.get_all_parameters
1738 (p_payroll_action_id => p_payroll_action_id
1739 ,p_business_group_id => g_business_group_id
1740 ,p_company_id => g_company_id
1741 ,p_period_type => g_period_type
1742 ,p_period_start_date => g_period_start_date
1743 ,p_effective_date => g_effective_date
1744 ,p_english_base => g_english_base
1745 ,p_english_rate => g_english_rate
1746 ,p_english_pay_value => g_english_pay_value
1747 ,p_english_contrib_code => g_english_contrib_code
1748 ,p_french_base => g_french_base
1749 ,p_french_rate => g_french_rate
1750 ,p_french_pay_value => g_french_pay_value
1751 ,p_french_contrib_code => g_french_contrib_code);
1752 g_payroll_action_id := p_payroll_action_id;
1753 END IF;
1754
1755
1756 l_year := to_char(g_effective_date,'YYYY');
1757 l_quarter := to_char(g_effective_date,'Q');
1758 l_month := replace(to_char(g_effective_date,'MONTH'),' ','');
1759 l_mm := to_char(g_effective_date,'MM');
1760 l_miq := to_char(to_number(l_mm)-(to_number(l_quarter)*3-2)+1);
1761
1762 l_date_to := to_char(g_effective_date,'DD/MM/YYYY');
1763
1764 IF g_period_type = 'CM' THEN
1765
1766 l_date_from := '01/'||to_char(g_effective_date,'MM/YYYY');
1767 l_period_description := l_month||' '||l_year;
1768
1769 l_period_code := substr(l_year,3,2)||l_quarter||l_miq;
1770
1771 ELSE
1772 l_date_from := '01/'||to_char(add_months(g_effective_date,-2), 'MM/YYYY');
1773 l_period_description := l_quarter||' '|| hr_general.decode_lookup('PROC_PERIOD_TYPE','Q')||' '||l_year;
1774 l_period_code := substr(l_year,3,2)||l_quarter||'0';
1775 END IF;
1776
1777 l_currency := 'euro';
1778 l_currency_code := 'EUR';
1779 l_currency_number := '9';
1780
1781 OPEN c_existing_archive(fnd_number.number_to_canonical(g_company_id));
1782 FETCH c_existing_archive INTO l_payroll_action_id;
1783 IF c_existing_archive%found THEN
1784
1785 RAISE duplicate;
1786
1787 END IF;
1788 CLOSE c_existing_archive;
1789
1790
1791
1792 DELETE FROM pay_action_information
1793 WHERE action_context_id = p_payroll_action_id
1794 and action_context_type = 'PA'
1795 and action_information_category IN
1796 ('FR_DUCS_COMP_INFO'
1797 ,'FR_DUCS_ESTAB_INFO'
1798 ,'FR_DUCS_PAGE_INFO'
1799 ,'FR_DUCS_REFERENCE_INFO'
1800 ,'FR_DUCS_CONTRIB_INFO');
1801
1802 -- Delete any payment override information from previous periods
1803
1804 DELETE FROM hr_organization_information
1805 WHERE organization_id = g_company_id
1806 AND org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
1807 AND org_information1 <> p_payroll_action_id;
1808
1809
1810
1811 FOR rec_company IN csr_company loop
1812
1813 pay_action_information_api.create_action_information (
1814 p_action_information_id => l_action_info_id
1815 , p_action_context_id => p_payroll_action_id
1816 , p_action_context_type => 'PA'
1817 , p_object_version_number => l_ovn
1818 , p_action_information_category => 'FR_DUCS_COMP_INFO'
1819 , p_action_information1 => fnd_number.number_to_canonical(
1820 g_company_id)
1821 , p_action_information2 => rec_company.company_name
1822 , p_action_information3 => rec_company.company_address_line_1
1823 , p_action_information4 => rec_company.company_address_line_2
1824 , p_action_information5 => rec_company.company_address_line_3
1825 , p_action_information6 => rec_company.company_address_line_4
1826 , p_action_information7 => rec_company.company_telephone
1827 , p_action_information8 => rec_company.company_fax
1828 , p_action_information9 => rec_company.rep_estab_SIRET
1829 , p_action_information10 => rec_company.rep_estab_NAF
1830 , p_action_information11 => null
1831 , p_action_information12 => null
1832 , p_action_information13 => rec_company.Activities_Ceased_Date
1833 , p_action_information14 => rec_company.No_Employees_Date
1834 , p_action_information15 => rec_company.Activities_Suspended
1835 , p_action_information16 => rec_company.Keep_Account_Open);
1836
1837
1838 -- Create a record in PAY_ACTION_INFORMATION --FR_DUCS_REFERENCE_INFO
1839
1840 pay_action_information_api.create_action_information (
1841 p_action_information_id => l_action_info_id
1842 , p_action_context_id => p_payroll_action_id
1843 , p_action_context_type => 'PA'
1844 , p_object_version_number => l_ovn
1845 , p_action_information_category => 'FR_DUCS_REFERENCE_INFO'
1846 , p_action_information1 => fnd_number.number_to_canonical(g_company_id)
1847 , p_action_information2 => l_period_code
1848 , p_action_information3 => l_date_from
1849 , p_action_information4 => l_date_to
1850 , p_action_information5 => l_period_description
1851 , p_action_information6 => l_currency
1852 , p_action_information7 => l_currency_code
1853 , p_action_information8 => l_currency_number
1854 , p_action_information9 => rec_company.administrator_line_1
1855 , p_action_information10 => rec_company.administrator_line_2
1856 , p_action_information11 => rec_company.administrator_telephone_number
1857 , p_action_information12 => rec_company.administrator_fax_number);
1858
1859 END LOOP;
1860
1861 ------
1862
1863
1864 FOR rec_estab IN csr_estab(fnd_number.number_to_canonical(g_company_id))
1865 LOOP
1866
1867 pay_action_information_api.create_action_information (
1868 p_action_information_id => l_action_info_id
1869 , p_action_context_id => p_payroll_action_id
1870 , p_action_context_type => 'PA'
1871 , p_object_version_number => l_ovn
1872 , p_action_information_category => 'FR_DUCS_ESTAB_INFO'
1873 , p_action_information1 => rec_estab.establishment_id
1874 , p_action_information2 => rec_estab.estab_SIRET
1875 , p_action_information3 => rec_estab.estab_NAF
1876 , p_action_information4 => rec_estab.Activities_Ceased_Date
1877 , p_action_information5 => rec_estab.No_Employees_Date
1878 , p_action_information6 => rec_estab.Activities_Suspended
1879 , p_action_information7 => rec_estab.Keep_Account_Open);
1880
1881 --
1882 -- Insert the Establishment Archive record
1883 --
1884
1885 -- Process URSSAF contributions
1886 pay_fr_ducs.process_contributions
1887 (p_payroll_action_id => p_payroll_action_id
1888 ,p_page_identifier => rec_estab.establishment_id
1889 ,p_page_type => 'URSSAF'
1890 ,p_total_contributions => l_total_contributions
1891 );
1892
1893
1894 -- Get existing payment options (from previous archive)
1895
1896 OPEN cpayment(p_page_identifier => rec_estab.establishment_id
1897 ,p_page_type => 'URSSAF');
1898
1899 FETCH cpayment INTO l_payment_1_account,
1900 l_payment_1_type,
1901 l_payment_1_limit,
1902 l_payment_2_account,
1903 l_payment_2_type,
1904 l_payment_2_limit,
1905 l_payment_3_account,
1906 l_payment_3_type,
1907 l_payment_3_limit,
1908 l_advances,
1909 l_regularization;
1910
1911 IF cpayment%notfound THEN
1912
1913 -- Initialise the Payment Options
1914 l_payment_1_account := rec_estab.urssaf_payment_1_account;
1915 l_payment_1_type := rec_estab.urssaf_payment_1_type;
1916 l_payment_1_limit := rec_estab.urssaf_payment_1_limit;
1917 l_payment_2_account := rec_estab.urssaf_payment_2_account;
1918 l_payment_2_type := rec_estab.urssaf_payment_2_type;
1919 l_payment_2_limit := rec_estab.urssaf_payment_2_limit;
1920 l_payment_3_account := rec_estab.urssaf_payment_3_account;
1921 l_payment_3_type := rec_estab.urssaf_payment_3_type;
1922 l_payment_3_limit := rec_estab.urssaf_payment_3_limit;
1923 l_advances := null;
1924 l_regularization := null;
1925
1926 hr_organization_api.create_org_information(
1927 p_effective_date => g_effective_date
1928 ,p_organization_id => g_company_id
1929 ,p_org_info_type_code => 'FR_COMP_PAYMENT_OVERRIDE'
1930 ,p_org_information1 => p_payroll_action_id
1931 ,p_org_information2 => rec_estab.establishment_id
1932 ,p_org_information3 => l_period_code
1933 ,p_org_information4 => 'URSSAF'
1934 ,p_org_information5 => null
1935 ,p_org_information6 => rec_estab.establishment_id
1936 ,p_org_information7 => l_payment_1_account
1937 ,p_org_information8 => l_payment_1_type
1938 ,p_org_information9 => l_payment_1_limit
1939 ,p_org_information10 => l_payment_2_account
1940 ,p_org_information11 => l_payment_2_type
1941 ,p_org_information12 => l_payment_2_limit
1942 ,p_org_information13 => l_payment_3_account
1943 ,p_org_information14 => l_payment_3_type
1944 ,p_org_information15 => l_payment_3_limit
1945 ,p_org_information_id => l_org_information_id
1946 ,p_object_version_number => l_object_version_number);
1947
1948 END IF;
1949
1950
1951 CLOSE cpayment;
1952
1953 l_advances := round(nvl(l_advances,0),2);
1954 l_regularization := round(nvl(l_regularization,0),2);
1955 l_total_payment := l_total_contributions + l_advances + l_regularization;
1956
1957
1958
1959 pay_fr_ducs.process_payment
1960 (p_name => rec_estab.assedic_name
1961 ,p_total_payment => l_total_payment
1962 ,p_payment1_type => l_payment_1_type
1963 ,p_payment1_limit => fnd_number.canonical_to_number(l_payment_1_limit)
1964 ,p_payment1_value => l_payment_1_val
1965 ,p_payment2_type => l_payment_2_type
1966 ,p_payment2_limit => fnd_number.canonical_to_number(l_payment_2_limit)
1967 ,p_payment2_value => l_payment_2_val
1968 ,p_payment3_type => l_payment_3_type
1969 ,p_payment3_limit => fnd_number.canonical_to_number(l_payment_3_limit)
1970 ,p_payment3_value => l_payment_3_val);
1971
1972
1973
1974 pay_fr_ducs.get_count_emps(p_payroll_action_id
1975 ,rec_estab.establishment_id
1976 ,'URSSAF'
1977 ,l_contribution_emps
1978 ,l_month_end_male
1979 ,l_month_end_female
1980 ,l_month_end_total
1981 ,l_total_actions);
1982
1983
1984
1985 l_Declaration_Due := g_effective_date
1986 + nvl(to_number(rec_estab.U_Declaration_Due_Offset),0);
1987 l_Latest_Declaration := g_effective_date + nvl(to_number(
1988 rec_estab.U_Latest_Declaration_Offset),0);
1989 l_Last_Contribution := g_effective_date + nvl(to_number(
1990 rec_estab.U_Last_Contribution_Offset),0);
1991 l_Payment_Date := g_effective_date
1992 + nvl(to_number(rec_estab.U_Payment_Date_Offset),0);
1993
1994 l_payment_1_acc_no := null;
1995 l_payment_2_acc_no := null;
1996 l_payment_3_acc_no := null;
1997 IF l_payment_1_account IS NOT NULL THEN
1998 OPEN cbank_info(l_payment_1_account);
1999 FETCH cbank_info INTO l_payment_1_acc_no;
2000 CLOSE cbank_info;
2001 END IF;
2002
2003 IF l_payment_2_account IS NOT NULL THEN
2004 OPEN cbank_info(l_payment_2_account);
2005 FETCH cbank_info INTO l_payment_2_acc_no;
2006 CLOSE cbank_info;
2007 END IF;
2008
2009 IF l_payment_3_account IS NOT NULL THEN
2010 OPEN cbank_info(l_payment_3_account);
2011 FETCH cbank_info INTO l_payment_3_acc_no;
2012 CLOSE cbank_info;
2013 END IF;
2014
2015
2016 pay_action_information_api.create_action_information (
2017 p_action_information_id => l_action_info_id
2018 ,p_action_context_id => p_payroll_action_id
2019 ,p_action_context_type => 'PA'
2020 ,p_object_version_number => l_ovn
2021 ,p_action_information_category => 'FR_DUCS_PAGE_INFO'
2022 ,p_action_information1 => rec_estab.establishment_id
2023 ,p_action_information2 => 'URSSAF'
2024 ,p_action_information3 => rec_estab.urssaf_id
2025 ,p_action_information4 => rec_estab.urssaf_name
2026 ,p_action_information5 => rec_estab.estab_urssaf_id
2027 ,p_action_information6 => null
2028 ,p_action_information7 => rec_estab.urssaf_address_line_1
2029 ,p_action_information8 => rec_estab.urssaf_address_line_2
2030 ,p_action_information9 => rec_estab.urssaf_address_line_3
2031 ,p_action_information10 => rec_estab.urssaf_address_line_4
2032 ,p_action_information11 => l_payment_1_acc_no
2033 ,p_action_information12 => fnd_number.number_to_canonical(l_payment_1_val)
2034 ,p_action_information13 => l_payment_2_acc_no
2035 ,p_action_information14 => fnd_number.number_to_canonical(l_payment_2_val)
2036 ,p_action_information15 => l_payment_3_acc_no
2037 ,p_action_information16 => fnd_number.number_to_canonical(l_payment_3_val)
2038 ,p_action_information17 => to_char(l_Declaration_Due,'dd/mm/yyyy')
2039 ,p_action_information18 => to_char(l_Latest_Declaration,'dd/mm/yyyy')
2040 ,p_action_information19 => to_char(l_Last_Contribution,'dd/mm/yyyy')
2041 ,p_action_information20 => to_char(l_Payment_Date,'dd/mm/yyyy')
2042 ,p_action_information21 => l_contribution_emps
2043 ,p_action_information22 => l_month_end_male
2044 ,p_action_information23 => l_month_end_female
2045 ,p_action_information24 => l_month_end_total
2046 ,p_action_information25 => fnd_number.number_to_canonical(
2047 l_total_contributions)
2048 ,p_action_information26 => fnd_number.number_to_canonical(l_advances)
2049 ,p_action_information27 => fnd_number.number_to_canonical(l_regularization)
2050 ,p_action_information28 => fnd_number.number_to_canonical(l_total_payment)
2051 ,p_action_information29 => l_total_actions);
2052
2053 -- AASEDIC
2054
2055 pay_fr_ducs.process_contributions
2056 (p_payroll_action_id => p_payroll_action_id
2057 ,p_page_identifier => rec_estab.establishment_id
2058 ,p_page_type => 'ASSEDIC'
2059 ,p_total_contributions => l_total_contributions
2060 );
2061
2062
2063 -- Get existing payment options (from previous archive)
2064 OPEN cpayment(p_page_identifier => rec_estab.establishment_id
2065 ,p_page_type => 'ASSEDIC');
2066
2067 FETCH cpayment INTO l_payment_1_account,
2068 l_payment_1_type,
2069 l_payment_1_limit,
2070 l_payment_2_account,
2071 l_payment_2_type,
2072 l_payment_2_limit,
2073 l_payment_3_account,
2074 l_payment_3_type,
2075 l_payment_3_limit,
2076 l_advances,
2077 l_regularization;
2078 IF cpayment%notfound THEN
2079
2080 -- Initialise the Payment Options
2081 l_payment_1_account := rec_estab.assedic_payment_1_account;
2082 l_payment_1_type := rec_estab.assedic_payment_1_type;
2083 l_payment_1_limit := rec_estab.assedic_payment_1_limit;
2084 l_payment_2_account := rec_estab.assedic_payment_2_account;
2085 l_payment_2_type := rec_estab.assedic_payment_2_type;
2086 l_payment_2_limit := rec_estab.assedic_payment_2_limit;
2087 l_payment_3_account := rec_estab.assedic_payment_3_account;
2088 l_payment_3_type := rec_estab.assedic_payment_3_type;
2089 l_payment_3_limit := rec_estab.assedic_payment_3_limit;
2090 l_advances := null;
2091 l_regularization := null;
2092
2093 hr_organization_api.create_org_information (
2094 p_effective_date => g_effective_date
2095 ,p_organization_id => g_company_id
2096 ,p_org_info_type_code => 'FR_COMP_PAYMENT_OVERRIDE'
2097 ,p_org_information1 => p_payroll_action_id
2098 ,p_org_information2 => rec_estab.establishment_id
2099 ,p_org_information3 => l_period_code
2100 ,p_org_information4 => 'ASSEDIC'
2101 ,p_org_information5 => null
2102 ,p_org_information6 => rec_estab.establishment_id
2103 ,p_org_information7 => l_payment_1_account
2104 ,p_org_information8 => l_payment_1_type
2105 ,p_org_information9 => l_payment_1_limit
2106 ,p_org_information10 => l_payment_2_account
2107 ,p_org_information11 => l_payment_2_type
2108 ,p_org_information12 => l_payment_2_limit
2109 ,p_org_information13 => l_payment_3_account
2110 ,p_org_information14 => l_payment_3_type
2111 ,p_org_information15 => l_payment_3_limit
2112 ,p_org_information_id => l_org_information_id
2113 ,p_object_version_number => l_object_version_number);
2114
2115
2116 END IF;
2117
2118 CLOSE cpayment;
2119
2120 --
2121 -- Determine how ASSEDIC payments are to be split across bank accounts
2122 --
2123 l_advances := round(nvl(l_advances,0),2);
2124 l_regularization := round(nvl(l_regularization,0),2);
2125 l_total_payment := l_total_contributions + l_advances + l_regularization;
2126
2127 pay_fr_ducs.process_payment
2128 (p_name => rec_estab.assedic_name
2129 ,p_total_payment => l_total_payment
2130 ,p_payment1_type => l_payment_1_type
2131 ,p_payment1_limit => fnd_number.canonical_to_number(l_payment_1_limit)
2132 ,p_payment1_value => l_payment_1_val
2133 ,p_payment2_type => l_payment_2_type
2134 ,p_payment2_limit => fnd_number.canonical_to_number(l_payment_2_limit)
2135 ,p_payment2_value => l_payment_2_val
2136 ,p_payment3_type => l_payment_3_type
2137 ,p_payment3_limit => fnd_number.canonical_to_number(l_payment_3_limit)
2138 ,p_payment3_value => l_payment_3_val);
2139
2140
2141
2142 pay_fr_ducs.get_count_emps(p_payroll_action_id
2143 ,rec_estab.establishment_id
2144 ,'ASSEDIC'
2145 ,l_contribution_emps
2146 ,l_month_end_male
2147 ,l_month_end_female
2148 ,l_month_end_total
2149 ,l_total_actions);
2150
2151 l_Declaration_Due := g_effective_date
2152 + nvl(to_number(rec_estab.A_Declaration_Due_Offset),0);
2153 l_Latest_Declaration := g_effective_date + nvl(to_number(
2154 rec_estab.A_Latest_Declaration_Offset),0);
2155 l_Last_Contribution := g_effective_date + nvl(to_number(
2156 rec_estab.A_Last_Contribution_Offset),0);
2157 l_Payment_Date := g_effective_date
2158 + nvl(to_number(rec_estab.A_Payment_Date_Offset),0);
2159
2160 l_payment_1_acc_no := null;
2161 l_payment_2_acc_no := null;
2162 l_payment_3_acc_no := null;
2163 IF l_payment_1_account IS NOT NULL THEN
2164 OPEN cbank_info(l_payment_1_account);
2165 FETCH cbank_info INTO l_payment_1_acc_no;
2166 CLOSE cbank_info;
2167 END IF;
2168
2169 IF l_payment_2_account IS NOT NULL THEN
2170 OPEN cbank_info(l_payment_2_account);
2171 FETCH cbank_info INTO l_payment_2_acc_no;
2172 CLOSE cbank_info;
2173 END IF;
2174
2175 IF l_payment_3_account IS NOT NULL THEN
2176 OPEN cbank_info(l_payment_3_account);
2177 FETCH cbank_info INTO l_payment_3_acc_no;
2178 CLOSE cbank_info;
2179 END IF;
2180
2181
2182 pay_action_information_api.create_action_information (
2183 p_action_information_id => l_action_info_id
2184 ,p_action_context_id => p_payroll_action_id
2185 ,p_action_context_type => 'PA'
2186 ,p_object_version_number => l_ovn
2187 ,p_action_information_category => 'FR_DUCS_PAGE_INFO'
2188 ,p_action_information1 => rec_estab.establishment_id
2189 ,p_action_information2 => 'ASSEDIC'
2190 ,p_action_information3 => rec_estab.estab_ASSEDIC_id
2191 ,p_action_information4 => rec_estab.ASSEDIC_name
2192 ,p_action_information5 => rec_estab.estab_ASSEDIC_id
2193 ,p_action_information6 => null
2194 ,p_action_information7 => rec_estab.assedic_address_line_1
2195 ,p_action_information8 => rec_estab.assedic_address_line_2
2196 ,p_action_information9 => rec_estab.assedic_address_line_3
2197 ,p_action_information10 => rec_estab.assedic_address_line_4
2198 ,p_action_information11 => l_payment_1_acc_no
2199 ,p_action_information12 => fnd_number.number_to_canonical(l_payment_1_val)
2200 ,p_action_information13 => l_payment_2_acc_no
2201 ,p_action_information14 => fnd_number.number_to_canonical(l_payment_2_val)
2202 ,p_action_information15 => l_payment_3_acc_no
2203 ,p_action_information16 => fnd_number.number_to_canonical(l_payment_3_val)
2204 ,p_action_information17 => to_char(l_Declaration_Due,'dd/mm/yyyy')
2205 ,p_action_information18 => to_char(l_Latest_Declaration,'dd/mm/yyyy')
2206 ,p_action_information19 => to_char(l_Last_Contribution,'dd/mm/yyyy')
2207 ,p_action_information20 => to_char(l_Payment_Date,'dd/mm/yyyy')
2208 ,p_action_information21 => l_contribution_emps
2209 ,p_action_information22 => l_month_end_male
2210 ,p_action_information23 => l_month_end_female
2211 ,p_action_information24 => l_month_end_total
2212 ,p_action_information25 => fnd_number.number_to_canonical(
2213 l_total_contributions)
2214 ,p_action_information26 => fnd_number.number_to_canonical(l_advances)
2215 ,p_action_information27 => fnd_number.number_to_canonical(l_regularization)
2216 ,p_action_information28 => fnd_number.number_to_canonical(l_total_payment)
2217 ,p_action_information29 => l_total_actions);
2218
2219 END LOOP;
2220
2221 --Pension
2222
2223 FOR rec_pens IN csr_comp_pension_prvs LOOP
2224
2225
2226 -- Process PENSION contributions
2227 pay_fr_ducs.process_contributions
2228 (p_payroll_action_id => p_payroll_action_id
2229 ,p_page_identifier => rec_pens.comp_pens_prov_id
2230 ,p_page_type => 'PENSION'
2231 ,p_total_contributions => l_total_contributions
2232 );
2233
2234 -- Get existing payment options (from previous archive)
2235 OPEN cpayment(p_page_identifier => rec_pens.comp_pens_prov_id
2236 ,p_page_type => 'PENSION');
2237
2238 FETCH cpayment INTO l_payment_1_account,
2239 l_payment_1_type,
2240 l_payment_1_limit,
2241 l_payment_2_account,
2242 l_payment_2_type,
2243 l_payment_2_limit,
2244 l_payment_3_account,
2245 l_payment_3_type,
2246 l_payment_3_limit,
2247 l_advances,
2248 l_regularization;
2249
2250 IF cpayment%notfound THEN
2251
2252 -- Initialise the Payment Options
2253 l_payment_1_account := rec_pens.payment_1_account;
2254 l_payment_1_type := rec_pens.payment_1_type;
2255 l_payment_1_limit := rec_pens.payment_1_limit;
2256 l_payment_2_account := rec_pens.payment_2_account;
2257 l_payment_2_type := rec_pens.payment_2_type;
2258 l_payment_2_limit := rec_pens.payment_2_limit;
2259 l_payment_3_account := rec_pens.payment_3_account;
2260 l_payment_3_type := rec_pens.payment_3_type;
2261 l_payment_3_limit := rec_pens.payment_3_limit;
2262 l_advances := null;
2263 l_regularization := null;
2264
2265 hr_organization_api.create_org_information (
2266 p_effective_date => g_effective_date
2267 ,p_organization_id => g_company_id
2268 ,p_org_info_type_code => 'FR_COMP_PAYMENT_OVERRIDE'
2269 ,p_org_information1 => p_payroll_action_id
2270 ,p_org_information2 => rec_pens.comp_pens_prov_id
2271 ,p_org_information3 => l_period_code
2272 ,p_org_information4 => 'PENSION'
2273 ,p_org_information5 => rec_pens.pens_prov_id
2274 ,p_org_information6 => null
2275 ,p_org_information7 => l_payment_1_account
2276 ,p_org_information8 => l_payment_1_type
2277 ,p_org_information9 => l_payment_1_limit
2278 ,p_org_information10 => l_payment_2_account
2279 ,p_org_information11 => l_payment_2_type
2280 ,p_org_information12 => l_payment_2_limit
2281 ,p_org_information13 => l_payment_3_account
2282 ,p_org_information14 => l_payment_3_type
2283 ,p_org_information15 => l_payment_3_limit
2284 ,p_org_information_id => l_org_information_id
2285 ,p_object_version_number => l_object_version_number);
2286
2287 END IF;
2288
2289 CLOSE cpayment;
2290
2291 l_advances := round(nvl(l_advances,0),2);
2292 l_regularization := round(nvl(l_regularization,0),2);
2293
2294 l_total_payment := l_total_contributions + l_advances + l_regularization;
2295
2296 pay_fr_ducs.process_payment
2297 (p_name => rec_pens.name
2298 ,p_total_payment => l_total_payment
2299 ,p_payment1_type => l_payment_1_type
2300 ,p_payment1_limit => fnd_number.canonical_to_number(l_payment_1_limit)
2301 ,p_payment1_value => l_payment_1_val
2302 ,p_payment2_type => l_payment_2_type
2303 ,p_payment2_limit => fnd_number.canonical_to_number(l_payment_2_limit)
2304 ,p_payment2_value => l_payment_2_val
2305 ,p_payment3_type => l_payment_3_type
2306 ,p_payment3_limit => fnd_number.canonical_to_number(l_payment_3_limit)
2307 ,p_payment3_value => l_payment_3_val);
2308
2309 pay_fr_ducs.get_count_emps(p_payroll_action_id
2310 ,rec_pens.comp_pens_prov_id
2311 ,'PENSION'
2312 ,l_contribution_emps
2313 ,l_month_end_male
2314 ,l_month_end_female
2315 ,l_month_end_total
2316 ,l_total_actions);
2317
2318 l_Declaration_Due := g_effective_date
2319 + nvl(to_number(rec_pens.Declaration_Due_Offset),0);
2320 l_Latest_Declaration := g_effective_date
2321 + nvl(to_number(rec_pens.Latest_Declaration_Offset),0);
2322 l_Last_Contribution := g_effective_date
2323 + nvl(to_number(rec_pens.Last_Contribution_Offset),0);
2324 l_Payment_Date := g_effective_date
2325 + nvl(to_number(rec_pens.Payment_Date_Offset),0);
2326
2327 l_payment_1_acc_no := null;
2328 l_payment_2_acc_no := null;
2329 l_payment_3_acc_no := null;
2330 IF l_payment_1_account IS NOT NULL THEN
2331 OPEN cbank_info(l_payment_1_account);
2332 FETCH cbank_info INTO l_payment_1_acc_no;
2333 CLOSE cbank_info;
2334 END IF;
2335
2336 IF l_payment_2_account IS NOT NULL THEN
2337 OPEN cbank_info(l_payment_2_account);
2338 FETCH cbank_info INTO l_payment_2_acc_no;
2339 CLOSE cbank_info;
2340 END IF;
2341
2342 IF l_payment_3_account IS NOT NULL THEN
2343 OPEN cbank_info(l_payment_3_account);
2344 FETCH cbank_info INTO l_payment_3_acc_no;
2345 CLOSE cbank_info;
2346 END IF;
2347
2348 pay_action_information_api.create_action_information (
2349 p_action_information_id => l_action_info_id
2350 ,p_action_context_id => p_payroll_action_id
2351 ,p_action_context_type => 'PA'
2352 ,p_object_version_number => l_ovn
2353 ,p_action_information_category => 'FR_DUCS_PAGE_INFO'
2354 ,p_action_information1 => rec_pens.comp_pens_prov_id
2355 ,p_action_information2 => 'PENSION'
2356 ,p_action_information3 => rec_pens.pens_prov_id
2357 ,p_action_information4 => rec_pens.name
2358 ,p_action_information5 => null
2359 ,p_action_information6 => null
2360 ,p_action_information7 => rec_pens.address_line_1
2361 ,p_action_information8 => rec_pens.address_line_2
2362 ,p_action_information9 => rec_pens.address_line_3
2363 ,p_action_information10 => rec_pens.address_line_4
2364 ,p_action_information11 => l_payment_1_acc_no
2365 ,p_action_information12 => fnd_number.number_to_canonical(l_payment_1_val)
2366 ,p_action_information13 => l_payment_2_acc_no
2367 ,p_action_information14 => fnd_number.number_to_canonical(l_payment_2_val)
2368 ,p_action_information15 => l_payment_3_acc_no
2369 ,p_action_information16 => fnd_number.number_to_canonical(l_payment_3_val)
2370 ,p_action_information17 => to_char(l_Declaration_Due,'dd/mm/yyyy')
2371 ,p_action_information18 => to_char(l_Latest_Declaration,'dd/mm/yyyy')
2372 ,p_action_information19 => to_char(l_Last_Contribution,'dd/mm/yyyy')
2373 ,p_action_information20 => to_char(l_Payment_Date,'dd/mm/yyyy')
2374 ,p_action_information21 => l_contribution_emps
2375 ,p_action_information22 => l_month_end_male
2376 ,p_action_information23 => l_month_end_female
2377 ,p_action_information24 => l_month_end_total
2378 ,p_action_information25 => fnd_number.number_to_canonical(
2379 l_total_contributions)
2380 ,p_action_information26 => fnd_number.number_to_canonical(l_advances)
2381 ,p_action_information27 => fnd_number.number_to_canonical(l_regularization)
2382 ,p_action_information28 => fnd_number.number_to_canonical(l_total_payment)
2383 ,p_action_information29 => l_total_actions);
2384
2385
2386 END LOOP; -- End of Pension Loop
2387
2388
2389
2390 --
2391 hr_utility.set_location('Leaving ' || l_proc, 100);
2392
2393 EXCEPTION
2394
2395 WHEN duplicate THEN
2396 hr_utility.set_location('Leaving with duplicate exception' || l_proc, 100);
2397 WHEN others THEN
2398 --3655620 write any other errors to the log file as otherwise the message
2399 --text is not propagated back to the CM and only appears in the log if
2400 --the LOGGING action parameter includes the letter G
2401 FND_FILE.NEW_LINE(fnd_file.log, 1);
2402 FND_FILE.PUT_LINE(fnd_file.log,substrb(SQLERRM,1,1023));
2403 raise;
2404 END deinitialize_code;
2405 --
2406
2407 -------------------------------------------------------------------------------
2408 -- PROCEDURE recalculate_payment
2409 -------------------------------------------------------------------------------
2410
2411 PROCEDURE recalculate_payment(
2412 errbuf out nocopy varchar2
2413 ,retcode out nocopy varchar2
2414 ,p_company_id in number
2415 ,p_period_end_date in varchar2
2416 ,p_period_type in varchar2
2417 ,p_override_information_id in number default null) IS
2418
2419 l_proc varchar2(60) := g_package||' recalculate_payment ';
2420 -- Local variables
2421 l_error_flag varchar2(2):='N';
2422 l_final_error_flag varchar2(2):='N';
2423 l_total_payment number;
2424 l_advances number;
2425 l_regularisation number;
2426 l_payment_1_val number;
2427 l_payment_2_val number;
2428 l_payment_3_val number;
2429
2430 l_period_end_date date;
2431
2432 l_payment_1_acc_no varchar2(60);
2433 l_payment_2_acc_no varchar2(60);
2434 l_payment_3_acc_no varchar2(60);
2435
2436
2437 -- Cursor definitions
2438
2439 CURSOR cpayment_option IS
2440 SELECT payment.org_information1 payroll_action_id
2441 , payment.org_information2 page_identifier
2442 , payment.org_information4 page_type
2443 , payment.org_information7 payment_1_account
2444 , payment.org_information8 payment_1_type
2445 , payment.org_information9 payment_1_limit
2446 , payment.org_information10 payment_2_account
2447 , payment.org_information11 payment_2_type
2448 , payment.org_information12 payment_2_limit
2449 , payment.org_information13 payment_3_account
2450 , payment.org_information14 payment_3_type
2451 , payment.org_information15 payment_3_limit
2452 , payment.org_information16 advances
2453 , payment.org_information17 regularisation
2454 , page.action_information_id
2455 , page.object_version_number
2456 , page.action_information4 organization_name
2457 , page.action_information25 total_contributions
2458 FROM hr_organization_information payment
2459 , pay_action_information page
2460 WHERE payment.organization_id = p_company_id
2461 and payment.org_information3 =
2462 to_char(l_period_end_date,'YY') ||
2463 to_char(l_period_end_date,'Q') ||
2464 decode(p_period_type,'CM',
2465 to_char(to_number(to_char(l_period_end_date,'MM'))
2466 -(to_number(to_char(l_period_end_date,'Q'))*3-2)+1)
2467 ,'0')
2468 and payment.org_information_context = 'FR_COMP_PAYMENT_OVERRIDE'
2469 and payment.org_information_id =
2470 nvl(p_override_information_id, payment.org_information_id)
2471 and payment.org_information2 = page.action_information1
2472 and payment.org_information1 = page.action_context_id
2473 and page.action_context_type = 'PA'
2474 and page.action_information_category = 'FR_DUCS_PAGE_INFO'
2475 and page.action_information1 = payment.org_information2
2476 and page.action_information2 = payment.org_information4;
2477
2478 --
2479 -- Cursor to retrieve Bank Info
2480 --
2481 CURSOR cbank_info(l_org_method_id number) is
2482 SELECT ea.segment2 || ea.segment3 || replace(ea.segment5,'-','')
2483 FROM pay_org_payment_methods_f opm
2484 , pay_external_accounts ea
2485 WHERE opm.org_payment_method_id = l_org_method_id
2486 AND opm.external_account_id = ea.external_account_id
2487 AND l_period_end_date between opm.effective_start_date
2488 and opm.effective_end_date;
2489
2490
2491
2492 BEGIN
2493
2494 hr_utility.set_location('Entering ' || l_proc,10);
2495
2496 l_period_end_date := fnd_date.string_to_date(p_period_end_date,'YYYY/MM/DD HH24:MI:SS');
2497
2498 FOR payment IN cpayment_option LOOP
2499
2500
2501 -- Validation checks on data entered
2502 l_error_flag:='N';
2503
2504 IF (payment.payment_1_type IN ('AMOUNT','PERCENT')
2505 AND payment.payment_1_limit IS NULL)
2506 OR (payment.payment_2_type IN ('AMOUNT','PERCENT')
2507 AND payment.payment_2_limit IS NULL)
2508 OR (payment.payment_3_type IN ('AMOUNT','PERCENT')
2509 AND payment.payment_3_limit IS NULL) THEN
2510 l_error_flag:='Y';
2511 hr_utility.set_message(801, 'PAY_75089_DUCS_NULL_ACC_LIMIT');
2512 hr_utility.set_message_token(801,'ORGANIZATION',payment.organization_name);
2513 FND_FILE.NEW_LINE(fnd_file.log, 1);
2514 FND_FILE.PUT_LINE(fnd_file.log,hr_utility.get_message);
2515 END IF;
2516
2517 IF l_error_flag ='Y' THEN
2518
2519 l_final_error_flag:='Y';
2520
2521 ELSE
2522
2523 -- Set total payment using the update advances and regularisation values
2524
2525 l_advances:=round(nvl(fnd_number.canonical_to_number(payment.advances),0),2);
2526 l_regularisation:=round(nvl(fnd_number.canonical_to_number(
2527 payment.regularisation),0),2);
2528 l_total_payment :=
2529 round(fnd_number.canonical_to_number(payment.total_contributions),2) +
2530 l_advances + l_regularisation;
2531
2532 pay_fr_ducs.process_payment
2533 (p_name => payment.organization_name
2534 ,p_total_payment => l_total_payment
2535 ,p_payment1_type => payment.payment_1_type
2536 ,p_payment1_limit => fnd_number.canonical_to_number(payment.payment_1_limit)
2537 ,p_payment1_value => l_payment_1_val
2538 ,p_payment2_type => payment.payment_2_type
2539 ,p_payment2_limit => fnd_number.canonical_to_number(payment.payment_2_limit)
2540 ,p_payment2_value => l_payment_2_val
2541 ,p_payment3_type => payment.payment_3_type
2542 ,p_payment3_limit => fnd_number.canonical_to_number(payment.payment_3_limit)
2543 ,p_payment3_value => l_payment_3_val);
2544
2545
2546 l_payment_1_acc_no := null;
2547 l_payment_2_acc_no := null;
2548 l_payment_3_acc_no := null;
2549 IF payment.payment_1_account IS NOT NULL THEN
2550 OPEN cbank_info(payment.payment_1_account);
2551 FETCH cbank_info INTO l_payment_1_acc_no;
2552 CLOSE cbank_info;
2553 END IF;
2554
2555 IF payment.payment_2_account IS NOT NULL THEN
2556 OPEN cbank_info(payment.payment_2_account);
2557 FETCH cbank_info INTO l_payment_2_acc_no;
2558 CLOSE cbank_info;
2559 END IF;
2560
2561 IF payment.payment_3_account IS NOT NULL THEN
2562 OPEN cbank_info(payment.payment_3_account);
2563 FETCH cbank_info INTO l_payment_3_acc_no;
2564 CLOSE cbank_info;
2565 END IF;
2566
2567
2568 -- Update FR_DUCS_PAGE_INFO record using the update API.
2569
2570
2571 pay_action_information_api.update_action_information(
2572 p_action_information_id => payment.action_information_id
2573 ,p_object_version_number => payment.object_version_number
2574 ,p_action_information11 => l_payment_1_acc_no
2575 ,p_action_information12 => fnd_number.number_to_canonical(l_payment_1_val)
2576 ,p_action_information13 => l_payment_2_acc_no
2577 ,p_action_information14 => fnd_number.number_to_canonical(l_payment_2_val)
2578 ,p_action_information15 => l_payment_3_acc_no
2579 ,p_action_information16 => fnd_number.number_to_canonical(l_payment_3_val)
2580 ,p_action_information26 => fnd_number.number_to_canonical(l_advances)
2581 ,p_action_information27 => fnd_number.number_to_canonical(l_regularisation)
2582 ,p_action_information28 => fnd_number.number_to_canonical(l_total_payment));
2583
2584 END IF;
2585
2586 END LOOP;
2587
2588 IF l_final_error_flag = 'Y' THEN
2589 retcode := 1;
2590 END IF;
2591
2592
2593 hr_utility.set_location('Leaving ' || l_proc, 100);
2594
2595 END recalculate_payment;
2596 --
2597
2598 END PAY_FR_DUCS; -- End of package