Open Forum

Expand all | Collapse all

Delete or deactivate Cost Categories in Project Accounting

  • 1.  Delete or deactivate Cost Categories in Project Accounting

    Posted Mar 12, 2019 09:15 AM
    We are going through a clean-up of our projects, and I am trying to find a way to add new cost categories to existing projects and deactivate any incorrect categories.

    If needed we will update budgets if that gets in our way.

    Anyone know of a best practice for this?

    Thanks

    ------------------------------
    Tom Repetti
    Manager Core IT Services
    Comport Technology Solutions
    Ramsey NJ
    ------------------------------


  • 2.  RE: Delete or deactivate Cost Categories in Project Accounting

    GPUG ALL STAR
    Posted Mar 12, 2019 11:41 AM
    Hi @Tom Repetti,
    De-activating existing Cost Categories in PA isn't really a big deal as it can be done thru a SQL Script fairly easily. ​

    The script bellow with return all CC's from a Project that are not in the status 3, 4 or 5 (see status label in script and sorry for the bad formatting) :

    SELECT PA01101.CustNmbr ,
    PA01101.[PAcontname],
    PA01101.PAcontid ,
    PA01101.PAcontclassid,
    CASE PA01101.PASTAT --> Contract level
    WHEN 1 THEN 'Open'
    WHEN 2 THEN 'On Hold'
    WHEN 3 THEN 'Closed'
    WHEN 4 THEN 'Estimate'
    WHEN 5 THEN 'Completed'
    END AS 'Contract_Status' ,
    PA01201.PAprojid ,
    PA01201.[PAprojname],
    CASE PA01201.PASTAT --> Project level
    WHEN 1 THEN 'Open'
    WHEN 2 THEN 'On Hold'
    WHEN 3 THEN 'Closed'
    WHEN 4 THEN 'Estimate'
    WHEN 5 THEN 'Completed'
    END AS 'Project_Status' ,
    PA01301.PACOSTCATID ,
    CASE PA01301.PASTAT --> Budget/CC level
    WHEN 1 THEN 'Open'
    WHEN 2 THEN 'On Hold'
    WHEN 3 THEN 'Closed'
    WHEN 4 THEN 'Estimate'
    WHEN 5 THEN 'Completed'
    ELSE 'Undefined'
    END AS 'Budget-CC_Status' ,
    CASE PA01301.PATU
    WHEN 1 THEN 'Timesheets'
    WHEN 2 THEN 'Equipment Logs'
    WHEN 3 THEN 'Miscellaneous Logs'
    WHEN 4 THEN 'Purchases/Material'
    WHEN 5 THEN 'Employee Expenses'
    ELSE 'Undefined'
    END AS 'PA Trans Usage',
    PA01301.[PAACTUALBEGDATE],
    PA01301.[PA_Actual_End_Date]
    FROM
    PA01201 INNER JOIN PA01301
    ON PA01201.PAPROJNUMBER = PA01301.PAPROJNUMBER
    INNER JOIN PA01101
    ON PA01201.PACONTNUMBER = PA01101.PACONTNUMBER
    WHERE PA01201.PASTAT NOT IN (3,5)
    AND PA01301.PASTAT NOT IN (3,4,5)
    --AND PA01301.PATU = 5
    ORDER BY PA01201.CUSTNMBR , PA01201.PACONTNUMBER , PA01301.PAPROJNUMBER , PA01301.PACOSTCATID;

    With that you can easily update the PA01301.PASTAT field to 'Estimate', 'On Hold' or 'Complete' (never use Closed, as this implies other processes in GP).
    Adding new CC's and updating the budgets is a 2-step process for which I used most of the time SmartConnect or GP Macro's, the later being more work to prepare, but remains the free option.
    I say 2 steps, because eConnect I believe doesn't let you put the budget within the same task as opening a new CC, but I might be wrong, you have to check it out. For sure I remember that you can't open the CC within the same process, as every new CC created gets by default 'Estimate' status, and than you have to roll a post-map script to open the CC. You cannot upload a budget when a CC is in any other status than 'Estimate'.
    That's as far as I recall from Project opening processes.. I've not used it in a while as not many customers use Project Accounting in GP.
    Hope that helps to get you started.

    ------------------------------
    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
    ------------------------------



  • 3.  RE: Delete or deactivate Cost Categories in Project Accounting

    TOP CONTRIBUTOR
    Posted Mar 13, 2019 08:49 AM
    Hi @Tom Repetti

    It's always super easy to use SQL to turn things on and off, and I'm a big fan of it.

    However, I am feeling concerned over the "plan" you have below - and I may just not be privy to all of the details, but here is my concern and suggestion:

    If you have many active projects then they have ALREADY had transactions use those cost codes and you will lose a significant amount of reporting and analysis - unless you write specific reporting to pull in both cost codes on one line - if you remove one cost code on a project and replace it with another.

    After that you will now have two different cost codes with history on the same project - confusing to say the least, and difficult reporting and analysis and you could easily break some aspects of how your Forecast and Budget are operating for existing budgets which will look at transactions that already exist to determine not only percent complete, but your revenue recognition as well.

    You'll want to think this through thoroughly.

    You have a couple of options, create the new cost codes and use those on new projects going forward and hopefully the old projects will close out and then you can deactivate those cost codes when the projects that have them get closed

    OR you can go through the above process but add to that the tedious process of changing all of the cost codes throughout the tables to the cost codes you are changing to - this opens things up to a lot of possible consequences, and if you do it, please remember to rebuild and recreate histories.

    This is not a simple undertaking and I believe you should plan carefully.


    ------------------------------
    Jo deRuiter, MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    770-906-4504 (Cell)

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



  • 4.  RE: Delete or deactivate Cost Categories in Project Accounting

    GPUG ALL STAR
    Posted Mar 13, 2019 10:18 AM
    @Tom Repetti,
    Agree with @Jo deRuiter that it can quickly become a mess if you try to get around old CC's and create new ones just because you don't like the way the old ones were named at the time they were created.
    No problem in disabling CC's that have no transactions, but she is right that it might well throw out your PA reporting if you don't plan carefully.
    I've gone thru that exercise to reduce over 450 cost categories in one single GP company to eliminate everything that wasn't relevant anymore.
    Sometimes we just had to 'rename' some of those CC's to make them more meaningful to grab for the employees that were filling TS or ​​EE reports.

    I've been using some PA Changer SQL scripts for several years and they are very handy when it comes to rename Projects, Contracts or Cost Categories in GP. Here is a link to a forum post which points tothe scripts :  https://community.dynamics.com/gp/f/32/t/200661

    If you feel unsecure with using the SQL scripts (to TEST before using in PROD of course), the company CRG has a tool called 'Changer' that would allow to rename Project & Contracts (tough I'm not sure it's capable of renaming CC's). https://crgroup.com/changer/

    ------------------------------
    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
    ------------------------------



  • 5.  RE: Delete or deactivate Cost Categories in Project Accounting

    Posted Mar 13, 2019 03:13 PM
    Thanks everyone.  To clarify, I want to leave the old CCs in the budget, but I want to see if I can prevent them from being used once the correct ones are imported.

    I will be using SmartConnect so I expect that the business rules within GP will be adhered to.

    I see that there is a PASTAT field in PA1301, so does anyone know if I can mark the old CCs as 'Estimate' even if they have transactions posted against them?  How about 'Completed'?

    We are using a 3rd party expense system, and I extract and upload our Customer/Project/Cost Category list to it.  I could easily modify mt extract script to ignore a PASTAT of 2, 4 or 5.  I will heed the advice NOT to use type 5 'Closed'

    So the key question is changing the status of an existing cost category that has transactions against it.

    ------------------------------
    Tom Repetti
    Manager Core IT Services
    Comport Technology Solutions
    Ramsey NJ
    ------------------------------



  • 6.  RE: Delete or deactivate Cost Categories in Project Accounting

    GPUG ALL STAR
    Posted Mar 13, 2019 03:38 PM
    Hi @Tom Repetti
    That was exactly what my 1st post was all about.. scroll up and copy the SQL script to check with your own data. you can update the status of the CC's to set them as 'Estimate' and the users shouldn't be able to pull in the CC anymore.​

    ------------------------------
    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
    ------------------------------



  • 7.  RE: Delete or deactivate Cost Categories in Project Accounting

    Posted Mar 13, 2019 03:42 PM
    Thanks @Beat Bucher, I already have the offending CCs identified.  I am waiting for my DEV environment to be brought back on line to test.

    Appreciate the help​

    ------------------------------
    Tom Repetti
    Manager Core IT Services
    Comport Technology Solutions
    Ramsey NJ
    ------------------------------



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