Query de Antigüedad de saldo SAP B1

SAP Business One
3

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
3 Me gusta