We have been under a hosted environment for quite some time now, but we have recently decided to go on our own and with that, we had to take a fresh copy of the Compiere 3.6.2 source code and do everything from scratch. I have been familiar with the source code so it was not a huge deal, but what was lurking in the unknown of a new set of source code and unsupported set up, so who knows how this thing would run. I am in the data load stage right now and ran into my first hurdle.

First a little background: we are loading data using a tool called Talend. With this tool we load data from the client’s existing production system and load it into Compiere. Some data is done through the import process, but some is done the hard way. I decided to load the data directly into the tables. This causes problems and this was decided on before I had any hands on experience with the Compiere source code so I had to go with what I knew, and that is raw data. I had problems with this method from the beginning and if I knew what I know now, I’d probably do it through code, but sometimes moving data from one system to another is not a 1 to 1. Transformations must be made, cleansing, well you know the drill. Using a ETL tool such as Talend is so nice and I have moved a lot of data cleanly using this method, but learning all the hooks in Compiere that are needed when doing this was a difficult lesson.

Loading something as simple as product categories in Compiere would seem straight forward. Load data directly into the m_product_category table and voila, product categories. Doing it by hand was not possible because of the amount of product categories. I do not think the developers at Compiere consider retail customers when they made this system. I am not sure about manufacturing or other markets, but retails have lots of categories.

After I loaded the categories, my scripts then went to loading products into the i_product table and from there, I run the Import Product process. During the run, I got the dreaded dialog which displays all the errors and successes. I never get all successes on the first try, but this time, I got no error messages. As someone who learned Compiere with little training, I have grown to hate the import processes. The error messages are misleading, false, or non existent. This time I got no error messages so I went to the logs ( I am running the enterprise edition so the logs are in the:

$COMPIERE_HOME/jboss/server/compiere/logs directory.

So I check the logs and I see this error:

cannot update ("COMPIERE"."M_PRODUCT_ACCT"."UPDATEDBY") to NULL

I wont bore you with the long process of finding the reason for this, but it turns out to be because the product categories I loaded in did not have an accounting schema record in the m_product_category_acct table. After a product is saved, there is a call to the updating accounting method that has a very large SQL statement to update the accounting. There is a join against the account table for the product category and is not an outer join so the insert fails. When it fails the import product batch fails and the import product process just stops.

So, what are the options here? I see several and perhaps someone could enlighten me on what the pros and cons of each are or even a better solution.

1) I am more on the development side of things so I am not sure ( yet ) what the consequences are of turning off the accounting for products would be, but that was my first thought.

2) modify the SQL to make it an outer join so the whole insert does not fail because of it. Again, what the consequences are, I am not sure.

3) This is the solution I am going with right now. I am manually creating the accounting records for the product categories. I went into the MProductCategory object and looked at the AfterSave method to see if I could just manually create the records. I caught the StringBuffer before it was sent and now I am going into Talend and running the following SQL using the m_product_category_id for each record in the m_product_category table ( within the tenant of course! )


INSERT INTO M_Product_Category_Acct (M_Product_Category_ID,
C_AcctSchema_ID,
AD_Client_ID,
AD_Org_ID,
IsActive,
Created,
CreatedBy,
Updated,
UpdatedBy,
P_Asset_Acct,
P_COGS_Acct,
P_CostAdjustment_Acct,
P_Expense_Acct,
P_InventoryClearing_Acct,
P_InvoicePriceVariance_Acct,
P_MaterialOverhd_Acct,
P_PurchasePriceVariance_Acct,
P_Resource_Absorption_Acct,
P_Revenue_Acct,
P_TradeDiscountGrant_Acct,
P_TradeDiscountRec_Acct)
SELECT ,
p.C_AcctSchema_ID,
p.AD_Client_ID,
0,
'Y',
SysDate,
,
SysDate,
,
p.P_Asset_Acct,
p.P_COGS_Acct,
p.P_CostAdjustment_Acct,
p.P_Expense_Acct,
p.P_InventoryClearing_Acct,
p.P_InvoicePriceVariance_Acct,
p.P_MaterialOverhd_Acct,
p.P_PurchasePriceVariance_Acct,
p.P_Resource_Absorption_Acct,
p.P_Revenue_Acct,
p.P_TradeDiscountGrant_Acct,
p.P_TradeDiscountRec_Acct
FROM C_AcctSchema_Default p
WHERE p.AD_Client_ID= AND
NOT EXISTS (SELECT *
FROM M_Product_Category_Acct e
WHERE e.C_AcctSchema_ID=p.C_AcctSchema_ID AND
e.M_Product_Category_ID=)


After all that, I ran the imports again and this time all the products made it in safe and sound. Let this be a lesson to me. If I load data in directly into the tables, look to see if there is an accompanying accounting table. Now to the next challenge.