cancel
Showing results for 
Search instead for 
Did you mean: 

Antiguedad de Clientes detallado

Former Member
0 Kudos

Hola buenas tardes tengo algunos problemas con una consulta del reporte de antiguedad de saldos ya que no me cuadra con lo de b1, esta consulta es detallada con sus facturas y la moneda que se refiere la moneda.

haber si me pueden ayudar a reparar este informe

SELECT T0.CardCode AS 'Cod S/N', T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE

,'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end)

, T0.DocNum AS 'N/Doc'

,DOCDATE as 'Fech/Crea',GETDATE() as FechaInforme,

T0.NUMATCARD as Referencia, T0.DOCDUEDATE as 'Fech/Venc.', DATEDIFF(day, docduedate, getdate())

as Días,'Saldo Pendiente'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end)

, DocCur as Moneda

,'Corriente' = (case when (Doccur='MXP' and getdate() <= DocDueDate) then (DocTotal-PaidToDate)

else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end)

,' 0-30' = (case when (Doccur='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF

(day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate) else

(case when DATEDIFF(day, docduedate, getdate())>=0

and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'

then (DocTotalFC-PaidFC) else 0 end) end)

,' 31-60' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and

DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate) else

(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate

, getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)else 0 end) end)

,' 61-90' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and

DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate) else

(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90

and DocCur='USD' then (DocTotalFC-PaidFC) else 0 end) end),

'90+' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.DocTotal-T0.PaidToDate) else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.DocTotalFC-T0.PaidFC) else 0 end) end), 'Factura' as 'Docto' FROM OINV T0

INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

WHERE (T0.DocTotal-T0.PaidToDate <>0 or DocTotalFC-PaidFC <> 0)

union all

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE,

'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end), T0.DocNum AS NoDocto,DOCDATE as FechaDocto,GETDATE() as FechaInforme,

T0.NUMATCARD as Referencia, T0.DOCDUEDATE as Vencimiento, DATEDIFF(day, docduedate, getdate())

as DíasVencimiento,'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC)-1 else (DocTotal-PaidToDate)-1 end)

, DocCur as Moneda

,'Por Vencer' = (case when (Doccur='MXP' and getdate() <= DocDueDate) then (DocTotal-PaidToDate)*-1

else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end)

,' 0-30' = (case when (Doccur='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF

(day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate)*-1 else

(case when DATEDIFF(day, docduedate, getdate())>=0

and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD'

then (DocTotalFC-PaidFC)*-1 else 0 end) end)

,' 31-60' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and

DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate)*-1 else

(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate

, getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)*-1 else 0 end) end)

,' 61-90' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and

DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate)*-1 else

(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90

and DocCur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end),

'90+' = (case when (DocCur='MXP' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.DocTotal-T0.PaidToDate)*-1 else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.DocTotalFC-T0.PaidFC)*-1 else 0 end) end) , 'NC' as 'Docto' FROM ORIN T0

INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

WHERE (T0.DocTotal-T0.PaidToDate <>0 or DocTotalFC-PaidFC <> 0)

union all

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE

,'TotalDoc'= (case when DocCurr='USD' then (T0.DocTotalFC) else (DocTotal) end)

, T0.DocNum AS NoDocto,DOCDATE as FechaDocto,GETDATE() as FechaInforme,

T0.CounterRef as Referencia, T0.DOCDUEDATE as Vencimiento, DATEDIFF(day, docduedate, getdate())

as DíasVencimiento,'Saldo'= (case when DocCurr='USD' then (T0.NoDocSumFC * -1) else (T0.NoDocSum * -1) end)

, DocCurr as Moneda

,'Por Vencer' = (case when (DocCurr='MXP' and getdate() <= DocDueDate) then (T0.NoDocSum)

else (case when getdate() <= DocDueDate and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end)

,' 0-30' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF

(day, docduedate, getdate())<=30) then (T0.NoDocSum * -1) else

(case when DATEDIFF(day, docduedate, getdate())>=0

and DATEDIFF(day, docduedate, getdate())<=30 and DocCurr='USD'

then (T0.NoDocSumFC * -1) else 0 end) end)

,' 31-60' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=31 and

DATEDIFF(day, docduedate, getdate())<=60) then (T0.NoDocSum * -1) else

(case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate

, getdate())<=60 and DocCurr='USD' then (T0.NoDocSumFC * -1)else 0 end) end)

,' 61-90' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=61 and

DATEDIFF(day, docduedate, getdate())<=90) then (T0.NoDocSum * -1) else

(case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90

and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end),

'90+' = (case when (DocCurr='MXP' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.NoDocSum * -1) else (case when (DocCurr='USD' and DATEDIFF(day, docduedate, getdate())>=90)

then (T0.NoDocSumFC * -1) else 0 end) end), 'Cobranza' as 'Docto' FROM ORCT T0

INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE

INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM

WHERE (T0.NoDocSum <> 0 or T0.NoDocSumFC <> 0) AND T0.[DocType] = 'C' AND T0.OpenBal <> 0 AND T0.[JrnlMemo] not like '%%Cancelado%%' ORDER BY T0.CardCode, T0.DocDate

Accepted Solutions (0)

Answers (4)

Answers (4)

former_member210784
Active Contributor
0 Kudos

Hola.

Pruebe el código y nos cuenta cómo le fué.

Quedo en espera de sus comentarios.

Saludos.

Former Member
0 Kudos

Excelente , voy a probarlo, lo unico que no veo es el SLP , pero ahí lo agrego.

Slds

Diego Castillo

SBO Consultant

Former Member
0 Kudos

tomando el query anterior que me sirvio muchisimo!! como puedo verificar la factura con mas dias de vencimiento por cliente, para poder restringir a la hora de hacer una factura que si el cliente tiene facturas con mas de 120 dias vencidos, no deje facturar

former_member210784
Active Contributor
0 Kudos

Hola Jcarito, cómo vas?

Tu pregunta debes colocarla en tu propio post.

Puedes usar un query que te valide esto en la factura, de modo que se intercepte con procedimiento de autorización, o un Transaction notification (TN) que impidda que se cree el documento.

Saludos cordiales.

Former Member
0 Kudos

ya abri el post

gracias

former_member210784
Active Contributor
0 Kudos

union all 

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE ,
	'TotalDoc'= (case when DocCurr='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS NoDocto,
	DOCDATE as FechaDocto,GETDATE() as FechaInforme, T0.CounterRef as Referencia, T0.DOCDUEDATE as Vencimiento, 
	DATEDIFF(day, docduedate, getdate()) as DíasVencimiento,
	'Saldo'= (case when DocCurr='USD' then (T0.NoDocSumFC * -1) else (T0.NoDocSum * -1) end) , DocCurr as Moneda ,
	'Por Vencer' = (case when (DocCurr=@LOCCUR and getdate() <= DocDueDate) then (T0.NoDocSum) else (case when getdate() <= DocDueDate and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,
	' 0-30' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end) ,
	' 31-60' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCurr='USD' then (T0.NoDocSumFC * -1)else 0 end) end) ,
	' 61-90' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.NoDocSum * -1) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCurr='USD' then (T0.NoDocSumFC * -1) else 0 end) end), 
	'90+' = (case when (DocCurr=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSum * -1) else (case when (DocCurr='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.NoDocSumFC * -1) else 0 end) end), 
	'Cobranza' as 'Docto' 
FROM ORCT T0 
	INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE 
	INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM 
WHERE (T0.NoDocSum > 0 or T0.NoDocSumFC > 0) AND T0.[DocType] = 'C' AND T0.OpenBal > 0 AND T0.[JrnlMemo] not like '%%Cancelado%%' 

ORDER BY T0.CardCode, T0.DocDate

juandfranco
Explorer
0 Kudos

Estoy con el mismo problema y me da un error en @LOCCUR

former_member210784
Active Contributor
0 Kudos

DECLARE @LOCCUR VARCHAR(3)
SET @LOCCUR = '$'

SELECT T0.CardCode AS 'Cod S/N', T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE ,
	'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end) , T0.DocNum AS 'N/Doc' ,
	DOCDATE as 'Fech/Crea',GETDATE() as FechaInforme, T0.NUMATCARD as Referencia, T0.DOCDUEDATE as 'Fech/Venc.', 
	DATEDIFF(day, docduedate, getdate()) as Días,
	'Saldo Pendiente'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC) else (DocTotal-PaidToDate) end) , 
	DocCur as Moneda ,'Corriente' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate) else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC) else 0 end) end) ,
	' 0-30' = (case when (T0.Doccur=@LOCCUR and DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 and 30) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, T0.docduedate, getdate())BETWEEN 0 AND 30 and T0.Doccur='USD' then (T0.DocTotalFC-T0.PaidFC) else 0 end) end) ,
	' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)else 0 end) end) ,
	' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate) else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC) else 0 end) end), 
	'90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate) else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC) else 0 end) end), 
	'Factura' as 'Docto' 
FROM OINV T0 
	INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE 
	INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM 
WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0) 

former_member210784
Active Contributor
0 Kudos

Hola.

Intenta con este query. Lo voy a colocar en tres segmentos separados para que se pueda visualizar bien.

He identado las secciones y cambié las condiciones del where. Ya debe coincidir con el Informe de Antiguedad de saldos del sistema. Hay que colocar el símbolo de moneda correcto en la variable @LOCCUR.

Saludos.

former_member210784
Active Contributor
0 Kudos

union all 

SELECT T0.CardCode AS Cliente, T0.CardName AS Nombre, T1.[CardFName], T2.PYMNTGROUP, T1.CREDITLINE, 
	'TotalDoc'= (case when DocCur='USD' then (T0.DocTotalFC) else (DocTotal) end), T0.DocNum AS NoDocto,
	DOCDATE as FechaDocto,GETDATE() as FechaInforme, T0.NUMATCARD as Referencia, T0.DOCDUEDATE as Vencimiento, 
	DATEDIFF(day, docduedate, getdate()) as DíasVencimiento,
	'Saldo'= (case when DocCur='USD' then (T0.DocTotalFC-T0.PaidFC)*-1 else (DocTotal-PaidToDate)*-1 end) , 
	DocCur as Moneda ,'Por Vencer' = (case when (Doccur=@LOCCUR and getdate() <= DocDueDate) then (DocTotal-PaidToDate)*-1 else (case when getdate() <= DocDueDate and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,
	' 0-30' = (case when (Doccur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF (day, docduedate, getdate())<=30) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=0 and DATEDIFF(day, docduedate, getdate())<=30 and Doccur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end) ,
	' 31-60' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate, getdate())<=60) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=31 and DATEDIFF(day, docduedate , getdate())<=60 and DocCur='USD' then (DocTotalFC-PaidFc)*-1 else 0 end) end) ,
	' 61-90' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when DATEDIFF(day, docduedate, getdate())>=61 and DATEDIFF(day, docduedate, getdate())<=90 and DocCur='USD' then (DocTotalFC-PaidFC)*-1 else 0 end) end), 
	'90+' = (case when (DocCur=@LOCCUR and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotal-T0.PaidToDate)*-1 else (case when (DocCur='USD' and DATEDIFF(day, docduedate, getdate())>=90) then (T0.DocTotalFC-T0.PaidFC)*-1 else 0 end) end) , 
	'NC' as 'Docto' 
FROM ORIN T0 
	INNER JOIN OCRD T1 ON T0.CARDCODE= T1.CARDCODE 
	INNER JOIN OCTG T2 ON T1.GROUPNUM = T2.GROUPNUM 
WHERE (T0.DocTotal-T0.PaidToDate > 0 or DocTotalFC-PaidFC > 0)