Reference - IOM ImpEx Interfaces 2.1

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 3rd party warehouse management system.

1.1 Glossary

Term
Description
CreditorThe supplier, who has to send the products to the customer. A customer owes money to the supplier for the purchase(s) made.
DebtorA 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).

IMPEXA portmanteau word. Short for Imp(ort) and Ex(port).
IOMThe abbreviation for Intershop Order Management
OMSThe abbreviation for Order Management System, the technical name of the IOM
RMA numberNumber 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.

2.1 XML Reference

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

LevelParent ElementElementTypeminOccmaxOccSizeDescriptionAttributes
0-PurchaseOrdersxs:complexType11 Root elementno
1PurchaseOrdersMerchantIDalphanumeric1120Merchant IDno
1PurchaseOrdersTransIDalphanumeric1120Transaction numberno
1PurchaseOrdersPurchaseOrderxs:complexType1n List of all open payments for different debtorsno
2PurchaseOrderRefNralphanumeric1120Reference for payment, e.g., invoice numberno
2PurchaseOrderPurchaseOrderNoalphanumeric1120Purchase order no from supplierno
2PurchaseOrderCurrencytext113DIN / ISO 4217 (EUR, USD, GBP, ...)no
2PurchaseOrderEventTokenalphanumeric112Event token, OC = Open Position Createno
2PurchaseOrderPayTypealphanumeric115CC = 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
2PurchaseOrderCreditorxs:complexType11 Creditor of purchase orderyes
2PurchaseOrderCustomerxs:complexType01 Customer of purchase orderyes
2PurchaseOrderCompanyCodealphanumeric0110Company code, e.g., 001 for Shop-A – differs per merchantno
2PurchaseOrderPayTermalphanumeric01255Terms of paymentno
2PurchaseOrderPurchasexs:complexType11 Purchase pricesno
3PurchaseNetxs:complexType1120Net amountyes

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.

2.1.1 Attributes

Required attributes for XML-structure above are:

ElementAttributeTypeRequiredSizeDescription
Creditoridalphanumericyes20Unique ID of creditor, provided by supplier
Customeridalphanumericyes20Unique ID of customer
Netamountnumericyes20Net amount in minor units

2.2 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>

2.3 Example XML

2.4 XSDs

2.5 Configuration

see Guide - Generic Interfaces for External Accounting Systems

2.5.1 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);

2.5.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.

3.1 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:

LevelParent ElementElementTypeminOccmaxOccSizeDescriptionAttributes
0-PurchaseOrdersxs:complexType11 Root elementno
1PurchaseOrdersMerchantIDalphanumeric1120Merchant IDno
1TransIDPurchaseOrdersalphanumeric1120Transaction numberno
1PurchaseOrdersPurchaseOrderxs:complexType1n List of all open payments a.k.a. unpaid amounts for different creditorsno
2PurchaseOrderRefNralphanumeric1120Reference for payment, e.g., invoice numberno
2PurchaseOrderPurchaseOrderNoalphanumeric1120Purchase order number of the supplierno
2PurchaseOrderCurrencytext113DIN / ISO 4217 (EUR, USD, GBP, ...)no
2PurchaseOrderEventTokenalphanumeric112Event token, OC = Create Open Paymentsno
2PurchaseOrderPayTypealphanumeric115CC = 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
2PurchaseOrderCreditorxs:complexType11 Creditor of purchase orderyes
2PurchaseOrderCustomerxs:complexType01 Customer of purchase orderyes
2PurchaseOrderCompanyCodealphanumeric0110Company code, e.g., 001 for Shop-A – differs per merchantno
2PurchaseOrderPayTermalphanumeric01255Terms of paymentno
2PurchaseOrderPurchasexs:complexType11 Purchase pricesno
3PurchaseNetxs:complexType1120

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.

3.1.1 Attributes

Required attributes for XML-structure above are:

ElementAttributeTypeRequiredSizeDescription
Creditoridalphanumericyes20Unique ID of creditor, provided by supplier
Customeridalphanumericyes20Unique ID of customer
Netamountnumericyes20Net amount in minor units

3.2 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>

3.3 Example File

3.4 XSDs

3.5 Configuration

see Guide - Generic Interfaces for External Accounting Systems.

3.5.1 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);

3.5.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

4.1 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.

4.2 XML Reference

The export file has the following syntax:

LevelParent ElementElementTypeminOccmaxOccSizeDescriptionAttributes
0-Debtorsxs:complexType11 Root elementno
1DebtorsMerchantIDxs:string1120Merchant ID of the retailer / shop at the export receiving systemno
1DebtorsTransIDxs:string1120Unique ID of the exportno
1DebtorsDebtorxs:complexType1n A debtor is an entity that owes a debt to another entityyes
2DebtorEventxs:string1110

Specifies the occasion of the export

  • CD = create debtor
  • UD = update debtor
  • DD = deactivate debtor
no
2DebtorCompanyxs:complexTypechoice Company
or  Person
 The debtor is a companyyes
2DebtorPersonxs:complexType The debtor is a private personyes
2DebtorAddressxs:complexType0n Billing address of the debtoryes
2DebtorEmailxs:string01128The email address of the debtorno
2DebtorTelephonexs:string0132The telephone number of the debtorno
2DebtorCurrencyxs:string013

Currency used by the debtor - DIN / ISO 4217 (EUR, USD, GBP, ...)

Note

always empty for debtor exports

<Currency></Currency>
no
2DebtorLanguagexs:language012

Language of the debtor ISO 639-1 (de, en, ...)

Note

always empty for debtor exports

<Language></Language>
no
2DebtorBankxs:complexType01 The bank account of the debtoryes
2DebtorProperty

xs:complexType

0n Additional properties of the debtoryes
3AddressCityxs:string1132City of the billing addressno
3AddressPostCodexs:string118ZIP code of the billing addressno
3AddressStatexs:string0164Federal state of the billing addressno
3AddressDistrictxs:string0164

District of the billing address

no
3AddressPOBoxxs:stringchoice POBox or
a sequenceof Street
and HouseNumber
 

Post office box of the billing address

no
3AddressStreetxs:string Street of the billing addressno
3AddressHouseNumberxs:string House number of the billing addressno
3AddressAdditionxs:string0464Address addition of the billing addressno
3AddressCountryxs:string012

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

4.2.1 Attributes

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

ElementAttributeTypeRequiredDescription
Debtoridxs:longyesID / primary key of the debtor
Debtortypexs:intyes

Type of the debtor:

  • 1 = B2B (company)
  • 2 = B2C (private person)
Companynamexs:stringyesName of the company
Companydepartmentxs:stringnoDepartment of the company
CompanylineOfBusinessxs:stringnoLine of business of the company
CompanycostNoxs:stringnoCost center of the company
CompanycommercialRegisterIdxs:stringnoCommercial register number of the company
CompanycommercialRegisterLocationxs:stringnoLocation of commercial register of the company
Companytypexs:stringnoBusiness form of t he company
CompanyvatNoxs:stringnoVAT No. of the company
Personsalutationxs:stringnoSalutation of the private person
Persontitlexs:stringno

Title of the private person

PersonfirstNamexs:stringnoFirst name of the private person
PersonmiddleNamexs:stringnoMiddle name of the private person
PersonlastNamexs:stringyesLast name of the private person
Addresstypexs:intyesAlways 2 = billing address, for debtor exports
Bankownerxs:stringnoName of the bank account owner
Bankibanxs:stringyesIBAN - International Bank Account Number
Bankbicxs:stringnoBIC - Bank Identifier Code
BankbankNamexs:stringnoName of the bank
Propertykeyxs:stringyesKey of an additional debtor property
Propertyvaluexs:stringyesValue of an additional debtor property

4.3 XSD's

4.4 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>

4.5 Example File

4.6 Configuration

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

PropertyDescriptionExample
idID / primary key100001

decisionBeanDefRef

<optional> not needed for debtor export - set to null
- reference to oms."DecisionBeanDefDO"
null

splitTransmission

not needed for debtor export - set to FALSEFALSE

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 retries10m

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.

5.1 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'.

ElementParent ElementTypeSizeMandatoryDescription
OpenPositions-   Root element
MerchantIdOpenPositionsalphanumeric20xMerchant ID (financeControllerShopName in Shop2FinanceControllerDO)
TransIdOpenPositionsalphanumeric20xTransaction number
OpenPositionOpenPostions 1unbounded 
RefNrOpenPositionalphanumeric20xReference for payment, e.g., invoicing number (InvoiceNo in InvoicingDO)
DebtorOpenPositionDebtorSimple x

refer ID from oms."DebitorDO"

AttributeTypeSizeDescription
idxs:long-Unique id of debtor
CreationDateOpenPositionnumeric6xCreation date in format YYYY-MM-DD (e.g., 2015-01-18)
VoucherNoOpenPositionalphanumeric30xVoucher Number
VoucherDateOpenPositionalphanumeric6xVoucher date in format YYYY-MM-DD (e.g., 2015-01-18)
EntryTextOpenPositionalphanumeric255-Entry text, booking text
EntryTextExtOpenPositionalphanumeric255-Entry text, booking text extension
CurrencyOpenPositiontext3xCurrency
PayTypeOpenPositionalphanumeric3x

Shortened name of the payment method (configurable)

PayTermOpenPositionalphanumeric255-Terms of payment
OPAmountOpenPositionnumeric20xAmount of open position
SumAmountOpenPositionnumeric20xSum of all payments
OpenAmountOpenPositionnumeric20xOpen Amount
DunningLevelOpenPositionalphanumeric20-Dunning indicator
LastDunningDateOpenPositionnumeric6-Date of last reminderin format YYYY-MM-DD (e.g., 2015-01-18)
ContraAccountOpenPositionalphanumeric20-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.
EntryRangeOpenPositionalphanumeric10-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.
ModificationDateOpenPositionnumeric6-Date of last modification in format YYYY-MM-DD (e.g., 2015-01-18)

5.1.1 Attributes

Required attributes for XML-structure above are:

ElementAttributeTypeRequiredSizeDescription
Debtoridxs:longyes-NA-Unique ID of debtor

5.2 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>

5.4 XSDs

5.5 Configuration

5.5.1 General Configuration

Also see Guide - Generic Interfaces for External Accounting Systems.

5.5.2 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')

5.5.3 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);

5.5.4 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.

6.1 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".

ElementParent ElementTypeSizeMandatoryDescription
PaymentHistories-   Root element
MerchantIdPaymentHistoriesalphanumeric20xMerchant ID (financeControllerShopName in Shop2FinanceControllerDO)
TransIdPaymentHistoriesalphanumeric20xTransaction number
PaymentHistoryPaymentHistories 1unbounded 
RefNrPaymentHistoryalphanumeric20xReference for payment, e.g., invoicing number (InvoiceNo in InvoicingDO)
DebtorPaymentHistoryDebtorSimple x
AttributeTypeSizeDescription
idxs:long-Unique id of debtor
EntryDatePaymentHistorynumeric6xEntry datein format DDMMYYYY
VoucherNoPaymentHistoryalphanumeric30xVoucher Number
VoucherDatePaymentHistoryalphanumeric6xVoucher date in format DDMMYYYY
ContraAccountPaymentHistoryalphanumeric20-

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.

PositionTypePaymentHistoryalphanumeric255x

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

Four outcomes:

  1. Positive amount and 'R' position type => invoicing amount
  2. Negative amount and 'R' position type => credit note / return
  3. Positive amount and 'Z' position type => amount for payment
  4. 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.

PositionDetailsPaymentHistoryalphanumeric255-Details of position, free text for field PostionType
PositionDetailsExtPaymentHistoryalphanumeric255-Details of position extension
AmountPaymentHistorynumeric20xAmount in minor units
EntryRangePaymentHistoryalphanumeric10-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.

6.1.1 Attributes

Required attributes for XML-structure above are:

ElementAttributeTypeRequiredSizeDescription
Debtoridxs:longyes-NA-Unique ID of debtor

6.2 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>

6.3 XSDs

6.4 Configuration

6.4.1 General Configuration

Also see Guide - Generic Interfaces for External Accounting Systems.

6.4.2 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

7.1 XML Reference

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

ElementParent Element(s)TypeminOccmaxOccSizeDescription
Customersroot 11 Root element
CustomerCustomersxs:complexType1n List of all customers
ShopCustomerxs:complexType01  
AddressCustomerxs:complexType0unbounded  
PersonCustomer or Addressxs:complexType01 May occur as child of Customer or Address
ContactCustomer or Addressxs:complexType01 May occur as child of Customer or Address
IsValidAddressxs:boolean01  
LocationAddressxs:complexType    
StreetLocationxs:string01100street
HouseNoLocationxs:string0110house number
POBoxLocationxs:string0125post box number
PackstationLocationxs:complexType01  
AddressAdditionLocationxs:string04100address additions
DistrictLocationxs:string01100district
CityLocationxs:string01100city
PostCodeLocationxs:string01100zip
CountryLocationxs:string01100country
BankAccountCustomerxs:complexType0unbounded  
BankNameBankAccountxs:string0130 
AccountHolderBankAccountxs:string0130 
AccountNumberBankAccountxs:string0134 
BankCodeBankAccountxs:string0120 
ScoringCustomerxs:string0110 
PropertiesCustomerxs:complexType01  
PropertyPropertiesxs:complexType0unbounded  

7.1.1 Attributes

7.1.1.1 Attributes of Customer 

AttributeTypeRequiredSizeDescription
customerTypexs:stringx50type of customer (B2B/B2C)
dunningLevelxs:string-50dunning level
isNewCustomerxs:boolean- true if new customer
hasDeliveryStopxs:boolean- true if customer has delivery stop
versionxs:int-  
isActivexs:boolean- true if customer is active
hasNewsletterxs:boolean- true if customer has newsletter assignment

7.1.1.2 Attributes of Shop

AttributeTypeRequiredSizeDescription
shopCustomerNoxs:string 20shop customer number

7.1.1.3 Attributes of Person

AttributeTypeRequiredSizeDescription
companyNamexs:string-100name of the company
titlexs:string-10title
dateOfBirthxs:date- date of birth
lastNamexs:string-50lastname
isFemalexs:boolean- true if customer is female
firstNamexs:string-50firstname

7.1.1.4 Attributes of Contact

AttributeTypeRequiredSizeDescription
phone1xs:string-25phone number
phone2xs:string-25phone number
eMailxs:string-100mail address
faxxs:string-25fax number
mobilePhonexs:string 25mobile phone number

7.1.1.5 Attributes of Address

AttributeTypeRequiredSizeDescription
isDefaultxs:boolean- true if default address
addressTypexs:stringx50SHIPPING/BILLING/IMMATERIAL
shopAddressNoxs:string-50shop number if address
versionxs:int-  

7.1.1.6 Attributes of IsValid

AttributeTypeRequiredSizeDescription
lastValidationxs:dateTime- date of last address validation

7.1.1.7 Attributes of Packstation 

AttributeTypeRequiredSizeDescription
stationIdxs:stringx100id of container freight station
userIdxs:stringx100user id of container freight station

7.1.1.8 Attributes of BankAccount 

AttributeTypeRequiredSizeDescription
shopBankAccountNoxs:string-50shop number of bank account
isDefaultxs:boolean- true if default bank account
hasDirectDebitxs:boolean- true if bank account has direct debit
versionxs:int- version

7.1.1.9 Attributes of BankCode

AttributeTypeRequiredSizeDescription
bankCodeTypexs:stringx50sepa/standard

7.1.1.10 Attributes of Scoring 

AttributeTypeRequiredSizeDescription
sourcexs:stringx50source of the scoring
scoringDatexs:datex date of scoring
shopScoringNoxs:string-50shop number of scoring
versionxs:int- version

7.1.1.11 Attributes of Properties 

AttributeTypeRequiredSizeDescription
typexs:stringx100BOOLEAN/STRING/TIMESTAMP

7.1.1.12 Attributes of Property 

AttributeTypeRequiredSizeDescription
shopPropertyNoxs:string-50shop number of property
keyxs:stringx100property key
valuexs:stringx100property value
versionxs:int- version

7.2 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> 

7.3 XSD's

7.4 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.

PropertyDescriptionExample
idID / primary key10000
exportNamename of the export, used in filename ID / primary key - <required>IOM-CUSTOMER
exportStartDatedate when the export starts the first time2015-10-13 12:00:00
exportVersionexport version, used in filename - <required>1.00000
intervalFullInDaysintervall for full customer export in days1(once a day at 12:00:00) depends on exportStartDate
intervalIncrementInMinutesintervall for incremental customer export in minutes10 (every 10 minutes)
exportNewCustomerOnIncrementif true the new customers will be exported in incremental exports otherwise nottrue
lastExporttimestamp of last export2015-10-19 10:12:00.029
shopRefreference to the OMS shop instance - references oms."ShopDO" - <required>10000
versionversion 10 means ten exports already done1
startTimeControlApptimestamp when quartz has started the export, just for controllingnull
startTimeMDBtimestamp when MDBean was catching the JMS, just for controllingnull

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.

8.1 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.

LevelParent ElementElementTypeminOccmaxOccSizeDescriptionAttributes
0-SalesOrders    Root Elementno
1SalesOrdersMerchantIDalphanumeric1120Merchant IDno
1SalesOrdersTransIDalphanumeric1120Transaction IDno
1SalesOrdersSalesOrder 1n List of all open positions for different debtorsno
2SalesOrderRefNralphanumeric1120Reference for payment, e.g. shop order numberno
2SalesOrderCurrencytext113DIN / ISO 4217 (EUR, USD, GBP, ...)no
2SalesOrderEventTokenalphanumeric112Event token, OC = Open Position Createno
2SalesOrderPayTypealphanumeric115Configured as financeControllerPaymentExportName in FinanceController2PaymentDefDO with an internal reference to PaymentDefDOno
2SalesOrderDebtorcomplexType with attributes11 Debtor of the orderyes
2SalesOrderCustomercomplexType with attributes01 Customer of the orderyes
2SalesOrderCompanyCodealphanumeric0110Company code, e.g., 001 for Shop-A – differs per merchantno
2SalesOrderPayTermalphanumeric01255Terms of paymentno
2SalesOrderInvoiceDatenumeric116OP_Valutadate in format DDMMYYno
2SalesOrderSalecomplexType11 Sales pricesno
3SaleTaxescomplexType11 Taxes of the orderno
4TaxesTaxcomplexType with attributes11 Single taxyes
2SalesOrderGrosscomplexType with attributes1120Gross price of the orderyes
2SalesOrderNetcomplexType with attributes1120Net price of the orderyes

8.1.1 Attributes

ElementAttributeTypeRequiredSizeDescription
Debtoridnumericyes20Unique ID of debtor
Debtortypeintyes11=B2B, 2=B2C
Customeridaplhanumericyes20Unique ID of customer
Grossamountnumericyes20Gross amount in minor units
Netamountnumericyes20Net amount in minor units
Taxcodealphanumericyes10Tax Code (e.g., 001 for 19%)
Configured as financeControllerTaxTypeExportName in FinanceController2TaxTypeDefDO with an internal reference to TaxTypeDefDO
TaxnetAmountnumericyes20Net amount for this tax code in minor units
TaxtaxAmountnumericyes20Tax amount for this tax code in minor units

8.2 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>

8.3 Example File

8.4 XSDs

8.5 Configuration

8.5.1 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
    );

8.5.2 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);

8.5.3 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

9.1 XML Reference

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

ElementParent Element(s)TypeminOccmaxOccDescription
Orderroot 11Root element
CustomerOrderxs:complexType1n

The Customer

ContactCustomer; DeliveryAddress; Addressxs:complexType01Customer contact data
PersonCustomer; Receiverxs:complexType11A customer can be a private person or a company
CompanyCustomer; Receiverxs:complexType11A customer can be a private person or a company
ContactPersonCustomer; Receiverxs:complexType01Contact person for a company
ShopOrderxs:complexType11The Shop
ImmaterialOrderxs:complexType11The package is non-physical/ digital. No delivery address required.
CarrierOrderxs:string01Carrier, if the package is physical/ non-digital
EmailImmaterialxs:string11receiver mail address
TelephoneImmaterialxs:string01receiver telephone number
OtherImmaterialxs:string01other receiver information
DeliveryAddressOrderxs:complexType11Delivery address, if the package is physical/ non-digital
ReceiverDeliveryAddress; Addressxs:complexType11Receiver, can be a private person or a company
LocationDeliveryAddress; Addressxs:complexType11Location of the address
CityLocationxs:string11City
PostCodeLocationxs:string11Postal code
DistrictLocationxs:string01District
StreetLocationxs:string11Address can be a street, post box, or a pack station
POBoxLocationxs:string11Address can be a street, post box, or a pack station
PackstationLocationxs:string11Address can be a street, post box, or a pack station
AdditionLocationxs:string02Addition to address
CountryLocationxs:string11Country
AddressOrderxs:complexType01Generic addresses: Optional; in consultation further addresses can be specified
SalesOrder; Positionxs:complexType11Submit sales prices. In case of cash on delivery set ./Total/Gross/@amount which is the price the carrier should receive
SumSalesxs:complexType01Sum of prices of order positions
NetSum; Charge; Total; Unitxs:string01Net price
GrossSum; Charge; Total; Unitxs:string01Gross price
TaxSum; Charge; Total; Unitxs:string0nSum of VAT (value added tax) by type of VAT
ChargeSalesxs:complexType0nFees including delivery
TotalSalesxs:complexType01Total order amount
NoSalesOrderxs:string11Set if no payment information should be sent
OptimizationOrderxs:string01Preferred optimization. If the order can be handled in different ways a preference between best-price or processing-time can be set
SplitShipmentAllowedOrderxs:string01Set true to deliver immediately available articles even if other articles of the order currently are not available
PositionOrderxs:complexType1nFor each article (line-item) there is only one order position.
ArticlePositionxs:complexType11Article reference
SelectedSupplierPositionxs:string01Supplier selected to deliver the article
OrderedPositionxs:complexType11Ordered (quantity/delivery days)
DeliveryOptionPositionxs:complexType0nExtra delivery conditions for supplier
PurchasePositionxs:complexType01Purchase prices
UnitSalesxs:complexType11Price per unit
PropertiesOrder; Positionxs:complexType0nAdditional information
PropertyPropertiesxs:complexType1nA list of key-value pairs

9.1.1 Attributes

9.1.1.1 Attributes of Customer

AttributeTypeRequiredDescription
orderIdxs:stringxOrder ID as used by the shop

9.1.1.2 Attributes of Contact

AttributeTypeRequiredDescription
emailxs:stringxMail address
telephonexs:string-Phone number
mobilexs-string-Mobile phone number
faxxs-string-Fax number

9.1.1.3 Attributes of Person

AttributeTypeRequiredDescription
salutationxs:string-Salutation
titlexs:string-Title
firstnamexs-string-First name
lastnamexs-stringxLast name

9.1.1.4 Attributes of Company

AttributeTypeRequiredDescription
namexs:stringxName of company
departmentxs:string-Department
lineOfBusinessxs-string-Area of business, e.g., automotive
costNoxs-string-Cost center
commercialRegisterIdxs-string-Commercial register number
commercialRegisterLocationxs-string-Location if commercial register
typexs-string-Corporate form
vatNoxs-string-TAX number (VAT)

9.1.1.5 Attributes of Shop

AttributeTypeRequiredDescription
orderCreationDatexs:dateTimexDate of order creation
customerIdxs:string-Customer ID as used by the shop
companyIdxs-string-Company ID as used by the shop
orderIdxs-string-Order ID as used by the shop
invoiceIdxs-string-Invoice ID

9.1.1.6 Attributes of ContactPerson

AttributeTypeRequiredDescription
salutationxs:string-Salutation
titlexs:string-Title
firstnamexs-string-First name
lastnamexs-stringxLast name

9.1.1.7 Attributes of Address

AttributeTypeRequiredDescription
typexs:string-Type of address, e.g., "BILLING"

9.1.1.8 Attributes of Sales

AttributeTypeRequiredDescription
methodxs:string-Payment method
currencyxs:string-Currency

9.1.1.9 Attributes of Net

AttributeTypeRequiredDescription
amountxs:string-Amount

9.1.1.10 Attributes of Gross

AttributeTypeRequiredDescription
amountxs:string-Amount

9.1.1.11 Attributes of Tax

AttributeTypeRequiredDescription
typexs:string-Type of tax, e.g., "NO_TAX", "LOW_TAX", "NORMAL_TAX", ... See documentation reference SOAP API, section codes, section 'Types of Tax'
amountxs:string-Amount of tax

9.1.1.12 Attributes of Charge

AttributeTypeRequiredDescription
typexs:string-Type of fee, e.g., DELIVERYCHARGE, PAYMENTCHARGE

9.1.1.13 Attributes of Position

AttributeTypeRequiredDescription
numberxs:stringxOrder position number

9.1.1.14 Attributes of Article

AttributeTypeRequiredDescription
namexs:stringxArticle name
articleIdxs:stringxID of article
eanxs-string-EAN
isbnxs-string-ISBN

9.1.1.15 Attributes of Ordered

AttributeTypeRequiredDescription
quantityxs:integerxQuantity
deliveryDaysxs:integerxExpected availability of article in days

9.1.1.16 Attributes of DeliveryOption

AttributeTypeRequiredDescription
namexs:stringxCode of delivery option

9.1.1.17 Attributes of Properties

AttributeTypeRequiredDescription
idxs:stringxID of the property group

9.1.1.18 Attributes of Property

AttributeTypeRequiredDescription
keyxs:stringxName of the property
valuexs:stringxValue of the property

9.2 Example

9.2.1 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>

9.3 Example File

9.4 XSD's

9.5 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".

9.5.1 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.

PropertyDescriptionExample
id

Primary key of the partner referrer

5000
versionVersion flag1
shopRefReference to a shop (oms."ShopDO")null
supplierRefReference to a supplier (oms."SupplierDO")5000
paymentProviderRefReference to a payment provider (oms."PaymentProviderDO")null
financeControllerRefReference to a finance controller (oms."FinanceControllerDO")null
carrierRefReference to a carrier (oms."CarrierDO")null
bonusPartnerRefReference 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);

9.5.2 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.

PropertyDescriptionExample
idPrimary key of the communication partner1010
decisionBeanDefRefOptional: Reference to a decision bean for customized controlling of communication partnersNULL
splitTransmissionSplit into single transmissions for every order positionfalse
communicationRefReference 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'
receivingPartnerReferrerRefid 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
sendingPartnerReferrerRefid of the sending oms."PartnerReferrerDO", in case of order placement the partner referrer id of the shop10010
maxNoOfRetries

Maximum number of retries in case of errornous transmissions.
0   meant no further try .

10
retryDelayDelay of the retry5m
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.

10.1.1 XML Reference

The file has following syntax:

LevelParent ElementElementTypeminOccmaxOccSizeDescriptionAttributes
0-ReturnAnnouncementExportxs:complexType11 Root elementyes
1ReturnAnnouncementExportReturnAnnouncementxs:complexType1n List of return announcementsyes
2ReturnAnnouncementRMANumberxs:string1150Return Merchandise Authorization / Return Material Authorization numberno
2ReturnAnnouncementShopOrderNumberxs:string1150Order number of shopno
2ReturnAnnouncementSupplierOrderNumberxs:string0150Order number of supplier - received in the order response or dispatch message from the supplierno
2ReturnAnnouncementItemxs:complexType1n List of return announcement items. Each item covers one product.no
3ItemReturnReasonxs:string1150Return reason - mapped at Supplier2ReturnReasonDefDO or the IOM return reasonsno
3ItemShopArticleNumberxs:string1150Article / product number from shop systemno
3ItemSupplierArticleNumberxs:string0150Supplier article / product numberno
3ItemShopArticleNamexs:string11255Article / product name from shop systemno
3ItemSerialNumberxs:string0170Serial number of the product to returnno
2ReturnAnnouncementContactPersonxs:complexType1n List of contact person / Person in chargeno
3ContactPersonFirstNamexs:string0150First name of the contact person / person in chargeno
3ContactPersonLastNamexs:string1150Last name of the contact person / person in chargeno
3ContactPersonCompanyNamexs:string01100Contact person / person in charge is acting for this companyno
3ContactPersonPhonexs:string0125Phone number of contact person / person in chargeno
3ContactPersonEmailAddressxs:string11100Email address of contact person / person in chargeno
3ContactPersonCorrespondenceLanguagexs:string012Language the customer should be notified in (ISO-639-1 format)no
2ReturnAnnouncementPickupAddressxs:complexType01 Address where the return is to be picked up. Only included, if type of return announcement = PICKUPno
3PickupAddressFirstNamexs:string0150First name of the person where the return is to be picked upno
3PickupAddressLastNamexs:string1150Last name of the person where the return is to be picked upno
3PickupAddressCompanyNamexs:string01100Company name where the return is to be picked upno
3PickupAddressStreetNamexs:string11100Street name where the return is to be picked up, without street numberno
3PickupAddressStreetNumberxs:string0120Street number where the return is to be picked upno
3PickupAddressCityxs:string11100City where the return is to be picked upno
3PickupAddressPostCodexs:string1125Postal code / ZIP code where the return is to be picked upno
3PickupAddressDistrictxs:string01100District where the return is to be picked upno
3PickupAddressAdditionxs:string02100Addition to address where the return is to be picked upno
3PickupAddressCountryxs:string113Country 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

2ReturnAnnouncementProperties

PropertyGroup

0n
List of return announcement properties. yes
3PropertiesPropertyProperty1n

A list of key-value pairs

yes

10.1.2 Attributes

Required attributes for XML structure above are:

ElementAttributeTypeRequiredDescription
ReturnAnnouncementExportcreationDatexs:dateTimeyesTimestamp when the export file was created
ReturnAnnouncementidxs:longyesUnique ID of the return announcement from IOM
ReturnAnnouncementcreationDatexs:dateTimeyesTimestamp when the object was stored at the IOM
ReturnAnnouncementtypexs:stringyesType of return announcement - RETURN or PICKUP

Note

The following attributes are valid since version 2.9

Required attributes for the properties structure above are:

ElementAttributeTypeRequiredDescription
Propertiesidxs:stringyesThe ID of the property group
Propertykeyxs:stringyesA unique identifier for the property data
Propertyvaluexs:stringyesThe data that is identified

10.2 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>

10.3 Example File

10.4 XSDs

10.5 Configuration

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

ColumnDescription
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);

10.5.1 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.

Customer Support
Knowledge Base
Product Resources
Tickets