[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_AWCAP_ARCHIVE
Source
1 package body pay_sg_awcap_archive as
2 /* $Header: pysgawcp.pkb 120.11 2012/01/19 11:25:23 rpahune noship $ */
3 -------------------------------------------------------------------
4 -- Package Level Global Variables
5 -------------------------------------------------------------------
6 l_package varchar2(100) ;
7 g_debug boolean;
8 ----------------------------------------------------------------------
9 -- Record with payroll action details populated in Initialization_code
10 ----------------------------------------------------------------------
11 type t_pact is record (
12 report_type pay_payroll_actions.report_type%TYPE,
13 report_qualifier pay_payroll_actions.report_qualifier%TYPE,
14 report_category pay_payroll_actions.report_category%TYPE,
15 business_group_id number,
16 effective_date date,
17 retain_archive_data char(1),
18 person_id per_people_f.person_id%type,
19 basis_year varchar2(4),
20 legal_entity_id number ,
21 Start_date date ,
22 End_date date );
23 --
24 g_pact t_pact;
25 --
26 ----------------------------------------------------------------------
27 --Table to store employee details
28 ----------------------------------------------------------------------
29 type emp_details_store_rec is record (
30 employee_name per_all_people_f.per_information1%type ,
31 employee_number per_all_people_f.employee_number%type ,
32 cpf_number per_all_people_f.per_information14%type,
33 person_id per_all_people_f.person_id%type,
34 telephone_number per_addresses.telephone_number_1%type) ;
35 --
36 emp_details_rec emp_details_store_rec;
37 --
38 -----------------------------------------------------
39 -- Table to store Defined Balance details
40 -----------------------------------------------------
41 type t_def_bal_tbl is table of pay_defined_balances.defined_balance_id%type;
42 g_ytd_def_bal_tbl t_def_bal_tbl;
43 g_mtd_def_bal_tbl t_def_bal_tbl;
44 --
45 type t_bal_name_tbl is table of pay_balance_types.balance_name%type;
46 g_bal_name_tbl t_bal_name_tbl;
47 -------------------------------------------------------------------
48 -- YTD balances Archival Variables
49 -------------------------------------------------------------------
50 type ytd_balance_store_rec is record ( balance_name varchar2(60),
51 balance_value number );
52 type ytd_balance_tab is table of ytd_balance_store_rec index by binary_integer;
53 ytd_balance_rec ytd_balance_tab;
54 -------------------------------------------------------------------
55 -- MTD balances Archival Variables
56 -------------------------------------------------------------------
57 type mtd_balance_store_rec is record ( date_earned varchar2(20),
58 balance_value number );
59 type mtd_balance_tab is table of mtd_balance_store_rec index by binary_integer;
60 mtd_balance_rec mtd_balance_tab;
61 --------------------------------------------------------------------
62 -- These are PUBLIC procedures are required by the Archive process.
63 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
64 -- the archive process knows what code to execute for each step of
65 -- the archive.
66 --------------------------------------------------------------------
67 procedure range_code
68 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
69 p_sql out nocopy varchar2)
70 is
71 l_proc_name varchar2(100);
72 c_range_cursor constant varchar2(3000) :=
73 ' select distinct pap.person_id
74 from pay_payroll_actions ppa,
75 per_people_f pap
76 where ppa.payroll_action_id = :payroll_action_id
77 and pap.business_group_id = ppa.business_group_id
78 order by pap.person_id ' ;
79 begin
80 l_proc_name := l_package || 'range_code';
81 if g_debug then
82 hr_utility.set_location(l_proc_name||' Start of procedure',10);
83 end if;
84 p_sql := c_range_cursor ;
85 if g_debug then
86 hr_utility.set_location(l_proc_name||' End of procedure',20);
87 end if;
88 end range_code ;
89 ------------------------------------------------------------
90 -- Assignment Action Code
91 ------------------------------------------------------------
92 procedure assignment_action_code
93 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
94 p_start_person_id in per_all_people_f.person_id%type,
95 p_end_person_id in per_all_people_f.person_id%type,
96 p_chunk in number )
97 is
98 l_proc_name varchar2(100) ;
99 l_next_action_id pay_assignment_actions.assignment_action_id%type;
100 --
101 cursor c_assact
102 is
103 select max(paa.assignment_id) assignment_id
104 from pay_payroll_actions rppa,
105 pay_assignment_actions rpac,
106 per_assignments_f paa
107 where paa.person_id between p_start_person_id
108 and p_end_person_id
109 and rppa.business_group_id = g_pact.business_group_id
110 and rppa.effective_date between g_pact.start_date
111 and g_pact.end_date
112 and rppa.action_type in ('R','B','I','Q','V')
113 and rpac.action_status = 'C'
114 and rppa.payroll_action_id = rpac.payroll_action_id
115 and rpac.tax_unit_id = g_pact.legal_entity_id
116 and rpac.assignment_id = paa.assignment_id
117 and rppa.effective_date between paa.effective_start_date
118 and paa.effective_end_date
119 and paa.person_id + 0 = nvl(g_pact.person_id,paa.person_id)
120 group by paa.person_id;
121 --
122 cursor next_action_id
123 is
124 select pay_assignment_actions_s.nextval
125 from dual;
126 --
127 begin
128 l_proc_name := l_package || 'assignment_action_code';
129 pay_sg_awcap_archive.initialization_code(p_payroll_action_id) ;
130 --
131 if g_debug then
132 hr_utility.set_location(l_proc_name || ' Start of assignment_action_code',30);
133 end if;
134 --
135 for i in c_assact
136 loop
137 open next_action_id;
138 fetch next_action_id into l_next_action_id;
139 close next_action_id;
140 --
141 if g_debug then
142 hr_utility.set_location(l_proc_name|| ' Before calling hr_nonrun_asact.insact',10);
143 end if;
144 --
145 hr_nonrun_asact.insact( l_next_action_id,
146 i.assignment_id,
147 p_payroll_action_id,
148 p_chunk,
149 g_pact.legal_entity_id);
150 --
151 if g_debug then
152 hr_utility.set_location(l_proc_name||' After calling hr_nonrun_asact.insact',20);
153 end if;
154 --
155 end loop;
156 --
157 if g_debug then
158 hr_utility.set_location(l_proc_name|| ' End of assignment_action_code',30);
159 end if;
160 exception
161 when others then
162 if g_debug then
163 hr_utility.set_location(l_proc_name||' Error raised in assignment_action_code procedure',40);
164 end if;
165 raise;
166 end assignment_action_code ;
167 --
168 ------------------------------------------------------------
169 -- Initialization Code
170 ------------------------------------------------------------
171 --
172 procedure initialization_code
173 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
174 is
175 l_proc_name varchar2(100) ;
176 begin
177 l_proc_name := l_package || 'initialization_code';
178
179 g_debug := hr_utility.debug_enabled;
180 --
181 if g_debug then
182 hr_utility.set_location(l_proc_name||' Start of procedure',10);
183 end if;
184 --
185 if g_pact.report_type is null then
186 select ppa.report_type,
187 ppa.report_qualifier,
188 ppa.report_category,
189 ppa.business_group_id,
190 ppa.effective_date,
191 pay_core_utils.get_parameter('RETAIN_ARCHIVE_DATA',legislative_parameters),
192 pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
193 to_number(pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters)),
194 to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters)) legal_entity_id,
195 to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
196 to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY')
197 into g_pact
198 from pay_payroll_actions ppa
199 where ppa.payroll_action_id = p_payroll_action_id;
200 end if ;
201 --
202 if g_debug then
203 hr_utility.set_location(l_proc_name||' End of procedure',20);
204 end if;
205 exception
206 when others then
207 if g_debug then
208 hr_utility.set_location(l_proc_name||' Error in procedure',100);
209 end if;
210 raise;
211 end initialization_code;
212 ------------------------------------------------------------
213 -- Archive Code
214 ------------------------------------------------------------
215 procedure archive_code
216 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
217 p_effective_date in date)
218 is
219
220 l_proc_name varchar2(100) ;
221 --
222 l_assignment_id per_all_assignments_f.assignment_id%type;
223 l_payroll_id pay_payroll_actions.payroll_action_id%type ;
224 --------------------------
225 -- Tables for pay_balance_pkg
226 --------------------------
227 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
228 g_context_tab pay_balance_pkg.t_context_tab;
229 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
230 --
231 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
232 l_person_id per_all_people_f.person_id%type;
233 l_cpf_tot_earn_cap_amt ff_globals_f.global_value%type;
234 l_aw_toward_cap number;
235 l_aw_cap_recalculated number;
236 l_master_block char(1);
237 l_over_paid_flag char(1);
238 l_date_earned date;
239 --
240 cursor c_get_details
241 is
242 select pac.assignment_id,
243 pps.person_id,
244 pps.final_process_date
245 from pay_assignment_actions pac,
246 per_assignments_f paa,
247 per_periods_of_service pps
248 where pac.assignment_action_id = p_assignment_action_id
249 and paa.assignment_id = pac.assignment_id
250 and pps.person_id = paa.person_id
251 order by pps.date_start desc;
252 --
253 cursor c_employee_details
254 (c_person_id per_people_f.person_id%type)
255 is
256 select substr(pap.per_information1,1,50) legal_name, --Legal Name
257 pap.employee_number employee_number, --Employee Number
258 nvl(pap.per_information14,pap.national_identifier) cpf_number, --CPF Number/National Identifier
259 pap.person_id person_id, --Person ID
260 nvl(addr.telephone_number_1,nvl(addr.telephone_number_2,addr.telephone_number_3)) telephone_number
261 from per_people_f pap,
262 per_addresses addr
263 where pap.person_id = c_person_id
264 and addr.person_id (+)= pap.person_id
265 and addr.primary_flag (+)= 'Y'
266 and pap.effective_start_date = (
267 select max(people1.effective_start_date)
268 from per_people_f people1
269 where people1.person_id = pap.person_id);
270 --
271 cursor month_year_action_sequence ( c_person_id per_all_people_f.person_id%type,
272 c_business_group_id hr_organization_units.business_group_id%type,
273 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
274 c_basis_year varchar2 )
275 is
276 select /*+ ORDERED USE_NL(pacmax) */
277 max(pacmax.action_sequence) act_seq,
278 to_char(ppamax.effective_date,'MM')
279 from per_assignments_f paamax,
280 pay_assignment_actions pacmax,
281 pay_payroll_actions ppamax
282 where ppamax.business_group_id = c_business_group_id
283 and pacmax.tax_unit_id = c_legal_entity_id
284 and paamax.person_id = c_person_id
285 and paamax.assignment_id = pacmax.assignment_id
286 and ppamax.payroll_action_id = pacmax.payroll_action_id
287 and ppamax.effective_date between g_pact.start_date
288 and g_pact.end_date
289 and ppamax.action_type in ('R','B','I','Q','V')
290 group by to_char(ppamax.effective_date,'MM')
291 order by to_char(ppamax.effective_date,'MM') desc;
292 --
293 cursor month_year_action ( c_person_id per_all_people_f.person_id%type,
294 c_business_group_id hr_organization_units.business_group_id%type,
295 c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
296 c_basis_year varchar2,
297 c_action_sequence pay_assignment_actions.action_sequence%type )
298 is
299 select /*+ ORDERED USE_NL(pac) */
300 pac.assignment_action_id assact_id,
301 decode(ppa.action_type,'V',ppa.effective_date,ppa.date_earned) date_earned,
302 pac.tax_unit_id tax_uid
303 from per_assignments_f paa,
304 pay_assignment_actions pac,
305 pay_payroll_actions ppa
306 where ppa.business_group_id = c_business_group_id
307 and pac.tax_unit_id = c_legal_entity_id
308 and paa.person_id = c_person_id
309 and paa.assignment_id = pac.assignment_id
310 and ppa.effective_date between g_pact.start_date
311 and g_pact.end_date
312 and ppa.payroll_action_id = pac.payroll_action_id
313 and pac.action_sequence = c_action_sequence;
314 --
315 cursor c_globals(c_effective_date ff_globals_f.effective_start_date%type)
316 is
317 select global_value
318 from ff_globals_f
319 where global_name = 'CPF_TOT_EARN_CAP_AMT'
320 and c_effective_date between effective_start_date and effective_end_date ;
321 --
322 month_year_action_sequence_rec month_year_action_sequence%rowtype;
323 month_year_action_rec month_year_action%rowtype;
324 --
325 begin
326 l_proc_name := l_package || 'archive_code';
327
328 l_aw_toward_cap := 0;
329 l_aw_cap_recalculated := 0;
330 l_master_block := 'Y';
331 l_over_paid_flag := 'N';
332
333 if g_debug then
334 hr_utility.set_location(l_proc_name||' Start of archive_code',10);
335 end if;
336 open c_get_details ;
337 fetch c_get_details into l_assignment_id,l_person_id,l_date_earned;
338 close c_get_details ;
339 ---------------------------------------------------------------------------------------
340 --Storing minimum of final process date and end of the basis year .
341 ---------------------------------------------------------------------------------------
342 l_date_earned := least(nvl(l_date_earned,to_date('31-12-4712','dd-mm-yyyy')),g_pact.end_date);
343 --
344 --------------------------------------------------------
345 -- Fetch the value for the global 'CPF_TOT_EARN_CAP_AMT'
346 -- Bug 13247242, use the latest payroll process date to fetch the
347 -- value for the global 'CPF_TOT_EARN_CAP_AMT', instead of using
348 -- sysdate
349 --------------------------------------------------------
350 open c_globals(l_date_earned);
351 fetch c_globals into l_cpf_tot_earn_cap_amt ;
352 close c_globals;
353 --
354 open month_year_action_sequence( l_person_id,
355 g_pact.business_group_id,
356 g_pact.legal_entity_id,
357 g_pact.basis_year );
358 loop
359 fetch month_year_action_sequence into month_year_action_sequence_rec;
360 exit when month_year_action_sequence%notfound;
361 --
362 open month_year_action( l_person_id,
363 g_pact.business_group_id,
364 g_pact.legal_entity_id,
365 g_pact.basis_year,
366 month_year_action_sequence_rec.act_seq );
367 --
368 fetch month_year_action into month_year_action_rec;
369 if month_year_action%found then
370 --
371 if l_master_block = 'Y' then
372
373 ----------------------------------------------------
374 -- Populating emp_details_rec with Employee Details
375 -----------------------------------------------------
376
377 open c_employee_details(l_person_id);
378 fetch c_employee_details into emp_details_rec;
379 close c_employee_details;
380 --
381 --------------------------------------------------------------------------
382 -- Populating g_balance_value_tab with defined balance ids and
383 -- g_context_tab with tax unit id.
384 --------------------------------------------------------------------------
385 --
386 for counter in 1..g_ytd_def_bal_tbl.count
387 loop
388 g_balance_value_tab(counter).defined_balance_id := g_ytd_def_bal_tbl(counter);
389 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
390 end loop;
391 --
392 -----------------------------------------
393 -- Batch Balance Retrival
394 -----------------------------------------
395 --
396 pay_balance_pkg.get_value ( month_year_action_rec.assact_id,
397 g_balance_value_tab,
398 g_context_tab,
399 false,
400 false,
401 g_detailed_bal_out_tab );
402 --
403 --------------------------------------------------------------------------
404 -- Populating record ytd_balance_rec with Balance Values and Balance Name.
405 --------------------------------------------------------------------------
406 --
407 for counter in 1..g_detailed_bal_out_tab.count
408 loop
409 ytd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
410 ytd_balance_rec(counter).balance_name := g_bal_name_tbl(counter);
411 --------------------------------------------------------------------------------
412 -- Storing balance 'CPF Additional Earnings Toward Cap' in to local variable.
413 --------------------------------------------------------------------------------
414 if g_bal_name_tbl(counter) = 'CPF Additional Earnings Toward Cap' then
415 l_aw_toward_cap := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
416 end if;
417 end loop;
418 --
419 -----------------------------------------------------------------------------------
420 -- Additional Wages Cap is recalculated based on current year Ordinary Earnings.
421 -----------------------------------------------------------------------------------
422 --
423 l_aw_cap_recalculated := nvl(l_cpf_tot_earn_cap_amt,0) - nvl(get_cur_year_ord_ytd(l_person_id,l_assignment_id,l_date_earned),0);
424 --
425 ------------------------------------------------
426 -- Insert data into pay_action_information
427 ------------------------------------------------
428 --
429 insert into pay_action_information (
430 action_information_id,
431 action_context_id,
432 action_context_type,
433 tax_unit_id,
434 assignment_id,
435 action_information_category,
436 action_information1,
437 action_information2,
438 action_information3,
439 action_information4,
440 action_information5,
441 action_information6, -- CPF Additional Earnings Eligible Comp - Bug 12873143
442 action_information7, -- CPF Additional Earnings Toward Cap
443 action_information8) -- Additional Wages Cap Recalculated
444 values ( pay_action_information_s.nextval,
445 p_assignment_action_id,
446 'AAC',
447 g_pact.legal_entity_id,
448 l_assignment_id,
449 'SG AWCAP DETAILS',
450 'HEADER',
451 emp_details_rec.employee_number,
452 emp_details_rec.employee_name,
453 emp_details_rec.cpf_number,
454 emp_details_rec.telephone_number,
455 ytd_balance_rec(1).balance_value,
456 ytd_balance_rec(2).balance_value,
457 l_aw_cap_recalculated ) ;
458 --
459 if nvl(l_aw_cap_recalculated,0) >= nvl(l_aw_toward_cap,0) then
460 l_over_paid_flag := 'N';
461 l_master_block := 'N';
462 else
463 l_over_paid_flag := 'Y';
464 l_master_block := 'N';
465 end if;
466 end if;
467 --
468 ------------------------------------------------------------------------------
469 --
470 if l_over_paid_flag = 'Y' then
471 --------------------------------------------------------------------------
472 -- Populating g_balance_value_tab with defined balance ids and
473 -- g_context_tab with tax unit id.
474 --------------------------------------------------------------------------
475 for counter in 1..g_mtd_def_bal_tbl.count
476 loop
477 g_balance_value_tab(counter).defined_balance_id := g_mtd_def_bal_tbl(counter);
478 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
479
480 end loop;
481 --
482 -----------------------------------------
483 -- Batch Balance Retrival
484 -----------------------------------------
485 --
486 pay_balance_pkg.get_value ( month_year_action_rec.assact_id,
487 g_balance_value_tab,
488 g_context_tab,
489 false,
490 false,
491 g_detailed_bal_out_tab );
492 --
493 --------------------------------------------------------------------------
494 -- Populating record mtd_balance_rec with Balance Values and Date Earned.
495 --------------------------------------------------------------------------
496 --
497 for counter in 1..g_detailed_bal_out_tab.count
498 loop
499 mtd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
500 mtd_balance_rec(counter).date_earned := month_year_action_rec.date_earned;
501
502 end loop;
503 --
504 ------------------------------------------------
505 -- Insert data into pay_action_information
506 ------------------------------------------------
507 --
508 insert into pay_action_information (
509 action_information_id,
510 action_context_id,
511 action_context_type,
512 tax_unit_id,
513 assignment_id,
514 effective_date,
515 action_information_category,
516 action_information1,
517 action_information2, -- CPF Additional Earnings Eligible Comp - Bug 12873143
518 action_information3, -- EE CPF AE
519 action_information4, -- EE CPF OE
520 action_information5, -- EE VOL CPF AE
521 action_information6, -- EE VOL CPF OE
522 action_information7, -- ER CPF AE
523 action_information8, -- ER CPF OE
524 action_information9, -- ER VOL CPF AE
525 action_information10, -- ER VOL CPF OE
526 action_information11 ) -- CPF Ordinary Earnings Eligible Comp - Bug 12873143
527 values (
528 pay_action_information_s.nextval,
529 p_assignment_action_id,
530 'AAC',
531 g_pact.legal_entity_id,
532 l_assignment_id,
533 mtd_balance_rec(10).date_earned ,
534 'SG AWCAP DETAILS',
535 'DETAIL',
536 mtd_balance_rec(1).balance_value,
537 mtd_balance_rec(2).balance_value,
538 mtd_balance_rec(3).balance_value,
539 mtd_balance_rec(4).balance_value,
540 mtd_balance_rec(5).balance_value,
541 mtd_balance_rec(6).balance_value,
542 mtd_balance_rec(7).balance_value,
543 mtd_balance_rec(8).balance_value,
544 mtd_balance_rec(9).balance_value,
545 mtd_balance_rec(10).balance_value ) ;
546 end if;
547 end if;
548 close month_year_action;
549 end loop;
550 close month_year_action_sequence;
551 --
552 if g_debug then
553 hr_utility.set_location(l_proc_name||' End of archive_code',20);
554 end if;
555 exception
556 when others then
557 if g_debug then
558 hr_utility.set_location(l_proc_name||' Error raised in procedure',100);
559 end if;
560 raise;
561 end archive_code ;
562 ----------------------------------------------------------------------------------
563 --Function calculates current year Ordinary Earnings with monthly ceiling of 5,500
564 ---------------------------------------------------------------------------------
565 function get_cur_year_ord_ytd (p_person_id in per_all_people_f.person_id%type,
566 p_assignment_id in per_all_assignments_f.assignment_id%type,
567 p_date_earned in date) return number
568 is
569 l_proc_name varchar2(100);
570 --
571 cursor c_month_year_action_sequence ( c_date_earned date)
572 is
573 select /*+ ORDERED USE_NL(paa) */
574 max(paa.action_sequence),
575 to_number(to_char(ppa.effective_date,'MM'))
576 from per_assignments_f paaf,
577 pay_assignment_actions paa,
578 pay_payroll_actions ppa
579 where paaf.person_id = p_person_id
580 and paa.assignment_id = paaf.assignment_id
581 and ppa.payroll_action_id = paa.payroll_action_id
582 and ppa.action_type in ('R','Q','B','V','I')
583 and ppa.date_earned between trunc(c_date_earned,'Y')
584 and last_day(c_date_earned)
585 group by to_number(to_char(ppa.effective_date,'MM'))
586 order by to_number(to_char(ppa.effective_date,'MM')) desc;
587 --
588 cursor c_month_year_action ( c_date_earned date,
589 c_action_sequence number )
590 is
591 select /*+ ORDERED USE_NL(paa) */
592 paa.assignment_action_id,
593 ppa.effective_date
594 from per_assignments_f paaf,
595 pay_assignment_actions paa,
596 pay_payroll_actions ppa
597 where paaf.person_id = p_person_id
598 and paa.assignment_id = paaf.assignment_id
599 and ppa.payroll_action_id = paa.payroll_action_id
600 and paa.action_sequence = c_action_sequence
601 and ppa.date_earned between trunc(c_date_earned,'Y')
602 and last_day(c_date_earned);
603 --
604 cursor c_defined_bal_id ( p_balance_name in varchar2,
605 p_dimension_name in varchar2 )
606 is
607 select pdb.defined_balance_id
608 from pay_defined_balances pdb,
609 pay_balance_types pbt,
610 pay_balance_dimensions pbd
611 where pbt.balance_name = p_balance_name
612 and pbd.dimension_name = p_dimension_name
613 and pbt.balance_type_id = pdb.balance_type_id
614 and pdb.balance_dimension_id = pbd.balance_dimension_id
615 and pdb.legislation_code = 'SG';
616 --
617 cursor c_globals (p_date in ff_globals_f.effective_start_date%type)
618 is
619 select global_value, effective_start_date
620 from ff_globals_f
621 where global_name = 'CPF_ORD_MONTH_CAP_AMT'
622 and p_date between effective_start_date and effective_end_date;
623
624 --
625 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
626 g_context_tab pay_balance_pkg.t_context_tab;
627 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
628 --
629 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
630 l_action_sequence pay_assignment_actions.action_sequence%TYPE;
631 l_month number;
632 l_effective_date date;
633 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
634 l_defined_bal_id number;
635 l_cur_ord_ytd number;
636 l_ord_mon_cap_amt number;
637 l_ord_mon_cap_amt_date date;
638 l_ord_mon_cap_amt_tmp_date date;
639 l_ord_mon_cap_amt_change varchar2(1);
640 l_retro_exist boolean := FALSE ;
641 l_retro_ele number;
642 l_final_process_date date;
643 begin
644 l_proc_name := l_package || 'get_cur_year_ord_ytd';
645 l_cur_ord_ytd := 0;
646 l_ord_mon_cap_amt_change := 'N';
647 l_ord_mon_cap_amt := 0;
648 --
649 if g_debug then
650 hr_utility.set_location(l_proc_name||' start of procedure',10);
651 end if;
652
653 /* Bug 12639110, ORD month ceiling changed in the current year */
654 open c_globals(p_date_earned);
655 fetch c_globals into l_ord_mon_cap_amt, l_ord_mon_cap_amt_date;
656 close c_globals;
657
658 if l_ord_mon_cap_amt_date between trunc(p_date_earned,'Y') and p_date_earned then
659 l_ord_mon_cap_amt_change := 'Y';
660 else
661 l_ord_mon_cap_amt_change := 'N';
662 end if;
663 --
664 open c_defined_bal_id('CPF Ordinary Earnings Eligible Comp','_PER_LE_MONTH');
665 fetch c_defined_bal_id into g_balance_value_tab(1).defined_balance_id;
666 close c_defined_bal_id;
667 --
668 open c_defined_bal_id('Ordinary Earnings ineligible for CPF','_PER_LE_MONTH');
669 fetch c_defined_bal_id into g_balance_value_tab(2).defined_balance_id;
670 close c_defined_bal_id;
671 --
672 open c_defined_bal_id('Retro Ord Retro Period','_ASG_PTD');
673 fetch c_defined_bal_id into g_balance_value_tab(3).defined_balance_id;
674 close c_defined_bal_id;
675 --
676 -- Bug 12885297, removed fix for bug 7661439
677 --
678 open c_month_year_action_sequence( p_date_earned );
679 loop
680 fetch c_month_year_action_sequence into l_action_sequence,l_month;
681 exit when c_month_year_action_sequence%NOTFOUND;
682 --
683 open c_month_year_action( p_date_earned, l_action_sequence );
684 fetch c_month_year_action into l_assignment_action_id,l_effective_date;
685 --
686 if c_month_year_action%FOUND then
687 --
688 g_context_tab.delete;
689 g_detailed_bal_out_tab.delete;
690 --
691 g_context_tab(1).tax_unit_id := g_pact.legal_entity_id;
692 g_context_tab(2).tax_unit_id := g_pact.legal_entity_id;
693 g_context_tab(3).tax_unit_id := g_pact.legal_entity_id;
694
695 -- Bug 12639110
696 if l_ord_mon_cap_amt_change = 'Y' then
697 open c_globals(l_effective_date);
698 fetch c_globals into l_ord_mon_cap_amt, l_ord_mon_cap_amt_tmp_date;
699 close c_globals;
700 end if;
701
702 pay_balance_pkg.get_value ( l_assignment_action_id,
703 g_balance_value_tab,
704 g_context_tab,
705 false,
706 false,
707 g_detailed_bal_out_tab );
708 --
709 if l_retro_exist
710 or nvl(g_detailed_bal_out_tab(3).balance_value,0)<>0 then /* Bug 6815874 */
711 l_retro_ele := pay_sg_deductions.get_retro_earnings( p_assignment_id , l_effective_date ); /* Bug 6815874 */
712 if l_retro_ele = 0 then /* Bug 6815874 */
713 l_retro_exist := FALSE;
714 end if;
715 -- Bug 12885297, removed fix for bug 7661439
716 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
717 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
718 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )
719 + nvl(l_retro_ele,0)),l_ord_mon_cap_amt );
720 else
721 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
722 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
723 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )),l_ord_mon_cap_amt );
724 end if;
725 --
726 if nvl( g_detailed_bal_out_tab(3).balance_value,0 ) <> 0 then
727 l_retro_exist := TRUE;
728 end if;
729 --
730 end if;
731 --
732 close c_month_year_action;
733 end loop;
734 --
735 close c_month_year_action_sequence;
736 --
737 if g_debug then
738 hr_utility.set_location(l_proc_name||' End of procedure',20);
739 end if;
740 return l_cur_ord_ytd;
741 --
742 exception
743 when others then
744 if g_debug then
745 hr_utility.set_location(l_proc_name||' Error raised in procedure',100);
746 end if;
747 raise;
748
749 end get_cur_year_ord_ytd;
750 --
751 procedure deinit_code
752 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
753 is
754 retain_archive_flag char(1);
755 l_rep_req_id number;
756
757 begin
758 retain_archive_flag := 'N' ;
759 l_rep_req_id := 0;
760 if g_debug then
761 hr_utility.set_location('Start of denit_code',10);
762 end if;
763 ------------------------------------------------
764 -- Call to AW CPF Capping Recalculation Report
765 ------------------------------------------------
766 l_rep_req_id := FND_REQUEST.SUBMIT_REQUEST (
767 APPLICATION => 'PAY',
768 PROGRAM => 'PAYSGCPF',
769 ARGUMENT1 => 'P_BASIS_YEAR=' || g_pact.basis_year,
770 ARGUMENT2 => 'P_BUSINESS_GROUP_ID='|| g_pact.business_group_id,
771 ARGUMENT3 => 'P_LEGAL_ENTITY=' || g_pact.legal_entity_id,
772 ARGUMENT4 => 'P_PAYROLL_ACTION_ID=' || p_payroll_action_id,
773 ARGUMENT5 => 'P_PERSON_ID=' || g_pact.person_id ,
774 ARGUMENT6 => 'P_RETAIN_ARCHIVE_DATA='|| g_pact.retain_archive_data);
775 --
776 if g_debug then
777 hr_utility.set_location('End of denit_code',20);
778 end if;
779 end deinit_code ;
780 begin
781
782 l_package := 'pay_sg_awcap_archive-';
783 -------------------------------------------
784 -- package body level code
785 -- Populates defined Balance ids
786 -- Bug 12873143, get balance CPF Additional Earnings Eligible Comp
787 -- instead of Additional Earnings and CPF Ordinary Earnings
788 -- Eligible Comp instead of Ordinary Earnings
789 -------------------------------------------
790
791 select pdb.defined_balance_id def_bal_id,
792 pbt.balance_name
793 bulk collect into
794 g_ytd_def_bal_tbl,
795 g_bal_name_tbl
796 from pay_balance_types pbt,
797 pay_defined_balances pdb,
798 pay_balance_dimensions pbd
799 where pbt.legislation_code = 'SG'
800 and pbd.legislation_code = pbt.legislation_code
801 and pdb.legislation_code = pbt.legislation_code
802 and pbt.balance_name in ( 'CPF Additional Earnings Eligible Comp',
803 'CPF Additional Earnings Toward Cap' )
804 and pbt.balance_type_id = pdb.balance_type_id
805 and pbd.balance_dimension_id = pdb.balance_dimension_id
806 and pbd.dimension_name = '_PER_LE_YTD'
807 order by pbt.balance_name;
808 --
809 select pdb.defined_balance_id def_bal_id
810 bulk collect into
811 g_mtd_def_bal_tbl
812 from pay_balance_types pbt,
813 pay_defined_balances pdb,
814 pay_balance_dimensions pbd
815 where pbt.legislation_code = 'SG'
816 and pbd.legislation_code = pbt.legislation_code
817 and pdb.legislation_code = pbt.legislation_code
818 and pbt.balance_name in ('Employee CPF Contributions Additional Earnings',
819 'Employee CPF Contributions Ordinary Earnings',
820 'Employer CPF Contributions Additional Earnings',
821 'Employer CPF Contributions Ordinary Earnings',
822 'Employee Vol CPF Contributions Additional Earnings',
823 'Employee Vol CPF Contributions Ordinary Earnings' ,
824 'Employer Vol CPF Contributions Additional Earnings',
825 'Employer Vol CPF Contributions Ordinary Earnings',
826 'CPF Additional Earnings Eligible Comp',
827 'CPF Ordinary Earnings Eligible Comp')
828 and pbt.balance_type_id = pdb.balance_type_id
829 and pbd.balance_dimension_id = pdb.balance_dimension_id
830 and pbd.dimension_name = '_PER_LE_MONTH'
831 order by decode(pbt.balance_name,'CPF Additional Earnings Eligible Comp',0,
832 'Employee CPF Contributions Additional Earnings',1,
833 'Employee CPF Contributions Ordinary Earnings',2,
834 'Employee Vol CPF Contributions Additional Earnings',3,
835 'Employee Vol CPF Contributions Ordinary Earnings',4,
836 'Employer CPF Contributions Additional Earnings',5,
837 'Employer CPF Contributions Ordinary Earnings',6,
838 'Employer Vol CPF Contributions Additional Earnings',7,
839 'Employer Vol CPF Contributions Ordinary Earnings',8,
840 'CPF Ordinary Earnings Eligible Comp',9,0);
841 --
842 exception
843 when others then
844 raise;
845 end pay_sg_awcap_archive;