Current payable positions, vendor and address book — Pro
Stored functional unapplied amounts, due dates and native document groups, with lazy current vendor and address dossiers.
UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. For accounts payable, purchasing, supplier service and managers: choose a vendor or document group, inspect a current payable position with its stored unapplied amount, company functional currency setting, document currency, due date, void flag, terms and commercial references. Open the current vendor dossier and its address book on demand. Older documents remain available: there is no automatic opening-date cutoff.
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, three explicit SELECT lists, one Home and two lazy buttons (position to vendor, vendor to address book). Each read is capped at 1,000 rows; no scheduled refresh. Optional opening filters precede the cap. A filter/cap does not prove completeness or cheap execution. No SELECT *, credentials, cached business rows or executable code in this export.
Own source registrations and selected AL staging definitions are pinned to Microsoft commit e7ed235bc979c0283306e9639ff7cd22ce78341d. AL fields/types/logical keys are not installed SQL DDL, physical PK/FK, constraints, collation, permissions or performance proof. Run the import read test and verify actual company objects, GP version, SQL types/keys and financial interpretations against known transactions. No real GP, SQL Server or iOS source test is claimed.
PM20000.CURTRXAM is the stored unapplied amount in Functional view (Microsoft KB3208146). CURNCYID is the document currency, NOT the currency of that amount. Current vendor default is separate again. MC40000.FUNLCURR is shown only when the entire setting has exactly one nonblank row; zero, duplicate (even identical), NULL or blank settings show the reason and no invented currency. The MAX in the one-pass setting aggregate is never accepted as a first-match/dedup substitute when the row count differs from one. No ISO, exchange rate or conversion is inferred.
PM Open table membership is not a full unpaid balance, posting/payment proof or historical/as-of aging report. Fully applied rows may remain Open during maintenance issues, and applied documents may move to PM30200 history. Keep zero, negative, NULL and tiny stored amounts, independent void state and all document types. Do not copy migration-only amount/type filters, reconstruct original amounts or applied sums, invert signs or remove zero rows. Amount text is lossless; local sorting is textual, not numeric aggregation.
Only native PM Open groups are labelled from KB852208: debit group codes 1-3 and unapplied credit group codes 4-5. Individual Invoice/Charge/Return names and type 6 are not inferred from the historical table, BC migration grouping, PM10300 computer-check context, RM or SOP. Other stored codes remain explicitly unknown. POSTEDDT remains full source ISO text, not a silently truncated Date or a certified timezone/event. PORDNMBR and PONUMBER are separate native reference fields, not a merged or proven order link.
Position identity contains full DOCTYPE plus VCHRNMBR, not vendor invoice DOCNUMBR alone. Invalid/duplicate identities block the root read. Vendor name is available only from a unique current master; missing/ambiguous metadata preserves the position with a state, without fanout or first match. Lazy routes revalidate the exact position and its NULL-aware vendor/document/currency binding. A removed/changed/ambiguous parent blocks stale routes. Address identity contains vendor and address code; an invalid/duplicate selected address identity blocks that whole child. Current master/address are not historical voucher snapshots or proven remit-to/payment eligibility.
Current master data uses its own documented 35-field subset; the 10-field address subset does not claim email/country/extensions. No personal tax IDs, bank/checkbook, 1099, technical audit users or internal row IDs requested. Use a separately authorised least-privilege SELECT reporting login on allowed company data, not DYNAMICS, BC staging, sa/sysadmin/DYNGRP. GP application passwords are transformed; filters are not access control and do not inherit GP permissions. Commercial/contact information can be confidential.
Screenshots use native Cifru with wholly fictional DEMO source-shaped rows and synthetic transport, not live GP/SQL Server. Unofficial, not endorsed by Microsoft. Support maintenance SQL or Check Links is not executed.
Selected source evidence: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPPM20000.Table.al
Amount semantics: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/a-fully-applied-document-open-status
Document groups: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/fail-to-print-checks-in-payables-management
Lifecycle: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/how-manual-payments-credit-memos-appear
Vendor: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Vendors/GPPM00200.Table.al
Address: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Vendors/GPVendorAddress.table.al
Functional setting: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPMC40000.Table.al
Screenshots
What this package creates
1 Home2 Details3 lists1 sources to map
- Home: Current payable positions
- Details: Current vendor dossier
- Details: Vendor address book
- Sub-button: Current position vendor
- Sub-button: Position vendor address book
Sources are mapped locally and verified before applying.
Custom queriesPRO3 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.sqlQueryWITH setup AS (SELECT COUNT(1) AS SettingRows,MAX(CAST(s.FUNLCURR AS nvarchar(100))) AS CurrencyValue FROM dbo.MC40000 s) SELECT RTRIM(m.VCHRNMBR) AS VCHRNMBR, RTRIM(m.VENDORID) AS VENDORID, m.DOCTYPE AS DOCTYPE, m.DOCDATE AS DOCDATE, RTRIM(m.DOCNUMBR) AS DOCNUMBR, CAST(m.CURTRXAM AS nvarchar(100)) AS CURTRXAM, m.DUEDATE AS DUEDATE, m.TRXDSCRN AS TRXDSCRN, m.PORDNMBR AS PORDNMBR, m.PONUMBER AS PONUMBER, m.VOIDED AS VOIDED, CONVERT(nvarchar(33),m.POSTEDDT,126) AS POSTEDDT, RTRIM(m.CURNCYID) AS CURNCYID, m.PYMTRMID AS PYMTRMID, m.SHIPMTHD AS SHIPMTHD, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS PositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, CASE WHEN m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' THEN (SELECT CASE WHEN COUNT(1)=1 THEN MAX(v.VENDNAME) END FROM dbo.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000)))))) END AS CurrentVendorName, CASE WHEN m.VENDORID IS NULL OR RTRIM(m.VENDORID)=N'' THEN N'Missing position vendor identity' ELSE (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.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000)))))) END AS VendorState, CASE WHEN m.DOCTYPE IN (1,2,3) THEN N'Debit document group (1-3)' WHEN m.DOCTYPE IN (4,5) THEN N'Unapplied credit group (4-5)' ELSE N'Other / unknown stored document type' END AS DocumentGroup, CASE WHEN m.VOIDED IS NULL THEN N'Unknown (NULL)' WHEN m.VOIDED=0 THEN N'Not voided' WHEN m.VOIDED=1 THEN N'Voided' ELSE N'Unknown stored flag' END AS VoidContext, CASE WHEN setup.SettingRows=1 AND setup.CurrencyValue IS NOT NULL AND RTRIM(setup.CurrencyValue)<>N'' THEN RTRIM(setup.CurrencyValue) END AS FunctionalCurrencyContext, CASE WHEN setup.SettingRows=0 THEN N'Missing functional currency setting' WHEN setup.SettingRows<>1 THEN N'Ambiguous functional currency setting' WHEN setup.CurrencyValue IS NULL OR RTRIM(setup.CurrencyValue)=N'' THEN N'Functional currency setting is empty' ELSE N'Unique stored setting; not ISO or FX proof' END AS CurrencyState, N'Current PM Open position; not an aging, unpaid or payment-status guarantee' AS Scope FROM dbo.PM20000 m CROSS JOIN setup WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.PM20000 bad WHERE bad.DOCTYPE IS NULL OR bad.VCHRNMBR IS NULL OR RTRIM(bad.VCHRNMBR)=N'' OR (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=bad.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.VCHRNMBR AS nvarchar(4000))))))<>1)
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQueryWITH parent AS (SELECT p.DOCTYPE,p.VCHRNMBR,p.VENDORID,p.DOCNUMBR,p.CURNCYID FROM dbo.PM20000 p WHERE (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:position_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:position_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.DOCTYPE IS NOT NULL AND p.VCHRNMBR IS NOT NULL AND RTRIM(p.VCHRNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=p.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))))))=1) SELECT RTRIM(v.VENDORID) AS VENDORID, v.VENDNAME AS VENDNAME, v.VNDCHKNM AS VNDCHKNM, v.VENDSHNM AS VENDSHNM, v.VNDCLSID AS VNDCLSID, v.VNDCNTCT AS VNDCNTCT, v.ADDRESS1 AS ADDRESS1, v.ADDRESS2 AS ADDRESS2, v.ADDRESS3 AS ADDRESS3, v.CITY AS CITY, v.STATE AS STATE, v.ZIPCODE AS ZIPCODE, v.COUNTRY AS COUNTRY, v.PHNUMBR1 AS PHNUMBR1, v.PHNUMBR2 AS PHNUMBR2, v.PHONE3 AS PHONE3, v.FAXNUMBR AS FAXNUMBR, v.VADDCDPR AS VADDCDPR, v.VADCDPAD AS VADCDPAD, v.VADCDSFR AS VADCDSFR, v.VADCDTRO AS VADCDTRO, v.SHIPMTHD AS SHIPMTHD, v.TAXSCHID AS TAXSCHID, v.UPSZONE AS UPSZONE, v.ACNMVNDR AS ACNMVNDR, v.VENDSTTS AS VENDSTTS, v.HOLD AS HOLD, v.CURNCYID AS CURNCYID, v.RATETPID AS RATETPID, v.PYMTRMID AS PYMTRMID, v.PYMNTPRI AS PYMNTPRI, v.DISGRPER AS DISGRPER, v.DUEGRPER AS DUEGRPER, v.COMMENT1 AS COMMENT1, v.COMMENT2 AS COMMENT2, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS ParentPositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, N'Current PM00200 master, not the position snapshot or a payment permission' AS MasterScope FROM dbo.PM00200 v JOIN parent m ON (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' AND (SELECT COUNT(1) FROM dbo.PM00200 other WHERE (RTRIM(CAST(other.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000))))))=1
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQueryWITH parent AS (SELECT p.DOCTYPE,p.VCHRNMBR,p.VENDORID,p.DOCNUMBR,p.CURNCYID FROM dbo.PM20000 p WHERE (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:position_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:position_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.DOCTYPE IS NOT NULL AND p.VCHRNMBR IS NOT NULL AND RTRIM(p.VCHRNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=p.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))))))=1) SELECT a.VENDORID AS VENDORID, a.ADRSCODE AS ADRSCODE, a.VNDCNTCT AS VNDCNTCT, a.ADDRESS1 AS ADDRESS1, a.ADDRESS2 AS ADDRESS2, a.CITY AS CITY, a.STATE AS STATE, a.ZIPCODE AS ZIPCODE, a.PHNUMBR1 AS PHNUMBR1, a.FAXNUMBR AS FAXNUMBR, CONCAT(LEN(RTRIM(CAST(a.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(a.VENDORID AS nvarchar(100))),LEN(RTRIM(CAST(a.ADRSCODE AS nvarchar(100)))),N':',RTRIM(CAST(a.ADRSCODE AS nvarchar(100)))) AS AddressKey, RTRIM(m.VENDORID) AS ParentVendorKey, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS ParentPositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, N'Current vendor address book; not a proven voucher remit-to address' AS AddressScope FROM dbo.PM00300 a JOIN parent m ON (RTRIM(CAST(a.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' AND (SELECT COUNT(1) FROM dbo.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.PM00300 bad WHERE (RTRIM(CAST(bad.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) AND (bad.ADRSCODE IS NULL OR RTRIM(bad.ADRSCODE)=N'' OR (SELECT COUNT(1) FROM dbo.PM00300 other WHERE (RTRIM(CAST(other.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.VENDORID AS nvarchar(4000))))) AND (RTRIM(CAST(other.ADRSCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.ADRSCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ADRSCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.ADRSCODE 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": "E8BDEDF7-7C49-5448-A0A3-6971AB79F500",
"kind": "sqlServer",
"requiredObjects": [
"dbo.PM20000",
"dbo.PM00200",
"dbo.MC40000",
"dbo.PM00300"
],
"requiresCustomSQL": false
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "B9249584-0A9D-5DF2-B9D4-D064F4E554B3",
"key": "DUEDATE",
"type": "date"
},
{
"direction": "ascending",
"id": "51250900-B56A-5D38-BE4C-6CDDAD6893FA",
"key": "DOCNUMBR",
"type": "text"
}
],
"id": "69E6569A-B230-584D-AA83-BE653DACF22D",
"mappings": [
{
"commonFieldKey": "",
"key": "PositionKey",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PositionKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentContextToken",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentContextToken",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VCHRNMBR",
"label": "Voucher reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VCHRNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDORID",
"label": "Position vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDORID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DOCTYPE",
"label": "Stored document type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DOCTYPE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DOCDATE",
"label": "Document date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DOCDATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DOCNUMBR",
"label": "Document number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DOCNUMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CURTRXAM",
"label": "Stored unapplied amount — functional currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CURTRXAM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DUEDATE",
"label": "Source due-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DUEDATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "TRXDSCRN",
"label": "Transaction description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TRXDSCRN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PORDNMBR",
"label": "Source purchase-order reference (PORDNMBR)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PORDNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PONUMBER",
"label": "Source purchase-order reference (PONUMBER)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PONUMBER",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VOIDED",
"label": "Stored void flag (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VOIDED",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "POSTEDDT",
"label": "Source posting timestamp (timezone unverified)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "POSTEDDT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CURNCYID",
"label": "Document currency ID — not amount currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CURNCYID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PYMTRMID",
"label": "Position payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PYMTRMID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SHIPMTHD",
"label": "Position shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHIPMTHD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentVendorName",
"label": "Current vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CurrentVendorName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorState",
"label": "Current vendor availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VendorState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DocumentGroup",
"label": "Document group — own Open evidence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DocumentGroup",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "VoidContext",
"label": "Independent void context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VoidContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "FunctionalCurrencyContext",
"label": "Company functional currency context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FunctionalCurrencyContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CurrencyState",
"label": "Functional currency setting availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CurrencyState",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Scope",
"label": "Current position scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Scope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Current payable positions",
"primaryKey": "PositionKey",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"VCHRNMBR",
"VENDORID",
"DOCTYPE",
"DOCDATE",
"DOCNUMBR",
"CURTRXAM",
"DUEDATE",
"TRXDSCRN",
"PORDNMBR",
"PONUMBER",
"VOIDED",
"POSTEDDT",
"CURNCYID",
"PYMTRMID",
"SHIPMTHD",
"CurrentVendorName",
"VendorState",
"DocumentGroup",
"VoidContext",
"FunctionalCurrencyContext",
"CurrencyState",
"Scope"
],
"sourceID": "E8BDEDF7-7C49-5448-A0A3-6971AB79F500",
"sqlQuery": "WITH setup AS (SELECT COUNT(1) AS SettingRows,MAX(CAST(s.FUNLCURR AS nvarchar(100))) AS CurrencyValue FROM dbo.MC40000 s) SELECT RTRIM(m.VCHRNMBR) AS VCHRNMBR, RTRIM(m.VENDORID) AS VENDORID, m.DOCTYPE AS DOCTYPE, m.DOCDATE AS DOCDATE, RTRIM(m.DOCNUMBR) AS DOCNUMBR, CAST(m.CURTRXAM AS nvarchar(100)) AS CURTRXAM, m.DUEDATE AS DUEDATE, m.TRXDSCRN AS TRXDSCRN, m.PORDNMBR AS PORDNMBR, m.PONUMBER AS PONUMBER, m.VOIDED AS VOIDED, CONVERT(nvarchar(33),m.POSTEDDT,126) AS POSTEDDT, RTRIM(m.CURNCYID) AS CURNCYID, m.PYMTRMID AS PYMTRMID, m.SHIPMTHD AS SHIPMTHD, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS PositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, CASE WHEN m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' THEN (SELECT CASE WHEN COUNT(1)=1 THEN MAX(v.VENDNAME) END FROM dbo.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000)))))) END AS CurrentVendorName, CASE WHEN m.VENDORID IS NULL OR RTRIM(m.VENDORID)=N'' THEN N'Missing position vendor identity' ELSE (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.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000)))))) END AS VendorState, CASE WHEN m.DOCTYPE IN (1,2,3) THEN N'Debit document group (1-3)' WHEN m.DOCTYPE IN (4,5) THEN N'Unapplied credit group (4-5)' ELSE N'Other / unknown stored document type' END AS DocumentGroup, CASE WHEN m.VOIDED IS NULL THEN N'Unknown (NULL)' WHEN m.VOIDED=0 THEN N'Not voided' WHEN m.VOIDED=1 THEN N'Voided' ELSE N'Unknown stored flag' END AS VoidContext, CASE WHEN setup.SettingRows=1 AND setup.CurrencyValue IS NOT NULL AND RTRIM(setup.CurrencyValue)<>N'' THEN RTRIM(setup.CurrencyValue) END AS FunctionalCurrencyContext, CASE WHEN setup.SettingRows=0 THEN N'Missing functional currency setting' WHEN setup.SettingRows<>1 THEN N'Ambiguous functional currency setting' WHEN setup.CurrencyValue IS NULL OR RTRIM(setup.CurrencyValue)=N'' THEN N'Functional currency setting is empty' ELSE N'Unique stored setting; not ISO or FX proof' END AS CurrencyState, N'Current PM Open position; not an aging, unpaid or payment-status guarantee' AS Scope FROM dbo.PM20000 m CROSS JOIN setup WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.PM20000 bad WHERE bad.DOCTYPE IS NULL OR bad.VCHRNMBR IS NULL OR RTRIM(bad.VCHRNMBR)=N'' OR (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=bad.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.VCHRNMBR AS nvarchar(4000))))))<>1)",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "44EA15AF-43DA-5B5D-8A52-17FD850DB6B6",
"key": "VENDORID",
"type": "text"
}
],
"id": "F739C5ED-0249-5347-B377-F2E4746B3796",
"mappings": [
{
"commonFieldKey": "",
"key": "ParentPositionKey",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentPositionKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentContextToken",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentContextToken",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDORID",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDORID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDNAME",
"label": "Vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDNAME",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VNDCHKNM",
"label": "Payee / check name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VNDCHKNM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDSHNM",
"label": "Short name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDSHNM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VNDCLSID",
"label": "Vendor class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VNDCLSID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "VNDCNTCT",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VNDCNTCT",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ADDRESS1",
"label": "Address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS2",
"label": "Address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS3",
"label": "Address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "STATE",
"label": "State / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STATE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZIPCODE",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIPCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "COUNTRY",
"label": "Country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COUNTRY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHNUMBR1",
"label": "Phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHNUMBR1",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PHNUMBR2",
"label": "Phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHNUMBR2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE3",
"label": "Phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FAXNUMBR",
"label": "Fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FAXNUMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VADDCDPR",
"label": "Primary address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VADDCDPR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VADCDPAD",
"label": "Address default code (VADCDPAD)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VADCDPAD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VADCDSFR",
"label": "Address default code (VADCDSFR)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VADCDSFR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VADCDTRO",
"label": "Remit-to address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VADCDTRO",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SHIPMTHD",
"label": "Shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHIPMTHD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TAXSCHID",
"label": "Tax schedule code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TAXSCHID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UPSZONE",
"label": "UPS zone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UPSZONE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ACNMVNDR",
"label": "Our account code with vendor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ACNMVNDR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDSTTS",
"label": "Stored vendor status code (3 = temporary)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDSTTS",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "HOLD",
"label": "Stored payment hold (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "HOLD",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CURNCYID",
"label": "Default currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CURNCYID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "RATETPID",
"label": "Exchange-rate type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "RATETPID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PYMTRMID",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PYMTRMID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PYMNTPRI",
"label": "Payment priority code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PYMNTPRI",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DISGRPER",
"label": "Discount grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DISGRPER",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DUEGRPER",
"label": "Due-date grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DUEGRPER",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "COMMENT1",
"label": "Comment 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COMMENT1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "COMMENT2",
"label": "Comment 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COMMENT2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MasterScope",
"label": "Current master scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MasterScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Current position vendor",
"primaryKey": "VENDORID",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PositionKey",
"id": "7D93959D-0BCE-57BE-B525-4C3C743452A0",
"name": "position_ref",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ParentContextToken",
"id": "A6742308-F41B-5E92-8847-9E4F2166FCDB",
"name": "context_ref",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"VENDORID",
"VENDNAME",
"VNDCHKNM",
"VENDSHNM",
"VNDCLSID",
"VNDCNTCT",
"ADDRESS1",
"ADDRESS2",
"ADDRESS3",
"CITY",
"STATE",
"ZIPCODE",
"COUNTRY",
"PHNUMBR1",
"PHNUMBR2",
"PHONE3",
"FAXNUMBR",
"VADDCDPR",
"VADCDPAD",
"VADCDSFR",
"VADCDTRO",
"SHIPMTHD",
"TAXSCHID",
"UPSZONE",
"ACNMVNDR",
"VENDSTTS",
"HOLD",
"CURNCYID",
"RATETPID",
"PYMTRMID",
"PYMNTPRI",
"DISGRPER",
"DUEGRPER",
"COMMENT1",
"COMMENT2",
"MasterScope"
],
"sourceID": "E8BDEDF7-7C49-5448-A0A3-6971AB79F500",
"sqlQuery": "WITH parent AS (SELECT p.DOCTYPE,p.VCHRNMBR,p.VENDORID,p.DOCNUMBR,p.CURNCYID FROM dbo.PM20000 p WHERE (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:position_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:position_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.DOCTYPE IS NOT NULL AND p.VCHRNMBR IS NOT NULL AND RTRIM(p.VCHRNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=p.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))))))=1) SELECT RTRIM(v.VENDORID) AS VENDORID, v.VENDNAME AS VENDNAME, v.VNDCHKNM AS VNDCHKNM, v.VENDSHNM AS VENDSHNM, v.VNDCLSID AS VNDCLSID, v.VNDCNTCT AS VNDCNTCT, v.ADDRESS1 AS ADDRESS1, v.ADDRESS2 AS ADDRESS2, v.ADDRESS3 AS ADDRESS3, v.CITY AS CITY, v.STATE AS STATE, v.ZIPCODE AS ZIPCODE, v.COUNTRY AS COUNTRY, v.PHNUMBR1 AS PHNUMBR1, v.PHNUMBR2 AS PHNUMBR2, v.PHONE3 AS PHONE3, v.FAXNUMBR AS FAXNUMBR, v.VADDCDPR AS VADDCDPR, v.VADCDPAD AS VADCDPAD, v.VADCDSFR AS VADCDSFR, v.VADCDTRO AS VADCDTRO, v.SHIPMTHD AS SHIPMTHD, v.TAXSCHID AS TAXSCHID, v.UPSZONE AS UPSZONE, v.ACNMVNDR AS ACNMVNDR, v.VENDSTTS AS VENDSTTS, v.HOLD AS HOLD, v.CURNCYID AS CURNCYID, v.RATETPID AS RATETPID, v.PYMTRMID AS PYMTRMID, v.PYMNTPRI AS PYMNTPRI, v.DISGRPER AS DISGRPER, v.DUEGRPER AS DUEGRPER, v.COMMENT1 AS COMMENT1, v.COMMENT2 AS COMMENT2, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS ParentPositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, N'Current PM00200 master, not the position snapshot or a payment permission' AS MasterScope FROM dbo.PM00200 v JOIN parent m ON (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' AND (SELECT COUNT(1) FROM dbo.PM00200 other WHERE (RTRIM(CAST(other.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000))))))=1",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "4FFDE6B4-2149-5A3F-9385-91284A8741BF",
"key": "AddressKey",
"type": "text"
}
],
"id": "1782CD9E-7ED5-5042-BE9E-B4B6A5ACCF72",
"mappings": [
{
"commonFieldKey": "",
"key": "AddressKey",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AddressKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentVendorKey",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentVendorKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentPositionKey",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentPositionKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentContextToken",
"label": "Hidden complete identity / exact position context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentContextToken",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VENDORID",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VENDORID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADRSCODE",
"label": "Address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADRSCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VNDCNTCT",
"label": "Address contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VNDCNTCT",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ADDRESS1",
"label": "Address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS1",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ADDRESS2",
"label": "Address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "STATE",
"label": "State / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STATE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ZIPCODE",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIPCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PHNUMBR1",
"label": "Phone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHNUMBR1",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "FAXNUMBR",
"label": "Fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FAXNUMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressScope",
"label": "Current address scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AddressScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Position vendor address book",
"primaryKey": "AddressKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ParentPositionKey",
"id": "7D93959D-0BCE-57BE-B525-4C3C743452A0",
"name": "position_ref",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ParentContextToken",
"id": "A6742308-F41B-5E92-8847-9E4F2166FCDB",
"name": "context_ref",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"VENDORID",
"ADRSCODE",
"VNDCNTCT",
"ADDRESS1",
"ADDRESS2",
"CITY",
"STATE",
"ZIPCODE",
"PHNUMBR1",
"FAXNUMBR",
"AddressScope"
],
"sourceID": "E8BDEDF7-7C49-5448-A0A3-6971AB79F500",
"sqlQuery": "WITH parent AS (SELECT p.DOCTYPE,p.VCHRNMBR,p.VENDORID,p.DOCNUMBR,p.CURNCYID FROM dbo.PM20000 p WHERE (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:position_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.VCHRNMBR AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:position_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(p.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN p.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(p.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.DOCTYPE IS NOT NULL AND p.VCHRNMBR IS NOT NULL AND RTRIM(p.VCHRNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.PM20000 other WHERE other.DOCTYPE=p.DOCTYPE AND (RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VCHRNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.VCHRNMBR AS nvarchar(4000))))))=1) SELECT a.VENDORID AS VENDORID, a.ADRSCODE AS ADRSCODE, a.VNDCNTCT AS VNDCNTCT, a.ADDRESS1 AS ADDRESS1, a.ADDRESS2 AS ADDRESS2, a.CITY AS CITY, a.STATE AS STATE, a.ZIPCODE AS ZIPCODE, a.PHNUMBR1 AS PHNUMBR1, a.FAXNUMBR AS FAXNUMBR, CONCAT(LEN(RTRIM(CAST(a.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(a.VENDORID AS nvarchar(100))),LEN(RTRIM(CAST(a.ADRSCODE AS nvarchar(100)))),N':',RTRIM(CAST(a.ADRSCODE AS nvarchar(100)))) AS AddressKey, RTRIM(m.VENDORID) AS ParentVendorKey, CONCAT(LEN(RTRIM(CAST(m.DOCTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCTYPE AS nvarchar(100))),LEN(RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.VCHRNMBR AS nvarchar(100)))) AS ParentPositionKey, CONCAT(LEN(RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.VENDORID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.VENDORID AS nvarchar(100)))),N':',RTRIM(CAST(m.VENDORID AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.DOCNUMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.DOCNUMBR AS nvarchar(100)))) END AS nvarchar(100))),LEN(RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))),N':',RTRIM(CAST(CASE WHEN m.CURNCYID IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURNCYID AS nvarchar(100)))),N':',RTRIM(CAST(m.CURNCYID AS nvarchar(100)))) END AS nvarchar(100)))) AS ParentContextToken, N'Current vendor address book; not a proven voucher remit-to address' AS AddressScope FROM dbo.PM00300 a JOIN parent m ON (RTRIM(CAST(a.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.PM20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.PM00300',N'U') IS NOT NULL AND m.VENDORID IS NOT NULL AND RTRIM(m.VENDORID)<>N'' AND (SELECT COUNT(1) FROM dbo.PM00200 v WHERE (RTRIM(CAST(v.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(v.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.PM00300 bad WHERE (RTRIM(CAST(bad.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.VENDORID AS nvarchar(4000))))) AND (bad.ADRSCODE IS NULL OR RTRIM(bad.ADRSCODE)=N'' OR (SELECT COUNT(1) FROM dbo.PM00300 other WHERE (RTRIM(CAST(other.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.VENDORID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.VENDORID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.VENDORID AS nvarchar(4000))))) AND (RTRIM(CAST(other.ADRSCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.ADRSCODE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.ADRSCODE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.ADRSCODE AS nvarchar(4000))))))<>1))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "B1330C82-05EF-5347-920E-1EFA78CF9324",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "8BD9EA59-238C-5706-9682-E3A69616762C",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "F739C5ED-0249-5347-B377-F2E4746B3796",
"title": "Current vendor dossier",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VENDORID",
"label": "Vendor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DUEDATE",
"label": "Due-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CURTRXAM",
"label": "Unapplied — functional",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "FunctionalCurrencyContext",
"label": "Functional currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DocumentGroup",
"label": "Document group",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VoidContext",
"label": "Void status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Terms",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrencyState",
"label": "Currency setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "69E6569A-B230-584D-AA83-BE653DACF22D",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "DOCNUMBR",
"label": "Document number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "VCHRNMBR",
"label": "Voucher reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "VENDORID",
"label": "Position vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "CurrentVendorName",
"label": "Current vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "VendorState",
"label": "Current vendor availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Position and current vendor",
"detailRole": "information",
"isVisible": true,
"key": "Scope",
"label": "Current position scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored amount and its currency",
"detailRole": "information",
"isVisible": true,
"key": "CURTRXAM",
"label": "Stored unapplied amount — functional currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored amount and its currency",
"detailRole": "information",
"isVisible": true,
"key": "FunctionalCurrencyContext",
"label": "Company functional currency context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored amount and its currency",
"detailRole": "information",
"isVisible": true,
"key": "CurrencyState",
"label": "Functional currency setting availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored amount and its currency",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Document currency ID — not amount currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Independent document states",
"detailRole": "information",
"isVisible": true,
"key": "DocumentGroup",
"label": "Document group — own Open evidence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Independent document states",
"detailRole": "information",
"isVisible": true,
"key": "DOCTYPE",
"label": "Stored document type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Independent document states",
"detailRole": "information",
"isVisible": true,
"key": "VoidContext",
"label": "Independent void context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Independent document states",
"detailRole": "information",
"isVisible": true,
"key": "VOIDED",
"label": "Stored void flag (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Dates and terms",
"detailRole": "information",
"isVisible": true,
"key": "DOCDATE",
"label": "Document date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Dates and terms",
"detailRole": "information",
"isVisible": true,
"key": "DUEDATE",
"label": "Source due-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Dates and terms",
"detailRole": "information",
"isVisible": true,
"key": "POSTEDDT",
"label": "Source posting timestamp (timezone unverified)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Dates and terms",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Position payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial references",
"detailRole": "information",
"isVisible": true,
"key": "TRXDSCRN",
"label": "Transaction description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial references",
"detailRole": "information",
"isVisible": true,
"key": "PORDNMBR",
"label": "Source purchase-order reference (PORDNMBR)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial references",
"detailRole": "information",
"isVisible": true,
"key": "PONUMBER",
"label": "Source purchase-order reference (PONUMBER)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial references",
"detailRole": "information",
"isVisible": true,
"key": "SHIPMTHD",
"label": "Position shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "D6BF529C-F02C-5921-A434-9193EE1903AD",
"openFilters": [
{
"id": "A900A85E-ACAA-5854-97EF-A11D064C875B",
"includeAllOption": true,
"key": "VENDORID",
"title": "Position vendor",
"type": "text"
},
{
"id": "4096EED3-C3F2-5AEF-ADBA-9B49E3805D60",
"includeAllOption": true,
"key": "DocumentGroup",
"title": "Document group",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "ascending",
"id": "B9249584-0A9D-5DF2-B9D4-D064F4E554B3",
"key": "DUEDATE",
"type": "date"
},
{
"direction": "ascending",
"id": "51250900-B56A-5D38-BE4C-6CDDAD6893FA",
"key": "DOCNUMBR",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CurrentVendorName",
"systemImage": "doc.text",
"title": "Current payable positions",
"titleKey": "DOCNUMBR"
},
{
"actions": [
{
"id": "CF634B3B-49F3-527D-9DF0-971864A13749",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "F0CB1681-199E-5AA9-B144-41D6A7324704",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "1782CD9E-7ED5-5042-BE9E-B4B6A5ACCF72",
"title": "Vendor address book",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VNDCNTCT",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR1",
"label": "Phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VNDCLSID",
"label": "Vendor class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Terms",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Default currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "HOLD",
"label": "Payment hold (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VENDSTTS",
"label": "Status code (3=temporary)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "F739C5ED-0249-5347-B377-F2E4746B3796",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "VENDNAME",
"label": "Vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "VENDORID",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "VENDSHNM",
"label": "Short name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "VNDCHKNM",
"label": "Payee / check name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "VNDCLSID",
"label": "Vendor class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "ACNMVNDR",
"label": "Our account code with vendor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "VNDCNTCT",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR1",
"label": "Phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR2",
"label": "Phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE3",
"label": "Phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "FAXNUMBR",
"label": "Fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS2",
"label": "Address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS3",
"label": "Address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "State / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "COUNTRY",
"label": "Country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "VADDCDPR",
"label": "Primary address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "VADCDTRO",
"label": "Remit-to address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "VADCDPAD",
"label": "Address default code (VADCDPAD)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "VADCDSFR",
"label": "Address default code (VADCDSFR)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "SHIPMTHD",
"label": "Shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "TAXSCHID",
"label": "Tax schedule code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "UPSZONE",
"label": "UPS zone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "PYMNTPRI",
"label": "Payment priority code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "DISGRPER",
"label": "Discount grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "DUEGRPER",
"label": "Due-date grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Default currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Payment defaults",
"detailRole": "information",
"isVisible": true,
"key": "RATETPID",
"label": "Exchange-rate type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored payment hold and status",
"detailRole": "information",
"isVisible": true,
"key": "HOLD",
"label": "Stored payment hold (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored payment hold and status",
"detailRole": "information",
"isVisible": true,
"key": "VENDSTTS",
"label": "Stored vendor status code (3 = temporary)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Business notes",
"detailRole": "information",
"isVisible": true,
"key": "COMMENT1",
"label": "Comment 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Business notes",
"detailRole": "information",
"isVisible": true,
"key": "COMMENT2",
"label": "Comment 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current master scope",
"detailRole": "information",
"isVisible": true,
"key": "MasterScope",
"label": "Current master scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "EAD22868-917C-51A6-823F-26A783AB656E",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "44EA15AF-43DA-5B5D-8A52-17FD850DB6B6",
"key": "VENDORID",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "VENDORID",
"systemImage": "building.2",
"title": "Current position vendor",
"titleKey": "VENDNAME"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VNDCNTCT",
"label": "Address contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR1",
"label": "Phone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "State / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "1782CD9E-7ED5-5042-BE9E-B4B6A5ACCF72",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business identity",
"detailRole": "information",
"isVisible": true,
"key": "ADRSCODE",
"label": "Address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Business identity",
"detailRole": "information",
"isVisible": true,
"key": "VENDORID",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address contact",
"detailRole": "information",
"isVisible": true,
"key": "VNDCNTCT",
"label": "Address contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address contact",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR1",
"label": "Phone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Address contact",
"detailRole": "information",
"isVisible": true,
"key": "FAXNUMBR",
"label": "Fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Vendor address",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Vendor address",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS2",
"label": "Address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Vendor address",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "City",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Vendor address",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "State / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Vendor address",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current address scope",
"detailRole": "information",
"isVisible": true,
"key": "AddressScope",
"label": "Current address scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "56922DF4-78F7-5CB8-A413-8E17BC14D257",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "4FFDE6B4-2149-5A3F-9385-91284A8741BF",
"key": "AddressKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CITY",
"systemImage": "map",
"title": "Position vendor address book",
"titleKey": "ADRSCODE"
}
],
"relations": [
{
"childDatasetID": "F739C5ED-0249-5347-B377-F2E4746B3796",
"childKey": "ParentPositionKey",
"id": "8BD9EA59-238C-5706-9682-E3A69616762C",
"name": "Current vendor dossier",
"parentDatasetID": "69E6569A-B230-584D-AA83-BE653DACF22D",
"parentKey": "PositionKey"
},
{
"childDatasetID": "1782CD9E-7ED5-5042-BE9E-B4B6A5ACCF72",
"childKey": "ParentVendorKey",
"id": "F0CB1681-199E-5AA9-B144-41D6A7324704",
"name": "Vendor address book",
"parentDatasetID": "F739C5ED-0249-5347-B377-F2E4746B3796",
"parentKey": "VENDORID"
}
],
"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. For accounts payable, purchasing, supplier service and managers: choose a vendor or document group, inspect a current payable position with its stored unapplied amount, company functional currency setting, document currency, due date, void flag, terms and commercial references. Open the current vendor dossier and its address book on demand. Older documents remain available: there is no automatic opening-date cutoff.\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, three explicit SELECT lists, one Home and two lazy buttons (position to vendor, vendor to address book). Each read is capped at 1,000 rows; no scheduled refresh. Optional opening filters precede the cap. A filter/cap does not prove completeness or cheap execution. No SELECT *, credentials, cached business rows or executable code in this export.\n\nOwn source registrations and selected AL staging definitions are pinned to Microsoft commit e7ed235bc979c0283306e9639ff7cd22ce78341d. AL fields/types/logical keys are not installed SQL DDL, physical PK/FK, constraints, collation, permissions or performance proof. Run the import read test and verify actual company objects, GP version, SQL types/keys and financial interpretations against known transactions. No real GP, SQL Server or iOS source test is claimed.\n\nPM20000.CURTRXAM is the stored unapplied amount in Functional view (Microsoft KB3208146). CURNCYID is the document currency, NOT the currency of that amount. Current vendor default is separate again. MC40000.FUNLCURR is shown only when the entire setting has exactly one nonblank row; zero, duplicate (even identical), NULL or blank settings show the reason and no invented currency. The MAX in the one-pass setting aggregate is never accepted as a first-match/dedup substitute when the row count differs from one. No ISO, exchange rate or conversion is inferred.\n\nPM Open table membership is not a full unpaid balance, posting/payment proof or historical/as-of aging report. Fully applied rows may remain Open during maintenance issues, and applied documents may move to PM30200 history. Keep zero, negative, NULL and tiny stored amounts, independent void state and all document types. Do not copy migration-only amount/type filters, reconstruct original amounts or applied sums, invert signs or remove zero rows. Amount text is lossless; local sorting is textual, not numeric aggregation.\n\nOnly native PM Open groups are labelled from KB852208: debit group codes 1-3 and unapplied credit group codes 4-5. Individual Invoice/Charge/Return names and type 6 are not inferred from the historical table, BC migration grouping, PM10300 computer-check context, RM or SOP. Other stored codes remain explicitly unknown. POSTEDDT remains full source ISO text, not a silently truncated Date or a certified timezone/event. PORDNMBR and PONUMBER are separate native reference fields, not a merged or proven order link.\n\nPosition identity contains full DOCTYPE plus VCHRNMBR, not vendor invoice DOCNUMBR alone. Invalid/duplicate identities block the root read. Vendor name is available only from a unique current master; missing/ambiguous metadata preserves the position with a state, without fanout or first match. Lazy routes revalidate the exact position and its NULL-aware vendor/document/currency binding. A removed/changed/ambiguous parent blocks stale routes. Address identity contains vendor and address code; an invalid/duplicate selected address identity blocks that whole child. Current master/address are not historical voucher snapshots or proven remit-to/payment eligibility.\n\nCurrent master data uses its own documented 35-field subset; the 10-field address subset does not claim email/country/extensions. No personal tax IDs, bank/checkbook, 1099, technical audit users or internal row IDs requested. Use a separately authorised least-privilege SELECT reporting login on allowed company data, not DYNAMICS, BC staging, sa/sysadmin/DYNGRP. GP application passwords are transformed; filters are not access control and do not inherit GP permissions. Commercial/contact information can be confidential.\n\nScreenshots use native Cifru with wholly fictional DEMO source-shaped rows and synthetic transport, not live GP/SQL Server. Unofficial, not endorsed by Microsoft. Support maintenance SQL or Check Links is not executed.\n\nSelected source evidence: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPPM20000.Table.al\nAmount semantics: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/a-fully-applied-document-open-status\nDocument groups: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/fail-to-print-checks-in-payables-management\nLifecycle: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/how-manual-payments-credit-memos-appear\nVendor: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Vendors/GPPM00200.Table.al\nAddress: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Vendors/GPVendorAddress.table.al\nFunctional setting: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPMC40000.Table.al",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "9A4DDE65-CF22-57DB-A809-C6713F5CECF8",
"rootButtonCount": 1,
"summary": "Stored functional unapplied amounts, due dates and native document groups, with lazy current vendor and address dossiers.",
"tags": [
"Dynamics GP",
"SQL Server",
"Payables",
"Accounting",
"Suppliers",
"Pro"
],
"title": "Current payable positions, vendor and address book — Pro"
}
}