1 Package pqp_aat_shd as
2 /* $Header: pqaatrhi.pkh 120.2.12010000.1 2008/07/28 11:07:11 appldev ship $ */
3 --
4 -- ---------------------------------------------------------------------------+
5 -- | Global Record Type Specification |
6 -- ---------------------------------------------------------------------------+
7 --
8 Type g_rec_type Is Record
9 (assignment_attribute_id number(15)
10 ,effective_start_date date
11 ,effective_end_date date
12 ,business_group_id number(15)
13 ,assignment_id number(15)
14 ,contract_type varchar2(80)
15 ,work_pattern varchar2(80)
16 ,start_day varchar2(30)
17 ,object_version_number number(15)
18 ,primary_company_car number(15)
19 ,primary_car_fuel_benefit varchar2(30)
20 ,primary_class_1a varchar2(30)
21 ,primary_capital_contribution number(15)
22 ,primary_private_contribution number(15)
23 ,secondary_company_car number(15)
24 ,secondary_car_fuel_benefit varchar2(30)
25 ,secondary_class_1a varchar2(30)
26 ,secondary_capital_contribution number(15)
27 ,secondary_private_contribution number(15)
28 ,company_car_calc_method varchar2(30)
29 ,company_car_rates_table_id number(9)
30 ,company_car_secondary_table_id number(9)
31 ,private_car number(15)
32 ,private_car_calc_method varchar2(30)
33 ,private_car_rates_table_id number(9)
34 ,private_car_essential_table_id number(9)
35 ,tp_is_teacher varchar2(30)
36 ,tp_headteacher_grp_code number(15) --added for head Teacher seconded location for salary scale calculation
37 ,tp_safeguarded_grade varchar2(150)
38 ,tp_safeguarded_grade_id number(15)
39 ,tp_safeguarded_rate_type varchar2(30)
40 ,tp_safeguarded_rate_id number(15)
41 ,tp_safeguarded_spinal_point_id number(15)
42 ,tp_elected_pension varchar2(30)
43 ,tp_fast_track varchar2(30)
44 ,aat_attribute_category varchar2(30)
45 ,aat_attribute1 varchar2(150)
46 ,aat_attribute2 varchar2(150)
47 ,aat_attribute3 varchar2(150)
48 ,aat_attribute4 varchar2(150)
49 ,aat_attribute5 varchar2(150)
50 ,aat_attribute6 varchar2(150)
51 ,aat_attribute7 varchar2(150)
52 ,aat_attribute8 varchar2(150)
53 ,aat_attribute9 varchar2(150)
54 ,aat_attribute10 varchar2(150)
55 ,aat_attribute11 varchar2(150)
56 ,aat_attribute12 varchar2(150)
57 ,aat_attribute13 varchar2(150)
58 ,aat_attribute14 varchar2(150)
59 ,aat_attribute15 varchar2(150)
60 ,aat_attribute16 varchar2(150)
61 ,aat_attribute17 varchar2(150)
62 ,aat_attribute18 varchar2(150)
63 ,aat_attribute19 varchar2(150)
64 ,aat_attribute20 varchar2(150)
65 ,aat_information_category varchar2(30)
66 ,aat_information1 varchar2(150)
67 ,aat_information2 varchar2(150)
68 ,aat_information3 varchar2(150)
69 ,aat_information4 varchar2(150)
70 ,aat_information5 varchar2(150)
71 ,aat_information6 varchar2(150)
72 ,aat_information7 varchar2(150)
73 ,aat_information8 varchar2(150)
74 ,aat_information9 varchar2(150)
75 ,aat_information10 varchar2(150)
76 ,aat_information11 varchar2(150)
77 ,aat_information12 varchar2(150)
78 ,aat_information13 varchar2(150)
79 ,aat_information14 varchar2(150)
80 ,aat_information15 varchar2(150)
81 ,aat_information16 varchar2(150)
82 ,aat_information17 varchar2(150)
83 ,aat_information18 varchar2(150)
84 ,aat_information19 varchar2(150)
85 ,aat_information20 varchar2(150)
86 ,lgps_process_flag varchar2(30)
87 ,lgps_exclusion_type varchar2(80)
88 ,lgps_pensionable_pay varchar2(60)
89 ,lgps_trans_arrang_flag varchar2(30)
90 ,lgps_membership_number varchar2(60)
91 );
92 --
93 -- ---------------------------------------------------------------------------+
94 -- | Global Definitions - Internal Development Use Only |
95 -- ---------------------------------------------------------------------------+
96 --
97 g_old_rec g_rec_type; -- Global record definition
98 g_api_dml boolean; -- Global api dml status
99 --
100 -- ---------------------------------------------------------------------------+
101 -- |------------------------< return_api_dml_status >-------------------------|
102 -- ---------------------------------------------------------------------------+
103 -- {Start Of Comments}
104 --
105 -- Description:
106 -- This function will return the current g_api_dml private global
107 -- boolean status.
108 -- The g_api_dml status determines if at the time of the function
109 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
110 -- is being issued from within an api.
111 -- If the status is TRUE then a dml statement is being issued from
112 -- within this entity api.
113 -- This function is primarily to support database triggers which
114 -- need to maintain the object_version_number for non-supported
115 -- dml statements (i.e. dml statement issued outside of the api layer).
116 --
117 -- Prerequisites:
118 -- None.
119 --
120 -- In Parameters:
121 -- None.
122 --
123 -- Post Success:
124 -- Processing continues.
125 -- If the function returns a TRUE value then, dml is being executed from
126 -- within this api.
127 --
128 -- Post Failure:
129 -- None.
130 --
131 -- Access Status:
132 -- Internal Row Handler Use Only.
133 --
134 -- {End Of Comments}
135 -- ---------------------------------------------------------------------------+
136 Function return_api_dml_status Return Boolean;
137 --
138 -- ---------------------------------------------------------------------------+
139 -- |---------------------------< constraint_error >---------------------------|
140 -- ---------------------------------------------------------------------------+
141 -- {Start Of Comments}
142 --
143 -- Description:
144 -- This procedure is called when a constraint has been violated (i.e.
145 -- The exception hr_api.check_integrity_violated,
146 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
147 -- hr_api.unique_integrity_violated has been raised).
148 -- The exceptions can only be raised as follows:
149 -- 1) A check constraint can only be violated during an INSERT or UPDATE
150 -- dml operation.
151 -- 2) A parent integrity constraint can only be violated during an
152 -- INSERT or UPDATE dml operation.
153 -- 3) A child integrity constraint can only be violated during an
154 -- DELETE dml operation.
155 -- 4) A unique integrity constraint can only be violated during INSERT or
156 -- UPDATE dml operation.
157 --
158 -- Prerequisites:
159 -- 1) Either hr_api.check_integrity_violated,
160 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
161 -- hr_api.unique_integrity_violated has been raised with the subsequent
162 -- stripping of the constraint name from the generated error message
163 -- text.
164 -- 2) Standalone validation test which corresponds with a constraint error.
165 --
166 -- In Parameter:
167 -- p_constraint_name is in upper format and is just the constraint name
168 -- (e.g. not prefixed by brackets, schema owner etc).
169 --
170 -- Post Success:
171 -- Development dependant.
172 --
173 -- Post Failure:
174 -- Developement dependant.
175 --
176 -- Developer Implementation Notes:
177 -- For each constraint being checked the hr system package failure message
178 -- has been generated as a template only. These system error messages should
179 -- be modified as required (i.e. change the system failure message to a user
180 -- friendly defined error message).
181 --
182 -- Access Status:
183 -- Internal Development Use Only.
184 --
185 -- {End Of Comments}
186 -- ---------------------------------------------------------------------------+
187 Procedure constraint_error
188 (p_constraint_name in all_constraints.constraint_name%TYPE);
189 --
190 -- ---------------------------------------------------------------------------+
191 -- |-----------------------------< api_updating >-----------------------------|
192 -- ---------------------------------------------------------------------------+
193 -- {Start Of Comments}
194 --
195 -- Description:
196 -- This function is used to populate the g_old_rec record with the
197 -- current row from the database for the specified primary key
198 -- provided that the primary key exists and is valid and does not
199 -- already match the current g_old_rec. The function will always return
200 -- a TRUE value if the g_old_rec is populated with the current row.
201 -- A FALSE value will be returned if all of the primary key arguments
202 -- are null.
203 --
204 -- Prerequisites:
205 -- None.
206 --
207 -- In Parameters:
208 --
209 -- Post Success:
210 -- A value of TRUE will be returned indiciating that the g_old_rec
211 -- is current.
212 -- A value of FALSE will be returned if all of the primary key arguments
213 -- have a null value (this indicates that the row has not be inserted into
214 -- the Schema), and therefore could never have a corresponding row.
215 --
216 -- Post Failure:
217 -- A failure can only occur under two circumstances:
218 -- 1) The primary key is invalid (i.e. a row does not exist for the
219 -- specified primary key values).
220 -- 2) If an object_version_number exists but is NOT the same as the current
221 -- g_old_rec value.
222 --
223 -- Developer Implementation Notes:
224 -- None.
225 --
226 -- Access Status:
227 -- Internal Development Use Only.
228 --
229 -- {End Of Comments}
230 -- ---------------------------------------------------------------------------+
231 Function api_updating
232 (p_effective_date in date
233 ,p_assignment_attribute_id in number
234 ,p_object_version_number in number
235 ) Return Boolean;
236 --
237 -- ---------------------------------------------------------------------------+
238 -- |---------------------------< find_dt_upd_modes >--------------------------|
239 -- ---------------------------------------------------------------------------+
240 -- {Start Of Comments}
241 --
242 -- Description:
243 -- This procedure is used to determine what datetrack update modes are
244 -- allowed as of the effective date for this entity. The procedure will
245 -- return a corresponding Boolean value for each of the update modes
246 -- available where TRUE indicates that the corresponding update mode
247 -- is available.
248 --
249 -- Prerequisites:
250 -- None.
251 --
252 -- In Parameters:
253 -- p_effective_date
254 -- Specifies the date at which the datetrack modes will be operated on.
255 -- p_base_key_value
256 -- Specifies the primary key value for this datetrack entity.
257 -- (E.g. For this entity the assignment of the argument would be:
258 -- p_base_key_value = :assignment_attribute_id).
259 --
260 -- Post Success:
261 -- Processing continues.
262 --
263 -- Post Failure:
264 -- Failure might occur if for the specified effective date and primary key
265 -- value a row doesn't exist.
266 --
267 -- Developer Implementation Notes:
268 -- This procedure could require changes if this entity has any sepcific
269 -- delete restrictions.
270 -- For example, this entity might disallow the datetrack update mode of
271 -- UPDATE. To implement this you would have to set and return a Boolean
272 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
273 --
274 -- Access Status:
275 -- Internal Development Use Only.
276 --
277 -- {End Of Comments}
278 -- ---------------------------------------------------------------------------+
279 Procedure find_dt_upd_modes
280 (p_effective_date in date
281 ,p_base_key_value in number
282 ,p_correction out nocopy boolean
283 ,p_update out nocopy boolean
284 ,p_update_override out nocopy boolean
285 ,p_update_change_insert out nocopy boolean
286 );
287 --
288 -- ---------------------------------------------------------------------------+
289 -- |---------------------------< find_dt_del_modes >--------------------------|
290 -- ---------------------------------------------------------------------------+
291 -- {Start Of Comments}
292 --
293 -- Description:
294 -- This procedure is used to determine what datetrack delete modes are
295 -- allowed as of the effective date for this entity. The procedure will
296 -- return a corresponding Boolean value for each of the delete modes
297 -- available where TRUE indicates that the corresponding delete mode is
298 -- available.
299 --
300 -- Prerequisites:
301 -- None.
302 --
303 -- In Parameters:
304 -- p_effective_date
305 -- Specifies the date at which the datetrack modes will be operated on.
306 -- p_base_key_value
307 -- Specifies the primary key value for this datetrack entity.
308 -- (E.g. For this entity the assignment of the argument would be:
309 -- p_base_key_value = :assignment_attribute_id).
310 --
311 -- Post Success:
312 -- Processing continues.
313 --
314 -- Post Failure:
315 -- Failure might occur if for the specified effective date and primary key
316 -- value a row doesn't exist.
317 --
318 -- Developer Implementation Notes:
319 -- This procedure could require changes if this entity has any sepcific
320 -- delete restrictions.
321 -- For example, this entity might disallow the datetrack delete mode of
322 -- ZAP. To implement this you would have to set and return a Boolean value
323 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
324 --
325 -- Access Status:
326 -- Internal Development Use Only.
327 --
328 -- {End Of Comments}
329 -- ---------------------------------------------------------------------------+
330 Procedure find_dt_del_modes
331 (p_effective_date in date
332 ,p_base_key_value in number
333 ,p_zap out nocopy boolean
334 ,p_delete out nocopy boolean
335 ,p_future_change out nocopy boolean
336 ,p_delete_next_change out nocopy boolean
337 );
338 --
339 -- ---------------------------------------------------------------------------+
340 -- |-----------------------< upd_effective_end_date >-------------------------|
341 -- ---------------------------------------------------------------------------+
342 -- {Start Of Comments}
343 --
344 -- Description:
345 -- This procedure will update the specified datetrack row with the
346 -- specified new effective end date. The object version number is also
347 -- set to the next object version number. DateTrack modes which call
348 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
349 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
350 -- This is an internal datetrack maintenance procedure which should
351 -- not be modified in anyway.
352 --
353 -- Prerequisites:
354 -- None.
355 --
356 -- In Parameters:
357 -- p_new_effective_end_date
358 -- Specifies the new effective end date which will be set for the
359 -- row as of the effective date.
360 -- p_base_key_value
364 --
361 -- Specifies the primary key value for this datetrack entity.
362 -- (E.g. For this entity the assignment of the argument would be:
363 -- p_base_key_value = :assignment_attribute_id).
365 -- Post Success:
366 -- The specified row will be updated with the new effective end date and
367 -- object_version_number.
368 --
369 -- Post Failure:
370 -- Failure might occur if for the specified effective date and primary key
371 -- value a row doesn't exist.
372 --
373 -- Developer Implementation Notes:
374 -- This is an internal datetrack maintenance procedure which should
375 -- not be modified in anyway.
376 --
377 -- Access Status:
378 -- Internal Row Handler Use Only.
379 --
380 -- {End Of Comments}
381 -- ---------------------------------------------------------------------------+
382 Procedure upd_effective_end_date
383 (p_effective_date in date
384 ,p_base_key_value in number
385 ,p_new_effective_end_date in date
386 ,p_validation_start_date in date
387 ,p_validation_end_date in date
388 ,p_object_version_number out nocopy number
389 );
390 --
391 -- ---------------------------------------------------------------------------+
392 -- |---------------------------------< lck >----------------------------------|
393 -- ---------------------------------------------------------------------------+
394 -- {Start Of Comments}
395 --
396 -- Description:
397 -- The Lck process for datetrack is complicated and comprises of the
398 -- following processing
399 -- The processing steps are as follows:
400 -- 1) The row to be updated or deleted must be locked.
401 -- By locking this row, the g_old_rec record data type is populated.
402 -- 2) If a comment exists the text is selected from hr_comments.
403 -- 3) The datetrack mode is then validated to ensure the operation is
404 -- valid. If the mode is valid the validation start and end dates for
405 -- the mode will be derived and returned. Any required locking is
406 -- completed when the datetrack mode is validated.
407 --
408 -- Prerequisites:
409 -- When attempting to call the lck procedure the object version number,
410 -- primary key, effective date and datetrack mode must be specified.
411 --
412 -- In Parameters:
413 -- p_effective_date
414 -- Specifies the date of the datetrack update operation.
415 -- p_datetrack_mode
416 -- Determines the datetrack update or delete mode.
417 --
418 -- Post Success:
419 -- On successful completion of the Lck process the row to be updated or
420 -- deleted will be locked and selected into the global data structure
421 -- g_old_rec.
422 --
423 -- Post Failure:
424 -- The Lck process can fail for three reasons:
425 -- 1) When attempting to lock the row the row could already be locked by
426 -- another user. This will raise the HR_Api.Object_Locked exception.
427 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
428 -- This error is trapped and reported using the message name
429 -- 'HR_7220_INVALID_PRIMARY_KEY'.
430 -- 3) The row although existing in the HR Schema has a different object
431 -- version number than the object version number specified.
432 -- This error is trapped and reported using the message name
433 -- 'HR_7155_OBJECT_INVALID'.
434 --
435 -- Developer Implementation Notes:
436 -- None.
437 --
438 -- Access Status:
439 -- Internal Development Use Only.
440 --
441 -- {End Of Comments}
442 -- ---------------------------------------------------------------------------+
443 Procedure lck
444 (p_effective_date in date
445 ,p_datetrack_mode in varchar2
446 ,p_assignment_attribute_id in number
447 ,p_object_version_number in number
448 ,p_validation_start_date out nocopy date
449 ,p_validation_end_date out nocopy date
450 );
451 --
452 -- ---------------------------------------------------------------------------+
453 -- |-----------------------------< convert_args >-----------------------------|
454 -- ---------------------------------------------------------------------------+
455 -- {Start Of Comments}
456 --
457 -- Description:
458 -- This function is used to turn attribute parameters into the record
459 -- structure parameter g_rec_type.
460 --
461 -- Prerequisites:
462 -- This is a private function and can only be called from the ins or upd
463 -- attribute processes.
464 --
465 -- In Parameters:
466 --
467 -- Post Success:
468 -- A returning record structure will be returned.
469 --
470 -- Post Failure:
471 -- No direct error handling is required within this function. Any possible
472 -- errors within this function will be a PL/SQL value error due to conversion
473 -- of datatypes or data lengths.
474 --
475 -- Developer Implementation Notes:
476 -- None.
477 --
478 -- Access Status:
479 -- Internal Row Handler Use Only.
480 --
481 -- {End Of Comments}
482 -- ---------------------------------------------------------------------------+
483 Function convert_args
484 (p_assignment_attribute_id in number
485 ,p_effective_start_date in date
486 ,p_effective_end_date in date
487 ,p_business_group_id in number
488 ,p_assignment_id in number
492 ,p_object_version_number in number
489 ,p_contract_type in varchar2
490 ,p_work_pattern in varchar2
491 ,p_start_day in varchar2
493 ,p_primary_company_car in number
494 ,p_primary_car_fuel_benefit in varchar2
495 ,p_primary_class_1a in varchar2
496 ,p_primary_capital_contribution in number
497 ,p_primary_private_contribution in number
498 ,p_secondary_company_car in number
499 ,p_secondary_car_fuel_benefit in varchar2
500 ,p_secondary_class_1a in varchar2
501 ,p_secondary_capital_contributi in number
502 ,p_secondary_private_contributi in number
503 ,p_company_car_calc_method in varchar2
504 ,p_company_car_rates_table_id in number
505 ,p_company_car_secondary_table in number
506 ,p_private_car in number
507 ,p_private_car_calc_method in varchar2
508 ,p_private_car_rates_table_id in number
509 ,p_private_car_essential_table in number
510 ,p_tp_is_teacher in varchar2
511 ,p_tp_headteacher_grp_code in number --added for head Teacher seconded location for salary scale calculation
512 ,p_tp_safeguarded_grade in varchar2
513 ,p_tp_safeguarded_grade_id in number
514 ,p_tp_safeguarded_rate_type in varchar2
515 ,p_tp_safeguarded_rate_id in number
516 ,p_tp_spinal_point_id in number
517 ,p_tp_elected_pension in varchar2
518 ,p_tp_fast_track in varchar2
519 ,p_aat_attribute_category in varchar2
520 ,p_aat_attribute1 in varchar2
521 ,p_aat_attribute2 in varchar2
522 ,p_aat_attribute3 in varchar2
523 ,p_aat_attribute4 in varchar2
524 ,p_aat_attribute5 in varchar2
525 ,p_aat_attribute6 in varchar2
526 ,p_aat_attribute7 in varchar2
527 ,p_aat_attribute8 in varchar2
528 ,p_aat_attribute9 in varchar2
529 ,p_aat_attribute10 in varchar2
530 ,p_aat_attribute11 in varchar2
531 ,p_aat_attribute12 in varchar2
532 ,p_aat_attribute13 in varchar2
533 ,p_aat_attribute14 in varchar2
534 ,p_aat_attribute15 in varchar2
535 ,p_aat_attribute16 in varchar2
536 ,p_aat_attribute17 in varchar2
537 ,p_aat_attribute18 in varchar2
538 ,p_aat_attribute19 in varchar2
539 ,p_aat_attribute20 in varchar2
540 ,p_aat_information_category in varchar2
541 ,p_aat_information1 in varchar2
542 ,p_aat_information2 in varchar2
543 ,p_aat_information3 in varchar2
544 ,p_aat_information4 in varchar2
545 ,p_aat_information5 in varchar2
546 ,p_aat_information6 in varchar2
547 ,p_aat_information7 in varchar2
548 ,p_aat_information8 in varchar2
549 ,p_aat_information9 in varchar2
550 ,p_aat_information10 in varchar2
551 ,p_aat_information11 in varchar2
552 ,p_aat_information12 in varchar2
553 ,p_aat_information13 in varchar2
554 ,p_aat_information14 in varchar2
555 ,p_aat_information15 in varchar2
556 ,p_aat_information16 in varchar2
557 ,p_aat_information17 in varchar2
558 ,p_aat_information18 in varchar2
559 ,p_aat_information19 in varchar2
560 ,p_aat_information20 in varchar2
561 ,p_lgps_process_flag in varchar2
562 ,p_lgps_exclusion_type in varchar2
563 ,p_lgps_pensionable_pay in varchar2
564 ,p_lgps_trans_arrang_flag in varchar2
565 ,p_lgps_membership_number in varchar2
566 )
567 Return g_rec_type;
568 --
569 end pqp_aat_shd;
[Home] [Help]