1 PACKAGE BODY pay_us_dedn_pkg AS
2 /* $Header: pyusdedn.pkb 120.1 2010/05/07 06:37:25 jdevasah ship $ */
3 --
4 ------------------------------------------------------------------------
5 -- PAY_US_TOT_OWED returns total owed input value of those deduction elements
6 -- for which the stop rule is defined as total reached.
7 -------------------------------------------------------------------------
8 FUNCTION pay_us_tot_owed (
9 p_assignment_id IN NUMBER DEFAULT NULL
10 ,p_element_type_id IN NUMBER DEFAULT NULL
11 ,p_effective_date IN DATE DEFAULT NULL
12 ,p_date_earned IN DATE DEFAULT NULL)
13
14 RETURN number IS
15 --
16 l_Total_owed NUMBER;
17 --
18 BEGIN
19 --
20 l_Total_owed := 0;
21 BEGIN
22 -- Bug#9393014: Added SUM in the select clause
23 select sum(nvl(peev.screen_entry_value,0))
24 into l_Total_owed
25 from
26 pay_element_entry_values_f peev,
27 pay_input_values_f piv,
28 pay_element_links_f pel,
29 pay_element_entries_f pee
30 where pee.assignment_id = p_assignment_id
31 and p_date_earned between pee.effective_start_date
32 and pee.effective_end_date
33 and pee.element_link_id = pel.element_link_id
34 and p_date_earned between pel.effective_start_date
35 and pel.effective_end_date
36 and pel.element_type_id = p_element_type_id
37 and piv.element_type_id = p_element_type_id
38 and piv.name = 'Total Owed'
39 and p_date_earned between piv.effective_start_date
40 and piv.effective_end_date
41 and piv.input_value_id = peev.input_value_id
42 and pee.element_entry_id = peev.element_entry_id
43 and p_date_earned between peev.effective_start_date
44 and peev.effective_end_date;
45
46 EXCEPTION
47 WHEN TOO_MANY_ROWS THEN
48 return -9999999.99;
49 WHEN OTHERS THEN
50 null;
51 END;
52
53 RETURN l_Total_owed;
54 --
55 END pay_us_tot_owed;
56 end pay_us_dedn_pkg;
[Home] [Help]