/*<OXADWDB>*/
/*req only on OXADWDB DB*/

/*MAJ PAR_CS creation query to create col*/
if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='PAR_CS' and COLUMN_NAME='PAR_CS_REQ') alter table PAR_CS add  PAR_CS_REQ varchar(3000) 
<SEPARATEUR>
if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='PAR_CS' and COLUMN_NAME='PAR_CS_TYPE_SQL') alter table PAR_CS add  PAR_CS_TYPE_SQL varchar(30) 
<SEPARATEUR>
if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='PAR_CS' and COLUMN_NAME='PAR_CS_DW_TABLE_SQL') alter table PAR_CS add  PAR_CS_DW_TABLE_SQL varchar(3000) 

<SEPARATEUR>
update  PAR_CS  set PAR_CS_SIZE=3000 where PAR_CS_TYPE='M' and PAR_CS_SIZE=0
update  PAR_CS  set PAR_CS_SIZE=3000 where PAR_CS_TYPE='U' and PAR_CS_SIZE=0
update  PAR_CS  set PAR_CS_SIZE=100 where PAR_CS_TYPE='E' and PAR_CS_SIZE=0
update  PAR_CS  set PAR_CS_SIZE=12   where PAR_CS_TYPE='H' and PAR_CS_SIZE=0

<SEPARATEUR>
update  PAR_CS  set PAR_CS_TYPE_SQL=case when PAR_CS_TYPE='L' then 'int' when PAR_CS_TYPE='N' then 'float'  else 'varchar(' + cast(PAR_CS_SIZE as varchar) +')'  end
update  PAR_CS  set PAR_CS_TYPE_SQL='datetime' where PAR_CS_TYPE='D'

<SEPARATEUR>
update  PAR_CS  set PAR_CS_DW_TABLE_SQL= PAR_CS_DW_TABLE 
update  PAR_CS  set PAR_CS_DW_TABLE_SQL= 'DOCUMENTS' where PAR_CS_DW_TABLE like 'DOCUMENTS_%'
update  PAR_CS  set PAR_CS_DW_TABLE_SQL= 'MOUVEMENTS' where PAR_CS_DW_TABLE like 'MOUVEMENTS_%'

<SEPARATEUR>
update  PAR_CS set PAR_CS_REQ= 'if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='+ quotename(PAR_CS_DW_TABLE_SQL,'''') +' and COLUMN_NAME=' + quotename(PAR_CS_DW_NAME,'''') +') ' + ' alter table '+quotename(PAR_CS_DW_TABLE_SQL,'') +' add ' +quotename(PAR_CS_DW_NAME,'') + ' '+PAR_CS_TYPE_SQL from PAR_CS where PAR_CS_DW_NCS<>0

<SEPARATEUR>
/*Adding Lib col for CONTROLED_BY_TABLE*/
 update  PAR_CS set PAR_CS_REQ=PAR_CS_REQ+ ' if not exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='+ quotename(PAR_CS_DW_TABLE_SQL,'''') +' and COLUMN_NAME=' + quotename(PAR_CS_DW_NAME+'_L','''') +') ' + ' alter table '+quotename(PAR_CS_DW_TABLE_SQL,'') +' add ' +quotename(PAR_CS_DW_NAME+'_L','') + ' varchar(3000)' from PAR_CS where PAR_CS_DW_NCS<>0 and PAR_CS_CONTROLED_BY_TABLE=1
 
<SEPARATEUR>
/*On vire aussi au cas celle avec le S*/
if  exists (select * from sysobjects where name='NOMENCLATURE_ACHATS_IMPUTATIONS' and xtype='U') drop table NOMENCLATURE_ACHATS_IMPUTATIONS

<SEPARATEUR>
/*On vire NOMENCLATURE_ACHATS_IMPUTATION*/
if  exists (select * from sysobjects where name='NOMENCLATURE_ACHATS_IMPUTATION' and xtype='U') drop table NOMENCLATURE_ACHATS_IMPUTATION

<SEPARATEUR>
/*creation NOMENCLATURE_ACHATS_IMPUTATION*/
create table NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET varchar(30) null default '',  NAI_TYPE_IMPUTATION varchar(30) null default '',NAI_CODE_OBJET varchar(30) null default '',NAI_CODE_SOCIETE varchar(30) null default '', NAI_CODE_IMPUTATION varchar(30) null default '')



