Reporting - Material Transactions (V8)

Yahoo Archive
1
Thanks!

Karen





"Gerard Wadman"
<gwadman@scandius
.com> To
Sent by: <vantage@yahoogroups.com>
vantage@yahoogrou cc
ps.com
Subject
RE: [Vantage] Re: Reporting -
11/01/2006 03:11 Material Transactions (V8)
PM


Please respond to
vantage@yahoogrou
ps.com






On the client machine, go to:

Control Panel\Administrative Tools\Data Sources ODBC

Select your data source and click the configure button

Select the advanced tab and then select "Read Uncommitted" from the
"Default Isolation Level" drop down.

Click OK and exit.

Gerard M Wadman

Sr. Network Systems Engineer

Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460

978/486-4088 x 124

978/486-4108 (fax)

http://www.scandius.com/

This e-mail is for the use of the intended recipient(s) only. If you
have received this e-mail in error, please notify the sender immediately
and then delete it. If you are not the intended recipient, you must not
use, disclose or distribute this e-mail without the author's prior
permission. We have taken precautions to minimize the risk of
transmitting software viruses, but we advise you to carry out your own
virus checks on any attachment to this message. We do not accept
liability for any loss or damage caused by software viruses

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of kknolls@...
Sent: Wednesday, November 01, 2006 3:50 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Reporting - Material Transactions (V8)

Could you remind me where you select the Read Uncommitted?

Thanks,

Karen
Micro Dynamics

"Gerard Wadman"
<gwadman@scandius
.com> To
Sent by: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
vantage@yahoogrou cc
ps.com
Subject
RE: [Vantage] Re: Reporting -
11/01/2006 09:09 Material Transactions (V8)
AM

Please respond to
vantage@yahoogrou
ps.com

Correct the PartTran table is what you want.

If you are connecting via ODBC, make sure that you select Read
Uncommitted as your connection method as the PartTran table is written
to extensively and any record locks created against that table will slow
down Vantage big time.

Gerard M Wadman

Sr. Network Systems Engineer

Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460

978/486-4088 x 124

978/486-4108 (fax)

http://www.scandius.com/ <http://www.scandius.com/>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of bw2868bond
Sent: Wednesday, November 01, 2006 9:52 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Reporting - Material Transactions (V8)

I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ,
"Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [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]
2
We are on Vantage 8 - trying to write a report to pull all material
transactions on a job.
Can anyone help point me in the direction of where these individual
transactions are stored?
I've tried the JobMtl table and get only summary info - not each
transaction.
Thanks for the help.

Pam
Metro


[Non-text portions of this message have been removed]
3
I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com, "Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [Non-text portions of this message have been removed]
>
4
Thank you!!!

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of bw2868bond
Sent: Wednesday, November 01, 2006 8:52 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Reporting - Material Transactions (V8)



I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [Non-text portions of this message have been removed]
>






[Non-text portions of this message have been removed]
5
Correct the PartTran table is what you want.

If you are connecting via ODBC, make sure that you select Read
Uncommitted as your connection method as the PartTran table is written
to extensively and any record locks created against that table will slow
down Vantage big time.







Gerard M Wadman

Sr. Network Systems Engineer



Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460



978/486-4088 x 124

978/486-4108 (fax)



http://www.scandius.com/





________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of bw2868bond
Sent: Wednesday, November 01, 2006 9:52 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Reporting - Material Transactions (V8)



I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
6
Here are two Access queries for version 6.1 that I use to group part and
labor transactions for Vantage (I have a form where I allow the user to
specify a cutoff date for the transactions to be able to compare the
results to the WIP report in Vantage). Hopefully this will get you
started.

Butch

This query should be called qryWipLaborDetail and is used by the next
query:

SELECT PUB_LaborDtl.ClockInDate, PUB_LaborDtl.Company, 1 AS SysDate, 1
AS SysTime, 1 AS TranNum, PUB_LaborDtl.JobNum, 1 AS PartNum, 1 AS
TranQty, "Labor" AS TranType, "Labor" AS TranGroup, 0 AS Material, 0 AS
Labor, nz([LaborHrs])*nz([LaborRate]) AS LaborCost, 0 AS Burden,
nz([BurdenHrs])*nz([BurdenRate]) AS BurdenCost, 0 AS SubContract, 0 AS
MatBurden,
(nz([LaborHrs])*nz([LaborRate]))+(nz([BurdenHrs])*nz([BurdenRate])) AS
ExtendedCost
FROM PUB_LaborDtl
WHERE
(((PUB_LaborDtl.ClockInDate)<=CDate([Forms]![frmWipJobs]![txtCutoffDate]
)) AND ((PUB_LaborDtl.JobNum)<>""))
ORDER BY PUB_LaborDtl.JobNum;

This query can be called anything you wish:

SELECT PUB_PartTran.Company, PUB_PartTran.SysDate, PUB_PartTran.SysTime,
PUB_PartTran.TranNum, PUB_PartTran.JobNum, PUB_PartTran.PartNum,
PUB_PartTran.TranQty, PUB_PartTran.TranType, PUB_PartTran.TranDate,
IIf([TranType] In
("ADJ-MTL","ADJ-PUR","ADJ-SUB","DMR-ASM","DMR-MTL","INS-ASM","INS-MTL","
INS-SUB","PUR-MTL","PUR-SUB","STK-ASM","STK-MTL","WIP-MFG"),"Job
In",IIf([TranType] In
("MFG-CUS","MFG-STK","Mfg-Ven","MFG-WIP","ASM-INS","MTL-INS"),"Job
Out",IIf([TranType] In ("DMR-REJ"),"Scrap",IIf([TranType] In ("INS-DMR")
And [InventoryTrans],"Job Out","Other")))) AS TranGroup,
IIf([PUB_PartTran]![TranType]="adj-pur",IIf([JobSeqType]="M",[ExtCost],0
),nz([MtlUnitCost])*[TranQty]) AS Material, nz([LbrUnitCost])*[TranQty]
AS Labor, nz([BurUnitCost])*[TranQty] AS Burden,
IIf([PUB_PartTran]![TranType]="adj-pur",IIf([JobSeqType]="S",[ExtCost],0
),nz([SubUnitCost])*[TranQty]) AS SubContract,
nz([MtlBurUnitCost])*[TranQty] AS MatBurden, nz([ExtCost]) AS
ExtendedCost
FROM PUB_PartTran
WHERE (((PUB_PartTran.JobNum)<>"") AND
((PUB_PartTran.TranDate)<=CDate([Forms]![frmWipJobs]![txtCutoffDate])))
ORDER BY PUB_PartTran.JobNum
UNION SELECT qryWipLaborDetail.Company, qryWipLaborDetail.SysDate,
qryWipLaborDetail.SysTime, qryWipLaborDetail.TranNum,
qryWipLaborDetail.JobNum, qryWipLaborDetail.PartNum,
qryWipLaborDetail.TranQty, qryWipLaborDetail.TranType,
Last(qryWipLaborDetail.SysDate) AS TranDate,
qryWipLaborDetail.TranGroup, Sum(qryWipLaborDetail.Material) AS
Material, Sum(qryWipLaborDetail.LaborCost) AS Labor,
Sum(qryWipLaborDetail.BurdenCost) AS Burden,
Sum(qryWipLaborDetail.SubContract) AS SubContract,
Sum(qryWipLaborDetail.MatBurden) AS MatBurden,
Sum(qryWipLaborDetail.ExtendedCost) AS ExtendedCost
FROM qryWipLaborDetail
GROUP BY qryWipLaborDetail.Company, qryWipLaborDetail.SysDate,
qryWipLaborDetail.SysTime, qryWipLaborDetail.TranNum,
qryWipLaborDetail.JobNum, qryWipLaborDetail.PartNum,
qryWipLaborDetail.TranQty, qryWipLaborDetail.TranType,
qryWipLaborDetail.TranGroup;



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Lindquist, Pam
Sent: Wednesday, November 01, 2006 6:40 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Reporting - Material Transactions (V8)



We are on Vantage 8 - trying to write a report to pull all material
transactions on a job.
Can anyone help point me in the direction of where these individual
transactions are stored?
I've tried the JobMtl table and get only summary info - not each
transaction.
Thanks for the help.

Pam
Metro

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






[Non-text portions of this message have been removed]
7
Could you remind me where you select the Read Uncommitted?

Thanks,

Karen
Micro Dynamics





"Gerard Wadman"
<gwadman@scandius
.com> To
Sent by: <vantage@yahoogroups.com>
vantage@yahoogrou cc
ps.com
Subject
RE: [Vantage] Re: Reporting -
11/01/2006 09:09 Material Transactions (V8)
AM


Please respond to
vantage@yahoogrou
ps.com






Correct the PartTran table is what you want.

If you are connecting via ODBC, make sure that you select Read
Uncommitted as your connection method as the PartTran table is written
to extensively and any record locks created against that table will slow
down Vantage big time.

Gerard M Wadman

Sr. Network Systems Engineer

Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460

978/486-4088 x 124

978/486-4108 (fax)

http://www.scandius.com/

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of bw2868bond
Sent: Wednesday, November 01, 2006 9:52 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Reporting - Material Transactions (V8)

I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [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]
8
On the client machine, go to:



Control Panel\Administrative Tools\Data Sources ODBC



Select your data source and click the configure button

Select the advanced tab and then select "Read Uncommitted" from the
"Default Isolation Level" drop down.



Click OK and exit.



Gerard M Wadman

Sr. Network Systems Engineer



Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460



978/486-4088 x 124

978/486-4108 (fax)



http://www.scandius.com/





This e-mail is for the use of the intended recipient(s) only. If you
have received this e-mail in error, please notify the sender immediately
and then delete it. If you are not the intended recipient, you must not
use, disclose or distribute this e-mail without the author's prior
permission. We have taken precautions to minimize the risk of
transmitting software viruses, but we advise you to carry out your own
virus checks on any attachment to this message. We do not accept
liability for any loss or damage caused by software viruses







________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of kknolls@...
Sent: Wednesday, November 01, 2006 3:50 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Reporting - Material Transactions (V8)





Could you remind me where you select the Read Uncommitted?

Thanks,

Karen
Micro Dynamics

"Gerard Wadman"
<gwadman@scandius
.com> To
Sent by: <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
vantage@yahoogrou cc
ps.com
Subject
RE: [Vantage] Re: Reporting -
11/01/2006 09:09 Material Transactions (V8)
AM


Please respond to
vantage@yahoogrou
ps.com



Correct the PartTran table is what you want.

If you are connecting via ODBC, make sure that you select Read
Uncommitted as your connection method as the PartTran table is written
to extensively and any record locks created against that table will slow
down Vantage big time.

Gerard M Wadman

Sr. Network Systems Engineer

Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460

978/486-4088 x 124

978/486-4108 (fax)

http://www.scandius.com/ <http://www.scandius.com/>

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of bw2868bond
Sent: Wednesday, November 01, 2006 9:52 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Reporting - Material Transactions (V8)

I believe what you are looking for is in the PartTran table
There is an index consisting of PartNum,JobNum,LotNum,TranDate
that should get you to the data you want..

Bernie.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ,
"Lindquist, Pam" <plindquist@...> wrote:
>
> We are on Vantage 8 - trying to write a report to pull all material
> transactions on a job.
> Can anyone help point me in the direction of where these individual
> transactions are stored?
> I've tried the JobMtl table and get only summary info - not each
> transaction.
> Thanks for the help.
>
> Pam
> Metro
>
>
> [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]