SEULEMENT  sur PRD2 pour Entoria 

  • MMH_ENTORIA1#0
  • GENERALI_ENTORIA1#0




Pnum - CourtierCédant = Courtier actuel du dossier

Pnum - Courtier Cessionnaire = Nouveau courtier 




Convertir le numéro de dossier ENTORIA en MS

select distinct SUBSTR(ID_CONTRACT_TECH, 1, 9) ID_BO_DEVIS   from t_emp_devis   where  `NUM_CONTRAT_EXTERNE` in (168515,17713)





Récupération des informations du Courtier en fonction d code ENTORIA

  • Pour Ms
    • ID_FO_BROKER = @ancienFoBroker ou @nouveauFoBroker
    • ID_FO_DISTRIB = @ancienFoDistrib ou @nouveuaFoDistrib
  • Pour ALOGIE
    • ID_BO_BROKER = courtierSource ou courtierCible


select
ext.id_broker_externe as id_externe,
inter.ID_BO_BROKER, inter.id_alph_broker as id_fo_broker,
inter.distributeur as id_fo_distrib,
inter.corporatename
from v_broker_real inter
join t_bo_broker ext on inter.id_bo_broker = ext.id_bo_broker
where inter.ENTITYTYPE != 'DISTRIBUTEUR' and ext.id_broker_externe like ('%772366');


Remarque:

Si le courtier n'existe pas il faut le créer via la procédure Création CODE ENTORIA



        1. TRANSFERT sur BASE MS



Vérification du rattachement du dossier au courtier

SELECT
v_broker_real.ID_BO_BROKER,
v_broker_real.id_alph_broker as ID_FO_BROKER_BROKER,
v_broker_real.distributeur as ID_FO_DISTRIB_BROKER,
t_devis.ID_FO_BROKER as ID_FO_BROKER_DEVIS,
t_devis.ID_FO_DISTRIB as ID_FO_DISTRIB_DEVIS,
v_broker_real.corporatename,
v_broker_real.CIVILITY,
v_broker_real.FIRSTNAME,
v_broker_real.LASTNAME,
t_devis.ID_BO_DEVIS
FROM v_broker_real
JOIN t_devis ON t_devis.ID_FO_BROKER = v_broker_real.id_alph_broker
WHERE v_broker_real.ENTITYTYPE != 'DISTRIBUTEUR' AND t_devis.ID_BO_DEVIS in ('900641215');



Requête de transfert sur Ms

SET @ancienFoBroker = 'ENT_85801';
SET @ancienFoDistrib = 'D-ENTORIA';
SET @nouveauFoBroker = 'ENT_571331';
SET @nouveauFoDistrib = 'D-ENTORIA';
UPDATE t_devis set ID_FO_BROKER=@nouveauFoBroker, ID_FO_DISTRIB=@nouveauFoDistrib
WHERE ID_FO_BROKER=@ancienFoBroker AND id_fo_distrib=@ancienFoDistrib AND id_bo_devis in ('900641215');
UPDATE t_client_identity
LEFT JOIN t_assure_devis ON t_assure_devis.id_client_identity = t_client_identity.id_tech_idtclt
LEFT JOIN t_devis ON t_devis.id_tech_devis = t_assure_devis.id_devis
SET id_broker=@nouveauFoBroker, id_distrib=@nouveauFoDistrib
WHERE t_devis.id_bo_devis in ('900641215');


Request de contrôle

SELECT
v_broker_real.ID_BO_BROKER,
v_broker_real.id_alph_broker as ID_FO_BROKER_BROKER,
v_broker_real.distributeur as ID_FO_DISTRIB_BROKER,
t_devis.ID_FO_BROKER as ID_FO_BROKER_DEVIS,
t_devis.ID_FO_DISTRIB as ID_FO_DISTRIB_DEVIS,
v_broker_real.corporatename,
v_broker_real.CIVILITY,
v_broker_real.FIRSTNAME,
v_broker_real.LASTNAME,
t_devis.ID_BO_DEVIS
FROM v_broker_real
JOIN t_devis ON t_devis.ID_FO_BROKER = v_broker_real.id_alph_broker
WHERE v_broker_real.ENTITYTYPE != 'DISTRIBUTEUR' AND t_devis.ID_BO_DEVIS in ('900641215');




        2. TRANSFERT ALOGIE 


Vérification de la présence du C_APP2

SELECT N_SS, NEVG, C_ETAT, C_APP1, C_APP2, C_APP3  FROM bpsi.persempr WHERE N_SS  LIKE '900643894%'; 


Remarques:
    Si C_APP2 = 0 => pas de C_APP2
    Si C_App2 existe mais est différent de C_APP1 "STOP"

   




Requête si il n'y a pas de C_APP2

@set courtierSource = '862'
@set courtierCible = '2694'
@set numDevis = 900641215'
@set nevg = select distinct(NEVG) from persempr where numprospect in (${numDevis})
update pppret set numapporteur = ${courtierCible} where numapporteur = ${courtierSource} and numprospect in (${numDevis});
update persempr set c_app1 = ${courtierCible} where c_etat = 0 and c_app1 = ${courtierSource} and nevg in (${nevg});
update quitemad q set c_app1 = ${courtierCible} where q.c_app1 = ${courtierSource} and nevg in (${nevg});
update quitapp a set a.c_apporteur = ${courtierCible} where a.c_apporteur = ${courtierSource} and nevg in (${nevg}) ;
update comptapp set c_app = ${courtierCible} where c_app = ${courtierSource} and nevg in (${nevg}) and c_edite=0;



Requête si il y a un C_APP2


@set courtierSource = '862'
@set courtierCible = '2694'
@set numDevis = '900641215'
@set nevg = select distinct(NEVG) from persempr where numprospect in (${numDevis})
update pppret set numapporteur = ${courtierCible} where numapporteur = ${courtierSource} and numprospect in (${numDevis});
update persempr set c_app1 = ${courtierCible} where c_etat = 0 and c_app1 = ${courtierSource} and nevg in (${nevg});
update persempr set c_app2 = ${courtierCible} where c_etat = 0 and c_app2 = ${courtierSource} and nevg in (${nevg});
update quitemad q set c_app1 = ${courtierCible} where q.c_app1 = ${courtierSource} and nevg in (${nevg});
update quitemad q set c_app2 = ${courtierCible} where q.c_app2 = ${courtierSource} and nevg in (${nevg});
update quitapp a set a.c_apporteur = ${courtierCible} where a.c_apporteur = ${courtierSource} and nevg in (${nevg}) ;
update comptapp set c_app = ${courtierCible} where c_app = ${courtierSource} and nevg in (${nevg}) and c_edite=0;



Requêtes de contrôle


SELECT N_SS, NEVG, C_ETAT, C_APP1, C_APP2, C_APP3  FROM bpsi.PERSEMPR WHERE N_SS LIKE '900641824%';
SELECT * FROM BPSI.COMPTAPP WHERE NEVG ='1632586';



ATTENTION: EN CAS DE CREATION DE COMPTE  ENTORIA PREVENIR ODILE TEYSSENDIER