This articles discusses how to create a macro that automatically closes unpaid orders that are in Sale in a particular station. This is useful for customers when entering the orders into another accounting system for billing, but the general approach may be adapted for lots of uses.

These SQLs irreversibly change your data. It can change lots all at once. Do not use them lightly or without experience. Always create a backup before running.

Creating this macro involves the following solutions:

1. Determine the values to search on and change.
2. Revise and Test the SQL.
3. Create a Macro that executes a SQL Report to run the SQL.
4. Schedule the macro to run automatically on a schedule on the SSLIP.

To manually close an order in Sale to Closed, two steps are necessary:

1. Change the A/R general ledger entries to a Bank Account
2. Change the Order's status and update relevant information

You will need the following information to complete this task. Make sure you have the exact spelling.

• Station Name. You need to the name of the station the order must be in to be closed. In this example, it is 'Accounting-Invoiced (Already Picked Up)'.
• Bank Account Name. You need the name of the GL Account for the bank. In this example, we use 'Bank Account' (which is a system default account).
• Payment Account Name. You need to indicate which payment account was used to pay off the order. You may want to create a new payment account so it can be disabled later if you start recording payments in Control. IN this example, we use 'Payment to AutoClose Order'.

You will need to set the value of the @StationName, @BankAccountName, and @PaymentAccountName to those identified above.

The SQL below should be tested in the SQL Management Console with @DebugMode=1 and @MakeChanges=0 until you are confident it is pulling the right information and working correctly. Once you have the SQL running correctly, you are ready to proceed to the next step.

-- 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 = 0;

-- Set @makechages to 1 to make the changes in the database,
-- otherwise it just shows how many changes would be made
DECLARE @makechanges bit = 1;

-- 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;

Once you have the SQL, you will need to create a Macro that runs this SQL upon execution. To do this, follow the following steps.

• Create a New Order Macro
• On the Description Tab:
  • Name the Macro something like “Automatically Close Open Orders”
• On the Trigger Tab:
  • 
  • Choose “Run this macro manually or on a schedule” * Choose “Use a SQL Query”
  • Enter “Select Top 1 ID, ClassTypeID from TransHeader with(nolock)” (without the quotes) in the SQL field.^]
  • Do not check the box “Also allow this macro to appear in the menu …”.^]
• On the Actions Tab:

• Add a Report Macro (SQL), and set these values. If you miss any of these values, the SQL will not run! * * Give it a name like “SQL To Change Status”
  • * Check the box Print Report As Group.
  • * Enter a menu item name like “Change Status to Closed”
  • * Change the Print Option to Quick Execute
  • * Select the “SQL” option and paste the SQL you refined in Step 2. Make sure you change the @MakeChanges to 1 and @Debug back to 0 on your SQL.
  • * Check the “Execute SQL” option
• * You may add other message notifications you like (before or after running)
• * Save the Macro
• * Test it by running it manually. This will convert any orders you current have in the designated Status to Sale.

If desired you can schedule the macro to run automatically on the server by scheduling a “Run Macro” activity on the calendar. You can then make it a recurring activity that runs on the server every evening or morning.

1. ^ Note: We do not need any data to run this macro, since the query we use pulls the data itself. The purpose of the “Select 1,1” is simply to return a record so the macro execution will continue. -
2. ^ Checking this box would present the user with the false idea that the change would only apply to that one order. Since the SQL will always run against all orders, this would be an incorrect perception and could result in unintended changes.