- Honeywell CK65
Thursday, December 31, 2020
Monday, December 21, 2020
Kingswaysoft - Deploying SSIS Package - User cannot login error even though the package runs correctly in Visual Studi
Solution:
When deploying the project, choose "EncryptWithPassword" as the protectionlevel.
On Project>Properties - Choose "Encrypt all data with Password"
On Package>Double click to enter>Properties - Choose "Encrypt all data with Password"
Enter password in PackagePassword field
Debug>Rebuild Solution
Friday, December 18, 2020
NAV / BC - Inventory Valuation Report shows value when stock is zero
- There are two solutions.
- Solution 1
- Change average cost calculation in inventory setup to Item,Location,Variant
- However, this will start tracking separate costs per location
- Solution 2
- https://www.navug.com/communities/community-home/digestviewer/viewthread?GroupId=49&MID=1095
- 1) Bring the inventory to zero. Physical inventory journal works well. (Lets say your using Jan 1, 2014 for your posting date)
- 2) Run the adjust cost.
- 3) Run inventory valuation.
- 4) Add 1 of your item back into stock at the opposite amount of what your inventory valuation is. If you have -0.05 as your inventory valuation make sure you add one at 0.05. Make sure your posting date is one day offset from your physical inventory date. (Use Jan 2, 2014 for your posting date).
- 5) Run the adjust cost routine and inventory valuation report.
- 6) You should have 1 in stock at 0 dollars.
- 7) Adjust your inventory out again on a different date (Use Jan 3, 2014) with a cost of zero.
- 8) Run adjust cost and inventory valuation. You should have zero on hand and zero dollar value.
- 9) Adjust your inventory back into the system at the correct quantity and dollar value. Once again use a different date. (Jan 4, 2014)
- You have to be very careful with the dates. If any transactions are posted for the items you are adjusting inside the date period you are using you will have to run this entire routine again.
Monday, December 14, 2020
NAV / Business Central - Insufficient Bin qantity when attempting to pick - CalcTotalQtyAvailToTake logic
- Calc Negative Adjustments from warehouse lines
- Warehouse Journal may have unposted negative adjustments allocating stock
- Calc ATOQty - Warehouse Activity Lines (Pick, Take, Assemble to Order)
Friday, December 11, 2020
Business Central AL - Sequence contains no matching element
- This is happening because one of your table fields has enabled = false
Wednesday, December 9, 2020
Business Central AL - the type or method setsumlinesfilter cannot be used for extension development
Add this line to your app.json for V3 and lower
"target":"Internal",
Add this line to your app.json for V4 and higher
"target":"OnPrem",
AL Snippet - Move an existing field
pageextension 50001 PageExtension50001 extends "Warehouse Shipment List"
{
layout
{
movefirst(Control1;Status)
}
}
Excel - Auto fit row height does not work
- Autofit Row heights will not work properly if
- Merge cells are used
- Bullets are used within cells
- Data is in a table
- Resolution
- Select Cells>Clear format
- Reformat your document to allow the autofit to work properly
- Format your document to use word wrap cells
- No merge cells
- No Tables
Tuesday, December 8, 2020
LS NAV / LS Central - Custom Windows POS Receipt prints extra lines, white space, blank lines
- Report Layout
- Page size is a little smaller than actual print size eg. 3 inch = 2.8 inch page
- Field padding set to 0 for all fields
- Row - Increase Height or HeightCanGrow = False
- Font size 8
- Font Type courier
- Tablix must be all left and exact same size as page, no white space on sides
- Barcode font must be installed on machine
- POS Print Utility - PrintToWindowsPrint
- Changed report number to custom report
- Local Variable POS Receipt - Changed to custom report number
Columbus - ToIncrease - Unit of Measure tables - Define KG to LB conversion rate
- T37002000 - Measuring System
- T204 - Unit of Measure - Base per unit of measure
- CheckforBase
- T5404 - Item Unit of Measure
- C37002005 - Process 800 UoM functions - ModifyItemUoM
Monday, November 30, 2020
SSIS - How to deploy a package to SSIS from Visual Studio
- Ensure you can connect to Integration Services from SSMS
- SSMS 17.9.1 is the last version that can connect to SSIS properly
- https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791
- This a link with multiple versions of SSMS : https://sqlserverbuilds.blogspot.com/2018/01/sql-server-management-studio-ssms.html
- Create SSIS Catalog
- From your SQL Server>Integration Services Catalog
- Right Click>Create Catalog
- Right click>New folder
- Publish Project in catalog
- Inside Catalog>right click on Projects folder>Deploy Project
- Select files from \Users\AdmBob\source\repos\projectname
- enter password>Apply
- Refresh
- Publish>Overwrite? click yes
- Schedule Package
- From Job, Right click, new job, new step
- Type: SQL Server integration package
- Package Source:SSIS Catalog
- Package: Local path for project (copy from \Users\AdmBob\source\repos\projectname)
- Define job schedule
Friday, November 27, 2020
Columbus /ToIncrease - Quantity (Base) available must not be less than x in Bin Content Location Code
- This is happening becasue UofM conversion factors are causing a 0.00001 difference in the total inventory calculation
- Go to Inventory Setup>Near Zero qty Value
- Set a value of 0.00001
- It will add this value to any entries that find a difference when attempting to register
SSIS and SQL Management Studio compatibility
The SSMS that works is
the 17.9.1 version . Link: https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-ver15#1791
This a link with
multiple versions of SSMS : https://sqlserverbuilds.blogspot.com/2018/01/sql-server-management-studio-ssms.html
Tuesday, November 24, 2020
SQL - How to delete large numbers of records without locking tables
- If you delete more than around 5000 records, the entire table gets locked for the duration of the delete
- If you split up the job into a loop of 1000 record delete bursts, it never locks the entire table
declare @MoreRowsToDelete bit
set @MoreRowsToDelete = 1
while @MoreRowsToDelete = 1
begin
delete top (1000) CRONUS$Preaction from CRONUS$Preaction where Date < '2020-07-01 00:00:00.000'
if not exists (select top 1 * from CRONUS$Preaction where Date < '2020-07-01 00:00:00.000')
set @MoreRowsToDelete = 0
end
Friday, November 13, 2020
Business Central - VSCode - extension could not be deployed because already deployed in another tenant
Increment the version in the app.json file
Thursday, November 12, 2020
SSIS - Cannot access *.ispac because it is in use by another process
- Go to Task Manager–> Details Tab.
- Locate the process “DtsDebugHost.exe“. Kill this process. There might be multiple instances of this process. Kill all of them.
Tuesday, November 10, 2020
Business Central - View all Objects or Run tables in the web Client
Go to this link, then filter on the app package id
Monday, November 2, 2020
Azure SQL DTU Calculator
https://dtucalculator.azurewebsites.net/
- run the powershell below for an hour
- upload the file to the above website
<#
.SYNOPSIS
Collect counters required for DTU Calculator and log as CSV.
.DESCRIPTION
Collect counters required for DTU Calculator and log as CSV.
Default log file location is C:\sql-perfmon-log.csv.
Counters are collected at 1 second intervals for 1 hour or 3600 seconds.
No support or warranty is supplied or inferred.
Use at your own risk.
.PARAMETER DatabaseName
The name of the SQL Server database to monitor.
.INPUTS
Parameters above.
.OUTPUTS
None.
.NOTES
Version: 1.0
Creation Date: May 1, 2015
Modified Date: June 17, 2016
Author: Justin Henriksen ( http://justinhenriksen.wordpress.com )
#>
Set-ExecutionPolicy -Scope Process -ExecutionPolicy Unrestricted -Force
$ErrorActionPreference = "Stop"
$VerbosePreference = "Continue"
cls
Write-Output "Collecting counters..."
Write-Output "Press Ctrl+C to exit."
$counters = @("\Processor(_Total)\% Processor Time",
"\LogicalDisk(_Total)\Disk Reads/sec",
"\LogicalDisk(_Total)\Disk Writes/sec",
"\SQLServer:Databases(_Total)\Log Bytes Flushed/sec")
Get-Counter -Counter $counters -SampleInterval 1 -MaxSamples 3600 |
Export-Counter -FileFormat csv -Path "C:\sql-perfmon-log.csv" -Force
Tuesday, October 27, 2020
Business Central - VS Code - View AL Base Code
Alpackages>Microsoft Application
You can view the source code for all objects from here
Monday, October 26, 2020
SQL - Rebuild Indexes to resolve fragmentation issues
Identify Fragmentation
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'CRONUS'), OBJECT_ID(N'Cronus$Item'), NULL, NULL , 'DETAILED');
Rebuild Indexes
USE MyDatabase; GO ALTER INDEX ALL ON MyTable REBUILD; GO
-------------------------------------------------------------------------More advanced script to report and manage the process better.Reportonly Mode = 1 will generate all scripts required to rebuild indexes for each table independently.You can choose to execute which ones are most necessary.
https://www.mssqltips.com/sqlservertip/4470/script-to-manage-sql-server-rebuilds-and-reorganize-for-index-fragmentation/
----
-- Script that reorganizes or rebuilds all indexes having an average fragmentation
-- percentage above a given threshold. It also works in the case
-- where Availability Groups are enabled as it determines if the
-- relevant databases are the primary replicas.
--
-- This script supports only SQL Server 2005 or later.
-- Also, if you execute this script in a SQL Server 2005 instance
-- or later, any databases with compatibility level 2000 (80) or earlier
-- will be automatically excluded from the index reorganization/rebuild process.
----
--Initial check - You must be SysAdmin
DECLARE @isSysAdmin INT
SET @isSysAdmin=(SELECT IS_SRVROLEMEMBER ('sysadmin'));
--Initial check - You must be using SQL Server 2005 or later
DECLARE @SQLServerVersion INT
SET @SQLServerVersion=(SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)))-1) AS INT));
IF @isSysAdmin=1 AND @SQLServerVersion >= 9
BEGIN
--
-- Variable/parameters Declaration
--
DECLARE @dbname NVARCHAR(128);
DECLARE @ReorganizeOrRebuildCommand NVARCHAR(MAX);
DECLARE @dbid INT;
DECLARE @indexFillFactor VARCHAR(5);
DECLARE @fragmentationThreshold VARCHAR(10);
DECLARE @indexStatisticsScanningMode VARCHAR(20);
DECLARE @verboseMode BIT;
DECLARE @reportOnly BIT;
DECLARE @sortInTempdb VARCHAR(3);
DECLARE @isHadrEnabled BIT;
DECLARE @databaseToCheck VARCHAR(250)
DECLARE @dynamic_command NVARCHAR(1024);
DECLARE @dynamic_command_get_tables NVARCHAR(MAX);
--Initializations - Do not change
SET @databaseToCheck=NULL;
SET @dynamic_command = NULL;
SET @dynamic_command_get_tables = NULL;
SET @isHadrEnabled=0;
SET NOCOUNT ON;
---------------------------------------------------------
--Set Parameter Values: You can change these (optional) -
--Note: The script has default parameters set -
---------------------------------------------------------
--if set to 1: it will just generate a report with the index reorganization/rebuild statements
--if set to 0: it will reorganize or rebuild the fragmented indexes
SET @reportOnly = 0;
--optional: if not set (NULL), it will scann all databases
--If name is set (i.e. 'testDB') it will just scan the given database
SET @databaseToCheck = NULL;
--maintains only the indexes that have average fragmentation percentage equal or higher from the given value
SET @fragmentationThreshold = 15;
--fill factor - the percentage of the data page to be filled up with index data
SET @indexFillFactor = 90;
--sets the scanning mode for index statistics
--available values: 'DEFAULT', NULL, 'LIMITED', 'SAMPLED', or 'DETAILED'
SET @indexStatisticsScanningMode='SAMPLED';
--if set to ON: sorts intermediate index results in TempDB
--if set to OFF: sorts intermediate index results in user database's log file
SET @sortInTempdb='ON';
--if set to 0: Does not output additional information about the index reorganization/rebuild process
--if set to 0: Outputs additional information about the index reorganization/rebuild process
SET @verboseMode = 0;
------------------------------
--End Parameter Values Setup -
------------------------------
-- check if given database exists and if compatibility level >= SQL 2005 (90)
IF @verboseMode=1
PRINT 'Checking if database '+@databaseToCheck+' exists and if compatibility level equals or greater 2005 (90)';
-- if given database does not exist, raise error with severity 20
-- in order to terminate script's execution
IF @databaseToCheck IS NOT NULL
BEGIN
DECLARE @checkResult INT
SET @checkResult=(SELECT COUNT(*) FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
IF @checkResult<1
RAISERROR('Error executing index reorganization/rebuild script: Database does not exist' , 20, 1) WITH LOG;
DECLARE @checkResult2 INT
SET @checkResult=(SELECT [compatibility_level] FROM master.sys.databases WHERE [name]=RTRIM(@databaseToCheck));
IF @checkResult<90
RAISERROR('Error executing index reorganization/rebuild script: Only databases with SQL Server 2005 or later compatibility level are supported' , 20, 1) WITH LOG;
END
IF @verboseMode=1
PRINT 'Initial checks completed with no errors.';
-- Temporary table for storing index fragmentation details
IF OBJECT_ID('tempdb..#tmpFragmentedIndexes') IS NULL
BEGIN
CREATE TABLE #tmpFragmentedIndexes
(
[dbName] sysname,
[tableName] sysname,
[schemaName] sysname,
[indexName] sysname,
[databaseID] SMALLINT ,
[objectID] INT ,
[indexID] INT ,
[AvgFragmentationPercentage] FLOAT,
[reorganizationOrRebuildCommand] NVARCHAR(MAX)
);
END
-- Initialize temporary table
DELETE FROM #tmpFragmentedIndexes;
-- Validate parameters/set defaults
IF @sortInTempdb NOT IN ('ON','OFF')
SET @sortInTempdb='ON';
-- Check if instance has AlwaysOn AGs enabled
SET @isHadrEnabled=CAST((SELECT ISNULL(SERVERPROPERTY('IsHadrEnabled'),0)) AS BIT);
-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
AND s.[compatibility_level]>=90
ORDER BY s.database_id;
END
ELSE
-- if database specified, scan only that database
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND s.[name]=RTRIM(@databaseToCheck)
END
-- if Always On Availability Groups are enabled, check for primary databases
-- (thus exclude secondary databases)
IF @isHadrEnabled=1
BEGIN
DEALLOCATE dbNames_cursor;
-- if database not specified scan all databases
IF @databaseToCheck IS NULL
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'
AND s.[name] NOT IN ( 'master', 'model', 'tempdb' )
AND s.[compatibility_level]>=90
ORDER BY s.database_id;
END
ELSE
-- if database specified, scan only that database
BEGIN
DECLARE dbNames_cursor CURSOR
FOR
SELECT s.[name] AS dbName ,
s.database_id
FROM master.sys.databases s
LEFT JOIN master.sys.dm_hadr_availability_replica_states r ON s.replica_id = r.replica_id
WHERE s.state_desc = 'ONLINE'
AND s.is_read_only != 1
AND UPPER(ISNULL(r.role_desc, 'NonHadrEnabled')) NOT LIKE 'SECONDARY'
AND s.[name]=RTRIM(@databaseToCheck);
END
END
--
-- For each database included in the cursor,
-- gather all tables that have indexes with
-- average fragmentation percentage equal or above @fragmentationThreshold
--
OPEN dbNames_cursor;
FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
WHILE @@fetch_status = 0
BEGIN
--If verbose mode is enabled, print logs
IF @verboseMode = 1
BEGIN
PRINT ''
PRINT 'Gathering index fragmentation statistics for database: ['+ @dbname + '] with id: ' + CAST(@dbid AS VARCHAR(10));
END;
SET @dynamic_command_get_tables = N'
USE [' + @dbname+ N'];
INSERT INTO #tmpFragmentedIndexes (
[dbName],
[tableName],
[schemaName],
[indexName],
[databaseID],
[objectID],
[indexID],
[AvgFragmentationPercentage],
[reorganizationOrRebuildCommand]
)
SELECT
DB_NAME() as [dbName],
tbl.name as [tableName],
SCHEMA_NAME (tbl.schema_id) as schemaName,
idx.Name as [indexName],
pst.database_id as [databaseID],
pst.object_id as [objectID],
pst.index_id as [indexID],
pst.avg_fragmentation_in_percent as [AvgFragmentationPercentage],
CASE WHEN pst.avg_fragmentation_in_percent > 30 THEN
''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REBUILD WITH (FILLFACTOR = '+@indexFillFactor+', SORT_IN_TEMPDB = '+@sortInTempdb+', STATISTICS_NORECOMPUTE = OFF);''
WHEN pst.avg_fragmentation_in_percent > 5 AND pst.avg_fragmentation_in_percent <= 30 THEN
''ALTER INDEX [''+idx.Name+''] ON [''+DB_NAME()+''].[''+SCHEMA_NAME (tbl.schema_id)+''].[''+tbl.name+''] REORGANIZE;''
ELSE
NULL
END
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL , '''+@indexStatisticsScanningMode+''') as pst
INNER JOIN sys.tables as tbl ON pst.object_id = tbl.object_id
INNER JOIN sys.indexes idx ON pst.object_id = idx.object_id AND pst.index_id = idx.index_id
WHERE pst.index_id != 0
AND pst.alloc_unit_type_desc IN ( N''IN_ROW_DATA'', N''ROW_OVERFLOW_DATA'')
AND pst.avg_fragmentation_in_percent >= '+ @fragmentationThreshold + '';
-- if verbose mode is enabled, print logs
IF @verboseMode=1
BEGIN
PRINT 'Index fragmentation statistics script: ';
PRINT @dynamic_command_get_tables;
END
-- gather index fragmentation statistics
EXEC (@dynamic_command_get_tables);
-- bring next record from the cursor
FETCH NEXT FROM dbNames_cursor INTO @dbname, @dbid;
END;
CLOSE dbNames_cursor;
DEALLOCATE dbNames_cursor;
------------------------------------------------------------
-- if 'report only' mode is enabled
IF @reportOnly=1
BEGIN
SELECT dbName ,
tableName ,
schemaName ,
indexName ,
AvgFragmentationPercentage ,
reorganizationOrRebuildCommand
FROM #tmpFragmentedIndexes
ORDER BY AvgFragmentationPercentage DESC;
END
ELSE
-- if 'report only' mode is disabled, then execute
-- index reorganize/rebuild statements
BEGIN
DECLARE reorganizeOrRebuildCommands_cursor CURSOR
FOR
SELECT reorganizationOrRebuildCommand
FROM #tmpFragmentedIndexes
WHERE reorganizationOrRebuildCommand IS NOT NULL
ORDER BY AvgFragmentationPercentage DESC;
OPEN reorganizeOrRebuildCommands_cursor;
FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
WHILE @@fetch_status = 0
BEGIN
IF @verboseMode = 1
BEGIN
PRINT ''
PRINT 'Executing script:'
PRINT @ReorganizeOrRebuildCommand
END
EXEC (@ReorganizeOrRebuildCommand);
FETCH NEXT FROM reorganizeOrRebuildCommands_cursor INTO @ReorganizeOrRebuildCommand;
END;
CLOSE reorganizeOrRebuildCommands_cursor;
DEALLOCATE reorganizeOrRebuildCommands_cursor;
PRINT ''
PRINT 'All fragmented indexes have been reorganized/rebuilt.'
PRINT ''
END
END
ELSE
BEGIN
PRINT '';
PRINT 'Error: You need to be SysAdmin and use SQL Server 2005 or later in order to use this script.';
PRINT '';
END
--End of Script
Thursday, October 15, 2020
GP - SQL View - Commission Report based on applied payments and applied transaction dates with national customers and returns by line item
GO
/****** Object: View [dbo].[BI_ARApplyDtl] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ARApplyDtl]
AS
SELECT T.CUSTNMBR AS Customer_ID, CM.CUSTNAME AS Customer_Name, T.DOCDATE AS Document_Date, T.GLPOSTDT AS GL_Posting_Date,
CASE T .RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN 'Payment' END AS RM_Doc_Type,
T.docTypeNum AS Document_Type_and_Number, T.DOCNUMBR AS Document_Number, T.ORTRXAMT AS Original_Trx_Amount,
T.CURTRXAM AS Current_Trx_Amount, T.amountApplied AS Total_Applied_Amount, A.APPTOAMT AS Amount_Applied, A.APTODCTY AS Applied_to_Doc_Type,
A.debitType AS Applied_to_Doc_Type_Name, A.APTODCNM AS Applied_to_Doc_Number, A.APTODCDT AS Applied_to_Document_Date,
A.ApplyToGLPostDate AS Applied_to_GL_Posting_Date, A.DISTKNAM AS Discount, A.WROFAMNT AS Writeoff, A.DATE1 AS Apply_Document_Date,
A.GLPOSTDT AS Apply_GL_Posting_Date, CAST(YEAR(A.DATE1) AS varchar) + '-' + RIGHT('00' + CAST(MONTH(A.DATE1) AS varchar), 2) AS AppYrMth,
DATEDIFF(dd, A.APTODCDT, A.DATE1) AS DaystoPay, T.SLPRSNID, T.SLSTERCD
FROM (SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE
CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum,
DOCNUMBR, ORTRXAMT, CURTRXAM, ORTRXAMT - CURTRXAM AS amountApplied, SLPRSNID, SLSTERCD
FROM dbo.RM20101
WHERE (RMDTYPAL > 6) AND (VOIDSTTS = 0)
UNION
SELECT CUSTNMBR, DOCDATE, GLPOSTDT, RMDTYPAL,
CASE RMDTYPAL WHEN 7 THEN 'Credit Memo' WHEN 8 THEN 'Return' WHEN 9 THEN CASE CSHRCTYP WHEN 0 THEN 'Payment - Check ' + CASE
CHEKNMBR WHEN '' THEN '' ELSE '#' + CHEKNMBR END WHEN 1 THEN 'Payment - Cash' WHEN 2 THEN 'Payment - Credit Card' END END AS docTypeNum,
DOCNUMBR, ORTRXAMT, CURTRXAM, ORTRXAMT - CURTRXAM AS amountApplied, SLPRSNID, SLSTERCD
FROM dbo.RM30101
WHERE (RMDTYPAL > 6) AND (VOIDSTTS = 0)) AS T INNER JOIN
dbo.RM00101 AS CM ON T.CUSTNMBR = CM.CUSTNMBR INNER JOIN
(SELECT tO1.CUSTNMBR, tO2.APTODCTY, tO2.APTODCNM, tO2.APFRDCTY, tO2.APFRDCNM,
CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END AS debitType, tO2.APPTOAMT, tO2.ApplyToGLPostDate, tO2.APTODCDT,
tO2.DISTKNAM, tO2.WROFAMNT, tO2.DATE1, tO2.GLPOSTDT, tO1.SLPRSNID, tO1.SLSTERCD
FROM dbo.RM20201 AS tO2 INNER JOIN
dbo.RM20101 AS tO1 ON tO2.APTODCTY = tO1.RMDTYPAL AND tO2.APTODCNM = tO1.DOCNUMBR
UNION
SELECT tH1.CUSTNMBR, tH2.APTODCTY, tH2.APTODCNM, tH2.APFRDCTY, tH2.APFRDCNM,
CASE APTODCTY WHEN 1 THEN 'Sale / Invoice' WHEN 2 THEN 'Scheduled Payment' WHEN 3 THEN 'Debit Memo' WHEN 4 THEN 'Finance Charge'
WHEN 5 THEN 'Service Repair' WHEN 6 THEN 'Warranty' END AS debitType, tH2.APPTOAMT, tH2.ApplyToGLPostDate, tH2.APTODCDT,
tH2.DISTKNAM, tH2.WROFAMNT, tH2.DATE1, tH2.GLPOSTDT, tH1.SLPRSNID, tH1.SLSTERCD
FROM dbo.RM30201 AS tH2 INNER JOIN
dbo.RM30101 AS tH1 ON tH2.APTODCTY = tH1.RMDTYPAL AND tH2.APTODCNM = tH1.DOCNUMBR) AS A ON A.APFRDCTY = T.RMDTYPAL AND
A.APFRDCNM = T.DOCNUMBR
GO
/****** Object: View [dbo].[BI_Cust_First_Sale] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Cust_First_Sale]
AS
SELECT CUSTNMBR, MIN(DOCDATE) AS FirstSalesDate
FROM dbo.SOP30200
GROUP BY CUSTNMBR
GO
/****** Object: View [dbo].[BI_Sales_History] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Sales_History]
AS
SELECT TOP (100) PERCENT dbo.SOP30200.SOPNUMBE AS [SOP Number], dbo.SOP30200.DOCDATE AS [Document Date],
dbo.SOP30200.GLPOSTDT AS [GL Post Date], CASE WHEN sop30200.soptype = 4 THEN (sop30300.XTNDPRCE - SOP30300.TRDISAMT)
* - 1 ELSE (sop30300.xtndprce - SOP30300.TRDISAMT) END AS Sales,
CASE WHEN sop30200.soptype = 4 THEN sop30300.EXTDCOST * - 1 ELSE sop30300.extdcost END AS Costs,
CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END AS Qty, dbo.SOP30200.CUSTNMBR AS [Customer Number],
CASE WHEN sop30200.soptype = 1 THEN 'Quote' WHEN sop30200.soptype = 2 THEN 'Order' WHEN sop30200.soptype = 3 THEN 'Invoice' WHEN sop30200.soptype
= 4 THEN 'Return' WHEN sop30200.soptype = 5 THEN 'BackOrder' WHEN sop30200.soptype = 6 THEN 'FulfilmentOrder' END AS [SOP Type],
dbo.IV40400.ITMCLSDC AS [Item Class Description], dbo.IV40400.ITMCLSCD AS [Item Class Code], dbo.RM00101.CUSTNAME AS [Customer Name],
dbo.RM00101.CUSTCLAS AS [Customer Class], dbo.SOP30300.QTYBSUOM AS [Base Unit Of Measure],
(CASE WHEN sop30200.soptype = 4 THEN sop30300.quantity * - 1 ELSE sop30300.quantity END) * dbo.SOP30300.QTYBSUOM AS [Units Sold],
dbo.SOP30300.STATE, dbo.SOP30300.PRSTADCD, dbo.SOP30200.SLPRSNID AS [Salesperson ID], dbo.IV00101.USCATVLS_1, dbo.IV00101.USCATVLS_2,
dbo.IV00101.USCATVLS_3, dbo.SOP30300.ITEMNMBR AS [Item Number], dbo.SOP30300.ITEMDESC AS [Item Description], dbo.SOP30200.ShipToName,
LEFT(dbo.SOP30300.ITEMNMBR, 2) AS Category, dbo.SOP30300.UNITCOST, dbo.RM00101.CREATDDT, dbo.BI_Cust_First_Sale.FirstSalesDate,
LEFT(dbo.SOP30300.ITEMNMBR, 2) AS AB, dbo.SOP30300.UNITPRCE,
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.ZIP ELSE rm00301.zip END AS [Sales Person Department],
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.SLPRSNFN ELSE rm00301.SLPRSNFN END AS [Sales Person First Name],
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.SPRSNSLN ELSE rm00301.SPRSNSLN END AS [Sales Person Last Name],
dbo.SOP30200.LOCNCODE, dbo.SOP30300.SLPRSNID AS SOPLineSR,
CASE WHEN rm00301_1.slprsnid > '' THEN rm00301_1.slprsnid ELSE rm00301.slprsnid END AS FinalSR, dbo.SOP30300.LNITMSEQ,
dbo.SOP30300.DEX_ROW_ID, AlertCRM.dbo.InvoiceDetail.EmployeeCode
FROM dbo.RM00101 INNER JOIN
dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE ON
dbo.RM00101.CUSTNMBR = dbo.SOP30200.CUSTNMBR INNER JOIN
AlertCRM.dbo.InvoiceDetail ON dbo.SOP30300.SOPNUMBE = AlertCRM.dbo.InvoiceDetail.InvoiceNumber AND
dbo.SOP30300.LNITMSEQ = AlertCRM.dbo.InvoiceDetail.InvoiceDetailRecord LEFT OUTER JOIN
dbo.RM00301 AS RM00301_1 ON AlertCRM.dbo.InvoiceDetail.EmployeeCode = RM00301_1.SLPRSNID LEFT OUTER JOIN
dbo.RM00301 ON dbo.SOP30200.SLPRSNID = dbo.RM00301.SLPRSNID LEFT OUTER JOIN
dbo.IV40400 INNER JOIN
dbo.IV00101 ON dbo.IV40400.ITMCLSCD = dbo.IV00101.ITMCLSCD ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR RIGHT OUTER JOIN
dbo.BI_Cust_First_Sale ON dbo.RM00101.CUSTNMBR = dbo.BI_Cust_First_Sale.CUSTNMBR
WHERE (dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE IN (3, 4)) AND (dbo.SOP30300.CMPNTSEQ = 0)
GO
/****** Object: View [dbo].[BI_Sales_History_ReturnItems] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_Sales_History_ReturnItems]
AS
SELECT dbo.BI_ARApplyDtl.Applied_to_Doc_Number AS AppliedtoInv, dbo.BI_ARApplyDtl.RM_Doc_Type, dbo.BI_Sales_History.[SOP Number] AS RtnNo,
dbo.BI_Sales_History.[Item Number], dbo.BI_Sales_History.Sales AS RtnAmount, dbo.BI_Sales_History.Qty AS RtnQty
FROM dbo.BI_ARApplyDtl LEFT OUTER JOIN
dbo.BI_Sales_History ON dbo.BI_ARApplyDtl.Document_Number = dbo.BI_Sales_History.[SOP Number]
GO
/****** Object: View [dbo].[BI_AR_LastDateApplied] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_AR_LastDateApplied]
AS
SELECT Applied_to_Doc_Number, MAX(Document_Date) AS LastDocDate, MAX(Apply_Document_Date) AS LastDateApplied
FROM dbo.BI_ARApplyDtl
GROUP BY Applied_to_Doc_Number
GO
/****** Object: View [dbo].[BI_CusMaster] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_CusMaster]
AS
SELECT Cus.CUSTNMBR, Cus.CUSTNAME, Cus.CPRCSTNM, Cus.CUSTCLAS, CASE WHEN Cus.Div IS NULL
THEN '(NONE)' WHEN Cus.Div = '' THEN '(NONE)' ELSE Cus.Div END AS Div, CASE WHEN rm00101.SLPRSNID IS NULL
THEN '(NONE)' WHEN rm00101.SLPRSNID = '' THEN '(NONE)' ELSE rtrim(rm00101.SLPRSNID) END AS SLPRSNID, CASE WHEN rm00101.SALSTERR IS NULL
THEN '(NONE)' WHEN rm00101.SALSTERR = '' THEN '(NONE)' ELSE rtrim(rm00101.SALSTERR) END AS SALSTERR, Cus.RlNatCus,
dbo.RM00101.CUSTNAME AS RlNatCusNm, RTRIM(Cus.CUSTNMBR) + ' | ' + RTRIM(Cus.CUSTNAME) AS CusLbl, dbo.RM00201.CLASDSCR,
LEFT(dbo.RM00201.CLASDSCR, 3) AS CusClsDiv, CASE WHEN rm00101.CURNCYID NOT IN ('TTD', '') THEN 'Export' ELSE 'Local' END AS LocExpCs,
LEFT(dbo.RM00201.CLASDSCR, 3) + '-' + Cus.Div AS CusDiv
FROM (SELECT RM00101_1.CUSTNMBR, RM00101_1.CUSTNAME, RM00101_1.CPRCSTNM, RM00101_1.CUSTCLAS, CASE LEFT(CUSTNMBR, 1)
WHEN '2' THEN LEFT(Custclas, 4)
WHEN '4' THEN 'MP' WHEN '6' THEN 'MABELS' WHEN '8' THEN 'FROZEN' WHEN 'E' THEN CLASDSCR ELSE 'PARENT CUSTOMER' END AS Div,
RM00101_1.SLPRSNID, RM00101_1.SALSTERR, CASE WHEN CPRCSTNM = '' THEN custnmbr ELSE cprcstnm END AS RlNatCus
FROM dbo.RM00101 AS RM00101_1 LEFT OUTER JOIN
dbo.RM00201 AS RM00201_1 ON RM00101_1.CUSTCLAS = RM00201_1.CLASSID) AS Cus LEFT OUTER JOIN
dbo.RM00201 ON Cus.CUSTCLAS = dbo.RM00201.CLASSID LEFT OUTER JOIN
dbo.RM00101 ON Cus.CUSTNMBR = dbo.RM00101.CUSTNMBR
GO
/****** Object: View [dbo].[BI_ARActivity] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_ARActivity]
AS
SELECT DATEDIFF(d, AllOS.DOCDATE, GETDATE()) AS DaysOld, AllOS.CUSTNMBR, AllOS.CPRCSTNM, AllOS.DOCNUMBR, AllOS.DOCDATE, AllOS.DUEDATE,
AllOS.ORTRXAMT, AllOS.CURTRXAM, CASE WHEN rmdtypal >= 7 THEN curtrxam * - 1 ELSE curtrxam END AS CurrOsAmt, AllOS.RMDTYPAL,
CASE WHEN rmdtypal >= 7 THEN ortrxamt ELSE 0 END AS Credits, CASE WHEN rmdtypal >= 7 THEN 0 ELSE ortrxamt END AS Debits,
CAST(YEAR(AllOS.DOCDATE) AS varchar) + '-' + RIGHT('00' + CAST(MONTH(AllOS.DOCDATE) AS varchar), 2) AS YrMth,
dbo.BI_AR_LastDateApplied.LastDateApplied, AllOS.SLPRSNID, AllOS.SLSTERCD, dbo.BI_AR_LastDateApplied.LastDocDate,
dbo.BI_CusMaster.CUSTNAME
FROM (SELECT CUSTNMBR, CPRCSTNM, DOCNUMBR, DOCDATE, DUEDATE, ORTRXAMT, CURTRXAM, RMDTYPAL, SLPRSNID, SLSTERCD
FROM dbo.RM20101
UNION
SELECT CUSTNMBR, CPRCSTNM, DOCNUMBR, DOCDATE, DUEDATE, ORTRXAMT, CURTRXAM, RMDTYPAL, SLPRSNID, SLSTERCD
FROM dbo.RM30101) AS AllOS LEFT OUTER JOIN
dbo.BI_CusMaster ON AllOS.CUSTNMBR = dbo.BI_CusMaster.CUSTNMBR LEFT OUTER JOIN
dbo.BI_AR_LastDateApplied ON AllOS.DOCNUMBR = dbo.BI_AR_LastDateApplied.Applied_to_Doc_Number
GO
/****** Object: View [dbo].[BI_FullyPaidSalesDetails] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_FullyPaidSalesDetails]
AS
SELECT dbo.BI_ARActivity.DaysOld, dbo.BI_ARActivity.CUSTNMBR, dbo.BI_ARActivity.CPRCSTNM, dbo.BI_ARActivity.DOCNUMBR, dbo.BI_ARActivity.DOCDATE,
dbo.BI_ARActivity.DUEDATE, dbo.BI_ARActivity.ORTRXAMT, dbo.BI_ARActivity.CURTRXAM, dbo.BI_ARActivity.CurrOsAmt, dbo.BI_ARActivity.RMDTYPAL,
dbo.BI_ARActivity.Credits, dbo.BI_ARActivity.Debits, dbo.BI_ARActivity.YrMth, dbo.BI_ARActivity.LastDateApplied, RTRIM(dbo.BI_ARActivity.SLPRSNID)
AS SLPRSNID, dbo.BI_ARActivity.SLSTERCD, dbo.BI_ARActivity.LastDocDate, dbo.BI_ARActivity.CUSTNAME, dbo.BI_Sales_History.[Item Number],
dbo.BI_Sales_History.[Item Description], dbo.BI_Sales_History.UNITPRCE, dbo.BI_Sales_History.[Salesperson ID],
dbo.BI_Sales_History.[Sales Person First Name], dbo.BI_Sales_History.[Sales Person Last Name], dbo.BI_Sales_History.[Sales Person Department],
dbo.BI_Sales_History.[Units Sold], dbo.BI_Sales_History.Qty, dbo.BI_Sales_History.Sales, dbo.BI_Sales_History.FinalSR,
ISNULL(dbo.BI_Sales_History_ReturnItems.RtnAmount, 0) AS RtnAmount, ISNULL(dbo.BI_Sales_History_ReturnItems.RtnQty, 0) AS RtnQty,
ISNULL(dbo.BI_Sales_History_ReturnItems.RtnAmount, 0) + dbo.BI_Sales_History.Sales AS FSales, ISNULL(dbo.BI_Sales_History_ReturnItems.RtnQty, 0)
+ dbo.BI_Sales_History.Qty AS FQty
FROM dbo.BI_ARActivity INNER JOIN
dbo.BI_Sales_History ON dbo.BI_ARActivity.DOCNUMBR = dbo.BI_Sales_History.[SOP Number] LEFT OUTER JOIN
dbo.BI_Sales_History_ReturnItems ON dbo.BI_Sales_History.[SOP Number] = dbo.BI_Sales_History_ReturnItems.AppliedtoInv AND
dbo.BI_Sales_History.[Item Number] = dbo.BI_Sales_History_ReturnItems.[Item Number]
GO
/****** Object: View [dbo].[BI_SRMaster] Script Date: 10/23/2020 1:54:12 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SRMaster]
AS
SELECT RTRIM(dbo.RM00301.SLPRSNID) AS SLPRSNID, CASE WHEN rm00301.SALSTERR = '' THEN '(NONE)' ELSE rtrim(rm00301.SALSTERR) END AS SALSTERR,
ISNULL(dbo.RM00303.SLTERDSC, '(NONE)') AS SLTERDSC, dbo.RM00301.EMPLOYID, dbo.RM00301.SLPRSNFN, dbo.RM00301.SPRSNSLN,
RTRIM(dbo.RM00301.SLPRSNFN) + ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrNm, RTRIM(dbo.RM00301.SLPRSNID)
+ ' | ' + RTRIM(CASE WHEN rm00301.SALSTERR = '' THEN '(NONE)' ELSE rm00301.SALSTERR END) + ' | ' + RTRIM(dbo.RM00301.SLPRSNFN)
+ ' ' + RTRIM(dbo.RM00301.SPRSNSLN) AS SrTerrLbl, dbo.RM00301.STATE, dbo.RM00301.INACTIVE AS SRInactive, ISNULL(dbo.RM00303.INACTIVE, 0)
AS TerrInactive, dbo.RM00301.SPRSNSMN
FROM dbo.RM00301 LEFT OUTER JOIN
dbo.RM00303 ON dbo.RM00301.SALSTERR = dbo.RM00303.SALSTERR
GO
Friday, October 9, 2020
Nav/BC - How can i tell if an item has been successfully cost adjusted?
Check the Cost Is Adjusted field. 0 = No, 1 = Yes.
select No_,[Cost is Adjusted] from [Cronus$Item] where [Cost is Adjusted] = 0
How can i tell how many adjustments need to be done?
The highest count ones should be done first
SELECT CRONUS$Item.No_, CRONUS$Item.[Cost is Adjusted], COUNT([CRONUS$Avg_ Cost Adjmt_ Entry Point].[Cost Is Adjusted]) AS EPCount
FROM CRONUS$Item INNER JOIN
[CRONUS$Avg_ Cost Adjmt_ Entry Point] ON CRONUS$Item.No_ = [CRONUS$Avg_ Cost Adjmt_ Entry Point].[Item No_]
WHERE (CRONUS$Item.[Cost is Adjusted] = 0) AND ([CRONUS$Avg_ Cost Adjmt_ Entry Point].[Cost Is Adjusted] = 0)
GROUP BY CRONUS$Item.No_, CRONUS$Item.[Cost is Adjusted]
ORDER BY EPCount desc
Wednesday, October 7, 2020
NAV/BC - Error when running Adjust Costs - ExeID SE:0000000065;DT:GLREG=45;DI:C12;T81;OnCode Not Found
ExeID SE:0000001842;DT:GLREG=45;DI:C12;T81;OnCode Not Found
This error randomly happens when running adjust costs manually and by automated job.
If you keep trying with smaller item ranges, eventually the adjust costs goes through.
We have found no resolution for this other than manually running adjust costs for small ranges at a time.
Run the adjust costs job, Clear as many items as possible.
Schedule it to run weekly after closing hours.
Re-Run the adjust costs job.
We have found missing Item Application entry records can cause this.
Confirm by checking is all Item Ledger entry numbers have a corresponding entry in Item Application Entries in the Item Ledger entry no column.
Any missing records will need to be manually inserted.
NAV - User cannot save ribbon or screen customizations on full client
To resolve, create a permission set that gives explicit access to the 2000000075 User Metadata table
Assign this to all users.
In some cases, even SUPER does not give appropriate permissions.
Tuesday, October 6, 2020
LS NAV/ BC - How to stop a job that is currently running
Open the development environment
Debug the session
Stop the job
NAV/BC - View Locked Tables
Useful for identifying if a specific routine is locking up tables
select distinct TableName from(
SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName,
resource_type, resource_description
FROM sys.dm_tran_locks as l
JOIN sys.partitions as p ON l.resource_associated_entity_id = p.hobt_id) as locks
Columbus - ADC Error when picking loose stock - Qty to handle cannot exceed outstanding quantity
- This error occurs on the ADC whenever there are multiple units of measure for the same item in the same bin.
- For now, the easiest workaround to fulfil eaches for a sales order is to move a single case into a separate bin, then break the entire quantity so it only has eaches in that bin, and not a mixture of eaches and cases. The ADC should work correctly as long as there are no mixed UofM’s within the same bin.
Thursday, October 1, 2020
Zoom Whiteboard and annotations do not work on Chromebook
Zoom whiteboard and annotations do not work on Chromebook when using the regular Zoom, or the Zoom web client.
You must install and use the Zoom Cloud Meetings app or the Zoom Rooms app (not sure what it's called now..there's a bunch of them on the chrome store) to get the whiteboard functionality to work.
However, the interface is not as nice or as responsive, and there are lag and freezing issues.
But the whiteboard works.
I hate you zoom. Fix your garbage.
Seesaw Classes App on Chromebook - Voice Recording - Feedback problem
This is a problem specific to the Seesaw app in the Google store for the Chromebook.
When recording anything, it immediately plays the output through the speakers and causes feedback.
Solution:
Delete the Seesaw app, it's a piece of garbage. Use the Seesaw website and scan the login code instead.
Bookmark it once you get into your class.
Recording works just fine.
Seesaw, fix your chromebook app. No one can use it for their kids in it's current state.
NAV / BC - Hide Purchase Order Header based on Page Count - Only show header on first page
- There is no way to get the page numbers from SSRS
- We have to put a line counter in the Purchase Order Report Code to calculate the number of lines per page
- then we use SSRS functions on the lines to hide based on the value of the Line Count
- We reset the line count for every PO
- Create Pagecount Global variable
- Added to Data source to make it available to the report
- Added Pagecount := 0 in Copyloop Onpredataitem after Purchase Header
- Added Pagecount := Pagecount + 1 in Roundloop Onaftergetrecord after Purchase Line
- Our count ended up being max 18 records on the first page
- Added visibility function on SSRS row
- IIF(Pagecount <10,false,true)
Friday, September 25, 2020
NAV/BC - Columbus ToIncrease - ADC Bin to Bin - Error when trying to place all remaining
- ADC prompts you to place all remaining?
- However, this only applies if you were transferring a container.
- If you are transferring loose stock, you have to say no. If you say yes, you get an error
- Choose no
- Scan item again
- Enter qty
BC16 - An encryption key must be imported using Import-NAVEncryptionKey before using Sql Authentication to access the database
C:\ProgramData\Microsoft\Microsoft
Dynamics NAV\160\Server\Keys was not
being deleted.
On deleting the key and re-entering the database
credentials for the BC instance a new key was generated automatically and the
BC instance ran without issue.
Wednesday, September 23, 2020
NAV/BC - How to remove lot tracking from an item
You can remove the item tracking code and lot no assignment method from items after doing the following
- Ensure stock is 0
- ensure there are no outstanding transactions with the item on it
- Run calculate warehouse movements>Post
- Run adjust costs
update [CRONUS$Item] set [Item Tracking Code] = '', [Lot No_ Assignment Method] = '' where No_ = '101261'
Tuesday, September 22, 2020
NAV/BC - Columbus Toincrease - How to do a warehouse stock count
- Cycle Count
- Warehouse Physical Inventory Journal>Calculate Inventory>Select Item
- Actions>Print
- Employees execute physical count, enter on sheet or excel
- Enter count against each of the lines or paste from excel
- Register
- Full Count Manual
- Warehouse Physical Inventory Journal>Calculate Inventory>Set count to zero
- Register
- Copy and paste a single record to excel to create a template
- Employees execute physical count, enter on excel
- Enter count against each of the lines or paste from excel
- Register
- Full Count Custom ADC Mobile Scan
- Warehouse Physical Inventory Journal>Calculate Inventory>Set count to zero
- Register
- Employees execute physical count on ADC Whse. Phys. Inv.
- Register
- Full Count Custom Web Client Mobile Scan
- Add custom barcode field to Warehouse Physical Inventory Journal to allow for scanning and line population
- Add code to autocreate container if it does not exist
- Warehouse Physical Inventory Journal>Calculate Inventory>Set count to zero
- Register
- Copy and paste a single record to excel to create a template
- Employees execute physical count, scan item, bin, lot, container
- Register
LS Retail - LS Appshell Mobile POS
- LS Central 11.2
- Appshell does not work with Android device and Bluetooth paired printer - App is unstable
- Appshell works correctly with Windows devices and hardware station - Windows appshell cannot detect bluetooth printer
- LS Central 16
- Appshell works correctly with Android device and Bluetooth paired printer
- Appshell works correctly with Windows devices and hardware station - Windows appshell cannot detect bluetooth printer
Monday, September 21, 2020
Nav/BC - Columbus ToIncrease - Quantity(Base) Available must not be less than x in Bin Content Location Code y
Delete or complete all Warehouse Picks
Delete or complete all Warehouse Shipments
Delete or complete all Warehouse Movements
NAV/BC - Columbus To-Increase - Insufficient quantity to complete this action. The base quantity in the bin is x. 0.00001 units are not available for Bin Code y
This can happen if large decimals are used on UofM's by mistake, causing inadvertent rounding to 5 dp in stock quantities.
- Identify the exact item, uofm and lot that is giving the problem
- In SQL, update the appropriate Qty_Base value to ensure sufficient stock is available on the line
update [CRONUS$Warehouse Entry]
set [Qty_ (Base)] = [Qty_ (Base)]+0.00001
where [Item No_] = '100001' and [Unit of Measure Code] = 'LB' and [Entry No_]=9317
Wednesday, September 16, 2020
LS Central - SQL View - Get Latest 12-13 Digit Barcode or other barcode if no 12-13 digit barcode can be found
SELECT ItemNo, [1213BC], [1213BCDate], OtherBC, OtherBCDate,Case when ([1213BC] > '') then [1213BC] else OtherBC end as FinalBarcode,Case when ([1213BC] > '') then [1213BCDate] else OtherBCDate end as FinalBarcodeDate
FROM (SELECT ItemNo, MAX([1213BC]) AS [1213BC], MAX([1213BCDate]) AS [1213BCDate], MAX(OtherBC) AS OtherBC, MAX(OtherBCDate) AS OtherBCDate
FROM (SELECT CASE WHEN BCLen = '1213' THEN LastDateModified ELSE '' END AS [1213BCDate], CASE WHEN BCLen = 'Other' THEN LastDateModified ELSE '' END AS OtherBCDate, ItemNo,
CASE WHEN BCLen = '1213' THEN MAX([Barcode No_]) ELSE '' END AS [1213BC], CASE WHEN BCLen = 'Other' THEN MAX([Barcode No_]) ELSE '' END AS OtherBC
FROM (SELECT LB.LastDateModified, LB.ItemNo, LB.BCLen, LB.BCCount, BC.[Barcode No_]
FROM (SELECT MAX([Last Date Modified]) AS LastDateModified, [Item No_] AS ItemNo, CASE WHEN LEN([Barcode No_]) BETWEEN 12 AND 13 THEN '1213' ELSE 'Other' END AS BCLen, COUNT([Barcode No_])
AS BCCount
FROM [Master Config$Barcodes]
GROUP BY LEN([Barcode No_]), [Item No_]) AS LB INNER JOIN
(SELECT [Item No_], [Barcode No_], [Last Date Modified]
FROM [Master Config$Barcodes] AS [Master Config$Barcodes_1]) AS BC ON LB.LastDateModified = BC.[Last Date Modified] AND LB.ItemNo = BC.[Item No_]) AS LBC
GROUP BY LastDateModified, ItemNo, BCLen, BCCount) AS IBC
GROUP BY ItemNo) AS FLBC
Tuesday, September 15, 2020
How to get whiteboards to work on Zoom when using a Chromebook
- How to connect to zoom
- Using the web client at https://zoom.us/signin
- Download Zoom Rooms from the Google Play Store
- Download Zoom Cloud Meetings from the Google Play Store
- By default, using the regular web client, or Zoom Rooms client, whiteboard and annotation features are not available
- However, you can use the Zoom Cloud Meetings app to get the whiteboard and annotation functionality
- When entering the room passcode, the chrome password manager hijacks the screen, and you have to select a password, clear it, then enter the proper room code
Friday, September 11, 2020
NAV / BC - How to modify R7390 Calculate Warehouse Inventory for Warehouse Physical Journal to allow item card filters
The first table in the default report is Bin Content.
Add item level above bin content
- Item (Item2)
- Bin Content (Bin Content)
Linked on item number for each level
This allow you to use the item filters on the report.
Enable network drives in SQL Management Studio
https://vandijk.cloud/restore-sql-backup-from-a-network-share/
If the specific share has authentication enabled you might have the change the last command to:
Tuesday, September 8, 2020
NAV/BC Columbus Foods - Must not have less than 0 in Bin
This error occurs when a warehouse movement has been halfway done on ADC.
A Take without a place.
Go to Bin Status
Find the item
Check all of the outbound transactions holding stock
Delete any half-done movements
Monday, September 7, 2020
NAV/BC Columbus Foods - Breakbulk does not work with Containers, ADC and Warehouse Picking
Automatic breakbulk does not work with Columbus Foods ADC Picking
You must manually break the stock to make it available in smaller UofM's for picking before you pick
Saturday, September 5, 2020
Bluetooth devices do not connect to laptop. Forever detecting.
Task Manager>Services
Set all bluetooth services to automatic
Restart machine
Friday, September 4, 2020
Camera does not work, or is not detected by Teams, Zoom, etc.
https://support.microsoft.com/en-us/help/13753/windows-10-camera-does-not-work#:~:text=Camera%20doesn't%20work%20in%20Windows%2010&text=When%20your%20camera%20isn't,app%20you%20want%20to%20use.
Run camera app
If you have disabled gps tracking on your camera, the entire camera is disabled
There are a number of issues with the latest versions of windows and camera drivers.
Update to the latest drivers if they exist
OR
Go to system restore, and restore your machine back to an earlier version before the latest update when the camera was working
I was never able to get my dell xps camera to function consistently. Sometimes it would detect, sometimes it would not. Ended up getting a chromebook instead. Haven't had any issues since.
NAV / BC - Change Exchange rates per transaction
Click on the three dots next to the currency to change the rate for that specific transaction
LS Central - Barcode Import Error - Does not exist in item
You get this error when trying to config package import barcodes to the barcode table
There is a bug in the barcode table meant to handle barcode entry via the page which causes the import to fail.
Disabling the validation in the package has no effect.
Solution: Import to table directly using SQL, or use the item import tool for just the barcode data
Tuesday, September 1, 2020
LS Central - Device "VIRTUAL_P1" of type "Printer" has not been loaded into POS.
- Run Virtual Printer (on desktop) as administrator
- C:\Program Files (x86)\LS Retail\LSHardwareStation\LSVirtualstation.exe
- Open Web client > run pos > Login
- Go to services.msc and restart the LS Hardware Station
- Proceed to cash
To automate the restart, Create a batch file to restart the LS Hardware service
net stop "LS Hardware Station"
If this does not work, or you get the error "Could not accept control messages" or "could not be controlled"
Manually run
Sc queryex
Get the PID, eg. 6123
Then run
taskkill /PID 6123 /F
Subscribe to:
Posts (Atom)