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