Vantage 8.00 -- Need Help With Exportable Stock Status Report

Thanks Butch.

I'm not using Access but your code will undoubtedly help me move this
forward. My priorities keep shifting but I'm hoping to tackle this
very soon -- and I do appreciate the help.

Ahmet

--- In vantage@yahoogroups.com, "Butch Menge" <bmenge@...> wrote:
>
> Below is a query that may get you started if you're using Access.
To
> use it, just create a new query, don't add any tables, select the
SQL
> view and then paste in the query below. Go into design view and
> customize it to your company. You'll see I'm pulling info from a
form
> to filter the warehouse, typecode and date (this gets an inventory
on a
> certain date). You may find that you have to rewrite the trantype
> criteria because Access will rewrite the query when storing it to
> optimize it but doesn't always work when running it in design view.
>
>
>
> Butch
>
>
>
> SELECT
> IIf(Left([Class],1)="B","BioArchive",IIf(Left([Class],1)
="C","CryoSeal",
> IIf(Left([Class],1)="T","ThermoLine","Unknown"))) AS ProductLine,
> PUB_PartTran.PartNum, PUB_Part.PartDescription, PUB_Part.TypeCode,
> PUB_PartTran.WareHouseCode, First(PUB_PartTran.BinNum) AS
FirstOfBinNum,
> Sum(IIf(([TranClass]="I") Or ([TranClass]="S"),-[TranQty],
[TranQty])) AS
> Quantity, PUB_Part.StdMaterialCost, PUB_Part.StdLaborCost,
> PUB_Part.StdBurdenCost, PUB_Part.StdSubContCost,
PUB_Part.StdMtlBurCost,
> [StdMaterialCost]+[StdLaborCost]+[StdBurdenCost]+[StdSubContCost]+
[StdMt
> lBurCost] AS UnitCost, Sum(IIf(([TranClass]="I") Or
> ([TranClass]="S"),-[TranQty]*([StdMaterialCost]+[StdSubContCost]),
[TranQ
> ty]*([StdMaterialCost]+[StdSubContCost]))) AS ExtMtlCost,
> Sum(IIf(([TranClass]="I") Or
> ([TranClass]="S"),-[TranQty]*([StdMaterialCost]+[StdLaborCost]+
[StdBurde
> nCost]+[StdSubContCost]+[StdMtlBurCost]),[TranQty]*
([StdMaterialCost]+[S
> tdLaborCost]+[StdBurdenCost]+[StdSubContCost]+[StdMtlBurCost]))) AS
> ExtCost
>
> FROM PUB_PartTran INNER JOIN PUB_Part ON PUB_PartTran.PartNum =
> PUB_Part.PartNum
>
> WHERE
> (((PUB_PartTran.TranDate)<=Eval("[forms]![frmWarehouseReports]!
[txtCutof
> fDate]")) AND ((PUB_PartTran.InventoryTrans)=True) AND
> ((([PUB_PartTran].[TranType])="adj-cst" Or
> ([PUB_PartTran].[TranType])="dmr-rej" Or
> ([PUB_PartTran].[TranType])="ins-dmr")=False) AND
((PUB_Part.TypeCode)
> Like Eval("[forms]![frmWarehouseReports]![lbType]") & "*"))
>
> GROUP BY
> IIf(Left([Class],1)="B","BioArchive",IIf(Left([Class],1)
="C","CryoSeal",
> IIf(Left([Class],1)="T","ThermoLine","Unknown"))),
PUB_PartTran.PartNum,
> PUB_Part.PartDescription, PUB_Part.TypeCode,
PUB_PartTran.WareHouseCode,
> PUB_Part.StdMaterialCost, PUB_Part.StdLaborCost,
PUB_Part.StdBurdenCost,
> PUB_Part.StdSubContCost, PUB_Part.StdMtlBurCost,
> [StdMaterialCost]+[StdLaborCost]+[StdBurdenCost]+[StdSubContCost]+
[StdMt
> lBurCost]
>
> HAVING
> (((PUB_PartTran.WareHouseCode)=Eval("[forms]![frmWarehouseReports]!
[lbWa
> rehouse]")) AND ((Sum(IIf(([TranClass]="I") Or
> ([TranClass]="S"),-[TranQty],[TranQty])))<>0));
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of ahmeterispaha
> Sent: Friday, March 21, 2008 10:20 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Vantage 8.00 -- Need Help With Exportable
Stock
> Status Report
>
>
>
> Hi everybody.
>
> Here are my questions:
>
> 1)Can someone fill me in on what it means in Vantage for a PartTran
> record to be marked as an InventoryTrans? All the Vantage Data
> Dictionary Viewer says is "Indicates if this is an inventory
> transaction. It will be used in transaction selection." Aren't all
> part transactions inventory transactions of some sort?
>
> I'm relatively new to the manufacturing side of business so please
> forgive me if this question is naive.
>
> 2) Mark -- I did see (at least in our database) that if I filter
out
> the records where InventoryTrans=0 (False), my 'PUR-SUB' TranTypes
> also drop out. Does this sound like something I can universally
> count on? If so, are there other such relationships that can give
me
> a broader understanding of the meanings of TranClass,
InventoryTrans,
> and TranType?
>
> 3) How can I get the best information on how the plethora of
> TranTypes impact the various part "buckets" -- both in terms of
> number of parts and costs. I noticed, for example, that a MTL-INS
> transaction transfers ## but not $$ out of WIP. What, in Vantage,
> determines when a shift in # of parts from one "bucket" to another
> implies a corresponding shift in $?
>
>
> Thanks,
>
> Ahmet
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> "Mark Wonsil" <mark_wonsil@> wrote:
> >
> > Hi Ahmet,
> > > I need to write a custom version of the stock status report
(SQL
> ODBC) so
> > > that I can export it to Excel -- the existing report is plain
> text and
> > > cannot be exported.
> >
> > The general idea is to start with your quantities and back off
> transactions
> > that occurred since the As Of date, and like Kersten said, you
use
> the
> > PartTran table. You need to exclude some records though:
> >
> > - All PartTran records before your As Of date.
> > - All PartTran records where InventoryTrans = False (Non-
inventory
> records)
> > - All PartTran records related to subcontracting (like PUR-SUB)
> > - All PartTran records that are cost adjustments (ADJ-CST)
> >
> > The next thing I did was to calculate the correct sign for the
> quantity. You
> > want to add back in issues and subtract out receipts. For
> adjustments, you
> > want to flip the sign. You can use TranClass to determine what
kind
> of
> > PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)
> >
> > A good starting point is to do a query showing how many of the
> PartTran
> > records there are by TranType and TranClass where the
> InventoryTrans = True.
> > These are the records that you'll have to "undo".
> >
> > I was doing this by warehouse and not by bin. But the idea is the
> same, you
> > just need to start with a quantity by part and bin and then
> calculate the
> > offset by part and bin.
> >
> > Have fun!
> >
> > Mark W.
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
I need to write a custom version of the stock status report (SQL ODBC) so that I can export it to Excel -- the existing report is plain text and cannot be exported.

I think I have everything except the table(s) that contain the inventory "as of date" data. Here's my SQL command so far:

SELECT Part.PartNum AS Part,
PartDescription AS Description,
BinNum AS Bin,
PartBin.OnHandQty AS Qty,
avglaborcost + avgburdencost+ avgmaterialcost+ avgsubcontcost+ avgmtlburcost as [Unit Cost],
PartBin.OnHandQty * (avglaborcost + avgburdencost+ avgmaterialcost+ avgsubcontcost+ avgmtlburcost) as [Extended Cost]
FROM ((Part INNER JOIN PartCost ON Part.Company=PartCost.Company AND Part.PartNum=PartCost.PartNum)
INNER JOIN PartWhse ON Part.Company=PartWhse.Company AND Part.PartNum=PartWhse.PartNum)
INNER JOIN PartBin ON Part.Company=PartBin.Company AND Part.PartNum=PartBin.PartNum
ORDER BY Part.PartNum;

Any help in identifying the table(s) that store information relating to inventory counts as of a particular date would be appreciated.

Thanks,

Ahmet
New Way Air Bearings


---------------------------------
Never miss a thing. Make Yahoo your homepage.

[Non-text portions of this message have been removed]
I believe the levels are calculated from the PartTrans table


Kersten MacLennan
IT/ERP Analyst
Semrock, Inc.
3625 Buffalo Rd.,
Suite 6
Rochester, NY 14624
585-594-7009
585-594-7095 fax

kmaclennan@...

The Standard in Optical Filters for Biotech & Analytical Instrumentation


Hundreds of Thousands of Ion Beam Sputtered filters delivered -
extensive inventory now!

The information contained in this message and any attachments may be
privileged, confidential, and protected from disclosure. If the reader
of this message is not the intended recipient, or any agent responsible
for delivering this message to the intended recipient, you are hereby
notified that any dissemination, distribution, or copying of this
communication may be unlawful and therefore strictly prohibited. If you
received this message in error, please reply to the message and delete
it. Thank you




________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ahmet Erispaha
Sent: Friday, January 25, 2008 3:34 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 8.00 -- Need Help With Exportable Stock
Status Report



I need to write a custom version of the stock status report (SQL ODBC)
so that I can export it to Excel -- the existing report is plain text
and cannot be exported.

I think I have everything except the table(s) that contain the inventory
"as of date" data. Here's my SQL command so far:

SELECT Part.PartNum AS Part,
PartDescription AS Description,
BinNum AS Bin,
PartBin.OnHandQty AS Qty,
avglaborcost + avgburdencost+ avgmaterialcost+ avgsubcontcost+
avgmtlburcost as [Unit Cost],
PartBin.OnHandQty * (avglaborcost + avgburdencost+ avgmaterialcost+
avgsubcontcost+ avgmtlburcost) as [Extended Cost]
FROM ((Part INNER JOIN PartCost ON Part.Company=PartCost.Company AND
Part.PartNum=PartCost.PartNum)
INNER JOIN PartWhse ON Part.Company=PartWhse.Company AND
Part.PartNum=PartWhse.PartNum)
INNER JOIN PartBin ON Part.Company=PartBin.Company AND
Part.PartNum=PartBin.PartNum
ORDER BY Part.PartNum;

Any help in identifying the table(s) that store information relating to
inventory counts as of a particular date would be appreciated.

Thanks,

Ahmet
New Way Air Bearings

---------------------------------
Never miss a thing. Make Yahoo your homepage.

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






[Non-text portions of this message have been removed]
Hi Ahmet,
> I need to write a custom version of the stock status report (SQL ODBC) so
> that I can export it to Excel -- the existing report is plain text and
> cannot be exported.

The general idea is to start with your quantities and back off transactions
that occurred since the As Of date, and like Kersten said, you use the
PartTran table. You need to exclude some records though:

- All PartTran records before your As Of date.
- All PartTran records where InventoryTrans = False (Non-inventory records)
- All PartTran records related to subcontracting (like PUR-SUB)
- All PartTran records that are cost adjustments (ADJ-CST)

The next thing I did was to calculate the correct sign for the quantity. You
want to add back in issues and subtract out receipts. For adjustments, you
want to flip the sign. You can use TranClass to determine what kind of
PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)

A good starting point is to do a query showing how many of the PartTran
records there are by TranType and TranClass where the InventoryTrans = True.
These are the records that you'll have to "undo".

I was doing this by warehouse and not by bin. But the idea is the same, you
just need to start with a quantity by part and bin and then calculate the
offset by part and bin.

Have fun!

Mark W.
Thanks Mark and Kersten!

I think that gives me enough to get started.

Ahmet

Mark Wonsil <mark_wonsil@...> wrote:
Hi Ahmet,
> I need to write a custom version of the stock status report (SQL ODBC) so
> that I can export it to Excel -- the existing report is plain text and
> cannot be exported.

The general idea is to start with your quantities and back off transactions
that occurred since the As Of date, and like Kersten said, you use the
PartTran table. You need to exclude some records though:

- All PartTran records before your As Of date.
- All PartTran records where InventoryTrans = False (Non-inventory records)
- All PartTran records related to subcontracting (like PUR-SUB)
- All PartTran records that are cost adjustments (ADJ-CST)

The next thing I did was to calculate the correct sign for the quantity. You
want to add back in issues and subtract out receipts. For adjustments, you
want to flip the sign. You can use TranClass to determine what kind of
PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)

A good starting point is to do a query showing how many of the PartTran
records there are by TranType and TranClass where the InventoryTrans = True.
These are the records that you'll have to "undo".

I was doing this by warehouse and not by bin. But the idea is the same, you
just need to start with a quantity by part and bin and then calculate the
offset by part and bin.

Have fun!

Mark W.






---------------------------------
Looking for last minute shopping deals? Find them fast with Yahoo! Search.

[Non-text portions of this message have been removed]
I wrote my stock status in Access for Vantage 6.1. Hopefully it will
work in 8.0 when we convert. Below are my filters:



Not (([PUB_PartTran].[TranType])="adj-cst" Or
([PUB_PartTran].[TranType])="dmr-rej" Or
([PUB_PartTran].[TranType])="ins-dmr")

InventoryTrans = True

Quantity: IIf(([TranClass]="I") Or
([TranClass]="S"),-[TranQty],[TranQty])

TranDate <= [Enter cutoff date]



The cost is the tricky part - I just use current standard cost. Group
and sum the numbers and you should be there.



Good luck,

Butch



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Ahmet Erispaha
Sent: Sunday, January 27, 2008 2:03 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Vantage 8.00 -- Need Help With Exportable Stock
Status Report



Thanks Mark and Kersten!

I think that gives me enough to get started.

Ahmet

Mark Wonsil <mark_wonsil@... <mailto:mark_wonsil%40yahoo.com> >
wrote:
Hi Ahmet,
> I need to write a custom version of the stock status report (SQL ODBC)
so
> that I can export it to Excel -- the existing report is plain text and
> cannot be exported.

The general idea is to start with your quantities and back off
transactions
that occurred since the As Of date, and like Kersten said, you use the
PartTran table. You need to exclude some records though:

- All PartTran records before your As Of date.
- All PartTran records where InventoryTrans = False (Non-inventory
records)
- All PartTran records related to subcontracting (like PUR-SUB)
- All PartTran records that are cost adjustments (ADJ-CST)

The next thing I did was to calculate the correct sign for the quantity.
You
want to add back in issues and subtract out receipts. For adjustments,
you
want to flip the sign. You can use TranClass to determine what kind of
PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)

A good starting point is to do a query showing how many of the PartTran
records there are by TranType and TranClass where the InventoryTrans =
True.
These are the records that you'll have to "undo".

I was doing this by warehouse and not by bin. But the idea is the same,
you
just need to start with a quantity by part and bin and then calculate
the
offset by part and bin.

Have fun!

Mark W.

---------------------------------
Looking for last minute shopping deals? Find them fast with Yahoo!
Search.

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





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

The as inventory quantities for a date in the past must be
calculated as far as I know. I don't think that this can be done
with a simple select.

Matthew

--- In vantage@yahoogroups.com, Ahmet Erispaha <ahmeterispaha@...>
wrote:
>
> I need to write a custom version of the stock status report (SQL
ODBC) so that I can export it to Excel -- the existing report is
plain text and cannot be exported.
>
> I think I have everything except the table(s) that contain the
inventory "as of date" data. Here's my SQL command so far:
>
> SELECT Part.PartNum AS Part,
> PartDescription AS Description,
> BinNum AS Bin,
> PartBin.OnHandQty AS Qty,
> avglaborcost + avgburdencost+ avgmaterialcost+ avgsubcontcost+
avgmtlburcost as [Unit Cost],
> PartBin.OnHandQty * (avglaborcost + avgburdencost+
avgmaterialcost+ avgsubcontcost+ avgmtlburcost) as [Extended Cost]
> FROM ((Part INNER JOIN PartCost ON Part.Company=PartCost.Company
AND Part.PartNum=PartCost.PartNum)
> INNER JOIN PartWhse ON Part.Company=PartWhse.Company AND
Part.PartNum=PartWhse.PartNum)
> INNER JOIN PartBin ON Part.Company=PartBin.Company AND
Part.PartNum=PartBin.PartNum
> ORDER BY Part.PartNum;
>
> Any help in identifying the table(s) that store information
relating to inventory counts as of a particular date would be
appreciated.
>
> Thanks,
>
> Ahmet
> New Way Air Bearings
>
>
> ---------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
> [Non-text portions of this message have been removed]
>
Matthew,

I think you're right. Butch provided some more details on how he did this a couple of emails back (thanks Butch!).

Thanks,

Ahmet

macfarmw <matthew_macfarland@...> wrote:
Ahmet,

The as inventory quantities for a date in the past must be
calculated as far as I know. I don't think that this can be done
with a simple select.

Matthew

--- In vantage@yahoogroups.com, Ahmet Erispaha <ahmeterispaha@...>
wrote:
>
> I need to write a custom version of the stock status report (SQL
ODBC) so that I can export it to Excel -- the existing report is
plain text and cannot be exported.
>
> I think I have everything except the table(s) that contain the
inventory "as of date" data. Here's my SQL command so far:
>
> SELECT Part.PartNum AS Part,
> PartDescription AS Description,
> BinNum AS Bin,
> PartBin.OnHandQty AS Qty,
> avglaborcost + avgburdencost+ avgmaterialcost+ avgsubcontcost+
avgmtlburcost as [Unit Cost],
> PartBin.OnHandQty * (avglaborcost + avgburdencost+
avgmaterialcost+ avgsubcontcost+ avgmtlburcost) as [Extended Cost]
> FROM ((Part INNER JOIN PartCost ON Part.Company=PartCost.Company
AND Part.PartNum=PartCost.PartNum)
> INNER JOIN PartWhse ON Part.Company=PartWhse.Company AND
Part.PartNum=PartWhse.PartNum)
> INNER JOIN PartBin ON Part.Company=PartBin.Company AND
Part.PartNum=PartBin.PartNum
> ORDER BY Part.PartNum;
>
> Any help in identifying the table(s) that store information
relating to inventory counts as of a particular date would be
appreciated.
>
> Thanks,
>
> Ahmet
> New Way Air Bearings
>
>
> ---------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
> [Non-text portions of this message have been removed]
>






---------------------------------
Never miss a thing. Make Yahoo your homepage.

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

Here are my questions:

1)Can someone fill me in on what it means in Vantage for a PartTran
record to be marked as an InventoryTrans? All the Vantage Data
Dictionary Viewer says is "Indicates if this is an inventory
transaction. It will be used in transaction selection." Aren't all
part transactions inventory transactions of some sort?

I'm relatively new to the manufacturing side of business so please
forgive me if this question is naive.

2) Mark -- I did see (at least in our database) that if I filter out
the records where InventoryTrans=0 (False), my 'PUR-SUB' TranTypes
also drop out. Does this sound like something I can universally
count on? If so, are there other such relationships that can give me
a broader understanding of the meanings of TranClass, InventoryTrans,
and TranType?

3) How can I get the best information on how the plethora of
TranTypes impact the various part "buckets" -- both in terms of
number of parts and costs. I noticed, for example, that a MTL-INS
transaction transfers ## but not $$ out of WIP. What, in Vantage,
determines when a shift in # of parts from one "bucket" to another
implies a corresponding shift in $?


Thanks,

Ahmet


--- In vantage@yahoogroups.com, "Mark Wonsil" <mark_wonsil@...> wrote:
>
> Hi Ahmet,
> > I need to write a custom version of the stock status report (SQL
ODBC) so
> > that I can export it to Excel -- the existing report is plain
text and
> > cannot be exported.
>
> The general idea is to start with your quantities and back off
transactions
> that occurred since the As Of date, and like Kersten said, you use
the
> PartTran table. You need to exclude some records though:
>
> - All PartTran records before your As Of date.
> - All PartTran records where InventoryTrans = False (Non-inventory
records)
> - All PartTran records related to subcontracting (like PUR-SUB)
> - All PartTran records that are cost adjustments (ADJ-CST)
>
> The next thing I did was to calculate the correct sign for the
quantity. You
> want to add back in issues and subtract out receipts. For
adjustments, you
> want to flip the sign. You can use TranClass to determine what kind
of
> PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)
>
> A good starting point is to do a query showing how many of the
PartTran
> records there are by TranType and TranClass where the
InventoryTrans = True.
> These are the records that you'll have to "undo".
>
> I was doing this by warehouse and not by bin. But the idea is the
same, you
> just need to start with a quantity by part and bin and then
calculate the
> offset by part and bin.
>
> Have fun!
>
> Mark W.
>
> 1)Can someone fill me in on what it means in Vantage for a PartTran
> record to be marked as an InventoryTrans? All the Vantage Data
> Dictionary Viewer says is "Indicates if this is an inventory
> transaction. It will be used in transaction selection." Aren't all
> part transactions inventory transactions of some sort?

Every PartTran record will have a financial effect of some kind but not every
PartTran record affects an inventory quantity. If you buy material directly
for a job, the dollars go into WIP but no part quantities are affected. The
same for material going to and from subcontracting - money moves but no
changes to part location quantities. An "Inventory Transaction" changes a
parts location quantity I'm guessing.

> 2) Mark -- I did see (at least in our database) that if I filter out
> the records where InventoryTrans=0 (False), my 'PUR-SUB' TranTypes
> also drop out. Does this sound like something I can universally
> count on? If so, are there other such relationships that can give me
> a broader understanding of the meanings of TranClass, InventoryTrans,
> and TranType?

Since you're trying to recreate the Stock-Status Report, you're trying to only
consider transactions that affect location quantities. Any buy or make direct
parts do not affect a location quantity. So anything with SUB in it shouldn't
change your report.

> 3) How can I get the best information on how the plethora of
> TranTypes impact the various part "buckets" -- both in terms of
> number of parts and costs. I noticed, for example, that a MTL-INS
> transaction transfers ## but not $$ out of WIP. What, in Vantage,
> determines when a shift in # of parts from one "bucket" to another
> implies a corresponding shift in $?

As far as I know, the BEST place for that information is the Cost Accounting
guide that you get when you attended class. I think you can find each type of
transaction in the Help files but not all in one place IIRC.

Mark W.
Below is a query that may get you started if you're using Access. To
use it, just create a new query, don't add any tables, select the SQL
view and then paste in the query below. Go into design view and
customize it to your company. You'll see I'm pulling info from a form
to filter the warehouse, typecode and date (this gets an inventory on a
certain date). You may find that you have to rewrite the trantype
criteria because Access will rewrite the query when storing it to
optimize it but doesn't always work when running it in design view.



Butch



SELECT
IIf(Left([Class],1)="B","BioArchive",IIf(Left([Class],1)="C","CryoSeal",
IIf(Left([Class],1)="T","ThermoLine","Unknown"))) AS ProductLine,
PUB_PartTran.PartNum, PUB_Part.PartDescription, PUB_Part.TypeCode,
PUB_PartTran.WareHouseCode, First(PUB_PartTran.BinNum) AS FirstOfBinNum,
Sum(IIf(([TranClass]="I") Or ([TranClass]="S"),-[TranQty],[TranQty])) AS
Quantity, PUB_Part.StdMaterialCost, PUB_Part.StdLaborCost,
PUB_Part.StdBurdenCost, PUB_Part.StdSubContCost, PUB_Part.StdMtlBurCost,
[StdMaterialCost]+[StdLaborCost]+[StdBurdenCost]+[StdSubContCost]+[StdMt
lBurCost] AS UnitCost, Sum(IIf(([TranClass]="I") Or
([TranClass]="S"),-[TranQty]*([StdMaterialCost]+[StdSubContCost]),[TranQ
ty]*([StdMaterialCost]+[StdSubContCost]))) AS ExtMtlCost,
Sum(IIf(([TranClass]="I") Or
([TranClass]="S"),-[TranQty]*([StdMaterialCost]+[StdLaborCost]+[StdBurde
nCost]+[StdSubContCost]+[StdMtlBurCost]),[TranQty]*([StdMaterialCost]+[S
tdLaborCost]+[StdBurdenCost]+[StdSubContCost]+[StdMtlBurCost]))) AS
ExtCost

FROM PUB_PartTran INNER JOIN PUB_Part ON PUB_PartTran.PartNum =
PUB_Part.PartNum

WHERE
(((PUB_PartTran.TranDate)<=Eval("[forms]![frmWarehouseReports]![txtCutof
fDate]")) AND ((PUB_PartTran.InventoryTrans)=True) AND
((([PUB_PartTran].[TranType])="adj-cst" Or
([PUB_PartTran].[TranType])="dmr-rej" Or
([PUB_PartTran].[TranType])="ins-dmr")=False) AND ((PUB_Part.TypeCode)
Like Eval("[forms]![frmWarehouseReports]![lbType]") & "*"))

GROUP BY
IIf(Left([Class],1)="B","BioArchive",IIf(Left([Class],1)="C","CryoSeal",
IIf(Left([Class],1)="T","ThermoLine","Unknown"))), PUB_PartTran.PartNum,
PUB_Part.PartDescription, PUB_Part.TypeCode, PUB_PartTran.WareHouseCode,
PUB_Part.StdMaterialCost, PUB_Part.StdLaborCost, PUB_Part.StdBurdenCost,
PUB_Part.StdSubContCost, PUB_Part.StdMtlBurCost,
[StdMaterialCost]+[StdLaborCost]+[StdBurdenCost]+[StdSubContCost]+[StdMt
lBurCost]

HAVING
(((PUB_PartTran.WareHouseCode)=Eval("[forms]![frmWarehouseReports]![lbWa
rehouse]")) AND ((Sum(IIf(([TranClass]="I") Or
([TranClass]="S"),-[TranQty],[TranQty])))<>0));



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of ahmeterispaha
Sent: Friday, March 21, 2008 10:20 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Vantage 8.00 -- Need Help With Exportable Stock
Status Report



Hi everybody.

Here are my questions:

1)Can someone fill me in on what it means in Vantage for a PartTran
record to be marked as an InventoryTrans? All the Vantage Data
Dictionary Viewer says is "Indicates if this is an inventory
transaction. It will be used in transaction selection." Aren't all
part transactions inventory transactions of some sort?

I'm relatively new to the manufacturing side of business so please
forgive me if this question is naive.

2) Mark -- I did see (at least in our database) that if I filter out
the records where InventoryTrans=0 (False), my 'PUR-SUB' TranTypes
also drop out. Does this sound like something I can universally
count on? If so, are there other such relationships that can give me
a broader understanding of the meanings of TranClass, InventoryTrans,
and TranType?

3) How can I get the best information on how the plethora of
TranTypes impact the various part "buckets" -- both in terms of
number of parts and costs. I noticed, for example, that a MTL-INS
transaction transfers ## but not $$ out of WIP. What, in Vantage,
determines when a shift in # of parts from one "bucket" to another
implies a corresponding shift in $?


Thanks,

Ahmet

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Mark Wonsil" <mark_wonsil@...> wrote:
>
> Hi Ahmet,
> > I need to write a custom version of the stock status report (SQL
ODBC) so
> > that I can export it to Excel -- the existing report is plain
text and
> > cannot be exported.
>
> The general idea is to start with your quantities and back off
transactions
> that occurred since the As Of date, and like Kersten said, you use
the
> PartTran table. You need to exclude some records though:
>
> - All PartTran records before your As Of date.
> - All PartTran records where InventoryTrans = False (Non-inventory
records)
> - All PartTran records related to subcontracting (like PUR-SUB)
> - All PartTran records that are cost adjustments (ADJ-CST)
>
> The next thing I did was to calculate the correct sign for the
quantity. You
> want to add back in issues and subtract out receipts. For
adjustments, you
> want to flip the sign. You can use TranClass to determine what kind
of
> PartTran record it is (A=Adjustment,I=Issue,R=Receipt,etc.)
>
> A good starting point is to do a query showing how many of the
PartTran
> records there are by TranType and TranClass where the
InventoryTrans = True.
> These are the records that you'll have to "undo".
>
> I was doing this by warehouse and not by bin. But the idea is the
same, you
> just need to start with a quantity by part and bin and then
calculate the
> offset by part and bin.
>
> Have fun!
>
> Mark W.
>





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