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 BROWSEUna herramienta rápida para ver el flujo de las ventas, la facturación y el ingreso de dinero.
