======  ======


===== Explanation of SQL =====


Prior to mid 2009, Control only checked if the entire GL entry was in balance, not if the Off-Balance sheet portion and on-balance sheet portion were both in balance by themselves.  It was therefore possible to set up parts that used On-Balance Sheet Inventory and Off-Balance Sheet Expenses (then called computed costs).


This SQL is used to identify GL entries where the On-Balance sheet and Off-Balance sheet portions don't independently sum to zero.


===== Risk of Data Corruption if Run Improperly =====

The selection of this data for examination poses no risks. However, some of the queries following it do alter data irrevocably and should only be used by a skilled technician.


===== SQL =====


<code sql>
-- GL Out of Balance By Order
declare @StartDate DateTime
SET @StartDate = '1/1/2000'
 
SELECT    
    (SELECT DivisionName AS Division 
        FROM EmployeeGroup 
        WHERE OOBGL.DivisionID = EmployeeGroup.ID) AS Division,
    TransHeader.OrderNumber,
    TransHeader.BillNumber,
    OOBGL.OOBAmount,
    OffBalanceSheet,
    Account.CompanyName,
    TransHeader.Description,
    TransHeader.AccountID,
    OOBGL.TransHeaderID,
    (SELECT max(EntryDateTime) 
        FROM Ledger GL2 
        WHERE GL2.TransactionID = OOBGL.TransHeaderID) AS LastGLEntryDate
FROM
(
    SELECT    Coalesce(DivisionID, 10) AS DivisionID, 
            COALESCE (TransactionID, 0) AS TransHeaderID, 
            SUM(Amount) AS OOBAmount,
            OffBalanceSheet
    FROM    Ledger
    WHERE EntryDateTime >= @StartDate
    GROUP BY Coalesce(DivisionID, 10), COALESCE (TransactionID, 0), OffBalanceSheet
    HAVING    (SUM(Amount)>0)
) AS OOBGL
 
LEFT OUTER JOIN TransHeader ON TransHeader.ID = OOBGL.TransHeaderID 
 
LEFT OUTER JOIN Account ON TransHeader.AccountID = Account.ID
 
ORDER BY Division, OrderNumber, BillNumber, OffBalanceSheet
</code>


==Possible Fixes==


The following SQLs may be used to fix certain Off-Balance problems, but should only be used by a skilled technician.  Your data should be backed up first as these changes are irrevocable in the current data!


===Set Bills to Only Use Real Costs===


Bills should never interact with off-balance sheet costs, so this query adjust the GL of all Bill entries that are set as OffBalanceSheet = 1 to zero.


<code sql>
update Ledger
set OffBalanceSheet = 0
where OffBalanceSheet = 1 and 
TransactionID in (select ID from TransHeader where TransactionType = 8)
</code>


===Update Off-Balance Orders Based on the current Part Settings===


This query updates the GL to fix off-balance entries.  


<html><div style="margin-left: 40px;"> **WARNING**: This query uses the part's current setting to determine if the cost should be accured (on balance sheet) or computer (off balance sheet).  There is no way to know how the part was set at the time of the GL entry so it could end up adjusting it the wrong way.  Make sure everyone understands this before running this query! </div></html>


<code sql>
update Ledger
set OffBalanceSheet = (select abs(1-AccrueCosts) from Part where Part.ID = Ledger.PartID) 
where OffBalanceSheet = (select AccrueCosts from Part where Part.ID = Ledger.PartID) 
    and PartID is not NULL
    and TransactionID in 
	(
		select distinct TransactionID from
		(
		select sum(amount) Amount, transactionid, ItemName, OffBalanceSheet, AccrueCosts,
		  (select OrderNumber from TransHeader where ID = TransactionID) as OrderNumber
		from ledger
		join part on ledger.partid = part.id
		where OffBalanceSheet = AccrueCosts
		group by transactionid, ItemName, OffBalanceSheet, AccrueCosts
		having sum(amount)  0
		-- order by OrderNumber desc
		) TempA
	) 
</code>


===== Version Information =====
  * Entered : 6/2010
  * Version : 4.4+