update STOCK_LOCAL set STL_QUANTITE_DISPONIBLE= STL_QUANTITE_PHYSIQUE - STL_QUANTITE_RESERVEE - STL_QUANTITE_INDISPO - STL_QUANTITE_EN_TRANSFERT - STL_QUANTITE_EN_PREVISION
from <PREFIXE_DW>STOCK_LOCAL,<PREFIXE_DW>ARTICLES where ART_CODE_ARTICLE=STL_CODE_ARTICLE
and ART_S_STRATEGIQUE=0
<SEPARATEUR>
update STOCK_LOCAL set STL_QUANTITE_DISPONIBLE= STL_QUANTITE_PHYSIQUE - STL_QUANTITE_RESERVEE - STL_QUANTITE_INDISPO - STL_QUANTITE_EN_TRANSFERT - STL_QUANTITE_EN_PREVISION - STL_STOCK_SURETE
from <PREFIXE_DW>STOCK_LOCAL,<PREFIXE_DW>ARTICLES where ART_CODE_ARTICLE=STL_CODE_ARTICLE
and ART_S_STRATEGIQUE=1
<SEPARATEUR>
update <PREFIXE_DW>MOUVEMENTS set MVT_CODE_SOUS_FAMILLE=ART_CODE_SOUS_FAMILLE,MVT_CODE_FAMILLE=ART_CODE_FAMILLE_ARTICLE from <PREFIXE_DW>MOUVEMENTS,<PREFIXE_DW>ARTICLES where MVT_CODE_ARTICLE=ART_CODE_ARTICLE
<SEPARATEUR>
update <PREFIXE_DW>FOURNISSEURS
set 
FRS_DATE_MODIFICATION = 
(select max(DAT_LOG) from <PREFIXE_HA>OXALOG ha  <WITHNOLOCK>
where frs.FRS_CODE_FOURNISSEUR = ha.ID1_OBJ and TYP_OBJ='FOURNISSEURS'
group by ID1_OBJ)
from <PREFIXE_HA>OXALOG ha , <PREFIXE_DW>FOURNISSEURS frs
where frs.FRS_CODE_FOURNISSEUR = ha.ID1_OBJ and TYP_OBJ='FOURNISSEURS'

<SEPARATEUR>
/*Imputations Articles SCA*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 
'ARTICLE'+replicate(' ',10) as NAI_TYPE_OBJET,
'SCA' +replicate(' ',10) as 'NAI_TYPE_IMPUTATION',
CODE_ART +replicate(' ',10) as NAI_CODE_OBJET,
CODE_STE as NAI_CODE_SOCIETE,
SCA +replicate(' ',10) as NAI_CODE_IMPUTATION
from <PREFIXE_HA>SCASTEAR with (nolock)

/*Imputation Articles CDC*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'ARTICLE','CDC',CODE_ART,CODE_STE,CDC 
from <PREFIXE_HA>CDCSTEAR with (nolock)

/*Imputation Articles SI*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'ARTICLE','SI',CODE_ART,CODE_STE,SI 
from <PREFIXE_HA>SISTEART with (nolock)

<SEPARATEUR>
/*Imputation Familles CDC*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'FAMILLE','CDC',FAMILLE,CODE_STE,CDC
from <PREFIXE_HA>CDCSTEFA with (nolock)

/*Imputation Familles SI*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'FAMILLE','SI',FAMILLE,CODE_STE,SI
from <PREFIXE_HA>SISTEFAM with (nolock)

/*Imputation Familles SCA*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'FAMILLE','SCA',FAMILLE,CODE_STE,SCA
from <PREFIXE_HA>SCASTEFA with (nolock)

<SEPARATEUR>
/*Imputation Sous Familles CDC*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'SOUS_FAMILLE','CDC',SFAMILL,CODE_STE,CDC
from <PREFIXE_HA>CDCSTESF with (nolock)

<SEPARATEUR>
/*Imputation Sous Familles SI*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'SOUS_FAMILLE','SI',SFAMILL,CODE_STE,SI
from <PREFIXE_HA>SISTESFA with (nolock)

<SEPARATEUR>
/*Imputation Sous Familles SCA*/
insert into <PREFIXE_DW>NOMENCLATURE_ACHATS_IMPUTATION (NAI_TYPE_OBJET,NAI_TYPE_IMPUTATION,NAI_CODE_OBJET,NAI_CODE_SOCIETE,NAI_CODE_IMPUTATION)
select 'SOUS_FAMILLE','SCA',SFAMILL,CODE_STE,SCA
from <PREFIXE_HA>SCASTESF with (nolock)

<SEPARATEUR>
/*Imputation utilisateurs par UO*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='UO_UTILISATEURS' and xtype='U') drop table  <PREFIXE_DW>UO_UTILISATEURS
<SEPARATEUR>
/*<VERSION_MINIMUM>10.31aa</VERSION_MINIMUM>*/
select CODE_USER as UOU_CODE_UTILISATEUR,CODE_STE as UOU_CODE_SOCIETE,CODE_UO as UOU_CODE_UO
into <PREFIXE_DW>UO_UTILISATEURS
from <PREFIXE_HA>LSTUOPARUSR <WITHNOLOCK>
<SEPARATEUR>
/*Imputation par UO*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='UO_IMPUTATIONS' and xtype='U') drop table  <PREFIXE_DW>UO_IMPUTATIONS
<SEPARATEUR>
/*<VERSION_MINIMUM>10.31aa</VERSION_MINIMUM>*/
select 'SI' as 'UOI_TYPE_IMPUTATION',CODE_UO as UOI_CODE_UO,CODE_STESI as UOI_CODE_SOCIETE,CODE_SI as UOI_CODE_IMPUTATION
into <PREFIXE_DW>UO_IMPUTATIONS
from <PREFIXE_HA>LSTSIPARUO <WITHNOLOCK> 
<SEPARATEUR>
/*Table obsolete*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='USERS_DROITS_DW' and xtype='U') drop table  <PREFIXE_DW>USERS_DROITS_DW
<SEPARATEUR>
/*Groupes de droits*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='DROITS_GROUPES' and xtype='U') drop table  <PREFIXE_DW>DROITS_GROUPES
select 
d.COD_GRP as DRG_CODE_GROUPE_DROITS,isnull(g.LIB_GRP,' ') as DRG_LIBBELLE_GROUPE_DROITS,d.COD_DR as DRG_CODE_DROIT,dr.LIB_DR as DRG_LIBBELLE_DROIT 
into <PREFIXE_DW>DROITS_GROUPES
from <PREFIXE_HA>DROITGRP d
left outer join <PREFIXE_HA>GRPDROIT g on d.COD_GRP=g.COD_GRP
left outer join <PREFIXE_HA>DROITS dr on dr.COD_DR=d.COD_DR
<SEPARATEUR>
if  exists (select * from <PREFIXE_DW>sysobjects where name='DROITS_GROUPES_UTILISATEURS' and xtype='U') drop table  <PREFIXE_DW>DROITS_GROUPES_UTILISATEURS
<SEPARATEUR>
select 
grp.COD_GRP as DGU_CODE_GROUPE_DROITS,g.LIB_GRP as DGU_LIBELLE_GROUPE_DROITS,grp.COD_USR as DGU_CODE_UTILISATEUR,u.USR_NOM_UTILISATEUR as DGU_NOM_UTILISATEUR
into <PREFIXE_DW>DROITS_GROUPES_UTILISATEURS
from <PREFIXE_HA>GRPUSR grp
left outer join <PREFIXE_HA>GRPDROIT g on grp.COD_GRP=g.COD_GRP
left outer join <PREFIXE_DW>UTILISATEURS u on grp.COD_USR=u.USR_CODE_UTILISATEUR

<SEPARATEUR>
if  exists (select * from <PREFIXE_DW>sysobjects where name='DROITS_UTILISATEURS' and xtype='U') drop table  <PREFIXE_DW>DROITS_UTILISATEURS

<SEPARATEUR>
select 
du.COD_DR as DU_CODE_DROIT,d.LIB_DR as DU_LIBELLE_DROIT,du.COD_USR as DU_CODE_UTILISATEUR,u.USR_NOM_UTILISATEUR as DU_NOM_UTILISATEUR
into <PREFIXE_DW>DROITS_UTILISATEURS
from <PREFIXE_HA>DROITUSR du
left outer join <PREFIXE_HA>DROITS d <WITHNOLOCK> on du.COD_DR=d.COD_DR
left outer join <PREFIXE_DW>UTILISATEURS u on du.COD_USR=u.USR_CODE_UTILISATEUR

<SEPARATEUR>
/*Client de connexion*/
UPDATE u set USR_CLIENT_CONNEXION=case when (h.ACCOXTRA=0x54 and  h.ACCOXAPR=0x46) then 'OXASRM' when (h.ACCOXTRA=0x46 and  h.ACCOXAPR=0x54) then 'OXAPROC' when (h.ACCOXTRA=0x54 and  h.ACCOXAPR=0x54) then 'OXASRM,OXAPROC' else ' ' end
from <PREFIXE_DW>UTILISATEURS u,<PREFIXE_HA>HASTUTIL h <WITHNOLOCK> where u.USR_CODE_UTILISATEUR=h.CODE

<SEPARATEUR>
/*Date derniere utilisation article*/
update <PREFIXE_DW>ARTICLES set ART_DATE_DERNIERE_UTILISATION =
(select max(EVE_DATE_CREATION) from <PREFIXE_DW>EVENEMENTS,<PREFIXE_DW>DOCUMENTS where DOC_NUMERO_DOC=EVE_NUMERO_DOCUMENT and DOC_TYPE_TRANSACTION=EVE_TYPE_TRANSACTION and ART_CODE_ARTICLE=DOC_CODE_ARTICLE 
and DOC_TYPE_TRANSACTION in ('DA','CA') and EVE_CODE_EVENEMENT in('DAV','VAL')
group by DOC_CODE_ARTICLE )
where ART_CODE_ARTICLE in 
(select distinct DOC_CODE_ARTICLE from <PREFIXE_DW>DOCUMENTS,<PREFIXE_DW>EVENEMENTS where DOC_TYPE_TRANSACTION in ('DA','CA') and DOC_TYPE_TRANSACTION=EVE_TYPE_TRANSACTION and DOC_NUMERO_DOC=EVE_NUMERO_DOCUMENT and EVE_CODE_EVENEMENT in('DAV','VAL'))

<SEPARATEUR>
/*Date derniere connexion utilisateur*/
update <PREFIXE_DW>UTILISATEURS set USR_DATE_DERNIERE_CONNEXION=
(select MAX(AUD_DATE) from <PREFIXE_DW>AUDITS where USR_CODE_UTILISATEUR=AUD_UTILISATEUR and AUD_OPERATION='LOGIN')
<SEPARATEUR>
/*PAYS UE conversion binary -> Enier*/
update  d set d.PAY_UE=case when( s.PAYSCEE =0x54) then 1 else 0 end from <PREFIXE_HA>COUNTRY s ,<PREFIXE_DW>PAYS d  where s.CODE_PAYS=d.PAY_CODE_PAYS

<SEPARATEUR>
/*MAJ des Natures sur CODES_COMPLEMTAIRES*/
update c set c.CC_CODE_NATURE_ANALYTIQUE=d.CODNAN,CC_LIBELLE_NATURE_ANALYTIQUE=d.LIBELLE
from <PREFIXE_DW>CODES_COMPLEMTAIRES c, <PREFIXE_HA>CODECOMP d 
where c.CC_CODE_SOCIETE=d.CODE_STE and c.CC_CODE_COMPLEMENTAIRE=d.CODE_CDC 

<SEPARATEUR>
/*Imputation par articles*/
if  exists (select * from <PREFIXE_DW>sysobjects where name='ARTICLES_IMPUTATIONS' and xtype='U') drop table <PREFIXE_DW>ARTICLES_IMPUTATIONS
<SEPARATEUR>
select 'SCA' as 'ARI_TYPE_IMPUTATION',CODE_ART as ARI_CODE_ARTICLE,CODE_STE as ARI_CODE_SOCIETE,SCA as ARI_CODE_IMPUTATION
into <PREFIXE_DW>ARTICLES_IMPUTATIONS
from <PREFIXE_HA>SCASTEAR <WITHNOLOCK> 
<SEPARATEUR>
insert into <PREFIXE_DW>ARTICLES_IMPUTATIONS (ARI_TYPE_IMPUTATION,ARI_CODE_ARTICLE,ARI_CODE_SOCIETE,ARI_CODE_IMPUTATION)
select 'CDC',CODE_ART,CODE_STE,CDC 
from <PREFIXE_HA>CDCSTEAR <WITHNOLOCK>
<SEPARATEUR>
insert into <PREFIXE_DW>ARTICLES_IMPUTATIONS (ARI_TYPE_IMPUTATION,ARI_CODE_ARTICLE,ARI_CODE_SOCIETE,ARI_CODE_IMPUTATION)
select 'SI',CODE_ART,CODE_STE,SI 
from <PREFIXE_HA>SISTEART <WITHNOLOCK>


<SEPARATEUR>
/*Contacts frs*/
update c set c.COF_CONSULTATIONS=case when f.CTC_CONSULT =0x54 then 1 else 0 end
	from <PREFIXE_DW>CONTACTS_FOURNISSEURS c, <PREFIXE_HA>CONTFOUR f
	where c.COF_CODE_FOURNISSEUR=f.CODE_FOU and c.COF_INDICE_CONTACT=f.IND_FOU 

<SEPARATEUR> 
update c set c.[COF_UTILISATEUR_PORTAIL_FOURNISSEURS_STATUT]=u.STATUT
	from <PREFIXE_DW>CONTACTS_FOURNISSEURS c, <PREFIXE_HA>CONTFOUR f, <PREFIXE_HA>OXALINK_USER u
	where c.COF_CODE_FOURNISSEUR=f.CODE_FOU and c.COF_INDICE_CONTACT=f.IND_FOU and f.OXALINK_ID=u.USER_ID    
    
<SEPARATEUR>
/*MAJ des lib sur STOCK_LOCAL*/
DECLARE @sector CHAR(1)
set @sector = (select LIB01PARV from <PREFIXE_HA>PARAVRAC where TYPEPARV='PARAMSTE' and CODEPARV='SISECTOR')
print @sector
IF @sector<>'O'
Begin
	print 'Non Sector';
	update stk set stk.STL_LIBELLE_SECTION_IMPUTATION =sec.SI_DESIGNATION from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>SECTIONS_IMPUTATIONS sec where stk.STL_CODE_SECTION_IMPUTATION=sec.SI_CODE_SECTION_IMPUTATION 
	update stk set stk.STL_LIBELLE_CODE_COMPLEMENTAIRE=b.CC_DESIGNATION	  from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>CODES_COMPLEMTAIRES b where stk.STL_CODE_COMPLEMENTAIRE=b.CC_CODE_COMPLEMENTAIRE 
	update stk set stk.STL_LIBELLE_SOUS_COMPTE_ANALYSE=b.SCA_DESIGNATION  from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>SOUS_CPTES_ANALYSE b where stk.STL_CODE_SOUS_CPTE_ANALYSE=b.SCA_CODE_SOUS_CPTE_ANALYSE 
end
else
begin
	print 'Sector';
	update stk set stk.STL_LIBELLE_SECTION_IMPUTATION=sec.SI_DESIGNATION from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>SECTIONS_IMPUTATIONS sec where stk.STL_CODE_SECTION_IMPUTATION=sec.SI_CODE_SECTION_IMPUTATION and stk.STL_CODE_SOCIETE=sec.SI_CODE_SOCIETE 
	update stk set stk.STL_LIBELLE_CODE_COMPLEMENTAIRE=b.CC_DESIGNATION	 from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>CODES_COMPLEMTAIRES b where stk.STL_CODE_COMPLEMENTAIRE=b.CC_CODE_COMPLEMENTAIRE and stk.STL_CODE_SOCIETE=b.CC_CODE_SOCIETE 
	update stk set stk.STL_LIBELLE_SOUS_COMPTE_ANALYSE=b.SCA_DESIGNATION from <PREFIXE_DW>STOCK_LOCAL stk , <PREFIXE_DW>SOUS_CPTES_ANALYSE b where stk.STL_CODE_SOUS_CPTE_ANALYSE=b.SCA_CODE_SOUS_CPTE_ANALYSE and stk.STL_CODE_SOCIETE=b.SCA_CODE_SOCIETE 
end
     
<SEPARATEUR>
/*MAJ des lib sur SITES*/
DECLARE @sector CHAR(1)
set @sector = (select LIB01PARV from <PREFIXE_HA>PARAVRAC where TYPEPARV='PARAMSTE' and CODEPARV='SISECTOR')
print @sector

IF @sector<>'O'
Begin
	print 'Non Sector';
	update stk set stk.SIT_LIBELLE_SECTION_IMPUTATION =sec.SI_DESIGNATION from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>SECTIONS_IMPUTATIONS sec where stk.SIT_CODE_SECTION_IMPUTATION=sec.SI_CODE_SECTION_IMPUTATION 
	update stk set stk.SIT_LIBELLE_CODE_COMPLEMENTAIRE=b.CC_DESIGNATION	  from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>CODES_COMPLEMTAIRES b where stk.SIT_CODE_COMPLEMENTAIRE=b.CC_CODE_COMPLEMENTAIRE 
	update stk set stk.SIT_LIBELLE_SOUS_COMPTE_ANALYSE=b.SCA_DESIGNATION  from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>SOUS_CPTES_ANALYSE b where stk.SIT_CODE_SOUS_CPTE_ANALYSE=b.SCA_CODE_SOUS_CPTE_ANALYSE 
end
else
begin
	print 'Sector';
	update stk set stk.SIT_LIBELLE_SECTION_IMPUTATION=sec.SI_DESIGNATION from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>SECTIONS_IMPUTATIONS sec where stk.SIT_CODE_SECTION_IMPUTATION=sec.SI_CODE_SECTION_IMPUTATION and stk.SIT_CODE_SOCIETE=sec.SI_CODE_SOCIETE 
	update stk set stk.SIT_LIBELLE_CODE_COMPLEMENTAIRE=b.CC_DESIGNATION	 from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>CODES_COMPLEMTAIRES b where stk.SIT_CODE_COMPLEMENTAIRE=b.CC_CODE_COMPLEMENTAIRE and stk.SIT_CODE_SOCIETE=b.CC_CODE_SOCIETE 
	update stk set stk.SIT_LIBELLE_SOUS_COMPTE_ANALYSE=b.SCA_DESIGNATION from <PREFIXE_DW>SITE_STOCKAGE stk , <PREFIXE_DW>SOUS_CPTES_ANALYSE b where stk.SIT_CODE_SOUS_CPTE_ANALYSE=b.SCA_CODE_SOUS_CPTE_ANALYSE and stk.SIT_CODE_SOCIETE=b.SCA_CODE_SOCIETE 
end    

<SEPARATEUR>
/*Traitemenjpm	t des RC*/
update <PREFIXE_DW>FOURNISSEURS set FRS_ADRESSE=replace( replace(FRS_ADRESSE,char(10),' ') ,char(13),''), FRS_ADR_REGL=replace( replace(FRS_ADR_REGL,char(10),' ') ,char(13),'')

<SEPARATEUR>
/*Spec 5275*/
update sf set sf.SF_COMMENTAIRE=left(m.CONTMEMO,250)
	from <PREFIXE_DW>SOUS_FAMILLES sf, <PREFIXE_HA>MEMOS m , <PREFIXE_HA>SSFAMILL sfs where sfs.NM_COMM=m.NODEMEMO and sf.SF_CODE_SOUS_FAMILLE=sfs.CODE_SFA
    
update sf set sf.SF_DESIGNATION_UNSPSC=d.LIB01DICO
	from <PREFIXE_DW>SOUS_FAMILLES sf, <PREFIXE_HA>DILISPEC d  where  d.TYPEDICO='UNSPSC' and  sf.SF_CODE_UNSPSC=d.CODEDICO     