Differences
This shows you the differences between two versions of the page.
— |
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, | ||
+ | , cast(coalesce(InvYear, | ||
+ | , coalesce((SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID), | ||
+ | , coalesce((Select ItemName from Part where ID = coalesce(GLPartID, | ||
+ | , coalesce(InventoryValue, | ||
+ | , coalesce(GLAssetAmount, | ||
+ | , floor((COALESCE(InventoryValue, | ||
+ | , AverageCost | ||
+ | , AssetAccountID | ||
+ | , coalesce(InvPartID, | ||
+ | , 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, | ||
+ | ) 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, | ||
+ | ) TempAllGL | ||
+ | ON TempInv.AssetAccountID = TempAllGL.GLAccountID | ||
+ | and TempInv.InvPartID = TempAllGL.GLPartID | ||
+ | and TempInv.InvYear = TempAllGL.GLYear | ||
+ | where (COALESCE(InventoryValue, | ||
+ | and (floor((COALESCE(InventoryValue, | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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/ | ||
+ | IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' | ||
+ | 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, | ||
+ | , cast(coalesce(InvYear, | ||
+ | , coalesce((SELECT AccountName FROM GLAccount WHERE ID = AssetAccountID), | ||
+ | , coalesce((Select ItemName from Part where ID = coalesce(GLPartID, | ||
+ | , coalesce(InventoryValue, | ||
+ | , coalesce(GLAssetAmount, | ||
+ | , floor((COALESCE(InventoryValue, | ||
+ | , AverageCost | ||
+ | , AssetAccountID | ||
+ | , coalesce(InvPartID, | ||
+ | , 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, | ||
+ | ) 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, | ||
+ | ) TempAllGL | ||
+ | ON TempInv.AssetAccountID = TempAllGL.GLAccountID | ||
+ | and TempInv.InvPartID = TempAllGL.GLPartID | ||
+ | and TempInv.InvYear = TempAllGL.GLYear | ||
+ | where (COALESCE(InventoryValue, | ||
+ | and (floor((COALESCE(InventoryValue, | ||
+ | and coalesce(InvPartID, | ||
+ | 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' | ||
+ | -- 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, | ||
+ | ' | ||
+ | '' | ||
+ | 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, | ||
+ | '' | ||
+ | cast(' | ||
+ | cast(' | ||
+ | CAST(NULL AS DateTime) AS TotalTime, | ||
+ | cast(' | ||
+ | CAST(10 AS INT) AS CompletedByID, | ||
+ | cast(' | ||
+ | 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('' | ||
+ | cast(' | ||
+ | cast(' | ||
+ | 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('' | ||
+ | CAST(0 AS bit) AS IsBillable, | ||
+ | CAST(NULL AS datetime) AS BillableDateTime, | ||
+ | CAST(0 AS bit) AS UseActualTime, | ||
+ | CAST('' | ||
+ | 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('' | ||
+ | 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, | ||
+ | ' | ||
+ | '' | ||
+ | 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, | ||
+ | CAST(0 AS INT) 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, | ||
+ | ' | ||
+ | '' | ||
+ | GetDate() AS ModifiedDate, | ||
+ | CAST(0 AS INT) AS SeqID, | ||
+ | CAST(0 AS bit) AS IsSystem, | ||
+ | CAST(1 AS bit) AS IsActive | ||
+ | , cast(' | ||
+ | , 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 | ||
+ | , ' | ||
+ | , 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, | ||
+ | ' | ||
+ | '' | ||
+ | GetDate() AS ModifiedDate, | ||
+ | CAST(0 AS INT) AS SeqID, | ||
+ | CAST(0 AS bit) AS IsSystem, | ||
+ | CAST(1 AS bit) AS IsActive | ||
+ | , cast(' | ||
+ | , -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 | ||
+ | , 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 | ||
+ | , ' | ||
+ | , 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 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 11/2011 | ||
+ | * Version : 4.5+ | ||
+ | |||
+ | |||
+ | |||