1 package hr_entry AUTHID CURRENT_USER as
2 /* $Header: pyeentry.pkh 120.2 2010/09/20 14:31:22 asnell ship $ */
3 --
4 -- Global TYPE definitions
5 type number_table is table of number not null
6 index by binary_integer;
7 /*Changed the size from 80-240 for bug 9350651*/
8 type varchar2_table is table of varchar2(240)
9 index by binary_integer;
10 --
11 -- ----------------- Assignment_eligible_for_link -----------------------------
12 --
13 -- NAME
14 -- hr_entry.Assignment_eligible_for_link
15 --
16 -- DESCRIPTION
17 -- Returns 'Y' if the specified assignment and link match as at the
18 -- specified date. A match indicates that the assignment is eligible for
19 -- the link as at that date. This function may be called from within SQL.
20 -- If no match is found then 'N' will be returned (it never returns NULL).
21 --
22 function Assignment_eligible_for_link (
23 --
24 p_assignment_id in natural,
25 p_element_link_id in natural,
26 p_effective_date in date
27 ) return varchar2;
28 --
29 --pragma restrict_references (Assignment_eligible_for_link, WNDS, WNPS);
30 -- ---------------------------------------------------------------------------
31 --
32 -- NAME
33 -- hr_entry.return_termination_date
34 --
35 -- DESCRIPTION
36 -- Returns the actual_termination_date if an assignment has been
37 -- terminated.
38 -- If the assignment has not been terminated then the returned
39 -- actual_termination_date date will be null.
40 --
41 function return_termination_date(p_assignment_id in number,
42 p_session_date in date)
43 return date;
44 --
45 -- NAME
46 -- hr_entry.get_nonrecurring_dates
47 --
48 -- DESCRIPTION
49 -- Called when a nonrecurring entry is about to be created. Makes sure that
50 -- the assignment is to a payroll and also a time period exists. Returns the
51 -- start and end dates of the nonrecurring entry taking into account
52 -- changes in payroll.
53 --
54 procedure get_nonrecurring_dates
55 (
56 p_assignment_id in number,
57 p_session_date in date,
58 p_effective_start_date out nocopy date,
59 p_effective_end_date out nocopy date,
60 p_payroll_id out nocopy number,
61 p_period_start_date out nocopy date,
62 p_period_end_date out nocopy date
63 );
64 --
65 --
66 -- --------------------- return_qualifying_conditions -------------------------
67 --
68 -- Name: return_qualifying_conditions
69 --
70 -- Description: If the element entry link is discretionary and has
71 -- qualifying conditions then check the length of
72 -- service and age conditions.
73 --
74 -- Returns: p_los_date --> date at which the los is eligible.
75 -- p_age_date --> date at which the age is eligible.
76 --
77 -- If dates return null then check is not valid.
78 --
79 procedure return_qualifying_conditions
80 (
81 p_assignment_id in number,
82 p_element_link_id in number,
83 p_session_date in date,
84 p_los_date out nocopy date,
85 p_age_date out nocopy date
86 );
87 --
88 /*
89 NAME
90 hr_entry.generate_entry_id
91
92 DESCRIPTION
93 Generates then next sequence value for inserting an element entry into the
94 PAY_ELEMENT_ENTRIES_F base table.
95 */
96 --
97 FUNCTION generate_entry_id return number;
98 --
99 /*
100 NAME
101 hr_entry.generate_run_result_id
102
103 DESCRIPTION
104 Generates then next sequence value for inserting a run result into
105 PAY_RUN_RESULTS base table.
106 */
107 --
108 FUNCTION generate_run_result_id return number;
109 --
110 /*
111 NAME
112 hr_entry.entry_process_in_run
113
114 DESCRIPTION
115 This function return a boolean value for the specified element_type_id
116 depending on the process_in_run_flag attribute. The function returns TRUE
117 if the process_in_run_flag = 'Y' or FALSE if the process_in_run_flag = 'N'
118 */
119 --
120 FUNCTION entry_process_in_run(p_element_type_id in number,
121 p_session_date in date) return boolean;
122 --
123 /*
124 NAME
125 hr_entry.get_eligibility_period
126
127 DESCRIPTION
128 This procedure selects the minimum or maximum (or both) effective assignment
129 dates where the assignment is eligible for a given element link.
130 */
131 PROCEDURE get_eligibility_period (
132 p_assignment_id in number,
133 p_element_link_id in number,
134 p_session_date in date,
135 p_min_eligibility_date in out nocopy date,
136 p_max_eligibility_date in out nocopy date
137 );
138 --
139 /*
140 NAME
141 hr_entry.entry_asg_pay_link_dates
142
143 DESCRIPTION
144 This procedure returns the min(effective_start/end_date) for a specified
145 element link and payroll. Also, if the specified employee assignment has
146 been terminated the element termination date as of the termination rule
147 is returned.
148 */
149 PROCEDURE entry_asg_pay_link_dates (p_assignment_id in number,
150 p_element_link_id in number,
151 p_session_date in date,
152 p_element_term_rule_date out nocopy date,
153 p_element_link_start_date out nocopy date,
154 p_element_link_end_date out nocopy date,
155 p_payroll_start_date out nocopy date,
156 p_payroll_end_date out nocopy date,
157 p_entry_mode in boolean default true);
158 --
159 /*
160 NAME
161 hr_entry.recurring_entry_end_date
162
163 DESCRIPTION
164 This function is used to return the valid effective end of a recurring entry.
165 */
166 function recurring_entry_end_date
167 (
168 p_assignment_id in number,
169 p_element_link_id in number,
170 p_session_date in date,
171 p_overlap_chk in varchar2 default 'Y',
172 p_mult_entries_allowed_flag in varchar2,
173 p_element_entry_id in number,
174 p_original_entry_id in number
175 ) return date;
176 --
177 /*
178 NAME
179 hr_entry.chk_element_entry_eligbility
180
181 DESCRIPTION
182 This procedure is used to check if entries (which are defined below) are
183 eligble to be inserted/deleted.
184 This procedure is only called when:
185 1) Inserting an NONRECURRING element entry
186 (which is defined as: Nonrecurring, Additional, Override, Adjustment,
187 Balance Adjustment etc).
188 e.g. when (p_usage = 'INSERT' and
189 ((p_processing_type = 'R' and
190 p_entry_type != 'E') or
191 p_processing_type = 'N'))
192
193 2) DateTrack deleting (Next/Future Changes) of a RECURRING element entry.
194 e.g. (p_dt_delete_mode = 'DELETE_NEXT_CHANGE' or
195 p_dt_delete_mode = 'FUTURE_CHANGES')
196 */
197 PROCEDURE chk_element_entry_eligbility (p_assignment_id in number,
198 p_element_link_id in number,
199 p_session_date in date,
200 p_usage in varchar2,
201 p_validation_start_date in date,
202 p_validation_end_date in date,
203 p_min_eligibility_date out nocopy date,
204 p_max_eligibility_date out nocopy date);
205 /*
206 NAME
207 hr_entry.chk_element_entry_open
208
209 DESCRIPTION
210 This procedure does the following checks:
211 1) Ensure that the element type is not closed for entry currently
212 or in the future by determining the value of the
213 CLOSED_FOR_ENTRY_FLAG attribute on PAY_ELEMENT_TYPES_F.
214 2) If the employee assignment is to a payroll then ensure that
215 the current and future periods as of session date are open.
216 If the period is closed, you can only change entries providing
217 */
218 PROCEDURE chk_element_entry_open (p_element_type_id in number,
219 p_session_date in date,
220 p_validation_start_date in date,
221 p_validation_end_date in date,
222 p_assignment_id in number);
223 --
224 /*
225 NAME
226 hr_entry.derive_default_value
227
228 DESCRIPTION
229 This procedure is used to return default screen and database formatted
230 values in either a cold or hot format for the specified link and
231 input value. The default value can be for Minimum, Maximum or Default
232 values.
233 Therefore, it hot defaults are being used the returned database value
234 will be null but, the return screen value will be encapsulated in
235 */
236 PROCEDURE derive_default_value (p_element_link_id in number,
237 p_input_value_id in number,
238 p_session_date in date,
239 p_input_currency_code in varchar2,
240 p_min_max_def in varchar2
241 default 'DEF',
242 v_screen_format_value out nocopy varchar2,
243 v_database_format_value out nocopy varchar2);
244 --
245 /*
246 NAME
247 hr_entry.chk_mandatory_input_value
248
249 DESCRIPTION
250 This procedure produces an error is any input value which is defined as
251 having a mandatory value is null.
252 */
253 PROCEDURE chk_mandatory_input_value (p_input_value_id in number,
254 p_entry_value in varchar2,
255 p_session_date in date,
256 p_element_link_id in number);
257 --
258 /*
259 NAME
260 hr_entry.chk_element_entry
261
262 DESCRIPTION
263 This procedure is a cover to chk_element_entry_main and simply calls
264 chk_element_entry_main with a null p_creator_type.
265 This change has been made because overloading can't be used because
266 chk_element_entry is called from forms.
267 */
268 --
269 procedure chk_element_entry
270 (
271 p_element_entry_id in number,
272 p_original_entry_id in number,
273 p_session_date in date,
274 p_element_link_id in number,
275 p_assignment_id in number,
276 p_entry_type in varchar2,
277 p_effective_start_date in out nocopy date,
278 p_effective_end_date in out nocopy date,
279 p_validation_start_date in date,
280 p_validation_end_date in date,
281 p_dt_update_mode in varchar2,
282 p_dt_delete_mode in varchar2,
283 p_usage in varchar2,
284 p_target_entry_id in number
285 );
286 --
287 /*
288 NAME
289 hr_entry.chk_element_entry_main
290
291 DESCRIPTION
292 This procedure is used for referential/standard checks when inserting/
293 updating or deleteing element enries.
294 */
295 --
296 procedure chk_element_entry_main
297 (
298 p_element_entry_id in number,
299 p_original_entry_id in number,
300 p_session_date in date,
301 p_element_link_id in number,
302 p_assignment_id in number,
303 p_entry_type in varchar2,
304 p_effective_start_date in out nocopy date,
305 p_effective_end_date in out nocopy date,
306 p_validation_start_date in date,
307 p_validation_end_date in date,
308 p_dt_update_mode in varchar2,
309 p_dt_delete_mode in varchar2,
310 p_usage in varchar2,
311 p_target_entry_id in number,
312 p_creator_type in varchar2
313 );
314 --
315 /*
316 NAME
317 hr_entry.del_3p_entry_values
318
319 DESCRIPTION
320 This procedure is used for third party deletes from:
321 PAY_ELEMENT_ENTRIES_F (If an abscence etc).
322 PAY_ELEMENT_ENTRY_VALUES_F (Entry Values are always deleted).
323 PAY_RUN_RESULTS (If nonrecurring, and exist).
324 PAY_RUN_RESULT_VALUES (If nonrecurring, and exist).
325 */
326 --
327 PROCEDURE del_3p_entry_values (p_assignment_id in number,
328 p_element_entry_id in number,
329 p_element_type_id in number,
330 p_element_link_id in number,
331 p_entry_type in varchar2,
332 p_processing_type in varchar2,
333 p_creator_type in varchar2,
334 p_creator_id in varchar2,
335 p_dt_delete_mode in varchar2,
336 p_session_date in date,
337 p_validation_start_date in date,
338 p_validation_end_date in date);
339 --
340 --
341 /*
342 NAME
343 hr_entry.trigger_workload_shifting
344
345 DESCRIPTION
346 This procedure is used for triggering workload shifting.
347 */
348 --
349 PROCEDURE trigger_workload_shifting(p_mode varchar2,
350 p_assignment_id number,
351 p_effective_start_date date,
352 p_effective_end_date date);
353 --
354 -- NAME
355 -- hr_entry.check_format
356 --
357 -- DESCRIPTION
358 -- Makes sure that the entry value is correct for the UOM and also convert the
359 -- screen value into the database value ie. internal format.
360 --
361 procedure check_format
362 (
363 p_element_link_id in number,
364 p_input_value_id in number,
365 p_session_date in date,
366 p_formatted_value in out nocopy varchar2,
367 p_database_value in out nocopy varchar2,
368 p_nullok in varchar2 default 'Y',
369 p_min_max_failure in out nocopy varchar2,
370 p_warning_or_error out nocopy varchar2,
371 p_minimum_value out nocopy varchar2,
372 p_maximum_value out nocopy varchar2
373 );
374 --
378 -- DESCRIPTION
375 -- NAME
376 -- hr_entry.maintain_cost_keyflex
377 --
379 --
380 function maintain_cost_keyflex(
381 p_cost_keyflex_structure in number,
382 p_cost_allocation_keyflex_id in number,
383 p_concatenated_segments in varchar2,
384 p_summary_flag in varchar2,
385 p_start_date_active in date,
386 p_end_date_active in date,
387 p_segment1 in varchar2,
388 p_segment2 in varchar2,
389 p_segment3 in varchar2,
390 p_segment4 in varchar2,
391 p_segment5 in varchar2,
392 p_segment6 in varchar2,
393 p_segment7 in varchar2,
394 p_segment8 in varchar2,
395 p_segment9 in varchar2,
396 p_segment10 in varchar2,
397 p_segment11 in varchar2,
398 p_segment12 in varchar2,
399 p_segment13 in varchar2,
400 p_segment14 in varchar2,
401 p_segment15 in varchar2,
402 p_segment16 in varchar2,
403 p_segment17 in varchar2,
404 p_segment18 in varchar2,
405 p_segment19 in varchar2,
406 p_segment20 in varchar2,
407 p_segment21 in varchar2,
408 p_segment22 in varchar2,
409 p_segment23 in varchar2,
410 p_segment24 in varchar2,
411 p_segment25 in varchar2,
412 p_segment26 in varchar2,
413 p_segment27 in varchar2,
414 p_segment28 in varchar2,
415 p_segment29 in varchar2,
416 p_segment30 in varchar2)
417 return number;
418 --
419 -- NAME
420 -- hr_entry.return_entry_display_status
421 --
422 -- DESCRIPTION
423 -- Used by PAYEEMEE/PAYWSMEE to return current entry statuses during a
424 -- post-query.
425 --
426 procedure return_entry_display_status(p_element_entry_id in number,
427 p_element_type_id in number,
428 p_element_link_id in number,
429 p_assignment_id in number,
430 p_entry_type in varchar2,
431 p_session_date in date,
432 p_additional out nocopy varchar2,
433 p_adjustment out nocopy varchar2,
434 p_overridden out nocopy varchar2,
435 p_processed out nocopy varchar2);
436 --
437 -- NAME
438 -- hr_entry.Ins_3p_entry_values
439 --
440 -- DESCRIPTION
441 -- This function is used for third party inserts into:
442 -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
443 -- PAY_ELEMENT_ENTRY_VALUES_F (Entry Values are always inserted).
444 -- PAY_RUN_RESULTS (If nonrecurring).
445 -- PAY_RUN_RESULT_VBALUES (If nonrecurring).
446 --
447 -- NB. this function is OVERLOADED !
448 --
449 procedure ins_3p_entry_values
450 (
451 p_element_link_id number,
452 p_element_entry_id number,
453 p_session_date date,
454 p_num_entry_values number,
455 p_input_value_id_tbl hr_entry.number_table,
456 p_entry_value_tbl hr_entry.varchar2_table
457 );
458 --
459 --
460 -- NAME
461 -- hr_entry.ins_3p_entry_values
462 --
463 -- DESCRIPTION
464 -- This function is used for third party inserts into:
465 -- PAY_ELEMENT_ENTRIES_F (If an abscence, or DT functions are being used).
466 -- PAY_ELEMENT_ENTRY_VALUES_F (Entry Values are always inserted).
467 -- PAY_RUN_RESULTS (If nonrecurring).
468 -- PAY_RUN_RESULT_VBALUES (If nonrecurring).
469 --
470 -- NB. this function is OVERLOADED !
471 --
472 procedure ins_3p_entry_values
473 (
474 p_element_link_id number,
475 p_element_entry_id number,
476 p_session_date date,
477 /** sbilling **/
478 p_creator_type varchar2,
479 p_entry_type varchar2,
480 p_input_value_id1 number,
481 p_input_value_id2 number,
482 p_input_value_id3 number,
483 p_input_value_id4 number,
484 p_input_value_id5 number,
485 p_input_value_id6 number,
486 p_input_value_id7 number,
487 p_input_value_id8 number,
488 p_input_value_id9 number,
489 p_input_value_id10 number,
490 p_input_value_id11 number,
491 p_input_value_id12 number,
492 p_input_value_id13 number,
493 p_input_value_id14 number,
494 p_input_value_id15 number,
495 p_entry_value1 varchar2,
496 p_entry_value2 varchar2,
497 p_entry_value3 varchar2,
498 p_entry_value4 varchar2,
499 p_entry_value5 varchar2,
500 p_entry_value6 varchar2,
501 p_entry_value7 varchar2,
502 p_entry_value8 varchar2,
506 p_entry_value12 varchar2,
503 p_entry_value9 varchar2,
504 p_entry_value10 varchar2,
505 p_entry_value11 varchar2,
507 p_entry_value13 varchar2,
508 p_entry_value14 varchar2,
509 p_entry_value15 varchar2
510 );
511 --
512 -- NAME
513 -- hr_entry.upd_3p_entry_values
514 --
515 -- DESCRIPTION
516 -- This procedure is used for third party updates into:
517 -- PAY_ELEMENT_ENTRY_VALUES_F
518 -- PAY_RUN_RESULTS (If nonrecurring).
519 -- PAY_RUN_RESULT_VALUES (If nonrecurring).
520 --
521 -- NB. this procedure is OVERLOADED !
522 --
523 procedure upd_3p_entry_values
524 (
525 p_element_entry_id number,
526 p_element_type_id number,
527 p_element_link_id number,
528 p_cost_allocation_keyflex_id number,
529 p_entry_type varchar2,
530 p_processing_type varchar2,
531 p_creator_type varchar2,
532 p_creator_id number,
533 p_assignment_id number,
534 p_input_currency_code varchar2,
535 p_output_currency_code varchar2,
536 p_validation_start_date date,
537 p_validation_end_date date,
538 p_session_date date,
539 p_dt_update_mode varchar2,
540 p_num_entry_values number,
541 p_input_value_id_tbl hr_entry.number_table,
542 p_entry_value_tbl hr_entry.varchar2_table
543 );
544 --
545 -- NAME
546 -- hr_entry.upd_3p_entry_values
547 --
548 -- DESCRIPTION
549 -- This procedure is used for third party updates into:
550 -- PAY_ELEMENT_ENTRY_VALUES_F
551 -- PAY_RUN_RESULTS (If nonrecurring).
552 -- PAY_RUN_RESULT_VALUES (If nonrecurring).
553 --
554 -- NB. this Procedure is OVERLOADED !
555 --
556 procedure upd_3p_entry_values
557 (
558 p_element_entry_id number,
559 p_element_type_id number,
560 p_element_link_id number,
561 p_cost_allocation_keyflex_id number,
562 p_entry_type varchar2,
563 p_processing_type varchar2,
564 p_creator_type varchar2,
565 p_creator_id number,
566 p_assignment_id number,
567 p_input_currency_code varchar2,
568 p_output_currency_code varchar2,
569 p_validation_start_date date,
570 p_validation_end_date date,
571 p_session_date date,
572 p_dt_update_mode varchar2,
573 p_input_value_id1 number,
574 p_input_value_id2 number,
575 p_input_value_id3 number,
576 p_input_value_id4 number,
577 p_input_value_id5 number,
578 p_input_value_id6 number,
579 p_input_value_id7 number,
580 p_input_value_id8 number,
581 p_input_value_id9 number,
582 p_input_value_id10 number,
583 p_input_value_id11 number,
584 p_input_value_id12 number,
585 p_input_value_id13 number,
586 p_input_value_id14 number,
587 p_input_value_id15 number,
588 p_entry_value1 varchar2,
589 p_entry_value2 varchar2,
590 p_entry_value3 varchar2,
591 p_entry_value4 varchar2,
592 p_entry_value5 varchar2,
593 p_entry_value6 varchar2,
594 p_entry_value7 varchar2,
595 p_entry_value8 varchar2,
596 p_entry_value9 varchar2,
597 p_entry_value10 varchar2,
598 p_entry_value11 varchar2,
599 p_entry_value12 varchar2,
600 p_entry_value13 varchar2,
601 p_entry_value14 varchar2,
602 p_entry_value15 varchar2
603 );
604 --
605 procedure chk_creator_type(p_element_entry_id in number,
606 p_creator_type in varchar2,
607 p_quickpay_mode in varchar2,
608 p_dml_operation in varchar2,
609 p_dt_update_mode in varchar2,
610 p_dt_delete_mode in varchar2,
611 p_validation_start_date in date,
612 p_validation_end_date in date);
613 --
614 --------------------------------------
615 --
616 -- NAME hr_entry.delete_covered_dependants
617 --
618 -- DESCRIPTION deals with calls to update BEN_COVERED_DPENDENTS for a given
619 -- element_entry
620 --
621 ---------------------------------------
622 procedure delete_covered_dependants(
623 p_validation_start_date in date,
624 p_element_entry_id in number,
625 p_start_date in date DEFAULT NULL,
626 p_end_date in date DEFAULT NULL);
627
628
629
630 ------------------------------------------
631 --
632 -- NAME hr_entry.delete_beneficiaries
633 --
634 -- DESCRIPTION deals with calls to update BEN_BENEFICIARIES for a given
635 -- element_entry
636 --
637 -------------------------------------------
638 procedure delete_beneficiaries(
639 p_validation_start_date in date,
640 p_element_entry_id in number,
641 p_start_date in date DEFAULT NULL,
642 p_end_date in date DEFAULT NULL);
643
644 -------------------------------------------------------------------------
645 -- NAME maintain_covered_dependants
646 --
647 -- DESCRIPTION Deals with calls to update the covered dependents of an
648 -- Element entry.
649 -- Called by :
650 -- : hrentmnt.maintain_dependent_entities
651 --
652 ----------------------------------------------------------------------------
653
654 procedure maintain_covered_dependants
655
656 (p_element_entry_id in number,
657 p_element_entry_ESD in date,
658 p_element_entry_EED in date,
659 p_new_element_entry_id in number,
660 p_new_element_entry_ESD in date,
661 p_new_element_entry_EED in date);
662
663 -------------------------------------------------------------------------
664 -- NAME maintain_beneficiaries
665 --
666 -- DESCRIPTION Deals with calls to update the beneficiaries of an
667 -- Element entry.
668 -- Called by :
669 -- : hrentmnt.maintain_dependent_entities
670 --
671 ----------------------------------------------------------------------------
672
673 procedure maintain_beneficiaries
674
675 (p_element_entry_id in number,
676 p_element_entry_ESD in date,
677 p_element_entry_EED in date,
678 p_new_element_entry_id in number,
679 p_new_element_entry_ESD in date,
680 p_new_element_entry_EED in date);
681 end hr_entry;