-- DECLARE @WMS_Ver  AS VARCHAR (50) = (SELECT T0."AppVersion" FROM "PMX_FILES" T0 WHERE T0."FileName" = 'Produmex.Foundation.dll')
-- DECLARE @Beas_Ver AS VARCHAR (50) = (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2')

-- PMX Serial Numbers 
SELECT '2.1.01' AS "Section", 'Critical' AS "Impact", 'OITM.U_PMX_HSER' AS "Table Field Name", 'Has PMX Serial Number (Item Master Data - General Tab) is not supported by Beas' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_HSER" <> 'N'
UNION ALL 

-- PMX Track Location of Serial Numbers
SELECT '2.1.02' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_TLSN' AS "Table Field Name", 'Track Location of Serial Numbers (Item Master Data - General Tab) is off for serial tracked item' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE (T0."ManSerNum" = 'Y' AND T0."U_PMX_TLSN" <> 'Y') OR (T0."U_PMX_HSER" <> 'N' AND T0."U_PMX_TLSN" <> 'Y')
UNION ALL 

-- Release-Only on Batch- or Serial-Tracked Items 
SELECT '2.1.03a' AS "Section", 'Critical' AS "Impact", 'OITM.MngMethod' AS "Table Field Name", 'Management Method (Item Master Data - General Tab) is Release Only for serial or batch tracked item' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE (T0."ManSerNum" = 'Y' AND T0."MngMethod" = 'R') OR (T0."ManBtchNum" <> 'N' AND "MngMethod" = 'R')
UNION ALL 

-- Issue by not Serial/Batch for tracked items 
SELECT '2.1.03b' AS "Section", 'Critical' AS "Impact", 'OITM.IssuePriBy' AS "Table Field Name", 'Issue by (Item Master Data - General Tab) is not Serial/Batch for serial or batch tracked item' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE (T0."ManSerNum" = 'Y' AND T0."IssuePriBy" <> 0) OR (T0."ManBtchNum" <> 'N' AND "IssuePriBy" <> 0)
UNION ALL 

-- Global setting Unique Serial Number not set to Serial Number 
SELECT '2.1.03c' AS "Section", 'Critical' AS "Impact", 'OADM.SriUniqFld' AS "Table Field Name", 'Unique Serial Number by (General Settings - Inventory Tab) is not Serial Number' AS "Description", 
'' AS "Item Code", '' AS "Item Description", '' AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "OADM" T0 
WHERE T0."SriUniqFld" <> '3'
UNION ALL 

-- PMX UOM Decimal Places in OITM 
SELECT '2.1.04a' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_UOMD' AS "Table Field Name", 'Decimal Places detected (Item Master Data - Produmex tab, Inventory sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", CAST (T0."U_PMX_UOMD" AS VARCHAR) AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE COALESCE(T0."U_PMX_UOMD",0) <> 0
UNION ALL

-- Check for differences with decimal place definitions beween BEAS UOMs and PMX UOMs WMS 
--JMC 08.10.2021
SELECT '2.1.04b' AS "Section", 'Critical' AS "Impact", 'OITM.U_PMX_UOMD' AS "Table Field Name", 'Differences in Decimal Places detected (WMS - Beas)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(CAST (T0."U_PMX_UOMD" AS VARCHAR) , ' - ') , CAST("ANZDEZ" as VARCHAR)) , '  (') , "InvntryUom")  , ')') AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
LEFT OUTER JOIN BEAS_ME on T0."InvntryUom" = BEAS_ME.ME_ID
WHERE COALESCE(T0."U_PMX_UOMD",0) <> 0 AND "U_PMX_UOMD" <> "ANZDEZ"
UNION ALL

-- PMX secondary Batch tracking defined 
SELECT '2.1.06' AS "Section", 'Critical' AS "Impact", 'OITM.U_PMX_HBN2' AS "Table Field Name", 'Has second batch number (Item Master Data - Produmex tab, Inventory sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_HBN2" = 'Y' 
UNION ALL

-- PMX Zone Types on Items 
SELECT '2.1.07' AS "Section", 'Critical' AS "Impact", 'OITM.U_PMX_ITZT' AS "Table Field Name", 'Zone Type Code (Item Master Data - Produmex tab, Inventory sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  CAST(T1."ZoneTypeCode" AS VARCHAR) AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
INNER JOIN "PMX_ITZT" T1 ON T0."ItemCode" = T1."ItemCode"
UNION ALL

-- PMX Shelf Life Delivery defined on Items 
--JMC 08/10/2021 Shelf Life defined in WMS -- DG 10/27/2021 - Added CAST() on Referenced Data
SELECT '2.1.09' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_SLID' AS "Table Field Name", 'Shelf Life Delivery defined (Item Master Data - Produmex tab, Sales sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  CAST(U_PMX_SLID AS VARCHAR) AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE U_PMX_SLID > 0
UNION ALL

-- PMX Purchase Return Reasons defined on Items 
SELECT '2.1.10' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_NRSN' AS "Table Field Name", 'Purchase Return Reason (Item Master Data - Produmex tab, Inventory sub-tab), ignored by Beas' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", '' AS "Referenced Data",T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_NRSN" = 'Y' 
UNION ALL

-- PMX Quality Status Production defined on Items 
-- Only outputs data if the Beas version is before 2021.09 which doesn't support this feature
SELECT '2.1.11' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_QSPR' AS "Table Field Name", 'Quality Status Production (Item Master Data - Produmex tab, Inventory sub-tab), ignored by Beas' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  T0."U_PMX_QSPR" AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_QSPR" <> '' AND (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2') < '202109'
UNION ALL

-- PMX Expiry definition used on Items 
SELECT '2.1.12' AS "Section", 'Warning' AS "Impact", 'OITM.U_PMX_EXDP' AS "Table Field Name", 'Expiry Definition (Item Master Data - Produmex tab, Inventory sub-tab), ignored by Beas' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", CAST(T0."U_PMX_EXDP" AS VARCHAR) AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_EXDP" <> ''
UNION ALL

-- PMX Catch Weights used on Items 
SELECT '2.1.13' AS "Section", 'Critical' AS "Impact", 'OITM.U_PMX_ICAW' AS "Table Field Name", 'Catch Weight (Item Master Data - Produmex tab, Catch Weight sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description", '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
WHERE T0."U_PMX_ICAW" = 'Y'
UNION ALL

-- PMX Batch Attributes used on Items 
SELECT '2.1.14a' AS "Section", 'Critical' AS "Impact", 'PMX_IBAT' AS "Table Field Name", 'Batch Attributes detected (Item Master Data - Produmex tab, Attributes sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  '' AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
INNER JOIN "PMX_IBAT" T1 ON T0."ItemCode" = T1."ItemCode"
WHERE T0."ManBtchNum" = 'Y' AND (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2') < '202202'
UNION ALL

-- PMX Batch Attributes defined (in UDTs) 
SELECT DISTINCT '2.1.14b' AS "Section", 'zInformation' AS "Impact", 'PMX_IBAT' AS "Table Field Name", 'Batch Attributes Defined in UDT: PMX_BATT but not used (Item Master Data - Produmex tab, Attributes sub-tab)' AS "Description", 
'' AS "Item Code", '' AS "Item Description", CAST(T0."Code" AS VARCHAR) AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "@PMX_BATT" T0
LEFT OUTER JOIN "PMX_IBAT" T1 ON T1."BatchAttributeCode" = T0."Code"
WHERE T1."InternalKey" IS NULL
UNION ALL

-- PMX Batch Attributes used on Items 
SELECT '2.1.14c' AS "Section", 'Critical' AS "Impact", 'PMX_IBAT' AS "Table Field Name", 'Required Batch Attributes detected (Item Master Data - Produmex tab, Attributes sub-tab)' AS "Description", 
T0."ItemCode" AS "Item Code", T0."ItemName" AS "Item Description",  T1."BatchAttributeCode" AS "Referenced Data", T0."InvntItem" AS "Inv Item?", T0."ItemType" AS "Item Type", T0."ManBtchNum" AS "Batch Tracked?", T0."ManSerNum" AS "Serial Tracked?", 
CASE WHEN (T0."UgpEntry") = -1 THEN 'Manual' ELSE (SELECT T9."UgpCode" FROM "OUGP" T9 WHERE T9."UgpEntry" = T0."UgpEntry") END AS "UOM Group" 
FROM "OITM" T0 
INNER JOIN "PMX_IBAT" T1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN "PMX_IBAT" T2 ON T0."ItemCode" = T2."ItemCode" AND T1."BatchAttributeCode" = T2."BatchAttributeCode" AND T2."ValueEntryOption" = 'REQUIRED'
WHERE T0."ManBtchNum" = 'Y' AND (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2') >= '202202'
UNION ALL

-- Check for existance of NON UPPERCASE batches 
SELECT DISTINCT 'Beas001' AS "Section", 'Critical' AS "Impact", 'OBTN' AS "Table Field Name", 'Existing Batches found with NON UPPERCASE values' AS "Description", 
T0."ItemCode" AS "Item Code", T0."itemName" AS "Item Description", T0."DistNumber" AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM OBTN T0
WHERE  UPPER(T0."DistNumber") <> (T0."DistNumber") 
UNION ALL

-- Beas External Operations 
--JMC 08/10/2021 Validation for External Operations -- DG 10/27/2021 - Added CAST() on Referenced Data
SELECT DISTINCT 'Beas002' AS "Section", 'Warning' AS "Impact", 'BEAS_AG' AS "Table Field Name", 'External Operations not supported by WMS' AS "Description", 
'' AS "Item Code", '' AS "Item Description", CAST ("AG_ID" AS VARCHAR) AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM BEAS_AG T0
WHERE  "FAG" = 'J' 
UNION ALL 

-- Beas Software Version 
-- DG 10/27/2021 - Added version information with logic to set Impact to Critical if not on latest version of both WMS and Beas
SELECT TOP 1 '0.0.00' AS "Section", 
CASE WHEN (SELECT T0."AppVersion" FROM "PMX_FILES" T0 WHERE T0."FileName" = 'Produmex.Foundation.dll') >= '2021.09' AND (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2') >= '202109' THEN 'Information' ELSE 'Critical' END AS "Impact", '' AS "Table Field Name", concat(CONCAT(CONCAT ('WMS Version: ', (SELECT T0."AppVersion" FROM "PMX_FILES" T0 WHERE T0."FileName" = 'Produmex.Foundation.dll')), '; Beas Version: '), (SELECT LEFT(T9."INHALT",6) FROM "BEAS_SYS_SETUP" T9 WHERE T9."VARIABLE_ID" = 'dbversion2') ) AS "Description", 
'' AS "Item Code", '' AS "Item Description", '' AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "PMX_FILES" T0 
UNION ALL

-- Beas Alloation Warehouses 
SELECT '0.0.01' AS "Section", 'Warning' AS "Impact", 'OWHS.U_beas_lck' AS "Table Field Name", CONCAT(CONCAT('Warehouse ',T0."WhsCode"),' is a Beas Allocation warehouse') AS "Description", 
'' AS "Item Code", '' AS "Item Description", T0."WhsCode" AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "OWHS" T0 
WHERE T0."U_beas_lck" = 'B'
UNION ALL

-- SAP Bins activated in Warehouses 
SELECT '0.0.02' AS "Section", 'Warning' AS "Impact", 'OWHS.BinActivat' AS "Table Field Name", CONCAT(CONCAT('Warehouse ',T0."WhsCode"),' has SAP Bins enabled') AS "Description", 
'' AS "Item Code", '' AS "Item Description", T0."WhsCode" AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "OWHS" T0 
WHERE T0."BinActivat" = 'Y'
UNION ALL

-- Beas Bins activated in Warehouses 
SELECT TOP 1 '0.0.03' AS "Section", 'Warning' AS "Impact", 'BEAS_WHSBIN' AS "Table Field Name", 'Beas Bins found in warehouse' AS "Description", 
'' AS "Item Code", '' AS "Item Description", T0."WhsCode" AS "Referenced Data", '' AS "Inv Item?", '' AS "Item Type", '' AS "Batch Tracked?", '' AS "Serial Tracked?", 
'' AS "UOM Group" 
FROM "OWHS" T0
INNER JOIN "BEAS_WHSBIN" T1 ON T0."WhsCode" =  T1."WhsCode"

ORDER BY "Section", "Impact"