Differences

This shows you the differences between two versions of the page.

Link to this comparison view

batch_close_orders_in_sale_with_balance_due [2019/01/30 09:22]
127.0.0.1 external edit 		batch_close_orders_in_sale_with_balance_due [2019/01/30 16:13] (current)
kcifreo
Line 1: Line 1:
-=Explanation of SQL= +===== 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. 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 =====
- +
-=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**. **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.
  
 +===== Basic Procedure =====
  
-=Approach 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. 
- +  - Adjust the SQL statement below for the **Station Name**  you want to use and the **Bank Account Name**  you want to use.
- +
- +
-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. +
- +
- +
- +
-===== Basic Procedure ===== +
-  - 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.  +
-  - Adjust the SQL statement below for the **Station Name** you want to use and the **Bank Account Name** you want to use.+
   - Set @MakeChanges = 0 and @Debug = 1 to test   - Set @MakeChanges = 0 and @Debug = 1 to test
   - Run the query and look at the results.   - Run the query and look at the results.
   - If you don't have the results you expect, review the previous steps.   - If you don't have the results you expect, review the previous steps.
-  - When you have everything right, Set @MakeChanges = 1 and @Debug = 0 +  - When you have everything right, Set @MakeChanges = 1 and @Debug = 0
   - Run the query   - Run the query
- 
- 
  
 ===== Query 1 ===== ===== Query 1 =====
- 
- 
  
 <code sql> <code sql>
--- This SQL will automatically close out an order in Sale when it reaches a particular station. +-- 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 -- 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 -- are using another system for billing) but want to use Control to track the orders and then
 -- close them out at a particular station. -- close them out at a particular station.
- +
 -- The steps to doing this (once the order is identified) are: -- The steps to doing this (once the order is identified) are:
 --   1. Change the A/R general ledger entries to a Bank Account --   1. Change the A/R general ledger entries to a Bank Account
---   2. Change the Order's status and update relevant information  +--   2. Change the Order's status and update relevant information 
- +
 -- Set @debug to 1 to show debugging information -- Set @debug to 1 to show debugging information
 DECLARE @debug bit = 1; DECLARE @debug bit = 1;
-  + 
--- Set @makechages to 1 to make the changes in the database, +-- Set @makechages to 1 to make the changes in the database,
 -- otherwise it just shows how many changes would be made -- otherwise it just shows how many changes would be made
 DECLARE @makechanges bit = 0; DECLARE @makechanges bit = 0;
- +
 -- 1. define the station, bank account, and payment account -- 1. define the station, bank account, and payment account
 DECLARE @StationName VARCHAR(128) = 'Accounting-Invoiced (Already Picked Up)'; DECLARE @StationName VARCHAR(128) = 'Accounting-Invoiced (Already Picked Up)';
 DECLARE @BankAccountName VARCHAR(128) = 'Bank Account'; DECLARE @BankAccountName VARCHAR(128) = 'Bank Account';
 DECLARE @PaymentAccountName VARCHAR(128) = 'Payment to AutoClose Order'; DECLARE @PaymentAccountName VARCHAR(128) = 'Payment to AutoClose Order';
- +
 -- 2. look up the station ID and Bank GLAccountID -- 2. look up the station ID and Bank GLAccountID
 DECLARE @StationID INT = (SELECT ID FROM Station WHERE StationName = @StationName); DECLARE @StationID INT = (SELECT ID FROM Station WHERE StationName = @StationName);
Line 64: Line 50:
 DECLARE @BankClassTypeName VARCHAR(64) = (SELECT GLClassTypeName 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); DECLARE @PaymentAccountID INT = (SELECT ID FROM PaymentAccount WHERE AccountName = @PaymentAccountName);
-  + 
-IF (@debug=1) +IF (@debug=1)
 BEGIN BEGIN
- print 'StationID = '+COALESCE(CONVERT(VARCHAR(12), @StationID), 'NOT FOUND'); +    print 'StationID = '+COALESCE(CONVERT(VARCHAR(12), @StationID), 'NOT FOUND'); 
- print 'BankAccountID = '+COALESCE(CONVERT(VARCHAR(12), @BankAccountID), 'NOT FOUND'); +    print 'BankAccountID = '+COALESCE(CONVERT(VARCHAR(12), @BankAccountID), 'NOT FOUND'); 
- print 'BankClassificationType = '+COALESCE(CONVERT(VARCHAR(12), @BankClassificationType), 'NOT FOUND'); +    print 'BankClassificationType = '+COALESCE(CONVERT(VARCHAR(12), @BankClassificationType), 'NOT FOUND'); 
- print 'BankClassTypeName = '+COALESCE(CONVERT(VARCHAR(12), @BankClassTypeName), 'NOT FOUND'); +    print 'BankClassTypeName = '+COALESCE(CONVERT(VARCHAR(12), @BankClassTypeName), 'NOT FOUND'); 
- print 'PaymentAccountID = '+COALESCE(CONVERT(VARCHAR(12), @PaymentAccountID), 'NOT FOUND');+    print 'PaymentAccountID = '+COALESCE(CONVERT(VARCHAR(12), @PaymentAccountID), 'NOT FOUND');
 END; END;
 -- Stop Immediately if not found -- Stop Immediately if not found
-IF (@StationID IS NULL) or (@BankAccountID is NULL) or (@PaymentAccountID is NULL)  +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;+    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 -- 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); DECLARE @Orders TABLE (ID INT PRIMARY KEY, ClassTypeID INT NOT NULL);
-  + 
-INSERT INTO @Orders +INSERT INTO @Orders
     SELECT ID, ClassTypeID     SELECT ID, ClassTypeID
-    FROM TransHeader +    FROM TransHeader
     WHERE (TransactionType IN (1,6)) AND (StatusID = 3) AND (StationID = @StationID);     WHERE (TransactionType IN (1,6)) AND (StatusID = 3) AND (StationID = @StationID);
 DECLARE @AREntries TABLE (ID INT PRIMARY KEY, ClassTypeID INT NOT NULL); DECLARE @AREntries TABLE (ID INT PRIMARY KEY, ClassTypeID INT NOT NULL);
-  + 
-INSERT INTO @AREntries +INSERT INTO @AREntries
     SELECT ID, ClassTypeID     SELECT ID, ClassTypeID
-    FROM Ledger  +    FROM Ledger 
- WHERE TransactionID IN (SELECT ID FROM @Orders) +    WHERE TransactionID IN (SELECT ID FROM @Orders) 
- and GLAccountID = 14  -- Accounts Receivable Account +    AND GLAccountID = 14  -- Accounts Receivable Account 
- +
 -- display the list of orders to change if in debug mode -- display the list of orders to change if in debug mode
-IF (@debug=1)  +IF (@debug=1) 
-    SELECT T.ID, T.OrderNumber, T.Description, T.TotalPrice, T.BalanceDue +    SELECT T.ID, T.OrderNumber, T.Description, T.TotalPrice, T.BalanceDue
     FROM TransHeader T     FROM TransHeader T
     JOIN @Orders O ON T.ID = O.ID     JOIN @Orders O ON T.ID = O.ID
- +
 -- 4. Now Change out the GL Entries for these orders -- 4. Now Change out the GL Entries for these orders
-IF (@makechanges = 1) +IF (@makechanges = 1)
     UPDATE Ledger     UPDATE Ledger
     SET SeqID = SeqID + 1     SET SeqID = SeqID + 1
Line 106: Line 92:
     , Classification = COALESCE(@PaymentAccountID, 16)  -- Payment Method     , Classification = COALESCE(@PaymentAccountID, 16)  -- Payment Method
     , EntryType = 2     , EntryType = 2
-    WHERE ID in (Select ID From @AREntries)+    WHERE ID IN (SELECT ID FROM @AREntries)
 ELSE ELSE
     SELECT CONVERT(VARCHAR(12), COUNT(*)) + ' GL Changes Would Have Been Made'     SELECT CONVERT(VARCHAR(12), COUNT(*)) + ' GL Changes Would Have Been Made'
-    FROM Ledger  +    FROM Ledger 
- WHERE ID in (Select ID From @AREntries)+    WHERE ID IN (SELECT ID FROM @AREntries)
 ; ;
- +
 -- 5. Now Change the Order Record -- 5. Now Change the Order Record
-IF (@makechanges = 1) +IF (@makechanges = 1)
     UPDATE TransHeader     UPDATE TransHeader
     SET SeqID = SeqID + 1     SET SeqID = SeqID + 1
Line 128: Line 114:
     WHERE ID IN (SELECT ID FROM @Orders)     WHERE ID IN (SELECT ID FROM @Orders)
 ; ;
-  + 
--- 6. Insert into the RefreshMonitor table so these records will refresh to Closed in Control +-- 6. Insert into the RefreshMonitor table so these records will refresh to Closed in Control
 IF (@makechanges = 1) IF (@makechanges = 1)
 BEGIN BEGIN
Line 141: Line 127:
 </code> </code>
  
 +===== Approach 2 =====
  
 +This approach closes all orders in Sale regardless of station. The accounts are all preset in SQL.
  
-=Approach 2=+===== Basic Procedure =====
  
- +  - 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.
- +
-This approach closes all orders in Sale regardless of station.  The accounts are all preset in SQL. +
- +
- +
- +
-===== Basic Procedure ===== +
-  - 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. +
   - Create a checkbox UDF in Control named **CloseOrder**.   - Create a checkbox UDF in Control named **CloseOrder**.
-  - 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.+  - 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.
   - Once you have unchecked all orders that should remain, run Query 2 to close all orders in sale that have the CloseOrder UDF enabled.   - Once you have unchecked all orders that should remain, run Query 2 to close all orders in sale that have the CloseOrder UDF enabled.
- 
- 
  
 ===== Query 1 ===== ===== Query 1 =====
- 
- 
  
 <code sql> <code sql>
Line 167: Line 144:
 UPDATE TransHeaderUserField UPDATE TransHeaderUserField
 SET CloseOrder = 1 SET CloseOrder = 1
-WHERE ID IN (SELECT ID FROM TransHeader WHERE TransActionType in (1,6) AND StatusID = 3)+WHERE ID IN (SELECT ID FROM TransHeader WHERE TransActionType IN (1,6) AND StatusID = 3)
 ; ;
 </code> </code>
- 
- 
  
 ===== Query 2 ===== ===== Query 2 =====
- 
- 
  
 <code sql> <code sql>
Line 182: Line 155:
 -- the Undeposited Funds account. -- the Undeposited Funds account.
 UPDATE GL UPDATE GL
-SET SeqID = SeqID + 1 +SET SeqID = SeqID + 1
 , Classification = 16 , Classification = 16
 , GLAccountID = 91 , GLAccountID = 91
Line 202: Line 175:
 </code> </code>
  
 +Version Information
  
- 
-=Version Information= 
   * Control: 5.7+   * Control: 5.7+
  
- +Related SQLs
- +
-=Related SQLs=+