This is an old revision of the document!


=Explanation of SQL=

Routinely users that only utilize Control for estimating will leave a large number of invoices in the status of Sale. This is due to handling the invoicing and payments in another accounting system instead of within Control. Later if a user intends on closing a batch of those orders in sale or potentially closing a group because they are about to start invoicing and payments in Control this query will help close those orders without having to handle them one by one. In the example below the customer had roughly 3,000 orders in Sale without any payments posted to the order. They wanted to retain approximately 200 orders that were still truly in AR's. Their intention is to begin using Control for invoicing and move away from their current accounting system for that task.

=Risk of Data Corruption if Run Improperly=

High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

=Approach 1=

This approach will close out all orders that are in a particular station. This is useful you want to keep the order in Sale to track, for instance, that it needs to be entered into a separate accounting system.

  1. It is recommended that you create a backup before proceeding. It is also recommended that you run this when there are not any other users in the system in the event that you need to revert to your backup.
  2. Adjust the SQL statement below for the Station Name you want to use and the Bank Account Name you want to use.
  3. Set @MakeChanges = 0 and @Debug = 1 to test
  4. Run the query and look at the results.
  5. If you don't have the results you expect, review the previous steps.
  6. When you have everything right, Set @MakeChanges = 1 and @Debug = 0
  7. Run the query
-- This SQL will automatically close out an order in Sale when it reaches a particular station. 
-- This is used by Control users who are not posting payments in Cyrious (usually because they
-- are using another system for billing) but want to use Control to track the orders and then
-- close them out at a particular station.
 
-- The steps to doing this (once the order is identified) are:
--   1. Change the A/R general ledger entries to a Bank Account
--   2. Change the Order's status and update relevant information 
 
-- Set @debug to 1 to show debugging information
DECLARE @debug bit = 1;
 
-- Set @makechages to 1 to make the changes in the database, 
-- otherwise it just shows how many changes would be made
DECLARE @makechanges bit = 0;
 
-- 1. define the station, bank account, and payment account
DECLARE @StationName VARCHAR(128) = 'Accounting-Invoiced (Already Picked Up)';
DECLARE @BankAccountName VARCHAR(128) = 'Bank Account';
DECLARE @PaymentAccountName VARCHAR(128) = 'Payment to AutoClose Order';
 
-- 2. look up the station ID and Bank GLAccountID
DECLARE @StationID INT = (SELECT ID FROM Station WHERE StationName = @StationName);
DECLARE @BankAccountID INT = (SELECT ID FROM GLAccount WHERE AccountName = @BankAccountName);
DECLARE @BankClassificationType INT = (SELECT GlClassificationType FROM GLAccount WHERE ID = @BankAccountID);
DECLARE @BankClassTypeName VARCHAR(64) = (SELECT GLClassTypeName FROM GLAccount WHERE ID = @BankAccountID);
DECLARE @PaymentAccountID INT = (SELECT ID FROM PaymentAccount WHERE AccountName = @PaymentAccountName);
 
IF (@debug=1) 
BEGIN
	print 'StationID = '+COALESCE(CONVERT(VARCHAR(12), @StationID), 'NOT FOUND');
	print 'BankAccountID = '+COALESCE(CONVERT(VARCHAR(12), @BankAccountID), 'NOT FOUND');
	print 'BankClassificationType = '+COALESCE(CONVERT(VARCHAR(12), @BankClassificationType), 'NOT FOUND');
	print 'BankClassTypeName = '+COALESCE(CONVERT(VARCHAR(12), @BankClassTypeName), 'NOT FOUND');
	print 'PaymentAccountID = '+COALESCE(CONVERT(VARCHAR(12), @PaymentAccountID), 'NOT FOUND');
END;
-- Stop Immediately if not found
IF (@StationID IS NULL) OR (@BankAccountID IS NULL) OR (@PaymentAccountID IS NULL) 
	THROW 51000, 'Some of the Required Data was not Found in the Database', 1;
-- 3. pull a list of all Sale orders in that station and GL Entries to Change
DECLARE @Orders TABLE (ID INT PRIMARY KEY, ClassTypeID INT NOT NULL);
 
INSERT INTO @Orders 
    SELECT ID, ClassTypeID
    FROM TransHeader 
    WHERE (TransactionType IN (1,6)) AND (StatusID = 3) AND (StationID = @StationID);
DECLARE @AREntries TABLE (ID INT PRIMARY KEY, ClassTypeID INT NOT NULL);
 
INSERT INTO @AREntries 
    SELECT ID, ClassTypeID
    FROM Ledger 
	WHERE TransactionID IN (SELECT ID FROM @Orders)
	AND GLAccountID = 14  -- Accounts Receivable Account
 
-- display the list of orders to change if in debug mode
IF (@debug=1) 
    SELECT T.ID, T.OrderNumber, T.Description, T.TotalPrice, T.BalanceDue 
    FROM TransHeader T
    JOIN @Orders O ON T.ID = O.ID
 
-- 4. Now Change out the GL Entries for these orders
IF (@makechanges = 1) 
    UPDATE Ledger
    SET SeqID = SeqID + 1
    , GLAccountID = COALESCE(@BankAccountID, GLAccountID)
    , GLClassificationType = COALESCE(@BankClassificationType, GLAccountID)
    , GLClassTypeName = COALESCE(@BankClassTypeName, GLClassTypeName)
    , Classification = COALESCE(@PaymentAccountID, 16)  -- Payment Method
    , EntryType = 2
    WHERE ID IN (SELECT ID FROM @AREntries)
ELSE
    SELECT CONVERT(VARCHAR(12), COUNT(*)) + ' GL Changes Would Have Been Made'
    FROM Ledger 
	WHERE ID IN (SELECT ID FROM @AREntries)
;
 
-- 5. Now Change the Order Record
IF (@makechanges = 1) 
    UPDATE TransHeader
    SET SeqID = SeqID + 1
    , PaymentTotal = TotalPrice
    , BalanceDue = 0
    , ClosedDate = GetDate()
    , StatusID = 4
    , StatusText = 'Closed'
    WHERE ID IN (SELECT ID FROM @Orders)
ELSE
    SELECT CONVERT(VARCHAR(12), COUNT(*)) + ' Orders Would Have Been Made Closed'
    FROM TransHeader
    WHERE ID IN (SELECT ID FROM @Orders)
;
 
-- 6. Insert into the RefreshMonitor table so these records will refresh to Closed in Control 
IF (@makechanges = 1)
BEGIN
    INSERT INTO RefreshMonitor
        SELECT ID, ClassTypeID, -1, CONVERT(bit, 0)
        FROM @Orders;
    INSERT INTO RefreshMonitor
        SELECT ID, ClassTypeID, -1, CONVERT(bit, 0)
        FROM @AREntries;
END;

=Approach 2=

This approach closes all orders in Sale regardless of station. The accounts are all preset in SQL.

  1. It is recommended that you create a backup before proceeding. It is also recommended that you run this when there are not any other users in the system in the event that you need to revert to your backup.
  2. Create a checkbox UDF in Control named CloseOrder.
  3. Run Query 1 below to enable the CloseOrder UDF on all orders in sale. Using the Order Explorer in Control go through every order that should remain open and uncheck the CloseOrder UDF in Control. You can use the advanced explorer group option to speed up this process.
  4. Once you have unchecked all orders that should remain, run Query 2 to close all orders in sale that have the CloseOrder UDF enabled.
-- Query1: This query checks the UDF for all orders in sale.
UPDATE TransHeaderUserField
SET CloseOrder = 1
WHERE ID IN (SELECT ID FROM TransHeader WHERE TransActionType IN (1,6) AND StatusID = 3)
;
-- Query2: After the user has unchecked the UDF's for the orders in sale
-- that should remain, run this query to change the AR account to
-- the Undeposited Funds account.
UPDATE GL
SET SeqID = SeqID + 1 
, Classification = 16
, GLAccountID = 91
, GLClassificationType = 1007
, GLClassTypeName = 'Undeposited Funds Account'
, EntryType = 2
WHERE GLAccountID = 14
AND TransActionID IN (SELECT ID FROM TransHeaderUserField WHERE CloseOrder = 1)
;
-- Query3: to close all orders in sale that should be closed.
UPDATE TransHeader
SET SeqID = SeqID + 1
, PaymentTotal = TotalPrice
, BalanceDue = 0
, ClosedDate = SaleDate
, StatusID = 4
, StatusText = 'Closed'
WHERE ID IN (SELECT ID FROM TransHeaderUserField WHERE CloseOrder = 1)

=Version Information=

  • Control: 5.7+

=Related SQLs=

You could leave a comment if you were logged in.