SQLize Online / PHPize Online  /  SQLtest Online

USE [GCIMMO_Orbis] GO /****** Object: StoredProcedure [dbo].[GenererContrat] Script Date: 09/01/2024 11:55:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ------------------------------------------ ALTER procedure [dbo].[GenererContrat] (@Code_Reservation int, @Aleatoire NVARCHAR(50), @Type_Doc as nvarchar(1)) AS BEGIN DECLARE @Req NVARCHAR(max) DECLARE @Sql NVARCHAR(max) DECLARE @ReqPartie2 NVARCHAR(max) DECLARE @ReqPartie3 NVARCHAR(max) DECLARE @ReqPartie4 NVARCHAR(max) DECLARE @ReqPartie5 NVARCHAR(max) DECLARE @Bien NVARCHAR(50) DECLARE @Nature NVARCHAR(50) DECLARE @Nature_Lib NVARCHAR(50) DECLARE @date_reservation NVARCHAR(50) DECLARE @date_livraison NVARCHAR(50) DECLARE @Date_concretisation NVARCHAR(50) DECLARE @Client NVARCHAR(2000) DECLARE @Echeances NVARCHAR(4000) DECLARE @Infos_Client_Contrat_serenitylinving NVARCHAR(4000) DECLARE @Infos_Client_Contrat_Reservation NVARCHAR(4000) DECLARE @Client_NomPrenom NVARCHAR(4000) DECLARE @Client_Nom NVARCHAR(max) DECLARE @Client_Prenom NVARCHAR(max) DECLARE @Client_Recu NVARCHAR(2000) DECLARE @Client_promesse NVARCHAR(2000) DECLARE @TF_mere NVARCHAR(50) DECLARE @TF NVARCHAR(50) DECLARE @Superficie real DECLARE @SupJardin real DECLARE @SupMezzanine real DECLARE @SupTerCouverte real DECLARE @prix_vente DECIMAL(18,2) /* ajouté par Reda */ DECLARE @prix_initial DECIMAL(18,2) DECLARE @Gh NVARCHAR(50) DECLARE @Tranche NVARCHAR(50) DECLARE @Ville_Pgm NVARCHAR(50) DECLARE @Operateur NVARCHAR(50) DECLARE @Etage NVARCHAR(50) DECLARE @Num_Bien NVARCHAR(50) DECLARE @Sup_EnLettre NVARCHAR(200) DECLARE @PV_EnLettre NVARCHAR(200) DECLARE @Immeuble NVARCHAR(50) DECLARE @Avc_convenue DECIMAL(18,2) DECLARE @Prix_m2 nvarchar(50) DECLARE @logo VARBINARY(MAX) /*DECLARE @Prix_m22 DECIMAL(18,2)*/ DECLARE @Avc_convenue_EnLettre NVARCHAR(200) DECLARE @Client_Light NVARCHAR(2000) DECLARE @Descriptif NVARCHAR(250) DECLARE @Annexes NVARCHAR(2000) DECLARE @Code_Bien INT DECLARE @Reliquat_Avc REAL DECLARE @Reliquat_Avc_EnLettre NVARCHAR(200) DECLARE @Reliquat REAL DECLARE @Reliquat_EnLettre NVARCHAR(200) DECLARE @Echeances_Avc NVARCHAR(4000) DECLARE @Liste_Echeances_Contrat NVARCHAR(4000) DECLARE @Liste_Echeances_Contrat_Mag NVARCHAR(4000) DECLARE @Liste_Echeances_AccordDesignation NVARCHAR(MAX) DECLARE @Liste_Echeances_promesse NVARCHAR(3000) DECLARE @Liste_Encaissements_Contrat_Mag NVARCHAR(4000) DECLARE @Liste_Encaissements_Eng_Mag NVARCHAR(4000) DECLARE @Echeances_LEco NVARCHAR(4000) DECLARE @Sup_Ter_Mez_Type NVARCHAR(50) DECLARE @Sup_Ter_Mez real DECLARE @Sup_Ter_Mez_EnLettre NVARCHAR(200) DECLARE @Sup_TF_Formule real DECLARE @SupTitre real DECLARE @SupConstruite real DECLARE @Sup_TF_Formule_EnLettre NVARCHAR(200) DECLARE @Consistance NVARCHAR(200) DECLARE @Projet NVARCHAR(200) DECLARE @Capital DECIMAL(18,2) DECLARE @RegCommerce NVARCHAR(200) DECLARE @SiegeSocial NVARCHAR(max) DECLARE @Liste_Encaissement_Contrat_Arsat_Bernoussi_T3 NVARCHAR(max) DECLARE @Observation NVARCHAR(200) DECLARE @AdresseProjet NVARCHAR(200) DECLARE @Standing NVARCHAR(10) DECLARE @Signataire NVARCHAR(200) DECLARE @Sig_Light NVARCHAR(200) DECLARE @Tribunal NVARCHAR(50) DECLARE @AVANCE REAL DECLARE @Nombre_Annexes nvarchar(50) DECLARE @EncaissAvance REAL DECLARE @AVANCE_EnLettre NVARCHAR(200) DECLARE @Reliquat_EncaissAvance REAL DECLARE @DateImpression nvarchar(50) DECLARE @Paiement nvarchar(50) DECLARE @EcheancesSOCIAL NVARCHAR(4000) DECLARE @Echeances_2 NVARCHAR(4000) DECLARE @Echeances_LEcoSAB NVARCHAR(4000) DECLARE @Echeances_LEco3 NVARCHAR(4000) DECLARE @Echeances_LEco4 NVARCHAR(4000) DECLARE @Echeances_LEco5 NVARCHAR(4000) DECLARE @Infos_EncaisAvance NVARCHAR(4000) DECLARE @Penalite NVARCHAR(10) DECLARE @num_dossier nvarchar(50) DECLARE @Date_PrevueLivraison nvarchar(50) DECLARE @Client_CR NVARCHAR(4000) DECLARE @Ech_CR NVARCHAR(4000) DECLARE @Client_CR_Credit NVARCHAR(4000) DECLARE @Type_Bien nvarchar(50) DECLARE @Num_Patente NVARCHAR(50) DECLARE @IdFisc NVARCHAR(50) DECLARE @ICE NVARCHAR(50) DECLARE @denomination NVARCHAR(200) DECLARE @conservation_fonciere NVARCHAR(200) DECLARE @Sup_Projet NVARCHAR(200) DECLARE @Tf_Projet NVARCHAR(200) DECLARE @supjardin_EnLettre NVARCHAR(200) DECLARE @SupConstruite_EnLettre NVARCHAR(200) DECLARE @prix_metre_carreEnlettre NVARCHAR(200) DECLARE @Client_Details_Contrat NVARCHAR(4000) DECLARE @Client_Engagement_Mandataire_HTML NVARCHAR(4000) DECLARE @Liste_Echeances_Contrat_Arsat_Bernoussi_T3 NVARCHAR(4000) DECLARE @Liste_Encaissement_Engagment_magasin_Arsat_Bernoussi_T3 NVARCHAR(4000) DECLARE @Liste_Encaissement_Contrat_serenitylinving NVARCHAR(4000) DECLARE @Client_Engagement NVARCHAR(2000) DECLARE @Echeances_Engagement_Html NVARCHAR(4000) DECLARE @Liste_Encaissement_Engagment_Arsat_Bernoussi_T3 NVARCHAR(MAX) DECLARE @Client_reservation NVARCHAR(4000) DECLARE @EncaissAv_En_Lettre NVARCHAR(4000) DECLARE @Reliquat_EncaissEn_Lettre NVARCHAR(4000) DECLARE @sup_En_Lettre_Minu NVARCHAR(4000) DECLARE @PV_EnLettre_Minu NVARCHAR(4000) DECLARE @EncaissAvance_totale nvarchar(100) DECLARE @EncaissAvance_totale_En_Lettre NVARCHAR(4000) DECLARE @Reliquat_EncaissAvance_N_EnLettres NVARCHAR(4000) DECLARE @Reliquat_EncaissAvance_N NVARCHAR(100) DECLARE @Avance_Convenue REAL DECLARE @Avance_Convenue_EnLettre NVARCHAR(100) DECLARE @P_Versement_Reliquat NVARCHAR(max) DECLARE @Client_Adresse NVARCHAR(MAX) DECLARE @ClientTEL NVARCHAR(MAX) DECLARE @Infos_Notaire NVARCHAR(max) DECLARE @VilleByCodeClient nvarchar(50) DECLARE @Civilite_Client nvarchar(200) DECLARE @Cilvilite_ONLY nvarchar(200) DECLARE @Dt_Naissance NVARCHAR(2000) DECLARE @LieuClient nvarchar(200) DECLARE @NationaliteClient NVARCHAR(200) DECLARE @CINClient nvarchar(200) DECLARE @TelDomicile NVARCHAR(200) DECLARE @EmailClient nvarchar(200) DECLARE @Client_tableHTML nvarchar(2000) DECLARE @ClientFicheVente nvarchar(2000) DECLARE @Statut_Familial_Client nvarchar(max) declare @Responsable nvarchar(200) set @ReqPartie2='' set @ReqPartie3='' set @ReqPartie4='' set @ReqPartie5='' DECLARE Cur_Produit CURSOR FOR SELECT ISNULL(Bien,''), ISnull(Nature,''), ISNULL(Nature_Lib,''), ISNULL(date_reservation,''), ISNULL(Date_concretisation,''), ISNULL(TF_mere,''), ISNULL(TF,''), ISNULL(Superficie,0), ISNULL(prix_vente,0), ISNULL(prix_initial,0), ISNULL(Gh,''),ISNULL(tranche,''),ISNULL(ville_projet,''),ISNULL(Operateur,''), ISNULL(Etage,''), ISNULL(Num_Bien,''), ISNULL(Sup_EnLettre,''), ISNULL(PV_EnLettre,''), ISNULL(Immeuble,''), ISNULL(Avance_Convenue,0), ISNULL(Avance_Convenue_EnLettre,''), ISNULL(descriptif,''), Code_Bien, ISNULL(Reliquat_Avc,0), ISNULL(Reliquat_Avc_EnLettre,''), ISNULL(Reliquat,0), ISNULL(Reliquat_EnLettre,''), ISNULL(Sup_Ter_Mez_Type,''), ISNULL(Sup_Ter_Mez,0), ISNULL(Sup_Ter_Mez_EnLettre,''), ISNULL(Sup_TF_Formule,0), ISNULL(Sup_TF_Formule_EnLettre,''), ISNULL(Descriptif,''), ISNULL(Projet,''), ISNULL(Capital,0), ISNULL(Reg_commerce,''), ISNULL(SiegeSocial,''), ISNULL(Observation,''), ISNULL(AdresseProjet,''), ISNULL(Standing,''), ISNULL(Paiement,''), ISNULL(prix_metre_carre,''), ISNULL(SupTitre,0), ISNULL(SupConstruite,0), ISNULL(Penalite,0), ISNULL(SupJardin,0), ISNULL(SupMezzanine,0), ISNULL(SupTerCouverte,0) ,ISNULL(Date_Livraison,'') , ISNULL(EncaissAvance,0) , ISNULL(Reliquat_EncaissAvance,0) , ISNULL(num_dossier,'') , isnull(Date_PrevueLivraison,''),ISNULL(Num_Patente,''),ISNULL(IdFisc,''),ISNULL(ICE,''),ISNULL(type_Bien,''),ISNULL(denomination,''),ISNULL(conservation_fonciere,''),ISNULL(Sup_Projet,''),ISNULL(Tf_Projet,'TF'),ISNULL(supjardin_EnLettre,''),ISNULL(SupConstruite_EnLettre,''),ISNULL(prix_metre_carreEnlettre,''), ISNULL(Client_NomPrenom,''),ISNULL(Infos_Client_Contrat_Reservation,''),ISNULL(Liste_Echeances_Contrat,''),ISNULL(Liste_Echeances_Contrat_Mag,''),ISNULL(Liste_Encaissements_Contrat_Mag,''),ISNULL(Liste_Encaissements_Eng_Mag,''),Logo,Nombre_Annexes,ISNULL(Client_reservation,''),ISNULL(EncaissAvanceEnlettre,''),ISNULL(Reliquat_EncaissAvanceEnLettre,''),ISNULL(sup_En_Lettre_Minuscule,''),ISNULL(PV_EnLettre_Minu,''),ISNULL(EncaissAvance_totale,0),ISNULL(EncaissAvance_totale_En_Lettre,''),ISNULL(Reliquat_EncaissAvance_N_EnLettres,''),ISNULL(Reliquat_EncaissAvance_N,0) ,isnull(Responsable,'') FROM V_Compromis WHERE Code = @Code_Reservation OPEN Cur_Produit FETCH NEXT FROM Cur_Produit INTO @Bien, @Nature, @Nature_Lib,@date_reservation, @Date_concretisation, @TF_mere, @TF, @Superficie, @prix_vente,@prix_initial, @Gh, @Tranche, @Ville_Pgm, @Operateur, @Etage, @Num_Bien, @Sup_EnLettre, @PV_EnLettre, @Immeuble, @Avc_Convenue, @Avc_convenue_EnLettre,@Descriptif, @Code_Bien, @Reliquat_Avc, @Reliquat_Avc_EnLettre, @Reliquat, @Reliquat_EnLettre, @Sup_Ter_Mez_Type, @Sup_Ter_Mez, @Sup_Ter_Mez_EnLettre,@Sup_TF_Formule, @Sup_TF_Formule_EnLettre, @Consistance, @Projet, @capital, @RegCommerce, @SiegeSocial, @Observation, @AdresseProjet, @Standing, @Paiement, @Prix_m2, @SupTitre, @SupConstruite, @Penalite, @SupJardin, @SupMezzanine, @SupTerCouverte , @date_livraison , @EncaissAvance , @Reliquat_EncaissAvance , @num_dossier , @Date_PrevueLivraison,@Num_Patente,@IdFisc,@ICE,@Type_Bien,@denomination,@conservation_fonciere,@Sup_Projet,@Tf_Projet,@supjardin_EnLettre,@SupConstruite_EnLettre,@prix_metre_carreEnlettre,@Client_NomPrenom, @Infos_Client_Contrat_Reservation,@Liste_Echeances_Contrat,@Liste_Echeances_Contrat_Mag,@Liste_Encaissements_Contrat_Mag,@Liste_Encaissements_Eng_Mag,@logo,@Nombre_Annexes,@Client_reservation,@EncaissAv_En_Lettre,@Reliquat_EncaissEn_Lettre,@sup_En_Lettre_Minu,@PV_EnLettre_Minu,@EncaissAvance_totale,@EncaissAvance_totale_En_Lettre,@Reliquat_EncaissAvance_N_EnLettres,@Reliquat_EncaissAvance_N ,@Responsable DECLARE Cur_Signataire CURSOR FOR SELECT ISNULL(Signataire,''), ISNULL(Sig_Light,''), ISNULL(Tribunal,'') FROM Config_Contrat WHERE ltrim(rtrim(Projet)) = ltrim(rtrim(@Projet)) and ltrim(rtrim(Nature)) = ltrim(rtrim(@nature)) and ltrim(rtrim(standing)) = ltrim(rtrim(@standing)) and ltrim(rtrim(Doc)) = ltrim(rtrim(@Type_Doc)) OPEN Cur_Signataire FETCH NEXT FROM Cur_Signataire INTO @signataire, @Sig_Light, @Tribunal set @Client = dbo.liste_client(@Code_Reservation, 0) set @Infos_Client_Contrat_serenitylinving = dbo.liste_client(@Code_Reservation, 17) set @Client_Recu = dbo.Liste_Client(@Code_Reservation,67) set @Client_promesse = dbo.Liste_Client(@Code_Reservation,54) set @Client_Light = dbo.liste_client(@Code_Reservation, 1) set @Infos_Notaire = dbo.Infos_Notaire(@Code_Reservation) set @Client_Engagement = dbo.Liste_Client(@Code_Reservation,6) set @Client_Engagement_Mandataire_HTML = dbo.Liste_Client(@Code_Reservation,5) SET @P_Versement_Reliquat = dbo.info_Versement_Reliquat_Contrat_Reservation(@Code_Reservation) set @Echeances_Avc = dbo.liste_echeances(@Code_Reservation, 2) set @Liste_Echeances_Contrat_Arsat_Bernoussi_T3=dbo.Liste_Echeances_Contrat_Arsat_Bernoussi_T3(@Code_Reservation) --set @Liste_Echeances_AccordDesignation = dbo.Liste_Echeances_AccordDesignation(@Code_Reservation,1) set @Liste_Encaissement_Engagment_magasin_Arsat_Bernoussi_T3=dbo.Liste_Encaissement_Engagment_magasin_Arsat_Bernoussi_T3(@Code_Reservation) set @Echeances_Engagement_Html = dbo.Liste_Echeances_Engagement_Html(@Code_Reservation, 1) /*set @Annexes = isnull(dbo.Annexes_Bien(@Code_Bien),'')*/ set @AVANCE = dbo.Avance(@Code_Reservation) --set @AVANCE_EnLettre = dbo.NombreEnLettres(@AVANCE) set @Infos_EncaisAvance = dbo.Liste_Encaissement(@Code_Reservation,0) set @Echeances = dbo.liste_ech(@Code_Reservation, @Paiement ) set @Client_Details_Contrat = dbo.Liste_Client(@Code_Reservation,4) set @Client_Nom = dbo.Liste_Client(@Code_Reservation,33) set @Client_Prenom = dbo.Liste_Client(@Code_Reservation,34) set @EcheancesSOCIAL = dbo.Liste_EchSocial(@Code_Reservation, @Paiement ) set @Echeances_2 = dbo.liste_ech2(@Code_Reservation, @Paiement ) Set @Echeances_LEco = dbo.liste_ech_LEco(@Code_Reservation, @Paiement ) Set @Echeances_LEcoSAB = dbo.Liste_Ech_LEco2(@Code_Reservation, @Paiement ) /*Nouria*/ Set @Echeances_LEco3 = dbo.liste_ech_LEco3(@Code_Reservation, @Paiement ) /*Nouria*/ SET @Liste_Encaissement_Contrat_Arsat_Bernoussi_T3=dbo.Liste_Encaissement_Contrat_Arsat_Bernoussi_T3(@Code_Reservation) SET @Liste_Encaissement_Engagment_Arsat_Bernoussi_T3=dbo.Liste_Encaissement_Engagment_Arsat_Bernoussi_T3(@Code_Reservation) SET @Liste_Encaissement_Contrat_serenitylinving=dbo.Liste_Encaissement_Contrat_serenitylinving(@Code_Reservation) /*MEHDIA II*/ Set @Echeances_LEco4 = dbo.liste_ech_LEco4(@Code_Reservation, @Paiement ) /*MEHDIA II*/ set @Client_CR = dbo.liste_client(@Code_Reservation, 10) set @Client_CR_Credit = dbo.liste_client(@Code_Reservation, 11) set @Ech_CR = dbo.Liste_Echeances_CR(@Code_Reservation, 1) set @Liste_Echeances_promesse = dbo.Liste_Echeances_promesse(@Code_Reservation, 1) set @Liste_Echeances_AccordDesignation = dbo.Liste_Echeances_AccordDesignation(@Code_Reservation, 1) /*Hay Hassani MS Titré*/ Set @Echeances_LEco5 = dbo.liste_ech_LEco5(@Code_Reservation, @Paiement ) /*Hay Hassani MS Titré*/ set @Client_Adresse = dbo.Liste_Client(@Code_Reservation,35) set @ClientTEL = dbo.Liste_Client(@Code_Reservation,36) set @VilleByCodeClient = dbo.Liste_Client(@Code_Reservation,37) set @Civilite_Client = dbo.Liste_Client(@Code_Reservation,38) set @Cilvilite_ONLY = dbo.Liste_Client(@Code_Reservation,39) Set @Dt_Naissance = dbo.Liste_Client(@Code_Reservation,40) Set @LieuClient = dbo.Liste_Client(@Code_Reservation,41) Set @NationaliteClient = dbo.Liste_Client(@Code_Reservation,42) Set @CINClient = dbo.Liste_Client(@Code_Reservation,43) Set @TelDomicile = dbo.Liste_Client(@Code_Reservation,44) Set @EmailClient = dbo.Liste_Client(@Code_Reservation,45) Set @Client_tableHTML = dbo.Liste_Client(@Code_Reservation,60) Set @ClientFicheVente = dbo.Liste_Client(@Code_Reservation,61) Set @Statut_Familial_Client = dbo.Liste_Client(@Code_Reservation,46) set @sql= 'Insert into dbo.Compromis_Word (Aleatoire, ' set @req=' Values ('''+ @Aleatoire +''',' set @sql=@sql + 'Bien, ' set @req=@req + '''' + isnull(@Bien,'') +''',' set @sql = @sql + 'Nature, ' set @req = @req + '''' + isnull(@Nature,'') +''',' set @sql = @sql + 'NatureLibelle, ' set @req = @req + '''' + isnull(@Nature_Lib,'') +''',' set @sql = @sql + 'DateReservation, ' set @req = @req + '''' + isnull(@date_reservation,'') +''',' set @sql = @sql + 'DateConcretisation, ' set @req = @req + '''' + isnull(@Date_concretisation,'') +''',' set @sql = @sql + 'DateLivraison, ' set @req = @req + '''' + isnull(@date_livraison,'') +''',' set @sql = @sql + 'Date_PrevueLivraison, ' set @req = @req + '''' + isnull(@Date_PrevueLivraison,'') +''',' set @sql = @sql + 'Client, ' set @req = @req + '''' + isnull(replace(@Client,'''',''''''),'') +''',' set @sql = @sql + 'Infos_Client_Contrat_serenitylinving, ' set @req = @req + '''' + replace(@Infos_Client_Contrat_serenitylinving,'''','''''') +''',' set @sql = @sql + 'Client_Recu, ' set @req = @req + '''' + replace(@Client_Recu,'''','''''') +''',' set @sql = @sql + 'Client_promesse, ' set @req = @req + '''' + replace(@Client_promesse,'''','''''') +''',' set @sql = @sql + 'Client_Details_Contrat, ' set @req = @req + '''' + replace(@Client_Details_Contrat,'''','''''') +''',' set @sql = @sql + 'Echeances, ' set @req = @req + '''' + replace(@Echeances,'''','''''') +''',' set @sql = @sql + 'Liste_Echeances_promesse, ' set @req = @req + '''' + ISNULL(replace(@Liste_Echeances_promesse,'''',''''''),'') +''',' set @sql = @sql + 'Liste_Echeances_AccordDesignation, ' set @req = @req + '''' + ISNULL(replace(@Liste_Echeances_AccordDesignation,'''',''''''),'') +''',' set @sql = @sql + 'Nombre_Annexes, ' set @req = @req + '''' + isnull(replace(@Nombre_Annexes,'''',''''''),0) +''',' set @sql = @sql + 'Infos_EncaisAvance, ' set @req = @req + '''' + isnull(replace(@Infos_EncaisAvance,'''',''''''),'') +''',' set @sql = @sql + 'Liste_Encaissement_Contrat_serenitylinving, ' set @req = @req + '''' + isnull(replace(@Liste_Encaissement_Contrat_serenitylinving,'''',''''''),'') +''',' set @sql = @sql + 'TitreFoncierMere, ' set @req = @req + '''' + isnull(@TF_mere,'') +''',' set @sql = @sql + 'TitreFoncier, ' set @req = @req + '''' + isnull(@TF,'') +''',' set @sql = @sql + 'Superficie, ' set @req = @req + '''' + Convert(NVARCHAR,isnull(@Superficie,0)) +''',' ------------------------------------ set @sql = @sql + 'SuperficieTitre, ' set @req = @req + '''' + Convert(NVARCHAR,isnull(@SupTitre,0)) +''',' set @sql = @sql + 'SuperficieConstruite, ' set @req = @req + '''' + Convert(NVARCHAR,isnull(@SupConstruite,0)) +''',' set @sql = @sql + 'SuperficieJardin, ' set @req = @req + ''''+ Convert(NVARCHAR,isnull(@supjardin,0)) +''',' set @sql = @sql + 'PrixM2, ' set @req = @req + ''''+ isnull(@Prix_m2,CAST(isnull(@prix_vente/@Superficie,0) as nvarchar)) +''',' set @sql = @sql + 'PrixVente, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@prix_vente,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'PrixInitial, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@prix_initial,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'Immeuble, ' set @req = @req + '''' + isnull(@Immeuble,'') +''',' set @sql = @sql + 'Groupement, ' set @req = @req + '''' + isnull(@Gh,'') +''',' set @sql = @sql + 'Tranche, ' set @req = @req + '''' + isnull(@tranche,'') +''',' set @sql = @sql + 'VilleProjet, ' set @req = @req + '''' + isnull(@Ville_Pgm,'') +''',' set @sql = @sql + 'Operateur, ' set @req = @req + '''' + isnull(@Operateur,'') +''',' set @sql = @sql + 'Etage, ' set @req = @req + '''' + isnull(@Etage,'') +''',' set @sql = @sql + 'NumeroBien, ' set @req = @req + '''' + isnull(@Num_Bien,'') +''',' set @sql = @sql + 'SuperficieEnlettre, ' set @req = @req + '''' + isnull(@Sup_EnLettre,'') +''',' set @sql = @sql + 'PrixVenteEnlettre, ' set @req = @req + '''' + isnull(@PV_EnLettre,'') +''',' set @sql = @sql + 'Client_NomPrenom, ' set @req = @req + '''' + replace(isnull(@Client_NomPrenom, ''),'''','''''') +''',' set @sql = @sql + 'Client_Nom, ' set @req = @req + '''' + replace(isnull(@Client_Nom,''),'''','''''') +''',' set @sql = @sql + 'Client_Prenom, ' set @req = @req + '''' + replace(isnull(@Client_Prenom,''),'''','''''') +''',' set @sql = @sql + 'Client_Adresse, ' set @req = @req + '''' + replace(isnull(@Client_Adresse,''),'''','''''') +''',' set @sql = @sql + 'ClientTEL, ' set @req = @req + '''' + replace(isnull(@ClientTEL,''),'''','''''') +''',' set @sql = @sql + 'Dt_Naissance, ' set @req = @req + '''' + replace(isnull(@Dt_Naissance,''),'''','''''') +''',' set @sql = @sql + 'LieuClient, ' set @req = @req + '''' + replace(isnull(@LieuClient,''),'''','''''') +''',' set @sql = @sql + 'VilleByCodeClient, ' set @req = @req + '''' + replace(isnull(@VilleByCodeClient,''),'''','''''') +''',' set @sql = @sql + 'Civilite_Client, ' set @req = @req + '''' + replace(isnull(@Civilite_Client,''),'''','''''') +''',' set @sql = @sql + 'Cilvilite_ONLY, ' set @req = @req + '''' + replace(isnull(@Cilvilite_ONLY,''),'''','''''') +''',' set @sql = @sql + 'AvanceConvenue, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@Avc_Convenue,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'AvanceConvenueEnlettre, ' set @req = @req + '''' + isnull(@Reliquat_Avc_EnLettre,'') +''',' set @sql = @sql + 'prix_metre_carreEnlettre, ' set @req = @req + '''' + isnull(@prix_metre_carreEnlettre,'') +''',' set @sql = @sql + 'ClientLight, ' set @req = @req + '''' + replace(@Client_Light,'''','''''') +''',' set @sql = @sql + 'Responsable, ' set @req = @req + '''' + replace(@Responsable,'''','''''') +''',' set @sql = @sql + 'Descriptif, ' set @req = @req + '''' + replace(isnull(@Descriptif,''),'''','''''') +''',' set @sql = @sql + 'Annexes, ' set @req = @req + '''' + isnull(@Annexes,'') +''',' set @sql = @sql + 'Reliquat_avc, ' set @req = @req + '''' + isnull(convert(nvarchar,convert(money,@Reliquat_Avc),1),'') +''',' set @sql = @sql + 'ReliquatAvcEnlettre, ' set @req = @req + '''' + isnull(@Reliquat_Avc_EnLettre,'') +''',' set @sql = @sql + 'Reliquat, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@Reliquat,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'ReliquatEnlettre, ' set @req = @req + '''' + isnull(@Reliquat_EnLettre,'') +''',' set @sql = @sql + 'EcheancesAvc, ' ----------------------------------- set @req = @req + '''' + isnull( replace(@Echeances_Avc,'''',''''''),'') +''',' set @sql = @sql + 'EncaissAvance, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@EncaissAvance,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'Reliquat_EncaissAvance, ' set @req = @req + '''' + replace(replace(replace(Convert(NVARCHAR,convert(money,isnull(@Reliquat_EncaissAvance,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'Superficie_Ter_Mez_Type, ' set @req = @req + '''' + isnull(@Sup_Ter_Mez_Type,'') +''',' set @sql = @sql + 'Superficie_Ter_Mez, ' set @req = @req + '''' + Convert(NVARCHAR,isnull(@Sup_Ter_Mez,0)) +''',' set @sql = @sql + 'Superficie_Ter_Mez_EnLettre, ' set @req = @req + '''' + isnull(@Sup_Ter_Mez_EnLettre,'') +''',' set @sql = @sql + 'Superficie_Tf_Formule, ' set @req = @req + '''' + Convert(NVARCHAR,isnull(@Sup_TF_Formule,0)) +''',' set @sql = @sql + 'Superficie_Tf_Formule_EnLettre, ' set @req = @req + '''' + isnull(@Sup_TF_Formule_EnLettre,'') +''',' set @sql=@sql + 'Penalite, ' set @req = @req + '''' + isnull(@Penalite,'') +''',' set @sql = @sql + 'Consistance, ' set @req = @req + '''' + isnull( replace(@Consistance,'''',''''''),'') +''',' set @sql = @sql + 'Projet, ' set @req = @req + '''' + isnull(@projet,'') +''',' set @sql = @sql + 'Capital, ' set @req = @req + '''' + isnull(convert(nvarchar,convert(money,@Capital),1) ,'') +''',' set @sql = @sql + 'RegCommerce, ' set @req = @req + '''' + isnull(@RegCommerce,'') +''',' set @sql = @sql + 'num_dossier, ' set @req = @req + '''' + isnull(@num_dossier,'') +''',' set @Sql = @Sql + 'SiegeSocial, ' set @req = @req + '''' + isnull(@SiegeSocial,'') +''',' set @Sql = @Sql + 'AdresseProjet, ' set @req = @req + '''' + isnull( replace(@AdresseProjet,'''',''''''),'') +''',' set @Sql = @Sql + 'Observation, ' set @req = @req + '''' + isnull(replace(@Observation,'''',''''''),'') +''',' set @Sql = @Sql + 'Signataire, ' set @req = @req + '''' + isnull(@Signataire,'') +''',' set @Sql = @Sql + 'SignataireLight, ' set @req = @req + '''' + isnull(@Sig_light,'') +''',' set @Sql = @Sql + 'denomination, ' set @req = @req + '''' + isnull(@denomination,'') +''',' set @Sql = @Sql + 'Conservation, ' set @req = @req + '''' + isnull(@conservation_fonciere,'') +''',' set @Sql = @Sql + 'Sup_Projet, ' set @req = @req + '''' + isnull(@Sup_Projet,'') +''',' set @Sql = @Sql + 'Tf_Projet, ' set @req = @req + '''' + isnull(@Tf_Projet,'') +''',' set @Sql = @Sql + 'supjardin_EnLettre, ' set @req = @req + '''' + isnull(@supjardin_EnLettre,'') +''',' set @Sql = @Sql + 'SupConstruite_EnLettre, ' set @req = @req + '''' + isnull(@SupConstruite_EnLettre,'') +''',' set @Sql = @Sql + 'Tribunal, ' set @req = @req + '''' + isnull(@Tribunal,'') +''',' set @Sql = @Sql + 'Avance, ' set @req = @req + '''' + isnull(convert(nvarchar,convert(money,@Avance),1) ,'') +''',' set @Sql = @Sql + 'Avance_EnLettre, ' set @req = @req + '''' + isnull(@AVANCE_EnLettre,'') +''',' set @Sql = @Sql + 'DateImpression, ' set @req = @req + ''''+ isnull(@DateImpression,'') +''',' set @Sql = @Sql + 'EcheancesSocial, ' set @req = @req + ''''+ isnull(replace(@EcheancesSOCIAL,'''',''''''),'') +''',' ------------------------------ set @Sql = @Sql + 'Echeances_LEco, ' set @req = @req + ''''+ isnull(replace(@Echeances_LEco,'''',''''''),'') +''',' set @Sql = @Sql + 'Echeances_LEcoSAB, ' set @req = @req + ''''+ isnull(replace(@Echeances_LEcoSAB,'''',''''''),'') +''',' set @Sql = @Sql + 'Echeances_LEco4, ' set @req = @req+ ''''+ isnull(replace(@Echeances_LEco4,'''',''''''),'') +''',' set @Sql = @Sql + 'Echeances_LEco5, ' set @req = @req + ''''+ isnull(replace(@Echeances_LEco5,'''',''''''),'') +''',' set @Sql = @Sql + 'Liste_Encaissement_Engagment_Arsat_Bernoussi_T3, ' set @req = @req + ''''+ isnull(replace(@Liste_Encaissement_Engagment_Arsat_Bernoussi_T3,'''',''''''),'') +''',' set @Sql = @Sql + 'Liste_Encaissement_Contrat_Arsat_Bernoussi_T3, ' set @req = @req + ''''+ isnull(replace(@Liste_Encaissement_Contrat_Arsat_Bernoussi_T3,'''',''''''),'') +''',' set @Sql = @Sql + 'Liste_Encaissement_Engagment_magasin_Arsat_Bernoussi_T3, ' set @req = @req + ''''+ isnull(replace(@Liste_Encaissement_Engagment_magasin_Arsat_Bernoussi_T3,'''',''''''),'') +''',' set @sql = @sql + 'Num_Patente, ' set @req = @req + ''''+ isnull(@Num_Patente,'') +''',' set @sql = @sql + 'IdFisc, ' set @req = @req + ''''+ isnull(@IdFisc,'') +''',' set @sql = @sql + 'ICE, ' set @req = @req + ''''+ isnull(@ICE,'') +''',' set @sql = @sql + 'Client_CR, ' set @req = @req + ''''+isnull( replace(@Client_CR,'''',''''''),'') +''',' set @sql = @sql + 'Ech_CR, ' set @req = @req + ''''+ isnull(@Ech_CR,'') +''',' set @sql = @sql + 'Client_CR_Credit, ' set @req = @req + ''''+ isnull( replace(@Client_CR_Credit,'''',''''''),'') +''',' set @sql = @sql + 'Type_Bien, ' set @req = @req + ''''+ isnull(@Type_Bien,'') +''',' set @sql = @sql + 'logo, ' set @req = @req + '''' + isnull(replace(@logo,'''',''''''),'') +''',' set @Sql = @Sql + 'SuperficieMezzanine, ' set @req = @req + ''''+ Convert(NVARCHAR,isnull(@SupMezzanine,0)) +''',' set @sql = @sql + 'Client_Engagement, ' set @req = @req + '''' + replace(@Client_Engagement,'''','''''') +''',' set @sql = @sql + 'Echeances_Engagement_Html, ' set @req = @req + '''' + replace(@Echeances_Engagement_Html,'''','''''') +''',' set @sql = @sql + 'Echeance_Arsat_Bernoussi_T3, ' set @req = @req + '''' + replace(@Liste_Echeances_Contrat_Arsat_Bernoussi_T3,'''','''''') +''',' --set @sql = @sql + 'Liste_Echeances_AccordDesignation, ' --set @req = @req + '''' + replace(isnull(@Liste_Echeances_AccordDesignation,''),'''','''''') +''',' set @sql = @sql + 'Client_Engagement_Mandataire_HTML, ' set @req = @req + '''' + replace(@Client_Engagement_Mandataire_HTML,'''','''''') +''',' set @sql = @sql + 'Infos_Client_Contrat_Reservation, ' set @req = @req + '''' + replace(isnull(@Infos_Client_Contrat_Reservation,''),'''','''''') +''',' set @sql = @sql + 'Liste_Echeances_Contrat, ' set @req = @req + '''' + isnull(replace(@Liste_Echeances_Contrat,'''',''''''),'') +''',' set @sql = @sql + 'Liste_Echeances_Contrat_Mag, ' set @req = @req + '''' + isnull(replace(@Liste_Echeances_Contrat_Mag,'''',''''''),'') +''',' set @sql = @sql + 'Liste_Encaissements_Contrat_Mag, ' set @req = @req + '''' + isnull(replace(@Liste_Encaissements_Contrat_Mag,'''',''''''),'') +''',' set @sql = @sql + 'Liste_Encaissements_Eng_Mag, ' set @req = @req + '''' + isnull(replace(@Liste_Encaissements_Eng_Mag,'''',''''''),'') +''',' set @sql = @sql + 'Client_reservation, ' set @req = @req + '''' + isnull(replace(@Client_reservation,'''',''''''),'') +''',' set @sql = @sql + 'EncaissAv_En_Lettre, ' set @req = @req + '''' + isnull(replace(@EncaissAv_En_Lettre,'''',''''''),'') +''',' set @sql = @sql + 'Reliquat_EncaissEn_Lettre, ' set @req = @req + '''' + isnull(replace(@Reliquat_EncaissEn_Lettre,'''',''''''),'') +''',' set @sql = @sql + 'sup_En_Lettre_Minuscule, ' set @req = @req + '''' + isnull(replace(@sup_En_Lettre_Minu,'''',''''''),'') +''',' set @sql = @sql + 'PV_EnLettre_Minu, ' set @req = @req + '''' + isnull(replace(@PV_EnLettre_Minu,'''',''''''),'') +''',' set @sql = @sql + 'EncaissAvance_totale, ' set @req = @req + '''' + replace(replace(replace(Convert(nvarchar,convert(money,isnull(@EncaissAvance_totale,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'EncaissAvance_totale_En_Lettre, ' set @req = @req + '''' + isnull(replace(@EncaissAvance_totale_En_Lettre,'''',''''''),'') +''',' set @sql = @sql + 'Reliquat_EncaissAvance_N, ' set @req = @req + '''' + replace(replace(replace(Convert(nvarchar,convert(money,isnull(@Reliquat_EncaissAvance_N,0)),1),',','_'),'.',','),'_','.') +''',' set @sql = @sql + 'Reliquat_EncaissAvance_N_EnLettres, ' set @req = @req + '''' + isnull(replace(@Reliquat_EncaissAvance_N_EnLettres,'''',''''''),'') +''',' set @Sql = @Sql + 'P_Versement_Reliquat, ' set @req = @req + ''''+ isnull(replace(@P_Versement_Reliquat,'''',''''''),'') +''',' set @sql = @sql + 'NationaliteClient, ' set @req = @req + '''' + isnull(replace(@NationaliteClient,'''',''''''),'') +''',' set @sql = @sql + 'CINClient, ' set @req = @req + '''' + isnull(replace(@CINClient,'''',''''''),'') +''',' set @sql = @sql + 'TelDomicile, ' set @req = @req + '''' + isnull(replace(@TelDomicile,'''',''''''),'') +''',' set @sql = @sql + 'EmailClient, ' set @req = @req + '''' + isnull(replace(@EmailClient,'''',''''''),'') +''',' set @sql = @sql + 'Client_tableHTML, ' set @req = @req + '''' + isnull(replace(@Client_tableHTML,'''',''''''),'') +''',' set @sql = @sql + 'ClientFicheVente, ' set @req = @req + '''' + isnull(replace(@ClientFicheVente,'''',''''''),'') +''',' set @sql = @sql + 'Statut_Familial_Client, ' set @req = @req + '''' + isnull(replace(@Statut_Familial_Client,'''',''''''),'') +''',' set @Sql = @Sql + 'Infos_Notaire, ' set @req = @req + '''' + isnull(replace(@Infos_Notaire,'''',''''''),'') +''',' set @Sql = @Sql + 'SuperficieTerCouverte ' set @req = @req + '''' + Convert(NVARCHAR, isnull(@SupTerCouverte, 0)) + ''')' set @Sql = @Sql + ')' execute (@sql + @req) CLOSE Cur_Signataire DEALLOCATE Cur_Signataire CLOSE Cur_Produit DEALLOCATE Cur_Produit END