1 PACKAGE BODY AP_PAYMENT_UTIL_PKG AS
2 /* $Header: appayutb.pls 120.38 2011/03/10 21:40:27 bgoyal ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_PMT_CALLOUT_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'AP.PLSQL.AP_PAYMENT_UTIL_PKG.';
21 /*===========================================================================
22 | FUNCTION - get_le_name
23 | DESCRIPTION - Utility to get Legal Entity Name for transactions which store
24 | legal_entity_id
25 *==========================================================================*/
26 FUNCTION get_le_name( p_legal_entity_id IN NUMBER)
27
28 RETURN VARCHAR2 IS
29
30 l_le_name xle_entity_profiles.name%TYPE;
31
32 BEGIN
33
34 SELECT NAME
35 INTO l_le_name
36 FROM xle_entity_profiles
37 WHERE legal_entity_id = p_legal_entity_id;
38
39 RETURN l_le_name;
40
41 END;
42
43
44 /*===========================================================================
45 | FUNCTION - get_ou_name
46 | DESCRIPTION - Utility to get the Operating Unit name for a given org_id
47 | This API should be replaced by
48 | fnd_access_control_util.get_org_name(AI.org_id)
49 *==========================================================================*/
50 FUNCTION Get_OU_NAME( p_org_id IN NUMBER)
51 RETURN VARCHAR2 IS
52
53 l_ou_name hr_all_organization_units.name%TYPE;
54
55 BEGIN
56 SELECT name
57 INTO l_ou_name
58 FROM hr_all_organization_units
59 WHERE organization_id = p_org_id;
60
61 RETURN l_ou_name;
62
63 END;
64
65 /*===========================================================================
66 | FUNCTION - Get_iby_payer_defaults
67 | DESCRIPTION - Defaults the following IBY defaults(org_id is NULL)
68 | 1. Document Rejection Level
69 | 2. Payment Rejection Level
70 | 3. Whether to Stop Procees for Review After Proposed
71 | payments creation
72 *==========================================================================*/
73 PROCEDURE Get_iby_payer_defaults(
74 p_doc_rejection_level_code OUT NOCOPY VARCHAR2,
75 p_payment_rejection_level_code OUT NOCOPY VARCHAR2,
76 p_payments_review_settings OUT NOCOPY VARCHAR2)
77 IS
78
79 BEGIN
80
81 SELECT document_rejection_level_code,
82 payment_rejection_level_code,
83 require_prop_pmts_review_flag
84 INTO p_doc_rejection_level_code,
85 p_payment_rejection_level_code,
86 p_payments_review_settings
87 FROM iby_internal_payers_all
88 WHERE org_id IS NULL;
89
90 EXCEPTION
91 WHEN NO_DATA_FOUND
92 THEN NULL;
93
94 END ;
95
96 /*===========================================================================
97 | FUNCTION - get_pi_count
98 | DESCRIPTION - This is the number of payment instructions created for all
99 | payments belonging to the PPR. This appears only after at
100 | least one exists.
101 |
102 *==========================================================================*/
103 FUNCTION get_pi_count(p_psr_id IN NUMBER)
104 RETURN NUMBER IS
105
106 l_ps_count NUMBER;
107
108 BEGIN
109
110 IF ( p_psr_id IS NOT NULL ) THEN
111
112 SELECT COUNT(DISTINCT payment_instruction_id)
113 INTO l_ps_count
114 FROM iby_fd_paymentS_v
115 WHERE payment_instruction_id is NOT NULL
116 AND payment_service_request_id = p_psr_id;
117
118 END IF;
119 IF l_ps_count = 0 THEN
120 l_ps_count := NULL;
121 END IF;
122
123 RETURN l_ps_count;
124
125 END;
126
127 /*===========================================================================
128 | FUNCTION - get_selected_ps_count
129 | DESCRIPTION - Gets the count of selected scheduled payments for the
130 | Payment Batch(Pay Run)
131 |
132 *==========================================================================*/
133 FUNCTION get_selected_ps_count(p_checkrun_id IN NUMBER,
134 p_psr_id IN NUMBER)
135 RETURN NUMBER IS
136
137 l_count1 NUMBER;
138 l_count2 NUMBER;
139 l_selected_ps_count NUMBER;
140
141 BEGIN
142 l_selected_ps_count := 0;
143
144 SELECT count(*)
145 INTO l_count1
146 FROM ap_selected_invoices_All
147 WHERE checkrun_id = p_checkrun_id
148 AND original_invoice_id is NULL
149 AND original_payment_num is NULL
150 AND ok_to_pay_flag = 'Y';
151 --
152 SELECT count(*)
153 INTO l_count2
154 FROM ap_invoice_payments_all aip,
155 ap_checks_all ac
156 WHERE aip.check_id = ac.check_id
157 AND ac.checkrun_id = p_checkrun_id
158 AND aip.reversal_inv_pmt_id is null; -- Bug 10180975
159
160 --
161 l_selected_ps_count := l_count1 + l_count2;
162 --
163
164 RETURN l_selected_ps_count;
165
166 END;
167
168
169 /*===========================================================================
170 | FUNCTION - get_unsel_ps_count
171 | DESCRIPTION - Gets the count of un-selected scheduled payments for the
172 | Payment Batch(Pay Run)
173 |
174 *==========================================================================*/
175 FUNCTION Get_unsel_ps_Count(p_checkrun_id IN NUMBER)
176 RETURN NUMBER IS
177
178 l_unsel_ps_count NUMBER;
179
180 BEGIN
181
182 SELECT count(*)
183 INTO l_unsel_ps_count
184 FROM ap_unselected_invoices_All
185 WHERE checkrun_id = p_checkrun_id;
186 RETURN l_unsel_ps_count;
187
188 END;
189
190
191 /*=============================================================================
192 | FUNCTION - Get_Rejected_ps_Count
193 | DESCRIPTION - This is the total count of scheduled payments that Oracle
194 | Payments(IBY) has rejected.
195 |
196 *============================================================================*/
197 FUNCTION Get_Rejected_ps_Count(p_psr_id IN NUMBER)
198 RETURN NUMBER IS
199
200 l_rejected_ps_count NUMBER;
201
202 BEGIN
203
204 IF ( p_psr_id IS NOT NULL ) THEN
205
206 SELECT count(*)
207 INTO l_rejected_ps_count
208 FROM IBY_DOCS_PAYABLE_ALL
209 WHERE payment_service_request_id = p_psr_id
210 AND rejected_docs_group_id is NOT NULL;
211
212 END IF;
213
214 IF l_rejected_ps_count = 0 THEN
215 l_rejected_ps_count := NULL;
216 END IF;
217
218 RETURN l_rejected_ps_count;
219
220 END;
221
222 /*===========================================================================
223 | FUNCTION - get_unsel_reason_count
224 | DESCRIPTION - This is the count of payment schedules that met the criteria
225 | but were not selected for a particular dont_pay_reason_code
226 |
227 *==========================================================================*/
228 FUNCTION get_unsel_reason_count(
229 p_checkrun_id IN NUMBER,
230 p_dont_pay_code IN VARCHAR2)
231 RETURN NUMBER IS
232
233 l_count NUMBER;
234 BEGIN
235 IF p_dont_pay_code = 'OTHERS' THEN
236 SELECT count(*)
237 INTO l_count
238 FROM ap_unselected_invoices_all
239 WHERE checkrun_id = p_checkrun_id
240 AND dont_pay_reason_code NOT IN ('NEEDS_INVOICE_VALIDATION',
241 'FAILED_INVOICE_VALIDATION',
242 'ZERO INVOICE',
243 'NEEDS_APPROVAL',
244 'APPROVER_REJECTED',
245 'USER REMOVED',
246 'SCHEDULED_PAYMENT_HOLD',
247 'SUPPLIER_SITE_HOLD',
248 'DISCOUNT_RATE_TOO_LOW');
249 ELSE
250
251 SELECT count(*)
252 INTO l_count
253 FROM ap_unselected_invoices_all
254 WHERE checkrun_id = p_checkrun_id
255 AND dont_pay_reason_code = p_dont_pay_code;
256
257 END IF;
258
259 RETURN l_count;
260
261 END;
262
263 /*===========================================================================
264 | FUNCTION - get_ps_ur_count
265 | DESCRIPTION - Gets the count of scheduled payments for the missing User
266 | exchange rates for a combination of payment currency
267 | and the ledger currency(Invoices associated with one OU
268 | have one ledger currency associated with them).
269 | Usage: PsExchangeRatesPG.
270 |
271 *==========================================================================*/
272 FUNCTION get_ps_ur_count(
273 p_checkrun_id IN NUMBER,
274 p_ledger_currency_code IN VARCHAR2,
275 p_payment_currency_code IN VARCHAR2)
276 RETURN NUMBER IS
277
278 l_ps_ur_count NUMBER;
279
280
281 BEGIN
282
283 SELECT count(*)
284 INTO l_ps_ur_count
285 FROM ap_selected_invoices_All asi,
286 ap_system_parameters_all asp
287 WHERE asi.org_id = asp.org_id
288 AND asi.checkrun_id = p_checkrun_id
289 AND asp.base_currency_code = p_ledger_currency_code
290 AND asi.payment_currency_code = p_payment_currency_code
291 AND asi.payment_exchange_rate_type = 'User'
292 AND asi.ok_to_pay_flag = 'Y';
293
294
295 RETURN l_ps_ur_count;
296
297 END;
298
299 /*=============================================================================
300 | FUNCTION - Get_missing_rates_ps_count
301 | DESCRIPTION - This is the total count of selected scheduled payments that
302 | have missing exchange rates
303 |
304 *============================================================================*/
305 FUNCTION Get_missing_rates_ps_Count(p_checkrun_id IN NUMBER)
306 RETURN NUMBER IS
307
308 l_missing_rates_ps_count NUMBER := 0;
309
310 BEGIN
311
312 SELECT count(*)
313 INTO l_missing_rates_ps_count
314 FROM ap_selected_invoices_all asi,
315 ap_system_parameters_all asp
316 WHERE asi.org_id = asp.org_id
317 AND asi.checkrun_id = p_checkrun_id
318 AND asp.base_currency_code <> asi.payment_currency_code
319 AND asi.payment_exchange_rate_type = 'User'
320 AND asi.payment_exchange_rate is NULL
321 AND asi.ok_to_pay_flag = 'Y'
322 AND EXISTS (SELECT 'No User Rate'
323 FROM ap_user_exchange_rates aur
324 WHERE aur.ledger_currency_code = asp.base_currency_code
325 AND aur.payment_currency_code = asi.payment_currency_code
326 AND aur.exchange_rate is NULL);
327
328 RETURN l_missing_rates_ps_count;
329
330 END;
331
332
333 /*=============================================================================
334 | FUNCTION - Get_interest_due
335 | DESCRIPTION - This is the Total Interest due for the Selected Payment
336 | Schedule wrt the Payment Date on the Pay Run. The Interest
337 | Due is calculated during the AutoSelect/Recalculation.
338 |
339 *============================================================================*/
340 FUNCTION Get_Interest_due(
341 p_checkrun_id IN NUMBER,
342 p_invoice_id IN NUMBER,
343 p_payment_num IN NUMBER
344 )
345 RETURN NUMBER IS
346
347 l_interest_due NUMBER;
348
349 BEGIN
350
351 BEGIN
352 SELECT payment_amount
353 INTO l_interest_due
354 FROM ap_selected_invoices_All
355 WHERE checkrun_id = p_checkrun_id
356 AND original_invoice_id = p_invoice_id
357 AND original_payment_num = p_payment_num;
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 l_interest_due := NULL;
361 END;
362
363 RETURN l_interest_due;
364 END;
365
366
367 /*===========================================================================
368 | FUNCTION - Get_interest_paid
369 | DESCRIPTION - This is the total interest that has been paid for the Invoice
370 | Used in PsDetailsPG
371 |
372 *==========================================================================*/
373 FUNCTION get_interest_paid(p_invoice_id IN NUMBER)
374 RETURN NUMBER IS
375
376 l_interest_paid NUMBER := 0;
377
378 BEGIN
379
380 BEGIN
381
382 SELECT SUM(NVL(amount_paid,0))
383 INTO l_interest_paid
384 FROM ap_invoices_all ai
385 WHERE invoice_id IN (
386 SELECT DISTINCT related_invoice_id
387 FROM ap_invoice_relationships air
388 WHERE original_invoice_id = p_invoice_id );
389 EXCEPTION
390 WHEN NO_DATA_FOUND THEN
391 NULL;
392 END;
393
394 RETURN l_interest_paid;
395 END;
396
397 /*===========================================================================
398 | FUNCTION - Get_interest_inv
399 | DESCRIPTION - This is the Interest Invoice planned for the Selected
400 | Scheduled Payment.
401 | Used in PsDetailsPG
402 |
403 *==========================================================================*/
404 FUNCTION Get_Interest_inv(
405 p_checkrun_id IN NUMBER,
406 p_invoice_id IN NUMBER,
407 p_payment_num IN NUMBER
408 )
409 RETURN VARCHAR2 IS
410
411 l_interest_inv AP_INVOICES_ALL.invoice_num%TYPE;
412
413
414 BEGIN
415
416 BEGIN
417 SELECT invoice_num
418 INTO l_interest_inv
419 FROM ap_selected_invoices_All
420 WHERE checkrun_id = p_checkrun_id
421 AND original_invoice_id = p_invoice_id
422 AND original_payment_num = p_payment_num;
423 EXCEPTION
424 WHEN NO_DATA_FOUND THEN
425 NULL;
426 END;
427
428 RETURN l_interest_inv;
429 END;
430
431
432 /*=============================================================================
433 | FUNCTION - Get_gain_loss_amt
434 | DESCRIPTION - This function would calculate the estimated gain loss for the
435 | Payment Schedule.
436 | PsDetailsPG : Nice to have
437 |
438 *============================================================================*/
439 FUNCTION Get_gain_loss_amount
440 RETURN NUMBER IS
441 l_gain_loss_amt NUMBER;
442
443 BEGIN
444
445 RETURN l_gain_loss_amt;
446
447 END;
448
449
450 /*===========================================================================
451 | FUNCTION - Get_interest_rate
452 | DESCRIPTION - This is the interest rate use to create planned interest
453 | invoices. The rate is calculated wrt the payment date
454 |
455 *==========================================================================*/
456 FUNCTION get_interest_rate(p_check_date IN DATE)
457 RETURN NUMBER IS
458
459 l_rate NUMBER;
460
461 BEGIN
462
463 BEGIN
464 SELECT annual_interest_rate
465 INTO l_rate
466 FROM ap_interest_periods
467 WHERE p_check_date BETWEEN start_date and end_date;
468 EXCEPTION
469 WHEN NO_DATA_FOUND THEN
470 l_rate := NULL;
471 END;
472
473 RETURN l_rate;
474 END;
475
476
477 /*=============================================================================
478 | FUNCTION - get_payment_status_flag
479 | DESCRIPTION - This function returns the status w.r.t payments that have been
480 | created in IBY for this Payment Process Request(Checkrun_id)
481 |
482 |
483 | BUG 6476559 - Removed the entire code in this function and calling the IBY
484 | function to return the status flag.
485 |
486 *============================================================================*/
487 FUNCTION get_payment_status_flag(p_psr_id IN NUMBER)
488 RETURN VARCHAR2 IS
489
490 l_payment_status_flag VARCHAR2(1);
491 l_total_pmt_count NUMBER;
492 l_pmt_complete_count NUMBER;
493
494
495 BEGIN
496
497 l_payment_status_flag := IBY_UTILITY_PVT.get_payment_status_flag(p_psr_id);
498
499 RETURN l_payment_status_flag;
500
501 END get_payment_status_flag;
502
503
504
505 /*=============================================================================
506 | FUNCTION - get_psr_status
507 | DESCRIPTION - This function returns the status w.r.t payments that have been
508 | created in IBY for this Payment Process Request(Checkrun_id)
509 |
510 |
511 | BUG 6476559 - Removed the entire code in this function and calling the IBY
512 | function to return the status.
513 |
514 *============================================================================*/
515 FUNCTION get_psr_status(p_psr_id IN NUMBER,
516 p_psr_status IN VARCHAR2)
517 RETURN VARCHAR2 IS
518
519 l_psr_status VARCHAR2(30);
520 l_total_pmt_count NUMBER;
521 l_instr_count NUMBER;
522 l_pmt_terminate_count NUMBER;
523
524
525 BEGIN
526
527 l_psr_status := IBY_UTILITY_PVT.get_psr_status(p_psr_id
528 ,p_psr_status);
529
530 RETURN l_psr_status;
531
532 END get_psr_status;
533
534 /*===========================================================================
535 | FUNCTION - get_destination_function
536 | DESCRIPTION - The destination function is attached to the Payment Process
537 | request Link on the PsrSearch Page. The Payment Process
538 | Name Link navigates the user to Selected Payment Schedules
539 | Page if the request is not yet submitted to IBY and status
540 | is 'REVIEW' or 'MISSING RATES'. If the request has been
541 | submitted to IBY, the link navigates the user to
542 | IBY_FD_REQUEST_DETAIL Page unless the status is INSERTED' or
543 | 'SUBMITTED'. For all other statuses the Link is disabled
544 |
545 |
546 *==========================================================================*/
547 FUNCTION get_destination_function(p_checkrun_id IN NUMBER,
548 p_status_code IN VARCHAR2)
549 RETURN VARCHAR2 IS
550
551 l_function fnd_form_functions.FUNCTION_NAME%TYPE;
552
553 l_count1 NUMBER;
554 l_count2 NUMBER;
555
556 l_count3 NUMBER;
557 l_count4 NUMBER;
558
559 BEGIN
560
561 /* Code Added for Bug#9276224 Start */
562 SELECT count(*)
563 INTO l_count1
564 FROM ap_selected_invoices_All
565 WHERE checkrun_id = p_checkrun_id;
566
567 SELECT count(*)
568 INTO l_count2
569 FROM ap_selected_invoices
570 WHERE checkrun_id = p_checkrun_id;
571
572 -- current user doesn't has access to all the selected docs
573 IF (l_count1 <> l_count2) THEN
574 RETURN NULL;
575 END IF;
576 /* Code Added for Bug#9276224 End */
577
578 /* Added for bug#11774797 Start */
579 cep_standard.init_security;
580
581 SELECT count(*)
582 INTO l_count3
583 FROM iby_pay_service_requests ipsr
584 , ap_inv_selection_criteria_all aisca
585 WHERE aisca.checkrun_id = p_checkrun_id
586 AND aisca.checkrun_name = ipsr.call_app_pay_service_req_code;
587
588 SELECT count(*)
589 INTO l_count4
590 FROM iby_pay_request_sec_v ipsr
591 , ap_inv_selection_criteria_all aisca
592 WHERE aisca.checkrun_id = p_checkrun_id
593 AND aisca.checkrun_name = ipsr.call_app_pay_service_req_code;
594
595 -- current user doesn't has access to all the selected docs
596 IF (l_count3 <> l_count4) THEN
597 RETURN NULL;
598 END IF;
599 /* Added for bug#11774797 End */
600
601 IF p_status_code IN ('REVIEW', 'MISSING RATES') THEN
602 l_function := 'AP_PAY_PSR_SEL_DOCS';
603
604 /* commented for bug#9276224 Start
605 SELECT count(*)
606 INTO l_count1
607 FROM ap_selected_invoices_All
608 WHERE checkrun_id = p_checkrun_id;
609
610 SELECT count(*)
611 INTO l_count2
612 FROM ap_selected_invoices
613 WHERE checkrun_id = p_checkrun_id;
614
615 -- current user doesn't has access to all the selected docs
616 IF (l_count1 <> l_count2) THEN
617 l_function := NULL;
618 END IF;
619 commented for bug#9276224 End */
620
621 -- bug 9384008. Added COMPLETED status to the list of statuses.
622
623 ELSIF p_status_code IN (
624 'INFORMATION_REQUIRED', 'ASSIGNMENT_COMPLETE',
625 'DOCUMENTS_VALIDATED', 'VALIDATION_FAILED',
626 'RETRY_DOCUMENT_VALIDATION', 'PENDING_REVIEW_DOC_VAL_ERRORS',
627 'PAYMENTS_CREATED', 'PENDING_REVIEW',
628 'FAILED_PAYMENT_VALIDATION', 'PENDING_REVIEW_PMT_VAL_ERRORS',
629 'RETRY_PAYMENT_CREATION', 'RETRY_PAYMENT_CREATION',
630 'TERMINATED', 'COMPLETED') THEN
631 l_function := 'IBY_FD_REQUEST_DETAIL';
632 ELSE
633 --SQLAP --'UNSTARTED', 'SELECTING', 'CANCELLED NO PAYMENTS' , 'SELECTED'
634 --IBY -- 'INSERTED', 'SUBMITTED'
635 l_function := null;
636
637 END IF;
638
639 RETURN l_function;
640
641 END;
642
643
644 /*=============================================================================
645 | FUNCTION - Action_Switcher
646 | DESCRIPTION - The function is attached to the ActionSwitcher
647 | TakeActionEnabled -- takeaction_enabled.gif(ActionFunction)
648 | TakeActionDisabled -- No Icon (No Action)
649 | WarningIndEvenActive -- warningind_even_active.gif (This case can happens
650 | when say a Concurrent Program is Terminated).
651 | ActionInProgress -- inprogressind_status.gif (No Action)
652 |
653 *============================================================================*/
654 FUNCTION Action_Switcher(p_checkrun_id IN NUMBER,
655 p_psr_id IN NUMBER,
656 p_status_code IN VARCHAR2,
657 p_checkrun_request_id IN NUMBER,
658 p_psr_request_id IN NUMBER
659 )
660 RETURN VARCHAR2 IS
661
662 l_action VARCHAR2(100);
663 l_count1 NUMBER;
664 l_count2 NUMBER;
665 l_request_status VARCHAR2(100);
666
667 BEGIN
668
669 /* Added for bug#9276224 Start */
670 SELECT count(*)
671 INTO l_count1
672 FROM ap_selected_invoices_All
673 WHERE checkrun_id = p_checkrun_id;
674
675 SELECT count(*)
676 INTO l_count2
677 FROM ap_selected_invoices
678 WHERE checkrun_id = p_checkrun_id;
679
680 -- current user doesn't has access to all the selected docs
681 IF (l_count1 <> l_count2) THEN
682 RETURN 'TakeActionDisabled';
683 END IF;
684 /* Added for bug#9276224 End */
685
686 IF p_psr_id is NULL THEN
687
688 IF p_status_code IN ( 'REVIEW', 'MISSING RATES') THEN
689 l_action := 'TakeActionEnabled';
690 ELSE
691 l_action := 'TakeActionDisabled';
692 END IF;
693
694 ELSE --Request is submitted to IBY
695
696 IF p_status_code IN ( 'INFORMATION_REQUIRED',
697 'PENDING_REVIEW_DOC_VAL_ERRORS',
698 'PENDING_REVIEW_PMT_VAL_ERRORS',
699 'PENDING_REVIEW') THEN
700
701 l_action := 'TakeActionEnabled';
702
703 ELSE
704
705 l_action := 'TakeActionDisabled';
706 END IF;
707 --
708 --If the Conc Request is complete(Normal) IBY NULL's out the Request_id column
709 IF l_action = 'TakeActionEnabled' AND p_psr_request_id IS NOT NULL THEN
710
711 l_request_status :=
712 iby_disburse_ui_api_pub_pkg.get_conc_request_status(
713 p_psr_request_id);
714 --
715 IF (l_request_status = 'SUCCESS') THEN
716 l_action := 'TakeActionEnabled';
717 ELSIF (l_request_status = 'ERROR') THEN
718 l_action := 'WarningIndEvenActive';
719 ELSE
720 l_action := 'ActionInProgress';
721 END IF;
722 END IF;
723 END IF;
724
725 /* commented for bug#9276224 Start
726 --Disable Action if User doesn't has MOAC access
727 IF l_action IN ('TakeActionEnabled') THEN
728 --
729 SELECT count(*)
730 INTO l_count1
731 FROM ap_selected_invoices_All
732 WHERE checkrun_id = p_checkrun_id;
733
734 SELECT count(*)
735 INTO l_count2
736 FROM ap_selected_invoices
737 WHERE checkrun_id = p_checkrun_id;
738
739 -- current user doesn't has access to all the selected docs
740 IF (l_count1 <> l_count2) THEN
741 l_action := 'TakeActionDisabled';
742 END IF;
743 --
744 END IF;
745 --
746 commented for bug#9276224 End */
747
748 RETURN l_action;
749 --
750 END;
751
752 /*=============================================================================
753 | FUNCTION - Get_Action_Function
754 | DESCRIPTION - The Action function is attached to the Start Action Icon
755 | The Icon navigates the user to Selected Payment Schedules
756 | Page if the request is not yet submitted to IBY and status
757 | is 'REVIEW' or 'MISSING RATES'. If the request has been
758 | submitted to IBY, the link navigates the user to the
759 | following pages
760 |
761 | IBY Statuses Destinations
762 | ---------------------------- -----------------------
763 | INFORMATION_REQUIRED IBY_FD_ASSIGN_COMPLETE
764 | PENDING_REVIEW_DOC_VAL_ERRORS IBY_FD_DOCS_VALIDATE
765 | PENDING_REVIEW_PMT_VAL_ERRORS IBY_FD_PAYMENT_VALIDATE
766 | PENDING_REVIEW IBY_FD_PAYMENT_REVIEW
767 |
768 *============================================================================*/
769 FUNCTION get_action_function(p_checkrun_id IN NUMBER,
770 p_status_code IN VARCHAR2)
771 RETURN VARCHAR2 IS
772
773 l_function fnd_form_functions.function_name%TYPE;
774 l_count1 NUMBER;
775 l_count2 NUMBER;
776
777 BEGIN
778
779 IF p_status_code IN ('REVIEW', 'MISSING RATES') THEN
780 l_function := 'AP_PAY_PSR_SEL_DOCS';
781
782 ELSIF p_status_code IN ('INFORMATION_REQUIRED') THEN
783 l_function := 'IBY_FD_ASSIGN_COMPLETE'; -- Complete Document
784 -- Assignments
785 ELSIF p_status_code IN ('PENDING_REVIEW_DOC_VAL_ERRORS') THEN
786 l_function := 'IBY_FD_DOCS_VALIDATE'; -- Resolve Document
787 -- Validation Errors
788 ELSIF p_status_code IN ('PENDING_REVIEW_PMT_VAL_ERRORS') THEN
789 l_function := 'IBY_FD_PAYMENT_VALIDATE'; -- Resolve Payment
790 -- Validation Errors
791 ELSIF p_status_code IN ('PENDING_REVIEW') THEN
792 l_function := 'IBY_FD_PAYMENT_REVIEW'; -- Review proposed
793 -- payment
794 ELSE
795 l_function := null;
796 END IF;
797
798 IF l_function is NOT NULL THEN
799 SELECT count(*)
800 INTO l_count1
801 FROM ap_selected_invoices_All
802 WHERE checkrun_id = p_checkrun_id;
803
804
805 SELECT count(*)
806 INTO l_count2
807 FROM ap_selected_invoices
808 WHERE checkrun_id = p_checkrun_id;
809
810 -- current user doesn't has access to all the selected docs
811 IF (l_count1 <> l_count2) THEN
812 l_function := NULL;
813 END IF;
814
815 END IF;
816
817
818 RETURN l_function;
819
820 END;
821
822
823 /*===========================================================================
824 | FUNCTION - get_psr_snapshot_count
825 | DESCRIPTION - This function is designed for the Payables Payment Manager
826 | Home Page . The function returns the total count of Payment
827 | Process Requests with a particular Status or a combination
828 | of Payment Process Request Statuses that map to a particular
829 | snapshot code
830 |
831 | BUG 6476559 - Removed the entire code in this function and calling the IBY
832 | function to return the count.
833 |
834 |
835 *==========================================================================*/
836 FUNCTION get_psr_snapshot_count(p_snapshot_code IN VARCHAR2)
837 RETURN NUMBER IS
838
839 l_ret_val NUMBER;
840
841 BEGIN
842
843 l_ret_val := IBY_UTILITY_PVT.get_psr_snapshot_count(p_snapshot_code);
844
845 RETURN l_ret_val;
846
847 END;
848
849 /*=============================================================================
850 | FUNCTION - Update Payment Schedules
851 | DESCRIPTION - This method locks the newly added Payment Schedules
852 |
853 | Bug 5646905 -- Modified the update statement from the = clause to the IN
854 | clause so the plan can execute the sub-query first.
855 |
856 |
857 *============================================================================*/
858 FUNCTION Update_payment_schedules(
859 p_checkrun_id IN NUMBER)
860 RETURN VARCHAR2 IS
861
862 BEGIN
863 UPDATE ap_payment_schedules_all APS
864 SET APS.checkrun_id = p_checkrun_id
865 WHERE (APS.invoice_id, APS.payment_num ) IN
866 (SELECT ASI.invoice_id, ASI.payment_num
867 FROM ap_selected_invoices_all ASI
868 WHERE APS.invoice_id = ASI.invoice_id
869 AND APS.payment_num = ASI.payment_num
870 AND ASI.checkrun_id = p_checkrun_id)
871 AND APS.checkrun_id is NULL;
872
873 RETURN 'S';
874 END;
875
876 /*=============================================================================
877 | FUNCTION - Validates Psr
878 | DESCRIPTION - This method is a wrapper on IBY's API
879 | IBY_FD_USER_API_PUB.Validate_Method_and_Profile(ibyfduas.pls)
880 *============================================================================*/
881 FUNCTION Validate_Psr(
882 p_checkrun_id IN NUMBER)
883 RETURN VARCHAR2 IS
884
885 l_ret_status VARCHAR2(1) := 'E';
886 l_msg_count NUMBER;
887 l_msg_data VARCHAR2(2000);
888
889 l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
890 l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
891 l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
892 l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
893
894
895 l_le_arr IBY_FD_USER_API_PUB.Legal_Entity_Tab_Type;
896 l_int_bank_account_id NUMBER;
897 l_int_bank_account_arr IBY_FD_USER_API_PUB.Int_Bank_Acc_Tab_Type;
898 l_curr_arr IBY_FD_USER_API_PUB.Currency_Tab_type;
899 l_ou_arr IBY_FD_USER_API_PUB.Org_Tab_Type;
900
901 CURSOR curr_arr_cursor IS
902 SELECT currency_code
903 FROM ap_currency_group
904 WHERE checkrun_id = p_checkrun_id;
905
906 CURSOR le_arr_cursor IS
907 SELECT legal_entity_id
908 FROM ap_le_group
909 WHERE checkrun_id = p_checkrun_id;
910
911 CURSOR ou_arr_cursor IS
912 SELECT org_id,
913 'OPERATING_UNIT'
914 FROM ap_ou_group
915 WHERE checkrun_id = p_checkrun_id;
916
917 BEGIN
918 --
919 fnd_msg_pub.Initialize;
920 --
921 SELECT payment_method_code,
922 payment_profile_id,
923 bank_account_id,
924 payment_document_id,
925 create_instrs_flag
926 INTO l_payment_method_code,
927 l_payment_profile_id,
928 l_int_bank_account_id,
929 l_payment_document_id,
930 l_create_instrs_flag
931 FROM ap_inv_selection_criteria_all
932 WHERE checkrun_id = p_checkrun_id;
933 --
934 OPEN curr_arr_cursor;
935 FETCH curr_arr_cursor
936 BULK COLLECT INTO l_curr_arr;
937 CLOSE curr_arr_cursor;
938 --
939 OPEN le_arr_cursor;
940 FETCH le_arr_cursor
941 BULK COLLECT INTO l_le_arr;
942 CLOSE le_arr_cursor;
943 --
944 OPEN ou_arr_cursor;
945 FETCH ou_arr_cursor
946 BULK COLLECT INTO l_ou_arr;
947 CLOSE ou_arr_cursor;
948 --
949 IF l_int_bank_account_id IS NOT NULL THEN
950 l_int_bank_account_arr(1) := l_int_bank_Account_id;
951 END IF;
952 --
953 IBY_FD_USER_API_PUB.Validate_Method_and_Profile (
954 p_api_version => 1.0,
955 p_init_msg_list => 'F',
956 p_payment_method_code => l_payment_method_code,
957 p_ppp_id => l_payment_profile_id,
958 p_payment_document_id => l_payment_document_id,
959 p_crt_instr_flag => l_create_instrs_flag,
960 p_int_bank_acc_arr => l_int_bank_account_arr,
961 p_le_arr => l_le_arr,
962 p_org_arr => l_ou_arr,
963 p_curr_arr => l_curr_arr,
964 x_return_status => l_ret_status,
965 x_msg_count => l_msg_count,
966 x_msg_data => l_msg_data);
967 --
968 l_curr_arr.DELETE;
969 l_le_arr.DELETE;
970 l_ou_arr.DELETE;
971 --
972 RETURN l_ret_status;
973
974 END;
975
976 /*=============================================================================
977 | FUNCTION - Validates Payment Template
978 | DESCRIPTION - This method is a wrapper on IBY's API
979 | IBY_FD_USER_API_PUB.Validate_Method_and_Profile(ibyfduas.pls)
980 |
981 *============================================================================*/
982 FUNCTION Validate_payment_template(
983 p_template_id IN NUMBER)
984 RETURN VARCHAR2 IS
985
986 l_ret_status VARCHAR2(1) := 'E';
987 l_msg_count NUMBER;
988 l_msg_data VARCHAR2(2000);
989
990 l_payment_method_code ap_inv_selection_criteria_all.payment_method_code%TYPE;
991 l_payment_profile_id ap_inv_selection_criteria_all.payment_profile_id%TYPE;
992 l_payment_document_id ap_inv_selection_criteria_all.payment_document_id%TYPE;
993 l_create_instrs_flag ap_inv_selection_criteria_all.create_instrs_flag%TYPE;
994
995 l_le_arr IBY_FD_USER_API_PUB.Legal_Entity_Tab_Type;
996 l_int_bank_account_id NUMBER;
997 l_int_bank_account_arr IBY_FD_USER_API_PUB.Int_Bank_Acc_Tab_Type;
998 l_curr_arr IBY_FD_USER_API_PUB.Currency_Tab_type;
999 l_ou_arr IBY_FD_USER_API_PUB.Org_Tab_Type;
1000
1001 CURSOR curr_arr_cursor IS
1002 SELECT currency_code
1003 FROM ap_currency_group
1004 WHERE template_id = p_template_id;
1005
1006 CURSOR le_arr_cursor IS
1007 SELECT legal_entity_id
1008 FROM ap_le_group
1009 WHERE template_id = p_template_id;
1010
1011 CURSOR ou_arr_cursor IS
1012 SELECT org_id,
1013 'OPERATING_UNIT'
1014 FROM ap_ou_group
1015 WHERE template_id = p_template_id;
1016
1017 BEGIN
1018 --
1019 fnd_msg_pub.Initialize;
1020 --
1021 SELECT payment_method_code,
1022 payment_profile_id,
1023 bank_account_id,
1024 payment_document_id,
1025 create_instrs_flag
1026 INTO l_payment_method_code,
1027 l_payment_profile_id,
1028 l_int_bank_account_id,
1029 l_payment_document_id,
1030 l_create_instrs_flag
1031 FROM ap_payment_templates
1032 WHERE template_id = p_template_id;
1033 --
1034 OPEN curr_arr_cursor;
1035 FETCH curr_arr_cursor
1036 BULK COLLECT INTO l_curr_arr;
1037 CLOSE curr_arr_cursor;
1038 --
1039 OPEN le_arr_cursor;
1040 FETCH le_arr_cursor
1041 BULK COLLECT INTO l_le_arr;
1042 CLOSE le_arr_cursor;
1043 --
1044 OPEN ou_arr_cursor;
1045 FETCH ou_arr_cursor
1046 BULK COLLECT INTO l_ou_arr;
1047 CLOSE ou_arr_cursor;
1048 --
1049 IF l_int_bank_account_id IS NOT NULL THEN
1050 l_int_bank_account_arr(1) := l_int_bank_Account_id;
1051 END IF;
1052 --
1053 IBY_FD_USER_API_PUB.Validate_Method_and_Profile (
1054 p_api_version => 1.0,
1055 p_init_msg_list => 'F',
1056 p_payment_method_code => l_payment_method_code,
1057 p_ppp_id => l_payment_profile_id,
1058 p_payment_document_id => l_payment_document_id,
1059 p_crt_instr_flag => l_create_instrs_flag,
1060 p_int_bank_acc_arr => l_int_bank_account_arr,
1061 p_le_arr => l_le_arr,
1062 p_org_arr => l_ou_arr,
1063 p_curr_arr => l_curr_arr,
1064 x_return_status => l_ret_status,
1065 x_msg_count => l_msg_count,
1066 x_msg_data => l_msg_data);
1067
1068 --
1069 l_curr_arr.DELETE;
1070 l_le_arr.DELETE;
1071 l_ou_arr.DELETE;
1072 --
1073 RETURN l_ret_status;
1074 --
1075 END;
1076
1077 /*=============================================================================
1078 | FUNCTION - Get_request_status
1079 | DESCRIPTION - This function returns YES if a batch can be cancelled and NO
1080 | if the PPR is not allowed to be cancelled.
1081 | In NO case, appropriate error messages are thrown on to OA page.
1082 | Earlier, this method was used to determine whether Cancel icon
1083 | should be enabled. Post PPR/PI Termination enhancement, this
1084 | is used to check if PPR can be terminated after clicking the
1085 | always enabled Terminate icon.
1086 *============================================================================*/
1087 FUNCTION Get_request_status
1088 (p_checkrun_id IN NUMBER,
1089 p_psr_id IN NUMBER)
1090 RETURN VARCHAR2
1091 IS
1092 l_request_id NUMBER;
1093 call_status BOOLEAN;
1094 rphase VARCHAR2(80);
1095 rstatus VARCHAR2(80);
1096 dphase VARCHAR2(30);
1097 dstatus VARCHAR2(30);
1098 message VARCHAR2(240);
1099 l_status VARCHAR2(30);
1100 l_payment_status VARCHAR2(1);
1101 l_psr_status VARCHAR2(30);
1102 l_pmt_complete_count NUMBER;
1103 -- added 2 vars for Bug 9074848 PPR/PI Termination Enhancement
1104 l_straight_through_flag VARCHAR2(1);
1105 l_ppr_term_allowed VARCHAR2(3);
1106 BEGIN
1107
1108 FND_MSG_PUB.initialize;
1109 IF p_psr_id IS NULL THEN
1110 --
1111
1112 /* Bug 9074848 PPR/PI Termination Enhancement
1113 Commented below SELECT as no further code uses it.
1114 */
1115
1116 /*
1117 SELECT request_id, status
1118 INTO l_request_id, l_status
1119 FROM ap_inv_selection_criteria_all
1120 WHERE checkrun_id = p_checkrun_id;
1121 */
1122
1123
1124 /* Bug 9074848 PPR/PI Termination Enhancement
1125 Commented below check for 11i batches.
1126 We are disabling Terminate icon for such batches through the PsrSearchVO itself
1127 */
1128
1129 /*
1130 -- 11i Payment batches have request_id as NULL, disable the cancel icon
1131 IF l_request_id is NULL THEN
1132 RETURN 'N';
1133 END IF;
1134 */
1135
1136
1137 /* Bug 9074848 PPR/PI Termination Enhancement
1138 Commented below check for cancelled batches.
1139 We are disabling Terminate icon for such batches through the PsrSearchVO itself
1140 */
1141
1142
1143 /*
1144 --R12 baches which are already cancelled.
1145 IF l_status IN ( 'CANCELED' , 'CANCELLED NO PAYMENTS') THEN
1146 RETURN 'N';
1147 END IF;
1148
1149 */
1150
1151 /* Bug 9074848 PPR/PI Termination Enhancement
1152 Returning YES to signal cancellation of the AP PPR if no IBY PSR is raised yet.
1153 */
1154 RETURN 'YES';
1155 ELSE --IBY
1156
1157 /* Bug 11063950 : Populating request_id from ap_inv_selection_criteria_all instead of iby_pay_service_requests */
1158
1159 /* Commenting out for Bug 11063950 */
1160 /* SELECT request_id,
1161 payment_service_request_status
1162 INTO l_request_id,l_psr_status
1163 FROM iby_pay_service_requests
1164 WHERE payment_service_request_id = p_psr_id; */
1165
1166 SELECT request_id
1167 INTO l_request_id
1168 FROM ap_inv_selection_criteria_all
1169 WHERE checkrun_id = p_checkrun_id;
1170
1171 /* Bug 9074848 PPR/PI Termination Enhancement
1172 Commented below check for terminated Payment Service Request.
1173 We are disabling Terminate icon for such requests through the PsrSearchVO itself
1174 */
1175
1176 /* IF l_psr_status = 'TERMINATED' THEN
1177 RETURN 'N';
1178 END IF;
1179 */
1180
1181 SELECT Count(* )
1182 INTO l_pmt_complete_count
1183 FROM iby_payments_all
1184 WHERE payment_service_request_id = p_psr_id
1185 AND payment_instruction_id IS NOT NULL
1186 AND ROWNUM = 1;
1187
1188 --If Instructions exist, we should not allow Cancellation of the Pay Run.
1189 IF l_pmt_complete_count > 0 THEN
1190 /* Bug 9074848 PPR/PI Termination Enhancement
1191 At least on PI exists for this PPR.
1192 Check if straight-through processing is used.
1193 */
1194 SELECT Nvl(create_instrs_flag,'N')
1195 INTO l_straight_through_flag
1196 FROM ap_inv_selection_criteria_all
1197 WHERE checkrun_id = p_checkrun_id;
1198
1199 /* Bug 9074848 PPR/PI Termination Enhancement
1200 Throw error message if straight through processing is not used.
1201 PIs of this PPR may contain payments from other PPRs, so cant terminate.
1202 */
1203 IF l_straight_through_flag <> 'Y' THEN
1204 fnd_message.Set_name('SQLAP','AP_PPR_STRGT_THRU_NOT_USED');
1205 fnd_msg_pub.ADD;
1206 RETURN 'NO';
1207 ELSE -- straight-through processing is used
1208 -- Check if PPR can be terminated (whether all PIs of this PPR are terminate-able)
1209 l_ppr_term_allowed := iby_fd_user_api_pub.Ppr_sec_term_allowed(p_psr_id);
1210 IF l_ppr_term_allowed <> 'YES' THEN
1211 fnd_message.Set_name('SQLAP','AP_PPR_PI_CANT_TERM');
1212 fnd_msg_pub.ADD;
1213 RETURN 'NO';
1214 END IF;
1215 END IF;
1216 END IF; --psr_id is NOT NULL
1217 IF l_request_id IS NOT NULL THEN
1218 call_status := fnd_concurrent.Get_request_status(l_request_id,'','',rphase,rstatus,dphase,
1219 dstatus,message);
1220
1221 IF ((dphase = 'COMPLETE')
1222 OR (dphase IS NULL)) THEN
1223 RETURN 'YES';
1224 ELSE
1225 fnd_message.Set_name('SQLAP','AP_PPR_BUILD_PMT_RUNNING');
1226 fnd_msg_pub.ADD;
1227 RETURN 'NO';
1228 END IF;
1229 ELSE --request_id is NULL
1230 RETURN 'YES';
1231 END IF;
1232 END IF;
1233 RETURN 'YES';
1234 END;
1235
1236
1237
1238 /*=============================================================================
1239 | FUNCTION - Is Federal Installed
1240 | DESCRIPTION - This method is a wrapper on FV_INSTALL.Enabled(org_id)
1241 | API
1242 |
1243 *============================================================================*/
1244 FUNCTION Is_Federal_Installed(p_org_id IN NUMBER)
1245 RETURN VARCHAR2 IS
1246 BEGIN
1247
1248 IF FV_INSTALL.Enabled(p_org_id) THEN
1249 RETURN 'Y';
1250 ELSE
1251 RETURN 'N';
1252 END IF;
1253
1254 END Is_Federal_Installed;
1255
1256 /*=============================================================================
1257 | FUNCTION - CHECK_PPR_MOAC_ACCESS |
1258 | DESCRIPTION - This method will check if User can take action on PPR or not |
1259 | Logic is to check the each Org against the current user Org |
1260 | access. If any of the org is not listed under User's Security|
1261 | set by FND; Function will return 'N' else 'Y' |
1262 | |
1263 | Get Distinct Org Id for Invoices selected in PPR --> Count1 |
1264 | Check no of Org_Id accessible against the security context |
1265 | set in session. If Count mismatches Return 'N' else 'Y' |
1266 *============================================================================*/
1267 FUNCTION Check_PPR_MOAC_Access(p_checkrun_id IN NUMBER)
1268 RETURN VARCHAR2 IS
1269
1270 CURSOR C_GET_ORG_INFO IS
1271 SELECT DISTINCT ORG_ID
1272 FROM AP_SELECTED_INVOICES_ALL
1273 WHERE CHECKRUN_ID = p_checkrun_id
1274 and NVL(MO_GLOBAL.CHECK_ACCESS(ORG_ID), 'N') <> 'Y';
1275
1276 l_return_val VARCHAR2(1);
1277 l_org_id number;
1278 l_curent_calling_seq VARCHAR2(60);
1279 l_debug_info VARCHAR2(200);
1280 l_api_name VARCHAR2(100);
1281 BEGIN
1282 l_return_val := 'Y';
1283 l_api_name := 'Check_PPR_MOAC_Access';
1284 l_curent_calling_seq := 'AP_PAYMENT_UTIL_PKG.Check_PPR_MOAC_Access';
1285 l_debug_info := 'AP_PAYMENT_UTIL_PKG.Check_PPR_MOAC_Access(+)';
1286
1287 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1288 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1289 END IF;
1290 IF(p_checkrun_id IS NULL) THEN
1291 l_debug_info := 'Checkrun Id is NULL';
1292 RETURN 'N';
1293 END IF;
1294
1295 --If one record exists then Return the value as 'N'
1296 OPEN C_GET_ORG_INFO;
1297 FETCH C_GET_ORG_INFO INTO l_org_id;
1298 IF(C_GET_ORG_INFO%ROWCOUNT > 0) THEN
1299 l_return_val := 'N';
1300 END IF;
1301 CLOSE C_GET_ORG_INFO;
1302 l_debug_info := 'AP_PAYMENT_UTIL_PKG.Check_PPR_MOAC_Access(-) with Rtuen value ' ||l_return_val;
1303
1304 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1306 END IF;
1307
1308 RETURN l_return_val;
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 l_debug_info := 'AP_PAYMENT_UTIL_PKG.Check_PPR_MOAC_Access(-) Exception Occurs';
1312 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1313 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1314 END IF;
1315 RETURN 'N';
1316 END Check_PPR_MOAC_Access;
1317
1318 /* Added for bug#9835337 Start */
1319 FUNCTION Check_PPR_PMT_MOAC_Access(p_instruction_id IN NUMBER)
1320 RETURN VARCHAR2 IS
1321 l_access_link VARCHAR2(20);
1322 l_debug_info VARCHAR2(200);
1323 l_api_name VARCHAR2(100);
1324 BEGIN
1325 l_api_name := 'Check_PPR_PMT_MOAC_Access';
1326 l_access_link := 'NOLINK';
1327
1328 SELECT 'NOLINK'
1329 INTO l_access_link
1330 FROM dual
1331 WHERE EXISTS
1332 ( SELECT 1
1333 FROM iby_payments_all
1334 WHERE payment_instruction_id = p_instruction_id
1335 AND org_id <> -1
1336 AND MO_GLOBAL.CHECK_ACCESS(org_id) = 'N'
1337 );
1338 RETURN 'NOLINK';
1339
1340 EXCEPTION
1341 WHEN NO_DATA_FOUND THEN
1342 RETURN 'LINK';
1343 WHEN OTHERS THEN
1344 l_debug_info := 'AP_PAYMENT_UTIL_PKG.Check_PPR_PMT_MOAC_Access(-) Exception Occurs';
1345 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1346 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1347 END IF;
1348 RETURN 'NOLINK';
1349
1350 END Check_PPR_PMT_MOAC_Access;
1351 /* Added for bug#9835337 End */
1352
1353 END AP_PAYMENT_UTIL_PKG;
[Home] [Help]