Quick Shared Var question

[system]

A few things I forgot to mention are;

To display a simple report total I create a another formula
DisplayReportTotal which is basically:

Whileprintingrecords;

Numbervar TotalReport;

TotalReport;



If you are grouping, I create a InitGroupVars formula to reset the
totals in the group header (Hidden field) like this:

Whileprintingrecords;

Numbervar TotalGroup := 0;



Hope this helps,



BB









From: Butler, Bruce
Sent: Tuesday, October 26, 2010 7:30 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question



You can create a formula to maintain a sharedvar total. There is a
trick to it. Below is an example of a formula I use. In order for this
to work correctly, the sub report needs to be in a preceding section.
In this example, I have a subreport in Group Header #4a, and the
formula in Group Header #4b. You could do the same with detail lines
if you are not using grouping (i.e. subreport in Detail a followed by
formula in Detail b).



Whileprintingrecords;

Shared NumberVar TotalPrice;

Numbervar TotalGroup;

Numbervar TotalReport;

TotalGroup := TotalGroup + TotalPrice;

TotalReport := TotalReport + TotalPrice;

TotalPrice;



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 4:06 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question





Oops, forgot about that.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 4:38 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Quick Shared Var question

No you cannot use running totals on sharedvar

Thanks. That worked. Brain-faded. I Knew that!

Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: "Vic Drecchio" <vic.drecchio@...
<mailto:vic.drecchio%40timco.aero>
<mailto:vic.drecchio%40timco.aero> >
Sender: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Date: Mon, 25 Oct 2010 16:06:33
To: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Reply-To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question

Can you use RT's on Vars?

I didn't think so..... I cannot on my reports using shared vars.

What I do is create another var on the main report page to tally those
detail records. You must have this "tally" formula in a subsequent
detail section (Detail b). You can suppress that detail subsection and
the formula for the var tally will still fire.

Something like this:

Shared NumberVar VarTotal := New + VarTotal

Then in your report footer display the "VarTotal"

Vic

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 3:55 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question

You could use a running total.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
<mailto:advantage%40wcoil.com>
<mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Quick Shared Var question

If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

[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]

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





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

[system]

I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?

thanks

[system]

Hi,

Not sure what version you are on. We are on Vantage v8. I did try and investigate this before and couldn't find a direct link at the database level. I think what was happening is as follows;

1) Invoice is posted
2) Vantage uses a program to generate the journal entries to include any Tax and other transactions. However, there is no reference to individual invoice item/Lines entries in the resulting GL journal entries. I did try creating a query which would model the links but gave up.

--- In vantage@yahoogroups.com, "hessd3" <hessd3@...> wrote:
>
> I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
>
> thanks
>

[system]

hessd3,

We also use vantage 8.03 - are you using the same?

When you say invoice and invoice line do you mean sales or purchase invoice? I have written sql views to replace all of the 'periodic posting' gljrndtl entries with exploded details. The views are kind of specialised to the way we use vantage e.g. we use material and labour costs but not burden rates. We also have to compare the views to gljrndtl monthly just to make sure no new transactions have been used that my views dont cater for.

Let me know sales or purchases and I can post the relevant sql script.

Dean

--- In vantage@yahoogroups.com, "brychanwilliams" <brychanwilliams@...> wrote:
>
> Hi,
>
> Not sure what version you are on. We are on Vantage v8. I did try and investigate this before and couldn't find a direct link at the database level. I think what was happening is as follows;
>
> 1) Invoice is posted
> 2) Vantage uses a program to generate the journal entries to include any Tax and other transactions. However, there is no reference to individual invoice item/Lines entries in the resulting GL journal entries. I did try creating a query which would model the links but gave up.
>
> --- In vantage@yahoogroups.com, "hessd3" <hessd3@> wrote:
> >
> > I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
> >
> > thanks
> >
>

[system]

We are on 8.03.406

I am trying this with the AR Invoice.

--- In vantage@yahoogroups.com, "karenalexander78" <karena@...> wrote:
>
> hessd3,
>
> We also use vantage 8.03 - are you using the same?
>
> When you say invoice and invoice line do you mean sales or purchase invoice? I have written sql views to replace all of the 'periodic posting' gljrndtl entries with exploded details. The views are kind of specialised to the way we use vantage e.g. we use material and labour costs but not burden rates. We also have to compare the views to gljrndtl monthly just to make sure no new transactions have been used that my views dont cater for.
>
> Let me know sales or purchases and I can post the relevant sql script.
>
> Dean
>
>
> --- In vantage@yahoogroups.com, "brychanwilliams" <brychanwilliams@> wrote:
> >
> > Hi,
> >
> > Not sure what version you are on. We are on Vantage v8. I did try and investigate this before and couldn't find a direct link at the database level. I think what was happening is as follows;
> >
> > 1) Invoice is posted
> > 2) Vantage uses a program to generate the journal entries to include any Tax and other transactions. However, there is no reference to individual invoice item/Lines entries in the resulting GL journal entries. I did try creating a query which would model the links but gave up.
> >
> > --- In vantage@yahoogroups.com, "hessd3" <hessd3@> wrote:
> > >
> > > I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
> > >
> > > thanks
> > >
> >
>

[system]

I've come to the same conclusion which has led me here. It's very frustrating to go into the AR Invoice Tracker and see the GL Transactions tab completely isolated from how the rest of the SO, PO, Invoices work.

I did find some help in gljrnsrc table but it looks to only be sparsely populated. I tried matching the dollar values but I get duplicates when multiple AR Invoice lines have the same amount. So if lines 2, 3, and 4 are $120, then I get one Crystal Report detail for line 1, and three detail records for line 2, three for line 3, and three for line 4. I also looked at maybe using the line description to finish linking them together but couldn't find a description in the GL section.

So what tables did you use to try your query?

A more general question. How does finance match up the AR Invoice lines to the journal and GL if there are Sales Orders with 300 lines with the same dollar amount?

--- In vantage@yahoogroups.com, "brychanwilliams" <brychanwilliams@...> wrote:
>
> Hi,
>
> Not sure what version you are on. We are on Vantage v8. I did try and investigate this before and couldn't find a direct link at the database level. I think what was happening is as follows;
>
> 1) Invoice is posted
> 2) Vantage uses a program to generate the journal entries to include any Tax and other transactions. However, there is no reference to individual invoice item/Lines entries in the resulting GL journal entries. I did try creating a query which would model the links but gave up.
>
> --- In vantage@yahoogroups.com, "hessd3" <hessd3@> wrote:
> >
> > I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
> >
> > thanks
> >
>

[system]

hessd3,

this is the sql that replaces all gljrndtl lines where sourcemodule = 'AR'.

each section gets a different element of the invoice;
– goods sale,
- sl control,
- vat sl control,
- vat outputs,
- advance bill debit,
- advance bill credit,
- misc goods,
- misc vat,
- ar control

note - we are a UK user so where you see vat think sales tax.

Dean
--------------------------------------------------------------------
USE [MfgSys803]
GO

--drop view [dbo].[biogas_actsal_2]
--go
--create view [dbo].[biogas_actsal_2] as

select 'actsal_2 arinvoice - goods sale' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
id.salesdiv AS gldiv,
id.salesdept AS gldept,
id.saleschart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(extprice*-1,2) AS transamt,
-- jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
case when id.ordernum is null then '[Blank]' when len(jp.jobnum)=0 or jp.jobnum is null then cast('00' as varchar) + cast(id.ordernum as varchar) + '-' + cast(id.orderline as varchar) + '-' + cast(id.orderrelnum as varchar) else jp.jobnum end as jobnum,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invchead as ih
INNER JOIN mfgsys803.dbo.invcdtl as id ON
ih.invoicenum = id.invoicenum AND ih.company = id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
(id.company=jp.company) AND (id.orderrelnum=jp.orderrelnum) AND (id.orderline=jp.orderline) AND (id.ordernum=jp.ordernum)
WHERE ih.startup=0
and ih.unappliedcash <> 1
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - goods sl control' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
ih.ardiv AS gldiv,
ih.ardept AS gldept,
ih.archart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(extprice,2) AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invchead as ih
INNER JOIN mfgsys803.dbo.invcdtl as id on
(ih.invoicenum=id.invoicenum) AND (ih.company=id.company)
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
(id.company=jp.company) AND (id.orderrelnum=jp.orderrelnum) AND (id.orderline=jp.orderline) AND (id.ordernum=jp.ordernum)
WHERE ih.startup=0
and ih.unappliedcash <> 1
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - vat sl control' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
ih.ardiv AS gldiv,
ih.ardept AS gldept,
ih.archart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(it.taxamt,2) AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.invctax as it on
id.invoicenum=it.invoicenum AND id.company=it.company and id.invoiceline = it.invoiceline
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
WHERE ih.startup=0 and ih.unappliedcash <> 1
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - vat outputs' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
it.taxdivision AS gldiv,
it.taxgldept AS gldept,
it.taxchart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(it.taxamt,2) * -1 AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.invctax as it on
id.invoicenum=it.invoicenum AND id.company=it.company and id.invoiceline = it.invoiceline
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
WHERE ih.startup=0
and ih.unappliedcash <> 1
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - advance bill debit' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
id.advancebilldiv AS gldiv,
id.advancebilldept AS gldept,
id.advancebillchart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(id.advancebillcredit,2) AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
WHERE ih.startup=0
and ih.unappliedcash <> 1
and id.advancebillcredit <> 0
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - advance bill credit' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
ih.ardiv AS gldiv,
ih.ardept AS gldept,
ih.archart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(id.advancebillcredit,2) * -1 AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
WHERE ih.startup=0
and ih.unappliedcash <> 1
and id.advancebillcredit <> 0
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod

--new section added 5/5/2010 to add in misc non job type invoices
union all
select 'actsal_2 arinvoice - misc goods' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
im.miscdivision AS gldiv,
im.miscgldept AS gldept,
im.miscchart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(im.miscamt,2) * -1 AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
LEFT JOIN mfgsys803.dbo.invcmisc as im ON
id.company=im.company AND id.invoicenum=im.invoicenum AND id.invoiceline=im.invoiceline

WHERE ih.startup=0
and ih.unappliedcash <> 1
and im.invoicenum is not null
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - misc vat' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
it.taxdivision AS gldiv,
it.taxgldept AS gldept,
it.taxchart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(it.taxamt,2) * -1 AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
LEFT JOIN mfgsys803.dbo.invcmisc as im ON
id.company=im.company AND id.invoicenum=im.invoicenum AND id.invoiceline=im.invoiceline
LEFT JOIN mfgsys803.dbo.invctax as it ON
im.company=it.company AND im.invoicenum=it.invoicenum AND im.invoiceline=it.invoiceline

WHERE ih.startup=0
and ih.unappliedcash <> 1
and im.invoicenum is not null
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod
union all
select 'actsal_2 arinvoice - ar control' AS source_qry,
id.company,
ih.fiscalyear,
ih.fiscalperiod,
ih.ardiv AS gldiv,
ih.ardept AS gldept,
ih.archart AS glchart,
ih.invoicedate AS transdate,
id.journalnum,
'' AS journalline,
replace(cast(id.linedesc as varchar(50)),',',' ') AS description,
round(ih.invoiceamt,2) AS transamt,
jobnum = case when len(jp.jobnum)=0 or jp.jobnum is null then '[Blank]' else jp.jobnum end,
'SJ' AS journalcode,
id.invoicenum AS arinvoicenum,
id.ordernum AS salesordernum,
id.orderline AS salesorderline,
ih.posted,
cu.custid,
cu.name
FROM mfgsys803.dbo.invcdtl as id
join mfgsys803.dbo.invchead as ih on
ih.invoicenum=id.invoicenum AND ih.company=id.company
join mfgsys803.dbo.customer as cu on
ih.company = cu.company and ih.custnum = cu.custnum
LEFT JOIN mfgsys803.dbo.jobprod as jp ON
id.company=jp.company AND id.orderrelnum=jp.orderrelnum AND id.orderline=jp.orderline AND id.ordernum=jp.ordernum
LEFT JOIN mfgsys803.dbo.invcmisc as im ON
id.company=im.company AND id.invoicenum=im.invoicenum AND id.invoiceline=im.invoiceline
WHERE ih.startup=0
and ih.unappliedcash <> 1
and im.invoicenum is not null
--and 2009 = ih.fiscalyear and 8 = ih.fiscalperiod

[system]

So I've talked with Epicor BAQ and GL support and there isn't any way that they can think of to link the AR invoice line and the GL. The only way I could think of to possibly do this now would be to construct the full path of GL accounts so that I could follow a Sales Order breakdown by GL all the way into the GL and then tie that from the SO line to the AR invoice line, in the end invoice line -> GL entry. (maybe) Seems like a lot of work for little benefit.

The original problem occurred because I had to total up an AR invoice from the GL because it was the only way to correctly capture all of the different stuff we put on the Sales Order line.

In the end, I asked sales to just do 1 shipment to a Sales Order which will prevent an invoice from showing up as having a shipment to different states and hence duplicating the invoice total on the report. For us it's not a big deal because we only do 5 split shipment Sales Orders a year.

--- In vantage@yahoogroups.com, "hessd3" <hessd3@...> wrote:
>
> I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
>
> thanks
>

[system]

What version are you on?
We're at 9.04.506C - 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.

--- In vantage@yahoogroups.com, "hessd3" <hessd3@...> wrote:
>
> So I've talked with Epicor BAQ and GL support and there isn't any way that they can think of to link the AR invoice line and the GL. The only way I could think of to possibly do this now would be to construct the full path of GL accounts so that I could follow a Sales Order breakdown by GL all the way into the GL and then tie that from the SO line to the AR invoice line, in the end invoice line -> GL entry. (maybe) Seems like a lot of work for little benefit.
>
> The original problem occurred because I had to total up an AR invoice from the GL because it was the only way to correctly capture all of the different stuff we put on the Sales Order line.
>
> In the end, I asked sales to just do 1 shipment to a Sales Order which will prevent an invoice from showing up as having a shipment to different states and hence duplicating the invoice total on the report. For us it's not a big deal because we only do 5 split shipment Sales Orders a year.
>
>
> --- In vantage@yahoogroups.com, "hessd3" <hessd3@> wrote:
> >
> > I can't find any way to link the GL or journal transactions to the invoice and invoice line. I tried the glacct and gljrndtl tables I'm still can't get the invoice line linked. What are the tables I should be using?
> >
> > thanks
> >
>

[system]

If I have a shared numbervar at Detail publishing out from a subreport to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

[system]

You could use a running total.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of advantage@...
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Quick Shared Var question





If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;





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

[system]

How do you mean. I right click the sharedvar, and Insert Running Total
isn't available

> You could use a running total.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of advantage@...
> Sent: Monday, October 25, 2010 3:08 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Quick Shared Var question
>
>
>
>
>
> If I have a shared numbervar at Detail publishing out from a subreport
> to
> main report
>
> How do I get the Report Footer to total for those shared var
>
> i.e.
>
> Subreport:
>
> Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);
>
> then on the main report in the Detail
>
> Shared NumberVar New :=ExtdStdMtlCost;
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>

[system]

Can you use RT's on Vars?



I didn't think so..... I cannot on my reports using shared vars.



What I do is create another var on the main report page to tally those
detail records. You must have this "tally" formula in a subsequent
detail section (Detail b). You can suppress that detail subsection and
the formula for the var tally will still fire.



Something like this:



Shared NumberVar VarTotal := New + VarTotal





Then in your report footer display the "VarTotal"







Vic



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 3:55 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question





You could use a running total.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Quick Shared Var question

If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

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





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

[system]

No you cannot use running totals on sharedvar

Thanks. That worked. Brain-faded. I Knew that!


Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: "Vic Drecchio" <vic.drecchio@...>
Sender: vantage@yahoogroups.com
Date: Mon, 25 Oct 2010 16:06:33
To: <vantage@yahoogroups.com>
Reply-To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question

Can you use RT's on Vars?



I didn't think so..... I cannot on my reports using shared vars.



What I do is create another var on the main report page to tally those
detail records. You must have this "tally" formula in a subsequent
detail section (Detail b). You can suppress that detail subsection and
the formula for the var tally will still fire.



Something like this:



Shared NumberVar VarTotal := New + VarTotal





Then in your report footer display the "VarTotal"







Vic



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 3:55 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question





You could use a running total.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Quick Shared Var question

If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

[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]

[system]

Oops, forgot about that.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of advantage@...
Sent: Monday, October 25, 2010 4:38 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Quick Shared Var question





No you cannot use running totals on sharedvar

Thanks. That worked. Brain-faded. I Knew that!


Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: "Vic Drecchio" <vic.drecchio@...
<mailto:vic.drecchio%40timco.aero> >
Sender: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Date: Mon, 25 Oct 2010 16:06:33
To: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Reply-To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question

Can you use RT's on Vars?



I didn't think so..... I cannot on my reports using shared vars.



What I do is create another var on the main report page to tally those
detail records. You must have this "tally" formula in a subsequent
detail section (Detail b). You can suppress that detail subsection and
the formula for the var tally will still fire.



Something like this:



Shared NumberVar VarTotal := New + VarTotal





Then in your report footer display the "VarTotal"







Vic



________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 3:55 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question





You could use a running total.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
<mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Quick Shared Var question

If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);


then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

[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]





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

[system]

You can create a formula to maintain a sharedvar total. There is a
trick to it. Below is an example of a formula I use. In order for this
to work correctly, the sub report needs to be in a preceding section.
In this example, I have a subreport in Group Header #4a, and the
formula in Group Header #4b. You could do the same with detail lines
if you are not using grouping (i.e. subreport in Detail a followed by
formula in Detail b).



Whileprintingrecords;

Shared NumberVar TotalPrice;

Numbervar TotalGroup;

Numbervar TotalReport;

TotalGroup := TotalGroup + TotalPrice;

TotalReport := TotalReport + TotalPrice;

TotalPrice;



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 4:06 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Quick Shared Var question





Oops, forgot about that.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 4:38 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Quick Shared Var question

No you cannot use running totals on sharedvar

Thanks. That worked. Brain-faded. I Knew that!

Sent from my Verizon Wireless BlackBerry

-----Original Message-----
From: "Vic Drecchio" <vic.drecchio@...
<mailto:vic.drecchio%40timco.aero>
<mailto:vic.drecchio%40timco.aero> >
Sender: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Date: Mon, 25 Oct 2010 16:06:33
To: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Reply-To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question

Can you use RT's on Vars?

I didn't think so..... I cannot on my reports using shared vars.

What I do is create another var on the main report page to tally those
detail records. You must have this "tally" formula in a subsequent
detail section (Detail b). You can suppress that detail subsection and
the formula for the var tally will still fire.

Something like this:

Shared NumberVar VarTotal := New + VarTotal

Then in your report footer display the "VarTotal"

Vic

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Linda Lowney
Sent: Monday, October 25, 2010 3:55 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Quick Shared Var question

You could use a running total.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of advantage@... <mailto:advantage%40wcoil.com>
<mailto:advantage%40wcoil.com>
<mailto:advantage%40wcoil.com>
Sent: Monday, October 25, 2010 3:08 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Quick Shared Var question

If I have a shared numbervar at Detail publishing out from a subreport
to
main report

How do I get the Report Footer to total for those shared var

i.e.

Subreport:

Shared NumberVar ExtStdMtlCost := (RequiredQty) * (PartCost.StdMtlCost);

then on the main report in the Detail

Shared NumberVar New :=ExtdStdMtlCost;

[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]

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





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