Author Archive

Historical Stock Status Script

We been suffering from Historical Stock Status report issues when it comes to customers with big inventory transactions, I am providing a script that recalculate the historical stock status for your items based on the IV10200 and IV10201 tables, it will allow you to estimate your inventories using a historical date:

Historical Stock Sttus Summary
  1. DECLARE @ASOFDATE DATETIME
  2. SET @ASOFDATE = ‘2016-12-31′
  3.  
  4. SELECT
  5. ITEMNMBR AS Item Number,
  6. TRXLOCTN AS Location,
  7. SUM(Quantity) AS Quantity,
  8. SUM(Extended Cost) AS Total Cost
  9.  
  10. FROM
  11.  
  12. (SELECT ITEMNMBR,
  13.  
  14. (dbo.IV10200.QTYRECVD -
  15. ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
  16.         WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
  17.         AND ITEMNMBR = IV10200.ITEMNMBR
  18.         AND DOCDATE <= @ASOFDATE), 0))
  19. AS Quantity,
  20.         UNITCOST *
  21.             (dbo.IV10200.QTYRECVD -
  22.             ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
  23.             WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
  24.             AND ITEMNMBR = IV10200.ITEMNMBR
  25.             AND DOCDATE <= @ASOFDATE), 0))
  26. AS Extended Cost,             
  27. TRXLOCTN
  28. FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
  29. GROUP BY ITEMNMBR, TRXLOCTN

Hope that his helps!


Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read the article:
Historical Stock Status Script

April 22, 2013 ·  · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 594

Dynamics GP 2013 MVP

In April 1st I got a mail from Microsoft telling me that I got a new MVP for 2013! That was a great news to hear but wanted to postpone publishing the announcement before getting the below!

IMAG0304

IMAG0305

IMAG0307

IMAG0306

See more here:
Dynamics GP 2013 MVP

April 16, 2013 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 150

Installing SQL Failover Clustering Without SAN Storage – Windows 2008 R2

It always been hard to test out the failover clustering as it requires SAN Storage device to be connected and requires many servers for testing.

This morning I have seen an interesting article in the community the Microsoft has released “Microsoft iScsi Software Target” free of charge, by using the utility you can create a storage and share it across multiple servers, “fooling” the windows to think that a SAN Storage is available.

Following the above, I been able to install failover clustering for testing over one single machine without any other equipments! I used Hyper-V to create 3 Windows 2008 R2 Servers and 1 Virtual Hard Disk Drive and worked out a fully functioning test environment, below what you need if you like to test this one your machine:

1. Over a test server, install Windows 2008 R2 and activate the Hyper-V.

2.  Create 3 virtual machines, one for SQL Server Node1, and the second for the second failover node, and the third one to act as storage server.

3. Create 1 virtual hard disk to represent your SAN Storage.

When you gather these, click on the following tech-net article that details all what you need:

http://blogs.technet.com/b/mghazai/archive/2009/12/12/hyper-v-guest-clustering-step-by-step-guide.aspx

Hope that this helps.

Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read more here:
Installing SQL Failover Clustering Without SAN Storage – Windows 2008 R2

February 26, 2013 ·  · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 247

LinkedIn

As many of you might notice that Linked In is celebrating their 200,000,000 users and started sending thanks for people who help spreading their name! They have ranked their users per the the viewing of their profile, so you either be one of the top 1% most viewer profiles, 5% or 10%.

I made the 5% List!

Linked In

Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read More:
LinkedIn

February 21, 2013 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 164

How to Track unused Journal Numbers

I got an interesting question from one of the GP users requesting a list of unused Journal Number, therefore I decided to scratch my head and write this query, below will help:

SELECT DISTINCT NUMBER FROM MASTER..spt_values WHERE NUMBER BETWEEN
(SELECT MIN(JRNENTRY) AS FROMNUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
(SELECT MAX(JRNENTRY) AS TONUMBER FROM (SELECT DISTINCT JRNENTRY FROM GL20000) AS JOURNALS)
AND
number NOT IN (SELECT DISTINCT JRNENTRY FROM GL20000)

Happy tracking…!

Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

See original article:
How to Track unused Journal Numbers

February 17, 2013 ·  · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 202

Dynamics GP Excel Paste V 4.0

We have finally released and uploaded a new version for the Excel Paste, this one has the following coverage:

1. The major move was the ability to integrate into multiple products, like combining the GL Transaction Entry with the Analytical Accounting.

2. Ability to Paste into modified forms and additional fields.

3. We have added a link between the defined Products and the actually installed products to avoid annoying errors if you have missed one product in your workstation.

4. Added an option to handle prompts or disable the handling, to avoid any annoying messages during the paste operation.

5. For users who are running behind firewalls, they used to have Slowness during the login to GP, we have fixed this by moving the online validation to be through the webservices first and then direct database connection.

6. We had many “Minor Bugs” that were reported, all been resolved.

Regards,


Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Read the original post:
Dynamics GP Excel Paste V 4.0

January 28, 2013 ·  · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 207

Dynamics GP 2013 Demo Image

Yes it has been released, Microsoft team is doing everything fast in relation to GP 2013! take a look into the following article:

http://blogs.msdn.com/b/gp/archive/2013/01/15/where-s-the-demo-image-here-today.aspx

Regards,

Mohammad R. Daoud MVP – MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 – 79 – 999 65 85
me@mohdaoud.com
http://www.di.jo

Link:
Dynamics GP 2013 Demo Image

January 19, 2013 ·  · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 288