This procedure is used to backdate an order. It should only be used BEFORE the system is live in production in order to shift an order (usually one that was in A/Rs) so that the original create date is accurate.

Cautions

**This query should ONLY be used under the direction of a Cyrious consultant. There are NO checks or validation within this SQL. If it is run on a production system, it may result in __irreversible changes within the Accounting and General Ledger__.**

**If you are using INVENTORY within Control the entry of orders and backdating will affect your inventory counts, be aware of this before proceeding with the entry of any historical orders into Control.**

Concept

This approach uses a SQL command that shifts the entry dates for the following tables:

  • TransHeader
  • Ledger (GL)
  • Journal

This may be done one order at at time (much safer) or all at once.

Backdating One Order At A Time

To use this process, you will need to setup a UDF and a SQL Report. The SQL report is then run on each order that needs to be shifted. When done, the procedure should be removed to prevent accidental running of it.

To set up this procedure,

  1. Create an Order UDF called Original Sale Date
  2. Create a SQL Report on the Order Menu
    1. Call it “Backdate Imported Order”
    2. Paste this SQL into the SQL box
    3. Set the option to Execute SQL
    4. On the Options tab, change the Parameter Type of “Order Number” to Order
    5. Save it.
  3. Restart Control so that the UDF is created and the report menu is updated

The text for the SQL on the report follows.

code_formatsql

This procedure is used to backdate an order. It should only be used BEFORE the system is live in production in order to shift an order (usually one that was in A/Rs) so that he original create date is accurate.

To set up this procedure,

  1. Create an Order UDF called Original Sale Date
  2. Create a SQL Report on the Order Menu
    1. Call it “Backdate Imported Order”
    2. Paste this SQL into the SQL box
    3. Set the option to Execute SQL
    4. On the Options tab, change the Parameter Type of “Order Number” to Order
    5. Save it.
  3. Restart Control so that the UDF is created and the report menu is updated

For each order,

  1. Set the UDF value to the original sale date.
  2. Mark the order sale (if it was shipped/installed/delivered)
  3. Apply any payments.
  4. Run this SQL Report on that order to shift the GL and other dates

When you are all finished:

  1. Delete the Report from the Menu!
  2. Set the UDF inactive

PROCEDURE VARIABLES

These are variables used by the system but not set directly by the user

declare @THID int;

declare @SaleDate smalldatetime;

set variables, and check that they are valid.

return NULL if invalid values

select @THID = TH.ID,

      @SaleDate = U.Original_Sale_Date

from TransHeader TH

join TransHeaderUserField U on U.ID = TH.ID

where TH.ID =

  and TH.TransactionType = 1
  and U.Original_Sale_Date is not NULL

;

IF (@THID IS NULL)

  RETURN;

BEGIN TRANSACTION

BEGIN TRY

  1. shift the journal entries
  update Journal
  set SeqID = SeqID + 1,
      CompletedDateTime = @SaleDate,
      StartDateTime = @SaleDate,
      EndDateTime = @SaleDate,
      QueryStartDateTime = @SaleDate,
      QueryEndDateTime = @SaleDate,
      Notes = 'Dates Shifted via SQL from '+convert(varchar(18), CompletedDateTime)+'; '+coalesce(Notes, '')
  where TransactionID = @THID
  ;
  -- shift the GL
  update Ledger
  set SeqID = SeqID + 1,
      EntryDateTime = @SaleDate,
      Notes = 'Dates Shifted via SQL from '+convert(varchar(18), EntryDateTime)+'; '+coalesce(Notes, '')
  where TransactionID = @THID
  ;
  -- shift the Order
  update TransHeader
  set SeqID = SeqID + 1,
      OrderCreatedDate = @SaleDate,
      BuiltDate = (case when StatusID in (2,3,4) then @SaleDate else NULL end),
      SaleDate = (case when StatusID in (3,4) then @SaleDate else NULL end),
      ClosedDate = (case when StatusID = 4 then @SaleDate else NULL end),
      ProductionNotes = 'Created Dates Shifted via SQL from '+convert(varchar(18), OrderCreatedDate)+'; '+coalesce(ProductionNotes, '')
  where ID = @THID
  ;
  -- Now commit the Transaction
  COMMIT TRANSACTION
  ;
  -- Now insert records into the refresh table so the updated information is refreshed in Control
  INSERT INTO RefreshMonitor
      SELECT @THID as ID, 10000 as ClassTypeID, -1 as SeqID, 0 as IsDeleted
  ;
  INSERT INTO RefreshMonitor
      SELECT ID, ClassTypeID, SeqID, 0 as IsDeleted
      FROM Journal
      Where TransactionID = @THID
  ;
  INSERT INTO RefreshMonitor
      SELECT ID, ClassTypeID, SeqID, 0 as IsDeleted
      FROM Ledger
      Where TransactionID = @THID
  ;

END TRY

BEGIN CATCH

  1. Now handle any errors
  ROLLBACK TRANSACTION;
  DECLARE @ErrorMessage    VARCHAR(2048);
  DECLARE @ErrorSeverity   INT;
  DECLARE @ErrorState      INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();
  -- Use RAISERROR inside the CATCH block to return
  -- error information about the original error that
  -- caused execution to jump to the CATCH block.
  RAISERROR (@ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );

END CATCH;

For each order,

  1. Set the UDF value to the original sale date.
  2. Mark the order sale (if it was shipped/installed/delivered). This routine does not change the status of the order.
  3. Apply any payments.
  4. Run this SQL Report on that order to shift the GL and other dates

When you are all finished:

  1. Delete the Report from the Menu!
  2. Set the UDF inactive.

Backdating All Order At Once

To use this process, you will need to setup a UDF and then run the SQL manually.

To set up this procedure,

  1. Backup Your Database
  2. Create an Order UDF called Original Sale Date
  3. Restart Control so that the UDF is created

For each order,

  1. Set the UDF value to the original sale date.
  2. Mark the order sale (if it was shipped/installed/delivered)
  3. Apply any payments.

When you are all finished entering them:

  1. Run this SQL Report on that order to shift the GL and other dates
  2. Wait one minute for the data to refresh.

The text for the SQL follows. code_formatsql

This procedure is used to backdate an order. It should only be used BEFORE the system is live in production in order to shift an order (usually one that was in A/Rs) so that the original create date is accurate.

To set up this procedure,

  1. Create an Order UDF called Original Sale Date
  2. Restart Control so that the UDF is created

For each order,

  1. Set the UDF value to the original sale date.
  2. Mark the order sale (if it was shipped/installed/delivered)
  3. Apply any payments.

When you are all finished entering them:

  1. Run this SQL Report on that order to shift the GL and other dates

set @Debug to 1 to display intermediate steps

declare @debug bit = 0;

set to @RollBackChanges to 1 to rollback the changes and not commit it (helpful while testing)

declare @RollBackChanges bit = 0;

PROCEDURE VARIABLES

Create a table to store all the IDs change

DECLARE @IDs Table (THID INT Primary Key, SaleDate smalldatetime, OriginalCreatedDate smalldatetime, OriginalSaleDate smalldatetime); set variables, and check that they are valid. return NULL if invalid values insert into @IDs

  SELECT U.ID, U.Original_Sale_Date, TH.OrderCreatedDate, TH.SaleDate
  FROM TransHeader TH
  JOIN TransHeaderUserField U ON U.ID = TH.ID
  WHERE   TH.TransactionType = 1
      AND U.Original_Sale_Date IS NOT NULL

;

if (@debug = 1) Select * from @IDs

;

IF ((select count(*) from @IDs) = 0)

  RETURN;

BEGIN TRANSACTION

BEGIN TRY

  1. - shift the journal entries
  UPDATE J
  SET SeqID = SeqID + 1,
      CompletedDateTime = I.SaleDate,
      StartDateTime = I.SaleDate,
      EndDateTime = I.SaleDate,
      QueryStartDateTime = I.SaleDate,
      QueryEndDateTime = I.SaleDate,
      Notes = 'Dates Shifted via SQL from '+CONVERT(VARCHAR(18), CompletedDateTime)+'; '+COALESCE(Notes, '')
  FROM Journal J
  JOIN @IDs I on J.TransactionID = I.THID
  ;
  -- shift the GL
  UPDATE L
  SET SeqID = SeqID + 1,
      EntryDateTime = I.SaleDate,
      Notes = 'Dates Shifted via SQL from '+CONVERT(VARCHAR(18), EntryDateTime)+'; '+COALESCE(Notes, '')
  FROM Ledger L
  JOIN @IDs I on L.TransactionID = I.THID
  ;
  -- shift the Order
  UPDATE TH
  SET SeqID = SeqID + 1,
      OrderCreatedDate = I.SaleDate,
      BuiltDate = (CASE WHEN StatusID IN (2,3,4) THEN I.SaleDate ELSE NULL END),
      SaleDate = (CASE WHEN StatusID IN (3,4) THEN I.SaleDate ELSE NULL END),
      ClosedDate = (CASE WHEN StatusID = 4 THEN I.SaleDate ELSE NULL END),
      ProductionNotes = 'Created Dates Shifted via SQL from '+CONVERT(VARCHAR(18), OrderCreatedDate)+'; '+COALESCE(ProductionNotes, '')
  FROM TransHeader TH
  JOIN @IDs I on TH.ID = I.THID
  ;
  -- Clear the Import Field (so it doesn't get used in the next run)
  UPDATE THU
  SET SeqID = SeqID + 1,
      Original_Sale_Date = NULL
  FROM TransHeaderUserField THU
  JOIN @IDs I on THU.ID = I.THID
  ;
  -- Now commit the Transaction
  IF (@RollBackChanges=1)
      ROLLBACK TRANSACTION
  ELSE
      COMMIT TRANSACTION
  ;
  -- Now insert records into the refresh table so the updated information is refreshed in Control
  INSERT INTO RefreshMonitor
      SELECT THID AS ID, 10000 AS ClassTypeID, -1 AS SeqID, 0 AS IsDeleted
      FROM @IDs
  ;
  INSERT INTO RefreshMonitor
      SELECT THID AS ID, 10002 AS ClassTypeID, -1 AS SeqID, 0 AS IsDeleted
      FROM @IDs
  ;
  INSERT INTO RefreshMonitor
      SELECT J.ID, J.ClassTypeID, -1 as SeqID, 0 AS IsDeleted
      FROM Journal J
      JOIN @IDs I on J.TransactionID = I.THID
  ;
  INSERT INTO RefreshMonitor
      SELECT L.ID, L.ClassTypeID, -1, 0 AS IsDeleted
      FROM Ledger L
      JOIN @IDs I on L.TransactionID = I.THID
  ;

END TRY

BEGIN CATCH

  1. - Now handle any errors
  ROLLBACK TRANSACTION;
  DECLARE @ErrorMessage    VARCHAR(2048);
  DECLARE @ErrorSeverity   INT;
  DECLARE @ErrorState      INT;
  SELECT @ErrorMessage = ERROR_MESSAGE(),
          @ErrorSeverity = ERROR_SEVERITY(),
          @ErrorState = ERROR_STATE();
  -- Use RAISERROR inside the CATCH block to return
  -- error information about the original error that
  -- caused execution to jump to the CATCH block.
  RAISERROR (@ErrorMessage, -- Message text.
              @ErrorSeverity, -- Severity.
              @ErrorState -- State.
              );

END CATCH;

SELECT TH.OrderNumber, I.SaleDate as [New Date], I.OriginalCreatedDate, I.OriginalSaleDate

FROM @IDs I

JOIN TransHeader TH on I.THID = TH.ID

Source

Contributor: Cyrious Software Date: 6/2016 Version: Control 5.1+

You could leave a comment if you were logged in.