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