1 Package ben_pep_shd AUTHID CURRENT_USER as
2 /* $Header: bepeprhi.pkh 120.0 2005/05/28 10:40:10 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 elig_per_id number(15),
11 effective_start_date date,
12 effective_end_date date,
13 business_group_id number(15),
14 pl_id number(15),
15 pgm_id number(15),
16 plip_id number(15),
17 ptip_id number(15),
18 ler_id number(15),
19 person_id number(15),
20 per_in_ler_id number(15),
21 dpnt_othr_pl_cvrd_rl_flag varchar2(30),
22 prtn_ovridn_thru_dt date,
23 pl_key_ee_flag varchar2(30),
24 pl_hghly_compd_flag varchar2(30),
25 elig_flag varchar2(30),
26 comp_ref_amt number,
27 cmbn_age_n_los_val number,
28 comp_ref_uom varchar2(30),
29 age_val number,
30 los_val number,
31 prtn_end_dt date,
32 prtn_strt_dt date,
33 wait_perd_cmpltn_dt date,
34 wait_perd_strt_dt date,
35 wv_ctfn_typ_cd varchar2(30),
36 hrs_wkd_val number,
37 hrs_wkd_bndry_perd_cd varchar2(30),
38 prtn_ovridn_flag varchar2(30),
39 no_mx_prtn_ovrid_thru_flag varchar2(30),
40 prtn_ovridn_rsn_cd varchar2(30),
41 age_uom varchar2(30),
42 los_uom varchar2(30),
43 ovrid_svc_dt date,
44 inelg_rsn_cd varchar2(30),
45 frz_los_flag varchar2(30),
46 frz_age_flag varchar2(30),
47 frz_cmp_lvl_flag varchar2(30),
48 frz_pct_fl_tm_flag varchar2(30),
49 frz_hrs_wkd_flag varchar2(30),
50 frz_comb_age_and_los_flag varchar2(9), -- Increased length
51 dstr_rstcn_flag varchar2(30),
52 pct_fl_tm_val number,
53 wv_prtn_rsn_cd varchar2(30),
54 pl_wvd_flag varchar2(30),
55 rt_comp_ref_amt number,
56 rt_cmbn_age_n_los_val number,
57 rt_comp_ref_uom varchar2(30),
58 rt_age_val number,
59 rt_los_val number,
60 rt_hrs_wkd_val number,
61 rt_hrs_wkd_bndry_perd_cd varchar2(30),
62 rt_age_uom varchar2(30),
63 rt_los_uom varchar2(30),
64 rt_pct_fl_tm_val number,
65 rt_frz_los_flag varchar2(30),
66 rt_frz_age_flag varchar2(30),
67 rt_frz_cmp_lvl_flag varchar2(30),
68 rt_frz_pct_fl_tm_flag varchar2(30),
69 rt_frz_hrs_wkd_flag varchar2(30),
70 rt_frz_comb_age_and_los_flag varchar2(30),
71 once_r_cntug_cd varchar2(30),
72 pl_ordr_num number(15),
73 plip_ordr_num number(15),
74 ptip_ordr_num number(15),
75 pep_attribute_category varchar2(30),
76 pep_attribute1 varchar2(150),
77 pep_attribute2 varchar2(150),
78 pep_attribute3 varchar2(150),
79 pep_attribute4 varchar2(150),
80 pep_attribute5 varchar2(150),
81 pep_attribute6 varchar2(150),
82 pep_attribute7 varchar2(150),
83 pep_attribute8 varchar2(150),
84 pep_attribute9 varchar2(150),
85 pep_attribute10 varchar2(150),
86 pep_attribute11 varchar2(150),
87 pep_attribute12 varchar2(150),
88 pep_attribute13 varchar2(150),
89 pep_attribute14 varchar2(150),
90 pep_attribute15 varchar2(150),
91 pep_attribute16 varchar2(150),
92 pep_attribute17 varchar2(150),
93 pep_attribute18 varchar2(150),
94 pep_attribute19 varchar2(150),
95 pep_attribute20 varchar2(150),
96 pep_attribute21 varchar2(150),
97 pep_attribute22 varchar2(150),
98 pep_attribute23 varchar2(150),
99 pep_attribute24 varchar2(150),
100 pep_attribute25 varchar2(150),
101 pep_attribute26 varchar2(150),
102 pep_attribute27 varchar2(150),
103 pep_attribute28 varchar2(150),
104 pep_attribute29 varchar2(150),
105 pep_attribute30 varchar2(150),
106 request_id number(15),
107 program_application_id number(15),
108 program_id number(15),
109 program_update_date date,
110 object_version_number number(9)
111 );
112 --
113 -- ----------------------------------------------------------------------------
114 -- | Global Definitions - Internal Development Use Only |
115 -- ----------------------------------------------------------------------------
116 --
117 g_old_rec g_rec_type; -- Global record definition
118 g_api_dml boolean; -- Global api dml status
119 --
120 -- ----------------------------------------------------------------------------
121 -- |------------------------< return_api_dml_status >-------------------------|
122 -- ----------------------------------------------------------------------------
123 -- {Start Of Comments}
124 --
125 -- Description:
126 -- This function will return the current g_api_dml private global
127 -- boolean status.
128 -- The g_api_dml status determines if at the time of the function
129 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
130 -- is being issued from within an api.
131 -- If the status is TRUE then a dml statement is being issued from
132 -- within this entity api.
133 -- This function is primarily to support database triggers which
134 -- need to maintain the object_version_number for non-supported
135 -- dml statements (i.e. dml statement issued outside of the api layer).
136 --
137 -- Prerequisites:
138 -- None.
139 --
140 -- In Parameters:
141 -- None.
142 --
143 -- Post Success:
144 -- Processing continues.
145 -- If the function returns a TRUE value then, dml is being executed from
146 -- within this api.
147 --
148 -- Post Failure:
149 -- None.
150 --
151 -- Access Status:
152 -- Internal Row Handler Use Only.
153 --
154 -- {End Of Comments}
155 -- ----------------------------------------------------------------------------
156 Function return_api_dml_status Return Boolean;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |---------------------------< constraint_error >---------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- This procedure is called when a constraint has been violated (i.e.
165 -- The exception hr_api.check_integrity_violated,
166 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
167 -- hr_api.unique_integrity_violated has been raised).
168 -- The exceptions can only be raised as follows:
169 -- 1) A check constraint can only be violated during an INSERT or UPDATE
170 -- dml operation.
171 -- 2) A parent integrity constraint can only be violated during an
172 -- INSERT or UPDATE dml operation.
173 -- 3) A child integrity constraint can only be violated during an
174 -- DELETE dml operation.
175 -- 4) A unique integrity constraint can only be violated during INSERT or
176 -- UPDATE dml operation.
177 --
178 -- Prerequisites:
179 -- 1) Either hr_api.check_integrity_violated,
180 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
181 -- hr_api.unique_integrity_violated has been raised with the subsequent
182 -- stripping of the constraint name from the generated error message
183 -- text.
184 -- 2) Standalone validation test which corresponds with a constraint error.
185 --
186 -- In Parameter:
187 -- p_constraint_name is in upper format and is just the constraint name
188 -- (e.g. not prefixed by brackets, schema owner etc).
189 --
190 -- Post Success:
191 -- Development dependant.
192 --
193 -- Post Failure:
194 -- Developement dependant.
195 --
196 -- Developer Implementation Notes:
197 -- For each constraint being checked the hr system package failure message
198 -- has been generated as a template only. These system error messages should
199 -- be modified as required (i.e. change the system failure message to a user
200 -- friendly defined error message).
201 --
202 -- Access Status:
203 -- Internal Development Use Only.
204 --
205 -- {End Of Comments}
206 -- ----------------------------------------------------------------------------
207 Procedure constraint_error
208 (p_constraint_name in all_constraints.constraint_name%TYPE);
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-----------------------------< api_updating >-----------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This function is used to populate the g_old_rec record with the current
217 -- row from the database for the specified primary key provided that the
218 -- primary key exists, and is valid, and does not already match the current
219 -- g_old_rec.
220 -- The function will always return a TRUE value if the g_old_rec is
221 -- populated with the current row. A FALSE value will be returned if all of
222 -- the primary key arguments are null.
223 --
224 -- Prerequisites:
225 -- None.
226 --
227 -- In Parameters:
228 --
229 -- Post Success:
230 -- A value of TRUE will be returned indiciating that the g_old_rec is
231 -- current.
232 -- A value of FALSE will be returned if all of the primary key arguments
233 -- have a null value (this indicates that the row has not be inserted into
234 -- the Schema), and therefore could never have a corresponding row.
235 --
236 -- Post Failure:
237 -- A failure can only occur under two circumstances:
238 -- 1) The primary key is invalid (i.e. a row does not exist for the
239 -- specified primary key values).
240 -- 2) If an object_version_number exists but is NOT the same as the current
241 -- g_old_rec value.
242 --
243 -- Developer Implementation Notes:
244 -- None.
245 --
246 -- Access Status:
247 -- Internal Development Use Only.
248 --
249 -- {End Of Comments}
250 -- ----------------------------------------------------------------------------
251 Function api_updating
252 (p_effective_date in date,
253 p_elig_per_id in number,
254 p_object_version_number in number
255 ) Return Boolean;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |--------------------------< find_dt_del_modes >---------------------------|
259 -- ----------------------------------------------------------------------------
260 -- {Start Of Comments}
261 --
262 -- Description:
263 -- This procedure is used to determine what datetrack delete modes are
264 -- allowed as of the effective date for this entity. The procedure will
265 -- return a corresponding Boolean value for each of the delete modes
266 -- available where TRUE indicates that the corresponding delete mode is
267 -- available.
268 --
269 -- Prerequisites:
270 -- None.
271 --
272 -- In Parameters:
273 -- p_effective_date
274 -- Specifies the date at which the datetrack modes will be operated on.
275 -- p_base_key_value
276 -- Specifies the primary key value for this datetrack entity.
277 -- (E.g. For this entity the assignment of the argument would be:
278 -- p_base_key_value = :elig_per_id).
279 --
280 -- Post Success:
281 -- Processing continues.
282 --
283 -- Post Failure:
284 -- Failure might occur if for the specified effective date and primary key
285 -- value a row doesn't exist.
286 --
287 -- Developer Implementation Notes:
288 -- This procedure could require changes if this entity has any sepcific
289 -- delete restrictions.
290 -- For example, this entity might disallow the datetrack delete mode of
291 -- ZAP. To implement this you would have to set and return a Boolean value
292 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
293 --
294 -- Access Status:
295 -- Internal Development Use Only.
296 --
297 -- {End Of Comments}
298 -- ----------------------------------------------------------------------------
299 Procedure find_dt_del_modes
300 (p_effective_date in date,
301 p_base_key_value in number,
302 p_zap out nocopy boolean,
303 p_delete out nocopy boolean,
304 p_future_change out nocopy boolean,
305 p_delete_next_change out nocopy boolean);
306 --
307 -- ----------------------------------------------------------------------------
308 -- |--------------------------< find_dt_upd_modes >---------------------------|
309 -- ----------------------------------------------------------------------------
310 -- {Start Of Comments}
311 --
312 -- Description:
313 -- This procedure is used to determine what datetrack update modes are
314 -- allowed as of the effective date for this entity. The procedure will
315 -- return a corresponding Boolean value for each of the update modes
316 -- available where TRUE indicates that the corresponding update mode
317 -- is available.
318 --
319 -- Prerequisites:
320 -- None.
321 --
322 -- In Parameters:
323 -- p_effective_date
324 -- Specifies the date at which the datetrack modes will be operated on.
325 -- p_base_key_value
326 -- Specifies the primary key value for this datetrack entity.
327 -- (E.g. For this entity the assignment of the argument would be:
328 -- p_base_key_value = :elig_per_id).
329 --
330 -- Post Success:
331 -- Processing continues.
332 --
333 -- Post Failure:
334 -- Failure might occur if for the specified effective date and primary key
335 -- value a row doesn't exist.
336 --
337 -- Developer Implementation Notes:
338 -- This procedure could require changes if this entity has any sepcific
339 -- delete restrictions.
340 -- For example, this entity might disallow the datetrack update mode of
341 -- UPDATE. To implement this you would have to set and return a Boolean
342 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
343 --
344 -- Access Status:
345 -- Internal Development Use Only.
346 --
347 -- {End Of Comments}
348 -- ----------------------------------------------------------------------------
349 Procedure find_dt_upd_modes
350 (p_effective_date in date,
351 p_base_key_value in number,
352 p_correction out nocopy boolean,
353 p_update out nocopy boolean,
354 p_update_override out nocopy boolean,
355 p_update_change_insert out nocopy boolean);
356 --
357 -- ----------------------------------------------------------------------------
358 -- |------------------------< upd_effective_end_date >------------------------|
362 -- Description:
359 -- ----------------------------------------------------------------------------
360 -- {Start Of Comments}
361 --
363 -- This procedure will update the specified datetrack row with the
364 -- specified new effective end date. The object version number is also
365 -- set to the next object version number. DateTrack modes which call
366 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
367 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
368 -- This is an internal datetrack maintenance procedure which should
369 -- not be modified in anyway.
370 --
371 -- Prerequisites:
372 -- None.
373 --
374 -- In Parameters:
375 -- p_new_effective_end_date
376 -- Specifies the new effective end date which will be set for the
377 -- row as of the effective date.
378 -- p_base_key_value
379 -- Specifies the primary key value for this datetrack entity.
380 -- (E.g. For this entity the assignment of the argument would be:
381 -- p_base_key_value = :elig_per_id).
382 --
383 -- Post Success:
384 -- The specified row will be updated with the new effective end date and
385 -- object_version_number.
386 --
387 -- Post Failure:
388 -- Failure might occur if for the specified effective date and primary key
389 -- value a row doesn't exist.
390 --
391 -- Developer Implementation Notes:
392 -- This is an internal datetrack maintenance procedure which should
393 -- not be modified in anyway.
394 --
395 -- Access Status:
396 -- Internal Row Handler Use Only.
397 --
398 -- {End Of Comments}
399 -- ----------------------------------------------------------------------------
400 Procedure upd_effective_end_date
401 (p_effective_date in date,
402 p_base_key_value in number,
403 p_new_effective_end_date in date,
404 p_validation_start_date in date,
405 p_validation_end_date in date,
406 p_object_version_number out nocopy number);
407 --
408 -- ----------------------------------------------------------------------------
409 -- |---------------------------------< lck >----------------------------------|
410 -- ----------------------------------------------------------------------------
411 -- {Start Of Comments}
412 --
413 -- Description:
414 -- The Lck process for datetrack is complicated and comprises of the
415 -- following processing
416 -- The processing steps are as follows:
417 -- 1) The row to be updated or deleted must be locked.
418 -- By locking this row, the g_old_rec record data type is populated.
419 -- 2) If a comment exists the text is selected from hr_comments.
420 -- 3) The datetrack mode is then validated to ensure the operation is
421 -- valid. If the mode is valid the validation start and end dates for
422 -- the mode will be derived and returned. Any required locking is
423 -- completed when the datetrack mode is validated.
424 --
425 -- Prerequisites:
426 -- When attempting to call the lck procedure the object version number,
427 -- primary key, effective date and datetrack mode must be specified.
428 --
429 -- In Parameters:
430 -- p_effective_date
431 -- Specifies the date of the datetrack update operation.
432 -- p_datetrack_mode
433 -- Determines the datetrack update or delete mode.
434 --
435 -- Post Success:
436 -- On successful completion of the Lck process the row to be updated or
437 -- deleted will be locked and selected into the global data structure
438 -- g_old_rec.
439 --
440 -- Post Failure:
441 -- The Lck process can fail for three reasons:
442 -- 1) When attempting to lock the row the row could already be locked by
443 -- another user. This will raise the HR_Api.Object_Locked exception.
444 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
445 -- This error is trapped and reported using the message name
446 -- 'HR_7220_INVALID_PRIMARY_KEY'.
447 -- 3) The row although existing in the HR Schema has a different object
448 -- version number than the object version number specified.
449 -- This error is trapped and reported using the message name
450 -- 'HR_7155_OBJECT_INVALID'.
451 --
452 -- Developer Implementation Notes:
453 -- None.
454 --
455 -- Access Status:
456 -- Internal Development Use Only.
457 --
458 -- {End Of Comments}
459 -- ----------------------------------------------------------------------------
460 Procedure lck
461 (p_effective_date in date,
462 p_datetrack_mode in varchar2,
463 p_elig_per_id in number,
464 p_object_version_number in number,
465 p_validation_start_date out nocopy date,
466 p_validation_end_date out nocopy date);
467 --
468 -- ----------------------------------------------------------------------------
469 -- |-----------------------------< convert_args >-----------------------------|
470 -- ----------------------------------------------------------------------------
471 -- {Start Of Comments}
472 --
473 -- Description:
474 -- This function is used to turn attribute parameters into the record
475 -- structure parameter g_rec_type.
476 --
477 -- Prerequisites:
478 -- This is a private function and can only be called from the ins or upd
479 -- attribute processes.
480 --
481 -- In Parameters:
482 --
483 -- Post Success:
484 -- A returning record structure will be returned.
488 -- errors within this function will be a PL/SQL value error due to conversion
485 --
486 -- Post Failure:
487 -- No direct error handling is required within this function. Any possible
489 -- of datatypes or data lengths.
490 --
491 -- Developer Implementation Notes:
492 -- None.
493 --
494 -- Access Status:
495 -- Internal Row Handler Use Only.
496 --
497 -- {End Of Comments}
498 -- ----------------------------------------------------------------------------
499 Function convert_args
500 (
501 p_elig_per_id in number,
502 p_effective_start_date in date,
503 p_effective_end_date in date,
504 p_business_group_id in number,
505 p_pl_id in number,
506 p_pgm_id in number,
507 p_plip_id in number,
508 p_ptip_id in number,
509 p_ler_id in number,
510 p_person_id in number,
511 p_per_in_ler_id in number,
512 p_dpnt_othr_pl_cvrd_rl_flag in varchar2,
513 p_prtn_ovridn_thru_dt in date,
514 p_pl_key_ee_flag in varchar2,
515 p_pl_hghly_compd_flag in varchar2,
516 p_elig_flag in varchar2,
517 p_comp_ref_amt in number,
518 p_cmbn_age_n_los_val in number,
519 p_comp_ref_uom in varchar2,
520 p_age_val in number,
521 p_los_val in number,
522 p_prtn_end_dt in date,
523 p_prtn_strt_dt in date,
524 p_wait_perd_cmpltn_dt in date,
525 p_wait_perd_strt_dt in date,
526 p_wv_ctfn_typ_cd in varchar2,
527 p_hrs_wkd_val in number,
528 p_hrs_wkd_bndry_perd_cd in varchar2,
529 p_prtn_ovridn_flag in varchar2,
530 p_no_mx_prtn_ovrid_thru_flag in varchar2,
531 p_prtn_ovridn_rsn_cd in varchar2,
532 p_age_uom in varchar2,
533 p_los_uom in varchar2,
534 p_ovrid_svc_dt in date,
535 p_inelg_rsn_cd in varchar2,
536 p_frz_los_flag in varchar2,
537 p_frz_age_flag in varchar2,
538 p_frz_cmp_lvl_flag in varchar2,
539 p_frz_pct_fl_tm_flag in varchar2,
540 p_frz_hrs_wkd_flag in varchar2,
541 p_frz_comb_age_and_los_flag in varchar2,
542 p_dstr_rstcn_flag in varchar2,
543 p_pct_fl_tm_val in number,
544 p_wv_prtn_rsn_cd in varchar2,
545 p_pl_wvd_flag in varchar2,
546 p_rt_comp_ref_amt in number,
547 p_rt_cmbn_age_n_los_val in number,
548 p_rt_comp_ref_uom in varchar2,
549 p_rt_age_val in number,
550 p_rt_los_val in number,
551 p_rt_hrs_wkd_val in number,
552 p_rt_hrs_wkd_bndry_perd_cd in varchar2,
553 p_rt_age_uom in varchar2,
554 p_rt_los_uom in varchar2,
555 p_rt_pct_fl_tm_val in number,
556 p_rt_frz_los_flag in varchar2,
557 p_rt_frz_age_flag in varchar2,
558 p_rt_frz_cmp_lvl_flag in varchar2,
559 p_rt_frz_pct_fl_tm_flag in varchar2,
560 p_rt_frz_hrs_wkd_flag in varchar2,
561 p_rt_frz_comb_age_and_los_flag in varchar2,
562 p_once_r_cntug_cd in varchar2,
563 p_pl_ordr_num in number,
564 p_plip_ordr_num in number,
565 p_ptip_ordr_num in number,
566 p_pep_attribute_category in varchar2,
567 p_pep_attribute1 in varchar2,
568 p_pep_attribute2 in varchar2,
569 p_pep_attribute3 in varchar2,
570 p_pep_attribute4 in varchar2,
571 p_pep_attribute5 in varchar2,
572 p_pep_attribute6 in varchar2,
573 p_pep_attribute7 in varchar2,
574 p_pep_attribute8 in varchar2,
575 p_pep_attribute9 in varchar2,
576 p_pep_attribute10 in varchar2,
577 p_pep_attribute11 in varchar2,
578 p_pep_attribute12 in varchar2,
579 p_pep_attribute13 in varchar2,
580 p_pep_attribute14 in varchar2,
581 p_pep_attribute15 in varchar2,
582 p_pep_attribute16 in varchar2,
583 p_pep_attribute17 in varchar2,
584 p_pep_attribute18 in varchar2,
585 p_pep_attribute19 in varchar2,
586 p_pep_attribute20 in varchar2,
587 p_pep_attribute21 in varchar2,
588 p_pep_attribute22 in varchar2,
589 p_pep_attribute23 in varchar2,
590 p_pep_attribute24 in varchar2,
591 p_pep_attribute25 in varchar2,
592 p_pep_attribute26 in varchar2,
593 p_pep_attribute27 in varchar2,
594 p_pep_attribute28 in varchar2,
595 p_pep_attribute29 in varchar2,
596 p_pep_attribute30 in varchar2,
597 p_request_id in number,
598 p_program_application_id in number,
599 p_program_id in number,
600 p_program_update_date in date,
601 p_object_version_number in number
602 )
603 Return g_rec_type;
604 --
605 end ben_pep_shd;