1 package body PAY_ELEMENT_LINKS_PKG as
2 /* $Header: pyeli.pkb 120.2.12010000.2 2008/08/06 07:09:30 ubhat ship $ */
3 /*===========================================================================+
4 | copyright (c) 1993 oracle corporation |
5 | redwood shores, california, usa |
6 | all rights reserved. |
7 +===========================================================================*/
8 --
9 --
10 /*
11 Name
12 Element Links Table Handler
13 Purpose
14 To act as an interface between forms and the Element Links entity
15 History
16 16-MAR-1994 N Simpson Created
17 29-MAR-1994 N Simpson Amended element_in_distribution_set to
18 restrict cursor by business group/
19 legislation code
20 22-JUN-1994 N Simpson Fixes to G908 unit test bugs
21 40.5 04-OCT-1994 R Fine Renamed called package from
22 assignment_link_usages_pkg to
23 pay_asg_link_usages_pkg
24 25-Oct-1994 N Simpson Fixed G1355 by adding check to
25 check_deletion_allowed to prevent
26 deletion if balance adjustment entries
27 exist outside the life of the link
28 40.7 24-NOV-1994 R Fine Suppressed index on business_group_id
29 40.9 22-MAR-1995 N Simpson Modified delete_row to cascade
30 deletion of children differently
31 according to the datetrack mode.
32 See comments within that
33 procedure.
34 40.10 22-MAR-1995 N Simpson Removed trace_on/off calls
35 40.11 05-MAR-1997 J Alloun Changed all occurances of system.dual
36 to sys.dual for next release requirements.
37 40.12 ??
38 40.13 01-JUN-1997 M Lisiecki Bug 481143. Changed message
39 PAY_6465_LINK_NO_COST_UPD1 to more
40 generic PAY_52151_ENTRIES_EXIST.
41 40.14 02-JUN-1997 M Lisiecki Changed 52151 to 52153 as 51 already
42 existed.
43 110.3 10-FEB-1999 M Reid 809540: Added segment19 to link test
44 as it was missing.
45 115.2 27-APR-1999 S Billing 874781,
46 pay_element_links_pkg.update_row(),
47 if updating an element link row with
48 non-criteria information
49 (ie. Qualifying Conditions), then the
50 EED of the updated record or the
51 newly created record should not exceed
52 the EED of the original element link row
53 update
54 115.4 10-NOV-2000 RThirlby Bug 1490304 Updated procedure
55 check_deletion_allowed, so that cursor
56 csr_balance_adjustments only gets
57 called if p_delete_mode in DELETE or
58 ZAP.
59 115.5 27-APR-2001 DSaxby Fix for 1755379. Removed the erroneous
60 close of the csr_balance_adjustments
61 cursor in the check_deletion_allowed
62 procedure.
63 115.6 15-NOV-2002 ALogue Performance fix for csr_entries in
64 CHECK_DELETION_ALLOWED. Bug 2667222.
65 115.7 03-DEC-2002 ALogue dbdrv lines.
66 115.8 20-MAY-2005 SuSivasu Only update the last date tracked record
67 with the end of time for the case of
68 DELETE_NEXT_CHANGE DT mode.
69 115.9 20-MAY-2005 SuSivasu Fixed NOCOPY and GSCC issues.
70 115.10 26-SEP-2006 THabara Batch Element Link support. Bug 5512101.
71 Modified cascade_deletion, update_row,
72 check_deletion_allowed and
73 last_exclusive_date.
74 115.11 14-SEP-2006 THabara Added function pay_basis_exists.
75 Added pay basis check to insert_row.
76 115.12 06-FEB-2008 salogana Commented the pay_basis_exists
77 check as the customer doesnt require
78 this validation ( BUG NO : 6764215 ).
79 */
80 --------------------------------------------------------------------------------
81 -- Declare global package variables and constants.
82 --
83 c_end_of_time constant date := hr_general.end_of_time;
84 g_dummy number(1);
85 --------------------------------------------------------------------------------
86 procedure CASCADE_INSERTION (
87 --
88 --******************************************************************************
89 --* This procedure inserts link input values when an element link is
90 --* created. It will also insert assignment link usages.
91 --******************************************************************************
92 --
93 -- Parameters:
94 --
95 p_element_link_id number,
96 p_element_type_id number,
97 p_effective_start_date date,
98 p_effective_end_date date,
99 p_people_group_id number,
100 p_costable_type varchar2,
101 p_business_group_id number ) is
102 --
103 begin
104 --
105 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_INSERTION', 1);
106 --
107 pay_link_input_values_pkg.create_link_input_value(
108 --
109 p_element_link_id,
110 p_costable_type,
111 p_effective_start_date,
112 p_effective_end_date,
113 p_element_type_id);
114 --
115 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_INSERTION', 2);
116 --
117 pay_asg_link_usages_pkg.insert_alu(
118 --
119 p_business_group_id,
120 p_people_group_id,
121 p_element_link_id,
122 p_effective_start_date,
123 p_effective_end_date);
124 --
125 end cascade_insertion;
126 --------------------------------------------------------------------------------
127 procedure CASCADE_DELETION(
128 --
129 --******************************************************************************
130 --* Cascades the deletion of an element link to its child entities.
131 --******************************************************************************
132 --
133 -- Parameters:
134 --
135 p_element_link_id number,
136 p_business_group_id number,
137 p_people_group_id number,
138 p_delete_mode varchar2,
139 p_effective_start_date date,
140 p_effective_end_date date,
141 p_session_date date,
142 p_validation_start_date date,
143 p_validation_end_date date) is
144 --
145 cursor csr_links_entries is
146 select element_entry_id
147 from pay_element_entries_f
148 where element_link_id = p_element_link_id
149 and p_session_date between effective_start_date
150 and effective_end_date;
151 --
152 cursor csr_all_inputs_for_link is
153 select rowid, pay_link_input_values_f.*
154 from pay_link_input_values_f
155 where element_link_id = p_element_link_id
156 for update;
157 --
158 begin
159 --
160 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_DELETION', 1);
161 --
162 for fetched_entry in csr_links_entries LOOP
163 --
164 hr_entry_api.delete_element_entry (
165 --
166 p_delete_mode,
167 p_session_date,
168 fetched_entry.element_entry_id);
169 --
170 end loop;
171 --
172 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_DELETION', 2);
173 --
174 pay_asg_link_usages_pkg.cascade_link_deletion (
175 --
176 p_element_link_id,
177 p_business_group_id,
178 p_people_group_id,
179 p_delete_mode,
180 p_effective_start_date,
181 p_effective_end_date,
182 p_validation_start_date,
183 p_validation_end_date);
184 --
185 -- Bug 5512101. Batch Element Link support.
186 -- Delete the object status record if the delete mode is ZAP.
187 --
188 if p_delete_mode = 'ZAP' then
189 pay_batch_object_status_pkg.delete_object_status
190 (p_object_type => 'EL'
191 ,p_object_id => p_element_link_id
192 ,p_payroll_action_id => null
193 );
194 end if;
195 --
196 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_DELETION', 3);
197 --
198 <<REMOVE_ORPHANED_INPUT_VALUES>>
199 --
200 FOR fetched_input_value in csr_all_inputs_for_link LOOP
201 --
202 -- Delete input value if in ZAP mode
203 -- Delete input value if it is in the future and in DELETE mode
204 --
205 if p_delete_mode = 'ZAP'
206 or (p_delete_mode = 'DELETE'
207 and fetched_input_value.effective_start_date > p_session_date ) then
208 --
209 delete from pay_link_input_values_f
210 where current of csr_all_inputs_for_link;
211 --
212 -- For date effective deletes, shut down the input value by ensuring its end
213 -- date matches that of its closed parent
214 --
215 elsif p_delete_mode = 'DELETE'
216 and p_session_date between fetched_input_value.effective_start_date
217 and fetched_input_value.effective_end_date then
218 --
219 update pay_link_input_values_f
220 set effective_end_date = p_session_date
221 where current of csr_all_inputs_for_link;
222 --
223 -- For delete next changes when there are no future rows for the link,
224 -- extend the input value's end date to the end of time to match the action
225 -- which will be performed on the parent
226 --
227 elsif p_delete_mode = 'DELETE_NEXT_CHANGE'
228 and p_validation_end_date = hr_general.end_of_time then
229 --
230 update pay_link_input_values_f
231 set effective_end_date = c_end_of_time
232 where --current of csr_all_inputs_for_link
233 rowid = fetched_input_value.rowid
234 and not exists
235 (select null
236 from pay_link_input_values_f pliv
237 where pliv.element_link_id = fetched_input_value.element_link_id
238 and pliv.input_value_id = fetched_input_value.input_value_id
239 and pliv.effective_start_date > fetched_input_value.effective_start_date);
240 --
241 end if;
242 --
243 end loop remove_orphaned_input_values;
244 --
245 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CASCADE_DELETION', 4);
246 --
247 end cascade_deletion;
248 --------------------------------------------------------------------------------
249 function LINK_END_DATE (p_link_id number) return date is
250 --
251 --******************************************************************************
252 --* Returns the end date of the Link.
253 --******************************************************************************
254 v_link_end_date date;
255 --
256 cursor csr_link is
257 select max(effective_end_date)
258 from pay_element_links_f
259 where element_link_id = p_link_id;
260 --
261 begin
262 open csr_link;
263 fetch csr_link into v_link_end_date;
264 close csr_link;
265 return v_link_end_date;
266 end link_end_date;
267 --------------------------------------------------------------------------------
268 procedure insert_row(p_rowid in out nocopy varchar2,
269 p_element_link_id in out nocopy number,
270 p_effective_start_date date,
271 p_effective_end_date in out nocopy date,
272 p_payroll_id number,
273 p_job_id number,
274 p_position_id number,
275 p_people_group_id number,
276 p_cost_allocation_keyflex_id number,
277 p_organization_id number,
278 p_element_type_id number,
279 p_location_id number,
280 p_grade_id number,
281 p_balancing_keyflex_id number,
282 p_business_group_id number,
283 p_legislation_code varchar2,
284 p_element_set_id number,
285 p_pay_basis_id number,
286 p_costable_type varchar2,
287 p_link_to_all_payrolls_flag varchar2,
288 p_multiply_value_flag varchar2,
289 p_standard_link_flag varchar2,
290 p_transfer_to_gl_flag varchar2,
291 p_comment_id number,
292 p_employment_category varchar2,
293 p_qualifying_age number,
294 p_qualifying_length_of_service number,
295 p_qualifying_units varchar2,
296 p_attribute_category varchar2,
297 p_attribute1 varchar2,
298 p_attribute2 varchar2,
299 p_attribute3 varchar2,
300 p_attribute4 varchar2,
301 p_attribute5 varchar2,
302 p_attribute6 varchar2,
303 p_attribute7 varchar2,
304 p_attribute8 varchar2,
305 p_attribute9 varchar2,
306 p_attribute10 varchar2,
307 p_attribute11 varchar2,
308 p_attribute12 varchar2,
309 p_attribute13 varchar2,
310 p_attribute14 varchar2,
311 p_attribute15 varchar2,
312 p_attribute16 varchar2,
313 p_attribute17 varchar2,
314 p_attribute18 varchar2,
315 p_attribute19 varchar2,
316 p_attribute20 varchar2) is
317 cursor csr_new_rowid is
318 select rowid
319 from pay_element_links_f
320 where element_link_id = p_element_link_id
321 and effective_start_date = p_effective_start_date
322 and effective_end_date = p_effective_end_date;
323 --
324 cursor csr_next_ID is
325 select pay_element_links_s.nextval
326 from sys.dual;
327 --
328 begin
329 --
330 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.INSERT_ROW',1);
331 --
332 if p_element_link_id is null then
333 open csr_next_ID;
334 fetch csr_next_ID into p_element_link_id;
335 close csr_next_ID;
339 p_element_type_id,
336 end if;
337 --
338 if p_costable_type = 'D' and pay_element_links_pkg.element_in_distribution_set (
340 p_business_group_id,
341 p_legislation_code) then
342
343 hr_utility.set_message(801,'PAY_6462_LINK_DIST_IN_DIST');
344 hr_utility.raise_error;
345
346 end if;
347 --
348
349 /* commented the below block for bug no : 6764215
350
351 if p_standard_link_flag = 'Y' and
352 pay_basis_exists(p_element_type_id, p_business_group_id) then
353
354 hr_utility.set_message(801,'PAY_33093_LINK_NO_PAY_BASIS');
355 hr_utility.raise_error;
356 end if;
357
358 */
359 --
360 insert into pay_element_links_f(
361 --
362 element_link_id,
363 effective_start_date,
364 effective_end_date,
365 payroll_id,
366 job_id,
367 position_id,
368 people_group_id,
369 cost_allocation_keyflex_id,
370 organization_id,
371 element_type_id,
372 location_id,
373 grade_id,
374 balancing_keyflex_id,
375 business_group_id,
376 element_set_id,
377 pay_basis_id,
378 costable_type,
379 link_to_all_payrolls_flag,
380 multiply_value_flag,
381 standard_link_flag,
382 transfer_to_gl_flag,
383 comment_id,
384 employment_category,
385 qualifying_age,
386 qualifying_length_of_service,
387 qualifying_units,
388 attribute_category,
389 attribute1,
390 attribute2,
391 attribute3,
392 attribute4,
393 attribute5,
394 attribute6,
395 attribute7,
396 attribute8,
397 attribute9,
398 attribute10,
399 attribute11,
400 attribute12,
401 attribute13,
402 attribute14,
403 attribute15,
404 attribute16,
405 attribute17,
406 attribute18,
407 attribute19,
408 attribute20)
409 values (
410 p_element_link_id,
411 p_effective_start_date,
412 p_effective_end_date,
413 p_payroll_id,
414 p_job_id,
415 p_position_id,
416 p_people_group_id,
417 p_cost_allocation_keyflex_id,
418 p_organization_id,
419 p_element_type_id,
420 p_location_id,
421 p_grade_id,
422 p_balancing_keyflex_id,
423 p_business_group_id,
424 p_element_set_id,
425 p_pay_basis_id,
426 p_costable_type,
427 p_link_to_all_payrolls_flag,
428 p_multiply_value_flag,
429 p_standard_link_flag,
430 p_transfer_to_gl_flag,
431 p_comment_id,
432 p_employment_category,
433 p_qualifying_age,
434 p_qualifying_length_of_service,
435 p_qualifying_units,
436 p_attribute_category,
437 p_attribute1,
438 p_attribute2,
439 p_attribute3,
440 p_attribute4,
441 p_attribute5,
442 p_attribute6,
443 p_attribute7,
444 p_attribute8,
445 p_attribute9,
446 p_attribute10,
447 p_attribute11,
448 p_attribute12,
449 p_attribute13,
450 p_attribute14,
451 p_attribute15,
452 p_attribute16,
453 p_attribute17,
454 p_attribute18,
455 p_attribute19,
456 p_attribute20);
457 --
458 open csr_new_rowid;
459 fetch csr_new_rowid into p_rowid;
460 if (csr_new_rowid%notfound) then
461 close csr_new_rowid;
462 raise no_data_found;
463 end if;
464 close csr_new_rowid;
465 --
466 cascade_insertion (
467 p_element_link_id,
468 p_element_type_id,
469 p_effective_start_date,
470 p_effective_end_date,
471 p_people_group_id,
472 p_costable_type,
473 p_business_group_id);
474 --
475 end insert_row;
476 -------------------------------------------------------------------------------
477 procedure lock_row(p_rowid varchar2,
478 p_element_link_id number,
479 p_effective_start_date date,
480 p_effective_end_date date,
481 p_payroll_id number,
482 p_job_id number,
483 p_position_id number,
484 p_people_group_id number,
485 p_cost_allocation_keyflex_id number,
486 p_organization_id number,
487 p_element_type_id number,
488 p_location_id number,
489 p_grade_id number,
493 p_pay_basis_id number,
490 p_balancing_keyflex_id number,
491 p_business_group_id number,
492 p_element_set_id number,
494 p_costable_type varchar2,
495 p_link_to_all_payrolls_flag varchar2,
496 p_multiply_value_flag varchar2,
497 p_standard_link_flag varchar2,
498 p_transfer_to_gl_flag varchar2,
499 p_comment_id number,
500 p_employment_category varchar2,
501 p_qualifying_age number,
502 p_qualifying_length_of_service number,
503 p_qualifying_units varchar2,
504 p_attribute_category varchar2,
505 p_attribute1 varchar2,
506 p_attribute2 varchar2,
507 p_attribute3 varchar2,
508 p_attribute4 varchar2,
509 p_attribute5 varchar2,
510 p_attribute6 varchar2,
511 p_attribute7 varchar2,
512 p_attribute8 varchar2,
513 p_attribute9 varchar2,
514 p_attribute10 varchar2,
515 p_attribute11 varchar2,
516 p_attribute12 varchar2,
517 p_attribute13 varchar2,
518 p_attribute14 varchar2,
519 p_attribute15 varchar2,
520 p_attribute16 varchar2,
521 p_attribute17 varchar2,
522 p_attribute18 varchar2,
523 p_attribute19 varchar2,
524 p_attribute20 varchar2) is
525 --
526 cursor csr_locked_row is
527 select *
528 from pay_element_links_f
529 where rowid = p_rowid
530 for update of element_link_id NOWAIT;
531 --
532 locked_row csr_locked_row%rowtype;
533 --
534 begin
535 --
536 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.LOCK_ROW',1);
537 --
538 open csr_locked_row;
539 fetch csr_locked_row into locked_row;
540 if csr_locked_row%notfound then
541 close csr_locked_row;
542 raise no_data_found;
543 end if;
544 close csr_locked_row;
545 --
546 if (((locked_row.element_link_id = p_element_link_id)
547 or ((locked_row.element_link_id is null)
548 and (p_element_link_id is null)))
549 and ((locked_row.effective_start_date = p_effective_start_date)
550 or ((locked_row.effective_start_date is null)
551 and (p_effective_start_date is null)))
552 and ((locked_row.effective_end_date = p_effective_end_date)
553 or ((locked_row.effective_end_date is null)
554 and (p_effective_end_date is null)))
555 and ((locked_row.payroll_id = p_payroll_id)
556 or ((locked_row.payroll_id is null)
557 and (p_payroll_id is null)))
558 and ((locked_row.job_id = p_job_id)
559 or ((locked_row.job_id is null)
560 and (p_job_id is null)))
561 and ((locked_row.position_id = p_position_id)
562 or ((locked_row.position_id is null)
563 and (p_position_id is null)))
567 and ((locked_row.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id)
564 and ((locked_row.people_group_id = p_people_group_id)
565 or ((locked_row.people_group_id is null)
566 and (p_people_group_id is null)))
568 or ((locked_row.cost_allocation_keyflex_id is null)
569 and (p_cost_allocation_keyflex_id is null)))
570 and ( (locked_row.organization_id = p_organization_id)
571 or ((locked_row.organization_id is null)
572 and (p_organization_id is null)))
573 and ( (locked_row.element_type_id = p_element_type_id)
574 or ((locked_row.element_type_id is null)
575 and (p_element_type_id is null)))
576 and ( (locked_row.location_id = p_location_id)
577 or ((locked_row.location_id is null)
578 and (p_location_id is null)))
579 and ( (locked_row.grade_id = p_grade_id)
580 or ((locked_row.grade_id is null)
581 and (p_grade_id is null)))
582 and ( (locked_row.balancing_keyflex_id = p_balancing_keyflex_id)
583 or ((locked_row.balancing_keyflex_id is null)
584 and (p_balancing_keyflex_id is null)))
585 and ( (locked_row.business_group_id = p_business_group_id)
586 or ((locked_row.business_group_id is null)
587 and (p_business_group_id is null)))
588 and ( (locked_row.element_set_id = p_element_set_id)
589 or ( (locked_row.element_set_id is null)
590 and (p_element_set_id is null)))
591 and ( (locked_row.pay_basis_id = p_pay_basis_id)
592 or ( (locked_row.pay_basis_id is null)
593 and (p_pay_basis_id is null)))
594 and ( (locked_row.costable_type = p_costable_type)
595 or ( (locked_row.costable_type is null)
596 and (p_costable_type is null)))
597 and ( (locked_row.link_to_all_payrolls_flag = p_link_to_all_payrolls_flag)
598 or ( (locked_row.link_to_all_payrolls_flag is null)
599 and (p_link_to_all_payrolls_flag is null)))
600 and ( (locked_row.multiply_value_flag = p_multiply_value_flag)
601 or ( (locked_row.multiply_value_flag is null)
602 and (p_multiply_value_flag is null)))
603 and ( (locked_row.standard_link_flag = p_standard_link_flag)
604 or ( (locked_row.standard_link_flag is null)
605 and (p_standard_link_flag is null)))
606 and ( (locked_row.transfer_to_gl_flag = p_transfer_to_gl_flag)
607 or ( (locked_row.transfer_to_gl_flag is null)
608 and (p_transfer_to_gl_flag is null)))
609 and ( (locked_row.comment_id = p_comment_id)
610 or ( (locked_row.comment_id is null)
611 and (p_comment_id is null)))
612 and ( (locked_row.employment_category = p_employment_category)
613 or ( (locked_row.employment_category is null)
614 and (p_employment_category is null)))
615 and ( (locked_row.qualifying_age = p_qualifying_age)
616 or ( (locked_row.qualifying_age is null)
617 and (p_qualifying_age is null)))
618 and ((locked_row.qualifying_length_of_service=p_qualifying_length_of_service)
619 or ( (locked_row.qualifying_length_of_service is null)
620 and (p_qualifying_length_of_service is null)))
621 and ( (locked_row.qualifying_units = p_qualifying_units)
622 or ( (locked_row.qualifying_units is null)
623 and (p_qualifying_units is null)))
624 and ( (locked_row.attribute_category = p_attribute_category)
625 or ( (locked_row.attribute_category is null)
626 and (p_attribute_category is null)))
627 and ( (locked_row.attribute1 = p_attribute1)
628 or ( (locked_row.attribute1 is null)
629 and (p_attribute1 is null)))
630 and ( (locked_row.attribute2 = p_attribute2)
631 or ( (locked_row.attribute2 is null)
632 and (p_attribute2 is null)))
633 and ( (locked_row.attribute3 = p_attribute3)
634 or ( (locked_row.attribute3 is null)
635 and (p_attribute3 is null)))
636 and ( (locked_row.attribute4 = p_attribute4)
637 or ( (locked_row.attribute4 is null)
638 and (p_attribute4 is null)))
639 and ( (locked_row.attribute5 = p_attribute5)
640 or ( (locked_row.attribute5 is null)
641 and (p_attribute5 is null)))
642 and ( (locked_row.attribute6 = p_attribute6)
643 or ( (locked_row.attribute6 is null)
644 and (p_attribute6 is null)))
645 and ( (locked_row.attribute7 = p_attribute7)
646 or ( (locked_row.attribute7 is null)
647 and (p_attribute7 is null)))
648 and ( (locked_row.attribute8 = p_attribute8)
649 or ( (locked_row.attribute8 is null)
650 and (p_attribute8 is null)))
651 and ( (locked_row.attribute9 = p_attribute9)
652 or ( (locked_row.attribute9 is null)
653 and (p_attribute9 is null)))
654 and ( (locked_row.attribute10 = p_attribute10)
655 or ( (locked_row.attribute10 is null)
656 and (p_attribute10 is null)))
657 and ( (locked_row.attribute11 = p_attribute11)
658 or ( (locked_row.attribute11 is null)
659 and (p_attribute11 is null)))
660 and ( (locked_row.attribute12 = p_attribute12)
661 or ( (locked_row.attribute12 is null)
665 and (p_attribute13 is null)))
662 and (p_attribute12 is null)))
663 and ( (locked_row.attribute13 = p_attribute13)
664 or ( (locked_row.attribute13 is null)
666 and ( (locked_row.attribute14 = p_attribute14)
667 or ( (locked_row.attribute14 is null)
668 and (p_attribute14 is null)))
669 and ( (locked_row.attribute15 = p_attribute15)
670 or ( (locked_row.attribute15 is null)
671 and (p_attribute15 is null)))
672 and ( (locked_row.attribute16 = p_attribute16)
673 or ( (locked_row.attribute16 is null)
674 and (p_attribute16 is null)))
675 and ( (locked_row.attribute17 = p_attribute17)
676 or ( (locked_row.attribute17 is null)
677 and (p_attribute17 is null)))
678 and ( (locked_row.attribute18 = p_attribute18)
679 or ( (locked_row.attribute18 is null)
680 and (p_attribute18 is null)))
681 and ( (locked_row.attribute19 = p_attribute19)
682 or ( (locked_row.attribute19 is null)
683 and (p_attribute19 is null)))
684 and ( (locked_row.attribute20 = p_attribute20)
685 or ( (locked_row.attribute20 is null)
686 and (p_attribute20 is null)))
687 ) then
688 return;
689 else
690 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
691 app_exception.raise_exception;
692 end if;
693 end lock_row;
694 --------------------------------------------------------------------------------
695 procedure update_row(p_rowid varchar2,
696 p_element_link_id number,
697 p_effective_start_date date,
698 p_effective_end_date in out nocopy date,
699 p_payroll_id number,
700 p_job_id number,
701 p_position_id number,
702 p_people_group_id number,
703 p_cost_allocation_keyflex_id number,
704 p_organization_id number,
705 p_element_type_id number,
706 p_location_id number,
707 p_grade_id number,
708 p_balancing_keyflex_id number,
709 p_business_group_id number,
710 p_legislation_code varchar2,
711 p_element_set_id number,
712 p_pay_basis_id number,
713 p_costable_type varchar2,
714 p_link_to_all_payrolls_flag varchar2,
715 p_multiply_value_flag varchar2,
716 p_standard_link_flag varchar2,
717 p_transfer_to_gl_flag varchar2,
718 p_comment_id number,
719 p_employment_category varchar2,
720 p_qualifying_age number,
721 p_qualifying_length_of_service number,
722 p_qualifying_units varchar2,
723 p_attribute_category varchar2,
724 p_attribute1 varchar2,
725 p_attribute2 varchar2,
726 p_attribute3 varchar2,
727 p_attribute4 varchar2,
728 p_attribute5 varchar2,
729 p_attribute6 varchar2,
730 p_attribute7 varchar2,
731 p_attribute8 varchar2,
732 p_attribute9 varchar2,
733 p_attribute10 varchar2,
734 p_attribute11 varchar2,
735 p_attribute12 varchar2,
736 p_attribute13 varchar2,
737 p_attribute14 varchar2,
738 p_attribute15 varchar2,
739 p_attribute16 varchar2,
740 p_attribute17 varchar2,
741 p_attribute18 varchar2,
742 p_attribute19 varchar2,
743 p_attribute20 varchar2) is
744 --
745 begin
746 --
747 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.UPDATE_ROW',1);
748 --
749 /*
750 p_effective_end_date := pay_element_links_pkg.max_end_date (
751 p_element_type_id,
752 p_element_link_id,
753 p_effective_start_date,
754 p_effective_end_date,
755 p_organization_id,
756 p_people_group_id,
757 p_job_id,
758 p_position_id,
759 p_grade_id,
760 p_location_id,
761 p_link_to_all_payrolls_flag,
762 p_payroll_id,
766 */
763 p_employment_category,
764 p_pay_basis_id,
765 p_business_group_id);
767 --
768 -- Bug 5512101. Batch Element Link support.
769 -- We need to lock the batch object status and ensure that the element
770 -- link is complete.
771 --
772 pay_batch_object_status_pkg.chk_complete_status
773 (p_object_type => 'EL'
774 ,p_object_id => p_element_link_id
775 );
776
777 --
778 -- sbilling 874781,
779 -- pay_element_links_pkg.update_row(),
780 -- if updating an element link row with non-criteria information
781 -- (ie. Qualifying Conditions), then the EED
782 -- of the updated record or the newly created record should not exceed
783 -- the EED of the original element link row
784 -- update
785 --
786 p_effective_end_date :=
787 least(
788 p_effective_end_date,
789 pay_element_links_pkg.max_end_date (
790 p_element_type_id,
791 p_element_link_id,
792 p_effective_start_date,
793 p_effective_end_date,
794 p_organization_id,
795 p_people_group_id,
796 p_job_id,
797 p_position_id,
798 p_grade_id,
799 p_location_id,
800 p_link_to_all_payrolls_flag,
801 p_payroll_id,
802 p_employment_category,
803 p_pay_basis_id,
804 p_business_group_id)
805 );
806 hr_utility.trace ('|p_effective_end_date>' || p_effective_end_date || '<');
807
808
809 if p_costable_type = 'D'
810 and pay_element_links_pkg.element_in_distribution_set (
811 p_element_type_id,
812 p_business_group_id,
813 p_legislation_code) then
814
815 hr_utility.set_message(801,'PAY_6462_LINK_DIST_IN_DIST');
816 hr_utility.raise_error;
817
818 end if;
819
820 update pay_element_links_f
821 set element_link_id = p_element_link_id,
822 effective_start_date = p_effective_start_date,
823 effective_end_date = p_effective_end_date,
824 payroll_id = p_payroll_id,
825 job_id = p_job_id,
826 position_id = p_position_id,
827 people_group_id = p_people_group_id,
828 cost_allocation_keyflex_id = p_cost_allocation_keyflex_id,
829 organization_id = p_organization_id,
830 element_type_id = p_element_type_id,
831 location_id = p_location_id,
832 grade_id = p_grade_id,
833 balancing_keyflex_id = p_balancing_keyflex_id,
834 business_group_id = p_business_group_id,
835 element_set_id = p_element_set_id,
836 pay_basis_id = p_pay_basis_id,
837 costable_type = p_costable_type,
838 link_to_all_payrolls_flag = p_link_to_all_payrolls_flag,
839 multiply_value_flag = p_multiply_value_flag,
840 standard_link_flag = p_standard_link_flag,
841 transfer_to_gl_flag = p_transfer_to_gl_flag,
842 comment_id = p_comment_id,
843 employment_category = p_employment_category,
844 qualifying_age = p_qualifying_age,
845 qualifying_length_of_service = p_qualifying_length_of_service,
846 qualifying_units = p_qualifying_units,
847 attribute_category = p_attribute_category,
848 attribute1 = p_attribute1,
849 attribute2 = p_attribute2,
850 attribute3 = p_attribute3,
851 attribute4 = p_attribute4,
852 attribute5 = p_attribute5,
853 attribute6 = p_attribute6,
854 attribute7 = p_attribute7,
855 attribute8 = p_attribute8,
856 attribute9 = p_attribute9,
857 attribute10 = p_attribute10,
858 attribute11 = p_attribute11,
859 attribute12 = p_attribute12,
860 attribute13 = p_attribute13,
861 attribute14 = p_attribute14,
862 attribute15 = p_attribute15,
863 attribute16 = p_attribute16,
864 attribute17 = p_attribute17,
865 attribute18 = p_attribute18,
866 attribute19 = p_attribute19,
867 attribute20 = p_attribute20
868 where rowid = p_rowid;
869 --
870 if sql%notfound then
871 raise no_data_found;
872 end if;
873 --
874 end update_row;
878 p_rowid varchar2,
875 --------------------------------------------------------------------------------
876 procedure delete_row(
877 --
879 p_element_link_id number,
880 p_delete_mode varchar2,
881 p_session_date date,
882 p_validation_start_date date,
883 p_validation_end_date date,
884 p_effective_start_date date,
885 p_business_group_id number,
886 p_people_group_id number) is
887 --
888 v_effective_end_date date;
889 --
890 begin
891 --
892 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.DELETE_ROW',1);
893 --
894 check_deletion_allowed ( p_element_link_id,
895 p_delete_mode,
896 p_validation_start_date);
897 --
898 v_effective_end_date := pay_element_links_pkg.link_end_date (p_element_link_id);
899 --
900 -- Note that the cascade delete of any action other than PURGE/ZAP will
901 -- be done AFTER the deletion of the master record. This is due to a
902 -- quirk of datetrack in forms which means that up to the point of
903 -- deletion of a link, when not zapping, there will be TWO rows with the
904 -- same ID and overlapping in date-effective time. If any of the child
905 -- entities looks back up at the link table, it will return duplicate
906 -- rows and may fail with unhelpful system errors. By doing the cascade
907 -- after the deletion of the master, we ensure that the duplicate row
908 -- has been removed prior to the child deletions.
909 --
910 if p_delete_mode = 'ZAP' then
911 --
912 cascade_deletion (
913 p_element_link_id ,
914 p_business_group_id,
915 p_people_group_id,
916 p_delete_mode ,
917 p_effective_start_date,
918 v_effective_end_date,
919 p_session_date,
920 p_validation_start_date ,
921 p_validation_end_date );
922 --
923 end if;
924 --
925 delete from pay_element_links_f
926 where rowid = p_rowid;
927 --
928 if sql%notfound then
929 raise no_data_found;
930 end if;
931 --
932 if p_delete_mode <> 'ZAP' then
933 --
934 cascade_deletion (
935 p_element_link_id ,
936 p_business_group_id,
937 p_people_group_id,
938 p_delete_mode ,
939 p_effective_start_date,
940 v_effective_end_date,
941 p_session_date,
942 p_validation_start_date ,
943 p_validation_end_date );
944 --
945 end if;
946 --
947 end delete_row;
948 --------------------------------------------------------------------------------
949 function LAST_EXCLUSIVE_DATE (
950 --
951 --******************************************************************************
952 --* Returns the last date on which the link will be mutually exclusive with *
953 --* other links for the same element type, or an error if no such date can be *
954 --* achieved. *
955 --******************************************************************************
956 --
957 -- Parameters are:
958 --
959 p_element_type_id number,
960 p_element_link_id number,
961 p_validation_start_date date,
962 p_validation_end_date date,
963 p_organization_id number,
964 p_people_group_id number,
965 p_job_id number,
966 p_position_id number,
967 p_grade_id number,
968 p_location_id number,
969 p_payroll_id number,
970 p_employment_category varchar2,
971 p_pay_basis_id number,
972 p_business_group_id number) return date is
973 --
974 cursor csr_other_links_for_element is
975 -- Set of links for the same element as the link being tested
976 select link.effective_start_date,
977 link.effective_end_date,
978 link.organization_id,
979 link.people_group_id,
980 link.job_id,
981 link.position_id,
982 link.grade_id,
983 link.location_id,
984 link.link_to_all_payrolls_flag PAYROLL_FLAG,
985 link.payroll_id,
986 link.employment_category,
987 link.pay_basis_id,
988 people_group.segment1,
989 people_group.segment2,
990 people_group.segment3,
991 people_group.segment4,
992 people_group.segment5,
993 people_group.segment6,
994 people_group.segment7,
995 people_group.segment8,
996 people_group.segment9,
997 people_group.segment10,
998 people_group.segment11,
999 people_group.segment12,
1000 people_group.segment13,
1001 people_group.segment14,
1002 people_group.segment15,
1003 people_group.segment16,
1004 people_group.segment17,
1005 people_group.segment18,
1006 people_group.segment19,
1007 people_group.segment20,
1011 people_group.segment24,
1008 people_group.segment21,
1009 people_group.segment22,
1010 people_group.segment23,
1012 people_group.segment25,
1013 people_group.segment26,
1014 people_group.segment27,
1015 people_group.segment28,
1016 people_group.segment29,
1017 people_group.segment30
1018 from pay_element_links_f LINK,
1019 pay_people_groups PEOPLE_GROUP
1020 where link.people_group_id = people_group.people_group_id(+)
1021 and link.element_type_id = p_element_type_id
1022 and link.element_link_id <> nvl(p_element_link_id,0)
1023 and link.business_group_id + 0 = p_business_group_id
1024 and link.effective_end_date >= p_validation_start_date
1025 --
1026 -- Batch element link support.
1027 --
1028 UNION ALL
1029 select bel.effective_date effective_start_date,
1030 hr_general.end_of_time effective_end_date,
1031 bel.organization_id,
1032 bel.people_group_id,
1033 bel.job_id,
1034 bel.position_id,
1035 bel.grade_id,
1036 bel.location_id,
1037 bel.link_to_all_payrolls_flag PAYROLL_FLAG,
1038 bel.payroll_id,
1039 bel.employment_category,
1040 bel.pay_basis_id,
1041 people_group.segment1,
1042 people_group.segment2,
1043 people_group.segment3,
1044 people_group.segment4,
1045 people_group.segment5,
1046 people_group.segment6,
1047 people_group.segment7,
1048 people_group.segment8,
1049 people_group.segment9,
1050 people_group.segment10,
1051 people_group.segment11,
1052 people_group.segment12,
1053 people_group.segment13,
1054 people_group.segment14,
1055 people_group.segment15,
1056 people_group.segment16,
1057 people_group.segment17,
1058 people_group.segment18,
1059 people_group.segment19,
1060 people_group.segment20,
1061 people_group.segment21,
1062 people_group.segment22,
1063 people_group.segment23,
1064 people_group.segment24,
1065 people_group.segment25,
1066 people_group.segment26,
1067 people_group.segment27,
1068 people_group.segment28,
1069 people_group.segment29,
1070 people_group.segment30
1071 from pay_batch_element_links BEL,
1072 pay_people_groups PEOPLE_GROUP
1073 where bel.people_group_id = people_group.people_group_id(+)
1074 and bel.element_type_id = p_element_type_id
1075 and bel.batch_element_link_id <> nvl(p_element_link_id,0)
1076 and bel.element_link_id is null
1077 and bel.business_group_id + 0 = p_business_group_id
1078 -- exclude the batch link that is currently processing.
1079 and nvl(pay_batch_object_status_pkg.get_status
1080 ('BEL',bel.batch_element_link_id),'U') <> 'P'
1081 order by effective_start_date;
1082 --
1083 cursor csr_my_people_group is
1084 -- Segments of people group for the link being tested
1085 select *
1086 from pay_people_groups
1087 where people_group_id = p_people_group_id;
1088 --
1089 new_link csr_my_people_group%rowtype;
1090 v_last_exclusive_date date;
1091 --
1092 function link_differs_on (p_existing_row varchar2,
1093 p_new_row varchar2) return boolean is
1094 -- return TRUE if the two parameters differ and neither one is null
1095 begin
1096 return (p_existing_row <> p_new_row
1097 and p_existing_row is not null
1098 and p_new_row is not null);
1099 end link_differs_on;
1100
1101 BEGIN
1102
1103 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.LAST_EXCLUSIVE_DATE', 1);
1104
1105 open csr_my_people_group;
1106 fetch csr_my_people_group into new_link;
1107
1108 -- Check for matching criteria on other links for this element.
1109 -- The link criteria being tested are made up of the parameters and the
1110 -- new_link record. If any of the link's criteria are matched in
1111 -- another link for the same element, then the link being tested must be
1112 -- given an end date just prior to the start date of the matching link. If this
1113 -- cannot be done, then an error will occur. A match is PAY_ELEMENT_LINKS_PKGd as either
1114 -- equality between the criteria items, or where one of the items is
1115 -- unspecified.
1116 -- NB It is more efficient to check for a non-match because a single
1117 -- non-matching item will satisfy the exclusivity for the whole record, whilst
1118 -- finding a match need not negate the exclusivity.
1119
1120 <<LINK_TEST>>
1121 for existing_link in csr_other_links_for_element LOOP
1122
1126 or (link_differs_on (existing_link.grade_id, p_grade_id))
1123 if NOT( (link_differs_on (existing_link.organization_id, p_organization_id))
1124 or (link_differs_on (existing_link.job_id, p_job_id ))
1125 or (link_differs_on (existing_link.position_id, p_position_id))
1127 or (link_differs_on (existing_link.location_id, p_location_id))
1128 or (link_differs_on (existing_link.payroll_id, p_payroll_id))
1129 or (link_differs_on (existing_link.employment_category, p_employment_category))
1130 or (link_differs_on (existing_link.pay_basis_id, p_pay_basis_id))
1131 or (link_differs_on (existing_link.segment1, new_link.segment1))
1132 or (link_differs_on (existing_link.segment2, new_link.segment2))
1133 or (link_differs_on (existing_link.segment3, new_link.segment3))
1134 or (link_differs_on (existing_link.segment4, new_link.segment4))
1135 or (link_differs_on (existing_link.segment5, new_link.segment5))
1136 or (link_differs_on (existing_link.segment6, new_link.segment6))
1137 or (link_differs_on (existing_link.segment7, new_link.segment7))
1138 or (link_differs_on (existing_link.segment8, new_link.segment8))
1139 or (link_differs_on (existing_link.segment9, new_link.segment9))
1140 or (link_differs_on (existing_link.segment10, new_link.segment10))
1141 or (link_differs_on (existing_link.segment11, new_link.segment11))
1142 or (link_differs_on (existing_link.segment12, new_link.segment12))
1143 or (link_differs_on (existing_link.segment13, new_link.segment13))
1144 or (link_differs_on (existing_link.segment14, new_link.segment14))
1145 or (link_differs_on (existing_link.segment15, new_link.segment15))
1146 or (link_differs_on (existing_link.segment16, new_link.segment16))
1147 or (link_differs_on (existing_link.segment17, new_link.segment17))
1148 or (link_differs_on (existing_link.segment18, new_link.segment18))
1149 or (link_differs_on (existing_link.segment19, new_link.segment19))
1150 or (link_differs_on (existing_link.segment20, new_link.segment20))
1151 or (link_differs_on (existing_link.segment21, new_link.segment21))
1152 or (link_differs_on (existing_link.segment22, new_link.segment22))
1153 or (link_differs_on (existing_link.segment23, new_link.segment23))
1154 or (link_differs_on (existing_link.segment24, new_link.segment24))
1155 or (link_differs_on (existing_link.segment25, new_link.segment25))
1156 or (link_differs_on (existing_link.segment26, new_link.segment26))
1157 or (link_differs_on (existing_link.segment27, new_link.segment27))
1158 or (link_differs_on (existing_link.segment28, new_link.segment28))
1159 or (link_differs_on (existing_link.segment29, new_link.segment29))
1160 or (link_differs_on (existing_link.segment30, new_link.segment30)))
1161 then
1162 --
1163 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.last_exclusive_date',2);
1164 --
1165 -- Set the end date to avoid clash; raise an error if that is not possible
1166 if (p_validation_start_date < existing_link.effective_start_date) then
1167 --
1168 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.last_exclusive_date',3);
1169 v_last_exclusive_date := existing_link.effective_start_date - 1;
1170 exit LINK_TEST; -- The cursor ordering ensures that the date is that
1171 -- of the chronologically first matching link
1172 --
1173 else
1174 --
1175 hr_utility.set_message(801,'PAY_6398_ELEMENT_NOT_MUT_EXCLU');
1176 hr_utility.raise_error;
1177 --
1178 end if;
1179 --
1180 end if;
1181 --
1182 end loop;
1183 close csr_my_people_group;
1184 --
1185 if v_last_exclusive_date is null then -- no matching criteria were found
1186 v_last_exclusive_date := c_end_of_time;
1187 end if;
1188 --
1189 hr_utility.trace ('v_last_exclusive_date is '||v_last_exclusive_date);
1190 --
1191 return v_last_exclusive_date;
1192 --
1193 end last_exclusive_date;
1194 --------------------------------------------------------------------------------
1195 function MAX_END_DATE(
1196 --
1197 --******************************************************************************
1198 --* Returns the latest allowable date for the effective end date of a link. *
1199 --* This is the latest of: *
1200 --* 1. The element type's last effective end date. *
1201 --* 2. The latest end date for a payroll associated with the link. *
1202 --* 3. The day before the start date of another link which would *
1203 --* have matching criteria for the same element.
1204 --******************************************************************************
1205 --
1206 -- Parameters are:
1207 --
1208 p_element_type_id number,
1209 p_element_link_id number,
1210 p_validation_start_date date,
1211 p_validation_end_date date,
1212 p_organization_id number,
1213 p_people_group_id number,
1214 p_job_id number,
1215 p_position_id number,
1216 p_grade_id number,
1217 p_location_id number,
1218 p_link_to_all_payrolls_flag varchar2,
1219 p_payroll_id number,
1220 p_employment_category varchar2,
1221 p_pay_basis_id number,
1222 p_business_group_id number) return date is
1223 --
1227 cursor csr_named_element is
1224 v_max_payroll_date date;
1225 v_max_element_date date;
1226 --
1228 -- Maximum end date for the named element type
1229 select max(effective_end_date)
1230 from pay_element_types_f
1231 where element_type_id = p_element_type_id;
1232 --
1233 cursor csr_business_group_payrolls is
1234 -- Maximum end date for any payroll in the business group
1235 select max(effective_end_date)
1236 from pay_payrolls_f
1237 where business_group_id + 0 = p_business_group_id;
1238 --
1239 cursor csr_named_payroll is
1240 -- Maximum end date for the payroll identified by the payroll ID
1241 select max(effective_end_date)
1242 from pay_payrolls_f
1243 where payroll_id = p_payroll_id;
1244 begin
1245 --
1246 hr_utility.set_location ('PAY_ELEMENT_LINKS_PKG.MAX_END_DATE',1);
1247 hr_utility.trace ('p_validation_start_date = '||p_validation_start_date);
1248 hr_utility.trace ('p_validation_end_date = '||p_validation_end_date);
1249 --
1250 -- If there is a named payroll, then take its last end date; if the link is to
1251 -- all payrolls, then take the latest end date from all payrolls in the
1252 -- business group.
1253 if p_link_to_all_payrolls_flag = 'Y' then
1254 open csr_business_group_payrolls;
1255 fetch csr_business_group_payrolls into v_max_payroll_date;
1256 close csr_business_group_payrolls;
1257 --
1258 elsif p_payroll_id is not null then
1259 open csr_named_payroll;
1260 fetch csr_named_payroll into v_max_payroll_date;
1261 close csr_named_payroll;
1262 --
1263 end if;
1264 --
1265 v_max_payroll_date := nvl(v_max_payroll_date, c_end_of_time);
1266 --
1267 open csr_named_element;
1268 fetch csr_named_element into v_max_element_date;
1269 close csr_named_element;
1270 --
1271 v_max_element_date := nvl(v_max_element_date, c_end_of_time);
1272 --
1273 hr_utility.trace ('v_max_element_date is '||v_max_element_date);
1274 hr_utility.trace ('v_max_payroll_date is '||v_max_payroll_date);
1275
1276 return least ( v_max_payroll_date,
1277 v_max_element_date,
1278 last_exclusive_date (p_element_type_id,
1279 p_element_link_id,
1280 p_validation_start_date,
1281 p_validation_end_date,
1282 p_organization_id,
1283 p_people_group_id,
1284 p_job_id,
1285 p_position_id,
1286 p_grade_id,
1287 p_location_id,
1288 p_payroll_id,
1289 p_employment_category,
1290 p_pay_basis_id,
1291 p_business_group_id ));
1292 --
1293 end max_end_date;
1294 --------------------------------------------------------------------------------
1295 function ELEMENT_IN_DISTRIBUTION_SET (
1296 --******************************************************************************
1297 --* Returns TRUE if the element is in a distribution set
1298 --******************************************************************************
1299
1300 p_element_type_id number,
1301 p_business_group_id number,
1302 p_legislation_code varchar2) return boolean is
1303
1304 cursor csr_distribution_set is
1305 --
1306 -- Returns a row if the element is in a distribution set
1307 -- NB Because pay_element_set_members is a complex view it cannot
1308 -- restrict on business group id and so I must use this as part of the
1309 -- join to it.
1310 --
1311 select 1
1312 from pay_element_set_members MEMBER,
1313 pay_element_sets ELEMENT_SET
1314 where element_set.element_set_id = member.element_set_id
1315 and member.element_type_id = p_element_type_id
1316 and element_set.element_set_type = 'D'
1317 and (element_set.business_group_id + 0
1318 = member.business_group_id + 0
1319 and element_set.business_group_id + 0 = p_business_group_id
1320 or (p_business_group_id is null
1321 and element_set.legislation_code =
1322 member.legislation_code
1323 and element_set.legislation_code = p_legislation_code));
1324
1325 element_in_set boolean;
1326
1327 begin
1328
1329 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CHECK_DISTRIBUTION_SET', 1);
1330
1331 open csr_distribution_set;
1332 fetch csr_distribution_set into g_dummy;
1333 element_in_set := csr_distribution_set%found;
1334 close csr_distribution_set;
1335
1336 return element_in_set;
1337
1338 end element_in_distribution_set;
1339 --------------------------------------------------------------------------------
1340 function ELEMENT_ENTRIES_EXIST (
1341 --
1342 --******************************************************************************
1343 --* Returns TRUE if element entries already exist for this link *
1347 --
1344 --******************************************************************************
1345 --
1346 -- Parameters are:
1348 p_element_link_id number,
1349 p_error_if_true boolean := FALSE) return boolean is
1350 --
1351 v_entries_exist boolean := FALSE;
1352 --
1353 cursor csr_entries is
1354 select 1
1355 from pay_element_entries_f
1356 where element_link_id = p_element_link_id;
1357 begin
1358 --
1359 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.ELEMENT_ENTRIES_EXIST', 1);
1360 --
1361 open csr_entries;
1362 fetch csr_entries into g_dummy;
1363 if csr_entries%found and p_error_if_true then
1364 -- Bug 481143. Changed message 6465 to 52153.01-Jul-1997.mlisieck.
1365 hr_utility.set_message(801,'PAY_52153_ENTRIES_EXIST');
1366 hr_utility.raise_error;
1367 end if;
1368 v_entries_exist := csr_entries%found;
1369 close csr_entries;
1370 --
1371 return v_entries_exist;
1372 --
1373 end element_entries_exist;
1374 --------------------------------------------------------------------------------
1375 function DATE_EFFECTIVELY_UPDATED (
1376 --
1377 --******************************************************************************
1378 --* Returns TRUE if there exists more than one row with the same link ID *
1379 --******************************************************************************
1380 --
1381 -- Parameters are:
1382 --
1383 p_element_link_id number,
1384 p_rowid varchar2) return boolean is
1385 --
1386 v_updates_exist boolean := FALSE;
1387 --
1388 cursor csr_updates is
1389 select 1
1390 from pay_element_links_f
1391 where element_link_id = p_element_link_id
1392 and rowid <> p_rowid;
1393 --
1394 begin
1395 --
1396 hr_utility.set_location ('PAY_ELEMENT_TYPES_PKG.DATE_EFFECTIVELY_UPDATED',1);
1397 --
1398 open csr_updates;
1399 fetch csr_updates into g_dummy;
1400 v_updates_exist := csr_updates%found;
1401 close csr_updates;
1402 --
1403 return v_updates_exist;
1404 --
1405 end date_effectively_updated;
1406 --------------------------------------------------------------------------------
1407 procedure CHECK_DELETION_ALLOWED (
1408 --
1409 --******************************************************************************
1410 --* Checks to see if link may be deleted. *
1411 --******************************************************************************
1412 --
1413 -- Parameters:
1414 --
1415 p_element_link_id number,
1416 p_delete_mode varchar2,
1417 p_validation_start_date date ) is
1418 --
1419 -- Are there entries whose earliest start date is after the deletion date?
1420 cursor csr_entries is
1421 select 1
1422 from pay_element_entries_f ENTRY1
1423 where entry1.element_link_id = p_element_link_id
1424 and not(entry1.effective_start_date < p_validation_start_date);
1425 --
1426 -- Bug 1490304 - removed p_delete_mode from where clause.
1427 --
1428 cursor csr_balance_adjustments is
1429 select 1
1430 from pay_element_entries_f BALANCE_ENTRY
1431 where balance_entry.element_link_id = p_element_link_id
1432 and balance_entry.effective_start_date > p_validation_start_date
1433 -- and p_delete_mode in ('DELETE','ZAP')
1434 and balance_entry.entry_type = 'B';
1435 --
1436 begin
1437 --
1438 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CHECK_DELETION_ALLOWED', 10);
1439 --
1440 -- Bug 5512101. Changes for Batch Element Link support.
1441 -- We need to ensure that the element link is complete
1442 -- unless the delete mode is ZAP.
1443 --
1444 if p_delete_mode <> 'ZAP' then
1445 pay_batch_object_status_pkg.chk_complete_status
1446 (p_object_type => 'EL'
1447 ,p_object_id => p_element_link_id
1448 );
1449 end if;
1450
1451
1452 hr_utility.set_location('PAY_ELEMENT_LINKS_PKG.CHECK_DELETION_ALLOWED', 20);
1453 --
1454 -- Bug 1490304 - if p_delete_mode in DELETE or ZAP then open both cursors
1455 -- else only open csr_entries.
1456 --
1457 IF p_delete_mode in ('DELETE','ZAP') THEN
1458 open csr_entries;
1459 open csr_balance_adjustments;
1460 fetch csr_entries into g_dummy;
1461 fetch csr_balance_adjustments into g_dummy;
1462 if csr_entries%found or csr_balance_adjustments%found then
1463 hr_utility.set_message(801,'HR_7134_LINK_NO_DEL_ENTRIES');
1464 hr_utility.raise_error;
1465 end if;
1466 close csr_entries;
1467 close csr_balance_adjustments;
1468 ELSE
1469 open csr_entries;
1470 fetch csr_entries into g_dummy;
1471 if csr_entries%found then
1472 hr_utility.set_message(801,'HR_7134_LINK_NO_DEL_ENTRIES');
1473 hr_utility.raise_error;
1474 end if;
1475 close csr_entries;
1476 END IF;
1477 end check_deletion_allowed;
1478 --------------------------------------------------------------------------------
1479 procedure CHECK_RELATIONSHIPS (
1480 --
1481 --******************************************************************************
1482 --* Returns values used by forms to set item properties. The calls within this *
1483 --* procedure could be used separately, but bundling them here reduces network *
1484 --* traffic. *
1485 --******************************************************************************
1486 --
1487 -- Parameters are:
1488 --
1489 p_element_link_id number,
1490 p_rowid varchar2,
1491 p_date_effectively_updated out nocopy boolean,
1492 p_element_entries_exist out nocopy boolean ) is
1493 --
1494 begin
1495 --
1496 hr_utility.set_location ('pay_element_links_pkg.check_relationships',1);
1497 --
1498 p_date_effectively_updated := date_effectively_updated (p_element_link_id,
1499 p_rowid );
1500 --
1501 p_element_entries_exist := element_entries_exist ( p_element_link_id,
1502 FALSE );
1503 --
1504 end check_relationships;
1505 --------------------------------------------------------------------------------
1506 function PAY_BASIS_EXISTS (
1507 --
1508 --******************************************************************************
1509 --* Returns TRUE if a pay basis exists for the element type. *
1510 --******************************************************************************
1511 --
1512 -- Parameters are:
1513 --
1514 p_element_type_id number
1515 ,p_business_group_id number) return boolean
1516 is
1517 l_pay_basis_exists number;
1518 l_exists boolean;
1519 --
1520 cursor csr_pay_basis_exists is
1521 select 1
1522 from
1523 pay_input_values_f piv
1524 ,per_pay_bases ppb
1525 where
1526 piv.element_type_id = p_element_type_id
1527 and ppb.input_value_id = piv.input_value_id
1528 and ppb.business_group_id = p_business_group_id
1529 ;
1530 begin
1531
1532 open csr_pay_basis_exists;
1533 fetch csr_pay_basis_exists into l_pay_basis_exists;
1534 if csr_pay_basis_exists%found then
1535 l_exists := true;
1536 else
1537 l_exists := false;
1538 end if;
1539 close csr_pay_basis_exists;
1540
1541 return l_exists;
1542
1543 end pay_basis_exists;
1544 --------------------------------------------------------------------------------
1545 end PAY_ELEMENT_LINKS_PKG;
[Home] [Help]