/*==============SPE056=============*/
if  exists (select * from sysobjects where name='DATAMART_056_002' and xtype='U') drop table DATAMART_056_002
<SEPARATEUR>
select CD_SOC as BUD_CODE_SOCIETE,AXE_UN as BUD_CODE_SOUS_COMPTE_ANALYSE,BUDGE as BUD_MONTANT_BUDGET,SCA_CODE_CENTRE_ANALYSE,
CD_PER as BUD_CODE_PERIODE,
CASE WHEN (DATE_DEB = ' ' OR DATE_DEB = '00000000' ) THEN NULL WHEN (DATE_DEB < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_DEB ,103) END as BUD_DATE_DEBUT,
CASE WHEN (DATE_FIN = ' ' OR DATE_FIN = '00000000' ) THEN NULL WHEN (DATE_FIN < '17530101') THEN CONVERT (datetime,'19000101' ,103) ELSE CONVERT (datetime,DATE_FIN ,103) END as BUD_DATE_FIN
into  DATAMART_056_002
from <PREFIXE_HA>BUD_CTRL ax
left outer join SOUS_CPTES_ANALYSE on CD_SOC=SCA_CODE_SOCIETE and AXE_UN=SCA_CODE_SOUS_CPTE_ANALYSE
left outer join <PREFIXE_HA>PERIODES on CODE_PER=CD_PER
<SEPARATEUR>
/*Ajout de documents*/
insert into DATAMART_056_002
select distinct DOC_CODE_SOCIETE as BUD_CODE_SOCIETE, DOC_CODE_SOUS_CPTE_ANALYSE as BUD_CODE_SOUS_COMPTE_ANALYSE ,0 as BUD_MONTANT_BUDGET,SCA_CODE_CENTRE_ANALYSE, 
isnull(PER_CODE_PERIODE,' ') as BUD_CODE_PERIODE,
PER_DATE_DEBUT_PERIODE,
PER_DATE_FIN_PERIODE
from DOCUMENTS
left outer join SOUS_CPTES_ANALYSE on DOC_CODE_SOCIETE=SCA_CODE_SOCIETE and DOC_CODE_SOUS_CPTE_ANALYSE=SCA_CODE_SOUS_CPTE_ANALYSE
left outer join PERIODES on DOC_DATE_PERIODE=PER_DATE
where DOC_TYPE_TRANSACTION in ('CA') and DOC_DATE_PERIODE>'19010101'
and DOC_CODE_SOCIETE+DOC_CODE_SOUS_CPTE_ANALYSE+SCA_CODE_CENTRE_ANALYSE+PER_CODE_PERIODE not in (select BUD_CODE_SOCIETE+BUD_CODE_SOUS_COMPTE_ANALYSE+SCA_CODE_CENTRE_ANALYSE+BUD_CODE_PERIODE from DATAMART_056_002)
<SEPARATEUR>
if  exists (select * from sysobjects where name='DATAMART_056_001' and xtype='U') drop table DATAMART_056_001
<SEPARATEUR>
/****** Object:  Table [dbo].[DATAMART_056_001]    Script Date: 19/02/2015 15:23:51 ******/
CREATE TABLE [dbo].[DATAMART_056_001](
	[DOC_TYPE_TRANSACTION] [varchar](10) NOT NULL,
	[DOC_CODE_SOCIETE] [varchar](12) NOT NULL,
	[SOC_RAISON_SOCIALE] [varchar](45) NULL,
	[CTA_CODE_CENTRE_ANALYSE] [varchar](8) NULL,
	[CTA_DESIGNATION] [varchar](60) NULL,
	[DOC_CODE_SOUS_CPTE_ANALYSE] [varchar](14) NOT NULL,
	[SCA_DESIGNATION] [varchar](60) NULL,
	[SCA_CS01] [varchar](30) NULL,
	[CTA_CS01] [varchar](30) NULL,
	[DOC_CODE_SOUS_FAMILLE] [varchar](7) NOT NULL,
	[SF_DESIGNATION] [varchar](30) NULL,
	[DOC_CODE_FOURNISSEUR] [varchar](12) NOT NULL,
	[FRS_RAISON_SOCIALE] [varchar](45) NULL,
	[FRS_VILLE] [varchar](45) NULL,
	[DOC_NUMERO_DOC] [int] NOT NULL,
	[DOC_NUMERO_LIGNE_1] [int] NOT NULL,
	[DOC_DESIGNATION_ARTICLE_CDE] [varchar](60) NOT NULL,
	[DOC_DESIGNATION_COMP_CDE] [varchar](201) NOT NULL,
	[DOC_CODE_ACHETEUR] [varchar](30) NOT NULL,
	[ACH_NOM_ACHETEUR] [varchar](60) NULL,
	[DOC_DATE_CREATION_CDE] [datetime] NULL,
	[DOC_DATE_PERIODE_FAC] [datetime] NULL,
	[DOC_DATE_DOC] [datetime] NULL,
	[DOC_CODE_SECTION_IMPUTATION] [varchar](14) NOT NULL,
	[SI_DESIGNATION] [varchar](60) NULL,
	[SI_CODE_DIRECTION] [varchar](13) NULL,
	[SI_DESIGNATION_DIRECTION] [varchar](60) NULL,
	[DOC_MONTANT_LIGNE_CDE] [float] NOT NULL,
	[DOC_MONTANT_RECEPTION_CDE] [float] NOT NULL,
	[DOC_MONTANT_DEJA_FACTURE_CDE] [float] NOT NULL,
	[DOC_MONTANT_FAR_CDE] [float] NOT NULL,
	[DOC_MONTANT_RAL_CDE] [float] NOT NULL,
	[BUD_CODE_PERIODE] [varchar](11) NOT NULL
) ON [PRIMARY]
<SEPARATEUR>
/****** Object:  Index [DATAMART_056_001_K1]    Script Date: 19/02/2015 15:24:07 ******/
CREATE CLUSTERED INDEX [DATAMART_056_001_K1] ON [dbo].[DATAMART_056_001]
(
	[BUD_CODE_PERIODE] ASC,
  [DOC_CODE_SOCIETE] ASC,
	[CTA_CODE_CENTRE_ANALYSE] ASC,
	[DOC_CODE_SOUS_CPTE_ANALYSE] ASC,
	[DOC_CODE_SOUS_FAMILLE] ASC,
	[DOC_CODE_FOURNISSEUR] ASC,
	[DOC_TYPE_TRANSACTION] ASC,
	[DOC_NUMERO_DOC] ASC,
	[DOC_NUMERO_LIGNE_1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
<SEPARATEUR>
insert into DATAMART_056_001
select DOC_TYPE_TRANSACTION,DOC_CODE_SOCIETE, SOC_RAISON_SOCIALE,CTA_CODE_CENTRE_ANALYSE,CTA_DESIGNATION,DOC_CODE_SOUS_CPTE_ANALYSE,SCA_DESIGNATION,SCA_CS01,CTA_CS01,DOC_CODE_SOUS_FAMILLE,SF_DESIGNATION,DOC_CODE_FOURNISSEUR, 
FRS_RAISON_SOCIALE,FRS_VILLE, DOC_NUMERO_DOC,DOC_NUMERO_LIGNE_1, DOC_DESIGNATION_ARTICLE_CDE,DOC_DESIGNATION_COMP_CDE,DOC_CODE_ACHETEUR,ACH_NOM_ACHETEUR,DOC_DATE_CREATION_CDE,DOC_DATE_PERIODE_FAC,DOC_DATE_ARRIVEE_PREVUE as DOC_DATE_DOC,DOC_CODE_SECTION_IMPUTATION,
SI_DESIGNATION,SI_CODE_DIRECTION,SI_DESIGNATION_DIRECTION,DOC_MONTANT_LIGNE_FAC as DOC_MONTANT_LIGNE_CDE,
0 as DOC_MONTANT_RECEPTION_CDE,
DOC_MONTANT_LIGNE_FAC as DOC_MONTANT_DEJA_FACTURE_CDE,
0 as DOC_MONTANT_FAR_CDE,
0 as DOC_MONTANT_RAL_CDE,
'           ' as BUD_CODE_PERIODE
from DOCUMENTS
left outer join SOCIETES on DOC_CODE_SOCIETE=SOC_CODE_SOCIETE
left outer join SOUS_CPTES_ANALYSE on  DOC_CODE_SOCIETE=SCA_CODE_SOCIETE and  DOC_CODE_SOUS_CPTE_ANALYSE=SCA_CODE_SOUS_CPTE_ANALYSE
left outer join CENTRES_ANALYSES on DOC_CODE_SOCIETE=CTA_CODE_SOCIETE and SCA_CODE_CENTRE_ANALYSE=CTA_CODE_CENTRE_ANALYSE
left outer join SOUS_FAMILLES on DOC_CODE_SOUS_FAMILLE=SF_CODE_SOUS_FAMILLE
left outer join FOURNISSEURS on DOC_CODE_FOURNISSEUR=FRS_CODE_FOURNISSEUR
left outer join ACHETEURS on DOC_CODE_ACHETEUR=ACH_CODE_ACHETEUR
left outer join SECTIONS_IMPUTATIONS on DOC_CODE_SOCIETE=SI_CODE_SOCIETE and DOC_CODE_SECTION_IMPUTATION=SI_CODE_SECTION_IMPUTATION
where
DOC_TYPE_TRANSACTION = 'FAC' and DOC_NUMERO_LIGNE_CDE=0 and DOC_MONTANT_LIGNE_FAC>0
and rtrim(DOC_CODE_SOCIETE) + rtrim(DOC_CODE_SOUS_CPTE_ANALYSE) in (select rtrim(b.CODE_SOC)+ rtrim(AXE_UN) from <PREFIXE_HA>BUD_1_VAL v left outer join <PREFIXE_HA>BUDGETS b on v.CODE_BUD=b.CODE_BUD where ACTIF=0x54)
<SEPARATEUR>
SET DATEFORMAT DMY update DATAMART_056_001 set DOC_DATE_DOC=DOC_DATE_PERIODE_FAC where DOC_TYPE_TRANSACTION='FAC'
<SEPARATEUR>
update doc set BUD_CODE_PERIODE  = bud.BUD_CODE_PERIODE
from DATAMART_056_001 doc, DATAMART_056_002 bud
where DOC_CODE_SOCIETE=BUD_CODE_SOCIETE and DOC_CODE_SOUS_CPTE_ANALYSE=BUD_CODE_SOUS_COMPTE_ANALYSE 
and DOC_DATE_DOC>=BUD_DATE_DEBUT and DOC_DATE_DOC<=BUD_DATE_FIN
<SEPARATEUR>
delete from DATAMART_056_001  where BUD_CODE_PERIODE=' '
<SEPARATEUR>
/* Curseur pour insertion des lignes de commande sur chaque période */
DECLARE @CodePeriode varchar(7)
DECLARE @DteDebPeriode varchar(8)
DECLARE @DteFinPeriode varchar(8)

DECLARE periode_cur  CURSOR  
FOR
SELECT CODE_PER, DATE_DEB,DATE_FIN from <PREFIXE_HA>PERIODES

OPEN periode_cur

FETCH NEXT FROM periode_cur 
INTO @CodePeriode, @DteDebPeriode, @DteFinPeriode

WHILE (@@fetch_status = 0)
BEGIN
/* Insertion jeux de données principal */
insert into DATAMART_056_001
select DOC_TYPE_TRANSACTION,DOC_CODE_SOCIETE, SOC_RAISON_SOCIALE,CTA_CODE_CENTRE_ANALYSE,CTA_DESIGNATION,DOC_CODE_SOUS_CPTE_ANALYSE,SCA_DESIGNATION,SCA_CS01,CTA_CS01,DOC_CODE_SOUS_FAMILLE,SF_DESIGNATION,DOC_CODE_FOURNISSEUR, 
FRS_RAISON_SOCIALE,FRS_VILLE, DOC_NUMERO_DOC,DOC_NUMERO_LIGNE_1, DOC_DESIGNATION_ARTICLE_CDE,DOC_DESIGNATION_COMP_CDE,DOC_CODE_ACHETEUR,ACH_NOM_ACHETEUR,DOC_DATE_CREATION_CDE,DOC_DATE_PERIODE_FAC,DOC_DATE_ARRIVEE_PREVUE as DOC_DATE_DOC,DOC_CODE_SECTION_IMPUTATION,
SI_DESIGNATION,SI_CODE_DIRECTION,SI_DESIGNATION_DIRECTION,
/* Mt Cde */
DOC_MONTANT_LIGNE_CDE,
/* Montant réception */
0 as DOC_MONTANT_RECEPTION_CDE,
/* Déjà facturé */
0 as DOC_MONTANT_DEJA_FACTURE_CDE,
/* FAR */
0 as DOC_MONTANT_FAR_CDE,
/* RAL */
0 as DOC_MONTANT_RAL_CDE,
@CodePeriode as BUD_CODE_PERIODE
from DOCUMENTS
left outer join SOCIETES on DOC_CODE_SOCIETE=SOC_CODE_SOCIETE
left outer join SOUS_CPTES_ANALYSE on  DOC_CODE_SOCIETE=SCA_CODE_SOCIETE and  DOC_CODE_SOUS_CPTE_ANALYSE=SCA_CODE_SOUS_CPTE_ANALYSE
left outer join CENTRES_ANALYSES on DOC_CODE_SOCIETE=CTA_CODE_SOCIETE and SCA_CODE_CENTRE_ANALYSE=CTA_CODE_CENTRE_ANALYSE
left outer join SOUS_FAMILLES on DOC_CODE_SOUS_FAMILLE=SF_CODE_SOUS_FAMILLE
left outer join FOURNISSEURS on DOC_CODE_FOURNISSEUR=FRS_CODE_FOURNISSEUR
left outer join ACHETEURS on DOC_CODE_ACHETEUR=ACH_CODE_ACHETEUR
left outer join SECTIONS_IMPUTATIONS on DOC_CODE_SOCIETE=SI_CODE_SOCIETE and DOC_CODE_SECTION_IMPUTATION=SI_CODE_SECTION_IMPUTATION
where DOC_TYPE_TRANSACTION = 'CA' 
and rtrim(DOC_CODE_SOCIETE) + rtrim(DOC_CODE_SOUS_CPTE_ANALYSE) in 
(select rtrim(b.CODE_SOC)+ rtrim(AXE_UN) from <PREFIXE_HA>BUD_1_VAL v left outer join <PREFIXE_HA>BUDGETS b on v.CODE_BUD=b.CODE_BUD where ACTIF=0x54)
and convert (char(8),DOC_DATE_PERIODE_CDE,112)<=@DteFinPeriode and (DOC_DATE_SOLDE_FINANCIER_CDE is null or convert (char(8),DOC_DATE_SOLDE_FINANCIER_CDE,112)='19000101' or convert (char(8),DOC_DATE_SOLDE_FINANCIER_CDE,112)>=@DteDebPeriode)

/* Calcul du réceptionné de la période */
update DATAMART_056_001 set DOC_MONTANT_RECEPTION_CDE=isnull((select sum(br.DOC_MONTANT_LIGNE_BR) 
from DOCUMENTS br where br.DOC_TYPE_TRANSACTION='BR' and br.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and br.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),br.DOC_DATE_PERIODE_BR,112) between @DteDebPeriode and @DteFinPeriode),0)
where DOC_TYPE_TRANSACTION='CA' and BUD_CODE_PERIODE=@CodePeriode

/* Calcul du reste à livrer cde */
update DATAMART_056_001 set DOC_MONTANT_RAL_CDE=DOC_MONTANT_LIGNE_CDE-isnull((select sum(br.DOC_MONTANT_LIGNE_BR) 
from DOCUMENTS br where br.DOC_TYPE_TRANSACTION='BR' and br.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and br.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),br.DOC_DATE_PERIODE_BR,112)<=@DteFinPeriode),0)
where DOC_TYPE_TRANSACTION='CA' and BUD_CODE_PERIODE=@CodePeriode

/* On enlève du mt ligne commande ce qui a été réceptionné dans les périodes antérieures ATTENTION : doit rester après le calcul du RAL  */
update DATAMART_056_001 set DOC_MONTANT_LIGNE_CDE=DOC_MONTANT_LIGNE_CDE-isnull((select sum(br.DOC_MONTANT_LIGNE_BR) 
from DOCUMENTS br where br.DOC_TYPE_TRANSACTION='BR' and br.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and br.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),br.DOC_DATE_PERIODE_BR,112) < @DteDebPeriode),0)
where DOC_TYPE_TRANSACTION='CA' and BUD_CODE_PERIODE=@CodePeriode

/* Calcul du montant facturé dans la période */
update DATAMART_056_001 set DOC_MONTANT_DEJA_FACTURE_CDE=isnull((select sum(fa.DOC_MONTANT_LIGNE_FAC) 
from DOCUMENTS fa where fa.DOC_TYPE_TRANSACTION='FAC' and fa.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and fa.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),fa.DOC_DATE_PERIODE_FAC,112) between @DteDebPeriode and @DteFinPeriode),0)
where DOC_TYPE_TRANSACTION='CA' and BUD_CODE_PERIODE=@CodePeriode

/* Calcul de la FAR */
update DATAMART_056_001 set DOC_MONTANT_FAR_CDE=(isnull((select sum(br.DOC_MONTANT_LIGNE_BR) 
from DOCUMENTS br where br.DOC_TYPE_TRANSACTION='BR' and br.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and br.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),br.DOC_DATE_PERIODE_BR,112)<=@DteFinPeriode),0)-isnull((select sum(fa.DOC_MONTANT_LIGNE_FAC) 
from DOCUMENTS fa where fa.DOC_TYPE_TRANSACTION='FAC' and fa.DOC_NUMERO_CDE=DATAMART_056_001.DOC_NUMERO_DOC and fa.DOC_NUMERO_LIGNE_CDE=DATAMART_056_001.DOC_NUMERO_LIGNE_1
and convert (char(8),fa.DOC_DATE_PERIODE_FAC,112)<=@DteFinPeriode),0))
where DOC_TYPE_TRANSACTION='CA' and BUD_CODE_PERIODE=@CodePeriode

/* Calcul de la date de dernière facture par ligne de cde pour la période*/
update DATAMART_056_001 set DOC_DATE_PERIODE_FAC=(select max(fa.DOC_DATE_PERIODE_FAC) from DOCUMENTS fa where fa.DOC_TYPE_TRANSACTION='FAC' and fa.DOC_NUMERO_CDE=m.DOC_NUMERO_DOC and fa.DOC_NUMERO_LIGNE_CDE=m.DOC_NUMERO_LIGNE_1 and convert (char(8),fa.DOC_DATE_PERIODE_FAC,112)<=@DteFinPeriode)
from DATAMART_056_001 m
where m.DOC_TYPE_TRANSACTION='CA' and m.BUD_CODE_PERIODE=@CodePeriode

FETCH NEXT FROM periode_cur INTO @CodePeriode, @DteDebPeriode, @DteFinPeriode
END
CLOSE periode_cur
DEALLOCATE periode_cur
<SEPARATEUR>
/* Traitement des négatifs */
update DATAMART_056_001 set DOC_MONTANT_LIGNE_CDE=0 where DOC_MONTANT_LIGNE_CDE<0
update DATAMART_056_001 set DOC_MONTANT_RAL_CDE=0 where DOC_MONTANT_RAL_CDE<0
update DATAMART_056_001 set DOC_MONTANT_FAR_CDE=0 where DOC_MONTANT_FAR_CDE<0

<SEPARATEUR>
SET DATEFORMAT DMY update DATAMART_056_001 set DOC_DATE_DOC=DOC_DATE_CREATION_CDE where DOC_TYPE_TRANSACTION='CA'
