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) / Open AP Transactions -...
Microsoft Dynamics GP (Archived)

Open AP Transactions - INSERT SQL from One Company to another

(0) ShareShare
ReportReport
Posted on by Napolo 756

We are consolidating 4 companies into 1.

July 1, we need to move all OPEN Invoices over to the new company, without affecting General Ledger.

I am testing this now, to see what needs to take place early 7/1.

I have tested using INSERT statement for two tables. PM20000 for the OPEN invoice and PM10100 for the OPEN Invoice DISTRIBUTION.

Our goal is to be able to see the invoice to pay and be able to see the expense account along with old payables clearing account (this may be the issue).

Here is an image of what I have imported. It does not bring in the Purch account (I confirmed it is setup in new company), and the PAY account is grabbing a random account.

As I am writing this up, I am thinking we can't bring over the distributions because we would be cutting checks with the offset of our new AP Control account 100-000-2100-001 rather than the old one 200-000-2100-001.

Since we are not moving over History, Is there any other way to see from our new company, the GL distribution that was entered, that I might be missing?

OPEN-AP-ISSUE.png

*This post is locked for comments

  • Napolo Profile Picture
    Napolo 756 on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    Thank you all.  This makes sense. IM seems to be most logical, I think I am obsessed to see what else SQL can do:)

    Integrate and "Kill" the batch, so it doesn't post to GL. SOLD

    Thank you all.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,788 Moderator on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    I cast my vote against doing it this way. The simpler and more complete way is to use IM to import the transactions and then turn off posting to the ledger. Either way you will not have the zoom feature back from the ledger to the PM transactions but at least this way you have allowed GP to post the transactions so you know all the tables will be updated correctly.

  • Suggested answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    Fully agree with Victoria,

    Your biggest issue here are the account indexes that are used and certainly don't match between the companies.

    We do have over a dozen companies and I've never seen a single GL accounts # match the index # from another one.

    Exporting and using IM to recreate your open invoices is probably your best bet,  as your dealing with real GL numbers.

  • Verified answer
    Victoria Yudin Profile Picture
    Victoria Yudin 22,766 on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    Hi Nicole,

    Just my 2 cents on this.

    Putting transaction information directly into SQL tables is not something to be taken lightly. You would have to check every ID (not just vendor ID, but address ID, payment terms, tax schedules, etc.) for validity. You would need to validate and translate all the account indexes. And there could be many other considerations, depending on the details in your transactions.

    Long story short, it's something I would strongly recommend against for a one time need. There is just too much work involved for the data validation and testing required. Depending on how many open transactions you are bringing over, your best bet may be to either type them in manually or import them using Integration Manager. 

  • Napolo Profile Picture
    Napolo 756 on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    We did evaluate that option. We are not bringing over historical information, so we are doing this manually.

  • Napolo Profile Picture
    Napolo 756 on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    p.s. If we need to VOID an open invoice in our new company, we were hoping to be able to see how it was charged rather than going to the old companies to track down the inv. distributions.

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: Open AP Transactions - INSERT SQL from One Company to another

    Hi,

    There is a tool from Corporate Renaissance Group called Combiner www.crgroup.com/company-combiner that can combine your databases without the time and pain of doing it manually.

    Kind regards,

    Leslie

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,307 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans