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