Sunday, August 17, 2014

API to account the AP Payments in oracle apps R12 AP_DRILLDOWN_PUB_PKG. PAYMENT_ONLINE_ACCOUNTING














In my recent project, we have a requirement to account the payment of the AP Invoices via PLSQL. 
Hence, We have used the API
 AP_DRILLDOWN_PUB_PKG.PAYMENT_ONLINE_ACCOUNTING

Script:

----------------------------------------------------------------------
  -- PROCEDURE account_payments 
  -- This procedure is to create Accounting for payments of the 
  -- invoices in AP
  ---------------------------------------------------------------------- 
  PROCEDURE account_payments (pin_invoice_id    IN  NUMBER,
                              pov_err_message OUT VARCHAR2
                              )  
  IS
    CURSOR cur_imp_invoices IS
    SELECT DISTINCT aca.check_id, aca.check_number        
      FROM ap_invoices_all xlpi,
           ap_invoice_payments_all aipa,
           ap_checks_all aca
     WHERE xlpi.invoice_id   = aipa.invoice_id
    AND xlpi.invoice_id   = pin_invoice_id
       AND aipa.check_id     = aca.check_id
       AND ap_checks_pkg.get_posting_status(aca.check_id) = 'N';  
    -- This condition is to pick invoices which are paid and unaccounted
 
    ln_processed_cnt      NUMBER DEFAULT 0;
    ln_failed_cnt         NUMBER DEFAULT 0;  
    lv_error_buf          VARCHAR2(500);
    ln_retcode            NUMBER; 
  BEGIN
 
    FOR rec_imp_inv IN cur_imp_invoices
    LOOP
      BEGIN
        DBMS_OUTPUT.put_line(rec_imp_inv.check_id);
        ln_retcode   := NULL;
        lv_error_buf := NULL;
 
        ap_drilldown_pub_pkg.payment_online_accounting 
           (
             p_check_id           => rec_imp_inv.check_id,
             p_accounting_mode    => 'F',        
             p_errbuf             => lv_error_buf,
             p_retcode            => ln_retcode,
             p_calling_sequence   => 'xxyour_package.account_payments'
           );
 
        IF ln_retcode = 0 
        THEN
          DBMS_OUTPUT.put_line('Invoice Check id: '
                  ||rec_imp_inv.check_id 
    ||'. Payment ('
    ||rec_imp_inv.check_number
    ||') Accounted Sucessfully'
        );
          ln_processed_cnt := ln_processed_cnt+1;
        ELSIF ln_retcode = 1 THEN
          DBMS_OUTPUT.put_line('Invoice Check id: '
                 ||rec_imp_inv.check_id
          ||'. Payment ('
          ||rec_imp_inv.check_number
          ||') Accounting ended in WARNING. Errbuf : '
          ||lv_error_buf
         );
          ln_processed_cnt := ln_processed_cnt+1;
        ELSIF ln_retcode = 2 THEN
          DBMS_OUTPUT.put_line('Invoice Check id: '
                  ||rec_imp_inv.check_id 
    ||'. Payment ('
    ||rec_imp_inv.check_number
    ||') Accounting ended in ERROR. Errbuf : '
    ||lv_error_buf
    );       
          ln_failed_cnt := ln_failed_cnt +1;          
        ELSE
          DBMS_OUTPUT.put_line('Invoice Check id: '
                  ||rec_imp_inv.check_id 
    ||'. Payment ('
    ||rec_imp_inv.check_number
    ||') Accounting ended in ERROR. Errbuf : '
    ||lv_error_buf
    ||' Retcode: '
    ||ln_retcode
    );
          ln_failed_cnt := ln_failed_cnt +1;
        END IF; 
 
        COMMIT;
 
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.put_line('Invoice Check id: ' 
                               ||rec_imp_inv.check_id
                               ||'. Payment Accounting failed with unhandled exception.Error:' 
                               ||SQLERRM);
          ln_failed_cnt := ln_failed_cnt + 1;
      END;
    END LOOP;
    pov_err_message := 'PROCESSED: '||ln_processed_cnt||' FAILED: '||ln_failed_cnt;
  END account_payments; 
 
Hope this helps!!!
 
 

2 Responses to “API to account the AP Payments in oracle apps R12 AP_DRILLDOWN_PUB_PKG. PAYMENT_ONLINE_ACCOUNTING”

Unknown said...
September 17, 2014 at 3:20 AM This comment has been removed by the author.

Unknown said...
September 17, 2014 at 3:24 AM

Hey :) .. I got a site too
Oracle Apps Today
Please refer this in additional resources


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.