WITH cte AS (
SELECT es000 AS articuloPadre,
es020 as articuloHijo,
es000 AS PT,
(
(
(
(
(es030/ar066)*(1+(ISNULL(es040,0)/100))
)
/
isnull((ar240/100),1)
)
/
(SELECT ar310 FROM fproart WHERE ar000 = es000)
)
*
(
SELECT coalesce (SUM(lp120),0)
FROM fcomlpe, fcomcpe,fcompae --demanda desde pedidos (futuro)
WHERE cp000 = lp000
AND cp010 = lp010
AND lp156 BETWEEN DATEADD(mm, +4, GETDATE()) AND DATEADD(day, -1, DATEADD(mm, +5, GETDATE()))
AND lp300 = 0
AND lp030 = es000
AND pa010 = lp010
AND pa020 = lp020
AND pa470 IN ('SE','PS','SC', 'MP')
)+(
(
(
( (es030/ar066) * (1 + (ISNULL(es040, 0) / 100)) )
/
isnull((ar240 / 100), 1) )
/
(SELECT ar310 FROM fproart WHERE ar000 = es000)
)
*
(
CASE
WHEN EXISTS (
SELECT 1
FROM fpladin
WHERE di001 = 'DIARIO'
AND di040 = a.es000
AND di050 BETWEEN DATEADD(mm, +4, GETDATE()) AND DATEADD(day, -1, DATEADD(mm, +5, GETDATE()))
GROUP BY di040
) THEN (
SELECT COALESCE(SUM(di070), 0)
FROM fpladin
WHERE di001 = 'DIARIO'
AND di040 = a.es000
AND di050 BETWEEN DATEADD(mm, +4, GETDATE()) AND DATEADD(day, -1, DATEADD(mm, +5, GETDATE()))
)
ELSE 0
END
)
)
) demanda_5M
FROM fproest a JOIN fproart ON a.es020=fproart.ar000
WHERE es020 LIKE '0035/1600' AND-- <--Descomenta esto para ejecutar la consulta con un articulo Padre especifico
es000 IN (
SELECT lp030
FROM fcomlpe, fcompae
WHERE lp156 BETWEEN GETDATE() AND DATEADD(mm, +8, GETDATE())
AND lp300 = 0
AND lp030 = es000
AND pa010 = lp010
AND pa020 = lp020
AND pa470 IN ('SE','PS','SC', 'MP')
)
AND ar030 IN ('S','T','P','A')
UNION ALL
SELECT t.es000, t.es020, cte.PT,
(
(
(
(
(es030/ar066)*(1+(ISNULL(es040,0)/100))
)
/
isnull((ar240/100),1)
)
/
(SELECT ar310 FROM fproart WHERE ar000 = es000)
)
*
demanda_5M
) demanda_5M
FROM fproest t JOIN cte ON t.es000 = cte.articuloHijo JOIN fproart ON t.es020=ar000
WHERE ar030 IN ('S','T','P','A')
)
SELECT
cte.demanda_5M Dem_5
FROM cte JOIN fproest ON cte.articuloPadre=es000 AND cte.articuloHijo=es020
JOIN fproart h ON h.ar000=cte.articuloHijo
JOIN fproart p ON p.ar000=cte.articuloPadre
WHERE h.ar030 IN ('P')
-- ORDER BY MP