1 Package pay_ppm_shd AUTHID CURRENT_USER as
2 /* $Header: pyppmrhi.pkh 120.1 2008/12/05 13:44:37 abanand ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (
10 personal_payment_method_id pay_personal_payment_methods_f.personal_payment_method_id%TYPE, -- Bug 7499474
11 effective_start_date pay_personal_payment_methods_f.effective_start_date%TYPE,
12 effective_end_date pay_personal_payment_methods_f.effective_end_date%TYPE,
13 business_group_id pay_personal_payment_methods_f.business_group_id%TYPE,
14 external_account_id pay_personal_payment_methods_f.external_account_id%TYPE,
15 assignment_id pay_personal_payment_methods_f.assignment_id%TYPE,
16 run_type_id pay_personal_payment_methods_f.run_type_id%TYPE,
17 org_payment_method_id pay_personal_payment_methods_f.org_payment_method_id%TYPE,
18 amount pay_personal_payment_methods_f.amount%TYPE,
19 comment_id pay_personal_payment_methods_f.comment_id%TYPE,
20 comments varchar2(2000), -- pseudo column
21 percentage pay_personal_payment_methods_f.percentage%TYPE,
22 priority pay_personal_payment_methods_f.priority%TYPE,
23 attribute_category pay_personal_payment_methods_f.attribute_category%TYPE,
24 attribute1 pay_personal_payment_methods_f.attribute1%TYPE,
25 attribute2 pay_personal_payment_methods_f.attribute2%TYPE,
26 attribute3 pay_personal_payment_methods_f.attribute3%TYPE,
27 attribute4 pay_personal_payment_methods_f.attribute4%TYPE,
28 attribute5 pay_personal_payment_methods_f.attribute5%TYPE,
29 attribute6 pay_personal_payment_methods_f.attribute6%TYPE,
30 attribute7 pay_personal_payment_methods_f.attribute7%TYPE,
31 attribute8 pay_personal_payment_methods_f.attribute8%TYPE,
32 attribute9 pay_personal_payment_methods_f.attribute9%TYPE,
33 attribute10 pay_personal_payment_methods_f.attribute10%TYPE,
34 attribute11 pay_personal_payment_methods_f.attribute11%TYPE,
35 attribute12 pay_personal_payment_methods_f.attribute12%TYPE,
36 attribute13 pay_personal_payment_methods_f.attribute13%TYPE,
37 attribute14 pay_personal_payment_methods_f.attribute14%TYPE,
38 attribute15 pay_personal_payment_methods_f.attribute15%TYPE,
39 attribute16 pay_personal_payment_methods_f.attribute16%TYPE,
40 attribute17 pay_personal_payment_methods_f.attribute17%TYPE,
41 attribute18 pay_personal_payment_methods_f.attribute18%TYPE,
42 attribute19 pay_personal_payment_methods_f.attribute19%TYPE,
43 attribute20 pay_personal_payment_methods_f.attribute20%TYPE,
44 object_version_number pay_personal_payment_methods_f.object_version_number%TYPE,
45 payee_type pay_personal_payment_methods_f.payee_type%TYPE,
46 payee_id pay_personal_payment_methods_f.payee_id%TYPE,
47 ppm_information_category pay_personal_payment_methods_f.ppm_information_category%TYPE,
48 ppm_information1 pay_personal_payment_methods_f.ppm_information1%TYPE,
49 ppm_information2 pay_personal_payment_methods_f.ppm_information2%TYPE,
50 ppm_information3 pay_personal_payment_methods_f.ppm_information3%TYPE,
51 ppm_information4 pay_personal_payment_methods_f.ppm_information4%TYPE,
52 ppm_information5 pay_personal_payment_methods_f.ppm_information5%TYPE,
53 ppm_information6 pay_personal_payment_methods_f.ppm_information6%TYPE,
54 ppm_information7 pay_personal_payment_methods_f.ppm_information7%TYPE,
55 ppm_information8 pay_personal_payment_methods_f.ppm_information8%TYPE,
56 ppm_information9 pay_personal_payment_methods_f.ppm_information9%TYPE,
57 ppm_information10 pay_personal_payment_methods_f.ppm_information10%TYPE,
58 ppm_information11 pay_personal_payment_methods_f.ppm_information11%TYPE,
59 ppm_information12 pay_personal_payment_methods_f.ppm_information12%TYPE,
60 ppm_information13 pay_personal_payment_methods_f.ppm_information13%TYPE,
61 ppm_information14 pay_personal_payment_methods_f.ppm_information14%TYPE,
62 ppm_information15 pay_personal_payment_methods_f.ppm_information15%TYPE,
63 ppm_information16 pay_personal_payment_methods_f.ppm_information16%TYPE,
64 ppm_information17 pay_personal_payment_methods_f.ppm_information17%TYPE,
65 ppm_information18 pay_personal_payment_methods_f.ppm_information18%TYPE,
66 ppm_information19 pay_personal_payment_methods_f.ppm_information19%TYPE,
67 ppm_information20 pay_personal_payment_methods_f.ppm_information20%TYPE,
68 ppm_information21 pay_personal_payment_methods_f.ppm_information21%TYPE,
69 ppm_information22 pay_personal_payment_methods_f.ppm_information22%TYPE,
70 ppm_information23 pay_personal_payment_methods_f.ppm_information23%TYPE,
71 ppm_information24 pay_personal_payment_methods_f.ppm_information24%TYPE,
72 ppm_information25 pay_personal_payment_methods_f.ppm_information25%TYPE,
73 ppm_information26 pay_personal_payment_methods_f.ppm_information26%TYPE,
74 ppm_information27 pay_personal_payment_methods_f.ppm_information27%TYPE,
75 ppm_information28 pay_personal_payment_methods_f.ppm_information28%TYPE,
76 ppm_information29 pay_personal_payment_methods_f.ppm_information29%TYPE,
77 ppm_information30 pay_personal_payment_methods_f.ppm_information30%TYPE
78 );
79 --
80 -- ----------------------------------------------------------------------------
81 -- | Global Definitions - Internal Development Use Only |
82 -- ----------------------------------------------------------------------------
83 --
84 g_old_rec g_rec_type; -- Global record definition
85 g_api_dml boolean; -- Global api dml status
86 --
87 -- ----------------------------------------------------------------------------
88 -- |------------------------< return_api_dml_status >-------------------------|
89 -- ----------------------------------------------------------------------------
90 -- {Start Of Comments}
91 --
92 -- Description:
93 -- This function will return the current g_api_dml private global
94 -- boolean status.
95 -- The g_api_dml status determines if at the time of the function
96 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
97 -- is being issued from within an api.
98 -- If the status is TRUE then a dml statement is being issued from
99 -- within this entity api.
100 -- This function is primarily to support database triggers which
101 -- need to maintain the object_version_number for non-supported
102 -- dml statements (i.e. dml statement issued outside of the api layer).
103 --
104 -- Pre Conditions:
105 -- None.
106 --
107 -- In Arguments:
108 -- None.
109 --
110 -- Post Success:
111 -- Processing continues.
112 -- If the function returns a TRUE value then, dml is being executed from
113 -- within this api.
114 --
115 -- Post Failure:
116 -- None.
117 --
118 -- Access Status:
119 -- Internal Table Handler Use Only.
120 --
121 -- {End Of Comments}
122 -- ----------------------------------------------------------------------------
123 Function return_api_dml_status Return Boolean;
124 --
125 -- ----------------------------------------------------------------------------
126 -- |---------------------------< constraint_error >---------------------------|
127 -- ----------------------------------------------------------------------------
128 -- {Start Of Comments}
129 --
130 -- Description:
131 -- This procedure is called when a constraint has been violated (i.e.
132 -- The exception hr_api.check_integrity_violated,
133 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
134 -- hr_api.unique_integrity_violated has been raised).
135 -- The exceptions can only be raised as follows:
136 -- 1) A check constraint can only be violated during an INSERT or UPDATE
137 -- dml operation.
138 -- 2) A parent integrity constraint can only be violated during an
139 -- INSERT or UPDATE dml operation.
140 -- 3) A child integrity constraint can only be violated during an
141 -- DELETE dml operation.
142 -- 4) A unique integrity constraint can only be violated during INSERT or
143 -- UPDATE dml operation.
144 --
145 -- Pre Conditions:
146 -- 1) Either hr_api.check_integrity_violated,
147 -- hr_api.parent_integrity_violated, hr_api.child_integrity_violated or
148 -- hr_api.unique_integrity_violated has been raised with the subsequent
149 -- stripping of the constraint name from the generated error message
150 -- text.
151 -- 2) Standalone validation test which correspond with a constraint error.
152 --
153 -- In Arguments:
154 -- p_constraint_name is in upper format and is just the constraint name
155 -- (e.g. not prefixed by brackets, schema owner etc).
156 --
157 -- Post Success:
158 -- Development dependant.
159 --
160 -- Post Failure:
161 -- Developement dependant.
162 --
163 -- Developer Implementation Notes:
164 -- For each constraint being checked the hr system package failure message
165 -- has been generated as a template only. These system error messages should
166 -- be modified as required (i.e. change the system failure message to a user
167 -- friendly defined error message).
168 --
169 -- Access Status:
170 -- Internal Development Use Only.
171 --
172 -- {End Of Comments}
173 -- ----------------------------------------------------------------------------
174 Procedure constraint_error
175 (p_constraint_name in all_constraints.constraint_name%TYPE);
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------------< api_updating >-----------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This function is used to populate the g_old_rec record with the current
184 -- row from the database for the specified primary key provided that the
185 -- primary key exists and is valid and does not already match the current
186 -- g_old_rec.
187 -- The function will always return a TRUE value if the g_old_rec is
188 -- populated with the current row. A FALSE value will be returned if all of
189 -- the primary key arguments are null.
190 --
191 -- Pre Conditions:
192 -- None.
193 --
194 -- In Arguments:
195 --
196 -- Post Success:
197 -- A value of TRUE will be returned indiciating that the g_old_rec is
198 -- current.
199 -- A value of FALSE will be returned if all of the primary key arguments
200 -- have a null value (this indicates that the row has not be inserted into
201 -- the Schema), and therefore could never have a corresponding row.
202 --
203 -- Post Failure:
204 -- A failure can only occur under two circumstances:
205 -- 1) The primary key is invalid (i.e. a row does not exist for the
206 -- specified primary key values).
207 -- 2) If an object_version_number exists but is NOT the same as the current
208 -- g_old_rec value.
209 --
210 -- Developer Implementation Notes:
211 -- None.
212 --
213 -- Access Status:
214 -- Internal Table Handler Use Only.
215 --
216 -- {End Of Comments}
217 -- ----------------------------------------------------------------------------
218 Function api_updating
219 (p_effective_date in date,
220 p_personal_payment_method_id in number,
221 p_object_version_number in number
222 ) Return Boolean;
223 --
224 -- ----------------------------------------------------------------------------
225 -- |--------------------------< find_dt_del_modes >---------------------------|
226 -- ----------------------------------------------------------------------------
227 -- {Start Of Comments}
228 --
229 -- Description:
230 -- This procedure is used to determine what datetrack delete modes are
231 -- allowed as of the effective date for this entity. The procedure will
232 -- return a corresponding Boolean value for each of the delete modes
233 -- available where TRUE indicates that the corresponding delete mode is
234 -- available.
235 --
236 -- Pre Conditions:
237 -- None.
238 --
239 -- In Arguments:
240 -- p_effective_date
241 -- Specifies the date at which the datetrack modes will be operated on.
242 -- p_base_key_value
243 -- Specifies the primary key value for this datetrack entity.
244 -- (E.g. For this entity the assignment of the argument would be:
245 -- p_base_key_value = :personal_payment_method_id).
246 --
247 -- Post Success:
248 -- Processing continues.
249 --
250 -- Post Failure:
251 -- Failure might occur if for the specified effective date and primary key
252 -- value a row doesn't exist.
253 --
254 -- Developer Implementation Notes:
255 -- This procedure could require changes if this entity has any sepcific
256 -- delete restrictions.
257 -- For example, this entity might disallow the datetrack delete mode of
258 -- ZAP. To implement this you would have to set and return a Boolean value
259 -- of FALSE after the call to the dt_api.find_dt_del_modes procedure.
260 --
261 -- Access Status:
262 -- Internal Development Use Only.
263 --
264 -- {End Of Comments}
265 -- ----------------------------------------------------------------------------
266 Procedure find_dt_del_modes
267 (p_effective_date in date,
268 p_base_key_value in number,
269 p_zap out nocopy boolean,
270 p_delete out nocopy boolean,
271 p_future_change out nocopy boolean,
272 p_delete_next_change out nocopy boolean);
273 --
274 -- ----------------------------------------------------------------------------
275 -- |--------------------------< find_dt_upd_modes >---------------------------|
276 -- ----------------------------------------------------------------------------
277 -- {Start Of Comments}
278 --
279 -- Description:
280 -- This procedure is used to determine what datetrack update modes are
281 -- allowed as of the effective date for this entity. The procedure will
282 -- return a corresponding Boolean value for each of the update modes
283 -- available where TRUE indicates that the corresponding update mode
284 -- is available.
285 --
286 -- Pre Conditions:
287 -- None.
288 --
289 -- In Arguments:
290 -- p_effective_date
291 -- Specifies the date at which the datetrack modes will be operated on.
292 -- p_base_key_value
293 -- Specifies the primary key value for this datetrack entity.
294 -- (E.g. For this entity the assignment of the argument would be:
295 -- p_base_key_value = :personal_payment_method_id).
296 --
297 -- Post Success:
298 -- Processing continues.
299 --
300 -- Post Failure:
301 -- Failure might occur if for the specified effective date and primary key
302 -- value a row doesn't exist.
303 --
304 -- Developer Implementation Notes:
305 -- This procedure could require changes if this entity has any sepcific
306 -- delete restrictions.
307 -- For example, this entity might disallow the datetrack update mode of
308 -- UPDATE. To implement this you would have to set and return a Boolean
309 -- value of FALSE after the call to the dt_api.find_dt_upd_modes procedure.
310 --
311 -- Access Status:
312 -- Internal Development Use Only.
313 --
314 -- {End Of Comments}
315 -- ----------------------------------------------------------------------------
316 Procedure find_dt_upd_modes
320 p_update out nocopy boolean,
317 (p_effective_date in date,
318 p_base_key_value in number,
319 p_correction out nocopy boolean,
321 p_update_override out nocopy boolean,
322 p_update_change_insert out nocopy boolean);
323 --
324 -- ----------------------------------------------------------------------------
325 -- |------------------------< upd_effective_end_date >------------------------|
326 -- ----------------------------------------------------------------------------
327 -- {Start Of Comments}
328 --
329 -- Description:
330 -- This procedure will update the specified datetrack row with the
331 -- specified new effective end date. The object version number is also
332 -- set to the next object version number. DateTrack modes which call
333 -- this procedure are: UPDATE, UPDATE_CHANGE_INSERT,
334 -- UPDATE_OVERRIDE, DELETE, FUTURE_CHANGE and DELETE_NEXT_CHANGE.
335 -- This is an internal datetrack maintenance procedure which should
336 -- not be modified in anyway.
337 --
338 -- Pre Conditions:
339 -- None.
340 --
341 -- In Arguments:
342 -- p_new_effective_end_date
343 -- Specifies the new effective end date which will be set for the
344 -- row as of the effective date.
345 -- p_base_key_value
346 -- Specifies the primary key value for this datetrack entity.
347 -- (E.g. For this entity the assignment of the argument would be:
348 -- p_base_key_value = :personal_payment_method_id).
349 --
350 -- Post Success:
351 -- The specified row will be updated with the new effective end date and
352 -- object_version_number.
353 --
354 -- Post Failure:
355 -- Failure might occur if for the specified effective date and primary key
356 -- value a row doesn't exist.
357 --
358 -- Developer Implementation Notes:
359 -- This is an internal datetrack maintenance procedure which should
360 -- not be modified in anyway.
361 --
362 -- Access Status:
363 -- Internal Table Handler Use Only.
364 --
365 -- {End Of Comments}
366 -- ----------------------------------------------------------------------------
367 Procedure upd_effective_end_date
368 (p_effective_date in date,
369 p_base_key_value in number,
370 p_new_effective_end_date in date,
371 p_validation_start_date in date,
372 p_validation_end_date in date,
373 p_object_version_number out nocopy number);
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------------< lck >----------------------------------|
377 -- ----------------------------------------------------------------------------
378 -- {Start Of Comments}
379 --
380 -- Description:
381 -- The Lck process for datetrack is complicated and comprises of the
382 -- following processing
383 -- The processing steps are as follows:
384 -- 1) The row to be updated or deleted must be locked.
385 -- By locking this row, the g_old_rec record data type is populated.
386 -- 2) If a comment exists the text is selected from hr_comments.
387 -- 3) The datetrack mode is then validated to ensure the operation is
388 -- valid. If the mode is valid the validation start and end dates for
389 -- the mode will be derived and returned. Any required locking is
390 -- completed when the datetrack mode is validated.
391 --
392 -- Pre Conditions:
393 -- When attempting to call the lck procedure the object version number,
394 -- primary key, effective date and datetrack mode must be specified.
395 --
396 -- In Arguments:
397 -- p_effective_date
398 -- Specifies the date of the datetrack update operation.
399 -- p_datetrack_mode
400 -- Determines the datetrack update or delete mode.
401 --
402 -- Post Success:
403 -- On successful completion of the Lck process the row to be updated or
404 -- deleted will be locked and selected into the global data structure
405 -- g_old_rec.
406 --
407 -- Post Failure:
408 -- The Lck process can fail for three reasons:
409 -- 1) When attempting to lock the row the row could already be locked by
410 -- another user. This will raise the HR_Api.Object_Locked exception.
411 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
412 -- This error is trapped and reported using the message name
413 -- 'HR_7220_INVALID_PRIMARY_KEY'.
414 -- 3) The row although existing in the HR Schema has a different object
415 -- version number than the object version number specified.
416 -- This error is trapped and reported using the message name
417 -- 'HR_7155_OBJECT_INVALID'.
418 --
419 -- Developer Implementation Notes:
420 -- None.
421 --
422 -- Access Status:
423 -- Internal Development Use Only.
424 --
425 -- {End Of Comments}
426 -- ----------------------------------------------------------------------------
427 Procedure lck
428 (p_effective_date in date,
429 p_datetrack_mode in varchar2,
430 p_personal_payment_method_id in number,
431 p_object_version_number in number,
432 p_validation_start_date out nocopy date,
433 p_validation_end_date out nocopy date);
434 --
435 -- ----------------------------------------------------------------------------
436 -- |-----------------------------< convert_args >-----------------------------|
437 -- ----------------------------------------------------------------------------
438 -- {Start Of Comments}
439 --
440 -- Description:
444 -- Pre Conditions:
441 -- This function is used to turn attribute arguments into the record
442 -- structure g_rec_type.
443 --
445 -- This is a private function and can only be called from the ins or upd
446 -- attribute processes.
447 --
448 -- In Arguments:
449 --
450 -- Post Success:
451 -- A returning record structure will be returned.
452 --
453 -- Post Failure:
454 -- No direct error handling is required within this function. Any possible
455 -- errors within this function will be a PL/SQL value error due to conversion
456 -- of datatypes or data lengths.
457 --
458 -- Developer Implementation Notes:
459 -- None.
460 --
461 -- Access Status:
462 -- Internal Table Handler Use Only.
463 --
464 -- {End Of Comments}
465 -- ----------------------------------------------------------------------------
466 Function convert_args
467 (
468 p_personal_payment_method_id in number,
469 p_effective_start_date in date,
470 p_effective_end_date in date,
471 p_business_group_id in number,
472 p_external_account_id in number,
473 p_assignment_id in number,
474 p_run_type_id in number,
475 p_org_payment_method_id in number,
476 p_amount in number,
477 p_comment_id in number,
478 p_comments in varchar2,
479 p_percentage in number,
480 p_priority in number,
481 p_attribute_category in varchar2,
482 p_attribute1 in varchar2,
483 p_attribute2 in varchar2,
484 p_attribute3 in varchar2,
485 p_attribute4 in varchar2,
486 p_attribute5 in varchar2,
487 p_attribute6 in varchar2,
488 p_attribute7 in varchar2,
489 p_attribute8 in varchar2,
490 p_attribute9 in varchar2,
491 p_attribute10 in varchar2,
492 p_attribute11 in varchar2,
493 p_attribute12 in varchar2,
494 p_attribute13 in varchar2,
495 p_attribute14 in varchar2,
496 p_attribute15 in varchar2,
497 p_attribute16 in varchar2,
498 p_attribute17 in varchar2,
499 p_attribute18 in varchar2,
500 p_attribute19 in varchar2,
501 p_attribute20 in varchar2,
502 p_object_version_number in number,
503 p_payee_type in varchar2,
504 p_payee_id in number,
505 p_ppm_information_category in varchar2,
506 p_ppm_information1 in varchar2,
507 p_ppm_information2 in varchar2,
508 p_ppm_information3 in varchar2,
509 p_ppm_information4 in varchar2,
510 p_ppm_information5 in varchar2,
511 p_ppm_information6 in varchar2,
512 p_ppm_information7 in varchar2,
513 p_ppm_information8 in varchar2,
514 p_ppm_information9 in varchar2,
515 p_ppm_information10 in varchar2,
516 p_ppm_information11 in varchar2,
517 p_ppm_information12 in varchar2,
518 p_ppm_information13 in varchar2,
519 p_ppm_information14 in varchar2,
520 p_ppm_information15 in varchar2,
521 p_ppm_information16 in varchar2,
522 p_ppm_information17 in varchar2,
523 p_ppm_information18 in varchar2,
524 p_ppm_information19 in varchar2,
525 p_ppm_information20 in varchar2,
526 p_ppm_information21 in varchar2,
527 p_ppm_information22 in varchar2,
528 p_ppm_information23 in varchar2,
529 p_ppm_information24 in varchar2,
530 p_ppm_information25 in varchar2,
531 p_ppm_information26 in varchar2,
532 p_ppm_information27 in varchar2,
533 p_ppm_information28 in varchar2,
534 p_ppm_information29 in varchar2,
535 p_ppm_information30 in varchar2
536 )
537 Return g_rec_type;
538 --
539 end pay_ppm_shd;