SELECT
    CASE
        WHEN OT.planta = 'PET' THEN 'QUI'
        ELSE OT.planta
        END AS PLANTA,
    CAJA.id_maquina AS MAQUINA,
    CASE
        WHEN DATEPART(hh, CAJA.fecha_impresion) >= 23 THEN CAST(DATEADD(DAY, 1, CAJA.fecha_impresion) AS DATE)
        ELSE CAST(CAJA.fecha_impresion AS DATE)
        END AS FECHA,
    CASE
        WHEN CAJA.turno = 'Turno 1' THEN 2
        WHEN CAJA.turno = 'Turno 2' THEN 3
        ELSE 1
        END AS TURNO,
    OT.num_ot AS OT,
    OT.num_detalle_ot AS SUBOT,
    CAJA.id_articulo AS CODIGO,
    SUM(CAJA.bulto) AS CANTIDAD,
    OT.glosa AS DESCRIPCION,
    PALLET.cantidad_por_pallet AS UXPALLET,
    MAQUINAS.NOMBREMAQ AS NOMBREMAQ,
    PRECIOS.precio_unitario,
    TURNOS.color,
    SUM(CAJA.bulto) * PRECIOS.precio_unitario AS TOTAL

    FROM      [SKA].[SISTEMAS_EPT].[sip].[caja] AS CAJA
    LEFT JOIN [SKA].[SISTEMAS_EPT].[sip].[pallet] AS PALLET ON PALLET.id_pallet = CAJA.id_pallet
    LEFT JOIN [SKA].[SISTEMAS_EPT].[dbo].[view_detalle_orden_trabajo] AS OT ON OT.id_detalle_orden_trabajo = CAJA.id_detalle_orden_trabajo
    LEFT JOIN [COUNTRY\EPT].[EPT].[dbo].[PMAQUI] AS MAQUINAS ON MAQUINAS.CODMAQ COLLATE SQL_Latin1_General_CP1_CI_AS = OT.id_maquina
    LEFT JOIN [JAZZ].[GESTION].[ept].[precios_unitarios] AS PRECIOS ON
        PRECIOS.id_producto = CAJA.id_articulo
        AND PRECIOS.id_maquina = CAJA.id_maquina
        AND PRECIOS.planta = CASE WHEN OT.planta = 'PET' THEN 'QUI' ELSE OT.planta END
    LEFT JOIN [JAZZ].[GESTION].[ept].[diccionario_color_turno] AS TURNOS ON
        TURNOS.fecha = CASE
            WHEN DATEPART(hh, CAJA.fecha_impresion) >= 23 THEN CAST(DATEADD(DAY, 1, CAJA.fecha_impresion) AS DATE)
            ELSE CAST(CAJA.fecha_impresion AS DATE) END
        AND TURNOS.turno = CASE
            WHEN CAJA.turno = 'Turno 1' THEN 2
            WHEN CAJA.turno = 'Turno 2' THEN 3
            ELSE 1 END

    WHERE PALLET.estado = 4

    GROUP BY
        OT.planta,
        CAJA.id_maquina,
        CASE
            WHEN DATEPART(hh, CAJA.fecha_impresion) >= 23 THEN CAST(DATEADD(DAY, 1, CAJA.fecha_impresion) AS DATE)
            ELSE CAST(CAJA.fecha_impresion AS DATE) END,
        CAJA.turno,
        OT.num_ot,
        OT.num_detalle_ot,
        CAJA.id_articulo,
        OT.glosa,
        PALLET.cantidad_por_pallet,
        MAQUINAS.NOMBREMAQ,
        PRECIOS.precio_unitario,
        TURNOS.color