Hi all, this is our first year end with Polaris. Our finance dept asks for a list of encumbrances each year. I basically need a total dollar number that is on order with each supplier. I had thought that the Encumbrances and Expenditures by Supplier report in the Admin Portal could be used. (Utilities/Reports & Notices/Acquisitions menu). However, the Fund summary encumbrances total is not matching with the total encumbrances on the Encumbrances & Expenditures by Supplier report. First, does anyone know why those numbers would not match? And second, if you have to provide a similar report, how do you do it?
I talked to our head of acquisitions and she said she just doesn’t really rely on the numbers Polaris provides re: encumbrance.
I took a look at the 2 reports you mentioned:
- The Fund summary is very simple, it just pulls the “total currently encumbered” from each fund
- The Encumbrances and Expenditures by Supplier report pulls the amount encumbered from each purchase order that is associated with a given fiscal year. The purchase orders are necessary so that the supplier info can be joined
As far as I can tell, part of the discrepancy between the two seems to be that the “total currently encumbered” in the funds table excludes line items that were created prior to the current fiscal year - even if the fiscal year listed is the current fiscal year!
So if you wanted the encumbrances by supplier to match up with the fund report, I would try something like this:
select suppliers.suppliername,suppliers.suppliersan,suppliers.lbacctnumber,sum(amountencumbered) from polineitemsegments with (nolock)
left join polineitemsegmentamts with (nolock) on polineitemsegments.polineitemsegmentid=polineitemsegmentamts.polineitemsegmentid
left join purchaseorders with (nolock) on polineitemsegments.purchaseorderid=purchaseorders.purchaseorderid
left join suppliers with (nolock) on purchaseorders.supplierid=suppliers.supplierid
where polineitemsegmentamts.fiscalyearid=88 --or your org's current fiscal year id
and polineitemsegments.creationdate > '2025-02-10' --this is about when we did our fiscal year rollover this year
group by suppliers.suppliername,suppliers.suppliersan,suppliers.lbacctnumber
This got me within $1,000 of the fund summary. Limiting by destinationorgid got me a bit closer - within $300. Still not ideal but the original discrepancy between the two reports was more like $60,000 vs $30,000.
I would be curious if anyone has any more practical experience with this, or knows how the “total current encumbrance” is calculated for the funds.
2 Likes
Hi Eleanor, thank you for sending the query. How do I find our fiscal year ID? Sorry, this is all new to me. I looked in the Polaris Admin portal and didn’t see a number. I put in the name of our fiscal year but that gave me an error with the overall query.
Hello Amy, there’s 2 ways:
- Open the fiscal year in Polaris from the fiscal year find tool - the fiscal year ID is at the top of the window
- Run a separate query to find the fiscal year id, something like this:
select fiscalyearid from fiscalyears with (nolock)
where name like 'Your Fiscal Year Name'
1 Like
Hi Eleanor, we’ve only been live with Polaris for two months. I’m still learning a lot. What do you mean by this, “Limiting by destinationorgid got me a bit closer”? When I ran your query it gave me the same $ as the Encumbrances and Expenditures by Supplier report.