GO
 
 CREATE OR ALTER VIEW _as_AROverdueTransactions
 AS
 SELECT C.AccountTerms,T.IntervalValue,I.DueDate as InvNumDueDate
	,Calculated_IsOverdue = CASE 
			WHEN Outstanding <= 0 THEN 0
			WHEN (COALESCE(I.DueDate,'1900-01-01') > P.TxDate AND I.DueDate < GETDATE()) THEN 1 /*DUE Date more than inv date and less than today.*/
			WHEN (COALESCE(I.DueDate,'1900-01-01') <= P.TxDate AND DATEADD(day,T.IntervalValue,P.TxDate) < GETDATE()) THEN 1
			ELSE 0 END 
	,CalculatedDueDate = CASE WHEN (COALESCE(I.DueDate,'1900-01-01') > P.TxDate) THEN COALESCE(I.DueDate,'1900-01-01')
			WHEN (COALESCE(I.DueDate,'1900-01-01') <= P.TxDate) THEN DATEADD(day,T.IntervalValue,P.TxDate)
			ELSE P.TxDate END 
	,P.* 
	from PostAR P 
	LEFT JOIN Client C on P.AccountLink = C.DCLink
	--LEFT JOIN _etblTerms T ON C.iAgeingTermID = T.iTermID
	LEFT JOIN _as_ARAP_Terms_Unpivot T ON T.iTermID = C.iAgeingTermID AND C.AccountTerms = T.IntervalNumber 

	LEFT JOIN InvNum I on I.AutoIndex = P.InvNumKey
 WHERE P.Id IN ('OInv','Inv') 
	AND Outstanding > 0 /*handles debit only as well (for AR)*/

CREATE VIEW _as_AROverdueClients
	AS
	SELECT C.DCLink, SUM(t.Outstanding) as OverdueAmount, IsOverdue = CASE WHEN SUM(t.Outstanding) > 1 THEN 1 ELSE 0 END FROM _as_AROverdueTransactions t
		LEFT JOIN Client C ON t.AccountLink = C.DCLink
		GROUP BY C.DCLink