INFORMES EN CRM GO LIVE - ESTADO PEDIDOS

DECLARE @__Ocasional_Value_6 nvarchar(60) = N'Ocasional';

DECLARE @__ECommerce_Key_7 nvarchar(60) = N'e-Commerce';

DECLARE @__Base_Key_0 nvarchar(1) = N'B';

DECLARE @__doJoin_2 int = 0;

DECLARE @__p_4 datetime = '2020-07-01T00:00:00.000';

DECLARE @__p_5 datetime = '2030-06-30T00:00:00.000';

SET DEADLOCK_PRIORITY -8;

SELECT [g4].[ID_GVA14], [g0].[ID_GVA21], [s1].[ID_STA11], CAST([g0].[TALON_PED] AS int) AS [TALON_PEDIDO], [g0].[NRO_PEDIDO] AS N'Nro. pedido', CASE

    WHEN [g0].[FECHA_ENTR] IS NOT NULL THEN [g0].[FECHA_ENTR]

    ELSE [t].[FECHA_MINIMA]

END AS N'Fecha entrega', [g0].[ESTADO] AS N'Estado', [g13].[DESC_COND] AS N'Condición de venta', CASE

    WHEN [g0].[COD_CLIENT] = N'000000' THEN @__Ocasional_Value_6

    ELSE [g4].[RAZON_SOCI]

END AS N'Razón social', [g].[COD_ARTICU] AS N'Cód. artículo', CASE

    WHEN [g11].[DESC] IS NULL OR ([g11].[DESC] LIKE N'') THEN [s1].[DESC_ADIC]

    ELSE [g11].[DESC_ADIC]

END AS N'Desc. Adicional', COALESCE(SUM(CASE

    WHEN (([g0].[ESTADO] = CAST(3 AS smallint)) OR ([g0].[ESTADO] = CAST(4 AS smallint))) OR ([g0].[ESTADO] = CAST(5 AS smallint)) THEN 0.0

    ELSE [g].[CANT_PEN_D]

END), 0.0) AS N'Cantidad pendiente de descargar', COALESCE(SUM(CASE

    WHEN (([g1].[INCLUY_IVA] IS NULL) OR ([g1].[INCLUY_IVA] = CAST(1 AS bit))) OR (([g1].[INCLUY_IMP] IS NOT NULL) AND ([g1].[INCLUY_IMP] = CAST(1 AS bit))) THEN 0.0

    WHEN ([g0].[ES_PEDIDO_WEB] = CAST(1 AS bit)) AND (([g0].[TIENDA] <> @__ECommerce_Key_7) OR [g0].[TIENDA] IS NULL) THEN ((CASE

        WHEN [g1].[MON_CTE] = CAST(1 AS bit) THEN [g].[PRECIO] / [g0].[COTIZ]

        ELSE [g].[PRECIO]

    END * (1.0 - ([g].[DESCUENTO] / 100.0))) * CASE

        WHEN (([g0].[ESTADO] = CAST(3 AS smallint)) OR ([g0].[ESTADO] = CAST(4 AS smallint))) OR ([g0].[ESTADO] = CAST(5 AS smallint)) THEN 0.0

        ELSE [g].[CANT_PEN_F]

    END) * (1.0 - (([g0].[PORC_DESC] + ([g0].[PORCEN_DESC_TIENDA] / CASE

        WHEN ((CASE

            WHEN [g1].[MON_CTE] = CAST(1 AS bit) THEN [g].[PRECIO]

            ELSE [g].[PRECIO] * [g0].[COTIZ]

        END * (1.0 - ([g].[DESCUENTO] / 100.0))) * [g].[CANT_PEDID]) = 0.0 THEN 1.0

        ELSE (CASE

            WHEN [g1].[MON_CTE] = CAST(1 AS bit) THEN [g].[PRECIO]

            ELSE [g].[PRECIO] * [g0].[COTIZ]

        END * (1.0 - ([g].[DESCUENTO] / 100.0))) * [g].[CANT_PEDID]

    END)) / 100.0))

    ELSE ((CASE

        WHEN [g1].[MON_CTE] = CAST(1 AS bit) THEN [g].[PRECIO] / [g0].[COTIZ]

        ELSE [g].[PRECIO]

    END * (1.0 - ([g].[DESCUENTO] / 100.0))) * CASE

        WHEN (([g0].[ESTADO] = CAST(3 AS smallint)) OR ([g0].[ESTADO] = CAST(4 AS smallint))) OR ([g0].[ESTADO] = CAST(5 AS smallint)) THEN 0.0

        ELSE [g].[CANT_PEN_F]

    END) * (1.0 - ([g0].[PORC_DESC] / 100.0))

END), 0.0) AS N'Total pendiente sin impuestos', [g0].[NRO_OC_COMP] AS N'Número de orden de compra'

FROM [GVA03] AS [g]

INNER JOIN [GVA21] AS [g0] ON ([g].[NRO_PEDIDO] = [g0].[NRO_PEDIDO]) AND ([g].[TALON_PED] = [g0].[TALON_PED])

LEFT JOIN [GVA10] AS [g1] ON [g0].[N_LISTA] = [g1].[NRO_DE_LIS]

LEFT JOIN [GVA43] AS [g2] ON [g0].[TALON_PED] = [g2].[TALONARIO]

LEFT JOIN [GVA23] AS [g3] ON [g0].[ID_GVA23] = [g3].[ID_GVA23]

LEFT JOIN [GVA14] AS [g4] ON [g0].[ID_GVA14] = [g4].[ID_GVA14]

LEFT JOIN [GVA05] AS [g5] ON [g4].[ID_GVA05] = [g5].[ID_GVA05]

LEFT JOIN [GVA18] AS [g6] ON [g4].[ID_GVA18] = [g6].[ID_GVA18]

LEFT JOIN [GVA133] AS [g7] ON [g6].[ID_GVA133] = [g7].[ID_GVA133]

LEFT JOIN [GVA24] AS [g8] ON [g0].[ID_GVA24] = [g8].[ID_GVA24]

INNER JOIN [STA22] AS [s] ON COALESCE([g].[COD_DEPOSI], [g0].[COD_SUCURS]) = [s].[COD_SUCURS]

LEFT JOIN [GVA81] AS [g9] ON [g0].[ID_GVA81] = [g9].[ID_GVA81]

LEFT JOIN [GVA81] AS [g10] ON [g].[COD_CLASIF] = [g10].[COD_CLASIF]

CROSS JOIN [STA16] AS [s0]

INNER JOIN [STA11] AS [s1] ON [g].[COD_ARTICU] = [s1].[COD_ARTICU]

LEFT JOIN [GVA45] AS [g11] ON (([g].[TALON_PED] = [g11].[TALONARIO]) AND ([g].[NRO_PEDIDO] = [g11].[N_COMP])) AND (([g].[N_RENGLON] = [g11].[N_RENGLON]) OR ([g].[N_RENGLON] IS NULL AND [g11].[N_RENGLON] IS NULL))

LEFT JOIN [MEDIDA] AS [m] ON [g].[ID_MEDIDA_STOCK] = [m].[ID_MEDIDA]

LEFT JOIN [MEDIDA] AS [m0] ON [g].[ID_MEDIDA_STOCK_2] = [m0].[ID_MEDIDA]

LEFT JOIN [DIRECCION_ENTREGA] AS [d] ON [g0].[ID_DIRECCION_ENTREGA] = [d].[ID_DIRECCION_ENTREGA]

CROSS JOIN [EMPRESA] AS [e]

LEFT JOIN [SUCURSAL] AS [s2] ON CASE

    WHEN [g0].[ID_SUCURSAL] IS NOT NULL THEN [g0].[ID_SUCURSAL]

    ELSE [e].[ID_SUCURSAL]

END = [s2].[NRO_SUCURSAL]

LEFT JOIN (

    SELECT [g12].[TALON_PED], [g12].[NRO_PEDIDO], MIN([g12].[FEC_ENTREG]) AS [FECHA_MINIMA]

    FROM [GVA126] AS [g12]

    GROUP BY [g12].[TALON_PED], [g12].[NRO_PEDIDO]

) AS [t] ON (([g0].[NRO_PEDIDO] = [t].[NRO_PEDIDO]) OR ([g0].[NRO_PEDIDO] IS NULL AND [t].[NRO_PEDIDO] IS NULL)) AND ([g0].[TALON_PED] = [t].[TALON_PED])

LEFT JOIN [GVA01] AS [g13] ON [g0].[ID_GVA01] = [g13].[ID_GVA01]

LEFT JOIN [AXV_LIVE_Sta11FamiliaGrupo] AS [a] ON [s1].[ID_STA11] = [a].[ID_STA11]

LEFT JOIN [STA29] AS [s3] ON [a].[ID_STA29_GRUPO] = [s3].[ID_STA29]

LEFT JOIN [STA29] AS [s4] ON [a].[ID_STA29_FAMILIA] = [s4].[ID_STA29]

LEFT JOIN [AXV_LIVE_GVA14FAMILIAGRUPO] AS [a0] ON [g4].[ID_GVA14] = [a0].[ID_GVA14]

LEFT JOIN [GVA28] AS [g14] ON [a0].[ID_GVA28_GRUPO] = [g14].[ID_GVA28]

LEFT JOIN [GVA28] AS [g15] ON [a0].[ID_GVA28_FAMILIA] = [g15].[ID_GVA28]

LEFT JOIN (

    SELECT [s5].[ID_STA11], [s5].[AFECTA_AF], [s5].[AFIP_EQ_S], [s5].[AFIP_EQ_V], [s5].[AFIP_UMEX_S], [s5].[AFIP_UMEX_V], [s5].[AFIP_UM_S], [s5].[AFIP_UM_V], [s5].[ALI_NO_CAT], [s5].[BASE], [s5].[CALCULA_CM], CONVERT(VARCHAR(max), [s5].[CAMPOS_ADICIONALES]) AS [CAMPOS_ADICIONALES], [s5].[CL_SIAP_CP], [s5].[CL_SIAP_GV], [s5].[COD_ACTIVI], [s5].[COD_ARTICU], [s5].[COD_BARRA], [s5].[COD_IB], [s5].[COD_IB3], [s5].[COD_II], [s5].[COD_II_CO], [s5].[COD_II_V_2], [s5].[COD_IVA], [s5].[COD_IVA_CO], [s5].[COD_NCM], [s5].[COD_PLANTI], [s5].[COD_SII_V2], [s5].[COD_STA11], [s5].[COD_S_II], [s5].[COD_S_II_C], [s5].[COD_S_IVA], [s5].[COD_S_IV_C], [s5].[COD_TIPOB], [s5].[COMISION_V], [s5].[CTA_COMPRA], [s5].[CTA_VENTAS], [s5].[CTO_COMPRA], [s5].[CTO_VENTAS], [s5].[DESCARGA_NEGATIVO_STOCK], [s5].[DESCARGA_NEGATIVO_VENTAS], [s5].[DESCRIPCIO], [s5].[DESCUENTO], [s5].[DESC_ADIC], [s5].[DESTI_ART], [s5].[DESVIO_CIERRE_PEDIDOS], [s5].[DESVIO_CONTROL_UNIDADES_STOCK], [s5].[EGRESO_MODIFICA_PARTIDA_PROPUESTA], [s5].[EQUIVALENCIA_STOCK_2], [s5].[EQUIVALE_V], [s5].[ESCALA_1], [s5].[ESCALA_2], [s5].[FACT_IMPOR], [s5].[FECHA_ALTA], [s5].[FECHA_MODI], [s5].[FILLER], [s5].[GENERACOT], [s5].[GEN_IB], [s5].[GEN_IB3], [s5].[ID_ACTIVIDAD_DGI], [s5].[ID_CLASIFICACION_SIAP_CL_SIAP_CP], [s5].[ID_CLASIFICACION_SIAP_CL_SIAP_GV], [s5].[ID_CODIGO_ITEM_TURISMO], [s5].[ID_CPA14_COD_II_CO], [s5].[ID_CPA14_COD_IVA_CO], [s5].[ID_CPA14_COD_S_II_C], [s5].[ID_CPA14_COD_S_IV_C], [s5].[ID_GVA125], [s5].[ID_GVA22], [s5].[ID_GVA41_ALI_NO_CAT], [s5].[ID_GVA41_COD_IB], [s5].[ID_GVA41_COD_IB3], [s5].[ID_GVA41_COD_II], [s5].[ID_GVA41_COD_II_V_2], [s5].[ID_GVA41_COD_IVA], [s5].[ID_GVA41_COD_SII_V2], [s5].[ID_GVA41_COD_S_II], [s5].[ID_GVA41_COD_S_IVA], [s5].[ID_MEDIDA_CONTROL_STOCK], [s5].[ID_MEDIDA_STOCK], [s5].[ID_MEDIDA_STOCK_2], [s5].[ID_MEDIDA_VENTAS], [s5].[ID_MODELO_PERCEPCION_VENTAS], [s5].[ID_STA115], [s5].[ID_STA11_BASE], [s5].[ID_STA32_ESCALA_1], [s5].[ID_STA32_ESCALA_2], [s5].[ID_STA33_VALOR1], [s5].[ID_STA33_VALOR2], [s5].[ID_TIPO_BIEN], [s5].[ID_TIPO_ITEM_AFIP], [s5].[ID_TIPO_UNIDAD_TURISMO], [s5].[ID_TYPS], [s5].[ID_UNIDAD_MEDIDA_AFIP_UMEX_S], [s5].[ID_UNIDAD_MEDIDA_AFIP_UMEX_V], [s5].[ID_UNIDAD_MEDIDA_AFIP_UM_S], [s5].[ID_UNIDAD_MEDIDA_AFIP_UM_V], [s5].[IMPUESTO_I], [s5].[IMPUEST_IC], [s5].[IMP_II_V_2], [s5].[LLEVA_DOBLE_UNIDAD_MEDIDA], [s5].[MET_DES_PA], [s5].[OBSERVACIONES], [s5].[ORD_DES_PA], [s5].[ORIGEN_PARA_CM], [s5].[PERC_NO_CA], [s5].[PERFIL], [s5].[PORC_DESVI], [s5].[PORC_SCRAP], [s5].[PORC_UTILI], [s5].[PRODUCTO_TERMINADO_COT], [s5].[PROMODESDE], [s5].[PROMOHASTA], [s5].[PROMO_MENU], [s5].[PTO_PEDIDO], [s5].[PUBLICA_WEB_PEDIDO], [s5].[RELACION_UNIDADES_STOCK], [s5].[REMITIBLE], [s5].[RENTA_EQ_S], [s5].[RENTA_EQ_V], [s5].[RENTA_PROD], [s5].[RENTA_UM_S], [s5].[RENTA_UM_V], [s5].[RET_RNI], [s5].[RET_RNI_CO], [s5].[ROW_VERSION], [s5].[SERIE_DESC_ADICIONAL_1], [s5].[SERIE_DESC_ADICIONAL_2], [s5].[SINCRONIZA_WEB_PEDIDO], [s5].[SINONIMO], [s5].[STOCK], [s5].[STOCK_MAXI], [s5].[STOCK_MINI], [s5].[STOCK_NEG], [s5].[TIPO_PROMO], [s5].[USA_CONTROL_UNIDADES_STOCK], [s5].[USA_CTRPRE], [s5].[USA_ESC], [s5].[USA_PARTID], [s5].[USA_SCRAP], [s5].[USA_SERIE], [s5].[VALOR1], [s5].[VALOR2]

    FROM [STA11] AS [s5]

    WHERE [s5].[USA_ESC] = @__Base_Key_0

) AS [t0] ON (CASE

    WHEN [s1].[BASE] = N'' THEN [s1].[COD_ARTICU]

    ELSE [s1].[BASE]

END = [t0].[COD_ARTICU]) OR (CASE

    WHEN [s1].[BASE] = N'' THEN [s1].[COD_ARTICU]

    ELSE [s1].[BASE]

END IS NULL AND [t0].[COD_ARTICU] IS NULL)

LEFT JOIN [dbo].[ClasificadorGVA14](@__doJoin_2) AS [c] ON [g0].[ID_GVA14] = [c].[ID_GVA14]

LEFT JOIN [SUCURSAL] AS [s6] ON [g0].[NRO_SUCURSAL_DESTINO_PEDIDO] = [s6].[NRO_SUCURSAL]

WHERE ((([g].[COD_ARTICU] <> N'') OR [g].[COD_ARTICU] IS NULL) AND ([g].[RENGL_PADR] = 0)) AND (([g0].[FECHA_PEDI] IS NOT NULL AND (CONVERT(date, [g0].[FECHA_PEDI]) >= @__p_4)) AND (CONVERT(date, [g0].[FECHA_PEDI]) <= @__p_5))

GROUP BY [g4].[ID_GVA14], [g0].[ID_GVA21], [s1].[ID_STA11], CAST([g0].[TALON_PED] AS int), [g0].[NRO_PEDIDO], CASE

    WHEN [g0].[FECHA_ENTR] IS NOT NULL THEN [g0].[FECHA_ENTR]

    ELSE [t].[FECHA_MINIMA]

END, [g0].[ESTADO], [g13].[DESC_COND], CASE

    WHEN [g0].[COD_CLIENT] = N'000000' THEN @__Ocasional_Value_6

    ELSE [g4].[RAZON_SOCI]

END, [g].[COD_ARTICU], CASE

    WHEN [g11].[DESC] IS NULL OR ([g11].[DESC] LIKE N'') THEN [s1].[DESC_ADIC]

    ELSE [g11].[DESC_ADIC]

END, [g0].[NRO_OC_COMP]