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;
/ |
Partager