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 sucecssful.
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 SQLBridge in order to obtain IDs for the inserted records. SQL Bridge is a collection of SQL Server stored prodedures 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+
See Also
- Backlinks include_pagepage_componentbacklinks
You could leave a comment if you were logged in.