AP - Invoice and Payment form Personalization

 Requirement 1

1) Prepayment Matching Validation Restriction (Disable)

substr(sri_ap_chk_prepay_fun(:INV_SUM_FOLDER.VENDOR_ID,:INV_SUM_FOLDER.VENDOR_SITE_ID,:INV_SUM_FOLDER.ORG_ID,:INV_SUM_FOLDER.INVOICE_TYPE,:INV_SUM_FOLDER.INVOICE_CURRENCY_CODE), 1, 1) <> 'N' and :INV_SUM_FOLDER.VENDOR_SITE_ID IS NOT NULL AND :INV_SUM_FOLDER.APPROVAL_STATUS_DISPLAY='Never Validated'

INV_SUM_ACTIONS.APPROVE                         

 =substr(sri_ap_chk_prepay_fun(:INV_SUM_FOLDER.VENDOR_ID,:INV_SUM_FOLDER.VENDOR_SITE_ID,:INV_SUM_FOLDER.ORG_ID,:INV_SUM_FOLDER.INVOICE_TYPE,:INV_SUM_FOLDER.INVOICE_CURRENCY_CODE), 1)

                    

    Enabled :- 71.1

        substr(sri_ap_chk_prepay_fun(:INV_SUM_FOLDER.VENDOR_ID,:INV_SUM_FOLDER.VENDOR_SITE_ID,:INV_SUM_FOLDER.ORG_ID,:INV_SUM_FOLDER.INVOICE_TYPE,:INV_SUM_FOLDER.INVOICE_CURRENCY_CODE), 1, 1) = 'N' and :INV_SUM_FOLDER.VENDOR_SITE_ID IS NOT NULL AND :INV_SUM_FOLDER.APPROVAL_STATUS_DISPLAY='Never Validated'

        

 sri_ap_chk_prepay_fun

   create or replace FUNCTION sri_ap_chk_prepay_fun

(p_vendor_id NUMBER

,p_vendor_site_id NUMBER

,p_num_org_id NUMBER

,p_inv_type VARCHAR2

,p_inv_cur VARCHAR2

) RETURN VARCHAR2 IS

l_payment_count NUMBER :=0;

l_msg VARCHAR2(2000);

BEGIN

IF p_inv_type = 'Standard' THEN

BEGIN

SELECT COUNT(1)

INTO l_payment_count

FROM ap_invoices_all aia,

( SELECT

decode(apps.ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount, payment_status_flag, invoice_type_lookup_code),

'APPROVED', 'Validated', 'FULL', 'Fully Validatd',

   'UNAPPROVED', 'Unvalidated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'NEVER APPROVED',

   'Never Validated', 'CANCELLED', 'Cancelled', 'UNPAID', 'Unpaid',

   'AVAILABLE', 'Available') status,

vendor_id,

vendor_site_id,

invoice_id

FROM  ap_invoices_all

WHERE 1=1

AND upper(invoice_type_lookup_code) = upper('Prepayment')

AND vendor_id = p_vendor_id

AND vendor_site_id = p_vendor_site_id

AND org_id = p_num_org_id

AND invoice_currency_code = p_inv_cur

) ail

WHERE 1 = 1

AND ail.invoice_id = aia.invoice_id

AND ail.status = 'Available'

AND aia.vendor_id = ail.vendor_id

AND aia.vendor_site_id = ail.vendor_site_id

AND upper(aia.invoice_type_lookup_code) = upper('Prepayment');

EXCEPTION

                     WHEN OTHERS

                     THEN

                        NULL;

END;

IF l_payment_count >0 THEN

fnd_message.set_name('SRI','SRI_AP_PREPAYMENTS_MSG');

l_msg := fnd_message.get;

RETURN l_msg;

END IF;

END IF;

  

  RETURN 'N';

END;

Requirement 2

    Restrict Payment invoice details for already paid Invoice

            substr(sri_ap_chk_payment_fun(:PAY_SUM_FOLDER.VENDOR_ID,:PAY_SUM_FOLDER.VENDOR_SITE_ID,:PAY_SUM_FOLDER.ORG_ID,:ADJ_INV_PAY.INVOICE_TYPE), 1, 1) <> 'N' and :PAY_SUM_FOLDER.VENDOR_SITE_ID IS NOT NULL

 =substr(sri_ap_chk_payment_fun(:PAY_SUM_FOLDER.VENDOR_ID,:PAY_SUM_FOLDER.VENDOR_SITE_ID,:PAY_SUM_FOLDER.ORG_ID,:ADJ_INV_PAY.INVOICE_TYPE), 1)

    

 sri_ap_chk_payment_fun

 create or replace FUNCTION sri_ap_chk_payment_fun

(p_vendor_id NUMBER

,p_vendor_site_id NUMBER

,p_num_org_id NUMBER

,p_inv_type VARCHAR2

) RETURN VARCHAR2 IS

l_payment_count NUMBER :=0;

l_msg VARCHAR2(2000);

BEGIN

IF p_inv_type = 'Standard' THEN

BEGIN

SELECT COUNT(1)

INTO l_payment_count

FROM ap_invoices_all aia,

( SELECT

decode(apps.ap_invoices_pkg.get_approval_status(invoice_id, invoice_amount, payment_status_flag, invoice_type_lookup_code),

'APPROVED', 'Validated', 'FULL', 'Fully Validatd',

   'UNAPPROVED', 'Unvalidated', 'NEEDS REAPPROVAL', 'Needs Revalidation', 'NEVER APPROVED',

   'Never Validated', 'CANCELLED', 'Cancelled', 'UNPAID', 'Unpaid',

   'AVAILABLE', 'Available') status,

vendor_id,

vendor_site_id,

invoice_id

FROM  ap_invoices_all

WHERE 1=1

AND upper(invoice_type_lookup_code) = upper('Prepayment')

AND vendor_id = p_vendor_id

AND vendor_site_id = p_vendor_site_id

--AND Invoice_id = l_num_pay_inv_id

AND org_id = p_num_org_id

) ail

WHERE 1 = 1

AND ail.invoice_id = aia.invoice_id

AND ail.status = 'Available'

AND aia.vendor_id = ail.vendor_id

AND aia.vendor_site_id = ail.vendor_site_id

AND upper(aia.invoice_type_lookup_code) = upper('Prepayment');

EXCEPTION

                     WHEN OTHERS

                     THEN

                        NULL;

END;

IF l_payment_count >0 THEN

fnd_message.set_name('XXFIT','BAP_AP_PAY_PREPAYMENTS_MSG');

l_msg := fnd_message.get;

RETURN l_msg;

END IF;

END IF;

  RETURN 'N';

END;