Loading

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 · 1

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 · 0

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 · 0