Differences

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

Link to this comparison view

control_sql_-_create_inventorylog_and_gl_entries_to_balance_historical_inventory [2019/01/27 11:28] (current)
Line 1: Line 1:
 +===== Explanation of SQL =====
 +
 +*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.
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**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**.
 +
 +
 +
 +===== SQL to View GL Imbalances =====
 +
 +
 +
 +<code sql>
 +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)
 +</code>
 +
 +
 +
 +===== SQL to Create InventoryLog and GL =====
 +
 +
 +
 +<code sql>
 +-- 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
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 11/2011
 +  * Version : 4.5+
 +
 +
 +