Description

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.

Warning

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.

How it Works

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.

1. Determine Key Variables

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'.

2. Revise and Test the SQL

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.

code format="SQL"

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

<code>

3. Create a Macro that executes a SQL Report to run the SQL

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.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.
  • Do not check the box “Also allow this macro to appear in the menu …”.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.
  • 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.

4. Schedule the macro to run automatically on a schedule on the SSLIP

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.

You could leave a comment if you were logged in.