Toolbox

Openbravo ERP is a multi-entity and multi-organization application, but some functionality about multi-organization is not coherently managed. This project tries to solve it.

Notice the multi-organization support is a feature of the system core. So currently, all registers have an Organization field to identify the organization that owns the register. For some functionalities, like accounting and warehouse management, there is need to define specific rules.

Technical design

Accounting

1.1

A new table called AD_OrgType has been created. This table will store all types of organizations available into the system. Since an organization could be a Legal Entity or a Business Unit, two columns called IsLegalEntity and IsBusinessUnit have been created. Apart from these two columns, another column called IsTransactionsAllowed has been created to store if transactions are possible or not for this Organization type.

A new column called AD_OrgType_ID has been added to the AD_Org table. This column is a foreign key to the AD_OrgType table, thus each organization is connected to its type.

1.2

Every organization where transactions are possible must have just one ancestor (including itself) that is a legal entity. This business rule could be broken in three cases:

Each time the IsTransactionAllowed field of an organization type is modified.
Each time the organization tree is modified.
Each time the associated organization type is changed for an Organization.

A function called AD_ORGTYPE_ISTRANS_ALLOWED will be the responsible of checking if this business rule is broken or not. First of all, the function will get all the organizations that have the IsTransactionAllowed='Y' and which have not got any child. For each organization, it will check if it's a legal entity or not. Finally the function will look around every organization's parents to see if any of them is a legal entity. The number of legal entities found will be updated during this process. In case the final number of legal entities is 0 or more than 1 for a full branch (from an organization without a child to the global parent), an exception will raise informing the user about the broken business rule, and it will not allow the change.

Every organization can have (not compulsory) just one ancestor (including itself) that is a business unit. This business rule could be broken in three cases:

Each time either the IsBusinessUnit field or the IsLegalEntity field is modified.
Each time the organization tree is modified.
Each time the associated organization type is changed for an Organization.

A function called AD_ORGTYPE_ISLE_ISBU, which will have a similar algorithm to the previous function, will be the responsible of guarantee this business rule is not broken. It will get every organization which does not have a child and it will check if itself or any ancestor in the branch is a business unit. Finally, if the number of business units found into the full branch is greater than 1, an exception will raise informing the user about the broken business rule, and it will not allow the change.

This two functions will be executed by triggers configured into the modified tables. Here is a summary about the relationship between triggers and functions:

Function	Trigger Name	On Action	DB Table
AD_ORGTYPE_ISLE_ISBU AD_ORGTYPE_ISTRANS_ALLOWED	AD_ORGTYPE_TRG	AFTER UPDATE ON	AD_OrgType
AD_ORGTYPE_ISLE_ISBU AD_ORGTYPE_ISTRANS_ALLOWED	AD_TREENODE_STLE_TRG	AFTER INSERT OR UPDATE ON	AD_Treenode
AD_ORGTYPE_ISLE_ISBU AD_ORGTYPE_ISTRANS_ALLOWED	AD_ORG_STLE_TRG	AFTER INSERT OR UPDATE OR DELETE ON	AD_Org

Points to take into account

This three triggers will be configured as Statement Level and not as Row Level, thus avoiding the mutant table issues. Notice that it is not necessary to use a Row Level trigger because we always want to check the whole structure of the tree, and not only the modified elements.
Due to the use of Statement Level triggers, we do not have information about the modified row, and as consequently we do not know the particular tree of the modified organization. Because of that, we are forced to check the structure of every organization tree available into the system, i.e we check the client organization tree and the other client organization trees. This does not mean a significant performance loss because this process will be executed only a few times by the user. Moreover, the check will not be affected by the other organization trees because they will also fulfill the business rules.

1.3

The application has to check a document and therefore an accounting entry does not have elements of different business unit or different legal entities. To fulfill this requirement, it will be necessary to modify every PL process which processes documents in order to ensure it.

First of all, the process will get the Identifier of the Business Unit or the Legal Entity from the document's header. If the header document organization is not neither a business unit nor a legal entity, it will search into the internal tree until finding a parent legal entity or a parent business unit. Then it will check every document's lines belong to the same Business Unit or Legal Entity as the header or to any child organization of the internal tree of the Business Unit or the Legal Entity.
This business rule will be implemented into two places: inside the Organization combobox of the document's line, and using a PL function which will be called each time a document is processed.

PL Check

The PL function, called AD_ORG_CHK_DOCUMENTS, will have some dynamical SQL code to generate the proper SQL statement for each document. For example, if we want to check a Remittance, we need to use the C_Remittance and C_RemittanceLine tables, but for a Bank Statement we will use the C_BankStatement and the C_BankStatementLines tables. Using this method we centralize this algorithm into just one place, reducing the amount of code added to each process. But, on the other hand, using dynamic cursors introduce us the need of adding the function code into the postscript-Oracle and postscript-PostgreSql scripts, because the DBSourceManager is unable of translating dynamic cursors.

Here is the code of the AD_ORG_CHK_DOCUMENTS function for Oracle:

create or replace
FUNCTION AD_ORG_CHK_DOCUMENTS(p_header_table IN VARCHAR2, p_lines_table IN VARCHAR2, p_document_id IN VARCHAR2, p_header_column_id IN VARCHAR2, p_lines_column_id IN VARCHAR2) RETURN NUMBER
AS
  v_org_header_id ad_org.ad_org_id%TYPE;
  v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
  v_islegalentity ad_orgtype.islegalentity%TYPE;
  v_tree_id ad_treenode.ad_tree_id%TYPE;
  v_is_included NUMBER:=0;
 

  TYPE RECORD IS REF CURSOR;
  cur_doc_lines RECORD;

  v_line_org VARCHAR2(32);
BEGIN

  -- Gets the organization and the organization type of the document's header
  EXECUTE IMMEDIATE 
    'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity 
    FROM '||p_header_table||', ad_org, ad_orgtype
    WHERE '||p_header_table||'.'||p_header_column_id||' = ||p_document_id||
    AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id
    AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id ' 
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity;
 
  -- Gets the organization's tree
  SELECT ad_tree.ad_tree_id
  INTO v_tree_id
  FROM ad_treenode, ad_tree
  WHERE ad_treenode.node_id=v_org_header_id
  AND ad_tree.ad_tree_id = ad_treenode.ad_tree_id
  AND ad_tree.ad_client_id= ad_treenode.ad_client_id
  AND ad_tree.treetype='OO';


  -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
  WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
    SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity, hh.ad_tree_id
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity, v_tree_id
    FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
    WHERE pp.node_id = hh.parent_id
    AND hh.ad_tree_id = pp.ad_tree_id
    AND pp.node_id=ad_org.ad_org_id
    AND hh.node_id=v_org_header_id
    AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
    AND  EXISTS (SELECT 1 FROM ad_tree WHERE ad_tree.treetype='OO' AND hh.ad_tree_id=ad_tree.ad_tree_id AND hh.ad_client_id=ad_tree.ad_client_id);     
  END LOOP;

  -- Check the lines belong to the same BU or LE as the header
  OPEN cur_doc_lines FOR
  'SELECT DISTINCT('||p_lines_table||'.ad_org_id) AS v_line_org
   FROM '||p_header_table||', '||p_lines_table||'
   WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
   AND '||p_lines_table||'.'||p_lines_column_id||'=||p_document_id||';    
   LOOP
     FETCH cur_doc_lines INTO v_line_org;
     EXIT WHEN cur_doc_lines%NOTFOUND;

     SELECT AD_ISMEMBERINCLUDED(v_line_org, v_org_header_id, v_tree_id) INTO v_is_included FROM dual;
     EXIT WHEN v_is_included=-1;

   END LOOP; 
  CLOSE cur_doc_lines;

  RETURN v_is_included;

END AD_ORG_CHK_DOCUMENTS;

And here is the same function for PostgreSQL:

CREATE OR REPLACE FUNCTION AD_ORG_CHK_DOCUMENTS(p_header_table character varying, p_lines_table character varying, p_document_id character varying, p_header_column_id character varying, p_lines_column_id character varying)
  RETURNS numeric AS
$BODY$ DECLARE
  v_org_header_id ad_org.ad_org_id%TYPE;
  v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
  v_islegalentity ad_orgtype.islegalentity%TYPE;
  v_tree_id ad_treenode.ad_tree_id%TYPE;
  v_is_included NUMERIC:=0;
  v_aaa VARCHAR(2000);

  TYPE_Ref REFCURSOR;
  cur_doc_lines TYPE_REF%TYPE;

  v_line_org VARCHAR(32);
BEGIN

  EXECUTE 
    'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity 
    FROM '||p_header_table||', ad_org, ad_orgtype
    WHERE '||p_header_table||'.'||p_header_column_id||'='||'||p_document_id||'||' 
    AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id
    AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id' 
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity;

  SELECT ad_tree.ad_tree_id
  INTO v_tree_id
  FROM ad_treenode, ad_tree
  WHERE ad_treenode.node_id=v_org_header_id
  AND ad_tree.ad_tree_id = ad_treenode.ad_tree_id
  AND ad_tree.ad_client_id= ad_treenode.ad_client_id
  AND ad_tree.treetype='OO';


  WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
    SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity, hh.ad_tree_id
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity, v_tree_id
    FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
    WHERE pp.node_id = hh.parent_id
    AND hh.ad_tree_id = pp.ad_tree_id
    AND pp.node_id=ad_org.ad_org_id
    AND hh.node_id=v_org_header_id
    AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
    AND  EXISTS (SELECT 1 FROM ad_tree WHERE ad_tree.treetype='OO' AND hh.ad_tree_id=ad_tree.ad_tree_id and hh.ad_client_id=ad_tree.ad_client_id);     
  END LOOP;

  v_aaa:='SELECT DISTINCT('||p_lines_table||'.ad_org_id) AS v_line_org FROM '||p_header_table||', '||p_lines_table||'   WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||' AND '||p_lines_table||'.'||p_lines_column_id||'='||'||p_document_id||';

  OPEN cur_doc_lines FOR EXECUTE v_aaa;   
   LOOP
     FETCH cur_doc_lines INTO v_line_org;
     IF NOT FOUND THEN
       EXIT;
     END IF;


     SELECT AD_ISMEMBERINCLUDED(v_line_org, v_org_header_id, v_tree_id) INTO v_is_included FROM dual;
     EXIT WHEN v_is_included=-1;

   END LOOP; 
  CLOSE cur_doc_lines;

  RETURN v_is_included;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

The function will return -1 if the business rule is broken for any line.

The processes which are going to use the AD_ORG_CHK_DOCUMENTS function are:

DB Table	PL Process	Must have	Note
A_Amortization	A_Amortization_Process	Y	Completed
C_BankStatement	C_BankStatement_Post	Y	Completed
C_DP_Management	C_DP_Management_Post	Y	Completed
C_Cash	C_Cash_Post	Y	Completed
C_Settlement	C_Settlement_Post	Y	Completed. We check that the Settlement has the same legal entity or business unit as the Canceled and Created Payments. Take into account the process currently check if the organization associated with the debt payment is different or does not depend on the organization associated with the Settlement; so although our check is valid, the process will fail if the previous rule is broken.
C_Invoice	C_Invoice_Post	Y	Completed. The application currently forces the lines to be of the same organization as the header, so this check will always be valid.
GL_Journal	GL_Journal_Post	Y	Completed
C_Remittance	C_Remittance_Post	Y	Completed
C_Order	C_Order_Post1	N	Completed. The application currently forces the lines to be of the same organization as the header, so this check will always be valid.
M_InOut	M_InOut_Post	N	Completed. The application currently forces the lines to be of the same organization as the header, so this check will always be valid.
M_Inventory	M_Inventory_Post	N	Completed
M_Movement	M_Movement_Post	N	Completed
M_Production	M_Production_Run MA_WorkEffort_Validate	N	Completed

Finally, the PL process will have to guarantee that the payments inside the document's lines belong to the same Business Unit or Legal Entity as the header. The latest requirement will only take into account for the following cases:

Document type	DB Table	PL Process	Note
Remittances	C_Remittance	C_Remittance_Post	Completed
Bank Statement	C_BankStatement	C_BankStatement_Post	Completed
Cash	C_Cash	C_Cash_Post	Completed
Payment Management	C_DP_Management	C_DP_Management_Post	Completed

A function called AD_ORG_CHK_DOC_PAYMENTS has been created following a similar algorithm as the AD_ORG_CHK_DOCUMENTS. Here is the source code of the AD_ORG_CHK_DOC_PAYMENTS function (Oracle version):

create or replace
FUNCTION AD_ORG_CHK_DOC_PAYMENTS(p_header_table IN VARCHAR2, p_lines_table IN VARCHAR2, p_document_id IN VARCHAR2, p_header_column_id IN VARCHAR2, p_lines_column_id IN VARCHAR2, p_lines_column_payment_id IN VARCHAR2) RETURN NUMBER
AS
  v_org_header_id ad_org.ad_org_id%TYPE;
  v_isbusinessunit ad_orgtype.isbusinessunit%TYPE;
  v_islegalentity ad_orgtype.islegalentity%TYPE;
  v_tree_id ad_treenode.ad_tree_id%TYPE;
  v_is_included NUMBER:=0;


  TYPE RECORD IS REF CURSOR;
  cur_doc_lines_payment RECORD;

  v_line_org_payment VARCHAR2(32);
BEGIN

  -- Gets the organization and the organization type of the document's header
  EXECUTE IMMEDIATE 
    'SELECT ad_org.ad_org_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity 
    FROM '||p_header_table||', ad_org, ad_orgtype
    WHERE '||p_header_table||'.'||p_header_column_id||' = ||p_document_id||
    AND ad_org.ad_orgtype_id = ad_orgtype.ad_orgtype_id
    AND '||p_header_table||'.ad_org_id=ad_org.ad_org_id ' 
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity;

  -- Gets the organization's tree
  SELECT ad_tree.ad_tree_id
  INTO v_tree_id
  FROM ad_treenode, ad_tree
  WHERE ad_treenode.node_id=v_org_header_id
  AND ad_tree.ad_tree_id = ad_treenode.ad_tree_id
  AND ad_tree.ad_client_id= ad_treenode.ad_client_id
  AND ad_tree.treetype='OO';


  -- Gets recursively the organization parent until finding a Business Unit or a Legal Entity
  WHILE (v_isbusinessunit='N' AND v_islegalentity='N') LOOP
    SELECT hh.parent_id, ad_orgtype.isbusinessunit, ad_orgtype.islegalentity, hh.ad_tree_id
    INTO v_org_header_id, v_isbusinessunit, v_islegalentity, v_tree_id
    FROM ad_org, ad_orgtype, ad_treenode pp, ad_treenode hh
    WHERE pp.node_id = hh.parent_id
    AND hh.ad_tree_id = pp.ad_tree_id
    AND pp.node_id=ad_org.ad_org_id
    AND hh.node_id=v_org_header_id
    AND ad_org.ad_orgtype_id=ad_orgtype.ad_orgtype_id
    AND  EXISTS (SELECT 1 FROM ad_tree WHERE ad_tree.treetype='OO' AND hh.ad_tree_id=ad_tree.ad_tree_id AND hh.ad_client_id=ad_tree.ad_client_id);     
  END LOOP;


  -- Check the payments of the lines belong to the same BU or LE as the document header
  OPEN cur_doc_lines_payment FOR
  'SELECT DISTINCT(C_DEBT_PAYMENT.ad_org_id) AS v_line_org_payment
   FROM '||p_header_table||', '||p_lines_table||', C_DEBT_PAYMENT
   WHERE '||p_header_table||'.'||p_header_column_id||' = '||p_lines_table||'.'||p_lines_column_id||'
   AND C_DEBT_PAYMENT.C_DEBT_PAYMENT_ID='||p_lines_table||'.'||p_lines_column_payment_id||'
   AND '||p_lines_table||'.'||p_lines_column_id||'=||p_document_id||';


  LOOP
   FETCH cur_doc_lines_payment INTO v_line_org_payment;
   EXIT WHEN cur_doc_lines_payment%NOTFOUND;

   SELECT AD_ISMEMBERINCLUDED(v_line_org_payment, v_org_header_id, v_tree_id) INTO v_is_included FROM dual;
   EXIT WHEN v_is_included=-1;

  END LOOP; 
  CLOSE cur_doc_lines_payment;

  RETURN v_is_included;

END AD_ORG_CHK_DOC_PAYMENTS;

Combobox Validation

The combobox validation will be even more restrictive than the previous check. The combobox inside the document's lines will only show the child organizations of the document's header organization. For guaranteeing it, the validation will use the AD_ISORGINCLUDED function.

A new SQL validation rule, called AD_Org show child organizations has been created:

AD_ISORGINCLUDED(ad_org.ad_org_id, @AD_ORG_ID@, ad_org.ad_client_id)<>-1

This validation rule will be included into the AD_ORG_ID column of every line table of the following header tables:

DB Header Table	Note
A_Amortization	Completed
C_BankStatement	Completed
C_DP_Management	Completed
C_Cash	Completed
C_Settlement	Completed. Added the validation to the C_Debt_Payment_Generate table
C_Invoice	Completed
GL_Journal	Completed. The validation has been also added to the GL_Journal table which is the child of the Batch tab.
C_Remittance	Completed
C_Order	Completed
M_InOut	Completed
M_Inventory	Completed
M_Movement	Completed
M_Production	Completed. The validation has been also added to the M_ProductionPlan table which is the child of the BOM tab.

Take into account that the AD_ORG_ID column of the header table must be stored as a session variable, otherwise the validation rule will not work.

1.5

When the Report Cash Flow is configured to filter by organization (inside Financial Management || Accounting || Analysis Tools || Accounting Report window), the organization field must only show business units or legal entities in order to guarantee the report is balanced.

Thus, the report's filter for selecting the organization will be modified to show only legal entities or business units. To do this, the corresponding SQL query will include the AD_OrgType table, which gives us information about the organization type.

The way to implement it will be to create a new Validation rule called AD_OrgType_BU_LE, which will be used by the ReportCashFlow.java controller to build the corresponding SQL query for the Organization Combobox displayed into the window.

The AD_OrgType_BU_LE validation rule is:

exists (select 1 from ad_orgtype where ad_orgtype.ad_orgtype_id = ad_org.ad_orgtype_id and (islegalentity='Y' or isbusinessunit='Y'))

1.5b

The General Accounting Report Setup window will have a checkbox called Balanced where the user could select if he or she wants to configure a balanced report or not. When Balanced is marked, only Business Unit or Legal Entities should appear into the Organization combobox of the Financial Management || Accounting || Analysis Tools || General Accounting Report || General Accounting Report window.

A new column called IsOrgBalanced will be created into the C_ACCT_RPT table. This column will be of the type char(1) and will only allow 'Y' or 'N' character. IsOrgBalanced will be the responsible of storing if the corresponding report is balanced or not.

Take also into account it will be necessary to guarantee the Organization combobox in the General Accounting Report window is filled again each time the Accounting Report combobox is changed; this will be done using a Javascript function called validateOrgCombo() which will be executed with the onChange event of the Accounting Report and Accounting schema fields. This function will fill the Organization combobox with two possible sets:

If the selected Accounting Report is balanced, it will fill the combobox with the available Organizations that are either business unit or legal entity.
If it is not balanced, it will fill the combobox with all the available Organizations.

The GeneralAccountingReport.java controller will send three new Arrays to the view:

arrBalancedOrg which contains the Business Unit or Legal Entity Organizations
arrAllOrg which contains all the available Organizations
arrReportIsBalanced which contains a list of all the reports and information about if they are balanced or not.

Example: code for generating the arrBalancedOrg array:

String balancedOrg;
    try {
      ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "AD_Org_ID", "", "AD_OrgType_BU_LE", Utility.getContext(this, vars, "#User_Org", "ReportCashFlow"), Utility.getContext(this, vars, "#User_Client", "ReportCashFlow"), 0);
      Utility.fillSQLParameters(this, vars, null, comboTableData, "ReportCashFlow", "");
      FieldProvider[] dataOrg = comboTableData.select(false);
      balancedOrg = "var arrBalancedOrg = new Array(\n";
      for (int i = 0;i<dataOrg.length;i++) {
        balancedOrg += "new Array(\"" + dataOrg[i].getField("id")  + "\",\"" + dataOrg[i].getField("name") + "\")";
        if (i<dataOrg.length-1) balancedOrg += ",\n";
      }
      balancedOrg += ");";

      comboTableData = null;
    } catch (Exception ex) {
      throw new ServletException(ex);
    }
    xmlDocument.setParameter("balancedOrg", balancedOrg);

The validateOrgCombo() Javascript function will use this three Arrays to fill the Organization combobox properly. Here is the code of this function:

function validateOrgCombo() {
  var id = document.frmMain.inpcElementvalueId.value;
  var cmbOrganization = document.frmMain.inpOrganizacion; 

  for (i = 0; i < arrReportIsBalanced.length; i++) {
    if (id == arrReportIsBalanced[i][0] && arrReportIsBalanced[i][2]=='N') {
      fillCombo(cmbOrganization, arrAllOrg, false, false);
    }
    if (id == arrReportIsBalanced[i][0] && arrReportIsBalanced[i][2]=='Y') {
      fillCombo(cmbOrganization, arrBalancedOrg, false, false);
    }
  }
  return true;
}

Finally, the General Accounting Report window will be reorganized to have a coherent flow for the user. So the new flow for the fields will be:

Accounting schema
Accounting Report
Organization
Account Level

1.6

Not to be done.

1.7

The organizations which are Business Unit or Legal Entity can have one or several accounting schemas.

Accounting schemas will be stored into the C_ACCTSCHEMA table. Because a Business Unit or a Legal Entity can have more than one accounting schema, a new table called AD_ORG_ACCTSCHEMA will be created. This table will connect an organization with all of its accounting schemas. Here is shown the Entity-Relational model of the tables:

The AD_ORG_ACCTSCHEMA table will have the following structure:

AD_ORG_ACCTSCHEMA_ID           NOT NULL VARCHAR2(32)                                                                                                                                                                                  
AD_CLIENT_ID                   NOT NULL VARCHAR2(32)                                                                                                                                                                                  
AD_ORG_ID                      NOT NULL VARCHAR2(32)                                                                                                                                                                                  
ISACTIVE                       NOT NULL CHAR(1)                                                                                                                                                                                       
CREATED                        NOT NULL DATE                                                                                                                                                                                          
CREATEDBY                      NOT NULL VARCHAR2(32)                                                                                                                                                                                  
UPDATED                        NOT NULL DATE                                                                                                                                                                                          
UPDATEDBY                      NOT NULL VARCHAR2(32)                                                                                                                                                                                  
C_ACCTSCHEMA_ID                NOT NULL VARCHAR2(32)

The tab Org Schema will be created into the Organization window. The user could create or delete an association between the organization and an accounting schema inside it. This tab will only allow the user to create/modify/delete an association for the current organization, i.e, the one selected into the Organization window.

Another tab, called Period Control, will be created into the Organization window (view Projects/EnhancedMulti-organizationSupport/Technical_Documentation#2.2). This tab will be the frontend for the C_PeriodControl table, and it will have a similar behavior as the Org Schema tab.

1.8

Each time a legal entity is created, modified or deleted, we have to check that it has itself or an ancestor at least an accounting schema. This will be done creating a function with a similar algorithm as the 1.2 functions. This function, called AD_ORG_CHK_SCHEMAS, will be executed by the triggers configured into the affected database tables. Here is the relationship between the function and the tables:

Function	Trigger Name	On Action	DB Table
AD_ORG_CHK_SCHEMAS	AD_ORGTYPE_TRG	AFTER UPDATE ON	AD_OrgType
AD_ORG_CHK_SCHEMAS	AD_TREENODE_STLE_TRG	AFTER INSERT OR UPDATE ON	AD_Treenode
AD_ORG_CHK_SCHEMAS	AD_ORG_STLE_TRG	AFTER INSERT OR UPDATE OR DELETE ON	AD_Org

Points to take into account

Due to the design of the application, when the user is creating a new organization and he wants it to be a legal entity, he has to select an organization type which is not a legal entity, save it, associate it with an accounting schema, then go again to the Organization window and change the organization type to the correct one.

A user can change the tree structure of the organizations, so the business rule about the accounting schema of ancestors could be broken. A Row Level trigger can not be implemented into the AD_Treenode table because this trigger must consult the same table and, if it does that, a mutant table issue will raise. So instead of using a Row Level trigger, we are going to implement the same solution as in 1.2, i.e, using a Statement Level trigger.

1.9

An organization can have (or not) an assigned calendar. For allowing it, a new column called C_Calendar_ID, which will accept null values, will be added to the AD_Org table. This column will be a foreign key pointing to the C_Calendar table. An entity-relation schema showing this relationship is available in a previous chapter.

All the organizations that belong to the same legal entity must have an unique calendar. We will check it with the help of the AD_ISORGINCLUDED function. The number of distinct calendars for the legal entity internal tree must be 0 or 1. Example:

select count(distinct c_calendar_id), max(c_calendar_id)
from ad_org
where AD_ISORGINCLUDED(ad_org.ad_org_id, '1000006', ad_org.ad_client_id)<>-1

Moreover, we also have to check that every ancestor of the legal entity has assigned the same calendar (or no calendar) as the legal entity. For guaranteeing it we will check every parent organization calendar.

Finally, if at the end of the process we detect that the legal entity does not have neither a calendar itself nor any of the ancestor, the application will raise an error informing the user that a legal entity does not have a calendar attached to it.

A new function called AD_ORG_CHK_CALENDAR will be created following a similar algorithm as the AD_ORG_CHK_SCHEMAS. This new function will be executed by the following triggers (same design as the previous chapter):

Function	Trigger Name	On Action	DB Table
AD_ORG_CHK_CALENDAR	AD_ORGTYPE_TRG	AFTER UPDATE ON	AD_OrgType
AD_ORG_CHK_CALENDAR	AD_TREENODE_STLE_TRG	AFTER INSERT OR UPDATE ON	AD_Treenode
AD_ORG_CHK_CALENDAR	AD_ORG_STLE_TRG	AFTER INSERT OR UPDATE OR DELETE ON	AD_Org

Here is the source code of the AD_ORG_CHK_CALENDAR:

create or replace
FUNCTION AD_ORG_CHK_CALENDAR RETURN NUMBER

AS 
  v_parent_id ad_treenode.parent_id%TYPE;
  v_node_id ad_treenode.parent_id%TYPE;

  e_le_gt1_calendars EXCEPTION;
  e_le_parent_calendars EXCEPTION;
  e_le_no_calendars EXCEPTION;

  v_num_calendars INTEGER;
  v_calendar_id ad_org.c_calendar_id%TYPE;
  v_calendar_id_old ad_org.c_calendar_id%TYPE;


  -- Get Legal Entities
  CURSOR c_org_le IS
  SELECT ad_org.ad_org_id
   FROM ad_org, ad_orgtype
   WHERE IsLegalEntity='Y'
   AND ad_org.ad_orgtype_id= ad_orgtype.ad_orgtype_id;


BEGIN

  FOR v_reg_org_le IN c_org_le LOOP

    -- The number of calendars must be < 1
    SELECT COUNT(DISTINCT c_calendar_id), COALESCE(MAX(c_calendar_id), 'NO CALENDAR')
    INTO v_num_calendars, v_calendar_id
    FROM ad_org
    WHERE AD_ISORGINCLUDED(ad_org.ad_org_id, v_reg_org_le.ad_org_id, ad_org.ad_client_id)<>-1;

    IF (v_num_calendars>1) THEN
      RAISE e_le_gt1_calendars;
    END IF;

    v_calendar_id_old := v_calendar_id;
    v_node_id := v_reg_org_le.ad_org_id;
    v_parent_id := 'XX';
    WHILE ( v_parent_id <> '0' AND v_node_id <> '0' ) LOOP      

      SELECT parent_id
      INTO v_parent_id
      FROM ad_treenode t
      WHERE node_id=v_node_id
      AND EXISTS (SELECT 1 
                  FROM ad_tree, ad_org
                  WHERE ad_tree.ad_client_id = ad_org.ad_client_id
                  AND ad_tree.ad_client_id=t.ad_client_id
                  AND ad_tree.treetype='OO'
                  AND t.ad_tree_id=ad_tree.ad_tree_id
                  );


      SELECT COALESCE(ad_org.c_calendar_id, 'NO CALENDAR')
      INTO v_calendar_id
      FROM ad_org
      WHERE ad_org_id=v_parent_id;


      IF ( (v_calendar_id_old <> v_calendar_id) AND ((v_calendar_id_old <> 'NO CALENDAR') AND (v_calendar_id <> 'NO CALENDAR' ))) THEN
        RAISE e_le_parent_calendars;
      END IF;

      IF (v_calendar_id<>'NO CALENDAR') THEN
        v_num_calendars:= v_num_calendars+1;
        v_calendar_id_old:= v_calendar_id;
      END IF;


      v_node_id:=v_parent_id;
    END LOOP;

    IF (v_num_calendars=0) THEN
      RAISE e_le_no_calendars;
    END IF;

 END LOOP;


 RETURN 0;

EXCEPTION
  WHEN e_le_gt1_calendars THEN
    RETURN -2;
  WHEN e_le_parent_calendars THEN
    RETURN -1;
  WHEN e_le_no_calendars THEN
    RETURN -3;

END AD_ORG_CHK_CALENDAR;

Period Control

2.1

An organization that is checked as business unit or a legal entity has a mark to define if that organization can open and close the period control.

A new column called AD_ORG_ISPERIODCONTROLALLOWED will be added to the AD_ORG table. The Organization window will be updated to show this new checkbox.

This checkbox will be only available when the organization is a business unit or a legal entity. To achieve this requirement a Display logic will be implemented into this field.

Note that to know if the organization is a legal entity or a business unit we have to query the AD_OrgType table, but we can't do it inside the Organization window. To solve it, a new Auxiliary input, called ISPERIODCONTROLALLOWED, will be created with the following validation code:

@SQL=SELECT 'Y' FROM ad_org, ad_orgtype 
WHERE ad_org.ad_orgtype_id= ad_orgtype.ad_orgtype_id 
AND ad_org.ad_org_id=@AD_ORG_ID@ AND (ISBUSINESSUNIT='Y' OR ISLEGALENTITY='Y')

Finally, the Display logic inside the Allow Period Control field will have the following rule:

@ISPERIODCONTROLALLOWED@='Y'

2.2

AD_ORG_TRG

The AD_ORG_TRG trigger will be modified to automatically insert or delete period controls for the organization when the organization changes the associated calendar.

When creating (inserting) a new organization, if it is associated to a calendar, the trigger will create the period controls for this organization.
When modifying (updating) an organization, the trigger will check if it had previously associated a calendar and it will delete the related period controls. If a new calendar is associated to the organization, it will recreate the proper period controls for the organization.

C_PERIOD_TRG

The C_Period_TRG currently inserts into the C_PeriodControl table the necessary documents for every period, but it always associates the new row to the organization which has created the period. This behavior is not the expected now, instead of it each new created row inside the C_PeriodControl table has to be assigned to the same organization that uses the year's calendar. The original cursor inside the trigger will be changed by:

(SELECT Value , a.AD_Org_ID AS Organization
 FROM AD_Ref_List , (SELECT AD_Org_ID 
                     FROM AD_Org 
                     WHERE C_Calendar_ID = (SELECT C_Calendar_ID
                                            FROM C_Year
                                            WHERE C_Year.C_Year_ID=:new.C_Year_ID
                                            )
                     ) a
 WHERE AD_Reference_ID='183'
 ORDER BY 1)

Note that the 'Calendar' must be associated to a Organization before 'Creating Periods'.

Organization window

The period control must be managed independently of the creating and assigning calendars to organizations process. A user should have access to close and open a period but not to create calendars, years and periods. To guarantee it, some application windows will be redesigned: the Organization window will have inside the Year tab, and Period tab inside it, being both tabs read only. Finally, the Period Control tab, which will be inside the Period tab, will let the user to open or close the periods. Here is shown a mockup of the design:

The Year tab will only show the fiscal years of the calendar for the selected organization; if the selected organization does not have an associated calendar, the Year tab will be empty. WAD currently allows only to link two tabs by the primary key of the header tab, so a workaround to avoid this behavior will be to create a database view which will connect the C_Year and the AD_Org tables. Here is the code of the C_Year_V view:

CREATE OR REPLACE VIEW C_YEAR_V AS
  SELECT C_YEAR.C_YEAR_ID, C_YEAR.C_CALENDAR_ID, C_YEAR.AD_CLIENT_ID,  AD_ORG.AD_ORG_ID, C_YEAR.ISACTIVE, C_YEAR.CREATED, C_YEAR.CREATEDBY, C_YEAR.UPDATED, 
  C_YEAR.UPDATEDBY, C_YEAR.YEAR, C_YEAR.DESCRIPTION
  FROM C_YEAR, AD_ORG
  WHERE C_YEAR.C_CALENDAR_ID = AD_ORG.C_CALENDAR_ID;

The Period Control tab will show the period controls for the selected period and for the selected organization into the header Organization tab. So the AD_Org_ID value selected into the header Organization header will be stored as a session variable, but the same variable for the Year tab and, specially, for the Period tab will not be saved as a session variable because the period's organization could be different from the header's organization.

Note: it's not possible to go back from Period tab to the Year tab, because the application does not remember the AD_ORG_ID session variable from the organization's header.

The Period Control tab which is currently inside the Fiscal Calendar window will be deprecated, and the one inside the Organization window will be used instead of it. Besides, the Open/Close All button inside the Period tab (into the Fiscal Calendar window) will be hidden because this functionality will be done by a new process, called Open/Close Period Control, which will be available into the application menu.

Open/Close Period Control Process

Window

The Open/Close Period Control window will have the following fields:

Organization [combobox]: It will show only the organizations that are allowed to open or close periods, i.e., the ones which have the ISPERIODCONTROLALLOWED column equals true.
Calendar [combobox, readonly]: When the organization is selected, it will show the Calendar of this organization for information purpose.
Open also child organizations [checkbox]: If it's selected, the process will also open the documents of all the organizations that depend on the selected organization.
Year [combobox]: It will show the available years for the selected organization.
Until Period [combobox]: It will show the available periods for the selected year. The process will open from the last document open or closed.
Document Category [combobox]: If it's not defined, all the documents are closed or opened.
Action [combobox]: Available actions are: Open, Close and Permanent close.

A Process created by the WAD can't have neither Callouts (or validation rules) nor Display logic, so the first idea was to create a manual window where the developer has the freedom to do those things. But the solution that is going to be implemented is to create an automatically generated window that will be based on a new created database table called C_PERIODCONTROL_LOG. This table will have the common auditing columns plus nine columns:

Description [varchar2 (255)]: where the user can store information about why he has run the process.
Processing [char (1)]: it stores if the process is currently running. This column will avoid that two processes execute at the same time in the same record.
Processed [char (1)]: it stores if the process has been executed or not. This field will let the developer to display or not the Process button depending on this value, thus avoiding running the process several times for the same record.
C_CALENDAR_ID [varchar2 (32)]: it stores the organization's calendar.
ISRECURSIVE [char(1)]: it stores if it is necessary also to open the documents for the child organizations.
C_YEAR_ID [varchar2 (32)]: it stores the selected calendar.
PERIODNO [numeric (10)]: it is the selected period
DOCBASETYPE [char (3)]: it is the document type.
PERIODACTION [char (1)]: a character which represents the action type to be executed.

Notice that this design based on the C_PERIODCONTROL_LOG allows the user to have a log about the processes run. To enforce this idea, each time a record is processed, the process button will be hidden and all the fields will become read only. This can be easily done using the Display Logic [@PROCESSED@='N'] and the Read Only Logic [@PROCESSED@='Y'] respectively.

Three new validations rules will be added to application to manage the subordinate comboboxes:

AD_Org IsPeriodControlAllowed: which will be associated with the Organization combobox. Only organizations which can open or close period will be displayed.
C_Calendar and C_Year of an organization: which will be associated with the Calendar and Year comboboxes. It ensures the calendar combobox shows always the calendar of the selected organization, and also that the Year combobox is only filled with available years for the organization's calendar. Here is the code of the validation rule:

C_CALENDAR_ID IN (SELECT C_CALENDAR_ID FROM AD_ORG WHERE AD_ORG_ID=@AD_ORG_ID@)

Allowed PeriodActions: which will ensure that only proper values (Open, Close and Permanently Close Period) are displayed into the Period Action combobox. This combobox is filled using the already created C_PeriodControl Action reference list which contains a <No action> element, but we do not want to introduce this element in our combobox, that is why this validation rule will be created.

Due to some problems with the automatically generated windows by the WAD, it is impossible to add a new validation rule to the Until Period No. field because this validation must be done using the C_Year_ID session variable, which stores the currently selected year, but the session variable is not updated until the user manually selects a new year. Because of that, the values that appear into the Until Period No. for the first time (after changing the organization) will not be valid.

That is why we have decided to create a new callout, called SE_PeriodNo, which will be the responsible of updating the values of the Until Period No.' field. So this callout will be associated to the Year combobox.

Process

The PL process called C_PERIOD_PROCESS will be almost completely redesigned. This process will read the data from the C_PeriodControl_Log table and execute the action configured by the user following the specifications commented before. Here is shown the source code of the process:

create or replace
PROCEDURE C_PERIOD_PROCESS(p_pinstance_id IN VARCHAR2) 

AS
  -- Logistice
  v_ResultStr VARCHAR2(2000):=;
  v_Message VARCHAR2(2000):=;
  v_Record_ID VARCHAR2(32);
  v_Record_ID_Log VARCHAR2(32);
  -- Parameter
  TYPE RECORD IS REF CURSOR;
    Cur_Document RECORD;
  
  -- Parameter Variables
  p_Organization C_PeriodControl_Log.AD_Org_ID%TYPE;
  p_IsRecursive C_PeriodControl_Log.IsRecursive%TYPE;
  p_Year C_PeriodControl_Log.C_Year_ID%TYPE;
  p_PeriodNO C_PeriodControl_Log.PeriodNO%TYPE;
  p_DocBaseType C_PeriodControl_Log.DocBaseType%TYPE;
  p_PeriodAction C_PeriodControl_Log.PeriodAction%TYPE;
  p_Processing C_PeriodControl_Log.Processing%TYPE;
  
BEGIN
    --  Update AD_PInstance
    DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID) ;
    v_ResultStr:='PInstanceNotFound';
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;

BEGIN

    -- Get Parameters
    SELECT Record_ID
    INTO v_Record_ID_Log
    FROM AD_PInstance
    WHERE AD_PInstance_ID=p_PInstance_ID;
    
    SELECT AD_Org_ID, ISRecursive, C_Year_ID, PeriodNO, DocBaseType, PeriodAction, Processing
    INTO p_Organization, p_IsRecursive, p_Year, p_PeriodNO, p_DocBaseType, p_PeriodAction, p_Processing
    FROM C_PeriodControl_Log
    WHERE C_PeriodControl_Log_ID=v_Record_ID_Log;
      
    IF (p_Processing='N') THEN 
      
      UPDATE C_PeriodControl_Log
      SET Processing='Y'
      WHERE C_PeriodControl_Log_ID=v_Record_ID_Log;   
      -- Action: Open if not permanently closed
      IF(p_PeriodAction='O') THEN
              
        IF (p_IsRecursive='N') THEN
          FOR Cur_Document IN
            (SELECT C_PERIODCONTROL_ID
              FROM C_PERIODCONTROL, C_PERIOD 
              WHERE C_PERIODCONTROL.C_PERIOD_ID=C_PERIOD.C_PERIOD_ID 
              AND C_PERIOD.C_Year_ID=p_Year
              AND C_PERIODCONTROL.AD_Org_ID=p_Organization
              AND C_PERIOD.EndDate<=(SELECT EndDate FROM C_PERIOD WHERE C_Period.C_Period_ID=p_PeriodNO)
              AND C_PERIODCONTROL.DocBaseType LIKE COALESCE(p_DocBaseType, '%')
            )
          LOOP
            v_Record_ID:=Cur_Document.C_PERIODCONTROL_ID;
            UPDATE C_PeriodControl
              SET PeriodStatus='O'
            WHERE C_PeriodControl_ID=v_Record_ID
              AND PeriodStatus<>'P';
          END LOOP;
          
        ELSIF (p_IsRecursive='Y') THEN
          FOR Cur_Document IN
            (SELECT C_PERIODCONTROL_ID
              FROM C_PERIODCONTROL, C_PERIOD 
              WHERE C_PERIODCONTROL.C_PERIOD_ID=C_PERIOD.C_PERIOD_ID 
              AND C_PERIOD.C_Year_ID=p_Year
              AND C_PERIODCONTROL.AD_Org_ID IN (SELECT AD_Org_ID
                                                FROM AD_Org 
                                                WHERE AD_ISORGINCLUDED(ad_org.ad_org_id, p_Organization, ad_org.ad_client_id)<>-1)
              AND C_PERIOD.EndDate<=(SELECT EndDate FROM C_PERIOD WHERE C_Period.C_Period_ID=p_PeriodNO)
              AND C_PERIODCONTROL.DocBaseType LIKE COALESCE(p_DocBaseType, '%')
            )
          LOOP
            v_Record_ID:=Cur_Document.C_PERIODCONTROL_ID;    
            UPDATE C_PeriodControl
              SET PeriodStatus='O'
            WHERE C_PeriodControl_ID=v_Record_ID
              AND PeriodStatus<>'P';
          END LOOP;
        END IF;
        
        -- Action: Close if not permanently closed
      ELSIF(p_PeriodAction='C') THEN   
         FOR Cur_Document IN
            (SELECT C_PERIODCONTROL_ID
              FROM C_PERIODCONTROL, C_PERIOD 
              WHERE C_PERIODCONTROL.C_PERIOD_ID=C_PERIOD.C_PERIOD_ID 
              AND C_PERIOD.C_Year_ID=p_Year
              AND C_PERIODCONTROL.AD_Org_ID=p_Organization
              AND C_PERIOD.EndDate<=(SELECT EndDate FROM C_PERIOD WHERE C_Period.C_Period_ID=p_PeriodNO)
              AND C_PERIODCONTROL.DocBaseType LIKE COALESCE(p_DocBaseType, '%')
            )
          LOOP
            v_Record_ID:=Cur_Document.C_PERIODCONTROL_ID;            
            UPDATE C_PeriodControl
              SET PeriodStatus='C'
            WHERE C_PeriodControl_ID=v_Record_ID
              AND PeriodStatus<>'P';
          END LOOP;
        -- Action: Permanently Close
      ELSIF(p_PeriodAction='P') THEN
        FOR Cur_Document IN
            (SELECT C_PERIODCONTROL_ID
              FROM C_PERIODCONTROL, C_PERIOD 
              WHERE C_PERIODCONTROL.C_PERIOD_ID=C_PERIOD.C_PERIOD_ID 
              AND C_PERIOD.C_Year_ID=p_Year
              AND C_PERIODCONTROL.AD_Org_ID=p_Organization
              AND C_PERIOD.EndDate<=(SELECT EndDate FROM C_PERIOD WHERE C_Period.C_Period_ID=p_PeriodNO)
              AND C_PERIODCONTROL.DocBaseType LIKE COALESCE(p_DocBaseType, '%')
            )
          LOOP
            v_Record_ID:=Cur_Document.C_PERIODCONTROL_ID;
            UPDATE C_PeriodControl  SET PeriodStatus='P'  WHERE C_PeriodControl_ID=v_Record_ID;
          END LOOP;
      END IF;
      
      
      --<<FINISH_PROCESS>>
      --  Update AD_PInstance
      DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
      AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 1, v_Message) ;
      -- Update C_PeriodControl_Log
      UPDATE C_PeriodControl_Log
      SET Processing='N', Processed='Y'
      WHERE C_PeriodControl_Log_ID=v_Record_ID_Log;     
      
    ELSE
      RAISE_APPLICATION_ERROR(-20000, '@OtherProcessActive@');
    END IF;
END; --BODY
EXCEPTION
WHEN OTHERS THEN
  v_ResultStr:= '@ERROR=' || SQLERRM;
  DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
  IF(p_PInstance_ID IS NOT NULL) THEN
    ROLLBACK;
    AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
  ELSE
    RAISE;
  END IF;
END C_PERIOD_PROCESS
;

2.3

When a document is going to be processed, every process has to check that the period control already exists and it is not closed (or permanently closed).

So it will be necessary to change the following process in order to include this check:

DB Table	PL Process	Note
A_Amortization	A_Amortization_Process	Completed. The document type 'AMZ' is hardcoded because the table does not have a C_DocType_ID column.
C_BankStatement	C_BankStatement_Post	Completed. The document type 'CMB' is hardcoded because the table does not have a C_DocType_ID column.
C_DP_Management	C_DP_Management_Post	Completed
C_Cash	C_Cash_Post	Completed. The document type 'CMC' is hardcoded because the table does not have a C_DocType_ID column.
C_Settlement	C_Settlement_Post	Completed
C_Invoice	C_Invoice_Post	Completed
GL_Journal	GL_Journal_Post	Completed
M_InOut	M_InOut_Post	Completed
M_Inventory	M_Inventory_Post	Completed. The document type 'MMI' is hardcoded because the table does not have a C_DocType_ID column.
M_Movement	M_Movement_Post	Completed. The document type 'MMM' is hardcoded because the table does not have a C_DocType_ID column.
M_Production	M_Production_Run MA_WorkEffort_Validate	Completed. The document type 'MMP' is hardcoded because the table does not have a C_DocType_ID column.

Finally, a new Message called PeriodNotAvailable will be created to inform the user that The Period does not exist or it is not opened when he tries to process a document without a opened period available for the date.

2.4

Not to be done

Reset Account Entries

Due to the new multi-organization capabilities of the Openbravo ERP, the Reset Account Entries process must be modified to be adapted to this structure. A new combobox called Organization will be included into the process window, where only business unit or legal entity organizations will be displayed. To ensure it, this field will use the previously defined AD_OrgType_BU_LE validation rule.

This process will delete the accounting records of documents for the selected organization and for all organizations that depend on it. So, the FACT_ACCT_RESET procedure, which is the responsible of this process, will be modified accordingly. As it was explained into a previous chapter, to delete also the documents of the child organizations, it will be used the AD_ISORGINCLUDED function. Finally, if the user does not select an organization, the process will be executed over every organization, i.e, it will be like if the user selects the * Organization.

A new PL function called AD_ORG_GETCALENDAROWNER will be created. This function will receive an Organization identifier and it will return the Organization (itself or an ancestor) which has the associated calendar (remember that all the organizations that belong to the same legal entity must have an unique calendar). This function will be used by the Fact_Acct_Reset procedure. Here is the source code of AD_ORG_GETCALENDAROWNER:

CREATE OR REPLACE
FUNCTION AD_ORG_GETCALENDAROWNER (p_organization IN VARCHAR2) RETURN VARCHAR2 AS

 v_calendar_id AD_Org.C_Calendar_ID%TYPE;  
 v_parent_id ad_treenode.parent_id%TYPE;
 v_node_id ad_treenode.parent_id%TYPE;

BEGIN
 SELECT C_Calendar_ID, AD_Org_ID
 INTO v_calendar_id, v_parent_id
 FROM AD_Org
 WHERE AD_Org_ID=p_organization;
  
 IF (v_calendar_id IS NOT NULL) THEN
   RETURN v_parent_id;
 ELSE
   v_node_id:=p_organization;
   WHILE ( v_parent_id <> '0' AND v_node_id <> '0' ) LOOP      
     SELECT parent_id
     INTO v_parent_id
     FROM ad_treenode t
     WHERE node_id=v_node_id
     AND EXISTS (SELECT 1 
                 FROM ad_tree, ad_org
                 WHERE ad_tree.ad_client_id = ad_org.ad_client_id
                 AND ad_tree.ad_client_id=t.ad_client_id
                 AND ad_tree.treetype='OO'
                 AND t.ad_tree_id=ad_tree.ad_tree_id
                 );

     SELECT C_Calendar_ID, AD_Org_ID
     INTO v_calendar_id, v_parent_id
     FROM AD_Org
     WHERE AD_Org_ID=v_parent_id;
     
     IF (v_calendar_id IS NOT NULL) THEN
       RETURN v_parent_id;
     END IF;
     
     v_node_id:=v_parent_id;
  END LOOP;
END IF;
RETURN NULL;
END AD_ORG_GETCALENDAROWNER;

Redesign the accounting process

Due to the new multi-organization structure created within this project, the accounting process must be adapted to it.

The accounting document constructors will receive the Organization ID as a parameter. Here is the list of the modified constructors:

src/org/openbravo/erpCommon/ad_forms/DocCash.java
src/org/openbravo/erpCommon/ad_forms/DocDPManagement.java
src/org/openbravo/erpCommon/ad_forms/DocMovement.java
src/org/openbravo/erpCommon/ad_forms/DocInOut.java
src/org/openbravo/erpCommon/ad_forms/DocGLJournal.java
src/org/openbravo/erpCommon/ad_forms/DocInventory.java
src/org/openbravo/erpCommon/ad_forms/DocOrder.java
src/org/openbravo/erpCommon/ad_forms/DocPayment.java
src/org/openbravo/erpCommon/ad_forms/DocInvoice.java
src/org/openbravo/erpCommon/ad_forms/DocBank.java
src/org/openbravo/erpCommon/ad_forms/DocAmortization.java

The AcctSchema class should also take into account the organization when retrieving the array of accounting schemas. So the getAcctSchemaArray() and the getAcctSchemaList() methods should include the Organization ID as a parameter. As a consequence, the selectAcctSchemas SQL query inside the AcctSchema_data.xsql file should be rewritten completely to include the Organization. Here is the content of the SQL query:

SELECT distinct ad_org_acctschema.C_AcctSchema_ID
FROM ad_org_acctschema, c_acctschema 
WHERE ad_org_acctschema.c_acctschema_id = c_acctschema.c_acctschema_id
and ad_org_acctschema.AD_Client_ID=?
and ad_isorgincluded(ad_org_acctschema.AD_org_ID,?,ad_org_acctschema.AD_Client_ID) <> -1
and c_acctschema.isactive = 'Y'
and ad_org_acctschema.isactive = 'Y'

The src/org/openbravo/erpCommon/ad_forms/AcctServer.java, which is the responsible of the accounting process, will be changed to be adapted to the new constructor definition of the accounting documents. For example, instead of using the old DocAmortization (AD_Client_ID, connectionProvider) constructor, we will use the new one: new DocAmortization (AD_Client_ID, AD_Org_ID, connectionProvider). It will also be necessary to modify the get() method to include the Organization ID as a parameter.

Finally, the src/org/openbravo/erpCommon/ad_actionButton/Posted.java and the src/org/openbravo/erpCommon/ad_background/PeriodicAcctServer.java classes will be changed to use the new AcctServer.get() method.

Last changes

The design of the multiorg project has been changed recently. There have been two major modifications:

The first one is that now every organization where transactions are possible can have more than one ancestor (including itself) that is a legal entity.
A new flag, called IsReady has been created to identify if an organization is ready for usage or not.

Legal entities modification

To allow the new legal entity structure, the AD_ORGTYPE_ISTRANS_ALLOWED must be modified to ensure it. Before this modification, this function checked that just one legal entity was presented into the every branch.

A point to also note is that now the organization tree could have organizations marked as not ready, so this function must ignore these organizations when checking the tree.

IsReady flag

Adding the IsReady flag involves some changes into the multiorg project. There are some business rules that the application has to fulfilled now:

A ready organization can not be moved into the organization's tree.
A not ready organization can not have transactions allowed.
A new process will be created to mark an organization as ready. This process must check that every business rule for the organization is fulfilled before marking it as ready.

So, to avoid moving a ready organization into the organization's tree, the AD_TREENODE_TRG will be modified to check if the moved organization is a ready organization or not.

 
 IF (UPDATING AND v_TreeType='OO' AND (:old.parent_id <> :new.parent_id)) THEN
  SELECT AD_Org.IsReady
  INTO v_IsReady
  FROM AD_Org
  WHERE AD_Org.AD_Org_ID=:new.NODE_ID;
 
  IF (v_IsReady='Y') THEN
   RAISE_APPLICATION_ERROR(-20543,'A ready organization can not be moved into the tree structure');
  END IF;
 
 END IF;

The new IsReady flag must be taken into account before processing any transaction document; so it will be necessary to add a check into the 1.3 processes.

Moreover, the validation rules for the Windows' Organization field should be modified for just including ready organizations, specially into the transaction's windows.

Finally, the AD_Org_Ready procedure will be the responsible of checking if the selected organization fulfills every requirement. The procedure will have a Cascade parameter to make also ready the child organizations. Here is the source code of the procedure:

 
 CREATE OR REPLACE PROCEDURE AD_ORG_READY(p_pinstance_id IN VARCHAR2) 
 
 AS
    -- Logistice
   v_ResultStr VARCHAR2(2000):='';
   v_Message VARCHAR2(2000):='';
   v_Result NUMBER:=1; -- 0=failure
   v_Record_ID VARCHAR2(32);
   v_IsRecursive AD_Org.IsActive%TYPE:='N';
  
 
   v_num NUMBER; 
   TYPE RECORD IS REF CURSOR;
   Cur_Parameter RECORD;
 BEGIN
   DBMS_OUTPUT.PUT_LINE('Updating PInstance - Processing ' || p_PInstance_ID) ;
   v_ResultStr:='PInstanceNotFound';
   AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'Y', NULL, NULL) ;
 BEGIN
     -- Get Parameters
     v_ResultStr:='ReadingParameters';
     FOR Cur_Parameter IN
       (SELECT i.Record_ID, i.AD_User_ID, p.ParameterName, p.P_String, p.P_Number, p.P_Date
       FROM AD_PInstance i
       LEFT JOIN AD_PInstance_Para p
         ON i.AD_PInstance_ID=p.AD_PInstance_ID
       WHERE i.AD_PInstance_ID=p_PInstance_ID
       ORDER BY p.SeqNo
       )
     LOOP
       v_Record_ID:=Cur_Parameter.Record_ID;
       IF(Cur_Parameter.ParameterName='Cascade') THEN
         v_IsRecursive:=Cur_Parameter.P_String;
         DBMS_OUTPUT.PUT_LINE('  Cascade=' || v_IsRecursive) ;
       ELSE
         DBMS_OUTPUT.PUT_LINE('*** Unknown Parameter=' || Cur_Parameter.ParameterName) ;
       END IF;
     END LOOP; -- Get Parameter
 
     v_ResultStr:='Updating Ready Flag';
     IF (v_IsRecursive='Y') THEN
       UPDATE AD_ORG
       SET ISREADY='Y'
       WHERE AD_ISORGINCLUDED(ad_org.ad_org_id, v_Record_ID, ad_org.ad_client_id)<>-1
       AND IsReady='N';      
     ELSE
       UPDATE AD_ORG
       SET ISREADY='Y'
       WHERE AD_ORG_ID=v_Record_ID;
     END IF;
  
     v_ResultStr:='Checking Ready';
     SELECT AD_ORG_CHK_READY(v_Record_ID) INTO v_num FROM DUAL;
     IF (v_num = -1) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20545, 'Every ancestor of the organization must be a ready organization');
     END IF;
    
     v_ResultStr:='Checking LE';
     SELECT AD_ORGTYPE_ISTRANS_ALLOWED() INTO v_num FROM DUAL;
     IF (v_num = -1) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20540, 'Every organization where transactions are possible must belong (at least) to a legal entity organization.');
     END IF;
    
     v_ResultStr:='Checking Schemas';
     SELECT AD_ORG_CHK_SCHEMAS() INTO v_num FROM DUAL;
     IF (v_num = -1) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20542, 'Every legal entity must have itself or an ancestor at least an accounting schema attached to it');
     END IF;
    
     v_ResultStr:='Checking Calendar';
     SELECT AD_ORG_CHK_CALENDAR() INTO v_num FROM DUAL;
     IF (v_num = -3) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20537, 'Every legal entity must have itself or an ancestor at least a calendar attached to it');
     ELSIF (v_num = -2) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20538, 'All the organizations that belong to the same legal entity must have a unique calendar');
     ELSIF (v_num = -1) THEN
       ROLLBACK;
       v_Result:=0;
       RAISE_APPLICATION_ERROR(-20539, 'The calendar associated to a legal entity must be unique. So, an organization that is a legal entity must have assigned itself or any ancestor the same calendar');
     END IF;
    
     IF(p_PInstance_ID IS NOT NULL) THEN
       --  Update AD_PInstance
       DBMS_OUTPUT.PUT_LINE('Updating PInstance - Finished ' || v_Message) ;
       AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', v_Result, v_Message) ;
     ELSE
       DBMS_OUTPUT.PUT_LINE('Finished ' || v_Message) ;
     END IF;
 
 EXCEPTION
 WHEN OTHERS THEN
   v_ResultStr:= '@ERROR=' || SQLERRM;
   DBMS_OUTPUT.PUT_LINE(v_ResultStr) ;
   ROLLBACK;
   IF(p_PInstance_ID IS NOT NULL) THEN
     AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
   END IF;
 END;
 END AD_ORG_READY;

Retrieved from "http://wiki.openbravo.com/wiki/Projects:EnhancedMulti-organizationSupport/Technical_Documentation"

This page has been accessed 10,841 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.

Toolbox

PDF Books

Search

Participate

Projects:EnhancedMulti-organizationSupport/Technical Documentation

Contents

Enhanced Multi-organization Support - Technical Documentation

Overview

Current status

Technical design

Accounting

1.1

1.2

Points to take into account

1.3

1.5

1.5b

1.6

1.7

1.8

Points to take into account

1.9

Period Control

2.1

2.2

AD_ORG_TRG

C_PERIOD_TRG

Organization window

Open/Close Period Control Process

2.3

2.4

Reset Account Entries

Redesign the accounting process

Last changes

Legal entities modification

IsReady flag