control_sql_-_create_inventorylog_and_gl_entries_to_balance_historical_inventory [Support]

*This is only approved for use in Control version 4.6 or lower. Do not use in Control version 5.1.*

This SQL compares the historic GL with the historic InventoryLog and then compares them based on the CURRENT inventory value.

It then creates the following to reconcile

InventoryLog Entries for each part, for each year, for each Inventory Account
Journal for the same
GL Entries for each of the Inventory Log entries.

The SSLIP must be shut down when this is run.

WARNING Do not use this SQL unless you really understand it. It should only be used after all other cleanup is completed.

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.

SELECT
  ROW_NUMBER() OVER (ORDER BY CAST(COALESCE(InvYear, GLYear) AS VARCHAR(4)), COALESCE(InvPartID, GLPartID)) AS RowID
  , CAST(COALESCE(InvYear, GLYear) AS VARCHAR(4)) AS [YEAR]
  , COALESCE((SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID), '') AS [GL Account]
  , COALESCE((SELECT ItemName FROM Part WHERE ID = COALESCE(GLPartID, InvPartID)), '') AS [Part]
  , COALESCE(InventoryValue, 0) AS [Inventory VALUE]
  , COALESCE(GLAssetAmount, 0) AS [GL VALUE]
  , FLOOR((COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0))*100+0.5)/100.0 AS Adjustment
  , AverageCost
  , AssetAccountID
  , COALESCE(InvPartID, GLPartID) AS PartID
  , InvPartID
  , GLPartID
INTO TempTable
FROM
(    SELECT
            SUM(IL.QuantityBilled * Inventory.AverageCost) AS InventoryValue,
            Inventory.AssetAccountID,
            Inventory.AverageCost,
            Part.ID AS InvPartID,
            YEAR(Journal.CompletedDateTime) AS InvYear
     FROM Part
     LEFT JOIN Inventory ON Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12200
     LEFT JOIN InventoryLog IL ON Inventory.ID = IL.InventoryID
          JOIN Journal ON Journal.ID = IL.ID
     WHERE Part.TrackInventory = 1 AND Part.AccrueCosts = 1 AND Part.TrackCosts = 1
     GROUP BY Inventory.AssetAccountID, Part.ID, YEAR(Journal.CompletedDateTime), Inventory.AverageCost
) AS TempInv
FULL OUTER JOIN
(
    SELECT GLAccountID
       , SUM(amount) AS GLAssetAmount
       , PartID AS GLPartID
       , YEAR(EntryDateTime) AS GLYear
    FROM GL
    WHERE GLAccountID IN (SELECT ID FROM GLAccount WHERE GLClassificationType = 1003)
    GROUP BY GLAccountID, PartID, YEAR(EntryDateTime)
) TempAllGL
ON TempInv.AssetAccountID = TempAllGL.GLAccountID
  AND TempInv.InvPartID = TempAllGL.GLPartID
  AND TempInv.InvYear = TempAllGL.GLYear
WHERE (COALESCE(InventoryValue,0)  0 OR COALESCE(GLAssetAmount,0)  0)
AND (FLOOR((COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0))*100+0.5)  0)

-- This query creates the corresponding GL and InventoryLog journal
-- entries so that the cummulative value of inventory matches the historical count of inventory.
--
-- WARNING #1: The valuation that is used is based on the CURRENT cost of the part, regardless of the
-- value of the part at the time.  This is because the UnitCost in the InventoryLog is not reliable.
--
-- WARNING #2: This query IS NOT WAREHOUSE OR DIVISION SAFE.  It assumes there is only 1 warehouse/division.
IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TempTable' )
BEGIN
  DROP TABLE TempTable;
END
;
-- Step 1
-- Get the Data and create #TempTable
-- SQL to Find Discrepencies in Inventory GL Value v Inventory History
--
SELECT
  ROW_NUMBER() OVER (ORDER BY CAST(COALESCE(InvYear, GLYear) AS VARCHAR(4)), AssetAccountID, COALESCE(InvPartID, GLPartID)) AS RowID
  , CAST(COALESCE(InvYear, GLYear) AS VARCHAR(4)) AS [YEAR]
  , COALESCE((SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID), '') AS [GL Account]
  , COALESCE((SELECT ItemName FROM Part WHERE ID = COALESCE(GLPartID, InvPartID)), '') AS [Part]
  , COALESCE(InventoryValue, 0) AS [Inventory VALUE]
  , COALESCE(GLAssetAmount, 0) AS [GL VALUE]
  , FLOOR((COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0))*100+0.5)/100.0 AS Adjustment
  , AverageCost
  , AssetAccountID
  , COALESCE(InvPartID, GLPartID) AS PartID
  , InvPartID
  , GLPartID
INTO TempTable
FROM
(    SELECT
            SUM(IL.QuantityBilled * Inventory.AverageCost) AS InventoryValue,
            Inventory.AssetAccountID,
            Inventory.AverageCost,
            Part.ID AS InvPartID,
            YEAR(Journal.CompletedDateTime) AS InvYear
     FROM Part
     LEFT JOIN Inventory ON Part.ID = Inventory.PartID AND Inventory.ClassTypeID = 12200
     LEFT JOIN InventoryLog IL ON Inventory.ID = IL.InventoryID
          JOIN Journal ON Journal.ID = IL.ID
     WHERE Part.TrackInventory = 1 AND Part.AccrueCosts = 1 AND Part.TrackCosts = 1
     GROUP BY Inventory.AssetAccountID, Part.ID, YEAR(Journal.CompletedDateTime), Inventory.AverageCost
) AS TempInv
FULL OUTER JOIN
(
    SELECT GLAccountID
       , SUM(amount) AS GLAssetAmount
       , PartID AS GLPartID
       , YEAR(EntryDateTime) AS GLYear
    FROM GL
    WHERE GLAccountID IN (SELECT ID FROM GLAccount WHERE GLClassificationType = 1003)
    GROUP BY GLAccountID, PartID, YEAR(EntryDateTime)
) TempAllGL
ON TempInv.AssetAccountID = TempAllGL.GLAccountID
  AND TempInv.InvPartID = TempAllGL.GLPartID
  AND TempInv.InvYear = TempAllGL.GLYear
WHERE (COALESCE(InventoryValue,0)  0 OR COALESCE(GLAssetAmount,0)  0)
AND (FLOOR((COALESCE(InventoryValue,0) - COALESCE(GLAssetAmount,0))*100+0.5)  0)
AND COALESCE(InvPartID, GLPartID) IS NOT NULL
AND AssetAccountID IS NOT NULL
-- select * from TempTable
-- Step 2
-- Save the starting ID for the next journal
-- so that the process of posting the updates doesn't
-- adjust it.
DECLARE @FirstID INT;
SET @FirstID = 1000 + (SELECT MAX(ID) FROM Journal)
DECLARE @FirstGLID INT;
SET @FirstGLID = 1000 + (SELECT MAX(ID) FROM Ledger)
-- Step 3
-- Update the Journal
INSERT INTO Journal
SELECT -- top 2
RowID + @FirstID AS ID,
CAST(-1 AS INT) AS StoreID,
CAST(20530 AS INT) ClassTypeID,
'CyriousTech' AS ModifiedByUser,
'' AS ModifiedByComputer,
GetDate() AS ModifiedDate,
CAST(0 AS INT) AS SeqID,
CAST(0 AS bit) AS IsSystem,
CAST(1 AS bit) AS IsActive,
CAST(10 AS INT) AS EmployeeID,
CAST(17 AS INT) AS JournalActivityType,
'Part Usage' AS JournalActivityText,
CAST([YEAR] + ' EOY Correction for '+Part AS VARCHAR(50)) AS Description,
'' AS Notes,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS StartDateTime,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS EndDateTime,
CAST(NULL AS DateTime) AS TotalTime,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS ScheduledDateTime,
CAST(10 AS INT) AS CompletedByID,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS CompletedDateTime,
CAST(1 AS bit) AS IsSummary,
CAST(1 AS bit) AS IsDetail,
CAST(NULL AS INT) AS SummaryID,
CAST(NULL AS INT) AS SummaryClassTypeID,
CAST(0 AS FLOAT) AS SummaryAmount,
CAST(0 AS FLOAT) AS DetailAmount,
CAST(NULL AS INT) AS StartGLGroupID,
CAST(NULL AS INT) AS EndGLGroupID,
CAST(NULL AS INT) AS AccountID,
CAST(NULL AS INT) AS AccountClassTypeID,
CAST(NULL AS INT) AS ContactID,
CAST(NULL AS INT) AS ContactClassTypeID,
CAST(NULL AS INT) AS TransactionID,
CAST(NULL AS INT) AS TransactionClassTypeID,
CAST(0 AS bit) AS IsVoided,
CAST(NULL AS DateTime) AS VoidedDateTime,
CAST(NULL AS INT) AS VoidedEntryID,
CAST(NULL AS INT) AS VoidedEntryClassTypeID,
CAST('' AS VARCHAR(3)) AS VoidedReason,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS QueryStartDateTime,
CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) QueryEndDateTime,
CAST(NULL AS DateTime) AS ReminderDateTime,
CAST(0 AS bit) AS ReminderPrompt,
CAST(NULL AS INT) AS PartID,
CAST(0 AS INT) AS ActivityType,
CAST('' AS VARCHAR(3)) AS ActivityTypeText,
CAST(0 AS bit) AS IsBillable,
CAST(NULL AS datetime) AS BillableDateTime,
CAST(0 AS bit) AS UseActualTime,
CAST('' AS VARCHAR(3)) AS BillingNotes,
CAST(0 AS INT) AS BillingType,
CAST(0 AS FLOAT) AS TotalBilledTime,
CAST(NULL AS INT) AS RecurringActivityID,
CAST(NULL AS INT) AS LinkID,
CAST(NULL AS INT) AS LinkStoreID,
CAST(NULL AS INT) AS LinkClassTypeID,
CAST('' AS VARCHAR(3)) AS SpecialCode,
10 AS DivisionID, -- DivisionID,
CAST(0 AS bit) AS HasCalendarLinks,
CAST(NULL AS INT) AS TipRecipientID,
CAST(12014 AS INT) AS PartClassTypeID,
CAST(NULL AS INT) AS RecurringClassTypeID,
CAST(NULL AS INT) AS StationID,
CAST(NULL AS INT) AS StationClassTypeID,
--cast(null as varchar(10)) as CalendarLinksXML,
CAST(NULL AS INT) AS CurrentState,
CAST(NULL AS INT) AS StageID,
CAST(NULL AS INT) AS StageClassTypeID
FROM TempTable
;
-- Step 4
-- now update the InventoryLog Table
INSERT INTO InventoryLog
SELECT -- top 2
RowID + @FirstID AS ID,
CAST(-1 AS INT) AS StoreID,
CAST(20530 AS INT) ClassTypeID,
'CyriousTech' AS ModifiedByUser,
'' AS ModifiedByComputer,
GetDate() AS ModifiedDate,
CAST(0 AS INT) AS SeqID,
CAST(0 AS bit) AS IsSystem,
CAST(1 AS bit) AS IsActive,
CAST(0 AS FLOAT) AS QuantityBilled,
CAST(0 AS FLOAT) AS QuantityReceivedOnly,
CAST(0 AS FLOAT) AS QuantityOnHand,
CAST(0 AS FLOAT) AS QuantityReserved,
CAST(0 AS FLOAT) AS QuantityAvailable,
CAST(0 AS FLOAT) AS QuantityOnOrder,
CAST(0 AS FLOAT) AS QuantityExpected,
AverageCost AS UnitCost,
Adjustment AS Cost,
PartID,
CAST(12014 AS INT) AS PartClassTypeID,
(SELECT Top 1 ID FROM Inventory WHERE PartID = TempTable.PartID AND ClassTypeID = 12200) AS InventoryID,
CAST(12200 AS INT) AS InventoryClassTypeID,
CAST(NULL AS INT) AS TransDetailID,
CAST(NULL AS INT) AS TransDetailClassTypeID,
CAST(NULL AS INT) AS TransPartID,
CAST(NULL AS INT) AS TransPartClassTypeID,
CAST(NULL AS INT) AS ProductID,
CAST(NULL AS INT) AS ProductClassTypeID,
CAST(NULL AS INT) AS ModifierID,
CAST(NULL AS INT) AS ModifierClassTypeID,
CAST(NULL AS INT) AS ActivityLinkID,
CAST(NULL AS INT) AS ActivityLinkClassTypeID,
CAST(NULL AS INT) AS ToInventoryID,
CAST(NULL AS INT) AS ToWarehouseID,
CAST(NULL AS INT) AS ToDivisionID,
CAST(0 AS INT) AS FromWarehouseID, -- COALESCE(WarehouseID,10) AS FromWarehouseID,
CAST(0 AS INT) AS FromDivisionID, -- COALESCE(DivisionID,10) AS FromDivisionID,
CAST(NULL AS INT) AS SortIndex,
CAST(NULL AS INT) AS UnitID
FROM TempTable
-- STEP 5a - Create the GL Entries for the Inventory Account
--
--
INSERT INTO Ledger
SELECT -- top 4
RowID*2 + @FirstGLID AS ID,
CAST(-1 AS INT) AS StoreID,
CAST(8900 AS INT) ClassTypeID,
'CyriousTech' AS ModifiedByUser,
'' AS ModifiedByComputer,
GetDate() AS ModifiedDate,
CAST(0 AS INT) AS SeqID,
CAST(0 AS bit) AS IsSystem,
CAST(1 AS bit) AS IsActive
, CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS EntryDateTime
, Adjustment AS Amount
, CAST(NULL AS INT) AS Classification
, CAST(0 AS bit) AS IsTaxable
, CAST(NULL AS INT) AS GroupID
, CAST(AssetAccountID AS INT) AS GLAccountID
, CAST(8001 AS INT) AS GLAccountClassTypeID
, CAST(NULL AS INT) AS AccountID
, CAST(NULL AS INT) AS AccountClassTypeID
, CAST(NULL AS INT) AS TransactionID
, CAST(NULL AS INT) AS TransactionClassTypeID
, CAST(NULL AS INT) AS TransDetailID
, CAST(NULL AS INT) AS TransDetailClassTypeID
, CAST(NULL AS INT) AS GoodsItemID
, CAST(NULL AS INT) AS GoodsItemClassTypeID
, CAST([YEAR] + ' EOY Correction for '+Part AS VARCHAR(50)) AS Description
, CAST(10 AS INT) AS DivisionID
, CAST(NULL AS VARCHAR(50)) AS Notes
, CAST(0 AS bit) AS IsModified
, CAST(NULL AS bit) AS IsUser
, CAST(NULL AS INT) AS TaxClassID
, CAST(0 AS FLOAT) AS Quantity
, PartID AS PartID
, CAST(12014 AS INT) AS PartClassTypeID
, RowID + @FirstID AS JournalID
, CAST(20530 AS INT) AS JournalClassTypeID
, CAST(0 AS bit) AS Reconciled
, CAST(NULL AS DateTime) AS ReconciliationDateTime
, CAST(NULL AS INT) AS ReconciliationID
, CAST(NULL AS INT) AS ReconciliationClassTypeID
, CAST(NULL AS INT) AS ProcessedDivisionID
, CAST(1003 AS INT) AS GLClassificationType
, 'Inventory' AS GLClassTypeName
, CAST(NULL AS INT) AS TransPartID
, CAST(NULL AS INT) AS TransPartClassTypeID
, CAST(NULL AS INT) AS StationID
, CAST(NULL AS INT) AS PayrollID
, CAST(NULL AS INT) AS PayrollClassTypeID
, CAST(NULL AS INT) AS DepositJournalID
, CAST(NULL AS INT) AS EntryType
, CAST(10 AS INT) AS EmployeeID
, CAST(0 AS bit) AS OffBalanceSheet
, CAST(10 AS INT) AS WarehouseID
, CAST(10 AS INT) AS InventoryID
FROM TempTable
-- STEP 5b - Create the GL Entries for the Inventory Adjustment
--
--
INSERT INTO Ledger
SELECT -- top 4
RowID*2+1 + @FirstGLID AS ID,
CAST(-1 AS INT) AS StoreID,
CAST(8900 AS INT) ClassTypeID,
'CyriousTech' AS ModifiedByUser,
'' AS ModifiedByComputer,
GetDate() AS ModifiedDate,
CAST(0 AS INT) AS SeqID,
CAST(0 AS bit) AS IsSystem,
CAST(1 AS bit) AS IsActive
, CAST('12/31/'+[YEAR]+' 12:00' AS DateTime) AS EntryDateTime
, -Adjustment AS Amount
, CAST(NULL AS INT) AS Classification
, CAST(0 AS bit) AS IsTaxable
, CAST(NULL AS INT) AS GroupID
, CAST(33 AS INT) AS GLAccountID  -- Inventory Adjustment Account
, CAST(8001 AS INT) AS GLAccountClassTypeID
, CAST(NULL AS INT) AS AccountID
, CAST(NULL AS INT) AS AccountClassTypeID
, CAST(NULL AS INT) AS TransactionID
, CAST(NULL AS INT) AS TransactionClassTypeID
, CAST(NULL AS INT) AS TransDetailID
, CAST(NULL AS INT) AS TransDetailClassTypeID
, CAST(NULL AS INT) AS GoodsItemID
, CAST(NULL AS INT) AS GoodsItemClassTypeID
, CAST([YEAR] + ' EOY Correction for '+Part AS VARCHAR(50)) AS Description
, CAST(10 AS INT) AS DivisionID
, CAST(NULL AS VARCHAR(50)) AS Notes
, CAST(0 AS bit) AS IsModified
, CAST(NULL AS bit) AS IsUser
, CAST(NULL AS INT) AS TaxClassID
, CAST(0 AS FLOAT) AS Quantity
, PartID AS PartID
, CAST(12014 AS INT) AS PartClassTypeID
, RowID + @FirstID AS JournalID
, CAST(20530 AS INT) AS JournalClassTypeID
, CAST(0 AS bit) AS Reconciled
, CAST(NULL AS DateTime) AS ReconciliationDateTime
, CAST(NULL AS INT) AS ReconciliationID
, CAST(NULL AS INT) AS ReconciliationClassTypeID
, CAST(NULL AS INT) AS ProcessedDivisionID
, CAST(1003 AS INT) AS GLClassificationType
, 'Current Asset' AS GLClassTypeName
, CAST(NULL AS INT) AS TransPartID
, CAST(NULL AS INT) AS TransPartClassTypeID
, CAST(NULL AS INT) AS StationID
, CAST(NULL AS INT) AS PayrollID
, CAST(NULL AS INT) AS PayrollClassTypeID
, CAST(NULL AS INT) AS DepositJournalID
, CAST(NULL AS INT) AS EntryType
, CAST(10 AS INT) AS EmployeeID
, CAST(0 AS bit) AS OffBalanceSheet
, CAST(10 AS INT) AS WarehouseID
, CAST(10 AS INT) AS InventoryID
FROM TempTable

Entered : 11/2011
Version : 4.5+

You could leave a comment if you were logged in.

Explanation of SQL

Risk of Data Corruption if Run Improperly

SQL to View GL Imbalances

SQL to Create InventoryLog and GL

Version Information