Mastering SQL Queries for SAP Business One

Mastering SQL Queries for SAP Business One

by Gordon Du
Released May 2011
Publisher(s): Packt Publishing
ISBN: 9781849682367

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Exploit one of the most powerful features of SAP Business One with this practical guide to mastering SQL Queries. With the skills to quickly acquire business intelligence, your enterprise can gain the competitive edge.

  • Practical SAP query examples from an SAP Business One expert
  • Detailed steps to create and troubleshoot SQL queries for Alerts, Approvals, Formatted Searches, and Crystal Reports
  • Understand the importance and benefit of keeping SQL queries simple and easy to understand
  • Benefit from special tips and tricks related directly to SQL queries within SAP Business One

In Detail

SAP Business One is an integrated Enterprise Resource Planning (ERP) software which offers an ideal solution for growing small to midsized businesses. For such companies, retrieving the most relevant information from their business data can be key to standing out from the competition. SAP Business One is a rapidly growing software package and this book is timely in giving those businesses an advantage in the area of Business Intelligence. Having SQL query skills in- house is the most important and cost-effective move you can make in this growing field.

This practical guide will provide you with the skills to gain more specific business information from SAP Business One by using SQL queries. It will provide you with solutions for solving complicated report related problems, covering basic tools like the Query Generator and Query Wizard. More advanced content like using queries with Crystal Reports will also be delved into.

SQL query is one of the advanced tools available in SAP Business One which is easily learned and quickly utilized. By referring back to and applying the many examples in this book, you will be able to create and run correct, and therefore effective, SQL queries to help your business.

The book begins by teaching a clear definition of the SQL query, and covers the data dictionary and table links. Coverage will then jump to a higher level of complex SQL queries, discussing features like FMS. Along the way more advanced SQL Query topics will be covered, such as extending the scope of basic SQL queries for more complicated cases. You will ultimately gain in depth query knowledge to bring more Business Intelligence into SAP Business One.

Gain the skills to provide much needed business information within your company with SQL queries for SAP Business One

Table of contents

  1. Mastering SQL Queries for SAP Business One
    1. Table of Contents
    2. Mastering SQL Queries for SAP Business One
    3. Credits
    4. About the Author
    5. Acknowledgement
    6. About the Reviewers
    7. www.PacktPub.com
      1. Support files, eBooks, discount offers and more
      2. Why Subscribe?
      3. Free Access for Packt account holders
      4. Instant Updates on New Packt Books
    8. Preface
      1. Business Intelligence (BI)
      2. What this book covers
        1. Section 1: SQL Query Basic
        2. Section 2: SQL Query in Action
      3. What you need for this book
      4. Who this book is for
      5. Conventions
      6. Reader feedback
      7. Customer support
        1. Downloading the example code
        2. Errata
        3. Piracy
        4. Questions
    9. 1. SQL Query Basic
      1. 1. SAP Business One Query Users and Query Basics
        1. Who can benefit from using SQL Queries in SAP Business One?
          1. Consultant
          2. Developer
          3. SAP Business One end user
          4. Non-SAP Business One users
        2. SQL query and related terms
          1. RDBMS
          2. Table
          3. Field
          4. SQL
          5. T-SQL
          6. Subsets of SQL
          7. Query
        3. Data dictionary
          1. SAP Business One—Database tables reference
          2. Naming convention of tables for SAP Business One
            1. Three letter words
            2. "O" tables
            3. "A" tables
            4. Document header tables
            5. Document line tables
            6. Important table examples
        4. Table links—the key for the right query
          1. Primary key
          2. Foreign key
          3. Example of table links within SAP Business One
        5. Base tables versus target tables
        6. Keeping it simple—The key to build a good query
        7. Summary
      2. 2. Query Generator and Query Wizard
        1. Query Generator
          1. Query Generator overview
          2. Left part of Query Generator form
          3. Middle and right parts of Query Generator form
          4. Executing a query from query generator form
        2. Query wizard
          1. Query Wizard overview
          2. Step 1—Splash screen
          3. Step 2—Select tables for the report
          4. Step 3—Select fields and sort orders
          5. Step 4—Conditions and relations
          6. Step 5—Query wizard completion
        3. What is the difference between Query generator and Query wizard?
        4. Benefitting from built-in system queries
        5. Summary
      3. 3. Query Manager and Query Statements
        1. Query manager user interface
          1. Display all existing queries
          2. Creating and saving user queries
          3. Deleting user queries
          4. Managing query categories
        2. Commonly used statements
          1. SELECT—first statement to retrieve data
            1. The scope of the value that can be retrieved
              1. A single value
              2. A group of values
              3. Return a single database table column
              4. Return a group of database table columns
              5. Return complete database table columns
              6. Used in a subquery
            2. The numbers of columns to be included
            3. Column name descriptions
            4. Clauses can follow this statement
          2. DISTINCT—duplicated records can be removed
          3. TOP—number of lines returned by ranking
          4. FROM—data resource can be assigned
            1. A single table
            2. A group of linked tables
            3. Multiple tables separated by commas
          5. JOIN—addition table or tables can be linked
            1. Inner Join
            2. Outer Join
              1. Left Outer Join
              2. Right Outer Join
              3. Full Outer Join
              4. Self-Join
          6. WHERE—query conditions to be defined
          7. BETWEEN—ranges to be defined from lower to higher end
          8. IN/EXISTS—the value list that may satisfy the condition
          9. LIKE—similar records can be found
          10. GROUP BY—summarizing the data according to the list
          11. HAVING—conditions to be defined in summary report
          12. ORDER BY—report result can be by your preferred order
          13. UNION/UNION ALL—to put two or more queries together
        3. Some important functions to return values
          1. ISNULL() predicate
          2. SUM() function
          3. MAX() function
          4. MIN() function
          5. COUNT() function
          6. DATEDIFF() function
          7. DATEADD() function
          8. DATEPART() function
          9. CAST()/CONVERT() function
          10. CASE expressions
          11. IF expressions
        4. Summary
    10. 2. SQL Query in Action
      1. 4. Query Examples
        1. Why three categories have been chosen
        2. Defining variables for queries
          1. Case 4-R1: Four variables in one query
          2. Case 4-R2: Variables first or last
        3. Date function—where the most problems emerge
          1. Case 4-D1: Balance of production for a month
          2. Case 4-D2: How to input a fixed date range
        4. Orange arrow—an excellent tool for drill down
          1. Case 4-O1: Make it simple
          2. Case 4-O2: Sales order updating alert with drill down
        5. Getting a subtotal from the query
          1. Case 4-T1: By Union ALL
          2. Case 2: By running total
        6. Query for marketing documents
          1. Case 4-M1: Overview of BP with selection of realized balance
          2. Case 4-M2: Top five items sold
          3. Case 4-M3: A filter by notes from OCRD
          4. Case 4-M4: Adding sales employees' names to a query
          5. Case 4-M5: A case for solution just from deduction
          6. Case 4-M6: Goods Receipt PO within 10 days
          7. Case 4-M7: Quantity purchased, received, and returned
          8. Case 4-M8: Customized sales analysis report
          9. Case 4-M9: Average sales per month
          10. Case 4-M10: Credit Memo user check
          11. Case 4-M11: Delivery date on sales order
          12. Case 4-M12: Reducing from two to one line for the sales summary
          13. Case 4-M13: Tax code summary
          14. Case 4-M14: Sales by states
          15. Case 4-M15: Many linked tables in one query
          16. Case 4-M16: Sales Order with PO
        7. Query for inventory transactions
          1. Case 4-I1: Adding stock total to the query
          2. Case 4-I2: Adding a total to the query bottom
          3. Case 4-I3: Items not delivered within 15 days
          4. Case 4-I4: Active item list
          5. Case 4-I5: How to find stock taking details
          6. Case 4-I6: Query on price updates
          7. Case 4-I7: Planned quantity versus in stock
          8. Case 4-I8: Adding to the production orders list from a sales order
          9. Case 4-I9: Complete item list with or without transactions
        8. Query for financial transactions
          1. Case 4-F1: Top five customers
          2. Case 4-F2: Incoming payment
          3. Case 4-F3: Linking an incoming payment with an invoice
          4. Case 4-F4: Listing both types of payment transactions
          5. Case 4-F5: Incoming payment filtering
        9. User query for alert
          1. Case 4-A1: Creating a right alert without duplicated lines
          2. Case 4-A2: Alert for invoice without base document
          3. Case 4-A3: A/R Invoice past due alert
          4. Case 4-A4: Special ship to alert for Sales Order
          5. Case 4-A5: Open Sales Opportunity alert
          6. User query alert guide
        10. Miscellaneous query examples
          1. Case 4-X1: Query related to service call
          2. Case 4-X2: Concatenating two text columns
        11. Summary
      2. 5. Securities and Approvals
        1. How to handle securities for query usage
          1. Giving only a few users the capability to build a query report
          2. Creating queries under different categories
          3. Query Groups: a tool to assign user permissions
        2. How to use query for approval procedures
          1. Creating approval stages
          2. Creating approval templates
            1. Originator
            2. Documents
            3. Stages
            4. Terms
              1. Selecting a query for the approval template
        3. Examples of user queries for approval
          1. Case 1—Approval for adding delivery document
          2. Case 2—"On Account" outgoing payment approval
          3. Case 3—Approval for invoice to special customer groups
          4. Case 4—Approval for over booking sales order
          5. Case 5—None cash outgoing payment approval
        4. Summary
      3. 6. SQL Query for Formatted Search (FMS)
        1. Formatted Search and User-Defined Values
        2. How to work with User-Defined Values
          1. Search in existing User-Defined Values according to the saved queries
            1. Where do the $ values come from?
            2. How to get the value you need from, and for, the FMS query
            3. Can you run FMS queries directly?
            4. What is the negative sign's function in FMS query?
          2. Search in existing User-Defined Values only
        3. A typical FMS query application: auto code creation
          1. BP code auto generation
          2. Item code auto generation
          3. Special code auto generation
        4. General FMS query examples
          1. Case 1—Double quotes should be avoided
          2. Case 2—Price value validation on line level
          3. Case 3—Populating a UDF from OITM in a UDF on quotation
          4. Case 4—Difference between two UDFs into another UDF
          5. Case 5—Displaying warehouse name beside warehouse code
          6. Case 6—Showing purchase order due date on sales order
          7. Case 7—Auto populating the profit center code
          8. Case 8—Calculation by three user-defined fields
          9. Case 9—Open order reminder in new order
          10. Case 10—Commitment checks for warehouse in stock
          11. Case 11—Multiplying a field from OITM with a field on order line
          12. Case 12—Multiplying two UDF values from two tables
          13. Case 13—Last sales price for a customer
          14. Case 14—Calling a UDF value in the BOM to Production Order
          15. Case 15—Multiplying a UDF value with a system field value
          16. Case 16—Eliminating the duplicate lines returned by FMS query
          17. Case 17—Getting the sales rep code assigned to an activity form
          18. Case 18—FMS query for User-Defined Table (UDT)
        5. Summary
      4. 7. SQL Query for Reporting Tools
        1. Query Print Layout Designer (QPLD) and its usage
          1. Simple query report printing
          2. Query Print Layout Designer
            1. Working with a QPLD report
            2. Creating a QPLD report
            3. Editing a QPLD report
              1. Working with Print Layout Designer for a QPLD report
              2. Working with a property form when editing QPLD
              3. Editing QPLD field content and the limitation in editing
              4. Changing field type of QPLD
            4. Saving a QPLD report
            5. Printing a QPLD report
            6. Deleting a QPLD report
            7. Recreating the QPLD report
        2. Direct query for Crystal Reports (Command)
          1. Working with Standard Report Wizard
            1. Creating a new database connection
            2. Adding a Command to a report
            3. Working with a Command
              1. Selecting fields from a Command
              2. Working with two optional forms—records selection and templates
          2. Basic formatting for a Crystal Report
        3. Summary
      5. 8. SQL Query for a Stored Procedure
        1. Why Stored Procedure is included in this book
        2. SBO_SP_TransactionNotification overview
        3. How to work with SBO_SP_TransactionNotification
        4. Some example queries for this SP
          1. Case 1—Blocking an outgoing payment for a specific BP
          2. Case 2—Restricting outgoing payments above 20,000
          3. Case 3—Blocking goods receipt entry
          4. Case 4—Blocking a sales quotation if no value in row level UDF
          5. Case 5—Blocking invoice based on GL account and project
          6. Case 6—Blocking GRPO if quantity is more than PO quantity
          7. Case 7—Blocking, adding, or updating an order for duplicated BP ref #
          8. Case 8—Blocking sales documents based on dates
          9. Case 9—Validation service type A/R credit memo
          10. Case 10—Blocking goods issue for none super user
          11. Case 11—Blocking Goods Receipt PO if no based PO
        5. Summary
      6. 9. More Complicated SQL Query Topics
        1. The Case expression usage
          1. Case 9-C1—Displaying Transtype as code instead of a number
          2. Case 9-C2—Combining two queries with a Case expression
          3. Case 9-C3—Showing discount percentage for each interval
          4. Case 9-C4—Item wise subtotal in a goods receipt
          5. Case 9-C5—Updating UDF with different dates
        2. Working with a subquery
          1. Case 9-S1—Item groups not in use
          2. Case 9-S2—YTD sales for two years
          3. Case 9-S3—Checking only the similar records
          4. Case 9-S4—Showing the last A/P invoice document date for items
        3. Using PIVOT to simplify a cross tab style queries
          1. Case 9-P1—Monthly sales by geography
          2. Case 9-P2—Complete list of all items with/without sales
        4. Database query for Excel
          1. Creating a new data source
            1. New data source added within Excel
            2. New data source added from the control panel
          2. Query wizard for database query in Excel
          3. Microsoft Query window
        5. Avoiding pitfalls while building queries
          1. Creating a query before knowing the data table structure
          2. Complicating the logic instead of simplifying it
          3. Trying to do too many things in one query
          4. Relying on others' help only
        6. Summary
      7. A. Appendix
        1. Original transaction list by code
        2. Original transaction list by name
        3. Object codes and names
    11. Index

Product information

  • Title: Mastering SQL Queries for SAP Business One
  • Author(s): Gordon Du
  • Release date: May 2011
  • Publisher(s): Packt Publishing
  • ISBN: 9781849682367