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
| create or replace view AlARMS as
(select distinct
SYS.mst_perspective_id,
SYS.meta_perspective_id,
SYS.fst_perspective_id,
SYS.snd_perspective_id,
SYS.system,
REF_SYS.description as system_desc,
REF_SYS.system_a as system_a,
REF_SYS.system_b as system_b,
SYS.period_type as period_type,
REF_PT.description as period_type_desc,
ALRM.equation_id,
decode(ALRM.period_type, 'DAILY', ALRM.period, ALRM.year || ALRM.period) as period,
TO_NUMBER(ALRM.year) as YEAR,
ALRM.run_id,
decode(ALRM.run_id, '-1','', (select runs.mois_run from RAID_T_ADV_FAC_RUN runs where runs.date_emis = ALRM.period)) as mois_run,
ALRM.alarm_id, ALRM.alarm_type as alarm_status, ALRM.status,
ALRM.equation_type,
ALRM.side_a , ALRM.side_b,ALRM.ecart, ALRM.ecart_perc,
ALRM.severity,ALRM.criticity,
ALRM.platform,
ALRM_DET.term_id,
ALRM_DET.term_value,
EQ_FORM.description as equation_desc,
EQ_FORM.short_desc as equation_formula,
EQ_FORM.alarm_type as alarm_type,
EQ_FORM.cra_type as cra_type,
EQ_FORM.unite as unite,
PERS_TERM.code_term as pers_code_term,
PERS_TERM.term_desc as pers_term_desc,
PERS_TERM.term_order as pers_term_order,
PERS_TERM.term_desc_l1 as pers_term_desc_l1,
PERS_TERM.term_desc_l2 as pers_term_desc_l2,
PERS_TERM.col_span_l1 as pers_col_span_l1,
PERS_TERM.col_span_l2 as pers_col_span_l2,
PERS_MAX.term_nb,
AGR_EQ.agregat_eq as agr_eq,
ALRM.criticity || nvl(ALRM.greenvisible, '0') as greenvisible
From
RAID_T_ADV_SYSTEMS SYS,
RAID_T_ADV_SYSTEMS_EQUATIONS SYS_EQ,
RAID_T_ADV_REF_SYSTEMS REF_SYS,
RAID_T_ADV_REF_PERIOD_TYPE REF_PT,
RAID_T_RC_ALARMS ALRM,
RAID_T_RC_ALARMS_DETAILS ALRM_DET,
RAID_T_ADV_EQUATION_FORMULAS EQ_FORM,
RAID_T_ADV_TERMS_INDICATEURS TERM_IND,
RAID_T_ADV_PERSPECTIVES_TERMS PERS_TERM,
RAID_T_ADV_AGREGAT_EQUATIONS AGR_EQ,
(select perspective_id, max(term_order)+1 as term_nb
from RAID_T_ADV_PERSPECTIVES_TERMS group by perspective_id) PERS_MAX
where
(SYS_EQ.system = SYS.system) AND
(REF_SYS.system = SYS.system) AND
(SYS.mst_perspective_id = PERS_TERM.perspective_id) AND
(SYS.mst_perspective_id = PERS_MAX.perspective_id) AND
(PERS_TERM.code_term = TERM_IND.code_term) AND
(TERM_IND.code_indicateur = ALRM_DET.term_id) AND
(REF_PT.period_type = SYS.period_type) AND
(ALRM.alarm_type = 'CALC') AND
(SYS_EQ.equation = ALRM.equation_id) AND
(AGR_EQ.equation_id = ALRM.equation_id) AND
(SYS.period_type = ALRM.period_type) AND
(ALRM.period_type = ALRM_DET.period_type) AND
(ALRM.platform = ALRM_DET.platform) AND
(ALRM.period = ALRM_DET.period) AND
(ALRM.year = ALRM_DET.year) AND
(ALRM.equation_type = ALRM_DET.equation_type) AND
(ALRM.equation_id = ALRM_DET.equation_id) AND
(EQ_FORM.equation_id = ALRM_DET.equation_id) AND
(EQ_FORM.period_type = ALRM_DET.period_type) AND
(EQ_FORM.equation_type = ALRM_DET.equation_type)
order by mst_perspective_id, period) |
