red1.org

[red1]

Here we shall discuss and locate all the changes to the Table-Field structure, be it New Creations or Amendments to present structures.

From the discussions, we can see that there will be substantial intro of new fields into the present tables i.e. M_Product, C_BPartners, etc.

I have read the sample.xls from Rich which is further commented by Trifon, and noted the ???; with important normalisation by Trifon on where such field can be situated in other tables. Thus an ERD will have to be done. Compiere already has its own ERD master, which we will extend on.

TODO -
1. To map out changes to the Entity Relationship Diagram (ERD) of Compiere to incorporate the new changes. 1-day
2. To list the normalised changes in a separate deliverable doc. 2-day

I hope to do this with Gan this week before speaking again with Rich.

[red1]

I found the ER Studio diagrams too elaborate and tedious to handle when we already have homed in on the tables concerned.

To save time, we will just re-use the SampleData.xls commented on by Trifon and Rich to give right away the new fields names to be created in Compiere table structure. Trifon may do the same thing too, mentally for the imports to map to.

[riche]

I was wondering how you were going to do those oversized diagrams . I agree. It will be much easier to follow this way.

-Rich

[trifon]

Hi All,

I'm in a hurry to publish first sources. Unfortunately they are not tested, because of the temporary miss of Oracle instance arround me. They are related to imports of Loader Format Definitions.
This is just one of two files that will be responsible for that works. The other will be finished tomorrow.
Location is: http://www.red1.org/barn/HutzlerCompiereUSA/sources/ImportImpFormat.java

Two new windows must be created, but as we know with the help of XML2AD this is easy part.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Trifon,
If i understand this correctly, it seems that we can then import the different loaders (designed for the migration table format themselves?) Then this will let us transfer the loaders around without redefining in the client instance? Hmm sounds good and smart!

[trifon]

Hi Red1,

You are absolutely correct. That was my purpose, I have developed and the second class and now I need to test it.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Trifon,
In your ADChanges 16 May.xls u mention the need to have a new field for Date Last Order in the Customer (BPartner) file. I feel that may be redundant cos somewhere in Compiere u can have transactional history of the Customer. Its more normalised to create Views (if they do not already exist) to join such data from the system.

We also intend to import orders or invoices to reflect the present state of the system and operations.

[trifon]

Ok,

Thanks for this info Red1 I will have to research it.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

I think u can get that from the main menu top bar of Compiere window and select Customer Info.

Such info is also follow thru by the CRM part of the Web interface Request from a client where the admin following up can view such transactional history. This is what i have seen demo before so i know it must work.

[red1]

About Import Loaders.
I m putting some mental notes here:

1) After Importing into I_tables, the system wll process the info and populate the necessary files. For Products or Items as we have ascertained, there will be corelating of PriceList code with PriceLists (Version) IDs done in hardcode IDs. We can prepare the codes first and feed in the IDs during run as the Pricelists will be known after Rich have them.

Thus we have to look into the post-Import processing code. I have done it for the SalesOrder Importing before which is quite extensive cos it has not only to import the orders but to process them and create Invoices.

2) Import Of Sales Orders and Invoices has to be prepared and standby as we will need them; test during this trials, and vetted for live import during cutover.

I envisaged these Imports to happen:
a) Products
b) Business Partners
c) Inventory
d) Orders

We also need some TOAD SQLs to brush up the info as needed. This is what happened on previous occasions. Migrating into Inventory was created that way, since there is no import for it.

[trifon]

Import of Loaders is Ready.

Source codes are here:
http://www.red1.org/barn/HutzlerCompiereUSA/sources/ImportImpFormat.java
http://www.red1.org/barn/HutzlerCompiereUSA/sources/ImportImpFormat_Row.java
http://www.red1.org/barn/HutzlerCompiereUSA/sources/X_I_ImpFormat_Row.java
http://www.red1.org/barn/HutzlerCompiereUSA/sources/X_I_ImpFormat.java
http://www.red1.org/barn/HutzlerCompiereUSA/sources/Import Loader Format.xls

This is what have to be done in order Import Loader functionality to work:
Creatoin fo two Processes.
Creation of two tables and two windows.
Creation of Two Import Loader Formats("Import Loader Format" and "Import Loader Format Row"). This formats are described in "Import Loader Format.xls" file.

How to use Import Loader functionality:
When someone creates "Import Loader Format" he can export this format and it can be imported into other Compiere instance. Process of export is:
Window: "Import Loader Format", go to Tab "Format Filed" press "Report" Button and after that "Export" button, choose file name and save it.
CSV file contains rows like that:

Sequence,"Import Format",Name,Column,"Data Type","Data Format","Start No","End No","Decimal Point","Divide by 100",Constant,Callout,Script

50,"Example BPartner",City,"City - City",String,,4,0,".",false,,,

30,"Example BPartner","Contact Name","Contact Name - ContactName",String,,2,0,".",false,,,

Unfortunately Compiere concatanes Value and ColumnName, so Column looks like that: "Contact Name - ContactName", but we need only
"ContactName", so first part "Contact Name - " have to be deleted. This is small manual interaction and it do not take much time, compared to manual creation of whole Import Format.

Import of Loader Formats is easy open "Import File Loader" window, choose "Import Loader Format Row" Import Format and select proper .csv file.
Open "Import Loader Format Definition" Window and start import process.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Thanks Trifon,
We have to figure out the Product Import Format Loader and the Customer Import Format Loader. Thats easy cos u have commented on the SampleData what the Compiere fields are. Studying them to see if we need to look into the import processing codes for changes.

[trifon]

I'm preparing Customer Imports at the moment.
I'm making Customer Loader Format and after that will modify source codes if necesary.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Rich,
From the Product sample data, i notice a ITTYPE field that Trifon says its ProdCategory but its data is 0-5, F and u remarked: represent item types - ex: purchased, raw materials, manufactured,etc.; data can be changed from single character.

Is that Item Class (ProdCategory)? If its new field, then where is the ItemClass?. I expect same data i.e. 10,11,52...

Oh i found out now its ITCLS.. cos that data match... so i will take this instead and leave out ITTYPE. The only use for ITTYPE will be scrub it and make it IsPurchased which is used in Compiere. Uncheck that will means its produced. Or IsBOM can be used to say its produced from other raw products.

[red1]

I have created a test data for ImpProduct to use a RichProduct.csv :
SearchKey, Name, SKU, UOM, VendorProdNo, Weight, Category, POPrice, ListPrice.

That corresponds to ITNBR ITDSC, ENGNO, VendorProdNo, Weight, ITCLS, UCDEF, Price.

The last two r commented by Trifon as need to be in PriceList but it seems to be right there in Product Window as the ProductPurchasing Tab or ProductPO Table. Later the PriceSchema can derive from there. So Trifon is correct but it goes there first. In ProductPurchasing there is also Effective Price and so forth, so will need Rich to look thru to see if any are needed or more technically correct.

Will test later the full 3,000 prods. Will update here any issues.

[red1]

There are two errors:

1) UOM is a record_id thus X125 field is to be used. But valid data is EA but not CS, DL, etc. So we need to create such UOMs first. Later Rich can fill in the desc and other info for these UOMs.

2) There must be a BPartner for each product! I check from GardenWorld and it has to be a supplier. I do not find this in the sampledata. Rich pls advice, otherwise we will use Standard.

[trifon]

Status Report:

Finaly I have created Tables, Windows and Process at Compiere and tested Loader Formats.
Export is located here:
http://www.red1.org/barn/HutzlerCompiereUSA/sources/ExpDat-20050526d-FixLoaderFormat.jar

I have added new functionality to Import Loaders, now already existing Loaders are updated and it is not necesary to delete them prior importing.

I have uploaded new versions of:
X_I_ImpFormat.java
X_I_ImpFormat_Row.java
ImportImpFormat_Row.java

There is partialy done Loader format for Customers, but it is in process of creation, so it is not working for now.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

[red1]

My mistake then. VNDNR is not ProdNo but PartnerValue. Trifon did note that correctly. I will create the UOMs in and try again.

[red1]

Just to remind Trifon that Table-Fields for Vendor and Customers changes will happen in the same C_BPartner table. . So u will have to give me in one XML2AD.

I need to create those few vendor records first anyway, so that the VNDNRs IDs will match during Products import.

I just created the UOMs as given above and the symbols are same. This is done at SystemAdmin level and confirmed viewable in Client level.

[riche]

You should also have a similar situation with salespeople (they are pointed to from customers).

-Rich

[red1]

I have succesfully created 2 new fields in M_Product table (with full persistence in Compiere i.e. GenModel) - ITTYP and PACKC sa 2 chars fields.

USProduct Loader also nicely done here (ignore old one above):

ITNBR Value String
ITNDESC Name String
ENGNO SKU String
PRICE PriceList Number
UCDEF PricePO Number
UOM _X12DE355 String
ITTYP ITTYP String
ITCLS ProductCategory_Value String
VNDNR _BPartner_Value String
Weight Weight String
PACKC PACKC String

I redo in the above order, to stick close to the arrangement of the sample data so that when we reapply the live, minimal shuffling of the cols.

Will give a throwaway dmp with migrated products in it to Rich in the server to check shortly.

[red1]

Trifon's Customer ILS (importLoaderFormat):
(I assume that they follow the comment notes in sampleData.xls given before. But need to comment whether new Compiere fields are in same BPartner table or elsewhere. And some shuld use proper Compiere names such as GroupValue shuld be C_BPGroup_ID or else the loader wont know)
I also suggested yesterday that CUSA2,3 be replaced by City, Region (data need scrubbing).

Company No[COMNO] -- AD_Org_Value
Customer Number[CUSNO] -- Value
Customer Name[CUSNM] -- Name
Address 1[CUSA1] -- Address1
Address 2[CUSA2] -- Address2
Address 3[CUSA3] -- Address3
Zip Code[ZIPCD] -- Postal
State Code[STACD] -- RegionName
Area Code[AREAC] -- AreaPhoneCode
Phone[Phone] -- Phone
BP Group[CUSCL] -- GroupValue
Is Back Order[BKORD] -- IsBackOrderAllowed_Value
Partial Shipment Code[BKPSC] --
Unit price discount code (item master)[DCODE] --
Cust type disc code,A=key 0=none 1-5=cus typ[ITDCD] --
Standad Terms[STERM] -- C_PaymentTerm_Value
Credit Limit[CRLAM] -- SO_CreditLimit
Salesman Number[SLSNO] --
Amount Due[AMDUE] --
Date of Last Payment[DLTPM] --
Date of Last Order[DTLOA] -- DateLastOrder
Non-Mail list indicator (N or blank)[MLLST] -- SendPostalEmail
Carrier Code[CARCD] -- CarrierCode
Shipping Comment[SHCOM] -- ShippingComment

[red1]

and this is the VendorLoader.csv from Trifon. Again we need to scrub address2,3 to fit in City,Region.

Value
Name
Address1
Address2
Address3
Postal
V_Liability_Acct_Value
Name2
AreaPhoneCode
Phone
PO_FlatDiscount
TaxID

[riche]

I can do this in excel if you like (I've had to do a lot of data cleanup in my previous jobs so I know a few shortcuts in excel), but I will have to add a few columns, including City, Region. So we will also have "new Addr1-3". New Address 1-3 will map to old Address 1-3 where they don't contain City/Region. My question, though, is how do we deal with international addresses? Let me know who should do this.

-Rich