1 PACKAGE PQP_GB_PSI_WPS_HISTORY AS
2 -- /* $Header: pqpgbpsiwps.pkh 120.1 2007/05/24 06:35:02 jvaradra noship $ */
3
4 --
5 -- Debug Variables.
6 --
7
8 g_proc_name VARCHAR2(61):= 'PQP_GB_PSI_WPS_HISTORY.';
9
10 g_debug BOOLEAN := hr_utility.debug_enabled;
11 c_application_id CONSTANT NUMBER := 8303;
12 c_highest_date CONSTANT DATE := hr_api.g_eot;
13 g_person_id NUMBER := NULL;
14 g_business_group_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
15 g_assignment_id NUMBER := NULL; -- IMPORTANT TO KEEP NULL
16 g_person_dtl per_all_people_f%rowtype;
17 g_assignment_dtl per_all_assignments_f%rowtype;
18 g_altkey VARCHAR2(12):= NULL;
19 g_current_run varchar2(20) := NULL;
20 g_prev_event_dtl_rec ben_ext_person.t_detailed_output_tab_rec;
21 g_notional_pay NUMBER; -- used while calculating actual pay
22 g_effective_date DATE;-- for cutover run this will be the cutover date
23 g_extract_type VARCHAR2(100);
24 g_legislation_code per_business_groups.legislation_code%TYPE := 'GB';
25
26 g_pension_element_type_id NUMBER;
27
28 -- for 6071527
29 g_pension_scheme_name VARCHAR2(50);
30
31
32 -- for include_events
33 g_pay_proc_evt_tab ben_ext_person.t_detailed_output_table;
34
35 -- globals set by set_shared_globals
36 g_paypoint VARCHAR2(5) := NULL;
37 g_cutover_date DATE;
38 g_ext_dfn_id NUMBER;
39 g_is_terminated VARCHAR2(1) :='N';
40
41
42 g_curr_person_dtls per_all_people_f%ROWTYPE;
43 -- this contains the person details on effective date
44
45 g_curr_assg_dtls per_all_assignments_f%ROWTYPE;
46
47 -- uniformed grade override flag configuration values.
48 g_unigrade_source VARCHAR2(30);
49 g_assignment_context VARCHAR2(80);
50 g_assignment_column VARCHAR2(30);
51 g_people_group_column VARCHAR2(30);
52 ----------
53
54
55 TYPE t_varchar30 IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
56 g_dated_tables t_varchar30;
57
58 CURSOR get_wps_percent_cont_per
59 (p_element_entry_id IN NUMBER
60 ,p_effective_date IN DATE
61 ,p_input_value_name IN VARCHAR2
62 )
63 IS
64 SELECT peev.screen_entry_value
65 FROM pay_element_entry_values_f peev
66 ,pay_input_values_f piv
67 WHERE peev.element_entry_id = p_element_entry_id
68 AND piv.input_value_id = peev.input_value_id
69 AND piv.NAME = p_input_value_name
70 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date;
71
72
73 CURSOR get_wps_percent_cont_cut
74 (p_assignment_id IN NUMBER
75 ,p_effective_date IN DATE
76 ,p_element_type_id IN NUMBER
77 ,p_input_value_name IN VARCHAR2
78 )
79 IS
80 SELECT peev.screen_entry_value,peev.element_entry_id
81 FROM pay_element_entries_f pee
82 ,pay_element_entry_values_f peev
83 ,pay_input_values_f piv
84 WHERE pee.assignment_id=p_assignment_id
85 AND pee.element_type_id = p_element_type_id
86 AND peev.element_entry_id = pee.element_entry_id
87 AND piv.input_value_id = peev.input_value_id
88 AND piv.NAME = p_input_value_name
89 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
90 AND rownum = 1;
91
92 CURSOR csr_get_entry_type
93 (p_element_entry_id IN NUMBER
94 ,p_effective_date IN DATE
95 )
96 IS
97 SELECT entry_type
98 FROM pay_element_entries_f
99 where element_entry_id = p_element_entry_id
100 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
101
102 CURSOR csr_get_start_date_cut
103 (p_element_entry_id IN NUMBER
104 )
105 IS
106 SELECT effective_start_date FROM pay_element_entries_f
107 WHERE element_entry_id = p_element_entry_id
108 AND rownum = 1
109 ORDER BY effective_start_date;
110
111 CURSOR get_wps_element_name
112 (p_element_type_id IN NUMBER
113 )
114 IS
115 SELECT element_name FROM pay_element_types_f
116 WHERE element_type_id = p_element_type_id
117 AND rownum=1;
118
119 TYPE t_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
120
121 CURSOR csr_get_element_entry_id
122 (
123 p_element_entry_value_id IN NUMBER
124 )
125 IS
126 SELECT element_entry_id
127 FROM PAY_ELEMENT_ENTRY_VALUES_F
128 WHERE element_entry_value_id =p_element_entry_value_id
129 AND ROWNUM=1;
130
131 CURSOR csr_get_element_type_id
132 (
133 c_element_entry_id IN NUMBER
134 )
135 IS
136 SELECT element_type_id
137 FROM pay_element_entries_f
138 WHERE element_entry_id = c_element_entry_id
139 AND rownum=1;
140
141 -- for 6071527
142 /* to fetch the pension scheme name */
143 CURSOR get_wps_ele_scheme_name
144 (
145 p_element_type_id IN NUMBER
146 )
147 IS
148 SELECT eei_information1
149 FROM pay_element_type_extra_info
150 WHERE element_type_id = p_element_type_id
151 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO';
152
153 /* to check if the buy back element is valid */
154 CURSOR get_wps_byb_ele_scheme_name
155 (
156 p_element_type_id IN NUMBER
157 ,p_pension_scheme_name IN VARCHAR2
158 )
159 IS
160 SELECT eei_information1
161 FROM pay_element_type_extra_info
162 WHERE element_type_id = p_element_type_id
163 AND information_type = 'PQP_GB_PENSION_SCHEME_INFO'
164 AND eei_information1 = p_pension_scheme_name;
165
166 /* to fetch the basic element contribution percent */
167 CURSOR get_wps_percent_cont
168 (p_assignment_id IN NUMBER
169 ,p_effective_date IN DATE
170 ,p_element_type_id IN NUMBER
171 ,p_input_value_name IN VARCHAR2
172 )
173 IS
174 SELECT peev.screen_entry_value
175 FROM pay_element_entries_f pee
176 ,pay_element_entry_values_f peev
177 ,pay_input_values_f piv
178 WHERE pee.assignment_id=p_assignment_id
179 AND pee.element_type_id = p_element_type_id
180 AND peev.element_entry_id = pee.element_entry_id
181 AND piv.input_value_id = peev.input_value_id
182 AND piv.NAME = p_input_value_name
183 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
184 AND rownum = 1;
185
186 /* to fetch the buy back element contribution percent */
187 CURSOR get_wps_byb_percent_cont
188 (p_effective_date IN DATE
189 ,p_assignment_id IN NUMBER
190 ,p_input_value_name IN VARCHAR2
191 ,p_scheme_name IN VARCHAR2
192 )
193 IS
194 SELECT peev.screen_entry_value
195 FROM pay_element_types_f pet
196 ,pay_element_entries_f pee
197 ,pay_element_type_extra_info pete
198 ,pay_element_entry_values_f peev
199 ,pay_input_values_f piv
200 WHERE pee.assignment_id = p_assignment_id -- 301168
201 AND pee.element_type_id = pet.element_type_id -- 172156
202 AND pet.element_name like '%Buy Back FWC'
203 AND pet.element_type_id = pete.element_type_id
204 AND pete.eei_information1 = p_scheme_name --'Classic'
205 AND pee.element_entry_id = peev.element_entry_id
206 AND peev.input_value_id = piv.input_value_id
207 AND piv.NAME = p_input_value_name -- p_input_value_name
208 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
209 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
210
211 CURSOR get_wps_eff_end_date(p_element_type_id in number
212 ,p_assignment_id in number
213 ,p_effective_date in date
214 )
215 IS SELECT effective_end_date
216 FROM pay_element_entries_f
217 WHERE assignment_id = p_assignment_id
218 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
219 AND element_type_id = g_pension_element_type_id;
220
221 CURSOR get_assgn_eff_end_date(p_assignment_id in number
222 ,p_effective_date in date
223 )
224 IS SELECT effective_end_date
225 FROM per_all_assignments_f
226 WHERE assignment_id = p_assignment_id
227 AND effective_end_date = p_effective_date
228 AND assignment_status_type_id = 1;
229
230 -- for 6071527 end
231
232 -- Debug
233 PROCEDURE DEBUG (
234 p_trace_message IN VARCHAR2
235 ,p_trace_location IN NUMBER DEFAULT NULL
236 );
237
238 -- Debug_Enter
239 PROCEDURE debug_enter (
240 p_proc_name IN VARCHAR2
241 ,p_trace_on IN VARCHAR2 DEFAULT NULL
242 );
243
244 -- Debug_Exit
245 PROCEDURE debug_exit (
246 p_proc_name IN VARCHAR2
247 ,p_trace_off IN VARCHAR2 DEFAULT NULL
248 );
249
250 -- Debug Others
251 PROCEDURE debug_others (
252 p_proc_name IN VARCHAR2
253 ,p_proc_step IN NUMBER DEFAULT NULL
254 );
255 ---
256
257 -- ----------------------------------------------------------------------------
258 -- |------------------------< Function Definitions >---------------------------|
259 -- ----------------------------------------------------------------------------
260
261 --
262 -- WPS history main function
263 --
264 FUNCTION wps_history_main
265 (p_business_group_id IN NUMBER -- context
266 ,p_effective_date IN DATE -- context
267 ,p_assignment_id IN NUMBER -- context
268 ,p_rule_parameter IN VARCHAR2 -- parameter
269 ,p_output OUT NOCOPY VARCHAR2
270 )
271 RETURN number;
272
273 -- ----------------------------------------------------------------------------
274 -- |------------------------< chk_wps_cutover_crit >-------------------|
275 -- ----------------------------------------------------------------------------
276
277 FUNCTION chk_wps_cutover_crit
278 (p_business_group_id IN NUMBER
279 ,p_effective_date IN DATE
280 ,p_assignment_id IN NUMBER
281 )
282 RETURN VARCHAR2;
283
284
285
286 -- ----------------------------------------------------------------------------
287 -- |------------------------< chk_wps_periodic_crit >-------------------|
288 -- ----------------------------------------------------------------------------
289
290 FUNCTION chk_wps_periodic_crit
291 (p_business_group_id IN NUMBER
292 ,p_effective_date IN DATE
293 ,p_assignment_id IN NUMBER
294 )RETURN VARCHAR2;
295
296 -- ----------------------------------------------------------------------------
297 -- |----------------------< wps_post_processing >--------------------------|
298 -- Description: This is the post-processing rule for the Salary History.
299 -- ----------------------------------------------------------------------------
300 FUNCTION wps_post_processing RETURN VARCHAR2;
301
302 END PQP_GB_PSI_WPS_HISTORY;
[Home] [Help]