Fiscal year rollover

AuthorAndrei Makaranka
JIRA task
Business Requirements

UXPROD-772 - Getting issue details... STATUS

Architects Review

DONE

PO review

DONE

Revision History

VersionDateOwnerDescriptionReason
v1.007.27.2020Andrei MakarankaInitial version
v1.107.31.2020Andrei MakarankaFill gapsGrooming session 29.07.2020
v1.208.01.2020Andrei MakarankaDesign writing started. Activity diagram addedInternal grooming session 08.07.2020
v1.309.25.2020Andrei MakarankaUpdate model and API definition
v1.410.13.2020Andrei MakarankaDetailed rollover logic addedInternal 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

PropertyTypeDefaultRequiredNotes

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

needCloseBudgetsbooleantrueN

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

PropertyTypeDefaultRequiredNotes

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

PropertyTypeDefaultRequiredNotes

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

PropertyTypeDefaultRequiredNotes

id

UUID


N

UUID - System generated if not specified

ledgerRolloverIdUUID
YLedger UUID for which rollover was started
overallRolloverStatusEnumNot StartedNProgress of the rollover process : Not Started, In Progress, Error, Success
budgetsClosingRolloverStatusEnumNot StartedNProgress of budgets closing rollover process : Not Started, In Progress, Error, Success
financialRolloverStatus
EnumNot StartedNProgress of new budgets and encumbrance creation rollover process : Not Started, In Progress, Error, Success
ordersRolloverStatusEnumNot StartedNProgress of orders rollover process : Not Started, In Progress, Error, Success

ledger_fiscal_year_rollover_error

PropertyTypeDefaultRequiredNotes

id

UUID


N

UUID - System generated if not specified

ledgerRolloverIdUUID
YLedger rollover UUID for which rollover was started
errorTypeEnum
YFUND, ORDER
failedActionstring
YExample: Create Encumbrance, Create allocation
errorMessagestring
YExample: 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

PropertyTypeDefaultRequiredNotes
purchaseOrderIdUUID
Y
poLineIdUUID
YUUID of the POL. Need for link building
polNumberstring


amountnumber

Wrong amount
fundIdUUID
YUUID of the fund
fundCodestring

Fund code

fund_rollover_error_details

PropertyTypeDefaultRequiredNotes
fundIdUUID
YUUID of the fund

fundCode

string



Fund code

amountnumber

Wrong amount

APIs

Rollover Ledger Business API

Method

Path

Request

Response

Description

Interface

Notes

POST/finance/ledger-rolloversledger_fiscal_year_rolloverledger_fiscal_year_rolloverCreate and start process ledger fiscal year rolloverfinance.ledger-rolloversStatus : 201
GET/finance/ledger-rollovers/{id}NAledger_fiscal_year_rolloverGET ledger fiscal year rollover by idfinance.ledger-rolloversStatus : 200
GET/finance/ledger-rolloversNAledger_fiscal_year_rollover_collectionGET ledger fiscal year rolloversfinance.ledger-rolloversStatus : 200

Rollover Ledger Storage API

Method

Path

Request

Response

Description

Interface

Notes

POST

/finance-storage/ledger-rollovers

ledger_fiscal_year_rolloverledger_fiscal_year_rolloverCreate and start process ledger fiscal year rolloverfinance-storage.ledger-rolloversStatus : 201
GET

/finance-storage/ledger-rollovers/{id}

NAledger_fiscal_year_rolloverGET ledger fiscal year rollover by idfinance-storage.ledger-rolloversStatus : 200
PUT

/finance-storage/ledger-rollovers/{id}

ledger_fiscal_year_rolloverNAUPDATE ledger fiscal year rollover if it not started or end. finance-storage.ledger-rolloversStatus : 204
DELETE/finance-storage/ledger-rollovers/{id}NANADELETE  ledger fiscal year rollover by idfinance-storage.ledger-rolloversStatus : 204
GET/finance-storage/ledger-rolloversNAledger_fiscal_year_rollover_collectionGET all ledger fiscal year rolloversfinance-storage.ledger-rolloversStatus : 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 formatfinance.ledger-rollovers-errorsStatus : 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 errorfinance-storage.ledger-rollovers-errorsStatus : 201
PUT

/finance-storage/ledger-rollovers-errors/{id}

ledger_fiscal_year_rollover_errorNAUPDATE ledger fiscal year rollover error by Idfinance-storage.ledger-rollovers-errorsStatus : 204
DELETE/finance-storage/ledger-rollovers-errors/{id}NANADELETE  ledger fiscal year rollover error by idfinance-storage.ledger-rollovers-errorsStatus : 204
GET/finance/ledger-rollovers-errors/{id}

NA

ledger_fiscal_year_rollover_errorReturn rollover errors finance-storage.ledger-rollovers-errorsStatus : 200
GET/finance/ledger-rollovers-errors

NA

Collection of ledger_fiscal_year_rollover_errorReturn rollover errorsfinance-storage.ledger-rollovers-errorsStatus : 200

Rollover Ledger Storage API

Method

Path

Request

Response

Description

Interface

Notes

POST

/finance-storage/ledger-rollovers

ledger_fiscal_year_rolloverledger_fiscal_year_rolloverCreate and start process ledger fiscal year rolloverfinance-storage.ledger-rolloversStatus : 201
GET

/finance-storage/ledger-rollovers/{id}

NAledger_fiscal_year_rolloverGET ledger fiscal year rollover by idfinance-storage.ledger-rolloversStatus : 200
PUT

/finance-storage/ledger-rollovers/{id}

ledger_fiscal_year_rolloverNAUPDATE ledger fiscal year rollover if it not started or end. Alsofinance-storage.ledger-rolloversStatus : 204
DELETE/finance-storage/ledger-rollovers/{id}NANADELETE  ledger fiscal year rollover by idfinance-storage.ledger-rolloversStatus : 204
GET/finance-storage/ledger-rolloversNAledger_fiscal_year_rollover_collectionGET all ledger fiscal year rolloversfinance-storage.ledger-rolloversStatus : 200

Rollover Ledger progress business API

Method

Path

Request

Response

Description

Interface

Notes

POST/finance/ledger-rollovers-progressledger_fiscal_year_rollover_progressledger_fiscal_year_rollover_progressCreate rollover processfinance.ledger-rollovers-progress.item.postStatus : 201
GET/finance/ledger-rollovers-progressNAcollection of ledger_fiscal_year_rollover_progressGET rollover processfinance.ledger-rollovers-progress.collection.getStatus : 200
GET/finance/ledger-rollovers-progress/{id}NAledger_fiscal_year_rollover_progressGET rollover process by Idfinance.ledger-rollovers-progress.item.getStatus : 200
PUT/finance/ledger-rollovers-progress/{id}ledger_fiscal_year_rollover_progressNAUPDATE rollover processfinance.ledger-rollovers-progress.item.putStatus : 204

Rollover Ledger progress storage API

Method

Path

Request

Response

Description

Interface

Notes

POST/finance-storage/ledger-rollovers-progressledger_fiscal_year_rollover_progressledger_fiscal_year_rollover_progressCreate rollover processfinance-storage.ledger-rollovers-progress.item.postStatus : 201
GET/finance-storage/ledger-rollovers-progressNAcollection of ledger_fiscal_year_rollover_progressGET rollover processfinance-storage.ledger-rollovers-progress.collection.getStatus : 200
GET/finance-storage/ledger-rollovers-progress/{id}NAledger_fiscal_year_rollover_progressGET rollover process by Idfinance-storage.ledger-rollovers-progress.item.getStatus : 200
PUT/finance-storage/ledger-rollovers-progress/{id}ledger_fiscal_year_rollover_progressNAUPDATE rollover processfinance-storage.ledger-rollovers-progress.item.putStatus : 204
DELETE/finance-storage/ledger-rollovers/progress/{id}NANADELETE rollover processfinance-storage.ledger-rollovers-progress.item.deleteStatus : 204

Rollover Orders API

Method

Path

Request

Response

Description

Interface

Notes

POST/orders/rollover

ledger_fiscal_year_rollover

NAStart orders rollover orders.rolloverStatus : 204
POST/orders/composite-orders/{id}/re-encumberNANARerun single order rolloverorders.rolloverStatus : 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 :

  1. Budget rollover procedure
  2. 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 

  1. Find previously budgets by fromFisclaYearId and FundTypes
  2. 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%)
  3. 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

  1. Find all prev encumbrances with group by poUUID and sort by Min "encumbrance creation date"
  2. 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

QuestionAnswerStory

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:

  1.  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.
  2.  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 MakarankaWe 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: 

  1. We can start from the very first in time to the last created order.
  2. 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.