Mastering SQL Queries for SAP Business One. 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

Spis treści ebooka

• 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 OneDatabase 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 linksthe key for the right query
        • Primary key
        • Foreign key
        • Example of table links within SAP Business One
      • Base tables versus target tables
      • Keeping it simpleThe 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 1Splash screen
        • Step 2Select tables for the report
        • Step 3Select fields and sort orders
        • Step 4Conditions and relations
        • Step 5Query 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
        • SELECTfirst 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
        • DISTINCTduplicated records can be removed
        • TOPnumber of lines returned by ranking
        • FROMdata resource can be assigned
          • A single table
          • A group of linked tables
          • Multiple tables separated by commas
        • JOINaddition table or tables can be linked
          • Inner Join
          • Outer Join
            • Left Outer Join
            • Right Outer Join
            • Full Outer Join
            • Self-Join
        • WHEREquery conditions to be defined
        • BETWEENranges to be defined from lower to higher end
        • IN/EXISTSthe value list that may satisfy the condition
        • LIKEsimilar records can be found
        • GROUP BYsummarizing the data according to the list
        • HAVINGconditions to be defined in summary report
        • ORDER BYreport result can be by your preferred order
        • UNION/UNION ALLto 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 functionwhere the most problems emerge
        • Case 4-D1: Balance of production for a month
        • Case 4-D2: How to input a fixed date range
      • Orange arrowan 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 1Approval for adding delivery document
        • Case 2"On Account" outgoing payment approval
        • Case 3Approval for invoice to special customer groups
        • Case 4Approval for over booking sales order
        • Case 5None 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 1Double quotes should be avoided
        • Case 2Price value validation on line level
        • Case 3Populating a UDF from OITM in a UDF on quotation
        • Case 4Difference between two UDFs into another UDF
        • Case 5Displaying warehouse name beside warehouse code
        • Case 6Showing purchase order due date on sales order
        • Case 7Auto populating the profit center code
        • Case 8Calculation by three user-defined fields
        • Case 9Open order reminder in new order
        • Case 10Commitment checks for warehouse in stock
        • Case 11Multiplying a field from OITM with a field on order line
        • Case 12Multiplying two UDF values from two tables
        • Case 13Last sales price for a customer
        • Case 14Calling a UDF value in the BOM to Production Order
        • Case 15Multiplying a UDF value with a system field value
        • Case 16Eliminating the duplicate lines returned by FMS query
        • Case 17Getting the sales rep code assigned to an activity form
        • Case 18FMS 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 formsrecords 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 1Blocking an outgoing payment for a specific BP
        • Case 2Restricting outgoing payments above 20,000
        • Case 3Blocking goods receipt entry
        • Case 4Blocking a sales quotation if no value in row level UDF
        • Case 5Blocking invoice based on GL account and project
        • Case 6Blocking GRPO if quantity is more than PO quantity
        • Case 7Blocking, adding, or updating an order for duplicated BP ref #
        • Case 8Blocking sales documents based on dates
        • Case 9Validation service type A/R credit memo
        • Case 10Blocking goods issue for none super user
        • Case 11Blocking Goods Receipt PO if no based PO
      • Summary
    • 9. More Complicated SQL Query Topics
      • The Case expression usage
        • Case 9-C1Displaying Transtype as code instead of a number
        • Case 9-C2Combining two queries with a Case expression
        • Case 9-C3Showing discount percentage for each interval
        • Case 9-C4Item wise subtotal in a goods receipt
        • Case 9-C5Updating UDF with different dates
      • Working with a subquery
        • Case 9-S1Item groups not in use
        • Case 9-S2YTD sales for two years
        • Case 9-S3Checking only the similar records
        • Case 9-S4Showing the last A/P invoice document date for items
      • Using PIVOT to simplify a cross tab style queries
        • Case 9-P1Monthly sales by geography
        • Case 9-P2Complete 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

pokaż cały spis treści

ukryj recenzje