IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'OXADW_SPE056_001')
   DROP PROCEDURE OXADW_SPE056_001

<SEPARATEUR>

/*Procédure stockée pour de SPE056 Suivi Glaobal Engagé*/

CREATE PROCEDURE OXADW_SPE056_001 
		@LoginUser char(30), 
		@OXASQLDB char(20), 
		@ISLExercice char(7), 
		@ISLSociete char(7), 
		@ISLProjet char(14), 
		@ISLLieuCommande char(14), 
		@ISLFournisseur char(12), 
		@ISLDemandeur char(30), 
		@ISLSousFamille char(7), 
		@ChoixCde char(1) 

AS

declare @Choix1 varchar(8000)
declare @Choix2 varchar(8000)
declare @ReqPrincipale varchar (8000)
declare @ReqFin varchar (8000)
declare @ReqExecute varchar (8000)
DECLARE @UOmere varchar (15)
DECLARE @ListUO varchar (3000)

/*Obligé de remplir car au premier lancement on a pas encore le filtre de la période*/
IF @ISLExercice =''
	BEGIN
	set @ISLExercice = (select top 1 CODE_PER from <PREFIXE_HA>PERIODES with(nolock) order by CODE_PER DESC)
END



IF @ChoixCde='1' or @ChoixCde=''
	BEGIN
	set @Choix1 = ''
	set @UOmere= (select UO_CODE_UO from UO with(nolock) where UO_CODE_RESP_HIERARCHIQUE=@LoginUser)
	set @ListUO=char(39)+'|'+rtrim(@UOmere)+'|'+char(39)
	DECLARE @ChaineUOParent varchar (3000)
	set @ChaineUOParent=char(39)+'|'+rtrim(@UOmere)+'|'+char(39)

	DECLARE @NivDepart int
	set @NivDepart=(select UO_NIVEAU from UO with(nolock) where (UO_CODE_SOCIETE in (@ISLSociete) or '' in (@ISLSociete)) and UO_CODE_UO=@UOmere)

	DECLARE @NivMax int
	set @NivMax=(select max(UO_NIVEAU) from UO)

	DECLARE @UOLue varchar (15)
	set @UOLue=''

	DECLARE @BoucleNiveau int
	set @BoucleNiveau=0

	DECLARE @requete varchar(5000)
	set @requete=''

	print @NivDepart
	print @NivMax

	WHILE @BoucleNiveau<@NivMax-@NivDepart
	BEGIN


	DECLARE cl_cur  CURSOR  
	FOR
	/*SELECT CODE_UO FROM UO with(nolock) WHERE CODE_STE=@ste and UO_MERE in (@ChaineUOParent)*/
	SELECT UO_CODE_UO FROM UO with(nolock) WHERE (UO_CODE_SOCIETE in (@ISLSociete) or '' in (@ISLSociete) ) and PATINDEX ('%|'+rtrim(UO_MERE)+'|%',@ChaineUOParent)<>0

	set @ChaineUOParent=''
	OPEN cl_cur
		FETCH NEXT FROM cl_cur INTO @UOLue
		WHILE (@@fetch_status = 0)
		BEGIN
		if @ChaineUOParent = ''
		set @ChaineUOParent = char(39)+'|'+rtrim(@UOLue)+'|'+char(39)
		else
		set @ChaineUOParent = rtrim(@ChaineUOParent)+','+char(39)+'|'+rtrim(@UOLue)+'|'+char(39)
		if @ListUO = ''
		set @ListUO = char(39)+'|'+rtrim(@UOLue)+'|'+char(39)
		else
		set @ListUO = rtrim(@ListUO)+','+char(39)+'|'+@UOLue+'|'+char(39)
		set @UOLue = ''
		FETCH NEXT FROM cl_cur INTO @UOLue
	  END
	CLOSE cl_cur
	DEALLOCATE cl_cur
		/*print @chaineUOParent  */
	set @BoucleNiveau=@BoucleNiveau+1
	END

	set @Choix1 = 'DOC_CODE_ACHETEUR in (SELECT CODE from '+rtrim(@OXASQLDB)+'HASTUTIL with(nolock) where PATINDEX (''%|''+rtrim(CODE_UO)+''|%'','+@ListUO+')<>0)'

END

set @Choix2 = ''''+@LoginUser+''''+' in (select VALEUR from '+rtrim(@OXASQLDB)+'CS_DON with(nolock) where COD_TAB = ''DIRECTIO''
	AND NUM_FIX = 1
	AND COD_ENR = (substring(DOC_CODE_SOCIETE+''       '',1,7)+SI_CODE_DIRECTION))'

set @ReqPrincipale = 'select 
	DOC_CODE_SOCIETE + '' - '' + SOCIETES.SOC_RAISON_SOCIALE as [Societe],
	SF_DESIGNATION as [DesignationSousFamille],
	DOC_DESIGNATION_COMP_CDE as [Designation],
	FRS_RAISON_SOCIALE +'', '' + FRS_VILLE as [Fournisseur],
	DOC_CODE_SOUS_CPTE_ANALYSE as [No_de_projet],
	SCA_DESIGNATION as [Designation_de_projet],
	DEM_NOM_DEMANDEUR as [Demandeur],
	SI_CODE_DIRECTION as [Lieu_analytique],
	DOC_NUMERO_CDE as [No_de_commande],
	Convert(varchar(10),DOC_DATE_CREATION_CDE, 103) as [Date_de_la_commande],
	Convert(varchar(10),DOC_DATE_PERIODE_FAC, 103) as [Date_de_periode_de_facture],
	SI_CODE_DIRECTION as [Code_CRB_de_la_commande],
	SI_DESIGNATION_DIRECTION as [Libelle_du_CRB_de_la_commande],
	DOC_CODE_SECTION_IMPUTATION as [Section_imputation],
	SUM(DOC_MONTANT_LIGNE_FAC) as [MontantLigneFacturee],
	MAX(DOC_MONTANT_LIGNE_CDE-
	CASE DOC_ETAT_CDE when ''S'' then DOC_QUANTITE_RESTANT_CDE*DOC_PRIX_UNITAIRE_CDE
	else 0 end) as [MontantEngage],
	MAX(DOC_MONTANT_LIGNE_CDE-(DOC_QUANTITE_RESTANT_CDE*DOC_PRIX_UNITAIRE_CDE)) as [MontantRealise],
	MAX((case when DOC_QUANTITE_RESTANT_CDE <0 then 0 else DOC_QUANTITE_RESTANT_CDE end )*DOC_PRIX_UNITAIRE_CDE) as [MontantRAL],
	DOC_CODE_SOCIETE, SOC_RAISON_SOCIALE, FRS_RAISON_SOCIALE, FRS_VILLE
from DOCUMENTS with(nolock) left outer join SOCIETES with(nolock) on DOC_CODE_SOCIETE=SOC_CODE_SOCIETE
left outer join SOUS_CPTES_ANALYSE with(nolock) on  DOC_CODE_SOCIETE=SCA_CODE_SOCIETE and  DOC_CODE_SOUS_CPTE_ANALYSE=SCA_CODE_SOUS_CPTE_ANALYSE
left outer join CENTRES_ANALYSES with(nolock) on DOC_CODE_SOCIETE=CTA_CODE_SOCIETE and SCA_CODE_CENTRE_ANALYSE=CTA_CODE_CENTRE_ANALYSE
left outer join SOUS_FAMILLES with(nolock) on DOC_CODE_SOUS_FAMILLE=SF_CODE_SOUS_FAMILLE
left outer join FOURNISSEURS with(nolock) on DOC_CODE_FOURNISSEUR=FRS_CODE_FOURNISSEUR
left outer join ACHETEURS with(nolock) on DOC_CODE_ACHETEUR=ACH_CODE_ACHETEUR
left outer join DEMANDEURS with(nolock) on DOC_CODE_DEMANDEUR=DEM_CODE_DEMANDEUR
left outer join SECTIONS_IMPUTATIONS with(nolock) on DOC_CODE_SOCIETE=SI_CODE_SOCIETE and DOC_CODE_SECTION_IMPUTATION=SI_CODE_SECTION_IMPUTATION
where 
((DOC_TYPE_TRANSACTION=''FAC'' and 
	(	(DOC_MONTANT_DEJA_FACTURE_CDE<DOC_MONTANT_LIGNE_CDE and DOC_ETAT_CDE=''S'' and DOC_ETAT_FINANCIER=''F'' and 
			(select CODE_PER from '+rtrim(@OXASQLDB)+'PERIODES with(nolock) where DOCUMENTS.DOC_DATE_CREATION_CDE>=DATE_DEB and DOCUMENTS.DOC_DATE_CREATION_CDE<=DATE_FIN
			) In ('''+@ISLExercice+''')
		) 
		OR DOC_ETAT_FINANCIER<>''F''
	) 
)
OR (DOC_TYPE_TRANSACTION=''CA'' and DOC_NUMERO_FAC=0)
) 
AND ( 
		(DOC_CODE_SOCIETE In ('''+@ISLSociete+''') or '''' In ('''+@ISLSociete+''')) and 
		(DOC_CODE_SOUS_CPTE_ANALYSE In ('''+@ISLProjet+''') or '''' In ('''+@ISLProjet+''')) and 
		(DOC_CODE_SECTION_IMPUTATION In ('''+@ISLLieuCommande+''') or '''' In ('''+@ISLLieuCommande+''')) and 
		(DOC_CODE_FOURNISSEUR In ('''+@ISLFournisseur+''') or '''' In ('''+@ISLFournisseur+''')) and 
		(DOC_CODE_ACHETEUR In ('''+@ISLDemandeur+''') or '''' In ('''+@ISLDemandeur+''')) and 
		(DOC_CODE_SOUS_FAMILLE In ('''+@ISLSousFamille+''') or '''' In ('''+@ISLSousFamille+''')) and 
			((select CODE_PER from '+rtrim(@OXASQLDB)+'PERIODES with(nolock) where DOCUMENTS.DOC_DATE_CREATION_CDE>=DATE_DEB and DOCUMENTS.DOC_DATE_CREATION_CDE<=DATE_FIN
			) In ('''+@ISLExercice+'''))
	)'
	

set @ReqFin= 'group by DOC_CODE_SOCIETE, SOC_RAISON_SOCIALE, SF_DESIGNATION, DOC_DESIGNATION_COMP_CDE, FRS_RAISON_SOCIALE, FRS_VILLE, DOC_CODE_SOUS_CPTE_ANALYSE,
SCA_DESIGNATION, SI_CODE_DIRECTION, DOC_DATE_CREATION_CDE, DOC_CODE_SECTION_IMPUTATION, DEM_NOM_DEMANDEUR, DOC_NUMERO_CDE, DOC_NUMERO_LIGNE_CDE, DOC_DATE_PERIODE_FAC, SI_DESIGNATION_DIRECTION'


set @ReqExecute = @ReqPrincipale+case @ChoixCde when '1' then ' AND '+@Choix1 when '2' then ' AND '+@Choix2 when '' then ' AND ('+@Choix1+' OR '+@Choix2+')' else '' end +' '+@ReqFin


exec 	(@ReqExecute)