ODS transfer fails

[Former Member]

Hi

I'm tring to get my timebased comps to ODS while transforming them to discreet. I ran into this error:

(SITE,ASSEMBLY_SFC,ASSEMBLY,ASSEMBLY_REVISION,ASSY_ID,BOM,BOM_TYPE,BOM_REVISION,BOM_COMPONENT,BOM_COMPONENT_REVISION,SEQUENCE,COMPONENT,COMPONENT_REVISION,SHOP_ORDER,CUSTOMER,CUSTOMER_ORDER,OPERATION,OPERATION_REVISION,STEP_ID,RESRCE,ASSEMBLED_DATE_TIME,ASSEMBLED_PRODUCTION_SHIFT,ASSEMBLED_BY,QTY,BOM_QTY,REF_DES,ASSEMBLY_STATE,EXCESS,TESTPART,NON_BOM,TIME_BASED,INVENTORY_ID,VENDOR,VENDOR_LOT,VENDOR_DATE_CODE,CUSTOM_INVENTORY_ID,ODS_CREATED_DATE_TIME,ODS_MODIFIED_DATE_TIME) VALUES ('EEEL1' ,'EE101108000495' ,'6556' ,'CE' ,'0' ,'6556' ,'U' ,'CE' ,'STENCIL' ,'1' ,'777' ,'STENCIL' ,'1' ,'636509' ,NULL ,NULL ,'SPI' ,'1' ,'020' ,'365-001' ,'2010-11-09T00:42:51.000Z' ,'UNKNOWN' ,'ARGOS' ,'65536.000000000000000000000000000000000000' ,'65536.000000000000000000000000000000000000' ,NULL ,'A' ,'false' ,'false' ,'false' ,'true' ,'EE-ST100826-02' ,'KOENEN' ,'75333/2' ,'100826' ,'EE-ST100826-02' ,'2010-11-09T07:00:00.000Z' ,'2010-11-09T07:00:00.000Z' )

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)

at com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeUpdate(ODSBaseDataSource.java:1109)

at com.sap.me.frame.jdbc.DynamicQuery.executeUpdate(DynamicQuery.java:857)

at com.sap.me.frame.AbstractSqlImplementation.executeUpdate(AbstractSqlImplementation.java:64)

at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:299)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:450)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:149)

at com.sap.me.ods.ODSTimeBasedExpansion.main(ODSTimeBasedExpansion.java:41)

Sun Jul 03 23:00:01 EEST 2011 ODSTimeBasedExpansion: Exception Occurred.

javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.

com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)

at com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeUpdate(ODSBaseDataSource.java:1109)

at com.sap.me.frame.jdbc.DynamicQuery.executeUpdate(DynamicQuery.java:857)

at com.sap.me.frame.AbstractSqlImplementation.executeUpdate(AbstractSqlImplementation.java:64)

at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:299)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:450)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:149)

at com.sap.me.ods.ODSTimeBasedExpansion.main(ODSTimeBasedExpansion.java:41)

javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: Error converting data type nvarchar to decimal.

at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:305)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:450)

at com.sap.me.ods.olap.ODSTimeBasedRollupProcess.ODSConvertTBToDiscrete(ODSTimeBasedRollupProcess.java:149)

at com.sap.me.ods.ODSTimeBasedExpansion.main(ODSTimeBasedExpansion.java:41)

Sun Jul 03 23:00:01 EEST 2011 ODSTimeBasedExpansion: Completed

Any ideas? It seems that the data in DB is incorrect? How to fix it?

Br,

Jennsen

[sergiy_katerinich · Accepted solution]

Hi,

I've seen such issue once. That time the original data in WIP were correct but in ODS the value of Qty was enormous. When I tried to replicate the issue by means of deleting ODS data and re-running ODS scripts, the same SFC_ASSY data were processed correctly, without error.

Thus I suppose some runtime problem in either ME code or JVM or DB driver or whatever once the issue is not replicable on constant basis.

As alternative, I believe it is possible to change the data in ODS tables manually.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi

Which tables are we talking about? I do not have a clear understanding which data is taken as base for ODSTimeBasedExpansion.

Is the it WIP or ODS resource time log? How and where to compare them?. I do not wish clear all resource time log data from ODS either. Which table should I fix?

Br,

Jennsen

[sergiy_katerinich · Accepted solution]

As I see, it is ODSTimeBasedExpansion phase, so the system is trying to move data from

ODS_COMP_TIME_LOG

ODS_COMP_TIME_REF_DES

ODS_COMP_TIME_ASSY_DATA

ODS_RESOURCE_TIME_MEMBER

to

ODS_ASSEMBLY_HISTORY.

In WIP, the origin of data for this script are:

COMP_TIME_LOG

COMP_TIME_REF_DES

COMP_TIME_ASSY_DATA

and BOM-related tables (BOM, BOM_*, SFC_BOM)

Additionally, please check if the following tables are empty now:

ODS_TEMP_COMP_TIME_LOG

ODS_TEMP_COMP_TIME_REF_DES

ODS_TEMP_COMP_TIME_ASSY_DATA

If yes, that's correct. If not, that could be the root cause of the issue.

Regards,

Sergiy

[Former Member · Accepted solution]

Hi

I looked hard and I did not find tables like

ODS_COMP_TIME_REF_DES

ODS_COMP_TIME_ASSY_DATA

I also did not find these table creation scripts from the ME server installation scripts folder. Shold they be present?

I checked tables:

ODS_COMP_TIME_LOG

ODS_RESOURCE_TIME_MEMBER

and the data there seems to be in tact.

Any other ideas?

Br,

Jennsen

[Former Member · Accepted solution]

Jennsen

There are no such tables as

ODS_COMP_TIME_REF_DES

ODS_COMP_TIME_ASSY_DATA

This data is expanded into the

ODS_COMP_TIME_LOG table

[Former Member · Accepted solution]

Hi

Now the 1M $ question why does the ODS transfer script think that there is a qty 65536.000000000000000000000000000000000000

when I can read from the ODS that there should be just 8? I do not get it.

The process should be: RESOURCE_TIME_MEMBER records are matched against COMP_TIME_LOG and thus the SFC -> INV_ID binding is done and written into SFC_ASSY table, right?

How can i debug it in more detail?

Br,

Jennsen

[Former Member · Accepted solution]

Jennsen

You should open a separate support ticket for this - the Java code has altered the quantity field and the SQL is now failing as a result of this.