1 Package ben_prc_shd AUTHID CURRENT_USER as
2 /* $Header: beprcrhi.pkh 120.3.12010000.1 2008/07/29 12:53:36 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 prtt_reimbmt_rqst_id number(15),
11 effective_start_date date,
12 effective_end_date date,
13 incrd_from_dt date,
14 incrd_to_dt date,
15 rqst_num number(15),
16 rqst_amt number(15,2),
17 rqst_amt_uom varchar2(30),
18 rqst_btch_num number(15),
19 prtt_reimbmt_rqst_stat_cd varchar2(30),
20 reimbmt_ctfn_typ_prvdd_cd varchar2(30),
21 rcrrg_cd varchar2(30),
22 submitter_person_id number(15),
23 recipient_person_id number(15),
24 provider_person_id number(15),
25 provider_ssn_person_id number(15),
26 pl_id number(15),
27 gd_or_svc_typ_id number(15),
28 contact_relationship_id number(15),
29 business_group_id number(15),
30 opt_id number(15),
31 popl_yr_perd_id_1 number(15),
32 popl_yr_perd_id_2 number(15),
33 amt_year1 number,
34 amt_year2 number,
35 prc_attribute_category varchar2(30),
36 prc_attribute1 varchar2(150),
37 prc_attribute2 varchar2(150),
38 prc_attribute3 varchar2(150),
39 prc_attribute4 varchar2(150),
40 prc_attribute5 varchar2(150),
41 prc_attribute6 varchar2(150),
42 prc_attribute7 varchar2(150),
43 prc_attribute8 varchar2(150),
44 prc_attribute9 varchar2(150),
45 prc_attribute10 varchar2(150),
46 prc_attribute11 varchar2(150),
47 prc_attribute12 varchar2(150),
48 prc_attribute13 varchar2(150),
49 prc_attribute14 varchar2(150),
50 prc_attribute15 varchar2(150),
51 prc_attribute16 varchar2(150),
52 prc_attribute17 varchar2(150),
53 prc_attribute18 varchar2(150),
54 prc_attribute19 varchar2(150),
55 prc_attribute20 varchar2(150),
56 prc_attribute21 varchar2(150),
57 prc_attribute22 varchar2(150),
58 prc_attribute23 varchar2(150),
59 prc_attribute24 varchar2(150),
60 prc_attribute25 varchar2(150),
61 prc_attribute26 varchar2(150),
62 prc_attribute27 varchar2(150),
63 prc_attribute28 varchar2(150),
64 prc_attribute29 varchar2(150),
65 prc_attribute30 varchar2(150),
66 prtt_enrt_rslt_id number(15) ,
67 comment_id number(15) ,
68 object_version_number number(9) ,
69 -- Fide enh -- Check datatype and width.
70 stat_rsn_cd varchar2(30),
71 pymt_stat_cd varchar2(30),
72 pymt_stat_rsn_cd varchar2(30),
73 stat_ovrdn_flag varchar2(30),
74 stat_ovrdn_rsn_cd varchar2(30),
75 stat_prr_to_ovrd varchar2(30),
76 pymt_stat_ovrdn_flag varchar2(30),
77 pymt_stat_ovrdn_rsn_cd varchar2(30),
78 pymt_stat_prr_to_ovrd varchar2(30),
79 adjmt_flag varchar2(30),
80 submtd_dt date,
81 ttl_rqst_amt number,
82 aprvd_for_pymt_amt number,
83 pymt_amount number, -- this not db column added for stroing value
84 exp_incurd_dt date -- 2272862
85 );
86 --
87 -- ----------------------------------------------------------------------------
88 -- | Global Definitions - Internal Development Use Only |
89 -- ----------------------------------------------------------------------------
90 --
91 g_old_rec g_rec_type; -- Global record definition
92 g_api_dml boolean; -- Global api dml status
93 --
94 -- ----------------------------------------------------------------------------
95 -- |------------------------< return_api_dml_status >-------------------------|
96 -- ----------------------------------------------------------------------------
97 -- {Start Of Comments}
98 --
99 -- Description:
100 -- This function will return the current g_api_dml private global
101 -- boolean status.
102 -- The g_api_dml status determines if at the time of the function
103 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
104 -- is being issued from within an api.
105 -- If the status is TRUE then a dml statement is being issued from
106 -- within this entity api.
107 -- This function is primarily to support database triggers which
108 -- need to maintain the object_version_number for non-supported
109 -- dml statements (i.e. dml statement issued outside of the api layer).
110 --
111 -- Prerequisites:
112 -- None.
113 --
114 -- In Parameters:
115 -- None.
116 --
117 -- Post Success:
118 -- Processing continues.
119 -- If the function returns a TRUE value then, dml is being executed from
120 -- within this api.
121 --
122 -- Post Failure:
123 -- None.
124 --
125 -- Access Status:
126 -- Internal Row Handler Use Only.
127 --
128 -- {End Of Comments}
129 -- ----------------------------------------------------------------------------
130 Function return_api_dml_status Return Boolean;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |---------------------------< constraint_error >---------------------------|
134 -- ----------------------------------------------------------------------------
135 -- {Start Of Comments}
136 --
137 -- Description:
138 -- This procedure is called when a constraint has been violated (i.e.
139 -- The exception hr_api.check_integrity_violated,
140 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
141 -- hr_api.unique_integrity_violated has been raised).
142 -- The exceptions can only be raised as follows:
143 -- 1) A check constraint can only be violated during an INSERT or UPDATE
144 -- dml operation.
145 -- 2) A parent integrity constraint can only be violated during an
146 -- INSERT or UPDATE dml operation.
147 -- 3) A child integrity constraint can only be violated during an
148 -- DELETE dml operation.
149 -- 4) A unique integrity constraint can only be violated during INSERT or
150 -- UPDATE dml operation.
151 --
152 -- Prerequisites:
153 -- 1) Either hr_api.check_integrity_violated,
154 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
155 -- hr_api.unique_integrity_violated has been raised with the subsequent
156 -- stripping of the constraint name from the generated error message
157 -- text.
158 -- 2) Standalone validation test which corresponds with a constraint error.
159 --
160 -- In Parameter:
161 -- p_constraint_name is in upper format and is just the constraint name
162 -- (e.g. not prefixed by brackets, schema owner etc).
163 --
164 -- Post Success:
165 -- Development dependant.
166 --
167 -- Post Failure:
168 -- Developement dependant.
169 --
170 -- Developer Implementation Notes:
171 -- For each constraint being checked the hr system package failure message
172 -- has been generated as a template only. These system error messages should
173 -- be modified as required (i.e. change the system failure message to a user
174 -- friendly defined error message).
175 --
176 -- Access Status:
177 -- Internal Development Use Only.
178 --
179 -- {End Of Comments}
180 -- ----------------------------------------------------------------------------
181 Procedure constraint_error
182 (p_constraint_name in all_constraints.constraint_name%TYPE);
183 --
184 -- ----------------------------------------------------------------------------
185 -- |-----------------------------< api_updating >-----------------------------|
186 -- ----------------------------------------------------------------------------
187 -- {Start Of Comments}
188 --
189 -- Description:
190 -- This function is used to populate the g_old_rec record with the current
191 -- row from the database for the specified primary key provided that the
192 -- primary key exists, and is valid, and does not already match the current
193 -- g_old_rec.
194 -- The function will always return a TRUE value if the g_old_rec is
195 -- populated with the current row. A FALSE value will be returned if all of
196 -- the primary key arguments are null.
197 --
198 -- Prerequisites:
199 -- None.
200 --
201 -- In Parameters:
202 --
203 -- Post Success:
204 -- A value of TRUE will be returned indiciating that the g_old_rec is
205 -- current.
206 -- A value of FALSE will be returned if all of the primary key arguments
207 -- have a null value (this indicates that the row has not be inserted into
208 -- the Schema), and therefore could never have a corresponding row.
209 --
210 -- Post Failure:
211 -- A failure can only occur under two circumstances:
212 -- 1) The primary key is invalid (i.e. a row does not exist for the
213 -- specified primary key values).
214 -- 2) If an object_version_number exists but is NOT the same as the current
215 -- g_old_rec value.
216 --
217 -- Developer Implementation Notes:
218 -- None.
219 --
220 -- Access Status:
221 -- Internal Development Use Only.
222 --
223 -- {End Of Comments}
224 -- ----------------------------------------------------------------------------
225 Function api_updating
226 (p_effective_date in date,
227 p_prtt_reimbmt_rqst_id in number,
228 p_object_version_number in number
229 ) Return Boolean;
230 --
231 -- ----------------------------------------------------------------------------
232 -- |--------------------------< find_dt_del_modes >---------------------------|
233 -- ----------------------------------------------------------------------------
234 -- {Start Of Comments}
235 --
236 -- Description:
237 -- This procedure is used to determine what datetrack delete modes are
238 -- allowed as of the effective date for this entity. The procedure will
239 -- return a corresponding Boolean value for each of the delete modes
240 -- available where TRUE indicates that the corresponding delete mode is
241 -- available.
242 --
243 -- Prerequisites:
244 -- None.
245 --
246 -- In Parameters:
247 -- p_effective_date
248 -- Specifies the date at which the datetrack modes will be operated on.
249 -- p_base_key_value
250 -- Specifies the primary key value for this datetrack entity.
251 -- (E.g. For this entity the assignment of the argument would be:
252 -- p_base_key_value = :prtt_reimbmt_rqst_id).
253 --
254 -- Post Success:
255 -- Processing continues.
256 --
257 -- Post Failure:
258 -- Failure might occur if for the specified effective date and primary key
259 -- value a row doesn't exist.
260 --
261 -- Developer Implementation Notes:
262 -- This procedure could require changes if this entity has any sepcific
263 -- delete restrictions.
264 -- For example, this entity might disallow the datetrack delete mode of
265 -- ZAP. To implement this you would have to set and return a Boolean value
266 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
267 --
268 -- Access Status:
269 -- Internal Development Use Only.
270 --
271 -- {End Of Comments}
272 -- ----------------------------------------------------------------------------
273 Procedure find_dt_del_modes
274 (p_effective_date in date,
275 p_base_key_value in number,
276 p_zap out nocopy boolean,
277 p_delete out nocopy boolean,
278 p_future_change out nocopy boolean,
279 p_delete_next_change out nocopy boolean);
280 --
281 -- ----------------------------------------------------------------------------
282 -- |--------------------------< find_dt_upd_modes >---------------------------|
283 -- ----------------------------------------------------------------------------
284 -- {Start Of Comments}
285 --
286 -- Description:
287 -- This procedure is used to determine what datetrack update modes are
288 -- allowed as of the effective date for this entity. The procedure will
289 -- return a corresponding Boolean value for each of the update modes
290 -- available where TRUE indicates that the corresponding update mode
291 -- is available.
292 --
293 -- Prerequisites:
294 -- None.
295 --
296 -- In Parameters:
297 -- p_effective_date
298 -- Specifies the date at which the datetrack modes will be operated on.
299 -- p_base_key_value
300 -- Specifies the primary key value for this datetrack entity.
301 -- (E.g. For this entity the assignment of the argument would be:
302 -- p_base_key_value = :prtt_reimbmt_rqst_id).
303 --
304 -- Post Success:
305 -- Processing continues.
306 --
307 -- Post Failure:
308 -- Failure might occur if for the specified effective date and primary key
309 -- value a row doesn't exist.
313 -- delete restrictions.
310 --
311 -- Developer Implementation Notes:
312 -- This procedure could require changes if this entity has any sepcific
314 -- For example, this entity might disallow the datetrack update mode of
315 -- UPDATE. To implement this you would have to set and return a Boolean
316 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
317 --
318 -- Access Status:
319 -- Internal Development Use Only.
320 --
321 -- {End Of Comments}
322 -- ----------------------------------------------------------------------------
323 Procedure find_dt_upd_modes
324 (p_effective_date in date,
325 p_base_key_value in number,
326 p_correction out nocopy boolean,
327 p_update out nocopy boolean,
328 p_update_override out nocopy boolean,
329 p_update_change_insert out nocopy boolean);
330 --
331 -- ----------------------------------------------------------------------------
332 -- |------------------------< upd_effective_end_date >------------------------|
333 -- ----------------------------------------------------------------------------
334 -- {Start Of Comments}
335 --
336 -- Description:
337 -- This procedure will update the specified datetrack row with the
338 -- specified new effective end date. The object version number is also
339 -- set to the next object version number. DateTrack modes which call
340 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
341 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
342 -- This is an internal datetrack maintenance procedure which should
343 -- not be modified in anyway.
344 --
345 -- Prerequisites:
346 -- None.
347 --
348 -- In Parameters:
349 -- p_new_effective_end_date
350 -- Specifies the new effective end date which will be set for the
351 -- row as of the effective date.
352 -- p_base_key_value
353 -- Specifies the primary key value for this datetrack entity.
354 -- (E.g. For this entity the assignment of the argument would be:
355 -- p_base_key_value = :prtt_reimbmt_rqst_id).
356 --
357 -- Post Success:
358 -- The specified row will be updated with the new effective end date and
359 -- object_version_number.
360 --
361 -- Post Failure:
362 -- Failure might occur if for the specified effective date and primary key
363 -- value a row doesn't exist.
364 --
365 -- Developer Implementation Notes:
366 -- This is an internal datetrack maintenance procedure which should
367 -- not be modified in anyway.
368 --
369 -- Access Status:
370 -- Internal Row Handler Use Only.
371 --
372 -- {End Of Comments}
373 -- ----------------------------------------------------------------------------
374 Procedure upd_effective_end_date
375 (p_effective_date in date,
376 p_base_key_value in number,
377 p_new_effective_end_date in date,
378 p_validation_start_date in date,
379 p_validation_end_date in date,
380 p_object_version_number out nocopy number);
381 --
382 -- ----------------------------------------------------------------------------
383 -- |---------------------------------< lck >----------------------------------|
384 -- ----------------------------------------------------------------------------
385 -- {Start Of Comments}
386 --
387 -- Description:
388 -- The Lck process for datetrack is complicated and comprises of the
389 -- following processing
390 -- The processing steps are as follows:
391 -- 1) The row to be updated or deleted must be locked.
392 -- By locking this row, the g_old_rec record data type is populated.
393 -- 2) If a comment exists the text is selected from hr_comments.
394 -- 3) The datetrack mode is then validated to ensure the operation is
395 -- valid. If the mode is valid the validation start and end dates for
396 -- the mode will be derived and returned. Any required locking is
397 -- completed when the datetrack mode is validated.
398 --
399 -- Prerequisites:
400 -- When attempting to call the lck procedure the object version number,
401 -- primary key, effective date and datetrack mode must be specified.
402 --
403 -- In Parameters:
404 -- p_effective_date
405 -- Specifies the date of the datetrack update operation.
406 -- p_datetrack_mode
407 -- Determines the datetrack update or delete mode.
408 --
409 -- Post Success:
410 -- On successful completion of the Lck process the row to be updated or
411 -- deleted will be locked and selected into the global data structure
412 -- g_old_rec.
413 --
414 -- Post Failure:
415 -- The Lck process can fail for three reasons:
416 -- 1) When attempting to lock the row the row could already be locked by
417 -- another user. This will raise the HR_Api.Object_Locked exception.
418 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
419 -- This error is trapped and reported using the message name
420 -- 'HR_7220_INVALID_PRIMARY_KEY'.
421 -- 3) The row although existing in the HR Schema has a different object
422 -- version number than the object version number specified.
423 -- This error is trapped and reported using the message name
424 -- 'HR_7155_OBJECT_INVALID'.
425 --
426 -- Developer Implementation Notes:
427 -- None.
428 --
429 -- Access Status:
430 -- Internal Development Use Only.
431 --
432 -- {End Of Comments}
436 p_datetrack_mode in varchar2,
433 -- ----------------------------------------------------------------------------
434 Procedure lck
435 (p_effective_date in date,
437 p_prtt_reimbmt_rqst_id in number,
438 p_object_version_number in number,
439 p_validation_start_date out nocopy date,
440 p_validation_end_date out nocopy date);
441 --
442 -- ----------------------------------------------------------------------------
443 -- |-----------------------------< convert_args >-----------------------------|
444 -- ----------------------------------------------------------------------------
445 -- {Start Of Comments}
446 --
447 -- Description:
448 -- This function is used to turn attribute parameters into the record
449 -- structure parameter g_rec_type.
450 --
451 -- Prerequisites:
452 -- This is a private function and can only be called from the ins or upd
453 -- attribute processes.
454 --
455 -- In Parameters:
456 --
457 -- Post Success:
458 -- A returning record structure will be returned.
459 --
460 -- Post Failure:
461 -- No direct error handling is required within this function. Any possible
462 -- errors within this function will be a PL/SQL value error due to conversion
463 -- of datatypes or data lengths.
464 --
465 -- Developer Implementation Notes:
466 -- None.
467 --
468 -- Access Status:
469 -- Internal Row Handler Use Only.
470 --
471 -- {End Of Comments}
472 -- ----------------------------------------------------------------------------
473 Function convert_args
474 (
475 p_prtt_reimbmt_rqst_id in number,
476 p_effective_start_date in date,
477 p_effective_end_date in date,
478 p_incrd_from_dt in date,
479 p_incrd_to_dt in date,
480 p_rqst_num in number,
481 p_rqst_amt in number,
482 p_rqst_amt_uom in varchar2,
483 p_rqst_btch_num in number,
484 p_prtt_reimbmt_rqst_stat_cd in varchar2,
485 p_reimbmt_ctfn_typ_prvdd_cd in varchar2,
486 p_rcrrg_cd in varchar2,
487 p_submitter_person_id in number,
488 p_recipient_person_id in number,
489 p_provider_person_id in number,
490 p_provider_ssn_person_id in number,
491 p_pl_id in number,
492 p_gd_or_svc_typ_id in number,
493 p_contact_relationship_id in number,
494 p_business_group_id in number,
495 p_opt_id in number,
496 p_popl_yr_perd_id_1 in number,
497 p_popl_yr_perd_id_2 in number,
498 p_amt_year1 in number ,
499 p_amt_year2 in number,
500 p_prc_attribute_category in varchar2,
501 p_prc_attribute1 in varchar2,
502 p_prc_attribute2 in varchar2,
503 p_prc_attribute3 in varchar2,
504 p_prc_attribute4 in varchar2,
505 p_prc_attribute5 in varchar2,
506 p_prc_attribute6 in varchar2,
507 p_prc_attribute7 in varchar2,
508 p_prc_attribute8 in varchar2,
509 p_prc_attribute9 in varchar2,
510 p_prc_attribute10 in varchar2,
511 p_prc_attribute11 in varchar2,
512 p_prc_attribute12 in varchar2,
513 p_prc_attribute13 in varchar2,
514 p_prc_attribute14 in varchar2,
515 p_prc_attribute15 in varchar2,
516 p_prc_attribute16 in varchar2,
517 p_prc_attribute17 in varchar2,
518 p_prc_attribute18 in varchar2,
519 p_prc_attribute19 in varchar2,
520 p_prc_attribute20 in varchar2,
521 p_prc_attribute21 in varchar2,
522 p_prc_attribute22 in varchar2,
523 p_prc_attribute23 in varchar2,
524 p_prc_attribute24 in varchar2,
525 p_prc_attribute25 in varchar2,
526 p_prc_attribute26 in varchar2,
527 p_prc_attribute27 in varchar2,
528 p_prc_attribute28 in varchar2,
529 p_prc_attribute29 in varchar2,
530 p_prc_attribute30 in varchar2,
531 p_prtt_enrt_rslt_id in number default null ,
532 p_comment_id in number default null ,
533 p_object_version_number in number ,
534 -- Fide enh
535 p_stat_rsn_cd in varchar2 default null,
536 p_pymt_stat_cd in varchar2 default null,
537 p_pymt_stat_rsn_cd in varchar2 default null,
538 p_stat_ovrdn_flag in varchar2 default null,
539 p_stat_ovrdn_rsn_cd in varchar2 default null,
540 p_stat_prr_to_ovrd in varchar2 default null,
541 p_pymt_stat_ovrdn_flag in varchar2 default null,
542 p_pymt_stat_ovrdn_rsn_cd in varchar2 default null,
543 p_pymt_stat_prr_to_ovrd in varchar2 default null,
544 p_adjmt_flag in varchar2 default null,
545 p_submtd_dt in date default null,
546 p_ttl_rqst_amt in number default null,
547 p_aprvd_for_pymt_amt in number default null,
548 p_pymt_amount in number default null,
549 p_exp_incurd_dt in date default null
550 ) Return g_rec_type;
551 --
552 end ben_prc_shd;