Before you Begin

This 30-minute hands-on tutorial shows you how to modify attributes for seeded accounts in Financial Consolidation and Close. This enables you to load negative values as normal balances for selected account types. The sections build on each other and should be completed sequentially.

Background

Dimensions are seeded with system-defined members to support the built-in system calculations. Seeded dimension members have the prefix FCCS underscore (FCCS_). You cannot rename or delete seeded members. For most dimensions, you can modify only the Parent Member and Alias attributes for seeded members. In the Account dimension, however, you can modify in addition the Account Type, Consolidation Operator, Variance Reporting, and Exchange Rate Type attributes.

The Financial Consolidation and Close seeded account hierarchy expect that normal balance data for all account types is represented by positive values. For Asset and Expense accounts, positive values represent a debit balance and negative values a credit balance. For Revenue, Liability, and Equity Accounts, positive values represent a credit balance and negative numbers a debit balance. Your source data, however, may represent the normal balance for expenses, liability, or equity accounts as negative values. Because the seeded account members in the system-defined balance sheet hierarchy are configured to expect the normal balance to be a positive number, you must modify the Account Type and Consolidation Operator properties for the seeded account members if you plan to load source data that includes accounts with negative numbers for normal balance.

What Do You Need?

An EPM Cloud Service instance allows you to deploy and use one of the supported business processes. To deploy another business process, you must request another EPM Enterprise Cloud Service instance or remove the current business process.

  • Have Service Administrator access to EPM Enterprise Cloud Service. The instance should not have a business process created.
  • Upload and import this snapshot into your Financial Consolidation and Close instance.
  • Upload and import this additional snapshot into your Financial Consolidation and Close instance to add the data form and Movement member used in this tutorial.

    Note:

    For more information on uploading and importing migration snapshots, refer to the Administering Migration for Oracle Enterprise Performance Management Cloud documentation.

  • Save this compressed file locally and extract the (csv) data files. You will be importing data and metadata using the extracted files.

Reviewing the balance sheet hierarchy

In this section, you load data and review the default balance sheet hierarchy in a form.

Loading data

  1. Using a text editor or spreadsheet program, browse to the folder where you extracted the data files and open DataLoad1.csv.
  2. Review the contents of the DataLoad1.csv file.
    Data Load File

    3. The file contains account balances for January. Normal balances are represented as positive numbers for all account types. Returns and Allowances is a Revenue account with Credit as the normal balance type, so the negative value for this account represents a Debit.

  3. On the Financial Consolidation and Close home page, click Application, then Overview.
    Home Page
  4. On the Application page, click Actions, and select Import Data.
    Actions Menu
  5. In Import Data dialog, click Create.
    Create Button
  6. Keep all the default selections, and click Browse to select a file.
    Browse Button

    Note:

    Depending on your browser, the button label may display Choose File or Browse.
  7. Locate and select DataLoad1.csv, and click Open.
  8. Click Import.
    Import Button
  9. At the information dialog, click OK.
    Information Prompt

    Tip:

    The data files provided in this tutorial have been validated against the loaded business process. For newly created or modified data files, Oracle recommends that you validate the files before running the import process.
  10. Click Close twice.
  11. Click Jobs.
    Jobs Icon
  12. Verify that the job completed successfully.
    Jobs Page
  13. Click Home icon (Home).

Viewing balance sheet data

  1. From the Home page, click Data.
    Data Card
  2. In the form list, select Account Attributes Example.
    Form List
  3. Review the data in the form.
    Balance Sheet

    Normal balances for all account types are positive numbers. For child accounts that aggregate to a parent with an opposite normal balance type, the consolidation operator is set to Subtraction in the account metadata. For example, Total Liabilities has a normal balance of Credit and its parent Net Assets a normal balance of Debit, so the consolidation operator for Total Liabilities is set to subtraction.

  4. Click Close to close the Form.
  5. Click Home icon (Home).

Updating seeded account attributes

Updating account types

If you plan to load negative values as the normal balance for liability and equity accounts, you must change the account type for liability and equity accounts accounts to Asset. If you plan to load negative values as the normal balance for expense accounts, you must change to account type for expense accounts to Revenue.

  1. On the Financial Consolidation and Close home page, click Application, then Overview.
    Overview Card
  2. Click Dimensions.
    Dimensions Tab
  3. In the list of dimensions, click Account.
    Dimensions List
  4. Expand the FCCS_Balance sheet member, and then select FCCS_Total Balance Sheet - Net Asset Approach.
    Total Balance Sheet
  5. In the toolbar, click Zoom to all levels icon (Zoom in all levels).
  6. Click the Member Name column heading to select the Member Name column, and then click Freeze icon (Freeze) to freeze the member names column.
  7. Scroll down the rows until the FCCS_Total Liabilities member is visible, and then scroll the columns until the Account Type column is visible.
    Account Type attribute
  8. In the Account Type list for the FCCS_Total Liabilities row, select Asset.
    Account Types list
  9. Scroll down the list of accounts and make the following changes to the seeded account members (prefix FCCS_):
    • Change seeded accounts with the Account Type Liability or Equity to the account type Asset.
    • Change seeded accounts of account type Expense to the account type Revenue. (Verify that the Variance Reporting attribute for the account automatically updates to Non-Expense. If it does not, change Variance Reporting to Non-Expense.)

    Note:

    When you change the account type for a seeded account to Asset, Liability, or Equity, the Time Balance property automatically changes to Balance. When you save your changes, the system changes the Time Balance property back to Flow, which is the required setting for seeded balance sheet accounts.

    Note:

    You need to update account types for the descendants of FCCS_Total Balance Sheet-Net Asset Approach only. You do not need to update the members in the FCCS_Total Balance Sheet-Cash and NonCash hierarchy or for FCCS_EPUSource.
  10. When you are finished updating the seeded member account types, click Save.
    Save button

Updating consolidation operators

When a child is aggregated to a parent with a normal balance of the same type (for example Credit to Credit) the system expects Addition for the consolidation operator. If a child aggregates to an parent with a normal balance of a different type (for example, Debit to Credit), the system expects Subtraction for the consolidation operator. The system metadata validation shows an error if the expected consolidation operator is not used. In this part of the tutorial you update the consolidation operator for seeded accounts for which you modified the account type where needed.

  1. Scroll up the rows until the FCCS_Total Liabilities member is visible, and then scroll the columns until the Consol Consol op column is visible.
    Consolidation Operator
  2. In the consolidation operator list, change the operator from Subtraction to Addition.
    List of Operators

    Because the normal balance for Total Liabilities will be negative values, an operator of Addition will reduce the balance of its parent, Net Assets.

  3. Scroll down the list of accounts and change the consolidation operator for the accounts in the table.:
    Account Change Consol Operator To
    FCCS_Total Equity Addition
    FCCS_Owners Income Subtraction
    FCCS_Minority Interest Income Addition
    FCCS_Discontinued Operations Tax Addition
    FCCS_Cost of Sales Addition
    FCCS_Operating Expenses Addition
    FCCS_Provision for Income Tax Addition
    FCCS_Dividends Declared Addition
    FCCS_Total Other Comprehensive Income Subtraction
  4. When you are finished updating consolidation operators for the seeded members, click Save.
  5. Click Cancel to return to the Overview page.

Loading metadata for non-seeded accounts

The file AccountMetadataLoad.csv includes metadata for all of the non-seeded accounts. It sets the account type for all non-seeded expense accounts to Revenue and all non-seeded liability and equity accounts to Asset, It updates the consolidation operators for the accounts where needed.

  1. Click Import.
    Import button
  2. Click Create.
  3. In the Account row, click Browse, navigate to the location where you unzipped the data files, and select the file AccountMetadataLoad.csv.
    Browse button
  4. Click Import.
    Import button
  5. At the Options dialog box, click OK (do not select to refresh the database).
    Options dialog box
  6. At the information prompt, click OK.
    Information Prompt

    The metadata files provided in this tutorial have been validated against the loaded business process. For newly created or modified metadata files, Oracle recommends that you validate the files before running the import process.

  7. Click Close twice to return to the Overview page.
  8. Click Jobs.
    Jbb icon
  9. Verify that the import completed successfully.
    Jobs page
  10. Click Overview, and then Dimensions.
  11. In the list of dimensions, click Account.
    Dimensions List
  12. Expand the FCCS_Balance sheet member, and then select FCCS_Total Balance Sheet - Net Asset Approach.
    Total Balance Sheet
  13. In the toolbar, click Zoom to all levels icon (Zoom in all levels).
  14. Click the Member Name column heading to select the Member Name column, and then click Freeze icon (Freeze) to freeze the member names column.
  15. Scroll the rows until the FCCS_Total Liabilities member is visible, and then scroll the columns until the Account Type column is visible.

    All seeded and non-seeded liability and equity accounts should now be set to the Asset account type. All seeded and non-seeded expense accounts should now be set to the Revenue account type.

    Account Types
  16. To verify your work, continue scrolling down the entire list of accounts under Total Balance Sheet Traditional-Net Asset Approach. Change any accounts that still have Liability or Equity for Account Type to Asset. Change any accounts that still have Expense for Account type to Revenue.
  17. When you are finished verifying the account types, click Save.
  18. Click Cancel to return to the Overview page.

Validating metadata and refreshing the database

  1. In the Actions menu, select Validate Metadata.
    Validate Metadata Menu Option
  2. Click Run.
    Valide Metadata dialog box
  3. If any errors are reported open the Account dimension in the dimension editor and correct the errors, and then run metadata validation again to verify the fix. Repeat until no errors are reported
  4. From the Overview page, select Refresh Database from the Actions menu.
    Refresh Database menu option
  5. Click Create.
    Create button
  6. In the Refresh Database dialog box, click Refresh Database.
    Refresh Database dialog box
  7. At the warning prompt, click Refresh.
    Refresh Database wraning prompt
  8. When the status displays as Succeeded and the percent complete is 100%, click Finish.
    Refresh Database status
  9. Click Close twice.

Reviewing the updated balance sheet hierarchy

In this section, you load data that includes negative values as the normal balance for liability, equity, and expense accounts, and then review the modified balance sheet hierarchy in a form.

Loading data with negatives values

  1. Using a text editor or spreadsheet program,, browse to the folder where you extracted the data files and open DataLoad2.csv.
  2. Review the contents of the DataLoad2.csv file.
    Data load file with negative numbers

    3. In this file, normal balances for liability, equity, and expense accounts are represented as negative numbers. Normal balances for all other account types are represented as positive numbers.

  3. On the Financial Consolidation and Close home page, click Application, then Overview.
    Home Page
  4. On the Application page, click Actions, and select Import Data.
    Actions Menu
  5. In the Import Data dialog, click Create.
    Create Button
  6. Click Browse and browse to the location where you unzipped the compress files. Select DataLoad2.csv.
    Browse Button

    Note:

    Depending on your browser, the button label may display Choose File or Browse.
  7. Click Import.
  8. At the information dialog, click OK.
    Information Prompt

    The data files provided in this tutorial have been validated against the loaded business process. For newly created or modified data files, Oracle recommends that you validate the files before running the import process.

  9. Click Close twice.
  10. Click Jobs.
    Jobs Icon
  11. On the Jobs page, verify that the job completed successfully.
  12. Click Home icon (Home).

Viewing the updated balance sheet

  1. From the Home page, click Data.
    Data Card
  2. In the form list, select Account Attributes Example.
    Form List
  3. Review the data in the form.

    Normal balances for liability, equity, and expense accounts are negative numbers.. For child accounts that have negative numbers as normal balance, the consolidation operator is set to addition. This decreases the parent during aggregation. In the example, adding Total Liabilities to Net Assets reduces Net Assets by 10,000.

    Form with data
  4. Click Close to close the Form.

Learn More