Scribd
Upload
10 views7 pages

Invoice and Payment Report Query

The document defines SQL queries and variables to generate reports summarizing sales transaction data between specified date ranges for a selected store. It selects data from various invoice, payment, and store tables, and aggregates fields like subtotals, taxes, payments by type to calculate totals. Report data is grouped and summarized by date, document number, customer and store.

Uploaded by

bryan pineda
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
10 views7 pages

Invoice and Payment Report Query

The document defines SQL queries and variables to generate reports summarizing sales transaction data between specified date ranges for a selected store. It selects data from various invoice, payment, and store tables, and aggregates fields like subtotals, taxes, payments by type to calculate totals. Report data is grouped and summarized by date, document number, customer and store.

Uploaded by

bryan pineda
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SET DATEFORMAT DMY

/*SELECT FROM [dbo].[OINV] T0*/


declare @FInicial as Datetime
/* WHERE */
set @FInicial = /* [Link] */ '[%0]'
--set @FInicial = /* [Link] */ '14/01/2016'

/*SELECT FROM [dbo].[OINV] T1*/


declare @FFinal as Datetime
/* WHERE */
set @FFinal = /* [Link] */ '[%1]'
--set @FFinal = /* [Link] */ '14/01/2016'

declare @Tienda as Nvarchar(8)


SELECT @Tienda = [Link]
FROM [dbo].[@TIENDAS] T2
WHERE [Link] = '[%2]'

SELECT [Link]
, T0.Nombre_Tienda
, [Link]

, [Link]
, [Link]
, [Link]
, [Link]
, CASE WHEN SUM(T0.Total_Factura) = 0 AND [Link] <> 'PCR'
THEN '*** ANULADA ***'
ELSE [Link]
END 'Nombre'
, [Link]
, SUM([Link]) As 'SubTotal'
, SUM([Link]) As 'Iva'
, SUM(T0.Total_Factura) As 'Total_Factura'
, SUM(T0.Saldo_Factura) As 'Saldo_Factura'
, SUM(T0.Efectivo_Q) As 'Efectivo_Q'
, SUM(T0.Efectivo_$) As 'Efectivo_$'
, SUM(T0.Cheque_Q) As 'Cheque_Q'
, SUM(T0.Cheque_$) As 'Cheque_$'
, T0.Tasa_Cambio
, SUM([Link]) As 'Deposito'
, SUM(T0.Tarjeta_Credito) As 'Tarjeta_Credito'
, SUM(T0.Total_Pago) As 'Total_Pago'
, [Link] as 'Vendedor',
CASE WHEN [Link] = 'C'
THEN 'CERRADO'
WHEN [Link] = 'O'
THEN 'ABIERTO'
END As 'Estado'

FROM
(
/* FACTURACION Y PAGOS DEL DIA */
SELECT [Link] As 'Tienda'
, [Link] As 'Nombre_Tienda'
, 1 As 'Reporte'
, [Link] As 'Fecha'
, [Link] As 'Factura'
, [Link] As 'Tipo'
, [Link] As 'Cliente'
, [Link] As 'Nombre'
, T1.U_Nit As 'NIT'
, SUM([Link]) As 'SubTotal'
, SUM([Link]) As 'IVA'
, SUM(T0.Total_Factura) As 'Total_Factura'
, SUM(T0.Saldo_Factura) As 'Saldo_Factura'
, SUM(T0.CashSum_QTZ) As 'Efectivo_Q'
, SUM(T0.CashSum_USD) As 'Efectivo_$'
, SUM(T0.CheckSum_QTZ) As 'Cheque_Q'
, SUM(T0.CheckSum_USD) As 'Cheque_$'
, ISNULL(T0.Tasa_Cambio,0) As 'Tasa_Cambio'
, SUM([Link]) As 'Deposito'
, SUM([Link]) As 'Tarjeta_Credito'
, SUM([Link]) As 'Total_Pago'
, [Link] as 'Vendedor'
, [Link] as 'Estado'
FROM
(
SELECT T0.U_Tienda As 'Tienda'
, [Link] As 'Fecha'
, 'FAC' As 'Tipo'
, [Link] As 'Cliente'
, [Link] As 'Factura'
, SUM([Link])-SUM([Link]) As 'SubTotal'
, SUM([Link]) As 'IVA'
, SUM([Link]) As 'Total_Factura'
, SUM([Link]) As 'Saldo_Factura'
/* EFECTIVO Q*/
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
WHERE [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y'
AND [Link] = 'QTZ') As 'CashSum_QTZ'
/* EFECTIVO $*/
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
WHERE [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y'
AND [Link] = 'USD') As 'CashSum_USD'
/* CHEQUE QTZ */
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
INNER JOIN [dbo].[RCT1] T3 ON [Link] = [Link]
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y'
AND [Link] = 'QTZ') As 'CheckSum_QTZ'
/* CHEQUE USD */
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
INNER JOIN [dbo].[RCT1] T3 ON [Link] = [Link]
WHERE [Link] = [Link] AND [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y'
AND [Link] = 'USD') As 'CheckSum_USD'
/* TASA DE CAMBIO */
, (SELECT TOP 1 CASE WHEN [Link] = 'USD'
THEN [Link]
ELSE 1
END
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
WHERE [Link] = [Link]
AND ([Link] <> 0 OR [Link] <> 0)
AND [Link] != 'Y')
As 'Tasa_Cambio'
/* TRANSFERENCIA O DEPOSITO */
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
WHERE [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y') As 'Deposito'
/* TARJETA DE CREDITO */
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
INNER JOIN [dbo].[RCT3] T3 ON [Link] = [Link]
WHERE [Link] = [Link]
AND [Link] <> 0
AND [Link] != 'Y') As 'CreditSum'
, (SELECT ISNULL(SUM([Link]),0)
FROM [dbo].[ORCT] T1 INNER JOIN [dbo].[RCT2] T2 ON [Link] =
[Link]
WHERE [Link] = [Link]
AND [Link] != 'Y'
AND [Link] = [Link]) As 'DocTotal'
, [Link] as 'Vendedor', [Link] as 'Estado'
FROM [dbo].[OINV] T0
inner join oslp t5 on [Link] = [Link]
WHERE ([Link] >= @FInicial OR @FInicial = '') AND ([Link] <=@FFinal
OR @FFinal = '')
AND (T0.U_Tienda = @Tienda OR @Tienda Is Null)
GROUP BY T0.U_Tienda, [Link], [Link], [Link],
[Link],[Link], [Link]

UNION ALL

SELECT T2.U_Tienda As 'Tienda'


, [Link] As 'Fecha'
, CASE WHEN [Link] = (SELECT U_VALPAR FROM [dbo].[@PARAMETROS] WHERE
CODE = '01')
THEN 'FAC'
ELSE 'N/C'
END As 'Tipo'
, [Link] As 'Cliente'
, CASE WHEN [Link] = (SELECT U_VALPAR FROM [dbo].[@PARAMETROS] WHERE
CODE = '01')
THEN [Link]
ELSE [Link]
END As 'Factura'
, -SUM ([Link]) As 'SubTotal'
, -SUM ([Link]) As 'IVA'
, -SUM ([Link]+[Link]) As 'Total_Factura'
, 0 As 'Saldo_Factura'
, 0 As 'CashSum_QTZ'
, 0 As 'CashSum_USD'
, 0 As 'CheckSum_QTZ'
, 0 As 'CheckSum_USD'
, 0 As 'Tasa_Cambio'
, 0 As 'Deposito'
, 0 As 'CreditSum'
, 0 As 'DocTotal'
, [Link] as 'Vendedor'
, [Link] as 'Estado'
FROM [dbo].[OINV] T0 INNER JOIN [dbo].[RIN1] T1 ON [Link] = [Link]
INNER JOIN [dbo].[ORIN] T2 ON [Link] = [Link] inner join OSLP T5 ON
[Link] = [Link]
WHERE ([Link] >= @FInicial OR @FInicial = '') AND ([Link] <=@FFinal
OR @FFinal = '')
AND (T2.U_Tienda = @Tienda OR @Tienda Is Null)
GROUP BY T2.U_Tienda, [Link], [Link], [Link], [Link],
[Link], [Link], [Link]
) T0
INNER JOIN OCRD T1 ON [Link] = [Link]
INNER JOIN [dbo].[@TIENDAS] T2 ON [Link] = [Link]
GROUP BY [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, T1.U_Nit
, T0.Tasa_Cambio
,[Link]
, [Link]

UNION ALL
/* PAGOS DE FACTURAS AL CREDITO */
SELECT T1.U_Tienda As 'Tienda'
, [Link] As 'Nombre_Tienda'
, '1' As 'Reporte'
, [Link] As 'Fecha'
, [Link] As 'Factura'
, 'PCR' As 'Tipo'
, [Link] As 'Cliente'
, [Link] As 'Nombre'
, '' As 'NIT'
, 0 As 'SubTotal'
, 0 As 'IVA'
, 0 As 'Total_Factura'
, SUM([Link]) As 'Saldo_Factura'
/* EFECTIVO Q */
, CASE WHEN [Link] = 'QTZ'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Efectivo_Q'
/* EFECTIVO $ */
, CASE WHEN [Link] = 'USD'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Efectivo_$'
/* CHEQUE Q */
, CASE WHEN [Link] = 'QTZ'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Cheque_Q'
/* CHEQUE $ */
, CASE WHEN [Link] = 'USD'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Cheque_$'
, CASE WHEN [Link] = 'USD'
THEN [Link]
ELSE 0
END As 'Tasa_Cambio'
, ISNULL(SUM([Link]),0) As 'Deposito'
, ISNULL (SUM([Link]),0) As 'Tarjeta_Credito'
, SUM([Link]) As 'Total_Pago'
, ' ' as 'Vendedor'
, [Link] as 'Estado'
FROM [dbo].[ORCT] T1
INNER JOIN [dbo].[OCRD] T4 ON [Link] = [Link]
INNER JOIN [dbo].[@TIENDAS] T5 ON T1.U_Tienda = [Link]
WHERE ([Link] >= @FInicial OR @FInicial = '') AND ([Link] <=@FFinal OR
@FFinal = '')
AND (T1.U_Tienda = @Tienda OR @Tienda Is Null)
AND [Link] != 'Y'
-- --------------------------------------------------------------------------
-- AUTOR: AR
-- FECHA: 29/MAY/2008
-- DESCRIPCION: RESOLUCION CASO DE DUPLICIDAD EN PAGOS DE FACTURAS DE CREDITO
-- --------------------------------------------------------------------------
AND [Link] IN (SELECT [Link]
FROM RCT2 T10, OINV T11
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] < @FInicial)
GROUP BY T1.U_Tienda
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link],
[Link]

) T0
GROUP BY [Link]
, T0.Nombre_Tienda
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, T0.Tasa_Cambio
,[Link]
, [Link]
UNION ALL
/* PAGOS A CUENTA O ANTICIPOS */
SELECT T1.U_Tienda As 'Tienda'
, [Link] As 'Nombre_Tienda'
, '1' As 'Reporte'
, [Link] As 'Fecha'
, [Link] As 'Factura'
, 'PAG' As 'Tipo'
, [Link] As 'Emisor'
, [Link] As 'Nombre'
, '' As 'NIT'
, 0 As 'SubTotal'
, 0 As 'IVA'
, 0 As 'Total_Factura'
, 0 As 'Saldo_Factura'
/* EFECTIVO Q */
, CASE WHEN [Link] = 'QTZ'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Efectivo_Q'
/* EFECTIVO $ */
, CASE WHEN [Link] = 'USD'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Efectivo_$'
/* CHEQUE Q */
, CASE WHEN [Link] = 'QTZ'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Cheque_Q'
/* CHEQUE $ */
, CASE WHEN [Link] = 'USD'
THEN ISNULL(SUM([Link]),0)
ELSE 0
END As 'Cheque_$'
, CASE WHEN [Link] = 'USD'
THEN [Link]
ELSE 0
END As 'Tasa_Cambio'
, ISNULL(SUM([Link]),0) As 'Deposito'
, ISNULL (SUM([Link]),0) As 'Tarjeta_Credito'
, SUM([Link]) As 'Total_Pago'
, ' ' as 'Vendedor'
, [Link] as 'Estado'
FROM [dbo].[ORCT] T1
INNER JOIN [dbo].[OCRD] T4 ON [Link] = [Link]
INNER JOIN [dbo].[@TIENDAS] T5 ON T1.U_Tienda = [Link]
WHERE ([Link] >= @FInicial OR @FInicial = '') AND ([Link] <=@FFinal OR
@FFinal = '')
AND (T1.U_Tienda = @Tienda OR @Tienda Is Null)
AND [Link] != 'Y'
AND [Link] = 'Y'
GROUP BY T1.U_Tienda
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
UNION ALL
/* PAGOS CON TARJETA DE CREDITO */
SELECT T0.U_Tienda As 'Tienda'
, [Link] As 'Nombre_Tienda'
, '2' As 'Reporte'
, [Link] As 'Fecha'
, [Link] As 'Factura'
, 'TCR' As 'Tipo'
, Convert(Varchar(8), [Link]) As 'Emisor'
, [Link] As 'Nombre'
, '' As 'NIT'
, 0 As 'SubTotal'
, 0 As 'IVA'
, 0 As 'Total_Factura'
, 0 AS 'Saldo_Factura'
, 0 As 'Efectivo_Q'
, 0 As 'Efectivo_$'
, 0 As 'Cheque_Q'
, 0 As 'Cheque_$'
, 0 As 'Tasa_Cambio'
, 0 As 'Deposito'
, SUM([Link]) As 'Tarjeta_Credito'
, SUM([Link]) As 'Total_Pago'
, ' ' as 'Vendedor'
, '' as 'Estado'
FROM [dbo].[ORCT] T0 INNER JOIN [dbo].[RCT3] T1 ON [Link] = [Link]
INNER JOIN [dbo].[OCRC] T2 ON [Link] = [Link]
INNER JOIN [dbo].[@TIENDAS] T3 ON T0.U_Tienda = [Link]
WHERE ([Link] >= @FInicial OR @FInicial = '') AND ([Link] <=@FFinal OR
@FFinal = '')
AND (T0.U_Tienda = @Tienda OR @Tienda Is Null)
AND [Link] != 'Y'
GROUP BY T0.U_Tienda
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]
, [Link]

ORDER BY [Link], [Link]

You might also like