[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_CPFLINE
Source
1 package body pay_sg_cpfline as
2 /* $Header: pysgcpfl.pkb 120.0 2005/05/29 08:43:26 appldev noship $ */
3 g_debug boolean;
4 --
5 l_package VARCHAR2(100);
6 l_proc_name VARCHAR2(100) ;
7
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 month_date varchar2(11),
18 legal_entity_id number ,
19 Start_date date ,
20 End_date date
21 );
22 --
23 g_pact t_pact;
24 -----------------------------------------------------
25 -- record type to hold archival information
26 -----------------------------------------------------
27 rec_action_info pay_action_information%rowtype ;
28 -----------------------------------------------------
29 -- Table to store Defined Balance details
30 -----------------------------------------------------
31 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type ;
32 type t_def_bal_name is table of pay_balance_types.balance_name%type ;
33 --
34 g_def_bal_id t_def_bal_id ;
35 g_def_bal_name t_def_bal_name ;
36 ----------------------------------------------------------------
37 -- Function Returns Defined Balance id
38 ----------------------------------------------------------------
39 function get_def_bal_id
40 ( p_def_bal_name in pay_balance_types.balance_name%type ) return number
41 is
42 begin
43 for i in 1..g_def_bal_name.count
44 loop
45 if g_def_bal_name(i) =p_def_bal_name then
46 return g_def_bal_id(i) ;
47 end if ;
48 end loop ;
49 -- if reached here
50 raise_application_error(-20001 , ' Program Error : Defined Balance not found ') ;
51 end get_def_bal_id ;
52 --------------------------------------------------------------------
53 -- These are PUBLIC procedures are required by the Archive process.
54 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
55 -- the archive process knows what code to execute for each step of
56 -- the archive.
57 --------------------------------------------------------------------
58 procedure range_code
59 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
60 p_sql out nocopy varchar2)
61 is
62 c_range_cursor constant varchar2(3000) :=
63 ' select distinct pap.person_id
64 from pay_payroll_actions ppa,
65 per_people_f pap
66 where ppa.payroll_action_id = :payroll_action_id
67 and pap.business_group_id = ppa.business_group_id
68 order by pap.person_id ' ;
69 begin
70 p_sql := c_range_cursor ;
71 end range_code ;
72 ------------------------------------------------------------
73 -- Assignment Action Code
74 ------------------------------------------------------------
75 procedure assignment_action_code
76 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
77 p_start_person_id in per_all_people_f.person_id%type,
78 p_end_person_id in per_all_people_f.person_id%type,
79 p_chunk in number )
80 is
81
82 l_next_action_id pay_assignment_actions.assignment_action_id%type;
83 --
84 ------------------------------------------------------------------------
85 --Bug#3833818 Added payroll_id join to improve performance of the query.
86 ------------------------------------------------------------------------
87 cursor c_assact
88 is
89 select distinct paa.assignment_id
90 from pay_payroll_actions xppa,
91 pay_payroll_actions rppa,
92 pay_assignment_actions rpac,
93 per_assignments_f paa
94 where xppa.payroll_action_id = p_payroll_action_id
95 and paa.person_id between p_start_person_id
96 and p_end_person_id
97 and rppa.business_group_id = g_pact.business_group_id
98 and rppa.payroll_id in ( select payroll_id
99 from pay_payrolls_f
100 where business_group_id = g_pact.business_group_id )
101 and rppa.effective_date between g_pact.start_date
102 and g_pact.end_date
103 and rppa.action_type in ('R','Q')
104 and rpac.action_status = 'C'
105 and rppa.payroll_action_id = rpac.payroll_action_id
106 and rpac.tax_unit_id = g_pact.legal_entity_id
107 and rpac.assignment_id = paa.assignment_id
108 and rppa.effective_date between paa.effective_start_date
109 and paa.effective_end_date ;
110 --
111 cursor next_action_id
112 is
113 select pay_assignment_actions_s.nextval
114 from dual;
115 --
116 begin
117 l_package := ' pay_sg_cpfline.';
118 l_proc_name := l_package || 'assignment_action_code';
119 pay_sg_cpfline.initialization_code(p_payroll_action_id) ;
120 --
121 if g_debug then
122 hr_utility.set_location(l_proc_name || ' Start of assignment_action_code',30);
123 end if;
124 --
125 for i in c_assact
126 loop
127 open next_action_id;
128 fetch next_action_id into l_next_action_id;
129 close next_action_id;
130 --
131 if g_debug then
132 hr_utility.set_location(l_proc_name|| ' Before calling hr_nonrun_asact.insact',30);
133 end if;
134 --
135 hr_nonrun_asact.insact( l_next_action_id,
136 i.assignment_id,
137 p_payroll_action_id,
138 p_chunk,
139 g_pact.legal_entity_id );
140 --
141 if g_debug then
142 hr_utility.set_location(l_proc_name||' After calling hr_nonrun_asact.insact',30);
143 end if;
144 --
145 end loop;
146 --
147 if g_debug then
148 hr_utility.set_location(l_proc_name|| ' End of assignment_action_code',30);
149 end if;
150 exception
151 when others then
152 if g_debug then
153 hr_utility.set_location(l_proc_name||' Error raised in assignment_action_code procedure',30);
154 end if;
155 raise;
156 end assignment_action_code ;
157 ------------------------------------------------------------
158 -- Assignment Action Code
159 ------------------------------------------------------------
160 procedure initialization_code
161 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
162 is
163
164 begin
165 l_package := ' pay_sg_cpfline.';
166 l_proc_name := l_package || 'initialization_code';
167 g_debug := hr_utility.debug_enabled;
168 --
169 if g_debug then
170 hr_utility.set_location(l_proc_name||' Start of initialization_code',20);
171 end if;
172 --
173 if g_pact.report_type is null then
174 select ppa.report_type,
175 ppa.report_qualifier,
176 ppa.report_category,
177 ppa.business_group_id,
178 ppa.effective_date,
179 to_number(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)) month_date,
180 to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters)) legal_entity_id,
181 to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)||'01','YYYYMMDD'),
182 last_day(to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)|| '01','YYYYMMDD'))
183 into g_pact
184 from pay_payroll_actions ppa
185 where ppa.payroll_action_id = p_payroll_action_id;
186 end if ;
187 --
188 if g_debug then
189 hr_utility.set_location(l_proc_name||' End of initialization_code',20);
190 end if;
191 exception
192 when others then
193 if g_debug then
194 hr_utility.set_location(l_proc_name||' Error in initialization code ',20);
195 end if;
196 raise;
197 end initialization_code;
198 ------------------------------------------------------------
199 -- Archive Code
200 ------------------------------------------------------------
201 procedure archive_code
202 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
203 p_effective_date in date)
204 is
205 --
206 l_assignment_id per_all_assignments_f.assignment_id%type;
207 l_payroll_id pay_payroll_actions.payroll_action_id%type ;
208 --------------------------
209 -- Tables for pay_balance_pkg
210 --------------------------
211 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
212 g_context_tab pay_balance_pkg.t_context_tab;
213 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
214 --
215 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
216 l_action_info_id pay_action_information.action_information_id%type;
217 l_ovn pay_action_information.object_version_number%type;
218 l_person_id per_all_people_f.person_id%type;
219 l_fwl_amt number;
220 l_spl_amt number; /* Bug: 3595103 */
221
222 l_1984_frozen_earnings number; /*Bug 3501915 */
223
224 ---------------------------------------------------------------------------
225 -- Employee Details cursor
226 -- Bug# 4226037 Included period_of_service_id join to fetch correct
227 -- actual_termination_date of an assignment.
228 ---------------------------------------------------------------------------
229 cursor csr_employee_details
230 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type )
231 is
232 select substr(pap.per_information1,1,22),
233 substr(pap.employee_number,1,15),
234 fnd_date.date_to_canonical(pps.actual_termination_date),
235 decode(g_pact.month_date,to_char(pap.start_date,'YYYYMM'),'NEW','EE') emp_status,
236 rpac.assignment_action_id,
237 fnd_date.date_to_canonical(rppa.effective_date),
238 pap.per_information6,
239 substr(hou.name,1,80),
240 nvl(pap.per_information14,pap.national_identifier),
241 fnd_date.date_to_canonical(pps.date_start),
242 pap.person_id,
243 fnd_date.date_to_canonical(paa.effective_start_date)
244 from pay_assignment_actions pac,
245 pay_payroll_actions rppa,
246 pay_assignment_actions rpac,
247 per_assignments_f paa,
248 per_people_f pap,
249 per_periods_of_service pps,
250 hr_organization_units hou
251 where pac.assignment_action_id = p_assignment_action_id
252 and pac.assignment_id = rpac.assignment_id
253 and rpac.payroll_action_id = rppa.payroll_action_id
254 and rppa.action_type in ('R','Q')
255 and rpac.action_status = 'C'
256 and rppa.effective_date between g_pact.start_date
257 and g_pact.end_date
258 and pac.assignment_id = paa.assignment_id
259 and rppa.effective_date between paa.effective_start_date
260 and paa.effective_end_date
261 and paa.person_id = pap.person_id
262 and rppa.effective_date between pap.effective_start_date
263 and pap.effective_end_date
264 and pap.person_id = pps.person_id
265 and paa.period_of_service_id = pps.period_of_service_id
266 and paa.organization_id = hou.organization_id
267 order by rppa.action_sequence desc;
268
269 begin
270 l_package := ' pay_sg_cpfline.';
271 l_proc_name := l_package || 'archive_code';
272 l_1984_frozen_earnings := 0;
273
274 ---------------------------------
275 -- Initializing rec_action_info
276 ---------------------------------
277 rec_action_info := null ;
278 --------------------------------
279 -- Populating rec_action_info with Employee Details
280 --------------------------------
281 if g_debug then
282 hr_utility.set_location(l_proc_name||' Start of archive_code',40);
283 end if;
284 --
285 open csr_employee_details( p_assignment_action_id ) ;
286 fetch csr_employee_details
287 into rec_action_info.action_information17, -- Legal Name
288 rec_action_info.action_information18, -- Employee Number
289 rec_action_info.action_information19, -- Termination Date
290 rec_action_info.action_information2 , -- Employee Status (EE/NEW)
291 l_asg_act_id ,
292 rec_action_info.action_information20, -- Employee payroll run date
293 rec_action_info.action_information21, -- Permit Type
294 rec_action_info.action_information22, -- Department
295 rec_action_info.action_information1, -- CPF Number
296 rec_action_info.action_information3, -- Hire Date
297 l_person_id ,
298 rec_action_info.action_information23; -- Assignment Effective Start Date
299 close csr_employee_details;
300 -------------------------------------------------------
301 -- Do a batch balance retrieval for better performance
302 -------------------------------------------------------
303 g_balance_value_tab.delete;
304 g_context_tab.delete;
305 g_detailed_bal_out_tab.delete;
306 --------------------------------------------------------------------------
307 -- Populating g_balance_value_tab with defined balance ids and tax unit id
308 --------------------------------------------------------------------------
309 g_balance_value_tab(1).defined_balance_id := get_def_bal_id('Voluntary CPF Liability');
310 g_balance_value_tab(2).defined_balance_id := get_def_bal_id('Voluntary CPF Withheld');
311 g_balance_value_tab(3).defined_balance_id := get_def_bal_id('CPF Liability');
312 g_balance_value_tab(4).defined_balance_id := get_def_bal_id('CPF Withheld');
313 g_balance_value_tab(5).defined_balance_id := get_def_bal_id('MBMF Withheld');
314 g_balance_value_tab(6).defined_balance_id := get_def_bal_id('SINDA Withheld');
315 g_balance_value_tab(7).defined_balance_id := get_def_bal_id('CDAC Withheld');
316 g_balance_value_tab(8).defined_balance_id := get_def_bal_id('ECF Withheld');
317 g_balance_value_tab(9).defined_balance_id := get_def_bal_id('CPF Ordinary Earnings Eligible Comp');
318 g_balance_value_tab(10).defined_balance_id := get_def_bal_id('CPF Additional Earnings Eligible Comp');
319 g_balance_value_tab(11).defined_balance_id := get_def_bal_id('Community Chest Withheld');
320 g_balance_value_tab(12).defined_balance_id := get_def_bal_id('SDL Liability');
321 g_balance_value_tab(13).defined_balance_id := get_def_bal_id('FWL Liability');
322 g_balance_value_tab(14).defined_balance_id := get_def_bal_id('S Pass Liability');
323 /* Bug# 3501915 */
324 g_balance_value_tab(15).defined_balance_id := get_def_bal_id('CPF Elig Comp 1984 Frozen Salary and Other Earnings');
325 --
326 for counter in 1..g_balance_value_tab.count loop
327 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
328 end loop;
329 -----------------------------------------
330 -- Batch Balance Retrival
331 -----------------------------------------
332 pay_balance_pkg.get_value( l_asg_act_id ,
333 g_balance_value_tab,
334 g_context_tab,
335 false,
336 false,
337 g_detailed_bal_out_tab );
338 ----------------------------------------------------------------------
339 -- Populating record rec_action_info with Balance Values
340 ----------------------------------------------------------------------
341 rec_action_info.action_information4 := g_detailed_bal_out_tab(1).balance_value; -- Voluntary CPF Liability
342 rec_action_info.action_information5 := g_detailed_bal_out_tab(2).balance_value; -- Voluntary CPF Withheld
343 rec_action_info.action_information6 := g_detailed_bal_out_tab(3).balance_value; -- CPF Liability
344 rec_action_info.action_information7 := g_detailed_bal_out_tab(4).balance_value; -- CPF Withheld
345 rec_action_info.action_information8 := g_detailed_bal_out_tab(5).balance_value; -- MBMF Withheld
346 rec_action_info.action_information9 := g_detailed_bal_out_tab(6).balance_value; -- SINDA Withheld
347 rec_action_info.action_information10 := g_detailed_bal_out_tab(7).balance_value; -- CDAC Withheld
348 rec_action_info.action_information11 := g_detailed_bal_out_tab(8).balance_value; -- ECF Withheld
349 rec_action_info.action_information12 := g_detailed_bal_out_tab(9).balance_value; -- CPF Ordinary Earnings Eligible Comp
350
351 ------------------------------------------------------------------------
352 -- Bug 3501915 - IF CPF Elig 1984 CPF Earnings exists and current month ordinary earnings are
353 -- greater than zero then report frozen earnings in magtape file
354 -------------------------------------------------------------------------
355 l_1984_frozen_earnings := g_detailed_bal_out_tab(15).balance_value; -- CPF Elig Comp 1984 Frozen Salary and Other Earnings
356 if l_1984_frozen_earnings > 0 and (rec_action_info.action_information12) > 0 then
357 rec_action_info.action_information12 := l_1984_frozen_earnings;
358 end if;
359
360 rec_action_info.action_information13 := g_detailed_bal_out_tab(10).balance_value; -- CPF Additional Earnings Eligible Comp
361 rec_action_info.action_information14 := g_detailed_bal_out_tab(11).balance_value; -- Community Chest Withheld
362 rec_action_info.action_information15 := g_detailed_bal_out_tab(12).balance_value; -- SDL Eligible Comp
363 /* Bug 3595103 - Archived sum of S Pass Liability, FWL Liability in pai.action_information16 */
364 l_fwl_amt := g_detailed_bal_out_tab(13).balance_value; -- FWL Liability
365 l_spl_amt := g_detailed_bal_out_tab(14).balance_value; -- S Pass Liability
366 rec_action_info.action_information16 := l_fwl_amt + l_spl_amt ;
367
368 ------------------------------------------------
369 -- Insert data into pay_action_information
370 ------------------------------------------------
371 if g_debug then
372 hr_utility.set_location(l_proc_name||' Before Insert into pay_action_information',40);
373 end if;
374 insert into pay_action_information (
375 action_information_id,
376 action_context_id,
377 action_context_type,
378 effective_date,
379 source_id,
380 tax_unit_id,
381 action_information_category,
382 action_information1,
383 action_information2,
384 action_information3,
385 action_information4,
386 action_information5,
387 action_information6,
388 action_information7,
389 action_information8,
390 action_information9,
391 action_information10,
392 action_information11,
393 action_information12,
394 action_information13,
395 action_information14,
396 action_information15,
397 action_information16,
398 action_information17,
399 action_information18,
400 action_information19,
401 action_information20,
402 action_information21,
403 action_information22,
404 action_information23)
405 values (
406 pay_action_information_s.nextval,
407 p_assignment_action_id,
408 'AAC',
409 fnd_date.canonical_to_date(rec_action_info.action_information20),
410 l_person_id,
411 g_pact.legal_entity_id,
412 'SG CPF DETAILS',
413 rec_action_info.action_information1,
414 rec_action_info.action_information2,
415 rec_action_info.action_information3,
416 rec_action_info.action_information4,
417 rec_action_info.action_information5,
418 rec_action_info.action_information6,
419 rec_action_info.action_information7,
420 rec_action_info.action_information8,
421 rec_action_info.action_information9,
422 rec_action_info.action_information10,
423 rec_action_info.action_information11,
424 rec_action_info.action_information12,
425 rec_action_info.action_information13,
426 rec_action_info.action_information14,
427 rec_action_info.action_information15,
428 rec_action_info.action_information16,
429 rec_action_info.action_information17,
430 rec_action_info.action_information18,
431 rec_action_info.action_information19,
432 rec_action_info.action_information20,
433 rec_action_info.action_information21,
434 rec_action_info.action_information22,
435 rec_action_info.action_information23 ) ;
436 if g_debug then
437 hr_utility.set_location(l_proc_name||' After Insert into pay_action_information',40);
438 end if;
439 --
440 if g_debug then
441 hr_utility.set_location(l_proc_name||' End of archive_code',40);
442 end if;
443 end archive_code ;
444 ---------------------------------
445 -- Deinitialization_code
446 -- Removes data from pay_action_information
447 -- table based on parameter value
448 -- Bug: 3619297 - Added check on action_context_type
449 ---------------------------------
450 procedure deinit_code
451 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
452 is
453 retain_archive_flag char(1) ;
454 begin
455 retain_archive_flag := 'N' ;
456 select pay_core_utils.get_parameter('RETAIN_ARCHIVE_DATA',ppa.legislative_parameters)
457 into retain_archive_flag
458 from pay_payroll_actions ppa
459 where ppa.payroll_action_id = p_payroll_action_id ;
460 --
461 if retain_archive_flag = 'N' then
462 delete from pay_action_information
463 where action_context_id in ( select assignment_action_id
464 from pay_assignment_actions
465 where payroll_action_id = p_payroll_action_id )
466 and action_information_category = 'SG CPF DETAILS'
467 and action_context_type = 'AAC';
468 end if ;
469 --
470 end deinit_code ;
471 begin
472 -------------------------------------------
473 -- package body level code
474 -- Populates defined Balance ids
475 -- Bug 3595103 - Added new balance S Pass Liability
476 -------------------------------------------
477 select pdb.defined_balance_id,pbt.balance_name
478 bulk collect into g_def_bal_id , g_def_bal_name
479 from pay_balance_types pbt,
480 pay_defined_balances pdb,
481 pay_balance_dimensions pbd
482 where pbt.legislation_code = 'SG'
483 and pbd.legislation_code = pbt.legislation_code
484 and pdb.legislation_code = pbt.legislation_code
485 and pbt.balance_type_id = pdb.balance_type_id
486 and pbd.balance_dimension_id = pdb.balance_dimension_id
487 and pbd.dimension_name = '_ASG_LE_MONTH'
488 and pbt.balance_name in ('CDAC Withheld',
489 'CPF Additional Earnings Eligible Comp',
490 'CPF Liability',
491 'CPF Ordinary Earnings Eligible Comp',
492 'CPF Withheld',
493 'Community Chest Withheld',
494 'ECF Withheld',
495 'FWL Liability',
496 'S Pass Liability',
497 'MBMF Withheld',
498 'SDL Liability',
499 'SINDA Withheld',
500 'Voluntary CPF Liability',
501 'Voluntary CPF Withheld',
502 'CPF Elig Comp 1984 Frozen Salary and Other Earnings') /*Bug 3501915 */
503 order by 2 ;
504
505 end pay_sg_cpfline;