SAP Business One 2007 integration

for SAP NetWeaver (B1iSN2007)

Build & Run: File To Database

September 2010

Subsidiary
Headquarters
Customer
Manufacturing
Site
Branch Office
Acquisition

Branch Office Supplier

Customer
Objectives
Build & Run (File To Database)

After completing this chapter, you will be able to:

 Develop an Integration Scenario to send data from File System into a
Database System
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Design – BizPackage

 Identifier/Name WS_FSFile2DBTbl
 BizArea Test
 One or multiple sender one
 One or multiple receiver one
 Trigger (Event, Scheduler) existence of file in file system
 Scenario Type (Initial, Process) process
 Potential Sender Systems F.AnySystem
 Potential Receiver Systems J.AnySystem
 BizStep 1 WS_FSFile2DBTbl
 BizStep 2
 BizStep 3
 BizStep 4
 …
Design – BizStep

 Identifier/Name WS_FSFile2DBTbl
 Sender SysType F.AnySystem
 Receiver SysType J.AnySystem
 Sender Object to be created -> Z.F.AnySystem_FileIn
 Receiver Object to be created -> Z.J.AnySystem_TBL
 Inbound Task Filter *
 Data Enrichment
 Retrieval Method 1
 Retrieval Key 1
 Retrieval Method 2
 Retrieval Key 2
 Properties
 Property 1
 Property 2
 Property 3
 Property 4
 Property 5
 Property 6
Design – Sender Object

 SysType F.AnySystem
 Object Id FileIn
 Local Object Id name of root-tag -> INFILE
 Key Handling no key handling
 Key Path
 Value Mapping
 Field 1 n.a.
 Field 2 n.a.
 Field 3 n.a.
 Field 4 n.a.
 …
 Criteria Fields
 Field 1
 Field 2
 Field 3
 Field 4
 …
Design – Receiver Object

 SysType J.AnySystem
 Object Id TBL
 Local Object Id TBL
 Key Handling no key handling
 Key Path
 Value Mapping
 Field 1
 Field 2
 Field 3
 Field 4
 …
 Criteria Fields
 Field 1 n.a.
 Field 2 n.a.
 Field 3 n.a.
 Field 4 n.a.
 …
Design – Specials

 Sender
 Add SysType Properties
 B1 Inbound
 DB Inbound
 File Inbound ID Mapping
 R/3 Inbound
 Receiver
 Add SysType Properties
 B1 Outbound
 DB Outbound DB Definitions (tablename=“B1iSNTest”, key=“DBKey”)
 File Outbound
 R/3 Outbound
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Test Inbound XML Document

Open your XML Editor

 Create new folder “File2Database”
 Create test document (File -> New -> XML document)

 Save As: C:\B1iSNworkshop\file\testmessage.xml

 Add to project folder (position on “File2Database”, right mouse click, add file)
 Save project
Check Test XML Document exists
Create Database Table

Start SQL Server Management Studio

 Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio
 Database -> SBO-COMMON -> Tables (right mouse click) -> New Table

 Structure of table
 Name of table: B1iSNTest
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Add A new System to the Landscape

Type
J.AnySystem

Name
SystemDB_01

sa

busmb

[Test Connection]

[Save]

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://localhost:1433;integratedSecurity=false;databaseName=SBO-COMMON;selectMethod=cursor
Assign the new System to the B1iSN
Landscape

2
click the save
button

1
check your new
system
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Create Sender ObjectType

1 SysType
F.AnySystem
Object Id
FileIn
Local Object Id
INFILE
Value Mapping
no Value Mapping
KeyPolicy
0 - no Key Handling

2
[Save]

3
[Generate]
4
[Activate]
Create Receiver ObjectType

1 SysType
J.AnySystem
Object Id
TBL
Local Object Id
TBL
Value Mapping
no Value Mapping
KeyPolicy
0 - no Key Handling

2
[Save]

3
[Generate]
4
[Activate]
Check new ObjectTypes

Result
 There is a new ObjectType Z.F.AnySystem_FileIn available in the B1iSN Repository
 There is a new ObjectType Z.J.AnySystem_TBL available in the B1iSN Repository
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Create BizPackage with BIU

BizPackage Identifier
WS_FSFile2DBTbl

BizArea
Test

BIU Identifier
WS_FSFile2DBTbl

Sender SysType
F.AnySystem

Receiver SysType
J.AnySystem

Sender ObjectType
Z.F.AnySystem_FileIn

Receiver ObjectType
Z.J.AnySystem_TBL

[Generate]
BizPackage & BIU Documents

Result
 The BizPackage Definition Document is generated
/sim.com.sap.b1i.datasync.001/bp.Z.WS_FSFile2DBTbl/BizPackageMETA.xml
 The BIU Documents are generated
 The BIU Definition Document
/sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/BIUMETA.F.AnySystem_J.AnySystem.xml
 The BIU Version Document
/sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/version.xml
 The BIU Transformation Flow Definition
/sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/Main.bfd
 The BIU Transformation XSL Document
/sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/biu.Z.WS_FSFile2DBTbl.xsl
Setup XML Editor Project

Add BP
 Position on File2Database, right mouse click -> add URL
 Server URL: http://localhost:8080/B1iXcellerator/exec/dummy
 Browse
 Select /sim.com.sap.b1i.datasync.001/bp.Z.WS_FSFile2DBTbl/BizPackageMETA.xml
 Double-click to add the document to the project

Add BIU Documents to the folder File2Database

 Create Folder for BIU (position on Project root, right mouse click -> add project Folder
File2DB)
 Add the 4 BIU documents (Position on BIU folder, right mouse-click -> add URL)
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/BIUMETA.F.AnySystem_J.AnySystem.xml
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/Main.bfd
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/Z.WS_FSFile2DBTbl.xsl
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/version.xml
 Store Project
XML Editor Project

Result
 XML Spy Project with BP/BIU documents available
 We can design the BP/BIU

new scenario
Check the BP/BIU Documents

 Open the BizPackage Definition Document

/sim.com.sap.b1i.datasync.001/bp.Z.WS_FSFile2DBTbl/BizPackageMETA.xml
 Pretty-print
 All default values are okay
 Open the following BIU documents
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/BIUMETA.F.AnySystem_J.AnySystem.xml
– Pretty-print
– All default values are okay
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/biu.Z.WS_FSFile2DBTbl.xsl
– Pretty-print
– We will do the transformation later
 Don’t touch the following BIU documents
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/version.xml
 /sim.com.sap.b1i.datasync.001/biu.Z.WS_FSFile2DBTbl/Main.bfd
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Define Database System Outbound

select your system

Z.J.AnySystem_TBL

B1iSNTest

DBKey

[Set To Default]

[Save]
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Define File System Inbound

F.AnySystem

1 Additional Map

[Generate own]

[Save own]
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Task

Description
 One message of Inbound Document contains multiple fields. For each field we need to insert
a record into the database table
 Combine the primary key of the DB Table
 Delete leading zeros from primary key value
 Add position number (length 5, filled with leading zeros)
 Fill in the original file key into the field FileKey (delete leading zeros)
 Fill in the text in the field DBField
 Fill the field Flag always with “O”
Required Format for Database Outbound

Name of the
Local ObjectType
“TBL”

Name of the
DB Table
“B1iSNTest”

Name of the
DB Fields
The correct Transformation for our Example
Content
Build & Run (File To Database)

Part 1: Analysis
Part 2: Create Test Inbound File and Database Test Table
Part 3: Add a new System (Database) to the System Landscape
Part 4: Create ObjectTypes
Part 5: Create Integration Scenario
Part 6: Special Definitions for Database
Part 7: Special Definitions for File Inbound
Part 8: Specify the Transformation
Part 9: Setup the Scenario and run Test
Setup the Scenario

WS_FSFile2DBTbl

Z.WS_FSFile2DBTbl

select your
file system

select your
database system
Run through the different Steps
Activate Scenario

check

[Activate]
Check Scenario in the Scenario List
Prepare the BIU Development Environment

1
check
Message recording

2
Click on the
screen
Send Test Data

Copy the Test Message into the File Inbound Folder

Check the B1SN Message Log
Check Database

Start SQL Server Management Studio

 Start -> All Programs -> Microsoft SQL Server 2005 -> SQL Server Management Studio
 Database -> SBO-COMMON -> Tables -> B1iSNTest (right mouse click) -> Open Table
Check Message

After first test, message leads to an insert

Perform a second test. The message leads to an update

Summary
Build & Run (File To Database)

You should be able to:

 Develop an Integration Scenario to send data from
File System into a Database System
© 2010 SAP AG. All Rights Reserved

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG.
The information contained herein may be changed without prior notice.
Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.
Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.
IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries,
eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POW ER6+, POWER6, POWER5+,
POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex,
MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.
Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.
Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other
countries.
Oracle is a registered trademark of Oracle Corporation.
UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.
Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.
HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C ®, World Wide Web Consortium, Massachusetts Institute of Technology.
Java is a registered trademark of Sun Microsystems, Inc.
JavaScript is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.
SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries.
Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and
services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects Software Ltd. in the United States and in other
countries.
All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only.
National product specifications may vary.
The information in this document is proprietary to SAP. No part of this document may be reproduced, copied, or transmitted in any form or for any purpose without the
express prior written permission of SAP AG.
This document is a preliminary version and not subject to your license agreement or any other agreement with SAP. This document contains only intended strategies,
developments, and functionalities of the SAP® product and is not intended to be binding upon SAP to any particular course of business, product strategy, and/or
development. Please note that this document is subject to change and may be changed by SAP at any time without notice.
SAP assumes no responsibility for errors or omissions in this document. SAP does not warrant the accuracy or completeness of the information, text, graphics, links, or other
items contained within this material. This document is provided without a warranty of any kind, either express or implied, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, or non-infringement.
SAP shall have no liability for damages of any kind including without limitation direct, special, indirect, or consequential damages that may result from the use of these
materials. This limitation shall not apply in cases of intent or gross negligence.
The statutory liability for personal injury and defective products is not affected. SAP has no control over the information that you may access through the use of hot links
contained in these materials and does not endorse your use of third-party Web pages nor provide any warranty whatsoever relating to third-party Web pages.