Vista ventas SQL – SAP Business One
Vista en SQL Server para obtener el detalle de las ventas, con todos sus campos importantes.
–VISTA DE VENTAS
CERATE VIEW FC_VENTAS AS (
–Unicamente se tienen en cuenta las Facturas – Abonos
SELECT T0.”CardCode” AS “Cod. Cliente”,
T0.”CardName” AS “Nombre Cliente”,
T2.”CardType” AS “Tipo Cliente”,
T2.”GroupCode” AS “Cod. Grupo Cliente”,
T5.”GroupName” AS “Nombre Grupo Cliente”,
T2.”LicTradNum” AS “Ident. Fiscal”,
T0.”DocNum” AS “Nro Doc”,
T0.”Series” AS “Serie”,
T8.”SeriesName” AS “Nombre Serie”,
T0.”DocType” AS “DocTypeSAP”,
T0.”ObjType” AS “ObjType”,
(SELECT dbo.NOMBRE_OBJTYPE(T0.”ObjType”)) AS “Tipo Doc”,
T0.”DocDate” AS “Fecha Contable”,
T0.”DocDueDate” AS “Fecha Vencimiento”,
T0.”TaxDate” AS “Fecha Documento”,
T0.”CreateDate” AS “Fecha Creacion”,
T0.”Address” AS “Direccion Factura”,
T0.”VatSum” AS “Importe Total Impuesto”,
T0.”DiscSum” AS “Importe Descuento Total”,
T0.”DocCur” AS “Moneda Documento”,
T0.”DocTotal” AS “Total Documento”,
T0.”GrosProfit” AS “Beneficio Bruto Documento”,
T1.”GrossBuyPr” AS “Precio Base unitario”,
T1.”GPTtlBasPr” AS “Precio Base Total Linea”,
T0.”PaidToDate” AS “Pagado”,
T1.”LineNum” AS “LineNum”,
T1.”VisOrder” AS “VisOrder”,
T1.”ItemCode” AS “Articulo”,
T1.”Dscription” AS “Descripcion Artículo”,
T7.”ItmsGrpNam” AS “Grupo Articulo”,
T1.”Quantity” AS “Cantidad”,
T1.”UomCode” AS “Unidad Medida”,
T1.”Price” AS “Precio”,
T1.”PriceBefDi” AS “Precio Unitario”,
T1.”DiscPrcnt” AS “Porcentaje Descuento”,
T1.”LineTotal” – T1.”Price” AS “Importe del descuento”,
T1.”VatSum” AS “Importe Impuesto”,
T1.LineTotal AS “Total Linea”,
T1.”WhsCode” AS “Cod. Almacen”,
T3.”WhsName” AS “Nombre Almacen”,
T1.”AcctCode” AS “Cuenta Mayor”,
T1.”GrssProfit” AS “Ganancia Bruta Linea”,
T1.”SlpCode” AS “Cod. Vendedor”,
T6.”SlpName” AS “Nombre Vendedor”
FROM OINV T0
INNER JOIN INV1 T1 ON T0.”DocEntry” = T1.”DocEntry”
INNER JOIN OCRD T2 ON T0.”CardCode” = T2.”CardCode”
LEFT JOIN OWHS T3 ON T1.”WhsCode” = T3.”WhsCode”
LEFT JOIN OITM T4 ON T1.”ItemCode” = T4.”ItemCode”
INNER JOIN OCRG T5 ON T2.”GroupCode” = T5.”GroupCode”
INNER JOIN OSLP T6 ON T1.”SlpCode” = T6.”SlpCode”
LEFT JOIN OITB T7 ON T4.”ItmsGrpCod” = T7.”ItmsGrpCod”
INNER JOIN NNM1 T8 ON T0.”Series” = T8.”Series”
WHERE T0.”CANCELED” = ‘N’
UNION ALL
SELECT T0.”CardCode” AS “Cod. Clinte”,
T0.”CardName” AS “Nombre Cliente”,
T2.”CardType” AS “Tipo Cliente”,
T2.”GroupCode” AS “Cod. Grupo Cliente”,
T5.”GroupName” AS “Nombre Grupo Cliente”,
T2.”LicTradNum” AS “Ident. Fiscal”,
T0.”DocNum” AS “Nro Doc”,
T0.”Series” AS “Serie”,
T8.”SeriesName” AS “Nombre Serie”,
T0.”DocType” AS “DocTypeSAP”,
T0.”ObjType” AS “ObjType”,
(SELECT dbo.NOMBRE_OBJTYPE(T0.”ObjType”)) AS “Tipo Doc”,
T0.”DocDate” AS “Fecha Contable”,
T0.”DocDueDate” AS “Fecha Vencimiento”,
T0.”TaxDate” AS “Fecha Documento”,
T0.”CreateDate” AS “Fecha Creacion”,
T0.”Address” AS “Direccion Factura”,
T0.”VatSum”*-1 AS “Importe Total Impuesto”,
T0.”DiscSum”*-1 AS “Importe Descuento Total”,
T0.”DocCur” AS “Moneda Documento”,
T0.”DocTotal”*-1 AS “Total Documento”,
T0.”GrosProfit”*-1 AS “Beneficio Bruto Documento”,
T1.”GrossBuyPr”*-1 AS “Precio Base unitario”,
T1.”GPTtlBasPr”*-1 AS “Precio Base Total Linea”,
T0.”PaidToDate”*-1 AS “Pagado”,
T1.”LineNum” AS “LineNum”,
T1.”VisOrder” AS “VisOrder”,
T1.”ItemCode” AS “Articulo”,
T1.”Dscription” AS “Descripcion Artículo”,
T7.”ItmsGrpNam” AS “Grupo Articulo”,
T1.”Quantity”*-1 AS “Cantidad”,
T1.”UomCode” AS “Unidad Medida”,
T1.”Price” AS “Precio”,
T1.”PriceBefDi” AS “Precio Unitario”,
T1.”DiscPrcnt” AS “Porcentaje Descuento”,
(T1.”LineTotal” – T1.”Price”)*-1 AS “Importe del descuento”,
T1.”VatSum” AS “Importe Impuesto”,
T1.LineTotal*-1 AS “Total Linea”,
T1.”WhsCode” AS “Cod. Almacen”,
T3.”WhsName” AS “Nombre Almacen”,
T1.”AcctCode” AS “Cuenta Mayor”,
T1.”GrssProfit”*-1 AS “Ganancia Bruta Linea”,
T1.”SlpCode” AS “Cod. Vendedor”,
T6.”SlpName” AS “Nombre Vendedor”
FROM ORIN T0
INNER JOIN RIN1 T1 ON T0.”DocEntry” = T1.”DocEntry”
INNER JOIN OCRD T2 ON T0.”CardCode” = T2.”CardCode”
LEFT JOIN OWHS T3 ON T1.”WhsCode” = T3.”WhsCode”
LEFT JOIN OITM T4 ON T1.”ItemCode” = T4.”ItemCode”
INNER JOIN OCRG T5 ON T2.”GroupCode” = T5.”GroupCode”
INNER JOIN OSLP T6 ON T1.”SlpCode” = T6.”SlpCode”
LEFT JOIN OITB T7 ON T4.”ItmsGrpCod” = T7.”ItmsGrpCod”
INNER JOIN NNM1 T8 ON T0.”Series” = T8.”Series”
WHERE T0.”CANCELED” = ‘N’
)
–Nota: la query utiliza la función de NOMBRE_OBJTYPE –> para saber como crear esta función ingresa a este enlace.
Con esta vista podrás crear tus informes de ventas, KPI’s o cuadros de mandos, totalizando el campo de “Total Línea” como valor principal para obtener las ventas.
La query únicamente consulta las tablas OINV y ORIN (con valores negativos).