Fiscal year rollover
Author | Andrei Makaranka |
---|---|
JIRA task | |
Business Requirements | |
Architects Review | DONE |
PO review | DONE |
Revision History
Version | Date | Owner | Description | Reason |
---|---|---|---|---|
v1.0 | 07.27.2020 | Andrei Makaranka | Initial version | |
v1.1 | 07.31.2020 | Andrei Makaranka | Fill gaps | Grooming session 29.07.2020 |
v1.2 | 08.01.2020 | Andrei Makaranka | Design writing started. Activity diagram added | Internal grooming session 08.07.2020 |
v1.3 | 09.25.2020 | Andrei Makaranka | Update model and API definition | |
v1.4 | 10.13.2020 | Andrei Makaranka | Detailed rollover logic added | Internal feature telling |
Overview
In order to start the next fiscal year the user must be able to transfer or close outstanding encumbrances for orders that have not yet been resolved. This is generally done based on order type.
Use cases:
- Library has ongoing orders that must be re-encumbered in the upcoming fiscal year. The library will want to encumber money next year based on what was spent in the current year and potentially increase it by a specific percentage.
- Library has ongoing orders that must be re-encumbered in the upcoming fiscal year. The library will want to encumber money differently for continuations that for subscription orders. Increasing them by different percentages.
- Library has one-time orders that could not be close and must be re-encumbered in the upcoming fiscal year. The method of encumbrance may differ from that of on-going orders. For one-time orders the library would rollover remaining encumbrances.
High-Level Requirements:
- Adjust the date of encumbrances without affecting monies that have already been expended against this encumbrance.
- Release encumbrances without affecting the monies that have already been expended.
- Capture the original amount encumbered based on the purchase order line.
- Track the amount expended against this encumbrance.
- Track the current encumbrance.
Constraints
- The fund must contain only one active budget.
Assumptions, dependencies
- API for changing and creating a budget is ready.
- API for changing, creating and releasing encumbrances is ready.
- API for re-encumbered is ready. (Confirmed by Dennis Bridges on grooming 29.07.2020)
- It is likely that we will be creating upwards of 75000 - 100000 encumbrances during this process. (statistics from Library)
Out of Scope
- Automatically trying to resolve issues which occurred in rollover time.
- Support exchange rate.
- Ledger fiscal year rollover preview.
- Store ledger fiscal year rollover history.
Architecturally Significant Requirements
- Fiscal year rollover is a process which must happen for all ledgers separately and this process started manually by user.
- Ledger rollover includes : Budgets rollover and encumbrances rollover and those rollovers are independent processes.
- The system after rollover must be in a consistent state. All budgets and encumbrances must be processed and as result rollover end successfully or with errors.
- An error report will be generated after the process completes.
- Must be compatible with PostgreSQL 10.x
Solution Design
Rollover flows
Data Model
ledger_fiscal_year_rollover
Property | Type | Default | Required | Notes |
---|---|---|---|---|
id | string | N | UUID - System generated if not specified | |
ledgerId | string | Y | Ledger UUID for which rollover was started | |
fromFisclaYearId | string | Y | UUID of the fiscal year from which rollover will be happened | |
toFisclaYearId | string | Y | UUID of the fiscal year to which rollover will be happened | |
restrictEncumbrance | boolean | false | N | If true, imposes restrictions on encumbrances |
restrictExpenditures | boolean | false | N | If true, imposes restrictions on payments |
needCloseBudgets | boolean | true | N | |
budgetsRollover | array<budgets_rollover> | Y | ||
encumbrancesRollover | array<encumbrances_rollover> | Y | ||
metadata | metadata | Generated by system | No | System generated record metadata |
budgets_rollover
Property | Type | Default | Required | Notes |
---|---|---|---|---|
fundTypeId | string | UUID - UUID from fund_type table. It can be possible when fund don't have Type | ||
rolloverAllocation | boolean | true | N | Should allocated amount needs to be rollovered |
adjustAllocation | number | 0% | N | How much should we increase the allocation |
rolloverAvailable | boolean | true | N | Should available amount needs to be rollovered |
addAvailableTo | enum | Available | N | Where to add available : Available or Allocation |
allowableEncumbrance | number | 100% | N | The encumbrance percentage limit for this budget |
allowableExpenditure | number | 100% | N | The expenditure percentage limit for this budget |
encumbrances_rollover
Property | Type | Default | Required | Notes |
---|---|---|---|---|
orderType | enum | Y | Ongoing, Ongoing-Subscription, One-time | |
basedOn | enum | Y | Expended, Remaining | |
increaseBy | number | 0% | N | How much should we increase the encumbrance amount based on "basedOn" |
ledger_fiscal_year_rollover_progress
Property | Type | Default | Required | Notes |
---|---|---|---|---|
id | UUID | N | UUID - System generated if not specified | |
ledgerRolloverId | UUID | Y | Ledger UUID for which rollover was started | |
overallRolloverStatus | Enum | Not Started | N | Progress of the rollover process : Not Started, In Progress, Error, Success |
budgetsClosingRolloverStatus | Enum | Not Started | N | Progress of budgets closing rollover process : Not Started, In Progress, Error, Success |
financialRolloverStatus | Enum | Not Started | N | Progress of new budgets and encumbrance creation rollover process : Not Started, In Progress, Error, Success |
ordersRolloverStatus | Enum | Not Started | N | Progress of orders rollover process : Not Started, In Progress, Error, Success |
ledger_fiscal_year_rollover_error
Property | Type | Default | Required | Notes |
---|---|---|---|---|
id | UUID | N | UUID - System generated if not specified | |
ledgerRolloverId | UUID | Y | Ledger rollover UUID for which rollover was started | |
errorType | Enum | Y | FUND, ORDER | |
failedAction | string | Y | Example: Create Encumbrance, Create allocation | |
errorMessage | string | Y | Example: Not enough money available in the Fund to create encumbrance, System error: server could not be reached | |
details | Map<String, Object> | Y |
order_rollover_error_details
Property | Type | Default | Required | Notes |
---|---|---|---|---|
purchaseOrderId | UUID | Y | ||
poLineId | UUID | Y | UUID of the POL. Need for link building | |
polNumber | string | |||
amount | number | Wrong amount | ||
fundId | UUID | Y | UUID of the fund | |
fundCode | string | Fund code |
fund_rollover_error_details
Property | Type | Default | Required | Notes |
---|---|---|---|---|
fundId | UUID | Y | UUID of the fund | |
fundCode | string | Fund code | ||
amount | number | Wrong amount |
APIs
Rollover Ledger Business API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance/ledger-rollovers | ledger_fiscal_year_rollover | ledger_fiscal_year_rollover | Create and start process ledger fiscal year rollover | finance.ledger-rollovers | Status : 201 |
GET | /finance/ledger-rollovers/{id} | NA | ledger_fiscal_year_rollover | GET ledger fiscal year rollover by id | finance.ledger-rollovers | Status : 200 |
GET | /finance/ledger-rollovers | NA | ledger_fiscal_year_rollover_collection | GET ledger fiscal year rollovers | finance.ledger-rollovers | Status : 200 |
Rollover Ledger Storage API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance-storage/ledger-rollovers | ledger_fiscal_year_rollover | ledger_fiscal_year_rollover | Create and start process ledger fiscal year rollover | finance-storage.ledger-rollovers | Status : 201 |
GET | /finance-storage/ledger-rollovers/{id} | NA | ledger_fiscal_year_rollover | GET ledger fiscal year rollover by id | finance-storage.ledger-rollovers | Status : 200 |
PUT | /finance-storage/ledger-rollovers/{id} | ledger_fiscal_year_rollover | NA | UPDATE ledger fiscal year rollover if it not started or end. | finance-storage.ledger-rollovers | Status : 204 |
DELETE | /finance-storage/ledger-rollovers/{id} | NA | NA | DELETE ledger fiscal year rollover by id | finance-storage.ledger-rollovers | Status : 204 |
GET | /finance-storage/ledger-rollovers | NA | ledger_fiscal_year_rollover_collection | GET all ledger fiscal year rollovers | finance-storage.ledger-rollovers | Status : 200 |
Rollover Error Report Business API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
GET | /finance/ledger-rollovers-errors |
| ABC-Rollover-Errors-2020.csv or Collection of ledger_fiscal_year_rollover_error | Return rollover errors in specified format | finance.ledger-rollovers-errors | Status : 200 |
Rollover Error Report Storage API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance-storage/ledger-rollovers-errors | ledger_fiscal_year_rollover_error | ledger_fiscal_year_rollover_error | Create and ledger fiscal year rollover error | finance-storage.ledger-rollovers-errors | Status : 201 |
PUT | /finance-storage/ledger-rollovers-errors/{id} | ledger_fiscal_year_rollover_error | NA | UPDATE ledger fiscal year rollover error by Id | finance-storage.ledger-rollovers-errors | Status : 204 |
DELETE | /finance-storage/ledger-rollovers-errors/{id} | NA | NA | DELETE ledger fiscal year rollover error by id | finance-storage.ledger-rollovers-errors | Status : 204 |
GET | /finance/ledger-rollovers-errors/{id} | NA | ledger_fiscal_year_rollover_error | Return rollover errors | finance-storage.ledger-rollovers-errors | Status : 200 |
GET | /finance/ledger-rollovers-errors | NA | Collection of ledger_fiscal_year_rollover_error | Return rollover errors | finance-storage.ledger-rollovers-errors | Status : 200 |
Rollover Ledger Storage API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance-storage/ledger-rollovers | ledger_fiscal_year_rollover | ledger_fiscal_year_rollover | Create and start process ledger fiscal year rollover | finance-storage.ledger-rollovers | Status : 201 |
GET | /finance-storage/ledger-rollovers/{id} | NA | ledger_fiscal_year_rollover | GET ledger fiscal year rollover by id | finance-storage.ledger-rollovers | Status : 200 |
PUT | /finance-storage/ledger-rollovers/{id} | ledger_fiscal_year_rollover | NA | UPDATE ledger fiscal year rollover if it not started or end. Also | finance-storage.ledger-rollovers | Status : 204 |
DELETE | /finance-storage/ledger-rollovers/{id} | NA | NA | DELETE ledger fiscal year rollover by id | finance-storage.ledger-rollovers | Status : 204 |
GET | /finance-storage/ledger-rollovers | NA | ledger_fiscal_year_rollover_collection | GET all ledger fiscal year rollovers | finance-storage.ledger-rollovers | Status : 200 |
Rollover Ledger progress business API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance/ledger-rollovers-progress | ledger_fiscal_year_rollover_progress | ledger_fiscal_year_rollover_progress | Create rollover process | finance.ledger-rollovers-progress.item.post | Status : 201 |
GET | /finance/ledger-rollovers-progress | NA | collection of ledger_fiscal_year_rollover_progress | GET rollover process | finance.ledger-rollovers-progress.collection.get | Status : 200 |
GET | /finance/ledger-rollovers-progress/{id} | NA | ledger_fiscal_year_rollover_progress | GET rollover process by Id | finance.ledger-rollovers-progress.item.get | Status : 200 |
PUT | /finance/ledger-rollovers-progress/{id} | ledger_fiscal_year_rollover_progress | NA | UPDATE rollover process | finance.ledger-rollovers-progress.item.put | Status : 204 |
Rollover Ledger progress storage API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /finance-storage/ledger-rollovers-progress | ledger_fiscal_year_rollover_progress | ledger_fiscal_year_rollover_progress | Create rollover process | finance-storage.ledger-rollovers-progress.item.post | Status : 201 |
GET | /finance-storage/ledger-rollovers-progress | NA | collection of ledger_fiscal_year_rollover_progress | GET rollover process | finance-storage.ledger-rollovers-progress.collection.get | Status : 200 |
GET | /finance-storage/ledger-rollovers-progress/{id} | NA | ledger_fiscal_year_rollover_progress | GET rollover process by Id | finance-storage.ledger-rollovers-progress.item.get | Status : 200 |
PUT | /finance-storage/ledger-rollovers-progress/{id} | ledger_fiscal_year_rollover_progress | NA | UPDATE rollover process | finance-storage.ledger-rollovers-progress.item.put | Status : 204 |
DELETE | /finance-storage/ledger-rollovers/progress/{id} | NA | NA | DELETE rollover process | finance-storage.ledger-rollovers-progress.item.delete | Status : 204 |
Rollover Orders API
Method | Path | Request | Response | Description | Interface | Notes |
---|---|---|---|---|---|---|
POST | /orders/rollover | ledger_fiscal_year_rollover | NA | Start orders rollover | orders.rollover | Status : 204 |
POST | /orders/composite-orders/{id}/re-encumber | NA | NA | Rerun single order rollover | orders.rollover | Status : 204 |
Design
Solution will be based on stored procedures in PostgreSQL and this is followed from 3d item from "Architecturally Significant Requirements".
The following PostgreSQL procedures are required :
- Budget rollover procedure
- Encumbrances rollover procedure
Ledger Fiscal Year Rollover Flow
Stages and sequence of execution fiscal year rollover
mod-finance-storage
1. Create rollover structures and close budgets for previous fiscal year
In scope of one DB transaction
1.1 Create ledger_fiscal_year_rollover and ledger_fiscal_year_rollover_status
1.2 All budget for the selected ledger and rollover options must be closed, if flag "needCloseBudge" is True (BudgetStatus.CLOSED). Dennis Bridges Should we close all funds and budgets and ledger for current FY? From Dennis BridgesOnly budgets
1.3 If budged closed logic successfully finished
then invoke procedure "budget_encumbrances_rollover.sql" (Item 2)
else finish rollover with error :
throw HttpException(500, "Can't close prev budgets please restart")
2. Invoke procedure "budget_encumbrances_rollover.sql" (Encumbrance procedure logic)
Rollover budgets and encumbrances will be processed in scope of procedure DB transaction.
2.1 Create new budgets with Budget.BudgetStatus.ACTIVE
Note - If Fund status is Frozen, then new budget should be created in status Frozen.
2.2 New encumbrances creation started with appropriate logic
Note - In case of an error of lack of money in the fund : order should be skipped.
3. Rollover orders
Rollover orders will be run from code by invoking API from orders
Note - We will start from the oldest orders and finish with the newest orders.
4. Collect errors in the DB table and convert to CSV if user request report.
budget_encumbrances_rollover.sql
Rollover budgets
- Find previously budgets by fromFisclaYearId and
FundTypes - Find planned existed budgets by toFisclaYearId and FundTypes. Dennis Bridges Can we create budget for the next FY with allocated money from UI? If yes should we update existed allocation amount? Increase current allocation on 5% and + allocation amount of existing planned budget and create new allocation Transaction for planned budget (Increase current allocation on 5%)
- Then create new budgets for toFisclaYearId and FundTypes. (clarify logic for amounts calculation) and create allocation transactions.
Note : better to collect errors directly in processing time
Rollover Encumbrances
Dennis Bridges There is a flag ""Re-Encumbered" for order. Should we taking into account this flag, when rollover encumbrances? User will be responsible to unmark flag where needed.
Dennis Bridges No possible order encumbrances by order's created time or number(need cross-module migration). Can we order encumbrances by "encumbrance creation date"(means by open order date)? Dennis Bridges Confirmed
- Find all prev encumbrances with group by poUUID and sort by Min "encumbrance creation date"
- Process encumbrances related to the same order with appropriate logic (one-time, ongoing, subscription):
- If Enough money in budget
- then create new encumbrances and update budgets amounts.
- else don't create encumbrances and store error
- If Enough money in budget
Note : better to collect errors directly in processing time
Build CSV error log
polNumber and fundCode should be get on report building time.
Open Items
Question | Answer | Story |
---|---|---|
Andrei Makaranka : How many users can start rollover at the same time? | Dennis Bridges Each ledger could rollover independently but most would roll one at a time. Generally there is one user that handles the rollover at each institution. However, for a multi library instance each Ledger could be rolled over by a different user. | |
Andrei Makaranka: Could you clarify what "unfinished orders" are and what encumbrances should we choose for a rollover? | Dennis Bridges Any "Open" orders should be considered in the rollover. However, some may not have encumbrances. Closed and pending orders will not need encumbrances created during rollover for the new fiscal year. | |
Andrei Makaranka: There are to approaches how to rollover budgets:
Example : Block first budget and then create new budget. Block second budget and then create new budget. What is the right approach from business view? | Dennis Bridges This process is generally done while all other system activity is frozen. Meaning the expectation would be that if I am rolling over Ledger A no one can spend money against any of the budgets assigned to Ledger A. Option 1 seems like the appropriate approach. | |
Andrei Makaranka: Do we need set "Active" status for new budgets immediately after creation or only when rollover will be finished? | Dennis Bridges So long as the system can still create the necessary encumbrances I believe we should activate the budget last. So the users can be sure no one is able to order or invoice against them during rollover. | |
Andrei Makaranka: We need set of checks which we can use for building error report. Also it's needed for estimating the scope an effort what we need for implementing a rollover check process. Could you please provide this set of checks? | Dennis Bridges The checks would include: Were budgets closed successfully for previous fiscal year? Were budgets created successfully for new fiscal year? Were budgets activated successfully for new fiscal year? Note: if Fund status is Frozen, then new budget should be created in status Frozen. Were allocations created against all new budgets successfully for new fiscal year? Were encumbrances created for all POLs against new budgets for new fiscal year? | |
Andrei Makaranka: Library will want to release all remaining encumbrances in the fiscal year that is being closed? | Dennis Bridges No, I have confirmed that the encumbrances should remain as they are in the fiscal year being closed, for reporting purposes. | |
Andrei Makaranka: What field need to be included in the error report? | Order error table POL Number (Hyperlink) Dennis Bridges Failed action(s) (Eg. Create Encumbrance) Amount (Eg. $104.03) Fund Code (Eg. ECON) (Hyperlink) Error message (Eg. Not enough money available in the Fund to create encumbrance) Fund error table Fund Code (Hyperlink) Failed action(s) (Eg. Create allocation) Amount (Eg. $1000) Error message (Eg. System error: server could not be reached) | |
Aliaksandr Pautau : What should happen to invoices during a rollover, specifically with Pending payments? | Dennis Bridges : Any invoice with pending payment(s) that are in the workflow state "Approved" should NOT rollover pending payments into the next fiscal year. Important: Also, any payment or credit for the invoice should be created for the FY during which the invoice was approved.
| |
Andrei Makaranka In what order should we process orders during rollover?
| We will start from the oldest orders and finish with the newest orders. | |
Andrei Makaranka Is not clear how to create new fiscal year rollover? In mockup https://drive.google.com/drive/u/0/folders/1OGnJNiNVgm6c6qifjDf-ZXnAhlv54JTV
| Mockup has been updated so the user will not need to edit the Fiscal year at this time. They can select an existing year or create a new one during rollover. https://drive.google.com/file/d/15i9H9JM6Tk6DDv9M2ubkzincxN4xep2t/view?usp=sharing | |
Andrei Makaranka Is not clear how to change costs in mixed POL? How to split percentage between different types physical and e-resource? | We are not really concern with cost being updated it might be ideal not to update the cost. Particularly when basing the increase on amount expended which could be higher OR lower than current cost of POL. We may need to discuss what mechanism could be added to handle these changes. I have often times considered that it could make sense to have a Cost FY for the POL so an order can have a clear cost history from one year to the next. I have created a visual to describe use cases and help us discuss the solution. FYRO Encumbrance visual.pdf | |
Aliaksandr Pautau In case of an error of lack of money in the fund: Should we skip the creation all encumbrances for the order, or only for a fund where there is no money? | In this case the order should be skipped. Essentially it should appear in the error report after rollover as having failed because the encumbrances could not be made. Because there was not enough money available in FUND XYZ | |
Andrei Makaranka How to be in a situation if in the same POL we have funds from different ledgers and then the cost changing affects the ledger who did not rollover? | In this case the order should be skipped as well. Essentially it should appear in the error report after rollover as having failed because the encumbrances could not be made. Because there was not enough money available in FUND XYZ. Ideally IF the other ledger was rolled over already, at which time this order failed to encumber. Then rolling over this ledger would make another attempt and this time it would be successful encumbering both funds as they would now have allocations. So again either the order is entirely successful or not and if not it is flagged for the user in the report. | |
Andrei Makaranka Should user has specific permissions for starting rollover? (the similar like we done for approve order) | Dennis Bridges Yes, note the permission reference in UIF-168. "Finance: Execute fiscal year rollover" permission. |