Revised: 09/10/2021
There is a Budget Data Load Template for the capital budget model (AGYCAP) and another for the capital reappropriation (AGYRAP) provided by OBM. They are accessible through the OBM budget web page.
The Budget Data Load process enables multiple AMTPER method line items to be imported at one time. This process imports line item data (ChartField strings) from a .csv file into the specified business unit, planning model, scenario, activity, planning center, and budget version. Keep the following considerations in mind when using this process:
All locks to the model need to be released prior to attempting the upload and the model must be in the Released state or the upload will fail.
Only method amounts are updated (adjustment and allocation amounts are not updated).
Any line items that have been manually deleted after the model was released will be reintroduced by the import process if the combination exists in the input .csv file.
Deleting lines from the budget data load will not delete lines that already exist in BPM. It is necessary to go back to My Planning Workspace to revise or delete existing ChartField strings online.
This process locks the Planning Center. The Planning Center will automatically unlock when the upload process is completed.
The system does not prevent multiple concurrent processes from writing data to the same planning center; if multiple import files are being processed for the same planning center, they should be run sequentially.
Always save the working version as an .xls or .xlsx file. Save the final version to load as a .csv file. The .csv file must be .csv comma delimited and not .csv Macintosh or .csv MS-DOS.
If corrections are needed after the .csv file has been saved, go back to the .xls or .xlsx version to make the corrections and save as a new .csv to load.
The navigation path to load the final .csv file:
For revisions where the row of data is no longer needed, it is necessary to delete the entire row in Excel rather than merely clearing the data from the cells. Simply clearing the data and saving the file as .csv will add commas for that line, which will result in an error when loading.
The first row of the .csv file must be a header row that names the ChartFields, then the budget periods to be loaded, in order in which they originally appear. DO NOT CHANGE THE HEADER ROW.
The ChartField names must exactly match those specified in the activity definition for the model; and all of the model’s selected activity dimensions, including CURRENCY_CD must be included in the header row. Use headers provided – DO NOT CHANGE THE HEADER ROW.
All alpha characters within the body of the spreadsheet must be UPPER CASE.
Each ChartField member in the .csv file must already exist in Budgeting and Planning. If new ChartFields are needed for the upcoming biennium, follow the standard process to have the ChartFields created in FIN prior to usage in BPM.
Budget period amounts should not contain currency symbols, commas or decimal points. All dollar amounts should be entered in whole dollars.
Budget period amounts should be entered as values, not as formulas.
If the value for a budget period is zero, the user must enter a number zero (0) because a blank value will result in an error. No fields can be left blank in the file.
Below is an example of data expected for the AGYCAP and AGYRAP budget scenarios:
Chartfield in AGY Capital Model |
CAP Scenario |
RAP Scenario |
Account |
"570" |
"570" |
Fund_Code |
Fund |
Fund |
Product (ALI) |
ALI beginning with C |
ALI beginning with C |
DeptID |
3-digit AGY code |
3-digit AGY code |
Program_Code |
3-digit AGY code or "UNI" |
3-digit AGY code or "UNI" |
Class (Service Location) |
County "C0001" - "C0089" |
N/A |
Dimension 1 (Initiatives) |
"001" - "099" |
"001" |
Dimension 2 (Product Type) |
Choose from list |
"Community Project-Yes" or "Community Project-No" |
Dimension 3 (Priorities) |
N/A |
"Redirection-Yes" or "Redirection-No" |
Data Expected in each field on first tab |
|
| DeptID | 3-digit agency code |
| Fund_Code | Fund |
| Product | ALI |
| Account | Will always be '570' |
| Program_Code | Program will be the 3 digit agency code and should match the DeptID field. All colleges and universities will use 'UNI' |
| Class_FLD | Service Location code. Choose county code, C0001 though C0089 |
| Dimension 2 | Project Type. Choose from 'New Construction', 'Subsidy Capital', 'Basic Renovation', Site Development/Land Acquisition', 'Capital Equipment', 'Planning', 'Major Renovation', 'Information Technology', and 'Other' |
| Dimension 3 | Prioritization of capital requests. Choose from '001' - '099' for each row to tie priorities with multiple rows together. |
| CURRENCY_CD | Always 'USD' |
| 201911 | Full appropriation requested for the 2019-2021 biennium, dollar amount, no comma, dollar sign, or blanks. |
| 202111 | Amount planned for the 2021-2022 biennium, dollar amount, no comma, dollar sign, or blanks. |
| 202311 | Amount planned for the 2023-2024 biennium, dollar amount, no comma, dollar sign, or blanks. |
Data Expected in each field on first tab |
|
| DeptID | 3-digit agency code |
| Fund_Code | Fund |
| Product | ALI |
| Account | Will always be '570' |
| Program_Code | Program will be the 3 digit agency code and should match the DeptID field. All colleges and universities will use 'UNI' |
| Dimension 2 | Choose either "Community Project-Yes" or "Community Project-No" |
| Dimension 3 | Choose either 'Redirection-Yes' or "Redirection-No' |
| CURRENCY_CD | Always 'USD' |
| 201911 | Full appropriation requested for the 2019-2021 biennium, dollar amount, no comma, dollar sign, or blanks. |
The key to successful budget dataloads is accuracy in completing the spreadsheet template. The .csv file must meet the following requirements:
The first row of the .csv file must be a header row that names the ChartFields, then the budget periods to be loaded, in order. DO NOT CHANGE THE HEADER ROW -- this will create errors.
The ChartField names must exactly match those specified in the activity definition for the model; and all of the model’s selected activity dimensions, including CURRENCY_CD must be included in the header row. Again, DO NOT CHANGE THE HEADER ROW -- this will create errors.
Each ChartField member in the .csv file must already exist in Planning and Budgeting.
Budget period amounts should not contain currency symbols, commas, or decimal points. All amounts should be entered in whole numbers.
Budget period amounts should be entered as values, not as formulas.
If the value for a budget period is zero, the user must enter the number zero (0) because a blank value will generate errors.
The entire spreadsheet must be formatted as text. The template will be set up this way so that any leading zeros in the numbers entered will remain intact. It is a good idea to double check the formatting to avoid errors.
All alpha characters in the field entries MUST be upper case -- CAPITAL letters.
During the import process, the system checks that the data in the .csv file meets various requirements for Planning and Budgeting, such as ChartField combinations. Only rows that pass the requirements are imported. View the details for rows that do not import by viewing the Budget Data Load Error Report.
The method ID default will be updated for ChartField combinations provided in the .csv file. The method ID will be updated to AMTPER if the method override flag is enabled.
When the user loads a .csv file to a budget version, it will:
Replace any rows in the online budget version with what is in the .csv file for rows that have the same ChartField strings that are on the .csv file.
Add rows to the online budget version with new ChartField strings that are in the .csv file that were not already in the online budget version.
Do nothing to the ChartField string rows already in the online budget version that are not in the .csv file.
Deleting lines from the budget data load will not remove lines that already exist in BPM. It is necessary to go back to My Planning Workspace online to revise or delete existing ChartField strings.
This process locks the Planning Center. The Planning Center will automatically unlock when the upload process is completed.
BPM does not prevent multiple concurrent processes from writing data to the same planning center; if multiple import files are being processed for the same planning center, they should be run sequentially.
Both the .xls (or .xlsx) and .csv files can be saved to the location of the user's choice. It is suggested that the file(s) be saved in a location that is easy to remember and access by the user.
Below is an explanation of the field entries:
Planning Model ID "1920_CAP_AGY" is the AGY model used for CAP scenarios for the FY 2019-2020 biennium.
Scenario "1920AGYCAP" is the CAP Scenario for the FY 2019-2020 biennium.
Activity "AGYCAP" is the CAP Activity.
This example run control is for the "PWC" planning center. (This will be the 3-digit agency code.)
Note the version must already exist in BPM to be able to select it here in the run control.
Add the .csv file as the Source File.
IMPORTANT: When using this feature, the system will place the uploaded file into the version selected by the user. The Budget Data Load is both Incremental and Destructive.
Incremental:
Adds rows to the budget version with new ChartField strings that are in the .csv file that were not already in the budget version.
Does nothing to the ChartField string rows already in the budget version but not in the .csv file.
Destructive: If the ChartFields are the same but the amounts are different, then the amount will be overridden.
The spreadsheet upload will fail if the planning center that the file is loading has already been locked by another user. The planning center must be unlocked before the spreadsheet will upload.
Below is an explanation of the field entries:
Planning Model ID "1920_CAP_AGY" is the AGY model used for CAP scenarios for the FY 2019-2020 biennium.
Scenario "1920AGYRAP" is the RAP Scenario for the FY 2019-2020 biennium.
Activity "AGYRAP" is the RAP Activity.
This example run control is for the "ADJ" planning center. (This will be the 3-digit agency code.)
Choose the working version to load the .csv file. There are up to 36 working versions but we recommend you use Version 1.
IMPORTANT: When using this feature, the system will place the uploaded file into the version selected by the user. The Budget Data Load is both Incremental and Destructive.
Incremental:
Adds rows to the budget version with new ChartField strings that are in the .csv file that were not already in the budget version.
Does nothing to the ChartField string rows already in the budget version but not in the .csv file.
Destructive: If the ChartFields are the same but the amounts are different, then the amount will be overridden.
The spreadsheet upload will fail if the planning center that the file is loading has already been locked by another user. The planning center must be unlocked before the spreadsheet will upload.
