Preview Ledger Fiscal Year Rollover

Owned by Andrei Makaranka
Last updated: Aug 09, 2022
9 min read
AuthorAndrei 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 DONE

PO review

Dennis Bridges DONE

Revision History

VersionDateOwnerDescriptionReason
v1.006.14.2022Andrei Makaranka Initial version
v2.006.22.2022

Andrei Makaranka 

Schema changed and WBS added
v2.106.30.2022

Andrei Makaranka 

Status for preview added
v3.007.07.2022Andrei Makaranka Redesign and rollover logs functionality added Requirements changed
v4.008.03.2022Andrei Makaranka Rename "action" field tp "rolloverType"

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 (warning)

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

PropertyTypeRead OnlyDefaultRequiredNotes

rolloverType

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


falseCommit

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 ledger fiscal year rollover Budget schemas

Shema name : ledger_fiscal_year_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")

ledger_fiscal_year_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"
  ]
}

Schema name: ledger_fiscal_year_rollover_budget_collection

ledger_fiscal_year_rollover_budget_collection 
{
  "$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": "ledger_fiscal_year_rollover_budget.json"
      }
    },
    "totalRecords": {
      "description": "The number of records contained in this collection",
      "type": "integer",
      "minimum": 0
    }
  },
  "additionalProperties": false,
  "required": [
    "budgets",
    "totalRecords"
  ]
}

Create new DB Table "ledger_fiscal_year_rollover_budget" → schema.json

In the database will be stored records with type of "ledger_fiscal_year_rollover_budget"

ledger_fiscal_year_rollover_budget 
    {
      "tableName": "ledger_fiscal_year_rollover_budget",
      "fromModuleVersion": "mod-finance-storage-8.1.7",
      "withMetadata": true,
      "index": [
        {
          "fieldName": "ledgerRolloverId",
          "tOps": "ADD",
          "caseSensitive": false,
          "removeAccents": true
        },
        {
          "fieldName": "fundId",
          "tOps": "ADD",
          "caseSensitive": false,
          "removeAccents": true
        },
        {
          "fieldName": "fiscalYearId"
        }
      ],
      "foreignKeys": [
        {
          "fieldName": "ledgerRolloverId",
          "targetTable": "ledger_fiscal_year_rollover",
          "tableAlias": "ledgerFYROBudget",
          "targetTableAlias": "ledgerFYRO",
          "tOps": "ADD"
        }
      ]
    }

Create ledger fiscal year rollover Logs schemas 

Schema name : ledger_fiscal_year_rollover_log

PropertyTypeRead OnlyDefaultRequiredNotes

ledgerRolloverId

UUID


trueNA

Y


startDatedate-timetrueNAYThis is "creratedDate" from rollover metadata. 
endDatedate-timetrueNA
This is "updatedDate" from rollover metadata. Should be provided only when "status" is "Success" or "Error". 
rolloverStatusramls/acq-models/mod-finance/schemas/rollover_status.jsontrueNAY
errorsLinkString or patterntrueNANIf possible Link should included environment URL : https://okapi-bugfest-lotus.int.aws.folio.org/finance/ledger-rollovers-errors?query=ledgerRolloverId=={id}fff-e-errors 
budgetsLinkString or patterntrueNANIf possible Link should included environment URL : https://okapi-bugfest-lotus.int.aws.folio.org/finance/ledger-rollovers-budgets?query=ledgerRolloverId=={id}fff-
ledgerRolloverTypeenum ["Preview", "Commit", "Rollback"]trueNAY


Schema name : ledger_fiscal_year_rollover_log_collection

ledger_fiscal_year_rollover_budget_collection 
{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "description": "Contains a list of rollover runs",
  "type": "object",
  "properties": {
    "logs": {
      "description": "The list of  rollover runs",
      "type": "array",
      "items": {
        "$ref": "ledger_fiscal_year_rollover_log.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

Method

Path

Request

Response

Description

Interface

Notes

GET/finance/ledger-rollovers-budgets/{id}NA

ledger_fiscal_year_rollover_budget

GET budgets of the ledger fiscal year rollover by idfinance.ledger-rollovers-budgets.item.getStatus : 200
GET/finance/ledger-rollovers-budgetsNAledger_fiscal_year_rollover_budget_collectionGET budgets of the ledger fiscal year rolloversfinance.ledger-rollovers-budgets.collection.getStatus : 200

Ledger Rollover generated Budgets Storage API

Method

Path

Request

Response

Description

Interface

Notes

GET/finance/ledger-rollovers-budgets/{id}NA

ledger_fiscal_year_rollover_budget

GET budgets of the ledger fiscal year rollover by idfinance.ledger-rollovers-budgets.item.getStatus : 200
GET/finance/ledger-rollovers-budgetsNAledger_fiscal_year_rollover_budget_collectionGET budgets of the ledger fiscal year rolloversfinance.ledger-rollovers-budgets.collection.getStatus : 200

Ledger Rollover Logs Business API

Method

Path

Request

Response

Description

Interface

Notes

GET/finance/ledger-rollovers-logs/{id}NA

ledger_fiscal_year_rollover_log

GET logs of the ledger fiscal year rollover by idfinance.ledger-rollovers-logs.item.getStatus : 200
GET/finance/ledger-rollovers-logsNA

ledger_fiscal_year_rollover_log_collection

GET logs of the ledger fiscal year rolloversfinance.ledger-rollovers-logs.collection.getStatus : 200

Design


Work Breakdown Structure


WorkCommentsStory~ Estimate
1
  1. Update schema "ledger_fiscal_year_rollover" with new "action" field
  2. Define schema and table for preview rollover results → Define new schemas and table

 2.1. Create ledger fiscal year rollover Budget schemas
 2.2. Create ledger fiscal year rollover Log schemas
 2.3. Define new table for storing generated budgets in scope of Rollover

acq-models, mod-finance-storage, mod-finance

MODFISTO-319 - Getting issue details... STATUS

1
2
  1. Define Storage API : Preview Ledger Rollover → Storage API : Ledger Rollover Budgets
  2. Implement API → Should be without any logic.
 mod-finance-storage

MODFISTO-320 - Getting issue details... STATUS

1
3
  1. Define and Implement Budgets API → Budgets Ledger Rollover Business API
  2. Define and Implement Logs API → Logs view should be built accordingly with ledger_fiscal_year_rollover_log Ledger Rollover Logs Business API
mod-finance

MODFIN-256 - Getting issue details... STATUS

1
4
  1. 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
  1. 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

  1. 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 "ledger_fiscal_year_rollover_budget", 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 → ledger_fiscal_year_rollover_budget

  2. Update delete rollover logic : org.folio.service.rollover.LedgerRolloverService#deleteRollover. Rollover record  deletion should happened together with rollover budgets in scope of one DB transaction

mod-finance-storage

MODFISTO-318 - Getting issue details... STATUS


7

Replace unique index in the table "ledger_fiscal_year_rollover" with uniqueness logic in the code

  1. Remove index "ledgerId_fromFiscalYearId" from table "ledger_fiscal_year_rollover"
  2. Implement validation logic in the separate validation service:

2.1 If incoming rollover is real with action "Commit", then rollover record should be unique for pair "ledgerId, fromFiscalYearId" (the same as in the index)

2.2 If incoming rollover is real with action "Preview", then rollover record should not be unique for pair "ledgerId, fromFiscalYearId"  and it possible to have many previews for one ledger and same fiscal year.

mod-finance-storage

MODFISTO-327 - Getting issue details... STATUS


8
  1. Spike : To gain knowledge about mod-email and implement example with usage email sending
mod-finance

MODFIN-258 - Getting issue details... STATUS


9
  1. Implement sending email and include it in the preview ledger rollover flow
mod-finance

MODFIN-257 - Getting issue details... STATUS




Open Items

QuestionAnswerStory