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

Analytical Accounting GL Accounts and Dimensions Assignment Details –SQL View

 

 

In this post, a view for the GL accounts assigned to dimensions are provided along with all the associated details, as shown below;

 

Tables Included;

• AAG00201 | AA Account Class Master
• AAG00202 | AA Account Class Dimension
• AAG00200L|AA Account Class Link
• AAG00400 | AA Transaction Dimension Master
• AAG00401 | AA Transaction Dimension Code Setup
• GL00105  | Account Index Master
• GL00100  | Account Master

 

SELECT  aaAcctClassID ,

        aaAccountClass ,

        aaTrxDimID ,

        ACTINDX ,

        ACTNUMST ,

        Master_TB ,

        aaTrxDim ,

        aaTrxDimCode

FROM    ( SELECT    A.aaAcctClassID ,

                    A.aaAccountClass ,

                    B.aaTrxDimID ,

                    C.ACTINDX ,

                    D.ACTNUMST ,

                    CASE F.TPCLBLNC

                      WHEN 0 THEN 'Debit'

                      WHEN 1 THEN 'Credit'

                      ELSE ' '

                    END AS Master_TB ,

                    E.aaTrxDim ,

                    E.aaTrxDimCode

          FROM      AAG00201 AS A

                    INNER JOIN AAG00202 AS B ON A.aaAcctClassID = B.aaAcctClassID

                    LEFT OUTER JOIN AAG00200L AS C ON B.aaAcctClassID = C.aaAcctClassID

                    LEFT OUTER JOIN GL00105 AS D ON C.ACTINDX = D.ACTINDX

                    LEFT OUTER JOIN GL00100 AS F ON D.ACTINDX = F.ACTINDX

                    LEFT OUTER JOIN ( SELECT    A.aaTrxDimID ,

                                                A.aaTrxDim ,

                                                B.aaTrxDimCode

                                      FROM      AAG00400 AS A

                                                INNER JOIN AAG00401 AS B ON A.aaTrxDimID = B.aaTrxDimID

                                    ) AS E ON B.aaTrxDimID = E.aaTrxDimID

        ) AS AA

 

 

Best Regards,
Mahmoud M. AlSaadi