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
|
SELECT
societe,
magasin,
marque,
Case Muerto WHEN 1 then '2-Stock Mort' else '1-Stock Vivant' End As Muerto ,
Case Rango
WHEN 1 then '1-Mois 6 mois'
WHEN 2 then '2-Mois 6-12 mois'
WHEN 3 then '3-Mois 12-24 mois'
WHEN 4 then '4-Mois + 24 mois' End as Rango ,
SUM(CantidadStock) as qte,
SUM((PCPMAlmacen)* CantidadStock) as "Pamp",
SUM((taHistorico_PrecioVenta)* CantidadStock) as "PVP",
SUM((PrecioCompra)* CantidadStock) as "PA",
SUM((PrecioCosteMedio )* CantidadStock) as "Pamp2"
FROM (
SELECT tgMarca.Descrip as "marque",
tgEmpresa.Razon as "societe",
tgAlma.Descrip as "magasin",
taArticuloAlma.PrecioCosteMedio AS PCPMAlmacen,
taHistorico.PrecioCosteMedio AS PrecioCosteMedio,
CASE
WHEN COALESCE(taHistorico.PrecioVentaTarifa, 0.00) = 0.00 THEN CASE
WHEN COALESCE(taHistorico.PrecioVenta, 0.00) = 0.00 THEN CASE
WHEN taArticuloPrecio.Precioventa > 0 THEN taArticuloPrecio.Precioventa
ELSE taArticulo.PrecioVenta
END
ELSE taHistorico.PrecioVenta
END
ELSE taHistorico.PrecioVentaTarifa
END AS taHistorico_PrecioVenta,
CASE
WHEN COALESCE(taHistorico.PrecioCompraTarifa, 0.00) = 0.00 THEN CASE
WHEN COALESCE(taHistorico.PrecioCompra, 0.00) = 0.00 THEN COALESCE(
(SELECT PrecioCoste
FROM taArticuloProv
WHERE taArticuloProv.NumInterno = taArticulo.NumInterno
AND taArticuloProv.Proveedor = taArticulo.Proveedor),0.00)
ELSE taHistorico.PrecioCompra
END
ELSE taHistorico.PrecioCompraTarifa
END AS PrecioCompra,
taHistorico.CategoriaStock AS CategoriaStock,
taHistorico.StockFinal AS CantidadStock,
CASE
WHEN taHistorico.StockFinal <= 0 THEN 0.00
ELSE COALESCE(taHistorico.StockFinal, 0.00)
END AS CdadStock,
CASE
WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 6 THEN 1
WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 2
WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 24 THEN 3
ELSE 4
END AS Rango,
CASE
WHEN dbo.fn_ICarDMS_dateDiff ('MONTH2',(COALESCE (dbo.fn_ICarDMS_GetLastHistMovAlm ('001', taArticuloAlma.Almacen, taArticulo.NumInterno, '27-04-2020 00:00:00.000', 1), taArticuloalma.FechaAlta)) ,'27-04-2020 00:00:00.000') < 12 THEN 0
ELSE 1
END AS Muerto
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 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 = '10' ), tgEmpresa,
tgAlma,
tgMarca
WHERE taArticuloAlma.Emp = '001'
AND taArticuloAlma.Almacen = '10'
AND taHistorico.Emp = taArticuloAlma.Emp
AND taHistorico.Almacen = taArticuloAlma.Almacen
AND taHistorico.NumIntArticulo = taArticuloAlma.NumInterno
AND taHistorico.NumIntHistorico = dbo.fn_ICarDMS_LastHistArt ('001', '10', taArticuloAlma.NumInterno, '27-04-2020 00:00:00.000')
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) as requete1
GROUP BY societe,
magasin,
marque,
Muerto,Rango
ORDER BY societe,
magasin,
marque,
Muerto,Rango |
