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