SAP BO. SAP Business One. Consultas diversas segunda parte.
Posted On jueves, 11 de agosto de 2011 at en 10:21 by Edgar Avena Vázquez
Como lo habia comentado antes este mes vamos a sacra pequeñas consultas que nos sena de gran utilidad. Estuve un poco ausente por motivos personales pero aqui seguimos.
Y en este post vamos agregar unas consultas con lo que respecta a ventas y a compras. Pasemos entonces con las primeras de ventas.
Ventas.
Consulta 1. Esta consulta nos mostrara las facturas de venta y notas de credito en un rango de fechas, especificado por nosotros ordenando los documentos de menor a mayor porcentaje de ganancia bruta.
SELECT DISTINCT
T0.TaxDate AS 'Fecha',
CASE
WHEN T0.ObjType = 13 THEN 'Inv'
WHEN T0.ObjType = 14 THEN 'CM'
ELSE 'Error'
END AS 'Inv/CM',
T0.DocNum AS 'Num',
T0.CardCode AS 'Codigo',
T0.CardName AS 'Nombre',
CASE
WHEN T0.DocType = 'S' THEN 'Servicio'
WHEN T0.DocType = 'I' THEN 'Producto'
ELSE 'Error'
END AS 'Tipo Venta',
(T0.DocTotal - T0.VatSum) - T0.TotalExpns AS 'Subtotal',
T0.GrosProfit as 'Ganancia Bruta',
CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END AS 'Ganacia Bruta %'
FROM OINV T0
WHERE
T0.TaxDate >= '29-01-2011'
AND T0.TaxDate <= '10-08-2011'
UNION ALL
SELECT DISTINCT
T0.TaxDate AS 'Posted',
CASE
WHEN T0.ObjType = 13 THEN 'Inv'
WHEN T0.ObjType = 14 THEN 'CM'
ELSE 'Error'
END AS 'Inv/CM',
T0.DocNum AS 'Num',
T0.CardCode AS 'Codigo',
T0.CardName AS 'Nombre',
CASE
WHEN T0.DocType = 'S' THEN 'Servicio'
WHEN T0.DocType = 'I' THEN 'Product0'
ELSE 'Error'
END AS 'Tipo Venta',
((T0.DocTotal - T0.VatSum) - T0.TotalExpns ) * -1 AS 'Subtotal',
T0.GrosProfit * -1,
CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * -100
END AS 'Ganacia Bruta %'
FROM ORIN T0
WHERE
T0.TaxDate >= '29-01-2011'
AND T0.TaxDate <= '10-08-2011'
ORDER BY
CASE
WHEN ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) = 0.000 THEN 0.000
ELSE ((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END,
T0.GrosProfit,
T0.CardName,
T0.DocNum
Un ejemplo del resultado a continuacion:
Consulta 2. Esta consulta nso mostrara los empleados de venta y los totales de los montos en los documentos en los que figuran como titulares.
SELECT
T1.SlpName AS 'Personal de ventas ',--T0.SlpCode AS 'SP Code',
COUNT(T0.DocNum) AS 'Numero de documentos',
'Credit Memos' AS 'Tipo documento',
SUM ((T0.DocTotal) * -1) AS 'Total de los documentos',
SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1) AS 'Total en productos',
SUM ((T0.GrosProfit) * -1) AS 'Ganancia bruta',
CASE
WHEN (SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) = 0.00 THEN 0.00
ELSE (SUM ((T0.GrosProfit) * -1)/SUM (((T0.DocTotal - T0.VatSum) - T0.TotalExpns) * -1)) * 100
END AS 'Ganancia bruta %',
SUM ((T0.PaidToDate) * -1) AS 'Pagado a la fecha',
SUM ((T0.DocTotal - T0.PaidToDate) *-1) AS 'Documentos abiertos'
FROM ORIN T0
INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCodeWHERE
(T1.SlpName LIKE '%%%%' OR '%' = ' ')
AND T0.TaxDate >= '29-01-2011'
AND T0.TaxDate <= '29-11-2011'
GROUP BY T0.SlpCode, T1.SlpName
UNION
SELECT
T1.SlpName AS 'Personal de ventas ',
COUNT(T0.DocNum) AS 'Numero de documentos',
SUM (T0.DocTotal) AS 'Total de los documentos',
SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns) AS 'Total en productos',
SUM (T0.GrosProfit) AS 'Ganancia bruta',
CASE
WHEN (SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) = 0.00 THEN 0.00
ELSE (SUM (T0.GrosProfit)/SUM ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100
END AS 'Ganancia bruta %',
SUM (T0.PaidToDate) AS 'Pagado a la fecha',
SUM (T0.DocTotal - T0.PaidToDate) AS 'Documentos abiertos'
FROM OINV T0
INNER JOIN OSLP T1
ON T0.SlpCode = T1.SlpCode
WHERE
(T1.SlpName LIKE '%%%%' OR '%' = ' ')
AND T0.TaxDate >= '29-01-2011'
AND T0.TaxDate <= '29-11-2011'
GROUP BY T0.SlpCode, T1.SlpName
Aquí un ejemplo del resultado.
Compras.
Consulta 3. Una consulta que nos da un listado de articulos de inventario que es necesario comprar basandose en los limites de inventario que existen en los datos maestros de los articulos.
SELECT
T0.CardCode AS 'Pref Vend',T2.CardName AS 'Pref Vend Name',
T0.ItemCode AS 'Item Code',
T0.ItemName AS 'Item Description',
T1.ItmsGrpNam AS 'Item Group',
T0.LeadTime AS 'Lead',
CASE
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > T0.MinOrdrQty
THEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited)
WHEN T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) < T0.MinOrdrQty
THEN T0.MinOrdrQty
END AS 'To Purch',
T0.MinOrdrQty AS 'Min Ord',
T0.DfltWH AS 'Whs',
T0.MinLevel AS 'Min Inv',
T0.PrchseItem AS 'Buy',
T0.OnHand AS 'On Hand',
T0.OnOrder AS 'On Order',
T0.IsCommited AS 'Committed'
FROM OITM T0
LEFT OUTER JOIN OITB T1
ON T0.ItmsGrpCod = T1.ItmsGrpCodLEFT OUTER JOIN OCRD T2
ON T0.CardCode = T2.CardCode
WHERE
T0.InvntItem = 'Y'
AND T0.PrchseItem = 'Y'
AND T0.MinLevel - (T0.OnHand + T0.OnOrder - T0.IsCommited) > 0
AND T0.ItmsGrpCod <> 108
ORDER BY
T2.CardCode,
T0.ItemCode
FOR BROWSE
Una imagen de como se veria esto.
Espero les sean de ayuda estas consultas estamos en contacto. Saludos.
