Supplier invoices and full dossiers — Pro
Invoice-date period, native AP/PO context and precise lines; current item and vendor dossiers.
NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial candidate based on Sage 100 US 2026 FLOR Rel 7.50 selected AP/PO invoice layouts, complete logical keys, functional help and synthetic validation. Verify installed schema, identities, units, currency, NULL, collation, permissions, date adapters and query cost at import. Not a real Sage SQL Server test.
For accounting, purchasing and managers: choose an invoice-date period and inspect retained AP/PO supplier invoices, due/discount dates, stored vendor and destination context, native amounts, retention and order/receipt references. Open own invoice lines, their available PO inventory item dossier and the separately identified current vendor.
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, four lists, three lazy buttons, parameterized read-only SELECT, at most 2,000 rows per request, no scheduled refresh. Both opening-period boundaries apply before the limit. Narrow the period on a limit hit. Local search/filters cover loaded rows only, not an authorization or query-cost boundary.
AP/PO invoice history, not current payable settlement or a complete payment ledger. InvoiceNo plus HeaderSeqNo identifies each history header; lines also require DetailSeqNo. Vendor identity includes APDivisionNo plus VendorNo. Never deduplicate by invoice, vendor or item number alone. Explicit binary Unicode and byte-length comparisons preserve full strings; physical types, padding and collation must be tested on the actual installation. No AR-specific case annotation is claimed for AP.
Native AP InvoiceType and source PurchaseOrderType codes stay untranslated: the selected AP layout does not certify their legend. AR IN/CM/DM/AD/FC/CA/XD meanings are not copied. No deleted-state, credit/debit sign normalization or unsupported classification is invented. Known item types use their own AP line documentation; unknown values stay unknown.
InvoiceAmt, NetInvoiceAmt, discount, prepayment, retention, tax and other amounts are native components, not a calculated amount due or proof of payment. Source currency is not assumed USD. NULL is not replaced by zero. Native HoldPayment and prepaid references/dates are context, not settlement. Adjustment invoice payment review uses the original invoice; this dossier does not claim to provide the Payments tab or link to open items.
Track Detailed Invoice/Payment History, comment retention and purges affect availability. Missing lines do not prove no invoice, zero value or no payment. Multiple PO/prepayment flags are preserved; a single header PO and aggregate prepayment do not allocate values to each line. AP and PO fields can be empty or inapplicable by source module. Line comments provide a title when no item description is retained, without inventing an item. Only PO inventory lines with an existing master open an item dossier. Current item/vendor data differs from stored destinations and historical quantities/costs. Original PO quantity/cost, received quantity and invoiced quantity remain separate. Precise values are text with textual local filters/sorting, not silently rounded.
No fabricated G/L account number from internal AccountKey, banking/taxpayer/check/transfer data or audit user identifiers. No source credentials, server addresses or business rows in the package. Any later native gallery must use wholly fictional DEMO values. SQL Server 2012+/compatibility 110+ for TRY_CONVERT dates; invalid dates become NULL, not 1900. Public FLOR layouts are logical evidence, not installed DDL, indexes, types or performance. Verify dbo/company/module scope, full keys, dates, units, NULL, monetary meanings, schema compatibility and response time at import. Native operator security does not carry into direct SQL: use separately authorized least-privilege SELECT access. Unofficial, not endorsed by Sage; not France/Contractor/ProvideX.
Dictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/
AP history: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAIN/INV_HIST_INQUIRY_OVR.htm
AP fields: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAINFIELD/Invoice_History_Inquiry_-_Fields.htm
Screenshots
What this package creates
1 Home3 Details4 lists1 sources to map
- Home: Supplier invoices
- Details: Invoice lines
- Details: Item dossier
- Details: Vendor dossier
- Sub-button: Invoice lines
- Sub-button: Item dossier
- Sub-button: Vendor dossier
Sources are mapped locally and verified before applying.
Custom queriesPRO4 SQL
Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.
Statically verified read-only
$.components.workspaceSelection.datasets.0.sqlQuerySELECT h.InvoiceNo, h.HeaderSeqNo, h.ModuleCode, h.InvoiceType, h.APDivisionNo, h.VendorNo, 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.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.InvoiceDiscountDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),8) ELSE NULL END,112) AS InvoiceDiscountDate, 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.TermsCode, h.HoldPayment, h.Comment, h.PrepaidApplyToInvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),8) ELSE NULL END,112) AS PrepaidPaymentDate, h.SourceJournal, h.SourceJournalNo, h.JobNo, h.VendorName, h.PurchaseOrderNo, h.MultiplePurchaseOrdersApplied, h.MultiplePrepaymentsApplied, h.ReceiptNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),8) ELSE NULL END,112) AS ReceiptDate, h.PurchaseAddressCode, h.PurchaseName, h.PurchaseAddress1, h.PurchaseAddress2, h.PurchaseAddress3, h.PurchaseCity, h.PurchaseState, h.PurchaseZipCode, h.PurchaseCountryCode, 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.RequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),8) ELSE NULL END,112) AS RequiredDate, h.ShipVia, h.WarehouseCode, h.PurchaseOrderType, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),8) ELSE NULL END,112) AS PurchaseOrderDate, h.InvoiceAmt, h.NetInvoiceAmt, h.DiscountAmt, h.RetentionAmt, h.PrepaymentAmt, h.TaxableAmt, h.NonTaxableAmt, h.FreightAmt, h.TaxAmt, h.NonRecoverableAmt, h.TaxableMiscChargeAmt, h.NonTaxableMiscChargeAmt, 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.APDivisionNo)),N':',CONVERT(nvarchar(4000), h.APDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.VendorNo)),N':',CONVERT(nvarchar(4000), h.VendorNo),N'|') AS VendorKey, COALESCE(NULLIF(h.VendorName,N''), h.VendorNo) AS PartyDisplayName FROM dbo.AP_InvoiceHistoryHeader h WHERE h.ModuleCode IN (N'AP',N'PO') 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.JobNo, l.CostCode, l.CostType, l.CommentText, l.ItemCode, l.ItemCodeDesc, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),8) ELSE NULL END,112) AS RequiredDate, l.VendorPriceCode, l.UnitOfMeasure, l.WarehouseCode, l.POWarehouseCode, l.ProductLine, l.VendorAliasItemNo, l.SalesOrderNo, l.CustomerPONo, l.AppliedPurchaseOrderNo, l.RetentionRate, l.RetentionAmt, l.QuantityOrdered, l.QuantityReceived, l.QuantityInvoiced, l.UnitCost, l.OriginalUnitCost, l.ExtensionAmt, l.QuantityOrderedOriginal, l.UnitOfMeasureConvFactor, 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, CASE l.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: ', l.Valuation) END AS ValuationName, COALESCE(NULLIF(l.ItemCodeDesc,N''),NULLIF(l.CommentText,N''),N'No retained line description') AS LineDisplayName, CASE WHEN h.ModuleCode<>N'PO' OR l.ItemType IS NULL OR l.ItemType<>N'1' THEN N'Not an inventory PO line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.AP_InvoiceHistoryDetail l INNER JOIN dbo.AP_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 h.ModuleCode=N'PO' AND 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'AP',N'PO') 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.AP_InvoiceHistoryDetail l INNER JOIN dbo.AP_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 h.ModuleCode=N'PO' AND 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'AP',N'PO') 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 v.APDivisionNo, v.VendorNo, v.VendorName, v.AddressLine1, v.AddressLine2, v.AddressLine3, v.City, v.State, v.ZipCode, v.CountryCode, v.PrimaryContact, v.TelephoneNo, v.TelephoneExt, v.EmailAddress, v.TermsCode, v.Reference, v.HoldPayment, v.Comment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),8) ELSE NULL END,112) AS LastPurchaseDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),8) ELSE NULL END,112) AS LastPaymentDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),8) ELSE NULL END,112) AS DateEstablished, v.AverageDaysToPay, v.AverageDaysOverDue, v.BalanceDue, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), v.APDivisionNo)),N':',CONVERT(nvarchar(4000), v.APDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), v.VendorNo)),N':',CONVERT(nvarchar(4000), v.VendorNo),N'|') AS VendorKey, CASE v.VendorStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', v.VendorStatus) END AS VendorState FROM dbo.AP_Vendor v WHERE (CONVERT(nvarchar(4000), v.APDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), v.APDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), v.VendorNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :vendor) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), v.VendorNo))=DATALENGTH(CONVERT(nvarchar(4000), :vendor)))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 100 US — SQL Server company database",
"id": "661F3036-70B3-539A-AAC6-99FC18058BA0",
"kind": "sqlServer",
"requiredObjects": [
"dbo.AP_InvoiceHistoryDetail",
"dbo.AP_InvoiceHistoryHeader",
"dbo.AP_Vendor",
"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": "F041C99A-9DB1-5D3F-8D38-5ADE8D1D94CD",
"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": true
},
{
"commonFieldKey": "",
"key": "InvoiceType",
"label": "Native invoice type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "APDivisionNo",
"label": "Internal vendor division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "APDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorNo",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorNo",
"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": "InvoiceDueDate",
"label": "Invoice due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDueDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "InvoiceDiscountDate",
"label": "Invoice discount date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceDiscountDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TransactionDate",
"label": "Transaction date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TransactionDate",
"type": "date",
"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": "HoldPayment",
"label": "Native invoice hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HoldPayment",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Comment",
"label": "Note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Comment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PrepaidApplyToInvoiceNo",
"label": "Native prepaid apply-to reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PrepaidApplyToInvoiceNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PrepaidPaymentDate",
"label": "Native prepaid payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PrepaidPaymentDate",
"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": "SourceJournalNo",
"label": "Source journal reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournalNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JobNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorName",
"label": "Stored invoice vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseOrderNo",
"label": "Purchase order number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MultiplePurchaseOrdersApplied",
"label": "Multiple purchase orders (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MultiplePurchaseOrdersApplied",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MultiplePrepaymentsApplied",
"label": "Multiple prepayments (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MultiplePrepaymentsApplied",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReceiptNo",
"label": "Receipt reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReceiptNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReceiptDate",
"label": "Receipt date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReceiptDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddressCode",
"label": "Stored purchase address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddressCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseName",
"label": "Stored purchase name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress1",
"label": "Stored purchase address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress2",
"label": "Stored purchase address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress3",
"label": "Stored purchase address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseCity",
"label": "Stored purchase city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseCity",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseState",
"label": "Stored purchase state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseZipCode",
"label": "Stored purchase postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseCountryCode",
"label": "Stored purchase country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseCountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCode",
"label": "Stored delivery address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToName",
"label": "Deliver to",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress1",
"label": "Delivery address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress2",
"label": "Delivery address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToAddress3",
"label": "Delivery address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToAddress3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCity",
"label": "Delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCity",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToState",
"label": "Delivery state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToZipCode",
"label": "Delivery postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipToCountryCode",
"label": "Delivery country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ShipToCountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RequiredDate",
"label": "Required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequiredDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ShipVia",
"label": "Shipping method 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": "PurchaseOrderType",
"label": "Native source PO type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseOrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoiceAmt",
"label": "Native invoice amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoiceAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "NetInvoiceAmt",
"label": "Native net invoice amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NetInvoiceAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DiscountAmt",
"label": "Native discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DiscountAmt",
"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": "PrepaymentAmt",
"label": "Native prepayment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PrepaymentAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxableAmt",
"label": "Native taxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonTaxableAmt",
"label": "Native nontaxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonTaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FreightAmt",
"label": "Native freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FreightAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxAmt",
"label": "Native tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonRecoverableAmt",
"label": "Native nonrecoverable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonRecoverableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxableMiscChargeAmt",
"label": "Native taxable miscellaneous — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxableMiscChargeAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonTaxableMiscChargeAmt",
"label": "Native nontaxable miscellaneous — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonTaxableMiscChargeAmt",
"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": "VendorKey",
"label": "Internal full vendor identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartyDisplayName",
"label": "Stored invoice vendor / code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartyDisplayName",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Supplier invoices",
"primaryKey": "InvoiceIdentity",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceDate",
"id": "570A4E58-1B9F-5EFF-9ECE-2D53F3A28635",
"name": "date_from",
"source": "openingPeriodStart",
"type": "date"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "InvoiceDate",
"id": "B1B03D70-00BA-5D42-8C68-B186034ED0F9",
"name": "date_until",
"source": "openingPeriodEndExclusive",
"type": "date"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvoiceNo",
"ModuleCode",
"InvoiceType",
"VendorNo",
"TermsCode",
"HoldPayment",
"Comment",
"PrepaidApplyToInvoiceNo",
"SourceJournal",
"SourceJournalNo",
"JobNo",
"VendorName",
"PurchaseOrderNo",
"MultiplePurchaseOrdersApplied",
"MultiplePrepaymentsApplied",
"ReceiptNo",
"PurchaseAddressCode",
"PurchaseName",
"PurchaseAddress1",
"PurchaseAddress2",
"PurchaseAddress3",
"PurchaseCity",
"PurchaseState",
"PurchaseZipCode",
"PurchaseCountryCode",
"ShipToCode",
"ShipToName",
"ShipToAddress1",
"ShipToAddress2",
"ShipToAddress3",
"ShipToCity",
"ShipToState",
"ShipToZipCode",
"ShipToCountryCode",
"ShipVia",
"WarehouseCode",
"PurchaseOrderType"
],
"sourceID": "661F3036-70B3-539A-AAC6-99FC18058BA0",
"sqlQuery": "SELECT h.InvoiceNo, h.HeaderSeqNo, h.ModuleCode, h.InvoiceType, h.APDivisionNo, h.VendorNo, 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.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.InvoiceDiscountDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.InvoiceDiscountDate, 112))),8) ELSE NULL END,112) AS InvoiceDiscountDate, 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.TermsCode, h.HoldPayment, h.Comment, h.PrepaidApplyToInvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PrepaidPaymentDate, 112))),8) ELSE NULL END,112) AS PrepaidPaymentDate, h.SourceJournal, h.SourceJournalNo, h.JobNo, h.VendorName, h.PurchaseOrderNo, h.MultiplePurchaseOrdersApplied, h.MultiplePrepaymentsApplied, h.ReceiptNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.ReceiptDate, 112))),8) ELSE NULL END,112) AS ReceiptDate, h.PurchaseAddressCode, h.PurchaseName, h.PurchaseAddress1, h.PurchaseAddress2, h.PurchaseAddress3, h.PurchaseCity, h.PurchaseState, h.PurchaseZipCode, h.PurchaseCountryCode, 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.RequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredDate, 112))),8) ELSE NULL END,112) AS RequiredDate, h.ShipVia, h.WarehouseCode, h.PurchaseOrderType, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.PurchaseOrderDate, 112))),8) ELSE NULL END,112) AS PurchaseOrderDate, h.InvoiceAmt, h.NetInvoiceAmt, h.DiscountAmt, h.RetentionAmt, h.PrepaymentAmt, h.TaxableAmt, h.NonTaxableAmt, h.FreightAmt, h.TaxAmt, h.NonRecoverableAmt, h.TaxableMiscChargeAmt, h.NonTaxableMiscChargeAmt, 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.APDivisionNo)),N':',CONVERT(nvarchar(4000), h.APDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), h.VendorNo)),N':',CONVERT(nvarchar(4000), h.VendorNo),N'|') AS VendorKey, COALESCE(NULLIF(h.VendorName,N''), h.VendorNo) AS PartyDisplayName FROM dbo.AP_InvoiceHistoryHeader h WHERE h.ModuleCode IN (N'AP',N'PO') 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": "B2488603-E08D-56BA-9F27-D3651F361AF9",
"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": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JobNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CostCode",
"label": "Job cost code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CostCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CostType",
"label": "Native job cost type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CostType",
"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": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCodeDesc",
"label": "Item",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCodeDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RequiredDate",
"label": "Required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequiredDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorPriceCode",
"label": "Vendor price reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorPriceCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitOfMeasure",
"label": "Historical 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": "POWarehouseCode",
"label": "Source PO warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "POWarehouseCode",
"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": "VendorAliasItemNo",
"label": "Vendor item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorAliasItemNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesOrderNo",
"label": "Line sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CustomerPONo",
"label": "Line customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CustomerPONo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AppliedPurchaseOrderNo",
"label": "Line applied purchase order",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AppliedPurchaseOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RetentionRate",
"label": "Native line retention rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RetentionRate",
"type": "text",
"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": "QuantityOrdered",
"label": "Native ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityOrdered",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QuantityReceived",
"label": "Native received — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityReceived",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityInvoiced",
"label": "Native invoiced — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityInvoiced",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnitCost",
"label": "Historical invoice unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OriginalUnitCost",
"label": "Original order unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OriginalUnitCost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ExtensionAmt",
"label": "Native line amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ExtensionAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityOrderedOriginal",
"label": "Original ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityOrderedOriginal",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitOfMeasureConvFactor",
"label": "Native line unit factor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitOfMeasureConvFactor",
"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": "LineDisplayName",
"label": "Retained line description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDisplayName",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProductAvailability",
"label": "Item dossier availability",
"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": "BEF8BEE9-2B78-5758-9329-38E146B71E22",
"name": "invoice_no",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "HeaderSeqNo",
"id": "F90276E0-9B81-5200-960E-EDB25F44DEA9",
"name": "invoice_seq",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"InvoiceNo",
"JobNo",
"CostCode",
"CostType",
"CommentText",
"ItemCode",
"ItemCodeDesc",
"VendorPriceCode",
"UnitOfMeasure",
"WarehouseCode",
"POWarehouseCode",
"ProductLine",
"VendorAliasItemNo",
"SalesOrderNo",
"CustomerPONo",
"AppliedPurchaseOrderNo",
"RetentionRate",
"QuantityOrdered",
"QuantityReceived",
"QuantityInvoiced",
"UnitCost",
"OriginalUnitCost",
"QuantityOrderedOriginal",
"UnitOfMeasureConvFactor",
"ItemTypeName",
"ValuationName",
"ProductAvailability"
],
"sourceID": "661F3036-70B3-539A-AAC6-99FC18058BA0",
"sqlQuery": "SELECT l.InvoiceNo, l.HeaderSeqNo, l.DetailSeqNo, l.JobNo, l.CostCode, l.CostType, l.CommentText, l.ItemCode, l.ItemCodeDesc, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), l.RequiredDate, 112))),8) ELSE NULL END,112) AS RequiredDate, l.VendorPriceCode, l.UnitOfMeasure, l.WarehouseCode, l.POWarehouseCode, l.ProductLine, l.VendorAliasItemNo, l.SalesOrderNo, l.CustomerPONo, l.AppliedPurchaseOrderNo, l.RetentionRate, l.RetentionAmt, l.QuantityOrdered, l.QuantityReceived, l.QuantityInvoiced, l.UnitCost, l.OriginalUnitCost, l.ExtensionAmt, l.QuantityOrderedOriginal, l.UnitOfMeasureConvFactor, 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, CASE l.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: ', l.Valuation) END AS ValuationName, COALESCE(NULLIF(l.ItemCodeDesc,N''),NULLIF(l.CommentText,N''),N'No retained line description') AS LineDisplayName, CASE WHEN h.ModuleCode<>N'PO' OR l.ItemType IS NULL OR l.ItemType<>N'1' THEN N'Not an inventory PO line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.AP_InvoiceHistoryDetail l INNER JOIN dbo.AP_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 h.ModuleCode=N'PO' AND 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'AP',N'PO') 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": "90013FF2-0216-5E5E-B77A-90B21C512313",
"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",
"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 date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastSoldDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastReceiptDate",
"label": "Last receipt date",
"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": true
},
{
"commonFieldKey": "",
"key": "StandardUnitPrice",
"label": "Standard price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "StandardUnitPrice",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"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 method",
"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": "BEF8BEE9-2B78-5758-9329-38E146B71E22",
"name": "invoice_no",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "HeaderSeqNo",
"id": "F90276E0-9B81-5200-960E-EDB25F44DEA9",
"name": "invoice_seq",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "DetailSeqNo",
"id": "1210BCB2-CEFF-541F-858D-3D16B94F2F10",
"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": "661F3036-70B3-539A-AAC6-99FC18058BA0",
"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.AP_InvoiceHistoryDetail l INNER JOIN dbo.AP_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 h.ModuleCode=N'PO' AND 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'AP',N'PO') 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": "BC515F3F-CAEC-53F1-9967-06042027411E",
"key": "VendorKey",
"type": "text"
}
],
"id": "496EFFBA-34EF-5217-93D1-5B09EA3235C5",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "APDivisionNo",
"label": "Internal vendor division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "APDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorNo",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorName",
"label": "Current vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AddressLine3",
"label": "Current address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AddressLine3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "City",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "State",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CountryCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PrimaryContact",
"label": "Primary contact code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PrimaryContact",
"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": "TelephoneExt",
"label": "Telephone extension",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TelephoneExt",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "EmailAddress",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Reference",
"label": "Vendor reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Reference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "HoldPayment",
"label": "Payment selection hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HoldPayment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Comment",
"label": "Note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Comment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastPurchaseDate",
"label": "Vendor last purchase date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastPurchaseDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastPaymentDate",
"label": "Vendor last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastPaymentDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateEstablished",
"label": "Vendor established date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateEstablished",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AverageDaysToPay",
"label": "Stored average days to pay",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AverageDaysToPay",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AverageDaysOverDue",
"label": "Stored average days overdue",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AverageDaysOverDue",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BalanceDue",
"label": "Current vendor balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BalanceDue",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "VendorKey",
"label": "Internal full vendor identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorState",
"label": "Vendor status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Vendor dossier",
"primaryKey": "VendorKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "APDivisionNo",
"id": "3526A5B9-4DEF-5D73-BE33-51B0C5B1CB77",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "VendorNo",
"id": "6352742A-D0DA-509B-95C4-F6F458A5F5B3",
"name": "vendor",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"VendorNo",
"VendorName",
"AddressLine1",
"AddressLine2",
"AddressLine3",
"City",
"State",
"ZipCode",
"CountryCode",
"PrimaryContact",
"TelephoneNo",
"TelephoneExt",
"EmailAddress",
"TermsCode",
"Reference",
"HoldPayment",
"Comment",
"VendorState"
],
"sourceID": "661F3036-70B3-539A-AAC6-99FC18058BA0",
"sqlQuery": "SELECT v.APDivisionNo, v.VendorNo, v.VendorName, v.AddressLine1, v.AddressLine2, v.AddressLine3, v.City, v.State, v.ZipCode, v.CountryCode, v.PrimaryContact, v.TelephoneNo, v.TelephoneExt, v.EmailAddress, v.TermsCode, v.Reference, v.HoldPayment, v.Comment, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPurchaseDate, 112))),8) ELSE NULL END,112) AS LastPurchaseDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.LastPaymentDate, 112))),8) ELSE NULL END,112) AS LastPaymentDate, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), v.DateEstablished, 112))),8) ELSE NULL END,112) AS DateEstablished, v.AverageDaysToPay, v.AverageDaysOverDue, v.BalanceDue, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), v.APDivisionNo)),N':',CONVERT(nvarchar(4000), v.APDivisionNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), v.VendorNo)),N':',CONVERT(nvarchar(4000), v.VendorNo),N'|') AS VendorKey, CASE v.VendorStatus WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'T' THEN N'Temporary' ELSE CONCAT(N'Unknown / unset: ', v.VendorStatus) END AS VendorState FROM dbo.AP_Vendor v WHERE (CONVERT(nvarchar(4000), v.APDivisionNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :division) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), v.APDivisionNo))=DATALENGTH(CONVERT(nvarchar(4000), :division))) AND (CONVERT(nvarchar(4000), v.VendorNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :vendor) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), v.VendorNo))=DATALENGTH(CONVERT(nvarchar(4000), :vendor)))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "5F515585-91D0-512D-A28A-B032A03652C4",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "5656A03D-47B9-56DD-9A57-B0E3B250EBFA",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "B2488603-E08D-56BA-9F27-D3651F361AF9",
"title": "Invoice lines",
"urlKey": ""
},
{
"id": "EF63E745-13C9-5D1D-9F4C-BA455E73F717",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "084434C5-D6CE-50BA-A619-3C8664FBFC95",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "496EFFBA-34EF-5217-93D1-5B09EA3235C5",
"title": "Vendor dossier",
"urlKey": ""
}
],
"badgeKey": "InvoiceType",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ModuleCode",
"label": "Origin module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"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": "HoldPayment",
"label": "Native invoice hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceAmt",
"label": "Invoice amount — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "NetInvoiceAmt",
"label": "Net invoice — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "F041C99A-9DB1-5D3F-8D38-5ADE8D1D94CD",
"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": "InvoiceType",
"label": "Native invoice type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorNo",
"label": "Vendor 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": "InvoiceDueDate",
"label": "Invoice due date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceDiscountDate",
"label": "Invoice discount 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": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "HoldPayment",
"label": "Native invoice hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Comment",
"label": "Note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PrepaidApplyToInvoiceNo",
"label": "Native prepaid apply-to reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PrepaidPaymentDate",
"label": "Native prepaid payment 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": "SourceJournalNo",
"label": "Source journal reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorName",
"label": "Stored invoice vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseOrderNo",
"label": "Purchase order number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MultiplePurchaseOrdersApplied",
"label": "Multiple purchase orders (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MultiplePrepaymentsApplied",
"label": "Multiple prepayments (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ReceiptNo",
"label": "Receipt reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ReceiptDate",
"label": "Receipt date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddressCode",
"label": "Stored purchase address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseName",
"label": "Stored purchase name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress1",
"label": "Stored purchase address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress2",
"label": "Stored purchase address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress3",
"label": "Stored purchase address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseCity",
"label": "Stored purchase city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseState",
"label": "Stored purchase state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseZipCode",
"label": "Stored purchase postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored purchase destination",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseCountryCode",
"label": "Stored purchase country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCode",
"label": "Stored delivery address code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToName",
"label": "Deliver to",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress1",
"label": "Delivery address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress2",
"label": "Delivery address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress3",
"label": "Delivery address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCity",
"label": "Delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToState",
"label": "Delivery state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToZipCode",
"label": "Delivery postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Stored delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCountryCode",
"label": "Delivery country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RequiredDate",
"label": "Required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ShipVia",
"label": "Shipping method code",
"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": "PurchaseOrderType",
"label": "Native source PO type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseOrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "InvoiceAmt",
"label": "Native invoice amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NetInvoiceAmt",
"label": "Native net invoice amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "DiscountAmt",
"label": "Native discount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "RetentionAmt",
"label": "Native retention — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "PrepaymentAmt",
"label": "Native prepayment — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxableAmt",
"label": "Native taxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonTaxableAmt",
"label": "Native nontaxable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "FreightAmt",
"label": "Native freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxAmt",
"label": "Native tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonRecoverableAmt",
"label": "Native nonrecoverable amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxableMiscChargeAmt",
"label": "Native taxable miscellaneous — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonTaxableMiscChargeAmt",
"label": "Native nontaxable miscellaneous — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "3A2E9088-99C8-58A3-8C63-6B6BC1B01053",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "16DC3651-D741-5C23-937F-AF08160FFC89",
"includeAllOption": false,
"key": "InvoiceDate",
"title": "Supplier 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": "Supplier invoices",
"titleKey": "InvoiceNo"
},
{
"actions": [
{
"id": "5D6EB5CB-637A-5C90-A364-AE95F4C6EA98",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "5B68C550-4095-5820-ADC6-54A06F5D2D0D",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "90013FF2-0216-5E5E-B77A-90B21C512313",
"title": "Item dossier",
"urlKey": ""
}
],
"badgeKey": "ItemTypeName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Historical line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QuantityReceived",
"label": "Received — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QuantityInvoiced",
"label": "Invoiced — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ExtensionAmt",
"label": "Line amount — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "B2488603-E08D-56BA-9F27-D3651F361AF9",
"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": "JobNo",
"label": "Job reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CostCode",
"label": "Job cost code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CostType",
"label": "Native job cost type code",
"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": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ItemCodeDesc",
"label": "Item",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RequiredDate",
"label": "Required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorPriceCode",
"label": "Vendor price reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Historical 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": "POWarehouseCode",
"label": "Source PO 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": "VendorAliasItemNo",
"label": "Vendor item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SalesOrderNo",
"label": "Line sales order reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CustomerPONo",
"label": "Line customer PO reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AppliedPurchaseOrderNo",
"label": "Line applied purchase order",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "RetentionRate",
"label": "Native line retention rate",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "RetentionAmt",
"label": "Native retention — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrdered",
"label": "Native ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityReceived",
"label": "Native received — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityInvoiced",
"label": "Native invoiced — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitCost",
"label": "Historical invoice unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "OriginalUnitCost",
"label": "Original order unit cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "ExtensionAmt",
"label": "Native line amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrderedOriginal",
"label": "Original ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasureConvFactor",
"label": "Native line unit factor",
"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": "Item dossier availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "6E50B4BE-2645-57EB-BD38-E15CCC545572",
"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": "LineDisplayName"
},
{
"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": "StandardUnitCost",
"label": "Reference cost — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TotalQuantityOnHand",
"label": "On hand — stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "90013FF2-0216-5E5E-B77A-90B21C512313",
"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",
"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 date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastReceiptDate",
"label": "Last receipt date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitCost",
"label": "Standard cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitPrice",
"label": "Standard price — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "LastTotalUnitCost",
"label": "Last total cost — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"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 method",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "F23B02EE-0ADD-5FF6-AAA5-C31089348042",
"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": "VendorState",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TelephoneNo",
"label": "Telephone",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "EmailAddress",
"label": "Email",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BalanceDue",
"label": "Current balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "496EFFBA-34EF-5217-93D1-5B09EA3235C5",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorNo",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorName",
"label": "Current vendor name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine1",
"label": "Current address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine2",
"label": "Current address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AddressLine3",
"label": "Current address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "City",
"label": "Current city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "State",
"label": "Current state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ZipCode",
"label": "Current postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CountryCode",
"label": "Current country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PrimaryContact",
"label": "Primary contact 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": "TelephoneExt",
"label": "Telephone extension",
"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": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Reference",
"label": "Vendor reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "HoldPayment",
"label": "Payment selection hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Comment",
"label": "Note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastPurchaseDate",
"label": "Vendor last purchase date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastPaymentDate",
"label": "Vendor last payment date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateEstablished",
"label": "Vendor established date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AverageDaysToPay",
"label": "Stored average days to pay",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AverageDaysOverDue",
"label": "Stored average days overdue",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "BalanceDue",
"label": "Current vendor balance — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorState",
"label": "Vendor status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "57FB1BFC-8F0D-5ADD-9085-827C58F27FFB",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "BC515F3F-CAEC-53F1-9967-06042027411E",
"key": "VendorKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "VendorNo",
"systemImage": "doc.text",
"title": "Vendor dossier",
"titleKey": "VendorName"
}
],
"relations": [
{
"childDatasetID": "B2488603-E08D-56BA-9F27-D3651F361AF9",
"childKey": "InvoiceIdentity",
"id": "5656A03D-47B9-56DD-9A57-B0E3B250EBFA",
"name": "Invoice lines",
"parentDatasetID": "F041C99A-9DB1-5D3F-8D38-5ADE8D1D94CD",
"parentKey": "InvoiceIdentity"
},
{
"childDatasetID": "90013FF2-0216-5E5E-B77A-90B21C512313",
"childKey": "LineIdentity",
"id": "5B68C550-4095-5820-ADC6-54A06F5D2D0D",
"name": "Item dossier",
"parentDatasetID": "B2488603-E08D-56BA-9F27-D3651F361AF9",
"parentKey": "LineIdentity"
},
{
"childDatasetID": "496EFFBA-34EF-5217-93D1-5B09EA3235C5",
"childKey": "VendorKey",
"id": "084434C5-D6CE-50BA-A619-3C8664FBFC95",
"name": "Vendor dossier",
"parentDatasetID": "F041C99A-9DB1-5D3F-8D38-5ADE8D1D94CD",
"parentKey": "VendorKey"
}
],
"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 selected AP/PO invoice layouts, complete logical keys, functional help and synthetic validation. Verify installed schema, identities, units, currency, NULL, collation, permissions, date adapters and query cost at import. Not a real Sage SQL Server test.\n\nFor accounting, purchasing and managers: choose an invoice-date period and inspect retained AP/PO supplier invoices, due/discount dates, stored vendor and destination context, native amounts, retention and order/receipt references. Open own invoice lines, their available PO inventory item dossier and the separately identified current vendor.\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, four lists, three lazy buttons, parameterized read-only SELECT, at most 2,000 rows per request, no scheduled refresh. Both opening-period boundaries apply before the limit. Narrow the period on a limit hit. Local search/filters cover loaded rows only, not an authorization or query-cost boundary.\n\nAP/PO invoice history, not current payable settlement or a complete payment ledger. InvoiceNo plus HeaderSeqNo identifies each history header; lines also require DetailSeqNo. Vendor identity includes APDivisionNo plus VendorNo. Never deduplicate by invoice, vendor or item number alone. Explicit binary Unicode and byte-length comparisons preserve full strings; physical types, padding and collation must be tested on the actual installation. No AR-specific case annotation is claimed for AP.\n\nNative AP InvoiceType and source PurchaseOrderType codes stay untranslated: the selected AP layout does not certify their legend. AR IN/CM/DM/AD/FC/CA/XD meanings are not copied. No deleted-state, credit/debit sign normalization or unsupported classification is invented. Known item types use their own AP line documentation; unknown values stay unknown.\n\nInvoiceAmt, NetInvoiceAmt, discount, prepayment, retention, tax and other amounts are native components, not a calculated amount due or proof of payment. Source currency is not assumed USD. NULL is not replaced by zero. Native HoldPayment and prepaid references/dates are context, not settlement. Adjustment invoice payment review uses the original invoice; this dossier does not claim to provide the Payments tab or link to open items.\n\nTrack Detailed Invoice/Payment History, comment retention and purges affect availability. Missing lines do not prove no invoice, zero value or no payment. Multiple PO/prepayment flags are preserved; a single header PO and aggregate prepayment do not allocate values to each line. AP and PO fields can be empty or inapplicable by source module. Line comments provide a title when no item description is retained, without inventing an item. Only PO inventory lines with an existing master open an item dossier. Current item/vendor data differs from stored destinations and historical quantities/costs. Original PO quantity/cost, received quantity and invoiced quantity remain separate. Precise values are text with textual local filters/sorting, not silently rounded.\n\nNo fabricated G/L account number from internal AccountKey, banking/taxpayer/check/transfer data or audit user identifiers. No source credentials, server addresses or business rows in the package. Any later native gallery must use wholly fictional DEMO values. SQL Server 2012+/compatibility 110+ for TRY_CONVERT dates; invalid dates become NULL, not 1900. Public FLOR layouts are logical evidence, not installed DDL, indexes, types or performance. Verify dbo/company/module scope, full keys, dates, units, NULL, monetary meanings, schema compatibility and response time at import. Native operator security does not carry into direct SQL: use separately authorized least-privilege SELECT access. Unofficial, not endorsed by Sage; not France/Contractor/ProvideX.\n\nDictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/\nAP history: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAIN/INV_HIST_INQUIRY_OVR.htm\nAP fields: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAINFIELD/Invoice_History_Inquiry_-_Fields.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "28391C3E-B911-5B7E-BB67-2AFA18B96E1B",
"rootButtonCount": 1,
"summary": "Invoice-date period, native AP/PO context and precise lines; current item and vendor dossiers.",
"tags": [
"Sage 100 US",
"SQL Server",
"Supplier invoices",
"Accounting",
"Purchasing",
"Pro"
],
"title": "Supplier invoices and full dossiers — Pro"
}
}