1 PACKAGE BODY pqp_gb_ad_ee AS
2 /* $Header: pqgbadee.pkb 120.2.12010000.5 2008/08/08 15:14:15 namgoyal 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 CURSOR csr_get_element
1532 IS
1533 SELECT element_name
1534 FROM pay_element_types_f
1535 WHERE element_type_id = p_element_type_id
1536 AND p_effective_start_date BETWEEN effective_start_date AND effective_end_date
1537 AND legislation_code = 'GB';
1538
1539 CURSOR csr_get_ele_ent_values
1540 IS
1541 SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
1542 max (decode (piv.name,'Reference', peev.screen_entry_value))
1543 FROM pay_element_entry_values_f peev,
1544 pay_input_values_f piv
1545 WHERE peev.element_entry_id = p_element_entry_id
1546 AND p_effective_start_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1547 AND peev.input_value_id = piv.input_value_id
1548 AND piv.element_type_id = p_element_type_id
1549 AND p_effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1550 AND piv.name in ('Type','Reference')
1551 AND piv.legislation_code = 'GB'
1552 ORDER BY piv.name DESC;
1553
1554 l_element_name pay_element_types_f.element_name%TYPE;
1555 l_type pay_element_entry_values_f.screen_entry_value%TYPE;
1556 l_reference pay_element_entry_values_f.screen_entry_value%TYPE;
1557
1558 BEGIN
1559
1560 hr_utility.set_location('Entering procedure pqp_gb_ad_ee.AI_VAL_REF_COURT_ORDER',10);
1561 hr_utility.set_location('p_effective_start_date :' ||p_effective_start_date,10);
1562 hr_utility.set_location('p_element_entry_id :' ||p_element_entry_id,10);
1563 hr_utility.set_location('p_element_type_id :' ||p_element_type_id,10);
1564
1565 OPEN csr_get_element;
1566 FETCH csr_get_element INTO l_element_name;
1567 CLOSE csr_get_element;
1568
1569 hr_utility.set_location('l_element_name :' ||l_element_name,11);
1570
1571 IF (l_element_name = 'Court Order'
1572 OR l_element_name = 'Court Order NTPP')--element is Court Order
1573 THEN
1574
1575 hr_utility.set_location('Element is Court Order',12);
1576
1577 OPEN csr_get_ele_ent_values;
1578 FETCH csr_get_ele_ent_values INTO l_type, l_reference;
1579 CLOSE csr_get_ele_ent_values;
1580
1581 hr_utility.set_location('l_type :' ||l_type,12);
1582 hr_utility.set_location('l_reference :' ||l_reference,12);
1583
1584 IF (l_type = 'CCAEO'
1585 OR l_type = 'CTO'
1586 OR l_type = 'AEO_PERCENT'
1587 OR l_type = 'CTO_POST_APRIL_2007')
1588 THEN
1589 hr_utility.set_location('Type is applicable',13);
1590
1591 IF l_reference IS NULL
1592 THEN
1593 hr_utility.set_location('Ref is NULL: Raise Error',14);
1594
1595 hr_utility.set_message(801, 'PAY_GB_78138_NULL_CO_REF');
1596 hr_utility.raise_error;
1597 END IF;
1598 END IF;
1599 END IF;
1600
1601 hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.AI_VAL_REF_COURT_ORDER',15);
1602
1603 END AI_VAL_REF_COURT_ORDER;
1604
1605 -----------------------------------------------------------------------------
1606 -- AU_VAL_REF_COURT_ORDER
1607 --This procedure ensures that Reference is not NULL and not changed after a
1608 --payroll run for some types of Court Orders while updating the element
1609 -----------------------------------------------------------------------------
1610 PROCEDURE AU_VAL_REF_COURT_ORDER
1611 (
1612 p_effective_date IN DATE,
1613 p_datetrack_mode IN VARCHAR2,
1614 p_effective_start_date IN DATE,
1615 p_element_entry_id IN NUMBER,
1616 p_element_type_id_o IN NUMBER
1617 )
1618 IS
1619
1620 CURSOR csr_get_element
1621 IS
1622 SELECT element_name
1623 FROM pay_element_types_f
1624 WHERE element_type_id = p_element_type_id_o
1625 AND p_effective_date BETWEEN effective_start_date AND effective_end_date
1626 AND legislation_code = 'GB';
1627
1628 CURSOR csr_get_ele_ent_values
1629 IS
1630 SELECT max (decode (piv.name, 'Type', peev.screen_entry_value)),
1631 max (decode (piv.name,'Reference', peev.screen_entry_value))
1632 FROM pay_element_entry_values_f peev,
1633 pay_input_values_f piv
1634 WHERE peev.element_entry_id = p_element_entry_id
1635 AND p_effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
1636 AND peev.input_value_id = piv.input_value_id
1637 AND piv.element_type_id = p_element_type_id_o
1638 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1639 AND piv.name in ('Type','Reference')
1640 AND piv.legislation_code = 'GB'
1641 ORDER BY piv.name DESC;
1642
1643
1644 CURSOR csr_get_reference
1645 IS
1646 SELECT nvl(prrv.result_value,'Unknown')
1647 FROM pay_run_results prr,
1648 pay_run_result_values prrv,
1649 pay_assignment_actions pac,
1650 pay_input_values_f piv ,
1651 pay_payroll_actions ppa
1652 WHERE prr.run_result_id = prrv.run_result_id
1653 AND prr.entry_type = 'E'
1654 AND PRR.source_type IN ('E', 'I')
1655 AND prr.source_id = p_element_entry_id
1656 AND pac.assignment_action_id = prr.assignment_action_id
1657 AND pac.action_status IN ('C')
1658 and ppa.action_type IN ('R','Q')
1659 AND ppa.payroll_action_id = pac.payroll_action_id
1660 AND pac.assignment_action_id = (SELECT max(pac1.assignment_action_id)
1661 FROM pay_assignment_actions pac1,
1662 pay_run_results prr1,
1663 pay_payroll_actions ppa1
1664 WHERE pac1.assignment_action_id = prr1.assignment_action_id
1665 AND ppa1.payroll_action_id = pac1.payroll_action_id
1666 AND prr1.source_id = p_element_entry_id
1667 AND pac1.action_status IN ('C')
1668 and ppa1.action_type IN ('R','Q')
1669 and prr1.entry_type = 'E'
1670 AND PRR1.source_type IN ('E', 'I') )
1671 AND piv.legislation_code = 'GB'
1672 AND piv.name = 'Reference'
1673 AND piv.input_value_id = prrv.input_value_id
1674 AND p_effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date ;
1675
1676
1677 CURSOR csr_get_results
1678 IS
1679 SELECT PRR.run_result_id
1680 FROM pay_run_results PRR,
1681 pay_assignment_actions ASA,
1682 pay_payroll_actions PPA
1683 WHERE PRR.source_id = p_element_entry_id
1684 AND PRR.source_type IN ('E', 'I')
1685 AND PRR.status IN ('P', 'PA', 'R', 'O')
1686 AND ASA.assignment_action_id = PRR.assignment_action_id
1687 AND asa.action_status IN ( 'C')
1688 and ppa.action_type IN ('R','Q')
1689 AND PPA.payroll_action_id = ASA.payroll_action_id
1690 -- Check whether the run_result has been revered.
1691 AND NOT EXISTS (SELECT null
1692 FROM pay_run_results prr2
1693 WHERE prr2.source_id = PRR.run_result_id
1694 AND prr2.source_type IN ('R', 'V'));
1695
1696 l_element_name pay_element_types_f.element_name%TYPE;
1697 l_type pay_element_entry_values_f.screen_entry_value%TYPE;
1698 l_reference pay_element_entry_values_f.screen_entry_value%TYPE;
1699
1700 v_exists VARCHAR2(100) := 'N';
1701 v_value varchar2(100) := 'Unknown';
1702
1703 BEGIN
1704 hr_utility.set_location('Entering procedure pqp_gb_ad_ee.AU_VAL_REF_COURT_ORDER',10);
1705 hr_utility.set_location('p_effective_date :' ||p_effective_date,10);
1706 hr_utility.set_location('p_datetrack_mode :' ||p_datetrack_mode,10);
1707 hr_utility.set_location('p_effective_start_date :' ||p_effective_start_date,10);
1708 hr_utility.set_location('p_element_entry_id :' ||p_element_entry_id,10);
1709 hr_utility.set_location('p_element_type_id_o :' ||p_element_type_id_o,10);
1710
1711 OPEN csr_get_element;
1712 FETCH csr_get_element INTO l_element_name;
1713 CLOSE csr_get_element;
1714
1715 hr_utility.set_location('l_element_name :' ||l_element_name,11);
1716
1717 IF (l_element_name = 'Court Order'
1718 OR l_element_name = 'Court Order NTPP')--element is Court Order
1719 THEN
1720
1721 hr_utility.set_location('Element is Court Order',12);
1722
1723 OPEN csr_get_ele_ent_values;
1724 FETCH csr_get_ele_ent_values INTO l_type, l_reference;
1725 CLOSE csr_get_ele_ent_values;
1726
1727 hr_utility.set_location('l_type :' ||l_type,12);
1728 hr_utility.set_location('l_reference :' ||l_reference,12);
1729
1730 IF (l_type = 'CCAEO'
1731 OR l_type = 'CTO'
1732 OR l_type = 'AEO_PERCENT'
1733 OR l_type = 'CTO_POST_APRIL_2007')
1734 THEN
1735 hr_utility.set_location('Type is applicable',13);
1736
1737 OPEN csr_get_results;
1738 FETCH csr_get_results INTO v_exists;
1739
1740 IF csr_get_results%NOTFOUND
1741 THEN
1742
1743 hr_utility.set_location('No payroll run for this element',14);
1744
1745 IF (l_reference is null)
1746 THEN
1747 hr_utility.set_location('Ref is NULL: Raise Error',15);
1748
1749 hr_utility.set_message(801, 'PAY_GB_78138_NULL_CO_REF');
1750 hr_utility.raise_error;
1751 END IF;
1752
1753 ELSE
1754
1755 OPEN csr_get_reference;
1756 FETCH csr_get_reference INTO v_value;
1757
1758 hr_utility.set_location('v_value :' ||v_value,16);
1759
1760 --v_value can't be null as we are fetching it from payroll run
1761 IF (l_reference is null
1762 or v_value <> l_reference
1763 )
1764 THEN
1765 hr_utility.set_location('Ref is changed since previous payroll run: Raise Error',17);
1766
1767 hr_utility.set_message(801, 'PAY_GB_78139_INVALID_CO_REF');
1768 hr_utility.set_message_token('REFERENCE', l_reference);
1769 hr_utility.raise_error;
1770 END IF;
1771
1772 CLOSE csr_get_reference;
1773 END IF;
1774
1775 CLOSE csr_get_results;
1776 END IF;
1777 END IF;
1778
1779 hr_utility.set_location('Leaving procedure pqp_gb_ad_ee.AU_VAL_REF_COURT_ORDER',18);
1780
1781 END AU_VAL_REF_COURT_ORDER;
1782
1783 --For bug 7294977: End
1784
1785 END pqp_gb_ad_ee;
[Home] [Help]