Purchase orders and full dossiers — Pro
Purchase period, required dates and destinations; precise lines, item and current vendor dossiers.
NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial configuration based on Sage 100 US 2026 FLOR Rel 7.50 selected fields/complete documented keys, functional help, synthetic logic tests and native Cifru DEMO captures. Run the read and compatibility test at import; verify schema, units, source currency, dates, permissions and performance before business use.
For purchasing teams, receiving staff, finance and managers: choose an order-date period, inspect standard and drop-ship orders, delivery destinations, required dates, shipping method, requester, department, order status and native monetary context. Open all order lines, including services and notes, with precise quantities, purchase units, current/original unit cost and the vendor item reference. Regular inventory lines open an item dossier; a separate button opens the full current vendor dossier, contacts, payment-selection hold, stored current balance and activity dates.
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 company source, four lists and three separate lazy related buttons. All reads are parameterized SELECT, limited to 2,000 rows per request, on demand with no scheduled refresh. Root filtering uses both period boundaries before the row limit; children retain the exact parent order/line or full AP division + vendor identity. Repeated item codes on different lines stay separate. A limit hit does not mean complete data; search and local filters only cover loaded rows. Narrow the period and verify source response times; limits are not a query-cost guarantee.
Only Standard S and Drop Ship D orders are included. Master, repeating, material requisition and RFQ documents are excluded because their quantity/date meanings differ. Retained received/completed/held orders remain visible; purged history is not recovered. Change C is not Closed, Received R is not invoiced or paid, and order OnHold is distinct from vendor HoldPayment. Unknown statuses remain unknown. Drop ship is not stock physically received into your warehouse.
QuantityReceived is received to date for S/D; QuantityInvoiced is the native quantity invoiced through Receipt of Invoice Entry. Ordered, backordered and invoiced values are not recomputed from each other. UnitOfMeasure is the purchase line unit, not automatically the stocking unit. Native conversion factor, six-decimal quantities and unit costs remain precise text; local filtering/sorting of these values is textual, not numeric. Original unit cost is the original order value, not the current item master cost. Native line extension and raw received/invoiced amounts are not recalculated; no assertion of AP posting, paid/unpaid, reconciliation, landed cost or remaining balance. No calculated order total, invoice total, available stock or aging. NULL amounts remain NULL, not zero. Verify each company/source currency and extensions; no USD or other ISO currency is assumed.
Order purchase/delivery addresses are stored order values. The vendor dossier is current master information, not historical contact/address/balance as of the opening period. Primary contact is a native code, not an invented name. Vendor HoldPayment affects automatic invoice payment selection; Sage can explicitly select excluded invoices elsewhere. Displaying it does not change payment controls. Item prices/costs are current reference/master values, not vendor quotations or the original order costs. The item dossier opens only for regular inventory lines with an existing master; special, charge, comment, miscellaneous and missing items remain in the order-line list without invented item records.
Target: Sage 100 US 2026 SQL Server company database (formerly Premium), PO/AP/IM modules, dbo to verify, SQL Server 2012+ and compatibility 110+ for defensive TRY_CONVERT date handling. Native dates or YYYYMMDD with all-zero decimal suffix are supported; unsupported/corrupt dates become NULL, never a fabricated 1900 date. FLOR M/L and TUR are not physical SQL types, nullability or installed unique constraints. Verify column owners, full keys, collation, permissions, customizations, source currency, results and query timing at import. Direct SQL does not inherit Sage operator permissions. Use a dedicated least-privilege SELECT login; filters are not ACL. No bank/taxpayer/payment identifiers, encrypted fields, internal audit users, source credentials, connection addresses or production data are included. Gallery uses completely fictional DEMO data in real Cifru screens, not a Sage database connection test. Not Sage 100 France, Contractor or ProvideX. Unofficial and not endorsed by Sage.
Dictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/
Purchase semantics: https://help-sage100.na.sage.com/2026/Subsystems/PO/POMainFields/Purchase_Order_Entry_-Fields.htm
Vendor semantics: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAINFIELD/Vendor_Maintenance_-_Fields.htm
Screenshots
What this package creates
1 Home3 Details4 lists1 sources to map
- Home: Purchase orders
- Details: Purchase order lines
- Details: Item dossier
- Details: Vendor dossier
- Sub-button: Purchase order 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.PurchaseOrderNo, 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, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),8) ELSE NULL END,112) AS RequiredExpireDate, h.APDivisionNo, h.VendorNo, h.PurchaseName, h.PurchaseAddress1, h.PurchaseAddress2, h.PurchaseAddress3, h.PurchaseCity, h.PurchaseState, h.PurchaseZipCode, h.PurchaseCountryCode, h.ShipToName, h.ShipToAddress1, h.ShipToAddress2, h.ShipToAddress3, h.ShipToCity, h.ShipToState, h.ShipToZipCode, h.ShipToCountryCode, h.OnHold, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),8) ELSE NULL END,112) AS CompletionDate, h.ShipVia, h.WarehouseCode, h.Comment, h.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),8) ELSE NULL END,112) AS LastInvoiceDate, h.LastInvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),8) ELSE NULL END,112) AS LastReceiptDate, h.RequisitorName, h.RequisitorDepartment, h.PrepaidAmt, h.TaxableAmt, h.NonTaxableAmt, h.SalesTaxAmt, h.FreightAmt, h.InvoicedAmt, h.ReceivedAmt, COALESCE(NULLIF(h.PurchaseName,N''),NULLIF(v.VendorName,N''),h.VendorNo) AS PartyName, CONCAT(DATALENGTH(h.APDivisionNo),N':',h.APDivisionNo,h.VendorNo) AS VendorKey, CASE h.OrderType WHEN N'S' THEN N'Standard' WHEN N'D' THEN N'Direct delivery' WHEN N'M' THEN N'Master' WHEN N'R' THEN N'Repeating' WHEN N'X' THEN N'Material Requisition' WHEN N'Q' THEN N'Request for Quote' ELSE CONCAT(N'Unknown / unset: ', h.OrderType) END AS OrderTypeName, CASE h.OrderStatus WHEN N'N' THEN N'New' WHEN N'O' THEN N'Open' WHEN N'B' THEN N'Backordered' WHEN N'C' THEN N'Change' WHEN N'R' THEN N'Received' WHEN N'X' THEN N'complete' ELSE CONCAT(N'Unknown / unset: ', h.OrderStatus) END AS OrderState FROM dbo.PO_PurchaseOrderHeader h LEFT JOIN dbo.AP_Vendor v ON v.APDivisionNo=h.APDivisionNo AND v.VendorNo=h.VendorNo WHERE h.OrderType IN (N'S',N'D') AND 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)>=:date_from AND 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)<:date_until
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT l.PurchaseOrderNo, l.LineKey, l.LineSeqNo, 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.UnitOfMeasure, l.WarehouseCode, l.VendorAliasItemNo, l.CommentText, l.QuantityOrdered, l.QuantityReceived, l.QuantityBackordered, l.QuantityInvoiced, l.UnitCost, l.OriginalUnitCost, l.ExtensionAmt, l.ReceivedAmt, l.InvoicedAmt, l.UnitOfMeasureConvFactor, CONCAT(DATALENGTH(l.PurchaseOrderNo),N':',l.PurchaseOrderNo,l.LineKey) AS LineIdentity, CASE l.ItemType WHEN N'1' THEN N'Regular 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 WHEN l.ItemType<>N'1' THEN N'Not a regular inventory line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.PO_PurchaseOrderDetail l INNER JOIN dbo.PO_PurchaseOrderHeader h ON h.PurchaseOrderNo=l.PurchaseOrderNo LEFT JOIN dbo.CI_Item i ON l.ItemType=N'1' AND i.ItemCode=l.ItemCode WHERE h.OrderType IN (N'S',N'D') AND l.PurchaseOrderNo=:order_no
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(l.PurchaseOrderNo),N':',l.PurchaseOrderNo,l.LineKey) 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.PO_PurchaseOrderDetail l INNER JOIN dbo.PO_PurchaseOrderHeader h ON h.PurchaseOrderNo=l.PurchaseOrderNo INNER JOIN dbo.CI_Item i ON l.ItemType=N'1' AND i.ItemCode=l.ItemCode WHERE h.OrderType IN (N'S',N'D') AND l.PurchaseOrderNo=:order_no AND l.LineKey=:line_key
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(v.APDivisionNo),N':',v.APDivisionNo,v.VendorNo) 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 v.APDivisionNo=:division AND v.VendorNo=:vendor
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 100 US — SQL Server company database",
"id": "7F5307CF-DB4F-55F3-B7B4-E2A3B7A8EB89",
"kind": "sqlServer",
"requiredObjects": [
"dbo.AP_Vendor",
"dbo.CI_Item",
"dbo.PO_PurchaseOrderDetail",
"dbo.PO_PurchaseOrderHeader"
],
"requiresCustomSQL": true
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "C49BBE10-DC40-564F-AAED-92DB14FDE6CA",
"key": "PurchaseOrderDate",
"type": "date"
},
{
"direction": "ascending",
"id": "85E3C205-63FB-5910-89A8-80B214728552",
"key": "PurchaseOrderNo",
"type": "text"
}
],
"id": "7F6704B5-AED5-573F-AE2A-9277E634D4FB",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "PurchaseOrderNo",
"label": "Purchase order number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseOrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "RequiredExpireDate",
"label": "Required delivery date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequiredExpireDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "APDivisionNo",
"label": "Internal vendor division",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "APDivisionNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "VendorNo",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseName",
"label": "Order purchase name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress1",
"label": "Order purchase address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress2",
"label": "Order purchase address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseAddress3",
"label": "Order purchase address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseAddress3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseCity",
"label": "Order purchase city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseCity",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseState",
"label": "Order purchase state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseZipCode",
"label": "Order purchase postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseZipCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PurchaseCountryCode",
"label": "Order purchase country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseCountryCode",
"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": true
},
{
"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": "OnHold",
"label": "Order on hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OnHold",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CompletionDate",
"label": "Completion date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CompletionDate",
"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": true
},
{
"commonFieldKey": "",
"key": "Comment",
"label": "Note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Comment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TermsCode",
"label": "Payment terms code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TermsCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastInvoiceDate",
"label": "Last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastInvoiceDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LastInvoiceNo",
"label": "Last invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LastInvoiceNo",
"type": "text",
"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": "RequisitorName",
"label": "Requested by",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequisitorName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RequisitorDepartment",
"label": "Requesting department",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequisitorDepartment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PrepaidAmt",
"label": "Native prepaid amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PrepaidAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaxableAmt",
"label": "Taxable merchandise — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "NonTaxableAmt",
"label": "Nontaxable merchandise — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "NonTaxableAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SalesTaxAmt",
"label": "Sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SalesTaxAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FreightAmt",
"label": "Freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FreightAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoicedAmt",
"label": "Invoiced amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoicedAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ReceivedAmt",
"label": "Received amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReceivedAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PartyName",
"label": "Purchase name / vendor",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PartyName",
"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": "OrderTypeName",
"label": "Order type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OrderTypeName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OrderState",
"label": "Order status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OrderState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Purchase orders",
"primaryKey": "PurchaseOrderNo",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PurchaseOrderDate",
"id": "2D053E15-AB49-503C-8FEC-F4B675CEC45C",
"name": "date_from",
"source": "openingPeriodStart",
"type": "date"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PurchaseOrderDate",
"id": "2C589B64-5F98-5C9E-A73D-8C7B5B15B145",
"name": "date_until",
"source": "openingPeriodEndExclusive",
"type": "date"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"PurchaseOrderNo",
"VendorNo",
"PurchaseName",
"PurchaseAddress1",
"PurchaseAddress2",
"PurchaseAddress3",
"PurchaseCity",
"PurchaseState",
"PurchaseZipCode",
"PurchaseCountryCode",
"ShipToName",
"ShipToAddress1",
"ShipToAddress2",
"ShipToAddress3",
"ShipToCity",
"ShipToState",
"ShipToZipCode",
"ShipToCountryCode",
"OnHold",
"ShipVia",
"WarehouseCode",
"Comment",
"TermsCode",
"LastInvoiceNo",
"RequisitorName",
"RequisitorDepartment",
"OrderTypeName",
"OrderState"
],
"sourceID": "7F5307CF-DB4F-55F3-B7B4-E2A3B7A8EB89",
"sqlQuery": "SELECT h.PurchaseOrderNo, 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, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.RequiredExpireDate, 112))),8) ELSE NULL END,112) AS RequiredExpireDate, h.APDivisionNo, h.VendorNo, h.PurchaseName, h.PurchaseAddress1, h.PurchaseAddress2, h.PurchaseAddress3, h.PurchaseCity, h.PurchaseState, h.PurchaseZipCode, h.PurchaseCountryCode, h.ShipToName, h.ShipToAddress1, h.ShipToAddress2, h.ShipToAddress3, h.ShipToCity, h.ShipToState, h.ShipToZipCode, h.ShipToCountryCode, h.OnHold, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.CompletionDate, 112))),8) ELSE NULL END,112) AS CompletionDate, h.ShipVia, h.WarehouseCode, h.Comment, h.TermsCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastInvoiceDate, 112))),8) ELSE NULL END,112) AS LastInvoiceDate, h.LastInvoiceNo, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), h.LastReceiptDate, 112))),8) ELSE NULL END,112) AS LastReceiptDate, h.RequisitorName, h.RequisitorDepartment, h.PrepaidAmt, h.TaxableAmt, h.NonTaxableAmt, h.SalesTaxAmt, h.FreightAmt, h.InvoicedAmt, h.ReceivedAmt, COALESCE(NULLIF(h.PurchaseName,N''),NULLIF(v.VendorName,N''),h.VendorNo) AS PartyName, CONCAT(DATALENGTH(h.APDivisionNo),N':',h.APDivisionNo,h.VendorNo) AS VendorKey, CASE h.OrderType WHEN N'S' THEN N'Standard' WHEN N'D' THEN N'Direct delivery' WHEN N'M' THEN N'Master' WHEN N'R' THEN N'Repeating' WHEN N'X' THEN N'Material Requisition' WHEN N'Q' THEN N'Request for Quote' ELSE CONCAT(N'Unknown / unset: ', h.OrderType) END AS OrderTypeName, CASE h.OrderStatus WHEN N'N' THEN N'New' WHEN N'O' THEN N'Open' WHEN N'B' THEN N'Backordered' WHEN N'C' THEN N'Change' WHEN N'R' THEN N'Received' WHEN N'X' THEN N'complete' ELSE CONCAT(N'Unknown / unset: ', h.OrderStatus) END AS OrderState FROM dbo.PO_PurchaseOrderHeader h LEFT JOIN dbo.AP_Vendor v ON v.APDivisionNo=h.APDivisionNo AND v.VendorNo=h.VendorNo WHERE h.OrderType IN (N'S',N'D') AND 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)>=:date_from AND 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)<:date_until",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "3A0EDC00-081B-5F18-8D99-9CAF0BC87A18",
"key": "LineSeqNo",
"type": "text"
},
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"id": "59887A5D-BAF6-59BA-BB39-CC6C3C6DFF95",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "PurchaseOrderNo",
"label": "Purchase order number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PurchaseOrderNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineKey",
"label": "Internal line key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineSeqNo",
"label": "Internal line sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineSeqNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCode",
"label": "Item code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ItemCodeDesc",
"label": "Item",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ItemCodeDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RequiredDate",
"label": "Line required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RequiredDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnitOfMeasure",
"label": "Purchase 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": "VendorAliasItemNo",
"label": "Vendor item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "VendorAliasItemNo",
"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": "QuantityOrdered",
"label": "Ordered — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityOrdered",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityReceived",
"label": "Received to date — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityReceived",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "QuantityBackordered",
"label": "Backordered — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityBackordered",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "QuantityInvoiced",
"label": "Invoiced — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "QuantityInvoiced",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "UnitCost",
"label": "Native 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": "ReceivedAmt",
"label": "Received amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ReceivedAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "InvoicedAmt",
"label": "Invoiced amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "InvoicedAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitOfMeasureConvFactor",
"label": "Stock units per purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "UnitOfMeasureConvFactor",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineIdentity",
"label": "Internal full order-line identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineIdentity",
"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": "ProductAvailability",
"label": "Item dossier availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ProductAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Purchase order lines",
"primaryKey": "LineIdentity",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PurchaseOrderNo",
"id": "A0DEFCA0-5996-5E17-A142-C46562FC414C",
"name": "order_no",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"PurchaseOrderNo",
"ItemCode",
"ItemCodeDesc",
"UnitOfMeasure",
"WarehouseCode",
"VendorAliasItemNo",
"CommentText",
"QuantityOrdered",
"QuantityReceived",
"QuantityBackordered",
"QuantityInvoiced",
"UnitCost",
"OriginalUnitCost",
"UnitOfMeasureConvFactor",
"ItemTypeName",
"ProductAvailability"
],
"sourceID": "7F5307CF-DB4F-55F3-B7B4-E2A3B7A8EB89",
"sqlQuery": "SELECT l.PurchaseOrderNo, l.LineKey, l.LineSeqNo, 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.UnitOfMeasure, l.WarehouseCode, l.VendorAliasItemNo, l.CommentText, l.QuantityOrdered, l.QuantityReceived, l.QuantityBackordered, l.QuantityInvoiced, l.UnitCost, l.OriginalUnitCost, l.ExtensionAmt, l.ReceivedAmt, l.InvoicedAmt, l.UnitOfMeasureConvFactor, CONCAT(DATALENGTH(l.PurchaseOrderNo),N':',l.PurchaseOrderNo,l.LineKey) AS LineIdentity, CASE l.ItemType WHEN N'1' THEN N'Regular 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 WHEN l.ItemType<>N'1' THEN N'Not a regular inventory line' WHEN i.ItemCode IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS ProductAvailability FROM dbo.PO_PurchaseOrderDetail l INNER JOIN dbo.PO_PurchaseOrderHeader h ON h.PurchaseOrderNo=l.PurchaseOrderNo LEFT JOIN dbo.CI_Item i ON l.ItemType=N'1' AND i.ItemCode=l.ItemCode WHERE h.OrderType IN (N'S',N'D') AND l.PurchaseOrderNo=:order_no",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"id": "AF0A86AC-5734-515B-87B5-7B3EC726FA56",
"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 order-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": "PurchaseOrderNo",
"id": "A0DEFCA0-5996-5E17-A142-C46562FC414C",
"name": "order_no",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "LineKey",
"id": "A4232749-A7DF-5B4B-98A3-9868FD80E9A0",
"name": "line_key",
"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": "7F5307CF-DB4F-55F3-B7B4-E2A3B7A8EB89",
"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(l.PurchaseOrderNo),N':',l.PurchaseOrderNo,l.LineKey) 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.PO_PurchaseOrderDetail l INNER JOIN dbo.PO_PurchaseOrderHeader h ON h.PurchaseOrderNo=l.PurchaseOrderNo INNER JOIN dbo.CI_Item i ON l.ItemType=N'1' AND i.ItemCode=l.ItemCode WHERE h.OrderType IN (N'S',N'D') AND l.PurchaseOrderNo=:order_no AND l.LineKey=:line_key",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "BC515F3F-CAEC-53F1-9967-06042027411E",
"key": "VendorKey",
"type": "text"
}
],
"id": "EE2DA463-A59D-5B2C-9652-EC621CBBC388",
"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": "66617D67-8C9B-51F9-B241-B425B51BF695",
"name": "division",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "VendorNo",
"id": "D2845187-23BE-5C6E-BFB9-CDABF828FF84",
"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": "7F5307CF-DB4F-55F3-B7B4-E2A3B7A8EB89",
"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(v.APDivisionNo),N':',v.APDivisionNo,v.VendorNo) 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 v.APDivisionNo=:division AND v.VendorNo=:vendor",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "838ADAE6-6635-5D35-A84E-82C229535981",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "507D79A0-8787-5DD4-80F7-8B8A27B403A6",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "59887A5D-BAF6-59BA-BB39-CC6C3C6DFF95",
"title": "Purchase order lines",
"urlKey": ""
},
{
"id": "CE052E05-24DD-5BAB-9D18-ACFC77FBC7F4",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "D1621A33-2DA3-5055-9417-971098540E11",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "EE2DA463-A59D-5B2C-9652-EC621CBBC388",
"title": "Vendor dossier",
"urlKey": ""
}
],
"badgeKey": "OrderState",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseOrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "RequiredExpireDate",
"label": "Required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCity",
"label": "Delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OnHold",
"label": "Order on hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "WarehouseCode",
"label": "Warehouse code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OrderTypeName",
"label": "Order type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "7F6704B5-AED5-573F-AE2A-9277E634D4FB",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"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": "PurchaseOrderDate",
"label": "Order date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RequiredExpireDate",
"label": "Required delivery date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "VendorNo",
"label": "Vendor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseName",
"label": "Order purchase name",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress1",
"label": "Order purchase address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress2",
"label": "Order purchase address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseAddress3",
"label": "Order purchase address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseCity",
"label": "Order purchase city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseState",
"label": "Order purchase state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseZipCode",
"label": "Order purchase postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Order purchase address",
"detailRole": "information",
"isVisible": true,
"key": "PurchaseCountryCode",
"label": "Order purchase country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToName",
"label": "Deliver to",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress1",
"label": "Delivery address",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress2",
"label": "Delivery address 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToAddress3",
"label": "Delivery address 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCity",
"label": "Delivery city",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToState",
"label": "Delivery state",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToZipCode",
"label": "Delivery postal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Delivery destination",
"detailRole": "information",
"isVisible": true,
"key": "ShipToCountryCode",
"label": "Delivery country code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OnHold",
"label": "Order on hold (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CompletionDate",
"label": "Completion 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": "Comment",
"label": "Note",
"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": "LastInvoiceDate",
"label": "Last invoice date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastInvoiceNo",
"label": "Last invoice reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LastReceiptDate",
"label": "Last receipt date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RequisitorName",
"label": "Requested by",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RequisitorDepartment",
"label": "Requesting department",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "PrepaidAmt",
"label": "Native prepaid amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "TaxableAmt",
"label": "Taxable merchandise — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "NonTaxableAmt",
"label": "Nontaxable merchandise — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "SalesTaxAmt",
"label": "Sales tax — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "FreightAmt",
"label": "Freight — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "InvoicedAmt",
"label": "Invoiced amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "ReceivedAmt",
"label": "Received amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OrderTypeName",
"label": "Order type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OrderState",
"label": "Order status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"icon": "cart",
"id": "29C68F41-F744-5A93-AF4F-CA8167B68196",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "AB761951-BBDB-584A-98F6-1EEC08843289",
"includeAllOption": false,
"key": "PurchaseOrderDate",
"title": "Purchase order period",
"type": "date"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "descending",
"id": "C49BBE10-DC40-564F-AAED-92DB14FDE6CA",
"key": "PurchaseOrderDate",
"type": "date"
},
{
"direction": "ascending",
"id": "85E3C205-63FB-5910-89A8-80B214728552",
"key": "PurchaseOrderNo",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "PurchaseOrderNo",
"systemImage": "doc.text",
"title": "Purchase orders",
"titleKey": "PartyName"
},
{
"actions": [
{
"id": "4978EE26-06DB-5834-A3E5-FEAF50D6DF3A",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "065A3924-B7F0-530B-BC03-E9C39DFAFD01",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "AF0A86AC-5734-515B-87B5-7B3EC726FA56",
"title": "Item dossier",
"urlKey": ""
}
],
"badgeKey": "ItemTypeName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "RequiredDate",
"label": "Line required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrdered",
"label": "Ordered — line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "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": "59887A5D-BAF6-59BA-BB39-CC6C3C6DFF95",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"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": "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": "Line required date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasure",
"label": "Purchase 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": "VendorAliasItemNo",
"label": "Vendor item reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CommentText",
"label": "Line note",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityOrdered",
"label": "Ordered — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityReceived",
"label": "Received to date — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityBackordered",
"label": "Backordered — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "QuantityInvoiced",
"label": "Invoiced — purchase line unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitCost",
"label": "Native 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": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "ExtensionAmt",
"label": "Native line amount — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "ReceivedAmt",
"label": "Received amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Values — source currency",
"detailRole": "information",
"isVisible": true,
"key": "InvoicedAmt",
"label": "Invoiced amount (native) — source currency",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Quantities, costs and units",
"detailRole": "information",
"isVisible": true,
"key": "UnitOfMeasureConvFactor",
"label": "Stock units per purchase line unit",
"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": "ProductAvailability",
"label": "Item dossier availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"icon": "doc.text",
"id": "EDDB6423-DEA3-553A-892D-D36790D9C48C",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "3A0EDC00-081B-5F18-8D99-9CAF0BC87A18",
"key": "LineSeqNo",
"type": "text"
},
{
"direction": "ascending",
"id": "A4475508-CEF0-5218-876C-9A7357D5E6E1",
"key": "LineIdentity",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "ItemCode",
"systemImage": "doc.text",
"title": "Purchase order lines",
"titleKey": "ItemCodeDesc"
},
{
"actions": [],
"badgeKey": "ItemState",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitOfMeasure",
"label": "Stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ProductLine",
"label": "Product line",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "StandardUnitCost",
"label": "Standard cost — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TotalQuantityOnHand",
"label": "On hand — stock unit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "AF0A86AC-5734-515B-87B5-7B3EC726FA56",
"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": [],
"icon": "doc.text",
"id": "8C1BB3E7-61FF-588C-BB36-BA256D68C63C",
"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": "Balance — source",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "EE2DA463-A59D-5B2C-9652-EC621CBBC388",
"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": "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": [],
"icon": "doc.text",
"id": "CDE0C071-06E2-5754-894C-F92DBF828B3D",
"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": "59887A5D-BAF6-59BA-BB39-CC6C3C6DFF95",
"childKey": "PurchaseOrderNo",
"id": "507D79A0-8787-5DD4-80F7-8B8A27B403A6",
"name": "Purchase order lines",
"parentDatasetID": "7F6704B5-AED5-573F-AE2A-9277E634D4FB",
"parentKey": "PurchaseOrderNo"
},
{
"childDatasetID": "AF0A86AC-5734-515B-87B5-7B3EC726FA56",
"childKey": "LineIdentity",
"id": "065A3924-B7F0-530B-BC03-E9C39DFAFD01",
"name": "Item dossier",
"parentDatasetID": "59887A5D-BAF6-59BA-BB39-CC6C3C6DFF95",
"parentKey": "LineIdentity"
},
{
"childDatasetID": "EE2DA463-A59D-5B2C-9652-EC621CBBC388",
"childKey": "VendorKey",
"id": "D1621A33-2DA3-5055-9417-971098540E11",
"name": "Vendor dossier",
"parentDatasetID": "7F6704B5-AED5-573F-AE2A-9277E634D4FB",
"parentKey": "VendorKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Sage 100 US — SQL Server",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-08T00:00:00Z",
"description": "NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial configuration based on Sage 100 US 2026 FLOR Rel 7.50 selected fields/complete documented keys, functional help, synthetic logic tests and native Cifru DEMO captures. Run the read and compatibility test at import; verify schema, units, source currency, dates, permissions and performance before business use.\n\nFor purchasing teams, receiving staff, finance and managers: choose an order-date period, inspect standard and drop-ship orders, delivery destinations, required dates, shipping method, requester, department, order status and native monetary context. Open all order lines, including services and notes, with precise quantities, purchase units, current/original unit cost and the vendor item reference. Regular inventory lines open an item dossier; a separate button opens the full current vendor dossier, contacts, payment-selection hold, stored current balance and activity dates.\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 company source, four lists and three separate lazy related buttons. All reads are parameterized SELECT, limited to 2,000 rows per request, on demand with no scheduled refresh. Root filtering uses both period boundaries before the row limit; children retain the exact parent order/line or full AP division + vendor identity. Repeated item codes on different lines stay separate. A limit hit does not mean complete data; search and local filters only cover loaded rows. Narrow the period and verify source response times; limits are not a query-cost guarantee.\n\nOnly Standard S and Drop Ship D orders are included. Master, repeating, material requisition and RFQ documents are excluded because their quantity/date meanings differ. Retained received/completed/held orders remain visible; purged history is not recovered. Change C is not Closed, Received R is not invoiced or paid, and order OnHold is distinct from vendor HoldPayment. Unknown statuses remain unknown. Drop ship is not stock physically received into your warehouse.\n\nQuantityReceived is received to date for S/D; QuantityInvoiced is the native quantity invoiced through Receipt of Invoice Entry. Ordered, backordered and invoiced values are not recomputed from each other. UnitOfMeasure is the purchase line unit, not automatically the stocking unit. Native conversion factor, six-decimal quantities and unit costs remain precise text; local filtering/sorting of these values is textual, not numeric. Original unit cost is the original order value, not the current item master cost. Native line extension and raw received/invoiced amounts are not recalculated; no assertion of AP posting, paid/unpaid, reconciliation, landed cost or remaining balance. No calculated order total, invoice total, available stock or aging. NULL amounts remain NULL, not zero. Verify each company/source currency and extensions; no USD or other ISO currency is assumed.\n\nOrder purchase/delivery addresses are stored order values. The vendor dossier is current master information, not historical contact/address/balance as of the opening period. Primary contact is a native code, not an invented name. Vendor HoldPayment affects automatic invoice payment selection; Sage can explicitly select excluded invoices elsewhere. Displaying it does not change payment controls. Item prices/costs are current reference/master values, not vendor quotations or the original order costs. The item dossier opens only for regular inventory lines with an existing master; special, charge, comment, miscellaneous and missing items remain in the order-line list without invented item records.\n\nTarget: Sage 100 US 2026 SQL Server company database (formerly Premium), PO/AP/IM modules, dbo to verify, SQL Server 2012+ and compatibility 110+ for defensive TRY_CONVERT date handling. Native dates or YYYYMMDD with all-zero decimal suffix are supported; unsupported/corrupt dates become NULL, never a fabricated 1900 date. FLOR M/L and TUR are not physical SQL types, nullability or installed unique constraints. Verify column owners, full keys, collation, permissions, customizations, source currency, results and query timing at import. Direct SQL does not inherit Sage operator permissions. Use a dedicated least-privilege SELECT login; filters are not ACL. No bank/taxpayer/payment identifiers, encrypted fields, internal audit users, source credentials, connection addresses or production data are included. Gallery uses completely fictional DEMO data in real Cifru screens, not a Sage database connection test. Not Sage 100 France, Contractor or ProvideX. Unofficial and not endorsed by Sage.\n\nDictionary: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/\nPurchase semantics: https://help-sage100.na.sage.com/2026/Subsystems/PO/POMainFields/Purchase_Order_Entry_-Fields.htm\nVendor semantics: https://help-sage100.na.sage.com/2026/Subsystems/AP/APMAINFIELD/Vendor_Maintenance_-_Fields.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "91FC6A31-3E03-597B-8166-925E242DF40C",
"rootButtonCount": 1,
"summary": "Purchase period, required dates and destinations; precise lines, item and current vendor dossiers.",
"tags": [
"Sage 100 US",
"SQL Server",
"Purchase orders",
"Procurement",
"Vendors",
"Receiving",
"Pro"
],
"title": "Purchase orders and full dossiers — Pro"
}
}