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.

Oracle Taiwan Localizations

Oracle Financials provides standard localization features for Taiwan region,which includes

One function:

  1. Government Uniform Invoice Number Generation (GUI)
    The feature works together with Oracle e-Business Suite to support Taiwanese three kinds of invoict types and its special invoice number generation. The invoice number is predefined by tax authority for the company bi-monthly which has a prefix and changed every two months. 
    The feature uses standard transaction source to fulfill the requirement. User must set up new transaction source when they get the new pre-defined invoice number from tax authority every two months. Which is not very user-friendly.

Nine Reports:

  1. Taiwanese Input VAT Report
    List input VAT entries from Payables. The report can be submit at different levels, legal entity, operating unit, ledger, etc. as well as below reports.
  2. Taiwanese Payables Sales/Purchase Return and Discount Certificate
    This is the purchase return or discount certificate assigned to supplier for evidence of accounitng and audit purpose.
  3. Taiwanese Purchase Return and Discount Report
    List purchase return and discount with tax entry information.
  4. Taiwanese Output VAT Report
    List input VAT entries from Receivables.
  5. Taiwanese Receivables Government Uniform Invoice Report
    This is an internal report to list all GUI invoice in receivable.
  6. Taiwanese Receivables Zero-Rate Tax Report
    List the tax exemption entries informaiton.
  7. Taiwanese Sales Return and Discount Report
    List sales return and discount with tax entry information.
  8. Taiwanese EDI Government Uniform Invoice
    List all GUI invoice from both Payables and Receivables in tax authority required format. The EDI file will upload to tax authority statutorly. 
  9. Taiwanese Pro Forma 401 Report
    This report provides required information to help customer submit statutory 401 Form. 401 form should be tax return report. It is like below
    Taiwan401

 

Legal Entity (legal_entity_id) in AP invoice header

Oracle default the legal entity (legal_entity_id) based on the balancing segment value of the liability account in AP invoice. The balance segment value assigned to legal entity and optionally operating unit in legal entity configuration pages.

Oracle allow user to enter other legal entity value in “customer taxpayer ID” in AP invioce header to change legal entity party and determine tax.

The legal entity (legal_entity_id) is used to determine tax. In E-Business Tax, the legal entity first party is get from the source product like AP, AR, etc.

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.
  • Usage and purpose of Customer Taxpayer Id

    Per R12 design the operating unit and legal entity do not hold one-to-one relationship anymore, which is in sharp contrast to 11i. Now one operating unit can be attached to many legal entities in a ledger. Under General Ledger (GL) responsibility, Setup : Financials : Accounting Setup Manager, all the legal entities of a primary ledger or secondary ledger can be found.

    Here, customer refers to the Apps deploying agency that is liable to pay a supplier invoice and is accountable to legal authorities for it. So once the “customer taxpayer id” is selected at the header then the system doesn’t default legal entity on the basis of “bill to location” of the selected supplier site and the invoice liability account. The “customer taxpayer id” gives a way to explicitly override the default legal entity derived from supplier site and liability account and default org id. “Customer taxpayer id” is the unique reg. number used to identify an LE. Also “Customer Taxpayer ID” can be used for any type of invoices. The system does not consider it to be mandatory.

    In summary, the customer taxpayer id is used to explicitly override the default legal entity derived from supplier site. On the contrary, the taxpayer id is used to enter tax information about the individual or legal entity, which can be a corporation or a partnership.

    Differences between HR:Security Profile & MO:Security Profile

    HR:Security Profile and MO:Security Profile are used for MOAC (Multi-Org-Access-Control).
    HR:Security Profile is used to restrict the data in Human Resources according to the Business Group or whatever criteria you define in this security profile. Thus, for HR, it uses this secuity profile in its data exposure to the user.
    MO:Security Profile acts the same but is used for Financials and Manufacturing applications. It restricts the access (site level or can be set at responsibility level) to certain operating unit (or whatever criteria defined in the security profile).
    If there is no value at MO:Security Profile, then Financials and Manufacturing use the HR:Security Profile option for their data exposure to users.

    Multi-Org features in Oracle EBS 11i and R12

    Generally, Oracle 11i fulfills multi-org feature by the views in apps schema; while Oracle R12 fulfills the feature by Virtual Private Database feature.

    In 11i,  the base table are created by product schema, e.g. oe_order_headers_all in ont db schema. In apps schema, there’s a view called oe_order_headers, which has a where clause like “org_id = SUBSTRB (USERENV (‘CLIENT_INFO’), 1, 10)” . So every user accessed application has his own userenv, then every user can access his own org’s data.

    In R12, the base table are also created by product schema, e.g. oe_order_haders_all in ont schema. In apps schema, there’s a Synonym oe_order_headers with VPD feature to fulfill the multi-org feature.

    In 11i, we set MO: Operating Unit profile on the responsibility level, then all of user logged from this responsibility has this OU(org_id) client info, so this user can only access this operating unit data from this responsibility.

    In R12, we set up a security profile, which can include several operating units, then we assign this security profile to the profile option “MO: Security Profile” on the responsibility level.  The user logged from this responsibility can access all of OUs’ data  in the security profile.

    Adding a New Operating Unit

    From:  http://www.oracleug.com

    The following steps are required to add new operating units to your organizational structure:
    1.1 Revise the Organization Structure.
    1.2 Define Sets of Books (optional).
    1.3 Define Locations.
    1.4. Define Organizations and Relationships.

    2.1 Define Responsibilities.
    2.2 Set Profile Options for Each Responsibility linked to the new Operating Unit.
    1.1. HR: Business Group
    1.2  HR: Security Option
    1.3: HR: User Type (FOR accessing HRMS functions)
    1.4  HR: Cross Business Group
    2.1. GL: Set of Books(11i)
    2.1  GL:%Ledger%  (R12)
    2.3  GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.
    3.1. MO: Operating Unit
    3.2. MO: Security Profile (R12)
    3.3. MO: Default Operating Unit
    4.1 Tax: Allow Override of Tax Code
    4.2 Tax: Invoice Freight as Revenue
    4.3 Tax: Inventory Item for Freight
    5.1 Sequential Numbering
    5.2 INV: Intercompany Currency Conversion
    2.3 Run the Replicate Seed Data concurrent program.

    3.1 Define Inventory Organization Security.
    3.2 Define Intercompany Relations (optional).
    3.3 Implement the Application Products.

    4.1 Secure Balancing Segment Values (optional).
    4.2 Run the Setup Validation Report (recommended).
    4.3 Implement Document Sequencing (optional).
    4.4 Set the top reporting level (optional).
    4.5 Set up conflict domains (optional).

    Rejection Reason of Expense Report Import

    To import expense report to AP invoice, we need to run Expense Report Import program from AP module. Some expense report may be not able to convert to AP invoice successfully, but fortunately, “Expense Report Import Report” will list all of the expense report transferred to AP invoice successfully and unsuccessfully. In the “Exceptions Report” part, you can also find out the rejection reason for the failed expense report from “Expense Report Import Report”.

    Blow I listed all of the rejection reasons and the explanations from Oracle Document, the user can try to correct system against the reason.

    • Addr Line [1-3] Too Large. Invoice Import attempted to create a supplier from an employee and Address Line 1-3 for the employee home address exceeds 35 characters.
    • City/State/Country] Too Large. Invoice Import attempted to create a supplier from an employee and the City/State/Country for the employee home address exceeds 25 characters.
    • Create As Supplier. You are using manual supplier number entry and you did not enter the employee as a supplier before submitting Invoice Import.
    • Create Duplicate Supplier. You are trying to create an invoice from an expense report for an employee name that is not already a supplier and a supplier name already exists for the same employee name.
    • Duplicate Report. The invoice number already exists for an employee or supplier.
    • Future Date. The expense report date, the accounting date which determines the period in which your general ledger recognizes an invoice expense and liability, is in a closed period and is after the date you entered an expense report.
      If the expense report date is in a closed period but before the date you entered an expense report, Payables creates an invoice from this expense report so it does not appear on the Exceptions Report.
    • No Exchange Rate. The Payables option Require Exchange Rate Entry is Yes and the expense report is in a foreign currency and has no exchange rate information.
    • No Home Address. The expense report is being sent to the employee’s home address, but there is no home address defined for the employee.
    • No Location. You entered an employee as a supplier without entering a location.
    • No Office Address. The expense report is being sent to the employee’s office address, but there is no office address defined for the employee.
    • No Withholding on Site. Supplier site does not have the Allow Withholding Tax option enabled and you tried to import an invoice that a) has an invoice distribution with an automatic withholding tax group assigned to it, b) has an invoice distribution with a line type of Withholding Tax, or c) has a withholding tax group assigned to it.
    • Not A Supplier. The Payables option Automatically Create Employee as Supplier is not enabled, and the employee does not already exist in Payables as a supplier.
    • Zip Code Too Large. Invoice Import attempted to create a supplier from an employee on an expense report and the Postal Code for the employee home address exceeds 20 characters.

    Expense Report Import and Employee-Type Supplier

    Expense report will be imported to AP invoices once you run the request "Expense Report Import". Every AP invoice should be a specified supplier, so if there's no existing supplier in your system, the expense report cannot be imported to AP invoice.

    There's an setup option called "Automatically create supplier during expense report import" in Payables Options – Expense Report tab.

    • If the option is checked, the system will auto create a supplier if it is not existing.
    • If the option is not checked and the supplier master is also not existing, the expense report will be under the exception report.
    • If the option is not checked but the supplier mater is existing while the supplier site is not existing or not called "OFFICE"/"HOME“, a supplier site will be automatically created called "OFFICE"/"HOME", then book the AP invoice on it.

    Oracle Standard Payment Process in EBS 11i

    My drafted Oracle standard Payment Process in EBS 11i –

    Welcome to comment.

    Expense Amortization in Oracle

    You have a big expense which can be amortized to multiple periods against your local accounting principle, e.g. building fixture, etc.

    1. Create a FA(fixed asset) category in your FA book.  This category is similar with real fixed asset category but with 0 salvage value. Depreciation expense is the amortization expense in this case and the life year is your amortization periods.

    2. Create AP invoice with the full figure of your fixture fee. The distribution line is in the account of FA clearing. So the accounting entry is

    Dr. FA clearing
    Cr. Liability

    3. Make FA addition in FA module with the category for expense amortization (or by mass addition from AP).

    Dr. Asset Cost (Expense Asset)
    Cr. FA clearing

    4. Run depreciation every period to amortize the expense.

    Dr. Amortization Expense
    Cr. Asset Cost (Expense Asset)

    Security Rule in Payables Accounting Process

    Sometimes when you try to create accounting for an AP invoice or AP payment, an error message said that you can’t do that and suggests you to run Payables Accounting Process. But after running the suggested request, the accounting entries are still not generated.

    Why? The security rule setup.

    When creating accounting, the system may generate some entries like bank charges, realized/unrealized gain and loss, etc. These accounting entries may not able to be entered or seen by your responsibility against your organization’s security rule/cross validation rule setup. So if you create accounting or run Payables accounting process in a responsibility without required authorities, above error message will be shown.

    How to handle it? We have several options to selection. You can select anyone against your situation.

    1. Wait for a night and to see it tomorrow morning. Generally, Payables Accounting Process will be scheduled to run at night with a top authority in every organization.

    2. Find a authorized responsibility and switch to it and run the process.

    3. Contact system administrator or finance control admin to release the security rule and then run the process.

    How to Set up AP Invoice Voucher Number Sequence by Operating Unit (OU)

    Maybe you have known that, by Oracle default setup, we can’t use the separate sequence for AP invoice voucher number by Operating Unit (OU) while we can only share one sequence for each type of AP invoice in one set of books (SOB). These days, I’m considering this flexibility and find out a way to set up the separate sequence for AP invoice voucher number by specific operating unit (OU) like below.

    1. Mark the check box of “Allow Document Category Override” in payables options, invoice tab

    2. Set up Document Category for the specific OU

    3. Define the sequence for the OU 

    4. Assign the sequence (of step 3) to the category (of step 2)

    5. Make the field “Document Category Name” to mandatory by Form Personalization Feature

    6. To make the system not to auto default the document category, inactive the default category sequence assignment