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
| create view v_etat_parc as (
with nowm as(
select d.societe as soc_now,d.agence as ag_now,d.type as typ_now,d.affectation as aff_now,d.vehicule as vehic_now,
d.date_from as date_from_now,d.date_to as date_to_now,d.version as ver_now,d.date_sortie_parc as sortie_now
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > sysdate)
and to_char(d.date_to)='31/12/99'
),
beforem1 as(
select d.societe as soc_m1,d.agence as ag_m1,d.type as typ_m1,d.affectation as aff_m1,d.vehicule as vehic_m1,
d.date_from as date_from_m1,d.date_to as date_to_m1,d.version as ver_m1,d.date_sortie_parc as sortie_m1
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > last_day(add_months(trunc(sysdate,'mm'),-1)))
and ((to_char(d.date_to)='31/12/99' and d.date_from <last_day(add_months(trunc(sysdate,'mm'),-1))+1)
or (d.date_to <= sysdate
and d.date_from = (select max(dd.date_from)
from d_vehicule dd
where dd.vehicule=d.vehicule
and (dd.date_from <last_day(add_months(trunc(sysdate,'mm'),-1))+1)
)))
and (d.date_from <last_day(add_months(trunc(sysdate,'mm'),-1)) and d.date_to>=last_day(add_months(trunc(sysdate,'mm'),-1)))
),
beforem2 as(
select d.societe as soc_m2,d.agence as ag_m2,d.type as typ_m2,d.affectation as aff_m2,d.vehicule as vehic_m2,
d.date_from as date_from_m2,d.date_to as date_to_m2,d.version as ver_m2,d.date_sortie_parc as sortie_m2
from d_vehicule d
where (d.date_sortie_parc is null or d.date_sortie_parc > last_day(add_months(trunc(sysdate,'mm'),-2)))
and ((to_char(d.date_to)='31/12/99' and d.date_from <last_day(add_months(trunc(sysdate,'mm'),-2))+1)
or (d.date_to <= sysdate
and d.date_from = (select max(dd.date_from)
from d_vehicule dd
where dd.vehicule=d.vehicule
and (dd.date_from <last_day(add_months(trunc(sysdate,'mm'),-2))+1)
)))
and (d.date_from <last_day(add_months(trunc(sysdate,'mm'),-2)) and d.date_to>=last_day(add_months(trunc(sysdate,'mm'),-2)))
)
select unique n.soc_now as societe_m, n.ag_now as agence_m,
d.vehicule, d.type, n.aff_now as affectation_m, n.date_from_now as date_from_m,
n.date_to_now as date_to_m,n.ver_now as version_m, n.sortie_now,
b.soc_m1 as societe_m1, b.ag_m1 as agence_m1,
b.aff_m1 as affectation_m1, b.date_from_m1 as date_from_m1,
b.date_to_m1 as date_to_m1, b.ver_m1 as version_m1, b.sortie_m1,
bb.soc_m2 as societe_m2, bb.ag_m2 as agence_m2,
bb.aff_m2 as affectation_m2, bb.date_from_m2 as date_from_m2,
bb.date_to_m2 as date_to_m2, bb.ver_m2 as version_m2, bb.sortie_m2
from d_vehicule d, nowm n, beforem1 b, beforem2 bb
where d.vehicule=n.vehic_now(+)
and d.vehicule=b.vehic_m1(+)
and d.vehicule=bb.vehic_m2(+)
) |
