Auto Approval of Invoice Batch

The Auto Approval of Invoice Batch in AP Program achieves the functionality to validate and approve invoices created in a batch through Payables Open Interface program.

Oracle provides facility only to validate the invoices in a batch. It doesn’t provide any standard Program or public API to approve invoices in a batch automatically. The seeded functionality is to approve each invoice manually which is a cumbersome task for a large industry implementing ERP. 

The Auto Approval of Invoice Batch in AP Program has a vital importance because of its functionality to auto approve invoices in a batch which is required for any further processing of invoices.

To validate and approve the invoices in a batch the program uses Oracle provided program and procedures to eliminate any type of malfunctioning. While achieving this goal some good features provided by Oracle have been customized.

The large no. of invoices are created through open interface program and it’s almost impossible to manually approve each invoice of the batch as it’s time consuming, a costlier affair and additional burden on the business. As invoices are validated properly while importing through payables open interface, business wants the AP invoices has to be also imported in APPROVED status also.

Oracle doesn’t provide any automated facility to achieve the above requirement. Rather it provides facility to approve each invoice manually.

This document describes the details of this program which deals with the above requirement of validating and approving AP invoices that are created in a batch. To achieve this functionality the program calls the Oracle’s “Invoice validation” program which validates the invoices created in a given batch and then few procedures provided by Oracle to approve the validated Invoices. The used procedures,

                      (

                           1.  AP_WFAPPROVAL_PKG.insert_history

                           2.  AP_INVOICES_PKG.invoice_pre_update

                           3.  AP_INVOICES_PKG.update_row

                           4.  AP_INVOICES_PKG.invoice_post_update

                           5.  AP_INVOICES_PKG.post_forms_commit

                       )

are called and required parameters are passed to approve the validated invoice batch.

The parameters detail of these procedures is discussed in Customization of Oracle Procedures section.

Invoice Batch Approval

Ø  Once invoice batch is validated successfully the invoice approval program gets submitted and it approves the validated invoice batch.

Ø  To approve the invoices the pre-requisite is that these invoices should be validated which is achieved by submitting “Invoice Validation” program with proper parameters.

Ø  Once the Invoices are validated the following procedures are called one by one in series to approve the validated invoice batch:-

Take all relevant information for the invoices in a cursor for the batch and then pass them as parameters to the various procedures contained in the loop:-

i) As a first step we call the procedure to create history.

AP_WFAPPROVAL_PKG.insert_history
                  (
                     p_invoice_id => v_nettingapprove_rec.invoice_i  

                     /* Pass the invoice id of the invoice to be approved */

                   , p_iteration     => 0                                                                                                      
                   , p_org_id        => g_org_id

                     /* Pass the org_id for your operating unit */
                   , p_status         => 'MANUALLY APPROVED'
                  );

ii) As a second step towards invoice approval the invoice_pre_update procedure is called   with following set of parameters:-

AP_INVOICES_PKG.invoice_pre_update

                (
                       X_invoice_id                      =>  v_nettingapprove_rec.invoice_id
                      ,X_invoice_amount             => NULL
                      ,X_payment_status_flag     => v_payment_status_flag
                      ,X_invoice_type_lookup_code   => NULL
                      ,X_last_updated_by            => g_last_updated_by
                      ,X_accts_pay_ccid              => NULL
                      ,X_terms_id                        => NULL
                      ,X_terms_date                    => NULL
                      ,X_discount_amount          => NULL
                      ,X_exchange_rate_type      => NULL
                      ,X_exchange_date              => NULL
                      ,X_exchange_rate               => NULL
                      ,X_vendor_id                     => NULL
                      ,X_payment_method_lookup_code => NULL
                      ,X_message1                      => v_message1
                      ,X_message2                      => v_message2
                      ,X_reset_match_status       => v_match_status
                      ,X_vendor_changed_flag   => v_vendor_changed_flag
                      ,X_recalc_pay_sched         => v_recalc_pay_sched
                      ,X_liability_adjusted_flag => v_liability_adjusted_flag
                      ,X_calling_sequence          => 'APXINWKB'
                );

iii)            Next step is to call update_row procedure with all the columns of ap_invoices_all passed as parameter. Please pass atleast those columns whose value you have populated to generate invoices.

                                    ap_invoices_pkg.Update_Row
            (
             X_Rowid                       => v_nettingapprove_rec.row_id
            ,X_Invoice_Id                => 

                                                                         v_nettingapprove_rec.invoice_id                                            

                                                ,X_Last_Update_Date            => SYSDATE
            ,X_Last_Updated_By             => g_last_updated_by
            ,X_Vendor_Id                   =>        

                                                        v_nettingapprove_rec.vendor_id

                                                ,X_Invoice_Num                 =>

                                                        v_nettingapprove_rec.invoice_num

                                                ,X_Invoice_Amount              =>   

                                                       v_nettingapprove_rec.invoice_amount
            ,X_Vendor_Site_Id              =>

                                                       v_nettingapprove_rec.vendor_site_id

                                                ,X_Amount_Paid                 => NULL
            ,X_Discount_Amount_Taken       => NULL
            ,X_Invoice_Date                =>

                                                      v_nettingapprove_rec.invoice_date

                                               ,X_Source                      =>

                                                      v_nettingapprove_rec.SOURCE

                                                ,X_Invoice_Type_Lookup_Code    => v_nettingapprove_rec.invoice_type_lookup_code
            ,X_Description                 => NULL
            ,X_Batch_Id                    => v_nettingapprove_rec.batch_id
            ,X_Amt_Applicable_To_Discount  => v_nettingapprove_rec.amount_applicable_to_discount
            ,X_Tax_Amount                  => NULL
            ,X_Terms_Id                    =>

                                                        v_nettingapprove_rec.terms_id

                                                ,X_Terms_Date                  =>   

                                                        v_nettingapprove_rec.terms_date

                                                ,X_Payment_Method_Lookup_Code  =>  v_nettingapprove_rec.payment_method_lookup_code
            ,X_Goods_Received_Date         => NULL
            ,X_Invoice_Received_Date       => NULL
            ,X_Voucher_Num                 => NULL
            ,X_Approved_Amount             => NULL
            ,X_Approval_Status             => 'APPROVED'
            ,X_Approval_Description        => NULL
            ,X_Pay_Group_Lookup_Code       => NULL
            ,X_Set_Of_Books_Id             =>        

                                                       v_nettingapprove_rec.set_of_books_id
            ,X_Accts_Pay_CCId              =>                                                                              v_nettingapprove_rec.accts_pay_code_combination_id    

                                                ,X_Recurring_Payment_Id        => NULL
            ,X_Invoice_Currency_Code       =>

                                                         v_nettingapprove_rec.invoice_currency_code
            ,X_Payment_Currency_Code       =>

                                                         v_nettingapprove_rec.payment_currency_code

                                                ,X_Exchange_Rate               => NULL
            ,X_Invoice_Distribution_Total  => NULL
            ,X_Payment_Amount_Total        => NULL
            ,X_Payment_Status_Flag         => 'N'
            ,X_Posting_Status              => NULL
            ,X_Authorized_By               => NULL                                               

                                                ,X_Attribute_Category          =>      v_nettingapprove_rec.attribute_category
            ,X_Attribute1                  => NULL                                                        
            ,X_Attribute2                  =>    

                                                         v_nettingapprove_rec.expense_dff                                                                    

                                                ,X_Attribute3                  =>      

                                                         v_nettingapprove_rec.orig_invoice_number

                                                ,X_Attribute4                   => NULL
            ,X_Attribute5                  => NULL
            ,X_Vendor_Prepay_Amount        => NULL
            ,X_Prepay_Flag                 => NULL
            ,X_Base_Amount                 => NULL
            ,X_Exchange_Rate_Type          => NULL
            ,X_Exchange_Date               => NULL
            ,X_Payment_Cross_Rate          => 

                                                              v_nettingapprove_rec.payment_cross_rate   

                                                ,X_Payment_Cross_Rate_Type     => NULL
            ,X_Payment_Cross_Rate_Date     => 

                                                              v_nettingapprove_rec.payment_cross_rate_date

                                               ,X_Pay_Curr_Invoice_Amount     =>

                                                               v_nettingapprove_rec.pay_curr_invoice_amount

                                                ,X_Vat_Code                    => NULL
            ,X_Last_Update_Login           => g_last_update_login
            ,X_Original_Prepayment_Amount  => NULL
            ,X_Earliest_Settlement_Date    => NULL
            ,X_Attribute11                 => NULL
            ,X_Attribute12                 => NULL
            ,X_Attribute13                 => NULL
            ,X_Attribute14                 => NULL
            ,X_Attribute6                  => NULL
            ,X_Attribute7                  => NULL
            ,X_Attribute8                  => NULL
            ,X_Attribute9                  => NULL
            ,X_Attribute10                 => NULL
            ,X_Attribute15                 => NULL
            ,X_Cancelled_Date          => NULL
            ,X_Cancelled_By             => NULL
            ,X_Cancelled_Amount    => NULL
            ,X_Temp_Cancelled_Amount    => NULL
            ,X_Exclusive_Payment_Flag      => 'N'
            ,X_Po_Header_Id                       => NULL
            ,X_Ussgl_Transaction_Code      => NULL
            ,X_Ussgl_Trx_Code_Context    => NULL
            ,X_Doc_Sequence_Id                 =>

                                                               v_nettingapprove_rec.doc_sequence_id               

                                               ,X_Doc_Sequence_Value          =>

                                                               v_nettingapprove_rec.doc_sequence_value            

                                               ,X_Doc_Category_Code           =>

                                                               v_nettingapprove_rec.doc_category_           

                                               ,X_Freight_Amount              => NULL
           ,X_Expenditure_Item_Date       => NULL
            ,X_Expenditure_Organization_Id => NULL
            ,X_Expenditure_Type               => NULL
            ,X_Pa_Default_Dist_Ccid        => NULL
            ,X_Pa_Quantity                        => NULL
            ,X_Project_Id                            => NULL
            ,X_Project_Accounting_Context  => NULL
            ,X_Task_Id                       => NULL
            ,X_Awt_Flag                    => NULL
            ,X_Awt_Group_Id            => NULL
            ,X_Reference_1                => NULL
            ,X_Reference_2                => NULL
            ,X_Auto_Tax_Calc_Flag  =>

                                                              v_nettingapprove_rec.auto_tax_calc_flag

                                                ,X_Org_Id                                => g_org_id
            ,X_global_attribute_category   => NULL
            ,X_global_attribute1           => NULL
            ,X_global_attribute2           => NULL
            ,X_global_attribute3           => NULL                                           

                                                ,X_global_attribute4           => NULL
            ,X_global_attribute5           => NULL
            ,X_global_attribute6           => NULL
            ,X_global_attribute7           => NULL
            ,X_global_attribute8           => NULL
            ,X_global_attribute9           => NULL
            ,X_global_attribute10          => NULL
            ,X_global_attribute11          => NULL
            ,X_global_attribute12          => NULL
            ,X_global_attribute13          => NULL
            ,X_global_attribute14          => NULL
            ,X_global_attribute15          => NULL
            ,X_global_attribute16          => NULL
            ,X_global_attribute17          => NULL
            ,X_global_attribute18          => NULL
            ,X_global_attribute19          => NULL
            ,X_global_attribute20          => NULL
            ,X_calling_sequence           => 'APXINWKB'
            ,X_gl_date                           =>

                                                                  v_nettingapprove_rec.gl_date    
            ,X_Award_Id                       => NULL
            ,X_APPROVAL_ITERATION          => NULL
            ,X_APPROVAL_READY_FLAG     => 'Y'
            ,X_WFAPPROVAL_STATUS          =>  

                                                                   v_wfapproval_status
            ,X_REQUESTER_ID                          => NULL
            ,X_DBI_UPDATED_FLAG               => NULL
           );

iv)   Now we call the package invoice_post_update with the following set of parameters:-

      ap_invoices_pkg.invoice_post_update
                     (
                         X_invoice_id          =>   

                                          v_nettingapprove_rec.invoice_id

                        ,X_payment_priority         => 1
                        ,X_recalc_pay_sched        => v_recalc_pay_sched
                        ,X_Hold_count                 => v_Hold_count
                        ,X_update_base                => NULL
                        ,X_vendor_changed_flag => NULL
                        ,X_calling_sequence        => v_calling_sequence
                     );

                         v)    Now as the final step we call the procedure post_forms_commit with                  

  following parameters:-

       ap_invoices_pkg.post_forms_commit
             (
               X_invoice_id                  => v_nettingapprove_rec.invoice_id
              ,X_type_1099                   => NULL
              ,X_income_tax_region           => NULL
              ,X_vendor_changed_flag       => v_vendor_changed_flag
              ,X_update_base                    => v_update_base
              ,X_reset_match_status         => v_match_status
              ,X_update_occurred             => v_update_occurred
              ,X_approval_status_lookup_code =>   

                                    v_approval_status_lookup_code  

              ,X_holds_count                          => v_holds_count
              ,X_posting_flag                          => v_posting_flag
              ,X_amount_paid                         => v_amount_paid
              ,X_highest_dist_line_num       => v_highest_dist_line_num
              ,X_distribution_total                => v_distribution_total
              ,X_actual_invoice_count         => v_actual_invoice_count            

              ,X_actual_invoice_total           => v_actual_invoice_total            

              ,X_calling_sequence            => v_calling_sequence
              ,X_update_awt_flag             => NULL
             );