[Home] [Help]
PACKAGE BODY: APPS.PAY_SG_SOE
Source
1 package body pay_sg_soe as
2 /* $Header: pysgsoe.pkb 120.3 2008/05/28 01:41:37 jalin noship $ */
3 ------------------------------------------------------------------------
4 -- Selects the Salary for the Person.
5 -- clone of hr_general.get_salary but fetch at a given date
6 -- This cursor gets the screen_entry_value from pay_element_entry_values_f.
7 -- This is the salary amount obtained when the pay basis isn't null.
8 -- The pay basis and assignment_id are passed in by the view.
9 -- A check is made on the effective date of pay_element_entry_values_f
10 -- and pay_element_entries_f as they're datetracked.
11 ------------------------------------------------------------------------
12 function current_salary
13 (p_pay_basis_id in per_pay_bases.pay_basis_id%type,
14 p_assignment_id in pay_element_entries_f.assignment_id%type,
15 p_effective_date in date)
16 return varchar2 is
17
18 cursor salary
19 (c_pay_basis_id per_pay_bases.pay_basis_id%type,
20 c_assignment_id pay_element_entries_f.assignment_id%type,
21 c_effective_date date) is
22 select pev.screen_entry_value
23 from per_pay_bases ppb,
24 pay_element_entries_f pee,
25 pay_element_entry_values_f pev
26 where pee.assignment_id = c_assignment_id
27 and ppb.pay_basis_id = c_pay_basis_id
28 and pee.element_entry_id = pev.element_entry_id
29 and ppb.input_value_id = pev.input_value_id
30 and c_effective_date between pev.effective_start_date
31 and pev.effective_end_date
32 and c_effective_date between pee.effective_start_date
33 and pee.effective_end_date;
34
35 v_salary pay_element_entry_values_f.screen_entry_value%type := null;
36 begin
37 -- Only open the cursor if the parameter may retrieve anything
38 -- In practice, p_assignment_id is always going to be non null;
39 -- p_pay_basis_id may be null, though. If it is, don't bother trying
40 -- to fetch a salary.
41 -- If we do have a pay basis, try and get a salary. There may not be one,
42 -- in which case no problem: just return null.
43
44 if p_pay_basis_id is not null and p_assignment_id is not null then
45 open salary (p_pay_basis_id,
46 p_assignment_id,
47 p_effective_date);
48 fetch salary into v_salary;
49 if salary%NOTFOUND then
50 close salary;
51 raise NO_DATA_FOUND;
52 end if;
53 close salary;
54 end if;
55
56 return v_salary;
57 end current_salary;
58
59 ------------------------------------------------------------------------
60 -- Calls the core accrual function. As per_accrual_calc_functions may
61 -- change to have extra (default) parameters, it is called here rather
62 -- than in the form or report as the version of SQL in these will not
63 -- process default parameters.
64 -- Also handy as we only need to pass back the accrual value, not the
65 -- other OUT parameters.
66 ------------------------------------------------------------------------
67 function net_accrual
68 (p_assignment_id in pay_assignment_actions.assignment_id%type,
69 p_plan_id in pay_accrual_plans.accrual_plan_id%type,
70 p_payroll_id in pay_payroll_actions.payroll_id%type,
71 p_business_group_id in pay_accrual_plans.business_group_id%type,
72 p_effective_date in per_time_periods.end_date%type)
73 return number is
74 v_start_date date;
75 v_end_date date;
76 v_accrual_end_date date;
77 v_accrual number;
78 v_net_entitlement number;
79 begin
80 per_accrual_calc_functions.get_net_accrual
81 (p_assignment_id => p_assignment_id, -- number in
82 p_plan_id => p_plan_id, -- number in
83 p_payroll_id => p_payroll_id, -- number in
84 p_business_group_id => p_business_group_id, -- number in
85 p_calculation_date => p_effective_date, -- date in
86 p_start_date => v_start_date, -- date out
87 p_end_date => v_end_date, -- date out
88 p_accrual_end_date => v_accrual_end_date, -- date out
89 p_accrual => v_accrual, -- number out
90 p_net_entitlement => v_net_entitlement); -- number out
91
92 return v_net_entitlement;
93 end net_accrual;
94 ------------------------------------------------------------------------
95 -- Sums the Balances for Current and YTD, according to the parameters.
96 -- for the SOE window
97 ------------------------------------------------------------------------
98 /* Bug:2883606. This procedure is modified to retrieve balance values
99 directly from pay_balance_pkg.get_value instead of retrieving it from
100 the view pay_sg_soe_balances_v */
101
102 procedure current_and_ytd_balances
103 (p_prepaid_tag in varchar,
104 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
105 p_balance_name in pay_balance_types.balance_name%type,
106 p_person_id in per_all_people_f.person_id%type,
107 p_current_balance out nocopy number,
108 p_ytd_balance out nocopy number)
109 is
110 v_balance_value number;
111 v_asg_le_ytd number;
112 v_per_le_ytd number;
113 v_payments number;
114 v_asg_le_run number;
115 v_pp_asg_le_ytd number;
116 v_pp_asg_le_run number;
117 v_pp_per_le_ytd number;
118 v_pp_payments number;
119 v_pp_assignment_action_id pay_assignment_actions.assignment_action_id%type;
120 v_cur_run_id pay_assignment_actions.assignment_action_id%type;
121 l_RUN_ASSIGNMENT_ACTION_ID pay_assignment_actions.assignment_action_id%type;
122
123 /* Bug 2824397 */
124
125 v_assign_count number := 1;
126 v_date_earned date;
127 v_ass_act_id pay_assignment_actions.assignment_action_id%type;
128 v_asg_le_value number;
129 v_counter number := 1;
130
131 TYPE assign_tab is table of per_all_assignments_f.assignment_id%type
132 index by BINARY_INTEGER;
133
134 assignment_table assign_tab;
135
136 /* Bug 2824397 */
137
138 -- pay_balance_pkg.set_context('TAX_UNIT_ID');
139 -- pay_balance_pkg.set_context('JURISDICTION_CODE');
140
141 /* Bug:2883606. Cursor to get the balance values from pay_balance_pkg.get_value */
142 cursor balance_value
143 (c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
144 c_balance_name pay_balance_types.balance_name%type,
145 c_dimension_name pay_balance_dimensions.dimension_name%type) is
146 select nvl(pay_balance_pkg.get_value(pdb.defined_balance_id,c_assignment_action_id),0)
147 from pay_balance_dimensions pbd,
148 pay_defined_balances pdb,
149 pay_balance_types pbt
150 where pbt.balance_name = c_balance_name
151 and pbd.dimension_name = c_dimension_name
152 and pbt.balance_type_id = pdb.balance_type_id
153 and pbd.balance_dimension_id = pdb.balance_dimension_id
154 and pbt.legislation_code = 'SG';
155
156 /* Bug:2883606. Cursor to get all the locked actions locked by prepayments */
157 cursor run_ids
158 (p_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
159 select LOCKED_ACTION_ID
160 from pay_action_interlocks
161 where LOCKING_ACTION_ID = p_assignment_action_id
162 order by locked_action_id asc;
163
164 /* Bug#2883606. Included check for costing(C) */
165 /* Bug 6978015, added ORDER BY to get locking assignment_action_id */
166 /* whatever Costing runs after prepayment or before */
167 cursor c_lock_id
168 (c_assign_act_id pay_assignment_actions.assignment_action_id%type) is
169 select pai.locking_action_id
170 from pay_action_interlocks pai,
171 pay_payroll_actions ppa,
172 pay_assignment_actions paa
173 where pai.locked_action_id = c_assign_act_id
174 and paa.assignment_action_id=pai.locking_action_id
175 and ppa.payroll_action_id=paa.payroll_action_id
176 and ppa.action_type in ('P','U','C')
177 order by decode(ppa.action_type,'C',0,1) desc;
178
179 /* Bug #2824397 */
180 /* Bug:2824397 Added extra parameter c_date_earned */
181 cursor c_check_assignment
182 (c_person_id per_all_people_f.person_id%type,
183 c_date_earned pay_payroll_actions.date_earned%type) is
184 select distinct assignment_id
185 from per_all_assignments_f
186 where person_id = c_person_id
187 and effective_start_date <= c_date_earned
188 and assignment_type = 'E';
189
190 cursor c_date_earned
191 (c_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
192 select ppa.effective_date /* Bug 4267365 */
193 from pay_payroll_actions ppa,
194 pay_assignment_actions pac
195 where pac.payroll_action_id = ppa.payroll_action_id
196 and pac.assignment_action_id = c_assignment_action_id;
197
198 cursor c_max_assign_action
199 (c_assignment_id per_all_assignments_f.assignment_id%type,
200 c_date_earned pay_payroll_actions.date_earned%type ) is
201 select max(pac.assignment_action_id)
202 from pay_assignment_actions pac,
203 per_all_assignments_f paa,
204 pay_payroll_actions ppa
205 where paa.assignment_id = c_assignment_id
206 and paa.assignment_type = 'E'
207 and paa.assignment_id = pac.assignment_id
208 and pac.payroll_action_id = ppa.payroll_action_id
209 and ppa.action_type in ('R','Q','P','U')
210 and ppa.effective_date between to_date('01-01'||to_char(c_date_earned,'YYYY'),'DD-MM-YYYY')
211 and c_date_earned; /* Bug 4267365 */
212
213 /* Bug #2824397 */
214
215 begin
216
217 /* Bug:2883606. If prepayments is done then sum up the balance values
218 for all the actions that are locked by the prepayments.
219 Else get the balance value for that single run */
220
221 if p_prepaid_tag = 'Y' then
222 open c_lock_id(p_assignment_action_id);
223 fetch c_lock_id into v_pp_assignment_action_id;
224 close c_lock_id;
225
226 open run_ids(v_pp_assignment_action_id);
227 loop
228 fetch run_ids into v_cur_run_id;
229 exit when run_ids%NOTFOUND;
230 l_RUN_ASSIGNMENT_ACTION_ID := v_cur_run_id;
231
232 open balance_value ( l_RUN_ASSIGNMENT_ACTION_ID ,p_balance_name,'_ASG_LE_RUN');
233 fetch balance_value into v_asg_le_run;
234 p_current_balance := NVL(p_current_balance,0) + v_asg_le_run;
235 close balance_value;
236 end loop;
237 close run_ids;
238
239 else
240
241 open balance_value ( p_assignment_action_id ,p_balance_name,'_ASG_LE_RUN');
242 fetch balance_value into p_current_balance;
243 close balance_value;
244
245 end if;
246
247 if instr(p_balance_name,'CPF') = 0 then
248 open balance_value (p_assignment_action_id,
249 p_balance_name,
250 '_ASG_LE_YTD');
251 fetch balance_value into p_ytd_balance;
252 close balance_value;
253 else
254
255 /* Bug 2824397 */
256 hr_utility.trace('Act_Act_Id' || to_char(p_assignment_action_id));
257 hr_utility.trace('Person_Id' || to_char(p_person_id));
258 hr_utility.trace('Date' ||to_char(v_date_earned));
259
260 /* Get the Date Earned of the Assignment Action */
261 open c_date_earned(p_assignment_action_id);
262 fetch c_date_earned into v_date_earned;
263 close c_date_earned;
264
265 hr_utility.trace('Date' ||to_char(v_date_earned));
266
267 /* Get all the assignments for the person_id and place the data into a PLSQL Table */
268
269 open c_check_assignment(p_person_id,v_date_earned);
270 loop
271 fetch c_check_assignment into assignment_table(v_assign_count);
272 exit when c_check_assignment%NOTFOUND;
273 v_assign_count := v_assign_count + 1;
274 end loop;
275 close c_check_assignment;
276
277 hr_utility.trace('Count' || to_char(assignment_table.count));
278
279 if assignment_table.count > 1 then
280 for v_counter in 1..assignment_table.count
281 loop
282
283 /* Get the maximum assignment action within the financial year for every
284 assignment fetched */
285
286 v_asg_le_value := 0;
287 open c_max_assign_action(assignment_table(v_counter),v_date_earned);
288 fetch c_max_assign_action into v_ass_act_id;
289 close c_max_assign_action;
290
291 hr_utility.trace('Loop_Id' || to_char(v_ass_act_id));
292 if (v_ass_act_id is not null) then
293 open balance_value (v_ass_act_id,
294 p_balance_name,
295 '_ASG_LE_YTD');
296 fetch balance_value into v_asg_le_value;
297 close balance_value;
298 end if;
299
300 hr_utility.trace('v_asg_le_value' || to_char(v_asg_le_value));
301
302 /* Sum up the asg_le value to arrive at the per_le value */
303
304 v_per_le_ytd := nvl(v_per_le_ytd,0) + nvl(v_asg_le_value,0);
305 hr_utility.trace('v_per_le_ytd' || to_char(v_per_le_ytd));
306
307 end loop;
308
309 p_ytd_balance := nvl(v_per_le_ytd,0);
310
311 hr_utility.trace('p_ytd_balance' || to_char(p_ytd_balance));
312
313 else
314
315 /* If the person has a single assignment_id then directly use asg_le_ytd value,
316 since asg_le_ytd and per_le_ytd value */
317
318 open balance_value (p_assignment_action_id,
319 p_balance_name,
320 '_ASG_LE_YTD');
321 fetch balance_value into p_ytd_balance;
322 close balance_value;
323
324 end if;
325 /* Bug 2824397 */
326
327 end if;
328
329 end current_and_ytd_balances;
330 ------------------------------------------------------------------------
331 -- Sums the Balances for Current and YTD, according to the parameters.
332 -- for the Pay Advice report
333 ------------------------------------------------------------------------
334 /* Bug:2883606. This procedure is modified to retrieve balance values
335 directly from pay_balance_pkg.get_value instead of retrieving it from
336 the view pay_sg_soe_balances_v */
337
338 procedure current_and_ytd_balances
339 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
340 p_balance_name in pay_balance_types.balance_name%type,
341 p_person_id in per_all_people_f.person_id%type,
342 p_current_balance out nocopy number,
343 p_ytd_balance out nocopy number)
344 is
345 v_balance_value number;
346 v_asg_le_ytd number;
347 v_per_le_ytd number;
348 v_payments number;
349 v_asg_le_run number;
350 v_pp_asg_le_ytd number;
351 v_pp_asg_le_run number;
352 v_pp_per_le_ytd number;
353 v_pp_payments number;
354 v_pp_assignment_action_id pay_assignment_actions.assignment_action_id%type;
355 v_cur_run_id pay_assignment_actions.assignment_action_id%type;
356 l_RUN_ASSIGNMENT_ACTION_ID pay_assignment_actions.assignment_action_id%type;
357
358 /* Bug 2824397 */
359
360 v_assign_count number := 1;
361 v_date_earned date;
362 v_ass_act_id pay_assignment_actions.assignment_action_id%type;
363 v_asg_le_value number;
364 v_counter number := 1;
365
366 TYPE assign_tab is table of per_all_assignments_f.assignment_id%type
367 index by BINARY_INTEGER;
368
369 assignment_table assign_tab;
370
371 /* Bug 2824397 */
372 -- pay_balance_pkg.set_context('TAX_UNIT_ID');
373 -- pay_balance_pkg.set_context('JURISDICTION_CODE');
374
375 /* Bug:2883606. Cursor to get the balance values from pay_balance_pkg.get_value */
376 cursor balance_value
377 (c_assignment_action_id pay_assignment_actions.assignment_action_id%type,
378 c_balance_name pay_balance_types.balance_name%type,
379 c_dimension_name pay_balance_dimensions.dimension_name%type) is
380 select nvl(pay_balance_pkg.get_value(pdb.defined_balance_id,c_assignment_action_id),0)
381 from pay_balance_dimensions pbd,
382 pay_defined_balances pdb,
383 pay_balance_types pbt
384 where pbt.balance_name = c_balance_name
385 and pbd.dimension_name = c_dimension_name
386 and pbt.balance_type_id = pdb.balance_type_id
387 and pbd.balance_dimension_id = pdb.balance_dimension_id
388 and pbt.legislation_code = 'SG';
389
390 /* Bug:2883606. Cursor to get all the locked actions locked by prepayments */
391 cursor run_ids
392 (p_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
393 select LOCKED_ACTION_ID
394 from pay_action_interlocks
395 where LOCKING_ACTION_ID = p_assignment_action_id
396 order by locked_action_id asc;
397
398 /* Bug#2883606. Included check for costing(C) */
399 cursor c_lock_id
400 (c_assign_act_id pay_assignment_actions.assignment_action_id%type) is
401 select pai.locking_action_id
402 from pay_action_interlocks pai,
403 pay_payroll_actions ppa,
404 pay_assignment_actions paa
405 where pai.locked_action_id = c_assign_act_id
406 and paa.assignment_action_id=pai.locking_action_id
407 and ppa.payroll_action_id=paa.payroll_action_id
408 and ppa.action_type in ('P','U','C');
409
410 /* Bug #2824397 */
411 /* Bug:2824397 Added extra parameter c_date_earned */
412 cursor c_check_assignment
413 (c_person_id per_all_people_f.person_id%type,
414 c_date_earned pay_payroll_actions.date_earned%type ) is
415 select distinct assignment_id
416 from per_all_assignments_f
417 where person_id = c_person_id
418 and effective_start_date <= c_date_earned
419 and assignment_type = 'E';
420
421 cursor c_date_earned
422 (c_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
423 select ppa.effective_date /* Bug 4267365 */
424 from pay_payroll_actions ppa,
425 pay_assignment_actions pac
426 where pac.payroll_action_id = ppa.payroll_action_id
427 and pac.assignment_action_id = c_assignment_action_id;
428
429 cursor c_max_assign_action
430 (c_assignment_id per_all_assignments_f.assignment_id%type,
431 c_date_earned pay_payroll_actions.date_earned%type ) is
432 select max(pac.assignment_action_id)
433 from pay_assignment_actions pac,
434 per_all_assignments_f paa,
435 pay_payroll_actions ppa
436 where paa.assignment_id = c_assignment_id
437 and paa.assignment_type = 'E'
438 and paa.assignment_id = pac.assignment_id
439 and pac.payroll_action_id = ppa.payroll_action_id
440 and ppa.action_type in ('R','Q','P','U')
441 and ppa.effective_date between to_date('01-01'||to_char(c_date_earned,'YYYY'),'DD-MM-YYYY')
442 and c_date_earned; /* Bug 4267365 */
443
444 /* Bug #2824397 */
445
446 begin
447
448 /* Get the prepayments assignment action id */
449 open c_lock_id(p_assignment_action_id);
450 fetch c_lock_id into v_pp_assignment_action_id;
451 close c_lock_id;
452
453 /* Bug:2883606. If the run is locked by the prepayments then
454 get the balance values for all the runs that are locked by
455 the prepayments and sum it up.
456 Else get the balance value for the single run itself */
457
458 if v_pp_assignment_action_id is not NULL then
459 open run_ids(v_pp_assignment_action_id);
460 loop
461 fetch run_ids into v_cur_run_id;
462 exit when run_ids%NOTFOUND;
463 l_RUN_ASSIGNMENT_ACTION_ID := v_cur_run_id;
464
465 open balance_value ( l_RUN_ASSIGNMENT_ACTION_ID ,p_balance_name,'_ASG_LE_RUN');
466 fetch balance_value into v_asg_le_run;
467 p_current_balance := NVL(p_current_balance,0) + v_asg_le_run;
468 close balance_value;
469 end loop;
470 close run_ids;
471
472 else
473
474 open balance_value ( p_assignment_action_id ,p_balance_name,'_ASG_LE_RUN');
475 fetch balance_value into p_current_balance;
476 close balance_value;
477
478 end if;
479
480 if instr(p_balance_name,'CPF') = 0 then
481 open balance_value (p_assignment_action_id,
482 p_balance_name,
483 '_ASG_LE_YTD');
484 fetch balance_value into p_ytd_balance;
485 close balance_value;
486 else
487 /* Bug 2824397 */
488 /* Get the Date Earned of the Assignment Action */
489
490 open c_date_earned(p_assignment_action_id);
491 fetch c_date_earned into v_date_earned;
492 close c_date_earned;
493
494 hr_utility.trace('Date' ||to_char(v_date_earned));
495 /* Get all the assignments for the person_id and place the data into a PLSQL Table */
496
497 open c_check_assignment(p_person_id,v_date_earned);
498 loop
499 fetch c_check_assignment into assignment_table(v_assign_count);
500 exit when c_check_assignment%NOTFOUND;
501 v_assign_count := v_assign_count + 1;
502 end loop;
503 close c_check_assignment;
504
505
506
507 if assignment_table.count > 1 then
508 for v_counter in 1..assignment_table.count
509 loop
510
511 /* Get the maximum assignment action within the financial year for every
512 assignment fetched */
513
514 v_asg_le_value := 0;
515 open c_max_assign_action(assignment_table(v_counter),v_date_earned);
516 fetch c_max_assign_action into v_ass_act_id;
517 close c_max_assign_action;
518
519 if (v_ass_act_id is not null) then
520 open balance_value (v_ass_act_id,
521 p_balance_name,
522 '_ASG_LE_YTD');
523 fetch balance_value into v_asg_le_value;
524 close balance_value;
525 end if;
526
527 /* Sum up the asg_le value to arrive at the per_le value */
528
529 v_per_le_ytd := nvl(v_per_le_ytd,0) + nvl(v_asg_le_value,0);
530
531 end loop;
532
533 p_ytd_balance := nvl(v_per_le_ytd,0);
534
535 else
536
537 /* If the person has a single assignment_id then directly use asg_le_ytd value,
538 since asg_le_ytd and per_le_ytd value */
539
540 open balance_value (p_assignment_action_id,
541 p_balance_name,
542 '_ASG_LE_YTD');
543 fetch balance_value into p_ytd_balance;
544 close balance_value;
545
546 end if;
547 /* Bug 2824397 */
548
549 end if;
550
551 end current_and_ytd_balances;
552 ------------------------------------------------------------------------
553 -- Procedure to merely pass all the balance results back in one hit,
554 -- rather than 6 separate calls.
555 -- for the SOE window
556 ------------------------------------------------------------------------
557 procedure balance_totals
558 (p_prepaid_tag in varchar,
559 p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
560 p_person_id in per_all_people_f.person_id%type,
561 p_gross_pay_current out nocopy number,
562 p_statutory_deductions_current out nocopy number,
563 p_other_deductions_current out nocopy number,
564 p_net_pay_current out nocopy number,
565 p_non_payroll_current out nocopy number,
566 p_gross_pay_ytd out nocopy number,
567 p_statutory_deductions_ytd out nocopy number,
568 p_other_deductions_ytd out nocopy number,
569 p_net_pay_ytd out nocopy number,
570 p_non_payroll_ytd out nocopy number,
571 p_employee_cpf_current out nocopy number,
572 p_employer_cpf_current out nocopy number,
573 p_cpf_total_current out nocopy number,
574 p_employee_cpf_ytd out nocopy number,
575 p_employer_cpf_ytd out nocopy number,
576 p_cpf_total_ytd out nocopy number)
577 is
578 v_gross_pay_curr number;
579 v_involuntary_deductions_curr number;
580 v_voluntary_deductions_curr number;
581 v_statutory_deductions_curr number;
582 v_net_pay_curr number;
583 v_non_payroll_curr number;
584
585 v_gross_pay_ytd number;
586 v_involuntary_deductions_ytd number;
587 v_voluntary_deductions_ytd number;
588 v_statutory_deductions_ytd number;
589 v_net_pay_ytd number;
590 v_non_payroll_ytd number;
591
592 v_employee_cpf_curr_stat number;
593 v_employee_cpf_ytd_stat number;
594 v_employee_cpf_curr_vol number;
595 v_employee_cpf_ytd_vol number;
596 v_cpf_total_current number;
597 v_employer_cpf_curr_stat number;
598 v_employer_cpf_ytd_stat number;
599 v_employer_cpf_curr_vol number;
600 v_employer_cpf_ytd_vol number;
601 v_cpf_total_ytd number;
602
603 v_tax_id number;
604
605 begin
606 v_tax_id := get_tax_id(p_assignment_action_id);
607
608 pay_balance_pkg.set_context('TAX_UNIT_ID',v_tax_id);
609 -- Call procedure to get Current and YTD balances for Payment Summary Totals
610 hr_utility.trace('JL pay_sg_soe, p_assignment_action_id:'||p_assignment_action_id);
611 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
612 p_assignment_action_id => p_assignment_action_id,
613 p_balance_name => 'Gross Pay',
614 p_person_id => p_person_id,
615 p_current_balance => v_gross_pay_curr,
616 p_ytd_balance => v_gross_pay_ytd);
617 hr_utility.trace('JL v_gross_pay_curr:'||v_gross_pay_curr);
618
619 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
620 p_assignment_action_id => p_assignment_action_id,
621 p_balance_name => 'Statutory Deductions',
622 p_person_id => p_person_id,
623 p_current_balance => v_statutory_deductions_curr,
624 p_ytd_balance => v_statutory_deductions_ytd);
625
626 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
627 p_assignment_action_id => p_assignment_action_id,
628 p_balance_name => 'Involuntary Deductions',
629 p_person_id => p_person_id,
630 p_current_balance => v_involuntary_deductions_curr,
631 p_ytd_balance => v_involuntary_deductions_ytd);
632
633 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
634 p_assignment_action_id => p_assignment_action_id,
635 p_balance_name => 'Voluntary Deductions',
636 p_person_id => p_person_id,
637 p_current_balance => v_voluntary_deductions_curr,
638 p_ytd_balance => v_voluntary_deductions_ytd);
639
640 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
641 p_assignment_action_id => p_assignment_action_id,
642 p_balance_name => 'Net',
643 p_person_id => p_person_id,
644 p_current_balance => v_net_pay_curr,
645 p_ytd_balance => v_net_pay_ytd);
646
647 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
648 p_assignment_action_id => p_assignment_action_id,
649 p_balance_name => 'Non Payroll Payments',
650 p_person_id => p_person_id,
651 p_current_balance => v_non_payroll_curr,
652 p_ytd_balance => v_non_payroll_ytd);
653
654 p_gross_pay_current := v_gross_pay_curr;
655 p_statutory_deductions_current := v_statutory_deductions_curr;
656 p_other_deductions_current := v_involuntary_deductions_curr +
657 v_voluntary_deductions_curr;
658 p_net_pay_current := v_net_pay_curr;
659 p_non_payroll_current := v_non_payroll_curr;
660
661 p_gross_pay_ytd := v_gross_pay_ytd;
662 p_other_deductions_ytd := v_involuntary_deductions_ytd +
663 v_voluntary_deductions_ytd;
664 p_statutory_deductions_ytd := v_statutory_deductions_ytd;
665 p_net_pay_ytd := v_net_pay_ytd;
666 p_non_payroll_ytd := v_non_payroll_ytd;
667
668 -- Call procedure to get Current and YTD balances for CPF Summary Totals
669 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
670 p_assignment_action_id => p_assignment_action_id,
671 p_balance_name => 'CPF Withheld',
672 p_person_id => p_person_id,
673 p_current_balance => v_employee_cpf_curr_stat,
674 p_ytd_balance => v_employee_cpf_ytd_stat);
675
676 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
677 p_assignment_action_id => p_assignment_action_id,
678 p_balance_name => 'Voluntary CPF Withheld',
679 p_person_id => p_person_id,
680 p_current_balance => v_employee_cpf_curr_vol,
681 p_ytd_balance => v_employee_cpf_ytd_vol);
682
683 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
684 p_assignment_action_id => p_assignment_action_id,
685 p_balance_name => 'CPF Liability',
686 p_person_id => p_person_id,
687 p_current_balance => v_employer_cpf_curr_stat,
688 p_ytd_balance => v_employer_cpf_ytd_stat);
689
690 current_and_ytd_balances (p_prepaid_tag => p_prepaid_tag,
691 p_assignment_action_id => p_assignment_action_id,
692 p_balance_name => 'Voluntary CPF Liability',
693 p_person_id => p_person_id,
694 p_current_balance => v_employer_cpf_curr_vol,
695 p_ytd_balance => v_employer_cpf_ytd_vol);
696
697
698 p_employee_cpf_current := v_employee_cpf_curr_stat + v_employee_cpf_curr_vol;
699 p_employer_cpf_current := v_employer_cpf_curr_stat + v_employer_cpf_curr_vol;
700 p_cpf_total_current := p_employee_cpf_current + p_employer_cpf_current;
701
702 p_employee_cpf_ytd := v_employee_cpf_ytd_stat + v_employee_cpf_ytd_vol;
703 p_employer_cpf_ytd := v_employer_cpf_ytd_stat + v_employer_cpf_ytd_vol;
704 p_cpf_total_ytd := p_employee_cpf_ytd + p_employer_cpf_ytd;
705 end balance_totals;
706 ------------------------------------------------------------------------
707 -- Procedure to merely pass all the balance results back in one hit,
708 -- rather than 6 separate calls.
709 -- for the Pay Advice report
710 ------------------------------------------------------------------------
711 procedure balance_totals
712 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
713 p_person_id in per_all_people_f.person_id%type,
714 p_gross_pay_current out nocopy number,
715 p_statutory_deductions_current out nocopy number,
716 p_other_deductions_current out nocopy number,
717 p_net_pay_current out nocopy number,
718 p_non_payroll_current out nocopy number,
719 p_gross_pay_ytd out nocopy number,
720 p_statutory_deductions_ytd out nocopy number,
721 p_other_deductions_ytd out nocopy number,
722 p_net_pay_ytd out nocopy number,
723 p_non_payroll_ytd out nocopy number,
724 p_employee_cpf_current out nocopy number,
725 p_employer_cpf_current out nocopy number,
726 p_cpf_total_current out nocopy number,
727 p_employee_cpf_ytd out nocopy number,
728 p_employer_cpf_ytd out nocopy number,
729 p_cpf_total_ytd out nocopy number)
730 is
731 v_gross_pay_curr number;
732 v_involuntary_deductions_curr number;
733 v_voluntary_deductions_curr number;
734 v_statutory_deductions_curr number;
735 v_net_pay_curr number;
736 v_non_payroll_curr number;
737
738 v_gross_pay_ytd number;
739 v_involuntary_deductions_ytd number;
740 v_voluntary_deductions_ytd number;
741 v_statutory_deductions_ytd number;
742 v_net_pay_ytd number;
743 v_non_payroll_ytd number;
744
745 v_employee_cpf_curr_stat number;
746 v_employee_cpf_ytd_stat number;
747 v_employee_cpf_curr_vol number;
748 v_employee_cpf_ytd_vol number;
749 v_cpf_total_current number;
750 v_employer_cpf_curr_stat number;
751 v_employer_cpf_ytd_stat number;
752 v_employer_cpf_curr_vol number;
753 v_employer_cpf_ytd_vol number;
754 v_cpf_total_ytd number;
755
756 v_tax_id number;
757
758 begin
759 v_tax_id := get_tax_id(p_assignment_action_id);
760 pay_balance_pkg.set_context('TAX_UNIT_ID',v_tax_id);
761 -- Call procedure to get Current and YTD balances for Payment Summary Totals
762 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
763 p_balance_name => 'Gross Pay',
764 p_person_id => p_person_id,
765 p_current_balance => v_gross_pay_curr,
766 p_ytd_balance => v_gross_pay_ytd);
767
768 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
769 p_balance_name => 'Statutory Deductions',
770 p_person_id => p_person_id,
771 p_current_balance => v_statutory_deductions_curr,
772 p_ytd_balance => v_statutory_deductions_ytd);
773
774 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
775 p_balance_name => 'Involuntary Deductions',
776 p_person_id => p_person_id,
777 p_current_balance => v_involuntary_deductions_curr,
778 p_ytd_balance => v_involuntary_deductions_ytd);
779
780 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
781 p_balance_name => 'Voluntary Deductions',
782 p_person_id => p_person_id,
783 p_current_balance => v_voluntary_deductions_curr,
784 p_ytd_balance => v_voluntary_deductions_ytd);
785
786 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
787 p_balance_name => 'Net',
788 p_person_id => p_person_id,
789 p_current_balance => v_net_pay_curr,
790 p_ytd_balance => v_net_pay_ytd);
791
792 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
793 p_balance_name => 'Non Payroll Payments',
794 p_person_id => p_person_id,
795 p_current_balance => v_non_payroll_curr,
796 p_ytd_balance => v_non_payroll_ytd);
797
798 p_gross_pay_current := v_gross_pay_curr;
799 p_statutory_deductions_current := v_statutory_deductions_curr;
800 p_other_deductions_current := v_involuntary_deductions_curr +
801 v_voluntary_deductions_curr;
802 p_net_pay_current := v_net_pay_curr;
803 p_non_payroll_current := v_non_payroll_curr;
804
805 p_gross_pay_ytd := v_gross_pay_ytd;
806 p_other_deductions_ytd := v_involuntary_deductions_ytd +
807 v_voluntary_deductions_ytd;
808 p_statutory_deductions_ytd := v_statutory_deductions_ytd;
809 p_net_pay_ytd := v_net_pay_ytd;
810 p_non_payroll_ytd := v_non_payroll_ytd;
811
812 -- Call procedure to get Current and YTD balances for CPF Summary Totals
813 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
814 p_balance_name => 'CPF Withheld',
815 p_person_id => p_person_id,
816 p_current_balance => v_employee_cpf_curr_stat,
817 p_ytd_balance => v_employee_cpf_ytd_stat);
818
819 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
820 p_balance_name => 'Voluntary CPF Withheld',
821 p_person_id => p_person_id,
822 p_current_balance => v_employee_cpf_curr_vol,
823 p_ytd_balance => v_employee_cpf_ytd_vol);
824
825 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
826 p_balance_name => 'CPF Liability',
827 p_person_id => p_person_id,
828 p_current_balance => v_employer_cpf_curr_stat,
829 p_ytd_balance => v_employer_cpf_ytd_stat);
830
831 current_and_ytd_balances (p_assignment_action_id => p_assignment_action_id,
832 p_balance_name => 'Voluntary CPF Liability',
833 p_person_id => p_person_id,
834 p_current_balance => v_employer_cpf_curr_vol,
835 p_ytd_balance => v_employer_cpf_ytd_vol);
836
837
838 p_employee_cpf_current := v_employee_cpf_curr_stat + v_employee_cpf_curr_vol;
839 p_employer_cpf_current := v_employer_cpf_curr_stat + v_employer_cpf_curr_vol;
840 p_cpf_total_current := p_employee_cpf_current + p_employer_cpf_current;
841
842 p_employee_cpf_ytd := v_employee_cpf_ytd_stat + v_employee_cpf_ytd_vol;
843 p_employer_cpf_ytd := v_employer_cpf_ytd_stat + v_employer_cpf_ytd_vol;
844 p_cpf_total_ytd := p_employee_cpf_ytd + p_employer_cpf_ytd;
845 end balance_totals;
846
847 ------------------------------------------------------------------------
848 -- get exchange rate for the payroll action effective_date
849 -- to be used by pay_sg_asg_elements_v
850 -- to convert the pay value from other currencies to BG currency
851 ------------------------------------------------------------------------
852 function get_exchange_rate
853 (p_from_currency in gl_daily_rates.from_currency%type,
854 p_to_currency in gl_daily_rates.to_currency%type,
855 eff_date in gl_daily_rates.conversion_date%type,
856 p_business_group_id in pay_user_columns.business_group_id%type )
857 return number is
858 CURSOR rate is SELECT gdr.conversion_rate
859 FROM gl_daily_rates gdr, gl_daily_conversion_types gdct
860 WHERE gdr.conversion_type = gdct.conversion_type
861 AND gdr.from_currency = p_from_currency
862 AND gdr.to_currency = p_to_currency
863 AND gdr.conversion_date = eff_date
864 AND gdct.user_conversion_type = (
865 SELECT puci.value
866 FROM pay_user_column_instances_f puci,
867 pay_user_rows_f pur,
868 pay_user_columns puc,
869 pay_user_tables put
870 WHERE puci.user_row_id = pur.user_row_id
871 AND puci.user_column_id = puc.user_column_id
872 AND pur.user_table_id = put.user_table_id
873 AND puc.user_table_id = put.user_table_id
874 AND puci.business_group_id = p_business_group_id
875 AND pur.ROW_LOW_RANGE_OR_NAME = 'PAY'
876 AND put.user_table_name = 'EXCHANGE_RATE_TYPES' );
877 v_rate number;
878 BEGIN
879 IF p_from_currency <> p_to_currency THEN
880 OPEN rate;
881 FETCH rate INTO v_rate;
882 IF rate%NOTFOUND THEN
883 v_rate := null;
884 END IF;
885 END IF;
886 return(v_rate);
887 CLOSE rate;
888 end get_exchange_rate;
889 ------------------------------------------------------------------------
890 -- get tax_unit_id for an assignment_action_id
891 -- needed in setting the context id
892 ------------------------------------------------------------------------
893 function get_tax_id
894 ( p_assignment_action_id number)
895 return number is
896 cursor tax is SELECT tax_unit_id
897 FROM pay_assignment_actions
898 WHERE assignment_action_id = p_assignment_action_id;
899 v_tax_id number;
900 BEGIN
901 open tax;
902 fetch tax into v_tax_id;
903 if tax%NOTFOUND then
904 close tax;
905 v_tax_id := null;
906 end if;
907 close tax;
908 return(v_tax_id);
909 END get_tax_id;
910 ------------------------------------------------------------------------
911 -- Selects the Home Address for the Person.
912 ------------------------------------------------------------------------
913 procedure get_home_address
914 (p_person_id in per_addresses.person_id%type,
915 p_address_line1 out nocopy per_addresses.address_line1%type,
916 p_address_line2 out nocopy per_addresses.address_line2%type,
917 p_address_line3 out nocopy per_addresses.address_line3%type,
918 p_town_city out nocopy per_addresses.town_or_city%type,
919 p_postal_code out nocopy per_addresses.postal_code%type,
920 p_country_name out nocopy fnd_territories_tl.territory_short_name%type) is
921
922 cursor home_address
923 (c_person_id per_addresses.person_id%type) is
924 select pad.address_line1,
925 pad.address_line2,
926 pad.address_line3,
927 pad.town_or_city,
928 pad.postal_code,
929 ftt.territory_short_name
930 from per_addresses pad,
931 fnd_territories_tl ftt
932 where pad.person_id = c_person_id
933 and ftt.language = userenv('LANG')
934 and ftt.territory_code = pad.country
935 and sysdate between nvl(pad.date_from, sysdate) and nvl(pad.date_to, sysdate);
936
937 begin
938 open home_address(p_person_id);
939 fetch home_address into p_address_line1,
940 p_address_line2,
941 p_address_line3,
942 p_town_city,
943 p_postal_code,
944 p_country_name;
945 close home_address;
946 end get_home_address;
947 ------------------------------------------------------------------------
948 -- Selects the Work Address for the Person.
949 ------------------------------------------------------------------------
950 procedure get_work_address
951 (p_location_id in hr_locations.location_id%type,
952 p_address_line1 out nocopy hr_locations.address_line_1%type,
953 p_address_line2 out nocopy hr_locations.address_line_2%type,
954 p_address_line3 out nocopy hr_locations.address_line_3%type,
955 p_town_city out nocopy hr_locations.town_or_city%type,
956 p_postal_code out nocopy hr_locations.postal_code%type,
957 p_country_name out nocopy fnd_territories_tl.territory_short_name%type) is
958
959 cursor c_get_work_address
960 (c_location_id hr_locations.location_id%type) is
961 select hrl.address_line_1,
962 hrl.address_line_2,
963 hrl.address_line_3,
964 hrl.town_or_city,
965 hrl.postal_code,
966 ftt.territory_short_name
967 from hr_locations hrl,
968 fnd_territories_tl ftt
969 where hrl.location_id = c_location_id
970 and ftt.language (+) = userenv('LANG')
971 and ftt.territory_code (+) = hrl.country;
972
973 begin
974 open c_get_work_address(p_location_id);
975 fetch c_get_work_address into p_address_line1,
976 p_address_line2,
977 p_address_line3,
978 p_town_city,
979 p_postal_code,
980 p_country_name;
981 close c_get_work_address;
982 end get_work_address;
983 ------------------------------------------------------------------------
984 -- Returns the Currency Code for the Business Group.
985 ------------------------------------------------------------------------
986 function business_currency_code
987 (p_business_group_id in hr_organization_units.business_group_id%type)
988 return fnd_currencies.currency_code%type is
989
990 v_currency_code fnd_currencies.currency_code%type;
991
992 cursor currency_code
993 (c_business_group_id hr_organization_units.business_group_id%type) is
994 select fcu.currency_code
995 from hr_organization_information hoi,
996 hr_organization_units hou,
997 fnd_currencies fcu
998 where hou.business_group_id = c_business_group_id
999 and hou.organization_id = hoi.organization_id
1000 and hoi.org_information_context = 'Business Group Information'
1001 and fcu.issuing_territory_code = hoi.org_information9;
1002
1003 begin
1004 open currency_code (p_business_group_id);
1005 fetch currency_code into v_currency_code;
1006 close currency_code;
1007 return v_currency_code;
1008 end business_currency_code;
1009
1010 ------------------------------------------------------------------------
1011 -- Bug#5633652 - Returns currency code based on the payment method attached
1012 -- to a payroll
1013 ------------------------------------------------------------------------
1014 function get_payroll_currency_code
1015 (p_payroll_id in pay_payrolls_f.payroll_id%type,
1016 p_effective_date in pay_payroll_actions.effective_date%type)
1017 return fnd_currencies.currency_code%type is
1018
1019 v_currency_code fnd_currencies.currency_code%type;
1020
1021 cursor csr_currency_code
1022 (c_payroll_id pay_assignment_actions.assignment_action_id%type,
1023 c_effective_date in pay_payroll_actions.effective_date%type) is
1024 select popm.currency_code
1025 from pay_payrolls_f ppf,
1026 pay_org_payment_methods_f popm
1027 where ppf.payroll_id = c_payroll_id
1028 and popm.org_payment_method_id = ppf.default_payment_method_id
1029 and c_effective_date between ppf.effective_start_date and ppf.effective_end_date
1030 and c_effective_date between popm.effective_start_date and popm.effective_end_date;
1031
1032 begin
1033 open csr_currency_code (p_payroll_id,p_effective_date);
1034 fetch csr_currency_code into v_currency_code;
1035 if csr_currency_code%NOTFOUND then
1036 v_currency_code := 'SGD';
1037 end if;
1038 close csr_currency_code;
1039 return v_currency_code;
1040 end get_payroll_currency_code;
1041
1042 ------------------------------------------------------------------------
1043 -- Bug#5633652 - Returns currency code based on the payroll attached
1044 -- to an assignment
1045 ------------------------------------------------------------------------
1046 function get_assignment_currency_code
1047 (p_assignment_id in per_all_assignments_f.assignment_id%type,
1048 p_effective_date in pay_payroll_actions.effective_date%type)
1049 return fnd_currencies.currency_code%type is
1050
1051 v_currency_code fnd_currencies.currency_code%type;
1052 v_payroll_id pay_payrolls_f.payroll_id%type;
1053
1054 cursor csr_get_payroll_id
1055 (c_assignment_id pay_assignment_actions.assignment_action_id%type,
1056 c_effective_date in pay_payroll_actions.effective_date%type) is
1057 select payroll_id
1058 from per_all_assignments_f
1059 where assignment_id = c_assignment_id
1060 and c_effective_date between effective_start_date and effective_end_date;
1061
1062 begin
1063 open csr_get_payroll_id(p_assignment_id,p_effective_date);
1064 fetch csr_get_payroll_id into v_payroll_id;
1065 if csr_get_payroll_id%FOUND then
1066 v_currency_code := get_payroll_currency_code(v_payroll_id,p_effective_date);
1067 else
1068 v_currency_code := 'SGD';
1069 end if;
1070 close csr_get_payroll_id;
1071 return v_currency_code;
1072 end get_assignment_currency_code;
1073
1074 end pay_sg_soe;