...

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

...

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

...

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).

Create new table "preview_ledger_fiscal_year_rollover"

Schema for the table "preview_ledger_fiscal_year_rollover"Also link on Ledger Rollover ("ledgerRolloverId")

{
  "$schema": "http://json-schema.org/draft-04/schema#",
  "description": "Preview ledger fiscal year rolloverA budget",
  "type": "object",
  "propertiesjavaName": {
    "id": {
   budget",
  "descriptionextends" : "The unique id of this category",
 {
    "$ref" : "../../common/schemas/uuidentity.json"
 
  },

    "ledgerFiscalYearRolloverproperties": {
 
    "descriptionid": "Fiscal year rollover",{
      "typedescription": "objectUUID of this budget",
      "$ref": "ledger_fiscal_year_rollover../../common/schemas/uuid.json"
    },
    "previewResultledgerRolloverId": {
      "description": "ResultLedger fiscal ofyear previewrollover runUUID",
      "type$ref": "object",../../common/schemas/uuid.json"
      "$ref": "preview_ledger_fiscal_year_rollover_result.json"
    },
    "totalRecordsname": {
      "description": "The numbername of records contained in this collectionthe budget",
      "type": "integerstring",
    },
    "minimumbudgetStatus": 0
   {
}   },   "additionalPropertiesdescription": false,"The status of the budget",
      "type": "string",
      "requiredenum": [
        "ledgerFiscalYearRolloverActive",
        "totalRecords"Frozen",
       ]
}

Schema for the preview result "preview_ledger_fiscal_year_rollover_result"

{
  "$schema": "http://json-schema.org/draft-04/schema# "Inactive",
        "Planned",
  "description": "Preview ledger fiscal year rollover result"Closed",
  "type": "object",    ]
   "properties": { },
    "errorsallowableEncumbrance": {
      "description": "The listencumbrance ofpercentage fiscallimit yearfor rolloverthis errorsbudget",
      "type": "arraynumber",
    },
    "itemsallowableExpenditure": {
 
      "$refdescription": "ledger_fiscal_year_rollover_error.json"
      }The expenditure percentage limit for this budget",
      "type": "number"
    },
    "budgetsallocated": {
      "description": "The amount currently listallocated ofto plannedthis budgetsbudget",
      "type": "arraynumber",
    },
    "itemsawaitingPayment": {
 
      "$refdefault": "preview_rollover_budget.json"0,
      }
"description": "The amount currently awaiting payment for this budget",
      "type": "number"
    },
    "totalRecordsavailable": {
      "description": "The numberamount ofcurrently recordsavailable contained infor this collectionbudge",
      "type": "integernumber",
    },
    "minimumencumbered": 0{
    }   },"default": 0,
      "additionalPropertiesdescription": false, "The amount currently encumbered for this budget",
      "requiredtype": [ "number"
    },
    "totalRecords"expenditures": {
     ]
}

APIs

...

 "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

{
  "$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"

    {
      "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

Schema name : ledger_fiscal_year_rollover_log_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

Ledger Rollover generated Budgets Storage API

Ledger Rollover Logs Business API

Preview Ledger Rollover Storage API

Design

Work Breakdown Structure

Design

Work Breakdown Structure

...