SELECT T0.USERID AS 'User ID', T0.USER_CODE AS 'User Code', T1.LicType AS 'License Type', T2.AuthName AS 'Authorization Name'
FROM OUSR T0 LEFT JOIN USR7 T1 ON T0.USERID = T1.USERID LEFT JOIN UACR T2 ON T1.AuthorID = T2.AuthID ORDER BY T0.USERID, T2.AuthNamemercredi 22 février 2023
SAP Business One query that compares all salespersons
SELECT T0.SlpName AS 'Salesperson', SUM(T1.LineTotal) AS 'Total Sales'
FROM OSLP T0 INNER JOIN ORDR T2 ON T0.SlpCode = T2.SlpCode INNER JOIN RDR1 T1 ON T2.DocEntry = T1.DocEntry WHERE T2.DocDate >= '[%0]' AND T2.DocDate <= '[%1]' GROUP BY T0.SlpName ORDER BY SUM(T1.LineTotal) DESCSAP Business One query that shows the best sold product by group
SELECT T0.GroupCode, T1.ItemCode, T1.ItemName, SUM(T1.Quantity) AS 'Total Sales'
FROM OCRG T0 INNER JOIN OITM T1 ON T0.GroupCode = T1.ItmsGrpCod INNER JOIN INV1 T2 ON T1.ItemCode = T2.ItemCode GROUP BY T0.GroupCode, T1.ItemCode, T1.ItemName ORDER BY T0.GroupCode, SUM(T1.Quantity) DESCmardi 21 février 2023
query that retrieves the chart of accounts data in SAP Business One and calculates the account balance for each account:
SELECT
T0.AcctCode,
T0.AcctName,
T0.Levels,
T1.AcctType,
T2.Balance
FROM
OACT T0
LEFT JOIN
(
SELECT
T1.Account,
SUM(T1.Debit - T1.Credit) as Balance
FROM
JDT1 T1
INNER JOIN
OJDT T2 ON T1.TransId = T2.TransId
WHERE
T2.RefDate <= '[end date]'
GROUP BY
T1.Account
) T2 ON T0.AcctCode = T2.Account
LEFT JOIN
OACT T1 ON T0.FatherNum = T1.AcctCode
WHERE
T0.FinanseTr = 'Y'
ORDER BY
T0.AcctCode
SAP Business One query to retrieve inventory data for all items
SELECT
T0.ItemCode,
T0.ItemName,
T0.InvntryUom,
T0.NumInBuy,
T0.NumInSale,
T0.OnHand,
T0.IsCommited,
T0.OnOrder,
T0.MinLevel,
T0.MaxLevel
FROM
OITM T0
SAP Business One query to retrieve inventory items by serial number
SELECT
T0.ItemCode,
T0.ItemName,
T1.SerialNum
FROM
OITM T0
INNER JOIN OSRN T1 ON T0.ItemCode = T1.ItemCode
WHERE
T1.SerialNum IN ('serial_number_1', 'serial_number_2', 'serial_number_3')
SAP Business One query to retrieve the top customers based on their total sales amount
SELECT
T0.CardCode,
T0.CardName,
SUM(T1.LineTotal) AS 'Total Sales'
FROM
OCRD T0
INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode
WHERE
T1.DocStatus = 'C'
GROUP BY
T0.CardCode,
T0.CardName
ORDER BY
SUM(T1.LineTotal) DESC
SAP Business One query to retrieve customers who have not yet made any purchases
SELECT
T0.CardCode,
T0.CardName
FROM
OCRD T0
WHERE
NOT EXISTS (
SELECT
1
FROM
OINV T1
WHERE
T0.CardCode = T1.CardCode
)
SAP Business One query to compare sales turnover between two periods
SELECT
YEAR(T1.DocDate) AS 'Year',
MONTH(T1.DocDate) AS 'Month',
SUM(CASE WHEN T1.DocDate BETWEEN '2021-01-01' AND '2021-03-31' THEN T1.LineTotal ELSE 0 END) AS 'Sales Period 1',
SUM(CASE WHEN T1.DocDate BETWEEN '2021-04-01' AND '2021-06-30' THEN T1.LineTotal ELSE 0 END) AS 'Sales Period 2'
FROM
OINV T1
WHERE
T1.DocStatus = 'C'
AND T1.DocDate BETWEEN '2021-01-01' AND '2021-06-30'
GROUP BY
YEAR(T1.DocDate),
MONTH(T1.DocDate)
SAP Business One query to compare sales turnover between two salespersons
SELECT
T0.SlpName AS 'Sales Person',
YEAR(T1.DocDate) AS 'Year',
MONTH(T1.DocDate) AS 'Month',
SUM(CASE WHEN T1.SlpCode = 'SLP001' THEN T1.LineTotal ELSE 0 END) AS 'Sales Turnover 1',
SUM(CASE WHEN T1.SlpCode = 'SLP002' THEN T1.LineTotal ELSE 0 END) AS 'Sales Turnover 2'
FROM
OSLP T0
INNER JOIN OINV T1 ON T0.SlpCode = T1.SlpCode
WHERE
T1.DocStatus = 'C'
AND YEAR(T1.DocDate) = YEAR(GETDATE())
AND T0.SlpCode IN ('SLP001', 'SLP002')
GROUP BY
T0.SlpName,
YEAR(T1.DocDate),
MONTH(T1.DocDate)
SAP Business One query to retrieve the sales turnover by month
SELECT
YEAR(T0.DocDate) AS 'Year',
MONTH(T0.DocDate) AS 'Month',
SUM(T1.LineTotal) AS 'Sales Turnover'
FROM
OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE
T0.DocStatus = 'C'
AND YEAR(T0.DocDate) = YEAR(GETDATE())
GROUP BY
YEAR(T0.DocDate),
MONTH(T0.DocDate)
SAP Business One query to retrieve the sales turnover by sales person
SELECT
T0.SlpCode AS 'Sales Person Code',
T4.SlpName AS 'Sales Person Name',
SUM(T1.LineTotal) AS 'Sales Turnover'
FROM
OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode
WHERE
T0.DocStatus = 'O'
AND T0.DocDate >= '[%0]'
AND T0.DocDate <= '[%1]'
GROUP BY
T0.SlpCode,
T4.SlpName
SAP Business One query to retrieve sales orders that have not been fully invoiced yet
SELECT
T0.DocNum AS 'SO Number',
T0.DocDate AS 'SO Date',
T1.ItemCode AS 'Item Code',
T1.Dscription AS 'Item Description',
T1.Quantity AS 'Quantity Ordered',
ISNULL(T2.Quantity, 0) AS 'Quantity Invoiced'
FROM
ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN (
SELECT
BaseEntry,
BaseLine,
SUM(Quantity) AS Quantity
FROM
INV1
GROUP BY
BaseEntry,
BaseLine
) T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE
T0.DocStatus = 'O'
AND (T2.Quantity IS NULL OR T1.Quantity > T2.Quantity)
SAP Business One query that retrieves purchase orders that have not been fully received yet, including those that have not been received at all or have been partially received
SELECT
T0.DocNum AS 'PO Number',
T0.DocDate AS 'PO Date',
T1.ItemCode AS 'Item Code',
T1.Dscription AS 'Item Description',
T1.Quantity AS 'Quantity Ordered',
ISNULL(T2.Quantity, 0) AS 'Quantity Received'
FROM
OPOR T0
INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN (
SELECT
BaseEntry,
BaseLine,
SUM(Quantity) AS Quantity
FROM
PDN1
GROUP BY
BaseEntry,
BaseLine
) T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE
T0.DocStatus = 'O'
AND (T2.Quantity IS NULL OR T1.Quantity > T2.Quantity)
SAP Business One query to retrieve purchase orders that have not been received yet
SELECT
T0.DocNum AS 'PO Number',
T0.DocDate AS 'PO Date',
T1.ItemCode AS 'Item Code',
T1.Dscription AS 'Item Description',
T1.Quantity AS 'Quantity Ordered',
T2.Quantity AS 'Quantity Received'
FROM
OPOR T0
INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN (
SELECT
BaseEntry,
BaseLine,
SUM(Quantity) AS Quantity
FROM
PDN1
GROUP BY
BaseEntry,
BaseLine
) T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE
T0.DocStatus = 'O'
AND (T2.Quantity IS NULL OR T1.Quantity > T2.Quantity)
Inscription à :
Articles (Atom)