1 PACKAGE BODY PAY_US_ELEMENT_ENTRY_HOOK AS
2 /* $Header: pyuseehd.pkb 120.2 2006/04/04 23:59:10 rdhingra noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PAY_US_ELEMENT_ENTRY_HOOK
21 File Name : pyuseehd.pkb
22
23 Description : This package is called from the AFTER INSERT/UPDATE/DELETE
24 User Hooks. The following are the functionalities present
25 in User Hook
26
27 1. Create/Update/Delete Recurring Element Entries for
28 Augment Elements
29 2. Create Tax Records for the Employee if Jurisdiction
30 code is entered.
31 3. Create/Update/Delete Premium Recalc Element Entries for
32 Premium Elements
33
34 Change List
35 -----------
36 Name Date Version Bug Text
37 -------------- ----------- ------- ------- -----------------------------
38 rdhingra 22-Mar-2006 115.13 5042715 R12 Performance Fixes on cursors
39 c_get_nrec_mop_up_dates
40 c_get_rec_mop_up_dates
41 sackumar 17-Nov-2005 115.12 4728252 Introduced a check for jurisdiction_code < 11
42 in Create_tax_record procedure.
43 kvsankar 20-SEP-2005 115.11 FLSA Modified
44 update_premium_mop_up_element
45 and
46 create_premium_mop_up_element
47 to set the Mulitple input
48 value of the Adjustment
49 element.
50 rdhingra 16-SEP-2005 115.10 FLSA Correcting the file version in
51 history and arcs
52 rdhingra 16-SEP-2005 115.9 FLSA Changed update_premium_mop_up_element
53 and create_premium_mop_up_element to
54 take of creation/deletion of mopup
55 depending on date earned given
56 Changed message text in
57 create_premium_mop_up_element
58 asasthan 06-SEP-2005 115.7 FLSA Changed c_get_rec_mop_up_dates
59 kvsankar 30-AUG-2005 115.6 FLSA Corrected GSCC Errors
60 kvsankar 30-AUG-2005 115.6 FLSA Added code for creating
61 ' for FLSA Period Adjustment'
62 Recalc Element Entry
63 kvsankar 11-AUG-2005 115.5 FLSA Modified the Creator Type
64 to 'FL'
65 kvsankar 28-JUL-2005 115.4 FLSA Modified CHECK_AUGMENT_ELEM
66 procedure
67 kvsankar 27-JUL-2005 115.3 FLSA Removed code giving warning
68 message for Payroll change
69 kvsankar 27-JUL-2005 115.2 FLSA Incorporated Changes for
70 Penny Issue for Augments.
71 kvsankar 20-JUL-2005 115.1 Corrected GSCC errors and
72 warnings.
73 kvsankar 19-JUL-2005 115.0 FLSA Created
74 *****************************************************************************/
75
76 /******************************************************************************
77 Name : GET_DAILY_AMOUNT
78 Scope : LOCAL
79 Description : This function is called to get the daily amount that will
80 be entered in the recurring element entry created for the
81 Augment element.
82 ******************************************************************************/
83 FUNCTION GET_DAILY_AMOUNT(
84 p_assignment_id in number
85 ,p_start_date in date
86 ,p_end_date in date
87 ,p_inp_value_name in varchar2
88 ,p_inp_value in number
89 ) RETURN number IS
90
91 ln_daily_rate number;
92 ln_no_of_days number;
93 BEGIN
94 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.GET_DAILY_AMOUNT');
95
96 -- Initialization Code
97 ln_daily_rate := 0;
98
99 -- Get the number of days between Start Date and End Date
100 ln_no_of_days := p_end_date - p_start_date + 1;
101 ln_daily_rate := round((nvl(p_inp_value,0)/ln_no_of_days),2);
102
103 hr_utility.trace('DAILY AMOUNT = ' || ln_daily_rate);
104 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.GET_DAILY_AMOUNT');
105 return ln_daily_rate ;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 return NULL;
110 END GET_DAILY_AMOUNT;
111
112 /******************************************************************************
113 Name : CHECK_AUGMENT_ELEM
114 Scope : LOCAL
115 Description : This function is called to check whether the element getting
116 added is an Augment element.
117 ******************************************************************************/
118 FUNCTION CHECK_AUGMENT_ELEM(
119 p_assignment_id in number
120 ,p_element_entry_id in number
121 ,p_effective_start_date in date
122 ,p_element_name out nocopy varchar2
123 ,p_business_group_id out nocopy number
124 ) RETURN boolean IS
125
126 -- Cursor to fetch Earned Start and Earned End Date
127 cursor c_check_aug_entry is
128 select petf.element_name
129 ,petf.business_group_id
130 ,pivf.name
131 ,peevf.screen_entry_value
132 from pay_element_entries_f peef
133 ,pay_element_types_f petf
134 ,pay_element_entry_values_f peevf
135 ,pay_input_values_f pivf
136 ,pay_status_processing_rules_f psprf
137 ,ff_formulas_f fff
138 ,pay_element_classifications pec
139 where peef.element_entry_id = p_element_entry_id
140 and petf.element_type_id = peef.element_type_id
141 and petf.processing_type = 'N'
142 and psprf.element_type_id = petf.element_type_id
143 and fff.formula_id = psprf.formula_id
144 and fff.formula_name like '%_FLAT_AMOUNT_NRRWOSI'
145 and peevf.element_entry_id = peef.element_entry_id
146 and pivf.element_type_id = peef.element_type_id
147 and peevf.input_value_id = pivf.input_value_id
148 and upper(pivf.name) in ('EARNED START DATE',
149 'EARNED END DATE')
150 and pec.classification_id = petf.classification_id
151 and pec.classification_name = 'Supplemental Earnings'
152 and pec.legislation_code = 'US'
153 and p_effective_start_date between peef.effective_start_date
154 and peef.effective_end_date
155 and p_effective_start_date between petf.effective_start_date
156 and petf.effective_end_date
157 and p_effective_start_date between peevf.effective_start_date
158 and peevf.effective_end_date
159 and p_effective_start_date between pivf.effective_start_date
160 and pivf.effective_end_date
161 and p_effective_start_date between psprf.effective_start_date
162 and psprf.effective_end_date;
163
164 -- Cursor to fetch Assignment Start Date
165 cursor c_check_assignment_validity(c_assignment_id number
166 ,c_start_date date) is
167 select 1
168 from per_assignments_f paf
169 where paf.assignment_id = c_assignment_id
170 and paf.effective_start_date <= c_start_date;
171
172 -- Cursor to get Period Start and End Dates
173 cursor c_get_period_dates(c_assignment_id number
174 ,c_effective_start_date date) is
175 select ptp.start_date
176 ,ptp.end_date
177 from per_assignments_f paf,
178 per_time_periods ptp
179 where paf.assignment_id = c_assignment_id
180 and ptp.payroll_id = paf.payroll_id
181 and c_effective_start_date between paf.effective_start_date
182 and paf.effective_end_date
183 and c_effective_start_date between ptp.start_date
184 and ptp.end_date;
185
186 lv_inp_val_name VARCHAR2(100);
187 lv_screen_entry_value VARCHAR2(100);
188 ln_count NUMBER;
189 ln_dummy_var NUMBER;
190 lb_aug_flag BOOLEAN;
191 ld_rec_ele_start_date DATE;
192 ld_rec_ele_end_date DATE;
193
194 BEGIN
195 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.CHECK_AUGMENT_ELEM');
196
197 ln_count := 0;
198 ld_rec_ele_end_date := NULL;
199 ld_rec_ele_start_date := NULL;
200
201 open c_check_aug_entry;
202 loop
203 fetch c_check_aug_entry into p_element_name
204 ,p_business_group_id
205 ,lv_inp_val_name
206 ,lv_screen_entry_value;
207 exit when c_check_aug_entry%NOTFOUND;
208
209 hr_utility.trace('Element Name = ' || p_element_name);
210 hr_utility.trace('Business Group ID = ' || p_business_group_id);
211 hr_utility.trace(lv_inp_val_name || ' = ' || lv_screen_entry_value);
212
213 ln_count := ln_count + 1;
214
215 if (upper(lv_inp_val_name) = 'EARNED START DATE'
216 AND lv_screen_entry_value IS NOT NULL) then
217 ld_rec_ele_start_date :=
218 fnd_date.canonical_to_date(lv_screen_entry_value);
219 elsif (upper(lv_inp_val_name) = 'EARNED END DATE'
220 AND lv_screen_entry_value IS NOT NULL) then
221 ld_rec_ele_end_date :=
222 fnd_date.canonical_to_date(lv_screen_entry_value);
223 end if; -- if (upper(lv_inp_val_name) ....
224 end loop;
225 close c_check_aug_entry;
226
227 -- Check for Augment Element
228 if ln_count < 2 then
229 lb_aug_flag := FALSE;
230 else
231 lb_aug_flag := TRUE;
232 end if; -- if ln_count < 2
233
234 if lb_aug_flag then
235 -- Checking for all ERROR conditions for the Augment element
236 if (ld_rec_ele_start_date IS NULL
237 AND ld_rec_ele_end_date IS NOT NULL) then
238 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
239 hr_utility.set_message_token('FORMULA_TEXT',
240 'Please specify Earned Start Date');
241 hr_utility.raise_error;
242 elsif (ld_rec_ele_start_date IS NOT NULL
243 AND ld_rec_ele_end_date IS NULL) then
244 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
245 hr_utility.set_message_token('FORMULA_TEXT',
246 'Please specify Earned End Date');
247 hr_utility.raise_error;
248 elsif (ld_rec_ele_start_date IS NOT NULL
249 AND ld_rec_ele_end_date IS NOT NULL) then
250 if (ld_rec_ele_end_date < ld_rec_ele_start_date) then
251 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
252 hr_utility.set_message_token('FORMULA_TEXT',
253 'Please specify Earned End Date greater than Earned Start Date');
254 hr_utility.raise_error;
255 end if;
256 else
257 -- Both the Start Date and End Date are NULL
258 open c_get_period_dates(p_assignment_id
259 ,p_effective_start_date);
260 fetch c_get_period_dates into ld_rec_ele_start_date
261 ,ld_rec_ele_end_date;
262 close c_get_period_dates;
263 hr_utility.trace('Updating the Element Entry values for the NR Element');
264 -- Updating the Start Date to the Payroll Start Date
265 update pay_element_entry_values_f peev
266 set screen_entry_value = fnd_date.date_to_canonical
267 (ld_rec_ele_start_date)
268 where element_entry_value_id =
269 (select distinct peev1.element_entry_value_id
270 from pay_element_entry_values_f peev1,
271 pay_element_entries_f peef,
272 pay_input_values_f pivf
273 where peef.element_entry_id = p_element_entry_id
274 and pivf.element_type_id = peef.element_type_id
275 and upper(pivf.name) = 'EARNED START DATE'
276 and peev1.element_entry_id = peef.element_entry_id
277 and peev1.input_value_id = pivf.input_value_id);
278
279 -- Updating the End Date to the Payroll End Date
280 update pay_element_entry_values_f peev
281 set screen_entry_value = fnd_date.date_to_canonical
282 (ld_rec_ele_end_date)
283 where element_entry_value_id =
284 (select distinct peev1.element_entry_value_id
285 from pay_element_entry_values_f peev1,
286 pay_element_entries_f peef,
287 pay_input_values_f pivf
288 where peef.element_entry_id = p_element_entry_id
289 and pivf.element_type_id = peef.element_type_id
290 and upper(pivf.name) = 'EARNED END DATE'
291 and peev1.element_entry_id = peef.element_entry_id
292 and peev1.input_value_id = pivf.input_value_id);
293 end if; -- if (ld_rec_ele_start_date ....
294
295 -- Raise Error if the assignment is not valid as of Start Date
296 open c_check_assignment_validity(p_assignment_id
297 ,ld_rec_ele_start_date);
298 fetch c_check_assignment_validity into ln_dummy_var;
299 if c_check_assignment_validity%NOTFOUND then
300 close c_check_assignment_validity;
301 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
302 hr_utility.set_message_token('FORMULA_TEXT',
303 'Assignment is not valid as of Earned Start Date');
304 hr_utility.raise_error;
305 end if;
306 close c_check_assignment_validity;
307
308 hr_utility.trace('BASE ELEMENT NAME = ' || p_element_name);
309 hr_utility.trace('BUSINESS GROUP ID = ' || p_business_group_id);
310 end if; -- if lb_aug_flag
311
312 return lb_aug_flag;
313 END CHECK_AUGMENT_ELEM;
314
315 /******************************************************************************
316 Name : CHECK_PREMIUM_ELEM
317 Scope : LOCAL
318 Description : This function is used for checking whether the element is a
319 PREMIUM element.
320 ******************************************************************************/
321 FUNCTION CHECK_PREMIUM_ELEM(
322 p_element_entry_id in number
323 ,p_effective_start_date in date
324 ,p_element_name out nocopy varchar2
325 ,p_processing_type out nocopy varchar2
326 ,p_business_group_id out nocopy number
327 ) RETURN BOOLEAN IS
328
329 cursor c_check_prem_elem(c_element_entry_id number
330 ,c_effective_start_date date) is
331 select petf.element_name
332 ,petf.business_group_id
333 ,petf.processing_type
334 from pay_element_entries_f peef
335 ,pay_element_types_f petf
336 ,pay_status_processing_rules_f psprf
337 ,ff_formulas_f fff
338 where peef.element_entry_id = c_element_entry_id
339 and petf.element_type_id = peef.element_type_id
340 and psprf.element_type_id = petf.element_type_id
341 and fff.formula_id = psprf.formula_id
342 and fff.formula_name like '%_PREMIUM'
343 and c_effective_start_date between peef.effective_start_date
344 and peef.effective_end_date
345 and c_effective_start_date between petf.effective_start_date
346 and petf.effective_end_date
347 and c_effective_start_date between psprf.effective_start_date
348 and psprf.effective_end_date;
349
350 ln_business_group_id number;
351 lv_element_name varchar2(100);
352 lv_processing_type varchar2(10);
353 lb_prem_flag boolean;
354
355 BEGIN
356
357 hr_utility.trace('Inside CHECK_PREMIUM_ELEM');
358 lb_prem_flag := FALSE;
359 p_element_name := NULL;
360 p_processing_type := NULL;
361 p_business_group_id := NULL;
362
363 -- Check for PREMIUM Element
364 open c_check_prem_elem(p_element_entry_id
365 ,p_effective_start_date);
366 fetch c_check_prem_elem into lv_element_name
367 ,ln_business_group_id
368 ,lv_processing_type;
369 if c_check_prem_elem%FOUND THEN
370 hr_utility.trace('Premium Element');
371 lb_prem_flag := TRUE;
372 p_element_name := lv_element_name;
373 p_processing_type := lv_processing_type;
374 p_business_group_id := ln_business_group_id;
375 end if;
376 close c_check_prem_elem;
377
378 return lb_prem_flag;
379 END CHECK_PREMIUM_ELEM;
380
381 /******************************************************************************
382 Name : DELETE_DEPENDENT_ENTRIES
383 Scope : LOCAL
384 Description : This procedure is used to delete the element entry dependent
385 on current element entry.
386 ******************************************************************************/
387 PROCEDURE DELETE_DEPENDENT_ENTRIES(
388 p_element_entry_id in number
389 ,p_assignment_id in number) IS
390
391 -- Cursor to get the Recurring element entry id using
392 -- Cretor ID
393 cursor c_get_ele_entry_id(c_element_entry_id varchar2
394 ,c_assignment_id number) is
395 select max(peef.element_entry_id)
396 ,min(peef.effective_start_date)
397 from pay_element_entries_f peef
398 where peef.creator_id = c_element_entry_id
399 and peef.assignment_id = c_assignment_id
400 and peef.creator_type = 'FL'
401 group by peef.element_entry_id
402 order by peef.element_entry_id;
403
404 ln_ele_entry_id NUMBER;
405 ld_del_start_date DATE;
406
407 BEGIN
408 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_DEPENDENT_ENTRIES');
409 hr_utility.trace('P_ELEMENT_ENTRY_ID = ' ||p_element_entry_id);
410 hr_utility.trace('P_ASSIGNMENT_ID = ' || p_assignment_id);
411
412 open c_get_ele_entry_id(p_element_entry_id
413 ,p_assignment_id);
414 loop
415 -- Loop through all Element Entries and delete them
416 fetch c_get_ele_entry_id into ln_ele_entry_id
417 ,ld_del_start_date;
418 exit when c_get_ele_entry_id%NOTFOUND;
419
420 hr_utility.trace('Deleting Element Entry = ' || ln_ele_entry_id);
421 hr_utility.trace('Deletion Date = ' || ld_del_start_date);
422 hr_entry_api.delete_element_entry (
423 p_dt_delete_mode => 'ZAP',
424 p_session_date => ld_del_start_date,
425 p_element_entry_id => ln_ele_entry_id);
426 end loop;
427 close c_get_ele_entry_id;
428 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_DEPENDENT_ENTRIES');
429 return;
430
431 EXCEPTION
432 --
433 WHEN others THEN
434 raise;
435 END DELETE_DEPENDENT_ENTRIES;
436
437 -----------------------------INSERT SECTION STARTS HERE------------------------
438
439 /******************************************************************************
440 Name : POPULATE_ELE_LINK
441 Scope : LOCAL
442 Description : This function is called to populate the PL/SQL table with
443 the dates and the element links to be used for creating
444 the recurring element entry.
445 ******************************************************************************/
446 PROCEDURE POPULATE_ELE_LINK(
447 p_assignment_id in number
448 ,p_augment_elem_name in varchar2
449 ,p_start_date in date
450 ,p_end_date in date
451 ,p_business_group_id in number
452 ,p_inp_value_name in varchar2
453 ,p_inp_value in number
454 ,p_rec_element_type_id in number
455 ) IS
456
457 -- Cursor to get the Element link details for the assignment
458 cursor c_get_link_details(c_element_type_id number
459 ,c_assignment_id number
460 ,c_effective_start_date date
461 ,c_effective_end_date date) is
462 select paf.effective_start_date Asgt_Start_Date
463 ,paf.effective_end_date Asgt_End_Date
464 ,pelf.effective_start_date Link_Start_Date
465 ,pelf.effective_end_date Link_End_Date
466 ,pelf.element_link_id Element_Link_Id
467 from pay_element_types_f petf,
468 pay_element_links_f pelf,
469 per_assignments_f paf
470 where petf.element_type_id = c_element_type_id
471 and petf.element_type_id = pelf.element_type_id
472 and paf.assignment_id = c_assignment_id
473 and c_effective_start_date <= paf.effective_end_date
474 and c_effective_end_date >= paf.effective_start_date
475 and c_effective_start_date <= pelf.effective_end_date
476 and c_effective_end_date >= pelf.effective_start_date
477 and (
478 (pelf.effective_start_date between paf.effective_start_date
479 and paf.effective_end_date)
480 or
481 (pelf.effective_end_date between paf.effective_start_date
482 and paf.effective_end_date)
483 or
484 (
485 pelf.effective_start_date < paf.effective_start_date
486 and
487 pelf.effective_end_date > paf.effective_end_date
488 )
489 )
490 and (
491 (pelf.payroll_id is not null and pelf.payroll_id = paf.payroll_id)
492 or
493 (pelf.link_to_all_payrolls_flag = 'Y' and paf.payroll_id is not null)
494 or
495 (pelf.payroll_id is null and pelf.link_to_all_payrolls_flag = 'N')
496 )
497 and (
498 pelf.organization_id = paf.organization_id
499 or
500 pelf.organization_id is null
501 )
502 and (
503 pelf.position_id = paf.position_id
504 or
505 pelf.position_id is null
506 )
507 and (
508 pelf.job_id = paf.job_id
509 or
510 pelf.job_id is null
511 )
512 and (
513 pelf.grade_id = paf.grade_id
514 or
515 pelf.grade_id is null
516 )
517 and (
518 pelf.location_id = paf.location_id
519 or
520 pelf.location_id is null
521 )
522 and (
523 pelf.pay_basis_id = paf.pay_basis_id
524 or
525 pelf.pay_basis_id is null
526 )
527 and (
528 pelf.employment_category = paf.employment_category
529 or
530 pelf.employment_category is null
531 )
532 and (
533 pelf.people_group_id is null
534 or
535 exists (
536 select 1
537 from pay_assignment_link_usages_f usage
538 where usage.assignment_id = paf.assignment_id
539 and usage.element_link_id = pelf.element_link_id
540 )
541 )
542 order by Asgt_Start_Date, Link_Start_Date;
543
544 ln_payroll_count number;
545 ln_element_link_id number;
546 ln_no_of_days number;
547 ln_daily_amount number;
548 ld_asgt_eff_start_date date;
549 ld_asgt_eff_end_date date;
550 ld_link_eff_start_date date;
551 ld_link_eff_end_date date;
552 ld_dummy_start_date date;
553
554 BEGIN
555 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.POPULATE_ELE_LINK');
556 hr_utility.trace('p_assignment_id = ' || p_assignment_id);
557 hr_utility.trace('p_start_date = ' || p_start_date);
558 hr_utility.trace('p_end_date = ' || p_end_date);
559 hr_utility.trace('p_business_group_id = ' || p_business_group_id);
560 hr_utility.trace('p_inp_value_name = ' || p_inp_value_name);
561 hr_utility.trace('p_inp_value = ' || p_inp_value);
562 hr_utility.trace('p_rec_element_type_id = ' || p_rec_element_type_id);
563
564 ld_dummy_start_date := p_start_date;
565 gn_ele_ent_num := 0;
566 ln_element_link_id := -9999;
567 gn_link_id_tbl(0) := 0;
568
569 -- Find the PER Period value for the recurring element
570 gn_daily_amount := GET_DAILY_AMOUNT(
571 p_assignment_id => p_assignment_id
572 ,p_start_date => p_start_date
573 ,p_end_date => p_end_date
574 ,p_inp_value_name => p_inp_value_name
575 ,p_inp_value => p_inp_value);
576
577 open c_get_link_details(p_rec_element_type_id
578 ,p_assignment_id
579 ,p_start_date
580 ,p_end_date);
581 --
582 loop
583 fetch c_get_link_details into ld_asgt_eff_start_date
584 ,ld_asgt_eff_end_date
585 ,ld_link_eff_start_date
586 ,ld_link_eff_end_date
587 ,ln_element_link_id;
588 exit when c_get_link_details%NOTFOUND;
589
590 -- If the assignment itself is not valid on the Start date
591 -- then throw an error saying Assignment is not valid on Start Date
592 if (gn_ele_ent_num = 0 AND ld_asgt_eff_start_date > p_start_date) then
593 close c_get_link_details;
594 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
595 hr_utility.set_message_token('FORMULA_TEXT',
596 'Assignment is not Valid as of Earned Start Date');
597 hr_utility.raise_error;
598 end if; -- if (gn_ele_ent_num = 0 AND ....
599
600 if ln_element_link_id <> gn_link_id_tbl(gn_ele_ent_num) then
601 gn_ele_ent_num := gn_ele_ent_num + 1;
602 gn_link_id_tbl(gn_ele_ent_num) := ln_element_link_id;
603 if ld_asgt_eff_end_date > ld_link_eff_end_date then
604 gd_start_date_tbl(gn_ele_ent_num) := ld_dummy_start_date;
605 gd_end_date_tbl(gn_ele_ent_num) := ld_link_eff_end_date;
606 else
607 gd_start_date_tbl(gn_ele_ent_num) := ld_dummy_start_date;
608 gd_end_date_tbl(gn_ele_ent_num) := ld_asgt_eff_end_date;
609 end if; -- if ld_asgt_eff_end_date > ....
610
611 if gd_end_date_tbl(gn_ele_ent_num) > p_end_date then
612 gd_end_date_tbl(gn_ele_ent_num) := p_end_date;
613 else
614 ld_dummy_start_date := gd_end_date_tbl(gn_ele_ent_num) + 1;
615 end if; -- if gd_end_date_tbl(gn_ele_ent_num) ....
616 hr_utility.trace('gn_ele_ent_num = ' || gn_ele_ent_num);
617 hr_utility.trace('Asgt Eff End Date = ' || ld_asgt_eff_end_date);
618 hr_utility.trace('Link Eff End Date = ' || ld_link_eff_end_date);
619 hr_utility.trace('Global Start Date = ' ||
620 gd_start_date_tbl(gn_ele_ent_num));
621 hr_utility.trace('Global End Date = ' ||
622 gd_end_date_tbl(gn_ele_ent_num));
623 hr_utility.trace('Link ID = ' || gn_link_id_tbl(gn_ele_ent_num));
624 else
625 if ld_asgt_eff_end_date > ld_link_eff_end_date then
626 gd_end_date_tbl(gn_ele_ent_num) := ld_link_eff_end_date;
627 else
628 gd_end_date_tbl(gn_ele_ent_num) := ld_asgt_eff_end_date;
629 end if; -- if ld_asgt_eff_end_date ....
630
631 if gd_end_date_tbl(gn_ele_ent_num) > p_end_date then
632 gd_end_date_tbl(gn_ele_ent_num) := p_end_date;
633 else
634 ld_dummy_start_date := gd_end_date_tbl(gn_ele_ent_num) + 1;
635 end if; -- if gd_end_date_tbl(gn_ele_ent_num) ....
636
637 hr_utility.trace('gn_ele_ent_num = ' || gn_ele_ent_num);
638 hr_utility.trace('Global Start Date = ' ||
639 gd_start_date_tbl(gn_ele_ent_num));
640 hr_utility.trace('Global End Date = ' ||
641 gd_end_date_tbl(gn_ele_ent_num));
642 hr_utility.trace('Link ID = ' || gn_link_id_tbl(gn_ele_ent_num));
643 end if; -- if ln_element_link_id <> ....
644
645 end loop;
646 close c_get_link_details;
647
648 if gn_ele_ent_num = 0 then
649 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
650 hr_utility.set_message_token('FORMULA_TEXT',
651 'The assignment is not eligible for ' ||
652 p_augment_elem_name || ' for FLSA Calc.' ||
653 ' Please link the element to make it eligible to the assignment.');
654 hr_utility.raise_error;
655 elsif (gd_end_date_tbl(gn_ele_ent_num) < p_end_date) then
656 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
657 hr_utility.set_message_token('FORMULA_TEXT',
658 'The assignment is not eligible for ' ||
659 p_augment_elem_name || ' for FLSA Calc.' ||
660 ' Please link the element to make it eligible to the assignment.');
661 hr_utility.raise_error;
662 end if; -- if gn_ele_ent_num = 0
663 return;
664 END POPULATE_ELE_LINK;
665
666 /******************************************************************************
667 Name : CREATE_RECUR_ELEM_ENTRY
668 Scope : LOCAL
669 Description : This function is used for creating the recurring element
670 entry for an Augment element.
671 ******************************************************************************/
672 PROCEDURE CREATE_RECUR_ELEM_ENTRY(
673 p_element_entry_id in number
674 ,p_effective_start_date in date
675 ,p_effective_end_date in date
676 ,p_assignment_id in number
677 ,p_element_link_id in number
678 ,p_original_entry_id in number
679 ,p_creator_type in varchar2
680 ,p_entry_type in varchar2
681 ,p_entry_information_category in varchar2) IS
682
683 -- Cursor to get all input value details
684 cursor c_get_elem_inp_value_details(c_element_entry_id number
685 ,c_effective_date date) is
686 select pivf.name
687 ,peev.screen_entry_value
688 ,pivf.lookup_type
689 from pay_input_values_f pivf
690 ,pay_element_entry_values_f peev
691 where peev.element_entry_id = c_element_entry_id
692 and peev.input_value_id = pivf.input_value_id
693 and c_effective_date between peev.effective_start_date
694 and peev.effective_end_date
695 and c_effective_date between pivf.effective_start_date
696 and pivf.effective_end_date
697 order by pivf.name;
698
699 -- Cursor to get Recurring Element inp value details
700 cursor c_get_rec_elem_inp_val_det(c_element_name varchar2
701 ,c_business_grp_id number
702 ,c_efective_start_date date) is
703 select petf.element_type_id
704 ,pivf.input_value_id
705 ,pivf.name
706 from pay_element_types_f petf
707 ,pay_input_values_f pivf
708 where petf.element_name like c_element_name || ' for FLSA Calc'
709 and petf.business_group_id = c_business_grp_id
710 and pivf.element_type_id = petf.element_type_id
711 and c_efective_start_date between pivf.effective_start_date
712 and pivf.effective_end_date
713 and c_efective_start_date between petf.effective_start_date
714 and petf.effective_end_date;
715
716 -- Cursor to check if the Job is FLSA Eligible
717 cursor c_check_flsa_elig_job(c_assignment_id number
718 ,c_start_date date
719 ,c_end_date date) is
720 select 1
721 from per_jobs perj
722 ,per_jobs_tl perjtl
723 ,per_all_assignments_f paa
724 where paa.assignment_id = c_assignment_id
725 and c_start_date <= paa.effective_end_date
726 and c_end_date >= paa.effective_start_date
727 and nvl(perj.job_information3, 'EX') = 'NEX'
728 and paa.job_id = perj.job_id
729 and paa.job_id = perjtl.job_id
730 and userenv('lang') = perjtl.language;
731
732 -- Cursor to get Lookup Meaning
733 cursor c_get_lookup_value(c_lookup_type varchar2,
734 c_lookup_code varchar2) is
735 select meaning
736 from hr_lookups
737 where lookup_type = c_lookup_type
738 and lookup_code = c_lookup_code
739 and application_id = 800;
740
741
742 lv_element_name VARCHAR2(200);
743 lv_inp_value_name VARCHAR2(200);
744 lv_screen_entry_value VARCHAR2(200);
745 lv_lookup_type VARCHAR2(200);
746 lv_lookup_meaning VARCHAR2(200);
747 lv_inp_value_to_divide VARCHAR2(200);
748 ln_business_grp_id NUMBER;
749 ln_total_value NUMBER;
750 ln_per_period_value NUMBER;
751 ln_element_entry_id NUMBER;
752 ln_original_entry_id NUMBER;
753 ln_rec_element_type_id NUMBER;
754 ln_daily_amt_index NUMBER;
755 ln_rec_element_link_id NUMBER;
756 ln_no_of_days NUMBER;
757 ln_dummy_var NUMBER;
758 ld_rec_ele_start_date DATE;
759 ld_rec_ele_end_date DATE;
760 ld_dummy_end_date DATE;
761 lb_auth_flag BOOLEAN;
762 l_input_value_name_tbl varchar2_table;
763 l_input_value_id_tbl hr_entry.number_table;
764 l_entry_value_tbl hr_entry.varchar2_table;
765 lvr number; -- loop variable
766
767
768 BEGIN
769
770 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.CREATE_RECUR_ELEM_ENTRY');
771 hr_utility.trace('P_ELEMENT_ENTRY_ID = ' ||p_element_entry_id);
772 hr_utility.trace('P_EFFECTIVE_START_DATE = ' || p_effective_start_date);
773 hr_utility.trace('P_EFFECTIVE_END_DATE = ' || p_effective_end_date);
774 hr_utility.trace('P_ASSIGNMENT_ID = ' || p_assignment_id);
775 hr_utility.trace('P_ELEMENT_LINK_ID = ' || p_element_link_id);
776 hr_utility.trace('P_ORIGINAL_ENTRY_ID = ' || p_original_entry_id);
777 hr_utility.trace('P_CREATOR_TYPE = ' || p_creator_type);
778 hr_utility.trace('P_ENTRY_TYPE = ' || p_entry_type);
779 hr_utility.trace('P_ENTRY_INFORMATION_CATEGORY = ' || p_entry_information_category);
780
781 -- Initialization Code
782 ln_business_grp_id := NULL;
783 ld_rec_ele_start_date := NULL;
784 ld_rec_ele_end_date := NULL;
785 ln_rec_element_type_id := NULL;
786 lvr := 0;
787 lv_lookup_type := NULL;
788 ln_daily_amt_index := 0;
789 ld_dummy_end_date := fnd_date.canonical_to_date('4712/12/31');
790
791
792 -- Check whether this element entry is an augment element.
793 -- If not then we need not do anyhting additional
794 lb_auth_flag := CHECK_AUGMENT_ELEM(p_assignment_id
795 ,p_element_entry_id
796 ,p_effective_start_date
797 ,lv_element_name
798 ,ln_business_grp_id);
799
800 if NOT(lb_auth_flag) then
801 return;
802 end if;
803
804 -- Query the input values of the Recurring Element
805 -- and set the corresponding value to NULL
806 open c_get_rec_elem_inp_val_det(lv_element_name
807 ,ln_business_grp_id
808 ,p_effective_start_date);
809 loop
810 lvr := lvr + 1;
811 fetch c_get_rec_elem_inp_val_det into ln_rec_element_type_id
812 ,l_input_value_id_tbl(lvr)
813 ,l_input_value_name_tbl(lvr);
814 exit when c_get_rec_elem_inp_val_det%NOTFOUND;
815 hr_utility.trace('Rec Input Value Name = ' || l_input_value_name_tbl(lvr));
816 if upper(l_input_value_name_tbl(lvr)) = 'DAILY AMOUNT' then
817 ln_daily_amt_index := lvr;
818 end if;
819 l_entry_value_tbl(lvr) := NULL;
820 end loop;
821 close c_get_rec_elem_inp_val_det;
822
823 -- Fetch the value of all input values and provide the same value to the
824 -- corresponding input value of the recurring element
825 open c_get_elem_inp_value_details(p_element_entry_id,
826 p_effective_start_date);
827 loop
828 fetch c_get_elem_inp_value_details into lv_inp_value_name
829 ,lv_screen_entry_value
830 ,lv_lookup_type;
831 exit when c_get_elem_inp_value_details%NOTFOUND;
832 hr_utility.trace('Input Value Name = ' || lv_inp_value_name);
833 hr_utility.trace('Input Value = ' || lv_screen_entry_value);
834 hr_utility.trace('Look Up Type = ' || lv_lookup_type);
835
836 if upper(lv_inp_value_name) = 'EARNED START DATE' then
837 if lv_screen_entry_value is null then
838 ld_rec_ele_start_date := NULL;
839 exit;
840 else
841 ld_rec_ele_start_date :=
842 fnd_date.canonical_to_date(lv_screen_entry_value);
843 hr_utility.trace('Start Date = ' || to_char(ld_rec_ele_start_date,'DD-MON-YYYY'));
844 end if; -- if lv_screen_entry_value is null
845 elsif upper(lv_inp_value_name) = 'EARNED END DATE' then
846 if lv_screen_entry_value is null then
847 ld_rec_ele_end_date := NULL;
848 exit;
849 else
850 ld_rec_ele_end_date :=
851 fnd_date.canonical_to_date(lv_screen_entry_value);
852 hr_utility.trace('End Date = ' || to_char(ld_rec_ele_end_date,'DD-MON-YYYY'));
853 end if; -- if lv_screen_entry_value is null
854 elsif (upper(lv_inp_value_name) in ('AMOUNT')) THEN
855 lv_inp_value_to_divide := lv_inp_value_name;
856 ln_total_value := to_number(nvl(lv_screen_entry_value, '0'));
857 else
858 -- The else logic below is commented as the recurring element
859 -- will always have one input value namely 'Daily Amount'
860 -- for Augment which needs to be specified.
861 -- This code can be reused if we have to copy over values
862 -- from the Non recurrning element to Recurring element
863 -- in future.
864 hr_utility.trace('Commented Else part as not required');
865 /*
866 for i in l_input_value_name_tbl.first..l_input_value_name_tbl.last
867 loop
868 if l_input_value_name_tbl(i) = lv_inp_value_name then
869 hr_utility.trace('Input Value Found');
870 if lv_lookup_type is NOT NULL then
871 open c_get_lookup_value(lv_lookup_type
872 ,lv_screen_entry_value);
873 fetch c_get_lookup_value into lv_lookup_meaning;
874 close c_get_lookup_value;
875 hr_utility.trace('Lookup Meaning = ' || lv_lookup_meaning);
876 l_entry_value_tbl(i) := lv_lookup_meaning;
877 lv_lookup_type := NULL;
878 else
879 l_entry_value_tbl(i) := lv_screen_entry_value;
880 end if; -- if lv_lookup_type is NOT NULL
881 end if; -- if l_input_value_name_tbl(i) ....
882 end loop;
883 */
884 end if; -- if upper(lv_inp_value_name)
885 end loop;
886 close c_get_elem_inp_value_details;
887
888 -- If Either the start date or the End date is null, then we should not
889 -- create the recurring element entry
890 if ((ld_rec_ele_start_date is NULL) or (ld_rec_ele_end_date is NULL)) then
891 return;
892 end if;
893
894 -- Check whether the person has a FLSA Eligible job between the
895 -- Start Date and End Date
896 open c_check_flsa_elig_job(p_assignment_id
897 ,ld_rec_ele_start_date
898 ,ld_rec_ele_end_date);
899 fetch c_check_flsa_elig_job into ln_dummy_var;
900 if c_check_flsa_elig_job%NOTFOUND then
901 hr_utility.trace('Job is NOT FLSA Eligible');
902 close c_check_flsa_elig_job;
903 return;
904 end if;
905 close c_check_flsa_elig_job;
906
907 POPULATE_ELE_LINK(p_assignment_id => p_assignment_id
908 ,p_augment_elem_name => lv_element_name
909 ,p_start_date => ld_rec_ele_start_date
910 ,p_end_date => ld_rec_ele_end_date
911 ,p_business_group_id => ln_business_grp_id
912 ,p_inp_value_name => lv_inp_value_to_divide
913 ,p_inp_value => ln_total_value
914 ,p_rec_element_type_id => ln_rec_element_type_id
915 );
916
917
918 -- Create Recurrin Element Entry
919 for lvr in 1..gn_ele_ent_num loop
920 hr_utility.trace('Creating Recurring Element Entries');
921 hr_utility.trace('Element Start Date = ' || gd_start_date_tbl(lvr));
922 hr_utility.trace('Element End Date = ' || gd_end_date_tbl(lvr));
923 hr_utility.trace('Entry Daily Amount = ' || gn_daily_amount);
924
925 l_entry_value_tbl(ln_daily_amt_index) := gn_daily_amount;
926 ld_dummy_end_date := gd_end_date_tbl(lvr);
927
928 hr_entry_api.insert_element_entry (
929 p_effective_start_date => gd_start_date_tbl(lvr)
930 ,p_effective_end_date => ld_dummy_end_date
931 ,p_element_entry_id => ln_element_entry_id
932 ,p_original_entry_id => ln_original_entry_id
933 ,p_assignment_id => p_assignment_id
934 ,p_element_link_id => gn_link_id_tbl(lvr)
935 ,p_creator_type => 'FL'
936 ,p_creator_id => p_element_entry_id
937 ,p_entry_type => 'E' -- Normal Entry
938 ,p_entry_information_category => null
939 --
940 -- Element Entry Values Table
941 --
942 ,p_num_entry_values => l_entry_value_tbl.count()
943 ,p_input_value_id_tbl => l_input_value_id_tbl
944 ,p_entry_value_tbl => l_entry_value_tbl
945 );
946
947 -- End dating the element using gd_end_date_tbl(lvr)
948 -- as ld_dummy_end_date gets overwritten by the
949 -- previous call
950 if ld_dummy_end_date <> gd_end_date_tbl(lvr) then
951 hr_entry_api.delete_element_entry (
952 p_dt_delete_mode => 'DELETE',
953 p_session_date => gd_end_date_tbl(lvr),
954 p_element_entry_id => ln_element_entry_id);
955 end if;
956
957 -- Update the last Element Entry for Penny issue if required
958 if lvr = gn_ele_ent_num then
959 ln_no_of_days := ld_rec_ele_end_date - ld_rec_ele_start_date + 1;
960 if ln_total_value <> (ln_no_of_days * gn_daily_amount) then
961 l_entry_value_tbl(ln_daily_amt_index) :=
962 ln_total_value - ((ln_no_of_days - 1) * gn_daily_amount);
963 if gd_start_date_tbl(lvr) = gd_end_date_tbl(lvr) then
964 -- Update the Element Entry values in Date-track mode
965 hr_entry_api.update_element_entry
966 (p_dt_update_mode => 'CORRECTION'
967 ,p_session_date => gd_end_date_tbl(lvr)
968 ,p_element_entry_id => ln_element_entry_id
969 ,p_num_entry_values => l_entry_value_tbl.count()
970 ,p_input_value_id_tbl => l_input_value_id_tbl
971 ,p_entry_value_tbl => l_entry_value_tbl);
972 else
973 -- Update the Element Entry values in Date-track mode
974 hr_entry_api.update_element_entry
975 (p_dt_update_mode => 'UPDATE'
976 ,p_session_date => gd_end_date_tbl(lvr)
977 ,p_element_entry_id => ln_element_entry_id
978 ,p_num_entry_values => l_entry_value_tbl.count()
979 ,p_input_value_id_tbl => l_input_value_id_tbl
980 ,p_entry_value_tbl => l_entry_value_tbl);
981 end if; -- if gd_start_date_tbl(lvr)
982 end if; -- if ln_total_value <>
983 end if; -- if lvr = gn_ele_ent_num
984 end loop; -- for lvr in 1..gn_ele_ent_num
985
986 -- Reset Global Variables
987 gd_start_date_tbl.delete;
988 gd_end_date_tbl.delete;
989 gn_link_id_tbl.delete;
990 gn_ele_ent_num := NULL;
991 gn_daily_amount := NULL;
992 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.CREATE_RECUR_ELEM_ENTRY');
993 return;
994
995 EXCEPTION
996 --
997 WHEN others THEN
998 raise;
999 END CREATE_RECUR_ELEM_ENTRY;
1000
1001 /******************************************************************************
1002 Name : CREATE_TAX_RECORDS
1003 Scope : LOCAL
1004 Description : This procedure is used for create Tax records for the
1005 Jursidiction entered at element entry level.
1006 ******************************************************************************/
1007 PROCEDURE CREATE_TAX_RECORDS(
1008 p_element_entry_id in number
1009 ,p_effective_start_date in date
1010 ,p_effective_end_date in date
1011 ,p_assignment_id in number
1012 ,p_element_link_id in number
1013 ,p_original_entry_id in number
1014 ,p_creator_type in varchar2
1015 ,p_entry_type in varchar2
1016 ,p_entry_information_category in varchar2) IS
1017
1018 -- Cursor to get State Code associated with the jurisdiction
1019 -- entered in the element entry
1020 cursor c_get_jursidiction(c_element_entry_id number,
1021 c_effective_date date) is
1022 select rtrim(ltrim(peev.screen_entry_value))
1023 from pay_element_entry_values_f peev
1024 ,pay_element_entries_f peef
1025 ,pay_input_values_f pivf
1026 where peef.element_entry_id = c_element_entry_id
1027 and pivf.element_type_id = peef.element_type_id
1028 and upper(pivf.name) = 'JURISDICTION'
1029 and peev.element_entry_id = peef.element_entry_id
1030 and peev.input_value_id = pivf.input_value_id
1031 and c_effective_date between peef.effective_start_date
1032 and peef.effective_end_date
1033 and c_effective_date between pivf.effective_start_date
1034 and pivf.effective_end_date
1035 and c_effective_date between peev.effective_start_date
1036 and peev.effective_end_date;
1037
1038 -- Cursor to check the existence of State Tax Rule
1039 -- for a combination of Assignment and State
1040 cursor c_state_tax_rule(c_assignment_id number,
1041 c_state_code varchar2) is
1042 select 1
1043 from pay_us_emp_state_tax_rules_f sta
1044 where sta.assignment_id = c_assignment_id
1045 and sta.state_code = c_state_code;
1046
1047 -- Cursor to check the existence of County Tax Rule
1048 -- for a combination of Assignment, State and County
1049 cursor c_county_tax_rule(c_assignment_id number,
1050 c_state_code varchar2,
1051 c_county_code varchar2) is
1052 select 1
1053 from pay_us_emp_county_tax_rules_f cnt
1054 where cnt.assignment_id = c_assignment_id
1055 and cnt.state_code = c_state_code
1056 and cnt.county_code = c_county_code;
1057
1058
1059 -- Cursor to check the existence of City Tax Rule
1060 -- for a combination of Assignment, State, County and City
1061 cursor c_city_tax_rule(c_assignment_id number,
1062 c_state_code varchar2,
1063 c_county_code varchar2,
1064 c_city_code varchar2) is
1065 select 1
1066 from pay_us_emp_city_tax_rules_f cty
1067 where cty.assignment_id = p_assignment_id
1068 and cty.state_code = c_state_code
1069 and cty.county_code = c_county_code
1070 and cty.city_code = c_city_code;
1071
1072 -- Variable Declaration
1073 lv_jurisdiction_code VARCHAR2(100);
1074 lv_state_code VARCHAR2(10);
1075 lv_county_code VARCHAR2(10);
1076 lv_city_code VARCHAR2(10);
1077 ln_tmp_rule_id NUMBER;
1078 ln_ovn NUMBER;
1079 ln_dummy NUMBER;
1080 ld_eff_start_date DATE;
1081 ld_eff_end_date DATE;
1082
1083 BEGIN
1084 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.CREATE_TAX_RECORDS');
1085
1086 -- Initialization Code
1087 lv_state_code := NULL;
1088 lv_county_code := NULL;
1089 lv_city_code := NULL;
1090
1091 -- Get the State for which we have to create the State Tax Rules
1092 open c_get_jursidiction(p_element_entry_id
1093 ,p_effective_start_date);
1094 fetch c_get_jursidiction into lv_jurisdiction_code;
1095 close c_get_jursidiction;
1096
1097 -- check for school district bug no 4728252
1098 if length(lv_jurisdiction_code) < 11 then
1099 return;
1100 end if;
1101 -- Check if Jurisdiction is specified
1102 if lv_jurisdiction_code IS NOT NULL then
1103 -- Check if Tax Rule exists
1104 -- Create the State Tax Rule only if it does not exist
1105 lv_state_code := substr(lv_jurisdiction_code,1,2);
1106 if lv_state_code <> '00' then
1107 open c_state_tax_rule(p_assignment_id
1108 ,lv_state_code);
1109 fetch c_state_tax_rule into ln_dummy;
1110 if c_state_tax_rule%NOTFOUND then
1111 hr_utility.trace('Creating Tax Rule for State ' || lv_state_code);
1112 -- Create State Tax Records
1113 pay_state_tax_rule_api.create_state_tax_rule (
1114 p_effective_date => p_effective_start_date
1115 ,p_default_flag => 'Y'
1116 ,p_assignment_id => p_assignment_id
1117 ,p_state_code => lv_state_code
1118 ,p_emp_state_tax_rule_id => ln_tmp_rule_id
1119 ,p_object_version_number => ln_ovn
1120 ,p_effective_start_date => ld_eff_start_date
1121 ,p_effective_end_date => ld_eff_end_date
1122 );
1123 end if; -- if c_state_tax_rule%NOTFOUND
1124 close c_state_tax_rule;
1125
1126 -- Check if County Tax Rule exists
1127 -- Create the County Tax Rule only if it does not exist
1128 lv_county_code := substr(lv_jurisdiction_code,4,3);
1129 if lv_county_code <> '000' then
1130 open c_county_tax_rule(p_assignment_id
1131 ,lv_state_code
1132 ,lv_county_code);
1133 fetch c_county_tax_rule into ln_dummy;
1134 if c_county_tax_rule%NOTFOUND then
1135 hr_utility.trace('Creating Tax Rule for County '
1136 || lv_county_code);
1137 -- Create County Tax Records
1138 pay_county_tax_rule_api.create_county_tax_rule (
1139 p_effective_date => p_effective_start_date
1140 ,p_assignment_id => p_assignment_id
1141 ,p_state_code => lv_state_code
1142 ,p_county_code => lv_county_code
1143 ,p_additional_wa_rate => 0
1144 ,p_filing_status_code => '01'
1145 ,p_lit_additional_tax => 0
1146 ,p_lit_override_amount => 0
1147 ,p_lit_override_rate => 0
1148 ,p_withholding_allowances => 0
1149 ,p_lit_exempt => 'N'
1150 ,p_emp_county_tax_rule_id => ln_tmp_rule_id
1151 ,p_object_version_number => ln_ovn
1152 ,p_effective_start_date => ld_eff_start_date
1153 ,p_effective_end_date => ld_eff_end_date
1154 );
1155 end if; -- if c_county_tax_rule%NOTFOUND
1156 close c_county_tax_rule;
1157
1158 -- Check if County Tax Rule exists
1159 -- Create the County Tax Rule only if it does not exist
1160 lv_city_code := substr(lv_jurisdiction_code,8,4);
1161 if lv_city_code <> '0000' then
1162 open c_city_tax_rule(p_assignment_id
1163 ,lv_state_code
1164 ,lv_county_code
1165 ,lv_city_code);
1166 fetch c_city_tax_rule into ln_dummy;
1167 if c_city_tax_rule%NOTFOUND then
1168 hr_utility.trace('Creating Tax Rule for City ' ||
1169 lv_city_code);
1170 -- Create City Tax Records
1171 pay_city_tax_rule_api.create_city_tax_rule (
1172 p_effective_date => p_effective_start_date
1173 ,p_assignment_id => p_assignment_id
1174 ,p_state_code => lv_state_code
1175 ,p_county_code => lv_county_code
1176 ,p_city_code => lv_city_code
1177 ,p_additional_wa_rate => 0
1178 ,p_filing_status_code => '01'
1179 ,p_lit_additional_tax => 0
1180 ,p_lit_override_amount => 0
1181 ,p_lit_override_rate => 0
1182 ,p_withholding_allowances => 0
1183 ,p_lit_exempt => 'N'
1184 ,p_emp_city_tax_rule_id => ln_tmp_rule_id
1185 ,p_object_version_number => ln_ovn
1186 ,p_effective_start_date => ld_eff_start_date
1187 ,p_effective_end_date => ld_eff_end_date
1188 );
1189 end if; -- if c_city_tax_rule%NOTFOUND
1190 close c_city_tax_rule;
1191 end if; -- if lv_city_code <> '0000'
1192 end if; -- if lv_county_code <> '000'
1193 end if; -- if lv_state_code <> '00'
1194 end if; -- if lv_jurisdiction_code
1195 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.CREATE_TAX_RECORDS');
1196 return;
1197 END CREATE_TAX_RECORDS;
1198
1199 /******************************************************************************
1200 Name : CREATE_PREMIUM_MOP_UP_ELEMENT
1201 Scope : LOCAL
1202 Description : This function is used for creating the MOP UP element
1203 for Premium. This procedure creates a MOP UP element
1204 only if the FLSA Period crossed over Payroll Period in
1205 question
1206 ******************************************************************************/
1207 PROCEDURE CREATE_PREMIUM_MOP_UP_ELEMENT(
1208 p_element_entry_id in number
1209 ,p_effective_start_date in date
1210 ,p_effective_end_date in date
1211 ,p_assignment_id in number
1212 ,p_element_link_id in number
1213 ,p_original_entry_id in number
1214 ,p_creator_type in varchar2
1215 ,p_entry_type in varchar2
1216 ,p_entry_information_category in varchar2) IS
1217
1218 -- Get default time definition
1219 cursor c_get_def_time_def_id(c_time_def_name VARCHAR2
1220 ,c_legislation_code VARCHAR2) is
1221 select time_definition_id
1222 from pay_time_definitions
1223 where definition_name = c_time_def_name
1224 and legislation_code = c_legislation_code
1225 and business_group_id IS NULL;
1226
1227 cursor c_get_nrec_mop_up_dates(c_assignment_id NUMBER
1228 ,c_effective_start_date DATE
1229 ,c_date_earned DATE
1230 ,c_time_def_id NUMBER) is
1231 select /*+ use_nl(paf ptpp)*/
1232 ptpp.end_date + 1,
1233 ptpt.end_date
1234 from per_assignments_f paf
1235 ,per_time_periods ptpp
1236 ,per_time_periods ptpt
1237 where paf.assignment_id = c_assignment_id
1238 and NVL(c_date_earned,c_effective_start_date) between paf.effective_start_date
1239 and paf.effective_end_date
1240 and ptpp.payroll_id = paf.payroll_id
1241 and NVL(c_date_earned,c_effective_start_date) between ptpp.start_date
1242 and ptpp.end_date
1243 and NVL(c_date_earned,ptpp.end_date) between ptpt.start_date
1244 and ptpt.end_date
1245 and ptpt.time_definition_id = c_time_def_id
1246 and ptpp.end_date between ptpt.start_date
1247 and ptpt.end_date
1248 and ptpp.end_date <> ptpt.end_date
1249 and ptpt.time_definition_id is not null
1250 and ptpt.payroll_id is null
1251 and ptpp.time_definition_id is null
1252 and ptpp.payroll_id is not null;
1253
1254 cursor c_get_rec_mop_up_dates(c_assignment_id NUMBER
1255 ,c_element_entry_id NUMBER
1256 ,c_time_def_id NUMBER) is
1257 select /*+ use_nl(paf ptpp)*/
1258 ptpp.end_date + 1,
1259 ptpt.end_date
1260 from per_assignments_f paf
1261 ,per_time_periods ptpp
1262 ,per_time_periods ptpt
1263 where paf.assignment_id = c_assignment_id
1264 and ptpp.payroll_id = paf.payroll_id
1265 and ptpp.start_date <= (select max(peef.effective_end_date)
1266 from pay_element_entries_f peef
1267 where peef.element_entry_id = c_element_entry_id)
1268 and ptpp.end_date >= (select max(peef.effective_end_date)
1269 from pay_element_entries_f peef
1270 where peef.element_entry_id = c_element_entry_id)
1271 and ptpt.time_definition_id = c_time_def_id
1272 and ptpp.end_date between ptpt.start_date
1273 and ptpt.end_date
1274 and ptpp.end_date <> ptpt.end_date
1275 and NOT(ptpt.start_date between ptpp.start_date
1276 and ptpp.end_date
1277 AND
1278 ptpt.end_date between ptpp.start_date
1279 and ptpp.end_date)
1280 and ptpt.time_definition_id is not null
1281 and ptpt.payroll_id is null
1282 and ptpp.time_definition_id is null
1283 and ptpp.payroll_id is not null;
1284
1285 cursor c_get_rate_entry_count(c_element_entry_id number
1286 ,c_effective_date date) is
1287 select count(peev.screen_entry_value)
1288 from pay_element_entries_f peef,
1289 pay_input_values_f pivf,
1290 pay_element_entry_values_f peev
1291 where peef.element_entry_id = c_element_entry_id
1292 and pivf.element_type_id = peef.element_type_id
1293 and pivf.name in ('Rate', 'Rate Code')
1294 and peev.element_entry_id = peef.element_entry_id
1295 and peev.input_value_id = pivf.input_value_id
1296 and c_effective_date between peef.effective_start_date
1297 and peef.effective_end_date
1298 and c_effective_date between pivf.effective_start_date
1299 and pivf.effective_end_date
1300 and c_effective_date between pivf.effective_start_date
1301 and pivf.effective_end_date
1302 and peev.screen_entry_value is not null;
1303
1304 cursor c_get_elem_type_id(c_element_name varchar2
1305 ,c_business_group_id number) is
1306 select petf.element_type_id
1307 from pay_element_types_f petf
1308 where petf.element_name = c_element_name
1309 and petf.business_group_id = c_business_group_id;
1310
1311 -- Cursor to get the Element link details for the assignment
1312 cursor c_get_link_details(c_element_type_id number
1313 ,c_assignment_id number
1314 ,c_effective_start_date date
1315 ,c_effective_end_date date) is
1316 select paf.effective_start_date Asgt_Start_Date
1317 ,paf.effective_end_date Asgt_End_Date
1318 ,pelf.effective_start_date Link_Start_Date
1319 ,pelf.effective_end_date Link_End_Date
1320 ,pelf.element_link_id Element_Link_Id
1321 from pay_element_types_f petf,
1322 pay_element_links_f pelf,
1323 per_assignments_f paf
1324 where petf.element_type_id = c_element_type_id
1325 and petf.element_type_id = pelf.element_type_id
1326 and paf.assignment_id = c_assignment_id
1327 and c_effective_start_date <= paf.effective_end_date
1328 and c_effective_end_date >= paf.effective_start_date
1329 and c_effective_start_date <= pelf.effective_end_date
1330 and c_effective_end_date >= pelf.effective_start_date
1331 and (
1332 (pelf.effective_start_date between paf.effective_start_date
1333 and paf.effective_end_date)
1334 or
1335 (pelf.effective_end_date between paf.effective_start_date
1336 and paf.effective_end_date)
1337 or
1338 (
1339 pelf.effective_start_date < paf.effective_start_date
1340 and
1341 pelf.effective_end_date > paf.effective_end_date
1342 )
1343 )
1344 and (
1345 (pelf.payroll_id is not null and pelf.payroll_id = paf.payroll_id)
1346 or
1347 (pelf.link_to_all_payrolls_flag = 'Y' and paf.payroll_id is not null)
1348 or
1349 (pelf.payroll_id is null and pelf.link_to_all_payrolls_flag = 'N')
1350 )
1351 and (
1352 pelf.organization_id = paf.organization_id
1353 or
1354 pelf.organization_id is null
1355 )
1356 and (
1357 pelf.position_id = paf.position_id
1358 or
1359 pelf.position_id is null
1360 )
1361 and (
1362 pelf.job_id = paf.job_id
1363 or
1364 pelf.job_id is null
1365 )
1366 and (
1367 pelf.grade_id = paf.grade_id
1368 or
1369 pelf.grade_id is null
1370 )
1371 and (
1372 pelf.location_id = paf.location_id
1373 or
1374 pelf.location_id is null
1375 )
1376 and (
1377 pelf.pay_basis_id = paf.pay_basis_id
1378 or
1379 pelf.pay_basis_id is null
1380 )
1381 and (
1382 pelf.employment_category = paf.employment_category
1383 or
1384 pelf.employment_category is null
1385 )
1386 and (
1387 pelf.people_group_id is null
1388 or
1389 exists (
1390 select 1
1391 from pay_assignment_link_usages_f usage
1392 where usage.assignment_id = paf.assignment_id
1393 and usage.element_link_id = pelf.element_link_id
1394 )
1395 )
1396 order by Asgt_Start_Date, Link_Start_Date;
1397
1398 -- Get date_earned of element_entry
1399 CURSOR c_get_date_earned(c_element_entry_id number
1400 ) IS
1401 SELECT date_earned
1402 FROM pay_element_entries_f
1403 WHERE element_entry_id = c_element_entry_id;
1404
1405 -- Get the screen entry value
1406 cursor c_get_scr_entry_value(c_element_entry_id number,
1407 c_inp_value_name varchar2,
1408 c_effective_date date) is
1409 select peev.screen_entry_value
1410 from pay_element_entry_values_f peev
1411 ,pay_element_entries_f peef
1412 ,pay_input_values_f pivf
1413 where peef.element_entry_id = c_element_entry_id
1414 and peev.element_entry_id = peef.element_entry_id
1415 and pivf.element_type_id = peef.element_type_id
1416 and upper(pivf.name) = upper(c_inp_value_name)
1417 and peev.input_value_id = pivf.input_value_id
1418 and c_effective_date between peef.effective_start_date
1419 and peef.effective_end_date
1420 and c_effective_date between peev.effective_start_date
1421 and peev.effective_end_date
1422 and c_effective_date between pivf.effective_start_date
1423 and pivf.effective_end_date;
1424
1425 cursor c_get_inp_val_id(c_element_name varchar2
1426 ,c_inp_val_name varchar2
1427 ,c_business_group_id number) is
1428 select distinct
1429 pivf.input_value_id
1430 from pay_element_types_f petf
1431 ,pay_input_values_f pivf
1432 where petf.element_name = c_element_name
1433 and petf.business_group_id = c_business_group_id
1434 and pivf.element_type_id = petf.element_type_id
1435 and pivf.name = c_inp_val_name;
1436
1437 ln_business_group_id number;
1438 ln_time_definition_id number;
1439 ln_def_time_definition_id number;
1440 ln_mop_up_ele_type_id number;
1441 ln_ele_link_id number;
1442 ln_ele_ent_num number;
1443 ln_element_entry_id number;
1444 ln_original_entry_id number;
1445 ln_count number;
1446 ln_inp_value_id number;
1447 lvr number;
1448 lv_processing_type varchar2(10);
1449 lv_element_name varchar2(200);
1450 ln_screen_entry_value varchar2(1000);
1451 ld_mop_up_start_date date;
1452 ld_mop_up_end_date date;
1453 ld_asgt_eff_start_date date;
1454 ld_asgt_eff_end_date date;
1455 ld_link_eff_start_date date;
1456 ld_link_eff_end_date date;
1457 ld_dummy_start_date date;
1458 ld_dummy_end_date date;
1459 ld_date_earned date;
1460 lb_prem_flag boolean;
1461 lb_mop_up_flag boolean;
1462 ld_start_date_tbl date_table;
1463 ld_end_date_tbl date_table;
1464 ln_link_id_tbl number_table;
1465 ln_input_value_id_tbl hr_entry.number_table;
1466 lv_entry_value_tbl hr_entry.varchar2_table;
1467
1468 BEGIN
1469
1470 lb_mop_up_flag := FALSE;
1471 lb_prem_flag := FALSE;
1472 ln_ele_ent_num := 0;
1473 ln_link_id_tbl(0) := 0;
1474 ln_count := 0;
1475
1476 -- Check For Premium Element
1477 hr_utility.trace('Entering CREATE_PREMIUM_MOP_UP_ELEMENT');
1478 lb_prem_flag := CHECK_PREMIUM_ELEM(p_element_entry_id
1479 ,p_effective_start_date
1480 ,lv_element_name
1481 ,lv_processing_type
1482 ,ln_business_group_id);
1483
1484 if lb_prem_flag then
1485 hr_utility.trace('Getting Time Definition ID');
1486 -- Get the Default Time Definition ID
1487 open c_get_def_time_def_id('Non Allocated Time Definition'
1488 ,'US');
1489 fetch c_get_def_time_def_id into ln_def_time_definition_id;
1490 close c_get_def_time_def_id;
1491
1492 hr_utility.trace('Element Entry ID ' || p_element_entry_id);
1493 hr_utility.trace('Assignment ID ' || p_assignment_id);
1494 hr_utility.trace('Business Group ID ' || ln_business_group_id);
1495 hr_utility.trace('Eff Start Date ' || p_effective_start_date);
1496
1497 -- Get the Time Definition associated with the Assignment as of
1498 -- Premium Element Entry Start Date
1499 ln_time_definition_id :=
1500 pay_us_rules.get_time_def_for_entry_func(
1501 p_element_entry_id => p_element_entry_id
1502 ,p_assignment_id => p_assignment_id
1503 ,p_assignment_action_id => NULL
1504 ,p_business_group_id => ln_business_group_id
1505 ,p_time_def_date => p_effective_start_date);
1506
1507 if ln_time_definition_id = ln_def_time_definition_id then
1508 hr_utility.trace('Default Time Definition ID');
1509 lb_prem_flag := FALSE;
1510 end if;
1511 end if;
1512
1513
1514 -- For Premium Element based on the Time Definition
1515 -- we have to create a MOP UP element.
1516 if lb_prem_flag then
1517 -- Check whether it is a Non Recurring Premium or Recurring
1518 if upper(lv_processing_type) = 'N' then
1519 hr_utility.trace('Non Recurring Premium Element');
1520
1521 open c_get_rate_entry_count(p_element_entry_id
1522 ,p_effective_start_date);
1523 fetch c_get_rate_entry_count into ln_count;
1524 close c_get_rate_entry_count;
1525
1526 -- Get the Re Calc Element Entry dates only if
1527 -- Rate/Rate Code is not specified
1528 if ln_count = 0 then
1529
1530 open c_get_date_earned (p_element_entry_id);
1531 fetch c_get_date_earned into ld_date_earned;
1532 IF c_get_date_earned%NOTFOUND THEN
1533 ld_date_earned := NULL;
1534 END IF;
1535 close c_get_date_earned;
1536
1537 open c_get_nrec_mop_up_dates(p_assignment_id
1538 ,p_effective_start_date
1539 ,ld_date_earned
1540 ,ln_time_definition_id);
1541 fetch c_get_nrec_mop_up_dates into ld_mop_up_start_date
1542 ,ld_mop_up_end_date;
1543 if c_get_nrec_mop_up_dates%FOUND then
1544 lb_mop_up_flag := TRUE;
1545 end if;
1546 close c_get_nrec_mop_up_dates;
1547 end if; -- if ln_count > 0
1548 else
1549 hr_utility.trace('Recurring Premium Element');
1550 open c_get_rec_mop_up_dates(p_assignment_id
1551 ,p_element_entry_id
1552 ,ln_time_definition_id);
1553 fetch c_get_rec_mop_up_dates into ld_mop_up_start_date
1554 ,ld_mop_up_end_date;
1555 if c_get_rec_mop_up_dates%FOUND then
1556 lb_mop_up_flag := TRUE;
1557 end if;
1558 close c_get_rec_mop_up_dates;
1559 end if; -- if upper(lv_processing_type)
1560
1561 if lb_mop_up_flag then
1562 -- Get the input value id 'Multiple' for the Adjustment element
1563 ln_inp_value_id := NULL;
1564 open c_get_inp_val_id(lv_element_name ||
1565 ' for FLSA Period Adjustment'
1566 ,'Multiple'
1567 ,ln_business_group_id);
1568 fetch c_get_inp_val_id into ln_inp_value_id;
1569 if c_get_inp_val_id%FOUND then
1570 ln_input_value_id_tbl(1) := ln_inp_value_id;
1571 end if;
1572 close c_get_inp_val_id;
1573
1574 if ln_inp_value_id is NOT NULL then
1575 -- Get the value for the input value MULTIPLE specified
1576 -- for the Premium element. This value needs to be paased
1577 -- to the Adjustment element
1578 open c_get_scr_entry_value(p_element_entry_id
1579 ,'Multiple'
1580 ,p_effective_start_date);
1581 fetch c_get_scr_entry_value into ln_screen_entry_value;
1582 if c_get_scr_entry_value%FOUND then
1583 lv_entry_value_tbl(1) := ln_screen_entry_value;
1584 else
1585 lv_entry_value_tbl(1) := NULL;
1586 end if; -- if c_get_scr_entry_value%FOUND
1587 close c_get_scr_entry_value;
1588 end if; -- if ln_inp_value_id is NOT NULL
1589
1590
1591 hr_utility.trace('Getting Mop UP ID');
1592 open c_get_elem_type_id(lv_element_name || ' for FLSA Period Adjustment'
1593 ,ln_business_group_id);
1594 fetch c_get_elem_type_id into ln_mop_up_ele_type_id;
1595 close c_get_elem_type_id;
1596 hr_utility.trace('Mop Up ID : ' || ln_mop_up_ele_type_id);
1597
1598 ld_dummy_start_date := ld_mop_up_start_date;
1599
1600 open c_get_link_details(ln_mop_up_ele_type_id
1601 ,p_assignment_id
1602 ,ld_mop_up_start_date
1603 ,ld_mop_up_end_date);
1604 loop
1605 fetch c_get_link_details into ld_asgt_eff_start_date
1606 ,ld_asgt_eff_end_date
1607 ,ld_link_eff_start_date
1608 ,ld_link_eff_end_date
1609 ,ln_ele_link_id;
1610 exit when c_get_link_details%NOTFOUND;
1611
1612 if ln_ele_link_id <> ln_link_id_tbl(ln_ele_ent_num) then
1613 ln_ele_ent_num := ln_ele_ent_num + 1;
1614 ln_link_id_tbl(ln_ele_ent_num) := ln_ele_link_id;
1615 if ld_asgt_eff_end_date > ld_link_eff_end_date then
1616 ld_start_date_tbl(ln_ele_ent_num) := ld_dummy_start_date;
1617 ld_end_date_tbl(ln_ele_ent_num) := ld_link_eff_end_date;
1618 else
1619 ld_start_date_tbl(ln_ele_ent_num) := ld_dummy_start_date;
1620 ld_end_date_tbl(ln_ele_ent_num) := ld_asgt_eff_end_date;
1621 end if; -- if ld_asgt_eff_end_date > ....
1622
1623 if ld_end_date_tbl(ln_ele_ent_num) > ld_mop_up_end_date then
1624 ld_end_date_tbl(ln_ele_ent_num) := ld_mop_up_end_date;
1625 else
1626 ld_dummy_start_date := ld_end_date_tbl(ln_ele_ent_num) + 1;
1627 end if; -- if ld_end_date_tbl(ln_ele_ent_num) ....
1628
1629 hr_utility.trace('ln_ele_ent_num = ' || ln_ele_ent_num);
1630 hr_utility.trace('Asgt Eff End Date = ' ||
1631 ld_asgt_eff_end_date);
1632 hr_utility.trace('Link Eff End Date = ' ||
1633 ld_link_eff_end_date);
1634 hr_utility.trace('Global Start Date = ' ||
1635 ld_start_date_tbl(ln_ele_ent_num));
1636 hr_utility.trace('Global End Date = ' ||
1637 ld_end_date_tbl(ln_ele_ent_num));
1638 hr_utility.trace('Link ID = ' ||
1639 ln_link_id_tbl(ln_ele_ent_num));
1640 else
1641 if ld_asgt_eff_end_date > ld_link_eff_end_date then
1642 ld_end_date_tbl(ln_ele_ent_num) := ld_link_eff_end_date;
1643 else
1644 ld_end_date_tbl(ln_ele_ent_num) := ld_asgt_eff_end_date;
1645 end if; -- if ld_asgt_eff_end_date ....
1646
1647 if ld_end_date_tbl(ln_ele_ent_num) > ld_mop_up_end_date then
1648 ld_end_date_tbl(ln_ele_ent_num) := ld_mop_up_end_date;
1649 else
1650 ld_dummy_start_date := ld_end_date_tbl(ln_ele_ent_num) + 1;
1651 end if; -- if ld_end_date_tbl(ln_ele_ent_num) ....
1652
1653 hr_utility.trace('ln_ele_ent_num = ' || ln_ele_ent_num);
1654 hr_utility.trace('Global Start Date = ' ||
1655 ld_start_date_tbl(ln_ele_ent_num));
1656 hr_utility.trace('Global End Date = ' ||
1657 ld_end_date_tbl(ln_ele_ent_num));
1658 hr_utility.trace('Link ID = ' ||
1659 ln_link_id_tbl(ln_ele_ent_num));
1660 end if; -- if ln_ele_link_id <> ....
1661 end loop;
1662 close c_get_link_details;
1663
1664 if ln_ele_ent_num = 0 then
1665 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1666 hr_utility.set_message_token('FORMULA_TEXT',
1667 'The assignment is not eligible for ' ||
1668 lv_element_name || ' for FLSA Period Adjustment. ' ||
1669 'Please link the element to make it eligible ' ||
1670 'for the assignment.');
1671 hr_utility.raise_error;
1672 elsif (ld_end_date_tbl(ln_ele_ent_num) < ld_mop_up_end_date) then
1673 hr_utility.set_message(801,'HR_ELE_ENTRY_FORMULA_HINT');
1674 hr_utility.set_message_token('FORMULA_TEXT',
1675 'The assignment is not eligible for ' ||
1676 lv_element_name || ' for FLSA Period Adjustment. ' ||
1677 'Please link the element to make it eligible ' ||
1678 'for the assignment.');
1679 hr_utility.raise_error;
1680 end if; -- if gn_ele_ent_num = 0
1681
1682 for lvr in 1..ln_ele_ent_num loop
1683 hr_utility.trace('Creating Recurring Element Entries');
1684 hr_utility.trace('Element Start Date = ' || ld_start_date_tbl(lvr));
1685 hr_utility.trace('Element End Date = ' || ld_end_date_tbl(lvr));
1686
1687 ld_dummy_end_date := ld_end_date_tbl(lvr);
1688
1689 hr_entry_api.insert_element_entry (
1690 p_effective_start_date => ld_start_date_tbl(lvr)
1691 ,p_effective_end_date => ld_dummy_end_date
1692 ,p_element_entry_id => ln_element_entry_id
1693 ,p_original_entry_id => ln_original_entry_id
1694 ,p_assignment_id => p_assignment_id
1695 ,p_element_link_id => ln_link_id_tbl(lvr)
1696 ,p_creator_type => 'FL'
1697 ,p_creator_id => p_element_entry_id
1698 ,p_entry_type => 'E' -- Normal Entry
1699 ,p_entry_information_category => null
1700 --
1701 -- Element Entry Values Table
1702 --
1703 ,p_num_entry_values => lv_entry_value_tbl.count()
1704 ,p_input_value_id_tbl => ln_input_value_id_tbl
1705 ,p_entry_value_tbl => lv_entry_value_tbl
1706 );
1707
1708 -- End dating the element using ld_end_date_tbl(lvr)
1709 -- as ld_dummy_end_date gets overwritten by the
1710 -- previous call
1711 if ld_dummy_end_date <> ld_end_date_tbl(lvr) then
1712 hr_utility.trace('End dating the Element Entry Created');
1713 hr_entry_api.delete_element_entry (
1714 p_dt_delete_mode => 'DELETE',
1715 p_session_date => ld_end_date_tbl(lvr),
1716 p_element_entry_id => ln_element_entry_id);
1717 end if; -- if ld_dummy_end_date
1718
1719 /*
1720 * Through the API call above, the Original Entry ID could not be
1721 * set as it requires that the Orignal Entry ID be a recurring
1722 * Element. In our case we can have the Original Entry ID to
1723 * be Non-Recurring.
1724 */
1725 update pay_element_entries_f
1726 set original_entry_id = p_element_entry_id
1727 where element_entry_id = ln_element_entry_id;
1728 end loop; -- for lvr in 1..
1729 end if; -- if lb_mop_up_flag
1730 end if; -- if lb_prem_flag
1731
1732 return;
1733
1734 END CREATE_PREMIUM_MOP_UP_ELEMENT;
1735
1736 /******************************************************************************
1737 Name : INSERT_USER_HOOK
1738 Scope : GLOBAL
1739 Description : This procedure is called by AFTER INSERT Row Level handler
1740 User Hook.
1741 ******************************************************************************/
1742 PROCEDURE INSERT_USER_HOOK(
1743 p_element_entry_id in number
1744 ,p_effective_start_date in date
1745 ,p_effective_end_date in date
1746 ,p_assignment_id in number
1747 ,p_element_link_id in number
1748 ,p_original_entry_id in number
1749 ,p_creator_type in varchar2
1750 ,p_entry_type in varchar2
1751 ,p_entry_information_category in varchar2) IS
1752 BEGIN
1753
1754 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.INSERT_USER_HOOK');
1755 -- Call CREATE_RECUR_ELEM_ENTRY
1756 -- The package has the check built in for identifying Augment Elements
1757 CREATE_RECUR_ELEM_ENTRY(p_element_entry_id
1758 ,p_effective_start_date
1759 ,p_effective_end_date
1760 ,p_assignment_id
1761 ,p_element_link_id
1762 ,p_original_entry_id
1763 ,p_creator_type
1764 ,p_entry_type
1765 ,p_entry_information_category);
1766 -- Call CREATE_TAX_REACORDS
1767 -- We need to create TAX RECORDS if JURSIDICTION input value is specified
1768 -- for the employee and the employee does not have any tax records for
1769 -- that state.
1770 CREATE_TAX_RECORDS(p_element_entry_id
1771 ,p_effective_start_date
1772 ,p_effective_end_date
1773 ,p_assignment_id
1774 ,p_element_link_id
1775 ,p_original_entry_id
1776 ,p_creator_type
1777 ,p_entry_type
1778 ,p_entry_information_category);
1779
1780 CREATE_PREMIUM_MOP_UP_ELEMENT(p_element_entry_id
1781 ,p_effective_start_date
1782 ,p_effective_end_date
1783 ,p_assignment_id
1784 ,p_element_link_id
1785 ,p_original_entry_id
1786 ,p_creator_type
1787 ,p_entry_type
1788 ,p_entry_information_category);
1789 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.INSERT_USER_HOOK');
1790 return;
1791 END INSERT_USER_HOOK;
1792
1793 -----------------------------INSERT SECTION ENDS HERE--------------------------
1794
1795 /******************************************************************************
1796 Name : UPDATE_RECUR_ELEM_ENTRY
1797 Scope : LOCAL
1798 Description : This procedure is used to Update the Recurring Element Entry
1799 associated with Non recurring Augment element.
1800 ******************************************************************************/
1801 PROCEDURE UPDATE_RECUR_ELEM_ENTRY(
1802 p_element_entry_id in number
1803 ,p_effective_start_date in date
1804 ,p_effective_end_date in date
1805 ,p_assignment_id_o in number
1806 ,p_element_link_id_o in number
1807 ,p_original_entry_id_o in number
1808 ,p_creator_type_o in varchar2
1809 ,p_entry_type_o in varchar2
1810 ,p_entry_information_category_o in varchar2) IS
1811
1812 -- Cursor to get the Recurring Element entries
1813 -- using the Creator ID
1814 cursor c_get_rec_elem_details(c_element_entry_id number
1815 ,c_assignment_id number) is
1816 select distinct
1817 peef.element_entry_id
1818 ,peef.effective_start_date
1819 ,peef.effective_end_date
1820 ,peev.screen_entry_value
1821 from pay_element_entries_f peef,
1822 pay_element_entry_values_f peev,
1823 pay_input_values_f pivf
1824 where peef.creator_id = c_element_entry_id
1825 and peef.assignment_id = c_assignment_id
1826 and peef.creator_type = 'FL'
1827 and peev.element_entry_id = peef.element_entry_id
1828 and pivf.element_type_id = peef.element_type_id
1829 and upper(pivf.name) = 'DAILY AMOUNT'
1830 and peev.input_value_id = pivf.input_value_id
1831 and peev.effective_start_date between peef.effective_start_date
1832 and peef.effective_end_date
1833 order by peef.effective_start_date;
1834
1835 -- Cursor to get the Non-recurring element details for updating the
1836 -- corresponding recurring element entry
1837 cursor c_get_aug_entry_details(c_element_entry_id number
1838 ,c_effective_start_date date) is
1839 select pivf.name
1840 ,peevf.screen_entry_value
1841 from pay_element_entries_f peef
1842 ,pay_element_entry_values_f peevf
1843 ,pay_input_values_f pivf
1844 where peef.element_entry_id = c_element_entry_id
1845 and peevf.element_entry_id = peef.element_entry_id
1846 and pivf.element_type_id = peef.element_type_id
1847 and peevf.input_value_id = pivf.input_value_id
1848 and upper(pivf.name) in ('EARNED START DATE',
1849 'EARNED END DATE',
1850 'AMOUNT')
1851 and c_effective_start_date between peef.effective_start_date
1852 and peef.effective_end_date
1853 and c_effective_start_date between peevf.effective_start_date
1854 and peevf.effective_end_date
1855 and c_effective_start_date between pivf.effective_start_date
1856 and pivf.effective_end_date;
1857
1858 -- Cursor to get Input value id for the Input value 'Daily Amount'
1859 cursor c_get_inp_value_id (c_element_name varchar2
1860 ,c_business_group_id number) is
1861 select pivf.input_value_id
1862 from pay_element_types_f petf,
1863 pay_input_values_f pivf
1864 where petf.element_name = c_element_name || ' for FLSA Calc'
1865 and petf.business_group_id = c_business_group_id
1866 and pivf.element_type_id = petf.element_type_id
1867 and upper(pivf.name) = 'DAILY AMOUNT';
1868
1869 ln_ele_entry_id number;
1870 ln_no_of_days number;
1871 lvar number;
1872 ln_daily_amount number;
1873 ln_total_amount number;
1874 ln_business_grp_id number;
1875 lv_inp_val_name varchar2(100);
1876 lv_screen_entry_value varchar2(100);
1877 lv_old_screen_entry_value varchar2(100);
1878 lv_element_name varchar2(100);
1879 lv_penny_issue_bu varchar2(100);
1880 lv_penny_issue_au varchar2(100);
1881 ld_eff_start_date date;
1882 ld_eff_end_date date;
1883 ld_rec_ele_start_date date;
1884 ld_rec_ele_end_date date;
1885 ld_new_rec_ele_start_date date;
1886 ld_new_rec_ele_end_date date;
1887 lb_aug_flag boolean;
1888 l_elem_entry_id_tbl number_table;
1889 l_del_start_date_tbl date_table;
1890 l_input_value_id_tbl hr_entry.number_table;
1891 l_entry_value_tbl hr_entry.varchar2_table;
1892
1893 BEGIN
1894
1895 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_RECUR_ELEM_ENTRY');
1896
1897 -- Initialization
1898 ld_rec_ele_start_date := hr_api.g_date;
1899 ld_rec_ele_end_date := hr_api.g_date;
1900 lvar := 0;
1901 ln_total_amount := 0;
1902 lv_penny_issue_bu := 'N';
1903 lv_penny_issue_au := 'N';
1904
1905 -- Check whether this element entry is an augment element.
1906 -- If not then we need not do anyhting additional
1907 lb_aug_flag := CHECK_AUGMENT_ELEM(p_assignment_id_o
1908 ,p_element_entry_id
1909 ,p_effective_start_date
1910 ,lv_element_name
1911 ,ln_business_grp_id);
1912 if NOT(lb_aug_flag) then
1913 return;
1914 end if;
1915
1916 -- Get the Start Date and End Date of the Recurring Element entry
1917 -- before the update was made.
1918 open c_get_rec_elem_details(p_element_entry_id
1919 ,p_assignment_id_o);
1920 loop
1921 fetch c_get_rec_elem_details into ln_ele_entry_id
1922 ,ld_eff_start_date
1923 ,ld_eff_end_date
1924 ,lv_screen_entry_value;
1925 exit when c_get_rec_elem_details%NOTFOUND;
1926
1927 if lvar = 0 then
1928 ld_rec_ele_start_date := ld_eff_start_date;
1929 lv_old_screen_entry_value := lv_screen_entry_value;
1930 l_elem_entry_id_tbl(lvar) := ln_ele_entry_id;
1931 l_del_start_date_tbl(lvar) := ld_eff_start_date;
1932 lvar := lvar + 1;
1933 else
1934 if l_elem_entry_id_tbl(lvar-1) <> ln_ele_entry_id then
1935 l_elem_entry_id_tbl(lvar) := ln_ele_entry_id;
1936 l_del_start_date_tbl(lvar) := ld_eff_start_date;
1937 lvar := lvar + 1;
1938 end if;
1939 end if; -- if lvar = 0
1940
1941 -- Checking if Penny Issue existed before Update
1942 if lv_old_screen_entry_value <> lv_screen_entry_value then
1943 lv_penny_issue_bu := 'Y';
1944 end if;
1945
1946 ld_rec_ele_end_date := ld_eff_end_date;
1947 end loop;
1948 close c_get_rec_elem_details;
1949
1950 hr_utility.trace('Previous Start Date = ' || ld_rec_ele_start_date);
1951 hr_utility.trace('Previous End Date = ' || ld_rec_ele_end_date);
1952
1953 -- Get the Start Date, End Date and Amount specified in the
1954 -- Non-recurring augment element
1955 open c_get_aug_entry_details(p_element_entry_id
1956 ,p_effective_start_date);
1957 loop
1958 fetch c_get_aug_entry_details into lv_inp_val_name
1959 ,lv_screen_entry_value;
1960 exit when c_get_aug_entry_details%NOTFOUND;
1961
1962 if upper(lv_inp_val_name) = 'EARNED START DATE' then
1963 ld_new_rec_ele_start_date :=
1964 fnd_date.canonical_to_date(lv_screen_entry_value);
1965 hr_utility.trace('New Start Date = ' || ld_new_rec_ele_start_date);
1966 elsif upper(lv_inp_val_name) = 'EARNED END DATE' then
1967 ld_new_rec_ele_end_date :=
1968 fnd_date.canonical_to_date(lv_screen_entry_value);
1969 hr_utility.trace('New End Date = ' || ld_new_rec_ele_end_date);
1970 elsif upper(lv_inp_val_name) = 'AMOUNT' then
1971 ln_total_amount := to_number(nvl(lv_screen_entry_value,0));
1972 hr_utility.trace('New Amount = ' || ln_total_amount);
1973 end if; -- if upper(lv_inp_val_name) = 'START DATE'
1974 end loop;
1975 close c_get_aug_entry_details;
1976
1977 ln_no_of_days := ld_new_rec_ele_end_date - ld_new_rec_ele_start_date + 1;
1978
1979 -- Getting daily Amount in the New scenario.
1980 -- This value is later used to fine id we need to just update the
1981 -- Element entries or delete and re-create them.
1982 ln_daily_amount := GET_DAILY_AMOUNT(p_assignment_id_o
1983 ,ld_new_rec_ele_start_date
1984 ,ld_new_rec_ele_end_date
1985 ,'Amount'
1986 ,ln_total_amount);
1987
1988 -- Check if Penny issue will exist After Update
1989 if ln_total_amount <> ln_daily_amount * ln_no_of_days then
1990 lv_penny_issue_au := 'Y';
1991 end if;
1992
1993 -- If the dates have been modified then we have to delete the Old
1994 -- element entries created for the Recurring element and create
1995 -- a New recurring element entry.
1996 -- We also need to recreate the Recurring Element entries in cases
1997 -- where Penny isssue exists in only one of the cases
1998 -- i.e either Before the Update or After the Update
1999 if (ld_rec_ele_start_date <> ld_new_rec_ele_start_date OR
2000 ld_rec_ele_end_date <> ld_new_rec_ele_end_date OR
2001 lv_penny_issue_au <> lv_penny_issue_bu) then
2002 -- Deleting the Recurring element entries previously created
2003 hr_utility.trace('Deleting Old Recurring Element Entries');
2004 if l_elem_entry_id_tbl.count() > 0 then
2005 for lvar in l_elem_entry_id_tbl.first..l_elem_entry_id_tbl.last loop
2006 hr_entry_api.delete_element_entry (
2007 p_dt_delete_mode => 'ZAP',
2008 p_session_date => l_del_start_date_tbl(lvar),
2009 p_element_entry_id => l_elem_entry_id_tbl(lvar));
2010 end loop; -- for lvar in l_elem_entry_id_tbl
2011 end if; -- if l_elem_entry_id_tbl.count()
2012
2013 -- Recreating New recurring element entries
2014 hr_utility.trace('Creating New Recurring Element Entries');
2015 PAY_US_ELEMENT_ENTRY_HOOK.CREATE_RECUR_ELEM_ENTRY(
2016 p_element_entry_id
2017 ,p_effective_start_date
2018 ,p_effective_end_date
2019 ,p_assignment_id_o
2020 ,p_element_link_id_o
2021 ,p_original_entry_id_o
2022 ,p_creator_type_o
2023 ,p_entry_type_o
2024 ,p_entry_information_category_o);
2025 else
2026 hr_utility.trace('Updating Recurring Amount Values');
2027 -- No changes have been made to Start Date and End Date
2028 hr_utility.trace('New Daily Amount = ' || ln_daily_amount);
2029
2030 -- Get the Input Value ID to be changed
2031 open c_get_inp_value_id(lv_element_name
2032 ,ln_business_grp_id);
2033 fetch c_get_inp_value_id into l_input_value_id_tbl(1);
2034 if c_get_inp_value_id%NOTFOUND then
2035 hr_utility.trace('No Input Value to be modified');
2036 return;
2037 end if;
2038 close c_get_inp_value_id;
2039
2040 -- Updating Recurring Element Entry values
2041 if l_elem_entry_id_tbl.count() > 0 then
2042 for lvar in l_elem_entry_id_tbl.first..l_elem_entry_id_tbl.last loop
2043 -- Set the Daily Amount for the last Element Entry to solve the
2044 -- Penny issue
2045 if lvar = l_elem_entry_id_tbl.last then
2046 l_entry_value_tbl(1) := ln_total_amount -
2047 ((ln_no_of_days-1) * ln_daily_amount);
2048 else
2049 l_entry_value_tbl(1) := to_char(ln_daily_amount);
2050 end if;
2051 hr_entry_api.update_element_entry
2052 (p_dt_update_mode => 'CORRECTION'
2053 ,p_session_date => l_del_start_date_tbl(lvar)
2054 ,p_element_entry_id => l_elem_entry_id_tbl(lvar)
2055 ,p_num_entry_values => 1
2056 ,p_input_value_id_tbl => l_input_value_id_tbl
2057 ,p_entry_value_tbl => l_entry_value_tbl);
2058 end loop; -- for lvar in l_elem_entry_id_tb
2059 end if; -- if l_elem_entry_id_tbl.count()
2060 end if; -- if (ld_rec_ele_start_date <>
2061
2062 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_RECUR_ELEM_ENTRY');
2063 return;
2064 EXCEPTION
2065 --
2066 WHEN others THEN
2067 raise;
2068 END UPDATE_RECUR_ELEM_ENTRY;
2069
2070 /******************************************************************************
2071 Name : UPDATE_PREMIUM_MOP_UP_ELEMENT
2072 Scope : LOCAL
2073 Description : This function is used for updating the MOP UP element
2074 for Premium. This procedure creates a MOP UP element
2075 only if the FLSA Period crossed over Payroll Period in
2076 question and the input values Rate/Rate Code both are not
2077 specified.
2078 ******************************************************************************/
2079 PROCEDURE UPDATE_PREMIUM_MOP_UP_ELEMENT(
2080 p_element_entry_id in number
2081 ,p_effective_start_date in date
2082 ,p_effective_end_date in date
2083 ,p_assignment_id in number
2084 ,p_element_link_id in number
2085 ,p_original_entry_id in number
2086 ,p_creator_type in varchar2
2087 ,p_entry_type in varchar2
2088 ,p_entry_information_category in varchar2) IS
2089
2090
2091 cursor c_get_rate_entry_count(c_element_entry_id number
2092 ,c_effective_date date) is
2093 select count(peev.screen_entry_value)
2094 from pay_element_entries_f peef,
2095 pay_input_values_f pivf,
2096 pay_element_entry_values_f peev
2097 where peef.element_entry_id = c_element_entry_id
2098 and pivf.element_type_id = peef.element_type_id
2099 and pivf.name in ('Rate', 'Rate Code')
2100 and peev.element_entry_id = peef.element_entry_id
2101 and peev.input_value_id = pivf.input_value_id
2102 and c_effective_date between peef.effective_start_date
2103 and peef.effective_end_date
2104 and c_effective_date between pivf.effective_start_date
2105 and pivf.effective_end_date
2106 and c_effective_date between peev.effective_start_date
2107 and peev.effective_end_date
2108 and peev.screen_entry_value is not null;
2109
2110 cursor c_check_mop_up_exists(c_element_entry_id number
2111 ,c_assignment_id number) is
2112 select 'Exist'
2113 from pay_element_entries_f peef
2114 where peef.creator_id = c_element_entry_id
2115 and peef.assignment_id = c_assignment_id
2116 and peef.creator_type = 'FL';
2117
2118 -- Get the End date of the recurring element.
2119 cursor c_get_entry_end_date(c_element_entry_id number
2120 ,c_assignment_id number) is
2121 select nvl(max(peef.effective_end_date),
2122 fnd_date.canonical_to_date('4712/12/31'))
2123 from pay_element_entries_f peef
2124 where peef.element_entry_id = c_element_entry_id
2125 and peef.assignment_id = c_assignment_id;
2126
2127 -- Get date_earned of element_entry
2128 CURSOR c_get_date_earned(c_element_entry_id number
2129 ) IS
2130 SELECT date_earned
2131 FROM pay_element_entries_f
2132 WHERE element_entry_id = c_element_entry_id;
2133
2134 -- Get dates in which mop-up should exist of element_entry
2135 cursor c_get_nrec_mop_up_dates(c_assignment_id NUMBER
2136 ,c_effective_start_date DATE
2137 ,c_date_earned DATE
2138 ,c_time_def_id NUMBER) is
2139 select /*+ use_nl(paf ptpp)*/
2140 ptpp.end_date + 1,
2141 ptpt.end_date
2142 from per_assignments_f paf
2143 ,per_time_periods ptpp
2144 ,per_time_periods ptpt
2145 where paf.assignment_id = c_assignment_id
2146 and NVL(c_date_earned,c_effective_start_date) between paf.effective_start_date
2147 and paf.effective_end_date
2148 and ptpp.payroll_id = paf.payroll_id
2149 and NVL(c_date_earned,c_effective_start_date) between ptpp.start_date
2150 and ptpp.end_date
2151 and NVL(c_date_earned,ptpp.end_date) between ptpt.start_date
2152 and ptpt.end_date
2153 and ptpt.time_definition_id = c_time_def_id
2154 and ptpp.end_date between ptpt.start_date
2155 and ptpt.end_date
2156 and ptpp.end_date <> ptpt.end_date
2157 and ptpt.time_definition_id is not null
2158 and ptpt.payroll_id is null
2159 and ptpp.time_definition_id is null
2160 and ptpp.payroll_id is not null;
2161
2162 -- Get the screen entry value
2163 cursor c_get_scr_entry_value(c_element_entry_id number,
2164 c_inp_value_name varchar2,
2165 c_effective_date date) is
2166 select peev.screen_entry_value
2167 from pay_element_entry_values_f peev
2168 ,pay_element_entries_f peef
2169 ,pay_input_values_f pivf
2170 where peef.element_entry_id = c_element_entry_id
2171 and peev.element_entry_id = peef.element_entry_id
2172 and pivf.element_type_id = peef.element_type_id
2173 and upper(pivf.name) = upper(c_inp_value_name)
2174 and peev.input_value_id = pivf.input_value_id
2175 and c_effective_date between peef.effective_start_date
2176 and peef.effective_end_date
2177 and c_effective_date between peev.effective_start_date
2178 and peev.effective_end_date
2179 and c_effective_date between pivf.effective_start_date
2180 and pivf.effective_end_date;
2181
2182 -- Cursot to fetch the recurring element to be updates
2183 cursor c_get_rec_elem_details(c_element_entry_id number
2184 ,c_assignment_id number) is
2185 select distinct
2186 peef.element_entry_id
2187 ,peef.effective_start_date
2188 from pay_element_entries_f peef
2189 where peef.creator_id = c_element_entry_id
2190 and peef.assignment_id = c_assignment_id
2191 and peef.creator_type = 'FL'
2192 order by peef.effective_start_date;
2193
2194 cursor c_get_inp_val_id(c_element_name varchar2
2195 ,c_inp_val_name varchar2
2196 ,c_business_group_id number) is
2197 select distinct
2198 pivf.input_value_id
2199 from pay_element_types_f petf
2200 ,pay_input_values_f pivf
2201 where petf.element_name = c_element_name
2202 and petf.business_group_id = c_business_group_id
2203 and pivf.element_type_id = petf.element_type_id
2204 and pivf.name = c_inp_val_name;
2205
2206 ln_count number;
2207 ln_business_group_id number;
2208 ln_time_definition_id number;
2209 ln_inp_value_id number;
2210 ln_ele_entry_id number;
2211 lvar number;
2212 ln_screen_entry_value varchar2(1000);
2213 lv_element_name varchar2(200);
2214 lv_processing_type varchar2(10);
2215 lv_exist varchar2(20);
2216 ld_entry_end_date date;
2217 ld_date_earned date;
2218 ld_mop_up_start_date date;
2219 ld_mop_up_end_date date;
2220 ld_eff_start_date date;
2221 lb_prem_flag boolean;
2222 lb_mop_up_flag boolean;
2223 lb_delete_mopup boolean;
2224 l_elem_entry_id_tbl number_table;
2225 l_del_start_date_tbl date_table;
2226 l_input_value_id_tbl hr_entry.number_table;
2227 l_entry_value_tbl hr_entry.varchar2_table;
2228
2229 BEGIN
2230
2231 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_PREMIUM_MOP_UP_ELEMENT');
2232
2233 ln_count := 0;
2234 lvar := 0;
2235 lb_mop_up_flag := FALSE;
2236 lb_delete_mopup := FALSE;
2237
2238 -- Check for Premium Element
2239 lb_prem_flag := CHECK_PREMIUM_ELEM(p_element_entry_id
2240 ,p_effective_start_date
2241 ,lv_element_name
2242 ,lv_processing_type
2243 ,ln_business_group_id);
2244
2245 -- Get the Time Definition associated with the Assignment as of
2246 -- Premium Element Entry Start Date
2247 ln_time_definition_id :=
2248 pay_us_rules.get_time_def_for_entry_func(
2249 p_element_entry_id => p_element_entry_id
2250 ,p_assignment_id => p_assignment_id
2251 ,p_assignment_action_id => NULL
2252 ,p_business_group_id => ln_business_group_id
2253 ,p_time_def_date => p_effective_start_date);
2254
2255 if lb_prem_flag then
2256 open c_get_entry_end_date(p_element_entry_id
2257 ,p_assignment_id);
2258 fetch c_get_entry_end_date into ld_entry_end_date;
2259 close c_get_entry_end_date;
2260 hr_utility.trace('Element End Date = ' || ld_entry_end_date);
2261
2262 if lv_processing_type = 'N' then
2263 hr_utility.trace('Non Recurring Element');
2264 open c_get_rate_entry_count(p_element_entry_id
2265 ,p_effective_start_date);
2266 fetch c_get_rate_entry_count into ln_count;
2267 close c_get_rate_entry_count;
2268
2269 /*Code to see if date earned demands a mopup to exist or not*/
2270 open c_get_date_earned (p_element_entry_id);
2271 fetch c_get_date_earned into ld_date_earned;
2272 IF c_get_date_earned%NOTFOUND THEN
2273 ld_date_earned := NULL;
2274 END IF;
2275 close c_get_date_earned;
2276
2277 open c_get_nrec_mop_up_dates(p_assignment_id
2278 ,p_effective_start_date
2279 ,ld_date_earned
2280 ,ln_time_definition_id);
2281 fetch c_get_nrec_mop_up_dates into ld_mop_up_start_date
2282 ,ld_mop_up_end_date;
2283 if c_get_nrec_mop_up_dates%FOUND then
2284 lb_delete_mopup := FALSE;
2285 else
2286 lb_delete_mopup := TRUE;
2287 end if;
2288 close c_get_nrec_mop_up_dates;
2289 /*End of Code to see if date earned demands a mopup to exist or not*/
2290
2291 else
2292 hr_utility.trace('Recurring Element');
2293 open c_get_rate_entry_count(p_element_entry_id
2294 ,ld_entry_end_date);
2295 fetch c_get_rate_entry_count into ln_count;
2296 close c_get_rate_entry_count;
2297 end if; -- if lv_processing_type =
2298
2299 open c_check_mop_up_exists(p_element_entry_id
2300 ,p_assignment_id);
2301 fetch c_check_mop_up_exists into lv_exist;
2302 if c_check_mop_up_exists%FOUND then
2303 if ((ln_count > 0) OR (lb_delete_mopup)) then
2304 -- Delete the Mop up as we do not require it now
2305 hr_utility.trace('Deleting Mop Up Element');
2306 DELETE_DEPENDENT_ENTRIES(p_element_entry_id
2307 ,p_assignment_id);
2308 else
2309 -- Get the input value id 'Multiple' for the Adjustment element
2310 ln_inp_value_id := NULL;
2311 open c_get_inp_val_id(lv_element_name ||
2312 ' for FLSA Period Adjustment'
2313 ,'Multiple'
2314 ,ln_business_group_id);
2315 fetch c_get_inp_val_id into ln_inp_value_id;
2316 if c_get_inp_val_id%FOUND then
2317 l_input_value_id_tbl(1) := ln_inp_value_id;
2318 end if;
2319 close c_get_inp_val_id;
2320
2321 if ln_inp_value_id is NOT NULL then
2322 -- Get the value for the input value MULTIPLE specified
2323 -- for the Premium element. This value needs to be paased
2324 -- to the Adjustment element
2325 open c_get_scr_entry_value(p_element_entry_id
2326 ,'Multiple'
2327 ,ld_entry_end_date);
2328 fetch c_get_scr_entry_value into ln_screen_entry_value;
2329 if c_get_scr_entry_value%FOUND then
2330 l_entry_value_tbl(1) := ln_screen_entry_value;
2331 else
2332 l_entry_value_tbl(1) := NULL;
2333 end if; -- if c_get_scr_entry_value%FOUND
2334 close c_get_scr_entry_value;
2335 end if; -- if ln_inp_value_id is NOT NULL
2336
2337 -- Get the Element ENtgry ID details fo the recurring elements
2338 lvar := 0;
2339 open c_get_rec_elem_details(p_element_entry_id
2340 ,p_assignment_id);
2341 loop
2342 fetch c_get_rec_elem_details into ln_ele_entry_id
2343 ,ld_eff_start_date;
2344 exit when c_get_rec_elem_details%NOTFOUND;
2345 l_elem_entry_id_tbl(lvar) := ln_ele_entry_id;
2346 l_del_start_date_tbl(lvar) := ld_eff_start_date;
2347 lvar := lvar + 1;
2348 end loop;
2349
2350 for lvar in l_elem_entry_id_tbl.first..l_elem_entry_id_tbl.last
2351 loop
2352 hr_entry_api.update_element_entry
2353 (p_dt_update_mode => 'CORRECTION'
2354 ,p_session_date => l_del_start_date_tbl(lvar)
2355 ,p_element_entry_id => l_elem_entry_id_tbl(lvar)
2356 ,p_num_entry_values => l_entry_value_tbl.count()
2357 ,p_input_value_id_tbl => l_input_value_id_tbl
2358 ,p_entry_value_tbl => l_entry_value_tbl);
2359 end loop; -- for lvar in 1..
2360 end if; -- if ln_count > 0
2361 else
2362 if ln_count = 0 then
2363 -- Create the Mop up as it does not exist
2364 hr_utility.trace('Creating Mop Up Element');
2365 CREATE_PREMIUM_MOP_UP_ELEMENT(
2366 p_element_entry_id
2367 ,p_effective_start_date
2368 ,p_effective_end_date
2369 ,p_assignment_id
2370 ,p_element_link_id
2371 ,p_original_entry_id
2372 ,p_creator_type
2373 ,p_entry_type
2374 ,p_entry_information_category);
2375 end if; -- if ln_count = 0
2376 end if; -- if c_check_mop_up_exists%FOUND
2377
2378 end if;
2379
2380 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_PREMIUM_MOP_UP_ELEMENT');
2381 return;
2382
2383 END UPDATE_PREMIUM_MOP_UP_ELEMENT;
2384
2385
2386 /******************************************************************************
2387 Name : UPDATE_USER_HOOK
2388 Scope : GLOBAL
2389 Description : This procedure is called by AFTER UPDATE Row Level handler
2390 User Hook.
2391 ******************************************************************************/
2392 PROCEDURE UPDATE_USER_HOOK(
2393 p_element_entry_id in number
2394 ,p_effective_start_date in date
2395 ,p_effective_end_date in date
2396 ,p_assignment_id_o in number
2397 ,p_element_link_id_o in number
2398 ,p_original_entry_id_o in number
2399 ,p_creator_type_o in varchar2
2400 ,p_entry_type_o in varchar2
2401 ,p_entry_information_category_o in varchar2) IS
2402 BEGIN
2403 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_USER_HOOK');
2404
2405 -- Update the Recurring Element Entry associated with
2406 -- the Augment element
2407 UPDATE_RECUR_ELEM_ENTRY(p_element_entry_id
2408 ,p_effective_start_date
2409 ,p_effective_end_date
2410 ,p_assignment_id_o
2411 ,p_element_link_id_o
2412 ,p_original_entry_id_o
2413 ,p_creator_type_o
2414 ,p_entry_type_o
2415 ,p_entry_information_category_o);
2416
2417 -- Update the Mop Up Element Entry associated with
2418 -- the Premium element
2419 UPDATE_PREMIUM_MOP_UP_ELEMENT(p_element_entry_id
2420 ,p_effective_start_date
2421 ,p_effective_end_date
2422 ,p_assignment_id_o
2423 ,p_element_link_id_o
2424 ,p_original_entry_id_o
2425 ,p_creator_type_o
2426 ,p_entry_type_o
2427 ,p_entry_information_category_o);
2428
2429 -- Call CREATE_TAX_REACORDS
2430 -- We need to create TAX RECORDS if JURSIDICTION input value is updated
2431 -- for the employee and the employee does not have any tax records for
2432 -- that state.
2433 CREATE_TAX_RECORDS(p_element_entry_id
2434 ,p_effective_start_date
2435 ,p_effective_end_date
2436 ,p_assignment_id_o
2437 ,p_element_link_id_o
2438 ,p_original_entry_id_o
2439 ,p_creator_type_o
2440 ,p_entry_type_o
2441 ,p_entry_information_category_o);
2442
2443 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.UPDATE_USER_HOOK');
2444 return;
2445 END UPDATE_USER_HOOK;
2446
2447 -----------------------------UPDATE SECTION ENDS HERE--------------------------
2448
2449 -----------------------------DELETE SECTION BEGINS HERE------------------------
2450
2451 /******************************************************************************
2452 Name : DELETE_DEPENDENT_ENTRIES
2453 Scope : LOCAL
2454 Description : This procedure is called by AFTER DELETE Row Level handler
2455 User Hook.
2456 ******************************************************************************/
2457 PROCEDURE DELETE_PREM_MOP_UP_ELE_ENTRY(
2458 p_element_entry_id in number
2459 ,p_effective_start_date in date
2460 ,p_effective_end_date in date
2461 ,p_assignment_id in number
2462 ,p_element_link_id in number
2463 ,p_original_entry_id in number
2464 ,p_creator_type in varchar2
2465 ,p_entry_type in varchar2
2466 ,p_entry_information_category in varchar2) IS
2467
2468 -- Get the End date of the recurring element.
2469 cursor c_get_entry_end_date(c_element_entry_id number
2470 ,c_assignment_id number) is
2471 select max(peef.effective_end_date)
2472 from pay_element_entries_f peef
2473 where peef.element_entry_id = c_element_entry_id
2474 and peef.assignment_id = c_assignment_id;
2475
2476 ln_business_group_id number;
2477 lv_element_name varchar2(200);
2478 lv_processing_type varchar2(10);
2479 ld_entry_end_date date;
2480 ld_end_of_time date;
2481 lb_prem_flag boolean;
2482
2483 BEGIN
2484 hr_utility.trace
2485 ('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_PREM_MOP_UP_ELE_ENTRY');
2486
2487 ld_end_of_time := fnd_date.canonical_to_date('4712/12/31');
2488
2489 lb_prem_flag := CHECK_PREMIUM_ELEM(p_element_entry_id
2490 ,p_effective_start_date
2491 ,lv_element_name
2492 ,lv_processing_type
2493 ,ln_business_group_id);
2494
2495 if lb_prem_flag then
2496 open c_get_entry_end_date(p_element_entry_id
2497 ,p_assignment_id);
2498 fetch c_get_entry_end_date into ld_entry_end_date;
2499 if c_get_entry_end_date%NOTFOUND then
2500 hr_utility.trace('Deleting Mop Up Element Entry');
2501 -- Delete the Recurring Element Entry associated with
2502 -- the Augment element
2503 DELETE_DEPENDENT_ENTRIES(p_element_entry_id
2504 ,p_assignment_id);
2505 elsif ld_entry_end_date <> ld_end_of_time then
2506 -- Create the Mop up as it does not exist
2507 hr_utility.trace('Creating Mop Up Element');
2508 CREATE_PREMIUM_MOP_UP_ELEMENT(
2509 p_element_entry_id
2510 ,p_effective_start_date
2511 ,p_effective_end_date
2512 ,p_assignment_id
2513 ,p_element_link_id
2514 ,p_original_entry_id
2515 ,p_creator_type
2516 ,p_entry_type
2517 ,p_entry_information_category);
2518 end if; -- if c_get_entry_end_date
2519 close c_get_entry_end_date;
2520 end if; -- if lb_prem_flag
2521
2522 hr_utility.trace
2523 ('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_PREM_MOP_UP_ELE_ENTRY');
2524 return;
2525 END DELETE_PREM_MOP_UP_ELE_ENTRY;
2526
2527 /******************************************************************************
2528 Name : DELETE_USER_HOOK
2529 Scope : GLOBAL
2530 Description : This procedure is called by AFTER DELETE Row Level handler
2531 User Hook.
2532 ******************************************************************************/
2533 PROCEDURE DELETE_USER_HOOK(
2534 p_element_entry_id in number
2535 ,p_effective_start_date in date
2536 ,p_effective_end_date in date
2537 ,p_assignment_id_o in number
2538 ,p_element_link_id_o in number
2539 ,p_original_entry_id_o in number
2540 ,p_creator_type_o in varchar2
2541 ,p_entry_type_o in varchar2
2542 ,p_entry_information_category_o in varchar2) IS
2543
2544 -- Check if the element entry Exists
2545 cursor c_chk_elem_entry_exists(c_element_entry_id number) is
2546 select 'Exist'
2547 from pay_element_entries_f
2548 where element_entry_id = c_element_entry_id;
2549
2550 lv_exists varchar2(10);
2551
2552 BEGIN
2553 hr_utility.trace('Entering PAY_US_ELEMENT_ENTRY_HOOK.DELETE_USER_HOOK');
2554
2555 -- Check if the Element Entry was Purged. If yes delete the associated
2556 -- 'FL' creator type elements.
2557 -- We do not have any way to check the type of element once it is deleted
2558 open c_chk_elem_entry_exists(p_element_entry_id);
2559 fetch c_chk_elem_entry_exists into lv_exists;
2560 if c_chk_elem_entry_exists%NOTFOUND then
2561 -- Delete the Element Entry associated with base element entry
2562 DELETE_DEPENDENT_ENTRIES(p_element_entry_id
2563 ,p_assignment_id_o);
2564 else
2565 -- Delete the Mop Up Element Entry associated with
2566 -- the Premium element
2567 DELETE_PREM_MOP_UP_ELE_ENTRY(p_element_entry_id
2568 ,p_effective_start_date
2569 ,p_effective_end_date
2570 ,p_assignment_id_o
2571 ,p_element_link_id_o
2572 ,p_original_entry_id_o
2573 ,p_creator_type_o
2574 ,p_entry_type_o
2575 ,p_entry_information_category_o);
2576
2577 end if; -- if c_chk_elem_entry_exists%NOTFOUND
2578 close c_chk_elem_entry_exists;
2579
2580 hr_utility.trace('Leaving PAY_US_ELEMENT_ENTRY_HOOK.DELETE_USER_HOOK');
2581 return;
2582 END DELETE_USER_HOOK;
2583
2584 -----------------------------DELETE SECTION ENDS HERE--------------------------
2585
2586 END PAY_US_ELEMENT_ENTRY_HOOK;
[Home] [Help]