Tracker for Oracle

TRACKER REPORT DOWNLOAD

1. Open an internet browser, go to the Tracker website https://v4.tracker.us.com/Login.aspx and login with credentials below.

1. Username: viewall@okc.gov

1. Password: Viewall@1122

1. Click on the Reports tab at the top and hover over reports, when the drop-down menu appears, click on report groups.

1. You will need to look for the report group named All Accounting (Z) and click the edit hyperlink to the left.

1. After you click edit, a popup will appear that shows a listing of all investments in tracker. You need to make sure that every investment that begins with Z_ has a checkmark next to it.

1. Note: T_ are investments that are managed by Treasury and the investment entries for those are posted separately with TRE entries.

1. DO NOT INLCUDE INVESTMENTS FROM FUND 190X (PREVIOUSLY 019). 190X will be posted separately by grant accountant in grants module.

1. Once you verify that each investment has a checkmark, click submit.

1. You will need to once again hover over the Reports tab and this time you will click on All.

1. This takes you to a page with a long list of reports. 

1. You will then look for the report named ASD-TrackerExport and click on the view hyperlink to the left.

1. Fill in the information for the Begin and End date for the month you are needing to post.

1. Begin date is prior month and end date is current month.

1. For report group select All accounting (Z).

1. Leave the Index as is

1. Click view and tracker will update the page with the requested months report.

1. You will need to click on the Excel button and look at your downloads for the excel report. 

1. Click open file and when the excel report opens, selet the enable editing button on the excel file. Leave the excel file open as you proceed to the next step.

TRACKER UPLOAD TEMPLATE

1. Open a file explorer and navigate to the following folder using the path \\ci.okc\OKC\FI-ASD\Oracle.

1. Look for the excel file name OracleTrackerTemplate and open it up.

1. With the workbook open, look at the bottom of the sheet and locate the tab named Report. 

1. Select the report tab and you will see an empty but formatted page.

1. Note: The MV difference column (yellow fill) has a formula that calculates the difference between beginning and ending market value. This should be left alone as the formulas in other tabs pull information from this column.

1. Copy and paste over the data from the tracker excel spreadsheet, that you downloaded, into the blank cells of the Report tab.Exclude the headings from the Tracker report.

1. Paste in Cell A2

1. Include the Total / Average row also.

1. Make sure to delete any rows that do not have information listed or the old total line.

1. In this example, I deleted rows 77-79.

1. Also make sure to add any rows as needed by copying a blank row and pasting using the formulas option.

1. Next go up to Cell A1 (portfolio name) and click on the filter and select sort Z-A.

1. This ensures that most Trust investments are grouped together, and it also keeps the totals row at the bottom.

1. After you have pasted the data, the Oracle Journal Actuals tab will fill itself in as that tab pulls all required information with formulas.

ACTUALS JOURNAL

1. Click on the Oracle Journal Actuals tab and begin by looking at column C.

1. If you see any #N/A where the oracle accounting string should be, that means you will need to update the Portfolio_Oracle String tab.

1. IF YOU DO NOT SEE ANY #N/A (like the image below this step) SKIP TO STEP 26 …

1. Below is an example of an investment that needs its oracle funding string updated.

1. The next step is to go the Portfolio Oracle String tab and add the Revenue, Investment and Cash strings.

1. To begin you can copy over the tracker string from the Tracker report you downloaded earlier and paste it to column A.

1. Crosswalk to the new fund. The revenue, investment and cash string should be the same as PS.

1. This is currently done using the chartfield database with old PS funds.

1. Occasionally T-bills are purchased and require a different set of rules than Money Market accounts.

1. Most tracker investments are money market accounts as noted in Column B of the Report tab.

1. CD investments rules are the same as MM since they are considered level 1 investments (Same as Cash).

1. Non money market accounts require you to look at the beginning and ending cost value of the investments.

1. The beginning cost value is currently not on the ASD-TrackerExport report but will be requested to Treasury to add it to the report.

1. Below is the current list of accounts that typically have T-Bill investments.

1. They currently use conditional formatting for easy spotting and to let you know that an maccrual entry will also be needed.

1. DO NOT USE THE VALUES IN COLUMN M AS THE AMOUNTS FOR THE ACTUALS ENTRY, THOSE ARE THE BALANCES FOR THE MACCRUAL PORTION. YOU WILL FIND THE ACTUALS ENTRY FOLLOWING THE STEPS BELOW.

1. To find the beginning cost value, you can open the prior and current month bank statements from the support folder and calculate the difference between the ending balance in the prior month and the ending balance in the current month.

1. If the investments are the same, as in this case with T3 being T-Bill you can take the total from the statement instead of each investment change from beginning to ending balance.

1. If the investments are different, you can verify which balance goes to which investment by using the CUSIP number.

1. For the month of March, we can see that there was no change in the cost value so there will be no actuals entry for T3. 

1. You will now need to go the Oracle Journal Actuals tab and find T3 (for both investments) and delete the formula in column A and B as it is currently pulling in the wrong information and will cause #NA outages in the debit/credit column.

1. In this case, there was no change in RB so you would delete it also.

1. You will then need to delete any placeholder formulas in column A or B where it says no actuals entry needed.

1. After deleting the unnecessary information and formulas, you can go to Cells AR1 and AS1 to verify that the journal is in balance.

1. The cells use conditional formatting to let you know if the journals is in balance

1. Green = In balance

1. Red = Not in balance

1. If you see #NA in the total’s cells, you will need to find the formula that is not pulling in a number and see if any corrective action is required.

1. The journal entry criteria such as journal date, posting date and batch name will automatically update. 

1. The journal should now be ready to post.

MACCRUAL JOURNAL

1. The Maccrual tracker entry is not as automated as the Actuals journal and will require you to manually add the tracker stringer on the Oracle Journal Macc tab in column A when a new non money market account is added.

1. There are currently only 3 investment portfolios that require a Maccrual entry. To help identify these investments, conditional formatting was used to identify duplicates and highlight the tracker string green.

1. To identify any new non money market accounts, you can look at the description in column B of the Report tab.

1. If new non money market investments are purchased in the future, you will need to add those to the list starting at row 107 of the report tab.

1. You can now go to the Oracle Journal Macc tab and begin building your Macc journal.

1. You will notice that there areprefilled tracker strings for T3, RB, and Special Purpose.

1. If you were to have any new investments, you would manually add the tracker string in Column A and you could then copy over the formulas that are in columns B and C.

1. For T-bills and other like investments, the revenue string in column B looks at Column M of the Report tab and takes the change in market difference for the journal entry.

1. For the CD, the revenue string looks at Column L of the Report tab which is the difference in accrued interest calculated by Tracker.

1. Once that information is entered, the Journal Lines to right build themselves with formulas and the Journal is now ready to be posted with a FDBI spreadsheet upload.

1. Oracle will let you put the actuals and maccrual journals on the same spreadsheet.

JOURNAL UPLOAD

1. To upload the Journal, follow Test Script GL.TS.001.004 FBDI Journal.

1. Begin at step 36 of the Test Script.