1 Package pqp_val_shd as
2 /* $Header: pqvalrhi.pkh 120.0.12010000.1 2008/07/28 11:25:43 appldev ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 g_message VARCHAR2(80);
9 Type g_rec_type Is Record
10 (vehicle_allocation_id number(10)
11 ,effective_start_date date
12 ,effective_end_date date
13 ,assignment_id number(10)
14 ,business_group_id number(15)
15 ,across_assignments varchar2(30)
16 ,vehicle_repository_id number(10)
17 ,usage_type varchar2(30)
18 ,capital_contribution number(11,2)
19 ,private_contribution number(11,2)
20 ,default_vehicle varchar2(9) -- Increased length
21 ,fuel_card varchar2(30)
22 ,fuel_card_number varchar2(30)
23 ,calculation_method varchar2(80)
24 ,rates_table_id number(10)
25 ,element_type_id number(10)
26 ,private_use_flag varchar2(30)
27 ,insurance_number varchar2(80)
28 ,insurance_expiry_date date
29 ,val_attribute_category varchar2(30)
30 ,val_attribute1 varchar2(150)
31 ,val_attribute2 varchar2(150)
32 ,val_attribute3 varchar2(150)
33 ,val_attribute4 varchar2(150)
34 ,val_attribute5 varchar2(150)
35 ,val_attribute6 varchar2(150)
36 ,val_attribute7 varchar2(150)
37 ,val_attribute8 varchar2(150)
38 ,val_attribute9 varchar2(150)
39 ,val_attribute10 varchar2(150)
40 ,val_attribute11 varchar2(150)
41 ,val_attribute12 varchar2(150)
42 ,val_attribute13 varchar2(150)
43 ,val_attribute14 varchar2(150)
44 ,val_attribute15 varchar2(150)
45 ,val_attribute16 varchar2(150)
46 ,val_attribute17 varchar2(150)
47 ,val_attribute18 varchar2(150)
48 ,val_attribute19 varchar2(150)
49 ,val_attribute20 varchar2(150)
50 ,val_information_category varchar2(30)
51 ,val_information1 varchar2(150)
52 ,val_information2 varchar2(150)
53 ,val_information3 varchar2(150)
54 ,val_information4 varchar2(150)
55 ,val_information5 varchar2(150)
56 ,val_information6 varchar2(150)
57 ,val_information7 varchar2(150)
58 ,val_information8 varchar2(150)
59 ,val_information9 varchar2(150)
60 ,val_information10 varchar2(150)
61 ,val_information11 varchar2(150)
62 ,val_information12 varchar2(150)
63 ,val_information13 varchar2(150)
64 ,val_information14 varchar2(150)
65 ,val_information15 varchar2(150)
66 ,val_information16 varchar2(150)
67 ,val_information17 varchar2(150)
68 ,val_information18 varchar2(150)
69 ,val_information19 varchar2(150)
70 ,val_information20 varchar2(150)
71 ,object_version_number number(9)
72 ,fuel_benefit varchar2(30)
73 ,sliding_rates_info varchar2(80)
74 );
75 --
76 -- ----------------------------------------------------------------------------
77 -- | Global Definitions - Internal Development Use Only |
78 -- ----------------------------------------------------------------------------
79 --
80 g_old_rec g_rec_type; -- Global record definition
81 -- Global table name
82 g_tab_nam constant varchar2(30) := 'PQP_VEHICLE_ALLOCATIONS_F';
83
84 --
85 -- ----------------------------------------------------------------------------
86 -- |---------------------------< constraint_error >---------------------------|
87 -- ----------------------------------------------------------------------------
88 -- {Start Of Comments}
89 --
90 -- Description:
91 -- This procedure is called when a constraint has been violated (i.e.
92 -- The exception hr_api.check_integrity_violated,
93 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
94 -- hr_api.unique_integrity_violated has been raised).
95 -- The exceptions can only be raised as follows:
96 -- 1) A check constraint can only be violated during an INSERT or UPDATE
97 -- dml operation.
98 -- 2) A parent integrity constraint can only be violated during an
99 -- INSERT or UPDATE dml operation.
100 -- 3) A child integrity constraint can only be violated during an
101 -- DELETE dml operation.
102 -- 4) A unique integrity constraint can only be violated during INSERT or
103 -- UPDATE dml operation.
104 --
105 -- Prerequisites:
106 -- 1) Either hr_api.check_integrity_violated,
107 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
108 -- hr_api.unique_integrity_violated has been raised with the subsequent
109 -- stripping of the constraint name from the generated error message
110 -- text.
111 -- 2) Standalone validation test which corresponds with a constraint error.
112 --
113 -- In Parameter:
114 -- p_constraint_name is in upper format and is just the constraint name
115 -- (e.g. not prefixed by brackets, schema owner etc).
116 --
117 -- Post Success:
118 -- Development dependant.
119 --
120 -- Post Failure:
121 -- Developement dependant.
122 --
123 -- Developer Implementation Notes:
124 -- For each constraint being checked the hr system package failure message
125 -- has been generated as a template only. These system error messages should
126 -- be modified as required (i.e. change the system failure message to a user
127 -- friendly defined error message).
128 --
129 -- Access Status:
130 -- Internal Development Use Only.
131 --
132 -- {End Of Comments}
133 -- ----------------------------------------------------------------------------
134 Procedure constraint_error
135 (p_constraint_name in all_constraints.constraint_name%TYPE);
136 --
137 -- ----------------------------------------------------------------------------
138 -- |-----------------------------< api_updating >-----------------------------|
139 -- ----------------------------------------------------------------------------
140 -- {Start Of Comments}
141 --
142 -- Description:
143 -- This function is used to populate the g_old_rec record with the
144 -- current row from the database for the specified primary key
145 -- provided that the primary key exists and is valid and does not
146 -- already match the current g_old_rec. The function will always return
147 -- a TRUE value if the g_old_rec is populated with the current row.
148 -- A FALSE value will be returned if all of the primary key arguments
149 -- are null.
150 --
151 -- Prerequisites:
152 -- None.
153 --
154 -- In Parameters:
155 --
156 -- Post Success:
157 -- A value of TRUE will be returned indiciating that the g_old_rec
158 -- is current.
159 -- A value of FALSE will be returned if all of the primary key arguments
160 -- have a null value (this indicates that the row has not be inserted into
161 -- the Schema), and therefore could never have a corresponding row.
162 --
163 -- Post Failure:
164 -- A failure can only occur under two circumstances:
165 -- 1) The primary key is invalid (i.e. a row does not exist for the
166 -- specified primary key values).
167 -- 2) If an object_version_number exists but is NOT the same as the current
168 -- g_old_rec value.
169 --
170 -- Developer Implementation Notes:
171 -- None.
172 --
173 -- Access Status:
174 -- Internal Development Use Only.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 Function api_updating
179 (p_effective_date in date
180 ,p_vehicle_allocation_id in number
181 ,p_object_version_number in number
182 ) Return Boolean;
183 --
184 ------------------------------------------------------------------------------
185 --This function returns global value.
186 Function pqp_get_global_msg_value return
187 varchar2 ;
188
189 ------------------------------------------------------------------------------
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< find_dt_upd_modes >--------------------------|
192 -- ----------------------------------------------------------------------------
193 -- {Start Of Comments}
194 --
195 -- Description:
196 -- This procedure is used to determine what datetrack update modes are
197 -- allowed as of the effective date for this entity. The procedure will
198 -- return a corresponding Boolean value for each of the update modes
199 -- available where TRUE indicates that the corresponding update mode
200 -- is available.
201 --
202 -- Prerequisites:
203 -- None.
204 --
205 -- In Parameters:
206 -- p_effective_date
207 -- Specifies the date at which the datetrack modes will be operated on.
208 -- p_base_key_value
209 -- Specifies the primary key value for this datetrack entity.
210 -- (E.g. For this entity the assignment of the argument would be:
211 -- p_base_key_value = :vehicle_allocation_id).
212 --
213 -- Post Success:
214 -- Processing continues.
215 --
216 -- Post Failure:
217 -- Failure might occur if for the specified effective date and primary key
218 -- value a row doesn't exist.
219 --
220 -- Developer Implementation Notes:
221 -- This procedure could require changes if this entity has any sepcific
222 -- delete restrictions.
223 -- For example, this entity might disallow the datetrack update mode of
224 -- UPDATE. To implement this you would have to set and return a Boolean
225 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
226 --
227 -- Access Status:
228 -- Internal Development Use Only.
229 --
230 -- {End Of Comments}
231 -- ----------------------------------------------------------------------------
232 Procedure find_dt_upd_modes
233 (p_effective_date in date
234 ,p_base_key_value in number
235 ,p_correction out nocopy boolean
236 ,p_update out nocopy boolean
237 ,p_update_override out nocopy boolean
238 ,p_update_change_insert out nocopy boolean
239 );
240 --
241 -- ----------------------------------------------------------------------------
242 -- |---------------------------< find_dt_del_modes >--------------------------|
243 -- ----------------------------------------------------------------------------
244 -- {Start Of Comments}
245 --
246 -- Description:
247 -- This procedure is used to determine what datetrack delete modes are
248 -- allowed as of the effective date for this entity. The procedure will
249 -- return a corresponding Boolean value for each of the delete modes
250 -- available where TRUE indicates that the corresponding delete mode is
251 -- available.
252 --
253 -- Prerequisites:
254 -- None.
255 --
256 -- In Parameters:
257 -- p_effective_date
258 -- Specifies the date at which the datetrack modes will be operated on.
259 -- p_base_key_value
260 -- Specifies the primary key value for this datetrack entity.
261 -- (E.g. For this entity the assignment of the argument would be:
262 -- p_base_key_value = :vehicle_allocation_id).
263 --
264 -- Post Success:
265 -- Processing continues.
266 --
267 -- Post Failure:
268 -- Failure might occur if for the specified effective date and primary key
269 -- value a row doesn't exist.
270 --
271 -- Developer Implementation Notes:
272 -- This procedure could require changes if this entity has any sepcific
273 -- delete restrictions.
274 -- For example, this entity might disallow the datetrack delete mode of
275 -- ZAP. To implement this you would have to set and return a Boolean value
276 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
277 --
278 -- Access Status:
279 -- Internal Development Use Only.
280 --
281 -- {End Of Comments}
282 -- ----------------------------------------------------------------------------
283 Procedure find_dt_del_modes
284 (p_effective_date in date
285 ,p_base_key_value in number
286 ,p_zap out nocopy boolean
287 ,p_delete out nocopy boolean
288 ,p_future_change out nocopy boolean
289 ,p_delete_next_change out nocopy boolean
290 );
291 --
292 -- ----------------------------------------------------------------------------
293 -- |-----------------------< upd_effective_end_date >-------------------------|
294 -- ----------------------------------------------------------------------------
295 -- {Start Of Comments}
296 --
297 -- Description:
298 -- This procedure will update the specified datetrack row with the
299 -- specified new effective end date. The object version number is also
300 -- set to the next object version number. DateTrack modes which call
301 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
302 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
303 -- This is an internal datetrack maintenance procedure which should
304 -- not be modified in anyway.
305 --
306 -- Prerequisites:
307 -- None.
308 --
309 -- In Parameters:
310 -- p_new_effective_end_date
311 -- Specifies the new effective end date which will be set for the
312 -- row as of the effective date.
313 -- p_base_key_value
314 -- Specifies the primary key value for this datetrack entity.
315 -- (E.g. For this entity the assignment of the argument would be:
316 -- p_base_key_value = :vehicle_allocation_id).
317 --
318 -- Post Success:
319 -- The specified row will be updated with the new effective end date and
320 -- object_version_number.
321 --
322 -- Post Failure:
323 -- Failure might occur if for the specified effective date and primary key
324 -- value a row doesn't exist.
325 --
326 -- Developer Implementation Notes:
327 -- This is an internal datetrack maintenance procedure which should
328 -- not be modified in anyway.
329 --
330 -- Access Status:
331 -- Internal Row Handler Use Only.
332 --
333 -- {End Of Comments}
334 -- ----------------------------------------------------------------------------
335 Procedure upd_effective_end_date
336 (p_effective_date in date
337 ,p_base_key_value in number
338 ,p_new_effective_end_date in date
339 ,p_validation_start_date in date
340 ,p_validation_end_date in date
341 ,p_object_version_number out nocopy number
342 );
343 --
344 -- ----------------------------------------------------------------------------
345 -- |---------------------------------< lck >----------------------------------|
346 -- ----------------------------------------------------------------------------
347 -- {Start Of Comments}
348 --
349 -- Description:
350 -- The Lck process for datetrack is complicated and comprises of the
351 -- following processing
352 -- The processing steps are as follows:
353 -- 1) The row to be updated or deleted must be locked.
354 -- By locking this row, the g_old_rec record data type is populated.
355 -- 2) If a comment exists the text is selected from hr_comments.
356 -- 3) The datetrack mode is then validated to ensure the operation is
357 -- valid. If the mode is valid the validation start and end dates for
358 -- the mode will be derived and returned. Any required locking is
359 -- completed when the datetrack mode is validated.
360 --
361 -- Prerequisites:
365 -- In Parameters:
362 -- When attempting to call the lck procedure the object version number,
363 -- primary key, effective date and datetrack mode must be specified.
364 --
366 -- p_effective_date
367 -- Specifies the date of the datetrack update operation.
368 -- p_datetrack_mode
369 -- Determines the datetrack update or delete mode.
370 --
371 -- Post Success:
372 -- On successful completion of the Lck process the row to be updated or
373 -- deleted will be locked and selected into the global data structure
374 -- g_old_rec.
375 --
376 -- Post Failure:
377 -- The Lck process can fail for three reasons:
378 -- 1) When attempting to lock the row the row could already be locked by
379 -- another user. This will raise the HR_Api.Object_Locked exception.
380 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
381 -- This error is trapped and reported using the message name
382 -- 'HR_7220_INVALID_PRIMARY_KEY'.
383 -- 3) The row although existing in the HR Schema has a different object
384 -- version number than the object version number specified.
385 -- This error is trapped and reported using the message name
386 -- 'HR_7155_OBJECT_INVALID'.
387 --
388 -- Developer Implementation Notes:
389 -- None.
390 --
391 -- Access Status:
392 -- Internal Development Use Only.
393 --
394 -- {End Of Comments}
395 -- ----------------------------------------------------------------------------
396 Procedure lck
397 (p_effective_date in date
398 ,p_datetrack_mode in varchar2
399 ,p_vehicle_allocation_id in number
400 ,p_object_version_number in number
401 ,p_validation_start_date out nocopy date
402 ,p_validation_end_date out nocopy date
403 );
404 --
405 -- ----------------------------------------------------------------------------
406 -- |-----------------------------< convert_args >-----------------------------|
407 -- ----------------------------------------------------------------------------
408 -- {Start Of Comments}
409 --
410 -- Description:
411 -- This function is used to turn attribute parameters into the record
412 -- structure parameter g_rec_type.
413 --
414 -- Prerequisites:
415 -- This is a private function and can only be called from the ins or upd
416 -- attribute processes.
417 --
418 -- In Parameters:
419 --
420 -- Post Success:
421 -- A returning record structure will be returned.
422 --
423 -- Post Failure:
424 -- No direct error handling is required within this function. Any possible
425 -- errors within this function will be a PL/SQL value error due to
426 -- conversion of datatypes or data lengths.
427 --
428 -- Developer Implementation Notes:
429 -- None.
430 --
431 -- Access Status:
432 -- Internal Row Handler Use Only.
433 --
434 -- {End Of Comments}
435 -- ----------------------------------------------------------------------------
436 Function convert_args
437 (p_vehicle_allocation_id in number
438 ,p_effective_start_date in date
439 ,p_effective_end_date in date
440 ,p_assignment_id in number
441 ,p_business_group_id in number
442 ,p_across_assignments in varchar2
443 ,p_vehicle_repository_id in number
444 ,p_usage_type in varchar2
445 ,p_capital_contribution in number
446 ,p_private_contribution in number
447 ,p_default_vehicle in varchar2
448 ,p_fuel_card in varchar2
449 ,p_fuel_card_number in varchar2
450 ,p_calculation_method in varchar2
451 ,p_rates_table_id in number
452 ,p_element_type_id in number
453 ,p_private_use_flag in varchar2
454 ,p_insurance_number in varchar2
455 ,p_insurance_expiry_date in date
456 ,p_val_attribute_category in varchar2
457 ,p_val_attribute1 in varchar2
458 ,p_val_attribute2 in varchar2
459 ,p_val_attribute3 in varchar2
460 ,p_val_attribute4 in varchar2
461 ,p_val_attribute5 in varchar2
462 ,p_val_attribute6 in varchar2
463 ,p_val_attribute7 in varchar2
464 ,p_val_attribute8 in varchar2
465 ,p_val_attribute9 in varchar2
466 ,p_val_attribute10 in varchar2
467 ,p_val_attribute11 in varchar2
468 ,p_val_attribute12 in varchar2
469 ,p_val_attribute13 in varchar2
470 ,p_val_attribute14 in varchar2
471 ,p_val_attribute15 in varchar2
472 ,p_val_attribute16 in varchar2
473 ,p_val_attribute17 in varchar2
474 ,p_val_attribute18 in varchar2
475 ,p_val_attribute19 in varchar2
476 ,p_val_attribute20 in varchar2
477 ,p_val_information_category in varchar2
478 ,p_val_information1 in varchar2
479 ,p_val_information2 in varchar2
480 ,p_val_information3 in varchar2
481 ,p_val_information4 in varchar2
482 ,p_val_information5 in varchar2
483 ,p_val_information6 in varchar2
484 ,p_val_information7 in varchar2
485 ,p_val_information8 in varchar2
486 ,p_val_information9 in varchar2
487 ,p_val_information10 in varchar2
488 ,p_val_information11 in varchar2
489 ,p_val_information12 in varchar2
490 ,p_val_information13 in varchar2
491 ,p_val_information14 in varchar2
492 ,p_val_information15 in varchar2
493 ,p_val_information16 in varchar2
494 ,p_val_information17 in varchar2
495 ,p_val_information18 in varchar2
496 ,p_val_information19 in varchar2
497 ,p_val_information20 in varchar2
498 ,p_object_version_number in number
499 ,p_fuel_benefit in varchar2
500 ,p_sliding_rates_info in varchar2
501 )
502 Return g_rec_type;
503 --
504 end pqp_val_shd;