MSSQL para SAP BO

MSSQL para SAP BO: Reporte de ventas por rango de fecha

Maynor Marino Mijangos
10 meses ago
10 meses ago
1 mins
35,0 Views
3MC SqlQuery TresEmeConsulting

Este reporte lo hice a partir de la factura de deudor (Ventas), se clasifica y se enlaza hacia otros documentos relacionados.

DECLARE @FechaInicial AS DATETIME 
DECLARE @FechaFinal AS DATETIME

SELECT @FechaInicial = T0.F_RefDate, @FechaFinal = T0.T_RefDate From OFPR T0 Where T0.F_RefDate = '[%0]' AND T0.T_RefDate = '[%1]' 
SET @FechaInicial = CONVERT(DATETIME, '[%0]', 112) 
SET @FechaFinal = CONVERT(DATETIME, '[%1]', 112)

SELECT
Distinct(V.DocNum) AS Factura,
V.NumAtCard AS FEL,
V.DocEntry AS DocInt,
V.TaxDate AS 'Fecha de Facturación',
D.BaseRef AS 'Pedido', 
C.CardName as Cliente,

(SELECT
ISNULL(SUM(Quantity*Price),0)
FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
WHERE TaxCode='IVA' AND V.DocEntry=V1.DocEntry AND (V1.ItemCode LIKE 'VB%') )
AS BOLSAS,

(SELECT
ISNULL(SUM(Quantity*Price),0)
FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
WHERE TaxCode='IVA' AND V.DocEntry=V1.DocEntry AND (V1.ItemCode LIKE 'VCA%') )
AS CARTON,

(SELECT
	ISNULL(SUM(Quantity*Price),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE V.DocEntry=V1.DocEntry AND V1.ItemCode LIKE 'VI[0-9]%')
AS IMPRESION,

(SELECT
	ISNULL(SUM(VatSum),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE TaxCode='IVA' AND V.DocEntry=V1.DocEntry )
AS IVA,

/*
(SELECT
	ISNULL(SUM(LineTotal),0)
    FROM INV2 V2
    WHERE V2.ExpnsCode = 1 AND V.DocEntry=V2.DocEntry)
AS TIMBRE,

(SELECT
	ISNULL(SUM(Quantity*Price),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE V.DocEntry=V1.DocEntry AND ( V1.ItemCode LIKE 'VC%' ) )
AS COMISIONAGENCIA,
*/

(V.Doctotal) AS TOTALES,

(SELECT
	ISNULL(SUM(T2.doctotal),0) 
	FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum INNER JOIN OINV T2 ON T1.DocEntry = V.DocEntry
 WHERE T2.docstatus = 'c' and T2.receiptnum=t1.docnum) AS PAGADA,

/*
(SELECT
	ISNULL(SUM(I.PrdStdCst),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE V.DocEntry=V1.DocEntry AND V1.ItemCode LIKE 'VI%'  )
AS Costo,

((SELECT
	ISNULL(SUM(Quantity*Price),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE V.DocEntry=V1.DocEntry AND V1.ItemCode LIKE 'VI[0-9]%')
-
(SELECT
	ISNULL(SUM(I.PrdStdCst),0)
    FROM INV1 V1 INNER JOIN OITM I ON V1.ItemCode=I.ItemCode
    WHERE V.DocEntry=V1.DocEntry AND V1.ItemCode LIKE 'VI[0-9]%' ))
AS 'Afecto (Imp-Costo)',

V.SlpCode, 
*/

T2.SlpName AS Vendedor, 

-- V.OwnerCode,
CONCAT(T3.FirstName, ' ', T3.LastName) AS Titular


FROM OINV V
INNER JOIN OCRD C ON V.CardCode = C.CardCode
INNER JOIN INV1 D ON V.DocEntry = D.DocEntry

INNER JOIN OSLP T2 ON T2.SlpCode = V.SlpCode
INNER JOIN OHEM T3 ON T3.empID = V.OwnerCode

WHERE V.Series != '1' AND V.Canceled = 'N' AND V.DocDate BETWEEN @FechaInicial AND @FechaFinal
ORDER BY 2

FOR BROWSE

Una herramienta rápida para ver el flujo de las ventas, la facturación y el ingreso de dinero.

Tags:
Shares: