Open Forum

Expand all | Collapse all

GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

  • 1.  GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    Posted 20 days ago
    Hello All,

    I am using eConnect to load invoices from another application into GP with MEM.  I'm populating Distribution Line for the PURCH DistType and allowing GP to calculate the rest of the distribution for Freight and Discount.  What I'm seeing is that when GP does the calculation it adds my PURCH lines together and creates another one with the total amount to our 1400 line.  Can anyone provide some insight on why this would be happening?  It's like the calculation ignores the distribution lines I load.  Below is a screenshot of how the distribution looks in GP.  The yellow line is the additional 1400 that throws it OOB.  Also included is the XML I'm sending.  Any help would be greatly appreciated.  I'm learning eConnect on the fly and have made a lot of progress.  This issue has me stumped.





    ------------------------------
    Brenda Rogers
    Financial Systems Business Applications Analyst
    Falls of Neuse Management, LLC
    Raleigh NC
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    TOP CONTRIBUTOR
    Posted 20 days ago
    Hi Brenda,

    With eConnect, you either allow it to create the distributions or you must supply ALL distributions. Can you supply all distributions? Are you using Integration Manager, SmartConnect or some other tool?


    ------------------------------
    Mark Wiley
    Dynamics Credentialed Professional
    Senior Solutions Architect
    GraVoc
    Ohio Office
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    Posted 20 days ago
    Thanks Mark

    I was afraid that was going to be the answer.  We are using Dell Boomi to pull PO and Invoice records into a Flow process where our Payables Processors can match them.  Then we have a Boomi integration that uses eConnect to import those invoices into GP.  I'm working on the integration piece.  I was hoping someone had a fancy way that could delete the extra distribution line after it's created.  I found a couple threads in my Google searching where this was done for SOP line items but nothing for Purchasing distribution lines.

    As far as whether or not I can calculate all the distribution lines... I think so if I have to.  The discount calc can be a bit complicated but I feel if GP can do it then I can possibly figure out how that's done and replicate it in Boomi using SQL Queries to hit the Vendor and Payment Terms tables.  My concern is that my calculation could be off by .001 and I would still have an unbalanced entry.  However, if it only happens occasionally then maybe that's ok and we can manually correct those.

    ------------------------------
    Brenda Rogers
    Financial Systems Business Applications Analyst
    Falls of Neuse Management, LLC
    Raleigh NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    SILVER CONTRIBUTOR
    Posted 20 days ago

    I echo what @Mark Wiley says - I believe there is a flag you need to pass to include distributions or not, but it is an all or nothing transaction.

    When dealing with custom distributions, I find it easier to let GP do it's own calculations, then use an eConnect post procedure to handle manually updating the special cases. This sounds like it may be much easier for you to do than to replicate the entire distribution from it's source.

    if you're new to eConnect - it allows you to update existing stored procedures that fire either before your transaction (pre), or after your transaction (post).  you can find them in the GP company database, and are named ta<transaction name>POST i.e. taPOPRcptHdrInsertPost.  The OOB versions are just stubs, but you can add on whatever business logic you need to, and can pass a few variables from your transaction XML to the stored procedure. They fire synchronously with each of your XML transactions. The trick when using these is to remember to re-create them after you run any upgrades on GP as the upgrade process overwrites any customized eConnect stored procedures.



    ------------------------------
    Jeff Woodard
    Chief Technical Officer
    Transportation Financial Services, Inc.
    West Palm Beach FL
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    Posted 20 days ago
    Thanks Jeff

    Yes, I am new to eConnect and learning it "by the seat of my pants" as they say.  I did see the pre and post procedures in SSMS while I was poking around.  I'll try my hand and utilizing that to delete the extra distribution line.  Thanks for the advice.  This seems like the approach I need to take.

    ------------------------------
    Brenda Rogers
    Financial Systems Business Applications Analyst
    Falls of Neuse Management, LLC
    Raleigh NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    Posted 20 days ago
    Edited by Brenda Rogers 20 days ago
    For those who may run across my post, here is the solution I have come up with based on the feedback everyone has provided.

    In Boomi, I added an incremental line function to populate the DSTSQNUM starting from the number 1.  The sequence number in GP typically starts around 16000 so I have no fear that my numbers will run into the GP calculated numbers.  Here's what my XML looks like.

    <?xml version='1.0' encoding='UTF-8'?>
    <eConnect>
    <PMTransactionType>
    <taPMDistribution_Items>
    <taPMDistribution>
    <DOCTYPE>1</DOCTYPE>
    <VCHRNMBR>100000002465274</VCHRNMBR>
    <VENDORID>013170</VENDORID>
    <DSTSQNUM>1</DSTSQNUM>
    <DISTTYPE>6</DISTTYPE>
    <DistRef>Boomi</DistRef>
    <ACTNUMST>2722-1400</ACTNUMST>
    <DEBITAMT>285.01</DEBITAMT>
    <USRDEFND1>2722</USRDEFND1>
    </taPMDistribution>
    <taPMDistribution>
    <DOCTYPE>1</DOCTYPE>
    <VCHRNMBR>100000002465274</VCHRNMBR>
    <VENDORID>013170</VENDORID>
    <DSTSQNUM>2</DSTSQNUM>
    <DISTTYPE>6</DISTTYPE>
    <DistRef>Boomi</DistRef>
    <ACTNUMST>2722-1401</ACTNUMST>
    <DEBITAMT>8.08</DEBITAMT>
    <USRDEFND1>2722</USRDEFND1>
    </taPMDistribution>
    <taPMDistribution>
    <DOCTYPE>1</DOCTYPE>
    <VCHRNMBR>100000002465274</VCHRNMBR>
    <VENDORID>013170</VENDORID>
    <DSTSQNUM>3</DSTSQNUM>
    <DISTTYPE>6</DISTTYPE>
    <DistRef>Boomi</DistRef>
    <ACTNUMST>2722-1404</ACTNUMST>
    <DEBITAMT>239.36</DEBITAMT>
    <USRDEFND1>2722</USRDEFND1>
    </taPMDistribution>
    </taPMDistribution_Items>
    <taPMTransactionInsert>
    <BACHNUMB>B-BR_2722</BACHNUMB>
    <VCHNUMWK>100000002465274</VCHNUMWK>
    <VENDORID>013170</VENDORID>
    <DOCNUMBR>100100</DOCNUMBR>
    <DOCTYPE>1</DOCTYPE>
    <DOCAMNT>553.34</DOCAMNT>
    <DOCDATE>08/16/2021</DOCDATE>
    <PRCHAMNT>532.45</PRCHAMNT>
    <CHRGAMNT>553.34</CHRGAMNT>
    <CASHAMNT>0</CASHAMNT>
    <CHEKAMNT>0</CHEKAMNT>
    <CRCRDAMT>0</CRCRDAMT>
    <TRDISAMT>0</TRDISAMT>
    <TAXAMNT>0</TAXAMNT>
    <FRTAMNT>20.89</FRTAMNT>
    <MSCCHAMT>0</MSCCHAMT>
    <PORDNMBR>383786</PORDNMBR>
    <DISTKNAM>0</DISTKNAM>
    <USRDEFND1>2722</USRDEFND1>
    <USRDEFND5>1</USRDEFND5>
    </taPMTransactionInsert>
    </PMTransactionType>
    </eConnect>

    In the taPMTransactionInsertPost procedure I added the following code snippet.  It finds any distribution lines with a DSTSQNUM > 1000 and a DISTTYPE of 6 (Purchases) and deletes it.  This code is added prior to any stock code in the procedure is run in order to allow MEM to work with only the corrected distribution.


    /* Custom Script by Brenda Rogers 09/03/2021 */
    /* Delete extra distribution lines for DistType 6. Should only have DistSeq < 1000.
    Boomi is creating distribution lines but also allowing GP to Calculate the distribution.
    This causes a duplication of the Purchase Amount. This section will delete the extra line.*/

    Delete from PM10100
    where VCHRNMBR = @I_vVCHNUMWK
    and DISTTYPE = '6'
    and DSTSQNUM > '1000'

    If anyone can find holes in my logic please do let me know.  I have tested it against several invoices and it seems to be working beautifully but more extensive testing is still needed.

    ------------------------------
    Brenda Rogers
    Financial Systems Business Applications Analyst
    Falls of Neuse Management, LLC
    Raleigh NC
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: GP 2016 Using eConnect to load Purchasing Invoices - Distribution OOB

    SILVER CONTRIBUTOR
    Posted 20 days ago
    Hi Brenda,

    I haven't worked with Boomi, so I'm not sure what complexity that might be adding, but I didn't see you passing a value for CREATEDIST in the XML.  The default is 1, which I think is going to ignore anything you pass on the taPMDistribution node.


    Here is a schema reference - despite the date on the documentation, it's the most up to date:
    https://docs.microsoft.com/en-us/previous-versions/dynamicsgp/developer/jj193390(v=msdn.10)

    The GP configuration for default G/L accounts can be quite challenging, especially if you're using multiple modules and/or third party products. When you are testing, also make sure that you look at the posted as well as the un-posted transaction distributions. Depending on the posting account setup, the values may change when posted.

    Regarding the line numbering in GP - you are correct, most tables line numbers are a multiple of 16384, but there are a few exceptions. Here is a fun fact article on why 16,384 was chosen: https://community.dynamics.com/gp/f/microsoft-dynamics-gp-forum/114186/dynamics-gp-invoice-line-numbers/232362

    ------------------------------
    Jeff Woodard
    Chief Technical Officer
    Transportation Financial Services, Inc.
    West Palm Beach FL
    ------------------------------

    Academy - Online Interactive Learning from Experts


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