Tag Archives: SLA

How to debug accounting issues in EBS and Fusion Application?

Oracle ERP applications, EBS and Fusion application, use subledger accounting architecture to create accounting for transactions and accounting events.

When you create accounting online from AP invoice, AR invoice, payment, receipt, etc., you may meet some problems that the accounting entries(subledger journals) are not able to created. It suggests you run create accounting concurrent program and see detailed errors. Generally, this is because some validations are not passed or some cross-application problems. For example, AP prepayment application for multiple times, etc.

At this moment, you should run the program and read the output file and log file to locate the problem. And you may need to validate the AAD (Application accounting definition) for your application and resolve the problems indicate in output file. Generally the problem can be resolved.

To validate ADD, you need to go to subledger accounting setup->subledger accounting setup->accounting methods builder->method and definitions->application accounting definition to validate your ADD by event class or run a concurrent program called “Validate Application Accounting Definitions” and select ledger and application in parameter.

To resolve other errors indicate in create accounting output file. It is case by case. For example, below error message

“The subledger journal entry does not balance by balancing segment and there is no Intracompany Balancing Rule defined for the ledger Vision India Operations, source Payables and journal category Purchase Invoices. The ledger option Intracompany Balancing is enabled, but no balancing rules are defined for the ledger. Please update the journal entry setups for the application accounting definition so that the journal entry balances by balancing segment, or define a cross-entity balancing rule for the ledger.”

It means the accounting entries generated for the transaction are not balanced. It means, in AP transaction, Dr. side accounting is from distribution line and Cr. Side accounting is from supplier liability account setup. They have different company segment (balance segment), so they are not balanced. You must need to change either distribution balance segment or supplier liability balance segment to make them consistent.

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.

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) 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.