Customer invoices and full dossiers — Pro
Invoice period, native historical values and precise lines; item and separate history/billing customer dossiers.
NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial candidate based on Sage 100 US 2026 FLOR Rel 7.50 complete documented invoice keys, public functional help and synthetic tests. Verify schema, full identities, units, source currency, collation, permissions, date adapters and query cost at import before business use. Native DEMO screenshots do not certify SQL Server execution.
For accounting, sales, dispatch and managers: choose an invoice-date period, inspect retained AR/SO invoice, credit, debit and adjustment context, stored billing/delivery addresses, due dates, native sales/tax/freight/discount/deposit/retention values and references. Open precise historical invoice lines and their current inventory item dossier. Two separate customer buttons preserve history-customer and billing-customer identities, current contacts, credit context and native balances.
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, five lists and four separate lazy buttons, parameterized read-only SELECT, at most 2,000 rows per request, no scheduled refresh. Both opening-period boundaries apply before the read limit. Narrow the period on a limit hit; search/local filters cover loaded rows only. Row limits and filters are not a query-cost guarantee or an authorization boundary.
Target: retained AR/SO invoice history in Sage 100 US 2026 SQL Server company databases, formerly Premium. Deleted XD history is excluded; Job Cost-origin documents are outside this dossier. Known invoice type codes use their own documented meaning; unknown values remain unknown. Credit/debit/adjustments can share an invoice number. Invoice identity includes the full case-sensitive HeaderSeqNo and line identity also includes DetailSeqNo. SQL compares explicit binary Unicode values and byte lengths, not an unverified default case-insensitive collation. Verify stored types, padding, identity handling and parameters on the actual installation. No deduplication by invoice or item code.
History is not a current receivables ledger. No paid/unpaid state, invoice balance, aging, exchange rate, calculated invoice total or sign normalization is invented. Native header amounts and line extensions/tax are displayed separately, without summing credit/debit records, reapplying discounts or replacing NULL by zero. Source currency must be verified; USD is not assumed. Historical line units, prices and costs differ from current item master quantities/reference prices/costs. Precise quantities, costs, factors and rates remain text; their local sorting/filtering is textual.
AR Retain in Detail Yes, Summary and No retain different information. Comment/deleted-invoice options and purges affect history. Missing lines do not prove a zero invoice or no activity. Multiple orders/customer POs/deposits are flagged; header references do not exhaust line-level applied references. Module-dependent values may be empty. Cifru does not change ERP options.
With bill-to reporting enabled, invoice history is maintained for sold-to while posting/payment uses bill-to. Keep the history and billing customer dossiers separate, including division. Missing current customer masters do not remove history; a missing billing master is not replaced by the history customer. Current customer balance, credit limit, open orders and last payment are current master information, not that invoice balance/payment or values as of the opening period. Stored invoice addresses remain separate from current customer contacts/addresses. Never add both roles sales history into one overstated total. Special/charge/comment/miscellaneous and orphan inventory lines stay in the line list without fabricated item dossiers.
SQL Server 2012+ / compatibility 110+ is required for defensive TRY_CONVERT dates. Unsupported or corrupt date values become NULL, not 1900. FLOR types/keys are logical evidence, not installed SQL DDL, indexes or collation certification. Verify dbo/company scope, full column owners, modules, NULL/units/monetary signs, padding, permissions, response time and read test at import. Direct SQL does not inherit native operator roles; use a separately authorized least-privilege SELECT login. No credentials, server addresses, cached business rows, banking/taxpayer/payment identifiers or internal audit users are exported. Any later gallery must use entirely fictional DEMO values in real Cifru screens. Unofficial, not endorsed by Sage; not Sage 100 France/Contractor/ProvideX.
Dictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/
History and options: https://help-sage100.na.sage.com/2026/Subsystems/AR/ARMainFields/Invoice_History_Inquiry_-_Fields1.htm
Retention and customer roles: https://help-sage100.na.sage.com/2026/Subsystems/AR/ARSetupFields/Accounts_Receivable_Options_-_Fields.htm
Screenshots
What this package creates
1 Home4 Details5 lists1 sources to map
- Home: Customer invoices
- Details: Invoice lines
- Details: Item dossier
- Details: History customer dossier
- Details: Billing customer dossier
- Sub-button: Invoice lines
- Sub-button: Item dossier
- Sub-button: History customer dossier
- Sub-button: Billing customer dossier
Sources are mapped locally and verified before applying.
Custom queriesPRO5 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 h.InvoiceNo, h.HeaderSeqNo, h.ModuleCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112) AS InvoiceDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),8) ELSE NULL END,112) AS TransactionDate, h.ARDivisionNo, h.CustomerNo, h.TermsCode, h.CustomerPONo, h.ApplyToInvoiceNo, h.Comment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),8) ELSE NULL END,112) AS InvoiceDueDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),8) ELSE NULL END,112) AS DiscountDueDate, h.SourceJournal, h.JournalNoGLBatchNo, h.SalesOrderNo, h.MultipleSalesOrdersApplied, h.MultipleCustomerPONos, h.MultipleDepositsApplied, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),8) ELSE NULL END,112) AS OrderDate, h.BillToDivisionNo, h.BillToCustomerNo, h.BillToName, h.BillToAddress1, h.BillToAddress2, h.BillToAddress3, h.BillToCity, h.BillToState, h.BillToZipCode, h.BillToCountryCode, h.ShipToCode, h.ShipToName, h.ShipToAddress1, h.ShipToAddress2, h.ShipToAddress3, h.ShipToCity, h.ShipToState, h.ShipToZipCode, h.ShipToCountryCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),8) ELSE NULL END,112) AS ShipDate, h.ShipVia, h.WarehouseCode, h.TaxableSalesAmt, h.NonTaxableSalesAmt, h.FreightAmt, h.SalesTaxAmt, h.CostOfSalesAmt, h.AmountSubjectToDiscount, h.DiscountRate, h.DiscountAmt, h.DepositAmt, h.RetentionAmt, h.NumberOfPackages, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo)),N':',CONVERT(nvarchar(4000), h.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), h.HeaderSeqNo),N'|') AS InvoiceIdentity, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.ARDivisionNo)),N':',CONVERT(nvarchar(4000), h.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.CustomerNo)),N':',CONVERT(nvarchar(4000), h.CustomerNo),N'|') AS CustomerKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.BillToDivisionNo)),N':',CONVERT(nvarchar(4000), h.BillToDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.BillToCustomerNo)),N':',CONVERT(nvarchar(4000), h.BillToCustomerNo),N'|') AS BillToKey, CONCAT(N'History customer: ', COALESCE(NULLIF(c.CustomerName,N''), h.CustomerNo)) AS PartyDisplayName, CASE h.InvoiceType WHEN N'CM' THEN N'Credit Memo' WHEN N'DM' THEN N'Debit Memo' WHEN N'IN' THEN N'Invoice' WHEN N'AD' THEN N'Adjustment' WHEN N'FC' THEN N'Finance Charge' WHEN N'CA' THEN N'Cash invoice' WHEN N'XD' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', h.InvoiceType) END AS InvoiceKind, CASE h.OrderType WHEN N'B' THEN N'Backorder' WHEN N'S' THEN N'Standard' WHEN N'1' THEN N'One Step Invoice' ELSE CONCAT(N'Unknown / unset: ', h.OrderType) END AS OrderKind FROM dbo.AR_InvoiceHistoryHeader h LEFT JOIN dbo.AR_Customer c ON (CONVERT(nvarchar(4000), h.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))) AND (CONVERT(nvarchar(4000), h.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112)>=:date_from AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112)<:date_until
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT l.InvoiceNo, l.HeaderSeqNo, l.DetailSeqNo, l.ItemCode, l.ItemCodeDesc, l.UnitOfMeasure, l.WarehouseCode, l.ProductLine, l.AliasItemNo, l.CommentText, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),8) ELSE NULL END,112) AS PromiseDate, l.APDivisionNo, l.VendorNo, l.PurchaseOrderNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),8) ELSE NULL END,112) AS PurchaseOrderRequiredDate, l.QuantityShipped, l.QuantityOrdered, l.QuantityBackordered, l.UnitPrice, l.UnitCost, l.UnitOfMeasureConvFactor, l.LineDiscountPercent, l.ExtensionAmt, l.TaxAmt, l.TaxRate, l.AppliedSalesOrderNo, l.AppliedCustomerPONo, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo)),N':',CONVERT(nvarchar(4000), l.DetailSeqNo),N'|') AS LineIdentity, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|') AS InvoiceIdentity, CASE l.ItemType WHEN N'1' THEN N'Inventory Item' WHEN N'2' THEN N'Special Item' WHEN N'3' THEN N'Charge Item' WHEN N'4' THEN N'Comment Item' WHEN N'5' THEN N'Miscellaneous Item' ELSE CONCAT(N'Unknown / unset: ', l.ItemType) END AS ItemTypeName, CONCAT(N'Native method code: ', l.Valuation) AS ValuationName, CASE WHEN l.ItemType<>N'1' THEN N'Not an inventory line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.AR_InvoiceHistoryDetail l INNER JOIN dbo.AR_InvoiceHistoryHeader h ON (CONVERT(nvarchar(4000), h.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))) AND (CONVERT(nvarchar(4000), h.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))) LEFT JOIN dbo.CI_Item i ON l.ItemType=N'1' AND (CONVERT(nvarchar(4000), l.ItemCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), i.ItemCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.ItemCode))=DATALENGTH(CONVERT(nvarchar(4000), i.ItemCode))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND (CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_no) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_no))) AND (CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_seq)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQuerySELECT i.ItemCode, i.ItemCodeDesc, i.SalesUnitOfMeasure, i.PurchaseUnitOfMeasure, i.StandardUnitOfMeasure, i.ProductLine, i.DefaultWarehouseCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),8) ELSE NULL END,112) AS LastSoldDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),8) ELSE NULL END,112) AS LastReceiptDate, i.StandardUnitCost, i.StandardUnitPrice, i.LastTotalUnitCost, i.AverageUnitCost, i.TotalQuantityOnHand, i.PurchaseUMConvFctr, i.SalesUMConvFctr, i.UPCEAN, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo)),N':',CONVERT(nvarchar(4000), l.DetailSeqNo),N'|') AS LineIdentity, CASE i.Valuation WHEN N'1' THEN N'Standard' WHEN N'2' THEN N'Average' WHEN N'3' THEN N'Fifo' WHEN N'4' THEN N'Lifo' WHEN N'5' THEN N'Lot' WHEN N'6' THEN N'Serial' ELSE CONCAT(N'Unknown / unset: ', i.Valuation) END AS ValuationName, CASE i.ProcurementType WHEN N'B' THEN N'Buy to Stock' WHEN N'M' THEN N'Make to Stock' WHEN N'C' THEN N'Buy to Order' WHEN N'N' THEN N'Make to Order' WHEN N'S' THEN N'Subcontract' ELSE CONCAT(N'Unknown / unset: ', i.ProcurementType) END AS ProcurementName, CASE i.InactiveItem WHEN N'N' THEN N'Active' WHEN N'Y' THEN N'Inactive' ELSE N'Unknown / unset' END AS ItemState FROM dbo.AR_InvoiceHistoryDetail l INNER JOIN dbo.AR_InvoiceHistoryHeader h ON (CONVERT(nvarchar(4000), h.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))) AND (CONVERT(nvarchar(4000), h.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))) INNER JOIN dbo.CI_Item i ON l.ItemType=N'1' AND (CONVERT(nvarchar(4000), l.ItemCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), i.ItemCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.ItemCode))=DATALENGTH(CONVERT(nvarchar(4000), i.ItemCode))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND (CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_no) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_no))) AND (CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_seq))) AND (CONVERT(nvarchar(4000), l.DetailSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :detail_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :detail_seq)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.3.sqlQuerySELECT c.ARDivisionNo, c.CustomerNo, c.CustomerName, c.AddressLine1, c.AddressLine2, c.City, c.State, c.ZipCode, c.CountryCode, c.TelephoneNo, c.EmailAddress, c.ShipMethod, c.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, c.CustomerDiscountRate, c.CreditLimit, c.LastPaymentAmt, c.CurrentBalance, c.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo)),N':',CONVERT(nvarchar(4000), c.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo)),N':',CONVERT(nvarchar(4000), c.CustomerNo),N'|') AS CustomerKey, CASE c.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', c.CustomerStatus) END AS CustomerStatusName, c.CreditHold AS CreditHoldName FROM dbo.AR_Customer c WHERE (CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :customer) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :customer)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.4.sqlQuerySELECT c.ARDivisionNo, c.CustomerNo, c.CustomerName, c.AddressLine1, c.AddressLine2, c.City, c.State, c.ZipCode, c.CountryCode, c.TelephoneNo, c.EmailAddress, c.ShipMethod, c.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, c.CustomerDiscountRate, c.CreditLimit, c.LastPaymentAmt, c.CurrentBalance, c.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo)),N':',CONVERT(nvarchar(4000), c.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo)),N':',CONVERT(nvarchar(4000), c.CustomerNo),N'|') AS BillToKey, CASE c.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', c.CustomerStatus) END AS CustomerStatusName, c.CreditHold AS CreditHoldName FROM dbo.AR_Customer c WHERE (CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :customer) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :customer)))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 100 US — SQL Server company database",
"id": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"kind": "sqlServer",
"requiredObjects": [
"dbo.AR_Customer",
"dbo.AR_InvoiceHistoryDetail",
"dbo.AR_InvoiceHistoryHeader",
"dbo.CI_Item"
],
"requiresCustomSQL": true
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "4F735011-E033-5589-BF51-40D6F11DA441",
"key": "InvoiceDate",
"type": "date"
},
{
"direction": "ascending",
"id": "06CB09EC-4B1B-554D-B76D-C27672800C5B",
"key": "InvoiceIdentity",
"type": "text"
}
],
"id": "4FF432E0-C154-589E-B814-435BFB7CFE20",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "HeaderSeqNo",
"label": "Internal invoice sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HeaderSeqNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ModuleCode",
"label": "Origin module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ModuleCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceDate",
"label": "Invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "TransactionDate",
"label": "Transaction date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TransactionDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ARDivisionNo",
"label": "Internal customer division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ARDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerNo",
"label": "Customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerPONo",
"label": "Header customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerPONo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ApplyToInvoiceNo",
"label": "Applies to invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ApplyToInvoiceNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Comment",
"label": "Stored invoice note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Comment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceDueDate",
"label": "Invoice due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDueDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DiscountDueDate",
"label": "Discount due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DiscountDueDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournal",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalNoGLBatchNo",
"label": "Journal / G/L batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalNoGLBatchNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesOrderNo",
"label": "Header sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MultipleSalesOrdersApplied",
"label": "Multiple sales orders (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MultipleSalesOrdersApplied",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MultipleCustomerPONos",
"label": "Multiple customer POs (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MultipleCustomerPONos",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MultipleDepositsApplied",
"label": "Multiple deposits (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MultipleDepositsApplied",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OrderDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToDivisionNo",
"label": "Internal billing division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToCustomerNo",
"label": "Stored bill-to customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToCustomerNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToName",
"label": "Stored bill-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "BillToAddress1",
"label": "Stored billing address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToAddress1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToAddress2",
"label": "Stored billing address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToAddress2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToAddress3",
"label": "Stored billing address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToAddress3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToCity",
"label": "Stored billing city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToCity",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToState",
"label": "Stored billing state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToZipCode",
"label": "Stored billing postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToCountryCode",
"label": "Stored billing country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToCountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCode",
"label": "Delivery address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToName",
"label": "Stored delivery name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress1",
"label": "Stored delivery address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress2",
"label": "Stored delivery address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress3",
"label": "Stored delivery address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCity",
"label": "Stored delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCity",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ShipToState",
"label": "Stored delivery state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToZipCode",
"label": "Stored delivery postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCountryCode",
"label": "Stored delivery country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipDate",
"label": "Shipping date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipVia",
"label": "Ship via code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipVia",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "WarehouseCode",
"label": "Warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxableSalesAmt",
"label": "Native taxable sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxableSalesAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "NonTaxableSalesAmt",
"label": "Native nontaxable sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonTaxableSalesAmt",
"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": "Native sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesTaxAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CostOfSalesAmt",
"label": "Native cost of sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CostOfSalesAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AmountSubjectToDiscount",
"label": "Amount subject to discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AmountSubjectToDiscount",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DiscountRate",
"label": "Native discount rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DiscountRate",
"type": "text",
"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": "DepositAmt",
"label": "Native deposit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DepositAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RetentionAmt",
"label": "Native retention — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RetentionAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NumberOfPackages",
"label": "Number of packages",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NumberOfPackages",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceIdentity",
"label": "Internal full invoice identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceIdentity",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerKey",
"label": "Internal full history customer identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BillToKey",
"label": "Internal full billing customer identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartyDisplayName",
"label": "History customer — current name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartyDisplayName",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceKind",
"label": "Invoice type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceKind",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrderKind",
"label": "Source order type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OrderKind",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Customer invoices",
"primaryKey": "InvoiceIdentity",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceDate",
"id": "6A0484C4-11FC-5FD9-A27A-ED96C68A399E",
"name": "date_from",
"source": "openingPeriodStart",
"type": "date"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceDate",
"id": "0F3D8E75-9594-5CE8-A815-E96A65E61143",
"name": "date_until",
"source": "openingPeriodEndExclusive",
"type": "date"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvoiceNo",
"ModuleCode",
"CustomerNo",
"TermsCode",
"CustomerPONo",
"ApplyToInvoiceNo",
"Comment",
"SourceJournal",
"JournalNoGLBatchNo",
"SalesOrderNo",
"MultipleSalesOrdersApplied",
"MultipleCustomerPONos",
"MultipleDepositsApplied",
"BillToCustomerNo",
"BillToName",
"BillToAddress1",
"BillToAddress2",
"BillToAddress3",
"BillToCity",
"BillToState",
"BillToZipCode",
"BillToCountryCode",
"ShipToCode",
"ShipToName",
"ShipToAddress1",
"ShipToAddress2",
"ShipToAddress3",
"ShipToCity",
"ShipToState",
"ShipToZipCode",
"ShipToCountryCode",
"ShipVia",
"WarehouseCode",
"DiscountRate",
"InvoiceKind",
"OrderKind"
],
"sourceID": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"sqlQuery": "SELECT h.InvoiceNo, h.HeaderSeqNo, h.ModuleCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112) AS InvoiceDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.TransactionDate, 112))),8) ELSE NULL END,112) AS TransactionDate, h.ARDivisionNo, h.CustomerNo, h.TermsCode, h.CustomerPONo, h.ApplyToInvoiceNo, h.Comment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDueDate, 112))),8) ELSE NULL END,112) AS InvoiceDueDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.DiscountDueDate, 112))),8) ELSE NULL END,112) AS DiscountDueDate, h.SourceJournal, h.JournalNoGLBatchNo, h.SalesOrderNo, h.MultipleSalesOrdersApplied, h.MultipleCustomerPONos, h.MultipleDepositsApplied, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.OrderDate, 112))),8) ELSE NULL END,112) AS OrderDate, h.BillToDivisionNo, h.BillToCustomerNo, h.BillToName, h.BillToAddress1, h.BillToAddress2, h.BillToAddress3, h.BillToCity, h.BillToState, h.BillToZipCode, h.BillToCountryCode, h.ShipToCode, h.ShipToName, h.ShipToAddress1, h.ShipToAddress2, h.ShipToAddress3, h.ShipToCity, h.ShipToState, h.ShipToZipCode, h.ShipToCountryCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ShipDate, 112))),8) ELSE NULL END,112) AS ShipDate, h.ShipVia, h.WarehouseCode, h.TaxableSalesAmt, h.NonTaxableSalesAmt, h.FreightAmt, h.SalesTaxAmt, h.CostOfSalesAmt, h.AmountSubjectToDiscount, h.DiscountRate, h.DiscountAmt, h.DepositAmt, h.RetentionAmt, h.NumberOfPackages, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo)),N':',CONVERT(nvarchar(4000), h.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), h.HeaderSeqNo),N'|') AS InvoiceIdentity, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.ARDivisionNo)),N':',CONVERT(nvarchar(4000), h.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.CustomerNo)),N':',CONVERT(nvarchar(4000), h.CustomerNo),N'|') AS CustomerKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), h.BillToDivisionNo)),N':',CONVERT(nvarchar(4000), h.BillToDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.BillToCustomerNo)),N':',CONVERT(nvarchar(4000), h.BillToCustomerNo),N'|') AS BillToKey, CONCAT(N'History customer: ', COALESCE(NULLIF(c.CustomerName,N''), h.CustomerNo)) AS PartyDisplayName, CASE h.InvoiceType WHEN N'CM' THEN N'Credit Memo' WHEN N'DM' THEN N'Debit Memo' WHEN N'IN' THEN N'Invoice' WHEN N'AD' THEN N'Adjustment' WHEN N'FC' THEN N'Finance Charge' WHEN N'CA' THEN N'Cash invoice' WHEN N'XD' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', h.InvoiceType) END AS InvoiceKind, CASE h.OrderType WHEN N'B' THEN N'Backorder' WHEN N'S' THEN N'Standard' WHEN N'1' THEN N'One Step Invoice' ELSE CONCAT(N'Unknown / unset: ', h.OrderType) END AS OrderKind FROM dbo.AR_InvoiceHistoryHeader h LEFT JOIN dbo.AR_Customer c ON (CONVERT(nvarchar(4000), h.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))) AND (CONVERT(nvarchar(4000), h.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112)>=:date_from AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDate, 112))),8) ELSE NULL END,112)<:date_until",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "B12B6571-C144-54BA-BF3D-039EBA5F7017",
"key": "DetailSeqNo",
"type": "text"
},
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"id": "040D5525-00DA-50CD-8AD3-408D86C122E6",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "HeaderSeqNo",
"label": "Internal invoice sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HeaderSeqNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DetailSeqNo",
"label": "Internal line sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DetailSeqNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCodeDesc",
"label": "Item / line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCodeDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitOfMeasure",
"label": "Invoice line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitOfMeasure",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "WarehouseCode",
"label": "Warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "WarehouseCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ProductLine",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AliasItemNo",
"label": "Customer item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AliasItemNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CommentText",
"label": "Line note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CommentText",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PromiseDate",
"label": "Promised date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PromiseDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "APDivisionNo",
"label": "Internal vendor division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "APDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorNo",
"label": "Line vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseOrderNo",
"label": "Line purchase order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseOrderRequiredDate",
"label": "Line purchase required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderRequiredDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QuantityShipped",
"label": "Native shipped quantity — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityShipped",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityOrdered",
"label": "Native ordered quantity — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityOrdered",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityBackordered",
"label": "Native backordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityBackordered",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitPrice",
"label": "Historical unit price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitPrice",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitCost",
"label": "Historical unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitOfMeasureConvFactor",
"label": "Native invoice unit factor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitOfMeasureConvFactor",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDiscountPercent",
"label": "Line discount (%) — already reflected by Sage",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDiscountPercent",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ExtensionAmt",
"label": "Native line extension — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ExtensionAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "TaxAmt",
"label": "Native line tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxRate",
"label": "Native line tax rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxRate",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AppliedSalesOrderNo",
"label": "Line sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AppliedSalesOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AppliedCustomerPONo",
"label": "Line customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AppliedCustomerPONo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineIdentity",
"label": "Internal full invoice-line identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineIdentity",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceIdentity",
"label": "Internal full invoice identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceIdentity",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemTypeName",
"label": "Line type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemTypeName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ValuationName",
"label": "Valuation method",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ValuationName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductAvailability",
"label": "Inventory item dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ProductAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Invoice lines",
"primaryKey": "LineIdentity",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceNo",
"id": "27A3DAAA-E611-5537-807A-12AFB9029CC2",
"name": "invoice_no",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "HeaderSeqNo",
"id": "2254CEDD-FCA4-57C1-B633-F97768E991E7",
"name": "invoice_seq",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvoiceNo",
"ItemCode",
"ItemCodeDesc",
"UnitOfMeasure",
"WarehouseCode",
"ProductLine",
"AliasItemNo",
"CommentText",
"VendorNo",
"PurchaseOrderNo",
"QuantityShipped",
"QuantityOrdered",
"QuantityBackordered",
"UnitPrice",
"UnitCost",
"UnitOfMeasureConvFactor",
"LineDiscountPercent",
"TaxRate",
"AppliedSalesOrderNo",
"AppliedCustomerPONo",
"ItemTypeName",
"ValuationName",
"ProductAvailability"
],
"sourceID": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"sqlQuery": "SELECT l.InvoiceNo, l.HeaderSeqNo, l.DetailSeqNo, l.ItemCode, l.ItemCodeDesc, l.UnitOfMeasure, l.WarehouseCode, l.ProductLine, l.AliasItemNo, l.CommentText, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PromiseDate, 112))),8) ELSE NULL END,112) AS PromiseDate, l.APDivisionNo, l.VendorNo, l.PurchaseOrderNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.PurchaseOrderRequiredDate, 112))),8) ELSE NULL END,112) AS PurchaseOrderRequiredDate, l.QuantityShipped, l.QuantityOrdered, l.QuantityBackordered, l.UnitPrice, l.UnitCost, l.UnitOfMeasureConvFactor, l.LineDiscountPercent, l.ExtensionAmt, l.TaxAmt, l.TaxRate, l.AppliedSalesOrderNo, l.AppliedCustomerPONo, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo)),N':',CONVERT(nvarchar(4000), l.DetailSeqNo),N'|') AS LineIdentity, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|') AS InvoiceIdentity, CASE l.ItemType WHEN N'1' THEN N'Inventory Item' WHEN N'2' THEN N'Special Item' WHEN N'3' THEN N'Charge Item' WHEN N'4' THEN N'Comment Item' WHEN N'5' THEN N'Miscellaneous Item' ELSE CONCAT(N'Unknown / unset: ', l.ItemType) END AS ItemTypeName, CONCAT(N'Native method code: ', l.Valuation) AS ValuationName, CASE WHEN l.ItemType<>N'1' THEN N'Not an inventory line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.AR_InvoiceHistoryDetail l INNER JOIN dbo.AR_InvoiceHistoryHeader h ON (CONVERT(nvarchar(4000), h.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))) AND (CONVERT(nvarchar(4000), h.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))) LEFT JOIN dbo.CI_Item i ON l.ItemType=N'1' AND (CONVERT(nvarchar(4000), l.ItemCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), i.ItemCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.ItemCode))=DATALENGTH(CONVERT(nvarchar(4000), i.ItemCode))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND (CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_no) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_no))) AND (CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_seq)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"id": "2EE2611B-E539-5BE9-810D-5E5F8401A4AA",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCodeDesc",
"label": "Item / line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCodeDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesUnitOfMeasure",
"label": "Sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesUnitOfMeasure",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseUnitOfMeasure",
"label": "Purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseUnitOfMeasure",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StandardUnitOfMeasure",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StandardUnitOfMeasure",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ProductLine",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DefaultWarehouseCode",
"label": "Default warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DefaultWarehouseCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastSoldDate",
"label": "Item last sold",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastSoldDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastReceiptDate",
"label": "Item last received",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastReceiptDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StandardUnitCost",
"label": "Standard cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StandardUnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "StandardUnitPrice",
"label": "Standard price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StandardUnitPrice",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "LastTotalUnitCost",
"label": "Last total cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastTotalUnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AverageUnitCost",
"label": "Average cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AverageUnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TotalQuantityOnHand",
"label": "On hand — all warehouses",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TotalQuantityOnHand",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PurchaseUMConvFctr",
"label": "Stock units per purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseUMConvFctr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesUMConvFctr",
"label": "Stock units per sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesUMConvFctr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UPCEAN",
"label": "UPC / EAN",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UPCEAN",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineIdentity",
"label": "Internal full invoice-line identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineIdentity",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemState",
"label": "Item status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ValuationName",
"label": "Valuation method",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ValuationName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProcurementName",
"label": "Procurement",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ProcurementName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Item dossier",
"primaryKey": "LineIdentity",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceNo",
"id": "27A3DAAA-E611-5537-807A-12AFB9029CC2",
"name": "invoice_no",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "HeaderSeqNo",
"id": "2254CEDD-FCA4-57C1-B633-F97768E991E7",
"name": "invoice_seq",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "DetailSeqNo",
"id": "AA3C3F74-6EA6-5011-9B7B-307CEDB2F435",
"name": "detail_seq",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"ItemCode",
"ItemCodeDesc",
"SalesUnitOfMeasure",
"PurchaseUnitOfMeasure",
"StandardUnitOfMeasure",
"ProductLine",
"DefaultWarehouseCode",
"StandardUnitCost",
"StandardUnitPrice",
"LastTotalUnitCost",
"AverageUnitCost",
"TotalQuantityOnHand",
"PurchaseUMConvFctr",
"SalesUMConvFctr",
"UPCEAN",
"ItemState",
"ValuationName",
"ProcurementName"
],
"sourceID": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"sqlQuery": "SELECT i.ItemCode, i.ItemCodeDesc, i.SalesUnitOfMeasure, i.PurchaseUnitOfMeasure, i.StandardUnitOfMeasure, i.ProductLine, i.DefaultWarehouseCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastSoldDate, 112))),8) ELSE NULL END,112) AS LastSoldDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), i.LastReceiptDate, 112))),8) ELSE NULL END,112) AS LastReceiptDate, i.StandardUnitCost, i.StandardUnitPrice, i.LastTotalUnitCost, i.AverageUnitCost, i.TotalQuantityOnHand, i.PurchaseUMConvFctr, i.SalesUMConvFctr, i.UPCEAN, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo)),N':',CONVERT(nvarchar(4000), l.InvoiceNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo)),N':',CONVERT(nvarchar(4000), l.HeaderSeqNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo)),N':',CONVERT(nvarchar(4000), l.DetailSeqNo),N'|') AS LineIdentity, CASE i.Valuation WHEN N'1' THEN N'Standard' WHEN N'2' THEN N'Average' WHEN N'3' THEN N'Fifo' WHEN N'4' THEN N'Lifo' WHEN N'5' THEN N'Lot' WHEN N'6' THEN N'Serial' ELSE CONCAT(N'Unknown / unset: ', i.Valuation) END AS ValuationName, CASE i.ProcurementType WHEN N'B' THEN N'Buy to Stock' WHEN N'M' THEN N'Make to Stock' WHEN N'C' THEN N'Buy to Order' WHEN N'N' THEN N'Make to Order' WHEN N'S' THEN N'Subcontract' ELSE CONCAT(N'Unknown / unset: ', i.ProcurementType) END AS ProcurementName, CASE i.InactiveItem WHEN N'N' THEN N'Active' WHEN N'Y' THEN N'Inactive' ELSE N'Unknown / unset' END AS ItemState FROM dbo.AR_InvoiceHistoryDetail l INNER JOIN dbo.AR_InvoiceHistoryHeader h ON (CONVERT(nvarchar(4000), h.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))) AND (CONVERT(nvarchar(4000), h.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), h.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))) INNER JOIN dbo.CI_Item i ON l.ItemType=N'1' AND (CONVERT(nvarchar(4000), l.ItemCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), i.ItemCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.ItemCode))=DATALENGTH(CONVERT(nvarchar(4000), i.ItemCode))) WHERE h.ModuleCode IN (N'AR',N'SO') AND (h.InvoiceType IS NULL OR h.InvoiceType<>N'XD') AND h.InvoiceNo IS NOT NULL AND h.HeaderSeqNo IS NOT NULL AND (CONVERT(nvarchar(4000), l.InvoiceNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_no) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.InvoiceNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_no))) AND (CONVERT(nvarchar(4000), l.HeaderSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :invoice_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.HeaderSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :invoice_seq))) AND (CONVERT(nvarchar(4000), l.DetailSeqNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :detail_seq) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), l.DetailSeqNo))=DATALENGTH(CONVERT(nvarchar(4000), :detail_seq)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "D389B707-4A2F-5643-8EAA-D9F4FE890B9E",
"key": "CustomerKey",
"type": "text"
}
],
"id": "7DE70548-F3BB-5EF0-BAC3-1004BCE25B63",
"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": "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 customer address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine2",
"label": "Current customer address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "City",
"label": "Current customer city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "City",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "State",
"label": "Current customer state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "State",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZipCode",
"label": "Current customer postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CountryCode",
"label": "Current customer 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": "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": "CustomerKey",
"label": "Internal full history customer identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerKey",
"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": "History customer dossier",
"primaryKey": "CustomerKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "ARDivisionNo",
"id": "FF4322BB-3815-5E11-A495-5520856DF520",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "CustomerNo",
"id": "74171031-03E3-5078-BE15-E22B54046DEB",
"name": "customer",
"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": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"sqlQuery": "SELECT c.ARDivisionNo, c.CustomerNo, c.CustomerName, c.AddressLine1, c.AddressLine2, c.City, c.State, c.ZipCode, c.CountryCode, c.TelephoneNo, c.EmailAddress, c.ShipMethod, c.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, c.CustomerDiscountRate, c.CreditLimit, c.LastPaymentAmt, c.CurrentBalance, c.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo)),N':',CONVERT(nvarchar(4000), c.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo)),N':',CONVERT(nvarchar(4000), c.CustomerNo),N'|') AS CustomerKey, CASE c.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', c.CustomerStatus) END AS CustomerStatusName, c.CreditHold AS CreditHoldName FROM dbo.AR_Customer c WHERE (CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :customer) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :customer)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "BEA97E2C-4AB7-574B-9907-53CC8C5B1BA3",
"key": "BillToKey",
"type": "text"
}
],
"id": "6E0AA5FF-1014-5846-A0CB-052DF1AAA7C9",
"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": "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 customer address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine2",
"label": "Current customer address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "City",
"label": "Current customer city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "City",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "State",
"label": "Current customer state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "State",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZipCode",
"label": "Current customer postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CountryCode",
"label": "Current customer 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": "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": "BillToKey",
"label": "Internal full billing customer identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BillToKey",
"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": "Billing customer dossier",
"primaryKey": "BillToKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "BillToDivisionNo",
"id": "704EEBBA-02CC-54C9-B7E5-BDE6DBC5469B",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "BillToCustomerNo",
"id": "202FFC15-119E-58D8-995F-7029515B0B80",
"name": "customer",
"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": "65E8E826-E522-556D-AAEB-6481AFC5AF89",
"sqlQuery": "SELECT c.ARDivisionNo, c.CustomerNo, c.CustomerName, c.AddressLine1, c.AddressLine2, c.City, c.State, c.ZipCode, c.CountryCode, c.TelephoneNo, c.EmailAddress, c.ShipMethod, c.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastPayment, 112))),8) ELSE NULL END,112) AS DateLastPayment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), c.DateLastInvoice, 112))),8) ELSE NULL END,112) AS DateLastInvoice, c.CustomerDiscountRate, c.CreditLimit, c.LastPaymentAmt, c.CurrentBalance, c.OpenOrderAmt, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo)),N':',CONVERT(nvarchar(4000), c.ARDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo)),N':',CONVERT(nvarchar(4000), c.CustomerNo),N'|') AS BillToKey, CASE c.CustomerStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', c.CustomerStatus) END AS CustomerStatusName, c.CreditHold AS CreditHoldName FROM dbo.AR_Customer c WHERE (CONVERT(nvarchar(4000), c.ARDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.ARDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), c.CustomerNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :customer) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), c.CustomerNo))=DATALENGTH(CONVERT(nvarchar(4000), :customer)))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "8C18A044-4A6F-5F22-BA42-84D061022406",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "122EA8C7-D063-57C6-AEF9-2FFD1117E06C",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "040D5525-00DA-50CD-8AD3-408D86C122E6",
"title": "Invoice lines",
"urlKey": ""
},
{
"id": "ABA7D8B1-8781-5E2D-8DFE-D9CDD7F02F56",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "8219FEDF-2FCC-56E9-8418-89072DF3B608",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "7DE70548-F3BB-5EF0-BAC3-1004BCE25B63",
"title": "Customer",
"urlKey": ""
},
{
"id": "A969E440-C982-5C5D-9BB2-7ABAE45F7B98",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "26D7B68C-078F-5094-BE02-E7318BCA0120",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "6E0AA5FF-1014-5846-A0CB-052DF1AAA7C9",
"title": "Bill-to",
"urlKey": ""
}
],
"badgeKey": "InvoiceKind",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDate",
"label": "Invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDueDate",
"label": "Invoice due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BillToName",
"label": "Stored bill-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCity",
"label": "Stored delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TaxableSalesAmt",
"label": "Taxable sales — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SalesTaxAmt",
"label": "Sales tax — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "4FF432E0-C154-589E-B814-435BFB7CFE20",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ModuleCode",
"label": "Origin module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDate",
"label": "Invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TransactionDate",
"label": "Transaction date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "Customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerPONo",
"label": "Header customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ApplyToInvoiceNo",
"label": "Applies to invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Comment",
"label": "Stored invoice note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDueDate",
"label": "Invoice due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DiscountDueDate",
"label": "Discount due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalNoGLBatchNo",
"label": "Journal / G/L batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SalesOrderNo",
"label": "Header sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MultipleSalesOrdersApplied",
"label": "Multiple sales orders (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MultipleCustomerPONos",
"label": "Multiple customer POs (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MultipleDepositsApplied",
"label": "Multiple deposits (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToCustomerNo",
"label": "Stored bill-to customer code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToName",
"label": "Stored bill-to name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToAddress1",
"label": "Stored billing address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToAddress2",
"label": "Stored billing address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToAddress3",
"label": "Stored billing address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToCity",
"label": "Stored billing city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToState",
"label": "Stored billing state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToZipCode",
"label": "Stored billing postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice billing destination",
"detailRole": "information",
"isVisible": true,
"key": "BillToCountryCode",
"label": "Stored billing country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCode",
"label": "Delivery address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToName",
"label": "Stored delivery name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress1",
"label": "Stored delivery address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress2",
"label": "Stored delivery address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress3",
"label": "Stored delivery address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCity",
"label": "Stored delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToState",
"label": "Stored delivery state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToZipCode",
"label": "Stored delivery postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored invoice delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCountryCode",
"label": "Stored delivery country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ShipDate",
"label": "Shipping date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ShipVia",
"label": "Ship via code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseCode",
"label": "Warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxableSalesAmt",
"label": "Native taxable sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonTaxableSalesAmt",
"label": "Native nontaxable sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "FreightAmt",
"label": "Native freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "SalesTaxAmt",
"label": "Native sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CostOfSalesAmt",
"label": "Native cost of sales — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "AmountSubjectToDiscount",
"label": "Amount subject to discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "DiscountRate",
"label": "Native discount rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "DiscountAmt",
"label": "Native discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "DepositAmt",
"label": "Native deposit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "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": "NumberOfPackages",
"label": "Number of packages",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceKind",
"label": "Invoice type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OrderKind",
"label": "Source order type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "6AEB239E-3BEC-54D1-963D-04C187E8B09D",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "D99B306A-D929-581A-8541-E4BD716A2ADA",
"includeAllOption": false,
"key": "InvoiceDate",
"title": "Invoice history period",
"type": "date"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "descending",
"id": "4F735011-E033-5589-BF51-40D6F11DA441",
"key": "InvoiceDate",
"type": "date"
},
{
"direction": "ascending",
"id": "06CB09EC-4B1B-554D-B76D-C27672800C5B",
"key": "InvoiceIdentity",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "PartyDisplayName",
"systemImage": "doc.text",
"title": "Customer invoices",
"titleKey": "InvoiceNo"
},
{
"actions": [
{
"id": "CE631B42-3A87-5F85-BA3C-8FA6A7CC1A40",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "96B1DEAC-A058-54CC-9086-9F578FCAEF7B",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "2EE2611B-E539-5BE9-810D-5E5F8401A4AA",
"title": "Item dossier",
"urlKey": ""
}
],
"badgeKey": "ItemTypeName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Invoice line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PromiseDate",
"label": "Promised date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QuantityShipped",
"label": "Shipped — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrdered",
"label": "Ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ExtensionAmt",
"label": "Line amount — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "040D5525-00DA-50CD-8AD3-408D86C122E6",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceNo",
"label": "Invoice number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemCodeDesc",
"label": "Item / line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Invoice line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseCode",
"label": "Warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AliasItemNo",
"label": "Customer item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CommentText",
"label": "Line note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PromiseDate",
"label": "Promised date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorNo",
"label": "Line vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseOrderNo",
"label": "Line purchase order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseOrderRequiredDate",
"label": "Line purchase required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityShipped",
"label": "Native shipped quantity — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrdered",
"label": "Native ordered quantity — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityBackordered",
"label": "Native backordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "UnitPrice",
"label": "Historical unit price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "UnitCost",
"label": "Historical unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasureConvFactor",
"label": "Native invoice unit factor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "LineDiscountPercent",
"label": "Line discount (%) — already reflected by Sage",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "ExtensionAmt",
"label": "Native line extension — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxAmt",
"label": "Native line tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "TaxRate",
"label": "Native line tax rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AppliedSalesOrderNo",
"label": "Line sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AppliedCustomerPONo",
"label": "Line customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemTypeName",
"label": "Line type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ValuationName",
"label": "Valuation method",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ProductAvailability",
"label": "Inventory item dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "137BC471-D038-5C1D-B75F-039417B5AD21",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "B12B6571-C144-54BA-BF3D-039EBA5F7017",
"key": "DetailSeqNo",
"type": "text"
},
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ItemCode",
"systemImage": "doc.text",
"title": "Invoice lines",
"titleKey": "ItemCodeDesc"
},
{
"actions": [],
"badgeKey": "ItemState",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitOfMeasure",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitPrice",
"label": "Reference price — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TotalQuantityOnHand",
"label": "On hand — stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "2EE2611B-E539-5BE9-810D-5E5F8401A4AA",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemCodeDesc",
"label": "Item / line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "SalesUnitOfMeasure",
"label": "Sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseUnitOfMeasure",
"label": "Purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitOfMeasure",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DefaultWarehouseCode",
"label": "Default warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastSoldDate",
"label": "Item last sold",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastReceiptDate",
"label": "Item last received",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitCost",
"label": "Standard cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitPrice",
"label": "Standard price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "LastTotalUnitCost",
"label": "Last total cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "AverageUnitCost",
"label": "Average cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "TotalQuantityOnHand",
"label": "On hand — all warehouses",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseUMConvFctr",
"label": "Stock units per purchase unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "SalesUMConvFctr",
"label": "Stock units per sales unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "UPCEAN",
"label": "UPC / EAN",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemState",
"label": "Item status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ValuationName",
"label": "Valuation method",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ProcurementName",
"label": "Procurement",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "2A196392-887E-5980-A542-3B6B57293302",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ItemCode",
"systemImage": "doc.text",
"title": "Item dossier",
"titleKey": "ItemCodeDesc"
},
{
"actions": [],
"badgeKey": "CustomerStatusName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current customer 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": "Current balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "7DE70548-F3BB-5EF0-BAC3-1004BCE25B63",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "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 customer address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine2",
"label": "Current customer address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current customer city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "State",
"label": "Current customer state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ZipCode",
"label": "Current customer postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CountryCode",
"label": "Current customer 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": "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": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "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": "72B9545E-90B1-5FCC-9D28-60D1E0B85C74",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "D389B707-4A2F-5643-8EAA-D9F4FE890B9E",
"key": "CustomerKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CustomerNo",
"systemImage": "doc.text",
"title": "History customer dossier",
"titleKey": "CustomerName"
},
{
"actions": [],
"badgeKey": "CustomerStatusName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current customer 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": "Current balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "6E0AA5FF-1014-5846-A0CB-052DF1AAA7C9",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerNo",
"label": "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 customer address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine2",
"label": "Current customer address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current customer city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "State",
"label": "Current customer state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ZipCode",
"label": "Current customer postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CountryCode",
"label": "Current customer 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": "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": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "CustomerDiscountRate",
"label": "Customer discount rate (%)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CreditLimit",
"label": "Current credit limit — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "LastPaymentAmt",
"label": "Current master last payment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "CurrentBalance",
"label": "Current customer balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "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": "7A42FA26-2C74-5B90-BB89-84AF1EA66FB5",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "BEA97E2C-4AB7-574B-9907-53CC8C5B1BA3",
"key": "BillToKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "CustomerNo",
"systemImage": "doc.text",
"title": "Billing customer dossier",
"titleKey": "CustomerName"
}
],
"relations": [
{
"childDatasetID": "040D5525-00DA-50CD-8AD3-408D86C122E6",
"childKey": "InvoiceIdentity",
"id": "122EA8C7-D063-57C6-AEF9-2FFD1117E06C",
"name": "Invoice lines",
"parentDatasetID": "4FF432E0-C154-589E-B814-435BFB7CFE20",
"parentKey": "InvoiceIdentity"
},
{
"childDatasetID": "2EE2611B-E539-5BE9-810D-5E5F8401A4AA",
"childKey": "LineIdentity",
"id": "96B1DEAC-A058-54CC-9086-9F578FCAEF7B",
"name": "Item dossier",
"parentDatasetID": "040D5525-00DA-50CD-8AD3-408D86C122E6",
"parentKey": "LineIdentity"
},
{
"childDatasetID": "7DE70548-F3BB-5EF0-BAC3-1004BCE25B63",
"childKey": "CustomerKey",
"id": "8219FEDF-2FCC-56E9-8418-89072DF3B608",
"name": "History customer dossier",
"parentDatasetID": "4FF432E0-C154-589E-B814-435BFB7CFE20",
"parentKey": "CustomerKey"
},
{
"childDatasetID": "6E0AA5FF-1014-5846-A0CB-052DF1AAA7C9",
"childKey": "BillToKey",
"id": "26D7B68C-078F-5094-BE02-E7318BCA0120",
"name": "Billing customer dossier",
"parentDatasetID": "4FF432E0-C154-589E-B814-435BFB7CFE20",
"parentKey": "BillToKey"
}
],
"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 candidate based on Sage 100 US 2026 FLOR Rel 7.50 complete documented invoice keys, public functional help and synthetic tests. Verify schema, full identities, units, source currency, collation, permissions, date adapters and query cost at import before business use. Native DEMO screenshots do not certify SQL Server execution.\n\nFor accounting, sales, dispatch and managers: choose an invoice-date period, inspect retained AR/SO invoice, credit, debit and adjustment context, stored billing/delivery addresses, due dates, native sales/tax/freight/discount/deposit/retention values and references. Open precise historical invoice lines and their current inventory item dossier. Two separate customer buttons preserve history-customer and billing-customer identities, current contacts, credit context and native balances.\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, five lists and four separate lazy buttons, parameterized read-only SELECT, at most 2,000 rows per request, no scheduled refresh. Both opening-period boundaries apply before the read limit. Narrow the period on a limit hit; search/local filters cover loaded rows only. Row limits and filters are not a query-cost guarantee or an authorization boundary.\n\nTarget: retained AR/SO invoice history in Sage 100 US 2026 SQL Server company databases, formerly Premium. Deleted XD history is excluded; Job Cost-origin documents are outside this dossier. Known invoice type codes use their own documented meaning; unknown values remain unknown. Credit/debit/adjustments can share an invoice number. Invoice identity includes the full case-sensitive HeaderSeqNo and line identity also includes DetailSeqNo. SQL compares explicit binary Unicode values and byte lengths, not an unverified default case-insensitive collation. Verify stored types, padding, identity handling and parameters on the actual installation. No deduplication by invoice or item code.\n\nHistory is not a current receivables ledger. No paid/unpaid state, invoice balance, aging, exchange rate, calculated invoice total or sign normalization is invented. Native header amounts and line extensions/tax are displayed separately, without summing credit/debit records, reapplying discounts or replacing NULL by zero. Source currency must be verified; USD is not assumed. Historical line units, prices and costs differ from current item master quantities/reference prices/costs. Precise quantities, costs, factors and rates remain text; their local sorting/filtering is textual.\n\nAR Retain in Detail Yes, Summary and No retain different information. Comment/deleted-invoice options and purges affect history. Missing lines do not prove a zero invoice or no activity. Multiple orders/customer POs/deposits are flagged; header references do not exhaust line-level applied references. Module-dependent values may be empty. Cifru does not change ERP options.\n\nWith bill-to reporting enabled, invoice history is maintained for sold-to while posting/payment uses bill-to. Keep the history and billing customer dossiers separate, including division. Missing current customer masters do not remove history; a missing billing master is not replaced by the history customer. Current customer balance, credit limit, open orders and last payment are current master information, not that invoice balance/payment or values as of the opening period. Stored invoice addresses remain separate from current customer contacts/addresses. Never add both roles sales history into one overstated total. Special/charge/comment/miscellaneous and orphan inventory lines stay in the line list without fabricated item dossiers.\n\nSQL Server 2012+ / compatibility 110+ is required for defensive TRY_CONVERT dates. Unsupported or corrupt date values become NULL, not 1900. FLOR types/keys are logical evidence, not installed SQL DDL, indexes or collation certification. Verify dbo/company scope, full column owners, modules, NULL/units/monetary signs, padding, permissions, response time and read test at import. Direct SQL does not inherit native operator roles; use a separately authorized least-privilege SELECT login. No credentials, server addresses, cached business rows, banking/taxpayer/payment identifiers or internal audit users are exported. Any later gallery must use entirely fictional DEMO values in real Cifru screens. Unofficial, not endorsed by Sage; not Sage 100 France/Contractor/ProvideX.\n\nDictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/\nHistory and options: https://help-sage100.na.sage.com/2026/Subsystems/AR/ARMainFields/Invoice_History_Inquiry_-_Fields1.htm\nRetention and customer roles: https://help-sage100.na.sage.com/2026/Subsystems/AR/ARSetupFields/Accounts_Receivable_Options_-_Fields.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "21E3AD08-7A9E-50DE-9649-F1DB79E01648",
"rootButtonCount": 1,
"summary": "Invoice period, native historical values and precise lines; item and separate history/billing customer dossiers.",
"tags": [
"Sage 100 US",
"SQL Server",
"Customer invoices",
"Accounting",
"Sales",
"Pro"
],
"title": "Customer invoices and full dossiers — Pro"
}
}