Remove Unused Business Partners from SAP Business One Database
Author: Sudha Samudrikam, Software Labs Inc.
It is a common scenario that a SAP Business one database may contain business partner
entries that do not have any associated journal entries. Removing such unnecessary
entries not only improves performance but also reduces storage space.
This article shows how one can use xFusion Studio to identify and remove unused vendors from a SAP business one database.
Step 1: Create connection to SAP Business One
The most preferable way of connecting to a database using xFusion studio
is to create a linked connection to the one in repository.
From xFusion Studio, select Repository under Tools.
In the Connection repository dialog,
right click on mouse and select Add Connection.
Select SAP Business One Data Connector under Application tab.
Enter the appropriate server and the database information.
If you do not have windows authentication to the server, provide the necessary information
in Advanced options dialog box.
Click on Test Connection to see if it is successful. Rename the connection to SBO.
Select "Create a new package" from dynamic help pane.
Right click on Connections, click New Linked Connection
and
select SBO in the repository.
Step 2: Extract the list of vendors from SBO connection
Create a new Standard Query object. Drag and drop OCRD table from the table list on the right
and select CardCode , CardType and any other required fields .
Set a filter on cardtype so that the query returns only the list of vendors,
not all the types of business partners.
When previewed, you should see a table with all the vendor CardCodes.
Rename the query to Step 2a: Vendors.
Create another Standard Query object.
Drag and drop JDT1 table from the table list on the right.
Select ContraAct and any other columns if required.
Alias the ContraAct column to CardCode.
Rename the query to Step 2b: Journal Entries by selecting Alias option.
Step 3: Find the vendors without any Journal Entries
Create a new Transformation Query object and
use the function FindNewRecords to list out the vendors without any journal entries,
as shown in the following figure:
Rename this query to Step 3: VendorsWithNoJournalEntries.
We now have the list of all the invalid vendors.
Step 4: Delete these records from SAP Business one
Create an integration function with following code in it.
Provide the required parameters using parameters option (f(x) icon in the toolbar).
Run the integration function and Hurray! you have a cleaner database.
Transactional success or failure with relevant information will be
logged into the RemoveBPStatus.txt file that is automatically
created in the same directory as the fusion pack.
Here is the complete data flow:
Download the xFusion pack from here.
|