[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_WAT_UDFS
Source
1 PACKAGE BODY PAY_CA_WAT_UDFS AS
2 /* $Header: pycagudf.pkb 120.0 2005/05/29 03:33:42 appldev noship $ */
3 FUNCTION ca_garn_subpriority (p_bus_grp_id in NUMBER,
4 p_assignment_id in NUMBER,
5 p_element_entry_id in NUMBER,
6 p_earned_date in DATE)
7 RETURN NUMBER IS
8
9 CURSOR garn_priority_cursor is
10 SELECT peef.element_entry_id,
11 decode(peef.entry_information1
12 ,'P1',1000
13 ,'P2',2000
14 ,'P3',3000,9000) garn_priority
15 FROM pay_element_entries_f peef
16 ,pay_element_classifications pec
17 ,pay_element_types_f petf
18 ,pay_element_links_f pelf
19 WHERE peef.assignment_id = p_assignment_id
20 AND trunc(p_earned_date) between trunc(peef.effective_start_date)
21 and
22 trunc(peef.effective_end_date)
23 AND peef.element_link_id = pelf.element_link_id
24 AND trunc(p_earned_date) between trunc(pelf.effective_start_date)
25 and
26 trunc(pelf.effective_end_date)
27 AND pelf.element_type_id = petf.element_type_id
28 AND trunc(p_earned_date) between trunc(petf.effective_start_date)
29 and
30 trunc(petf.effective_end_date)
31 AND petf.classification_id = pec.classification_id
32 AND pec.classification_name like 'Involuntary Deductions'
33 ORDER BY decode(peef.entry_information1
34 ,'P1',1000
35 ,'P2',2000
36 ,'P3',3000,9000),
37 decode(peef.entry_information3
38 ,'ORDER',1
39 ,'EQUAL',2
40 ,'PROPORTION',2,9),
41 peef.entry_information4;
42
43 return_priority NUMBER(4);
44 counter NUMBER(4);
45 v_entry_id NUMBER(15);
46 v_garn_priority NUMBER(4);
47
48 BEGIN
49 return_priority := 9999;
50 counter := 10;
51 OPEN garn_priority_cursor;
52 LOOP
53 FETCH garn_priority_cursor
54 INTO v_entry_id, v_garn_priority;
55 IF v_entry_id = p_element_entry_id THEN
56 return_priority := counter + v_garn_priority;
57 END IF;
58 EXIT WHEN garn_priority_cursor %NOTFOUND;
59 counter := counter + 10;
60 END LOOP;
61 CLOSE garn_priority_cursor;
62 RETURN return_priority;
63 END;
64 --
65 -- ************************************
66 --
67 FUNCTION ca_garn_bc_exempt( p_bus_grp_id in NUMBER,
68 p_element_entry_id in NUMBER,
69 p_pay_periods_per_year in NUMBER,
70 p_protected_basis in VARCHAR2,
71 p_gross_di_subject in NUMBER)
72 RETURN NUMBER IS
73
74 TYPE number_tabtype is TABLE OF NUMBER(15,2) INDEX BY BINARY_INTEGER;
75
76 bc_band number_tabtype;
77 bc_break number_tabtype;
78 di_subject_exemption NUMBER(15,2);
79
80 BEGIN
81 --
82 -- Set Legislation Defined Exemption Percentages
83 --
84 di_subject_exemption := 0;
85 IF p_protected_basis = 'BC_MS_PRE' THEN
86 bc_band(1) := 65;
87 bc_band(2) := 50;
88 bc_band(3) := 45;
89 ELSIF p_protected_basis = 'BC_MS_POST' THEN
90 bc_band(1) := 75;
91 bc_band(2) := 60;
92 bc_band(3) := 55;
93 ELSE
94 RETURN di_subject_exemption;
95 END IF;
96 --
97 -- Set Legislation Defined Exemption Breakpoints for
98 -- specific payroll frequency
99 --
100 IF p_pay_periods_per_year = 52 THEN
101 bc_break(1) := 150;
102 bc_break(2) := 520;
103 bc_break(3) := 1155;
104 ELSIF p_pay_periods_per_year = 26 THEN
105 bc_break(1) := 300;
106 bc_break(2) := 1040;
107 bc_break(3) := 2310;
108 ELSIF p_pay_periods_per_year = 24 THEN
109 bc_break(1) := 325;
110 bc_break(2) := 1125;
111 bc_break(3) := 2500;
112 ELSIF p_pay_periods_per_year = 12 THEN
113 bc_break(1) := 650;
114 bc_break(2) := 2250;
115 bc_break(3) := 5000;
116 ELSE
117 RETURN di_subject_exemption;
118 END IF;
119 --
120 -- Calculate di_subject_exemption
121 --
122 di_subject_exemption := least(bc_break(1),p_gross_di_subject);
123 IF p_gross_di_subject > bc_break(1) THEN
124 di_subject_exemption := di_subject_exemption+
125 ((greatest(least(bc_break(2),p_gross_di_subject),bc_break(1))-bc_break(1))*bc_band(1)/100);
126 END IF;
127 IF p_gross_di_subject > bc_break(2) THEN
128 di_subject_exemption := di_subject_exemption+
129 ((greatest(least(bc_break(3),p_gross_di_subject),bc_break(2))-bc_break(2))*bc_band(2)/100);
130 END IF;
131 IF p_gross_di_subject > bc_break(3) THEN
132 di_subject_exemption := di_subject_exemption+
133 ((greatest(bc_break(3),p_gross_di_subject)-bc_break(3))*bc_band(3)/100);
134 END IF;
135 RETURN di_subject_exemption;
136 END;
137 END;