Who are my Dynamics GP Power Users !

I have previously been asked to provide a report for Dynamics GP Power Users in several GP companies, here is an SQL scrip which provides the power users in all you GP Companies.

Tables Included:

·         SY01500 | Security Assignment User Role

SELECT  'Power USER' AS SecurityPrivilage ,

        [USERID] AS UserID ,

        [TWO] AS FabrikamDB ,

        [THREE] AS YourCompanyDB

FROM    ( SELECT    [INTERID] ,

                    [USERID] ,

                    [SECURITYROLEID]

          FROM      [DYNAMICS]..SY10500 AS A

                    INNER JOIN [DYNAMICS]..SY01500 AS B ON A.[CMPANYID] = B.[CMPANYID]

          WHERE     [SECURITYROLEID] = 'POWERUSER'

                    AND [INTERID] IN ( 'TWO', 'THREE' )

        ) P PIVOT ( COUNT([SECURITYROLEID]) FOR [INTERID] IN ( [TWO], [THREE] ) ) AS PVT

Explanation :

In order to run the above statement against your live GP Companies, you need to consider the following three steps;

1- On the select statement, add as many columns to reflect the GP Companies, in the example below we have (company two, three, four and five)

SELECT  'Power USER' AS SecurityPrivilage ,

        [USERID] AS UserID ,

        [TWO] AS FabrikamDB ,

        [THREE] AS YourCompanyDB

       [FOUR] AS YourCompanyDB,       

        [FIVE] AS YourCompanyDB

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

2- Secondly, On the where clause, include the company names as “TEXT” values under the[INTERID] field.

WHERE     [SECURITYROLEID] = 'POWERUSER'

         AND [INTERID] IN ( 'TWO', 'THREE', 'FOUR', 'FIVE' )

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

3- Finally, the pivot statement has to be modified to include the company values as shown below;

( COUNT([SECURITYROLEID]) FOR [INTERID] IN ( [TWO], [THREE], [FOUR], [FIVE] ) ) AS PVT

Best Regards,
Mahmoud M. AlSaadi

Fiscal Period Setup – SQL Script

 

In this post, SQL Script for the Fiscal Period Setup is provided, you might just need to publish this through Smart List designer to your Dynamics GP and enjoy inquiring your fiscal periods more quickly. The details are shown below;

 

Tables Included:

·         SY40100 | Period Setup

 

SELECT  Year1 AS Fiscal_Year ,

        [PERNAME] AS Fiscal_Period ,

        CASE WHEN [PSERIES_1] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Financial ,

        CASE WHEN [PSERIES_2] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Sales ,

        CASE WHEN [PSERIES_3] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Purchasing ,

        CASE WHEN [PSERIES_4] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Inventory ,

        CASE WHEN [PSERIES_5] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Payroll ,

        CASE WHEN [PSERIES_6] = 0 THEN 'Yes'

             ELSE 'No'

        END AS Project

FROM    SY40100

 

Best Regards,
Mahmoud M. AlSaadi

General Ledger Journals with Analytical Accounting Details –SQL Script

 

In this post, SQL script for the GL Journal entries with Analytical Accounting details is provided along with all the associated details, as shown below;

 

Tables Included:

·         AAG30000 | Analytical Accounting GL Header

·         AAG30001 | Analytical Accounting GL Distribution

·         AAG30002 | Analytical Accounting GL Assignment

·         AAG30003 | Analytical Accounting GL Code

·         AAG00401 | Analytical Accounting Transaction Dimension

·         GL00100  | Account Master

·         GL00105  | Account Index Master

 

SELECT  YEAR1 ,

        JRNENTRY ,

        B.ACTINDX ,

        H.ACTNUMST ,

        G.ACTDESCR ,

        A.aaTRXType ,

        aaGLTRXSource ,

        aaTRXSource ,

        GLPOSTDT ,

        B.DEBITAMT AS TotalDebit ,

        B.CRDTAMNT AS TotalCredit ,

        C.DEBITAMT AS aaDebit ,

        C.CRDTAMNT AS aaCredit ,

        aaTrxDim ,

        aaTrxDimDescr ,

        aaTrxDimDescr2 ,

        aaTrxDimCode ,

        aaTrxDimCodeDescr ,

        aaTrxDimCodeDescr2

FROM    [AAG30000] AS A

        LEFT OUTER JOIN [AAG30001] AS B ON A.[aaGLHdrID] = B.[aaGLHdrID]

        LEFT OUTER JOIN [AAG30002] AS C ON B.[aaGLHdrID] = C.[aaGLHdrID]

                                           AND B.[aaGLDistID] = C.[aaGLDistID]

        INNER JOIN [AAG30003] AS D ON C.[aaGLAssignID] = D.[aaGLAssignID]

                                      AND C.[aaGLDistID] = D.[aaGLDistID]

                                      AND C.[aaGLHdrID] = D.[aaGLHdrID]

        INNER JOIN [AAG00401] AS E ON D.[aaTrxDimID] = E.[aaTrxDimID]

                                      AND D.[aaTrxCodeID] = E.[aaTrxDimCodeID]

        INNER JOIN [AAG00400] AS F ON E.[aaTrxDimID] = F.[aaTrxDimID]

        LEFT OUTER JOIN dbo.GL00100 AS G ON B.ACTINDX = G.ACTINDX

        LEFT OUTER JOIN dbo.GL00105 AS H ON G.ACTINDX = H.ACTINDX

 

 

Best Regards,
Mahmoud M. AlSaadi

Reconciling Quantity on Hand – SQL Script

 

In a previous post, purchase receipt layers work and details SQL scrip was provided as part of the inventory reconciliation process; Inventory Reconciliation - Purchase Receipt Work and Details. This is a prerequisite for the next step illustrated below.

In this post, SQL script for the correct quantity on hand is provided to reconcile transaction balance versus master balance, as shown below;

Helping Note !

• Transaction Balance: as calculated from purchase receipt layers against purchase receipt layer details (in versus out transactions)
• Master Balance: as retrieved from quantity master table and displayed on item quantities inquiry.

 

Tables Included:

·         IV00102 | Item Quantity Master

·         IV10200 | Purchase Receipt Layers

 

SELECT  TRX_BALANCE.ITEMNMBR AS ItemNumber ,

        TRXLOCTN AS Location ,

        BALANCE AS TRX_BALNACE ,

        QTYONHND AS Master_Balnace ,

        ATYALLOC AS Master_AllocatedQuantity ,

        AvailableQuantity ,

        BALANCE - QTYONHND AS Variance

FROM    ( SELECT    ITEMNMBR ,

                    TRXLOCTN ,

                    SUM(QTYRECVD) - SUM(QTYSOLD) AS BALANCE

          FROM      dbo.IV10200

          GROUP BY  ITEMNMBR ,

                    TRXLOCTN

        ) AS TRX_BALANCE

        LEFT OUTER JOIN ( SELECT    ITEMNMBR ,

                                    LOCNCODE ,

                                    QTYONHND ,

                                    ATYALLOC ,

                                    QTYONHND - ATYALLOC AS AvailableQuantity

                          FROM      dbo.IV00102

                          WHERE     RCRDTYPE = 2

                        ) AS Master_Balance ON TRX_BALANCE.ITEMNMBR = Master_Balance.ITEMNMBR

                                               AND TRX_BALANCE.TRXLOCTN = Master_Balance.LOCNCODE

WHERE   BALANCE - QTYONHND <> 0

 

 

Best Regards,
Mahmoud M. AlSaadi