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