1 PACKAGE BODY AP_PAYMENT_UTIL_PKG AS
2 /* $Header: appayutb.pls 120.25.12010000.2 2008/08/08 03:26:31 sparames ship $ */
3
4 /*===========================================================================
5 | FUNCTION - get_le_name
6 | DESCRIPTION - Utility to get Legal Entity Name for transactions which store
7 | legal_entity_id
8 *==========================================================================*/
9 FUNCTION get_le_name( p_legal_entity_id IN NUMBER)
10
11 RETURN VARCHAR2 IS
12
13 l_le_name xle_entity_profiles.name%TYPE;
14
15 BEGIN
16
17 SELECT NAME
18 INTO l_le_name
19 FROM xle_entity_profiles
20 WHERE legal_entity_id = p_legal_entity_id;
21
22 RETURN l_le_name;
23
24 END;
25
26
27 /*===========================================================================
28 | FUNCTION - get_ou_name
29 | DESCRIPTION - Utility to get the Operating Unit name for a given org_id
30 | This API should be replaced by
31 | fnd_access_control_util.get_org_name(AI.org_id)
32 *==========================================================================*/
33 FUNCTION Get_OU_NAME( p_org_id IN NUMBER)
34 RETURN VARCHAR2 IS
35
36 l_ou_name hr_all_organization_units.name%TYPE;
37
38 BEGIN
39 SELECT name
40 INTO l_ou_name
41 FROM hr_all_organization_units
42 WHERE organization_id = p_org_id;
43
44 RETURN l_ou_name;
45
46 END;
47
48 /*===========================================================================
49 | FUNCTION - Get_iby_payer_defaults
50 | DESCRIPTION - Defaults the following IBY defaults(org_id is NULL)
51 | 1. Document Rejection Level
52 | 2. Payment Rejection Level
53 | 3. Whether to Stop Procees for Review After Proposed
54 | payments creation
55 *==========================================================================*/
56 PROCEDURE Get_iby_payer_defaults(
57 p_doc_rejection_level_code OUT NOCOPY VARCHAR2,
58 p_payment_rejection_level_code OUT NOCOPY VARCHAR2,
59 p_payments_review_settings OUT NOCOPY VARCHAR2)
60 IS
61
62 BEGIN
63
64 SELECT document_rejection_level_code,
65 payment_rejection_level_code,
66 require_prop_pmts_review_flag
67 INTO p_doc_rejection_level_code,
68 p_payment_rejection_level_code,
69 p_payments_review_settings
70 FROM iby_internal_payers_all
71 WHERE org_id IS NULL;
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND
75 THEN NULL;
76
77 END ;
78
79 /*===========================================================================
80 | FUNCTION - get_pi_count
81 | DESCRIPTION - This is the number of payment instructions created for all
82 | payments belonging to the PPR. This appears only after at
83 | least one exists.
84 |
85 *==========================================================================*/
86 FUNCTION get_pi_count(p_psr_id IN NUMBER)
87 RETURN NUMBER IS
88
89 l_ps_count NUMBER;
90
91 BEGIN
92
93 IF ( p_psr_id IS NOT NULL ) THEN
94
95 SELECT COUNT(DISTINCT payment_instruction_id)
96 INTO l_ps_count
97 FROM iby_fd_paymentS_v
98 WHERE payment_instruction_id is NOT NULL
99 AND payment_service_request_id = p_psr_id;
100
101 END IF;
102 IF l_ps_count = 0 THEN
103 l_ps_count := NULL;
104 END IF;
105
106 RETURN l_ps_count;
107
108 END;
109
110 /*===========================================================================
111 | FUNCTION - get_selected_ps_count
112 | DESCRIPTION - Gets the count of selected scheduled payments for the
113 | Payment Batch(Pay Run)
114 |
115 *==========================================================================*/
116 FUNCTION get_selected_ps_count(p_checkrun_id IN NUMBER,
117 p_psr_id IN NUMBER)
118 RETURN NUMBER IS
119
120 l_count1 NUMBER;
121 l_count2 NUMBER;
122 l_selected_ps_count NUMBER;
123
124 BEGIN
125 l_selected_ps_count := 0;
126
127 SELECT count(*)
128 INTO l_count1
129 FROM ap_selected_invoices_All
130 WHERE checkrun_id = p_checkrun_id
131 AND original_invoice_id is NULL
132 AND original_payment_num is NULL
133 AND ok_to_pay_flag = 'Y';
134 --
135 SELECT count(*)
136 INTO l_count2
137 FROM ap_invoice_payments_all aip,
138 ap_checks_all ac
139 WHERE aip.check_id = ac.check_id
140 AND ac.checkrun_id = p_checkrun_id;
141
142 --
143 l_selected_ps_count := l_count1 + l_count2;
144 --
145
146 RETURN l_selected_ps_count;
147
148 END;
149
150
151 /*===========================================================================
152 | FUNCTION - get_unsel_ps_count
153 | DESCRIPTION - Gets the count of un-selected scheduled payments for the
154 | Payment Batch(Pay Run)
155 |
156 *==========================================================================*/
157 FUNCTION Get_unsel_ps_Count(p_checkrun_id IN NUMBER)
158 RETURN NUMBER IS
159
160 l_unsel_ps_count NUMBER;
161
162 BEGIN
163
164 SELECT count(*)
165 INTO l_unsel_ps_count
166 FROM ap_unselected_invoices_All
167 WHERE checkrun_id = p_checkrun_id;
168 RETURN l_unsel_ps_count;
169
170 END;
171
172
173 /*=============================================================================
174 | FUNCTION - Get_Rejected_ps_Count
175 | DESCRIPTION - This is the total count of scheduled payments that Oracle
176 | Payments(IBY) has rejected.
177 |
178 *============================================================================*/
179 FUNCTION Get_Rejected_ps_Count(p_psr_id IN NUMBER)
180 RETURN NUMBER IS
181
182 l_rejected_ps_count NUMBER;
183
184 BEGIN
185
186 IF ( p_psr_id IS NOT NULL ) THEN
187
188 SELECT count(*)
189 INTO l_rejected_ps_count
190 FROM IBY_DOCS_PAYABLE_ALL
191 WHERE payment_service_request_id = p_psr_id
192 AND rejected_docs_group_id is NOT NULL;
193
194 END IF;
195
196 IF l_rejected_ps_count = 0 THEN
197 l_rejected_ps_count := NULL;
198 END IF;
199
200 RETURN l_rejected_ps_count;
201
202 END;
203
204 /*===========================================================================
205 | FUNCTION - get_unsel_reason_count
206 | DESCRIPTION - This is the count of payment schedules that met the criteria
207 | but were not selected for a particular dont_pay_reason_code
208 |
209 *==========================================================================*/
210 FUNCTION get_unsel_reason_count(
211 p_checkrun_id IN NUMBER,
212 p_dont_pay_code IN VARCHAR2)
213 RETURN NUMBER IS
214
215 l_count NUMBER;
216 BEGIN
217 IF p_dont_pay_code = 'OTHERS' THEN
218 SELECT count(*)
219 INTO l_count
220 FROM ap_unselected_invoices_all
221 WHERE checkrun_id = p_checkrun_id
222 AND dont_pay_reason_code NOT IN ('NEEDS_INVOICE_VALIDATION',
223 'FAILED_INVOICE_VALIDATION',
224 'ZERO INVOICE',
225 'NEEDS_APPROVAL',
226 'APPROVER_REJECTED',
227 'USER REMOVED',
228 'SCHEDULED_PAYMENT_HOLD',
229 'SUPPLIER_SITE_HOLD',
230 'DISCOUNT_RATE_TOO_LOW');
231 ELSE
232
233 SELECT count(*)
234 INTO l_count
235 FROM ap_unselected_invoices_all
236 WHERE checkrun_id = p_checkrun_id
237 AND dont_pay_reason_code = p_dont_pay_code;
238
239 END IF;
240
241 RETURN l_count;
242
243 END;
244
245 /*===========================================================================
246 | FUNCTION - get_ps_ur_count
247 | DESCRIPTION - Gets the count of scheduled payments for the missing User
248 | exchange rates for a combination of payment currency
249 | and the ledger currency(Invoices associated with one OU
250 | have one ledger currency associated with them).
251 | Usage: PsExchangeRatesPG.
252 |
253 *==========================================================================*/
254 FUNCTION get_ps_ur_count(
255 p_checkrun_id IN NUMBER,
256 p_ledger_currency_code IN VARCHAR2,
257 p_payment_currency_code IN VARCHAR2)
258 RETURN NUMBER IS
259
260 l_ps_ur_count NUMBER;
261
262
263 BEGIN
264
265 SELECT count(*)
266 INTO l_ps_ur_count
267 FROM ap_selected_invoices_All asi,
268 ap_system_parameters_all asp
269 WHERE asi.org_id = asp.org_id
270 AND asi.checkrun_id = p_checkrun_id
271 AND asp.base_currency_code = p_ledger_currency_code
272 AND asi.payment_currency_code = p_payment_currency_code
273 AND asi.payment_exchange_rate_type = 'User'
274 AND asi.ok_to_pay_flag = 'Y';
275
276
277 RETURN l_ps_ur_count;
278
279 END;
280
281 /*=============================================================================
282 | FUNCTION - Get_missing_rates_ps_count
283 | DESCRIPTION - This is the total count of selected scheduled payments that
284 | have missing exchange rates
285 |
286 *============================================================================*/
287 FUNCTION Get_missing_rates_ps_Count(p_checkrun_id IN NUMBER)
288 RETURN NUMBER IS
289
290 l_missing_rates_ps_count NUMBER := 0;
291
292 BEGIN
293
294 SELECT count(*)
295 INTO l_missing_rates_ps_count
296 FROM ap_selected_invoices_all asi,
297 ap_system_parameters_all asp
298 WHERE asi.org_id = asp.org_id
299 AND asi.checkrun_id = p_checkrun_id
300 AND asp.base_currency_code <> asi.payment_currency_code
301 AND asi.payment_exchange_rate_type = 'User'
302 AND asi.payment_exchange_rate is NULL
303 AND asi.ok_to_pay_flag = 'Y'
304 AND EXISTS (SELECT 'No User Rate'
305 FROM ap_user_exchange_rates aur
306 WHERE aur.ledger_currency_code = asp.base_currency_code
307 AND aur.payment_currency_code = asi.payment_currency_code
308 AND aur.exchange_rate is NULL);
309
310 RETURN l_missing_rates_ps_count;
311
312 END;
313
314
315 /*=============================================================================
316 | FUNCTION - Get_interest_due
317 | DESCRIPTION - This is the Total Interest due for the Selected Payment
318 | Schedule wrt the Payment Date on the Pay Run. The Interest
319 | Due is calculated during the AutoSelect/Recalculation.
320 |
321 *============================================================================*/
322 FUNCTION Get_Interest_due(
323 p_checkrun_id IN NUMBER,
324 p_invoice_id IN NUMBER,
325 p_payment_num IN NUMBER
326 )
327 RETURN NUMBER IS
328
329 l_interest_due NUMBER;
330
331 BEGIN
332
333 BEGIN
334 SELECT payment_amount
335 INTO l_interest_due
336 FROM ap_selected_invoices_All
337 WHERE checkrun_id = p_checkrun_id
338 AND original_invoice_id = p_invoice_id
339 AND original_payment_num = p_payment_num;
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 l_interest_due := NULL;
343 END;
344
345 RETURN l_interest_due;
346 END;
347
348
349 /*===========================================================================
350 | FUNCTION - Get_interest_paid
351 | DESCRIPTION - This is the total interest that has been paid for the Invoice
352 | Used in PsDetailsPG
353 |
354 *==========================================================================*/
355 FUNCTION get_interest_paid(p_invoice_id IN NUMBER)
356 RETURN NUMBER IS
357
358 l_interest_paid NUMBER := 0;
359
360 BEGIN
361
362 BEGIN
363
364 SELECT SUM(NVL(amount_paid,0))
365 INTO l_interest_paid
366 FROM ap_invoices_all ai
367 WHERE invoice_id IN (
368 SELECT DISTINCT related_invoice_id
369 FROM ap_invoice_relationships air
370 WHERE original_invoice_id = p_invoice_id );
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 NULL;
374 END;
375
376 RETURN l_interest_paid;
377 END;
378
379 /*===========================================================================
380 | FUNCTION - Get_interest_inv
381 | DESCRIPTION - This is the Interest Invoice planned for the Selected
382 | Scheduled Payment.
383 | Used in PsDetailsPG
384 |
385 *==========================================================================*/
386 FUNCTION Get_Interest_inv(
387 p_checkrun_id IN NUMBER,
388 p_invoice_id IN NUMBER,
389 p_payment_num IN NUMBER
390 )
391 RETURN VARCHAR2 IS
392
393 l_interest_inv AP_INVOICES_ALL.invoice_num%TYPE;
394
395
396 BEGIN
397
398 BEGIN
399 SELECT invoice_num
400 INTO l_interest_inv
401 FROM ap_selected_invoices_All
402 WHERE checkrun_id = p_checkrun_id
403 AND original_invoice_id = p_invoice_id
404 AND original_payment_num = p_payment_num;
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 NULL;
408 END;
409
410 RETURN l_interest_inv;
411 END;
412
413
414 /*=============================================================================
415 | FUNCTION - Get_gain_loss_amt
416 | DESCRIPTION - This function would calculate the estimated gain loss for the
417 | Payment Schedule.
418 | PsDetailsPG : Nice to have
419 |
420 *============================================================================*/
421 FUNCTION Get_gain_loss_amount
422 RETURN NUMBER IS
423 l_gain_loss_amt NUMBER;
424
425 BEGIN
426
427 RETURN l_gain_loss_amt;
428
429 END;
430
431
432 /*===========================================================================
433 | FUNCTION - Get_interest_rate
434 | DESCRIPTION - This is the interest rate use to create planned interest
435 | invoices. The rate is calculated wrt the payment date
436 |
437 *==========================================================================*/
438 FUNCTION get_interest_rate(p_check_date IN DATE)
439 RETURN NUMBER IS
440
441 l_rate NUMBER;
442
443 BEGIN
444
445 BEGIN
446 SELECT annual_interest_rate
447 INTO l_rate
451 WHEN NO_DATA_FOUND THEN
448 FROM ap_interest_periods
449 WHERE p_check_date BETWEEN start_date and end_date;
450 EXCEPTION
452 l_rate := NULL;
453 END;
454
455 RETURN l_rate;
456 END;
457
458
459 /*=============================================================================
460 | FUNCTION - get_payment_status_flag
461 | DESCRIPTION - This function returns the status w.r.t payments that have been
462 | created in IBY for this Payment Process Request(Checkrun_id)
463 |
464 |
465 | BUG 6476559 - Removed the entire code in this function and calling the IBY
466 | function to return the status flag.
467 |
468 *============================================================================*/
469 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER)
470 RETURN VARCHAR2 IS
471
472 l_payment_status_flag VARCHAR2(1);
473 l_total_pmt_count NUMBER;
474 l_pmt_complete_count NUMBER;
475
476
477 BEGIN
478
479 l_payment_status_flag := IBY_UTILITY_PVT.get_payment_status_flag(p_psr_id);
480
481 RETURN l_payment_status_flag;
482
483 END get_payment_status_flag;
484
485
486
487 /*=============================================================================
488 | FUNCTION - get_psr_status
489 | DESCRIPTION - This function returns the status w.r.t payments that have been
490 | created in IBY for this Payment Process Request(Checkrun_id)
491 |
492 |
493 | BUG 6476559 - Removed the entire code in this function and calling the IBY
494 | function to return the status.
495 |
496 *============================================================================*/
497 FUNCTION get_psr_status(p_psr_id IN NUMBER,
498 p_psr_status IN VARCHAR2)
499 RETURN VARCHAR2 IS
500
501 l_psr_status VARCHAR2(30);
502 l_total_pmt_count NUMBER;
503 l_instr_count NUMBER;
504 l_pmt_terminate_count NUMBER;
505
506
507 BEGIN
508
509 l_psr_status := IBY_UTILITY_PVT.get_psr_status(p_psr_id
510 ,p_psr_status);
511
512 RETURN l_psr_status;
513
514 END get_psr_status;
515
516 /*===========================================================================
517 | FUNCTION - get_destination_function
518 | DESCRIPTION - The destination function is attached to the Payment Process
519 | request Link on the PsrSearch Page. The Payment Process
520 | Name Link navigates the user to Selected Payment Schedules
521 | Page if the request is not yet submitted to IBY and status
522 | is 'REVIEW' or 'MISSING RATES'. If the request has been
523 | submitted to IBY, the link navigates the user to
524 | IBY_FD_REQUEST_DETAIL Page unless the status is INSERTED' or
525 | 'SUBMITTED'. For all other statuses the Link is disabled
526 |
527 |
528 *==========================================================================*/
529 FUNCTION get_destination_function(p_checkrun_id IN NUMBER,
530 p_status_code IN VARCHAR2)
531 RETURN VARCHAR2 IS
532
533 l_function fnd_form_functions.FUNCTION_NAME%TYPE;
534
535 l_count1 NUMBER;
536 l_count2 NUMBER;
537 BEGIN
538
539 IF p_status_code IN ('REVIEW', 'MISSING RATES') THEN
540 l_function := 'AP_PAY_PSR_SEL_DOCS';
541
542 SELECT count(*)
543 INTO l_count1
544 FROM ap_selected_invoices_All
545 WHERE checkrun_id = p_checkrun_id;
546
547 SELECT count(*)
548 INTO l_count2
549 FROM ap_selected_invoices
550 WHERE checkrun_id = p_checkrun_id;
551
552 -- current user doesn't has access to all the selected docs
553 IF (l_count1 <> l_count2) THEN
554 l_function := NULL;
555 END IF;
556
557 ELSIF p_status_code IN (
558 'INFORMATION_REQUIRED', 'ASSIGNMENT_COMPLETE',
559 'DOCUMENTS_VALIDATED', 'VALIDATION_FAILED',
560 'RETRY_DOCUMENT_VALIDATION', 'PENDING_REVIEW_DOC_VAL_ERRORS',
561 'PAYMENTS_CREATED', 'PENDING_REVIEW',
562 'FAILED_PAYMENT_VALIDATION', 'PENDING_REVIEW_PMT_VAL_ERRORS',
563 'RETRY_PAYMENT_CREATION', 'RETRY_PAYMENT_CREATION',
564 'TERMINATED') THEN
565 l_function := 'IBY_FD_REQUEST_DETAIL';
566 ELSE
567 --SQLAP --'UNSTARTED', 'SELECTING', 'CANCELLED NO PAYMENTS' , 'SELECTED'
568 --IBY -- 'INSERTED', 'SUBMITTED'
569 l_function := null;
570
571 END IF;
572
573 RETURN l_function;
574
575 END;
576
577
578 /*=============================================================================
579 | FUNCTION - Action_Switcher
580 | DESCRIPTION - The function is attached to the ActionSwitcher
581 | TakeActionEnabled -- takeaction_enabled.gif(ActionFunction)
582 | TakeActionDisabled -- No Icon (No Action)
583 | WarningIndEvenActive -- warningind_even_active.gif (This case can happens
584 | when say a Concurrent Program is Terminated).
585 | ActionInProgress -- inprogressind_status.gif (No Action)
589 p_psr_id IN NUMBER,
586 |
587 *============================================================================*/
588 FUNCTION Action_Switcher(p_checkrun_id IN NUMBER,
590 p_status_code IN VARCHAR2,
591 p_checkrun_request_id IN NUMBER,
592 p_psr_request_id IN NUMBER
593 )
594 RETURN VARCHAR2 IS
595
596 l_action VARCHAR2(100);
597 l_count1 NUMBER;
598 l_count2 NUMBER;
599 l_request_status VARCHAR2(100);
600
601 BEGIN
602 --
603
604 IF p_psr_id is NULL THEN
605
606 IF p_status_code IN ( 'REVIEW', 'MISSING RATES') THEN
607 l_action := 'TakeActionEnabled';
608 ELSE
609 l_action := 'TakeActionDisabled';
610 END IF;
611
612 ELSE --Request is submitted to IBY
613
614 IF p_status_code IN ( 'INFORMATION_REQUIRED',
615 'PENDING_REVIEW_DOC_VAL_ERRORS',
616 'PENDING_REVIEW_PMT_VAL_ERRORS',
617 'PENDING_REVIEW') THEN
618
619 l_action := 'TakeActionEnabled';
620
621 ELSE
622
623 l_action := 'TakeActionDisabled';
624 END IF;
625 --
626 --If the Conc Request is complete(Normal) IBY NULL's out the Request_id column
627 IF l_action = 'TakeActionEnabled' AND p_psr_request_id IS NOT NULL THEN
628
629 l_request_status :=
630 iby_disburse_ui_api_pub_pkg.get_conc_request_status(
631 p_psr_request_id);
632 --
633 IF (l_request_status = 'SUCCESS') THEN
634 l_action := 'TakeActionEnabled';
635 ELSIF (l_request_status = 'ERROR') THEN
636 l_action := 'WarningIndEvenActive';
637 ELSE
638 l_action := 'ActionInProgress';
639 END IF;
640 END IF;
641 END IF;
642
643 --Disable Action if User doesn't has MOAC access
644 IF l_action IN ('TakeActionEnabled') THEN
645 --
646 SELECT count(*)
647 INTO l_count1
648 FROM ap_selected_invoices_All
649 WHERE checkrun_id = p_checkrun_id;
650
651 SELECT count(*)
652 INTO l_count2
653 FROM ap_selected_invoices
654 WHERE checkrun_id = p_checkrun_id;
655
656 -- current user doesn't has access to all the selected docs
657 IF (l_count1 <> l_count2) THEN
658 l_action := 'TakeActionDisabled';
659 END IF;
660 --
661 END IF;
662 --
663
664 RETURN l_action;
665 --
666 END;
667
668 /*=============================================================================
669 | FUNCTION - Get_Action_Function
670 | DESCRIPTION - The Action function is attached to the Start Action Icon
671 | The Icon navigates the user to Selected Payment Schedules
672 | Page if the request is not yet submitted to IBY and status
673 | is 'REVIEW' or 'MISSING RATES'. If the request has been
674 | submitted to IBY, the link navigates the user to the
675 | following pages
676 |
677 | IBY Statuses Destinations
678 | ---------------------------- -----------------------
679 | INFORMATION_REQUIRED IBY_FD_ASSIGN_COMPLETE
680 | PENDING_REVIEW_DOC_VAL_ERRORS IBY_FD_DOCS_VALIDATE
681 | PENDING_REVIEW_PMT_VAL_ERRORS IBY_FD_PAYMENT_VALIDATE
682 | PENDING_REVIEW IBY_FD_PAYMENT_REVIEW
683 |
684 *============================================================================*/
685 FUNCTION get_action_function(p_checkrun_id IN NUMBER,
686 p_status_code IN VARCHAR2)
687 RETURN VARCHAR2 IS
688
689 l_function fnd_form_functions.function_name%TYPE;
690 l_count1 NUMBER;
691 l_count2 NUMBER;
692
693 BEGIN
694
695 IF p_status_code IN ('REVIEW', 'MISSING RATES') THEN
696 l_function := 'AP_PAY_PSR_SEL_DOCS';
697
698 ELSIF p_status_code IN ('INFORMATION_REQUIRED') THEN
699 l_function := 'IBY_FD_ASSIGN_COMPLETE'; -- Complete Document
700 -- Assignments
701 ELSIF p_status_code IN ('PENDING_REVIEW_DOC_VAL_ERRORS') THEN
702 l_function := 'IBY_FD_DOCS_VALIDATE'; -- Resolve Document
703 -- Validation Errors
704 ELSIF p_status_code IN ('PENDING_REVIEW_PMT_VAL_ERRORS') THEN
705 l_function := 'IBY_FD_PAYMENT_VALIDATE'; -- Resolve Payment
706 -- Validation Errors
707 ELSIF p_status_code IN ('PENDING_REVIEW') THEN
708 l_function := 'IBY_FD_PAYMENT_REVIEW'; -- Review proposed
709 -- payment
710 ELSE
711 l_function := null;
712 END IF;
713
714 IF l_function is NOT NULL THEN
715 SELECT count(*)
716 INTO l_count1
717 FROM ap_selected_invoices_All
718 WHERE checkrun_id = p_checkrun_id;
719
720
721 SELECT count(*)
725
722 INTO l_count2
723 FROM ap_selected_invoices
724 WHERE checkrun_id = p_checkrun_id;
726 -- current user doesn't has access to all the selected docs
727 IF (l_count1 <> l_count2) THEN
728 l_function := NULL;
729 END IF;
730
731 END IF;
732
733
734 RETURN l_function;
735
736 END;
737
738
739 /*===========================================================================
740 | FUNCTION - get_psr_snapshot_count
741 | DESCRIPTION - This function is designed for the Payables Payment Manager
742 | Home Page . The function returns the total count of Payment
743 | Process Requests with a particular Status or a combination
744 | of Payment Process Request Statuses that map to a particular
745 | snapshot code
746 |
747 | BUG 6476559 - Removed the entire code in this function and calling the IBY
748 | function to return the count.
749 |
750 |
751 *==========================================================================*/
752 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
753 RETURN NUMBER IS
754
755 l_ret_val NUMBER;
756
757 BEGIN
758
759 l_ret_val := IBY_UTILITY_PVT.get_psr_snapshot_count(p_snapshot_code);
760
761 RETURN l_ret_val;
762
763 END;
764
765 /*=============================================================================
766 | FUNCTION - Update Payment Schedules
767 | DESCRIPTION - This method locks the newly added Payment Schedules
768 |
769 | Bug 5646905 -- Modified the update statement from the = clause to the IN
770 | clause so the plan can execute the sub-query first.
771 |
772 |
773 *============================================================================*/
774 FUNCTION Update_payment_schedules(
775 p_checkrun_id IN NUMBER)
776 RETURN VARCHAR2 IS
777
778 BEGIN
779 UPDATE ap_payment_schedules_all APS
780 SET APS.checkrun_id = p_checkrun_id
781 WHERE (APS.invoice_id, APS.payment_num ) IN
782 (SELECT ASI.invoice_id, ASI.payment_num
783 FROM ap_selected_invoices_all ASI
784 WHERE APS.invoice_id = ASI.invoice_id
785 AND APS.payment_num = ASI.payment_num
786 AND ASI.checkrun_id = p_checkrun_id)
787 AND APS.checkrun_id is NULL;
788
789 RETURN 'S';
790 END;
791
792 /*=============================================================================
793 | FUNCTION - Validates Psr
794 | DESCRIPTION - This method is a wrapper on IBY's API
795 | IBY_FD_USER_API_PUB.Validate_Method_and_Profile(ibyfduas.pls)
796 *============================================================================*/
797 FUNCTION Validate_Psr(
798 p_checkrun_id IN NUMBER)
799 RETURN VARCHAR2 IS
800
801 l_ret_status VARCHAR2(1) := 'E';
802 l_msg_count NUMBER;
803 l_msg_data VARCHAR2(2000);
804
805 l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
806 l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
807 l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
808 l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
809
810
811 l_le_arr IBY_FD_USER_API_PUB.Legal_Entity_Tab_Type;
812 l_int_bank_account_id NUMBER;
813 l_int_bank_account_arr IBY_FD_USER_API_PUB.Int_Bank_Acc_Tab_Type;
814 l_curr_arr IBY_FD_USER_API_PUB.Currency_Tab_type;
815 l_ou_arr IBY_FD_USER_API_PUB.Org_Tab_Type;
816
817 CURSOR curr_arr_cursor IS
818 SELECT currency_code
819 FROM ap_currency_group
820 WHERE checkrun_id = p_checkrun_id;
821
822 CURSOR le_arr_cursor IS
823 SELECT legal_entity_id
824 FROM ap_le_group
825 WHERE checkrun_id = p_checkrun_id;
826
827 CURSOR ou_arr_cursor IS
828 SELECT org_id,
829 'OPERATING_UNIT'
830 FROM ap_ou_group
831 WHERE checkrun_id = p_checkrun_id;
832
833 BEGIN
834 --
835 fnd_msg_pub.Initialize;
836 --
837 SELECT payment_method_code,
838 payment_profile_id,
839 bank_account_id,
840 payment_document_id,
841 create_instrs_flag
842 INTO l_payment_method_code,
843 l_payment_profile_id,
844 l_int_bank_account_id,
845 l_payment_document_id,
846 l_create_instrs_flag
847 FROM ap_inv_selection_criteria_all
848 WHERE checkrun_id = p_checkrun_id;
849 --
850 OPEN curr_arr_cursor;
851 FETCH curr_arr_cursor
852 BULK COLLECT INTO l_curr_arr;
853 CLOSE curr_arr_cursor;
854 --
855 OPEN le_arr_cursor;
856 FETCH le_arr_cursor
857 BULK COLLECT INTO l_le_arr;
858 CLOSE le_arr_cursor;
859 --
860 OPEN ou_arr_cursor;
861 FETCH ou_arr_cursor
862 BULK COLLECT INTO l_ou_arr;
863 CLOSE ou_arr_cursor;
864 --
865 IF l_int_bank_account_id IS NOT NULL THEN
866 l_int_bank_account_arr(1) := l_int_bank_Account_id;
870 p_api_version => 1.0,
867 END IF;
868 --
869 IBY_FD_USER_API_PUB.Validate_Method_and_Profile (
871 p_init_msg_list => 'F',
872 p_payment_method_code => l_payment_method_code,
873 p_ppp_id => l_payment_profile_id,
874 p_payment_document_id => l_payment_document_id,
875 p_crt_instr_flag => l_create_instrs_flag,
876 p_int_bank_acc_arr => l_int_bank_account_arr,
877 p_le_arr => l_le_arr,
878 p_org_arr => l_ou_arr,
879 p_curr_arr => l_curr_arr,
880 x_return_status => l_ret_status,
881 x_msg_count => l_msg_count,
882 x_msg_data => l_msg_data);
883 --
884 l_curr_arr.DELETE;
885 l_le_arr.DELETE;
886 l_ou_arr.DELETE;
887 --
888 RETURN l_ret_status;
889
890 END;
891
892 /*=============================================================================
893 | FUNCTION - Validates Payment Template
894 | DESCRIPTION - This method is a wrapper on IBY's API
895 | IBY_FD_USER_API_PUB.Validate_Method_and_Profile(ibyfduas.pls)
896 |
897 *============================================================================*/
898 FUNCTION Validate_payment_template(
899 p_template_id IN NUMBER)
900 RETURN VARCHAR2 IS
901
902 l_ret_status VARCHAR2(1) := 'E';
903 l_msg_count NUMBER;
904 l_msg_data VARCHAR2(2000);
905
906 l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
907 l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
908 l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
909 l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
910
911 l_le_arr IBY_FD_USER_API_PUB.Legal_Entity_Tab_Type;
912 l_int_bank_account_id NUMBER;
913 l_int_bank_account_arr IBY_FD_USER_API_PUB.Int_Bank_Acc_Tab_Type;
914 l_curr_arr IBY_FD_USER_API_PUB.Currency_Tab_type;
915 l_ou_arr IBY_FD_USER_API_PUB.Org_Tab_Type;
916
917 CURSOR curr_arr_cursor IS
918 SELECT currency_code
919 FROM ap_currency_group
920 WHERE template_id = p_template_id;
921
922 CURSOR le_arr_cursor IS
923 SELECT legal_entity_id
924 FROM ap_le_group
925 WHERE template_id = p_template_id;
926
927 CURSOR ou_arr_cursor IS
928 SELECT org_id,
929 'OPERATING_UNIT'
930 FROM ap_ou_group
931 WHERE template_id = p_template_id;
932
933 BEGIN
934 --
935 fnd_msg_pub.Initialize;
936 --
937 SELECT payment_method_code,
938 payment_profile_id,
939 bank_account_id,
940 payment_document_id,
941 create_instrs_flag
942 INTO l_payment_method_code,
943 l_payment_profile_id,
944 l_int_bank_account_id,
945 l_payment_document_id,
946 l_create_instrs_flag
947 FROM ap_payment_templates
948 WHERE template_id = p_template_id;
949 --
950 OPEN curr_arr_cursor;
951 FETCH curr_arr_cursor
952 BULK COLLECT INTO l_curr_arr;
953 CLOSE curr_arr_cursor;
954 --
955 OPEN le_arr_cursor;
956 FETCH le_arr_cursor
957 BULK COLLECT INTO l_le_arr;
958 CLOSE le_arr_cursor;
959 --
960 OPEN ou_arr_cursor;
961 FETCH ou_arr_cursor
962 BULK COLLECT INTO l_ou_arr;
963 CLOSE ou_arr_cursor;
964 --
965 IF l_int_bank_account_id IS NOT NULL THEN
966 l_int_bank_account_arr(1) := l_int_bank_Account_id;
967 END IF;
968 --
969 IBY_FD_USER_API_PUB.Validate_Method_and_Profile (
970 p_api_version => 1.0,
971 p_init_msg_list => 'F',
972 p_payment_method_code => l_payment_method_code,
973 p_ppp_id => l_payment_profile_id,
974 p_payment_document_id => l_payment_document_id,
975 p_crt_instr_flag => l_create_instrs_flag,
976 p_int_bank_acc_arr => l_int_bank_account_arr,
977 p_le_arr => l_le_arr,
978 p_org_arr => l_ou_arr,
979 p_curr_arr => l_curr_arr,
980 x_return_status => l_ret_status,
981 x_msg_count => l_msg_count,
982 x_msg_data => l_msg_data);
983
984 --
985 l_curr_arr.DELETE;
986 l_le_arr.DELETE;
987 l_ou_arr.DELETE;
988 --
989 RETURN l_ret_status;
990 --
991 END;
992
993 /*=============================================================================
994 | FUNCTION - Get_request_status
995 | DESCRIPTION - This function returns Y is a batch can be cancelled and N
996 | if the PPR is not allowed to be cancelled.
997 | This method is a wrapper on FND API API
998 | FND_CONCURRENT.Get_Request_status
999 |
1000 *============================================================================*/
1001 FUNCTION Get_request_status(p_checkrun_id IN NUMBER,
1002 p_psr_id IN NUMBER)
1003 RETURN VARCHAR2 IS
1004
1005 l_request_id NUMBER;
1006 call_status BOOLEAN;
1007 rphase VARCHAR2(80);
1008 rstatus VARCHAR2(80);
1009 dphase VARCHAR2(30);
1010 dstatus VARCHAR2(30);
1011 message VARCHAR2(240);
1012 l_status VARCHAR2(30);
1013 l_payment_status VARCHAR2(1);
1014 l_psr_status VARCHAR2(30);
1015 l_pmt_complete_count NUMBER;
1016
1017
1018 BEGIN
1019
1020 IF p_psr_id is NULL THEN
1021 --
1022 SELECT request_id, status
1023 INTO l_request_id, l_status
1024 FROM ap_inv_selection_criteria_all
1025 WHERE checkrun_id = p_checkrun_id;
1026
1027 -- 11i Payment batches have request_id as NULL, disable the cancel icon
1028 IF l_request_id is NULL THEN
1029 RETURN 'N';
1030 END IF;
1031
1032 --R12 baches which are already cancelled.
1033 IF l_status IN ( 'CANCELED' , 'CANCELLED NO PAYMENTS') THEN
1034 RETURN 'N';
1035 END IF;
1036 --
1037 ELSE --IBY
1038 --
1039 SELECT request_id, payment_service_request_status
1040 INTO l_request_id, l_psr_status
1041 FROM iby_pay_service_requests
1042 WHERE payment_service_request_id = p_psr_id;
1043
1044 IF l_psr_status = 'TERMINATED' THEN
1045 RETURN 'N';
1046 END IF;
1047 --
1048 SELECT COUNT(*)
1049 INTO l_pmt_complete_count
1050 FROM iby_payments_all
1051 WHERE payment_service_request_id = p_psr_id
1052 AND payment_instruction_id is NOT NULL
1053 AND ROWNUM =1;
1054 --
1055 --If Instructions exist, we should not allow Cancellation of the Pay Run.
1056 IF l_pmt_complete_count > 0 THEN
1057 RETURN 'N';
1058 END IF;
1059 --
1060 END IF; --psr_id is NOT NULL
1061 --
1062 IF l_request_id is NOT NULL THEN
1063 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(
1064 l_request_id,
1065 '',
1066 '',
1067 rphase,
1068 rstatus,
1069 dphase,
1070 dstatus,
1071 message);
1072 --
1073 IF ((dphase = 'COMPLETE') OR (dphase is NULL)) THEN
1074 RETURN 'Y';
1075 ELSE
1076 RETURN 'N';
1077 END IF;
1078 ELSE --request_id is NULL
1079 --
1080 RETURN 'Y';
1081 --
1082 END IF;
1083 --
1084 END;
1085
1086
1087
1088 /*=============================================================================
1089 | FUNCTION - Is Federal Installed
1090 | DESCRIPTION - This method is a wrapper on FV_INSTALL.Enabled(org_id)
1091 | API
1092 |
1093 *============================================================================*/
1094 FUNCTION Is_Federal_Installed(p_org_id IN NUMBER)
1095 RETURN VARCHAR2 IS
1096 BEGIN
1097
1098 IF FV_INSTALL.Enabled(p_org_id) THEN
1099 RETURN 'Y';
1100 ELSE
1101 RETURN 'N';
1102 END IF;
1103
1104 END Is_Federal_Installed;
1105
1106
1107 END AP_PAYMENT_UTIL_PKG;
[Home] [Help]