Client had over 400 bank accounts into which cash could be received.Some of these cash transactions were to be reconciled & accounted from Oracle Receivables. For this purpose "Receipt Handling Process" needed a mechanism to load bank receipts into Oracle AR in realtime. For this requirement, I developed an API based process which made possible the integration of "Receipt Handling Process" with Oracle Receivables. This technique was preferred over lockbox approach. The requirement was quite simple, receipts from a third party system were being dumped into a couple of tables. Those receipts were to be validated and transferred into Oracle Receivables. Doing a bit of research on Metalink I found that AR now has a receipt creation API. Please find the steps below for implementing a Receipt creation API in Oracle AR.

Step 1. Validate the following:-
a. Ensure that exchange Rate exists in the system if the receipt being interfaced is a foreign currency receipt.
b. Validate that the receipt belongs to an Open or Future enterable period in GL.
This check can be done by using below function within a package
   FUNCTION validate_gl_accounting_date (
      p_accounting_date          IN       DATE
     ,p_sob_id                   IN       NUMBER
   )
      RETURN BOOLEAN
   IS
      v_count   NUMBER := 0;
   BEGIN
      SELECT COUNT ( * )
        INTO v_count
        FROM gl_period_statuses gps
       WHERE gps.application_id = g_gl_application_id
         AND gps.set_of_books_id = p_sob_id
         AND gps.closing_status IN ( 'O', 'F' )
         AND p_accounting_date BETWEEN NVL ( gps.start_date, p_accounting_date )
                 AND NVL ( gps.end_date, p_accounting_date );
       
      IF v_count > 0 THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;

   END validate_gl_accounting_date ;
 
Step 2. After successful validation, call the AR Receipt API ar_receipt_api_pub.create_cash. If the receipt is a foreign currency receipt, then parameters p_exchange_rate_type & p_exchange_rate_date must be supplied with a value. You may pass a value of fnd_api.g_false to parameter p_commit , to facilitate rollback if an errored is encountered in any other part of the functionality. If OUT parameter x_return_status is returned with a value of E, it implies error.
IF p_foriegn_currency THEN
      ar_receipt_api_pub.create_cash (
         p_api_version                  => g_api_version
        ,p_init_msg_list                => fnd_api.g_true
        ,p_commit                       => fnd_api.g_false
        ,p_validation_level             => fnd_api.g_valid_level_full
        ,x_return_status                => v_return_status
        ,x_msg_count                    => v_msg_count
        ,x_msg_data                     => v_msg_data
        ,p_currency_code                => p_rhp_receipt.originating_currency
        ,p_amount                       => p_rhp_receipt_routing.routed_amount
        ,p_receipt_number               => g_process_name || '-' ||
                                              p_rhp_receipt.receipt_id ||
                                              '-' ||
                                              p_rhp_receipt_routing.receipt_routing_id
        ,p_receipt_date                 => p_rhp_receipt.receipt_date
        ,p_cr_id                        => v_cr_id
        ,p_receipt_method_name          => g_receipt_method_name
        ,p_customer_number              => p_rhp_receipt_routing.ar_customer_number
        ,p_comments                     => p_rhp_receipt.originating_customer
        ,p_customer_receipt_reference   => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
        ,p_remittance_bank_account_id   => p_rhp_receipt.bank_account_id
        ,p_exchange_rate_type           => xxdhi_util_pkg.g_conversion_type_code
        ,p_exchange_rate_date           => p_rhp_receipt.receipt_date
      );
ELSE
      ar_receipt_api_pub.create_cash (
         p_api_version                  => g_api_version
        ,p_init_msg_list                => fnd_api.g_true
        ,p_commit                       => fnd_api.g_false
        ,p_validation_level             => fnd_api.g_valid_level_full
        ,x_return_status                => v_return_status
        ,x_msg_count                    => v_msg_count
        ,x_msg_data                     => v_msg_data
        ,p_currency_code                => p_rhp_receipt.originating_currency
        ,p_amount                       => p_rhp_receipt_routing.routed_amount
        ,p_receipt_number               => g_process_name || '-' ||
                                              p_rhp_receipt.receipt_id ||
                                              '-' ||
                                              p_rhp_receipt_routing.receipt_routing_id
        ,p_receipt_date                 => p_rhp_receipt.receipt_date
        ,p_cr_id                        => v_cr_id
        ,p_receipt_method_name          => g_receipt_method_name
        ,p_customer_number              => p_rhp_receipt_routing.ar_customer_number
        ,p_comments                     => p_rhp_receipt.originating_customer
        ,p_customer_receipt_reference   => SUBSTR(p_rhp_receipt.sender_to_receiver_info,1,30)
        ,p_remittance_bank_account_id   => p_rhp_receipt.bank_account_id
      );
END IF ;

Step 3. If the requirement is To reverse an existing receipt, then use API ar_receipt_api_pub.REVERSE. Once again, prefer setting the validation level to fnd_api.g_valid_level_full
.
 Following validation must be done at the time of reversal. This is required because Oracle wouldn't let you Reverse an applied receipt straightaway.
  FUNCTION validate_reversal_flag_valid(p_cash_receipt_id OUT INTEGER)
    RETURN BOOLEAN IS
    CURSOR c_check IS
      SELECT cash_receipt_id
            ,status
      FROM   ar_cash_receipts
      WHERE  receipt_number =
             g_process_name || '-' || p_rhp_receipt.receipt_id || '-' ||
             p_rhp_receipt_routing.receipt_routing_id;
    p_check c_check%ROWTYPE;
    no_existing_receipt EXCEPTION;
    receipt_already_applied EXCEPTION;
    receipt_already_reversed EXCEPTION;
  BEGIN
    OPEN c_check;
    FETCH c_check
      INTO p_check;
    CLOSE c_check;
 
    IF p_check.cash_receipt_id IS NULL
    THEN
      RAISE no_existing_receipt;
    END IF;
 
    IF p_check.status = 'APP'
    THEN
      RAISE receipt_already_applied;
    END IF;
 
    IF p_check.status = 'REV'
    THEN
      RAISE receipt_already_reversed;
    END IF;
 
    p_cash_receipt_id := p_check.cash_receipt_id;
    RETURN TRUE;
 
  EXCEPTION
    WHEN no_existing_receipt THEN
      fnd_message.set_name('AR', 'AR_RAPI_CASH_RCPT_ID_INVALID');
      RETURN FALSE;
    WHEN receipt_already_applied THEN
      fnd_message.set_name('AR', 'GENERIC_MESSAGE');
      fnd_message.set_token('GENERIC_TEXT'
                           ,'This receipt has been applied to transaction in Delphi Oracle Receivables.' ||
                            chr(10) ||
                            'Please unapply the receipt before reversing');
      RETURN FALSE;
    WHEN receipt_already_reversed THEN
      fnd_message.set_name('AR', 'GENERIC_MESSAGE');
      fnd_message.set_token('GENERIC_TEXT'
                           ,'This receipt has already been reversed in Delphi Oracle Receivables.');
      RETURN FALSE;
  END validate_reversal_flag_valid;

For any given receipt, the Step 2 & Step 3 is mutually exclusive.

To  refer to the source code  of the  package body, please click on AR Receipts API.pdf

Comments (12)

Subscribe to this comment's feed

...
written by Rajesh , November 28, 2006

Hi Anil,

The articles in your website is excellent.It gives an indepth knowledge to anyone.The presentation of the articles is also excellent.

I am looking forward for some more articles like this.

Regards,
Rajesh

...
written by Rajesh , November 28, 2006

Hi Anil,

The articles in your website is excellent.It gives an indepth knowledge to anyone.The presentation of the articles is also excellent.

I am looking forward for some more articles like this.

Regards,
Rajesh

...
written by murali , January 30, 2007

hi anil,
In receivable i got following error,Please help me..

message: ARGLTP: Error from argopn

When I try to running the Program: General Ledger Transfer Program
The Program Completed with Error - message: ARGLTP: Error from argopn.

Thanks,
Murali

...
written by Karthi , March 30, 2007

Excellent Informations. I appreciate Your honest and real informations. Good Work.

...
written by Ramachandran , April 05, 2007

Dear Anil Passi,
We have problem in developing the Customer AR Statement of account between GL Dates, to tally with AGING 4 or Aging 7 buckets report...

I have done some wonders in applications but this is a great failure for me...

Please help...


Regards,
Ram
+971 50 7840842

...
written by Ranjeet , June 21, 2007

That a Great knowledge Info ,Hats Off..

...
written by Nagesh .Athe , July 06, 2007

hi anil u r articals r very nice we want same interaction in feture also i need help from om discoverer report plz send to this mail nagesh_athe2000@yahoo.com

...
written by Ashish Sharma , September 20, 2007

If there a way to process a negative receipt for a credit memo using Oracle's auto lockbox procedure.

Say we have 6 receipts lines in a file, 5 for invoices which are of $100 each and one for the credit memo -$20. The net receipts are $480.


The standard Oracle functionality will process the 5 invoices and error out the credit memo receipt line since it’s less than $0.

Now, we want to write off the invoices and also the credit memo. Can this be done in Oracle automatically?

...
written by vishalaksha , October 01, 2007

Hi Anil,
I am using oracle apps 11.5.10.2.
Whenever I run customer interface in oracle receivables manager responsibility then after completion of the import from interface tables to base tables there are some fields which are not populated in base tables. I tried to find them from front end of oracle apps but those fields are not populated. Can you suggest me the reason for that.

Thanks
Vishalaksha

Permissions to execute this API
written by abdulqayyum , October 30, 2007

I have quite some applications which post data in AR through this API. I want to create seperate users for each application in order to keep track of their activities instead of using user APPS and sharing its password with everyone.

If anybody can help me out to figure out rights to be granted to a user so that it can execute this API?

Thanks

error while using receipt creation API with exchange rates.
written by kalyan kumar g , February 21, 2008

Hi Anil Passi

I am working in 11.5.9 instance. In this I tried to create a cash receipt using above said api, but I am geting the following errors
Message1 ---Currency code took a precedence over the user currency code.
Message2 ---Exchange rate should not be entered.
Message3 ---Invalid exchange rate type.
Message4 ---Exchange rate date should not be entered.

Can You please suggest me what can I do to use these exchange rate parameters in the API, as I have to handle the creation od receipts for both forign currency and also with local currency.
I request you to please send replay to this mail id kkgrandhi@gmail.com
Regards
Kalyan

Amazing Article........
written by Vijayakumar Kasi , March 19, 2008

Anil,

Amazing Article... A Job Well Done.

Regards,

Kasi, Vijayakumar