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 Shipping Information to an order in Control.
Notes:
- This import only supports the option to ship everything in 1 shipment.
- The import will fail if you already have a shipment on the order, unless you specify the option to delete existing Shipments.
- This import requires csp_ImportAddress to create the addresses (if required).
One of the two following values must be supplied:
- @THID INT → The TransHeader.ID of the order
- @OrderNumber INT → The Order Number.
The following *optional* values may be set:
- – Information for Shipping RECIPIENT
- @ShipToContactID INT → Ship To Contact ID for shipping. Defaults to Billing Contact for Order
- @ShipToPhoneAC VARCHAR(5) → Phone Area code for shipping contact. Defaults to Shipping Contact's phone.
- @ShipToPhoneNumber VARCHAR(15) → Phone number for shipping contact. Defaults to Shipping Contact's phone.
- @ShipToEmail VARCHAR(128) → Email Address for Shipping Contact. Defaults to Shipping Contact's email.
- @UseNewShipToAddress BIT → Set to 0 to use the contact shipping address. Set to 1 to add a custom address.
- – The following values only apply when @UseNewShipToAddress = 1
- @SaveShipToAddressInContact BIT → Set to 1 to permanently add a new shiping address to the contact record.
- @ShipToAddressName VARCHAR(256) → The name the address is saved as if @UseNewShipToAddress = 1
- @ShipToStreetAddress1 VARCHAR(256) →
- @ShipToStreetAddress2 VARCHAR(256) →
- @ShipToCity VARCHAR(256) →
- @ShipToState VARCHAR(256) →
- @ShipToPostalCode VARCHAR(256) →
- – Information for Shipping SENDER
- @ShipFromContactID INT → Contact ID for third party shipping (Ship From).
- @ShipFromShippingAccount VARCHAR(256) → Shipping Account Number for 3rd Party Shipping
- @UseNewShipFromAddress BIT → Set to 1 to use the contact shipping address. Set to 0 to add a custom address.
- – The following values only apply when @UseNewShipFromAddress = 1
- @SaveShipFromAddressInContact BIT → Set to 1 to permanently add a new shiping address to the contact record.
- @ShipFromAddressName VARCHAR(256) → The name the address
- @ShipFromStreetAddress1 VARCHAR(256) →
- @ShipFromStreetAddress2 VARCHAR(256) →
- @ShipFromCity VARCHAR(256) →
- @ShipFromState VARCHAR(256) →
- @ShipFromPostalCode VARCHAR(256) →
- – General Fields
- @Notes VARCHAR(2048) → Shipping notes
- @ShippedDueDate DATE →
- @Packages INT →
- @PackageSize VARCHAR(256) →
- @PackageWeight DECIMAL(18,4) →
- @CarrierName VARCHAR(100) →
- @CarrierService VARCHAR(100) →
- @EstimatedCost DECIMAL(18,4) →
- @ActualCost DECIMAL(18,4) →
- @InsuredValue DECIMAL(18,4) →
- @WasShipped BIT → Defaults to true is tracking number entered.
- @ShippedDate DATE → The date the shipment was shipped
- @TrackingNumber VARCHAR(256) →
Additional Options used to manage the process:
- @DeleteExistingShipments BIT → set this option to delete all existing shipments on the order first
- @RecomputeOnSave BIT → set to 1 to have the order to recompute and update on Save. 0 to do it manually afterwards.
- @RefreshOnSave BIT → 0 = Don't Refresh the order on Save, 1 = Do Refresh
You can use these output parameters to retreive the IDs created:
- @ShipmentID - The Shipment.ID of the new record
- @ShipmentLinkID - The _first_ ShipmentLink.ID created. Subsquent links use incremented numbers.
Notes:
- In general, don't set values you want to use the default to.
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 adds basic shipping information to an order:
– Simple Import
DECLARE @OrderNumber INT = 1980;
– [Optional] Create variable to capture resulting Shipment ID
DECLARE @ShipmentID INT = NULL;
– Now Create a (single) Shipping Record on the OrderNumber
EXEC dbo.csp_ImportShipment
@OrderNumber = @OrderNumber,
@Packages = 2,
@ShippedDate = '7/1/2016',
@Carrier = 'FEDEX',
@TrackingNumber = 'FDX 12345-3834'
;
This adds advanced shipping information to an order and denotes a 3rd party shipper (by setting the ShipFromContactID):
– Import using 3rd Party Shipping To a Unique Address
DECLARE @OrderNumber INT = 1980;
DECLARE @ThirdPartySenderContactID INT = (select PrimaryContactID from Account where CompanyName = 'McAlisters #1384-LAWTON');
DECLARE @RecipientContactID INT = (select PrimaryContactID from Account where CompanyName = 'Cyrious Software');
– [Optional] Create variable to capture resulting Shipment ID
DECLARE @ShipmentID INT = NULL;
–SELECT ID from AddressLink where ParentID = @ThirdPartySenderContactID and ParentClassTypeID = 3000 AND AddressTypeID = 11
EXEC dbo.csp_ImportShipment
@OrderNumber = @OrderNumber,
@ShipFromContactID = @ThirdPartySenderContactID,
@ShipFromShippingAccount = 'DHL 38-293822',
@UseNewShipFromAddress = 0,
@ShipToContactID = @RecipientContactID,
@ShipToPhoneAC = '225',
@ShipToPhoneNumber = '7522867',
@ShipToEmail = 'shipping@cyrious.com',
@UseNewShipToAddress = 1,
@SaveShipToAddressInContact = 0,
@ShipToAddressName = 'Ship To',
@ShipToStreetAddress1 = '9432 Common St',
@ShipToStreetAddress2 = 'Suite A',
@ShipToCity = 'Baton Rouge',
@ShipToState = 'LA',
@ShipToPostalCode = '70809',
@Notes = 'Web Order #1234',
@ShippedDueDate = '8/15/2016',
@Packages = 2,
@PackageSize = '12“ x 18” x 12“',
@PackageWeight = 6.2,
@CarrierName = 'DHL',
@CarrierService = 'Express',
@EstimatedCost = 15.35,
@InsuredValue = 324.22,
@DeleteExistingShipments = 1,
@ShipmentID = @ShipmentID OUTPUT
;
code
====== 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.
code_formatsql
– =============================================
– Author: Cyrious Sofware
– Create date: May-2016
– Description: This stored procedure adds a shipment to an order
– Many of the parameters are optional, but if not supplied will used
– The default behavior.
–
– NOTE: This import only supports the “Ship Everything in One Shipnment” Option
–
– Returns: New ShipmentID, ShipmentClassTypeID
– =============================================
ALTER PROCEDURE csp_ImportShipment
– You must supply one (and only one) of the following
@THID INT = NULL, – the ID of the order record
@OrderNumber INT = NULL, – the order number
– Information for Shipping RECIPIENT
@ShipToContactID INT = NULL, – Ship To Contact ID for shipping. Defaults to Billing Contact for Order
@ShipToPhoneAC VARCHAR(5) = NULL, – Phone Area code for shipping contact. Defaults to Shipping Contact's phone.
@ShipToPhoneNumber VARCHAR(15) = NULL, – Phone number for shipping contact. Defaults to Shipping Contact's phone.
@ShipToEmail VARCHAR(128) = NULL, – Email Address for Shipping Contact. Defaults to Shipping Contact's email.
@UseNewShipToAddress BIT = 0, – Set to 0 to use the contact shipping address. Set to 1 to add a custom address.
– The following values only apply when @UseNewShipToAddress = 1
@SaveShipToAddressInContact BIT = 0, – Set to 1 to permanently add a new shiping address to the contact record.
@ShipToAddressName VARCHAR(256) = 'Ship To', – The name the address is saved as if @UseNewShipToAddress = 1
@ShipToStreetAddress1 VARCHAR(256) = NULL,
@ShipToStreetAddress2 VARCHAR(256) = NULL,
@ShipToCity VARCHAR(256) = NULL,
@ShipToState VARCHAR(256) = NULL,
@ShipToPostalCode VARCHAR(256) = NULL,
– Information for Shipping SENDER
@ShipFromContactID INT = NULL, – Contact ID for third party shipping (Ship From).
@ShipFromShippingAccount VARCHAR(256) = NULL, – Shipping Account Number for 3rd Party Shipping
@UseNewShipFromAddress BIT = 0, – Set to 1 to use the contact shipping address. Set to 0 to add a custom address.
– The following values only apply when @UseNewShipFromAddress = 1
@SaveShipFromAddressInContact BIT = 0, – Set to 1 to permanently add a new shiping address to the contact record.
@ShipFromAddressName VARCHAR(256) = 'Ship From', – The name the address
@ShipFromStreetAddress1 VARCHAR(256) = NULL,
@ShipFromStreetAddress2 VARCHAR(256) = NULL,
@ShipFromCity VARCHAR(256) = NULL,
@ShipFromState VARCHAR(256) = NULL,
@ShipFromPostalCode VARCHAR(256) = NULL,
– General Fields
@Notes VARCHAR(2048) = NULL, – Shipping notes
@ShippedDueDate DATE = NULL, – The date the shipment is due. Defaults to the Order Due Date'
@Packages INT = NULL,
@PackageSize VARCHAR(256) = NULL,
@PackageWeight DECIMAL(18,4) = NULL,
@CarrierName VARCHAR(100) = NULL,
@CarrierService VARCHAR(100) = NULL,
@EstimatedCost DECIMAL(18,4) = NULL,
@ActualCost DECIMAL(18,4) = NULL,
@InsuredValue DECIMAL(18,4) = NULL,
@WasShipped BIT = NULL, – Defaults to true is tracking number entered.
@ShippedDate DATE = NULL, – The date the shipment was shipped
@TrackingNumber VARCHAR(256) = NULL,
– Additional Optional values
@DeleteExistingShipments BIT = 0, – set this option to delete all existing shipments on the order first
@RecomputeOnSave BIT = 0, – set to 1 to have the order to recompute and update on Save. 0 to do it manually afterwards.
@RefreshOnSave BIT = 1, – 0 = Don't Refresh the order on Save, 1 = Do Refresh
– Some OUTPUT Parameters in case the caller wants any of these value back
@ShipmentID INT = NULL OUTPUT – ID for Shipment.
AS
BEGIN
– ————————————–
– Validate the Basic Input
– ————————————–
DECLARE @Logs TABLE( ID INT, ClassTypeID INT, ParentID INT, IsError BIT,
Summary Varchar(255),
Detail Varchar(2000)
);
DECLARE @ValidationError VARCHAR(2048) = ;
IF (IsNumeric(@THID) + IsNumeric(@OrderNumber) 1)
SET @ValidationError = @ValidationError + 'You must supply one and only one of these inputs: @OrderNumber, @THID; ';
IF (@THID IS NULL)
BEGIN
SET @THID = (SELECT ID FROM TransHeader WHERE (OrderNumber = @OrderNumber and TransactionType in (1,6)) );
IF (@THID IS NULL)
SET @ValidationError = @ValidationError + 'Order Number '+convert(varchar(12), @OrderNumber)+' does not exist; ';
END;
IF (@OrderNumber IS NULL)
BEGIN
SET @OrderNumber = (SELECT OrderNumber FROM TransHeader WHERE ID = @THID );
IF (@OrderNumber IS NULL)
SET @ValidationError = @ValidationError + 'TransHeader.ID = '+convert(varchar(12), @THID)+' does not exist or is not an order or estimate; ';
END;
IF Exists(SELECT * FROM Shipments WHERE TransHeaderID = @THID) and (@DeleteExistingShipments 1)
SET @ValidationError = @ValidationError + 'Shipment Record already exists for Order #'+convert(varchar(12),@OrderNumber)+'; ' ;
DECLARE @CarrierID INT = NULL;
IF (@CarrierName is NOT NULL)
BEGIN
SET @CarrierID = (SELECT ID FROM ShippingMethod where CarrierName = @CarrierName);
IF (@CarrierID IS NULL)
SET @ValidationError = @ValidationError + 'Carrier "'+@CarrierName+'" not Found; ';
END;
IF (@ValidationError '')
BEGIN
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
VALUES(
NULL, 10100, @THID, 1,
'Line Item Not Imported due to Validation Errors.',
'Validation Errors: '+@ValidationError
)
SELECT * from @Logs;
RETURN;
END;
- - ————————————–
- - Declare Variables and Computed Fields
- - ————————————–
- - Define some fixed values for reference
DECLARE @DT SMALLDATETIME = GetDate();
DECLARE @ComputerName VARCHAR(25) = @@ServerName;
DECLARE @NewLine CHAR(2) = CHAR(10)+CHAR(13);
DECLARE @ProcName VARCHAR(50) = OBJECT_NAME(@@PROCID);
- - Variables
DECLARE @PhoneNumberID INT ;
DECLARE @DivisionID INT ;
DECLARE @DefaultAccountID INT ;
DECLARE @DefaultContactID INT ;
DECLARE @DefaultOrderDueDate SMALLDATETIME ;
DECLARE @LinkContactID INT ;
DECLARE @ShipToOneTimeAddress BIT = (case when (@SaveShipToAddressInContact = 1) then 0 else 1 end);
DECLARE @ShipToAddressLinkID INT ;
DECLARE @ShipToAccountID INT ;
DECLARE @ShipToContactName VARCHAR(100) ;
DECLARE @ShipToCompanyName VARCHAR(100) ;
DECLARE @ShipToAddressID INT ;
DECLARE @ShipToThirdParty BIT ;
DECLARE @ShipFromOneTimeAddress BIT = (case when (@SaveShipFromAddressInContact = 1) then 0 else 1 end);
DECLARE @ShipFromAddressID INT ;
DECLARE @ShipFromAddressLinkID INT ;
DECLARE @ShipFromAccountID INT ;
DECLARE @ShipFromPhoneNumberID INT ;
DECLARE @ShipFromEmailAddress VARCHAR(255) ;
DECLARE @ShipFromThirdParty BIT ;
- - Retrieve IDs via SQL Bridge
SET @ShipmentID = (SELECT dbo.csf_chapi_nextid( 10700, 1));
SET @PhoneNumberID = (SELECT dbo.csf_chapi_nextid( 4100, 1));
- - Compute the ShipmentItems XML
DECLARE @ShipmentItemsXML VARCHAR(2048) = '';
DECLARE @ShipToTaxXML varchar(2048) = '';
SELECT @ShipmentItemsXML = @ShipmentItemsXML +
+ ''
+ '('+convert(varchar(12), ID)+',10100)'
+ ''+convert(varchar(18), Quantity)+''
+ '1'
+ '0'
+ '0'
+ '0'
+ ' '
+ ''
FROM TransDetail
WHERE TransHeaderID = @THID;
SET @ShipmentItemsXML = ''+@ShipmentItemsXML+'';
- - ————————————–
- - Pull Default Order Information
- - ————————————–
SELECT @DefaultOrderDueDate= TH.DueDate,
@DivisionID = TH.DivisionID,
@DefaultAccountID = TH.AccountID,
@DefaultContactID = TH.ContactID
FROM TransHeader TH
WHERE TH.ID = @THID;
- - ————————————–
- - Process SHIP TO information
- - ————————————–
- - Fill in Default Contact Info if not passed in
SET @ShipToContactID = Coalesce(@ShipToContactID, @DefaultContactID);
SELECT @ShipToContactName = AC.FirstName + ' ' + AC.LastName,
@ShipToAccountID = AC.AccountID,
@ShipToEmail = coalesce(@ShipToEmail, AC.EmailAddress),
@ShipToAddressID = (CASE WHEN @UseNewShipFromAddress = 1
THEN NULL
ELSE ShippingAddressID
END),
@ShipToAddressLinkID = (CASE WHEN @UseNewShipFromAddress = 1
THEN NULL
ELSE (SELECT ID from AddressLink where ParentID = AC.ID and ParentClassTypeID = 3000 AND AddressTypeID = 11)
END)
FROM AccountContact AC
WHERE AC.ID = @ShipToContactID;
- - Fill in Company Info
SELECT @ShipToCompanyName = A.CompanyName
FROM Account A
WHERE A.ID = @ShipToAccountID;
- - Fill in Default Phone Number if not passed in
IF (@ShipToPhoneNumber IS NULL)
BEGIN
SELECT @ShipToPhoneAC = PH.AreaCode
, @ShipToPhoneNumber = PH.PhoneNumber
FROM AccountContact AC
JOIN PhoneNumber PH on AC.MainPhoneNumberID = PH.ID
WHERE AC.ID = @ShipToContactID
END;
- - See if 3rd Party Ship To
SET @ShipToThirdParty = (CASE WHEN @ShipToAccountID = @DefaultAccountID THEN 0 ELSE 1 END);
- - ————————————–
- - Process SHIP FROM information
- - ————————————–
IF (@ShipFromContactID IS NOT NULL)
BEGIN
SET @ShipFromThirdParty = 1;
SELECT @ShipFromAccountID = AccountID,
@ShipFromAddressID = ShippingAddressID,
@ShipFromPhoneNumberID = MainPhoneNumberID,
@ShipFromEmailAddress = EmailAddress
FROM AccountContact AC
WHERE AC.ID = @ShipFromContactID;
SET @ShipFromAddressLinkID = (SELECT ID from AddressLink where ParentID = @ShipFromContactID and ParentClassTypeID = 3000 AND AddressTypeID = 11);
END
ELSE
BEGIN
SET @ShipFromThirdParty = 0;
SET @ShipFromContactID = NULL;
SET @ShipFromAccountID = NULL;
SET @ShipFromAddressLinkID = NULL;
SELECT TOP(1)
@ShipFromAddressID = ShippingAddressID,
@ShipFromPhoneNumberID = PhoneNumberID,
@ShipFromEmailAddress = EmailAddress
FROM Store
WHERE ID > 0;
END;
- - ————————————–
- - Process the Records
- - ————————————–
BEGIN TRANSACTION
BEGIN TRY
- - Delete Existing Shipment
IF (@DeleteExistingShipments = 1)
BEGIN
DELETE FROM Shipments
WHERE TransHeaderID = @THID
;
DELETE FROM AddressLink
WHERE ParentID = @THID and ParentClassTypeID = 10000 AND AddressTypeID 10
END;
- - Save 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 --
, -1 -- ,
, 4100 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @ShipmentID --
, -1 --
, 10700 --
, 10 --
, '1' --
, @ShipToPhoneAC --
, @ShipToPhoneNumber --
, '' --
, '('+@ShipToPhoneAC+') '+@ShipToPhoneNumber --
, 0 --
, '' --
);
- - Create Address if needed
IF (@UseNewShipToAddress = 1)
BEGIN
SET @LinkContactID = (CASE WHEN @SaveShipToAddressInContact = 1 THEN @ShipToContactID ELSE NULL END);
- - Save Address
EXEC csp_ImportAddress
@StreetAddress1 = @ShipToStreetAddress1,
@StreetAddress2 = @ShipToStreetAddress2,
@City = @ShipToCity,
@State = @ShipToState,
@PostalCode = @ShipToPostalCode,
@IsOneTimeAddress = @ShipToOneTimeAddress,
@AddressName = 'Ship To',
@LinkContactID = @LinkContactID,
@LinkContactType1ID = 0,
@RefreshOnSave = 0,
@AddressID = @ShipToAddressID OUTPUT,
@AddressLinkID = @ShipToAddressLinkID OUTPUT
;
END;
- - Insert Shipments
INSERT INTO [dbo].[Shipments]
([ID] ,[StoreID] ,[ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive]
, AccountID, ActualCost, ShipToAddressLinkID, CarrierID, CarrierClassTypeID, CarrierService, ContactID
, EmailAddress, EstimatedCost, IsShipped, NumberOfPackages, PackageSize, PackageWeight, PhoneNumberID
, ShipDate, ShipLineItemsXML, ShipmentNumber, TrackingNumber, TransHeaderID, TransHeaderClassTypeID
, InsuredValue, Notes, ShipToDivisionID, ShipToDivisionClassTypeID, ShippedDueDate, ShippedDueDateOV
, AccountName, ContactName, PhoneNumberText, ShipToAddressID, TaxInfo
)
VALUES
( @ShipmentID --
, -1 -- ,
, 10700 -- ,
, @ProcName -- ,
, @ComputerName -- ,
, @DT -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @ShipToAccountID --
, @ActualCost --
, @ShipToAddressLinkID --
, @CarrierID --
, 6130 --
, @CarrierService --
, @ShipToContactID --
, @ShipToEmail --
, @EstimatedCost --
, coalesce(@WasShipped, case when @TrackingNumber IS NULL then 0 else 1 end) --
, @Packages --
, @PackageSize --
, @PackageWeight --
, @PhoneNumberID --
, @ShippedDate --
, @ShipmentItemsXML --
, convert(varchar(12), @OrderNumber)+'-SAA' --
, @TrackingNumber --
, @THID --
, 10000 --
, @InsuredValue --
, @Notes --
, @DivisionID --
, 1010 --
, coalesce(@ShippedDueDate, @DefaultOrderDueDate) --
, (case when @ShippedDueDate IS NULL then 0 else 1 end) --
, @ShipToCompanyName --
, @ShipToContactName --
, @ShipToPhoneAC+'-'+@ShipToPhoneNumber --
, @ShipToAddressID --
, @ShipToTaxXML --
);
- - For 3rd Party Shipping, we need to create another address and link
IF (@ShipFromThirdParty = 1)
BEGIN
IF (@UseNewShipFromAddress = 1)
BEGIN
SET @LinkContactID = (CASE WHEN @SaveShipFromAddressInContact = 1 THEN @ShipFromContactID ELSE NULL END);
- - Create 3rd Party Address
EXEC csp_ImportAddress
@StreetAddress1 = @ShipFromStreetAddress1,
@StreetAddress2 = @ShipFromStreetAddress2,
@City = @ShipFromCity,
@State = @ShipFromState,
@PostalCode = @ShipFromPostalCode,
@LinkContactID = @LinkContactID,
@LinkContactType1ID = 0,
@IsOneTimeAddress = @ShipFromOneTimeAddress,
@AddressName = 'Ship From',
@RefreshOnSave = 0,
@AddressID = @ShipFromAddressID OUTPUT,
@AddressLinkID = @ShipFromAddressLinkID OUTPUT
;
END;
END;
- - Now Update the TransHeader
UPDATE TransHeader
SET SeqID = SeqID + 1,
ModifiedByComputer = @ComputerName,
ModifiedByUser = @ProcName,
ModifiedDate = @DT,
ShippingAddressID = @ShipToAddressID,
ShippingAddressLinkID = @ShipToAddressLinkID,
ShippingAddressOverridden = @UseNewShipToAddress,
ShippingCompanyID = @ShipToAccountID,
ShippingCompanyOV = (case when @DefaultAccountID = @ShipToAccountID then 0 else 1 end),
ShippingContactID = @ShipToContactID,
ShippingContactOV = (case when @DefaultContactID = @ShipToContactID then 0 else 1 end),
ShipmentType = 1, -- 0 for no shipments; 1 for 1 shipment
ShipmentTypeText = 'One Shipment',
ShipFromID = @ShipFromContactID,
ShipFromClassTypeID = 3000,
ShipFromAddressID = @ShipFromAddressID,
ShipFromAddressLinkID = @ShipFromAddressLinkID,
ShipFromAddressOverridden = (CASE WHEN @UseNewShipFromAddress=1 OR @ShipFromThirdParty = 1 THEN 1 ELSE 0 END),
ShipFromPhoneNumberID = @ShipFromPhoneNumberID,
ShipFromEmailAddress = @ShipFromEmailAddress,
UseShippingAccountInfo = @ShipFromThirdParty,
ShippingAccountNumber = @ShipFromShippingAccount,
ShippingCarrierID = @CarrierID,
ShippingCarrierClassTypeID = 6130,
IsShippingLocked = 0,
ShipFromCustomerID = @ShipFromAccountID,
ShipFromCustomerClassTypeID = 2000,
ShipFromDivisionID = @DivisionID,
ShipFromDivisionClassTypeID = 1010,
ShipToAddressType = 0
WHERE ID = @THID;
- - Now commit the Transaction
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
VALUES(
NULL, 10700, @THID, 1,
'Import for Shipment #'+convert(varchar(12),@OrderNumber)+'-SAA FAILED due to Unhandled Exception.',
'Exception: '+ERROR_MESSAGE()
);
SELECT * from @Logs;
RETURN;
END CATCH;
- - ————————————–
- - Refresh and Recompute the Order if Desired
- - ————————————–
IF (@RecomputeOnSave = 1)
EXEC dbo.csf_chapi_recompute @THID, 10000, 'Recompute after Import';
IF (@RefreshOnSave = 1)
BEGIN
EXEC dbo.csf_chapi_refresh @ShipmentID, 10700, -1;
EXEC dbo.csf_chapi_refresh @THID, 10000, -1;
END;
- - ————————————–
- - Return the Log as Results
- - ————————————–
INSERT INTO @Logs (ID, ClassTypeID, ParentID, IsError, Summary, Detail)
VALUES(
@ShipmentID, 10700, @THID, 0,
'Shipment #'+convert(varchar(12),@OrderNumber)+'-SAA Imported.',
''
);
SELECT * from @Logs;
RETURN;
END;
Source
Contributor: Cyrious Software
Date: 8/2016
Version: Control 5.7+
See Also
- Backlinks include_pagepage_componentbacklinks