1 PACKAGE BODY pqp_gb_ad_ee AS
2 /* $Header: pqgbadee.pkb 120.14.12020000.4 2013/01/30 13:20:36 rsadhana ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5 g_package varchar2(33) := 'pqp_gb_ad_ee.';
6 -----------------------------------------------------------------------------
7 -- ASG_EXPIRES
8 -- This function checks to make sure that the ASG does not expire before
9 -- the next payroll run.
10 -----------------------------------------------------------------------------
11 /*
12 FUNCTION ASG_EXPIRES ( p_in_asg_id IN NUMBER,
13 p_in_eff_date IN DATE )
14 RETURN BOOLEAN IS
15
16 l_asg_ed DATE;
17 l_asg_py_id NUMBER;
18 l_py_ed DATE;
19 asg_expires EXCEPTION;
20
21 BEGIN
22
23 BEGIN
24
25 -- Get ASG effective_end_date
26 SELECT effective_end_date,payroll_id
27 INTO l_asg_ed,l_asg_py_id
28 FROM PER_ALL_ASSIGNMENTS_F
29 WHERE assignment_id = p_in_asg_id
30 AND p_in_eff_date BETWEEN
31 effective_start_date
32 AND effective_end_date;
33
34 EXCEPTION WHEN NO_DATA_FOUND THEN
35 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
36 hr_utility.set_message_token('PROCEDURE',
37 'pqp_gb_ad_ee.open_cm_ele_entries');
38 hr_utility.set_message_token('STEP','7');
39 hr_utility.raise_error;
40 END;
41
42 BEGIN
43
44 -- Get the payroll end date
45 SELECT time.end_date
46 INTO l_py_ed
47 FROM per_time_periods time
48 WHERE time.payroll_id = l_asg_py_id
49 AND time.end_date > ( SELECT MAX(effective_date)
50 FROM pay_payroll_actions act
51 WHERE act.payroll_id =l_asg_py_id
52 and act.action_status='C')
53 AND ROWNUM = 1;
54
55 EXCEPTION WHEN NO_DATA_FOUND THEN
56 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
57 hr_utility.set_message_token('PROCEDURE',
58 'pqp_gb_ad_ee.open_cm_ele_entries');
59 hr_utility.set_message_token('STEP','8');
60 hr_utility.raise_error;
61 END;
62
63 IF l_asg_ed < l_py_ed THEN
64 RAISE asg_expires;
65 ELSE
66 RETURN FALSE;
67 END IF;
68
69 EXCEPTION
70
71 WHEN asg_expires THEN
72 hr_utility.raise_error;
73
74 END asg_expires;
75 */
76 -----------------------------------------------------------------------------
77 -- ASG_OVERLAP
78 -- This function returns TRUE if two asg's overlap each other.
79 -- Various conditions are described in detail below.
80 -----------------------------------------------------------------------------
81 FUNCTION ASG_OVERLAP ( p_in_asg_id IN NUMBER,
82 p_in_veh_type IN VARCHAR2,
83 p_in_eff_date IN DATE,
84 p_in_claim_end_date IN DATE)
85 RETURN BOOLEAN IS
86
87 l_bg_id NUMBER;
88 l_mult_asg VARCHAR2(10);
89 l_asg_sd DATE;
90 l_asg_ed DATE;
91 l_asg_count NUMBER;
92 l_person_id NUMBER;
93 asg_overlap EXCEPTION;
94
95 BEGIN
96
97 BEGIN
98
99 SELECT business_group_id
100 ,effective_start_date
101 ,effective_end_date
102 ,person_id
103 INTO l_bg_id
104 ,l_asg_sd
105 ,l_asg_ed
106 ,l_person_id
107 FROM PER_ALL_ASSIGNMENTS_F
108 WHERE assignment_id = p_in_asg_id
109 AND p_in_eff_date
110 BETWEEN effective_start_date AND
111 effective_end_date;
112
113 EXCEPTION
114
115 WHEN NO_DATA_FOUND THEN
116 RAISE;
117
118 END;
119
120 -- Check if there are any ASG's with C and M element entries
121 -- that are overlapping the ASG that is deing deleted.
122 -- An ASG is supposed to be overlapping if there is another
123 -- ASG that is valid after the current ASG and has an C and M
124 -- element entry of the same vehicle type. Check should be for
125 -- all the ASG's after the claim_end_date
126
127 -- Claim Date = 10-JUN
128 -- Vehicle Type = 'E'
129 -- E.g. |
130 -- <----------------------------> Current ASG
131 -- <------------ Overlap
132 -- <------------> Overlap
133 -- <-----------------> Overlap
134 -- <-------------------------------------------Overlap
135 -- <---------> Doesn't Overlap
136 --
137
138 SELECT count(asg.assignment_id)
139 INTO l_asg_count
140 FROM pay_element_links_f pel,
141 pay_element_entries_f pee,
142 pay_element_types_f pet,
143 per_all_assignments_f asg,
144 pay_element_type_extra_info pete,
145 pay_element_entry_values_f peev,
146 pay_input_values_f piv
147 WHERE pet.element_type_id = pel.element_type_id
148 AND pel.element_link_id = pee.element_link_id
149 AND pet.element_type_id = pete.element_type_id
150 AND pee.assignment_id = asg.assignment_id
151 AND peev.element_entry_id = pee.element_entry_id
152 AND peev.input_value_id = piv.input_value_id
153 AND pee.assignment_id <> p_in_asg_id
154 AND asg.person_id = l_person_id
155 AND piv.name IN ('Vehicle Type','Rate Type')
156 AND peev.screen_entry_value = p_in_veh_type
157 AND asg.business_group_id = l_bg_id
158 AND p_in_eff_date BETWEEN
159 pet.effective_start_date AND pet.effective_end_date
160 AND asg.effective_end_date > p_in_claim_end_date
161 AND asg.effective_start_date < l_asg_ed
162 AND p_in_eff_date BETWEEN
163 pel.effective_start_date AND pel.effective_end_date
164 AND p_in_eff_date BETWEEN
165 pee.effective_start_date AND pee.effective_end_date
166 AND p_in_eff_date BETWEEN
167 peev.effective_start_date AND peev.effective_end_date
168 AND p_in_eff_date BETWEEN
169 piv.effective_start_date AND piv.effective_end_date
170 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
171 AND pete.eei_information1 <>'L';
172
173 IF l_asg_count > 0 THEN
174 RAISE ASG_OVERLAP;
175 ELSE
176 RETURN FALSE;
177 END IF;
178
179 EXCEPTION
180
181 WHEN NO_DATA_FOUND THEN
182 RETURN FALSE;
183
184 WHEN ASG_OVERLAP THEN
185 hr_utility.set_message(8303,'PQP_230531_MULT_ASG_MILE_ADJ');
186 hr_utility.raise_error;
187
188 END asg_overlap;
189
190 -----------------------------------------------------------------------------
191 -- OPEN_CM_ELE_ENTRIES
192 -----------------------------------------------------------------------------
193 PROCEDURE open_cm_ele_entries( p_assignment_id_o IN NUMBER
194 ,p_effective_date IN DATE
195 ,p_effective_end_date IN DATE
196 ,p_element_entry_id IN NUMBER
197 ,p_datetrack_mode IN VARCHAR2) IS
198
199 --Checks whether the payroll has been run
200 -- in next tax year for car and mile element
201 CURSOR c_chk_payrun IS
202 select max(ppa.effective_date) effective_date
203 from pay_payroll_actions ppa,
204 pay_assignment_actions paa,
205 per_assignments_f paf
206 WHERE paf.person_id =(SELECT distinct person_id from per_assignments_f
207 where assignment_id =p_assignment_id_o)
208 AND paf.assignment_id=paa.assignment_id
209 and paa.payroll_action_id=ppa.payroll_action_id
210 AND ppa.action_type in ('R','Q','V');
211
212 CURSOR c_eentry_efdate IS
213 SELECT effective_start_date,
214 effective_end_date
215 FROM pay_element_entries_f
216 WHERE element_entry_id=p_element_entry_id
217 AND p_effective_date BETWEEN
218 effective_start_date
219 AND effective_end_date;
220 l_eentry_efdate c_eentry_efdate%ROWTYPE;
221 l_chk_payrun c_chk_payrun%ROWTYPE;
222 -- Cursor to fetch all car and mileage element entries
223 -- for the current and last fiscal years
224
225 CURSOR ele_cur (p_ele_start_date IN DATE,
226 p_ele_end_date IN DATE)is
227 SELECT pee.element_entry_id,
228 pee.effective_end_date,
229 pet.element_type_id,
230 pel.element_link_id,
231 pel.effective_end_date link_end_date,
232 pee.effective_start_date
233 FROM pay_element_links_f pel,
234 pay_element_entries_f pee,
235 pay_element_types_f pet,
236 pay_element_type_extra_info pete
237 WHERE pet.element_type_id = pel.element_type_id
238 AND pel.element_link_id = pee.element_link_id
239 AND pet.element_type_id = pete.element_type_id
240 AND p_effective_date BETWEEN
241 pel.effective_start_date AND pel.effective_end_date
242 AND p_effective_date BETWEEN
243 pet.effective_start_date AND pet.effective_end_date
244 AND pee.assignment_id = p_assignment_id_o
245 -- Open only those entries entered after the current entry.
246 AND pee.element_entry_id > p_element_entry_id
247 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
248 AND pete.eei_information1 <>'L'
249 AND pee.effective_start_date >= p_ele_start_date
250 AND (pee.effective_end_date <= p_ele_end_date
251 OR pee.effective_end_date = hr_general.end_of_time)
252 ORDER BY 1,2 desc;
253
254 -- Cursor to check for Car and Mileage element entries
255
256 CURSOR chk_cur IS
257 SELECT 'x'
258 FROM pay_element_links_f pel,
259 pay_element_entries_f pee,
260 pay_element_types_f pet,
261 pay_element_type_extra_info pete
262 WHERE pet.element_type_id = pel.element_type_id
263 AND pel.element_link_id = pee.element_link_id
264 AND pet.element_type_id = pete.element_type_id
265 AND pee.assignment_id = p_assignment_id_o
266 AND pee.element_entry_id = p_element_entry_id
267 AND p_effective_date BETWEEN
268 pet.effective_start_date AND pet.effective_end_date
269 AND p_effective_date BETWEEN
270 pel.effective_start_date AND pel.effective_end_date
271 AND p_effective_date BETWEEN
272 pee.effective_start_date AND pee.effective_end_date
273 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
274 AND pete.eei_information1 <>'L';
275
276 -- Cursor to fetch screen value with check on session_date
277 -- for the table pay_element_entry_values_f
278
279 CURSOR scr_val_cur( p_name IN VARCHAR2
280 ,p_in_element_entry_id IN NUMBER
281 ) IS
282 SELECT peev.screen_entry_value
283 FROM pay_element_links_f pel,
284 pay_element_entries_f pee,
285 pay_element_types_f pet,
286 pay_element_type_extra_info pete,
287 pay_element_entry_values_f peev,
288 pay_input_values_f piv
289 WHERE pet.element_type_id = pel.element_type_id
290 AND pel.element_link_id = pee.element_link_id
291 AND pet.element_type_id = pete.element_type_id
292 AND peev.element_entry_id = pee.element_entry_id
293 AND peev.input_value_id = piv.input_value_id
294 AND piv.name = p_name
295 AND pee.assignment_id = p_assignment_id_o
296 AND pee.element_entry_id = p_in_element_entry_id
297 AND p_effective_date BETWEEN
298 pet.effective_start_date AND pet.effective_end_date
299 AND p_effective_date BETWEEN
300 pel.effective_start_date AND pel.effective_end_date
301 AND p_effective_date BETWEEN
302 pee.effective_start_date AND pee.effective_end_date
303 AND p_effective_date BETWEEN
304 peev.effective_start_date AND peev.effective_end_date
305 AND p_effective_date BETWEEN
306 piv.effective_start_date AND piv.effective_end_date
307 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
308 AND pete.eei_information1 <>'L';
309
310 -- Cursor to fetch screen for vehicle type with check on session_date
311 -- for the table pay_element_entry_values_f
312
313 CURSOR vehicle_type_cur(p_in_element_entry_id IN NUMBER
314 ) IS
315 SELECT peev.screen_entry_value
316 FROM pay_element_links_f pel,
317 pay_element_entries_f pee,
318 pay_element_types_f pet,
319 pay_element_type_extra_info pete,
320 pay_element_entry_values_f peev,
321 pay_input_values_f piv
322 WHERE pet.element_type_id = pel.element_type_id
323 AND pel.element_link_id = pee.element_link_id
324 AND pet.element_type_id = pete.element_type_id
325 AND peev.element_entry_id = pee.element_entry_id
326 AND peev.input_value_id = piv.input_value_id
327 AND piv.name IN ('Vehicle Type','Rate Type')
328 AND pee.assignment_id = p_assignment_id_o
329 AND pee.element_entry_id = p_in_element_entry_id
330 AND p_effective_date BETWEEN
331 pet.effective_start_date AND pet.effective_end_date
332 AND p_effective_date BETWEEN
333 pel.effective_start_date AND pel.effective_end_date
334 AND p_effective_date BETWEEN
335 pee.effective_start_date AND pee.effective_end_date
336 AND p_effective_date BETWEEN
337 peev.effective_start_date AND peev.effective_end_date
338 AND p_effective_date BETWEEN
339 piv.effective_start_date AND piv.effective_end_date
340 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
341 AND pete.eei_information1 <>'L';
342
343
344 -- Cursor to fetch screen value WITHOUT CHECK ON SESSION_DATE
345 -- for the table pay_element_entry_values_f
346 -- Values fetched are based on effective_end_date
347 -- This is to fetch the current valid entry values
348
349 CURSOR scr_val_cur1( p_name IN VARCHAR2
350 ,p_in_element_entry_id IN NUMBER
351 ,p_in_end_date IN DATE) IS
352 SELECT peev.screen_entry_value
353 FROM pay_element_links_f pel,
354 pay_element_entries_f pee,
355 pay_element_types_f pet,
356 pay_element_type_extra_info pete,
357 pay_element_entry_values_f peev,
358 pay_input_values_f piv
359 WHERE pet.element_type_id = pel.element_type_id
360 AND pel.element_link_id = pee.element_link_id
361 AND pet.element_type_id = pete.element_type_id
362 AND peev.element_entry_id = pee.element_entry_id
363 AND peev.input_value_id = piv.input_value_id
364 AND piv.name = p_name
365 AND pee.assignment_id = p_assignment_id_o
366 AND pee.element_entry_id = p_in_element_entry_id
367 AND p_effective_date BETWEEN
368 pet.effective_start_date AND pet.effective_end_date
369 AND p_effective_date BETWEEN
370 pel.effective_start_date AND pel.effective_end_date
371 -- Code commented out to fix bug .
372 -- If the session date is before the EE start date
373 -- the hook failed. Go on effective end date instead.
374 -- AND p_effective_date BETWEEN
375 -- pee.effective_start_date AND pee.effective_end_date
376 AND p_effective_date BETWEEN
377 piv.effective_start_date AND piv.effective_end_date
378 AND peev.effective_end_date = p_in_end_date
379 AND pee.effective_end_date = p_in_end_date
380 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
381 AND pete.eei_information1 <>'L';
382
383 CURSOR vehicle_type_cur1(p_in_element_entry_id IN NUMBER
384 ,p_in_end_date IN DATE) IS
385 SELECT peev.screen_entry_value
386 FROM pay_element_links_f pel,
387 pay_element_entries_f pee,
388 pay_element_types_f pet,
389 pay_element_type_extra_info pete,
390 pay_element_entry_values_f peev,
391 pay_input_values_f piv
392 WHERE pet.element_type_id = pel.element_type_id
393 AND pel.element_link_id = pee.element_link_id
394 AND pet.element_type_id = pete.element_type_id
395 AND peev.element_entry_id = pee.element_entry_id
396 AND peev.input_value_id = piv.input_value_id
397 AND piv.name IN ('Vehicle Type','Rate Type')
398 AND pee.assignment_id = p_assignment_id_o
399 AND pee.element_entry_id = p_in_element_entry_id
400 AND p_effective_date BETWEEN
401 pet.effective_start_date AND pet.effective_end_date
402 AND p_effective_date BETWEEN
403 pel.effective_start_date AND pel.effective_end_date
404 -- Code commented out to fix bug .
405 -- If the session date is before the EE start date
406 -- the hook failed. Go on effective end date instead.
407 -- AND p_effective_date BETWEEN
408 -- pee.effective_start_date AND pee.effective_end_date
409 AND p_effective_date BETWEEN
410 piv.effective_start_date AND piv.effective_end_date
411 AND peev.effective_end_date = p_in_end_date
412 AND pee.effective_end_date = p_in_end_date
413 AND pete.information_type = 'PQP_VEHICLE_MILEAGE_INFO'
414 AND pete.eei_information1 <>'L';
415
416
417 l_dummy VARCHAR2(1);
418 l_claim_end_date DATE;
419 l_car_type VARCHAR2(10);
420 l_start_date DATE;
421 l_end_date DATE;
422 l_ele_start_date DATE;
423 l_ele_end_date DATE;
424 l_last_ee_id_tmp NUMBER := -9999;
425 l_scr_val PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value%TYPE;
426 l_ee_scr_val PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value%TYPE;
427 l_ee_vehicle_type PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value%TYPE;
428 l_paye_taxable PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value%TYPE;
429 l_current_year VARCHAR2(5);
430 l_ee_claim_end_date DATE;
431 l_asg_eff_ed DATE;
432 l_chk_start_date DATE;
433 l_ee_effdate DATE;
434 l_chk_effdate DATE;
435 BEGIN
436 --
437 -- Added for GSI Bug 5472781
438 --
439 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
440 --
441 -- Check if the element entry is for a Car and Mileage element.
442 -- The entries should be opened only for these elements.
443
444 OPEN chk_cur;
445 FETCH chk_cur INTO l_dummy;
446 IF chk_cur%FOUND THEN
447
448 -- Check if the user is end dating the element entry.
449 -- Open the entries only if user is NOT END DATING it.
450 IF p_datetrack_mode <> 'DELETE' THEN -- DT_MODE_CHK
451 -- Check if asg expires
452 -- Check not required as you cannot perform a NEXT / ALL
453 -- if the ASG is already end dated
454 --IF NOT asg_expires (p_assignment_id_o,p_effective_date) THEN
455
456 -- Get values from the screen for Claim End Date and Vehicle Type
457 -- OR Rate Type for the session date
458 OPEN c_chk_payrun;
459 LOOP
460 FETCH c_chk_payrun INTO l_chk_payrun;
461 EXIT WHEN c_chk_payrun%NOTFOUND;
462 l_chk_effdate:=l_chk_payrun.effective_date;
463
464 END LOOP;
465 CLOSE c_chk_payrun;
466
467 OPEN c_eentry_efdate;
468 LOOP
469 FETCH c_eentry_efdate INTO l_eentry_efdate;
470 EXIT WHEN c_eentry_efdate%NOTFOUND;
471 l_ee_effdate:= l_eentry_efdate.effective_start_date;
472 END LOOP;
473 CLOSE c_eentry_efdate ;
474
475 IF l_ee_effdate >
476 to_date('04/05/'||to_char(l_ee_effdate ,'YYYY'),'MM/DD/YYYY')
477 THEN
478 l_chk_start_date:=TO_DATE('04/05/'||(to_char(l_ee_effdate ,'YYYY')+1),'MM/DD/YYYY');
479 ELSE
480
481 l_chk_start_date:=TO_DATE('04/05/'||(to_char(l_ee_effdate ,'YYYY')),'MM/DD/YYYY');
482 END IF;
483 IF l_chk_effdate > l_chk_start_date THEN
484
485 hr_utility.set_message(8303, 'PQP_230575_FUTURE_RUN_EXISTS');
486 hr_utility.raise_error;
487 END IF;
488
489 BEGIN
490 OPEN scr_val_cur ('Claim End Date',p_element_entry_id);
491 FETCH scr_val_cur INTO l_scr_val;
492 CLOSE scr_val_cur;
493 l_claim_end_date := TO_DATE(SUBSTR(l_scr_val,1,11),'RRRR/MM/DD');
494 l_claim_end_date := TRUNC(l_claim_end_date);
495 l_current_year := TO_CHAR(l_claim_end_date,'RRRR');
496
497 EXCEPTION WHEN NO_DATA_FOUND THEN
498 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
499 hr_utility.set_message_token('PROCEDURE',
500 'pqp_gb_ad_ee.open_cm_ele_entries');
501 hr_utility.set_message_token('STEP','1');
502 hr_utility.raise_error;
503 END;
504
505 BEGIN
506 OPEN vehicle_type_cur(p_element_entry_id);
507 FETCH vehicle_type_cur INTO l_car_type;
508 CLOSE vehicle_type_cur;
509
510 EXCEPTION WHEN NO_DATA_FOUND THEN
511 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
512 hr_utility.set_message_token('PROCEDURE',
513 'pqp_gb_ad_ee.open_cm_ele_entries');
514 hr_utility.set_message_token('STEP','2');
515 hr_utility.raise_error;
516 END;
517
518 -- Check for overlapping ASG's
519 IF NOT asg_overlap(p_assignment_id_o
520 ,l_car_type
521 ,p_effective_date
522 ,l_claim_end_date) THEN
523
524 -- Determine the begin and end of the
525 -- fiscal year from the claim end date.
526 -- Fiscal year for GB is 6/April/XXXX to 5/April/XXXX
527
528 -- Current Fiscal_year
529 IF l_claim_end_date >= TO_DATE('06/04/'||l_current_year,'DD/MM/RRRR') THEN
530 l_start_date := TO_DATE('06/04/'||l_current_year,'DD/MM/RRRR');
531 l_end_date := TO_DATE('05/04/'||TO_CHAR(
532 TO_NUMBER(l_current_year)+1),'DD/MM/RRRR');
533 -- Previous Fiscal_year
534 ELSIF l_claim_end_date < TO_DATE('06/04/'||l_current_year,'DD/MM/RRRR') THEN
535 l_end_date := TO_DATE('05/04/'||l_current_year,'DD/MM/RRRR');
536 l_start_date := TO_DATE('06/04/'||TO_CHAR(
537 TO_NUMBER(l_current_year)-1),'DD/MM/RRRR');
538 END IF;
539
540 -- Determine the start and end dates to fetch all element
541 -- entries for the current and last fiscal years.
542 l_ele_start_date := TO_DATE('06/04/'||TO_CHAR(TO_NUMBER(l_current_year)-1),'DD/MM/RRRR');
543 l_ele_end_date := TO_DATE('05/04/'||TO_CHAR(TO_NUMBER(l_current_year)+1),'DD/MM/RRRR');
544
545 -- Update the end date and open the element entry.
546 -- Only the entries after the entry being deleted
547 -- should be opened and the claim end date should
548 -- fall between the fiscal year.
549
550 FOR temp_rec IN ele_cur(l_ele_start_date,l_ele_end_date)
551 LOOP
552 -- Entry is already open no update required.
553 IF TO_CHAR(temp_rec.effective_end_date,'DD/MM/RRRR')
554 = '31/12/4712' THEN
555 l_last_ee_id_tmp := temp_rec.element_entry_id;
556 ELSIF TO_CHAR(temp_rec.effective_end_date,'DD/MM/RRRR')
557 <> '31/12/4712' THEN
558 IF temp_rec.element_entry_id <> l_last_ee_id_tmp THEN
559
560 -- Get Vehicle Type for the Element Entry.
561 BEGIN
562 OPEN vehicle_type_cur1 ( temp_rec.element_entry_id
563 ,temp_rec.effective_end_date);
564 FETCH vehicle_type_cur1 INTO l_ee_vehicle_type;
565 CLOSE vehicle_type_cur1;
566
567 EXCEPTION WHEN NO_DATA_FOUND THEN
568 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
569 hr_utility.set_message_token('PROCEDURE',
570 'pqp_gb_ad_ee.open_cm_ele_entries');
571 hr_utility.set_message_token('STEP','3');
572 hr_utility.raise_error;
573 END;
574
575 -- Code commented out to open both Essential and Casual vehicles OR
576 -- Primary and Secondary vehicles.
577 -- IF l_ee_vehicle_type = l_car_type THEN
578 IF l_car_type IN ( 'C','E') and l_ee_vehicle_type IN ( 'C','E') OR
579 l_car_type IN ( 'P','S') and l_ee_vehicle_type IN ( 'P','S') THEN
580
581 -- Get PAYE Taxable value for the Element Entry.
582 /* BEGIN
583 OPEN scr_val_cur1 ('PAYE Taxable'
584 ,temp_rec.element_entry_id
585 ,temp_rec.effective_end_date);
586 FETCH scr_val_cur1 INTO l_paye_taxable;
587 CLOSE scr_val_cur1;
588
589 EXCEPTION WHEN NO_DATA_FOUND THEN
590 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
591 hr_utility.set_message_token('PROCEDURE',
592 'pqp_gb_ad_ee.open_cm_ele_entries');
593 hr_utility.set_message_token('STEP','4');
594 hr_utility.raise_error;
595 END;*/
596
597
598 -- IF l_paye_taxable <> 'Y' THEN
599
600 -- Get Claim End Date for the Element Entry.
601 BEGIN
602 OPEN scr_val_cur1 ('Claim End Date'
603 ,temp_rec.element_entry_id
604 ,temp_rec.effective_end_date);
605 FETCH scr_val_cur1 INTO l_ee_scr_val;
606 CLOSE scr_val_cur1;
607
608 l_ee_claim_end_date := TO_DATE(SUBSTR
609 (l_ee_scr_val,1,11),'RRRR/MM/DD');
610 l_ee_claim_end_date := TRUNC(l_ee_claim_end_date);
611
612 EXCEPTION WHEN NO_DATA_FOUND THEN
613 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
614 hr_utility.set_message_token('PROCEDURE',
615 'pqp_gb_ad_ee.open_cm_ele_entries');
616 hr_utility.set_message_token('STEP','5');
617 hr_utility.raise_error;
618 END;
619
620
621 -- Check if the claim end date falls in the fiscal yr.
622 -- IF l_ee_claim_end_date >= l_start_date
623 -- AND l_ee_claim_end_date <= l_end_date THEN
624
625 BEGIN
626
627 -- Get ASG effective_end_date
628 SELECT effective_end_date
629 INTO l_asg_eff_ed
630 FROM PER_ALL_ASSIGNMENTS_F
631 WHERE assignment_id = p_assignment_id_o
632 AND p_effective_date BETWEEN
633 effective_start_date
634 AND effective_end_date;
635
636 EXCEPTION WHEN NO_DATA_FOUND THEN
637 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
638 hr_utility.set_message_token('PROCEDURE',
639 'pqp_gb_ad_ee.open_cm_ele_entries');
640 hr_utility.set_message_token('STEP','6');
641 hr_utility.raise_error;
642 END;
643
644 -- Cannot call the API as it can go in an infinite loop.
645 -- Manually update the effective_end_date to
646 -- the effective_end_date on the Element Link
647 UPDATE pay_element_entries_f
648 SET effective_end_date =
649 LEAST(temp_rec.link_end_date,l_asg_eff_ed)
650 WHERE element_entry_id = temp_rec.element_entry_id
651 AND effective_end_date = temp_rec.effective_end_date;
652
653 -- Call API to open entry values
654 hr_entry.del_3p_entry_values
655 (p_assignment_id_o, --asgid
656 temp_rec.element_entry_id, --ee_id,
657 temp_rec.element_type_id, --ele_type_id,
658 temp_rec.element_link_id, --ele_link_id,
659 'E', --entry_type,
660 'R', --processing_type,
661 'F', --creator_type,
662 NULL, --creator_id,
663 'DELETE_NEXT_CHANGE', --dt_delete_mode,
664 --p_effective_date, --p_session_date,
665 -- Open the entry values from the start_date of
666 -- the element entry.
667 temp_rec.effective_start_date, --p_session_date,
668 NULL, --validation_start_date,
669 NULL); --validation_end_date);
670
671 l_last_ee_id_tmp := temp_rec.element_entry_id;
672
673 -- END IF; -- Check if element entry is in the fiscal yr.
674 -- END IF; -- Check for paye_taxable
675 END IF; -- Check if EE Vehicle type matches l_car_type
676 END IF; -- Check for last ee id
677 END IF; -- Check for open entries
678 END LOOP; -- Loop thru all C and M element entries
679 END IF; -- Check for overlapping ASG's
680 END IF; -- Check for DateTrack delete mode
681 CLOSE chk_cur;
682 ELSE
683 -- No Car and Mileage entries found . No action required.
684 CLOSE chk_cur;
685 END IF; -- Check if the entries are for C and M elements
686 END IF; -- hr_utility.chk_product_install('Oracle Human Resources', 'GB')
687 /*EXCEPTION WHEN OTHERS THEN
688 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
689 hr_utility.set_message_token('PROCEDURE','pqp_gb_ad_ee.open_cm_ele_entries');
690 hr_utility.set_message_token('STEP','7');
691 hr_utility.raise_error;
692 */
693
694 END;
695
696 PROCEDURE create_term_ele_entries ( p_assignment_id IN NUMBER
697 ,p_effective_date IN DATE
698 ,p_effective_start_date IN DATE
699 ,p_effective_end_date IN DATE
700 ,p_element_entry_id IN NUMBER
701 ,p_element_link_id IN NUMBER
702 ,p_element_type_id IN NUMBER
703 ) IS
704
705
706 l_proc varchar2(72) := g_package||'create_term_ele_entries';
707 CURSOR c_ele_type (cp_element_type_id NUMBER)
708 IS
709 SELECT pete.eei_information1 ele_type
710 FROM pay_element_type_extra_info pete
711 WHERE pete.information_type='PQP_VEHICLE_MILEAGE_INFO'
712 AND pete.element_type_id= cp_element_type_id;
713
714
715
716 CURSOR c_term
717 IS
718 SELECT 'Y' terminated
719 FROM per_periods_of_service pds
720 ,per_assignments_f pas
721 WHERE NVL(pds.final_process_date,hr_api.g_eot) >= p_effective_date
722 AND pds.last_standard_process_date <= p_effective_date
723 AND pds.period_of_service_id = pas.period_of_service_id
724 AND p_effective_date
725 BETWEEN pas.effective_start_date
726 AND pas.effective_end_date
727 AND pas.primary_flag = 'Y'
728 AND pas.assignment_id =p_assignment_id;
729
730 CURSOR c_entry_exist (cp_link_id NUMBER)
731 IS
732 SELECT 'Y'
733 FROM pay_element_entries_f pef
734 WHERE pef.assignment_id = p_assignment_id
735 AND pef.element_link_id=cp_link_id
736 AND p_effective_date
737 BETWEEN pef.effective_start_date
738 AND pef.effective_end_date;
739
740 CURSOR c_element_details
741 IS
742 SELECT element.element_type_id , link.element_link_id,element.business_group_id
743 FROM pay_element_types_f_tl elementtl,
744 pay_element_types_f element,
745 pay_element_links_f link,
746 per_all_assignments_f asgt ,
747 per_periods_of_service service_period
748 WHERE
749 --element.element_type_id = elementtl.element_type_id
750 -- AND elementtl.language = USERENV('LANG')
751 --AND
752 asgt.business_group_id = link.business_group_id
753 AND asgt.business_group_id =service_period.business_group_id
754 AND element.element_type_id = link.element_type_id
755 AND service_period.period_of_service_id = asgt.period_of_service_id
756 AND p_effective_date
757 between element.effective_start_date and element.effective_end_date
758 AND p_effective_date
759 between asgt.effective_start_date and asgt.effective_end_date
760 AND p_effective_date
761 between link.effective_start_date and link.effective_end_date
762 AND element.indirect_only_flag = 'N'
763 AND ((link.payroll_id is NOT NULL AND
764 link.payroll_id = asgt.payroll_id)
765 OR (link.link_to_all_payrolls_flag = 'Y'
766 AND asgt.payroll_id IS NOT NULL)
767 OR (link.payroll_id IS NULL
768 AND link.link_to_all_payrolls_flag = 'N'))
769 AND (link.organization_id = asgt.organization_id
770 OR link.organization_id IS NULL)
771 AND (link.position_id = asgt.position_id
772 OR link.position_id IS NULL)
773 AND (link.job_id = asgt.job_id OR link.job_id IS NULL)
774 AND (link.grade_id = asgt.grade_id OR link.grade_id IS NULL)
775 AND (link.location_id = asgt.location_id
776 OR link.location_id IS NULL)
777 AND (link.pay_basis_id = asgt.pay_basis_id
778 OR link.pay_basis_id IS NULL)
779 AND (link.employment_category = asgt.employment_category
780 OR link.employment_category IS NULL)
781 AND (link.people_group_id IS NULL OR EXISTS
782 ( SELECT 1 FROM pay_assignment_link_usages_f usage
783 WHERE usage.assignment_id = asgt.assignment_id
784 AND usage.element_link_id = link.element_link_id
785 AND p_effective_date
786 BETWEEN usage.effective_start_date
787 AND usage.effective_end_date))
788 AND (service_period.actual_termination_date
789 IS NULL OR (service_period.actual_termination_date IS NOT NULL
790 AND p_effective_date <=
791 DECODE(element.post_termination_rule, 'L',
792 service_period.last_standard_process_date, 'F',
793 NVL(service_period.final_process_date,hr_api.g_eot),
794 service_period.actual_termination_date) ))
795 AND asgt.assignment_id=p_assignment_id
796 -- AND asgt.business_group_id=2899
797 AND element.element_name='Recurring Entry Processor for Terminated EE'
798 ORDER BY element.effective_start_date DESC;
799
800 l_ele_type VARCHAR2(30) ;
801 l_term VARCHAR2(2);
802 l_exist VARCHAR2(1);
803 l_element_details c_element_details%ROWTYPE;
804 l_effective_start_date DATE;
805 l_effective_end_date DATE;
806 l_element_entry_id NUMBER;
807 l_object_version_number NUMBER;
808 l_create_warning BOOLEAN;
809
810 BEGIN
811 --
812 -- Added for GSI Bug 5472781
813 --
814 IF hr_utility.chk_product_install('Oracle Human Resources', 'GB') THEN
815 --
816 l_ele_type :=NULL;
817 l_term :='N';
818 l_exist:='N';
819
820 IF g_debug then
821 hr_utility.set_location('Entering:'|| l_proc, 10);
822 END IF;
823 --Check if the element is Car and Mileage element
824 OPEN c_ele_type (p_element_type_id);
825 FETCH c_ele_type INTO l_ele_type;
826 CLOSE c_ele_type;
827
828 IF g_debug then
829 hr_utility.set_location('Leaving c_ele_type :'|| l_proc, 20);
830 END IF;
831 IF l_ele_type IS NOT NULL THEN
832 --Check if employee is terminated.
833 OPEN c_term;
834 FETCH c_term INTO l_term;
835 CLOSE c_term;
836
837 IF g_debug then
838 hr_utility.set_location('Leaving c_term :'|| l_proc, 30);
839 END IF;
840 IF l_term ='Y' THEN
841 --Get element link info
842 IF g_debug then
843 hr_utility.set_location('Terminated :'|| l_proc, 50);
844 END IF;
845
846 OPEN c_element_details;
847 FETCH c_element_details INTO l_element_details;
848 CLOSE c_element_details;
849
850 IF g_debug then
851 hr_utility.set_location('Link :'||l_element_details.element_link_id
852 || l_proc, 40);
853 END IF;
854
855 ---Check if the entry already esists
856 OPEN c_entry_exist(l_element_details.element_link_id);
857 FETCH c_entry_exist INTO l_exist;
858 CLOSE c_entry_exist;
859 IF l_exist <> 'Y' THEN
860 IF g_debug then
861 hr_utility.set_location('Entry Exists :' || l_proc, 50);
862 END IF;
863 BEGIN
864 --Create entry
865 pay_element_entry_api.create_element_entry
866 (p_effective_date =>p_effective_date
867 ,p_business_group_id =>l_element_details.business_group_id
868 ,p_assignment_id =>p_assignment_id
869 ,p_element_link_id =>l_element_details.element_link_id
870 ,p_entry_type =>'E'
871 ,p_effective_start_date =>l_effective_start_date
872 ,p_effective_end_date =>l_effective_end_date
873 ,p_element_entry_id =>l_element_entry_id
874 ,p_object_version_number =>l_object_version_number
875 ,p_create_warning =>l_create_warning
876 );
877
878 EXCEPTION
879 --------
880 WHEN OTHERS THEN
881 fnd_message.set_name('PQP','PQP_230203_INVALID_LINK');
882 Raise;
883 END;
884 END IF;
885 END IF;
886
887 END IF;
888
889 IF g_debug then
890 hr_utility.set_location('Leaving :' || l_proc, 60);
891 END IF;
892 END IF; -- hr_utility.chk_product_install('Oracle Human Resources', 'GB')
893 END create_term_ele_entries;
894
895 --For bug 7013325
896 -----------------------------------------------------------------------------
897 -- update_ass_dff_col
898 -----------------------------------------------------------------------------
899 PROCEDURE UPDATE_PSI_ASS_DFF_COL
900 (
901 p_effective_start_date date,
902 p_element_entry_id number,
903 p_assignment_id number,
904 p_element_type_id Number
905 )
906 IS
907
908 --Cursor to fetch current Context value, Employment category and Business group for Employee
909 Cursor csr_get_curr_asg_dtls
910 IS
911 SELECT ass_attribute_category,
912 employment_category,
913 business_group_id,
914 effective_start_date,
915 effective_end_date,
916 object_version_number,
917 soft_coding_keyflex_id,
918 cagr_grade_def_id,
919 ass_attribute1,
920 ass_attribute2,
921 ass_attribute3,
922 ass_attribute4,
923 ass_attribute5,
924 ass_attribute6,
925 ass_attribute7,
926 ass_attribute8,
927 ass_attribute9,
928 ass_attribute10,
929 ass_attribute11,
930 ass_attribute12,
931 ass_attribute13,
932 ass_attribute14,
933 ass_attribute15,
934 ass_attribute16,
935 ass_attribute17,
936 ass_attribute18,
937 ass_attribute19,
938 ass_attribute20,
939 ass_attribute21,
940 ass_attribute22,
941 ass_attribute23,
942 ass_attribute24,
943 ass_attribute25,
944 ass_attribute26,
945 ass_attribute27,
946 ass_attribute28,
947 ass_attribute29,
948 ass_attribute30
949 FROM per_all_assignments_f
950 WHERE assignment_id = p_assignment_id
951 AND p_effective_start_date between effective_start_date and effective_end_date;
952 --
953 --Cursor to fetch Nuvos and Partnership elements
954 Cursor csr_get_nuv_part_elements(c_business_group_id number)
955 IS
956 SELECT pcv_information1 --element_type_id
957 FROM pqp_configuration_values
958 WHERE pcv_information_category = 'PQP_GB_PENSERV_SCHEME_MAP_INFO'
959 AND business_group_id =c_business_group_id
960 AND pcv_information2 in ('NUVOS','PARTNER');
961 --
962 --Cursor to fetch context and dff segment mapped for penserver on config page
963 --For bug 7202378: Cursor Modified
964 Cursor csr_get_mapped_context(c_business_group_id number)
965 IS
966 SELECT pcv_information1, --penserver_eligibility_context
967 pcv_information2, --mapped_segment
968 pcv_information3 --mapped_dff_segment
969 FROM pqp_configuration_values
970 WHERE pcv_information_category='PQP_GB_PENSERVER_ELIGBLTY_CONF'
971 AND business_group_id = c_business_group_id;
972 --
973 --Cursor to fetch Employment categories mapped to Casual for penserver
974 Cursor csr_get_casual_emp_cate(c_business_group_id number)
975 IS
976 SELECT pcv_information1 --mapped_casual_emp_categories
977 FROM pqp_configuration_values
978 WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
979 AND business_group_id = c_business_group_id
980 AND pcv_information2 = 'CASUAL';
981 --
982 --Cursor to fetch Employment categories mapped to Fixed and Regular for penserver
983 Cursor csr_get_non_casual_emp_cate(c_business_group_id number)
984 IS
985 SELECT pcv_information1 --mapped_non_casual_emp_cate
986 FROM pqp_configuration_values
987 WHERE pcv_information_category='PQP_GB_PENSERVER_EMPLYMT_TYPE'
988 AND business_group_id = c_business_group_id
989 AND pcv_information2 in ('FIXED','REGULAR');
990 --
991 --For bug 7202378:Second Change: Cursor commented out
992 /* --Cursor to fetch Emp_Cate and Context for employee prior to current date
993 Cursor csr_get_prior_asg_dtls
994 IS
995 SELECT employment_category,
996 ass_attribute_category
997 FROM per_all_assignments_f
998 WHERE assignment_id = p_assignment_id
999 AND effective_start_date < p_effective_start_date;
1000 --
1001 */
1002
1003 --Cursor to fetch date when employee becomes Casual
1004 CURSOR csr_get_casual_asg_start_dt(c_employment_category VARCHAR2)
1005 IS
1006 SELECT MIN(effective_start_date)
1007 FROM per_all_assignments_f
1008 WHERE assignment_id = p_assignment_id
1009 AND employment_category = c_employment_category;
1010 --
1011 --Cursor to fetch element type id's for Employee since he is Casual
1012 CURSOR csr_get_ele_type_since_casual(c_asg_start_date DATE)
1013 IS
1014 SELECT element_type_id
1015 FROM pay_element_entries_f
1016 WHERE assignment_id = p_assignment_id
1017 AND effective_start_date BETWEEN c_asg_start_date AND (p_effective_start_date-1)
1018 ORDER BY effective_start_date;
1019
1020 TYPE get_dff_val_ref_csr_typ IS REF CURSOR;
1021 c_get_dff_val get_dff_val_ref_csr_typ;
1022
1023 --For bug 7202378: Added new ref cursor
1024 TYPE get_segment_val_ref_csr_typ IS REF CURSOR;
1025 c_get_segment_val get_segment_val_ref_csr_typ;
1026
1027 --For bug 7202378:Second Change: Added new ref cursor
1028 TYPE get_prior_asg_dtls_ref_csr_typ IS REF CURSOR;
1029 c_get_prior_asg_dtls get_prior_asg_dtls_ref_csr_typ;
1030 --
1031 --Declare Varables
1032 l_rec_curr_asg_dtls csr_get_curr_asg_dtls%ROWTYPE;
1033 l_nuv_part_flag VARCHAR2(10):= 'N';
1034 l_rec_get_mapped_context csr_get_mapped_context%ROWTYPE;
1035 l_emp_is_casual_flag VARCHAR2(10):= 'N';
1036 l_emp_prior_non_casual_flag VARCHAR2(10):= 'N';
1037 l_get_casual_asg_start_dt DATE;
1038 l_prior_nuv_part_flag VARCHAR2(10):= 'N';
1039 l_query VARCHAR2(1000);
1040 l_value VARCHAR2(50);
1041 l_call_mode VARCHAR2(30);
1042
1043 --For bug 7202378
1044 l_penserv_emp VARCHAR2(10):= 'N';
1045 l_segment_val_query VARCHAR2(1000);
1046 l_segment_value VARCHAR2(50);
1047
1048 --For bug 7202378:Second Change
1049 l_prior_asg_dtls_query VARCHAR2(1000);
1050 l_prior_emp_cate per_all_assignments_f.employment_category%TYPE;
1051 l_prior_ass_att_cate per_all_assignments_f.ass_attribute_category%TYPE;
1052 l_prior_ass_att_xx per_all_assignments_f.ass_attribute10%TYPE;
1053 l_gde_context_flag VARCHAR2(10):= 'N';
1054
1055 --variables for API call(out parameters)
1056 l_object_version_number number;
1057 l_cagr_grade_def_id number;
1058 l_cagr_concatenated_segments varchar2(2000);
1059 l_concatenated_segments varchar2(2000);
1060 l_soft_coding_keyflex_id number;
1061 l_comment_id number;
1062 l_effective_start_date date;
1063 l_effective_end_date date;
1064 l_no_managers_warning boolean;
1065 l_other_manager_warning boolean;
1066 l_hourly_salaried_warning boolean;
1067
1068 --
1069
1070 BEGIN
1071
1072 hr_utility.set_location('Entering procedure pqp_gb_ad_ee.update_ass_dff_col',10);
1073 hr_utility.set_location('p_effective_start_date :' ||p_effective_start_date,10);
1074 hr_utility.set_location('p_element_entry_id :' ||p_element_entry_id,10);
1075 hr_utility.set_location('p_assignment_id :' ||p_assignment_id,10);
1076 hr_utility.set_location('p_element_type_id :' ||p_element_type_id,10);
1077
1078 --Get current assignment details
1079 OPEN csr_get_curr_asg_dtls;
1080 FETCH csr_get_curr_asg_dtls INTO l_rec_curr_asg_dtls;
1081 CLOSE csr_get_curr_asg_dtls;
1082
1083 hr_utility.set_location('Emp Context :' ||l_rec_curr_asg_dtls.ass_attribute_category,11);
1084 hr_utility.set_location('Emp Category :' ||l_rec_curr_asg_dtls.employment_category,11);
1085 hr_utility.set_location('Business Group :' ||l_rec_curr_asg_dtls.business_group_id,11);
1086 hr_utility.set_location('Effective Start date :' ||l_rec_curr_asg_dtls.effective_start_date,11);
1087 hr_utility.set_location('Effective End date :' ||l_rec_curr_asg_dtls.effective_end_date,11);
1088
1089 --Check if element is Nuvos or Partnership
1090 For rec_get_nuv_part_elements in csr_get_nuv_part_elements(l_rec_curr_asg_dtls.business_group_id)
1091 Loop
1092 IF rec_get_nuv_part_elements.pcv_information1 = p_element_type_id
1093 THEN
1094 l_nuv_part_flag := 'Y';
1095 hr_utility.set_location('Element is a Nuvos or Partnership element',12);
1096 hr_utility.set_location('Element_type_id :'
1097 ||rec_get_nuv_part_elements.pcv_information1,12);
1098 Exit;
1099 End IF;
1100 End Loop;
1101
1102 IF (l_nuv_part_flag = 'Y')
1103 THEN
1104
1105 --check if DFF segment is mapped on config page
1106 OPEN csr_get_mapped_context(l_rec_curr_asg_dtls.business_group_id);
1107 FETCH csr_get_mapped_context INTO l_rec_get_mapped_context;
1108 CLOSE csr_get_mapped_context;
1109
1110 hr_utility.set_location('Mapped Context is :'
1111 ||l_rec_get_mapped_context.pcv_information1,13);
1112
1113 IF(l_rec_get_mapped_context.pcv_information3 IS NOT NULL)
1114 THEN
1115 hr_utility.set_location('DFF Segment is mapped on config page',13);
1116 hr_utility.set_location('Mapped segment column is :'
1117 ||l_rec_get_mapped_context.pcv_information3,13);
1118
1119 --check if this is a penserver employee
1120 --For bug 7202378:Logic modifed for checking penserver emp
1121 l_segment_val_query := 'select '||l_rec_get_mapped_context.pcv_information2||' '||
1122 'from per_all_assignments_f'||' '||
1123 'where assignment_id = '||p_assignment_id||' '||
1124 'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
1125 'and effective_end_date';
1126
1127 hr_utility.set_location('l_segment_val_query: '||l_segment_val_query,14);
1128
1129 OPEN c_get_segment_val FOR l_segment_val_query;
1130 FETCH c_get_segment_val INTO l_segment_value;
1131 CLOSE c_get_segment_val;
1132
1133 IF l_segment_value IS NOT NULL
1134 THEN
1135 hr_utility.set_location('Segment field value is NOT NULL', 14);
1136 hr_utility.set_location('l_segment_value'||l_segment_value,14);
1137
1138 IF l_rec_get_mapped_context.pcv_information1 = 'Global Data Elements'
1139 THEN
1140 l_penserv_emp := 'Y';
1141
1142 --For bug 7202378:Second Change: Set Globa data element context flag
1143 l_gde_context_flag := 'Y';
1144
1145 ELSE
1146 IF(l_rec_curr_asg_dtls.ass_attribute_category = l_rec_get_mapped_context.pcv_information1)
1147 THEN
1148 l_penserv_emp := 'Y';
1149 END IF;
1150 END IF;
1151 END IF;
1152
1153 IF l_penserv_emp = 'Y'
1154 THEN
1155 hr_utility.set_location('This is a penserver employee',14);
1156 hr_utility.set_location('Mapped Context is :'
1157 ||l_rec_get_mapped_context.pcv_information1,14);
1158
1159 --Check if employee is Casual
1160 For rec_get_casual_emp_cate in csr_get_casual_emp_cate(l_rec_curr_asg_dtls.business_group_id)
1161 Loop
1162 IF rec_get_casual_emp_cate.pcv_information1 = l_rec_curr_asg_dtls.employment_category
1163 THEN
1164 l_emp_is_casual_flag := 'Y';
1165 hr_utility.set_location('Employee is Casual',15);
1166 hr_utility.set_location('Employment Categoty :'
1167 ||rec_get_casual_emp_cate.pcv_information1,15);
1168 Exit;
1169 End IF;
1170 End Loop;
1171
1172 IF(l_emp_is_casual_flag = 'Y')
1173 THEN
1174 --Check if Emp was something other than Casual anytime earlier
1175 --while he was a penserver employee
1176
1177 --For bug 7202378:Second Change: Modified logic
1178 l_prior_asg_dtls_query := 'select employment_category, ass_attribute_category, '||
1179 l_rec_get_mapped_context.pcv_information2||' '||
1180 'from per_all_assignments_f'||' '||
1181 'where assignment_id = '||p_assignment_id||' '||
1182 'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') > effective_start_date';
1183
1184 hr_utility.set_location('l_prior_asg_dtls_query: '||l_prior_asg_dtls_query,16);
1185
1186 OPEN c_get_prior_asg_dtls FOR l_prior_asg_dtls_query;
1187 Loop
1188 FETCH c_get_prior_asg_dtls INTO l_prior_emp_cate, l_prior_ass_att_cate, l_prior_ass_att_xx;
1189 EXIT WHEN c_get_prior_asg_dtls%NOTFOUND;
1190 hr_utility.set_location('l_prior_emp_cate: '||l_prior_emp_cate,16);
1191 hr_utility.set_location('l_prior_ass_att_cate: '||l_prior_ass_att_cate,16);
1192 hr_utility.set_location('l_prior_ass_att_xx: '||l_prior_ass_att_xx,16);
1193
1194 For rec_get_non_casual_emp_cate IN csr_get_non_casual_emp_cate(l_rec_curr_asg_dtls.business_group_id)
1195 Loop
1196 IF l_prior_emp_cate = rec_get_non_casual_emp_cate.pcv_information1
1197 THEN
1198 IF l_prior_ass_att_xx IS NOT NULL
1199 THEN
1200 IF l_gde_context_flag = 'Y'
1201 THEN
1202 l_emp_prior_non_casual_flag := 'Y';
1203 hr_utility.set_location('Employee was a non casual earlier',16);
1204 hr_utility.set_location('Prior Employment Categoty :'
1205 ||l_prior_emp_cate,16);
1206 Exit;
1207 ELSE
1208 IF l_prior_ass_att_cate = l_rec_get_mapped_context.pcv_information1
1209 THEN
1210 l_emp_prior_non_casual_flag := 'Y';
1211 hr_utility.set_location('Employee was a non casual earlier',16);
1212 hr_utility.set_location('Prior Employment Categoty :'
1213 ||l_prior_emp_cate,16);
1214 Exit;
1215 END IF;
1216 END IF; --end of l_gde_context_flag
1217 END IF; --end of l_prior_ass_att_xx is not null
1218 END IF; --end of l_prior_emp_cate
1219 End Loop;
1220
1221 IF (l_emp_prior_non_casual_flag = 'Y')
1222 THEN
1223 EXIT;
1224 END IF;
1225 END Loop;
1226 CLOSE c_get_prior_asg_dtls;
1227
1228 /*
1229 For rec_get_prior_asg_dtls in csr_get_prior_asg_dtls
1230 Loop
1231 For rec_get_non_casual_emp_cate IN csr_get_non_casual_emp_cate(l_rec_curr_asg_dtls.business_group_id)
1232 Loop
1233 IF rec_get_prior_asg_dtls.employment_category = rec_get_non_casual_emp_cate.pcv_information1
1234 THEN
1235 IF rec_get_prior_asg_dtls.ass_attribute_category = l_rec_get_mapped_context.pcv_information1
1236 THEN
1237 l_emp_prior_non_casual_flag := 'Y';
1238 hr_utility.set_location('Employee was a non casual earlier',16);
1239 hr_utility.set_location('Prior Employment Categoty :'
1240 ||rec_get_prior_asg_dtls.employment_category,16);
1241 Exit;
1242 END IF;
1243 End IF;
1244 End Loop;
1245
1246 IF (l_emp_prior_non_casual_flag = 'Y')
1247 THEN
1248 EXIT;
1249 END IF;
1250 END LOOP;
1251 */
1252
1253 IF(l_emp_prior_non_casual_flag = 'N')
1254 THEN
1255 --Check if any penserver element was attached to this employee
1256 --since he became casual
1257 OPEN csr_get_casual_asg_start_dt(l_rec_curr_asg_dtls.employment_category);
1258 FETCH csr_get_casual_asg_start_dt INTO l_get_casual_asg_start_dt;
1259 CLOSE csr_get_casual_asg_start_dt;
1260
1261 hr_utility.set_location('Casual start date :'||l_get_casual_asg_start_dt,17);
1262
1263 For rec_get_ele_type_since_casual in csr_get_ele_type_since_casual(l_get_casual_asg_start_dt)
1264 Loop
1265 For rec_get_nuv_part_elements in csr_get_nuv_part_elements(l_rec_curr_asg_dtls.business_group_id)
1266 Loop
1267 IF rec_get_nuv_part_elements.pcv_information1 = rec_get_ele_type_since_casual.element_type_id
1268 THEN
1269 l_prior_nuv_part_flag := 'Y';
1270 hr_utility.set_location('This is not the first element for this Casual Employee',17);
1271 hr_utility.set_location('Element_type_id :'
1272 ||rec_get_nuv_part_elements.pcv_information1,17);
1273 Exit;
1274 End IF;
1275 End Loop;
1276 IF(l_prior_nuv_part_flag = 'Y')
1277 THEN
1278 EXIT;
1279 END IF;
1280 End Loop;
1281
1282 IF(l_prior_nuv_part_flag = 'N')
1283 THEN
1284 --Check if DFF segment column is null
1285 l_query := 'select '||l_rec_get_mapped_context.pcv_information3||' '||
1286 'from per_all_assignments_f'||' '||
1287 'where assignment_id = '||p_assignment_id||' '||
1288 'and to_date('''||TO_CHAR(p_effective_start_date,'dd/mm/yyyy')||''',''dd/mm/yyyy'') between effective_start_date'||' '||
1289 'and effective_end_date';
1290
1291 hr_utility.set_location('l_query: '||l_query,18);
1292
1293 OPEN c_get_dff_val FOR l_query;
1294 FETCH c_get_dff_val INTO l_value;
1295 CLOSE c_get_dff_val;
1296
1297 IF l_value IS NULL
1298 THEN
1299 hr_utility.set_location('DFF field value is NULL', 19);
1300 hr_utility.set_location('l_value'||l_value,18);
1301
1302 --Decide the mode
1303 IF p_effective_start_date = l_rec_curr_asg_dtls.effective_start_date
1304 THEN
1305 hr_utility.set_location('Call update asg API in correction mode', 19);
1306 l_call_mode := 'CORRECTION';
1307 ELSIF l_rec_curr_asg_dtls.effective_end_date < hr_general.end_of_time
1308 THEN
1309 hr_utility.set_location('Call update asg API in update_change_insert mode', 20);
1310 l_call_mode := 'UPDATE_CHANGE_INSERT';
1311 ELSE
1312 hr_utility.set_location('Call update asg API in update mode', 19);
1313 l_call_mode := 'UPDATE';
1314 END IF; --End of decide the mode
1315
1316 --Decide DFF column values
1317 IF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE1'
1318 THEN
1319 l_rec_curr_asg_dtls.ass_attribute1 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1320
1321 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE2'
1322 THEN
1323 l_rec_curr_asg_dtls.ass_attribute2 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1324
1325 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE3'
1326 THEN
1327 l_rec_curr_asg_dtls.ass_attribute3 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1328
1329 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE4'
1330 THEN
1331 l_rec_curr_asg_dtls.ass_attribute4 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1332
1333 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE5'
1334 THEN
1335 l_rec_curr_asg_dtls.ass_attribute5 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1336
1337 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE6'
1338 THEN
1339 l_rec_curr_asg_dtls.ass_attribute6 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1340
1341 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE7'
1342 THEN
1343 l_rec_curr_asg_dtls.ass_attribute7 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1344
1345 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE8'
1346 THEN
1347 l_rec_curr_asg_dtls.ass_attribute8 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1348
1349 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE9'
1350 THEN
1351 l_rec_curr_asg_dtls.ass_attribute9 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1352
1353 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE10'
1354 THEN
1355 l_rec_curr_asg_dtls.ass_attribute10 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1356
1357 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE11'
1358 THEN
1359 l_rec_curr_asg_dtls.ass_attribute11 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1360
1361 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE12'
1362 THEN
1363 l_rec_curr_asg_dtls.ass_attribute12 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1364
1365 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE13'
1366 THEN
1367 l_rec_curr_asg_dtls.ass_attribute13 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1368
1369 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE14'
1370 THEN
1371 l_rec_curr_asg_dtls.ass_attribute14 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1372
1373 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE15'
1374 THEN
1375 l_rec_curr_asg_dtls.ass_attribute15 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1376
1377 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE16'
1378 THEN
1379 l_rec_curr_asg_dtls.ass_attribute16 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1380
1381 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE17'
1382 THEN
1383 l_rec_curr_asg_dtls.ass_attribute17 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1384
1385 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE18'
1386 THEN
1387 l_rec_curr_asg_dtls.ass_attribute18 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1388
1389 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE19'
1390 THEN
1391 l_rec_curr_asg_dtls.ass_attribute19 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1392
1393 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE20'
1394 THEN
1395 l_rec_curr_asg_dtls.ass_attribute20 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1396
1397 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE21'
1398 THEN
1399 l_rec_curr_asg_dtls.ass_attribute21 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1400
1401 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE22'
1402 THEN
1403 l_rec_curr_asg_dtls.ass_attribute22 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1404
1405 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE23'
1406 THEN
1407 l_rec_curr_asg_dtls.ass_attribute23 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1408
1409 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE24'
1410 THEN
1411 l_rec_curr_asg_dtls.ass_attribute24 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1412
1413 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE25'
1414 THEN
1415 l_rec_curr_asg_dtls.ass_attribute25 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1416
1417 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE26'
1418 THEN
1419 l_rec_curr_asg_dtls.ass_attribute26 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1420
1421 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE27'
1422 THEN
1423 l_rec_curr_asg_dtls.ass_attribute27 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1424
1425 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE28'
1426 THEN
1427 l_rec_curr_asg_dtls.ass_attribute28 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1428
1429 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE29'
1430 THEN
1431 l_rec_curr_asg_dtls.ass_attribute29 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1432
1433 ELSIF l_rec_get_mapped_context.pcv_information3 = 'ASS_ATTRIBUTE30'
1434 THEN
1435 l_rec_curr_asg_dtls.ass_attribute30 := TO_CHAR(p_effective_start_date,'dd-Mon-yy');
1436
1437 ELSE
1438 hr_utility.set_location('Invalid DFF segment mapped to penserver', 20);
1439 END IF;
1440
1441
1442 l_object_version_number := l_rec_curr_asg_dtls.object_version_number;
1443 l_cagr_grade_def_id := l_rec_curr_asg_dtls.cagr_grade_def_id;
1444 l_soft_coding_keyflex_id := l_rec_curr_asg_dtls.soft_coding_keyflex_id;
1445
1446 --Now Call update API
1447 hr_assignment_api.update_gb_emp_asg
1448 (p_validate => false
1449 ,p_effective_date => p_effective_start_date
1450 ,p_datetrack_update_mode => l_call_mode
1451 ,p_assignment_id => p_assignment_id
1452 ,p_object_version_number => l_object_version_number
1453 ,p_ass_attribute1 => l_rec_curr_asg_dtls.ass_attribute1
1454 ,p_ass_attribute2 => l_rec_curr_asg_dtls.ass_attribute2
1455 ,p_ass_attribute3 => l_rec_curr_asg_dtls.ass_attribute3
1456 ,p_ass_attribute4 => l_rec_curr_asg_dtls.ass_attribute4
1457 ,p_ass_attribute5 => l_rec_curr_asg_dtls.ass_attribute5
1458 ,p_ass_attribute6 => l_rec_curr_asg_dtls.ass_attribute6
1459 ,p_ass_attribute7 => l_rec_curr_asg_dtls.ass_attribute7
1460 ,p_ass_attribute8 => l_rec_curr_asg_dtls.ass_attribute8
1461 ,p_ass_attribute9 => l_rec_curr_asg_dtls.ass_attribute9
1462 ,p_ass_attribute10 => l_rec_curr_asg_dtls.ass_attribute10
1463 ,p_ass_attribute11 => l_rec_curr_asg_dtls.ass_attribute11
1464 ,p_ass_attribute12 => l_rec_curr_asg_dtls.ass_attribute12
1465 ,p_ass_attribute13 => l_rec_curr_asg_dtls.ass_attribute13
1466 ,p_ass_attribute14 => l_rec_curr_asg_dtls.ass_attribute14
1467 ,p_ass_attribute15 => l_rec_curr_asg_dtls.ass_attribute15
1468 ,p_ass_attribute16 => l_rec_curr_asg_dtls.ass_attribute16
1469 ,p_ass_attribute17 => l_rec_curr_asg_dtls.ass_attribute17
1470 ,p_ass_attribute18 => l_rec_curr_asg_dtls.ass_attribute18
1471 ,p_ass_attribute19 => l_rec_curr_asg_dtls.ass_attribute19
1472 ,p_ass_attribute20 => l_rec_curr_asg_dtls.ass_attribute20
1473 ,p_ass_attribute21 => l_rec_curr_asg_dtls.ass_attribute21
1474 ,p_ass_attribute22 => l_rec_curr_asg_dtls.ass_attribute22
1475 ,p_ass_attribute23 => l_rec_curr_asg_dtls.ass_attribute23
1476 ,p_ass_attribute24 => l_rec_curr_asg_dtls.ass_attribute24
1477 ,p_ass_attribute25 => l_rec_curr_asg_dtls.ass_attribute25
1478 ,p_ass_attribute26 => l_rec_curr_asg_dtls.ass_attribute26
1479 ,p_ass_attribute27 => l_rec_curr_asg_dtls.ass_attribute27
1480 ,p_ass_attribute28 => l_rec_curr_asg_dtls.ass_attribute28
1481 ,p_ass_attribute29 => l_rec_curr_asg_dtls.ass_attribute29
1482 ,p_ass_attribute30 => l_rec_curr_asg_dtls.ass_attribute30
1483 ,p_cagr_grade_def_id => l_cagr_grade_def_id
1484 ,p_cagr_concatenated_segments => l_cagr_concatenated_segments
1485 ,p_concatenated_segments => l_concatenated_segments
1486 ,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
1487 ,p_comment_id => l_comment_id
1488 ,p_effective_start_date => l_effective_start_date
1489 ,p_effective_end_date => l_effective_end_date
1490 ,p_no_managers_warning => l_no_managers_warning
1491 ,p_other_manager_warning => l_other_manager_warning
1492 ,p_hourly_salaried_warning => l_hourly_salaried_warning
1493 );
1494
1495 hr_utility.set_location('Update of assignment complete',21);
1496 hr_utility.set_location('l_effective_start_date :'||l_effective_start_date,21);
1497 hr_utility.set_location('l_effective_end_date :'||l_effective_end_date,21);
1498
1499
1500 END IF; --End of if DFF segment column is null
1501 END IF; --End of if any prior penserver element was not attached since he became casual
1502 END IF; --End of if employee was not something other than Casual anytime earlier
1503 END IF; --End of if employee is Casual
1504 END IF; --End of if this is a penserver employee
1505 END IF; --End of if DFF segment mapped on config page
1506 End If; --End of if element is nuvos or partnership
1507
1508 hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.update_ass_dff_col',22);
1509 EXCEPTION
1510 WHEN OTHERS
1511 THEN
1512 hr_utility.set_location('Proc: pqp_gb_ad_ee.update_ass_dff_col: Exception Section',23);
1513
1514 END UPDATE_PSI_ASS_DFF_COL;
1515
1516
1517 --For bug 7294977: Start
1518 -----------------------------------------------------------------------------
1519 -- AI_VAL_REF_COURT_ORDER
1520 --This procedure ensures that Reference is not NULL for some types of
1521 --Court Orders while inserting the element
1522 -----------------------------------------------------------------------------
1523 PROCEDURE AI_VAL_REF_COURT_ORDER
1524 (
1525 p_effective_start_date IN DATE,
1526 p_element_entry_id IN NUMBER,
1527 p_element_type_id IN NUMBER
1528 )
1529 IS
1530
1531
1532 -- BUG - 13363065. NI Changes 2012.
1533 CURSOR csr_ni_cate_pens_ent_values
1534 IS
1535
1536 SELECT max(decode (piv.name, 'Category', peev.screen_entry_value)) Category,
1537 max(decode (piv.name,'Pension', peev.screen_entry_value)) Pension
1538 FROM pay_element_entry_values_f peev,
1539 pay_input_values_f piv
1540 WHERE peev.element_entry_id = p_element_type_id
1541 AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1542 AND peev.input_value_id = piv.input_value_id
1543 AND piv.element_type_id = p_element_type_id
1544 AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1545 AND piv.name in ('Category', 'Pension')
1546 AND piv.legislation_code = 'GB'
1547 AND peev.screen_entry_value in ('F','G','S','A');
1548
1549 cursor csr_is_paye_aggregated
1550 is
1551 select pap.per_information10 per_agg_flag
1552 from
1553 per_all_people_f pap,
1554 pay_element_entries_f peef,
1555 per_all_assignments_f paaf
1556 where
1557 peef.element_entry_id = p_element_entry_id
1558 and paaf.assignment_id = peef.assignment_id
1559 and paaf.person_id = pap.person_id
1560 and p_effective_start_date BETWEEN peef.effective_start_date AND peef.effective_end_date
1561 and p_effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1562 and p_effective_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
1563
1564 CURSOR csr_get_element
1565 IS
1566 SELECT element_name
1567 FROM pay_element_types_f
1568 WHERE element_type_id = p_element_type_id
1569 AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date
1570 AND legislation_code = 'GB';
1571
1572 CURSOR csr_get_ele_ent_values
1573 IS
1574 SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
1575 max (decode (piv.name,'Reference', peev.screen_entry_value)),
1576 max (decode (piv.name,'Order Amount', peev.screen_entry_value)),
1577 max (decode (piv.name,'DEO Overriding Frequency', peev.screen_entry_value))
1578 FROM pay_element_entry_values_f peev,
1579 pay_input_values_f piv
1580 WHERE peev.element_entry_id = p_element_entry_id
1581 AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1582 AND peev.input_value_id = piv.input_value_id
1583 AND piv.element_type_id = p_element_type_id
1584 AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1585 AND piv.name in ('Type','Reference','Order Amount','DEO Overriding Frequency')
1586 AND piv.legislation_code = 'GB'
1587 ORDER BY piv.name DESC;
1588
1589 l_element_name pay_element_types_f.element_name%TYPE;
1590 l_type pay_element_entry_values_f.screen_entry_value%TYPE;
1591 l_reference pay_element_entry_values_f.screen_entry_value%TYPE;
1592 l_amount pay_element_entry_values_f.screen_entry_value%TYPE;
1593 l_override_ndr pay_element_entry_values_f.screen_entry_value%TYPE;
1594 l_aggr_flag VARCHAR2(1) := 'N';
1595 l_category varchar2(2);
1596 l_pension varchar2(2);
1597
1598 BEGIN
1599
1600 hr_utility.set_location('Entering procedure pqp_gb_ad_ee.AI_VAL_REF_COURT_ORDER',10);
1601 hr_utility.set_location('p_effective_start_date :' ||p_effective_start_date,10);
1602 hr_utility.set_location('p_element_entry_id :' ||p_element_entry_id,10);
1603 hr_utility.set_location('p_element_type_id :' ||p_element_type_id,10);
1604
1605 OPEN csr_get_element;
1606 FETCH csr_get_element INTO l_element_name;
1607 CLOSE csr_get_element;
1608
1609 hr_utility.set_location('l_element_name :' ||l_element_name,11);
1610
1611 IF (l_element_name = 'Court Order'
1612 OR l_element_name = 'Court Order NTPP')--element is Court Order
1613 THEN
1614
1615 hr_utility.set_location('Element is Court Order',12);
1616
1617 OPEN csr_get_ele_ent_values;
1618 FETCH csr_get_ele_ent_values INTO l_type, l_reference, l_amount,l_override_ndr;
1619 CLOSE csr_get_ele_ent_values;
1620
1621 hr_utility.set_location('l_type :' ||l_type,12);
1622 hr_utility.set_location('l_reference :' ||l_reference,12);
1623
1624 IF (l_type = 'CCAEO'
1625 OR l_type = 'CTO'
1626 OR l_type = 'AEO_PERCENT'
1627 OR l_type = 'CTO_POST_APRIL_2007'
1628 OR l_type = 'CMS_DEO')
1629 THEN
1630 hr_utility.set_location('Type is applicable',13);
1631
1632 IF l_reference IS NULL
1633 THEN
1634 hr_utility.set_location('Ref is NULL: Raise Error',14);
1635
1636 hr_utility.set_message(801, 'PAY_GB_78138_NULL_CO_REF');
1637 hr_utility.raise_error;
1638 END IF;
1639 END IF;
1640
1641 if l_type = 'CMS_DEO' then
1642 if l_amount is null then
1643 hr_utility.set_location('Order Amount is NULL: Raise Error',14);
1644
1645 hr_utility.set_message(800, 'PAY_GB_CMS_DEO_MAND_NDR');
1646 hr_utility.raise_error;
1647 end if;
1648
1649 OPEN csr_is_paye_aggregated;
1650 FETCH csr_is_paye_aggregated into l_aggr_flag;
1651 CLOSE csr_is_paye_aggregated;
1652 if l_override_ndr is null and l_aggr_flag = 'Y' then
1653 hr_utility.set_location('Overriding Frequency is NULL for aggregated assignment: Raise Error',14);
1654
1655 hr_utility.set_message(800, 'PAY_GB_CMS_DEO_OVR_FREQ');
1656 hr_utility.raise_error;
1657 end if;
1658
1659 end if;
1660 END IF;
1661 -----
1662 -- BUG - 13363065. NI Changes 2012.
1663 -----
1664 /*
1665 From 06-APR-2012 the NI categories F,G,S and
1666 Pension Category 'A' are not valid.
1667 We should throw the validation error if the user
1668 selects any of these categories or pension.
1669 */
1670 if l_element_name = 'NI' then
1671
1672 hr_utility.trace('NI Element');
1673 if p_effective_start_date >= to_Date('06-04-2012','dd-mm-yyyy') then
1674 open csr_ni_cate_pens_ent_values;
1675 fetch csr_ni_cate_pens_ent_values into l_category,l_pension;
1676 close csr_ni_cate_pens_ent_values;
1677
1678 if l_category <> 'A' then
1679 hr_utility.trace('Invalid Category');
1680 hr_utility.set_message(800,'HR_78097_GB_NI_INV_CATEGORY');
1681 hr_utility.raise_error;
1682 end if;
1683 if l_pension = 'A' then
1684 hr_utility.trace('Invalid Pension');
1685 hr_utility.set_message(800,'HR_78098_GB_NI_INV_PENSION');
1686 hr_utility.raise_error;
1687 end if;
1688
1689 end if;
1690 end if;
1691 hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.AI_VAL_REF_COURT_ORDER',15);
1692
1693 END AI_VAL_REF_COURT_ORDER;
1694
1695 -----------------------------------------------------------------------------
1696 -- AU_VAL_REF_COURT_ORDER
1697 --This procedure ensures that Reference is not NULL and not changed after a
1698 --payroll run for some types of Court Orders while updating the element
1699 -----------------------------------------------------------------------------
1700 PROCEDURE AU_VAL_REF_COURT_ORDER
1701 (
1702 p_effective_date IN DATE,
1703 p_datetrack_mode IN VARCHAR2,
1704 p_effective_start_date IN DATE,
1705 p_element_entry_id IN NUMBER,
1706 p_element_type_id_o IN NUMBER
1707 )
1708 IS
1709
1710 -- BUG - 13363065. NI Changes 2012.
1711 CURSOR csr_ni_cate_pens_ent_values
1712 IS
1713
1714 SELECT max(decode (piv.name, 'Category', peev.screen_entry_value)) Category,
1715 max(decode (piv.name,'Pension', peev.screen_entry_value)) Pension
1716 FROM pay_element_entry_values_f peev,
1717 pay_input_values_f piv
1718 WHERE peev.element_entry_id = p_element_entry_id
1719 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1720 AND peev.input_value_id = piv.input_value_id
1721 AND piv.element_type_id = p_element_type_id_o
1722 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1723 AND piv.name in ('Category', 'Pension')
1724 AND piv.legislation_code = 'GB'
1725 AND peev.screen_entry_value in ('F','G','S','A');
1726
1727 CURSOR csr_get_element
1728 IS
1729 SELECT element_name
1730 FROM pay_element_types_f
1731 WHERE element_type_id = p_element_type_id_o
1732 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1733 AND legislation_code = 'GB';
1734
1735 CURSOR csr_get_ele_ent_values
1736 IS
1737 SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
1738 max (decode (piv.name,'Reference', peev.screen_entry_value)),
1739 max (decode (piv.name,'Order Amount', peev.screen_entry_value)),
1740 max (decode (piv.name,'DEO Overriding Frequency', peev.screen_entry_value))
1741 FROM pay_element_entry_values_f peev,
1742 pay_input_values_f piv
1743 WHERE peev.element_entry_id = p_element_entry_id
1744 AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1745 AND peev.input_value_id = piv.input_value_id
1746 AND piv.element_type_id = p_element_type_id_o
1747 AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1748 AND piv.name in ('Type','Reference','Order Amount','DEO Overriding Frequency')
1749 AND piv.legislation_code = 'GB'
1750 ORDER BY piv.name DESC;
1751
1752 cursor csr_is_paye_aggregated
1753 is
1754 select pap.per_information10 per_agg_flag
1755 from
1756 per_all_people_f pap,
1757 pay_element_entries_f peef,
1758 per_all_assignments_f paaf
1759 where
1760 peef.element_entry_id = p_element_entry_id
1761 and paaf.assignment_id = peef.assignment_id
1762 and paaf.person_id = pap.person_id
1763 and p_effective_start_date BETWEEN peef.effective_start_date AND peef.effective_end_date
1764 and p_effective_start_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1765 and p_effective_start_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
1766
1767 CURSOR csr_get_reference
1768 IS
1769 SELECT nvl(prrv.result_value,'Unknown')
1770 FROM pay_run_results prr,
1771 pay_run_result_values prrv,
1772 pay_assignment_actions pac,
1773 pay_input_values_f piv ,
1774 pay_payroll_actions ppa
1775 WHERE prr.run_result_id = prrv.run_result_id
1776 AND prr.entry_type = 'E'
1777 AND PRR.source_type IN ('E', 'I')
1778 AND prr.source_id = p_element_entry_id
1779 AND pac.assignment_action_id = prr.assignment_action_id
1780 AND pac.action_status IN ('C')
1781 and ppa.action_type IN ('R','Q')
1782 AND ppa.payroll_action_id = pac.payroll_action_id
1783 AND pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
1784 FROM pay_assignment_actions pac1,
1785 pay_run_results prr1,
1786 pay_payroll_actions ppa1
1787 WHERE pac1.assignment_action_id = prr1.assignment_action_id
1788 AND ppa1.payroll_action_id = pac1.payroll_action_id
1789 AND prr1.source_id = p_element_entry_id
1790 AND pac1.action_status IN ('C')
1791 and ppa1.action_type IN ('R','Q')
1792 and prr1.entry_type = 'E'
1793 AND PRR1.source_type IN ('E', 'I') )
1794 AND piv.legislation_code = 'GB'
1795 AND piv.name = 'Reference'
1796 AND piv.input_value_id = prrv.input_value_id
1797 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date ;
1798
1799
1800 CURSOR csr_get_results
1801 IS
1802 SELECT PRR.run_result_id
1803 FROM pay_run_results PRR,
1804 pay_assignment_actions ASA,
1805 pay_payroll_actions PPA
1806 WHERE PRR.source_id = p_element_entry_id
1807 AND PRR.source_type IN ('E', 'I')
1808 AND PRR.status IN ('P', 'PA', 'R', 'O')
1809 AND ASA.assignment_action_id = PRR.assignment_action_id
1810 AND asa.action_status IN ( 'C')
1811 and ppa.action_type IN ('R','Q')
1812 AND PPA.payroll_action_id = ASA.payroll_action_id
1813 -- Check whether the run_result has been revered.
1814 AND NOT EXISTS (SELECT null
1815 FROM pay_run_results prr2
1816 WHERE prr2.source_id = PRR.run_result_id
1817 AND prr2.source_type IN ('R', 'V'));
1818
1819
1820 -----------------------------
1821 -- BEGIN
1822 -- For Bug 8485686
1823 -----------------------------
1824
1825 CURSOR tax_district(c_assignment_id in number) IS
1826 SELECT hsck.segment1
1827 FROM hr_soft_coding_keyflex hsck,
1828 pay_all_payrolls_f papf,
1829 per_all_assignments_f paaf
1830 WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
1831 AND papf.payroll_id =paaf.payroll_id
1832 AND paaf.assignment_id = c_assignment_id
1833 AND p_effective_date between paaf.effective_start_date and paaf.effective_end_date
1834 AND p_effective_date between papf.effective_start_date and papf.effective_end_date;
1835
1836
1837 CURSOR cur_element_entry_id(c_assignment_id IN NUMBER)
1838 --,c_tax_district IN VARCHAR2) 10157179
1839 IS
1840 SELECT pev.element_entry_id,
1841 paf.assignment_id
1842 , pev.effective_start_date --10157179
1843 FROM pay_paye_element_entries_v pev,
1844 per_all_assignments_f paf,
1845 per_assignment_status_types past
1846 WHERE pev.assignment_id = paf.assignment_id
1847 AND past.assignment_status_type_id = paf.assignment_status_type_id
1848 AND past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN')
1849 /* 10157179. These two conditions are executed first in certain instances which makes the program to run for more than 20 hours
1850 AND pay_gb_eoy_archive.get_agg_active_end(c_assignment_id, c_tax_district, p_effective_date)
1851 = pay_gb_eoy_archive.get_agg_active_end(paf.assignment_id, c_tax_district, p_effective_date)
1852 AND pay_gb_eoy_archive.get_agg_active_start(c_assignment_id, c_tax_district, p_effective_date)
1853 = pay_gb_eoy_archive.get_agg_active_start(paf.assignment_id, c_tax_district, p_effective_date) */
1854 AND p_effective_date between paf.effective_start_date and paf.effective_end_date
1855 AND paf.person_id =(SELECT person_id
1856 FROM per_all_assignments_f paf
1857 WHERE assignment_id = c_assignment_id
1858 AND p_effective_date between paf.effective_start_date and paf.effective_end_date)
1859 AND c_assignment_id <> paf.assignment_id; --10157179
1860
1861
1862 CURSOR c_get_input_values
1863 IS
1864 SELECT input_value_id1,
1865 tax_code,
1866 input_value_id2,
1867 d_tax_basis,
1868 input_value_id3,
1869 d_refundable,
1870 input_value_id4,
1871 d_pay_previous,
1872 input_value_id5,
1873 d_tax_previous,
1874 input_value_id6,
1875 d_authority,
1876 entry_information1, -- For bug 8548190
1877 entry_information2
1878 FROM pay_paye_element_entries_v
1879 WHERE element_entry_id = p_element_entry_id
1880 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
1881
1882 --Added the below cursor for the bug fix# 10254292
1883 CURSOR csr_get_tax_code
1884 IS
1885 SELECT max (decode (piv.name, 'Tax Code', peev.screen_entry_value))
1886 FROM pay_element_entry_values_f peev,
1887 pay_input_values_f piv
1888 WHERE peev.element_entry_id = p_element_entry_id
1889 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1890 AND peev.input_value_id = piv.input_value_id
1891 AND piv.element_type_id = p_element_type_id_o
1892 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1893 AND piv.name in ('Tax Code')
1894 AND piv.legislation_code = 'GB'
1895 ORDER BY piv.name DESC;
1896
1897 l_tax_code VARCHAR2(30); --Added for the bug fix# 10254292
1898
1899 l_cnt number := 0;
1900
1901 l_paye_assg_id number;
1902 l_paye_agg varchar2(10);
1903 l_paye_ini_tax_dist VARCHAR2(60);
1904 l_paye_tax_dist VARCHAR2(60);
1905
1906
1907 -----------------------------
1908 -- END
1909 -- For Bug 8485686
1910 -----------------------------
1911
1912
1913
1914 l_element_name pay_element_types_f.element_name%TYPE;
1915 l_type pay_element_entry_values_f.screen_entry_value%TYPE;
1916 l_reference pay_element_entry_values_f.screen_entry_value%TYPE;
1917 l_amount pay_element_entry_values_f.screen_entry_value%TYPE;
1918
1919 l_override_ndr pay_element_entry_values_f.screen_entry_value%TYPE;
1920 v_exists VARCHAR2(100) := 'N';
1921 v_value varchar2(100) := 'Unknown';
1922
1923 --10157179 Begin
1924 l_session_present number;
1925 l_datetrack_mode VARCHAR2(30);
1926 l_agg_active_end date;
1927 l_agg_active_start date;
1928 --10157179 End
1929 l_aggr_flag VARCHAR2(1):= 'N';
1930 --BUG - 13363065. NI Changes 2012.
1931 l_category varchar(1);
1932 l_pension varchar2(1);
1933
1934 BEGIN
1935
1936 --hr_utility.trace_on(null,'jag');
1937 hr_utility.set_location('Entering procedure pqp_gb_ad_ee.AU_VAL_REF_COURT_ORDER',10);
1938 hr_utility.set_location('p_effective_date :' ||p_effective_date,10);
1939 hr_utility.set_location('p_datetrack_mode :' ||p_datetrack_mode,10);
1940 hr_utility.set_location('p_effective_start_date :' ||p_effective_start_date,10);
1941 hr_utility.set_location('p_element_entry_id :' ||p_element_entry_id,10);
1942 hr_utility.set_location('p_element_type_id_o :' ||p_element_type_id_o,10);
1943
1944 OPEN csr_get_element;
1945 FETCH csr_get_element INTO l_element_name;
1946 CLOSE csr_get_element;
1947
1948 hr_utility.set_location('l_element_name :' ||l_element_name,11);
1949
1950 IF (l_element_name = 'Court Order'
1951 OR l_element_name = 'Court Order NTPP')--element is Court Order
1952 THEN
1953
1954 hr_utility.set_location('Element is Court Order',12);
1955
1956 OPEN csr_get_ele_ent_values;
1957 FETCH csr_get_ele_ent_values INTO l_type, l_reference, l_amount,l_override_ndr;
1958 CLOSE csr_get_ele_ent_values;
1959
1960 hr_utility.set_location('l_type :' ||l_type,12);
1961 hr_utility.set_location('l_reference :' ||l_reference,12);
1962
1963 IF (l_type = 'CCAEO'
1964 OR l_type = 'CTO'
1965 OR l_type = 'AEO_PERCENT'
1966 OR l_type = 'CTO_POST_APRIL_2007'
1967 OR l_type = 'CMS_DEO')
1968 THEN
1969 hr_utility.set_location('Type is applicable',13);
1970
1971 OPEN csr_get_results;
1972 FETCH csr_get_results INTO v_exists;
1973
1974 IF csr_get_results%NOTFOUND
1975 THEN
1976
1977 hr_utility.set_location('No payroll run for this element',14);
1978
1979 IF (l_reference is null)
1980 THEN
1981 hr_utility.set_location('Ref is NULL: Raise Error',15);
1982
1983 hr_utility.set_message(801, 'PAY_GB_78138_NULL_CO_REF');
1984 hr_utility.raise_error;
1985 END IF;
1986
1987 ELSE
1988
1989 OPEN csr_get_reference;
1990 FETCH csr_get_reference INTO v_value;
1991
1992 hr_utility.set_location('v_value :' ||v_value,16);
1993
1994 --v_value can't be null as we are fetching it from payroll run
1995 IF (l_reference is null
1996 or v_value <> l_reference
1997 )
1998 THEN
1999 hr_utility.set_location('Ref is changed since previous payroll run: Raise Error',17);
2000
2001 hr_utility.set_message(801, 'PAY_GB_78139_INVALID_CO_REF');
2002 hr_utility.set_message_token('REFERENCE', l_reference);
2003 hr_utility.raise_error;
2004 END IF;
2005
2006 CLOSE csr_get_reference;
2007 END IF;
2008
2009 CLOSE csr_get_results;
2010 END IF;
2011 if l_type = 'CMS_DEO' then
2012
2013
2014 if l_amount is null then
2015 hr_utility.set_location('Order Amount is NULL: Raise Error',14);
2016
2017 hr_utility.set_message(800, 'PAY_GB_CMS_DEO_MAND_NDR');
2018 hr_utility.raise_error;
2019 end if;
2020
2021 OPEN csr_is_paye_aggregated;
2022 FETCH csr_is_paye_aggregated into l_aggr_flag;
2023 CLOSE csr_is_paye_aggregated;
2024
2025 if l_override_ndr is null and l_aggr_flag = 'Y' then
2026
2027 hr_utility.set_location('Overriding Frequency is NULL for aggregated assignment: Raise Error',14);
2028
2029 hr_utility.set_message(800, 'PAY_GB_CMS_DEO_OVR_FREQ');
2030 hr_utility.raise_error;
2031 end if;
2032 end if;
2033
2034
2035 END IF;
2036
2037 -----------------------------
2038 -- BEGIN
2039 -- For Bug 8485686
2040 -----------------------------
2041
2042 hr_utility.set_location('PAYE g_global_paye_validation :'||g_global_paye_validation ,18);
2043
2044 --Modifications for the bug fix# 10254292 starts here
2045 /*
2046 Below condition checks whether the current PAYE element has
2047 1. effective_start_date < 06-04-2011
2048 2. date track mode = 'CORRECTION'
2049 3. tax_code = 'D1'
2050 If yes, then it will throw an error saying 'D1 tax code is valid only from 06-04-2011, CORRECTION mode is incorrect.'
2051 */
2052 IF (l_element_name = 'PAYE Details') THEN
2053 if ((trunc(p_effective_start_date) < to_date('06-04-2011','DD-MM-YYYY')) and
2054 (p_datetrack_mode = 'CORRECTION')) then
2055 OPEN csr_get_tax_code;
2056 FETCH csr_get_tax_code INTO l_tax_code;
2057 CLOSE csr_get_tax_code;
2058
2059 if l_tax_code = 'D1' then
2060 hr_utility.set_message(800,'HR_78095_GB_PAYE_D1_CORR');
2061 hr_utility.raise_error;
2062 end if;
2063 end if;
2064 END IF;
2065 --Modifications for the bug fix# 10254292 ends here
2066
2067 IF l_element_name = 'PAYE Details' AND g_global_paye_validation = 'Y'
2068 THEN
2069
2070 IF g_first_assignment = 'Y'
2071 THEN
2072
2073 hr_utility.set_location('PAYE l_element_name :'||l_element_name ,18);
2074 -- Fix for Bug# 11924747
2075 /**
2076 The query now retrieves the assignment id by taking into consideration p_effective_start_date instead of p_effective_date.
2077 */
2078 SELECT assignment_id
2079 INTO l_paye_assg_id
2080 FROM pay_element_entries_f
2081 WHERE element_entry_id = p_element_entry_id
2082 AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date;
2083
2084 --Fix for Bug 11924747 Ends here.
2085
2086 g_first_assignment_id := l_paye_assg_id;
2087 g_first_assignment := 'N';
2088
2089 hr_utility.set_location('PAYE l_paye_assg_id :'||l_paye_assg_id ,18);
2090
2091 SELECT papf.per_information10
2092 INTO l_paye_agg
2093 FROM per_all_people_f papf,
2094 per_all_assignments_f paaf
2095 WHERE paaf.assignment_id = l_paye_assg_id
2096 AND paaf.person_id = papf.person_id
2097 AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
2098 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date;
2099
2100 hr_utility.set_location('PAYE l_paye_agg :'||l_paye_agg ,18);
2101
2102 IF l_paye_agg = 'Y'
2103 THEN
2104
2105 OPEN tax_district(l_paye_assg_id);
2106 FETCH tax_district INTO l_paye_ini_tax_dist;
2107 CLOSE tax_district;
2108
2109 hr_utility.set_location('PAYE jag_ini_tax_district :'||l_paye_ini_tax_dist ,18);
2110
2111 OPEN c_get_input_values;
2112 FETCH c_get_input_values into l_input_value_id1,
2113 l_tax_code,
2114 l_input_value_id2,
2115 l_d_tax_basis,
2116 l_input_value_id3,
2117 l_d_refundable,
2118 l_input_value_id4,
2119 l_d_pay_previous,
2120 l_input_value_id5,
2121 l_d_tax_previous,
2122 l_input_value_id6,
2123 l_authority,
2124 l_ele_information1,
2125 l_ele_information2;
2126 CLOSE c_get_input_values;
2127
2128 hr_utility.set_location('PAYE l_tax_code :'||l_tax_code ,18);
2129 --10157179 Begin
2130 /*SESSION Date should be present for the view pay_paye_element_entries_v
2131 used in cursor cur_element_entry_id. */
2132 select count(*) into l_session_present from fnd_sessions ses
2133 where ses.session_id = userenv('SESSIONID');
2134 If l_session_present <> 1 then
2135 hr_utility.set_location('No session date so inserting ',18);
2136 insert into fnd_sessions (SESSION_ID, EFFECTIVE_DATE)
2137 values(userenv('sessionid'), p_effective_date);
2138 End if;
2139
2140 --OPEN cur_element_entry_id(l_paye_assg_id,l_paye_ini_tax_dist);
2141 OPEN cur_element_entry_id(l_paye_assg_id);
2142 --10157179 End
2143 FETCH cur_element_entry_id bulk collect into g_element_entry_rec_tab;
2144 CLOSE cur_element_entry_id;
2145
2146 --10157179 Begin
2147 hr_utility.set_location('Fetched cur_element_entry_id' ,19);
2148 --IF g_element_entry_rec_tab.COUNT > 1
2149 IF g_element_entry_rec_tab.COUNT > 0
2150 THEN
2151 l_agg_active_end := pay_gb_eoy_archive.get_agg_active_end(l_paye_assg_id, l_paye_ini_tax_dist, p_effective_date);
2152 l_agg_active_start := pay_gb_eoy_archive.get_agg_active_start(l_paye_assg_id, l_paye_ini_tax_dist, p_effective_date);
2153 hr_utility.set_location('l_agg_active_end '||l_agg_active_end ,20);
2154 hr_utility.set_location('l_agg_active_start'||l_agg_active_start ,20);
2155
2156
2157 l_cnt := g_element_entry_rec_tab.FIRST;
2158 LOOP
2159
2160 EXIT WHEN l_cnt IS NULL;
2161 hr_utility.set_location('g_element_entry_rec_tab(l_cnt).aid'||g_element_entry_rec_tab(l_cnt).aid ,21);
2162 hr_utility.set_location('g_first_assignment_id'||g_first_assignment_id ,21);
2163 /* 10157179 This condition is already added in cur_element_entry_id
2164 IF g_element_entry_rec_tab(l_cnt).aid <> g_first_assignment_id
2165 THEN
2166 */
2167 --10157179 the below condition was removed from cursor cur_element_entry_id and placed below
2168 IF
2169 pay_gb_eoy_archive.get_agg_active_end(g_element_entry_rec_tab(l_cnt).aid, l_paye_ini_tax_dist, p_effective_date) = l_agg_active_end
2170 and
2171 pay_gb_eoy_archive.get_agg_active_start(g_element_entry_rec_tab(l_cnt).aid, l_paye_ini_tax_dist, p_effective_date) = l_agg_active_start
2172 THEN
2173
2174 hr_utility.set_location(g_element_entry_rec_tab(l_cnt).aid ||' share same aggregation period with '|| g_first_assignment_id,21);
2175 --10157179 End
2176 OPEN tax_district(g_element_entry_rec_tab(l_cnt).aid);
2177 FETCH tax_district INTO l_paye_tax_dist;
2178 CLOSE tax_district;
2179
2180 hr_utility.set_location('PAYE l_paye_tax_dist :'||l_paye_tax_dist ,18);
2181
2182 IF l_paye_tax_dist=l_paye_ini_tax_dist
2183 THEN
2184
2185
2186 hr_utility.set_location('PAYE P_EFFECTIVE_DATE '|| P_EFFECTIVE_DATE ,18);
2187 --10157179 begin
2188 IF trunc(g_element_entry_rec_tab(l_cnt).eff_date) = trunc(P_EFFECTIVE_DATE) then
2189 L_DATETRACK_MODE := 'CORRECTION';
2190 ELSIF substr(P_DATETRACK_MODE,1,6) = 'DELETE' then
2191 L_DATETRACK_MODE := 'UPDATE';
2192 ELSE
2193 L_DATETRACK_MODE := P_DATETRACK_MODE;
2194 END IF;
2195 hr_utility.set_location(P_DATETRACK_MODE||'PAYE P_DATETRACK_MODE is changed to '|| L_DATETRACK_MODE ,23);
2196 --10157179 end
2197
2198 --Modifications for the bug fix# 10254292 starts here
2199 /*
2200 Below condition checks whether the current PAYE element has
2201 1. effective_start_date < 06-04-2011
2202 2. date track mode = 'CORRECTION'
2203 3. tax_code = 'D1'
2204 If yes, then it will throw an error saying 'D1 tax code is valid only from 06-04-2011,
2205 CORRECTION mode is incorrect for atleast one of the assignments in agregation.'
2206
2207 This check will be done for all the assignments in the aggregation.
2208 If any of the PAYE element meets this condition, it will not allow to CORRECT any of the PAYE records in the aggregation.
2209 */
2210 if ((l_tax_code = 'D1') and
2211 (trunc(g_element_entry_rec_tab(l_cnt).eff_date) < to_date('06-04-2011','DD-MM-YYYY')) and
2212 (p_datetrack_mode = 'CORRECTION'))
2213 then
2214 g_first_assignment := 'Y';
2215 hr_utility.set_message(800,'HR_78096_GB_PAYE_D1_AGR_CORR');
2216 hr_utility.raise_error;
2217 end if;
2218
2219 --Modifications for the bug fix# 10254292 ends here
2220
2221 hr_entry_api.update_element_entry
2222 (p_dt_update_mode => L_DATETRACK_MODE,
2223 p_session_date => P_EFFECTIVE_DATE,
2224 p_element_entry_id => g_element_entry_rec_tab(l_cnt).eeid,
2225 p_input_value_id1 => l_input_value_id1,
2226 P_entry_value1 => l_tax_code,
2227 p_input_value_id2 => l_input_value_id2,
2228 P_entry_value2 => l_d_tax_basis,
2229 p_input_value_id3 => l_input_value_id3,
2230 P_entry_value3 => l_d_refundable,
2231 p_input_value_id4 => l_input_value_id4,
2232 P_entry_value4 => l_d_pay_previous,
2233 p_input_value_id5 => l_input_value_id5,
2234 P_entry_value5 => l_d_tax_previous,
2235 p_input_value_id6 => l_input_value_id6,
2236 P_entry_value6 => l_authority,
2237 P_entry_information_category => 'GB_PAYE',
2238 P_entry_information1 => l_ele_information1,
2239 P_entry_information2 => l_ele_information2
2240 );
2241
2242 END IF;
2243
2244 END IF;
2245
2246 l_cnt := g_element_entry_rec_tab.NEXT (l_cnt);
2247
2248 IF l_cnt IS NULL
2249 THEN
2250 g_element_entry_rec_tab.DELETE;
2251 g_first_assignment := 'Y';
2252 END IF;
2253 END LOOP;
2254 ELSE
2255 g_first_assignment := 'Y';
2256 g_first_assignment_id := -1;
2257 END IF;
2258 ELSE
2259 g_first_assignment := 'Y';
2260 g_first_assignment_id := -1;
2261 END IF;
2262 END IF;
2263 END IF;
2264
2265 -----------------------------
2266 -- END
2267 -- For Bug 8485686
2268 -----------------------------
2269
2270 -----
2271 -- BUG - 13363065. NI Changes 2012.
2272 -----
2273 /*
2274 From 06-APR-2012 the NI categories F,G,S and
2275 Pension Category 'A' are not valid.
2276 We should throw the validation error if the user
2277 selects any of these categories or pension.
2278 */
2279 if l_element_name = 'NI' then
2280 hr_utility.trace('NI Element');
2281 if p_effective_date >= to_Date('06-04-2012','dd-mm-yyyy') then
2282 open csr_ni_cate_pens_ent_values;
2283 fetch csr_ni_cate_pens_ent_values into l_category,l_pension;
2284 close csr_ni_cate_pens_ent_values;
2285
2286 if l_category <> 'A' then
2287 hr_utility.trace('Invalid Category');
2288 hr_utility.set_message(800,'HR_78097_GB_NI_INV_CATEGORY');
2289 hr_utility.raise_error;
2290 end if;
2291 if l_pension = 'A' then
2292 hr_utility.trace('Invalid Pension');
2293 hr_utility.set_message(800,'HR_78098_GB_NI_INV_PENSION');
2294 hr_utility.raise_error;
2295 end if;
2296 end if;
2297 end if;
2298 -----
2299 -- END BUG - 13363065. NI Changes 2012.
2300 -----
2301
2302
2303 hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.AU_VAL_REF_COURT_ORDER',18);
2304
2305 END AU_VAL_REF_COURT_ORDER;
2306
2307 --For bug 7294977: End
2308
2309 END pqp_gb_ad_ee;
[Home] [Help]