Projects:Persist Organization Information
Contents |
Background
There are some places (like accounting process) that do call to below pl functions:
- ad_org_getperiodcontrolallow
- ad_org_getcalendarowner
Each call does not take a lot of time to be executed, but calling them several times in some places creates performance problems.
The idea is to include the information retrieved into columns in the ad_org table to improve the performance in such scenarios.
Introduction
This project aims to persist organization with the following information instead of getting it by calling PL functions every time.
- the organization's Period Control Allowed Organization.
- the organization's Calendar Owner Organization.
- Calendar of the organization's Calendar Owner Organization.
- the organization's Legal Entity.
- the organization's Business Unit.
Implementation
Add following columns in AD_Org table.
- AD_PeriodControlAllowed_Org_ID
- AD_CalendarOwner_Org_ID
- AD_InheritedCalendar_ID
- AD_LegalEntity_Org_ID
- AD_BusinessUnit_Org_ID
Application Dictionary
- Register columns using Tables and Columns.
- Add a field group Inherited Information which is collapse by default.
- Add read only fields for the above columns under Inherited Information field group.
Organization's Set Ready process
AD_Org_Ready is modified to set above columns using new functions AD_ORG_GETPERIODCONTROLALLOWTN, AD_ORG_GETCALENDAROWNERTN, AD_GET_ORG_LE_BU_TREENODE. Variables are used to store required information before setting them in AD_Org to avoid triggers mutating error in Oracle instance.
Refactoring existing function
Functions AD_ORG_GETPERIODCONTROLALLOW, AD_ORG_GETCALENDAROWNER and AD_GET_ORG_LE_BU are refactored to directly query appropriate new columns from ad_org table.
Modulescript
A modulescript is developed to update the old organization records with the inherited information in the respective columns.
Performance
- In an instance with 6000 products, a query to get inherited information of product's organization shows about 70% to 90% improvement in total run time.
- In an instance with 60 organizations, a query in reset accounting process reduced its time from 10.730 ms to 1.556 ms.
More details on performance are summarized in following sheet.
Notes for Developers
Below are the new functions created and they are used in the flows where organization is not set as ready.
- AD_ORG_GETPERIODCONTROLALLOWTN (Original code of AD_ORG_GETPERIODCONTROLALLOW)
- AD_ORG_GETCALENDAROWNERTN (Original Code of AD_ORG_GETCALENDAROWNER)
- AD_GET_ORG_LE_BU_TREENODE (Original Code of AD_GET_ORG_LE_BU)
 | In processes where performance is important, it is highly recommended to directly query new columns from AD_Org table which holds required inherited information as above functions have very important performance issues. |
References
- License: Openbravo Public License
- Category: Openbravo ERP
- Code: https://code.openbravo.com/erp/devel/pi/
- Tracking issue: https://issues.openbravo.com/view.php?id=33650
- Test case for complex organization tree creation with different levels of organization type and checking inherited information for organization is available in the test link.