Skip to end of metadata
Go to start of metadata

SAP B1 SQL Tips and Tricks Introduction Page

 (Back to SAP B1 Business One Main Wiki Page - HomeArchive )

Thanks for dropping by the area which is created to provide you information about writing SQL, to give you some pointers with tips and tricks, and also executable read-only SQL which you can “copy and paste” directly into your SAP B1 system. Many thanks to those contributors who have taken their personal time to post information for all SAP B1ers in this special area for all of us.

A few words about this area before you proceed further:

  1. Read SAP's policy about using information found in Wikis – link is Wiki FAQ .  Basically, SAP is not responsible for what you find here and you are using the Wiki's content at your own risk, and be sure you understand that.
  2. There is only, and* always *will be only, SQL to generate views and display data information from SAP B1 (this type of SQL is called “read-only”).  ANY SQL which is used to update, add, delete, or any type of SQL which manipulates the actual data in SAP B1 tables is against SAP's stated support policy and will be deleted from the Wiki. The link covering this policy can be found in the Forums at http://forums.sdn.sap.com/thread.jspa?threadID=603237&tstart=0 .
  3. There are several areas in the SAP B1 SQL Tips and Tricks Wiki:
    1. “Executable SQL” indicated with “Frequently Requested SQL” (FRS) from the SAP B1 forums or “Monthly Specials” (MS) written by contributing experts. This is SQL which you can use in your own SAP B1 Installation. Look for the links to this executable SQL further down at the bottom of this page. 
    2. “Tips and Tricks” is an accumulation of tips or tricks which might prove useful in your writing of SQL and learning new skills. Link is SAP B1 SQL Contributors' Tips and Tricks  . 
    3. “Standards for Submission”. If you feel that you can contribute to the SQL in this area, please conform to the standards and examples provided. Look through a variety of already posted SQL to see what that standard is and read what is written about standards. If the standards are not followed, your SQL will be updated to the appropriate level – so do not be surprised if your posting is changed. Everyone who contributes has agreed to follow these standards – links are SAP B1 SQL Suggested Standards - Basic Outline of the SQL  and SAP B1 SQL Standards and Notes .
    4. Videos which show how to quickly enter SQL into the SQL Tips and Tricks Area.  Go ahead and download these to your PC and use your video viewer (I use the free video viewer of Media Classic Player for SWF files).  Part One shows how to use the copy and paste function to make the intial posting quicker, copying the SQL from SAP B1:  http://www.sdn.sap.com/irj/scn/elearn?rid=/library/uuid/d0c0f4f9-2135-2e10-418a-90e9f78120c1 .  Part Two shows information about confidentiality, setting in the links to the modular landing pages and other items for easier usage by the person visiting SAP B1 SQL area: SAP B1 SQL Video Add Items onto Queries Landing Page Part 2.swf
  4. An area which holds links to other sites and manuals/books helpful with SAP B1 and SQL, online training sites, books, and SAP documentation which will prove invaluable while doing you work in writing SQL in your SAP B1 installation.  The link is SAP B1 SQL Links and Additional Information  .
  5. Those who have taken the time to help you out are doing this on their own personal time. Hardly anyone is able to take the time out at a moment's notice to deal with your requirements since those who contribute have their jobs and personal lives, which obviously takes precedence. If there is no SQL here dealing with your needs, or if the SQL posted here does not meet all of your requirements, please go through the appropriate areas in SAP to get your questions or changes resolved; namely the SAP B1 Forums – the link to the General Area for SAP B1 Forums is http://forums.sdn.sap.com/category.jspa?categoryID=44&start=0  .

EXECUTABLE SQL is arranged according to the SAP B1 Module found on the Main Menu.

The categorization is a subjective decision, so look in several modules if your needs deal with more than one area.

A – (AD) Administration Module. Link is - A-AD Administration Module SQL Posted to Wiki  .

B – (FN) Financial Module. Link is - B-FN Financials Module SQL Posted to Wiki . 

C – (SO) Sales Opportunities Module. Link is - C-SO Sales Opportunities Module SQL Posted to Wiki . No SQL posted as of yet.

D – (SL) Sales A/R Module. Link is - D-SL Sales AR Module SQL Posted to Wiki .

E – (PU) Purchasing A/P Module. Link is - E-PU Purchasing AP Module SQL Posted to Wiki . 

F – (BP) Business Partner Module. Link is - F-BP Business Partner Module SQL Posted to Wiki . .

G – (BK) Banking Module. Link is - G-BK Banking Module SQL Posted to Wiki .

H – (IN) Inventory Module. Link is - H-IN Inventory Module SQL Posted to Wiki .

I – (PR) Production Module. Link is - I-PR Production Module SQL Posted to Wiki . .

J – (MR) MRP Module. Link is - J-MR MRP Module SQL Posted to Wiki . .

K – (SR) Service Module. Link is - K-SR Services Module SQL Posted to Wiki . No SQL posted as of yet.

L – (HR) Human Resources Module. Link is - L-HR Human Resources Module SQL Posted to Wiki . No SQL postings as of yet.

S - (FM) Formatted Searches.  Link is - S-FS Formatted Searches SQL Posted to Wiki . 

V – (MS) Miscellaneous Items. Link is - V-MS Micellaneous Items SQL Posted to Wiki .

Y - (SP) Stored Procedures.  Link is - Y-SP Stored Procedures for SQL Posted to Wiki .

Z -- (TS) Transaction Notification Scripts. Link is - Z-TS Transaction Notification SQL Scripts Posted to Wiki