[Home] [Help]
PACKAGE BODY: APPS.PAY_PPMV4_SS
Source
1 package body pay_ppmv4_ss as
2 /* $Header: pyppmwrs.pkb 120.13.12020000.2 2012/07/11 07:14:22 vmaripal ship $ */
3 g_package constant varchar(2000) default 'pay_ppmv4_ss.';
4 g_item_type varchar2(2000);
5 g_item_key varchar2(2000);
6 g_debug boolean := hr_utility.debug_enabled;
7 --
8 -- Constants for payments list mode profile option.
9 --
10 C_OPM_LIST_MODE constant varchar2(127) default 'PAY_PSS_PAYMENTS_LIST';
11 C_OPM_LIST_MODE_ANY constant varchar2(63) default 'ANY';
12 C_OPM_LIST_MODE_LIKE constant varchar2(63) default 'LIKE';
13 C_OPM_LIST_MODE_MATCH constant varchar2(63) default 'MATCH';
14 --
15 procedure chk_foreign_account
16 ( p_transaction_step_id in varchar2
17 ) is
18 --
19 l_dummy varchar2(1);
20 --
21 begin
22 --
23 -- Check whether the territory code is same as the Org payment's
24 -- territory code. Otherwise raise an error.
25 --
26 begin
27 --
28 select null
29 into l_dummy
30 from sys.dual
31 where not exists
32 (select null
33 from pay_pss_transaction_steps pts,
34 pay_org_payment_methods_f opm,
35 pay_payment_types ppt
36 where pts.transaction_step_id = p_transaction_step_id
37 and opm.org_payment_method_id = pts.org_payment_method_id
38 and pts.effective_date
39 between opm.effective_start_date
40 and opm.effective_end_date
41 and opm.payment_type_id = ppt.payment_type_id
42 and ppt.category = 'MT'
43 and ppt.territory_code <> pts.territory_code);
44 --
45 --
46 exception
47 when no_data_found then
48 hr_utility.set_message
49 (applid => 800
50 ,l_message_name => 'PAY_449775_FOREIGN_ACCOUNT'
51 );
52 hr_utility.raise_error;
53 when others then
54 null;
55 end;
56 end;
57 --
58 --------------------------< post_submit_work >-----------------------------
59 procedure post_submit_work
60 (p_item_type in varchar2
61 ,p_item_key in varchar2
62 ,p_activity_id in varchar2
63 ,p_login_person_id in varchar2
64 ,p_transaction_id in varchar2
65 ,p_assignment_id in varchar2
66 ,p_effective_date in varchar2
67 ,p_return_status out nocopy varchar2
68 ,p_msg_count out nocopy number
69 ,p_msg_data out nocopy varchar2
70 ) is
71 cursor csr_ppm_info(p_transaction_id in number) is
72 select ppts.transaction_step_id
73 , ppts.state
74 from pay_pss_transaction_steps ppts
75 where ppts.transaction_id = p_transaction_id
76 and ppts.state <> C_STATE_FREED
77 ;
78 --
79 i binary_integer;
80 l_proc varchar2(2000) := g_package || 'post_submit_work';
81 l_txstepids t_number_tbl;
82 l_state varchar2(2000);
83 l_changes boolean := false;
84 l_review_proc_call varchar2(2000);
85 l_result varchar2(2000);
86 l_force boolean;
87 l_success boolean;
88 begin
89 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
90 fnd_msg_pub.initialize;
91 --
92 -- Rollback any existing HR transaction table data.
93 --
94 hr_transaction_ss.rollback_transaction
95 (itemtype => p_item_type
96 ,itemkey => p_item_key
97 ,actid => p_activity_id
98 ,funmode => 'RUN'
99 ,result => l_result
100 );
101 --
102 -- Set the HR TRANSACTION_ID value to null.
103 --
104 if hr_transaction_ss.get_transaction_id
105 (p_item_type => p_item_type
106 ,p_item_key => p_item_key
107 ) is not null then
108 wf_engine.setitemattrtext
109 (itemtype => p_item_type
110 ,itemkey => p_item_key
111 ,aname => C_HR_TXID_WF_ATTRIBUTE
112 ,avalue => null
113 );
114 end if;
115 --
116 -- Check for user changes.
117 --
118 i := 1;
119 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXECUTE_CURSOR', 10);
120 for rec in csr_ppm_info(p_transaction_id => to_number(p_transaction_id)) loop
121 l_changes := (l_changes or rec.state <> C_STATE_EXISTING);
122 l_txstepids(i) := rec.transaction_step_id;
123 i := i + 1;
124 end loop;
125 --
126 -- Allocate real priorities and write to HR transaction tables if there
127 -- were any changes.
128 --
129 if l_changes then
130 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ALLOC_REAL_PRIORITIES', 20);
131
132 alloc_real_priorities
133 (p_transaction_id => p_transaction_id
134 ,p_assignment_id => p_assignment_id
135 ,p_effective_date => p_effective_date
136 ,p_success => l_success
137 );
138 if not l_success then
139 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL:1', 25);
140 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
141 fnd_message.set_token('WHERE', l_proc);
142 fnd_message.set_token('ADDITIONAL_INFO', '<REAL PRIORITY ALLOC FAIL>');
143 fnd_msg_pub.add;
144 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
145 fnd_msg_pub.count_and_get
146 (p_count => p_msg_count
147 ,p_data => p_msg_data
148 );
149 return;
150 end if;
151 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'C_REVIEW_REGION_ITEM', 30);
152 l_review_proc_call := pay_ppmv4_utils_ss.read_wf_config_option
153 (p_item_type => p_item_type
154 ,p_item_key => p_item_key
155 ,p_activity_id => p_activity_id
156 ,p_option => pay_ppmv4_utils_ss.C_REVIEW_REGION_ITEM
157 );
158 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PPM2HRTT', 30);
159 for i in 1 .. l_txstepids.count loop
160 l_force := (i = 1);
161 pay_ppmv4_utils_ss.ppm2hrtt
162 (p_item_type => p_item_type
163 ,p_item_key => p_item_key
164 ,p_activity_id => p_activity_id
165 ,p_login_person_id => p_login_person_id
166 ,p_review_proc_call => l_review_proc_call
167 ,p_transaction_step_id => l_txstepids(i)
168 ,p_force_new_transaction => l_force
169 );
170 end loop;
171 commit;
172 end if;
173 p_return_status := fnd_api.G_RET_STS_SUCCESS;
174 p_msg_count := 0;
175 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 40);
176 exception
177 when others then
178 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL:2', 50);
179 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
180 fnd_message.set_token('WHERE', l_proc);
181 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
182 fnd_msg_pub.add;
183 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
184 fnd_msg_pub.count_and_get
185 (p_count => p_msg_count
186 ,p_data => p_msg_data
187 );
188 return;
189 end post_submit_work;
190 ---------------------------< include_in_page >-----------------------------
191 function include_in_page
192 (p_page in varchar2
193 ,p_state in varchar2
194 ) return varchar2 is
195 include boolean := false;
196 begin
197 --
198 -- Only interested in the Summary and Review Pages.
199 --
200 if p_page = C_SUMMARY_PAGE then
201 include :=
202 (p_state <> C_STATE_FREED and p_state <> C_STATE_DELETED);
203 elsif p_page = C_REVIEW_PAGE then
204 include :=
205 (p_state <> C_STATE_FREED and p_state <> C_STATE_EXISTING);
206 end if;
207 --
208 -- Include the change.
209 --
210 if include then
211 return 'Y';
212 end if;
213 --
214 -- Don't include the change.
215 --
216 return 'N';
217 end include_in_page;
218 ------------------------< update_remaining_pay_ppm >-----------------------
219 procedure update_remaining_pay_ppm
220 (p_transaction_id in number
221 ) is
222 l_txstepids varchar2(2000);
223 l_amount_type varchar2(2000);
224 l_start number;
225 l_next varchar2(2000);
226 l_txstepid number;
227 l_state varchar2(2000);
228 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
229 l_changes boolean;
230 l_bank boolean;
231 begin
232 l_txstepids := gettxstepids
233 (p_transaction_id => p_transaction_id
234 ,p_summary_page => true
235 );
236 if l_txstepids is null then
237 return;
238 else
239 l_start := 1;
240 loop
241 exit when l_start = 0;
242 --
243 l_next := pay_ppmv4_utils_ss.nextentry
244 (p_list => l_txstepids
245 ,p_separator => C_COMMA
246 ,p_start => l_start
247 );
248 l_txstepid := to_number(l_next);
249 end loop;
250 --
251 -- Need to check for state changes in this PPM.
252 --
253 select p.state
254 , p.amount_type
255 into l_state
256 , l_amount_type
257 from pay_pss_transaction_steps p
258 where p.transaction_step_id = l_txstepid;
259 if l_state = C_STATE_EXISTING and
260 l_amount_type <> C_REMAINING_PAY then
261 l_state := C_STATE_UPDATED;
262 elsif l_state = C_STATE_UPDATED then
263 --
264 -- Check if this results in a net change.
265 --
266 pay_ppmv4_utils_ss.tt2ppm
267 (p_transaction_step_id => l_txstepid
268 ,p_ppm => l_ppm
269 );
270 l_ppm.amount_type := C_REMAINING_PAY;
271 l_ppm.amount := 100;
272 pay_ppmv4_utils_ss.changedppm
273 (p_ppm => l_ppm
274 ,p_changes => l_changes
275 ,p_bank => l_bank
276 );
277 if not l_changes then
278 l_state := C_STATE_EXISTING;
279 end if;
280 end if;
281 --
282 -- Update the lowest priority PPM.
283 --
284 update pay_pss_transaction_steps p
285 set p.amount_type = C_REMAINING_PAY
286 , p.amount = 100
287 , p.state = l_state
288 where transaction_step_id = l_txstepid
289 ;
290 end if;
291 end update_remaining_pay_ppm;
292 -------------------------------< getwfattr >-------------------------------
293 function getwfattr
294 (p_item_type in varchar2
295 ,p_item_key in varchar2
296 ,p_attr_name in varchar2
297 ,p_ignore in boolean default true
298 ) return varchar2 is
299 l_value varchar2(2000);
300 begin
301 l_value :=
302 wf_engine.getitemattrtext(p_item_type, p_item_key, p_attr_name, p_ignore);
303 if g_debug then
304 hr_utility.trace
305 (g_package || 'getwfattr(' || p_attr_name || ')=' || l_value || ')');
306 end if;
307 return l_value;
308 end getwfattr;
309 ---------------------------------< add2wf >--------------------------------
310 procedure add2wf
311 (p_item_type in varchar2
312 ,p_item_key in varchar2
313 ,p_attr_name in varchar2
314 ,p_value in varchar2
315 ) is
316 begin
317 if g_debug then
318 hr_utility.trace
319 (g_package || 'add2wf(' || p_attr_name || ',' || p_value || ')');
320 end if;
321 wf_engine.additemattr
322 (itemtype => p_item_type
323 ,itemkey => p_item_key
324 ,aname => p_attr_name
325 ,text_value => p_value
326 );
327 end add2wf;
328 ---------------------------------< db2tts >--------------------------------
329 procedure db2tts
330 (p_assignment_id in varchar2
331 ,p_effective_date in varchar2
332 ,p_amount_type in varchar2
333 ,p_item_type in varchar2 default null
334 ,p_item_key in varchar2 default null
335 ,p_run_type_id in varchar2 default null
336 ,p_transaction_id out nocopy varchar2
337 ,p_prepayments out nocopy varchar2
338 ,p_return_status out nocopy varchar2
339 ,p_msg_count out nocopy number
340 ,p_msg_data out nocopy varchar2
341 ) is
342 --
343 -- Count the PPMs for this assignment.
344 --
345 cursor csr_ppm_count
346 (p_assignment_id in number
347 ,p_effective_date in date
348 ) is
349 select count(0)
350 from pay_personal_payment_methods_f ppm
351 , pay_org_payment_methods_f opm
352 where ppm.assignment_id = p_assignment_id
353 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
354
355
356 and opm.org_payment_method_id = ppm.org_payment_method_id
357 and opm.defined_balance_id is not null
358 and (
359 (p_effective_date between ppm.effective_start_date and ppm.effective_end_date
360 and p_effective_date between opm.effective_start_date and opm.effective_end_date)
361 or
362 ( check_future_payments(p_assignment_id ,to_char(p_effective_date,'YYYY-MM-DD'),p_run_type_id ) = 'Y'
363 and p_effective_date < ppm.effective_start_date )
364 );
365
366 --
367 -- Fetch current PPMs for this assignment.
368 --
369 cursor csr_ppms
370 (p_assignment_id in number
371 ,p_effective_date in date
372 ) is
373 select null
374 ,null
375 ,C_PAY_PERSONAL_PAYMENT_METHODS
376 ,C_STATE_EXISTING
377 ,ppm.personal_payment_method_id
378 ,ppm.object_version_number
379 ,ppm.object_version_number
380 ,null
381 ,null
382 ,'N'
383 ,ppm.effective_start_date
384 ,ppm.org_payment_method_id
385 ,p_assignment_id
386 ,ppt.category
387 ,pbt.currency_code
388 ,pay_ppmv4_ss.get_ppm_country(ppm.org_payment_method_id,ppm.business_group_id)
389 ,ppm.priority
390 ,null
391 ,decode(ppm.percentage, null, C_MONETARY, C_PERCENTAGE)
392 ,decode(ppm.percentage, null, ppm.amount, ppm.percentage)
393 ,ppm.external_account_id
394 ,ppm.attribute_category
395 ,ppm.attribute1
396 ,ppm.attribute2
397 ,ppm.attribute3
398 ,ppm.attribute4
399 ,ppm.attribute5
400 ,ppm.attribute6
401 ,ppm.attribute7
402 ,ppm.attribute8
403 ,ppm.attribute9
404 ,ppm.attribute10
405 ,ppm.attribute11
406 ,ppm.attribute12
407 ,ppm.attribute13
408 ,ppm.attribute14
409 ,ppm.attribute15
410 ,ppm.attribute16
411 ,ppm.attribute17
412 ,ppm.attribute18
413 ,ppm.attribute19
414 ,ppm.attribute20
415 ,ppm.priority
416 ,null
417 ,decode(ppm.percentage, null, C_MONETARY, C_PERCENTAGE)
418 ,decode(ppm.percentage, null, ppm.amount, ppm.percentage)
419 ,ppm.external_account_id
420 ,ppm.attribute_category
421 ,ppm.attribute1
422 ,ppm.attribute2
423 ,ppm.attribute3
424 ,ppm.attribute4
425 ,ppm.attribute5
426 ,ppm.attribute6
427 ,ppm.attribute7
428 ,ppm.attribute8
429 ,ppm.attribute9
430 ,ppm.attribute10
431 ,ppm.attribute11
432 ,ppm.attribute12
433 ,ppm.attribute13
434 ,ppm.attribute14
435 ,ppm.attribute15
436 ,ppm.attribute16
437 ,ppm.attribute17
438 ,ppm.attribute18
439 ,ppm.attribute19
440 ,ppm.attribute20
441 ,ppm.run_type_id
442 ,ppm.ppm_information_category
443 ,ppm.ppm_information1
444 ,ppm.ppm_information2
445 ,ppm.ppm_information3
446 ,ppm.ppm_information4
447 ,ppm.ppm_information5
448 ,ppm.ppm_information6
449 ,ppm.ppm_information7
450 ,ppm.ppm_information8
451 ,ppm.ppm_information9
452 ,ppm.ppm_information10
453 ,ppm.ppm_information11
454 ,ppm.ppm_information12
455 ,ppm.ppm_information13
456 ,ppm.ppm_information14
457 ,ppm.ppm_information15
458 ,ppm.ppm_information16
459 ,ppm.ppm_information17
460 ,ppm.ppm_information18
461 ,ppm.ppm_information19
462 ,ppm.ppm_information20
463 ,ppm.ppm_information21
464 ,ppm.ppm_information22
465 ,ppm.ppm_information23
466 ,ppm.ppm_information24
467 ,ppm.ppm_information25
468 ,ppm.ppm_information26
469 ,ppm.ppm_information27
470 ,ppm.ppm_information28
471 ,ppm.ppm_information29
472 ,ppm.ppm_information30
473 ,ppm.ppm_information_category
474 ,ppm.ppm_information1
475 ,ppm.ppm_information2
476 ,ppm.ppm_information3
477 ,ppm.ppm_information4
478 ,ppm.ppm_information5
479 ,ppm.ppm_information6
480 ,ppm.ppm_information7
481 ,ppm.ppm_information8
482 ,ppm.ppm_information9
483 ,ppm.ppm_information10
484 ,ppm.ppm_information11
485 ,ppm.ppm_information12
486 ,ppm.ppm_information13
487 ,ppm.ppm_information14
488 ,ppm.ppm_information15
489 ,ppm.ppm_information16
490 ,ppm.ppm_information17
491 ,ppm.ppm_information18
492 ,ppm.ppm_information19
493 ,ppm.ppm_information20
494 ,ppm.ppm_information21
495 ,ppm.ppm_information22
496 ,ppm.ppm_information23
497 ,ppm.ppm_information24
498 ,ppm.ppm_information25
499 ,ppm.ppm_information26
500 ,ppm.ppm_information27
501 ,ppm.ppm_information28
502 ,ppm.ppm_information29
503 ,ppm.ppm_information30
504 from pay_personal_payment_methods_f ppm
505 , pay_org_payment_methods_f opm
506 , pay_payment_types ppt
507 , pay_defined_balances pdb
508 , pay_balance_types pbt
509 where ppm.assignment_id = p_assignment_id
510 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
511 and opm.org_payment_method_id = ppm.org_payment_method_id
512 and (
513 (p_effective_date between ppm.effective_start_date and ppm.effective_end_date
514 and p_effective_date between opm.effective_start_date and opm.effective_end_date)
515 or
516 ( check_future_payments(p_assignment_id ,to_char(p_effective_date,'YYYY-MM-DD'),p_run_type_id ) = 'Y'
517 and p_effective_date < ppm.effective_start_date )
518 )
519 and opm.defined_balance_id is not null
520 and pdb.defined_balance_id = opm.defined_balance_id
521 and pbt.balance_type_id = pdb.balance_type_id
522 and ppt.payment_type_id = opm.payment_type_id
523 order by ppm.priority
524 ;
525 --
526 -- Check for future-dated prepayments.
527 --
528 cursor csr_prepayments_check
529 (p_personal_payment_method_id in number
530 ,p_effective_date in date
531 ) is
532 select 'Y'
533 from pay_pre_payments ppp
534 , pay_assignment_actions paa
535 , pay_payroll_actions ppa
536 where ppp.personal_payment_method_id = p_personal_payment_method_id
537 and paa.assignment_action_id = ppp.assignment_action_id
538 and ppa.payroll_action_id = paa.payroll_action_id
539 and ppa.effective_date >= p_effective_date
540 ;
541 --
542 -- Date-track row count (to get the delete mode).
543 --
544 cursor csr_dt_row_count
545 (p_personal_payment_method_id in number
546 ) is
547 select count(0)
548 from pay_personal_payment_methods_f ppm
549 where ppm.personal_payment_method_id = p_personal_payment_method_id
550 ;
551 l_effective_date date;
552 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
553 l_dummy varchar2(1);
554 i binary_integer;
555 irt binary_integer;
556 l_count number;
557 l_dt_count number;
558 l_transaction_id number := null;
559 l_item_type varchar2(2000);
560 l_item_key varchar2(2000);
561 l_proc varchar2(2000) := g_package || 'db2tts';
562 begin
563
564 savepoint db2tts;
565 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
566 --
567 fnd_msg_pub.initialize;
568 -------------------------------------------------------------------
569 -- Check if this routine has been called before and use workflow --
570 -- item attributes to return the values. --
571 -------------------------------------------------------------------
572 l_item_type := nvl(p_item_type, g_item_type);
573 l_item_key := nvl(p_item_key, g_item_key);
574 if getwfattr(l_item_type, l_item_key, C_GOT_CONFIG2_WF_ATTR) is not null then
575 p_prepayments :=
576 getwfattr(l_item_type, l_item_key, C_PREPAYMENTS_WF_ATTR, false);
577 p_transaction_id :=
578 getwfattr(l_item_type, l_item_key, C_PSS_TXID_WF_ATTRIBUTE, false);
579 --
580 -- Set successful return status.
581 --
582 p_return_status := fnd_api.G_RET_STS_SUCCESS;
583 fnd_msg_pub.count_and_get
584 (p_count => p_msg_count
585 ,p_data => p_msg_data
586 );
587 return;
588 end if;
589 --
590 -- Flag that this part of the code is being entered.
591 --
592 add2wf(l_item_type, l_item_key, C_GOT_CONFIG2_WF_ATTR, 'Y');
593 --
594 l_effective_date :=
595 to_date(p_effective_date, hr_transaction_ss.g_date_format);
596 p_prepayments := 'N';
597 add2wf(l_item_type, l_item_key, C_PREPAYMENTS_WF_ATTR, 'N');
598 --
599 -- Count the PPMs.
600 --
601 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'FETCH_PPMS', 5);
602 open csr_ppm_count
603 (p_assignment_id => p_assignment_id
604 ,p_effective_date => l_effective_date
605 );
606 fetch csr_ppm_count into l_count;
607 close csr_ppm_count;
608 if l_count = 0 then
609 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 10);
610 --
611 -- Return the transaction_id.
612 --
613 select pay_pss_transactions_s.nextval
614 into l_transaction_id
615 from dual;
616 p_transaction_id := to_char(l_transaction_id);
617 add2wf(l_item_type, l_item_key, C_PSS_TXID_WF_ATTRIBUTE, l_transaction_id);
618 p_msg_count := 0;
619 p_return_status := fnd_api.G_RET_STS_SUCCESS;
620 return;
621 end if;
622 --
623 -- Fetch the PPMs.
624 --
625 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'FETCH_PPMS', 15);
626 i := 1;
627 open csr_ppms
628 (p_assignment_id => p_assignment_id
629 ,p_effective_date => l_effective_date
630 );
631 loop
632 fetch csr_ppms into l_ppm;
633 exit when csr_ppms%notfound;
634 --
635 -- Set the logical priority.
636 --
637 l_ppm.logical_priority := i;
638 l_ppm.o_logical_priority := i;
639 --
640 -- If the amount type is restricted then it's necessary to
641 -- restrict l_ppm.amount_type accordingly. The configuration
642 -- options should have already been checked for consistency against
643 -- the user's data.
644 --
645 if p_amount_type = C_MONETARY_ONLY then
646 l_ppm.amount_type := C_MONETARY_ONLY;
647 elsif p_amount_type = C_PERCENTAGE_ONLY then
648 l_ppm.amount_type := C_PERCENTAGE_ONLY;
649 end if;
650 --
651 -- Check for future-dated prepayments.
652 --
653 pay_ppmv4_utils_ss.seterrorstage
654 (l_proc, 'PREPAYMENTS_CHECK:' || to_char(i), 20);
655 open csr_prepayments_check
656 (p_personal_payment_method_id => l_ppm.personal_payment_method_id
657 ,p_effective_date => l_effective_date
658 );
659 fetch csr_prepayments_check into l_dummy;
660 if csr_prepayments_check%found then
661 p_prepayments := 'Y';
662 wf_engine.setitemattrtext(l_item_type, l_item_key, C_PREPAYMENTS_WF_ATTR, 'Y');
663 --
664 -- Cannot delete the PPM if it is referenced in future-dated
665 -- prepayments.
666 --
667 l_ppm.delete_disabled := 'Y';
668 end if;
669 close csr_prepayments_check;
670 --
671 -- Fill in the date-track modes.
672 --
673 pay_ppmv4_utils_ss.seterrorstage
674 (l_proc, 'SET_DT_MODES:' || to_char(i), 30);
675 if l_ppm.effective_date = l_effective_date then
676 l_ppm.update_datetrack_mode := hr_api.g_correction;
677 --
678 -- Need to check whether or not there are preceding date-track rows as
679 -- this affects the delete date-track mode.
680 --
681 pay_ppmv4_utils_ss.seterrorstage
682 (l_proc, 'DT_ROW_COUNT:' || to_char(i), 40);
683 open csr_dt_row_count
684 (p_personal_payment_method_id => l_ppm.personal_payment_method_id
685 );
686 fetch csr_dt_row_count into l_dt_count;
687 if l_dt_count > 1 then
688 l_ppm.delete_datetrack_mode := hr_api.g_delete;
689 --
690 -- Get the object version number for the preceding row - as the
691 -- delete is done on that row.
692 --
693 select ppm.object_version_number
694 into l_ppm.delete_ovn
695 from pay_personal_payment_methods_f ppm
696 where ppm.personal_payment_method_id =
697 l_ppm.personal_payment_method_id
698 and ppm.effective_end_date = (l_effective_date-1);
699 else
700 l_ppm.delete_datetrack_mode := hr_api.g_zap;
701 end if;
702 close csr_dt_row_count;
703 else
704 l_ppm.update_datetrack_mode := hr_api.g_update;
705 l_ppm.delete_datetrack_mode := hr_api.g_delete;
706 end if;
707 --
708 -- Set the effective date to the correct value.
709 --
710 l_ppm.effective_date := l_effective_date;
711 --
712 -- Mark the Remaining Pay PPM (amount is 100%).
713 --
714 if p_run_type_id is null and i = l_count then
715 l_ppm.amount_type := C_REMAINING_PAY;
716 l_ppm.amount := 100;
717 l_ppm.o_amount_type := C_REMAINING_PAY;
718 l_ppm.o_amount := 100;
719 end if;
720 --
721 -- Now, create a transaction table entry for this PPM.
722 --
723 pay_ppmv4_utils_ss.seterrorstage
724 (l_proc, 'WRITE_TRANSACTION:' || to_char(i), 50);
725 if i > 1 then
726 l_ppm.transaction_id := l_transaction_id;
727 end if;
728 pay_ppmv4_utils_ss.ppm2tt
729 (p_ppm => l_ppm
730 );
731 --
732 -- Save the transaction_id.
733 --
734 if i = 1 then
735 l_transaction_id := l_ppm.transaction_id;
736 end if;
737 --
738 i := i + 1;
739 end loop;
740 close csr_ppms;
741 p_transaction_id := l_transaction_id;
742 add2wf(l_item_type, l_item_key, C_PSS_TXID_WF_ATTRIBUTE, l_transaction_id);
743 p_msg_count := 0;
744 p_return_status := fnd_api.G_RET_STS_SUCCESS;
745 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 60);
746 return;
747 exception
748 when others then
749 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 70);
750 if csr_ppm_count%isopen then
751 close csr_ppm_count;
752 end if;
753 --
754 if csr_ppms%isopen then
755 close csr_ppms;
756 end if;
757 --
758 if csr_prepayments_check%isopen then
759 close csr_prepayments_check;
760 end if;
761 --
762 if csr_dt_row_count%isopen then
763 close csr_dt_row_count;
764 end if;
765 --
766 rollback to db2tts;
767 --
768 -- Set up messages to Oracle Applications API standards as these
769 -- are handled "for free" using checkErrors.
770 --
771 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
772 fnd_msg_pub.initialize;
773 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
774 fnd_message.set_token('WHERE', l_proc);
775 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
776 fnd_msg_pub.add;
777 fnd_msg_pub.count_and_get
778 (p_count => p_msg_count
779 ,p_data => p_msg_data
780 );
781 return;
782 end db2tts;
783 --------------------------------< getcustomopms >----------------------------
784 --
785 -- {Start Of Comments}
786 --
787 -- Description:
788 --
789 -- akadam 30-DEC-2006 115.26 5739568
790 --
791 -- Returns the OPMID's of different Payment category depending upon the value
792 -- of the profile PAY_PSS_PAYMENT_FUNCTION.
793 --
794 -- If profile PAY_PSS_PAYMENT_FUNCTION value is ALL then it would return a string of
795 -- ALL valid OPMID's for each of the payment category attached to the payroll attached
796 -- to the assignment.
797 --
798 -- If profile PAY_PSS_PAYMENT_FUNCTION is null then this function will not be called.
799 --
800 -- If profile PAY_PSS_PAYMENT_FUNCTION is not null and ALL then the value should be a
801 -- valid function name which would be dynamically executed and for each of the payment
802 -- category and the returned OPMID would be returned.
803 --
804 --
805 -- Prerequisites:
806 -- None.
807 --
808 -- Post Success:
809 -- The OUT parameters are filled in for the required payment types.
810 --
811 -- Post Failure:
812 -- An exception is raised.
813 --
814 -- Access Status:
815 -- Internal Development Use Only.
816 --
817 -- {End Of Comments}
818 --
819 procedure getcustomopms
820 (p_assignment_id in number
821 ,p_effective_date in date
822 ,p_cash in boolean
823 ,p_check in boolean
824 ,p_deposit in boolean
825 ,p_function in varchar2
826 ,p_show_paymthd_lov in varchar2
827 ,p_cash_opmid out nocopy number
828 ,p_check_opmid out nocopy number
829 ,p_deposit_opmid out nocopy number
830 ,p_faa_ch_opmid_list out nocopy varchar2
831 ,p_faa_ca_opmid_list out nocopy varchar2
832 ,p_faa_mt_opmid_list out nocopy varchar2
833 ) is
834 --
835 -- Cursor for fetching the OPMs.
836 --
837 cursor csr_opms
838 (p_assignment_id in number
839 ,p_effective_date in date
840 ,p_category in varchar2
841 ,p_org_payment_id in number
842 ) is
843 select opm.org_payment_method_id opmid
844 from pay_org_payment_methods_f opm
845 , per_all_assignments_f paa
846 , pay_org_pay_method_usages_f popmu
847 , pay_payment_types ppt
848 where paa.assignment_id = p_assignment_id
849 and p_effective_date between
850 paa.effective_start_date and paa.effective_end_date
851 and opm.org_payment_method_id = p_org_payment_id
852 and popmu.payroll_id = paa.payroll_id
853 and p_effective_date between
854 popmu.effective_start_date and popmu.effective_end_date
855 and opm.org_payment_method_id = popmu.org_payment_method_id
856 and opm.defined_balance_id is not null
857 and p_effective_date between
858 opm.effective_start_date and opm.effective_end_date
859 and ppt.payment_type_id = opm.payment_type_id
860 and ppt.category = p_category
861 ;
862
863 cursor csr_allopms
864 (p_assignment_id in number
865 ,p_effective_date in date
866 ,p_category in varchar2
867 ) is
868 select opm.org_payment_method_id opmid
869 from pay_org_payment_methods_f opm
870 , per_all_assignments_f paa
871 , pay_org_pay_method_usages_f popmu
872 , pay_payment_types ppt
873 where paa.assignment_id = p_assignment_id
874 and p_effective_date between
875 paa.effective_start_date and paa.effective_end_date
876 and popmu.payroll_id = paa.payroll_id
877 and p_effective_date between
878 popmu.effective_start_date and popmu.effective_end_date
879 and opm.org_payment_method_id = popmu.org_payment_method_id
880 and opm.defined_balance_id is not null
881 and p_effective_date between
882 opm.effective_start_date and opm.effective_end_date
883 and ppt.payment_type_id = opm.payment_type_id
884 and ppt.category = p_category
885 ;
886
887
888 --
889 --
890 l_category varchar2(2000);
891 l_opmids varchar2(2000);
892 l_opmid number;
893 l_found boolean;
894 l_cash_found boolean;
895 l_check_found boolean;
896 l_deposit_found boolean;
897 l_sql_stat varchar2(4000);
898 l_faa_ch_opmid_list varchar2(32767);
899 l_faa_ca_opmid_list varchar2(32767);
900 l_faa_mt_opmid_list varchar2(32767);
901 j binary_integer;
902
903 begin
904 --
905 l_faa_ch_opmid_list := '(';
906 l_faa_ca_opmid_list := '(';
907 l_faa_mt_opmid_list := '(';
908
909 for i in 1..3 loop
910 --
911 if i=1 then
912 l_category := C_DEPOSIT;
913 l_found := not p_deposit;
914 elsif i=2 then
915 l_category := C_CHECK;
916 l_found := not p_check;
917 elsif i=3 then
918 l_category := C_CASH;
919 l_found := not p_cash;
920 end if;
921 --
922
923 if p_show_paymthd_lov = C_YES then
924 /* Bug#9044653
925 * Clearing the flags to have a fresh values.
926 * This enables us to display respective buttons
927 * depending on the payment types attached to the payroll*/
928 l_found := false;
929 l_opmid := null;
930 for rec in csr_allopms
931 (p_assignment_id => p_assignment_id
932 ,p_effective_date => p_effective_date
933 ,p_category => l_category
934 ) loop
935 l_opmids := to_char(rec.opmid);
936
937 if i = 1 then
938 if not l_found then
939 l_opmid := rec.opmid;
940 end if;
941 l_found := true;
942 l_faa_mt_opmid_list := l_faa_mt_opmid_list || l_opmids ||',';
943 elsif i = 2 then
944 if not l_found then
945 l_opmid := rec.opmid;
946 end if;
947 l_found := true;
948 l_faa_ch_opmid_list := l_faa_ch_opmid_list || l_opmids ||',';
949 else
950 if not l_found then
951 l_opmid := rec.opmid;
952 end if;
953 l_found := true;
954 l_faa_ca_opmid_list := l_faa_ca_opmid_list || l_opmids ||',';
955 end if;
956
957
958 end loop;
959 else
960 if not l_found then
961 --
962 l_sql_stat:='select '||p_function||'('||p_assignment_id||','''||to_char(p_effective_date)||''','''||l_category||''') from sys.dual';
963 --
964 begin
965 execute immediate l_sql_stat into l_opmid;
966 exception
967 when others then
968 fnd_message.set_name('PER', 'PAY_34069_NO_PAY_METHOD');
969 fnd_msg_pub.add;
970 fnd_message.raise_error;
971 end;
972 --
973 open csr_opms(p_assignment_id,p_effective_date,l_category,l_opmid);
974 fetch csr_opms into l_opmid;
975 if csr_opms%notfound then
976 close csr_opms;
977 fnd_message.set_name('PER', 'PAY_34069_NO_PAY_METHOD');
978 fnd_msg_pub.add;
979 fnd_message.raise_error;
980 end if;
981 close csr_opms;
982 --
983 l_found := TRUE;
984 --
985 end if;
986 end if;
987 --
988 -- Update the payment category-specific variables.
989 --
990
991 if p_show_paymthd_lov = C_YES then
992
993 if i = 1 then
994 l_deposit_found := l_found;
995 p_deposit_opmid := l_opmid;
996 l_faa_mt_opmid_list := rtrim(l_faa_mt_opmid_list,',') || ')';
997 elsif i = 2 then
998 l_check_found := l_found;
999 p_check_opmid := l_opmid;
1000 l_faa_ch_opmid_list := rtrim(l_faa_ch_opmid_list,',') || ')';
1001 else
1002 l_cash_found := l_found;
1003 p_cash_opmid := l_opmid;
1004 l_faa_ca_opmid_list := rtrim(l_faa_ca_opmid_list,',') || ')';
1005 end if;
1006
1007 else
1008 if i = 1 then
1009 l_deposit_found := l_found;
1010 p_deposit_opmid := l_opmid;
1011 l_faa_mt_opmid_list := l_faa_mt_opmid_list || to_char(p_deposit_opmid) || ')';
1012 elsif i = 2 then
1013 l_check_found := l_found;
1014 p_check_opmid := l_opmid;
1015 l_faa_ch_opmid_list := l_faa_ch_opmid_list || to_char(p_check_opmid) || ')';
1016 else
1017 l_cash_found := l_found;
1018 p_cash_opmid := l_opmid;
1019 l_faa_ca_opmid_list := l_faa_ca_opmid_list || to_char(p_cash_opmid) || ')';
1020 end if;
1021 end if;
1022
1023 p_faa_ch_opmid_list := l_faa_ch_opmid_list;
1024 p_faa_ca_opmid_list := l_faa_ca_opmid_list;
1025 p_faa_mt_opmid_list := l_faa_mt_opmid_list;
1026 --
1027 end loop;
1028 --
1029 --
1030 -- Output error message.
1031 --
1032 if not l_cash_found and not l_check_found and not l_deposit_found then
1033 fnd_message.set_name('PER', 'PAY_52626_NO_PAYMENT_TYPES');
1034 fnd_msg_pub.add;
1035 fnd_message.raise_error;
1036 end if;
1037 --
1038
1039 end getcustomopms;
1040
1041 --------------------------------< getopms >----------------------------
1042 --
1043 -- {Start Of Comments}
1044 --
1045 -- Description:
1046 -- Returns the OPMIDs of the first OPM of the required type from the
1047 -- supplied configuration information.
1048 --
1049 -- Prerequisites:
1050 -- None.
1051 --
1052 -- Post Success:
1053 -- The OUT parameters are filled in for the required payment types.
1054 --
1055 -- Post Failure:
1056 -- An exception is raised.
1057 --
1058 -- Access Status:
1059 -- Internal Development Use Only.
1060 --
1061 -- {End Of Comments}
1062 --
1063 procedure getopms
1064 (p_assignment_id in number
1065 ,p_effective_date in date
1066 ,p_cash in boolean
1067 ,p_check in boolean
1068 ,p_deposit in boolean
1069 ,p_deposit_list in varchar2
1070 ,p_cash_list in varchar2
1071 ,p_check_list in varchar2
1072 ,p_cash_opmid out nocopy number
1073 ,p_check_opmid out nocopy number
1074 ,p_deposit_opmid out nocopy number
1075 ) is
1076 --
1077 -- Cursor for fetching the OPMs.
1078 --
1079 cursor csr_opms
1080 (p_assignment_id in number
1081 ,p_effective_date in date
1082 ,p_category in varchar2
1083 ) is
1084 select opm.org_payment_method_name name
1085 , opm.org_payment_method_id opmid
1086 from pay_org_payment_methods_f opm
1087 , per_all_assignments_f paa
1088 , pay_org_pay_method_usages_f popmu
1089 , pay_payment_types ppt
1090 where paa.assignment_id = p_assignment_id
1091 and p_effective_date between
1092 paa.effective_start_date and paa.effective_end_date
1093 and popmu.payroll_id = paa.payroll_id
1094 and p_effective_date between
1095 popmu.effective_start_date and popmu.effective_end_date
1096 and opm.org_payment_method_id = popmu.org_payment_method_id
1097 and opm.defined_balance_id is not null
1098 and p_effective_date between
1099 opm.effective_start_date and opm.effective_end_date
1100 and ppt.payment_type_id = opm.payment_type_id
1101 and ppt.category = p_category
1102 ;
1103 --
1104 type t_opm_tbl is table of varchar2(2000) index by binary_integer;
1105 --
1106 l_opm_list_mode varchar2(64);
1107 l_category varchar2(2000);
1108 l_opms t_opm_tbl;
1109 l_opmids t_opm_tbl;
1110 l_opmid number;
1111 l_found boolean;
1112 l_cash_found boolean;
1113 l_check_found boolean;
1114 l_deposit_found boolean;
1115 l_list long;
1116 l_start number;
1117 l_name long;
1118 j binary_integer;
1119 begin
1120 --
1121 -- Get and set the mode in which the payment lists will be used. The default
1122 -- mode is C_OPM_LIST_MODE_MATCH.
1123 --
1124 l_opm_list_mode :=
1125 nvl(upper(fnd_profile.value(name => C_OPM_LIST_MODE)), C_OPM_LIST_MODE_MATCH);
1126 if l_opm_list_mode <> C_OPM_LIST_MODE_MATCH and
1127 l_opm_list_mode <> C_OPM_LIST_MODE_ANY and
1128 l_opm_list_mode <> C_OPM_LIST_MODE_LIKE
1129 then
1130 l_opm_list_mode := C_OPM_LIST_MODE_MATCH;
1131 end if;
1132 begin
1133 for i in 1 .. 3 loop
1134 --
1135 -- Initialise general variables.
1136 --
1137 l_opms.delete;
1138 l_opmids.delete;
1139 l_opmid := null;
1140 if i = 1 then
1141 l_category := C_DEPOSIT;
1142 l_found := not p_deposit;
1143 l_list := p_deposit_list;
1144 elsif i = 2 then
1145 l_category := C_CHECK;
1146 l_found := not p_check;
1147 l_list := p_check_list;
1148 else
1149 l_category := C_CASH;
1150 l_found := not p_cash;
1151 l_list := p_cash_list;
1152 end if;
1153 --
1154 -- Only search if required.
1155 --
1156 if not l_found then
1157 --
1158 -- Read in OPMs by category.
1159 --
1160 j := 1;
1161 for rec in csr_opms
1162 (p_assignment_id => p_assignment_id
1163 ,p_effective_date => p_effective_date
1164 ,p_category => l_category
1165 ) loop
1166 l_opms(j) := upper(rec.name);
1167 l_opmids(j) := to_char(rec.opmid);
1168 j := j + 1;
1169 end loop;
1170 --
1171 -- ANY suitable OPM will do.
1172 --
1173 if l_opm_list_mode = C_OPM_LIST_MODE_ANY then
1174 if l_opmids.exists(1) then
1175 l_found := true;
1176 l_opmid := l_opmids(1);
1177 end if;
1178 else
1179 --
1180 -- Match the opm names against the list for the category.
1181 --
1182 l_start := 1;
1183 loop
1184 exit when (l_found or l_start = 0);
1185 --
1186 l_name := pay_ppmv4_utils_ss.nextentry
1187 (p_list => l_list
1188 ,p_separator => C_LIST_SEPARATOR
1189 ,p_start => l_start
1190 );
1191 l_name := upper(l_name);
1192 -- Prevent LIKE behaving like ANY.
1193 exit when l_name is null;
1194 --
1195 -- Look for a match.
1196 --
1197 j := l_opms.first;
1198 loop
1199 exit when (l_found or not l_opms.exists(j));
1200 --
1201 -- OPM name must MATCH.
1202 --
1203 if l_opm_list_mode = C_OPM_LIST_MODE_MATCH then
1204 if l_opms(j) = l_name then
1205 l_found := true;
1206 l_opmid := l_opmids(j);
1207 end if;
1208 --
1209 -- OPM name is LIKE list name.
1210 --
1211 elsif l_opm_list_mode = C_OPM_LIST_MODE_LIKE then
1212 if l_opms(j) like '%' || l_name || '%' then
1213 l_found := true;
1214 l_opmid := l_opmids(j);
1215 end if;
1216 end if;
1217 --
1218 j := l_opms.next(j);
1219 end loop;
1220 end loop;
1221 end if;
1222 end if;
1223 --
1224 -- Update the payment category-specific variables.
1225 --
1226 if i = 1 then
1227 l_deposit_found := l_found;
1228 p_deposit_opmid := l_opmid;
1229 elsif i = 2 then
1230 l_check_found := l_found;
1231 p_check_opmid := l_opmid;
1232 else
1233 l_cash_found := l_found;
1234 p_cash_opmid := l_opmid;
1235 end if;
1236 end loop;
1237
1238 exception
1239 when others then
1240 --
1241 -- Output error message for unexpected error.
1242 --
1243 if csr_opms%isopen then
1244 close csr_opms;
1245 end if;
1246 fnd_message.set_name('PAY', 'PAY_50405_PSS_CFG_PROC_ERROR');
1247 fnd_message.set_token('STAGE', 'GET_OPMS');
1248 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
1249 fnd_msg_pub.add;
1250 fnd_message.raise_error;
1251 end;
1252 --
1253 -- Output error message.
1254 --
1255 if not l_cash_found and not l_check_found and not l_deposit_found then
1256 fnd_message.set_name('PER', 'PAY_52626_NO_PAYMENT_TYPES');
1257 fnd_msg_pub.add;
1258 fnd_message.raise_error;
1259 end if;
1260 end getopms;
1261
1262 -------------------------------< getpayrollinfo >--------------------------
1263 procedure getpayrollinfo
1264 (p_assignment_id in number
1265 ,p_effective_date in date
1266 ,p_default_payment_type out nocopy varchar2
1267 ,p_prenote_required out nocopy varchar2
1268 ,p_payroll_id out nocopy varchar2
1269 ) is
1270 cursor csr_getpayrollinfo
1271 (p_assigment_id in number
1272 ,p_effective_date in date
1273 ) is
1274 select ppt.category
1275 , nvl(ppt.territory_code, hr_api.return_legislation_code(paa.business_group_id))
1276 , pap.prl_information3
1277 , paa.payroll_id
1278 from per_all_assignments_f paa
1279 , pay_all_payrolls_f pap
1280 , pay_org_payment_methods_f popm
1281 , pay_payment_types ppt
1282 where paa.assignment_id = p_assignment_id
1283 and p_effective_date between
1284 paa.effective_start_date and paa.effective_end_date
1285 and pap.payroll_id = paa.payroll_id
1286 and p_effective_date between
1287 pap.effective_start_date and pap.effective_end_date
1288 and popm.org_payment_method_id(+) = pap.default_payment_method_id
1289 and p_effective_date between
1290 popm.effective_start_date and popm.effective_end_date
1291 and ppt.payment_type_id = popm.payment_type_id
1292 ;
1293 --
1294 l_payment_type varchar2(2000);
1295 l_territory_code varchar2(2000);
1296 l_prenote_allowed varchar2(2000);
1297 l_proc varchar2(2000) := g_package || 'getpayrollinfo';
1298 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
1299
1300 begin
1301 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
1302 open csr_getpayrollinfo
1303 (p_assigment_id => p_assignment_id
1304 ,p_effective_date => p_effective_date
1305 );
1306 fetch csr_getpayrollinfo
1307 into l_payment_type
1308 , l_territory_code
1309 , l_prenote_allowed
1310 , l_payroll_id
1311 ;
1312 close csr_getpayrollinfo;
1313 p_default_payment_type := l_payment_type;
1314 --
1315 -- PRL_INFORMATION3 is used as a "Prenote Allowed" indicator on the
1316 -- payroll. The default behaviour was to always have Prenoting, so
1317 -- the code should allow for that. Non-US territories don't have
1318 -- Prenoting.
1319 --
1320 if l_territory_code = 'US' then
1321 if l_prenote_allowed is null or
1322 (l_prenote_allowed <> 'Y' and l_prenote_allowed <> 'N')
1323 then
1324 l_prenote_allowed := 'Y';
1325 end if;
1326 p_prenote_required := l_prenote_allowed;
1327 else
1328 p_prenote_required := 'N';
1329 end if;
1330 p_payroll_id := to_char(l_payroll_id);
1331 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 90);
1332
1333 return;
1334 exception
1335 when others then
1336 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 100);
1337 if csr_getpayrollinfo%isopen then
1338 close csr_getpayrollinfo;
1339 end if;
1340 raise;
1341 end getpayrollinfo;
1342
1343 ---------------------------------< getconfig >-----------------------------
1344 -- akadam 30-DEC-2006 115.26 5739568
1345 --
1346 -- Added some more configuration paremeters to support Foreign Payment Method
1347 --
1348
1349 procedure getconfig
1350 (p_item_type in varchar2
1351 ,p_item_key in varchar2
1352 ,p_activity_id in varchar2
1353 ,p_person_id in varchar2
1354 ,p_assignment_id in varchar2
1355 ,p_effective_date in varchar2
1356 ,p_run_type_id in varchar2 default null
1357 ,p_business_group_id out nocopy varchar2
1358 ,p_territory_code out nocopy varchar2
1359 ,p_id_flex_num out nocopy varchar2
1360 ,p_flex_struct_code out nocopy varchar2
1361 ,p_default_payment_type out nocopy varchar2
1362 ,p_prenote_required out nocopy varchar2
1363 ,p_use_check out nocopy varchar2
1364 ,p_view_only out nocopy varchar2
1365 ,p_payment_types out nocopy varchar2
1366 ,p_amount_types out nocopy varchar2
1367 ,p_max_pay_methods out nocopy varchar2
1368 ,p_cash_opmid out nocopy varchar2
1369 ,p_check_opmid out nocopy varchar2
1370 ,p_deposit_opmid out nocopy varchar2
1371 ,p_obscure_prompt out nocopy varchar2
1372 ,p_obscure_digits out nocopy varchar2
1373 ,p_obscure_char out nocopy varchar2
1374 ,p_return_status out nocopy varchar2
1375 ,p_msg_count out nocopy number
1376 ,p_msg_data out nocopy varchar2
1377 ,p_branch_validation out nocopy varchar2
1378 ,p_show_paymthd_lov out nocopy varchar2
1379 ,p_faa_ch_opmid_list out nocopy varchar2
1380 ,p_faa_ca_opmid_list out nocopy varchar2
1381 ,p_faa_mt_opmid_list out nocopy varchar2
1382 ,p_payroll_id out nocopy varchar2
1383
1384
1385
1386
1387
1388
1389
1390 ) is
1391
1392
1393
1394
1395
1396
1397
1398 -- Check if this assignment has any PPMs with not null (monetary) amount
1399 -- values.
1400 --
1401 cursor csr_monetary
1402 (p_assignment_id in number
1403 ,p_effective_date in date
1404 ) is
1405 select null
1406 from pay_personal_payment_methods_f ppm
1407 where ppm.assignment_id = p_assignment_id
1408 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
1409 and p_effective_date between
1410 ppm.effective_start_date and ppm.effective_end_date
1411 and ppm.amount is not null
1412 ;
1413 --
1414 -- Check if this assignment has any PPMs with not null percentage values.
1415 -- The cursor must ignore 3rd party payments and the remaining pay PPM
1416 -- (highest priority number).
1417 --
1418 cursor csr_percentage
1419 (p_assignment_id in number
1420 ,p_effective_date in date
1421 ) is
1422 select null
1423 from pay_personal_payment_methods_f ppm
1424 , pay_org_payment_methods_f opm
1425 where ppm.assignment_id = p_assignment_id
1426 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
1427 and p_effective_date between
1428 ppm.effective_start_date and ppm.effective_end_date
1429 and opm.org_payment_method_id = ppm.org_payment_method_id
1430 and p_effective_date between
1431 opm.effective_start_date and opm.effective_end_date
1432 and opm.defined_balance_id is not null
1433 and ( (p_run_type_id is not null) or (ppm.priority <
1434 (select max(priority)
1435 from pay_personal_payment_methods_f ppm1
1436 where ppm1.assignment_id = p_assignment_id
1437 and nvl(ppm1.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
1438 and p_effective_date between
1439 ppm1.effective_start_date and ppm1.effective_end_date)))
1440 and ppm.percentage is not null
1441 ;
1442 --
1443 cursor csr_amount_lookup ( p_lookup_code in varchar2
1444 ,p_effective_date in date
1445 ) is
1446 select null
1447 from hr_lookups
1448 where lookup_type = 'PAY_METHOD_AMOUNT_TYPE'
1449 and lookup_code = p_lookup_code
1450 and enabled_flag = 'Y'
1451 and p_effective_date between nvl(start_date_active, p_effective_date)
1452 and nvl(end_date_active, p_effective_date) ;
1453 --
1454 l_start number;
1455 l_part varchar2(32767);
1456 l_wf_attr varchar2(128);
1457 l_wf_attr1 varchar2(128);
1458 l_option_value varchar2(32767);
1459 l_option_value1 varchar2(32767);
1460 l_option varchar2(64);
1461 l_number_value number;
1462 l_view_only boolean := false;
1463 --
1464 -- Database information.
1465 --
1466 l_person_id number;
1467 l_assignment_id number;
1468 l_effective_date date;
1469 l_business_group_id number;
1470 l_territory_code varchar2(64);
1471 --
1472 -- Variables used to get the OPMID values.
1473 --
1474 l_payment_types varchar2(2000);
1475 l_cash boolean := false;
1476 l_check boolean := false;
1477 l_deposit boolean := false;
1478 l_cash_list varchar2(32767);
1479 l_check_list varchar2(32767);
1480 l_deposit_list varchar2(32767);
1481 l_cash_opmid number;
1482 l_check_opmid number;
1483 l_deposit_opmid number;
1484 l_show_paymthd_lov varchar2(200);
1485 l_payroll_id varchar2(200);
1486
1487
1488 --
1489 -- Flags for amount type checking.
1490 --
1491 l_percentage_only boolean;
1492 l_monetary_only boolean;
1493
1494 l_percentage_enabled boolean;
1495 l_monetary_enabled boolean;
1496 l_null varchar2(10);
1497 --
1498 l_function varchar2(1000);
1499 --
1500 l_proc varchar2(2000) := g_package||'getconfig';
1501 --
1502 -- How far the code has progressed.
1503 --
1504 l_stage varchar2(2000);
1505 --
1506 l_msg_count number;
1507 --
1508 -- Convenience procedure for raising error in the initial code.
1509 --
1510 procedure initialerror(p_stage in varchar2, p_additional_info in varchar2) is
1511 begin
1512 fnd_message.set_name('PAY', 'PAY_50405_PSS_CFG_PROC_ERROR');
1513 fnd_message.set_token('STAGE', p_stage);
1514 fnd_message.set_token('ADDITIONAL_INFO', p_additional_info);
1515 fnd_msg_pub.add;
1516 fnd_message.raise_error;
1517 end initialerror;
1518 begin
1519 --
1520 -- Initialise the AOL message tables.
1521 --
1522 l_show_paymthd_lov := null;
1523
1524
1525 fnd_msg_pub.initialize;
1526 -------------------------------------------------------------------
1527 -- Check if this routine has been called before and use workflow --
1528 -- item attributes to return the values. --
1529 -------------------------------------------------------------------
1530 g_item_type := p_item_type;
1531 g_item_key := p_item_key;
1532 if getwfattr(p_item_type, p_item_key, C_GOT_CONFIG1_WF_ATTR) is not null
1533 then
1534 p_business_group_id :=
1535 getwfattr(p_item_type, p_item_key, C_BUS_GROUP_ID_WF_ATTR, false);
1536 p_branch_validation :=
1537 getwfattr(p_item_type, p_item_key, C_BRANCH_CODE_CHK_WF_ATTR, false);
1538 p_territory_code :=
1539 getwfattr(p_item_type, p_item_key, C_LEG_CODE_WF_ATTR, false);
1540 p_id_flex_num :=
1541 getwfattr(p_item_type, p_item_key, C_ID_FLEX_NUM_WF_ATTR, false);
1542 p_flex_struct_code :=
1543 getwfattr(p_item_type, p_item_key, C_FLEX_STRUCT_CODE_WF_ATTR, false);
1544 p_default_payment_type :=
1545 getwfattr(p_item_type, p_item_key, C_DEF_PAYMENT_TYPE_WF_ATTR, false);
1546 p_prenote_required :=
1547 getwfattr(p_item_type, p_item_key, C_PRENOTE_REQUIRED_WF_ATTR, false);
1548 p_use_check:=
1549 getwfattr(p_item_type, p_item_key, C_USE_CHECK_WF_ATTR, false);
1550 p_view_only :=
1551 getwfattr(p_item_type, p_item_key, C_VIEW_ONLY_WF_ATTR, false);
1552 p_payment_types :=
1553 getwfattr(p_item_type, p_item_key, C_PAYMENT_TYPES_WF_ATTR, false);
1554 p_amount_types :=
1555 getwfattr(p_item_type, p_item_key, C_AMOUNT_TYPES_WF_ATTR, false);
1556 p_max_pay_methods :=
1557 getwfattr(p_item_type, p_item_key, C_MAX_PAY_METHODS_WF_ATTR, false);
1558 p_cash_opmid :=
1559 getwfattr(p_item_type, p_item_key, C_CA_OPM_ID_WF_ATTR);
1560 p_check_opmid :=
1561 getwfattr(p_item_type, p_item_key, C_CH_OPM_ID_WF_ATTR);
1562 p_deposit_opmid :=
1563 getwfattr(p_item_type, p_item_key, C_MT_OPM_ID_WF_ATTR);
1564 p_show_paymthd_lov :=
1565 getwfattr(p_item_type, p_item_key, C_FACCT_ALWD_WF_ATTR); --- akadam
1566 p_faa_ch_opmid_list :=
1567 getwfattr(p_item_type, p_item_key, C_FAA_CH_OPMID_LST_WF_ATTR);
1568 p_faa_ca_opmid_list :=
1569 getwfattr(p_item_type, p_item_key, C_FAA_CA_OPMID_LST_WF_ATTR);
1570 p_faa_mt_opmid_list :=
1571 getwfattr(p_item_type, p_item_key, C_FAA_MT_OPMID_LST_WF_ATTR); -- akadam
1572
1573
1574 --
1575 -- Set successful return status.
1576 --
1577 p_return_status := fnd_api.G_RET_STS_SUCCESS;
1578 fnd_msg_pub.count_and_get
1579 (p_count => p_msg_count
1580 ,p_data => p_msg_data
1581 );
1582 return;
1583 end if;
1584 --
1585 -- Flag that this part of the code is being entered.
1586 --
1587 add2wf(p_item_type, p_item_key, C_GOT_CONFIG1_WF_ATTR, 'Y');
1588 --
1589 -- Handle information on the payroll, legislation etc. Errors here indicate
1590 -- serious errors in the code (as opposed to user errors in configuration).
1591 --
1592 begin
1593 --
1594 -- 1. Check that EFFECTIVE_DATE is valid and save it to the workflow.
1595 --
1596 l_stage := 'EFFECTIVE_DATE:1';
1597 l_effective_date :=
1598 to_date(p_effective_date, hr_transaction_ss.g_date_format);
1599 --
1600 l_stage := 'EFFECTIVE_DATE:2';
1601 if not hr_workflow_utility.item_attribute_exists
1602 (p_item_type => p_item_type
1603 ,p_item_key => p_item_key
1604 ,p_name => pay_ppmv4_utils_ss.C_EFFECTIVE_DATE
1605 )
1606 then
1607 wf_engine.additemattr
1608 (itemtype => p_item_type
1609 ,itemkey => p_item_key
1610 ,aname => pay_ppmv4_utils_ss.C_EFFECTIVE_DATE
1611 ,date_value => l_effective_date
1612 );
1613 end if;
1614 --
1615 -- Check that the assignment_id is valid and save it to the workflow.
1616 --
1617 l_stage := 'ASSIGNMENT_ID:1';
1618 l_assignment_id := to_number(p_assignment_id);
1619 select person_id
1620 into l_person_id
1621 from per_all_assignments_f a
1622 where a.assignment_id = l_assignment_id
1623 and l_effective_date
1624 between a.effective_start_date and a.effective_end_date;
1625 --
1626 l_stage := 'ASSIGNMENT_ID:2';
1627 if not hr_workflow_utility.item_attribute_exists
1628 (p_item_type => p_item_type
1629 ,p_item_key => p_item_key
1630 ,p_name => pay_ppmv4_utils_ss.C_ASSIGNMENT_ID
1631 )
1632 then
1633 wf_engine.additemattr
1634 (itemtype => p_item_type
1635 ,itemkey => p_item_key
1636 ,aname => pay_ppmv4_utils_ss.C_ASSIGNMENT_ID
1637 ,number_value => p_assignment_id
1638 );
1639 end if;
1640 --
1641 -- Fetch the payroll information.
1642 --
1643 l_stage := 'PAYROLL_INFO';
1644
1645 getpayrollinfo
1646 (p_assignment_id => l_assignment_id
1647 ,p_effective_date => l_effective_date
1648 ,p_default_payment_type => l_option_value
1649 ,p_prenote_required => l_option_value1
1650 ,p_payroll_id => l_payroll_id
1651 );
1652 p_default_payment_type := l_option_value;
1653 p_prenote_required := l_option_value1;
1654 p_payroll_id := l_payroll_id;
1655 add2wf(p_item_type, p_item_key, C_DEF_PAYMENT_TYPE_WF_ATTR, l_option_value);
1656 add2wf(p_item_type, p_item_key, C_PRENOTE_REQUIRED_WF_ATTR, l_option_value1);
1657 add2wf(p_item_type, p_item_key, C_PAYROLL_ID, l_payroll_id);
1658 --
1659 -- TERRITORY_CODE
1660 --
1661 l_stage := 'TERRITORY_CODE';
1662 select pbg.legislation_code
1663 , pbg.business_group_id
1664 into l_territory_code
1665 , l_business_group_id
1666 from per_business_groups pbg
1667 , per_all_assignments_f asg
1668 where asg.assignment_id = l_assignment_id
1669 and l_effective_date between
1670 asg.effective_start_date and asg.effective_end_date
1671 and pbg.business_group_id + 0 = asg.business_group_id;
1672 p_territory_code := l_territory_code;
1673 p_business_group_id := to_char(l_business_group_id);
1674 add2wf(p_item_type, p_item_key, C_LEG_CODE_WF_ATTR, l_territory_code);
1675 add2wf(p_item_type, p_item_key, C_BUS_GROUP_ID_WF_ATTR, to_char(l_business_group_id));
1676 --
1677 -- ID_FLEX_NUM/FLEX_STRUCT_CODE
1678 --
1679 l_stage := 'ID_FLEX_NUM';
1680 select leg.rule_mode
1681 , flex.id_flex_structure_code
1682 into l_option_value
1683 , l_option_value1
1684 from pay_legislation_rules leg
1685 , fnd_id_flex_structures flex
1686 where leg.legislation_code = l_territory_code
1687 and leg.rule_type = 'E'
1688 and to_char(flex.id_flex_num) = leg.rule_mode
1689 and flex.id_flex_code = 'BANK';
1690 p_id_flex_num := l_option_value;
1691 p_flex_struct_code := l_option_value1;
1692 add2wf(p_item_type, p_item_key, C_ID_FLEX_NUM_WF_ATTR, l_option_value);
1693 add2wf(p_item_type, p_item_key, C_FLEX_STRUCT_CODE_WF_ATTR, l_option_value1);
1694 --
1695 -- USE_CHECK.
1696 --
1697 l_stage := 'USE_CHECK';
1698 begin
1699 select 'Y'
1700 into l_option_value
1701 from pay_legislative_field_info leg
1702 where leg.field_name = 'CHEQUE_CHECK'
1703 and leg.legislation_code = l_territory_code;
1704 exception
1705 when no_data_found then
1706 l_option_value := 'N';
1707 when others then
1708 raise;
1709 end;
1710 p_use_check := l_option_value;
1711 add2wf(p_item_type, p_item_key, C_USE_CHECK_WF_ATTR, l_option_value);
1712 exception
1713 when others then
1714 initialerror(l_stage, sqlerrm);
1715 end;
1716
1717
1718 ---------------------------------------------------
1719 -- Now look at the configuration for the module. --
1720 ---------------------------------------------------
1721 --
1722 -- VIEW_ONLY
1723 --
1724 l_option := C_VIEW_ONLY;
1725 l_option_value := pay_ppmv4_utils_ss.read_wf_config_option
1726 (p_item_type => p_item_type
1727 ,p_item_key => p_item_key
1728 ,p_activity_id => p_activity_id
1729 ,p_option => l_option
1730 );
1731 l_option_value := nvl(l_option_value, 'N');
1732 p_view_only := l_option_value;
1733 l_view_only := (l_option_value = 'Y');
1734 add2wf(p_item_type, p_item_key, C_VIEW_ONLY_WF_ATTR, l_option_value);
1735 --
1736 -- PERMITTED_PAYMENT_TYPES
1737 --
1738 if l_view_only then
1739 --
1740 -- Display everything.
1741 --
1742 p_payment_types := C_ALL;
1743 add2wf(p_item_type, p_item_key, C_PAYMENT_TYPES_WF_ATTR, C_ALL);
1744 p_show_paymthd_lov := C_NO;
1745 add2wf(p_item_type, p_item_key, C_FACCT_ALWD_WF_ATTR, C_NO);
1746 else
1747 l_option := C_PERMITTED_PAYMENT_TYPES;
1748 l_option_value := pay_ppmv4_utils_ss.read_wf_config_option
1749 (p_item_type => p_item_type
1750 ,p_item_key => p_item_key
1751 ,p_activity_id => p_activity_id
1752 ,p_option => l_option
1753 );
1754 l_option_value := nvl(l_option_value, C_ALL);
1755 l_payment_types := l_option_value;
1756 p_payment_types := l_option_value;
1757 l_cash :=
1758 l_option_value = C_CASH_ONLY or l_option_value = C_CASH_AND_CHECK or
1759 l_option_value = C_CASH_AND_DEPOSIT or l_option_value = C_ALL;
1760 l_check :=
1761 l_option_value = C_CHECK_ONLY or l_option_value = C_CASH_AND_CHECK or
1762 l_option_value = C_CHECK_AND_DEPOSIT or l_option_value = C_ALL;
1763 l_deposit :=
1764 l_option_value = C_DEPOSIT_ONLY or l_option_value = C_CASH_AND_DEPOSIT or
1765 l_option_value = C_CHECK_AND_DEPOSIT or l_option_value = C_ALL;
1766 --
1767 -- Avoid problems such as that reported in bug2869603 where the
1768 -- lookup type was incorrectly extended.
1769 --
1770 if l_option_value <> C_CASH_ONLY and
1771 l_option_value <> C_CHECK_ONLY and
1772 l_option_value <> C_DEPOSIT_ONLY and
1773 l_option_value <> C_CASH_AND_CHECK and
1774 l_option_value <> C_CASH_AND_DEPOSIT and
1775 l_option_value <> C_CHECK_AND_DEPOSIT and
1776 l_option_value <> C_ALL
1777 then
1778 goto option_error;
1779 end if;
1780 add2wf(p_item_type, p_item_key, C_PAYMENT_TYPES_WF_ATTR, l_option_value);
1781 end if;
1782 --
1783 -- PERMITTED_AMOUNT_TYPE
1784 -- Need to read the configuration option in all cases as some organisations
1785 -- probably wouldn't want to display Amount Type.
1786 --
1787 l_option := C_PERMITTED_AMOUNT_TYPES;
1788 l_option_value := pay_ppmv4_utils_ss.read_wf_config_option
1789 (p_item_type => p_item_type
1790 ,p_item_key => p_item_key
1791 ,p_activity_id => p_activity_id
1792 ,p_option => l_option
1793 );
1794
1795 l_option_value := nvl(l_option_value, C_EITHER_AMOUNT);
1796 p_amount_types := l_option_value;
1797 l_monetary_only := (l_option_value = C_MONETARY_ONLY);
1798 l_percentage_only := (l_option_value = C_PERCENTAGE_ONLY);
1799
1800 if l_option_value = C_EITHER_AMOUNT then
1801
1802 -- Bug 3697405. Don't display if the lookup codes are not enabled.
1803 -- This Check is done only when the workflow setting is EITHER_AMOUNT,
1804 -- to avoid the scenario where one Amount type is disabled at Workflow
1805 -- level and the other has been disabled at Lookup level which will
1806 -- result in no amount type being available for the transaction.
1807 -- Though this scenario is very unlikely, handling this avoids
1808 -- unnecessary confusion.
1809
1810 open csr_amount_lookup( p_lookup_code => 'MONETARY'
1811 ,p_effective_date => l_effective_date );
1812 fetch csr_amount_lookup into l_null;
1813 if csr_amount_lookup%found then
1814 l_monetary_enabled := true;
1815 else
1816 l_monetary_enabled := false;
1817 end if;
1818 close csr_amount_lookup;
1819
1820 open csr_amount_lookup( p_lookup_code => 'PERCENTAGE'
1821 ,p_effective_date => l_effective_date );
1822 fetch csr_amount_lookup into l_null;
1823 if csr_amount_lookup%found then
1824 l_percentage_enabled := true;
1825 else
1826 l_percentage_enabled := false;
1827 end if;
1828 close csr_amount_lookup;
1829
1830 if l_percentage_enabled and not l_monetary_enabled then
1831
1832 l_option_value := C_PERCENTAGE_ONLY ;
1833 p_amount_types := l_option_value ;
1834 l_percentage_only := true;
1835 l_monetary_only := false;
1836
1837 elsif l_monetary_enabled and not l_percentage_enabled then
1838
1839 l_option_value := C_MONETARY_ONLY ;
1840 p_amount_types := l_option_value ;
1841 l_monetary_only := true;
1842 l_percentage_only := false;
1843
1844 end if;
1845
1846 end if;
1847
1848 add2wf(p_item_type, p_item_key, C_AMOUNT_TYPES_WF_ATTR, l_option_value);
1849 --
1850 -- MAXIMUM_PAYMENT_METHODS
1851 --
1852 if l_view_only then
1853 --
1854 -- Prevent attempted use of this option.
1855 --
1856 p_max_pay_methods := 0;
1857 add2wf(p_item_type, p_item_key, C_MAX_PAY_METHODS_WF_ATTR, '0');
1858 else
1859 l_option := C_MAXIMUM_PAYMENT_METHODS;
1860 l_option_value := pay_ppmv4_utils_ss.read_wf_config_option
1861 (p_item_type => p_item_type
1862 ,p_item_key => p_item_key
1863 ,p_activity_id => p_activity_id
1864 ,p_option => l_option
1865 ,p_number => true
1866 );
1867 begin
1868 l_number_value := trunc(to_number(l_option_value));
1869 exception
1870 when others then
1871 goto option_error;
1872 end;
1873 --
1874 if l_number_value is null then
1875 l_number_value := C_DEFAULT_PAYMENT_METHODS;
1876 elsif l_number_value < C_MIN_PAYMENT_METHODS then
1877 l_number_value := C_MIN_PAYMENT_METHODS;
1878 elsif l_number_value > C_MAX_PAYMENT_METHODS then
1879 l_number_value := C_MAX_PAYMENT_METHODS;
1880 end if;
1881 p_max_pay_methods := to_char(l_number_value);
1882 add2wf(p_item_type, p_item_key, C_MAX_PAY_METHODS_WF_ATTR, to_char(l_number_value));
1883 end if;
1884
1885
1886 --
1887 -- CASH_OPMID
1888 -- CHECK_OPMID
1889 -- DEPOSIT_OPMID
1890 --
1891 if not l_view_only then
1892
1893 l_option := C_CASH_LIST;
1894 l_cash_list := pay_ppmv4_utils_ss.read_wf_config_option
1895 (p_item_type => p_item_type
1896 ,p_item_key => p_item_key
1897 ,p_activity_id => p_activity_id
1898 ,p_option => l_option
1899 );
1900 --
1901 l_option := C_CHECK_LIST;
1902 l_check_list := pay_ppmv4_utils_ss.read_wf_config_option
1903 (p_item_type => p_item_type
1904 ,p_item_key => p_item_key
1905 ,p_activity_id => p_activity_id
1906 ,p_option => l_option
1907 );
1908 --
1909 l_option := C_DEPOSIT_LIST;
1910 l_deposit_list := pay_ppmv4_utils_ss.read_wf_config_option
1911 (p_item_type => p_item_type
1912 ,p_item_key => p_item_key
1913 ,p_activity_id => p_activity_id
1914 ,p_option => l_option
1915 );
1916 --
1917
1918 l_function := substr(fnd_profile.value(name => 'PAY_PSS_PAYMENT_FUNCTION'),1,1000);
1919
1920 if l_function is not null and l_function = C_ALL then
1921 l_show_paymthd_lov := C_YES;
1922 else
1923 l_show_paymthd_lov := C_NO;
1924 end if;
1925
1926 add2wf(p_item_type, p_item_key, C_FACCT_ALWD_WF_ATTR, l_show_paymthd_lov);
1927
1928 p_show_paymthd_lov := l_show_paymthd_lov;
1929
1930
1931 if l_function is not null then
1932
1933 getcustomopms
1934 (p_assignment_id => l_assignment_id
1935 ,p_effective_date => l_effective_date
1936 ,p_cash => l_cash
1937 ,p_check => l_check
1938 ,p_deposit => l_deposit
1939 ,p_function => l_function
1940 ,p_show_paymthd_lov => l_show_paymthd_lov
1941 ,p_cash_opmid => l_cash_opmid
1942 ,p_check_opmid => l_check_opmid
1943 ,p_deposit_opmid => l_deposit_opmid
1944 ,p_faa_ch_opmid_list => p_faa_ch_opmid_list
1945 ,p_faa_ca_opmid_list => p_faa_ca_opmid_list
1946 ,p_faa_mt_opmid_list => p_faa_mt_opmid_list
1947 );
1948 else
1949
1950 getopms
1951 (p_assignment_id => l_assignment_id
1952 ,p_effective_date => l_effective_date
1953 ,p_cash => l_cash
1954 ,p_check => l_check
1955 ,p_deposit => l_deposit
1956 ,p_deposit_list => l_deposit_list
1957 ,p_cash_list => l_cash_list
1958 ,p_check_list => l_check_list
1959 ,p_cash_opmid => l_cash_opmid
1960 ,p_check_opmid => l_check_opmid
1961 ,p_deposit_opmid => l_deposit_opmid
1962 );
1963 end if;
1964 --
1965 -- If OPMID of a particular type cannot be found then reduce the returned
1966 -- Payment Types value accordingly to allow the module to continue.
1967 --
1968 if l_deposit and l_deposit_opmid is null then
1969 l_deposit := false;
1970 if l_payment_types = C_ALL then
1971 l_payment_types := C_CASH_AND_CHECK;
1972 elsif l_payment_types = C_CASH_AND_DEPOSIT then
1973 l_payment_types := C_CASH_ONLY;
1974 elsif l_payment_types = C_CHECK_AND_DEPOSIT then
1975 l_payment_types := C_CHECK_ONLY;
1976 else
1977 l_payment_types := null;
1978 end if;
1979 end if;
1980 --
1981 if l_cash and l_cash_opmid is null then
1982 l_cash := false;
1983 if l_payment_types = C_ALL then
1984 l_payment_types := C_CHECK_AND_DEPOSIT;
1985 elsif l_payment_types = C_CASH_AND_DEPOSIT then
1986 l_payment_types := C_DEPOSIT_ONLY;
1987 elsif l_payment_types = C_CASH_AND_CHECK then
1988 l_payment_types := C_CHECK_ONLY;
1989 else
1990 l_payment_types := null;
1991 end if;
1992 end if;
1993 --
1994 if l_check and l_check_opmid is null then
1995 l_check := false;
1996 if l_payment_types = C_ALL then
1997 l_payment_types := C_CASH_AND_DEPOSIT;
1998 elsif l_payment_types = C_CHECK_AND_DEPOSIT then
1999 l_payment_types := C_DEPOSIT_ONLY;
2000 elsif l_payment_types = C_CASH_AND_CHECK then
2001 l_payment_types := C_CASH_ONLY;
2002 else
2003 l_payment_types := null;
2004 end if;
2005 end if;
2006 --
2007 if l_payment_types is null then
2008 fnd_message.set_name('PER', 'PAY_52626_NO_PAYMENT_TYPES');
2009 fnd_msg_pub.add;
2010 fnd_message.raise_error;
2011 end if;
2012 p_payment_types := l_payment_types;
2013 p_deposit_opmid := to_char(l_deposit_opmid);
2014 p_cash_opmid := to_char(l_cash_opmid);
2015 p_check_opmid := to_char(l_check_opmid);
2016
2017
2018 add2wf(p_item_type, p_item_key, C_CA_OPM_ID_WF_ATTR, to_char(l_cash_opmid));
2019 add2wf(p_item_type, p_item_key, C_CH_OPM_ID_WF_ATTR, to_char(l_check_opmid));
2020 add2wf(p_item_type, p_item_key, C_MT_OPM_ID_WF_ATTR, to_char(l_deposit_opmid));
2021
2022 add2wf(p_item_type, p_item_key, C_FAA_CH_OPMID_LST_WF_ATTR, p_faa_ch_opmid_list);
2023 add2wf(p_item_type, p_item_key, C_FAA_CA_OPMID_LST_WF_ATTR, p_faa_ca_opmid_list);
2024 add2wf(p_item_type, p_item_key, C_FAA_MT_OPMID_LST_WF_ATTR, p_faa_mt_opmid_list);
2025
2026 wf_engine.setitemattrtext
2027 (p_item_type, p_item_key, C_PAYMENT_TYPES_WF_ATTR, l_payment_types);
2028 end if;
2029 --
2030 -- 9. OBSCURE_ACCOUNT_NUMBER
2031 --
2032 l_option := null;
2033 l_option_value := null;
2034 p_obscure_prompt := null;
2035 p_obscure_digits := null;
2036 p_obscure_char := null;
2037 if not l_view_only and l_option_value is not null then
2038 l_start := 1;
2039 --
2040 -- Flexfield prompt.
2041 --
2042 l_part := pay_ppmv4_utils_ss.nextentry
2043 (p_list => l_option_value
2044 ,p_separator => C_LIST_SEPARATOR
2045 ,p_start => l_start
2046 );
2047 if l_part is null or l_start = 0 then
2048 goto option_error;
2049 end if;
2050 p_obscure_prompt := l_part;
2051 --
2052 -- Character used to obscure account information.
2053 --
2054 l_part := pay_ppmv4_utils_ss.nextentry
2055 (p_list => l_option_value
2056 ,p_separator => C_LIST_SEPARATOR
2057 ,p_start => l_start
2058 );
2059 if l_part is null or length(l_part) <> 1 or l_start = 0 then
2060 goto option_error;
2061 end if;
2062 p_obscure_char := l_part;
2063 --
2064 -- Number of characters to obscure.
2065 --
2066 l_part := pay_ppmv4_utils_ss.nextentry
2067 (p_list => l_option_value
2068 ,p_separator => C_LIST_SEPARATOR
2069 ,p_start => l_start
2070 );
2071 if l_part is null then
2072 goto option_error;
2073 end if;
2074 --
2075 -- Check that it's a valid number.
2076 --
2077 begin
2078 l_number_value := trunc(to_number(l_part));
2079 exception
2080 when others then
2081 goto option_error;
2082 end;
2083 p_obscure_digits := to_char(l_number_value);
2084 end if;
2085 --
2086 -- 10. Branch validation.
2087 --
2088 l_option := C_BRANCH_VALIDATION;
2089 l_option_value :=
2090 nvl(upper(fnd_profile.value(name => 'PAY_ENABLE_BANK_BRANCHES')), 'N');
2091 if l_option_value = 'Y' then
2092 --
2093 -- Branch validation is restricted to the following territories:
2094 -- GB
2095 --
2096 if l_territory_code <> 'GB' then
2097 l_option_value := 'N';
2098 end if;
2099 elsif l_option_value <> 'N' then
2100 goto option_error;
2101 end if;
2102 p_branch_validation := l_option_value;
2103 add2wf(p_item_type, p_item_key, C_BRANCH_CODE_CHK_WF_ATTR, l_option_value);
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119 --
2120 -- Check for consistency between the permitted amount types and the
2121 -- user's PPMs.
2122 --
2123 if l_percentage_only then
2124 open csr_monetary
2125 (p_assignment_id => l_assignment_id
2126 ,p_effective_date => l_effective_date
2127 );
2128 fetch csr_monetary into l_option_value;
2129 if csr_monetary%found then
2130 close csr_monetary;
2131 fnd_message.set_name('PER', 'PAY_52625_PPM_USES_AMOUNT');
2132 fnd_msg_pub.add;
2133 fnd_message.raise_error;
2134 end if;
2135 close csr_monetary;
2136 elsif l_monetary_only then
2137 open csr_percentage
2138 (p_assignment_id => l_assignment_id
2139 ,p_effective_date => l_effective_date
2140 );
2141 fetch csr_percentage into l_option_value;
2142 if csr_percentage%found then
2143 close csr_percentage;
2144 fnd_message.set_name('PER', 'PAY_52624_PPM_USES_PERCENTAGE');
2145 fnd_msg_pub.add;
2146 fnd_message.raise_error;
2147 end if;
2148 close csr_percentage;
2149 end if;
2150 --
2151 -- Set up success status.
2152 --
2153 p_return_status := fnd_api.G_RET_STS_SUCCESS;
2154 fnd_msg_pub.count_and_get
2155 (p_count => p_msg_count
2156 ,p_data => p_msg_data
2157 );
2158 return;
2159 <<option_error>>
2160 fnd_message.set_name('PER', 'PAY_52631_PPMSS_OPTION_ERROR');
2161 fnd_message.set_token('OPTION', l_option);
2162 fnd_msg_pub.add;
2163 fnd_message.raise_error;
2164 exception
2165 --
2166 -- Set up the error status.
2167 --
2168 when others then
2169 --
2170 -- Errors should have already been added.
2171 --
2172 p_return_status := fnd_api.G_RET_STS_ERROR;
2173 fnd_msg_pub.count_and_get
2174 (p_count => l_msg_count
2175 ,p_data => p_msg_data
2176 );
2177 if l_msg_count = 0 then
2178 fnd_message.set_name('PAY', 'PAY_50405_PSS_CFG_PROC_ERROR');
2179 fnd_message.set_token('STAGE', 'UNEXPECTED_ERROR');
2180 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
2181 fnd_msg_pub.add;
2182 fnd_msg_pub.count_and_get
2183 (p_count => l_msg_count
2184 ,p_data => p_msg_data
2185 );
2186 end if;
2187
2188
2189 p_msg_count := l_msg_count;
2190 end getconfig;
2191 --------------------------------< gettxstepids >---------------------------
2192 function gettxstepids
2193 (p_transaction_id in varchar2
2194 ,p_review_page in boolean default false
2195 ,p_summary_page in boolean default false
2196 ,p_freed in boolean default false
2197 ) return varchar2 is
2198 cursor csr_txstepids
2199 (p_transaction_id in number
2200 ) is
2201 select transaction_step_id
2202 , state
2203 , logical_priority
2204 from pay_pss_transaction_steps
2205 where transaction_id = p_transaction_id;
2206 --
2207 l_state varchar2(2000);
2208 l_priority number;
2209 l_txstepid_tbl t_number_tbl;
2210 l_add_to_list boolean;
2211 l_list varchar2(32767);
2212 i number;
2213 l_proc varchar2(2000) := g_package || 'gettxstepids';
2214 begin
2215 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
2216 --
2217 -- Look for the transaction_id.
2218 --
2219 if p_transaction_id is null then
2220 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 0);
2221 return null;
2222 end if;
2223 --
2224 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'CURSOR_FOR_LOOP', 10);
2225 for rec in csr_txstepids(p_transaction_id => p_transaction_id) loop
2226 l_state := rec.state;
2227 l_priority := rec.logical_priority;
2228 --
2229 -- Which PPMs get displayed depends upon whether or not this is
2230 -- for the Review page.
2231 --
2232
2233 l_add_to_list :=
2234 (p_summary_page and include_in_page(C_SUMMARY_PAGE, l_state) = 'Y') or
2235 (p_review_page and include_in_page(C_REVIEW_PAGE, l_state) = 'Y') or
2236 (p_freed and l_state = C_STATE_FREED);
2237 if l_add_to_list then
2238
2239 --
2240 -- For the Summary Page, and Review Page (non-deleted PPMs), the
2241 -- transaction_step_ids are put in priority order.
2242 --
2243 if p_summary_page or
2244 p_review_page and l_state <> C_STATE_DELETED then
2245 l_txstepid_tbl(l_priority) := rec.transaction_step_id;
2246 --
2247 -- Otherwise, order does not matter but need to avoid clashes with
2248 -- priority for the Review Page PPMs.
2249 --
2250 else
2251 i := pay_ppmv4_utils_ss.C_MAX_PRIORITY + 1 + rec.transaction_step_id;
2252 l_txstepid_tbl(i) := rec.transaction_step_id;
2253 end if;
2254 end if;
2255 end loop;
2256 --
2257 -- Build list from table.
2258 --
2259 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ADD_TO_LIST', 35);
2260 i := l_txstepid_tbl.first;
2261 loop
2262 exit when not l_txstepid_tbl.exists(i);
2263 --
2264 if l_list is not null then
2265 l_list := l_list || C_COMMA;
2266 end if;
2267 l_list := l_list || to_char(l_txstepid_tbl(i));
2268 --
2269 i := l_txstepid_tbl.next(i);
2270 end loop;
2271 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 40);
2272 return l_list;
2273 exception
2274 when others then
2275 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 50);
2276 raise;
2277 end gettxstepids;
2278 -----------------------------< alloc_real_priorities >---------------------
2279 procedure alloc_real_priorities
2280 (p_transaction_id in varchar2
2281 ,p_assignment_id in varchar2
2282 ,p_effective_date in varchar2
2283 ,p_success out nocopy boolean
2284 ) is
2285 l_effective_date date;
2286 --
2287 -- transaction_step_id variables.
2288 --
2289 l_txstepids varchar2(32767);
2290 l_next varchar2(32767);
2291 l_start number := 1;
2292 l_txstepid number;
2293 --
2294 -- Priority variables.
2295 --
2296 l_priority number;
2297 l_o_priority number;
2298 l_priorities pay_ppmv4_utils_ss.t_boolean_tbl;
2299 i number;
2300 l_proc varchar2(2000) := g_package||'alloc_real_priorities';
2301
2302 l_run_type_id pay_pss_transaction_steps.run_type_id%type;
2303
2304 --
2305 -- cursor to fetch the run_type_id
2306 --
2307
2308 cursor csr_run_type_id(p_transaction_id in number) is
2309 select distinct ppts.run_type_id
2310 from pay_pss_transaction_steps ppts
2311 where ppts.transaction_id = p_transaction_id;
2312
2313 begin
2314 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
2315 p_success := true;
2316 --
2317 -- Fetch transaction_step_ids.
2318 --
2319 l_txstepids := gettxstepids
2320 (p_transaction_id => p_transaction_id
2321 ,p_summary_page => true
2322 );
2323 if l_txstepids is null then
2324 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 5);
2325 return;
2326 end if;
2327
2328 -- Fetch the run type id for the transaction.
2329 -- All the transaction steps for the given transaction will have the same value for run_type_id.
2330
2331 open csr_run_type_id (p_transaction_id => p_transaction_id);
2332 fetch csr_run_type_id into l_run_type_id;
2333
2334 if csr_run_type_id%notfound then
2335 l_run_type_id := null;
2336 end if;
2337
2338 close csr_run_type_id;
2339
2340 --
2341 -- Fetch priorities.
2342 --
2343 l_effective_date :=
2344 to_date(p_effective_date, hr_transaction_ss.g_date_format);
2345 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'GETPRIORITIES', 10);
2346 pay_ppmv4_utils_ss.getpriorities
2347 (p_assignment_id => p_assignment_id
2348 ,p_effective_date => l_effective_date
2349 ,p_run_type_id => l_run_type_id
2350 ,p_priority_tbl => l_priorities
2351 ,p_first_available => i
2352 );
2353 --
2354 -- Start from the beginning of the priority list to avoid unnecessary
2355 -- reallocation of priorities.
2356 --
2357 i := l_priorities.first;
2358 while l_start <> 0 loop
2359 l_next := pay_ppmv4_utils_ss.nextentry
2360 (p_list => l_txstepids
2361 ,p_separator => C_COMMA
2362 ,p_start => l_start
2363 );
2364 l_txstepid := to_number(l_next);
2365
2366 --
2367 -- Fetch original real priority.
2368 --
2369 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'O_REAL_PRIORITY', 20);
2370 select p.o_real_priority
2371 into l_o_priority
2372 from pay_pss_transaction_steps p
2373 where p.transaction_step_id = l_txstepid
2374 ;
2375
2376 --
2377 -- Handle NULL value in the case of a brand new row.
2378 --
2379 if l_o_priority is null then
2380 l_o_priority := pay_ppmv4_utils_ss.C_NO_PRIORITY;
2381 end if;
2382 --
2383 -- Loop through the priority list until there is an available
2384 -- priority or the original real priority value is found.
2385 --
2386 l_priority := pay_ppmv4_utils_ss.C_NO_PRIORITY;
2387
2388
2389 for j in i .. pay_ppmv4_utils_ss.C_MAX_PRIORITY loop
2390 if l_priorities(j) or (not l_priorities(j) and j = l_o_priority) then
2391 l_priority := j;
2392 --
2393 -- Make sure that the next iteration starts one up in the list.
2394 --
2395 i := j + 1;
2396 exit;
2397 end if;
2398 end loop;
2399 --
2400 -- The following IF-statement is an ASSERTION that a priority
2401 -- value must be allocated.
2402 --
2403 if l_priority = pay_ppmv4_utils_ss.C_NO_PRIORITY then
2404 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ALLOC_FAIL', 30);
2405 p_success := false;
2406 return;
2407 end if;
2408 --
2409 -- Write back the real priority value.
2410 --
2411 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'WRITE_PRIORITY', 40);
2412 update pay_pss_transaction_steps p
2413 set p.real_priority = l_priority
2414 where p.transaction_step_id = l_txstepid;
2415 --
2416
2417 end loop;
2418 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 50);
2419 return;
2420 exception
2421 when others then
2422 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 60);
2423 raise;
2424 end alloc_real_priorities;
2425 ----------------------------< update_logical_priority >--------------------
2426 procedure update_logical_priority
2427 (p_transaction_step_id in varchar2
2428 ,p_logical_priority in varchar2
2429 ,p_amount_type in varchar2
2430 ) is
2431 l_logical_priority varchar2(2000);
2432 l_o_logical_priority varchar2(2000);
2433 l_state varchar2(2000);
2434 l_amount_type varchar2(2000);
2435 l_amount number;
2436 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
2437 l_changes boolean;
2438 l_bank boolean;
2439 l_proc varchar2(2000) := g_package||'update_logical_priority';
2440
2441 l_org_amt number;
2442 l_org_percent number;
2443
2444 /*Below cursor created for fixing the bug#7230549. The changes
2445 done are not in compliance with the functionality. So we are
2446 commenting the changes. So we are not using below cursor any more.
2447 If the customer still want this functionality then we need to create
2448 a profile and switch this functionality based on the profile.*/
2449
2450 /*
2451 -- Curosor to get the actual percentage/amount of the ppm.
2452 cursor fetch_org_values is
2453 select ppm.AMOUNT, ppm.PERCENTAGE
2454 from pay_personal_payment_methods_f ppm,
2455 pay_pss_transaction_steps pps
2456 where pps.transaction_step_id = p_transaction_step_id
2457 and pps.PERSONAL_PAYMENT_METHOD_ID = ppm.PERSONAL_PAYMENT_METHOD_ID
2458 and pps.ASSIGNMENT_ID= ppm.ASSIGNMENT_ID
2459 and pps.ORG_PAYMENT_METHOD_ID = ppm.ORG_PAYMENT_METHOD_ID
2460 and pps.effective_date between ppm.effective_start_date and ppm.effective_end_date;
2461 */
2462
2463 begin
2464 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
2465 --
2466 -- Fetch the current logical priority.
2467 --
2468 select to_char(p.logical_priority)
2469 , to_char(p.o_logical_priority)
2470 , p.state
2471 , p.amount_type
2472 , p.amount
2473 into l_logical_priority
2474 , l_o_logical_priority
2475 , l_state
2476 , l_amount_type
2477 , l_amount
2478 from pay_pss_transaction_steps p
2479 where p.transaction_step_id = p_transaction_step_id;
2480 --
2481 -- Only need to do something if the new logical priority differs
2482 -- from the current logical priority.
2483 --
2484 if l_logical_priority <> p_logical_priority then
2485 --
2486 -- chk_foreign_account(p_transaction_step_id => p_transaction_step_id); removed for Foreign Account Enh akadam
2487 --
2488 -- Check if this pay method is the Remaining Pay pay method. If the
2489 -- logical priority moves the pay method up the priority order, the
2490 -- amount type and amount need for the Remaining Pay pay method.
2491 --
2492 if l_amount_type = C_REMAINING_PAY and
2493 p_logical_priority < l_logical_priority then
2494 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'REMAINING_PAY', 5);
2495
2496 /*Below are the changes done based on the bug#7230549. This is not
2497 expected functionality. So we are revering back the changes.
2498 If the customer still want this functionality then we need to
2499 create a profile and switch this functionality based on the profile.*/
2500 /*fetch the oriiginal values from the pay_personal_payment_methods_f table
2501 open fetch_org_values;
2502 fetch fetch_org_values into l_org_amt, l_org_percent;
2503
2504 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'l_org_amt-percent : ' || l_org_amt || '-' || l_org_percent , 2);
2505
2506 -- if no value is returned...use the earlier algorithm to set the value to 0
2507 if(fetch_org_values%notfound) then
2508 l_amount := 0;
2509 if p_amount_type = C_EITHER_AMOUNT then
2510 l_amount_type := C_PERCENTAGE;
2511 else
2512 l_amount_type := p_amount_type;
2513 end if;
2514 else
2515 -- if values are found.. assign it to l_amount and accordingly also change the l_amount_type
2516 if(l_org_amt is not null) then
2517 l_amount := l_org_amt;
2518 l_amount_type := C_MONETARY;
2519 else
2520 l_amount := l_org_percent;
2521 l_amount_type := C_PERCENTAGE;
2522 end if;
2523
2524 end if;*/
2525
2526 l_amount := 0;
2527 if p_amount_type = C_EITHER_AMOUNT then
2528 l_amount_type := C_PERCENTAGE;
2529 else
2530 l_amount_type := p_amount_type;
2531 end if;
2532 end if;
2533 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'LOGICAL_PRIORITY CHANGE', 10);
2534 if nvl(l_o_logical_priority, to_char(hr_api.g_number)) =
2535 p_logical_priority then
2536 --
2537 -- If this is an existing PPM that has been updated then it is
2538 -- necessary to check whether or not this change will reset the PPM
2539 -- to its original state.
2540 --
2541 if l_state = C_STATE_UPDATED then
2542 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'TT2PPM', 20);
2543 pay_ppmv4_utils_ss.tt2ppm
2544 (p_transaction_step_id => to_number(p_transaction_step_id)
2545 ,p_ppm => l_ppm
2546 );
2547 l_ppm.logical_priority := to_number(p_logical_priority);
2548 l_ppm.amount := l_amount;
2549 l_ppm.amount_type := l_amount_type;
2550 pay_ppmv4_utils_ss.changedppm
2551 (p_ppm => l_ppm
2552 ,p_changes => l_changes
2553 ,p_bank => l_bank
2554 );
2555 if not l_changes then
2556 --
2557 -- This priority change takes the PPM back to its original
2558 -- state so the state must be reset to C_STATE_EXISTING.
2559 --
2560 l_state := C_STATE_EXISTING;
2561 end if;
2562 end if;
2563 else
2564 --
2565 -- COMPLETELY NEW LOGICAL PRIORITY
2566 -- The state may need to be updated if this is effectively the
2567 -- first change on an existing PPM.
2568 --
2569 if l_o_logical_priority is not null then
2570 if l_state = C_STATE_EXISTING then
2571 l_state := C_STATE_UPDATED;
2572 end if;
2573 end if;
2574 end if;
2575 --
2576 -- Make and Commit the changes.
2577 --
2578 update pay_pss_transaction_steps p
2579 set p.amount = l_amount
2580 , p.amount_type = l_amount_type
2581 , p.logical_priority = p_logical_priority
2582 , p.state = l_state
2583 where p.transaction_step_id = p_transaction_step_id;
2584 end if;
2585 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 70);
2586 exception
2587 when others then
2588 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 80);
2589 raise;
2590 end update_logical_priority;
2591 ----------------------------< increment_priorities >-----------------------
2592 --
2593 -- {Start Of Comments}
2594 --
2595 -- Description:
2596 -- Increments the logical priority of each transaction table entry by
2597 -- 1. This is done because a newly added PPM is created with a priority
2598 -- of 1.
2599 --
2600 -- Prerequisites:
2601 -- None.
2602 --
2603 -- Post Success:
2604 --
2605 -- Post Failure:
2606 -- An exception is raised.
2607 --
2608 -- Access Status:
2609 -- Internal Development Use Only.
2610 --
2611 -- {End Of Comments}
2612 --
2613 procedure increment_priorities
2614 (p_transaction_id in number
2615 ) is
2616 cursor csr_ppms(p_transaction_id in number) is
2617 select ppts.transaction_step_id
2618 , ppts.logical_priority
2619 , ppts.amount_type
2620 from pay_pss_transaction_steps ppts
2621 where ppts.transaction_id = p_transaction_id
2622 and pay_ppmv4_ss.include_in_page(C_SUMMARY_PAGE, ppts.state) = 'Y';
2623
2624 --
2625 l_proc varchar2(2000) := g_package||'increment_priorities';
2626 begin
2627 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 10);
2628 for rec in csr_ppms(p_transaction_id => p_transaction_id) loop
2629 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'IN-LOOP', 20);
2630 update_logical_priority
2631 (p_transaction_step_id => rec.transaction_step_id
2632 ,p_amount_type => rec.amount_type
2633 ,p_logical_priority => rec.logical_priority + 1
2634 );
2635 end loop;
2636 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 30);
2637 return;
2638 exception
2639 when others then
2640 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 40);
2641 raise;
2642 end increment_priorities;
2643 ----------------------------------< enter_ppm >----------------------------
2644 procedure enter_ppm
2645 (p_transaction_id in varchar2
2646 ,p_transaction_step_id in out nocopy varchar2
2647 ,p_source_table in varchar2
2648 default pay_pss_tx_steps_pkg.C_PAY_PERSONAL_PAYMENT_METHODS
2649 ,p_assignment_id in varchar2
2650 ,p_payment_type in varchar2
2651 ,p_currency_code in varchar2
2652 ,p_org_payment_method_id in varchar2
2653 ,p_territory_code in varchar2
2654 ,p_effective_date in varchar2
2655 ,p_amount_type in varchar2
2656 ,p_amount in number
2657 ,p_external_account_id in number
2658 ,p_attribute_category in varchar2
2659 ,p_attribute1 in varchar2
2660 ,p_attribute2 in varchar2
2661 ,p_attribute3 in varchar2
2662 ,p_attribute4 in varchar2
2663 ,p_attribute5 in varchar2
2664 ,p_attribute6 in varchar2
2665 ,p_attribute7 in varchar2
2666 ,p_attribute8 in varchar2
2667 ,p_attribute9 in varchar2
2668 ,p_attribute10 in varchar2
2669 ,p_attribute11 in varchar2
2670 ,p_attribute12 in varchar2
2671 ,p_attribute13 in varchar2
2672 ,p_attribute14 in varchar2
2673 ,p_attribute15 in varchar2
2674 ,p_attribute16 in varchar2
2675 ,p_attribute17 in varchar2
2676 ,p_attribute18 in varchar2
2677 ,p_attribute19 in varchar2
2678 ,p_attribute20 in varchar2
2679 ,p_run_type_id in varchar2 default null
2680 ,p_ppm_information_category in varchar2 default null
2681 ,p_ppm_information1 in varchar2 default null
2682 ,p_ppm_information2 in varchar2 default null
2683 ,p_ppm_information3 in varchar2 default null
2684 ,p_ppm_information4 in varchar2 default null
2685 ,p_ppm_information5 in varchar2 default null
2686 ,p_ppm_information6 in varchar2 default null
2687 ,p_ppm_information7 in varchar2 default null
2688 ,p_ppm_information8 in varchar2 default null
2689 ,p_ppm_information9 in varchar2 default null
2690 ,p_ppm_information10 in varchar2 default null
2691 ,p_ppm_information11 in varchar2 default null
2692 ,p_ppm_information12 in varchar2 default null
2693 ,p_ppm_information13 in varchar2 default null
2694 ,p_ppm_information14 in varchar2 default null
2695 ,p_ppm_information15 in varchar2 default null
2696 ,p_ppm_information16 in varchar2 default null
2697 ,p_ppm_information17 in varchar2 default null
2698 ,p_ppm_information18 in varchar2 default null
2699 ,p_ppm_information19 in varchar2 default null
2700 ,p_ppm_information20 in varchar2 default null
2701 ,p_ppm_information21 in varchar2 default null
2702 ,p_ppm_information22 in varchar2 default null
2703 ,p_ppm_information23 in varchar2 default null
2704 ,p_ppm_information24 in varchar2 default null
2705 ,p_ppm_information25 in varchar2 default null
2706 ,p_ppm_information26 in varchar2 default null
2707 ,p_ppm_information27 in varchar2 default null
2708 ,p_ppm_information28 in varchar2 default null
2709 ,p_ppm_information29 in varchar2 default null
2710 ,p_ppm_information30 in varchar2 default null
2711 ,p_return_status out nocopy varchar2
2712 ,p_msg_count out nocopy number
2713 ,p_msg_data out nocopy varchar2
2714 ) is
2715 l_current boolean;
2716 l_original boolean;
2717 l_new_ppm pay_ppmv4_utils_ss.t_ppmv4;
2718 l_saved_ppm pay_ppmv4_utils_ss.t_ppmv4;
2719 l_freed_txstepids varchar2(32767);
2720 l_freed_txstepid varchar2(2000);
2721 l_start number := 1;
2722 l_return_status varchar2(2000);
2723 l_msg_count number;
2724 l_msg_data varchar2(16000);
2725 l_proc varchar2(2000) := g_package||'enter_ppm';
2726 l_segment1 varchar2(2000);
2727 l_segment2 varchar2(2000);
2728 l_segment3 varchar2(2000);
2729 l_segment4 varchar2(2000);
2730 l_segment5 varchar2(2000);
2731 l_segment6 varchar2(2000);
2732 l_segment7 varchar2(2000);
2733 l_segment8 varchar2(2000);
2734 l_segment9 varchar2(2000);
2735 l_segment10 varchar2(2000);
2736 l_segment11 varchar2(2000);
2737 l_segment12 varchar2(2000);
2738 l_segment13 varchar2(2000);
2739 l_segment14 varchar2(2000);
2740 l_segment15 varchar2(2000);
2741 l_segment16 varchar2(2000);
2742 l_segment17 varchar2(2000);
2743 l_segment18 varchar2(2000);
2744 l_segment19 varchar2(2000);
2745 l_segment20 varchar2(2000);
2746 l_segment21 varchar2(2000);
2747 l_segment22 varchar2(2000);
2748 l_segment23 varchar2(2000);
2749 l_segment24 varchar2(2000);
2750 l_segment25 varchar2(2000);
2751 l_segment26 varchar2(2000);
2752 l_segment27 varchar2(2000);
2753 l_segment28 varchar2(2000);
2754 l_segment29 varchar2(2000);
2755 l_segment30 varchar2(2000);
2756 begin
2757
2758 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
2759 --
2760 -- Initialise the AOL message tables.
2761 --
2762 fnd_msg_pub.initialize;
2763 --
2764 -- Write to a t_ppmv4 structure.
2765 --
2766 hr_utility.trace('Creating record : context => '||p_ppm_information_category);
2767 hr_utility.trace('information 1 => '||p_ppm_information1);
2768 hr_utility.trace('information 2 => '||p_ppm_information2);
2769 hr_utility.trace('information 3 => '||p_ppm_information3);
2770 hr_utility.trace('information 4 => '||p_ppm_information4);
2771 hr_utility.trace('p_transaction_id => '||p_transaction_id);
2772 hr_utility.trace('p_transaction_id => '||p_transaction_id);
2773 hr_utility.trace('p_assignment_id => '||p_assignment_id);
2774 hr_utility.trace('p_org_payment_method_id => '||p_org_payment_method_id);
2775 hr_utility.trace('p_payment_type => '||p_payment_type);
2776
2777 l_new_ppm.transaction_id := p_transaction_id;
2778 l_new_ppm.transaction_step_id := p_transaction_step_id;
2779 l_new_ppm.assignment_id := p_assignment_id;
2780 l_new_ppm.payment_type := p_payment_type;
2781 l_new_ppm.currency_code := p_currency_code;
2782 l_new_ppm.org_payment_method_id := p_org_payment_method_id;
2783 l_new_ppm.territory_code := p_territory_code;
2784 l_new_ppm.effective_date :=
2785 to_date(p_effective_date, hr_transaction_ss.g_date_format);
2786 l_new_ppm.amount_type := p_amount_type;
2787 l_new_ppm.amount := p_amount;
2788 l_new_ppm.external_account_id := p_external_account_id;
2789 l_new_ppm.attribute_category := p_attribute_category;
2790 l_new_ppm.attribute1 := p_attribute1;
2791 l_new_ppm.attribute2 := p_attribute2;
2792 l_new_ppm.attribute3 := p_attribute3;
2793 l_new_ppm.attribute4 := p_attribute4;
2794 l_new_ppm.attribute5 := p_attribute5;
2795 l_new_ppm.attribute6 := p_attribute6;
2796 l_new_ppm.attribute7 := p_attribute7;
2797 l_new_ppm.attribute8 := p_attribute8;
2798 l_new_ppm.attribute9 := p_attribute9;
2799 l_new_ppm.attribute10 := p_attribute10;
2800 l_new_ppm.attribute11 := p_attribute11;
2801 l_new_ppm.attribute12 := p_attribute12;
2802 l_new_ppm.attribute13 := p_attribute13;
2803 l_new_ppm.attribute14 := p_attribute14;
2804 l_new_ppm.attribute15 := p_attribute15;
2805 l_new_ppm.attribute16 := p_attribute16;
2806 l_new_ppm.attribute17 := p_attribute17;
2807 l_new_ppm.attribute18 := p_attribute18;
2808 l_new_ppm.attribute19 := p_attribute19;
2809 l_new_ppm.attribute20 := p_attribute20;
2810 l_new_ppm.source_table := p_source_table;
2811 l_new_ppm.update_ovn := 1;
2812 l_new_ppm.delete_disabled := 'N';
2813 l_new_ppm.run_type_id := p_run_type_id;
2814 l_new_ppm.ppm_information_category := p_ppm_information_category;
2815 l_new_ppm.ppm_information1 := p_ppm_information1;
2816 l_new_ppm.ppm_information2 := p_ppm_information2;
2817 l_new_ppm.ppm_information3 := p_ppm_information3;
2818 l_new_ppm.ppm_information4 := p_ppm_information4;
2819 l_new_ppm.ppm_information5 := p_ppm_information5;
2820 l_new_ppm.ppm_information6 := p_ppm_information6;
2821 l_new_ppm.ppm_information7 := p_ppm_information7;
2822 l_new_ppm.ppm_information8 := p_ppm_information8;
2823 l_new_ppm.ppm_information9 := p_ppm_information9;
2824 l_new_ppm.ppm_information10 := p_ppm_information10;
2825 l_new_ppm.ppm_information11 := p_ppm_information11;
2826 l_new_ppm.ppm_information12 := p_ppm_information12;
2827 l_new_ppm.ppm_information13 := p_ppm_information13;
2828 l_new_ppm.ppm_information14 := p_ppm_information14;
2829 l_new_ppm.ppm_information15 := p_ppm_information15;
2830 l_new_ppm.ppm_information16 := p_ppm_information16;
2831 l_new_ppm.ppm_information17 := p_ppm_information17;
2832 l_new_ppm.ppm_information18 := p_ppm_information18;
2833 l_new_ppm.ppm_information19 := p_ppm_information19;
2834 l_new_ppm.ppm_information20 := p_ppm_information20;
2835 l_new_ppm.ppm_information21 := p_ppm_information21;
2836 l_new_ppm.ppm_information22 := p_ppm_information22;
2837 l_new_ppm.ppm_information23 := p_ppm_information23;
2838 l_new_ppm.ppm_information24 := p_ppm_information24;
2839 l_new_ppm.ppm_information25 := p_ppm_information25;
2840 l_new_ppm.ppm_information26 := p_ppm_information26;
2841 l_new_ppm.ppm_information27 := p_ppm_information27;
2842 l_new_ppm.ppm_information28 := p_ppm_information28;
2843 l_new_ppm.ppm_information29 := p_ppm_information29;
2844 l_new_ppm.ppm_information30 := p_ppm_information30;
2845 --------------------
2846 -- AN UPDATED PPM --
2847 --------------------
2848 if p_transaction_step_id is not null then
2849 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'TT2PPM', 10);
2850 pay_ppmv4_utils_ss.tt2ppm
2851 (p_transaction_step_id => p_transaction_step_id
2852 ,p_ppm => l_saved_ppm
2853 );
2854 --
2855 -- Compare the new and saved PPMs.
2856 --
2857 pay_ppmv4_utils_ss.changedppm
2858 (p_new_ppm => l_new_ppm
2859 ,p_saved_ppm => l_saved_ppm
2860 ,p_original => l_original
2861 ,p_current => l_current
2862 );
2863 --
2864 -- Return if the result is no net change from the saved PPM.
2865 --
2866 if not l_current then
2867 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 20);
2868 --
2869 -- Set up messages to Oracle Applications API standards as these
2870 -- are handled "for free" using checkErrors.
2871 --
2872 p_return_status := fnd_api.G_RET_STS_SUCCESS;
2873 fnd_msg_pub.count_and_get
2874 (p_count => p_msg_count
2875 ,p_data => p_msg_data
2876 );
2877 return;
2878 end if;
2879 --
2880 -- Update L_SAVED_PPM with changes from L_NEW_PPM. This gives a
2881 -- completed PPM for subsequent saving.
2882 --
2883 l_saved_ppm.amount_type := l_new_ppm.amount_type;
2884 l_saved_ppm.amount := l_new_ppm.amount;
2885 l_saved_ppm.external_account_id := l_new_ppm.external_account_id;
2886 l_saved_ppm.attribute_category := l_new_ppm.attribute_category;
2887 l_saved_ppm.attribute1 := l_new_ppm.attribute1;
2888 l_saved_ppm.attribute2 := l_new_ppm.attribute2;
2889 l_saved_ppm.attribute3 := l_new_ppm.attribute3;
2890 l_saved_ppm.attribute4 := l_new_ppm.attribute4;
2891 l_saved_ppm.attribute5 := l_new_ppm.attribute5;
2892 l_saved_ppm.attribute6 := l_new_ppm.attribute6;
2893 l_saved_ppm.attribute7 := l_new_ppm.attribute7;
2894 l_saved_ppm.attribute8 := l_new_ppm.attribute8;
2895 l_saved_ppm.attribute9 := l_new_ppm.attribute9;
2896 l_saved_ppm.attribute10 := l_new_ppm.attribute10;
2897 l_saved_ppm.attribute11 := l_new_ppm.attribute11;
2898 l_saved_ppm.attribute12 := l_new_ppm.attribute12;
2899 l_saved_ppm.attribute13 := l_new_ppm.attribute13;
2900 l_saved_ppm.attribute14 := l_new_ppm.attribute14;
2901 l_saved_ppm.attribute15 := l_new_ppm.attribute15;
2902 l_saved_ppm.attribute15 := l_new_ppm.attribute15;
2903 l_saved_ppm.attribute16 := l_new_ppm.attribute16;
2904 l_saved_ppm.attribute17 := l_new_ppm.attribute17;
2905 l_saved_ppm.attribute18 := l_new_ppm.attribute18;
2906 l_saved_ppm.attribute19 := l_new_ppm.attribute19;
2907 l_saved_ppm.attribute20 := l_new_ppm.attribute20;
2908
2909 l_saved_ppm.ppm_information_category := l_new_ppm.ppm_information_category;
2910 l_saved_ppm.ppm_information1 := l_new_ppm.ppm_information1;
2911 l_saved_ppm.ppm_information2 := l_new_ppm.ppm_information2;
2912 l_saved_ppm.ppm_information3 := l_new_ppm.ppm_information3;
2913 l_saved_ppm.ppm_information4 := l_new_ppm.ppm_information4;
2914 l_saved_ppm.ppm_information5 := l_new_ppm.ppm_information5;
2915 l_saved_ppm.ppm_information6 := l_new_ppm.ppm_information6;
2916 l_saved_ppm.ppm_information7 := l_new_ppm.ppm_information7;
2917 l_saved_ppm.ppm_information8 := l_new_ppm.ppm_information8;
2918 l_saved_ppm.ppm_information9 := l_new_ppm.ppm_information9;
2919 l_saved_ppm.ppm_information10 := l_new_ppm.ppm_information10;
2920 l_saved_ppm.ppm_information11 := l_new_ppm.ppm_information11;
2921 l_saved_ppm.ppm_information12 := l_new_ppm.ppm_information12;
2922 l_saved_ppm.ppm_information13 := l_new_ppm.ppm_information13;
2923 l_saved_ppm.ppm_information14 := l_new_ppm.ppm_information14;
2924 l_saved_ppm.ppm_information15 := l_new_ppm.ppm_information15;
2925 l_saved_ppm.ppm_information16 := l_new_ppm.ppm_information16;
2926 l_saved_ppm.ppm_information17 := l_new_ppm.ppm_information17;
2927 l_saved_ppm.ppm_information18 := l_new_ppm.ppm_information18;
2928 l_saved_ppm.ppm_information19 := l_new_ppm.ppm_information19;
2929 l_saved_ppm.ppm_information20 := l_new_ppm.ppm_information20;
2930 l_saved_ppm.ppm_information21 := l_new_ppm.ppm_information21;
2931 l_saved_ppm.ppm_information22 := l_new_ppm.ppm_information22;
2932 l_saved_ppm.ppm_information23 := l_new_ppm.ppm_information23;
2933 l_saved_ppm.ppm_information24 := l_new_ppm.ppm_information24;
2934 l_saved_ppm.ppm_information25 := l_new_ppm.ppm_information25;
2935 l_saved_ppm.ppm_information26 := l_new_ppm.ppm_information26;
2936 l_saved_ppm.ppm_information27 := l_new_ppm.ppm_information27;
2937 l_saved_ppm.ppm_information28 := l_new_ppm.ppm_information28;
2938 l_saved_ppm.ppm_information29 := l_new_ppm.ppm_information29;
2939 l_saved_ppm.ppm_information30 := l_new_ppm.ppm_information30;
2940 --
2941 -- If there is no difference from the original saved PPM then no
2942 -- input validation is required, but the changes need to be saved.
2943 -- This cannot be done for PPMs newly created during this PSS
2944 -- session (being paranoid just in case the UI does not validate
2945 -- mandatory fields) as the "original" just contains NULL fields
2946 -- which correspond to an invalid PPM.
2947 --
2948 if not l_original and l_saved_ppm.state = C_STATE_UPDATED then
2949 --
2950 -- The above version of changedppm ignores logical_priority so
2951 -- do the check here.
2952 --
2953 if l_saved_ppm.logical_priority = l_saved_ppm.o_logical_priority then
2954 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXISTING', 30);
2955 l_saved_ppm.state := C_STATE_EXISTING;
2956 end if;
2957 --
2958 -- Save the PPM and return.
2959 --
2960 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PPM2TT', 40);
2961 pay_ppmv4_utils_ss.ppm2tt
2962 (p_ppm => l_saved_ppm
2963 );
2964 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 50);
2965 --
2966 -- Set up messages to Oracle Applications API standards as these
2967 -- are handled "for free" using checkErrors().
2968 --
2969 p_return_status := fnd_api.G_RET_STS_SUCCESS;
2970 fnd_msg_pub.count_and_get
2971 (p_count => p_msg_count
2972 ,p_data => p_msg_data
2973 );
2974 return;
2975 end if;
2976 --
2977 -- The PPM has been updated (rather than just having its priority
2978 -- changed) so update its state accordingly.
2979 --
2980 if l_saved_ppm.state = C_STATE_EXISTING then
2981 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXISTING', 60);
2982 l_saved_ppm.state := C_STATE_UPDATED;
2983 end if;
2984 -------------------------
2985 -- A NEWLY CREATED PPM --
2986 -------------------------
2987 else
2988 l_saved_ppm := l_new_ppm;
2989 l_saved_ppm.state := C_STATE_NEW;
2990 l_saved_ppm.logical_priority := 1;
2991 --
2992 -- Reuse any available freed transaction.
2993 --
2994 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'GETTXSTEPIDS', 80);
2995 l_freed_txstepids := gettxstepids
2996 (p_transaction_id => p_transaction_id
2997 ,p_freed => true
2998 );
2999 l_freed_txstepid := pay_ppmv4_utils_ss.nextentry
3000 (p_list => l_freed_txstepids
3001 ,p_separator => C_COMMA
3002 ,p_start => l_start
3003 );
3004 l_saved_ppm.transaction_step_id := to_number(l_freed_txstepid);
3005 end if;
3006 --
3007 -- Get the bank segments, if necessary.
3008 --
3009 if l_saved_ppm.external_account_id is not null then
3010 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'GET_SEGMENTS', 85);
3011 pay_ppmv4_utils_ss.get_bank_segments
3012 (p_external_account_id => l_saved_ppm.external_account_id
3013 ,p_segment1 => l_segment1
3014 ,p_segment2 => l_segment2
3015 ,p_segment3 => l_segment3
3016 ,p_segment4 => l_segment4
3017 ,p_segment5 => l_segment5
3018 ,p_segment6 => l_segment6
3019 ,p_segment7 => l_segment7
3020 ,p_segment8 => l_segment8
3021 ,p_segment9 => l_segment9
3022 ,p_segment10 => l_segment10
3023 ,p_segment11 => l_segment11
3024 ,p_segment12 => l_segment12
3025 ,p_segment13 => l_segment13
3026 ,p_segment14 => l_segment14
3027 ,p_segment15 => l_segment15
3028 ,p_segment16 => l_segment16
3029 ,p_segment17 => l_segment17
3030 ,p_segment18 => l_segment18
3031 ,p_segment19 => l_segment19
3032 ,p_segment20 => l_segment20
3033 ,p_segment21 => l_segment21
3034 ,p_segment22 => l_segment22
3035 ,p_segment23 => l_segment23
3036 ,p_segment24 => l_segment24
3037 ,p_segment25 => l_segment25
3038 ,p_segment26 => l_segment26
3039 ,p_segment27 => l_segment27
3040 ,p_segment28 => l_segment28
3041 ,p_segment29 => l_segment29
3042 ,p_segment30 => l_segment30
3043 );
3044 end if;
3045 --
3046 -- chk_foreign_account(p_transaction_step_id => p_transaction_step_id); removed for Foreign Account Enh akadam
3047 --
3048 -- Validate the PPM changes.
3049 --
3050 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'VALIDATEPPM', 90);
3051 pay_ppmv4_utils_ss.validateppm
3052 (p_state => l_saved_ppm.state
3053 ,p_personal_payment_method_id => l_saved_ppm.personal_payment_method_id
3054 ,p_object_version_number => l_saved_ppm.update_ovn
3055 ,p_update_datetrack_mode => l_saved_ppm.update_datetrack_mode
3056 ,p_effective_date => l_saved_ppm.effective_date
3057 ,p_org_payment_method_id => l_saved_ppm.org_payment_method_id
3058 ,p_assignment_id => l_saved_ppm.assignment_id
3059 ,p_run_type_id => l_saved_ppm.run_type_id
3060 ,p_payment_type => l_saved_ppm.payment_type
3061 ,p_territory_code => l_saved_ppm.territory_code
3062 ,p_amount_type => l_saved_ppm.amount_type
3063 ,p_amount => l_saved_ppm.amount
3064 ,p_external_account_id => l_saved_ppm.external_account_id
3065 ,p_attribute_category => l_saved_ppm.attribute_category
3066 ,p_attribute1 => l_saved_ppm.attribute1
3067 ,p_attribute2 => l_saved_ppm.attribute2
3068 ,p_attribute3 => l_saved_ppm.attribute3
3069 ,p_attribute4 => l_saved_ppm.attribute4
3070 ,p_attribute5 => l_saved_ppm.attribute5
3071 ,p_attribute6 => l_saved_ppm.attribute6
3072 ,p_attribute7 => l_saved_ppm.attribute7
3073 ,p_attribute8 => l_saved_ppm.attribute8
3074 ,p_attribute9 => l_saved_ppm.attribute9
3075 ,p_attribute10 => l_saved_ppm.attribute10
3076 ,p_attribute11 => l_saved_ppm.attribute11
3077 ,p_attribute12 => l_saved_ppm.attribute12
3078 ,p_attribute13 => l_saved_ppm.attribute13
3079 ,p_attribute14 => l_saved_ppm.attribute14
3080 ,p_attribute15 => l_saved_ppm.attribute15
3081 ,p_attribute16 => l_saved_ppm.attribute16
3082 ,p_attribute17 => l_saved_ppm.attribute17
3083 ,p_attribute18 => l_saved_ppm.attribute18
3084 ,p_attribute19 => l_saved_ppm.attribute19
3085 ,p_attribute20 => l_saved_ppm.attribute20
3086 ,p_ppm_information_category => l_saved_ppm.ppm_information_category
3087 ,p_ppm_information1 => l_saved_ppm.ppm_information1
3088 ,p_ppm_information2 => l_saved_ppm.ppm_information2
3089 ,p_ppm_information3 => l_saved_ppm.ppm_information3
3090 ,p_ppm_information4 => l_saved_ppm.ppm_information4
3091 ,p_ppm_information5 => l_saved_ppm.ppm_information5
3092 ,p_ppm_information6 => l_saved_ppm.ppm_information6
3093 ,p_ppm_information7 => l_saved_ppm.ppm_information7
3094 ,p_ppm_information8 => l_saved_ppm.ppm_information8
3095 ,p_ppm_information9 => l_saved_ppm.ppm_information9
3096 ,p_ppm_information10 => l_saved_ppm.ppm_information10
3097 ,p_ppm_information11 => l_saved_ppm.ppm_information11
3098 ,p_ppm_information12 => l_saved_ppm.ppm_information12
3099 ,p_ppm_information13 => l_saved_ppm.ppm_information13
3100 ,p_ppm_information14 => l_saved_ppm.ppm_information14
3101 ,p_ppm_information15 => l_saved_ppm.ppm_information15
3102 ,p_ppm_information16 => l_saved_ppm.ppm_information16
3103 ,p_ppm_information17 => l_saved_ppm.ppm_information17
3104 ,p_ppm_information18 => l_saved_ppm.ppm_information18
3105 ,p_ppm_information19 => l_saved_ppm.ppm_information19
3106 ,p_ppm_information20 => l_saved_ppm.ppm_information20
3107 ,p_ppm_information21 => l_saved_ppm.ppm_information21
3108 ,p_ppm_information22 => l_saved_ppm.ppm_information22
3109 ,p_ppm_information23 => l_saved_ppm.ppm_information23
3110 ,p_ppm_information24 => l_saved_ppm.ppm_information24
3111 ,p_ppm_information25 => l_saved_ppm.ppm_information25
3112 ,p_ppm_information26 => l_saved_ppm.ppm_information26
3113 ,p_ppm_information27 => l_saved_ppm.ppm_information27
3114 ,p_ppm_information28 => l_saved_ppm.ppm_information28
3115 ,p_ppm_information29 => l_saved_ppm.ppm_information29
3116 ,p_ppm_information30 => l_saved_ppm.ppm_information30
3117 ,p_segment1 => l_segment1
3118 ,p_segment2 => l_segment2
3119 ,p_segment3 => l_segment3
3120 ,p_segment4 => l_segment4
3121 ,p_segment5 => l_segment5
3122 ,p_segment6 => l_segment6
3123 ,p_segment7 => l_segment7
3124 ,p_segment8 => l_segment8
3125 ,p_segment9 => l_segment9
3126 ,p_segment10 => l_segment10
3127 ,p_segment11 => l_segment11
3128 ,p_segment12 => l_segment12
3129 ,p_segment13 => l_segment13
3130 ,p_segment14 => l_segment14
3131 ,p_segment15 => l_segment15
3132 ,p_segment16 => l_segment16
3133 ,p_segment17 => l_segment17
3134 ,p_segment18 => l_segment18
3135 ,p_segment19 => l_segment19
3136 ,p_segment20 => l_segment20
3137 ,p_segment21 => l_segment21
3138 ,p_segment22 => l_segment22
3139 ,p_segment23 => l_segment23
3140 ,p_segment24 => l_segment24
3141 ,p_segment25 => l_segment25
3142 ,p_segment26 => l_segment26
3143 ,p_segment27 => l_segment27
3144 ,p_segment28 => l_segment28
3145 ,p_segment29 => l_segment29
3146 ,p_segment30 => l_segment30
3147 ,p_return_status => l_return_status
3148 ,p_msg_data => l_msg_data
3149 ,p_msg_count => l_msg_count
3150 );
3151 --
3152 -- Save the PPM if there were no errors.
3153 --
3154 if l_return_status = fnd_api.G_RET_STS_SUCCESS then
3155 --
3156 -- Increment the logical priorities if this is a brand
3157 -- new PPM - it must be inserted at priority 1.
3158 --
3159 if p_transaction_step_id is null then
3160 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'INCREMENT_PRIORITIES', 100);
3161 increment_priorities
3162 (p_transaction_id => p_transaction_id
3163 );
3164 end if;
3165 --
3166 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PPM2TT', 110);
3167 pay_ppmv4_utils_ss.ppm2tt
3168 (p_ppm => l_saved_ppm
3169 );
3170 --
3171 -- Update p_transaction_step_id.
3172 --
3173 p_transaction_step_id := l_saved_ppm.transaction_step_id;
3174 end if;
3175 --
3176 -- Set up messages to Oracle Applications API standards as these
3177 -- are handled "for free" using checkErrors().
3178 --
3179 p_return_status := l_return_status;
3180 fnd_msg_pub.count_and_get
3181 (p_count => p_msg_count
3182 ,p_data => p_msg_data
3183 );
3184 if l_return_status = fnd_api.G_RET_STS_SUCCESS then
3185 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 120);
3186 else
3187 pay_ppmv4_utils_ss.seterrorstage
3188 (l_proc, 'EXIT:VALIDATE_FAIL:' || l_return_status, 125);
3189 end if;
3190 return;
3191 exception
3192 when others then
3193 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 130);
3194 --
3195 -- Set up messages to Oracle Applications API standards as these
3196 -- are handled "for free" using checkErrors().
3197 --
3198 p_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
3199 fnd_msg_pub.add_exc_msg
3200 (p_pkg_name => g_package
3201 ,p_procedure_name => 'enter_ppm'
3202 );
3203 fnd_msg_pub.count_and_get
3204 (p_count => p_msg_count
3205 ,p_data => p_msg_data
3206 );
3207 return;
3208 end enter_ppm;
3209 ---------------------------------< delete_ppm >----------------------------
3210 procedure delete_ppm
3211 (p_transaction_step_id in varchar2
3212 ,p_return_status out nocopy varchar2
3213 ,p_msg_count out nocopy number
3214 ,p_msg_data out nocopy varchar2
3215 ) is
3216 l_state varchar2(2000);
3217 l_amount_type varchar2(2000);
3218 l_transaction_id number;
3219 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
3220 l_proc varchar2(2000) := g_package||'delete_ppm';
3221 l_message_name varchar2(2000);
3222 begin
3223 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
3224 --
3225 -- Initialise the AOL message tables.
3226 --
3227 fnd_msg_pub.initialize;
3228 --
3229 -- Get additional information about this PPM.
3230 --
3231 select p.state
3232 , p.amount_type
3233 , p.transaction_id
3234 into l_state
3235 , l_amount_type
3236 , l_transaction_id
3237 from pay_pss_transaction_steps p
3238 where p.transaction_step_id = p_transaction_step_id;
3239 --
3240 -- If this is a newly created PPM then it's only necessary to update
3241 -- the state.
3242 --
3243 if l_state = C_STATE_NEW then
3244 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PPM_FREED', 10);
3245 update pay_pss_transaction_steps p
3246 set p.state = C_STATE_FREED
3247 where p.transaction_step_id = p_transaction_step_id;
3248 else
3249 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'TT2PPM', 20);
3250 pay_ppmv4_utils_ss.tt2ppm
3251 (p_transaction_step_id => p_transaction_step_id
3252 ,p_ppm => l_ppm
3253 );
3254 --
3255 -- All the information for deleting the PPM is automatically set up.
3256 -- Call process_api to validate the changes.
3257 --
3258 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PROCESS_API', 25);
3259 begin
3260 pay_ppmv4_utils_ss.process_api
3261 (p_validate => true
3262 ,p_state => C_STATE_DELETED
3263 ,p_effective_date => l_ppm.effective_date
3264 ,p_personal_payment_method_id => l_ppm.personal_payment_method_id
3265 ,p_delete_datetrack_mode => l_ppm.delete_datetrack_mode
3266 ,p_delete_ovn => l_ppm.delete_ovn
3267 );
3268 exception
3269 when others then
3270 hr_message.provide_error;
3271 l_message_name := hr_message.last_message_name;
3272 if l_message_name = 'HR_7360_PPM_DEL_NOT_ALLOWED' or
3273 l_message_name = 'HR_6679_PPM_PRE_PAY' or
3274 l_message_name = 'PER_PRS_PAY_MTD_DISABLE_DEL'
3275 then
3276 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL:1', 30);
3277 --
3278 -- Handle valid DELETE failures:
3279 -- a) Prepayments exist.
3280 -- b) 3rd Party Payroll interface does not allow zap deletes.
3281 --
3282 fnd_message.set_name('PAY', 'PAY_51519_PSS_CANNOT_DELETE');
3283 fnd_msg_pub.add;
3284 p_return_status := fnd_api.G_RET_STS_ERROR;
3285 fnd_msg_pub.count_and_get
3286 (p_count => p_msg_count
3287 ,p_data => p_msg_data
3288 );
3289 return;
3290 else
3291 --
3292 -- Unexpected error so raise the exception.
3293 --
3294 raise;
3295 end if;
3296 end;
3297 l_ppm.logical_priority := null;
3298 l_ppm.amount_type := null;
3299 l_ppm.amount := null;
3300 l_ppm.external_account_id := null;
3301 l_ppm.attribute_category := null;
3302 l_ppm.attribute1 := null;
3303 l_ppm.attribute2 := null;
3304 l_ppm.attribute3 := null;
3305 l_ppm.attribute4 := null;
3306 l_ppm.attribute5 := null;
3307 l_ppm.attribute6 := null;
3308 l_ppm.attribute7 := null;
3309 l_ppm.attribute8 := null;
3310 l_ppm.attribute9 := null;
3311 l_ppm.attribute10 := null;
3312 l_ppm.attribute11 := null;
3313 l_ppm.attribute12 := null;
3314 l_ppm.attribute13 := null;
3315 l_ppm.attribute14 := null;
3316 l_ppm.attribute15 := null;
3317 l_ppm.attribute15 := null;
3318 l_ppm.attribute16 := null;
3319 l_ppm.attribute17 := null;
3320 l_ppm.attribute18 := null;
3321 l_ppm.attribute19 := null;
3322 l_ppm.attribute20 := null;
3323 l_ppm.state := C_STATE_DELETED;
3324
3325 l_ppm.ppm_information_category := null;
3326 l_ppm.ppm_information1 := null;
3327 l_ppm.ppm_information2 := null;
3328 l_ppm.ppm_information3 := null;
3329 l_ppm.ppm_information4 := null;
3330 l_ppm.ppm_information5 := null;
3331 l_ppm.ppm_information6 := null;
3332 l_ppm.ppm_information7 := null;
3333 l_ppm.ppm_information8 := null;
3334 l_ppm.ppm_information9 := null;
3335 l_ppm.ppm_information10 := null;
3336 l_ppm.ppm_information11 := null;
3337 l_ppm.ppm_information12 := null;
3338 l_ppm.ppm_information13 := null;
3339 l_ppm.ppm_information14 := null;
3340 l_ppm.ppm_information15 := null;
3341 l_ppm.ppm_information15 := null;
3342 l_ppm.ppm_information16 := null;
3343 l_ppm.ppm_information17 := null;
3344 l_ppm.ppm_information18 := null;
3345 l_ppm.ppm_information19 := null;
3346 l_ppm.ppm_information20 := null;
3347 l_ppm.ppm_information21 := null;
3348 l_ppm.ppm_information22 := null;
3349 l_ppm.ppm_information23 := null;
3350 l_ppm.ppm_information24 := null;
3351 l_ppm.ppm_information25 := null;
3352 l_ppm.ppm_information26 := null;
3353 l_ppm.ppm_information27 := null;
3354 l_ppm.ppm_information28 := null;
3355 l_ppm.ppm_information29 := null;
3356 l_ppm.ppm_information30 := null;
3357 --
3358 -- Write back the PPM.
3359 --
3360 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'DELETED', 40);
3361 pay_ppmv4_utils_ss.ppm2tt
3362 (p_ppm => l_ppm
3363 );
3364 end if;
3365 --
3366 -- If the Remaining Pay PPM was deleted then it's necessary to create a
3367 -- new Remaining Pay PPM.
3368 --
3369 if l_amount_type = C_REMAINING_PAY then
3370 update_remaining_pay_ppm
3371 (p_transaction_id => l_transaction_id
3372 );
3373 end if;
3374 --
3375 -- Set up messages to Oracle Applications API standards as these
3376 -- are handled "for free" using checkErrors().
3377 --
3378 p_return_status := fnd_api.G_RET_STS_SUCCESS;
3379 fnd_msg_pub.count_and_get
3380 (p_count => p_msg_count
3381 ,p_data => p_msg_data
3382 );
3383 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 45);
3384 return;
3385 exception
3386 when others then
3387 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL:2', 50);
3388 --
3389 -- Set up messages to Oracle Applications API standards as these
3390 -- are handled "for free" using checkErrors().
3391 --
3392 fnd_message.set_name('PAY', 'PAY_51518_PSS_ASSERT_ERROR');
3393 fnd_message.set_token('WHERE', l_proc);
3394 fnd_message.set_token('ADDITIONAL_INFO', sqlerrm);
3395 fnd_msg_pub.add;
3396 p_return_status := fnd_api.G_RET_STS_ERROR;
3397 fnd_msg_pub.count_and_get
3398 (p_count => p_msg_count
3399 ,p_data => p_msg_data
3400 );
3401 return;
3402 end delete_ppm;
3403 --
3404 -------------------------< resequence_priorities >-----------------------
3405 --
3406 -- {Start Of Comments}
3407 --
3408 -- Description:
3409 --
3410 -- Called after the API calls in the transaction to make the pay method
3411 -- go up in sequence order 1, 2, 3, .. N.
3412 --
3413 -- Prerequisites:
3414 -- Also, all of the PPMSS transaction API calls must have been made.
3415 --
3416 -- Post Success:
3417 -- The pay method priorities are updated.
3418 --
3419 -- Post Failure:
3420 -- An exception is raised.
3421 --
3422 -- Access Status:
3423 -- Internal Development Use Only.
3424 --
3425 -- {End Of Comments}
3426 --
3427 procedure resequence_priorities
3428 (p_assignment_id in number
3429 ,p_effective_date in date
3430 ,p_run_type_id in number default null
3431 ,p_transaction_step_id in number default null
3432 ) is
3433 l_proc varchar2(2000) := g_package || 'resequence_priorities';
3434 l_priority number;
3435 l_dt_mode varchar2(30);
3436 l_exaid number;
3437 l_comment_id number;
3438 l_esd date;
3439 l_eed date;
3440
3441 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
3442 --
3443 cursor c_ppms(p_assignment_id in number, p_effective_date in date) is
3444 select ppm.personal_payment_method_id ppmid
3445 , ppm.object_version_number ovn
3446 , ppm.effective_start_date esd
3447 , ppm.priority priority
3448 from pay_personal_payment_methods_f ppm
3449 , pay_org_payment_methods_f opm
3450 where ppm.assignment_id = p_assignment_id
3451 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
3452 and p_effective_date
3453 between ppm.effective_start_date and ppm.effective_end_date
3454 and opm.org_payment_method_id = ppm.org_payment_method_id
3455 and p_effective_date between
3456 opm.effective_start_date and opm.effective_end_date
3457 and opm.defined_balance_id is not null
3458 order by ppm.priority;
3459 begin
3460 --
3461 -- Fix up the priorities.
3462 --
3463 hr_utility.trace('Entering resequence_priorities....');
3464 l_priority := pay_ppmv4_utils_ss.C_MIN_PRIORITY;
3465 for ppm in c_ppms(p_assignment_id, p_effective_date) loop
3466 hr_utility.trace('ppm.priority => '||ppm.priority);
3467 hr_utility.trace('l_priority => '||l_priority);
3468 if ppm.priority <> l_priority then
3469 pay_ppmv4_utils_ss.tt2ppm
3470 (p_transaction_step_id => p_transaction_step_id
3471 ,p_ppm => l_ppm
3472 );
3473 --
3474 -- Set the correct datetrack mode.
3475 --
3476 l_dt_mode := hr_api.g_correction;
3477 if ppm.esd < p_effective_date then
3478 l_dt_mode := hr_api.g_update;
3479 end if;
3480 hr_utility.trace('Calling UPDATE from resequence_priorities');
3481 hr_personal_pay_method_api.update_personal_pay_method
3482 (p_validate => false
3483 ,p_personal_payment_method_id => ppm.ppmid
3484 ,p_object_version_number => ppm.ovn
3485 ,p_priority => l_priority
3486 ,p_effective_date => p_effective_date
3487 ,p_datetrack_update_mode => l_dt_mode
3488 /*,p_ppm_information_category => l_ppm.ppm_information_category
3489 ,p_ppm_information1 => l_ppm.ppm_information1
3490 ,p_ppm_information2 => l_ppm.ppm_information2
3491 ,p_ppm_information3 => l_ppm.ppm_information3
3492 ,p_ppm_information4 => l_ppm.ppm_information4
3493 ,p_ppm_information5 => l_ppm.ppm_information5
3494 ,p_ppm_information6 => l_ppm.ppm_information6
3495 ,p_ppm_information7 => l_ppm.ppm_information7
3496 ,p_ppm_information8 => l_ppm.ppm_information8
3497 ,p_ppm_information9 => l_ppm.ppm_information9
3498 ,p_ppm_information10 => l_ppm.ppm_information10
3499 ,p_ppm_information11 => l_ppm.ppm_information11
3500 ,p_ppm_information12 => l_ppm.ppm_information12
3501 ,p_ppm_information13 => l_ppm.ppm_information13
3502 ,p_ppm_information14 => l_ppm.ppm_information14
3503 ,p_ppm_information15 => l_ppm.ppm_information15
3504 ,p_ppm_information16 => l_ppm.ppm_information16
3505 ,p_ppm_information17 => l_ppm.ppm_information17
3506 ,p_ppm_information18 => l_ppm.ppm_information18
3507 ,p_ppm_information19 => l_ppm.ppm_information19
3508 ,p_ppm_information20 => l_ppm.ppm_information20
3509 ,p_ppm_information21 => l_ppm.ppm_information21
3510 ,p_ppm_information22 => l_ppm.ppm_information22
3511 ,p_ppm_information23 => l_ppm.ppm_information23
3512 ,p_ppm_information24 => l_ppm.ppm_information24
3513 ,p_ppm_information25 => l_ppm.ppm_information25
3514 ,p_ppm_information26 => l_ppm.ppm_information26
3515 ,p_ppm_information27 => l_ppm.ppm_information27
3516 ,p_ppm_information28 => l_ppm.ppm_information28
3517 ,p_ppm_information29 => l_ppm.ppm_information29
3518 ,p_ppm_information30 => l_ppm.ppm_information30*/
3519 ,p_external_account_id => l_exaid
3520 ,p_effective_start_date => l_esd
3521 ,p_effective_end_date => l_eed
3522 ,p_comment_id => l_comment_id
3523 );
3524 hr_utility.trace('Came out from UPDATE in resequence_priorities');
3525 end if;
3526 l_priority := l_priority + 1;
3527 end loop;
3528 exception
3529 when others then
3530 raise;
3531 end resequence_priorities;
3532 -------------------------------< process_api >---------------------------
3533 procedure process_api
3534 (p_transaction_step_id in number
3535 ,p_validate in boolean default false
3536 ) is
3537 l_ppm pay_ppmv4_utils_ss.t_ppmv4;
3538 l_proc varchar2(2000) := g_package||'process_api';
3539 l_pss_txstepid number;
3540 l_segment1 varchar2(2000);
3541 l_segment2 varchar2(2000);
3542 l_segment3 varchar2(2000);
3543 l_segment4 varchar2(2000);
3544 l_segment5 varchar2(2000);
3545 l_segment6 varchar2(2000);
3546 l_segment7 varchar2(2000);
3547 l_segment8 varchar2(2000);
3548 l_segment9 varchar2(2000);
3549 l_segment10 varchar2(2000);
3550 l_segment11 varchar2(2000);
3551 l_segment12 varchar2(2000);
3552 l_segment13 varchar2(2000);
3553 l_segment14 varchar2(2000);
3554 l_segment15 varchar2(2000);
3555 l_segment16 varchar2(2000);
3556 l_segment17 varchar2(2000);
3557 l_segment18 varchar2(2000);
3558 l_segment19 varchar2(2000);
3559 l_segment20 varchar2(2000);
3560 l_segment21 varchar2(2000);
3561 l_segment22 varchar2(2000);
3562 l_segment23 varchar2(2000);
3563 l_segment24 varchar2(2000);
3564 l_segment25 varchar2(2000);
3565 l_segment26 varchar2(2000);
3566 l_segment27 varchar2(2000);
3567 l_segment28 varchar2(2000);
3568 l_segment29 varchar2(2000);
3569 l_segment30 varchar2(2000);
3570 --
3571 l_hr_txpersonid number;
3572 l_hr_txid number;
3573 l_unprocessed number;
3574 begin
3575 --HR_UTILITY.TRACE_ON(NULL,'PPM');
3576 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'ENTER', 0);
3577 l_pss_txstepid := hr_transaction_api.get_number_value
3578 (p_transaction_step_id => p_transaction_step_id
3579 ,p_name => pay_ppmv4_utils_ss.C_TX_STEP_ID_ARG
3580 );
3581
3582 pay_ppmv4_utils_ss.tt2ppm
3583 (p_transaction_step_id => l_pss_txstepid
3584 ,p_ppm => l_ppm
3585 );
3586 --
3587 -- Get the bank segments for deposit PPMs.
3588 --
3589
3590 if l_ppm.external_account_id is not null then
3591 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'GET_BANK_SEGMENTS', 5);
3592 pay_ppmv4_utils_ss.get_bank_segments
3593 (p_external_account_id => l_ppm.external_account_id
3594 ,p_segment1 => l_segment1
3595 ,p_segment2 => l_segment2
3596 ,p_segment3 => l_segment3
3597 ,p_segment4 => l_segment4
3598 ,p_segment5 => l_segment5
3599 ,p_segment6 => l_segment6
3600 ,p_segment7 => l_segment7
3601 ,p_segment8 => l_segment8
3602 ,p_segment9 => l_segment9
3603 ,p_segment10 => l_segment10
3604 ,p_segment11 => l_segment11
3605 ,p_segment12 => l_segment12
3606 ,p_segment13 => l_segment13
3607 ,p_segment14 => l_segment14
3608 ,p_segment15 => l_segment15
3609 ,p_segment16 => l_segment16
3610 ,p_segment17 => l_segment17
3611 ,p_segment18 => l_segment18
3612 ,p_segment19 => l_segment19
3613 ,p_segment20 => l_segment20
3614 ,p_segment21 => l_segment21
3615 ,p_segment22 => l_segment22
3616 ,p_segment23 => l_segment23
3617 ,p_segment24 => l_segment24
3618 ,p_segment25 => l_segment25
3619 ,p_segment26 => l_segment26
3620 ,p_segment27 => l_segment27
3621 ,p_segment28 => l_segment28
3622 ,p_segment29 => l_segment29
3623 ,p_segment30 => l_segment30
3624 );
3625 end if;
3626 --
3627 -- Make the API call.
3628 --
3629 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'PROCESS_API', 10);
3630 hr_utility.trace('l_ppm.ppm_information_category => '||l_ppm.ppm_information_category);
3631 hr_utility.trace('l_pss_txstepid => '||l_pss_txstepid);
3632 pay_ppmv4_utils_ss.process_api
3633 (p_state => l_ppm.state
3634 ,p_personal_payment_method_id => l_ppm.personal_payment_method_id
3635 ,p_object_version_number => l_ppm.update_ovn
3636 ,p_delete_ovn => l_ppm.delete_ovn
3637 ,p_update_datetrack_mode => l_ppm.update_datetrack_mode
3638 ,p_delete_datetrack_mode => l_ppm.delete_datetrack_mode
3639 ,p_effective_date => l_ppm.effective_date
3640 ,p_org_payment_method_id => l_ppm.org_payment_method_id
3641 ,p_assignment_id => l_ppm.assignment_id
3642 ,p_run_type_id => l_ppm.run_type_id
3643 ,p_territory_code => l_ppm.territory_code
3644 ,p_real_priority => l_ppm.real_priority
3645 ,p_amount_type => l_ppm.amount_type
3646 ,p_amount => l_ppm.amount
3647 ,p_attribute_category => l_ppm.attribute_category
3648 ,p_attribute1 => l_ppm.attribute1
3649 ,p_attribute2 => l_ppm.attribute2
3650 ,p_attribute3 => l_ppm.attribute3
3651 ,p_attribute4 => l_ppm.attribute4
3652 ,p_attribute5 => l_ppm.attribute5
3653 ,p_attribute6 => l_ppm.attribute6
3654 ,p_attribute7 => l_ppm.attribute7
3655 ,p_attribute8 => l_ppm.attribute8
3656 ,p_attribute9 => l_ppm.attribute9
3657 ,p_attribute10 => l_ppm.attribute10
3658 ,p_attribute11 => l_ppm.attribute11
3659 ,p_attribute12 => l_ppm.attribute12
3660 ,p_attribute13 => l_ppm.attribute13
3661 ,p_attribute14 => l_ppm.attribute14
3662 ,p_attribute15 => l_ppm.attribute15
3663 ,p_attribute16 => l_ppm.attribute16
3664 ,p_attribute17 => l_ppm.attribute17
3665 ,p_attribute18 => l_ppm.attribute18
3666 ,p_attribute19 => l_ppm.attribute19
3667 ,p_attribute20 => l_ppm.attribute20
3668 ,p_segment1 => l_segment1
3669 ,p_segment2 => l_segment2
3670 ,p_segment3 => l_segment3
3671 ,p_segment4 => l_segment4
3672 ,p_segment5 => l_segment5
3673 ,p_segment6 => l_segment6
3674 ,p_segment7 => l_segment7
3675 ,p_segment8 => l_segment8
3676 ,p_segment9 => l_segment9
3677 ,p_segment10 => l_segment10
3678 ,p_segment11 => l_segment11
3679 ,p_segment12 => l_segment12
3680 ,p_segment13 => l_segment13
3681 ,p_segment14 => l_segment14
3682 ,p_segment15 => l_segment15
3683 ,p_segment16 => l_segment16
3684 ,p_segment17 => l_segment17
3685 ,p_segment18 => l_segment18
3686 ,p_segment19 => l_segment19
3687 ,p_segment20 => l_segment20
3688 ,p_segment21 => l_segment21
3689 ,p_segment22 => l_segment22
3690 ,p_segment23 => l_segment23
3691 ,p_segment24 => l_segment24
3692 ,p_segment25 => l_segment25
3693 ,p_segment26 => l_segment26
3694 ,p_segment27 => l_segment27
3695 ,p_segment28 => l_segment28
3696 ,p_segment29 => l_segment29
3697 ,p_segment30 => l_segment30
3698 ,p_o_real_priority => l_ppm.o_real_priority
3699 ,p_ppm_information_category => l_ppm.ppm_information_category
3700 ,p_ppm_information1 => l_ppm.ppm_information1
3701 ,p_ppm_information2 => l_ppm.ppm_information2
3702 ,p_ppm_information3 => l_ppm.ppm_information3
3703 ,p_ppm_information4 => l_ppm.ppm_information4
3704 ,p_ppm_information5 => l_ppm.ppm_information5
3705 ,p_ppm_information6 => l_ppm.ppm_information6
3706 ,p_ppm_information7 => l_ppm.ppm_information7
3707 ,p_ppm_information8 => l_ppm.ppm_information8
3708 ,p_ppm_information9 => l_ppm.ppm_information9
3709 ,p_ppm_information10 => l_ppm.ppm_information10
3710 ,p_ppm_information11 => l_ppm.ppm_information11
3711 ,p_ppm_information12 => l_ppm.ppm_information12
3712 ,p_ppm_information13 => l_ppm.ppm_information13
3713 ,p_ppm_information14 => l_ppm.ppm_information14
3714 ,p_ppm_information15 => l_ppm.ppm_information15
3715 ,p_ppm_information16 => l_ppm.ppm_information16
3716 ,p_ppm_information17 => l_ppm.ppm_information17
3717 ,p_ppm_information18 => l_ppm.ppm_information18
3718 ,p_ppm_information19 => l_ppm.ppm_information19
3719 ,p_ppm_information20 => l_ppm.ppm_information20
3720 ,p_ppm_information21 => l_ppm.ppm_information21
3721 ,p_ppm_information22 => l_ppm.ppm_information22
3722 ,p_ppm_information23 => l_ppm.ppm_information23
3723 ,p_ppm_information24 => l_ppm.ppm_information24
3724 ,p_ppm_information25 => l_ppm.ppm_information25
3725 ,p_ppm_information26 => l_ppm.ppm_information26
3726 ,p_ppm_information27 => l_ppm.ppm_information27
3727 ,p_ppm_information28 => l_ppm.ppm_information28
3728 ,p_ppm_information29 => l_ppm.ppm_information29
3729 ,p_ppm_information30 => l_ppm.ppm_information30
3730 ,p_validate => p_validate
3731 );
3732 if not p_validate then
3733 --
3734 -- Get the HR transaction information.
3735 --
3736 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'GET_HR_TX_INFO', 20);
3737 select creator_person_id
3738 , transaction_id
3739 into l_hr_txpersonid
3740 , l_hr_txid
3741 from hr_api_transaction_steps
3742 where transaction_step_id = p_transaction_step_id;
3743 --
3744 -- Set the processed flag for this transaction.
3745 --
3746 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'SET_PROCESSED_FLAG', 25);
3747 hr_transaction_api.set_varchar2_value
3748 (p_validate => false
3749 ,p_transaction_step_id => p_transaction_step_id
3750 ,p_person_id => l_hr_txpersonid
3751 ,p_name => pay_ppmv4_utils_ss.C_PROCESSED_FLAG_ARG
3752 ,p_value => 'Y'
3753 );
3754 --
3755 -- Check is any HR transactions are still to be processed.
3756 --
3757 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'COUNT_UNPROCESSED', 30);
3758 select count(*)
3759 into l_unprocessed
3760 from hr_api_transaction_values hatv
3761 where hatv.name = pay_ppmv4_utils_ss.C_PROCESSED_FLAG_ARG
3762 and hatv.varchar2_value = 'N'
3763 and hatv.transaction_step_id in
3764 (select transaction_step_id
3765 from hr_api_transaction_steps hats
3766 where hats.transaction_id = l_hr_txid);
3767 pay_ppmv4_utils_ss.seterrorstage
3768 (l_proc, 'UNPROCESSED_COUNT:' || to_char(l_unprocessed), 35);
3769 --
3770 -- Resequence priorities if nothing left to be processed.
3771 --
3772 if l_unprocessed = 0 then
3773 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'RESEQUENCE_PRIORITIES', 40);
3774 resequence_priorities
3775 (p_assignment_id => l_ppm.assignment_id
3776 ,p_effective_date => l_ppm.effective_date
3777 ,p_run_type_id => l_ppm.run_type_id
3778 ,p_transaction_step_id => l_pss_txstepid
3779 );
3780 end if;
3781 end if;
3782 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:SUCCESS', 50);
3783 return;
3784 exception
3785 when others then
3786 pay_ppmv4_utils_ss.seterrorstage(l_proc, 'EXIT:FAIL', 60);
3787 raise;
3788 end process_api;
3789 ------------------------< delete_pss_transactions >----------------------
3790 procedure delete_ppm_transactions
3791 (item_type in varchar2
3792 ,item_key in varchar2
3793 ,actid in number
3794 ,funmode in varchar2
3795 ,result out nocopy varchar2
3796 ) is
3797 l_transaction_id varchar2(2000);
3798 begin
3799 if funmode = 'RUN' then
3800 --
3801 -- Make sure that the transaction workflow attribute is set-up. It
3802 -- may not be if there was a problem with the configuration.
3803 --
3804 begin
3805 l_transaction_id := wf_engine.getitemattrtext
3806 (itemtype => item_type
3807 ,itemkey => item_key
3808 ,aname => C_PSS_TXID_WF_ATTRIBUTE
3809 );
3810 exception
3811 when others then
3812 l_transaction_id := null;
3813 end;
3814 if l_transaction_id is not null then
3815 pay_pss_tx_steps_pkg.delete_rows
3816 (p_transaction_id => to_number(l_transaction_id)
3817 );
3818 end if;
3819 result := 'SUCCESS';
3820 end if;
3821 exception
3822 when others then
3823 raise;
3824 end delete_ppm_transactions;
3825 --------------------------< resequence_priorities >-----------------------
3826 procedure resequence_priorities
3827 (item_type in varchar2
3828 ,item_key in varchar2
3829 ,actid in number
3830 ,funmode in varchar2
3831 ,result out nocopy varchar2
3832 ) is
3833 begin
3834 --
3835 -- This code must not be changed.
3836 --
3837 result := 'SUCCESS';
3838 end resequence_priorities;
3839 --
3840
3841 -------------------------< get_ppm_country >-----------------------
3842 --
3843 -- {Start Of Comments}
3844 --
3845 -- Description:
3846 -- 11-12-2006 This function gets the Country associated with the PPM
3847 --
3848 --
3849 -- Prerequisites:
3850 -- None.
3851 --
3852 -- Post Success:
3853 -- Always returns the Terirory code associated with the PPM
3854 --
3855 -- Post Failure:
3856 --
3857 -- Access Status:
3858 -- Internal Development Use Only.
3859 --
3860 -- {End Of Comments}
3861 --
3862 function get_ppm_country
3863 (p_org_payment_method_id IN number,
3864 p_business_group_id IN number,
3865 p_return_desc IN VARCHAR2 default 'N'
3866 )return varchar2 IS
3867
3868 cursor c_get_country_code (
3869 cp_org_payment_method_id number) IS
3870 SELECT ppt.territory_code,
3871 TERR.territory_short_name
3872 FROM pay_org_payment_methods_f pom,
3873 pay_payment_types ppt,
3874 FND_TERRITORIES_VL TERR
3875 WHERE pom.org_payment_method_id = cp_org_payment_method_id
3876 AND pom.payment_type_id = ppt.payment_type_id
3877 AND ppt.territory_code = TERR.territory_code (+);
3878
3879 cursor c_get_bg_country (
3880 cp_business_group_id number) IS
3881 SELECT org_inf.org_information9,
3882 TERR.territory_short_name
3883 FROM hr_all_organization_units org,
3884 hr_organization_information org_inf,
3885 FND_TERRITORIES_VL TERR
3886 WHERE org.ORGANIZATION_ID = cp_business_group_id
3887 AND org.ORGANIZATION_ID = org_inf.ORGANIZATION_ID
3888 AND ORG_INFORMATION_CONTEXT = 'Business Group Information'
3889 AND org_inf.org_information9 = TERR.territory_code (+);
3890
3891 l_ppt_territory_code pay_payment_types.territory_code%TYPE;
3892 l_bg_country hr_organization_information.org_information9%TYPE;
3893 l_territory_desc FND_TERRITORIES_VL.territory_short_name%TYPE;
3894
3895 begin
3896 l_ppt_territory_code := null;
3897 l_bg_country := null;
3898 l_territory_desc := null;
3899
3900 open c_get_country_code(p_org_payment_method_id);
3901 fetch c_get_country_code into l_ppt_territory_code, l_territory_desc;
3902 close c_get_country_code;
3903
3904 if l_ppt_territory_code is null then
3905 open c_get_bg_country(p_business_group_id);
3906 fetch c_get_bg_country into l_bg_country,l_territory_desc;
3907 close c_get_bg_country;
3908 if p_return_desc = 'Y' then
3909 return l_territory_desc;
3910 else
3911 return l_bg_country;
3912 end if;
3913 else
3914 if p_return_desc = 'Y' then
3915 return l_territory_desc;
3916 else
3917 return l_ppt_territory_code;
3918 end if;
3919 end if ;
3920
3921 end get_ppm_country;
3922
3923 -------------------------< get_bank_flexcode >-----------------------
3924 --
3925 -- {Start Of Comments}
3926 --
3927 -- Description:
3928 -- 11-12-2006 This function gets the Bank Flex Structure Code
3929 -- for country with the Payment Type Country if it is Non Generic
3930 -- Payment Types and BG County for Generic Payment Types.
3931 --
3932 --
3933 -- Prerequisites:
3934 -- None.
3935 --
3936 -- Post Success:
3937 -- Always returns the required Bank Flex Structure Code.
3938 --
3939 -- Post Failure:
3940 --
3941 -- Access Status:
3942 -- Internal Development Use Only.
3943 --
3944 -- {End Of Comments}
3945 --
3946 function get_bank_flexcode
3947 (p_org_payment_method_id IN number,
3948 p_business_group_id IN number)return varchar2 IS
3949
3950 l_bank_country_code hr_organization_information.org_information9%TYPE;
3951
3952 cursor c_get_flexcode (
3953 cp_opm_id number
3954 ,cp_bgid number ) IS
3955 select flex.id_flex_structure_code
3956 from pay_legislation_rules leg
3957 , fnd_id_flex_structures flex
3958 , PAY_ORG_PAYMENT_METHODS_F opm
3959 , PAY_PAYMENT_TYPES PTS
3960 where opm.ORG_PAYMENT_METHOD_ID = cp_opm_id
3961 and opm.BUSINESS_GROUP_ID = cp_bgid
3962 and opm.PAYMENT_TYPE_ID = PTS.PAYMENT_TYPE_ID
3963 and leg.legislation_code = DECODE(PTS.territory_code, null, HR_API.RETURN_LEGISLATION_CODE(OPM.BUSINESS_GROUP_ID),PTS.territory_code)
3964 and leg.rule_type = 'E'
3965 and to_char(flex.id_flex_num) = leg.rule_mode
3966 and flex.id_flex_code = 'BANK';
3967
3968 l_bank_flex_structure_code fnd_id_flex_structures.id_flex_structure_code%TYPE;
3969
3970 begin
3971
3972 OPEN c_get_flexcode(p_org_payment_method_id,p_business_group_id);
3973 fetch c_get_flexcode into l_bank_flex_structure_code;
3974 close c_get_flexcode;
3975
3976 return l_bank_flex_structure_code;
3977
3978 end get_bank_flexcode;
3979
3980 -------------------------< get_org_method_name >-----------------------
3981 --
3982 -- {Start Of Comments}
3983 --
3984 -- Description:
3985 -- 11-12-2006 This function gets the ORG Payment Method Name
3986 --
3987 --
3988 -- Prerequisites:
3989 -- None.
3990 --
3991 -- Post Success:
3992 -- Always returns the required Bank Flex Structure Code.
3993 --
3994 -- Post Failure:
3995 --
3996 -- Access Status:
3997 -- Internal Development Use Only.
3998 --
3999 -- {End Of Comments}
4000 --
4001 function get_org_method_name
4002 (p_org_payment_method_id IN number,
4003 p_business_group_id IN number
4004 )return varchar2 is
4005
4006 cursor c_get_opm_name ( cp_opmid number,
4007 cp_bgid number) is
4008 select OPMTL.ORG_PAYMENT_METHOD_NAME
4009 from PAY_ORG_PAYMENT_METHODS_F_TL OPMTL,
4010 PAY_ORG_PAYMENT_METHODS_F OPM
4011 where OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID
4012 AND OPM.ORG_PAYMENT_METHOD_ID = cp_opmid
4013 AND BUSINESS_GROUP_ID + 0 = cp_bgid
4014 AND OPMTL.LANGUAGE = USERENV('LANG');
4015
4016 l_payment_method_name PAY_ORG_PAYMENT_METHODS_F_TL.ORG_PAYMENT_METHOD_NAME%TYPE;
4017 Begin
4018 l_payment_method_name := null;
4019
4020 open c_get_opm_name(p_org_payment_method_id,p_business_group_id);
4021 fetch c_get_opm_name into l_payment_method_name;
4022 close c_get_opm_name;
4023 return l_payment_method_name;
4024 end get_org_method_name;
4025
4026 function is_foreign_transaction(p_opm_id in number,
4027 p_effective_date DATE) return varchar is
4028 l_is_foreign_transaction PAY_ORG_PAYMENT_METHODS_F.PMETH_INFORMATION9%TYPE;
4029 begin
4030 select popm.PMETH_INFORMATION9
4031 INTO l_is_foreign_transaction
4032 from pay_org_payment_methods_f popm
4033 where popm.ORG_PAYMENT_METHOD_ID = p_opm_id
4034 and HR_API.RETURN_LEGISLATION_CODE(POPM.BUSINESS_GROUP_ID)='US'
4035 AND p_effective_date
4036 BETWEEN popm.effective_start_date AND popm.effective_end_date;
4037
4038 return nvl(l_is_foreign_transaction,'N');
4039 exception
4040 when no_data_found then
4041 return 'N';
4042 end is_foreign_transaction;
4043
4044 function get_payment_type_name(p_opm_id number,
4045 p_effective_date date) RETURN varchar2 is
4046
4047 l_payment_type_name PAY_PAYMENT_TYPES.PAYMENT_TYPE_NAME%TYPE;
4048
4049 begin
4050 SELECT PT.PAYMENT_TYPE_NAME
4051 INTO l_payment_type_name
4052 FROM PAY_PAYMENT_TYPES PT, PAY_ORG_PAYMENT_METHODS_F PM
4053 WHERE PM.ORG_PAYMENT_METHOD_ID = P_OPM_ID
4054 AND PM.PAYMENT_TYPE_ID = PT.PAYMENT_TYPE_ID
4055 AND P_EFFECTIVE_DATE BETWEEN PM.EFFECTIVE_START_DATE
4056 AND PM.EFFECTIVE_END_DATE;
4057 RETURN NVL(L_PAYMENT_TYPE_NAME,'');
4058 EXCEPTION
4059 WHEN NO_DATA_FOUND THEN
4060 RETURN '';
4061 end get_payment_type_name;
4062
4063 procedure store_session(p_effective_date DATE) IS
4064 --
4065 --local variable
4066 l_eff_date date;
4067 l_proc constant varchar2(100) := g_package || ' setEffectiveDate';
4068 BEGIN
4069 hr_utility.set_location('Entering: '|| l_proc,5);
4070 hr_utility.set_location('Effective Date => '||p_effective_date,6);
4071 hr_utility.set_location('Session Date => '||userenv('sessionid'),7);
4072 --
4073 l_eff_date := trunc(p_effective_date);
4074 begin
4075 dt_fndate.set_effective_date(l_eff_date);
4076 exception
4077 when DUP_VAL_ON_INDEX then
4078 hr_utility.set_location('change for DUP_VAL_ON_INDEX : ' || l_proc ,999);
4079 when others then
4080 hr_utility.set_location('change for others : ' || l_proc ,998);
4081 end;
4082 --
4083 --
4084 hr_utility.set_location('Leaving: '|| l_proc,10);
4085 EXCEPTION
4086 WHEN others THEN
4087 hr_utility.set_location('EXCEPTION: '|| l_proc,555);
4088 rollback;
4089 raise;
4090 END STORE_SESSION;
4091
4092 function check_future_payments(p_assignment_id number ,p_effective_date_str varchar2,p_run_type_id varchar2 ) return char is
4093 v_option_value varchar2(30);
4094 p_effective_date date;
4095 cursor csr_future_ppms
4096 (p_assignment_id in number
4097 ,p_effective_date in date
4098 ) is
4099 select null
4100 from pay_personal_payment_methods_f ppm
4101 where ppm.assignment_id = p_assignment_id
4102 and nvl(ppm.run_type_id, hr_api.g_number) = nvl(p_run_type_id, hr_api.g_number)
4103 and ppm.effective_start_date > p_effective_date;
4104 begin
4105 p_effective_date := to_date(p_effective_date_str,hr_transaction_ss.g_date_format);
4106 open csr_future_ppms
4107 (p_assignment_id
4108 ,p_effective_date
4109 );
4110 fetch csr_future_ppms into v_option_value;
4111 if csr_future_ppms%found then
4112 close csr_future_ppms;
4113 return 'Y';
4114 end if;
4115 close csr_future_ppms;
4116 return 'N';
4117 end check_future_payments;
4118 end pay_ppmv4_ss;