Query de Antigüedad de saldo SAP B1

[wilsonrivas]

Buenas tardes estimado he creado un query donde me refleje la antiguedad de saldos, pero me he topado con el inconveniente de no poder restar las Notas de Credito, Anticipos de Clientes y Asientos contables realizados a algunos clientes el query que tengo es el siguiente:

select 
'0.No Vencido' as Grupo,
T0.DocDate,
t0.DocDueDate,
DATEDIFF(day,t0.docduedate, getdate()) as Dias,
T0.CardCode,
T0.CardName,
T0.DocTotal,
T0.PaidToDate,
T0.DocTotal-T0.PaidToDate AS Saldo
from OINV T0 
WHERE --T0.DocDate > '2017-01-01'
--AND 
T0.PaidToDate != T0.DocTotal 
AND T0.DocDueDate > GETDATE()

union all

select 
'0-30' as Grupo,
T0.DocDate,
t0.DocDueDate,
DATEDIFF(day,t0.docduedate, getdate()) as Dias,
T0.CardCode,
T0.CardName,
T0.DocTotal,
T0.PaidToDate,
T0.DocTotal-T0.PaidToDate AS Saldo,
T1.Balance
from OINV T0
LEFT OUTER JOIN OCRD T1 ON T1.CardCode = T0.CardCode
Where/* T0.DocDate > '2017-01-01' and T0.CardCode='C00080' AND*/
T0.PaidToDate != T0.DocTotal AND DATEDIFF(day,T0.DocDueDate, getdate()) >= 0 and DATEDIFF(day,T0.DocDueDate, getdate()) <= 30 and T1.CardType ='C'

[OscarGerardo]

En las tablas OJDT y JDT1 puedes obtener toda esa informacion, creo que yo en la antigua empresa que laboraba hice un query similar y publique un post similar.

[Willy_Caldero]

La verdad sacar el query del agging documento por documento es muy largo, es mejor sacar de la parte contable, igual desde allí podrías filtrar por tipo de documento de los asientos, prueba con este query:

--
-- SAP B1 Customer Receivables Aging - By Due Date
-- 
--
--
-- Version 10022008
-- 



select OCRD.cardcode 'Customer Code',OCRD.cardname 'Customer Name',

SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) "Balance Due",


ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) < 0
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "Future Remit",

ISNULL(SUM(CASE when (DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 0
and datediff(dd,JDT1.DueDate,current_timestamp)< 30)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "0-30 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 30
and datediff(dd,JDT1.DueDate,current_timestamp)< 60)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "31 to 60 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 60
and datediff(dd,JDT1.DueDate,current_timestamp)< 90)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "61 to 90 days",

ISNULL(SUM(CASE when (datediff(dd,JDT1.DueDate,current_timestamp) >= 90
and datediff(dd,JDT1.DueDate,current_timestamp) < 120)
then 
	case
		when JDT1.BalDueCred <> 0 then JDT1.BalDueCred * - 1
		else JDT1.BalDueDeb
	end
end),0.00) "91 to 120 days",

ISNULL(SUM(CASE when DATEDIFF(dd,JDT1.DueDate,current_timestamp) >= 120
then 
	case
		when BalDueCred <> 0 then BalDueCred * - 1
		else BalDueDeb
	end
end),0.00) "120+ days"

from JDT1,OCRD 
WHERE JDT1.shortname = OCRD.cardcode and OCRD.cardtype = 'c' 
GROUP BY OCRD.cardcode, OCRD.cardname
--Filters Out Zero Balances
HAVING SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) > 0 OR SUM(case when syscred <> 0 then syscred * - 1
else sysdeb
end) < 0

ORDER BY OCRD.CARDCODE

[Un_Tal_Erik_J]

Bueno el aporte lo ejecute y efectivamente mi antiguedad de saldos en moneda de sistema