Dynamics GP Data Level Security

 

I am getting requests about this subject almost from all my customers, therefore we at Dynamics Innovations have decided to develop an add-on for Dynamics GP to fulfill this request a month ago and my development team is currently in the process of developing the final touches on the utility.

The add-on is basically works on creating new Dynamic lookup that replaces traditional Dynamics GP lookups, the same lookup is used for all kind of data inside Dynamics GP and the rows are filter dynamically at runtime based on user privileges which to be setup by creating a specific view for each user/role per each business entity and the system will display only data from the linked view:

image

By achieving this, the system administrator will be able to customize columns names and columns to be displayed by using SQL views or direct SQL queries and rows to be displayed, and the lookup will automatically filter rows using “like/contains” operator based on the user selection and based on the clicked column:

image

The data load were enhanced to load data into .Net data grid that pulls rows from database directly into the user interface, 250,000 records were pulled within less than 7 seconds:

image

In addition to the lookup functionality we have developed a routine that validates user inputs inside GP texts and will make sure that the user selected data is a part of the user query:

image 

The development team is currently working to enhance the navigation buttons as it might violates our security setup.

We been able to automate all the above processes and hanged with a couple of issues, the first is reports and the other is the smart list, and been able to handle smart list security by linking users view with smart list views using smart list builder, so each user will have his smart list configured, not an easy task but doable.

The last part is our challenge for the time being, we cannot figure out a solution for filtering report writer reports using available algorithms, we tried to pass restrictions fields to the reports using VBA but this wasn’t a sustainable solution, we still didn’t give up but we might not be able have this done.


Regards,

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

What is ERP?

Guys,

For those who attended my session last week at Applied Science University, as promised kindly find below materials I presented during the session:

http://di.jo/presentations/What is ERP.pdf

http://di.jo/presentations/Microsoft Dynamics GP 2010 Modules.pdf


Regards,

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

Inventory Reset – Average Item receipt QTY on hand does not equal IV QTY on hand.

 

As a part of the inventory reset utility you might get an issue with “Run Data Checks” step, that the Average Item Receipt Quantity On Hand (Table IV10200) does not equal Inventory Quantity On Hand (Table IV00102), this is really confusing specially after doing a full inventory reconcile as a part of the inventory reset process.

While investigating this I found indeed that Quantity at IV00102 does not match the IV10200 quantity and the Inventory Reconcile Process does not cover this part, where I had to find an alternative method to proceed.

On of the community users did face this issue and posted this question on one of the community portal and got an answer from WAQAS who went through the stored procedures of the reset tool and extracted a query that detects variances between IV10200 and IV00102, I have used this query to create the below cursor to collect and fix differences automatically:

DECLARE @ITEMNUMBER VARCHAR(500)
DECLARE @DEXROWID BIGINT
 
DECLARE QTYFIX CURSOR FOR
SELECT 
A.DEX_ROW_ID,
A.ITEMNMBR 
FROM IV10200 A 
INNER JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR
INNER JOIN IV00102 C ON A.ITEMNMBR = C.ITEMNMBR
WHERE B.VCTNMTHD = 3 AND A.QTYTYPE = 1 AND C.LOCNCODE = '' 
AND A.QTYONHND <> C.QTYONHND
AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR 
AND VCTNMTHD <> 0
GROUP BY ITEMNMBR HAVING MAX(DATERECD) = A.DATERECD)
AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR =B.ITEMNMBR 
AND DATERECD = A.DATERECD AND VCTNMTHD <> 0
GROUP BY ITEMNMBR, DATERECD HAVING MAX(RCTSEQNM) = A.RCTSEQNM)
AND EXISTS (SELECT 1 FROM IV10200
WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR AND DATERECD = A.DATERECD 
AND RCTSEQNM = A.RCTSEQNM 
AND VCTNMTHD <> 0
GROUP BY ITEMNMBR, DATERECD, RCTSEQNM HAVING MAX(DEX_ROW_ID) = A.DEX_ROW_ID)
 
OPEN QTYFIX
FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER
WHILE @@FETCH_STATUS = 0 
BEGIN
UPDATE IV10200 SET QTYONHND=(SELECT QTYONHND FROM IV00102 WHERE ITEMNMBR=@ITEMNUMBER AND RCRDTYPE='1') WHERE DEX_ROW_ID= @DEXROWID
FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER
END
CLOSE QTYFIX
DEALLOCATE QTYFIX

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Enjoy!

Regards,

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

There is no Web named /BP/Administration, The language is not supported on the server

 

When you install business portal, you might face the below error:

Feature Id: BP Home
Location: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\BPHome
Action: Install
Exception: The language is not supported on the server.
Stack Trace: at Microsoft.SharePoint.Library.SPRequest.CreateWeb(String bstrUrl, String bstrTitle, String bstrDescription, UInt32 nLCID, String bstrWebTemplate, Boolean bCreateUniqueWeb, Boolean bConvertIfThere, Guid& pgWebId, Guid& pgRootFolderId, Boolean bCreateSystemCatalogs)
at Microsoft.SharePoint.SPSite.CreateWeb(String strUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere, Guid webId, Guid rootFolderId, Boolean createSystemCatalogs)
at Microsoft.SharePoint.SPSite.SPWebCollectionProvider.CreateWeb(String strWebUrl, String strTitle, String strDescription, UInt32 nLCID, String strWebTemplate, Boolean bCreateUniqueSubweb, Boolean bConvertIfThere)

Your issue is basically related to your server language, just go and change the language on the server to English / United States and enjoy!

Regards,

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

Receivables Management Return Account

A customer wanted to have several “Returns” accounts based on customer classification, and uses “Receivables Management Returns” not SOP Returns, they tried to change “Sales Order Returns” account in Customer Card but unfortunately this did not affect the Receivables Distribution:

image

Receivables Returns reads only from Posting Accounts form:

image

This is annoying!! I published this on Connect and wants your votes!

Regards,

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

Who’s editing this transaction?

 

Dynamics GP manages transactions locking by saving the “ID” of the transaction’s being edited in the SQL (SY00800, SQL00801 and DEX_LOCK in tempdb) to avoid any deadlocks in the database.

However, when a user tries to open one of the locked transactions, it will be notified that the transaction is currently locked by another user:

Michael D Johnson posted a solution for this. Now, you can download the free Record Lock Tracing add-on for Dynamics GP (Tested on v10 and GP2010) that will replace this generic message with a message that includes the specific user that has the record locked:

Well done Michael!

Regards,

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

Add Purchase Order Line Account Number to PO Workflow

My customer requested to setup the workflow step based on the account (expense or inventory) assigned to the line items, this account is loaded from the e-requisition module when generating the purchase order and it represents the cost center of the requested item.

They wanted to have this for their projects which is currently managed using an account segment, unfortunately the workflow does not integrates the line account number where I had to find another alternative solution.

image

One of the fields I found in the workflow was “Requested By” which was unused by most of users nor was used by e-requisition module, so I created a trigger to copy account number from the original field to the requested by field and created the workflow on the requested by field.

Below the trigger I used:

— =============================================
— Author:        Mohammad R. Daoud
— Create date: 24-02-2011
— Description:    Trigger to copy Account Number to
— requested by field in PO Details
— =============================================
CREATE TRIGGER POP10110RequestedBy
   ON  POP10110
   AFTER INSERT
AS
BEGIN
DECLARE @PONUMBER    VARCHAR(500)
DECLARE @ORD        BIGINT
DECLARE @INVINDX    BIGINT
DECLARE @REQSTDBY    VARCHAR(5000)

SELECT @PONUMBER = PONUMBER, @ORD = ORD, @INVINDX = INVINDX FROM INSERTED

SELECT @REQSTDBY = LEFT(LTRIM(RTRIM(ACTNUMST, 20))) FROM GL00105 WHERE ACTINDX = @INVINDX

UPDATE POP10110 SET REQSTDBY = @REQSTDBY WHERE PONUMBER = @PONUMBER AND ORD = @ORD

END
GO

Regards,

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