UDT - No object - B1if

[Former Member]

Hi everybody,

I sucessfully replicate UDOs with B1if, but I can't do it with user-defined tables of type "No object".

Please, can anyone help me?

I will be grateful for any help you can provide.

Fernanda

[bastian_schaefer · Accepted solution]

Hi Fernanda,

for UDTs you can use the standard B1 Event Mechanism. Just activate the B1EventMonitor and you will see events with pattern: <nameofyourUDT>.

Here is an example of an INBOUND definition for a UDT called @INBOUND:

<Inbound>
 <DataRetrieval delret="true">
 <method>Retrieval</method>
 <adapter>JDBC</adapter>
 <type>SQL</type>
 <ruledoc/>
 <JDBC_SQL>
 <sql>select * from [@INBOUND] where Code = &apos;$key&apos;</sql>
 </JDBC_SQL>
 </DataRetrieval>
 <Channel Id="INB_B1_EVNT_ASYN_EVT">
 <type>SAP Business One</type>
 <mode>Asynchronous</mode>
 <trigger>B1Event</trigger>
 <obj>INBOUND</obj>
 <ident>B1 Event</ident>
 <idpar>n.a.</idpar>
 <idns/>
 </Channel>
</Inbound>

The OUTBOUND definition for a UDT - if you don't have an UDO defined based on an UDT - is B1 via SQL:

<Outbound>
 <Channel Id="OUT_B1">
 <type>SAP Business One</type>
 <format>B1i SQL</format>
 </Channel>
 <Successor Id="" Mode=""/>
 <OUT_B1>
 <sql>
 <sqlmode>single</sqlmode>
 </sql>
 </OUT_B1> 
</Outbound>

Your atom0 should have the following structure (which can be found in the B1i online help under phrase B1 outbound):

<B1out type="b1isql">

       <SQL sqlmode="single/multiple">

             <Table id="name" keylist="f1,f2,..." task="A" del="">

                    <Field id="name" value="field_value" wrapchar="true"/>

                    <Field id="fieldname" value="field_value" wrapchar=" false"/>                   
                         ...

                    <Table  id="name" keylist="f1,f2,..." task="I" del="">

                           <Field id="name" value="field_value" wrapchar="true "/>

                           <Field id="name" value="field_value" wrapchar="true "/>                          
                              ...

                    </Table>                   
                        ...                         
             </Table>

             <Table id="name" keylist="f1,f2,..." task="D" del="">                
                        ...

            
             </Table>

             ...       
        </SQL>

</B1out>

B1out/@type

Mandatory root tag B1out, mandatory attribute with the value b1isql

SQL/@sqlmode

This is the mandatory tag SQL. The sqlmode attribute is also mandatory. Set the value to “single” or “multiple”. This flag is relevant for processing multiple SQL statements. The default is “multiple”.

• The system concatenates multiple SQL statements with a semicolon. The system hands over this single SQL statement to the database. Most databases systems support this feature.
• In this mode B1i processes each SQL statement one after the other. This is necessary if the destination database system does not support semicolon-separated multiple SQL statements. This is for example true for the DB2 database system.

Table

“Table” is a fixed value. Use it to define a database table record. You can define multiple tags. You can nest the tags to represent a father-child relationship.

Table/@id

The “id” attribute specifies the name of the database table.

Table/@keylist

The “keylist” attribute represents the specification of the primary key field name. If the primary key consists of multiple concatenated field values, enter a comma-separated list of all field names.

Table/@task

Specify the operation in the database.

• task=”A” (Automatic detection)

Automatic detection decides whether an update or insert is necessary. The system detects the correct operation for each record separately using the definitions of the primary keys.

• task=”I” (Insert)
  The operation is insert. If the record already exists, this leads to duplicate entries or a unique key constraint in the database.
• task=”U” (Update)
  The operation is update. If the record does not exist, the system does not insert any record.
• task=”D” (Delete)
  The operation is delete. If the record does not exist, nothing happens.

Table/@del

If the operation is “D”, define a comma-separated list of table names where the additional deletion happens. The prerequisite for this cascading deletion is the same primary key.

Field

“Field” is a fixed value for defining a field in the database table record. You can define multiple of these tags.

Field/@id

The “id” attribute specifies the name of the database table field.

Field/@value

The “value” attribute contains the value of the field.

Field/@wrapchar

It is the default that the value is wrapped by the apostrophe character. If you want not want this because for example, if the value contains a number, set wrapchar to false.

Best regards

Bastian

P.S.: of course inserting into UDTs via SQL statements should be done very carefully.

[Former Member · Accepted solution]

Hi Bastian,

thank you very much for a so detailed answer!

I'll try this way!

Fernanda

[david_stegnitz3 · Accepted solution]

Dear experts,

could I use the same outbound method (B1isql) to Insert/Update an UDO and would that still be 'SAP-Conform'? I understand that usually I could use the B1 Service Method for UDOs, but the B1isql would just work much faster and also supports father-child table relationships.

Thanks, David

[n007praveen71 · Accepted solution]

Hello there experts,

please help me how to give inbound for UDO replication via DIAPI and i have tried my own research and made a sample scenario but i keep getting he same error as :

DoQuery Source: SAPbobsCOM.Recordset.90.0 Description: 1). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'UDO'. 2). [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Statement(s) could not be prepared.

I can see that the Object name must have an prefix of [dbo].[@UDO]. but i dont know where i should give this in Inbound please help me push data into the sender payload . pleaseeeeeee

hope you guys will notice this message and help me out thanks in advance 🙂

[former_member373665]

hi!,

have you solved the problem?,

thanks.

[Former Member]

Hi Mariano,

yes, I follow the instructions from Maria Trinidad in this post: http://scn.sap.com/thread/2093543

I hope it help you

Fernanda

[former_member373665]

Hi, thank you for your reply, but I have a doubt. I think you can´t do directly insert or update in Sap B1, do I have use an specific object for UDT??

thanks.

[Former Member]

Hi Mariano,

I had this doubt too, but Maria Trinidad said in her post: "You can use the JDBC connector to write into a UDT or any SQL table (not B1 system tables or UDO tables)."

I believed in her and develop the scenario this way

Fernanda

[former_member373665]

Hi Fernanda,

I think you are reason, I have read the post several times and I think you can modify using insert directly when you update UDT's.

Thank you