Daily Budget and Subject Reports
Each morning at 6 AM, an automated script exports Sirsi fund information into a delimited text file (Wfdata.txt) located at G:\Acquisitions & Discovery\ILS\UnicornReports\Budget_reports[Current Fiscal Year]\. Dawn Pearce maintains the automated script and can help troubleshoot if there is a problem with the Wfdata file. This file is incorporated into the DPP Fund Statistics Database as a linked table named “Raw Sirsi Fund Data.” The linked table is updated each time the Wfdata.txt file is overwritten by the automated script in the morning.
The “Sirsi Fund Breakdown Query” transforms the “Raw Sirsi Fund Data” linked table into the “SirsiFundData” table by doing some minor processing, such as changing -1 to 0 (which is caused by an issue with the export script created by Dawn) and generating a series of columns based on information in the fund code which are used in the budget report (e.g. Location, Funding, etc.). The “Sirsi Fund Breakdown Query” is one of many queries triggered by the module associated with the “Run Daily Queries and Export Budget and Subject Reports” button on the dashboard.
After the “SirsiFundData” table is overwritten by “Sirsi Fund Breakdown Query,” other queries triggered by the “Run Daily Queries and Export Budget and Subject Reports” button overwrite the various “DBR” and “DSR” tables based on the updated “SirsiFundData” table.
These “DBR” and “DSR” tables are the source data for the “Daily Subject Report” and the “Daily Budget Report” and its associated subreports, with the exception of the “PeopleSoft Budget Totals” line, which is populated by the “PeopleSoftBudget,” “PeopleSoftEncumbrances” and “PeopleSoftTransactions” tables. As the source data is changed each day, the content of the reports changes and PDFs of the “Daily Subject Report” and the “Daily Budget Report” are exported to G:\CMACQ\Budget Reports[fiscal year] to provide a snapshot of each day’s budget report. See also Troubleshooting and Adjusting for Changes to the Daily Budget and Subject Reports and DBR Full Reset Query.
PeopleSoft vs. Sirsi Query - Invoices that appear in PeopleSoft and not Sirsi. Invoices often appear in this query due to a slight difference in the invoice number used in Sirsi and the one used in PeopleSoft. If it is discovered that the invoice is present in both systems but there is a difference in the invoice number, the primary reconciler will exclude both invoice numbers by adding them to ExceptionsSirsiPeopleSoftInvoices table (in Matt's database). This query should be run every day to create the authoritative PeopleSoft vs. Sirsi table in the primary reconciler’s database that all of the other databases are linked to.
Sirsi vs. PeopleSoft Query - Invoices that appear in Sirsi but do not appear in PeopleSoft. It generally takes a few days for an invoice to show up in PeopleSoft after it is created in Sirsi so this query will always have the most recently created invoices in Sirsi. This query only brings in voucher invoices; credit card invoices should not be included in the results because PeopleSoft uses different invoice numbers for P-card transaction that will never match the invoice number used in Sirsi. If an auto-generated invoice number (INV-####) is found in the results, this might indicate a credit card purchase. It's probable that a note saying "credit card" was not included in the invoice data, so the P-card transaction was not excluded from the query results, as it should have been. Check the P-card Transactions table for the amount in question and if it is a P-card invoice, ask the appropriate unit manager to add the credit card note to the invoice for future reference. If you are reasonably sure it's a pcard transaction but it can't be found in PS, check with someone who has access to pcard reconciliation, since there may have been a debit/credit situation that would result in two separate transactions whose balance would total the Sirsi invoice amount.
Also note any invoices older than 30 days in this query, these may also require investigation: A voucher created for a Sirsi invoice may not have been submitted for approval in PS (see Checking for incomplete vouchers), or a gift fund code may have been included along with deposit accounts under the Sirsi fund summary (while the deposit accounts would be ignored by the query, a code such as LGEJL would show up in the query results). The Sirsi vs. PeopleSoft query should be run every day to create the authoritative Sirsi vs. PeopleSoft table in the primary reconciler’s database that all of the other databases are linked to.
Some older Western Union vouchers using foreign currencies may also appear in this query. Often, these invoices are actually in PeopleSoft, but are under a different invoice ID that will never match was is listed in Sirsi. These invoices usually (but not always!) appear listed in the PeopleSoftTransactions table as the invoice number in Sirsi, but prepended with "TR####". It's also important to note that, due to these invoices using foreign currencies, there will also likely be an amount discrepancy between Sirsi and PeopleSoft. For these types of invoices, it's therefore not always useful to look up the invoice amount in PeopleSoftTransactions when you don't know the invoice ID. Instead, it's more useful to search for a range of amounts using the "Between" filter in the PeopleSoftTransactions table; for example, if a Sirsi invoice amount is $332, you could search for amounts between $300 and $400 to locate the corresponding invoice in PeopleSoftTransactions. A final verification step would be to look up the transaction in the WRS and search for the original invoice image and verifying the correct amount that should be listed in Sirsi. Once you have collected this information, contact the appropriate unit manager to change the invoice amount in Sirsi to what is reflected in the WRS, and add the Sirsi invoice ID to the Exceptions table, since it will never match the invoice number listed in PeopleSoft.
Reconcile Report - The reconcile report compares the total expended amounts in Sirsi and PeopleSoft and factors in several types of transactions that take longer to post in either Sirsi or PeopleSoft, which can explain the difference between the expended amounts recorded in both systems. This report is dependent on information from the following queries Sirsi vs. PeopleSoft, PeopleSoft vs. Sirsi, ILL Monthly Charges Query, Kindle Monthly Charges Query, Lost Books Monthly Charges Query, and Pending P-card Charges Not In PS Query.
Reconcile Report By Account Number Query - Similar to the “Reconcile Report,” this query compares the amount spent against each account number in the Sirsi Fund Data table and PeopleSoft Transactions and takes into account the amounts in the Sirsi vs. PeopleSoft query. When investigating discrepancies in the Reconcile Report, this query can help users identify the account numbers that are affected by these errors. One culprit often uncovered is a pcard purchase reconciled with an incorrect account number, so a JV will be required. Investigate by estimating the time period for PS pcard transactions that would total the discrepancy listed for each account in this query. Then use those transaction amounts to match against any invoices found in SirsiInvoicesPOsCurrentFY (filtered for any kind of credit card note or comment). Search the Sirsi fund summary for each invoice to verify the account number. Another problem, although more rare, could be a credit was issued in the current fiscal year for a pcard transaction that occurred in the previous fiscal year. If the item was never received, there would be no corresponding Sirsi debit amount to match to the original PS charge. This kind of discrepancy can show up in the first few weeks of the new fiscal year. Check with the pcard reconciler or the Monographs specialist for help with investigating the situation.
Endowment Reconcile Query - Compares how much has been spent on endowment project IDs in Sirsi and PeopleSoft. If there are differences between the amounts in both systems, invoices paid against those project IDs should be investigated for errors. Sometimes discrepancies appear as a result of timing differences between Sirsi and PeopleSoft, so the Sirsi vs. PeopleSoft and the PeopleSoft vs. Sirsi queries should be checked first if the amounts do not match in this query. Discrepancies in the 679862 project ID may be related to VetMed cash gift purchases, like Purrdey Haven, which are documented at VML Gift Purchases. For these, purchases are made on 679862 but F&B will eventually IDT money back into this account after paying from a VML funding source (Animal behavior service – 682237). These IDT credits will appear in PS Transactions, but the debits from the VML funding source won't be picked up in our queries.
NOTE: At the end of the fiscal year, a Sirsi account total expenditure should not be greater than a PS account total expenditure (Diff would be a positive number in this case); this situation requires investigation. Conversely, if a PS account total expenditure is more than what has been spent in Sirsi, it just means that non-A&D-related purchases have been made on the account. Notify Maria of the Endowment account statuses at the end of the fiscal year. She oversees any account balance resets that need to happen in Sirsi at the beginning of the new fiscal year.
Compare Sirsi Invoices to Fund Data Query - Compares how much is spent against each fund code in the Sirsi Invoices Current FY table and the Sirsi Fund Data table. This query helps users verify that the daily Sirsi invoice reports are not failing to export invoices, given that certain errors prevent invoices and orders from exporting properly. When these errors happen, discrepancies will appear between the two tables in this query. The Sirsi Fund Data table is based on the Wfdata file (G:\Acquisitions & Discovery\ILS\UnicornReports\Budget_reports) which is created by an automated process every day at 6:00 AM.
There are several reasons that discrepancies may appear in this report:
- Invoices created that day - If the daily Sirsi Invoices Current FY report are run later in the day, discrepancies may show up when invoices are created between when the Wfdata file is created and when the Sirsi Invoices Current FY report is run.
- Fiscal Cycle “0” Invoices - This Sirsi report can help users detect invoices that have been created with a Fiscal Cycle of “0” because the amount charged would be counted in the Sirsi Fund Data table but the invoices would not appear in the Sirsi Invoices Current FY table. Users can run the Fiscal Cycle 0 Invoices report in Sirsi and any invoices that appear should be corrected by Monographs or Serials staff.
- Over Distributed Lines - Over distributed order lines can cause duplicate invoice lines to appear in reports and these errors can be identified with the Over Distributed Lines Query.
- Orders Not Attached to Bib Records - If an orderline is not attached to a bib record, any invoice line that is attached to that orderline will not export as a part of the daily Sirsi invoice report. The Orders Not Attached to Bib Records will identify any of these orderlines that need to be corrected.
Default Account Errors (Supplies) - Tracks expenditures made against default Supplies accounts (52300, 52600, 52821, 52900) that should be against collections accounts. These will require JV requests to Finance & Business so that SumTotal column is $0. See Reconciling Default Account Errors (Supplies) for more details.
Invoices With Account Mismatch Between Sirsi and PS - This query reports invoices that are paid against different accounts in Sirsi and PeopleSoft. These discrepancies are usually resolved by asking for an invoice to be JVed to the correct account number in PeopleSoft. This query excludes invoices found in the “Invoices Paid Across Multiple Accounts” query because they appear as errors even when they are correctly paid. If invoices are incorrectly picked up by the query (often due to PeopleSoft irregularities), the invoice number can added to the Exceptions Sirsi PeopleSoft Invoices table and it will no longer appear. This query should be run at least every week to create the authoritative Invoices With Sirsi Vs PS Account Mismatch table in the primary reconciler’s database that all of the other databases are linked to.
Sirsi Vs PeopleSoft Invoice Amount Reconcile Query - Compares the total amount paid on an invoice in both Sirsi and PeopleSoft for voucher transactions. Discrepancies should be investigated to see if there are amounts that were entered incorrectly or credits that need to be applied in Sirsi or PeopleSoft.
One possible discrepancy is if an invoice line in Sirsi doesn't contain a title associated with the amount paid, causing an amount discrepancy to appear. To verify this, first check the "Orders Not Attached To Bib Records" query, and compare the results to the Invoice Amount Reconcile query. If there are matching invoice IDs, look up the invoice in Sirsi to find the invoice line missing a title, and let Monographs or Serials know which invoice line is causing the problem. Also check the Overdistributed Lines query, since Sirsi invoice line totals of a given invoice may not match the total invoice amount (e.g. annual EBSCO renewals that often have more than 200 invoice lines might need additional invoice line clean up post PS processing).
Also, discrepancies will appear if the daily Sirsi invoice reports are not exporting all invoices, so any results in this query should be compared to discrepancies found in the Compare Sirsi Invoices to Fund Data Query. If invoices are incorrectly picked up by the query (often due to PeopleSoft irregularities or if an invoice was JVed to another account), the invoice number can be added to Exceptions Sirsi PeopleSoft Invoices table and it will no longer appear. This query should be run at least weekly to create the authoritative Invoices With Sirsi Vs PeopleSoft Invoice Amount Reconcile table in the primary reconciler’s database that all of the other databases are linked to.
Deposit Fund Invoices Paid in PS Query - Invoices paid against deposit funds should not appear in PeopleSoft because they have already been paid for as a part of the annual EBSCO deposit invoice. If deposit invoices are paid in PeopleSoft, the Serials unit should be notified so they can be removed. If an invoice has some lines paid against state funds and some lines paid against deposit funds, the invoice will appear in this query even if only the state money invoice lines appear in PeopleSoft. To remove these invoices from the query, the invoice number can be added to Exceptions Sirsi PeopleSoft Invoices table. This query should be run at least weekly to create the authoritative Deposit Fund Invoices Paid in PS table in the primary reconciler’s database that all of the other databases are linked to.
Incomplete Vouchers - If a voucher is created but not submitted for approval, it will not appear on the PeopleSoft Transactions table. Finance & Business sends out a request to clean up these vouchers at the end of the fiscal year but we try to correct them each month to make sure that all of our vouchers have been submitted properly. This query is found in the PeopleSoft Query Manager. See Checking for incomplete vouchers.
ILL Monthly Charges Query - Compares the amount paid on the monthly ILL invoices in Sirsi and the ILL charges on the 55691 and 53600 accounts in PeopleSoft that are used to generate the amount of the ILL invoice in Sirsi.
Kindle Monthly Charges Query - Compares the amount paid on the monthly Kindle invoices in Sirsi and the Kindle charges in PeopleSoft that are used to generate the amount of the Kindle invoice in Sirsi.
Lost Books Monthly Charges Query - Compares the amount paid on the monthly lost books invoices in Sirsi and the lost book charges on the 55697 accounts in PeopleSoft that are used to generate the amount of the lost books invoice in Sirsi.
OCLC Charges - Compares the amount paid on the monthly OCLC invoice in Sirsi and the OCLC charges in PeopleSoft to determine if the discount for early payment (if received) is reflected in Sirsi.
What's Missing In Budget Report By Individual Fund Query - Reports funds with expenditures in the Wfdata data file that are not appearing in the Daily Budget Report and the Budget Report By Individual Fund queries. If funds appear in this query they should be added to both budget report queries.
Funds Missing From Endowments Name Table Query - Compares the “Endowments, Gifts, Grants Names - Sirsi” table to the “Endowments, Gifts, Grants Names - Edited” table to see if any new funds need to be added to “Endowments, Gifts, Grants Names - Edited.” The “EndowmentsGiftsGrantsNames-Edited” table provides a cleaner version of the endowment name than what is exported from Sirsi into the “EndowmentsGiftsGrantsNames-Sirsi” table.
Funds Without Accounts Query - Tracks Sirsi fund codes that still need to be assigned a PeopleSoft account number.
In addition to database related tasks, international transaction fees and currency conversions are also handled in a separate datasheet shared between DPP, Monographs, and Serials and reviewed on a monthly basis. See Reconciling Invoices with International Transaction Fees & Currency Conversions.
Consult When Discrepancies Appear
Book Store and Other NCSU PeopleSoft Transactions Query - Tracks interdepartmental transfers in PeopleSoft in order to track NCSU book store orders. Not all interdepartmental transfers are related to book store purchases however and some transactions in this query may need to be ignored if this query is being reconciled against Sirsi textbook orders. If non-book store transfers ARE included in the query results, and are not on 55600, further investigation needs to be done. See Interdepartmental Transactions (IDTs).
Budget Report By Individual Fund Query - Displays each fund code with the section (Fund and Fund Group) of the budget report in which the fund code appears. This query should be updated whenever changes are made to DBR - Budget Report Query in the DPP Fund Statistics database.
Fiscal Cycle 0 Invoices Report In Sirsi Query - This Sirsi report is a template saved as “Fiscal Cycle 0 Invoices.” Sometimes automated invoice loading fails in Sirsi and invoices are created a Fiscal Year of “0”. These invoices will not be picked up in the daily invoice reports and need to be corrected so that they will be picked up the next time the invoice report is run. This query should be consulted when there is a discrepancy in the Compare Sirsi Invoices to Fund Data.
Invoices Paid Across Multiple Accounts Query - Tracks invoices with lines that are paid against fund codes associated with multiple project IDs/account numbers. These invoices are a common cause of reconciliation problems and this query can be useful when investigating errors.
Invoices Paid Against State Money And Deposit Fund Codes Query - Tracks invoices with lines that are paid against both state money and deposit fund codes. While these invoices are not necessarily errors, they can often be a source of reconciliation problems between PeopleSoft and Sirsi, so this query can be consulted when trying to find the source of discrepancies.
Non-A&D P-card Users Query - This query tracks P-card transactions paid against the 214050 project ID that charged to P-card users other than Maria Collins, Lynn Whittenberger, Kristen Wilson, and Alex Reher (his P-card is used to purchase Kindle titles). These P-card transactions make take longer to longer to post as charges on the PeopleSoft Transactions table because other department do not reconcile their P-cards as frequently as Acquisitions & Discovery. It is also possible that these transactions should not be paid on the 214050 project and some follow-up may be required with the P-card user.
Pending P-card Charges Not In PS Query - Tracks P-card transactions paid against the 214050 that are not yet considered to be paid in PeopleSoft. P-card charges first post to the PeopleSoft P-card Transactions table and once the charges are reconciled they will appear in the PeopleSoft Transactions table.
Sirsi Textbook Orders Query - Tracks all orders placed on the LSFIR-TEXT fund code, which is exclusively used for textbook purchasing. This query captures orders placed with the NCSU Book Store and other vendors such as Amazon.
Textbooks Order Cleanup Query - Tracks open textbook orders. This query is run at the end of a textbook purchasing cycle to assist the Monographs unit in cleaning up any open orders.
Unpaid Invoices With PO Data Query - Tracks Sirsi invoices that have been created without being paid and includes PO data.
Unpaid Invoices Query - Tracks Sirsi invoices that have been created without being paid.
Unpaid PeopleSoft Vouchers Query - Vouchers have not yet been paid by check or electronic transfer. For the purposes of the budget report and reconciliation, we are generally not concerned about whether has been made to the vendor, we are only concerned whether the transaction has posted and is considered spent in the PeopleSoft budget. However, this query is useful in case there is a question about whether a payment has been issued to the vendor.
Western Union Foreign Currency Vouchers Query - Tracks Western Union transactions which involve international transfer fees. This transactions are a common cause of discrepancies between Sirsi and PeopleSoft. David Applegate from the Monographs units monitors this query to update Sirsi invoices with the accurate amount after Finance and Business has processed the wire transfer.
For Monographs and Serials
Cancelled But Rolled Orders Query - Sirsi orders that were cancelled in the last fiscal year but rolled over to the current fiscal year.
Change Invoice To Current FY Query - Invoices paid against the previous fiscal that are attached to orders in the current fiscal cycle. Invoices should always be linked to orders in the same fiscal cycle.
Change Order To Current FY Query - Orders in a previous fiscal cycle attached to invoices in the current fiscal cycle. Orders should always be linked to invoices in the same fiscal cycle.
Encumbered But Cancelled Query - Sirsi orders that have been cancelled still have an encumbrance.
Encumbered But Paid Query - Sirsi orders that have been paid but still have an encumbrance.
Orders Not Attached To Bib Records Query - Sirsi orders that have become unattached to bibliographic records.
Not Received After 30 Days Query - Sirsi orders that have not been received after 30 days but less than 90 days.
Not Received After 90 Days Query - Sirsi orders that have not been received after 30 days
Not Received But Invoiced Or Paid Query - Sirsi orders that have been paid or invoiced but have not been received.
Received and Invoiced But Not Paid Query - Sirsi orders that have been received and invoiced but have not been paid.
Received But Not Invoiced and Not Paid Query - Sirsi orders that have been received but not invoiced and not paid.
Unpaid Order Invoices Not Linked to Orders Query - Sirsi order invoices with an amount invoiced but that are not linked to an order
Audit Signature Query - Details what Sirsi funds collection managers are authorized to spend with subject names.
Endowment Balances By Fund Query - Snapshot of DBR - Endowments, Gifts, Grants Individual Funds budgeted amounts, encumbrances, expenditures, balances, and % to spend for fund allocation reset in Sirsi at beginning of fiscal year.
Unique Monographs Queries
Current Fiscal Year Open Orders Query - Tracks open orders on “FIR” fund codes to facilitate order clean-up.
YBP Bib Utilities Invoices Query - Tracks YBP invoices that are paid against the LSUTL fund code.
Unique Serials Queries
Amount Ordered on Kristen's P-card Query - Tracks the amount spent on ILL using Kristen’s P-card for each fiscal cycle beginning with 2013-2014.
Branch Subscriptions Annual Statistics Query - Counts print subscriptions for the branch libraries, including Design, NRL, VetMed, and LRL.
Child Orders With Payment or Encumbrance Query - Tracks POs with an order type of “CHILD” that have money paid or encumbered against them. Serials staff will correct these POs because “CHILD” type orders should not have money associated with them
Deposit Invoices Paid in Peoplesoft Query -
EBSCO Invoicelines Paid Against Deposit Funds - Tracks all EBSCO invoices on deposit fund codes.
EBSCO Invoicelines Paid Against State Funds - Tracks all EBSCO invoices on state fund codes.
Money Spent on Renewals Previous Fiscal Year Query - Tracks the money spent on serials renewal in the previous fiscal cycle based on by resource type (e.g. Print Serials, Databases, Electronic Access Fees, etc).
Orders in Previous FY Not in Current FY Query - Used to identify orders that have not been cancelled but have not rolled into the newest fiscal cycle.
Over Distributed Lines Query - Sirsi orders with over distributed segments. The extra segments need to be deleted by Serials staff because they cause duplicates to appear in various reports.
Package Report - Tracks the amount spent , the amount encumbered, and the number of POs associated with several different serial providers.
Rolled Orders Still Paid On LSCNM - Tracks orders that were paid on LSNCM (new continuing money) fund codes in the previous fiscal cycle and rolled into the current fiscal cycle with a LSNCM fund code. Serials staff will correct these orders and transfer them to a new fund code.
Subscription Orders With No Encumbrance or Payment Query - Tracks POs with an order type of “SUBSCRIPT” that do not money paid or encumbered against them. Serials staff will correct these POs because “SUBSCRIPT” type orders should have money associated with them
Annual Report - Gifts Added By Format - Provides statistics for the Gifts Added to All Collections section in the department’s annual report. Run the Sirsi report template “Annual Report - Gifts Received Count.” Import this file into one of the Fund Statistics test databases as AnnualReportGiftsAdded. Run the query Annual Report Gifts Added By Format Query and add the numbers to the Gifts Added to All Collections section in the annual report.
Annual Report - Monographs Receiving - Provides statistics for the Monographs Added section in the department’s annual report.
Annual Report - Renewals - Generates the number of renewals by format in the previous fiscal cycle.
Annual Report - Serials Cancellations - Generates the number of orders that were cancelled by formats in the fiscal cycle that just ended.
Annual Report - ServiceNow Statistics - Generate a report in ServiceNow according the instructions located at ?ServiceNow statistics. Load the spreadsheet in the Fund Statistics database as a table named ServiceNow[FiscalYear].” Update the Annual Report ServiceNow Statistics Query to reflect the just completed fiscal cycle. Run the query and skip any numbers associated with a Closure CI of “A&D Triage.”