Troubleshooting and Adjusting for Changes to the Daily Budget and Subject Reports
When there appears to be differences between what is in Sirsi and what is in the budget report, it is necessary to track where the differences begin appearing in the process. It is recommended to work backwards, beginning with the DBR tables that serve as sources for the “Daily Budget Report.” From there, determine if the error is also present in the SirsiFundData table which is the source of the DBR tables, and the Raw Sirsi Fund Data table in the DPP Fund Statistics database, which is the source of the SirsiFundData table. If the error is present in the Raw Sirsi Fund Data, there is an issue with the Wfdata file and Dawn Pearce can help to identify the source of the problem since she maintains the automated script that creates the Wfdata file.
Wfdata file (G: Drive) > Raw Sirsi Fund Data (DPP Fund Statistics database) > SirsiFundData (local table in DPP Fund Statistics database and a linked table in the other DBs) > DBR tables (local table in DPP Fund Statistics database and linked tables in the other DBs) > Daily Budget Report (all databases)
--an allocation is made in PS but not pulled into our PS Budget query ("Journal Line Description" Criteria edit may be needed in each private Budget query)
--expenditures total (and other totals) in PS do not match ours (accounting period edited too soon – especially at beginning of January after holiday break; run DBR_FULL_YEAR_RESET query, instead of DBR_CURRMONTH, to capture ALL current FY PS transactions; some current month's expenditure data can also be lost if there is a system/server failure). See DBR Full Reset Query instructions.
Making Adjustments to the daily budget and subject reports:
1. Adding Funds to A Selector’s Subject Report
Periodically, Collection Management may request a change in fund code manager or that a new fund code be added to the budget and subject reports. Add the subject portion of the fund code (the last four characters after the dash) along with a description and the last name of the collection manager as a new row to the “Collection Managers” table in the DPP Fund Statistics database. The changes will be reflected next time the Daily Budget and Subject reports are run. A comprehensive list for collection managers' assignments can be found on the Collection Management Department Fund Codes page.
2. Adding Endowments
If new endowment is created, it will need to be added to the “Endowments, Gifts, Grants Names - Edited” table for it to show up on the “DBR - Endowments, Gifts, Grants Individual Funds” table. Generally, the head of Monographs notifies DPP whenever a new endowment is created and the “Funds Missing From Endowments Name Table” query will also show any endowments that need to be added.
3. Making Changes to the Daily Budget Report
Collection Management or the A & D department head may request other changes in the daily budget report, typically at the beginning of a new fiscal year. Edit the appropriate DBR SQL queries in the DPP Fund Statistics database that create the DBR tables in the database. If the DBR - State Money Query is updated, the DBR - Branch Fund Statistics Query should be updated with the same changes or it will be out of sync with the overview section of the the Daily Budget Report.
Database Error messages when running the daily reports :
-- Occasionally the names of fields in the PeopleSoft reports are changed (mostly following system upgrades) which causes some queries in the DPP Fund Statistics database to fail because they can’t find the previous field name in the new file. These parameter errors occur when attempting to run and export the daily budget report. To fix this, compare the new PeopleSoft report to the appropriate DBR query and update the SELECT portion of the query with references to the new field names. For example, with the PS Financials 9.2 upgrade in 2017, vendor data was relabeled as supplier data, so the DBR query to create a new PS voucher table was edited, where "name" was replaced with the new "Supplier" field, and "vendor" was renamed "Supplier1". Parameter errors may also occur if the top heading line in a spreadsheet exported from a PS query was not deleted.
-- Another error may occur after an upgrade if the Financials naming convention for an Excel sheet changes, as when sheet tab "1" is renamed "sheet1". If this happens, the five linked spreadsheets in the DPP Fund Stats database need to be re-linked to the source data in the Input Data folder. In the database, select External Data, import and link an Excel sheet, click the Link to the data source radio button, and choose the file path to the Input Data folder. Name the new table the same name as the original, e.g. PeopleSoftBudgetExcel, so that it replaces it. To ensure the file name is now correct, right click on the new table name, select Linked Table Manager, and check the file path. It should end with "\sheet1$" instead of "\1$". Repeat the process for the other four PS sheets (CurrentMonth, Encumbrances, P-cardTransactions, & Vouchers) that have been imported into the database.