SQL Stored Procedure - QuickBooks Import

WARNING: This is not kids stuff. Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine. If you are interested in using sql_bridge but are not a SQL guru, please contact a sales or consulting at Cyrious.

Note: This feature requires Control 5.75 or higher.

Overview

The stored procedure can be used to Import Customers and Orders from a QuickBooks IIF file into Control.

The following values are required:

  • @ImportPath = 'C:\Users\scott\Accounting\QB Import\',
  • @ImportFile = 'TEST.iif',

The following values may be supplied, but will use the default value is not supplied:

  • @CustomerNotes - Anything here will appear in the notes for imported customers.
  • @OrderStatusID - The status of the imported order. Can be 1 (WIP), 2 (Built), or 3 (Sale)
  • @AltFreightProduct - The product code to map all imported Freight to. Leave this blank.
  • @DivisionID - Enter the Division.ID for new companies and orders. Omit if you are not using Division.
  • @OrderStatusID TINYINT = 1, – The status of the imported order. Can be 1 (WIP), 2 (Built), or 3 (Sale)
  • @OrderStationID INT = NULL, – The StationID for the Order
  • @SaleStationID INT = NULL, – The Sales StationID fo the Order
  • @IsPriceLocked bit = 1, – Indicates if the order is price locked. 0 = Unlocked, 1 = Locked
  • @SuccessSubFolderName VARCHAR(255) = NULL, – The name of a sub-folder to move the Import file to if all of the import is successful.

The Stored Procedure returns a table of newly creates records (Customers, Contacts, Orders, Line Items).

Notes:

  • Payments are NOT Imported. Orders come in with full balance due.
  • Taxes are NOT imported but are recomputed when the order is saved.
  • However, when importing customers and orders the IsTaxable is set.
  • If the Customer or Contact already exist, they are NOT updated.
  • If the customer exists but not the contact, just the contact is imported.
  • The Recompute can take 2-10 minutes depending on how many orders are
  • imported. Until that time, all order totals will be $0.00. You should
  • not edit or change the order until it is recomputed.

The stored procedure requires sql_bridge in order to obtain IDs for the inserted records. sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

Example Usage

This imports all Customers and Invoices into Control and sets the orders to Sale.

DECLARE @CustomerNotes    VARCHAR(255)    = 'Imported from Vivid South on '+CONVERT(VARCHAR(25), GetDate());
 
EXEC csp_ImportQBIIF_Customers_Orders
    -- Define some Input variables
    @ImportPath         = 'C:\Dat\QB Import\',    -- '
    @ImportFile         = 'Test.iif',
    @CustomerNotes      = @CustomerNotes,
    @DivisionID         = 10,       -- From Division.ID. (Use 10 if not using divisions!)
    @OrderStatusID      = 1,        -- The status of the imported order.  Can be 1 (WIP), 2 (Built), or 3 (Sale)
    @AltFreightProduct  = 'VS FREIGHT',  -- If this is not null, all freight line items will be mapped to this product name.
    @IsPriceLocked      = 0,            -- 0 = Unlocked, 1 = Locked
    @SuccessSubFolderName  = 'Success'  -- moves successfully imported files to this folder if provided
;

Stored Procedure

The SQL to create the QuickBooks Import Function.

  • This must be run to create the stored procedure before it can be used.
  • This function also requires two additional SQL procedures found below. They must be run to create those functions before this method can be used.

csp_ImportQBIIF_Customers_Orders

-- =============================================
-- Author:          Cyrious Sofware
-- Create date:     May-2016
-- Description:     This stored procedure imports a QuickBooks IIF file with the
--                  following information:
--
--                  Customer (!CUST)
--                  Orders  (!TRNS)
--                  Line Items (!SPL)
--
-- Notes:
--          ** Payments are NOT Imported.  Orders come in with full balance due.
--          ** Taxes are NOT imported but are recomputed when the order is saved.
--          ** However, when importing customers and orders the IsTaxable is set.
--  
--          ** If the Customer or Contact already exist, they are NOT updated.
--          ** If the customer exists but not the contact, just the contact is imported.
--
--          ** The Recompute can take 2-10 minutes depending on how many orders are
--          ** imported.  Until that time, all order totals will be $0.00.  You should
--          ** not edit or change the order until it is recomputed.
--
-- To Enable the File Copy, you must enable SQL to run a command shell.
-- To do this, run the following commands (in order) in SQL Server Management Console.
--
    -- ---- To allow advanced options to be changed.
    -- EXEC sp_configure 'show advanced options', 1
    -- GO
    -- ---- To update the currently configured value for advanced options.
    -- RECONFIGURE
    -- GO
    -- ---- To enable the feature.
    -- EXEC sp_configure 'xp_cmdshell',1
    -- GO
    -- ---- To update the currently configured value for this feature.
    -- RECONFIGURE
    -- GO 
--
-- Returns:     Table of new records
-- =============================================
ALTER PROCEDURE csp_ImportQBIIF_Customers_Orders
    -- The Path and File Name (with extension) is the only required input
    @ImportPath         VARCHAR(512),   
    @ImportFile         VARCHAR(512),
 
    -- Optional inputs - Leave off for the default value or if they don't apply
    @CustomerNotes      VARCHAR(4096)   = 'Imported from QB',
    @OrderStatusID      TINYINT         = 1,        -- The status of the imported order.  Can be 1 (WIP), 2 (Built), or 3 (Sale)
    @OrderStationID     INT             = NULL,     -- The StationID for the Order
    @SaleStationID      INT             = NULL,     -- The Sales StationID fo the Order
    @AltFreightProduct  VARCHAR(64)     = NULL,     -- If this is not null, all freight line items will be mapped to this product name.
    @IsPriceLocked      bit             = 1,        -- Indicates if the order is price locked.  0 = Unlocked, 1 = Locked
    @DivisionID         INT             = 10,       -- Division for new Customers and Orders.  From Division.ID. (Leave off if not using divisions!)
    @SuccessSubFolderName  VARCHAR(255) = NULL,  -- The name of a sub-folder to move the Import file to if all of the import is sucecssful.
    @StripLeadingCoNumbers bit          = 0,        -- If set to 1, will remove the number:number from the company name that QuickBooks places there for subsidiaries
    @DeveloperMode      bit             = 0         -- Show addtional information helpful for development
 
AS
BEGIN
    -- Define some variables used in the import
    DECLARE @Logs                               TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT,
                                                       Summary VARCHAR(255),
                                                       Detail VARCHAR(2000)
                                                       );
    DECLARE @NewCompanies                        SMALLINT = 0;
    DECLARE @NewContacts                        SMALLINT = 0;
    DECLARE @Neworders                            SMALLINT = 0;
 
    DECLARE @AccountID                          INT;
    DECLARE @ContactID                          INT;
    DECLARE @THID                                INT;
    DECLARE @OrderNumber                        INT;
    DECLARE @CreateCompany                      BIT;
    DECLARE @CreateContact                      BIT;
 
    DECLARE @RowID                              SMALLINT;
    DECLARE @CompanyName                        VARCHAR(128);
 
    DECLARE @WorkPhoneAC                        VARCHAR(25);
    DECLARE @WorkPhoneNumber                    VARCHAR(25);
 
    DECLARE @BillingAddress1                    VARCHAR(256);
    DECLARE @BillingAddress2                    VARCHAR(256);
    DECLARE @BillingCSZ                         VARCHAR(256);
    DECLARE @BillingCity                        VARCHAR(256);
    DECLARE @BillingState                       VARCHAR(256);
    DECLARE @BillingPostalCode                  VARCHAR(256);
 
    DECLARE @ShippingAddressSameAsBilling       BIT;
    DECLARE @UseCompanyShippingAddress            BIT;
 
    DECLARE @ShippingAddress1                   VARCHAR(256);
    DECLARE @ShippingAddress2                   VARCHAR(256);
    DECLARE @ShippingCSZ                        VARCHAR(256);
    DECLARE @ShippingCity                       VARCHAR(256);
    DECLARE @ShippingState                      VARCHAR(256);
    DECLARE @ShippingPostalCode                 VARCHAR(256);
 
    DECLARE @FirstName                         VARCHAR(25);
    DECLARE @LastName                          VARCHAR(25);
    DECLARE @EmailAddress                      VARCHAR(50);
 
    DECLARE @AddCellPhone                      BIT;
    DECLARE @CellPhoneAC                       VARCHAR(10);
    DECLARE @CellPhoneNumber                   VARCHAR(25);
 
    DECLARE  @OrderPlacedDate       SMALLDATETIME;
    DECLARE  @OrderNotes            VARCHAR(2000);
    DECLARE  @SalespersonID            INT;
    DECLARE  @RepFirstName          VARCHAR(255);
    DECLARE  @RepLastName           VARCHAR(255);
    DECLARE  @Amount                DECIMAL(18,4);
    DECLARE  @DocNum                VARCHAR(255);
    DECLARE  @OrderDescription        VARCHAR(255);
    DECLARE  @IsTaxExempt           BIT;
    DECLARE  @ShipVia               VARCHAR(255);
    DECLARE  @ShipToCompanyName     VARCHAR(255);
    DECLARE  @Terms                 VARCHAR(255);
    DECLARE  @PONumber              VARCHAR(255);
    DECLARE  @OrderDueDate          SMALLDATETIME;
 
    DECLARE  @TDID                  INT;
    DECLARE  @GLAccountName         VARCHAR(255);
    DECLARE  @ProductName           VARCHAR(255);
    DECLARE  @BasePrice             DECIMAL(18,4);
    DECLARE  @Discount              DECIMAL(18,4);
    DECLARE  @Tax                   DECIMAL(18,4);
    DECLARE  @Quantity              DECIMAL(18,4);
    DECLARE  @UnitPrice             DECIMAL(18,4);
    DECLARE  @Description           VARCHAR(255);
    DECLARE  @IsFreight             BIT;
 
    DECLARE  @TaxClassID            INT;
    DECLARE  @TaxClassName          VARCHAR(255);
 
    SET NOCOUNT ON;
 
    -- Define some error variables in case we need them
    DECLARE @DT              SMALLDATETIME = GetDate();
    DECLARE @ComputerName    VARCHAR(25) = @@ServerName;
    DECLARE @NewLine         CHAR(2) =  CHAR(10)+CHAR(13);
    DECLARE @Pos             INT = 0;
 
    -- If the Temp Tables we are going to use exist, we need to drop them first
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIFFileCols'  , 'U') IS NOT NULL)  DROP TABLE #Temp_IIFFileCols; 
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIF_Customers', 'U') IS NOT NULL)  DROP TABLE #Temp_IIF_Customers; 
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIF_Orders'   , 'U') IS NOT NULL)  DROP TABLE #Temp_IIF_Orders; 
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIF_Items'    , 'U') IS NOT NULL)  DROP TABLE #Temp_IIF_Items; 
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIF_Freight'  , 'U') IS NOT NULL)  DROP TABLE #Temp_IIF_Freight; 
    IF (OBJECT_ID('TempDB.dbo.#Temp_IIF_SalesTax' , 'U') IS NOT NULL)  DROP TABLE #Temp_IIF_SalesTax; 
 
    -- Create the main table for the results
    CREATE TABLE #Temp_IIFFileCols (
        RowID SMALLINT PRIMARY KEY,
        Col01 VARCHAR(2000), Col02 VARCHAR(2000), Col03 VARCHAR(2000), Col04 VARCHAR(2000), Col05 VARCHAR(2000), Col06 VARCHAR(2000), Col07 VARCHAR(2000), Col08 VARCHAR(2000), Col09 VARCHAR(2000), Col10 VARCHAR(2000),
        Col11 VARCHAR(2000), Col12 VARCHAR(2000), Col13 VARCHAR(2000), Col14 VARCHAR(2000), Col15 VARCHAR(2000), Col16 VARCHAR(2000), Col17 VARCHAR(2000), Col18 VARCHAR(2000), Col19 VARCHAR(2000), Col20 VARCHAR(2000),
        Col21 VARCHAR(2000), Col22 VARCHAR(2000), Col23 VARCHAR(2000), Col24 VARCHAR(2000), Col25 VARCHAR(2000), Col26 VARCHAR(2000), Col27 VARCHAR(2000), Col28 VARCHAR(2000), Col29 VARCHAR(2000), Col30 VARCHAR(2000)
        );
 
    -- --------------------------------------------------
    -- Upload the IIF File into #Temp_IIFFileCols
    -- --------------------------------------------------
    IF RIGHT(@ImportPath,1) NOT IN ('/','\')   -- 'Need TO ADD a final delimiter IF NOT there
        SET @ImportPath = @ImportPath + '/';
 
    -- Now combine the FilePath and Name
    SET @ImportFile = @ImportPath + @ImportFile;
 
    -- And run the import routine
    INSERT INTO #Temp_IIFFileCols
        EXEC csp_ImportTextFileToCols
            @FileName    = @ImportFile
    ;
 
    -- --------------------------------------------------
    -- Now split the import into the following different temp files
    -- --------------------------------------------------
    --  #Temp_IIF_Customers; 
    --  #Temp_IIF_Orders; 
    --  #Temp_IIF_Items; 
    --  #Temp_IIF_Freight; 
    --  #Temp_IIF_SalesTax; 
 
    -- Create #Temp_IIF_Customers list
    SELECT
        -- ID fields looked up or assigned
        RowID,
        ROW_NUMBER() OVER (ORDER BY RowID) AS CustRowNum,
        CONVERT(INT, 0) AS AccountID,
        CONVERT(INT, 0) AS ContactID,
 
        -- Straight data fields from IIF File
        Col02 AS Name,
        Col03 AS BAddr1,  -- Always a duplicate of the company name
        Col04 AS BAddr2,
        Col05 AS BAddr3,
        Col06 AS BAddr4,
        Col07 AS BAddr5,
        Col08 AS SAddr1, -- Always a duplicate of the company name
        Col09 AS SAddr2,
        Col10 AS SAddr3,
        Col11 AS SAddr4,
        Col12 AS SAddr5,
        Col13 AS Phone1,
        Col14 AS Phone2,
        Col15 AS FaxNum,
        Col16 AS Email,
        Col17 AS Cont1,
        Col18 AS CType,
        Col19 AS Terms,
        Col20 AS Taxable,
        Col21 AS TaxItem,
        Col22 AS ResaleNum,
        Col23 AS CompanyName,
        Col24 AS CustomerID,
        Col25 AS UserID,
 
        -- Derived Fields
        (CASE WHEN CHARINDEX(' ',Col17) = 0 
            THEN Col17 
            ELSE LEFT(Col17, CHARINDEX(' ',Col17)-1) END
        ) AS FirstName,
        (CASE WHEN CHARINDEX(' ',Col17) = 0 
            THEN '' 
            ELSE SUBSTRING(Col17, CHARINDEX(' ',Col17)+1, 99) END
        ) AS LastName,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Col13, ' ', ''), '(', ''), ')', ''), '.', ''), '-', '') AS WorkPhone,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Col14, ' ', ''), '(', ''), ')', ''), '.', ''), '-', '') AS CellPhone,
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Col15, ' ', ''), '(', ''), ')', ''), '.', ''), '-', '') AS FaxNumber,
        Col04 AS BillingAddress1,
        (CASE WHEN Len(COALESCE(Col06, '')) > 1 THEN COALESCE(Col05, '') ELSE '' END)
            + (CASE WHEN Len(COALESCE(Col07, '')) > 1 THEN @NewLine + COALESCE(Col06, '') ELSE '' END)
            AS BillingAddress2,
        (CASE WHEN Len(COALESCE(Col07, '')) > 1  THEN Col07 
            WHEN Len(COALESCE(Col06, '')) > 1  THEN Col06
            ELSE Col05 END) AS BillingCSZ,
        Col09 AS ShippingAddress1,
        (CASE WHEN Len(COALESCE(Col11, '')) > 1 THEN COALESCE(Col10, '') ELSE '' END)
            + (CASE WHEN Len(COALESCE(Col12, '')) > 1 THEN @NewLine + COALESCE(Col11, '') ELSE '' END)
            AS ShippingAddress2,
        (CASE WHEN Len(COALESCE(Col12, '')) > 1  THEN Col12 
            WHEN Len(COALESCE(Col11, '')) > 1  THEN Col11
            ELSE Col10 END) AS ShippingCSZ   
 
    INTO #Temp_IIF_Customers
    FROM  #Temp_IIFFileCols
    WHERE Col01 = 'CUST'
    ;
 
    -------------------------------------------------------------------
    -- Import the Customers
    -------------------------------------------------------------------
    --
    -- Loop through the customer data in the inmport file and create them
    --
    DECLARE CustomerList CURSOR FOR
        SELECT
            RowID                        ,   -- Unique RowID. used to update the record back 
            Name                        ,   -- CompanyName
            LEFT(WorkPhone, 3)          ,   -- WorkPhoneAC
            SUBSTRING(WorkPhone, 4, 99) ,   -- WorkPhoneNumber
            BillingAddress1                ,   -- Billing StreetAddress1
            BillingAddress2                ,   -- Billing StreetAddress2
            BillingCSZ                  ,   -- Billing City, State, Zip
            ShippingAddress1            ,   -- Shipping StreetAddress1
            ShippingAddress2            ,   -- Shipping StreetAddress2
            ShippingCSZ                 ,   -- Shipping City, State, Zip
            FirstName                   ,   -- Contact First Name
            LastName                    ,   -- Contact Last Name
            Email                        ,   -- Contact Email
            LEFT(CellPhone, 3)          ,   -- Contact CellPhoneAC
            SUBSTRING(CellPhone, 4, 99)        -- Contact CellPhoneNumber
        FROM #Temp_IIF_Customers C; 
 
    OPEN CustomerList;
    FETCH NEXT FROM CustomerList INTO 
        @RowID            ,
        @CompanyName      ,
        @WorkPhoneAC      ,
        @WorkPhoneNumber  ,
        @BillingAddress1  ,
        @BillingAddress2  ,
        @BillingCSZ             ,
        @ShippingAddress1  ,
        @ShippingAddress2  ,
        @ShippingCSZ             ,
        @FirstName        ,
        @LastName         ,
        @EmailAddress     ,
        @CellPhoneAC      ,
        @CellPhoneNumber  ;
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- For each customer, create a separate transaction
    BEGIN TRANSACTION
    BEGIN TRY
            PRINT @CompanyName;
 
            -- Remove leading numbers from the CompanyName if desired
            IF ((@StripLeadingCoNumbers = 1) AND ( IsNumeric( LEFT(@CompanyName,1) ) =1 ) )
            BEGIN
                -- Search for Colon and remove up to that if is numeric
                SET @Pos = CHARINDEX(':', @CompanyName);
                IF ((@Pos>1) AND (@Pos < 10) AND (IsNumeric( LEFT( @CompanyName, @Pos-1 ) ) =1 ) )
                    SET @CompanyName = RIGHT(@CompanyName, Len(@CompanyName)-@Pos );
 
                -- Search for Space and remove up to that if is numeric
                SET @Pos = CHARINDEX(' ', @CompanyName);
                IF ((@Pos>1) AND (@Pos < 10) AND (IsNumeric( LEFT( @CompanyName, @Pos-1 ) ) =1 ) )
                    SET @CompanyName = RIGHT(@CompanyName, Len(@CompanyName)-@Pos );
            END;
 
            -- See if the customer exists already
            SELECT TOP(1)
                @AccountID = A.ID,
                @ContactID = A.PrimaryContactID  -- use as a default
            FROM Account A
            WHERE A.CompanyName = @CompanyName;
 
            SET @FirstName             = (CASE WHEN COALESCE(@FirstName, '') = '' THEN '.' ELSE @FirstName END);
            SET @LastName             = (CASE WHEN COALESCE(@LastName , '') = '' THEN '.' ELSE @LastName  END);
 
            IF (@AccountID IS NULL)
            BEGIN
                SET @CreateCompany = 1;
                SET @CreateContact = 1;
            END
            ELSE
            BEGIN
                SET @CreateCompany = 0;
 
                IF (@FirstName = '.') AND (@LastName = '.')
                    SET @CreateContact = 0
                ELSE
                BEGIN
                    SET @ContactID = (SELECT TOP(1) ID FROM AccountContact 
                                    WHERE AccountID = @AccountID
                                        AND FirstName = @FirstName AND LastName = @LastName); 
 
                    SET @CreateContact = (CASE WHEN @ContactID IS NULL THEN 1 ELSE 0 END);
                END;
            END;                
 
            -- Compute some additional data (which may or may not be needed)
            IF (@CreateCompany = 1 OR @CreateContact = 1)
            BEGIN
 
                -- Split City/State/Zip
 
                IF ( CHARINDEX(',', @BillingCSZ) > 0)
                BEGIN
                    SET @BillingCity         = LEFT(@BillingCSZ, CHARINDEX(',', @BillingCSZ)-1 );
                    SET @BillingState        = SUBSTRING(@BillingCSZ, CHARINDEX(',', @BillingCSZ)+2, 2 );
                    SET @BillingPostalCode   = RIGHT(@BillingCSZ, CHARINDEX(' ', REVERSE(@BillingCSZ))-1 );
                END
                ELSE
                BEGIN
                    SET @BillingCity         = @BillingCSZ;
                    SET @BillingState        = '';
                    SET @BillingPostalCode   = '';
                END;
 
                IF ( CHARINDEX(',', @ShippingCSZ) > 0)
                BEGIN
                    SET @ShippingCity         = LEFT(@ShippingCSZ, CHARINDEX(',', @ShippingCSZ)-1 );
                    SET @ShippingState        = SUBSTRING(@ShippingCSZ, CHARINDEX(',', @ShippingCSZ)+2, 2 );
                    SET @ShippingPostalCode   = RIGHT(@ShippingCSZ, CHARINDEX(' ', REVERSE(@ShippingCSZ))-1 );
                END
                ELSE
                BEGIN
                    SET @ShippingCity         = @ShippingCSZ;
                    SET @ShippingState        = '';
                    SET @ShippingPostalCode   = '';
                END;
 
                SET @AddCellPhone  = (CASE WHEN LEN(@CellPhoneNumber) > 1 THEN 1 ELSE 0 END); 
 
                SET @ShippingAddressSameAsBilling = 
                        (CASE WHEN (@ShippingAddress1 = '' ) OR
                            ((@ShippingAddress1 = @BillingAddress1)
                            AND (@ShippingAddress2 = @BillingAddress2)
                            AND (@ShippingCity = @BillingCity) AND (@ShippingState = @BillingState))
                            THEN 1 
                            ELSE 0 END);
            END;
 
            -- insert the new company record if needed
            IF (@CreateCompany = 1)
            BEGIN
                INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
                EXEC csp_ImportCompany 
 
                    -- OUTPUT Variables to Capture the Key
                    @AccountID            = @AccountID    OUTPUT,
                    @ContactID            = @ContactID    OUTPUT,
 
                    -- Input Variables
                    @CompanyName        = @CompanyName,
 
                    @WorkPhoneAC        = @WorkPhoneAC,
                    @WorkPhoneNumber    = @WorkPhoneNumber,
 
                    -- Company Billing Address Info
                    @BillingAddress1    = @BillingAddress1,
                    @BillingAddress2    = @BillingAddress2,
                    @BillingCity                = @BillingCity,
                    @BillingState                = @BillingState,
                    @BillingPostalCode        = @BillingPostalCode,
 
                    -- Company Shipping Address Info
                    @ShippingAddressSameAsBilling = @ShippingAddressSameAsBilling,
                    @ShippingAddress1    = @ShippingAddress1,
                    @ShippingAddress2    = @ShippingAddress2,
                    @ShippingCity                = @ShippingCity,
                    @ShippingState                = @ShippingState,
                    @ShippingPostalCode        = @ShippingPostalCode,
 
                    -- Add other company information
                    @Notes                = @CustomerNotes,
                    @DivisionID         = @DivisionID,
 
                    -- Contact #1 Information
                    @AddContact            = 1,
                    @FirstName             = @FirstName,
                    @LastName             = @LastName,
                    @EmailAddress          = @EmailAddress,
 
                    @AddCellPhone        = @AddCellPhone,
                    @CellPhoneAC        = @CellPhoneAC,
                    @CellPhoneNumber    = @CellPhoneNumber
                ;
                SET @NewCompanies = @NewCompanies + 1;
                SET @NewContacts  = @NewContacts  + 1;
 
                PRINT '    Company and Contact Created';
            END
 
            -- insert the new contact record if needed
            ELSE IF (@CreateContact = 1)
            BEGIN
                SET @UseCompanyShippingAddress = (CASE WHEN @ShippingAddress1 = '' THEN 1 ELSE 0 END);
 
                INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
                EXEC csp_ImportContact
 
                    -- OUTPUT Variables to Capture the Key
                    @ContactID            = @ContactID    OUTPUT,
 
                    -- INPUT Variables
                    @AccountID            = @AccountID, 
 
                    @FirstName             = @FirstName,
                    @LastName             = @LastName,
                    @EmailAddress          = @EmailAddress,
 
                    @AddCellPhone        = @AddCellPhone,
                    @CellPhoneAC        = @CellPhoneAC,
                    @CellPhoneNumber    = @CellPhoneNumber,
 
                    @Notes                = @CustomerNotes,
 
                    @UseDefaultPhone    = 0,
                    @WorkPhoneAC        = @WorkPhoneAC,
                    @WorkPhoneNumber    = WorkPhoneNumber,
 
                    @UseCompanyShippingAddress = @UseCompanyShippingAddress,
                    @ShippingAddress1    = @ShippingAddress1,
                    @ShippingAddress2    = @ShippingAddress2,
                    @ShippingCity                = @ShippingCity,
                    @ShippingState                = @ShippingState,
                    @ShippingPostalCode        = @ShippingPostalCode
                ;
                SET @NewContacts  = @NewContacts  + 1;
 
                PRINT '    Company Exists; Contact Created';
            END
 
            ELSE
                PRINT '    Company and Contact Exist Already';
 
            -- now save the AccountID and ContactID back in the temp table
            --
            UPDATE #Temp_IIF_Customers
            SET AccountID = @AccountID, ContactID = @ContactID
            WHERE RowID = @RowID;
 
 
            -- and reset the variables for the next time
            SET @AccountID = NULL;
            SET @ContactID = NULL;
 
            -- Make it so, #1
            COMMIT TRANSACTION;
 
        END TRY
 
        BEGIN CATCH
            ROLLBACK TRANSACTION;
 
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            VALUES(
                @AccountID, 2000, NULL, 1,
                'Company '+@CompanyName+'  Import FAILED due to Unhandled Exception.',
                'Exception: '+ERROR_MESSAGE()
            );
 
        END CATCH;
 
        -- Now continue to the next customer
        FETCH NEXT FROM CustomerList INTO 
            @RowID            ,
            @CompanyName      ,
            @WorkPhoneAC      ,
            @WorkPhoneNumber  ,
            @BillingAddress1  ,
            @BillingAddress2  ,
            @BillingCSZ             ,
            @ShippingAddress1  ,
            @ShippingAddress2  ,
            @ShippingCSZ             ,
            @FirstName        ,
            @LastName         ,
            @EmailAddress     ,
            @CellPhoneAC      ,
            @CellPhoneNumber  ;
    END;  
 
    CLOSE CustomerList;
    DEALLOCATE CustomerList;
 
    -------------------------------------------------------------------
    -- Map the IIF Order Transactions
    -------------------------------------------------------------------
 
    -- Create the #Temp_IIF_Orders table for the orders
    SELECT
        -- ID fields looked up or assigned
        RowID,
        ROW_NUMBER() OVER (ORDER BY RowID) AS OrderRowNum,
        CONVERT(INT, 0) AS THID,
        CONVERT(INT, 0) AS AccountID,
        CONVERT(INT, 0) AS ContactID,
 
        -- Straight data fields from IIF File
        Col02 AS TrnsID,
        Col03 AS TrnsType,
        CONVERT(DATE, Col04) AS OrderDate ,
        Col05 AS Accnt ,
        Col06 AS Name ,
        Col07 AS Rep ,
        Col08 AS Class ,
        CONVERT(DECIMAL(18,4), Col09) AS Amount ,
        CONVERT(INT, Col10) AS DocNum ,
        Col11 AS Memo ,
        Col12 AS ShipVia,
        (CASE WHEN COALESCE(Col13, '') = '' THEN NULL WHEN Col13 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS Clear ,
        (CASE WHEN COALESCE(Col14, '') = '' THEN NULL WHEN Col14 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS ToPrint ,
        (CASE WHEN COALESCE(Col15, '') = '' THEN NULL WHEN Col15 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS NameIsTaxable ,
        Col16 AS Addr1 ,
        Col17 AS Addr2 ,
        Col18 AS Addr3 ,
        Col19 AS Addr4 ,
        Col20 AS SAddr1 ,
        Col21 AS SAddr2 ,
        Col22 AS SAddr3 ,
        Col23 AS SAddr4 ,
        Col24 AS Terms ,
        Col25 AS PONum ,
        CONVERT(DATE, Col26) AS DueDate ,
        COl27 AS CustomID,
 
        -- Derived Fields
        (CASE WHEN CHARINDEX(' ',Col07) = 0 
            THEN Col07 
            ELSE LEFT(Col07, CHARINDEX(' ',Col07)-1) END
        ) AS RepFirstName,
        (CASE WHEN CHARINDEX(' ',Col07) = 0 
            THEN '' 
            ELSE SUBSTRING(Col07, CHARINDEX(' ',Col07)+1, 99) END
        ) AS RepLastName,
        Col20 AS ShipToCompanyName,
        Col21 AS ShippingAddress1,
        (CASE WHEN Len(COALESCE(Col23, '')) > 1 THEN Col22 ELSE '' END) AS ShippingAddress2,
        (CASE WHEN Len(COALESCE(Col23, '')) > 1  THEN Col23 ELSE Col22 END) AS ShippingCSZ   
 
    INTO #Temp_IIF_Orders
    FROM  #Temp_IIFFileCols
    WHERE Col01 = 'TRNS' AND Col03 = 'INVOICE'
    ;
 
    -- Now fill in the AccountID and ContactID
    -- The only way to match them is by the name and order they are in the file (yuck!)
    UPDATE O
    SET     O.AccountID = C.AccountID, 
            O.ContactID = C.ContactID
    FROM #Temp_IIF_Orders O
    JOIN #Temp_IIF_Customers C ON C.CustRowNum = O.OrderRowNum 
    WHERE O.Name = C.Name   -- this line should not be necessary, but a nice safety to have
    ;
 
        -- We need to map the Tax Items too so that we can look up the tax class for the order
 
        -- -----------------------------------------------------------------
        -- Map the IIF Sales Tax Lines
        -- -----------------------------------------------------------------
 
        -- Create the #Temp_IIF_SalesTax table for the sales tax
        SELECT
            RowID,
            ROW_NUMBER() OVER (ORDER BY RowID) AS ItemRowNum,
            CONVERT(INT, 0) AS THID,
 
            Col02 AS SplID ,
            Col03 AS TrnsType ,
            CONVERT(DATE, Col04) AS [DATE] ,
            Col05 AS Accnt ,
            Col06 AS Name ,
            Col07 AS Class ,
            (CASE WHEN ISNUMERIC(Col08)=1 THEN CONVERT(DECIMAL(18,4), Col08) ELSE NULL END) AS Amount,
            CONVERT(INT, Col09) AS DocNum ,
            Col10 AS Memo ,
            (CASE WHEN COALESCE(Col11, '') = '' THEN NULL WHEN Col11 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS Clear,
            (CASE WHEN ISNUMERIC(Col12)=1 THEN CONVERT(DECIMAL(18,4), Col12) ELSE NULL END) AS Qnty,
            (CASE WHEN ISNUMERIC(Col13)=1 THEN CONVERT(DECIMAL(18,4), Col13) ELSE NULL END) AS Price,
            Col14 AS InvItem,
            Col15 AS PayMeth,
            (CASE WHEN COALESCE(Col16, '') = '' THEN NULL WHEN Col16 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS Taxable,
            Col17 AS ValAdj ,
            (CASE WHEN COALESCE(Col18, '') <> '' THEN CONVERT(DATE, Col18) ELSE NULL END)  AS ServiceDate,
            Col19 AS Extra ,
            Col20 AS CustomID 
        INTO #Temp_IIF_SalesTax
        FROM #Temp_IIFFileCols
        WHERE (Col01 = 'SPL') AND (Col05 ='Sales Tax Payable')
        ;
 
    -------------------------------------------------------------------
    -- Import the Orders
    -------------------------------------------------------------------
    --
    -- Loop through the order data in the inmport file and create them
    --
    DECLARE OrderList CURSOR FOR
        SELECT
            RowID                        ,    
            AccountID                   ,
            ContactID                   ,
            DATEADD(HOUR, 12, CONVERT(SmallDateTime, OrderDate)) ,  -- Place the order time at noon of the day
            RepFirstName                ,
            RepLastName                 ,
            Amount                      ,
            DocNum                      ,
            Memo                        , 
            ShipVia                     ,
            (CASE WHEN NameIsTaxable = 1 THEN 0 ELSE 1 END) AS IsTaxExempt ,
            ShipToCompanyName           ,
            ShippingAddress1            ,
            ShippingAddress2            ,
            (CASE WHEN CHARINDEX(',', @ShippingCSZ ) > 0 THEN LEFT(@ShippingCSZ, CHARINDEX(',', @ShippingCSZ )-1 ) ELSE @ShippingCSZ END)  AS ShippingCity,
            (CASE WHEN CHARINDEX(',', @ShippingCSZ ) > 0 THEN SUBSTRING(@ShippingCSZ, CHARINDEX(',', @ShippingCSZ )+2, 2 ) ELSE '' END) AS ShippingState,
            (CASE WHEN CHARINDEX(' ', @ShippingCSZ ) > 0 THEN RIGHT(@ShippingCSZ, CHARINDEX(' ', REVERSE(@ShippingCSZ) )-1 ) ELSE '' END) AS ShippingPostalCode,
            Terms,
            PONum,
            DATEADD(HOUR, 16, CONVERT(SMALLDATETIME, DueDate))      -- Place the due time at 4pm
        FROM #Temp_IIF_Orders;
 
    OPEN OrderList;
    FETCH NEXT FROM OrderList INTO 
        @RowID               ,
        @AccountID           ,
        @ContactID           ,
        @OrderPlacedDate     ,
        @RepFirstName        ,
        @RepLastName         ,
        @Amount              ,
        @DocNum              ,
        @OrderDescription    ,
        @ShipVia             ,
        @IsTaxExempt         ,
        @ShipToCompanyName   ,
        @ShippingAddress1    ,
        @ShippingAddress2    ,
        @ShippingCity        ,
        @ShippingState       ,
        @ShippingPostalCode  ,
        @Terms               ,
        @PONumber            ,
        @OrderDueDate
    ;
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- For each customer, create a separate transaction
    BEGIN TRANSACTION
    BEGIN TRY
            PRINT @DocNum;
 
            -- Look up or fix up some fields
            SET @OrderDescription    = 'VIG South #'+CONVERT(VARCHAR(12), @DocNum)+
                                        (CASE WHEN @OrderDescription = '' THEN '' ELSE ' : '+@OrderDescription END);
            SET @SalespersonID        = COALESCE( (SELECT ID FROM Employee WHERE FirstName = @RepFirstName AND LastName = @RepLastName), 10);
            SET @OrderNotes         = 'Reference Vivid South Order #'+CONVERT(VARCHAR(12), @DocNum);
 
            -- Lookup the Tax Class from the #Temp_IIF_SalesTax table
            SET @TaxClassName       = (SELECT TOP(1) InvItem FROM #Temp_IIF_SalesTax WHERE DocNum = @DocNum );
            SET @TaxClassID         = dbo.csf_MapTaxClassByName(@TaxClassName);
 
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            EXEC csp_ImportOrder 
 
                -- OUTPUT Variables to Capture the Key
                @THID                = @THID        OUTPUT,
 
                -- Input Variables
                @AccountID            = @AccountID,
                @ContactID            = @ContactID,
                @DivisionID         = @DivisionID,
 
                @OrderDescription   = @OrderDescription,   -- The description of the order
                @OrderPlacedDate    = @OrderPlacedDate,  -- The create date *AND TIME* of the order.  Leave blank to use current date and time
                @OrderBuiltDate     = @DT,
                @OrderSaleDate      = @DT,
                @OrderDueDate       = @OrderDueDate,     -- The create date *AND TIME* of the order.  Leave blank to use current date and time
                @OrderNotes         = @OrderNotes,
                @OrderProductionNotes= NULL,
 
                @OrderStationID     = @OrderStationID,
                @SaleStationID      = @SaleStationID,
 
                @OriginalOrderNumber    = @DocNum,      -- Pass the original order number
 
 
                @Salesperson1ID     = @SalespersonID,  
                @PONumber           = @PONumber,       
                @IsTaxExempt        = @IsTaxExempt,    
                @TaxClassID         = @TaxClassID,     
 
                -- Shipping Address Info
                @IsShipped          = 0,
                --@UseOneTimeShippingAddress  = 1,        -- Set to 1 if adding a new shiping address.  This address is then added as a one-time use address.
                --@ShippingAddress1    = @ShippingAddress1,
                --@ShippingAddress2    = @ShippingAddress2,
                --@ShippingCity                = @ShippingCity,
                --@ShippingState                = @ShippingState,
                --@ShippingPostalCode        = @ShippingPostalCode,
                --@ShippingNotes      = NULL,     -- Shipping notes
 
                -- Other configuration fields
                @StatusID           = @OrderStatusID,        
                @RecomputeOnSave    = 0,
                @RefreshOnSave      = 0,
                @IsPriceLocked      = @IsPriceLocked,
                @IsServiceTicket    = 0
            ;         
 
            SET @NewOrders  = @NewOrders + 1;
            PRINT '    Order Created';
 
            -- now save the THID back in the temp table
            --
            UPDATE #Temp_IIF_Orders
            SET THID = @THID
            WHERE RowID = @RowID;
 
            -- Make it so, #1
            COMMIT TRANSACTION;
 
        END TRY
 
        BEGIN CATCH
            ROLLBACK TRANSACTION;
 
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            VALUES(
                @THID, 10000, @AccountID, 1,
                'Original Order '+CONVERT(VARCHAR(12),@DocNum)+'  Import FAILED due to Unhandled Exception.',
                'Exception: '+ERROR_MESSAGE()
            );
        END CATCH;
 
        -- Now continue to the next order
        FETCH NEXT FROM OrderList INTO 
            @RowID               ,
            @AccountID           ,
            @ContactID           ,
            @OrderPlacedDate     ,
            @RepFirstName        ,
            @RepLastName         ,
            @Amount              ,
            @DocNum              ,
            @OrderDescription    ,
            @ShipVia             ,
            @IsTaxExempt         ,
            @ShipToCompanyName   ,
            @ShippingAddress1    ,
            @ShippingAddress2    ,
            @ShippingCity        ,
            @ShippingState       ,
            @ShippingPostalCode  ,
            @Terms               ,
            @PONumber            ,
            @OrderDueDate
    END;  
 
    CLOSE OrderList;
    DEALLOCATE OrderList;
 
    -------------------------------------------------------------------
    -- Map the IIF Line Items Transactions (non-shipment and sales tax)
    -------------------------------------------------------------------
 
    --    Create #Temp_IIF_Items temp table for the line items
    SELECT
        -- ID fields looked up or assigned
        RowID,
        ROW_NUMBER() OVER (ORDER BY RowID) AS ItemRowNum,
        CONVERT(INT, 0) AS THID,
 
        -- Straight data fields from IIF File
        Col02 AS SplID ,
        Col03 AS TrnsType ,
        CONVERT(DATE, Col04) AS OrderDate,
        Col05 AS Accnt ,
        Col06 AS Name ,
        Col07 AS Class ,
        (CASE WHEN ISNUMERIC(Col08)=1 THEN CONVERT(DECIMAL(18,4), Col08) ELSE NULL END) AS Amount,
        CONVERT(INT, Col09) AS DocNum ,
        Col10 AS Memo ,
        (CASE WHEN COALESCE(Col11, '') = '' THEN NULL WHEN Col11 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS Clear,
        (CASE WHEN ISNUMERIC(Col12)=1 THEN CONVERT(DECIMAL(18,4), Col12) ELSE NULL END) AS Qnty,
        (CASE WHEN ISNUMERIC(Col13)=1 THEN CONVERT(DECIMAL(18,4), Col13) ELSE NULL END) AS Price,
        Col14 AS InvItem,
        Col15 AS PayMeth,
        (CASE WHEN COALESCE(Col16, '') = '' THEN NULL WHEN Col16 = 'Y' THEN CONVERT(bit, 1) ELSE CONVERT(bit, 0) END) AS Taxable,
        Col17 AS ValAdj,
        (CASE WHEN COALESCE(Col18, '') <> '' THEN CONVERT(DATE, Col18) ELSE NULL END)  AS ServiceDate,
        Col19 AS Extra,
        Col20 AS CustomID 
    INTO #Temp_IIF_Items
    FROM  #Temp_IIFFileCols
    WHERE (Col01 = 'SPL') AND (Col05 <> 'Sales Tax Payable')
    ;
 
    -- Now fill in the THID by matching on the DocNum
 
    UPDATE #Temp_IIF_Items
    SET     THID    = (SELECT O.THID 
                    FROM #Temp_IIF_Orders O
                    WHERE O.DocNum = #Temp_IIF_Items.DocNum ) ;
 
    -------------------------------------------------------------------
    -- Import the Line Items
    -------------------------------------------------------------------
    --
    -- Loop through the line item data in the import file and create them
    --
    DECLARE ItemList CURSOR FOR
        SELECT
            RowID                        ,    
            THID                        ,
            Accnt AS GLAccountName      ,
            -Amount AS BasePrice        ,
            DocNum                      ,
            Memo AS Description         ,
            -Qnty AS Quantity           ,
            Price AS UnitPrice          ,
            InvItem AS ProductName      ,
            (CASE WHEN Taxable = 1 THEN 0 ELSE 1 END) AS IsTaxExempt,             
            (CASE WHEN Accnt = 'Freight Income' THEN 1 ELSE 0 END) AS IsFreight
        FROM #Temp_IIF_Items
        WHERE THID IS NOT NULL;  -- Don't import if no THID, since that means Order Failed
 
    OPEN ItemList;
    FETCH NEXT FROM ItemList INTO 
        @RowID          ,
        @THID           ,
        @GLAccountName  ,
        @BasePrice      ,
        @DocNum         ,
        @Description    ,
        @Quantity       ,
        @UnitPrice      ,
        @ProductName    ,
        @IsTaxExempt    ,
        @IsFreight
    ;
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- For each customer, create a separate transaction
    BEGIN TRANSACTION
    BEGIN TRY
 
            -- If Freight, check if we need to map freight to another field
            IF (@IsFreight=1) 
            BEGIN
                -- Check if we need to map freight to another field
                SET @ProductName = COALESCE(@AltFreightProduct, @ProductName);
            END;
 
            -- And set a quantity since it is usually 0
            IF (COALESCE(@Quantity,0) = 0)  SET @Quantity = 1; 
 
 
            -- Now create the line items
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            EXEC csp_ImportLineItem 
 
                -- OUTPUT Variables to Capture the Key
                @TDID                = @TDID        OUTPUT,
 
                -- Input Variables
                @THID                = @THID,
 
                @ProductName        = @ProductName,
                @Quantity           = @Quantity,
                @BasePrice          = @BasePrice,
                @BasePriceOV        = 1,
                -- @Discount           = 0.0,
                -- @Tax                = 0.0,
 
                @Description        = @Description,   -- The description of the order
                @ProductionNotes    = 'Imported from Vivid South',
 
                -- Other configuration fields
                @RecomputeOnSave    = 0,
                @RefreshOnSave      = 0,
                @AddJournal            = 0,
                @StationID          = NULL
            ;         
 
            PRINT '    Item Created';
 
            -- Make it so, #1
            COMMIT TRANSACTION;
 
        END TRY
 
        BEGIN CATCH
            ROLLBACK TRANSACTION;
 
            SET @OrderNumber = (SELECT OrderNumber FROM TransHeader WHERE ID = @THID);
 
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            VALUES(
                NULL, 10100, @THID, 1,
                'Line Item '+@ProductName+' Import for Order #'+CONVERT(VARCHAR(12),@OrderNumber)+' FAILED due to Unhandled Exception.',
                'Exception: '+ERROR_MESSAGE()
            );
 
        END CATCH;
 
        -- Now continue to the next Item
        FETCH NEXT FROM ItemList INTO 
            @RowID          ,
            @THID           ,
            @GLAccountName  ,
            @BasePrice      ,
            @DocNum         ,
            @Description    ,
            @Quantity       ,
            @UnitPrice      ,
            @ProductName    ,
            @IsTaxExempt    ,
            @IsFreight      ;
 
    END;  
 
    CLOSE ItemList;
    DEALLOCATE ItemList;
 
    -------------------------------------------------------------------
    -- Map the IIF Sales Tax Lines
    -------------------------------------------------------------------
 
    -- --    Create the #Temp_IIF_SalesTax table for the sales tax
    -- SELECT
    --    RowID,
    --    ROW_NUMBER() OVER (Order by RowID) AS ItemRowNum,
    --    CONVERT(INT, 0) AS THID,
    --
    --    Col02 AS SplID ,
    --    Col03 AS TrnsType ,
    --    convert(Date, Col04) AS [Date] ,
    --    Col05 AS Accnt ,
    --    Col06 AS Name ,
    --    Col07 AS Class ,
    --    (case when ISNUMERIC(Col08)=1 THEN convert(DECIMAL(18,4), Col08) ELSE NULL END) AS Amount,
    --    convert(INT, Col09) AS DocNum ,
    --    Col10 AS Memo ,
    --    (case when Coalesce(Col11, '') = '' then NULL when Col11 = 'Y' then convert(bit, 1) else convert(bit, 0) end) AS Clear,
    --    (case when ISNUMERIC(Col12)=1 THEN convert(DECIMAL(18,4), Col12) ELSE NULL END) AS Qnty,
    --    (case when ISNUMERIC(Col13)=1 THEN convert(DECIMAL(18,4), Col13) ELSE NULL END) AS Price,
    --    Col14 AS InvItem,
    --    Col15 AS PayMeth,
    --    (case when Coalesce(Col16, '') = '' then NULL when Col16 = 'Y' then convert(bit, 1) else convert(bit, 0) end) AS Taxable,
    --    Col17 AS ValAdj ,
    --    (case when Coalesce(Col18, '') <> '' then convert(date, Col18) else NULL end)  AS ServiceDate,
    --    Col19 AS Extra ,
    --    Col20 AS CustomID 
    -- INTO #Temp_IIF_SalesTax
    -- FROM #Temp_IIFFileCols
    -- WHERE (Col01 = 'SPL') AND (Col05 ='Sales Tax Payable')
    -- ;
 
    -- Now fill in the THID by matching on the DocNum
 
    -- Update #Temp_IIF_SalesTax
    -- SET     THID    = (SELECT O.THID 
    --                   FROM #Temp_IIF_Orders O
    --                   WHERE O.DocNum = #Temp_IIF_SalesTax.DocNum ) ;
 
    -------------------------------------------------------------------
    -- Import the Sales Tax
    -------------------------------------------------------------------
    --
    -- FUTURE TASK (if required)
    --
 
    -------------------------------------------------------------------
    -- Now Loop Through and RECOMPUTE all the orders
    -------------------------------------------------------------------
 
    DECLARE RecomputeList CURSOR FOR
        SELECT
            RowID,    
            THID
        FROM #Temp_IIF_Orders
        WHERE THID IS NOT NULL;  -- Don't recompute if no THID, since that means Order Failed
 
    OPEN RecomputeList;
    FETCH NEXT FROM RecomputeList INTO 
        @RowID  ,
        @THID   
    ;
 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- For each customer, create a separate transaction
    BEGIN TRY
            SET @OrderNumber = (SELECT OrderNumber FROM TransHeader WHERE ID = @THID);
 
            PRINT @THID;
 
            EXEC dbo.csf_chapi_recompute @THID, 10000, 'Recompute on Import';
 
            PRINT '    Order Recomputed';
 
        END TRY
 
        BEGIN CATCH
            INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
            VALUES(
                @THID, 10100, NULL, 1,
                'Exception in RECOMPUTE',
                'Exception in RECOMPUTE: '+ERROR_MESSAGE()
            );
 
        END CATCH;
 
        -- Now continue to the next order
        FETCH NEXT FROM RecomputeList INTO 
            @RowID  ,
            @THID   ;
    END;  
 
    CLOSE RecomputeList;
    DEALLOCATE RecomputeList;
 
    -------------------------------------------------------------------
    ---- Move the Outut File if we are all successful and a path is given
    -------------------------------------------------------------------
    --
    -- Note: This requires that the 
 
    IF (@SuccessSubFolderName IS NOT NULL) AND NOT EXISTS(SELECT 1 FROM @Logs WHERE IsError = 1)
    BEGIN
        DECLARE @CMD VARCHAR(512) =  
            'MOVE "' + @ImportFile + '" "' + @ImportPath + @SuccessSubFolderName + '"';
 
        DECLARE @MoveResults TABLE(VALUE VARCHAR(255));
 
        INSERT INTO @MoveResults
            EXEC XP_CMDSHELL @CMD;
 
        INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
        SELECT NULL, NULL, NULL, 0,
            'Import File Moved',
            'Import File moved from "' + @ImportFile + '" to "' + @ImportPath + @SuccessSubFolderName + '''  : '+VALUE
        FROM @MoveResults
        WHERE VALUE IS NOT NULL;
 
    END;
 
    -------------------------------------------------------------------
    ---- Output an Informational Summary of Results
    -------------------------------------------------------------------
    --
    IF (@DeveloperMode = 1)
    BEGIN
        SELECT (CASE WHEN IsError = 1 THEN 'ERROR' ELSE 'Information' END) AS [Message TYPE], *
        FROM @Logs
        ORDER BY IsError DESC, ClassTypeID, COALESCE(ParentID, ID);
 
        SELECT * FROM #Temp_IIF_Customers;
        SELECT * FROM #Temp_IIF_Orders;
        SELECT * FROM #Temp_IIF_Items;
    END
    ELSE
    BEGIN
        SELECT (CASE WHEN IsError = 1 THEN 'ERROR' ELSE 'Information' END) AS [Message TYPE],
            Summary, ParentID, ID, ClassTypeID, Detail 
        FROM @Logs
        ORDER BY IsError DESC, ClassTypeID, COALESCE(ParentID, ID);
    END;
 
END;    

Source

Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+

You could leave a comment if you were logged in.