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
|
select count(*) from (
SELECT
decode(tab_result.typ, 2, FUNC.cc2,
37, FUNC.cc3) cc,
tab_result.nid ,
tab_result.sect ,
tab_result.nom ,
tab_result.codif ,
tab_result.m_sys,
tab_result.m_aer,
tab_result.m_tech,
tab_result.m_ll
FROM
(
SELECT
tabl.cle,
tabl.typ,
tabl.nid,
tabl.nom,
tabl.sect,
tabl.codif,
max(tabl.m_sys) m_sys,
max(tabl.m_aer) m_aer,
max(tabl.m_tech) m_tech,
max(tabl.m_ll) m_ll
FROM (
SELECT
N.cle,
N.typ,
N.nid,
N.nom,
N.sect,
D.codif,
null m_sys,
null m_aer,
null m_tech,
null m_ll
FROM MAT N INNER JOIN
SIM D ON substr(N.nid, -2) = D.codif
WHERE D.codifu = :param
and N.typ in (2,37)
UNION
SELECT
N1.cle,
N1.typ,
N1.nid,
N1.nom,
N1.sect,
D1.codif,
null m_sys,
null m_aer,
null m_tech,
CHARE_LL.CARAC_N2 m_ll
FROM MAT N1 LEFT OUTER JOIN
CHARE CHARE_LL ON N1.cle = CHARE_LL.cle INNER JOIN
SIM D1 ON substr(N1.nid, -2) = D1.codif
WHERE D1.codifu = :param AND (CHARE_LL.CARAC_N1 = 'Maintenance LL')
AND N1.typ in (2,37)
UNION
SELECT
N2.cle,
N2.typ,
N2.nid,
N2.nom,
N2.sect,
D2.codif,
null m_sys,
null m_aer,
CHARE_Tec.CARAC_N2 m_tech,
null m_ll
FROM MAT N2 LEFT OUTER JOIN
CHARE CHARE_Tec ON N2.cle = CHARE_Tec.cle INNER JOIN
SIM D2 ON substr(N2.nid, -2) = D2.codif
WHERE D2.codifu = :param AND (CHARE_Tec.CARAC_N1 = 'Maintenance Environnement Technique')
and N2.typ in (2,37)
UNION
SELECT
N3.cle,
N3.typ,
N3.nid,
N3.nom,
N3.sect,
D3.codif,
null m_sys,
CHARE_Aer.CARAC_N2 m_aer,
null m_tech,
null m_ll
FROM MAT N3 LEFT OUTER JOIN
CHARE CHARE_Aer ON N3.cle = CHARE_Aer.cle INNER JOIN
SIM D3 ON substr(N3.nid, -2) = D3.codif
WHERE D3.codifu = :param AND (CHARE_Aer.CARAC_N1 = 'Maintenance des Aériens')
and N3.typ in (2,37)
UNION
SELECT
N4.cle,
N4.typ,
N4.nid,
N4.nom,
N4.sect,
D4.codif,
CHARE_Syst.CARAC_N2 m_sys,
null m_aer,
null m_tech,
null m_ll
FROM MAT N4 LEFT OUTER JOIN
CHARE CHARE_Syst ON N4.cle = CHARE_Syst.cle INNER JOIN
SIM D4 ON substr(N4.nid, -2) = D4.codif
WHERE D4.codifu = :param AND (CHARE_Syst.CARAC_N1 = 'Maintenance Système')
and N4.typ in (2,37)
) tabl inner join
MAT_state on MAT_state.cle = tabl.cle
where MAT_state.STATE_KEY = 3
group by
tabl.cle,
tabl.typ,
tabl.nid,
tabl.nom,
tabl.sect,
tabl.codif
having (max(tabl.m_aer) is null or max(tabl.m_sys) is null
or max(tabl.m_tech) is null or max(tabl.m_ll) is null)
order by tabl.cle
) tab_result inner join
FUNC on tab_result.cle = FUNC.cle and tab_result.typ = FUNC.typ
) |
