cancel
Showing results for 
Search instead for 
Did you mean: 

Delete SalesOrders and Invoices using SQL

azaleamaemae
Explorer
0 Kudos

I'm looking for a method or tool, similar to the Master Data Cleanup Wizard, for obliterating all Sales Orders and all Invoices. I am not interested in creating reversals or credit memos, in fact, I want to wipe those out, too.

I am asking if there's a pre-formulated SQL query that someone already has which can achieve this, without corrupting my installation.

This is a sandbox environment with a new CompanyDB that we are almost ready to put into production, but, I need a repeatable way to wipe out all the data from multiple rounds of testing

azaleamaemae
Explorer
0 Kudos

I see at this post they recommend migrating to a new database -

how to delete the marketing documents in sap b1 database | SAP Community

is that really my only option? And where is information on how to do this. Thank you

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor

Hi Brian,

First, please take a deep breath, and count to a number as high as needed.

The system is more complex than it may seem. Deleting (or updating) data from system tables will have unexpected side effects that will become apparent sooner or later. The question is not if, the question is when. Just off the top of my head:

  • the Inventory Audit Report will no longer work (reliably), and pretty much any other report as well.
  • any history logs will stop working reliably
  • data corruption will be caused > SAP support will be voided > the system will not work correctly > you will be in deep dodo.

If your purpose is to start a completely new company database, you should just do so, and use the Data Transfer Workbench to copy relevant data, and/or use the tools sontran suggested in his answer to transfer settings.

If your purpose is merely to reduce the size of your company database, you should first look at the database settings, to see if the recovery model is set to 'Simple'. Otherwise you can use the Data Archive Wizard to do a controlled delete of old transactional data (older than 2 years).

Regards,

Johan

azaleamaemae
Explorer
0 Kudos

Thank you Johan for taking the time to warn this wayfaring newb!

For what it's worth, this is a completely new companyDB, it has all the schema modifications we want, but, I'm needing to test my integration logic where I push in thousands of Business Partners from an external system via the OData Service Layer. I see that there's a Master Data Cleanup wizard that will obliterate most of the BP's that are in there (those BP's were from a past trial run), but, it cannot wipe out the BP's with test Sales Orders / Invoices..

Due to the structure of our dev team, where another group is developing a lot of the schema in a different silo, I'm handed a database .bak file that I restore from, and I have a valid CompanyDB approved by the seasoned SAP implementation guru who is working with the other dev team.

I just wish the environment they handed me was pristine (zero BP's and zero TXN's) - and I'm trying to interject some DevOps practices that I've become familiar with in other environments (where I reset the DB with a few scripts) - and I guess I just need to dig a little deeper.

FWIW - no one will be using my version of the DB for further development. I'm sure the lead dev team will spin up a proper CompanyDB for production using the suggested methods from Son Tran Van, since that team has the seasoned SAP guy working with them. My goal is only to verify that I can push in BP's with a desired address structure from our Salesforce installation

azaleamaemae
Explorer
0 Kudos

Here's the rub: after I spend time learning how to manipulate the tools mentioned by Son Tran Van, I will have a new CompanyDB, that I suppose I may need to tinker with further to expose correctly on the Service Layer (I hope it be as simple as logging in with the correct CompanyDB specified in the credentials)

I'm really wanting to stick to my core competencies (SQL, OData) without having to become an SAP expert.. I'm a little stunned that if we know I'm setting the system back to square one, there's not a script that than reset the histories and reports (report parameters? / dependencies?) back to a default state. I guess if they'd handed me an empty CompanyDB to begin with, I could always just restore from .bak.. and that's what I intend to do once I obtain one.

Is there additional state stored outside the database? (just trying to round out my knowledge a little further)

Johan_H
Active Contributor

Hi Brian,

There are no such sanctioned scripts because B1's whole premise is to be a legally valid and trustworthy business record, that does not allow users to delete data. Hence also the 2-year limit for the Data Archive Wizard, which is actually such a script...

Well anyway, if I understand you correctly, you are trying to create a test database, to test your integration scenario. The simplest way to do this is this:

  1. create a new empty database in MS SQL Server Management Studio.
  2. make a backup of your fully setup company database
  3. restore this backup on top of the new empty database (with overwrite)

The B1 system will recognize this new database to be a B1 company database.

Now have your way with the backup db, and just drop it when you are done. You do not have to call it later.

Please note, that if you delete your test db, and want to create another one, you must name it differently. B1 recognizes new dbs but not that you deleted an old one.

Regards,

Johan

azaleamaemae
Explorer
0 Kudos

I was able to achieve the environment I wanted using Solution Package.

However, my initial Sol. Pkg. was lacking only a couple of things: Multi-Branch support wasn't checked like it was in source environment (well lots of company details from OADM were missing), and the data defining the actual Branches (OBPL)

(get ready to shake your head)


I noticed, one of the first steps in the Solution Package wizard is a place for additional SQL scripts.
So, I added 2 scripts:

1.) Update Company Information in [OADM] (this sets up some bank account stuff, and the multi-branch support

2.) copy Branches [OBPL] (the warehouses seem to come over nicely, everything wires up nicely)

Now my new environments are geared up pretty nicely. I have to login to the SAPB1, Administration > Setup > General > Users and re-check which branches my integration account has access into. But that's about it.

I find Quick Copy to be a daunting maze of checkbox dependencies that either brings over more than intended (somehow picking up BP's from some of those undesired txn's) or less than intended (in my trials Quick Copy only selected 1 branch instead of all thirteen that we have specified). Also, if there's official guidance for correctly seeding the "Basic Intitialization" tab in the "Company Details" screen I haven't found it.

I appreciate your time and advice.

Johan_H
Active Contributor
0 Kudos

Hi Brian,

Unfortunately I have no experience with this type of scenario nor with these tools.

The best advice I can give you, is to post separate questions for each question, challenge, and hickup that you encounter.

Best to add both the SAP Business One and SAP Business One Extensibility tags to each question, and if the question is (for example) about the Solution package wizard, you may want to add a user tag to your question as well.

Regards,

Johan

Answers (2)

Answers (2)

SonTran
Active Contributor

Hi,

You are not allowed to delete/update any data in the system by SQL scripts.

You can use 2 tools for transferring configuration, settings... from test environment to productive environment:

  1. Solution Package
  2. Quick Copy

Hope this helps,

Son Tran

azaleamaemae
Explorer
0 Kudos

Thank you for these links!!!!!

azaleamaemae
Explorer
0 Kudos

So far, this query seems to do the trick, wiping down all the Marketing Document tables I could possibly care about, and resetting the numbering series back to 1 for everything that has a 'Primary' configuration

UPDATE NNM1 SET NextNumber=1 WHERE SeriesName='Primary'; -- reset numbering series info

TRUNCATE TABLE OINV; -- A/R Invoice
TRUNCATE TABLE ORIN; -- A/R Credit Memo
TRUNCATE TABLE ODLN; -- Delivery
TRUNCATE TABLE ORDN; -- Returns
TRUNCATE TABLE ORDR; -- Sales Order
TRUNCATE TABLE OPCH; -- A/P Invoice
TRUNCATE TABLE ORPC; -- A/P Credit Memo
TRUNCATE TABLE OPDN; -- Goods Receipt PO
TRUNCATE TABLE ORPD; -- Goods Return
TRUNCATE TABLE OPOR; -- Purchase Order
TRUNCATE TABLE OQUT; -- Sales Quotation
TRUNCATE TABLE ORCT; -- Incoming Payment
TRUNCATE TABLE OVPM; -- Outgoing Payments
TRUNCATE TABLE OIGN; -- Goods Receipt
TRUNCATE TABLE OIGE; -- Goods Issue
TRUNCATE TABLE OWTR; -- Inventory Transfer
TRUNCATE TABLE OIPF; -- Landed Costs
TRUNCATE TABLE OENT; -- Shipping Types
TRUNCATE TABLE OSAL; -- Outgoing
TRUNCATE TABLE OTRA; -- Transition
TRUNCATE TABLE ODRF; -- Drafts
TRUNCATE TABLE OCIN; -- A/R Correction Invoice
TRUNCATE TABLE OPDF; -- Payment Draft
TRUNCATE TABLE OMRV; -- Inventory Revaluation
TRUNCATE TABLE OCPI; -- A/P Correction Invoice
TRUNCATE TABLE OCPV; -- A/P Correction Invoice Reversal
TRUNCATE TABLE OCSI; -- A/R Correction Invoice
TRUNCATE TABLE OCSV; -- A/R Correction Invoice Reversal
TRUNCATE TABLE OWOR; -- Production Order
TRUNCATE TABLE ODPI; -- A/R Down Payment
TRUNCATE TABLE ODPO; -- A/P Down Payment
TRUNCATE TABLE ORCM; -- Recommendation Data
TRUNCATE TABLE OTSI; -- Sales Tax Invoice
TRUNCATE TABLE OTPI; -- Purchase Tax Invoice
TRUNCATE TABLE OPRQ; -- Stock Transfer Draft
TRUNCATE TABLE ODRF; -- Inventory Posting
TRUNCATE TABLE OIQR; -- Inventory Opening Balance
TRUNCATE TABLE OIQI; -- Tax Code Determination
TRUNCATE TABLE OTCX; -- Purchase Quotation
TRUNCATE TABLE OPQT; -- Inventory Transfer Request
TRUNCATE TABLE OWTQ; -- Capitalization
TRUNCATE TABLE OACQ; -- Credit Memo
TRUNCATE TABLE OACD; -- Inventory Counting
TRUNCATE TABLE OINC; -- Purchase Request