1 Package ben_pen_shd AUTHID CURRENT_USER as
2 /* $Header: bepenrhi.pkh 120.3 2011/07/03 08:17:27 pvelvano ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 prtt_enrt_rslt_id number(15),
11 effective_start_date date,
12 effective_end_date date,
13 business_group_id number(15),
14 oipl_id number(15),
15 person_id number(15),
16 assignment_id number(15),
17 pgm_id number(15),
18 pl_id number(15),
19 rplcs_sspndd_rslt_id number(15),
20 ptip_id number(15),
21 pl_typ_id number(15),
22 ler_id number(15),
23 sspndd_flag varchar2(30),
24 prtt_is_cvrd_flag varchar2(30),
25 bnft_amt number,
26 uom varchar2(30),
27 orgnl_enrt_dt date,
28 enrt_mthd_cd varchar2(30),
29 no_lngr_elig_flag varchar2(30),
30 enrt_ovridn_flag varchar2(30),
31 enrt_ovrid_rsn_cd varchar2(30),
32 erlst_deenrt_dt date,
33 enrt_cvg_strt_dt date,
34 enrt_cvg_thru_dt date,
35 enrt_ovrid_thru_dt date,
36 pl_ordr_num number(15),
37 plip_ordr_num number(15),
38 ptip_ordr_num number(15),
39 oipl_ordr_num number(15),
40 pen_attribute_category varchar2(30),
41 pen_attribute1 varchar2(150),
42 pen_attribute2 varchar2(150),
43 pen_attribute3 varchar2(150),
44 pen_attribute4 varchar2(150),
45 pen_attribute5 varchar2(150),
46 pen_attribute6 varchar2(150),
47 pen_attribute7 varchar2(150),
48 pen_attribute8 varchar2(150),
49 pen_attribute9 varchar2(150),
50 pen_attribute10 varchar2(150),
51 pen_attribute11 varchar2(150),
52 pen_attribute12 varchar2(150),
53 pen_attribute13 varchar2(150),
54 pen_attribute14 varchar2(150),
55 pen_attribute15 varchar2(150),
56 pen_attribute16 varchar2(150),
57 pen_attribute17 varchar2(150),
58 pen_attribute18 varchar2(150),
59 pen_attribute19 varchar2(150),
60 pen_attribute20 varchar2(150),
61 pen_attribute21 varchar2(150),
62 pen_attribute22 varchar2(150),
63 pen_attribute23 varchar2(150),
64 pen_attribute24 varchar2(150),
65 pen_attribute25 varchar2(150),
66 pen_attribute26 varchar2(150),
67 pen_attribute27 varchar2(150),
68 pen_attribute28 varchar2(150),
69 pen_attribute29 varchar2(150),
70 pen_attribute30 varchar2(150),
71 request_id number(15),
72 program_application_id number(15),
73 program_id number(15),
74 program_update_date date,
75 object_version_number number(9),
76 per_in_ler_id number(15),
77 bnft_typ_cd varchar2(30),
78 bnft_ordr_num number(15),
79 prtt_enrt_rslt_stat_cd varchar2(30),
80 bnft_nnmntry_uom varchar2(30) ,
81 comp_lvl_cd varchar2(30)
82 );
83 --
84 -- ----------------------------------------------------------------------------
85 -- | Global Definitions - Internal Development Use Only |
86 -- ----------------------------------------------------------------------------
87 --
88 g_old_rec g_rec_type; -- Global record definition
89 g_api_dml boolean; -- Global api dml status
90 --
91 -- ----------------------------------------------------------------------------
92 -- |------------------------< return_api_dml_status >-------------------------|
93 -- ----------------------------------------------------------------------------
94 -- {Start Of Comments}
95 --
96 -- Description:
97 -- This function will return the current g_api_dml private global
98 -- boolean status.
99 -- The g_api_dml status determines if at the time of the function
100 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
101 -- is being issued from within an api.
102 -- If the status is TRUE then a dml statement is being issued from
103 -- within this entity api.
104 -- This function is primarily to support database triggers which
105 -- need to maintain the object_version_number for non-supported
106 -- dml statements (i.e. dml statement issued outside of the api layer).
107 --
108 -- Prerequisites:
109 -- None.
110 --
111 -- In Parameters:
112 -- None.
113 --
114 -- Post Success:
115 -- Processing continues.
116 -- If the function returns a TRUE value then, dml is being executed from
117 -- within this api.
118 --
119 -- Post Failure:
120 -- None.
121 --
122 -- Access Status:
123 -- Internal Row Handler Use Only.
124 --
125 -- {End Of Comments}
126 -- ----------------------------------------------------------------------------
127 Function return_api_dml_status Return Boolean;
128 --
129 -- ----------------------------------------------------------------------------
130 -- |---------------------------< constraint_error >---------------------------|
131 -- ----------------------------------------------------------------------------
132 -- {Start Of Comments}
133 --
134 -- Description:
135 -- This procedure is called when a constraint has been violated (i.e.
136 -- The exception hr_api.check_integrity_violated,
137 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
138 -- hr_api.unique_integrity_violated has been raised).
139 -- The exceptions can only be raised as follows:
140 -- 1) A check constraint can only be violated during an INSERT or UPDATE
141 -- dml operation.
142 -- 2) A parent integrity constraint can only be violated during an
143 -- INSERT or UPDATE dml operation.
144 -- 3) A child integrity constraint can only be violated during an
145 -- DELETE dml operation.
146 -- 4) A unique integrity constraint can only be violated during INSERT or
147 -- UPDATE dml operation.
148 --
149 -- Prerequisites:
150 -- 1) Either hr_api.check_integrity_violated,
151 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
152 -- hr_api.unique_integrity_violated has been raised with the subsequent
153 -- stripping of the constraint name from the generated error message
154 -- text.
155 -- 2) Standalone validation test which corresponds with a constraint error.
156 --
157 -- In Parameter:
158 -- p_constraint_name is in upper format and is just the constraint name
159 -- (e.g. not prefixed by brackets, schema owner etc).
160 --
161 -- Post Success:
162 -- Development dependant.
163 --
164 -- Post Failure:
165 -- Developement dependant.
166 --
167 -- Developer Implementation Notes:
168 -- For each constraint being checked the hr system package failure message
169 -- has been generated as a template only. These system error messages should
170 -- be modified as required (i.e. change the system failure message to a user
171 -- friendly defined error message).
172 --
173 -- Access Status:
174 -- Internal Development Use Only.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 Procedure constraint_error
179 (p_constraint_name in all_constraints.constraint_name%TYPE);
180 --
181 -- ----------------------------------------------------------------------------
182 -- |-----------------------------< api_updating >-----------------------------|
183 -- ----------------------------------------------------------------------------
184 -- {Start Of Comments}
185 --
186 -- Description:
187 -- This function is used to populate the g_old_rec record with the current
188 -- row from the database for the specified primary key provided that the
189 -- primary key exists, and is valid, and does not already match the current
190 -- g_old_rec.
191 -- The function will always return a TRUE value if the g_old_rec is
192 -- populated with the current row. A FALSE value will be returned if all of
193 -- the primary key arguments are null.
194 --
195 -- Prerequisites:
196 -- None.
197 --
198 -- In Parameters:
199 --
200 -- Post Success:
201 -- A value of TRUE will be returned indiciating that the g_old_rec is
202 -- current.
203 -- A value of FALSE will be returned if all of the primary key arguments
204 -- have a null value (this indicates that the row has not be inserted into
205 -- the Schema), and therefore could never have a corresponding row.
206 --
207 -- Post Failure:
208 -- A failure can only occur under two circumstances:
209 -- 1) The primary key is invalid (i.e. a row does not exist for the
210 -- specified primary key values).
211 -- 2) If an object_version_number exists but is NOT the same as the current
212 -- g_old_rec value.
213 --
214 -- Developer Implementation Notes:
215 -- None.
216 --
217 -- Access Status:
218 -- Internal Development Use Only.
219 --
220 -- {End Of Comments}
221 -- ----------------------------------------------------------------------------
222 Function api_updating
223 (p_effective_date in date,
224 p_prtt_enrt_rslt_id in number,
225 p_object_version_number in number
226 ) Return Boolean;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |--------------------------< find_dt_del_modes >---------------------------|
230 -- ----------------------------------------------------------------------------
231 -- {Start Of Comments}
232 --
233 -- Description:
234 -- This procedure is used to determine what datetrack delete modes are
235 -- allowed as of the effective date for this entity. The procedure will
236 -- return a corresponding Boolean value for each of the delete modes
237 -- available where TRUE indicates that the corresponding delete mode is
238 -- available.
239 --
240 -- Prerequisites:
241 -- None.
242 --
243 -- In Parameters:
244 -- p_effective_date
245 -- Specifies the date at which the datetrack modes will be operated on.
246 -- p_base_key_value
247 -- Specifies the primary key value for this datetrack entity.
248 -- (E.g. For this entity the assignment of the argument would be:
249 -- p_base_key_value = :prtt_enrt_rslt_id).
250 --
251 -- Post Success:
252 -- Processing continues.
253 --
254 -- Post Failure:
255 -- Failure might occur if for the specified effective date and primary key
256 -- value a row doesn't exist.
257 --
258 -- Developer Implementation Notes:
259 -- This procedure could require changes if this entity has any sepcific
260 -- delete restrictions.
261 -- For example, this entity might disallow the datetrack delete mode of
262 -- ZAP. To implement this you would have to set and return a Boolean value
263 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
264 --
265 -- Access Status:
266 -- Internal Development Use Only.
267 --
268 -- {End Of Comments}
269 -- ----------------------------------------------------------------------------
270 Procedure find_dt_del_modes
271 (p_effective_date in date,
272 p_base_key_value in number,
273 p_zap out nocopy boolean,
274 p_delete out nocopy boolean,
275 p_future_change out nocopy boolean,
276 p_delete_next_change out nocopy boolean);
277 --
278 -- ----------------------------------------------------------------------------
279 -- |--------------------------< find_dt_upd_modes >---------------------------|
280 -- ----------------------------------------------------------------------------
281 -- {Start Of Comments}
282 --
283 -- Description:
284 -- This procedure is used to determine what datetrack update modes are
285 -- allowed as of the effective date for this entity. The procedure will
286 -- return a corresponding Boolean value for each of the update modes
287 -- available where TRUE indicates that the corresponding update mode
288 -- is available.
289 --
290 -- Prerequisites:
291 -- None.
292 --
293 -- In Parameters:
294 -- p_effective_date
295 -- Specifies the date at which the datetrack modes will be operated on.
296 -- p_base_key_value
297 -- Specifies the primary key value for this datetrack entity.
298 -- (E.g. For this entity the assignment of the argument would be:
299 -- p_base_key_value = :prtt_enrt_rslt_id).
300 --
301 -- Post Success:
302 -- Processing continues.
303 --
304 -- Post Failure:
305 -- Failure might occur if for the specified effective date and primary key
306 -- value a row doesn't exist.
307 --
308 -- Developer Implementation Notes:
309 -- This procedure could require changes if this entity has any sepcific
310 -- delete restrictions.
311 -- For example, this entity might disallow the datetrack update mode of
312 -- UPDATE. To implement this you would have to set and return a Boolean
313 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
314 --
315 -- Access Status:
316 -- Internal Development Use Only.
317 --
318 -- {End Of Comments}
319 -- ----------------------------------------------------------------------------
320 Procedure find_dt_upd_modes
321 (p_effective_date in date,
322 p_base_key_value in number,
323 p_correction out nocopy boolean,
324 p_update out nocopy boolean,
325 p_update_override out nocopy boolean,
326 p_update_change_insert out nocopy boolean);
327 --
328 -- ----------------------------------------------------------------------------
332 --
329 -- |------------------------< upd_effective_end_date >------------------------|
330 -- ----------------------------------------------------------------------------
331 -- {Start Of Comments}
333 -- Description:
334 -- This procedure will update the specified datetrack row with the
335 -- specified new effective end date. The object version number is also
336 -- set to the next object version number. DateTrack modes which call
337 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
338 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
339 -- This is an internal datetrack maintenance procedure which should
340 -- not be modified in anyway.
341 -- *** THIS PROCEDURE IS OVERLOADED.
342 -- Prerequisites:
343 -- None.
344 --
345 -- In Parameters:
346 -- p_new_effective_end_date
347 -- Specifies the new effective end date which will be set for the
348 -- row as of the effective date.
349 -- p_base_key_value
350 -- Specifies the primary key value for this datetrack entity.
351 -- (E.g. For this entity the assignment of the argument would be:
352 -- p_base_key_value = :prtt_enrt_rslt_id).
353 --
354 -- Post Success:
355 -- The specified row will be updated with the new effective end date and
356 -- object_version_number.
357 --
358 -- Post Failure:
359 -- Failure might occur if for the specified effective date and primary key
360 -- value a row doesn't exist.
361 --
362 -- Developer Implementation Notes:
363 -- This is an internal datetrack maintenance procedure which should
364 -- not be modified in anyway.
365 --
366 -- Access Status:
367 -- Internal Row Handler Use Only.
368 --
369 -- {End Of Comments}
370 -- ----------------------------------------------------------------------------
371 Procedure upd_effective_end_date
372 (p_effective_date in date,
373 p_base_key_value in number,
374 p_new_effective_end_date in date,
375 p_validation_start_date in date,
376 p_validation_end_date in date,
377 p_object_version_number out nocopy number);
378 --
379 -- ----------------------------------------------------------------------------
380 -- Bug 3843657 : Added this overloaded procedure to pass p_prtt_enrt_rslt_stat_cd
381 -- as an extra paramter.
382 --
383 Procedure upd_effective_end_date
384 (p_effective_date in date,
385 p_base_key_value in number,
386 p_new_effective_end_date in date,
387 p_validation_start_date in date,
388 p_validation_end_date in date,
389 p_prtt_enrt_rslt_stat_cd in varchar2,
390 p_object_version_number out nocopy number);
391 --
392 -- ----------------------------------------------------------------------------
393 -- |---------------------------------< lck >----------------------------------|
394 -- ----------------------------------------------------------------------------
395 -- {Start Of Comments}
396 --
397 -- Description:
398 -- The Lck process for datetrack is complicated and comprises of the
399 -- following processing
400 -- The processing steps are as follows:
401 -- 1) The row to be updated or deleted must be locked.
402 -- By locking this row, the g_old_rec record data type is populated.
403 -- 2) If a comment exists the text is selected from hr_comments.
404 -- 3) The datetrack mode is then validated to ensure the operation is
405 -- valid. If the mode is valid the validation start and end dates for
406 -- the mode will be derived and returned. Any required locking is
407 -- completed when the datetrack mode is validated.
408 --
409 -- Prerequisites:
410 -- When attempting to call the lck procedure the object version number,
411 -- primary key, effective date and datetrack mode must be specified.
412 --
413 -- In Parameters:
414 -- p_effective_date
415 -- Specifies the date of the datetrack update operation.
416 -- p_datetrack_mode
417 -- Determines the datetrack update or delete mode.
418 --
419 -- Post Success:
420 -- On successful completion of the Lck process the row to be updated or
421 -- deleted will be locked and selected into the global data structure
422 -- g_old_rec.
423 --
424 -- Post Failure:
425 -- The Lck process can fail for three reasons:
426 -- 1) When attempting to lock the row the row could already be locked by
427 -- another user. This will raise the HR_Api.Object_Locked exception.
428 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
429 -- This error is trapped and reported using the message name
430 -- 'HR_7220_INVALID_PRIMARY_KEY'.
431 -- 3) The row although existing in the HR Schema has a different object
432 -- version number than the object version number specified.
433 -- This error is trapped and reported using the message name
434 -- 'HR_7155_OBJECT_INVALID'.
435 --
436 -- Developer Implementation Notes:
437 -- None.
438 --
439 -- Access Status:
440 -- Internal Development Use Only.
441 --
442 -- {End Of Comments}
443 -- ----------------------------------------------------------------------------
444 Procedure lck
445 (p_effective_date in date,
446 p_datetrack_mode in varchar2,
447 p_prtt_enrt_rslt_id in number,
448 p_object_version_number in number,
449 p_validation_start_date out nocopy date,
450 p_validation_end_date out nocopy date);
451 --
455 -- {Start Of Comments}
452 -- ----------------------------------------------------------------------------
453 -- |-----------------------------< convert_args >-----------------------------|
454 -- ----------------------------------------------------------------------------
456 --
457 -- Description:
458 -- This function is used to turn attribute parameters into the record
459 -- structure parameter g_rec_type.
460 --
461 -- Prerequisites:
462 -- This is a private function and can only be called from the ins or upd
463 -- attribute processes.
464 --
465 -- In Parameters:
466 --
467 -- Post Success:
468 -- A returning record structure will be returned.
469 --
470 -- Post Failure:
471 -- No direct error handling is required within this function. Any possible
472 -- errors within this function will be a PL/SQL value error due to conversion
473 -- of datatypes or data lengths.
474 --
475 -- Developer Implementation Notes:
476 -- None.
477 --
478 -- Access Status:
479 -- Internal Row Handler Use Only.
480 --
481 -- {End Of Comments}
482 -- ----------------------------------------------------------------------------
483 Function convert_args
484 (
485 p_prtt_enrt_rslt_id in number,
486 p_effective_start_date in date,
487 p_effective_end_date in date,
488 p_business_group_id in number,
489 p_oipl_id in number,
490 p_person_id in number,
491 p_assignment_id in number,
492 p_pgm_id in number,
493 p_pl_id in number,
494 p_rplcs_sspndd_rslt_id in number,
495 p_ptip_id in number,
496 p_pl_typ_id in number,
497 p_ler_id in number,
498 p_sspndd_flag in varchar2,
499 p_prtt_is_cvrd_flag in varchar2,
500 p_bnft_amt in number,
501 p_uom in varchar2,
502 p_orgnl_enrt_dt in date,
503 p_enrt_mthd_cd in varchar2,
504 p_no_lngr_elig_flag in varchar2,
505 p_enrt_ovridn_flag in varchar2,
506 p_enrt_ovrid_rsn_cd in varchar2,
507 p_erlst_deenrt_dt in date,
508 p_enrt_cvg_strt_dt in date,
509 p_enrt_cvg_thru_dt in date,
510 p_enrt_ovrid_thru_dt in date,
511 p_pl_ordr_num in number,
512 p_plip_ordr_num in number,
513 p_ptip_ordr_num in number,
514 p_oipl_ordr_num in number,
515 p_pen_attribute_category in varchar2,
516 p_pen_attribute1 in varchar2,
517 p_pen_attribute2 in varchar2,
518 p_pen_attribute3 in varchar2,
519 p_pen_attribute4 in varchar2,
520 p_pen_attribute5 in varchar2,
521 p_pen_attribute6 in varchar2,
522 p_pen_attribute7 in varchar2,
523 p_pen_attribute8 in varchar2,
524 p_pen_attribute9 in varchar2,
525 p_pen_attribute10 in varchar2,
526 p_pen_attribute11 in varchar2,
527 p_pen_attribute12 in varchar2,
528 p_pen_attribute13 in varchar2,
529 p_pen_attribute14 in varchar2,
530 p_pen_attribute15 in varchar2,
531 p_pen_attribute16 in varchar2,
532 p_pen_attribute17 in varchar2,
533 p_pen_attribute18 in varchar2,
534 p_pen_attribute19 in varchar2,
535 p_pen_attribute20 in varchar2,
536 p_pen_attribute21 in varchar2,
537 p_pen_attribute22 in varchar2,
538 p_pen_attribute23 in varchar2,
539 p_pen_attribute24 in varchar2,
540 p_pen_attribute25 in varchar2,
541 p_pen_attribute26 in varchar2,
542 p_pen_attribute27 in varchar2,
543 p_pen_attribute28 in varchar2,
544 p_pen_attribute29 in varchar2,
545 p_pen_attribute30 in varchar2,
546 p_request_id in number,
547 p_program_application_id in number,
548 p_program_id in number,
549 p_program_update_date in date,
550 p_object_version_number in number,
551 p_per_in_ler_id in number,
552 p_bnft_typ_cd in varchar2,
553 p_bnft_ordr_num in number,
554 p_prtt_enrt_rslt_stat_cd in varchar2,
555 p_bnft_nnmntry_uom in varchar2 ,
556 p_comp_lvl_cd in varchar2
557
558 )
559 Return g_rec_type;
560 end ben_pen_shd;