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) |
Partager