Current receivables and customer dossiers — Pro
Current retained item balances and due dates; complete context and separate current customer roles.
NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial configuration based on Sage 100 US 2026 FLOR Rel 7.50 own open-item layouts, complete logical keys, public functional help and synthetic validation. Verify installed schema, currency, NULL, roles, date adapters, padding, collation, least-privilege SELECT permissions and query cost at import. Not a real Sage SQL Server test.
Inspect current retained customer open items, native item balances, due/discount/posting dates, document types, payments-today, credit references and sold-to context. Open independent current account-customer and sold-to customer dossiers without replacing a missing role.
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 source, 3 lists, 2 lazy dossier buttons, parameterized read-only SELECT and at most 2,000 rows per request. No scheduled refresh. No implicit Last 7 days or Balance<>0 filter: old debts, retained zero-balance rows and unrecognized dates remain available. Oldest due dates sort first with a complete-identity tie break; NULL dates can sort ahead of dated rows. Local Search, Filters and sorting affect loaded rows only, not the entire source. A 2,000-row limit hit means the list may be incomplete; adapt source-side filtering before relying on totals. No fabricated aggregate total or full-catalog claim.
CURRENT native open-item records, not a historical closing balance, a reconstructed aging report or a complete payment ledger. Invoice/due-date filtering selects current retained records; it does not show the balance as of the selected date. Retention, purges and balance-forward accounting options affect availability. Source changes between reads are not a transaction-consistent snapshot. A missing row or master is not evidence of no debt or a zero balance.
Balance, original invoice/tax/freight components, payments today, discount and retention are separate native values. NULL remains NULL, never zero. No general paid/unpaid status, source currency code, USD assumption, universal debit/credit sign normalization or reconstructed amount due. Monetary labels refer to source currency; verify its meaning in your company setup.
A/R open-item types use only their own legend: CM Credit Memo, DM Debit Memo, FC Finance Charge, IN Invoice, PP Prepayment, PY Payment, BC Balance forward other Charges, BF Balance Forward. Unknown codes stay unknown; no AD/CA/XD history legend is borrowed. BC/BF are balance-forward context, not ordinary commercial invoices. Full identity is ARDivisionNo + CustomerNo + InvoiceNo + InvoiceType. Sold-to uses its explicitly stored division and customer; a missing sold-to master is not replaced by the account customer.
Master contacts, payment terms, balances and last payment values are CURRENT, separately labeled from this individual open item. InvoiceHistoryHeaderSeqNo is kept internally but does not certify a history or line linkage; no partial invoice-number join is provided. Technical keys are hidden. Exact binary Unicode and byte-length comparisons preserve complete strings, but installed physical types, padding, collation and performance still require validation. SQL Server 2012+/compatibility 110+ is required for defensive date conversion; invalid or unsupported dates become NULL, not 1900, with interpretation labels.
No source credentials, server addresses, cached business rows, banking/check/taxpayer data or audit-user identifiers in the package. DEMO captures must be wholly fictional. Public layouts prove selected logical names and keys, not installed DDL/indexes or speed. Native ERP operator permissions do not carry into direct SQL: separately authorized least-privilege SELECT is required. Unofficial, not endorsed by Sage; not France, Contractor or ProvideX.
Own open-item layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/Accounts_Receivable/AR_OpenInvoice.htm
Own master layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/Accounts_Receivable/AR_Customer.htm
Screenshots
What this package creates
1 Home2 Details3 lists1 sources to map
- Home: Current receivables
- Details: Account customer dossier
- Details: Sold-to customer dossier
- Sub-button: Account customer dossier
- Sub-button: Sold-to customer dossier
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 o.ARDivisionNo, o.CustomerNo, o.InvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) ELSE NULL END,112) AS InvoiceDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) ELSE NULL END,112) AS InvoiceDueDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),8) ELSE NULL END,112) AS InvoiceDiscountDate, o.InvoiceHistoryHeaderSeqNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, o.TermsCode, o.Comment, o.CreditMemoInvoiceReference, o.JobNo, o.CustomerPONo, o.PostingReference, o.SoldToDivisionNo, o.SoldToCustomerNo, o.TaxableAmt, o.NonTaxableAmt, o.FreightAmt, o.SalesTaxAmt, o.CostOfSalesAmt, o.DiscountAmt, o.PaymentsToday, o.Balance, o.RetentionAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo)),N':',CONVERT(nvarchar(4000), o.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo)),N':',CONVERT(nvarchar(4000), o.CustomerNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.InvoiceNo)),N':',CONVERT(nvarchar(4000), o.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.InvoiceType)),N':',CONVERT(nvarchar(4000), o.InvoiceType),N'|') AS OpenItemKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo)),N':',CONVERT(nvarchar(4000), o.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo)),N':',CONVERT(nvarchar(4000), o.CustomerNo),N'|') AS AccountKey, COALESCE(NULLIF(m.CustomerName,N''),o.CustomerNo) AS PartyDisplayName, CASE WHEN m.CustomerNo IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS PartyAvailability, CASE WHEN o.InvoiceDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS InvoiceDateReadState, CASE WHEN o.InvoiceDueDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DueDateReadState, CASE o.InvoiceType WHEN N'CM' THEN N'Credit Memo' WHEN N'DM' THEN N'Debit Memo' WHEN N'FC' THEN N'Finance Charge' WHEN N'IN' THEN N'Invoice' WHEN N'PP' THEN N'Prepayment' WHEN N'PY' THEN N'Payment' WHEN N'BC' THEN N'Balance forward other Charges' WHEN N'BF' THEN N'Balance Forward' ELSE CONCAT(N'Unknown / unset: ', o.InvoiceType) END AS InvoiceKind, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.SoldToDivisionNo)),N':',CONVERT(nvarchar(4000), o.SoldToDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.SoldToCustomerNo)),N':',CONVERT(nvarchar(4000), o.SoldToCustomerNo),N'|') AS SoldToKey FROM dbo.AR_OpenInvoice o LEFT JOIN dbo.AR_Customer m ON (CONVERT(nvarchar(4000), o.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))) AND (CONVERT(nvarchar(4000), o.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))) WHERE o.ARDivisionNo IS NOT NULL AND o.CustomerNo IS NOT NULL AND o.InvoiceNo IS NOT NULL AND o.InvoiceType IS NOT NULL
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT m.ARDivisionNo, m.CustomerNo, m.CustomerName, m.AddressLine1, m.AddressLine2, m.City, m.State, m.ZipCode, m.CountryCode, m.TelephoneNo, m.EmailAddress, m.ShipMethod, m.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, m.CustomerDiscountRate, m.CreditLimit, m.LastPaymentAmt, m.CurrentBalance, m.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo)),N':',CONVERT(nvarchar(4000), m.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo)),N':',CONVERT(nvarchar(4000), m.CustomerNo),N'|') AS AccountKey, CASE m.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', m.CustomerStatus) END AS CustomerStatusName, m.CreditHold AS CreditHoldName FROM dbo.AR_Customer m WHERE (CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :party) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :party)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQuerySELECT m.ARDivisionNo, m.CustomerNo, m.CustomerName, m.AddressLine1, m.AddressLine2, m.City, m.State, m.ZipCode, m.CountryCode, m.TelephoneNo, m.EmailAddress, m.ShipMethod, m.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, m.CustomerDiscountRate, m.CreditLimit, m.LastPaymentAmt, m.CurrentBalance, m.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo)),N':',CONVERT(nvarchar(4000), m.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo)),N':',CONVERT(nvarchar(4000), m.CustomerNo),N'|') AS SoldToKey, CASE m.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', m.CustomerStatus) END AS CustomerStatusName, m.CreditHold AS CreditHoldName FROM dbo.AR_Customer m WHERE (CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :party) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :party)))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 100 US — SQL Server company database",
"id": "C466CFDB-FFDA-5EBC-BC15-16353BC77CC6",
"kind": "sqlServer",
"requiredObjects": [
"dbo.AR_Customer",
"dbo.AR_OpenInvoice"
],
"requiresCustomSQL": true
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "FB11735C-3787-5AA4-90B5-0E7CED9BA057",
"key": "InvoiceDueDate",
"type": "date"
},
{
"direction": "ascending",
"id": "B964634B-49A9-5A8F-BDF3-CFF9848AE88D",
"key": "OpenItemKey",
"type": "text"
}
],
"id": "053B6508-7162-528A-BB87-81CAA991563A",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "ARDivisionNo",
"label": "Internal customer division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ARDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerNo",
"label": "Account customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceDate",
"label": "Invoice / reference date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "InvoiceDueDate",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDueDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "InvoiceDiscountDate",
"label": "Discount date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDiscountDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceHistoryHeaderSeqNo",
"label": "Internal retained invoice history sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceHistoryHeaderSeqNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Stored payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Comment",
"label": "Retained open-item note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Comment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CreditMemoInvoiceReference",
"label": "Credit memo invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditMemoInvoiceReference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JobNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerPONo",
"label": "Customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerPONo",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PostingReference",
"label": "Posting reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingReference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SoldToDivisionNo",
"label": "Internal sold-to division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SoldToDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SoldToCustomerNo",
"label": "Stored sold-to customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SoldToCustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxableAmt",
"label": "Native taxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonTaxableAmt",
"label": "Native nontaxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonTaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FreightAmt",
"label": "Native freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FreightAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesTaxAmt",
"label": "Sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesTaxAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CostOfSalesAmt",
"label": "Native cost of sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CostOfSalesAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DiscountAmt",
"label": "Native discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DiscountAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PaymentsToday",
"label": "Native payments today — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PaymentsToday",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Balance",
"label": "Current item balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Balance",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "RetentionAmt",
"label": "Native retention — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RetentionAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OpenItemKey",
"label": "Internal complete open-item identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OpenItemKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountKey",
"label": "Internal complete account identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartyDisplayName",
"label": "Current account name / stored code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartyDisplayName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartyAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartyAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceDateReadState",
"label": "Invoice date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDateReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DueDateReadState",
"label": "Due date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DueDateReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceKind",
"label": "Open-item type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceKind",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SoldToKey",
"label": "Internal complete sold-to identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SoldToKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Current receivables",
"primaryKey": "OpenItemKey",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"CustomerNo",
"InvoiceNo",
"TermsCode",
"Comment",
"CreditMemoInvoiceReference",
"JobNo",
"CustomerPONo",
"PostingReference",
"SoldToCustomerNo",
"PartyDisplayName",
"PartyAvailability",
"InvoiceDateReadState",
"DueDateReadState",
"InvoiceKind"
],
"sourceID": "C466CFDB-FFDA-5EBC-BC15-16353BC77CC6",
"sqlQuery": "SELECT o.ARDivisionNo, o.CustomerNo, o.InvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) ELSE NULL END,112) AS InvoiceDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) ELSE NULL END,112) AS InvoiceDueDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDiscountDate, 112))),8) ELSE NULL END,112) AS InvoiceDiscountDate, o.InvoiceHistoryHeaderSeqNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, o.TermsCode, o.Comment, o.CreditMemoInvoiceReference, o.JobNo, o.CustomerPONo, o.PostingReference, o.SoldToDivisionNo, o.SoldToCustomerNo, o.TaxableAmt, o.NonTaxableAmt, o.FreightAmt, o.SalesTaxAmt, o.CostOfSalesAmt, o.DiscountAmt, o.PaymentsToday, o.Balance, o.RetentionAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo)),N':',CONVERT(nvarchar(4000), o.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo)),N':',CONVERT(nvarchar(4000), o.CustomerNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.InvoiceNo)),N':',CONVERT(nvarchar(4000), o.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.InvoiceType)),N':',CONVERT(nvarchar(4000), o.InvoiceType),N'|') AS OpenItemKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo)),N':',CONVERT(nvarchar(4000), o.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo)),N':',CONVERT(nvarchar(4000), o.CustomerNo),N'|') AS AccountKey, COALESCE(NULLIF(m.CustomerName,N''),o.CustomerNo) AS PartyDisplayName, CASE WHEN m.CustomerNo IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS PartyAvailability, CASE WHEN o.InvoiceDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS InvoiceDateReadState, CASE WHEN o.InvoiceDueDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), o.InvoiceDueDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DueDateReadState, CASE o.InvoiceType WHEN N'CM' THEN N'Credit Memo' WHEN N'DM' THEN N'Debit Memo' WHEN N'FC' THEN N'Finance Charge' WHEN N'IN' THEN N'Invoice' WHEN N'PP' THEN N'Prepayment' WHEN N'PY' THEN N'Payment' WHEN N'BC' THEN N'Balance forward other Charges' WHEN N'BF' THEN N'Balance Forward' ELSE CONCAT(N'Unknown / unset: ', o.InvoiceType) END AS InvoiceKind, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), o.SoldToDivisionNo)),N':',CONVERT(nvarchar(4000), o.SoldToDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), o.SoldToCustomerNo)),N':',CONVERT(nvarchar(4000), o.SoldToCustomerNo),N'|') AS SoldToKey FROM dbo.AR_OpenInvoice o LEFT JOIN dbo.AR_Customer m ON (CONVERT(nvarchar(4000), o.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), o.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))) AND (CONVERT(nvarchar(4000), o.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), o.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))) WHERE o.ARDivisionNo IS NOT NULL AND o.CustomerNo IS NOT NULL AND o.InvoiceNo IS NOT NULL AND o.InvoiceType IS NOT NULL",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "D5B71B7F-A683-5FCE-93DE-AFE3EBA82A9B",
"key": "AccountKey",
"type": "text"
}
],
"id": "1A8C673C-8F7C-53A4-B48A-05DDC4505A1E",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "ARDivisionNo",
"label": "Internal customer division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ARDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerNo",
"label": "Current customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "City",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "State",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TelephoneNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "EmailAddress",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ShipMethod",
"label": "Default shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipMethod",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Current master payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateLastPayment",
"label": "Current master last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateLastPayment",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateLastInvoice",
"label": "Current master last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateLastInvoice",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerDiscountRate",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditLimit",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastPaymentAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentBalance",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OpenOrderAmt",
"label": "Current customer open orders — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OpenOrderAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountKey",
"label": "Internal complete account identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerStatusName",
"label": "Current customer status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerStatusName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CreditHoldName",
"label": "Credit hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditHoldName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Account customer dossier",
"primaryKey": "AccountKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ARDivisionNo",
"id": "47D7DA6A-E4B6-5B78-9690-116AA96850B0",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "CustomerNo",
"id": "B9C7DAE1-B75B-588E-B1E1-C420C84899C4",
"name": "party",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"CustomerNo",
"CustomerName",
"AddressLine1",
"AddressLine2",
"City",
"State",
"ZipCode",
"CountryCode",
"TelephoneNo",
"EmailAddress",
"ShipMethod",
"TermsCode",
"CustomerDiscountRate",
"CustomerStatusName",
"CreditHoldName"
],
"sourceID": "C466CFDB-FFDA-5EBC-BC15-16353BC77CC6",
"sqlQuery": "SELECT m.ARDivisionNo, m.CustomerNo, m.CustomerName, m.AddressLine1, m.AddressLine2, m.City, m.State, m.ZipCode, m.CountryCode, m.TelephoneNo, m.EmailAddress, m.ShipMethod, m.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, m.CustomerDiscountRate, m.CreditLimit, m.LastPaymentAmt, m.CurrentBalance, m.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo)),N':',CONVERT(nvarchar(4000), m.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo)),N':',CONVERT(nvarchar(4000), m.CustomerNo),N'|') AS AccountKey, CASE m.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', m.CustomerStatus) END AS CustomerStatusName, m.CreditHold AS CreditHoldName FROM dbo.AR_Customer m WHERE (CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :party) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :party)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "30BC9919-5886-5767-92BF-C6360D86C6E1",
"key": "SoldToKey",
"type": "text"
}
],
"id": "E0835B30-79FF-5D1A-A74C-45D2DDEA5774",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "ARDivisionNo",
"label": "Internal customer division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ARDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerNo",
"label": "Current customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "City",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "State",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TelephoneNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "EmailAddress",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ShipMethod",
"label": "Default shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipMethod",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Current master payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateLastPayment",
"label": "Current master last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateLastPayment",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateLastInvoice",
"label": "Current master last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateLastInvoice",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerDiscountRate",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditLimit",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastPaymentAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentBalance",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OpenOrderAmt",
"label": "Current customer open orders — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OpenOrderAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SoldToKey",
"label": "Internal complete sold-to identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SoldToKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerStatusName",
"label": "Current customer status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerStatusName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CreditHoldName",
"label": "Credit hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditHoldName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Sold-to customer dossier",
"primaryKey": "SoldToKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "SoldToDivisionNo",
"id": "BD135FF0-AFEA-596B-A261-6E5FBFED3383",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "SoldToCustomerNo",
"id": "A8FF0D85-43CA-50E8-8B8F-08B2681FB426",
"name": "party",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"CustomerNo",
"CustomerName",
"AddressLine1",
"AddressLine2",
"City",
"State",
"ZipCode",
"CountryCode",
"TelephoneNo",
"EmailAddress",
"ShipMethod",
"TermsCode",
"CustomerDiscountRate",
"CustomerStatusName",
"CreditHoldName"
],
"sourceID": "C466CFDB-FFDA-5EBC-BC15-16353BC77CC6",
"sqlQuery": "SELECT m.ARDivisionNo, m.CustomerNo, m.CustomerName, m.AddressLine1, m.AddressLine2, m.City, m.State, m.ZipCode, m.CountryCode, m.TelephoneNo, m.EmailAddress, m.ShipMethod, m.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), m.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, m.CustomerDiscountRate, m.CreditLimit, m.LastPaymentAmt, m.CurrentBalance, m.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo)),N':',CONVERT(nvarchar(4000), m.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo)),N':',CONVERT(nvarchar(4000), m.CustomerNo),N'|') AS SoldToKey, CASE m.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', m.CustomerStatus) END AS CustomerStatusName, m.CreditHold AS CreditHoldName FROM dbo.AR_Customer m WHERE (CONVERT(nvarchar(4000), m.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), m.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :party) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :party)))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "8C07217A-2A00-52F8-B28D-12F4B60E9733",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "21DD915C-4409-5BF9-B7F0-A3ABFBE3EB69",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "1A8C673C-8F7C-53A4-B48A-05DDC4505A1E",
"title": "Account customer",
"urlKey": ""
},
{
"id": "DFF1A572-A11D-504C-8157-7ACCB611FC9B",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "ED6F04C4-40D5-5D68-9E06-85DE04ED3510",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "E0835B30-79FF-5D1A-A74C-45D2DDEA5774",
"title": "Sold-to customer",
"urlKey": ""
}
],
"badgeKey": "InvoiceKind",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDate",
"label": "Invoice / reference date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDueDate",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CustomerPONo",
"label": "Customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PaymentsToday",
"label": "Payments today — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Balance",
"label": "Current balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "053B6508-7162-528A-BB87-81CAA991563A",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "Account customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDate",
"label": "Invoice / reference date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDueDate",
"label": "Due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDiscountDate",
"label": "Discount date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TermsCode",
"label": "Stored payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Comment",
"label": "Retained open-item note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CreditMemoInvoiceReference",
"label": "Credit memo invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerPONo",
"label": "Customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingReference",
"label": "Posting reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SoldToCustomerNo",
"label": "Stored sold-to customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxableAmt",
"label": "Native taxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonTaxableAmt",
"label": "Native nontaxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "FreightAmt",
"label": "Native freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "SalesTaxAmt",
"label": "Sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CostOfSalesAmt",
"label": "Native cost of sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "DiscountAmt",
"label": "Native discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "PaymentsToday",
"label": "Native payments today — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "Balance",
"label": "Current item balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "RetentionAmt",
"label": "Native retention — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PartyDisplayName",
"label": "Current account name / stored code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PartyAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDateReadState",
"label": "Invoice date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DueDateReadState",
"label": "Due date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceKind",
"label": "Open-item type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "B2922306-4951-5551-B3A6-4A448B6EEB68",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": true,
"sortRules": [
{
"direction": "ascending",
"id": "FB11735C-3787-5AA4-90B5-0E7CED9BA057",
"key": "InvoiceDueDate",
"type": "date"
},
{
"direction": "ascending",
"id": "B964634B-49A9-5A8F-BDF3-CFF9848AE88D",
"key": "OpenItemKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "PartyDisplayName",
"systemImage": "doc.text",
"title": "Current receivables",
"titleKey": "InvoiceNo"
},
{
"actions": [],
"badgeKey": "CustomerStatusName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Master balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "1A8C673C-8F7C-53A4-B48A-05DDC4505A1E",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "Current customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ShipMethod",
"label": "Default shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TermsCode",
"label": "Current master payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateLastPayment",
"label": "Current master last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateLastInvoice",
"label": "Current master last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "OpenOrderAmt",
"label": "Current customer open orders — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerStatusName",
"label": "Current customer status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CreditHoldName",
"label": "Credit hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "A7BCE3C0-4583-5022-9348-FFCD03C02338",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "D5B71B7F-A683-5FCE-93DE-AFE3EBA82A9B",
"key": "AccountKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CustomerNo",
"systemImage": "doc.text",
"title": "Account customer dossier",
"titleKey": "CustomerName"
},
{
"actions": [],
"badgeKey": "CustomerStatusName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Master balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "E0835B30-79FF-5D1A-A74C-45D2DDEA5774",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "Current customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerName",
"label": "Current customer name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ShipMethod",
"label": "Default shipping method code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TermsCode",
"label": "Current master payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateLastPayment",
"label": "Current master last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateLastInvoice",
"label": "Current master last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "OpenOrderAmt",
"label": "Current customer open orders — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerStatusName",
"label": "Current customer status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CreditHoldName",
"label": "Credit hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "62D76614-AF6D-5748-B772-CFC187770211",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "30BC9919-5886-5767-92BF-C6360D86C6E1",
"key": "SoldToKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CustomerNo",
"systemImage": "doc.text",
"title": "Sold-to customer dossier",
"titleKey": "CustomerName"
}
],
"relations": [
{
"childDatasetID": "1A8C673C-8F7C-53A4-B48A-05DDC4505A1E",
"childKey": "AccountKey",
"id": "21DD915C-4409-5BF9-B7F0-A3ABFBE3EB69",
"name": "Account customer dossier",
"parentDatasetID": "053B6508-7162-528A-BB87-81CAA991563A",
"parentKey": "AccountKey"
},
{
"childDatasetID": "E0835B30-79FF-5D1A-A74C-45D2DDEA5774",
"childKey": "SoldToKey",
"id": "ED6F04C4-40D5-5D68-9E06-85DE04ED3510",
"name": "Sold-to customer dossier",
"parentDatasetID": "053B6508-7162-528A-BB87-81CAA991563A",
"parentKey": "SoldToKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Sage 100 US — SQL Server",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-09T00:00:00Z",
"description": "NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial configuration based on Sage 100 US 2026 FLOR Rel 7.50 own open-item layouts, complete logical keys, public functional help and synthetic validation. Verify installed schema, currency, NULL, roles, date adapters, padding, collation, least-privilege SELECT permissions and query cost at import. Not a real Sage SQL Server test.\n\nInspect current retained customer open items, native item balances, due/discount/posting dates, document types, payments-today, credit references and sold-to context. Open independent current account-customer and sold-to customer dossiers without replacing a missing role.\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 source, 3 lists, 2 lazy dossier buttons, parameterized read-only SELECT and at most 2,000 rows per request. No scheduled refresh. No implicit Last 7 days or Balance<>0 filter: old debts, retained zero-balance rows and unrecognized dates remain available. Oldest due dates sort first with a complete-identity tie break; NULL dates can sort ahead of dated rows. Local Search, Filters and sorting affect loaded rows only, not the entire source. A 2,000-row limit hit means the list may be incomplete; adapt source-side filtering before relying on totals. No fabricated aggregate total or full-catalog claim.\n\nCURRENT native open-item records, not a historical closing balance, a reconstructed aging report or a complete payment ledger. Invoice/due-date filtering selects current retained records; it does not show the balance as of the selected date. Retention, purges and balance-forward accounting options affect availability. Source changes between reads are not a transaction-consistent snapshot. A missing row or master is not evidence of no debt or a zero balance.\n\nBalance, original invoice/tax/freight components, payments today, discount and retention are separate native values. NULL remains NULL, never zero. No general paid/unpaid status, source currency code, USD assumption, universal debit/credit sign normalization or reconstructed amount due. Monetary labels refer to source currency; verify its meaning in your company setup.\n\nA/R open-item types use only their own legend: CM Credit Memo, DM Debit Memo, FC Finance Charge, IN Invoice, PP Prepayment, PY Payment, BC Balance forward other Charges, BF Balance Forward. Unknown codes stay unknown; no AD/CA/XD history legend is borrowed. BC/BF are balance-forward context, not ordinary commercial invoices. Full identity is ARDivisionNo + CustomerNo + InvoiceNo + InvoiceType. Sold-to uses its explicitly stored division and customer; a missing sold-to master is not replaced by the account customer.\n\nMaster contacts, payment terms, balances and last payment values are CURRENT, separately labeled from this individual open item. InvoiceHistoryHeaderSeqNo is kept internally but does not certify a history or line linkage; no partial invoice-number join is provided. Technical keys are hidden. Exact binary Unicode and byte-length comparisons preserve complete strings, but installed physical types, padding, collation and performance still require validation. SQL Server 2012+/compatibility 110+ is required for defensive date conversion; invalid or unsupported dates become NULL, not 1900, with interpretation labels.\n\nNo source credentials, server addresses, cached business rows, banking/check/taxpayer data or audit-user identifiers in the package. DEMO captures must be wholly fictional. Public layouts prove selected logical names and keys, not installed DDL/indexes or speed. Native ERP operator permissions do not carry into direct SQL: separately authorized least-privilege SELECT is required. Unofficial, not endorsed by Sage; not France, Contractor or ProvideX.\n\nOwn open-item layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/Accounts_Receivable/AR_OpenInvoice.htm\nOwn master layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/Accounts_Receivable/AR_Customer.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "A0964CAA-D45F-55E4-A4E0-71CECAF9B522",
"rootButtonCount": 1,
"summary": "Current retained item balances and due dates; complete context and separate current customer roles.",
"tags": [
"Sage 100 US",
"SQL Server",
"Accounting",
"Receivables",
"Pro"
],
"title": "Current receivables and customer dossiers — Pro"
}
}