/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
update <PREFIXE_HA>DOA_STRUCT set NOMSQLDW=upper(NOMSQLDW)
update <PREFIXE_HA>DOA_DATA set NOMSQLDW=upper(NOMSQLDW) 

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
/*if  exists (select * from sysobjects where name='DOA_DATA' and xtype='U') drop table DOA_DATA*/
truncate table <PREFIXE_DW>DOA_DATA

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
insert into <PREFIXE_DW>DOA_DATA (DOAD_ID_DOA,DOAD_MODELE,DOAD_REFERENCE,DOAD_LIBELLE,DOAD_DESCRIPTION,DOAD_ID_TYPE,DOAD_LIBELLE_TYPE,DOAD_CODE_OPERATEUR,DOAD_PROPRIETAIRE,
DOAD_STATUT,DOAD_ID_ETAT_AVANCEMENT,DOAD_CODE_PRESCRIPTEUR,DOAD_CODE_CENTRE_EMETTEUR,DOAD_DATE_CREATION,DOAD_DATE_DEBUT,DOAD_DATE_FIN,DOAD_ID_DONNEE,DOAD_TYPE_OBJET,
DOAD_NOM_DONNEE,DOAD_MULTIPLE,DOAD_ID_CAT,DOAD_ID_DATA,DOAD_NUMERO_ORDRE,DOAD_NOM_DATA,DOAD_OBJET,DOAD_OBJET_DATA,DOAD_CHAMP_OBJET_DATA,DOAD_TYPE_DATA,DOAD_ID_RECORD,DOAD_VALEUR)
select 
data.ID_OBJET as DOAD_ID_DOA,
case when ent.MODELE=0x54 then 1 else 0 end as DOAD_MODELE,
ent.REF as DOAD_REFERENCE,
ent.LIB as DOAD_LIBELLE,
ent.DESCR as DOAD_DESCRIPTION,
ent.ID_TYPE as DOAD_ID_TYPE,
typ.LIB as DOAD_LIBELLE_TYPE,
ent.CODE_OPE as DOAD_CODE_OPERATEUR,
ent.PROPRIETAIRE as DOAD_PROPRIETAIRE,
ent.STATUT as DOAD_STATUT,
ent.ID_ETAT_AV as DOAD_ID_ETAT_AVANCEMENT,
ent.PRESCRIPTEUR as DOAD_CODE_PRESCRIPTEUR,
ent.CTR_EMETT as DOAD_CODE_CENTRE_EMETTEUR,
case when isdate(ent.DATE_CREAT)=1 then CONVERT (datetime,ent.DATE_CREAT ,103) else NULL     end as DOAD_DATE_CREATION,
case when isdate(ent.DATE_DEB)=1 then CONVERT (datetime,ent.DATE_DEB ,103) else NULL     end as DOAD_DATE_DEBUT,
case when isdate(ent.DATE_FIN)=1 then CONVERT (datetime,ent.DATE_FIN ,103) else NULL     end as DOAD_DATE_FIN,
struct.ID_DONNEE as DOAD_ID_DONNEE,
struct.TYPE_OBJET as DOAD_TYPE_OBJET,
struct.NOM_DONNEE as DOAD_NOM_DONNEE,
case when struct.MULTIPLE=0x54 then 1 else 0 end as DOAD_MULTIPLE,
struct.ID_CAT as DOAD_ID_CAT,
data.ID_DATA as DOAD_ID_DATA,
data.NUM_ORDRE as DOAD_NUMERO_ORDRE,
data.NOM_DATA as DOAD_NOM_DATA,
data.OBJET as DOAD_OBJET,
data.OBJET_DATA as DOAD_OBJET_DATA,
data.CHAMP_OBJET_DATA as DOAD_CHAMP_OBJET_DATA,
data.TYPE_DATA as DOAD_TYPE_DATA,
val.ID_RECORD as DOAD_ID_RECORD,
val.VALEUR as DOAD_VALEUR
from <PREFIXE_HA>DOA_RECORD_VAL val <WITHNOLOCK>
left outer join <PREFIXE_HA>DOA_DATA data <WITHNOLOCK> on data.ID_DATA=val.ID_DATA and data.ID_DONNEE=val.ID_DONNEE and data.ID_OBJET=val.ID_DOA
left outer join <PREFIXE_HA>DOA_STRUCT struct <WITHNOLOCK> on data.ID_OBJET=struct.ID_OBJET and data.ID_DONNEE=struct.ID_DONNEE
left outer join <PREFIXE_HA>DOA ent <WITHNOLOCK> on struct.ID_OBJET=ent.ID
left outer join <PREFIXE_HA>DOAR_TYPE typ <WITHNOLOCK> on ent.ID_TYPE=typ.ID

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
insert into <PREFIXE_DW>DOA_DATA (DOAD_ID_DOA,DOAD_MODELE,DOAD_REFERENCE,DOAD_LIBELLE,DOAD_DESCRIPTION,DOAD_ID_TYPE,DOAD_LIBELLE_TYPE,DOAD_CODE_OPERATEUR,DOAD_PROPRIETAIRE,
DOAD_STATUT,DOAD_ID_ETAT_AVANCEMENT,DOAD_CODE_PRESCRIPTEUR,DOAD_CODE_CENTRE_EMETTEUR,DOAD_DATE_CREATION,DOAD_DATE_DEBUT,DOAD_DATE_FIN,DOAD_ID_DONNEE,DOAD_TYPE_OBJET,
DOAD_NOM_DONNEE,DOAD_MULTIPLE,DOAD_ID_CAT,DOAD_ID_DATA,DOAD_NUMERO_ORDRE,DOAD_NOM_DATA,DOAD_OBJET,DOAD_OBJET_DATA,DOAD_CHAMP_OBJET_DATA,DOAD_TYPE_DATA,DOAD_ID_RECORD,DOAD_VALEUR)
select 
data.ID_OBJET ,
case when ent.MODELE=0x54 then 1 else 0 end,
ent.REF,ent.LIB,ent.DESCR,ent.ID_TYPE,typ.LIB as LIB_TYPE,ent.CODE_OPE,ent.PROPRIETAIRE,ent.STATUT,ent.ID_ETAT_AV,ent.PRESCRIPTEUR,ent.CTR_EMETT,
case when isdate(ent.DATE_CREAT)=1 then CONVERT (datetime,ent.DATE_CREAT ,103) else NULL     end,
case when isdate(ent.DATE_DEB)=1 then CONVERT (datetime,ent.DATE_DEB ,103) else NULL     end,
case when isdate(ent.DATE_FIN)=1 then CONVERT (datetime,ent.DATE_FIN ,103) else NULL     end,
struct.ID_DONNEE,struct.TYPE_OBJET,struct.NOM_DONNEE,case when struct.MULTIPLE=0x54 then 1 else 0 end,struct.ID_CAT,
data.ID_DATA,data.NUM_ORDRE,data.NOM_DATA,data.OBJET,data.OBJET_DATA,data.CHAMP_OBJET_DATA,data.TYPE_DATA,
0 as ID_RECORD,
'                                                  ' as VALEUR

from <PREFIXE_HA>DOA_DATA data <WITHNOLOCK>
left outer join <PREFIXE_HA>DOA_STRUCT struct on data.ID_OBJET=struct.ID_OBJET and data.ID_DONNEE=struct.ID_DONNEE
left outer join <PREFIXE_HA>DOA ent on struct.ID_OBJET=ent.ID
left outer join <PREFIXE_HA>DOAR_TYPE typ on ent.ID_TYPE=typ.ID
where not exists (select 1 from <PREFIXE_DW>DOA_DATA doad where doad.DOAD_ID_DOA=data.ID_OBJET and doad.DOAD_ID_DATA=data.ID_DATA)

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='DOA_ELEMENTS' and xtype='U') drop table <PREFIXE_DW>DOA_ELEMENTS

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
select el.ID_DOA as DOAE_ID_DOA,ent.LIB as DOAE_LIBELLE_DOA ,el.ID_ELEM as DOAE_ID_ELEMENT,el.TYPE_ELEM as DOAE_TYPE_ELEMENT
into <PREFIXE_DW>DOA_ELEMENTS 
from <PREFIXE_HA>DOA_ELEM el <WITHNOLOCK>
left outer join <PREFIXE_HA>DOA ent <WITHNOLOCK> on ent.ID=el.ID_DOA

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='DOA_SOCIETES' and xtype='U') drop table <PREFIXE_DW>DOA_SOCIETES

<SEPARATEUR>
select doasoc.ID_DOA as DOAS_ID_DOA ,ent.LIB as DOAS_LIBELLE_DOA ,doasoc.CODE as DOAS_CODE_SOCIETE,soc.RAIS_SOC as DOAS_RAISON_SOCIALE_SOCIETE
into <PREFIXE_DW>DOA_SOCIETES
from <PREFIXE_HA>DOA_SOC doasoc <WITHNOLOCK>
left outer join <PREFIXE_HA>DOA ent on ent.ID=doasoc.ID_DOA
left outer join <PREFIXE_HA>SOCIETE soc <WITHNOLOCK> on doasoc.CODE=soc.CODE_STE

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
delete from <PREFIXE_DW>DOA_DATA where DOAD_ID_DOA is null and DOAD_TYPE_OBJET is null and DOAD_ID_DATA is null

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
insert into <PREFIXE_DW>DOA_DATA (DOAD_ID_DOA,DOAD_VALEUR,DOAD_MODELE,DOAD_MULTIPLE,DOAD_ID_RECORD,DOAD_REFERENCE,DOAD_LIBELLE,DOAD_DESCRIPTION,DOAD_ID_TYPE,DOAD_LIBELLE_TYPE,DOAD_CODE_OPERATEUR,DOAD_PROPRIETAIRE,
DOAD_STATUT,DOAD_ID_ETAT_AVANCEMENT,DOAD_CODE_PRESCRIPTEUR,DOAD_CODE_CENTRE_EMETTEUR,DOAD_DATE_CREATION,DOAD_DATE_DEBUT,DOAD_DATE_FIN)  
select ent.ID,' ',MODELE,' ',0,REF,ent.LIB,ent.DESCR,ID_TYPE,typ.LIB,CODE_OPE,ent.PROPRIETAIRE,STATUT,ent.ID_ETAT_AV,ent.PRESCRIPTEUR,ent.CTR_EMETT,
case when isdate(ent.DATE_CREAT)=1 then CONVERT (datetime,ent.DATE_CREAT ,103) else NULL     end ,
case when isdate(ent.DATE_DEB)=1 then CONVERT (datetime,ent.DATE_DEB ,103) else NULL     end,
case when isdate(ent.DATE_FIN)=1 then CONVERT (datetime,ent.DATE_FIN ,103) else NULL     end 
from <PREFIXE_HA>DOA ent 
left outer join <PREFIXE_HA>DOAR_TYPE typ on ent.ID_TYPE=typ.ID
--where ent.ID not in (select DOAD_ID_DOA from DOA_DATA)

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='DOA_TACHES' and xtype='U') drop table <PREFIXE_DW>DOA_TACHES

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
select a.TYPE_OBJET as DOAT_TYPE_OBJET,a.ID_OBJET as DOAT_ID_DOA,t.ID_TACHE as DOAT_ID_TACHE,t.LIB as DOAT_LIBELLE_TACHE,DESCR as DOAT_DESCRIPTION_TACHE,RESPONSABLE as DOAT_RESPONSABLE_TACHE,
case when t.TERMINEE=0x54 then 1 else 0 end as DOAT_TACHE_TERMINEE,
a.ID_ACTION as DOAT_ID_ACTION,a.LIB as DOAT_LIBELLE_ACTION,
case when a.TERMINEE=0x54 then 1 else 0 end as DOAT_ACTION_TERMINEE
into <PREFIXE_DW>DOA_TACHES
from <PREFIXE_HA>DOA_ACTIONS a
left outer join <PREFIXE_HA>DOA_TACHES t on t.ID_OBJET=a.ID_OBJET and t.ID_TACHE=a.ID_TACHE

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
insert into <PREFIXE_DW>DOA_TACHES  (DOAT_TYPE_OBJET,DOAT_ID_DOA,DOAT_ID_TACHE,DOAT_LIBELLE_TACHE,DOAT_DESCRIPTION_TACHE,DOAT_RESPONSABLE_TACHE,DOAT_TACHE_TERMINEE,DOAT_ACTION_TERMINEE,DOAT_ID_ACTION,DOAT_LIBELLE_ACTION) 
select TYPE_OBJET,ID_OBJET,ID_TACHE,LIB,DESCR,RESPONSABLE,
case when t.TERMINEE=0x54 then 1 else 0 end as DOAT_TACHE_TERMINEE,
0,0,' ' 
from <PREFIXE_HA>DOA_TACHES t
where ID_OBJET not in (select DOAT_ID_DOA from <PREFIXE_DW>DOA_TACHES)

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='DOA_ACTIONS_DATA' and xtype='U') drop table <PREFIXE_DW>DOA_ACTIONS_DATA

<SEPARATEUR>
/*<VERSION_MINIMUM>10.31af</VERSION_MINIMUM>*/
select TYPE_OBJET as DOAA_TYPE_OBJET,ID_OBJET as DOAA_ID_DOA,ID_TACHE as DOAA_ID_TACHE,ID_ACTION as DOAA_ID_ACTION,ID_DONNEE as DOAA_ID_DONNEE,ID_DATA as DOAA_ID_DATA,
case when AFFICHEE=0x54 then 1 else 0 end as DOAA_AFFICHEE,
case when MODIFIABLE=0x54 then 1 else 0 end as DOAA_MODIFIABLE
into <PREFIXE_DW>DOA_ACTIONS_DATA
from <PREFIXE_HA>DOA_ACTIONS_DATA

<SEPARATEUR>
create or alter view TEMP_PREPARE as
select tab.NOM_DONNEE,tab.NOMSQLDW,chp.NOM_DATA,upper(chp.NOMSQLDW) as NOMSQLDA,
case when isnull(o.BDD_TYPE,'')<>'' then left(o.BDD_TYPE,1) 
when ISNULL(cs.FORMAT,'')='M' then 'A'
when ISNULL(cs.FORMAT,'')='U' then 'A'
when ISNULL(cs.FORMAT,'')='E' then 'A'
when ISNULL(cs.FORMAT,'')<>'' then left(cs.FORMAT,1) else chp.TYPE_DATA end as TYPE,
case when left(isnull(o.BDD_TYPE,''),1)='A' then o.BDD_SIZE 
when left(ISNULL(cs.FORMAT,''),1)='A' then cs.ALP_LONG 
when left(ISNULL(cs.FORMAT,''),1)='M' then 3000
when left(ISNULL(cs.FORMAT,''),1)='U' then 255
when left(ISNULL(cs.FORMAT,''),1)='E' then 100
else chp.A_LONG end as SIZE,
chp.NUM_ORDRE
from <PREFIXE_HA>DOA_STRUCT tab with(nolock)
left outer join <PREFIXE_HA>DOA_DATA chp with(nolock) on chp.TYPE_OBJET=tab.TYPE_OBJET and chp.ID_OBJET=tab.ID_OBJET and chp.ID_DONNEE=tab.ID_DONNEE
/* recherche dans les types de CS pour d�tecter les DOA_STRUC li�es � TS_DON */
left outer join <PREFIXE_HA>CS_TYPES cs on cs.TYPE=
    case when tab.LIE_A_OBJET=0x54 then
        case when ISNULL(patindex('.',chp.OBJET),0)<>0 then SUBSTRING(chp.OBJET,1,patindex('.',chp.OBJET)-1)
        else
        tab.OBJET
        end
    else
        case when chp.FLAG_ID_OBJET=0x54 or chp.DATA_LIE_A_OBJ=0x54 then chp.OBJET_DATA
        else '' end
    end
    and cs.CONTROLE=0x54 and cs.CTRL_STD=0x46
/* lecture du format dans BDD_OBJECTS pour les champs li�s � des objets de la base */
left outer join <PREFIXE_HA>BDD_OBJECTS o on o.BDD_TYPE_OBJET='TABLE' and o.BDD_TABLE=
    case 
    -- cas de DOA_STRUCT li�e � un objet principal
    when tab.LIE_A_OBJET=0x54 then
        case 
            -- Cas de AO_FOURN qui est trait� comme extension de FOURNISS. Le nom du champ de d.OBJET est AO_FOURN.<nomchamp>
            when ISNULL(patindex('.',chp.OBJET),0)<>0 then SUBSTRING(chp.OBJET,1,patindex('.',chp.OBJET)-1)
            -- Cas d'une table suppl�mentaire TS_DON
            when isnull(cs.CONTROLE,0x46)=0x54 and isnull(cs.CTRL_STD,0x46)=0x46 then 'TS_DON'
            -- Cas normal
            else
            tab.OBJET
        end
    -- cas de DOA_DATA li� � un objet secondaire
    else
        case when chp.FLAG_ID_OBJET=0x54 or (chp.DATA_LIE_A_OBJ=0x54 and chp.ID_DATA_LIEE<>0)  then chp.OBJET_DATA
        else '' end
    end
    and o.BDD_COL=
    case when chp.DATA_LIE_A_OBJ=0x54 and chp.OBJET_DATA<>tab.OBJET then chp.OBJET_DATA
        else chp.OBJET end
    and o.BDD_VERSION_END='' and o.BDD_CONNECTION='OXASQLDB'
where tab.TYPE_OBJET='B'
--and tab.NOMSQLDW=upper('TSOURCING')
and chp.NOMSQLDW<>' '
union select distinct ' ' as NOM_DONNEE, NOMSQLDW,' ' as NOM_DATA,'ID_DOA' as NOM,'N' as N,9 as NUM,1 as ORDRE  from <PREFIXE_HA>DOA_STRUCT where NOMSQLDW<>''
union select distinct ' ' as NOM_DONNEE, NOMSQLDW,' ' as NOM_DATA,'ID_DONNEE' as NOM,'N' as N,9 as NUM,1 as ORDRE  from <PREFIXE_HA>DOA_STRUCT where NOMSQLDW<>''
union select distinct ' ' as NOM_DONNEE, NOMSQLDW,' ' as NOM_DATA,'ID_RECORD' as NOM,'N' as N,9 as NUM,1 as ORDRE  from <PREFIXE_HA>DOA_STRUCT where NOMSQLDW<>''

<SEPARATEUR>
create or alter view TEMP_PREPARE2 as
 select bibli.NOMSQLDW,data.NOMSQLDW as NOMSQLDW2 ,v.ID_DOA,v.ID_DONNEE,v.ID_DATA,v.ID_RECORD,replace(v.VALEUR,char(10),'') as VALEUR,  
   case when isnull(o.BDD_TYPE,'')<>'' then left(o.BDD_TYPE,1)   
   when ISNULL(cs.FORMAT,'')='M' then 'A'  
   when ISNULL(cs.FORMAT,'')='U' then 'A'  
   when ISNULL(cs.FORMAT,'')='E' then 'A'  
   when ISNULL(cs.FORMAT,'')<>'' then left(cs.FORMAT,1) else data.TYPE_DATA end  as TYPE
   from      <PREFIXE_HA>DOA_RECORD_VAL v  
   left outer join      <PREFIXE_HA>DOA_DATA dtdoa on dtdoa.TYPE_OBJET='D' and dtdoa.ID_OBJET=v.ID_DOA and dtdoa.ID_DONNEE=v.ID_DONNEE and dtdoa.ID_DATA=v.ID_DATA  
   left outer join      <PREFIXE_HA>DOA_STRUCT don on don.TYPE_OBJET='D' AND don.ID_OBJET=dtdoa.ID_OBJET and don.ID_DONNEE=dtdoa.ID_DONNEE  
   left outer join      <PREFIXE_HA>DOA_DATA data on data.TYPE_OBJET='B' and data.ID_DONNEE=don.ID_STRUCT and data.ID_DATA=dtdoa.ID_STRUCT  
   left outer join      <PREFIXE_HA>DOA_STRUCT bibli on bibli.TYPE_OBJET='B' AND bibli.ID_OBJET=0 and bibli.ID_DONNEE=don.ID_STRUCT  
   /* recherche dans les types de CS pour détecter les DOA_STRUC liées à TS_DON */  
   left outer join      <PREFIXE_HA>CS_TYPES cs on cs.TYPE=  
   case when bibli.LIE_A_OBJET=0x54 then  
   case when ISNULL(patindex('.',data.OBJET),0)<>0 then SUBSTRING(data.OBJET,1,patindex('.',data.OBJET)-1)  
   else  
   bibli.OBJET  
   end  
   else  
   case when data.FLAG_ID_OBJET=0x54 or data.DATA_LIE_A_OBJ=0x54 then data.OBJET_DATA  
   else '' end  
   end  
   and cs.CONTROLE=0x54 and cs.CTRL_STD=0x46  
   /* lecture du format dans BDD_OBJECTS pour les champs liés à des objets de la base */  
   left outer join      <PREFIXE_HA>BDD_OBJECTS o on o.BDD_TYPE_OBJET='TABLE' and o.BDD_TABLE=  
   case  
   -- cas de DOA_STRUCT liée à un objet principal  
   when bibli.LIE_A_OBJET=0x54 then  
   case  
   -- Cas de AO_FOURN qui est traité comme extension de FOURNISS. Le nom du champ de d.OBJET est AO_FOURN.<nomchamp>  
   when ISNULL(patindex('.',data.OBJET),0)<>0 then SUBSTRING(data.OBJET,1,patindex('.',data.OBJET)-1)  
   -- Cas d'une table supplémentaire TS_DON  
   when isnull(cs.CONTROLE,0x46)=0x54 and isnull(cs.CTRL_STD,0x46)=0x46 then 'TS_DON'  
   -- Cas normal  
   else  
   bibli.OBJET  
   end  
   -- cas de DOA_DATA lié à un objet secondaire  
   else  
   case when data.FLAG_ID_OBJET=0x54 or (data.DATA_LIE_A_OBJ=0x54 and data.ID_DATA_LIEE<>0) then data.OBJET_DATA  
   else '' end  
   end  
   and o.BDD_COL=  
   case when data.DATA_LIE_A_OBJ=0x54 and data.OBJET_DATA<>bibli.OBJET then data.OBJET_DATA  
   else data.OBJET end  
   and o.BDD_VERSION_END='' and o.BDD_CONNECTION='OXASQLDB'  
   where  
   case when isnull(o.BDD_TYPE,'')<>'' then left(o.BDD_TYPE,1)  
   when ISNULL(cs.FORMAT,'')='M' then 'A'  
   when ISNULL(cs.FORMAT,'')='U' then 'A'  
   when ISNULL(cs.FORMAT,'')='E' then 'A'  
   when ISNULL(cs.FORMAT,'')<>'' then left(cs.FORMAT,1) else data.TYPE_DATA end <>''  
   and v.VALEUR<>'' and data.NOMSQLDW<>'' and bibli.NOMSQLDW<>''  
   --order by bibli.NOMSQLDW,data.NOMSQLDW 