Items, native quantities, list prices and kit parts — Pro
An item dossier with distinct units, on-demand quantity/site records, currency list prices and kit definitions.
UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. Selected Microsoft GP source registrations and GP-shaped AL declarations at commit e7ed235bc979c0283306e9639ff7cd22ce78341d are documentary evidence, not installed SQL DDL. No real GP/SQL Server/iOS test, installed physical types/keys/owner, release compatibility, authorization or performance is certified. Verify the company database, dbo objects, source data, completeness and import read test before business use.
For inventory, purchasing, sales support and managers: choose an item class and inspect an item dossier. Keep the purchasing, selling and current base units distinct; see original classification, stored inactive flag and tracking/valuation codes. Open native quantity records with current site addresses and contacts, original currency list prices, or stored kit components only when needed.
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 authorised SQL company source, one Home, four lists and three on-demand sub-buttons. Each SELECT explicitly projects only the selected fields; no SELECT *, source credentials, business rows, SQL writes, procedures or executable code are included. Live reads are capped at 1,000 rows without scheduled refresh. Caps and filters do not guarantee cheap queries or complete results. Verify indexing and Cifru query wrapping on your own source; SQLite logic tests are not SQL Server tests.
Quantity records preserve the full item + native RCRDTYPE + location identity. Code 1 with a blank location is the overall on-hand record used by Microsoft migration code; other record codes, including 2, are not guessed. Overall and site records must never be added together. QTYONHND is original current on-hand, not availability-to-promise, bin stock, historical stock, valuation or a delivery commitment. Keep zero, negative and NULL values. Base unit is current metadata context; verify the installed unit for each native record type before interpretation. Source reconciliation, missing history, changed unit equivalents and serial/lot overrides can affect GP stock; this package neither repairs nor reconstructs that history.
List prices retain their own CURNCYID and original LISTPRCE, not a customer quotation, tax-inclusive price or pricing-engine result. Base unit is context only, not a certified pricing unit. No currency conversion, sum across currencies, cost, stock valuation or Qty-times-price total is calculated. Quantities and monetary amounts are text to preserve tiny precision and signs; their local sorting/filtering is textual, not numeric aggregation. Currency IDs need not be ISO.
Kit definitions are IV00104, not the separate bill-of-materials or manufacturing revision modules. Keep the complete parent + component + component-unit identity and original sequence/quantity. Current component names are not historical snapshots; no recursive explosion, buildability, availability or conversion is inferred. CMPSERNM remains its raw stored flag because its full business meaning is unproven. Codes 1 inventory, 2 discontinued and 3 kit are evidenced; other item type codes stay raw. INACTIVE stays the original independent flag, not a migration-derived inactive state. Tracking and valuation methods remain native codes.
Missing or ambiguous class/unit/site/component metadata preserves the source item/record with an explicit state and empty enrichment, never a first-match name or invented unit. NULL or duplicate child identities block the entire selected child; its reason appears in the parent dossier. Invalid or duplicate item master identities block the entire root read; ask your administrator to inspect source integrity rather than treating an empty read as an empty catalog. Child queries revalidate the current parent and derive current unit context afresh. Complete business codes and leading zeros are retained, removing right padding only; source collation and installed uniqueness still require validation.
Use a separate least-privilege SELECT reporting login on permitted company data, not DYNAMICS, Business Central staging, sa, sysadmin or DYNGRP. GP application passwords are transformed; obtain an independently authorised SQL login. Cifru filtering is not access control and does not inherit ERP security. Real commercial/site data may be confidential. Screenshots must be native Cifru with entirely fictional DEMO rows, not a live GP connection. Unofficial and not endorsed by Microsoft.
Primary source registration: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/codeunits/GPCloudMigration.codeunit.al
Inventory manual: https://learn.microsoft.com/en-us/dynamics-gp/distribution/inventory
Stock limitations: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/incorrect-balance-item-stock-inquiry
Selected field definitions: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPIV00101.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00102.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00104.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00105.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV40201.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPIV40400.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPItemLocation.table.al
Screenshots
What this package creates
1 Home3 Details4 lists1 sources to map
- Home: Item dossier
- Details: Quantities
- Details: List prices
- Details: Kit parts
- Sub-button: Native quantity records
- Sub-button: Currency list prices
- Sub-button: Kit definitions
Sources are mapped locally and verified before applying.
Custom queriesPRO4 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(m.ITEMNMBR) AS ITEMNMBR, m.ITEMDESC AS ITEMDESC, m.ITMSHNAM AS ITMSHNAM, m.ITMGEDSC AS ITMGEDSC, m.ITMCLSCD AS ITMCLSCD, m.ITEMTYPE AS ITEMTYPE, m.ITMTRKOP AS ITMTRKOP, m.VCTNMTHD AS VCTNMTHD, m.UOMSCHDL AS UOMSCHDL, m.PRCHSUOM AS PRCHSUOM, m.SELNGUOM AS SELNGUOM, m.INACTIVE AS INACTIVE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(cl.ITMCLSDC) END FROM dbo.IV40400 cl WHERE (RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000)))))) AS ClassDescription, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, CASE m.ITEMTYPE WHEN 1 THEN N'Inventory (code 1)' WHEN 2 THEN N'Discontinued (code 2)' WHEN 3 THEN N'Kit (code 3)' ELSE N'Other / unknown native code' END AS ItemTypeName, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40400 cl WHERE (RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000)))))) AS ClassState, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00102 q WHERE (RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (q.ITEMNMBR IS NOT NULL AND q.RCRDTYPE IS NOT NULL AND q.LOCNCODE IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00102 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))))) AND x.RCRDTYPE = q.RCRDTYPE AND (RTRIM(CAST(x.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS QuantityState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00105 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (p.ITEMNMBR IS NOT NULL AND p.CURNCYID IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00105 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CURNCYID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CURNCYID AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS PriceState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00104 k WHERE (RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (k.ITEMNMBR IS NOT NULL AND k.CMPTITNM IS NOT NULL AND k.CMPITUOM IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00104 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPTITNM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPITUOM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPITUOM AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS KitState FROM dbo.IV00101 m WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00101 bad WHERE bad.ITEMNMBR IS NULL OR RTRIM(bad.ITEMNMBR)=N'' OR (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.ITEMNMBR AS nvarchar(4000))))))<>1)
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQueryWITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(q.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(q.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(q.RCRDTYPE AS nvarchar(100)))),N':',RTRIM(CAST(q.RCRDTYPE AS nvarchar(100))),LEN(RTRIM(CAST(q.LOCNCODE AS nvarchar(100)))),N':',RTRIM(CAST(q.LOCNCODE AS nvarchar(100)))) AS QuantityKey, RTRIM(q.ITEMNMBR) AS ITEMNMBR, RTRIM(q.LOCNCODE) AS LOCNCODE, q.RCRDTYPE AS RCRDTYPE, CAST(q.QTYONHND AS nvarchar(100)) AS QTYONHND, CASE WHEN q.RCRDTYPE=1 AND RTRIM(q.LOCNCODE)=N'' THEN N'Overall native record (1)' ELSE CONCAT(N'Native record ',q.RCRDTYPE,N' — ',RTRIM(q.LOCNCODE)) END AS QuantityScope, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS SiteState, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.LOCNDSCR) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS LOCNDSCR, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ADDRESS1) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ADDRESS1, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ADDRESS2) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ADDRESS2, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.CITY) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS CITY, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.STATE) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS STATE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ZIPCODE) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ZIPCODE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.PHONE1) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS PHONE1, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.PHONE2) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS PHONE2, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.FAXNUMBR) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS FAXNUMBR FROM dbo.IV00102 q JOIN parent m ON (RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00102 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.RCRDTYPE IS NOT NULL AND b.LOCNCODE IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00102 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND x.RCRDTYPE = b.RCRDTYPE AND (RTRIM(CAST(x.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.LOCNCODE AS nvarchar(4000)))))) <> 1))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQueryWITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(p.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) AS PriceKey, RTRIM(p.ITEMNMBR) AS ITEMNMBR, RTRIM(p.CURNCYID) AS CURNCYID, CAST(p.LISTPRCE AS nvarchar(100)) AS LISTPRCE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, N'Source list price; not a customer quote' AS PriceScope FROM dbo.IV00105 p JOIN parent m ON (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00105 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.CURNCYID IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00105 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CURNCYID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CURNCYID AS nvarchar(4000)))))) <> 1))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.3.sqlQueryWITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(k.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(k.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(k.CMPTITNM AS nvarchar(100)))),N':',RTRIM(CAST(k.CMPTITNM AS nvarchar(100))),LEN(RTRIM(CAST(k.CMPITUOM AS nvarchar(100)))),N':',RTRIM(CAST(k.CMPITUOM AS nvarchar(100)))) AS KitKey, RTRIM(k.ITEMNMBR) AS ITEMNMBR, k.SEQNUMBR AS SEQNUMBR, RTRIM(k.CMPTITNM) AS CMPTITNM, RTRIM(k.CMPITUOM) AS CMPITUOM, CAST(k.CMPITQTY AS nvarchar(100)) AS CMPITQTY, k.CMPSERNM AS CMPSERNM, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(i.ITEMDESC) END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentDescription, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(i.ITMCLSCD) END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentClass, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentState, m.ITEMTYPE AS ParentType, CASE WHEN m.ITEMTYPE=3 THEN N'Kit definition; not a manufacturing BOM' ELSE N'Stored definition under a parent not identified as kit code 3' END AS KitScope FROM dbo.IV00104 k JOIN parent m ON (RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00104 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.CMPTITNM IS NOT NULL AND b.CMPITUOM IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00104 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPTITNM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CMPTITNM AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPITUOM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CMPITUOM AS nvarchar(4000)))))) <> 1))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Dynamics GP — authorised SQL company database",
"id": "168E564E-E273-5F75-8DCB-9D4D6C4AF52E",
"kind": "sqlServer",
"requiredObjects": [
"dbo.IV00101",
"dbo.IV00102",
"dbo.IV00104",
"dbo.IV00105",
"dbo.IV40201",
"dbo.IV40400",
"dbo.IV40700"
],
"requiresCustomSQL": false
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "750AEDEF-3129-58FD-82B1-2382341E685B",
"key": "ITEMDESC",
"type": "text"
},
{
"direction": "ascending",
"id": "9F453B3D-EC63-57F8-BECE-C415F69F8C1A",
"key": "ITEMNMBR",
"type": "text"
}
],
"id": "1F9CAD85-BEFC-5931-8155-DE9E7C646CC6",
"mappings": [
{
"commonFieldKey": "",
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMDESC",
"label": "Item description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMDESC",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITMSHNAM",
"label": "Short description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITMSHNAM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITMGEDSC",
"label": "Generic description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITMGEDSC",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITMCLSCD",
"label": "Item class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITMCLSCD",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ClassDescription",
"label": "Current item class description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassDescription",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMTYPE",
"label": "Native item type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMTYPE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemTypeName",
"label": "Documented item type context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ItemTypeName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ITMTRKOP",
"label": "Native tracking option code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITMTRKOP",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VCTNMTHD",
"label": "Native valuation method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VCTNMTHD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UOMSCHDL",
"label": "Unit schedule code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UOMSCHDL",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BaseUnit",
"label": "Current base unit of measure",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BaseUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PRCHSUOM",
"label": "Purchasing unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRCHSUOM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SELNGUOM",
"label": "Selling unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SELNGUOM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "INACTIVE",
"label": "Stored inactive flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "INACTIVE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ClassState",
"label": "Class metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UnitState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QuantityState",
"label": "Quantity record availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QuantityState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PriceState",
"label": "List price availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PriceState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "KitState",
"label": "Kit definition availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "KitState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Item dossier",
"primaryKey": "ITEMNMBR",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ITEMNMBR",
"ITEMDESC",
"ITMSHNAM",
"ITMGEDSC",
"ITMCLSCD",
"ClassDescription",
"ITEMTYPE",
"ItemTypeName",
"ITMTRKOP",
"VCTNMTHD",
"UOMSCHDL",
"BaseUnit",
"PRCHSUOM",
"SELNGUOM",
"INACTIVE",
"ClassState",
"UnitState",
"QuantityState",
"PriceState",
"KitState"
],
"sourceID": "168E564E-E273-5F75-8DCB-9D4D6C4AF52E",
"sqlQuery": "SELECT RTRIM(m.ITEMNMBR) AS ITEMNMBR, m.ITEMDESC AS ITEMDESC, m.ITMSHNAM AS ITMSHNAM, m.ITMGEDSC AS ITMGEDSC, m.ITMCLSCD AS ITMCLSCD, m.ITEMTYPE AS ITEMTYPE, m.ITMTRKOP AS ITMTRKOP, m.VCTNMTHD AS VCTNMTHD, m.UOMSCHDL AS UOMSCHDL, m.PRCHSUOM AS PRCHSUOM, m.SELNGUOM AS SELNGUOM, m.INACTIVE AS INACTIVE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(cl.ITMCLSDC) END FROM dbo.IV40400 cl WHERE (RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000)))))) AS ClassDescription, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, CASE m.ITEMTYPE WHEN 1 THEN N'Inventory (code 1)' WHEN 2 THEN N'Discontinued (code 2)' WHEN 3 THEN N'Kit (code 3)' ELSE N'Other / unknown native code' END AS ItemTypeName, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40400 cl WHERE (RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(cl.ITMCLSCD AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITMCLSCD AS nvarchar(4000)))))) AS ClassState, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00102 q WHERE (RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (q.ITEMNMBR IS NOT NULL AND q.RCRDTYPE IS NOT NULL AND q.LOCNCODE IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00102 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))))) AND x.RCRDTYPE = q.RCRDTYPE AND (RTRIM(CAST(x.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS QuantityState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00105 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (p.ITEMNMBR IS NOT NULL AND p.CURNCYID IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00105 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CURNCYID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CURNCYID AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS PriceState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.IV00104 k WHERE (RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (k.ITEMNMBR IS NOT NULL AND k.CMPTITNM IS NOT NULL AND k.CMPITUOM IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00104 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPTITNM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPITUOM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPITUOM AS nvarchar(4000)))))) <> 1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate record identity' END AS KitState FROM dbo.IV00101 m WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00101 bad WHERE bad.ITEMNMBR IS NULL OR RTRIM(bad.ITEMNMBR)=N'' OR (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.ITEMNMBR AS nvarchar(4000))))))<>1)",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "B29DF7FD-FBE3-5632-A768-A92B13C999EF",
"key": "RCRDTYPE",
"type": "text"
},
{
"direction": "ascending",
"id": "C2799507-3CF2-59EC-A69C-C566DF3736C8",
"key": "LOCNCODE",
"type": "text"
}
],
"id": "37D6F95A-C80B-5C7A-8161-778C9BF80F3A",
"mappings": [
{
"commonFieldKey": "",
"key": "QuantityKey",
"label": "Hidden complete record identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QuantityKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LOCNCODE",
"label": "Native location code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LOCNCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "RCRDTYPE",
"label": "Native record type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "RCRDTYPE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityScope",
"label": "Record scope (not a stock total)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QuantityScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QTYONHND",
"label": "Original on-hand quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QTYONHND",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "BaseUnit",
"label": "Current item base-unit context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BaseUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UnitState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LOCNDSCR",
"label": "Current site description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LOCNDSCR",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ADDRESS1",
"label": "Site address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS2",
"label": "Site address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CITY",
"label": "Site city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "STATE",
"label": "Site state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STATE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZIPCODE",
"label": "Site postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIPCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE1",
"label": "Site phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE2",
"label": "Site phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FAXNUMBR",
"label": "Site fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FAXNUMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SiteState",
"label": "Site metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SiteState",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
}
],
"maxRows": 1000,
"name": "Native quantity records",
"primaryKey": "QuantityKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ITEMNMBR",
"id": "897B582A-7070-5E30-A312-FAD7DFC9D6A5",
"name": "item",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ITEMNMBR",
"LOCNCODE",
"RCRDTYPE",
"QuantityScope",
"QTYONHND",
"BaseUnit",
"UnitState",
"LOCNDSCR",
"ADDRESS1",
"ADDRESS2",
"CITY",
"STATE",
"ZIPCODE",
"PHONE1",
"PHONE2",
"FAXNUMBR",
"SiteState"
],
"sourceID": "168E564E-E273-5F75-8DCB-9D4D6C4AF52E",
"sqlQuery": "WITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(q.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(q.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(q.RCRDTYPE AS nvarchar(100)))),N':',RTRIM(CAST(q.RCRDTYPE AS nvarchar(100))),LEN(RTRIM(CAST(q.LOCNCODE AS nvarchar(100)))),N':',RTRIM(CAST(q.LOCNCODE AS nvarchar(100)))) AS QuantityKey, RTRIM(q.ITEMNMBR) AS ITEMNMBR, RTRIM(q.LOCNCODE) AS LOCNCODE, q.RCRDTYPE AS RCRDTYPE, CAST(q.QTYONHND AS nvarchar(100)) AS QTYONHND, CASE WHEN q.RCRDTYPE=1 AND RTRIM(q.LOCNCODE)=N'' THEN N'Overall native record (1)' ELSE CONCAT(N'Native record ',q.RCRDTYPE,N' — ',RTRIM(q.LOCNCODE)) END AS QuantityScope, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS SiteState, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.LOCNDSCR) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS LOCNDSCR, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ADDRESS1) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ADDRESS1, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ADDRESS2) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ADDRESS2, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.CITY) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS CITY, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.STATE) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS STATE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.ZIPCODE) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS ZIPCODE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.PHONE1) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS PHONE1, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.PHONE2) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS PHONE2, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.FAXNUMBR) END FROM dbo.IV40700 s WHERE (RTRIM(CAST(s.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(q.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(s.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(q.LOCNCODE AS nvarchar(4000)))))) AS FAXNUMBR FROM dbo.IV00102 q JOIN parent m ON (RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(q.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00102 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.RCRDTYPE IS NOT NULL AND b.LOCNCODE IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00102 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND x.RCRDTYPE = b.RCRDTYPE AND (RTRIM(CAST(x.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.LOCNCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.LOCNCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.LOCNCODE AS nvarchar(4000)))))) <> 1))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "6F76EDFC-D8C4-5AA1-B6E3-D678142BF2B1",
"key": "CURNCYID",
"type": "text"
}
],
"id": "FCE6EBAB-8D0C-5558-BBE3-727E2009A6BE",
"mappings": [
{
"commonFieldKey": "",
"key": "PriceKey",
"label": "Hidden complete record identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PriceKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CURNCYID",
"label": "List price currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CURNCYID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "LISTPRCE",
"label": "Original unit list price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LISTPRCE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "BaseUnit",
"label": "Current base unit (context only)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BaseUnit",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PriceScope",
"label": "List price context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PriceScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UnitState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Currency list prices",
"primaryKey": "PriceKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ITEMNMBR",
"id": "897B582A-7070-5E30-A312-FAD7DFC9D6A5",
"name": "item",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ITEMNMBR",
"CURNCYID",
"LISTPRCE",
"BaseUnit",
"PriceScope",
"UnitState"
],
"sourceID": "168E564E-E273-5F75-8DCB-9D4D6C4AF52E",
"sqlQuery": "WITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(p.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) AS PriceKey, RTRIM(p.ITEMNMBR) AS ITEMNMBR, RTRIM(p.CURNCYID) AS CURNCYID, CAST(p.LISTPRCE AS nvarchar(100)) AS LISTPRCE, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(u.BASEUOFM) END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS BaseUnit, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV40201 u WHERE (RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(u.UOMSCHDL AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.UOMSCHDL AS nvarchar(4000)))))) AS UnitState, N'Source list price; not a customer quote' AS PriceScope FROM dbo.IV00105 p JOIN parent m ON (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00105 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.CURNCYID IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00105 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CURNCYID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CURNCYID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CURNCYID AS nvarchar(4000)))))) <> 1))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "D7CC5C09-7D8C-533B-A3A1-2B8974B528B7",
"key": "SEQNUMBR",
"type": "text"
},
{
"direction": "ascending",
"id": "F3E0A0C4-B0FB-57F0-A71A-ABCA6A0202F1",
"key": "CMPTITNM",
"type": "text"
},
{
"direction": "ascending",
"id": "1D9F62DD-BA2C-5589-9FDA-2415BBB1DC58",
"key": "CMPITUOM",
"type": "text"
}
],
"id": "3EB125F5-7479-53B0-9A84-2E80A75CDC9D",
"mappings": [
{
"commonFieldKey": "",
"key": "KitKey",
"label": "Hidden complete record identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "KitKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMNMBR",
"label": "Parent item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SEQNUMBR",
"label": "Stored sequence number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SEQNUMBR",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CMPTITNM",
"label": "Component item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CMPTITNM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CMPITUOM",
"label": "Component unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CMPITUOM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CMPITQTY",
"label": "Original quantity per kit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CMPITQTY",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CMPSERNM",
"label": "Native CMPSERNM flag",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CMPSERNM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ComponentDescription",
"label": "Current component description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ComponentDescription",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ComponentClass",
"label": "Current component class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ComponentClass",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ComponentState",
"label": "Component master availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ComponentState",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ParentType",
"label": "Current parent native type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentType",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "KitScope",
"label": "Definition context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "KitScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Kit definitions",
"primaryKey": "KitKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ITEMNMBR",
"id": "897B582A-7070-5E30-A312-FAD7DFC9D6A5",
"name": "item",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ITEMNMBR",
"SEQNUMBR",
"CMPTITNM",
"CMPITUOM",
"CMPITQTY",
"CMPSERNM",
"ComponentDescription",
"ComponentClass",
"ComponentState",
"ParentType",
"KitScope"
],
"sourceID": "168E564E-E273-5F75-8DCB-9D4D6C4AF52E",
"sqlQuery": "WITH parent AS (SELECT p.ITEMNMBR,p.UOMSCHDL,p.ITEMTYPE FROM dbo.IV00101 p WHERE (RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:item AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:item AS nvarchar(4000))))) AND (SELECT COUNT(1) FROM dbo.IV00101 other WHERE (RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.ITEMNMBR AS nvarchar(4000))))))=1) SELECT CONCAT(LEN(RTRIM(CAST(k.ITEMNMBR AS nvarchar(100)))),N':',RTRIM(CAST(k.ITEMNMBR AS nvarchar(100))),LEN(RTRIM(CAST(k.CMPTITNM AS nvarchar(100)))),N':',RTRIM(CAST(k.CMPTITNM AS nvarchar(100))),LEN(RTRIM(CAST(k.CMPITUOM AS nvarchar(100)))),N':',RTRIM(CAST(k.CMPITUOM AS nvarchar(100)))) AS KitKey, RTRIM(k.ITEMNMBR) AS ITEMNMBR, k.SEQNUMBR AS SEQNUMBR, RTRIM(k.CMPTITNM) AS CMPTITNM, RTRIM(k.CMPITUOM) AS CMPITUOM, CAST(k.CMPITQTY AS nvarchar(100)) AS CMPITQTY, k.CMPSERNM AS CMPSERNM, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(i.ITEMDESC) END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentDescription, (SELECT CASE WHEN COUNT(1)=1 THEN MAX(i.ITMCLSCD) END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentClass, (SELECT CASE WHEN COUNT(1)=1 THEN N'Unique current metadata' WHEN COUNT(1)=0 THEN N'Missing current metadata' ELSE N'Ambiguous current metadata' END FROM dbo.IV00101 i WHERE (RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(k.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(i.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(k.CMPTITNM AS nvarchar(4000)))))) AS ComponentState, m.ITEMTYPE AS ParentType, CASE WHEN m.ITEMTYPE=3 THEN N'Kit definition; not a manufacturing BOM' ELSE N'Stored definition under a parent not identified as kit code 3' END AS KitScope FROM dbo.IV00104 k JOIN parent m ON (RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(k.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.IV00101',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00102',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00104',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40201',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40400',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.IV40700',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.IV00104 b WHERE (RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ITEMNMBR AS nvarchar(4000))))) AND (NOT (b.ITEMNMBR IS NOT NULL AND b.CMPTITNM IS NOT NULL AND b.CMPITUOM IS NOT NULL) OR (SELECT COUNT(1) FROM dbo.IV00104 x WHERE (RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.ITEMNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.ITEMNMBR AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CMPTITNM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPTITNM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CMPTITNM AS nvarchar(4000))))) AND (RTRIM(CAST(x.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(b.CMPITUOM AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.CMPITUOM AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(b.CMPITUOM AS nvarchar(4000)))))) <> 1))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "58ADCD6F-082E-5BE5-99CB-A7529D899B33",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "D4D04F92-1BB9-5F8C-A601-8C68EE73D562",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "37D6F95A-C80B-5C7A-8161-778C9BF80F3A",
"title": "Quantities",
"urlKey": ""
},
{
"id": "D28561D8-F6FD-5332-B716-312B7C8C1480",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "AF055AED-D7EC-5A95-9E5C-F1D556E83BC0",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "FCE6EBAB-8D0C-5558-BBE3-727E2009A6BE",
"title": "List prices",
"urlKey": ""
},
{
"id": "A6DB8706-CAB3-5C7E-AAF6-11A5C689AD3E",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "ED6DA9E5-07FE-5CFB-A62F-1D9FD852A232",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "3EB125F5-7479-53B0-9A84-2E80A75CDC9D",
"title": "Kit parts",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ITMCLSCD",
"label": "Class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Base UOM context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PRCHSUOM",
"label": "Purchase UOM",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SELNGUOM",
"label": "Sales UOM",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ItemTypeName",
"label": "Item type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "INACTIVE",
"label": "Inactive (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "1F9CAD85-BEFC-5931-8155-DE9E7C646CC6",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ITEMDESC",
"label": "Item description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ITMSHNAM",
"label": "Short description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ITMGEDSC",
"label": "Generic description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ITMCLSCD",
"label": "Item class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ClassDescription",
"label": "Current item class description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ClassState",
"label": "Class metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item settings",
"detailRole": "information",
"isVisible": true,
"key": "ITEMTYPE",
"label": "Native item type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item settings",
"detailRole": "information",
"isVisible": true,
"key": "ItemTypeName",
"label": "Documented item type context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item settings",
"detailRole": "information",
"isVisible": true,
"key": "ITMTRKOP",
"label": "Native tracking option code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item settings",
"detailRole": "information",
"isVisible": true,
"key": "VCTNMTHD",
"label": "Native valuation method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item settings",
"detailRole": "information",
"isVisible": true,
"key": "INACTIVE",
"label": "Stored inactive flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units — distinct roles",
"detailRole": "information",
"isVisible": true,
"key": "UOMSCHDL",
"label": "Unit schedule code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units — distinct roles",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Current base unit of measure",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units — distinct roles",
"detailRole": "information",
"isVisible": true,
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units — distinct roles",
"detailRole": "information",
"isVisible": true,
"key": "PRCHSUOM",
"label": "Purchasing unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Units — distinct roles",
"detailRole": "information",
"isVisible": true,
"key": "SELNGUOM",
"label": "Selling unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "On-demand routes",
"detailRole": "information",
"isVisible": true,
"key": "QuantityState",
"label": "Quantity record availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "On-demand routes",
"detailRole": "information",
"isVisible": true,
"key": "PriceState",
"label": "List price availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "On-demand routes",
"detailRole": "information",
"isVisible": true,
"key": "KitState",
"label": "Kit definition availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "40F3C974-3C50-52BC-B585-2368762D5522",
"openFilters": [
{
"id": "344AF859-5413-5AF0-83CD-C566C6646C54",
"includeAllOption": true,
"key": "ITMCLSCD",
"title": "Item class",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "ascending",
"id": "750AEDEF-3129-58FD-82B1-2382341E685B",
"key": "ITEMDESC",
"type": "text"
},
{
"direction": "ascending",
"id": "9F453B3D-EC63-57F8-BECE-C415F69F8C1A",
"key": "ITEMNMBR",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ITEMNMBR",
"systemImage": "shippingbox",
"title": "Item dossier",
"titleKey": "ITEMDESC"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LOCNCODE",
"label": "Location",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "RCRDTYPE",
"label": "Record code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QTYONHND",
"label": "On hand",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Base UOM context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LOCNDSCR",
"label": "Current site",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SiteState",
"label": "Site metadata",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "37D6F95A-C80B-5C7A-8161-778C9BF80F3A",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "LOCNCODE",
"label": "Native location code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "RCRDTYPE",
"label": "Native record type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "QuantityScope",
"label": "Record scope (not a stock total)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "QTYONHND",
"label": "Original on-hand quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Current item base-unit context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Source quantity record",
"detailRole": "information",
"isVisible": true,
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site identity",
"detailRole": "information",
"isVisible": true,
"key": "LOCNDSCR",
"label": "Current site description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site identity",
"detailRole": "information",
"isVisible": true,
"key": "SiteState",
"label": "Site metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Site address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS2",
"label": "Site address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "Site city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "Site state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Site postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE1",
"label": "Site phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE2",
"label": "Site phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current site address and contact",
"detailRole": "information",
"isVisible": true,
"key": "FAXNUMBR",
"label": "Site fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "65EE9B5F-E5FC-5F51-BCC9-A262EC7D4051",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "B29DF7FD-FBE3-5632-A768-A92B13C999EF",
"key": "RCRDTYPE",
"type": "text"
},
{
"direction": "ascending",
"id": "C2799507-3CF2-59EC-A69C-C566DF3736C8",
"key": "LOCNCODE",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "LOCNCODE",
"systemImage": "shippingbox",
"title": "Native quantity records",
"titleKey": "QuantityScope"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LISTPRCE",
"label": "Unit list price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Base UOM context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PriceScope",
"label": "Price context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "FCE6EBAB-8D0C-5558-BBE3-727E2009A6BE",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "List price currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "LISTPRCE",
"label": "Original unit list price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "BaseUnit",
"label": "Current base unit (context only)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "PriceScope",
"label": "List price context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original currency list price",
"detailRole": "information",
"isVisible": true,
"key": "UnitState",
"label": "Unit metadata availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "A040B78A-205C-5A66-BC1F-623809175CCE",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "6F76EDFC-D8C4-5AA1-B6E3-D678142BF2B1",
"key": "CURNCYID",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ITEMNMBR",
"systemImage": "shippingbox",
"title": "Currency list prices",
"titleKey": "CURNCYID"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CMPTITNM",
"label": "Component",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CMPITUOM",
"label": "Component UOM",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CMPITQTY",
"label": "Quantity per",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SEQNUMBR",
"label": "Sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ParentType",
"label": "Parent type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ComponentState",
"label": "Component metadata",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "3EB125F5-7479-53B0-9A84-2E80A75CDC9D",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Kit parent context",
"detailRole": "information",
"isVisible": true,
"key": "ITEMNMBR",
"label": "Parent item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Kit parent context",
"detailRole": "information",
"isVisible": true,
"key": "ParentType",
"label": "Current parent native type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Kit parent context",
"detailRole": "information",
"isVisible": true,
"key": "KitScope",
"label": "Definition context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored component definition",
"detailRole": "information",
"isVisible": true,
"key": "CMPTITNM",
"label": "Component item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored component definition",
"detailRole": "information",
"isVisible": true,
"key": "SEQNUMBR",
"label": "Stored sequence number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored component definition",
"detailRole": "information",
"isVisible": true,
"key": "CMPITUOM",
"label": "Component unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored component definition",
"detailRole": "information",
"isVisible": true,
"key": "CMPITQTY",
"label": "Original quantity per kit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored component definition",
"detailRole": "information",
"isVisible": true,
"key": "CMPSERNM",
"label": "Native CMPSERNM flag",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current component master",
"detailRole": "information",
"isVisible": true,
"key": "ComponentDescription",
"label": "Current component description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current component master",
"detailRole": "information",
"isVisible": true,
"key": "ComponentClass",
"label": "Current component class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current component master",
"detailRole": "information",
"isVisible": true,
"key": "ComponentState",
"label": "Component master availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "81648AAA-006E-5DEB-A07F-0551E36C2705",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "D7CC5C09-7D8C-533B-A3A1-2B8974B528B7",
"key": "SEQNUMBR",
"type": "text"
},
{
"direction": "ascending",
"id": "F3E0A0C4-B0FB-57F0-A71A-ABCA6A0202F1",
"key": "CMPTITNM",
"type": "text"
},
{
"direction": "ascending",
"id": "1D9F62DD-BA2C-5589-9FDA-2415BBB1DC58",
"key": "CMPITUOM",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CMPTITNM",
"systemImage": "shippingbox",
"title": "Kit definitions",
"titleKey": "ComponentDescription"
}
],
"relations": [
{
"childDatasetID": "37D6F95A-C80B-5C7A-8161-778C9BF80F3A",
"childKey": "ITEMNMBR",
"id": "D4D04F92-1BB9-5F8C-A601-8C68EE73D562",
"name": "Quantities",
"parentDatasetID": "1F9CAD85-BEFC-5931-8155-DE9E7C646CC6",
"parentKey": "ITEMNMBR"
},
{
"childDatasetID": "FCE6EBAB-8D0C-5558-BBE3-727E2009A6BE",
"childKey": "ITEMNMBR",
"id": "AF055AED-D7EC-5A95-9E5C-F1D556E83BC0",
"name": "List prices",
"parentDatasetID": "1F9CAD85-BEFC-5931-8155-DE9E7C646CC6",
"parentKey": "ITEMNMBR"
},
{
"childDatasetID": "3EB125F5-7479-53B0-9A84-2E80A75CDC9D",
"childKey": "ITEMNMBR",
"id": "ED6DA9E5-07FE-5CFB-A62F-1D9FD852A232",
"name": "Kit parts",
"parentDatasetID": "1F9CAD85-BEFC-5931-8155-DE9E7C646CC6",
"parentKey": "ITEMNMBR"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Microsoft Dynamics GP",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-09T00:00:00Z",
"description": "UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. Selected Microsoft GP source registrations and GP-shaped AL declarations at commit e7ed235bc979c0283306e9639ff7cd22ce78341d are documentary evidence, not installed SQL DDL. No real GP/SQL Server/iOS test, installed physical types/keys/owner, release compatibility, authorization or performance is certified. Verify the company database, dbo objects, source data, completeness and import read test before business use.\n\nFor inventory, purchasing, sales support and managers: choose an item class and inspect an item dossier. Keep the purchasing, selling and current base units distinct; see original classification, stored inactive flag and tracking/valuation codes. Open native quantity records with current site addresses and contacts, original currency list prices, or stored kit components only when needed.\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 authorised SQL company source, one Home, four lists and three on-demand sub-buttons. Each SELECT explicitly projects only the selected fields; no SELECT *, source credentials, business rows, SQL writes, procedures or executable code are included. Live reads are capped at 1,000 rows without scheduled refresh. Caps and filters do not guarantee cheap queries or complete results. Verify indexing and Cifru query wrapping on your own source; SQLite logic tests are not SQL Server tests.\n\nQuantity records preserve the full item + native RCRDTYPE + location identity. Code 1 with a blank location is the overall on-hand record used by Microsoft migration code; other record codes, including 2, are not guessed. Overall and site records must never be added together. QTYONHND is original current on-hand, not availability-to-promise, bin stock, historical stock, valuation or a delivery commitment. Keep zero, negative and NULL values. Base unit is current metadata context; verify the installed unit for each native record type before interpretation. Source reconciliation, missing history, changed unit equivalents and serial/lot overrides can affect GP stock; this package neither repairs nor reconstructs that history.\n\nList prices retain their own CURNCYID and original LISTPRCE, not a customer quotation, tax-inclusive price or pricing-engine result. Base unit is context only, not a certified pricing unit. No currency conversion, sum across currencies, cost, stock valuation or Qty-times-price total is calculated. Quantities and monetary amounts are text to preserve tiny precision and signs; their local sorting/filtering is textual, not numeric aggregation. Currency IDs need not be ISO.\n\nKit definitions are IV00104, not the separate bill-of-materials or manufacturing revision modules. Keep the complete parent + component + component-unit identity and original sequence/quantity. Current component names are not historical snapshots; no recursive explosion, buildability, availability or conversion is inferred. CMPSERNM remains its raw stored flag because its full business meaning is unproven. Codes 1 inventory, 2 discontinued and 3 kit are evidenced; other item type codes stay raw. INACTIVE stays the original independent flag, not a migration-derived inactive state. Tracking and valuation methods remain native codes.\n\nMissing or ambiguous class/unit/site/component metadata preserves the source item/record with an explicit state and empty enrichment, never a first-match name or invented unit. NULL or duplicate child identities block the entire selected child; its reason appears in the parent dossier. Invalid or duplicate item master identities block the entire root read; ask your administrator to inspect source integrity rather than treating an empty read as an empty catalog. Child queries revalidate the current parent and derive current unit context afresh. Complete business codes and leading zeros are retained, removing right padding only; source collation and installed uniqueness still require validation.\n\nUse a separate least-privilege SELECT reporting login on permitted company data, not DYNAMICS, Business Central staging, sa, sysadmin or DYNGRP. GP application passwords are transformed; obtain an independently authorised SQL login. Cifru filtering is not access control and does not inherit ERP security. Real commercial/site data may be confidential. Screenshots must be native Cifru with entirely fictional DEMO rows, not a live GP connection. Unofficial and not endorsed by Microsoft.\n\nPrimary source registration: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/codeunits/GPCloudMigration.codeunit.al\nInventory manual: https://learn.microsoft.com/en-us/dynamics-gp/distribution/inventory\nStock limitations: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/incorrect-balance-item-stock-inquiry\nSelected field definitions: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPIV00101.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00102.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00104.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV00105.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPIV40201.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPIV40400.Table.al https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Items/GPItemLocation.table.al",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "BBFEC41D-0268-5CA7-80BC-A8959808B9AA",
"rootButtonCount": 1,
"summary": "An item dossier with distinct units, on-demand quantity/site records, currency list prices and kit definitions.",
"tags": [
"Dynamics GP",
"SQL Server",
"Inventory",
"Products",
"Sites",
"Purchasing",
"Kits",
"Pro"
],
"title": "Items, native quantities, list prices and kit parts — Pro"
}
}