Skip to main content
Microsoft Dynamics GP forum
Unanswered

Update Cost Category Accounts for Cost Category ID's

editSubscribe (0) ShareShare
ReportReport
Posted on by UG Leader

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

Attachments
  • Community Member Profile Picture
    Community Member UG Leader 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,730 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 UG Leader 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,730 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 UG Leader on at
    RE: Update Cost Category Accounts for Cost Category ID's

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

Helpful resources

Quick Links

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,080 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,601 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Featured topics

Product updates

Dynamics 365 release plans