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