Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Update Cost Category A...
Microsoft Dynamics GP (Archived)

Update Cost Category Accounts for Cost Category ID's

(0) ShareShare
ReportReport
Posted on by Community Member

I have a large list of over 1400 Cost Category ID's that I need to change the fifth segment of the revenue account.

I was wondering if this is feasible through SQL or possibly PSTL.

I have a Cost Category ID that is associated with a  revenue account 00-00000-00-0000-52000 and I need to change that fifth segment to 42000.

Within sql I am not seeing how to associate GL00100/105 with the cost Category ID to filter by.

Any expert advice or best practices for this task?

Thank you

Mark

*This post is locked for comments

  • Community Member Profile Picture
    Community Member on at
    RE: Update Cost Category Accounts for Cost Category ID's

    One of our consultants used PA table and Accttype = sales / revenue (7)

    Thought is was pretty good and very efficient, I love working with smart people.

    I wanted to share the script in case someone else might need a hand down the road.

    How does this look to you Richard?

    begin tran

    update a

    set a.paactindx = newgl.actindx

    from PA43001 a

    left outer join gl00105 gl on a.paactindx = gl.actindx

    left outer join gl00105 newgl on gl.ACTNUMBR_1 = newgl.ACTNUMBR_1 and gl.ACTNUMBR_2 = newgl.ACTNUMBR_2 and gl.ACTNUMBR_3 = newgl.ACTNUMBR_3 and gl.ACTNUMBR_4 = newgl.ACTNUMBR_4 and newgl.ACTNUMBR_5 = '42000'

    where PAaccttype = 7

    and gl.ACTNUMBR_5 = '52000'

    commit

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: Update Cost Category Accounts for Cost Category ID's

    You can run the Accounts SmartList and it will show you the account category assigned to each GL account.

    SELECT * FROM GL00100 will show it to you as well but only as as integer. The PSTL does not allow you to filter by account category. So I suppose you can run your SmartList first, filter by account category and then use PSTL to perform the desired account number change.

  • Community Member Profile Picture
    Community Member on at
    RE: Update Cost Category Accounts for Cost Category ID's

    Thank you Richard, PSTL sounds like the best method.

    Quick question: I need to select only specific COSTCATEGORYID's to update that specific segment.

    What is the best way to filter our only the cost category ID's and see the associated GL's associated with each COSTCATEGORYID?

    I need to get the DATA clean before I can update OLD GL to NEW GL using PSTL.

    Thank you Sir!

    Mark

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: Update Cost Category Accounts for Cost Category ID's

    The GL00100.ACCATNUM holds the account category number. It sounds like you need to use PSTL tool to change 00-00000-00-0000-52000 to 00-00000-00-0000-42000. The value for ACCATNUM will simply go along for the ride. All you need to do is change the GL account number. There is a way in SQL to accomplish this but this will entail several steps. Using PSTL is the way to go here.

  • Community Member Profile Picture
    Community Member on at
    RE: Update Cost Category Accounts for Cost Category ID's

    I forgot to mention that we are running mem as well.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Jonas ”Jones” Melgaard – Community Spotlight

We are honored to recognize Jonas "Jones" Melgaard as our April 2025…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 294,430 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 233,043 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,158 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans