Archive for Alerts
SAP Business One Query to Show Open Balances by Customer (or Vendor)
Use this query to show you a list of open balances by customer group. If you would like to see
open balances by vendor, then change the Where clause to cs.cardtype = ‘S’.
/* Shows the open balances by Customer Group */ SELECT gp.GroupCode, gp.GroupName, Sum(cs.Balance) ‘Open Balance’
FROM OCRD cs INNER JOIN [...]
SAP Business One Alerts Check for BP Tax Codes
If a state tax code was not entered on the Customer’s Ship-To address then when the Sales Order is invoiced you will receive a message stating that the tax code was not provided for the order. This Alert will notify someone when the tax code was not entered. Create a Query with the following SQL [...]
SAP Business One Birthday List! (without the year)
A quick and easy query to give you a list of the birthdays from the Employee Master Data application in SBO.
Copy and paste the lines below into the Query Generator tool.
/*———————–*/ /* Displays a list of all the employees in SBO with their birthdays (without the year) */
SELECT lastname as ‘Last [...]
SAP Business One: High Quantity on a Sales Order
Use this query to check for high order quantities on a Sales Orders.
If you make an alert out of this query, then you can be notified when potential keying errors occur.
/* Shows all sales orders where the qty ordered is greater */ /* than or equal to the amount within the [...]
SAP Business One: Query to Show Customers Without Sales Employees Assigned
Use the query to show all of the customers that do not have sales employees assigned. You can use this as an alert as well.
SELECT CardCode as BP, CardName as ‘Customer Name’ FROM OCRD Where SLPCODE = -1 and CardType = ‘C’ FOR BROWSE
Thanks to Ed Monk of SBONotes.com
SAP Business One: Commonly Used Tables for Queries
For those who would like to try their hand at doing some queries for formatted searches, alerts, or reports, but do not know what the common table names are, here is a short list. Check back and I will update the list from time to time.
Master Files: OITM = Item Master [...]
Creating Custom Queries in SAP Business One
There are two tools for creating custom queries in SAP Business One, both of which will walk you through making your own query:
Query Generator
Query Wizard
The end result of both tools listed above is a results window with the actual query statement in the top portion of the window and any data that was retrieved in [...]
SAP Business One The Anatomy of a SQL Statement: Rename your columns and tables (Part 3)
Using the example from the previous lesson, lets look at how we can make a SQL query more user friendly.
SELECT ItemCode, ItemName FROM OITM The fields ItemCode and ItemName will appear as the column names for the data when the query is executed. Many times the field names (the ones from the [...]
The Anatomy of a SQL Statement – SQL Statement Breakdown (Part 2)
In the previous discussion I introduced a simple SQL query that selected all of the customer numbers and names from the SAP Customer/Vendor/Lead Master file. The query actually selects all three types (customers, vendors, and leads) because SBO uses one table/file to store this information. How you will know which type you are looking at [...]
The Anatomy of a SQL Statement – The Basics (Part 1)
The Basics
Writing your own query for SAP (or any system) can be very satisfying and cost effective, if you are a user of SBO. Once you understand the basics, you will be able to direct your computer to give you exactly what information you are looking for from the system.
Most systems have some common elements [...]