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. The steps outlined in this article are for users of Excel Smart View.
Essential Information
- KB0033728: How to Transfer Resources in OFC using Resource Commitment Data in EPBCS via the EPBCS web application is a separate article.
- 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
- Open Excel workbook and connect to EPBCS Planning environment.
- Login using Single Sign-On.
- In the Private Connections Smart View panel, expand each node in the connection panel by clicking the ‘+’ symbols until you see the Task Lists folder, then click on the ‘+’ symbol next to Task Lists to populate the list of commitment tracking forms.
- Double-click 7—Create and Manage Commitments to expand the Task List; this will result in a new view in the panel.
- Double-click Task 7.4 Review Commitments Journal Extract to render the form in the active Excel sheet.
- Review to make sure all expected records appear.
- 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).
- Open a new sheet in Excel - copy contents of Task 7.4 and paste as values into a new sheet - this new sheet of copied values is your Journal Extract.
- To copy contents, click in top-left corner of sheet (between A column header and 1 row header) to select entire sheet, then use Ctrl + C key combination.
- To paste as values, click in cell A1 of blank sheet, then use Alt + E + S + V key combination followed by Enter on your keyboard.
- Click on File located in the top-left corner and select Save As.
- Save in a location and with a name that is meaningful to you.
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 rows of content (excluding headers) from your cleaned Journal Extract and paste into Cell A7 of 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 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.