AR Functional Questions

SOURCE 

There has been a lot of requests to post sample questions for other financials modules also. Here i am posting a small sample of questions on Oracle Receivables.
1. Provide an overview of AR invoice processing.
2. Provide an overview of AR cash receipt processing.
3. How do you adjust receivables?
4. Do you receive deposits and/or prepayments?
5. Explain Customer Creation and Maintenance Process. Provide samples of customer master records with Payment Terms.
6. Do you track and record relationships between customers e.g. franchises, subsidiaries etc.?
7. Do you create reciprocal customer relationships whenever you create a relationship between customers for tracking purposes?
8. Do you have multiple "remit to’s"?How are multiple "remit to’s" determined for each customer?
9. What credit information is tracked for each customer?
10. How do you group your customers for credit limits, credit checks?
11. Do you do miscellaneous invoicing? Please explain with examples.
12. Do you send customers statements? If so please describe the process and highlight any exceptions.
13. How are the revenue, receivable, freight and tax accounts configured for each invoice type?14. Explain Debit Memo & Credit Memo Process.
15. Do you bill customers in a foreign currency?How many invoice types do you have? Provide invoice samples. (Credit Memo, Debit Memo etc.)
16. How do you number your invoices?
17. Where are the invoices printed? How many printers and where are they? Pre-printed forms?
18. How do you calculate your tax charges? What are the rates? Explain Tax Structure.
19. Do you validate the tax rate against a Customer’s Ship To address when preparing invoices? If so please describe how.
20. Do you charge finance charges? If so - how do you charge finance charges?
21. Explain your dunning and Collection Process. Please provide dunning statement formats.
22. Do collectors actively call customers on delinquent items?
23. What sort of buckets do you use for cash requirements forecasting? (i.e. 30, 60, 90...)
24. Do you write off receivable? Is there a specific dollar amount or time frame used by your Company for write-offs?
25. Do you have Chargeback? Explain.
26. Explain Revenue Recognition Process and explain the scenarious.
27. Explain Bills Receivable?
28. Do you associate Revenue to Sales Person.
29. How is Sales Territory Information for tracking purposes derived? (i.e. customer, salesperson etc.)
30. How do your customers generally pay invoices? single check—single invoice/single check—multiple invoices/multiple checks—multiple invoicesProvide list of Banks and Purpose?
31. Explain Payment Process?
32. Are you using Automatic Receipts? Explain?
33. Explain Remittance Process?
34. Explain your Auto Lock Box Proces if you have enabled it?
35. What types of payments are received and input? (i.e. check, cash etc.)
36. Explain Receipt Reconcilliation Process?
37. What reports do you currently generate for Invoicing, Sales and Revenue tracking? Provide samples including frequency and distribution.

Revenue Recognition Conversion Question

Source

Say you have sold service or subscription for entire year in advance. Example is the yearly subscription price I pay in advance for my HBR copy. I pay about 144 USD in advance. What HBR does with this advance payment I make? Basically they had an invoice created on my name for that amount. But since the magazine needs to be delivered 12 months into the future, they have to recognize the revenue appropriately every month for each issue that is delivered. They cannot recognize the revenue in one month for all the144 USD. But I have already paid for it so they apply cash to this invoice thus bringing down the receivables to zero.

Imagine if HBSP was using a legacy system when I was a subscriber and would like to move to Oracle in the middle of my subscription. My subscription is from Jan-09 to Jan-10 and if they are migrating in Aug-09, how will this invoice converted?

In general this invoice is not considered as open because I have already paid for it (usually we convert open invoices only). So the invoice that is converted should have zero outstanding balance but outstanding revenue or in other words deferred or unearned revenue.

Why is this hard? The reason is accounting. Let us take the same example and create accounting entries for the same. In the first month when the invoice is created here is the entry.

Account	GL Date	Cr	Dr
Unearned Revenue  Account	01-Jan-2009	144
Receivables Account	01-Jan-2009		144
Unearned Revenue Account	01-Feb-2009		12
Revenue Account	01-Feb-2009	12
Unearned Revenue Account	01-Mar-2009		12
Revenue Account	01-Mar-2009	12
Unearned Revenue Account	01-Apr-2009		12
Revenue Account	01-Apr-2009	12
Unearned Revenue Account	01-May-2009		12
Revenue Account	01-May-2009	12
Unearned Revenue Account	01-Jun-2009		12
Revenue Account	01-Jun-2009	12
Unearned Revenue Account	01-Jul-2009		12
Revenue Account	01-Jul-2009	12
Unearned Revenue Account	01-Aug-2009		12
Revenue Account	01-Aug-2009	12
Unearned Revenue Account	01-Sep-2009		12
Revenue Account	01-Sep-2009	12
Unearned Revenue Account	01-Oct-2009		12
Revenue Account	01-Oct-2009	12
Unearned Revenue Account	01-Nov-2009		12
Revenue Account	01-Nov-2009	12
Unearned Revenue Account	01-Dec-2009		12
Revenue Account	01-Dec-2009	12
Unearned Revenue Account	01-Jan-2010		12
Revenue Account	01-Jan-2010	12

Since the one issue of the magazine will be delivered in this month they need to recognize 12 USD in the month of Jan-09. Also mind that I am paying this invoice. Accounting entries are:

When cash is applied against the invoice:

Account	GL Date	Cr	Dr
Receivables  Account	01-Jan-2009	144
Cash Account	01-Jan-2009		144

When revenue is recognized:

Account	GL Date	Cr	Dr
Revenue  Account	01-Jan-2009	12
Unearned Revenue  Account	01-Jan-2009		12

And this recognition continues till August in the legacy system. In August when we convert the invoice into Oracle, how to convert this? Should we convert the entire balance of receivables and unearned revenue or only the remaining unearned revenue about should be the amount in the invoice to convert?

In general cases are:

1.     Invoice is completely paid, and unearned amount is greater than receivables amount (receivables is zero)

2.     Invoice is partially paid and remaining receivable balance is less than the remaining unearned amount.

3.     Invoice is partially paid and the open balance on the invoice is more than the unearned amount.

Two important things to keep in mind are: how much should be the invoice value in all these cases? This question is relevant because receivables balance is not the same as the original invoice amount in the legacy system. And the second question is which GL date to use? And we need an accounting rule to make this happen. As invoices in the legacy can have different number of periods to recognize revenue, we define this rule as Variable type? As we schedule the revenue as soon the revenue recognition is run, we do not check the deferred flag in the accounting rule.

You can convert the invoice amount as is from the legacy and then use adjustments to reduce the receivables balance to appropriate number(zero or partial).  As for the revenue use the rule start date as the equal to the transaction date (as it is in the source). If you keep only current period open gl date will be current for all the past revenue (when revenue recognition is run) and future date for the future periods as per the schedule till the end of it. The future revenue will be recognized on that appropriate date.

But again this falls into bigger picture of your migration strategy and depends on how you convert GL balances for these accounts.

Revenue Recognition

Revenue Recognition principle is one of the important principles of Accrual Accounting. According to this principle, revenue must be recognized when

(1) They are realized or realizable and

(2) They are earned

Revenue is realized when products are exchanged for cash or claims to cash (Receivable).

Revenue is realizable when related assets received are readily convertible to cash or claims to cash.

Revenue is earned when the products are delivered or services are performed.

Recognizing the revenue means recording the amount as revenue in the financial statements.

Realization is the process of converting non-cash resources into cash.

In the Revenue Recognition principle, it does not matter when cash is received. (In Cash Basis Accounting, revenue is recognized when cash is received no matter when goods or services are sold).

For revenue to be recognized, both the above conditions must be met. In other words for revenue to be recognized, final delivery must be completed (of goods or services) and there has to be a payment assurance.

Let us have a look at the timing of Revenue Recognition

1) For sale of finished goods (Inventory Items), revenue is recognized at the date of sale (some interpret this as the date of shipping or the date of delivery)

2) For sale of services (e.g. support services), revenue is recognized when the services are performed (delivered)

3) For sale of Asset Items (other than inventory items like finished goods), revenue is recognized at the point of sale (i.e. when the customer is invoiced)

4) For revenue from other activities like rent for using company’s Fixed Assets, revenue is recognized as time passes or as assets are used.

Examples:

1) If a company invoices its customer for 100 units of item ‘A’, and ships (delivers) only 25 units, the company cannot recognize revenue for entire 100 items. It can only recognize revenue equivalent to the number of units delivered (Revenue is earned only when the products are delivered). Similarly, let’s say you pay $120 in advance to company ‘ABC’ for magazine subscription for one full year. The fact that company ‘ABC’ received money for one full year does not mean that they can record the entire amount as Revenue. In-fact the amount received in advance is a Liability to the company because they have to deliver magazines to their customer every month and if they fail to do so, they are liable to refund the amount received in advance. In this scenario, the company will recognize 1/12th of the entire amount every month as earned revenue after they deliver the magazine.

2) Company ‘ZXC’ signs a 3 year support contract with its client for a total amount of 3 million. This amount cannot be recorded as revenue unless the Company provides the support services to the client. Assuming the company is following a monthly calendar accounting period, the company will recognize 1/36th of the entire support contract deal amount every month. (Revenue is recognized when services are performed)

There are few exceptions to the timing of revenue recognition for sale of inventory items. Under normal scenario, revenue is recognizes at the point of sale, however if there are return policies, and if the company cannot reasonably estimate the amount of future returns, the revenue should be recognized only after the expiration of the return policy period.

Revenue Recognition Accounting:

If revenue is not recognized immediately, what is the accounting entry for the Sales Invoice? Let’s have a look

Let’s say, you invoice the Customer in Advance for the annual support contract of $12000. Since, you are invoicing the customer in Advance, you debit your Receivables. But then if you are not crediting the revenue right away, where do you account for the credit side of the accounting entry? You credit, what is called as Deferred Revenue (or Unearned Revenue). Deferred Revenue is actually a liability for the company. (The company is liable to provide the goods or services for which cash is received or will be received in advance). As and when the goods or services are delivered, the Deferred Revenue is reduced (debited) and revenue is recognized.

Accounting when the Invoice is created in Jan

Date	Accounting Class	Debit	Credit	Comments
1-Jan	Receivables	12000		The entire receivables is recognized in advance. How this receivable is collected will depend on the payment terms of the Invoice
1-Jan	Deferred Revenue		12000

End of Jan, Revenue is recognized for 1/12th of the entire amount, because the company has provided one month’s service to its client. To that effect, Deferred Revenue will be reduced and revenue will be recognized

Date	Accounting Class	Debit	Credit	Comments
31-Jan	Deferred Revenue	1000		Deferred Revenue reduced
31-Jan	Earned Revenue		1000	Earned Revenue amount for one month

End of Feb, another months revenue is recognized

Date	Accounting Class	Debit	Credit	Comments
28-Feb	Deferred Revenue	1000		Deferred Revenue reduced
28-Feb	Earned Revenue		1000	Earned Revenue amount for one month

The company will have similar accounting entry each month till Dec. At the end of Dec, the Deferred Revenue will be Zero and the entire amount will be reported as Revenue earned.








Rules for Revenue recognition ( instead of using Accounting rules )

SOURCE

API approach

This is most flexible approach. You can find sample script of the ar_revenueadjust_pub API here and here. In this example I am deferring revenue for all the amount of the invoice for 12 months equally. If you are creating invoices all through the day (not in batch mode), you can create a subscription to a standard oracle business event oracle.apps.ar.transaction.Invoice.complete (as shown in this API). If it is in the batch mode, you can create a concurrent program and run that after invoices are created or imported but before accounting is created in Subledger Accounting to transfer to GL.

DECLARE
   x_return_status   VARCHAR2 (1);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (4000);
   x_adj_id          NUMBER;
   x_adj_number      VARCHAR2 (100);
   v_msg_index_out number;
BEGIN
   mo_global.set_org_context (204, NULL, 'AR');
   fnd_global.apps_initialize (1005902, 50559, 222);
   xx_inv_complete.defer_revenue (523432, 807271, 1500, x_return_status, x_msg_count, x_msg_data, x_adj_id, x_adj_number);
    DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
      DBMS_OUTPUT.put_line ('Msg Count is :' || x_msg_count);
      DBMS_OUTPUT.put_line ('Adjustment ID is :' || x_adj_id);
      DBMS_OUTPUT.put_line ('Adjustment Number is :' || x_adj_number);
   IF x_msg_count > 0
      THEN
         FOR v_index IN 1 .. x_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
            DBMS_OUTPUT.put_line (x_msg_data);
            DBMS_OUTPUT.put_line ('============================================================');
         END LOOP;
      END IF;
END;

CREATE OR REPLACE PACKAGE BODY xx_inv_complete
AS
   FUNCTION inv_complete (p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t)
      RETURN VARCHAR2
   IS
      CURSOR get_trx_lines (p_cust_trx_id IN NUMBER)
      IS
         SELECT customer_trx_line_id, extended_amount
           FROM ra_customer_trx_lines rci
          WHERE customer_trx_id = p_cust_trx_id;

      i                       INTEGER;
      l_key                   VARCHAR2 (240)                             := p_event.geteventkey ();
      l_payment_schedule_id   NUMBER (15);
      l_customer_trx_id       NUMBER;
      l_org_id                NUMBER;
      l_user_id               NUMBER;
      l_resp_id               NUMBER;
      l_application_id        NUMBER;
      l_security_gr_id        NUMBER;
      l_counter               NUMBER;
      l_history_exists_flag   BOOLEAN;
      l_ps_exists             BOOLEAN;
      l_history_rec           ar_trx_summary_hist%ROWTYPE;
      l_tot_inv_amt           NUMBER;
      l_inv_inst_count        NUMBER;
      l_rev_adj_rec           ar_revenue_adjustment_pvt.rev_adj_rec_type;
      v_msg_index_out         NUMBER;
      v_message               VARCHAR2 (4000);
      x_return_status         VARCHAR2 (1);
      x_msg_count             NUMBER;
      x_msg_data              VARCHAR2 (4000);
      x_adj_id                NUMBER;
      x_adj_number            VARCHAR2 (100);
      j                       NUMBER;
      l_amount                NUMBER;
      l_per                   NUMBER;
      l_gl_date               DATE;
   BEGIN
      l_customer_trx_id := p_event.getvalueforparameter ('CUSTOMER_TRX_ID');
      l_org_id := p_event.getvalueforparameter ('ORG_ID');
      l_user_id := p_event.getvalueforparameter ('USER_ID');
      l_resp_id := p_event.getvalueforparameter ('RESP_ID');
      l_application_id := p_event.getvalueforparameter ('RESP_APPL_ID');
      l_security_gr_id := p_event.getvalueforparameter ('SECURITY_GROUP_ID');
      SAVEPOINT inv_complete_event;
      fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);

      FOR i IN get_trx_lines (l_customer_trx_id)
      LOOP
         defer_revenue (l_customer_trx_id
                      , i.customer_trx_line_id
                      , i.extended_amount
                      , x_return_status
                      , x_msg_count
                      , x_msg_data
                      , x_adj_id
                      , x_adj_number
                       );
      END LOOP;

      RETURN 'SUCCESS';
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK TO inv_complete_event;
         fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
         fnd_message.set_token ('GENERIC_TEXT', SQLERRM);
         fnd_msg_pub.ADD;
         wf_core.CONTEXT ('AR_BUS_EVENT_SUB_PVT'
                        , 'INV_COMPLETE'
                        , 'oracle.apps.ar.transaction.Invoice.complete'
                        , p_subscription_guid
                         );
         RETURN 'ERROR';
   END inv_complete;

   PROCEDURE defer_revenue (
      p_customer_trx_id        IN              NUMBER
    , p_customer_trx_line_id   IN              NUMBER
    , p_ext_amount             IN              NUMBER
    , x_return_status          OUT NOCOPY      VARCHAR2
    , x_msg_count              OUT NOCOPY      NUMBER
    , x_msg_data               OUT NOCOPY      VARCHAR2
    , x_adj_id                 OUT NOCOPY      NUMBER
    , x_adj_number             OUT NOCOPY      VARCHAR2
   )
   IS
      l_rev_adj_rec     ar_revenue_adjustment_pvt.rev_adj_rec_type;
      v_msg_index_out   NUMBER;
      v_message         VARCHAR2 (4000);
      j        CONSTANT NUMBER                                     := 12;
      l_amount          NUMBER;
      l_per             NUMBER;
      l_gl_date         DATE;
   BEGIN
      l_amount := (p_ext_amount / j);
      l_per := (l_amount / p_ext_amount);

      FOR i IN 0 .. j
      LOOP
         l_gl_date := SYSDATE + (i * 30);
         l_rev_adj_rec.customer_trx_id := p_customer_trx_id;
         l_rev_adj_rec.adjustment_type := 'UN';
         l_rev_adj_rec.amount_mode := 'A';
         l_rev_adj_rec.amount := l_amount;
         l_rev_adj_rec.PERCENT := l_per;
         l_rev_adj_rec.line_selection_mode := 'S';
         l_rev_adj_rec.from_cust_trx_line_id := p_customer_trx_line_id;
         l_rev_adj_rec.reason_code := 'RA';
         l_rev_adj_rec.comments := 'TEST';
         l_rev_adj_rec.gl_date := l_gl_date;
         ar_revenueadjust_pub.unearn_revenue (p_api_version            => 2.0
                                            , x_return_status          => x_return_status
                                            , x_msg_count              => x_msg_count
                                            , x_msg_data               => x_msg_data
                                            , p_rev_adj_rec            => l_rev_adj_rec
                                            , x_adjustment_id          => x_adj_id
                                            , x_adjustment_number      => x_adj_number
                                             );
         DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
         DBMS_OUTPUT.put_line ('Msg Count is :' || x_msg_count);
         DBMS_OUTPUT.put_line ('Adjustment ID is :' || x_adj_id);
         DBMS_OUTPUT.put_line ('Adjustment Number is :' || x_adj_number);

         IF x_msg_count > 0
         THEN
            FOR v_index IN 1 .. x_msg_count
            LOOP
               fnd_msg_pub.get (p_msg_index          => v_index
                              , p_encoded            => 'F'
                              , p_data               => x_msg_data
                              , p_msg_index_out      => v_msg_index_out
                               );
               v_message := SUBSTR (x_msg_data, 1, 200);
               DBMS_OUTPUT.put_line (x_msg_data);
               DBMS_OUTPUT.put_line ('============================================================');
            END LOOP;

            DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
            DBMS_OUTPUT.put_line ('============================================================');
         END IF;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;
         fnd_message.set_name ('AR', 'GENERIC_MESSAGE');
         fnd_message.set_token ('GENERIC_TEXT', SQLERRM);
         fnd_msg_pub.ADD;
   END defer_revenue;
END xx_inv_complete;
/