Microsoft Dynamics GP

Posted GL distributions, current account and segments — Pro

Period-filtered posted distribution dossiers, independent currency contexts and lazy current account/segment details.

UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. For accountants, controllers and managers: choose a transaction-date period, then inspect posted GL distributions from open fiscal years. See the journal reference, complete source timestamp, independent debit/credit values and original source references. Open the current account dossier and every nonblank account segment on demand.

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 authorised SQL company source, one Home, three explicit read-only lists and two nested lazy buttons. Required inclusive start/exclusive end dates and optional journal, current account and source currency filters precede the 1,000-row root cap; each child has its own cap. No scheduled refresh, SUM, netting, currency conversion, writes, credentials, business rows, SELECT * or executable code is exported. Filters/caps do not guarantee completeness or cheap source scans.

Own Microsoft GL20000 source registration and AL staging fields are pinned to e7ed235bc979c0283306e9639ff7cd22ce78341d. Own GP Support documentation identifies DEBITAMT/CRDTAMNT as functional values and ORDBTAMT/ORCRDAMT as originating values in its documented GL20000 context. This is not installed SQL DDL, physical PK/FK, installed monetary storage, timezone, ledger partition, current edition, SQL Server execution or real ERP validation. Verify installed objects, types, identities, authorised permissions and interpretations with the import read test and known distributions.

GL20000 is Open-year posted distributions, not Work drafts, GL30000 closed-year history, all fiscal years, RM/PM positions, a complete journal, accounting balance or bank statement. Fiscal years need not match calendar years. Journal/year/sequence is not a certified full journal key. NULL timestamps are excluded by the required date period. SERIES remains its original code; source master/document references do not imply customer, vendor, invoice or a verified foreign key.

The four native values retain precise source text, NULL, zero, tiny amounts, negatives and simultaneous debit/credit without netting, rounding or repairs. Local value sorting is textual, not numeric aggregation. Functional currency requires exactly one nonblank row in the WHOLE MC40000 setting, never a filtered unique-looking row or CURNCYID fallback. Originating context uses the distribution own CURNCYID, never the current partner default. No ISO currency or exchange rate is inferred. Revaluation distributions may have only functional values; they are not automatically corrupt.

Only a unique current Posting account (code 1) receives monetary currency contexts. Unit accounts (code 2) hold nonfinancial quantities, NOT money; their stored CURNCYID is not a unit measure. Allocation (3), unknown, missing or ambiguous current account types leave monetary contexts empty with a reason, while preserving all source values. Current account classification is not a certified historical journal classification.

Missing/ambiguous current account metadata preserves the distribution, without first-match, deduplication or fanout. Complete account numbers require one GL00105 record with all eight segments agreeing NULL-aware; no guessed separator or truncation. Internal DEX_ROW_ID, ACTINDX and route/context keys remain hidden. NULL/duplicate distribution identity blocks the selected-period root read, counting duplicates across the whole source. Each child revalidates the original distribution, every selected source field (including the full timestamp and values), current account/formatting context and whole company currency state. Changed/removed/ambiguous parents block stale routes until reread. The nested segment route also revalidates its original GL parent, not just the account.

Current account/segment labels and stored settings are not journal snapshots, inherited GP permissions or effective posting eligibility. Optional metadata absence is explicit. Binary Unicode comparisons preserve full codes, leading spaces/case/zeroes and remove only right padding; they do not certify installed collation or constraints.

Use a separately authorised least-privilege SELECT reporting login to allowed company data, not sa/sysadmin, DYNGRP, DYNAMICS or BC staging. GP application passwords are transformed. Cifru filters are not access control. Accounting/source references can be confidential. No maintenance instructions from support articles are executed. Native screenshots use entirely fictional DEMO rows and synthetic transport, not live GP or SQL Server. Unofficial; not endorsed by Microsoft.

Primary GL20000 source: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPGL20000.Table.al
Own currency roles: https://community.dynamics.com/blogs/post/?postid=162eb501-091c-42b6-a13e-ad88eee63d82
Distribution anomalies: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/financial-report-do-not-match-gl-trial-balance-report
Unit account binding: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/clear-beginning-balances-for-unit-accounts-in-general-ledger
Current chart / segments: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPGL00100.Table.al
Whole company currency setting: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPMC40000.Table.al

Screenshots

What this package creates

1 Home2 Details3 lists1 sources to map
  • Home: Posted GL distributions
  • Details: Current account
  • Details: Account segments
  • Sub-button: Current distribution account
  • Sub-button: Distribution account segments

Sources are mapped locally and verified before applying.

Custom queriesPRO3 SQL

Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.

Statically verified read-only$.components.workspaceSelection.datasets.0.sqlQuery
WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL) SELECT g.DistributionKey AS DistributionKey,g.LedgerContextToken AS LedgerContextToken,g.CurrentAccountKey AS CurrentAccountKey,g.CurrentAccountContext AS CurrentAccountContext,g.TRXDATE AS TRXDATE,g.JRNENTRY AS JRNENTRY,g.SourceTimestamp AS SourceTimestamp,g.OPENYEAR AS OPENYEAR,g.SEQNUMBR AS SEQNUMBR,g.REFRENCE AS REFRENCE,g.DSCRIPTN AS DSCRIPTN,g.AccountNumber AS AccountNumber,g.CurrentAccountDescription AS CurrentAccountDescription,g.AccountState AS AccountState,g.NumberState AS NumberState,g.AccountTypeName AS AccountTypeName,g.ValueKind AS ValueKind,g.FunctionalCurrencyContext AS FunctionalCurrencyContext,g.FunctionalCurrencyState AS FunctionalCurrencyState,g.CURNCYID AS CURNCYID,g.OriginatingCurrencyContext AS OriginatingCurrencyContext,g.OriginatingCurrencyState AS OriginatingCurrencyState,g.DEBITAMT AS DEBITAMT,g.CRDTAMNT AS CRDTAMNT,g.ORDBTAMT AS ORDBTAMT,g.ORCRDAMT AS ORCRDAMT,g.SOURCDOC AS SOURCDOC,g.SERIES AS SERIES,g.TRXSORCE AS TRXSORCE,g.ORMSTRID AS ORMSTRID,g.ORMSTRNM AS ORMSTRNM,g.ORDOCNUM AS ORDOCNUM,g.ORTRXSRC AS ORTRXSRC,g.User_Defined_Text01 AS User_Defined_Text01,g.User_Defined_Text02 AS User_Defined_Text02,g.Scope AS Scope FROM ledger g WHERE NOT EXISTS (SELECT 1 FROM dbo.GL20000 bad WHERE bad.TRXDATE>=:date_from AND bad.TRXDATE<:date_until AND NOT (bad.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=bad.DEX_ROW_ID)=1)) AND g.TRXDATE>=:date_from AND g.TRXDATE<:date_until

static read-only checks passed

Statically verified read-only$.components.workspaceSelection.datasets.1.sqlQuery
WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL), ledger_parent AS (SELECT g.DistributionKey,g.LedgerContextToken,g.CurrentAccountKey,g.CurrentAccountContext FROM ledger g WHERE (RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:distribution_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:distribution_ref AS nvarchar(4000))))) AND (RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:ledger_context AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:ledger_context AS nvarchar(4000))))) AND EXISTS (SELECT 1 FROM dbo.GL20000 src WHERE src.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=src.DEX_ROW_ID)=1 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000))))))) SELECT a.AccountKey AS AccountKey,a.ParentContextToken AS ParentContextToken,a.AccountNumber AS AccountNumber,a.ACTDESCR AS ACTDESCR,a.NumberState AS NumberState,a.AccountTypeName AS AccountTypeName,a.ActiveContext AS ActiveContext,a.EntryContext AS EntryContext,a.PostingContext AS PostingContext,a.BalanceSideContext AS BalanceSideContext,a.MNACSGMT AS MNACSGMT,a.ACCTTYPE AS ACCTTYPE,a.ACCATNUM AS ACCATNUM,a.ACTIVE AS ACTIVE,a.ACCTENTR AS ACCTENTR,a.PSTNGTYP AS PSTNGTYP,a.TPCLBLNC AS TPCLBLNC,a.BALFRCLC AS BALFRCLC,a.Clear_Balance AS Clear_Balance,a.Scope AS Scope,p.DistributionKey AS ParentDistributionKey,p.LedgerContextToken AS OriginalLedgerContext FROM chart a JOIN ledger_parent p ON (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL

static read-only checks passed

Statically verified read-only$.components.workspaceSelection.datasets.2.sqlQuery
WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL), ledger_parent AS (SELECT g.DistributionKey,g.LedgerContextToken,g.CurrentAccountKey,g.CurrentAccountContext FROM ledger g WHERE (RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:distribution_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:distribution_ref AS nvarchar(4000))))) AND (RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:ledger_context AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:ledger_context AS nvarchar(4000))))) AND EXISTS (SELECT 1 FROM dbo.GL20000 src WHERE src.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=src.DEX_ROW_ID)=1 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000))))))), segment_parent AS (SELECT a.SourceAccountIndex,a.AccountKey,a.ParentContextToken,a.AccountNumber,a.NumberState,a.ACTNUMBR_1,a.ACTNUMBR_2,a.ACTNUMBR_3,a.ACTNUMBR_4,a.ACTNUMBR_5,a.ACTNUMBR_6,a.ACTNUMBR_7,a.ACTNUMBR_8 FROM chart a JOIN ledger_parent p ON (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))))) WHERE (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:account_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:account_ref AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000)))))), segment AS (SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,1 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_1 AS SegmentCode,p.ACTNUMBR_1 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_1 IS NOT NULL AND RTRIM(p.ACTNUMBR_1)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,2 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_2 AS SegmentCode,p.ACTNUMBR_2 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_2 IS NOT NULL AND RTRIM(p.ACTNUMBR_2)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,3 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_3 AS SegmentCode,p.ACTNUMBR_3 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_3 IS NOT NULL AND RTRIM(p.ACTNUMBR_3)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,4 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_4 AS SegmentCode,p.ACTNUMBR_4 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_4 IS NOT NULL AND RTRIM(p.ACTNUMBR_4)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,5 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_5 AS SegmentCode,p.ACTNUMBR_5 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_5 IS NOT NULL AND RTRIM(p.ACTNUMBR_5)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,6 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_6 AS SegmentCode,p.ACTNUMBR_6 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_6 IS NOT NULL AND RTRIM(p.ACTNUMBR_6)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,7 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_7 AS SegmentCode,p.ACTNUMBR_7 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_7 IS NOT NULL AND RTRIM(p.ACTNUMBR_7)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,8 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_8 AS SegmentCode,p.ACTNUMBR_8 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_8 IS NOT NULL AND RTRIM(p.ACTNUMBR_8)<>N'') SELECT CONCAT(LEN(RTRIM(CAST(segment.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(segment.SourceAccountIndex AS nvarchar(100))),LEN(RTRIM(CAST(segment.SegmentNumber AS nvarchar(100)))),N':',RTRIM(CAST(segment.SegmentNumber AS nvarchar(100)))) AS SegmentKey,segment.AccountKey AS ParentAccountKey,segment.ParentContextToken AS ParentContextToken,:distribution_ref AS ParentDistributionKey,:ledger_context AS OriginalLedgerContext,segment.AccountNumber AS ParentAccountNumber,segment.SegmentNumber AS SegmentNumber,RTRIM(segment.SegmentCode) AS SegmentCode,CASE WHEN segment.NumberState=N'Unique stored number; all eight segments agree' THEN RTRIM(segment.FormattedSegmentCode) END AS FormattedSegmentCode,(SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.SGMTNAME) END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) AS SegmentName,(SELECT CASE WHEN COUNT(1)=1 THEN MAX(d.DSCRIPTN) END FROM dbo.GL40200 d WHERE d.SGMTNUMB=segment.SegmentNumber AND (RTRIM(CAST(d.SGMNTID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(segment.SegmentCode AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(d.SGMNTID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(segment.SegmentCode AS nvarchar(4000)))))) AS SegmentDescription,CASE (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.MNSEGIND) END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) WHEN 0 THEN N'Not main segment' WHEN 1 THEN N'Main segment' ELSE N'Unknown / unavailable main-segment setting' END AS MainSegmentContext,(SELECT CASE WHEN COUNT(1)=0 THEN N'Missing current metadata' WHEN COUNT(1)<>1 THEN N'Ambiguous current metadata' WHEN MAX(s.SGMTNAME) IS NULL THEN N'Unique record; label is NULL' WHEN RTRIM(MAX(s.SGMTNAME))=N'' THEN N'Unique record; label is blank' ELSE N'Unique current metadata' END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) AS SettingState,(SELECT CASE WHEN COUNT(1)=0 THEN N'Missing current metadata' WHEN COUNT(1)<>1 THEN N'Ambiguous current metadata' WHEN MAX(d.DSCRIPTN) IS NULL THEN N'Unique record; label is NULL' WHEN RTRIM(MAX(d.DSCRIPTN))=N'' THEN N'Unique record; label is blank' ELSE N'Unique current metadata' END FROM dbo.GL40200 d WHERE d.SGMTNUMB=segment.SegmentNumber AND (RTRIM(CAST(d.SGMNTID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(segment.SegmentCode AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(d.SGMNTID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(segment.SegmentCode AS nvarchar(4000)))))) AS DescriptionState,N'Current stored nonblank segment; no assumed department, profit centre or category role' AS SegmentScope FROM segment

static read-only checks passed

View the JSON being importedcollapsed by default

The exact portable content of this version.

{
    "components": {
        "sourceSlots": [
            {
                "displayName": "Dynamics GP — authorised SQL company database",
                "id": "E47EE20F-48B3-5D3C-AF73-124C7C04AFF5",
                "kind": "sqlServer",
                "requiredObjects": [
                    "dbo.GL20000",
                    "dbo.GL00100",
                    "dbo.GL00105",
                    "dbo.GL40200",
                    "dbo.SY00300",
                    "dbo.MC40000"
                ],
                "requiresCustomSQL": true
            }
        ],
        "workspaceSelection": {
            "commonFields": [],
            "datasets": [
                {
                    "cacheMode": "live",
                    "calculatedFields": [],
                    "customQueryIntegrationName": "",
                    "endpointPath": "",
                    "fetchSortRules": [
                        {
                            "direction": "descending",
                            "id": "B8EA2DA0-D967-5494-B8C1-6E26BCF11A9E",
                            "key": "TRXDATE",
                            "type": "date"
                        },
                        {
                            "direction": "ascending",
                            "id": "BECF2A88-E500-581E-9CC9-E72B46CB1A46",
                            "key": "JRNENTRY",
                            "type": "number"
                        },
                        {
                            "direction": "ascending",
                            "id": "4F458A2C-358B-5577-87CF-9C95B536418D",
                            "key": "DistributionKey",
                            "type": "text"
                        }
                    ],
                    "id": "87679410-CC5B-5A5B-BE76-92D773FFA01D",
                    "mappings": [
                        {
                            "commonFieldKey": "",
                            "key": "DistributionKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "DistributionKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "LedgerContextToken",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "LedgerContextToken",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "CurrentAccountKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "CurrentAccountKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "CurrentAccountContext",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "CurrentAccountContext",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "TRXDATE",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "TRXDATE",
                            "type": "date",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "JRNENTRY",
                            "label": "Stored journal number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "JRNENTRY",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SourceTimestamp",
                            "label": "Complete source transaction timestamp",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SourceTimestamp",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "OPENYEAR",
                            "label": "Stored open fiscal year",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "OPENYEAR",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SEQNUMBR",
                            "label": "Stored distribution sequence",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SEQNUMBR",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "REFRENCE",
                            "label": "Stored reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "REFRENCE",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "DSCRIPTN",
                            "label": "Stored distribution description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "DSCRIPTN",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "AccountNumber",
                            "label": "Current complete account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountNumber",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "CurrentAccountDescription",
                            "label": "Current account description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "CurrentAccountDescription",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "AccountState",
                            "label": "Current account availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "NumberState",
                            "label": "Current formatted-number availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "NumberState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "AccountTypeName",
                            "label": "Current documented account type",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountTypeName",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ValueKind",
                            "label": "Value interpretation — current account context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ValueKind",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "FunctionalCurrencyContext",
                            "label": "Functional monetary currency context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "FunctionalCurrencyContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "FunctionalCurrencyState",
                            "label": "Whole company functional-currency setting availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "FunctionalCurrencyState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "CURNCYID",
                            "label": "Stored originating currency ID (not a unit measure)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "CURNCYID",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "OriginatingCurrencyContext",
                            "label": "Originating monetary currency context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "OriginatingCurrencyContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "OriginatingCurrencyState",
                            "label": "Originating currency / unit interpretation",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "OriginatingCurrencyState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "DEBITAMT",
                            "label": "Stored functional debit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "DEBITAMT",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "CRDTAMNT",
                            "label": "Stored functional credit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "CRDTAMNT",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORDBTAMT",
                            "label": "Stored originating debit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORDBTAMT",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORCRDAMT",
                            "label": "Stored originating credit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORCRDAMT",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SOURCDOC",
                            "label": "Stored source-document code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SOURCDOC",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SERIES",
                            "label": "Stored series code — no invented legend",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SERIES",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "TRXSORCE",
                            "label": "Stored transaction-source reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "TRXSORCE",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORMSTRID",
                            "label": "Original source master reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORMSTRID",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORMSTRNM",
                            "label": "Original source master name",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORMSTRNM",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORDOCNUM",
                            "label": "Original source document reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORDOCNUM",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ORTRXSRC",
                            "label": "Original transaction-source reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ORTRXSRC",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "User_Defined_Text01",
                            "label": "Stored user-defined text 1",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "User_Defined_Text01",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "User_Defined_Text02",
                            "label": "Stored user-defined text 2",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "User_Defined_Text02",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "Scope",
                            "label": "Posted distribution scope — not a complete journal or balance",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "Scope",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        }
                    ],
                    "maxRows": 1000,
                    "name": "Posted GL distributions",
                    "primaryKey": "DistributionKey",
                    "queryParameters": [
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "TRXDATE",
                            "id": "68B24489-B60C-5410-A8ED-C60DC548D930",
                            "name": "date_from",
                            "source": "openingPeriodStart",
                            "type": "date"
                        },
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "TRXDATE",
                            "id": "B70088AA-8BEC-5A02-8E71-8DE0B5DD6B5B",
                            "name": "date_until",
                            "source": "openingPeriodEndExclusive",
                            "type": "date"
                        }
                    ],
                    "refreshPolicy": {
                        "enabled": false,
                        "intervalMinutes": 60
                    },
                    "rootArrayPath": "",
                    "rowLimitEnabled": true,
                    "searchKeys": [
                        "JRNENTRY",
                        "SourceTimestamp",
                        "OPENYEAR",
                        "SEQNUMBR",
                        "REFRENCE",
                        "DSCRIPTN",
                        "AccountNumber",
                        "CurrentAccountDescription",
                        "AccountState",
                        "NumberState",
                        "AccountTypeName",
                        "ValueKind",
                        "FunctionalCurrencyContext",
                        "FunctionalCurrencyState",
                        "CURNCYID",
                        "OriginatingCurrencyContext",
                        "OriginatingCurrencyState",
                        "DEBITAMT",
                        "CRDTAMNT",
                        "ORDBTAMT",
                        "ORCRDAMT",
                        "SOURCDOC",
                        "SERIES",
                        "TRXSORCE",
                        "ORMSTRID",
                        "ORMSTRNM",
                        "ORDOCNUM",
                        "ORTRXSRC",
                        "User_Defined_Text01",
                        "User_Defined_Text02",
                        "Scope"
                    ],
                    "sourceID": "E47EE20F-48B3-5D3C-AF73-124C7C04AFF5",
                    "sqlQuery": "WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL) SELECT g.DistributionKey AS DistributionKey,g.LedgerContextToken AS LedgerContextToken,g.CurrentAccountKey AS CurrentAccountKey,g.CurrentAccountContext AS CurrentAccountContext,g.TRXDATE AS TRXDATE,g.JRNENTRY AS JRNENTRY,g.SourceTimestamp AS SourceTimestamp,g.OPENYEAR AS OPENYEAR,g.SEQNUMBR AS SEQNUMBR,g.REFRENCE AS REFRENCE,g.DSCRIPTN AS DSCRIPTN,g.AccountNumber AS AccountNumber,g.CurrentAccountDescription AS CurrentAccountDescription,g.AccountState AS AccountState,g.NumberState AS NumberState,g.AccountTypeName AS AccountTypeName,g.ValueKind AS ValueKind,g.FunctionalCurrencyContext AS FunctionalCurrencyContext,g.FunctionalCurrencyState AS FunctionalCurrencyState,g.CURNCYID AS CURNCYID,g.OriginatingCurrencyContext AS OriginatingCurrencyContext,g.OriginatingCurrencyState AS OriginatingCurrencyState,g.DEBITAMT AS DEBITAMT,g.CRDTAMNT AS CRDTAMNT,g.ORDBTAMT AS ORDBTAMT,g.ORCRDAMT AS ORCRDAMT,g.SOURCDOC AS SOURCDOC,g.SERIES AS SERIES,g.TRXSORCE AS TRXSORCE,g.ORMSTRID AS ORMSTRID,g.ORMSTRNM AS ORMSTRNM,g.ORDOCNUM AS ORDOCNUM,g.ORTRXSRC AS ORTRXSRC,g.User_Defined_Text01 AS User_Defined_Text01,g.User_Defined_Text02 AS User_Defined_Text02,g.Scope AS Scope FROM ledger g WHERE NOT EXISTS (SELECT 1 FROM dbo.GL20000 bad WHERE bad.TRXDATE>=:date_from AND bad.TRXDATE<:date_until AND NOT (bad.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=bad.DEX_ROW_ID)=1)) AND g.TRXDATE>=:date_from AND g.TRXDATE<:date_until",
                    "tableName": ""
                },
                {
                    "cacheMode": "live",
                    "calculatedFields": [],
                    "customQueryIntegrationName": "",
                    "endpointPath": "",
                    "fetchSortRules": [
                        {
                            "direction": "ascending",
                            "id": "BF1B51DC-8E01-5DD8-97E6-A2638F5B15C6",
                            "key": "AccountNumber",
                            "type": "text"
                        }
                    ],
                    "id": "41F90D88-4C35-57F3-96BA-52A3982BB738",
                    "mappings": [
                        {
                            "commonFieldKey": "",
                            "key": "AccountKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentContextToken",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentContextToken",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentDistributionKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentDistributionKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "OriginalLedgerContext",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "OriginalLedgerContext",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "AccountNumber",
                            "label": "Complete stored account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountNumber",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ACTDESCR",
                            "label": "Account description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ACTDESCR",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "NumberState",
                            "label": "Stored account-number availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "NumberState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "AccountTypeName",
                            "label": "Documented account type",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "AccountTypeName",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ActiveContext",
                            "label": "Stored active setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ActiveContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "EntryContext",
                            "label": "Stored manual/direct-entry setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "EntryContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "PostingContext",
                            "label": "Income/balance mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "PostingContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "BalanceSideContext",
                            "label": "Debit/credit mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "BalanceSideContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "MNACSGMT",
                            "label": "Stored main-account segment",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "MNACSGMT",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ACCTTYPE",
                            "label": "Stored account type code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ACCTTYPE",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ACCATNUM",
                            "label": "Stored account category code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ACCATNUM",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ACTIVE",
                            "label": "Stored active flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ACTIVE",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ACCTENTR",
                            "label": "Stored manual/direct-entry flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ACCTENTR",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "PSTNGTYP",
                            "label": "Stored posting type code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "PSTNGTYP",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "TPCLBLNC",
                            "label": "Stored typical-balance code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "TPCLBLNC",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "BALFRCLC",
                            "label": "Stored balance-calculation option code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "BALFRCLC",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "Clear_Balance",
                            "label": "Stored Clear_Balance flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "Clear_Balance",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "Scope",
                            "label": "Current master context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "Scope",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        }
                    ],
                    "maxRows": 1000,
                    "name": "Current distribution account",
                    "primaryKey": "AccountKey",
                    "queryParameters": [
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "DistributionKey",
                            "id": "DA060BE0-E185-5A6C-BB4D-96A8685DAFD5",
                            "name": "distribution_ref",
                            "source": "parentField",
                            "type": "text"
                        },
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "LedgerContextToken",
                            "id": "82D35956-3F7C-56DF-A45D-AF060949305E",
                            "name": "ledger_context",
                            "source": "parentField",
                            "type": "text"
                        }
                    ],
                    "refreshPolicy": {
                        "enabled": false,
                        "intervalMinutes": 60
                    },
                    "rootArrayPath": "",
                    "rowLimitEnabled": true,
                    "searchKeys": [
                        "AccountNumber",
                        "ACTDESCR",
                        "NumberState",
                        "AccountTypeName",
                        "ActiveContext",
                        "EntryContext",
                        "PostingContext",
                        "BalanceSideContext",
                        "MNACSGMT",
                        "ACCTTYPE",
                        "ACCATNUM",
                        "ACTIVE",
                        "ACCTENTR",
                        "PSTNGTYP",
                        "TPCLBLNC",
                        "BALFRCLC",
                        "Clear_Balance",
                        "Scope"
                    ],
                    "sourceID": "E47EE20F-48B3-5D3C-AF73-124C7C04AFF5",
                    "sqlQuery": "WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL), ledger_parent AS (SELECT g.DistributionKey,g.LedgerContextToken,g.CurrentAccountKey,g.CurrentAccountContext FROM ledger g WHERE (RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:distribution_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:distribution_ref AS nvarchar(4000))))) AND (RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:ledger_context AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:ledger_context AS nvarchar(4000))))) AND EXISTS (SELECT 1 FROM dbo.GL20000 src WHERE src.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=src.DEX_ROW_ID)=1 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000))))))) SELECT a.AccountKey AS AccountKey,a.ParentContextToken AS ParentContextToken,a.AccountNumber AS AccountNumber,a.ACTDESCR AS ACTDESCR,a.NumberState AS NumberState,a.AccountTypeName AS AccountTypeName,a.ActiveContext AS ActiveContext,a.EntryContext AS EntryContext,a.PostingContext AS PostingContext,a.BalanceSideContext AS BalanceSideContext,a.MNACSGMT AS MNACSGMT,a.ACCTTYPE AS ACCTTYPE,a.ACCATNUM AS ACCATNUM,a.ACTIVE AS ACTIVE,a.ACCTENTR AS ACCTENTR,a.PSTNGTYP AS PSTNGTYP,a.TPCLBLNC AS TPCLBLNC,a.BALFRCLC AS BALFRCLC,a.Clear_Balance AS Clear_Balance,a.Scope AS Scope,p.DistributionKey AS ParentDistributionKey,p.LedgerContextToken AS OriginalLedgerContext FROM chart a JOIN ledger_parent p ON (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))))) WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL",
                    "tableName": ""
                },
                {
                    "cacheMode": "live",
                    "calculatedFields": [],
                    "customQueryIntegrationName": "",
                    "endpointPath": "",
                    "fetchSortRules": [
                        {
                            "direction": "ascending",
                            "id": "B7E6B71C-8B5F-521D-BDE2-32E81B9EF8E2",
                            "key": "SegmentNumber",
                            "type": "number"
                        }
                    ],
                    "id": "1CA4D7C2-B45E-5414-BF9F-182BDF5E9775",
                    "mappings": [
                        {
                            "commonFieldKey": "",
                            "key": "SegmentKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentAccountKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentAccountKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentContextToken",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentContextToken",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentDistributionKey",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentDistributionKey",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "OriginalLedgerContext",
                            "label": "Hidden full route identity / source context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "OriginalLedgerContext",
                            "type": "text",
                            "visibleInDetail": false,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "ParentAccountNumber",
                            "label": "Current complete account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "ParentAccountNumber",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SegmentNumber",
                            "label": "Account segment position",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentNumber",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SegmentCode",
                            "label": "Complete stored segment code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentCode",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SegmentName",
                            "label": "Current segment name",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentName",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SegmentDescription",
                            "label": "Current code description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentDescription",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "MainSegmentContext",
                            "label": "Current main-segment setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "MainSegmentContext",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SettingState",
                            "label": "Segment setting availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SettingState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "DescriptionState",
                            "label": "Code-description availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "DescriptionState",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": true
                        },
                        {
                            "commonFieldKey": "",
                            "key": "FormattedSegmentCode",
                            "label": "Stored formatted-table segment code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "FormattedSegmentCode",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        },
                        {
                            "commonFieldKey": "",
                            "key": "SegmentScope",
                            "label": "Current structure context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text",
                            "sourceColumn": "SegmentScope",
                            "type": "text",
                            "visibleInDetail": true,
                            "visibleInList": false
                        }
                    ],
                    "maxRows": 1000,
                    "name": "Distribution account segments",
                    "primaryKey": "SegmentKey",
                    "queryParameters": [
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "ParentDistributionKey",
                            "id": "DA060BE0-E185-5A6C-BB4D-96A8685DAFD5",
                            "name": "distribution_ref",
                            "source": "parentField",
                            "type": "text"
                        },
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "OriginalLedgerContext",
                            "id": "82D35956-3F7C-56DF-A45D-AF060949305E",
                            "name": "ledger_context",
                            "source": "parentField",
                            "type": "text"
                        },
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "AccountKey",
                            "id": "420F8645-DAC2-5487-B19F-922E7B53EC33",
                            "name": "account_ref",
                            "source": "parentField",
                            "type": "text"
                        },
                        {
                            "constantValue": "",
                            "dayOffset": 0,
                            "fieldKey": "ParentContextToken",
                            "id": "D44A20F9-50D1-5785-9EA4-2A025424068F",
                            "name": "context_ref",
                            "source": "parentField",
                            "type": "text"
                        }
                    ],
                    "refreshPolicy": {
                        "enabled": false,
                        "intervalMinutes": 60
                    },
                    "rootArrayPath": "",
                    "rowLimitEnabled": true,
                    "searchKeys": [
                        "ParentAccountNumber",
                        "SegmentNumber",
                        "SegmentCode",
                        "SegmentName",
                        "SegmentDescription",
                        "MainSegmentContext",
                        "SettingState",
                        "DescriptionState",
                        "FormattedSegmentCode",
                        "SegmentScope"
                    ],
                    "sourceID": "E47EE20F-48B3-5D3C-AF73-124C7C04AFF5",
                    "sqlQuery": "WITH formatted_accounts AS (SELECT f.ACTINDX AS ACTINDX,COUNT(1) AS FormatRows,MAX(f.ACTNUMST) AS RawAccountNumber,MAX(f.ACTNUMBR_1) AS ACTNUMBR_1,MAX(f.ACTNUMBR_2) AS ACTNUMBR_2,MAX(f.ACTNUMBR_3) AS ACTNUMBR_3,MAX(f.ACTNUMBR_4) AS ACTNUMBR_4,MAX(f.ACTNUMBR_5) AS ACTNUMBR_5,MAX(f.ACTNUMBR_6) AS ACTNUMBR_6,MAX(f.ACTNUMBR_7) AS ACTNUMBR_7,MAX(f.ACTNUMBR_8) AS ACTNUMBR_8 FROM dbo.GL00105 f GROUP BY f.ACTINDX), account_base AS (SELECT m.ACTINDX AS SourceAccountIndex,m.ACTDESCR AS ACTDESCR,m.MNACSGMT AS MNACSGMT,m.ACCTTYPE AS ACCTTYPE,m.PSTNGTYP AS PSTNGTYP,m.ACCATNUM AS ACCATNUM,m.ACTIVE AS ACTIVE,m.TPCLBLNC AS TPCLBLNC,m.BALFRCLC AS BALFRCLC,m.ACCTENTR AS ACCTENTR,m.Clear_Balance AS Clear_Balance,m.ACTNUMBR_1 AS ACTNUMBR_1,m.ACTNUMBR_2 AS ACTNUMBR_2,m.ACTNUMBR_3 AS ACTNUMBR_3,m.ACTNUMBR_4 AS ACTNUMBR_4,m.ACTNUMBR_5 AS ACTNUMBR_5,m.ACTNUMBR_6 AS ACTNUMBR_6,m.ACTNUMBR_7 AS ACTNUMBR_7,m.ACTNUMBR_8 AS ACTNUMBR_8,CASE WHEN f.FormatRows IS NULL THEN N'Missing formatted account record' WHEN f.FormatRows<>1 THEN N'Ambiguous formatted account records' WHEN NOT (((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000)))))))) THEN N'Segments disagree with formatted record' WHEN f.RawAccountNumber IS NULL OR RTRIM(f.RawAccountNumber)=N'' THEN N'Complete stored account number is empty' ELSE N'Unique stored number; all eight segments agree' END AS NumberState,CASE WHEN f.FormatRows=1 AND ((f.ACTNUMBR_1 IS NULL AND m.ACTNUMBR_1 IS NULL) OR (f.ACTNUMBR_1 IS NOT NULL AND m.ACTNUMBR_1 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_1 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_1 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_2 IS NULL AND m.ACTNUMBR_2 IS NULL) OR (f.ACTNUMBR_2 IS NOT NULL AND m.ACTNUMBR_2 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_2 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_2 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_3 IS NULL AND m.ACTNUMBR_3 IS NULL) OR (f.ACTNUMBR_3 IS NOT NULL AND m.ACTNUMBR_3 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_3 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_3 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_4 IS NULL AND m.ACTNUMBR_4 IS NULL) OR (f.ACTNUMBR_4 IS NOT NULL AND m.ACTNUMBR_4 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_4 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_4 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_5 IS NULL AND m.ACTNUMBR_5 IS NULL) OR (f.ACTNUMBR_5 IS NOT NULL AND m.ACTNUMBR_5 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_5 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_5 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_6 IS NULL AND m.ACTNUMBR_6 IS NULL) OR (f.ACTNUMBR_6 IS NOT NULL AND m.ACTNUMBR_6 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_6 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_6 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_7 IS NULL AND m.ACTNUMBR_7 IS NULL) OR (f.ACTNUMBR_7 IS NOT NULL AND m.ACTNUMBR_7 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_7 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_7 AS nvarchar(4000))))))) AND ((f.ACTNUMBR_8 IS NULL AND m.ACTNUMBR_8 IS NULL) OR (f.ACTNUMBR_8 IS NOT NULL AND m.ACTNUMBR_8 IS NOT NULL AND (RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(f.ACTNUMBR_8 AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(m.ACTNUMBR_8 AS nvarchar(4000))))))) THEN f.RawAccountNumber END AS ValidAccountNumber FROM dbo.GL00100 m LEFT JOIN formatted_accounts f ON f.ACTINDX=m.ACTINDX WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL AND m.ACTINDX IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL00100 other WHERE other.ACTINDX=m.ACTINDX)=1), chart AS (SELECT b.SourceAccountIndex AS SourceAccountIndex,b.ACTDESCR AS ACTDESCR,b.MNACSGMT AS MNACSGMT,b.ACCTTYPE AS ACCTTYPE,b.PSTNGTYP AS PSTNGTYP,b.ACCATNUM AS ACCATNUM,b.ACTIVE AS ACTIVE,b.TPCLBLNC AS TPCLBLNC,b.BALFRCLC AS BALFRCLC,b.ACCTENTR AS ACCTENTR,b.Clear_Balance AS Clear_Balance,b.ACTNUMBR_1 AS ACTNUMBR_1,b.ACTNUMBR_2 AS ACTNUMBR_2,b.ACTNUMBR_3 AS ACTNUMBR_3,b.ACTNUMBR_4 AS ACTNUMBR_4,b.ACTNUMBR_5 AS ACTNUMBR_5,b.ACTNUMBR_6 AS ACTNUMBR_6,b.ACTNUMBR_7 AS ACTNUMBR_7,b.ACTNUMBR_8 AS ACTNUMBR_8,CONCAT(LEN(RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(b.SourceAccountIndex AS nvarchar(100)))) AS AccountKey,CONCAT(CASE WHEN b.ACTDESCR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTDESCR AS nvarchar(4000)))) END,CASE WHEN b.MNACSGMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))),N':',RTRIM(CAST(b.MNACSGMT AS nvarchar(4000)))) END,CASE WHEN b.ACCTTYPE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTTYPE AS nvarchar(4000)))) END,CASE WHEN b.PSTNGTYP IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))),N':',RTRIM(CAST(b.PSTNGTYP AS nvarchar(4000)))) END,CASE WHEN b.ACCATNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCATNUM AS nvarchar(4000)))) END,CASE WHEN b.ACTIVE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTIVE AS nvarchar(4000)))) END,CASE WHEN b.TPCLBLNC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))),N':',RTRIM(CAST(b.TPCLBLNC AS nvarchar(4000)))) END,CASE WHEN b.BALFRCLC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))),N':',RTRIM(CAST(b.BALFRCLC AS nvarchar(4000)))) END,CASE WHEN b.ACCTENTR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACCTENTR AS nvarchar(4000)))) END,CASE WHEN b.Clear_Balance IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))),N':',RTRIM(CAST(b.Clear_Balance AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_1 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_1 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_2 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_2 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_3 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_3 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_4 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_4 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_5 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_5 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_6 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_6 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_7 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_7 AS nvarchar(4000)))) END,CASE WHEN b.ACTNUMBR_8 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))),N':',RTRIM(CAST(b.ACTNUMBR_8 AS nvarchar(4000)))) END,CASE WHEN b.ValidAccountNumber IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))),N':',RTRIM(CAST(b.ValidAccountNumber AS nvarchar(4000)))) END,CASE WHEN b.NumberState IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(b.NumberState AS nvarchar(4000)))),N':',RTRIM(CAST(b.NumberState AS nvarchar(4000)))) END) AS ParentContextToken,CASE WHEN b.ValidAccountNumber IS NOT NULL AND RTRIM(b.ValidAccountNumber)<>N'' THEN RTRIM(b.ValidAccountNumber) ELSE N'Complete account number unavailable' END AS AccountNumber,b.NumberState AS NumberState,CASE b.ACCTTYPE WHEN 1 THEN N'Posting account (code 1)' WHEN 2 THEN N'Unit account — nonfinancial (code 2)' WHEN 3 THEN N'Allocation account (code 3)' ELSE N'Other / unknown stored account type' END AS AccountTypeName,CASE b.ACTIVE WHEN 0 THEN N'Inactive setting' WHEN 1 THEN N'Active setting' ELSE N'Unknown stored active flag' END AS ActiveContext,CASE b.ACCTENTR WHEN 0 THEN N'Manual/direct entry disabled setting' WHEN 1 THEN N'Manual/direct entry enabled setting' ELSE N'Unknown stored entry flag' END AS EntryContext,CASE b.PSTNGTYP WHEN 0 THEN N'Balance-sheet mapping context' WHEN 1 THEN N'Income-statement mapping context' ELSE N'Unknown stored posting type' END AS PostingContext,CASE b.TPCLBLNC WHEN 0 THEN N'Debit mapping context' WHEN 1 THEN N'Credit mapping context' ELSE N'Unknown stored balance side' END AS BalanceSideContext,N'Current chart master; settings are not effective permissions or a journal snapshot' AS Scope FROM account_base b), currency_setup AS (SELECT COUNT(1) AS SettingRows, MAX(FUNLCURR) AS FunctionalCurrency FROM dbo.MC40000), ledger AS (SELECT l.JRNENTRY AS JRNENTRY, l.OPENYEAR AS OPENYEAR, l.SEQNUMBR AS SEQNUMBR, l.REFRENCE AS REFRENCE, l.DSCRIPTN AS DSCRIPTN, l.CURNCYID AS CURNCYID, CAST(l.DEBITAMT AS nvarchar(100)) AS DEBITAMT, CAST(l.CRDTAMNT AS nvarchar(100)) AS CRDTAMNT, CAST(l.ORDBTAMT AS nvarchar(100)) AS ORDBTAMT, CAST(l.ORCRDAMT AS nvarchar(100)) AS ORCRDAMT, l.SOURCDOC AS SOURCDOC, l.SERIES AS SERIES, l.TRXSORCE AS TRXSORCE, l.ORMSTRID AS ORMSTRID, l.ORMSTRNM AS ORMSTRNM, l.ORDOCNUM AS ORDOCNUM, l.ORTRXSRC AS ORTRXSRC, l.User_Defined_Text01 AS User_Defined_Text01, l.User_Defined_Text02 AS User_Defined_Text02, l.TRXDATE AS TRXDATE, CONVERT(nvarchar(33),l.TRXDATE,126) AS SourceTimestamp, CONCAT(LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(100)))) AS DistributionKey, CONCAT(CASE WHEN l.OPENYEAR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))),N':',RTRIM(CAST(l.OPENYEAR AS nvarchar(4000)))) END,CASE WHEN l.JRNENTRY IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))),N':',RTRIM(CAST(l.JRNENTRY AS nvarchar(4000)))) END,CASE WHEN l.SOURCDOC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))),N':',RTRIM(CAST(l.SOURCDOC AS nvarchar(4000)))) END,CASE WHEN l.REFRENCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.REFRENCE AS nvarchar(4000)))) END,CASE WHEN l.DSCRIPTN IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))),N':',RTRIM(CAST(l.DSCRIPTN AS nvarchar(4000)))) END,CASE WHEN CONVERT(nvarchar(33),l.TRXDATE,126) IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))),N':',RTRIM(CAST(CONVERT(nvarchar(33),l.TRXDATE,126) AS nvarchar(4000)))) END,CASE WHEN l.TRXSORCE IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))),N':',RTRIM(CAST(l.TRXSORCE AS nvarchar(4000)))) END,CASE WHEN l.ACTINDX IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))),N':',RTRIM(CAST(l.ACTINDX AS nvarchar(4000)))) END,CASE WHEN l.SERIES IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SERIES AS nvarchar(4000)))),N':',RTRIM(CAST(l.SERIES AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRID AS nvarchar(4000)))) END,CASE WHEN l.ORMSTRNM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORMSTRNM AS nvarchar(4000)))) END,CASE WHEN l.ORDOCNUM IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDOCNUM AS nvarchar(4000)))) END,CASE WHEN l.ORTRXSRC IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORTRXSRC AS nvarchar(4000)))) END,CASE WHEN l.SEQNUMBR IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))),N':',RTRIM(CAST(l.SEQNUMBR AS nvarchar(4000)))) END,CASE WHEN l.CURNCYID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))),N':',RTRIM(CAST(l.CURNCYID AS nvarchar(4000)))) END,CASE WHEN l.DEBITAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEBITAMT AS nvarchar(4000)))) END,CASE WHEN l.CRDTAMNT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))),N':',RTRIM(CAST(l.CRDTAMNT AS nvarchar(4000)))) END,CASE WHEN l.ORDBTAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORDBTAMT AS nvarchar(4000)))) END,CASE WHEN l.ORCRDAMT IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))),N':',RTRIM(CAST(l.ORCRDAMT AS nvarchar(4000)))) END,CASE WHEN l.DEX_ROW_ID IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))),N':',RTRIM(CAST(l.DEX_ROW_ID AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text01 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text01 AS nvarchar(4000)))) END,CASE WHEN l.User_Defined_Text02 IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))),N':',RTRIM(CAST(l.User_Defined_Text02 AS nvarchar(4000)))) END,CASE WHEN acct.ParentContextToken IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))),N':',RTRIM(CAST(acct.ParentContextToken AS nvarchar(4000)))) END,CASE WHEN CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))),N':',RTRIM(CAST(CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS nvarchar(4000)))) END,CASE WHEN setting.SettingRows IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))),N':',RTRIM(CAST(setting.SettingRows AS nvarchar(4000)))) END,CASE WHEN setting.FunctionalCurrency IS NULL THEN N'N;' ELSE CONCAT(N'T',LEN(RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))),N':',RTRIM(CAST(setting.FunctionalCurrency AS nvarchar(4000)))) END) AS LedgerContextToken, acct.AccountKey AS CurrentAccountKey, acct.ParentContextToken AS CurrentAccountContext, acct.AccountNumber AS AccountNumber, acct.ACTDESCR AS CurrentAccountDescription, CASE WHEN l.ACTINDX IS NULL THEN N'Missing source account identity' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)=0 THEN N'Missing current account' WHEN (SELECT COUNT(1) FROM dbo.GL00100 cm WHERE cm.ACTINDX=l.ACTINDX)<>1 THEN N'Ambiguous current account' ELSE N'Unique current account; not historical state' END AS AccountState, acct.NumberState AS NumberState, acct.AccountTypeName AS AccountTypeName, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities — NOT money' WHEN acct.ACCTTYPE=1 THEN N'Posting account; separate monetary contexts' WHEN acct.ACCTTYPE=3 THEN N'Allocation account; monetary interpretation unverified' ELSE N'Account type unavailable / unknown; monetary interpretation unverified' END AS ValueKind, CASE WHEN acct.ACCTTYPE=1 AND setting.SettingRows=1 AND setting.FunctionalCurrency IS NOT NULL AND RTRIM(setting.FunctionalCurrency)<>N'' THEN RTRIM(setting.FunctionalCurrency) END AS FunctionalCurrencyContext, CASE WHEN setting.SettingRows=0 THEN N'Missing company functional-currency setting' WHEN setting.SettingRows<>1 THEN N'Ambiguous company functional-currency settings' WHEN setting.FunctionalCurrency IS NULL OR RTRIM(setting.FunctionalCurrency)=N'' THEN N'Unique setting; currency missing or blank' WHEN acct.ACCTTYPE=2 THEN N'Unique stored currency setting; NOT applicable to unit quantities' WHEN acct.ACCTTYPE=1 THEN N'Unique stored company currency ID; no ISO or installed-storage certification' ELSE N'Unique stored currency setting; monetary interpretation unverified' END AS FunctionalCurrencyState, CASE WHEN acct.ACCTTYPE=1 AND l.CURNCYID IS NOT NULL AND RTRIM(l.CURNCYID)<>N'' THEN RTRIM(l.CURNCYID) END AS OriginatingCurrencyContext, CASE WHEN acct.ACCTTYPE=2 THEN N'Unit quantities; stored currency ID is NOT a unit measure' WHEN acct.ACCTTYPE IS NULL OR acct.ACCTTYPE<>1 THEN N'Monetary interpretation unverified; source currency ID preserved' WHEN l.CURNCYID IS NULL OR RTRIM(l.CURNCYID)=N'' THEN N'Missing / blank source originating currency ID' ELSE N'Stored originating currency context; no ISO or conversion certification' END AS OriginatingCurrencyState, N'GL20000 Open-year posted distribution; not all years, complete journal, ledger partition or balance' AS Scope FROM dbo.GL20000 l LEFT JOIN chart acct ON acct.SourceAccountIndex=l.ACTINDX CROSS JOIN currency_setup setting WHERE OBJECT_ID(N'dbo.GL20000',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00100',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL00105',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.GL40200',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.SY00300',N'U') IS NOT NULL AND OBJECT_ID(N'dbo.MC40000',N'U') IS NOT NULL), ledger_parent AS (SELECT g.DistributionKey,g.LedgerContextToken,g.CurrentAccountKey,g.CurrentAccountContext FROM ledger g WHERE (RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:distribution_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:distribution_ref AS nvarchar(4000))))) AND (RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:ledger_context AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(g.LedgerContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:ledger_context AS nvarchar(4000))))) AND EXISTS (SELECT 1 FROM dbo.GL20000 src WHERE src.DEX_ROW_ID IS NOT NULL AND (SELECT COUNT(1) FROM dbo.GL20000 dup WHERE dup.DEX_ROW_ID=src.DEX_ROW_ID)=1 AND (RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(g.DistributionKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(CONCAT(LEN(RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))),N':',RTRIM(CAST(src.DEX_ROW_ID AS nvarchar(100)))) AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(g.DistributionKey AS nvarchar(4000))))))), segment_parent AS (SELECT a.SourceAccountIndex,a.AccountKey,a.ParentContextToken,a.AccountNumber,a.NumberState,a.ACTNUMBR_1,a.ACTNUMBR_2,a.ACTNUMBR_3,a.ACTNUMBR_4,a.ACTNUMBR_5,a.ACTNUMBR_6,a.ACTNUMBR_7,a.ACTNUMBR_8 FROM chart a JOIN ledger_parent p ON (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountKey AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(p.CurrentAccountContext AS nvarchar(4000))))) WHERE (RTRIM(CAST(a.AccountKey AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:account_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.AccountKey AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:account_ref AS nvarchar(4000))))) AND (RTRIM(CAST(a.ParentContextToken AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(:context_ref AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.ParentContextToken AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(:context_ref AS nvarchar(4000)))))), segment AS (SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,1 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_1 AS SegmentCode,p.ACTNUMBR_1 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_1 IS NOT NULL AND RTRIM(p.ACTNUMBR_1)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,2 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_2 AS SegmentCode,p.ACTNUMBR_2 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_2 IS NOT NULL AND RTRIM(p.ACTNUMBR_2)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,3 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_3 AS SegmentCode,p.ACTNUMBR_3 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_3 IS NOT NULL AND RTRIM(p.ACTNUMBR_3)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,4 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_4 AS SegmentCode,p.ACTNUMBR_4 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_4 IS NOT NULL AND RTRIM(p.ACTNUMBR_4)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,5 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_5 AS SegmentCode,p.ACTNUMBR_5 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_5 IS NOT NULL AND RTRIM(p.ACTNUMBR_5)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,6 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_6 AS SegmentCode,p.ACTNUMBR_6 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_6 IS NOT NULL AND RTRIM(p.ACTNUMBR_6)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,7 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_7 AS SegmentCode,p.ACTNUMBR_7 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_7 IS NOT NULL AND RTRIM(p.ACTNUMBR_7)<>N'' UNION ALL SELECT p.SourceAccountIndex,p.AccountKey,p.ParentContextToken,p.AccountNumber,8 AS SegmentNumber,p.NumberState AS NumberState,p.ACTNUMBR_8 AS SegmentCode,p.ACTNUMBR_8 AS FormattedSegmentCode FROM segment_parent p WHERE p.ACTNUMBR_8 IS NOT NULL AND RTRIM(p.ACTNUMBR_8)<>N'') SELECT CONCAT(LEN(RTRIM(CAST(segment.SourceAccountIndex AS nvarchar(100)))),N':',RTRIM(CAST(segment.SourceAccountIndex AS nvarchar(100))),LEN(RTRIM(CAST(segment.SegmentNumber AS nvarchar(100)))),N':',RTRIM(CAST(segment.SegmentNumber AS nvarchar(100)))) AS SegmentKey,segment.AccountKey AS ParentAccountKey,segment.ParentContextToken AS ParentContextToken,:distribution_ref AS ParentDistributionKey,:ledger_context AS OriginalLedgerContext,segment.AccountNumber AS ParentAccountNumber,segment.SegmentNumber AS SegmentNumber,RTRIM(segment.SegmentCode) AS SegmentCode,CASE WHEN segment.NumberState=N'Unique stored number; all eight segments agree' THEN RTRIM(segment.FormattedSegmentCode) END AS FormattedSegmentCode,(SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.SGMTNAME) END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) AS SegmentName,(SELECT CASE WHEN COUNT(1)=1 THEN MAX(d.DSCRIPTN) END FROM dbo.GL40200 d WHERE d.SGMTNUMB=segment.SegmentNumber AND (RTRIM(CAST(d.SGMNTID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(segment.SegmentCode AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(d.SGMNTID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(segment.SegmentCode AS nvarchar(4000)))))) AS SegmentDescription,CASE (SELECT CASE WHEN COUNT(1)=1 THEN MAX(s.MNSEGIND) END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) WHEN 0 THEN N'Not main segment' WHEN 1 THEN N'Main segment' ELSE N'Unknown / unavailable main-segment setting' END AS MainSegmentContext,(SELECT CASE WHEN COUNT(1)=0 THEN N'Missing current metadata' WHEN COUNT(1)<>1 THEN N'Ambiguous current metadata' WHEN MAX(s.SGMTNAME) IS NULL THEN N'Unique record; label is NULL' WHEN RTRIM(MAX(s.SGMTNAME))=N'' THEN N'Unique record; label is blank' ELSE N'Unique current metadata' END FROM dbo.SY00300 s WHERE s.SGMTNUMB=segment.SegmentNumber) AS SettingState,(SELECT CASE WHEN COUNT(1)=0 THEN N'Missing current metadata' WHEN COUNT(1)<>1 THEN N'Ambiguous current metadata' WHEN MAX(d.DSCRIPTN) IS NULL THEN N'Unique record; label is NULL' WHEN RTRIM(MAX(d.DSCRIPTN))=N'' THEN N'Unique record; label is blank' ELSE N'Unique current metadata' END FROM dbo.GL40200 d WHERE d.SGMTNUMB=segment.SegmentNumber AND (RTRIM(CAST(d.SGMNTID AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 = RTRIM(CAST(segment.SegmentCode AS nvarchar(4000))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(d.SGMNTID AS nvarchar(4000)))) = DATALENGTH(RTRIM(CAST(segment.SegmentCode AS nvarchar(4000)))))) AS DescriptionState,N'Current stored nonblank segment; no assumed department, profit centre or category role' AS SegmentScope FROM segment",
                    "tableName": ""
                }
            ],
            "pages": [
                {
                    "actions": [
                        {
                            "id": "C9E970B4-959C-5B2D-9C38-7A98D0D90574",
                            "kind": "showRelated",
                            "relatedInitiallyExpanded": false,
                            "relatedPresentation": "separate",
                            "relatedPreviewLimit": 12,
                            "relatedRowStyle": "cards",
                            "relatedShowsCount": true,
                            "relationID": "7E6D119F-9CCA-559B-80D4-D57F09A7E6BE",
                            "systemImage": "list.bullet.rectangle",
                            "targetDatasetID": "41F90D88-4C35-57F3-96BA-52A3982BB738",
                            "title": "Current account",
                            "urlKey": ""
                        }
                    ],
                    "badgeKey": "",
                    "cardEnrichments": [],
                    "cardFieldLayout": [
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SourceTimestamp",
                            "label": "Transaction timestamp",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "JRNENTRY",
                            "label": "Journal number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ValueKind",
                            "label": "Value interpretation",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "DEBITAMT",
                            "label": "Functional debit",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "CRDTAMNT",
                            "label": "Functional credit",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "FunctionalCurrencyContext",
                            "label": "Functional money context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORDBTAMT",
                            "label": "Originating debit",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORCRDAMT",
                            "label": "Originating credit",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "OriginatingCurrencyContext",
                            "label": "Originating money context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountState",
                            "label": "Account availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "datasetID": "87679410-CC5B-5A5B-BE76-92D773FFA01D",
                    "dateFilterKey": "",
                    "dateFilterLastDays": 7,
                    "dateFilterPreset": "none",
                    "detailFieldLayout": [
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "JRNENTRY",
                            "label": "Stored journal number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SourceTimestamp",
                            "label": "Complete source transaction timestamp",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "OPENYEAR",
                            "label": "Stored open fiscal year",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SEQNUMBR",
                            "label": "Stored distribution sequence",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "REFRENCE",
                            "label": "Stored reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "DSCRIPTN",
                            "label": "Stored distribution description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Source distribution identity and scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "Scope",
                            "label": "Posted distribution scope — not a complete journal or balance",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountNumber",
                            "label": "Current complete account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "CurrentAccountDescription",
                            "label": "Current account description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountState",
                            "label": "Current account availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "NumberState",
                            "label": "Current formatted-number availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountTypeName",
                            "label": "Current documented account type",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account — not a journal snapshot",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ValueKind",
                            "label": "Value interpretation — current account context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent functional values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "DEBITAMT",
                            "label": "Stored functional debit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent functional values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "CRDTAMNT",
                            "label": "Stored functional credit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent functional values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "FunctionalCurrencyContext",
                            "label": "Functional monetary currency context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent functional values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "FunctionalCurrencyState",
                            "label": "Whole company functional-currency setting availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent originating values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORDBTAMT",
                            "label": "Stored originating debit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent originating values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORCRDAMT",
                            "label": "Stored originating credit value",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent originating values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "CURNCYID",
                            "label": "Stored originating currency ID (not a unit measure)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent originating values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "OriginatingCurrencyContext",
                            "label": "Originating monetary currency context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent originating values and context",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "OriginatingCurrencyState",
                            "label": "Originating currency / unit interpretation",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SOURCDOC",
                            "label": "Stored source-document code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SERIES",
                            "label": "Stored series code — no invented legend",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "TRXSORCE",
                            "label": "Stored transaction-source reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORMSTRID",
                            "label": "Original source master reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORMSTRNM",
                            "label": "Original source master name",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORDOCNUM",
                            "label": "Original source document reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Original source references — no inferred partner relation",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ORTRXSRC",
                            "label": "Original transaction-source reference",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored additional text",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "User_Defined_Text01",
                            "label": "Stored user-defined text 1",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored additional text",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "User_Defined_Text02",
                            "label": "Stored user-defined text 2",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "detailLiveRefreshSeconds": 0,
                    "fixedFilters": [],
                    "id": "D5B8DCA9-525B-5986-8C57-B09C2F9D02F6",
                    "openFilters": [
                        {
                            "datePeriodOptions": [
                                "today",
                                "currentMonth",
                                "last7Days",
                                "last30Days",
                                "last90Days"
                            ],
                            "id": "3C354550-9C93-5A22-998C-AD2E8174540F",
                            "includeAllOption": false,
                            "key": "TRXDATE",
                            "title": "Transaction-date period",
                            "type": "date"
                        },
                        {
                            "id": "428B89EE-45BA-575A-A580-E9BE618C3700",
                            "includeAllOption": true,
                            "key": "JRNENTRY",
                            "title": "Stored journal number",
                            "type": "number"
                        },
                        {
                            "id": "699C7186-67ED-574E-95BD-EBA366ABE9DB",
                            "includeAllOption": true,
                            "key": "AccountNumber",
                            "title": "Current account",
                            "type": "text"
                        },
                        {
                            "id": "9C095E93-C8E0-59B2-884A-D03375429571",
                            "includeAllOption": true,
                            "key": "CURNCYID",
                            "title": "Source currency ID",
                            "type": "text"
                        }
                    ],
                    "pageSize": 100,
                    "requiresOpeningFilterSelection": true,
                    "showOnHome": true,
                    "sortRules": [
                        {
                            "direction": "descending",
                            "id": "B8EA2DA0-D967-5494-B8C1-6E26BCF11A9E",
                            "key": "TRXDATE",
                            "type": "date"
                        },
                        {
                            "direction": "ascending",
                            "id": "BECF2A88-E500-581E-9CC9-E72B46CB1A46",
                            "key": "JRNENTRY",
                            "type": "number"
                        },
                        {
                            "direction": "ascending",
                            "id": "4F458A2C-358B-5577-87CF-9C95B536418D",
                            "key": "DistributionKey",
                            "type": "text"
                        }
                    ],
                    "subtitle": "",
                    "subtitleKey": "AccountNumber",
                    "systemImage": "doc.text",
                    "title": "Posted GL distributions",
                    "titleKey": "REFRENCE"
                },
                {
                    "actions": [
                        {
                            "id": "F1458C0E-C95A-5DAC-BE22-FDD4C3285CFF",
                            "kind": "showRelated",
                            "relatedInitiallyExpanded": false,
                            "relatedPresentation": "separate",
                            "relatedPreviewLimit": 12,
                            "relatedRowStyle": "cards",
                            "relatedShowsCount": true,
                            "relationID": "DD69CEDF-B5B9-5F50-A2AA-055F2E8C84AD",
                            "systemImage": "list.bullet.rectangle",
                            "targetDatasetID": "1CA4D7C2-B45E-5414-BF9F-182BDF5E9775",
                            "title": "Account segments",
                            "urlKey": ""
                        }
                    ],
                    "badgeKey": "",
                    "cardEnrichments": [],
                    "cardFieldLayout": [
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountTypeName",
                            "label": "Documented account type",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ActiveContext",
                            "label": "Stored active setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "EntryContext",
                            "label": "Stored manual/direct-entry setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "PostingContext",
                            "label": "Income/balance mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "BalanceSideContext",
                            "label": "Debit/credit mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "MNACSGMT",
                            "label": "Stored main-account segment",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "NumberState",
                            "label": "Stored account-number availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "datasetID": "41F90D88-4C35-57F3-96BA-52A3982BB738",
                    "dateFilterKey": "",
                    "dateFilterLastDays": 7,
                    "dateFilterPreset": "none",
                    "detailFieldLayout": [
                        {
                            "detailGroup": "Complete identity and current scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountNumber",
                            "label": "Complete stored account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Complete identity and current scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ACTDESCR",
                            "label": "Account description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Complete identity and current scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "NumberState",
                            "label": "Stored account-number availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Complete identity and current scope",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "Scope",
                            "label": "Current master context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Account role and classification",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "AccountTypeName",
                            "label": "Documented account type",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Account role and classification",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ACCTTYPE",
                            "label": "Stored account type code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Account role and classification",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "MNACSGMT",
                            "label": "Stored main-account segment",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Account role and classification",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ACCATNUM",
                            "label": "Stored account category code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent stored entry settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ActiveContext",
                            "label": "Stored active setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent stored entry settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ACTIVE",
                            "label": "Stored active flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent stored entry settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "EntryContext",
                            "label": "Stored manual/direct-entry setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Independent stored entry settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ACCTENTR",
                            "label": "Stored manual/direct-entry flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "PostingContext",
                            "label": "Income/balance mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "PSTNGTYP",
                            "label": "Stored posting type code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "BalanceSideContext",
                            "label": "Debit/credit mapping context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "TPCLBLNC",
                            "label": "Stored typical-balance code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "BALFRCLC",
                            "label": "Stored balance-calculation option code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Stored balance/reporting settings",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "Clear_Balance",
                            "label": "Stored Clear_Balance flag (1=yes)",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "detailLiveRefreshSeconds": 0,
                    "fixedFilters": [],
                    "id": "7049CE46-E7A9-526A-8144-4CCCA678448D",
                    "openFilters": [],
                    "pageSize": 100,
                    "requiresOpeningFilterSelection": false,
                    "showOnHome": false,
                    "sortRules": [
                        {
                            "direction": "ascending",
                            "id": "BF1B51DC-8E01-5DD8-97E6-A2638F5B15C6",
                            "key": "AccountNumber",
                            "type": "text"
                        }
                    ],
                    "subtitle": "",
                    "subtitleKey": "ACTDESCR",
                    "systemImage": "list.bullet.rectangle",
                    "title": "Current distribution account",
                    "titleKey": "AccountNumber"
                },
                {
                    "actions": [],
                    "badgeKey": "",
                    "cardEnrichments": [],
                    "cardFieldLayout": [
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentNumber",
                            "label": "Account segment position",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentDescription",
                            "label": "Current code description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "MainSegmentContext",
                            "label": "Current main-segment setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SettingState",
                            "label": "Segment setting availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "DescriptionState",
                            "label": "Code-description availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "datasetID": "1CA4D7C2-B45E-5414-BF9F-182BDF5E9775",
                    "dateFilterKey": "",
                    "dateFilterLastDays": 7,
                    "dateFilterPreset": "none",
                    "detailFieldLayout": [
                        {
                            "detailGroup": "Current account structure",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "ParentAccountNumber",
                            "label": "Current complete account number",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account structure",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentNumber",
                            "label": "Account segment position",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account structure",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentCode",
                            "label": "Complete stored segment code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account structure",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "FormattedSegmentCode",
                            "label": "Stored formatted-table segment code",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current account structure",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentScope",
                            "label": "Current structure context",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current segment definition",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentName",
                            "label": "Current segment name",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current segment definition",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "MainSegmentContext",
                            "label": "Current main-segment setting",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current segment definition",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SettingState",
                            "label": "Segment setting availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current code description",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "SegmentDescription",
                            "label": "Current code description",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        },
                        {
                            "detailGroup": "Current code description",
                            "detailRole": "information",
                            "isVisible": true,
                            "key": "DescriptionState",
                            "label": "Code-description availability",
                            "locationLabelKey": "",
                            "locationLongitudeKey": "",
                            "presentation": "text"
                        }
                    ],
                    "detailLiveRefreshSeconds": 0,
                    "fixedFilters": [],
                    "id": "176CC5CE-F137-5109-A01C-FD7F97E10C48",
                    "openFilters": [],
                    "pageSize": 100,
                    "requiresOpeningFilterSelection": false,
                    "showOnHome": false,
                    "sortRules": [
                        {
                            "direction": "ascending",
                            "id": "B7E6B71C-8B5F-521D-BDE2-32E81B9EF8E2",
                            "key": "SegmentNumber",
                            "type": "number"
                        }
                    ],
                    "subtitle": "",
                    "subtitleKey": "SegmentCode",
                    "systemImage": "list.bullet",
                    "title": "Distribution account segments",
                    "titleKey": "SegmentName"
                }
            ],
            "relations": [
                {
                    "childDatasetID": "41F90D88-4C35-57F3-96BA-52A3982BB738",
                    "childKey": "ParentDistributionKey",
                    "id": "7E6D119F-9CCA-559B-80D4-D57F09A7E6BE",
                    "name": "Current account",
                    "parentDatasetID": "87679410-CC5B-5A5B-BE76-92D773FFA01D",
                    "parentKey": "DistributionKey"
                },
                {
                    "childDatasetID": "1CA4D7C2-B45E-5414-BF9F-182BDF5E9775",
                    "childKey": "ParentAccountKey",
                    "id": "DD69CEDF-B5B9-5F50-A2AA-055F2E8C84AD",
                    "name": "Account segments",
                    "parentDatasetID": "41F90D88-4C35-57F3-96BA-52A3982BB738",
                    "parentKey": "AccountKey"
                }
            ],
            "widgets": []
        }
    },
    "format": "cifru-configuration-package",
    "formatVersion": 1,
    "manifest": {
        "applicationName": "Microsoft Dynamics GP",
        "configurationLanguages": [
            "en"
        ],
        "countries": [
            "US"
        ],
        "createdAt": "2026-10-09T00:00:00Z",
        "description": "UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION. For accountants, controllers and managers: choose a transaction-date period, then inspect posted GL distributions from open fiscal years. See the journal reference, complete source timestamp, independent debit/credit values and original source references. Open the current account dossier and every nonblank account segment on demand.\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 authorised SQL company source, one Home, three explicit read-only lists and two nested lazy buttons. Required inclusive start/exclusive end dates and optional journal, current account and source currency filters precede the 1,000-row root cap; each child has its own cap. No scheduled refresh, SUM, netting, currency conversion, writes, credentials, business rows, SELECT * or executable code is exported. Filters/caps do not guarantee completeness or cheap source scans.\n\nOwn Microsoft GL20000 source registration and AL staging fields are pinned to e7ed235bc979c0283306e9639ff7cd22ce78341d. Own GP Support documentation identifies DEBITAMT/CRDTAMNT as functional values and ORDBTAMT/ORCRDAMT as originating values in its documented GL20000 context. This is not installed SQL DDL, physical PK/FK, installed monetary storage, timezone, ledger partition, current edition, SQL Server execution or real ERP validation. Verify installed objects, types, identities, authorised permissions and interpretations with the import read test and known distributions.\n\nGL20000 is Open-year posted distributions, not Work drafts, GL30000 closed-year history, all fiscal years, RM/PM positions, a complete journal, accounting balance or bank statement. Fiscal years need not match calendar years. Journal/year/sequence is not a certified full journal key. NULL timestamps are excluded by the required date period. SERIES remains its original code; source master/document references do not imply customer, vendor, invoice or a verified foreign key.\n\nThe four native values retain precise source text, NULL, zero, tiny amounts, negatives and simultaneous debit/credit without netting, rounding or repairs. Local value sorting is textual, not numeric aggregation. Functional currency requires exactly one nonblank row in the WHOLE MC40000 setting, never a filtered unique-looking row or CURNCYID fallback. Originating context uses the distribution own CURNCYID, never the current partner default. No ISO currency or exchange rate is inferred. Revaluation distributions may have only functional values; they are not automatically corrupt.\n\nOnly a unique current Posting account (code 1) receives monetary currency contexts. Unit accounts (code 2) hold nonfinancial quantities, NOT money; their stored CURNCYID is not a unit measure. Allocation (3), unknown, missing or ambiguous current account types leave monetary contexts empty with a reason, while preserving all source values. Current account classification is not a certified historical journal classification.\n\nMissing/ambiguous current account metadata preserves the distribution, without first-match, deduplication or fanout. Complete account numbers require one GL00105 record with all eight segments agreeing NULL-aware; no guessed separator or truncation. Internal DEX_ROW_ID, ACTINDX and route/context keys remain hidden. NULL/duplicate distribution identity blocks the selected-period root read, counting duplicates across the whole source. Each child revalidates the original distribution, every selected source field (including the full timestamp and values), current account/formatting context and whole company currency state. Changed/removed/ambiguous parents block stale routes until reread. The nested segment route also revalidates its original GL parent, not just the account.\n\nCurrent account/segment labels and stored settings are not journal snapshots, inherited GP permissions or effective posting eligibility. Optional metadata absence is explicit. Binary Unicode comparisons preserve full codes, leading spaces/case/zeroes and remove only right padding; they do not certify installed collation or constraints.\n\nUse a separately authorised least-privilege SELECT reporting login to allowed company data, not sa/sysadmin, DYNGRP, DYNAMICS or BC staging. GP application passwords are transformed. Cifru filters are not access control. Accounting/source references can be confidential. No maintenance instructions from support articles are executed. Native screenshots use entirely fictional DEMO rows and synthetic transport, not live GP or SQL Server. Unofficial; not endorsed by Microsoft.\n\nPrimary GL20000 source: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPGL20000.Table.al\nOwn currency roles: https://community.dynamics.com/blogs/post/?postid=162eb501-091c-42b6-a13e-ad88eee63d82\nDistribution anomalies: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/financial-report-do-not-match-gl-trial-balance-report\nUnit account binding: https://learn.microsoft.com/en-us/troubleshoot/dynamics/gp/clear-beginning-balances-for-unit-accounts-in-general-ledger\nCurrent chart / segments: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPGL00100.Table.al\nWhole company currency setting: https://raw.githubusercontent.com/microsoft/ALAppExtensions/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridGP/app/src/Migration/GPTables/GPMC40000.Table.al",
        "licenseCode": "Cifru-Community-1.0",
        "minimumCifruVersion": "1.1.0",
        "minimumPlan": "pro",
        "packageID": "15D23DBC-5706-5831-9ECE-D956CCF527E0",
        "rootButtonCount": 1,
        "summary": "Period-filtered posted distribution dossiers, independent currency contexts and lazy current account/segment details.",
        "tags": [
            "Dynamics GP",
            "SQL Server",
            "Accounting",
            "General ledger",
            "Journal distributions",
            "Pro"
        ],
        "title": "Posted GL distributions, current account and segments — Pro"
    }
}

Tags

Reviews

There are no approved reviews for this version yet.

Write a review

Sign in to review

Similar templates

From the same application, then shared tags, in the same language and country.
Microsoft Dynamics GP

Current payable positions, vendor and address book — Pro

PROMicrosoft Dynamics GP

Chart of accounts and full segment dossiers — Pro

PROMicrosoft Dynamics GP

Customer dossier and address book — Pro

PROMicrosoft Dynamics GP

Vendor dossier and address book — Pro

PROMicrosoft Dynamics GP

Historical customer invoices, lines and current customer — Pro

PROMicrosoft Dynamics GP

Purchase orders, lines and current vendor — Pro

PRO