red1.org

[trifon]

Hi Rich,

Ok. It will be good to have new coulmns City and Region.
For Internationnal addresses we need column which must contain two letter country code.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

I have done a test of the cleanup and this is what I have learned:
1-I have added the following columns: new address2, new address 3, city, country code
2-Address1 will stay the same (always street address)
3-When using country code (international addresses), new address2 & new address3 will = old addr2 & 3 and we will stay the same and we will do the cleanup latter (creating city value). This is because international address scheme are difficult for us to decode so we just enter it exactly as we see it, but I will look into this further.
4-a new state/region column is not necessary as it already exists, unless we will include international regions, but I would not know how to handle this. US state/region is a 2-letter code. We do not know the codes for international state/regions.

Please let me know if this is acceptable.

-Rich

[trifon]

Region is necesay only if Country in Compiere is defined to have regions. And we could set one default region if we do not know it.

Rich, could you publish modified version of order_sample.xls?

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

order sample? do you mean updated customer sample? I didn't make any changes to order sample. Please specify what table/fields you think changed.

Thanks,
Rich

[trifon]

Ups, it is my mistake i mean Customer data.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

International addresses will have different country codes. We do that by supplying the Compiere's country code to the import loader for BPartner which is catering for that. Without supplying the code, now your data is defaulted to US.

Compiere already has all the standard 2-char codes, such as CA for Canada, MY for Malaysia, JP for Japan, HK for HongKong. In your Excel, we put a new column to the data rows (preferably right most), then we intro a new field to the import loader and bring them in.

NOTE: U need not repeat for US ones. Compier Import is an updating engine, u just need to feed it new changes, and leave those that are imported and ok.

[riche]

Trifon-
I will do that tomorrow when I have a chance.

-Rich

[trifon]

[riche]

If you need to replicate what I have done, here are the steps:
1-add 2 new columns: New CUSA2(=IF(F3=0,0,E3)), New CUSA3(=IF(F3=0,E3,F3))
2-Dragged the formulas all the way down
3-Copy the columns, then paste special:Values
4-Search and replace:
a-replace '0' with nothing (match entire cell)
5-Add Country code column, manually populate with 2-letter code for non US addresses

If this is acceptable, we will add these 3 columns to all address tables (ShipTo List, Vendor List, Order Data), at the rightmost end of the table

I have e-mailed some sample data to Gan to post here.

-Rich

>> Sample Data - New Customer (MS Excel, 34.5KB) -- Posted by Gan

PS- I thought about this some more and thought perhaps we should modify "New CUSA3" and another column for City. The formula for "City" would be =IF(Country Code = 0,IF(F3=0,E3,F3),0). The formula for "New CUSA3" would be IF(Country Code <> 0,F3,0).

I will send Gan the updated file to reflect this logic.

>> [url=http://www.red1.org/barn/HutzlerCompiereUSA/sample_data-new_customer(new).xls]Updated Sample Data - New Customer[/url] (MS Excel, 34.5KB) -- Posted by Gan

[riche]

Trifon-
I was reviewing the customer loader in the new dump and had a few comments:
1-Customer.SHCOM can be set = Compiere Customer "Order Description" - In the legacy system, this field is automatically added to the order, just like this field in Compiere
2-Compiere "Invoice Rule" should be set = After Delivery
3-Compiere "Delivery Rule" should = Complete Order (I think - we should discuss, how we are handling backorders with IsBackOrderAllowed field)
4-Compiere "Payment Rule" should = On Credit
5-Compiere "Credit Limit" should = Customer.CRLAM
6-Sales Rep, Flat Discount, Pricelist will all need to be set (and probably loaded first, particularly sales reps)
7-Customer.BUYER will need to be added as a contact (with same address, phone,etc)
8-We should discuss the Customer.MLLST field.
9-I assume the City/Address3/Country Code will be set once we work out the details.
10-Per our conversation a few weeks back (posted in "On Sales Order thru Shipment Cycle"), Org should = *, and for Search Key we append 0+Company # to the begining of the Customer #.

I will also comment on the Product Loader and Vendor Loader. I will take a look at the other loaders, but will assume that those will be refined later.

-Rich

[riche]

Product loader comments:
1-ITNBR = both Search Key & Name
2-ITNDESC = Description
3-ENGNO = UPC
4-SKU will be reserved for later usage (sometimes we have a second UPC #)
5-Should List & Standard (& Limit) all = PRICE?
6-WHSLC = Locator (we should discuss whether we will use 1 or 2 dimensions - I am leaning towards 2)

-Rich

[riche]

Vendor Import Comments:
1-For salesreps, we should set IsSalesRep flag.
2-We must do a search & replace for the Liability Account field (we are changing the chart of account structure for Compiere)
3-BPGroup = Vendor
4-DISCP = PO Payment Terms (not flat discount)... you can assume assume it is discount if paid in 10 days, net 30.

-Rich

[red1]

After examining and understanding what Rich is trying to do for new CUSAs, I suggest rather that those 2 new columns replace the positions of CUSA2&3 and the later moved to the rightmost but not imported into loader as they are reference source.

That 2 new fields are called scrubbed from source (reference cols).

On the change to the COA's Structure, will it impact on any Defaulted Account No.? If so, we have to test if its possible. There was an earlier bug resolved by Compiere but we need to reconfirm.

[trifon]

[riche]

Vendor Account Liability Field in Compiere - In the legacy vendor table, there is an account field (referencing the old chart of accounts). I had to drastically change (totally reorganize) our accounting structure in order for it to fit into Compiere's account code structure, but I did track the changes I made in an excel spreadsheet. Therefore the value for account in the legacy vendor table has to be re-maped to the new value. I will post a draft of this table here.

PO Payment Terms (vendor) - all discounts are given in 10 days (.5% 10, net 30; 1% 10, net 30; 2% 10 net 30)

Customer Payment Term - Vendor Payment Term and Customer Payment Terms are different (although there is some overlap). The 6 terms you listed are customer payment terms. I don't think Compiere differentiates between Vendor & Customer Payment Terms, so I think you should create the Customer Terms first then the Vendor Terms.

EOM = End Of Month

-Rich

[riche]

FRTCD / CARCD / SHCOM fields exist in both customer and in ship-to table. Therefore, these fields should be added to Compiere location table. They will only appear if Ship-To flag is checked. FRTCD & CARCD fields will be added to Bpartner and used as the default when autopopulating new locations that don't have a value specified. When location is chosen in sales order, it will autopopulate the FRTCD and CARCD. SO_Comment in sales order will be a concatanation of SO_Comment from Bpartner and SHCOM from location (if this is too difficult, we could just add shipping comment to sales order).

I discussed this with Trifon.

-Rich

[riche]

A few comments / questions:

1-Please remove UOMs that we do not use / did not import.
2-All imported customers should be tax exempt and this should be set as the default for all new customers..
3-Who is making changes to the database for adding fields? I just want to make sure I followup with the right person.

Thanks,
Rich

[riche]

Vendor Import Comments:
1-"Is Active Customer / Prospect" is still set for vendors (this is the flag on the Business Partner Tab (1st) - not the one on the customer tab)
2-Vendor Tax IDs were not imported
3-Payment rule for vendors should = check
4-We need to set up a flat discount field for vendor payments (I believe Trifon is working on the functionality).

-Rich

============== comment by red1 ============
noted points 1,2,3 (intentionally left out)
=============== red1 ===================

[trifon]

1,2,3 are in my TODOs list and will be fixed for next import.

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

Hi,

This is a list with changes that i have made:
Changed ILf for Ship To Address in order to import [SHPNO] column. It will be used as a search key when importing Sales Orders.

30 Import Hutzler Ship To Addresses Customer ship-to number[SHPNO] C_BPartner_Location_Value String 3

Changed ILF for Sales Orders:
200 Import Hutzler Sales Orders Customer ship-to number[SHPNO] C_BPartner_Location_Value String 22

Uploaded :
http://red1.org/barn/HutzlerCompiereUSA/sources/Import Loader Format.xls
http://red1.org/barn/HutzlerCompiereUSA/sources/Import Loader Format.csv

Aded new column(C_BPartner_Location_Value) to : I_Order and I_BPartner tables.
Added new Column (Value) to C_Order table.
Druid File uploaded :
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/HutzlerDruidProject.druid
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/DruidSummary.xml
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ALTER_Table.sql
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ViewTest.xml

Changed ImportBParter in order to import C_BPartner_Location_Value column in C_BPartner_Location.Value column.
Uploaded :
http://red1.org/barn/HutzlerCompiereUSA/sources/base.src.org.compiere.process/ImportBPartner.java

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

Please do not use ViewTest.xml.
It seems that I have to modify it further in order to add filed to proper places.

Trifon
_________________
Trifon Trifonov

[trifon]

Hi everyone,

I modified XML2ADHandler and now "field" element supports this attributes:
displayLogic="some expression here"
isSameLine="Y/N"
displayLength="number"

Sources are uploaded at wellknown place:
http://red1.org/barn/HutzlerCompiereUSA/sources/XML2ADHandler.java

Also I have modified and uploaded :
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ViewTest.xml
This version creates required by Rich changes:
03 ) FRTCD / CARCD / SHCOM to appear if Ship-To flag is checked.

You can use it to create/modify new/existing fields in windows. But remember to order fields as you want, because at the moment it creates them on the bottom of the window.

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

I modified XML2ADHandler and now "field" element supports and this attribute:
defaultValue="some text here"

Sources are uploaded at well known place:
http://red1.org/barn/HutzlerCompiereUSA/sources/XML2ADHandler.java

Also I have modified and uploaded :
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ViewTest.xml
This version creates required by Rich changes:
8 ) Tax exempt should be set as the default for all new customers.
But unfortunately in Compiere we can't create such functionality, so IsTaxExempt can be SET for every new Business Partner.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

I have modified and uploaded Hutzler.druid to

modify tables:

M_Package > FreightRule
C_Order > SalesComm
C_OrderLine > SalesComm
C_CommissionDetail > CommPercent,CommAmt, FlatDiscount, PriceActual, PriceList.
(But since we are now calculating Comm at Order level, we wont need this, and will leave them alone).
I_Product.ITTYP, PACKC (forgotten in previous druid)

http://red1.org/barn/HutzlerCompiereUSA/sources/red1/Hutzler.druid

Trifon, pls take this if u wana add your changes.

[red1]

Trifon,
I notice your post to pick up your Druid was 13th June, but your folder in FTP says 16th June, and it has a SalesRepID in I_BPartner which i dont have

And somehow your latest ILF doesnt have the SalesRepValue in it.. hmm.. dont u need it so that the Vendor Loader can import SalesReps? Am i missing sumtin here?

Anyway i have put in SalesRep_ID via XML2AD and AlterTable into I_BPartner. My Druid as per posted is now synch to yours. Can u update and synch them?

Thanks!
red1