declare @idSucursal int

set @idSucursal = 2262
                            declare @PromocionesPLPorSucursales TABLE
                            (
                                idSucursal int NOT NULL,
                                idAgrupacion int NOT NULL,
                                codigoPromocion int NOT NULL,
                                codigoProducto int not null,
                                nombre nvarchar(240) NOT NULL,
                                descripcion nvarchar(240) NOT NULL,
                                fechaInicio date NOT NULL,
                                fechaTermino date NULL,
                                tipoProgramaLealtad varchar(100),
                                codigoProgramaLealtad tinyint,
                                estaActivo bit,
vigente bit
                            );
                            ;with tabla(idAgrupacion) as 
                            (
                            select gasc.codigoAgrupacion as idAgruapacion 
                            from genAgrupacionesSucursalesCat as gasc
                            inner join genSucursalesCat as gsc
                            on gasc.codigoAgrupacion = gsc.idAgrupacion
                            where gsc.codigoSucursal = @idSucursal and gasc.estaActivo = 1
                            union all
                            select gaas.codigoAgrupacionPadre
                            from genAsociacionesAgrupacionSucursales as gaas
                            inner join tabla as tb
                            on gaas.codigoAgrupacionHijo = tb.idAgrupacion
                            where gaas.estaActivo = 1 and gaas.codigoJerarquia = 2
                            )
                            insert into @PromocionesPLPorSucursales
                            select
                            @idSucursal,
                            tmp.idAgrupacion,
                            gppl.codigoPromocion,
                            gpplp.codigoProducto,
                            gppl.nombre,
                            gppl.descripcion,
                            gppl.fechaInicio,
                            gppl.fechaTermino,
                            gplc.descripcion,
                            gplc.codigoProgramaLealtad,
                            gppl.estaActivo,
case when gppl.fechaTermino >= CAST(GETDATE() as date) and gppl.fechaInicio <= CAST(GETDATE() as date)
then 1 else 0 end as vigente
                            from tabla as tmp
                            inner join genAgrupacionSucursalesProgramasLealtad as gaspl
                              on tmp.idAgrupacion = gaspl.codigoAgrupacion
                            inner join genPromocionesProgramasLealtad as gppl
                              on gaspl.codigoPromocion = gppl.codigoPromocion
                            inner join genProgramasLealtadCat as gplc
                              on gplc.codigoProgramaLealtad = gppl.codigoProgramaLealtad
                            inner join genPromocionesProgramaLealtadProductos as gpplp
                                on gppl.codigoPromocion = gpplp.codigoPromocion
                            --and gpplp.codigoProducto in (select Id from @tempProducts)
                            and gpplp.estaActivo = 1 and gppl.estaActivo = 1            

                            select distinct
                                ppls.idSucursal,
                                ppls.codigoPromocion,
                                ppls.codigoProducto,
                                ppls.nombre,
                                ppls.descripcion,
                                ppls.fechaInicio,
                                ppls.fechaTermino,
                                ppls.tipoProgramaLealtad,
                                ppls.codigoProgramaLealtad,
                                ppls.estaActivo,
ppls.vigente
                            FROM @PromocionesPLPorSucursales as ppls