1 Package ben_bpp_shd AUTHID CURRENT_USER as
2 /* $Header: bebpprhi.pkh 120.0 2005/05/28 00:48:43 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 bnft_prvdr_pool_id number(15),
11 effective_start_date date,
12 effective_end_date date,
13 name varchar2(240), -- UTF8 Change Bug 2254683
14 pgm_pool_flag varchar2(9), -- Increased length
15 excs_alwys_fftd_flag varchar2(30),
16 use_for_pgm_pool_flag varchar2(9), -- Increased length
17 pct_rndg_cd varchar2(30),
18 pct_rndg_rl number(15),
19 val_rndg_cd varchar2(30),
20 val_rndg_rl number(15),
21 dflt_excs_trtmt_cd varchar2(30),
22 dflt_excs_trtmt_rl number(15),
23 rlovr_rstrcn_cd varchar2(30),
24 no_mn_dstrbl_pct_flag varchar2(30),
25 no_mn_dstrbl_val_flag varchar2(30),
26 no_mx_dstrbl_pct_flag varchar2(30),
27 no_mx_dstrbl_val_flag varchar2(30),
28 auto_alct_excs_flag varchar2(30),
29 alws_ngtv_crs_flag varchar2(30),
30 uses_net_crs_mthd_flag varchar2(30),
31 mx_dfcit_pct_pool_crs_num number,
32 mx_dfcit_pct_comp_num number,
33 comp_lvl_fctr_id number(15),
34 mn_dstrbl_pct_num number(15),
35 mn_dstrbl_val number,
36 mx_dstrbl_pct_num number(15),
37 mx_dstrbl_val number,
38 excs_trtmt_cd varchar2(30),
39 ptip_id number(15),
40 plip_id number(15),
41 pgm_id number(15),
42 oiplip_id number(15),
43 cmbn_plip_id number(15),
44 cmbn_ptip_id number(15),
45 cmbn_ptip_opt_id number(15),
46 business_group_id number(15),
47 bpp_attribute_category varchar2(30),
48 bpp_attribute1 varchar2(150),
49 bpp_attribute2 varchar2(150),
50 bpp_attribute3 varchar2(150),
51 bpp_attribute4 varchar2(150),
52 bpp_attribute5 varchar2(150),
53 bpp_attribute6 varchar2(150),
54 bpp_attribute7 varchar2(150),
55 bpp_attribute8 varchar2(150),
56 bpp_attribute9 varchar2(150),
57 bpp_attribute10 varchar2(150),
58 bpp_attribute11 varchar2(150),
59 bpp_attribute12 varchar2(150),
60 bpp_attribute13 varchar2(150),
61 bpp_attribute14 varchar2(150),
62 bpp_attribute15 varchar2(150),
63 bpp_attribute16 varchar2(150),
64 bpp_attribute17 varchar2(150),
65 bpp_attribute18 varchar2(150),
66 bpp_attribute19 varchar2(150),
67 bpp_attribute20 varchar2(150),
68 bpp_attribute21 varchar2(150),
69 bpp_attribute22 varchar2(150),
70 bpp_attribute23 varchar2(150),
71 bpp_attribute24 varchar2(150),
72 bpp_attribute25 varchar2(150),
73 bpp_attribute26 varchar2(150),
74 bpp_attribute27 varchar2(150),
75 bpp_attribute28 varchar2(150),
76 bpp_attribute29 varchar2(150),
77 bpp_attribute30 varchar2(150),
78 object_version_number number(9)
79 );
80 --
81 -- ----------------------------------------------------------------------------
82 -- | Global Definitions - Internal Development Use Only |
83 -- ----------------------------------------------------------------------------
84 --
85 g_old_rec g_rec_type; -- Global record definition
86 g_api_dml boolean; -- Global api dml status
87 --
88 -- ----------------------------------------------------------------------------
89 -- |------------------------< return_api_dml_status >-------------------------|
90 -- ----------------------------------------------------------------------------
91 -- {Start Of Comments}
92 --
93 -- Description:
94 -- This function will return the current g_api_dml private global
95 -- boolean status.
96 -- The g_api_dml status determines if at the time of the function
97 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
98 -- is being issued from within an api.
99 -- If the status is TRUE then a dml statement is being issued from
100 -- within this entity api.
101 -- This function is primarily to support database triggers which
102 -- need to maintain the object_version_number for non-supported
103 -- dml statements (i.e. dml statement issued outside of the api layer).
104 --
105 -- Prerequisites:
106 -- None.
107 --
108 -- In Parameters:
109 -- None.
110 --
111 -- Post Success:
112 -- Processing continues.
113 -- If the function returns a TRUE value then, dml is being executed from
114 -- within this api.
115 --
116 -- Post Failure:
117 -- None.
118 --
119 -- Access Status:
120 -- Internal Row Handler Use Only.
121 --
122 -- {End Of Comments}
123 -- ----------------------------------------------------------------------------
124 Function return_api_dml_status Return Boolean;
125 --
126 -- ----------------------------------------------------------------------------
127 -- |---------------------------< constraint_error >---------------------------|
128 -- ----------------------------------------------------------------------------
129 -- {Start Of Comments}
130 --
131 -- Description:
132 -- This procedure is called when a constraint has been violated (i.e.
133 -- The exception hr_api.check_integrity_violated,
134 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
135 -- hr_api.unique_integrity_violated has been raised).
136 -- The exceptions can only be raised as follows:
137 -- 1) A check constraint can only be violated during an INSERT or UPDATE
138 -- dml operation.
139 -- 2) A parent integrity constraint can only be violated during an
140 -- INSERT or UPDATE dml operation.
141 -- 3) A child integrity constraint can only be violated during an
142 -- DELETE dml operation.
143 -- 4) A unique integrity constraint can only be violated during INSERT or
144 -- UPDATE dml operation.
145 --
146 -- Prerequisites:
147 -- 1) Either hr_api.check_integrity_violated,
148 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
149 -- hr_api.unique_integrity_violated has been raised with the subsequent
150 -- stripping of the constraint name from the generated error message
151 -- text.
152 -- 2) Standalone validation test which corresponds with a constraint error.
153 --
154 -- In Parameter:
155 -- p_constraint_name is in upper format and is just the constraint name
156 -- (e.g. not prefixed by brackets, schema owner etc).
157 --
158 -- Post Success:
159 -- Development dependant.
160 --
161 -- Post Failure:
162 -- Developement dependant.
163 --
164 -- Developer Implementation Notes:
165 -- For each constraint being checked the hr system package failure message
166 -- has been generated as a template only. These system error messages should
167 -- be modified as required (i.e. change the system failure message to a user
168 -- friendly defined error message).
169 --
170 -- Access Status:
171 -- Internal Development Use Only.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure constraint_error
176 (p_constraint_name in all_constraints.constraint_name%TYPE);
177 --
178 -- ----------------------------------------------------------------------------
179 -- |-----------------------------< api_updating >-----------------------------|
180 -- ----------------------------------------------------------------------------
181 -- {Start Of Comments}
182 --
183 -- Description:
184 -- This function is used to populate the g_old_rec record with the current
185 -- row from the database for the specified primary key provided that the
186 -- primary key exists, and is valid, and does not already match the current
187 -- g_old_rec.
188 -- The function will always return a TRUE value if the g_old_rec is
189 -- populated with the current row. A FALSE value will be returned if all of
190 -- the primary key arguments are null.
191 --
192 -- Prerequisites:
193 -- None.
194 --
195 -- In Parameters:
196 --
197 -- Post Success:
198 -- A value of TRUE will be returned indiciating that the g_old_rec is
199 -- current.
200 -- A value of FALSE will be returned if all of the primary key arguments
201 -- have a null value (this indicates that the row has not be inserted into
202 -- the Schema), and therefore could never have a corresponding row.
203 --
204 -- Post Failure:
205 -- A failure can only occur under two circumstances:
206 -- 1) The primary key is invalid (i.e. a row does not exist for the
207 -- specified primary key values).
208 -- 2) If an object_version_number exists but is NOT the same as the current
209 -- g_old_rec value.
210 --
211 -- Developer Implementation Notes:
212 -- None.
213 --
214 -- Access Status:
215 -- Internal Development Use Only.
216 --
217 -- {End Of Comments}
218 -- ----------------------------------------------------------------------------
219 Function api_updating
220 (p_effective_date in date,
221 p_bnft_prvdr_pool_id in number,
222 p_object_version_number in number
223 ) Return Boolean;
224 --
225 -- ----------------------------------------------------------------------------
226 -- |--------------------------< find_dt_del_modes >---------------------------|
227 -- ----------------------------------------------------------------------------
228 -- {Start Of Comments}
229 --
230 -- Description:
231 -- This procedure is used to determine what datetrack delete modes are
232 -- allowed as of the effective date for this entity. The procedure will
233 -- return a corresponding Boolean value for each of the delete modes
234 -- available where TRUE indicates that the corresponding delete mode is
235 -- available.
236 --
237 -- Prerequisites:
238 -- None.
239 --
240 -- In Parameters:
241 -- p_effective_date
242 -- Specifies the date at which the datetrack modes will be operated on.
243 -- p_base_key_value
244 -- Specifies the primary key value for this datetrack entity.
245 -- (E.g. For this entity the assignment of the argument would be:
246 -- p_base_key_value = :bnft_prvdr_pool_id).
247 --
248 -- Post Success:
249 -- Processing continues.
250 --
251 -- Post Failure:
252 -- Failure might occur if for the specified effective date and primary key
253 -- value a row doesn't exist.
254 --
255 -- Developer Implementation Notes:
256 -- This procedure could require changes if this entity has any sepcific
257 -- delete restrictions.
258 -- For example, this entity might disallow the datetrack delete mode of
259 -- ZAP. To implement this you would have to set and return a Boolean value
260 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
261 --
262 -- Access Status:
263 -- Internal Development Use Only.
264 --
265 -- {End Of Comments}
266 -- ----------------------------------------------------------------------------
267 Procedure find_dt_del_modes
268 (p_effective_date in date,
269 p_base_key_value in number,
270 p_zap out nocopy boolean,
271 p_delete out nocopy boolean,
272 p_future_change out nocopy boolean,
273 p_delete_next_change out nocopy boolean);
274 --
275 -- ----------------------------------------------------------------------------
276 -- |--------------------------< find_dt_upd_modes >---------------------------|
277 -- ----------------------------------------------------------------------------
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- This procedure is used to determine what datetrack update modes are
282 -- allowed as of the effective date for this entity. The procedure will
283 -- return a corresponding Boolean value for each of the update modes
284 -- available where TRUE indicates that the corresponding update mode
285 -- is available.
286 --
287 -- Prerequisites:
288 -- None.
289 --
290 -- In Parameters:
291 -- p_effective_date
292 -- Specifies the date at which the datetrack modes will be operated on.
293 -- p_base_key_value
294 -- Specifies the primary key value for this datetrack entity.
295 -- (E.g. For this entity the assignment of the argument would be:
296 -- p_base_key_value = :bnft_prvdr_pool_id).
297 --
298 -- Post Success:
299 -- Processing continues.
300 --
301 -- Post Failure:
302 -- Failure might occur if for the specified effective date and primary key
303 -- value a row doesn't exist.
304 --
305 -- Developer Implementation Notes:
306 -- This procedure could require changes if this entity has any sepcific
307 -- delete restrictions.
308 -- For example, this entity might disallow the datetrack update mode of
309 -- UPDATE. To implement this you would have to set and return a Boolean
310 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
311 --
312 -- Access Status:
313 -- Internal Development Use Only.
314 --
315 -- {End Of Comments}
316 -- ----------------------------------------------------------------------------
317 Procedure find_dt_upd_modes
318 (p_effective_date in date,
319 p_base_key_value in number,
320 p_correction out nocopy boolean,
321 p_update out nocopy boolean,
322 p_update_override out nocopy boolean,
323 p_update_change_insert out nocopy boolean);
324 --
325 -- ----------------------------------------------------------------------------
326 -- |------------------------< upd_effective_end_date >------------------------|
327 -- ----------------------------------------------------------------------------
328 -- {Start Of Comments}
329 --
330 -- Description:
331 -- This procedure will update the specified datetrack row with the
332 -- specified new effective end date. The object version number is also
333 -- set to the next object version number. DateTrack modes which call
334 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
335 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
339 -- Prerequisites:
336 -- This is an internal datetrack maintenance procedure which should
337 -- not be modified in anyway.
338 --
340 -- None.
341 --
342 -- In Parameters:
343 -- p_new_effective_end_date
344 -- Specifies the new effective end date which will be set for the
345 -- row as of the effective date.
346 -- p_base_key_value
347 -- Specifies the primary key value for this datetrack entity.
348 -- (E.g. For this entity the assignment of the argument would be:
349 -- p_base_key_value = :bnft_prvdr_pool_id).
350 --
351 -- Post Success:
352 -- The specified row will be updated with the new effective end date and
353 -- object_version_number.
354 --
355 -- Post Failure:
356 -- Failure might occur if for the specified effective date and primary key
357 -- value a row doesn't exist.
358 --
359 -- Developer Implementation Notes:
360 -- This is an internal datetrack maintenance procedure which should
361 -- not be modified in anyway.
362 --
363 -- Access Status:
364 -- Internal Row Handler Use Only.
365 --
366 -- {End Of Comments}
367 -- ----------------------------------------------------------------------------
368 Procedure upd_effective_end_date
369 (p_effective_date in date,
370 p_base_key_value in number,
371 p_new_effective_end_date in date,
372 p_validation_start_date in date,
373 p_validation_end_date in date,
374 p_object_version_number out nocopy number);
375 --
376 -- ----------------------------------------------------------------------------
377 -- |---------------------------------< lck >----------------------------------|
378 -- ----------------------------------------------------------------------------
379 -- {Start Of Comments}
380 --
381 -- Description:
382 -- The Lck process for datetrack is complicated and comprises of the
383 -- following processing
384 -- The processing steps are as follows:
385 -- 1) The row to be updated or deleted must be locked.
386 -- By locking this row, the g_old_rec record data type is populated.
387 -- 2) If a comment exists the text is selected from hr_comments.
388 -- 3) The datetrack mode is then validated to ensure the operation is
389 -- valid. If the mode is valid the validation start and end dates for
390 -- the mode will be derived and returned. Any required locking is
391 -- completed when the datetrack mode is validated.
392 --
393 -- Prerequisites:
394 -- When attempting to call the lck procedure the object version number,
395 -- primary key, effective date and datetrack mode must be specified.
396 --
397 -- In Parameters:
398 -- p_effective_date
399 -- Specifies the date of the datetrack update operation.
400 -- p_datetrack_mode
401 -- Determines the datetrack update or delete mode.
402 --
403 -- Post Success:
404 -- On successful completion of the Lck process the row to be updated or
405 -- deleted will be locked and selected into the global data structure
406 -- g_old_rec.
407 --
408 -- Post Failure:
409 -- The Lck process can fail for three reasons:
410 -- 1) When attempting to lock the row the row could already be locked by
411 -- another user. This will raise the HR_Api.Object_Locked exception.
412 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
413 -- This error is trapped and reported using the message name
414 -- 'HR_7220_INVALID_PRIMARY_KEY'.
415 -- 3) The row although existing in the HR Schema has a different object
416 -- version number than the object version number specified.
417 -- This error is trapped and reported using the message name
418 -- 'HR_7155_OBJECT_INVALID'.
419 --
420 -- Developer Implementation Notes:
421 -- None.
422 --
423 -- Access Status:
424 -- Internal Development Use Only.
425 --
426 -- {End Of Comments}
427 -- ----------------------------------------------------------------------------
428 Procedure lck
429 (p_effective_date in date,
430 p_datetrack_mode in varchar2,
431 p_bnft_prvdr_pool_id in number,
432 p_object_version_number in number,
433 p_validation_start_date out nocopy date,
434 p_validation_end_date out nocopy date);
435 --
436 -- ----------------------------------------------------------------------------
437 -- |-----------------------------< convert_args >-----------------------------|
438 -- ----------------------------------------------------------------------------
439 -- {Start Of Comments}
440 --
441 -- Description:
442 -- This function is used to turn attribute parameters into the record
443 -- structure parameter g_rec_type.
444 --
445 -- Prerequisites:
446 -- This is a private function and can only be called from the ins or upd
447 -- attribute processes.
448 --
449 -- In Parameters:
450 --
451 -- Post Success:
452 -- A returning record structure will be returned.
453 --
454 -- Post Failure:
455 -- No direct error handling is required within this function. Any possible
456 -- errors within this function will be a PL/SQL value error due to conversion
457 -- of datatypes or data lengths.
458 --
459 -- Developer Implementation Notes:
460 -- None.
461 --
462 -- Access Status:
463 -- Internal Row Handler Use Only.
464 --
465 -- {End Of Comments}
469 p_bnft_prvdr_pool_id in number,
466 -- ----------------------------------------------------------------------------
467 Function convert_args
468 (
470 p_effective_start_date in date,
471 p_effective_end_date in date,
472 p_name in varchar2,
473 p_pgm_pool_flag in varchar2,
474 p_excs_alwys_fftd_flag in varchar2,
475 p_use_for_pgm_pool_flag in varchar2,
476 p_pct_rndg_cd in varchar2,
477 p_pct_rndg_rl in number,
478 p_val_rndg_cd in varchar2,
479 p_val_rndg_rl in number,
480 p_dflt_excs_trtmt_cd in varchar2,
481 p_dflt_excs_trtmt_rl in number,
482 p_rlovr_rstrcn_cd in varchar2,
483 p_no_mn_dstrbl_pct_flag in varchar2,
484 p_no_mn_dstrbl_val_flag in varchar2,
485 p_no_mx_dstrbl_pct_flag in varchar2,
486 p_no_mx_dstrbl_val_flag in varchar2,
487 p_auto_alct_excs_flag in varchar2,
488 p_alws_ngtv_crs_flag in varchar2,
489 p_uses_net_crs_mthd_flag in varchar2,
490 p_mx_dfcit_pct_pool_crs_num in number,
491 p_mx_dfcit_pct_comp_num in number,
492 p_comp_lvl_fctr_id in number,
493 p_mn_dstrbl_pct_num in number,
494 p_mn_dstrbl_val in number,
495 p_mx_dstrbl_pct_num in number,
496 p_mx_dstrbl_val in number,
497 p_excs_trtmt_cd in varchar2,
498 p_ptip_id in number,
499 p_plip_id in number,
500 p_pgm_id in number,
501 p_oiplip_id in number,
502 p_cmbn_plip_id in number,
503 p_cmbn_ptip_id in number,
504 p_cmbn_ptip_opt_id in number,
505 p_business_group_id in number,
506 p_bpp_attribute_category in varchar2,
507 p_bpp_attribute1 in varchar2,
508 p_bpp_attribute2 in varchar2,
509 p_bpp_attribute3 in varchar2,
510 p_bpp_attribute4 in varchar2,
511 p_bpp_attribute5 in varchar2,
512 p_bpp_attribute6 in varchar2,
513 p_bpp_attribute7 in varchar2,
514 p_bpp_attribute8 in varchar2,
515 p_bpp_attribute9 in varchar2,
516 p_bpp_attribute10 in varchar2,
517 p_bpp_attribute11 in varchar2,
518 p_bpp_attribute12 in varchar2,
519 p_bpp_attribute13 in varchar2,
520 p_bpp_attribute14 in varchar2,
521 p_bpp_attribute15 in varchar2,
522 p_bpp_attribute16 in varchar2,
523 p_bpp_attribute17 in varchar2,
524 p_bpp_attribute18 in varchar2,
525 p_bpp_attribute19 in varchar2,
526 p_bpp_attribute20 in varchar2,
527 p_bpp_attribute21 in varchar2,
528 p_bpp_attribute22 in varchar2,
529 p_bpp_attribute23 in varchar2,
530 p_bpp_attribute24 in varchar2,
531 p_bpp_attribute25 in varchar2,
532 p_bpp_attribute26 in varchar2,
533 p_bpp_attribute27 in varchar2,
534 p_bpp_attribute28 in varchar2,
535 p_bpp_attribute29 in varchar2,
536 p_bpp_attribute30 in varchar2,
537 p_object_version_number in number
538 )
539 Return g_rec_type;
540 --
541 end ben_bpp_shd;