truncate table <PREFIXE_DW>BUDGETS_CROISES

<SEPARATEUR>
declare @SISecto as varchar(1)
declare @CDCSecto as varchar(1)
declare @SCASecto as varchar(1)
declare @ste as varchar(7)
declare @codeper as varchar(7)
declare @budget as varchar(10)
declare @axe1 as varchar(2)
declare @axe2 as varchar(2)
declare @axe3 as varchar(2)
declare @RevActive as int
declare @ClauseSI as varchar(max)
declare @ClauseDIR as varchar(max)
declare @ClauseCDC as varchar(max)
declare @ClauseNA as varchar(max)
declare @ClauseSCA as varchar(max)
declare @ClauseCA as varchar(max)
declare @ClauseAxe1 as varchar(max)
declare @ClauseAxe2 as varchar(max)
declare @ClauseAxe3 as varchar(max)
declare @ReqLigBud as nvarchar(max)
/* Lecture de la sectorisation des axes */
set @SISecto=isnull((SELECT ltrim(rtrim(LIB01PARV)) from <PREFIXE_HA>PARAVRAC WHERE TYPEPARV='PARAMSTE' and CODEPARV='SISECTOR'),'N')   
set @CDCSecto=isnull((SELECT ltrim(rtrim(LIB01PARV)) from <PREFIXE_HA>PARAVRAC WHERE TYPEPARV='PARAMSTE' and CODEPARV='CDCSECTO'),'N') 
set @SCASecto=isnull((SELECT ltrim(rtrim(LIB01PARV)) from <PREFIXE_HA>PARAVRAC WHERE TYPEPARV='PARAMSTE' and CODEPARV='SCASECTO'),'N')
/* Pr�paration des clauses par axe pour tous les cas */
set @ClauseSI='c.SI=x.@AXE@'
set @ClauseDIR='c.SI in (select s.CODE_SI from <PREFIXE_HA>SECTIMPU s with (nolock) where s.CODE_STE='+case when @SISecto='O' then 'x.CODE_SOC' else '''''' end + ' and s.CODE_DIR=x.@AXE@)'
set @ClauseCDC='c.CDC=x.@AXE@'
set @ClauseCA='c.CDC in (select s.CODE_CDC from <PREFIXE_HA>CODECOMP s with (nolock) where s.CODE_STE='+case when @CDCSecto='O' then 'x.CODE_SOC' else '''''' end + ' and s.CODNAN=x.@AXE@)'
set @ClauseSCA='c.SCA=x.@AXE@'
set @ClauseCA='c.SCA in (select s.CODE_SCA from <PREFIXE_HA>SSCPTANA s with (nolock) where s.CODE_STE='+case when @SCASecto='O' then 'x.CODE_SOC' else '''''' end + ' and s.CODE_CTA=x.@AXE@)'
/* Curseur sur les budgets */
declare c_budsoc cursor
FOR
SELECT b.CODE_SOC,b.CODE_PER,b.CODE_BUD,r.IND_REV,b.CH_AX_UN,b.CH_AX_DE,b.CH_AX_TR FROM <PREFIXE_HA>BUDGETS b
left outer join <PREFIXE_HA>REV_BUDG r on r.COD_SOC=b.CODE_SOC and r.COD_PER=b.CODE_PER and r.COD_BUD=b.CODE_BUD
where r.ACTI=0x54
OPEN c_budsoc
FETCH NEXT FROM c_budsoc INTO @ste,@codeper,@budget,@RevActive,@axe1,@axe2,@axe3
WHILE (@@fetch_status = 0)
	BEGIN
/* Calcul des clauses r�elles par axe */
set @ClauseAxe1=REPLACE(
	case when @axe1='DI' then @ClauseDIR when @axe1='SI' then @ClauseSI when @axe1='NA' then @ClauseNA when @axe1='CD' then @ClauseCDC when @axe1='CA' then @ClauseCA when @axe1='SC' then @ClauseSCA else '' end
	,'@AXE@','AXE_UN')

set @ClauseAxe2=REPLACE(
	case when @axe2='DI' then @ClauseDIR when @axe2='SI' then @ClauseSI when @axe2='NA' then @ClauseNA when @axe2='CD' then @ClauseCDC when @axe2='CA' then @ClauseCA when @axe2='SC' then @ClauseSCA else '' end
	,'@AXE@','AXE_DEUX')

set @ClauseAxe3=REPLACE(
	case when @axe3='DI' then @ClauseDIR when @axe3='SI' then @ClauseSI when @axe3='NA' then @ClauseNA when @axe3='CD' then @ClauseCDC when @axe3='CA' then @ClauseCA when @axe3='SC' then @ClauseSCA else '' end
	,'@AXE@','AXE_DEUX')
/* Calcul de la requ�te d'insertion */
set @ReqLigBud=
'insert into <PREFIXE_DW>BUDGETS_CROISES ([BUC_CODE_SOCIETE],[BUC_CODE_PERIODE],[BUC_CODE_BUDGET],[BUC_CODE_AXE_1],[BUC_TYPE_AXE_1],[BUC_CODE_AXE_2],[BUC_TYPE_AXE_2],[BUC_CODE_AXE_3],[BUC_TYPE_AXE_3], [BUC_MONTANT_BUDGET],[BUC_MONTANT_CONSOMMATION],[BUC_INDICE_REVISION] )
select x.CODE_SOC,x.CODE_PER,N'''+@budget+''',x.ENT_AXE_UN,N'''+@axe1+''',x.ENT_AXE_DEUX,N'''+@axe2+''',x.ENT_AXE_TROIS,N'''+@axe3+''',0,isnull(SUM(c.MON_ENG)+SUM(MON_FAC),0),'+cast(@RevActive as varchar)+' from <PREFIXE_HA>BUD_CROSS x with (nolock)
left outer join <PREFIXE_HA>BUD_CUM c with (nolock) on c.CODE_SOC=x.CODE_SOC and c.CODE_PER=x.CODE_PER and '+ @ClauseAxe1 +
case when @axe2<>'' then ' and (x.AXE_DEUX=''*'' or '+@ClauseAxe2+')' else '' end +
case when @axe3<>'' then ' and (x.AXE_TROIS=''*'' or '+@ClauseAxe3+')' else '' end +
' where x.CODE_SOC=N'''+@ste+''' and x.CODE_PER=N'''+@codeper+''' group by x.CODE_SOC,x.CODE_PER,x.ENT_AXE_UN,x.ENT_AXE_DEUX,x.ENT_AXE_TROIS'
/* Ex�cution de la requ�te d'insertion */
exec sp_executesql @ReqLigBud

	FETCH NEXT FROM c_budsoc INTO @ste,@codeper,@budget,@RevActive,@axe1,@axe2,@axe3
  END
CLOSE c_budsoc
DEALLOCATE c_budsoc
<SEPARATEUR>
/* Calcul du montant de budget pour chaque ligne */
update <PREFIXE_DW>BUDGETS_CROISES
set BUC_MONTANT_BUDGET=
/* Si budget p�riode */
BUDGE+
/* Si budget sur sous p�riode */
isnull((select SUM(sp.BUDGET) from <PREFIXE_HA>BUD_SPER sp where sp.CD_SOCI=bu.CD_SOC and sp.CD_PERI=bu.CD_PER and sp.CD_BUDGE=bu.CD_BUD and sp.IN_REVIS=bu.IN_REVI and sp.AX_UN=bu.AXE_UN and sp.AX_DEUX=bu.AXE_DEUX and sp.AX_TROIS=bu.AXE_TROI),0)
+
/* Transfert budg�taires */
isnull((select SUM(m.MTVAR) from <PREFIXE_HA>BHSTMOD m where m.COD_STE=bu.CD_SOC and m.PERIODE=bu.CD_PER and m.BUDGET=bu.CD_BUD and m.REVIS=bu.IN_REVI and m.AXE_1=bu.AXE_UN and m.AXE_2=bu.AXE_DEUX and m.AXE_3=bu.AXE_TROI),0)
 from <PREFIXE_DW>BUDGETS_CROISES cr
 inner join <PREFIXE_HA>BUD_AXE bu on bu.CD_SOC=cr.BUC_CODE_SOCIETE and bu.CD_PER=cr.BUC_CODE_PERIODE and bu.CD_BUD=cr.BUC_CODE_BUDGET and bu.IN_REVI=cr.BUC_INDICE_REVISION
 and bu.AXE_UN=cr.BUC_CODE_AXE_1 and bu.AXE_DEUX=cr.BUC_CODE_AXE_2 and bu.AXE_TROI=cr.BUC_CODE_AXE_3