Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

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

2 comments:

  1. Hi Mahmoud.
    Thank you for your above script.
    Appreciate if you could let know if i have 3rd party module like wennsoft for job costing & time track, how can it be included in above script so that we get comprehensive posting account view

    ReplyDelete
  2. I got my already programmed and blanked ATM card to withdraw the maximum of $50,000 daily for a maximum of 20 days. I am so happy about this because i got mine last week and I have used it to get $100,000. Skylink technologies is giving out the card just to help the poor and needy though it is illegal but it is something nice and  they're not like other scam pretending to have the blank ATM cards. And no one gets caught when using the card. get yours today Just send them an email On skylinktechnes@yahoo.com whatsapp: +1(213)785-1553 

    ReplyDelete