Open Forum

Expand all | Collapse all

GL00200 vs GL00201

  • 1.  GL00200 vs GL00201

    SILVER CONTRIBUTOR
    Posted 25 days ago
    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 24 days ago
    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 24 days ago
    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

    SILVER CONTRIBUTOR
    Posted 23 days ago
    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
    ------------------------------