1 Package ben_esw_shd AUTHID CURRENT_USER as
2 /* $Header: beeswrhi.pkh 120.1 2005/06/17 09:38:47 abparekh noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (elig_scre_wtg_id number
10 ,elig_per_id number
11 ,elig_per_opt_id number
12 ,elig_rslt_id number
13 ,per_in_ler_id number
14 ,effective_start_date date
15 ,effective_end_date date
16 ,object_version_number number
17 ,eligy_prfl_id number
18 ,crit_tab_short_name varchar2(30)
19 ,crit_tab_pk_id number
20 ,computed_score number
21 ,benefit_action_id number
22 );
23 --
24 -- ----------------------------------------------------------------------------
25 -- | Global Definitions - Internal Development Use Only |
26 -- ----------------------------------------------------------------------------
27 --
28 g_old_rec g_rec_type; -- Global record definition
29 g_api_dml boolean; -- Global api dml status
30 -- Global table name
31 g_tab_nam constant varchar2(30) := 'BEN_ELIG_SCRE_WTG_F';
32 --
33 -- ----------------------------------------------------------------------------
34 -- |---------------------------< constraint_error >---------------------------|
35 -- ----------------------------------------------------------------------------
36 -- {Start Of Comments}
37 --
38 -- Description:
39 -- This procedure is called when a constraint has been violated (i.e.
40 -- The exception hr_api.check_integrity_violated,
41 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
42 -- hr_api.unique_integrity_violated has been raised).
43 -- The exceptions can only be raised as follows:
44 -- 1) A check constraint can only be violated during an INSERT or UPDATE
45 -- dml operation.
46 -- 2) A parent integrity constraint can only be violated during an
47 -- INSERT or UPDATE dml operation.
48 -- 3) A child integrity constraint can only be violated during an
49 -- DELETE dml operation.
50 -- 4) A unique integrity constraint can only be violated during INSERT or
51 -- UPDATE dml operation.
52 --
53 -- Prerequisites:
54 -- 1) Either hr_api.check_integrity_violated,
55 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
56 -- hr_api.unique_integrity_violated has been raised with the subsequent
57 -- stripping of the constraint name from the generated error message
58 -- text.
59 -- 2) Standalone validation test which corresponds with a constraint error.
60 --
61 -- In Parameter:
62 -- p_constraint_name is in upper format and is just the constraint name
63 -- (e.g. not prefixed by brackets, schema owner etc).
64 --
65 -- Post Success:
66 -- Development dependant.
67 --
68 -- Post Failure:
69 -- Developement dependant.
70 --
71 -- Developer Implementation Notes:
72 -- For each constraint being checked the hr system package failure message
73 -- has been generated as a template only. These system error messages should
74 -- be modified as required (i.e. change the system failure message to a user
75 -- friendly defined error message).
76 --
77 -- Access Status:
78 -- Internal Development Use Only.
79 --
80 -- {End Of Comments}
81 -- ----------------------------------------------------------------------------
82 Procedure constraint_error
83 (p_constraint_name in all_constraints.constraint_name%TYPE);
84 --
85 -- ----------------------------------------------------------------------------
86 -- |-----------------------------< api_updating >-----------------------------|
87 -- ----------------------------------------------------------------------------
88 -- {Start Of Comments}
89 --
90 -- Description:
91 -- This function is used to populate the g_old_rec record with the
92 -- current row from the database for the specified primary key
93 -- provided that the primary key exists and is valid and does not
94 -- already match the current g_old_rec. The function will always return
95 -- a TRUE value if the g_old_rec is populated with the current row.
96 -- A FALSE value will be returned if all of the primary key arguments
97 -- are null.
98 --
99 -- Prerequisites:
100 -- None.
101 --
102 -- In Parameters:
103 --
104 -- Post Success:
105 -- A value of TRUE will be returned indiciating that the g_old_rec
106 -- is current.
107 -- A value of FALSE will be returned if all of the primary key arguments
108 -- have a null value (this indicates that the row has not be inserted into
109 -- the Schema), and therefore could never have a corresponding row.
110 --
111 -- Post Failure:
112 -- A failure can only occur under two circumstances:
113 -- 1) The primary key is invalid (i.e. a row does not exist for the
114 -- specified primary key values).
115 -- 2) If an object_version_number exists but is NOT the same as the current
116 -- g_old_rec value.
117 --
118 -- Developer Implementation Notes:
119 -- None.
120 --
121 -- Access Status:
122 -- Internal Development Use Only.
123 --
124 -- {End Of Comments}
125 -- ----------------------------------------------------------------------------
126 Function api_updating
127 (p_effective_date in date
128 ,p_elig_scre_wtg_id in number
129 ,p_object_version_number in number
130 ) Return Boolean;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |---------------------------< find_dt_upd_modes >--------------------------|
134 -- ----------------------------------------------------------------------------
135 -- {Start Of Comments}
136 --
137 -- Description:
138 -- This procedure is used to determine what datetrack update modes are
139 -- allowed as of the effective date for this entity. The procedure will
140 -- return a corresponding Boolean value for each of the update modes
141 -- available where TRUE indicates that the corresponding update mode
142 -- is available.
143 --
144 -- Prerequisites:
145 -- None.
146 --
147 -- In Parameters:
148 -- p_effective_date
149 -- Specifies the date at which the datetrack modes will be operated on.
150 -- p_base_key_value
151 -- Specifies the primary key value for this datetrack entity.
152 -- (E.g. For this entity the assignment of the argument would be:
153 -- p_base_key_value = :elig_scre_wtg_id).
154 --
155 -- Post Success:
156 -- Processing continues.
157 --
158 -- Post Failure:
159 -- Failure might occur if for the specified effective date and primary key
160 -- value a row doesn't exist.
161 --
162 -- Developer Implementation Notes:
163 -- This procedure could require changes if this entity has any sepcific
164 -- delete restrictions.
165 -- For example, this entity might disallow the datetrack update mode of
166 -- UPDATE. To implement this you would have to set and return a Boolean
167 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
168 --
169 -- Access Status:
170 -- Internal Development Use Only.
171 --
172 -- {End Of Comments}
173 -- ----------------------------------------------------------------------------
174 Procedure find_dt_upd_modes
175 (p_effective_date in date
176 ,p_base_key_value in number
177 ,p_correction out nocopy boolean
178 ,p_update out nocopy boolean
179 ,p_update_override out nocopy boolean
180 ,p_update_change_insert out nocopy boolean
181 );
182 --
183 -- ----------------------------------------------------------------------------
184 -- |---------------------------< find_dt_del_modes >--------------------------|
185 -- ----------------------------------------------------------------------------
186 -- {Start Of Comments}
187 --
188 -- Description:
189 -- This procedure is used to determine what datetrack delete modes are
190 -- allowed as of the effective date for this entity. The procedure will
191 -- return a corresponding Boolean value for each of the delete modes
192 -- available where TRUE indicates that the corresponding delete mode is
193 -- available.
194 --
195 -- Prerequisites:
196 -- None.
197 --
198 -- In Parameters:
199 -- p_effective_date
200 -- Specifies the date at which the datetrack modes will be operated on.
201 -- p_base_key_value
202 -- Specifies the primary key value for this datetrack entity.
203 -- (E.g. For this entity the assignment of the argument would be:
204 -- p_base_key_value = :elig_scre_wtg_id).
205 --
206 -- Post Success:
207 -- Processing continues.
208 --
209 -- Post Failure:
210 -- Failure might occur if for the specified effective date and primary key
211 -- value a row doesn't exist.
212 --
213 -- Developer Implementation Notes:
214 -- This procedure could require changes if this entity has any sepcific
215 -- delete restrictions.
216 -- For example, this entity might disallow the datetrack delete mode of
217 -- ZAP. To implement this you would have to set and return a Boolean value
218 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
219 --
220 -- Access Status:
221 -- Internal Development Use Only.
222 --
223 -- {End Of Comments}
224 -- ----------------------------------------------------------------------------
225 Procedure find_dt_del_modes
226 (p_effective_date in date
227 ,p_base_key_value in number
228 ,p_zap out nocopy boolean
229 ,p_delete out nocopy boolean
230 ,p_future_change out nocopy boolean
231 ,p_delete_next_change out nocopy boolean
232 );
233 --
234 -- ----------------------------------------------------------------------------
235 -- |-----------------------< upd_effective_end_date >-------------------------|
236 -- ----------------------------------------------------------------------------
237 -- {Start Of Comments}
238 --
239 -- Description:
240 -- This procedure will update the specified datetrack row with the
241 -- specified new effective end date. The object version number is also
242 -- set to the next object version number. DateTrack modes which call
243 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
244 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
245 -- This is an internal datetrack maintenance procedure which should
246 -- not be modified in anyway.
247 --
248 -- Prerequisites:
249 -- None.
250 --
251 -- In Parameters:
252 -- p_new_effective_end_date
253 -- Specifies the new effective end date which will be set for the
254 -- row as of the effective date.
255 -- p_base_key_value
256 -- Specifies the primary key value for this datetrack entity.
257 -- (E.g. For this entity the assignment of the argument would be:
258 -- p_base_key_value = :elig_scre_wtg_id).
259 --
260 -- Post Success:
261 -- The specified row will be updated with the new effective end date and
262 -- object_version_number.
263 --
264 -- Post Failure:
265 -- Failure might occur if for the specified effective date and primary key
266 -- value a row doesn't exist.
267 --
268 -- Developer Implementation Notes:
269 -- This is an internal datetrack maintenance procedure which should
270 -- not be modified in anyway.
271 --
272 -- Access Status:
273 -- Internal Row Handler Use Only.
274 --
275 -- {End Of Comments}
276 -- ----------------------------------------------------------------------------
277 Procedure upd_effective_end_date
278 (p_effective_date in date
279 ,p_base_key_value in number
280 ,p_new_effective_end_date in date
281 ,p_validation_start_date in date
282 ,p_validation_end_date in date
283 ,p_object_version_number out nocopy number
284 );
285 --
286 -- ----------------------------------------------------------------------------
287 -- |---------------------------------< lck >----------------------------------|
288 -- ----------------------------------------------------------------------------
289 -- {Start Of Comments}
290 --
291 -- Description:
292 -- The Lck process for datetrack is complicated and comprises of the
293 -- following processing
294 -- The processing steps are as follows:
295 -- 1) The row to be updated or deleted must be locked.
296 -- By locking this row, the g_old_rec record data type is populated.
297 -- 2) If a comment exists the text is selected from hr_comments.
298 -- 3) The datetrack mode is then validated to ensure the operation is
299 -- valid. If the mode is valid the validation start and end dates for
300 -- the mode will be derived and returned. Any required locking is
301 -- completed when the datetrack mode is validated.
302 --
303 -- Prerequisites:
304 -- When attempting to call the lck procedure the object version number,
305 -- primary key, effective date and datetrack mode must be specified.
306 --
307 -- In Parameters:
308 -- p_effective_date
309 -- Specifies the date of the datetrack update operation.
310 -- p_datetrack_mode
311 -- Determines the datetrack update or delete mode.
312 --
313 -- Post Success:
314 -- On successful completion of the Lck process the row to be updated or
315 -- deleted will be locked and selected into the global data structure
316 -- g_old_rec.
317 --
318 -- Post Failure:
319 -- The Lck process can fail for three reasons:
320 -- 1) When attempting to lock the row the row could already be locked by
321 -- another user. This will raise the HR_Api.Object_Locked exception.
322 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
323 -- This error is trapped and reported using the message name
324 -- 'HR_7220_INVALID_PRIMARY_KEY'.
325 -- 3) The row although existing in the HR Schema has a different object
326 -- version number than the object version number specified.
327 -- This error is trapped and reported using the message name
328 -- 'HR_7155_OBJECT_INVALID'.
329 --
330 -- Developer Implementation Notes:
331 -- None.
332 --
333 -- Access Status:
334 -- Internal Development Use Only.
335 --
336 -- {End Of Comments}
337 -- ----------------------------------------------------------------------------
338 Procedure lck
339 (p_effective_date in date
340 ,p_datetrack_mode in varchar2
341 ,p_elig_scre_wtg_id in number
342 ,p_object_version_number in number
343 ,p_validation_start_date out nocopy date
344 ,p_validation_end_date out nocopy date
345 );
346 --
347 -- ----------------------------------------------------------------------------
348 -- |-----------------------------< convert_args >-----------------------------|
349 -- ----------------------------------------------------------------------------
350 -- {Start Of Comments}
351 --
352 -- Description:
353 -- This function is used to turn attribute parameters into the record
354 -- structure parameter g_rec_type.
355 --
356 -- Prerequisites:
357 -- This is a private function and can only be called from the ins or upd
358 -- attribute processes.
359 --
360 -- In Parameters:
361 --
362 -- Post Success:
363 -- A returning record structure will be returned.
364 --
365 -- Post Failure:
366 -- No direct error handling is required within this function. Any possible
367 -- errors within this function will be a PL/SQL value error due to
368 -- conversion of datatypes or data lengths.
369 --
370 -- Developer Implementation Notes:
371 -- None.
372 --
373 -- Access Status:
374 -- Internal Row Handler Use Only.
375 --
376 -- {End Of Comments}
377 -- ----------------------------------------------------------------------------
378 Function convert_args
379 (p_elig_scre_wtg_id in number
380 ,p_elig_per_id in number
381 ,p_elig_per_opt_id in number
385 ,p_effective_end_date in date
382 ,p_elig_rslt_id in number
383 ,p_per_in_ler_id in number
384 ,p_effective_start_date in date
386 ,p_object_version_number in number
387 ,p_eligy_prfl_id in number
388 ,p_crit_tab_short_name in varchar2
389 ,p_crit_tab_pk_id in number
390 ,p_computed_score in number
391 ,p_benefit_action_id in number
392 )
393 Return g_rec_type;
394 --
395 end ben_esw_shd;