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+
 +
 +
 +