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
109
110
111
112
113
|
CREATE OR REPLACE FUNCTION GetItvList (pNumItv INTEGER, pMdtId IN MDT.MDTIDINI%TYPE, pItvId INTEGER, pRolId INTEGER)
RETURN VARCHAR2
IS
/* Fonction utilisee pour la generation des etats
Description : obtenir la liste des intervenants (Rolnom + ' ' + ItvNom) concatener par 'ALT + ENtree' pour un mandat donne)
Entrees : pNumItv - 0 : Tous les intervenants
1 : Tremier intervenant
2 : Tous les intervenants ou Tous sauf le premier
pMdtId : le numero du syst. du mandat
pItvId : l'identifiant de l'intervenant
pRolId ; l'identifiant du role
*/
sResult VARCHAR2(1000) := '';
sMdtId INTEGER;
itvid1 INTEGER;
rolid1 INTEGER;
-- Desclaration du cursor qui va contenir la liste des itvs sauf itv1 et itv2
CURSOR curItvs(pmdtid IN INTEGER, pitvid IN INTEGER, prolid IN INTEGER) IS
SELECT ROLNOM, ITVNOM
FROM
(
SELECT ROL.ROLNOM, ITV.ITVNOM ITVNOM
FROM ITV
INNER JOIN ACT ON ITV.ITVID = ACT.ITVID
INNER JOIN ROL ON ROL.ROLID = ACT.ROLID
WHERE ACT.MDTID = pmdtid
AND (
(pitvid IS NULL AND prolid IS NULL) OR
(pitvid IS NULL AND 1 IS NOT NULL) OR
(pitvid IS NOT NULL AND prolid IS NULL AND ACT.ITVID <> pitvid) OR
(pitvid IS NOT NULL AND prolid IS NOT NULL AND (ACT.ROLID <> prolid OR ACT.ITVID <> pitvid))
)
ORDER BY ROL.ROLNOM, ITV.ITVNOM
);
BEGIN
sMdtId := pMdtId;
IF (pItvId IS NULL AND pRolId IS NULL) THEN
SELECT ITVID, ROLID INTO itvid1, rolid1
FROM
(
SELECT ROWNUM num, ITVID, ROLID
FROM
(
SELECT ITV.ITVID, ROL.ROLID
FROM ITV
INNER JOIN ACT ON ITV.ITVID = ACT.ITVID
INNER JOIN ROL ON ROL.ROLID = ACT.ROLID
WHERE ACT.MDTID = sMdtId
ORDER BY ROL.ROLNOM, ITV.ITVNOM
)
)
WHERE num = 1;
ELSE
itvid1 := pItvId;
rolid1 := pRolId;
END IF;
-- Ontenir le premier intervenant (Rele + Nom)
IF (pNumItv = 1) THEN
SELECT ROLITVOM
INTO sResult
FROM
(
SELECT ROWNUM num, ROLITVOM
FROM
(
SELECT ROL.ROLNOM || ' ' || ITV.ITVNOM ROLITVOM
FROM ITV
INNER JOIN ACT ON ITV.ITVID = ACT.ITVID
INNER JOIN ROL ON ROL.ROLID = ACT.ROLID
WHERE ACT.MDTID = sMdtId
AND (itvid1 IS NULL OR ACT.ITVID = itvid1)
AND (rolid1 IS NULL OR ACT.ROLID = rolid1)
ORDER BY ROL.ROLNOM, ITV.ITVNOM
)
)
WHERE num = 1;
-- Obtenir les autres intervenants
ELSIF (pNumItv = 0) THEN
FOR ITVS IN curItvs(sMdtId, pItvId, pRolId)
LOOP
IF (LENGTH(TO_CHAR(sResult || ITVS.ROLNOM || ' ' || ITVS.ITVNOM || CHR(10))) < 10000) THEN
sResult := sResult || ITVS.ROLNOM || ' ' || ITVS.ITVNOM || CHR(10);
END IF;
END LOOP;
ELSIF (pNumItv = 2) THEN
SELECT ITVID, ROLID INTO itvid1, rolid1
FROM
(
SELECT ROWNUM num, ITVID, ROLID
FROM
(
SELECT ITV.ITVID, ROL.ROLID
FROM ITV
INNER JOIN ACT ON ITV.ITVID = ACT.ITVID
INNER JOIN ROL ON ROL.ROLID = ACT.ROLID
WHERE ACT.MDTID = sMdtId
AND (pItvId IS NULL OR ACT.ITVID = pItvId)
AND (pRolId IS NULL OR ACT.ROLID = pRolId)
ORDER BY ROL.ROLNOM, ITV.ITVNOM
)
)
WHERE num = 1;
FOR ITVS IN curItvs(sMdtId, itvid1, rolid1)
LOOP
IF (LENGTH(TO_CHAR(sResult || ITVS.ROLNOM || ' ' || ITVS.ITVNOM || CHR(10))) < 10000) THEN
sResult := sResult || ITVS.ROLNOM || ' ' || ITVS.ITVNOM || CHR(10);
END IF;
END LOOP;
END IF;
RETURN sResult;
END;
/ |
