Ariba JavaScript: Export Hierarchy Objects In CSV

Ariba includes in its meta model a number of hierarchical objects (ie: commodity, department, region …) which handles a hierarchy with a parent/child mechanism, this is really nice as it allows you to have an undefined number of level in your hierarchy and not a frozen structure.

However when you talk to business people, in many situation, the hierarchical model is indeed frozen (look the unspsc with up to 6 levels, the department class to represent the organization of the customer) so once you have implemented the structure in the system you do not get the level of a given object and it’s really difficult to represent the object in its original form.

The following Javascript will write in a csv file the CommoditCode object keeping the hierarchy of all objects:

var Log                   = ariba.util.log.Log;
var AQLQuery              = ariba.base.core.aql.AQLQuery;
var AQLOptions            = ariba.base.core.aql.AQLOptions;
var AQLResultCollection   = ariba.base.core.aql.AQLResultCollection;
var BaseId                = ariba.base.core.BaseId;
var Base                  = ariba.base.core.Base;
var Partition             = ariba.base.core.Partition;
var Locale                = java.util.Locale;
var ListUtil              = ariba.util.core.ListUtil;

var File       = java.io.File;
var FileUtil   = ariba.util.core.FileUtil;
var FileWriter = java.io.FileWriter;

// start from the main root of the tree
var queryText = 
  "select cc from ariba.basic.core.CommodityCode cc include inactive " +
  "where cc.Parent Is Null";

var query = AQLQuery.parseQuery(queryText);

var option = new AQLOptions(Partition.None);
option.setUserLocale(Locale.ENGLISH);
option.setUserPartition(Partition.None);

// define the file where results will be output
var sourceFile = 
  new File("config/variants/Plain/partitions/None/data/QueryReport/cc_super_cool.csv");
FileUtil.createDirsForFile(sourceFile);
var fileOut = new FileWriter(sourceFile);

// run the query and loop through results
var result = Base.getService().executeQuery(query, option);
while (result.next()) {
    var cc = Base.getSession().objectFromId(result.getObject(0));
    var parentList = ListUtil.list();
    writeCC(parentList, cc);
}
result.close();
fileOut.close();

/*
 * write a cc object : one cc line is represented with all its hierarchy
 * recursive function to loop through all objects which have child
 */
function writeCC(parentList, cc) {
    // 1.write active status and hierarchy path
    fileOut.write(cc.getActive() + "," + cc.getHierarchyPath() + ",");

    // 2.write parent list structure
    writeParentList(parentList);

    // 3.write cc info – this can be changed depending the object
    fileOut.write(cc.getDomain() + "," + cc.getUniqueName() + "," + cc.getName() + "\n");

    // 4.call on new list
    var childccList = getChildccFor (cc);
    if (!childccList.isEmpty()) {
        var newList = ListUtil.list();
        newList = ListUtil.copyList(parentList);
        newList.add(cc);
        for (var i=0; i<childccList.size(); i++) {
            var childcc = childccList.get(i);
            writeCC(newList, childcc);
        }
    }
}

/*
 * write the hierarchy
 */
function writeParentList (list) {
    for (var i=0; i<list.size(); i++) {
        var cc = list.get(i);
        fileOut.write(cc.getDomain() + "," + cc.getUniqueName() + "," + cc.getName() + ",");
    }
}

/*
 * search and return the child object for a given parent
 */
function getChildccFor (parentcc) {

    var queryChildText = 
      "select cc from ariba.basic.core.CommodityCode cc include inactive " +
      "where cc.Parent = BaseId('" + parentcc.getBaseId().toDBString() + "')";
    var queryChild = AQLQuery.parseQuery(queryChildText);

    var optionChild = new AQLOptions(Partition.None);
    optionChild.setUserLocale(Locale.ENGLISH);
    optionChild.setUserPartition(Partition.None);

    var childList = ListUtil.list();
    var resultChild = Base.getService().executeQuery(queryChild, optionChild);
    while (resultChild.next()) {
        var cc = Base.getSession().objectFromId(resultChild.getObject(0));
        childList.add(cc);
    }
    resultChild.close();
    return childList;
}

Note : the query written is a bit lazy and it would be more effective to query the field you are interested to, but this is for demo purpose.
you could also use the Formatting API from Ariba (Fmt class) rather than using the + concatenate of String objects.

You can then import the csv file in xl and run the filtering from there:

Tags: Ariba
Comments 11 | Views: 5633 | Read more...

CSV Edit 1.1 is out

Hey,

well it is not really up to date as the csv edit 1.1 has been released 2 weeks ago but I wanted to make this announcement and present some of the new functionnality.

So, I created CSVEdit plugin in January and thanks to the Eclipse Marketplace, the plugin has met quite a success (it has been quickly in the top 100 plugins downloads) and I received some positive feedback right after the plugin was published.

Some people contacted me by directly by email or thought the issue list of the project - I host the project on google code and the issue managment, although not perfect is quite convenient, so it is an easy way to contact the team (http://code.google.com/p/csvedit/downloads/list)

I talked about the team is because J. Andrés Pizarro joined me to work on the plugin. he needs this plugin for his daily work and hopefully will bring a lot of new functionnality in the plugin. He already contributed in his first feature for the source view (I talk about it just after)

CSV Edit 1.1 has come with the following features :

- An extended set of preferences : you can select to open the csv file as source or table view by default; manage your csv file format with more settings

- Source code editing with region markup, means that even in the source view, you can differentiate the column

- Column management : you can create and delete the column directly from the table view

 

I've rebuilt the page of the plugin feature on this website to document in detail each feature.

 

I hope you will continue working with the plugin and do not hesitate to send me your feedback

 

Thanks,

Fred

Tags: Java
Comments 1 | Views: 2494 | Read more...

CSV Editor Eclipse Plugin

The first version of the csv plugin editor has been released and is available at http://code.google.com/p/csvedit/downloads/list

If you are dealing with csv files in your Eclipse project, you might want to take a look at this plugin which will make it easy .
Among the feature you will find:
- view csv file in source editor or table editor
- easy search in table view
- customize view with filtering column
- sorting column
- easily insert/delete rows

Tags: Java, Ariba
Comments 5 | Views: 6946 | Read more...

Welcome

Welcome in FHSolution,

This is the first post as the site is launched. I'll speak about Ariba and Java technologies, try to share some good tips regarding the Ariba implementation that I gathered with my experience, and see how it evolves.

Thanks,

Comments 5 | Views: 2707 | Read more...

Warning: Illegal string offset 'active' in /homepages/11/d292190974/htdocs/templates/fh_solution/html/pagination.php on line 94

Warning: Illegal string offset 'active' in /homepages/11/d292190974/htdocs/templates/fh_solution/html/pagination.php on line 100

Warning: Illegal string offset 'active' in /homepages/11/d292190974/htdocs/templates/fh_solution/html/pagination.php on line 94

Warning: Illegal string offset 'active' in /homepages/11/d292190974/htdocs/templates/fh_solution/html/pagination.php on line 100