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.