3 Replies Latest reply on Feb 3, 2012 6:23 PM by 777243

    Invoice Validation status check in Invoice base tables - urgent Pls..

    Sen2008
      All,
      Version:11.5.10.2

      After the invoice is validated in Invoice workbench (AP), where the validation status got stored. I came across ap_invoices_v table and AP_INVOICES_PKG.GET_APPROVAL_STATUS package can be used to check the status. But I've seen in ap_invoice_distributions_all table, match_status_update flag gets updated with flag 'A' after validation of invoice.
      I want to know which option(view,package or ap_invoice_distributions_all table) Oracle recommends to check the status of invoice.

      rgds
      sen
        • 1. Re: Invoice Validation status check in Invoice base tables - urgent Pls..
          Tarun_Sharma
          Hi:

          Pl Check APPROVAL_STATUS_LOOKUP_CODE in AP_INVOICES_ALL table to check the invoice validation status. You have to set the ORG_ID first.

          Tarun
          • 2. Re: Invoice Validation status check in Invoice base tables - urgent Pls..
            Sen2008
            Hi Tarun,

            For my scenario, I'm forced to use the match_status_flag option. So i wanted to know can I go ahead using this flag to check the status of invoice?

            thanks,
            sen
            • 3. Re: Invoice Validation status check in Invoice base tables - urgent Pls..
              777243
              Metalink note 301806.1 tells how the validation status is generated (it is not a field in a table for sure) which is different from approval_status.

              I have taken the note and came up with this function to determine it and works well so far.

              FUNCTION getvalstatus(p_invoice_id IN NUMBER) return VARCHAR2
              IS
              p_dist_count NUMBER;
              p_dist_valid_count NUMBER;
              p_dist_nevvald_count NUMBER;
              p_dist_needs_revald NUMBER;
              p_hold_count number;

              p_status varchar2(25);

              BEGIN

              SELECT count(*) INTO p_dist_count FROM
              ap_invoice_distributions_all WHERE invoice_id=p_invoice_id;

              SELECT count(*) INTO p_dist_valid_count FROM
              ap_invoice_distributions_all WHERE invoice_id=p_invoice_id
              and match_status_flag='A' ;

              SELECT count(*) INTO p_dist_nevvald_count FROM
              ap_invoice_distributions_all WHERE invoice_id=p_invoice_id
              AND (match_status_flag='N' OR match_status_flag IS NULL);

              SELECT count(*) INTO p_hold_count FROM ap_holds_all
              WHERE invoice_id=p_invoice_id
              and release_lookup_code is null;

              IF (p_dist_count=p_dist_valid_count and p_hold_count=0 and p_dist_count <>0) THEN
              p_status:='Validated';
              ELSIF
              p_dist_count=p_dist_nevvald_count THEN
              p_status:='Never Validated';
              ELSIF
              p_hold_count >0 THEN
              p_status:='On Hold';
              else
              p_status:='Needs Revalidation';
              end if;



              RETURN p_status;
              end getvalstatus;