## PCD_SUPPRIMER_EDT

Doc
Details
Grants
References
Dependencies
Code

TEXT
    PROCEDURE          "PCD_SUPPRIMER_EDT" (p_annee_civile number,p_semaine number,p_site varchar2, p_annee varchar2) as

    v_annee varchar2(10);
    v_id_annee number;
    v_id_site number;
    v_date_debut date;
    v_cpt number;

    begin
    	if 		p_annee = 'L1' then v_annee := 'CPI1';
    	elsif	p_annee = 'L2' then v_annee := 'CPI2';
    	elsif	p_annee = 'L3' then v_annee := 'ING1';
    	elsif	p_annee = 'M1' then v_annee := 'ING2';
    	elsif	p_annee = 'M2' then v_annee := 'ING3';
      elsif p_annee = 'DIVERS' then v_annee := 'DIVERS';
    	end if;

    dbms_output.put_line('libelle annee '|| p_annee);
    	select p_id into v_id_annee from annees where libelle = v_annee;
    	select p_id into v_id_site from groupes where upper(libelle) = upper(p_site);
    	v_date_debut := date_jour(p_annee_civile,p_semaine,1);

    	begin
      dbms_output.put_line('semaine du '|| p_semaine || '   annee '|| v_annee);
    dbms_output.put_line('id_site du '|| v_id_site);
    dbms_output.put_line('id_annee du '|| v_id_annee);

     select count(*) into v_cpt from  creneaux where
    		pf_edt in (select p_id from edt where
    			annee_civile = p_annee_civile and f_site = v_id_site and semaine = p_semaine
    			and to_char(date_jour_heure_edt(annee_civile,semaine,jour,hf),'YY/MM/DD HH24:MI:SS') >= to_char(sysdate,'YY/MM/DD HH24:MI:SS')
    			and f_programme_session in (select p_id from programmes_sessions
    			where f_programme in (select pf_id from programmes where f_annee = v_id_annee)
          and f_semainier in (select p_id from semainiers where
    			date_debut <= v_date_debut and v_date_debut <= date_fin))
          )
              ;
      dbms_output.put_line('nb créneaux '|| v_cpt);
      delete creneaux where
    		pf_edt in (select p_id from edt where
    			annee_civile = p_annee_civile and f_site = v_id_site and semaine = p_semaine
    			and to_char(date_jour_heure_edt(annee_civile,semaine,jour,hf),'YY/MM/DD HH24:MI:SS') >= to_char(sysdate,'YY/MM/DD HH24:MI:SS')
    			and f_programme_session in (select p_id from programmes_sessions
    				where f_programme in (select pf_id from programmes where f_annee = v_id_annee)
    				and f_semainier in (select p_id from semainiers where
    					date_debut <= v_date_debut and v_date_debut <= date_fin))
            );
      exception WHEN no_data_found then dbms_output.put_line('rien créneaux');
      end;
      begin
    	delete edt where
    			annee_civile = p_annee_civile and f_site = v_id_site and semaine = p_semaine
    			and to_char(date_jour_heure_edt(annee_civile,semaine,jour,hf),'YY/MM/DD HH24:MI:SS') >= to_char(sysdate,'YY/MM/DD HH24:MI:SS')
    			and f_programme_session in (select p_id from programmes_sessions
    				where f_programme in (select p_id from programmes where f_annee = v_id_annee)
    				and f_semainier in (select p_id from semainiers where
    					date_debut <= v_date_debut and v_date_debut <= date_fin));
    	exception when no_data_found then dbms_output.put_line('rien edt');
      end;
    end;