Journal transactions and current account dossier — Pro
Date/company/ledger selection, original debit/credit, currency and period context; on-demand current account settings.
UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.
For accountants, controllers and managers: choose transaction-date period, company and ledger, find a journal reference, and inspect original account/subaccount, debit and credit values, fiscal period codes, native state values, project/task context and original cross-entry references. Open Current account only when needed to inspect its current description, class, category and accounting settings.
Why Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.
Pro: one SQL source, one Home button, two lists and one lazy current-account button. 43 journal and 20 current-account values in grouped Details, eight useful fields per card, Search, native date-period selection and mandatory company/ledger opening choices. Each live list is capped at 1,000 rows; no automatic refresh, complete-ledger total, all-account preload or measured performance. Identity checks may scan beyond the visible cap.
Own pinned Microsoft registration and declarations identify original GLTran and Account. The account code is copied into staging and G/L account identity; Microsoft historical views explicitly filter GL entries by Acct. Original transaction fields are transferred into history. This is documentary lookup context, not installed SQL DDL, PK/FK, permissions or a Batch join certificate. Logical keys are Module+BatNbr+LineNbr and Acct; do not invent company/ledger additions to a physical primary key. Blank/oversized keys, invalid integer line numbers and duplicate transaction identities fail closed. Company and ledger additionally guard the selected parent. Every one of its 40 selected original values is rechecked losslessly before the current-account lookup. Refresh the journal if it changed. Missing/ambiguous account records preserve original journal rows; the lookup returns nothing instead of picking a first match or multiplying rows.
DrAmt and CrAmt are separately identified as debit and credit in the Microsoft historical page. BaseCuryID, CuryId, CuryDrAmt/CuryCrAmt and stored rate fields remain separate original values. No ISO validation, assumed symbol, signed net balance, FX conversion, amount/quantity multiplication, aggregation, balanced batch, trial balance, income/expense classification or account balance is claimed. Zero, negative and absent values are preserved. Current Account.CuryId is a separately named current setting, not a replacement for original journal currencies.
Posted P is documented as historical migration selection, not a complete status or financial eligibility map. Posted/Rlsed/JrnlType/TranType/BalanceType and reversal values remain native codes. Active 0 inactive/nonzero active is current-account migration context only, not access control. Company is parent transaction context; no CpnyID column is invented on Account. Current account description/defaults are not their historical values at posting. No Batch join, AcctHist summary, destination opening-balance sign inversion or guessed account category is used.
Date-period selection concerns TranDate, not fiscal posting period, applied/service date or fiscal calendar boundaries. Original declarations use DateTime while migration buffers use Date; original values are retained, physical storage and time zone unverified. Fiscal period/year codes stay raw. Qty and Units are separately stored context, without a certified unit or conversion. Audit users, technical IDs, employee identifiers, payroll, bank, future and custom fields are omitted.
Documentary and adapted synthetic/native Cifru DEMO tests only. No real Dynamics SL/SQL Server installation or iOS validation. Check installed edition, dbo owner, business database, columns, SQL compatibility, indexes and read performance during import. Direct SQL does not inherit ERP roles; company/ledger/date filters and hidden keys are not ACL. Require administrator-scoped SELECT-only reporting access, not migration sysadmin/db_owner. Migration code is consulted, never executed. No credentials or live business data are embedded. Accounting data can be confidential. All native screenshots use fictional DEMO, not a live SL connection; eight images are representative, not exhaustive. Raw footage remains private pending its own review.
Unofficial, not Microsoft endorsed. Dynamics SL is a distinct legacy installed-base product with a July 2028 extended-support boundary, not a current top-three or no-mobile claim.
Original transaction declaration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/History/SLGLTran.Table.al
Original account declaration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Accounts/SLAccount.Table.al
Explicit account identity copy: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/SLTables/SLPopulateAccounts.Codeunit.al
Historical account filter: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/DynamicsSLHistoricalData/app/src/PageExt/SLHistChartofAccounts.PageExt.al
Journal labels and filters: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/DynamicsSLHistoricalData/app/src/Pages/SLHistGLTranEntries.Page.al
Source registration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/codeunits/SLHybridWizard.Codeunit.al
Screenshots
What this package creates
1 Home1 Details2 lists1 sources to map
- Home: Journal transaction dossier
- Details: Current account
- Sub-button: Current account
Sources are mapped locally and verified before applying.
Custom queriesPRO2 SQL
Custom queries are a PRO feature. Cifru repeats read-only validation against the local source before execution.
Statically verified read-only
$.components.workspaceSelection.datasets.0.sqlQuerySELECT m.Acct AS Acct,m.Sub AS Sub,RTRIM(CAST(m.CpnyID AS nvarchar(max))) AS CpnyID,RTRIM(CAST(m.LedgerID AS nvarchar(max))) AS LedgerID,m.Module AS Module,m.BatNbr AS BatNbr,m.LineNbr AS LineNbr,m.RefNbr AS RefNbr,m.ExtRefNbr AS ExtRefNbr,m.TranDesc AS TranDesc,m.TranDate AS TranDate,m.PerEnt AS PerEnt,m.PerPost AS PerPost,m.FiscYr AS FiscYr,m.JrnlType AS JrnlType,m.TranType AS TranType,m.Posted AS Posted,m.Rlsed AS Rlsed,m.BalanceType AS BalanceType,m.DrAmt AS DrAmt,m.CrAmt AS CrAmt,m.BaseCuryID AS BaseCuryID,m.CuryDrAmt AS CuryDrAmt,m.CuryCrAmt AS CuryCrAmt,m.CuryId AS CuryId,m.CuryEffDate AS CuryEffDate,m.CuryRate AS CuryRate,m.CuryMultDiv AS CuryMultDiv,m.CuryRateType AS CuryRateType,m.ProjectID AS ProjectID,m.TaskID AS TaskID,m.Qty AS Qty,m.Units AS Units,m.AppliedDate AS AppliedDate,m.ServiceDate AS ServiceDate,m.OrigAcct AS OrigAcct,m.OrigSub AS OrigSub,m.OrigCpnyID AS OrigCpnyID,m.OrigBatNbr AS OrigBatNbr,m.RevEntryOption AS RevEntryOption,CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS TransactionKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) AS TransactionContext,CASE WHEN (m.Acct IS NULL OR RTRIM(CAST(m.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))>20) THEN N'No usable original account code' WHEN (SELECT COUNT(1) FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) AND (probe.Acct IS NULL OR RTRIM(CAST(probe.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))>20)) THEN N'Unique current account on demand' WHEN EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max)))))) THEN N'Ambiguous / invalid current account' ELSE N'No current account record' END AS AccountAvailability,N'Separate stored debit/credit and currency codes; no FX or net balance' AS CurrencyScope,N'Original journal row; not an invoice, balanced batch or complete ledger total' AS TransactionScope FROM dbo.GLTran m WHERE NOT EXISTS (SELECT 1 FROM dbo.GLTran bad WHERE (bad.Module IS NULL OR RTRIM(CAST(bad.Module AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max))))>4) OR (bad.BatNbr IS NULL OR RTRIM(CAST(bad.BatNbr AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20) OR (bad.LedgerID IS NULL OR RTRIM(CAST(bad.LedgerID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.LedgerID AS nvarchar(max))))>20) OR TRY_CONVERT(int,bad.LineNbr) IS NULL OR NOT (RTRIM(CAST(bad.LineNbr AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.LineNbr AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max)))))) AND NOT EXISTS (SELECT 1 FROM dbo.GLTran bad GROUP BY CAST(RTRIM(CAST(bad.Module AS nvarchar(max))) AS nvarchar(2)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max)))),CAST(RTRIM(CAST(bad.BatNbr AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max)))),bad.LineNbr HAVING COUNT(1)<>1)
static read-only checks passed
Statically verified read-only
$.components.workspaceSelection.datasets.1.sqlQuerySELECT a.Acct AS Acct,a.Descr AS Descr,a.AcctType AS AcctType,a.Active AS Active,a.ClassID AS ClassID,a.Acct_Cat AS Acct_Cat,a.Acct_Cat_SW AS Acct_Cat_SW,a.ConsolAcct AS ConsolAcct,a.CuryId AS CuryId,a.SummPost AS SummPost,a.UnitofMeas AS UnitofMeas,a.Units_SW AS Units_SW,a.ValidateID AS ValidateID,a.RatioGrp AS RatioGrp,a.Employ_Sw AS Employ_Sw,CONCAT(CASE WHEN CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS nvarchar(max))),N':',CAST(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(a.Acct AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.Acct AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.Acct AS nvarchar(max))) AS nvarchar(max)),N';') END) AS AccountLookupKey,CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS TransactionKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) AS TransactionContext,RTRIM(CAST(m.CpnyID AS nvarchar(max))) AS ParentCompany,RTRIM(CAST(m.LedgerID AS nvarchar(max))) AS ParentLedger,m.RefNbr AS ParentReference,CASE WHEN a.Active IS NULL THEN N'Absent source value' WHEN a.Active=0 THEN N'Inactive in Microsoft migration' ELSE N'Active in Microsoft migration; not an access grant' END AS AccountStateContext,N'Current account settings, not the historic name/currency on the journal; company is parent context' AS AccountScope FROM dbo.Account a INNER JOIN dbo.GLTran m ON (RTRIM(CAST(a.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) WHERE NOT EXISTS (SELECT 1 FROM dbo.GLTran bad WHERE (bad.Module IS NULL OR RTRIM(CAST(bad.Module AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max))))>4) OR (bad.BatNbr IS NULL OR RTRIM(CAST(bad.BatNbr AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20) OR (bad.LedgerID IS NULL OR RTRIM(CAST(bad.LedgerID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.LedgerID AS nvarchar(max))))>20) OR TRY_CONVERT(int,bad.LineNbr) IS NULL OR NOT (RTRIM(CAST(bad.LineNbr AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.LineNbr AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max)))))) AND NOT EXISTS (SELECT 1 FROM dbo.GLTran bad GROUP BY CAST(RTRIM(CAST(bad.Module AS nvarchar(max))) AS nvarchar(2)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max)))),CAST(RTRIM(CAST(bad.BatNbr AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max)))),bad.LineNbr HAVING COUNT(1)<>1) AND (SELECT COUNT(1) FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) AND (probe.Acct IS NULL OR RTRIM(CAST(probe.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))>20)) AND NOT (m.Acct IS NULL OR RTRIM(CAST(m.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))>20) AND (CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:transaction COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END))=DATALENGTH(:transaction)) AND (CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:transactionContext COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END))=DATALENGTH(:transactionContext)) AND (RTRIM(CAST(m.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:company AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(m.CpnyID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:company AS nvarchar(max))))) AND (RTRIM(CAST(m.LedgerID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:ledger AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(m.LedgerID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:ledger AS nvarchar(max)))))
static read-only checks passed
View the JSON being importedcollapsed by default
{
"components": {
"sourceSlots": [
{
"displayName": "Dynamics SL — authorised SQL business database",
"id": "75E047FC-9511-5B13-AD4E-0A2781A93F9E",
"kind": "sqlServer",
"requiredObjects": [
"dbo.GLTran",
"dbo.Account"
],
"requiresCustomSQL": false
}
],
"workspaceSelection": {
"commonFields": [],
"datasets": [
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "descending",
"id": "D6853DF4-7245-57EF-9B4F-6CCE5D61CD5D",
"key": "TranDate",
"type": "date"
},
{
"direction": "ascending",
"id": "E090F16D-50C3-5228-91D7-2B1CBABB3A9D",
"key": "TransactionKey",
"type": "text"
}
],
"id": "D914D4F7-9916-5FDC-B436-FA452CCE1484",
"mappings": [
{
"commonFieldKey": "",
"key": "TransactionKey",
"label": "Internal complete original journal context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TransactionKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TransactionContext",
"label": "Internal complete original journal context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TransactionContext",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Acct",
"label": "Original account business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Acct",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Sub",
"label": "Original subaccount business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Sub",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CpnyID",
"label": "Transaction company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CpnyID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LedgerID",
"label": "Transaction ledger code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LedgerID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Module",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Module",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BatNbr",
"label": "Original batch number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BatNbr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "LineNbr",
"label": "Original journal line number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "LineNbr",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RefNbr",
"label": "Original reference number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "RefNbr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ExtRefNbr",
"label": "Original external reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ExtRefNbr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TranDesc",
"label": "Original transaction description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TranDesc",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TranDate",
"label": "Stored transaction date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TranDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "PerEnt",
"label": "Entered fiscal period code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PerEnt",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "PerPost",
"label": "Posting fiscal period code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "PerPost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "FiscYr",
"label": "Stored fiscal year code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "FiscYr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "JrnlType",
"label": "Native journal type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "JrnlType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TranType",
"label": "Native transaction type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TranType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Posted",
"label": "Native posted status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Posted",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Rlsed",
"label": "Native released value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Rlsed",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "BalanceType",
"label": "Native balance type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BalanceType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "DrAmt",
"label": "Stored debit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "DrAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CrAmt",
"label": "Stored credit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CrAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "BaseCuryID",
"label": "Stored base currency code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "BaseCuryID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CuryDrAmt",
"label": "Stored currency debit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryDrAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryCrAmt",
"label": "Stored currency credit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryCrAmt",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryId",
"label": "Stored transaction currency code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryId",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryEffDate",
"label": "Stored currency effective date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryEffDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryRate",
"label": "Stored exchange-rate value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryRate",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryMultDiv",
"label": "Native rate multiplier/divisor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryMultDiv",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryRateType",
"label": "Native exchange-rate type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryRateType",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ProjectID",
"label": "Original project business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ProjectID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TaskID",
"label": "Original task business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TaskID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Qty",
"label": "Stored quantity — unit not certified",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Qty",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Units",
"label": "Separate stored units value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Units",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AppliedDate",
"label": "Stored applied date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AppliedDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ServiceDate",
"label": "Stored service date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ServiceDate",
"type": "date",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrigAcct",
"label": "Source original-account reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OrigAcct",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrigSub",
"label": "Source original-subaccount reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OrigSub",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrigCpnyID",
"label": "Source original-company reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OrigCpnyID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "OrigBatNbr",
"label": "Source original-batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "OrigBatNbr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RevEntryOption",
"label": "Native reversing-entry option code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "RevEntryOption",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountAvailability",
"label": "Current account lookup availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AccountAvailability",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "CurrencyScope",
"label": "Separate stored currency value scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CurrencyScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TransactionScope",
"label": "Original transaction scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TransactionScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Journal transaction dossier",
"primaryKey": "TransactionKey",
"queryParameters": [],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"Acct",
"Sub",
"CpnyID",
"LedgerID",
"Module",
"BatNbr",
"LineNbr",
"RefNbr",
"ExtRefNbr",
"TranDesc",
"TranDate",
"PerEnt",
"PerPost",
"FiscYr",
"JrnlType",
"TranType",
"Posted",
"Rlsed",
"BalanceType",
"DrAmt",
"CrAmt",
"BaseCuryID",
"CuryDrAmt",
"CuryCrAmt",
"CuryId",
"CuryEffDate",
"CuryRate",
"CuryMultDiv",
"CuryRateType",
"ProjectID",
"TaskID",
"Qty",
"Units",
"AppliedDate",
"ServiceDate",
"OrigAcct",
"OrigSub",
"OrigCpnyID",
"OrigBatNbr",
"RevEntryOption",
"AccountAvailability",
"CurrencyScope",
"TransactionScope"
],
"sourceID": "75E047FC-9511-5B13-AD4E-0A2781A93F9E",
"sqlQuery": "SELECT m.Acct AS Acct,m.Sub AS Sub,RTRIM(CAST(m.CpnyID AS nvarchar(max))) AS CpnyID,RTRIM(CAST(m.LedgerID AS nvarchar(max))) AS LedgerID,m.Module AS Module,m.BatNbr AS BatNbr,m.LineNbr AS LineNbr,m.RefNbr AS RefNbr,m.ExtRefNbr AS ExtRefNbr,m.TranDesc AS TranDesc,m.TranDate AS TranDate,m.PerEnt AS PerEnt,m.PerPost AS PerPost,m.FiscYr AS FiscYr,m.JrnlType AS JrnlType,m.TranType AS TranType,m.Posted AS Posted,m.Rlsed AS Rlsed,m.BalanceType AS BalanceType,m.DrAmt AS DrAmt,m.CrAmt AS CrAmt,m.BaseCuryID AS BaseCuryID,m.CuryDrAmt AS CuryDrAmt,m.CuryCrAmt AS CuryCrAmt,m.CuryId AS CuryId,m.CuryEffDate AS CuryEffDate,m.CuryRate AS CuryRate,m.CuryMultDiv AS CuryMultDiv,m.CuryRateType AS CuryRateType,m.ProjectID AS ProjectID,m.TaskID AS TaskID,m.Qty AS Qty,m.Units AS Units,m.AppliedDate AS AppliedDate,m.ServiceDate AS ServiceDate,m.OrigAcct AS OrigAcct,m.OrigSub AS OrigSub,m.OrigCpnyID AS OrigCpnyID,m.OrigBatNbr AS OrigBatNbr,m.RevEntryOption AS RevEntryOption,CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS TransactionKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) AS TransactionContext,CASE WHEN (m.Acct IS NULL OR RTRIM(CAST(m.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))>20) THEN N'No usable original account code' WHEN (SELECT COUNT(1) FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) AND (probe.Acct IS NULL OR RTRIM(CAST(probe.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))>20)) THEN N'Unique current account on demand' WHEN EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max)))))) THEN N'Ambiguous / invalid current account' ELSE N'No current account record' END AS AccountAvailability,N'Separate stored debit/credit and currency codes; no FX or net balance' AS CurrencyScope,N'Original journal row; not an invoice, balanced batch or complete ledger total' AS TransactionScope FROM dbo.GLTran m WHERE NOT EXISTS (SELECT 1 FROM dbo.GLTran bad WHERE (bad.Module IS NULL OR RTRIM(CAST(bad.Module AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max))))>4) OR (bad.BatNbr IS NULL OR RTRIM(CAST(bad.BatNbr AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20) OR (bad.LedgerID IS NULL OR RTRIM(CAST(bad.LedgerID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.LedgerID AS nvarchar(max))))>20) OR TRY_CONVERT(int,bad.LineNbr) IS NULL OR NOT (RTRIM(CAST(bad.LineNbr AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.LineNbr AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max)))))) AND NOT EXISTS (SELECT 1 FROM dbo.GLTran bad GROUP BY CAST(RTRIM(CAST(bad.Module AS nvarchar(max))) AS nvarchar(2)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max)))),CAST(RTRIM(CAST(bad.BatNbr AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max)))),bad.LineNbr HAVING COUNT(1)<>1)",
"tableName": ""
},
{
"cacheMode": "live",
"calculatedFields": [],
"customQueryIntegrationName": "",
"endpointPath": "",
"fetchSortRules": [
{
"direction": "ascending",
"id": "EF1B5D57-9DE9-5E18-A006-364460BC1E07",
"key": "Acct",
"type": "text"
},
{
"direction": "ascending",
"id": "2E04CB5F-A7A4-5AE5-84DB-0D981EDE1FB1",
"key": "AccountLookupKey",
"type": "text"
}
],
"id": "7514CF6F-9445-558C-80FD-43CE5F825C64",
"mappings": [
{
"commonFieldKey": "",
"key": "AccountLookupKey",
"label": "Internal complete original journal context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AccountLookupKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TransactionKey",
"label": "Internal complete original journal context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TransactionKey",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "TransactionContext",
"label": "Internal complete original journal context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "TransactionContext",
"type": "text",
"visibleInDetail": false,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Acct",
"label": "Current account business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Acct",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Descr",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Descr",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AcctType",
"label": "Current account type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AcctType",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Active",
"label": "Stored current active value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Active",
"type": "number",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ClassID",
"label": "Current account class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ClassID",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Acct_Cat",
"label": "Current account category code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Acct_Cat",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Acct_Cat_SW",
"label": "Stored category switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Acct_Cat_SW",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ConsolAcct",
"label": "Current consolidation account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ConsolAcct",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "CuryId",
"label": "Current account currency setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "CuryId",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "SummPost",
"label": "Stored summary-posting code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "SummPost",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "UnitofMeas",
"label": "Current account unit setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "UnitofMeas",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "Units_SW",
"label": "Stored units switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Units_SW",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ValidateID",
"label": "Stored validation setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ValidateID",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "RatioGrp",
"label": "Current ratio-group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "RatioGrp",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "Employ_Sw",
"label": "Stored employee switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "Employ_Sw",
"type": "number",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "ParentCompany",
"label": "Parent transaction company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentCompany",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ParentLedger",
"label": "Parent transaction ledger code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentLedger",
"type": "text",
"visibleInDetail": true,
"visibleInList": true
},
{
"commonFieldKey": "",
"key": "ParentReference",
"label": "Parent transaction reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "ParentReference",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountStateContext",
"label": "Active value — migration context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AccountStateContext",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
},
{
"commonFieldKey": "",
"key": "AccountScope",
"label": "Current account record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text",
"sourceColumn": "AccountScope",
"type": "text",
"visibleInDetail": true,
"visibleInList": false
}
],
"maxRows": 1000,
"name": "Current account dossier",
"primaryKey": "AccountLookupKey",
"queryParameters": [
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "TransactionKey",
"id": "D9C6B019-51F6-5596-AB3A-565CB76BB628",
"name": "transaction",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "TransactionContext",
"id": "94853000-6786-5B39-AC5E-EFDE196FAF81",
"name": "transactionContext",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "CpnyID",
"id": "0D9557CA-B609-5A90-951F-9649DCBAEA3B",
"name": "company",
"source": "parentField",
"type": "text"
},
{
"constantValue": "",
"dayOffset": 0,
"fieldKey": "LedgerID",
"id": "45EA2880-96A9-5686-8FA9-BEF19FAE73AD",
"name": "ledger",
"source": "parentField",
"type": "text"
}
],
"refreshPolicy": {
"enabled": false,
"intervalMinutes": 60
},
"rootArrayPath": "",
"rowLimitEnabled": true,
"searchKeys": [
"Acct",
"Descr",
"AcctType",
"Active",
"ClassID",
"Acct_Cat",
"Acct_Cat_SW",
"ConsolAcct",
"CuryId",
"SummPost",
"UnitofMeas",
"Units_SW",
"ValidateID",
"RatioGrp",
"Employ_Sw",
"ParentCompany",
"ParentLedger",
"ParentReference",
"AccountStateContext",
"AccountScope"
],
"sourceID": "75E047FC-9511-5B13-AD4E-0A2781A93F9E",
"sqlQuery": "SELECT a.Acct AS Acct,a.Descr AS Descr,a.AcctType AS AcctType,a.Active AS Active,a.ClassID AS ClassID,a.Acct_Cat AS Acct_Cat,a.Acct_Cat_SW AS Acct_Cat_SW,a.ConsolAcct AS ConsolAcct,a.CuryId AS CuryId,a.SummPost AS SummPost,a.UnitofMeas AS UnitofMeas,a.Units_SW AS Units_SW,a.ValidateID AS ValidateID,a.RatioGrp AS RatioGrp,a.Employ_Sw AS Employ_Sw,CONCAT(CASE WHEN CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS nvarchar(max))),N':',CAST(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(a.Acct AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(a.Acct AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(a.Acct AS nvarchar(max))) AS nvarchar(max)),N';') END) AS AccountLookupKey,CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) AS TransactionKey,CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) AS TransactionContext,RTRIM(CAST(m.CpnyID AS nvarchar(max))) AS ParentCompany,RTRIM(CAST(m.LedgerID AS nvarchar(max))) AS ParentLedger,m.RefNbr AS ParentReference,CASE WHEN a.Active IS NULL THEN N'Absent source value' WHEN a.Active=0 THEN N'Inactive in Microsoft migration' ELSE N'Active in Microsoft migration; not an access grant' END AS AccountStateContext,N'Current account settings, not the historic name/currency on the journal; company is parent context' AS AccountScope FROM dbo.Account a INNER JOIN dbo.GLTran m ON (RTRIM(CAST(a.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(a.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) WHERE NOT EXISTS (SELECT 1 FROM dbo.GLTran bad WHERE (bad.Module IS NULL OR RTRIM(CAST(bad.Module AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max))))>4) OR (bad.BatNbr IS NULL OR RTRIM(CAST(bad.BatNbr AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max))))>20) OR (bad.CpnyID IS NULL OR RTRIM(CAST(bad.CpnyID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.CpnyID AS nvarchar(max))))>20) OR (bad.LedgerID IS NULL OR RTRIM(CAST(bad.LedgerID AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(bad.LedgerID AS nvarchar(max))))>20) OR TRY_CONVERT(int,bad.LineNbr) IS NULL OR NOT (RTRIM(CAST(bad.LineNbr AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(bad.LineNbr AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(TRY_CONVERT(int,bad.LineNbr) AS nvarchar(max)))))) AND NOT EXISTS (SELECT 1 FROM dbo.GLTran bad GROUP BY CAST(RTRIM(CAST(bad.Module AS nvarchar(max))) AS nvarchar(2)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.Module AS nvarchar(max)))),CAST(RTRIM(CAST(bad.BatNbr AS nvarchar(max))) AS nvarchar(10)) COLLATE Latin1_General_100_BIN2,DATALENGTH(RTRIM(CAST(bad.BatNbr AS nvarchar(max)))),bad.LineNbr HAVING COUNT(1)<>1) AND (SELECT COUNT(1) FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))))=1 AND NOT EXISTS (SELECT 1 FROM dbo.Account probe WHERE (RTRIM(CAST(probe.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(m.Acct AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))) AND (probe.Acct IS NULL OR RTRIM(CAST(probe.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(probe.Acct AS nvarchar(max))))>20)) AND NOT (m.Acct IS NULL OR RTRIM(CAST(m.Acct AS nvarchar(max)))=N'' OR DATALENGTH(RTRIM(CAST(m.Acct AS nvarchar(max))))>20) AND (CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:transaction COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN RTRIM(CAST(m.Module AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.Module AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.BatNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.BatNbr AS nvarchar(max))) AS nvarchar(max)),N';') END,CASE WHEN RTRIM(CAST(m.LineNbr AS nvarchar(max))) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max))),N':',CAST(RTRIM(CAST(m.LineNbr AS nvarchar(max))) AS nvarchar(max)),N';') END))=DATALENGTH(:transaction)) AND (CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END) COLLATE Latin1_General_100_BIN2=:transactionContext COLLATE Latin1_General_100_BIN2 AND DATALENGTH(CONCAT(CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Acct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Sub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LedgerID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Module),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.LineNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ExtRefNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDesc),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerEnt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.PerPost),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.FiscYr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.JrnlType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TranType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Posted),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Rlsed),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BalanceType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.DrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.BaseCuryID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryDrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryCrAmt),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryId),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryEffDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryMultDiv),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.CuryRateType),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ProjectID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.TaskID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Qty),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.Units),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.AppliedDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.ServiceDate),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigAcct),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigSub),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigCpnyID),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.OrigBatNbr),2) AS nvarchar(max)),N';') END,CASE WHEN CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) IS NULL THEN CAST(N'~;' AS nvarchar(max)) ELSE CONCAT(N'=',DATALENGTH(CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max))),N':',CAST(CONVERT(varchar(max),CONVERT(varbinary(max),m.RevEntryOption),2) AS nvarchar(max)),N';') END))=DATALENGTH(:transactionContext)) AND (RTRIM(CAST(m.CpnyID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:company AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(m.CpnyID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:company AS nvarchar(max))))) AND (RTRIM(CAST(m.LedgerID AS nvarchar(max))) COLLATE Latin1_General_100_BIN2=RTRIM(CAST(:ledger AS nvarchar(max))) COLLATE Latin1_General_100_BIN2 AND DATALENGTH(RTRIM(CAST(m.LedgerID AS nvarchar(max))))=DATALENGTH(RTRIM(CAST(:ledger AS nvarchar(max)))))",
"tableName": ""
}
],
"pages": [
{
"actions": [
{
"id": "45ACAC27-E467-51A4-B603-38778CD43193",
"kind": "showRelated",
"relatedInitiallyExpanded": false,
"relatedPresentation": "separate",
"relatedPreviewLimit": 12,
"relatedRowStyle": "cards",
"relatedShowsCount": true,
"relationID": "86F602AA-47D1-5FA3-959F-63B7F64C347D",
"systemImage": "list.bullet.rectangle",
"targetDatasetID": "7514CF6F-9445-558C-80FD-43CE5F825C64",
"title": "Current account",
"urlKey": ""
}
],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Acct",
"label": "Account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Sub",
"label": "Subaccount code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "TranDate",
"label": "Stored date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "DrAmt",
"label": "Stored debit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CrAmt",
"label": "Stored credit",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "BaseCuryID",
"label": "Base currency code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Posted",
"label": "Posted code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AccountAvailability",
"label": "Current account",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "D914D4F7-9916-5FDC-B436-FA452CCE1484",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "TranDesc",
"label": "Original transaction description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "RefNbr",
"label": "Original reference number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "ExtRefNbr",
"label": "Original external reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "Module",
"label": "Source module code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "BatNbr",
"label": "Original batch number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "LineNbr",
"label": "Original journal line number",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "CpnyID",
"label": "Transaction company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "LedgerID",
"label": "Transaction ledger code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "Acct",
"label": "Original account business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original journal identity",
"detailRole": "information",
"isVisible": true,
"key": "Sub",
"label": "Original subaccount business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "TranDate",
"label": "Stored transaction date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "AppliedDate",
"label": "Stored applied date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "ServiceDate",
"label": "Stored service date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "PerEnt",
"label": "Entered fiscal period code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "PerPost",
"label": "Posting fiscal period code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored dates and separate fiscal period codes",
"detailRole": "information",
"isVisible": true,
"key": "FiscYr",
"label": "Stored fiscal year code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "JrnlType",
"label": "Native journal type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "TranType",
"label": "Native transaction type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "Posted",
"label": "Native posted status code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "Rlsed",
"label": "Native released value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "BalanceType",
"label": "Native balance type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Native journal and posting values",
"detailRole": "information",
"isVisible": true,
"key": "RevEntryOption",
"label": "Native reversing-entry option code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "DrAmt",
"label": "Stored debit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "CrAmt",
"label": "Stored credit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "BaseCuryID",
"label": "Stored base currency code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "CuryDrAmt",
"label": "Stored currency debit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "CuryCrAmt",
"label": "Stored currency credit amount",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Separate debit and credit values",
"detailRole": "information",
"isVisible": true,
"key": "CuryId",
"label": "Stored transaction currency code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored currency context — no conversion",
"detailRole": "information",
"isVisible": true,
"key": "CuryEffDate",
"label": "Stored currency effective date",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored currency context — no conversion",
"detailRole": "information",
"isVisible": true,
"key": "CuryRate",
"label": "Stored exchange-rate value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored currency context — no conversion",
"detailRole": "information",
"isVisible": true,
"key": "CuryMultDiv",
"label": "Native rate multiplier/divisor code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored currency context — no conversion",
"detailRole": "information",
"isVisible": true,
"key": "CuryRateType",
"label": "Native exchange-rate type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Stored currency context — no conversion",
"detailRole": "information",
"isVisible": true,
"key": "CurrencyScope",
"label": "Separate stored currency value scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original project and quantity context",
"detailRole": "information",
"isVisible": true,
"key": "ProjectID",
"label": "Original project business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original project and quantity context",
"detailRole": "information",
"isVisible": true,
"key": "TaskID",
"label": "Original task business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original project and quantity context",
"detailRole": "information",
"isVisible": true,
"key": "Qty",
"label": "Stored quantity — unit not certified",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original project and quantity context",
"detailRole": "information",
"isVisible": true,
"key": "Units",
"label": "Separate stored units value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original cross-entry references",
"detailRole": "information",
"isVisible": true,
"key": "OrigAcct",
"label": "Source original-account reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original cross-entry references",
"detailRole": "information",
"isVisible": true,
"key": "OrigSub",
"label": "Source original-subaccount reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original cross-entry references",
"detailRole": "information",
"isVisible": true,
"key": "OrigCpnyID",
"label": "Source original-company reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Original cross-entry references",
"detailRole": "information",
"isVisible": true,
"key": "OrigBatNbr",
"label": "Source original-batch reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "On-demand lookup and scope",
"detailRole": "information",
"isVisible": true,
"key": "AccountAvailability",
"label": "Current account lookup availability",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "On-demand lookup and scope",
"detailRole": "information",
"isVisible": true,
"key": "TransactionScope",
"label": "Original transaction scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "9C870BA7-D50B-5E69-90D1-704766957DBC",
"openFilters": [
{
"datePeriodOptions": [
"today",
"currentMonth",
"last7Days",
"last30Days",
"last90Days"
],
"id": "1A1CC55B-0BDC-5C28-B985-E40FB2B5600D",
"includeAllOption": false,
"key": "TranDate",
"title": "Transaction-date period",
"type": "date"
},
{
"id": "6C289EEA-BF42-5BE7-A691-557C9389CF27",
"includeAllOption": false,
"key": "CpnyID",
"title": "Company",
"type": "text"
},
{
"id": "4779CA89-8572-50DA-AF0A-1532AC822A7A",
"includeAllOption": false,
"key": "LedgerID",
"title": "Ledger",
"type": "text"
}
],
"pageSize": 100,
"requiresOpeningFilterSelection": true,
"showOnHome": true,
"sortRules": [
{
"direction": "descending",
"id": "D6853DF4-7245-57EF-9B4F-6CCE5D61CD5D",
"key": "TranDate",
"type": "date"
},
{
"direction": "ascending",
"id": "E090F16D-50C3-5228-91D7-2B1CBABB3A9D",
"key": "TransactionKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "RefNbr",
"systemImage": "doc.text",
"title": "Journal transaction dossier",
"titleKey": "TranDesc"
},
{
"actions": [],
"badgeKey": "",
"cardEnrichments": [],
"cardFieldLayout": [
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "AcctType",
"label": "Account type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Active",
"label": "Stored active value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ClassID",
"label": "Class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "Acct_Cat",
"label": "Category code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "CuryId",
"label": "Account currency setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "UnitofMeas",
"label": "Unit setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ParentCompany",
"label": "Parent company",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "",
"detailRole": "information",
"isVisible": true,
"key": "ParentLedger",
"label": "Parent ledger",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"datasetID": "7514CF6F-9445-558C-80FD-43CE5F825C64",
"dateFilterKey": "",
"dateFilterLastDays": 7,
"dateFilterPreset": "none",
"detailFieldLayout": [
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "Descr",
"label": "Current account description",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "Acct",
"label": "Current account business code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "AcctType",
"label": "Current account type code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "Active",
"label": "Stored current active value",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "AccountStateContext",
"label": "Active value — migration context",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "ClassID",
"label": "Current account class code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "Acct_Cat",
"label": "Current account category code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current account identity",
"detailRole": "information",
"isVisible": true,
"key": "Acct_Cat_SW",
"label": "Stored category switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "ConsolAcct",
"label": "Current consolidation account code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "CuryId",
"label": "Current account currency setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "SummPost",
"label": "Stored summary-posting code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "UnitofMeas",
"label": "Current account unit setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "Units_SW",
"label": "Stored units switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "ValidateID",
"label": "Stored validation setting",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "RatioGrp",
"label": "Current ratio-group code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Current accounting defaults — native values",
"detailRole": "information",
"isVisible": true,
"key": "Employ_Sw",
"label": "Stored employee switch",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Parent transaction and lookup scope",
"detailRole": "information",
"isVisible": true,
"key": "ParentCompany",
"label": "Parent transaction company code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Parent transaction and lookup scope",
"detailRole": "information",
"isVisible": true,
"key": "ParentLedger",
"label": "Parent transaction ledger code",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Parent transaction and lookup scope",
"detailRole": "information",
"isVisible": true,
"key": "ParentReference",
"label": "Parent transaction reference",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
},
{
"detailGroup": "Parent transaction and lookup scope",
"detailRole": "information",
"isVisible": true,
"key": "AccountScope",
"label": "Current account record scope",
"locationLabelKey": "",
"locationLongitudeKey": "",
"presentation": "text"
}
],
"detailLiveRefreshSeconds": 0,
"fixedFilters": [],
"id": "3D357517-73B3-5400-A8AB-D88B942A04B5",
"openFilters": [],
"pageSize": 100,
"requiresOpeningFilterSelection": false,
"showOnHome": false,
"sortRules": [
{
"direction": "ascending",
"id": "EF1B5D57-9DE9-5E18-A006-364460BC1E07",
"key": "Acct",
"type": "text"
},
{
"direction": "ascending",
"id": "2E04CB5F-A7A4-5AE5-84DB-0D981EDE1FB1",
"key": "AccountLookupKey",
"type": "text"
}
],
"subtitle": "",
"subtitleKey": "Acct",
"systemImage": "list.bullet",
"title": "Current account",
"titleKey": "Descr"
}
],
"relations": [
{
"childDatasetID": "7514CF6F-9445-558C-80FD-43CE5F825C64",
"childKey": "TransactionKey",
"id": "86F602AA-47D1-5FA3-959F-63B7F64C347D",
"name": "Current account",
"parentDatasetID": "D914D4F7-9916-5FDC-B436-FA452CCE1484",
"parentKey": "TransactionKey"
}
],
"widgets": []
}
},
"format": "cifru-configuration-package",
"formatVersion": 1,
"manifest": {
"applicationName": "Microsoft Dynamics SL",
"configurationLanguages": [
"en"
],
"countries": [
"US"
],
"createdAt": "2026-10-10T00:00:00Z",
"description": "UNOFFICIAL — NOT VALIDATED ON A REAL ERP INSTALLATION.\n\nFor accountants, controllers and managers: choose transaction-date period, company and ledger, find a journal reference, and inspect original account/subaccount, debit and credit values, fiscal period codes, native state values, project/task context and original cross-entry references. Open Current account only when needed to inspect its current description, class, category and accounting settings.\n\nWhy Cifru? Adapt configurations to the way you work. Choose the fields, filters and details you need, and bring information to your phone that may not be available in your business software’s own mobile app. Available options depend on the data exposed by your authorized source and your Cifru plan.\n\nPro: one SQL source, one Home button, two lists and one lazy current-account button. 43 journal and 20 current-account values in grouped Details, eight useful fields per card, Search, native date-period selection and mandatory company/ledger opening choices. Each live list is capped at 1,000 rows; no automatic refresh, complete-ledger total, all-account preload or measured performance. Identity checks may scan beyond the visible cap.\n\nOwn pinned Microsoft registration and declarations identify original GLTran and Account. The account code is copied into staging and G/L account identity; Microsoft historical views explicitly filter GL entries by Acct. Original transaction fields are transferred into history. This is documentary lookup context, not installed SQL DDL, PK/FK, permissions or a Batch join certificate. Logical keys are Module+BatNbr+LineNbr and Acct; do not invent company/ledger additions to a physical primary key. Blank/oversized keys, invalid integer line numbers and duplicate transaction identities fail closed. Company and ledger additionally guard the selected parent. Every one of its 40 selected original values is rechecked losslessly before the current-account lookup. Refresh the journal if it changed. Missing/ambiguous account records preserve original journal rows; the lookup returns nothing instead of picking a first match or multiplying rows.\n\nDrAmt and CrAmt are separately identified as debit and credit in the Microsoft historical page. BaseCuryID, CuryId, CuryDrAmt/CuryCrAmt and stored rate fields remain separate original values. No ISO validation, assumed symbol, signed net balance, FX conversion, amount/quantity multiplication, aggregation, balanced batch, trial balance, income/expense classification or account balance is claimed. Zero, negative and absent values are preserved. Current Account.CuryId is a separately named current setting, not a replacement for original journal currencies.\n\nPosted P is documented as historical migration selection, not a complete status or financial eligibility map. Posted/Rlsed/JrnlType/TranType/BalanceType and reversal values remain native codes. Active 0 inactive/nonzero active is current-account migration context only, not access control. Company is parent transaction context; no CpnyID column is invented on Account. Current account description/defaults are not their historical values at posting. No Batch join, AcctHist summary, destination opening-balance sign inversion or guessed account category is used.\n\nDate-period selection concerns TranDate, not fiscal posting period, applied/service date or fiscal calendar boundaries. Original declarations use DateTime while migration buffers use Date; original values are retained, physical storage and time zone unverified. Fiscal period/year codes stay raw. Qty and Units are separately stored context, without a certified unit or conversion. Audit users, technical IDs, employee identifiers, payroll, bank, future and custom fields are omitted.\n\nDocumentary and adapted synthetic/native Cifru DEMO tests only. No real Dynamics SL/SQL Server installation or iOS validation. Check installed edition, dbo owner, business database, columns, SQL compatibility, indexes and read performance during import. Direct SQL does not inherit ERP roles; company/ledger/date filters and hidden keys are not ACL. Require administrator-scoped SELECT-only reporting access, not migration sysadmin/db_owner. Migration code is consulted, never executed. No credentials or live business data are embedded. Accounting data can be confidential. All native screenshots use fictional DEMO, not a live SL connection; eight images are representative, not exhaustive. Raw footage remains private pending its own review.\n\nUnofficial, not Microsoft endorsed. Dynamics SL is a distinct legacy installed-base product with a July 2028 extended-support boundary, not a current top-three or no-mobile claim.\n\nOriginal transaction declaration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/History/SLGLTran.Table.al\nOriginal account declaration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/Accounts/SLAccount.Table.al\nExplicit account identity copy: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/Migration/SLTables/SLPopulateAccounts.Codeunit.al\nHistorical account filter: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/DynamicsSLHistoricalData/app/src/PageExt/SLHistChartofAccounts.PageExt.al\nJournal labels and filters: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/DynamicsSLHistoricalData/app/src/Pages/SLHistGLTranEntries.Page.al\nSource registration: https://github.com/microsoft/ALAppExtensions/blob/e7ed235bc979c0283306e9639ff7cd22ce78341d/Apps/W1/HybridSL/app/src/codeunits/SLHybridWizard.Codeunit.al",
"licenseCode": "Cifru-Community-1.0",
"minimumCifruVersion": "1.1.0",
"minimumPlan": "pro",
"packageID": "FFF7E800-3B55-5F2C-B687-8A3D784A355E",
"rootButtonCount": 1,
"summary": "Date/company/ledger selection, original debit/credit, currency and period context; on-demand current account settings.",
"tags": [
"Dynamics SL",
"SQL Server",
"Accounting",
"General ledger",
"Journal",
"Accounts",
"Pro"
],
"title": "Journal transactions and current account dossier — Pro"
}
}