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.
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'.
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
Query to Show When Item Min/Max is Not Set
You can use this query to view or alert you to items where the min/max on the Inventory tab is not set for an item.
SELECT ItemCode, ItemName, MinLevel, MaxLevel FROM OITM Where MinLevel = 0 or MaxLevel = 0 FOR BROWSE
Alert: 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 and associate it with an alert:
SELECT
T1.CardCode AS 'BP Code'
Need the Current Date to Use Within Your Query?
Get the current system date using the GetDate() value within your query...
Examples:
SELECT GetDate()
Returns the current date
Use the date for comparisons:
SELECT * FROM OINV WHERE DocDate <= GETDATE()
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) */
Alert: 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 and associate it with an alert:
SELECT
T1.CardCode AS 'BP Code'
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
OCRD = Customer, Vendor, and Lead Master
CRD1 = Customer Master Address
OCRG = Customer Master Group Code
The Anatomy of a SQL Statement - SQL Statement Breakdown (Part 2)
Previous Discussion:
The Anatomy of a SQL Statement - The Basics