Oracle Payables Invoice Interface – Validating Invoice Number

During the Payables Invoice Interface process, one of the validations involve validating the Invoice Number. If the Invoice Number already exists in the system for a given Vendor in a given Operating Unit, then another Invoice cannot be created with the same Invoice Number. The following logic can be used for validation:

DECLARE
   p_inv_number   ap_invoices_all.invoice_num%TYPE;
   p_vendor_id    ap_invoices_all.vendor_id%TYPE;
   p_org_id       ap_invoices_all.org_id%TYPE;
   l_inv_id       ap_invoices_all.invoice_id%TYPE;

   CURSOR cur_inv_num_chk (
      cp_inv_number    ap_invoices_all.invoice_num%TYPE,
      cp_vendor_id     ap_invoices_all.vendor_id%TYPE,
      cp_org_id        ap_invoices_all.org_id%TYPE)
   IS
      SELECT invoice_id
        FROM ap_invoices_all
       WHERE     invoice_number = cp_inv_number
             AND vendor_id = cp_vendor_id
             AND org_id = cp_org_id;
BEGIN
   OPEN cur_inv_num_chk (p_inv_number, p_vendor_id, p_org_id);

   FETCH cur_inv_num_chk INTO l_inv_id;

   IF (cur_inv_num_chk%FOUND)
   THEN
      DBMS_OUTPUT.put_line (
            'Invoice already exists in the system'
         || CHR (10)
         || ' Thus, Invoice Cannot be created with this Invoice Number
                        for this Vendor '
         || p_vendor_id
         || ' in this organization: '
         || p_org_id);
   ELSE
      DBMS_OUTPUT.put_line (
         'Invoice does not exist and can be created using this Invoice Number');
   END IF;

   CLOSE cur_inv_num_chk;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error while validation: ' || SQLERRM);
END;

One Response to “Oracle Payables Invoice Interface – Validating Invoice Number”

Leave a Reply

  • (will not be published)