Uno de los reportes útiles para controlar la cartera de clientes por cobrar. En este reporte básicamente vemos todos los clientes que nos deben a 30, 60, 90 y 120 días.
-- Declaración de la variable para filtrar los clientes
DECLARE @CardCode nvarchar(30)
SET @CardCode = 'C%'
-- Creación de una tabla temporal para almacenar los resultados
DECLARE @Cartera table
(
CardCode nvarchar(30)
,CardName nvarchar(100)
,DocNum int
,NumDoc nvarchar(254)
,TipoDoc nvarchar(30)
,Fecha datetime
,FechaVencimiento datetime
,Total numeric(19,6)
,Saldo numeric(19,6)
,NoVencido numeric(19,6)
,C_1_30 numeric(19,6)
,C_31_60 numeric(19,6)
,C_61_90 numeric(19,6)
,C_91_120 numeric(19,6)
,C_121_mas numeric(19,6)
)
-- Inserción de facturas pendientes en la tabla temporal
INSERT INTO @Cartera
SELECT
T0.cardCode as CveCliente,T0.CardName,Convert(varchar(20),T0.docnum) docnum,
-- Concatenación de detalles de factura para descripción
'Factura # ' + Convert(varchar(20),T0.docnum) + ', Saldo pendiente: QTZ. ' + Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0)) + ', Días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate())) AS NumDoc,
'Factura' as TipoDoc, max(T0.DocDate) as 'fecha',
max(T0.docduedate) as 'Fecha_vencimiento',
SUM(docTotal)as Total,
-- Cálculo de saldos y clasificación por rango de vencimiento
Saldo = ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) ,
-- Continuación del cálculo de saldos en rangos de tiempo
'NoVencido'= ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0),
'1_30' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0),
'31_60' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0),
'61_90' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0),
'91_120' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0),
'121_mas' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0)
FROM OINV T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O' AND DocSubType <>'DN' AND T0.CardCode LIKE @CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName
UNION ALL
-- Continuación con la inserción de Notas Débito y Notas Crédito
SELECT
T0.cardCode as CveCliente,T0.CardName,Convert(varchar(20),T0.docnum) docnum,
'Nota Débito ' + Convert(varchar(20),T0.docnum) + ' Saldo pendiente: $ ' + Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0)) + ' días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate())) AS NumDoc,
'Nota Débito' as TipoDoc, max(T0.DocDate) as 'fecha del doc',
max(T0.DocDueDate) as 'Fecha de vencimiento',SUM(docTotal) as Total,
Saldo= ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) ,
'NoVencido'= ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0),
'1_30' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0),
'31_60' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0),
'61_90' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0),
'91_120' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0),
'121_mas' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0)
FROM OINV T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O' AND DocSubType ='DN' AND T0.CardCode=@CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName
UNION ALL
SELECT T0.cardCode as CveCliente, T0.CardName,Convert(varchar(20),T0.docnum) docnum,
'Nota Crédito ' + Convert(varchar(20),T0.docnum) + ' Saldo pendiente: $ ' + Convert(varchar(20), ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0)) + ' días vencida: ' + Convert(varchar(10),DATEDIFF(day , max(T0.DocDueDate),getdate())) as NumDoc
,'NC' as TipoDoc
,max(T0.DocDate) as 'fecha del doc',
max(T0.DocDueDate) as 'Fecha de vencimiento',SUM(docTotal)*-1 as Total,
Saldo= ISNULL(sum(T0.doctotal) - sum(T0.paidtodate),0) *-1 ,
'NoVencido' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1<0 ),0) *-1,
'1_30' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 0 and 30 ),0) *-1,
'31_60' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 31 and 60 ),0) *-1,
'61_90' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 61 and 90),0) *-1,
'91_120' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 between 91 and 120),0) *-1,
'121_mas' =ISNULL((select sum(T0.doctotal) - sum(T0.paidtodate) WHERE (DATEDIFF(day ,getdate() , max(T0.DocDueDate)))*-1 > 121),0) *-1
FROM ORIN T0
INNER JOIN OSLP T1 ON T0.SlpCode = T1.SlpCode
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode
WHERE T0.DocStatus='O' AND T0.CardCode=@CardCode
GROUP BY T0.cardname, T0.docnum,T0.CardCode ,T0.CardName
SELECT
CardCode
,CardName
,DocNum
,NumDoc
,TipoDoc
,Fecha
,FechaVencimiento
,Total
,Saldo
,NoVencido
,C_1_30
,C_31_60
,C_61_90
,C_91_120
,C_121_mas
FROM @Cartera
ORDER BY Fecha ASC