1 Package ben_cop_shd as
2 /* $Header: becoprhi.pkh 120.0 2005/05/28 01:10:23 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 oipl_id number(15),
11 effective_start_date date,
12 effective_end_date date,
13 ivr_ident varchar2(90), -- UTF8 Change Bug 2254683
14 url_ref_name varchar2(240),
15 opt_id number(15),
16 business_group_id number(15),
17 pl_id number(15),
18 ordr_num number(15),
19 rqd_perd_enrt_nenrt_val number(15),
20 dflt_flag varchar2(30),
21 actl_prem_id number(15),
22 mndtry_flag varchar2(30),
23 oipl_stat_cd varchar2(30),
24 pcp_dsgn_cd varchar2(30),
25 pcp_dpnt_dsgn_cd varchar2(30),
26 rqd_perd_enrt_nenrt_uom varchar2(30),
27 elig_apls_flag varchar2(30),
28 dflt_enrt_det_rl number(15),
29 trk_inelig_per_flag varchar2(30),
30 drvbl_fctr_prtn_elig_flag varchar2(30),
31 mndtry_rl number(15),
32 rqd_perd_enrt_nenrt_rl number(15),
33 dflt_enrt_cd varchar2(30),
34 prtn_elig_ovrid_alwd_flag varchar2(30),
35 drvbl_fctr_apls_rts_flag varchar2(30),
36 per_cvrd_cd varchar2(30),
37 postelcn_edit_rl number(15),
38 vrfy_fmly_mmbr_cd varchar2(30),
39 vrfy_fmly_mmbr_rl number(15),
40 enrt_cd varchar2(30),
41 enrt_rl number(15),
42 auto_enrt_flag varchar2(30),
43 auto_enrt_mthd_rl number(15),
44 short_name varchar2(30), --FHR
45 short_code varchar2(30), --FHR
46 legislation_code varchar2(30),
47 legislation_subgroup varchar2(30),
48 hidden_flag varchar2(30), --CWB Itemization
49 susp_if_ctfn_not_prvd_flag varchar2(30),
50 ctfn_determine_cd varchar2(30),
51 cop_attribute_category varchar2(30),
52 cop_attribute1 varchar2(150),
53 cop_attribute2 varchar2(150),
54 cop_attribute3 varchar2(150),
55 cop_attribute4 varchar2(150),
56 cop_attribute5 varchar2(150),
57 cop_attribute6 varchar2(150),
58 cop_attribute7 varchar2(150),
59 cop_attribute8 varchar2(150),
60 cop_attribute9 varchar2(150),
61 cop_attribute10 varchar2(150),
62 cop_attribute11 varchar2(150),
63 cop_attribute12 varchar2(150),
64 cop_attribute13 varchar2(150),
65 cop_attribute14 varchar2(150),
66 cop_attribute15 varchar2(150),
67 cop_attribute16 varchar2(150),
68 cop_attribute17 varchar2(150),
69 cop_attribute18 varchar2(150),
70 cop_attribute19 varchar2(150),
71 cop_attribute20 varchar2(150),
72 cop_attribute21 varchar2(150),
73 cop_attribute22 varchar2(150),
74 cop_attribute23 varchar2(150),
75 cop_attribute24 varchar2(150),
76 cop_attribute25 varchar2(150),
77 cop_attribute26 varchar2(150),
78 cop_attribute27 varchar2(150),
79 cop_attribute28 varchar2(150),
80 cop_attribute29 varchar2(150),
81 cop_attribute30 varchar2(150),
82 object_version_number number(9)
83 );
84 --
85 -- ----------------------------------------------------------------------------
86 -- | Global Definitions - Internal Development Use Only |
87 -- ----------------------------------------------------------------------------
88 --
89 g_old_rec g_rec_type; -- Global record definition
90 g_api_dml boolean; -- Global api dml status
91 --
92 -- ----------------------------------------------------------------------------
93 -- |------------------------< return_api_dml_status >-------------------------|
94 -- ----------------------------------------------------------------------------
95 -- {Start Of Comments}
96 --
97 -- Description:
98 -- This function will return the current g_api_dml private global
99 -- boolean status.
100 -- The g_api_dml status determines if at the time of the function
101 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
102 -- is being issued from within an api.
103 -- If the status is TRUE then a dml statement is being issued from
104 -- within this entity api.
105 -- This function is primarily to support database triggers which
106 -- need to maintain the object_version_number for non-supported
107 -- dml statements (i.e. dml statement issued outside of the api layer).
108 --
109 -- Prerequisites:
110 -- None.
111 --
112 -- In Parameters:
113 -- None.
114 --
115 -- Post Success:
116 -- Processing continues.
117 -- If the function returns a TRUE value then, dml is being executed from
118 -- within this api.
119 --
120 -- Post Failure:
121 -- None.
122 --
123 -- Access Status:
124 -- Internal Row Handler Use Only.
125 --
126 -- {End Of Comments}
127 -- ----------------------------------------------------------------------------
128 Function return_api_dml_status Return Boolean;
129 --
130 -- ----------------------------------------------------------------------------
131 -- |---------------------------< constraint_error >---------------------------|
132 -- ----------------------------------------------------------------------------
133 -- {Start Of Comments}
134 --
135 -- Description:
136 -- This procedure is called when a constraint has been violated (i.e.
137 -- The exception hr_api.check_integrity_violated,
138 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
139 -- hr_api.unique_integrity_violated has been raised).
140 -- The exceptions can only be raised as follows:
141 -- 1) A check constraint can only be violated during an INSERT or UPDATE
142 -- dml operation.
143 -- 2) A parent integrity constraint can only be violated during an
144 -- INSERT or UPDATE dml operation.
145 -- 3) A child integrity constraint can only be violated during an
146 -- DELETE dml operation.
147 -- 4) A unique integrity constraint can only be violated during INSERT or
148 -- UPDATE dml operation.
149 --
150 -- Prerequisites:
151 -- 1) Either hr_api.check_integrity_violated,
152 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
153 -- hr_api.unique_integrity_violated has been raised with the subsequent
154 -- stripping of the constraint name from the generated error message
155 -- text.
156 -- 2) Standalone validation test which corresponds with a constraint error.
157 --
158 -- In Parameter:
159 -- p_constraint_name is in upper format and is just the constraint name
160 -- (e.g. not prefixed by brackets, schema owner etc).
161 --
162 -- Post Success:
163 -- Development dependant.
164 --
165 -- Post Failure:
166 -- Developement dependant.
167 --
168 -- Developer Implementation Notes:
169 -- For each constraint being checked the hr system package failure message
170 -- has been generated as a template only. These system error messages should
171 -- be modified as required (i.e. change the system failure message to a user
172 -- friendly defined error message).
173 --
174 -- Access Status:
175 -- Internal Development Use Only.
176 --
177 -- {End Of Comments}
178 -- ----------------------------------------------------------------------------
179 Procedure constraint_error
180 (p_constraint_name in all_constraints.constraint_name%TYPE);
181 --
182 -- ----------------------------------------------------------------------------
183 -- |-----------------------------< api_updating >-----------------------------|
184 -- ----------------------------------------------------------------------------
185 -- {Start Of Comments}
186 --
187 -- Description:
188 -- This function is used to populate the g_old_rec record with the current
189 -- row from the database for the specified primary key provided that the
190 -- primary key exists, and is valid, and does not already match the current
191 -- g_old_rec.
192 -- The function will always return a TRUE value if the g_old_rec is
193 -- populated with the current row. A FALSE value will be returned if all of
194 -- the primary key arguments are null.
195 --
196 -- Prerequisites:
197 -- None.
198 --
199 -- In Parameters:
200 --
201 -- Post Success:
202 -- A value of TRUE will be returned indiciating that the g_old_rec is
203 -- current.
204 -- A value of FALSE will be returned if all of the primary key arguments
205 -- have a null value (this indicates that the row has not be inserted into
206 -- the Schema), and therefore could never have a corresponding row.
207 --
208 -- Post Failure:
209 -- A failure can only occur under two circumstances:
210 -- 1) The primary key is invalid (i.e. a row does not exist for the
211 -- specified primary key values).
212 -- 2) If an object_version_number exists but is NOT the same as the current
213 -- g_old_rec value.
214 --
215 -- Developer Implementation Notes:
216 -- None.
217 --
218 -- Access Status:
219 -- Internal Development Use Only.
220 --
221 -- {End Of Comments}
222 -- ----------------------------------------------------------------------------
223 Function api_updating
224 (p_effective_date in date,
225 p_oipl_id in number,
226 p_object_version_number in number
227 ) Return Boolean;
228 --
229 -- ----------------------------------------------------------------------------
230 -- |--------------------------< find_dt_del_modes >---------------------------|
231 -- ----------------------------------------------------------------------------
232 -- {Start Of Comments}
233 --
234 -- Description:
235 -- This procedure is used to determine what datetrack delete modes are
236 -- allowed as of the effective date for this entity. The procedure will
237 -- return a corresponding Boolean value for each of the delete modes
238 -- available where TRUE indicates that the corresponding delete mode is
239 -- available.
240 --
241 -- Prerequisites:
242 -- None.
243 --
244 -- In Parameters:
245 -- p_effective_date
246 -- Specifies the date at which the datetrack modes will be operated on.
247 -- p_base_key_value
248 -- Specifies the primary key value for this datetrack entity.
249 -- (E.g. For this entity the assignment of the argument would be:
250 -- p_base_key_value = :oipl_id).
251 --
252 -- Post Success:
253 -- Processing continues.
254 --
255 -- Post Failure:
256 -- Failure might occur if for the specified effective date and primary key
257 -- value a row doesn't exist.
258 --
259 -- Developer Implementation Notes:
260 -- This procedure could require changes if this entity has any sepcific
261 -- delete restrictions.
262 -- For example, this entity might disallow the datetrack delete mode of
263 -- ZAP. To implement this you would have to set and return a Boolean value
264 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
265 --
266 -- Access Status:
267 -- Internal Development Use Only.
268 --
269 -- {End Of Comments}
270 -- ----------------------------------------------------------------------------
271 Procedure find_dt_del_modes
272 (p_effective_date in date,
273 p_base_key_value in number,
274 p_zap out nocopy boolean,
275 p_delete out nocopy boolean,
276 p_future_change out nocopy boolean,
277 p_delete_next_change out nocopy boolean);
278 --
279 -- ----------------------------------------------------------------------------
280 -- |--------------------------< find_dt_upd_modes >---------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This procedure is used to determine what datetrack update modes are
286 -- allowed as of the effective date for this entity. The procedure will
287 -- return a corresponding Boolean value for each of the update modes
288 -- available where TRUE indicates that the corresponding update mode
289 -- is available.
290 --
291 -- Prerequisites:
292 -- None.
293 --
294 -- In Parameters:
295 -- p_effective_date
296 -- Specifies the date at which the datetrack modes will be operated on.
297 -- p_base_key_value
298 -- Specifies the primary key value for this datetrack entity.
299 -- (E.g. For this entity the assignment of the argument would be:
300 -- p_base_key_value = :oipl_id).
301 --
302 -- Post Success:
303 -- Processing continues.
304 --
305 -- Post Failure:
306 -- Failure might occur if for the specified effective date and primary key
307 -- value a row doesn't exist.
308 --
309 -- Developer Implementation Notes:
310 -- This procedure could require changes if this entity has any sepcific
311 -- delete restrictions.
312 -- For example, this entity might disallow the datetrack update mode of
313 -- UPDATE. To implement this you would have to set and return a Boolean
314 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
315 --
316 -- Access Status:
317 -- Internal Development Use Only.
318 --
319 -- {End Of Comments}
320 -- ----------------------------------------------------------------------------
321 Procedure find_dt_upd_modes
322 (p_effective_date in date,
323 p_base_key_value in number,
324 p_correction out nocopy boolean,
325 p_update out nocopy boolean,
326 p_update_override out nocopy boolean,
327 p_update_change_insert out nocopy boolean);
328 --
329 -- ----------------------------------------------------------------------------
333 --
330 -- |------------------------< upd_effective_end_date >------------------------|
331 -- ----------------------------------------------------------------------------
332 -- {Start Of Comments}
334 -- Description:
335 -- This procedure will update the specified datetrack row with the
336 -- specified new effective end date. The object version number is also
337 -- set to the next object version number. DateTrack modes which call
338 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
339 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
340 -- This is an internal datetrack maintenance procedure which should
341 -- not be modified in anyway.
342 --
343 -- Prerequisites:
344 -- None.
345 --
346 -- In Parameters:
347 -- p_new_effective_end_date
348 -- Specifies the new effective end date which will be set for the
349 -- row as of the effective date.
350 -- p_base_key_value
351 -- Specifies the primary key value for this datetrack entity.
352 -- (E.g. For this entity the assignment of the argument would be:
353 -- p_base_key_value = :oipl_id).
354 --
355 -- Post Success:
356 -- The specified row will be updated with the new effective end date and
357 -- object_version_number.
358 --
359 -- Post Failure:
360 -- Failure might occur if for the specified effective date and primary key
361 -- value a row doesn't exist.
362 --
363 -- Developer Implementation Notes:
364 -- This is an internal datetrack maintenance procedure which should
365 -- not be modified in anyway.
366 --
367 -- Access Status:
368 -- Internal Row Handler Use Only.
369 --
370 -- {End Of Comments}
371 -- ----------------------------------------------------------------------------
372 Procedure upd_effective_end_date
373 (p_effective_date in date,
374 p_base_key_value in number,
375 p_new_effective_end_date in date,
376 p_validation_start_date in date,
377 p_validation_end_date in date,
378 p_object_version_number out nocopy number);
379 --
380 -- ----------------------------------------------------------------------------
381 -- |---------------------------------< lck >----------------------------------|
382 -- ----------------------------------------------------------------------------
383 -- {Start Of Comments}
384 --
385 -- Description:
386 -- The Lck process for datetrack is complicated and comprises of the
387 -- following processing
388 -- The processing steps are as follows:
389 -- 1) The row to be updated or deleted must be locked.
390 -- By locking this row, the g_old_rec record data type is populated.
391 -- 2) If a comment exists the text is selected from hr_comments.
392 -- 3) The datetrack mode is then validated to ensure the operation is
393 -- valid. If the mode is valid the validation start and end dates for
394 -- the mode will be derived and returned. Any required locking is
395 -- completed when the datetrack mode is validated.
396 --
397 -- Prerequisites:
398 -- When attempting to call the lck procedure the object version number,
399 -- primary key, effective date and datetrack mode must be specified.
400 --
401 -- In Parameters:
402 -- p_effective_date
403 -- Specifies the date of the datetrack update operation.
404 -- p_datetrack_mode
405 -- Determines the datetrack update or delete mode.
406 --
407 -- Post Success:
408 -- On successful completion of the Lck process the row to be updated or
409 -- deleted will be locked and selected into the global data structure
410 -- g_old_rec.
411 --
412 -- Post Failure:
413 -- The Lck process can fail for three reasons:
414 -- 1) When attempting to lock the row the row could already be locked by
415 -- another user. This will raise the HR_Api.Object_Locked exception.
416 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
417 -- This error is trapped and reported using the message name
418 -- 'HR_7220_INVALID_PRIMARY_KEY'.
419 -- 3) The row although existing in the HR Schema has a different object
420 -- version number than the object version number specified.
421 -- This error is trapped and reported using the message name
422 -- 'HR_7155_OBJECT_INVALID'.
423 --
424 -- Developer Implementation Notes:
425 -- None.
426 --
427 -- Access Status:
428 -- Internal Development Use Only.
429 --
430 -- {End Of Comments}
431 -- ----------------------------------------------------------------------------
432 Procedure lck
433 (p_effective_date in date,
434 p_datetrack_mode in varchar2,
435 p_oipl_id in number,
436 p_object_version_number in number,
437 p_validation_start_date out nocopy date,
438 p_validation_end_date out nocopy date);
439 --
440 -- ----------------------------------------------------------------------------
441 -- |-----------------------------< convert_args >-----------------------------|
442 -- ----------------------------------------------------------------------------
443 -- {Start Of Comments}
444 --
445 -- Description:
446 -- This function is used to turn attribute parameters into the record
447 -- structure parameter g_rec_type.
448 --
449 -- Prerequisites:
450 -- This is a private function and can only be called from the ins or upd
451 -- attribute processes.
452 --
453 -- In Parameters:
454 --
455 -- Post Success:
459 -- No direct error handling is required within this function. Any possible
456 -- A returning record structure will be returned.
457 --
458 -- Post Failure:
460 -- errors within this function will be a PL/SQL value error due to conversion
461 -- of datatypes or data lengths.
462 --
463 -- Developer Implementation Notes:
464 -- None.
465 --
466 -- Access Status:
467 -- Internal Row Handler Use Only.
468 --
469 -- {End Of Comments}
470 -- ----------------------------------------------------------------------------
471 Function convert_args
472 (
473 p_oipl_id in number,
474 p_effective_start_date in date,
475 p_effective_end_date in date,
476 p_ivr_ident in varchar2,
477 p_url_ref_name in varchar2,
478 p_opt_id in number,
479 p_business_group_id in number,
480 p_pl_id in number,
481 p_ordr_num in number,
482 p_rqd_perd_enrt_nenrt_val in number,
483 p_dflt_flag in varchar2,
484 p_actl_prem_id in number,
485 p_mndtry_flag in varchar2,
486 p_oipl_stat_cd in varchar2,
487 p_pcp_dsgn_cd in varchar2,
488 p_pcp_dpnt_dsgn_cd in varchar2,
489 p_rqd_perd_enrt_nenrt_uom in varchar2,
490 p_elig_apls_flag in varchar2,
491 p_dflt_enrt_det_rl in number,
492 p_trk_inelig_per_flag in varchar2,
493 p_drvbl_fctr_prtn_elig_flag in varchar2,
494 p_mndtry_rl in number,
495 p_rqd_perd_enrt_nenrt_rl in number,
496 p_dflt_enrt_cd in varchar2,
497 p_prtn_elig_ovrid_alwd_flag in varchar2,
498 p_drvbl_fctr_apls_rts_flag in varchar2,
499 p_per_cvrd_cd in varchar2,
500 p_postelcn_edit_rl in number,
501 p_vrfy_fmly_mmbr_cd in varchar2,
502 p_vrfy_fmly_mmbr_rl in number,
503 p_enrt_cd in varchar2,
504 p_enrt_rl in number,
505 p_auto_enrt_flag in varchar2,
506 p_auto_enrt_mthd_rl in number,
507 p_short_name in varchar2, --FHR
508 p_short_code in varchar2, --FHR
509 p_legislation_code in varchar2,
510 p_legislation_subgroup in varchar2,
511 p_hidden_flag in varchar2, --CWB Itemization
512 p_susp_if_ctfn_not_prvd_flag in varchar2 default 'Y' ,
513 p_ctfn_determine_cd in varchar2 default null ,
514 p_cop_attribute_category in varchar2,
515 p_cop_attribute1 in varchar2,
516 p_cop_attribute2 in varchar2,
517 p_cop_attribute3 in varchar2,
518 p_cop_attribute4 in varchar2,
519 p_cop_attribute5 in varchar2,
520 p_cop_attribute6 in varchar2,
521 p_cop_attribute7 in varchar2,
522 p_cop_attribute8 in varchar2,
523 p_cop_attribute9 in varchar2,
524 p_cop_attribute10 in varchar2,
525 p_cop_attribute11 in varchar2,
526 p_cop_attribute12 in varchar2,
527 p_cop_attribute13 in varchar2,
528 p_cop_attribute14 in varchar2,
529 p_cop_attribute15 in varchar2,
530 p_cop_attribute16 in varchar2,
531 p_cop_attribute17 in varchar2,
532 p_cop_attribute18 in varchar2,
533 p_cop_attribute19 in varchar2,
534 p_cop_attribute20 in varchar2,
535 p_cop_attribute21 in varchar2,
536 p_cop_attribute22 in varchar2,
537 p_cop_attribute23 in varchar2,
538 p_cop_attribute24 in varchar2,
539 p_cop_attribute25 in varchar2,
543 p_cop_attribute29 in varchar2,
540 p_cop_attribute26 in varchar2,
541 p_cop_attribute27 in varchar2,
542 p_cop_attribute28 in varchar2,
544 p_cop_attribute30 in varchar2,
545 p_object_version_number in number
546 )
547 Return g_rec_type;
548 --
549 end ben_cop_shd;