/*=============RAZ*/
truncate table <PREFIXE_DW>CUMULS


/*=============INSERT*/
<SEPARATEUR>
insert  into <PREFIXE_DW>CUMULS (CUM_CODE_TABLE,CUM_CODE_SOCIETE,CUM_CODE_IDENTIFIANT,CUM_CODE_PERIODE,CUM_DATE_DEBUT_SOUS_PERIODE,CUM_MONTANT_PRE_ENGAGE,CUM_MONTANT_ENGAGE,CUM_MONTANT_REALISE,CUM_MONTANT_FACTURE)
select CODE_TAB,CODE_STE,CODE_IDEN,CODE_PER,CASE WHEN (DATE_SPER = ' ' OR DATE_SPER = '00000000' ) THEN CONVERT (datetime,'19000101' ,103) WHEN (DATE_SPER < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_SPER ,103) END, 
PRE_ENG,ENGAGE,REALISE,FACTURE
from <PREFIXE_HA>STE_SSPE 
 
/*===========RENOMMAGE CODE TABLE*/
<SEPARATEUR>
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='SECTIONS_IMPUTATIONS' where CUM_CODE_TABLE='SI'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='ARTICLES' where CUM_CODE_TABLE='ART'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='ACHETEURS' where CUM_CODE_TABLE='ACH'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='CENTRES_ANALYSE' where CUM_CODE_TABLE='CTA'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='DEVISES' where CUM_CODE_TABLE='DEV'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='FOURNISSEURS' where CUM_CODE_TABLE='FOU'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='CENTRES' where CUM_CODE_TABLE='CTR'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='GROUPEMENTS_FOURNISSEURS' where CUM_CODE_TABLE='GFO'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='FAMILLES' where CUM_CODE_TABLE='FAM'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='ACCORD_CADRE' where CUM_CODE_TABLE='ACD'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='SOUS_FAMILLES' where CUM_CODE_TABLE='SFA'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='DIRECTIONS' where CUM_CODE_TABLE='DIR'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='DEMANDEURS' where CUM_CODE_TABLE='DEM'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='CODES_COMPLEMENTAIRES' where CUM_CODE_TABLE='CDC'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='SOUS_COMPTES_ANALYSE' where CUM_CODE_TABLE='SCA'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='ARTICLES_FOURNISSEURS' where CUM_CODE_TABLE='AFO'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='SOCIETES' where CUM_CODE_TABLE='SOC'
update <PREFIXE_DW>CUMULS set CUM_CODE_TABLE='NATURES_ANALYTIQUES' where CUM_CODE_TABLE='NAT'

/*============RECHERCHE LIB SOCIETE*/
<SEPARATEUR>
update cum set CUM_LIBELLE_SOCIETE=RAIS_SOC from <PREFIXE_DW>CUMULS cum , <PREFIXE_HA>SOCIETE d where cum.CUM_CODE_SOCIETE=d.CODE_STE

/*============RECHERCHE LIB IDENTIFIANT*/
<SEPARATEUR>
update cum set CUM_LIBELLE_IDENTIFIANT=SI_DESIGNATION from <PREFIXE_DW>CUMULS cum , <PREFIXE_DW>SECTIONS_IMPUTATIONS d where cum.CUM_CODE_IDENTIFIANT=d.SI_CODE_SECTION_IMPUTATION and CUM_CODE_TABLE='SECTIONS_IMPUTATIONS'
update cum set CUM_LIBELLE_IDENTIFIANT=ACC_LIBELLE from <PREFIXE_DW>CUMULS cum , <PREFIXE_DW>ACCORD_CADRE d where cum.CUM_CODE_IDENTIFIANT=d.ACC_CODE_ACCORD_CADRE and CUM_CODE_TABLE='ACCORD_CADRE'

/*============RECHERCHE PERIODE*/
<SEPARATEUR>
update cum set CUM_LIBELLE_PERIODE=LIBELLE,CUM_DATE_DEBUT_PERIODE= CASE WHEN (DATE_FIN = ' ' OR DATE_DEB = '00000000' ) THEN CONVERT (datetime,'19000101' ,103) WHEN (DATE_DEB < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_DEB ,103) END,	CUM_DATE_FIN_PERIODE= CASE WHEN (DATE_FIN = ' ' OR DATE_FIN = '00000000' ) THEN CONVERT (datetime,'19000101' ,103) WHEN (DATE_FIN < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_FIN ,103) END			 from <PREFIXE_DW>CUMULS cum , <PREFIXE_HA>PERIODES d where cum.CUM_CODE_PERIODE=d.CODE_PER

/*============RECHERCHE SOUS_PERIODE*/
<SEPARATEUR>
update cum set CUM_LIBELLE_SOUS_PERIODE=LIBELLE,CUM_DATE_FIN_SOUS_PERIODE=CASE WHEN (DATE_FIN = ' ' OR DATE_FIN = '00000000' ) THEN CONVERT (datetime,'19000101' ,103) WHEN (DATE_FIN < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_FIN ,103) END       from <PREFIXE_DW>CUMULS cum , <PREFIXE_HA>SSPERIOD d where cum.CUM_CODE_PERIODE=d.CODE_PER and convert(char, CUM_DATE_DEBUT_SOUS_PERIODE,112)=DATE_DEB

/*============RECHERCHE CUM_LIBELLE_PERIODE*/
<SEPARATEUR>
update cum set CUM_LIBELLE_PERIODE=LIBELLE from <PREFIXE_DW>CUMULS cum , <PREFIXE_HA>PERIODES d where cum.CUM_CODE_PERIODE=d.CODE_PER 

/*============RECHERCHE CUM_LIBELLE_IDENTIFIANT*/
<SEPARATEUR>
update cum set cum.CUM_LIBELLE_IDENTIFIANT=u.USR_NOM_UTILISATEUR
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>UTILISATEURS u where CUM_CODE_IDENTIFIANT=u.USR_CODE_UTILISATEUR and CUM_CODE_TABLE in ('DEMANDEURS','ACHETEURS')
    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.ART_DESIGNATION_ARTICLE
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>ARTICLES x where CUM_CODE_IDENTIFIANT=x.ART_CODE_ARTICLE and CUM_CODE_TABLE in ('ARTICLES')    
    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.FRS_RAISON_SOCIALE
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>FOURNISSEURS x where CUM_CODE_IDENTIFIANT=x.FRS_CODE_FOURNISSEUR and CUM_CODE_TABLE in ('FOURNISSEURS')
    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.SF_DESIGNATION
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>SOUS_FAMILLES x where CUM_CODE_IDENTIFIANT=x.SF_CODE_SOUS_FAMILLE  and CUM_CODE_TABLE in ('SOUS_FAMILLES')    
    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.AFR_DESIGNATION_ART_FRS
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>ARTICLES_FOURNISSEURS x where substring(CUM_CODE_IDENTIFIANT,1,12)=x.AFR_CODE_FOURNISSEUR and substring(cum.CUM_CODE_IDENTIFIANT,13,15)=x.AFR_CODE_ARTICLE
	and CUM_CODE_TABLE in ('ARTICLES_FOURNISSEURS')  
    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.FA_DESIGNATION
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>FAMILLES x where CUM_CODE_IDENTIFIANT=x.FA_CODE_FAMILLE
	and CUM_CODE_TABLE in ('FAMILLES')      

<SEPARATEUR>    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.CC_DESIGNATION
	from <PREFIXE_DW>CUMULS cum, <PREFIXE_DW>CODES_COMPLEMTAIRES x where CUM_CODE_IDENTIFIANT=x.CC_CODE_COMPLEMENTAIRE
	and cum.CUM_CODE_SOCIETE=x.CC_CODE_SOCIETE
	and CUM_CODE_TABLE in ('CODES_COMPLEMENTAIRES') 
 
<SEPARATEUR>     
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.DIR_DESIGNATION
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>DIRECTIONS x where CUM_CODE_IDENTIFIANT=x.DIR_CODE_DIRECTION
	and CUM_CODE_TABLE in ('DIRECTIONS')      

<SEPARATEUR>     
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.RAIS_SOC
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_HA>CENTRE x where CUM_CODE_IDENTIFIANT=x.CODE_CTR and cum.CUM_CODE_SOCIETE=x.CODE_STE
	and CUM_CODE_TABLE in ('CENTRES')           

<SEPARATEUR>    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.SOC_RAISON_SOCIALE
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>SOCIETES x where CUM_CODE_IDENTIFIANT=x.SOC_CODE_SOCIETE
	and CUM_CODE_TABLE in ('SOCIETES')    

<SEPARATEUR>    
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.LIBELLE
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_HA>DEVISES x where CUM_CODE_IDENTIFIANT=x.CODE_DEVI
	and CUM_CODE_TABLE in ('DEVISES')      
    
<SEPARATEUR>     
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.CTA_DESIGNATION
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>CENTRES_ANALYSES x where CUM_CODE_IDENTIFIANT=x.CTA_CODE_CENTRE_ANALYSE and cum.CUM_CODE_SOCIETE=x.CTA_CODE_SOCIETE
	and CUM_CODE_TABLE in ('CENTRES_ANALYSE')    

<SEPARATEUR>      
update cum set cum.CUM_LIBELLE_IDENTIFIANT=x.SCA_DESIGNATION
	from <PREFIXE_DW>CUMULS cum,<PREFIXE_DW>SOUS_CPTES_ANALYSE x where CUM_CODE_IDENTIFIANT=x.SCA_CODE_SOUS_CPTE_ANALYSE and cum.CUM_CODE_SOCIETE=x.SCA_CODE_SOCIETE
	and CUM_CODE_TABLE in ('SOUS_COMPTES_ANALYSE')         