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.
===== Setup =====
To set up this procedure,
- Create an Order UDF called Original Sale Date
- Create a SQL Report on the Order Menu
- Call it "Backdate Imported Order"
- Paste this SQL into the SQL box
- Set the option to Execute SQL
- On the Options tab, change the Parameter Type of "Order Number" to Order
- Save it.
- Restart Control so that the UDF is created and the report menu is updated
===== SQL =====
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,
- Create an Order UDF called Original Sale Date
- Create a SQL Report on the Order Menu
- Call it "Backdate Imported Order"
- Paste this SQL into the SQL box
- Set the option to Execute SQL
- On the Options tab, change the Parameter Type of "Order Number" to Order
- Save it.
- Restart Control so that the UDF is created and the report menu is updated
For each order,
- Set the UDF value to the original sale date.
- Mark the order sale (if it was shipped/installed/delivered)
- Apply any payments.
- Run this SQL Report on that order to shift the GL and other dates
When you are all finished:
- Delete the Report from the Menu!
- 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
- 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
- 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;
===== Steps to Use =====
For each order,
- Set the UDF value to the original sale date.
- Mark the order sale (if it was shipped/installed/delivered). This routine does not change the status of the order.
- Apply any payments.
- Run this SQL Report on that order to shift the GL and other dates
===== Steps when Complete =====
When you are all finished:
- Delete the Report from the Menu!
- 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.
===== Setup =====
To set up this procedure,
- Backup Your Database
- Create an Order UDF called Original Sale Date
- Restart Control so that the UDF is created
For each order,
- Set the UDF value to the original sale date.
- Mark the order sale (if it was shipped/installed/delivered)
- Apply any payments.
When you are all finished entering them:
- Run this SQL Report on that order to shift the GL and other dates
- Wait one minute for the data to refresh.
===== SQL =====
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,
- Create an Order UDF called Original Sale Date
- Restart Control so that the UDF is created
For each order,
- Set the UDF value to the original sale date.
- Mark the order sale (if it was shipped/installed/delivered)
- Apply any payments.
When you are all finished entering them:
- 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
- - 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
- - 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+