Ledger reports

Yahoo Archive
1
Thank you so much for your help. I'll have to have someone here help me
with this..

I was wondering if this would even be possible.



Thanks again,

Chris





-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of rybermail
Sent: Tuesday, June 20, 2006 9:21 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Ledger reports



I had a manager request exactly such a report. The issue we had with
it is that there is a missing index on the APInvDtl.InvoiceLine field
which causes any queries to run VERY unacceptably slowly. When
requesting / suggesting adding an index with tech support, they
redirected me to custom programming. That wasnt a realistic option ,
so I 'fixed' this by running a nightly export into sql server, where
I am able to index it. The speed went from 15 minutes down to about
30 seconds for one query. The key tables you will need are:
GLJrnDtl, GLJrnSrc, and APInvDtl. (Note the odd join between
APInvDtl and GLJrnSrc based on GLJrnSrc.APInvoiceNum =
APInvDtl.InvoiceNum) Here is the basic query written in MSAccess:
"SELECT PUB_GLJrnDtl.GLDiv, PUB_GLJrnDtl.GLChart,
PUB_GLJrnDtl.FiscalYear, PUB_GLJrnDtl.FiscalPeriod,
PUB_APInvDtl.PONum, PUB_APInvDtl.POLine, PUB_APInvDtl.PartNum,
PUB_APInvDtl.InvoiceNum, PUB_APInvDtl.ExtCost, PUB_GLJrnDtl.PostedDate
FROM (PUB_GLJrnDtl INNER JOIN PUB_GLJrnSrc ON
(PUB_GLJrnDtl.FiscalYear = PUB_GLJrnSrc.FiscalYear) AND
(PUB_GLJrnDtl.JournalLine = PUB_GLJrnSrc.JournalLine) AND
(PUB_GLJrnDtl.JournalNum = PUB_GLJrnSrc.JournalNum) AND
(PUB_GLJrnDtl.Company = PUB_GLJrnSrc.Company)) INNER JOIN
PUB_APInvDtl ON (PUB_GLJrnSrc.Company = PUB_APInvDtl.Company) AND
(PUB_GLJrnSrc.APInvoiceNum = PUB_APInvDtl.InvoiceNum) AND
(PUB_GLJrnSrc.InvoiceLine = PUB_APInvDtl.InvoiceLine);"

> Does anyone have reports that they would be willing to share for the
> following examples?
>
>
>
> When you look at the general ledger, say for tooling expense, you
can
> see the tooling vendor's names posted to that account from A/P, but
then
> you see the "periodic posting process" lines listed for the items
that
> were from PO's. Does anyone have a report that would combine the
best
> of both worlds so that you can see the vendor info from A/P and
from the
> PO's on one report that still ties out to the ledger?
>
>
>
> The other report that I can't come up with is something where I
could
> choose a vendor, and see how much was purchased from them in a given
> month, that would also tie back to the ledger. So that if tooling
> expense was $50,000 for the month, I would know that we purchased
> $30,000 of it from a particular vendor?
>
>
>
> Thanks in advance, I'd appreciate any help..
>
> Chris Hansen
>
> Controller
>
> Herker Industries, Inc.
>
> (we are on Vantage 6.1)
>
>
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
2
Does anyone have reports that they would be willing to share for the
following examples?



When you look at the general ledger, say for tooling expense, you can
see the tooling vendor's names posted to that account from A/P, but then
you see the "periodic posting process" lines listed for the items that
were from PO's. Does anyone have a report that would combine the best
of both worlds so that you can see the vendor info from A/P and from the
PO's on one report that still ties out to the ledger?



The other report that I can't come up with is something where I could
choose a vendor, and see how much was purchased from them in a given
month, that would also tie back to the ledger. So that if tooling
expense was $50,000 for the month, I would know that we purchased
$30,000 of it from a particular vendor?



Thanks in advance, I'd appreciate any help..

Chris Hansen

Controller

Herker Industries, Inc.

(we are on Vantage 6.1)





[Non-text portions of this message have been removed]
3
I had a manager request exactly such a report. The issue we had with
it is that there is a missing index on the APInvDtl.InvoiceLine field
which causes any queries to run VERY unacceptably slowly. When
requesting / suggesting adding an index with tech support, they
redirected me to custom programming. That wasnt a realistic option ,
so I 'fixed' this by running a nightly export into sql server, where
I am able to index it. The speed went from 15 minutes down to about
30 seconds for one query. The key tables you will need are:
GLJrnDtl, GLJrnSrc, and APInvDtl. (Note the odd join between
APInvDtl and GLJrnSrc based on GLJrnSrc.APInvoiceNum =
APInvDtl.InvoiceNum) Here is the basic query written in MSAccess:
"SELECT PUB_GLJrnDtl.GLDiv, PUB_GLJrnDtl.GLChart,
PUB_GLJrnDtl.FiscalYear, PUB_GLJrnDtl.FiscalPeriod,
PUB_APInvDtl.PONum, PUB_APInvDtl.POLine, PUB_APInvDtl.PartNum,
PUB_APInvDtl.InvoiceNum, PUB_APInvDtl.ExtCost, PUB_GLJrnDtl.PostedDate
FROM (PUB_GLJrnDtl INNER JOIN PUB_GLJrnSrc ON
(PUB_GLJrnDtl.FiscalYear = PUB_GLJrnSrc.FiscalYear) AND
(PUB_GLJrnDtl.JournalLine = PUB_GLJrnSrc.JournalLine) AND
(PUB_GLJrnDtl.JournalNum = PUB_GLJrnSrc.JournalNum) AND
(PUB_GLJrnDtl.Company = PUB_GLJrnSrc.Company)) INNER JOIN
PUB_APInvDtl ON (PUB_GLJrnSrc.Company = PUB_APInvDtl.Company) AND
(PUB_GLJrnSrc.APInvoiceNum = PUB_APInvDtl.InvoiceNum) AND
(PUB_GLJrnSrc.InvoiceLine = PUB_APInvDtl.InvoiceLine);"

> Does anyone have reports that they would be willing to share for the
> following examples?
>
>
>
> When you look at the general ledger, say for tooling expense, you
can
> see the tooling vendor's names posted to that account from A/P, but
then
> you see the "periodic posting process" lines listed for the items
that
> were from PO's. Does anyone have a report that would combine the
best
> of both worlds so that you can see the vendor info from A/P and
from the
> PO's on one report that still ties out to the ledger?
>
>
>
> The other report that I can't come up with is something where I
could
> choose a vendor, and see how much was purchased from them in a given
> month, that would also tie back to the ledger. So that if tooling
> expense was $50,000 for the month, I would know that we purchased
> $30,000 of it from a particular vendor?
>
>
>
> Thanks in advance, I'd appreciate any help..
>
> Chris Hansen
>
> Controller
>
> Herker Industries, Inc.
>
> (we are on Vantage 6.1)
>
>
>
>
>
> [Non-text portions of this message have been removed]
>