1 PACKAGE BODY PQP_GB_LGPS_PENSIONPAY AS
2 /* $Header: pqgblgps.pkb 120.9.12010000.9 2008/09/26 06:42:07 pbalu ship $ */
3 --
4 g_package VARCHAR2(31) := 'PQP_GB_LGPS_PENSIONPAY.';
5
6 --6666135 Begin
7 Type Formula_Element is RECORD
8 (
9 Element_name varchar2(150),
10 Formula_name varchar2(150),
11 formula_id Number(9)
12 );
13 Type Formula_Element_Tab_typ is table of Formula_Element index by Binary_integer;
14 Formula_Element_Tab Formula_Element_Tab_typ;
15 Type Formula_Tab_typ is table of varchar2(150) index by Binary_integer;
16 Formula_Tab Formula_Tab_typ;
17 Formula_Tab_new Formula_Tab_typ;
18
19 j_formula_count number;
20 k_aasgn_form_count number;
21 l_warning_msg varchar2(200);
22 --6666135 End
23
24
25 Function RUN_USER_FORMULA(p_assignment_id NUMBER
26 ,p_effective_date DATE
27 ,p_business_group_id NUMBER
28 ,p_payroll_id NUMBER
29 ,Formula_Tab Formula_Tab_typ
30 ,p_assignment_number VARCHAR2)
31 return number is
32
33 --6666135 Begin
34 cursor c_pqp_formula_id(P_FORMULA_NAME VARCHAR2)
35 is
36 select ffff.formula_id
37 From FF_FORMULAS_F ffff
38 where UPPER(ffff.FORMULA_NAME) = UPPER(P_FORMULA_NAME)
39 and ffff.business_group_id = p_business_group_id
40 and p_effective_date between ffff.EFFECTIVE_START_DATE and ffff.EFFECTIVE_END_DATE;
41 --6666135 End
42
43 --This cursor will fetch the seeded element details.
44 cursor c_additional_pension(p_assignment_id NUMBER
45 ,p_effective_date date)
46 is
47 SELECT peevf.element_entry_id
48 ,peevf.input_value_id
49 FROM pay_element_types_f petf
50 ,pay_element_entries_f peef
51 ,pay_element_entry_values_f peevf
52 where petf.element_name = 'PQP LGPS Additional Pensionable Pay'
53 and petf.legislation_code = 'GB'
54 -- and petf.business_group_id = p_business_group_id 6652351
55 and petf.element_type_id = peef.element_type_id
56 and peef.element_entry_id = peevf.element_entry_id
57 and peef.assignment_id = p_assignment_id
58 --and p_effective_date between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE --6666135
59 and p_effective_date between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
60 and p_effective_date between peevf.EFFECTIVE_START_DATE and peevf.EFFECTIVE_END_DATE;
61
62 --6666135 Begin
63 cursor c_seeded_element_det
64 is
65 select pelf.element_link_id,plivf.input_value_id
66 from pay_element_links_f pelf
67 ,pay_link_input_values_f plivf
68 ,pay_element_types_f petf
69 where petf.element_name = 'PQP LGPS Additional Pensionable Pay'
70 and petf.legislation_code = 'GB'
71 and petf.element_type_id = pelf.element_type_id
72 and pelf.ELEMENT_LINK_ID = plivf.ELEMENT_LINK_ID
73 and pelf.business_group_id = p_business_group_id
74 and pelf.LINK_TO_ALL_PAYROLLS_FLAG = 'Y'
75 and p_effective_date between pelf.EFFECTIVE_START_DATE and pelf.EFFECTIVE_END_DATE;
76 --6666135 End
77
78 cursor c_action_ids(p_assignment_id NUMBER
79 ,p_effective_date date)
80 is
81 SELECT max(paa.assignment_action_id) ASSIGNMENT_ACTION_ID
82 ,max(ppa.payroll_action_id) PAYROLL_ACTION_ID
83 FROM pay_payroll_actions ppa,
84 pay_assignment_actions paa
85 WHERE ppa.action_type in ('Q','R')
86 AND paa.action_status = 'C'
87 AND ppa.business_group_id = p_business_group_id
88 AND paa.assignment_id = p_assignment_id
89 AND ppa.payroll_action_id = paa.payroll_action_id
90 AND effective_date <= p_effective_date;
91
92 c_additional_pension_row c_additional_pension%rowtype;
93 l_inputs ff_exec.inputs_t;
94 p_inputs ff_exec.inputs_t;
95 l_outputs ff_exec.outputs_t;
96 c_action_ids_row c_action_ids%rowtype;
97 n_sum_formula_val number :=0;
98 --6666135 Begin
99 c_seeded_element_det_row c_seeded_element_det%rowtype;
100 v_formula_id number(9);
101 Skip_formula Exception;
102 v_eff_start_date date;
103 v_eff_end_date date;
104 v_element_entry_id pay_element_entry_values_f.element_entry_value_id%type;
105 --6666135 End
106 BEGIN
107 -- setting the contexts
108 p_inputs(1).name := 'ASSIGNMENT_ID';
109 p_inputs(1).value := p_assignment_id;
110 p_inputs(2).name := 'DATE_EARNED';
111 p_inputs(2).value := fnd_date.date_to_canonical(p_effective_date);
112 p_inputs(3).name := 'BUSINESS_GROUP_ID';
113 p_inputs(3).value := p_business_group_id;
114 p_inputs(4).name := 'PAYROLL_ID';
115 p_inputs(4).value := p_payroll_id;
116 OPEN c_action_ids(p_assignment_id, p_effective_date);
117 FETCH c_action_ids into c_action_ids_row;
118 IF c_action_ids_row.ASSIGNMENT_ACTION_ID is not null
119 THEN
120 p_inputs(5).name := 'PAYROLL_ACTION_ID';
121 p_inputs(5).value := c_action_ids_row.PAYROLL_ACTION_ID;
122 p_inputs(6).name := 'ASSIGNMENT_ACTION_ID';
123 p_inputs(6).value := c_action_ids_row.ASSIGNMENT_ACTION_ID;
124 END IF;
125 CLOSE c_action_ids;
126 hr_utility.trace(' PAYROLL_ACTION_ID '|| c_action_ids_row.PAYROLL_ACTION_ID);
127 hr_utility.trace(' ASSIGNMENT_ACTION_ID '||c_action_ids_row.ASSIGNMENT_ACTION_ID);
128 hr_utility.trace(' p_assignment_id '||p_assignment_id);
129 hr_utility.trace('**Date earned '|| fnd_date.date_to_canonical(p_effective_date));
130 hr_utility.trace(' p_payroll_id '||p_payroll_id);
131
132 -- 6666135 Begin
133 FOR I in 1..Formula_Tab.COUNT
134 LOOP
135 v_formula_id := Null;
136 hr_utility.trace(' For Formula_Tab '||Formula_Tab(I));
137 OPEN c_pqp_formula_id(Formula_Tab(I));
138 Fetch c_pqp_formula_id into v_formula_id;
139 if c_pqp_formula_id%NOTFOUND then
140 hr_utility.set_location('Formula -'||Formula_Tab(I)||'- Not Present/effective in Table',11);
141 l_warning_msg := 'Formula -'||Formula_Tab(I)||'- Not Present or effective for Assignment '||p_assignment_number||' On '||p_effective_date;
142 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
143 fnd_file.put_line(FND_FILE.OUTPUT, l_warning_msg);
144 raise_application_error(-20001,' Invalid Formula ');
145 end if;
146 Close c_pqp_formula_id;
147 if v_formula_id is not null then
148 hr_utility.trace(' Inside RUN_USER_FORMULA '||Formula_Tab(I));
149 ff_exec.init_formula(v_formula_id, p_effective_date , l_inputs, l_outputs);
150 --6666135 End
151 --Assigning the contexts to the input variables
152 IF l_inputs.count > 0 and p_inputs.count > 0
153 THEN
154 FOR i IN l_inputs.first..l_inputs.last
155 LOOP
156 FOR j IN p_inputs.first..p_inputs.last
157 LOOP
158 IF l_inputs(i).name = p_inputs(j).name
159 THEN
160 l_inputs(i).value := p_inputs(j).value;
161 exit;
162 END IF;
163 END LOOP;
164 END LOOP;
165 END IF;
166 FOR i IN l_inputs.first..l_inputs.last
167 LOOP
168 hr_utility.trace(' i= '||i||' name '||l_inputs(i).name ||' value '||l_inputs(i).value);
169 END LOOP;
170 --executing the formula
171 ff_exec.run_formula(l_inputs,l_outputs);
172 hr_utility.trace(' calculated value from User formula '||l_outputs(1).value);
173 fnd_file.put_line(FND_FILE.OUTPUT,' ------ Formula '||Formula_Tab(I)||' value '||l_outputs(1).value);
174 n_sum_formula_val := n_sum_formula_val+ nvl(l_outputs(1).value,0);
175 End if; --formula id not null check 6666135
176 END LOOP;
177 hr_utility.trace(' Total value from all User formulas '||n_sum_formula_val);
178 IF n_sum_formula_val > 0
179 THEN --if the formula returns value greater than 0 6666135
180 OPEN c_additional_pension(p_assignment_id
181 ,p_effective_date);
182 FETCH c_additional_pension INTO c_additional_pension_row;
183 IF c_additional_pension%NOTFOUND then
184 hr_utility.trace(' Inserting seeded element with value'||n_sum_formula_val);
185 OPEN c_seeded_element_det;
186 FETCH c_seeded_element_det into c_seeded_element_det_row;
187 if c_seeded_element_det%NOTFOUND then
188 hr_utility.set_location(' Seeded Element is not linked ',11);
189 l_warning_msg := 'Seeded Element is not linked to the Payroll of Assignment '||p_assignment_number||' On '||p_effective_date;
190 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
191 Raise Skip_formula;
192 end if;
193 CLOSE c_seeded_element_det;
194 v_eff_start_date := p_effective_date;
195
196 hr_entry_api.insert_element_entry(
197 p_effective_start_date => v_eff_start_date,
198 p_effective_end_date => v_eff_end_date,
199 p_element_entry_id => v_element_entry_id,
200 p_assignment_id => p_assignment_id,
201 p_element_link_id => c_seeded_element_det_row.element_link_id,
202 p_creator_type => 'F',
203 p_entry_type => 'E',
204 p_date_earned => v_eff_start_date,
205 p_input_value_id1 => c_seeded_element_det_row.input_value_id,
206 p_entry_value1 => to_char(n_sum_formula_val)
207 );
208 ELSE
209 hr_utility.trace(' calling for correction of seeded element '||n_sum_formula_val);
210 --6666135 End
211 hr_entry_api.update_element_entry (p_dt_update_mode =>'CORRECTION',
212 p_session_date => p_effective_date,
213 p_check_for_update =>'N',
214 p_creator_type => 'F',
215 p_element_entry_id => c_additional_pension_row.element_entry_id,
216 p_input_value_id1 => c_additional_pension_row.input_value_id,
217 p_entry_value1 => n_sum_formula_val --n_pen_value 6666135
218 );
219
220 END IF; --Seeded element present or not
221 CLOSE c_additional_pension;
222 END IF; -- formula returned value greater than zero
223 hr_utility.trace(' formula completed');
224 return n_sum_formula_val;
225 Exception
226 When Skip_formula then
227 --seeded element is not linked to Payroll so skipping
228 Null;
229 When others then
230 hr_utility.trace(sqlerrm);
231 Raise;
232 --6666135 End
233 END RUN_USER_FORMULA;
234
235 PROCEDURE DERIVE_PENSIONABLE_PAY(errbuf out nocopy varchar2,
236 retcode out nocopy number,
237 p_effective_start_dt IN varchar2,
238 p_effective_end_dt IN varchar2,
239 p_payroll_id IN NUMBER,
240 p_assignment_set_id IN NUMBER,
241 p_assignment_number IN varchar2,
242 p_employee_no IN varchar2,
243 p_business_group_id IN NUMBER,
244 p_mode in varchar2 )
245 IS
246 --
247 v_eff_end_date date;
248 v_given_end_date date;
249 v_eff_end_date_corr date;
250 v_eff_start_date_corr date;
251 v_eff_start_date date;
252 v_max_date date;
253 v_assignment_eff_date date;
254 max_future_date date;
255 l_mode varchar2(20);
256 l_proc VARCHAR2(61) := g_package || 'DERIVE_PENSIONABLE_PAY';
257 n_object_version_no number;
258 l_eff_start_date_op date;
259 l_eff_end_date_op date;
260 n_prev_assignment_id per_all_assignments_f.assignment_id%type;
261 l_ELEMENT_NAME pay_element_types_f.ELEMENT_NAME%TYPE;
262
263 -- This cursor will fetch assignment ids
264 -- based on the payroll id or assignment id or employee number or assignment set id whichever entered by the user
265
266 CURSOR c_all_valid_assignment
267 IS
268 SELECT paaf.assignment_id, paaf.assignment_number,
269 paaf.payroll_id, MIN(paaf.EFFECTIVE_START_DATE) EFFECTIVE_START_DATE
270 FROM per_all_assignments_f paaf,
271 per_all_people_f papf,
272 per_assignment_status_types past
273 WHERE paaf.business_group_id = p_business_group_id
274 AND paaf.payroll_id= nvl(p_payroll_id, paaf.payroll_id)
275 -- AND paaf.assignment_id = nvl(p_assignment_id, paaf.assignment_id)
276 AND paaf.assignment_number = nvl(p_assignment_number, paaf.assignment_number)
277 AND paaf.EFFECTIVE_START_DATE <= v_given_end_date
278 AND paaf.EFFECTIVE_END_DATE > v_eff_start_date
279 AND paaf.person_id = papf.person_id
280 AND papf.employee_number = nvl(p_employee_no, papf.employee_number)
281 AND past.ASSIGNMENT_STATUS_TYPE_ID = paaf.ASSIGNMENT_STATUS_TYPE_ID
282 AND past.PER_SYSTEM_STATUS in ('ACTIVE_ASSIGN','SUSP_ASSIGN', 'TERM_ASSIGN')-- 'TERM_ASSIGN' added for bug 6868115
283 --6813970 begin
284 /* Moving Assignment set check from here, so that both types of Assignment set can be processed.
285 AND (p_assignment_set_id IS NULL -- don't check for assignment set in this case
286 OR EXISTS (SELECT 1 FROM hr_assignment_sets has1
287 WHERE has1.assignment_set_id = p_assignment_set_id
288 AND has1.business_group_id = paaf.business_group_id
289 AND nvl(has1.payroll_id, paaf.payroll_id) = paaf.payroll_id
290 AND (NOT EXISTS (SELECT 1 -- chk no amendmts
291 FROM hr_assignment_set_amendments hasa1
292 WHERE hasa1.assignment_set_id =
293 has1.assignment_set_id)
294 OR EXISTS (SELECT 1 -- chk include amendmts
295 FROM hr_assignment_set_amendments hasa2
296 WHERE hasa2.assignment_set_id =
297 has1.assignment_set_id
298 AND hasa2.assignment_id = paaf.assignment_id
299 AND nvl(hasa2.include_or_exclude,'I') = 'I')
300 OR (NOT EXISTS (SELECT 1 --chk no exlude amendmts
301 FROM hr_assignment_set_amendments hasa3
302 WHERE hasa3.assignment_set_id =
303 has1.assignment_set_id
304 AND hasa3.assignment_id = paaf.assignment_id
305 AND nvl(hasa3.include_or_exclude,'I') = 'E')
306 AND NOT EXISTS (SELECT 1 --and chk no Inc amendmts
307 FROM hr_assignment_set_amendments hasa4
308 WHERE hasa4.assignment_set_id =
309 has1.assignment_set_id
310 AND nvl(hasa4.include_or_exclude,'I') = 'I') ) -- end checking exclude amendmts
311 ) -- done checking amendments
312 ) -- done asg set check when not null
313 ) -- end of asg set check
314 6813970 end */
315 GROUP BY assignment_id, assignment_number, payroll_id
316 ORDER BY assignment_id, EFFECTIVE_START_DATE;
317
318 --6666135 Begin
319 --This cursor will fetch the formual and element details from the Configuration value
320 cursor c_pqp_formula_element_det
321 is
322 select distinct PCV_INFORMATION1
323 From pqp_configuration_values
324 where pcv_information_category = 'PQP_GB_LGPS_FF_INFO'
325 AND business_group_id=p_business_group_id; --New business group specific Condition is added in this cursor for bug 6856733
326
327 --6666135 End
328
329 --This cursor will check for the presence of assignments in pqp_assignment_attributes_f table
330 CURSOR c_pqp_assignment(n_assignment_id NUMBER)
331 IS
332 SELECT pqpaaf.lgps_process_flag,
333 pqpaaf.assignment_attribute_id,
334 pqpaaf.EFFECTIVE_START_DATE,
335 pqpaaf.object_version_number
336 FROM pqp_assignment_attributes_f pqpaaf
337 WHERE pqpaaf.assignment_id = n_assignment_id
338 AND pqpaaf.business_group_id = p_business_group_id
339 AND nvl(pqpaaf.lgps_process_flag,'Nul') = nvl(l_mode,nvl(pqpaaf.lgps_process_flag,'Nul')) --l_mode will have value only in case of Incomplete and reprocess
340 AND ( v_assignment_eff_date between pqpaaf.EFFECTIVE_START_DATE and pqpaaf.EFFECTIVE_END_DATE
341 OR pqpaaf.EFFECTIVE_START_DATE = (select min(EFFECTIVE_START_DATE) from pqp_assignment_attributes_f where assignment_id = n_assignment_id
342 AND lgps_process_flag = nvl(l_mode,lgps_process_flag) AND business_group_id = p_business_group_id
343 AND EFFECTIVE_START_DATE BETWEEN v_assignment_eff_date AND v_eff_end_date));
344
345 --This cursor will fetch the all elements for the assigment
346 CURSOR c1_all_element(n_assignment_id NUMBER)
347 IS
348 SELECT peef.ELEMENT_TYPE_ID,petf.ELEMENT_NAME
349 FROM pay_element_entries_f peef, pay_element_types_f petf
350 WHERE peef.ASSIGNMENT_ID=n_assignment_id
351 AND peef.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
352 AND petf.business_group_id = p_business_group_id
353 AND v_assignment_eff_date between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
354 AND v_assignment_eff_date between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE;
355 --6666135 End
356
357 --For the element to get processed by pqp_rates_history_calc.get_historic_rate two rows should be present
358 --One row with data PQP_UK_RATE_TYPE and PQP_LGPS_PENSION_PAY
359 --and second row with PQP_UK_ELEMENT_ATTRIBUTION and Pay Value.
360 -- --7369484 Begin Cursor including Scottish LGPS Rate Type
361 CURSOR c2_PQP_UK_RATE_TYPE(n_element_type_id NUMBER)
362 IS
363 SELECT pet.EEI_INFORMATION1
364 FROM pay_element_type_extra_info pet
365 WHERE pet.element_type_id = n_element_type_id
366 AND pet.INFORMATION_TYPE = 'PQP_UK_RATE_TYPE'
367 -- AND pet.EEI_INFORMATION1 = 'PQP_LGPS_PENSION_PAY';
368 AND pet.EEI_INFORMATION1 in ('PQP_LGPS_SCOTLAND_PENSION_PAY','PQP_LGPS_PENSION_PAY');
369 -- --7369484 End
370
371 CURSOR c3_pqp_lgps_pension_pay(n_element_type_id NUMBER)
372 IS
373 SELECT 1
374 FROM pay_element_type_extra_info pet
375 WHERE pet.element_type_id = n_element_type_id
376 AND pet.INFORMATION_TYPE = 'PQP_UK_ELEMENT_ATTRIBUTION';
377
378
379 --This cursor to check the future date track records in pqp_assignment_attributes_f
380 CURSOR c_future_date(n_assignment_id NUMBER)
381 IS
382 SELECT max(EFFECTIVE_START_DATE)
383 FROM pqp_assignment_attributes_f pqpaaf
384 WHERE pqpaaf.assignment_id = n_assignment_id
385 AND pqpaaf.business_group_id = p_business_group_id;
386 --
387 --This cursor to correct the entries till the end date
388 CURSOR c_correct_pqp(n_assignment_id NUMBER)
389 IS
390 SELECT ASSIGNMENT_ATTRIBUTE_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, object_version_number
391 FROM pqp_assignment_attributes_f pqpaaf
392 WHERE pqpaaf.assignment_id = n_assignment_id
393 and pqpaaf.business_group_id = p_business_group_id
394 and pqpaaf.EFFECTIVE_START_DATE > v_max_date
395 -- and pqpaaf.EFFECTIVE_START_DATE between v_eff_start_date and v_eff_end_date_corr;
396 and pqpaaf.EFFECTIVE_START_DATE between v_assignment_eff_date and v_eff_end_date_corr;
397
398 --
399
400 c1_all_element_data c1_all_element%rowtype;
401 c2_PQP_UK_RATE_TYPE_data c2_PQP_UK_RATE_TYPE%rowtype;
402 c3_pqp_lgps_pension_pay_data c3_pqp_lgps_pension_pay%rowtype;
403 --c_formula_pension_value pay_element_entry_values_f.SCREEN_ENTRY_VALUE%type;
404 c_formula_pension_value number;
405 v_mode varchar2(30);
406 N_start_year NUMBER;
407 n_present number;
408 b_rate_type boolean;
409 b_element_present boolean;
410 b_input_value_present boolean;
411 l_lgps_process_flag pqp_assignment_attributes_f.lgps_process_flag%type;
412 --l_lgps_pensionable_pay pqp_assignment_attributes_f.lgps_pensionable_pay%type;
413 l_lgps_pensionable_pay number;
414 b_pqp_assignment_found boolean;
415 b_pqp_found boolean;
416 c_pqp_assignment_row c_pqp_assignment%rowtype;
417 skip_assignment Exception;
418 skip_element Exception;
419 l_assignment_attribute_id number;
420 --7369484 Begin
421 b_scot_rate boolean;
422 b_eng_rate boolean;
423 --7369484 End
424 --6813970 begin
425
426 l_formula_id NUMBER;
427 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
428 l_include_flag VARCHAR2(10);
429
430 --6813970 end
431
432 -- main
433 BEGIN
434 -- hr_utility.trace_on(null,'gag');
435 hr_utility.set_location('Entering: ' || l_proc, 10);
436 BEGIN
437 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
438 values(userenv('sessionid'), trunc(SYSDATE));
439 EXCEPTION
440 WHEN others THEN
441 hr_utility.trace('SESSION ALREADY EXISTS :'|| sqlerrm);
442 Raise;
443 END;
444 v_eff_start_date := fnd_date.canonical_to_date(p_effective_start_dt);
445 --calculation of pension end date for the year
446 N_start_year := TO_NUMBER(TO_CHAR(v_eff_start_date,'YYYY'));
447 IF trunc(v_eff_start_date) > TO_DATE('31-03-'||N_start_year,'DD-MM-YYYY')
448 THEN
449 N_start_year := N_start_year+1;
450 END IF;
451 v_eff_end_date := TO_DATE('31-03-'||N_start_year,'DD-MM-YYYY');
452 v_given_end_date := v_eff_end_date;
453 IF p_effective_end_dt IS not NULL --to date entered by the user.
454 THEN
455 v_given_end_date := fnd_date.canonical_to_date(p_effective_end_dt);
456 --To check the given dates falls in the same pension year
457 IF ((v_eff_start_date BETWEEN to_date('01/04/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
458 AND to_date('31/12/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy'))
459 AND (v_eff_end_date BETWEEN to_date('01/04/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
460 AND to_date('31/03/'||to_char(to_number(to_char(v_eff_start_date,'YYYY'))+1),'dd/mm/yyyy')))
461 OR ((v_eff_start_date BETWEEN to_date('01/01/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
462 AND to_date('31/03/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy'))
463 AND (v_eff_end_date BETWEEN to_date('01/01/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
464 AND to_date('31/03/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')))
465 THEN
466 hr_utility.set_location('Start date and End date are in the same pension year ',6);
467 ELSE
468 fnd_file.put_line (fnd_file.LOG, 'Start date and End date should fall in the same pension year.');
469 hr_utility.set_location('Start date and End date should fall in the same tax year',8);
470 v_eff_start_date := Null; -- to exit the program
471 END IF;
472 END IF;
473 --6813970 begin
474 --Check for Assignment set
475 If p_assignment_set_id is not null then
476 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => p_assignment_set_id
477 ,p_formula_id => l_formula_id
478 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
479 );
480 If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
481 fnd_file.put_line (fnd_file.LOG, ' Assignment set id entered '||p_assignment_set_id||' is not valid');
482 v_eff_start_date := Null; -- to exit the program
483 end if;
484 End if;
485 --6813970 end
486 --6666135 Begin
487 --Fetching formula details
488 j_formula_count := 0;
489 For I in c_pqp_formula_element_det
490 loop
491 j_formula_count := j_formula_count+1;
492 Formula_Element_Tab(j_formula_count).Element_name := substr(I.PCV_INFORMATION1,1,instr(I.PCV_INFORMATION1,'|')-1);
493 Formula_Element_Tab(j_formula_count).Formula_name := substr(I.PCV_INFORMATION1,instr(I.PCV_INFORMATION1,'|')+1);
494 hr_utility.trace(' Element_name '||j_formula_count|| Formula_Element_Tab(j_formula_count).Element_name);
495 hr_utility.trace(' Formula_name '||j_formula_count|| Formula_Element_Tab(j_formula_count).Formula_name);
496 end loop;
497 --6666135 End
498
499 v_eff_end_date_corr := v_eff_end_date;
500 hr_utility.set_location('Effective Start Date: ' || p_effective_start_dt,1);
501 hr_utility.set_location('Effective End Date: ' || p_effective_end_dt,2);
502 hr_utility.set_location('Calculated End Date: ' || v_eff_end_date_corr, 3);
503 hr_utility.set_location('Calculated Start Date: ' || v_eff_start_date, 3);
504 hr_utility.set_location('p_assignment_set_id: ' || p_assignment_set_id, 3);
505 hr_utility.set_location('p_assignment_number: ' || p_assignment_number, 3);
506 hr_utility.set_location('p_employee_no: ' || p_employee_no, 3);
507 hr_utility.set_location('p_payroll_id: ' || p_payroll_id, 3);
508 hr_utility.set_location('p_business_group_id: ' || p_business_group_id, 3);
509 hr_utility.set_location('p_mode: ' || p_mode, 3);
510 IF p_mode = 'Reprocess'
511 THEN
512 l_mode := 'Y';
513 ELSIF p_mode = 'Incomplete'
514 THEN
515 l_mode := 'I';
516 END IF;
517 --
518 hr_utility.set_location('l_mode: ' || l_mode, 3);
519 FOR c_all_assignments in c_all_valid_assignment
520 LOOP
521 Begin
522 hr_utility.set_location('Inside valid Assignments'||c_all_assignments.assignment_id,20);
523
524 if nvl(n_prev_assignment_id,'0.000') = c_all_assignments.assignment_id then
525 raise skip_assignment;
526 end if;
527
528
529
530 n_prev_assignment_id := c_all_assignments.assignment_id;
531 l_lgps_pensionable_pay := NULL;
532 c_formula_pension_value := NULL;
533 b_element_present := FALSE;
534 b_rate_type := FALSE;
535 --7369484 Begin
536 b_scot_rate := FALSE;
537 b_eng_rate := FALSE;
538 --7369484 End
539 b_input_value_present := FALSE;
540 b_pqp_found := NULL;
541 b_pqp_assignment_found := NULL;
542 c_pqp_assignment_row := NULL;
543 v_assignment_eff_date := NULL;
544 max_future_date := NULL;
545 Formula_Tab := Formula_Tab_new; --6666135
546 k_aasgn_form_count := 0; --6666135
547
548 v_assignment_eff_date := Greatest(c_all_assignments.EFFECTIVE_START_DATE,v_eff_start_date);
549
550 --6813970 begin
551 l_include_flag := 'N';
552 If p_assignment_set_id is not null then
553 l_include_flag := pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => c_all_assignments.assignment_id
554 ,p_formula_id => l_formula_id
555 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
556 ,p_effective_date => v_assignment_eff_date
557 );
558 if l_include_flag = 'N' then
559 raise skip_assignment;
560 end if;
561
562 end if;
563 --6813970 end
564
565 OPEN c_future_date(c_all_assignments.assignment_id);
566 FETCH c_future_date into max_future_date;
567 IF max_future_date is not NULL THEN
568 b_pqp_found := TRUE;
569 END IF;
570 CLOSE c_future_date;
571
572 IF b_pqp_found THEN
573 OPEN c_pqp_assignment(c_all_assignments.assignment_id);
574 FETCH c_pqp_assignment INTO c_pqp_assignment_row;
575 IF c_pqp_assignment%found
576 then
577 b_pqp_assignment_found := TRUE;
578 END IF;
579 CLOSE c_pqp_assignment;
580 IF b_pqp_assignment_found THEN
581 IF ((p_mode = 'Start of the Year' and nvl(c_pqp_assignment_row.lgps_process_flag,'Y') in ('Y','P','I','U'))
582 or l_mode in ('Y','I')
583 or nvl(c_pqp_assignment_row.lgps_process_flag,'New Hires') = p_mode) THEN
584 v_max_date := Greatest(c_pqp_assignment_row.EFFECTIVE_START_DATE, v_eff_start_date);
585 n_object_version_no := c_pqp_assignment_row.object_version_number;
586 ElSE
587 Raise skip_assignment;
588 END IF;
589
590 v_assignment_eff_date := Greatest(c_pqp_assignment_row.EFFECTIVE_START_DATE, v_assignment_eff_date);
591 END IF;
592 END IF;
593
594 IF b_pqp_assignment_found is NULL AND l_mode is not null THEN
595 --No records in PQP_ASSIGNMENT_ATTRIBUTES_F table matching mode - reprocess or incomplete.
596 raise skip_assignment;
597 END IF;
598
599 OPEN c1_all_element(c_all_assignments.assignment_id);
600 LOOP
601 BEGIN
602 hr_utility.set_location('Inside valid Elements for the Assignment'||c_all_assignments.assignment_id,20);
603 FETCH c1_all_element into c1_all_element_data;
604 IF c1_all_element%NOTFOUND
605 THEN
606 CLOSE c1_all_element;
607 EXIT;
608 END IF;
609 hr_utility.set_location('Checking for Historic rate type',25);
610 b_element_present := TRUE;
611
612 l_ELEMENT_NAME := null;
613
614 --6666135 Begin
615 FOR F in 1..j_formula_count
616 loop
617 if Formula_Element_Tab(F).Element_name = c1_all_element_data.Element_name then
618 k_aasgn_form_count := k_aasgn_form_count+1;
619 Formula_Tab(k_aasgn_form_count) := Formula_Element_Tab(F).Formula_name;
620 l_ELEMENT_NAME := c1_all_element_data.Element_name;
621 End if;
622 End loop;
623 --6666135 End
624
625 --To Check the values PQP_UK_RATE_TYPE, PQP_LGPS_PENSION_PAY, INPUT_VALUE AND PAY VALUE in pay_element_type_extra_info
626 OPEN c2_PQP_UK_RATE_TYPE(c1_all_element_data.element_type_id);
627 FETCH c2_PQP_UK_RATE_TYPE INTO c2_PQP_UK_RATE_TYPE_data;
628 IF c2_PQP_UK_RATE_TYPE%NOTFOUND
629 THEN
630 CLOSE c2_PQP_UK_RATE_TYPE;
631 RAISE skip_element;
632 END IF;
633 -- 7369484 Begin
634 IF c2_PQP_UK_RATE_TYPE_data.EEI_INFORMATION1 = 'PQP_LGPS_SCOTLAND_PENSION_PAY'
635 THEN
636 b_scot_rate := TRUE;
637 IF b_eng_rate = TRUE
638 THEN
639 fnd_file.put_line (fnd_file.LOG,'Assignment can not have both Types of RATE TYPE attached. Assignment Number : '||c_all_assignments.assignment_number);
640 CLOSE c1_all_element;
641 CLOSE c2_PQP_UK_RATE_TYPE;
642 RAISE skip_assignment;
643 END IF;
644 ELSIF c2_PQP_UK_RATE_TYPE_data.EEI_INFORMATION1 = 'PQP_LGPS_PENSION_PAY'
645 THEN
646 --7369484 End
647 hr_utility.set_location('Historic Rate Type present for Element '||c1_all_element_data.element_type_id,26);
648 -- 7369484 Begin
649 b_rate_type := TRUE;
650 b_eng_rate := TRUE;
651 IF b_scot_rate = TRUE
652 THEN
653 fnd_file.put_line (fnd_file.LOG,'Assignment can not have both Types of RATE TYPE attached. Assignment Number : '||c_all_assignments.assignment_number);
654 CLOSE c1_all_element;
655 CLOSE c2_PQP_UK_RATE_TYPE;
656 RAISE skip_assignment;
657 END IF;
658 END IF; -- 7369484 end if rate type id = Scottish rate type
659 OPEN c3_pqp_lgps_pension_pay(c1_all_element_data.element_type_id);
660 FETCH c3_pqp_lgps_pension_pay INTO c3_pqp_lgps_pension_pay_data;
661 IF c3_pqp_lgps_pension_pay%NOTFOUND
662 THEN
663 hr_utility.set_location('Pay Value Not Present',27);
664 IF l_ELEMENT_NAME IS NULL THEN
665 l_warning_msg := 'PQP_LGPS_MISSING_HISTORIC_RATE_INFO: Historic Rate - Element Attribution information missing for Element '||c1_all_element_data.Element_name||' in Assignment '||c_all_assignments.assignment_number;
666 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
667 END IF;
668 ELSE
669 b_input_value_present := TRUE;
670 hr_utility.set_location('Pay Value Present',28);
671 END IF;
672 CLOSE c2_PQP_UK_RATE_TYPE;
673 CLOSE c3_pqp_lgps_pension_pay;
674 EXCEPTION
675 WHEN skip_element THEN
676 NULL;
677 END;
678 END LOOP; --loop for all elements in a assignment
679
680 IF b_scot_rate = TRUE
681 THEN
682 raise skip_assignment;
683 END IF;
684
685 IF b_pqp_found THEN
686 IF b_pqp_assignment_found THEN
687 IF c_pqp_assignment_row.EFFECTIVE_START_DATE = v_assignment_eff_date
688 THEN
689 v_mode := 'CORRECTION';
690 ELSIF v_max_date < max_future_date
691 THEN
692 v_mode := 'UPDATE_CHANGE_INSERT';
693 ELSE
694 v_mode := 'UPDATE';
695 END IF;
696 hr_utility.set_location('Mode for update v_mode: ' || v_mode, 3);
697 --6813970 begin
698 -- IF (not b_element_present) or (not b_rate_type)
699 -- THEN
700 -- l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Historic Rate - Rate Type "LGPS Pensionable Pay" not set for elements against Assignment '||c_all_assignments.assignment_number;
701 IF ((not b_element_present) or (not b_rate_type)) and k_aasgn_form_count = 0
702 THEN
703 l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Neither Historic Rate - Rate Type "LGPS Pensionable Pay" nor User defined formula set for elements against Assignment '||c_all_assignments.assignment_number;
704 --6813970 end
705 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
706 l_lgps_process_flag := 'I';
707 hr_utility.set_location('Calling API to update LGPS Process Flag I',35);
708 pqp_aat_api.update_assignment_attribute
709 (p_validate => false
710 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
711 ,p_datetrack_mode => v_mode
712 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
713 ,p_business_group_id => p_business_group_id
714 ,p_effective_start_date => v_max_date
715 ,p_effective_end_date => v_eff_end_date
716 ,p_assignment_id => c_all_assignments.assignment_id
717 ,p_object_version_number => n_object_version_no
718 ,p_lgps_process_flag => l_lgps_process_flag
719 );
720 ---******************
721 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
722 LOOP
723 n_object_version_no := K.object_version_number;
724 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
725 pqp_aat_api.update_assignment_attribute
726 (p_validate => false
727 ,p_effective_date => v_eff_start_date_corr
728 ,p_datetrack_mode => 'CORRECTION'
729 ,p_assignment_attribute_id => K.assignment_attribute_id
730 ,p_business_group_id => p_business_group_id
731 ,p_effective_start_date => K.EFFECTIVE_START_DATE
732 ,p_effective_end_date => K.EFFECTIVE_END_DATE
733 ,p_assignment_id => c_all_assignments.assignment_id
734 ,p_object_version_number => n_object_version_no
735 ,p_lgps_process_flag => l_lgps_process_flag
736 );
737 END LOOP;
738 raise skip_assignment;
739 END IF;
740 ----------
741 ELSE
742 IF l_mode is null then
743 l_warning_msg := 'Cannot process Assignment : '||c_all_assignments.assignment_number||' Future changes present in table pqp_assignment_attributes_f ';
744 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
745 END IF;
746 raise skip_assignment;
747 END IF; --b_pqp_assignment_found
748
749 ELSIF p_mode = 'New Hires' then --insert
750 --NO CURRENT RECORD IN pqp SO INSERT A RECORD WITH CP START DATE
751 --6813970 begin
752 -- IF (not b_element_present) or (not b_rate_type)
753 -- THEN
754 -- l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Historic Rate - Rate Type "LGPS Pensionable Pay" not set for elements against Assignment '||c_all_assignments.assignment_number;
755
756 IF ((not b_element_present) or (not b_rate_type)) and k_aasgn_form_count = 0
757 THEN
758 l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Neither Historic Rate - Rate Type "LGPS Pensionable Pay" nor User defined formula set for elements against Assignment '||c_all_assignments.assignment_number;
759 --6813970 end
760 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
761 l_lgps_process_flag := 'I';
762 hr_utility.set_location('Calling API to insert LGPS Process Flag I',355);
763 pqp_aat_api.create_assignment_attribute
764 (p_effective_date => v_assignment_eff_date
765 ,p_business_group_id => p_business_group_id
766 ,p_assignment_id => c_all_assignments.assignment_id
767 ,p_assignment_attribute_id => l_assignment_attribute_id
768 ,p_object_version_number => n_object_version_no
769 ,p_effective_start_date => l_eff_start_date_op
770 ,p_effective_end_date => l_eff_end_date_op
771 ,p_lgps_process_flag => l_lgps_process_flag
772 );
773 raise skip_assignment;
774 END IF;
775
776 END IF; -- b_pqp_found
777
778 --6813970 begin
779 -- IF (b_element_present and b_input_value_present)
780 -- THEN
781 IF (b_rate_type and b_input_value_present)
782 THEN
783 --6813970 end
784 Begin
785 l_lgps_pensionable_pay:=pqp_rates_history_calc.get_historic_rate(p_assignment_id => c_all_assignments.assignment_id
786 ,p_rate_name => 'LGPS Pensionable Pay'
787 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
788 ,p_time_dimension => 'A'
789 ,p_rate_type_or_element => 'R');
790 Exception
791 when others then
792 l_warning_msg := 'Cannot process Assignment : '||c_all_assignments.assignment_number||' Historic Rate calculations failed. Please check the error message ';
793 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
794 fnd_file.put_line (fnd_file.LOG,'*** -ERROR- '||SQLCODE||' - '||SQLERRM);
795 b_rate_type := FALSE; --to skip processing of user defined formulae
796 k_aasgn_form_count := 0; --to skip processing of user defined formulae --6813970
797 b_input_value_present := FALSE; --to make the LGPS process flag as Incomplete.
798 End;
799 END IF;
800 hr_utility.set_location('Calculated Pensionable Pay'||l_lgps_pensionable_pay,40);
801
802 ---- To calculate the value from user defined formula and add it to the value from historic rates function
803 --6813970 begin
804 -- IF b_rate_type
805 -- THEN
806 IF b_rate_type or k_aasgn_form_count > 0
807 THEN -- Rate type defined for atleast one element
808 --6813970 end
809 IF b_input_value_present or k_aasgn_form_count > 0 then -- if b_input_value_present is true then Historic rates funciton will return value; if k_aasgn_form_count > 0 means user defined formula is attached
810 fnd_file.put_line(FND_FILE.OUTPUT, ' Assignment '||c_all_assignments.assignment_number ||' Historic rate value '||nvl(l_lgps_pensionable_pay,0));
811 END IF;
812 IF k_aasgn_form_count > 0 then
813
814 hr_utility.set_location(' Calling RUN_USER_FORMULA ',25);
815 -- Call the formula to calculate the additional pension pay
816 c_formula_pension_value := RUN_USER_FORMULA(c_all_assignments.assignment_id , v_assignment_eff_date , p_business_group_id , c_all_assignments.payroll_id, Formula_Tab , c_all_assignments.assignment_number);
817 hr_utility.set_location(' formula returned value '||c_formula_pension_value,40);
818
819 IF nvl(c_formula_pension_value,0) >= 0 -- equal to condition is added for bug 6857280
820 THEN
821 l_lgps_pensionable_pay := NVL(l_lgps_pensionable_pay,0) + c_formula_pension_value;
822 END IF;
823 hr_utility.set_location('After adding additional Pension Value'||l_lgps_pensionable_pay,42);
824
825 END IF; --check for presence of formula in configuration value screen
826
827 IF c_formula_pension_value is not null or l_lgps_pensionable_pay is not null
828 THEN
829 l_lgps_pensionable_pay := round(l_lgps_pensionable_pay,2);
830 fnd_file.put_line(FND_FILE.OUTPUT, '-----------------------------Total value '||nvl(l_lgps_pensionable_pay,0));
831 l_lgps_process_flag := 'P';
832 IF b_pqp_found
833 THEN
834 hr_utility.trace(' updating the Contractual pay for effective date '||v_max_date|| ' mode '||v_mode||' OVN '||n_object_version_no);
835 pqp_aat_api.update_assignment_attribute
836 (p_validate => false
837 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
838 ,p_datetrack_mode => v_mode
839 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
840 ,p_business_group_id => p_business_group_id
841 ,p_effective_start_date => v_max_date
842 ,p_effective_end_date => v_eff_end_date
843 ,p_assignment_id => c_all_assignments.assignment_id
844 ,p_object_version_number => n_object_version_no
845 ,p_lgps_process_flag => l_lgps_process_flag
846 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
847 );
848
849 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
850 LOOP
851 n_object_version_no := K.object_version_number;
852 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
853 pqp_aat_api.update_assignment_attribute
854 (p_validate => false
855 ,p_effective_date => v_eff_start_date_corr
856 ,p_datetrack_mode => 'CORRECTION'
857 ,p_assignment_attribute_id => K.assignment_attribute_id
858 ,p_business_group_id => p_business_group_id
859 ,p_effective_start_date => K.EFFECTIVE_START_DATE
860 ,p_effective_end_date => K.EFFECTIVE_END_DATE
861 ,p_assignment_id => c_all_assignments.assignment_id
862 ,p_object_version_number => n_object_version_no
863 ,p_lgps_process_flag => l_lgps_process_flag
864 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
865 );
866 END LOOP;
867
868 Else
869 hr_utility.set_location('Inserting the Contractual pay for effective date'||v_assignment_eff_date,555);
870 pqp_aat_api.create_assignment_attribute
871 (p_effective_date => v_assignment_eff_date
872 ,p_business_group_id => p_business_group_id
873 ,p_assignment_id => c_all_assignments.assignment_id
874 ,p_assignment_attribute_id => l_assignment_attribute_id
875 ,p_object_version_number => n_object_version_no
876 ,p_effective_start_date => l_eff_start_date_op
877 ,p_effective_end_date => l_eff_end_date_op
878 ,p_lgps_process_flag => l_lgps_process_flag
879 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
880 );
881 END IF; -- b_pqp_found
882 END IF; -- check for l_lgps_pensionable_pay
883 hr_utility.set_location('After updating Process flag to P'||c_all_assignments.assignment_id,45);
884 Raise skip_assignment;
885 END IF; --rate type entered
886
887
888 IF b_pqp_found THEN
889 IF (not b_input_value_present)
890 THEN
891 l_lgps_process_flag := 'I';
892 hr_utility.set_location('Calling API to update LGPS Process Flag I',30);
893 pqp_aat_api.update_assignment_attribute
894 (p_validate => false
895 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
896 ,p_datetrack_mode => v_mode
897 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
898 ,p_business_group_id => p_business_group_id
899 ,p_effective_start_date => v_max_date
900 ,p_effective_end_date => v_eff_end_date
901 ,p_assignment_id => c_all_assignments.assignment_id
902 ,p_object_version_number => n_object_version_no
903 ,p_lgps_process_flag => l_lgps_process_flag
904 );
905
906 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
907 LOOP
908 n_object_version_no := K.object_version_number;
909 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
910 pqp_aat_api.update_assignment_attribute
911 (p_validate => false
912 ,p_effective_date => v_eff_start_date_corr
913 ,p_datetrack_mode => 'CORRECTION'
914 ,p_assignment_attribute_id => K.assignment_attribute_id
915 ,p_business_group_id => p_business_group_id
916 ,p_effective_start_date => K.EFFECTIVE_START_DATE
917 ,p_effective_end_date => K.EFFECTIVE_END_DATE
918 ,p_assignment_id => c_all_assignments.assignment_id
919 ,p_object_version_number => n_object_version_no
920 ,p_lgps_process_flag => l_lgps_process_flag
921 );
922 END LOOP;
923 END IF;
924 Else
925 IF (not b_input_value_present)
926 THEN
927 l_lgps_process_flag := 'I';
928 hr_utility.set_location('Calling API to insert LGPS Process Flag I',305);
929 pqp_aat_api.create_assignment_attribute
930 (p_effective_date => v_assignment_eff_date
931 ,p_business_group_id => p_business_group_id
932 ,p_assignment_id => c_all_assignments.assignment_id
933 ,p_assignment_attribute_id => l_assignment_attribute_id
934 ,p_object_version_number => n_object_version_no
935 ,p_effective_start_date => l_eff_start_date_op
936 ,p_effective_end_date => l_eff_end_date_op
937 ,p_lgps_process_flag => l_lgps_process_flag
938 );
939 END IF;
940 END IF;
941
942 Exception
943 when skip_assignment then
944 hr_utility.set_location('Skipped Assignment'||c_all_assignments.assignment_id,45);
945 END;
946 END LOOP; --loop for all valid assignments
947 COMMIT;
948 hr_utility.set_location('Leaving: ' || l_proc, 100);
949 END DERIVE_PENSIONABLE_PAY;
950 ---------------------------------------------------------------------
951 /* This section of code is ued by the formula functions
952 which inturn will be call from the pension element fast formula */
953 ---------------------------------------------------------------------
954 -- To fetch the Transitional flag for the assignment
955 FUNCTION GET_PQP_LGPS_TRANSITIONAL_FLAG(p_assignment_id IN NUMBER,
956 p_effective_date Date,
957 p_business_group_id NUMBER)
958 RETURN VARCHAR2
959 IS
960 v_trans_flag varchar2(30);
961 CURSOR C_Transtional_Flag
962 IS
963 SELECT LGPS_TRANS_ARRANG_FLAG
964 FROM pqp_assignment_attributes_f pqaaf
965 WHERE pqaaf.assignment_id = p_assignment_id
966 AND pqaaf.business_group_id = p_business_group_id
967 AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;
968 BEGIN
969 OPEN C_Transtional_Flag;
970 FETCH C_Transtional_Flag INTO v_trans_flag;
971 CLOSE C_Transtional_Flag;
972 return v_trans_flag;
973 END GET_PQP_LGPS_TRANSITIONAL_FLAG;
974 -- To fetch the Contractual pay for the assignment
975 FUNCTION GET_PQP_LGPS_PENSION_PAY(p_assignment_id IN NUMBER,
976 p_effective_date Date,
977 p_business_group_id NUMBER)
978 RETURN number
979 IS
980 n_lgps_pension_pay pqp_assignment_attributes_f.LGPS_PENSIONABLE_PAY%type;
981 CURSOR c_pension_pay
982 IS
983 SELECT nvl(LGPS_PENSIONABLE_PAY,-1)
984 FROM pqp_assignment_attributes_f pqaaf
985 WHERE pqaaf.assignment_id = p_assignment_id
986 AND pqaaf.business_group_id = p_business_group_id
987 AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;
988 BEGIN
989 OPEN c_pension_pay;
990 FETCH c_pension_pay INTO n_lgps_pension_pay;
991 CLOSE c_pension_pay;
992 RETURN n_lgps_pension_pay;
993 END GET_PQP_LGPS_PENSION_PAY;
994
995 -- To fetch the current Pension Financial year
996 FUNCTION GET_FINANCIAL_YEAR(p_effective_date Date)
997 RETURN NUMBER IS
998 n_year NUMBER;
999 n_date DATE;
1000 BEGIN
1001 IF p_effective_date between to_date('01-04-'||to_char(p_effective_date,'YYYY'),'DD-MM-YYYY') and to_date('31-12-'||to_char(p_effective_date,'YYYY'),'DD-MM-YYYY')
1002 THEN
1003 n_year := to_number(to_char(p_effective_date,'yyyy'));
1004 ELSE
1005 n_year := to_number(to_char(p_effective_date,'yyyy')) -1 ;
1006 END IF;
1007 RETURN n_year;
1008 END GET_FINANCIAL_YEAR;
1009 end PQP_GB_LGPS_PENSIONPAY;
1010
[Home] [Help]