SQL Stored Procedure - Import Shipping Information
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
This is now a system stored procedure as of 9/23/2023. If you have a store procedure with the same name, please re-name yours to something other than “csp_ImportShipment” as the Control updates will change it.
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 afterward.
- @RefreshOnSave BIT → 0 = Don't Refresh the order on Save, 1 = Do Refresh
You can use these output parameters to retrieve the IDs created:
- @ShipmentID - The Shipment.ID of the new record
- @ShipmentLinkID - The _first_ ShipmentLink.ID created. Subsequent links use incremented numbers.
Notes:
- In general, don't set values you want to use the default to.
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 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 = 'McAlister''s #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 ;
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 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) = '<TaxInfo />'; SELECT @ShipmentItemsXML = @ShipmentItemsXML + + '<LineItem>' + '<LineItemID>('+CONVERT(VARCHAR(12), ID)+',10100)</LineItemID>' + '<Quantity>'+CONVERT(VARCHAR(18), Quantity)+'</Quantity>' + '<IsValidTax>1</IsValidTax>' + '<TaxesCalcPrice>0</TaxesCalcPrice>' + '<TaxesOverriddenPrice>0</TaxesOverriddenPrice>' + '<TaxesIsOverridden>0</TaxesIsOverridden>' + '<TaxItems> <Taxes /> </TaxItems>' + '</LineItem>' FROM TransDetail WHERE TransHeaderID = @THID; SET @ShipmentItemsXML = '<LineItems>'+@ShipmentItemsXML+'</LineItems>'; -- -------------------------------------- -- 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 -- <ID, int,> , -1 -- ,<StoreID, int,> , 4100 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @ShipmentID -- <ParentID, int,> , -1 -- <ParentStoreID, smallint,> , 10700 -- <ParentClassTypeID, smallint,> , 10 -- <PhoneNumberTypeID, int,> , '1' -- <CountryCode, varchar(20),> , @ShipToPhoneAC -- <AreaCode, varchar(20),> , @ShipToPhoneNumber -- <PhoneNumber, varchar(50),> , '' -- <Extension, varchar(20),> , '('+@ShipToPhoneAC+') '+@ShipToPhoneNumber -- <FormattedText, varchar(150),> , 0 -- <PhoneNumberIndex, int,> , '' -- <PhoneNumberTypeText, varchar(100),> ); -- 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 -- <ID, int,> , -1 -- ,<StoreID, int,> , 10700 -- ,<ClassTypeID, int,> , @ProcName -- ,<ModifiedByUser, nvarchar(25),> , @ComputerName -- ,<ModifiedByComputer, nvarchar(25),> , @DT -- ,<ModifiedDate, datetime,> , 0 -- ,<SeqID, int,> , 0 -- ,<IsSystem, bit,> , 1 -- ,<IsActive, bit,> , @ShipToAccountID -- <AccountID, int,> , @ActualCost -- <ActualCost, decimal(18, 4),> , @ShipToAddressLinkID -- <ShipToAddressLinkID, int,> , @CarrierID -- <CarrierID, int,> , 6130 -- <CarrierClassTypeID, int,> , @CarrierService -- <CarrierService, varchar(100),> , @ShipToContactID -- <ContactID, int,> , @ShipToEmail -- <EmailAddress, varchar(50),> , @EstimatedCost -- <EstimatedCost, decimal(18, 4),> , COALESCE(@WasShipped, CASE WHEN @TrackingNumber IS NULL THEN 0 ELSE 1 END) -- <IsShipped, bit,> , @Packages -- <NumberOfPackages, int,> , @PackageSize -- <PackageSize, varchar(50),> , @PackageWeight -- <PackageWeight, decimal(18, 4),> , @PhoneNumberID -- <PhoneNumberID, int,> , @ShippedDate -- <ShipDate, smalldatetime,> , @ShipmentItemsXML -- <ShipLineItemsXML, xml,> , CONVERT(VARCHAR(12), @OrderNumber)+'-SAA' -- <ShipmentNumber, varchar(100),> , @TrackingNumber -- <TrackingNumber, varchar(100),> , @THID -- <TransHeaderID, int,> , 10000 -- <TransHeaderClassTypeID, int,> , @InsuredValue -- <InsuredValue, decimal(18, 4),> , @Notes -- <Notes, varchar(max),> , @DivisionID -- <ShipToDivisionID, int,> , 1010 -- <ShipToDivisionClassTypeID, int,> , COALESCE(@ShippedDueDate, @DefaultOrderDueDate) -- <ShippedDueDate, smalldatetime,> , (CASE WHEN @ShippedDueDate IS NULL THEN 0 ELSE 1 END) -- <ShippedDueDateOV, bit,> , @ShipToCompanyName -- <AccountName, varchar(100),> , @ShipToContactName -- <ContactName, varchar(100),> , @ShipToPhoneAC+'-'+@ShipToPhoneNumber -- <PhoneNumberText, varchar(100),> , @ShipToAddressID -- <ShipToAddressID, int,> , @ShipToTaxXML -- <TaxInfo, text,> ); -- 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+