Setup Custom Account Combination using User List

Accounting Dimension is a powerful tool for analytic accounting. Out of the box, ADempiere has provided many Accounting Dimensions, i.e., Sales Region, Product, Partner, Campaign, Project, etc. These dimension will account into Accounting Combination (i.e., HQ-12110-_-_-_-_) which will be used when doing account posting. Each dimension will allow us to analyze our business in different angle

What if we want to add new unique Dimension to our Account Combination?

To answer this question, ADempiere prepared 4 additional fields (already in Fact_Acct table). You can add new Dimension using window Accounting Schema > Accounting Schema Element

1) Type User Element (2 fields)
  • UserElement1_ID (User Element 1), UserElement2_ID (User Element 2)
  • User Element will allow you to define any Table’s Column to be used as a new dimension.
2) Type User List (2 fields)
  • User1_ID (User List 1), User2_ID (User List 2)
  • User List will allow you to use Account Code from Account Element

As for User Element, it is quite simple and straight forward and is not the intention of this tutorial. As such, in this tutorial, we will cover only of type User List.

Follow below steps to create a new dimension of type User List,

Create new Tree

  • As Client, open Tree window and add new record.
Name             | Type|Area    | All node
Cost Center Tree | Element Vlue | True

Create new Account Element

  • As Client, open Account Element and add new record.
Org | Name        | Type    | Tree
*   | Cost Center | Account | Cost Center Tree
  • Add our sample Element Value, as following,
Search Key | Name 
000        | No Cost Centre
010        | Phang Nga Office
020        | Khoa Lak Office
030        | Phuket Sales Office
040        | Samui Island Sales Office
050        | Hui Hin Sales Office
999        | TOTAL COST CENTRE (Summary Level)


Create new Accounting Schema Element (new Dimension)

  • We will be using the new Account Element we have created.
  • As Client, open Accounting Schema > Accounting Schema Element, add new record
Name        | Type        | Element
Cost Center | User List 1 | Cost Center
  • That’s all. We have enalbed User List 1 as new dimension and it will follow us in every account posting. Next we only want to make it more meaningful by changing the label.

Change Label of “User List 1” to “Cost Center”

  • Re-login as System, open Element window and search for “User List 1”
  • Change name to “Cost Center”

Re-login again, now, in the every document window, i.e., Sales Order, Invoice, etc…, you will see new field “Cost Center” with the list all cost center we have filled in previously.


Summary of characters when we define the new Cost Center using User List

  • New dimension field will appear in every Document window.
  • The new Cost Center will be carried over automatically, i.e., when Invoice is created from this Sales Order.
  • When document is posted, the new Cost Center will be in the Fact Account table.


  • This new Cost Center will also available in Account Info for analysis,


ADempiere Integration With Alfresco

Why Integrate ECM and ERP?

A little bit about ECM first.

  • ECM is the best software for managing unstructured content like, documents, videos, web pages, etc.
  • ECM (like Alfresco) is not just a software to store documents, ECM provides hell lots of service, such as better security, version control, audit history, transformation, publishing, collaboration, record management, business rules for content, workflow (Alfresco bundle with Activiti), and many more.
  • In other words, ECM can manage entire life cycle of a document, from Capture -> Manage -> Deliver -> Retain -> Archive.
  • In the real world scenarios, we can’t really separate these two, each of the application processes in ERP, there are often textual documents supporting the purely transactional data. Product data sheets, customer correspondence, quality reports, invoices, etc.

Based on my previous life experience in commercial world, software like SAP, just to make it able to save files into an ECM system (i.e., Documentum or FileNet), as they are closed system, business have to pay a lot of money for the stupid plugin license.

Here, we see how to accomplish the same thing, EASILY and OPENLY.

See Appendix on some facts about the two integration.

How this demo works?

Adempiere already have 2 options for saving attachment files. 1) Save as binary in database 2) Save in file server. This plugin will provide the third option 3) Save to ECM (CMIS)

The usage of the new attachment will be mostly identical to the existing. But when saving the document, it will be saved as a new object of type “Adempiere Attachment” into a dropping point folder in Alfresco, and link itself back with the ObjectID rather than file path. This make it a loosely couple integration.

But as Alfresco ECM is smart, once the document is in there, we can set any business rule to automate document as we like. In this simple example, we will be creating a rule in dropping point folder, so that it automate the organization of folder structure.

  1. In ADempeire, i.e., Sales Order, user attach some files
  2. Files saved into the dropping point folder in Alfresco
  3. Auto create relevant folders according to the document’s properties
  4. Move it self there for better organization of files.
Image:Note.gif Note:

  • The plugin is created and test with Alfresco. But as it is using CMIS protocol, which is and open standard for all big name ECM, I assume it would work with others too, (i.e., Alfresco, Documentum, Filenet, OpenText, etc.)



1) Install Alfresco Community

2) Test that the CMIS service using OpenCMIS Workbench

  • Noted that, CMIS services will be provided through CMIS URL = http://localhost:8080/alfresco/cmisatom
  • Download “OpenCMIS Workbench” from Apache Chemistry’s download page.
  • Just extract the zip file and run workbench.bat. Test the connection with CMIS URL, you should be able to browse through Alfresco Repository.
  • Open Query dialog, and you can test CMIS query, like,
SELECT * FROM cmis:folder
  • Note: Beware about SOLR Certification Expiration. Make sure you have the SOLR certificate renewed before proceed, otherwise you may face problem when do CMIS query.

3) Install adempiereCMIS plugin for Adempiere

  • Extract
  • Install OpenCMIS libraries -> Copy OpenCMIS libraries from folder “lib” into “ADEMPIERE_HOME/lib”. (You can always find the latest libraries from Apache OpenCMIS website).
  • Install new plugin libraries -> Copy our plugin library, “adCMIS.jar”, into ADEMPIERE_HOME/packages/adCMIS/lib folder.
  • Run a migration script, located in folder “migration” against your database. This will create new fields for CMIS properties in Client window.
  • RUN_silentsetup.bat (.sh), to make sure that new libraries are included in Adempiere packages.
  • Login to ADempiere as SuperUser, open “Client” window and configure the connection parameter as following, we are telling Adempiere how to hook with Alfresco,
Store Attachments On CMIS: Yes
CMIS URL: http://localhost:8080/alfresco/cmisatom
CMIS Viewer URL: http://localhost:8080/share/page/document-details?nodeRef=
CMIS Root Folder: AdempiereCMIS
CMIS User: admin
CMIS Password: admin
  • Now, from Adempiere side, it is now ready.

4) New object type, “ADempiere Attachment” in Alfresco

  • Copy the 4 xml files from folder “alfresco_extension”, into folder “ALFRESCO_HOME\tomcat\shared\classes\alfresco\extension”. These XML files contains definition of new object type and its properties.
  • Restart Alfresco Tomcat. New object type will be loaded.
  • To test that new type is ready, login to Alfresco Explorer. Upon creating new document, you should see new option “Adempiere Attachment” for object type.


5) Create meeting point folder in Alfresco

  • Lets use Alfresco Share this time.
  • Login as to Alfresco Share http://localhost:8080/share. From here you will see My Dashboard view.
  • Click on “Repository” icon in the top menu, this bring up the Repository view.
  • Click on “New Folder” icon and create new folder name “ADempiereCMIS” under “Repository” folder.

6) Test attach file from Adempeire

  • Launch Adempiere and try attach files into “Sales Order” window. Click on Attachment icon, attach some files, and click OK.
  • Back and refresh Alfresco Share. Voila! Now the new files are attached and stored in Alfresco!


  • Also note on the CMIS Link button in Attachment dialog.
  • With a document selected, click the CMIS Link button and it will bring up the file with Alfresco Share viewer.


  • As the file is now in Alfresco, you can enjoy all the benefit. You can view, edit, make new versions, start workflow, move files around, etc. The CMIS Link Button will always open up the right file with the latest version!

Let’s do some more fancy stuff

The true benefit of ECM is about document automation. I won’t go into too much detail on ECM features, it is a big topic.

Now, let’s say, in our case, after document is loaded into ECM, we want to organize it.

As for this simple demonstration, we already have 2 additional properties from Adempeire in each document, “TableName” and “RecordID”. As a document enter Alfresco, we want to automatically create document’s sub folders and put it there.

(In future, we can have more properties from Adempiere like, Document Type, Document Number, Search Key, Document Owner etc. Then we can do a lot more automation based on those values).

1) Setup Business Rules to AdempiereCMIS Folder

  • Login as admin to Alfresco Explorer, http://localhost:8080/alfresco
  • We will be assigning a Rule for AdempiereCMIS folder, so that as a document enter it, something will happen.
  • First we want to prepare a java script that will do part of the work.
    • Browse to “Data Dictionary” -> “Scripts” folder.
    • Click “Add content” and choose file = “adempiere.js” (from rule_script folder in, read the code to see what it try to do)
    • Set General Properties, and finish it.
      • Name = “adempiere.js”
      • Type = “Content”
      • Content Type = “Java Script”
  • Now start applying rule, let’s browse to “AdempeireCMIS” folder we created earlier.
  • Click “More Actions” -> “Manage Content Rules”
  • On Create Rule Wizard Page
    • Step One – Select Conditions
      • Select condition = “Content of type or sub-type”
      • Set value and add = “Adempiere Attachment”, ok then next.
    • Step Two – Select Actions
      • Select action = “Execute script”
      • Set value and add = “adempiere.js”, OK then Next.
    • Step Three – Enter Details
      • Type = “Item are created or enter this folder”
      • Title = “Organize Adempeire Attachment”
    • Finish!

2) See the result

  • In ADempiere, back to Sales Order window and delete all old attachments.
  • Attach new documents again.
  • Go to Alfresco Share, this time you will see that documents are organized according to the Rule! Voila!


  • The magic lies in the Rule and the Script we have setup for ADempiereCMIS folder.
// Create first level folder (TableName)
var subfolder1 = space.childByNamePath(["ad:tablename"]);
if ( subfolder1== null)
   subfolder1 = space.createFolder(["ad:tablename"]);

// Move itself to first level folder

// Create second level folder (RecordID)
var subfolder2 = subfolder1.childByNamePath(["ad:recordid"]);
if ( subfolder2 == null)
   subfolder2 = subfolder1.createFolder(["ad:recordid"]);

// Move itself to second level folder

My Take

To me, the integration between the two is about bridging between the unstructured world of ECM (unstructured, non-linear, collaboratively flexible business process) with the structured world of ERP (structured, sophisticated, high precision).

What we have done so far was just a small demonstration. We can do a lot more in this area. And Adempiere will really be a killing sofware.

For example,
  • Complex approval process, can be done in Alfresco, backed by Activiti workflow engine which is far more advanced than what can be done with Adempiere workflow. The result of approval can create a new Document in Adempiere.
  • Marketing dept, use Alfresco workflow to collaborate in designing of a new product and its required parts. Once finished and approved by workflow, auto create new Product and BOM and attach itself as image for easy reference in Adempiere.
  • Important accounting documents, reports and etc. in Adempiere, once freezed, auto archive as PDF and store in Alfresco in a well organized ways (i.e., by Year/Month/DocType/DocumentNo/Creator). Non ERP users can access based on their own rights, using powerful full-text/index search capability of Alfresco for auditing purposes.
  • Etc…

What’s Next?

Here are my random thought about features to be included in Adempiere,

  • Attachment: Ability to select folder in Alfresco when do the upload.
  • Attachment: Ability to select version of document (view and upload) within Attachment window.
  • Archive: And CMIS option for archive.
  • Auto archive document (i.e., Sales Order form) into Alfresco , when document is completed.
  • Image field: Ability to link to pictures in Alfresco .
  • Add more standard properties in ADempiere Document so that it will be useful when create rules.
  • In the opposite way, from Alfresco , link the file back to attach with Adempiere Document.
  • Workflow node in Alfresco to integrate with Adempiere.
  • Workflow node in Adempiere, i.e., Complete, to trigger new workflow in Alfresco .
  • Use LDAP to authenticate both side (not just using an admin to connect with Alfresco).

Have Suggestions?

If you have real business case that see the benefit of ECM + ERP, please share it here. We can use it as input to develop more features.


Key findings (AIIM’s Report – Connecting ERP and ECM, Measuring the Benefit)

  • 27% of respondents who ranked themselves at a mature Level 4 or 5 for the completeness of their ERP installation only reached maturity Level 1 or 2 for their ECM practices.
  • The biggest business benefit given by our respondents for linking ERP and ECM is the productivity gained by linking document and process workflows, followed by improved customer service and then knowledge sharing.
  • Accounts payable is the best performing integration, followed by HR, Project Management and CRM. Invoice Number, Customer Number and Purchase Order Number are the most popular process integration links between ERP and ECM, with Contract Number as an important document integration link.
  • 68% considered that linking ERP and ECM gave a better or much better return on investment than other IT integration projects.
  • The biggest issue encountered by users was deciding which systems or portals to consolidate around, followed by the difficulty of convincing process owners. Mismatch of metadata standards came next.
  • Amongst non-users, technical complexity and maintenance of links were the biggest concerns, followed by the political issue that ERP and ECM do not have the same project managers or sponsors. Increased licensing cost for wider client rollout was also an issue, as was divergence of ECM systems across the enterprise.
  • 31% of respondents are using standard, vendor-supplied connectors to integrate ERP and ECM systems, but a total of 38% are using custom developed links – 24% in-house developed and 14% custom-developed by the vendor.
  • 53% have a portal to provide single point of access to ERP and ECM content or plan to have one in the next 12 months.
  • An ERP portal linking to ECM content (27%) is much more popular than an ECM portal picking up ERP content (7%), although 24% have chosen SharePoint as their portal, and may consider it to also be their ECM system.
  • Within our sample, 47% have business process links and 34% have document access links, but only 12% can declare a record within ERP for migration to, or management by, a dedicated records management system and only 5% are able to implement a single-point legal hold across records in both ERP and ECM.
  • SAP is the primary ERP supplier for 48% of our respondents, with 24% Oracle and 12% Microsoft.
  • As regards ECM supplier, 23% are using SharePoint, with EMC/Documentum, Open Text, IBM/Filenet and Oracle/Stellent all taking around a 10-12% share.
  • Asked “How would you feel about sourcing your ERP and ECM from the same supplier?” 13% already source from the same supplier and 42% agree that it could have benefits and they might consider it. 20% are committed to their existing supplier and 24% prefer the flexibility of different suppliers.
  • In terms of more specific content management elements, ECM suppliers are preferred in most cases, although BPM is the most likely to be sourced from the ERP supplier, and email management from a best-of-breed supplier.

A Step by Step Guide to Data Migration with Talend ETL


In ERP Implementation, one of the major activity is Data Migration. This task is undoubtedly, tedious and time consuming, yet very important one. I have been exploring for tools to help in this boring task since I start working with ADempiere. For instance, Excel, ADempiere own Data Import Tools, or even with PackOut.

While ADempiere own import module seem to be the best as it can directly call the underlining API to make a valid object, it is not very flexible. And yes, to make it fit, we also provide templates and hoping the customers will fill them correctly. But in reality, most data from customers are far from usable. And most of the time, we need to rewrite the import process by ourselves in order to fit with the submitted data.

I have been thinking about tools like ETL in order to ease the process. Though the migration between DB to DB is easy, but without ability to call ADempiere’s API to importing data is not very useful.

Until very recently, I just found that Talend has been used for ADempiere with ability to call ADempiere API for sometime Talend Open Studio (gosh!, where have I been). But Late is better than never. So far, it has been the best tools to work with ADempiere in my opinion. And in this article, I will try to help explaining how to use Talend with Adempiere in a bit more detail.

Why Talend ETL and ADempiere?

The fantastic thing about Talend that make it very helpful to use with ADempiere, is ability to write custom components in java, i.e., tAdempiereConnection and tAdempiereOutput which you will see in a moment.

The code generator (java) nature of Talend does impart certain capabilities such as the ability to easily integrate into other system, ADempeire is one of them. And once you know how it works, it is quite easy to write one of you own to do just about any task.

Following picture depict the overview of how it will work for you,


How it works?

  1. Initialize database connection
  2. Login to Adempiere using tAdempiereConnection component to receive ADempiere Context (i.e., Env.getCtx()). After this, we are considered Logged In which will also trigger the main import sub process.
  3. This component will will initialize Delimited File (point to local csv file), read the file for schema and data (row by row) to to tMap component
  4. tMap component (the following picture), will map the input schema to the output schema. We can use expression to transform/filter/lookup data before sending to out.
  5. Optionally, provide lookup to other table, for instance, we have Org Name and we want to lookup for AD_Org_ID.
  6. Output data will be mapped to Adempiere’s Model using tAdempiereOutput component. As you can guess, data will be created using Adempiere’s API (e.g.,
  7. And just for information, the tLogRow_1 will display the raw output data.

All the task can be done through drag and drop in the Designer tab. But no magic hidden, you can always refer to the generated code in the Code tab and see exactly what is happening programmatically.


Now, if you are convinced that Talend can help your project, we can move on (if not, and you have better solution, please let me know too. :).

In this tutorial, I will not go into details of why I am doing the way I am doing, just provide you with some examples related to Adempiere.

Before continue reading, I highly recommend you to read the following list of articles first.

Above articles are critical for you to know how it works and how to debug if face problem.

Setup Talend for ADempiere

Given that you have the Talend Open Studio (TOS) installed and have some hands on experience with it, we can proceed.

Note: You can also refer to Talend Open Studio as the original document.

  • Provide Adempiere core libraries for Talend
    • Copy ADEMPIERE_HOME/lib/Adempiere.jar and ADEMPIERE_HOME/lib/CCTools.jar to TOS_HOME/lib/java
Note: For customization.jar or other additional library, you can put it here as well. But be remembered that we will have to tell about it in the next (Optional) step.
  • Install Adempiere Talend’s components
    • Download the Adempiere components
    • Extract components (tAdempiereConnection, tAdempiereOutput, tAdempiereProcess) and put it in any directory, i.e., TOS_HOME/custom_components
    • Point TOS to the directory containing the components.
      • Open TOS, Menu > Window > Preferences, and then, > Talend > Components
      • Update field User component folder: to your custom_component folder.
      • Apply the change, the new component will be loaded in the right palette. Now, if you create new job, you should see Adempiere components in the Palette area under Business > Adempiere
  • (Optional), if you also want to installed your customization.jar, make sure your new Adempiere components knows about it.
    • Change to Designer Perspective.
    • With TOS, Menu > Window > Perspectives > Component’s Designer
    • You should see 3 Adempiere components here, each component will have a config file (tAdempiere…._java.xml).
  • Add new line for your customiizatoin.jar in <CODEGENERATION> part.
			<IMPORT NAME="Adempiere" MODULE="Adempiere.jar" REQUIRED="true" />
			<IMPORT NAME="Adempiere Libraries" MODULE="CCTools.jar" REQUIRED="true" />
			<IMPORT NAME="Customization" MODULE="customization.jar" REQUIRED="true" />

Data Migration Use Cases

Now as the environment setup is ready, and you have some knowledge about Talend ETL, now we are ready to explore how it works with Adempiere. The first case, will be a bit in detailed, but will be more conceptual as we move on.

Case 1: Simple Data Import – MBPGroup

In this case, we will learn how to import data into 1 table. I.e., Business Partner Group. Though it is a simple case, you will see how other data, in this case, Accounting tab will be created as well. And at the end, we will elaborate it a bit, by look up to other table for ID instead hard code the ID.

Source Data: c_bp_group.csv

"Search Key";Name;Description;Default;"Print Color";"Priority Base";"Confidential Info";"Price List";"Purchase Pricelist";"Discount Schema";"PO Discount Schema";"Credit Watch %";"Price Match Tolerance";Dunning
WHS;"Whole seller";;false;Black;Same;false;Standard;;;;0;0;Default


  • Create new job named MBPGroup, click finish.
    • Double click on the new job to open the canvas.
  • Create database connection to adempiere (postgres)
    • From the left panel, > Metadata > Db Connections, right click > Create Connection
    • Create new connection named adempiere, click next.
    • DB Type:’ PostgreSQL
    • Fill in all information, Login, Password, Server, Port, Database (i.e., adempiere), Schema (i.e., adempeire)
    • Click check button, it should show connection successful. Click finish.
    • Drag the newly created connection, i.e., adempeire, to the canvas.
    • Choose to create component as “tPostgresqlConnection”
  • Create adempeire connection
    • From the right palette, drag component tAdempiereConnection into canvas
    • Double click on the icon, you will see component properties in below panel. Fill in Adempiere connection as following,
  • Test Adempiere Connection
    • Connect database connection with tAdempiereConnection, right click on adempiere (db connection) > Trigger > On Subjob OK, and drag it to tAdempiereConnection_1
    • On the Run tab, click Run, if connection is valid, it should show no exception error.


Image:Note.gif Note:

  • Just like when connect Adempiere for the first time, it will show Adempiere Connection dialog.
  • If you have read the suggested tutorial, you will know that Talend is using Code Generation concept.
  • You can always see the generated java code from the Code tab of the canvas. It is important to know this as it will be the Code that you want to trace for error.
  • Create source file connector (c_bp_group.csv)
    • On the left panel, > File delimited, right click > Create file delimited
    • Create new delimited file named “c_bp_group”, click next.
    • Browse to select file c_bp_group.csv, click next.
    • This step will allow you to configure how it read the source file. I.e., check “Set heading row as column name” and click Refresh Preview


    • Click next, it will show the final schema for c_bp_group, click finish to create the file connector.
    • Drag the newly created file connector, c_bp_group, to the canvas.
    • Choose to create component as tFileInputDelimited.
  • Connect data from source data to Adempiere
    • Drag following components from palette to canvas
      • tMap this component will be used to map data between input source file and output Adempiere Model
      • tAdempiereOutput_1 this component represent Adempiere model.
      • tLogRow_1 this is optional, but will be useful to display log of data row.
    • Connect each component together (as in following picture)
      • From tAdempeireConnection_1 to c_bp_group file connector, use On Subjob OK trigger
      • From c_bp_group to tMap_1, simply connect.
      • From tMap_1 to tAdempiereOutput_1, name the output “c_bp_group”
      • From tAdempiereOutput_1 to tLogRow_1, simply connect.
  • Configure tAdempiereOutput to import data with MBPGroup model,
    • Click on tAdempiereOutput_1, it will open the component properties.
    • Set Adempiere model class name: org.compiere.model.MBPGroup
    • As we are going to insert new data (not update), leave Insert Mode checked.
  • Important step, map input data to output model
    • Double click on tMap_1 component, tMap window will appear.
    • You will notice that now we have a table schema on the left ready, but on the right only empty c_bp_group output table. We will have to define what we want to map to MBPGroup model.
    • For c_bp_group, add the column that match with ADempiere column names,
    • Map column from the left to table on the right. For simplicity, we will hardcode AD_Org_ID to 0



Image:Note.gif Note:

  • A key field is required for tAdempiereOutput component to run smoothly.
  • In Insert Mode, this field will be left blank (internally, 0 will be filled). In Update Mode, provided with Value / Name, ID can be looked up from database)
  • Make sure that Data Type match from left to right.
  • Make sure that Data Type match with the underlining Adempiere Model.
  • Click Finish to close the window. If you are asked to “Propagate the changes”, click “Yes”. This will make sure that the new c_bp_group’s schema is synchronized to tAdempiereOutput_1 and tLogRow_1.

Now we are ready to import data. Make sure that, we do not have the importing data in BPartner Group window yet. And if ready, click Run button.

You will see the data being transferred from source file to Adempiere in real time.


And as promised, currently we have hard coded the AD_Org_ID to be 0, we want to add some table look up here.

  • Add look up to AD_Org_ID, connect to database table ad_org.
    • On left panel, > Metadata > Db Connections, right click on > adempiere 0.1
    • Select “Retrieve schama, click Next, and select table ad_org
    • As we want to lookup AD_Org_ID from Name, remove other fields, except these records.
    • Click OK to finish it.
    • Now, we wan to use it as our look up table. Simple drag it to the canvas.
    • Right click from ad_org icon and connect to tMap_1
    • To lookup for AD_Org_ID, double click on tMap_1. You will see the new schema appears.
    • Drag name from top table to name in the bottom table. Drag ad_org_id to output schema.
    • That’s all, you can now rerun to see the result. The AD_Org_ID is now looked up!

Case 2: More Complex Data Import – MBPartner

Most data model are not as simple as in the first example. Give business partners for instance. Creating business partner consist of creating data in multiple tables, i.e., C_BPartner + C_BPartner_Acct + C_Location + C_BPartner_Location + AD_User. And these data will be created with the relation to one another, i.e., C_BPartner_Location is created with the foreign key C_BPartner_ID & C_Location_ID created before hand. In this example, we will be creating Business Partner and its related information, each from a row of record. And to make life a bit more complex, we will exclude all Business Partner with BP Group “Wholesales”.

Source Data: c_bpartner.csv

Client;Organisation;Search Key;Name;Name 2;Description;Credit Status;Tax ID;Tax Group;Business Partner Group;Customer;Invoice Rule (D=After Delivery);Delivery Rule (A=Availability);Delivery Via (D=Delivery);Price List;Payment Rule;Payment Term;Sales Region;Sales Representative;Credit Limit;Partner Location;Address 1;Address 2;Address 3;Address 4;City;ZIP;Country;C_Country_ID;Ship Address;Invoice Address;Phone;2nd Phone;Fax
GardenWorld;HQ;CUS1001;SONY CO.,LTD.;;;;             ;;Retailers;TRUE;D;A;D;Standard;S;Immediate;East;GardenAdmin;0;Office;2322 Wall Street;Central City;;;Florida;23110;Thailand;319;TRUE;TRUE;9877687817;;877899998
GardenWorld;HQ;CUS1002;CP Co.,Ltd.;;;;;;Retailers;TRUE;D;A;D;Standard;S;30 Net;East;GardenAdmin;2000000;Factory;5765 Hia Jieng Road;Hua Him;Thien 2,Ward Tan Phong;District 7;Thianjin;12321;Vietnam;340;TRUE;TRUE;0986799719, 998991822;;998877766
GardenWorld;HQ;CUS1003;Ecosoft Co., Ltd.;;;;;;Retailers;TRUE;D;A;D;Standard;S;Immediate;West;GardenAdmin;0;Factory;76/98 Naratiwas Rd.;Sathorn;CENTRE#06-05;;Bangkok;33212;Singapore;300;TRUE;TRUE;6596192189;;291181812
GardenWorld;HQ;CUS1004;P&G Co., Ltd.;;;;;;Wholesales;TRUE;D;A;D;Standard;S;30 Net;West;GardenAdmin;500000;Office;889 Empire Tower;Yanawa;;;Bangkok;10120;Thailand;319;TRUE;TRUE;6756255-8,01 8272366;;987766121


  • In this example, we will learn from the pre-installed job, MBPartner.
  • Install Samples, for sample job discussed in this tutorial.
    • Download the sample jobs
    • Switch back to perspective Integration, on Job Design, right click, select Import Items
    • Select archive file to import.
  • Double click on it will open the following canvas,


  • The connection to Adempiere as the same as previous example. Following are highlights of this case,
  • tMap – Lookup Tables
    • Looking up to related tables for IDs, for example, the following picture show how c_bp_group_id is looked up from c_bp_group table. Other look up tables are c_paymentterm, c_salesregion, m_pricelist, ad_user
    • You can also notice that, internally it is the joining of tables.


  • tMap – Filter
    • As mentioned, that we want to exclude Business Partner in Group Wholesales
    • Clicking on Expression Filter button File:expression_filter_icon.jpg on c_bpartner schema allow us to filter out the group Wholesales
    • Note that, the Filter is available on both the Output and Input side.


  • tMap – Multiple Output Model
    • As you can guess, the output row from tMap can map to as many table as we wish, what we have to do is create that output schema, and map fields.
    • What is important is to making sure that in sub sequence tables, we are using the IDs created from prior tables. For example, in this case we want to use C_BPartner_ID and C_Location_ID as foreign key in C_BPartner_Location table.
    • This can be achieved by adding Init Code for MBPartnerLocation’s Mode, tAdempiereOutput_3. This code will get the C_BPartner_ID and C_Location_ID from their schema internally.


    • I believe all the setup is intuitive enough to understand. In fact, it is very similar to the first example.
    • Next, simply click Run button to finish your task. Open the Business Partner window to check the result.

We shall end the tutorial here. If you have done some interesting case with Talend ETL, please feel free to share your insight here.

Possible Future Enhancement

  • Ability to work with DocAction for Documents. Although customizing tAdempiereOut to cover the
  • In updating mode, currently it only support Model with a unique key, but not yet support model with multiple foreign keys, i.e., C_BPartner_Location.
  • Transaction control is not yet supported, as currently it will commit with each
  • Ability to report the success / fail record. I think it will be possible to just reuse the source data file and give pass/fail flag and remarks at record end.

See Also