-- 1-- RECHERCHER De l'ACTE
SELECT * FROM p_acte
WHERE  -- codeActe='AC0000917' 
 libelle LIKE '%DOPPLER%'
-- AND codeGarantie = 'IMA' 
ORDER BY libelle

-- 2 mise à jours du code acte

UPDATE p_prestationactes SET codeActe='AC0000917'  WHERE codeActe = 'AC0000328';

UPDATE p_demandeautorisation SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_demandeententeprealable SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_demandeententeprealableopt SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';


UPDATE actebaremeaconserver SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_demandepec SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_detailexamen SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_detailmiseenobservation SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_detailoptique SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_prescriptionseance SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_prestationexamens SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_prestationoptiques SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_prestationseances SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_sousacte SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_sousactebase SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_prestationacteshospitalisation SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_detailmiseenobservation SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE tarifacte SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE tarifgroupeprestataire SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE tarifprestataire SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

UPDATE p_acte_collegelienparente SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';


UPDATE actesbaremepriseencharge SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';


  --  suppression des lignes comportant l'acte à remplacer dans les bareme
  -- parametre1 codeActeasupprimer
  -- parametre2  codeActeaconverver
  
 SELECT * FROM actesbaremepriseencharge WHERE codeActe = 'AC0000917'
 AND idBaremePriseEnCharge 
  IN ( SELECT idBaremePriseEnCharge FROM actesbaremepriseencharge WHERE codeActe = 'AC0000328');
  

UPDATE actesbaremepriseencharge SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';


UPDATE actesbaremepriseencharge a JOIN p_acte b ON (a.codeActe=b.codeActe)
SET a.libelle = b.libelle,a.libelleEng = b.libelleEng,a.codeTarifActe = b.codeTarifActe
WHERE a.codeActe = 'AC0000328';


UPDATE p_detailtarifactepolice SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';

--  suppression des lignes comportant l'acte à remplacer dans les tarifs prestataire
-- (codeActeaconverver,codeActeasupprimer)
-- parametre1 codeActeaconverver
-- parametre2 codeActeasupprimer

 SELECT a.* FROM 
 p_detailtarifacteprestataire a INNER JOIN 
 (SELECT codePrestataire,COUNT(0) FROM
 -- p_detailtarifacteprestataire WHERE codeActe IN ('AC0000917','AC0000328')
 p_detailtarifacteprestataire WHERE codeActe IN ('AC0000917','AC0000328')
 GROUP BY codePrestataire 
 HAVING COUNT(0) > 1) b ON (a.codePrestataire = b.codePrestataire) WHERE a.codeActe = 'AC0000328';
 
 UPDATE p_detailtarifacteprestataire SET codeActe='AC0000917'   WHERE codeActe = 'AC0000328';
 
-- DELETE FROM p_detailtarifacte  WHERE codeActe = 'AC0000917'

 -- 4 suppression definitive de l'acte
  DELETE  FROM p_acte WHERE codeActe = 'AC0000917';
  DELETE FROM a_acte WHERE codeActe = 'AC0000917';