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.