Personal Library

Reference - IOM ImpEx Interfaces 2.1

https://support.intershop.com/kb/2S8052

1 Introduction
- 1.1 Glossary
2 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Dispatches
3 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Returns
4 Debtor Management - Export Customer Information (Debtor Service)
5 Debtor Management Import Open Positions
6 Debtor Management Import Payment Histories
7 Export Customer
8 Export Open Positions
9 Export Order for Order Placement
10 Export Return Announcement for Supplier

Product Version	2.1
Product To Version	2.1
Status	Final

1 Introduction

This document gives an overview of the so-called IMPEX interfaces of the Intershop Order Management (IOM) system. The word IMPEX is a portmanteau word used for the file import and export functionalities of the OMS.

Use an IMPORT interface to bring data into the IOM system (e.g., product data from a supplier) and vice versa use an EXPORT interface to create data files to be transmitted to a partner system like a 3^rd party warehouse management system.

1.1 Glossary

Term	Description
Creditor	The supplier, who has to send the products to the customer. A customer owes money to the supplier for the purchase(s) made.
Debtor	A person (in B2C sense) or company (in B2B sense) who owes money (to shop or supplier) for the purchase made.
External Accounting System	Accounting service, which synchronizes debits and credits of the shop. It is an intermediate item between creditor (i.e., supplier) and debtor (i.e., customer). The debtor pays to the creditor via an External Accounting System for the purchase(s).
IMPEX	A portmanteau word. Short for Imp(ort) and Ex(port).
IOM	The abbreviation for Intershop Order Management
OMS	The abbreviation for Order Management System, the technical name of the IOM
RMA number	Number for return material authorization, an identification number for an expected return

2 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Dispatches

Note

Based on PurchaseOrder interface version 1.0

The IOM provides an external standard interface in order to use a service of an external accounting system for the creditor management. This allows to export creditor relevant information about deliveries. A new dispatch triggers the export.

XML Reference

The file will be stored under <oms_base_path>/communication/messages/out/ with following syntax:

Level	Parent Element	Element	Type	minOcc	maxOcc	Size	Description	Attributes
0	-	PurchaseOrders	xs:complexType	1	1		Root element	no
1	PurchaseOrders	MerchantID	alphanumeric	1	1	20	Merchant ID	no
1	PurchaseOrders	TransID	alphanumeric	1	1	20	Transaction number	no
1	PurchaseOrders	PurchaseOrder	xs:complexType	1	n		List of all open payments for different debtors	no
2	PurchaseOrder	RefNr	alphanumeric	1	1	20	Reference for payment, e.g., invoice number	no
2	PurchaseOrder	PurchaseOrderNo	alphanumeric	1	1	20	Purchase order no from supplier	no
2	PurchaseOrder	Currency	text	1	1	3	DIN / ISO 4217 (EUR, USD, GBP, ...)	no
2	PurchaseOrder	EventToken	alphanumeric	1	1	2	Event token, OC = Open Position Create	no
2	PurchaseOrder	PayType	alphanumeric	1	1	5	CC = Creditcard EDD = Direct debit SO = Prepayment PP = PayPal GP = Giropay SK = Check BP = BillPay COD = cash on delivery CBA = Checkout by Amazon RP = RatePay	no
2	PurchaseOrder	Creditor	xs:complexType	1	1		Creditor of purchase order	yes
2	PurchaseOrder	Customer	xs:complexType	0	1		Customer of purchase order	yes
2	PurchaseOrder	CompanyCode	alphanumeric	0	1	10	Company code, e.g., 001 for Shop-A – differs per merchant	no
2	PurchaseOrder	PayTerm	alphanumeric	0	1	255	Terms of payment	no
2	PurchaseOrder	Purchase	xs:complexType	1	1		Purchase prices	no
3	Purchase	Net	xs:complexType	1	1	20	Net amount	yes

Note

Refer to oms."PlatformConfigDO" to find out more about <oms_base_path>. An example of <oms_base_path> is /home/baker under Linux operating system.

Attributes

Required attributes for XML-structure above are:

Element	Attribute	Type	Required	Size	Description
Creditor	id	alphanumeric	yes	20	Unique ID of creditor, provided by supplier
Customer	id	alphanumeric	yes	20	Unique ID of customer
Net	amount	numeric	yes	20	Net amount in minor units

Example

PurchaseOrders

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<PurchaseOrders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <MerchantId>10461-10177-0a</MerchantId>
    <TransId>12345a</TransId>
    <PurchaseOrder>
        <RefNr>123456a</RefNr>
		<PurchaseOrderNo>1241451wre</PurchaseOrderNo>
        <Currency>EUR</Currency>
        <EventToken>OC</EventToken>
        <PayType>CC</PayType>
        <Creditor id="1234556a"/>
        <CompanyCode>001a</CompanyCode>
        <PayTerm></PayTerm>
        <Purchase>
            <Net amount="33900"/>
        </Purchase>
    </PurchaseOrder>
    <PurchaseOrder>
        <RefNr>123456a</RefNr>
        <Currency>EUR</Currency>
        <EventToken>OC</EventToken>
        <PayType>CC</PayType>
        <CompanyCode>001a</CompanyCode>
        <PayTerm></PayTerm>
        <Purchase>
            <Net amount="33900"/>
        </Purchase>
    </PurchaseOrder>
</PurchaseOrders>

Example XML

SEND_PURCHASE_ORDERS_10000_Sports in Motion_20151215101443.xml

XSDs

Configuration

see Guide - Generic Interfaces for External Accounting Systems

FinanceController2PaymentDefDO

For the required payment methods (e.g. cash on delivery, refer oms."PaymentDefDO"), the import and export names according to the finance controller have to be configured.

INSERT INTO "FinanceController2PaymentDefDO" (id, "financeControllerRef", "financeControllerPaymentExportName", "financeControllerPaymentImportName", "paymentDefRef")
VALUES(nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'RG', '00', 5),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'CC', '55', 3),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'GP', '51', 21),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'PP', '60', 10),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'COD', '80', 4),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'EDD', '65', 7),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'SO', '50', 9),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'ISH', '01', 24),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'COP', '02', 23),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'PRE', '03', 2);

Communication Partners

In case of a return, the external accounting system (sending partner) triggers the communication and pushs it to the supplier (receiving partner) using the sender bean FileSenderBean and XML as document type (refer the table oms.DocumentTypeDefDO). It is possible to use a custom implemented sender bean which is configured in the table oms.CommunicationDO.

INSERT INTO "CommunicationPartnerDO"
("id", "decisionBeanDefRef", "splitTransmission", "communicationRef", "sendingPartnerReferrerRef", "receivingPartnerReferrerRef", "maxNoOfRetries", "retryDelay", "mergeTypeDefRef") 
values(
        nextval('"CommunicationPartnerDO_id_seq"'), -- id 
        null, -- decisionBeanDefRef
        FALSE, -- splitTransmission
        (select id from "CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_OPEN_AMOUNT'), -- communicationRef
        (select id from "PartnerReferrerDO" where "shopRef" = 10000), -- sendingPartnerReferrerRef
        (select id from "PartnerReferrerDO" where "financeControllerRef" = (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController')), -- receivingPartnerReferrerRef 
        5, -- maxNoOfRetries
        '1m', -- retryDelay
        null -- mergeTypeDefRef
    );

3 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Returns

Note

Based on PurchaseOrder interface version 1.0

The IOM provides an external standard interface In order to use a service of an external accounting system for creditor management. This allows to export creditor relevant information for returns. New returns trigger the export process.

XML Reference

The same XSD/XML format is used for exporting open amounts for returns as well as for dispatches. The only difference is that the amount is prefixed with a minus, i.e., negative amount (e.g., -3200),as opposed to dispatches to indicate that it is for returns.

Upon export, the file will be stored in <impexBasePath>/communication/messages/out/ with following syntax:

Level	Parent Element	Element	Type	minOcc	maxOcc	Size	Description	Attributes
0	-	PurchaseOrders	xs:complexType	1	1		Root element	no
1	PurchaseOrders	MerchantID	alphanumeric	1	1	20	Merchant ID	no
1	TransID	PurchaseOrders	alphanumeric	1	1	20	Transaction number	no
1	PurchaseOrders	PurchaseOrder	xs:complexType	1	n		List of all open payments a.k.a. unpaid amounts for different creditors	no
2	PurchaseOrder	RefNr	alphanumeric	1	1	20	Reference for payment, e.g., invoice number	no
2	PurchaseOrder	PurchaseOrderNo	alphanumeric	1	1	20	Purchase order number of the supplier	no
2	PurchaseOrder	Currency	text	1	1	3	DIN / ISO 4217 (EUR, USD, GBP, ...)	no
2	PurchaseOrder	EventToken	alphanumeric	1	1	2	Event token, OC = Create Open Payments	no
2	PurchaseOrder	PayType	alphanumeric	1	1	5	CC = Creditcard EDD = Direct debit SO = Prepayment PP = PayPal GP = Giropay SK = Check BP = BillPay COD = cash on delivery CBA = Checkout by Amazon RP = RatePay	no
2	PurchaseOrder	Creditor	xs:complexType	1	1		Creditor of purchase order	yes
2	PurchaseOrder	Customer	xs:complexType	0	1		Customer of purchase order	yes
2	PurchaseOrder	CompanyCode	alphanumeric	0	1	10	Company code, e.g., 001 for Shop-A – differs per merchant	no
2	PurchaseOrder	PayTerm	alphanumeric	0	1	255	Terms of payment	no
2	PurchaseOrder	Purchase	xs:complexType	1	1		Purchase prices	no
3	Purchase	Net	xs:complexType	1	1	20	Net amount provided in negative contrary to dispatches, e.g., -120 EUR, for indicating the money transaction is the other way around.	yes

Note

Refer to oms."PlatformConfigDO" to find out more about <oms_base_path>. An example of <oms_base_path> is /home/baker under Linux operating system.

Attributes

Required attributes for XML-structure above are:

Element	Attribute	Type	Required	Size	Description
Creditor	id	alphanumeric	yes	20	Unique ID of creditor, provided by supplier
Customer	id	alphanumeric	yes	20	Unique ID of customer
Net	amount	numeric	yes	20	Net amount in minor units

Example

PurchaseOrders

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<PurchaseOrders xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <MerchantId>10461-10177-0a</MerchantId>
    <TransId>12345a</TransId>
    <PurchaseOrder>
        <RefNr>123456a</RefNr>
		<PurchaseOrderNo>1241451wre</PurchaseOrderNo>
        <Currency>EUR</Currency>
        <EventToken>OC</EventToken>
        <PayType>CC</PayType>
        <Creditor id="1234556a"/>
        <CompanyCode>001a</CompanyCode>
        <PayTerm></PayTerm>
        <Purchase>
            <Net amount="-33900"/>
        </Purchase>
    </PurchaseOrder>
    <PurchaseOrder>
        <RefNr>123456a</RefNr>
        <Currency>EUR</Currency>
        <EventToken>OC</EventToken>
        <PayType>CC</PayType>
        <CompanyCode>001a</CompanyCode>
        <PayTerm></PayTerm>
        <Purchase>
            <Net amount="-33900"/>
        </Purchase>
    </PurchaseOrder>
</PurchaseOrders>

Example File

SEND_PURCHASE_ORDERS_10000_Sports in Motion_20151215101647.xml

XSDs

Configuration

see Guide - Generic Interfaces for External Accounting Systems.

FinanceController2PaymentDefDO

For the required payment methods (e.g., cash on delivery, refer oms."PaymentDefDO"), the import and export names according to the finance controller have to be configured.

INSERT INTO "FinanceController2PaymentDefDO" (id, "financeControllerRef", "financeControllerPaymentExportName", "financeControllerPaymentImportName", "paymentDefRef")
VALUES(nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'RG', '00', 5),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'CC', '55', 3),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'GP', '51', 21),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'PP', '60', 10),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'COD', '80', 4),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'EDD', '65', 7),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'SO', '50', 9),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'ISH', '01', 24),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'COP', '02', 23),
    (nextval('"FinanceController2PaymentDefDO_id_seq"'), (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController'), 'PRE', '03', 2);

Communication Partners

In case of a return,the external accounting system (sending partner) triggers the communication and pushs it to the supplier (receiving partner) by using the sender bean FileSenderBean and XML as document type (refer the table oms.DocumentTypeDefDO).

It is possible to use a custom implemented sender bean which is configured in the table oms.CommunicationDO.

INSERT INTO "CommunicationPartnerDO"
("id", "decisionBeanDefRef", "splitTransmission", "communicationRef", "sendingPartnerReferrerRef", "receivingPartnerReferrerRef", "maxNoOfRetries", "retryDelay", "mergeTypeDefRef") 
values(
        nextval('"CommunicationPartnerDO_id_seq"'), -- id 
        null, -- decisionBeanDefRef
        FALSE, -- splitTransmission
        (select id from "CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_OPEN_AMOUNT'), -- communicationRef
        (select id from "PartnerReferrerDO" where "shopRef" = 10000), -- sendingPartnerReferrerRef
	    (select id from "PartnerReferrerDO" where "financeControllerRef" = (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController')), -- receivingPartnerReferrerRef 
        5, -- maxNoOfRetries
        '1m', -- retryDelay
        null -- mergeTypeDefRef
    );

4 Debtor Management - Export Customer Information (Debtor Service)

This chapter describes the debtor export of the IOM. The export creates an XML file which is typically addressed to an external accounting system.

The syntax of the export file is SEND_DEBTOR_<ShopID>_<ShopName>_<Timestamp>_<DebitorTransmissionID>.xml.

<ShopID> is the ID of the OMS shop instance from the table oms."ShopDO"
<ShopName> is the property name of the OMS shop instance from the table oms."ShopDO"
<Timestamp> is in format YYYYMMDDHHMMSS
<DebitorTransmissionID> is the ID of the transmission object which triggered the export

Example:

SEND_DEBTOR_9012_PCompanyUK - Demo Shop_20151106092726.xml

Note

The export file is placed into the pre-configured directory BASEPATH/communication/messages/out

The BASEPATH is defined by the OMS property IS_OMS_DIR_VAR

Prerequisite

A debtor export only makes sense for OMS shop instances with configured “Invoicing Processes”. In other words, only through the “Invoicing Processes”, the debtors will be generated and updated.

XML Reference

The export file has the following syntax:

Level	Parent Element	Element	Type	minOcc	maxOcc	Size	Description	Attributes
0	-	Debtors	xs:complexType	1	1		Root element	no
1	Debtors	MerchantID	xs:string	1	1	20	Merchant ID of the retailer / shop at the export receiving system	no
1	Debtors	TransID	xs:string	1	1	20	Unique ID of the export	no
1	Debtors	Debtor	xs:complexType	1	n		A debtor is an entity that owes a debt to another entity	yes
2	Debtor	Event	xs:string	1	1	10	Specifies the occasion of the export CD = create debtor UD = update debtor DD = deactivate debtor	no
2	Debtor	Company	xs:complexType	choice Company or Person			The debtor is a company	yes
2	Debtor	Person	xs:complexType	choice Company or Person			The debtor is a private person	yes
2	Debtor	Address	xs:complexType	0	n		Billing address of the debtor	yes
2	Debtor	Email	xs:string	0	1	128	The email address of the debtor	no
2	Debtor	Telephone	xs:string	0	1	32	The telephone number of the debtor	no
2	Debtor	Currency	xs:string	0	1	3	Currency used by the debtor - DIN / ISO 4217 (EUR, USD, GBP, ...) Note always empty for debtor exports <Currency></Currency>	no
2	Debtor	Language	xs:language	0	1	2	Language of the debtor ISO 639-1 (de, en, ...) Note always empty for debtor exports <Language></Language>	no
2	Debtor	Bank	xs:complexType	0	1		The bank account of the debtor	yes
2	Debtor	Property	xs:complexType	0	n		Additional properties of the debtor	yes
3	Address	City	xs:string	1	1	32	City of the billing address	no
3	Address	PostCode	xs:string	1	1	8	ZIP code of the billing address	no
3	Address	State	xs:string	0	1	64	Federal state of the billing address	no
3	Address	District	xs:string	0	1	64	District of the billing address	no
3	Address	POBox	xs:string	choice POBox or a sequenceof Street and HouseNumber			Post office box of the billing address	no
3	Address	Street	xs:string				Street of the billing address	no
3	Address	HouseNumber	xs:string				House number of the billing address	no
3	Address	Addition	xs:string	0	4	64	Address addition of the billing address	no
3	Address	Country	xs:string	0	1	2	Country of the billing address Note The OMS ignores the size restriction and the comment on this element for the debtor export. Normally, the XSD specifies the use of the ISO 3166-2 code at this place.	no

Attributes

The attributes for XML elements of the above XML structure are:

Element	Attribute	Type	Required	Description
Debtor	id	xs:long	yes	ID / primary key of the debtor
Debtor	type	xs:int	yes	Type of the debtor: 1 = B2B (company) 2 = B2C (private person)
Company	name	xs:string	yes	Name of the company
Company	department	xs:string	no	Department of the company
Company	lineOfBusiness	xs:string	no	Line of business of the company
Company	costNo	xs:string	no	Cost center of the company
Company	commercialRegisterId	xs:string	no	Commercial register number of the company
Company	commercialRegisterLocation	xs:string	no	Location of commercial register of the company
Company	type	xs:string	no	Business form of t he company
Company	vatNo	xs:string	no	VAT No. of the company
Person	salutation	xs:string	no	Salutation of the private person
Person	title	xs:string	no	Title of the private person
Person	firstName	xs:string	no	First name of the private person
Person	middleName	xs:string	no	Middle name of the private person
Person	lastName	xs:string	yes	Last name of the private person
Address	type	xs:int	yes	Always 2 = billing address, for debtor exports
Bank	owner	xs:string	no	Name of the bank account owner
Bank	iban	xs:string	yes	IBAN - International Bank Account Number
Bank	bic	xs:string	no	BIC - Bank Identifier Code
Bank	bankName	xs:string	no	Name of the bank
Property	key	xs:string	yes	Key of an additional debtor property
Property	value	xs:string	yes	Value of an additional debtor property

XSD's

Example

Debtors

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Debtors xmlns="http://types.theberlinbakery.com/v1_0" xmlns:ns2="http://types.theberlinbakery.com/v1_1">
    <MerchantID>MerchantId</MerchantID>
    <TransID>MJ002-20151106001-101217</TransID>
    <Debtor id="100654" type="2">
        <Event>CD</Event>
        <Person salutation="Mr." firstName="Matze" lastName="Katze"/>
        <Address type="2">
            <ns2:City>Berlin</ns2:City>
            <ns2:PostCode>10178</ns2:PostCode>
            <ns2:Street>Neue Promenade</ns2:Street>
            <ns2:HouseNumber>5</ns2:HouseNumber>
            <ns2:Addition>3. OG</ns2:Addition>
            <ns2:Addition>Terrasse</ns2:Addition>
            <ns2:Country>Germany</ns2:Country>
       </Address>
        <Currency></Currency>
        <Language></Language>
   </Debtor>
</Debtors>

Example File

SEND_DEBTOR_9012_PCompanyUK - Demo Shop_20151106092726.xml

Configuration

To enable the debtor export, the OMS needs a specific configuration at the database table oms."CommunicationPartnerDO".

Property	Description	Example
id	ID / primary key	100001
decisionBeanDefRef	<optional> not needed for debtor export - set to null - reference to oms."DecisionBeanDefDO"	null
splitTransmission	not needed for debtor export - set to FALSE	FALSE
communicationRef	id of oms."CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_DEBITOR' - reference to oms."CommunicationDO"	2
receivingPartnerReferrerRef	specifies the receiver of the debtor export - typically the partner referrer of the finance controller / accounting system - reference to oms."PartnerReferrerDO"	5001
sendingPartnerReferrerRef	specifies the sender of the export - typically the partner referrer of the retailer / shop - reference to oms."PartnerReferrerDO"	10100
maxNoOfRetries	maximum number of retries in case of an erroneous transmission 0 = no further retry	5
retryDelay	delay between the retries	10m
mergeTypeDefRef	<optional> not needed for debtor export - set to null - reference to oms."MergeTypeDefDO"	null

Configuration example:

INSERT INTO oms."CommunicationPartnerDO"(
            "id", 
			"decisionBeanDefRef", 
			"splitTransmission", 
			"communicationRef", 
            "receivingPartnerReferrerRef", 
			"sendingPartnerReferrerRef", 
			"maxNoOfRetries", 
            "retryDelay", 
			"mergeTypeDefRef")
    VALUES (
        nextval('oms."CommunicationPartnerDO_id_seq"'), 
		null, 
		FALSE, 
		(select "id" from oms."CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_DEBITOR'), 
        (select "id" from oms."PartnerReferrerDO" where "financeControllerRef" = (SELECT "id" FROM oms."FinanceControllerDO" WHERE "name" = 'Name of FinanceController')),
        (select "id" from oms."PartnerReferrerDO" where "shopRef" = shopId), -- enter the id of the OMS shop instance here
        5,
        '10m',
        null
    );

5 Debtor Management Import Open Positions

This chapter addresses consultants or support engineers for IOM with intermediate knowledge of SQL, XML/XSD, and Java.

In order to be able to use services of an external accounting system for debtor management, IOM provides an external standard interface to import relevant information about open amounts (also known as open payments or open positions). The import is triggered when a new file is placed in a pre-configured IOM file system directory.

XML Reference

The file should be placed in <configured_location> with following syntax:

Regarding file location, kindly refer the section 'FTP pull / scheduler / ExecutionBean configuration' and comment '-- FTP: remote ftp directory'.

Element

Parent Element

Type

Size

Mandatory

Description

OpenPositions

Root element

MerchantId

OpenPositions

alphanumeric

Merchant ID (financeControllerShopName in Shop2FinanceControllerDO)

TransId

OpenPositions

alphanumeric

Transaction number

OpenPosition

OpenPostions

unbounded

RefNr

OpenPosition

alphanumeric

Reference for payment, e.g., invoicing number (InvoiceNo in InvoicingDO)

Debtor

OpenPosition

DebtorSimple

refer ID from oms."DebitorDO"

Attribute	Type	Size	Description
id	xs:long	-	Unique id of debtor

CreationDate

OpenPosition

numeric

Creation date in format YYYY-MM-DD (e.g., 2015-01-18)

VoucherNo

OpenPosition

alphanumeric

Voucher Number

VoucherDate

OpenPosition

alphanumeric

Voucher date in format YYYY-MM-DD (e.g., 2015-01-18)

EntryText

OpenPosition

alphanumeric

255

Entry text, booking text

EntryTextExt

OpenPosition

alphanumeric

255

Entry text, booking text extension

Currency

OpenPosition

text

Currency

PayType

OpenPosition

alphanumeric

Shortened name of the payment method (configurable)

PayTerm

OpenPosition

alphanumeric

255

Terms of payment

OPAmount

OpenPosition

numeric

Amount of open position

SumAmount

OpenPosition

numeric

Sum of all payments

OpenAmount

OpenPosition

numeric

Open Amount

DunningLevel

OpenPosition

alphanumeric

Dunning indicator

LastDunningDate

OpenPosition

numeric

Date of last reminderin format YYYY-MM-DD (e.g., 2015-01-18)

ContraAccount

OpenPosition

alphanumeric

In a double accounting system, at least two bookings must be made for each transaction. One booking on credit side of an account (e.g., first account), and the another booking on the debit side of an another account (e.g., second account). The account where debiting takes place is called as contra account.

EntryRange

OpenPosition

alphanumeric

Company code
Smallest organizational unit of external accounting for which a complete, self-contained set of accounts can be created. This includes the entry of all transactions that must be posted and the creation of all items for legal individual financial statements, such as the balance sheet and the profit and loss statement.

ModificationDate

OpenPosition

numeric

Date of last modification in format YYYY-MM-DD (e.g., 2015-01-18)

Attributes

Required attributes for XML-structure above are:

Element	Attribute	Type	Required	Size	Description
Debtor	id	xs:long	yes	-NA-	Unique ID of debtor

Example

OpenPositions

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OpenPositions xmlns="http://types.theberlinbakery.com/v1_0">
    <MerchantId>XYZ</MerchantId> <!-- refer financeControllerShopName from oms."Shop2FinanceControllerDO" -->
    <TransId>12345a</TransId>
	<OpenPosition>
		<RefNr>2014000001</RefNr> <!-- invoicingNo/ID from oms."InvoicingDO" -->
		<Debtor id="10000"/> <!-- id from oms."DebitorDO" -->
		<CreationDate>2015-01-18</CreationDate>
		<VoucherNo>12345dsf</VoucherNo>
		<VoucherDate>2015-01-18</VoucherDate>
		<EntryText>abc</EntryText>
		<!-- Avoid empty tags: <EntryTextExt></EntryTextExt> -->
		<Currency>EUR</Currency>
		<PayType>55</PayType> <!-- refer financeControllerPaymentImportName from oms."FinanceController2PaymentDefDO "-->
		<OPAmount>12345</OPAmount>
		<SumAmount>12346</SumAmount>
		<OpenAmount>1</OpenAmount>
		<EntryRange>01</EntryRange>
		<ModificationDate>2015-01-18</ModificationDate>
	</OpenPosition>
</OpenPositions>

Example XML for Download

OpenPositionsImport_1228.xml

XSDs

Configuration

General Configuration

Also see Guide - Generic Interfaces for External Accounting Systems.

FinanceController2PaymentDefDO (mandatory)

Defines how the payment methods are called for export and import. If the FinanceControllerDO uses "60" as name / id for PayPal payment in their OpenPositions.xml, it will be mapped to the PaymentDefDO with id = 10. This mapping is mandatory for all payment methods supported by the FinanceControllerDO, there is no fallback method.

FinanceController2PaymentDefDO payment method mapping

INSERT INTO oms."FinanceController2PaymentDefDO" (id, "financeControllerRef", "financeControllerPaymentExportName", "financeControllerPaymentImportName", "paymentDefRef") VALUES 
	(8, 10000, 'RG', '00', 5), 
    (1, 10000, 'CC', '55', 3), 
    (5, 10000, 'GP', '51', 21), 
    (4, 10000, 'PP', '60', 10), 
    (7, 10000, 'COD', '80', 4),
    (2, 10000, 'EDD', '65', 7), 
    (3, 10000, 'SO', '50', 9), 
    (9, 10000, 'ISH', '01', 24), 
    (10, 10000, 'COP', '02', 23), 
    (11, 10000, 'PRE', '03', 2);
-- also: instead of hardcoding financeControllerRef to 10000
-- (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController')

Shop2DunningLevelDO

The Shop2DunningLevelDO provides a mapping for (FinanceControllerDunningLevelName x Shop) → (DunningLevelDefDO). The configuration is optional, as long as the DunningLevel field is not used during import.

FinanceControllerDunningLevelName x Shop mapping

INSERT INTO oms."Shop2DunningLevelDO" (id, description, "dunningLevelDefRef", "financeControllerDunningLevel", "financeControllerRef", "shopRef")
    						   VALUES (10000, "No dunning level", 1, "INITIAL", 1, 10000);

Transformer / FTP Pull Configuration

Currently, the Open Positions import is only available as import via XML files. The files are read, transformed and persisted by a "Transformer" bean which has to be configured properly. The following basic configuration will pull and transform an OpenPositions.xml file from a specified FTP location every 10 minutes. Note that this has to be done after completing General Configuration.

PartnerReferrer and TransformerProcess(Group)

-- create transformer process group
INSERT INTO oms."TransformerProcessGroupDO" (id, name)
									VALUES  (nextval('"TransformerProcessGroupDO_id_seq"'), 'import_openpositions');
-- transformer to map and persist the op files into DOs
-- filenameRegex has to be defined, only these files are handled by the transformer
-- transformerBeanDefRef = 22 (SELECT id FROM oms."TransformerBeanDefDO" where name = 'ImportXmlOpenpositionsBean')
INSERT INTO oms."TransformerProcessDO" (id, index, "transformerBeanDefRef", "transformerProcessGroupRef", "filenameRegex", "moveObsoleteFiles")
							   VALUES  (nextval('"TransformerProcessDO_id_seq"'), 2, 22, currval('"TransformerProcessGroupDO_id_seq"'), 'OpenPositionsImport_[0-9]*\.xml', true);
-- the Transformer needs a special parameter where the shopId is configured (the internal id of the ShopDO, transformerProcessParameterKeyDefRef = 1)
INSERT INTO oms."TransformerProcessParameterDO" ( id, "parameterValue", "transformerProcessesParameterKeyDefRef", "transformerProcessesRef")
										VALUES  (nextval('"TransformerProcessParameterDO_id_seq"'), 10000, 1, currval('"TransformerProcessDO_id_seq"'));
 
-- filenameRegex for the transformer process 
INSERT INTO oms."TransformerProcessParameterDO" ( id, "parameterValue", "transformerProcessesParameterKeyDefRef", "transformerProcessesRef")
										VALUES (nextval('"TransformerProcessParameterDO_id_seq"'), 'OpenPositionsImport_[0-9]*\.xml', 4, 10000);

FTP Pull / Scheduler / ExecutionBean Configuration

--FTP config 
INSERT INTO oms."FileTransferConfigurationDO" (id, "basePath", "transmissionTypeDefRef", "typeDefRef", "creationDate", "modificationDate", "partnerReferrerRef", "description", "transformerProcessGroupRef")
									  VALUES  (nextval('"FileTransferConfigurationDO_id_seq"'), null, 1900, 10, current_timestamp, current_timestamp, (SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" =
											   currval('"FinanceControllerDO_id_seq"')), 'import_op', currval('"TransformerProcessGroupDO_id_seq"'));

-- pull every 10 min, Quartz cron format
INSERT INTO oms."ScheduleDO" (id, "creationDate", "modificationDate", active, "configId", cron, "expectedRuntime", "jobDefRef", "lastRun", "lockedSince", "key", "maxNoOfRetries", "retryDelay", "countRetry")
					 VALUES  (nextval('"ScheduleDO_id_seq"'), current_timestamp, current_timestamp, TRUE, currval('"FileTransferConfigurationDO_id_seq"'), '0 0/10 * 1/1 * ? *', 60000, 3, NULL, NULL,
							  'import_openpositions', 10, '5m', 0);

-- CommunicationPartnerDO
INSERT INTO oms."CommunicationPartnerDO" (id, "splitTransmission", "communicationRef", "receivingPartnerReferrerRef", "sendingPartnerReferrerRef", "maxNoOfRetries", "retryDelay")
								 VALUES  (nextval('"CommunicationPartnerDO_id_seq"'), FALSE, (SELECT id FROM "CommunicationDO" WHERE "key" = 'ANY###FTP_JOB###EXT_TRANSFER_FILE'),
										  (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000), (SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"')),
										  12, '30m');

-- FTP: Credentials
INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullFtpAccount'),
										'ftp-omtdata:ftp-omtdata@localhost', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
										'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
										(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));	

-- FTP: optional private key config
/*
INSERT INTO "ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
						   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullPrivateKeyFile'),
									'/home/baker/keys/yourKeyFile.ppk', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
									'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
									(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));
*/

-- FTP remote ftp directory 
-- FTP file should be placed under <impexBathPath>/accounting/customerxy/import_openpositions/
INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullDirectory'), 				
										'/accounting/customerxy/import_openpositions', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
										'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
										(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));

-- FTP: regex filename (needs to be consistent with the TransformerProcessDO if there is no additional renaming process defined!)
-- Example file name: OpenPositionsImport_1234.xml
 INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullFilenameRegex'), 'OpenPositionsImport_[0-9]*\.xml',
										(SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" = 'ANY###FTP_JOB###EXT_TRANSFER_FILE') and
										"receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO"
										WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));

6 Debtor Management Import Payment Histories

This chapter addresses consultants or support engineers for IOM with intermediate knowledge of SQL, XML/XSD, and Java.

In order to be able to use services of an external accounting system for debtor management, IOM provides an external standard interface to import relevant information about open amount histories (also known as open payment histories or open position histories). The import is triggered when a new file is placed in a pre-configured IOM file system directory.

XML Reference

The file should be placed in <impexBasePath>/import with following syntax:

Note

<impexBasePath> is configurable

Please contact your support or refer the column "omtBasePath" from the table oms."PlatformConfigDO".

Element

Parent Element

Type

Size

Mandatory

Description

PaymentHistories

Root element

MerchantId

PaymentHistories

alphanumeric

Merchant ID (financeControllerShopName in Shop2FinanceControllerDO)

TransId

PaymentHistories

alphanumeric

Transaction number

PaymentHistory

PaymentHistories

unbounded

RefNr

PaymentHistory

alphanumeric

Reference for payment, e.g., invoicing number (InvoiceNo in InvoicingDO)

Debtor

PaymentHistory

DebtorSimple

Attribute	Type	Size	Description
id	xs:long	-	Unique id of debtor

EntryDate

PaymentHistory

numeric

Entry datein format DDMMYYYY

VoucherNo

PaymentHistory

alphanumeric

Voucher Number

VoucherDate

PaymentHistory

alphanumeric

Voucher date in format DDMMYYYY

ContraAccount

PaymentHistory

alphanumeric

PositionType

PaymentHistory

alphanumeric

255

Two types of position: Z being payment amount, R being invoicing amount based on the column HI record.

Four outcomes:

Positive amount and 'R' position type => invoicing amount
Negative amount and 'R' position type => credit note / return
Positive amount and 'Z' position type => amount for payment
Negative amount and 'Z' position type => redemption / refund

The other possible combinations RU and ZU are rare and occur during re-booking between different open payments/open positions. These re-bookings are initiated manually by an accountant.

PositionDetails

PaymentHistory

alphanumeric

255

Details of position, free text for field PostionType

PositionDetailsExt

PaymentHistory

alphanumeric

255

Details of position extension

Amount

PaymentHistory

numeric

Amount in minor units

EntryRange

PaymentHistory

alphanumeric

Attributes

Required attributes for XML-structure above are:

Element	Attribute	Type	Required	Size	Description
Debtor	id	xs:long	yes	-NA-	Unique ID of debtor

Example

The XML file placed in the pre-configured directory might look as follows:

Example-XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<PaymentHistories xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <MerchantId>10461-10177-0a</MerchantId>
    <TransId>12345a</TransId>
	<PaymentHistory>
		<RefNr>123456a</RefNr>
		<Debtor id="1234556a"/>
		<EntryDate>23022015</EntryDate>
		<VoucherNo>12345dsf</VoucherNo>
		<VoucherDate>23022015</VoucherDate>
		<ContraAccount></ContraAccount>
		<PositionType></PositionType>
		<PositionDetails></PositionDetails>
		<PositionDetailsExt></PositionDetailsExt>
		<Amount>12345</Amount>
		<EntryRange>01</EntryRange>
	</PaymentHistory>
</PaymentHistories>

XSDs

Configuration

General Configuration

Also see Guide - Generic Interfaces for External Accounting Systems.

Transformer / FTP Pull Configuration

Currently, the Open Positions Histories import is only available as import via XML files. The files are read, transformed and persisted by a "Transformer" bean which has to be configured properly. The following basic configuration will pull and transform an OpenPositionHistories.xml file from a specified ftp location every 10 minutes. Note that this has to be done after completing General Configuration.

PartnerReferrer and TransformerProcess(Group)

-- create transformer process group
INSERT INTO oms."TransformerProcessGroupDO" (id, name)
									VALUES  (nextval('"TransformerProcessGroupDO_id_seq"'), 'import_paymenthistory');

-- transformer to map and persist the op files into DOs
-- filenameRegex has to be defined, only these files are handled by the transformer
-- transformerBeanDefRef = 23 (SELECT id FROM oms."TransformerBeanDefDO" where name = 'ImportXmlPaymenthistoriesBean')
INSERT INTO oms."TransformerProcessDO" (id, index, "transformerBeanDefRef", "transformerProcessGroupRef", "filenameRegex", "moveObsoleteFiles")
							   VALUES  (nextval('"TransformerProcessDO_id_seq"'), 2, 23, currval('"TransformerProcessGroupDO_id_seq"'), 'PaymentHistories_[0-9]*\.xml', true);

-- the Transformer needs a special parameter where the shopId is configured (the internal id of the ShopDO, transformerProcessParameterKeyDefRef = 1)
INSERT INTO oms."TransformerProcessParameterDO" ( id, "parameterValue", "transformerProcessesParameterKeyDefRef", "transformerProcessesRef")
										VALUES  (nextval('"TransformerProcessParameterDO_id_seq"'), 10000, 1, currval('"TransformerProcessDO_id_seq"'));
 
INSERT INTO oms."TransformerProcessParameterDO" ( id, "parameterValue", "transformerProcessesParameterKeyDefRef", "transformerProcessesRef") 
										VALUES (nextval('"TransformerProcessParameterDO_id_seq"'), 'PaymentHistories_[0-9]*\.xml', 4, 10000);

FTP Pull / Scheduler / ExecutionBean Configuration

--FTP config 
INSERT INTO oms."FileTransferConfigurationDO" (id, "basePath", "transmissionTypeDefRef", "typeDefRef", "creationDate", "modificationDate", "partnerReferrerRef", "description", "transformerProcessGroupRef")
									  VALUES  (nextval('"FileTransferConfigurationDO_id_seq"'), null, 1900, 10, current_timestamp, current_timestamp, (SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" =
											   currval('"FinanceControllerDO_id_seq"')), 'import_paymenthistories', currval('"TransformerProcessGroupDO_id_seq"'));

-- pull every 10 min, Quartz cron format
INSERT INTO oms."ScheduleDO" (id, "creationDate", "modificationDate", active, "configId", cron, "expectedRuntime", "jobDefRef", "lastRun", "lockedSince", "key", "maxNoOfRetries", "retryDelay", "countRetry")
					 VALUES  (nextval('"ScheduleDO_id_seq"'), current_timestamp, current_timestamp, TRUE, currval('"FileTransferConfigurationDO_id_seq"'), '0 0/10 * 1/1 * ? *', 60000, 3, NULL, NULL,
							  'import_paymenthistory', 10, '5m', 0);

-- CommunicationPartnerDO
INSERT INTO oms."CommunicationPartnerDO" (id, "splitTransmission", "communicationRef", "receivingPartnerReferrerRef", "sendingPartnerReferrerRef", "maxNoOfRetries", "retryDelay")
								 VALUES  (nextval('"CommunicationPartnerDO_id_seq"'), FALSE, (SELECT id FROM "CommunicationDO" WHERE "key" = 'ANY###FTP_JOB###EXT_TRANSFER_FILE'),
										  (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000), (SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"')),
										  12, '30m');

-- FTP: Credentials
INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullFtpAccount'),
										'ftp-omtdata:ftp-omtdata@localhost', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
										'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
										(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));	

-- FTP: optional private key config
/*
INSERT INTO "ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
						   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullPrivateKeyFile'),
									'/home/baker/keys/yourKeyFile.ppk', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
									'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
									(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));
*/

-- FTP: remote ftp directory
INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullDirectory'), 				
										'/accounting/customerxy/import_openpositions', (SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" =
										'ANY###FTP_JOB###EXT_TRANSFER_FILE') and "receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" =
										(SELECT id FROM "PartnerReferrerDO" WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));

-- FTP: regex filename (needs to be consistent with the TransformerProcessDO if there is no additional renaming process defined!)
INSERT INTO oms."ExecutionBeanValueDO" (id, "executionBeanKeyDefRef", "parameterValue", "communicationPartnerRef")
							   VALUES  (nextval('"ExecutionBeanValueDO_id_seq"'), (SELECT id FROM "ExecutionBeanKeyDefDO" WHERE "executionBeanDefRef" = 202 and "parameterKey" = 'pullFilenameRegex'), 'PaymentHistories_[0-9]*\.xml',
										(SELECT id FROM "CommunicationPartnerDO" WHERE "communicationRef" = (SELECT id FROM "CommunicationDO" WHERE "key" = 'ANY###FTP_JOB###EXT_TRANSFER_FILE') and
										"receivingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO" WHERE "shopRef" = 10000) and "sendingPartnerReferrerRef" = (SELECT id FROM "PartnerReferrerDO"
										WHERE "financeControllerRef" = currval('"FinanceControllerDO_id_seq"'))));

7 Export Customer

Note

Interface version 1.0 - based on CustomerService-v1.0.xsd

This chapter describes the customer export format of IOM 1.1.0.0 defined in the XML schema description<jboss>/bakery.schemas/communication-v1.0/src/main/resources/META-INF/wsdl/CustomerService-v1.0.xsd.

If the customer data is changed via OMT or the CustomerService, the IOM exports the customer data as XML file.
The file is stored in impexBasePath/export with following syntax:
customerExportConfigDO.getExportName()_customerExportConfigDO.getShopRef()customerExportConfigDO.getExportVersion_yyyyMMddHHmmss.xml

XML Reference

XML elements listed in the table with specific attributes are rendered as links. Click the link to navigate to the attributes' description.

Element	Parent Element(s)	Type	minOcc	maxOcc	Size	Description
Customers	root		1	1		Root element
Customer	Customers	xs:complexType	1	n		List of all customers
Shop	Customer	xs:complexType	0	1
Address	Customer	xs:complexType	0	unbounded
Person	Customer or Address	xs:complexType	0	1		May occur as child of Customer or Address
Contact	Customer or Address	xs:complexType	0	1		May occur as child of Customer or Address
IsValid	Address	xs:boolean	0	1
Location	Address	xs:complexType
Street	Location	xs:string	0	1	100	street
HouseNo	Location	xs:string	0	1	10	house number
POBox	Location	xs:string	0	1	25	post box number
Packstation	Location	xs:complexType	0	1
AddressAddition	Location	xs:string	0	4	100	address additions
District	Location	xs:string	0	1	100	district
City	Location	xs:string	0	1	100	city
PostCode	Location	xs:string	0	1	100	zip
Country	Location	xs:string	0	1	100	country
BankAccount	Customer	xs:complexType	0	unbounded
BankName	BankAccount	xs:string	0	1	30
AccountHolder	BankAccount	xs:string	0	1	30
AccountNumber	BankAccount	xs:string	0	1	34
BankCode	BankAccount	xs:string	0	1	20
Scoring	Customer	xs:string	0	1	10
Properties	Customer	xs:complexType	0	1
Property	Properties	xs:complexType	0	unbounded

Attributes

Attributes of Customer

Attribute	Type	Required	Size	Description
customerType	xs:string	x	50	type of customer (B2B/B2C)
dunningLevel	xs:string	-	50	dunning level
isNewCustomer	xs:boolean	-		true if new customer
hasDeliveryStop	xs:boolean	-		true if customer has delivery stop
version	xs:int	-
isActive	xs:boolean	-		true if customer is active
hasNewsletter	xs:boolean	-		true if customer has newsletter assignment

Attributes of Shop

Attribute	Type	Required	Size	Description
shopCustomerNo	xs:string		20	shop customer number

Attributes of Person

Attribute	Type	Required	Size	Description
companyName	xs:string	-	100	name of the company
title	xs:string	-	10	title
dateOfBirth	xs:date	-		date of birth
lastName	xs:string	-	50	lastname
isFemale	xs:boolean	-		true if customer is female
firstName	xs:string	-	50	firstname

Attributes of Contact

Attribute	Type	Required	Size	Description
phone1	xs:string	-	25	phone number
phone2	xs:string	-	25	phone number
eMail	xs:string	-	100	mail address
fax	xs:string	-	25	fax number
mobilePhone	xs:string		25	mobile phone number

Attributes of Address

Attribute	Type	Required	Size	Description
isDefault	xs:boolean	-		true if default address
addressType	xs:string	x	50	SHIPPING/BILLING/IMMATERIAL
shopAddressNo	xs:string	-	50	shop number if address
version	xs:int	-

Attributes of IsValid

Attribute	Type	Required	Size	Description
lastValidation	xs:dateTime	-		date of last address validation

Attributes of Packstation

Attribute	Type	Required	Size	Description
stationId	xs:string	x	100	id of container freight station
userId	xs:string	x	100	user id of container freight station

Attributes of BankAccount

Attribute	Type	Required	Size	Description
shopBankAccountNo	xs:string	-	50	shop number of bank account
isDefault	xs:boolean	-		true if default bank account
hasDirectDebit	xs:boolean	-		true if bank account has direct debit
version	xs:int	-		version

Attributes of BankCode

Attribute	Type	Required	Size	Description
bankCodeType	xs:string	x	50	sepa/standard

Attributes of Scoring

Attribute	Type	Required	Size	Description
source	xs:string	x	50	source of the scoring
scoringDate	xs:date	x		date of scoring
shopScoringNo	xs:string	-	50	shop number of scoring
version	xs:int	-		version

Attributes of Properties

Attribute	Type	Required	Size	Description
type	xs:string	x	100	BOOLEAN/STRING/TIMESTAMP

Attributes of Property

Attribute	Type	Required	Size	Description
shopPropertyNo	xs:string	-	50	shop number of property
key	xs:string	x	100	property key
value	xs:string	x	100	property value
version	xs:int	-		version

Example

Customer data export format

<Customers xsi:schemaLocation="http://types.theberlinbakery.com/customer/v1_0 CustomerService-v1.0.xsd" xmlns="http://types.theberlinbakery.com/customer/v1_0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2
 <Customer customerType="a" dunningLevel="a" isNewCustomer="true" hasDeliveryStop="true"version="0" id="String" isActive="true" hasNewsletter="true">
   <Shop shopCustomerNo="123abc"/>
   <Person companyName="Company" title="Dr." dateOfBirth="1967-08-13" lastName="Lastname"isFemale="false" firstName="Firstname"/>
   <Contact phone1="123abc" phone2="123abcd" eMail="mail@mail.com" fax="123abc" mobilePhone="1234abcd"/>
   <Address isDefault="true" addressType="BILLING" shopAddressNo="123abc" version="0"id="String">
     <Person title="Dr." companyName="Company" lastName="Lastname" isFemale="false" firstName="Firstname"/>
     <Contact phone1="123abc" phone2="123abcd" eMail="mail@mail.com" fax="123abc" mobilePhone="123abcd"/>
     <Location>
       <Street>Street</Street>
       <HouseNo>Number</HouseNo>
       <POBox>Postbox</POBox>
       <Packstation stationId="123abc" userId="123abc"/>
       <AddressAddition>Addition1</AddressAddition>
       <District>District</District>
       <City>City</City>
       <PostCode>Postcode</PostCode>
       <Country>Country</Country>
     </Location>
     <IsValidated lastValidation="2001-12-17T09:30:47Z">true</IsValidated>
   </Address>
   <BankAccount shopBankAccountNo="a" isDefault="true" hasDirectDebit="true"version="0" id="String">
     <BankName>Bank</BankName>
     <AccountHolder>Customer</AccountHolder>
     <AccountNumber>1234abc</AccountNumber>
     <BankCode bankCodeType="SEPA">123abc</BankCode>
   </BankAccount>
   <Scoring source="a" shopScoringNo="123abc" scoringDate="2012-08-13" version="0" id="String">0</Scoring>
   <Properties type="STRING">
     <Property shopPropertyNo="abc123" key="key" value="value" version="0" id="String"/>
   </Properties>
 </Customer>
</Customers>

XSD's

Configuration

The configuration for the schedule of the customer export has to be done in database table customer."CustomerExportConfigDO". An export configuration relates to one OMS shop instance

It is possible to define an interval for a incremental export in minutes and/or a interval for a full export in days.

Property	Description	Example
id	ID / primary key	10000
exportName	name of the export, used in filename ID / primary key - <required>	IOM-CUSTOMER
exportStartDate	date when the export starts the first time	2015-10-13 12:00:00
exportVersion	export version, used in filename - <required>	1.00000
intervalFullInDays	intervall for full customer export in days	1(once a day at 12:00:00) depends on exportStartDate
intervalIncrementInMinutes	intervall for incremental customer export in minutes	10 (every 10 minutes)
exportNewCustomerOnIncrement	if true the new customers will be exported in incremental exports otherwise not	true
lastExport	timestamp of last export	2015-10-19 10:12:00.029
shopRef	reference to the OMS shop instance - references oms."ShopDO" - <required>	10000
version	version 10 means ten exports already done	1
startTimeControlApp	timestamp when quartz has started the export, just for controlling	null
startTimeMDB	timestamp when MDBean was catching the JMS, just for controlling	null

8 Export Open Positions

This chapter describes the open position export format of IOM 1.1.0.0 defined in the XML schema description <jboss>/bakery.schemas/communication-v1.0/src/main/resources/META-INF/wsdl/SalesOrders-v1.0.xsd.

XML Reference

Note

In the following tables, the contents of the fields Class:Attribute represent object path expressions.

The element is the root of open position XML data. SalesOrder definitions and lists must be enclosed in this element.

Level	Parent Element	Element	Type	minOcc	maxOcc	Size	Description	Attributes
0	-	SalesOrders					Root Element	no
1	SalesOrders	MerchantID	alphanumeric	1	1	20	Merchant ID	no
1	SalesOrders	TransID	alphanumeric	1	1	20	Transaction ID	no
1	SalesOrders	SalesOrder		1	n		List of all open positions for different debtors	no
2	SalesOrder	RefNr	alphanumeric	1	1	20	Reference for payment, e.g. shop order number	no
2	SalesOrder	Currency	text	1	1	3	DIN / ISO 4217 (EUR, USD, GBP, ...)	no
2	SalesOrder	EventToken	alphanumeric	1	1	2	Event token, OC = Open Position Create	no
2	SalesOrder	PayType	alphanumeric	1	1	5	Configured as financeControllerPaymentExportName in FinanceController2PaymentDefDO with an internal reference to PaymentDefDO	no
2	SalesOrder	Debtor	complexType with attributes	1	1		Debtor of the order	yes
2	SalesOrder	Customer	complexType with attributes	0	1		Customer of the order	yes
2	SalesOrder	CompanyCode	alphanumeric	0	1	10	Company code, e.g., 001 for Shop-A – differs per merchant	no
2	SalesOrder	PayTerm	alphanumeric	0	1	255	Terms of payment	no
2	SalesOrder	InvoiceDate	numeric	1	1	6	OP_Valutadate in format DDMMYY	no
2	SalesOrder	Sale	complexType	1	1		Sales prices	no
3	Sale	Taxes	complexType	1	1		Taxes of the order	no
4	Taxes	Tax	complexType with attributes	1	1		Single tax	yes
2	SalesOrder	Gross	complexType with attributes	1	1	20	Gross price of the order	yes
2	SalesOrder	Net	complexType with attributes	1	1	20	Net price of the order	yes

Attributes

Element	Attribute	Type	Required	Size	Description
Debtor	id	numeric	yes	20	Unique ID of debtor
Debtor	type	int	yes	1	1=B2B, 2=B2C
Customer	id	aplhanumeric	yes	20	Unique ID of customer
Gross	amount	numeric	yes	20	Gross amount in minor units
Net	amount	numeric	yes	20	Net amount in minor units
Tax	code	alphanumeric	yes	10	Tax Code (e.g., 001 for 19%) Configured as financeControllerTaxTypeExportName in FinanceController2TaxTypeDefDO with an internal reference to TaxTypeDefDO
Tax	netAmount	numeric	yes	20	Net amount for this tax code in minor units
Tax	taxAmount	numeric	yes	20	Tax amount for this tax code in minor units

Examples

SalesOrders example

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<SalesOrders xmlns="http://types.theberlinbakery.com/v1_0" xmlns:ns2="http://types.theberlinbakery.com/v1_1" xmlns:ns3="http://types.theberlinbakery.com/customer/v1_0">
    <MerchantID>MerchantId</MerchantID>
    <TransID>2014007838</TransID>
    <SalesOrder>
        <RefNr>0083</RefNr>
        <Currency>GBP</Currency>
        <EventToken>OC</EventToken>
        <PayType>PP</PayType>
        <Debtor id="100646" type="2"/>
        <Customer id="MJ4"/>
        <CompanyCode>123abc</CompanyCode>
        <InvoiceDate>2015-10-27</InvoiceDate>
        <Sale>
            <Gross amount="87925">0</Gross>
            <Net amount="75387">0</Net>
            <Taxes>
                <Tax code="101" netAmount="62687" taxAmount="12538"/>
                <Tax code="000" netAmount="12700" taxAmount="0"/>
                <Tax code="002" netAmount="0" taxAmount="0"/>
            </Taxes>
        </Sale>
    </SalesOrder>
</SalesOrders>

Example File

SEND_SALES_ORDERS9012_PCompanyUK - Demo Shop_20151118154913.xml

XSDs

Configuration

General Configuration

see Guide - Generic Interfaces for External Accounting Systems

INSERT INTO "CommunicationPartnerDO"("id", "decisionBeanDefRef", "splitTransmission", "communicationRef", "sendingPartnerReferrerRef", "receivingPartnerReferrerRef", "maxNoOfRetries", "retryDelay", "mergeTypeDefRef") values
    (
        nextval('"CommunicationPartnerDO_id_seq"'),
        null,
        FALSE,
        (select id from "CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_OPEN_AMOUNT'),
        (select id from "PartnerReferrerDO" where "shopRef" = 100), -- enter the required shop ID (e.g. 10000) here
        (select id from "PartnerReferrerDO" where "financeControllerRef" = (SELECT "id" FROM "FinanceControllerDO" WHERE "name" = 'Name of FinanceController')),
        5,
        '10m',
        null
    );

FinanceController2PaymentDefDO

Illustrates how the payment methods are called for export and import.

FinanceController2PaymentDefDO

INSERT INTO "FinanceController2PaymentDefDO" (id, "financeControllerRef", "financeControllerPaymentExportName", "financeControllerPaymentImportName", "paymentDefRef") 
VALUES(8, 1, 'RG', '00', 5), 
	(1, 1, 'CC', '55', 3), 
	(5, 1, 'GP', '51', 21), 
	(4, 1, 'PP', '60', 10), 
	(7, 1, 'COD', '80', 4),
	(2, 1, 'EDD', '65', 7), 
	(3, 1, 'SO', '50', 9), 
	(9, 1, 'ISH', '01', 24), 
	(10, 1, 'COP', '02', 23), 
	(11, 1, 'PRE', '03', 2);

FinanceController2TaxTypeDefDO

Illustrates how the tax types are called for export and import.

FinanceController2TaxTypeDefDO

INSERT INTO "FinanceController2TaxTypeDefDO" (id, "financeControllerRef", "financeControllerTaxTypeExportName", "financeControllerTaxTypeImportName", "taxTypeDefRef") 
VALUES(1, 1, '101', '001', 5),
 	(2, 1, '002', '002', 3),
 	(3, 1, '000', '000', 1);

9 Export Order for Order Placement

This section describes the order export format of IOM 1.1.0.0 defined in the XML schema description <jboss>/bakery.schemas/communication-v1.0/src/main/resources/META-INF/wsdl/OrderService-v1.0.xsd.

After processing the order in IOM, it is exported as an XML-file with the structure described here.

The file is located in: $OMS_VAR/communication/messages/out/

The file name has the following syntax: sendOrder_shopId_supplierId_timestamp_uniqueId.xml

E.g., sendOrder_1000_2000_20160608150955_10084.xml

XML Reference

XML elements listed in the table with specific attributes are rendered as links. Click the link to navigate to the attributes' description.

Element	Parent Element(s)	Type	minOcc	maxOcc	Description
Order	root		1	1	Root element
Customer	Order	xs:complexType	1	n	The Customer
Contact	Customer; DeliveryAddress; Address	xs:complexType	0	1	Customer contact data
Person	Customer; Receiver	xs:complexType	1	1	A customer can be a private person or a company
Company	Customer; Receiver	xs:complexType	1	1	A customer can be a private person or a company
ContactPerson	Customer; Receiver	xs:complexType	0	1	Contact person for a company
Shop	Order	xs:complexType	1	1	The Shop
Immaterial	Order	xs:complexType	1	1	The package is non-physical/ digital. No delivery address required.
Carrier	Order	xs:string	0	1	Carrier, if the package is physical/ non-digital
Email	Immaterial	xs:string	1	1	receiver mail address
Telephone	Immaterial	xs:string	0	1	receiver telephone number
Other	Immaterial	xs:string	0	1	other receiver information
DeliveryAddress	Order	xs:complexType	1	1	Delivery address, if the package is physical/ non-digital
Receiver	DeliveryAddress; Address	xs:complexType	1	1	Receiver, can be a private person or a company
Location	DeliveryAddress; Address	xs:complexType	1	1	Location of the address
City	Location	xs:string	1	1	City
PostCode	Location	xs:string	1	1	Postal code
District	Location	xs:string	0	1	District
Street	Location	xs:string	1	1	Address can be a street, post box, or a pack station
POBox	Location	xs:string	1	1	Address can be a street, post box, or a pack station
Packstation	Location	xs:string	1	1	Address can be a street, post box, or a pack station
Addition	Location	xs:string	0	2	Addition to address
Country	Location	xs:string	1	1	Country
Address	Order	xs:complexType	0	1	Generic addresses: Optional; in consultation further addresses can be specified
Sales	Order; Position	xs:complexType	1	1	Submit sales prices. In case of cash on delivery set ./Total/Gross/@amount which is the price the carrier should receive
Sum	Sales	xs:complexType	0	1	Sum of prices of order positions
Net	Sum; Charge; Total; Unit	xs:string	0	1	Net price
Gross	Sum; Charge; Total; Unit	xs:string	0	1	Gross price
Tax	Sum; Charge; Total; Unit	xs:string	0	n	Sum of VAT (value added tax) by type of VAT
Charge	Sales	xs:complexType	0	n	Fees including delivery
Total	Sales	xs:complexType	0	1	Total order amount
NoSales	Order	xs:string	1	1	Set if no payment information should be sent
Optimization	Order	xs:string	0	1	Preferred optimization. If the order can be handled in different ways a preference between best-price or processing-time can be set
SplitShipmentAllowed	Order	xs:string	0	1	Set true to deliver immediately available articles even if other articles of the order currently are not available
Position	Order	xs:complexType	1	n	For each article (line-item) there is only one order position.
Article	Position	xs:complexType	1	1	Article reference
SelectedSupplier	Position	xs:string	0	1	Supplier selected to deliver the article
Ordered	Position	xs:complexType	1	1	Ordered (quantity/delivery days)
DeliveryOption	Position	xs:complexType	0	n	Extra delivery conditions for supplier
Purchase	Position	xs:complexType	0	1	Purchase prices
Unit	Sales	xs:complexType	1	1	Price per unit
Properties	Order; Position	xs:complexType	0	n	Additional information
Property	Properties	xs:complexType	1	n	A list of key-value pairs

Attributes

Attributes of Customer

Attribute	Type	Required	Description
orderId	xs:string	x	Order ID as used by the shop

Attributes of Contact

Attribute	Type	Required	Description
email	xs:string	x	Mail address
telephone	xs:string	-	Phone number
mobile	xs-string	-	Mobile phone number
fax	xs-string	-	Fax number

Attributes of Person

Attribute	Type	Required	Description
salutation	xs:string	-	Salutation
title	xs:string	-	Title
firstname	xs-string	-	First name
lastname	xs-string	x	Last name

Attributes of Company

Attribute	Type	Required	Description
name	xs:string	x	Name of company
department	xs:string	-	Department
lineOfBusiness	xs-string	-	Area of business, e.g., automotive
costNo	xs-string	-	Cost center
commercialRegisterId	xs-string	-	Commercial register number
commercialRegisterLocation	xs-string	-	Location if commercial register
type	xs-string	-	Corporate form
vatNo	xs-string	-	TAX number (VAT)

Attributes of Shop

Attribute	Type	Required	Description
orderCreationDate	xs:dateTime	x	Date of order creation
customerId	xs:string	-	Customer ID as used by the shop
companyId	xs-string	-	Company ID as used by the shop
orderId	xs-string	-	Order ID as used by the shop
invoiceId	xs-string	-	Invoice ID

Attributes of ContactPerson

Attribute	Type	Required	Description
salutation	xs:string	-	Salutation
title	xs:string	-	Title
firstname	xs-string	-	First name
lastname	xs-string	x	Last name

Attributes of Address

Attribute	Type	Required	Description
type	xs:string	-	Type of address, e.g., "BILLING"

Attributes of Sales

Attribute	Type	Required	Description
method	xs:string	-	Payment method
currency	xs:string	-	Currency

Attributes of Net

Attribute	Type	Required	Description
amount	xs:string	-	Amount

Attributes of Gross

Attribute	Type	Required	Description
amount	xs:string	-	Amount

Attributes of Tax

Attribute	Type	Required	Description
type	xs:string	-	Type of tax, e.g., "NO_TAX", "LOW_TAX", "NORMAL_TAX", ... See documentation reference SOAP API, section codes, section 'Types of Tax'
amount	xs:string	-	Amount of tax

Attributes of Charge

Attribute	Type	Required	Description
type	xs:string	-	Type of fee, e.g., DELIVERYCHARGE, PAYMENTCHARGE

Attributes of Position

Attribute	Type	Required	Description
number	xs:string	x	Order position number

Attributes of Article

Attribute	Type	Required	Description
name	xs:string	x	Article name
articleId	xs:string	x	ID of article
ean	xs-string	-	EAN
isbn	xs-string	-	ISBN

Attributes of Ordered

Attribute	Type	Required	Description
quantity	xs:integer	x	Quantity
deliveryDays	xs:integer	x	Expected availability of article in days

Attributes of DeliveryOption

Attribute	Type	Required	Description
name	xs:string	x	Code of delivery option

Attributes of Properties

Attribute	Type	Required	Description
id	xs:string	x	ID of the property group

Attributes of Property

Attribute	Type	Required	Description
key	xs:string	x	Name of the property
value	xs:string	x	Value of the property

Example

storeOrder

Example: Order data export format

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<storeOrder xmlns="http://types.theberlinbakery.com/v1_0" xmlns:ns2="http://types.theberlinbakery.com/customer/v1_0" xmlns:ns3="http://types.theberlinbakery.com/as/v1_0" xmlns:ns4="http://types.theberlinbakery.com/immaterialorderstate/v1_0" xmlns:ns5="http://types.theberlinbakery.com/validatearticle/v1_0" xmlns:ns6="http://types.theberlinbakery.com/v1_1">
    <MessageId>7410612-64399579-0</MessageId>
    <User></User>
    <Password></Password>
    <Shop>Company.com</Shop>
    <Order>
        <Customer orderId="000000300">
            <Company name="Company"/>
            <ContactPerson salutation="Mr." firstName="Bob" lastName="Buyer"/>
            <Contact email="b.buyer@company.com" telephone="+493087723812"/>
        </Customer>
        <Shop orderCreationDate="2015-09-17T14:22:59.462+02:00" customerId="1234567" orderId="000000300"/>
        <DeliveryAddress>
            <Receiver>
                <CompanyName>Company</CompanyName>
                <ContactPerson salutation="Mr." firstName="Bov" lastName="Buyer"/>
            </Receiver>
            <Contact email="b.buyer@company.com" telephone="+493087723812"/>
            <Location>
                <City>Berlin</City>
                <PostCode>10234</PostCode>
                <Street>Street 1</Street>
                <Country>DE</Country>
            </Location>
        </DeliveryAddress>
        <Address type="BILLING">
            <Receiver>
                <CompanyName>Company</CompanyName>
                <ContactPerson salutation="Mr." firstName="Bob" lastName="Buyer"/>
            </Receiver>
            <Contact email="b.buyer@company.com" telephone=""/>
            <Location>
                <City>Berlin</City>
                <PostCode>10234</PostCode>
                <Street>Street 1</Street>
                <Country>DE</Country>
            </Location>
        </Address>
        <Sales method="PAYPAL" currency="EUR">
            <Sum>
                <Net amount="48.00000"/>
                <Gross amount="57.12000"/>
                <Tax type="FullTax" amount="9.12000"/>
            </Sum>
            <Charge type="DELIVERYCHARGE">
                <Net amount="5.00000"/>
                <Gross amount="5.00000"/>
                <Tax type="NoTax" amount="0.00000"/>
            </Charge>
            <Total>
                <Net amount="48.00000"/>
                <Gross amount="56.17000"/>
                <Tax type="FullTax" amount="8.17000"/>
            </Total>
        </Sales>
        <SplitShipmentAllowed>true</SplitShipmentAllowed>
        <Position number="1">
            <Article name="Copy of Copy of Stuurventiel 18-18-18-AET" articleId="SD111001" ean="123456789"/>
            <Ordered quantity="1500" deliveryDays="0"/>
            <Standard></Standard>
            <Sales>
                <Sum>
                    <Net amount="48.00000"/>
                    <Gross amount="57.12000"/>
                    <Tax type="FullTax" amount="9.12000"/>
                </Sum>
                <Unit>
                    <Net amount="0.03200"/>
                    <Gross amount="0.03808"/>
                </Unit>
            </Sales>
            <Purchase>
                <Sum>
                    <Net amount="30.00000"/>
                </Sum>
                <Unit>
                    <Net amount="0.02000"/>
                </Unit>
            </Purchase>
            <Properties id="references">
                <Property key="key" value="value"/>
            </Properties>
        </Position>
        <Properties id="references">
            <Property key="key" value="value"/>
        </Properties>
    </Order>
</storeOrder>

Example File

sendOrder_1000_2000_20160608150955_10084.xml

XSD's

Configuration

To get the processed orders exported as XML file, a communication partner configuration is needed. The configuration is carried out at the database table oms."CommunicationPartnerDO".
This configurations requires a so-called partner referrer configured at the table oms."PartnerReferrerDO".

PartnerReferrerDO

The oms."PartnerReferrerDO" table is configuration table to set up all partners of the OMS environment.
Partners in this context are shops, suppliers, payment providers, finance controllers, carriers, and bonus partners.

Note

The configuration is mostly already created with the setup of the partner (e.g., supplier, shop ...).

Each partner can have only one configuration in this table. This is ensured by a unique constraint at the database.

A valid configuration must and may only use one of the partner referencing columns.

Property	Description	Example
id	Primary key of the partner referrer	5000
version	Version flag	1
shopRef	Reference to a shop (oms."ShopDO")	null
supplierRef	Reference to a supplier (oms."SupplierDO")	5000
paymentProviderRef	Reference to a payment provider (oms."PaymentProviderDO")	null
financeControllerRef	Reference to a finance controller (oms."FinanceControllerDO")	null
carrierRef	Reference to a carrier (oms."CarrierDO")	null
bonusPartnerRef	Reference to a bonus partner (oms."BonusPartnerDO")	null

Example: Partner referrer for supplier with id 5000

INSERT INTO oms."PartnerReferrerDO"(
            "id", "version", "shopRef", "supplierRef", "paymentProviderRef", 
            "financeControllerRef", "carrierRef", "bonusPartnerRef")
    VALUES (5000, 1, null, 5000, null, 
            null, null, null);

CommunicationPartnerDO

Configuration table to set up all relationships for a communication between different partners. In case of order placement it is a communication between shop and supplier.

Property	Description	Example
id	Primary key of the communication partner	1010
decisionBeanDefRef	Optional: Reference to a decision bean for customized controlling of communication partners	NULL
splitTransmission	Split into single transmissions for every order position	false
communicationRef	Reference to oms."CommunicationDO", in this case CommunicationDO with key XML###MESSAGE_FILE_SENDER_BEAN###SEND_ORDER	100005 select id from oms."CommunicationDO" where "key" = 'XML###MESSAGE_FILE_SENDER_BEAN###SEND_ORDER'
receivingPartnerReferrerRef	id of the receiving oms."PartnerReferrerDO", in case of order placement the partner referrer id of the supplier or NULL for all suppliers of the OMS shop instance.	5000
sendingPartnerReferrerRef	id of the sending oms."PartnerReferrerDO", in case of order placement the partner referrer id of the shop	10010
maxNoOfRetries	Maximum number of retries in case of errornous transmissions. 0 meant no further try .	10
retryDelay	Delay of the retry	5m
mergeTypeDefRef	Optional: order merge type MergeTypeDefDO.MERGE_MASTER (10): only orders of merge type MERGE_MASTER will be transmitted MergeTypeDefDO.MERGE_CHILD(20): only orders of merge type MERGE_CHILD will be transmitted	NULL

Example

INSERT INTO oms."CommunicationPartnerDO"(
            "id", "decisionBeanDefRef", "splitTransmission", "communicationRef", 
            "receivingPartnerReferrerRef", "sendingPartnerReferrerRef", "maxNoOfRetries", 
            "retryDelay", "mergeTypeDefRef")
    VALUES (1010, null, false, 100005, 
            5000, 10010, 10, 
            '5m', null);

10 Export Return Announcement for Supplier

This chapter describes the structure and configuration for the export of return announcements for suppliers. These announcements help suppliers to prepare for a physical return of a customer’s order.

The export contains the incoming return announcements since the last export. The export of return announcements is an XML file export, which mandatorily contains the RMA number.

Note

The export is placed into the pre-configured directory BASEPATH/importarticle/out. The BASEPATH is defined by the OMS property IS_OMS_DIR_VAR.

The XML-files generated during processing are initially stored in the directory BASEPATH/importarticle/work before they are moved into the out directory.

XML Reference

The file has following syntax:

Level	Parent Element	Element	Type	minOcc	maxOcc	Size	Description	Attributes
0	-	ReturnAnnouncementExport	xs:complexType	1	1		Root element	yes
1	ReturnAnnouncementExport	ReturnAnnouncement	xs:complexType	1	n		List of return announcements	yes
2	ReturnAnnouncement	RMANumber	xs:string	1	1	50	Return Merchandise Authorization / Return Material Authorization number	no
2	ReturnAnnouncement	ShopOrderNumber	xs:string	1	1	50	Order number of shop	no
2	ReturnAnnouncement	SupplierOrderNumber	xs:string	0	1	50	Order number of supplier - received in the order response or dispatch message from the supplier	no
2	ReturnAnnouncement	Item	xs:complexType	1	n		List of return announcement items. Each item covers one product.	no
3	Item	ReturnReason	xs:string	1	1	50	Return reason - mapped at Supplier2ReturnReasonDefDO or the IOM return reasons	no
3	Item	ShopArticleNumber	xs:string	1	1	50	Article / product number from shop system	no
3	Item	SupplierArticleNumber	xs:string	0	1	50	Supplier article / product number	no
3	Item	ShopArticleName	xs:string	1	1	255	Article / product name from shop system	no
3	Item	SerialNumber	xs:string	0	1	70	Serial number of the product to return	no
2	ReturnAnnouncement	ContactPerson	xs:complexType	1	n		List of contact person / Person in charge	no
3	ContactPerson	FirstName	xs:string	0	1	50	First name of the contact person / person in charge	no
3	ContactPerson	LastName	xs:string	1	1	50	Last name of the contact person / person in charge	no
3	ContactPerson	CompanyName	xs:string	0	1	100	Contact person / person in charge is acting for this company	no
3	ContactPerson	Phone	xs:string	0	1	25	Phone number of contact person / person in charge	no
3	ContactPerson	EmailAddress	xs:string	1	1	100	Email address of contact person / person in charge	no
3	ContactPerson	CorrespondenceLanguage	xs:string	0	1	2	Language the customer should be notified in (ISO-639-1 format)	no
2	ReturnAnnouncement	PickupAddress	xs:complexType	0	1		Address where the return is to be picked up. Only included, if type of return announcement = PICKUP	no
3	PickupAddress	FirstName	xs:string	0	1	50	First name of the person where the return is to be picked up	no
3	PickupAddress	LastName	xs:string	1	1	50	Last name of the person where the return is to be picked up	no
3	PickupAddress	CompanyName	xs:string	0	1	100	Company name where the return is to be picked up	no
3	PickupAddress	StreetName	xs:string	1	1	100	Street name where the return is to be picked up, without street number	no
3	PickupAddress	StreetNumber	xs:string	0	1	20	Street number where the return is to be picked up	no
3	PickupAddress	City	xs:string	1	1	100	City where the return is to be picked up	no
3	PickupAddress	PostCode	xs:string	1	1	25	Postal code / ZIP code where the return is to be picked up	no
3	PickupAddress	District	xs:string	0	1	100	District where the return is to be picked up	no
3	PickupAddress	Addition	xs:string	0	2	100	Addition to address where the return is to be picked up	no
3	PickupAddress	Country	xs:string	1	1	3	Country where the return is to be picked up (ISO 3166-1 alpha-3 code)	no
Note The following extensions are valid since version 2.9
2	ReturnAnnouncement	Properties	PropertyGroup	0	n		List of return announcement properties.	yes
3	Properties	Property	Property	1	n		A list of key-value pairs	yes

Attributes

Required attributes for XML structure above are:

Element	Attribute	Type	Required	Description
ReturnAnnouncementExport	creationDate	xs:dateTime	yes	Timestamp when the export file was created
ReturnAnnouncement	id	xs:long	yes	Unique ID of the return announcement from IOM
ReturnAnnouncement	creationDate	xs:dateTime	yes	Timestamp when the object was stored at the IOM
ReturnAnnouncement	type	xs:string	yes	Type of return announcement - RETURN or PICKUP

Note

The following attributes are valid since version 2.9

Required attributes for the properties structure above are:

Element	Attribute	Type	Required	Description
Properties	id	xs:string	yes	The ID of the property group
Property	key	xs:string	yes	A unique identifier for the property data
Property	value	xs:string	yes	The data that is identified

Example

<ReturnAnnouncementExport creationDate="2015-09-23T15:04:52" xsi:schemaLocation="http://types.theberlinbakery.com/v1_0 ReturnAnnouncementExport-v1.0.xsd" xmlns="http://types.theberlinbakery.com/v1_0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ReturnAnnouncement creationDate="2015-09-22T22:34:12" type="RETURN" id="983401">
        <RMANumber>WÜ-8934983490</RMANumber>
        <ShopOrderNumber>10049031</ShopOrderNumber>
        <SupplierOrderNumber>893493402</SupplierOrderNumber>
        <Item>
            <ReturnReason>TRANSPORT DAMAGE</ReturnReason>
            <ShopArticleNumber>SKU#23421</ShopArticleNumber>
            <SupplierArticleNumber>W52357-130</SupplierArticleNumber>
            <ShopArticleName>T-Shirt L</ShopArticleName>
        </Item>
        <Item>
            <ReturnReason>TOO SMALL</ReturnReason>
            <ShopArticleNumber>SKU#98238</ShopArticleNumber>
            <SupplierArticleNumber>W12323-003</SupplierArticleNumber>
            <ShopArticleName>Bike Shirt short sleeve M</ShopArticleName>
        </Item>
        <ContactPerson>
            <FirstName>Rudi</FirstName>
            <LastName>Rückgabe</LastName>
            <Phone>030-98734953</Phone>
            <EmailAddress>r.return@rma.de</EmailAddress>
            <CorrespondenceLanguage>de</CorrespondenceLanguage>
        </ContactPerson>
		<PickupAddress>
            <FirstName>Claus</FirstName>
            <LastName>Return</LastName>
            <StreetName>Example Str.</StreetName>
            <StreetNumber>12a</StreetNumber>
            <City>Bonn</City>
            <PostCode>50935</PostCode>
            <Country>USA</Country>
            <District>Mitte</District>
            <Addition>Addi</Addition>
        </PickupAddress>
		<Properties id="12345">
			<Property key="key_1" value="value_1"/>
            <Property key="key_2" value="value_2"/>
		</Properties>
    </ReturnAnnouncement>   
</ReturnAnnouncementExport>

Example File

7000_9012_1086_20151117161000.ReturnAnnouncementExport.xml
Since version 2.9: 7000_200_2000_20180703091400.ReturnAnnouncementExport.xml

XSDs

ReturnAnnouncementExport-v1.0.xsd
Since version 2.9: ReturnAnnouncementExport-v1.0.xsd includes the Property-v1.0.xsd

Configuration

To configure the export of return announcements the IOM needs per shop / supplier relationship a configuration at the IOM database (table oms."OrderExportConfigDO).

Column	Description
id	Unique ID for configuration
active	Export active / inactive
shopRef	Shop which exports the return announcement
partnerReferrerRef	ID of the PartnerReferrerDO of the supplier who is the receiver of the return announcement
exportStartTime	Timestamp of first export
lastRun	Timestamp of the last run
startTimeMDB	Start time atOrderExportMDBean - after a successful run this column must be null
startTimeControl	Start time at control app - after a successful run this column must be null The general export process is triggered by a quartz job every minute
cronExportDate	Cron expression, e.g., '0 15 17 1/1 * ? *' - daily at 5:15 PM Via the last run date and this expression the next export date will be calculated. If next export date <= now the return announcement export will be performed.
fileName	Become part of the export file name. See section Export File Name.
exportBeanDefRef	Fix value - 10
jobId	Not relevant in this export
ciffers	Not relevant in this export
encoding	Not relevant in this export

Example Configuration

/* example config for shop 9012 and supplier 1086 */
/* export runs daily at 5:15 PM                   */
INSERT INTO oms."OrderExportConfigDO"(
            "id", 
            "active", 
            "shopRef", 
            "partnerReferrerRef", 
            "exportStartTime", 
            "lastRun", 
            "startTimeMDB", 
            "startTimeControl", 
            "cronExportDate", 
            "fileName", 
            "exportBeanDefRef", 
            "jobId", 
            "ciffers", 
            "encoding")
    VALUES (7000,
            TRUE, 
            9012,
            (select "id" from oms."PartnerReferrerDO" where "supplierRef" = 1086), 
            current_timestamp, 
            NULL, 
            NULL, 
            NULL, 
            '0 15 17 1/1 * ? *',
            'ReturnAnnouncementExport', 
            10,
            NULL,
            NULL,
            NULL);

Export File Name

The export configuration has direct impact to the name of export file.

Example: 7000_9012_1086_20151117161000.ReturnAnnouncementExport.xml

7000 = ID of export configuration
9012 = shopRef of export configuration
1086 = supplierRef from entity referenced by column partnerReferrerRef of export configuration
20151117161000 = export start time in format yyyyMMddHHmmss
ReturnAnnouncementExport = fileName of export configuration

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Table of Contents

1 Introduction

1.1 Glossary

2 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Dispatches

XML Reference

Attributes

Example

Example XML

XSDs

Configuration

FinanceController2PaymentDefDO

Communication Partners

3 Creditor Management - Export Open Payments a.k.a. Unpaid Amounts of Returns

XML Reference

Attributes

Example

Example File

XSDs

Configuration

FinanceController2PaymentDefDO

Communication Partners

4 Debtor Management - Export Customer Information (Debtor Service)

Prerequisite

XML Reference

Attributes

XSD's

Example

Example File

Configuration

5 Debtor Management Import Open Positions

XML Reference

Attributes

Example

Example XML for Download

XSDs

Configuration

General Configuration

FinanceController2PaymentDefDO (mandatory)

Shop2DunningLevelDO

Transformer / FTP Pull Configuration

6 Debtor Management Import Payment Histories

XML Reference

Attributes

Example

XSDs

Configuration

General Configuration

Transformer / FTP Pull Configuration

7 Export Customer

XML Reference

Attributes

Attributes of Customer

Attributes of Shop

Attributes of Person

Attributes of Contact

Attributes of Address

Attributes of IsValid

Attributes of Packstation

Attributes of BankAccount

Attributes of BankCode

Attributes of Scoring

Attributes of Properties

Attributes of Property

Example

XSD's

Configuration

8 Export Open Positions

XML Reference

Attributes

Examples

Example File

XSDs

Configuration

General Configuration

FinanceController2PaymentDefDO

FinanceController2TaxTypeDefDO

9 Export Order for Order Placement

XML Reference

Attributes

Attributes of Customer