Featured Post

Dynamics GP 2016 R2 is Now Available .. and #MSDynGP will Always be Avilable

The Microsoft Dynamics GP team announced today that Microsoft Dynamics GP 2016 R2 has released . Dynamics GP 2016 R2 continues the grea...

Thursday, May 1, 2014

Which are the Posting Accounts in my GP Company ! - SQL Script

 

Posting accounts can be configured throughout several modules and setup windows in Dynamics GP. Having them all at one report provides more control over the chart of account in your company.

The idea is to gather all the posting accounts configured in different setup windows in Dynamics GP and present them in a single report as shown below;

Accounts Graph

 

The series included in the SQL script are mentioned below, along the originating setup window:

1

 

Posting Accounts SQL Script

aCCOUNTS

Tables Included:

 

·         PM00200 | PM Vendor Master File

·         RM00101 | RM Customer Master

·         IV00101 | Item Master

·         CT00102 | IC Cost Item Master

·         SY01100 | Posting Account Master

·         GL40000 | General Ledger Setup

·         FA41300 | Account Group Setup

·         TX00201 | Sales/Purchase Tax Master

·         IV41100 | Inventory Landed Cost

·         GL00100 | Account Master

·         GL00105 | Account Index Master

 

 

 

 

SELECT  DISTINCT

        SERIES ,

        Account AS AccountIndex ,

        AccountType ,

        ACTNUMST AS AccountNumber ,

        ACTDESCR AS AccountDescription

FROM    ( SELECT  DISTINCT

                    Series ,

                    Account ,

                    AccountType

          FROM      ( SELECT    'Purchasing - VendorCard' AS Series ,

                                PMAPINDX AS APAccount ,

                                PMCSHIDX AS PMCASH ,

                                PMDAVIDX AS DiscountAvailable ,

                                PMDTKIDX AS DiscountTaken ,

                                PMFINIDX AS FinanceCharges ,

                                PMMSCHIX AS MisCharges ,

                                PMTAXIDX AS Tax ,

                                PMWRTIDX AS Writoffs ,

                                PMPRCHIX AS Purchases ,

                                ACPURIDX AS Accrued ,

                                PURPVIDX AS PurchasePrice ,

                                PMFRTIDX AS Freight ,

                                PMTDSCIX AS TradeDiscount

                      FROM      dbo.PM00200

                    ) AS PM UNPIVOT ( Account FOR AccountType

                                                IN ( APAccount,

                                                       PMCASH,

                                                       DiscountAvailable,

                                                       DiscountTaken,

                                                       FinanceCharges,

                                                       MisCharges, Tax,

                                                       Writoffs,

                                                       Purchases,

                                                       Accrued,

                                                       PurchasePrice,

                                                       Freight,

                                                       TradeDiscount ) ) unpvt

          UNION ALL

          SELECT  DISTINCT

                    Series ,

                    Account ,

                    AccountType

          FROM      ( SELECT    'Sales - CustomerCard' AS Series ,

                                RMCSHACC AS RMCASH ,

                                RMARACC AS ARAccount ,

                                RMSLSACC AS SALES ,

                                RMIVACC AS Inventory ,

                                RMCOSACC AS COGS ,

                                RMTAKACC AS DiscountTaken ,

                                RMAVACC AS DiscountAvailable ,

                                RMFCGACC AS FinanceCharges ,

                                RMWRACC AS Writeoffs ,

                                RMSORACC AS SalesReturn ,

                                RMOvrpymtWrtoffAcctIdx OverPayment

                      FROM      dbo.RM00101

                    ) AS RM UNPIVOT ( Account FOR AccountType

                                                IN ( RMCASH,

                                                ARAccount, SALES,

                                                Inventory, COGS,

                                                DiscountTaken,

                                                DiscountAvailable,

                                                FinanceCharges,

                                                Writeoffs,

                                                SalesReturn,

                                                OverPayment ) ) unpvt

          UNION ALL

          SELECT  DISTINCT

                    Series ,

                    Account ,

                    AccountType

                   FROM     

                              ( SELECT    'Inventory - ItemCard' AS Series ,

                                IVAC.ITEMNMBR ,

                                IVIVINDX AS Inventory ,

                                IVIVOFIX AS InventoryOffset ,

                                IVCOGSIX AS COGS ,

                                IVSLSIDX AS Sales ,

                                IVSLDSIX AS SalesDiscount ,

                                IVSLRNIX AS SalesReturn ,

                                IVINUSIX AS InUser ,

                                IVINSVIX AS InService ,

                                IVDMGIDX AS Damaged ,

                                IVVARIDX AS Variance ,

                                DPSHPIDX AS DropShip ,

                                PURPVIDX AS PurchasePriceVariance ,

                                UPPVIDX AS UnrealizedPPvariance ,

                                IVRETIDX AS InventoryReturn ,

                                ASMVRIDX AS AssemblyVariance ,

                                IVSCRVIX AS SalesCostRevalue ,

                                ICIVMFIXI ,

                                ICIVMVIXI ,

                                ICIVLVIXI ,

                                ICIVLABVAROHIX_I ,

                                ICIVLABVAROHVARIX_I ,

                                ICIVMEVIXI ,

                                ICIVMACHEFFVAROHIX_I ,

                                ICIVMACHEFFVAROHVARIX_I ,

                                ICIVMUIXI ,

                                ICIVMATUSAGEVAROHIX_I ,

                                ICIVMATUSAGEVAROHVARIX_I ,

                                ICIVSCIXI ,

                                ICIVWMIXI ,

                                ICIVWMATOHIXI ,

                                ICIVWIPMATOHVARIX_I ,

                                ICIVWLIXI ,

                                ICIVWLOHIXI ,

                                ICIVWIPLABOHVARIX_I ,

                                ICIVWCIXI ,

                                ICIVWMACHOHIXI ,

                                ICIVWIPMACHOHVARIX_I ,

                                ICIVCMIXI ,

                                ICIVCMATOHIXI ,

                                ICIVCOGSMATOHVARIX_I ,

                                ICIVCLIXI ,

                                ICIVCLABOHIXI ,

                                ICIVCOGSLABOHVARIX_I ,

                                ICIVCCIXI ,

                                ICIVCMACHOHIXI ,

                                ICIVCOGSMACHOHVARIX_I ,

                                ICIVILIXI ,

                                ICIVILABOHIXI ,

                                ICIVINVLABOHVARIX_I ,

                                ICIVIMXI ,

                                ICIVIMACHOHIXI ,

                                ICIVINVMACHOHVARIX_I ,

                                ICIVIMATOHIXI ,

                                ICIVINVMATOHVARIX_I

                      FROM      dbo.IV00101 IVAC

                                LEFT OUTER JOIN

                                                       ( SELECT   

                                                       ITEMNMBR ,

                                ICIVMFIXI ,

                                ICIVMVIXI ,

                                ICIVLVIXI ,

                                ICIVLABVAROHIX_I ,

                                ICIVLABVAROHVARIX_I ,

                                ICIVMEVIXI ,

                                ICIVMACHEFFVAROHIX_I ,

                                ICIVMACHEFFVAROHVARIX_I ,

                                ICIVMUIXI ,

                                ICIVMATUSAGEVAROHIX_I ,

                                ICIVMATUSAGEVAROHVARIX_I ,

                                ICIVSCIXI ,

                                ICIVWMIXI ,

                                ICIVWMATOHIXI ,

                                ICIVWIPMATOHVARIX_I ,

                                ICIVWLIXI ,

                                ICIVWLOHIXI ,

                                ICIVWIPLABOHVARIX_I ,

                                ICIVWCIXI ,

                                ICIVWMACHOHIXI ,

                                ICIVWIPMACHOHVARIX_I ,

                                ICIVCMIXI ,

                                ICIVCMATOHIXI ,

                                ICIVCOGSMATOHVARIX_I ,

                                ICIVCLIXI ,

                                ICIVCLABOHIXI ,

                                ICIVCOGSLABOHVARIX_I ,

                                ICIVCCIXI ,

                                ICIVCMACHOHIXI ,

                                ICIVCOGSMACHOHVARIX_I ,

                                ICIVILIXI ,

                                ICIVILABOHIXI ,

                                ICIVINVLABOHVARIX_I ,

                                ICIVIMXI ,

                                ICIVIMACHOHIXI ,

                                ICIVINVMACHOHVARIX_I ,

                                ICIVIMATOHIXI ,

                                ICIVINVMATOHVARIX_I

                        FROM      CT00102

                    ) AS IVCO ON

                                  IVAC.ITEMNMBR = IVCO.ITEMNMBR ) AS IV

                                  UNPIVOT ( Account FOR AccountType

                                  IN

                                  ( Inventory,

                    InventoryOffset,

                    COGS, Sales,

                    SalesDiscount,

                    SalesReturn,

                    InUser,

                    InService,

                    Damaged,

                    Variance,

                    DropShip,

                    PurchasePriceVariance,

                    UnrealizedPPvariance,

                    InventoryReturn,

                    AssemblyVariance,

                    SalesCostRevalue,

                    ICIVMFIXI,

                    ICIVMVIXI,

                    ICIVLVIXI,

                    ICIVLABVAROHIX_I,

                    ICIVLABVAROHVARIX_I,

                    ICIVMEVIXI,

                    ICIVMACHEFFVAROHIX_I,

                    ICIVMACHEFFVAROHVARIX_I,

                    ICIVMUIXI,

                    ICIVMATUSAGEVAROHIX_I,

                    ICIVMATUSAGEVAROHVARIX_I,

                    ICIVSCIXI,

                    ICIVWMIXI,

                    ICIVWMATOHIXI,

                    ICIVWIPMATOHVARIX_I,

                    ICIVWLIXI,

                    ICIVWLOHIXI,

                    ICIVWIPLABOHVARIX_I,

                    ICIVWCIXI,

                    ICIVWMACHOHIXI,

                    ICIVWIPMACHOHVARIX_I,

                    ICIVCMIXI,

                    ICIVCMATOHIXI,

                    ICIVCOGSMATOHVARIX_I,

                    ICIVCLIXI,

                    ICIVCLABOHIXI,

                    ICIVCOGSLABOHVARIX_I,

                    ICIVCCIXI,

                    ICIVCMACHOHIXI,

                    ICIVCOGSMACHOHVARIX_I,

                    ICIVILIXI,

                    ICIVILABOHIXI,

                    ICIVINVLABOHVARIX_I,

                    ICIVIMXI,

                    ICIVIMACHOHIXI,

                    ICIVINVMACHOHVARIX_I,

                    ICIVIMATOHIXI,

                    ICIVINVMATOHVARIX_I ) ) unpvt

          UNION ALL

          SELECT    CASE SERIES

                      WHEN 1 THEN 'ALL - PostingSetup'

                      WHEN 2 THEN 'Financial - PostingSetup'

                      WHEN 3 THEN 'Sales - PostingSetup'

                      WHEN 4 THEN 'Purchasing - PostingSetup'

                      WHEN 5 THEN 'Inventory - PostingSetup'

                      WHEN 7 THEN 'Project - PostingSetup'

                    END AS SERIES ,

                    ACTINDX ,

                    PTGACDSC AS AccountType

          FROM      SY01100

          UNION ALL

          SELECT    'Financial - CheckbookCard' AS Series ,

                    ACTINDX ,

                    'Cash Account' AS AccountType

          FROM      dbo.CM00100

          UNION ALL

          SELECT    'Financial - GLSetup' AS Series ,

                    RERINDX ,

                    'Retained Earnings'

          FROM      dbo.GL40000

          UNION ALL

          SELECT  DISTINCT

                    Series ,

                    Account ,

                    AccountType

          FROM     

                (

                     SELECT   

                     'Fixed Assets - AccountGroup' AS Series ,

                     DEPREXPACCTINDX AS DepreciationExpense ,

                     DEPRRESVACCTINDX AS DepreciationReserve ,

                     PRIORYRDEPRACCTINDX AS PriorYearDepreciation ,

                     ASSETCOSTACCTINDX AS AssetCost ,

                     PROCEEDSACCTINDX AS ProceedsAccount ,

                     RECGAINLOSSACCTINDX AS RecognizedGainANDloss ,

                     NONRECGAINLOSSACCTINDX NonRecognizedGainANDloss ,

                     CLEARINGACCTINDX AS ClearingAccount

                     FROM      dbo.FA41300

                     ) AS RM UNPIVOT ( Account FOR AccountType

                                  IN (    DepreciationExpense,

                                                DepreciationReserve,

                                                PriorYearDepreciation,

                                                AssetCost,

                                                ProceedsAccount,

                                                RecognizedGainANDloss,

                                                NonRecognizedGainANDloss,

                                                ClearingAccount ) ) unpvt

          UNION ALL

          SELECT DISTINCT

                    'Financial - TaxSetup' AS Series ,

                    ACTINDX ,

                    'Sales/Purchase Tax' AS AccountType

          FROM      TX00201

          UNION ALL

          SELECT  DISTINCT

                    Series ,

                    Account ,

                    AccountType

          FROM     

                (

                SELECT    'Inventory - LandedCostSetup' AS Series ,

                    ACPURIDX Accrued ,

                    PURPVIDX PurchasePriceVariance

          FROM      dbo.IV41100

          ) AS RM UNPIVOT ( Account FOR AccountType

                                                IN ( Accrued,

                                 PurchasePriceVariance ) ) unpvt

        ) AS ModuleAccounts

        LEFT OUTER JOIN

              ( SELECT    A.ACTINDX ,

                    B.ACTNUMST ,

                    A.ACTDESCR

            FROM      dbo.GL00100 AS A

                    LEFT OUTER JOIN dbo.GL00105 AS B

                                  ON A.ACTINDX = B.ACTINDX

        ) AS Accounts

              ON ModuleAccounts.ACCOUNT = Accounts.ACTINDX

WHERE   ACCOUNT <> 0 AND

              ACTNUMST IS NOT NULL

ORDER BY SERIES ,AccountType

 

 

 

Best Regards,
Mahmoud M. AlSaadi

No comments:

Post a Comment