red1.org

[trifon]

ViewTest.xml had a typo mistake in latest version.
I had written <!- instead <!--
I fixed it and uploaded new version.
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ViewTest.xml

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

I posted a 7mb log (mylog1 in the ftp root directory) of ALL from running Line Comment Import (from login to quitting). For some reason, even though I piped all of the output to that log file, the following showed up on screen and was not included in the file. It seemed useful so I post it:
-----------> MLookup.run: C_BPartner_Location.C_BPartner_Location_ID: Loader - Too many records [24]
-----------> MLookup.run: C_BPartner_Location.C_BPartner_Location_ID: Loader - Too many records [23]
-----------> MLookup.run: C_BPartner_Location.C_BPartner_Location_ID: Loader - Too many records [34]
-----------> MLookup.run: C_BPartner_Location.C_BPartner_Location_ID: Loader - Too many records [35]
===========> DB.executeUpdate: Update=UPDATE I_Order o SET C_OrderLine_ID=(SELECT C_OrderLine_ID FROM C_OrderLine l WHERE l.M_Product_ID = o.M_Product_ID AND l.C_Order_ID = o.C_Order_ID AND l.AD_Client_ID = o.AD_Client_ID ) WHERE C_Order_ID IS NOT NULL AND C_OrderLine_ID IS NULL AND M_Product_ID IS NOT NULL AND I_IsImported<>'Y' AND AD_Client_ID=1000000 [null] [42]
java.sql.SQLException: ORA-01427: single-row subquery returns more than one row
; State=21000; ErrorCode=1427
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:3753)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:8829)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2886)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement.java:157)
at org.compiere.util.DB.executeUpdate(DB.java:679)
at org.compiere.util.DB.executeUpdate(DB.java:633)
at org.compiere.process.ImportOrder.doIt(ImportOrder.java:201)
at org.compiere.process.SvrProcess.process(SvrProcess.java:93)
at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:77)
at org.compiere.apps.ProcessCtl.startProcess(ProcessCtl.java:442)
at org.compiere.apps.ProcessCtl.run(ProcessCtl.java:225)

===========> DB.saveError: DBExecuteError - ORA-01427: single-row subquery returns more than one row
[42]
-----------> ImportOrder.doIt: No DocType=116 [42]
-----------> ImportOrder.doIt: Invalid UOM=0 [42]

[trifon]

I think that i fixed problem with Sold To and Ship To.
I commited new versi0on into CVs server.

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Trifon can u also help to find out how to make our logger to give the running time as it happens at every logging prompt? Now the time shown on the left is 'frozen' to the time it first launched.

[riche]

Trifon-
See my post above about the order line comment import. I edited a previous post, so it posted above.

-Rich

[riche]

Trifon-
Inventory import isn't working. I get errors on console and nothing loaded into I_inventory except items from GardenWorld, which is really strange because I'm loading hutzler data. Some sample lines are:
Item .,On Hand (Count) Qty,Locator Key,X,Y,Z
#18 BOX,825,A,-,-,-
#369 BOX,300,A,-,-,-
100-62,30,985,985,-,-
1200.,5,1019E,1019,E,-

I set book Qty=0 for every line. if the locator key doesn't exist, it should create it using the XYZ coordinates, as we agreed.

Some errors from the console:
===========> DB.executeUpdate: Update=UPDATE I_Inventory SET M_Product_ID='RSC MAKE IT INS',QtyCount='1700',QtyBook=0,IsActive='Y',Processed='N',I_IsImported='N',Updated=SysDate,UpdatedBy=100 WHERE I_Inventory_ID=1002817 [null] [192]
java.sql.SQLException: ORA-01722: invalid number
; State=42000; ErrorCode=1722
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:3753)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:8829)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2886)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement.java:157)
at org.compiere.util.DB.executeUpdate(DB.java:679)
at org.compiere.util.DB.executeUpdate(DB.java:633)
at org.compiere.impexp.ImpFormat.updateDB(ImpFormat.java:590)
at org.compiere.apps.form.VFileImport.cmd_process(VFileImport.java:382)
at org.compiere.apps.form.VFileImport.access$000(VFileImport.java:36)
at org.compiere.apps.form.VFileImport$1.construct(VFileImport.java:213)
at org.compiere.apps.SwingWorker$2.run(SwingWorker.java:65)
at java.lang.Thread.run(Thread.java:534)

===========> DB.saveError: DBExecuteError - ORA-01722: invalid number
[192]
===========> ImpFormat.updateDB: I_Inventory_ID=1002817 - rows updated=-1 [192]
java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:3753)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:8829)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2886)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement.java:157)
at org.compiere.util.DB.executeUpdate(DB.java:679)
at org.compiere.util.DB.executeUpdate(DB.java:633)
at org.compiere.impexp.ImpFormat.updateDB(ImpFormat.java:590)
at org.compiere.apps.form.VFileImport.cmd_process(VFileImport.java:382)
at org.compiere.apps.form.VFileImport.access$000(VFileImport.java:36)
at org.compiere.apps.form.VFileImport$1.construct(VFileImport.java:213)
at org.compiere.apps.SwingWorker$2.run(SwingWorker.java:65)
at java.lang.Thread.run(Thread.java:534)
SQLException: SQLState(42000) vendor code(1722)
===========> DB.executeUpdate: Update=UPDATE I_Inventory SET M_Product_ID='RSC PP FOL MAST',QtyCount='255',QtyBook=0,IsActive='Y',Processed='N',I_IsImported='N',Updated=SysDate,UpdatedBy=100 WHERE I_Inventory_ID=1002818 [null] [192]
java.sql.SQLException: ORA-01722: invalid number
; State=42000; ErrorCode=1722
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:3753)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:8829)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2886)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement.java:157)
at org.compiere.util.DB.executeUpdate(DB.java:679)
at org.compiere.util.DB.executeUpdate(DB.java:633)
at org.compiere.impexp.ImpFormat.updateDB(ImpFormat.java:590)
at org.compiere.apps.form.VFileImport.cmd_process(VFileImport.java:382)
at org.compiere.apps.form.VFileImport.access$000(VFileImport.java:36)
at org.compiere.apps.form.VFileImport$1.construct(VFileImport.java:213)
at org.compiere.apps.SwingWorker$2.run(SwingWorker.java:65)
at java.lang.Thread.run(Thread.java:534)

===========> DB.saveError: DBExecuteError - ORA-01722: invalid number
[192]
===========> ImpFormat.updateDB: I_Inventory_ID=1002818 - rows updated=-1 [192]
java.sql.SQLException: ORA-01722: invalid number

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:181)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:543)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1028)
at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:3753)
at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:8829)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2886)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:2960)
at org.compiere.util.CPreparedStatement.executeUpdate(CPreparedStatement.java:157)
at org.compiere.util.DB.executeUpdate(DB.java:679)
at org.compiere.util.DB.executeUpdate(DB.java:633)
at org.compiere.impexp.ImpFormat.updateDB(ImpFormat.java:590)
at org.compiere.apps.form.VFileImport.cmd_process(VFileImport.java:382)
at org.compiere.apps.form.VFileImport.access$000(VFileImport.java:36)
at org.compiere.apps.form.VFileImport$1.construct(VFileImport.java:213)
at org.compiere.apps.SwingWorker$2.run(SwingWorker.java:65)
at java.lang.Thread.run(Thread.java:534)
SQLException: SQLState(42000) vendor code(1722)

[trifon]

[trifon]

[trifon]

I posted new version of ILF.
Removed one row in Order Header.
seq 180.

This is SQL statement that sets Product Locator by WHSLC

UPDATE M_Product p
SET M_Locator_ID = ( SELECT M_Locator_ID
FROM M_Locator l
WHERE l.Value = p.WHSLC
AND l.AD_Client_ID = p.AD_Client_ID
)
WHERE WHSLC IS NOT NULL
AND EXISTS ( SELECT M_Locator_ID
FROM M_Locator l
WHERE l.Value = p.WHSLC
AND l.AD_Client_ID = p.AD_Client_ID
)

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

Hi Rich,

in order to fix lateste problem with Document type of Material Receipt, please do this:

Login with System, go to "Validation Rules" window, find "C_DocType Material Shipments/Receipts" and set "C_DocType.DocBaseType IN ('MMR', 'MMS') AND C_DocType.IsSOTrx='@IsSOTrx@'" in Validation code.

This must fix the problem.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

Trifon-
I made the change and now there are no documents to choose from for doctype.

-Rich

[red1]

try this C_DocType.DocBaseType IN ('MMR', 'MMS') AND C_DocType.IsSOTrx='@IsSOTrx@'

[trifon]

Hi,

I Uploaded new version of ViewTest.xml.

It added new parameter to Import Inventory process.
Parameter Name is "Create New Locators", when enabled import process will create new locators. In othere case it will import inventory.

You can add this new parameter with Compiere functionality. The only manadatory thing is ColumnName to be CreateNewLocators.

<processpara name="Create New Locators" description="Create New Locators" columnname="CreateNewLocators" element="CreateNewLocators" reference="Yes-No" mandatory="N" />

Commited ImportInventory into CVS Server.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

Trifon-
Compiere will not allow me to process or complete orders that were imported. I am told that the "period is not open", but the periods for all of the ship and entry dates are open. Is it possible that because the "date created" is after the "date entered" that I get this error? I don't have this problem with new orders.

Update: After making Dateacct = August/current month (I had to make the fields write-able), I was allowed to process the order. The dateacct was = July 29 before I changed it. Period of July is open though, so I don't know why this is a problem. Any thoughts? I would prefer for these to register in July, but it's not a big problem since orders don't create accounting entries.

Also, can you give me an SQL statement to set all dateacct fields that were = to July 29, 2005 to be = Aug 1, 2005?

Thanks,
Rich

[trifon]

This is SQL that changes dates to 1-August.

UPDATE C_Order
SET DateAcct = TO_DATE('2005-08-1', 'YYYY-MM-DD')
WHERE DateAcct = TO_DATE('2005-07-29', 'YYYY-MM-DD')
AND AD_Client_ID = 1000000

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

I Finished Import Print Format and Print Format Item functionality.

It took me 3 days, instead of 1 as i thought at the beggining.

Sources are commited into CVs Server:
ImportPrintFormat.java
ImportPrintFormatItem.java
and ImpHelper.java

Uploaded new versions of:
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/CreateTables-Oracle.sql
http://red1.org/barn/HutzlerCompiereUSA/sources/druid/ViewTest.xml

My next task is EDI Imports.

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

Hi Rich,

I need some comments on EDI imports.

I uploaded Import Loader Format2.xls into EDI folder.
http://red1.org/barn/HutzlerCompiereUSA/EDI/Import Loader Format2.xls

It contains threee excel spread sheets:
"Loader Format"
"Lodaer Format Row"
"Column Mapping" - contains details about each column as decribed in .doc file.

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

This is SQL that wil create table "M_Product_BPartner_Comment"

CREATE TABLE M_Product_BPartner_Comment
(
C_BPartner_ID NUMBER(10,0) NOT NULL,
M_Product_ID NUMBER(10,0) NOT NULL,
AD_Client_ID NUMBER(10,0) NOT NULL,
AD_Org_ID NUMBER(10,0) NOT NULL,
IsActive CHAR(1) DEFAULT 'Y' NOT NULL,
Created DATE DEFAULT SYSDATE NOT NULL,
CreatedBy NUMBER(10,0) NOT NULL,
Updated DATE DEFAULT SYSDATE NOT NULL,
UpdatedBy NUMBER(10,0) NOT NULL,
Prod_Comment NVARCHAR2(2000),
primary key(C_BPartner_ID,M_Product_ID),
CHECK(IsActive IN ('Y', 'N'))
);

I commited X_M_Product_BPartner_Comment into CVS Server.

I created new folder in http://red1.org/barn/HutzlerCompiereUSA/sources/
it is named druid2
and contains xml that will create only M_Product_BPartner_Comment table in AD and new tab "Cust Product" user Customer in Business partner window.

next step is "3" from our plan:
1 ) create table
2 ) create window
3 ) create code for populating into sales order
4 ) create import functionality

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

I created functionality to populate Order Line with comments from BParter-product.

Sources are commited into CVS server.

Next step 4.
4 ) create import functionality

Regards,
Trifon
_________________
Trifon Trifonov

[trifon]

Functionality for import of BPartner-Prodiuct Comment is developed.

Please delete table and re-create it if you have created it already.
Old table had two Primary keys, this has only one.

Use this SQL for creation of table.
CREATE TABLE M_Product_BPartner_Comment
(
M_Product_BPartner_Comment_ID NUMBER(10,0) NOT NULL,
C_BPartner_ID NUMBER(10,0) NOT NULL,
M_Product_ID NUMBER(10,0) NOT NULL,
AD_Client_ID NUMBER(10,0) NOT NULL,
AD_Org_ID NUMBER(10,0) NOT NULL,
IsActive CHAR(1) DEFAULT 'Y' NOT NULL,
Created DATE DEFAULT SYSDATE NOT NULL,
CreatedBy NUMBER(10,0) NOT NULL,
Updated DATE DEFAULT SYSDATE NOT NULL,
UpdatedBy NUMBER(10,0) NOT NULL,
Prod_Comment NVARCHAR2(2000),
primary key(M_Product_BPartner_Comment_ID),
CHECK(IsActive IN ('Y', 'N'))
);

Source files are commited into CVS server.

Uploaded new version of:
http://red1.org/barn/HutzlerCompiereUSA/sources/druid2/DruidSummary.xml
http://red1.org/barn/HutzlerCompiereUSA/sources/druid2/ViewTest.xml
http://red1.org/barn/HutzlerCompiereUSA/sources/druid2/ALTER_Table.sql

ALTER_Table contains only sql statement that add new column to I_Product table.

ALTER TABLE I_Product
ADD ImportComment CHAR(1);

I created new ILF "Import Hutzler Bpartner-Product Comments"
It is very simple and contains only threee rows, so it can be created manualy, i created it only for the protocol and in order to have all ILF in written form.

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

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

[trifon]

1 )
I need to know what is stored in
‘PKG_SERVICE_TYPE’ - Level of Service Code column?
Is it Shipper code or name?

2 )
I need to know how do you use
‘PKG_CUSTOM2’ - 2nd Package Reference; This will be the Merchant SKU.

I posted new version of ImportLoaderIQVC2.xls. I have made some modifications to mappings.

Regards,
Trifon
_________________
Trifon Trifonov

[riche]

[trifon]

[riche]