On-hand quantity - 905.701

--- In vantage@yahoogroups.com, "bildo564" <BillyClaerbout@...> wrote:
>
> Is there a way to create a report to find out an on-hand quantity from a date in the past? The only way I could think of was to look at the part we are questioning and dissect the Part Transaction History Tracker.
>
> Thanks,
>
> Billy
>

The stock status report has an as of date.... only problem is its not selectable by part. Does have part class though.
Is there a way to create a report to find out an on-hand quantity from a date in the past? The only way I could think of was to look at the part we are questioning and dissect the Part Transaction History Tracker.

Thanks,

Billy
You're spot on; that's the only way. And it's not fun.



I worked with someone in the past and this was a real important metric for
them to have. I ended up utilizing one of the UD tables and since they were
on SQL I created a simple script that ran nightly that populated UD01 with
today's date, part number, qty on hand and total cost. So at any point in
time, we could look back to March 23rd and see what we had in stock and what
the value was.



So if you're a lucky SQL customer, perhaps that's an idea for moving
forward.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
bildo564
Sent: Friday, August 30, 2013 9:16 AM
To: vantage@yahoogroups.com
Subject: [Vantage] On-hand quantity - 905.701





Is there a way to create a report to find out an on-hand quantity from a
date in the past? The only way I could think of was to look at the part we
are questioning and dissect the Part Transaction History Tracker.

Thanks,

Billy





[Non-text portions of this message have been removed]
This will give you inventory counts as of whatever parameter date you pass
it (among st other parameters) I hope it helps you get started it also
compares it to current QtyOn hand but that's unrelated

CREATE PROCEDURE [dbo].[InventoryStockReport]
-- Add the parameters for the stored procedure here
@PartNum AS VARCHAR(MAX),
@Plant AS VARCHAR(MAX),
@BinNum AS VARCHAR(MAX),
@WhseCode AS VARCHAR(MAX),
@TranDate As VARCHAR(MAX),
@Company AS VARCHAR(MAX),
@DifffOnly As VARCHAR(MAX)
AS
BEGIN

WITH InvQty (Company, Plant, PartNum, WareHouseCode, BinNum, LotNum, Qty)
AS
(
SELECT Company, Plant, PartNum, WareHouseCode,BinNum, LotNum,
SUM(CASE WHEN TranType
IN('KIT-CUS','STK-CUS','STK-INS','STK-KIT','STK-MTL','STK-PLT','STK-UKN')
THEN TranQty * -1 ELSE TranQty END)
FROM Mfgsys803..PartTran WITH(NOLOCK)
WHERE TranType NOT IN
('ADJ-CST','ADJ-PUR','DMR-MTL','DMR-REJ','INS-DMR','INS-MTL','INS-REJ','MFG-VAR','PUR-INS','PUR-UKN','RMA-INS')
AND (TranDate <=@TranDate AND WareHouseCode LIKE IsNull(@WhseCode,'%') AND
BinNum LIKE IsNull(@BinNum,'%')
AND PartNum LIKE IsNull(@PartNum,'%') AND Plant LIKE IsNull(@Plant,'%')
AND Company LIKE IsNull(@Company,'%'))
GROUP BY Company, Plant, PartNum, WareHouseCode, BinNum, LotNum
)
SELECT InvQty.*, Qty * (IsNull(PartCost.StdLaborCost,0) +
IsNull(PartCost.StdBurdenCost,0) + IsNull(PartCost.StdMaterialCost,0) +
IsNull(PartCost.StdSubContCost,0) + IsNull(PartCost.StdMtlBurCost,0)) As
Value ,P.PartDescription, WH.Description AS WareHouseDescription,
IsNull(PB.OnHandQty,0) As OnHandQty, (IsNull(PB.OnHandQty,0) - Qty) As
Difference
FROM InvQty
LEFT OUTER JOIN Mfgsys803..PartCost WITH(NOLOCK) ON InvQty.Company =
PartCost.Company AND InvQty.PartNum = PartCost.PartNum AND PartCost.CostID=1
INNER JOIN Mfgsys803..Part P WITH(NOLOCK) ON InvQty.Company = P.Company
AND InvQty.PartNum = P.PartNum
LEFT OUTER JOIN Mfgsys803..PartBin PB on InvQty.Company = PB.Company AND
InvQty.PartNum = PB.PartNum AND InvQty.WareHouseCode= PB.WareHouseCode AND
InvQty.BinNum = PB.BinNum AND InvQty.LotNum = PB.LotNum
LEFT OUTER JOIN Mfgsys803..Warehse WH WITH(NOLOCK) ON InvQty.Company =
WH.Company AND InvQty.WareHouseCode=WH.WareHouseCode AND
InvQty.Plant=WH.Plant
WHERE InvQty.Qty <> 0 AND ((IsNull(PB.OnHandQty,0) - Qty) <> 0 OR
'YES'<>@DifffOnly) AND P.NonStock<>1
Order By InvQty.Company, InvQty.PartNum, InvQty.Plant,
InvQty.WareHouseCode, InvQty.BinNum, InvQty.LotNum
END





*Jose C Gomez*
*Software Engineer*
*
*
*
*T: 904.469.1524 mobile
E: jose@...
http://www.josecgomez.com
<http://www.linkedin.com/in/josecgomez> <http://www.facebook.com/josegomez>
<http://www.google.com/profiles/jose.gomez> <http://www.twitter.com/joc85>
<http://www.josecgomez.com/professional-resume/>
<http://www.josecgomez.com/feed/>
<http://www.usdoingstuff.com>

*Quis custodiet ipsos custodes?*


On Fri, Aug 30, 2013 at 9:22 AM, Vic Drecchio
<vic.drecchio@...>wrote:

> **
>
>
> You're spot on; that's the only way. And it's not fun.
>
> I worked with someone in the past and this was a real important metric for
> them to have. I ended up utilizing one of the UD tables and since they were
> on SQL I created a simple script that ran nightly that populated UD01 with
> today's date, part number, qty on hand and total cost. So at any point in
> time, we could look back to March 23rd and see what we had in stock and
> what
> the value was.
>
> So if you're a lucky SQL customer, perhaps that's an idea for moving
> forward.
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of
> bildo564
> Sent: Friday, August 30, 2013 9:16 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] On-hand quantity - 905.701
>
>
> Is there a way to create a report to find out an on-hand quantity from a
> date in the past? The only way I could think of was to look at the part we
> are questioning and dissect the Part Transaction History Tracker.
>
> Thanks,
>
> Billy
>
> [Non-text portions of this message have been removed]
>
>
>


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