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
| create or replace PROCEDURE PROC_CIRCUITCADENCIER AS
BEGIN
DELETE FROM CA_CIRCUIT ;
COMMIT ;
INSERT INTO CA_CIRCUIT (CODE_CIRCUIT, DATE_DEBUT,
ID_PAYS, LIB_CIRCUIT, CODE_ENS, DATE_FIN)
SELECT S2.CODE_CIRCUIT, S2.DATE_APPLI, S2.ID_PAYS,
S1.LIB_CIRCUIT, S2.CODE_ENS, S2.DATE_FIN
FROM (
SELECT D.CODE_CIRCUIT, MAX(D.DATE_APPLI) DATE_APPLI, B.ID_PAYS ,
C.CODE_ENS, A.DATE_FIN
FROM CL_CIRCUIT A, CL_CIRCUIT_PAYS B, CL_CIRCUIT_ENSEIGNE C,
CL_CIRCUIT_INFO D
WHERE
D.ID_TYPE_CIRCUIT = '1'
AND A.CODE_CIRCUIT = B.CODE_CIRCUIT
AND B.CODE_CIRCUIT = C.CODE_CIRCUIT
AND C.CODE_CIRCUIT = D.CODE_CIRCUIT
AND C.CODE_ENS IN ('IM', 'DM', 'BT', 'BM')
AND D.DATE_APPLI < (select trunc(sysdate) from dual)
GROUP BY D.CODE_CIRCUIT, B.ID_PAYS ,
C.CODE_ENS, A.DATE_FIN) S2, CL_CIRCUIT_INFO S1
WHERE S2.CODE_CIRCUIT=S1.CODE_CIRCUIT
AND S2.DATE_APPLI=S1.DATE_APPLI ;
COMMIT ; |
