Historical Stock Stock Status Stored Procedure

(0) Share

Report

Posted on by Community Member Microsoft Employee

Hi there, I was looking through the list of stored procedures in the GP company database and came across one called seeivPrintHistStockStatus. I took a look at it and realized this is a stored procedure that is used to create the Historical Stock Status report. It doesn't look as polished its counterpart seeivPrintStockStatus (there are some comments here and there explaining what the script is doing in this one).

So I have a couple of questions.

1) Can anyone comment on whether or not this stored procedure is legit and does what I'm assuming it does? I can't imagine that Microsoft would include something that wasn't ready for public consumption in a release of GP.

2) Has anyone built a SSRS version of the historical stock status report using this sp?

Thanks,

Mike

*This post is locked for comments

All responses (7)

Answers (0)

iruser2 2,046 on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

old post to reply to but i am using this stored proc in excel and have found a 'bug'

When you use the GP in transit transfer feature the store proc finds the From site if for the transfer into the in transit site, but it does not find the To side of the transfer. It does find the receipt out of the in transit site. Effectively then any in transit transfers performed after the as of date get picked up as negative.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

I use MarketXLS for this and it's great.

marketxls.com/historical-stock-data-in-excel

I hope it helps. :)
GMA 1,072 on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

As always I would recommend that you test and verify the sproc with your data; but prior to rolling this out to our accounting/inventory departments I compared the results against the HITB and it tied out on both quantity and cost figures.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

This is awesome.

I found the two sp's that GP uses to create the HITB report and was working on going through the code to try to clean it up, but this is perfect.

Have you confirmed that this ties to the out of the box HITB?

Thank you!!
GMA 1,072 on at

Like (1)

Report

RE: Historical Stock Stock Status Stored Procedure

Remind your customer that the HSS has correct quantity numbers, but uses an estimate for cost.

I use this sproc (requires you have already setup and been using the HITB) and then I setup an Excel spreadsheet to call the sproc allowing the end user to change the date. It is basically a trimmed down, simple version of the HITB.

It also omits items with zero cost and zero quantity.

-----------------------------------

Create procedure GPA_HITB (

@ASOFDATE DATETIME

)

as

SELECT RTRIM(A.ITEMNMBR) ITEMNMBR

, RTRIM(C.ITEMDESC) ITEMDESC

, RTRIM(A.LOCNCODE) LOCNCODE

, RTRIM(B.ACTNUMST) ACTNUMST

, SUM(A.TRXQTYInBase) QTY

, SUM(A.EXTDCOST) EXTDCOST

FROM SEE30303 A

JOIN GL00105 B

ON A.IVIVINDX = B.ACTINDX

JOIN IV00101 C

ON A.ITEMNMBR = C.ITEMNMBR

WHERE GLPOSTDT <= @ASOFDATE

GROUP BY A.ITEMNMBR, C.ITEMDESC, A.LOCNCODE, B.ACTNUMST

HAVING SUM(A.TRXQTYInBase) + SUM(A.EXTDCOST) <> 0

ORDER BY ITEMNMBR, LOCNCODE

GO

grant exec on GPA_HITB to DYNGRP

-----------------------------------

Spreadsheet looks like this
Community Member Microsoft Employee on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

Yes, I'm familiar with the stored procs and how they work.

However, I understand the HITB and HSS report use different datasets. My question is has anyone used the seeivPrintHistStockStatus report to build an SSRS version of the report? I have one customer who prefers this report to the HITB for cosmetic reasons and I'd like to be able to give them an SSRS version instead of the RW version.

Or are you suggesting that I create a modified version of the HITB report with the look and feel of the HSS?
sandipdjadhav 18,265 on at

Like (0)

Report

RE: Historical Stock Stock Status Stored Procedure

Hi Mike,

Dynamics GP use StoreProcs to print Historical Aged TB for Inventory. Below is storeProcs with parameters:-

StoreProceure Name: seeHITB , Parameter details as below. Let me know how it goes.

exec seeHITB @I_nSortBy=1,@I_nReceiptOptions=1,@I_sStartItemNumber=N'',@I_sEndItemNumber=N'þþþþþþþþþþþþþþþþþþþþþþþþþþþþþþþ',@I_sStartAccountNumber=N' - - ',@I_sEndAccountNumber=N'ÿÿÿ-ÿÿÿÿ-ÿÿ',@I_sStartLocationCode=N'',@I_sEndLocationCode=N'þþþþþþþþþþþ',@I_nStartQTYType=1,@I_nEndQTYType=5,@I_dtStart='1900-01-01 00:00:00',@I_dtEnd='2016-02-04 00:00:00',@sStartClass=N'',@sEndClass=N'þþþþþþþþþþþ',@sStartGenericDesc=N'',@sEndGenericDesc=N'þþþþþþþþþþþ',@I_fUseGLPostDate=0,@I_fIncludeZeroQtyItems=1,@O_SQL_Error_State=NULL