Thanks Linda I think I actually got it working now. What a Pain!
Thanks all for your hlep!
*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>
*Quis custodiet ipsos custodes?*
Thanks all for your hlep!
*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>
*Quis custodiet ipsos custodes?*
On Tue, Sep 4, 2012 at 3:04 PM, Linda Lowney <llowney@...> wrote:
> **
>
>
> Hi Jose,
>
> If you are trying to balance to the General Ledger you might also need to
> take a look in there to see if anyone has made a GL Adjustment to the
> account which would not be seen by the AR tables.
>
>
> Regards,
> Linda
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of Jose Gomez
> Sent: September-03-12 1:51 PM
> To: Vantage
> Subject: [Vantage] Re: AR Aging (Kinda) ---HELP
>
> Giving this a bump. I have a bank deadline tomorrow. Any ideas would be
> appreciated
> On Aug 31, 2012 4:51 PM, "Jose Gomez" <jose@...<mailto:
> jose%40josecgomez.com>> wrote:
>
> >
> > Long story short, our AR Aging Report has a bug in it in which if an
> > Adjusment was posted AFTER the period is closed it still affects the
> > report. So now we can't run past AR Aging Reports BC they don't match.
> > Alas I have to write a report that will allow me to get the Total Balance
> > of Accounts as of Date XXXX
> >
> > I have the following SQL which gets me close, however the number is OFF
> by
> > like 100K, what am I missing? I am looking at invoices, Payments and
> > adjustments... what else can someone take a look at this and maybe ilu
> >
> >
> >
> > DECLARE @invoice AS DECIMAL(18,5)
> >
> > DECLARE @payments AS DECIMAL(18,5)
> >
> > --Get All The Open Invoices As Off 5/31/2012
> >
> > SELECT @invoice=SUM(IH.InvoiceAmt)
> >
> > FROM InvcHead IH WITH(NOLOCK)
> >
> > WHERE IH.InvoiceDate <= N'05/31/2012'
> >
> > AND (IH.ClosedDate > N'04/30/2012' OR IH.ClosedDate IS NULL)
> >
> > AND IH.Company='KC' AND IH.Posted=1
> >
> > --Get All Payments / Adjustments to The Above Invoices thet were made
> > before May 31st
> >
> > SELECT @payments=SUM( CS.DocTranAmt )
> >
> > FROM CashDtl CS WITH(NOLOCK)
> >
> > WHERE CS.TranType NOT IN ('CMemo')
> >
> > AND CS.InvoiceNum IN (
> >
> > SELECT IH.InvoiceNUm
> >
> > FROM InvcHead IH WITH(NOLOCK)
> >
> > WHERE IH.InvoiceDate <= N'05/31/2012' AND (IH.ClosedDate >N'04/30/2012'
> ORIH
> > .ClosedDate IS NULL)
> >
>
> > AND IH.Company='KC' AND IH.Posted=1 )
> >
> > AND CS.TranDate <=N'5/31/2012' AND CS.Posted=1
> >
> >
> >
> >
> >
> >
> >
> > --Add Them Together
> >
> > SELECT (@invoice-@payments)
> >
> >
> >
> >
> > *Jose C Gomez*
> > *Software Engineer*
> > *
> > *
> > *
> > *T: 904.469.1524 mobile
> > E: jose@...<mailto:jose%40josecgomez.com>
> > http://www.josecgomez.com
> > <http://www.linkedin.com/in/josecgomez> <
> http://www.facebook.com/josegomez>
> > <http://www.google.com/profiles/jose.gomez> <
> http://www.twitter.com/joc85>
> > <http://www.josecgomez.com/professional-resume/> <
> http://www.josecgomez.com/feed/>
> > <http://www.usdoingstuff.com>
> >
> > *Quis custodiet ipsos custodes?*
> >
> >
>
> [Non-text portions of this message have been removed]
>
> [Non-text portions of this message have been removed]
>
>
>
[Non-text portions of this message have been removed]