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