Overview

You may need a list of active customer accounts for a particular state with positive balance age (AccountsReceivable.BalAge) and negative due balance (AccountsReceivable.BalDue) with addresses to be able to contact them and inform them of the new due dates and balances.

Solution

Create a support ticket and provide the name of the state for which you'd want this information exported.

<supportagent>

Follow the steps below to export the requested information

1. Login to the Jump server 10.185.40.141.
2. Open Microsoft SSMS and connect to aes.cons.com.
3. Select New Query in the top bar.
4. Execute the following query substituting the abbreviated state name shared by the client in the query.

   select  c.CustNo As AccountNumber
   ,p.PremNo as UtilityNumber
   ,c.FirstName,c.MidName,c.LastName
   ,a.Addr1,a.Addr2,a.City,a.State,a.Zip,a.email
   ,ps.Status as PremiseStatus,c.CustStatus
   ,ar.*
   ,ar.BalDue
   from Customer c
   join Premise p on p.CustID = c.CustID
   join AccountsReceivable ar on ar.AcctsRecID = c.AcctsRecID
   join PremiseStatus ps on ps.PremiseStatusID = p.StatusID
   join Address a on a.AddrID = c.MailAddrID
   where 1=1
   and p.LdcID in (select LDCID from LDC
                     join Market on Market.MarketID = LDC.MarketID
                     where Market.MarketCode='<abbreviated state code of the state>'
   )
   and p.StatusID=10
   and ar.BalDue<0
   and ( IsNull(ar.BalAge0,0) >0
   or IsNull(ar.BalAge1,0) >0
   or IsNull(ar.BalAge2,0) >0
   or IsNull(ar.BalAge3,0) >0
   or IsNull(ar.BalAge4,0) >0
   or IsNull(ar.BalAge5,0) >0
   or IsNull(ar.BalAge6,0) >0
   )
   order by 1 desc

5. Share the exported resultset with the client.

</supportagent>