Wednesday, May 31, 2017

Copying data from one legal entity to another for a group or setup

In many multi company implementation, the data setup becomes a time consuming activity especially when the setup needs to be copied from a master legal entity. A typical example may be customer group or delivery terms that are common across all legal entities. If you have a lot of legal entities, DIXF will be an ideal method but it is time consuming. For consultants, who need to speed up things and who understands the basic table structure, they can use the below code (in form of a runnable job) to copy data between legal entities for a table. You would need to select the table and use the parameter for providing from and to legal entities, Select the "Insert and Update" checkbox if you need to update the records in the target legal entity. 

This script will only work for tables having a clustered index (unique non recid index). It will find the fields comprising unique index and select the target table using those fields. If the record is not found then it will create. If the record is found and the parameter "Insert and Update" is set then it will just update the record.



static void CopyDataBetweenLegalEntities(Args _args)
{
    DictTable       dt; 
    DictIndex       di; 
    DictField       field; 
    int             i; 
    Query           queryFrom, queryTo;
    QueryRun        queryRunFrom, queryRunTo;
    QueryBuildDataSource qbr1, qbr2;
    QueryBuildRange    queryRange;
    Common          fromCommon, toCommon;
    Dialog          dialog;
    DialogField     dfFromCompany, dfToCompany, dfTableName, dfInsertUpdate;
    str             tableName;
    CompanyId       fromCompany, toCompany;
    boolean         insertUpdate;
    
    dialog = new Dialog("Enter company and table information");
    dfTableName = dialog.addfield(extendedTypeStr(TableName));
    dfFromCompany = dialog.addField(extendedTypeStr(CompanyId), "From Company");
    dfToCompany   = dialog.addField(extendedTypeStr(CompanyId),"To Company");
    dfInsertUpdate = dialog.addField(extendedTypeStr(NoYesId), "Insert and update");
    
    if(dialog.run())
    {
        tableName = dfTableName.value();
        fromCompany = dfFromCompany.value();
        toCompany   = dfToCompany.value();
        insertUpdate = dfInsertUpdate.value();
    }
    dt = new DictTable( tableName2id(tableName));
 
    if (dt) 
    { 
        di = dt.indexObject(dt.indexUnique()); 
        if (di) 
        { 
            info( di.name()); //verify for not RecId index
        } 
        else
        {
            throw error("No unique index found on the table");
        }
        
        queryFrom = new Query();
        qbr1 = queryFrom.addDataSource(dt.id());
                
        changeCompany(fromCompany)
        {
            queryRunFrom = new QueryRun(queryFrom);
            while( queryRunFrom.next())
            {
                fromCommon = queryRunFrom.getNo(1);
                
                queryTo = new Query();
                qbr2 = queryTo.addDataSource(dt.id());
            
                for (i=1; i <= di.numberOfFields(); i++) 
                {
                    field = new DictField(dt.id(), di.field(i)); 
                    queryRange = qbr2.addRange(field.id());                    
                    queryRange.value(queryValue(fromCommon.(field.id())));
                    
                }
                changeCompany(toCompany)
                {
                    queryRunTo = new QueryRun(queryTo);
                    if(!queryRunTo.next())
                    {                        
                        toCommon = dt.makeRecord();
                        toCommon.clear();
                        toCommon.data(fromCommon);                        
                        toCommon.company(toCompany);
                        toCommon.insert();                       
                        
                    }
                    else if(insertUpdate)
                    {
                        ttsBegin;
                        toCommon = queryRunTo.getNo(1);
                        toCommon.selectForUpdate(true);
                        buf2Buf(fromCommon, toCommon);                        
                        toCommon.update();
                        ttsCommit;
                            
                    }
                }
                
            }
            
        }
        
        
 
    }

}


No comments:

Post a Comment