Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Using eConnect to insert an invoice and override the exchange rate

  • 1.  Using eConnect to insert an invoice and override the exchange rate

    Posted 8 days ago
    We're attempting to use eConnect to programmatically insert invoices in GP, and sometimes need to override the exchange rate on the transaction. When using the taSopHdrIvcInsert method, instead of just setting the exchange rate on the transaction, it adds a new rate to the Exchange Rate table, which we absolutely do not want. When entering invoices through the GP UI, you're prompted whether you want to add the rate or not, but as far as we can tell, there is no option for this in eConnect.

     

    According to this post https://community.dynamics.com/gp/f/32/t/89548 , this may have been reported as a bug in 2012, but I've been unable to find any references to a bug or a fix.

     

    Has anyone encountered this issue or know if there have been any changes to how taSopHdrIvcInsert works in regards to exchange rates?




    ------------------------------
    Dave Finley
    Database Administrator
    Epic Systems Corporation
    Verona WI
    ------------------------------


  • 2.  RE: Using eConnect to insert an invoice and override the exchange rate

    GOLD CONTRIBUTOR
    Posted 7 days ago
    ​Hi Dave,

    I've reviewed the eConnect stored procedures, and it looks like the new rates are being inserted by the taMCExchangeRate stored proc.  This proc is called through a series of nested procs from taSopHdrIvcInsert, and there is no option to skip the rate insert.


    taMCExchangeRate queries MC00100 for a given rate table, date, and time:

    SELECT @XCHGRATE = XCHGRATE
    FROM DYNAMICS..MC00100 (NOLOCK)
    WHERE EXGTBLID = @I_vEXGTBLID
    AND EXCHDATE = @I_vEXCHDATE
    AND TIME1 = @I_vTIME1;


    If the rate in MC00100 is different than what is used in the SOP transaction, then it always inserts a new rate into MC00100.  There is no parameter to the proc that lets you choose whether or not to perform the insert.


    IF (@I_vXCHGRATE <> ISNULL(@XCHGRATE, 0))
    BEGIN
    INSERT DYNAMICS..MC00100
    (
    EXGTBLID,
    CURNCYID,
    EXCHDATE,
    TIME1,
    XCHGRATE,
    EXPNDATE
    )


    Unfortunately, the taMCExchangeRate procedure doesn't appear have any user defined or 'spare' parameters, so it would probably be tricky to pass in a value that would allow you to specify whether or not you wanted a new rate inserted.

    IF (@I_vXCHGRATE <> ISNULL(@XCHGRATE, 0) AND insertnewrate = true)


    Alternately, you could modify the logic to only call the insert if a rate did not exist for the specified date and time.  This would be specific to your environment and business rules.  You would have to think through this approach and test it to see if there are any downsides to this method.

    Something like:

    IF (NOT EXISTS
    (
    SELECT 1 FROM DYNAMICS..MC00100 (NOLOCK)
    WHERE EXGTBLID = @I_vEXGTBLID
    AND EXCHDATE = @I_vEXCHDATE
    AND TIME1 = @I_vTIME1
    )
    )
    BEGIN
       --insert new rate
    END


    I see in the forum post you cited that someone used the eConnect Post proc to remove the rate after it was inserted.  That is certainly an option as well, but would require similar design and testing, and will likely require some creativity to determine how to identify the rate to delete.

    And if you make any changes to the eConnect procs or any Pre / Post procs, remember that the eConnect procs are usually dropped and recreated from scratch during GP upgrades, and you have to re-run your ALTER scripts after every GP upgrade.  No matter how well you document or try to remember, everyone forgets that they have a custom proc, and you only figure it out a week after your upgrade.

    Hope that helps.

    ------------------------------
    Steve Endow, Microsoft MVP
    Precipio Services
    Los Angeles, CA
    ------------------------------



  • 3.  RE: Using eConnect to insert an invoice and override the exchange rate

    Posted 6 days ago
    Edited by Dave Finley 6 days ago
    ​Hi Steve,

    Thank you for digging into the code and confirming that we can't override that behavior.

    We'll likely pursue customizing the code to either prevent the creation of the rates or cleaning up afterwards, it's a shame that the eConnect code doesn't work the same way as the UI.


    ------------------------------
    Dave Finley
    Database Administrator
    Epic Systems Corporation
    Verona WI
    ------------------------------