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