1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
|
SELECT tgMarca.Descrip,
taArticuloAlma.Emp,
tgEmpresa.Razon,
tgAlma.Descrip,
tgGrupoCont.Descrip,
taFamInterna.Descrip,
taFamProveedor.Descrip, taArticulo.ReferenciaEditada,
taArticulo.ReferenciaOrden,
taArticulo.Descrip,
taArticulo.CodigoRim,
taArticuloAlma.Almacen,
taArticuloAlma.NumInterno,
taArticuloAlma.FechaAlta,
taArticuloAlma.FechaUltEntrada,
taArticuloAlma.FechaUltSalida,
taArticuloAlma.FechaUltInven,
taArticuloAlma.Ubicacion1,
taArticuloAlma.Baja,
taArticuloAlma.BajaCodigo,
taArticuloAlma.BajaFecha,
taArticuloAlma.CdadStock AS StockAlmacen,
taArticuloAlma.PrecioCosteMedio AS PCPMAlmacen,
taDctoCompra.Descrip,
taCategoriaStock.Descrip,
taCategoriaPieza.Descrip,
taHistorico.Marca AS Marca,
taHistorico.PrecioCosteMedio AS PrecioCosteMedio,
taHistorico.TradeClub AS TradeClub,
CASE WHEN taArticuloPrecio.Precioventa > 0 THEN
taArticuloPrecio.Precioventa
ELSE taArticulo.PrecioVenta END AS PrecioVenta,
COALESCE ((SELECT PrecioCoste FROM taArticuloProv WHERE taArticuloProv.NumInterno = taArticulo.NumInterno AND taArticuloProv.Proveedor = taArticulo.Proveedor),0.00) AS PrecioCompra,
taHistorico.CategoriaStock AS CategoriaStock,
taHistorico.StockFinal AS CantidadStock,
CASE
WHEN taHistorico.StockFinal <= 0 THEN 0.00
WHEN taHistorico.StockFinal IS NULL THEN 0.00
ELSE taHistorico.StockFinal
END AS CdadStock,
taHistorico.FamiliaInt AS FamiliaInt,
taHistorico.FamiliaProv AS FamiliaProv,
taHistorico.GrupoCont AS GrupoCont,
taHistorico.ClaseProducto AS ClaseProducto,
COALESCE (taHistorico.GrupoDctoCompra,'') AS GrupoDctoCompra,
taHistorico.CodigoCategoria AS CodigoCategoria,
( CASE taArticulo.TieneConsigna
WHEN 1 THEN
COALESCE ((SELECT taArticuloProv.PrecioCoste FROM taArticuloProv, taArticulo Consigna
WHERE Consigna.NumInterno = taArticulo.ReferenciaConsigna AND
taArticuloProv.NumInterno = Consigna.NumInterno AND
taArticuloProv.Proveedor = Consigna.Proveedor),0.00)
ELSE 0.00
END ) * 1 AS PrecioCompraConsigna,
( CASE taArticulo.TieneConsigna
WHEN 1 THEN
COALESCE ((SELECT Consigna.PrecioVenta FROM taArticulo Consigna
WHERE Consigna.NumInterno = taArticulo.ReferenciaConsigna),0.00)
ELSE 0.00
END ) * 1 AS PrecioVentaConsigna,
dbo.fn_ICarDMS_ConvertDateTime ('31-07-2013 23:59:59.000') AS ad_fecha,
( CASE taArticulo.TieneConsigna
WHEN 1 THEN
CASE WHEN ( SELECT histoprecio.precioCompraTarifa
FROM taHistorico histoprecio
WHERE histoprecio.Emp = taHistorico.emp AND
histoprecio.NumIntHistorico = ( SELECT MAX (histomaxnum.NumIntHistorico)
FROM taHistorico histomaxnum
WHERE histomaxnum.Emp = '001' AND histomaxnum.Almacen = '11' AND histomaxnum.NumIntArticulo = taArticulo.ReferenciaConsigna AND
histomaxnum.FechaHoraCreacion = ( SELECT MAX (histomaxfecha.FechaHoraCreacion)
FROM taHistorico histomaxfecha
WHERE histomaxfecha.Emp = '001' AND histomaxfecha.Almacen = '11' AND histomaxfecha.NumIntArticulo = taArticulo.ReferenciaConsigna AND
histomaxfecha.FechaHoraCreacion <= year('31-07-2013 23:59:59.000') * 10000000000 + month('31-07-2013 23:59:59.000') * 100000000 + day('31-07-2013 23:59:59.000') * 1000000 + 235959
) )
) <> 0 THEN ( SELECT histoprecio.precioCompraTarifa
FROM taHistorico histoprecio
WHERE histoprecio.Emp = taHistorico.emp AND
histoprecio.NumIntHistorico = (
SELECT MAX (histomaxnum.NumIntHistorico)
FROM taHistorico histomaxnum
WHERE histomaxnum.Emp = '001' AND histomaxnum.Almacen = '11' AND histomaxnum.NumIntArticulo = taArticulo.ReferenciaConsigna AND
histomaxnum.FechaHoraCreacion = (
SELECT MAX (histomaxfecha.FechaHoraCreacion)
FROM taHistorico histomaxfecha
WHERE histomaxfecha.Emp = '001' AND histomaxfecha.Almacen = '11' AND histomaxfecha.NumIntArticulo = taArticulo.ReferenciaConsigna AND
histomaxfecha.FechaHoraCreacion <= year('31-07-2013 23:59:59.000') * 10000000000 + month('31-07-2013 23:59:59.000') * 100000000 + day('31-07-2013 23:59:59.000') * 1000000 + 235959
) )
) ELSE
COALESCE ((SELECT taArticuloProv.PrecioCoste FROM taArticuloProv, taArticulo Consigna
WHERE Consigna.NumInterno = taArticulo.ReferenciaConsigna AND
taArticuloProv.NumInterno = Consigna.NumInterno AND
taArticuloProv.Proveedor = Consigna.Proveedor),0.00)
END
ELSE 0.00
END ) * 1 as PrecioCosteMedioConsigna,
taArticuloAlma.ultCoste,
1 as consigna,
taCaducidad.Descrip,
taarticulo.codigotipo,
taarticulo.indicadorpgr,
taClaseProducto.Descrip,
taArticulo.Caducidad
FROM taHistorico
LEFT OUTER JOIN tgGrupoCont ON taHistorico.GrupoCont = tgGrupoCont.GrupoCont
LEFT OUTER JOIN taFamProveedor ON taHistorico.Marca = taFamProveedor.Marca AND taHistorico.FamiliaProv = taFamProveedor.Codigo
LEFT OUTER JOIN taFamInterna ON taHistorico.Marca = taFamInterna.Marca AND taHistorico.FamiliaInt = taFamInterna.Codigo
LEFT OUTER JOIN taCategoriaStock ON taHistorico.Emp = taCategoriaStock.Emp AND taHistorico.Marca = taCategoriaStock.Marca AND taHistorico.CategoriaStock = taCategoriaStock.Codigo
LEFT OUTER JOIN taclaseproducto ON taHistorico.Marca = taclaseproducto.Marca AND taHistorico.claseproducto = taclaseproducto.Codigo
LEFT OUTER JOIN taCategoriaPieza ON taHistorico.Marca = taCategoriaPieza.Marca AND taHistorico.CodigoCategoria = taCategoriaPieza.Codigo,
taArticuloAlma
LEFT OUTER JOIN taArticuloPrecio ON (taArticuloAlma.NumInterno = taArticuloPrecio.NumInterno AND taArticuloAlma.Emp = taArticuloPrecio.Emp AND taArticuloAlma.Almacen = taArticuloPrecio.Almacen),
taArticulo
LEFT OUTER JOIN taDctoCompra ON taArticulo.Marca = taDctoCompra.Marca AND taArticulo.Proveedor = taDctoCompra.Proveedor AND
taArticulo.GrupoDctoCompra = taDctoCompra.GrupDctoComp AND
taDctoCompra.TipoPedido IN ( SELECT tipopedido FROM tgalma where emp = '001' and almacen = '11' )
LEFT OUTER JOIN taCaducidad ON taArticulo.CodigoCaducidad = taCaducidad.Codigo,
tgEmpresa,
tgAlma,
tgMarca
WHERE taArticuloAlma.Emp = '001' AND
taArticuloAlma.Almacen = '11' AND
taArticuloAlma.NumInterno IN (SELECT DISTINCT NumIntArticulo FROM taHistorico
WHERE taHistorico.Emp = '001' AND
taHistorico.Almacen = '11' AND
taHistorico.FechaHoraCreacion <= year ('31-07-2013 23:59:59.000') * 10000000000 + month('31-07-2013 23:59:59.000') * 100000000 + day ('31-07-2013 23:59:59.000') * 1000000 + 235959) AND
taHistorico.emp = '001' AND
taHistorico.NumIntHistorico = (SELECT MAX (taHistorico.NumIntHistorico) FROM taHistorico
WHERE taHistorico.Emp = '001' AND
taHistorico.Almacen = '11' AND
taHistorico.NumIntArticulo = taArticuloAlma.NumInterno AND
---------------------------
taHistorico.FechaHoraCreacion = (SELECT MAX (taHistorico.FechaHoraCreacion) FROM taHistorico
WHERE taHistorico.Emp = '001' AND
taHistorico.Almacen = '11' AND
taHistorico.NumIntArticulo = taArticuloAlma.NumInterno AND
taHistorico.FechaHoraCreacion <= year ('31-07-2013 23:59:59.000') * 10000000000 + month('31-07-2013 23:59:59.000') * 100000000 + day ('31-07-2013 23:59:59.000') * 1000000 + 235959))
-------------
AND
taHistorico.StockFinal <> 0 AND
taArticuloAlma.NumInterno = taArticulo.NumInterno AND
taArticulo.EsConsigna = 0 AND
taArticulo.NoAlmacenada = 0 AND
taArticuloAlma.Emp = tgEmpresa.Emp AND
taArticuloAlma.Emp = tgAlma.Emp AND
taArticuloAlma.Almacen = tgAlma.Almacen AND
taArticulo.Marca = tgMarca.Marca |
Partager