APINVDTL and GLJNLDTL

You can try using GlJrnSrc. Many GlJrnDtl records will contain multiple GlJrnSrc records showing the source of the GlJrnDtl transactions. I have used that file to track down parttran and labordtl records relating to gljrndtl, but I don't know if it contains the same detail relating to invoice records.

--- In vantage@yahoogroups.com, "jeffery_p_sanders" <jps@...> wrote:
>
> We are trying to match up the invoice detail to the journal entry detail. If you match on the InvoiceNum only and there are three lines on the invoice, you will end up with:
>
> Journal Entry 1 $5.00
> Journal Entry 1 $7.00
> Journal Entry 1 &6.50
> Journal Entry 2 $5.00
> Journal Entry 2 $7.00
> Journal Entry 2 $6.50
> Journal Entry 3 $5.00
> Journal Entry 3 $7.00
> Journal Entry 3 $6.50
>
> There are three line items on the invoice so each journal entry returns all three in the BAQ. We need to know which journal entry goes with which invoice line item.
>
> I hope this makes sense. As you can tell I am not in accounting, just trying to help create a report for the accounting group and do not understand the accounting tables in Epicor enough to figure this out.
>
> Seems to me there should be a field somewhere stating "this Journal Entry" goes to the "this invoice line item". But I guess that would be too easy.
>
> We do not use the Tax Module if that helps.
>
> Jeff Sanders
>
> --- In vantage@yahoogroups.com, "jonmurray999" <jon.murray@> wrote:
> >
> > Hi
> >
> > The field apinvdtl.invoicenum matches with gljndtl.apinvoicenum
> >
> > What are you trying to do ?
> >
> > Jon
> >
> > --- In vantage@yahoogroups.com, Brychan Williams <brychanwilliams@> wrote:
> > >
> > > Hi,
> > >
> > > In Vantage 8 there is no direct link, the records in the GLJNDTL are generated
> > > through code with no relationship. If you can work out how the code works then
> > > you could write a report. But there alot of gotchas such as tax etc.
> > >
> > > Regards
> > >
> > >
> > >
> > >
> > > ________________________________
> > > From: jeffery_p_sanders <jps@>
> > > To: vantage@yahoogroups.com
> > > Sent: Fri, 21 January, 2011 22:26:36
> > > Subject: [Vantage] APINVDTL and GLJNLDTL
> > >
> > > ÂÂ
> > > Does anyone know how to link these two tables together? I am missing a table or
> > > two somewhere in the middle.
> > >
> > > Thanks,
> > > Jeff Sanders
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
>
Does anyone know how to link these two tables together? I am missing a table or two somewhere in the middle.

Thanks,
Jeff Sanders
Hi,

In Vantage 8 there is no direct link, the records in the GLJNDTL are generated
through code with no relationship. If you can work out how the code works then
you could write a report. But there alot of gotchas such as tax etc.

Regards




________________________________
From: jeffery_p_sanders <jps@...>
To: vantage@yahoogroups.com
Sent: Fri, 21 January, 2011 22:26:36
Subject: [Vantage] APINVDTL and GLJNLDTL

Â
Does anyone know how to link these two tables together? I am missing a table or
two somewhere in the middle.

Thanks,
Jeff Sanders







[Non-text portions of this message have been removed]
Hi

The field apinvdtl.invoicenum matches with gljndtl.apinvoicenum

What are you trying to do ?

Jon

--- In vantage@yahoogroups.com, Brychan Williams <brychanwilliams@...> wrote:
>
> Hi,
>
> In Vantage 8 there is no direct link, the records in the GLJNDTL are generated
> through code with no relationship. If you can work out how the code works then
> you could write a report. But there alot of gotchas such as tax etc.
>
> Regards
>
>
>
>
> ________________________________
> From: jeffery_p_sanders <jps@...>
> To: vantage@yahoogroups.com
> Sent: Fri, 21 January, 2011 22:26:36
> Subject: [Vantage] APINVDTL and GLJNLDTL
>
> ÂÂ
> Does anyone know how to link these two tables together? I am missing a table or
> two somewhere in the middle.
>
> Thanks,
> Jeff Sanders
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
We are trying to match up the invoice detail to the journal entry detail. If you match on the InvoiceNum only and there are three lines on the invoice, you will end up with:

Journal Entry 1 $5.00
Journal Entry 1 $7.00
Journal Entry 1 &6.50
Journal Entry 2 $5.00
Journal Entry 2 $7.00
Journal Entry 2 $6.50
Journal Entry 3 $5.00
Journal Entry 3 $7.00
Journal Entry 3 $6.50

There are three line items on the invoice so each journal entry returns all three in the BAQ. We need to know which journal entry goes with which invoice line item.

I hope this makes sense. As you can tell I am not in accounting, just trying to help create a report for the accounting group and do not understand the accounting tables in Epicor enough to figure this out.

Seems to me there should be a field somewhere stating "this Journal Entry" goes to the "this invoice line item". But I guess that would be too easy.

We do not use the Tax Module if that helps.

Jeff Sanders

--- In vantage@yahoogroups.com, "jonmurray999" <jon.murray@...> wrote:
>
> Hi
>
> The field apinvdtl.invoicenum matches with gljndtl.apinvoicenum
>
> What are you trying to do ?
>
> Jon
>
> --- In vantage@yahoogroups.com, Brychan Williams <brychanwilliams@> wrote:
> >
> > Hi,
> >
> > In Vantage 8 there is no direct link, the records in the GLJNDTL are generated
> > through code with no relationship. If you can work out how the code works then
> > you could write a report. But there alot of gotchas such as tax etc.
> >
> > Regards
> >
> >
> >
> >
> > ________________________________
> > From: jeffery_p_sanders <jps@>
> > To: vantage@yahoogroups.com
> > Sent: Fri, 21 January, 2011 22:26:36
> > Subject: [Vantage] APINVDTL and GLJNLDTL
> >
> > ÂÂ
> > Does anyone know how to link these two tables together? I am missing a table or
> > two somewhere in the middle.
> >
> > Thanks,
> > Jeff Sanders
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
Hi

What version are you on ?

I found this in a historic post ;-

In 9.04 the fields relating to sales chart, div etc (from
version 8.xx) were removed. Now they use a file called TranGLC which contains a
cross reference (by book since there's now multiple books) on how ALL
transactions are posted to the GL. You need to be careful to get only the
records you want - example = the Sales posts with an SJ journal code, but the
Cost of Goods sold also posts for the InvcDtl file - so if you only go after the
data by the 'relatedtofile' - you might get more than you bargained for. We're
still trying to pull that file apart since Epicor has no documentation on it. I
have gotten some info from the group and a few Epicor Support people.


We are on 8.so it looks like I'll have to wait for the upgrade to get my hands on this.

If you aren't on 9.04 another (nasty) way of getting part way there is to construct a new invoice number joining the invoice number & value. Obviously duplicate values on different invoice lines would still cause a problem. I got this to work using { fn CONCAT(invoicenum, STR(docextcost)) } on the Ap table & { fn CONCAT(apinvoicenum, STR(transamt)) } on the GL table to create a unique (ish) matching field. (ugh).

Sorry - I'll keep looking for a full answer


Jon Murray
Group Finance Manager

[cid:image001.png@...7BEAD0]

7 Brandl St, Eight Mile Plains, QLD 4113
M: +61 404 788 757
P: +61 7 3340 8461
F: +61 7 3219 9649
www.silanna.com<http://www.silanna.com/>



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of jeffery_p_sanders
Sent: Tuesday, 25 January 2011 1:44 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: APINVDTL and GLJNLDTL



We are trying to match up the invoice detail to the journal entry detail. If you match on the InvoiceNum only and there are three lines on the invoice, you will end up with:

Journal Entry 1 $5.00
Journal Entry 1 $7.00
Journal Entry 1 &6.50
Journal Entry 2 $5.00
Journal Entry 2 $7.00
Journal Entry 2 $6.50
Journal Entry 3 $5.00
Journal Entry 3 $7.00
Journal Entry 3 $6.50

There are three line items on the invoice so each journal entry returns all three in the BAQ. We need to know which journal entry goes with which invoice line item.

I hope this makes sense. As you can tell I am not in accounting, just trying to help create a report for the accounting group and do not understand the accounting tables in Epicor enough to figure this out.

Seems to me there should be a field somewhere stating "this Journal Entry" goes to the "this invoice line item". But I guess that would be too easy.

We do not use the Tax Module if that helps.

Jeff Sanders

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "jonmurray999" <jon.murray@...> wrote:
>
> Hi
>
> The field apinvdtl.invoicenum matches with gljndtl.apinvoicenum
>
> What are you trying to do ?
>
> Jon
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Brychan Williams <brychanwilliams@> wrote:
> >
> > Hi,
> >
> > In Vantage 8 there is no direct link, the records in the GLJNDTL are generated
> > through code with no relationship. If you can work out how the code works then
> > you could write a report. But there alot of gotchas such as tax etc.
> >
> > Regards
> >
> >
> >
> >
> > ________________________________
> > From: jeffery_p_sanders <jps@>
> > To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> > Sent: Fri, 21 January, 2011 22:26:36
> > Subject: [Vantage] APINVDTL and GLJNLDTL
> >
> > Â
> > Does anyone know how to link these two tables together? I am missing a table or
> > two somewhere in the middle.
> >
> > Thanks,
> > Jeff Sanders
> >
> >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>



[Non-text portions of this message have been removed]