Connector

A CSV connector exports data to CSV files. You configure the data to export and the transformation rules with a "Mapping". You can also develop a specific connector to take specific business rules into account and send the data in a specific format.

beCPG provides a standard connector that extracts entities and their datalists in CSV format. It can also export documents (images, technical sheets) in their native format.

Version

There are different versions of the connector depending on your version of beCPG. We recommend using the latest stable version of the connector. The connector.isV1APi option activates compatibility with version 1.

Some connector options are not compatible with all API versions. The documentation indicates this with [>=3.2.1].

Installation

Install a Java JDK on your machine, in a version that matches your version of the connector. Download and unzip the file: becpg-connector-3.2.2-distribution.zip

If your application uses HTTPS and Java does not recognize the SSL certificate, you must install the certificate. For Windows, see Annex I.

Use

On Linux, run the following command in a terminal:

./becpg-connector.sh

On Windows, double-click becpg-connector.bat.

In a Docker environment, create your own launch script and mount the Docker volume corresponding to the file destination path:

Example of docker launch script:

docker run --rm -v "$PWD":/usr/becpg-connector -v "$PWD"/data:/usr/becpg-connector/data -w /usr/becpg-connector/data \
   --net inst1_internal --link inst1_becpg-mail_1 openjdk:8 java -Dfile.encoding=UTF8 \
   -jar /usr/becpg-connector/lib/becpg-connector-3.2.0-jar-with-dependencies.jar -file=/usr/becpg-connector/becpg-connector.properties >> $TMPFILE 2>&1

CSV files are created in connector.dest.path.

Examples of generated files:

csv/:
bcpg_client.csv           bcpg_semiFinishedProduct.csv  compoList.csv  organoList.csv     physicoChemList.csv
bcpg_finishedProduct.csv  bcpg_supplier.csv             nutList.csv    packagingList.csv

Configuration

To configure the connector, edit becpg-connector.properties. You can also override these parameters in the launch script with the Java -D option [>=3.0.0].

Example:

java -Dremote.scheme=https -Dremote.host=server.becpg.local -Dremote.port=443 
      -Dconnector.query.template="+TYPE:\"bcpg:finishedProduct\""
      -Dconnector.dest.path=".\data\case-units" 
      -jar ./lib/becpg-connector-3.2.0-jar-with-dependencies.jar -file=becpg-connector-case-units.properties

The becpg-connector.properties file is specified with the -file=sample-config.properties option.

Server and destination

First, you must indicate the address of the beCPG server:

remote.scheme=http
remote.host=localhost
remote.port=8080
remote.login=admin
remote.password=becpg

You must also indicate the destination path of the CSV files (the current folder by default):

connector.dest.path=.

Warning: on Windows, write the path as c:\\Temp\\becpg-connector (or c:/Temp/becpg-connector)

Notifications

You can send email notifications after the connector runs:

notify.enabled=false
connector.notify.from=support@becpg.fr
connector.notify.to=support@becpg.fr

mail.smtp.host=localhost
mail.smtp.port=25

You can also send files over FTP after the connector runs:

ftp.protocol=ftp or ftps or sftp (default is ftp) [ >= 3.0.0]
ftp.enabled=false
ftp.host= 
ftp.port=21
ftp.login= 
ftp.password=
ftp.destFolder=
ftp.deleteOnSuccess=false

Finally, you can write properties in the PLM for each synchronized entity, using the XML format of the API [>=3.0.0]:

plm.activity.enabled=false
plm.activity.syncRemoteXml=<gs1:isPublicated xmlns:gs1="http://www.bcpg.fr/model/gs1/1.0" type="d:boolean" >false</gs1:isPublicated> \
                           <gs1:publicationDate xmlns:gs1="http://www.bcpg.fr/model/gs1/1.0" type="d:date" >%s</gs1:publicationDate>

Compatibility

When the list of fields to extract is too long, you need to compress the headers [API >= 2.1]:

connector.compress.param=true

Or to use API VI [>=3.0.0]:

connector.isV1APi=true

Post-connector Actions

You can execute actions after the connector has run, using the following parameters:

connector.channel.onSuccess.remote=
connector.channel.onError.remote=

Here is an example of JSON to apply to the entity after the connector has run, in the remoteEntity format:

connector.channel.onSuccess.remote={"attributes": {"bcpg:productState": "Simulation"}}

Creation of a backup

For CSV exports, you can either append to the existing CSV files (connector.csv.appendToExistingFiles=true) or create a backup folder (connector.csv.appendToExistingFiles=false) into which the old CSV files are moved. To do so, use connector.csv.appendToExistingFiles (absent by default, which means false).

To append to existing CSV files without creating a backup folder:

connector.csv.appendToExistingFiles=true

Debugging

You can raise the debugging level with the connector.debug.level property, passed as a command-line argument:

-Dconnector.debug.level=trace
-Dconnector.debug.level=debug
-Dconnector.debug.level=info 
-Dconnector.debug.level=warn
-Dconnector.debug.level=error

By default, level INFO is used.

SSL

You can disable SSL verification with the following option (for testing only, for security reasons) [>=3.2.2]:

ssl.trustAll=true

Plugin configuration

One or more plugins can be used to extract PLM data [>= 3.0.0]:

To specify one or several plugins, use the following parameters:

connector.plugin.class=fr.becpg.connector.plugin.StandardCSVVisitor,fr.becpg.connector.plugin.StandardDocumentVisitor,fr.becpg.connector.plugin.WorkflowCSVVisitor

Standard configuration

CSV format

The standard plugin 'fr.becpg.connector.plugin.StandardCSVVisitor' lets you specify the output CSV format with the connector.csv.format option:

connector.csv.format=

For more details on the formats:

https://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVFormat.html

Other options specify the separator for associations (comma by default) [>=3.0.0]:

mapping.csv.assoc_separator=|

The number format (scientific notation by default) [>=3.2.1]:

connector.csv.decimalFormat=#.####

And the date format (ISO notation by default):

connector.csv.dateFormat=dd/MM/yyyy

https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/DecimalFormat.html

Configuration of the entities to extract

The list of entities to extract is configured by a search query where %s is replaced by the date of the last import. This date is saved in the lastImport file, which you can modify or delete manually.

connector.query.template=(@cm\\:created:[%s TO MAX] OR @cm\\:modified:[%s TO MAX]) AND ( ASPECT:\"bcpg:productAspect\" OR TYPE:\"bcpg:client\" OR TYPE:\"bcpg:supplier\" )

In this example, all entities of type "product", as well as suppliers and clients, created or modified since the last import are extracted.

Configuration of the fields to extract

For each entity type, specify the fields to export in the CSV files.

Export of entities

For each file to extract, use the mapping.csv key prefix followed by the entity type, for example mapping.csv.bcpg_finishedProduct for Finished products. By default, the CSV file is named after the type, e.g. bcpg_finishedProduct.csv.

You can also redefine the name of the created file [>=3.2.1]:

mapping.csv.bcpg_finishedProduct.fileName=Custom file name

You can map up to 10 files for the same type [>=3.2.1]:

mapping.csv.bcpg_finishedProduct.1.fileName=Sample file name
mapping.csv.bcpg_finishedProduct.1=bcpg:code,bcpg:erpCode,cm:name

You can also specify aliases to map multiple types with the same mapping [>=3.2.2]:

mapping.csv.bcpg_rawMaterial.alias=article
mapping.csv.bcpg_semiFinishedProduct.alias=article
mapping.csv.article=bcpg:entityTplRef,cm:name,cm:title,bcpg:productHierarchy1|bcpg:erpCode

Example of the mapping for the finished product type:

mapping.csv.bcpg_finishedProduct=bcpg:code,bcpg:erpCode,cm:name,cm:title,bcpg:eanCode,bcpg:code,bcpg:legalName,cm:description,bcpg:entityTplRef\
                    ,bcpg:productHierarchy1,bcpg:productHierarchy2,bcpg:bestBeforeDate,bcpg:certificateDate,bcpg:contractDate\
                    ,bcpg:foodContact,bcpg:netWeightUnit,bcpg:numberOfServings,bcpg:packagingDescription,bcpg:periodAfterOpening\
                    ,bcpg:precautionOfUseRef,bcpg:preparationTips,bcpg:priceCurrency,bcpg:productComments,bcpg:productDensity\
                    ,bcpg:productMicrobioCriteriaRef,bcpg:productQty,bcpg:productSpecifications,bcpg:productState,bcpg:productUnit\
                    ,bcpg:projectedQty,bcpg:servingSize,bcpg:startEffectivity,bcpg:storageConditionsRef\
                    ,bcpg:supplierPlants,bcpg:suppliers,bcpg:suppliers|bcpg:code,bcpg:suppliers|bcpg:erpCode\
                    ,bcpg:clients,bcpg:clients|bcpg:code,bcpg:clients|bcpg:erpCode,bcpg:unitPrice,bcpg:useByDate

Each field is specified by an internal name bcpg:code (for example).

You can specify the path of a document to share it and get its public share URL.

Example to export the public share URL of documents:

/Images/produit.jpg,/Documents/${cm:name} - Technical sheet.pdf

Here, the system shares the product.jpg document and adds its public share URL to the CSV.

You can specify several paths in the same column: the system tests each path until it gets a URL. Example:

/Images/produit.jpg|/Images/product.jpg

For associations, the name is exported by default. You can specify the association properties to extract with the assoc_name|prop_name syntax. The bcpg:nodeRef attribute is also available: assoc_name|bcpg:nodeRef.

Example to export the ISO code of an association:

bcpg:ingListGeoOrigin|bcpg:geoOriginISOCode

The standard connector can export multilingual data from a field of type "d:mltext". To export the different values of this field, add the language code after the field name ("locale").

Example:

cm:description_en, to export description in English

bcpg:legalName_ja_JP, to export the Japanese legal name.

Exporting lists

As for entities, define for each list a mapping of the fields to export. Examples for the nutrient and packaging lists:

mapping.csv.nutList=entity|bcpg:code,entity|bcpg:erpCode,bcpg:nutListNut,bcpg:nutListValue,bcpg:nutListFormulatedValue,bcpg:nutListUnit
mapping.csv.packagingList=entity|bcpg:code,entity|bcpg:erpCode,bcpg:packagingListProduct,bcpg:packagingListProduct|bcpg:erpCode,bcpg:packagingListQty,bcpg:packagingListUnit,bcpg:packagingListLossPerc,bcpg:packagingListPkgLevel,bcpg:packagingListIsMaster

Fields prefixed with entity| export fields of the entity (article| is a synonym).

Example:

 entity|bcpg:code, entity|bcpg:erpCode, entity|bcpg:nodeRef

Some specific lists have their own fields [>=3.0.0].

Nutrient list

• nut["Code"] ("locale"): gets the rounded nutrient value per country

Example:

• nut["ENER-KJO"] or nut["ENER-KJO"]("EU") or nut[ENER-KJO](EU): European rounded energy value
• nut["FAT"]("US"): US rounded value for fats

You can also display the following rounded values in bcpg:nutList:

• roundedValue_locale
• roundedSecondaryValue_locale
• roundedSecondaryValuePerServing_locale
• roundedMini_locale
• roundedMaxi_locale
• roundedValuePerServing_locale
• roundedGDAPerc_locale
• roundedValuePerContainer_locale
• roundedGDAPercPerContainer_locale

Example:

roundedValue_EU

Note: the mapping must contain the following fields:

mapping.csv.nutList=bcpg:nutListNut|gs1:nutrientTypeCode,bcpg:nutListRoundedValue

Claim list

claim[Code] - Returns true if the claim criteria (nutrition/health) are met.

Example:

claim[ORGANIC_GS1]

Returns "true" if the product is organic.

Note: the mapping must contain the following fields:

mapping.csv.labelClaimList=bcpg:lclLabelClaim|bcpg:labelClaimCode,bcpg:lclClaimValue,bcpg:lclComments

Ingredient List

ingLabelingList[label]|value - Returns manual labeling if available, otherwise the formulated labeling corresponding to the "label".

ingLabelingList[label]|manualValue [>=4.1.1] - Returns manual labeling corresponding to the "label".

ingLabelingList[label]|formulatedValue [>=4.1.1] - Returns formulated labeling corresponding to the "label".

The "label" can be either the index or the name of the label.

By default, HTML is removed from the labeling. By adding "@" at the end, you can get the version with HTML.

Example:

ingLabelingList[Ingredient List]|value

Note: the mapping must contain the following fields:

mapping.csv.ingLabelingList=bcpg:illManualValue,bcpg:illValue,bcpg:illGrp|bcpg:lrLabel

Allergens

• voluntaryAllergen: voluntary allergen legal names separated by commas
• voluntaryAllergen|code: voluntary allergen codes separated by commas
• inVoluntaryAllergen: involuntary allergen legal names separated by commas
• inVoluntaryAllergen|code: involuntary allergen codes separated by commas

You can retrieve the legal names of voluntary and involuntary allergens in the desired language by adding an underscore and the language code.

Example:

voluntaryAllergen_en returns the legal names of voluntary allergens, separated by commas, in English.

Note: the mapping must contain the following fields:

mapping.csv.allergenList=bcpg:allergenListAllergen|bcpg:allergenCode,bcpg:allergenListAllergen|bcpg:legalName,bcpg:allergenListVoluntary,bcpg:allergenListInVoluntary

Other lists

listName[0]|prop - Returns the property prop of item 0 in the listName list.

Example:

compoList[0]|bcpg:compoListQty

Configuration via an Excel file

The mapping is declared in becpg-connector.properties by default, but you can also define it in an Excel file with the following option:

connector.csv.xlsxMapping=./sample/mapping.xlsx

The file headers are the following:

• Each tab of the file corresponds to a CSV file.
• A tab without mapping is not exported.
• Excel formulas can be used in the headers.
• #MyValue in a column indicates a fixed value.

Example:

You can define the same mapping for multiple types. For example:

| TYPE | bcpg:finishedProduct,bcpg:semiFinishedProduct |

You can also define a mapping for a list of a specific type, for example:

| TYPE | bcpg:rawMaterial|nutList |

Formulas

In addition to extracting fields, you can use SPEL formulas in the field mapping. A formula column has 3 parts:

• Part 1: the word formula (static word), which indicates that this column is a SPEL formula
• Part 2: a vertical bar |
• Part 3: the formula

SPEL formulas let you customize the displayed columns. The simplest formula displays the value of a property (standard, dynamic, calculated).

Example:

 formula|props["bcpg:erpCode"] 

This formula returns the ERP code of the entity.

The props list contains all properties of the entity. To get the value of a property, write props followed by the property name in brackets.

props[ "PROPERTY_NAME"]

For properties of associations, replace the pipe | with \|:

props["bcpg:clients\|cm:title"]

To write complex formulas with conditions:

formula|props["qty"] != null ? props["qty"] / prop["bcpg:compoListQty"] : props["qty"]

which means:

If the qty property is not empty (null), calculate the percentage (qty / compoListQty). Otherwise, display qty. SPEL formulas can be used for mathematical or logical operations, or to customize the data display.

Example to format a value:

formula|(new java.text.DecimalFormat("#.###")).format(props["qty"])

For more information on the SPEL language, see:

http://docs.spring.io/spring/docs/3.2.x/spring-framework-reference/html/expressions.html

Mandatory fields

You can define a list of mandatory fields with the connector.csv.mandatoryFields option.

When a mandatory field is defined and a mapping contains this field, the system does not extract, for this mapping, the entities for which this field is null or empty.

Example:

connector.csv.mandatoryFields=bcpg:useByDate,bcpg:unitPrice

Configuration of the document connector

The document connector is a plugin of the standard connector. To activate it, add the following line to the becpg-connector.properties file:

connector.plugin.class=fr.becpg.connector.plugin.StandardDocumentVisitor

This line is present in the configuration file by default, but commented out with a #. Uncomment it by deleting the # symbol at the beginning of the line.

Note: you can choose which files are generated or extracted for each entity type (Finished product, Raw material, etc.).

Each entity has a mapping as below:

mapping.document.bcpg_<entity type> = <fields to be extracted>

Default mappings can be used to extract technical files and images for Raw materials, Semi-finished products and Finished products:

mapping.document.bcpg_finishedProduct=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf
mapping.document.bcpg_rawMaterial=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf
mapping.document.bcpg_semiFinishedProduct=/Images/produit.jpg|img_produit${bcpg:erpCode}.jpg;/Documents/${cm:name} - Fiche Technique Client.pdf|FT_client${bcpg:erpCode}.pdf

Each entity to extract is configured the same way:

• the path on the instance of the file to extract
• a vertical line |
• the name (and path) of the extracted file. This lets you choose the name of the file and whether to save it in another folder.

Each file to extract is separated from the next by a semicolon (;). Two variables can be used in the name of the source file (in beCPG) and the name of the target file (on your system): the ERP code of the product and the product name. Therefore, you can extract technical sheets into a folder named "ERP code - entity name":

/Documents/${cm:name} - Customer Technical Sheet.pdf|${bcpg:erpCode} - ${cm:name}/FT_client${bcpg:erpCode}.pdf

Configuration of workflow connectors

The workflow connector exports workflows and their tasks in CSV format. To activate it, add the following line to becpg-connector.properties:

connector.plugin.class=fr.becpg.connector.plugin.WorkflowCSVVisitor

The following mappings configure the fields to extract:

mapping.csv.workflow_instances=id,name,title,description\
                                    ,package|cm:name,formula|props["package\\|path"]!=null ?props["package\\|path"].split("/")[props["package\\|path"].split("/").length-1]:""\
                                    ,isActive,startDate,priority,message,endDate,dueDate,initiator.userName,initiator.firstName,initiator.lastName

    mapping.csv.workflow_tasks=workflowInstance|id,workflowInstance|name,id,state,name,title,description,bpm:completionDate,bpm:workflowDueDate

The connector.query.template property filters workflows:

    connector.query.template=startedAfter=%sZ

Possible values are:

state,initiator,priority
 ,dueBefore,dueAfter,
 ,definitionId,definitionName,
 ,startedBefore,startedAfter
 ,completedBefore,completedAfter,exclude

Multilevel Connector configuration

The multilevel connector is a plugin of the standard connector that extracts entities in CSV format, along with their composition data lists, packaging data lists and other data lists.

As its name suggests, the "multilevel connector" shows the level of each composition item.

In other words, this plugin extracts the composition of a Semi-finished product within a Finished product, or of a Semi-finished product within another Semi-finished product, and so on. It also shows the level of each component.

Example:

Composition of a Finished product:

Calculation of the quantity of a semi-finished product in a product

The level of semi-finished C is equal to 3. So, to calculate its quantity at product level, we must first calculate the quantity of semi-finished B at product level, using a rule of three:

Semi-finished B QTY = (Semi-finished B Weight / Semi-finished A Weight) * Semi-finished A Weight at the product level 

• Weight of semi-finished A = sum of its components, which can be found in the formulated characteristics

• Semi-finished B QTY = (0.31 / 0.66) * 5 = 2.348 kg.

You then use this result to calculate the quantity of semi-finished C, and so on as you go down the composition levels.

In the example:

• Quantity of semi-finished B at product level => after calculation = 2.348 kg.

• Semi-finished B weight = 0.17 kg;

• Semi-finished C quantity used in the composition of semi-finished B = 0.1 kg.

• The quantity of semi-finished C used at product level = (0.1 / 0.17) * 2.348 = 1.381 kg.

The quantity of raw material 3 at product level is calculated with the same logic, this time using 1.381 kg.

The multilevel plugin also calculates packaging values:

• Number of products per PCB packaging;
• Number of boxes per pallet (primary, secondary, tertiary);
• Quantity of packaging used for each product.

This tool also extracts dynamic characteristics. See the documentation on formulated characteristics.

These characteristics can be displayed directly or used in more complex calculations, as explained in the SPEL formula section.

Note: property names are the same as in the application: replace the spaces with underscores ( _ ) and add the dyn_ prefix at the beginning of the property name.

For example:

• property name: "qty multilevel"
• extracted name: "dyn_qty_multilevel"

Configuration

As in the standard connector, to activate the multilevel connector, add the following line:

connector.plugin.class=fr.becpg.connector.plugin.MultiLevelCSVVisitor

This line is available in the becpg-connector.properties file (delete the # symbol).

Once the plugin is loaded, you can choose which files to extract for each entity type (Finished product, Semi-finished product, Raw material, etc.).

Each entity has a mapping defined as follows:

mapping.document.bcpg_<Entity type> = <Field to extract>

Configuration of entities to extract

As the plugin is dedicated to two entity types (Finished and Semi-finished products), it does not produce CSV files for other entity types (clients, suppliers, etc.). The same applies to the search query: there is no need to extract other entities such as suppliers and clients.

connector.query.template=(@cm\\:created:[%s TO MAX] OR @cm\\:modified:[%s TO MAX]) AND ( TYPE:\"bcpg:finishedProduct\" OR TYPE:\"bcpg:semiFinishedProduct\")

Annex I: Certificate installation (Windows)

Getting the beCPG server certificate:

In Firefox, click the lock icon.

Then click the arrow to the right of the connection status.

Then click "More information".

Then click "Display certificate". Go to the "Miscellaneous" section and download the PEM (cert) certificate.

Save the file in a folder on your computer.

Open a terminal in your Java folder.

Note: you may need to open a terminal with administrator rights to continue the installation. To do so, search for "cmd" in the Windows search bar, right-click it and select "Open as administrator".

Go to the right folder with the cd command. Example with a Java folder located in C:\Program Files\Java\jre1.8.0_241.

Check that bin\keytool and lib\security\cacerts exist in the Java folder.

Once the terminal is open, use the following command:

bin\keytool -import -alias {certificate name} -file {.pem file location} -keystore lib\security\cacerts

Example with a certificate named becpg.pem, located in C:\tmp, with the alias becpg:

bin\keytool -import -alias becpg -file C:\tmp\becpg.pem -keystore lib\security\cacerts

The default password is: "changeit".

Follow the steps displayed by the terminal. Once the certificate is installed, you can check it with the following command:

bin\keytool -list -v -alias {certificate name} -keystore lib\security\cacerts

With our previous example:

bin\keytool -list -v -alias becpg -keystore lib\security\cacerts