Load Journals From a Flat File
Purpose: Use this document as a reference from loading journals from a flat (Excel) file in ctcLink.
Audience: Commitment Control staff.
You must have at least one of these local college managed security roles:
- ZZ CC Budget Entry
- ZZ CC Budget Processing
If you need assistance with the above security roles, please contact your local college supervisor or IT Admin to request role access.
Load Journals From a Flat File
Budget Upload Template
An Excel template was created to help with the input of large quantities of budget lines. Keep the format as is for the template to work properly. Not every column needs to be populated. We have highlighted the fields with green color for which you need to provide the details. Here is the Budget template:
Enter Budget Header Journal Details
- Here is the list of Header fields that have to be updated with current information:
- Business Unit must reflect your college, i.e. WAxxx. Enter your college Business Unit.
- Journal Date is the Budget Journal date. It is also a Budget date. Update the correct Journal date in the format MMDDYYYY (ex. 07012023 for July 1, 2023).
- Enter Budget Entry Type / Parent Budget Entry Type .
- Select ‘0’ Original if this is an original budget entry being made for the first time.
- Select ‘1’ Adjustment if this is an adjustment to an original budget amount.
- Description: Enter Budget Journal Description in short.
- Enter a detailed description of the budget in the Long Description field.
-
Enter either PERMANENT, TEMPORARY or EARMARK in the Alternate Description field.
- Permanent - The budget for that year.
- Temporary - Adjustments that are not intended to be carried over to the next year. It does not matter if it is a + or -.
- Earmark - Special budget for a specific directive spending. This is a variety of things, such as disability services or apprenticeship services.
- This field is required for reporting purposes. This will allow the budget entries to report in a query format using QFS_KK_BUDGET_EXPENSE_BALANCE. Enter separate Budget Journals based on the type of budget described in the Alternate Description.
Enter Budget Journal Line Details
- Data Flag for Lines Enter an “L” for each row of data.
- Journal Line It should be incremental.
- Enter Account This is the Account of the budget. It will be detail account level.
- Enter Department ID This is the Department where the budget resides.
- Enter Operating Unit This is college code.
- Enter Appropriation This is the Appropriation Index.
- Enter Fund Code This is the PeopleSoft Fund code.
- Enter Class This is the NACUBO (Program Code).
- Enter Budget Period This is the Fiscal Year of the budget period i.e 2023 or 2024.
- Enter Line Description (Optional) Provide line description. This is optional field. If you leave this field blank, then it defaults the value from Account description for each line.
- Enter Base Currency Amount This is the amount you wish to budget.
When finished with the Budget Import Template:
- Save the excel file in your local folder.
- Save as a file with CSV (comma delimited) format in same local folder. (DO NOT OPEN THE CSV FILE DIRECTLY WITH EXCEL). If you open the csv file in excel or double select the csv file then you loose leading zeros on numbers value from cell.
If you want to open the csv file then open in notepad/wordpad or any other text editor tool or right select on csv file and select edit.
Importing a Budget Template
Navigation: Commitment Control > Budget Journal > Import Budget Journals
- The Import Budget Journals page displays. If you have run this process or report in the past, select the Find an Existing Value tab to enter an existing Run Control ID and select the Search button. If this is the first time running this process or report, select the Add a New Value tab to create a new Run Control ID and select the Add button.
- NOTE: It is important to note that Run Control IDs cannot be deleted. Do not include spaces in your Run Control. We encourage the Run Control ID to have the same process naming convention but unique to the step; because of this, including your institution code and a short process description in the Run Control ID is recommended--e.g., WA040_BUDGET_UPLOAD (College, Process).
- The Import Commitment Control Budget Journals page displays.
- Enter the Journal ID Mask is setup as "BD_"
- Select Add Button. A new File Attachment window will pop up.
- Select Choose File button.
- Select the .csv file from your local folder and then the Open button to add the file as an attachment.
- Select the Upload button.
- Your .csv file name will be listed next to 'Attached File'.
- Select the Run button to run the 'Flat File Budget Jrnl Import' process.
- Select OK. Note that a Process Instance number appears. Select Process Monitor link and monitor the process.
Select View button to view the file content. If the file didn't look good or wasn't correct, select the Delete button to delete the file and add the correct file again.
- Use the Process List page to view the status of submitted process requests. Please refer to the Process Schedule Request steps for further instructions.
- Continue to select the Refresh button until the Run status is Success and Distribution status is Posted.
- Select the Details link.
- From the Process Detail page, select the View Log/Trace link.
- The View Log/Trace window opens. Select the KK_JRNL_IMP_XXXXXX.LOG Link.
- You will see the Budget Journal detail, which is created from this process.
- Note down the Budget Journal ID and proceed to the next step QRG Budget Pre-check and Submit the Budget Journal for Approval.
Video Tutorial
The video below demonstrates the process actions described in the steps listed above. There is no audio included with this video. Select the play button to start the video.
Video Tutorial Via Panopto
View the external link to Load Budget Journals From a Flat File. This link will open in a new tab/window.
0 Comments
Add your comment