Product and per-site stock dossier — Pro
Class selection, product attributes and native price/cost values, with on-demand site quantities, bins and replenishment context.
UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.
For warehouse, purchasing, sales and product teams: choose a product class, find an item, inspect its barcode, units, native prices/costs, attributes and location defaults. Open Site quantities only when needed to inspect each site/company, on-hand and separately stored availability, allocations, customer/purchase orders, backorders, transit, bins and replenishment settings.
Why Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.
Pro: one SQL source, one Home button, two lists and one on-demand related button. 32 product and 31 site values in grouped Details, eight useful fields per card, Search and native class opening selection. Technical keys/context are hidden. Live reads cap each list at 1,000 rows with no automatic refresh; identity guards may scan beyond the cap. This is not a complete-company stock total or installed performance guarantee.
Microsoft source registrations identify Inventory, ItemSite, Site and ProductClass. Own pinned migration code binds ItemSite.InvtID to the product, uses StkUnit as the base unit, uses site QtyOnHand directly, and identifies product class and site location codes. AL logical keys do not certify installed SQL DDL, PK/FK, storage types or constraints. Inventory has no selected CpnyID; it is not invented. ItemSite keys are item+site, not an invented company triple. Duplicate, blank or oversized products reject the root read; duplicate/invalid site rows block only their own product. Every selected product and optional current-class context is revalidated before reading sites. No first-match or silent duplicate removal.
Quantities remain separately named stored fields, not QtyOnHand-minus-allocations, available-to-promise, unit conversions, stock movements or calculated value. Zero, negative and NULL source values are not repaired or replaced. Purchase/sales units stay separate. StkBasePrc, LastCost and StdCost are distinct native values: source currency, price validity, tax, discount and valuation are unverified. No currency symbol, conversion or price-times-stock formula. Verify those settings before commercial use. TranStatusCode IN/DE has documented inactive/discontinued migration context; Status is a different raw field, not the same status or an access/eligibility flag. Other codes are not translated into guessed states.
Product class description and site name/company are optional current metadata, shown only with an unambiguous bounded identity; missing, duplicate or invalid metadata preserves the original product/site rows without fan-out. Current Site company stays separate from the original stock-row company; disagreements are displayed, never silently reassigned. Dates are source values, not UTC conversion or a transaction-period stock calculation.
Documentary and adapted synthetic/native Cifru DEMO validation only. No real Dynamics SL or SQL Server, installed owner/release/DDL/index/query plan, ERP permissions or iOS has been verified. The proposed dbo owner and business database must be checked at import/read test. Direct SQL does not inherit ERP/company ACL. Class/company/site filters and hidden keys are not access control; require an administrator-scoped SELECT-only reporting login, not migration db_owner/sysadmin. No source credentials, bank, audit/user or custom/future columns. Fictional DEMO screenshots are not a live ERP connection.
Unofficial, not Microsoft endorsed. Dynamics SL is a distinct legacy installed-base product with extended support ending July 2028, not a current top-three or no-mobile claim.
Primary source registration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/codeunits/SLHybridWizard.Codeunit.al
Product fields: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLInventoryBuffer.Table.al
Site quantity fields: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLItemSiteBuffer.Table.al
Binding and unit context: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLItemMigrator.Codeunit.al
Screenshots
What this package creates
1 Home1 Details2 lists1 sources to map
- Home: Products and site quantities
- Details: Site quantities
- Sub-button: Site quantities
Sources are mapped locally and verified before applying.
Custom queriesPRO2 SQL
Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.
Statically verified read-only
$.components.workspaceSelection.datasets.0.sqlQuerySELECT RTRIM(CAST(m.InvtID AS nvarchar(max))) AS InvtID,m.Descr AS Descr,m.ClassID AS ClassID,m.TranStatusCode AS TranStatusCode,m.Status AS Status,m.StkUnit AS StkUnit,m.DfltPOUnit AS DfltPOUnit,m.DfltSOUnit AS DfltSOUnit,m.DfltSite AS DfltSite,m.DfltPickLoc AS DfltPickLoc,m.DfltWhseLoc AS DfltWhseLoc,m.UPCCode AS UPCCode,m.PriceClassID AS PriceClassID,m.StkBasePrc AS StkBasePrc,m.LastCost AS LastCost,m.StdCost AS StdCost,m.LotSerTrack AS LotSerTrack,m.SerAssign AS SerAssign,m.ShelfLife AS ShelfLife,m.WarrantyDays AS WarrantyDays,m.Color AS Color,m.Size AS Size,m.Style AS Style,m.MaterialType AS MaterialType,m.Pack AS Pack,m.StkWt AS StkWt,m.StkWtUnit AS StkWtUnit,CASE WHEN RTRIM(CAST(m.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END AS ProductKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END) AS ProductContext,pc.Descr AS ClassName,CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS ClassContext,CASE WHEN NOT (NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) AND ((bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60) OR (bad.SiteID IS NULL OR RTRIM(CAST(bad.SiteID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20))) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) GROUP BY CAST(RTRIM(CAST(bad.SiteID AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max)))) HAVING COUNT(1)<>1)) THEN N'Ambiguous / invalid site rows' WHEN EXISTS (SELECT 1 FROM dbo.ItemSite a WHERE (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max)))))) THEN N'Available on demand' ELSE N'No stored site rows' END AS SiteAvailability,N'Stored values; source currency / price validity unverified' AS PriceScope,N'Current product master, not dated movements or company totals' AS MasterScope FROM dbo.Inventory m LEFT JOIN dbo.ProductClass pc ON (RTRIM(CAST(pc.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))) AND NOT (pc.ClassID IS NULL OR RTRIM(CAST(pc.ClassID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))>12) AND (SELECT COUNT(1) FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))))=1 WHERE NOT EXISTS (SELECT 1 FROM dbo.Inventory bad WHERE (bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60)) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad GROUP BY CAST(RTRIM(CAST(bad.InvtID AS nvarchar(max))) AS nvarchar(30)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max)))) HAVING COUNT(1)<>1)
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT RTRIM(CAST(a.InvtID AS nvarchar(max))) AS InvtID,RTRIM(CAST(a.SiteID AS nvarchar(max))) AS SiteID,RTRIM(CAST(a.CpnyID AS nvarchar(max))) AS CpnyID,a.QtyOnHand AS QtyOnHand,a.QtyAvail AS QtyAvail,a.QtyNotAvail AS QtyNotAvail,a.QtyAlloc AS QtyAlloc,a.QtyCustOrd AS QtyCustOrd,a.QtyOnBO AS QtyOnBO,a.QtyOnPO AS QtyOnPO,a.QtyInTransit AS QtyInTransit,a.QtyShipNotInv AS QtyShipNotInv,a.ReordPt AS ReordPt,a.ReordQty AS ReordQty,a.SafetyStk AS SafetyStk,a.MaxOnHand AS MaxOnHand,a.DfltWhseLoc AS DfltWhseLoc,a.DfltPickBin AS DfltPickBin,a.DfltPutAwayBin AS DfltPutAwayBin,a.DfltPOUnit AS DfltPOUnit,a.DfltSOUnit AS DfltSOUnit,a.LastCountDate AS LastCountDate,a.LastPurchaseDate AS LastPurchaseDate,a.LastVendor AS LastVendor,CONCAT(CASE WHEN RTRIM(CAST(a.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(a.SiteID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.SiteID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.SiteID AS nvarchar(max))) AS nvarchar(max)),N';') END) AS StockKey,CASE WHEN RTRIM(CAST(a.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END AS ProductKey,:productContext AS ProductContext,m.Descr AS ProductName,m.StkUnit AS StockUnit,s.Name AS SiteName,s.CpnyID AS SiteCompany,CASE WHEN s.SiteId IS NULL THEN N'Missing / ambiguous current site' WHEN s.CpnyID IS NULL OR RTRIM(CAST(s.CpnyID AS nvarchar(max)))=N'' THEN N'Current site has no company code' WHEN (RTRIM(CAST(s.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.CpnyID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.CpnyID AS nvarchar(max))))) THEN N'Unique current site; company agrees' ELSE N'Unique current site; company differs' END AS SiteContext,N'Current separate stored quantities; no movements or calculated total' AS QuantityScope,CASE WHEN s.Name IS NULL OR RTRIM(CAST(s.Name AS nvarchar(max)))=N'' THEN RTRIM(CAST(a.SiteID AS nvarchar(max))) ELSE s.Name END AS SiteDisplay FROM dbo.ItemSite a INNER JOIN dbo.Inventory m ON (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) LEFT JOIN dbo.ProductClass pc ON (RTRIM(CAST(pc.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))) AND NOT (pc.ClassID IS NULL OR RTRIM(CAST(pc.ClassID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))>12) AND (SELECT COUNT(1) FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))))=1 LEFT JOIN dbo.Site s ON (RTRIM(CAST(s.SiteId AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.SiteID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.SiteId AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.SiteID AS nvarchar(max))))) AND NOT (s.SiteId IS NULL OR RTRIM(CAST(s.SiteId AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(s.SiteId AS nvarchar(max))))>20) AND (SELECT COUNT(1) FROM dbo.Site probe WHERE (RTRIM(CAST(probe.SiteId AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.SiteID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.SiteId AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.SiteID AS nvarchar(max))))))=1 WHERE (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad WHERE (bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60)) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad GROUP BY CAST(RTRIM(CAST(bad.InvtID AS nvarchar(max))) AS nvarchar(30)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max)))) HAVING COUNT(1)<>1) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) AND ((bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60) OR (bad.SiteID IS NULL OR RTRIM(CAST(bad.SiteID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20))) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) GROUP BY CAST(RTRIM(CAST(bad.SiteID AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max)))) HAVING COUNT(1)<>1) AND (CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:productContext COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END))=DATALENGTH(:productContext))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Dynamics SL — authorised SQL business database",
"id": "16EAB704-ADF0-58EE-BC34-F4501BA89683",
"kind": "sqlServer",
"requiredObjects": [
"dbo.Inventory",
"dbo.ItemSite",
"dbo.Site",
"dbo.ProductClass"
],
"requiresCustomSQL": false
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "FDCF1C3C-2F60-5EED-9991-A99BD89E5CF7",
"key": "Descr",
"type": "text"
},
{
"direction": "ascending",
"id": "D548628C-5C21-5B03-B3A8-C42AD3D04106",
"key": "ProductKey",
"type": "text"
}
],
"id": "9730891D-9D25-5B8B-8176-E7E3B55E96D6",
"mappings": [
{
"commonFieldKey": "",
"key": "ProductKey",
"label": "Internal complete product context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProductKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductContext",
"label": "Internal complete product context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProductContext",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvtID",
"label": "Product business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "InvtID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Descr",
"label": "Product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Descr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ClassID",
"label": "Product class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TranStatusCode",
"label": "Transaction status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TranStatusCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Status",
"label": "Separate stored status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Status",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StkUnit",
"label": "Stock unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StkUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DfltPOUnit",
"label": "Default purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltPOUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltSOUnit",
"label": "Default sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltSOUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltSite",
"label": "Default site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltSite",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DfltPickLoc",
"label": "Default pick location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltPickLoc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltWhseLoc",
"label": "Default warehouse location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltWhseLoc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UPCCode",
"label": "UPC barcode",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UPCCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PriceClassID",
"label": "Price class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PriceClassID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StkBasePrc",
"label": "Stored base price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StkBasePrc",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "LastCost",
"label": "Stored last cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LastCost",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "StdCost",
"label": "Stored standard cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StdCost",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LotSerTrack",
"label": "Lot / serial tracking code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LotSerTrack",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SerAssign",
"label": "Serial assignment code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SerAssign",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShelfLife",
"label": "Stored shelf-life setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ShelfLife",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarrantyDays",
"label": "Warranty days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "WarrantyDays",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Color",
"label": "Colour",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Color",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Size",
"label": "Size",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Size",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Style",
"label": "Style",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Style",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MaterialType",
"label": "Material type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MaterialType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Pack",
"label": "Pack code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Pack",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StkWt",
"label": "Stock weight",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StkWt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StkWtUnit",
"label": "Weight unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StkWtUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ClassName",
"label": "Current class description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ClassContext",
"label": "Class metadata context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteAvailability",
"label": "Site-row identity availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PriceScope",
"label": "Price / cost value scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PriceScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MasterScope",
"label": "Product record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MasterScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Products and site quantities",
"primaryKey": "ProductKey",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvtID",
"Descr",
"ClassID",
"TranStatusCode",
"Status",
"StkUnit",
"DfltPOUnit",
"DfltSOUnit",
"DfltSite",
"DfltPickLoc",
"DfltWhseLoc",
"UPCCode",
"PriceClassID",
"StkBasePrc",
"LastCost",
"StdCost",
"LotSerTrack",
"SerAssign",
"ShelfLife",
"WarrantyDays",
"Color",
"Size",
"Style",
"MaterialType",
"Pack",
"StkWt",
"StkWtUnit",
"ClassName",
"ClassContext",
"SiteAvailability",
"PriceScope",
"MasterScope"
],
"sourceID": "16EAB704-ADF0-58EE-BC34-F4501BA89683",
"sqlQuery": "SELECT RTRIM(CAST(m.InvtID AS nvarchar(max))) AS InvtID,m.Descr AS Descr,m.ClassID AS ClassID,m.TranStatusCode AS TranStatusCode,m.Status AS Status,m.StkUnit AS StkUnit,m.DfltPOUnit AS DfltPOUnit,m.DfltSOUnit AS DfltSOUnit,m.DfltSite AS DfltSite,m.DfltPickLoc AS DfltPickLoc,m.DfltWhseLoc AS DfltWhseLoc,m.UPCCode AS UPCCode,m.PriceClassID AS PriceClassID,m.StkBasePrc AS StkBasePrc,m.LastCost AS LastCost,m.StdCost AS StdCost,m.LotSerTrack AS LotSerTrack,m.SerAssign AS SerAssign,m.ShelfLife AS ShelfLife,m.WarrantyDays AS WarrantyDays,m.Color AS Color,m.Size AS Size,m.Style AS Style,m.MaterialType AS MaterialType,m.Pack AS Pack,m.StkWt AS StkWt,m.StkWtUnit AS StkWtUnit,CASE WHEN RTRIM(CAST(m.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END AS ProductKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END) AS ProductContext,pc.Descr AS ClassName,CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS ClassContext,CASE WHEN NOT (NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) AND ((bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60) OR (bad.SiteID IS NULL OR RTRIM(CAST(bad.SiteID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20))) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) GROUP BY CAST(RTRIM(CAST(bad.SiteID AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max)))) HAVING COUNT(1)<>1)) THEN N'Ambiguous / invalid site rows' WHEN EXISTS (SELECT 1 FROM dbo.ItemSite a WHERE (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max)))))) THEN N'Available on demand' ELSE N'No stored site rows' END AS SiteAvailability,N'Stored values; source currency / price validity unverified' AS PriceScope,N'Current product master, not dated movements or company totals' AS MasterScope FROM dbo.Inventory m LEFT JOIN dbo.ProductClass pc ON (RTRIM(CAST(pc.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))) AND NOT (pc.ClassID IS NULL OR RTRIM(CAST(pc.ClassID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))>12) AND (SELECT COUNT(1) FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))))=1 WHERE NOT EXISTS (SELECT 1 FROM dbo.Inventory bad WHERE (bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60)) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad GROUP BY CAST(RTRIM(CAST(bad.InvtID AS nvarchar(max))) AS nvarchar(30)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max)))) HAVING COUNT(1)<>1)",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "08D49E80-66AF-5457-BB3C-8DEE698789E7",
"key": "CpnyID",
"type": "text"
},
{
"direction": "ascending",
"id": "D1F1F2FB-51D1-5382-9E32-EA471CE0B04A",
"key": "SiteID",
"type": "text"
},
{
"direction": "ascending",
"id": "6739E124-ABBF-5653-8180-FDC9DCBA0082",
"key": "StockKey",
"type": "text"
}
],
"id": "B8F6F91E-268C-59B4-A486-1BEAD320B2CD",
"mappings": [
{
"commonFieldKey": "",
"key": "StockKey",
"label": "Internal complete product context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StockKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductKey",
"label": "Internal complete product context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProductKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductContext",
"label": "Internal complete product context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProductContext",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvtID",
"label": "Product business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "InvtID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteID",
"label": "Stock site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CpnyID",
"label": "Stock-row company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CpnyID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyOnHand",
"label": "Stored quantity on hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyOnHand",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyAvail",
"label": "Stored available quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyAvail",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyNotAvail",
"label": "Stored unavailable quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyNotAvail",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QtyAlloc",
"label": "Stored allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyAlloc",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyCustOrd",
"label": "Stored customer-order quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyCustOrd",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QtyOnBO",
"label": "Stored backorder quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyOnBO",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QtyOnPO",
"label": "Stored purchase-order quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyOnPO",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyInTransit",
"label": "Stored in-transit quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyInTransit",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QtyShipNotInv",
"label": "Stored shipped-not-invoiced quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QtyShipNotInv",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReordPt",
"label": "Stored reorder point",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ReordPt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReordQty",
"label": "Stored reorder quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ReordQty",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SafetyStk",
"label": "Stored safety stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SafetyStk",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MaxOnHand",
"label": "Stored maximum on hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MaxOnHand",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltWhseLoc",
"label": "Warehouse location code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltWhseLoc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltPickBin",
"label": "Pick bin code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltPickBin",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltPutAwayBin",
"label": "Put-away bin code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltPutAwayBin",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltPOUnit",
"label": "Site purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltPOUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DfltSOUnit",
"label": "Site sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DfltSOUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastCountDate",
"label": "Last count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LastCountDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastPurchaseDate",
"label": "Last purchase date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LastPurchaseDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastVendor",
"label": "Last supplier business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LastVendor",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductName",
"label": "Parent product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProductName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StockUnit",
"label": "Parent stock unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "StockUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SiteName",
"label": "Current site name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteCompany",
"label": "Current site company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteCompany",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteContext",
"label": "Current site metadata context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityScope",
"label": "Quantity record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QuantityScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteDisplay",
"label": "Current site name or code fallback",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteDisplay",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Product site quantities",
"primaryKey": "StockKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvtID",
"id": "39C6CA5D-71DB-5430-9D0E-4EBCA16A7BFE",
"name": "product",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ProductContext",
"id": "D08D49FB-5BD4-50EE-AF34-2E52D5A5E22A",
"name": "productContext",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvtID",
"SiteID",
"CpnyID",
"QtyOnHand",
"QtyAvail",
"QtyNotAvail",
"QtyAlloc",
"QtyCustOrd",
"QtyOnBO",
"QtyOnPO",
"QtyInTransit",
"QtyShipNotInv",
"ReordPt",
"ReordQty",
"SafetyStk",
"MaxOnHand",
"DfltWhseLoc",
"DfltPickBin",
"DfltPutAwayBin",
"DfltPOUnit",
"DfltSOUnit",
"LastCountDate",
"LastPurchaseDate",
"LastVendor",
"ProductName",
"StockUnit",
"SiteName",
"SiteCompany",
"SiteContext",
"QuantityScope",
"SiteDisplay"
],
"sourceID": "16EAB704-ADF0-58EE-BC34-F4501BA89683",
"sqlQuery": "SELECT RTRIM(CAST(a.InvtID AS nvarchar(max))) AS InvtID,RTRIM(CAST(a.SiteID AS nvarchar(max))) AS SiteID,RTRIM(CAST(a.CpnyID AS nvarchar(max))) AS CpnyID,a.QtyOnHand AS QtyOnHand,a.QtyAvail AS QtyAvail,a.QtyNotAvail AS QtyNotAvail,a.QtyAlloc AS QtyAlloc,a.QtyCustOrd AS QtyCustOrd,a.QtyOnBO AS QtyOnBO,a.QtyOnPO AS QtyOnPO,a.QtyInTransit AS QtyInTransit,a.QtyShipNotInv AS QtyShipNotInv,a.ReordPt AS ReordPt,a.ReordQty AS ReordQty,a.SafetyStk AS SafetyStk,a.MaxOnHand AS MaxOnHand,a.DfltWhseLoc AS DfltWhseLoc,a.DfltPickBin AS DfltPickBin,a.DfltPutAwayBin AS DfltPutAwayBin,a.DfltPOUnit AS DfltPOUnit,a.DfltSOUnit AS DfltSOUnit,a.LastCountDate AS LastCountDate,a.LastPurchaseDate AS LastPurchaseDate,a.LastVendor AS LastVendor,CONCAT(CASE WHEN RTRIM(CAST(a.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(a.SiteID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.SiteID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.SiteID AS nvarchar(max))) AS nvarchar(max)),N';') END) AS StockKey,CASE WHEN RTRIM(CAST(a.InvtID AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.InvtID AS nvarchar(max))) AS nvarchar(max)),N';') END AS ProductKey,:productContext AS ProductContext,m.Descr AS ProductName,m.StkUnit AS StockUnit,s.Name AS SiteName,s.CpnyID AS SiteCompany,CASE WHEN s.SiteId IS NULL THEN N'Missing / ambiguous current site' WHEN s.CpnyID IS NULL OR RTRIM(CAST(s.CpnyID AS nvarchar(max)))=N'' THEN N'Current site has no company code' WHEN (RTRIM(CAST(s.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.CpnyID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.CpnyID AS nvarchar(max))))) THEN N'Unique current site; company agrees' ELSE N'Unique current site; company differs' END AS SiteContext,N'Current separate stored quantities; no movements or calculated total' AS QuantityScope,CASE WHEN s.Name IS NULL OR RTRIM(CAST(s.Name AS nvarchar(max)))=N'' THEN RTRIM(CAST(a.SiteID AS nvarchar(max))) ELSE s.Name END AS SiteDisplay FROM dbo.ItemSite a INNER JOIN dbo.Inventory m ON (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.InvtID AS nvarchar(max))))) LEFT JOIN dbo.ProductClass pc ON (RTRIM(CAST(pc.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))) AND NOT (pc.ClassID IS NULL OR RTRIM(CAST(pc.ClassID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(pc.ClassID AS nvarchar(max))))>12) AND (SELECT COUNT(1) FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max))))))=1 LEFT JOIN dbo.Site s ON (RTRIM(CAST(s.SiteId AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.SiteID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.SiteId AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.SiteID AS nvarchar(max))))) AND NOT (s.SiteId IS NULL OR RTRIM(CAST(s.SiteId AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(s.SiteId AS nvarchar(max))))>20) AND (SELECT COUNT(1) FROM dbo.Site probe WHERE (RTRIM(CAST(probe.SiteId AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(a.SiteID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.SiteId AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(a.SiteID AS nvarchar(max))))))=1 WHERE (RTRIM(CAST(a.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad WHERE (bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60)) AND NOT EXISTS (SELECT 1 FROM dbo.Inventory bad GROUP BY CAST(RTRIM(CAST(bad.InvtID AS nvarchar(max))) AS nvarchar(30)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max)))) HAVING COUNT(1)<>1) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) AND ((bad.InvtID IS NULL OR RTRIM(CAST(bad.InvtID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))>60) OR (bad.SiteID IS NULL OR RTRIM(CAST(bad.SiteID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20))) AND NOT EXISTS (SELECT 1 FROM dbo.ItemSite bad WHERE (RTRIM(CAST(bad.InvtID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:product AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.InvtID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:product AS nvarchar(max))))) GROUP BY CAST(RTRIM(CAST(bad.SiteID AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.SiteID AS nvarchar(max)))) HAVING COUNT(1)<>1) AND (CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:productContext COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.InvtID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Descr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranStatusCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Status),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSOUnit),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltSite),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltPickLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DfltWhseLoc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.UPCCode),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PriceClassID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkBasePrc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LastCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StdCost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LotSerTrack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.SerAssign),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ShelfLife),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.WarrantyDays),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Color),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Size),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Style),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.MaterialType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Pack),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.StkWtUnit),2) AS nvarchar(max)),N';') END,CASE WHEN pc.ClassID IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.ClassID AS nvarchar(max))),N':',CAST(pc.ClassID AS nvarchar(max)),N';') END,CASE WHEN pc.Descr IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(pc.Descr AS nvarchar(max))),N':',CAST(pc.Descr AS nvarchar(max)),N';') END,CASE WHEN CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max))),N':',CAST(CASE WHEN m.ClassID IS NULL OR RTRIM(CAST(m.ClassID AS nvarchar(max)))=N'' THEN N'No class code' WHEN NOT EXISTS (SELECT 1 FROM dbo.ProductClass probe WHERE (RTRIM(CAST(probe.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.ClassID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.ClassID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.ClassID AS nvarchar(max)))))) THEN N'No current class record' WHEN pc.ClassID IS NULL THEN N'Ambiguous / invalid class record' ELSE N'Unique current class record' END AS nvarchar(max)),N';') END))=DATALENGTH(:productContext))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "0358F268-655D-57D9-95FD-CDBDDD24A77F",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "AE95C831-43F8-5B21-A037-88C48F751D58",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "B8F6F91E-268C-59B4-A486-1BEAD320B2CD",
"title": "Site quantities",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ClassName",
"label": "Class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TranStatusCode",
"label": "Transaction status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StkUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StkBasePrc",
"label": "Base price · verify currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LastCost",
"label": "Last cost · verify currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DfltSite",
"label": "Default site",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UPCCode",
"label": "UPC barcode",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SiteAvailability",
"label": "Site quantities",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "9730891D-9D25-5B8B-8176-E7E3B55E96D6",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "Descr",
"label": "Product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "InvtID",
"label": "Product business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "ClassID",
"label": "Product class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "ClassName",
"label": "Current class description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "TranStatusCode",
"label": "Transaction status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "Status",
"label": "Separate stored status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product identity and class",
"detailRole": "information",
"isVisible": true,
"key": "ClassContext",
"label": "Class metadata context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "StkUnit",
"label": "Stock unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "DfltPOUnit",
"label": "Default purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "DfltSOUnit",
"label": "Default sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "DfltSite",
"label": "Default site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "DfltPickLoc",
"label": "Default pick location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units and default locations",
"detailRole": "information",
"isVisible": true,
"key": "DfltWhseLoc",
"label": "Default warehouse location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native price and cost values",
"detailRole": "information",
"isVisible": true,
"key": "PriceClassID",
"label": "Price class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native price and cost values",
"detailRole": "information",
"isVisible": true,
"key": "StkBasePrc",
"label": "Stored base price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native price and cost values",
"detailRole": "information",
"isVisible": true,
"key": "LastCost",
"label": "Stored last cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native price and cost values",
"detailRole": "information",
"isVisible": true,
"key": "StdCost",
"label": "Stored standard cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native price and cost values",
"detailRole": "information",
"isVisible": true,
"key": "PriceScope",
"label": "Price / cost value scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "UPCCode",
"label": "UPC barcode",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "Color",
"label": "Colour",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "Size",
"label": "Size",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "Style",
"label": "Style",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "MaterialType",
"label": "Material type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "Pack",
"label": "Pack code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "StkWt",
"label": "Stock weight",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Barcode and product attributes",
"detailRole": "information",
"isVisible": true,
"key": "StkWtUnit",
"label": "Weight unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Tracking and product lifespan",
"detailRole": "information",
"isVisible": true,
"key": "LotSerTrack",
"label": "Lot / serial tracking code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Tracking and product lifespan",
"detailRole": "information",
"isVisible": true,
"key": "SerAssign",
"label": "Serial assignment code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Tracking and product lifespan",
"detailRole": "information",
"isVisible": true,
"key": "ShelfLife",
"label": "Stored shelf-life setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Tracking and product lifespan",
"detailRole": "information",
"isVisible": true,
"key": "WarrantyDays",
"label": "Warranty days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Reading and scope",
"detailRole": "information",
"isVisible": true,
"key": "SiteAvailability",
"label": "Site-row identity availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Reading and scope",
"detailRole": "information",
"isVisible": true,
"key": "MasterScope",
"label": "Product record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "70AF16BE-FEED-5A1C-B54F-90CC6F44540C",
"openFilters": [
{
"id": "C12BB47E-3B9C-51E4-9379-170D6E71BD74",
"includeAllOption": true,
"key": "ClassID",
"title": "Product class",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "ascending",
"id": "FDCF1C3C-2F60-5EED-9991-A99BD89E5CF7",
"key": "Descr",
"type": "text"
},
{
"direction": "ascending",
"id": "D548628C-5C21-5B03-B3A8-C42AD3D04106",
"key": "ProductKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "InvtID",
"systemImage": "shippingbox",
"title": "Products and site quantities",
"titleKey": "Descr"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CpnyID",
"label": "Stock-row company",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QtyOnHand",
"label": "On hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QtyAvail",
"label": "Available · stored",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QtyAlloc",
"label": "Allocated · stored",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QtyOnPO",
"label": "Purchase orders · stored",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QtyInTransit",
"label": "In transit · stored",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SiteContext",
"label": "Site metadata",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "B8F6F91E-268C-59B4-A486-1BEAD320B2CD",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "SiteDisplay",
"label": "Current site name or code fallback",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "SiteID",
"label": "Stock site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "CpnyID",
"label": "Stock-row company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "ProductName",
"label": "Parent product description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "InvtID",
"label": "Product business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Product and site identity",
"detailRole": "information",
"isVisible": true,
"key": "StockUnit",
"label": "Parent stock unit code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyOnHand",
"label": "Stored quantity on hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyAvail",
"label": "Stored available quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyNotAvail",
"label": "Stored unavailable quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyAlloc",
"label": "Stored allocated quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyCustOrd",
"label": "Stored customer-order quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyOnBO",
"label": "Stored backorder quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyOnPO",
"label": "Stored purchase-order quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyInTransit",
"label": "Stored in-transit quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate stored quantities",
"detailRole": "information",
"isVisible": true,
"key": "QtyShipNotInv",
"label": "Stored shipped-not-invoiced quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Site replenishment settings",
"detailRole": "information",
"isVisible": true,
"key": "ReordPt",
"label": "Stored reorder point",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Site replenishment settings",
"detailRole": "information",
"isVisible": true,
"key": "ReordQty",
"label": "Stored reorder quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Site replenishment settings",
"detailRole": "information",
"isVisible": true,
"key": "SafetyStk",
"label": "Stored safety stock",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Site replenishment settings",
"detailRole": "information",
"isVisible": true,
"key": "MaxOnHand",
"label": "Stored maximum on hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "DfltWhseLoc",
"label": "Warehouse location code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "DfltPickBin",
"label": "Pick bin code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "DfltPutAwayBin",
"label": "Put-away bin code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "DfltPOUnit",
"label": "Site purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "DfltSOUnit",
"label": "Site sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "LastVendor",
"label": "Last supplier business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "LastCountDate",
"label": "Last count date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Bins and purchasing context",
"detailRole": "information",
"isVisible": true,
"key": "LastPurchaseDate",
"label": "Last purchase date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site metadata and scope",
"detailRole": "information",
"isVisible": true,
"key": "SiteName",
"label": "Current site name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site metadata and scope",
"detailRole": "information",
"isVisible": true,
"key": "SiteCompany",
"label": "Current site company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site metadata and scope",
"detailRole": "information",
"isVisible": true,
"key": "SiteContext",
"label": "Current site metadata context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site metadata and scope",
"detailRole": "information",
"isVisible": true,
"key": "QuantityScope",
"label": "Quantity record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "449650D7-EC25-5943-9637-7C9FF27E748B",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "08D49E80-66AF-5457-BB3C-8DEE698789E7",
"key": "CpnyID",
"type": "text"
},
{
"direction": "ascending",
"id": "D1F1F2FB-51D1-5382-9E32-EA471CE0B04A",
"key": "SiteID",
"type": "text"
},
{
"direction": "ascending",
"id": "6739E124-ABBF-5653-8180-FDC9DCBA0082",
"key": "StockKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "SiteID",
"systemImage": "building.2",
"title": "Site quantities",
"titleKey": "SiteDisplay"
}
],
"relations": [
{
"childDatasetID": "B8F6F91E-268C-59B4-A486-1BEAD320B2CD",
"childKey": "ProductKey",
"id": "AE95C831-43F8-5B21-A037-88C48F751D58",
"name": "Site quantities",
"parentDatasetID": "9730891D-9D25-5B8B-8176-E7E3B55E96D6",
"parentKey": "ProductKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Microsoft Dynamics SL",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-10T00:00:00Z",
"description": "UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.\n\nFor warehouse, purchasing, sales and product teams: choose a product class, find an item, inspect its barcode, units, native prices/costs, attributes and location defaults. Open Site quantities only when needed to inspect each site/company, on-hand and separately stored availability, allocations, customer/purchase orders, backorders, transit, bins and replenishment settings.\n\nWhy Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.\n\nPro: one SQL source, one Home button, two lists and one on-demand related button. 32 product and 31 site values in grouped Details, eight useful fields per card, Search and native class opening selection. Technical keys/context are hidden. Live reads cap each list at 1,000 rows with no automatic refresh; identity guards may scan beyond the cap. This is not a complete-company stock total or installed performance guarantee.\n\nMicrosoft source registrations identify Inventory, ItemSite, Site and ProductClass. Own pinned migration code binds ItemSite.InvtID to the product, uses StkUnit as the base unit, uses site QtyOnHand directly, and identifies product class and site location codes. AL logical keys do not certify installed SQL DDL, PK/FK, storage types or constraints. Inventory has no selected CpnyID; it is not invented. ItemSite keys are item+site, not an invented company triple. Duplicate, blank or oversized products reject the root read; duplicate/invalid site rows block only their own product. Every selected product and optional current-class context is revalidated before reading sites. No first-match or silent duplicate removal.\n\nQuantities remain separately named stored fields, not QtyOnHand-minus-allocations, available-to-promise, unit conversions, stock movements or calculated value. Zero, negative and NULL source values are not repaired or replaced. Purchase/sales units stay separate. StkBasePrc, LastCost and StdCost are distinct native values: source currency, price validity, tax, discount and valuation are unverified. No currency symbol, conversion or price-times-stock formula. Verify those settings before commercial use. TranStatusCode IN/DE has documented inactive/discontinued migration context; Status is a different raw field, not the same status or an access/eligibility flag. Other codes are not translated into guessed states.\n\nProduct class description and site name/company are optional current metadata, shown only with an unambiguous bounded identity; missing, duplicate or invalid metadata preserves the original product/site rows without fan-out. Current Site company stays separate from the original stock-row company; disagreements are displayed, never silently reassigned. Dates are source values, not UTC conversion or a transaction-period stock calculation.\n\nDocumentary and adapted synthetic/native Cifru DEMO validation only. No real Dynamics SL or SQL Server, installed owner/release/DDL/index/query plan, ERP permissions or iOS has been verified. The proposed dbo owner and business database must be checked at import/read test. Direct SQL does not inherit ERP/company ACL. Class/company/site filters and hidden keys are not access control; require an administrator-scoped SELECT-only reporting login, not migration db_owner/sysadmin. No source credentials, bank, audit/user or custom/future columns. Fictional DEMO screenshots are not a live ERP connection.\n\nUnofficial, not Microsoft endorsed. Dynamics SL is a distinct legacy installed-base product with extended support ending July 2028, not a current top-three or no-mobile claim.\n\nPrimary source registration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/codeunits/SLHybridWizard.Codeunit.al\nProduct fields: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLInventoryBuffer.Table.al\nSite quantity fields: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLItemSiteBuffer.Table.al\nBinding and unit context: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Items/SLItemMigrator.Codeunit.al",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "E7EBB891-7259-5798-B732-51F8F9637AB7",
"rootButtonCount": 1,
"summary": "Class selection, product attributes and native price/cost values, with on-demand site quantities, bins and replenishment context.",
"tags": [
"Dynamics SL",
"SQL Server",
"Products",
"Stock",
"Sites",
"Purchasing",
"Pro"
],
"title": "Product and per-site stock dossier — Pro"
}
}