Thanks for the reply John. The person before me implemented a set of reports using the method of stored procedure and shared data sets using those procedures. I’m trying to migrate the report set to a new environment. Below is the stored procedure created by running the script left by the previous person. When I compare this to the other environments it looks identical except for the environment I changed to point to the newly created one.
USE [EpicorLive10_2Reports]
GO
/****** Object: StoredProcedure [dbo].[MtlTags] Script Date: 3/1/2019 10:56:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MtlTags] @TableID CHARACTER(33)
AS
BEGIN
--DECLARE @TableID AS CHARACTER(33) = 'F79357CE951C41CC9EBD7FD698F4FF22'
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
BEGIN
DROP TABLE #Results;
END
CREATE TABLE #Results(
ItemQty INT,
PartNum NVARCHAR(MAX),
PartDesc NVARCHAR(MAX),
LotNum NVARCHAR(MAX),
XPartNum NVARCHAR(MAX),
OurSupplierCode NVARCHAR(MAX),
BoxPalletSerial NVARCHAR(MAX),
MfgDate DATETIME,
XRevisionNum NVARCHAR(MAX),
UM NVARCHAR(MAX),
PONum NVARCHAR(MAX),
POLine NVARCHAR(MAX),
WhseCode NVARCHAR(MAX),
AsmSeq NVARCHAR(MAX),
TagNum NVARCHAR(MAX),
BCAsmSeq NVARCHAR(MAX),
BCBinNum NVARCHAR(MAX),
BCJobNum NVARCHAR(MAX),
BCLotNum NVARCHAR(MAX),
BCOprSeq NVARCHAR(MAX),
BCPartNum NVARCHAR(MAX),
BCWhseCode NVARCHAR(MAX),
BinNum NVARCHAR(MAX),
JobNum NVARCHAR(MAX),
LaborNote NVARCHAR(MAX),
NCComment NVARCHAR(MAX),
NonConfTranID NVARCHAR(MAX),
OpCode NVARCHAR(MAX),
OprSeq NVARCHAR(MAX),
PORel NVARCHAR(MAX),
Reason NVARCHAR(MAX),
ReasonDesc NVARCHAR(MAX),
ResGrpID NVARCHAR(MAX),
TagFormat NVARCHAR(MAX),
TagTitle NVARCHAR(MAX),
VendID NVARCHAR(MAX),
VendName NVARCHAR(MAX),
QtyNum NVARCHAR(MAX),
RptLanguageID NVARCHAR(MAX),
Company NVARCHAR(MAX),
CustNum NVARCHAR(MAX),
GrossWeight DECIMAL(15, 5),
GrossWeightUOM NVARCHAR(MAX),
PartType NVARCHAR(MAX),
LongProductID NVARCHAR(MAX),
CustID NVARCHAR(MAX),
ShipToNum NVARCHAR(MAX)
);
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'MtlTags_' + @TableID)
)
BEGIN
DECLARE @SQL AS VARCHAR(MAX);
SET @SQL =
N'SELECT DISTINCT
CAST(ISNULL(MtlTags.ItemQty, 0) AS INT) AS ItemQty,
CAST(MtlTags.PartNum AS NVARCHAR(MAX)) AS PartNum,
CAST(MtlTags.PartDesc AS NVARCHAR(MAX)) AS PartDesc,
CAST(MtlTags.LotNum AS NVARCHAR(MAX)) AS LotNum,
CAST(ISNULL(CustXPrt.XPartNum, MtlTags.PartNum) AS NVARCHAR(MAX)) AS XPartNum,
CAST(ISNULL(Customer.OurSupplierCode, '''') AS NVARCHAR(MAX)) AS OurSupplierCode,
CAST(BoxData.Key1 AS NVARCHAR(MAX)) AS BoxPalletSerial,
CAST(ISNULL(BoxData.Date01, '''') AS DATE) AS MfgDate,
CAST(ISNULL(CustXPrt.XRevisionNum, '''') AS NVARCHAR(MAX)) AS XRevisionNum,
CAST(MtlTags.UM AS NVARCHAR(MAX)) AS UM,
CAST(MtlTags.PONum AS NVARCHAR(MAX)) AS PONum,
CAST(MtlTags.POLine AS NVARCHAR(MAX)) AS POLine,
CAST(MtlTags.WhseCode AS NVARCHAR(MAX)) AS WhseCode,
CAST(MtlTags.AsmSeq AS NVARCHAR(MAX)) AS AsmSeq,
CAST(MtlTags.TagNum AS NVARCHAR(MAX)) AS TagNum,
CAST(MtlTags.BCAsmSeq AS NVARCHAR(MAX)) AS BCAsmSeq,
CAST(MtlTags.BCBinNum AS NVARCHAR(MAX)) AS BCBinNum,
CAST(MtlTags.BCJobNum AS NVARCHAR(MAX)) AS BCJobNum,
CAST(MtlTags.BCLotNum AS NVARCHAR(MAX)) AS BCLotNum,
CAST(MtlTags.BCOprSeq AS NVARCHAR(MAX)) AS BCOprSeq,
CAST(MtlTags.BCPartNum AS NVARCHAR(MAX)) AS BCPartNum,
CAST(MtlTags.BCWhseCode AS NVARCHAR(MAX)) AS BCWhseCode,
CAST(MtlTags.BinNum AS NVARCHAR(MAX)) AS BinNum,
CAST(MtlTags.JobNum AS NVARCHAR(MAX)) AS JobNum,
CAST(MtlTags.LaborNote AS NVARCHAR(MAX)) AS LaborNote,
CAST(MtlTags.NCComment AS NVARCHAR(MAX)) AS NCComment,
CAST(MtlTags.NonConfTranID AS NVARCHAR(MAX)) AS NonConfTranD,
CAST(MtlTags.OpCode AS NVARCHAR(MAX)) AS OpCode,
CAST(MtlTags.OprSeq AS NVARCHAR(MAX)) AS OprSeq,
CAST(MtlTags.PORel AS NVARCHAR(MAX)) AS PORel,
CAST(MtlTags.Reason AS NVARCHAR(MAX)) AS Reason,
CAST(MtlTags.ReasonDesc AS NVARCHAR(MAX)) AS ReasonDesc,
CAST(MtlTags.ResGrpID AS NVARCHAR(MAX)) AS ResGrpID,
CAST(MtlTags.TagFormat AS NVARCHAR(MAX)) AS TagFormat,
CAST(MtlTags.TagTitle AS NVARCHAR(MAX)) AS TagTitle,
CAST(MtlTags.VendID AS NVARCHAR(MAX)) AS VendID,
CAST(MtlTags.VendName AS NVARCHAR(MAX)) AS VendName,
CAST(MtlTags.QtyNum AS NVARCHAR(MAX)) AS QtyNum,
CAST(MtlTags.RptLanguageID AS NVARCHAR(MAX)) AS RptLanguageID,
CAST(MtlTags.Company AS NVARCHAR(MAX)) AS Company,
CAST(ISNULL(CustXPrt.CustNum, '''') AS NVARCHAR(MAX)) AS CustNum,
CAST(ISNULL(Part.GrossWeight, 0) AS DECIMAL(15,5)) AS GrossWeight,
CAST(ISNULL(Part.GrossWeightUOM, '''') AS NVARCHAR(MAX)) AS GrossWeightUOM,
CAST(ISNULL(Part.TypeCode, ''Unknown'') AS NVARCHAR(MAX)) AS PartType,
CAST(ISNULL(Part.LongProductID_c, '''') AS NVARCHAR(MAX)) AS LongProductID,
CAST(ISNULL(Customer.CustID, '''') AS NVARCHAR(MAX)) AS CustID,
CAST(ISNULL(ShipTo.ShipToNum, '''') AS NVARCHAR(MAX)) AS ShipToNum
FROM MtlTags_'+@TableID +' MtlTags WITH(NOLOCK)
LEFT JOIN Part_' + @TableID + ' Part WITH(NOLOCK) ON MtlTags.Company = Part.Company AND MtlTags.PartNum = Part.PartNum
LEFT JOIN CustXPrt_' + @TableID + ' CustXPrt WITH(NOLOCK) ON Part.Company = CustXPrt.Company AND Part.PartNum = CustXPrt.PartNum
LEFT JOIN Customer_' + @TableID + ' Customer WITH(NOLOCK) ON CustXPrt.Company = Customer.Company AND CustXPrt.CustNum = Customer.CustNum
LEFT JOIN ShipTo_' + @TableID + ' ShipTo WITH(NOLOCK) ON Customer.Company = ShipTo.Company AND Customer.CustNum = ShipTo.CustNum
LEFT JOIN UD21_' + @TableID + ' BoxData WITH(NOLOCK) ON MtlTags.Company = BoxData.Company AND MtlTags.PartNum = BoxData.Key2 AND MtlTags.LotNum = BoxData.Character01
LEFT JOIN UD20_' + @TableID + ' PalletData WITH(NOLOCK) ON BoxData.Company = PalletData.Company AND BoxData.Key1 = PalletData.Key1
'
INSERT INTO #Results
EXEC(@SQL);
END
--Convert the Part Type to something readable.
UPDATE #Results SET PartType = 'Purchased' WHERE PartType = 'P'
UPDATE #Results SET PartType = 'Manufactured' WHERE PartType = 'M'
UPDATE #Results SET PartType = 'Sales Kit' WHERE PartType = 'S'
SELECT TOP 1
ItemQty
,PartNum
,PartDesc
,LotNum
,XPartNum
,OurSupplierCode
,BoxPalletSerial
,MfgDate
,XRevisionNum
,UM
,PONum
,POLine
,WhseCode
,AsmSeq
,TagNum
,BCAsmSeq
,BCBinNum
,BCJobNum
,BCLotNum
,BCOprSeq
,BCPartNum
,BCWhseCode
,BinNum
,JobNum
,LaborNote
,NCComment
,NonConfTranID
,OpCode
,OprSeq
,PORel
,Reason
,ReasonDesc
,ResGrpID
,TagFormat
,TagTitle
,VendID
,VendName
,QtyNum
,GrossWeight AS GrossWeightPerPart
,GrossWeightUOM AS GrossWeightUOMPerPart
,PartType
,Company
,CustNum
,LongProductID
,CustID
,ShipToNum
FROM #Results
WHERE (ShipToNum IS NULL OR ShipToNum <> '')
ORDER BY BoxPalletSerial DESC
END