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!!!
 
 

Sunday, August 17, 2014 by Team search · 2

API to account an AP invoice in Oracle Apps R12 AP_DRILLDOWN_PUB_PKG. INVOICE_ONLINE_ACCOUNTING















In my recent project, we have a requirement to account the AP Invoices via PLSQL. 
Hence, We have used the API AP_DRILLDOWN_PUB_PKG.INVOICE_ONLINE_ACCOUNTING
Script:

  ----------------------------------------------------------------------
  -- PROCEDURE account_imported_invoices 
  -- This procedure is to create Accounting for imported invoices in AP
  ---------------------------------------------------------------------- 
  PROCEDURE account_imported_invoices(pin_invoice_id   IN  NUMBER,
                                      pov_err_message OUT VARCHAR2
                                      )  
  IS
    CURSOR cur_imp_invoices IS
    SELECT *
      FROM ap_invoices_all xlpi
     WHERE xlpi.invoice_id  = pin_invoice_id
       AND ap_invoices_pkg.get_posting_status(xlpi.invoice_id) = 'N'; 
       -- This condition is to pick invoices which are un accounted
 
    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
 
        ln_retcode   := NULL;
        lv_error_buf := NULL;
 
        ap_drilldown_pub_pkg.invoice_online_accounting 
           (
             p_invoice_id         => rec_imp_inv.invoice_id,
             p_accounting_mode    => 'F',        
             p_errbuf             => lv_error_buf,
             p_retcode            => ln_retcode,
             p_calling_sequence   => 'XXyour_package.account_imported_invoices'
           );
 
        IF ln_retcode = 0 
        THEN
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num 
                               ||'- Invoice Accounted Sucessfully' 
                               );
          ln_processed_cnt := ln_processed_cnt+1;
        ELSIF ln_retcode = 1 THEN
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num  
                              ||'- Invoice Accounting ended in WARNING. Errbuf : '
                              ||lv_error_buf
                              );
          ln_processed_cnt := ln_processed_cnt+1;
        ELSIF ln_retcode = 2 THEN
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num 
                               ||'- Invoice Accounting ended in ERROR. Errbuf : '
                               ||lv_error_buf 
                              );       
          ln_failed_cnt := ln_failed_cnt +1;          
        ELSE
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num 
                               ||'- Invoice 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(rec_imp_inv.invoice_num
                              ||' Invoice Validation 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_imported_invoices; 
 
 

by Team search · 0

API to initiate the validation of AP invoice (AP_APPROVAL_PKG.BATCH_APPROVAL)

   
  
  
  
  
  
  
  
  
  
  
    

In my recent project, we have a requirement to initiate the validation of the AP invoice via PLSQL.  We have used the private API AP_APPROVAL_PKG.BATCH_APPROVAL

Script:
  
 ----------------------------------------------------------------------
  -- PROCEDURE validate_imported_invoices 
  -- This procedure is to validate imported invoices in AP
  ---------------------------------------------------------------------- 
  PROCEDURE validate_imported_invoices(pin_group_id    IN  NUMBER,
                                       pov_err_message OUT VARCHAR2
                                      )  
  IS
    CURSOR cur_imp_invoices IS
    SELECT ai.invoice_id ap_invoice_id,
           ai.approval_iteration
      FROM ap_invoices_all ai
     WHERE ap_invoices_pkg.get_approval_status(ai.invoice_id,
                                               ai.invoice_amount,
                                               ai.payment_status_flag,
                                               ai.invoice_type_lookup_code
                                              ) NOT IN ( 'APPROVED','UNPAID');  
  -- This condition is pickup invoices eligible for validation only
 
    ln_processed_cnt      NUMBER DEFAULT 0;
    ln_failed_cnt         NUMBER DEFAULT 0;
    ln_holds_cnt          NUMBER;
    lv_approval_status    VARCHAR2(100);
    lv_funds_return_code  VARCHAR2(100);    
 
  BEGIN
 
    FOR rec_imp_inv IN cur_imp_invoices
    LOOP
      BEGIN
 
        lv_approval_status    := NULL;
        lv_funds_return_code  := NULL;
        ln_holds_cnt          := NULL;
 
        IF (AP_APPROVAL_PKG.BATCH_APPROVAL
                (p_run_option           => NULL,
                 p_sob_id               => 1234,
                 p_inv_start_date       => NULL,
                 p_inv_end_date         => NULL,
                 p_inv_batch_id         => NULL,
                 p_vendor_id            => NULL,
                 p_pay_group            => NULL,
                 p_invoice_id           => rec_imp_inv.invoice_id,
                 p_entered_by           => NULL,
                 p_debug_switch         => 'N',
                 p_conc_request_id      => fnd_profile.VALUE('CONC_REQUEST_ID'),
                 p_commit_size          => NULL,
                 p_org_id               => 123,
                 p_report_holds_count   => ln_holds_cnt,
                 p_transaction_num      => NULL))   
       THEN
        DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
                      ||' Invoice Validated.Status(holds count) : '                   
                      ||ln_holds_cnt);
       ELSE
         DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
                      ||' Invoice Validation Failed ');
       END IF;          
 
        ln_processed_cnt := ln_processed_cnt+1;
 
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.put_line(rec_imp_inv.invoice_num
    ||' Invoice Validation 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; 

by Team search · 0

API to check the AP Invoice Accounting Status AP_INVOICES_PKG.GET_POSTING_STATUS















In my recent project, we have a requirement to display the invoice Accounted status in a custom form. We have used the API AP_INVOICES_PKG.GET_POSTING_STATUS 

Script:

  -------------------------------------------------------------------------
  -- Function get_inv_accounted_status
  -- This function returns the accounting status of the given ap invoice id  
  -------------------------------------------------------------------------   
  FUNCTION get_inv_accounted_status (pin_invoice_id IN NUMBER)
  RETURN VARCHAR2
  IS
  lv_return_status  VARCHAR2(2) DEFAULT NULL;
  BEGIN  
    RETURN (ap_invoices_pkg.get_posting_status(pin_invoice_id));  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
   RETURN 'N';
 WHEN OTHERS THEN
   RETURN 'N';
  END get_inv_accounted_status; 
 

by Team search · 1

Sunday, August 10, 2014

API to Add a Fixed Asset in Oracle Apps R12 without Source info - FA_ADDITION_PUB.DO_ADDITION
















In this post, i tried to create a Fixed asset via standard Oracle API FA_ADDITION_PUB.DO_ADDITION. I tested the script in Oracle Apps R12. Hope this helps. 

Script:

set serveroutput on;
DECLARE
   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;

   l_return_status            VARCHAR2(1);     
   l_mesg_count               number;
   l_mesg                     varchar2(4000);
BEGIN

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;  

   -- desc info
 --  l_asset_desc_rec.asset_number                 := '1234567';
   l_asset_desc_rec.tag_number                   := 'TEAM12345-1';
   l_asset_desc_rec.serial_number                := 'TEAM3567-1';
   l_asset_desc_rec.in_use_flag                  := 'YES';
   l_asset_desc_rec.new_used                     := 'NEW';
   l_asset_desc_rec.owned_leased                 := 'OWNED';
   l_asset_desc_rec.current_units                := 1;
   l_asset_desc_rec.description                  := 'Shareoracleapps Test Asset';
   l_asset_desc_rec.asset_key_ccid               := 1;

   -- cat info 
   -- Valid Value in FA_CATEGORIES
   l_asset_cat_rec.category_id                   := '332'; 

   --type info
   l_asset_type_rec.asset_type                   := 'CAPITALIZED';

   -- Asset Financial Information --
   l_asset_fin_rec.set_of_books_id               := 2243;    
   l_asset_fin_rec.date_placed_in_service        := TO_DATE('01-JUN-2014','DD-MON-RRRR');
   l_asset_fin_rec.deprn_start_date              := TO_DATE('01-JUN-2014','DD-MON-RRRR');
   l_asset_fin_rec.deprn_method_code             := 'STL';
   l_asset_fin_rec.life_in_months                := 240;
   l_asset_fin_rec.original_cost                 := 50000;
   l_asset_fin_rec.cost                          := 50000;
   l_asset_fin_rec.prorate_convention_code       := 'SAME MONTH'; 
   l_asset_fin_rec.salvage_type                  := 'AMT'; -- PCT - for Percentage
   l_asset_fin_rec.salvage_value                 := 1000;
   l_asset_fin_rec.percent_salvage_value         := NULL;
   l_asset_fin_rec.depreciate_flag               := 'YES';
   l_asset_fin_rec.orig_deprn_start_date         := TO_DATE('01-AUG-2010','DD-MON-RRRR');

   -- deprn info
   l_asset_deprn_rec.set_of_books_id             := 2243;    
   l_asset_deprn_rec.ytd_deprn                   := 20000;
   l_asset_deprn_rec.deprn_reserve               := 20000;
   l_asset_deprn_rec.bonus_ytd_deprn             := 0;
   l_asset_deprn_rec.bonus_deprn_reserve         := 0;

   -- book / trans info
   -- Valid value in FA_BOOK_CONTROLS
   l_asset_hdr_rec.book_type_code                := 'SHARE BOOK';

   -- distribution info
   l_asset_dist_rec.units_assigned               := 1;
   -- Valid Record from GL Code cominations with record type = 'E' (Expense)
   l_asset_dist_rec.expense_ccid                 := 12345; 
   -- Valid Value in FA Locations   
   l_asset_dist_rec.location_ccid                := 1881098; 
   l_asset_dist_rec.assigned_to                  := NULL;
   l_asset_dist_rec.transaction_units            := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                           := l_asset_dist_rec;

   -- call the api 
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then
      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);
      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);
         dbms_output.put_line(l_mesg);
      end loop;
      fnd_msg_pub.delete_msg();
   end if;

   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
     dbms_output.put_line('ASSET_ID :' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER :' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sunday, August 10, 2014 by Team search · 3

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.