1 Package pqp_gb_tp_type2_functions as
2 -- /* $Header: pqpgbtp2.pkh 120.0 2005/05/29 02:02:21 appldev noship $ */
3 --
4 -- Debug Variables.
5 --
6 g_proc_name varchar2(61) := 'pqp_gb_tp_type2_functions.';
7 g_nested_level number := 0;
8 g_trace varchar2(1) := Null;
9
10 --
11 -- Global Variables
12 --
13 g_business_group_id number := Null;
14 g_legislation_code varchar2(10) := 'GB';
15 g_effective_date date;
16 g_extract_type varchar2(30);
17 g_effective_start_date date;
18 g_effective_end_date date;
19 g_header_system_element ben_ext_rslt_dtl.val_01%type;
20 g_add_cont_balance_name varchar2(200) := 'Total Additional Contributions';
21 g_total_add_cont number := 0;
22
23
24 Type t_number is table of number
25 index by binary_integer;
26
27 g_add_cont_value t_number;
28
29 --ENH3 And ENH4:
30 --
31 g_estb_number VARCHAR2(4):='0000';
32 g_crossbg_enabled VARCHAR2(1) := 'N';
33 g_lea_number VARCHAR2(3):=RPAD(' ',3,' ');
34 g_criteria_location_code VARCHAR2(20);
35 g_extract_udt_name VARCHAR2(80);
36 g_master_bg_id NUMBER:= NULL;
37 g_effective_run_date DATE;
38 g_cross_per_enabled VARCHAR2(1);
39
40 --
41
42 --
43 -- Cursor Definitions
44 --
45
46 -- Cursor to get balance type id for a balance
47
48 Cursor csr_get_pay_bal_id
49 (c_balance_name varchar2
50 ) is
51 select balance_type_id
52 from pay_balance_types
53 where balance_name = c_balance_name
54 and nvl(business_group_id, g_business_group_id) =
55 g_business_group_id
56 and legislation_code = 'GB';
57
58 g_add_cont_bal_id pay_balance_types.balance_type_id%type;
59
60 -- Cursor to get element type ids from balance
61 --ENH2:The business group check was removed and business group id was
62 --added in the select.This was done so that we pick up all the
63 --element feeds to the balance.
64 Cursor csr_get_pay_ele_ids_from_bal
65 (c_balance_type_id number
66 ,c_effective_start_date date
67 ,c_effective_end_date date
68 ) is
69 select pet.element_type_id element_type_id,pet.business_group_id
70 from pay_element_types_f pet
71 ,pay_input_values_f piv
72 ,pay_balance_feeds_f pbf
73 where pet.element_type_id = piv.element_type_id
74 -- and pet.business_group_id = g_business_group_id
75 and piv.input_value_id = pbf.input_value_id
76 and pbf.balance_type_id = c_balance_type_id
77 and (pbf.effective_start_date between c_effective_start_date
78 and c_effective_end_date
79 or
80 pbf.effective_end_date between c_effective_start_date
81 and c_effective_end_date
82 or
83 c_effective_start_date between pbf.effective_start_date
84 and pbf.effective_end_date
85 or
86 c_effective_end_date between pbf.effective_start_date
87 and pbf.effective_end_date
88 )
89 order by pet.business_group_id,pet.element_type_id;
90
91 type t_ele_ids_from_bal is table of csr_get_pay_ele_ids_from_bal%rowtype
92 index by binary_integer;
93
94 g_add_cont_ele_ids t_ele_ids_from_bal;
95
96 -- Cursor to get assignment attribute information for a given assignment
97
98 Cursor csr_get_aat_info
99 (c_assignment_id number
100 ,c_effective_start_date date
101 ,c_effective_end_date date
102 ) is
103 select assignment_attribute_id
104 ,assignment_id
105 ,greatest(effective_start_date,
106 c_effective_start_date) effective_start_date
107 ,least(effective_end_date,
108 c_effective_end_date) effective_end_date
109 ,tp_is_teacher
110 ,tp_elected_pension
111 from pqp_assignment_attributes_f
112 where assignment_id = c_assignment_id
113 and (effective_start_date between c_effective_start_date
114 and c_effective_end_date
115 or
116 effective_end_date between c_effective_start_date
117 and c_effective_end_date
118 or
119 c_effective_start_date between effective_start_date
120 and effective_end_date
121 or
122 c_effective_end_date between effective_start_date
123 and effective_end_date
124 )
125 order by effective_start_date;
126
127 Type t_aat_info is table of csr_get_aat_info%rowtype
128 index by binary_integer;
129
130 -- Cursor to get assignment information
131
132 Cursor csr_get_asg_info
133 (c_assignment_id number
134 ,c_effective_start_date date
135 ,c_effective_end_date date
136 ) is
137 select person_id
138 ,assignment_id
139 ,greatest(effective_start_date,
140 c_effective_start_date) effective_start_date
141 ,least(effective_end_date,
142 c_effective_end_date) effective_end_date
143 ,location_id
144 ,business_group_id --ENH8
145 ,NVL(employment_category,'FT') asg_emp_cat_cd --ENH3
146 from per_all_assignments_f
147 where assignment_id = c_assignment_id
148 and (effective_start_date between c_effective_start_date
149 and c_effective_end_date
150 or
151 effective_end_date between c_effective_start_date
152 and c_effective_end_date
153 or
154 c_effective_start_date between effective_start_date
155 and effective_end_date
156 or
157 c_effective_end_date between effective_start_date
158 and effective_end_date
159 )
160 order by effective_start_date;
161
162 Type t_asg_info is table of csr_get_asg_info%rowtype
163 index by binary_integer;
164
165 -- Cursor to get element entries information
166 --ENH8:The cursor has been changed to return only the valid element type id
167 -- in the element entries list.
168
169 Cursor csr_get_eet_info
170 (c_assignment_id number
171 ,c_effective_start_date date
172 ,c_effective_end_date date
173 ,c_element_type_id number
174 ) is
175 select pee.element_type_id
176 from pay_element_entries_f pee
177 -- ,pay_element_links_f pel
178 where pee.assignment_id = c_assignment_id
179 and pee.element_type_id = c_element_type_id --ENH8
180 and (pee.effective_start_date between c_effective_start_date
181 and c_effective_end_date
182 or
183 pee.effective_end_date between c_effective_start_date
184 and c_effective_end_date
185 or
186 c_effective_start_date between pee.effective_start_date
187 and pee.effective_end_date
188 or
189 c_effective_end_date between pee.effective_start_date
190 and pee.effective_end_date
191 )
192 -- and pel.element_link_id = pee.element_link_id
193 order by pee.effective_start_date;
194
195 -- Cursor to get multiple assignment info for a primary
196 -- assignment
197
198 Cursor csr_get_multiple_assignments
199 (c_assignment_id number
200 ,c_effective_start_date date
201 ,c_effective_end_date date
202 ) is
203 select distinct(pef2.assignment_id) assignment_id
204 from per_assignments_f pef
205 ,per_assignments_f pef2
206 where pef.assignment_id = c_assignment_id
207 and pef2.person_id = pef.person_id
208 and pef2.assignment_id <> pef.assignment_id
209 and (pef.effective_start_date between c_effective_start_date
210 and c_effective_end_date
211 or
212 pef.effective_end_date between c_effective_start_date
213 and c_effective_end_date
214 or
215 c_effective_start_date between pef.effective_start_date
216 and pef.effective_end_date
217 or
218 c_effective_end_date between pef.effective_start_date
219 and pef.effective_end_date
220 )
221 and (pef2.effective_start_date between c_effective_start_date
222 and c_effective_end_date
223 or
224 pef2.effective_end_date between c_effective_start_date
225 and c_effective_end_date
226 or
227 c_effective_start_date between pef2.effective_start_date
228 and pef2.effective_end_date
229 or
230 c_effective_end_date between pef2.effective_start_date
231 and pef2.effective_end_date
232 );
233
234 -- Cursor to retrieve end_dates from per_time_periods
235 Cursor csr_get_end_date
236 (c_assignment_id number
237 ,c_effective_start_date date
238 ,c_effective_end_date date) is
239 select distinct(ptp.end_date) end_date
240 from per_time_periods ptp
241 ,pay_payroll_actions ppa
242 ,pay_assignment_actions paa
243 where ptp.time_period_id = ppa.time_period_id
244 and ppa.payroll_action_id = paa.payroll_action_id
245 and ppa.effective_date between c_effective_start_date
246 and c_effective_end_date
247 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
248 and paa.assignment_id = c_assignment_id
249 order by ptp.end_date;
250
251 --
252
253 --This cursor is not being used.The one from type1 is being used.
254 --
255 -- Secondary Assignments which are Effective and future
256 --
257 /*CURSOR csr_sec_assignments
258 (p_primary_assignment_id NUMBER
259 ,p_person_id NUMBER
260 ,p_effective_date DATE
261 ) IS
262 SELECT DISTINCT asg.person_id person_id
263 ,asg.assignment_id assignment_id
264 ,asg.primary_flag primary_flag
265 ,asg.business_group_id business_group_id
266 ,DECODE(asg.business_group_id
267 ,g_business_group_id, 0
268 ,asg.business_group_id) bizgrpcol
269 FROM per_all_assignments_f asg
270 WHERE asg.person_id = p_person_id
271 AND asg.assignment_id <> p_primary_assignment_id
272 AND ((p_effective_date BETWEEN asg.effective_start_date
273 AND asg.effective_end_date
274 )
275 OR
276 ( -- Must have started on or after pension year start date
277 asg.effective_start_date >= p_effective_date
278 AND
279 -- must have started within the reporting period
280 asg.effective_start_date <= g_effective_run_date
281 )
282 )
283 UNION
284 SELECT DISTINCT per.person_id person_id
285 ,asg.assignment_id assignment_id
286 ,asg.primary_flag primary_flag
287 ,asg.business_group_id business_group_id
288 ,DECODE(asg.business_group_id
289 ,g_business_group_id, 0
290 ,asg.business_group_id) bizgrpcol
291 FROM per_all_people_f per, per_all_assignments_f asg
292 WHERE per.person_id <> p_person_id
293 AND p_effective_date BETWEEN per.effective_start_date
294 AND per.effective_end_date
295 AND g_cross_per_enabled = 'Y' -- Cross Person is enabled
296 AND (g_crossbg_enabled = 'Y' -- get CrossBG multiple per recs
297 OR
298 (g_crossbg_enabled = 'N' -- get multiple per recs only in this BG
299 AND
300 per.business_group_id = g_business_group_id
301 )
302 )
303 AND national_identifier =
304 (SELECT national_identifier
305 FROM per_all_people_f per2
306 WHERE person_id = p_person_id
307 AND p_effective_date BETWEEN per2.effective_start_date
308 AND per2.effective_end_date
309 )
310 AND asg.person_id = per.person_id
311 AND ((p_effective_date BETWEEN asg.effective_start_date
312 AND asg.effective_end_date
313 )
314 OR
315 ( -- Must have started on or after pension year start date
316 asg.effective_start_date >= p_effective_date
317 AND
318 -- must have started within the reporting period
319 asg.effective_start_date <= g_effective_run_date
320 )
321 )
322 ORDER BY bizgrpcol ASC, person_id, primary_flag DESC;
323
324 TYPE t_secondary_asgs_type IS TABLE OF csr_sec_assignments%ROWTYPE
325 INDEX BY BINARY_INTEGER;
326 */
327
328 --ENH3 AND ENH4
329 CURSOR csr_get_person_id
330 (
331 c_assignment_id IN NUMBER
332 )
333 IS
334 SELECT person_id,business_group_id
335 FROM per_all_assignments_f
336 WHERE assignment_id = c_assignment_id
337 AND ROWNUM < 2;
338
339 --ENH3 AND ENH4
340
341 CURSOR csr_get_asg_cat
342 (
343 c_assignment_id IN NUMBER
344 ,c_start_date IN DATE
345 )
346 IS
347 SELECT NVL(asg.employment_category,'FT') asg_emp_cat_cd
348 FROM per_all_assignments_f asg
349 WHERE asg.assignment_id = c_assignment_id
350 AND ( c_start_date BETWEEN asg.effective_start_date
351 AND asg.effective_end_date )
352 ORDER BY asg.effective_start_date DESC; -- effective row first
353
354 ---
355
356
357
358 --
359 -- Procedures and Functions
360 --
361
362 -- Get Pay Balance ID From Name
363
364 Function get_pay_bal_id
365 (p_balance_name in varchar2)
366 Return number;
367
368 -- Get Pay Element Ids From Balance
369
370 Procedure get_pay_ele_ids_from_bal
371 (p_balance_type_id in number
372 ,p_effective_start_date in date
373 ,p_effective_end_date in date
374 ,p_tab_ele_ids out nocopy t_ele_ids_from_bal
375 );
376
377 -- Get Element Entires Details
378
379 Procedure get_eet_info
380 (p_assignment_id in number
381 ,p_effective_start_date in date
382 ,p_effective_end_date in date
383 ,p_location_id in number
384 ,p_business_group_id in number --ENH8
385 ,p_return_status out nocopy boolean --ENH3 And ENH4
386 );
387
388 -- Get Assignment Details
389
390 FUNCTION get_asg_info
391 (p_assignment_id in number
392 ,p_effective_start_date in out nocopy date --ENH3 And ENH4
393 ,p_effective_end_date in date
394 ,p_location_id out nocopy number --ENH3 And ENH4
395 ,p_ext_emp_cat_cd out nocopy varchar2 --ENH3 And ENH4
396 ) RETURN BOOLEAN; --ENH3 And ENH4
397
398 -- Get Assignment Attributes Details
399
400 FUNCTION get_aat_info
401 (p_assignment_id in number
402 ,p_effective_start_date in date
403 ,p_effective_end_date in date
404 ,p_ext_emp_cat_cd in varchar2 --ENH3 And ENH4
405 ,p_location_id in number --ENH3 And ENH4
406 ) RETURN BOOLEAN; --ENH3 And ENH4
407
408
409
410 -- Criteria function
411
412 Function chk_teacher_qual_for_tp2
413 (p_business_group_id in number
414 ,p_effective_date in date
415 ,p_assignment_id in number
416 ,p_error_text out nocopy varchar2
417 ,p_error_number out nocopy number
418 )
419 Return varchar2;
420
421 -- Get Additional Contribution Value
422
423 Function get_add_cont_value
424 (p_assignment_id in number)
425 Return varchar2;
426
427 -- Get Additional Contribution Refund Indicator
428
429 Function get_add_cont_refund_ind
430 (p_assignment_id in number)
431 Return number;
432
433 -- Get Financial Year
434
435 Function get_financial_year
436 Return varchar2;
437
438 -- Get Total Additional Contribution Value
439
440 Function get_total_add_cont
441 Return varchar2;
442
443 -- Get Total Additional Contribution Refund Indicator
444
445 Function get_total_add_cont_sign
446 Return number;
447
448 --
449
450 -- Check LEA run
451
452 Function chk_lea_run
453 Return varchar2;
454
455
456
457
458 End pqp_gb_tp_type2_functions;
[Home] [Help]