1 package body pay_sg_cpfline_balances as
2 /* $Header: pysgcpfb.pkb 120.5 2011/05/27 06:05:30 jalin ship $ */
3 g_debug boolean;
4 --
5 l_package VARCHAR2(100);
6 l_proc_name VARCHAR2(100) ;
7 ------------------------------------------------------
8 -- Record Used in function dup_bal_value
9 -- This is used to store the balance value once for all
10 -- the balances and then returned for subsequent columns
11 -- in the existing /new employees cursor
12 -- Bug No:3298317 Added column permit_type
13 ------------------------------------------------------
14 TYPE t_cpf_balances IS RECORD
15 ( assact_id pay_assignment_actions.assignment_action_id%type
16 ,vol_cpf_liab number -- action_information4
17 ,cpf_liab number -- action_information6
18 ,vol_cpf_wheld number -- action_information5
19 ,cpf_wheld number -- action_information7
20 ,mbmf_wheld number -- action_information8
21 ,sinda_wheld number -- action_information9
22 ,cdac_wheld number -- action_information10
23 ,ecf_wheld number -- action_information11
24 ,cpf_ord_earn number -- action_information12
25 ,cpf_addl_earn number -- action_information13
26 ,permit_type per_people_f.per_information6%type -- action_information21
27 ) ;
28
29 g_cpf_balances t_cpf_balances ;
30 ----------------------------------------------------------------------------
31 -- Global Variables used to retain balance values in multiple function calls
32 -- Used in function balance_amount
33 ----------------------------------------------------------------------------
34 g_cpf_with_bal_value number;
35 g_cpf_liab_bal_value number;
36 g_vol_cpf_with_bal_value number;
37 g_vol_cpf_liab_bal_value number;
38 g_comm_chest_with_bal_value number;
39 g_sdl_liab_bal_value number;
40 g_mbmf_with_bal_value number;
41 g_fwl_liab_bal_value number;
42 g_sinda_with_bal_value number;
43 g_cdac_with_bal_value number;
44 g_ecf_with_bal_value number;
45
46 fwl_reporting varchar2(1);
47 -------------------------------------------------------------
48 --Bug# 3501950
49 -- This function is called from company_identification cursor
50 -------------------------------------------------------------
51 function get_cpf_interest
52 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
53 return varchar2
54 is
55 l_cpf_interest varchar2(100);
56 begin
57 select nvl(pay_core_utils.get_parameter('CPF_INTEREST',legislative_parameters),0)
58 into l_cpf_interest
59 from pay_payroll_actions
60 where payroll_action_id = c_payroll_action_id;
61 return l_cpf_interest;
62 end get_cpf_interest;
63 -------------------------------------------------------------
64 --Bug# 3501950
65 -- This function is called from company_identification cursor
66 -------------------------------------------------------------
67 function get_fwl_interest
68 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type)
69 return varchar2
70 is
71 l_fwl_interest varchar2(100);
72 begin
73 select nvl(pay_core_utils.get_parameter('FWL_INTEREST',legislative_parameters),0)
74 into l_fwl_interest
75 from pay_payroll_actions
76 where payroll_action_id = c_payroll_action_id;
77 return l_fwl_interest;
78 end get_fwl_interest;
79 -----------------------------------------------
80 -- Function balance_amount
81 -- Returns Balance amount. called from stat_type_amount
82 -- Bug 10634286, modified cursor balance_amount to separate CSN
83 -----------------------------------------------
84 function balance_amount
85 ( p_payroll_action_id in number,
86 p_balance_name in varchar2 ) return number
87 is
88 l_balance_amount number;
89 /* Bug: 3595103 Modified cursor for permit type 'SP' */
90 /* Bug: 5937727 Modified the cursor to exclude negative values from summation */
91 cursor balance_amount
92 is
93 select to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
94 decode(sign(to_number(pai.action_information7)),1,to_number(pai.action_information7),0))) )),'99999999'),
95 to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
96 decode(sign(to_number(pai.action_information6)),1,to_number(pai.action_information6),0))) )),'99999999'),
97 to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
98 decode(sign(to_number(pai.action_information5)),1,to_number(pai.action_information5),0))) )),'99999999'),
99 to_char(sum(decode( pai.action_information1,null,0,(decode(pai.action_information21,'WP',0,'EP',0,'SP',0,
100 decode(to_number(sign(pai.action_information4)),1,to_number(pai.action_information4),0))) )),'99999999'),
101 nvl(to_char(sum(decode(sign(to_number(pai.action_information14)),1,to_number(pai.action_information14),0)),'999999.99'),0),
102 nvl(to_char(sum(decode(sign(to_number(pai.action_information15)),1,to_number(pai.action_information15),0)),'999999.99'),0),
103 nvl(to_char(sum(decode(sign(to_number(pai.action_information8)),1,to_number(pai.action_information8),0)),'999999.99'),0),
104 nvl(to_char(sum(decode(sign(to_number(pai.action_information16)),1,to_number(pai.action_information16),0)),'999999.99'),0),
105 nvl(to_char(sum(decode(sign(to_number(pai.action_information9)),1,to_number(pai.action_information9),0)),'999999.99'),0),
106 nvl(to_char(sum(decode(sign(to_number(pai.action_information10)),1,to_number(pai.action_information10),0)),'999999.99'),0),
107 nvl(to_char(sum(decode(sign(to_number(pai.action_information11)),1,to_number(pai.action_information11),0)),'999999.99'),0)
108 from pay_payroll_actions ppa,
109 pay_assignment_actions paa,
110 pay_action_information pai
111 where ppa.payroll_action_id = p_payroll_action_id
112 and ppa.payroll_action_id = paa.payroll_action_id
113 and paa.assignment_action_id = pai.action_context_id
114 and pai.action_information_category = 'SG CPF DETAILS'
115 and pai.action_context_type = 'AAC'
116 and ((pai.action_information24 = replace(pay_magtape_generic.get_parameter_value('CSN'),'#',' ') and (pai.action_information25 is null
117 or (pai.action_information25 is not null
118 and (pai.action_information6 <> 0
119 or pai.action_information7 <> 0))))
120 or
124 is
121 (pai.action_information25 = replace(pay_magtape_generic.get_parameter_value('CSN'),'#',' ') ));
122
123 cursor fwl_amount_reporting
125 select nvl(hoi.org_information20,'Y')
126 from hr_organization_information hoi,
127 pay_payroll_actions ppa
128 where ppa.payroll_action_id = p_payroll_action_id
129 and hoi.organization_id =
130 to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',
131 ppa.legislative_parameters))
132 and hoi.org_information_context = 'SG_LEGAL_ENTITY';
133 begin
134 l_package := ' pay_sg_cpfline.';
135 l_proc_name := l_package || 'balance_amount';
136 if g_debug then
137 hr_utility.set_location(l_proc_name || ' Start of balance_amount',60);
138 end if;
139 --
140 if g_cpf_with_bal_value is null then
141 open balance_amount ;
142 fetch balance_amount into g_cpf_with_bal_value,
143 g_cpf_liab_bal_value,
144 g_vol_cpf_with_bal_value,
145 g_vol_cpf_liab_bal_value,
146 g_comm_chest_with_bal_value,
147 g_sdl_liab_bal_value,
148 g_mbmf_with_bal_value,
149 g_fwl_liab_bal_value,
150 g_sinda_with_bal_value,
151 g_cdac_with_bal_value,
152 g_ecf_with_bal_value ;
153 close balance_amount;
154 end if;
155 --
156 if g_debug then
157
158 hr_utility.set_location(l_proc_name || ' End of balance_amount',60);
159 end if;
160 --
161 if p_balance_name = 'CPF Withheld' then
162 return g_cpf_with_bal_value;
163 elsif p_balance_name = 'CPF Liability' then
164 return g_cpf_liab_bal_value;
165 elsif p_balance_name = 'Voluntary CPF Withheld' then
166 return g_vol_cpf_with_bal_value;
167 elsif p_balance_name = 'Voluntary CPF Liability' then
168 return g_vol_cpf_liab_bal_value;
169 elsif p_balance_name = 'Community Chest Withheld' then
170 return g_comm_chest_with_bal_value ;
171 elsif p_balance_name = 'SDL Liability' then
172 return g_sdl_liab_bal_value ;
173 elsif p_balance_name = 'MBMF Withheld' then
174 return g_mbmf_with_bal_value ;
175 elsif p_balance_name = 'FWL Liability' then
176 return g_fwl_liab_bal_value ;
177 elsif p_balance_name = 'SINDA Withheld' then
178 return g_sinda_with_bal_value ;
179 elsif p_balance_name = 'CDAC Withheld' then
180 return g_cdac_with_bal_value ;
181 elsif p_balance_name = 'ECF Withheld' then
182 return g_ecf_with_bal_value ;
183 elsif p_balance_name = 'Balance Total' then
184 if fwl_reporting is null then
185 fwl_reporting := 'Y';
186 OPEN fwl_amount_reporting;
187 FETCH fwl_amount_reporting into fwl_reporting;
188 CLOSE fwl_amount_reporting;
189 end if;
190 ------------------------------------------------------------------------
191 --Bug# 4287277 - g_fwl_liab_bal_value value is not included in the Balance Total value if CPF Reporting option is set to No.
192 ------------------------------------------------------------------------
193 if fwl_reporting = 'N' then
194 return g_cpf_with_bal_value + g_cpf_liab_bal_value + g_vol_cpf_with_bal_value + g_vol_cpf_liab_bal_value +
195 g_comm_chest_with_bal_value + trunc(g_sdl_liab_bal_value) + g_mbmf_with_bal_value +
196 g_sinda_with_bal_value + g_cdac_with_bal_value + g_ecf_with_bal_value ;
197 else
198 return g_cpf_with_bal_value + g_cpf_liab_bal_value + g_vol_cpf_with_bal_value + g_vol_cpf_liab_bal_value +
199 g_comm_chest_with_bal_value + trunc(g_sdl_liab_bal_value) + g_mbmf_with_bal_value +
200 g_fwl_liab_bal_value + g_sinda_with_bal_value + g_cdac_with_bal_value + g_ecf_with_bal_value ;
201 end if;
202
203 end if;
204 --
205 end balance_amount;
206 ---------------------------------------------
207 -- Function stat_type_amount
208 -- Returns Balance Amount.
209 -- Called from Comapny_Identification cursor
210 ---------------------------------------------
211 function stat_type_amount
212 ( p_payroll_action_id in number,
213 p_stat_type in varchar2 ) return number
214 is
215
216 l_stat_type_total number;
217 begin
218 l_package := ' pay_sg_cpfline.';
219 l_proc_name := l_package || 'stat type count';
220 g_debug := hr_utility.debug_enabled;
221 --
222 if g_debug then
223 hr_utility.set_location(l_proc_name || ' Start of stat_type_amount',50);
224 end if;
225 --
226 if p_stat_type = 'AV1' then
227 l_stat_type_total := balance_amount ( p_payroll_action_id , 'CPF Withheld')+
228 balance_amount ( p_payroll_action_id , 'CPF Liability') +
232 l_stat_type_total := balance_amount ( p_payroll_action_id, 'Community Chest Withheld');
229 balance_amount ( p_payroll_action_id , 'Voluntary CPF Withheld') +
230 balance_amount ( p_payroll_action_id , 'Voluntary CPF Liability') ;
231 elsif p_stat_type = 'AV3' then
233 elsif p_stat_type = 'AV4' then
234 l_stat_type_total := trunc( balance_amount ( p_payroll_action_id, 'SDL Liability'));
235 elsif p_stat_type = 'AV5' then
236 l_stat_type_total := balance_amount ( p_payroll_action_id, 'MBMF Withheld');
237 elsif p_stat_type = 'AV7' then
238 l_stat_type_total := balance_amount ( p_payroll_action_id, 'FWL Liability');
239 elsif p_stat_type = 'AVA' then
240 l_stat_type_total := balance_amount ( p_payroll_action_id, 'SINDA Withheld');
241 elsif p_stat_type = 'AVE' then
242 l_stat_type_total := balance_amount ( p_payroll_action_id, 'CDAC Withheld');
243 elsif p_stat_type = 'AVG' then
244 l_stat_type_total := balance_amount ( p_payroll_action_id, 'ECF Withheld');
245 elsif p_stat_type = 'TOT' then
246 l_stat_type_total := balance_amount ( p_payroll_action_id, 'Balance Total');
247 end if;
248 --
249 if g_debug then
250 hr_utility.set_location(l_proc_name || ' End of stat_type_amount',50);
251 end if;
252 --
253 return l_stat_type_total;
254 end stat_type_amount;
255 -----------------------------------------------
256 -- Function stat_type_count
257 -- Returns person count contributing to different Balances.
258 -- called from company_identification cursor
259 -----------------------------------------------
260 function stat_type_count
261 ( p_payroll_action_id in number,
262 p_stat_type in varchar2 ) return number
263 is
264 --
265 l_count number;
266 --
267 begin
268 l_package := ' pay_sg_cpfline.';
269 l_proc_name := l_package || 'stat type count';
270 if g_debug then
271 hr_utility.set_location(l_proc_name || ' Start of stat_type_count',70);
272 end if;
273 ----------------------------------------------------------------------------------------------
274 -- Bug: 3298317 - Employee Count calculated based on distinct CPF number - action_information1
275 ----------------------------------------------------------------------------------------------
276 if p_stat_type = 'MUS' then
277 select count( distinct nvl(pai.action_information1,pai.source_id) )
278 into l_count
279 from pay_payroll_actions ppa,
280 pay_assignment_actions paa,
281 pay_action_information pai
282 where ppa.payroll_action_id = p_payroll_action_id
283 and ppa.payroll_action_id = paa.payroll_action_id
284 and paa.assignment_action_id = pai.action_context_id
285 and pai.action_information_category = 'SG CPF DETAILS'
286 and pai.action_context_type = 'AAC'
287 and to_number(pai.action_information8) > 0;
288 elsif p_stat_type = 'SHA' then
289 select count( distinct nvl(pai.action_information1,pai.source_id) )
290 into l_count
291 from pay_payroll_actions ppa,
292 pay_assignment_actions paa,
293 pay_action_information pai
294 where ppa.payroll_action_id = p_payroll_action_id
295 and ppa.payroll_action_id = paa.payroll_action_id
296 and paa.assignment_action_id = pai.action_context_id
297 and pai.action_information_category = 'SG CPF DETAILS'
298 and pai.action_context_type = 'AAC'
299 and to_number(pai.action_information14) > 0;
300 elsif p_stat_type = 'SIN' then
301 select count( distinct nvl(pai.action_information1,pai.source_id) )
302 into l_count
303 from pay_payroll_actions ppa,
304 pay_assignment_actions paa,
305 pay_action_information pai
306 where ppa.payroll_action_id = p_payroll_action_id
307 and ppa.payroll_action_id = paa.payroll_action_id
308 and paa.assignment_action_id = pai.action_context_id
309 and pai.action_information_category = 'SG CPF DETAILS'
310 and pai.action_context_type = 'AAC'
311 and to_number(pai.action_information9) > 0;
312 elsif p_stat_type = 'CDA' then
313 select count( distinct nvl(pai.action_information1,pai.source_id) )
314 into l_count
315 from pay_payroll_actions ppa,
316 pay_assignment_actions paa,
317 pay_action_information pai
318 where ppa.payroll_action_id = p_payroll_action_id
319 and ppa.payroll_action_id = paa.payroll_action_id
320 and paa.assignment_action_id = pai.action_context_id
321 and pai.action_information_category = 'SG CPF DETAILS'
322 and pai.action_context_type = 'AAC'
323 and to_number(pai.action_information10) > 0;
324 elsif p_stat_type = 'ECF' then
325 select count( distinct nvl(pai.action_information1,pai.source_id) )
326 into l_count
327 from pay_payroll_actions ppa,
328 pay_assignment_actions paa,
329 pay_action_information pai
330 where ppa.payroll_action_id = p_payroll_action_id
331 and ppa.payroll_action_id = paa.payroll_action_id
332 and paa.assignment_action_id = pai.action_context_id
333 and pai.action_information_category = 'SG CPF DETAILS'
334 and pai.action_context_type = 'AAC'
335 and to_number(pai.action_information11) > 0;
336 end if;
337 --
338 if g_debug then
339 hr_utility.set_location(l_proc_name || ' End of stat_type_count',70);
340 end if;
341 --
342 return l_count;
343 end stat_type_count;
344 --
345 function get_balance_value
346 ( p_employee_type in varchar2,
347 p_assignment_id in per_all_assignments_f.assignment_id%type,
348 p_cpf_acc_number in varchar2,
349 p_department in varchar2,
350 p_assignment_action_id in varchar2,
351 p_tax_unit_id in varchar2,
352 p_balance_name in varchar2,
353 p_balance_value in varchar2,
354 p_payroll_action_id in number,
355 p_permit_type per_people_f.per_information6%type) return varchar2
356 is
357 ----------------------------------------------------------------
358 -- For existing employees
359 -- NOTE: order by statement for above query should not be changed
360 ----------------------------------------------------------------
361
362 l_sort pay_action_information.action_information21%type;
363
364 ----------------------------------------------------------------
365 -- Bug No:3298317 Added new column permit_type(action_information21) in select clause.
366 -- Bug No:4226037 Added new column action_information19(termination date) in select clause
367 ----------------------------------------------------------------
368 cursor c_existing_employees ( p_payroll_action_id in number )
369 is
370 select nvl(pai.action_information1,pai.source_id) cpf_acc_number,
371 pai.action_information17,
372 pai.action_information18,
373 pai.action_information21,
374 pai.action_information22,
375 paa.assignment_id,
376 paa.assignment_action_id,
377 pai.tax_unit_id,
378 fnd_date.canonical_to_date(pai.action_information20),
379 pai.action_information19
380 from pay_payroll_actions ppa
381 , pay_assignment_actions paa
382 , pay_action_information pai
383 where ppa.payroll_action_id = p_payroll_action_id
384 and ppa.payroll_action_id = paa.payroll_action_id
385 and paa.assignment_action_id = pai.action_context_id
386 and pai.action_information_category = 'SG CPF DETAILS'
387 and pai.action_context_type = 'AAC'
388 and pai.action_information2 = 'EE'
389 and exists ( select 1
390 from pay_action_information pai_dup,
391 pay_assignment_actions paa_dup
392 where pai.action_information_category = pai_dup.action_information_category
393 and pai.rowid <> pai_dup.rowid
394 and paa_dup.payroll_action_id = ppa.payroll_action_id
395 and paa_dup.assignment_action_id = pai_dup.action_context_id
396 and pai.action_information1 = pai_dup.action_information1 )
400 -- Bug:3010644. Modified paa.effective_start_date join and added date track
397 order by cpf_acc_number,pai.action_information3 desc,pai.action_information23 desc;
398 ------------------------------------------------------------------------------
399 -- for new employees
401 -- check on ppa.effective_date and per_all_assignments_f
402 -- NOTE: order by statement for above query should not be changed
403 -- Bug No:3298317 Added new column permit_type(action_information21) in select clause.
404 -- Bug No:4226037 Added new column action_information19(termination date) in select clause
405 ------------------------------------------------------------------------------
406 cursor c_new_employees ( p_payroll_action_id in varchar2 )
407 is
408 select nvl(pai.action_information1,pai.source_id) cpf_acc_number,
409 pai.action_information17,
410 pai.action_information18,
411 pai.action_information21,
412 pai.action_information22,
413 paa.assignment_id,
414 paa.assignment_action_id,
415 pai.tax_unit_id,
416 fnd_date.canonical_to_date(pai.action_information20),
417 pai.action_information19
418 from pay_payroll_actions ppa
419 , pay_assignment_actions paa
420 , pay_action_information pai
421 where ppa.payroll_action_id = p_payroll_action_id
422 and ppa.payroll_action_id = paa.payroll_action_id
423 and paa.assignment_action_id = pai.action_context_id
424 and pai.action_information_category = 'SG CPF DETAILS'
425 and pai.action_context_type = 'AAC'
426 and pai.action_information2 = 'NEW'
427 and exists ( select 1
428 from pay_action_information pai_dup,
429 pay_assignment_actions paa_dup
430 where pai.action_information_category = pai_dup.action_information_category
431 and pai.rowid <> pai_dup.rowid
432 and paa_dup.payroll_action_id = ppa.payroll_action_id
433 and paa_dup.assignment_action_id = pai_dup.action_context_id
434 and pai.action_information1 = pai_dup.action_information1 )
435 order by cpf_acc_number,pai.action_information3 desc,pai.action_information23 desc;
436 --
437 l_date date;
438 l_counter number;
439 l_mon_counter number;
440 l_found boolean;
441 update_status boolean;
442 asg_is_duplicate number;
443 bal_value varchar2(20);
444 mf_tot_bal varchar2(20);
445 ctl_tot_bal varchar2(20);
446 ctl_bal_value varchar2(20);
447 mf_employee_info varchar2(200);
448 ctl_employee_info varchar2(200);
449 tot_bal varchar2(100);
450 l_wp char(1);
451 l_sg char(1);
452 --
453 function dup_bal_value ( c_assignment_action_id in number) return number
454 is
455
456 l_permit_type pay_action_information.action_information21%type;
457
458 /* Bug: 3595103 Modified cursor for permit type 'SP' */
459 cursor get_balances is
460 select nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information4) ))))))),0),
461 nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information6) ))))))),0),
462 nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information5) ))))))),0),
463 nvl(decode( action_information1,null,0,(decode(action_information21,'WP',0,(decode(action_information21,'EP',0,(decode(action_information21,'SP',0,to_number(action_information7) ))))))),0),
464 nvl(to_number(action_information8),0),
465 nvl(to_number(action_information9),0),
466 nvl(to_number(action_information10),0),
467 nvl(to_number(action_information11),0),
468 nvl(to_number(action_information12),0),
469 nvl(to_number(action_information13),0)
470 from pay_action_information
471 where action_context_id = c_assignment_action_id
472 and action_information_category = 'SG CPF DETAILS'
473 and action_context_type = 'AAC';
474
475
476 begin
477 l_package := ' pay_sg_cpfline.';
478 l_proc_name := l_package || 'get_balance_value';
479 if ( c_assignment_action_id <> g_cpf_balances.assact_id) or ( g_cpf_balances.assact_id is NULL ) then
480 open get_balances;
481 fetch get_balances
482 into g_cpf_balances.vol_cpf_liab -- action_information4
483 ,g_cpf_balances.cpf_liab -- action_information6
484 ,g_cpf_balances.vol_cpf_wheld -- action_information5
485 ,g_cpf_balances.cpf_wheld -- action_information7
486 ,g_cpf_balances.mbmf_wheld -- action_information8
487 ,g_cpf_balances.sinda_wheld -- action_information9
488 ,g_cpf_balances.cdac_wheld -- action_information10
489 ,g_cpf_balances.ecf_wheld -- action_information11
490 ,g_cpf_balances.cpf_ord_earn -- action_information12
491 ,g_cpf_balances.cpf_addl_earn -- action_information13
492 ;
493 --
494 g_cpf_balances.assact_id := c_assignment_action_id ;
495 --
496 close get_balances ;
497 end if;
498 --
499 if p_balance_name = 'Voluntary CPF Liability' then
500 return g_cpf_balances.vol_cpf_liab ;
501 elsif p_balance_name = 'CPF Liability' then
502 return g_cpf_balances.cpf_liab ;
503 elsif p_balance_name = 'Voluntary CPF Withheld' then
504 return g_cpf_balances.vol_cpf_wheld ;
505 elsif p_balance_name = 'CPF Withheld' then
506 return g_cpf_balances.cpf_wheld;
507 elsif p_balance_name = 'MBMF Withheld' then
508 return g_cpf_balances.mbmf_wheld ;
509 elsif p_balance_name = 'SINDA Withheld' then
510 return g_cpf_balances.sinda_wheld ;
511 elsif p_balance_name = 'CDAC Withheld' then
512 return g_cpf_balances.cdac_wheld ;
513 elsif p_balance_name = 'ECF Withheld' then
514 return g_cpf_balances.ecf_wheld ;
515 elsif p_balance_name = 'CPF Ordinary Earnings Eligible Comp' then
516 return g_cpf_balances.cpf_ord_earn ;
517 elsif p_balance_name = 'CPF Additional Earnings Eligible Comp' then
518 return g_cpf_balances.cpf_addl_earn ;
519 else
520 raise_application_error(-20001, 'Program Error : Invalid Balance') ;
521 end if ;
522 end;
523 begin
524 --
525 l_counter := 1;
526 l_mon_counter := 1;
527 l_found := false;
528 update_status := false;
529 asg_is_duplicate := 0;
530 bal_value := 0;
531 mf_tot_bal := 0;
532 ctl_tot_bal := 0;
533 ctl_bal_value := 0;
534 mf_employee_info := 'X';
535 ctl_employee_info := 'X';
536 tot_bal := '0#0';
537 l_wp :='N';
538 l_sg :='N';
539
540 if g_debug then
541 hr_utility.set_location(l_proc_name || ' Start of get_balance_value',80);
542 end if;
543 --
544 if p_employee_type = 'NEW' and global_exist_emp = true then
545 t_dup_emp_rec.delete;
546 global_exist_emp := false;
547 end if;
548 ---------------------------------------------------------------------------------
549 -- This function is called for every emplyee and all the balances through
550 -- the cursor existing_employees (identified by 'EE') and new employees
551 -- (identified by 'New'. When this function is called for the first time a
552 -- global pl/sql table is populated by opening cursor c_existing_employee
553 -- for existing employees and with c_new_employees for new employees.
554 -- The pl/sql table table will store employee level details for all the employees
555 ---------------------------------------------------------------------------------
556 if t_dup_emp_rec.count = 0 then
557 if p_employee_type = 'EE' then
558 open c_existing_employees( p_payroll_action_id );
559 --
560 loop
561 fetch c_existing_employees
562 into t_dup_emp_rec(l_counter).cpf_acc_number,
563 t_dup_emp_rec(l_counter).legal_name,
564 t_dup_emp_rec(l_counter).employee_number,
565 t_dup_emp_rec(l_counter).permit_type,
566 t_dup_emp_rec(l_counter).department,
567 t_dup_emp_rec(l_counter).assignment_id,
568 t_dup_emp_rec(l_counter).assignment_action_id,
569 t_dup_emp_rec(l_counter).tax_unit_id,
570 t_dup_emp_rec(l_counter).effective_date,
571 t_dup_emp_rec(l_counter).termination_date;
572 exit when c_existing_employees%NOTFOUND;
573 ------------------------------------------------------------
574 -- the record is not considered for magtape
575 ------------------------------------------------------------
576 t_dup_emp_rec(l_counter).cl_record_status:='U';
577 t_dup_emp_rec(l_counter).mf_record_status:='U';
578 l_counter := l_counter + 1;
579 end loop;
580 --
581 close c_existing_employees;
582 else
583 -------------------------------------------------------
584 -- if p_employee_type = NEW
585 -------------------------------------------------------
586 open c_new_employees( p_payroll_action_id );
587 --
588 loop
589 fetch c_new_employees
590 into t_dup_emp_rec(l_counter).cpf_acc_number,
591 t_dup_emp_rec(l_counter).legal_name,
592 t_dup_emp_rec(l_counter).employee_number,
593 t_dup_emp_rec(l_counter).permit_type,
594 t_dup_emp_rec(l_counter).department,
595 t_dup_emp_rec(l_counter).assignment_id,
596 t_dup_emp_rec(l_counter).assignment_action_id,
597 t_dup_emp_rec(l_counter).tax_unit_id,
598 t_dup_emp_rec(l_counter).effective_date,
599 t_dup_emp_rec(l_counter).termination_date;
600 exit when c_new_employees%NOTFOUND;
601 -----------------------------------------------------------
602 -- the record is not considered for magtape
603 -----------------------------------------------------------
604 t_dup_emp_rec(l_counter).cl_record_status:='U';
605 t_dup_emp_rec(l_counter).mf_record_status:='U';
606 l_counter := l_counter + 1;
607 end loop;
608 close c_new_employees;
609 end if;
610 end if;
611 -----------------------------------------------------------------------------------------------------------------
612 -- 1)
613 -- Legal name ,employee number and emp termination date are also derived in this function though they are
614 -- not balances. if balance_name passed is other then above names then find out the defined balance id wrt
615 -- balance names passed to the function from the pl/sql table populated above
616 --
617 -- 2)
618 -- The function is called for all the balances (10 balances and 3 non balances (legal name, emp number, term date)
619 -- for a selected assignment .
620 -- The global global_bal_count is incremented for every function call. Once the last function call is made,
621 -- the employee should be marked as processed for the cpf line.
622 --
623 -- if update_status = TRUE then update assignment status to processed (M)
624 -----------------------------------------------------------------------------------------------------------------
625 if global_bal_count = 12 then
626 update_status := TRUE; -- update assignment status to processed (M)
627 global_bal_count := 0;
628 else
629 global_bal_count := global_bal_count + 1;
630 end if;
631 -----------------------------------------------------------------------------------------------------------------
632 -- Records in the pl/sql table t_dup_emp_rec are sorted by the cpf account number. Once all the records are
633 -- fetched for a particular cpf account number passed to the function, there is no need to search further in the
634 -- table. the l_found variable is used to handle this
635 -----------------------------------------------------------------------------------------------------------------
636 l_found := FALSE; -- initialzed to false.
637
638 ------------------------------------------------------------------------------------------------------
639 -- 1) find out the matching records in the pl/sql table,for the cpf account number passed
640 -- 2)
641 -- 2.1) For Magtape:
642 -- if the record status of a record in the pl/sql table is unprocessed ('U') Then
643 -- for balance names 'Legal name','Employee Number' and 'Emp Termination date' find out the values.
644 -- A check is made so that the latest employee's data is only retrieved and if this function is called
645 -- next time the values should not be overriden. Records are stored in the pl/sql table such that the
646 -- first record for a CPF Account Number is the latest record
647 -- for actual balances , find out the balance value for all the assignments related to the Cpf account number passed.
648 -- sum up these values.If there is only one assignment for a cpf account number then, return the balance values
649 -- Once last balance is retrieved, mark all the assignments for the cpf account number as prcoessed in the
650 -- 'Magtape' status='M'
651 -- 2.2) For Control listing:
652 -- All the steps for 2.1 hold good for control listing also, but therte is an additional check for the department.
653 -- Here we find all the assignments which are under the CPF Account number and the Department passed as parameter.
654 -- Once all the relevent records are fetched the , mark all the assignments as processed in the control listing
655 -- status = 'C'
656 ------------------------------------------------------------------------------------------------------
657 l_wp:='N';
658 l_sg:='N';
659 if t_dup_emp_rec.count > 0 then
660 --
661 -----------------------------------------------------------------------------
662 -- Bug No:3298317 Added to skip the Employees of permit type 'WP' or 'EP'
663 -- and who are rehired with permit type 'SG' or 'PR'.
664 -- Bug: 3595103 Modified cursor for permit type 'SP'
665 -------------------------------------------------------------------------
666
667 if p_permit_type='WP' OR p_permit_type='EP' OR p_permit_type='SP' then
668 for l_dup_counter in t_dup_emp_rec.first..t_dup_emp_rec.last
669 loop
670 if p_assignment_action_id=t_dup_emp_rec(l_dup_counter).assignment_action_id and
671 (t_dup_emp_rec(l_dup_counter).permit_type='WP' or
672 t_dup_emp_rec(l_dup_counter).permit_type='SP' or
673 t_dup_emp_rec(l_dup_counter).permit_type='EP') then
674 l_wp:='Y';
675 end if;
676 --
677 if t_dup_emp_rec(l_dup_counter).cpf_acc_number = p_cpf_acc_number and (t_dup_emp_rec(l_dup_counter).permit_type='SG' or t_dup_emp_rec(l_dup_counter).permit_type='PR') then
678 l_sg:='Y' ;
679 end if;
680 end loop;
681 end if;
682 -------------------------------------------------------------------------------------------
683
684 for l_dup_counter in t_dup_emp_rec.first..t_dup_emp_rec.last
685 loop
686
687 --
688 -----------------------------------------------------------------------------
689 -- Bug No:3298317 Added to skip the Employees of permit type 'WP' or 'EP' or 'SP'
690 -- and who are rehired with permit type 'SG' or 'PR'.
691 -------------------------------------------------------------------------
692 if l_wp= 'Y' and l_sg= 'Y' then
693 exit;
694 end if;
695 ------------------------------------------------------------------------------
696
697 if (t_dup_emp_rec(l_dup_counter).cpf_acc_number <> p_cpf_acc_number) and l_found = true then
698 exit;
699 elsif (t_dup_emp_rec(l_dup_counter).cpf_acc_number = p_cpf_acc_number) then
700 ------------------------------------------------------
701 -- Magtape File
702 -------------------------------------------------------
703 if t_dup_emp_rec(l_dup_counter).mf_record_status = 'U' then
704 if ( p_balance_name in ('Legal_Name','Employee_Number','Emp_Term_Date')) then
705 if (mf_employee_info = 'X' ) then -- only latest information should be written
706 if p_balance_name ='Legal_Name' Then
707 mf_employee_info := t_dup_emp_rec(l_dup_counter).Legal_name;
708 /* Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).Legal_name */
709 elsif p_balance_name = 'Employee_Number' Then
710 mf_employee_info := t_dup_emp_rec(l_dup_counter).employee_number ;
711 /* Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).employee_number */
712 elsif p_balance_name = 'Emp_Term_Date' Then
716 -- Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).termination_date.
713 if t_dup_emp_rec(l_dup_counter).termination_date is not null then
714 mf_employee_info := t_dup_emp_rec(l_dup_counter).termination_date;
715 -----------------------------------------------------------------------------------------------
717 -- Included else clause to return default date if the latest assignment is not terminated.
718 -----------------------------------------------------------------------------------------------
719 else
720 l_date := to_date('01/01/1900','dd/mm/yyyy');
721 mf_employee_info := to_char(l_date,'dd/mm/yyyy');
722 end if;
723 end if;
724 end if;
725 --
726 if update_status=true then
727 t_dup_emp_rec(l_dup_counter).mf_record_status := 'M';
728 end if;
729 else
730 bal_value := dup_bal_value ( t_dup_emp_rec(l_dup_counter).assignment_action_id );
731 --
732 mf_tot_bal := mf_tot_bal + bal_value;
733 l_found := true;
734 ----------------------------------------------------
735 -- the record is considered for magtape, so next time if the current
736 -- assignment id is passed
737 -- through the main cursor, it should be ignored
738 ----------------------------------------------------
739 if update_status=true then
740 t_dup_emp_rec(l_dup_counter).mf_record_status := 'M';
741 end if;
742 --
743 if g_debug then
744 hr_utility.set_location(l_proc_name || ' MF Section',80);
745 hr_utility.set_location(l_proc_name || ' p_balance_name '||p_balance_name,80);
746 hr_utility.set_location(l_proc_name || ' Employee '||t_dup_emp_rec(l_counter).cpf_acc_number,80);
747 hr_utility.set_location(l_proc_name || ' balance_value '||mf_tot_bal,80);
748 hr_utility.set_location(l_proc_name || ' Asact id '||to_char(t_dup_emp_rec(l_counter).assignment_action_id),80);
749 end if;
750 end if;
751 end if;
752 ------------------------------------------------------
753 -- Control listing Section
754 ------------------------------------------------------
755 if t_dup_emp_rec(l_dup_counter).department = p_department then
756 if t_dup_emp_rec(l_dup_counter).cl_record_status = 'U' then
757 if( p_balance_name in ('Legal_Name','Employee_Number','Emp_Term_Date')) then
758 if (ctl_employee_info = 'X') then
759 if p_balance_name ='Legal_Name' Then
760 ctl_employee_info := t_dup_emp_rec(l_dup_counter).Legal_name;
761 /* Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).Legal_name */
762 elsif p_balance_name = 'Employee_Number' Then
763 ctl_employee_info := t_dup_emp_rec(l_dup_counter).employee_number;
764 /* Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).employee_number */
765 elsif p_balance_name = 'Emp_Term_Date' Then
766 ctl_employee_info := t_dup_emp_rec(l_dup_counter).termination_date;
767 /* Bug#4226037 p_balance_value is replaced with t_dup_emp_rec(l_dup_counter).termination_date */
768 end if;
769 end if;
770 --
771 if update_status=true then
772 t_dup_emp_rec(l_dup_counter).cl_record_status := 'C';
773 end if;
774 else
775 ctl_bal_value := dup_bal_value ( t_dup_emp_rec(l_dup_counter).assignment_action_id );
776 --
777 ctl_tot_bal := ctl_tot_bal + ctl_bal_value;
778 l_found := true;
779 --
780 if update_status=true then
781 t_dup_emp_rec(l_dup_counter).cl_record_status := 'C';
782 end if;
783 --
784 if g_debug then
785 hr_utility.set_location(l_proc_name || ' CTL Section',80);
786 hr_utility.set_location(l_proc_name || ' p_balance_name '||p_balance_name,80);
787 hr_utility.set_location(l_proc_name || ' Employee '||t_dup_emp_rec(l_counter).cpf_acc_number,80);
788 hr_utility.set_location(l_proc_name || ' balance_value '||ctl_tot_bal,80);
789 hr_utility.set_location(l_proc_name || ' Asact id '||to_char(t_dup_emp_rec(l_counter).assignment_action_id),80);
790 end if;
791 --
792 end if;
793 end if;
794 end if;
795 end if;
796 end loop;
797 end if;
798 ---------------------------------------------------------------------------------
799 -- return concatenated values, which will be considered in the magatape formula
800 -- If employee is not terminated, return default date
801 ---------------------------------------------------------------------------------
802 if p_balance_name in ('Legal_Name','Employee_Number') then
803 return mf_employee_info||'#'||ctl_employee_info;
804 elsif p_balance_name = 'Emp_Term_Date' then
805 ---------------------------------
806 -- If employee is not terminated, return default date
807 ---------------------------------
808 if mf_employee_info = 'X' then
809 l_date:= to_date('01/01/1900','dd/mm/yyyy');
810 mf_employee_info:= to_char(l_date,'dd/mm/yyyy');
811 else
812 l_date:= fnd_date.canonical_to_date(mf_employee_info);
813 mf_employee_info:= to_char(l_date,'dd/mm/yyyy');
814 end if;
815 --
816 return mf_employee_info;
817 else
818 tot_bal := mf_tot_bal||'#'||ctl_tot_bal;
819 --
820 return tot_bal ;
821 end if;
822 --
823 if g_debug then
824 hr_utility.set_location(l_proc_name || ' End of get_balance_value',80);
825 end if;
826 -- hr_utility.trace_off;
827 end get_balance_value;
828
829 end pay_sg_cpfline_balances;
[Home] [Help]