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

 

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.

    Prepayment Receipt Setup and Process

    Setup:

    1. Define system parameters in order management setup
    “Allow Multiple Payments” as “Y”

    2.  Define receipt class and payment method and assign document sequence (Optional)

    3. Define payment term with “Prepayment” option enabled.

    4. Define receivable activity with “Prepayment” type

    Process:

    1. Create sales order and book it.

    2. Go to Actions -> Payment, create prepayment. A prepayment type receipt will be created in AR.
    Dr. Cash/Bank
    Cr. Prepayment (Actually a liability account)

    3. Release, pick confirm, ship confirm sales order and run autoinvoice and prepayment match program in AR.  AR invoice is auto created and applied with prepayment.
    Dr. Receivables
    Cr. Revenue
    Dr. Prepayment
    Cr. Receivables

    Receipt at Risk

    From Oracle user guide, it is said that

    “In Receivables, you can apply a receipt to an open debit item before cash is actually received from the bank. Therefore, receipts with a Standard remittance method are considered receipts at risk if they have been confirmed, but not yet cleared. Receipts with a Factored remittance method are at risk if they have not yet been risk-eliminated.”

    But when I only select  “At Risk” option as “Y” in receipt summary form, not only above two kinds of receipts are displayed. Some receipt with state of “Remitted” and with receipt class “not remittance” are also displayed. It seems that “All not cleared receipts are considered as “At Risk”“. Is it right?

    In the last query, it shows

    “SELECT …. FROM AR_CASH_RECEIPTS_V WHERE (AT_RISK=’Y’) order by RECEIPT_NUMBER”

    Then I view the view definition of AR_CASH_RECEIPTS_V, the most important section is as

    “…

    DECODE ( NVL(CR.CONFIRMED_FLAG,’Y’), ‘Y’, DECODE( CR.REVERSAL_DATE, NULL, DECODE( CRH_CURRENT.STATUS, ‘REVERSED’,’N’, DECODE( CRH_CURRENT.FACTOR_FLAG, ‘Y’, DECODE( CRH_CURRENT.STATUS, ‘RISK_ELIMINATED’, ‘N’, ‘Y’), DECODE(CRH_CURRENT.STATUS,’CLEARED’, ‘N’,’Y’))), ‘N’), ‘N’) AT_RISK ,

    …”

    By the way, I refer the section of user guide to introduce the concept of “Include Receipts at Risk in Customer Balance”.

    ———————————————————————————————————————————————————–

    “In Receivables, you can apply a receipt to an open debit item before cash is actually received from the bank. Therefore, receipts with a Standard remittance method are considered receipts at risk if they have been confirmed, but not yet cleared. Receipts with a Factored remittance method are at risk if they have not yet been risk-eliminated.

    You can view the number and amount of receipts at risk and their effect on your customer’s open balance in the Receipts Summary window.

    To include receipts at risk and receipts that were created by the Bills Receivables Remittance or Maturity and Risk program, select Yes in the appropriate areas in the Find Receipts window.

    To display receipts at risk and include them when calculating a customer’s past due balance in the Account Details window, set the AR: Include Receipts at Risk in Customer Balance profile option to Yes. This profile option affects the Account Details window.

    If this profile option is set to No, you can choose to include items at risk by performing the following:

    1. Choose the Include Receipts at Risk option from the Tools menu.
    2. Execute your query.

     Important: The AR: Include Receipts at Risk in Customer Balance profile option and the option on the Tools menu do not affect the customer balance calculation in any Receivables standard reports or listings. These options only affect whether Receivables displays receipts at risk and includes them in the open balance calculation for Account Details.”

    AR Deposit Setup and Process

    Setup:

    1. Define a transaction type for deposit with “Deposit” transaction class.

     

     

     

     

     

     

     

     

    2. Define a receipt class and payment method to receive deposit transaction. (Optional)

    Process:

    1. Create AR transaction with deposit transaction type and complete it.
    Dr. Receivables
    Cr. Offset Account (Deposit Account, a kind of liability account)

    2. Create Receipt for deposit.
    Dr.  Cash/Bank
    Cr. Receivables

    3. AR invoice created automatically or manually
    Dr.  Receivables
    Cr. Revenue

    4. Apply Deposit with AR invoice
    Dr.  Offset Account
    Cr. Receivables

    Below is the screen dump to deposit application:

     

    Prepayment Receipt vs. Deposit in Oracle Receivables

    Oracle receivables have a “deposit” transaction class and meantime have a “prepayment” receipt type. From business view, it looks the same, but why does Oracle designed “duplicated”? What’s the difference between them? I think it for a long time, and get some points to share with our friends here. Hopefully it is helpful for your implementation and support.

    1. When deposit or prepayment created in AR, the actual AR invoice for goods or services have not been generated. This is normal business process for concept of deposit/prepayment. Deposit is created in Oracle Receivables independently for a customer. It is not able to be associated or called matched directly with any prior business documents before AR invoice, like sales order in OM. But Prepayment type receipt is generated from feeder module(like OM), it is associated with sales order in sales order additional information.

    2. One AR invoice can apply with only one deposit, but one ar invoice can apply multiple prepayment type receipts. It is also means that one sales order can be prepaid for multiple times before ship confirm. This is very useful for multiple times prepayment/deposit.

    3. Payment term flexibility: deposit can have its own payment term, while sales order/AR invoice can have different payment term. It is very flexible for customer balance control. As prepayment is a receipt type and it is auto generated from sales order,  it is must be immediate.  And sales order must have a payment term which is prepayment enable.

    At last, I would like to say the offset account for both deposit and prepayment should be set up as an actual liability account.  Some customers use on-account receipt for prepayment business before. It is not recommended, as on-account account is always set up as an actual asset account.

     

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