1 Package pay_ivl_shd as
2 /* $Header: pyivlrhi.pkh 120.0 2005/05/29 06:04:54 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (input_value_id number(9)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,element_type_id number(9)
13 ,lookup_type varchar2(30)
14 ,business_group_id number(15)
15 ,legislation_code varchar2(30)
16 ,formula_id number(9)
17 ,value_set_id number(10)
18 ,display_sequence number(9) -- Increased length
19 ,generate_db_items_flag varchar2(30)
20 ,hot_default_flag varchar2(30)
21 ,mandatory_flag varchar2(9) -- Increased length
22 ,name varchar2(80)
23 ,uom varchar2(30)
24 ,default_value varchar2(60)
25 ,legislation_subgroup varchar2(30)
26 ,max_value varchar2(60)
27 ,min_value varchar2(60)
28 ,warning_or_error varchar2(30)
29 ,object_version_number number(9)
30 );
31 --
32 -- ----------------------------------------------------------------------------
33 -- | Global Definitions - Internal Development Use Only |
34 -- ----------------------------------------------------------------------------
35 --
36 g_old_rec g_rec_type; -- Global record definition
37 -- Global table name
38 g_tab_nam constant varchar2(30) := 'PAY_INPUT_VALUES_F';
39 g_api_dml boolean; -- Global api dml status
40 --
41 -- ----------------------------------------------------------------------------
42 -- |------------------------< return_api_dml_status >-------------------------|
43 -- ----------------------------------------------------------------------------
44 -- {Start Of Comments}
45 --
46 -- Description:
47 -- This function will return the current g_api_dml private global
48 -- boolean status.
49 -- The g_api_dml status determines if at the time of the function
50 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
51 -- is being issued from within an api.
52 -- If the status is TRUE then a dml statement is being issued from
53 -- within this entity api.
54 -- This function is primarily to support database triggers which
55 -- need to maintain the object_version_number for non-supported
56 -- dml statements (i.e. dml statement issued outside of the api layer).
57 --
58 -- Prerequisites:
59 -- None.
60 --
61 -- In Parameters:
62 -- None.
63 --
64 -- Post Success:
65 -- Processing continues.
66 -- If the function returns a TRUE value then, dml is being executed from
67 -- within this api.
68 --
69 -- Post Failure:
70 -- None.
71 --
72 -- Access Status:
73 -- Internal Row Handler Use Only.
74 --
75 -- {End Of Comments}
76 -- ----------------------------------------------------------------------------
77 Function return_api_dml_status Return Boolean;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |---------------------------< constraint_error >---------------------------|
81 -- ----------------------------------------------------------------------------
82 -- {Start Of Comments}
83 --
84 -- Description:
85 -- This procedure is called when a constraint has been violated (i.e.
86 -- The exception hr_api.check_integrity_violated,
87 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
88 -- hr_api.unique_integrity_violated has been raised).
89 -- The exceptions can only be raised as follows:
90 -- 1) A check constraint can only be violated during an INSERT or UPDATE
91 -- dml operation.
92 -- 2) A parent integrity constraint can only be violated during an
93 -- INSERT or UPDATE dml operation.
94 -- 3) A child integrity constraint can only be violated during an
95 -- DELETE dml operation.
96 -- 4) A unique integrity constraint can only be violated during INSERT or
97 -- UPDATE dml operation.
98 --
99 -- Prerequisites:
100 -- 1) Either hr_api.check_integrity_violated,
101 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
102 -- hr_api.unique_integrity_violated has been raised with the subsequent
103 -- stripping of the constraint name from the generated error message
104 -- text.
105 -- 2) Standalone validation test which corresponds with a constraint error.
106 --
107 -- In Parameter:
108 -- p_constraint_name is in upper format and is just the constraint name
109 -- (e.g. not prefixed by brackets, schema owner etc).
110 --
111 -- Post Success:
112 -- Development dependant.
113 --
114 -- Post Failure:
115 -- Developement dependant.
116 --
117 -- Developer Implementation Notes:
118 -- For each constraint being checked the hr system package failure message
119 -- has been generated as a template only. These system error messages should
120 -- be modified as required (i.e. change the system failure message to a user
121 -- friendly defined error message).
122 --
123 -- Access Status:
124 -- Internal Development Use Only.
125 --
126 -- {End Of Comments}
127 -- ----------------------------------------------------------------------------
128 Procedure constraint_error
129 (p_constraint_name in all_constraints.constraint_name%TYPE);
130 --
131 -- ----------------------------------------------------------------------------
132 -- |-----------------------------< api_updating >-----------------------------|
133 -- ----------------------------------------------------------------------------
134 -- {Start Of Comments}
135 --
136 -- Description:
137 -- This function is used to populate the g_old_rec record with the
138 -- current row from the database for the specified primary key
139 -- provided that the primary key exists and is valid and does not
140 -- already match the current g_old_rec. The function will always return
141 -- a TRUE value if the g_old_rec is populated with the current row.
142 -- A FALSE value will be returned if all of the primary key arguments
143 -- are null.
144 --
145 -- Prerequisites:
146 -- None.
147 --
148 -- In Parameters:
149 --
150 -- Post Success:
151 -- A value of TRUE will be returned indiciating that the g_old_rec
152 -- is current.
153 -- A value of FALSE will be returned if all of the primary key arguments
154 -- have a null value (this indicates that the row has not be inserted into
155 -- the Schema), and therefore could never have a corresponding row.
156 --
157 -- Post Failure:
158 -- A failure can only occur under two circumstances:
159 -- 1) The primary key is invalid (i.e. a row does not exist for the
160 -- specified primary key values).
161 -- 2) If an object_version_number exists but is NOT the same as the current
162 -- g_old_rec value.
163 --
164 -- Developer Implementation Notes:
165 -- None.
166 --
167 -- Access Status:
168 -- Internal Development Use Only.
169 --
170 -- {End Of Comments}
171 -- ----------------------------------------------------------------------------
172 Function api_updating
173 (p_effective_date in date
174 ,p_input_value_id in number
175 ,p_object_version_number in number
176 ) Return Boolean;
177 --
178 -- ----------------------------------------------------------------------------
179 -- |---------------------------< find_dt_upd_modes >--------------------------|
180 -- ----------------------------------------------------------------------------
181 -- {Start Of Comments}
182 --
183 -- Description:
184 -- This procedure is used to determine what datetrack update modes are
185 -- allowed as of the effective date for this entity. The procedure will
186 -- return a corresponding Boolean value for each of the update modes
187 -- available where TRUE indicates that the corresponding update mode
188 -- is available.
189 --
190 -- Prerequisites:
191 -- None.
192 --
193 -- In Parameters:
194 -- p_effective_date
195 -- Specifies the date at which the datetrack modes will be operated on.
196 -- p_base_key_value
197 -- Specifies the primary key value for this datetrack entity.
198 -- (E.g. For this entity the assignment of the argument would be:
199 -- p_base_key_value = :input_value_id).
200 --
201 -- Post Success:
202 -- Processing continues.
203 --
204 -- Post Failure:
205 -- Failure might occur if for the specified effective date and primary key
206 -- value a row doesn't exist.
207 --
208 -- Developer Implementation Notes:
209 -- This procedure could require changes if this entity has any sepcific
210 -- delete restrictions.
211 -- For example, this entity might disallow the datetrack update mode of
212 -- UPDATE. To implement this you would have to set and return a Boolean
213 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
214 --
215 -- Access Status:
216 -- Internal Development Use Only.
217 --
218 -- {End Of Comments}
219 -- ----------------------------------------------------------------------------
220 Procedure find_dt_upd_modes
221 (p_effective_date in date
222 ,p_base_key_value in number
223 ,p_correction out nocopy boolean
224 ,p_update out nocopy boolean
225 ,p_update_override out nocopy boolean
226 ,p_update_change_insert out nocopy boolean
227 );
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 = :input_value_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 -- ----------------------------------------------------------------------------
281 -- |-----------------------< upd_effective_end_date >-------------------------|
282 -- ----------------------------------------------------------------------------
283 -- {Start Of Comments}
284 --
285 -- Description:
286 -- This procedure will update the specified datetrack row with the
287 -- specified new effective end date. The object version number is also
288 -- set to the next object version number. DateTrack modes which call
289 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
290 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
291 -- This is an internal datetrack maintenance procedure which should
292 -- not be modified in anyway.
293 --
294 -- Prerequisites:
295 -- None.
296 --
297 -- In Parameters:
298 -- p_new_effective_end_date
299 -- Specifies the new effective end date which will be set for the
300 -- row as of the effective date.
301 -- p_base_key_value
302 -- Specifies the primary key value for this datetrack entity.
303 -- (E.g. For this entity the assignment of the argument would be:
304 -- p_base_key_value = :input_value_id).
305 --
306 -- Post Success:
307 -- The specified row will be updated with the new effective end date and
308 -- object_version_number.
309 --
310 -- Post Failure:
311 -- Failure might occur if for the specified effective date and primary key
312 -- value a row doesn't exist.
313 --
314 -- Developer Implementation Notes:
315 -- This is an internal datetrack maintenance procedure which should
316 -- not be modified in anyway.
317 --
318 -- Access Status:
319 -- Internal Row Handler Use Only.
320 --
321 -- {End Of Comments}
322 -- ----------------------------------------------------------------------------
323 Procedure upd_effective_end_date
324 (p_effective_date in date
325 ,p_base_key_value in number
326 ,p_new_effective_end_date in date
327 ,p_validation_start_date in date
328 ,p_validation_end_date in date
329 ,p_object_version_number out nocopy number
330 );
331 --
332 -- ----------------------------------------------------------------------------
336 --
333 -- |---------------------------------< lck >----------------------------------|
334 -- ----------------------------------------------------------------------------
335 -- {Start Of Comments}
337 -- Description:
338 -- The Lck process for datetrack is complicated and comprises of the
339 -- following processing
340 -- The processing steps are as follows:
341 -- 1) The row to be updated or deleted must be locked.
342 -- By locking this row, the g_old_rec record data type is populated.
343 -- 2) If a comment exists the text is selected from hr_comments.
344 -- 3) The datetrack mode is then validated to ensure the operation is
345 -- valid. If the mode is valid the validation start and end dates for
346 -- the mode will be derived and returned. Any required locking is
347 -- completed when the datetrack mode is validated.
348 --
349 -- Prerequisites:
350 -- When attempting to call the lck procedure the object version number,
351 -- primary key, effective date and datetrack mode must be specified.
352 --
353 -- In Parameters:
354 -- p_effective_date
355 -- Specifies the date of the datetrack update operation.
356 -- p_datetrack_mode
357 -- Determines the datetrack update or delete mode.
358 --
359 -- Post Success:
360 -- On successful completion of the Lck process the row to be updated or
361 -- deleted will be locked and selected into the global data structure
362 -- g_old_rec.
363 --
364 -- Post Failure:
365 -- The Lck process can fail for three reasons:
366 -- 1) When attempting to lock the row the row could already be locked by
367 -- another user. This will raise the HR_Api.Object_Locked exception.
368 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
369 -- This error is trapped and reported using the message name
370 -- 'HR_7220_INVALID_PRIMARY_KEY'.
371 -- 3) The row although existing in the HR Schema has a different object
372 -- version number than the object version number specified.
373 -- This error is trapped and reported using the message name
374 -- 'HR_7155_OBJECT_INVALID'.
375 --
376 -- Developer Implementation Notes:
377 -- None.
378 --
379 -- Access Status:
380 -- Internal Development Use Only.
381 --
382 -- {End Of Comments}
383 -- ----------------------------------------------------------------------------
384 Procedure lck
385 (p_effective_date in date
386 ,p_datetrack_mode in varchar2
387 ,p_input_value_id in number
388 ,p_object_version_number in number
389 ,p_validation_start_date out nocopy date
390 ,p_validation_end_date out nocopy date
391 );
392 --
393 -- ----------------------------------------------------------------------------
394 -- |-----------------------------< convert_args >-----------------------------|
395 -- ----------------------------------------------------------------------------
396 -- {Start Of Comments}
397 --
398 -- Description:
399 -- This function is used to turn attribute parameters into the record
400 -- structure parameter g_rec_type.
401 --
402 -- Prerequisites:
403 -- This is a private function and can only be called from the ins or upd
404 -- attribute processes.
405 --
406 -- In Parameters:
407 --
408 -- Post Success:
409 -- A returning record structure will be returned.
410 --
411 -- Post Failure:
412 -- No direct error handling is required within this function. Any possible
413 -- errors within this function will be a PL/SQL value error due to
414 -- conversion of datatypes or data lengths.
415 --
416 -- Developer Implementation Notes:
417 -- None.
418 --
419 -- Access Status:
420 -- Internal Row Handler Use Only.
421 --
422 -- {End Of Comments}
423 -- ----------------------------------------------------------------------------
424 Function convert_args
425 (p_input_value_id in number
426 ,p_effective_start_date in date
427 ,p_effective_end_date in date
428 ,p_element_type_id in number
429 ,p_lookup_type in varchar2
430 ,p_business_group_id in number
431 ,p_legislation_code in varchar2
432 ,p_formula_id in number
433 ,p_value_set_id in number
434 ,p_display_sequence in number
435 ,p_generate_db_items_flag in varchar2
436 ,p_hot_default_flag in varchar2
437 ,p_mandatory_flag in varchar2
438 ,p_name in varchar2
439 ,p_uom in varchar2
440 ,p_default_value in varchar2
441 ,p_legislation_subgroup in varchar2
442 ,p_max_value in varchar2
443 ,p_min_value in varchar2
444 ,p_warning_or_error in varchar2
445 ,p_object_version_number in number
446 )
447 Return g_rec_type;
448 --
449 end pay_ivl_shd;