[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_AWCAP_ARCHIVE
Source
1 package body pay_sg_awcap_archive as
2 /* $Header: pysgawcp.pkb 120.0.12010000.5 2008/08/06 08:21:23 ubhat ship $ */
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
316 is
317 select global_value
318 from ff_globals_f
319 where global_name = 'CPF_TOT_EARN_CAP_AMT'
320 and g_pact.end_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 --------------------------------------------------------
347 open c_globals;
348 fetch c_globals into l_cpf_tot_earn_cap_amt ;
349 close c_globals;
350 --
351 open month_year_action_sequence( l_person_id,
352 g_pact.business_group_id,
353 g_pact.legal_entity_id,
354 g_pact.basis_year );
355 loop
356 fetch month_year_action_sequence into month_year_action_sequence_rec;
357 exit when month_year_action_sequence%notfound;
358 --
359 open month_year_action( l_person_id,
360 g_pact.business_group_id,
361 g_pact.legal_entity_id,
362 g_pact.basis_year,
363 month_year_action_sequence_rec.act_seq );
364 --
365 fetch month_year_action into month_year_action_rec;
366 if month_year_action%found then
367 --
368 if l_master_block = 'Y' then
369
370 ----------------------------------------------------
371 -- Populating emp_details_rec with Employee Details
372 -----------------------------------------------------
373
374 open c_employee_details(l_person_id);
375 fetch c_employee_details into emp_details_rec;
376 close c_employee_details;
377 --
378 --------------------------------------------------------------------------
379 -- Populating g_balance_value_tab with defined balance ids and
380 -- g_context_tab with tax unit id.
381 --------------------------------------------------------------------------
382 --
383 for counter in 1..g_ytd_def_bal_tbl.count
384 loop
385 g_balance_value_tab(counter).defined_balance_id := g_ytd_def_bal_tbl(counter);
386 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
387 end loop;
388 --
389 -----------------------------------------
390 -- Batch Balance Retrival
391 -----------------------------------------
392 --
393 pay_balance_pkg.get_value ( month_year_action_rec.assact_id,
394 g_balance_value_tab,
395 g_context_tab,
396 false,
397 false,
398 g_detailed_bal_out_tab );
399 --
400 --------------------------------------------------------------------------
401 -- Populating record ytd_balance_rec with Balance Values and Balance Name.
402 --------------------------------------------------------------------------
403 --
404 for counter in 1..g_detailed_bal_out_tab.count
405 loop
406 ytd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
407 ytd_balance_rec(counter).balance_name := g_bal_name_tbl(counter);
408 --------------------------------------------------------------------------------
409 -- Storing balance 'CPF Additional Earnings Toward Cap' in to local variable.
410 --------------------------------------------------------------------------------
411 if g_bal_name_tbl(counter) = 'CPF Additional Earnings Toward Cap' then
412 l_aw_toward_cap := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
413 end if;
414 end loop;
415 --
416 -----------------------------------------------------------------------------------
417 -- Additional Wages Cap is recalculated based on current year Ordinary Earnings.
418 -----------------------------------------------------------------------------------
419 --
420 l_aw_cap_recalculated := least(nvl(l_cpf_tot_earn_cap_amt,0) - nvl(get_cur_year_ord_ytd(l_person_id,l_assignment_id,l_date_earned),0),66000);
421 --
422 ------------------------------------------------
423 -- Insert data into pay_action_information
424 ------------------------------------------------
425 --
426 insert into pay_action_information (
427 action_information_id,
428 action_context_id,
429 action_context_type,
430 tax_unit_id,
431 assignment_id,
432 action_information_category,
433 action_information1,
434 action_information2,
435 action_information3,
436 action_information4,
437 action_information5,
438 action_information6, -- Additional Earnings
439 action_information7, -- CPF Additional Earnings Toward Cap
440 action_information8) -- Additional Wages Cap Recalculated
441 values ( pay_action_information_s.nextval,
442 p_assignment_action_id,
443 'AAC',
444 g_pact.legal_entity_id,
445 l_assignment_id,
446 'SG AWCAP DETAILS',
447 'HEADER',
448 emp_details_rec.employee_number,
449 emp_details_rec.employee_name,
450 emp_details_rec.cpf_number,
451 emp_details_rec.telephone_number,
452 ytd_balance_rec(1).balance_value,
453 ytd_balance_rec(2).balance_value,
454 l_aw_cap_recalculated ) ;
455 --
456 if nvl(l_aw_cap_recalculated,0) >= nvl(l_aw_toward_cap,0) then
457 l_over_paid_flag := 'N';
458 l_master_block := 'N';
459 else
460 l_over_paid_flag := 'Y';
461 l_master_block := 'N';
462 end if;
463 end if;
464 --
465 ------------------------------------------------------------------------------
466 --
467 if l_over_paid_flag = 'Y' then
468 --------------------------------------------------------------------------
469 -- Populating g_balance_value_tab with defined balance ids and
470 -- g_context_tab with tax unit id.
471 --------------------------------------------------------------------------
472 for counter in 1..g_mtd_def_bal_tbl.count
473 loop
474 g_balance_value_tab(counter).defined_balance_id := g_mtd_def_bal_tbl(counter);
475 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
476
477 end loop;
478 --
479 -----------------------------------------
480 -- Batch Balance Retrival
481 -----------------------------------------
482 --
483 pay_balance_pkg.get_value ( month_year_action_rec.assact_id,
484 g_balance_value_tab,
485 g_context_tab,
486 false,
487 false,
488 g_detailed_bal_out_tab );
489 --
490 --------------------------------------------------------------------------
491 -- Populating record mtd_balance_rec with Balance Values and Date Earned.
492 --------------------------------------------------------------------------
493 --
494 for counter in 1..g_detailed_bal_out_tab.count
495 loop
496 mtd_balance_rec(counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
497 mtd_balance_rec(counter).date_earned := month_year_action_rec.date_earned;
498
499 end loop;
500 --
501 ------------------------------------------------
502 -- Insert data into pay_action_information
503 ------------------------------------------------
504 --
505 insert into pay_action_information (
506 action_information_id,
507 action_context_id,
508 action_context_type,
509 tax_unit_id,
510 assignment_id,
511 effective_date,
512 action_information_category,
513 action_information1,
514 action_information2, -- Additional Earnings
515 action_information3, -- EE CPF AE
516 action_information4, -- EE CPF OE
517 action_information5, -- EE VOL CPF AE
518 action_information6, -- EE VOL CPF OE
519 action_information7, -- ER CPF AE
520 action_information8, -- ER CPF OE
521 action_information9, -- ER VOL CPF AE
522 action_information10, -- ER VOL CPF OE
523 action_information11 ) -- Ordinary Earnings
524 values (
525 pay_action_information_s.nextval,
526 p_assignment_action_id,
527 'AAC',
528 g_pact.legal_entity_id,
529 l_assignment_id,
530 mtd_balance_rec(10).date_earned ,
531 'SG AWCAP DETAILS',
532 'DETAIL',
533 mtd_balance_rec(1).balance_value,
534 mtd_balance_rec(2).balance_value,
535 mtd_balance_rec(3).balance_value,
536 mtd_balance_rec(4).balance_value,
537 mtd_balance_rec(5).balance_value,
538 mtd_balance_rec(6).balance_value,
539 mtd_balance_rec(7).balance_value,
540 mtd_balance_rec(8).balance_value,
541 mtd_balance_rec(9).balance_value,
542 mtd_balance_rec(10).balance_value ) ;
543 end if;
544 end if;
545 close month_year_action;
546 end loop;
547 close month_year_action_sequence;
548 --
549 if g_debug then
550 hr_utility.set_location(l_proc_name||' End of archive_code',20);
551 end if;
552 exception
553 when others then
554 if g_debug then
555 hr_utility.set_location(l_proc_name||' Error raised in procedure',100);
556 end if;
557 raise;
558 end archive_code ;
559 ----------------------------------------------------------------------------------
560 --Function calculates current year Ordinary Earnings with monthly ceiling of 5,500
561 ---------------------------------------------------------------------------------
562 function get_cur_year_ord_ytd (p_person_id in per_all_people_f.person_id%type,
563 p_assignment_id in per_all_assignments_f.assignment_id%type,
564 p_date_earned in date) return number
565 is
566 l_proc_name varchar2(100);
567 --
568 cursor c_month_year_action_sequence ( c_date_earned date)
569 is
570 select /*+ ORDERED USE_NL(paa) */
571 max(paa.action_sequence),
572 to_number(to_char(ppa.effective_date,'MM'))
573 from per_assignments_f paaf,
574 pay_assignment_actions paa,
575 pay_payroll_actions ppa
576 where paaf.person_id = p_person_id
577 and paa.assignment_id = paaf.assignment_id
578 and ppa.payroll_action_id = paa.payroll_action_id
579 and ppa.action_type in ('R','Q','B','V','I')
580 and ppa.date_earned between trunc(c_date_earned,'Y')
581 and last_day(c_date_earned)
582 group by to_number(to_char(ppa.effective_date,'MM'))
583 order by to_number(to_char(ppa.effective_date,'MM')) desc;
584 --
585 cursor c_month_year_action ( c_date_earned date,
586 c_action_sequence number )
587 is
588 select /*+ ORDERED USE_NL(paa) */
589 paa.assignment_action_id,
590 ppa.effective_date
591 from per_assignments_f paaf,
592 pay_assignment_actions paa,
593 pay_payroll_actions ppa
594 where paaf.person_id = p_person_id
595 and paa.assignment_id = paaf.assignment_id
596 and ppa.payroll_action_id = paa.payroll_action_id
597 and paa.action_sequence = c_action_sequence
598 and ppa.date_earned between trunc(c_date_earned,'Y')
599 and last_day(c_date_earned);
600 --
601 cursor c_defined_bal_id ( p_balance_name in varchar2,
602 p_dimension_name in varchar2 )
603 is
604 select pdb.defined_balance_id
605 from pay_defined_balances pdb,
606 pay_balance_types pbt,
607 pay_balance_dimensions pbd
608 where pbt.balance_name = p_balance_name
609 and pbd.dimension_name = p_dimension_name
610 and pbt.balance_type_id = pdb.balance_type_id
611 and pdb.balance_dimension_id = pbd.balance_dimension_id
612 and pdb.legislation_code = 'SG';
613 --
614 cursor c_globals(c_date_earned date)
615 is
616 select global_value
617 from ff_globals_f
618 where global_name = 'CPF_ORD_MONTH_CAP_AMT'
619 and c_date_earned between effective_start_date and effective_end_date;
620 --
621 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
622 g_context_tab pay_balance_pkg.t_context_tab;
623 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
624 --
625 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
626 l_action_sequence pay_assignment_actions.action_sequence%TYPE;
627 l_month number;
628 l_effective_date date;
629 l_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE;
630 l_defined_bal_id number;
631 l_cur_ord_ytd number;
632 l_ord_mon_cap_amt number;
633 l_retro_exist boolean := FALSE ;
634 l_retro_ele number;
635 l_final_process_date date;
636 begin
637 l_proc_name := l_package || 'get_cur_year_ord_ytd';
638 l_cur_ord_ytd := 0;
639 --
640 if g_debug then
641 hr_utility.set_location(l_proc_name||' start of procedure',10);
642 end if;
643
644 open c_globals(p_date_earned);
645 fetch c_globals into l_ord_mon_cap_amt;
646 close c_globals ;
647 --
648 open c_defined_bal_id('CPF Ordinary Earnings Eligible Comp','_PER_LE_MONTH');
649 fetch c_defined_bal_id into g_balance_value_tab(1).defined_balance_id;
650 close c_defined_bal_id;
651 --
652 open c_defined_bal_id('Ordinary Earnings ineligible for CPF','_PER_LE_MONTH');
653 fetch c_defined_bal_id into g_balance_value_tab(2).defined_balance_id;
654 close c_defined_bal_id;
655 --
656 open c_defined_bal_id('Retro Ord Retro Period','_ASG_PTD');
657 fetch c_defined_bal_id into g_balance_value_tab(3).defined_balance_id;
658 close c_defined_bal_id;
659 --
660 open c_month_year_action_sequence( p_date_earned );
661 loop
662 fetch c_month_year_action_sequence into l_action_sequence,l_month;
663 exit when c_month_year_action_sequence%NOTFOUND;
664 --
665 open c_month_year_action( p_date_earned, l_action_sequence );
666 fetch c_month_year_action into l_assignment_action_id,l_effective_date;
667 --
668 if c_month_year_action%FOUND then
669 --
670 g_context_tab.delete;
671 g_detailed_bal_out_tab.delete;
672 --
673 g_context_tab(1).tax_unit_id := g_pact.legal_entity_id;
674 g_context_tab(2).tax_unit_id := g_pact.legal_entity_id;
675 g_context_tab(3).tax_unit_id := g_pact.legal_entity_id;
676 --
677 pay_balance_pkg.get_value ( l_assignment_action_id,
678 g_balance_value_tab,
679 g_context_tab,
680 false,
681 false,
682 g_detailed_bal_out_tab );
683 --
684 if l_retro_exist
685 or nvl(g_detailed_bal_out_tab(3).balance_value,0)<>0 then /* Bug 6815874 */
686 l_retro_ele := pay_sg_deductions.get_retro_earnings( p_assignment_id , l_effective_date ); /* Bug 6815874 */
687 if l_retro_ele = 0 then /* Bug 6815874 */
688 l_retro_exist := FALSE;
689 end if;
690 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
691 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
692 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )
693 + nvl(l_retro_ele,0)),l_ord_mon_cap_amt );
694 else
695 l_cur_ord_ytd := l_cur_ord_ytd + least( (nvl( g_detailed_bal_out_tab(1).balance_value,0 )
696 - nvl( g_detailed_bal_out_tab(2).balance_value,0 )
697 - nvl( g_detailed_bal_out_tab(3).balance_value,0 )),l_ord_mon_cap_amt );
698 end if;
699 --
700 if nvl( g_detailed_bal_out_tab(3).balance_value,0 ) <> 0 then
701 l_retro_exist := TRUE;
702 end if;
703 --
704 end if;
705 --
706 close c_month_year_action;
707 end loop;
708 --
709 close c_month_year_action_sequence;
710 --
711 if g_debug then
712 hr_utility.set_location(l_proc_name||' End of procedure',20);
713 end if;
714 return l_cur_ord_ytd;
715 --
716 exception
717 when others then
718 if g_debug then
719 hr_utility.set_location(l_proc_name||' Error raised in procedure',100);
720 end if;
721 raise;
722
723 end get_cur_year_ord_ytd;
724 --
725 procedure deinit_code
726 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
727 is
728 retain_archive_flag char(1);
729 l_rep_req_id number;
730
731 begin
732 retain_archive_flag := 'N' ;
733 l_rep_req_id := 0;
734 if g_debug then
735 hr_utility.set_location('Start of denit_code',10);
736 end if;
737 ------------------------------------------------
738 -- Call to AW CPF Capping Recalculation Report
739 ------------------------------------------------
740 l_rep_req_id := FND_REQUEST.SUBMIT_REQUEST (
741 APPLICATION => 'PAY',
742 PROGRAM => 'PAYSGCPF',
743 ARGUMENT1 => 'P_BASIS_YEAR=' || g_pact.basis_year,
744 ARGUMENT2 => 'P_BUSINESS_GROUP_ID='|| g_pact.business_group_id,
745 ARGUMENT3 => 'P_LEGAL_ENTITY=' || g_pact.legal_entity_id,
746 ARGUMENT4 => 'P_PAYROLL_ACTION_ID=' || p_payroll_action_id,
747 ARGUMENT5 => 'P_PERSON_ID=' || g_pact.person_id ,
748 ARGUMENT6 => 'P_RETAIN_ARCHIVE_DATA='|| g_pact.retain_archive_data);
749 --
750 if g_debug then
751 hr_utility.set_location('End of denit_code',20);
752 end if;
753 end deinit_code ;
754 begin
755
756 l_package := 'pay_sg_awcap_archive-';
757 -------------------------------------------
758 -- package body level code
759 -- Populates defined Balance ids
760 -------------------------------------------
761
762 select pdb.defined_balance_id def_bal_id,
763 pbt.balance_name
764 bulk collect into
765 g_ytd_def_bal_tbl,
766 g_bal_name_tbl
767 from pay_balance_types pbt,
768 pay_defined_balances pdb,
769 pay_balance_dimensions pbd
770 where pbt.legislation_code = 'SG'
771 and pbd.legislation_code = pbt.legislation_code
772 and pdb.legislation_code = pbt.legislation_code
773 and pbt.balance_name in ( 'Additional Earnings',
774 'CPF Additional Earnings Toward Cap' )
775 and pbt.balance_type_id = pdb.balance_type_id
776 and pbd.balance_dimension_id = pdb.balance_dimension_id
777 and pbd.dimension_name = '_PER_LE_YTD'
778 order by pbt.balance_name;
779 --
780 select pdb.defined_balance_id def_bal_id
781 bulk collect into
782 g_mtd_def_bal_tbl
783 from pay_balance_types pbt,
784 pay_defined_balances pdb,
785 pay_balance_dimensions pbd
786 where pbt.legislation_code = 'SG'
787 and pbd.legislation_code = pbt.legislation_code
788 and pdb.legislation_code = pbt.legislation_code
789 and pbt.balance_name in ('Employee CPF Contributions Additional Earnings',
790 'Employee CPF Contributions Ordinary Earnings',
791 'Employer CPF Contributions Additional Earnings',
792 'Employer CPF Contributions Ordinary Earnings',
793 'Employee Vol CPF Contributions Additional Earnings',
794 'Employee Vol CPF Contributions Ordinary Earnings' ,
795 'Employer Vol CPF Contributions Additional Earnings',
796 'Employer Vol CPF Contributions Ordinary Earnings',
797 'Additional Earnings',
798 'Ordinary Earnings')
799 and pbt.balance_type_id = pdb.balance_type_id
800 and pbd.balance_dimension_id = pdb.balance_dimension_id
801 and pbd.dimension_name = '_PER_LE_MONTH'
802 order by pbt.balance_name;
803 --
804 exception
805 when others then
806 raise;
807 end pay_sg_awcap_archive;