1 PACKAGE BODY PAY_GB_TAX_CREDIT_PKG AS
2 /* $Header: pygbtaxc.pkb 120.1.12000000.2 2007/05/17 14:14:52 kthampan noship $ */
3
4 --
5 -- Private declarations
6 --
7
8 g_package VARCHAR2(31) := 'PAY_GB_TAX_CREDIT_PKG';
9
10 FUNCTION Get_Input_Value_Id(
11 p_name in VARCHAR2,
12 p_effective_date in DATE
13 ) RETURN NUMBER is
14 l_input_value_id PAY_INPUT_VALUES_F.input_value_id%TYPE;
15
16 BEGIN
17
18 SELECT ipv.input_value_id INTO l_input_value_id
19 FROM PAY_INPUT_VALUES_F ipv,
20 PAY_ELEMENT_TYPES_F ele
21 WHERE ele.element_name = 'Tax Credit'
22 AND ipv.name = p_name
23 AND ele.element_type_id = ipv.element_type_id
24 AND p_effective_date between ele.effective_start_date
25 and ele.effective_end_date
26 AND p_effective_date between ipv.effective_start_date
27 and ipv.effective_end_date;
28
29 RETURN l_input_value_id;
30
31 END Get_Input_Value_Id;
32
33 --
34 -- Public Declarations
35 --
36
37 FUNCTION Get_Element_Link_Id(
38 p_assignment_id in NUMBER
39 ) RETURN number is
40
41 Cursor c_effective_date is
42 select effective_date
43 from fnd_sessions
44 where session_id = userenv('sessionid');
45
46 Cursor c_element_type is
47 select element_type_id
48 from pay_element_types
49 where element_name = 'Tax Credit';
50
51 l_element_link_id PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
52 l_element_id PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;
53 l_create_warning BOOLEAN;
54 l_effective_date DATE;
55
56 BEGIN
57
58 Open c_effective_date;
59 Fetch c_effective_date into l_effective_date;
60 Close c_effective_date;
61
62 Open c_element_type;
63 Fetch c_element_type into l_element_id;
64 Close c_element_type;
65
66 l_element_link_id := hr_entry_api.get_link(
67 p_assignment_id,
68 l_element_id,
69 l_effective_date);
70 RETURN l_element_link_id;
71
72 END Get_Element_Link_Id;
73 --
74 --
75 --
76 PROCEDURE Check_Start_date(p_assignment_id in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
77 p_element_entry_id in PAY_ELEMENT_ENTRIES_F.element_entry_id%TYPE,
78 p_start_date in DATE,
79 p_element_name in VARCHAR2 default 'Tax Credit',
80 p_message out nocopy VARCHAR2) is
81 --
82 -- Note: Checks for errors first, then for warnings
83 -- finding one error stops validation, so user may get a succession of
84 -- different error messages as the date is entered.
85 --
86 cursor c_tax_credit_overlap(p_asg in NUMBER,
87 p_element_entry_id in NUMBER,
88 p_start_date in DATE,
89 p_ele in VARCHAR2
90 ) is
91 Select 'Y'
92 FROM pay_element_entry_values_f eev1,
93 pay_element_entry_values_f eev2,
94 pay_element_entry_values_f eev3,
95 pay_element_entries_f ent,
96 pay_input_values_f ipv1,
97 pay_input_values_f ipv2,
98 pay_input_values_f ipv3,
99 pay_element_links_f lnk,
100 pay_element_types_f ele
101 where ele.element_name = p_ele
102 and ele.element_type_id = lnk.element_type_id
103 and ent.element_link_id = lnk.element_link_id
104 and ent.assignment_id = p_asg
105 and ipv1.element_type_id = ele.element_type_id
106 and ipv2.element_type_id = ele.element_type_id
107 and ipv3.element_type_id = ele.element_type_id
108 and ipv1.name = 'Start Date'
109 and ipv2.name = 'End Date'
110 and ipv3.name = 'Stop Date'
111 and ipv1.input_value_id = eev1.input_value_id
112 and ipv2.input_value_id = eev2.input_value_id
113 and ipv3.input_value_id = eev3.input_value_id
114 and eev1.element_entry_id = ent.element_entry_id
115 and eev2.element_entry_id = ent.element_entry_id
116 and eev3.element_entry_id = ent.element_entry_id
117 and (ent.element_entry_id <> p_element_entry_id
118 or p_element_entry_id is null)
119 and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_start_date)
120 and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
121 OR (eev3.screen_entry_value is NULL
122 and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
123 OR eev2.screen_entry_value is NULL )))
124 and p_start_date between
125 eev1.effective_start_date and eev1.effective_end_date
126 and p_start_date between
127 eev2.effective_start_date and eev2.effective_end_date
128 and p_start_date between
129 eev3.effective_start_date and eev3.effective_end_date
130 and p_start_date between
131 ent.effective_start_date and ent.effective_end_date
132 and p_start_date between
133 lnk.effective_start_date and lnk.effective_end_date
134 and p_start_date between
135 ele.effective_start_date and ele.effective_end_date
139 ipv2.effective_start_date and ipv2.effective_end_date
136 and p_start_date between
137 ipv1.effective_start_date and ipv1.effective_end_date
138 and p_start_date between
140 and p_start_date between
141 ipv3.effective_start_date and ipv3.effective_end_date;
142
143 cursor c_student_loan_overlap(p_asg in NUMBER,
144 p_element_entry_id in NUMBER,
145 p_start_date in DATE,
146 p_ele in VARCHAR2
147 ) is
148 Select 'Y'
149 FROM pay_element_entry_values_f eev1,
150 pay_element_entry_values_f eev2,
151 pay_element_entries_f ent,
152 pay_input_values_f ipv1,
153 pay_input_values_f ipv2,
154 pay_element_links_f lnk,
155 pay_element_types_f ele
156 where ele.element_name = p_ele
157 and ele.element_type_id = lnk.element_type_id
158 and ent.element_link_id = lnk.element_link_id
159 and ent.assignment_id = p_asg
160 and ipv1.element_type_id = ele.element_type_id
161 and ipv2.element_type_id = ele.element_type_id
162 and ipv1.name = 'Start Date'
163 and ipv2.name = 'End Date'
164 and ipv1.input_value_id = eev1.input_value_id
165 and ipv2.input_value_id = eev2.input_value_id
166 and eev1.element_entry_id = ent.element_entry_id
167 and eev2.element_entry_id = ent.element_entry_id
168 and (ent.element_entry_id <> p_element_entry_id
169 or p_element_entry_id is null)
170 and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_start_date)
171 and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
172 OR (eev2.screen_entry_value is NULL));
173
174 cursor c_tax_credit_starting(p_asg in NUMBER,
175 p_element_entry_id in NUMBER,
176 p_start_date in DATE,
177 p_ele in VARCHAR2) is
178 Select 'Y'
179 FROM pay_element_entry_values_f eev1,
180 pay_element_entry_values_f eev2,
181 pay_element_entry_values_f eev3,
182 pay_element_entries_f ent,
183 pay_input_values_f ipv1,
184 pay_input_values_f ipv2,
185 pay_input_values_f ipv3,
186 pay_element_links_f lnk,
187 pay_element_types_f ele
188 where ele.element_name = p_ele
189 and ele.element_type_id = lnk.element_type_id
190 and ent.element_link_id = lnk.element_link_id
191 and ent.assignment_id = p_asg
192 and ipv1.element_type_id = ele.element_type_id
193 and ipv2.element_type_id = ele.element_type_id
194 and ipv3.element_type_id = ele.element_type_id
195 and ipv1.name = 'Start Date'
196 and ipv2.name = 'End Date'
197 and ipv3.name = 'Stop Date'
198 and ipv1.input_value_id = eev1.input_value_id
199 and ipv2.input_value_id = eev2.input_value_id
200 and ipv3.input_value_id = eev3.input_value_id
201 and eev1.element_entry_id = ent.element_entry_id
202 and eev2.element_entry_id = ent.element_entry_id
203 and eev3.element_entry_id = ent.element_entry_id
204 and (ent.element_entry_id <> p_element_entry_id
205 or p_element_entry_id is null)
206 and eev1.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
207 and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
208 OR (eev3.screen_entry_value is NULL
209 and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
210 OR eev2.screen_entry_value is NULL )));
211
212 cursor c_student_loan_starting(p_asg in NUMBER,
213 p_element_entry_id in NUMBER,
214 p_start_date in DATE,
215 p_ele in VARCHAR2) is
216 Select 'Y'
217 FROM pay_element_entry_values_f eev1,
218 pay_element_entry_values_f eev2,
219 pay_element_entries_f ent,
220 pay_input_values_f ipv1,
221 pay_input_values_f ipv2,
222 pay_element_links_f lnk,
223 pay_element_types_f ele
224 where ele.element_name = p_ele
225 and ele.element_type_id = lnk.element_type_id
226 and ent.element_link_id = lnk.element_link_id
227 and ent.assignment_id = p_asg
228 and ipv1.element_type_id = ele.element_type_id
229 and ipv2.element_type_id = ele.element_type_id
230 and ipv1.name = 'Start Date'
231 and ipv2.name = 'End Date'
232 and ipv1.input_value_id = eev1.input_value_id
233 and ipv2.input_value_id = eev2.input_value_id
234 and eev1.element_entry_id = ent.element_entry_id
235 and eev2.element_entry_id = ent.element_entry_id
236 and (ent.element_entry_id <> p_element_entry_id
237 or p_element_entry_id is null)
238 and eev1.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
239 and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
240 OR (eev2.screen_entry_value is NULL));
241
242 cursor c_tax_credit_prior_to_runs(p_asg in NUMBER
243 ) is
244 select 'Y'
245 from per_time_periods ptp,
246 pay_payroll_actions ppa,
247 pay_assignment_actions paa,
248 per_all_assignments_f asg
249 where asg.assignment_id = p_asg
250 and asg.payroll_id = ppa.payroll_id
251 and paa.assignment_id = asg.assignment_id
252 and paa.payroll_action_id = ppa.payroll_action_id
253 and ptp.time_period_id = ppa.time_period_id
254 and ppa.action_type = 'R'
255 and ppa.action_status='C'
259 select actual_termination_date,
256 and ptp.end_date > p_start_date;
257
258 cursor c_terminated(p_asg in NUMBER) is
260 last_standard_process_date,
261 final_process_date
262 from per_periods_of_service pos,
263 fnd_sessions ses,
264 per_all_assignments_f asg
265 where asg.assignment_id = p_asg
266 and ses.session_id = userenv('sessionid')
267 and ses.effective_date between asg.effective_start_date and asg.effective_end_date
268 and asg.person_id = pos.person_id
269 and ses.effective_date between pos.date_start and pos.last_standard_process_date
270 and pos.actual_termination_date is not null;
271
272 cursor c_periods_left(p_asg in NUMBER,
273 p_termination_date in DATE) is
274 select count(*)
275 from per_time_periods ptp,
276 per_all_assignments_f asg
277 where asg.assignment_id = p_asg
278 and asg.payroll_id = ptp.payroll_id
279 and ptp.end_date <= p_termination_date
280 and ptp.end_date >= p_start_date;
281
282 l_dummy VARCHAR2(2);
283 l_actual_termination_date DATE;
284 l_last_standard_process_date DATE;
285 l_final_process_date DATE;
286 l_date_to_check DATE;
287 l_periods_left NUMBER;
288 l_periods_within_check NUMBER := 3;
289
290
291
292
293 BEGIN
294
295 p_message := 'Passed';
296
297 if p_element_name = 'Tax Credit' then
298 open c_tax_credit_overlap(p_assignment_id, p_element_entry_id, p_start_date,p_element_name);
299 fetch c_tax_credit_overlap into l_dummy;
300
301 if c_tax_credit_overlap%FOUND then
302 close c_tax_credit_overlap;
303 p_message := 'HR_78002_TXC_TAXCREDIT_OVERLAP';
304 else
305 --
306 -- Nothing overlapping, continue with other validation
307 --
308 close c_tax_credit_overlap;
309 --
310 -- Check that the start date of the entry is not before the latest
311 -- payroll run, in which case the tax credit will not be processed
312 --
313 open c_tax_credit_prior_to_runs(p_assignment_id);
314 fetch c_tax_credit_prior_to_runs into l_dummy;
315
316 if c_tax_credit_prior_to_runs%FOUND then
317 close c_tax_credit_prior_to_runs;
318 p_message := 'HR_78004_TXC_BEFORE_PAYROLL';
319 else
320 close c_tax_credit_prior_to_runs;
321 --
322 -- No Runs exist, continue with other validation
323 --
324 --
325 -- Check to see if a tax credit is starting in the future, in which case,
326 -- the user must supply an end date before the start date of the future tax
327 -- credit
328 --
329 open c_tax_credit_starting(p_assignment_id, p_element_entry_id, p_start_date,p_element_name);
330 fetch c_tax_credit_starting into l_dummy;
331
332 if c_tax_credit_starting%FOUND then
333 close c_tax_credit_starting;
334 p_message := 'HR_78003_TXC_TAXCREDIT_START';
335 else
336 --
337 -- Nothing starting, continue with other validation
338 --
339 close c_tax_credit_starting;
340
341 open c_terminated(p_assignment_id);
342 fetch c_terminated into l_actual_termination_date,
343 l_last_standard_process_date,
344 l_final_process_date;
345 if c_terminated%FOUND then
346 --
347 -- Employee has been terminated
348 -- Work out which date to check
349 --
350 close c_terminated;
351 if l_last_standard_process_date is not null then
352 l_date_to_check := l_last_standard_process_date;
353 else
354 l_date_to_check := l_actual_termination_date;
355 end if;
356 --
357 -- Count the remaining periods
358 --
359 open c_periods_left(p_assignment_id, l_date_to_check);
360 fetch c_periods_left into l_periods_left;
361 if c_periods_left%NOTFOUND then
362 --
363 -- Shouldn't be possible!
364 --
365 null;
366 end if;
367 close c_periods_left;
368 if l_periods_left < l_periods_within_check then
369 p_message := 'HR_78005_TXC_TOO_FEW_PERIODS';
370 end if;
371
372 else
373 close c_terminated;
374
375 end if; -- Check for termination (Warning)
376 end if; -- Starting check (Warning)
377 end if; -- Payroll after Start Date check (Error)
378 end if; -- Overlap check (Error)
379
380 elsif p_element_name = 'Student Loan' then
381 --
382 -- Do validation for student loan Start Date
383 --
384 open c_student_loan_overlap(p_assignment_id, p_element_entry_id, p_start_date,p_element_name);
385 fetch c_student_loan_overlap into l_dummy;
386
387 if c_student_loan_overlap%FOUND then
388 close c_student_loan_overlap;
389 p_message := 'HR_78024_SLC_START_OVERLAP';
390 else
391 --
392 -- Nothing overlapping, continue with other validation
393 --
394 close c_student_loan_overlap;
395 --
396 -- Check that the start date of the entry is not before the latest
397 -- payroll run, in which case the tax credit will not be processed
398 --
399 open c_tax_credit_prior_to_runs(p_assignment_id);
400 fetch c_tax_credit_prior_to_runs into l_dummy;
401
402 if c_tax_credit_prior_to_runs%FOUND then
403 close c_tax_credit_prior_to_runs;
404 p_message := 'HR_78025_SLC_START_PROCESSED';
405 else
406 close c_tax_credit_prior_to_runs;
407 --
408 -- No Runs exist, continue with other validation
409 --
410 --
411 -- Check to see if a tax credit is starting in the future, in which case,
415 open c_student_loan_starting(p_assignment_id, p_element_entry_id, p_start_date,p_element_name);
412 -- the user must supply an end date before the start date of the future tax
413 -- credit
414 --
416 fetch c_student_loan_starting into l_dummy;
417
418 if c_student_loan_starting%FOUND then
419 close c_student_loan_starting;
420 p_message := 'HR_78026_SLC_START_NO_END';
421 else
422 --
423 -- Nothing starting, continue with other validation
424 --
425 close c_student_loan_starting;
426 end if; -- check for student loan starting
427 end if; -- check for student loan prior to processing
428 end if; -- check for student loan overlap
429
430 end if; -- Which element type, tax credit or student loan
431 --
432 END Check_Start_Date;
433
434 PROCEDURE Check_End_Or_Stop_Date(p_assignment_id in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
435 p_element_entry_id in PAY_ELEMENT_ENTRIES_F.element_entry_id%TYPE,
436 p_end_date in DATE,
437 p_start_date in DATE,
438 p_element_name in PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE,
439 p_message out nocopy VARCHAR2) is
440
441 CURSOR c_end_after_start_date(p_asg in NUMBER,
442 p_element_entry_id in NUMBER,
443 p_end_date in DATE,
444 p_start_date in DATE,
445 p_ele in VARCHAR2
446 ) is
447 Select 'Y'
448 FROM pay_element_entry_values_f eev1,
449 pay_element_entry_values_f eev2,
450 pay_element_entry_values_f eev3,
451 pay_element_entries_f ent,
452 pay_input_values_f ipv1,
453 pay_input_values_f ipv2,
454 pay_input_values_f ipv3,
455 pay_element_links_f lnk,
456 pay_element_types_f ele
457 where ele.element_name = p_ele
458 and ele.element_type_id = lnk.element_type_id
459 and ent.element_link_id = lnk.element_link_id
460 and ent.assignment_id = p_asg
461 and ipv1.element_type_id = ele.element_type_id
462 and ipv2.element_type_id = ele.element_type_id
463 and ipv3.element_type_id = ele.element_type_id
464 and ipv1.name = 'Start Date'
465 and ipv2.name = 'End Date'
466 and ipv3.name = 'Stop Date'
467 and ipv1.input_value_id = eev1.input_value_id
468 and ipv2.input_value_id = eev2.input_value_id
469 and ipv3.input_value_id = eev3.input_value_id
470 and eev1.element_entry_id = ent.element_entry_id
471 and eev2.element_entry_id = ent.element_entry_id
472 and eev3.element_entry_id = ent.element_entry_id
473 and (ent.element_entry_id <> p_element_entry_id
474 or p_element_entry_id is null)
475 and eev1.screen_entry_value <= fnd_date.date_to_canonical(p_end_date)
476 and (eev3.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
477 OR (eev3.screen_entry_value is NULL
478 and (eev2.screen_entry_value >= fnd_date.date_to_canonical(p_start_date)
479 OR eev2.screen_entry_value is NULL )))
480 and p_start_date between
481 eev1.effective_start_date and eev1.effective_end_date
482 and p_start_date between
483 eev2.effective_start_date and eev2.effective_end_date
484 and p_start_date between
485 eev3.effective_start_date and eev3.effective_end_date
486 and p_start_date between
487 ent.effective_start_date and ent.effective_end_date
488 and p_start_date between
489 lnk.effective_start_date and lnk.effective_end_date
490 and p_start_date between
491 ele.effective_start_date and ele.effective_end_date
492 and p_start_date between
493 ipv1.effective_start_date and ipv1.effective_end_date
494 and p_start_date between
495 ipv2.effective_start_date and ipv2.effective_end_date
496 and p_start_date between
497 ipv3.effective_start_date and ipv3.effective_end_date;
498
499 CURSOR c_before_a_payroll_run(p_asg in NUMBER
500 ) is
501 select 'Y'
502 from per_time_periods ptp,
503 pay_payroll_actions ppa,
504 per_all_assignments_f asg
505 where asg.assignment_id = p_asg
506 and asg.payroll_id = ppa.payroll_id
507 and ptp.time_period_id = ppa.time_period_id
508 and ppa.action_type = 'R'
509 and ppa.action_status='C'
510 and ptp.end_date > p_end_date;
511
512
513 l_dummy VARCHAR2(2);
514
515 BEGIN
516
517 p_message := 'Passed';
518
519 open c_end_after_start_date(p_assignment_id,
520 p_element_entry_id,
521 p_end_date,
522 p_start_date,
523 p_element_name);
524
525 fetch c_end_after_start_date into l_dummy;
526
527 if c_end_after_start_date%FOUND then
528 close c_end_after_start_date;
529 if p_element_name = 'Tax Credit' then
530 p_message := 'HR_78008_TXC_END_DATE_OVERLAP';
531 elsif p_element_name = 'Student Loan' then
532 p_message := 'HR_78027_SLC_END_DATE_OVERLAP';
533 end if;
534 else
535 close c_end_after_start_date;
536 --
537 -- End Date Not overlapping, continue with other validation
538 --
539 open c_before_a_payroll_run(p_assignment_id);
540 fetch c_before_a_payroll_run into l_dummy;
541 if c_before_a_payroll_run%FOUND then
542 close c_before_a_payroll_run;
546 p_message := 'HR_78028_SLC_END_BEF_PROC';
543 if p_element_name = 'Tax Credit' then
544 p_message := 'HR_78017_TXC_ENDDATE_BEF_PROC';
545 elsif p_element_name = 'Student Loan' then
547 end if;
548 else
549 close c_before_a_payroll_run;
550 end if;
551 --
552 -- Currently not before a payroll run, continue with other validation
553 --
554 end if; -- End date overlapping
555
556
557 END Check_End_Or_Stop_Date;
558
559 PROCEDURE Check_Delete_Possible(
560 p_datetrack_mode in VARCHAR2,
561 p_effective_date in DATE,
562 p_assignment_id in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
563 p_start_date in DATE,
564 p_end_date in DATE,
565 p_message out nocopy VARCHAR2) is
566
567 cursor c_purge_allowed(p_asg in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE) is
568 select max(effective_date)
569 from pay_payroll_actions ppa,
570 pay_assignment_actions paa,
571 per_all_assignments_f asg
572 where asg.assignment_id = p_asg
573 and asg.assignment_id = paa.assignment_id
574 and paa.payroll_action_id = ppa.payroll_action_id
575 and asg.payroll_id = ppa.payroll_id
576 and ppa.action_type = 'R'
577 and ppa.action_status='C';
578
579 cursor c_end_date_allowed(p_asg in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
580 p_date in DATE) is
581 select /*+ ORDERED
582 INDEX(ptp PER_TIME_PERIODS_PK) */ 'Y'
583 from per_all_assignments_f asg,
584 pay_payroll_actions ppa,
585 per_time_periods ptp
586 where asg.assignment_id = p_asg
587 and asg.payroll_id = ppa.payroll_id
588 and ptp.time_period_id = ppa.time_period_id
589 and ppa.action_type = 'R'
590 and ppa.action_status='C'
591 and ptp.end_date > p_date;
592
593 l_purge_date DATE;
594 l_dummy VARCHAR2(2);
595
596 BEGIN
597
598 p_message := 'Passed';
599
600 --
601 -- If the datetrack mode is purge, then see if there are any
602 -- processed entries, perhaps ought to check through entries
603 -- that these have been processed, but I think it is enough
604 -- just to check for later payroll runs.
605 --
606
607 if p_datetrack_mode = 'ZAP' then
608
609 open c_purge_allowed(p_assignment_id);
610 fetch c_purge_allowed into l_purge_date;
611
612 if c_purge_allowed%FOUND then
613 close c_purge_allowed;
614 if p_start_date < l_purge_date then
615 p_message := 'HR_78014_TXC_NO_PURGE';
616 end if;
617 else
618 close c_purge_allowed;
619 end if;
620
621 elsif p_datetrack_mode = 'DELETE' then
622
623 open c_end_date_allowed(p_assignment_id, p_effective_date);
624 fetch c_end_date_allowed into l_dummy;
625
626 if c_end_date_allowed%FOUND then
627 close c_end_date_allowed;
628 p_message := 'HR_78015_TXC_NO_END_DATE';
629 else
630 close c_end_date_allowed;
631 end if;
632
633 end if;
634
635 End Check_Delete_Possible;
636
637
638 Procedure Check_Daily_Rate(
639 p_assignment_id in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
640 p_start_date in DATE,
641 p_message out nocopy VARCHAR2
642 ) is
643
644 cursor c_processed(p_asg in PAY_ELEMENT_ENTRIES_F.assignment_id%TYPE,
645 p_date in DATE) is
646 select 'Y'
647 from pay_payroll_actions ppa,
648 per_all_assignments_f asg
649 where asg.assignment_id = p_asg
650 and asg.payroll_id = ppa.payroll_id
651 and ppa.action_type = 'R'
652 and ppa.action_status='C'
653 and ppa.effective_date > p_date;
654
655
656 l_dummy VARCHAR2(2);
657
658 BEGIN
659
660 p_message := 'Passed';
661
662 open c_processed(p_assignment_id, p_start_date);
663 fetch c_processed into l_dummy;
664
665 if c_processed%FOUND then
666 close c_processed;
667 p_message := 'HR_78016_TXC_DAILY_RATE_PROC';
668 else
669 close c_processed;
670 end if;
671
672 END Check_Daily_Rate;
673
674 PROCEDURE Fetch_Balances(
675 p_assignment_id in PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID%TYPE,
676 p_element_entry_id in PAY_RUN_RESULTS.SOURCE_ID%TYPE,
677 p_itd_balance OUT NOCOPY NUMBER,
678 p_ptd_balance OUT NOCOPY NUMBER
679 ) is
680
681 cursor c_balance_id is
682 select balance_type_id
683 from pay_balance_types
684 where balance_name = 'Tax Credit';
685
686 cursor c_itd_asgact(p_asg in NUMBER,
687 p_ent_id in NUMBER) is
688 select prr.assignment_action_id,
689 prr.source_id
690 from pay_run_results prr,
691 pay_element_types_f ele
692 where prr.assignment_action_id in (
693 SELECT to_number(substr(max(lpad(paa.action_sequence,15,'0')||
694 paa.assignment_action_id),16))
695 FROM pay_assignment_actions paa,
696 pay_payroll_actions ppa,
697 per_time_periods ptp,
698 fnd_sessions ses
699 WHERE paa.assignment_id = p_asg
700 AND ses.session_id = userenv('sessionid')
701 AND ppa.payroll_action_id = paa.payroll_action_id
702 AND ses.effective_date between ptp.start_date and ptp.end_date
706 AND ele.element_name = 'Tax Credit'
703 AND ppa.time_period_id = ptp.time_period_id
704 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B'))
705 AND prr.element_type_id = ele.element_type_id
707 AND prr.source_id = p_ent_id;
708
709 -- BUG 3221422 Changed Query for improving performance
710 cursor c_ptd_asgact(p_asg in NUMBER,
711 p_ent in NUMBER) is
712 select prr.assignment_action_id,
713 prr.source_id
714 from pay_run_results prr,
715 pay_element_types_f ele
716 where prr.assignment_action_id in (
717 select to_number(substr(max(lpad(paa.action_sequence,15,'0')||
718 paa.assignment_action_id),16))
719 from pay_assignment_actions paa,
720 pay_payroll_actions ppa,
721 fnd_sessions ses,
722 per_time_periods ptp,
723 per_all_assignments per
724 where paa.assignment_id = p_asg
725 and ses.session_id = userenv('sessionid')
726 and ptp.payroll_id = ppa.payroll_id
727 and ses.effective_date between ptp.start_date and ptp.end_date
728 and ppa.effective_date between ptp.start_date and ptp.end_date
729 and ppa.payroll_action_id = paa.payroll_action_id
730 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
731 and ppa.payroll_id = per.payroll_id
732 and paa.assignment_id = per.assignment_id
733 and ses.effective_date between per.effective_start_date and per.effective_end_date)
734 and prr.element_type_id = ele.element_type_id
735 and ele.element_name = 'Tax Credit'
736 and prr.source_id = p_ent;
737
738 l_proc VARCHAR(72) := g_package||'.FETCH_BALANCES';
739 l_itd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
740 l_itd_source_id PAY_RUN_RESULTS.source_id%TYPE;
741 l_ptd_action_id PAY_ASSIGNMENT_ACTIONS.assignment_action_id%TYPE;
742 l_ptd_source_id PAY_RUN_RESULTS.source_id%TYPE;
743 l_balance_type_id PAY_BALANCE_TYPES.balance_type_id%TYPE;
744 l_effective_date DATE;
745
746 BEGIN
747
748 hr_utility.set_location('Entering..'||l_proc,10);
749
750 open c_balance_id;
751 fetch c_balance_id into l_balance_type_id;
752 close c_balance_id;
753
754 open c_ptd_asgact(p_assignment_id,p_element_entry_id);
755 fetch c_ptd_asgact into l_ptd_action_id,l_ptd_source_id;
756
757 if c_ptd_asgact%NOTFOUND then
758 p_ptd_balance := NULL;
759 close c_ptd_asgact;
760 else
761 p_ptd_balance := hr_gbbal.calc_element_ptd_bal(
762 l_ptd_action_id,
763 l_balance_type_id,
764 l_ptd_source_id);
765 close c_ptd_asgact;
766 end if;
767
768 open c_itd_asgact(p_assignment_id,p_element_entry_id);
769 fetch c_itd_asgact into l_itd_action_id,l_itd_source_id;
770
771 if c_itd_asgact%NOTFOUND then
772 p_itd_balance := NULL;
773 close c_itd_asgact;
774 else
775 p_itd_balance := hr_gbbal.calc_element_itd_bal(
776 l_itd_action_id,
777 l_balance_type_id,
778 l_itd_source_id);
779 close c_itd_asgact;
780 end if;
781
782 hr_utility.set_location('leaving..'||l_proc,20);
783
784 END Fetch_Balances;
785
786
787 PROCEDURE Create_Tax_Credit(
788 p_effective_date in DATE
789 ,p_business_group_id in NUMBER
790 ,p_assignment_id in NUMBER
791 ,p_element_link_id in NUMBER
792 ,p_reference in VARCHAR2
793 ,p_start_date in VARCHAR2
794 ,p_end_date in VARCHAR2
795 ,p_daily_amount in VARCHAR2
796 ,p_total_amount in VARCHAR2
797 ,p_stop_date in VARCHAR2
798 ,p_reference_ipv_id in NUMBER
799 ,p_start_date_ipv_id in NUMBER
800 ,p_end_date_ipv_id in NUMBER
801 ,p_daily_amount_ipv_id in NUMBER
802 ,p_total_amount_ipv_id in NUMBER
803 ,p_stop_date_ipv_id in NUMBER
804 ,p_from in DATE
805 ,p_to in DATE
806 ,p_effective_start_date out nocopy DATE
807 ,p_effective_end_date out nocopy DATE
808 ,p_element_entry_id out nocopy NUMBER
809 ,p_object_version_number out nocopy NUMBER) is
810
811 l_create_warning BOOLEAN;
812
813 BEGIN
814
815 py_element_entry_api.create_element_entry(
816 P_VALIDATE =>FALSE,
817 P_EFFECTIVE_DATE =>p_effective_date,
818 P_BUSINESS_GROUP_ID =>p_business_group_id,
819 P_ORIGINAL_ENTRY_ID =>NULL,
820 P_ASSIGNMENT_ID =>p_assignment_id,
821 P_ELEMENT_LINK_ID =>p_element_link_id,
822 P_ENTRY_TYPE =>'E',
823 P_COST_ALLOCATION_KEYFLEX_ID=>NULL,
824 P_UPDATING_ACTION_ID =>NULL,
825 P_COMMENT_ID =>NULL,
826 P_REASON =>NULL,
827 P_TARGET_ENTRY_ID =>NULL,
828 P_SUBPRIORITY =>NULL,
829 P_DATE_EARNED =>NULL,
830 P_PERSONAL_PAYMENT_METHOD_ID=>NULL,
831 P_ATTRIBUTE_CATEGORY =>NULL,
832 P_ATTRIBUTE1 =>NULL,
833 P_ATTRIBUTE2 =>NULL,
834 P_ATTRIBUTE3 =>NULL,
835 P_ATTRIBUTE4 =>NULL,
836 P_ATTRIBUTE5 =>NULL,
837 P_ATTRIBUTE6 =>NULL,
838 P_ATTRIBUTE7 =>NULL,
839 P_ATTRIBUTE8 =>NULL,
840 P_ATTRIBUTE9 =>NULL,
841 P_ATTRIBUTE10 =>NULL,
842 P_ATTRIBUTE11 =>NULL,
843 P_ATTRIBUTE12 =>NULL,
844 P_ATTRIBUTE13 =>NULL,
848 P_ATTRIBUTE17 =>NULL,
845 P_ATTRIBUTE14 =>NULL,
846 P_ATTRIBUTE15 =>NULL,
847 P_ATTRIBUTE16 =>NULL,
849 P_ATTRIBUTE18 =>NULL,
850 P_ATTRIBUTE19 =>NULL,
851 P_ATTRIBUTE20 =>NULL,
852 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Reference',
853 p_effective_date),
854 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('Start Date',
855 p_effective_date),
856 P_INPUT_VALUE_ID3 =>Get_Input_Value_Id('End Date',
857 p_effective_date),
858 P_INPUT_VALUE_ID4 =>Get_Input_Value_Id('Daily Amount',
859 p_effective_date),
860 P_INPUT_VALUE_ID5 =>Get_Input_Value_Id('Total Amount',
861 p_effective_date),
862 P_INPUT_VALUE_ID6 =>Get_Input_Value_Id('Stop Date',
863 p_effective_date),
864 P_INPUT_VALUE_ID7 =>NULL,
865 P_INPUT_VALUE_ID8 =>NULL,
866 P_INPUT_VALUE_ID9 =>NULL,
867 P_INPUT_VALUE_ID10 =>NULL,
868 P_INPUT_VALUE_ID11 =>NULL,
869 P_INPUT_VALUE_ID12 =>NULL,
870 P_INPUT_VALUE_ID13 =>NULL,
871 P_INPUT_VALUE_ID14 =>NULL,
872 P_INPUT_VALUE_ID15 =>NULL,
873 P_ENTRY_VALUE1 =>p_reference,
874 P_ENTRY_VALUE2 =>p_start_date,
875 P_ENTRY_VALUE3 =>p_end_date,
876 P_ENTRY_VALUE4 =>p_daily_amount,
877 P_ENTRY_VALUE5 =>p_total_amount,
878 P_ENTRY_VALUE6 =>p_stop_date,
879 P_ENTRY_VALUE7 =>NULL,
880 P_ENTRY_VALUE8 =>NULL,
881 P_ENTRY_VALUE9 =>NULL,
882 P_ENTRY_VALUE10 =>NULL,
883 P_ENTRY_VALUE11 =>NULL,
884 P_ENTRY_VALUE12 =>NULL,
885 P_ENTRY_VALUE13 =>NULL,
886 P_ENTRY_VALUE14 =>NULL,
887 P_ENTRY_VALUE15 =>NULL,
888 P_EFFECTIVE_START_DATE =>p_effective_start_date,
889 P_EFFECTIVE_END_DATE =>p_effective_end_date,
890 P_ELEMENT_ENTRY_ID =>p_element_entry_id,
891 P_OBJECT_VERSION_NUMBER =>p_object_version_number,
892 P_CREATE_WARNING =>l_create_warning);
893
894 END Create_Tax_Credit;
895
896 PROCEDURE Delete_Tax_Credit(
897 p_datetrack_mode in VARCHAR2
898 ,p_element_entry_id in NUMBER
899 ,p_effective_date in DATE
900 ,p_object_version_number in NUMBER) IS
901
902 l_object_version_number NUMBER;
903 l_effective_start_date DATE;
904 l_effective_end_date DATE;
905 l_delete_warning BOOLEAN;
906
907 BEGIN
908
909 l_object_version_number := p_object_version_number;
910
911 py_element_entry_api.delete_element_entry(
912 p_validate => FALSE,
913 p_datetrack_delete_mode => p_datetrack_mode,
914 p_effective_date => p_effective_date,
915 p_element_entry_id => p_element_entry_id,
916 p_object_version_number => l_object_version_number,
917 p_effective_start_date => l_effective_start_date,
918 p_effective_end_date => l_effective_end_date,
919 p_delete_warning => l_delete_warning
920 );
921
922 END Delete_Tax_Credit;
923 --
924 -- Update_Tax_Credit process
925 --
926 PROCEDURE Update_Tax_Credit(
927 p_datetrack_update_mode in varchar2
928 ,p_effective_date in date
929 ,p_business_group_id in number
930 ,p_element_entry_id in number
931 ,p_object_version_number in out nocopy number
932 ,p_reference in VARCHAR2
933 ,p_start_date in VARCHAR2
934 ,p_end_date in VARCHAR2
935 ,p_daily_amount in VARCHAR2
936 ,p_total_amount in VARCHAR2
937 ,p_stop_date in VARCHAR2
938 ,p_reference_ipv_id in NUMBER
939 ,p_start_date_ipv_id in NUMBER
940 ,p_end_date_ipv_id in NUMBER
941 ,p_daily_amount_ipv_id in NUMBER
942 ,p_total_amount_ipv_id in NUMBER
943 ,p_stop_date_ipv_id in NUMBER
944 ,p_effective_start_date out nocopy date
945 ,p_effective_end_date out nocopy date) is
946
947 l_update_warning BOOLEAN;
948
949 BEGIN
950
951 py_element_entry_api.update_element_entry(
952 p_validate => FALSE,
953 p_datetrack_update_mode => p_datetrack_update_mode,
954 p_effective_date => p_effective_date,
955 p_business_group_id => p_business_group_id,
956 p_element_entry_id => p_element_entry_id,
957 p_object_version_number => p_object_version_number,
958 P_INPUT_VALUE_ID1 =>Get_Input_Value_Id('Reference',
959 p_effective_date),
960 P_INPUT_VALUE_ID2 =>Get_Input_Value_Id('Start Date',
961 p_effective_date),
962 P_INPUT_VALUE_ID3 =>Get_Input_Value_Id('End Date',
963 p_effective_date),
964 P_INPUT_VALUE_ID4 =>Get_Input_Value_Id('Daily Amount',
965 p_effective_date),
966 P_INPUT_VALUE_ID5 =>Get_Input_Value_Id('Total Amount',
967 p_effective_date),
968 P_INPUT_VALUE_ID6 =>Get_Input_Value_Id('Stop Date',
969 p_effective_date),
970 P_ENTRY_VALUE1 =>p_reference,
971 P_ENTRY_VALUE2 =>p_start_date,
975 P_ENTRY_VALUE6 =>p_stop_date,
972 P_ENTRY_VALUE3 =>p_end_date,
973 P_ENTRY_VALUE4 =>p_daily_amount,
974 P_ENTRY_VALUE5 =>p_total_amount,
976 P_EFFECTIVE_START_DATE =>p_effective_start_date,
977 P_EFFECTIVE_END_DATE =>p_effective_end_date,
978 P_UPDATE_WARNING =>l_update_warning);
979 --
980 -- Done the update
981 --
982 END Update_Tax_Credit;
983
984 END PAY_GB_TAX_CREDIT_PKG;
[Home] [Help]