Les dejo un query para recorrer la SBO-COMMON y tomar sus bases de datos y dentro de ellas ejecutar un mismo query en todas las empresas del servidor, solo deben de cambiar las variables por la que utilicen y agregar los campos que necesiten, lo que dejo es la base para recorrer
BEGIN
-- variables a utilizar
DECLARE
@sqlQuery nvarchar(max),
@companyName nvarchar(500),
@companyFather nvarchar(500),
@database nvarchar(500)= 'Nombre de la BD',
@numeroEmpresas int,
@empresaTurno int,
@tipoCambioUSD money,
@tipoCambioEUR money,
@fechaFinConsulta date
select @fechaFinConsulta = '20150630'
-- tabla de paso de las consultas
CREATE TABLE #empresas(
rowId int,
Empresa varchar(500),
BaseDatos varchar(500),
Padre varchar(500)
)
-- tabla de resultado
CREATE TABLE #result(
Empresa varchar(500) collate database_default,
CamposdelQuery varchar(500),
)
-- toma tipos de cambio
-- USD
select TOP 1 @tipoCambioUSD=Rate from Z_TEJAS.DBO.ORTT tc
where TC.Currency='USD' AND YEAR(tc.RateDate)=YEAR(@fechaFinConsulta) and MONTH(TC.RateDate)=MONTH(@fechaFinConsulta)
ORDER BY TC.RateDate DESC
-- EUR
select TOP 1 @tipoCambioEUR=Rate from Z_TEJAS.DBO.ORTT tc
where TC.Currency='EUR' AND YEAR(tc.RateDate)=YEAR(@fechaFinConsulta) and MONTH(TC.RateDate)=MONTH(@fechaFinConsulta)
ORDER BY TC.RateDate DESC
-- bucle de consulta
INSERT INTO #empresas
SELECT
ROW_NUMBER() OVER(ORDER BY ep.code DESC),
EH.Code+eh.U_CompanyName,
eh.U_CompanyDB,
ep.U_CompanyName
FROM
Z_TEJAS.dbo.[@EMPRESAS] eh
inner join Z_TEJAS.dbo.[@EMPRESAS] ep on eh.U_Father=ep.Code
where
eh.U_CompanyDB not in ('AGREGACION','EXCEL') and eh.U_SAP='YES'
order by
eh.code
SET @numeroEmpresas=@@ROWCOUNT
SET @empresaTurno = 1
WHILE @empresaTurno <= @numeroEmpresas
BEGIN
--consulta de valores
SELECT @companyName = Empresa, @database= BaseDatos, @companyFather= Padre
FROM #empresas
WHERE RowID = @empresaTurno
-- construye consulta
SELECT @sqlQuery = '
SELECT
'''+@companyName+''' Empresa ,
[CamposdelQuery] AS [CamposdelQuery]
FROM
'+@database+'.dbo.JDT1 T0
INNER JOIN '+@database+'.dbo.OJDT T1 ON T0.TransId = T1.TransId
INNER JOIN '+@database+'.dbo.OACT T2 ON T0.Account = T2.AcctCode
LEFT JOIN '+@database+'.dbo.ORTT TC ON TC.RateDate = T0.RefDate and TC.Currency=''USD''
WHERE
T0.TransType <> (-3)
AND (T0.SourceLine <> (-8) OR T0.SourceLine IS NULL)
AND T1.REFDATE <= '''+convert(varchar,@fechaFinConsulta)+'''
GROUP BY
T0.Account,T0.ShortName,T2.GroupMask
--,T2.U_BXP_TC
'
-- ejecuta y almacena resultado en tabla temporal
PRINT(@SQLQUERY)
INSERT INTO #result
EXEC (@SQLQUERY)
-- completa cuentas sin movimientos
SELECT @sqlQuery ='
insert into #result
SELECT
'''+@companyName+''' Empresa ,
[CamposdelQuery]
FROM
'+@database+'.dbo.OACT T2
WHERE
T2.AcctCode NOT IN (SELECT #result.[Cuenta SAP] FROM #result WHERE empresa='''+@companyName+''')
AND T2.Postable=''Y''
';
PRINT(@SQLQUERY)
INSERT INTO #result
EXEC (@SQLQUERY)
SET @empresaTurno = @empresaTurno + 1
END
-- DEFINICIÓN DE NIVELES
MERGE
#result as target
USING
(
SELECT
AC.AcctCode,
AC.U_IFRS1 AS [IFRS1],
AC.U_IFRS2 AS [IFRS2],
AC.U_IFRS3 AS [IFRS3],
AC.U_Order1 AS [Order 1],
AC.U_Order2 AS [Order 2]
FROM
z_tejas.dbo.OACT AC
) as source
ON
target.[Cuenta SAP] COLLATE DATABASE_DEFAULT =source.AcctCode COLLATE DATABASE_DEFAULT
WHEN MATCHED THEN
UPDATE SET
target.[IFRS1]= source.[IFRS1],
target.[IFRS2]= source.[IFRS2],
target.IFRS3 = source.[IFRS3],
target.[Order 1]= source.[Order 1],
target.[Order 2]= source.[Order 2]
;
-- REGRESA RESULTADO
SELECT * FROM #result order by Empresa, [Cuenta SAP]
--LIMPRIAMOS
drop table #empresas
DROP TABLE #result
END
Bluesky
