Friday, September 6, 2013

AR API to change Receipt from Unidentified to Unapplied

Recently I had a requirement to move unapplied AR receipts from one customer to another customer and apply to the related customer invoice.
I did not find any API to update the customer details of an unapplied receipt, nasty way to update the customer on unapplied receipt is to update related base tables (as_cash_receipts_all and ar_payment_schedules_all), I won't suggest to go with this.

There is an API to move unidentified receipt (Receipt without customer details) to unapplied receipts.
So solution offered was to run data loader for unapplied receipts to remove customers to make those to unidentified receipts and then use API ar_receipt_update_api_pub.update_receipt_unid_to_unapp to make it unapplied on required customer and then using API apps.ar_receipt_api_pub.apply, apply that receipt to related customer invocie.

Please find the sample script for API 'ar_receipt_update_api_pub.update_receipt_unid_to_unapp'

---------------------------------------------------------

declare
   cursor cur_unid_rcpts
   is
    select acr.org_id
          ,acr.receipt_number
          ,hca.cust_account_id
          ,acr.cash_receipt_id
     from ar_cash_receipts_all acr
         ,hz_cust_accounts hca
    where acr.status = 'UNID'
      -- Account number stored in attribute3
      and trim(leading 0 from acr.attribute3 )= hca.account_number
      and acr.type = 'CASH'
      and acr.receipt_number = 'AKTest'--<<ReceiptNumber>>
      order by acr.receipt_number;
         
   x_return_status               varchar2 (200);
   x_msg_count                   number;
   x_msg_data                    varchar2 (200);
   x_status                      varchar2 (200);
   p_count                       number;

begin

   for rec_unid_rcpts in cur_unid_rcpts loop
  
       -- Setting up the org context
       apps.mo_global.init ('AR');
       mo_global.set_policy_context('S' , rec_unid_rcpts.org_id) ;
        
       ar_receipt_update_api_pub.update_receipt_unid_to_unapp
              (p_api_version                    => 1.0,
               p_init_msg_list                  => apps.fnd_api.g_true,
               p_commit                         => apps.fnd_api.g_false,
               p_validation_level               => apps.fnd_api.g_valid_level_full,
               x_return_status                  => x_return_status,
               x_msg_count                      => x_msg_count,
               x_msg_data                       => x_msg_data,
               p_cash_receipt_id                => rec_unid_rcpts.cash_receipt_id,
               p_pay_from_customer          => rec_unid_rcpts.cust_account_id,
               x_status                              => x_status
              );
       dbms_output.put_line ('API Status = ' || x_return_status);
       dbms_output.put_line ('Receipt Status = ' || x_status);

       if x_msg_count = 1 then
          dbms_output.put_line ('API Error Msg: ' || x_msg_data);
       elsif x_msg_count > 1 then
      
          for i in 1 .. x_msg_count
          loop
             x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
             dbms_output.put_line ('Error Message_' || i || ': ' || x_msg_data);
          end loop;
       else
          -- Commit the changes
          commit;  
       end if;
   end loop;  
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm); 
end;
---------------------------------------------------------

5 comments:

  1. Hi Abhay Kumar,

    I was reproduced your code for change Receipt from Unidentified to Unapplied but I get this message:

    X_MSG_COUNT = 1
    X_MSG_DATA = Location could not be defaulted for the specified customer.
    Receipt Status = X_STATUS =
    l_msg_data Location could not be defaulted for the specified customer.

    Wold you help me?

    Any idea?

    Tks in avanced,
    Regards,
    Jorge Aparicio (japaricio_lopez@hotmail.com)

    ReplyDelete
    Replies
    1. HI Jorge,

      It looks like the flag 'Require Billing Location for Receipts' for the customer you are using is yes but does not have primary bill to site.
      Check the 'Require Billing Location for Receipts' flag for the customer you are using.
      Receivables > Setup > System > System Options >> Miscellaneous tab.

      I hope this will help.

      Regards
      Abhay

      Delete
    2. Hi Abhay,

      First of all, thank you very much for your help.

      Indeed, 'Require Billing Location for Receipts' was 'YES'

      I changed the api and it worked, but not very well.

      Now I have another problem.

      The API does not generate this information on the screen when inquiring receipt, Customer Name, Customer Number, Location.

      Normally, if I change the status of the receipt manually, by entering the number of the customer in full automatic the other boxes, Customer Name and Location.

      any suggest pls.

      Tks in advantage.

      Delete
  2. Hi Abhay,

    First of all, thank you very much for your help.

    Indeed, 'Require Billing Location for Receipts' was 'YES'

    I changed it and the API worked, but not very well.

    Now I have another problem.

    The API does not generate this information on the screen when inquiring receipt, Customer Name, Customer Number, Location.

    Normally, if I change the status of the receipt manually, by entering the number of the customer in full automatic the other boxes, Customer Name and Location.

    Any idea?

    Tanks in avantage.

    ReplyDelete
    Replies
    1. Hi Jorge,

      Customer details should appear on screen. I hope you are enquiring the receipt number in the correct operating unit. You can have same receipt number in different operating units.

      Delete