Spain SII: error when sending

[agustin_marcoscividanes]

Hi forum

I am facing an issue when I send the XML to the AEAT, and the process fails. Searching in the B1if logs I found an error message:

<SqlExceptions xmlns="urn:com.sap.b1i.adapter:jdbcadapter"><SqlException><ErrorCode>207</ErrorCode><Message>Invalid column name 'U_B1SYS_DIRECT_DEBIT'.</Message><SqlState>S0001</SqlState></SqlException></SqlExceptions>

The query that generates the error:

<Payload Role="C" id="atom3" system="0010000104" mode="single" method="Query Statement(Query Statement)" plr="5" dbtype="SQL" blockExecution="false" compatibilityMode="true" delimiter=";" commitMode="direct" sql="select top 1 [ECM2].[AbsEntry], [ActStatus], [LogData] as ActData, [Code], [ReportID], [SrcObjAbs], COALESCE(U_B1SYS_DIRECT_DEBIT, 'N') as "DirectDebit" from [ECM2] left join ECM3 on [ECM2].AbsEntry = [ECM3].AbsEntry left join [OVPM] on [ECM2].SrcObjAbs = [OVPM].DocEntry and [ECM2].SrcObjType = [OVPM].ObjType left join [OBOE] on [OVPM].BoeAbs = [OBOE].BoeKey left join [OPYM] on [OBOE].PayMethCod = [OPYM].PayMethCod where [LogType]='S' AND [ActStatus]='P' AND [ECM2].[AbsEntry]= 18 order by "LogNum" DESC" disable-output-escaping="false">

Any idea to solve?

Kind regards

Agustín

[agustin_marcoscividanes · Accepted solution]

Hi jesper.magnusson

we are in the next enviroment: SBO 9.3 PL03 and SII 1.1.

We were working in this issue and we updated the scenario, deleting the field in the atom and we published again.

Kind regards

Agustín

[Johan_Hakkesteegt]

Hi Agustin,

Just thinking out loud, in the query, the user defined field is implicitly mentioned (no table reference). Also, B1 is very specific about it upper and lower case of user defined field names.

Regards,

Johan

[JesperB1]

Hello,

Please check that you have the right scenario version with the correct B1 version.

SAP Note 2494985 - ES_Immediate Information Supply - Communication Setup

I hope it helps,

Jesper

Support Spotlight Series:

https://blogs.sap.com/2018/01/17/support-spotlight-tips-from-sap-business-one-support/

[agustin_marcoscividanes]

Hi jesper.magnusson

we are in the next enviroment: SBO 9.3 PL03 and SII 1.1.

We were working in this issue and we updated the scenario, deleting the field in the atom and we published again.

Kind regards

Agustín

[agustin_marcoscividanes]

Hi johan.hakkesteegt

the query is built by B1if or EFM, and this field does not exist in the database. I would like to know if it is a bug or what solution I can apply, I was looking for support notes and I did not find information.

Kind regards

Agustín

[Johan_Hakkesteegt]

Hi Agustin,

That would explain why there is no table reference for that field. Unless the configuration as copied from another system, or the system was upgraded after this configuration was set up, it sounds like a bug. Probably best to log a support ticket with SAP.

Regards,

Johan

[JesperB1]

Hello Augustin,

This to me would indicate that there is a version mismatch and also why I supplied the note as it lists all the changes done to the scenario.

What version of B1 are you using and what scenario package?

Jesper

[agustin_marcoscividanes]

Hi Jesper

we checked the note and no solution.

Kind regards

Agustín