1. Implementing Enterprise Structures and General Ledger
  2. How to Create Related Value Sets for a Chart of Accounts

How to Create Related Value Sets for a Chart of Accounts

You can define related value sets and values manually, or you can use an import process.

Manual Method

To create a value set relationship for a chart of accounts, or to edit or delete an existing value set relationship:

  1. In the Setup and Maintenance work area, go to the following:

    • Offering: Financials

    • Functional Area: Financial Reporting Structures

    • Task: Manage Chart of Accounts Value Sets

  2. Search for the Independent value set that you want another value set to be related to.

    For example, a chart of accounts has the following segments in this order: Company, Department, and Account. The corresponding value sets are also named Company, Department, and Account. To filter Department values based on the selected Company value, search for either the Company or Department value set.

  3. In the Search Results section, click the Related Value Sets tab.

  4. From the Actions menu, select Create to create the relationship, or select Edit or Delete to update or delete an existing relationship.

    To create a relationship, select the applicable value set in the Related Value Set Code field. For example, select the value set for the Department segment if you had selected the Company value set in the previous step.

  5. Click Save.

  6. Click the Add Row icon.

  7. Add the values and save.

    Note: When creating a new relationship between value sets, don't enable the relationship until after you finish mapping all of the related values.

    This image shows the Create Value Set Relationship page for the Company Vision Corporation and Department Vision Corporation value sets. The Related Values section shows that the related department values for company 01 are 110, 120, and 130, and the related department values for company 02 are 110, 111, and 112.

    The Create Value Set Relationship page with a sample value set relationship with mapped values.

  8. Redeploy the chart of accounts structure for the value set relationship to take effect.

    • Offering: Financials

    • Functional Area: Financial Reporting Structures

    • Task: Manage Chart of Accounts Structure Instances

    Note: You don't have to redeploy the chart of accounts when you're adding and updating related values.

Import Method

The import method is useful when you have to map a large number of related values. You can import both related value sets and related values, or either one of them. You can also use this method for future maintenance, such as updating existing relationships.

The import method uses .csv files. One file is used for relationship definitions and the other for the related values.

To create value set relationships:

  1. Enter the following header information in the first row of the .csv file, separating each attribute with a vertical bar (|): VALUE_SET_CODE1|VALUE_SET_CODE2|ENABLED_FLAG

  2. In the subsequent rows, enter the names of the related value sets followed by Y or N to indicate whether the relationship is enabled. Separate the values in each row using the vertical bar (|).

    This image shows an example .csv file. The Department Vision Corporation value set is related to the Company Vision Corporation value set and the relationship is enabled.

    An example .csv file with a value set relationship.

To create or maintain the values for defined relationships:

  1. Enter the following header information in the first row of the .csv file, separating each attribute with a vertical bar (|): VALUE_SET_CODE1|VALUE_SET_CODE2|VALUE1|VALUE2|ENABLED_FLAG

  2. In the subsequent rows, enter the names of the related value sets, the related values, and Y or N to indicate whether the row is enabled. Separate the values in each row using the vertical bar (|).

    This image shows an example .csv file with a header row and six rows of related values. The related department values for company 01 are 110, 120, and 130, and the related department values for company 02 are 110, 112, and 112. All of the rows are enabled.

    An example .csv file showing a header row followed by six detail rows with value set values.

To import the .csv files into the document repository:

  1. Navigate to the Tools work area and select the File Import and Export link.

  2. Select Upload from the Actions menu.

  3. Choose a .csv file that you prepared earlier.

  4. In the Account field, specify the location for upload. For example, select fin/generalLedger/import.

    This image shows an example of the Upload File dialog box with values for the File and Account parameters.

    An example of the Upload File dialog box.

  5. Click Save and Close.

To import the value set relationships and related values into the application:

  1. In the Setup and Maintenance work area, go to the following:

    • Offering: Financials

    • Functional Area: Financial Reporting Structures

    • Task: Manage Chart of Accounts Value Sets

  2. From the Search Results section Actions menu, select Import Related Values.

  3. On the Import Related Values dialog box, select the account location where you previously uploaded the .csv files.

  4. Specify the names of the files in the Related Value Set File Name and Related Values File Name fields. If the relationship between value set pairs has already been manually created, upload only the related values files.

    This image shows an example of the Import Related Values dialog box with values for the Account and File Name parameters.

    An example of the Import Related Values dialog box with sample values.

  5. Click Upload.

  6. Click the Download Log File link to verify the results.

  7. If you created or updated a value set relationship, verify that the chart of accounts structure was successfully redeployed.

  8. Optionally, verify the impact of related values on account combinations on the Create Journal page.

    This image shows the list of values for the Department segment in the Account dialog box on the Create Journal page when Company 01 is selected.

    An example of the list of values for the Department segment when Company segment 01 is selected. In this example, the Department values listed are 110, 120, and 130.

    This image shows the list of values for the Department segment in the Account dialog box on the Create Journal page when Company 02 is selected.

    An example of the list of values for the Department segment when Company segment 02 is selected. In this example, the Department values listed are 110, 111, and 112.

    As an alternative to steps 1 through 6, you can go to the Scheduled Processes page and submit the process called Upload Comma-Separated Values File. Enter the .csv file name and its location in the document repository. Select the appropriate file type of either RELATEDVALUESET or RELATEDVALUESETVALUES, depending on what you want to upload.

    This image shows the Process Details page with values for the File Name, Account, and File Type parameters.

    An example of the Process Details page with sample values for all of the parameters.