Description

The SQL function is a utility that is used by several SQL import methods. This function may be added and used as needed.

Purpose

This function is used to look up a tax class by name. It is broken out into a different routine in case a company wants to tailor their lookup rules.

Input
  • @TaxLookupValue - The name of the tax class to be looked up.
Output/Results
  • ID - The ID of the tax class to use.
Code
-- =============================================
-- Author:        Cyrious Sofware
-- Create date: May-2016
-- Description: This function maps a tax class name to a valid
--              tax class in Control.  The specifics of the tax class
--              logic can be customized for each company.
-- =============================================
CREATE FUNCTION [dbo].csf_MapTaxClassByName(
    @TaxLookupValue VARCHAR(255)
)
RETURNS INT
BEGIN
    DECLARE @TaxClassID INT;
 
    -- Step 1 -> See if an exact name match exists in our system
    IF (Len(@TaxLookupValue) > 0)
        SET @TaxClassID = (SELECT ID FROM TaxClass WHERE TaxClassName = @TaxLookupValue AND ID>0 );
 
    -- Step 2 -> If not found, use custom rules
    IF (@TaxClassID IS NULL)
    BEGIN
        DECLARE @FallBackTaxClassID INT = (SELECT TOP(1) COALESCE(DefaultTaxClassID, 50) FROM Store WHERE ID > 0 ORDER BY ID);
        -- ----------------------------------------------------------------------------------------------
        -- INSERT CUSTOM RULES HERE
        -- ----------------------------------------------------------------------------------------------
        -- DECLARE @OutOfStateTaxClass INT = 1006;
        -- SET @TaxClassID =
        --     ( CASE
        --         WHEN (@TaxLookupValue = 'Out Of State')             THEN @OutOfStateTaxClass
        --         WHEN (@TaxLookupValue Like '%East Baton Rouge%')    THEN @EBRTaxClass
        --         WHEN (@TaxLookupValue Like '%EBR%')                 THEN @EBRTaxClass
        --         WHEN (@TaxLookupValue Like 'LA-%')                  THEN @LAStateOnly
        --         ELSE @FallBackTaxClassID
        --        END
        --      );
 
        SET @TaxClassID = @FallBackTaxClassID;
    END;
    RETURN @TaxClassID;
END

Source

Contributor: Cyrious Software

Date: 5/2016

Version: Control 5.7+

You could leave a comment if you were logged in.