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
| CREATE OR REPLACE PROCEDURE TSA.P_GDAI_TO_TSA (pNUMINT IN TSA.NUMINT%TYPE) AS
BEGIN
-- Faire un curseurDA qui prend tous les champs de GDA_DA
DECLARE CURSOR curseurDA IS
SELECT * FROM DBGDA.GDA_DA WHERE DBGDA.NUMINTDEM = pNUMINT;
-- Ensuite faire un IF Curseur.NUMDA NOT IN (SELECT * NUMDA FROM TSA.TSA_PROJET) THEN (la procédure) ELSE Curseur+1 END IF)
BEGIN
FOR DA_rec in curseurDA
LOOP
IF DA_rec.NUMDA NOT IN (SELECT TSA.TSA_PROJET.NUMDA FROM TSA.TSA_PROJET) THEN
INSERT INTO TSA.TSA_PROJET(
ETATPROJ,
NUMINT,
LIBPROJ,
COUTPREV,
TYPTAXE,
NUMCOM,
DATTRANSMISSION,
NUMDA,
CODREFUS,
USERCRE,
DATCRE,
USERMOD,
DATMOD
)
VALUES(
/* ETATPROJ */ CASE WHEN DA_rec.ETATDA IN ('S100', 'S110')
THEN 'P30' -- Transmis
WHEN DA_rec.ETATDA = 'S120' -- DA Rattachée à au moins une opération
AND EXISTS(SELECT *
FROM DBGDA.V_DA_LISTE_ETATOPER t1
JOIN DBGDA.GDA_DA t2
ON t1.NUMDA = t2.NUMDA
JOIN DBGDA.V_OPER_LISTE_ETATCONV t3
ON t1.NUMOPER = t3.NUMOPER
WHERE t2.NUMINTDEM = pNUMINT
AND t3.ETATCONV >= 130 AND t3.ETATCONV < 200
) THEN 'P60' -- Conventionné
-- else case
WHEN DA_rec.ETATDA = 'S120'
AND EXISTS(SELECT *
FROM DBGDA.V_DA_LISTE_ETATOPER t1
JOIN DBGDA.GDA_DA t2
ON t1.NUMDA = t2.NUMDA
WHERE t2.NUMINTDEM = pNUMINT
AND t1.ETATOPER >= 110 AND t1.ETATOPER < 190
) THEN 'P50' -- Décidé
WHEN DA_rec.ETATDA = 'S120'
AND EXISTS(SELECT *
FROM DBGDA.V_DA_LISTE_ETATOPER t1
JOIN DBGDA.GDA_DA t2
ON t1.NUMDA = t2.NUMDA
WHERE t2.NUMINTDEM = pNUMINT
AND t1.ETATOPER >= 106 AND t1.ETATOPER < 190
) THEN 'P40' -- Complet
WHEN DA_rec.ETATDA = 'S120'
AND EXISTS(SELECT *
FROM DBGDA.V_DA_LISTE_ETATOPER t1
JOIN DBGDA.GDA_DA t2
ON t1.NUMDA = t2.NUMDA
WHERE t2.NUMINTDEM = pNUMINT
AND t1.ETATOPER = 100
) THEN 'P30' -- Initialisée
ELSE NULL
END
,
/* NUMINT */ DA_rec.NUMINTDEM AS NUMINT,
/* LIBPROJ */ DA_rec.OBJDA AS LIBPROJ,
/* COUTPREV */ DA_rec.MTGLOB AS COUTPREV,
/* TYPTAXE */ DA_rec.TYPTAXE AS TYPTAXE,
/* NUMCOM */ DA_rec.NUMCOMTRAV AS NUMCOM,
/* DATTRANSMISSION */ DA_rec.DATDEM AS DATTRANSMISSION,
/* NUMDA */ DA_rec.NUMDA AS NUMDA,
/* CODREFUS */ DA_rec.CODREFUS AS CODREFUS,
/* USERCRE */ 'Batch AI' AS USERCRE,
/* DATCRE */ SYSDATE AS DATECRE,
/* USERMOD */ NULL AS USERMOD,
/* DATMOD */ NULL AS DATMOD
)
-- Pour chaque DA non prise en compte dans le test précédent
UPDATE DBGDA.GDA_DA
SET DBGDA.GDA_DA.ORIGINE = 'GDAI'
DBGDA.GDA_DA.PROJTSA = 'N'
WHERE DBGDA.GDA_DA.NUMINTDEM = pNUMINT
AND DBGDA.GDA_DA.NUMDA = DA_rec.NUMDA
AND DA_rec.ETATDA NOT IN ('S100', 'S110', 'S120');
-- Si le numéro de programme est >= 10, à l’état S100 – Initial ou S110 – Recevable ou S120 – Rattachée à au moins une opération dans un état < 170 - Soldée: Mise à jour des champs de la DA : Origine = ‘GDAI’ et Projet Téléservice= ‘N’
UPDATE DBGDA.GDA_DA
SET DBGDA.GDA_DA.ORIGINE = (CASE WHEN DA_rec.NUMPROG >= 10
AND DA_rec.ETATDA IN ('S110', 'S120')
AND (SELECT DBGDA.V_DA_LISTE_ETATOPER.ETATOPER FROM DBGDA.GDA_DA JOIN DBGDA.V_DA_LISTE_ETATOPER ON DBGDA.GDA_DA.NUMDA=DBGDA.V_DA_LISTE_ETATOPER.NUMDA WHERE DBGDA.GDA_DA.NUMINTDEM=pNUMINT) > 170 THEN 'GDAI' ELSE DA_rec.ORIGINE END),
DBGDA.GDA_DA.PROJTSA = (CASE WHEN DA_rec.NUMPROG >= 10
AND DA_rec.ETATDA IN ('S110', 'S120')
AND (SELECT DBGDA.V_DA_LISTE_ETATOPER.ETATOPER FROM DBGDA.GDA_DA JOIN DBGDA.V_DA_LISTE_ETATOPER ON DBGDA.GDA_DA.NUMDA=DBGDA.V_DA_LISTE_ETATOPER.NUMDA WHERE DBGDA.GDA_DA.NUMINTDEM=pNUMINT) > 170 THEN 'O' ELSE DA_rec.PROJTSA END)
WHERE DA_rec.NUMINTDEM = pNUMINT
END IF;
END LOOP;
END;
END; |
