1 Package pay_qpu_api AUTHID CURRENT_USER as
2 /* $Header: pyqpurhi.pkh 115.1 2002/12/06 15:23:44 swinton ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Global Record Type Specification |
6 -- ----------------------------------------------------------------------------
7 --
8 Type g_rec_type Is Record
9 (payroll_action_id number(9)
10 ,business_group_id number(15)
11 ,org_payment_method_id number(9)
12 ,action_status varchar2(9) -- Increased from length 1 to 9
13 ,effective_date date
14 ,target_payroll_action_id number(9)
15 ,object_version_number number
16 );
17 --
18 -- ----------------------------------------------------------------------------
19 -- |----------------------------< paid_payments >-----------------------------|
20 -- ----------------------------------------------------------------------------
21 -- {Start Of Comments}
22 --
23 -- Description:
24 -- Indicates if any of the pre-payments for the QuickPay Pre-payment
25 -- Process have actually been paid.
26 --
27 -- Pre Conditions:
28 -- p_assignment_action_id must be the id of an Assignment Process for a
29 -- QuickPay Pre-payment Payroll Process.
30 --
31 -- In Arguments:
32 -- p_assignment_action_id is mandatory.
33 --
34 -- Post Success:
35 -- Returns TRUE if at least one of the pre-payments has actually been paid.
36 --
37 -- Post Failure:
38 -- Returns FALSE if there are no pre-payments or none of the pre-payments
39 --- have been paid.
40 --
41 -- Access Status:
42 -- Public.
43 --
44 -- {End Of Comments}
45 --
46 function paid_payments
47 (p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
48 ) return boolean;
49 --
50 -- ----------------------------------------------------------------------------
51 -- |------------------------< return_api_dml_status >-------------------------|
52 -- ----------------------------------------------------------------------------
53 -- {Start Of Comments}
54 --
55 -- Description:
56 -- This function will return the current g_api_dml private global
57 -- boolean status.
58 -- The g_api_dml status determines if at the time of the function
59 -- being executed if a dml statement (i.e. INSERT, UPDATE or DELETE)
60 -- is being issued from within an api.
61 -- If the status is TRUE then a dml statement is being issued from
62 -- within this entity api.
63 -- This function is primarily to support database triggers which
64 -- need to maintain the object_version_number for non-supported
65 -- dml statements (i.e. dml statement issued outside of the api layer).
66 --
67 -- Pre Conditions:
68 -- None.
69 --
70 -- In Arguments:
71 -- None.
72 --
73 -- Post Success:
74 -- Processing continues.
75 -- If the function returns a TRUE value then, dml is being executed from
76 -- within this api.
77 --
78 -- Post Failure:
79 -- None.
80 --
81 -- Access Status:
82 -- Public.
83 --
84 -- {End Of Comments}
85 --
86 Function return_api_dml_status Return Boolean;
87 --
88 -- ----------------------------------------------------------------------------
89 -- |---------------------------------< lck >----------------------------------|
90 -- ----------------------------------------------------------------------------
91 -- {Start Of Comments}
92 --
93 -- Description:
94 -- The Lck process has two main functions to perform. Firstly, the row to be
95 -- updated or deleted must be locked. The locking of the row will only be
96 -- successful if the row is not currently locked by another user, the
97 -- specified object version numbers match and there is no AOL request
98 -- waiting or still running on the concurrent manager for this QuickPay
99 -- Pre-payment. Secondly, during the locking of the row, the row is selected
100 -- into the g_old_rec data structure which enables the current row values
101 -- from the server to be available to the api.
102 --
103 -- Pre Conditions:
104 -- None.
105 --
106 -- In Arguments:
107 -- All the arguments to the Lck process are mandatory.
108 -- p_payroll_action_id is set to the id of the QuickPay Pre-payment Payroll
109 -- Process.
110 -- p_p_object_version_number is set to the object_version_number for the
111 -- Payroll Process.
112 -- p_a_object_version_number is set to the object_version_number for the
113 -- Assignment Process.
114 --
115 -- Post Success:
116 -- The lock will only be successful if the payroll action is for a
117 -- QuickPay Pre-payment.
118 -- On successful completion of the Lck process the row to be updated or
119 -- deleted will be locked and selected into the global data structure
120 -- g_old_rec.
121 --
122 -- Post Failure:
123 -- The Lck process can fail for four reasons:
124 -- 1) When attempting to lock the row the row could already be locked by
125 -- another user. This will raise the HR_7165_OBJECT_LOCKED error.
126 -- 2) The row which is required to be locked doesn't exist in the HR Schema.
127 -- This error is trapped and reported using the message name
128 -- 'HR_7155_OBJECT_INVALID'.
129 -- 3) The row although existing in the HR Schema has a different object
130 -- version number than the object version number specified.
131 -- This error is trapped and reported using the message name
132 -- 'HR_7155_OBJECT_INVALID'.
133 -- 4) An error is raised if an AOL concurrent request is waiting to run or
134 -- still running on the concurrent manager for this QuickPay Pre-payment.
135 --
136 -- Access Status:
137 -- Public.
138 --
139 -- {End Of Comments}
140 --
141 procedure lck
142 (p_payroll_action_id in number
143 ,p_p_object_version_number in number
144 ,p_a_object_version_number in number
145 );
146 --
147 -- ----------------------------------------------------------------------------
148 -- |---------------------------------< ins >----------------------------------|
149 -- ----------------------------------------------------------------------------
150 -- {Start Of Comments}
151 --
152 -- Description:
153 -- This procedure is the record interface for the insert business process
154 -- for the QuickPay Pre-payment entity. The role of this process is to
155 -- insert a fully validated row, into the HR schema passing back to the
156 -- calling process, any system generated values (e.g. primary and object
157 -- version number attributes). This process is the main backbone of the ins
158 -- business process. The processing of this procedure is as follows:
159 -- 1) If the p_validate argument has been set to true then a savepoint is
160 -- issued.
161 -- 2) The controlling validation process insert_validate is then executed
162 -- which will execute all private and public validation business rule
163 -- processes.
164 -- 3) The pre_insert business process is then executed which enables any
165 -- logic to be processed before the insert dml process is executed.
166 -- 4) The insert_dml process will physical perform the insert dml into the
167 -- specified entity.
168 -- 5) The post_insert business process is then executed which enables any
169 -- logic to be processed after the insert dml process.
170 -- 6) If the p_validate argument has been set to true an exception is raised
171 -- which is handled and processed by performing a rollback to the
172 -- savepoint which was issued at the beginning of the Ins process.
173 --
174 -- Pre Conditions:
175 -- The main arguments to the business process have to be in the record
176 -- format.
177 --
178 -- In Arguments:
179 -- p_validate
180 -- Determines if the business process is to be validated. Setting this
181 -- boolean value to true will invoke the process to be validated. The
182 -- default is false. The validation is controlled by a savepoint and
183 -- rollback mechanism. The savepoint is issued at the beginning of the
184 -- business process and is rollbacked at the end of the business process
185 -- when all the processing has been completed. The rollback is controlled
186 -- by raising and handling the exception hr_api.validate_enabled. We use
187 -- the exception because, by raising the exception with the business
188 -- process, we can exit successfully without having any of the 'OUT'
189 -- arguments being set.
190 --
191 -- Post Success:
192 -- A fully validated row will be inserted into the specified entity
193 -- without being committed. If the p_validate argument has been set to true
194 -- then all the work will be rolled back.
195 --
196 -- Post Failure:
197 -- If an error has occurred, an error message will be supplied with the work
198 -- rolled back. A failure will occur if any of the business rules/conditions
199 -- are found:
200 -- 1) p_rec.target_payroll_action_id, p_rec.business_group_id or
201 -- p_rec.effective_date are not null.
202 -- 2) p_rec.target_payroll_action_id does not exists in pay_payroll_actions
203 -- for a QuickPay Run Payroll Process.
204 -- 3) The associated Assignment Process has a complete status.
205 -- 4) Another QuickPay Pre-payment or batch Pre-payment process already
206 -- interlocks to the QuickPay Run Assignment Process.
207 -- 5) The QuickPay Pre-payment business_group_id and date_paid
208 -- (effective_date) are not the same as the QuickPay Run
209 -- business_group_id and date_paid.
210 -- 6) If p_rec.org_payment_method_id is not null and it does not exist in
211 -- pay_org_payment_methods_f of the QuickPay Run date paid.
212 -- 7) If p_rec.org_payment_method_id is not null and does not exist in the
213 -- same business group as the QuickPay Pre-Payment.
214 -- 8) If p_rec.org_payment_method_id is not null the corresponding payment
215 -- category must not be magnetic transfer.
216 --
217 -- {End Of Comments}
218 --
219 Procedure ins
220 (p_rec in out nocopy g_rec_type
221 ,p_assignment_action_id out nocopy number
222 ,p_a_object_version_number out nocopy number
223 ,p_validate in boolean default false
224 );
225 --
226 -- ----------------------------------------------------------------------------
227 -- |---------------------------------< ins >----------------------------------|
228 -- ----------------------------------------------------------------------------
229 -- {Start Of Comments}
230 --
231 -- Description:
232 -- This procedure is the attribute interface for the insert business
233 -- process for the QuickPay Pre-payment entity and is the outermost layer.
234 -- The role of this process is to insert a fully validated row into the HR
235 -- schema passing back to the calling process, any system generated values
236 -- (e.g. the primary key and the object version numbers).The processing of
237 -- this procedure is as follows:
238 -- 1) The attributes are converted into a local record structure by
239 -- calling the convert_defs function.
240 -- 2) After the conversion has taken place, the corresponding record ins
241 -- interface business process is executed.
242 -- 3) OUT arguments are then set to their corresponding record arguments.
243 --
244 -- Pre Conditions:
245 --
246 -- In Arguments:
247 -- p_validate
248 -- Determines if the business process is to be validated. Setting this
249 -- Boolean value to true will invoke the process to be validated.
250 -- The default is false.
251 --
252 -- Post Success:
253 -- A fully validated row will be inserted for the specified entity
254 -- without being committed (or rollbacked depending on the p_validate
255 -- status).
256 --
257 -- Post Failure:
258 -- If an error has occurred, an error message will be supplied with the work
259 -- rolled back.
260 --
261 -- Access Status:
262 -- Public.
263 --
264 -- {End Of Comments}
265 --
266 Procedure ins
267 (p_business_group_id in number
268 ,p_org_payment_method_id in number default null
269 ,p_effective_date in date
270 ,p_target_payroll_action_id in number default null
271 ,p_payroll_action_id out nocopy number
272 ,p_action_status out nocopy varchar2
273 ,p_p_object_version_number out nocopy number
274 ,p_assignment_action_id out nocopy number
275 ,p_a_object_version_number out nocopy number
276 ,p_validate in boolean default false
277 );
278 --
279 -- ----------------------------------------------------------------------------
280 -- |---------------------------------< upd >----------------------------------|
281 -- ----------------------------------------------------------------------------
282 -- {Start Of Comments}
283 --
284 -- Description:
285 -- This procedure is the record interface for the update business
286 -- process for the QuickPay Pre-payment entity. The role of this process is
287 -- to update a fully validated row for the HR schema passing back
288 -- to the calling process, any system generated values (e.g.
289 -- object version number attribute). This process is the main
290 -- backbone of the upd business process. The processing of this
291 -- procedure is as follows:
292 -- 1) If the p_validate argument has been set to true then a savepoint
293 -- is issued.
294 -- 2) The row to be updated is then locked and selected into the record
295 -- structure g_old_rec.
296 -- 3) Because on update arguments which are not part of the update do not
297 -- have to be defaulted, we need to build up the updated row by
298 -- converting any system defaulted arguments to their corresponding
299 -- value.
300 -- 4) The controlling validation process update_validate is then executed
301 -- which will execute all private and public validation business rule
302 -- processes.
303 -- 5) The pre_update business process is then executed which enables any
307 -- 7) The post_update business process is then executed which enables any
304 -- logic to be processed before the update dml process is executed.
305 -- 6) The update_dml process will physical perform the update dml into the
306 -- specified entity.
308 -- logic to be processed after the update dml process.
309 -- 8) If the p_validate argument has been set to true an exception is
310 -- raised which is handled and processed by performing a rollback to
311 -- the savepoint which was issued at the beginning of the upd process.
312 --
313 -- Pre Conditions:
314 -- The main arguments to the business process have to be in the record
315 -- format.
316 --
317 -- In Arguments:
318 -- p_validate
319 -- Determines if the business process is to be validated. Setting this
320 -- boolean value to true will invoke the process to be validated. The
321 -- default is false. The validation is controlled by a savepoint and
322 -- rollback mechanism. The savepoint is issued at the beginning of the
323 -- business process and is rollbacked at the end of the business process
324 -- when all the processing has been completed. The rollback is controlled
325 -- by raising and handling the exception hr_api.validate_enabled. We use
326 -- the exception because, by raising the exception with the business
327 -- process, we can exit successfully without having any of the 'OUT'
328 -- arguments being set.
329 --
330 -- Post Success:
331 -- The specified row will be fully validated and updated for the specified
332 -- entity without being committed. If the p_validate argument has been set
333 -- to true then all the work will be rolled back.
334 --
335 -- Post Failure:
336 -- If an error has occurred, an error message will be supplied with the work
337 -- rolled back. A failure will occur if any of the business rules/conditions
338 -- are found:
339 -- 1) An AOL concurrent request is waiting to run or still running on the
340 -- concurrent manager for this QuickPay Pre-payment.
341 -- 2) The QuickPay Pre-payment Payroll Process current_task is not
342 -- null.
343 -- 3) If the action_status is being updated the only valid change is from
344 -- Complete to Mark for Retry. Any other attempted change to the
345 -- action_status will result in an error being raised.
346 --
347 -- Access Status:
348 -- Public.
349 --
350 -- {End Of Comments}
351 --
352 procedure upd
353 (p_rec in out nocopy g_rec_type
354 ,p_assignment_action_id in number
355 ,p_a_object_version_number in number
356 ,p_validate in boolean default false
357 );
358 --
359 -- ----------------------------------------------------------------------------
360 -- |---------------------------------< upd >----------------------------------|
361 -- ----------------------------------------------------------------------------
362 -- {Start Of Comments}
363 --
364 -- Description:
365 -- This procedure is the attribute interface for the update business
366 -- process for the QuickPay Pre-payment entity and is the outermost layer.
367 -- The role of this process is to update a fully validated row into the HR
368 -- schema passing back to the calling process, any system generated values
369 -- (e.g. object version number attributes). The processing of this
370 -- procedure is as follows:
371 -- 1) The attributes are converted into a local record structure by
372 -- calling the convert_defs function.
373 -- 2) After the conversion has taken place, the corresponding record upd
374 -- interface business process is executed.
375 -- 3) OUT arguments are then set to their corresponding record arguments.
376 --
377 -- Pre Conditions:
378 --
379 -- In Arguments:
380 -- p_validate
381 -- Determines if the business process is to be validated. Setting this
382 -- Boolean value to true will invoke the process to be validated.
383 -- The default is false.
384 --
385 -- Post Success:
386 -- A fully validated row will be updated for the specified entity
387 -- without being committed (or rollbacked depending on the p_validate
388 -- status).
389 --
390 -- Post Failure:
391 -- If an error has occurred, an error message will be supplied with the work
392 -- rolled back.
393 --
394 -- Access Status:
395 -- Public.
396 --
397 -- {End Of Comments}
398 --
399 procedure upd
400 (p_payroll_action_id in number
401 ,p_assignment_action_id in number
402 ,p_action_status in varchar2 default hr_api.g_varchar2
403 ,p_p_object_version_number in out nocopy number
404 ,p_a_object_version_number in number
405 ,p_validate in boolean default false
406 );
407 --
408 -- ----------------------------------------------------------------------------
409 -- |---------------------------------< del >----------------------------------|
410 -- ----------------------------------------------------------------------------
411 -- {Start Of Comments}
412 --
413 -- Description:
414 -- This procedure is the record interface for the delete business process
415 -- for the QuickPay Pre-payment entity. The role of this process is to
416 -- delete the row from the HR schema. This process is the main backbone of
417 -- the del business process. The processing of this procedure is as follows:
418 -- 1) If the p_validate argument has been set to true then a savepoint is
422 -- processes.
419 -- issued.
420 -- 2) The controlling validation process delete_validate is then executed
421 -- which will execute all private and public validation business rule
423 -- 3) The pre_delete business process is then executed which enables any
424 -- logic to be processed before the delete dml process is executed.
425 -- 4) The delete_dml process will physical perform the delete dml for the
426 -- specified row.
427 -- 5) The post_delete business process is then executed which enables any
428 -- logic to be processed after the delete dml process.
429 -- 6) If the p_validate argument has been set to true an exception is raised
430 -- which is handled and processed by performing a rollback to the
431 -- savepoint which was issued at the beginning of the del process.
432 --
433 -- Pre Conditions:
434 -- The main arguments to the business process have to be in the record
435 -- format.
436 --
437 -- In Arguments:
438 -- p_validate
439 -- Determines if the business process is to be validated. Setting this
440 -- boolean value to true will invoke the process to be validated. The
441 -- default is false. The validation is controlled by a savepoint and
442 -- rollback mechanism. The savepoint is issued at the beginning of the
443 -- business process and is rollbacked at the end of the business process
444 -- when all the processing has been completed. The rollback is controlled
445 -- by raising and handling the exception hr_api.validate_enabled. We use
446 -- the exception because, by raising the exception with the business
447 -- process, we can exit successfully without having any of the 'OUT'
448 -- arguments being set.
449 --
450 -- Post Success:
451 -- The specified row will be fully validated and deleted for the specified
452 -- entity without being committed. If the p_validate argument has been set
453 -- to true then all the work will be rolled back.
454 --
455 -- Post Failure:
456 -- If an error has occurred, an error message will be supplied with the work
457 -- rolled back. A failure will occur if any of the business rules/conditions
458 -- are found:
459 -- 1) An AOL concurrent request is waiting to run or still running on the
460 -- concurrent manager for this QuickPay Pre-payment.
461 -- 2) The QuickPay Pre-payment Payroll Process current_task is not
462 -- null.
463 --
464 -- Access Status:
465 -- Public.
466 --
467 -- {End Of Comments}
468 --
469 Procedure del
470 (p_rec in g_rec_type
471 ,p_a_object_version_number in number
472 ,p_validate in boolean default false
473 );
474 --
475 -- ----------------------------------------------------------------------------
476 -- |---------------------------------< del >----------------------------------|
477 -- ----------------------------------------------------------------------------
478 -- {Start Of Comments}
479 --
480 -- Description:
481 -- This procedure is the attribute interface for the delete business
482 -- process for the QuickPay Pre-payment entity and is the outermost layer.
483 -- The role of this process is to validate and delete the specified row from
484 -- the HR schema. The processing of this procedure is as follows:
485 -- 1) The attributes are converted into a local record structure by
486 -- explicitly coding the attribute arguments into the g_rec_type
487 -- datatype.
488 -- 2) After the conversion has taken place, the corresponding record del
489 -- interface business process is executed.
490 --
491 -- Pre Conditions:
492 --
493 -- In Arguments:
494 -- p_validate
495 -- Determines if the business process is to be validated. Setting this
496 -- Boolean value to true will invoke the process to be validated.
497 -- The default is false.
498 --
499 -- Post Success:
500 -- The specified row will be fully validated and deleted for the specified
501 -- entity without being committed (or rollbacked depending on the
502 -- p_validate status).
503 --
504 -- Post Failure:
505 -- If an error has occurred, an error message will be supplied with the work
506 -- rolled back.
507 --
508 -- Access Status:
509 -- Public.
510 --
511 -- {End Of Comments}
512 --
513 procedure del
514 (p_payroll_action_id in number
515 ,p_p_object_version_number in number
516 ,p_a_object_version_number in number
517 ,p_validate in boolean default false
518 );
519 --
520 --
521 -- ----------------------------------------------------------------------------
522 -- |-----------------------------< get_latest_status >------------------------|
523 -- ----------------------------------------------------------------------------
524 --
525 -- {Start Of Comments}
526 --
527 -- Description:
528 -- Returns the latest Assignment Process action_status for a given QuickPay
529 -- Pre-Payment.
530 -- Used to find out the updated action_status after one of the C processes
531 -- has been ran.
532 --
533 -- Pre Conditions:
534 -- None.
535 --
536 -- In Arguments:
537 -- p_payroll_action_id is the id of a QuickPay Pre-payment payroll action,
538 -- which exists in the database. This is a mandatory argument.
539 --
540 -- Post Success:
541 -- p_action_status will be set to pay_assignment_actions.action_status.
542 --
543 -- Post Failure:
547 -- Access Status:
544 -- An error will be raised if a payroll_action does not exist with an id
545 -- of p_payroll_action_id.
546 --
548 -- Public.
549 --
550 -- {End Of Comments}
551 --
552 procedure get_latest_status
553 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
554 ,p_action_status out nocopy pay_assignment_actions.action_status%TYPE
555 );
556 end pay_qpu_api;