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