Author	Andrei Makaranka
JIRA task
Business Requirements	UXPROD-772 - Getting issue details... STATUS
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

Fiscal year 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	If true, then all budget must be closed in rollover time UIF-175 - Getting issue details... STATUS
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	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	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	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	`Accept: text/csv, application/json` `Param : CQL`	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

Method

Path

Request

Response

Description

Interface

Notes

GET

/finance/ledger-rollovers-errors

Accept: text/csv, application/json

Param : CQL

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):
1. If Enough money in budget
  1. then create new encumbrances and update budgets amounts.
  2. else don't create encumbrances and store error

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: After the start of the rollover all selected active budgets will be blocked(Status = "Closed") for any operations and then all new budgets start to be created. After the start of the rollover, the budgets are blocked(Status = "Closed") and new ones are created for them one by one. 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.	MODFISTO-167 - Getting issue details... STATUS
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.	MODFISTO-157 - Getting issue details... STATUS
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?	Dennis Bridges Order error table POL Number (Hyperlink) Dennis Bridges ~~It will be difficult to understand in which POL error occurs. Can we store link on order?~~ 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 a pending payment that IS NOT in the workflow state "Approved" should be moved into the next fiscal year (Note: Currently this is not an issue, but we will be implementing a feature in the future that will mean creating pending payments for all invoices that are Open). 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. Eg. Invoice 1234 is approved in FY2020. The ledger is rolled over into FY2021 and then the invoice is transitioned to paid. Payments are created for FY2020 budget.
Andrei Makaranka In what order should we process orders during rollover? Examples: We can start from the very first in time to the last created order. We can start with the orders with the least encumbered amount of money booked or vice versa.	Dennis Bridges 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 FY20 - Is this the fiscal year code? If yes why the use can't edit the code?	Dennis Bridges 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?	Dennis Bridges 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?	Dennis Bridges 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	MODFISTO-157 - Getting issue details... STATUS
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?	Dennis Bridges 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.

UXPROD-772 - Getting issue details... STATUS

Revision History

Overview

Constraints

Assumptions, dependencies

Out of Scope

Architecturally Significant Requirements

Solution Design

Rollover flows

Data Model

ledger_fiscal_year_rollover

budgets_rollover

encumbrances_rollover

ledger_fiscal_year_rollover_progress

ledger_fiscal_year_rollover_error

order_rollover_error_details

fund_rollover_error_details

APIs

Rollover Ledger Business API

Rollover Ledger Storage API

Rollover Error Report Business API

Rollover Error Report Storage API

Rollover Ledger Storage API

Rollover Ledger progress business API

Rollover Ledger progress storage API

Rollover Orders API

Design

Stages and sequence of execution fiscal year rollover

1. Create rollover structures and close budgets for previous fiscal year

2. Invoke procedure "budget_encumbrances_rollover.sql" (Encumbrance procedure logic)

3. Rollover orders

4. Collect errors in the DB table and convert to CSV if user request report.

budget_encumbrances_rollover.sql

Open Items

Browser not supported