Explanation of SQL

This SQL is used to adjust the InventoryLog so that the totals match the current inventory in the Inventory table. It does this by creating an adjustment record in the InventoryLog table for any difference found.

*Notes:*

• This query does not adjust the GL. Therefore, the cummulative value of inventory could become out of sync with the actual inventory counts. This should only be run under the direction of Cyrious technical support.
• This query uses the SQL function control_sql_-_add_function_to_compare_inventory_totals_to_log_records to determine any adjustments that need to be made. If it is not already there, you may need to add this query.
• After this query is run, you must either restart the SSLIP or log into the SSLIP and tell it to Resync IDs. This SQL inserts new records in the database that Control is unaware of. If allowed to continue without either of these actions, the users will receive Key Violations soon after this query is run.
• You may need to run a maintenance query on the InventoryLog table to update the InventoryID to the proper inventory record. In at least some customer data, the InventoryID has been observed to point to a warehouse summary record and not the actual inventory record. The correcting query can be found control_sql_-_fix_inventorylog_to_point_to_proper_inventory_record.

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.

As written, the following SQL will only display the changes to be made. It will not actually make the changes. To have this query actually insert the records otherwise displayed, uncomment out the two lines that begin with “Insert Into” so that the results are then added to their respective database tables.

-- This query creates the corresponding InventoryLog journal
-- entries so that the cummulative Log matches the current Inventory.
-- It does this by posting a manual Log adjustment entry
-- for any difference.
-- Step 1
-- 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)
;
-- Step 2
-- Update the Journal table first!
-- If you do the Log table first, there will be nothing in the
-- Check_CompareInventoryToLog() to create the Journal entries.
-- insert into Journal
SELECT
RowID + @FirstID,
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,
'Query Correction' AS Description,
'' AS Notes,
GetDate() AS StartDateTime,
GetDate() AS EndDateTime,
CAST(NULL AS DateTime) AS TotalTime,
CAST(NULL AS DateTime) AS ScheduledDateTime,
CAST(10 AS INT) AS CompletedByID,
GetDate() 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,
'' AS VoidedReason,
GetDate() AS QueryStartDateTime,
GetDate() 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,
'' AS ActivityTypeText,
CAST(0 AS bit) AS IsBillable,
CAST(NULL AS datetime) AS BillableDateTime,
CAST(0 AS bit) AS UseActualTime,
'' 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,
'' AS SpecialCode,
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 Check_CompareInventoryToLog()
;
-- Step 3
-- now update the InventoryLog Table
-- insert into InventoryLog
SELECT
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,
BilledDelta AS QuantityBilled,
RODelta AS QuantityReceivedOnly,
OnHandDelta AS QuantityOnHand,
ReservedDelta AS QuantityReserved,
AvailableDelta AS QuantityAvailable,
OnOrderDelta AS QuantityOnOrder,
ExpectedDelta AS QuantityExpected,
(SELECT UnitCost FROM Part WHERE Part.ID = PartID ) AS UnitCost,
(SELECT UnitCost FROM Part WHERE Part.ID = PartID ) * BilledDelta AS Cost,
PartID,
CAST(12014 AS INT) AS PartClassTypeID,
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,
COALESCE(WarehouseID,10) AS FromWarehouseID,
COALESCE(DivisionID,10) AS FromDivisionID,
CAST(NULL AS INT) AS SortIndex,
CAST(NULL AS INT) AS UnitID
FROM Check_CompareInventoryToLog()

• Entered : 01/2011
• Version : 4.5+