Alpackages>Microsoft Application
You can view the source code for all objects from here
Tuesday, October 27, 2020
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)
Subscribe to:
Posts (Atom)