Open Forum

Expand all | Collapse all

GL00200 vs GL00201

  • 1.  GL00200 vs GL00201

    GOLD CONTRIBUTOR
    Posted Jan 23, 2019 12:49 PM
    When looking at these two tables for by "BUDGET2019" budget ID I am seeing a YEAR1 = 2019 in the gl00200 table, but in gl00201 i'm seeing 2018 as the year.  What would've caused this and what's the best way to fix it?

    I was thinking of running  a sql script on the gl00201 table to adjust the date, but would like to know if there's a better way.

    ------------------------------
    Andy Berntson
    Fargo Public Schools
    Fargo ND
    ------------------------------


  • 2.  RE: GL00200 vs GL00201

    TOP CONTRIBUTOR
    Posted Jan 24, 2019 09:17 AM
    Hi @Andy Berntson

    You can get a good back up of the database and the run Check Links on the Budget Master..



    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)

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



  • 3.  RE: GL00200 vs GL00201

    GPUG ALL STAR
    Posted Jan 24, 2019 12:43 PM
    Hi @Andy Berntson,
    I've been using the script below to check the budget numbers in GL00200 & GL00201.. The 1st commented line checks on how many budgets you may have carrying the same name in GP (we used to refresh a budget 4 times a year: original & then revised OL 1-3)

    ​-- Figure out current budget in GP
    --select top 10 * from GL00200 where BUDGETID like '2016%' --> use this check how many budgets you may have for the same FY
    DECLARE
    @BUDGETID AS varchar( 15
    );
    SET @BUDGETID = '2016 BUDGET';
    SELECT GL00201.BUDGETID ,
    GL00201.ACTNUMBR_1 AS 'MAIN' ,
    GL00201.ACTNUMBR_2 AS 'REG' ,
    GL00201.ACTNUMBR_3 AS 'DEPT' ,
    GL00201.ACTNUMBR_4 AS 'PROD' ,
    GL00201.PERIODID AS 'MONTH' ,
    GL00201.BUDGETAMT ,
    GL00201.ACTINDX ,
    GL00201.YEAR1 ,
    GL00100.ACTINDX ,
    GL00100.ACTDESCR ,
    GL00102.ACCATNUM ,
    GL00105.ACTNUMST ,
    BAMT.AMOUNT AS Annual_Budget
    FROM
    GL00201 LEFT JOIN(
    SELECT ACTINDX ,
    SUM( BUDGETAMT
    )AMOUNT
    FROM GL00201
    WHERE BUDGETID IN( @BUDGETID
    )
    GROUP BY ACTINDX
    )BAMT
    ON BAMT.ACTINDX = GL00201.ACTINDX
    LEFT JOIN GL00100
    ON GL00201.ACTINDX = GL00100.ACTINDX
    LEFT JOIN GL00102
    ON GL00100.ACCATNUM = GL00102.ACCATNUM
    LEFT JOIN GL00105
    ON GL00201.ACTINDX = GL00105.ACTINDX
    WHERE GL00201.YEAR1 >= 2015
    AND GL00201.BUDGETID IN ( @BUDGETID )
    --AND GL00201.ACTNUMBR_3 = 350 --> uncomment this line if interested in specific GL segment values
    ORDER by ACTNUMST, MONTH;

    Also you should check your Fiscal period tables to see if they are setup properly.

    select * from SY40101 where YEAR1 = 2016
    select * from SY40100 where YEAR1 = 2016

    It could be that your FY calendar year might be off from calendar CY, as it might happen after FY change in the closing dates.


    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------



  • 4.  RE: GL00200 vs GL00201

    GOLD CONTRIBUTOR
    Posted Jan 25, 2019 09:00 AM
    We ended up reloading the budget that had been previously loaded and that did the trick.  The original upload took place they FY18 was still open, so i believe that's what ultimately caused the issue.  Thanks for the input guys!!!

    ------------------------------
    Andy Berntson
    Fargo Public Schools
    Fargo ND
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role