Follow

Recommended Queries by Module

Summary

Below is a list of queries you might find useful, broken down by module.  For more information on how to upload these queries, see How to Import Queries.

 

 

Business Partners

 

Business Partner Balance > 10000:

To change the BP balance to analyze, simply change the final number in single quotes below

====

SELECT T0.[CardCode], T0.[CardName], T0.[Balance] FROM OCRD T0 WHERE T0.[Balance] >= '10000' OR T0.[Balance] <='-10000'

==== 

 

Business Partners Without Addresses:

====

SELECT T0.[CardCode], T0.[CardName], T1.[Street], T1.[City], T1.[State], T1.[ZipCode] FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] WHERE T1.[State] = ' '

====

 

Financials:

-99 Account Balances:

Your database should ideally never post to any of the system (-99) accounts.  Run this query to determine which accounts have a balance so that you can find root cause and fix it going forward before correcting the balances on the GL account itself.

====

SELECT T0.[Segment_0],T0.[Segment_1], T0.[AcctName], T0.[CurrTotal] FROM OACT T0 WHERE T0.[Segment_1] ='99' AND T0.[CurrTotal] <> '0'

====

  

Journal Entry > 1000:

====

SELECT T0.[Number], T0.[RefDate], T0.[LocTotal] FROM OJDT T0 WHERE T0.[LocTotal] > '1000' OR T0.[LocTotal] < '-1000'

====

 

List of Budget Accounts:

====

SELECT T0.[Segment_0], T0.[AcctName], T0.[CurrTotal], T0.[Budget] FROM OACT T0 WHERE T0.[Budget] = 'Y'

====

  

Petty Cash / Clearing Account > $500:

====

SELECT T0.[Segment_0], T0.[AcctName], T0.[CurrTotal] FROM OACT T0 WHERE T0.[CurrTotal] > '500' AND (T0.[Segment_0] = '10098' OR T0.[Segment_0] = '10099')

====

 

Banking:

Checks Cleared:

====

SELECT *

FROM OCHO T0

INNER JOIN JDT1 T1 ON T1.CheckAbs = T0.CheckKey

WHERE T0.CheckNum = [%0]

====

 

Check Register:

====

SELECT T1.[RefDate],
T1.[TransId] AS 'Journal Entry No.',
T2.[CardName] AS 'Business Partner',
T1.[LineMemo] AS Details, COALESCE(T6.[DeposNum], T4.[DocNum]) AS 'Document No.' ,
T5.[CheckNum] AS ' Check No.',
SUM(T1.[Debit] - T1.[Credit]) AS 'Balance',
T1.[Debit],
T1.[Credit],
T1.[ExtrMatch]

FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account
LEFT JOIN OCRD T2 ON T1.ContraAct = T2.CardCode
INNER JOIN OJDT T3 ON T1.TransId = T3.TransId
LEFT JOIN OVPM T4 ON T3.TransId = T4.TransId
LEFT JOIN VPM1 T5 ON T4.DocEntry = T5.DocNum and t5.CheckSum = abs(t1.debit-t1.credit)
LEFT JOIN ODPS T6 ON T1.TransId = T6.TransAbs

WHERE T0.[FormatCode] = '1000500' AND T1.[RefDate] >= '[%0]' AND T1.[RefDate] <= '[%1]' /* AND T1.[ExtrMatch] = 0 */

GROUP BY T1.[RefDate],
T1.[TransId],
T2.[CardName],
T1.[LineMemo],
T1.[Credit],
T1.[Debit],
T5.[CheckNum],
T4.[DocNum],
T6.[DeposNum],
T1.[ExtrMatch]

====

 

Check Register: Incoming Payments:

 ====

SELECT T0.[CardCode], T1.[CardName], T0.[CheckKey], T0.[CheckDate],T0.[CheckNum], T0.[BankCode], T0.[CheckSum], T2.[DeposNum] AS 'Deposit No.', T2.[DeposDate] AS 'Deposit Date', T3.[FormatCode] AS 'GL Account Deposited', T4.[DocNum] AS 'Incoming Payment Doc No.', T4.[DocDate] AS 'Incoming Payment Date'
FROM OCHH T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
LEFT JOIN ODPS T2 ON T0.DpstAbs = T2.DeposId
LEFT JOIN OACT T3 ON T2.BanckAcct = T3.AcctCode
LEFT JOIN ORCT T4 ON T4.DocEntry = T0.RcptNum

=====

 

Deposit Detail:

====

SELECT T0.[CardCode], T1.[CardName], T0.[CheckKey], T0.[CheckDate],T0.[CheckNum], T0.[BankCode], T0.[CheckSum], T2.[DeposNum] AS 'Deposit No.', T2.[DeposDate] AS 'Deposit Date', T3.[FormatCode] AS 'GL Account Deposited', T4.[DocNum] AS 'Incoming Payment Doc No.', T4.[DocDate] AS 'Incoming Payment Date'
FROM OCHH T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
LEFT JOIN ODPS T2 ON T0.DpstAbs = T2.DeposId
LEFT JOIN OACT T3 ON T2.BanckAcct = T3.AcctCode
LEFT JOIN ORCT T4 ON T4.DocEntry = T0.RcptNum
WHERE T2.[DeposNum] = [%0]

==== 

 

Deposits > 10000:

To change the deposit total to analyze, simply change the final number in single quotes below:

====

SELECT T0.[DeposNum], T0.[DeposDate], T0.[Memo], T0.[LocTotal] FROM ODPS T0 WHERE T0.[LocTotal] >= '10000'

====

 

Pay to <> Vendor Name:

This query can be used to analyze payments sent where the "Pay to" on the check doesn't match the vendor name on the business partner paid:

====

SELECT T0.[CheckNum], T0.[PmntDate], T0.[VendorCode], T0.[VendorName], T1.[CardName] FROM OCHO T0 , OCRD T1 WHERE T0.[VendorName] <> T1.[CardName]

====

 

Production:

Disassemblies by Date Range:

====

SELECT T0.[DocNum], T0.[Type], T0.[PostDate] FROM OWOR T0 WHERE T0.[Type] = 'D' AND T0.[PostDate] >= [%0] AND T0.[PostDate] < [%1]

====

 

Goods Issues > $1000:

====

SELECT T0.[DocNum], T0.[DocType], T0.[DocStatus], T0.[DocDate], T0.[DocTotal] FROM OIGE T0 WHERE T0.[DocTotal] > '1000'

====

 

Goods Receipts > $1000:

====

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[DocTotal] FROM OIGN T0 WHERE T0.[DocTotal] > '1000'

====

 

Goods Issues by Date Range:

====

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[DocTotal] FROM OIGE T0 WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] < [%1]

====

 

Goods Receipts by Date Range:

====

SELECT T0.[DocNum], T0.[DocStatus], T0.[DocDate], T0.[DocTotal] FROM OIGN T0 WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] < [%1]

==== 

 

Production Order Component Variance*:

====

/* Production Orders
Alert for Component Items Issued vs Planned Qty
If the variance is greater than the percent specified
*/
-- Here you can set the Variance Percentage to report
-- Any Variance Greater than this will show up in the alert
-- Replace this value with your desired percentage (original value is 25)
Declare @Percentage AS INT = 25
-- Here you can set the number of days prior to today that
-- will display in the alert
-- Replace this value (original value is 7)
Declare @NumDays AS INT = 7
-- These are the columns that will be displayed
Select T2.[DocNum] AS 'Production #'
, T2.[U_ORC_BE_ProdDate] AS 'Production Date'
, Case (T2.[Type])
When 'S' Then 'Standard'
When 'D' Then 'Disassembly'
Else T2.[Type]
End AS 'Production Type'
, T2.[ItemCode] AS 'Parent ItemCode'
, T3.[ItemName] AS 'Parent ItemName'
, T0.[ItemCode] AS 'Component ItemCode'
, T1.[ItemName] AS 'Component ItemName'
, T0.[PlannedQty] AS 'Component Planned Qty'
, T0.[IssuedQty] AS 'Component Issued Qty'
, (T0.[IssuedQty] - T0.[PlannedQty]) AS 'Difference'
, ((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[PlannedQty] * 100) As 'Variance %'
-- Here are the tables the data comes from
from WOR1 T0
Inner Join OITM T1
ON T1.[ItemCode] = T0.[ItemCode]
Inner Join OWOR T2
ON T2.[DocEntry] = T0.[DocEntry]
Inner Join OITM T3
ON T3.[ItemCode] = T2.[ItemCode]
Where T0.[IssuedQty] > '0'
AND ABS((T0.[IssuedQty] - T0.[PlannedQty]) / T0.[PlannedQty] * 100) > @Percentage
AND DATEDIFF ( DAY , T2.[U_ORC_BE_ProdDate], GETDATE()) <= @NumDays
Order By T2.[U_ORC_BE_ProdDate] Desc, T2.[DocNum]
For Browse

====

 

Production Order Header Variance*:

====

/* Production Orders
Alert for Produced Items Received vs Planned Qty
If the variance is greater than the percent specified
*/
-- Here you can set the Variance Percentage to report
-- Any Variance Greater than this will show up in the alert
-- Replace this value with your desired percentage (original value is 25)
Declare @Percentage AS INT = 25
-- Here you can set the number of days prior to today that
-- will display in the alert
-- Replace this value (original value is 7)
Declare @NumDays AS INT = 7
-- These are the columns that will be displayed
Select T2.[DocNum] AS 'Production #'
, T2.[U_ORC_BE_ProdDate] AS 'Production Date'
, Case (T2.[Type])
When 'S' Then 'Standard'
When 'D' Then 'Disassembly'
Else T2.[Type]
End AS 'Production Type'
, T2.[ItemCode] AS 'Parent ItemCode'
, T3.[ItemName] AS 'Parent ItemName'
, T2.[PlannedQty] AS 'Planned Qty'
, T2.[CmpltQty] AS 'Completed Qty'
, (T2.[CmpltQty] - T2.[PlannedQty]) AS 'Difference'
, ((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) As 'Variance %'
-- Here are the tables the data comes from
from OWOR T2
Inner Join OITM T3
ON T3.[ItemCode] = T2.[ItemCode]
Where T2.[CmpltQty] > '0'
AND ABS((T2.[CmpltQty] - T2.[PlannedQty]) / T2.[PlannedQty] * 100) > @Percentage
AND DATEDIFF ( DAY , T2.[U_ORC_BE_ProdDate], GETDATE()) <= @NumDays
Order By T2.[U_ORC_BE_ProdDate] Desc, T2.[DocNum]
For Browse

====

 

Sales:

Open AR Invoice > $1000:

====

SELECT T0.[DocNum], T0.[DocType],T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OINV T0 WHERE T0.[DocStatus] = 'O' AND T0.[DocTotal] >= '1000'

====

 

Sales by Salesperson:

====

SELECT T0.[DocNum], T0.[DocTotal], T0.[SlpCode] FROM ORDR T0 WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] < [%1]

====

 

Purchasing:

Open AP Invoices > $1000

====

SELECT T0.[DocNum], T0.[DocType],T0.[CardCode], T0.[CardName], T0.[DocTotal] FROM OPCH T0 WHERE T0.[DocStatus] = 'O' AND T0.[DocTotal] >= '1000'

====

 

 

* This query likely already exists in your database under Tools > Queries > User Queries > OBeer Alerts

 

Version 4.8.0

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments