Page tree
Skip to end of metadata
Go to start of metadata

Overview

Most of the finance schemas are well out of date and need to be updated.  Recent clarifications on requirements have also introduced new complexities and challenges that need to be overcome.  Given the scope of these changes and how interrelated the various schemas and APIs will be, creating a wiki page to capture this information seemed to make sense.

Schemas

Schemas exist for most of the financial records, but will need to be updated as they're out of date.  The Acquisitions Interface Fields maybe be helpful to some degree, but isn't a direct 1-to-1 mapping with the schemas due to intricacies of the data model, e.g. internal/storage use are needed which aren't captured in that spreadsheet.

Acquisition Units

The complex relationships between the various record types makes it difficult to use an inheritance pattern like we've used in other apps.  For this reason, most finance records will have an acquisitionUnits field.  How these are applied is slightly different as well.  The fiscal year detail view for instance displays ledgers, groups and funds (budgets).  In order to keep things reasonably simple, if the user has the folio permission to view the fiscal year, and belongs to one of the acquisition units assigned to the fiscal year record, the ledger, group and fund (budget) data can also be viewed.  The same concept applies across the board - e.g. ledgers detail view shows groups and funds (budgets).  We will need to get creative with our APIs and database views to make this work.

Transactions

  • Immutable
  • Payments, credits, allocations, transfers, encumbrance (see below)
  • Each of these will have their own API to allow for business logic rules, but may share a table in the storage layer.
  • Since there isn't a directly link to a budget, Querying by toFundId, fromFundId, and fiscalYearId is needed to satisfy:
    • GET transactions by Budget
  • Single transactions table, shared by multiple APIs

Schemas

transaction

PropertyTypeDefaultRequiredNotes
idstring
NUUID - System generated if not specified
amountnumber
YIdeally we could filter by a range of values; also be able to search by exact amount of transaction.

NOTE: For encumbrances: This is initialAmountEncumbered - (amountAwaitingPayment + amountExpended)
descriptionstring
N
sourcestring
YThe readable identifier of the record that resulted in the creation of this transaction
transactionTypestring
YThis describes the type of transaction
fromFundIdstring
N

UUID of the fund money is moving from

toFundIdstring
NUUID of the fund money is moving to
fiscalYearIdstring
YUUID of the fiscal year that the transaction is taking place in
sourceInvoiceIdstring
NUUID of the Invoice that triggered the creation of this transaction
sourceInvoiceLineIdstring
NUUID of the InvoiceLine that triggered the creation of this transaction.  Needed to support an idempotent payments/credit API
sourceFiscalYearIdstring
NUUID of the fiscal year that triggered the creation of this transaction (Used during fiscal year rollover)
tagstags
N
currencystring
Ycurrency code for this transaction - from the system currency.
paymentEncumbranceIdstring
NUUID of the encumbrance associated with this payment/credit taking place.
encumbranceencumbrance
NEncumbrance sub-object - holds encumbrance-specific information not applicable to other transaction types
metadatametadata
NSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

encumbrance

PropertyTypeDefaultRequiredNotes
initialAmountEncumberednumber
YShouldn't change once create
amountAwaitingPaymentnumber
N
amountExpendednumber
N
statusstring
Yenum: Released, Unreleased
orderTypestring
YTaken from the purchase order.  enum:  One-time, Ongoing 
subscriptionboolean
YTaken from the purchase order.  Has implications for fiscal year rollover
reEncumberboolean
YTaken from the purchase order.  Has implications for fiscal year rollover
sourcePurchaseOrderIdstring
YUUID of the purchase order associated with this encumbrance
sourcePoLineIdstring
YUUID of the poLine associated with this encumbrance

awaiting_payment

PropertyTypeDefaultRequiredNotes
encumbranceIdstring
YUUID of the encumbrance to updated
amountAwaitingPaymentnumber
YThe amount of money moving from encumbered → awaitingPayment
releaseEncumbranceboolean
YWhether or not remaining encumbered money should be released 

invoice_transaction_summary

PropertyTypeDefaultRequiredNotes
idUUID
YUUID of the invoice these payments are associated with (Unique)
invoiceIdUUID
YUUID of the invoice these payments are associated with (Unique)
numEncumbrancesint
YTotal number of encumbrances expected for this invoice
numPaymentsCreditsint
YTotal number of payments/credits expected for this invoice

temporary_invoice_payments

PropertyTypeDefaultRequiredNotes
idUUID
NUUID of the payment (Unique) - Generated by the system
invoiceIdUUID
YUUID of the invoice these payments are associated with 
transactiontransaction (jsonb)
YThe payment or credit

NOTE:  Since these are for internal consumption only, we might consider just using regular old tables for both of these instead of creating full blown schemas.  (Update) - now that we're exposing invoice-transaction-summary APIs in the BL layer this may not make sense for that table anymore.  I think we're going to have to create a schema

Potential Optimization:  It might be worth pulling some information (e.g. fundId, amount, etc.) out into distinct columns of the temp table to make it easier/faster to apply the payments and update totals once they're all received... 

order_transaction_summary

PropertyTypeDefaultRequiredNotes
idUUID
YUUID of the order these payments are associated with (Unique)
purchaseOrderIdUUID
YUUID of the order these payments are associated with (Unique)
numTransactionsint
YTotal number of transactions (encumbrances) expected for this order

temporary_order_transactions

PropertyTypeDefaultRequiredNotes
idUUID
NUUID of the transaction (Unique) - Generated by the system
encumbrance_sourcePurchaseOrderIdUUID
YUUID of the order these payments are associated with 
transactiontransaction (jsonb)
YThe transaction (encumbrance)

APIs

Business Logic Module

MethodPathRequestResponseDescriptionNotes
POST/finance/allocationstransactiontransactionCreate an allocationcalls POST /finance-storage/transactions
POST/finance/creditstransactiontransaction

Create a credit

calls POST /finance-storage/transactions
POST/finance/paymentstransactiontransactionCreate a paymentcalls POST /finance-storage/transactions
POST/finance/transferstransactiontransactionCreate a transfercalls POST /finance-storage/transactions
POST/finance/encumbrancestransactiontransactionCreate an encumbrancecalls POST /finance-storage/transactions
GET/finance/transactions/<id>NAtransactionGet a transaction by Id
GET/finance/transactionsCQL Querytransaction_collectionSearch/List transactions
PUT/finance/encumbrance/<id>transactiontransactionUpdate an encumbranceOnly allowed if transactionType == encumbrance.  Needed for updating encumbrances (e.g. encumbered → awaiting payment)
POST/finance/invoice-transaction-summariesinvoice_transaction_summaryinvoice_transaction_summaryCreate an invoice transaction summarytells finance how many transactions (encumbrances/ payments & credits) to expect for a particular invoice.  Create if a record doesn't already exist for the invoice, otherwise return existing record.
POST/finance/order-transaction-summariesorder_transaction_summaryorder_transaction_summaryCreate an order transaction summarytells finance how many transactions (encumbrances) to expect for a particular order.  
POST/finance/awaiting-paymentawaiting_payment201Move money from encumbered → awaitingPaymentUpdates the specified encumbrance
POST/finance/release-encumbrance/<encumbranceId>NA201Release any remaining money encumbered back to the budget's available poolUpdates the specified encumbrance
GET/finance/invoice-transaction-summaries/<invoiceId>NAinvoice_transaction_summaryGet a invoice payment summary
DELETE/finance/invoice-transaction-summaries/<invoiceId>NA204Delete an invoice-transaction-summary

NOTE:  While the various POST endpoints all talk to the same storage API, they live as separate endpoints to allow for business logic to differ between transaction types.

NOTE:  Intentionally omitted DELETE transaction endpoint here since transactions are immutable.  The storage module provides a DELETE endpoint for purposes of cleaning up API test artifacts, etc.

NOTE:  May need to add a DELETE /finance/encumbrance/<id> to support the ability to cleanup encumbrances for orders that are removed/purged.

Update: Probably not needed since this cleanup would be done by one of the business logic modules (mod-orders).  I don't think we'll need to expose this functionality directly to the user/client.

Storage Module

MethodPathRequestResponseDescriptionNotes
POST/finance-storage/transactionstransactiontransactionCreate a transactionUpdate transactions, budget(s), ledger(s) all within a DB transaction.
GET/finance-storage/transactions/<id>NAtransactionGet a transaction by Id
GET/finance-storage/transactionsCQL Querytransaction_collectionSearch/List transactions
PUT/finance-storage/transactions/<id>transactiontransactionUpdate an encumbrance

Only allowed if transactionType == encumbrance

Update transactions, budget(s), ledger(s) all within a DB transaction.

DELETE/finance-storage/transactions/<id>NA204Delete a transactionfor internal/emergency admin use only.
POST/finance-storage/invoice-transaction-summariesinvoice_transaction_summaryinvoice_transaction_summaryCreate an invoice transaction summarytells finance how many transactions (encumbrances / payments & credits) to expect for a particular invoice
GET/finance-storage/invoice-transaction-summaries/<invoiceId>NAinvoice_transaction_summaryGet a invoice transaction summary
DELETE/finance-storage/invoice-transaction-summaries/<invoiceId>NA204Delete an invoice-transaction-summary
POST/finance-storage/order-transaction-summariesorder_transaction_summaryorder_transaction_summaryCreate an order transaction-summarytells finance how many transactions (encumbrances) to expect for a particular order.  Create if a record doesn't already exist for the order, otherwise return existing record.
GET/finance-storage/order-transaction-summaries/<purchaseOrderId>NAorder_transaction_summaryGet a order transaction summary
DELETE/finance-storage/order-transaction-summaries/<purchaseOrderId>NA204Delete an order-transaction-summary

Encumbrance as a Transaction

  • needs to be viewed as a transaction, but can't be immutable 
  • cross (x) Option A: make encumbrance an superset of transaction, store in separate table, and use views/joins to satisfy search/filter needs.
    • Some of the field names are unintuitive - "amount" for instance is vague and potentially confusing given that we have other fields: "initalAmountEncumbered", "amountAwaitingPayment", and "amountExpended"
  •  Option B: make encumbrance a special-case of transaction.  This involves adding a bunch of optional fields to the transaction schema which would only be used for the encumbrance type of transaction.  
    • Putting these extra fields in an "encumbrance" sub-object might make sense - would allow for some schema validation to be used instead of needing to do it all in code.
    • This would allow us to store all transaction types in a single table and avoid having to deal with views/joins and confusing field names.
    • Need to implement a PUT endpoint for transactions, to support this approach, but can restrict its use (in code) to only transactions of type "encumbrance".  I don't like putting even this much business logic in the storage layer, but it's fairly limited and allows for a cleaner design.
      • See below the API table about possibly not needing this endpoint.

Updating Allocated/Unavailable/Available and Other Running Totals

  • cross (x)Option A:  Have the business logic module perform calculations as money is moved
    • Harder to guarantee consistency/accuracy - is eventual consistency OK for finances?
    • Keeps business logic out of the storage module, consistent with approach taken in other acquisition apps
  • cross (x)Option B:  Perform summary/total calculations in the storage module and update all tables within a transaction.  Either they all succeed, or the entire operation fails
    • Much better control over consistency/accuracy
    • Bleeds business logic into the storage module
    • Other business logic remains in mod-finance - e.g. acquisitionUnits, what can be modified vs what can't, etc.
  • cross (x)Option C:  Calculate these numbers on-the-fly in the business logic module
    • Difficult/inefficient to do if these numbers are needed to be shown in search results (they are)
    • Likely means we'll need a more complicated data model with more views and schemas
    • Since we're calculating these numbers on the fly, consistency is less of a problem... the numbers only live in one place
  • Option D: Hybrid of B and C
    • Calculate most of these totals in the storage module and update all tables in a transaction
    • For some special cases, we need to perform calculations on the fly
      • e.g. GET groups for a ledger/FY - Only sum the amounts from the funds in those groups which are part of the ledger for this FY...

Preventing Partially Paid Invoices

When an invoice is paid, numerous transactions (payments, credits) will be generated.  Either all or none of these need to be successfully processed; we can't have partially paid invoices.  This presents a challenge of scale.  There could feasibly be hundreds of payments for an invoice.  Several options were weight and the most attractive approach involves collecting or aggregating payments/credits in the storage layer until all are received, then processing them all at once in a database transaction.  The other options are listed in the appendix.

This approach keeps the payment "simple" and consistent with the other transaction APIs.  The storage layer performs some aggregation in the database of payments/credits based on the sourceInvoiceId specified in each payment.  The flow looks something like this:

Introduce new tables: 

  • invoice_transaction_summary - Contains an invoiceId and the total number of transactions (payments/credits) expected when "paying" this invoice.
  • temp_invoice_payments - Temporary storage of payments until all payments related to the invoice are received.

When an invoice is paid

  • mod-invoice calls POST /finance/invoice-transaction-summary 
    • mod-finance calls mod-finance-storage to create the entry if one doesn't already exist
  • mod-invoice generates payments and calls POST /finance/payments
    • mod-finance checks calls POST /finance-storage/payments

When an payment is received, 

  • Persist the payment record in a temp_invoice_payments table
  • Count the number of records in the temp_invoice_payments table.  If this is the last payment, (the number of entries in the temp table == numTransactions)
    • In a database transaction:
      • Apply the payments/credits from the temp_invoice_payments table and perform the summary # updates (e.g. update budget, ledger, encumbrances, etc. - unavailable/available/encumbered/expended/etc.)
        • If everything works, cleanup the temp_invoice_payments table and return 200 OK
        • If something fails, no actual records are updated.  The payment API call can fail and can be retried (POST payment/credit are idempotent)

When an "approved" invoice is removed:

  • mod-invoice makes a call to mod-finance to ensure that the invoice_transaction_summary has been cleaned up.  These records are small so even if on is orphaned (which shouldn't happen often if ever) it's not a huge deal. 
  • DELETE /finance-storage/invoice-transaction-summary/<invoiceId>

If a payment request fails

  • mod-finance can safely retry it.
  • After N failed tries an error can be returned to mod-invoice and/or the client who can later try to pay the invoice again once the (network?) problem is resolved.

"All or Nothing" Operations

There are several situations in acquisitions where a bunch of transactions need to happen all at once.  Often the situation is such that either all of these transactions need to succeed, or none of them should.  We first crossed this in the context of preventing partially paid invoices (see above).  Fortunately, these cases are similar enough to one another that the design work and lessons learned during that work can be borrowed/reused.  There scenarios can be split into two categories:

Transactions applying to an invoice

  • When approving an invoice, money needs to move from encumbered to awaitingPayment in both the encumbrances and the corresponding budgets.  It's also possible that encumbrances need to be released at this point, e.g. we encumbered $100 but the invoice is for $90, so we "release" $10 back to the budget's "available" pool
  • When paying an invoice, payments and credits are made.  See the earlier section "Preventing Partially Paid Invoices" for details.

Here I think we can re-use the "invoice_transaction_summary" and temporary transaction table almost as-is for both of these cases.  We might want to rename "invoice_transaction_summary" to a more generic "invoice_transaction_summary".  What this means is that a mod-invoice would create the invoice summary record when the invoice is approved instead of when the invoice is paid.  The number of transactions should be the same for both of these events.  When determining if all of the payments/credits have been received, we'll need to also look at the transaction type to essentially ignore the "encumbrance" transactions in the temporary table.  The temporary table can be cleaned up, but there's not guarantee on the timing of that, so we can't assume that only payments/credits will be present at the time of an invoice being paid.

Transactions applying to an Order

  • When opening an order, encumbrances are created.  We need to ensure that all encumbrances for an order are created before opening an order.
  • When closing an order, encumbrances need to be released.  That is if we encumbered money that we didn't end up spending, it needs to be "released" or moved back into the budget's "available" pool.

We looked at handling this in a similar fashion to creation of records in inventory; i.e. when opening the order we try to create all of the encumbrances, and if any of them fail, we return an appropriate error response and leave the order in "Pending" state.  However, it was determined that was not a viable approach since it opens the door to having encumbrances in the system for "pending" orders.  

The idea for handling this is similar to what we do above for invoices... 

mod-finance-storage

  • Create an order_transaction_summaries table and API to sit in front of it
  • Create a temporary table for holding encumbrances (transactions)
  • Add a link to the order (POL?) in the encumbrance sub-object of transaction.  This is akin to sourceInvoiceId/sourceInvoiceLineId and is needed to accumulate all of the encumbrances associated with an order before applying changes.
  • Once all encumbrances for an order are received, apply the changes in a DB transaction

These new tables will be used for both opening an order (create encumbrances) as well as closing an order (releasing encumbrances).  The encumbrance status field will help differentiate the transactions for each of these events in the temporary table.

Business Logic API changes

The current design includes a generic PUT /finance/encumbrances/<id> endpoint that would be sufficient for this approach.  However, this generic PUT endpoint doesn't really allow the caller to specify exactly what they're trying to do, making it difficult to properly validate the input.  By incorporating specific endpoints for the various actions, we clarify the context.  By having these endpoints take input that isn't a full blown transaction, we can save ourselves a lot of validation.

  • POST /finance/awaiting-payment - takes a new schema w/ encumbranceId, amount.  Retrieves the specified encumbrance from storage, applies the change and sends the updated record back to storage.
  • POST /finance/release-encumbrance/<id> - no request payload.  Retrieves the specified encumbrance, applies the changes and sends the updated record back to storage.

NOTE:  These endpoints are slightly different from the rest of the finance API in that they're command endpoints, and aren't very RESTful in their design.  That said, there is some precedence for this in several other places in acquisitions, e.g. mod-orders receiving/check-in endpoints

Calculations

Allocations

Update Budget identified by the transactions fiscal year (fiscalYearId) and source fund (fromFundId)

  • If "fromFundId" is present,
    • allocated decreases by transaction amount
    • available decreases by transaction amount
    • recalculate overEncumbered
    • recalculate overExpended

Update Budget identified by the transaction's fiscal year (fiscalYearId) and the destination fund (toFundId)

  • allocated increases by the transaction amount
  • available increases by the transaction amount
  • recalculate overEncumbered
  • recalculate overExpended

Update LedgerFY identified by the transaction's fiscal year (fiscalYearId) and the source fund (fromFundId)

  • If "fromFundId" is present
    • allocated decreases by transaction amount
    • available decreases by transaction amount

Update LedgerFY identified by the transaction's fiscal year (fiscalYearId) and the source fund (toFundId)

  • allocated increases by the transaction amount
  • available increases by the transaction amount

Transfers

Update Budget identified by the transactions fiscal year (fiscalYearId) and source fund (fromFundId)

  • If "fromFundId" is present,
    • available decreases by the transaction amount
    • unavailable increases by the transaction amount
    • recalculate overEncumbered
    • recalculate overExpended

Update Budget identified by the transactions fiscal year (fiscalYearId) and source fund (toFundId)

  • available increases by the transaction amount
  • unavailable decreases by the transaction amount
  • recalculate overEncumbered
  • recalculate overExpended

Update LedgerFY identified by the transactions fiscal year (fiscalYearId) and source fund (fromFundId)

  • If "fromFundId" is present,
    • available decreases by the transaction amount
    • unavailable increases by the transaction amount

Update LedgerFY identified by the transactions fiscal year (fiscalYearId) and source fund (toFundId)

  • available increases by the transaction amount
  • unavailable decreases by the transaction amount

Payments

Update the encumbrance identified by the transactions (paymentEncumbranceId)

  • awaitingPayment decreases by transaction amount
  • expended increases by transaction amount
  • amount decreases by the transaction amount

NOTE:  if there is no encumbrance associated with the payment, the budget's available decreases and unavailable increases.

Update Budget identified by the transaction's fiscal year (fiscalYearId) and the source fund (fromFundId)

  • awaitingPayment decreases by transaction amount
  • expenditures increases by transaction amount
  • recalculate overExpended

Credits

Update the encumbrance identified by the transactions (paymentEncumbranceId)

  • awaitingPayment increases by transaction amount
  • expended decreases by transaction amount (min 0)
  • amount (effectiveEncumbrance) is recalculated
  • if(status == Released), the encumbered amount is immediately released (see update encumbrance section below) 

NOTE:  if there is no encumbrance associated with the credit, the budget's available increases and unavailable decreases (min 0).

Update Budget identified by the transaction's fiscal year (fiscalYearId) and the destination fund (toFundId)

  • awaitingPayment increases by transaction amount
  • encumbered increases by the transaction amount
  • expenditures decreases by transaction amount (min 0)
  • recalculate overExpended

Encumbrances

Upon Creation

  • Update Budget identified by the transaction fiscal year (fiscalYearId) and the source fund (fromFundId)
    • encumbered increases by the transaction amount
    • available decreases by the transaction amount
    • unavailable increases by the transaction amount
    • overEncumbered is recalculated
  • Update LedgerFY identified by the transaction fiscal year (fiscalYearId) and the source fund (fromFundId)
    • available decreases by the transaction amount
    • unavailable increases by the transaction amount

Upon Update

  • Update Budget identified by the transaction fiscal year (fiscalYearId) and the source fund (fromFundId)
    • encumbered decreases by the difference between provided and previous transaction.encumbrance.amountAwaitingPayment values
    • awaitingPayment increases by the same amount
    • encumbrance transaction.amount is updated to (initial encumbrance - awaiting payment - expended)
  • If encumbrance.status = Released 
    • Update the encumbrance
      • transaction.amount becomes 0 (save the original value for updating the budget)
    • Update the budget identified by the transaction fiscal year (fiscalYearId) and the source fund (fromFundId)
      • encumbered decreases by the amount being released
      • available increases by the amount being released
      • unavailable decreases by the amount being released (min 0)

Transaction Restrictions

It's often necessary to check if a transaction should be allowed to happen based on the current budget values.  This section explains these restrictions and how they're applied.

Payments

There are several factors that come into play here.  The flowchart below shows how we determine if a payment can be accepted or should be rejected.  

The "Remaining Allowable Exp." in the flowchart is calculated as follows:

[remaining amount we can expend] = (allocated * allowableExpenditure) - (allocated - (unavailable + available)) - (awaitingPayment + expended)

The "restrictExpenditures" flag actually lives in the ledger, so we'll need to find traverse:  budget → fund → ledger.restrictExpenditures  

Encumbrance

Creating encumbrances requires a similar set of checks as described above for payments.

The "Remaining Allowable Enc." in the flowchar is calculated as follows:

[remaining amount we can encumber] = (allocated * allowableEncumbered) - (allocated - (unavailable + available)) - (encumbered + awaitingPayment)

The "restrictEncumbrances" flag actually lives in the ledger, so we'll need to find traverse:  budget → fund → ledger.restrictEncumbrances  

FundTypes

  • Simple controlled vocabulary

Schemas

fund_type

PropertyTypeDefaultRequiredNotes
idstring
NoUUID - System generated if not specified

name

string
Yes
countstring
No

The number of funds w/ this fund-type

Not persisted (read only) - populated by mod-finance when listing fund-types

APIs

Business Logic Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance/fund-typesfund_typefund_typeCreate a fund-typefinance.fund-types
GET/finance/fund-types/<id>NAfund_typeGET fund-type by Idfinance.fund-types
GET/finance/fund-typesCQL Querycollection<fund_type>Search/List fund-typesfinance.fund-typesQueries GET /finance-storage/fund-types, and augments the results with count/usage info.  Will need to make additional calls to GET /finance-storage/funds?query=fundType=XYZ to get this information.  These queries can happen in parallel.
PUT/finance/fund-types/<id>fund_typefund_typeUpdate a fund-typefinance.fund-types
DELETE/finance/fund-types/<id>NA204Delete a fund-typefinance.fund-types

Storage Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance-storage/fund-typesfund_typefund_typeCreate a fund-typefinance-storage.fund-types
GET/finance-storage/fund-types/<id>NAfund_typeGET fund-type by Idfinance-storage.fund-types
GET/finance-storage/fund-typesCQL Querycollection<fund_type>Search/List fund-typesfinance-storage.fund-types
PUT/finance-storage/fund-types/<id>fund_typefund_typeUpdate a fund-typefinance-storage.fund-types
DELETE/finance-storage/fund-types/<id>NA204Delete a fund-typefinance-storage.fund-types

Funds

  • Record with info that doesn't change from FY to FY (name, code, acq. units, etc.)
  • Foreign Keys;
    • funds.ledgerId = ledgers.id

Schemas

fund

PropertyTypeDefaultRequiredNotes
idstring
NoUUID - System generated if not specified

name

string
Yese.g. African History
codestring
YesUnique.  e.g. AFRICAHIST
fundStatusstring
Yesenum - Active, Inactive, Frozen
fundTypeIdstring
NoA descripter that allows the users to categorize funds and drive functionality in workflows like rollover etc. controlled vocab.
externalAccountNostring

No

Corresponding account in the financial system. Will be recorded in payment generated as well.
descriptionstring
No
allocatedFromIdsarray<string>
NoUUIDs of funds money can be allocated from
allocatedToIdsarray<string>
NoUUIDs of funds money can be allocated to
tagstags
No
ledgerIdstring
YesUUID of the ledger this fund belongs to
acqUnitIdsarray<string>
NoArray of UUIDs corresponding to the acquisition units assigned to this fund.
metadatametadata
NoSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

compositeFund

This schema is essentially a fund w/ one additional field - groupIds.  The business logic module's fund APIs will use this schema, and will manage the fund and group/fund relationships independently via calls to the storage APIs.  This simplifies things on the UI end.

PropertyTypeDefaultRequiredNotes
idstring
NoUUID - System generated if not specified

name

string
Yese.g. African History
codestring
YesUnique.  e.g. AFRICAHIST
fundStatusstring
Yesenum - Active, Inactive, Frozen
fundTypeIdstring
NoA descripter that allows the users to categorize funds and drive functionality in workflows like rollover etc. controlled vocab.
externalAccountNostring

No

Corresponding account in the financial system. Will be recorded in payment generated as well.
descriptionstring
No
allocatedFromIdsarray<string>
NoUUIDs of funds money can be allocated from
allocatedToIdsarray<string>
NoUUIDs of funds money can be allocated to
tagstags
No
ledgerIdstring
YesUUID of the ledger this fund belongs to
acqUnitIdsarray<string>
NoArray of UUIDs corresponding to the acquisition units assigned to this fund.
groupIdsarray<string>
NoArray of UUIDs corresponding to the groups associated with this fund.
metadatametadata
NoSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

APIs

Business Logic Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance/fundscomposite_fundcomposite_fundCreate a fundfinance.fundsInvolves creating the fund as well as potientially adding groupFundFY records for the current fiscal year
GET/finance/funds/<id>NAcomposite_fundGET fund by Idfinance.fundsNeeds to make multiple calls to gather not only the fund, but also the ids of the groups associated with this fund for the "current" fiscal year
GET/finance/fundsCQL Querycollection<fund>Search/List fundsfinance.fundsproxy back to storage - NOTE that this returns a collection of fund not composite_fund
PUT/finance/funds/<id>composite_fund201Update a fundfinance.fundsMay involve updating the fund and/or adding/removing groupFundFY records for the current fiscal year
DELETE/finance/funds/<id>NA204Delete a fundfinance.fundsRemove the fund and related groupFundFy records - may eventually become a soft delete for one or both record types.

NOTE:  see the section below about determining the appropriate ("current") fiscal year.

Storage Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance-storage/fundsfundfundCreate a fundfinance-storage.funds
GET/finance-storage/funds/<id>NAfundGET fund by Idfinance-storage.funds
GET/finance-storage/fundsCQL Querycollection<fund>Search/List fundsfinance-storage.funds
PUT/finance-storage/funds/<id>fund201Update a fundfinance-storage.funds
DELETE/finance-storage/funds/<id>NA204Delete a fundfinance-storage.funds

Determining the "current" fiscal year

A few places in the fund API require the module to figure out the appropriate fiscal year to use when associating groups/funds.  The following process should probably be implemented in a way that can be easily reused.

  • Given provided fund, get the associated ledger using fund.ledgerId
    • Query fiscal years:
      • series = ledger.fiscalYearOne.series
      • currentDate >= startPeriod
      • currentDate < endPeriod
  • If no fiscal year is returned for this period, look for next period
    • Query fiscal years:
      • series = ledger.fiscalYearOne.series
      • currentDate + 1 year >= startPeriod
      • currentDate + 1 year < endPeriod
  • If still nothing, return an error

Groups

  • Record with info that doesn't change from FY to FY
  • A Group/Fund/FY table will be needed to track fund/group relationships on a per FY basis.
  • Foreign keys:
    • group_fund_fiscal_years.budgetId = budgets.id (alias: budget)
    • group_fund_fiscal_years.fundId = funds.id (alias: fund)
    • group_fund_fiscal_years.fiscalYearId = fiscal_years.id (alias: fiscalYear)
    • group_fund_fiscal_years.groupId = groups.id (alias: group)
    • group_fund_fiscal_years.fundId → funds.ledgerId → ledgers.id (alias: ledger)

Schemas

group

PropertyTypeDefaultRequiredNotes
idstring
NUUID - System generated if not specified
namestring
YUnique
descriptionstring
N
codestring
YUnique
statusstring
Yenum: Active, Inactive, Frozen
acqUnitIdsarray<string>
NArray of UUIDs corresponding to the acquisition units assigned to this group.  
metadatametadata
NSystem generated metadata (createdBy/updatedBy/createdOn/etc.

groupFundFY

PropertyTypeDefaultRequiredNotes
idstring
NUUID - System generated if not specified
groupIdstring
YUUID of group
fiscalYearIdstring
N

UUID of fiscal year 

Not required in schema, but will always be present in storage - added by BL module

fundIdstring
YUUID of fund
budgetIdstring
N

UUID of the budget - seems redundant but needed for cross-index querying

allocated

number
Nnot persisted
unavailablenumber
Nnot persisted
availablenumber
Nnot persisted

Note:  Removing the summary fields as they're moved to the groupFySummary schema

groupFySummary

PropertyTypeDefaultRequiredNotes
groupIdstring
YUUID of group
fiscalYearIdstring
Y

UUID of fiscal year

allocated

number
Ynot persisted
unavailablenumber
Ynot persisted
availablenumber
Ynot persisted

Note:  No id field as this is only used between the UI and business logic module

APIs

Business Logic Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance/groupsgroupgroupCreate a groupfinance.groups
GET/finance/groups/<id>NAgroupGET groupby Idfinance.groups
GET/finance/groupsCQL Querycollection<group>Search/List groupsfinance.groups
PUT/finance/groups/<id>groupgroupUpdate a groupfinance.groups
DELETE/finance/groups/<id>NA204Delete a groupfinance.groups
GET/finance/groups/<id>/budgets

CQL Query,

Fiscal Year Query (req)

collection<budget>List budgets in this groupfinance.groups

Queries view API: Group + Budget + Fund/Group/FY (by groupId, fiscalYearId, group.acqUnits)

  • returns array of budget

Enforces acquisition units based on group's assignments, ignores budget/fund acq. units.

The fiscalYear query arg is the UUID of a fiscal-year record

POST/finance/group-fund-fiscal-yearsgroup_fund_fy204Create a groupFundFYfinance.group-fund-fiscal-yearsMay not be needed in light of the new /finance/fund API w/ composite_fund
GET/finance/group-fund-fiscal-yearsCQL Querycollection<group_fund_fy>Search/List groupFundFYfinance.group-fund-fiscal-yearsMay not be needed in light of the new /finance/fund API w/ composite_fund
DELETE/finance/group-fund-fiscal-years/<id>NA204Delete a groupFundFYfinance.group-fund-fiscal-yearsMay not be needed in light of the new /finance/fund API w/ composite_fund
GET/finance/group-fiscal-year-summariesCQL Querycollection<group_fy_summary>Get summary info for a group/fiscal year combination based on the provided queryfinance-group

Queries both of these APIs with the provided CQL

  • GET /finance-storage/budgets
  • GET /finance-storage/group-fund-fiscal-years

The first provides budget data including allocated/available/unavailable

The second provides fund/group associations.

Iterate of the results and calculate summary data on a per-group basis.

Return this as a group_fy_summary collection.

Does not support paging

NOTE:  removing GET /finance/groups/<id>/budgets.  This functionality will be provided by GET /finance/budgets?query=GroupFundFY.groupId=XYZ and fiscalYearId=ABC and group.

NOTE:  adding GET /finance/group-fiscal-year-summaries.  This replaces GET /finance/ledgers/<id>/groups and GET /finance/fiscal-years/<id>/groups.

Storage Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance-storage/groupsgroupgroupCreate a groupfinance-storage.groups
GET/finance-storage/groups/<id>NAgroupGET group by Idfinance-storage.groups
GET/finance-storage/groupsCQL Querycollection<group>Search/List groupsfinance-storage.groups
PUT/finance-storage/groups/<id>groupgroupUpdate a groupfinance-storage.groups
DELETE/finance-storage/groups/<id>NA204Delete a groupfinance-storage.groups
POST/finance-storage/group-fund-fiscal-yearsgroup_fund_fy204Create a groupFundFYfinance-storage.group-fund-fiscal-years
GET/finance-storage/group-fund-fiscal-years/<id>NAgroup_fund_fyGET groupFundFY by Idfinance-storage.group-fund-fiscal-yearsmay not be needed, but can't hurt
GET/finance-storage/group-fund-fiscal-yearsCQL Querycollection<group_fund_fy>Search/List groupFundFYfinance-storage.group-fund-fiscal-years
PUT/finance-storage/group-fund-fiscal-years/<id>group_fund_fygroup_fund_fyUpdate a groupFundFYfinance-storage.group-fund-fiscal-yearsmay not be needed, but can't hurt
DELETE/finance-storage/group-fund-fiscal-years/<id>NA204Delete a groupFundFYfinance-storage.group-fund-fiscal-years

Ledgers

  • Record with info that doesn't change from FY to FY
  • Ledger/FY table will be needed to track per FY information (e.g. summary #s)
  • Foreign Keys:
    • ledger_fiscal_years.ledgerId = ledgers.id 
    • ledger.fiscalYearOneId = fiscalYear.id

Schemas

ledger

PropertyTypeDefaultRequiredNotes
idstring
NUUID - system generated if not specified
namestring
YUnique
descriptionstring
N
codestring
YUnique
ledgerStatusstring
Yenum: Active, Inactive, Frozen
restrictEncumbrancesbooleantrueYwhether or not to bypass allowableEncumbrance restrictions against budgets associated with this ledger.  Typically used for some period of time after rollover, before allocations are made to the new budgets.
restrictExpendituresbooleantrueYwhether or not to bypass allowableExpenditures restrictions against budgets associated with this ledger.  Typically used for some period of time after rollover, before allocations are made to the new budgets.
fiscalYearOneIdstring
YUUID of the first fiscal year to associate with this ledger.

allocated

number
Nnot persisted
unavailablenumber
Nnot persisted
availablenumber
Nnot persisted
currencystring
Ncurrency code 
acqUnitIdsarray<string>
NArray of UUIDs corresponding to the acquisition units assigned to this ledger.
metadatametadata
NSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

ledgerFY

PropertyTypeDefaultRequiredUpdated On TransactionNotes
idstring
N

N

UUID - system generated if not specified
ledgerIdstring
YNUUID of ledger
fiscalYearIdstring
YNUUID of fiscal year

allocated

number
NY
unavailablenumber
NY
availablenumber
NY
currencystring
YNcurrency code for this fiscal year 

APIs

Business Logic Module

Method

Path

Request

Response

Description

Interface

Notes

POST/finance/ledgersledgerledgerCreate a ledgerfinance.ledgers
GET/finance/ledgers/<id>Fiscal Year Query (opt)ledgerGET ledger by Idfinance.ledgers

If FY specified:Queries GET /finance-storage/ledger-fiscal-year

  • Return Ledger w/ summary #s
  • Enforces acquisition units based on ledger's assignments, ignores budget's acq. units.

else

  • return ledger w/o summary #s

The fiscalYear query arg is the UUID of a fiscal-year record

GET/finance/ledgers

CQL Query

Fiscal Year Query (opt)

collection<ledger>Search/List ledgersfinance.ledgers

If FY specified:

  • Queries GET /finance-storage/ledger-fiscal-year
  • Return Ledgers w/ summary #s
  • Enforces acquisition units based on ledger's assignments, ignores budget's acq. units.

else

  • return ledger w/o summary #s

The fiscalYear query arg is the UUID of a fiscal-year record

PUT/finance/ledgers/<id>ledgerledgerUpdate a ledgerfinance.ledgers
DELETE/finance/ledgers/<id>NA204Delete a ledgerfinance.ledgers
GET/finance/ledgers/<id>/budgets

CQL Query,

Fiscal Year Query (req)

collection<budget>Get budgets for a ledgerfinance.ledgers

Queries GET /finance-storage/group-fund-fiscal-years (by fiscalYear, ledger, ledger.acqUnits)

Enforces acquisition units based on ledger's assignments, ignores budget's acq. units.

The fiscalYear query arg is the UUID of a fiscal-year record

GET/finance/ledgers/<id>/groups

CQL Query,

Fiscal Year Query (req)

collection<group_fy_summary>Get groups for a ledgerfinance.ledgers

Queries GET /finance-storage/budgets (by fiscalYearId, ledgerId, ledger.acqUnits)

Queries GET /finance-storage/group-fund-fiscal-years (by ledgerId, fiscalYearId, ledger.acqUnits)

  • pages all results or gets them all in one go
  • sums #s for each group
  • returns array of group_fund_fy

Enforces acquisition units based on ledger's assignments, ignores group's acq. units.

Does not support paging

The fiscalYear query arg is the UUID of a fiscal-year record 

GET/finance/ledgers/<id>/current-fiscal-yearNAfiscal_yearGet the current fiscal year for a given ledger record with <id>finance.ledgers

NOTE: Removing GET /finance/ledgers/<id>/budgets.  This functionality will be provided by GET /finance/budgets?query=fiscalYearId=ABC and ledger.id=DEF and ledger.acqUnits=\"XYZ\"

NOTE: Removing GET finance/ledger/<id>/groups.  This functionality will be provided by GET /finance/group-fiscal-year-summaries?query=fiscalYearId=ABC and ledger.Id=DEF and ledger.acqUnits=\"XYZ\"

Storage Module

Method

Path

Request

Response

Description

Interface

Notes

POST/finance-storage/ledgersledgerledgerCreate a ledgerfinance-storage.ledgers
GET/finance-storage/ledgers/<id>NAledgerGET ledgerby Idfinance-storage.ledgers
GET/finance-storage/ledgersCQL Querycollection<ledger>Search/List ledgersfinance-storage.ledgers
PUT/finance-storage/ledgers/<id>ledger201Update a ledgerfinance-storage.ledgers
DELETE/finance-storage/ledgers/<id>NA204Delete a ledgerfinance-storage.ledgers
GET/finance-storage/ledger-fiscal-yearsCQL Querycollection<ledgerFY>Search/List LedgerFYsfinance-storage.ledgers

Updating the LedgerFY table

As you can see above, there is not API for directly manipulating the LedgerFY records.  These records are automatically updated by the storage module when related changes are made.

  • When a Ledger is created,
    • create a LedgerFY record for the FY identified by ledger.fiscalYearOneId
    • create a fiscalYear for the next period in the fiscalYear series (TODO: more details)
    • create a LedgerFY record for the FY just created
  • When a FiscalYear is created, create a new LedgerFY record for each existing Ledger.(only if fiscalYear endDate > now)  Handle this during rollover...

The approach we're taking will result in LedgerFY records being creating which may never be used - this is especially true in cases where there are simultaneous or overlapping fiscal years, e.g. Texas Fiscal Year (TFY19) and Qatar Fisal Year (QFY19) might both have similar or overlapping periods, but apply to two separate campuses, each with their own finances.  A given ledger would only really apply to one or the other, but LedgerFY records would be created for both.  We need to do this to support rollover.  The LedgerFY records themselves are quite small, so it shouldn't be too much of a problem.

For now we don't have to worry about managing LedgerFY records upon update or delete of a ledger or FiscalYear.  The plan is to implement a soft delete on both so reference integrity should be maintained without intervention.

The LegerFY records should be created in the storage module in database transactions (which also include the creation of the Ledger/FiscalYear record which caused the LedgerFY creation), possibly via trigger functions.

NOTE:  The idea of adding a "fiscal year" series which relates fiscal years together e.g. "Texas Fiscal Year" → (TFY19, TFY20, TFY21), and "Qatar Fiscal Year" → (QFY19, QFY20, QFY21) has been discussed and would help with both rollover and would also reduce the number of unnecessary/unused LedgerFY records being created.  This is still in the idea phase and has not yet been fleshed out.  If we choose to go this route, adding it later shouldn't be too disruptive.

Budgets

  • Essentially a Fund/FY object
  • Foreign Keys:
    • Budget.fiscalYearId = FiscalYear.id
    • Budget.fundId = Fund.id
    • Budget.ledgerId = Ledger.id
  • View:
    • Joins budgets + group_fund_fiscal_years + groups
    • Needed to query budgets by group criteria, e.g. group.acqUnitIds
    • queried by GET /finance-storage/group-budgets
    • Hopefully this is only a temporary solution that can be removed once RMB supports bi-directional cross-index sub-queries, e.g. budget ← group_fund_fiscal_years → group

Schemas

budget

PropertyTypeDefaultRequiredUpdated On TransactionNotes
idstring
NNUUID - system generated if not specified
namestring
YN= <fund.code>-<fiscal_year.code>, eg. AFRICAHIST-FY19.  Unique
budgetStatusstring
YNenum: Active, Frozen, Planned, Closed
allowableEncumbrancenumber
NNpercentage
allowableExpenditurenumber
NNpercentage
allocatednumber0YYRunning total of all the allocation transactions against the budget.
awaitingPaymentnumber
NY (encumbrance only)Running total of all the invoiced amounts (i.e. waiting to be paid)
availablenumber0NY

= (allocation + <transfers>) - awaiting_payment - encumbered - expenditures

NOTE: Doesn't include allowableEncumbered amount

encumberednumber
NY (encumbrance only)Running total of money set aside for purchases
expendituresnumber
NY (encumbrance only)Running total of all the payments recorded by the fund
unavailablenumber
NY

= awaiting_payment + encumbered + expenditures

NOTE: Doesn't include overEncumbered amount

overEncumbrancenumber
NY (encumbrance only)Amount the budget is over encumbered.  i.e. overEncumbered = max(0, encumbered - available - unavailable)
overExpendednumber
NY (encumbrance only)Amount the budget is over expended. i.e. overExpended = max(0, expended - available - unavailable)
fundIdstring
YNUUID of fund
fiscalYearIdstring
YNUUID of fiscal year
ledgerIdstring
YNUUID of ledger
acquisitionUnitsarray<string>
NNArray of UUIDs corresponding to the acquisition units assigned to this budget.
tagstags
NNinherited from fund
metadatametadata
NYSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

APIs

Business Logic Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance/budgetsbudgetbudgetCreate a budgetfinance.budgets
GET/finance/budgets/<id>NAbudgetGET budget by Idfinance.budgets
GET/finance/budgetsCQL Querycollection<budget>Search/List budgetsfinance.budgets

Special handling here... inspect query - if contains criteria in groups, e.g. "group.*" use the "group-budgets API, otherwise use the budgets API.  

While not ideal this saves us from having to create and later deprecate/remove (a breaking change) a separate API for this.  Instead we can make a non-breaking change (just remove the logic - the client is unaware anything changed)

PUT/finance/budgets/<id>budgetbudgetUpdate a budgetfinance.budgets
DELETE/finance/budgets/<id>NA204Delete a budgetfinance.budgets

Storage Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance-storage/budgetsbudgetbudgetCreate a budgetfinance-storage.budgets
GET/finance-storage/budgets/<id>NAbudgetGET budget by Idfinance-storage.budgets
GET/finance-storage/budgetsCQL Querycollection<budget>Search/List budgetsfinance-storage.budgetsneed to be able to query by fields from budget/ledger/fiscalYear/groupFundFy
PUT/finance-storage/budgets/<id>budgetbudgetUpdate a budgetfinance-storage.budgets
DELETE/finance-storage/budgets/<id>NA204Delete a budgetfinance-storage.budgets
GET/finance-storage/group-budgetsCQL Querycollection<budget>Search/List budgets by group criteriafinance-storage.budgetsqueries a view joining budgets, group_fund_fiscal_years, groups

Fiscal Years

Schemas

fiscal_year

PropertyTypeDefaultRequiredNotes
idstring
NUUID - system generated if not specified
namestring
Ye.g. Texas Fiscal Year
codestring
YUnique e.g FY19
descriptionstring
N
seriesstring
NThe alphabetical part of the code - set by mod-finance - used to group/relate fiscal years together.  E.g. codes=FY19, FY20, FY21 all have series=FY
periodStartdate
Y
periodEnddate
Y
currencystring
NCurrency code used (recorded when the FY ends, otherwise the system currency can be assumed)
acqUnitIdsarray<string>
NArray of UUIDs corresponding to the acquisition units assigned to this fiscal year.
metadatametadata
NSystem generated metadata (createdBy/updatedBy/createdOn/etc.)

APIs

Business Logic Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance/fiscal-yearsfiscal_yearfiscal_yearCreate a fiscalYearfinance.fiscal-yearsMay result in creation of other records
GET/finance/fiscal-years/<id>NAfiscal_yearGet fiscalYear by Idfinance.fiscal-years
GET/finance/fiscal-yearsCQL Querycollection<fiscal_year>Search/List fiscalYearsfinance.fiscal-years
PUT/finance/fiscal-years/<id>fiscal_yearfiscal_yearUpdate a fiscalYearfinance.fiscal-years
DELETE/finance/fiscal-years/<id>NA204Delete a fiscalYearfinance.fiscal-years
GET/finance/fiscal-years/<id>/ledgersCQL Querycollection<ledgerFY>Get ledgers for a fiscalYearfinance.fiscal-years

Queries GET /finance-storage/ledger-fiscal-year (on fiscalYear.id and fiscalYear.acqUnits)

Enforces acquisition units based on fiscalYear's assignments, ignores ledger's acq. units.

GET/finance/fiscal-years/<id>/groupsCQL Querycollection<group_fy_summary>Get groups for a fiscalYearfinance.fiscal-years

Queries GET /finance-storage/budgets (on fiscalYear.id and fiscalYear.acqUnits)

Queries GET /finance-storage/group-fund-fiscal-years (on fiscalYear.id and fiscalYear.acqUnits)

Iterates over results and calculates summary #s for each group

Enforces acquisition units based on fiscalYear's assignments, ignores group's acq. units.

Does not support paging

GET/finance/fiscal-years/<id>/budgetsCQL Querycollection<budget>Get budgets for a fiscalYearfinance.fiscal-years

Queries GET /finance-storage/ledger-budgets (on budget.fiscalYearId and fiscalYear.acqUnits)

Enforces acquisition units based on fiscalYear's assignments, ignores budget's acq. units.

NOTE:  GET /finance/fiscal-years/<id>/ledgers has been removed.  This functionality is covered by GET /finance/ledgers?query=ledgerFY.fiscalYearId=ABC

NOTE:  GET /finance/fiscal-years/<id>/budgets has been removed.  This functionality is covered by GET /finance/budgets?query=fiscalYearId=ABC

NOTE: GET finance/ledger/<id>/groups has been removed.  This functionality will be provided by GET /finance/group-fiscal-year-summaries?query=fiscalYearId=ABC and fiscalYear.acqUnits=\"XYZ\"

Storage Module

MethodPathRequestResponseDescriptionInterfaceNotes
POST/finance-storage/fiscal-yearsfiscal_yearfiscal_yearCreate a fiscalYearfinance-storage.fiscal-years
GET/finance-storage/fiscal-years/<id>NAfiscal_yearGET fiscalYear by Idfinance-storage.fiscal-years
GET/finance-storage/fiscal-yearsCQL Querycollection<fiscal_year>Search/List fiscalYearsfinance-storage.fiscal-years
PUT/finance-storage/fiscal-years/<id>fiscal_yearfiscal_yearUpdate a fiscalYearfinance-storage.fiscal-years
DELETE/finance-storage/fiscal-years/<id>NA204Delete a fiscalYearfinance-storage.fiscal-years

Database Views

Several views are needed to accommodate some of the inter-record queries.  These have been mentioned throughout the document.  This section pulls that info into a single place and adds additional details (e.g. related API definitions)

N.B. This was written prior to  RMB-395 - Getting issue details... STATUS .  These views are no longer needed in light of this new cross-index subquery functionality.  I'm leaving this section as-is in case this approach does not prove adequate for our needs.

 Collapsed to avoid confusion

LedgerFY

  • join: ledgerFY + ledger + fiscalYear
  • jsonb:  ledger_fy
  • NOTE: we need fiscalYear here because when calling this from GET /finance/fiscal-years/<id>/ledgers we need to query against the fiscalYear's acquisition units.

API

MethodPathRequestResponseDescriptionInterfaceNotes
GET/finance-storage/ledger-fiscal-yearsCQL Querycollection<ledger_fy>Search/list ledger/FYfinance-storage.ledgersNeeded for Fiscal Year details view, ledger accordion

GroupFundFY

  • joins: Fund + Budget + Fund/Group/FY + Ledger + FIscalYear
    • fund.id == FundGroupFY.fundId.
    • budget.fiscalYearId == FundGroupFY.fiscalYearId AND budget.fundId = FundGroupFY.fundId
    • ledger.id == fund.ledgerId
    • fiscalYear.id == budget.fiscalYearId
  • jsonb: group_fund_fy
  • NOTE: we need ledger here because when calling this from GET /finance/ledgers/<id>/groups we need to query against the ledger's acquisition units.
  • NOTE: we need fiscalYear here because when calling this from GET /finance/fiscal-years/<id>/groups we need to query against the FY's acquisition units.

API

MethodPathRequestResponseDescriptionInterfaceNotes
GET/finance-storage/group-fund-fiscal-yearsCQL Querycollection<group_fund_fy>Query group/fund/FY viewfinance-storage.groupsNeeded for Ledger and Fiscal Year details views, groups accordion

LedgerBudget

  • joins: Fund + Budget + Ledger + FiscalYear
  • jsonb: budget
  • NOTE: we need ledger here because when calling this from GET /finance/ledgers/<id>/budgets we need to query against the ledger's acquisition units.
  • NOTE: we need fiscalYear here because when calling this from GET /finance/fiscal-years/<id>/budgets we need to query against the fiscalYear's acquisition units.

API

NOTE:  We decided that having a separate API duplicates functionality provided by the GET budgets by query endpoint.  Unless we discover a reason why the subquery support in RMB won't for for this, there's no need for a separate API.

MethodPathRequestResponseDescriptionInterfaceNotes
GET/finance-storage/ledger-budgetsCQL Querycollection<budget>Query ledger/budget viewfinance-storage.budgetsNeeded for Ledger and Fiscal Year details views, funds accordion

End-to-End Flow

  1. Setup - Create and link financial structures
    • Fiscal Year(s) are created
      • a ledgerFY with new FY is created for each ledger??  Only if not in the past?
      • a budget with new FY is created for each fund??  Only if not in the past?
      • a groupFundFY with new FY is created for each fund/group pair??  Only if not in the past?
    • Ledgers are created
      • a ledgerFY is created for each FY??  Only if not in the past?
    • Funds are created
    • Budgets are created
    • Groups are created
    • Funds are added to groups
      • a groupFundFY is created for each fund/group/FY tuple??  Only if FY not in the past?
  2. Money is allocated to budgets
    • Allocations (transactions) are created
    • Budgets are updated
      • budget.allocated increases
      • budget.available/unavailable are re-calculated
    • Ledgers are updated
      • ledger.allocated/available/unavailable are re-calculated
  3. An order is created and opened
    • Encumbrances are created for each of the POL's fund distributions
      • Need to check if the budget.allocated >= amount being encumbered
      • Need to check if the budget.allowableEncumbered >= (budget.encumbered + amount being encumbered)
    • Budgets are updated
      • budget.encumbered increases
      • budget.available/unavailable are re-calculated
    • Ledgers are updated
      • ledger.allocated/available/unavailable are re-calculated
  4. An invoice is created and approved
    • Voucher is generated
    • Encumbrances are updated
      • transaction.encumbrance.amountAwaitingPayment increases
      • transaction.amount (effective encumbered amount is recalculated
    • Budgets are updated
      • budget.encumbered → budget.awaitingPayment
      • budget.available/unavailable are re-calculated
    • Ledgers are updated
      • ledger.allocated/available/unavailable are re-calculated
  5. Invoice is paid
    • Voucher is marked as paid
    • Payments (transactions) are created
    • Encumbrances are updated
      • transaction.encumbrance.amountAwaitingPayment → transaction.encumbrance.amountExpended
      • transaction.amount (effective encumbered amount is recalculated
    • Budgets are updated
      • budget.awaitingPayment → budget.expenditures
      • budget.available/unavailable are re-calculated
    • Ledgers are updated
      • ledger.allocated/available/unavailable are re-calculated
    • Orders/POLs are updated
      • purchaseOrder.paymentStatus = partially/fullyPaid
      • poLine.paymentStatus = partially/fullyPaid
    • For each order, If fully paid
      • repeat 4, 5 as needed
    • Else 
      • Encumbrances are released
        • transaction.amount (effective encumbered amount is recalculated (becomes 0)
      • Budgets are updated
        • budget.encumbered decreases
        • budget.available/unavailable are re-calculated
      • Ledgers are updated
        • ledger.allocated/available/unavailable are re-calculated
      • Order is closed
  6. Fiscal Year ends/starts
    1. TBD

JIRA

Convenient place to put links to features/stories in JIRA

  • TBD

Mockups

https://drive.google.com/drive/folders/1Vc401TCsooCeUWNwBQForjYpADE7CMW8

Open Issues/Other Considerations

  • The set of columns to show in search results has not been reviewed by the small group yet.  That could potentially affect this if there are changes.
  • How to express allocateTo/allocateFrom "all" funds?  none?  groups?
    • It was discussed that we might at some point add separate fields for specifying groups of funds that a fund can allocatedTo/From.  At the moment this is the best idea we've come up with.  One potential downside is that it might encourage proliferation of groups which will have a negative impact on performance in the current design due to needing to perform group calculations on the fly.
  • How to capture changes in currency?  Historically?  
    • No need to track the currency code in each fund, but when the fiscalYear ends, we should capture the system currency in use at the time.
    • Displaying search results with different currencies.... e.g. FY18 is in USD, FY19 is in CAD - Is this possible?  Something like:
      • Discussed with the FE guys, and this shouldn't be a problem. 

Appendix

Preventing Partially Paid Invoices

When an invoice is paid, numerous transactions (payments, credits) will be generated.  Either all or none of these need to be successfully processed; we can't have partially paid invoices.  This presents a challenge of scale.  There could feasibly be hundreds of payments for an invoice.

(error) Option A:  Allow multiple payments to be sent at once to the payment API.

The idea here is simple... update the POST payments API to accept a collection of payments/credits and process them all at once.

Pros/Cons

  • The request payload could be quite large
  • Processing all the payments in a single call might take a prohibitively long time

(tick) Option B:  Collect/Aggregate payments/credits in the storage layer until all are received, then process together

This approach keeps the payment "simple" and consistent with the other transaction APIs.  The storage layer performs some aggregation in the database of payments/credits based on the sourceInvoiceId specified in each payment.  The flow looks something like this:

Introduce a new type of record... let's just call it a "saga" for now - what's proposed here isn't strictly a saga, but it's similar in ways.  Maybe a "paymentManifest" or something is a better name;  anyway - its not important right now.

Saga:

  • invoiceId (UUID of the invoice these payments/credits are related to)
  • numTransactions (int - total number of payments/credits)A payment or credit is posted

Flow:

  • Using the sourceInvoiceId, get the invoiceLines and sum up the total number of fundDistributions - this is numTransactions.
  • Get the saga using payments sourceInvoiceId.  If one does exist, create one.
  • Update the saga record and persist the payment record in a temporary table - do all this in a database transaction
  • If this is the last payment in the saga, (the number of entries in the temp table == numTransactions) - NOTE: we'll probably want to use a unique_index on the id field or something to ensure the same payment isn't being counted more than once.
    • Persist the payments/credits from the temporary table and perform the summary # updates (e.g. update budget, ledger, encumbrances, etc. - unavailable/available/encumbered/expended/etc.) - do this in a database transaction
      • If everything works, cleanup the temporary table and return 200 OK
      • If something fails, no actual records are updated.  The payment API call can fail and can be retried (POST payment/credit are idempotent)

NOTE:  we might be able to move some of this logic to the BL module.  The trade-off there is that the storage module would contain less logic, but would have a more complicated API.

Pros/Cons:

  • Might be able to do some incremental processing instead of having to do it all at once
  • Multiple single payments can be sent - avoids the massive request payload
  • Individual payments/credits can be repeated if needed (the POST payment API is idempotent)

Other Ideas:

  • Option B w/ optional batching... it may be convenient to allow batching (send multiple payments/credits at once); would help cut down on number of API calls needed to pay an invoice.  
    • One call per invoiceLine vs one call per fund distribution
  • TBD

Required Queries (WIP)

View Budget Transactions

In order to support searching for transactions for a given budget:  https://drive.google.com/open?id=1w-kNrURaAQ0cjiHl0NzADaLnackJN0mT

  1. GET transactions by Budget, search/filter on:
    1. transactionType
    2. source
    3. fromFundId
    4. toFundId
    5. status
    6. tags
    7. date (range)
    8. fiscalYearId
    9. description
    10. amount

View Budget Details

In order to support Budget details view: https://drive.google.com/open?id=1ezTFSS6Rypv_wg8eVBoMbsLRoci-2UR-

  1. GET Budget by Id - including allocated/avail/unavail summary
  2. GET FY by Id

View Group Details

In order to support Group details view: https://drive.google.com/open?id=1qxEIiISiNX6wSTQUEbTwfcx-fwL8xwbv

  1. GET Group by Id - including allocated/avail/unavail summary
  2. GET Funds by Group/FY - including allocated/avail/unavail 

View Fund Details

In order to support Fund details view:  https://drive.google.com/open?id=1ksgVbdEc426JCMkUDuey_GlB-M9c3to_

  1. GET Fund by Id
  2. GET Budget by FY (current FY - Equals, previous FYs - LessThan, next FY - GreaterThan)

View Ledger Details

In order to support Ledger details view:  https://drive.google.com/open?id=1MMQdlKU1-OFlpCDCClHQMvTi-xPTf2K

  1. GET funds for ledger/FY - including allocated/avail/unavail
  2. GET groups for ledger/FY - including allocated/avail/unavail
  3. GET FYs for a ledger
  4. GET funds for a group
  5. GET ledger for FY - including allocated/avail/unavail summary



  • No labels
Write a comment…