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)*/ |