Page Comparison

...

...

...

...

Use the Salesforce data import wizard to load the tax rate data into the staging table.

...

New button on the Tax Rate list view “Import Tax Rate”

When the user clicks the button system will

Try to find an existing Tax Area record by zip code.

...

If the Tax Area record is found then compare the “Estimated Combined Rate” to Tax Detail’s Tax % field. If the rate is different then update the existing Tax Detail record, otherwise skip, and process the next line.

...

...

Create a staging table “Tax Rate” with the following fields.

...

Add the “Zip Code” field to the Tax Area table

• Zip Code is a unique external id field.

• Allow the system to match the Tax Area record quickly using a zip code.

• When a new Tax Area record is created by the Tax Rate Import routine, it will populate this field using data in the ZipCode column.

Tax Rate Import Routine

Overview

Ad Magic necessitates the functionality to compute taxes at the county level for designated sales transactions. This is imperative for optimizing the efficiency of tax return preparation, particularly in states where intricate tax structures are in place.

To attain this objective, we have implemented a Salesforce table, enabling the Ad Magic team to manually import tax rates extracted from Avalara. Subsequently, a button can be activated to generate Tax Area configurations, which will be utilized in the sales transactions at a later stage.

How to Import Tax Rates Extracted from Avalara

• Begin by accessing the “Tax Rates list page” and then selecting the “Import” button.

  Image Added

• On the 'Import Your Data into Salesforce' page:

  • Under the Custom objects tab, opt for Tax Rates.

  • Choose the option Add new and update existing records and specify matching records by Zip Code (External ID).

  • Place the CSV file exported from Avalara into the Where is your data located? section by dragging and dropping it.

  • Click Next.

    Image Added

• Review the field mapping; the system should automatically align fields where the table field name matches the file column name.

• If the field mapping appears satisfactory, advance to the next page by clicking Next.

  Image Added

• Initiate the import process by selecting Start Import.

• Complete the action by clicking OK on the Congratulations popup window.

• On the “Bulk Data Load Job” page, ensure that the import batch is completed without any errors.

  Image Added

• Once the tax rates have been successfully imported into Salesforce, navigate to the “Tax Rates list view” and click the Create GF Tax Area button.

  Image Added

• On the confirmation page, click the Create GF Tax Area button to trigger the designated routine.

  Image Added

• When the user activates the button, the system will perform the following actions:

  • Attempt to locate an existing Tax Area record based on the zip code

  There will be thousands of Tax Area records, and it will not be user-friendly when they need to look up a tax area code manually in a Sales Order.

  • Currently, on the Sales Order VF, and Sales Invoice VF, Tax Area is a dropdown list. Talk to Zon to see what option we have….He did something for the Warehouse field. Based on some configurations, or No. of Records, to use either drop-down list, or a typeahead lookup

    • If the system finds a Tax Area record, compare the “Estimated Combined Rate” with the Tax Detail’s Tax % field. If they differ, it will update the existing Tax Detail record. Otherwise, it will proceed to the next line.

    • If no Tax Area record is found, create new records for Tax Area, Tax Jurisdictions, and Tax Detail

      record

      .

      • When

        Creating

        creating a new Tax Area record

        use

        , Tax Area Name will be in the format {State}-{ZipCode

        }-{Tax Region Name

        }

        as the Tax Area Name

        .

      • When

        Creating

        creating a new Tax Jurisdiction record

        use

        , employ the format {State}-{ZipCode

        }-{Tax Region Name

        } as the Tax Jurisdiction Name.

      • When

        Creating

        generating a new Tax Detail record, use the value in the “Estimated Combined Rate” column as the Tax %, and set the default Tax Group to “Material”.

• When an Account record is inserted/updated

  • If the Account’s Shipping Zip Code is changed, then the system will try to pull the Tax Area by zip code, if a match is found then populate the Tax Area field, otherwise leave it blank.

  • If the Account’s Shipping Zip Code is blank then use the Billing Zip Code to search for a Tax Area.

• When an Alternate Shipping record is inserted/updated

  • If the Alternate Shipping’s Shipping Postal Code is changed, then the system will try to pull the Tax Area by shipping postal code, if a match is found then populate the Tax Area field, otherwise leave it blank.

• When to check Tax Liable on the Account and Alternate Shipping???

• Which division is Tax Liable, which channel???

• When a Sales Order is updated

  • If the Shipping Post Code is changed, then the system will try to pull the Tax Area by shipping postal code, if a match is found then populate the Tax Area field, otherwise leave it blank. - no need to do it. GoldFinch will pull in Tax Area automatically based on Alternating Shipping or Sell-to. Users can manually update as needed.

Potential Issues with this approach:

Salesforce Object Setup

Tax Rate Table Definition

• Add the Zip Code field to the Tax Area table.

  • The Zip Code functions as a distinctive external Id field.

  • This enables the system to swiftly locate and correlate the Tax Area record by utilizing the zip code.

  • Whenever the Tax Rate Import routine generates a fresh Tax Area record, it will automatically populate this field with data from the ZipCode column.

Pending Design Decision Review

• Pending - Zon will convert the Tax Area field from a dropdown menu to a Typeahead search field.

• Only 50 to 100 transactions per year would need Sales Tax:

  • Ad Magic

    • Breaking Game Sales Tax is handled by Avalara on Shopify sites.

  • Promotion items only

• Users will manually check Tax Liable, and enter Tax Area on the Sales Order.

  • No need to set up an Account or Alternating Shipping.