SQL Stored Procedure - Import Shipping Information Detail
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 sales or consulting at Cyrious.
Note: This feature requires Control 5.75 or higher.
Overview
The stored procedure can be used to add *Multiple* Shipments to an order in Control. If you are shipping everything together, use the other import method.
This process relies on a two step process using two stored procedures.
- [Control.Import.Shipment.Header]
- [Control.Import.Shipment.Detail]
To use this import:
- EXECute the [Control.Import.Shipment.Header] stored procedure (once) when importing the order. This will import the shipment information, but not create any shipment items (details).
- EXECute the [Control.Import.Shipment.Detail] stored procedure for each line item that you are shipping. This will create a shipping line for the line item.
Notes:
- 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 for [Control.Import.Shipment.Header]:
- @THID INT → The TransHeader.ID of the order
- @OrderNumber INT → The Order Number.
The two following value must be supplied for [Control.Import.Shipment.Detail]:
- @TDID INT → The TransDetail.ID of the order
The following *optional* values may be set in the Header procedure (ShipFrom Information) or the Detail procedure (ShipTo Information)
- – 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 sql_bridge 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 shipping information to an order and creates a shipment for the first line item.
-- Simple Import DECLARE @OrderNumber INT = 119977; DECLARE @THID INT = (SELECT ID FROM TransHeader WHERE OrderNumber = @OrderNumber AND TransactionType IN (1,6)); -- Now Create a (single) Shipping Record on the OrderNumber EXEC dbo.[Control.Import.Shipment.Header] @OrderNumber = @OrderNumber , @UseNewShipToAddress = 0 , @RecomputeOnSave = 0 -- Don't recompute the order until Done , @RefreshOnSave = 0 -- Don't refresh Control until done ; -- [Optional] Create variable to capture resulting Shipment ID DECLARE @ShipmentID INT = NULL; -- Now create a detail for one of the line items. Look it up for test. -- In a real situation, you need to do this for each line item that is shipped! DECLARE @TDID INT = (SELECT TOP 1 ID FROM TransDetail WHERE TransHeaderID = @THID ORDER BY id); EXEC dbo.[Control.Import.Shipment.Detail] @TDID = @TDID , @UseNewShipToAddress = 1 , @SaveShipToAddressInContact = 0 , @ShipToAddressName = 'East Office' , @ShipToStreetAddress1 = '1234 Main Street' , @ShipToStreetAddress2 = '' , @ShipToCity = 'Buffalo' , @ShipToState = 'NY' , @ShipToPostalCode = '12345' , @Notes = 'Imported from Web Order #4222' , @ShippedDueDate = '5/1/2017 12:00 PM' , @RecomputeOnSave = 0 , @RefreshOnSave = 0 , @Packages = 2 , @ShippedDate = '7/1/2016 11:32 AM' , @CarrierName = 'FEDEX' , @TrackingNumber = 'FDX 9874-9343' , @ShipmentID = @ShipmentID OUTPUT ; -- You don't want to recompute every time! So just recompute and refresh at the end. EXEC dbo.csf_chapi_refresh @THID, 10000, -1 EXEC dbo.csf_chapi_recompute @THID, 10000, 'Order Shipment Information Imported.'
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.
[Control.Import.Shipment.Header]
IF EXISTS(SELECT * FROM sys.objects WHERE TYPE = 'P' AND name = 'Control.Import.Shipment.Header') DROP PROCEDURE [Control.Import.Shipment.Header] GO -- ============================================= -- Author: Cyrious Sofware -- Create date: May-2017 -- Description: Use this procedure to set the shipping options for an order to -- use MULTIPLE SHIPMENT LINES. -- -- Then call [Control.Import.Shipment.Header] with the detail for each line item -- ============================================= CREATE PROCEDURE [Control.Import.Shipment.Header] -- 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(25) = RIGHT(OBJECT_NAME(@@PROCID),25); -- 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)); -- -------------------------------------- -- 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; -- 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 = 2, -- 0 for no shipments; 1 for 1 shipment; 2 for multiple shipments ShipmentTypeText = 'Multiple Shipments', 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)+'-Shipment Header 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 @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)+'-Shipment Header Imported.', '' ); SELECT * FROM @Logs; RETURN; END;
[Control.Import.Shipment.Detail]
IF EXISTS(SELECT * FROM sys.objects WHERE TYPE = 'P' AND name = 'Control.Import.Shipment.Detail') DROP PROCEDURE [Control.Import.Shipment.Detail] GO -- ============================================= -- Author: Cyrious Sofware -- Create date: May-2017 -- Description: Use this procedure to add shipping lines to an order already set for -- use MULTIPLE SHIPMENT LINES. -- -- This procedure adds a new shipping line for a single TRANSDETAIL -- ============================================= CREATE PROCEDURE [Control.Import.Shipment.Detail] -- You must supply one (and only one) of the following @TDID INT = NULL, -- the ID of the order record -- 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, -- 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 @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 DECLARE @THID INT , @OrderNumber INT ; -- Retrieve the order information SELECT @THID = TD.TransHeaderID , @OrderNumber = TD.TransHeaderTransNumber FROM TransDetail TD WHERE ID = @TDID ; -- -------------------------------------- -- 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 (@THID IS NULL) SET @ValidationError = @ValidationError + '@TDID = '+CONVERT(VARCHAR(12), @TDID)+' does not link to an order; '; 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(25) = RIGHT(OBJECT_NAME(@@PROCID), 25); -- 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 @ShipmentSuffix CHAR(2) = 'AA' , @ShipmentIndex SMALLINT = 1 ; -- 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 ID = @TDID; -- Only need to pull the one for now ... 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 Shipment Suffix; AA = 1, AB = 2, AZ = 26, BA = 27 ... -- -------------------------------------- SET @ShipmentIndex = COALESCE((SELECT COUNT(*) FROM Shipments WHERE TransHeaderID = @THID),0)+1; SET @ShipmentSuffix = CHAR( ASCII('A')+ ((@ShipmentIndex-1)/26) ) -- First letter increases every 26 + CHAR( ASCII('A')+ ((@ShipmentIndex-1) % 26) ); -- secton letter increase modulo 26 -- -------------------------------------- -- 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 @UseNewShipToAddress = 1 THEN NULL ELSE ShippingAddressID END), @ShipToAddressLinkID = (CASE WHEN @UseNewShipToAddress = 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 the Records -- -------------------------------------- BEGIN TRANSACTION BEGIN TRY -- 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)+'-S'+@ShipmentSuffix -- <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, > ); -- 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)+'-'+@ShipmentSuffix+' 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)+'-'+@ShipmentSuffix+' Imported.', '' ); SELECT * FROM @Logs; RETURN; END;
Source
Contributor: Cyrious Software
Date: 8/2016
Version: Control 6.0+
You could leave a comment if you were logged in.