1 Package pqp_pty_shd as
2 /* $Header: pqptyrhi.pkh 120.0.12000000.1 2007/01/16 04:29:04 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (pension_type_id number(10)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,pension_type_name varchar2(240)
13 ,pension_category varchar2(30)
14 ,pension_provider_type varchar2(30)
15 ,salary_calculation_method varchar2(30)
16 ,threshold_conversion_rule varchar2(30)
17 ,contribution_conversion_rule varchar2(30)
18 ,er_annual_limit number(14,4)
19 ,ee_annual_limit number(14,4)
20 ,er_annual_salary_threshold number(14,4)
21 ,ee_annual_salary_threshold number(14,4)
22 ,object_version_number number(9)
23 ,business_group_id number(15)
24 ,legislation_code varchar2(30)
25 ,description varchar2(240)
26 ,minimum_age number(9) -- Increased length
27 ,ee_contribution_percent number(13,4) -- Increased length
28 ,maximum_age number(9) -- Increased length
29 ,er_contribution_percent number(13,4) -- Increased length
30 ,ee_annual_contribution number(14,4)
31 ,er_annual_contribution number(14,4)
32 ,annual_premium_amount number(14,4)
33 ,ee_contribution_bal_type_id number(9)
34 ,er_contribution_bal_type_id number(9)
35 ,balance_init_element_type_id number(9)
36 ,ee_contribution_fixed_rate number(14,4) -- added for UK
37 ,er_contribution_fixed_rate number(14,4) -- added for UK
38 ,pty_attribute_category varchar2(30)
39 ,pty_attribute1 varchar2(150)
40 ,pty_attribute2 varchar2(150)
41 ,pty_attribute3 varchar2(150)
42 ,pty_attribute4 varchar2(150)
43 ,pty_attribute5 varchar2(150)
44 ,pty_attribute6 varchar2(150)
45 ,pty_attribute7 varchar2(150)
46 ,pty_attribute8 varchar2(150)
47 ,pty_attribute9 varchar2(150)
48 ,pty_attribute10 varchar2(150)
49 ,pty_attribute11 varchar2(150)
50 ,pty_attribute12 varchar2(150)
51 ,pty_attribute13 varchar2(150)
52 ,pty_attribute14 varchar2(150)
53 ,pty_attribute15 varchar2(150)
54 ,pty_attribute16 varchar2(150)
55 ,pty_attribute17 varchar2(150)
56 ,pty_attribute18 varchar2(150)
57 ,pty_attribute19 varchar2(150)
58 ,pty_attribute20 varchar2(150)
59 ,pty_information_category varchar2(30)
60 ,pty_information1 varchar2(150)
61 ,pty_information2 varchar2(150)
62 ,pty_information3 varchar2(150)
63 ,pty_information4 varchar2(150)
64 ,pty_information5 varchar2(150)
65 ,pty_information6 varchar2(150)
66 ,pty_information7 varchar2(150)
67 ,pty_information8 varchar2(150)
68 ,pty_information9 varchar2(150)
69 ,pty_information10 varchar2(150)
70 ,pty_information11 varchar2(150)
71 ,pty_information12 varchar2(150)
72 ,pty_information13 varchar2(150)
73 ,pty_information14 varchar2(150)
74 ,pty_information15 varchar2(150)
75 ,pty_information16 varchar2(150)
76 ,pty_information17 varchar2(150)
77 ,pty_information18 varchar2(150)
78 ,pty_information19 varchar2(150)
79 ,pty_information20 varchar2(150)
80 ,special_pension_type_code varchar2(30) -- added for NL Phase 2B
81 ,pension_sub_category varchar2(30) -- added for NL Phase 2B
82 ,pension_basis_calc_method varchar2(30) -- added for NL Phase 2B
83 ,pension_salary_balance number(9,0) -- added for NL Phase 2B
84 ,recurring_bonus_percent number(7,4) -- added for NL Phase 2B
85 ,non_recurring_bonus_percent number(7,4) -- added for NL Phase 2B
86 ,recurring_bonus_balance number(9,0) -- added for NL Phase 2B
87 ,non_recurring_bonus_balance number(9,0) -- added for NL Phase 2B
88 ,std_tax_reduction varchar2(30) -- added for NL Phase 2B
89 ,spl_tax_reduction varchar2(30) -- added for NL Phase 2B
90 ,sig_sal_spl_tax_reduction varchar2(30) -- added for NL Phase 2B
91 ,sig_sal_non_tax_reduction varchar2(30) -- added for NL Phase 2B
92 ,sig_sal_std_tax_reduction varchar2(30) -- added for NL Phase 2B
93 ,sii_std_tax_reduction varchar2(30) -- added for NL Phase 2B
94 ,sii_spl_tax_reduction varchar2(30) -- added for NL Phase 2B
95 ,sii_non_tax_reduction varchar2(30) -- added for NL Phase 2B
96 ,previous_year_bonus_included varchar2(30) -- added for NL Phase 2B
97 ,recurring_bonus_period varchar2(30) -- added for NL Phase 2B
98 ,non_recurring_bonus_period varchar2(30) -- added for NL Phase 2B
99 ,ee_age_threshold varchar2(30) -- added for ABP TAR Fixes
100 ,er_age_threshold varchar2(30) -- added for ABP TAR Fixes
101 ,ee_age_contribution varchar2(30) -- added for ABP TAR Fixes
102 ,er_age_contribution varchar2(30) -- added for ABP TAR Fixes
103 );
104 --
105 -- ----------------------------------------------------------------------------
106 -- | Global Definitions - Internal Development Use Only |
107 -- ----------------------------------------------------------------------------
108 --
109 g_old_rec g_rec_type; -- Global record definition
110 -- Global table name
111 g_tab_nam constant varchar2(30) := 'PQP_PENSION_TYPES_F';
112 --
113 -- ----------------------------------------------------------------------------
114 -- |---------------------------< constraint_error >---------------------------|
115 -- ----------------------------------------------------------------------------
116 -- {Start Of Comments}
117 --
118 -- Description:
119 -- This procedure is called when a constraint has been violated (i.e.
120 -- The exception hr_api.check_integrity_violated,
121 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
122 -- hr_api.unique_integrity_violated has been raised).
123 -- The exceptions can only be raised as follows:
124 -- 1) A check constraint can only be violated during an INSERT or UPDATE
125 -- dml operation.
126 -- 2) A parent integrity constraint can only be violated during an
127 -- INSERT or UPDATE dml operation.
128 -- 3) A child integrity constraint can only be violated during an
129 -- DELETE dml operation.
130 -- 4) A unique integrity constraint can only be violated during INSERT or
131 -- UPDATE dml operation.
132 --
133 -- Prerequisites:
134 -- 1) Either hr_api.check_integrity_violated,
135 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
136 -- hr_api.unique_integrity_violated has been raised with the subsequent
137 -- stripping of the constraint name from the generated error message
138 -- text.
139 -- 2) Standalone validation test which corresponds with a constraint error.
140 --
141 -- In Parameter:
142 -- p_constraint_name is in upper format and is just the constraint name
143 -- (e.g. not prefixed by brackets, schema owner etc).
144 --
145 -- Post Success:
146 -- Development dependant.
147 --
148 -- Post Failure:
149 -- Developement dependant.
150 --
151 -- Developer Implementation Notes:
152 -- For each constraint being checked the hr system package failure message
153 -- has been generated as a template only. These system error messages should
154 -- be modified as required (i.e. change the system failure message to a user
155 -- friendly defined error message).
156 --
157 -- Access Status:
158 -- Internal Development Use Only.
159 --
160 -- {End Of Comments}
161 -- ----------------------------------------------------------------------------
162 Procedure constraint_error
163 (p_constraint_name in all_constraints.constraint_name%TYPE);
164 --
165 -- ----------------------------------------------------------------------------
166 -- |-----------------------------< api_updating >-----------------------------|
167 -- ----------------------------------------------------------------------------
168 -- {Start Of Comments}
169 --
170 -- Description:
171 -- This function is used to populate the g_old_rec record with the
172 -- current row from the database for the specified primary key
173 -- provided that the primary key exists and is valid and does not
174 -- already match the current g_old_rec. The function will always return
175 -- a TRUE value if the g_old_rec is populated with the current row.
176 -- A FALSE value will be returned if all of the primary key arguments
177 -- are null.
178 --
179 -- Prerequisites:
180 -- None.
181 --
182 -- In Parameters:
183 --
184 -- Post Success:
185 -- A value of TRUE will be returned indiciating that the g_old_rec
186 -- is current.
187 -- A value of FALSE will be returned if all of the primary key arguments
188 -- have a null value (this indicates that the row has not be inserted into
189 -- the Schema), and therefore could never have a corresponding row.
190 --
191 -- Post Failure:
192 -- A failure can only occur under two circumstances:
193 -- 1) The primary key is invalid (i.e. a row does not exist for the
194 -- specified primary key values).
195 -- 2) If an object_version_number exists but is NOT the same as the current
196 -- g_old_rec value.
197 --
198 -- Developer Implementation Notes:
199 -- None.
200 --
201 -- Access Status:
202 -- Internal Development Use Only.
203 --
204 -- {End Of Comments}
205 -- ----------------------------------------------------------------------------
206 Function api_updating
207 (p_effective_date in date
208 ,p_pension_type_id in number
209 ,p_object_version_number in number
210 ) Return Boolean;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |---------------------------< find_dt_upd_modes >--------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- This procedure is used to determine what datetrack update modes are
219 -- allowed as of the effective date for this entity. The procedure will
220 -- return a corresponding Boolean value for each of the update modes
221 -- available where TRUE indicates that the corresponding update mode
222 -- is available.
223 --
224 -- Prerequisites:
225 -- None.
226 --
227 -- In Parameters:
228 -- p_effective_date
229 -- Specifies the date at which the datetrack modes will be operated on.
230 -- p_base_key_value
231 -- Specifies the primary key value for this datetrack entity.
232 -- (E.g. For this entity the assignment of the argument would be:
233 -- p_base_key_value = :pension_type_id).
234 --
235 -- Post Success:
236 -- Processing continues.
237 --
238 -- Post Failure:
239 -- Failure might occur if for the specified effective date and primary key
240 -- value a row doesn't exist.
241 --
242 -- Developer Implementation Notes:
243 -- This procedure could require changes if this entity has any sepcific
244 -- delete restrictions.
245 -- For example, this entity might disallow the datetrack update mode of
246 -- UPDATE. To implement this you would have to set and return a Boolean
247 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
248 --
249 -- Access Status:
250 -- Internal Development Use Only.
251 --
252 -- {End Of Comments}
253 -- ----------------------------------------------------------------------------
254 Procedure find_dt_upd_modes
255 (p_effective_date in date
256 ,p_base_key_value in number
257 ,p_correction out nocopy boolean
258 ,p_update out nocopy boolean
259 ,p_update_override out nocopy boolean
260 ,p_update_change_insert out nocopy boolean
261 );
262 --
263 -- ----------------------------------------------------------------------------
264 -- |---------------------------< find_dt_del_modes >--------------------------|
265 -- ----------------------------------------------------------------------------
266 -- {Start Of Comments}
267 --
268 -- Description:
269 -- This procedure is used to determine what datetrack delete modes are
270 -- allowed as of the effective date for this entity. The procedure will
271 -- return a corresponding Boolean value for each of the delete modes
272 -- available where TRUE indicates that the corresponding delete mode is
273 -- available.
274 --
275 -- Prerequisites:
276 -- None.
277 --
278 -- In Parameters:
279 -- p_effective_date
280 -- Specifies the date at which the datetrack modes will be operated on.
281 -- p_base_key_value
282 -- Specifies the primary key value for this datetrack entity.
283 -- (E.g. For this entity the assignment of the argument would be:
284 -- p_base_key_value = :pension_type_id).
285 --
286 -- Post Success:
287 -- Processing continues.
288 --
289 -- Post Failure:
290 -- Failure might occur if for the specified effective date and primary key
291 -- value a row doesn't exist.
292 --
293 -- Developer Implementation Notes:
294 -- This procedure could require changes if this entity has any sepcific
295 -- delete restrictions.
296 -- For example, this entity might disallow the datetrack delete mode of
297 -- ZAP. To implement this you would have to set and return a Boolean value
298 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
299 --
300 -- Access Status:
301 -- Internal Development Use Only.
302 --
303 -- {End Of Comments}
304 -- ----------------------------------------------------------------------------
305 Procedure find_dt_del_modes
306 (p_effective_date in date
307 ,p_base_key_value in number
308 ,p_zap out nocopy boolean
309 ,p_delete out nocopy boolean
310 ,p_future_change out nocopy boolean
311 ,p_delete_next_change out nocopy boolean
312 );
313 --
314 -- ----------------------------------------------------------------------------
315 -- |-----------------------< upd_effective_end_date >-------------------------|
316 -- ----------------------------------------------------------------------------
317 -- {Start Of Comments}
318 --
319 -- Description:
320 -- This procedure will update the specified datetrack row with the
321 -- specified new effective end date. The object version number is also
325 -- This is an internal datetrack maintenance procedure which should
322 -- set to the next object version number. DateTrack modes which call
323 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
324 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
326 -- not be modified in anyway.
327 --
328 -- Prerequisites:
329 -- None.
330 --
331 -- In Parameters:
332 -- p_new_effective_end_date
333 -- Specifies the new effective end date which will be set for the
334 -- row as of the effective date.
335 -- p_base_key_value
336 -- Specifies the primary key value for this datetrack entity.
337 -- (E.g. For this entity the assignment of the argument would be:
338 -- p_base_key_value = :pension_type_id).
339 --
340 -- Post Success:
341 -- The specified row will be updated with the new effective end date and
342 -- object_version_number.
343 --
344 -- Post Failure:
345 -- Failure might occur if for the specified effective date and primary key
346 -- value a row doesn't exist.
347 --
348 -- Developer Implementation Notes:
349 -- This is an internal datetrack maintenance procedure which should
350 -- not be modified in anyway.
351 --
352 -- Access Status:
353 -- Internal Row Handler Use Only.
354 --
355 -- {End Of Comments}
356 -- ----------------------------------------------------------------------------
357 Procedure upd_effective_end_date
358 (p_effective_date in date
359 ,p_base_key_value in number
360 ,p_new_effective_end_date in date
361 ,p_validation_start_date in date
362 ,p_validation_end_date in date
363 ,p_object_version_number out nocopy number
364 );
365 --
366 -- ----------------------------------------------------------------------------
367 -- |---------------------------------< lck >----------------------------------|
368 -- ----------------------------------------------------------------------------
369 -- {Start Of Comments}
370 --
371 -- Description:
372 -- The Lck process for datetrack is complicated and comprises of the
373 -- following processing
374 -- The processing steps are as follows:
375 -- 1) The row to be updated or deleted must be locked.
376 -- By locking this row, the g_old_rec record data type is populated.
377 -- 2) If a comment exists the text is selected from hr_comments.
378 -- 3) The datetrack mode is then validated to ensure the operation is
379 -- valid. If the mode is valid the validation start and end dates for
380 -- the mode will be derived and returned. Any required locking is
381 -- completed when the datetrack mode is validated.
382 --
383 -- Prerequisites:
384 -- When attempting to call the lck procedure the object version number,
385 -- primary key, effective date and datetrack mode must be specified.
386 --
387 -- In Parameters:
388 -- p_effective_date
389 -- Specifies the date of the datetrack update operation.
390 -- p_datetrack_mode
391 -- Determines the datetrack update or delete mode.
392 --
393 -- Post Success:
394 -- On successful completion of the Lck process the row to be updated or
395 -- deleted will be locked and selected into the global data structure
396 -- g_old_rec.
397 --
398 -- Post Failure:
399 -- The Lck process can fail for three reasons:
400 -- 1) When attempting to lock the row the row could already be locked by
401 -- another user. This will raise the HR_Api.Object_Locked exception.
402 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
403 -- This error is trapped and reported using the message name
404 -- 'HR_7220_INVALID_PRIMARY_KEY'.
405 -- 3) The row although existing in the HR Schema has a different object
406 -- version number than the object version number specified.
407 -- This error is trapped and reported using the message name
408 -- 'HR_7155_OBJECT_INVALID'.
409 --
410 -- Developer Implementation Notes:
411 -- None.
412 --
413 -- Access Status:
414 -- Internal Development Use Only.
415 --
416 -- {End Of Comments}
417 -- ----------------------------------------------------------------------------
418 Procedure lck
419 (p_effective_date in date
420 ,p_datetrack_mode in varchar2
421 ,p_pension_type_id in number
422 ,p_object_version_number in number
423 ,p_validation_start_date out nocopy date
424 ,p_validation_end_date out nocopy date
425 );
426 --
427 -- ----------------------------------------------------------------------------
428 -- |-----------------------------< convert_args >-----------------------------|
429 -- ----------------------------------------------------------------------------
430 -- {Start Of Comments}
431 --
432 -- Description:
433 -- This function is used to turn attribute parameters into the record
434 -- structure parameter g_rec_type.
435 --
436 -- Prerequisites:
437 -- This is a private function and can only be called from the ins or upd
438 -- attribute processes.
439 --
440 -- In Parameters:
441 --
442 -- Post Success:
443 -- A returning record structure will be returned.
444 --
445 -- Post Failure:
446 -- No direct error handling is required within this function. Any possible
447 -- errors within this function will be a PL/SQL value error due to
451 -- None.
448 -- conversion of datatypes or data lengths.
449 --
450 -- Developer Implementation Notes:
452 --
453 -- Access Status:
454 -- Internal Row Handler Use Only.
455 --
456 -- {End Of Comments}
457 -- ----------------------------------------------------------------------------
458 Function convert_args
459 (p_pension_type_id in number
460 ,p_effective_start_date in date
461 ,p_effective_end_date in date
462 ,p_pension_type_name in varchar2
463 ,p_pension_category in varchar2
464 ,p_pension_provider_type in varchar2
465 ,p_salary_calculation_method in varchar2
466 ,p_threshold_conversion_rule in varchar2
467 ,p_contribution_conversion_rule in varchar2
468 ,p_er_annual_limit in number
469 ,p_ee_annual_limit in number
470 ,p_er_annual_salary_threshold in number
471 ,p_ee_annual_salary_threshold in number
472 ,p_object_version_number in number
473 ,p_business_group_id in number
474 ,p_legislation_code in varchar2
475 ,p_description in varchar2
476 ,p_minimum_age in number
477 ,p_ee_contribution_percent in number
478 ,p_maximum_age in number
479 ,p_er_contribution_percent in number
480 ,p_ee_annual_contribution in number
481 ,p_er_annual_contribution in number
482 ,p_annual_premium_amount in number
483 ,p_ee_contribution_bal_type_id in number
484 ,p_er_contribution_bal_type_id in number
485 ,p_balance_init_element_type_id in number
486 ,p_ee_contribution_fixed_rate in number --added for UK
487 ,p_er_contribution_fixed_rate in number --added for UK
488 ,p_pty_attribute_category in varchar2
489 ,p_pty_attribute1 in varchar2
490 ,p_pty_attribute2 in varchar2
491 ,p_pty_attribute3 in varchar2
492 ,p_pty_attribute4 in varchar2
493 ,p_pty_attribute5 in varchar2
494 ,p_pty_attribute6 in varchar2
495 ,p_pty_attribute7 in varchar2
496 ,p_pty_attribute8 in varchar2
497 ,p_pty_attribute9 in varchar2
498 ,p_pty_attribute10 in varchar2
499 ,p_pty_attribute11 in varchar2
500 ,p_pty_attribute12 in varchar2
501 ,p_pty_attribute13 in varchar2
502 ,p_pty_attribute14 in varchar2
503 ,p_pty_attribute15 in varchar2
504 ,p_pty_attribute16 in varchar2
505 ,p_pty_attribute17 in varchar2
506 ,p_pty_attribute18 in varchar2
507 ,p_pty_attribute19 in varchar2
508 ,p_pty_attribute20 in varchar2
509 ,p_pty_information_category in varchar2
510 ,p_pty_information1 in varchar2
511 ,p_pty_information2 in varchar2
512 ,p_pty_information3 in varchar2
513 ,p_pty_information4 in varchar2
514 ,p_pty_information5 in varchar2
515 ,p_pty_information6 in varchar2
516 ,p_pty_information7 in varchar2
517 ,p_pty_information8 in varchar2
518 ,p_pty_information9 in varchar2
519 ,p_pty_information10 in varchar2
520 ,p_pty_information11 in varchar2
521 ,p_pty_information12 in varchar2
522 ,p_pty_information13 in varchar2
523 ,p_pty_information14 in varchar2
524 ,p_pty_information15 in varchar2
525 ,p_pty_information16 in varchar2
526 ,p_pty_information17 in varchar2
527 ,p_pty_information18 in varchar2
528 ,p_pty_information19 in varchar2
529 ,p_pty_information20 in varchar2
530 ,p_special_pension_type_code in varchar2 -- added for NL Phase 2B
531 ,p_pension_sub_category in varchar2 -- added for NL Phase 2B
532 ,p_pension_basis_calc_method in varchar2 -- added for NL Phase 2B
533 ,p_pension_salary_balance in number -- added for NL Phase 2B
534 ,p_recurring_bonus_percent in number -- added for NL Phase 2B
535 ,p_non_recurring_bonus_percent in number -- added for NL Phase 2B
536 ,p_recurring_bonus_balance in number -- added for NL Phase 2B
537 ,p_non_recurring_bonus_balance in number -- added for NL Phase 2B
538 ,p_std_tax_reduction in varchar2 -- added for NL Phase 2B
539 ,p_spl_tax_reduction in varchar2 -- added for NL Phase 2B
540 ,p_sig_sal_spl_tax_reduction in varchar2 -- added for NL Phase 2B
541 ,p_sig_sal_non_tax_reduction in varchar2 -- added for NL Phase 2B
542 ,p_sig_sal_std_tax_reduction in varchar2 -- added for NL Phase 2B
543 ,p_sii_std_tax_reduction in varchar2 -- added for NL Phase 2B
544 ,p_sii_spl_tax_reduction in varchar2 -- added for NL Phase 2B
545 ,p_sii_non_tax_reduction in varchar2 -- added for NL Phase 2B
546 ,p_previous_year_bonus_included in varchar2 -- added for NL Phase 2B
547 ,p_recurring_bonus_period in varchar2 -- added for NL Phase 2B
548 ,p_non_recurring_bonus_period in varchar2 -- added for NL Phase 2B
549 ,p_ee_age_threshold in varchar2 -- added for ABP TAR fixes
550 ,p_er_age_threshold in varchar2 -- added for ABP TAR fixes
551 ,p_ee_age_contribution in varchar2 -- added for ABP TAR fixes
552 ,p_er_age_contribution in varchar2 -- added for ABP TAR fixes
553 )
554 Return g_rec_type;
555 --
556 end pqp_pty_shd;
[Home] [Help]