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.