1 package pay_ppmv4_utils_ss as
2 /* $Header: pyppmv4u.pkh 120.0.12010000.1 2008/07/27 23:25:59 appldev ship $ */
3 ---------------------------------------------------------------------------
4 ---------------------------------- CONSTANTS ------------------------------
5 ---------------------------------------------------------------------------
6 -- Priority values to use.
7 --
8 C_MAX_PRIORITY constant number default 99;
9 C_MIN_PRIORITY constant number default 1;
10 C_NO_PRIORITY constant number default -1; -- Invalid value.
11 --
12 -- The API to be called for the PPM transactions.
13 --
14 C_PSS_API constant varchar2(2000) default
15 'PAY_PPMV4_SS.PROCESS_API';
16 --
17 -- Workflow constants.
18 --
19 C_PROCESSED_FLAG_ARG constant varchar2(2000) default 'P_PROCESSED_FLAG';
20 C_TX_STEP_ID_ARG constant varchar2(2000) default 'P_TRANSACTION_STEP_ID';
21 C_REVIEW_PROC_CALL_ARG constant varchar2(2000) default 'P_REVIEW_PROC_CALL';
22 C_REVIEW_ACTID_ARG constant varchar2(2000) default 'P_REVIEW_ACTID';
23 C_REVIEW_REGION_ITEM constant varchar2(2000) default 'HR_REVIEW_REGION_ITEM';
24 C_ASSIGNMENT_ID constant varchar2(2000) default 'PAY_PSS_ASSIGNMENT_ID';
25 C_EFFECTIVE_DATE constant varchar2(2000) default
26 'PAY_PSS_EFFECTIVE_DATE';
27 ---------------------------------------------------------------------------
28 -------------------------------- DATA TYPES -------------------------------
29 ---------------------------------------------------------------------------
30 --
31 -- T_PPM
32 --
33 type t_ppmv4 is record
34 (transaction_id pay_pss_transaction_steps.transaction_id%type
35 ,transaction_step_id pay_pss_transaction_steps.transaction_step_id%type
36 ,source_table pay_pss_transaction_steps.source_table%type
37 ,state pay_pss_transaction_steps.state%type
38 ,personal_payment_method_id
39 pay_personal_payment_methods_f.personal_payment_method_id%type
40 ,update_ovn
41 pay_personal_payment_methods_f.object_version_number%type
42 ,delete_ovn
43 pay_personal_payment_methods_f.object_version_number%type
44 ,update_datetrack_mode pay_pss_transaction_steps.update_datetrack_mode%type
45 ,delete_datetrack_mode pay_pss_transaction_steps.delete_datetrack_mode%type
46 ,delete_disabled pay_pss_transaction_steps.delete_disabled%type
47 ,effective_date date
48 ,org_payment_method_id pay_org_payment_methods_f.org_payment_method_id%type
49 ,assignment_id pay_personal_payment_methods_f.assignment_id%type
50 ,payment_type pay_pss_transaction_steps.payment_type%type
51 ,currency_code pay_pss_transaction_steps.currency_code%type
52 ,territory_code pay_pss_transaction_steps.territory_code%type
53 --
54 -- Current data values.
55 --
56 ,real_priority pay_personal_payment_methods_f.priority%type
57 ,logical_priority pay_personal_payment_methods_f.priority%type
58 ,amount_type pay_pss_transaction_steps.amount_type%type
59 ,amount pay_personal_payment_methods_f.amount%type
60 ,external_account_id pay_external_accounts.external_account_id%type
61 ,attribute_category pay_personal_payment_methods_f.attribute_category%type
62 ,attribute1 pay_personal_payment_methods_f.attribute1%type
63 ,attribute2 pay_personal_payment_methods_f.attribute2%type
64 ,attribute3 pay_personal_payment_methods_f.attribute3%type
65 ,attribute4 pay_personal_payment_methods_f.attribute4%type
66 ,attribute5 pay_personal_payment_methods_f.attribute5%type
67 ,attribute6 pay_personal_payment_methods_f.attribute6%type
68 ,attribute7 pay_personal_payment_methods_f.attribute7%type
69 ,attribute8 pay_personal_payment_methods_f.attribute8%type
70 ,attribute9 pay_personal_payment_methods_f.attribute9%type
71 ,attribute10 pay_personal_payment_methods_f.attribute10%type
72 ,attribute11 pay_personal_payment_methods_f.attribute11%type
73 ,attribute12 pay_personal_payment_methods_f.attribute12%type
74 ,attribute13 pay_personal_payment_methods_f.attribute13%type
75 ,attribute14 pay_personal_payment_methods_f.attribute14%type
76 ,attribute15 pay_personal_payment_methods_f.attribute15%type
77 ,attribute16 pay_personal_payment_methods_f.attribute16%type
78 ,attribute17 pay_personal_payment_methods_f.attribute17%type
79 ,attribute18 pay_personal_payment_methods_f.attribute18%type
80 ,attribute19 pay_personal_payment_methods_f.attribute19%type
81 ,attribute20 pay_personal_payment_methods_f.attribute20%type
82 --
83 -- Original data values.
84 --
85 ,o_real_priority pay_personal_payment_methods_f.priority%type
86 ,o_logical_priority pay_personal_payment_methods_f.priority%type
87 ,o_amount_type pay_pss_transaction_steps.o_amount_type%type
88 ,o_amount pay_personal_payment_methods_f.amount%type
89 ,o_external_account_id pay_external_accounts.external_account_id%type
90 ,o_attribute_category pay_personal_payment_methods_f.attribute_category%type
91 ,o_attribute1 pay_personal_payment_methods_f.attribute1%type
92 ,o_attribute2 pay_personal_payment_methods_f.attribute2%type
93 ,o_attribute3 pay_personal_payment_methods_f.attribute3%type
94 ,o_attribute4 pay_personal_payment_methods_f.attribute4%type
95 ,o_attribute5 pay_personal_payment_methods_f.attribute5%type
96 ,o_attribute6 pay_personal_payment_methods_f.attribute6%type
97 ,o_attribute7 pay_personal_payment_methods_f.attribute7%type
98 ,o_attribute8 pay_personal_payment_methods_f.attribute8%type
99 ,o_attribute9 pay_personal_payment_methods_f.attribute9%type
100 ,o_attribute10 pay_personal_payment_methods_f.attribute10%type
101 ,o_attribute11 pay_personal_payment_methods_f.attribute11%type
102 ,o_attribute12 pay_personal_payment_methods_f.attribute12%type
103 ,o_attribute13 pay_personal_payment_methods_f.attribute13%type
104 ,o_attribute14 pay_personal_payment_methods_f.attribute14%type
105 ,o_attribute15 pay_personal_payment_methods_f.attribute15%type
106 ,o_attribute16 pay_personal_payment_methods_f.attribute16%type
107 ,o_attribute17 pay_personal_payment_methods_f.attribute17%type
108 ,o_attribute18 pay_personal_payment_methods_f.attribute18%type
109 ,o_attribute19 pay_personal_payment_methods_f.attribute19%type
110 ,o_attribute20 pay_personal_payment_methods_f.attribute20%type
111 ,run_type_id pay_personal_payment_methods_f.run_type_id%type
112 );
113 --
114 -- T_PPM_TBL
115 --
116 type t_ppmv4_tbl is table of t_ppmv4 index by binary_integer;
117 --
118 -- T_BOOLEAN_TBL
119 --
120 type t_boolean_tbl is table of boolean index by binary_integer;
121 ---------------------------------------------------------------------------
122 ----------------------- FUNCTIONS AND PROCEDURES --------------------------
123 ---------------------------------------------------------------------------
124 -------------------------------< seterror >--------------------------------
125 --
126 -- {Start Of Comments}
127 --
128 -- Description:
129 -- Cover for hr_utility.set_location call.
130 -- SETERRORSTAGE
131 -- P_PROC - The procedure being called.
132 -- P_STAGE - Where in the code is at this moment.
133 -- P_LOC - Error location.
134 --
135 -- Prerequisites:
136 -- None.
137 --
138 -- Post Success:
139 --
140 -- Post Failure:
141 -- Not applicable.
142 --
143 -- Access Status:
144 -- Internal Development Use Only.
145 --
146 -- {End Of Comments}
147 --
148 procedure seterrorstage
149 (p_proc in varchar2
150 ,p_stage in varchar2
151 ,p_location in number
152 );
153 ------------------------------< ppm2hrtt >---------------------------------
154 --
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- Writes PPM information to HR transaction tables.
159 -- P_TRANSACTION_STEP_ID is from PAY_PSS_TRANSACTION_STEPS.
160 -- P_FORCE_NEW_TRANSACTION forces a new transaction to be created.
161 --
162 -- Prerequisites:
163 -- None.
164 --
165 -- Post Success:
166 -- The transaction table is populated.
167 --
168 -- Post Failure:
169 -- An exception is raised.
170 --
171 -- Access Status:
172 -- Internal Development Use Only.
173 --
174 -- {End Of Comments}
175 --
176 procedure ppm2hrtt
177 (p_item_type in varchar2
178 ,p_item_key in varchar2
179 ,p_activity_id in number
180 ,p_login_person_id in number
181 ,p_review_proc_call in varchar2
182 ,p_transaction_step_id in number
183 ,p_force_new_transaction in boolean
184 );
185 --------------------------------< ppm2tt >---------------------------------
186 --
187 -- {Start Of Comments}
188 --
189 -- Description:
190 -- Writes a T_PPM record to the transaction tables.
191 --
192 -- Prerequisites:
193 -- None.
194 --
195 -- Post Success:
196 -- The ppm values are saved to the transaction table. The save is committed.
197 --
198 -- Post Failure:
199 -- An exception is raised.
200 --
201 -- Access Status:
202 -- Internal Development Use Only.
203 --
204 -- {End Of Comments}
205 --
206 procedure ppm2tt
207 (p_ppm in out nocopy t_ppmv4
208 );
209 --------------------------------< tt2ppm >---------------------------------
210 --
211 -- {Start Of Comments}
212 --
213 -- Description:
214 -- Converts a transaction entry to a T_PPM record.
215 --
216 -- Prerequisites:
217 -- P_TRANSACTION_STEP_ID must point to a valid record.
218 --
219 -- Post Success:
220 -- The T_PPM record is populated using the supplied values.
221 --
222 -- Post Failure:
223 -- An exception is raised.
224 --
225 -- Access Status:
226 -- Internal Development Use Only.
227 --
228 -- {End Of Comments}
229 --
230 procedure tt2ppm
231 (p_transaction_step_id in number
232 ,p_ppm out nocopy t_ppmv4
233 );
234 -----------------------------< changedppm >------------------------
235 --
236 -- {Start Of Comments}
237 --
238 -- Description:
239 -- Detects whether or not a PPM has changed (original data values
240 -- differ from latest data values).
241 --
242 -- Prerequisites:
243 -- None.
244 --
245 -- Post Success:
246 -- P_CHANGES is set to true if there are any differences.
247 -- P_BANK is set to true if the Bank Details differ.
248 --
249 -- Post Failure:
250 -- Not applicable.
251 --
252 -- Access Status:
253 -- Internal Development Use Only.
254 --
255 -- {End Of Comments}
256 --
257 procedure changedppm
258 (p_ppm in t_ppmv4
259 ,p_changes out nocopy boolean
260 ,p_bank out nocopy boolean
261 );
262 -----------------------------< changedppm >------------------------
263 -- {Start Of Comments}
264 --
265 -- Description:
266 -- Overloaded version to use when comparing a new ppm with a saved
267 -- ppm. Differences in logical priority are overlooked in this
268 -- instance because it's used to compare an Added/Updated PPM with
269 -- a saved one to reduce unnecessary validation.
270 --
271 -- Post Success:
272 -- P_ORIGINAL:
273 -- TRUE - P_NEW_PPM differs from the original version of
274 -- P_SAVED_PPM.
275 -- FALSE - P_NEW_PPM is the same as the original version of
276 -- P_SAVED_PPM.
277 -- P_CURRENT:
278 -- TRUE - P_NEW_PPM differs from the current version of
279 -- P_SAVED_PPM.
280 -- FALSE - P_NEW_PPM is the same as the current version of
281 -- P_SAVED_PPM.
282 --
283 -- Access Status:
284 -- Internal Development Use Only.
285 --
286 -- {End Of Comments}
287 --
288 procedure changedppm
289 (p_new_ppm in t_ppmv4
290 ,p_saved_ppm in t_ppmv4
291 ,p_original out nocopy boolean
292 ,p_current out nocopy boolean
293 );
294 -----------------------------< nextentry >--------------------------
295 --
296 -- {Start Of Comments}
297 --
298 -- Description:
299 -- Gets the next entry from a list whose entries are separated by
300 -- a given character. May return NULL.
301 -- p_start is set to 0 when the last entry in the list is reached.
302 --
303 -- Prerequisites:
304 -- None.
305 --
306 -- Post Success:
307 -- The current list entry is returned. p_start is set to point at
308 -- the next entry in the list (or 0 if this list entry was the last
309 -- list entry).
310 --
311 -- Post Failure:
312 -- Not applicable.
313 --
314 -- Access Status:
315 -- Internal Development Use Only.
316 --
317 -- {End Of Comments}
318 --
319 function nextentry
320 (p_list in varchar2
321 ,p_separator in varchar2
322 ,p_start in out nocopy number
323 ) return varchar2;
324 ------------------------< read_wf_config_option >------------------------
325 --
326 -- {Start Of Comments}
327 --
328 -- Description:
329 -- Reads the value of an activity, or item attribute for the specified
330 -- workflow.
331 --
332 -- Prerequisites:
333 -- None.
334 --
335 -- Post Success:
336 -- The value of the attribute is returned.
337 --
338 -- Post Failure:
339 -- An exception is raised.
340 --
341 -- Access Status:
342 -- Internal Development Use Only.
343 --
344 -- {End Of Comments}
345 --
346 function read_wf_config_option
347 (p_item_type in varchar2
348 ,p_item_key in varchar2
349 ,p_activity_id in number default null
350 ,p_option in varchar2
351 ,p_number in boolean default false
352 ) return varchar2;
353 ----------------------< getpriorities >-----------------------
354 --
355 -- {Start Of Comments}
356 --
357 -- Description:
358 -- Returns a priority availability table that lists the PPM
359 -- priorities that are allocated to existing PPMs, and those
360 -- priorities that are free. Also, returns the first available
361 -- PPM priority.
362 --
363 -- Prerequisites:
364 -- None.
365 --
366 -- Post Success:
367 --
368 -- Post Failure:
369 -- Raises an exception.
370 --
371 -- Access Status:
372 -- Internal Development Use Only.
373 --
374 -- {End Of Comments}
375 --
376 procedure getpriorities
377 (p_assignment_id in number
378 ,p_effective_date in date
379 ,p_run_type_id in number default null
380 ,p_priority_tbl out nocopy t_boolean_tbl
381 ,p_first_available out nocopy number
382 );
383 -----------------------------< validateppm >------------------------
384 --
385 -- {Start Of Comments}
386 --
387 -- Description:
388 -- Checks that changes to a PPM for validity for INSERT/UPDATE of
389 -- a complete PPM record.
390 -- Delete validation and priority validation are not required since
391 -- the system prevalidates for those cases.
392 --
393 -- Prerequisites:
394 -- None.
395 --
396 -- Post Success:
397 -- If there are user errors then the hr_errors_api error table is
398 -- written to. The caller should call hr_errors_api.errorexists to
399 -- check for errors.
400 --
401 -- Post Failure:
402 -- An exception is raised.
403 --
404 -- Access Status:
405 -- Internal Development Use Only.
406 --
407 -- {End Of Comments}
408 --
409 procedure validateppm
410 (p_state in varchar2
411 ,p_personal_payment_method_id in number default null
412 ,p_object_version_number in number default null
413 ,p_update_datetrack_mode in varchar2 default null
414 ,p_effective_date in date default null
418 ,p_payment_type in varchar2 default null
415 ,p_org_payment_method_id in number default null
416 ,p_assignment_id in number default null
417 ,p_run_type_id in number default null
419 ,p_territory_code in varchar2 default null
420 ,p_amount_type in varchar2 default null
421 ,p_amount in number default null
422 ,p_external_account_id in number default null
423 ,p_attribute_category in varchar2 default null
424 ,p_attribute1 in varchar2 default null
425 ,p_attribute2 in varchar2 default null
426 ,p_attribute3 in varchar2 default null
427 ,p_attribute4 in varchar2 default null
428 ,p_attribute5 in varchar2 default null
429 ,p_attribute6 in varchar2 default null
430 ,p_attribute7 in varchar2 default null
431 ,p_attribute8 in varchar2 default null
432 ,p_attribute9 in varchar2 default null
433 ,p_attribute10 in varchar2 default null
434 ,p_attribute11 in varchar2 default null
435 ,p_attribute12 in varchar2 default null
436 ,p_attribute13 in varchar2 default null
437 ,p_attribute14 in varchar2 default null
438 ,p_attribute15 in varchar2 default null
439 ,p_attribute16 in varchar2 default null
440 ,p_attribute17 in varchar2 default null
441 ,p_attribute18 in varchar2 default null
442 ,p_attribute19 in varchar2 default null
443 ,p_attribute20 in varchar2 default null
444 ,p_segment1 in varchar2 default null
445 ,p_segment2 in varchar2 default null
446 ,p_segment3 in varchar2 default null
447 ,p_segment4 in varchar2 default null
448 ,p_segment5 in varchar2 default null
449 ,p_segment6 in varchar2 default null
450 ,p_segment7 in varchar2 default null
451 ,p_segment8 in varchar2 default null
452 ,p_segment9 in varchar2 default null
453 ,p_segment10 in varchar2 default null
454 ,p_segment11 in varchar2 default null
455 ,p_segment12 in varchar2 default null
456 ,p_segment13 in varchar2 default null
457 ,p_segment14 in varchar2 default null
458 ,p_segment15 in varchar2 default null
459 ,p_segment16 in varchar2 default null
460 ,p_segment17 in varchar2 default null
461 ,p_segment18 in varchar2 default null
462 ,p_segment19 in varchar2 default null
463 ,p_segment20 in varchar2 default null
464 ,p_segment21 in varchar2 default null
465 ,p_segment22 in varchar2 default null
466 ,p_segment23 in varchar2 default null
467 ,p_segment24 in varchar2 default null
468 ,p_segment25 in varchar2 default null
469 ,p_segment26 in varchar2 default null
470 ,p_segment27 in varchar2 default null
471 ,p_segment28 in varchar2 default null
472 ,p_segment29 in varchar2 default null
473 ,p_segment30 in varchar2 default null
474 ,p_return_status out nocopy varchar2
475 ,p_msg_count out nocopy number
476 ,p_msg_data out nocopy varchar2
477 );
478 -----------------------------< process_api >------------------------
479 --
480 -- {Start Of Comments}
481 --
482 -- Description:
483 -- Internal call to the PPM APIs.
484 --
485 -- Prerequisites:
486 -- None.
487 --
488 -- Post Success:
489 -- The API call is made.
490 --
491 -- Post Failure:
492 -- An exception is raised.
493 --
494 -- Access Status:
495 -- Internal Development Use Only.
496 --
497 -- {End Of Comments}
498 --
499 -----------------------------< process_api >------------------------
500 procedure process_api
501 (p_state in varchar2 default null
502 ,p_personal_payment_method_id in number default null
503 ,p_object_version_number in number default null
504 ,p_delete_ovn in number default null
505 ,p_update_datetrack_mode in varchar2 default null
506 ,p_delete_datetrack_mode in varchar2 default null
507 ,p_effective_date in date default null
508 ,p_org_payment_method_id in number default null
509 ,p_assignment_id in number default null
510 ,p_run_type_id in number default null
511 ,p_territory_code in varchar2 default null
512 ,p_real_priority in number default null
513 ,p_amount_type in varchar2 default null
514 ,p_amount in number default null
515 ,p_attribute_category in varchar2 default null
516 ,p_attribute1 in varchar2 default null
517 ,p_attribute2 in varchar2 default null
518 ,p_attribute3 in varchar2 default null
519 ,p_attribute4 in varchar2 default null
520 ,p_attribute5 in varchar2 default null
521 ,p_attribute6 in varchar2 default null
522 ,p_attribute7 in varchar2 default null
523 ,p_attribute8 in varchar2 default null
524 ,p_attribute9 in varchar2 default null
525 ,p_attribute10 in varchar2 default null
526 ,p_attribute11 in varchar2 default null
527 ,p_attribute12 in varchar2 default null
528 ,p_attribute13 in varchar2 default null
529 ,p_attribute14 in varchar2 default null
530 ,p_attribute15 in varchar2 default null
531 ,p_attribute16 in varchar2 default null
532 ,p_attribute17 in varchar2 default null
533 ,p_attribute18 in varchar2 default null
534 ,p_attribute19 in varchar2 default null
535 ,p_attribute20 in varchar2 default null
536 ,p_segment1 in varchar2 default null
537 ,p_segment2 in varchar2 default null
538 ,p_segment3 in varchar2 default null
539 ,p_segment4 in varchar2 default null
540 ,p_segment5 in varchar2 default null
541 ,p_segment6 in varchar2 default null
542 ,p_segment7 in varchar2 default null
543 ,p_segment8 in varchar2 default null
544 ,p_segment9 in varchar2 default null
545 ,p_segment10 in varchar2 default null
546 ,p_segment11 in varchar2 default null
547 ,p_segment12 in varchar2 default null
548 ,p_segment13 in varchar2 default null
549 ,p_segment14 in varchar2 default null
550 ,p_segment15 in varchar2 default null
551 ,p_segment16 in varchar2 default null
552 ,p_segment17 in varchar2 default null
553 ,p_segment18 in varchar2 default null
554 ,p_segment19 in varchar2 default null
555 ,p_segment20 in varchar2 default null
556 ,p_segment21 in varchar2 default null
557 ,p_segment22 in varchar2 default null
558 ,p_segment23 in varchar2 default null
559 ,p_segment24 in varchar2 default null
560 ,p_segment25 in varchar2 default null
561 ,p_segment26 in varchar2 default null
562 ,p_segment27 in varchar2 default null
563 ,p_segment28 in varchar2 default null
564 ,p_segment29 in varchar2 default null
565 ,p_segment30 in varchar2 default null
566 ,p_o_real_priority in number default null
567 ,p_validate in boolean default false
568 );
569 -------------------------< get_bank_segments >----------------------
570 --
571 -- {Start Of Comments}
572 --
573 -- Description:
574 -- Fetch the bank segments from PAY_EXTERNAL_ACCOUNTS.
575 --
576 -- Prerequisites:
577 -- None.
578 --
579 -- Post Success:
580 -- The OUT parameters are populated with the segment values.
581 --
582 -- Post Failure:
583 -- An exception is raised.
584 --
585 -- Access Status:
586 -- Internal Development Use Only.
587 --
588 -- {End Of Comments}
589 --
590 procedure get_bank_segments
591 (p_external_account_id in number
592 ,p_segment1 out nocopy varchar2
593 ,p_segment2 out nocopy varchar2
594 ,p_segment3 out nocopy varchar2
595 ,p_segment4 out nocopy varchar2
596 ,p_segment5 out nocopy varchar2
597 ,p_segment6 out nocopy varchar2
598 ,p_segment7 out nocopy varchar2
599 ,p_segment8 out nocopy varchar2
600 ,p_segment9 out nocopy varchar2
601 ,p_segment10 out nocopy varchar2
602 ,p_segment11 out nocopy varchar2
603 ,p_segment12 out nocopy varchar2
604 ,p_segment13 out nocopy varchar2
605 ,p_segment14 out nocopy varchar2
606 ,p_segment15 out nocopy varchar2
607 ,p_segment16 out nocopy varchar2
608 ,p_segment17 out nocopy varchar2
609 ,p_segment18 out nocopy varchar2
610 ,p_segment19 out nocopy varchar2
611 ,p_segment20 out nocopy varchar2
612 ,p_segment21 out nocopy varchar2
613 ,p_segment22 out nocopy varchar2
614 ,p_segment23 out nocopy varchar2
615 ,p_segment24 out nocopy varchar2
616 ,p_segment25 out nocopy varchar2
617 ,p_segment26 out nocopy varchar2
618 ,p_segment27 out nocopy varchar2
619 ,p_segment28 out nocopy varchar2
620 ,p_segment29 out nocopy varchar2
621 ,p_segment30 out nocopy varchar2
622 );
623 --
624 end pay_ppmv4_utils_ss;
[Home] [Help]