Mastering SQL Queries for SAP Business One电子书

Mastering SQL Queries for SAP Business One

Table of Contents

Mastering SQL Queries for SAP Business One

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

Business Intelligence (BI)

What this book covers

Section 1: SQL Query Basic

Section 2: SQL Query in Action

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. SQL Query Basic

1. SAP Business One Query Users and Query Basics

Who can benefit from using SQL Queries in SAP Business One?

Consultant

Developer

SAP Business One end user

Non-SAP Business One users

SQL query and related terms

RDBMS

Table

Field

SQL

T-SQL

Subsets of SQL

Query

Data dictionary

SAP Business One—Database tables reference

Naming convention of tables for SAP Business One

Three letter words

"O" tables

"A" tables

Document header tables

Document line tables

Important table examples

Table links—the key for the right query

Primary key

Foreign key

Example of table links within SAP Business One

Base tables versus target tables

Keeping it simple—The key to build a good query

Summary

2. Query Generator and Query Wizard

Query Generator

Query Generator overview

Left part of Query Generator form

Middle and right parts of Query Generator form

Executing a query from query generator form

Query wizard

Query Wizard overview

Step 1—Splash screen

Step 2—Select tables for the report

Step 3—Select fields and sort orders

Step 4—Conditions and relations

Step 5—Query wizard completion

What is the difference between Query generator and Query wizard?

Benefitting from built-in system queries

Summary

3. Query Manager and Query Statements

Query manager user interface

Display all existing queries

Creating and saving user queries

Deleting user queries

Managing query categories

Commonly used statements

SELECT—first statement to retrieve data

The scope of the value that can be retrieved

A single value

A group of values

Return a single database table column

Return a group of database table columns

Return complete database table columns

Used in a subquery

The numbers of columns to be included

Column name descriptions

Clauses can follow this statement

DISTINCT—duplicated records can be removed

TOP—number of lines returned by ranking

FROM－data resource can be assigned

A single table

A group of linked tables

Multiple tables separated by commas

JOIN—addition table or tables can be linked

Inner Join

Outer Join

Left Outer Join

Right Outer Join

Full Outer Join

Self-Join

WHERE—query conditions to be defined

BETWEEN—ranges to be defined from lower to higher end

IN/EXISTS—the value list that may satisfy the condition

LIKE—similar records can be found

GROUP BY—summarizing the data according to the list

HAVING—conditions to be defined in summary report

ORDER BY—report result can be by your preferred order

UNION/UNION ALL—to put two or more queries together

Some important functions to return values

ISNULL() predicate

SUM() function

MAX() function

MIN() function

COUNT() function

DATEDIFF() function

DATEADD() function

DATEPART() function

CAST()/CONVERT() function

CASE expressions

IF expressions

Summary

2. SQL Query in Action

4. Query Examples

Why three categories have been chosen

Defining variables for queries

Case 4-R1: Four variables in one query

Case 4-R2: Variables first or last

Date function—where the most problems emerge

Case 4-D1: Balance of production for a month

Case 4-D2: How to input a fixed date range

Orange arrow—an excellent tool for drill down

Case 4-O1: Make it simple

Case 4-O2: Sales order updating alert with drill down

Getting a subtotal from the query

Case 4-T1: By Union ALL

Case 2: By running total

Query for marketing documents

Case 4-M1: Overview of BP with selection of realized balance

Case 4-M2: Top five items sold

Case 4-M3: A filter by notes from OCRD

Case 4-M4: Adding sales employees' names to a query

Case 4-M5: A case for solution just from deduction

Case 4-M6: Goods Receipt PO within 10 days

Case 4-M7: Quantity purchased, received, and returned

Case 4-M8: Customized sales analysis report

Case 4-M9: Average sales per month

Case 4-M10: Credit Memo user check

Case 4-M11: Delivery date on sales order

Case 4-M12: Reducing from two to one line for the sales summary

Case 4-M13: Tax code summary

Case 4-M14: Sales by states

Case 4-M15: Many linked tables in one query

Case 4-M16: Sales Order with PO

Query for inventory transactions

Case 4-I1: Adding stock total to the query

Case 4-I2: Adding a total to the query bottom

Case 4-I3: Items not delivered within 15 days

Case 4-I4: Active item list

Case 4-I5: How to find stock taking details

Case 4-I6: Query on price updates

Case 4-I7: Planned quantity versus in stock

Case 4-I8: Adding to the production orders list from a sales order

Case 4-I9: Complete item list with or without transactions

Query for financial transactions

Case 4-F1: Top five customers

Case 4-F2: Incoming payment

Case 4-F3: Linking an incoming payment with an invoice

Case 4-F4: Listing both types of payment transactions

Case 4-F5: Incoming payment filtering

User query for alert

Case 4-A1: Creating a right alert without duplicated lines

Case 4-A2: Alert for invoice without base document

Case 4-A3: A/R Invoice past due alert

Case 4-A4: Special ship to alert for Sales Order

Case 4-A5: Open Sales Opportunity alert

User query alert guide

Miscellaneous query examples

Case 4-X1: Query related to service call

Case 4-X2: Concatenating two text columns

Summary

5. Securities and Approvals

How to handle securities for query usage

Giving only a few users the capability to build a query report

Creating queries under different categories

Query Groups: a tool to assign user permissions

How to use query for approval procedures

Creating approval stages

Creating approval templates

Originator

Documents

Stages

Terms

Selecting a query for the approval template

Examples of user queries for approval

Case 1—Approval for adding delivery document

Case 2—"On Account" outgoing payment approval

Case 3—Approval for invoice to special customer groups

Case 4—Approval for over booking sales order

Case 5—None cash outgoing payment approval

Summary

6. SQL Query for Formatted Search (FMS)

Formatted Search and User-Defined Values

How to work with User-Defined Values

Search in existing User-Defined Values according to the saved queries

Where do the $ values come from?

How to get the value you need from, and for, the FMS query

Can you run FMS queries directly?

What is the negative sign's function in FMS query?

Search in existing User-Defined Values only

A typical FMS query application: auto code creation

BP code auto generation

Item code auto generation

Special code auto generation

General FMS query examples

Case 1—Double quotes should be avoided

Case 2—Price value validation on line level

Case 3—Populating a UDF from OITM in a UDF on quotation

Case 4—Difference between two UDFs into another UDF

Case 5—Displaying warehouse name beside warehouse code

Case 6—Showing purchase order due date on sales order

Case 7—Auto populating the profit center code

Case 8—Calculation by three user-defined fields

Case 9—Open order reminder in new order

Case 10—Commitment checks for warehouse in stock

Case 11—Multiplying a field from OITM with a field on order line

Case 12—Multiplying two UDF values from two tables

Case 13—Last sales price for a customer

Case 14—Calling a UDF value in the BOM to Production Order

Case 15—Multiplying a UDF value with a system field value

Case 16—Eliminating the duplicate lines returned by FMS query

Case 17—Getting the sales rep code assigned to an activity form

Case 18—FMS query for User-Defined Table (UDT)

Summary

7. SQL Query for Reporting Tools

Query Print Layout Designer (QPLD) and its usage

Simple query report printing

Query Print Layout Designer

Working with a QPLD report

Creating a QPLD report

Editing a QPLD report

Working with Print Layout Designer for a QPLD report

Working with a property form when editing QPLD

Editing QPLD field content and the limitation in editing

Changing field type of QPLD

Saving a QPLD report

Printing a QPLD report

Deleting a QPLD report

Recreating the QPLD report

Direct query for Crystal Reports (Command)

Working with Standard Report Wizard

Creating a new database connection

Adding a Command to a report

Working with a Command

Selecting fields from a Command

Working with two optional forms—records selection and templates

Basic formatting for a Crystal Report

Summary

8. SQL Query for a Stored Procedure

Why Stored Procedure is included in this book

SBO_SP_TransactionNotification overview

How to work with SBO_SP_TransactionNotification

Some example queries for this SP

Case 1—Blocking an outgoing payment for a specific BP

Case 2—Restricting outgoing payments above 20,000

Case 3—Blocking goods receipt entry

Case 4—Blocking a sales quotation if no value in row level UDF

Case 5—Blocking invoice based on GL account and project

Case 6—Blocking GRPO if quantity is more than PO quantity

Case 7—Blocking, adding, or updating an order for duplicated BP ref #

Case 8—Blocking sales documents based on dates

Case 9—Validation service type A/R credit memo

Case 10—Blocking goods issue for none super user

Case 11—Blocking Goods Receipt PO if no based PO

Summary

9. More Complicated SQL Query Topics

The Case expression usage

Case 9-C1—Displaying Transtype as code instead of a number

Case 9-C2—Combining two queries with a Case expression

Case 9-C3—Showing discount percentage for each interval

Case 9-C4—Item wise subtotal in a goods receipt

Case 9-C5—Updating UDF with different dates

Working with a subquery

Case 9-S1—Item groups not in use

Case 9-S2—YTD sales for two years

Case 9-S3—Checking only the similar records

Case 9-S4—Showing the last A/P invoice document date for items

Using PIVOT to simplify a cross tab style queries

Case 9-P1—Monthly sales by geography

Case 9-P2—Complete list of all items with/without sales

Database query for Excel

Creating a new data source

New data source added within Excel

New data source added from the control panel

Query wizard for database query in Excel

Microsoft Query window

Avoiding pitfalls while building queries

Creating a query before knowing the data table structure

Complicating the logic instead of simplifying it

Trying to do too many things in one query

Relying on others' help only

Summary

A. Appendix

Original transaction list by code

Original transaction list by name

Object codes and names

Index