Request for feedback on Budget Feature implementation in OpenLMIS

Hello All,

In the Tanzania OpenLMIS upgrade, there is a requirement to include a Facility Budget feature in OpenLMIS, more details can be found here confluence-page

I’m requesting feedback to confirm if this is an OpenLMIS feature wanted, for incorporating into OpenLMIS core.

I have included the attached schema diagrams for the reference

Thank you

Regards,

Hassan

Hi @Hussein_Hassan,

Thanks for this.

A couple comments on conventions and the structure for you:

  1. All of the id, code, and by fields look like they’re for ids, in which case they should be UUID and end in id. See our postgres conventions.
    • fundsourceid should likely be sourceoffundid for easiest mapping in JPA if I remember right.
  2. What’s a budget_line_items.creditvalue? It looks like it’d be a numeric value, but then it’s a text field?
  3. Use text instead of things like varchar(50) in postgres.
  4. Why does source of funds not have audit fields for user but the other two do?
  5. Why does the line item field have audit fields for datetimes and users? Wouldn’t they be the same as in the budget?

A couple deeper questions:

  1. What does the additive column do?
  2. How does a budget apply to a facility? i.e. what does it budget for and how does it function.
    1. Does a budget for a facility vary by program?
    2. Does a budget for a facility vary over time?
    3. What differentiates one budget line item from another? Is it the source of fund? Or how it applies?
  3. How are sources of funds managed?

As you know the #product-committee is the place to discuss if the functionality is desired (so far it sounds like yes it is) in the default product. Here in the #technical-committee we will assume that we’re evaluating the design as if it should be in the default product offering.

Do we have the user stories yet for how the budget will be used? Those seem to be missing (or I’m missing them) at this moment. It’d help in evaluating the design further.

Hi @joshzamor and OpenLMIS technical forum…

Please see my response below with regards to the feedback provided on the question about adding Budget feature in openLMIS core.

Proposed budget tables can be found here https://rextester.com/XIFK47878

  1. All of the id, code, and by fields look like they’re for ids, in which case they should be UUID and end in id. See our postgres conventions 1.

Yes, updated the script to use UUID

#. fundsourceid should likely be sourceoffundid for easiest mapping in JPA if I remember right.

Yes, changed the field name and updated the script

  1. What’s a budget_line_items.creditvalue? It looks like it’d be a numeric value, but then it’s a text field?

I’ve redesigned the table and columns now to make them more streamlined. See the attached schema script

  1. Use text instead of things like varchar(50) in postgres.

Yes, updated the script to use text instead of varchar

  1. Why does source of funds not have audit fields for user but the other two do?

Updated script now uses audit fields

  1. Why does the line item field have audit fields for datetimes and users? Wouldn’t they be the same as in the budget?

See the updated script

A couple deeper questions:

What does the additive column do?
See the updated script

How does a budget apply to a facility? i.e. what does it budget for and how does it function.
Does a budget for a facility vary by program?

Yes varies by program. See the updated script

Does a budget for a facility vary over time?

Yes varies over time. However, the source application (e.g., Epicor/FFARS) maintains the accounting for budget and use of the budget. eLMIS always gets the latest available balance for the current period through API. As such eLMIS avoids the need to maintain debit/credits of balance-related transactions and always gets the current available budget balance and displays that on requisition form.

What differentiates one budget line item from another? Is it the source of funds? Or how it applies?

Yes, sources of fund and Facility

How are sources of funds managed?

eLMIS maintains the sources of fund as reference data initially coming from the source applications. If new sources of fund types are found on the API feed, a new record may needs to be created on that table. This is assumed to be a manual function since name, code and display order fields needs manual confirmation.

Bellow is a proposed budget tables for the reference.


– Name: budget_line_items; Type: TABLE; Schema: requisition; Owner: postgres; Tablespace:
– Purpose: To store current budget for specific facility and program, by sources of fund.
– This table is populated through API calls to source applications that maintains the budget
– eLMIS does not maintain budget transactions. It always get the latest available balance from source application

CREATE TABLE budget_line_items (
id uuid NOT NULL,
facilityid uuid NOT NULL,
programid uuid NOT NULL,
sourceoffundid uuid NOT NULL,
lastupdateddate timestamp with time zone,
allocatedbudget numeric(19,2) NOT NULL,
note TEXT,
createddate timestamp with time zone,
modifieddate timestamp with time zone
);


– Name: requisition_source_of_funds; Type: TABLE; Schema: requisition; Owner: postgres; Tablespace:
– Purpose: To store budget amount that was used to pay for the requisition.
– There can be multiple amounts for a specific requisition differentiated by source of fund

CREATE TABLE requisition_source_of_funds (
id uuid NOT NULL,
requisitionid uuid NOT NULL,
sourceoffundid uuid NOT NULL,
budgetamout numeric(19,2) NOT NULL,
createddate timestamp with time zone,
modifieddate timestamp with time zone
);


– Name: source_of_funds; Type: TABLE; Schema: requisition; Owner: postgres; Tablespace:
– Purpose: To store budget amount that was used to pay for the requisition.
– There can be multiple amounts for a specific requisition differentiated by source of fund

CREATE TABLE source_of_funds (
id uuid NOT NULL,
code TEXT NOT NULL UNIQUE,
name TEXT NOT NULL UNIQUE,
displayorder integer,
createddate timestamp with time zone,
modifieddate timestamp with time zone
);


– Name: budget_line_items_pkey; Type: CONSTRAINT; Schema: requisition; Owner: postgres; Tablespace:

ALTER TABLE ONLY budget_line_items
ADD CONSTRAINT budget_line_items_pkey PRIMARY KEY (id);


– Name: requisition_source_of_funds_pkey; Type: CONSTRAINT; Schema: requisition; Owner: postgres; Tablespace:

ALTER TABLE ONLY requisition_source_of_funds
ADD CONSTRAINT requisition_source_of_funds_pkey PRIMARY KEY (id);


– Name: source_of_funds_pkey; Type: CONSTRAINT; Schema: requisition; Owner: postgres; Tablespace:

ALTER TABLE ONLY source_of_funds
ADD CONSTRAINT source_of_funds_pkey PRIMARY KEY (id);


– Purpose

ALTER TABLE budget_line_items
ADD CONSTRAINT fk_budget_line_items_facilities FOREIGN KEY (facilityid) REFERENCES facilities(id);

ALTER TABLE budget_line_items
ADD CONSTRAINT fk_budget_line_items_programs FOREIGN KEY (programid) REFERENCES programs(id);

ALTER TABLE budget_line_items
ADD CONSTRAINT fk_budget_line_items_source_of_funds FOREIGN KEY (sourceoffundid) REFERENCES source_of_funds(id);

ALTER TABLE requisition_source_of_funds
ADD CONSTRAINT fk_requisition_source_of_funds_requisitions FOREIGN KEY (requisitionid) REFERENCES requisitions(id);

ALTER TABLE requisition_source_of_funds
ADD CONSTRAINT fk_requisition_source_of_funds_source_of_funds FOREIGN KEY (sourceoffundid) REFERENCES source_of_funds(id);

Thanks @Hussein_Hassan,

Thanks for cleaning up the convention issues. These changes are pretty significant though, so I have some all new questions/concerns:

  • Having a document line item table (budget_line_items) without a corresponding document table doesn’t really fit the convention that requisition service has followed. That is you had the document table, budgets, but now it’s either gone or it’s source_of_funds? Either the document table should be added back, or source_of_funds is the document and budget_line_items should be renamed to source_of_funds_line_items.
  • The requisition_source_of_funds (formerly budgets) is linked 1:1 with a Requisition? What about emergency Requisitions? What happens if the budget file arrives before the Requisition is created? Will the ERP or other external system always provide a budget file per Requisition? I don’t understand this change. Perhaps I don’t understand because the purpose for this table is written the same as the source_of_funds table? What’s wrong here?
  • Why does requisition_source_of_funds have a budget number for a Requisition, and budget_line_items have a budget for a Facility + Program? Does the latter inform the creation of the former?

Where does this happen in the table structure? Does the API create a new entry in sources_of_funds, but that entry isn’t valid until someone comes along later and gives the entry a code, name and display order? Which of those fields is the unique id for this entity coming through the API (e.g. if the ERP updates that source of fund - or does that not happen?)

Thanks @Hussein_Hassan, I think if you reset and focus on a functional description of each table, and how it’s supposed to be used, it’ll be clearer.