Universal Function File Exporter (UFFE)

Previous  Next |  Direct link to this topic

NB: Your SAP Partner need to have a “SAP Business One Software Development Kit” license from SAP to be allowed to use this feature. Read more here

With UFFE you are able to export data from SAP to any format using either C#/SQL or a mix. You can launch UFFE as a normal universal function or you can run it on a schedule.

UFFE supports two export types: Single and Batch.

The single export type will be described first.

image469

When creating an export, you will need a few things.

1.       A template (how the file should look)

2.       Some SQL/C# to get header data

3.       Some SQL/C# to get line data

4.       Optionally a way to mark a document/what you export as done using SQL or C#.

Field

Description

Export type

Defines if you are going to use single or batch

Body

The template field. This is where you define how the generated file should look.

Example of a simple xml format.

<?xml version="1.0" encoding="utf-8"?>

<Invoice>

<InvoiceNumber>@Get('DocNum')</InvoiceNumber>

<CustomerCode>@Get('CardCode')</CustomerCode>

<CustomerName>@Get('CardName')</CustomerName>

<InvoiceLines>

#foreach(@line in @DocumentLines)

#BEGIN

   <Line Item="@line.Get('ItemCode')" Quantity="@line.Get('Quantity')"/>

#END

</InvoiceLines>

</Invoice>

And its result (given invoice is number 373 to Earthshaker Corporation)

image470

In this case we have a very simple xml format but it could be any kind of format.

The @Get('x') are the keywords that are going to be replaced with header data.

The @x.Get('x') is  the syntax for inserting line data.

The syntax for looping trough line data and inserting data is:

#foreach(@xLine in @xLines)

#BEGIN

   <Tag Id="@xLine.Get('x')" Value="@xLine.Get('x')"/>

#END

 

NOTE: If you select XML as file type UFFE will automatically XML encode strings when using @Get('x')

NOTE: You are also able to access header data in a foreach loop by using the header syntax.

NOTE: You can substring a value by using the syntax: @Get('x',5) where 5 is the max length of the string. Example @Get('CardName',30) would only give you the first 30 characters of the CardName.

NOTE: You can use the keyword @GetRaw('x') to get the raw value and you can use @GetXmlEncoded('x') to get the an XML encoded string. They also accept a second parameter for substring.

NOTE: You can use the keyword $[TAB] to create at txt Tabulator and $[NEWLINE] to create a line break if needed.
NOTE: You have the option to use IF/ELSEIF/ELSE in your UFFE. Example:

#foreach(@bp in @BusinessPartners)

#BEGIN

#IF(@bp.Get('CardCode') = 'C20000')

#BEGIN

<BusinessPartnerSpecial>@bp.Get('CardName')</BusinessPartnerSpecial>

#END

#ELSEIF(@bp.Get('CardCode') = 'C23900')

#BEGIN

<BusinessPartnerSpecial2>@bp.Get('CardName')</BusinessPartnerSpecial2>

#END

#ELSE

#BEGIN

<BusinessPartner>@bp.Get('CardName')</BusinessPartner>

#END

#END

 

Warning: You are not allowed to use parenthesis in the columns names of the SQL result. This will break the .Get() syntax.

Body SQL Source

This is where you pull out your header data using SQL.

To fill out the template above you would write:

SELECT DocNum, CardCode, CardName FROM OINV WHERE DocEntry = DocKey@

 

DocKey@ is a keyword that will give you the id of the primary key for the type you are working with. For example, if you are launching UFFE from a document using a function button it will give you DocEntry for the document.

TIP: YOU CAN USE SPECIAL KEYWORD <NEW SQL> IF YOU WISH TO HAVE MORE SQL SENTENCES AS BODY SOURCE.

Example:

SELECT DocNum, CardCode, CardName FROM OINV WHERE DocEntry = DocKey@

<NEW SQL>

SELECT CompnyName FROM OADM

Will give you keywords “DocNum”, “CardCode”, “CardName” and “CompnyName”

NOTE: The names of the fields you pull using SQL/C# is what you can write @fieldName to get in the template. So in this case I pull out CardCode so I write @CardCode to add it into my template.

NOTE: The SAP RecordSet will trim the result and remove any prefixed/trailing whitespace characters. If you want to preserve the whitespace characters please use the ADO.NET read mode option in the B1UP main configuration window.

Body C# Source

It is also possible to pull out header data using C#.

When using C# you have full access to the SAP SDK and you will be expected to add keys with a value to a collection of keys.

You will receive a parameter called of type HeaderSourceDataParamters where you can then add new keys using:

paramters.SourceData.Add(”key”, value);

UFFE accept values of DateTime, Int, String and Double.

 

To pull the data you also get the DocKey@ and it is exposed on paramters.Key as a property.

NOTE: You can use both SQL and C# at the same time but you cannot have the same keyword returned from both SQL and C# as the keyword is unique.

C#: image472

Line sources

Line sources are where you define lines that can later be looped upon in the template using the foreach syntax. You can have multiple different line sources and you can either pull a line as SQL or C#.

image474

The key is what you will later access in the for each loop:

#foreach(@xLine in @DocumentLines)

#BEGIN

   <Line Item='@xLine.Get('ItemCode')' Quantity='@xLine.Get('Quantity')'/>

#END

And the SQL fields are the keys that will be used in the Get('x');

You can multiple for each loops in a template but the key is unique.

If you decide to use C# for pulling the line data, you will be expected to add data to a dictionary. You can access the dictionary from the paramters variable:

paramters.SourceData and you will be expected to add a new dictionary of type

new Dictionary<string, SourceDataCollection>() where the string is the lines key and the SourceDataCollection is a collection of the values as in the header data.

 

Warning: You are not allowed to use parenthesis in the columns names of the SQL result. This will break the .Get() syntax.

Mark as handled SQL

This allows you to run a SQL when an export has been done. This could be used to update a UDF a UDT or something else so that you will not export the same document twice.

Mark as handled C#

This allows you to run some C# with access to the SAP SDK to mark the export as done.

clip0073

File path

Here you define where UFFE should please the generated file. You can use the DocKey@ to make the file name unique.

Note: You can use SQL: in this field

File type

The combo box next to the file path defines what file type you are exporting. If you select XML UFFE will run XML validation when exporting and it will escape all XML characters when writing the file.

If you select custom UFFE will write the file without doing any validation.

Encoding

Here you specify what encoding UFFE should use when writing the file.

Open file after generation

If you enable this UFFE will launch the file after generation.

NB: This option is not available in SAP Browser Access

Log folder

Where should UFFE store the logs.

Default formatting

image475

This is where you define how UFFE should handle the culture variants like decimal separator and group separator. You can always format special values using SQL or C# and return the value as a string.

Batch

NOTE: Before continuing please read the single export type as this section will not cover shared features.

The batch export type is a way to support formats that need some header and footer data and multiple documents in between.

Example:

You have a format where you need to define some header data, then you need to write all open invoices and in the footer you need to summarize the value of all the invoices.

image477

Field

Description

Header

This is where you define the header template.

Header SQL source

SQL for getting header data.

Header C# source

C# for getting header data.

Footer

This is where you define the footer template.

Footer SQL source

SQL for getting footer data.

Footer C# source

C# for getting footer data.