Open Forum

Expand all | Collapse all

SmartConnect Mapping - SOP Header Sales Tax From Source File

  • 1.  SmartConnect Mapping - SOP Header Sales Tax From Source File

    Posted 19 days ago
    I am setting up a mapping for SOP Invoices from a *.csv source file. As part of the source file, I have the Tax Schedule ID value and the Total Tax value included.  I used this article: Tech Tuesday: SOP with Header Level Taxes | eOne Solutions
    eOne Solutions remove preview
    Tech Tuesday: SOP with Header Level Taxes | eOne Solutions
    This week's Tech Tuesday will give an example of how to do a SOP Transaction with header level taxes. The most common scenario we run into is allowing the sales transaction to use the default tax engine inside of GP. However, the second most common request is how to import a sales transaction with header...
    View this on eOne Solutions >


    There are no issues with the mapping until I get to the Add Line Tax Detail node. As part of the mapping, the Tax Detail ID is a required field, but I don't have the TAX DETAIL ID in the source file, I only have the TAX SCHEDULE ID. If I set it to use the GP tax engine, the tax calculations are a couple of cents off compared to the source program, and in turn, the document amount is off between the two systems.

    I know I am missing something because it is common not to have the tax details when bringing in taxes from a source file. 



    ------------------------------
    Lisa Sorensen
    LS Business Consulting
    Dallas, TX
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: SmartConnect Mapping - SOP Header Sales Tax From Source File

    Posted 19 days ago
    Lisa,

    I know I am missing something because it is common not to have the tax details when bringing in taxes from a source file. 

    Yes you are, you are missing the  Tax Detail ID.

    If you want GP to default the Taxes for you, you either let the schedule default from the customer or else can provide the Tax Schedule yourself.  And then GP/eConnect will create all the details for you based on that schedule.

    In your case you say - "I want to do this myself" so there isn't that default.

    If you go into SOP and enter a transaction that has taxes and then go to the Tax Details window, you see that there are individual tax detail lines with the specific tax details and amounts.  Since you are now creating those lines, via the add tax mapping, YOU then need to provide the amounts (which you have) and the Tax Detail ID (which you do not have yet).

    The solution to your issue can be either easy or complicated and really depends on how many tax detail lines you need to create and what you need the tax details to look like.

    The 3 scenarios i can think of:

    1 If in your file you just have - TaxAmount = $25.12 and that is a "combined total tax amount"
    Then the easiest solution is to just hard code the tax detail into the mapping.  Now the amount comes in under that one tax detail you hard coded.

    2. Same as above however you need the correct tax detail from the known tax schedule from the data

    Since you don't have the actual tax detail but you do have the tax schedule, you can use an MSSQL Lookup in SmartConnect to query the tax detail setup table to find the tax detail.  I'm not sure offhand which TX table that is but I'm sure you'd be able to find that table.

    Once you have the actual tax detail id from the lookup, you'd just map that lookup instead of hard coding it.

    3. you have multiple tax detail lines.  So in the source you might have:

    DocNo,     TaxAmount,  ScheduleID
    1000,       10.00          , SCHEDULE1
    1000,        15.25         , SCHEDULE1

    So in this case, you KNOW you have 2 tax details because you have two tax lines.  Now this is going to be much more difficult because we have no way of knowing which line is which (unless you know that line 1 is always "TaxDetail1" and line 2 is always "TaxDetail2" from the Tax Schedule table.

    This is pretty uncommon scenario and the solution is going to depend a lot on what the data looks like and what the destination needs to look like.  You'd have to send an email to support@eonesolutions.com to look into it further.

    patrick
    eOne


    ------------------------------
    Pat Roth
    eOne Solutions
    Fargo
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: SmartConnect Mapping - SOP Header Sales Tax From Source File

    GPUG ALL STAR
    Posted 18 days ago
    Hi Lisa,
    Pat showed you the 'long' way of including the taxes when it comes to integrate SOP Invoices thru eConnect.. I see this quite often with customers that want to integrate from CRM or another eCommerce solution.. and often time provide the tax amounts, but no tax schedule ID or Tax Detail ID.. I always have to tell them that this is a constriction of eConnect and without the Tax Schedule ID it is impossible to get the tax amounts integrated thru the source data.. so the choice is either you let GP calculate the taxes automatically, which implies that all the customers do need to have a Tax Schdule ID defined in their card, or you hard code in the map the Tax Schedule ID and assume it's going to be always the same (which is by far ideal, as there can be exceptions).
    In the case of CRM, I advise the clients that if they do want to provide the tax amounts and make no calculations, they'd better also sync the customer information from GP and decide where the "master" is going to reside (GP or CRM?).
    The other pitfall that happens sometime is the rounding of the taxes and the sub-totals, which can be a little tricky. That's why my prefered way of integrating invoices is to let GP do all the calculation :-)

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    All-Star 2013
    ------------------------------

    Academy - Online Interactive Learning from Experts


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