con esta consulta tengo lo mayores pero completos pero no he podido llegar a como modificarla para clasificar solo las no reconciliadas
SELECT TOP 100 PERCENT
(SELECT "CompnyName" FROM OADM) AS "NombreEmpresa",
ACT."FormatCode" AS "Cuenta",
ACT."AcctName" AS "Cuenta_Nombre",
ACT."GroupMask" AS "GrupoCta",
CASE WHEN ACT."GroupMask" < 4 THEN 'Balance' ELSE 'EE.RR.' END AS "GrupoEEFF",
YEAR(JED."RefDate") AS "Año",
MONTH(JED."RefDate") AS "Mes",
CAST(ROUND(JED."Debit", 2) AS Decimal(19,2)) AS "DebitoML",
CAST(ROUND(JED."Credit", 2) AS Decimal(19,2)) AS "CreditoML",
CAST(ROUND(JED."Debit", 2) - ROUND(JED."Credit", 2) AS Decimal(19,2)) AS "Resultado",
COALESCE(SNA."CardCode", '') AS "CtaCte",
COALESCE(SNA."CardName", '') AS "SocioNegocio",
COALESCE(SNA."LicTradNum", '') AS "Analisis",
JED."RefDate" AS "FechaCon",
JED."DueDate" AS "FechaVen",
JED."TaxDate" AS "FechaDoc",
JED."LineMemo" AS "Glosa",
CONCAT(COALESCE(JEC."FolioPref", ''), '-', LTRIM(CAST(COALESCE(JEC."FolioNum", 0) AS CHAR(12)))) AS "Folio",
COALESCE(JEC."FolioNum", 0) AS "FolioNum",
'MOVIMIENTO' AS "Tipo",
JED."TransId" AS "NumeroTransaccion",
-- Niveles de cuenta
L2."AcctCode" AS "ctaNivel2",
L2."AcctName" AS "ctaNivel2_Nombre",
L3."AcctCode" AS "ctaNivel3",
L3."AcctName" AS "ctaNivel3_Nombre",
L4."AcctCode" AS "ctaNivel4",
L4."AcctName" AS "ctaNivel4_Nombre",
L5."AcctCode" AS "ctaNivel5",
L5."AcctName" AS "ctaNivel5_Nombre",
-- Centro de Costo (Dimensión disponible)
COALESCE(CC2."OcrName", CC3."OcrName", CC4."OcrName", CC5."OcrName", '') AS "NombreCentroCosto",
-- Agregar la columna ProfitCode de la tabla JDT1
JED."ProfitCode" AS "ProfitCode",
-- Agregar el nombre del ProfitCode (relacionado con OOCR)
COALESCE(PC."OcrName", '') AS "NombreProfitCode" -- Buscando el nombre del ProfitCode en OOCR
FROM JDT1 JED
INNER JOIN OJDT JEC ON JED."TransId" = JEC."TransId"
LEFT JOIN OCRD SNA ON JED."ShortName" = SNA."CardCode"
LEFT JOIN OACT ACT ON JED."Account" = ACT."AcctCode"
-- Obtener niveles de cuenta usando FatherNum
LEFT JOIN OACT L2 ON ACT."FatherNum" = L2."AcctCode"
LEFT JOIN OACT L3 ON L2."FatherNum" = L3."AcctCode"
LEFT JOIN OACT L4 ON L3."FatherNum" = L4."AcctCode"
LEFT JOIN OACT L5 ON L4."FatherNum" = L5."AcctCode"
-- Obtener el centro de costo desde OOCR (usando las dimensiones disponibles)
LEFT JOIN OOCR CC2 ON JED."OcrCode2" = CC2."OcrCode"
LEFT JOIN OOCR CC3 ON JED."OcrCode3" = CC3."OcrCode"
LEFT JOIN OOCR CC4 ON JED."OcrCode4" = CC4."OcrCode"
LEFT JOIN OOCR CC5 ON JED."OcrCode5" = CC5."OcrCode"
-- Obtener el nombre del ProfitCode de la tabla OOCR
LEFT JOIN OOCR PC ON JED."ProfitCode" = PC."OcrCode"
WHERE YEAR(JED."RefDate") = YEAR(GETDATE())