1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
|
CREATE PROCEDURE spRPL
@TYPE INT,
@PART INT
AS
BEGIN
WITH RPL (PART, SUBPART, QTE)
AS
(
SELECT RT.PART, RT.SUBPART, CAST(RT.QTE AS DECIMAL(15,4))
FROM View_1 RT
WHERE RT.PART = @PART
UNION ALL
SELECT PARENT.PART, CHILD.SUBPART, CAST((ISNULL(PARENT.QTE,0) * ISNULL(CHILD.QTE,0)) AS DECIMAL(15,4))
FROM RPL PARENT, View_1 CHILD
WHERE (PARENT.SUBPART = CHILD.PART)
)
SELECT SUM(R.QTE), T.DPT_FAMILLE, T.DPT_SFAMILLE, T.DPT_LIBELLE, T.DPT_PUA1
FROM RPL R
INNER JOIN T_DEVPART_DPT T ON (R.SUBPART = T.DPT_ID)
WHERE T.DPT_STYPE = @TYPE AND R.QTE <> 0
GROUP BY T.DPT_FAMILLE, T.DPT_SFAMILLE, T.DPT_LIBELLE , T.DPT_PUA1
END |
Partager