**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 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 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) = '';
 
    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;
 

Contributor: Cyrious Software

Date: 8/2016

Version: Control 5.7+

You could leave a comment if you were logged in.