Historical customer invoices, lines and current customer — Pro
Period-filtered historical invoice dossiers with original amounts, due dates, on-demand lines and current customers.
UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. The selected SOP30200/SOP30300/RM00101 fields, native captions and logical identities come from Microsoft GP source registrations and AL staging definitions at commit e7ed235bc979c0283306e9639ff7cd22ce78341d. These are not installed SQL DDL, certified physical keys/relations, ERP ACL, current edition or execution plans. The proposed invoice/line relation uses SOPTYPE plus full SOPNUMBE; line identity also includes CMPNTSEQ and LNITMSEQ. Validate actual objects, types, relationships, source permissions and originating interpretations with the import read test and known invoices before use.
For sales, customer service, receivables teams and managers: select a document-date period and optionally a customer. Inspect the historical invoice, customer PO, due/discount dates, independent void flag, original originating amounts, commercial references and invoice ship-to. Open its own item/service lines with units, native quantities, prices/costs and line-specific delivery context, or the current customer dossier with contacts, addresses and commercial defaults.
Why Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.
Pro: one authorised SQL company source, one Home, three lists, two on-demand related buttons and explicit read-only SQL. No SELECT *, credential, business row or executable code is embedded. Mandatory start/inclusive and end/exclusive document dates and optional customer selection precede the 1,000-row root cap. Children have a separate 1,000-row cap and revalidate full invoice identity plus its customer/currency context; they are not pruned by the parent opening date period. Narrowing filters does not guarantee a cheap scan. Reaching a cap may mean incomplete data; no scheduled refresh is enabled.
This is SOP history invoices only (SOPTYPE=3), including independently displayed void status. It is not all sales documents, Work transactions, RM open receivables, invoice-payment application, an unpaid-balance report or proof of posting. History depends on GP retention settings; voided documents can appear in Work and History simultaneously. No union, payment reconstruction, aging or SOPSTATUS sales-order legend is applied.
Amounts and quantities preserve source text, NULL, zero, sign and decimal precision; local sorting is textual, not numeric aggregation. Original originating-labelled document amount, subtotal, discount/freight/misc/tax, unit/extended price and cost are shown without total, margin, FX or tax reconstruction. ORDAVAMT and ORDISTKN remain native originating fields, not an unpaid-balance formula. Native quantity roles remain separate; no remaining-to-deliver/invoice formula or unit conversion.
SOP30300 has a stored CURRNIDX, not its own selected CURNCYID. The line shows only parent invoice currency context when both stored indices match and the parent currency ID exists. Missing or mismatched indices show the reason and leave that context empty. Equality is not certification of a currency dictionary, ISO currency, actual cost valuation or conversion. Never substitute the current customer default. Header/line ship-to data are stored document contexts, not the customer master. Native date settings retain their source roles; an empty or sentinel date is not silently converted to a factual event.
Current RM00101 data is read separately and may differ from the invoice snapshot. Missing/ambiguous customer metadata preserves the invoice with an explicit state, without an arbitrary first match. Invalid or duplicate full line identities block that whole child, not a cosmetically partial list. Invalid/duplicate invoice identities block the selected-period root read. A changed parent customer/currency context blocks stale child bindings until the invoice is reread. Business codes preserve leading zeroes, case and leading spaces; only right padding is removed. Binary Unicode comparisons do not certify installed SQL constraints or collation.
Use a separately authorised least-privilege SELECT reporting login on allowed company data, not DYNAMICS, BC staging, sa, sysadmin or DYNGRP. GP application passwords are transformed; obtain an authorised SQL login. Cifru filters are not access control and do not inherit GP permissions. Commercial/contact/cost information may be confidential. Screenshots use native Cifru with wholly fictional DEMO rows and synthetic transport, not live GP or SQL Server. Unofficial, not endorsed by Microsoft.
DEMO includes deliberate missing, unknown and currency-inconsistent source records to illustrate safeguards; it is not a reconciled accounting invoice.
Primary registration: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/codeunits/GPCloudMigration.codeunit.al
Header: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/History/GPSOPTrxHist.table.al
Line: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/History/GPSOPTrxAmountsHist.table.al
Customer: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Customers/GPRM00101.Table.al
Transaction context: https://learn.microsoft.com/en-us/dynamics-gp/distribution/sales-order-processing-part2-transaction-entry
History: https://learn.microsoft.com/en-us/dynamics-gp/distribution/sales-order-processing-part4-transaction-activity
Work/history caveat: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/token-startelement-in-state-endrootelement-would-result-in-invalid-xml-document-error
Screenshots
What this package creates
1 Home2 Details3 lists1 sources to map
- Home: Historical customer invoice dossier
- Details: Invoice lines
- Details: Current customer
- Sub-button: Historical invoice lines
- Sub-button: Current invoice customer
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.sqlQuerySELECT RTRIM(m.SOPNUMBE) AS SOPNUMBE, m.DOCID AS DOCID, m.DOCDATE AS DOCDATE, m.INVODATE AS INVODATE, m.GLPOSTDT AS GLPOSTDT, m.DUEDATE AS DUEDATE, m.DISCDATE AS DISCDATE, RTRIM(m.CUSTNMBR) AS CUSTNMBR, m.CUSTNAME AS CUSTNAME, m.CSTPONBR AS CSTPONBR, m.VOIDSTTS AS VOIDSTTS, m.CURNCYID AS CURNCYID, CAST(m.ORDOCAMT AS nvarchar(100)) AS ORDOCAMT, CAST(m.ORSUBTOT AS nvarchar(100)) AS ORSUBTOT, CAST(m.ORTDISAM AS nvarchar(100)) AS ORTDISAM, CAST(m.ORFRTAMT AS nvarchar(100)) AS ORFRTAMT, CAST(m.ORMISCAMT AS nvarchar(100)) AS ORMISCAMT, CAST(m.ORTAXAMT AS nvarchar(100)) AS ORTAXAMT, CAST(m.ORDISTKN AS nvarchar(100)) AS ORDISTKN, CAST(m.ORDAVAMT AS nvarchar(100)) AS ORDAVAMT, m.PYMTRMID AS PYMTRMID, m.PRCLEVEL AS PRCLEVEL, m.SALSTERR AS SALSTERR, m.SLPRSNID AS SLPRSNID, m.SHIPMTHD AS SHIPMTHD, m.LOCNCODE AS LOCNCODE, m.ReqShipDate AS ReqShipDate, m.ACTLSHIP AS ACTLSHIP, m.FUFILDAT AS FUFILDAT, m.ShipToName AS ShipToName, m.CNTCPRSN AS CNTCPRSN, m.ADDRESS1 AS ADDRESS1, m.ADDRESS2 AS ADDRESS2, m.ADDRESS3 AS ADDRESS3, m.CITY AS CITY, m.STATE AS STATE, m.ZIPCODE AS ZIPCODE, m.COUNTRY AS COUNTRY, m.PHNUMBR1 AS PHNUMBR1, m.PHNUMBR2 AS PHNUMBR2, m.PHONE3 AS PHONE3, m.REFRENCE AS REFRENCE, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS InvoiceKey, CONCAT(CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),CASE WHEN m.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN m.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(m.CURRNIDX AS nvarchar(100)))) END,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 ParentContextToken, CASE WHEN m.VOIDSTTS IS NULL THEN N'Unknown (NULL)' ELSE CASE m.VOIDSTTS WHEN 0 THEN N'Not Voided' WHEN 1 THEN N'Voided' ELSE N'Unknown stored code' END END AS VoidContext, CASE WHEN m.CUSTNMBR IS NOT NULL AND RTRIM(m.CUSTNMBR)<>N'' THEN (SELECT CASE WHEN COUNT(1)=1 THEN MAX(c.CUSTNAME) END FROM dbo.RM00101 c WHERE (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000)))))) END AS CurrentCustomerName, CASE WHEN m.CUSTNMBR IS NULL OR RTRIM(m.CUSTNMBR)=N'' THEN N'Missing invoice customer 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.RM00101 c WHERE (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000)))))) END AS CustomerState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.SOP30300 bad WHERE bad.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) AND (bad.CMPNTSEQ IS NULL OR bad.LNITMSEQ IS NULL OR (SELECT COUNT(1) FROM dbo.SOP30300 x WHERE x.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))) AND x.CMPNTSEQ=bad.CMPNTSEQ AND x.LNITMSEQ=bad.LNITMSEQ)<>1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate full line identity' END AS LineState, N'SOP history invoice only; not payment or posting certification' AS Scope FROM dbo.SOP30200 m WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.SOP30200 bad WHERE bad.SOPTYPE=3 AND bad.DOCDATE>=:date_from AND bad.DOCDATE<:date_until AND (bad.SOPNUMBE IS NULL OR RTRIM(bad.SOPNUMBE)=N'' OR (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))))<>1)) AND m.SOPTYPE=3 AND m.DOCDATE>=:date_from AND m.DOCDATE<:date_until
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQueryWITH parent AS (SELECT p.SOPNUMBE,p.SOPTYPE,p.CUSTNMBR,p.CURRNIDX,p.CURNCYID FROM dbo.SOP30200 p WHERE p.SOPTYPE=3 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:invoice_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:invoice_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.SOPNUMBE IS NOT NULL AND RTRIM(p.SOPNUMBE)<>N'' AND (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=p.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))))))=1) SELECT RTRIM(l.SOPNUMBE) AS SOPNUMBE, l.ITEMNMBR AS ITEMNMBR, l.ITEMDESC AS ITEMDESC, l.NONINVEN AS NONINVEN, l.UOFM AS UOFM, l.LOCNCODE AS LOCNCODE, CAST(l.QUANTITY AS nvarchar(100)) AS QUANTITY, CAST(l.QTYORDER AS nvarchar(100)) AS QTYORDER, CAST(l.QTYCANCE AS nvarchar(100)) AS QTYCANCE, CAST(l.QTYFULFI AS nvarchar(100)) AS QTYFULFI, CAST(l.QTYTOINV AS nvarchar(100)) AS QTYTOINV, CAST(l.ORUNTPRC AS nvarchar(100)) AS ORUNTPRC, CAST(l.OXTNDPRC AS nvarchar(100)) AS OXTNDPRC, CAST(l.ORUNTCST AS nvarchar(100)) AS ORUNTCST, CAST(l.OREXTCST AS nvarchar(100)) AS OREXTCST, CAST(l.ORMRKDAM AS nvarchar(100)) AS ORMRKDAM, l.MRKDNTYP AS MRKDNTYP, CAST(l.ORTDISAM AS nvarchar(100)) AS ORTDISAM, CAST(l.ORTAXAMT AS nvarchar(100)) AS ORTAXAMT, l.PRCLEVEL AS PRCLEVEL, l.SHIPMTHD AS SHIPMTHD, l.SALSTERR AS SALSTERR, l.SLPRSNID AS SLPRSNID, l.ReqShipDate AS ReqShipDate, l.ACTLSHIP AS ACTLSHIP, l.FUFILDAT AS FUFILDAT, l.ShipToName AS ShipToName, l.CNTCPRSN AS CNTCPRSN, l.ADDRESS1 AS ADDRESS1, l.ADDRESS2 AS ADDRESS2, l.ADDRESS3 AS ADDRESS3, l.CITY AS CITY, l.STATE AS STATE, l.ZIPCODE AS ZIPCODE, l.COUNTRY AS COUNTRY, l.PHONE1 AS PHONE1, l.PHONE2 AS PHONE2, l.PHONE3 AS PHONE3, CONCAT(LEN(RTRIM(CAST(l.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(l.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(l.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(l.SOPTYPE AS nvarchar(100))),LEN(RTRIM(CAST(l.CMPNTSEQ AS nvarchar(100)))),N':',RTRIM(CAST(l.CMPNTSEQ AS nvarchar(100))),LEN(RTRIM(CAST(l.LNITMSEQ AS nvarchar(100)))),N':',RTRIM(CAST(l.LNITMSEQ AS nvarchar(100)))) AS LineKey, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS ParentInvoiceKey, CASE WHEN l.NONINVEN IS NULL THEN N'Unknown (NULL)' ELSE CASE l.NONINVEN WHEN 0 THEN N'No' WHEN 1 THEN N'Yes' ELSE N'Unknown stored code' END END AS NonInventoryContext, CASE WHEN l.MRKDNTYP IS NULL THEN N'Unknown (NULL)' ELSE CASE l.MRKDNTYP WHEN 0 THEN N'Percentage' WHEN 1 THEN N'Amount' ELSE N'Unknown stored code' END END AS MarkdownContext, CASE WHEN l.CURRNIDX IS NOT NULL AND m.CURRNIDX IS NOT NULL AND l.CURRNIDX=m.CURRNIDX AND m.CURNCYID IS NOT NULL AND RTRIM(m.CURNCYID)<>N'' THEN RTRIM(m.CURNCYID) END AS ParentCurrencyContext, CASE WHEN l.CURRNIDX IS NULL OR m.CURRNIDX IS NULL THEN N'Unknown: missing currency index' WHEN l.CURRNIDX<>m.CURRNIDX THEN N'Warning: line and invoice currency indices differ' WHEN m.CURNCYID IS NULL OR RTRIM(m.CURNCYID)=N'' THEN N'Unknown: parent currency ID absent' ELSE N'Equal stored indices; parent context only, not ISO proof' END AS CurrencyState, N'Original historical line; quantities and costs are not outstanding balance or margin' AS Scope FROM dbo.SOP30300 l JOIN parent m ON l.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(l.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(l.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.SOP30300 bad WHERE bad.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) AND (bad.CMPNTSEQ IS NULL OR bad.LNITMSEQ IS NULL OR (SELECT COUNT(1) FROM dbo.SOP30300 x WHERE x.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))) AND x.CMPNTSEQ=bad.CMPNTSEQ AND x.LNITMSEQ=bad.LNITMSEQ)<>1))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQueryWITH parent AS (SELECT p.SOPNUMBE,p.SOPTYPE,p.CUSTNMBR,p.CURRNIDX,p.CURNCYID FROM dbo.SOP30200 p WHERE p.SOPTYPE=3 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:invoice_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:invoice_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.SOPNUMBE IS NOT NULL AND RTRIM(p.SOPNUMBE)<>N'' AND (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=p.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))))))=1) SELECT RTRIM(c.CUSTNMBR) AS CUSTNMBR, c.CUSTNAME AS CUSTNAME, c.CUSTCLAS AS CUSTCLAS, c.STMTNAME AS STMTNAME, c.SHRTNAME AS SHRTNAME, c.CNTCPRSN AS CNTCPRSN, c.ADDRESS1 AS ADDRESS1, c.ADDRESS2 AS ADDRESS2, c.ADDRESS3 AS ADDRESS3, c.COUNTRY AS COUNTRY, c.CITY AS CITY, c.STATE AS STATE, c.ZIP AS ZIP, c.PHONE1 AS PHONE1, c.PHONE2 AS PHONE2, c.PHONE3 AS PHONE3, c.FAX AS FAX, c.ADRSCODE AS ADRSCODE, c.PRBTADCD AS PRBTADCD, c.PRSTADCD AS PRSTADCD, c.STADDRCD AS STADDRCD, c.SHIPMTHD AS SHIPMTHD, c.UPSZONE AS UPSZONE, c.SLPRSNID AS SLPRSNID, c.SALSTERR AS SALSTERR, c.PYMTRMID AS PYMTRMID, c.PRCLEVEL AS PRCLEVEL, c.CURNCYID AS CURNCYID, c.RATETPID AS RATETPID, c.CPRCSTNM AS CPRCSTNM, c.INACTIVE AS INACTIVE, c.HOLD AS HOLD, c.COMMENT1 AS COMMENT1, c.COMMENT2 AS COMMENT2, c.SHIPCOMPLETE AS SHIPCOMPLETE, c.DISGRPER AS DISGRPER, c.DUEGRPER AS DUEGRPER, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS ParentInvoiceKey, N'Current RM00101 master; not a historical invoice snapshot' AS MasterScope FROM dbo.RM00101 c JOIN parent m ON (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND m.CUSTNMBR IS NOT NULL AND RTRIM(m.CUSTNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.RM00101 other WHERE (RTRIM(CAST(other.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(c.CUSTNMBR 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": "5E35A9A8-CC94-5D93-8D74-5C92127D1A1D",
"kind": "sqlServer",
"requiredObjects": [
"dbo.SOP30200",
"dbo.SOP30300",
"dbo.RM00101"
],
"requiresCustomSQL": false
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "9BA8DDF4-6C48-5D38-A2BA-1E3FF9E7AE5C",
"key": "DOCDATE",
"type": "date"
},
{
"direction": "ascending",
"id": "9568BE68-FB06-5FE2-AFDE-6F2535812617",
"key": "SOPNUMBE",
"type": "text"
}
],
"id": "05F7E858-E8E3-5CB1-8FFE-A130466C86EA",
"mappings": [
{
"commonFieldKey": "",
"key": "InvoiceKey",
"label": "Hidden full identity / exact parent context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "InvoiceKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentContextToken",
"label": "Hidden full identity / exact parent context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentContextToken",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SOPNUMBE",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SOPNUMBE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DOCID",
"label": "Document ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DOCID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DOCDATE",
"label": "Document date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DOCDATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "INVODATE",
"label": "Stored invoice-date setting (INVODATE)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "INVODATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "GLPOSTDT",
"label": "Stored GL posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "GLPOSTDT",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DUEDATE",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DUEDATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DISCDATE",
"label": "Discount date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DISCDATE",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CUSTNMBR",
"label": "Invoice customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CUSTNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CUSTNAME",
"label": "Customer name stored on invoice",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CUSTNAME",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CSTPONBR",
"label": "Customer purchase-order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CSTPONBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "VOIDSTTS",
"label": "Stored void code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VOIDSTTS",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VoidContext",
"label": "Documented void context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "VoidContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CURNCYID",
"label": "Invoice currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CURNCYID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ORDOCAMT",
"label": "Original originating document amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORDOCAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ORSUBTOT",
"label": "Original originating subtotal",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORSUBTOT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORTDISAM",
"label": "Original originating trade discount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORTDISAM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORFRTAMT",
"label": "Original originating freight",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORFRTAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORMISCAMT",
"label": "Original originating miscellaneous amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORMISCAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORTAXAMT",
"label": "Original originating tax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORTAXAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORDISTKN",
"label": "Original originating discount taken",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORDISTKN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORDAVAMT",
"label": "Original originating discount available",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORDAVAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PYMTRMID",
"label": "Invoice payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PYMTRMID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PRCLEVEL",
"label": "Invoice price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRCLEVEL",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SALSTERR",
"label": "Invoice sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SALSTERR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SLPRSNID",
"label": "Invoice salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SLPRSNID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SHIPMTHD",
"label": "Invoice shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHIPMTHD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LOCNCODE",
"label": "Invoice site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LOCNCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReqShipDate",
"label": "Requested ship date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReqShipDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ACTLSHIP",
"label": "Actual ship-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ACTLSHIP",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FUFILDAT",
"label": "Fulfillment-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FUFILDAT",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToName",
"label": "Invoice ship-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ShipToName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CNTCPRSN",
"label": "Invoice ship-to contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CNTCPRSN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS1",
"label": "Invoice ship-to address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS2",
"label": "Invoice ship-to address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS3",
"label": "Invoice ship-to address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CITY",
"label": "Invoice ship-to city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "STATE",
"label": "Invoice ship-to state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STATE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZIPCODE",
"label": "Invoice ship-to postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIPCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "COUNTRY",
"label": "Invoice ship-to country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COUNTRY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHNUMBR1",
"label": "Invoice ship-to phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHNUMBR1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHNUMBR2",
"label": "Invoice ship-to phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHNUMBR2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE3",
"label": "Invoice ship-to phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "REFRENCE",
"label": "Stored document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "REFRENCE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentCustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CurrentCustomerName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerState",
"label": "Current customer master availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CustomerState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineState",
"label": "Full line identity availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LineState",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Scope",
"label": "Historical invoice scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Scope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Historical customer invoice dossier",
"primaryKey": "InvoiceKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "DOCDATE",
"id": "19DB5282-1AC8-53D3-8C96-D141470380CE",
"name": "date_from",
"source": "openingPeriodStart",
"type": "date"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "DOCDATE",
"id": "DFC65784-9820-5D5D-910D-31976F20F5F6",
"name": "date_until",
"source": "openingPeriodEndExclusive",
"type": "date"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"SOPNUMBE",
"DOCID",
"DOCDATE",
"INVODATE",
"GLPOSTDT",
"DUEDATE",
"DISCDATE",
"CUSTNMBR",
"CUSTNAME",
"CSTPONBR",
"VOIDSTTS",
"VoidContext",
"CURNCYID",
"ORDOCAMT",
"ORSUBTOT",
"ORTDISAM",
"ORFRTAMT",
"ORMISCAMT",
"ORTAXAMT",
"ORDISTKN",
"ORDAVAMT",
"PYMTRMID",
"PRCLEVEL",
"SALSTERR",
"SLPRSNID",
"SHIPMTHD",
"LOCNCODE",
"ReqShipDate",
"ACTLSHIP",
"FUFILDAT",
"ShipToName",
"CNTCPRSN",
"ADDRESS1",
"ADDRESS2",
"ADDRESS3",
"CITY",
"STATE",
"ZIPCODE",
"COUNTRY",
"PHNUMBR1",
"PHNUMBR2",
"PHONE3",
"REFRENCE",
"CurrentCustomerName",
"CustomerState",
"LineState",
"Scope"
],
"sourceID": "5E35A9A8-CC94-5D93-8D74-5C92127D1A1D",
"sqlQuery": "SELECT RTRIM(m.SOPNUMBE) AS SOPNUMBE, m.DOCID AS DOCID, m.DOCDATE AS DOCDATE, m.INVODATE AS INVODATE, m.GLPOSTDT AS GLPOSTDT, m.DUEDATE AS DUEDATE, m.DISCDATE AS DISCDATE, RTRIM(m.CUSTNMBR) AS CUSTNMBR, m.CUSTNAME AS CUSTNAME, m.CSTPONBR AS CSTPONBR, m.VOIDSTTS AS VOIDSTTS, m.CURNCYID AS CURNCYID, CAST(m.ORDOCAMT AS nvarchar(100)) AS ORDOCAMT, CAST(m.ORSUBTOT AS nvarchar(100)) AS ORSUBTOT, CAST(m.ORTDISAM AS nvarchar(100)) AS ORTDISAM, CAST(m.ORFRTAMT AS nvarchar(100)) AS ORFRTAMT, CAST(m.ORMISCAMT AS nvarchar(100)) AS ORMISCAMT, CAST(m.ORTAXAMT AS nvarchar(100)) AS ORTAXAMT, CAST(m.ORDISTKN AS nvarchar(100)) AS ORDISTKN, CAST(m.ORDAVAMT AS nvarchar(100)) AS ORDAVAMT, m.PYMTRMID AS PYMTRMID, m.PRCLEVEL AS PRCLEVEL, m.SALSTERR AS SALSTERR, m.SLPRSNID AS SLPRSNID, m.SHIPMTHD AS SHIPMTHD, m.LOCNCODE AS LOCNCODE, m.ReqShipDate AS ReqShipDate, m.ACTLSHIP AS ACTLSHIP, m.FUFILDAT AS FUFILDAT, m.ShipToName AS ShipToName, m.CNTCPRSN AS CNTCPRSN, m.ADDRESS1 AS ADDRESS1, m.ADDRESS2 AS ADDRESS2, m.ADDRESS3 AS ADDRESS3, m.CITY AS CITY, m.STATE AS STATE, m.ZIPCODE AS ZIPCODE, m.COUNTRY AS COUNTRY, m.PHNUMBR1 AS PHNUMBR1, m.PHNUMBR2 AS PHNUMBR2, m.PHONE3 AS PHONE3, m.REFRENCE AS REFRENCE, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS InvoiceKey, CONCAT(CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),CASE WHEN m.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(m.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN m.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(m.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(m.CURRNIDX AS nvarchar(100)))) END,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 ParentContextToken, CASE WHEN m.VOIDSTTS IS NULL THEN N'Unknown (NULL)' ELSE CASE m.VOIDSTTS WHEN 0 THEN N'Not Voided' WHEN 1 THEN N'Voided' ELSE N'Unknown stored code' END END AS VoidContext, CASE WHEN m.CUSTNMBR IS NOT NULL AND RTRIM(m.CUSTNMBR)<>N'' THEN (SELECT CASE WHEN COUNT(1)=1 THEN MAX(c.CUSTNAME) END FROM dbo.RM00101 c WHERE (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000)))))) END AS CurrentCustomerName, CASE WHEN m.CUSTNMBR IS NULL OR RTRIM(m.CUSTNMBR)=N'' THEN N'Missing invoice customer 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.RM00101 c WHERE (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000)))))) END AS CustomerState, CASE WHEN NOT EXISTS (SELECT 1 FROM dbo.SOP30300 bad WHERE bad.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) AND (bad.CMPNTSEQ IS NULL OR bad.LNITMSEQ IS NULL OR (SELECT COUNT(1) FROM dbo.SOP30300 x WHERE x.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))) AND x.CMPNTSEQ=bad.CMPNTSEQ AND x.LNITMSEQ=bad.LNITMSEQ)<>1)) THEN N'Read on demand; may be empty' ELSE N'Blocked: NULL or duplicate full line identity' END AS LineState, N'SOP history invoice only; not payment or posting certification' AS Scope FROM dbo.SOP30200 m WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.SOP30200 bad WHERE bad.SOPTYPE=3 AND bad.DOCDATE>=:date_from AND bad.DOCDATE<:date_until AND (bad.SOPNUMBE IS NULL OR RTRIM(bad.SOPNUMBE)=N'' OR (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))))<>1)) AND m.SOPTYPE=3 AND m.DOCDATE>=:date_from AND m.DOCDATE<:date_until",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "38F61D5E-BAB6-5BA0-9081-0A7F02099C14",
"key": "LineKey",
"type": "text"
}
],
"id": "D2632C90-1D76-5430-B638-1560B39B346A",
"mappings": [
{
"commonFieldKey": "",
"key": "LineKey",
"label": "Hidden full identity / exact parent context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LineKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentInvoiceKey",
"label": "Hidden full identity / exact parent context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentInvoiceKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SOPNUMBE",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SOPNUMBE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ITEMDESC",
"label": "Item / service description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ITEMDESC",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NONINVEN",
"label": "Stored non-inventory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "NONINVEN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonInventoryContext",
"label": "Documented non-inventory context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "NonInventoryContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UOFM",
"label": "Unit on line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UOFM",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "LOCNCODE",
"label": "Line site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LOCNCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QUANTITY",
"label": "Original quantity (QUANTITY)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QUANTITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QTYORDER",
"label": "Original ordered quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QTYORDER",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QTYCANCE",
"label": "Original canceled quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QTYCANCE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QTYFULFI",
"label": "Original fulfilled quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QTYFULFI",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QTYTOINV",
"label": "Original quantity-to-invoice setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "QTYTOINV",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORUNTPRC",
"label": "Original originating unit price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORUNTPRC",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OXTNDPRC",
"label": "Original originating extended price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OXTNDPRC",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ORUNTCST",
"label": "Original originating unit cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORUNTCST",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OREXTCST",
"label": "Original originating extended cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OREXTCST",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORMRKDAM",
"label": "Original originating markdown amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORMRKDAM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MRKDNTYP",
"label": "Stored markdown type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MRKDNTYP",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MarkdownContext",
"label": "Documented markdown type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MarkdownContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORTDISAM",
"label": "Original originating trade discount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORTDISAM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ORTAXAMT",
"label": "Original originating tax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ORTAXAMT",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentCurrencyContext",
"label": "Parent invoice currency context only",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentCurrencyContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CurrencyState",
"label": "Stored currency-index consistency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CurrencyState",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PRCLEVEL",
"label": "Line price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRCLEVEL",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SHIPMTHD",
"label": "Line shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHIPMTHD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SALSTERR",
"label": "Line sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SALSTERR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SLPRSNID",
"label": "Line salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SLPRSNID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReqShipDate",
"label": "Line requested ship date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReqShipDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ACTLSHIP",
"label": "Line actual ship-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ACTLSHIP",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FUFILDAT",
"label": "Line fulfillment-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FUFILDAT",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToName",
"label": "Line ship-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ShipToName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CNTCPRSN",
"label": "Line ship-to contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CNTCPRSN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS1",
"label": "Line ship-to address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS2",
"label": "Line ship-to address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADDRESS3",
"label": "Line ship-to address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADDRESS3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CITY",
"label": "Line ship-to city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CITY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "STATE",
"label": "Line ship-to state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STATE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZIPCODE",
"label": "Line ship-to postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIPCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "COUNTRY",
"label": "Line ship-to country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COUNTRY",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE1",
"label": "Line ship-to phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE2",
"label": "Line ship-to phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE3",
"label": "Line ship-to phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Scope",
"label": "Line scope — no balance or total inferred",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Scope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Historical invoice lines",
"primaryKey": "LineKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceKey",
"id": "A1B10FF1-E3DA-5599-8FC3-45A805449C9A",
"name": "invoice_ref",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ParentContextToken",
"id": "9D84A204-1C53-5D1B-B0FA-7076C26461C4",
"name": "context_ref",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"SOPNUMBE",
"ITEMNMBR",
"ITEMDESC",
"NONINVEN",
"NonInventoryContext",
"UOFM",
"LOCNCODE",
"QUANTITY",
"QTYORDER",
"QTYCANCE",
"QTYFULFI",
"QTYTOINV",
"ORUNTPRC",
"OXTNDPRC",
"ORUNTCST",
"OREXTCST",
"ORMRKDAM",
"MRKDNTYP",
"MarkdownContext",
"ORTDISAM",
"ORTAXAMT",
"ParentCurrencyContext",
"CurrencyState",
"PRCLEVEL",
"SHIPMTHD",
"SALSTERR",
"SLPRSNID",
"ReqShipDate",
"ACTLSHIP",
"FUFILDAT",
"ShipToName",
"CNTCPRSN",
"ADDRESS1",
"ADDRESS2",
"ADDRESS3",
"CITY",
"STATE",
"ZIPCODE",
"COUNTRY",
"PHONE1",
"PHONE2",
"PHONE3",
"Scope"
],
"sourceID": "5E35A9A8-CC94-5D93-8D74-5C92127D1A1D",
"sqlQuery": "WITH parent AS (SELECT p.SOPNUMBE,p.SOPTYPE,p.CUSTNMBR,p.CURRNIDX,p.CURNCYID FROM dbo.SOP30200 p WHERE p.SOPTYPE=3 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:invoice_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:invoice_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.SOPNUMBE IS NOT NULL AND RTRIM(p.SOPNUMBE)<>N'' AND (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=p.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))))))=1) SELECT RTRIM(l.SOPNUMBE) AS SOPNUMBE, l.ITEMNMBR AS ITEMNMBR, l.ITEMDESC AS ITEMDESC, l.NONINVEN AS NONINVEN, l.UOFM AS UOFM, l.LOCNCODE AS LOCNCODE, CAST(l.QUANTITY AS nvarchar(100)) AS QUANTITY, CAST(l.QTYORDER AS nvarchar(100)) AS QTYORDER, CAST(l.QTYCANCE AS nvarchar(100)) AS QTYCANCE, CAST(l.QTYFULFI AS nvarchar(100)) AS QTYFULFI, CAST(l.QTYTOINV AS nvarchar(100)) AS QTYTOINV, CAST(l.ORUNTPRC AS nvarchar(100)) AS ORUNTPRC, CAST(l.OXTNDPRC AS nvarchar(100)) AS OXTNDPRC, CAST(l.ORUNTCST AS nvarchar(100)) AS ORUNTCST, CAST(l.OREXTCST AS nvarchar(100)) AS OREXTCST, CAST(l.ORMRKDAM AS nvarchar(100)) AS ORMRKDAM, l.MRKDNTYP AS MRKDNTYP, CAST(l.ORTDISAM AS nvarchar(100)) AS ORTDISAM, CAST(l.ORTAXAMT AS nvarchar(100)) AS ORTAXAMT, l.PRCLEVEL AS PRCLEVEL, l.SHIPMTHD AS SHIPMTHD, l.SALSTERR AS SALSTERR, l.SLPRSNID AS SLPRSNID, l.ReqShipDate AS ReqShipDate, l.ACTLSHIP AS ACTLSHIP, l.FUFILDAT AS FUFILDAT, l.ShipToName AS ShipToName, l.CNTCPRSN AS CNTCPRSN, l.ADDRESS1 AS ADDRESS1, l.ADDRESS2 AS ADDRESS2, l.ADDRESS3 AS ADDRESS3, l.CITY AS CITY, l.STATE AS STATE, l.ZIPCODE AS ZIPCODE, l.COUNTRY AS COUNTRY, l.PHONE1 AS PHONE1, l.PHONE2 AS PHONE2, l.PHONE3 AS PHONE3, CONCAT(LEN(RTRIM(CAST(l.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(l.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(l.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(l.SOPTYPE AS nvarchar(100))),LEN(RTRIM(CAST(l.CMPNTSEQ AS nvarchar(100)))),N':',RTRIM(CAST(l.CMPNTSEQ AS nvarchar(100))),LEN(RTRIM(CAST(l.LNITMSEQ AS nvarchar(100)))),N':',RTRIM(CAST(l.LNITMSEQ AS nvarchar(100)))) AS LineKey, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS ParentInvoiceKey, CASE WHEN l.NONINVEN IS NULL THEN N'Unknown (NULL)' ELSE CASE l.NONINVEN WHEN 0 THEN N'No' WHEN 1 THEN N'Yes' ELSE N'Unknown stored code' END END AS NonInventoryContext, CASE WHEN l.MRKDNTYP IS NULL THEN N'Unknown (NULL)' ELSE CASE l.MRKDNTYP WHEN 0 THEN N'Percentage' WHEN 1 THEN N'Amount' ELSE N'Unknown stored code' END END AS MarkdownContext, CASE WHEN l.CURRNIDX IS NOT NULL AND m.CURRNIDX IS NOT NULL AND l.CURRNIDX=m.CURRNIDX AND m.CURNCYID IS NOT NULL AND RTRIM(m.CURNCYID)<>N'' THEN RTRIM(m.CURNCYID) END AS ParentCurrencyContext, CASE WHEN l.CURRNIDX IS NULL OR m.CURRNIDX IS NULL THEN N'Unknown: missing currency index' WHEN l.CURRNIDX<>m.CURRNIDX THEN N'Warning: line and invoice currency indices differ' WHEN m.CURNCYID IS NULL OR RTRIM(m.CURNCYID)=N'' THEN N'Unknown: parent currency ID absent' ELSE N'Equal stored indices; parent context only, not ISO proof' END AS CurrencyState, N'Original historical line; quantities and costs are not outstanding balance or margin' AS Scope FROM dbo.SOP30300 l JOIN parent m ON l.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(l.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(l.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dbo.SOP30300 bad WHERE bad.SOPTYPE=m.SOPTYPE AND (RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.SOPNUMBE AS nvarchar(4000))))) AND (bad.CMPNTSEQ IS NULL OR bad.LNITMSEQ IS NULL OR (SELECT COUNT(1) FROM dbo.SOP30300 x WHERE x.SOPTYPE=bad.SOPTYPE AND (RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(x.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(bad.SOPNUMBE AS nvarchar(4000))))) AND x.CMPNTSEQ=bad.CMPNTSEQ AND x.LNITMSEQ=bad.LNITMSEQ)<>1))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "EE9C52E5-1620-5AD5-8414-930D36846212",
"key": "CUSTNMBR",
"type": "text"
}
],
"id": "84495EE4-44B6-5152-B122-1ED675EF8EEB",
"mappings": [
{
"commonFieldKey": "",
"key": "ParentInvoiceKey",
"label": "Hidden full identity / exact parent context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentInvoiceKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CUSTNMBR",
"label": "Customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CUSTNMBR",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CUSTNAME",
"label": "Customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CUSTNAME",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CUSTCLAS",
"label": "Customer class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CUSTCLAS",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "STMTNAME",
"label": "Statement name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STMTNAME",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SHRTNAME",
"label": "Short name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHRTNAME",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CNTCPRSN",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CNTCPRSN",
"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": "COUNTRY",
"label": "Country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "COUNTRY",
"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": "ZIP",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ZIP",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PHONE1",
"label": "Phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE1",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PHONE2",
"label": "Phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PHONE2",
"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": "FAX",
"label": "Fax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FAX",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ADRSCODE",
"label": "Master address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ADRSCODE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PRBTADCD",
"label": "Default billing address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRBTADCD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PRSTADCD",
"label": "Default shipping address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRSTADCD",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "STADDRCD",
"label": "Statement address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "STADDRCD",
"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": "UPSZONE",
"label": "UPS zone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UPSZONE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SLPRSNID",
"label": "Salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SLPRSNID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SALSTERR",
"label": "Sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SALSTERR",
"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": "PRCLEVEL",
"label": "Price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PRCLEVEL",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"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": "CPRCSTNM",
"label": "National-account parent code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CPRCSTNM",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "INACTIVE",
"label": "Stored inactive flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "INACTIVE",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "HOLD",
"label": "Stored hold flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "HOLD",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"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": "SHIPCOMPLETE",
"label": "Complete shipments only (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SHIPCOMPLETE",
"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": "MasterScope",
"label": "Current customer master context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "MasterScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Current invoice customer",
"primaryKey": "CUSTNMBR",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceKey",
"id": "A1B10FF1-E3DA-5599-8FC3-45A805449C9A",
"name": "invoice_ref",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ParentContextToken",
"id": "9D84A204-1C53-5D1B-B0FA-7076C26461C4",
"name": "context_ref",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"CUSTNMBR",
"CUSTNAME",
"CUSTCLAS",
"STMTNAME",
"SHRTNAME",
"CNTCPRSN",
"ADDRESS1",
"ADDRESS2",
"ADDRESS3",
"COUNTRY",
"CITY",
"STATE",
"ZIP",
"PHONE1",
"PHONE2",
"PHONE3",
"FAX",
"ADRSCODE",
"PRBTADCD",
"PRSTADCD",
"STADDRCD",
"SHIPMTHD",
"UPSZONE",
"SLPRSNID",
"SALSTERR",
"PYMTRMID",
"PRCLEVEL",
"CURNCYID",
"RATETPID",
"CPRCSTNM",
"INACTIVE",
"HOLD",
"COMMENT1",
"COMMENT2",
"SHIPCOMPLETE",
"DISGRPER",
"DUEGRPER",
"MasterScope"
],
"sourceID": "5E35A9A8-CC94-5D93-8D74-5C92127D1A1D",
"sqlQuery": "WITH parent AS (SELECT p.SOPNUMBE,p.SOPTYPE,p.CUSTNMBR,p.CURRNIDX,p.CURNCYID FROM dbo.SOP30200 p WHERE p.SOPTYPE=3 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:invoice_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:invoice_ref AS nvarchar(4000))))) AND (RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(CONCAT(LEN(RTRIM(CAST(p.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(p.SOPTYPE AS nvarchar(100)))),CASE WHEN p.CUSTNMBR IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))),N':',RTRIM(CAST(p.CUSTNMBR AS nvarchar(100)))) END,CASE WHEN p.CURRNIDX IS NULL THEN N'NULL' ELSE CONCAT(LEN(RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))),N':',RTRIM(CAST(p.CURRNIDX AS nvarchar(100)))) END,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(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000))))) AND p.SOPNUMBE IS NOT NULL AND RTRIM(p.SOPNUMBE)<>N'' AND (SELECT COUNT(1) FROM dbo.SOP30200 other WHERE other.SOPTYPE=p.SOPTYPE AND (RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.SOPNUMBE AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.SOPNUMBE AS nvarchar(4000))))))=1) SELECT RTRIM(c.CUSTNMBR) AS CUSTNMBR, c.CUSTNAME AS CUSTNAME, c.CUSTCLAS AS CUSTCLAS, c.STMTNAME AS STMTNAME, c.SHRTNAME AS SHRTNAME, c.CNTCPRSN AS CNTCPRSN, c.ADDRESS1 AS ADDRESS1, c.ADDRESS2 AS ADDRESS2, c.ADDRESS3 AS ADDRESS3, c.COUNTRY AS COUNTRY, c.CITY AS CITY, c.STATE AS STATE, c.ZIP AS ZIP, c.PHONE1 AS PHONE1, c.PHONE2 AS PHONE2, c.PHONE3 AS PHONE3, c.FAX AS FAX, c.ADRSCODE AS ADRSCODE, c.PRBTADCD AS PRBTADCD, c.PRSTADCD AS PRSTADCD, c.STADDRCD AS STADDRCD, c.SHIPMTHD AS SHIPMTHD, c.UPSZONE AS UPSZONE, c.SLPRSNID AS SLPRSNID, c.SALSTERR AS SALSTERR, c.PYMTRMID AS PYMTRMID, c.PRCLEVEL AS PRCLEVEL, c.CURNCYID AS CURNCYID, c.RATETPID AS RATETPID, c.CPRCSTNM AS CPRCSTNM, c.INACTIVE AS INACTIVE, c.HOLD AS HOLD, c.COMMENT1 AS COMMENT1, c.COMMENT2 AS COMMENT2, c.SHIPCOMPLETE AS SHIPCOMPLETE, c.DISGRPER AS DISGRPER, c.DUEGRPER AS DUEGRPER, CONCAT(LEN(RTRIM(CAST(m.SOPNUMBE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPNUMBE AS nvarchar(100))),LEN(RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))),N':',RTRIM(CAST(m.SOPTYPE AS nvarchar(100)))) AS ParentInvoiceKey, N'Current RM00101 master; not a historical invoice snapshot' AS MasterScope FROM dbo.RM00101 c JOIN parent m ON (RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.CUSTNMBR AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.SOP30200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SOP30300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.RM00101',N'U') IS NOT NULL AND m.CUSTNMBR IS NOT NULL AND RTRIM(m.CUSTNMBR)<>N'' AND (SELECT COUNT(1) FROM dbo.RM00101 other WHERE (RTRIM(CAST(other.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(other.CUSTNMBR AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(c.CUSTNMBR AS nvarchar(4000))))))=1",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "44639CAA-FBEC-563D-BED9-B9AE9C868EA7",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "62FECDD0-F00E-5C1C-90A5-3A3C6675B97E",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "D2632C90-1D76-5430-B638-1560B39B346A",
"title": "Invoice lines",
"urlKey": ""
},
{
"id": "807E2488-BB81-5E0F-8247-EDD84A4A33D5",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "55DC7FB1-DDC5-5F2B-889A-B987BDC105EB",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "84495EE4-44B6-5152-B122-1ED675EF8EEB",
"title": "Current customer",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DOCDATE",
"label": "Document date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DUEDATE",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "VoidContext",
"label": "Void status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Invoice currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ORDOCAMT",
"label": "Originating amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CSTPONBR",
"label": "Customer PO",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Terms",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LineState",
"label": "Lines",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "05F7E858-E8E3-5CB1-8FFE-A130466C86EA",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "SOPNUMBE",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "DOCID",
"label": "Document ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "CUSTNAME",
"label": "Customer name stored on invoice",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "CUSTNMBR",
"label": "Invoice customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentCustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerState",
"label": "Current customer master availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice identity and customer context",
"detailRole": "information",
"isVisible": true,
"key": "Scope",
"label": "Historical invoice scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "VoidContext",
"label": "Documented void context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "VOIDSTTS",
"label": "Stored void code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "DOCDATE",
"label": "Document date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "INVODATE",
"label": "Stored invoice-date setting (INVODATE)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "GLPOSTDT",
"label": "Stored GL posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "DUEDATE",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "DISCDATE",
"label": "Discount date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Document state and dates",
"detailRole": "information",
"isVisible": true,
"key": "LineState",
"label": "Full line identity availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Invoice currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORDOCAMT",
"label": "Original originating document amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORSUBTOT",
"label": "Original originating subtotal",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORTDISAM",
"label": "Original originating trade discount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORFRTAMT",
"label": "Original originating freight",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORMISCAMT",
"label": "Original originating miscellaneous amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORTAXAMT",
"label": "Original originating tax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORDISTKN",
"label": "Original originating discount taken",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — invoice own currency",
"detailRole": "information",
"isVisible": true,
"key": "ORDAVAMT",
"label": "Original originating discount available",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "CSTPONBR",
"label": "Customer purchase-order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "REFRENCE",
"label": "Stored document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Invoice payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "PRCLEVEL",
"label": "Invoice price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "SLPRSNID",
"label": "Invoice salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "SALSTERR",
"label": "Invoice sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "SHIPMTHD",
"label": "Invoice shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice commercial references",
"detailRole": "information",
"isVisible": true,
"key": "LOCNCODE",
"label": "Invoice site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice delivery dates and identity",
"detailRole": "information",
"isVisible": true,
"key": "ReqShipDate",
"label": "Requested ship date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Invoice delivery dates and identity",
"detailRole": "information",
"isVisible": true,
"key": "ACTLSHIP",
"label": "Actual ship-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Invoice delivery dates and identity",
"detailRole": "information",
"isVisible": true,
"key": "FUFILDAT",
"label": "Fulfillment-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Invoice delivery dates and identity",
"detailRole": "information",
"isVisible": true,
"key": "ShipToName",
"label": "Invoice ship-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice delivery dates and identity",
"detailRole": "information",
"isVisible": true,
"key": "CNTCPRSN",
"label": "Invoice ship-to contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Invoice ship-to address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS2",
"label": "Invoice ship-to address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS3",
"label": "Invoice ship-to address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "Invoice ship-to city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "Invoice ship-to state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Invoice ship-to postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "COUNTRY",
"label": "Invoice ship-to country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR1",
"label": "Invoice ship-to phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHNUMBR2",
"label": "Invoice ship-to phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Invoice ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE3",
"label": "Invoice ship-to phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "6888F241-6D13-5D9C-999B-6AAF7074684A",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "CD9A3724-A654-5781-8974-0DD041364191",
"includeAllOption": false,
"key": "DOCDATE",
"title": "Document-date period",
"type": "date"
},
{
"id": "8F452090-1A48-5CF7-A854-38DC6D1BD6ED",
"includeAllOption": true,
"key": "CUSTNMBR",
"title": "Invoice customer",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "descending",
"id": "9BA8DDF4-6C48-5D38-A2BA-1E3FF9E7AE5C",
"key": "DOCDATE",
"type": "date"
},
{
"direction": "ascending",
"id": "9568BE68-FB06-5FE2-AFDE-6F2535812617",
"key": "SOPNUMBE",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CUSTNAME",
"systemImage": "doc.text",
"title": "Historical customer invoice dossier",
"titleKey": "SOPNUMBE"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QUANTITY",
"label": "Original quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UOFM",
"label": "Unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ORUNTPRC",
"label": "Originating unit price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OXTNDPRC",
"label": "Originating line price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ParentCurrencyContext",
"label": "Parent currency context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrencyState",
"label": "Currency consistency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "LOCNCODE",
"label": "Site",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "NonInventoryContext",
"label": "Non-inventory",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "D2632C90-1D76-5430-B638-1560B39B346A",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "ITEMDESC",
"label": "Item / service description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "ITEMNMBR",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "SOPNUMBE",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "NONINVEN",
"label": "Stored non-inventory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "NonInventoryContext",
"label": "Documented non-inventory context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "UOFM",
"label": "Unit on line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "LOCNCODE",
"label": "Line site code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "QUANTITY",
"label": "Original quantity (QUANTITY)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "QTYORDER",
"label": "Original ordered quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "QTYCANCE",
"label": "Original canceled quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "QTYFULFI",
"label": "Original fulfilled quantity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original item and quantity",
"detailRole": "information",
"isVisible": true,
"key": "QTYTOINV",
"label": "Original quantity-to-invoice setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ORUNTPRC",
"label": "Original originating unit price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "OXTNDPRC",
"label": "Original originating extended price",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ORUNTCST",
"label": "Original originating unit cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "OREXTCST",
"label": "Original originating extended cost",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ORMRKDAM",
"label": "Original originating markdown amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "MRKDNTYP",
"label": "Stored markdown type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "MarkdownContext",
"label": "Documented markdown type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ORTDISAM",
"label": "Original originating trade discount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ORTAXAMT",
"label": "Original originating tax",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "ParentCurrencyContext",
"label": "Parent invoice currency context only",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "CurrencyState",
"label": "Stored currency-index consistency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original amounts — no conversion or totals",
"detailRole": "information",
"isVisible": true,
"key": "Scope",
"label": "Line scope — no balance or total inferred",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "PRCLEVEL",
"label": "Line price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "SHIPMTHD",
"label": "Line shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "SLPRSNID",
"label": "Line salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "SALSTERR",
"label": "Line sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "ReqShipDate",
"label": "Line requested ship date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "ACTLSHIP",
"label": "Line actual ship-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Line commercial and delivery context",
"detailRole": "information",
"isVisible": true,
"key": "FUFILDAT",
"label": "Line fulfillment-date setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Line ship-to identity",
"detailRole": "information",
"isVisible": true,
"key": "ShipToName",
"label": "Line ship-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to identity",
"detailRole": "information",
"isVisible": true,
"key": "CNTCPRSN",
"label": "Line ship-to contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS1",
"label": "Line ship-to address line 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS2",
"label": "Line ship-to address line 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ADDRESS3",
"label": "Line ship-to address line 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "CITY",
"label": "Line ship-to city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "STATE",
"label": "Line ship-to state / region",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "ZIPCODE",
"label": "Line ship-to postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "COUNTRY",
"label": "Line ship-to country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE1",
"label": "Line ship-to phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE2",
"label": "Line ship-to phone 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Line ship-to address and contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE3",
"label": "Line ship-to phone 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "6857DD5C-6F4F-5A20-AC39-9D3026E1AD2B",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "38F61D5E-BAB6-5BA0-9081-0A7F02099C14",
"key": "LineKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ITEMNMBR",
"systemImage": "list.bullet",
"title": "Historical invoice lines",
"titleKey": "ITEMDESC"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CNTCPRSN",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PHONE1",
"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": "CUSTCLAS",
"label": "Customer 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": "Stored hold (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "INACTIVE",
"label": "Stored inactive (1=yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "84495EE4-44B6-5152-B122-1ED675EF8EEB",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "CUSTNAME",
"label": "Customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "CUSTNMBR",
"label": "Customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "SHRTNAME",
"label": "Short name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "STMTNAME",
"label": "Statement name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Identity and classification",
"detailRole": "information",
"isVisible": true,
"key": "CUSTCLAS",
"label": "Customer class",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "CNTCPRSN",
"label": "Contact",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE1",
"label": "Phone 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Primary contact",
"detailRole": "information",
"isVisible": true,
"key": "PHONE2",
"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": "FAX",
"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": "ZIP",
"label": "Postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "COUNTRY",
"label": "Country",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Master address",
"detailRole": "information",
"isVisible": true,
"key": "ADRSCODE",
"label": "Master address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "PRBTADCD",
"label": "Default billing address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "PRSTADCD",
"label": "Default shipping address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "STADDRCD",
"label": "Statement address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "SHIPMTHD",
"label": "Shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "UPSZONE",
"label": "UPS zone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Billing and delivery defaults",
"detailRole": "information",
"isVisible": true,
"key": "SHIPCOMPLETE",
"label": "Complete shipments only (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "SLPRSNID",
"label": "Salesperson code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "SALSTERR",
"label": "Sales territory code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "PYMTRMID",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "PRCLEVEL",
"label": "Price level code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "DISGRPER",
"label": "Discount grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "DUEGRPER",
"label": "Due-date grace days",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "CURNCYID",
"label": "Default currency ID",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Commercial defaults",
"detailRole": "information",
"isVisible": true,
"key": "RATETPID",
"label": "Exchange-rate type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored flags and national account",
"detailRole": "information",
"isVisible": true,
"key": "HOLD",
"label": "Stored hold flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored flags and national account",
"detailRole": "information",
"isVisible": true,
"key": "INACTIVE",
"label": "Stored inactive flag (1 = yes)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored flags and national account",
"detailRole": "information",
"isVisible": true,
"key": "CPRCSTNM",
"label": "National-account parent code",
"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 customer context",
"detailRole": "information",
"isVisible": true,
"key": "MasterScope",
"label": "Current customer master context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "89388679-63FD-51F9-92E4-6DBF6A5BE64A",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "EE9C52E5-1620-5AD5-8414-930D36846212",
"key": "CUSTNMBR",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CUSTNMBR",
"systemImage": "person.2",
"title": "Current invoice customer",
"titleKey": "CUSTNAME"
}
],
"relations": [
{
"childDatasetID": "D2632C90-1D76-5430-B638-1560B39B346A",
"childKey": "ParentInvoiceKey",
"id": "62FECDD0-F00E-5C1C-90A5-3A3C6675B97E",
"name": "Invoice lines",
"parentDatasetID": "05F7E858-E8E3-5CB1-8FFE-A130466C86EA",
"parentKey": "InvoiceKey"
},
{
"childDatasetID": "84495EE4-44B6-5152-B122-1ED675EF8EEB",
"childKey": "ParentInvoiceKey",
"id": "55DC7FB1-DDC5-5F2B-889A-B987BDC105EB",
"name": "Current customer",
"parentDatasetID": "05F7E858-E8E3-5CB1-8FFE-A130466C86EA",
"parentKey": "InvoiceKey"
}
],
"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. The selected SOP30200/SOP30300/RM00101 fields, native captions and logical identities come from Microsoft GP source registrations and AL staging definitions at commit e7ed235bc979c0283306e9639ff7cd22ce78341d. These are not installed SQL DDL, certified physical keys/relations, ERP ACL, current edition or execution plans. The proposed invoice/line relation uses SOPTYPE plus full SOPNUMBE; line identity also includes CMPNTSEQ and LNITMSEQ. Validate actual objects, types, relationships, source permissions and originating interpretations with the import read test and known invoices before use.\n\nFor sales, customer service, receivables teams and managers: select a document-date period and optionally a customer. Inspect the historical invoice, customer PO, due/discount dates, independent void flag, original originating amounts, commercial references and invoice ship-to. Open its own item/service lines with units, native quantities, prices/costs and line-specific delivery context, or the current customer dossier with contacts, addresses and commercial defaults.\n\nWhy Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.\n\nPro: one authorised SQL company source, one Home, three lists, two on-demand related buttons and explicit read-only SQL. No SELECT *, credential, business row or executable code is embedded. Mandatory start/inclusive and end/exclusive document dates and optional customer selection precede the 1,000-row root cap. Children have a separate 1,000-row cap and revalidate full invoice identity plus its customer/currency context; they are not pruned by the parent opening date period. Narrowing filters does not guarantee a cheap scan. Reaching a cap may mean incomplete data; no scheduled refresh is enabled.\n\nThis is SOP history invoices only (SOPTYPE=3), including independently displayed void status. It is not all sales documents, Work transactions, RM open receivables, invoice-payment application, an unpaid-balance report or proof of posting. History depends on GP retention settings; voided documents can appear in Work and History simultaneously. No union, payment reconstruction, aging or SOPSTATUS sales-order legend is applied.\n\nAmounts and quantities preserve source text, NULL, zero, sign and decimal precision; local sorting is textual, not numeric aggregation. Original originating-labelled document amount, subtotal, discount/freight/misc/tax, unit/extended price and cost are shown without total, margin, FX or tax reconstruction. ORDAVAMT and ORDISTKN remain native originating fields, not an unpaid-balance formula. Native quantity roles remain separate; no remaining-to-deliver/invoice formula or unit conversion.\n\nSOP30300 has a stored CURRNIDX, not its own selected CURNCYID. The line shows only parent invoice currency context when both stored indices match and the parent currency ID exists. Missing or mismatched indices show the reason and leave that context empty. Equality is not certification of a currency dictionary, ISO currency, actual cost valuation or conversion. Never substitute the current customer default. Header/line ship-to data are stored document contexts, not the customer master. Native date settings retain their source roles; an empty or sentinel date is not silently converted to a factual event.\n\nCurrent RM00101 data is read separately and may differ from the invoice snapshot. Missing/ambiguous customer metadata preserves the invoice with an explicit state, without an arbitrary first match. Invalid or duplicate full line identities block that whole child, not a cosmetically partial list. Invalid/duplicate invoice identities block the selected-period root read. A changed parent customer/currency context blocks stale child bindings until the invoice is reread. Business codes preserve leading zeroes, case and leading spaces; only right padding is removed. Binary Unicode comparisons do not certify installed SQL constraints or collation.\n\nUse a separately authorised least-privilege SELECT reporting login on allowed company data, not DYNAMICS, BC staging, sa, sysadmin or DYNGRP. GP application passwords are transformed; obtain an authorised SQL login. Cifru filters are not access control and do not inherit GP permissions. Commercial/contact/cost information may be confidential. Screenshots use native Cifru with wholly fictional DEMO rows and synthetic transport, not live GP or SQL Server. Unofficial, not endorsed by Microsoft.\n\nDEMO includes deliberate missing, unknown and currency-inconsistent source records to illustrate safeguards; it is not a reconciled accounting invoice.\n\nPrimary registration: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/codeunits/GPCloudMigration.codeunit.al\nHeader: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/History/GPSOPTrxHist.table.al\nLine: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/History/GPSOPTrxAmountsHist.table.al\nCustomer: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/Customers/GPRM00101.Table.al\nTransaction context: https://learn.microsoft.com/en-us/dynamics-gp/distribution/sales-order-processing-part2-transaction-entry\nHistory: https://learn.microsoft.com/en-us/dynamics-gp/distribution/sales-order-processing-part4-transaction-activity\nWork/history caveat: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/token-startelement-in-state-endrootelement-would-result-in-invalid-xml-document-error",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "E22C4ECC-5A7A-58F5-9D79-857921758F34",
"rootButtonCount": 1,
"summary": "Period-filtered historical invoice dossiers with original amounts, due dates, on-demand lines and current customers.",
"tags": [
"Dynamics GP",
"SQL Server",
"Customer invoices",
"Sales",
"Receivables",
"History",
"Pro"
],
"title": "Historical customer invoices, lines and current customer — Pro"
}
}