1 PACKAGE BODY pay_ca_dedn_pkg AS
2 /* $Header: pycadedn.pkb 120.0 2005/05/29 03:29:57 appldev noship $ */
3 --
4 ------------------------------------------------------------------------
5 -- PAY_CA_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_ca_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 select peev.screen_entry_value
23 into l_Total_owed
24 from
25 pay_element_entry_values_f peev,
26 pay_input_values_f piv,
27 pay_element_links_f pel,
28 pay_element_entries_f pee
29 where pee.assignment_id = p_assignment_id
30 and p_date_earned between pee.effective_start_date
31 and pee.effective_end_date
32 and pee.element_link_id = pel.element_link_id
33 and p_date_earned between pel.effective_start_date
34 and pel.effective_end_date
35 and pel.element_type_id = p_element_type_id
36 and piv.element_type_id = p_element_type_id
37 and piv.name = 'Total Owed'
38 and p_date_earned between piv.effective_start_date
39 and piv.effective_end_date
40 and piv.input_value_id = peev.input_value_id
41 and pee.element_entry_id = peev.element_entry_id
42 and p_date_earned between peev.effective_start_date
43 and peev.effective_end_date;
44
45 EXCEPTION
46 WHEN TOO_MANY_ROWS THEN
47 return -9999999.99;
48 WHEN OTHERS THEN
49 null;
50 END;
51
52 RETURN l_Total_owed;
53 --
54 END pay_ca_tot_owed;
55 end pay_ca_dedn_pkg;
[Home] [Help]