1 PACKAGE pqp_gb_pension_functions
2 -- /* $Header: pqpgbpef.pkh 120.0.12010000.1 2008/07/28 11:16:44 appldev ship $ */
3 AS
4 --
5 -- Debug Variables.
6 --
7 g_proc_name VARCHAR2 (61) := 'pqp_gb_pension_functions.';
8 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
9 g_debug BOOLEAN := hr_utility.debug_enabled;
10
11 -- Cursor to retrieve element entries information
12 -- for a given assignment
13 -- retrieve only those elements classified as "Pre Tax Deductions" and
14 -- "Voluntary Deductions"
15
16 CURSOR csr_get_ele_entry_info (
17 c_assignment_id NUMBER
18 ,c_element_type_id NUMBER
19 ,c_effective_date DATE
20 )
21 IS
22 SELECT DISTINCT (pee.element_link_id)
23 FROM pay_element_entries_f pee
24 WHERE pee.assignment_id = c_assignment_id
25 AND c_effective_date BETWEEN pee.effective_start_date
26 AND pee.effective_end_date
27 AND entry_type = 'E'
28 AND EXISTS (
29 SELECT 1
30 FROM pay_element_links_f pel
31 ,pay_element_types_f pet
32 ,pay_element_classifications pec
33 WHERE pel.element_link_id = pee.element_link_id
34 AND pel.element_type_id <> c_element_type_id
35 AND pet.element_type_id = pel.element_type_id
36 AND pet.classification_id =
37 pec.classification_id
38 -- Added to improve performance
39 AND pec.classification_name IN
40 ('Pre Tax Deductions'
41 ,'Voluntary Deductions'
42 )
43 AND pec.legislation_code = g_legislation_code);
44
45 TYPE t_number IS TABLE OF NUMBER
46 INDEX BY BINARY_INTEGER;
47
48 -- Cursor to get the element type information
49 -- from element links
50 CURSOR csr_get_ele_type_id (c_element_link_id NUMBER, c_effective_date DATE)
51 IS
52 SELECT element_type_id
53 FROM pay_element_links_f
54 WHERE element_link_id = c_element_link_id
55 AND c_effective_date BETWEEN effective_start_date
56 AND effective_end_date;
57
58 -- Cursor to get the element link information
59 -- from element links for a given type id
60 CURSOR csr_get_ele_link_id (
61 c_element_type_id NUMBER
62 ,c_business_group_id NUMBER
63 ,c_effective_date DATE
64 )
65 IS
66 SELECT element_link_id
67 FROM pay_element_links_f
68 WHERE element_type_id = c_element_type_id
69 AND business_group_id = c_business_group_id
70 AND c_effective_date BETWEEN effective_start_date
71 AND effective_end_date;
72
73 -- Cursor to check whether the element type is
74 -- a pension element for a given pension category
75 CURSOR csr_chk_is_this_pens_ele (
76 c_element_type_id NUMBER
77 ,c_effective_date DATE
78 ,c_pension_category VARCHAR2
79 )
80 IS
81 SELECT 'X'
82 FROM pay_element_types_f pet
83 WHERE pet.element_type_id = c_element_type_id
84 AND c_effective_date BETWEEN pet.effective_start_date
85 AND pet.effective_end_date
86 AND EXISTS ( SELECT 1
87 FROM pay_element_type_extra_info
88 WHERE element_type_id = pet.element_type_id
89 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO'
90 AND eei_information4 = c_pension_category
91 AND eei_information12 IS NULL);
92
93 -- Cursor to retrieve pension type information
94 CURSOR csr_get_pens_type_info (
95 c_pension_type_id NUMBER
96 ,c_business_group_id NUMBER
97 ,c_effective_date DATE
98 )
99 IS
100 SELECT pension_type_id, pension_type_name, pension_category
101 ,NVL (minimum_age, 0) minimum_age, NVL (maximum_age, 0) maximum_age
102 ,effective_start_date, effective_end_date
103 FROM pqp_pension_types_f
104 WHERE pension_type_id = c_pension_type_id
105 AND ( ( business_group_id IS NOT NULL
106 AND business_group_id = c_business_group_id
107 )
108 OR ( legislation_code IS NOT NULL
109 AND legislation_code = g_legislation_code
110 )
111 OR (business_group_id IS NULL AND legislation_code IS NULL)
112 )
113 AND c_effective_date BETWEEN effective_start_date
114 AND effective_end_date;
115
116 TYPE r_pension_types IS RECORD (
117 pension_type_id pqp_pension_types_f.pension_type_id%TYPE
118 ,pension_type_name pqp_pension_types_f.pension_type_name%TYPE
119 ,pension_category pqp_pension_types_f.pension_category%TYPE
120 ,minimum_age pqp_pension_types_f.minimum_age%TYPE
121 ,maximum_age pqp_pension_types_f.maximum_age%TYPE
122 ,effective_start_date pqp_pension_types_f.effective_start_date%TYPE
123 ,effective_end_date pqp_pension_types_f.effective_end_date%TYPE);
124
125 TYPE t_pension_types IS TABLE OF r_pension_types
126 INDEX BY BINARY_INTEGER;
127
128 TYPE r_element_types IS RECORD (
129 element_type_id NUMBER
130 ,assignment_id NUMBER
131 ,effective_start_date DATE
132 ,effective_end_date DATE
133 ,yes_no_opt VARCHAR2 (1));
134
135 TYPE t_element_types IS TABLE OF r_element_types
136 INDEX BY BINARY_INTEGER;
137
138 -- Changed cursor for BUG 3637584
139 -- Cursor to check existence of an element entry
140 -- for an assignment
141 CURSOR csr_chk_ele_entry_exists (
142 c_assignment_id NUMBER
143 -- ,c_element_link_id NUMBER
144 ,c_element_type_id NUMBER
145 ,c_business_group_id NUMBER
146 ,c_effective_date DATE
147 )
148 IS
149 SELECT pee.element_entry_id, pee.effective_start_date, pee.effective_end_date
150 FROM pay_element_entries_f pee
151 ,pay_element_links_f pel
152 WHERE pee.assignment_id = c_assignment_id
153 AND pee.element_link_id = pel.element_link_id
154 AND pee.entry_type = 'E'
155 AND c_effective_date BETWEEN pee.effective_start_date
156 AND pee.effective_end_date
157 AND pel.element_type_id = c_element_type_id
158 AND pel.business_group_id = c_business_group_id
159 AND c_effective_date BETWEEN pel.effective_start_date
160 AND pel.effective_end_date;
161
162 -- Cursor to get element name
163 CURSOR csr_get_ele_name (c_element_type_id NUMBER, c_effective_date DATE)
164 IS
165 SELECT element_name
166 FROM pay_element_types_f
167 WHERE element_type_id = c_element_type_id
168 AND c_effective_date BETWEEN effective_start_date
169 AND effective_end_date;
170
171 -- Cursor to get the second base element for
172 -- the same pension scheme name
173 CURSOR csr_get_sch_oth_ele_id (
174 c_element_type_id NUMBER
175 ,c_business_group_id NUMBER
176 )
177 IS
178 SELECT pps.element_type_id
179 FROM pqp_gb_pension_schemes_v pps, pay_element_type_extra_info eeit
180 WHERE pps.element_type_id <> c_element_type_id
181 AND pps.pension_scheme_name = eeit.eei_information1
182 AND eeit.element_type_id = c_element_type_id
183 AND eeit.information_type = 'PQP_GB_PENSION_SCHEME_INFO'
184 AND pps.business_group_id = c_business_group_id;
185
186 -- Procedures
187
188 -- Debug
189 PROCEDURE DEBUG (
190 p_trace_message IN VARCHAR2
191 ,p_trace_location IN NUMBER DEFAULT NULL
192 );
193
194 -- Debug_Enter
195 PROCEDURE debug_enter (
196 p_proc_name IN VARCHAR2
197 ,p_trace_on IN VARCHAR2 DEFAULT NULL
198 );
199
200 -- Debug_Exit
201 PROCEDURE debug_exit (
202 p_proc_name IN VARCHAR2
203 ,p_trace_off IN VARCHAR2 DEFAULT NULL
204 );
205
206 -- Debug Others
207 PROCEDURE debug_others (
208 p_proc_name IN VARCHAR2
209 ,p_proc_step IN NUMBER DEFAULT NULL
210 );
211
212 -- Public Functions
213
214 -- Function to check multiple pension membership for the same
215 -- pension category
216
217 FUNCTION chk_multiple_membership (
218 p_assignment_id IN NUMBER -- Context
219 ,p_element_type_id IN NUMBER -- Context
220 ,p_effective_date IN DATE -- Context
221 ,p_pension_category IN VARCHAR2
222 ,p_yes_no OUT NOCOPY VARCHAR2
223 ,p_error_msg OUT NOCOPY VARCHAR2
224 )
225 RETURN NUMBER;
226
227 --
228 -- Function to get the pension type information
229 FUNCTION get_ele_pens_type_info (
230 p_business_group_id IN NUMBER -- Context
231 ,p_effective_date IN DATE -- Context
232 ,p_element_type_id IN NUMBER -- Context
233 ,p_minimum_age OUT NOCOPY NUMBER
234 ,p_maximum_age OUT NOCOPY NUMBER
235 ,p_error_msg OUT NOCOPY VARCHAR2
236 )
237 RETURN NUMBER;
238
239 --
240 -- Function to check whether an element entry exists for
241 -- a given assignment and element type
242 FUNCTION chk_element_entry_exists (
243 p_assignment_id IN NUMBER -- Context
244 ,p_business_group_id IN NUMBER -- Context
245 ,p_effective_date IN DATE -- Context
246 ,p_element_type_id IN NUMBER
247 ,p_yes_no OUT NOCOPY VARCHAR2
248 ,p_error_msg OUT NOCOPY VARCHAR2
249 )
250 RETURN NUMBER;
251 --
252
253 END pqp_gb_pension_functions;
[Home] [Help]