- 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');
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 0
ORDER BY avg_fragmentation_in_percent DESC;
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.
---------------------------------------------------------------------------
Disabling Automatic Cost posting, and setting cost updates to Never allowed us to post out stuck inventory transactions.
After this completed, we were able to run the "Post Inventory Costs" and then the "Adjust Item Costs" afterwards.
then we re-enabled the automatic cost posting.
----------------------------------------------------------------------------
5-Jun-24 EXEID error came back when posting statements only. Cost posting is still off.
Copied company
Removed posting date limits
Posted all costs, one year at a time to avoid exeid errors
Attempted to post adjust cost – item entries, got exeid error
In application worksheet, unapplied and reapplied last sales, then was able to post adjust cost for that item
If you try to run adjust costs too quickly, it throws an exeid error. Give it 5 mins after running the application worksheet fixes. Close the session, reopen it. Adjust costs will complete now.
Error stopped at item I00000000421
LSC Session Data Exchange Util>FindCreateEXEIDIndex>EXEIDIndexBuffer>LSC ExecutionID Buffer
I0000000044
Trying to create actions and preactions
Trying to process changes, putting them into a buffer if the batchcreation date is too close to the current datetime. Basically, if the system processes too many transactions too quickly, it will fill the buffer, and cause it to stop. It essentially needs a larger buffer, or it needs to run slower.
This is causing the buffer to not empty out if it runs too fast.
Launching the debugger seems to clear the exeid temp table, and allows the adjust cost to complete for small runs until it fills up the temp table and sticks again.
Opening a second screen and running for the exact same item range also seems to clear the exeid table, and allow it to complete up to small ranges. Once the error appears, the temp table is held in that session. You must switch sessions to clear the table held in limbo from the error.
WORKAROUND: Open a new window, run adjust cost for 100 items. Do not reuse this window. Open a new window, run adjust cost for another 100 items. Repeat until all items have been updated.
The error occurs because the temp table doesn't clear after the error is thrown. Once the table is full, it can't run again to clear itself.
Subscribe to:
Posts (Atom)