web
You’re offline. This is a read only version of the page.
close
Skip to main content

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

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

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,854 Moderator on at

    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

    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,854 Moderator on at

    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

    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

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans