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
|
CREATE VIEW [dbo].[V_DEVPART_ALL_DPT1]
AS
WITH Descendants (ID, LVL, NUM)
AS
(
-- Ancre
SELECT A.DPT_ID, 0 AS LVL, CAST(A.DPT_ORD AS VARCHAR(32)) AS NUM
FROM T_DEVPART_DPT A
WHERE A.DPT_PID IS NULL
UNION ALL
-- Partie Recursive
SELECT B.DPT_ID, P.LVL+1 AS LVL, CAST(P.NUM + '-' + CAST(B.DPT_ORD AS VARCHAR(4)) AS VARCHAR(32))
FROM T_DEVPART_DPT B
INNER JOIN Descendants P ON B.DPT_PID = P.ID
)
--Utlisation de la cte
SELECT
A.DPT_ID,
T.LVL AS DPT_LVL,
T.NUM AS DPT_NUM,
A.DEV_ID,
A.DPT_PID,
A.DPT_ORD,
A.DPT_STYPE,
A.DPT_FAMILLE,
A.DPT_SFAMILLE,
A.DPT_REF,
A.DPT_LIBELLE,
A.DPT_QTE,
A.DPT_UNITP,
B.UPU_QTE,
A.DPT_PUA1,
A.DPT_PUA2,
A.DPT_PUVHF,
A.DPT_PUV1,
A.DPT_PUVF,
A.DPT_MT_AJUST,
A.DPT_NB_HRS_MO,
A.DPT_MT_MO,
A.DPT_PUA1 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTA1,
A.DPT_PUA2 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTA2,
A.DPT_PUVHF * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTVHF,
A.DPT_PUV1 * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTV1,
A.DPT_PUVF * A.DPT_QTE * (1 / B.UPU_QTE) AS DPT_PTVF,
A.DPT_TEXTCOM,
A.DPT_REF_DEEE,
A.DPT_CODE_DEEE,
A.DPT_UNIT_DEEE,
A.DPT_MT_DEEE
FROM
T_DEVPART_DPT AS A INNER JOIN Descendants AS T ON (A.DPT_ID = T.ID)
LEFT OUTER JOIN T_UNITPU_UPU AS B ON (A.DPT_UNITP = B.UPU_CODE); |
