[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_CPFLINE
Source
1 package body pay_sg_cpfline as
2 /* $Header: pysgcpfl.pkb 120.6.12020000.3 2012/10/08 07:05:06 jalin ship $ */
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 csn varchar2(15),
20 Start_date date ,
21 End_date date
22 );
23 --
24 g_pact t_pact;
25 -----------------------------------------------------
26 -- record type to hold archival information
27 -----------------------------------------------------
28 rec_action_info pay_action_information%rowtype ;
29 -----------------------------------------------------
30 -- Table to store Defined Balance details
31 -----------------------------------------------------
32 type t_def_bal_id is table of pay_defined_balances.defined_balance_id%type ;
33 type t_def_bal_name is table of pay_balance_types.balance_name%type ;
34 --
35 g_def_bal_id t_def_bal_id ;
36 g_def_bal_name t_def_bal_name ;
37 ----------------------------------------------------------------
38 -- Function Returns Defined Balance id
39 ----------------------------------------------------------------
40 function get_def_bal_id
41 ( p_def_bal_name in pay_balance_types.balance_name%type ) return number
42 is
43 begin
44 for i in 1..g_def_bal_name.count
45 loop
46 if g_def_bal_name(i) =p_def_bal_name then
47 return g_def_bal_id(i) ;
48 end if ;
49 end loop ;
50 -- if reached here
51 raise_application_error(-20001 , ' Program Error : Defined Balance not found ') ;
52 end get_def_bal_id ;
53 --------------------------------------------------------------------
54 -- These are PUBLIC procedures are required by the Archive process.
55 -- Their names are stored in PAY_REPORT_FORMAT_MAPPINGS_F so that
56 -- the archive process knows what code to execute for each step of
57 -- the archive.
58 --------------------------------------------------------------------
59 procedure range_code
60 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
61 p_sql out nocopy varchar2)
62 is
63 c_range_cursor constant varchar2(3000) :=
64 ' select distinct pap.person_id
65 from pay_payroll_actions ppa,
66 per_people_f pap
67 where ppa.payroll_action_id = :payroll_action_id
68 and pap.business_group_id = ppa.business_group_id
69 order by pap.person_id ' ;
70 begin
71 p_sql := c_range_cursor ;
72 end range_code ;
73 ------------------------------------------------------------
74 -- Assignment Action Code
75 ------------------------------------------------------------
76 procedure assignment_action_code
77 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
78 p_start_person_id in per_all_people_f.person_id%type,
79 p_end_person_id in per_all_people_f.person_id%type,
80 p_chunk in number )
81 is
82
83 l_next_action_id pay_assignment_actions.assignment_action_id%type;
84 --
85 ------------------------------------------------------------------------
86 --Bug#3833818 Added payroll_id join to improve performance of the query.
87 --Bug #14654315, added condition if multi CSN exists
88 ------------------------------------------------------------------------
89 cursor c_assact
90 is
91 select distinct paa.assignment_id
92 from pay_payroll_actions xppa,
93 pay_payroll_actions rppa,
94 pay_assignment_actions rpac,
95 per_assignments_f paa,
96 per_assignment_extra_info aei1,
97 per_assignment_extra_info aei2,
98 hr_organization_information hoi
99 where xppa.payroll_action_id = p_payroll_action_id
100 and paa.person_id between p_start_person_id
101 and p_end_person_id
102 and rppa.business_group_id = g_pact.business_group_id
103 and rppa.payroll_id in ( select payroll_id
104 from pay_payrolls_f
105 where business_group_id = g_pact.business_group_id )
106 and rppa.effective_date between g_pact.start_date
107 and g_pact.end_date
108 and rppa.action_type in ('R','Q')
109 and rpac.action_status = 'C'
110 and rppa.payroll_action_id = rpac.payroll_action_id
111 and rpac.tax_unit_id = g_pact.legal_entity_id
112 and rpac.assignment_id = paa.assignment_id
113 and rppa.effective_date between paa.effective_start_date
114 and paa.effective_end_date
115 and hoi.organization_id = rpac.tax_unit_id
116 and hoi.org_information_context = 'SG_LEGAL_ENTITY'
117 and paa.assignment_id = aei1.assignment_id(+)
118 and aei1.information_type(+) = 'HR_MULTI_CSN_SG'
119 and substr(aei1.aei_information1(+),11,3)='PTE'
120 and paa.assignment_id = aei2.assignment_id(+)
121 and aei2.information_type(+) = 'HR_MULTI_CSN_SG'
122 and substr(aei2.aei_information1(+),11,3)='VCT'
123 and (hoi.org_information10=g_pact.csn
124 or aei1.aei_information1=g_pact.csn
125 or aei2.aei_information1=g_pact.csn)
126 and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei1.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei1.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
127 and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei2.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei2.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'));
128 --
129 cursor next_action_id
130 is
131 select pay_assignment_actions_s.nextval
132 from dual;
133 --
134 begin
135 l_package := ' pay_sg_cpfline.';
136 l_proc_name := l_package || 'assignment_action_code';
137 pay_sg_cpfline.initialization_code(p_payroll_action_id) ;
138 --
139 if g_debug then
140 hr_utility.set_location(l_proc_name || ' Start of assignment_action_code',30);
141 end if;
142 --
143 for i in c_assact
144 loop
145 open next_action_id;
146 fetch next_action_id into l_next_action_id;
147 close next_action_id;
148 --
149 if g_debug then
150 hr_utility.set_location(l_proc_name|| ' Before calling hr_nonrun_asact.insact',30);
151 end if;
152 --
153 hr_nonrun_asact.insact( l_next_action_id,
154 i.assignment_id,
155 p_payroll_action_id,
156 p_chunk,
157 g_pact.legal_entity_id );
158 --
159 if g_debug then
160 hr_utility.set_location(l_proc_name||' After calling hr_nonrun_asact.insact',30);
161 end if;
162 --
163 end loop;
164 --
165 if g_debug then
166 hr_utility.set_location(l_proc_name|| ' End of assignment_action_code',30);
167 end if;
168 exception
169 when others then
170 if g_debug then
171 hr_utility.set_location(l_proc_name||' Error raised in assignment_action_code procedure',30);
172 end if;
173 raise;
174 end assignment_action_code ;
175 ------------------------------------------------------------
176 -- Assignment Action Code
177 ------------------------------------------------------------
178 procedure initialization_code
179 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
180 is
181
182 begin
183 l_package := ' pay_sg_cpfline.';
184 l_proc_name := l_package || 'initialization_code';
185 g_debug := hr_utility.debug_enabled;
186 --
187 if g_debug then
188 hr_utility.set_location(l_proc_name||' Start of initialization_code',20);
189 end if;
190 --
191 if g_pact.report_type is null then
192 select ppa.report_type,
193 ppa.report_qualifier,
194 ppa.report_category,
195 ppa.business_group_id,
196 ppa.effective_date,
197 to_number(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)) month_date,
198 to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters)) legal_entity_id,
199 replace(pay_core_utils.get_parameter('CSN',ppa.legislative_parameters),'#',' ') csn,
200 to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)||'01','YYYYMMDD'),
201 last_day(to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)|| '01','YYYYMMDD'))
202 into g_pact
203 from pay_payroll_actions ppa
204 where ppa.payroll_action_id = p_payroll_action_id;
205 end if ;
206 --
207 if g_debug then
208 hr_utility.set_location(l_proc_name||' End of initialization_code',20);
209 end if;
210 exception
211 when others then
212 if g_debug then
213 hr_utility.set_location(l_proc_name||' Error in initialization code ',20);
214 end if;
215 raise;
216 end initialization_code;
217 ------------------------------------------------------------
218 -- Archive Code
219 ------------------------------------------------------------
220 procedure archive_code
221 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
222 p_effective_date in date)
223 is
224 --
225 l_assignment_id per_all_assignments_f.assignment_id%type;
226 l_payroll_id pay_payroll_actions.payroll_action_id%type ;
227 --------------------------
228 -- Tables for pay_balance_pkg
229 --------------------------
230 g_balance_value_tab pay_balance_pkg.t_balance_value_tab;
231 g_context_tab pay_balance_pkg.t_context_tab;
232 g_detailed_bal_out_tab pay_balance_pkg.t_detailed_bal_out_tab;
233 --
234 l_asg_act_id pay_assignment_actions.assignment_action_id%type;
235 l_action_info_id pay_action_information.action_information_id%type;
236 l_ovn pay_action_information.object_version_number%type;
237 l_person_id per_all_people_f.person_id%type;
238 l_fwl_amt number;
239 l_spl_amt number; /* Bug: 3595103 */
240
241 l_1984_frozen_earnings number; /*Bug 3501915 */
242
243 ---------------------------------------------------------------------------
244 -- Employee Details cursor
245 -- Bug# 4226037 Included period_of_service_id join to fetch correct
246 -- actual_termination_date of an assignment.
247 ---------------------------------------------------------------------------
248 cursor csr_employee_details
249 ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type )
250 is
251 select substr(pap.per_information1,1,22),
252 substr(pap.employee_number,1,15),
253 fnd_date.date_to_canonical(pps.actual_termination_date),
254 decode(g_pact.month_date,to_char(pap.start_date,'YYYYMM'),'NEW','EE') emp_status,
255 rpac.assignment_action_id,
256 fnd_date.date_to_canonical(rppa.effective_date),
257 pap.per_information6,
258 substr(hou.name,1,80),
259 nvl(pap.per_information14,pap.national_identifier),
260 fnd_date.date_to_canonical(pps.date_start),
261 pap.person_id,
262 fnd_date.date_to_canonical(paa.effective_start_date),
263 nvl(aei1.aei_information1,hoi.org_information10),
264 aei2.aei_information1
265 from pay_assignment_actions pac,
266 pay_payroll_actions rppa,
267 pay_assignment_actions rpac,
268 per_assignments_f paa,
269 per_assignment_extra_info aei1,
270 per_assignment_extra_info aei2,
271 per_people_f pap,
272 per_periods_of_service pps,
273 hr_organization_units hou,
274 hr_organization_information hoi
275 where pac.assignment_action_id = p_assignment_action_id
276 and pac.assignment_id = rpac.assignment_id
277 and rpac.payroll_action_id = rppa.payroll_action_id
278 and rppa.action_type in ('R','Q')
279 and rpac.action_status = 'C'
280 and rppa.effective_date between g_pact.start_date
281 and g_pact.end_date
282 and pac.assignment_id = paa.assignment_id
283 and rppa.effective_date between paa.effective_start_date
284 and paa.effective_end_date
285 and paa.person_id = pap.person_id
286 and rppa.effective_date between pap.effective_start_date
287 and pap.effective_end_date
288 and pap.person_id = pps.person_id
289 and paa.period_of_service_id = pps.period_of_service_id
290 and paa.organization_id = hou.organization_id
291 and hoi.organization_id = g_pact.legal_entity_id
292 and hoi.org_information_context = 'SG_LEGAL_ENTITY'
293 and paa.assignment_id = aei1.assignment_id(+)
294 and aei1.information_type(+) = 'HR_MULTI_CSN_SG'
295 and substr(aei1.aei_information1(+),11,3)='PTE'
296 and paa.assignment_id = aei2.assignment_id(+)
297 and aei2.information_type(+) = 'HR_MULTI_CSN_SG'
298 and substr(aei2.aei_information1(+),11,3)='VCT'
299 and (hoi.org_information10=g_pact.csn
300 or aei1.aei_information1=g_pact.csn
301 or aei2.aei_information1=g_pact.csn)
302 and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei1.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei1.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
303 and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei2.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei2.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
304 order by rppa.action_sequence desc;
305
306 begin
307 l_package := ' pay_sg_cpfline.';
308 l_proc_name := l_package || 'archive_code';
309 l_1984_frozen_earnings := 0;
310
311 ---------------------------------
312 -- Initializing rec_action_info
313 ---------------------------------
314 rec_action_info := null ;
315 --------------------------------
316 -- Populating rec_action_info with Employee Details
317 --------------------------------
318 if g_debug then
319 hr_utility.set_location(l_proc_name||' Start of archive_code',40);
320 end if;
321 --
322 open csr_employee_details( p_assignment_action_id ) ;
323 fetch csr_employee_details
324 into rec_action_info.action_information17, -- Legal Name
325 rec_action_info.action_information18, -- Employee Number
326 rec_action_info.action_information19, -- Termination Date
327 rec_action_info.action_information2 , -- Employee Status (EE/NEW)
328 l_asg_act_id ,
329 rec_action_info.action_information20, -- Employee payroll run date
330 rec_action_info.action_information21, -- Permit Type
331 rec_action_info.action_information22, -- Department
332 rec_action_info.action_information1, -- CPF Number
333 rec_action_info.action_information3, -- Hire Date
334 l_person_id ,
335 rec_action_info.action_information23, -- Assignment Effective Start Date
336 rec_action_info.action_information24,
337 rec_action_info.action_information25;
338 close csr_employee_details;
339 -------------------------------------------------------
340 -- Do a batch balance retrieval for better performance
341 -------------------------------------------------------
342 g_balance_value_tab.delete;
343 g_context_tab.delete;
344 g_detailed_bal_out_tab.delete;
345 --------------------------------------------------------------------------
346 -- Populating g_balance_value_tab with defined balance ids and tax unit id
347 --------------------------------------------------------------------------
348 g_balance_value_tab(1).defined_balance_id := get_def_bal_id('Voluntary CPF Liability');
349 g_balance_value_tab(2).defined_balance_id := get_def_bal_id('Voluntary CPF Withheld');
350 g_balance_value_tab(3).defined_balance_id := get_def_bal_id('CPF Liability');
351 g_balance_value_tab(4).defined_balance_id := get_def_bal_id('CPF Withheld');
352 g_balance_value_tab(5).defined_balance_id := get_def_bal_id('MBMF Withheld');
353 g_balance_value_tab(6).defined_balance_id := get_def_bal_id('SINDA Withheld');
354 g_balance_value_tab(7).defined_balance_id := get_def_bal_id('CDAC Withheld');
355 g_balance_value_tab(8).defined_balance_id := get_def_bal_id('ECF Withheld');
356 g_balance_value_tab(9).defined_balance_id := get_def_bal_id('CPF Ordinary Earnings Eligible Comp');
357 g_balance_value_tab(10).defined_balance_id := get_def_bal_id('CPF Additional Earnings Eligible Comp');
358 g_balance_value_tab(11).defined_balance_id := get_def_bal_id('Community Chest Withheld');
359 g_balance_value_tab(12).defined_balance_id := get_def_bal_id('SDL Liability');
360 g_balance_value_tab(13).defined_balance_id := get_def_bal_id('FWL Liability');
361 g_balance_value_tab(14).defined_balance_id := get_def_bal_id('S Pass Liability');
362 /* Bug# 3501915 */
363 g_balance_value_tab(15).defined_balance_id := get_def_bal_id('CPF Elig Comp 1984 Frozen Salary and Other Earnings');
364 --
365 for counter in 1..g_balance_value_tab.count loop
366 g_context_tab(counter).tax_unit_id := g_pact.legal_entity_id;
367 end loop;
368 -----------------------------------------
369 -- Batch Balance Retrival
370 -----------------------------------------
371 pay_balance_pkg.get_value( l_asg_act_id ,
372 g_balance_value_tab,
373 g_context_tab,
374 false,
375 false,
376 g_detailed_bal_out_tab );
377 ------------------------------------------------------------------
378 -- Populating record rec_action_info with Balance Values
379 ------------------------------------------------------------------
380 if substr(g_pact.csn,11,3) <> 'VCT' then /* Bug 12919156, removed 'PTE' condition */
381 if rec_action_info.action_information25 is null then
382 rec_action_info.action_information4 := g_detailed_bal_out_tab(1).balance_value; -- Voluntary CPF Liability
383 rec_action_info.action_information5 := g_detailed_bal_out_tab(2).balance_value; -- Voluntary CPF Withheld
384 else
385 rec_action_info.action_information4 := 0; -- Voluntary CPF Liability
386 rec_action_info.action_information5 := 0; -- Voluntary CPF Withheld
387 end if;
388 rec_action_info.action_information6 := g_detailed_bal_out_tab(3).balance_value; -- CPF Liability
389 rec_action_info.action_information7 := g_detailed_bal_out_tab(4).balance_value; -- CPF Withheld
390 end if;
391 if substr(g_pact.csn,11,3)='VCT' then
392 rec_action_info.action_information6 := 0; -- CPF Liability
393 rec_action_info.action_information7 := 0; -- CPF Withheld
394 rec_action_info.action_information4 := g_detailed_bal_out_tab(1).balance_value; -- Voluntary CPF Liability
395 rec_action_info.action_information5 := g_detailed_bal_out_tab(2).balance_value; -- Voluntary CPF Withheld
396 end if;
397 rec_action_info.action_information8 := g_detailed_bal_out_tab(5).balance_value; -- MBMF Withheld
398 rec_action_info.action_information9 := g_detailed_bal_out_tab(6).balance_value; -- SINDA Withheld
399 rec_action_info.action_information10 := g_detailed_bal_out_tab(7).balance_value; -- CDAC Withheld
400 rec_action_info.action_information11 := g_detailed_bal_out_tab(8).balance_value; -- ECF Withheld
401 rec_action_info.action_information12 := g_detailed_bal_out_tab(9).balance_value; -- CPF Ordinary Earnings Eligible Comp
402
403 -------------------------------------------------------------------
404 -- Bug 3501915 - IF CPF Elig 1984 CPF Earnings exists and current month ordinary earnings are
405 -- greater than zero then report frozen earnings in magtape file
406 -------------------------------------------------------------------
407 l_1984_frozen_earnings := g_detailed_bal_out_tab(15).balance_value; -- CPF Elig Comp 1984 Frozen Salary and Other Earnings
408 if l_1984_frozen_earnings > 0 and (rec_action_info.action_information12) > 0 then
409 rec_action_info.action_information12 := l_1984_frozen_earnings;
410 end if;
411
412 rec_action_info.action_information13 := g_detailed_bal_out_tab(10).balance_value; -- CPF Additional Earnings Eligible Comp
413 rec_action_info.action_information14 := g_detailed_bal_out_tab(11).balance_value; -- Community Chest Withheld
414 rec_action_info.action_information15 := g_detailed_bal_out_tab(12).balance_value; -- SDL Eligible Comp
415 /* Bug 3595103 - Archived sum of S Pass Liability, FWL Liability in pai.action_information16 */
416 l_fwl_amt := g_detailed_bal_out_tab(13).balance_value; -- FWL Liability
417 l_spl_amt := g_detailed_bal_out_tab(14).balance_value; -- S Pass Liability
418 rec_action_info.action_information16 := l_fwl_amt + l_spl_amt ;
419
420 ------------------------------------------------
421 -- Insert data into pay_action_information
422 ------------------------------------------------
423 if g_debug then
424 hr_utility.set_location(l_proc_name||' Before Insert into pay_action_information',40);
425 end if;
426 insert into pay_action_information (
427 action_information_id,
428 action_context_id,
429 action_context_type,
430 effective_date,
431 source_id,
432 tax_unit_id,
433 action_information_category,
434 action_information1,
435 action_information2,
436 action_information3,
437 action_information4,
438 action_information5,
439 action_information6,
440 action_information7,
441 action_information8,
442 action_information9,
443 action_information10,
444 action_information11,
445 action_information12,
446 action_information13,
447 action_information14,
448 action_information15,
449 action_information16,
450 action_information17,
451 action_information18,
452 action_information19,
453 action_information20,
454 action_information21,
455 action_information22,
456 action_information23,
457 action_information24,
458 action_information25)
459 values (
460 pay_action_information_s.nextval,
461 p_assignment_action_id,
462 'AAC',
463 fnd_date.canonical_to_date(rec_action_info.action_information20),
464 l_person_id,
465 g_pact.legal_entity_id,
466 'SG CPF DETAILS',
467 rec_action_info.action_information1,
468 rec_action_info.action_information2,
469 rec_action_info.action_information3,
470 rec_action_info.action_information4,
471 rec_action_info.action_information5,
472 rec_action_info.action_information6,
473 rec_action_info.action_information7,
474 rec_action_info.action_information8,
475 rec_action_info.action_information9,
476 rec_action_info.action_information10,
477 rec_action_info.action_information11,
478 rec_action_info.action_information12,
479 rec_action_info.action_information13,
480 rec_action_info.action_information14,
481 rec_action_info.action_information15,
482 rec_action_info.action_information16,
483 rec_action_info.action_information17,
484 rec_action_info.action_information18,
485 rec_action_info.action_information19,
486 rec_action_info.action_information20,
487 rec_action_info.action_information21,
488 rec_action_info.action_information22,
489 rec_action_info.action_information23,
490 rec_action_info.action_information24,
491 rec_action_info.action_information25) ;
492 if g_debug then
493 hr_utility.set_location(l_proc_name||' After Insert into pay_action_information',40);
494 end if;
495 --
496 if g_debug then
497 hr_utility.set_location(l_proc_name||' End of archive_code',40);
498 end if;
499 end archive_code ;
500 ---------------------------------
501 -- Deinitialization_code
502 -- Removes data from pay_action_information
503 -- table based on parameter value
504 -- Bug: 3619297 - Added check on action_context_type
505 ---------------------------------
506 procedure deinit_code
507 ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type )
508 is
509 retain_archive_flag char(1) ;
510 begin
511 retain_archive_flag := 'N' ;
512 select pay_core_utils.get_parameter('RETAIN_ARCHIVE_DATA',ppa.legislative_parameters)
513 into retain_archive_flag
514 from pay_payroll_actions ppa
515 where ppa.payroll_action_id = p_payroll_action_id ;
516 --
517 if retain_archive_flag = 'N' then
518 delete from pay_action_information
519 where action_context_id in ( select assignment_action_id
520 from pay_assignment_actions
521 where payroll_action_id = p_payroll_action_id )
522 and action_information_category = 'SG CPF DETAILS'
523 and action_context_type = 'AAC';
524 end if ;
525 --
526 end deinit_code ;
527
528 ---------------------------------------------------------------------------
529 -- Bug 7532687 The function to check if the CPF CSN is invalid
530 ---------------------------------------------------------------------------
531 function check_cpf_number (p_er_cpf_number in varchar2,
532 p_er_cpf_category in varchar2,
533 p_er_payer_id in varchar2) return char is
534
535 l_return varchar2(1);
536 l_cpf_num_uen varchar2(10);
537 l_cpf_num_pc varchar2(3);
538 l_cpf_num_pc_s varchar2(2);
539 l_cpf_category varchar2(20);
540 l_payer_id_type varchar2(1);
541 l_year number;
542 begin
543
544 if g_debug then
545 hr_utility.set_location('pay_sg_cpfline: Start of check_cpf_number',10);
546 end if;
547
548 l_cpf_num_uen := substr(p_er_cpf_number,1,10);
549 l_cpf_num_pc := substr(p_er_cpf_number,11,3);
550 l_cpf_num_pc_s := substr(p_er_cpf_number,14,2);
551 l_cpf_category := p_er_cpf_category;
552 l_payer_id_type := p_er_payer_id;
553
554 l_return := 'Z';
555
556 if length(p_er_cpf_number) = 15 then
557 if l_payer_id_type = 'U' then
558 if (substr(l_cpf_num_uen, 1, 1) = 'S' or
559 substr(l_cpf_num_uen, 1, 1) = 'T') then
560 if pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 2, 2)) and
561 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 4, 2)) and
562 pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 6,4)) and
563 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen,10,1)) then
564 null;
565 else
566 l_return := 'N';
567 end if;
568 else
569 l_return := 'N';
570 end if;
571 elsif l_payer_id_type = '7' then
572 if pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen,1,8)) and
573 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen,9,1)) and
574 substr(l_cpf_num_uen,10,1) = ' ' then
575 null;
576 else
577 l_return := 'N';
578 end if;
579 elsif l_payer_id_type = '8' then
580 l_year := to_number(substr(l_cpf_num_uen, 1, 4));
581 if ((l_year >= 1900 and l_year < 4712) and
582 pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 5, 5)) and
583 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 10, 1))) or
584 (substr(l_cpf_num_uen, 1, 1) = 'F' and
585 pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 2, 8)) and
586 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_uen, 10, 1))) then
587 null;
588 else
589 l_return := 'N';
590 end if;
591 else
592 l_return := 'N';
593 end if;
594 else
595 l_return := 'N';
596 end if;
597
598 if l_return <> 'N' then
599 if l_cpf_category = 'A' then
600 if l_cpf_num_pc = 'PTE' or l_cpf_num_pc = 'AMS' or
601 l_cpf_num_pc = 'VCT' or l_cpf_num_pc = 'VSE' or
602 l_cpf_num_pc = ' MSE' then
603 null;
604 else
605 l_return := 'N';
606 end if;
607 else
608 if not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_pc,1,1)) and
609 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_pc,2,1)) and
610 not pay_sg_iras_archive.check_is_number(substr(l_cpf_num_pc,3,1)) then
611 null;
612 else
613 l_return := 'N';
614 end if;
615 end if;
616 end if;
617
618 if l_return <> 'N' then
619 if pay_sg_iras_archive.check_is_number(substr(l_cpf_num_pc_s,1,1)) and
620 pay_sg_iras_archive.check_is_number(substr(l_cpf_num_pc_s,2,1)) then
621 null;
622 else
623 l_return := 'N';
624 end if;
625 end if;
626
627 if g_debug then
628 hr_utility.set_location('pay_sg_cpfline: End of check_cpf_number',20);
629 end if;
630
631 return l_return;
632 end check_cpf_number;
633
634
635
636 begin
637 -------------------------------------------
638 -- package body level code
639 -- Populates defined Balance ids
640 -- Bug 3595103 - Added new balance S Pass Liability
641 -------------------------------------------
642 select pdb.defined_balance_id,pbt.balance_name
643 bulk collect into g_def_bal_id , g_def_bal_name
644 from pay_balance_types pbt,
645 pay_defined_balances pdb,
646 pay_balance_dimensions pbd
647 where pbt.legislation_code = 'SG'
648 and pbd.legislation_code = pbt.legislation_code
649 and pdb.legislation_code = pbt.legislation_code
650 and pbt.balance_type_id = pdb.balance_type_id
651 and pbd.balance_dimension_id = pdb.balance_dimension_id
652 and pbd.dimension_name = '_ASG_LE_MONTH'
653 and pbt.balance_name in ('CDAC Withheld',
654 'CPF Additional Earnings Eligible Comp',
655 'CPF Liability',
656 'CPF Ordinary Earnings Eligible Comp',
657 'CPF Withheld',
658 'Community Chest Withheld',
659 'ECF Withheld',
660 'FWL Liability',
661 'S Pass Liability',
662 'MBMF Withheld',
663 'SDL Liability',
664 'SINDA Withheld',
665 'Voluntary CPF Liability',
666 'Voluntary CPF Withheld',
667 'CPF Elig Comp 1984 Frozen Salary and Other Earnings') /*Bug 3501915 */
668 order by 2 ;
669
670 end pay_sg_cpfline;