SQL Stored Procedure - Import Company
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 add a new customer and contact. Not all of the information you can add in Control is included in this standard stored procedure, though someone familiar with SQL could extend this accordingly. All of the records inserted are wrapped in a single transaction. This ensures that the records are either all inserted, or none are inserted.
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 inserts basic customer and contact.
EXEC dbo.csp_ImportCompany @CompanyName = 'Basic Company', @URL = 'www.basics.com', -- Office Phone #1 @WorkPhoneAC = '225', @WorkPhoneNumber = '7522867', -- Company Billing Address Info @BStreetAddress1 = '12627 Jefferson Highway', @BStreetAddress2 = NULL, @BCity = 'Baton Rouge', @BState = 'LA', @BPostalCode = '70816', @Notes = 'Imported using SQLBridge', -- Contact #1 Information @AddContact = 1, -- Set to 1 to create a contract. Otherwise you will have to add the contacts manually. @Title = 'Mr.', @FirstName = 'John', @LastName = 'Paul', @POSITION = 'Owner', @EmailAddress = 'jpaul@NewCo.com'
This inserts a sample customer with
- a different billing and shipping addresses,
- a cell phone for the contact
- Some other company info
EXEC dbo.csp_ImportCompany @CompanyName = 'NewCo #5', @URL = 'www.newco.com', @PONumber = '1234', @PORequired = 0, -- false @SalespersonID = 10, -- Salesperson1 ID. 10 = House Account, or choose ID from Employee table where IsSalesperson = 1 -- Office Phone #1 @WorkPhoneAC = '225', @WorkPhoneNumber = '7522867', -- Company Billing Address Info @BStreetAddress1 = '12627 Jefferson Highway', @BStreetAddress2 = NULL, @BCity = 'Baton Rouge', @BState = 'LA', @BPostalCode = '70816', -- Company Shipping Address Info @ShippingAddressSameAsBilling = 0, -- Set to 1 to use the same shipping as billing address @SStreetAddress1 = '9432 Common St', @SStreetAddress2 = NULL, @SCity = 'Baton Rouge', @SState = 'LA', @SPostalCode = '70809', -- Add other company information @DivisionID = 10, -- must be an ID from the Division View. Use 10 if not using Divisions. @IndustryID = NULL, -- if provided, this must match an industry name in the Element Table @IndustryText = NULL, -- if provided this must match an *existing* industry. @RegionID = NULL, -- if provided, this must match an region in the Element Table @RegionText = NULL, -- if provided this must match an *existing* region. @OriginID = NULL, -- if provided, this must match an origin in the Element Table @OrginText = NULL, -- if provided this must match an existing origin. @Notes = 'Imported using SQLBridge', @Flags = 'VIP!', -- Contact #1 Information @AddContact = 1, -- Set to 1 to create a contract. Otherwise you will have to add the contacts manually. @Title = 'Mr.', @FirstName = 'John', @LastName = 'Paul', @POSITION = 'Owner', @EmailAddress = 'jpaul@NewCo.com', -- Contact Additional Phone #1 @AddCellPhone = 1, -- Set to 1 to Add a Cell Phone to the contact @CellPhoneAC = '225', @CellPhoneNumber = '900-8765'
Stored Procedure
-- ============================================= -- Author: Cyrious Software -- Create date: May-2016 -- Description: This stored procedure imports a customer record into Control. -- Many of the parameters are option, but if not supplied will used -- The default behavior. -- -- Returns: New ID for Customer (Account.ID) -- ============================================= ALTER PROCEDURE csp_ImportCompany -- Required Fields @CompanyName VARCHAR(128), -- Optional Ccmpany Fields Follow @URL VARCHAR(128) = '', @PONumber VARCHAR(16) = '', @PORequired bit = 0, -- false @SalespersonID INT = 10, -- Salesperson1 ID. 10 = House Account, or choose ID from Employee table where IsSalesperson = 1 @BatchName VARCHAR(255) = NULL, @WorkPhoneAC VARCHAR(10) = NULL, @WorkPhoneNumber VARCHAR(25) = NULL, -- Company Billing Address Info @BillingAddress1 VARCHAR(256) = NULL, @BillingAddress2 VARCHAR(256) = NULL, @BillingCity VARCHAR(256) = NULL, @BillingState VARCHAR(256) = NULL, @BillingPostalCode VARCHAR(256) = NULL, -- Company Shipping Address Info @ShippingAddressSameAsBilling bit = 1, -- Set to 1 to use the same shipping as billing address @ShippingAddress1 VARCHAR(256) = NULL, @ShippingAddress2 VARCHAR(256) = NULL, @ShippingCity VARCHAR(256) = NULL, @ShippingState VARCHAR(256) = NULL, @ShippingPostalCode VARCHAR(256) = NULL, -- Company Tax Class Information. Set one or none of these, but if you set the both, only the ID is used @TaxClassName VARCHAR(255) = NULL, @TaxClassID INT = NULL, -- Add other company information @DivisionID INT = 10, -- must be an ID from the Division View. Use 10 if not using Divisions. @IndustryID INT = NULL, -- if provided, this must match an industry name in the Element Table @IndustryText VARCHAR(255) = NULL, -- if provided, this must match an *existing* industry. @RegionID INT = NULL, -- if provided, this must match an region in the Element Table @RegionText VARCHAR(255) = NULL, -- if provided, this must match an *existing* region. @OriginID INT = NULL, -- if provided, this must match an origin in the Element Table @OrginText VARCHAR(255) = NULL, -- if provided and does not exist, will AUTOMATICALLY add this as an origin @Notes VARCHAR(4096) = '', @Flags VARCHAR(4096) = '', -- Contact #1 Information @AddContact bit = 1, -- Set to 1 to create a contract. Otherwise you will have to add the contacts manually. @Title VARCHAR(25) = NULL, @FirstName VARCHAR(25) = NULL, @LastName VARCHAR(25) = NULL, @POSITION VARCHAR(50) = NULL, @EmailAddress VARCHAR(50) = NULL, -- Contact Additional Phone #1 @AddCellPhone BIT = 0, -- Set to 1 to Add a Cell Phone to the contact @CellPhoneAC VARCHAR(10) = NULL, @CellPhoneNumber VARCHAR(25) = NULL, -- Some OUTPUT Parameters in case the caller wants any of these value back @AccountID INT = NULL OUTPUT, @ContactID INT = NULL OUTPUT, @CompanyNumber INT = NULL OUTPUT, @BillingAddressID INT = NULL OUTPUT, @ShippingAddressID INT = NULL OUTPUT, @JournalID INT = NULL OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @Logs TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT, Summary VARCHAR(255), Detail VARCHAR(2000) ); -- Company info Pulled from Store or looked up DECLARE @PaymentTermsID INT; DECLARE @PhoneCountryCode VARCHAR(10); -- Retrieve Defaults Information from databases SELECT TOP 1 @TaxClassID = COALESCE(DefaultTaxClassID, 50), @PhoneCountryCode = COALESCE(DefaultCountryCode, '1') FROM Store WHERE ID > 0 ORDER BY ID; -- Look up the Tax Class ID if not given. If not name is supplied, the function will provide the default. IF (@TaxClassID IS NULL) SET @TaxClassID = dbo.csf_MapTaxClassByName(@TaxClassName); -- Declare ID fields used in the process; DECLARE @AddressLinkID INT; -- First Address Link ID DECLARE @PhoneNumberID INT; -- First Phone Number ID DECLARE @WorkPhoneType INT = 10; -- Business. From Element Table, where ClassTypeID = 4101 DECLARE @CellPhoneType INT = 12; -- Mobile. From Element Table, where ClassTypeID = 4101 -- 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); -- Step 1, Request New IDs SET @AccountID = (SELECT dbo.csf_chapi_nextid( 2000, 1)); SET @CompanyNumber = (SELECT dbo.csf_chapi_nextnumber( 'CompanyNumber', 1)); SET @BillingAddressID = (SELECT dbo.csf_chapi_nextid( 4001, 2)); -- Address ID for Billing (and Shipping if needed) SET @AddressLinkID = (SELECT dbo.csf_chapi_nextid( 4002, 4)); -- Address Links for Company (and Contact if needed) SET @PhoneNumberID = (SELECT dbo.csf_chapi_nextid( 4100, 3)); -- Work Phone for Company (and Contact Cell if needed) SET @JournalID = (SELECT dbo.csf_chapi_nextid( 20510, 1)); -- Company Activity IF (@AddContact = 1) SET @ContactID = (SELECT dbo.csf_chapi_nextid( 3000, 1)); IF (@ShippingAddressSameAsBilling = 1) SET @ShippingAddressID = @BillingAddressID ELSE SET @ShippingAddressID = @BillingAddressID + 1; -- Step 2, Begin a Transaction so the whole thing succeeds or fails together BEGIN TRANSACTION BEGIN TRY -- -- Step 3, Create the associated Company records -- -- Insert Account (Company) INSERT INTO [dbo].[Account] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[Notes] ,[CompanyName] ,[AccountNumber] ,[ParentID] ,[Department] ,[DateCreated] ,[DateImported] ,[ImportBatch] ,[AccountingContactID] ,[PrimaryContactID] ,[BillingAddressID] ,[ShippingAddressID] ,[MainPhoneNumberID] ,[MainFaxNumberID] ,[Flags] ,[Keywords] ,[TaxNumber] ,[TaxNumberExpDate] ,[TaxClassID] ,[WebAddress] ,[IsProspect] ,[TaxExempt] ,[HasCreditAccount] ,[CreditApprovalDate] ,[CreditLimit] ,[CreditBalance] ,[PricingPlanTypeID] ,[PaymentTermsID] ,[DiscountLevel] ,[PricingLevel] ,[PONumberRequired] ,[IndustryID] ,[OriginID] ,[Marketing3ID] ,[SalesPersonID1] ,[SalesPersonID2] ,[SalesPersonID3] ,[TaxExemptExpDate] ,[CreditNumber] ,[PricingLevelID] ,[PromotionID] ,[UseTaxLookup] ,[HasServiceContract] ,[ServiceContractStartDate] ,[ServiceContractExpDate] ,[ServiceContractTypeID] ,[ServiceContractNotes] ,[DivisionID] ,[RegionID] ,[PONumber] ,[PrimaryNumber] ,[PriNumberTypeID] ,[PriNumberTypeText] ,[SecondaryNumber] ,[SecNumberTypeID] ,[SecNumberTypeText] ,[IsClient] ,[IsVendor] ,[IsPersonal] ,[Is1099Vendor] ,[VendorPaymentTermsID] ,[MyAccountNumber] ,[DefaultShipMethodID] ,[ThirdNumber] ,[ThirdNumberTypeID] ,[ThirdNumberTypeText] ,[IsFullyTaxExempt] ,[VendorCreditBalance] ,[StageID] ,[StageClassTypeID] ,[StageActivityID] ,[StageActivityClassTypeID] ,[LicenseKey]) VALUES ( @AccountID -- <ID, int,> , -1 -- ,<StoreID, int,> , 2000 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @Notes -- <Notes, varchar(max),> , @CompanyName -- <CompanyName, varchar(50),> , @CompanyNumber -- <AccountNumber, int,> , NULL -- <ParentID, int,> , NULL -- <Department, varchar(25),> , @DT -- <DateCreated, datetime,> , @DT -- <DateImported, datetime,> , NULL -- <ImportBatch, varchar(15),> , @ContactID -- <AccountingContactID, int,> , @ContactID -- <PrimaryContactID, int,> , @BillingAddressID -- <BillingAddressID, int,> , @ShippingAddressID -- <ShippingAddressID, int,> , @PhoneNumberID -- <MainPhoneNumberID, int,> , NULL -- <MainFaxNumberID, int,> , @Flags -- <Flags, varchar(max),> , NULL -- <Keywords, varchar(max),> , NULL -- <TaxNumber, varchar(25),> , NULL -- <TaxNumberExpDate, datetime,> , @TaxClassID -- <TaxClassID, int,> , @URL -- <WebAddress, varchar(50),> , 1 -- <IsProspect, bit,> , 0 -- <TaxExempt, bit,> , 0 -- <HasCreditAccount, bit,> , NULL -- <CreditApprovalDate, datetime,> , 0 -- <CreditLimit, float,> , 0 -- <CreditBalance, float,> , 10 -- <PricingPlanTypeID, int,> , @PaymentTermsID -- <PaymentTermsID, int,> , 0.00 -- <DiscountLevel, float,> , 1.00 -- <PricingLevel, float,> , @PORequired -- <PONumberRequired, bit,> , @IndustryID -- <IndustryID, int,> , @OriginID -- <OriginID, int,> , NULL -- <Marketing3ID, int,> , @SalespersonID -- <SalesPersonID1, int,> , NULL -- <SalesPersonID2, int,> , NULL -- <SalesPersonID3, int,> , NULL -- <TaxExemptExpDate, datetime,> , NULL -- <CreditNumber, varchar(25),> , NULL -- <PricingLevelID, int,> , NULL -- <PromotionID, int,> , 0 -- <UseTaxLookup, bit,> , 0 -- <HasServiceContract, bit,> , NULL -- <ServiceContractStartDate, datetime,> , NULL -- <ServiceContractExpDate, datetime,> , NULL -- <ServiceContractTypeID, int,> , NULL -- <ServiceContractNotes, varchar(max),> , @DivisionID -- <DivisionID, int,> , @RegionID -- <RegionID, int,> , @PoNumber -- <PONumber, varchar(25),> , '('+@WorkPhoneAC+') '+@WorkPhoneNumber -- <PrimaryNumber, varchar(75),> , @WorkPhoneType -- <PriNumberTypeID, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType ) -- <PriNumberTypeText, varchar(50),> , '' -- <SecondaryNumber, varchar(75),> , NULL -- <SecNumberTypeID, int,> , '' -- <SecNumberTypeText, varchar(50),> , 0 -- <IsClient, bit,> , 0 -- <IsVendor, bit,> , 0 -- <IsPersonal, bit,> , 0 -- <Is1099Vendor, bit,> , NULL -- <VendorPaymentTermsID, int,> , '' -- <MyAccountNumber, varchar(50),> , NULL -- <DefaultShipMethodID, int,> , '' -- <ThirdNumber, varchar(75),> , NULL -- <ThirdNumberTypeID, int,> , '' -- <ThirdNumberTypeText, varchar(50),> , 0 -- <IsFullyTaxExempt, bit,> , 0.00 -- <VendorCreditBalance, float,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,> , NULL -- <StageActivityID, int,> , NULL -- <StageActivityClassTypeID, int,> , NULL -- <LicenseKey, uniqueidentifier,> ); -- Insert AccountUDF (Nothing by default) INSERT INTO [dbo].[AccountUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ) VALUES ( @AccountID -- <ID, int,> , -1 -- ,<StoreID, int,> , 2001 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> ); -- Insert Billing Address and Address Link INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError]) VALUES ( @BillingAddressID -- <ID, int,> , -1 -- ,<StoreID, int,> , 4001 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @BillingAddress1 -- <StreetAddress1, varchar(40),> , @BillingAddress2 -- <StreetAddress2, varchar(40),> , @BillingCity -- <City, varchar(25),> , @BillingState -- <State, varchar(50),> , '' -- <County, varchar(50),> , @BillingPostalCode -- <PostalCode, varchar(50),> , 'US' -- <Country, varchar(50),> , @BillingAddress1 + @NewLine + (CASE WHEN len(COALESCE(@BillingAddress2, '')) > 1 THEN @BillingAddress2 + @NewLine ELSE '' END) + @BillingCity + ', ' + @BillingState + ' ' + @BillingPostalCode -- <FormattedText, varchar(max),> , NULL -- <TaxClassID, int,> , 0 -- <IsValidated, bit,> , NULL -- <ValidatedAddress, varchar(max),> , 0 -- <HasValidationError, bit,> , NULL -- <ValidationError, varchar(max),> ); INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName]) VALUES ( @AddressLinkID + 0 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 1 -- <IsMaster, bit,> , @AccountID -- <ParentID, int,> , 2000 -- <ParentClassTypeID, smallint,> , 10 -- Billing Address -- <AddressTypeID, tinyint,> , @BillingAddressID -- <AddressID, int,> , 'Billing' -- <AddressName, varchar(50),> , 0 -- <IsOneTimeCompany, bit,> , NULL -- <CompanyName, varchar(100),> , NULL -- <ContactName, varchar(100),> ); -- Insert Shipping Address if different. Insert Shipping Addres Link either way IF (@ShippingAddressID <> @BillingAddressID) BEGIN INSERT INTO [dbo].[Address] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [StreetAddress1], [StreetAddress2], [City], [State], [County], [PostalCode], [Country], [FormattedText], [TaxClassID], [IsValidated], [ValidatedAddress], [HasValidationError], [ValidationError]) VALUES ( @ShippingAddressID -- <ID, int,> , -1 -- ,<StoreID, int,> , 4001 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @ShippingAddress1 -- <StreetAddress1, varchar(40),> , @ShippingAddress2 -- <StreetAddress2, varchar(40),> , @ShippingCity -- <City, varchar(25),> , @ShippingState -- <State, varchar(50),> , '' -- <County, varchar(50),> , @ShippingPostalCode -- <PostalCode, varchar(50),> , 'US' -- <Country, varchar(50),> , @ShippingAddress1 + @NewLine + (CASE WHEN len(COALESCE(@ShippingAddress2, '')) > 1 THEN @ShippingAddress2 + @NewLine ELSE '' END) + @ShippingCity + ', ' + @ShippingState + ' ' + @ShippingPostalCode -- <FormattedText, varchar(max),> , NULL -- <TaxClassID, int,> , 0 -- <IsValidated, bit,> , NULL -- <ValidatedAddress, varchar(max),> , 0 -- <HasValidationError, bit,> , NULL -- <ValidationError, varchar(max),> ); END; INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName]) VALUES ( @AddressLinkID + 1 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , CASE WHEN (@ShippingAddressID = @BillingAddressID) THEN 0 ELSE 1 END -- <IsMaster, bit,> , @AccountID -- <ParentID, int,> , 2000 -- <ParentClassTypeID, smallint,> , 11 -- Shipping Address -- <AddressTypeID, tinyint,> , @ShippingAddressID -- <AddressID, int,> , 'Shipping' -- <AddressName, varchar(50),> , 0 -- <IsOneTimeCompany, bit,> , NULL -- <CompanyName, varchar(100),> , NULL -- <ContactName, varchar(100),> ); -- Insert Business Phone Number INSERT INTO [dbo].[PhoneNumber] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText]) VALUES ( @PhoneNumberID+0 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4100 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @AccountID -- <ParentID, int,> , -1 -- <ParentStoreID, smallint,> , 2000 -- <ParentClassTypeID, smallint,> , @WorkPhoneType -- <PhoneNumberTypeID, int,> , '1' -- <CountryCode, varchar(20),> , @WorkPhoneAC -- <AreaCode, varchar(20),> , @WorkPhoneNumber -- <PhoneNumber, varchar(50),> , '' -- <Extension, varchar(20),> , '('+@WorkPhoneAC+') '+@WorkPhoneNumber -- <FormattedText, varchar(150),> , 0 -- <PhoneNumberIndex, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- <PhoneNumberTypeText, varchar(100),> ); -- -- Step 4, Create the associated CONTACT RECORDS -- IF (@AddContact = 1) BEGIN -- Insert Contact INSERT INTO [dbo].[AccountContact] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[Notes] ,[FirstName] ,[LastName] ,[Title] ,[POSITION], [EmailAddress] ,[MainPhoneNumberID] ,[MainFaxNumberID] ,[AccountID] ,[IsPrimaryContact] ,[IsAccountingContact] ,[BillingAddressID] ,[ShippingAddressID] ,[BirthDateMonth] ,[BirthDateDay] ,[DefaultPaymentExpDate] ,[DefaultPaymentTrackingNumber] ,[DefaultPaymentNameOnCard] ,[DefaultPaymentTypeID] ,[CCBillingAddress] ,[DefaultPaymentVCode] ,[UserID] ,[SalespersonID1] ,[SalespersonID2] ,[SalespersonID3] ,[UseCompanySalespeople] ,[IsCCNumEncrypt] ,[DisplayNumber] ,[BirthDate] ,[IsVCodeEncrypted] ,[PrimaryNumber] ,[PriNumberTypeID] ,[PriNumberTypeText] ,[SecondaryNumber] ,[SecNumberTypeID] ,[SecNumberTypeText] ,[PaymentAddressID] ,[CCSwiped] ,[SendBillingAddress] ,[IDNumber] ,[ImageID] ,[MiddleName] ,[ContactType] ,[GenderType] ,[NumOfMakeups] ,[ThirdNumber] ,[ThirdNumberTypeID] ,[ThirdNumberTypeText] ,[DefaultPaymentBankReference] ,[DefaultPaymentBankCode] ,[DefaultPaymentBranchCode] ,[DefaultPaymentCIN] ,[DefaultPaymentState] ,[DefaultPaymentCCAccount] ,[PaymentAddressLinkID] ,[CCCSCustomerGuid] ,[UseShippingAccountInfo] ,[DefaultShippingAccountNumber] ,[DefaultShippingCarrierID] ,[DefaultShippingCarrierClassTypeID] ,[DefaultShippingAccountPostalCode] ,[ShippingMethodLinksXML] ,[PaymentAddressOV]) VALUES ( @ContactID -- <ID, int,> , -1 -- ,<StoreID, int,> , 3000 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , NULL -- <Notes, nvarchar(max),> , @FirstName -- <FirstName, nvarchar(25),> , @LastName -- <LastName, nvarchar(25),> , @Title -- <Title, nvarchar(10),> , @POSITION -- <Position, nvarchar(25),> , @EmailAddress -- <EmailAddress, nvarchar(50),> , @PhoneNumberID+1 -- <MainPhoneNumberID, int,> , CASE WHEN (@AddCellPhone = 1) THEN @PhoneNumberID+2 ELSE NULL END -- <MainFaxNumberID, int,> , @AccountID -- <AccountID, int,> , 1 -- <IsPrimaryContact, bit,> , 1 -- <IsAccountingContact, bit,> , @BillingAddressID -- <BillingAddressID, int,> , @ShippingAddressID -- <ShippingAddressID, int,> , NULL -- <BirthDateMonth, int,> , NULL -- <BirthDateDay, int,> , NULL -- <DefaultPaymentExpDate, datetime,> , NULL -- <DefaultPaymentTrackingNumber, varchar(25),> , NULL -- <DefaultPaymentNameOnCard, varchar(25),> , NULL -- <DefaultPaymentTypeID, int,> , NULL -- <CCBillingAddress, varchar(25),> , NULL -- <DefaultPaymentVCode, varchar(50),> , NULL -- <UserID, int,> , @SalespersonID -- <SalespersonID1, int,> , NULL -- <SalespersonID2, int,> , NULL -- <SalespersonID3, int,> , 1 -- <UseCompanySalespeople, bit,> , 1 -- <IsCCNumEncrypt, bit,> , '' -- <DisplayNumber, nvarchar(50),> , NULL -- <BirthDate, datetime,> , 1 -- <IsVCodeEncrypted, bit,> , '('+@WorkPhoneAC+') '+@WorkPhoneNumber -- <PrimaryNumber, varchar(75),> , @WorkPhoneType -- <PriNumberTypeID, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- <PriNumberTypeText, varchar(50),> , CASE WHEN (@AddCellPhone = 1) THEN '('+@CellPhoneAC+') '+@CellPhoneNumber ELSE '' END -- <SecondaryNumber, varchar(75),> , @CellPhoneType -- <SecNumberTypeID, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- <SecNumberTypeText, varchar(50),> , NULL -- <PaymentAddressID, int,> , 0 -- <CCSwiped, bit,> , 1 -- <SendBillingAddress, bit,> , NULL -- <IDNumber, nvarchar(50),> , NULL -- <ImageID, int,> , '' -- <MiddleName, nvarchar(50),> , 0 -- <ContactType, int,> , 0 -- <GenderType, int,> , NULL -- <NumOfMakeups, int,> , NULL -- <ThirdNumber, nvarchar(75),> , NULL -- <ThirdNumberTypeID, int,> , NULL -- <ThirdNumberTypeText, varchar(50),> , NULL -- <DefaultPaymentBankReference, varchar(50),> , NULL -- <DefaultPaymentBankCode, varchar(50),> , NULL -- <DefaultPaymentBranchCode, varchar(50),> , NULL -- <DefaultPaymentCIN, varchar(50),> , NULL -- <DefaultPaymentState, varchar(50),> , NULL -- <DefaultPaymentCCAccount, varchar(50),> , NULL -- <PaymentAddressLinkID, int,> , NULL -- <CCCSCustomerGuid, varchar(50),> , 0 -- <UseShippingAccountInfo, bit,> , NULL -- <DefaultShippingAccountNumber, varbinary(100),> , -1 -- <DefaultShippingCarrierID, int,> , -1 -- <DefaultShippingCarrierClassTypeID, int,> , NULL -- <DefaultShippingAccountPostalCode, nvarchar(25),> , NULL -- <ShippingMethodLinksXML, nvarchar(max),> , 0 -- <PaymentAddressOV, bit,> ); -- Insert ContactUDF (Nothing by default) INSERT INTO [dbo].[AccountContactUserField] ([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ) VALUES ( @ContactID -- <ID, int,> , -1 -- ,<StoreID, int,> , 3001 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> ); -- Insert Contact Address Links (Billing) INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName]) VALUES ( @AddressLinkID + 2 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 0 -- <IsMaster, bit,> , @ContactID -- <ParentID, int,> , 3000 -- <ParentClassTypeID, smallint,> , 10 -- Shipping Address -- <AddressTypeID, tinyint,> , @BillingAddressID -- <AddressID, int,> , 'Billing' -- <AddressName, varchar(50),> , 0 -- <IsOneTimeCompany, bit,> , NULL -- <CompanyName, varchar(100),> , NULL -- <ContactName, varchar(100),> ); -- Insert Contact Address Links (Shipping) INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany], [CompanyName], [ContactName]) VALUES ( @AddressLinkID + 3 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4002 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , 0 -- <IsMaster, bit,> , @ContactID -- <ParentID, int,> , 3000 -- <ParentClassTypeID, smallint,> , 11 -- Shipping Address -- <AddressTypeID, tinyint,> , @ShippingAddressID -- <AddressID, int,> , 'Shipping' -- <AddressName, varchar(50),> , 0 -- <IsOneTimeCompany, bit,> , NULL -- <CompanyName, varchar(100),> , NULL -- <ContactName, varchar(100),> ); -- Insert Contact Phone Numbers (Work and Cell) INSERT INTO [dbo].[PhoneNumber] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText]) VALUES ( @PhoneNumberID+1 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4100 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @ContactID -- <ParentID, int,> , -1 -- <ParentStoreID, smallint,> , 3000 -- <ParentClassTypeID, smallint,> , @WorkPhoneType -- <PhoneNumberTypeID, int,> , '1' -- <CountryCode, varchar(20),> , @WorkPhoneAC -- <AreaCode, varchar(20),> , @WorkPhoneNumber -- <PhoneNumber, varchar(50),> , '' -- <Extension, varchar(20),> , '('+@WorkPhoneAC+') '+@WorkPhoneNumber -- <FormattedText, varchar(150),> , 0 -- <PhoneNumberIndex, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @WorkPhoneType) -- <PhoneNumberTypeText, varchar(100),> ); IF (@AddCellPhone = 1) BEGIN INSERT INTO [dbo].[PhoneNumber] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [ParentID], [ParentStoreID], [ParentClassTypeID], [PhoneNumberTypeID], [CountryCode], [AreaCode], [PhoneNumber], [Extension], [FormattedText], [PhoneNumberIndex], [PhoneNumberTypeText]) VALUES ( @PhoneNumberID+2 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4100 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , GetDate() -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @ContactID -- <ParentID, int,> , -1 -- <ParentStoreID, smallint,> , 3000 -- <ParentClassTypeID, smallint,> , @CellPhoneType -- <PhoneNumberTypeID, int,> , '1' -- <CountryCode, varchar(20),> , @CellPhoneAC -- <AreaCode, varchar(20),> , @CellPhoneNumber -- <PhoneNumber, varchar(50),> , '' -- <Extension, varchar(20),> , '('+@CellPhoneAC+') '+@CellPhoneNumber -- <FormattedText, varchar(150),> , 0 -- <PhoneNumberIndex, int,> , (SELECT ElementName FROM Element WHERE ClassTypeID = 4101 AND ID = @CellPhoneType) -- <PhoneNumberTypeText, varchar(100),> ); END; -- of adding cell phone END; -- Of adding contact records -- -- Step 4, Create a not in the JOURNAL record -- INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime] ,[CompletedByID] ,[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID] ,[EndGLGroupID] ,[AccountID] ,[AccountClassTypeID] ,[ContactID] ,[ContactClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[IsVoided] ,[VoidedDateTime] ,[VoidedEntryID] ,[VoidedEntryClassTypeID] ,[VoidedReason] ,[QueryStartDateTime] ,[QueryEndDateTime] ,[ReminderDateTime] ,[ReminderPrompt] ,[PartID] ,[ActivityType] ,[ActivityTypeText] ,[IsBillable] ,[BillableDateTime] ,[UseActualTime] ,[BillingNotes] ,[BillingType] ,[TotalBilledTime] ,[RecurringActivityID] ,[LinkID] ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID]) VALUES ( @JournalID -- (<ID, int,> , -1 -- ,<StoreID, int,> , 20510 -- ,<ClassTypeID, int,> , 'SQLBridge' -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @SalespersonID -- <EmployeeID, int,> , 6 -- <JournalActivityType, int,> , 'Company' -- <JournalActivityText, nvarchar(25),> , 'Company Created' -- <Description, nvarchar(50),> , 'Inserted by SQLBridge' -- <Notes, nvarchar(max),> , @DT -- <StartDateTime, datetime,> , @DT -- <EndDateTime, datetime,> , NULL -- <TotalTime, datetime,> , NULL -- <ScheduledDateTime, datetime,> , @SalespersonID -- <CompletedByID, int,> , @DT -- <CompletedDateTime, datetime,> , 1 -- <IsSummary, bit,> , 1 -- <IsDetail, bit,> , NULL -- <SummaryID, int,> , NULL -- <SummaryClassTypeID, int,> , NULL -- <SummaryAmount, decimal(18,4),> , NULL -- <DetailAmount, decimal(18,4),> , NULL -- <StartGLGroupID, int,> , NULL -- <EndGLGroupID, int,> , @AccountID -- <AccountID, int,> , 2000 -- <AccountClassTypeID, int,> , @ContactID -- <ContactID, int,> , 3000 -- <ContactClassTypeID, int,> , NULL -- <TransactionID, int,> , NULL -- <TransactionClassTypeID, int,> , 0 -- <IsVoided, bit,> , NULL -- <VoidedDateTime, datetime,> , NULL -- <VoidedEntryID, int,> , NULL -- <VoidedEntryClassTypeID, int,> , NULL -- <VoidedReason, nvarchar(max),> , @DT -- <QueryStartDateTime, datetime,> , @DT -- <QueryEndDateTime, datetime,> , NULL -- <ReminderDateTime, datetime,> , 0 -- <ReminderPrompt, bit,> , NULL -- <PartID, int,> , 0 -- <ActivityType, int,> , NULL -- <ActivityTypeText, nvarchar(50),> , 0 -- <IsBillable, bit,> , NULL -- <BillableDateTime, datetime,> , 0 -- <UseActualTime, bit,> , NULL -- <BillingNotes, nvarchar(max),> , 0 -- <BillingType, int,> , 0 -- <TotalBilledTime, float,> , NULL -- <RecurringActivityID, int,> , NULL -- <LinkID, int,> , NULL -- <LinkStoreID, int,> , NULL -- <LinkClassTypeID, int,> , NULL -- <SpecialCode, nvarchar(25),> , @DivisionID -- <DivisionID, int,> , 0 -- <HasCalendarLinks, bit,> , NULL -- <TipRecipientID, int,> , NULL -- <PartClassTypeID, int,> , NULL -- <RecurringClassTypeID, int,> , NULL -- <StationID, int,> , NULL -- <StationClassTypeID, int,> , NULL -- <CurrentState, int,> , NULL -- <StageID, int,> , NULL -- <StageClassTypeID, int,>) ); -- -- Step 5, COMMIT the transaction to the database -- COMMIT TRANSACTION END TRY -- -- Step 6, Handle ERRORS by cancelling the transaction and re-raising the error -- BEGIN CATCH ROLLBACK TRANSACTION; INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail) VALUES( NULL, 2000, NULL, 1, 'Company Name ['+@CompanyName+'] Import FAILED due to Unhandled Exception.', 'Exception: '+ERROR_MESSAGE() ); RETURN; END CATCH; -- -- Step 7, REFRESH the Order Record to let copies of Control konw there is a new company -- EXEC dbo.csf_chapi_refresh @AccountID, 2000, 0; -- Now Return Displayable Results INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail) VALUES( @AccountID, 2000, NULL, 0, 'Company Name ['+@CompanyName+'] Imported as Company Number '+CONVERT(VARCHAR(12), @CompanyNumber), '' ); SELECT * FROM @Logs; RETURN; END GO
Source
Contributor: Cyrious Software
Date: 5/2016
Version: Control 5.7+
You could leave a comment if you were logged in.