Overview
This article explains how to export Commitment data from the Resource Commitment Tracking Tool (RCTT) via Task 7.4 Review Commitments Journal Extract, translating exported data to the appropriate format for using a Journal Spreadsheet to transfer resources in OFC, and how to submit the resulting Journal in OFC. This guide is catered to users of the EPBCS web application.
Essential Information
- Please note, KB0033728: How to Transfer Resources in OFC using Resource Commitment Data from EPBCS using Smart View.
- The Resource Commitment Tracking Tool is only available to Financial Units at Level B or above within the Financial Unit Hierarchy.
- Resource Commitments are recorded in EPBCS, but resource transfers occur in OFC.
- Task 7.4 Review Commitments Journal Extract in EPBCS provides a current month view of resource Commitment transactions under a selected POV - the month is set by the Systems Administrator and cannot be changed by a user.
- The Journal Extract Translation File translates data from Task 7.4 to the appropriate format for OFC upload via Journal Spreadsheet.
- These steps should typically only be followed by the Source of the Commitment, who is responsible for journaling both sides of the Commitment - Destinations should only journal Commitment records if they have coordinated with the Source to do so on their behalf.
Next Steps
Generating Monthly Resource Commitments Journal Extract from EPBCS
- Go to EPBCS.
- Login using Single Sign-On.
- On the EPBCS Home Page, click on the Tasks tile.
- Click on the caret to the left of 7 - Create and Manage Commitments to expand the Task List.
- Click on Task 7.4 Review Commitments Journal Extract.
- Within the POV, select the appropriate Financial Unit from which your extract should be based.
NOTE: If you wish to bring in all records under your security rights, choose FU_16000AA: UC San Diego, otherwise, select any hierarchy member (including parent member) to which you have access. - At top-right of the screen, click the Actions button to reveal the Action Menu.
- Select Spreadsheet Export from the Action Menu.
- Within the Spreadsheet Export window, click the Export button.
- Check your computer’s local downloads and open the exported Journal Extract Excel file, typically named HspWebGrid.xlsx
- Save a copy of the exported Journal Extract file in a location and with a name that is meaningful to you - you may have to select Enable Editing before you are able to do this.
- Review records in the spreadsheet export file to make sure all expected records appear - if records are missing, troubleshoot and repeat Steps #1-11 above.
NOTE: Common reasons for records not appearing on Journal Extract include: incorrect POV selection or records fall outside your security rights (i.e., destination is in another VC area).
Translating Journal Extract File from EPBCS to OFC Format
- Clean your Journal Extract file so that only records you will be journaling appear, keep in mind the following:
- All remaining records should have a CT Pushed value of Yes.
- Each CTID should have at least two records, typically Base Commitment and Transfer In Commitment, sometimes Reclass Commitment in your Journal Extract file.
- If you only have a Base Commitment record for a CTID, then you will need to manually construct the Transfer In record for that CTID - this row should have Destination elements (FinU/Fund/Function/Project) and opposing CT Amounts from the Base Commitment record, i.e., if CT Amount for Base is a positive number in the Debit column, then CT Amount for Transfer In is a negative number in the Credit column.
- If you have a Transfer In Commitment on your extract without a corresponding Base Commitment record, you should remove that record from your Extract prior to translation, as that record should be journaled by the Source.
- Ultimately, unless coordinated otherwise, resources should only be transferred by the Source unit.
- Download the RCTT Journal Extract Translation File, and save a local copy in a location that is meaningful to you.
- Copy the content from your cleaned Journal Extract and paste into the Extract worksheet within the Journal Extract Translation File.
- Navigate to the Translation worksheet - this worksheet contains formulas that automatically translate data from the Extract worksheet into the necessary format for processing OFC Journals.
- On the Translation worksheet, verify that all of your data have populated correctly.
NOTE:
- OFC Journal Spreadsheets only have a single column for Debits and a single column for Credits, and values must be recorded as positive numbers in both of these columns.
- The Translation tab formulas combine standard amounts and monthly adjustment amounts in the appropriate column, and flip signs as appropriate.
Preparing OFC Journal Spreadsheet
- Go to OFC.
- Login using Single Sign-On.
- From the OFC Home Page, click on General Accounting in the ribbon, and then click the Journals tile.
- On the Journals screen, click on the Tasks menu on the right-side of the page.
- Click the Create Journal in Spreadsheet link, then a new Journal Spreadsheet template will download.
- In the Opening File dialog box, to open the Journal Spreadsheet template file, select Open with Excel, then click OK.
NOTE:
- If you are not prompted to connect, you may need to download the latest adfdi Desktop Integration from OFC.
- You can do this by opening the Navigator menu at the top-left on any OFC page, from the Tools menu select Download Desktop Integration.
- Establish connection with OFC by clicking the Company Single Sign-On button and entering your AD credentials.
- Once connected, save the file in a location and with a name meaningful to you.
- In the Review ribbon, select Unprotect Sheet to Unprotect the Single Journal spreadsheet.
- Navigate back to your Journal Extract Translation File, and copy all cells with relevant data (including header) from the Translation worksheet - stop copying when you get to #N/A values in Column A.
- Navigate back to Journal Spreadsheet template file and paste contents as values on Single Journal worksheet, beginning in cell E17.
- Complete the required information at the top of the Single Journal worksheet as described below:
- Journal: must be a unique name in the system, typically shorthand that is easily searchable.
- Description: can be the same as Journal, or provide more descriptive text.
- Ledger = UCSD
- Accounting Date: can be any date within the period you wish to transfer resources.
- Category = Manual
- Save your OFC journal file.
Importing OFC Journal Spreadsheet
- When ready to import, navigate to the Create Journal ribbon at the top of the page, and click Submit.
- In the dialog box, in Submission Option, select Submit Journal Import, and for Import Descriptive Flexfields, select Yes without validation.
- Click Submit - if your import is successful, you will receive a confirmation message indicating your submission was successful.
NOTE:
- If any of your rows are unsuccessful, the entire file submission will fail.
- If any of your rows fail, you will receive a message indicating this.
- Reasons for error(s) are populated in the Error column on the Journal Spreadsheet in the relevant row.
- Review any errors, correct, and resubmit using Steps #1-3 above.
- After successful submission, return to OFC to review your imported Journal and Submit for approval/posting.
- From OFC Home Page, click on General Accounting in the ribbon, and then click the Journals tile.
- On the Journals page, click on the Tasks menu on the right-side of the page, and select Manage Journals.
- Search for your Journal using the Journal or Journal Batch fields - make sure you have the appropriate Accounting Period selected when searching.
- Select the appropriate Journal from your search results window, and review the Journal Transaction lines for accuracy.
- When ready, click the Post button near the top of the window - this will not post the Journal, it begins the workflow to route to the relevant approver as only Internal Controls & Accounting posts Journals.