[12.0][ADD] budget_management (PO/EX budget commitment, easy budget planing by KPI/Analytic, warning if not enough budget)

[kittiu]

These module is built on top of already great budgeting capability of mis_buider_budget, but add more features to

1. Tighten the control during document transaction
2. Simplify process for an organization to setup and make use of budget control add reporting especially in the context of cost/profit center (based on analytic_account)

Remarks: this is based on my experiences doing budget management with an organization, but for this I wanted to make it more lightweight and generic enough for most use. Feedback are greatly appreciated.

Design Goals

1. Budget Commitment: PR/PO/EX and SO to have accounting budget commitment. I.e., PR-PO-INV, budget will commit when document is confirmed, and reversed commit when passed to next document.
2. Budget Control: Ability to lock document when the commit/actual of any document will result in budget balance < 0.0
3. Budget Chartfield: Chartfield means, Costcenter/Project, which is analytic_account in Odoo. We want extend 1 and 2 to allowing operate at Costcenter/Project level.
4. Budget Planning: As a new budget planning document, for a user in a costcenter to easily fill in a fiscal year budget for his/her business unit. And once confirmed, system to auto create mis report instances as we know about in order to control budget as refer to 1, 2, 3.

Goal 1: Budget Commitment

This one can be quite easy, by adding a new table to log all the commitment, i.e., budget.commit. I am looking into create followings addons one for each type of document.

• purchase_budget_commit [12.0][ADD] purchase_budget_commit #29
• expense_budget_commit [12.0][ADD] hr_expense_budget_commit #31
• purchase_request_budget_commit

Goal 2: Budget Control

This part will deal with calling available mis_builder's API to find out available budget, and give warning.

• invoice_budget_control -- I.e., allow user to set to control on invoice, and lock budget on validate invoice.
• purchase_budget_control
• expense_budget_control
• purchase_request_budget_control

Goal 3: Budgeting with more Analytic Dimensions: (TBD)

N/A

Goal 4: Budget Planning and Budget Revision: (TBD)

For big organization, there will be preparation process, when organization discuss for next year budgeting. Normally it will occur, i.e, 2-3 months ahead of the budget year. Expected procedure can be,

• Budget committee call for new budget year (budget.management for new year), and setup budget target (for each operating unit)
• Each unit, by analytic account (unit / project), create new budget.plan for given budget.management.
• Approval process, to ensure budget allocated properly to all units/projects also withing budget target.
• After approved, budget.plan is converted into budget.control, and system can use it to control budget

[kittiu]

@sbidoul the discussion moved here, the module can be here or in https://github.com/OCA/mis-builder-contrib ?

[sbidoul]

@kittiu I like the idea of logging commitments explicitly. This allows for good traceability and handling of corner cases.

Regarding the repository choice it does not matter much to me. What matter more for me is knowing who is stepping in to care of the maintenance of modules.

[kittiu]

@kittiu I like the idea of logging commitments explicitly. This allows for good traceability and handling of corner cases.

Thank you, please come to comment. At this moment, I will try to make use of mis_builder as much. :) would need your support on mis_builder knowledge.

Regarding the repository choice it does not matter much to me. What matter more for me is knowing who is stepping in to care of the maintenance of modules.

I am not sure what you mean by this. We do have PSC/maintainer for this repo, right?

[kittiu]

@sbidoul from the evaluate(), I could get kpi's figure form account.move.line, i..e, exp by time period.

How can I get figure from Budget, Commit and Available?

company = self.env.ref('base.main_company')
report = self.env.ref('mis_builder_demo.mis_report_expenses')
aep = report._prepare_aep(company)
r = report.evaluate(
    aep,
    date_from='2014-01-01',
    date_to='2020-12-31',
)
print(r)

{
    'sum': <function _sum at 0x7f743688e0d0>,
    'min': <function _min at 0x7f7436896a60>,
    'max': <function _max at 0x7f7436896ae8>,
    'len': <built-in function len>,
    'avg': <function _avg at 0x7f74368969d8>,
    'time': <module 'time' (built-in)>,
    'datetime': <module 'datetime' from '/usr/lib/python3.6/datetime.py'>,
    'dateutil': <module 'dateutil' from '/home/kittiu/.virtualenvs/odoo12/lib/python3.6/site-packages/dateutil/__init__.py'>,
    'AccountingNone': AccountingNone,
    'SimpleArray': <class 'odoo.addons.mis_builder.models.simple_array.SimpleArray_1'>,
    'date_from': datetime.date(2014, 1, 1),
    'date_to': datetime.date(2020, 12, 31),
    'exp': 31591.5,
    'equip': AccountingNone,
    'other': AccountingNone,
    'total': 31591.5
}

[kittiu]

Got this, def _add_column_actuals_alt()

[sbidoul]

Hm, indeed evaluate is the simplified api. I need to dig a little bit to give you an example. No time today, though.

[kittiu]

Thank you, no worry if you are busy. :)
I am getting the better result for commitment by adding aml_model and target_move=None.

        r = report.evaluate(
            aep,
            date_from='2014-01-01',
            date_to='2020-12-31',
            aml_model='purchase.budget.commit',
            target_move=None,
        )

[kittiu]

@sbidoul
As I try to dig into the code, a little more progress, it is quite complex for me.
Am I correct that,

• evaluate() is only used to get account.move.line and alternate move.line values
• For budget item, we may need to use self.env['mis.budget.item']._query_kpi_data() to get the amount.

To get the available amount (sum column) we can get from combine the two.

[sbidoul]

@kittiu I'll try to answer your questions.

We do have PSC/maintainer for this repo, right?

This repo is part of the Accounting PSC which covers a huge amount of modules.
For such a vast domain one cannot really expect to have the PSC members to do active maintenance on everything. So it's better if some people declare themselves has maintainer on addons they care about and do more active maintenance work. That's the purpose of the maintainer role and the maintainers key in the manifest.

[sbidoul]

Regarding computation, I indeed sent you on the wrong track with the evaluate method, which is too limited for what you want to do.

A better starting point is the compute() method of mis.report.instance, which prepares the data for preview, or the lower level ._compute_matrix() method which gives you a more detailed data structure.

So the geneal algorithm goes as follow.

1. Find the well known mis.report.instance that has the budget calculation, and in that one, find the well known column (mis.report.instance.period) that has the available values => lets name it's id period_available_id.
2. Find the KPI for which you want the available budget, using get_kpis_by_account_id(), and find the one that has the budgetable flag set. There should be only one, otherwise there is an ambiguity in your budget. Lets call it budgetable_kpi
3. Use _compute_matrix() (possibly with a context containing analytic filters) and navigate the matrix to find the value you need with something like this:

        for row in kpi_matrix.iter_rows():
            if row.kpi == budgetable_kpi:
                for cell in row.iter_cells():
                    if cell.subcol.col.key == period_available.id:
                        return cell.val or 0.0

So yeah, it's a bit complex. OTOH the subject matter is not an easy one either :)

I'm open to discuss a higher level API that would make these kind of calculations easier.

[kittiu]

@sbidoul Thank you so much for taking your valuable time to explain. This helps a lot!
Got it about maintainers. I will do that.

[kittiu]

@sbidoul
So far, your recommendation fit into what I try to achieve quite nicely, thanks to mis_builder.

As the check_budget() will be called quite often, i.e., every time a document is validate. I have one concern on _compute_matrix(), as it return the whole result set, which we only need only result of some kpi and some period. I worry about performance

https://github.com/ecosoft-odoo/account-budgeting/blob/12.0-add-purchase_budget_commit/budget_management/models/budget_management.py#L137-L144

Do you have answer to this yet (i..e, filters)?

Thank you!

Note: I have some good progress, and soon can push the first demo.

[kittiu]

Closed as moved to PR #32