1 PACKAGE BODY PQP_GB_SCOTLAND_LGPS_PENSIONPY AS
2 /* $Header: pqgbsclgps.pkb 120.3.12010000.3 2008/09/26 06:43:38 pbalu noship $ */
3 --
4 g_package VARCHAR2(31) := 'PQP_GB_SCOTLAND_LGPS_PENSIONPY.';
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 --7369484 Changed to Scottish configuration value
321 cursor c_pqp_formula_element_det
322 is
323 select distinct PCV_INFORMATION1
324 From pqp_configuration_values
325 where pcv_information_category = 'PQP_GB_LGPS_SCOTLAND_FF_INFO'
326 AND business_group_id=p_business_group_id; --New business group specific Condition is added in this cursor for bug 6856733
327
328 --6666135 End
329 --7369484 End
330
331 --This cursor will check for the presence of assignments in pqp_assignment_attributes_f table
332 CURSOR c_pqp_assignment(n_assignment_id NUMBER)
333 IS
334 SELECT pqpaaf.lgps_process_flag,
335 pqpaaf.assignment_attribute_id,
336 pqpaaf.EFFECTIVE_START_DATE,
337 pqpaaf.object_version_number
338 FROM pqp_assignment_attributes_f pqpaaf
339 WHERE pqpaaf.assignment_id = n_assignment_id
340 AND pqpaaf.business_group_id = p_business_group_id
341 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
342 AND ( v_assignment_eff_date between pqpaaf.EFFECTIVE_START_DATE and pqpaaf.EFFECTIVE_END_DATE
343 OR pqpaaf.EFFECTIVE_START_DATE = (select min(EFFECTIVE_START_DATE) from pqp_assignment_attributes_f where assignment_id = n_assignment_id
344 AND lgps_process_flag = nvl(l_mode,lgps_process_flag) AND business_group_id = p_business_group_id
345 AND EFFECTIVE_START_DATE BETWEEN v_assignment_eff_date AND v_eff_end_date));
346
347 --This cursor will fetch the all elements for the assigment
348 CURSOR c1_all_element(n_assignment_id NUMBER)
349 IS
350 SELECT peef.ELEMENT_TYPE_ID,petf.ELEMENT_NAME
351 FROM pay_element_entries_f peef, pay_element_types_f petf
352 WHERE peef.ASSIGNMENT_ID=n_assignment_id
353 AND peef.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
354 AND petf.business_group_id = p_business_group_id
355 AND v_assignment_eff_date between peef.EFFECTIVE_START_DATE and peef.EFFECTIVE_END_DATE
356 AND v_assignment_eff_date between petf.EFFECTIVE_START_DATE and petf.EFFECTIVE_END_DATE;
357 --6666135 End
358
359 --For the element to get processed by pqp_rates_history_calc.get_historic_rate two rows should be present
360 --One row with data PQP_UK_RATE_TYPE and PQP_LGPS_SCOTLAND_PENSION_PAY
361 --and second row with PQP_UK_ELEMENT_ATTRIBUTION and Pay Value.
362 -- 7369484 Start Changes made in cursor to fetch both rate types
363 CURSOR c2_PQP_UK_RATE_TYPE(n_element_type_id NUMBER)
364 IS
365 SELECT pet.EEI_INFORMATION1
366 FROM pay_element_type_extra_info pet
367 WHERE pet.element_type_id = n_element_type_id
368 AND pet.INFORMATION_TYPE = 'PQP_UK_RATE_TYPE'
369 AND pet.EEI_INFORMATION1 IN ('PQP_LGPS_PENSION_PAY','PQP_LGPS_SCOTLAND_PENSION_PAY');
370 -- 7369484 End
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
421
422 --6813970 begin
423
424 l_formula_id NUMBER;
425 l_tab_asg_set_amnds pqp_budget_maintenance.t_asg_set_amnds;
426 l_include_flag VARCHAR2(10);
427
428 --6813970 end
429
430 --7369484 Begin
431
432 b_eng_rate boolean;
433 b_scot_rate boolean;
434
435 --7369484 End
436
437 -- main
438 BEGIN
439 -- hr_utility.trace_on(null,'gag');
440 hr_utility.set_location('Entering: ' || l_proc, 10);
441 BEGIN
442 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
443 values(userenv('sessionid'), trunc(SYSDATE));
444 EXCEPTION
445 WHEN others THEN
446 hr_utility.trace('SESSION ALREADY EXISTS :'|| sqlerrm);
447 Raise;
448 END;
449 v_eff_start_date := fnd_date.canonical_to_date(p_effective_start_dt);
450 --calculation of pension end date for the year
451 N_start_year := TO_NUMBER(TO_CHAR(v_eff_start_date,'YYYY'));
452 IF trunc(v_eff_start_date) > TO_DATE('31-03-'||N_start_year,'DD-MM-YYYY')
453 THEN
454 N_start_year := N_start_year+1;
455 END IF;
456 v_eff_end_date := TO_DATE('31-03-'||N_start_year,'DD-MM-YYYY');
457 v_given_end_date := v_eff_end_date;
458 IF p_effective_end_dt IS not NULL --to date entered by the user.
459 THEN
460 v_given_end_date := fnd_date.canonical_to_date(p_effective_end_dt);
461 --To check the given dates falls in the same pension year
462 IF ((v_eff_start_date BETWEEN to_date('01/04/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
463 AND to_date('31/12/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy'))
464 AND (v_eff_end_date BETWEEN to_date('01/04/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
465 AND to_date('31/03/'||to_char(to_number(to_char(v_eff_start_date,'YYYY'))+1),'dd/mm/yyyy')))
466 OR ((v_eff_start_date BETWEEN to_date('01/01/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
467 AND to_date('31/03/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy'))
468 AND (v_eff_end_date BETWEEN to_date('01/01/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')
469 AND to_date('31/03/'||to_char(v_eff_start_date,'YYYY'),'dd/mm/yyyy')))
470 THEN
471 hr_utility.set_location('Start date and End date are in the same pension year ',6);
472 ELSE
473 fnd_file.put_line (fnd_file.LOG, 'Start date and End date should fall in the same pension year.');
474 hr_utility.set_location('Start date and End date should fall in the same tax year',8);
475 v_eff_start_date := Null; -- to exit the program
476 END IF;
477 END IF;
478 --6813970 begin
479 --Check for Assignment set
480 If p_assignment_set_id is not null then
481 pqp_budget_maintenance.get_asg_set_details(p_assignment_set_id => p_assignment_set_id
482 ,p_formula_id => l_formula_id
483 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
484 );
485 If l_formula_id is null and l_tab_asg_set_amnds.count = 0 then
486 fnd_file.put_line (fnd_file.LOG, ' Assignment set id entered '||p_assignment_set_id||' is not valid');
487 v_eff_start_date := Null; -- to exit the program
488 end if;
489 End if;
490 --6813970 end
491 --6666135 Begin
492 --Fetching formula details
493 j_formula_count := 0;
494 For I in c_pqp_formula_element_det
495 loop
496 j_formula_count := j_formula_count+1;
497 Formula_Element_Tab(j_formula_count).Element_name := substr(I.PCV_INFORMATION1,1,instr(I.PCV_INFORMATION1,'|')-1);
498 Formula_Element_Tab(j_formula_count).Formula_name := substr(I.PCV_INFORMATION1,instr(I.PCV_INFORMATION1,'|')+1);
499 hr_utility.trace(' Element_name '||j_formula_count|| Formula_Element_Tab(j_formula_count).Element_name);
500 hr_utility.trace(' Formula_name '||j_formula_count|| Formula_Element_Tab(j_formula_count).Formula_name);
501 end loop;
502 --6666135 End
503
504 v_eff_end_date_corr := v_eff_end_date;
505 hr_utility.set_location('Effective Start Date: ' || p_effective_start_dt,1);
506 hr_utility.set_location('Effective End Date: ' || p_effective_end_dt,2);
507 hr_utility.set_location('Calculated End Date: ' || v_eff_end_date_corr, 3);
508 hr_utility.set_location('Calculated Start Date: ' || v_eff_start_date, 3);
509 hr_utility.set_location('p_assignment_set_id: ' || p_assignment_set_id, 3);
510 hr_utility.set_location('p_assignment_number: ' || p_assignment_number, 3);
511 hr_utility.set_location('p_employee_no: ' || p_employee_no, 3);
512 hr_utility.set_location('p_payroll_id: ' || p_payroll_id, 3);
513 hr_utility.set_location('p_business_group_id: ' || p_business_group_id, 3);
514 hr_utility.set_location('p_mode: ' || p_mode, 3);
515 IF p_mode = 'Reprocess'
516 THEN
517 l_mode := 'Y';
518 ELSIF p_mode = 'Incomplete'
519 THEN
520 l_mode := 'I';
521 END IF;
522 --
523 hr_utility.set_location('l_mode: ' || l_mode, 3);
524 FOR c_all_assignments in c_all_valid_assignment
525 LOOP
526 Begin
527 hr_utility.set_location('Inside valid Assignments'||c_all_assignments.assignment_id,20);
528
529 if nvl(n_prev_assignment_id,'0.000') = c_all_assignments.assignment_id then
530 raise skip_assignment;
531 end if;
532
533
534
535 n_prev_assignment_id := c_all_assignments.assignment_id;
536 l_lgps_pensionable_pay := NULL;
537 c_formula_pension_value := NULL;
538 b_element_present := FALSE;
539 b_rate_type := FALSE;
540 b_input_value_present := FALSE;
541 b_pqp_found := NULL;
542 b_pqp_assignment_found := NULL;
543 c_pqp_assignment_row := NULL;
544 v_assignment_eff_date := NULL;
545 max_future_date := NULL;
546 Formula_Tab := Formula_Tab_new; --6666135
547 k_aasgn_form_count := 0; --6666135
548 -- 7369484 Begin
549 b_eng_rate := FALSE;
550 b_scot_rate := FALSE;
551 -- 7369484 End
552
553 v_assignment_eff_date := Greatest(c_all_assignments.EFFECTIVE_START_DATE,v_eff_start_date);
554
555 --6813970 begin
556 l_include_flag := 'N';
557 If p_assignment_set_id is not null then
558 l_include_flag := pqp_budget_maintenance.chk_is_asg_in_asg_set(p_assignment_id => c_all_assignments.assignment_id
559 ,p_formula_id => l_formula_id
560 ,p_tab_asg_set_amnds => l_tab_asg_set_amnds
561 ,p_effective_date => v_assignment_eff_date
562 );
563 if l_include_flag = 'N' then
564 raise skip_assignment;
565 end if;
566
567 end if;
568 --6813970 end
569
570 OPEN c_future_date(c_all_assignments.assignment_id);
571 FETCH c_future_date into max_future_date;
572 IF max_future_date is not NULL THEN
573 b_pqp_found := TRUE;
574 END IF;
575 CLOSE c_future_date;
576
577 IF b_pqp_found THEN
578 OPEN c_pqp_assignment(c_all_assignments.assignment_id);
579 FETCH c_pqp_assignment INTO c_pqp_assignment_row;
580 IF c_pqp_assignment%found
581 then
582 b_pqp_assignment_found := TRUE;
583 END IF;
584 CLOSE c_pqp_assignment;
585 IF b_pqp_assignment_found THEN
586 IF ((p_mode = 'Start of the Year' and nvl(c_pqp_assignment_row.lgps_process_flag,'Y') in ('Y','P','I','U'))
587 or l_mode in ('Y','I')
588 or nvl(c_pqp_assignment_row.lgps_process_flag,'New Hires') = p_mode) THEN
589 v_max_date := Greatest(c_pqp_assignment_row.EFFECTIVE_START_DATE, v_eff_start_date);
590 n_object_version_no := c_pqp_assignment_row.object_version_number;
591 ElSE
592 Raise skip_assignment;
593 END IF;
594
595 v_assignment_eff_date := Greatest(c_pqp_assignment_row.EFFECTIVE_START_DATE, v_assignment_eff_date);
596 END IF;
597 END IF;
598
599 IF b_pqp_assignment_found is NULL AND l_mode is not null THEN
600 --No records in PQP_ASSIGNMENT_ATTRIBUTES_F table matching mode - reprocess or incomplete.
601 raise skip_assignment;
602 END IF;
603
604 OPEN c1_all_element(c_all_assignments.assignment_id);
605 LOOP
606 BEGIN
607 hr_utility.set_location('Inside valid Elements for the Assignment'||c_all_assignments.assignment_id,20);
608 FETCH c1_all_element into c1_all_element_data;
609 IF c1_all_element%NOTFOUND
610 THEN
611 CLOSE c1_all_element;
612 EXIT;
613 END IF;
614 hr_utility.set_location('Checking for Historic rate type',25);
615 b_element_present := TRUE;
616
617 l_ELEMENT_NAME := null;
618
619 --6666135 Begin
620 FOR F in 1..j_formula_count
621 loop
622 if Formula_Element_Tab(F).Element_name = c1_all_element_data.Element_name then
623 k_aasgn_form_count := k_aasgn_form_count+1;
624 Formula_Tab(k_aasgn_form_count) := Formula_Element_Tab(F).Formula_name;
625 l_ELEMENT_NAME := c1_all_element_data.Element_name;
626 End if;
627 End loop;
628 --6666135 End
629
630 --To Check the values PQP_UK_RATE_TYPE, PQP_LGPS_PENSION_PAY, INPUT_VALUE AND PAY VALUE in pay_element_type_extra_info
631 OPEN c2_PQP_UK_RATE_TYPE(c1_all_element_data.element_type_id);
632 FETCH c2_PQP_UK_RATE_TYPE INTO c2_PQP_UK_RATE_TYPE_data;
633 IF c2_PQP_UK_RATE_TYPE%NOTFOUND
634 THEN
635 CLOSE c2_PQP_UK_RATE_TYPE;
636 RAISE skip_element;
637 END IF;
638 -- 7369484 Begin
639 IF c2_PQP_UK_RATE_TYPE_data.EEI_INFORMATION1 = 'PQP_LGPS_PENSION_PAY'
640 THEN
641 b_eng_rate := TRUE;
642 IF b_scot_rate = TRUE
643 THEN
644 fnd_file.put_line (fnd_file.LOG,'Assignment can not have both Types of RATE TYPE attached. Assignment Number : '||c_all_assignments.assignment_number);
645 CLOSE c1_all_element;
646 CLOSE c2_PQP_UK_RATE_TYPE;
647 RAISE skip_assignment;
648 END IF;
649 ELSIF c2_PQP_UK_RATE_TYPE_data.EEI_INFORMATION1 = 'PQP_LGPS_SCOTLAND_PENSION_PAY'
650 THEN
651 -- 7369484 End
652 hr_utility.set_location('Historic Rate Type present for Element '||c1_all_element_data.element_type_id,26);
653 b_rate_type := TRUE;
654 -- 7369484 Begin
655 b_scot_rate := TRUE;
656 IF b_eng_rate = TRUE
657 THEN
658 fnd_file.put_line (fnd_file.LOG,'Assignment can not have both Types of RATE TYPE attached. Assignment Number : '||c_all_assignments.assignment_number);
659 CLOSE c1_all_element;
660 CLOSE c2_PQP_UK_RATE_TYPE;
661 RAISE skip_assignment;
662 END IF;
663 END IF; -- 7369484 if rate type is English rate type
664 OPEN c3_pqp_lgps_pension_pay(c1_all_element_data.element_type_id);
665 FETCH c3_pqp_lgps_pension_pay INTO c3_pqp_lgps_pension_pay_data;
666 IF c3_pqp_lgps_pension_pay%NOTFOUND
667 THEN
668 hr_utility.set_location('Pay Value Not Present',27);
669 IF l_ELEMENT_NAME IS NULL THEN
670 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;
671 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
672 END IF;
673 ELSE
674 b_input_value_present := TRUE;
675 hr_utility.set_location('Pay Value Present',28);
676 END IF;
677 CLOSE c2_PQP_UK_RATE_TYPE;
678 CLOSE c3_pqp_lgps_pension_pay;
679 EXCEPTION
680 WHEN skip_element THEN
681 NULL;
682 END;
683 END LOOP; --loop for all elements in a assignment
684
685
686 IF b_eng_rate = TRUE
687 THEN
688 raise skip_assignment;
689 END IF;
690
691
692 IF b_pqp_found THEN
693 IF b_pqp_assignment_found THEN
694 IF c_pqp_assignment_row.EFFECTIVE_START_DATE = v_assignment_eff_date
695 THEN
696 v_mode := 'CORRECTION';
697 ELSIF v_max_date < max_future_date
698 THEN
699 v_mode := 'UPDATE_CHANGE_INSERT';
700 ELSE
701 v_mode := 'UPDATE';
702 END IF;
703 hr_utility.set_location('Mode for update v_mode: ' || v_mode, 3);
704 --6813970 begin
705 -- IF (not b_element_present) or (not b_rate_type)
706 -- THEN
707 -- 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;
708 IF ((not b_element_present) or (not b_rate_type)) and k_aasgn_form_count = 0
709 THEN
710 l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Neither Historic Rate - Rate Type "LGPS Scotland Pensionable Pay" nor User defined formula set for elements against Assignment '||c_all_assignments.assignment_number;
711 --6813970 end
712 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
713 l_lgps_process_flag := 'I';
714 hr_utility.set_location('Calling API to update LGPS Process Flag I',35);
715 pqp_aat_api.update_assignment_attribute
716 (p_validate => false
717 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
718 ,p_datetrack_mode => v_mode
719 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
720 ,p_business_group_id => p_business_group_id
721 ,p_effective_start_date => v_max_date
722 ,p_effective_end_date => v_eff_end_date
723 ,p_assignment_id => c_all_assignments.assignment_id
724 ,p_object_version_number => n_object_version_no
725 ,p_lgps_process_flag => l_lgps_process_flag
726 );
727 ---******************
728 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
729 LOOP
730 n_object_version_no := K.object_version_number;
731 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
732 pqp_aat_api.update_assignment_attribute
733 (p_validate => false
734 ,p_effective_date => v_eff_start_date_corr
735 ,p_datetrack_mode => 'CORRECTION'
736 ,p_assignment_attribute_id => K.assignment_attribute_id
737 ,p_business_group_id => p_business_group_id
738 ,p_effective_start_date => K.EFFECTIVE_START_DATE
739 ,p_effective_end_date => K.EFFECTIVE_END_DATE
740 ,p_assignment_id => c_all_assignments.assignment_id
741 ,p_object_version_number => n_object_version_no
742 ,p_lgps_process_flag => l_lgps_process_flag
743 );
744 END LOOP;
745 raise skip_assignment;
746 END IF;
747 ----------
748 ELSE
749 IF l_mode is null then
750 l_warning_msg := 'Cannot process Assignment : '||c_all_assignments.assignment_number||' Future changes present in table pqp_assignment_attributes_f ';
751 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
752 END IF;
753 raise skip_assignment;
754 END IF; --b_pqp_assignment_found
755
756 ELSIF p_mode = 'New Hires' then --insert
757 --NO CURRENT RECORD IN pqp SO INSERT A RECORD WITH CP START DATE
758 --6813970 begin
759 -- IF (not b_element_present) or (not b_rate_type)
760 -- THEN
761 -- 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;
762
763 IF ((not b_element_present) or (not b_rate_type)) and k_aasgn_form_count = 0
764 THEN
765 l_warning_msg := 'PQP_LGPS_MISSING_RATE_INFO: Neither Historic Rate - Rate Type "LGPS Scotland Pensionable Pay" nor User defined formula set for elements against Assignment '||c_all_assignments.assignment_number;
766 --6813970 end
767 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
768 l_lgps_process_flag := 'I';
769 hr_utility.set_location('Calling API to insert LGPS Process Flag I',355);
770 pqp_aat_api.create_assignment_attribute
771 (p_effective_date => v_assignment_eff_date
772 ,p_business_group_id => p_business_group_id
773 ,p_assignment_id => c_all_assignments.assignment_id
774 ,p_assignment_attribute_id => l_assignment_attribute_id
775 ,p_object_version_number => n_object_version_no
776 ,p_effective_start_date => l_eff_start_date_op
777 ,p_effective_end_date => l_eff_end_date_op
778 ,p_lgps_process_flag => l_lgps_process_flag
779 );
780 raise skip_assignment;
781 END IF;
782
783 END IF; -- b_pqp_found
784
785 --6813970 begin
786 -- IF (b_element_present and b_input_value_present)
787 -- THEN
788 IF (b_rate_type and b_input_value_present)
789 THEN
790 --6813970 end
791 Begin
792 l_lgps_pensionable_pay:=pqp_rates_history_calc.get_historic_rate(p_assignment_id => c_all_assignments.assignment_id
793 ,p_rate_name => 'LGPS Scotland Pensionable Pay'
794 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
795 ,p_time_dimension => 'A'
796 ,p_rate_type_or_element => 'R');
797 Exception
798 when others then
799 l_warning_msg := 'Cannot process Assignment : '||c_all_assignments.assignment_number||' Historic Rate calculations failed. Please check the error message ';
800 fnd_file.put_line (fnd_file.LOG, l_warning_msg);
801 fnd_file.put_line (fnd_file.LOG,'*** -ERROR- '||SQLCODE||' - '||SQLERRM);
802 b_rate_type := FALSE; --to skip processing of user defined formulae
803 k_aasgn_form_count := 0; --to skip processing of user defined formulae --6813970
804 b_input_value_present := FALSE; --to make the LGPS process flag as Incomplete.
805 End;
806 END IF;
807 hr_utility.set_location('Calculated Pensionable Pay'||l_lgps_pensionable_pay,40);
808
809 ---- To calculate the value from user defined formula and add it to the value from historic rates function
810 --6813970 begin
811 -- IF b_rate_type
812 -- THEN
813 IF b_rate_type or k_aasgn_form_count > 0
814 THEN -- Rate type defined for atleast one element
815 --6813970 end
816 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
817 fnd_file.put_line(FND_FILE.OUTPUT, ' Assignment '||c_all_assignments.assignment_number ||' Historic rate value '||nvl(l_lgps_pensionable_pay,0));
818 END IF;
819 IF k_aasgn_form_count > 0 then
820
821 hr_utility.set_location(' Calling RUN_USER_FORMULA ',25);
822 -- Call the formula to calculate the additional pension pay
823 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);
824 hr_utility.set_location(' formula returned value '||c_formula_pension_value,40);
825
826 IF nvl(c_formula_pension_value,0) >= 0 -- equal to condition is added for bug 6857280
827 THEN
828 l_lgps_pensionable_pay := NVL(l_lgps_pensionable_pay,0) + c_formula_pension_value;
829 END IF;
830 hr_utility.set_location('After adding additional Pension Value'||l_lgps_pensionable_pay,42);
831
832 END IF; --check for presence of formula in configuration value screen
833
834 IF c_formula_pension_value is not null or l_lgps_pensionable_pay is not null
835 THEN
836 l_lgps_pensionable_pay := floor(l_lgps_pensionable_pay);
837 fnd_file.put_line(FND_FILE.OUTPUT, '-----------------------------Total value '||nvl(l_lgps_pensionable_pay,0));
838 l_lgps_process_flag := 'P';
839 IF b_pqp_found
840 THEN
841 hr_utility.trace(' updating the Contractual pay for effective date '||v_max_date|| ' mode '||v_mode||' OVN '||n_object_version_no);
842 pqp_aat_api.update_assignment_attribute
843 (p_validate => false
844 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
845 ,p_datetrack_mode => v_mode
846 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
847 ,p_business_group_id => p_business_group_id
848 ,p_effective_start_date => v_max_date
849 ,p_effective_end_date => v_eff_end_date
850 ,p_assignment_id => c_all_assignments.assignment_id
851 ,p_object_version_number => n_object_version_no
852 ,p_lgps_process_flag => l_lgps_process_flag
853 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
854 );
855
856 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
857 LOOP
858 n_object_version_no := K.object_version_number;
859 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
860 pqp_aat_api.update_assignment_attribute
861 (p_validate => false
862 ,p_effective_date => v_eff_start_date_corr
863 ,p_datetrack_mode => 'CORRECTION'
864 ,p_assignment_attribute_id => K.assignment_attribute_id
865 ,p_business_group_id => p_business_group_id
866 ,p_effective_start_date => K.EFFECTIVE_START_DATE
867 ,p_effective_end_date => K.EFFECTIVE_END_DATE
868 ,p_assignment_id => c_all_assignments.assignment_id
869 ,p_object_version_number => n_object_version_no
870 ,p_lgps_process_flag => l_lgps_process_flag
871 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
872 );
873 END LOOP;
874
875 Else
876 hr_utility.set_location('Inserting the Contractual pay for effective date'||v_assignment_eff_date,555);
877 pqp_aat_api.create_assignment_attribute
878 (p_effective_date => v_assignment_eff_date
879 ,p_business_group_id => p_business_group_id
880 ,p_assignment_id => c_all_assignments.assignment_id
881 ,p_assignment_attribute_id => l_assignment_attribute_id
882 ,p_object_version_number => n_object_version_no
883 ,p_effective_start_date => l_eff_start_date_op
884 ,p_effective_end_date => l_eff_end_date_op
885 ,p_lgps_process_flag => l_lgps_process_flag
886 ,p_lgps_pensionable_pay => l_lgps_pensionable_pay
887 );
888 END IF; -- b_pqp_found
889 END IF; -- check for l_lgps_pensionable_pay
890 hr_utility.set_location('After updating Process flag to P'||c_all_assignments.assignment_id,45);
891 Raise skip_assignment;
892 END IF; --rate type entered
893
894
895 IF b_pqp_found THEN
896 IF (not b_input_value_present)
897 THEN
898 l_lgps_process_flag := 'I';
899 hr_utility.set_location('Calling API to update LGPS Process Flag I',30);
900 pqp_aat_api.update_assignment_attribute
901 (p_validate => false
902 ,p_effective_date => v_assignment_eff_date --v_eff_start_date
903 ,p_datetrack_mode => v_mode
904 ,p_assignment_attribute_id => c_pqp_assignment_row.assignment_attribute_id
905 ,p_business_group_id => p_business_group_id
906 ,p_effective_start_date => v_max_date
907 ,p_effective_end_date => v_eff_end_date
908 ,p_assignment_id => c_all_assignments.assignment_id
909 ,p_object_version_number => n_object_version_no
910 ,p_lgps_process_flag => l_lgps_process_flag
911 );
912
913 FOR K IN c_correct_pqp(c_all_assignments.assignment_id)
914 LOOP
915 n_object_version_no := K.object_version_number;
916 v_eff_start_date_corr := K.EFFECTIVE_START_DATE;
917 pqp_aat_api.update_assignment_attribute
918 (p_validate => false
919 ,p_effective_date => v_eff_start_date_corr
920 ,p_datetrack_mode => 'CORRECTION'
921 ,p_assignment_attribute_id => K.assignment_attribute_id
922 ,p_business_group_id => p_business_group_id
923 ,p_effective_start_date => K.EFFECTIVE_START_DATE
924 ,p_effective_end_date => K.EFFECTIVE_END_DATE
925 ,p_assignment_id => c_all_assignments.assignment_id
926 ,p_object_version_number => n_object_version_no
927 ,p_lgps_process_flag => l_lgps_process_flag
928 );
929 END LOOP;
930 END IF;
931 Else
932 IF (not b_input_value_present)
933 THEN
934 l_lgps_process_flag := 'I';
935 hr_utility.set_location('Calling API to insert LGPS Process Flag I',305);
936 pqp_aat_api.create_assignment_attribute
937 (p_effective_date => v_assignment_eff_date
938 ,p_business_group_id => p_business_group_id
939 ,p_assignment_id => c_all_assignments.assignment_id
940 ,p_assignment_attribute_id => l_assignment_attribute_id
941 ,p_object_version_number => n_object_version_no
942 ,p_effective_start_date => l_eff_start_date_op
943 ,p_effective_end_date => l_eff_end_date_op
944 ,p_lgps_process_flag => l_lgps_process_flag
945 );
946 END IF;
947 END IF;
948
949 Exception
950 when skip_assignment then
951 hr_utility.set_location('Skipped Assignment'||c_all_assignments.assignment_id,45);
952 END;
953 END LOOP; --loop for all valid assignments
954 COMMIT;
955 hr_utility.set_location('Leaving: ' || l_proc, 100);
956 END DERIVE_PENSIONABLE_PAY;
957 ---------------------------------------------------------------------
958 /* This section of code is ued by the formula functions
959 which inturn will be call from the pension element fast formula */
960 ---------------------------------------------------------------------
961 -- To fetch the Transitional flag for the assignment
962 /*FUNCTION GET_PQP_LGPS_TRANSITIONAL_FLAG(p_assignment_id IN NUMBER,
963 p_effective_date Date,
964 p_business_group_id NUMBER)
965 RETURN VARCHAR2
966 IS
967 v_trans_flag varchar2(30);
968 CURSOR C_Transtional_Flag
969 IS
970 SELECT LGPS_TRANS_ARRANG_FLAG
971 FROM pqp_assignment_attributes_f pqaaf
972 WHERE pqaaf.assignment_id = p_assignment_id
973 AND pqaaf.business_group_id = p_business_group_id
974 AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;
975 BEGIN
976 OPEN C_Transtional_Flag;
977 FETCH C_Transtional_Flag INTO v_trans_flag;
978 CLOSE C_Transtional_Flag;
979 return v_trans_flag;
980 END GET_PQP_LGPS_TRANSITIONAL_FLAG; */
981 -- To fetch the Contractual pay for the assignment
982 FUNCTION GET_PQP_LGPS_PENSION_PAY(p_assignment_id IN NUMBER,
983 p_effective_date Date,
984 p_business_group_id NUMBER)
985 RETURN number
986 IS
987 n_lgps_pension_pay pqp_assignment_attributes_f.LGPS_PENSIONABLE_PAY%type;
988 CURSOR c_pension_pay
989 IS
990 SELECT nvl(LGPS_PENSIONABLE_PAY,-1)
991 FROM pqp_assignment_attributes_f pqaaf
992 WHERE pqaaf.assignment_id = p_assignment_id
993 AND pqaaf.business_group_id = p_business_group_id
994 AND p_effective_date between pqaaf.effective_start_date and pqaaf.effective_end_date;
995 BEGIN
996 OPEN c_pension_pay;
997 FETCH c_pension_pay INTO n_lgps_pension_pay;
998 CLOSE c_pension_pay;
999 RETURN n_lgps_pension_pay;
1000 END GET_PQP_LGPS_PENSION_PAY;
1001
1002 -- To fetch the current Pension Financial year
1003 /*FUNCTION GET_FINANCIAL_YEAR(p_effective_date Date)
1004 RETURN NUMBER IS
1005 n_year NUMBER;
1006 n_date DATE;
1007 BEGIN
1008 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')
1009 THEN
1010 n_year := to_number(to_char(p_effective_date,'yyyy'));
1011 ELSE
1012 n_year := to_number(to_char(p_effective_date,'yyyy')) -1 ;
1013 END IF;
1014 RETURN n_year;
1015 END GET_FINANCIAL_YEAR; */
1016 end PQP_GB_SCOTLAND_LGPS_PENSIONPY;
1017
[Home] [Help]