Two Issues to be Considered When Implementing Multi Period Accounting (MPA)

1. Seems that drilldown functionality doesn’t work very well in GL journal entries from MPA. When clicking “Line Drilldown” button in GL journal entry form, below page pop up without SLA journal information.

image

2. MPA only generates the multi journal entries for defined periods in the ledger’s accounting calendar setup. If the periods are not defined enough, the remaining expense/revenue/recovery tax will be allocated to the last defined calendar. This is the standard functionality of MPA following the generic EBS accounting policy.

Preaccounting and Postaccounting Hook

“Create Accounting” program creates SLA journal entries for events. As we ever mentioned in the post before, we can add some logic in preaccounting hook or postaccounting hook to do something before or after accounting.  If you want to do something in preaccounting or postaccounting, you must know which events will/have be processed in the accounting program. How to get these event or transaction id?

Generally, you can get it from “p_report_request_id”. The request id will be updated to xla_events table and the request id will be shipped to hooks as a parameter. So you can get the event or transaction by below code in accounting hook.

  SELECT……
  FROM   xla.xla_events xee,
                xla.xla_transaction_entities  xte,
                xla.xla_ae_headers xah,
                xla.xla_ae_lines xal
  WHERE  xee.application_id = XXX
  AND       xee.event_id = xte.event_id
  AND       xee.event_id=xah.event_id
  AND       xah.ae_header_id=xal.ae_header_id
  AND       ……         –any other condition you want to add
  AND    XEE.request_id = p_report_request_id;

Have a Look at DB Policy from xla_transaction_entities

Today, I need to get an AP inovice id from an SLA event_id. From xla_events table, I got the entity_id for the event. And then I directly query xla_transaction_entities table with entity_id, but nothing can be gotten.

It is very strange. Under the same schema apps, why there is this entity_id in xla_events table, but not exists in xla_transaction_entities table? xla_events and xla_transaction_entities are both the synonym in apps schema from xla schema!!! They’re the views!!!

In R12, oracle introduce a new concept – DB Policy, which will be assign to table and table’s synonym. It is used to apply MOAC security to table and synonym. (Maybe my express is not very suitable here. I’m not a technical person. I can only do some simple queries微笑

Let’s have a look at below two sql query.

image

No DB Policy assigned to XLA.XLA_TRANSACTION_ENTITIES

image

Several DB policies are assigned to XLA_TRANSACTION_ENTITIES synonym in apps schema. That’s why I can’t query out my event entity from apps schema in normaly way.

Why can we get the event from xla_events? Let’s have a look at xla_events’s db policy.

image

There’s no db policy on xla_event synonym in apps.

OK. Finally, after understanding the mechanism,  I can get my event entity by adding xla. pre-fix now.

image

Multi Period Accounting (MPA) 4 – Comparison & Extension

Comparison:

MPA in SLA is too limited. It can only recogonize the prepaid expense or defered revenue evenly. And the recogonition schedule is also very limited. So, the revenue recogonition functionality can’t use this SLA feature to fulfill their requirement. You can see “Accounting Rule” setup is more flexible than MPA setup.

MPA in SLA are generating multiple period journal entries under one event (m:1), while revenue recogonition are generating journal entries under multiple events (1:1).

MPA in SLA complete the journal entries by “Complete Multi Period Journal Entries” program, while revenue recogonition complete the journal entries by “Create Accounting”.

Personally I still prefer the way of Receivables’s revenue recogonition.

 

Extension:

1. In FAH (Financial Accounting Hub) context, as you can create your own event model, you can select the way of Receivables’s revenue recogonition to create multiple event for multiple period and assigne event source to these event. The event source can be calculated freely by program code, so the shedule is free and amount (proporate percentage) is also can be calculated by flexible configuration.

2. In SLA implementation context, you can not create your own event model, you can only use the existing event, e.g. AP invoice validation, but you want to get a flexible recogonition schedule and recogonition percentage. You have two way to try to do:

  • Use create accounting hook. There are seversal kind of create accounting hook. Here, we can use “Preaccounting” and “Postaccounting”hook. Before accounting, we can change the “MultiPeriod Start Date” and “MultiPeriod End Date”to a proper value to get the same installment number of your flexible recogonition schedule, so the create accounting will generate this number of recogonition installments. After accounting, in postaccounting hook, you can programmly change the GL date and line amount against your flexible recogonition schedule setup, then optionally change back the “MultiPeriod Start Date” and “MultiPeriod End Date”. For other process, it is as the same as standard functionality.
  • In above solution, we can make it more easier. We don’t need to change “MultiPeriod Start Date” and “MultiPeriod End Date”and change it back. As these two attributes are finally go into event sources. We can change the event sources directly by revising the event source “view”. In the view, we directly make it to get the same installment number of flexible recogonition schedule. In postaccounting hook, you can programmly change the GL date and line amount against your flexible recogonition schedule setup. By this way, user is more transparent.
  • As the multi period accounting entries are generated during “Create Accounting”, copy the create accounting program and find out the part of multi period journal entry generation, change the schedule and amount calculation logic. Don’t touch the status control part, just change the GL date and amount calculation.

Multi Period Accounting (MPA) 3 – SLA MPA Feature

From Oracle R12.0, subledger accounting (SLA) is introduced to Oracle E-Business Suite, which is a event-based accounting generation engine. From R12.1, MPA feature is introduced to SLA architecture. It enables users to create accounting for a single accounting event for more than one GL period.  Taking an example for prepaid expense, when AP invoice validated, an accounting event “Invoice Validation” will be created, by create accounting, the multiple period accounting journals will created for this event. As one SLA accounting journal can have only one GL date, so the relationship between event and SLA journal is 1:m.

The multiple period accounting process is depicted as below diagram.

image

  1. Setup
  1. Define Accounting Derivation Rule (ADR): This is used to get the journal entry account for recogonition entries. (Dr. Expense, Cr. Prepaid expense)
  2. Define Journal Entry Type (JLT):
    • Accural journal entry type
    • Recogonition journal entry type
  3. From journal entry type perspective, the total accounting entries should be like this
    Dr. Accrual journal entry type  (first period)
         Cr. Liability  (first period)
    Dr. Recogonition journal entry type  (first period)
         Cr. Accrual journal entry type (first period)
    Dr. Recogonition journal entry type (second period)
         Cr. Accrual journal entry type (second period)
    …….
  4. Define Journal Line Definition (JLD) and assign Accrual journal entry type to JLD and set up accrual jlt’s ADR;
  5. Attach Recogonition journal entry type to Accrual journal entry type and set up multi period accounting options and recogonition JLT’s ADR;
  6. Define Application Accounting Definition(AAD) and Subledger Accounting Method (SLAM)
  7. Assign SLAM to ledger
  • Create an AP invoice. In AP invoice line assign multiple accounting options.
    image
    The three options in red circle will be mapping to accounting attribute in SLA to create multiple accounitng. The two options in blue circle doesn’t make sense currently.
  • Name Data Type Journal Entry Level Accounting Method Builder Components Accounting Attribute Assignment Rules Optional or Mandatory Validation Rules
    Multiperiod Option Alphanumeric Line Event class Should be assigned if another multiperiod accounting attribute is assigned Optional Should be Y for yes or N for no
    Multiperiod Start Date Date Line Event class Should be assigned if another multiperiod accounting attribute is assigned Mandatory if multiperiod option is populated  
    Multiperiod End Date Date Line Event class Should be assigned if another multiperiod accounting attribute is assigned Mandatory if multiperiod period type is not populated  

    After invoice created and validated, the AP invoice distribution has still only two lines. The recogonition accounting entries will not be inserted to AP invoice.
    image

  • After AP invoice validation, the AP invoice validation accounting event will be generated.
    image
  • After AP invoice accounting, the event’s status becomes “Final Accounted”, even though not all of the journal entries under the event are finally accounted.
  • After AP invoice accounting, the multiple journal entries are generated for multiple periods.
    image
    For opened AP period, the status is Final;
    For unopened AP period, the status is Incompleted;

    Note that it depends on AP period status.
    If the accounting calendar has not been defined for the period, the journal entry will not be generated, the remaining balance will be recogonized in the last defined period.
    Another very important point is the relationship between the first accrual journal entry and repeated recogonition journal entries. In xla_ae_headers, accrual journal entry is the parent journal entry of recogonition journal entries.
    Please also understand that all of journal entries are generated by “Create Accounting”. It is very important for us to extent the MPA functionality.
  • When next AP period is opened, the user can run the concurrent program “Complete Multi Period Journal Entries” to make the journal entry status from “Incompleted” to “Final”. Please note that it also depends on AP period status.
    The program has outout to tell you how many and what MPA journal entries are handled.
    Below is the a section of complete MPA journal entry program code. It shows how MPA journal entries are controled. (Package Name: XLA_MULTIPERIOD_ACCOUNTING_PKG)
    image
    After the concurrent program is run, the journal entry will be finally processed and can be transferred to GL.
  • Multi Period Accounting (MPA) 2 – Revenue Recognition

    From Oracle official concept, revenue recognition is renue recognition. It belongs to AR. Multi Period Accounting is the concept of SLA. But in fact, they are the similar thing. One of  initial purpose of MPA is for revenue recognition, but it is not flexible enough for revenue recognition. So revenue recognition is still handled in Receivables itself.

    Let’s have a look at revenue recognition first then compare it with multi period accounting feature in SLA.

    1. Create AR transaction and assign Invoice Rule in AR transaction header. There are two options for Invoice Rule:

    • In Advance: Receiving money at first before revenue recogonition
    • In Arrears: Receiving money at last after revenue recogonition

    2. Assign accounting rules in AR transaction line. Accounting Rule means the revenue recogonition schedule. The revenue recognition program/API calcualte the recogonition schedule against accounting rule. Only if invoice rule is assigned in header, accounting rule can be

    image

    3. Once AR transaction is assigned with invoice rule and accounting rule, the original revenue account distribution will be changed to defered revenue account. It can be set up in AutoAccounting functionality. And receivable account distribution will be changed agasint invoice rule. If “In Arrears” is selected, the recevable account will be defaulted against the autoaccouting setup for Unbilled Receivable.

    4. Complete the AR invoice.

    5. Create Accounting with Final option.

    6. Revenue Recognition: You have two options to recogonize revenue.

    • by concurrent program “Revenue Recognition Master Program”
    • by Revenue Accounting form. In the form, you can manage revenue and sales credits more flexible than concurrent program. You can schedule or unschedule the revenue recogonition and you can also partailly recogonize the revenue.

    image

    7. Whatever you select to recogonize the revenue by concurrent program or form, the functionality will create series of AR transaction distributions with multiple period. And for each period, it create accounting event with type of “Invocie Update”. So by revenue recogonition, multiple events will be created. It’s one of the difference between MPA and Revenue Recogonition.

    image

    Please note that, for normal transaction entry, you can’t key in unopened GL date. But for program inserting, it can.

    8. Create Accounting againt for the new created events and distribution lines. The SLA journal entries are created for all of the periods even though the period is not opened.

    • For the opened GL period and future entry statsu GL period, the journal entry status is “Final”and can be transfered to GL.
    • For un-opened GL period, the journal entry status is “Invalid”and event status is unprocessed.
    • It is very strange that it depends on GL period but not Receivable period.
    EVENT_ID APPLICATION_ID EVENT_TYPE_CODE EVENT_DATE ENTITY_ID EVENT_STATUS_CODE PROCESS_STATUS_CODE
    3362490 222 INV_UPDATE 23-2月 -13 3295913 U I
    3362489 222 INV_CREATE 23-1月 -13 3295913 P P

     

    AE_HEADER_ID APPLICATION_ID LEDGER_ID ENTITY_ID EVENT_ID EVENT_TYPE_CODE ACCOUNTING_DATE GL_TRANSFER_STATUS_CODE GL_TRANSFER_DATE JE_CATEGORY_NAME ACCOUNTING_ENTRY_STATUS_CODE ACCOUNTING_ENTRY_TYPE_CODE AMB_CONTEXT_CODE PRODUCT_RULE_TYPE_CODE
    5003040 222 1 3295913 3362489 INV_CREATE 23-1月 -13 N   Sales Invoices F STANDARD DEFAULT S
    5002952 222 1 3295913 3362490 INV_UPDATE 23-2月 -13 N   Sales Invoices R STANDARD DEFAULT S

     

    image

     

    image

     

    image

     

    image

    9. When you open the next period and run create accounting again, the journal entry status will changed from Invalid to Final and transferred to GL module.

    By summery, in revenue recogonition solution, Oracle insert the transaction ditribution line with multiple periods, create events for each period and create journal entries for each period. For unopened period, Oracle make its journal entries as “Invalid” until the period opened.

    Multi Period Accounting (MPA) 1 – Introduction

    What is Multi Period Accounting

    I want to explain it from business perspective first. Supposed that there is a service type company, they have received whole money from their customer, but they have not provided the whole service finally. The whole service will be provided within 12 months from current month. Against accrual basis concept, the company can’t recognize revenue for the whole money amount to current month, even though they received whole money in current month. They need to defer the revenue to the following 12 months. The accounting entries should be like below

    Dr. Receivables 1200 (current month)
    Cr. Deferred Revenue 1200 (current month)
    Dr. Deferred Revenue 200 (current month)
    Cr. Revenue 200 (current month)
    Dr. Deferred Revenue 200 (next month)
    Cr. Revenue 200 (next month)
    ……
    Dr. Deferred Revenue 200 (the last month)
    Cr. Revenue 200 (the last month)

    As similar as revenue recognition, prepaid expense also has the similar accounting behavior like above, for example, the company pays the rent bill for the whole year at the beginning of the year.

    Without multi period accounting feature, customer can make the AR transaction or AP invoice with the distribution of deferred revenue/expense account at the first period, and then move it to revenue/expense account in each following period through GL journals. By this way, customer may forget to take this action during period ending process, because this is the manual work. (Maybe recurred journal template can help on this something)

    From R12, Oracle provided multiple period accounting feature to allow customer create accounting for multi period together for a single accounting event, even the period may have not been opened. For example, in AP, when AP invoice validated and accounted, all of the accounting entries are created for the whole 12 periods.

    Multi Period Accounting in Oracle E-Business Suite

    The functionality is primarily used to recognize revenue like above example or a prepaid expense. But the feature still has a lot of limitations, so Oracle Receivables developed its own revenue recognition functionality. They don’t use the MPA feature provided by Oracle Subledger Accounting. As they are doing the similar thing, I will introduce both of them and compare them in a series of blog post.

    • Revenue Recognition
    • Multi Period Accounting Setup in SLA