1. Using General Ledger
  2. Create Recurring Journals

Create Recurring Journals

This example shows how to define and generate formula recurring journals that are automatically generated every period.

You must have:

  • A role that can access the Journals work area in Oracle General Ledger.

  • A duty that can access the Create Allocation Rules task.

The following assumptions apply:

  • The chart of accounts includes segments for company, department, account, subaccount, and product.

  • Account 1210 is the trade receivables account.

  • The period-to-date activity in account 1210 is 100,000 USD.

Here are the goals for this example.

  • Create a journal that populates a monthly allowance for bad debt based on 5% period-to-date activity in the trade receivables account.

  • Account 7730 is the bad debt expense account and account 1260 is the allowance for bad debt account.

  • A formula rule must be defined to generate the following journal entry for the period Apr-17 and thereafter.

    • DR 01.000.7730.0000.000 5,000 USD

    • CR 01.000.1260.0000.000 5,000 USD

This worked example includes details for the following tasks:

  1. Configuring the Formula Rule

  2. Creating the Runtime Prompt Variable

  3. Creating the Rule Set

  4. Generating the Allocation Journal

Configuring the Formula Rule

Create a formula rule.

  1. Navigate to the Journals work area.

  2. From the Tasks pane, click Create Allocation Rules.

  3. Navigate to the Administer menu option and then select Calculation Manager. Calculation Manager opens in a new browser window and a cube is highlighted based on the data access set selected in the Journals work area.

  4. Expand Essbase.

  5. Expand VF_USA_Accounting_Flexfield (your cube).

  6. Expand db.

  7. Highlight the Rules row, right-click, and select New from the menu.

  8. Enter the Rule Name: Special Bad Debt Allocation, accept the other defaults, and click OK.

  9. The Rule Designer opens in a new tab. From the New Objects list, click, hold, and drag the Point of View object. Place it between the Begin and End nodes in the Rule Designer.

  10. Enter a Caption: Point of View.

  11. Perform the following steps to enter a Variable Dimension Value:

    • Click the Value field for Accounting Period.

    • Click the Actions icon and select Variable from the drop-down list. A new window opens.

    • For the Category, select Database from the drop-down list.

    • Click Accounting_Period.

    • Click OK.

  12. Perform the following steps to enter other member dimension values:

    • Click the Value field for another dimension.

    • Click the Actions icon and select Member from the drop-down list.

    • Select a member and click the Add icon to move the member to the Selections panel.

    • Click OK. Repeat for all dimensions to include in the Point of View.

    • In this scenario, the following are fixed dimension values:

      • Company: 01

      • Department: 000

      • Subaccount: 0000

      • Product: 000

      • Currency: USD

      • Currency Type: Total

    • From the New Objects list, click, hold, and drag the Formula component and place it between the Point of View nodes in the Rule Designer.

    • Enter a Caption: Bad Debts Calculation.

    • Enter the offset member.

    • Click Next.

    In this scenario, the offset is defined as account 1260, the allowance for bad debt. The offset is child combination 01.000.1260.0000.000 when combined with the fixed member dimension values in the Point of View.

  13. Perform the following steps to enter the Formula member dimension value:

    In this scenario, the formula member dimension value is defined as account 7730. The bad debt expense is charged to child combination 01.000.7730.0000.000 and combined with the fixed member dimension values in the Point of View.

    • Click the icon for the formula field and select Member from the drop-down list.

    • Select the Account dimension value, highlight the row, and click the Select icon to move the value to the Selections panel.

      In this scenario, the goal is to calculate an allowance for bad debt based on the period-to-date activity in trade receivables account 1210. Trade receivable is child combination 01.000.1210.0000.000 when combined with the fixed member dimension values in the Point of View.

    • Repeat for the other formula member values and click OK when all formula members are selected.

      In this scenario, the following dimension values are selected. Selection of members for the following dimensions is required for the source in a formula component.

      • Scenario: Actual

      • Balance Amount: Period Activity

      • Amount Type: PTD

    • Multiply the formula expression by .05.

    • Click the Save icon.

    • Click the Validate and Deploy icon.

Creating the Runtime Prompt Variable

Create a runtime prompt variable as an optional component of a rule. When you generate an allocation based on a rule with a defined runtime prompt variable, you are prompted to specify a dimension member for the variable. The variable is used in the allocation calculation.

For example, use a runtime prompt variable of Accounting Period, which prompts you to specify the period to use in the allocation calculation. A runtime prompt variable can be created once and used in multiple rules.

  1. Navigate to the Journals work area.

  2. From the Tasks pane, click Create Allocation Rules.

  3. Once the Calculation Manager opens in a new browser window, a cube is highlighted based on the data access set selected in Journals work area. To define the runtime prompt, select Variables from the Tools menu.

  4. Expand to the db for the cube, highlight the row, right-click the row, and select New from the menu.

  5. The Variable Designer opens in a new tab. A default value must be entered and the variable name can't contain any spaces. Complete the fields, as shown in this table.

    Field

    Value

    Name

    AccountingPeriod

    Type

    Member

    Dimension

    AccountingPeriod

    Default Value

    Apr-17

    RTP

    Selected

    RTP Text

    Enter Accounting Period

  6. Click the Save icon. The runtime prompt variable is ready for use.

Creating the Rule Set

Rule sets are created by combining two or more related rules together to enable sequential allocating of balances.

  1. Navigate to the Journals work area.

  2. From the Tasks pane, click Create Allocation Rules.

  3. After the Calculation Manager opens in a new browser window, expand to Rule Sets for the selected cube. Highlight the row, right-click the row, and select New from the menu.

  4. Enter the rule set name and click OK.

  5. The Ruleset Designer opens in a new tab. Expand to the db for the cube for which the rule set is created, expand the rules, and drag the rules for the rule set.

  6. Click the row for the rule set, click the Variables tab, and select Merge Variables.

    Merge variables means that common variables among all of the rules in the rule set are merged. You only have to select the runtime prompt value once when submitting the Generate General Ledger Allocations process.

  7. Click the Save icon.

  8. Click the Validate and Deploy icon.

Generating the Allocation Journal

Start the allocation process to create the journal entries that populate the account balances.

  1. Navigate to the Journals work area.

  2. From the Tasks pane, click Generate General Ledger Allocations.

  3. Select a rule or rule set and enter any runtime prompt values.

  4. Click Submit.

  5. The Generate General Ledger Allocations task submits four processes consecutively (three, if the Post Allocations option isn't is selected). The processes calculate the allocation, write the results to the GL_INTERFACE table, import the journal batches, and post the journal batches to the General Ledger.