red1.org

trifon · Posted: Mon Jul 11, 2005 11:52 pm Post subject:

I found that PO_FlatDiscount had wrong Reference "Integer", I changed it to "Number".

Uploaded new version of(I'm still trying to upload new version) :
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/HutzlerDruidProject.druid

http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/dbport.org.compiere.model/X_I_BPartner.java
http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/dbport.org.compiere.model/X_C_BPartner.java

2 )
In the latest ExpDat_11.dmp
i found that in window "Window, Tab & Field", window "Business Partner", Tab "Customer", parent link column is set to "BPartner_Parent_ID", which is wrong. It sould be "C_BPartner_ID".

3 )
I found that in Data.xml i had defined two Greetings with equl name "Ms.".
in new version of Data.xml i removed redundant.
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/Data.xml

4 )
During test of Products import i found that most of the products do not have information about bsuness Partner and import process fails with "Invalid BPartner"

Regards,
Trifon
_________________
Trifon Trifonov

trifon · Posted: Tue Jul 12, 2005 12:15 am Post subject: New Version of ILF Row

I modified ILF "Import Hutzler Sales Orders"

changed Data Format of four fields to "dd/mm/yyyy".

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

Regards,
Trifon
_________________
Trifon Trifonov

riche · Senior Joined: 16 Dec 2004 Posts: 386

1-US date format should be mm/dd/yyyy.
2-GLANO (G/L vendor liability account) in the vendor import is not being imported into the business partner. Is this being done on purpose?
3-In Customer Import, shipping comment code is only being imported into location, not into the customer tab. Remember the carrier code, freight code, & shippping comment on the customer tab are only used for the default for new locations. Sales orders should be pulling the carrier code, freight code, shipping comment from the location tab (the fields exist on both the customer & location tabs, fyi).

Thanks,
Rich

trifon · Posted: Tue Jul 12, 2005 7:45 pm Post subject:

trifon · Posted: Thu Jul 14, 2005 7:40 pm Post subject: Staus Report

I finished development of import BPartner accounts:

I added new constant to ILF for Vendors.

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

please note that this is new directory in ftp site:
http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/base.src.org.compiere.model/MBPVendorAcct.java

I added three new columns to I_BPartner table.

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

Regards,
Trifon

P.S. In the latest dump file Tab "Customer" still has wrong parent link column "BPartner_Parent_ID", please change it to "C_BPartner_ID".
_________________
Trifon Trifonov

trifon · Posted: Thu Jul 14, 2005 11:49 pm Post subject:

Hi All,

I uploaded new version of ImprtBPartner.
http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/base.src.org.compiere.process/ImportBPartner.java

The old one had a problem with imports of Customers.

Regards,
Trifon
_________________
Trifon Trifonov

riche · Senior Joined: 16 Dec 2004 Posts: 386

I broke up my latest feedback into a section for Trifon and a section for Red1. Please re-assign if I was not correct. Please include these changes in the next build.

Trifon
1-"delivery via rule" needs to be set during customer import for all customers = shipper

2-customer.invoice discount is being imported as invoice discount number instead of percent (ie - 1 instead of 0.5%, which ends up as 1%). I don't know if you want to fix this in the customer import or with an SQL statement. They should be corrected as follows:

Invoice Discounts:
1- 0.5%
2- 5.0%
3- 8.0%
4- 10.0%
5- 20.0%

3-What happened to payment terms? I don't think they import and I don't think I even see them in business partner. Can you confirm?

Red1
1-change the names of freight rules to match up to my terms. Create any of my required freight rules that do not exist.
a-in the last build "prepaid & add" rule was listed as fix price which it is not. this rule need to be created.
b-need to create "collect" rule & "prepaid" rule. they are both similar to freight included (no customer or invoice impact). the main items to point out are:
-both "collect" & "prepaid" should have no freight in the order
-both "collect" & "prepaid" should have no freight in the invoice
-"collect" should cause the freight amount field in shipment to not show up at all. this is because "collect" means freight is paid directly by customer so there is no freight amount to enter.
-for prepaid, we need to be able to enter a $ amt for what we get charged for freight into shipment, but this may be entered at the end of the month, long after the invoice is created. Also, we may want to letter create a "reconcile shipment charges" functionality that would do a check on these shipment charges and the rule = prepaid would be important for that

2-if freight code = blank, then Freight Cost Rule (FCR) = Prepaid & Add. I can set freight code = A in data if it makes it easier (and it will probably be clearer for users).

3-create shipping comment field in Sales Order

4-make sure Delivery Via Rule, Freight Cost Rule (FCR), and Shipper autopopulates from location. Remember FCR = freight code and Shipper = carrier code. (Note to Trifon - should we get rid of freight code and carrier code and instead load these values directly into the FCR & Shipper fields? Please work out with red1.)

5-Please change the term "standard price" to "customer price" (everywhere, or atleast in Sales Order and associated pop-up windows).

6-In Sales Order, can you change the term "Date Promised" to "Ship Date"

7-In Sales Order, please add the "recieve date" check box right under "Date Requested No Later" (see previous posts).

8-in sales order, you can choose BP through a pop-up window (called Business Partner Info). Can we change the fields listed to (in order): Key, Name, Credit Used, Credit Available, City, Zip, Address Line 1, Address Line 2, Address Line 3?

Thanks,
Rich

red1 · Posted: Sat Jul 16, 2005 2:06 am Post subject:

Trifon has recommended a way to solve (2) thru a post here http://www.red1.org/forum/viewtopic.php?p=2009#2009
I concur that we use the SQL edit way since its one time only and much slicker.

Red1
2) Yes, u can set FCR to 'A' for blank ones. Its C for Collect, P for Prepaid, and H for FS&H.
3) Create Shipping Comment Field in Sales Order: Either Trifon or i can do that into my Druid, but i must make sure Trifon doesnt forget to synch to his.
4) has to discuss with Trifon...
Other than that wont be an issue.

riche · Senior Joined: 16 Dec 2004 Posts: 386

red1 · Posted: Sat Jul 16, 2005 10:35 am Post subject:

Here it is again:

Posted: 11 Jun 2005 08:39 pm Post subject: Status Report

--------------------------------------------------------------------------------

I have created SQL statement that modifies C_BPartner.FlatDiscount field:

Set C_BPartner.FlaDiscount according to the mapping:
1=0.5%, 2=5%, 3=8%, 4=10%, 5=20%

UPDATE C_BPartner
SET FlatDiscount = 20
WHERE AD_Client_ID = 1000000
AND FlatDiscount = 5
AND IsCustomer = 'Y'

UPDATE C_BPartner
SET FlatDiscount = 10
WHERE AD_Client_ID = 1000000
AND FlatDiscount = 4
AND IsCustomer = 'Y'

UPDATE C_BPartner
SET FlatDiscount = 8
WHERE AD_Client_ID = 1000000
AND FlatDiscount = 3
AND IsCustomer = 'Y'

UPDATE C_BPartner
SET FlatDiscount = 5
WHERE AD_Client_ID = 1000000
AND FlatDiscount = 2
AND IsCustomer = 'Y'

UPDATE C_BPartner
SET FlatDiscount = 0.5
WHERE AD_Client_ID = 1000000
AND FlatDiscount = 1
AND IsCustomer = 'Y'

red1 · Posted: Sat Jul 16, 2005 2:48 pm Post subject:

ALL ARE DONE except red1.(4) needing to confer with Trifon. (download from Final Builds thread).

Trifon,
I somehow couldnt see my new fields (InvoiceDiscount) in my Druid Sad

And i did the few changes above directly into AD. They are 'U' entityType. So i hope your XML2AD doesnt delete stuff done by me. (I told u i disable ColumnDelete in XML2ADHandler to be careful).

Pls use my latest ExpDat dump when aligning new window seqs.
SameLine shuld uncheck if the seq before it is checked.

trifon · Posted: Mon Jul 18, 2005 6:59 am Post subject:

I added DeliveryViaRule to I_BPartner table and to ILF.
This finished this issues:

1-"delivery via rule" needs to be set during customer import for all customers = shipper

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

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

http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/base.src.org.compiere.process/ImportBPartner.java
http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/dbport.org.compiere.model/X_I_BPartner.java

Problem reported by Rich concerning Payment terms is solved:
Payment Terms weren't defined and import process didn't find them dirung imports.
I defined them in data.xml file:
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/Data.xml

Added functionality to set proper values of FreightCode, Carrier and Shipping Comment when creating new BPartner Location.
http://red1.org/barn/HutzlerCompiereUSA/sources/2.5.2d/base.src.org.compiere.model/MBPartnerLocation.java

Sorry, but i have problems with uploading of files, spo not all files are with most recent version
Regards,
Trifon
_________________
Trifon Trifonov

red1 · Posted: Mon Jul 18, 2005 9:58 am Post subject:

Trifon and i discussed about the FreightCode and Shipper fields, and this is how it shuld be done:

1) Leave the fields alone cos they can be deleted easily later. Has no impact now. (In Import files it doesnt matter which names. In base files they wont be used).
I have reviewed ImportBPartner.java here:

riche · Senior Joined: 16 Dec 2004 Posts: 386

Trifon-
1-The GLANO field is causing problems with the vendor import. I had to remove the field from the ILF to get vendors to import.
2-Salesman are being loading as vendors but are not being tagged as salesman anymore.
3-During vendor imports, can you set the invoice-to & ship-to address checkboxes fields also.
4-For all customer imports & ship-to imports (open order address imports, also), set delivery via = shipper in ILFs.
5-Can you let me know the status on the rest of the imports?

Thanks,
Rich

trifon · Posted: Tue Jul 19, 2005 8:11 pm Post subject:

red1 · Posted: Wed Jul 20, 2005 2:25 am Post subject:

Trifon,
Pls advice if your new Druid is ready, and what are the changes so that i implement only those changes.

I modified ImportBPartner that will set BPartnerLocation.FCR,Shipper and Comment from I_BPartner.FreightCode,Carrier.

I have commented your MBPartnerLocation cos we no longer use those fields and need not populate from main tab. its set at each location and will populate SO and PO:
// @Trifon
//red1 MBPartner bPartner = new MBPartner(getCtx(), getC_BPartner_ID(), null);
//red1 setShippingComment(bPartner.getShippingComment());
//red1 setCarrierCode(bPartner.getCarrierCode());
//red1 setFreightCode(bPartner.getFreightCode());

Take it easy, drink lots of water, and rest when tired.

trifon · Posted: Wed Jul 20, 2005 7:15 am Post subject:

I have e problem, while testing Import BPartners i get to moment when import stop working. I do not see any strange log and i do not know what to research.

I suspect that Oracle runs out of connections, but i'm not sure.
Also my computer blocked while doin db restore and probably this could have some impact.

I will try to restore DB and retest.

Regards,
Trifon
_________________
Trifon Trifonov

trifon · Posted: Wed Jul 20, 2005 7:47 am Post subject:

I think that i resolved my last problem. It was my fault in importBPartner.java.

Regards,
Trifon
_________________
Trifon Trifonov

trifon · Posted: Wed Jul 20, 2005 8:18 am Post subject:

Hi Red1,

Please change CarrierCode from Number to String.
Sorry it was my mistake that i told you that CarrierCode should be Number.

Please add new column to I_BPartner, name it M_Shipper_ID.
Also, please modify ILF, change CarrierCode from Number to String.

Regards,
Trifon
_________________
Trifon Trifonov

riche · Senior Joined: 16 Dec 2004 Posts: 386

red1 · Posted: Wed Jul 20, 2005 11:58 am Post subject:

Trifon,
I made a BIG mistake. I tot that the FreightCode and CarrierCode / Shipping Comment are no longer needed at BPartner. Rich stressed to me that they are, as base reference.

Its just that during Ship-To importing we populate the BPartner.Location this time as FCR and M_Shipper_ID.

To avoid coding confusion, u better update the ILF and ImportBPartner yourself.

Thus:
1) All your previous work remains
2) Only duirng Ship-To importing the values to be updated goes to BPartnerLocation's FCR and M_Shipper_ID instead.

(I have done autopopulate of those two and will do the same for Shipping Comment)
We can chat if u think i still got sumtin wrong here.

I have DONE the CarrierCode to String and added M_Shipper_ID to the ILF in live instance

trifon · Posted: Wed Jul 20, 2005 7:59 pm Post subject:

trifon · Posted: Wed Jul 20, 2005 8:30 pm Post subject:

Hi Team,

I'm not feeling quite well today, and i'm not sure how much i will be able to work. I hope after 3-4 hours to be better and to finish some work.

Regards,
Trifon
_________________
Trifon Trifonov

riche · Senior Joined: 16 Dec 2004 Posts: 386

Trifon-
I re-ran the imports and I got the following errors on many records for ship-to imports:
Insert BPLocation: java.sql.SQLException: ORA-01722: invalid number

Update BP Location : java.sql.SQLException: ORA-01722: invalid number

On the console, I noticed the following problems:
===========> DB.executeUpdate: Update=UPDATE I_BPartner i SET M_Shipper_ID=(SELECT M_Shipper_ID FROM C_BPartner_Location p WHERE i.C_BPartner_Location_ID = p.C_BPartner_Location_ID AND p.AD_Client_ID=i.AD_Client_ID) WHERE i.C_BPartner_Location_ID IS NOT NULL AND i.M_Shipper_ID IS NULL AND I_IsImported='N' AND AD_Client_ID=1000000 [null] [39]
java.sql.SQLException: ORA-00904: "I"."M_SHIPPER_ID": invalid identifier
; State=42000; ErrorCode=904

===========> DB.saveError: DBExecuteError - ORA-00904: "I"."M_SHIPPER_ID": invalid identifier
[39]
===========> DB.executeUpdate: Update=UPDATE I_BPartner i SET FreightCostRule=(SELECT FreightCostRule FROM C_BPartner_Location p WHERE i.C_BPartner_Location_ID = p.C_BPartner_Location_ID AND p.AD_Client_ID=i.AD_Client_ID) WHERE i.C_BPartner_Location_ID IS NOT NULL AND i.FreightCostRule IS NULL AND I_IsImported='N' AND AD_Client_ID=1000000 [null] [39]
java.sql.SQLException: ORA-00904: "I"."FREIGHTCOSTRULE": invalid identifier
; State=42000; ErrorCode=904

===========> DB.saveError: DBExecuteError - ORA-00904: "I"."FREIGHTCOSTRULE": invalid identifier

Any thoughts? Otherwise I did not notice any other problems. (btw, the problem with iscustomers did not occur. )

-Rich

red1 · Posted: Thu Jul 21, 2005 9:43 am Post subject:

i took the 2 SQLs above and tested in my TOAD, only the FCR is in error and not the M_Shipper_ID, meaning that Rich ExpDat may not be latest.

But i have to solve mine also by adding FCR to I_BPartner, and now that is also solved.

Posted ExpDat_21.jar

(will be back online this noon)