Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Receivables
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 
When working on 11.5.8 for one of my previous clients in Year 2002, I was tasked to build a lockbox interface.

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


Anil Passi

Comments   

0 #1 Rajesh 2006-11-28 00:00
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.

Regard s,
Rajesh
Quote
0 #2 Rajesh 2006-11-28 00:00
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.

Regard s,
Rajesh
Quote
0 #3 murali 2007-01-30 00:00
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,
Mu rali
Quote
0 #4 Karthi 2007-03-30 00:00
Excellent Informations. I appreciate Your honest and real informations. Good Work.
Quote
0 #5 Ramachandran 2007-04-05 00:00
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...


Re gards,
Ram
+9 71 50 7840842
Quote
0 #6 Ranjeet 2007-06-21 00:00
That a Great knowledge Info ,Hats Off..
Quote
0 #7 Nagesh .Athe 2007-07-06 10:39
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
Quote
0 #8 Ashish Sharma 2007-09-20 22:18
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?
Quote
0 #9 purab 2007-10-01 11:00
Hi Anil,
I am using oracle apps 11.5.10.2.
When ever 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
V ishalaksha
Quote
0 #10 abdulqayyum 2007-10-30 07:39
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
Quote
0 #11 kalyan kumar g 2008-02-21 06:00
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.
Messag e3 ---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
Regards
Kalyan
Quote
0 #12 udayachandrakumar kota 2008-03-19 09:28
Anil,

Amazing Article... A Job Well Done.

Regards,

Kasi, Vijayakumar
Quote
0 #13 Dennis Choo 2008-06-22 23:01
Hi Anil,

I am trying to invoke this API and i have got this error....

ORA- 01086: savepoint 'ACTIVITY_APP_P VT' never established
ORA -06512: at "APPS.AR_RECEIP T_API_PUB", line 7279

Some how this is a no commit or rollback error.

I have tried to call this API in a bulk processing environment.
When i call and pass in single transaction it works fine. But if i pass in as bulk it will always retrun this error.

This is the parameter i pass in -->

AR_RECE IPT_API_PUB.ACT IVITY_APPLICATI ON
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => X_DUMMY_CHAR,
P_COMMIT => X_DUMMY_CHAR,
P_VALIDATION_LE VEL => X_DUMMY_NUM,
X_RETURN_STATUS => X_STATUS,
X_MSG_COUNT => X_COUNT,
X_MSG_DATA => X_DATA,
P_RECEIPT_NUMBE R => RECEIPT_REC.REC EIPT_NUMBER,
P_APPLIED_PAYME NT_SCHEDULE_ID => X_PMT_SDL,
P_RECEIVABLES_T RX_ID => X_REC_TRX,
P_APPLICATION_R EF_TYPE => X_DUMMY_CHAR,
P_APPLICATION_R EF_ID => X_DUMMY_NUM,
P_APPLICATION_R EF_NUM => X_DUMMY_NUM,
P_SECONDARY_APP LICATION_REF_ID => X_DUMMY_NUM,
P_RECEIVABLE_AP PLICATION_ID => X_DUMMY_NUM,
P_PAYMENT_METHO D_CODE => X_RFD_MTD,
P_ORG_ID => X_ORG_ID,
P_PAY_GROUP_LOO KUP_CODE => X_PAY_GROUP
);

Really need some help here.
Quote
0 #14 vivek sathe 2009-10-06 12:54
Hello Anil, First please accept congratulations on the newly published book. I am sure technical and functional teams will find this as a good ready reackoner on many puzzles/process es. I am refreshing my BI Publisher awareness using this book.

Can any existing Receivables API be used to do the application of receipts that are already created? How this post creation clean-up scenario be handled? Need your suggestions. Thanks, Vivek
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
        1
  2  3  4  5  6  7  8
  9101112131415
16171819202122
23242526272829
3031     

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner