Author	Andrei Makaranka
JIRA task	UXPROD-2899 - Getting issue details... STATUS
Business Requirements	Allow User to generate a preview of fiscal year rollover
Architects Review	Raman Auramau IN PROGRESS
PO review	Dennis Bridges IN PROGRESS

Revision History

Version	Date	Owner	Description	Reason
v1.0	06.14.2022	Andrei Makaranka	Initial version
v2.0	06.22.2022	Andrei Makaranka	Schema changed and WBS added
v2.1	06.30.2022	Andrei Makaranka	Status for preview added
v3.0	07.07.2020	Andrei Makaranka	Redesign and rollover logs functionality added	Requirements changed

Overview

Allow user to generate a rollover report that will tell the user what errors may occur if rolling over with current data.

Preview report should contain : finance rollover errors (budget errors) and clone of new generated budgets.

Constraints

The current approach can cover rollover for the only one ledger though in real life there can be several ledgers - but this needs to be considered

Assumptions, dependencies

Ledger Fiscal Year Rollover already implemented.
It is likely that we will be creating upwards of 75000 - 100000 encumbrances during this process. (statistics from Library).
Prepare report with Unpaid invoices is UI responsibility.

Out of Scope

Prepare report for orders rollover.
Prepare report for encumbrances rollover.
Support exchange rate.

Architecturally Significant Requirements

The preview process should not make changes in real tables, but store the results in a temporary or separate tables.
Finance rollover preview should be done by the same SQL script as used for Real rollover process.
Must be compatible with PostgreSQL 10.x

Solution Design

Current scheme for rollover records - add a column/flag to mark every rollover if it is a real or a preview one - this will also keep the history of completed previews
mod-finance-storage-master\src\main\resources\templates\db_scripts\budget_encumbrances_rollover.sql - make this script parameterizable; pass a real/preview flag as the input parameter; use different tables depending on the flag value. This will allow not to duplicate the SQL script, but to use the same script for both the real rollover and the preview, and thereby get the correct results for the preview.
Add another table to store preview data, including Export budget and errors that have occurred; data from this table can then be exported to any convenient format or displayed on the UI

Data Model

Update schema "ledger_fiscal_year_rollover" with new "action" field

Property

Type

Read Only

Default

Required

Notes

action

enum ["Preview", "Commit", "Rollback"]

true

Commit

Y

Current scheme for rollover records - add a column/flag to mark every rollover if it is a real or a preview one - this will also keep the history of completed previews

Create duplicate of the Budget schema

New schema name : rollover_budget

Schema should be copy of ramls/acq-models/mod-finance/schemas/budget.json where all amounts are "persistent"(remove readOnly flag from all amounts). Also link on Ledger Rollover ("ledgerRolloverId")

rollover_budget

{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "description": "A budget",
  "type": "object",
  "javaName": "budget",
  "extends" : {
    "$ref" : "../../common/schemas/entity.json"
  },
  "properties": {
    "id": {
      "description": "UUID of this budget",
      "$ref": "../../common/schemas/uuid.json"
    },
    "ledgerRolloverId": {
      "description": "Ledger fiscal year rollover UUID",
      "$ref": "../../common/schemas/uuid.json"
    },
    "name": {
      "description": "The name of the budget",
      "type": "string"
    },
    "budgetStatus": {
      "description": "The status of the budget",
      "type": "string",
      "enum": [
        "Active",
        "Frozen",
        "Inactive",
        "Planned",
        "Closed"
      ]
    },
    "allowableEncumbrance": {
      "description": "The encumbrance percentage limit for this budget",
      "type": "number"
    },
    "allowableExpenditure": {
      "description": "The expenditure percentage limit for this budget",
      "type": "number"
    },
    "allocated": {
      "description": "The amount currently allocated to this budget",
      "type": "number"
    },
    "awaitingPayment": {
      "default": 0,
      "description": "The amount currently awaiting payment for this budget",
      "type": "number"
    },
    "available": {
      "description": "The amount currently available for this budge",
      "type": "number"
    },
    "encumbered": {
      "default": 0,
      "description": "The amount currently encumbered for this budget",
      "type": "number"
    },
    "expenditures": {
      "default": 0,
      "description": "The amount currently expended for this budget",
      "type": "number"
    },
    "netTransfers": {
      "default": 0,
      "description": "Summing all the transfers on this budget",
      "type": "number"
    },
    "unavailable" : {
      "description": "Unavailable amount for this budget",
      "type": "number"
    },
    "overEncumbrance": {
      "description": "The amount currently over-encumbered for this budget",
      "type": "number"
    },
    "overExpended": {
      "description": "Amount the budget is over expended.",
      "type": "number"
    },
    "fundId": {
      "description": "UUID of fund",
      "$ref": "../../common/schemas/uuid.json"
    },
    "fiscalYearId": {
      "description": "UUID of fiscal year",
      "$ref": "../../common/schemas/uuid.json"
    },
    "acqUnitIds": {
      "description": "acquisition unit ids associated with this budget",
      "type": "array",
      "items": {
        "$ref": "../../common/schemas/uuid.json"
      }
    },
    "tags": {
      "type": "object",
      "description": "arbitrary tags associated with this budget",
      "$ref": "../../../raml-util/schemas/tags.schema"
    },
    "metadata": {
      "type": "object",
      "$ref": "../../../raml-util/schemas/metadata.schema",
      "readonly": true
    },
    "initialAllocation": {
      "default": 0,
      "description": "The amount of the first allocation made to this budget",
      "type": "number"
    },
    "allocationTo": {
      "default": 0,
      "description": "The sum of all allocation transaction amounts made TO this budget not including the Initial allocation",
      "type": "number"
    },
    "allocationFrom": {
      "default": 0,
      "description": "The sum of all allocation transaction amounts made FROM this budget not including the Initial allocation",
      "type": "number"
    },
    "totalFunding": {
      "description": "budget.allocated + budget.netTransfers",
      "type": "number"
    },
    "cashBalance": {
      "description": "Total Funding minus Expended",
      "type": "number"
    }
  },
  "additionalProperties": false,
  "required": [
    "budgetStatus",
    "name",
    "fundId",
    "fiscalYearId"
  ]
}

Create new table "ledger_fiscal_year_rollover_budgets"

Schema for the table "ledger_fiscal_year_rollover_budgets"

{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "description": "Contains a list of budgets generated during the rollover. Also budgets include all calculated mounts",
  "type": "object",
  "properties": {
    "budgets": {
      "description": "The list of fiscal year rollover errors",
      "type": "array",
      "items": {
        "$ref": "rollover_budget.json"
      }
    },
    "totalRecords": {
      "description": "The number of records contained in this collection",
      "type": "integer",
      "minimum": 0
    }
  },
  "additionalProperties": false,
  "required": [
    "budgets",
    "totalRecords"
  ]
}

APIs

Ledger Rollover generated Budgets Business API

Ledger Rollover generated Budgets Storage API

Ledger Rollover Logs Business API

Method	Path	Request	Response	Description	Interface	Notes
GET	/finance/ledger-rollovers/logs/{id}	NA	preview_ledger_fiscal_year_rollover	GET logs of the ledger fiscal year rollover by id	finance.ledger-rollovers.logs.item.get	Status : 200
GET	/finance/ledger-rollovers/logs	NA	preview_ledger_fiscal_year_rollover_collection	GET logs of the ledger fiscal year rollovers	finance.ledger-rollovers.logs.collection.get	Status : 200

Preview Ledger Rollover Storage API

Method	Path	Request	Response	Description	Interface	Notes
GET	/finance-storage/ledger-rollovers/logs/{id}	NA	ledger_fiscal_year__logs_rollover	GET logs of the ledger fiscal year rollover by id	finance-storage.ledger-rollovers.logs.item.get	Status : 200
GET	/finance-storage/ledger-rollovers/logs	NA	ledger_fiscal_year_rollover__logs_collection	GET logs of the ledger fiscal year rollovers	finance-storage.ledger-rollovers.logs.collection.get	Status : 200

Design

Work Breakdown Structure

	Work	Comments	Story	~ Estimate
1	Update schema "ledger_fiscal_year_rollover" with new "action" field Create duplicate of the Budget schema where all amounts are "persistent" Define schema and table for preview rollover results → Define schema and table for preview rollover results	acq-models, mod-finance-storage, mod-finance	MODFISTO-319 - Getting issue details... STATUS	1
2	Define Storage API : Preview Ledger Rollover → Storage API : Preview Ledger Rollover Implement API → Should be without any logic. Business logic for POST will be implemented in scope of MODFISTO-318	mod-finance-storage	MODFISTO-320 - Getting issue details... STATUS	1
3	Define API : Preview Ledger Rollover → Business API : Preview Ledger Rollover Implement API → Should be Proxy to storage layer without any logic Sending email will be added in scope of MODFIN-257	mod-finance	MODFIN-256 - Getting issue details... STATUS	1
4	Spike : Implement POC with storing preview result in the temporary tables and after that upload results in the preview rollover table	mod-finance-storage All all open technical questions should be resolved	MODFISTO-322 - Getting issue details... STATUS	5
5	Update ledger rollover SQL script with preview rollover logic and store results in the preview rollover table	mod-finance-storage	MODFISTO-323 - Getting issue details... STATUS
6	Implement Preview rollover flow and update budgets with all calculated amounts Steps should be run in scope of one DB transaction : 1.1 Rollover script run (field LedgerFiscalYearRollover."action" = "Preview") 1.2 Retrieve result of preview from table "preview_ledger_fiscal_year_rollover", where budgets amounts will be partially filled (MODFISTO-323) 1.3. Go through all budgets and calculate remaining amounts → org.folio.utils.CalculationUtils#calculateBudgetSummaryFields 1.4. Storing updated budgets in the rollover preview table → preview_ledger_fiscal_year_rollover	mod-finance-storage	MODFISTO-318 - Getting issue details... STATUS
7	Spike : To gain knowledge about mod-email and implement example with usage email sending	mod-finance	MODFIN-258 - Getting issue details... STATUS
8	Implement sending email and include it in the preview ledger rollover flow	mod-finance	MODFIN-257 - Getting issue details... STATUS

Open Items

Question	Answer	Story

Preview Ledger Fiscal Year Rollover