Problemas generando el query de Balance

[jeriverod]

Hola gente, saludos a todos.

Vengo a realizar una consulta, algo rebuscada pero estoy trabajando en ello.

Estoy tratando de generar el query de balance en el B1. Como he leído, este query se debe generar de otra manera, ya que es un proceso interno. Generé el mismo usando el SQL Profiller.

Dentro de tooooooodas las consultas esta es una.

SELECT T0.[AcctCode] , T0.[AcctName] , T0.[CurrTotal] , T0.[EndTotal] , T0.[Finanse] , T0.[Groups] , T0.[Budget] , T0.[Frozen] , T0.[Free_2] , T0.[Postable] , T0.[Fixed] , T0.[Levels] , T0.[ExportCode] , T0.[GrpLine] , T0.[FatherNum] , T0.[AccntntCod] , T0.[CashBox] , T0.[GroupMask] , T0.[RateTrans] , T0.[TaxIncome] , T0.[ExmIncome] , T0.[ExtrMatch] , T0.[IntrMatch] , T0.[ActType] , T0.[Transfered] , T0.[BlncTrnsfr] , T0.[OverType] , T0.[OverCode] , T0.[SysMatch] , T0.[PrevYear] , T0.[ActCurr] , T0.[RateDifAct] , T0.[SysTotal] , T0.[FcTotal] , T0.[Protected] , T0.[RealAcct] , T0.[Advance] , T0.[CreateDate] , T0.[UpdateDate] , T0.[FrgnName] , T0.[Details] , T0.[ExtraSum] , T0.[Project] , T0.[RevalMatch] , T0.[DataSource] , T0.[LocMth] , T0.[MTHCounter] , T0.[BNKCounter] , T0.[UserSign] , T0.[LocManTran] , T0.[LogInstanc] , T0.[ObjType] , T0.[ValidFor] , T0.[ValidFrom] , T0.[ValidTo] , T0.[ValidComm] , T0.[FrozenFor] , T0.[FrozenFrom] , T0.[FrozenTo] , T0.[FrozenComm] , T0.[Counter] , T0.[Segment_0] , T0.[Segment_1] , T0.[Segment_2] , T0.[Segment_3] , T0.[Segment_4] , T0.[Segment_5] , T0.[Segment_6] , T0.[Segment_7] , T0.[Segment_8] , T0.[Segment_9] , T0.[FormatCode] , T0.[CfwRlvnt] , T0.[ExchRate] , T0.[RevalAcct] , T0.[LastRevBal] , T0.[LastRevDat] , T0.[DfltVat] , T0.[VatChange] , T0.[Category] , T0.[TransCode] , T0.[OverCode5] , T0.[OverCode2] , T0.[OverCode3] , T0.[OverCode4] , T0.[DfltTax] , T0.[TaxPostAcc] , T0.[AcctStrLe] , T0.[MeaUnit] , T0.[BalDirect] , T0.[UserSign2] , T0.[PlngLevel] , T0.[MultiLink] , T0.[PrjRelvnt] , T0.[Dim1Relvnt] , T0.[Dim2Relvnt] , T0.[Dim3Relvnt] , T0.[Dim4Relvnt] , T0.[Dim5Relvnt] , T0.[AccrualTyp] , T0.[DatevAcct] , T0.[DatevAutoA] , T0.[DatevFirst] , T0.[SnapShotId] , T0.[PCN874Rpt] , T0.[SCAdjust] , T0.[BPLId] , T0.[BPLName] , T0.[SubLedgerN] , T0.[VATRegNum] , T0.[ActId] , T0.[ClosingAcc] , T0.[PurpCode] , T0.[RefCode] , T0.[BlocManPos] , T0.[U_HBT_Tercero] , T0.[U_HBT_TipoCta] , T0.[U_IFRS_Taxonomia] , T0.[U_IFRS_Politica] , T0.[U_IFRS_Tipo]  FROM [dbo].[OACT] T0 WHERE T0.[GrpLine] <> (@P1)   ORDER BY T0.[GroupMask],T0.[GrpLine]',N'@P1 int',0

Yo lo arregle para usarlo desde SQL Server 2014 y lo tengo de esta manera:

SELECT T0.[AcctCode] , T0.[AcctName] , T0.[CurrTotal] , T0.[EndTotal] , T0.[Finanse] , T0.[Groups] , T0.[Budget] , T0.[Frozen] , T0.[Free_2] , T0.[Postable] , T0.[Fixed] , T0.[Levels] , T0.[ExportCode] , T0.[GrpLine] , T0.[FatherNum] , T0.[AccntntCod] , T0.[CashBox] , T0.[GroupMask] , T0.[RateTrans] , T0.[TaxIncome] , T0.[ExmIncome] , T0.[ExtrMatch] , T0.[IntrMatch] , T0.[ActType] , T0.[Transfered] , T0.[BlncTrnsfr] , T0.[OverType] , T0.[OverCode] , T0.[SysMatch] , T0.[PrevYear] , T0.[ActCurr] , T0.[RateDifAct] , T0.[SysTotal] , T0.[FcTotal] , T0.[Protected] , T0.[RealAcct] , T0.[Advance] , T0.[CreateDate] , T0.[UpdateDate] , T0.[FrgnName] , T0.[Details] , T0.[ExtraSum] , T0.[Project] , T0.[RevalMatch] , T0.[DataSource] , T0.[LocMth] , T0.[MTHCounter] , T0.[BNKCounter] , T0.[UserSign] , T0.[LocManTran] , T0.[LogInstanc] , T0.[ObjType] , T0.[ValidFor] , T0.[ValidFrom] , T0.[ValidTo] , T0.[ValidComm] , T0.[FrozenFor] , T0.[FrozenFrom] , T0.[FrozenTo] , T0.[FrozenComm] , T0.[Counter] , T0.[Segment_0] , T0.[Segment_1] , T0.[Segment_2] , T0.[Segment_3] , T0.[Segment_4] , T0.[Segment_5] , T0.[Segment_6] , T0.[Segment_7] , T0.[Segment_8] , T0.[Segment_9] , T0.[FormatCode] , T0.[CfwRlvnt] , T0.[ExchRate] , T0.[RevalAcct] , T0.[LastRevBal] , T0.[LastRevDat] , T0.[DfltVat] , T0.[VatChange] , T0.[Category] , T0.[TransCode] , T0.[OverCode5] , T0.[OverCode2] , T0.[OverCode3] , T0.[OverCode4] , T0.[DfltTax] , T0.[TaxPostAcc] , T0.[AcctStrLe] , T0.[MeaUnit] , T0.[BalDirect] , T0.[UserSign2] , T0.[PlngLevel] , T0.[MultiLink] , T0.[PrjRelvnt] , T0.[Dim1Relvnt] , T0.[Dim2Relvnt] , T0.[Dim3Relvnt] , T0.[Dim4Relvnt] , T0.[Dim5Relvnt] , T0.[AccrualTyp] , T0.[DatevAcct] , T0.[DatevAutoA] , T0.[DatevFirst] , T0.[SnapShotId] , T0.[PCN874Rpt] , T0.[SCAdjust] , T0.[BPLId] , T0.[BPLName] , T0.[SubLedgerN] , T0.[VATRegNum] , T0.[ActId] , T0.[ClosingAcc] , T0.[PurpCode] , T0.[RefCode] , T0.[BlocManPos] , T0.[U_HBT_Tercero] , T0.[U_HBT_TipoCta] , T0.[U_IFRS_Taxonomia] , T0.[U_IFRS_Politica] , T0.[U_IFRS_Tipo]  
FROM [dbo].[OACT] T0 
ORDER BY T0.[GroupMask],T0.[GrpLine]

Pero los datos no me están coincidiendo con lo que me muestra el reporte. Posteriormente me dediqué a generar un reporte que enlace los datos que deseo teniendo el siguiente query:

select t0.reconnum, t0.account, t4.AcctName, t0.transid, t3.refdate,sum(T0.reconsum), sum(T0.reconsumfc)as reconsumfc, sum(T0.reconsumsc) as reconsumsc, sum(T1.Total) as total, sum(t2.LocTotal) AS Loctotal, sum(t2.FcTotal) AS FCtotal, sum(t2.SysTotal) AS Systotal,
SUM(T3.Debit) AS Debit, SUM(T3.Credit) AS credit, SUM(T3.Syscred) AS Syscred, SUM(T3.SYSDeb)AS sysdeb, SUM(T3.FCDebit) as fcdebit, SUM(T3.FCCredit) as fccredit, SUM(T4.CurrTotal)as currtotal, SUM(T4.EndTotal) as endtotal
from ITR1 T0  
INNER JOIN OITR T1 ON T0.ReconNum=T1.ReconNum 
INNER JOIN OJDT T2 ON T0.TransId=T2.TransId
INNER JOIN JDT1 T3 ON T0.Account=T3.Account
INNER JOIN OACT T4 ON T0.Account=T4.AcctCode

WHERE YEAR(T3.RefDate)='2018' AND MONTH(T3.RefDate)='01' AND T3.ACCOUNT IN ('110510005','111005008')
GROUP BY t0.reconnum, t0.account, t4.AcctName, t0.transid, t3.RefDate

Lástimosamente, este tampoco me genera los valores que se ven en el reporte de B1. Por lo tanto mi consulta es la siguiente. Hay alguna condición implícita que no me permita mostrar la información como se ve en el reporte? No se sí haya algo que ver entre lo que es crédito y débito. Igualmente voy a agregar una parte de las pantallas del reporte para ver si pueden darme una pista.

Gracias a todos por la ayuda que me puedan brindar.

[CJPG]

Hola jeriverod porque no lo simplificas te doy un ejemplo yo lo probé y si me muestra la información según el balance de saldos solo modificalo para que lo puedas visualizar como lo necesitas.

Select T0.FormatCode, T0.AcctName, (sum(T1.Debit) - sum(T1.Credit))[Saldo]
From OACT T0 inner join
JDT1 T1 on T0.AcctCode = T1.Account
Where T0.FormatCode = ‘155030100’ and T1.DueDate ='20180223’
Group By T0.FormatCode, T0.AcctName

Espero te sea de ayuda.

Saludos

[jeriverod]

Muchas gracias por tu respuesta amigo. Ya generé esta consulta:

`SELECT  T2.[FatherNum], T2.AcctCode, T2.AcctName, T0.TaxDate, SUM(T1.Debit - T1.Credit) AS 'Balance L', SUM(T1.FCDebit-T1.FCCredit) AS 'Balance E'

FROM OJDT T0

INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]

INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]

INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]

WHERE T2.AcctCode IN ('110510005','111005005','111005008','124505005','124505006','124505007','130505005','130505006','130510005',
'133005005','133005010','136595005','138020005','143505005','143505006','143505007','143505008','143505009','143505010','146505005',
'135515003','135515005','135517005','135518005','135518006','135518008','135519025','135519050','135520010','135520011','135520012',
'135530005','154005021','154005030','154005031','159220005','159235005','159235090','15900301','15900601','132510005','220505005',
'221005005','233525005','233540005','233545005','233555005','233555005','233595005','236505005','236515005','236515006','236520005',
'236525005','236525006','236525015','236525031','236525045','236530005','236540003','236540006','236575005','236575010','236810005',
'236810007','236810008','236810009','236810011','236920010','240405005','240810010','240810013','240810015','240820010','240820015',
'240820014','240820027','240820056','241205005','251510005','252510006','261005005','261010005','261015005','261020005','289505005',
'280505005','269595005','269595010','210510005','210510006','210510007','211505005','219505005','281005005','235505005','310505005',
'310510005','320505005','330505005','370505005','371005005','371505005'/*N*/)--WHERE YEAR(T0.TaxDate)='2018'

GROUP BY T2.AcctCode, T2.AcctName, T0.TaxDate, T2.[FatherNum]-- 
ORDER BY T2.[FatherNum]
`

Ahora mi detalle es el siguiente. Debo introducir un valor que es el periodo de ganancias. (

Imágen adjunta) El cual proviene de otro query que es de ganancias y pérdidas. Ese periodo de ganancias, se estructura de la siguiente manera: Ventas-Costo de Venta+ Ganancia Bruta.

Ese query de ganancias y pérdidas me está dando algunos problemas porque necesito que me muestre los números de cuentas y no lo he logrado. Para esto ando usando este query:

SELECT 
P.[FatherNum],
    P.[Cuenta],
    P.[Nombre],
    P.[CCosto],
    [1] as [Ene],
    [2] as [Feb],
    [3] as [Mar],
    [4] as [Abr],
    [5] as [May],
    [6] as [Jun],
    [7] as [Jul],
    [8] as [Ago],
    [9] as [Sep],
    [10] as [Oct],
    [11] as [Nov],
    [12] as [Dic]
FROM (
    SELECT
	T1.[FatherNum],
        T0.Account AS Cuenta,
        T1.AcctName AS Nombre,
        T2.PrcName AS CCosto,
        MONTH(T0.RefDate)'Month',
        SUM(T0.Credit-T0.Debit)'CargoAbono'
		
    FROM dbo.JDT1 T0
    INNER JOIN dbo.OACT T1 ON T1.AcctCode=T0.Account
    LEFT JOIN dbo.OPRC T2 ON T2.PrcCode=T0.ProfitCode
	INNER JOIN dbo.OACT T3 ON T3.[AcctCode] = T1.[FatherNum]
    WHERE YEAR(T0.RefDate)=YEAR(T0.RefDate) AND T1.GroupMask BETWEEN 6 AND 8 --AND T0.Account IN ('413595005','413595006','413595007','413595008','413595009','413595100')

    GROUP BY T0.Account, T1.AcctName, T2.PrcName,MONTH(T0.RefDate),T1.[FatherNum]
) P
PIVOT (
    SUM(CargoAbono)
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) P
ORDER BY P.[Cuenta], P.[CCosto]

Muchas gracias por la ayuda que me puedas brindar.

Seguiré intentando.

Saludos.

[jeriverod]

Hola a todos. Ya conseguí de donde viene el valor de Periodo de Ganancias. Ahora lo que ando buscando de generar es un query de ganancias y pérdidas. Por muy básico que sea, agradecería la ayuda que me puedan brindar. Al finalizar esto adjuntare mi query para ayudar a la comunidad.

Les estoy agradecido por la ayuda que me puedan brindar.

Saludos.

[oscarezh]

Hola amigos de @businessone alguien con un ayuda por acá o @jeriverod ya lo resolvió?

Saludos a tod@s

[Oscar1900]

Buen día @jeriverod prueba este reporte

SELECT DISTINCT
T0.FormatCode,
null ‘Título’,
case
when T0.[GroupMask] like ‘4%’ then ‘1. Ventas’
when T0.[GroupMask] like ‘5%’ then ‘2. Costo de Ventas’
when T0.[GroupMask] like ‘6%’ then ‘3. Gastos’
when T0.[GroupMask] like ‘7%’ then ‘4. Otros Ingresos’
when T0.[GroupMask] like ‘8%’ then ‘5. Otros Gastos’
else ‘Utilidad o Pérdida’ end’Tipo’,
T0.[AcctName]‘Cuenta’,
SUM(T1.[Credit]-T1.[Debit])‘Sub Total’,
NULL’Total’

FROM
OACT T0
LEFT OUTER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT OUTER JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]
WHERE
T0.[GroupMask] IN (‘4’,‘5’,‘6’,‘7’,‘8’) AND
T2.[RefDate]>=[%0] and T2.[RefDate]<=[%1]
GROUP BY
T0.[AcctName],T0.[GroupMask],T0.FormatCode

UNION ALL

SELECT DISTINCT
null,
case
when T0.[GroupMask] like ‘4%’ then ‘Ventas’
when T0.[GroupMask] like ‘5%’ then ‘Costo’
when T0.[GroupMask] like ‘6%’ then ‘Gastos’
when T0.[GroupMask] like ‘9%’ then ‘Saldos Iniciales’
when T0.[GroupMask] like ‘7%’ then ‘Otros Ingresos’
when T0.[GroupMask] like ‘8%’ then ‘Otros Gastos’
else ‘Otros’ end,
case
when T0.[GroupMask] like ‘4%’ then ‘1. Ventas’
when T0.[GroupMask] like ‘5%’ then ‘2. Costo de Ventas’
when T0.[GroupMask] like ‘6%’ then ‘3. Gastos’
when T0.[GroupMask] like ‘7%’ then ‘4. Otros Ingresos’
when T0.[GroupMask] like ‘8%’ then ‘5. Otros Gastos’
else ‘Utilidad o Pérdida’ end,
NULL,
NULL,
SUM(T1.[Credit]-T1.[Debit])

FROM
OACT T0
LEFT OUTER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT OUTER JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]

WHERE
T0.[GroupMask] IN (‘4’,‘5’,‘6’,‘7’,‘8’) AND
T2.[RefDate]>=[%0] and T2.[RefDate]<=[%1]

GROUP BY
case
when T0.[GroupMask] like ‘4%’ then ‘1. Ventas’
when T0.[GroupMask] like ‘5%’ then ‘2. Costo de Ventas’
when T0.[GroupMask] like ‘6%’ then ‘3. Gastos’
when T0.[GroupMask] like ‘7%’ then ‘4. Otros Ingresos’
when T0.[GroupMask] like ‘8%’ then ‘5. Otros Gastos’
else ‘Utilidad o Pérdida’ end,T0.[GroupMask]

UNION ALL

SELECT DISTINCT
null,
‘Utilidad o Pérdida’,
‘Utilidad o Pérdida’,
NULL,
NULL,
SUM(T1.[Credit]-T1.[Debit])

FROM
OACT T0
LEFT OUTER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
LEFT OUTER JOIN OJDT T2 ON T1.[TransId] = T2.[TransId]

WHERE
T0.[GroupMask] IN (‘4’,‘5’,‘6’,‘7’,‘8’) AND
T2.[RefDate]>=[%0] and T2.[RefDate]<=[%1]

ORDER BY
case
when T0.[GroupMask] like ‘4%’ then ‘1. Ventas’
when T0.[GroupMask] like ‘5%’ then ‘2. Costo de Ventas’
when T0.[GroupMask] like ‘6%’ then ‘3. Gastos’
when T0.[GroupMask] like ‘7%’ then ‘4. Otros Ingresos’
when T0.[GroupMask] like ‘8%’ then ‘5. Otros Gastos’
else ‘Utilidad o Pérdida’ end,T0.FormatCode ASC