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 address (with Address Links if desired) in Control.
The following valuescan be supplied:
- @
If the following values are supplied, links for these records will be created automatically for this address
- @
You can use these output parameters to retreive the IDs created:
- @AddressID - The Address.ID of the new record
- @AddressLinkID - The _first_ AddressLink.ID created. Subsquent links use incremented numbers.
The Stored Procedure returns
- AddressID,
- AddressClassTypeID
- AddressLinkID
- AddressLinkClassTypeID
Notes:
- In general, don't set values you want to use the default to. For instance, if you set the @TaxClassID to the value the customer is set to, this will force it to be overridden and it won't change even if the customer's tax class is updated. Just leave these columns out of the call if you want to use the default value.
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 creates an address and links it to the shipment and to contact record:
– Figure out the ID of the order
DECLARE @THID int = (Select ID from TransHeader where OrderNumber = 1975 and TransactionType = 1);
EXEC dbo.csp_ImportAddress
@StreetAddress1 = '9432 Common St'
, @StreetAddress2 = 'Suite C'
, @City = 'Baton Rouge'
, @State = 'LA'
, @PostalCode = '70809'
, @AddressName = 'Corporate Office'
, @LinkTHID = @THID
, @LinkTHType1ID = 10 -- Shipping
;
This creates an address and links it to the Company record:
– [Optiona] Create variables to capture the results
DECLARE @AddressID INT = NULL;
DECLARE @AddressLinkID INT = NULL;
– Figure out what company
DECLARE @AccountID int = (Select ID from Account where CompanyName like 'ABC %');
EXEC dbo.csp_ImportAddress
@StreetAddress1 = '9432 Common St'
, @StreetAddress2 = 'Suite C'
, @City = 'Baton Rouge'
, @State = 'LA'
, @PostalCode = '70809'
, @LinkAccountID = @AccountID
, @LinkAccountType1ID = 10 -- Billing
, @LinkAccountType2ID = 11 -- Shipping
-- Some OUTPUT Parameters in case the caller wants any of these value back
, @AddressID = @AddressID OUTPUT
, @AddressLinkID = @AddressLinkID OUTPUT
;
Stored Procedure
The SQL to create the order import stored procedure follows. This must be run to create the stored procedure before it can be used.
-- Author: Cyrious Sofware
-- Create date: May-2016
--– Description: This stored procedure adds an address to an order, contact, or company
-- Returns: New AddressID, AddressClassTypeID
CREATE PROCEDURE csp_ImportAddress
-- Input Parameter Values
@StreetAddress1 VARCHAR(60) = NULL,
@StreetAddress2 VARCHAR(60) = NULL,
@City VARCHAR(25) = NULL,
@State VARCHAR(25) = NULL,
@PostalCode VARCHAR(25) = NULL,
@County VARCHAR(25) = NULL,
@Country VARCHAR(25) = 'US',
@IsOneTimeAddress BIT = 0,
@AddressName VARCHAR(25) = NULL,
@RefreshOnSave BIT = 1, -- Set to 0 to NOT trigger a refresh in the link parent ()
-- if you must supply the following ID values, AddressLinks will be created for them
-- To create Links to a TransHeader
@LinkTHID INT = NULL, -- the ID of the order record
@LinkTHType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary
@LinkTHType2ID TINYINT = NULL, -- Set to NULL to only create 1 link
-- To create Links to a Customer
@LinkAccountID INT = NULL, -- the ID of the order record
@LinkAccountType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary
@LinkAccountType2ID TINYINT = NULL, -- Set to NULL to only create 1 link
-- To create Links to a Contact
@LinkContactID INT = NULL, -- the ID of the order record
@LinkContactType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary
@LinkContactType2ID TINYINT = NULL, -- Set to NULL to only create 1 link
-- To create Links to a Shipment
@LinkShipmentID INT = NULL, -- the ID of the order record
@LinkShipmentType1ID TINYINT = NULL, -- Set to 10 for billing, 11 for shipping, NULL for temporary
-- Some OUTPUT Parameters in case the caller wants any of these value back
@AddressID INT = NULL OUTPUT, -- New Address ID.
@AddressLinkID INT = NULL OUTPUT -- New AddressLink ID for the FIRST address link.
AS
BEGIN
-- 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 @ProcName VARCHAR(50) = OBJECT_NAME(@@PROCID);
-- Avoid duplicate postings
IF (@LinkTHType1ID = @LinkTHType2ID) SET @LinkTHType2ID = NULL;
IF (@LinkAccountType1ID = @LinkAccountType2ID) SET @LinkAccountType2ID = NULL;
IF (@LinkContactType1ID = @LinkContactType2ID) SET @LinkContactType2ID = NULL;
--
-- Step 1. Declare some variable and look up some information
--
SET @AddressID = (SELECT dbo.csf_chapi_nextid( 4001, 1)); -- Address ID
DECLARE @LinkCount INT;
SET @LinkCount = (
IsNumeric(@LinkTHID) + IsNumeric(@LinkTHType2ID)
+ IsNumeric(@LinkAccountID) + IsNumeric(@LinkAccountType2ID)
+ IsNumeric(@LinkContactID) + IsNumeric(@LinkContactType2ID)
+ IsNumeric(@LinkShipmentID)
);
DECLARE @IsMaster BIT = 1; -- Use this to set the first on to IsMaster ...
IF (@LinkCount> 0)
SET @AddressLinkID = (SELECT dbo.csf_chapi_nextid( 4002, @LinkCount)); -- Address Links
--
-- Step 2. Create the Records
--
BEGIN TRANSACTION
BEGIN TRY
-- Insert Address
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
( @AddressID --
, -1 -- ,
, 4001 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @StreetAddress1 --
, @StreetAddress2 --
, @City --
, @State --
, @County --
, @PostalCode --
, @Country --
, @StreetAddress1 + @NewLine
+ (CASE WHEN len(COALESCE(@StreetAddress2, ''))> 1 THEN @StreetAddress2 + @NewLine ELSE '' END)
+ @City + ', ' + @State + ' ' + @PostalCode --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
);
-- INSERT Account Link ID if desired
IF (@LinkAccountID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @IsMaster --
, @LinkAccountID --
, 2000 --
, @LinkAccountType1ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkAccountType1ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkAccountType1ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkAccountID and ParentClassTypeID = 2000
AND AddressTypeID = @LinkAccountType1ID
AND AddressID != @AddressID;
END;
-- Update the Company if the Link is changing the Billing Address
IF (@LinkAccountType1ID = 10)
UPDATE Account
SET SeqID = SeqID + 1, BillingAddressID = @AddressID
WHERE ID = @LinkAccountID
;
-- Update the Company if the Link is changing the Shipping Address
IF (@LinkAccountType1ID = 11)
UPDATE Account
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID
WHERE ID = @LinkAccountID
;
-- See if we need to insert a second Link
IF (@LinkAccountType2ID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem]
, [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName]
, [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, 0 --
, @LinkAccountID --
, 2000 --
, @LinkAccountType2ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkAccountType2ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkAccountType2ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkAccountID and ParentClassTypeID = 2000
AND AddressTypeID = @LinkAccountType2ID
AND AddressID != @AddressID;
END;
-- Update the Company if the Link is changing the Billing Address
IF (@LinkAccountType2ID = 10)
UPDATE Account
SET SeqID = SeqID + 1, BillingAddressID = @AddressID
WHERE ID = @LinkAccountID
;
-- Update the Company if the Link is changing the Shipping Address
IF (@LinkAccountType2ID = 11)
UPDATE Account
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID
WHERE ID = @LinkAccountID
;
END;
SET @IsMaster = 0;
END;
-- INSERT Contact Link ID if desired
IF (@LinkContactID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @IsMaster --
, @LinkContactID --
, 3000 --
, @LinkContactType1ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkContactType1ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkContactType1ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkContactID and ParentClassTypeID = 3000
AND AddressTypeID = @LinkContactType1ID
AND AddressID != @AddressID;
END;
-- Update the Contact if the Link is changing the Billing Address
IF (@LinkContactType1ID = 10)
UPDATE AccountContact
SET SeqID = SeqID + 1, BillingAddressID = @AddressID
WHERE ID = @LinkContactID
;
-- Update the Company if the Link is changing the Shipping Address
IF (@LinkContactType1ID = 11)
UPDATE AccountContact
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID
WHERE ID = @LinkContactID
;
-- See if we need to insert a second Link
IF (@LinkAccountType2ID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem]
, [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName]
, [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, 0 --
, @LinkContactID --
, 3000 --
, @LinkContactType2ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkContactType2ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkContactType2ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkContactID and ParentClassTypeID = 3000
AND AddressTypeID = @LinkContactType2ID
AND AddressID != @AddressID;
END;
-- Update the Contact if the Link is changing the Billing Address
IF (@LinkContactType2ID = 10)
UPDATE AccountContact
SET SeqID = SeqID + 1, BillingAddressID = @AddressID
WHERE ID = @LinkContactID
;
-- Update the Contact if the Link is changing the Shipping Address
IF (@LinkContactType2ID = 11)
UPDATE Account
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID
WHERE ID = @LinkContactID
;
END;
SET @IsMaster = 0;
END;
-- INSERT TransHeader Link ID if desired
IF (@LinkTHID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem]
, [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName]
, [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @IsMaster --
, @LinkTHID --
, 10000 --
, @LinkTHType1ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkTHType1ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkTHType1ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkTHID and ParentClassTypeID = 10000
AND AddressTypeID = @LinkTHType1ID
AND AddressID != @AddressID;
END;
-- Update the Order if the Link is changing the Billing Address
IF (@LinkTHType1ID = 10)
BEGIN
UPDATE TransHeader
SET SeqID = SeqID + 1, InvoiceAddressID = @AddressID, InvoiceAddressLinkID = @AddressLinkID + @LinkCount
WHERE ID = @LinkTHID;
END;
;
-- Update the Company if the Link is changing the Shipping Address
IF (@LinkTHType1ID = 11)
UPDATE TransHeader
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID, ShippingAddressLinkID = @AddressLinkID + @LinkCount
WHERE ID = @LinkTHID
;
-- See if we need to insert a second Link
IF (@LinkTHType2ID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem]
, [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName]
, [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, 1 --
, @LinkTHID --
, 10000 --
, @LinkTHType2ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkTHType2ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
-- Delete the old Billing or Shipping Address Link if we are replacing it
IF (@LinkTHType2ID in (10,11))
BEGIN
DELETE FROM AddressLink
WHERE ParentID = @LinkTHID and ParentClassTypeID = 10000
AND AddressTypeID = @LinkTHType2ID
AND AddressID != @AddressID;
END;
-- Update the Order if the Link is changing the Billing Address
IF (@LinkTHType2ID = 10)
UPDATE TransHeader
SET SeqID = SeqID + 1, InvoiceAddressID = @AddressID, InvoiceAddressLinkID = @AddressLinkID + @LinkCount
WHERE ID = @LinkTHID
;
-- Update the Company if the Link is changing the Shipping Address
IF (@LinkTHType2ID = 11)
UPDATE TransHeader
SET SeqID = SeqID + 1, ShippingAddressID = @AddressID, ShippingAddressLinkID = @AddressLinkID + @LinkCount
WHERE ID = @LinkTHID
;
END;
SET @IsMaster = 0;
END;
-- INSERT Shipment Link ID if desired
IF (@LinkShipmentID IS NOT NULL)
BEGIN
SET @LinkCount = @LinkCount - 1;
INSERT INTO [dbo].[AddressLink] ( [ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive], [IsMaster], [ParentID], [ParentClassTypeID], [AddressTypeID], [AddressID], [AddressName], [IsOneTimeCompany])
VALUES
( @AddressLinkID + @LinkCount --
, -1 -- ,
, 4002 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @IsMaster --
, @LinkShipmentID --
, 10700 --
, @LinkShipmentType1ID -- Billing Address --
, @AddressID --
, coalesce(@AddressName,
case @LinkShipmentType1ID when 10 then 'Billing' when 11 then 'Shipping' else 'Temporary' end)
, @IsOneTimeAddress --
);
END;
-- Now commit the Transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
DECLARE @ErrorMessage VARCHAR(2048);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure VARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
END CATCH;
--
-- Step 4. Refresh the Records
--
IF (@RefreshOnSave=1)
BEGIN
IF (@LinkTHID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkTHID, 10000, -1;
IF (@LinkAccountID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkAccountID, 2000, -1;
IF (@LinkContactID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkContactID, 3000, -1;
IF (@LinkShipmentID IS NOT NULL) EXEC dbo.csf_chapi_refresh @LinkShipmentID, 10700, -1;
END;
--
-- Step 5. Return the New Detail ID
--
SELECT @AddressID, 4001, @AddressLinkID, 4002
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.