1 Package per_pcl_shd AUTHID CURRENT_USER as
2 /* $Header: pepclrhi.pkh 120.0 2005/05/31 13:01:05 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (cagr_entitlement_line_id number(15)
10 ,cagr_entitlement_id number(15)
11 ,mandatory varchar2(9) -- Increased length
12 ,value varchar2(240)
13 ,range_from varchar2(240)
14 ,range_to varchar2(240)
15 ,effective_start_date date
16 ,effective_end_date date
17 ,parent_spine_id number(15)
18 ,step_id number(15)
19 ,from_step_id number(15)
20 ,to_step_id number(15)
21 ,status varchar2(30)
22 ,oipl_id number(15)
23 ,object_version_number number(9)
24 ,grade_spine_id number(15)
25 ,eligy_prfl_id number(15)
26 );
27 --
28 -- ----------------------------------------------------------------------------
29 -- | Global Definitions - Internal Development Use Only |
30 -- ----------------------------------------------------------------------------
31 --
32 g_old_rec g_rec_type; -- Global record definition
33 --
34 -- ---------------------------------------------------------------------------
35 -- |----------------------------< non_value_category >-----------------------|
36 -- ---------------------------------------------------------------------------
37 -- {Start Of Comments}
38 --
39 -- Description:
40 -- Returns FALSE if the category is either Pay Scales, Position or Process
41 -- as for these categories the VALUE field is not used.
42 --
43 -- Prerequisites:
44 --
45 -- In Arguments:
46 -- p_category_name
47 --
48 --
49 -- Post Success:
50 -- Returns TRUE or FALSE
51 --
52 -- Post Failure:
53 --
54 -- Access Status:
55 -- Internal Development Use Only.
56 --
57 -- {End Of Comments}
58 -- ---------------------------------------------------------------------------
59 --
60 FUNCTION non_value_category
61 (p_category_name IN VARCHAR2) RETURN BOOLEAN;
62 --
63 -- ---------------------------------------------------------------------------
64 -- |----------------------------< retrieve_cagr_info >------------------------|
65 -- ---------------------------------------------------------------------------
66 -- {Start Of Comments}
67 --
68 -- Description:
69 -- Retrieves the collective_agreement_id and business_group_id that
70 -- the entitlement line is linked to.
71 --
72 -- Prerequisites:
73 --
74 -- In Arguments:
75 -- p_cagr_entitlement_item_id
76 --
77 --
78 -- Post Success:
79 -- Returns collective_agreement_id and business_group_id
80 --
81 -- Post Failure:
82 -- An error is raised if the entitlement_item does not exist.
83 --
84 -- Access Status:
85 -- Internal Development Use Only.
86 --
87 -- {End Of Comments}
88 -- ---------------------------------------------------------------------------
89 --
90 PROCEDURE retrieve_cagr_info
91 (p_cagr_entitlement_id IN NUMBER
92 ,p_collective_agreement_id OUT NOCOPY NUMBER
93 ,p_business_group_id OUT NOCOPY NUMBER);
94 --
95 -- ---------------------------------------------------------------------------
96 -- |--------------------< retrieve_entitlement_item_info >---------------------|
97 -- ---------------------------------------------------------------------------
98 -- {Start Of Comments}
99 --
100 -- Description:
101 -- Retrieves the entitlement item id and category name
102 -- for the entitlement item that the entitlement record is linked to
103 --
104 -- Prerequisites:
105 --
106 -- In Arguments:
107 -- p_cagr_entitlement_item_id
108 --
109 --
110 -- Post Success:
111 -- Returns cagr_entitlement_item_id, category_name
112 --
113 -- Post Failure:
114 -- An error is raised if the procedure cannont retrieve the requried
115 -- rows.
116 --
117 -- Access Status:
118 -- Internal Development Use Only.
119 --
120 -- {End Of Comments}
121 -- ---------------------------------------------------------------------------
122 --
123 PROCEDURE retrieve_entitlement_item_info
124 (p_cagr_entitlement_id IN NUMBER
125 ,p_cagr_entitlement_item_id OUT NOCOPY NUMBER
126 ,p_category_name OUT NOCOPY VARCHAR2
127 ,p_formula_criteria OUT NOCOPY VARCHAR2);
128 --
129 -- ---------------------------------------------------------------------------
130 -- |----------------------< retrieve_value_set_id >--------------------------|
131 -- ---------------------------------------------------------------------------
132 -- {Start Of Comments}
133 --
134 -- Description:
135 -- Retrieves the flex_value_set_id for a given entitlement item
136 --
137 -- Prerequisites:
138 --
139 -- In Arguments:
140 -- p_cagr_entitlement_line_id
141 --
142 -- Post Success:
143 -- Returns flex_value_set_id
144 --
145 -- Post Failure:
146 --
147 -- Access Status:
148 -- Internal Development Use Only.
149 --
150 -- {End Of Comments}
151 -- ---------------------------------------------------------------------------
152 --
153 FUNCTION retrieve_value_set_id
154 (p_cagr_entitlement_item_id IN
155 per_cagr_entitlements.cagr_entitlement_item_id%TYPE)
156 RETURN NUMBER;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |---------------------------< constraint_error >---------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- This procedure is called when a constraint has been violated (i.e.
165 -- The exception hr_api.check_integrity_violated,
166 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
167 -- hr_api.unique_integrity_violated has been raised).
168 -- The exceptions can only be raised as follows:
169 -- 1) A check constraint can only be violated during an INSERT or UPDATE
170 -- dml operation.
171 -- 2) A parent integrity constraint can only be violated during an
172 -- INSERT or UPDATE dml operation.
173 -- 3) A child integrity constraint can only be violated during an
174 -- DELETE dml operation.
175 -- 4) A unique integrity constraint can only be violated during INSERT or
176 -- UPDATE dml operation.
177 --
178 -- Prerequisites:
179 -- 1) Either hr_api.check_integrity_violated,
180 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
181 -- hr_api.unique_integrity_violated has been raised with the subsequent
182 -- stripping of the constraint name from the generated error message
183 -- text.
184 -- 2) Standalone validation test which corresponds with a constraint error.
185 --
186 -- In Parameter:
187 -- p_constraint_name is in upper format and is just the constraint name
188 -- (e.g. not prefixed by brackets, schema owner etc).
189 --
190 -- Post Success:
191 -- Development dependant.
192 --
193 -- Post Failure:
194 -- Developement dependant.
195 --
196 -- Developer Implementation Notes:
197 -- For each constraint being checked the hr system package failure message
198 -- has been generated as a template only. These system error messages should
199 -- be modified as required (i.e. change the system failure message to a user
200 -- friendly defined error message).
201 --
202 -- Access Status:
203 -- Internal Development Use Only.
204 --
205 -- {End Of Comments}
206 -- ----------------------------------------------------------------------------
207 Procedure constraint_error
208 (p_constraint_name in all_constraints.constraint_name%TYPE);
209 --
210 -- ----------------------------------------------------------------------------
211 -- |-----------------------------< api_updating >-----------------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This function is used to populate the g_old_rec record with the
217 -- current row from the database for the specified primary key
218 -- provided that the primary key exists and is valid and does not
219 -- already match the current g_old_rec. The function will always return
220 -- a TRUE value if the g_old_rec is populated with the current row.
221 -- A FALSE value will be returned if all of the primary key arguments
222 -- are null.
223 --
224 -- Prerequisites:
225 -- None.
226 --
227 -- In Parameters:
228 --
229 -- Post Success:
230 -- A value of TRUE will be returned indiciating that the g_old_rec
231 -- is current.
232 -- A value of FALSE will be returned if all of the primary key arguments
233 -- have a null value (this indicates that the row has not be inserted into
234 -- the Schema), and therefore could never have a corresponding row.
235 --
236 -- Post Failure:
237 -- A failure can only occur under two circumstances:
238 -- 1) The primary key is invalid (i.e. a row does not exist for the
239 -- specified primary key values).
240 -- 2) If an object_version_number exists but is NOT the same as the current
241 -- g_old_rec value.
242 --
243 -- Developer Implementation Notes:
244 -- None.
245 --
246 -- Access Status:
247 -- Internal Development Use Only.
248 --
249 -- {End Of Comments}
250 -- ----------------------------------------------------------------------------
251 Function api_updating
252 (p_effective_date in date
253 ,p_cagr_entitlement_line_id in number
254 ,p_object_version_number in number
255 ) Return Boolean;
256 --
257 -- ----------------------------------------------------------------------------
258 -- |---------------------------< find_dt_upd_modes >--------------------------|
259 -- ----------------------------------------------------------------------------
260 -- {Start Of Comments}
261 --
262 -- Description:
263 -- This procedure is used to determine what datetrack update modes are
264 -- allowed as of the effective date for this entity. The procedure will
265 -- return a corresponding Boolean value for each of the update modes
266 -- available where TRUE indicates that the corresponding update mode
267 -- is available.
268 --
269 -- Prerequisites:
270 -- None.
271 --
272 -- In Parameters:
273 -- p_effective_date
274 -- Specifies the date at which the datetrack modes will be operated on.
275 -- p_base_key_value
276 -- Specifies the primary key value for this datetrack entity.
277 -- (E.g. For this entity the assignment of the argument would be:
278 -- p_base_key_value = :cagr_entitlement_line_id).
279 --
280 -- Post Success:
281 -- Processing continues.
282 --
283 -- Post Failure:
284 -- Failure might occur if for the specified effective date and primary key
285 -- value a row doesn't exist.
286 --
287 -- Developer Implementation Notes:
288 -- This procedure could require changes if this entity has any sepcific
289 -- delete restrictions.
290 -- For example, this entity might disallow the datetrack update mode of
291 -- UPDATE. To implement this you would have to set and return a Boolean
292 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
293 --
294 -- Access Status:
295 -- Internal Development Use Only.
296 --
297 -- {End Of Comments}
298 -- ----------------------------------------------------------------------------
299 Procedure find_dt_upd_modes
300 (p_effective_date in date
301 ,p_base_key_value in number
302 ,p_correction out nocopy boolean
303 ,p_update out nocopy boolean
304 ,p_update_override out nocopy boolean
305 ,p_update_change_insert out nocopy boolean
306 );
307 --
308 -- ----------------------------------------------------------------------------
309 -- |---------------------------< find_dt_del_modes >--------------------------|
310 -- ----------------------------------------------------------------------------
311 -- {Start Of Comments}
312 --
313 -- Description:
314 -- This procedure is used to determine what datetrack delete modes are
315 -- allowed as of the effective date for this entity. The procedure will
316 -- return a corresponding Boolean value for each of the delete modes
317 -- available where TRUE indicates that the corresponding delete mode is
318 -- available.
319 --
320 -- Prerequisites:
321 -- None.
322 --
323 -- In Parameters:
324 -- p_effective_date
325 -- Specifies the date at which the datetrack modes will be operated on.
326 -- p_base_key_value
327 -- Specifies the primary key value for this datetrack entity.
328 -- (E.g. For this entity the assignment of the argument would be:
329 -- p_base_key_value = :cagr_entitlement_line_id).
330 --
331 -- Post Success:
332 -- Processing continues.
333 --
334 -- Post Failure:
335 -- Failure might occur if for the specified effective date and primary key
336 -- value a row doesn't exist.
337 --
338 -- Developer Implementation Notes:
339 -- This procedure could require changes if this entity has any sepcific
340 -- delete restrictions.
341 -- For example, this entity might disallow the datetrack delete mode of
342 -- ZAP. To implement this you would have to set and return a Boolean value
343 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
344 --
345 -- Access Status:
346 -- Internal Development Use Only.
347 --
348 -- {End Of Comments}
349 -- ----------------------------------------------------------------------------
350 Procedure find_dt_del_modes
351 (p_effective_date in date
352 ,p_base_key_value in number
353 ,p_zap out nocopy boolean
354 ,p_delete out nocopy boolean
355 ,p_future_change out nocopy boolean
356 ,p_delete_next_change out nocopy boolean
357 );
358 --
359 -- ----------------------------------------------------------------------------
360 -- |-----------------------< upd_effective_end_date >-------------------------|
361 -- ----------------------------------------------------------------------------
362 -- {Start Of Comments}
363 --
364 -- Description:
365 -- This procedure will update the specified datetrack row with the
366 -- specified new effective end date. The object version number is also
367 -- set to the next object version number. DateTrack modes which call
368 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
369 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
370 -- This is an internal datetrack maintenance procedure which should
371 -- not be modified in anyway.
372 --
373 -- Prerequisites:
374 -- None.
375 --
376 -- In Parameters:
377 -- p_new_effective_end_date
378 -- Specifies the new effective end date which will be set for the
379 -- row as of the effective date.
380 -- p_base_key_value
381 -- Specifies the primary key value for this datetrack entity.
382 -- (E.g. For this entity the assignment of the argument would be:
383 -- p_base_key_value = :cagr_entitlement_line_id).
384 --
385 -- Post Success:
386 -- The specified row will be updated with the new effective end date and
387 -- object_version_number.
388 --
389 -- Post Failure:
390 -- Failure might occur if for the specified effective date and primary key
391 -- value a row doesn't exist.
392 --
396 --
393 -- Developer Implementation Notes:
394 -- This is an internal datetrack maintenance procedure which should
395 -- not be modified in anyway.
397 -- Access Status:
398 -- Internal Row Handler Use Only.
399 --
400 -- {End Of Comments}
401 -- ----------------------------------------------------------------------------
402 Procedure upd_effective_end_date
403 (p_effective_date in date
404 ,p_base_key_value in number
405 ,p_new_effective_end_date in date
406 ,p_validation_start_date in date
407 ,p_validation_end_date in date
408 ,p_object_version_number out nocopy number
409 );
410 --
411 -- ----------------------------------------------------------------------------
412 -- |---------------------------------< lck >----------------------------------|
413 -- ----------------------------------------------------------------------------
414 -- {Start Of Comments}
415 --
416 -- Description:
417 -- The Lck process for datetrack is complicated and comprises of the
418 -- following processing
419 -- The processing steps are as follows:
420 -- 1) The row to be updated or deleted must be locked.
421 -- By locking this row, the g_old_rec record data type is populated.
422 -- 2) If a comment exists the text is selected from hr_comments.
423 -- 3) The datetrack mode is then validated to ensure the operation is
424 -- valid. If the mode is valid the validation start and end dates for
425 -- the mode will be derived and returned. Any required locking is
426 -- completed when the datetrack mode is validated.
427 --
428 -- Prerequisites:
429 -- When attempting to call the lck procedure the object version number,
430 -- primary key, effective date and datetrack mode must be specified.
431 --
432 -- In Parameters:
433 -- p_effective_date
434 -- Specifies the date of the datetrack update operation.
435 -- p_datetrack_mode
436 -- Determines the datetrack update or delete mode.
437 --
438 -- Post Success:
439 -- On successful completion of the Lck process the row to be updated or
440 -- deleted will be locked and selected into the global data structure
441 -- g_old_rec.
442 --
443 -- Post Failure:
444 -- The Lck process can fail for three reasons:
445 -- 1) When attempting to lock the row the row could already be locked by
446 -- another user. This will raise the HR_Api.Object_Locked exception.
447 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
448 -- This error is trapped and reported using the message name
449 -- 'HR_7220_INVALID_PRIMARY_KEY'.
450 -- 3) The row although existing in the HR Schema has a different object
451 -- version number than the object version number specified.
452 -- This error is trapped and reported using the message name
453 -- 'HR_7155_OBJECT_INVALID'.
454 --
455 -- Developer Implementation Notes:
456 -- None.
457 --
458 -- Access Status:
459 -- Internal Development Use Only.
460 --
461 -- {End Of Comments}
462 -- ----------------------------------------------------------------------------
463 Procedure lck
464 (p_effective_date in date
465 ,p_datetrack_mode in varchar2
466 ,p_cagr_entitlement_line_id in number
467 ,p_object_version_number in number
468 ,p_validation_start_date out nocopy date
469 ,p_validation_end_date out nocopy date
470 );
471 --
472 -- ----------------------------------------------------------------------------
473 -- |-----------------------------< convert_args >-----------------------------|
474 -- ----------------------------------------------------------------------------
475 -- {Start Of Comments}
476 --
477 -- Description:
478 -- This function is used to turn attribute parameters into the record
479 -- structure parameter g_rec_type.
480 --
481 -- Prerequisites:
482 -- This is a private function and can only be called from the ins or upd
483 -- attribute processes.
484 --
485 -- In Parameters:
486 --
487 -- Post Success:
488 -- A returning record structure will be returned.
489 --
490 -- Post Failure:
491 -- No direct error handling is required within this function. Any possible
492 -- errors within this function will be a PL/SQL value error due to
493 -- conversion of datatypes or data lengths.
494 --
495 -- Developer Implementation Notes:
496 -- None.
497 --
498 -- Access Status:
499 -- Internal Row Handler Use Only.
500 --
501 -- {End Of Comments}
502 -- ----------------------------------------------------------------------------
503 Function convert_args
504 (p_cagr_entitlement_line_id in number
505 ,p_cagr_entitlement_id in number
506 ,p_mandatory in varchar2
507 ,p_value in varchar2
508 ,p_range_from in varchar2
509 ,p_range_to in varchar2
510 ,p_effective_start_date in date
511 ,p_effective_end_date in date
512 ,p_parent_spine_id in number
513 ,p_step_id in number
514 ,p_from_step_id in number
515 ,p_to_step_id in number
516 ,p_status in varchar2
520 ,p_eligy_prfl_id in number
517 ,p_oipl_id in number
518 ,p_object_version_number in number
519 ,p_grade_spine_id in number
521 )
522 Return g_rec_type;
523 --
524 end per_pcl_shd;
[Home] [Help]