-- =====================================================================
-- Recetario de consultas SQL para SAP Business One
-- Aventi -- especialistas en SAP Business One  |  https://aventi.co
-- 20 consultas de LECTURA listas para pegar en:
--   Herramientas > Consultas > Generador de consultas
--
-- REGLA DE ORO: solo consultas de lectura (SELECT).
-- Nunca ejecutes UPDATE o DELETE sobre las tablas estándar de SAP B1.
-- Si necesitas escribir datos, hazlo con el Service Layer (API), no en la BD.
--
-- Convención: tablas con prefijo O = cabecera; sin O = líneas.
-- Los alias T0, T1... son cortos a propósito. Ajusta fechas y códigos.
-- =====================================================================


-- === VENTAS ===========================================================

-- 1. Facturas de venta de un mes, por cliente
SELECT T0.DocNum, T0.CardCode, T1.CardName, T0.DocDate, T0.DocTotal, T0.VatSum
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocDate BETWEEN '2026-01-01' AND '2026-01-31'
  AND T0.CANCELED = 'N'
ORDER BY T0.DocDate;

-- 2. Ventas por cliente y vendedor en un periodo
SELECT T1.CardCode, T2.CardName, T3.SlpName, SUM(T0.LineTotal) AS Total
FROM INV1 T0
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
LEFT JOIN OSLP T3 ON T1.SlpCode = T3.SlpCode
WHERE T1.DocDate BETWEEN '2026-01-01' AND '2026-03-31' AND T1.CANCELED = 'N'
GROUP BY T1.CardCode, T2.CardName, T3.SlpName
ORDER BY Total DESC;

-- 3. Top 20 artículos más vendidos (por importe) en un periodo
SELECT TOP 20 T0.ItemCode, T2.ItemName,
       SUM(T0.Quantity) AS Cantidad, SUM(T0.LineTotal) AS Importe
FROM INV1 T0
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T0.ItemCode = T2.ItemCode
WHERE T1.DocDate BETWEEN '2026-01-01' AND '2026-03-31' AND T1.CANCELED = 'N'
GROUP BY T0.ItemCode, T2.ItemName
ORDER BY Importe DESC;

-- 4. Pedidos de cliente abiertos (pendientes de entregar/facturar)
SELECT T0.DocNum, T0.CardCode, T1.CardName, T0.DocDate, T0.DocTotal
FROM ORDR T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O'
ORDER BY T0.DocDate;

-- 5. Ventas por mes del año en curso
SELECT MONTH(T0.DocDate) AS Mes, COUNT(*) AS Facturas, SUM(T0.DocTotal) AS Total
FROM OINV T0
WHERE YEAR(T0.DocDate) = 2026 AND T0.CANCELED = 'N'
GROUP BY MONTH(T0.DocDate)
ORDER BY Mes;


-- === CUENTAS POR COBRAR ==============================================

-- 6. Facturas de deudores abiertas con saldo, por antigüedad
SELECT T0.DocNum, T0.CardCode, T1.CardName, T0.DocDate, T0.DocDueDate,
       T0.DocTotal, T0.PaidToDate, (T0.DocTotal - T0.PaidToDate) AS Saldo
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N'
ORDER BY T0.DocDueDate;

-- 7. Saldo total por cliente (cuentas por cobrar)
SELECT T1.CardCode, T1.CardName, SUM(T0.DocTotal - T0.PaidToDate) AS SaldoTotal
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N'
GROUP BY T1.CardCode, T1.CardName
HAVING SUM(T0.DocTotal - T0.PaidToDate) > 0
ORDER BY SaldoTotal DESC;

-- 8. Facturas vencidas (fecha de vencimiento pasada y con saldo)
SELECT T0.DocNum, T1.CardName, T0.DocDueDate,
       (T0.DocTotal - T0.PaidToDate) AS Saldo
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocStatus = 'O' AND T0.CANCELED = 'N'
  AND T0.DocDueDate < '2026-07-01'
ORDER BY T0.DocDueDate;

-- 9. Pagos recibidos en un periodo (cobros)
SELECT T0.DocNum, T0.CardCode, T1.CardName, T0.DocDate, T0.DocTotal
FROM ORCT T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocDate BETWEEN '2026-01-01' AND '2026-01-31' AND T0.Canceled = 'N'
ORDER BY T0.DocDate;

-- 10. Notas de crédito de cliente de un periodo
SELECT T0.DocNum, T0.CardCode, T1.CardName, T0.DocDate, T0.DocTotal
FROM ORIN T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.DocDate BETWEEN '2026-01-01' AND '2026-03-31' AND T0.CANCELED = 'N'
ORDER BY T0.DocDate;


-- === COMPRAS =========================================================

-- 11. Compras por proveedor y periodo
SELECT T1.CardCode, T2.CardName, SUM(T0.LineTotal) AS TotalCompras
FROM PCH1 T0
INNER JOIN OPCH T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
WHERE T1.DocDate BETWEEN '2026-01-01' AND '2026-03-31' AND T1.CANCELED = 'N'
GROUP BY T1.CardCode, T2.CardName
ORDER BY TotalCompras DESC;

-- 12. Pedidos de compra abiertos por fecha prometida
SELECT T1.DocNum, T1.CardCode, T2.CardName, T0.ItemCode, T0.OpenQty, T0.ShipDate
FROM POR1 T0
INNER JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T1.CardCode = T2.CardCode
WHERE T1.DocStatus = 'O' AND T0.LineStatus = 'O'
ORDER BY T0.ShipDate;


-- === INVENTARIO ======================================================

-- 13. Existencias por almacén (disponible = en mano - comprometido)
SELECT T0.ItemCode, T0.ItemName, T1.WhsCode,
       T1.OnHand, T1.IsCommited, (T1.OnHand - T1.IsCommited) AS Disponible
FROM OITM T0
INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode
WHERE T1.OnHand > 0
ORDER BY T0.ItemCode, T1.WhsCode;

-- 14. Artículos bajo el stock mínimo
SELECT T0.ItemCode, T0.ItemName, T1.WhsCode, T1.OnHand, T1.MinStock
FROM OITM T0
INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode
WHERE T1.MinStock > 0 AND T1.OnHand < T1.MinStock
ORDER BY T0.ItemCode;

-- 15. Valor del inventario por almacén (stock * costo)
--     Nota: si activaste el costeo por almacén, usa OITW.AvgPrice en lugar de OITM.AvgPrice.
SELECT T1.WhsCode, SUM(T1.OnHand * T0.AvgPrice) AS ValorInventario
FROM OITM T0
INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode
GROUP BY T1.WhsCode
ORDER BY ValorInventario DESC;

-- 16. Artículos sin movimiento de salida en el último trimestre
SELECT T0.ItemCode, T0.ItemName
FROM OITM T0
WHERE T0.InvntItem = 'Y'
  AND T0.ItemCode NOT IN (
    SELECT DISTINCT T2.ItemCode FROM OINM T2
    WHERE T2.DocDate >= '2026-04-01' AND T2.OutQty > 0)
ORDER BY T0.ItemCode;


-- === MAESTROS Y UTILIDADES ===========================================

-- 17. Socios de negocio de tipo cliente activos
SELECT T0.CardCode, T0.CardName, T0.Phone1, T0.E_Mail, T0.Balance
FROM OCRD T0
WHERE T0.CardType = 'C' AND T0.validFor = 'Y'
ORDER BY T0.CardName;

-- 18. Flujo de un documento: pedido -> entrega -> factura (un mes)
SELECT T0.DocNum AS Pedido, T0.DocDate AS FechaPedido,
       T2.DocNum AS Entrega, T4.DocNum AS Factura
FROM ORDR T0
INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN DLN1 T3 ON T3.BaseType = 17 AND T3.BaseEntry = T1.DocEntry AND T3.BaseLine = T1.LineNum
LEFT JOIN ODLN T2 ON T3.DocEntry = T2.DocEntry
LEFT JOIN INV1 T5 ON T5.BaseType = 15 AND T5.BaseEntry = T3.DocEntry AND T5.BaseLine = T3.LineNum
LEFT JOIN OINV T4 ON T5.DocEntry = T4.DocEntry
WHERE T0.DocDate BETWEEN '2026-05-01' AND '2026-05-31'
ORDER BY T0.DocDate;

-- 19. Consulta con parámetros (el sistema pide los valores al ejecutar)
SELECT T0.DocNum, T1.CardName, T0.DocDate, T0.DocTotal
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
WHERE T0.CardCode = '[%0]'
  AND T0.DocDate BETWEEN '[%1]' AND '[%2]'
ORDER BY T0.DocDate;

-- 20. Margen por línea de factura, evitando división por cero
SELECT T0.ItemCode, T0.Dscription, T0.Quantity, T0.LineTotal, T0.GrossBuyPr,
       CASE WHEN T0.LineTotal = 0 THEN 0
            ELSE ((T0.LineTotal - (T0.Quantity * T0.GrossBuyPr)) / T0.LineTotal) * 100
       END AS MargenPct
FROM INV1 T0
INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.DocDate BETWEEN '2026-01-01' AND '2026-01-31'
  AND T1.CANCELED = 'N'
ORDER BY MargenPct DESC;

-- =====================================================================
-- Guía completa: https://aventi.co/glosario-sap-b1/consultas-sql-sap-business-one/
-- Diccionario de tablas: https://aventi.co/glosario-sap-b1/tablas-sap-business-one/
-- =====================================================================