Configure GL Account Budget Import Formats

If you are licensed to the General Ledger module, it is possible to import your GL account budget to help drive budgeting controls and onscreen event budgeting analysis. When importing a budget, an import format must first be configured. The import format is used to map the information in each column of your import file to the correct fields in the system. You typically only need to configure this one time.

In this article:

• Add a new Budget Import Format
• Configure Budget Format Header Details
  • Add a Constant Head Detail
  • Add a Fiscal Year Header Detail
  • Add the GL Account or Main GL Account Header Detail
  • Add GL Dimension Header Details (only if GL Dimensions is in use)
  • Add any Additional Columns as Header Details
  • Add the Budgeted Fiscal Periods Header Details
• Configure Budget Import Format Batch Control
  • Add a Constant Batch Control
  • Add a Fiscal Year Batch Control
  • Add a GL Account or Main GL Account Batch Control
  • Add GL Dimension Batch Controls (only if GL Dimensions is in use)
  • Add any Additional Column Batch Controls
  • Add the Budgeted Fiscal Period Batch Controls
• Copy a Budget Import Format
• Edit a Budget Import Format

Add a new Budget Import Format

When adding a new import format, you can create a new format or copy an existing format. If the required format is similar to an existing format, it is recommended to copy the existing format and then make the necessary adjustments.

To add a new Budget Import Format:

1. Navigate to the GL Posting Import Formats page and click Add. The Add GL Posting Imports Header window opens.
2. Enter the necessary information:
   • Description: Name for the import file format.
   • Control: Select Yes. This means you will be using a control record within the file.
   • Fixed: We recommend you select No.
     • Yes should only be selected if the layout of the import fields uses fixed format instead of delimited. A fixed format layout means each field will always have a specified number of characters so no field delimiter is needed.
   • Delimited: Determines how each field is separated (comma or tab) in the import file.
     • CSV: Select if using a .csv (Excel) file.
     • None: No delimiter is used in the file and the file is a fixed length.
     • Tab Delimited: Select if using a .txt file.
   • Export/Import File Name: Default file name for the import file once it is imported.
   • Skip First Row: If checked, the import process ignores the first row in the import file. Check this check box if the first row of your budget import file holds the column descriptions.
3. Click Save. The information is saved and the window name is updated to Edit GL Posting Imports Header.

Configure Budget Format Header Details

In the Details tab, you will map each column in your import file to the appropriate system field.

All Budget Format Headers Details should have the following list of detail lines, as a minimum:

• EBMSGL010BUDGET Constant
• Fiscal Year Detail
• GL Account or Main GL Account
• GL Dimensions 1 & 2 (only add if you are using the GL Dimension functionality).
• One line for each budgeted Fiscal Period

You must complete the set up for each of the elements above, for the budget import format to be successful. You can also add any additional columns required by your organization. These should be added before the Fiscal Period lines are added, as per the Add any Additional Columns as Header Details instructions.

Add a Constant Header Detail

The Constant tells the system which feature you would like the figures to be imported into. To add the Constant Header:

1. 1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
   2. Enter the necessary information: 
      • Order: Enter 10. It is best practice to order your details lines in multiples of 10.
      • Column Label: Select <Specify Constant>.
      • Value: Enter EBMSGL010BUDGET.
   3. Click OK. The detail line is saved and the window will close.

Add a Fiscal Year Header Detail

The Fiscal Year Header ensures that your figures are imported into the correct Fiscal Year. To add the Fiscal Year Header:

1. 1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
   2. Enter the necessary information: 
      • Order: Numeric value to represent the order the line is in the file (multiples of 10 are recommended as best practice).
      • Column Label: Select Fiscal Year - Detail.
      • Fill Factor: Select Blanks.
      • Show Decimal Point: Leave unchecked.
      • Number of Decimals: Enter 0.
   3. Click OK. The detail line is saved and the window will close.

Add the GL Account or Main GL Account Header Detail

The GL Account or Main GL Account tells the system which GL Account to map each budget line to.

To add the GL Account or Main GL Account Header:

1. 1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
   2. Enter the necessary information: 
      • Order: Numeric value to represent the order the line is in the file.
      • Column Label: Select GL Account or Main GL Account.
        • If you are using GL Dimension you will select Main GL Account.
        • If you are not using GL Dimensions, you will select GL Account.
      • Fill Factor: Select Blanks.
      • Show Decimal Point: Leave unchecked.
      • Number of Decimals: Enter 0.
   3. Click OK. The detail line is saved and the window will close.

Add GL Dimension Header Details

If you are using GL Dimensions, you should add one additional detail line for each GL Dimension required in the GL Mask Code assigned to Main GL Account (Core Dimension 1, Core Dimension 2, Department, Event Category, and so on.). The process to add each of these is the same:

1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select *Dimension field*
   • Fill Factor: Select Blanks.
   • Show Decimal Point: Leave unchecked.
   • Number of Decimals: Enter 0.
3. Click OK. The detail line is saved and the window will close.
4. Repeat as required for each of your Main GL Account dimensions.

Add any Additional Columns Header Details

Your Budget Import document might also include other columns with information important for your organization but is not required by Enterprise for the budget to be imported. To minimize modification of your organization's budget file, it is possible to add additional detail lines to the Import Format as a place holder for these columns. These additional columns can also be used to validate the import by using the Specify Constant column label.

To configure any additional Detail lines:

1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select one of the following options to assign the column name.
     • <Empty Field>: Indicates the information in this column should be ignored. An End Position of '1' is required when using this column label.
     • <Specify Constant>: Indicates a specific value should be validated in this column. The value to be validated should be entered into the Value field.
   • Fill Factor: Determines how missing values are filled if there are not enough digits in the import file. Although multiple options are available, the below are usually the only ones used for importing files:  
     
     • Blanks: A blank value is used for missing digits. Typically used for text fields.
     • Blanks +/- Left: No zeros are added for missing digits and a positive or negative sign shows to the left of the value (+100 or -100). Typically used for decimal values.
     • Zeros: Zeros are used for missing digits and values show without any positive or negative signs or decimal points. Typically used for non-decimal numeric values.
     • Zeros +/- Left: Zeros are used for missing digits and a positive or negative sign shows to the left of the value (+000000100 or -000000100). Typically used for decimal values.
   • Value: Value for the <Specify Constant>. Only available if <Specify Constant> is selected in the Column Label drop-down.
   • Show Decimal Point: If checked, include the decimal point in numbers.
   • Number of Decimals: Number of decimals in the value.
3. Click OK. The detail line is saved and the window will close.

Add the Budgeted Fiscal Periods Header Details

Once your GL Account/Dimensions, Fiscal Period and additional details are configured, it is important to also add one detail line per Fiscal Period that you would like to set budget amounts for.

To configure the Budget Period Detail lines:

1. In the Details tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select G/L Budget PTD Amount.
   • Fill Factor: Select Blanks.
   • Show Decimal Point: If checked, decimal points are included in the file.
   • Number of Decimals: Number of decimal places to include in the import.
3. Click OK. The detail line is saved and the window will close.
4. Repeat steps 1-3 for each budget period in the import file. For example, if you are importing a budget for 12 periods, you will add 12 detail lines with the column label G/L Budget PTD.

Once you have configured the above steps, the content of your Details tab for the import file format should look similar to below:

Configure Budget Import Format Batch Control

The Import Format Batch Control is used to tell the system what to expect from the Header row of your import document. If you do not have a header row in your import document, you would have selected No when configuring the Control field in the Budget Import Format Header and you will receive an Action Not Allowed warning when trying to add detail to the Batch Control tab.

It is important the lines added in the Control tab match the order of the configuration you have already set in the Details tab.

All Budget Format Batch Control tabs should have the following list of detail lines, as a minimum:

• EBMSGL010BUDGET Constant
• Fiscal Year Detail
• GL Account or Main GL Account
• GL Dimensions (only add if you are using the GL Dimension functionality).
• One line for each budgeted Fiscal Period

You must complete the set up for each of the elements above for the budget import format to be successful. You can also add any additional columns required by your organization. These should be added before the Fiscal Period lines are added, as per the Add any Additional Columns as Header Details instructions.

Add a Constant Batch Control

The Constant tells the system which feature you would like the figures to be imported into. To add the Constant Header:

1. In the Batch Control tab of the Edit GL Posting Imports Header window, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Enter 10. It is best practice to order your details lines in multiples of 10.
   • Column Label: Select <Specify Constant>.
   • Value: Enter EBMSGL010BUDGET.
3. Click OK. The batch control line is saved and the window will close.

Add a Fiscal Year Batch Control

The Fiscal Year Batch Control ensures that your figures are imported into the correct Fiscal Year. To add the Fiscal Year Batch Control:

1. In the Batch Control tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file (multiples of 10 are recommended as best practice).
   • Column Label: Select Fiscal Year - Header.
   • Fill Factor: Select Blanks.
   • Show Decimal Point: Leave unchecked.
   • Number of Decimals: Enter 0.
3. Click OK. The batch control line is saved and the window will close.

Add a GL Account or Main GL Account Batch Control

The GL Account or Main GL Account tells the system which GL Account to map each budget line to.

To add the GL Account or Main GL Account Header:

1. In the Batch Control tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select <Empty Field>.
   • Fill Factor: Select Blanks.
   • Show Decimal Point: Leave unchecked.
   • Number of Decimals: Enter 0.
3. Click OK. The batch control is saved and the window will close.

Add GL Dimension Batch Controls

If you are using GL Dimensions, you should add one additional control batch line for each GL Dimension required in the GL Mask Code assigned to Main GL Account (Core Dimension 1, Core Dimension 2, Department, Event Category etc.). The process to add each of these is the same:

1. In the Batch Control tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select <Empty Field>.
   • Fill Factor: Select Blanks.
   • Show Decimal Point: Leave unchecked.
   • Number of Decimals: Enter 0.
3. Click OK. The detail line is saved and the window will close.
4. Repeat as required for each of your Main GL Account dimensions.

Add any Additional Column Batch Controls

Your Budget Import document might also include other columns with information that is important for your organization but is not required by Enterprise for the budget to be imported.

To configure any additional Detail lines:

1. In the Batch Control tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Column name for the column.
     • <Empty Field>: Indicates the information in this column should be ignored.
     • <Specify Constant>: Indicates a specific value should be validated in this column. The value to be validated should be entered into the Value field.
   • Fill Factor: Determines how missing values are filled if there are not enough digits in the import file. Although multiple options are available, the below are usually the only ones used for importing files:  
     
     • Blanks: A blank value is used for missing digits. Typically used for text fields.
     • Blanks +/- Left: No zeros are added for missing digits and a positive or negative sign shows to the left of the value (+100 or -100). Typically used for decimal values.
     • Zeros: Zeros are used for missing digits and values show without any positive or negative signs or decimal points. Typically used for non-decimal numeric values.
     • Zeros +/- Left: Zeros are used for missing digits and a positive or negative sign shows to the left of the value (+000000100 or -000000100). Typically used for decimal values.
   • Value: Value for the <Specify Constant>. Only available if <Specify Constant> is selected in the Column Label drop-down.
   • Show Decimal Point: If checked, include the decimal point in numbers.
   • Number of Decimals: Number of decimals in the value.
3. Click OK. The control batch line is saved and the window will close.

Add the Budgeted Fiscal Period Batch Controls

Once your GL Account/Dimensions, Fiscal Period and additional batch controls are configured, it is important to also add one batch control per Fiscal Period that you would like to set budget amounts for.

To configure the Budget Period Batch Controls:

1. In the Batch Control tab, click Add. The Add GL Posting Import Detail window opens.
2. Enter the necessary information: 
   • Order: Numeric value to represent the order the line is in the file.
   • Column Label: Select Fiscal Period - Header.
   • Fill Factor: Select Blanks.
   • Show Decimal Point: If checked, decimal points are included in the file.
   • Number of Decimals: Number of decimal places to include in the import.
3. Click OK. The batch control is saved and the window will close.
4. Repeat steps 1-3 for each budget period in the import file. For example, if you are importing a budget for 12 periods, you will add 12 detail lines with the column label Fiscal Period - Header.

Once you have configured the above steps, the content of your Batch Control tab for the import file format should look similar to below:

When your Budget Import Format has been configured as above, click OK. You are now ready to Import your GL Account Budget.

Copy a Budget Import Format

If a new format is required which is similar to an existing format, you can make a copy of the existing format, then make your change on the copied format.

To copy an existing format:

1. Navigate to the GL Posting Import Formats page and find the import format to copy.
2. Right-click on the import format and select Copy. The Copy G/L Import Formats window opens.
3. In the New Format Description field, enter the name for the new import format.
4. Click OK. The new format is created. This can be altered as per the Edit a Budget Import Format process.

Edit a Budget Import Format

GL Posting Import Formats can be viewed on the GL Posting Import Formats page. Edit a format to view specific details. The general information about the format (in the top section) will determine the structure of the file. Please note that activating Header and Control records will change the tabs available for the contents of the file format.

1. Navigate to the GL Posting Import Formats page and find the import format to edit.
2. Right-click on the import format and select Edit.
3. Make any changes, as needed. Refer to the Configure Budget Format Header Details and Configure Budget Import Format Batch Control sections for more information about each field and tab.
4. Once you have completed your changes, click OK. The changes are saved and the Edit window closes.