Apps To Fusion

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 (14)

Subscribe to this comment's feed

Write comment

Name

Email

Title

Comment

smaller | bigger

Subscribe via email (Registered users only)

Write the displayed characters

Add Comment