Manage Workflow Rules Using a Spreadsheet

Before creating and managing workflow rules, perform these steps:

  1. Sign in to the application as a Financial Application Administrator.

  2. Verify if the Approval Routing Administration feature is enabled at Offerings > Financials > Opt In Features. Click the Edit icon for Financials. If the feature isn't enabled, select its check box.

  3. In the Setup and Maintenance work area, go to Financials > Application Extensions > Manage Workflow Rules in Spreadsheet task.

Download the Rules Template

Download the rule template by performing these steps:

  1. In the Rule Templates section of the Manage Workflow Rules in Spreadsheet page, select the required workflow.

  2. Click Download. The Download Templates dialog box appears.

  3. From the dialog box, select the required template. Save the template to your local computer.

Caution:

You must use MS Excel version 2016 to create workflow rules.

Note:

Each rule template contains an example of an approval business case to demonstrate how to manage workflow rules using the rule template.

Define the Rules in the Spreadsheet

After downloading the rule template, you must define the workflow rules using the sheets provided in the rule template. Here's a list of sheets you can see in the rule template spreadsheet:

  1. Instructions: This sheet contains details of the help topics present on Oracle Help Center for this feature and the Generate Rule File button. You can also update your rule template version from the Instructions sheet.

  2. Workflow Rules: Provides a template for configuring transaction approval rules.

    Note:

    The name of this sheet varies for each product. For example, for Payables, the sheet is labeled as Invoice Approval Rules.

  3. Data Set: This sheet provides a template to map the varying attributes to the data.

    Note:

    Currently, data sets are only available for Payables workflows. For more information about data sets, refer to the Data Sets section.

A business rule is an approval requirement within your approval policy. Before defining rules in the rule template, you must analyze approval policy. Consider these points before defining a business rule:

  • Which transactions require approval?

  • Who approves transactions in your organization?

  • Do the approvers vary based on the transaction attributes? If so, use a data set.

  • What are approval conditions?

  • How do you want to route the approval notifications?

  • Which approvals require FYI notifications?

  • Which transactions are exempted from the approval rule?

For example, for Payables, if your organization's approval policy mandates that:

  • All invoices that aren't matched to a purchase order must be approved at two levels of the supervisory hierarchy. This hierarchy starts from the manager of the user who creates the invoice.

  • All invoices that have an invoice amount of more than 5000 USD must be approved by a group of personnel from the Finance department.

For this example, you need two business rules.

Use the Workflow Rules sheet to define approval rules. Enter these details:

  1. Rule Description

    Enter the description for each approval business rule that you define.

  2. Approvers

    In this section, designate approvers, specify approval routing, and define rule priority. The template supports a variety of approval routing options. This table provides you details on approval routing and how it works.

    Approval Routing

    How Approval Routing Works

    Supervisory Hierarchy

    Members of the supervisory hierarchy beginning from the first applicable approver receive approval notifications.

    Group in Parallel

    Members of an approval group receive approval notifications. All members receive notifications at the same time. All members must take an action on the approval notification.

    Group in Serial

    Members of an approval group receive approval notifications. Only when a member takes an action on the approval notification does the next member of the series receive the approval notification.

    Group First Responder

    Members of an approval group receive approval notifications. All members receive notifications at the same time. Only one member is required to take an action on the approval notification.

    Job Level Hierarchy

    Members of the job hierarchy beginning from the first applicable approver receive approval notifications.

    User

    The specified application user receives the approval notification.

    Role

    The users with the specified application role receive the approval notification.

    Auto Approve

    Transactions that are automatically approved. No notifications are sent.

    Auto Reject

    Transactions that are automatically rejected. No notifications are sent.

    FYI

    Information only notifications. No action is required from the approver.

    Skip Approval

    Transactions for which the rule isn't applicable. No notifications are sent.

    Note:

    You can only use an approval group that exists in the BPM.

    For detailed instructions on the other columns in the Approvers section, refer to the tool tip on each column header.

    Rule Priority

    Rule priority specifies the order in which rules are evaluated within a particular block during evaluation of the rule set. Administrators can define the priority in the Simplified Workflow Rules Configuration spreadsheet. By default, the rule priority is set as Medium for all rules. You can change the rule priority by selecting a different value. If a dataset is being used for the rule, then its rule priority applies to all rules created using that dataset.

  3. Approval Conditions

    In the Approval Conditions section you can select the attributes based on which the transaction should be evaluated for the workflow rules. You can also add attribute categories.

    To add an attribute category:

    1. Open the list of values associated with the last column in the Approval Conditions section.

    2. Select the required attribute category.

    To add an attribute:

    1. Open the list of values associated with the attribute category.

    2. Select the required attribute.

    For example, in the Invoice Approval template for Payables, you can select Business Unit and Invoice Amount attributes for the attribute category Invoice Header. Similarly, you can select attributes for categories, such as Invoice Line, Invoice Distributions, and more.

    While defining approval conditions, you can use a variety of operators. This table lists the supported operators.

    Condition

    Value Type

    Format

    Example

    Attribute is a specific value

    Text, number, or date

    value

    Note:

    No specific format applies here.

    If the Invoice Type is Standard, then enter the value as:

    Standard

    Attribute value is one of multiple specific values

    Text or number

    in (value 1, value 2, ...)

    If the BU name is Vision Operations, Vision Services, or Vision Foods then enter the value as:

    In (Vision Operations, Vision Services, Vision Foods

    Attribute value should be within a range of values

    Number or date

    between value 1 and value 2

    OR

    value 1 to value 2

    If the Invoice Date is between 01 August 2018 to 01 August 2019, then enter the value as:

    Between 01/aug/2018 and 31/aug/2019

    OR

    01/aug/2018 to 31/aug/2018

    Attribute value starts with a specific value

    Text

    Starts with value

    If the BU name starts with Vision then enter the value as:

    Starts with Vision

    Attribute value ends with a specific value

    Text

    Ends with value

    If the BU name ends with Operations then enter the value as:

    Ends with Operations

    Attribute value contains a specific value

    Text

    Contains value

    If the Pay Group contains Standard then enter the value as:

    Contains Standard

    Attribute value matches a specific value

    Text

    Matches value

    If the Description matches manual invoice then enter the value as:

    Matches manual\\s(.*) invoice

    In this example, the Matches operator begins with Manual and ends with Invoice. Between the two words, there can be one space and any character.

    Other options that can be used with the Matches operator are:

    (.*) - Denotes zero or more characters.

    (.+) - Denotes one or more characters.

    \\s - Denotes space.

    \\d - Denotes numbers from 0-9.

    ? - Makes a character optional. For example: \\d?

    [ ] - Specifies range such as A-Z, 0-9

    Attribute value is more than or equal to a specific number

    Number

    More than equal to number

    OR

    >= number

    If the Invoice amount is more than or equal to 500, then enter the value as:

    More than equal to 500

    OR

    >= 500

    Attribute value is less than or equal to a specific number

    Number

    Less than equal to number

    OR

    <= number

    If the Invoice amount is less than or equal to 500, then enter the value as:

    Less than equal to 500

    OR

    <= 500

    Attribute value is more than a specific number

    Number

    More than number

    OR

    >number

    If the Invoice amount is more than 500, then enter the value as:

    More than 500

    OR

    >500

    Attribute value is less than a specific number

    Number

    Less than number

    OR

    <number

    If the Invoice amount is less than 500, then enter the value as:

    Less than 500

    OR

    <500

    Attribute value is on or before a specific date

    Date

    On or before date

    If the Invoice date is on or before 01/10/2018, then enter the value as:

    On or before 01/oct/2018

    Attribute value is on or after a specific date

    Date

    On or after date

    If the Invoice date is on or after 01/10/2018, then enter the value as:

    On or after 01/oct/2018

    Attribute value is before a specific date

    Date

    Before date

    If the Invoice date is before 01/10/2018, then enter the value as:

    Before 01/oct/2018

    Attribute value is after a specific date

    Date

    After date

    If the Invoice date is after 01/10/2018, then enter the value as:

    After 01/oct/2018

    Attribute value is a specific value and the condition must be evaluated as case insensitive

    Text

    Equals ignore case value

    If the Invoice Source is equal to Manual, then enter the value as:

    Equals ignore case manual

    Note:

    The operators aren't case-sensitive. However, you must enter the date in the DD/MMM/YYYY or DD-MMM-YYYY format only.

    If you have a negative approval condition, add Not as a prefix to any of the supported operators. For example, your approval condition states that BU name isn't Vision Operations, Vision Services, or Vision Foods then enter the value as: Not In (Vision Operations, Vision Services, Vision Foods).

    If you have two distinct rule conditions that require the same approval routing, then you must enter the rule conditions in two separate rows. Ensure that the information in the Approvers section is identical for both the rows.

Rule Blocks

A rule block is a group of rows in the workflow rules spreadsheet. You can define a business rule and all aspects of the business rule in these rows. Use a separate block for each business rule.

While all rule aspects defined within a rule block are processed simultaneously, rule blocks are processed in sequence. Therefore, before defining the rules, you must consider the sequence in which the rules should be processed.

You can create additional rows in a block and additional blocks in a sheet as needed.

To insert more blocks in a rule block:

  1. Select a row.

  2. Right-click and select Add Block.

To add a rule block after the existing rule blocks, click Add Block in the sheet.

To insert more rows in a rule block:

  1. Select a row and right-click.

  2. From the menu, select Insert.

To delete a rule block:

  1. Select all the rows in a rule block.

  2. Right-click and select Delete Block.

Lists

You can use Lists to create customized approval rules based on specific business needs, such as associating a department or project with an invoice or setting threshold limits. This provides greater flexibility and control over the approval process, reduces the need for creating and maintaining a large number of rules, and helps improve both efficiency and compliance.

Data Sets

In your approval policy, if the approver of a transaction varies based on the transaction attributes, then you should use a data set. A data set lets you define a mapping between your data and the variation in approvers based on such data.

For example, a transaction with an amount greater than 5000 USD must be approved by an approval group. However, the approval group varies depending on the cost center. In this case, you can use a data set to define a mapping between the cost center and the approval group.

Note:

Currently, data sets are only available for templates for Payables Invoice Approval Workflow.

To define a data set, perform these steps:

  1. Open the Data Set sheet of the rule template.

  2. In the Set Name column, enter a unique name.

  3. Enter the value in the Approval Group/Supervisory Level/Job Level Range/User/Role column. This value depends on the approval routing of the rule for which you're using the data set.

    For the given example, specify the approval group name in the Approval Group/Supervisory Level/Job Level Range/User/Role column.

    Note:

    You can only use an approval group that already exists in the BPM.

  4. If your starting approver for rules using Supervisory or Job Level Hierarchy approval routing varies based on transaction attributes, click Add Start Approver. This adds the Start Approver column in the dataset. You can select any of the values from the list of values or directly enter a user name as the start approver.

    Note:

    You must select a Use Dataset value in the Start Approver column of the Approvers section in the Invoice Approval Rules or Invoice Request Approval Rules sheet to specify a start approver in the dataset. This ensures that the Start Approvers are picked up from the dataset for such rules.

  5. In the Varying Attribute section, select the attributes based on which the approver varies for the transaction.

    For the given example, select Distribution Cost Center Segment from the list of values and specify the cost center values for each approval group.

  6. Enter values for each varying attribute. You can also use the supported operators with the values.

  7. Click Add New Column to create additional columns for varying attributes.

  8. Click Add Data Set to create additional data sets.

After you create a data set, you must enter a data set reference in your rule in the Workflow Rules sheet. Prefix the data set name with $ to create a reference. For example, to reference a data set named Supervisory, enter the value as $Supervisory Set in the Workflow Rules sheet.

You can enter the data set references in the Approvers section of the Workflow Rules sheet. Based on the approval routing used for the rule, enter data set references in these columns:

  • Job Level Range

  • Approval Level

  • Group/User/Role Name

Note:

You can enable or disable rules and rule blocks. This helps improve efficiency by eliminating the unnecessary steps of copying rules. Disabled rules don't affect approval processing.

Generate Rule File

After entering the data in the Workflow Rules sheet, click the Generate Rule File button located in the Instructions sheet to generate the rule file. A compressed file is generated. Save the file in your local computer.

Upload the Rule File

To upload the rule file, perform the following steps:

  1. Navigate to the Manage Workflow Rules in Spreadsheet page.

  2. In the Rule Templates section, select the required workflow.

  3. Click Upload. The Upload File dialog box appears.

  4. In the File field, click Choose File.

  5. From your local directory, select the compressed rule file that was generated from the workflow rules template.

  6. Click Submit. A confirmation message stating the process ID appears.

    Caution:

    Every successful rule upload using a spreadsheet template overrides the existing rules for the workflow.

  7. Click OK.

  8. Check the status of the upload in the Upload History section.

Update the Rule Template Version

While uploading your rule template, if you're asked to update the file version, perform the following steps:

  1. Download the latest version of the rule template from the Manage Workflow Rules in Spreadsheet page. You can select any of the available templates for the workflow.

  2. In the Instructions sheet of the rule template, click Update Spreadsheet.

  3. Select the older version of the rule template and click OK.

    This copies rules from the older version of your rule template to the latest version.

  4. Review the copied rules and proceed as usual to create rules using the latest version of the rule template.

Verify the Spreadsheet Upload

The Upload History section displays details of the spreadsheet uploads such as the date, user, rule template used, and the status.

If the rule upload process fails, the status is displayed as Error. Click Error to download the Error CSV file. Review the error details, resolve the errors in the spreadsheet, and generate the rule file again.