## PCD_REFRESH_V_DROITS_ACTEURS

Doc
Details
Grants
References
Dependencies
Code

TEXT
    PROCEDURE PCD_REFRESH_V_DROITS_ACTEURS
    AS
    BEGIN
    DELETE FROM v_droits_acteurs;
    INSERT INTO v_droits_acteurs(pf_acteur, pf_rel, pf_fonction)
    SELECT DISTINCT pf_acteur, pf_rel, pf_fonction
    FROM
    ( /* partie superadmin */
    SELECT (SELECT p_id FROM groupes WHERE f_type = 1 AND libelle = 'superadmin') AS pf_acteur,
    		r.p_id AS pf_rel,
    		(SELECT p_id FROM fonctions WHERE libelle='responsable') AS pf_fonction
    FROM rels r
    UNION /* racines de la partie publique */
    SELECT -1 AS pf_acteur,
            r.p_id AS pf_rel,
            -1 AS pf_fonction
    FROM rels r
    INNER JOIN visibilites v
    ON r.f_visibilite = v.p_id AND v.libelle IN('PUBLIQUE')
    WHERE (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    AND r.p_id NOT IN(SELECT rer.pf_fils FROM rels_relations rer)
    UNION /* descendants de la partie publique */
    SELECT -1 AS pf_acteur,
            rr.pf_fils AS pf_rel,
            -1 AS pf_fonction
    FROM rels_relations rr
    INNER JOIN rels r
    ON r.p_id = rr.pf_fils AND (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    INNER JOIN visibilites v
    ON r.f_visibilite = v.p_id AND v.libelle IN('PUBLIQUE')
    START WITH rr.pf_pere IN(SELECT re.p_id FROM rels re
                          INNER JOIN visibilites ve
                          ON re.f_visibilite = ve.p_id AND ve.libelle IN('PUBLIQUE')
                          WHERE (re.etat = 'PUBLIEE' OR (re.etat = 'EN ATTENTE' AND re.date_publication < sysdate))
                          AND re.p_id NOT IN(SELECT rer.pf_fils FROM rels_relations rer))
    CONNECT BY rr.pf_pere = PRIOR rr.pf_fils
    UNION /* racines de la partie semi privee */
    SELECT 0 AS pf_acteur,
            r.p_id AS pf_rel,
            -1 AS pf_fonction
    FROM rels r
    INNER JOIN visibilites v
    ON r.f_visibilite = v.p_id AND v.libelle IN('PUBLIQUE', 'SEMI PRIVEE')
    WHERE (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    AND r.p_id NOT IN(SELECT rer.pf_fils FROM rels_relations rer)
    UNION /* descendants de la partie publique */
    SELECT 0 AS pf_acteur,
            rr.pf_fils AS pf_rel,
            -1 AS pf_fonction
    FROM rels_relations rr
    INNER JOIN rels r
    ON r.p_id = rr.pf_fils AND (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    INNER JOIN visibilites v
    ON r.f_visibilite = v.p_id AND v.libelle IN('PUBLIQUE', 'SEMI PRIVEE')
    START WITH rr.pf_pere IN(SELECT re.p_id FROM rels re
                          INNER JOIN visibilites ve
                          ON re.f_visibilite = ve.p_id AND ve.libelle IN('PUBLIQUE', 'SEMI PRIVEE')
                          WHERE (re.etat = 'PUBLIEE' OR (re.etat = 'EN ATTENTE' AND re.date_publication < sysdate))
                          AND re.p_id NOT IN(SELECT rer.pf_fils FROM rels_relations rer))
    CONNECT BY rr.pf_pere = PRIOR rr.pf_fils
    UNION /* racines de la partie privee cote etudiant */
    SELECT ise.pf_groupe AS pf_acteur,
            p.pf_id AS pf_rel,
            0 AS pf_fonction
    FROM programmes p
    INNER JOIN programmes_sessions ps
    ON p.pf_id = ps.f_programme
    INNER JOIN inscriptions_sessions ise
    ON ps.p_id = ise.pf_session
    INNER JOIN semainiers s
    ON ps.f_semainier = s.p_id AND s.date_debut <= sysdate AND sysdate <= s.date_fin
    INNER JOIN rels r
    ON p.pf_id = r.p_id
    AND (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    UNION /* descendants de la partie privee cote etudiant */
    SELECT CONNECT_BY_ROOT ise.pf_groupe AS pf_acteur,
            rr.pf_fils AS pf_rel,
            0 AS pf_fonction
    FROM rels_relations rr
    INNER JOIN rels r
    ON r.p_id = rr.pf_pere AND (r.etat = 'PUBLIEE' OR (r.etat = 'EN ATTENTE' AND r.date_publication < sysdate))
    INNER JOIN rels re
    ON rr.pf_fils = re.p_id AND (re.etat = 'PUBLIEE' OR (re.etat = 'EN ATTENTE' AND re.date_publication < sysdate))
    LEFT OUTER JOIN programmes p
    ON r.p_id = p.pf_id
    LEFT OUTER JOIN programmes_sessions ps
    ON p.pf_id = ps.f_programme
    LEFT OUTER JOIN semainiers s
    ON ps.f_semainier = s.p_id AND s.date_debut <= sysdate AND sysdate <= s.date_fin
    LEFT OUTER JOIN inscriptions_sessions ise
    ON ps.p_id = ise.pf_session
    START WITH s.p_id is not null AND ise.pf_groupe is not null
    CONNECT BY rr.pf_pere = PRIOR rr.pf_fils
    UNION /* racines de la partie privee cote prof */
    SELECT da.pf_acteur AS pf_acteur,
            da.pf_rel AS pf_rel,
            da.f_fonction AS pf_fonction
    FROM droits_acteurs da
    WHERE da.pf_acteur IN(SELECT pf_groupe FROM acteurs)
    UNION /* descendants de la partie privee cote prof */
    SELECT CONNECT_BY_ROOT da.pf_acteur AS pf_acteur,
            rr.pf_fils AS pf_rel,
            CONNECT_BY_ROOT da.f_fonction AS f_fonction
    FROM rels r
    INNER JOIN rels_relations rr
    ON r.p_id = rr.pf_pere
    LEFT OUTER JOIN droits_acteurs da
    ON r.p_id = da.pf_rel
    START WITH da.pf_acteur is not null
    CONNECT BY r.p_id = PRIOR rr.pf_fils
    UNION /* racines de la partie privee cote prof avec des groupes */
    SELECT gp.pf_fils AS pf_acteur,
            da.pf_rel AS pf_rel,
            da.f_fonction AS f_fonction
    FROM droits_acteurs da
    INNER JOIN groupes_parentes gp
    ON da.pf_acteur = gp.pf_pere
    UNION /* descendants de la partie privee cote prof avec des groupes */
    SELECT CONNECT_BY_ROOT gp.pf_fils AS pf_acteur,
            rr.pf_fils AS pf_rel,
            CONNECT_BY_ROOT da.f_fonction AS pf_fonction
    FROM rels r
    INNER JOIN rels_relations rr
    ON r.p_id = rr.pf_pere
    LEFT OUTER JOIN droits_acteurs da
    ON r.p_id = da.pf_rel
    LEFT OUTER JOIN groupes_parentes gp
    ON da.pf_acteur = gp.pf_pere
    START WITH da.pf_acteur is not null AND gp.pf_fils is not null
    CONNECT BY r.p_id = PRIOR rr.pf_fils);
    COMMIT;
    END PCD_REFRESH_V_DROITS_ACTEURS;