Posted general ledger and account dossiers — Pro
Posting period, native debit/credit, complete context and current account/journal dossiers.
NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial Sage 100 US SQL Server configuration based on official 2026 FLOR Rel 7.50 full logical keys, own field notes, functional help and synthetic tests. Verify installed schema, exact dates/keys, padding, collation, currency/units, retention, least-privilege SELECT permissions and query performance at import. Native DEMO is not a real SQL Server test.
For accountants and managers: inspect retained posted general-ledger entries on the selected posting-date period, native debit/credit, document/journal/module/batch references and extended remarks. Open current account classifications, the current source-journal setup and separately scoped peer postings.
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 SQL source, four lists and three lazy dossier buttons. Required posting-date period at opening; separate inclusive start and exclusive end parameters. Read-only parameterized SELECT, at most 2,000 rows per request, no scheduled refresh. Local Search and Filters affect loaded rows, not the entire source. A limit hit may leave the list incomplete; validate query cost and source-side filters before use. No fictional grand total, account opening/closing balance or balanced-journal claim.
DebitAmount and CreditAmount are separate native values, not abs amounts or debit-plus-credit turnover. NULL is not zero. Financial and nonfinancial account/journal contexts differ: verify currency or operational units; no USD assumption and no mixing monetary and nonfinancial values. No historical fiscal balances, profit, budget variance or as-of classification is reconstructed from this bounded list.
Complete posting identity is AccountKey + original PostingDate + SourceJournal + JournalRegisterNo + SequenceNo. Technical account/document/row sequences stay hidden. Journal/register numbers may reset; the Same posting cohort button uses the exact original posting-date representation, source module, source journal and register number, not the number alone. It is a scoped retained peer-entry view, not proof of the entire legal journal, original subledger detail or transactional consistency.
Current GL account, main-account, group, category/type descriptions, statuses, date bounds, user-defined rollups and clearing/cash-flow settings are current masters, not metadata as of the posting date. Main-account lookup retains SegmentNo=01 documented in its own layout and MainAccountCode. Missing account/journal/classification masters do not delete retained postings; availability is explicit. Current offset account is a source-journal SETUP preference, not the counterpart of every shown entry.
Retained posted detail is not unposted General Journal work data, Source Journal History summaries or a complete original subledger. Retention, purges and summary posting affect availability. Native HeaderRec stays visible; no silent exclusion of header/summary/zero/negative entries. No reversal/void status is inferred from comments, signs, current inactive masters or a missing row. Own DocumentType I/C/R/S means Invoice/Check/Receipt/Summary; unknown codes remain unknown. Source journal codes and account-type default codes are not universal enums; actual descriptions come from the source.
SQL Server 2012+/compatibility 110+ required for defensive date conversion. Unsupported dates become NULL rather than 1900; the explicit opening period excludes unreadable posting dates, not certifies that no other rows exist. Fiscal periods need not be calendar months. Exact binary Unicode/byte-length identities and stable original-date conversion do not certify installed physical types, padding, collation, indexes or speed. Source changes across lazy reads are not an atomic snapshot.
No credentials, real server addresses, cached rows, bank keys, transfer identifiers or source-user audit fields in the package. Real document/line references and free remarks may contain confidential information; assess SQL access scope. DEMO screenshots use entirely fictional rows. Native ERP operator rights are not inherited by direct SQL: separately authorized least-privilege SELECT needed. Unofficial and not endorsed by Sage; not France, Contractor or ProvideX.
Official layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/General_Ledger/GL_DetailPosting.htm
Official functional help: https://help-sage100.na.sage.com/2026/Subsystems/GL/GLMainFields/Account_Maintenance_-_Fields.htm
Screenshots
What this package creates
1 Home3 Details4 lists1 sources to map
- Home: Posted GL entries
- Details: Account
- Details: Journal
- Details: Peer entries
- Sub-button: Current GL account dossier
- Sub-button: Current source journal dossier
- Sub-button: Same posting cohort
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 p.AccountKey, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, p.SourceJournal, p.JournalRegisterNo, p.SequenceNo, p.SourceModule, p.DocumentNo, p.DocSequenceNo, p.BatchType, p.BatchNo, p.PostingComment, p.HeaderRec, p.LineDocRefer, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) AS LineDate, p.DebitAmount, p.CreditAmount, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey)),N':',CONVERT(nvarchar(4000), p.AccountKey),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SequenceNo)),N':',CONVERT(nvarchar(4000), p.SequenceNo),N'|') AS PostingKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule)),N':',CONVERT(nvarchar(4000), p.SourceModule),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|') AS PostingCohortKey, CONVERT(nvarchar(4000), p.PostingDate, 126) AS RawPostingDate, CASE p.DocumentType WHEN N'I' THEN N'Invoice' WHEN N'C' THEN N'Check' WHEN N'R' THEN N'Receipt' WHEN N'S' THEN N'Summary' ELSE CONCAT(N'Unknown / unset: ', p.DocumentType) END AS DocumentKind, COALESCE(NULLIF(a.Account,N''),N'Account unavailable') AS FormattedAccount, a.AccountDesc AS CurrentAccountDesc, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS CurrentAccountState, j.SourceJournalDesc AS CurrentSourceJournalDesc, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS CurrentJournalType, CASE WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS AccountAvailability, CASE WHEN j.SourceJournal IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS JournalAvailability, CASE WHEN p.LineDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS LineDateReadState FROM dbo.GL_DetailPosting p LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), p.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) LEFT JOIN dbo.GL_SourceJournal j ON (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))) WHERE p.AccountKey IS NOT NULL AND p.PostingDate IS NOT NULL AND p.SourceJournal IS NOT NULL AND p.JournalRegisterNo IS NOT NULL AND p.SequenceNo IS NOT NULL AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) >= :date_from AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) < :date_until
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT a.AccountKey, a.AccountDesc, a.Account, a.RawAccount, a.MainAccountCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) ELSE NULL END,112) AS DateStart, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) ELSE NULL END,112) AS DateEnd, a.AccountType, a.RollupCode1, a.RollupCode2, a.RollupCode3, a.RollupCode4, a.AccountGroup, a.AccountCategory, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS AccountState, CASE a.ClearBalance WHEN N'Y' THEN N'Year end' WHEN N'N' THEN N'Never' ELSE CONCAT(N'Unknown / unset: ', a.ClearBalance) END AS ClearBalanceName, CASE a.CashFlowsType WHEN N'C' THEN N'Cash' WHEN N'F' THEN N'Financing' WHEN N'I' THEN N'Investment' WHEN N'N' THEN N'None' WHEN N'O' THEN N'Operations' ELSE CONCAT(N'Unknown / unset: ', a.CashFlowsType) END AS CashFlowName, m.MainAccountDesc, m.MainAccountShortDesc, t.AccountTypeDesc, g.AccountCategoryDesc, q.AccountGroupDesc, CONCAT(N'Main: ',CASE WHEN m.MainAccountCode IS NULL THEN N'missing' ELSE N'available' END,N'; type: ',CASE WHEN t.AccountType IS NULL THEN N'missing' ELSE N'available' END,N'; category: ',CASE WHEN g.AccountCategory IS NULL THEN N'missing' ELSE N'available' END,N'; group: ',CASE WHEN q.AccountGroup IS NULL THEN N'missing' ELSE N'available' END) AS LinkedMasterState, CASE WHEN a.DateStart IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DateStartReadState, CASE WHEN a.DateEnd IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DateEndReadState FROM dbo.GL_Account a LEFT JOIN dbo.GL_MainAccount m ON (CONVERT(nvarchar(4000), a.MainAccountCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.MainAccountCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.MainAccountCode))=DATALENGTH(CONVERT(nvarchar(4000), m.MainAccountCode))) AND (CONVERT(nvarchar(4000), m.SegmentNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :main_segment) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.SegmentNo))=DATALENGTH(CONVERT(nvarchar(4000), :main_segment))) LEFT JOIN dbo.GL_AccountType t ON (CONVERT(nvarchar(4000), a.AccountType) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), t.AccountType) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountType))=DATALENGTH(CONVERT(nvarchar(4000), t.AccountType))) LEFT JOIN dbo.GL_AccountCategory g ON (CONVERT(nvarchar(4000), a.AccountCategory) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), g.AccountCategory) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountCategory))=DATALENGTH(CONVERT(nvarchar(4000), g.AccountCategory))) LEFT JOIN dbo.GL_AccountGroup q ON (CONVERT(nvarchar(4000), a.AccountGroup) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), q.AccountGroup) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountGroup))=DATALENGTH(CONVERT(nvarchar(4000), q.AccountGroup))) WHERE (CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :account) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), :account)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.2.sqlQuerySELECT j.SourceJournal, j.SourceJournalDesc, j.OffsetAccountKey, j.EnterBatchTotForTransJrnlDE, j.PostBRDepositInSummary, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS JournalTypeName, CASE j.Offset WHEN N'D' THEN N'Debit' WHEN N'C' THEN N'Credit' ELSE CONCAT(N'Unknown / unset: ', j.Offset) END AS OffsetName, CASE j.TransactionType WHEN N'A' THEN N'Adjustment' WHEN N'B' THEN N'Bank transfer' WHEN N'C' THEN N'Check number' WHEN N'D' THEN N'Deposit' WHEN N'R' THEN N'document Reference' ELSE CONCAT(N'Unknown / unset: ', j.TransactionType) END AS TransactionTypeName, a.Account AS OffsetAccount, a.AccountDesc AS OffsetAccountDesc, CASE WHEN j.OffsetAccountKey IS NULL OR j.OffsetAccountKey=N'' THEN N'Not configured' WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS OffsetAccountAvailability FROM dbo.GL_SourceJournal j LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), j.OffsetAccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), j.OffsetAccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) WHERE (CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :journal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), :journal)))
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.3.sqlQuerySELECT p.AccountKey, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, p.SourceJournal, p.JournalRegisterNo, p.SequenceNo, p.SourceModule, p.DocumentNo, p.DocSequenceNo, p.BatchType, p.BatchNo, p.PostingComment, p.HeaderRec, p.LineDocRefer, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) AS LineDate, p.DebitAmount, p.CreditAmount, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey)),N':',CONVERT(nvarchar(4000), p.AccountKey),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SequenceNo)),N':',CONVERT(nvarchar(4000), p.SequenceNo),N'|') AS PostingKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule)),N':',CONVERT(nvarchar(4000), p.SourceModule),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|') AS PostingCohortKey, CONVERT(nvarchar(4000), p.PostingDate, 126) AS RawPostingDate, CASE p.DocumentType WHEN N'I' THEN N'Invoice' WHEN N'C' THEN N'Check' WHEN N'R' THEN N'Receipt' WHEN N'S' THEN N'Summary' ELSE CONCAT(N'Unknown / unset: ', p.DocumentType) END AS DocumentKind, COALESCE(NULLIF(a.Account,N''),N'Account unavailable') AS FormattedAccount, a.AccountDesc AS CurrentAccountDesc, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS CurrentAccountState, j.SourceJournalDesc AS CurrentSourceJournalDesc, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS CurrentJournalType, CASE WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS AccountAvailability, CASE WHEN j.SourceJournal IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS JournalAvailability, CASE WHEN p.LineDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS LineDateReadState FROM dbo.GL_DetailPosting p LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), p.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) LEFT JOIN dbo.GL_SourceJournal j ON (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))) WHERE p.AccountKey IS NOT NULL AND p.PostingDate IS NOT NULL AND p.SourceJournal IS NOT NULL AND p.JournalRegisterNo IS NOT NULL AND p.SequenceNo IS NOT NULL AND (CONVERT(nvarchar(4000), p.SourceModule) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :module) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule))=DATALENGTH(CONVERT(nvarchar(4000), :module))) AND (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :journal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), :journal))) AND (CONVERT(nvarchar(4000), p.JournalRegisterNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :register) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo))=DATALENGTH(CONVERT(nvarchar(4000), :register))) AND (CONVERT(nvarchar(4000), p.PostingDate, 126) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :raw_date) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126))=DATALENGTH(CONVERT(nvarchar(4000), :raw_date)))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Sage 100 US — SQL Server company database",
"id": "8113ED49-F492-5D1B-AE74-95231E816FE2",
"kind": "sqlServer",
"requiredObjects": [
"dbo.GL_Account",
"dbo.GL_AccountCategory",
"dbo.GL_AccountGroup",
"dbo.GL_AccountType",
"dbo.GL_DetailPosting",
"dbo.GL_MainAccount",
"dbo.GL_SourceJournal"
],
"requiresCustomSQL": true
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "812134CB-FF51-5295-821E-A69E0102937E",
"key": "PostingDate",
"type": "date"
},
{
"direction": "ascending",
"id": "AF6211CE-AA81-5D06-A0D9-8FFEFABDD891",
"key": "PostingKey",
"type": "text"
}
],
"id": "02989004-8A68-5159-9187-0D301B5A813B",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "AccountKey",
"label": "Internal account identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournal",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalRegisterNo",
"label": "Journal / register number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalRegisterNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SequenceNo",
"label": "Internal posting sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SequenceNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceModule",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DocumentNo",
"label": "Document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocumentNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DocSequenceNo",
"label": "Internal document sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocSequenceNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BatchType",
"label": "Batch type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BatchType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BatchNo",
"label": "Batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BatchNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingComment",
"label": "Native posting remark",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingComment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "HeaderRec",
"label": "Native header-record flag (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HeaderRec",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDocRefer",
"label": "Line document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDocRefer",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDate",
"label": "Native line date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DebitAmount",
"label": "Native debit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DebitAmount",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CreditAmount",
"label": "Native credit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditAmount",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PostingKey",
"label": "Internal full posting identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingCohortKey",
"label": "Internal exact posting cohort",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingCohortKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RawPostingDate",
"label": "Internal original posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RawPostingDate",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DocumentKind",
"label": "Native document type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocumentKind",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FormattedAccount",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FormattedAccount",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentAccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentAccountDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentAccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentAccountState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentSourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentSourceJournalDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CurrentJournalType",
"label": "Current journal financial context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentJournalType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalAvailability",
"label": "Current journal dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDateReadState",
"label": "Line date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDateReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Posted GL entries",
"primaryKey": "PostingKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PostingDate",
"id": "7781C556-3C85-5D9A-A765-1DA605D88FB8",
"name": "date_from",
"source": "openingPeriodStart",
"type": "date"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "PostingDate",
"id": "D42A8AED-3AE6-57BF-99B8-5128096D02C1",
"name": "date_until",
"source": "openingPeriodEndExclusive",
"type": "date"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"SourceJournal",
"JournalRegisterNo",
"SourceModule",
"DocumentNo",
"BatchType",
"BatchNo",
"PostingComment",
"HeaderRec",
"LineDocRefer",
"DocumentKind",
"FormattedAccount",
"CurrentAccountDesc",
"CurrentAccountState",
"CurrentSourceJournalDesc",
"CurrentJournalType",
"AccountAvailability",
"JournalAvailability",
"LineDateReadState"
],
"sourceID": "8113ED49-F492-5D1B-AE74-95231E816FE2",
"sqlQuery": "SELECT p.AccountKey, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, p.SourceJournal, p.JournalRegisterNo, p.SequenceNo, p.SourceModule, p.DocumentNo, p.DocSequenceNo, p.BatchType, p.BatchNo, p.PostingComment, p.HeaderRec, p.LineDocRefer, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) AS LineDate, p.DebitAmount, p.CreditAmount, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey)),N':',CONVERT(nvarchar(4000), p.AccountKey),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SequenceNo)),N':',CONVERT(nvarchar(4000), p.SequenceNo),N'|') AS PostingKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule)),N':',CONVERT(nvarchar(4000), p.SourceModule),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|') AS PostingCohortKey, CONVERT(nvarchar(4000), p.PostingDate, 126) AS RawPostingDate, CASE p.DocumentType WHEN N'I' THEN N'Invoice' WHEN N'C' THEN N'Check' WHEN N'R' THEN N'Receipt' WHEN N'S' THEN N'Summary' ELSE CONCAT(N'Unknown / unset: ', p.DocumentType) END AS DocumentKind, COALESCE(NULLIF(a.Account,N''),N'Account unavailable') AS FormattedAccount, a.AccountDesc AS CurrentAccountDesc, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS CurrentAccountState, j.SourceJournalDesc AS CurrentSourceJournalDesc, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS CurrentJournalType, CASE WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS AccountAvailability, CASE WHEN j.SourceJournal IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS JournalAvailability, CASE WHEN p.LineDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS LineDateReadState FROM dbo.GL_DetailPosting p LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), p.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) LEFT JOIN dbo.GL_SourceJournal j ON (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))) WHERE p.AccountKey IS NOT NULL AND p.PostingDate IS NOT NULL AND p.SourceJournal IS NOT NULL AND p.JournalRegisterNo IS NOT NULL AND p.SequenceNo IS NOT NULL AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) >= :date_from AND TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) < :date_until",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "D5B71B7F-A683-5FCE-93DE-AFE3EBA82A9B",
"key": "AccountKey",
"type": "text"
}
],
"id": "75358AFB-2AD4-5507-B437-2B824E7EDF94",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "AccountKey",
"label": "Internal account identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Account",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "Account",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RawAccount",
"label": "Internal unformatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RawAccount",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MainAccountCode",
"label": "Current main account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MainAccountCode",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DateStart",
"label": "Current posting start date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateStart",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateEnd",
"label": "Current posting end date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateEnd",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountType",
"label": "Current account type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RollupCode1",
"label": "Current user-defined rollup 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RollupCode1",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RollupCode2",
"label": "Current user-defined rollup 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RollupCode2",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RollupCode3",
"label": "Current user-defined rollup 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RollupCode3",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RollupCode4",
"label": "Current user-defined rollup 4",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RollupCode4",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountGroup",
"label": "Current account group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountGroup",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AccountCategory",
"label": "Current account category code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountCategory",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ClearBalanceName",
"label": "Native nonfinancial clearing rule",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "ClearBalanceName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CashFlowName",
"label": "Current cash-flow classification",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CashFlowName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MainAccountDesc",
"label": "Current main-account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MainAccountDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "MainAccountShortDesc",
"label": "Current main-account short label",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "MainAccountShortDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountTypeDesc",
"label": "Current account type description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountTypeDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AccountCategoryDesc",
"label": "Current account category description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountCategoryDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "AccountGroupDesc",
"label": "Current account group description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountGroupDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LinkedMasterState",
"label": "Linked current classification masters",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LinkedMasterState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateStartReadState",
"label": "Start date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateStartReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DateEndReadState",
"label": "End date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DateEndReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Current GL account dossier",
"primaryKey": "AccountKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "AccountKey",
"id": "9DD948A5-8C1D-5035-9BAA-113405100AEE",
"name": "account",
"source": "parentField",
"type": "text"
},
{
"constantValue": "01",
"dayOffset": 0,
"fieldKey": "",
"id": "5646D260-5E26-5E24-9848-5172CF172FCF",
"name": "main_segment",
"source": "constant",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"AccountDesc",
"Account",
"MainAccountCode",
"AccountType",
"RollupCode1",
"RollupCode2",
"RollupCode3",
"RollupCode4",
"AccountGroup",
"AccountCategory",
"AccountState",
"ClearBalanceName",
"CashFlowName",
"MainAccountDesc",
"MainAccountShortDesc",
"AccountTypeDesc",
"AccountCategoryDesc",
"AccountGroupDesc",
"LinkedMasterState",
"DateStartReadState",
"DateEndReadState"
],
"sourceID": "8113ED49-F492-5D1B-AE74-95231E816FE2",
"sqlQuery": "SELECT a.AccountKey, a.AccountDesc, a.Account, a.RawAccount, a.MainAccountCode, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) ELSE NULL END,112) AS DateStart, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) ELSE NULL END,112) AS DateEnd, a.AccountType, a.RollupCode1, a.RollupCode2, a.RollupCode3, a.RollupCode4, a.AccountGroup, a.AccountCategory, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS AccountState, CASE a.ClearBalance WHEN N'Y' THEN N'Year end' WHEN N'N' THEN N'Never' ELSE CONCAT(N'Unknown / unset: ', a.ClearBalance) END AS ClearBalanceName, CASE a.CashFlowsType WHEN N'C' THEN N'Cash' WHEN N'F' THEN N'Financing' WHEN N'I' THEN N'Investment' WHEN N'N' THEN N'None' WHEN N'O' THEN N'Operations' ELSE CONCAT(N'Unknown / unset: ', a.CashFlowsType) END AS CashFlowName, m.MainAccountDesc, m.MainAccountShortDesc, t.AccountTypeDesc, g.AccountCategoryDesc, q.AccountGroupDesc, CONCAT(N'Main: ',CASE WHEN m.MainAccountCode IS NULL THEN N'missing' ELSE N'available' END,N'; type: ',CASE WHEN t.AccountType IS NULL THEN N'missing' ELSE N'available' END,N'; category: ',CASE WHEN g.AccountCategory IS NULL THEN N'missing' ELSE N'available' END,N'; group: ',CASE WHEN q.AccountGroup IS NULL THEN N'missing' ELSE N'available' END) AS LinkedMasterState, CASE WHEN a.DateStart IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateStart, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DateStartReadState, CASE WHEN a.DateEnd IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), a.DateEnd, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS DateEndReadState FROM dbo.GL_Account a LEFT JOIN dbo.GL_MainAccount m ON (CONVERT(nvarchar(4000), a.MainAccountCode) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), m.MainAccountCode) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.MainAccountCode))=DATALENGTH(CONVERT(nvarchar(4000), m.MainAccountCode))) AND (CONVERT(nvarchar(4000), m.SegmentNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :main_segment) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), m.SegmentNo))=DATALENGTH(CONVERT(nvarchar(4000), :main_segment))) LEFT JOIN dbo.GL_AccountType t ON (CONVERT(nvarchar(4000), a.AccountType) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), t.AccountType) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountType))=DATALENGTH(CONVERT(nvarchar(4000), t.AccountType))) LEFT JOIN dbo.GL_AccountCategory g ON (CONVERT(nvarchar(4000), a.AccountCategory) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), g.AccountCategory) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountCategory))=DATALENGTH(CONVERT(nvarchar(4000), g.AccountCategory))) LEFT JOIN dbo.GL_AccountGroup q ON (CONVERT(nvarchar(4000), a.AccountGroup) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), q.AccountGroup) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountGroup))=DATALENGTH(CONVERT(nvarchar(4000), q.AccountGroup))) WHERE (CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :account) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), :account)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "DFF1294A-CA59-570E-AB25-386F32C4143C",
"key": "SourceJournal",
"type": "text"
}
],
"id": "78E29C4A-D2BD-5309-9D93-8B854FE1E912",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournal",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournalDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OffsetAccountKey",
"label": "Internal current offset-account key",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OffsetAccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "EnterBatchTotForTransJrnlDE",
"label": "Native transaction-journal batch total option (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "EnterBatchTotForTransJrnlDE",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostBRDepositInSummary",
"label": "Native bank-reconciliation summary option (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostBRDepositInSummary",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalTypeName",
"label": "Current financial / nonfinancial type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalTypeName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OffsetName",
"label": "Current offset orientation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OffsetName",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "TransactionTypeName",
"label": "Current transaction reference type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "TransactionTypeName",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OffsetAccount",
"label": "Current configured offset account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OffsetAccount",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "OffsetAccountDesc",
"label": "Current configured offset description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OffsetAccountDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OffsetAccountAvailability",
"label": "Current configured offset dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "OffsetAccountAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Current source journal dossier",
"primaryKey": "SourceJournal",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "SourceJournal",
"id": "8A350BF5-FE42-5806-A856-1A4BAA25B18D",
"name": "journal",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"SourceJournal",
"SourceJournalDesc",
"EnterBatchTotForTransJrnlDE",
"PostBRDepositInSummary",
"JournalTypeName",
"OffsetName",
"TransactionTypeName",
"OffsetAccount",
"OffsetAccountDesc",
"OffsetAccountAvailability"
],
"sourceID": "8113ED49-F492-5D1B-AE74-95231E816FE2",
"sqlQuery": "SELECT j.SourceJournal, j.SourceJournalDesc, j.OffsetAccountKey, j.EnterBatchTotForTransJrnlDE, j.PostBRDepositInSummary, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS JournalTypeName, CASE j.Offset WHEN N'D' THEN N'Debit' WHEN N'C' THEN N'Credit' ELSE CONCAT(N'Unknown / unset: ', j.Offset) END AS OffsetName, CASE j.TransactionType WHEN N'A' THEN N'Adjustment' WHEN N'B' THEN N'Bank transfer' WHEN N'C' THEN N'Check number' WHEN N'D' THEN N'Deposit' WHEN N'R' THEN N'document Reference' ELSE CONCAT(N'Unknown / unset: ', j.TransactionType) END AS TransactionTypeName, a.Account AS OffsetAccount, a.AccountDesc AS OffsetAccountDesc, CASE WHEN j.OffsetAccountKey IS NULL OR j.OffsetAccountKey=N'' THEN N'Not configured' WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS OffsetAccountAvailability FROM dbo.GL_SourceJournal j LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), j.OffsetAccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), j.OffsetAccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) WHERE (CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :journal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), :journal)))",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "Sage 300",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "812134CB-FF51-5295-821E-A69E0102937E",
"key": "PostingDate",
"type": "date"
},
{
"direction": "ascending",
"id": "AF6211CE-AA81-5D06-A0D9-8FFEFABDD891",
"key": "PostingKey",
"type": "text"
}
],
"id": "F2378D8E-E9F2-55A6-ABF1-F3AD6A27FA3A",
"integration": "Sage 100 US",
"mappings": [
{
"commonFieldKey": "",
"key": "AccountKey",
"label": "Internal account identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceJournal",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalRegisterNo",
"label": "Journal / register number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalRegisterNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SequenceNo",
"label": "Internal posting sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SequenceNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "SourceModule",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "DocumentNo",
"label": "Document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocumentNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DocSequenceNo",
"label": "Internal document sequence",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocSequenceNo",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BatchType",
"label": "Batch type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BatchType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BatchNo",
"label": "Batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "BatchNo",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingComment",
"label": "Native posting remark",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingComment",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "HeaderRec",
"label": "Native header-record flag (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "HeaderRec",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDocRefer",
"label": "Line document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDocRefer",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDate",
"label": "Native line date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DebitAmount",
"label": "Native debit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DebitAmount",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CreditAmount",
"label": "Native credit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CreditAmount",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PostingKey",
"label": "Internal full posting identity",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PostingCohortKey",
"label": "Internal exact posting cohort",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "PostingCohortKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RawPostingDate",
"label": "Internal original posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "RawPostingDate",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DocumentKind",
"label": "Native document type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "DocumentKind",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FormattedAccount",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "FormattedAccount",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentAccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentAccountDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentAccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentAccountState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CurrentSourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentSourceJournalDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CurrentJournalType",
"label": "Current journal financial context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "CurrentJournalType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "AccountAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JournalAvailability",
"label": "Current journal dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "JournalAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineDateReadState",
"label": "Line date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic",
"sourceColumn": "LineDateReadState",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 2000,
"name": "Same posting cohort",
"primaryKey": "PostingKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "SourceModule",
"id": "39F708CC-DA32-5984-8A5C-8F818554B5FF",
"name": "module",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "SourceJournal",
"id": "15D848B9-F9B4-50B1-B3C7-B12437931D82",
"name": "journal",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "JournalRegisterNo",
"id": "0742E320-1C76-5E0E-8C69-16A082D6FA90",
"name": "register",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "RawPostingDate",
"id": "E11C7D30-21CC-5C2D-BBF9-06C64903FD86",
"name": "raw_date",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"SourceJournal",
"JournalRegisterNo",
"SourceModule",
"DocumentNo",
"BatchType",
"BatchNo",
"PostingComment",
"HeaderRec",
"LineDocRefer",
"DocumentKind",
"FormattedAccount",
"CurrentAccountDesc",
"CurrentAccountState",
"CurrentSourceJournalDesc",
"CurrentJournalType",
"AccountAvailability",
"JournalAvailability",
"LineDateReadState"
],
"sourceID": "8113ED49-F492-5D1B-AE74-95231E816FE2",
"sqlQuery": "SELECT p.AccountKey, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.PostingDate, 112))),8) ELSE NULL END,112) AS PostingDate, p.SourceJournal, p.JournalRegisterNo, p.SequenceNo, p.SourceModule, p.DocumentNo, p.DocSequenceNo, p.BatchType, p.BatchNo, p.PostingComment, p.HeaderRec, p.LineDocRefer, TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) AS LineDate, p.DebitAmount, p.CreditAmount, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey)),N':',CONVERT(nvarchar(4000), p.AccountKey),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SequenceNo)),N':',CONVERT(nvarchar(4000), p.SequenceNo),N'|') AS PostingKey, CONCAT(DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule)),N':',CONVERT(nvarchar(4000), p.SourceModule),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal)),N':',CONVERT(nvarchar(4000), p.SourceJournal),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo)),N':',CONVERT(nvarchar(4000), p.JournalRegisterNo),N'|',DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126)),N':',CONVERT(nvarchar(4000), p.PostingDate, 126),N'|') AS PostingCohortKey, CONVERT(nvarchar(4000), p.PostingDate, 126) AS RawPostingDate, CASE p.DocumentType WHEN N'I' THEN N'Invoice' WHEN N'C' THEN N'Check' WHEN N'R' THEN N'Receipt' WHEN N'S' THEN N'Summary' ELSE CONCAT(N'Unknown / unset: ', p.DocumentType) END AS DocumentKind, COALESCE(NULLIF(a.Account,N''),N'Account unavailable') AS FormattedAccount, a.AccountDesc AS CurrentAccountDesc, CASE a.Status WHEN N'A' THEN N'Active' WHEN N'I' THEN N'Inactive' WHEN N'D' THEN N'Deleted' ELSE CONCAT(N'Unknown / unset: ', a.Status) END AS CurrentAccountState, j.SourceJournalDesc AS CurrentSourceJournalDesc, CASE j.JournalType WHEN N'F' THEN N'Financial' WHEN N'N' THEN N'Non-financial' ELSE CONCAT(N'Unknown / unset: ', j.JournalType) END AS CurrentJournalType, CASE WHEN a.AccountKey IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS AccountAvailability, CASE WHEN j.SourceJournal IS NULL THEN N'Missing: verify source integrity' ELSE N'Available' END AS JournalAvailability, CASE WHEN p.LineDate IS NULL THEN N'Missing source date' WHEN TRY_CONVERT(date, CASE WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))=8 AND LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) NOT LIKE '%[^0-9]%' THEN LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))) WHEN LEN(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))))>9 AND LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) NOT LIKE '%[^0-9]%' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),9,1)='.' AND SUBSTRING(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),10,31) NOT LIKE '%[^0]%' THEN LEFT(LTRIM(RTRIM(CONVERT(varchar(40), p.LineDate, 112))),8) ELSE NULL END,112) IS NULL THEN N'Unrecognized source date / convention' ELSE N'Readable source date' END AS LineDateReadState FROM dbo.GL_DetailPosting p LEFT JOIN dbo.GL_Account a ON (CONVERT(nvarchar(4000), p.AccountKey) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), a.AccountKey) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.AccountKey))=DATALENGTH(CONVERT(nvarchar(4000), a.AccountKey))) LEFT JOIN dbo.GL_SourceJournal j ON (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), j.SourceJournal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), j.SourceJournal))) WHERE p.AccountKey IS NOT NULL AND p.PostingDate IS NOT NULL AND p.SourceJournal IS NOT NULL AND p.JournalRegisterNo IS NOT NULL AND p.SequenceNo IS NOT NULL AND (CONVERT(nvarchar(4000), p.SourceModule) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :module) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceModule))=DATALENGTH(CONVERT(nvarchar(4000), :module))) AND (CONVERT(nvarchar(4000), p.SourceJournal) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :journal) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.SourceJournal))=DATALENGTH(CONVERT(nvarchar(4000), :journal))) AND (CONVERT(nvarchar(4000), p.JournalRegisterNo) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :register) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.JournalRegisterNo))=DATALENGTH(CONVERT(nvarchar(4000), :register))) AND (CONVERT(nvarchar(4000), p.PostingDate, 126) COLLATE Latin1_General_100_BIN2=CONVERT(nvarchar(4000), :raw_date) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONVERT(nvarchar(4000), p.PostingDate, 126))=DATALENGTH(CONVERT(nvarchar(4000), :raw_date)))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "6725215F-EDF1-5EA0-BBD1-6D746048BFB6",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "6870463C-2A61-5846-88C8-D75DC9AACCBB",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "75358AFB-2AD4-5507-B437-2B824E7EDF94",
"title": "Account",
"urlKey": ""
},
{
"id": "1A723C9A-0544-585C-9DD0-F3E46D4E0B81",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "34E2CE8E-747F-53B4-BB01-D3E4F3AF842B",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "78E29C4A-D2BD-5309-9D93-8B854FE1E912",
"title": "Journal",
"urlKey": ""
},
{
"id": "D8009C89-E6B1-51C6-B773-E7B77204FD7E",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "B2A183D0-A245-5016-8D85-781C839B1DB4",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "F2378D8E-E9F2-55A6-ABF1-F3AD6A27FA3A",
"title": "Peer entries",
"urlKey": ""
}
],
"badgeKey": "DocumentKind",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "JournalRegisterNo",
"label": "Journal / register",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DebitAmount",
"label": "Debit — native units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CreditAmount",
"label": "Credit — native units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrentSourceJournalDesc",
"label": "Current journal",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "02989004-8A68-5159-9187-0D301B5A813B",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalRegisterNo",
"label": "Journal / register number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DocumentNo",
"label": "Document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "BatchType",
"label": "Batch type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "BatchNo",
"label": "Batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingComment",
"label": "Native posting remark",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "HeaderRec",
"label": "Native header-record flag (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDocRefer",
"label": "Line document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDate",
"label": "Native line date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native posting values — verify units",
"detailRole": "information",
"isVisible": true,
"key": "DebitAmount",
"label": "Native debit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native posting values — verify units",
"detailRole": "information",
"isVisible": true,
"key": "CreditAmount",
"label": "Native credit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DocumentKind",
"label": "Native document type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "FormattedAccount",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentAccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentAccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentSourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentJournalType",
"label": "Current journal financial context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalAvailability",
"label": "Current journal dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDateReadState",
"label": "Line date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "0A5FE908-FBAF-5B95-993D-27DCD9045114",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "880A3C79-94B5-5279-9AA1-0ECBFB44801F",
"includeAllOption": false,
"key": "PostingDate",
"title": "Posting date period",
"type": "date"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "descending",
"id": "812134CB-FF51-5295-821E-A69E0102937E",
"key": "PostingDate",
"type": "date"
},
{
"direction": "ascending",
"id": "AF6211CE-AA81-5D06-A0D9-8FFEFABDD891",
"key": "PostingKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "DocumentNo",
"systemImage": "doc.text",
"title": "Posted GL entries",
"titleKey": "FormattedAccount"
},
{
"actions": [],
"badgeKey": "AccountState",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "MainAccountCode",
"label": "Current main account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AccountGroup",
"label": "Current account group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AccountTypeDesc",
"label": "Current account type description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AccountCategoryDesc",
"label": "Current account category description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "75358AFB-2AD4-5507-B437-2B824E7EDF94",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "Account",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MainAccountCode",
"label": "Current main account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateStart",
"label": "Current posting start date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateEnd",
"label": "Current posting end date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountType",
"label": "Current account type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RollupCode1",
"label": "Current user-defined rollup 1",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RollupCode2",
"label": "Current user-defined rollup 2",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RollupCode3",
"label": "Current user-defined rollup 3",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "RollupCode4",
"label": "Current user-defined rollup 4",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountGroup",
"label": "Current account group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountCategory",
"label": "Current account category code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "ClearBalanceName",
"label": "Native nonfinancial clearing rule",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CashFlowName",
"label": "Current cash-flow classification",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MainAccountDesc",
"label": "Current main-account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "MainAccountShortDesc",
"label": "Current main-account short label",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountTypeDesc",
"label": "Current account type description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountCategoryDesc",
"label": "Current account category description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountGroupDesc",
"label": "Current account group description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LinkedMasterState",
"label": "Linked current classification masters",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateStartReadState",
"label": "Start date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DateEndReadState",
"label": "End date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "5F386D2B-5734-5422-8C36-B666FB78D901",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "D5B71B7F-A683-5FCE-93DE-AFE3EBA82A9B",
"key": "AccountKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "AccountDesc",
"systemImage": "doc.text",
"title": "Current GL account dossier",
"titleKey": "Account"
},
{
"actions": [],
"badgeKey": "JournalTypeName",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OffsetName",
"label": "Current offset orientation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "OffsetAccount",
"label": "Current configured offset account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "78E29C4A-D2BD-5309-9D93-8B854FE1E912",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "EnterBatchTotForTransJrnlDE",
"label": "Native transaction-journal batch total option (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostBRDepositInSummary",
"label": "Native bank-reconciliation summary option (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalTypeName",
"label": "Current financial / nonfinancial type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OffsetName",
"label": "Current offset orientation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "TransactionTypeName",
"label": "Current transaction reference type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OffsetAccount",
"label": "Current configured offset account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OffsetAccountDesc",
"label": "Current configured offset description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "OffsetAccountAvailability",
"label": "Current configured offset dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "55760F87-0601-5929-8F47-0D364DD781BB",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "DFF1294A-CA59-570E-AB25-386F32C4143C",
"key": "SourceJournal",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "SourceJournal",
"systemImage": "doc.text",
"title": "Current source journal dossier",
"titleKey": "SourceJournalDesc"
},
{
"actions": [],
"badgeKey": "DocumentKind",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "JournalRegisterNo",
"label": "Journal / register",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DebitAmount",
"label": "Debit — native units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CreditAmount",
"label": "Credit — native units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CurrentSourceJournalDesc",
"label": "Current journal",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"datasetID": "F2378D8E-E9F2-55A6-ABF1-F3AD6A27FA3A",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingDate",
"label": "Posting date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceJournal",
"label": "Source journal code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalRegisterNo",
"label": "Journal / register number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "SourceModule",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DocumentNo",
"label": "Document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "BatchType",
"label": "Batch type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "BatchNo",
"label": "Batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "PostingComment",
"label": "Native posting remark",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "HeaderRec",
"label": "Native header-record flag (Y/N)",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDocRefer",
"label": "Line document reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDate",
"label": "Native line date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native posting values — verify units",
"detailRole": "information",
"isVisible": true,
"key": "DebitAmount",
"label": "Native debit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Native posting values — verify units",
"detailRole": "information",
"isVisible": true,
"key": "CreditAmount",
"label": "Native credit value — verify units",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "DocumentKind",
"label": "Native document type",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "FormattedAccount",
"label": "Current formatted account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentAccountDesc",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentAccountState",
"label": "Current account status",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentSourceJournalDesc",
"label": "Current source journal description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "CurrentJournalType",
"label": "Current journal financial context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "AccountAvailability",
"label": "Current account dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "JournalAvailability",
"label": "Current journal dossier",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
},
{
"detailGroup": "Business context",
"detailRole": "information",
"isVisible": true,
"key": "LineDateReadState",
"label": "Line date interpretation",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "automatic"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "8325C7CD-94E7-5F40-A616-F292A3FB8234",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "descending",
"id": "812134CB-FF51-5295-821E-A69E0102937E",
"key": "PostingDate",
"type": "date"
},
{
"direction": "ascending",
"id": "AF6211CE-AA81-5D06-A0D9-8FFEFABDD891",
"key": "PostingKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "DocumentNo",
"systemImage": "doc.text",
"title": "Same posting cohort",
"titleKey": "FormattedAccount"
}
],
"relations": [
{
"childDatasetID": "75358AFB-2AD4-5507-B437-2B824E7EDF94",
"childKey": "AccountKey",
"id": "6870463C-2A61-5846-88C8-D75DC9AACCBB",
"name": "Account",
"parentDatasetID": "02989004-8A68-5159-9187-0D301B5A813B",
"parentKey": "AccountKey"
},
{
"childDatasetID": "78E29C4A-D2BD-5309-9D93-8B854FE1E912",
"childKey": "SourceJournal",
"id": "34E2CE8E-747F-53B4-BB01-D3E4F3AF842B",
"name": "Journal",
"parentDatasetID": "02989004-8A68-5159-9187-0D301B5A813B",
"parentKey": "SourceJournal"
},
{
"childDatasetID": "F2378D8E-E9F2-55A6-ABF1-F3AD6A27FA3A",
"childKey": "PostingCohortKey",
"id": "B2A183D0-A245-5016-8D85-781C839B1DB4",
"name": "Peer entries",
"parentDatasetID": "02989004-8A68-5159-9187-0D301B5A813B",
"parentKey": "PostingCohortKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Sage 100 US — SQL Server",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-09T00:00:00Z",
"description": "NOT VALIDATED ON A REAL ERP INSTALLATION. Unofficial Sage 100 US SQL Server configuration based on official 2026 FLOR Rel 7.50 full logical keys, own field notes, functional help and synthetic tests. Verify installed schema, exact dates/keys, padding, collation, currency/units, retention, least-privilege SELECT permissions and query performance at import. Native DEMO is not a real SQL Server test.\n\nFor accountants and managers: inspect retained posted general-ledger entries on the selected posting-date period, native debit/credit, document/journal/module/batch references and extended remarks. Open current account classifications, the current source-journal setup and separately scoped peer postings.\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 SQL source, four lists and three lazy dossier buttons. Required posting-date period at opening; separate inclusive start and exclusive end parameters. Read-only parameterized SELECT, at most 2,000 rows per request, no scheduled refresh. Local Search and Filters affect loaded rows, not the entire source. A limit hit may leave the list incomplete; validate query cost and source-side filters before use. No fictional grand total, account opening/closing balance or balanced-journal claim.\n\nDebitAmount and CreditAmount are separate native values, not abs amounts or debit-plus-credit turnover. NULL is not zero. Financial and nonfinancial account/journal contexts differ: verify currency or operational units; no USD assumption and no mixing monetary and nonfinancial values. No historical fiscal balances, profit, budget variance or as-of classification is reconstructed from this bounded list.\n\nComplete posting identity is AccountKey + original PostingDate + SourceJournal + JournalRegisterNo + SequenceNo. Technical account/document/row sequences stay hidden. Journal/register numbers may reset; the Same posting cohort button uses the exact original posting-date representation, source module, source journal and register number, not the number alone. It is a scoped retained peer-entry view, not proof of the entire legal journal, original subledger detail or transactional consistency.\n\nCurrent GL account, main-account, group, category/type descriptions, statuses, date bounds, user-defined rollups and clearing/cash-flow settings are current masters, not metadata as of the posting date. Main-account lookup retains SegmentNo=01 documented in its own layout and MainAccountCode. Missing account/journal/classification masters do not delete retained postings; availability is explicit. Current offset account is a source-journal SETUP preference, not the counterpart of every shown entry.\n\nRetained posted detail is not unposted General Journal work data, Source Journal History summaries or a complete original subledger. Retention, purges and summary posting affect availability. Native HeaderRec stays visible; no silent exclusion of header/summary/zero/negative entries. No reversal/void status is inferred from comments, signs, current inactive masters or a missing row. Own DocumentType I/C/R/S means Invoice/Check/Receipt/Summary; unknown codes remain unknown. Source journal codes and account-type default codes are not universal enums; actual descriptions come from the source.\n\nSQL Server 2012+/compatibility 110+ required for defensive date conversion. Unsupported dates become NULL rather than 1900; the explicit opening period excludes unreadable posting dates, not certifies that no other rows exist. Fiscal periods need not be calendar months. Exact binary Unicode/byte-length identities and stable original-date conversion do not certify installed physical types, padding, collation, indexes or speed. Source changes across lazy reads are not an atomic snapshot.\n\nNo credentials, real server addresses, cached rows, bank keys, transfer identifiers or source-user audit fields in the package. Real document/line references and free remarks may contain confidential information; assess SQL access scope. DEMO screenshots use entirely fictional rows. Native ERP operator rights are not inherited by direct SQL: separately authorized least-privilege SELECT needed. Unofficial and not endorsed by Sage; not France, Contractor or ProvideX.\n\nOfficial layout: https://help-sage100.na.sage.com/2026/FLOR/Content/File_Layouts/General_Ledger/GL_DetailPosting.htm\nOfficial functional help: https://help-sage100.na.sage.com/2026/Subsystems/GL/GLMainFields/Account_Maintenance_-_Fields.htm",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "747F8C4B-4F46-53D8-9752-775900D41347",
"rootButtonCount": 1,
"summary": "Posting period, native debit/credit, complete context and current account/journal dossiers.",
"tags": [
"Sage 100 US",
"SQL Server",
"Accounting",
"General ledger",
"Pro"
],
"title": "Posted general ledger and account dossiers — Pro"
}
}