latency sql server

  • Hello everyone

    I have big problem of slowness on a SQL instance in azure

    the editor send me the procedure that poses the problem

    but I don't know what to do on the indexing side since it is a procedure

    Do you have a suggestion to improve the execution time?

    USE [MERCURE_GIT_DEVELOP]
    GO
    /****** Object: StoredProcedure [dbo].[SC_SearchQuotationsRefined] Script Date: 23/09/2021 09:38:23 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- TOUTES MODIFICATION DE CETTE PS DOIT ETRE REPORTÉE DANS LA PS [SC_SearchQuotationsRefinedRoot]
    ALTER PROCEDURE [dbo].[SC_SearchQuotationsRefined] (
    @Descriptionvarchar(255)
    , @QuotationIDENTbigint
    , @QuotationNumbervarchar(50)
    , @OU_IDENTint
    , @BP_IDENTbigint
    , @BPDescvarchar(255)
    , @Accountablebigint
    , @QuotationWriterbigint
    , @SaleRepresentativebigint
    , @AmountMindecimal(17,2)
    , @AmountMaxdecimal(17,2)
    , @DateMindate
    , @DateMaxdate
    , @Companybigint
    , @CompanyGroupbigint
    , @QuotationStateIDENTbigint
    , @StaffMemberIdentbigint
    , @CurrentPageint
    , @PageSizeint
    , @SortColumnint
    , @SortOrderint
    , @OnlyWinningQuotations bit/* null = ignore ### 1 = IsWinning ### 0 = not IsWinning */
    , @BPCodevarchar(50)
    , @onlyMyQuotationbit
    , @FilterCustomerNamevarchar(255)
    , @BPExternalRef nvarchar(50)
    )
    AS
    BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @MinAmount decimal(17,2) = -999999999999999.99
    DECLARE @MaxAmount decimal(17,2) = 999999999999999.99
    DECLARE @MinDate datetime = CONVERT(DATETIME, '17530101', 101)
    DECLARE @MaxDate datetime = CONVERT(DATETIME, '99991231', 101)
    /*
    @SortColumn :
    0 : No. devis (default sort column, clustered index)
    2 : Date
    3 : Amount
    4 : Creator
    5 : Client
    6 : Organisational unit
    7 : QuotationStatus
    8 : Description
    9 : Company
    10: CompanyGroup
    11: BusinessProjectCode
    12: Accountable
    13: QuotationWriter
    14: CustomerOrderNumber
    15: InvoicedAmount
    16: InvoicedPercent
    17: InvoicePending
    18: InvoiceRequest
    19: ProgressHours
    20: ProgressMaterials
    21: ProgressNote
    22: SuccessRate
    23: BPFolder
    24: SaleRepresentative
    25: QuotationDate
    26: BPDescription
    27: RealOrderDate
    @SortOrder :
    1 : ASC,
    2 : DESC (default sort order to show last inserted first)
    */
    SET @CurrentPage =ISNULL(@CurrentPage, 1)
    SET @PageSize =ISNULL(@PageSize, 10)
    SET @SortColumn =ISNULL(@SortColumn, 2)
    SET @SortOrder =ISNULL(@SortOrder, 2)
    DECLARE @ISGLOBAL bit;
    DECLARE @ISLOCAL bit;
    if(@onlyMyQuotation = 1)
    BEGIN
    SET @ISGLOBAL = 0;
    SET @ISLOCAL = 0;
    END
    ELSE
    BEGIN
    SELECT @ISGLOBAL= IsGlobalAdmin, @ISLOCAL = IsLocalAdmin from StaffMember where IDENT = @StaffMemberIdent
    END
    DEclare @IdentQuotationState table (IDENT int);
    insert into @IdentQuotationState
    select qs2.IDENT from SC_QuotationState as qs1 left join SC_QuotationState as qs2 on qs2.RootGUID = qs1.RootGUID where qs1.IDENT = @QuotationStateIDENT
    DECLARE @IdentCompanyGroup table (IDENT int);
    insert into @IdentCompanyGroup
    SELECT cg2.IDENT FROM SC_CompanyGroup as cg1 left join SC_CompanyGroup as cg2 on cg2.RootGUID = cg1.RootGUID where cg1.IDENT = @CompanyGroup;
    DECLARE @IdentQuotationStateForWinning table (IDENT int);
    insert into @IdentQuotationStateForWinning
    SELECT IDENT FROM SC_QuotationState WHERE ISNULL(IsSuccess, 0) = @OnlyWinningQuotations;
    DECLARE @IdentQuotationCollaborator table (IDENT int);
    insert into @IdentQuotationCollaborator
    select quotation_ident from [SC_QuotationCollaborator]
    where StaffMember_IDENT in
    (select ident from [CTEStaffMemberAncestors]
    where Ancestor=@StaffMemberIdent)
    DECLARE @ExistQuotationWriter table (IDENT int);
    insert into @ExistQuotationWriter
    SELECT QWrit.BusinessProject_IDENT FROM SC_QuotationWriter QWrit WHERE StaffMember_IDENT = ISNULL(@QuotationWriter, StaffMember_IDENT)
    DECLARE @RESULTS TABLE (
    orderedIndexint IDENTITY(1,1) PRIMARY KEY
    , IDENTbigintNOT NULL
    , Numbernvarchar(50)NULL
    , Vernvarchar(2)NULL
    , [Description]nvarchar(255)NULL
    , Notesnvarchar(max)NULL
    , CreationDatedatetimeNULL
    , BP_Codenvarchar(50)NULL
    , Creatornvarchar(150)NULL
    , Customer_IDENTbigintNULL
    , CustomerName nvarchar(255)NULL
    , CustomerExternalRefnvarchar(50)NULL
    , QuotationOptimaPKnvarchar(36)NULL
    , QuotationStatenvarchar(50)NULL
    , QuotationState_IDENTbigintNULL
    , Accountablenvarchar(301)NULL
    , QuotationWritersnvarchar(max)NULL
    , QuotationWriterIDsnvarchar(128)NULL
    , SalesRepresentativenvarchar(301)NULL
    , OUnvarchar(128)NULL
    , Companynvarchar(255)NULL
    , CompanyGroupnvarchar(50)NULL
    , TotalAmountdecimal(17, 2)NULL
    , [CustomerOrderNumber]nvarchar(50)NULL
    , [InvoicedAmount]decimal(17, 2)NULL
    , [InvoicedPercent]decimal(17, 2)NULL
    , [InvoicePending]decimal(17, 2)NULL
    , [InvoiceRequest]decimal(17, 2)NULL
    , [ProgressNote]nvarchar(200)NULL
    , [ProgressHours]decimal(17, 2)NULL
    , [ProgressMaterials]decimal(17, 2)NULL
    , [SaveTimeStamp]datetimenull
    , [LastPath] nvarchar(max)
    , [AzureDbzPath] nvarchar(max)
    , BPLibellenvarchar(max)
    , SuccessRatefloatNULL
    , BPFoldernvarchar(255)NULL
    , BP_Descnvarchar(max)NULL
    , QuotationStateColornvarchar(15)NULL
    , QuotationDateDatetimeNULL
    , IsActivebitNOT NULL
    , AwardDate datetimeNULL
    , RealOrderDatedatetimeNULL
    , Indicenvarchar(2) NULL
    , VersionIndicevarchar(5) NULL
    , QuoteLinkIDbigint NULL
    , CCOMarginValdecimal(17,4)NULL
    , CCOMarginPCdecimal(17,4)NULL
    , RexCCOValdecimal(17,4)NULL
    , RexCCOPCdecimal(17,4)NULL
    , LockingStaffMember_IDENTbigint NULL
    , LockingStaffMemberUserName varchar(150) NULL
    , LockDatedatetimeNULL
    ,OfflineLockStaffMember_IDENT bigintNULL
    , OfflineLockStaffMemberUserName varchar(150) NULL
    , OfflineLockDatedatetimeNULL
    , UnprotectedStaffMemberUserName varchar(150) NULL
    , UnprotectedDatedatetimeNULL
    , UnprotectedReasonvarchar(520) NULL
    , CustomerBlockedbit null
    , CustomerBlockedNotesnvarchar(max)
    , BPERPExternalRefnvarchar(50)
    , BPCRMExternalRefnvarchar(50)
    , WorkSituationStep_Stateint
    )
    IF (@OnlyWinningQuotations IS NOT NULL) --only winning quotations
    BEGIN
    INSERT INTO @RESULTS
    (IDENT
    , Number
    , Ver
    , [Description]
    , Notes
    , CreationDate
    , BP_Code
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , OU
    , Company
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , VersionIndice
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , [LockingStaffMember_IDENT]
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , CustomerBlocked
    , CustomerBlockedNotes
    , BPERPExternalRef
    , BPCRMExternalRef
    , WorkSituationStep_State
    )
    SELECT QO.IDENT
    , QO.Number
    , QO.[Version]
    , QO.[Description]
    , QO.Notes
    , QO.CreationDate
    , BP.Code as BP_Code
    , concat(SM.FirstName, ' ', SM.LastName) AS Creator
    , QO.Customer_IDENT
    , QO.CustomerName
    , QO.CustomerExternalRef
    , QO.QuotationOptimaPK
    , QS.Code as QuotationState
    , QS.IDENT as QuotationState_IDENT
    , concat(SMM.FirstName, ' ', SMM.LastName) AS Accountable
    , STUFF((SELECT ', ' + ISNULL(STM.FirstName, '') + ' ' + ISNULL(STM.LastName, '') AS [text()]
    FROM SC_QuotationWriter QW INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriters
    , STUFF((SELECT CAST(',' AS varchar(max)) + LOWER(CAST(QW.StaffMember_IDENT AS varchar(20)))
    FROM SC_QuotationWriter QW
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriterIDs
    , concat(SMMM.FirstName, ' ', SMMM.LastName) AS SalesRepresentative
    , OU.[Name] AS OU
    , CP.[Name] AS Company
    , CG.[Name] AS CompanyGroup
    , QO.TotalAmount
    , QO.[CustomerOrderNumber]
    , QO.[InvoicedAmount]
    , QO.[InvoicedPercent]
    , QO.[InvoicePending]
    , QO.[InvoiceRequest]
    , QO.[ProgressNote]
    , QO.[ProgressHours]
    , QO.[ProgressMaterials]
    , QO.SaveTimeStamp
    , QO.[LastPath]
    , QO.[AzureDbzPath]
    , BP.[Description] as BPLibelle
    , QO.SuccessRate
    , BF.[Name]
    , BP.[Description] as BP_Desc
    , QS.Color as QuotationStateColor
    , QO.QuotationDate AS QuotationDate
    , QO.IsActive
    , QO.ForecastOrderDate AS AwardDate
    , QO.RealOrderDate AS RealOrderDate
    , QO.Indice
    , concat(QO.[Version], '-', QO.[Indice]) as VersionIndice
    , QO.QuoteLinkID
    , QL.CCOMarginVal
    , QL.CCOMarginPC
    , QL.RexCCOVal
    , QL.RexCCOPC
    , QO.[LockingStaffMember_IDENT]
    , SM_Lock.LoginName
    , QO.LockDate
    , QO.OfflineLockStaffMember_IDENT
    , SM_Offline.LoginName
    , QO.OfflineLockDate
    , SM_Unprotect.LoginName
    , QO.UnprotectedDate
    , QO.UnprotectedReason
    , CP.Blocked
    , CP.BlockedNotes
    , BP.ERPExternalRef
    , BP.CRMExternalRef
    , SWSS.StepState
    FROM SC_Quotation QO
    LEFT OUTER JOIN StaffMember SM ON (SM.IDENT = QO.Creator_IDENT)
    LEFT OUTER JOIN StaffMember SMM ON (SMM.IDENT = QO.Accountable_IDENT)
    LEFT OUTER JOIN OrganisationalUnit OU ON (OU.IDENT = QO.OrganisationalUnit_IDENT)
    LEFT OUTER JOIN SC_QuotationState QS ON (QO.QuotationState_IDENT = QS.IDENT)
    LEFT OUTER JOIN SC_BusinessProject BP ON (QO.BusinessProject_IDENT = BP.IDENT)
    LEFT OUTER JOIN StaffMember SMMM ON (SMMM.IDENT = BP.SalesRepresentative_IDENT)
    LEFT OUTER JOIN SC_Company CP ON (CP.IDENT = BP.ProjectOwner_IDENT)
    LEFT OUTER JOIN SC_CompanyGroup CG ON (CG.IDENT = BP.CompanyGroup_IDENT)
    LEFT OUTER JOIN SC_BpFolder BF ON (BF.IDENT = BP.BpFolder_IDENT)
    LEFT OUTER JOIN SC_QuoteLinkQL ON (QL.Ident = QO.QuoteLinkID)
    LEFT OUTER JOIN (SELECT [QuotationIdent], [StepState], rank() over (partition by [QuotationIdent] order by [StepDate] desc) Worksituation_Date
    FROM SC_WorkSituationStep
    ) SWSS ON SWSS.QuotationIdent = QO.IDENT AND Worksituation_Date = 1
    LEFT OUTER JOIN StaffMember as SM_LockON (SM_Lock.IDENT = QO.LockingStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_OfflineON (SM_Offline.IDENT = QO.OfflineLockStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_UnprotectON (SM_Unprotect.IDENT = QO.UnprotectedStaffMember_IDENT)
    INNER JOIN CTEOrgaAncestors AS ANON AN.IDENT = ISNULL(QO.OrganisationalUnit_IDENT, AN.IDENT) AND AN.Ancestor = @OU_IDENT AND (AN.Distance = 0 OR @ISGLOBAL = 1)
    WHERE
    (isnull (@QuotationIDENT, '') = '' OR ISNULL(QO.IDENT, '') = COALESCE(@QuotationIDENT, QO.IDENT, ''))
    AND (isnull (@QuotationNumber, '') = '' OR QO.Number LIKE '%' + COALESCE(@QuotationNumber, QO.Number, '')+'%')
    AND (isnull (@Accountable, '') = '' OR ISNULL(QO.Accountable_IDENT, '') = COALESCE(@Accountable, QO.Accountable_IDENT, ''))
    AND (isnull (@BP_IDENT, '') = '' OR ISNULL(QO.BusinessProject_IDENT, '') = COALESCE(@BP_IDENT, QO.BusinessProject_IDENT, '') )
    AND (isnull (@SaleRepresentative, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@SaleRepresentative, BP.SalesRepresentative_IDENT, ''))
    AND ISNULL(QO.TotalAmount, 0) BETWEEN ISNULL(@AmountMin, @MinAmount) AND ISNULL(@AmountMax, @MaxAmount)
    AND ISNULL(CONVERT(DATE,QO.QuotationDate,103), @MinDate) BETWEEN ISNULL(CONVERT(DATE,@DateMin,103), @MinDate) AND ISNULL(CONVERT(DATE,@DateMax,103), @MaxDate)
    AND (isnull (@Company, '') = '' OR ISNULL(QO.Customer_IDENT, '') = COALESCE(@Company, QO.Customer_IDENT, '') )
    AND ((@ISGLOBAL = 1 AND (@CompanyGroup is null OR CG.IDENT in (SELECT IDENT FROM @IdentCompanyGroup)))
    OR (@ISGLOBAL = 0 AND (@CompanyGroup is null OR CG.IDENT = @CompanyGroup)))
    AND (
    (QO.QuotationState_IDENT IS NULL AND @OnlyWinningQuotations = 0)
    OR
    (QO.QuotationState_IDENT IS NOT NULL AND QO.QuotationState_IDENT IN (SELECT IDENT FROM @IdentQuotationStateForWinning)) -- TODO : ajouter filtre sur @OUIDENT. Voir PS de recherche de quotationState dans Mercuresur la pagede filtre des projets
    )
    AND (
    @ISLOCAL = 1
    OR
    @ISGLOBAL = 1
    OR
    (
    ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.Creator_IDENT, '') = COALESCE(@StaffMemberIdent, QO.Creator_IDENT, '')))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.accountable_IDENT, '') = COALESCE(@StaffMemberIdent, QO.accountable_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@StaffMemberIdent, BP.SalesRepresentative_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    )
    OR
    (
    ISNULL(QS.IsPrivate, 0)=0
    AND
    (QO.IDENT IN (select IDENT from @IdentQuotationCollaborator)
    )
    )
    )
    AND ((@QuotationWriter IS NULL)
    OR (EXISTS (SELECT 1 FROM @ExistQuotationWriter WHERE IDENT = QO.BusinessProject_IDENT ))
    )
    AND (isnull(@Description, '') = '' OR ( ISNULL(QO.[Description], '') LIKE '%'+COALESCE(@Description COLLATE French_CI_AI, QO.[Description] , '')+'%'))
    AND (isnull(@BPDesc, '') = '' OR ISNULL(BP.[Description], '') LIKE '%'+COALESCE(@BPDesc, BP.[Description] COLLATE French_CI_AI, '')+'%')
    AND (isnull(@BPCode, '') = '' OR ISNULL(BP.Code, '') LIKE COALESCE(@BPCode, BP.Code, '')+'%')
    AND (isnull(@BPExternalRef, '') = '' OR ISNULL(BP.ERPExternalRef, '') LIKE '%'+ COALESCE(@BPExternalRef, BP.ERPExternalRef, '')+'%')
    AND (isnull(@FilterCustomerName, '') = '' OR ISNULL(QO.CustomerName, '') LIKE '%'+COALESCE(@FilterCustomerName, QO.CustomerName, '')+'%')
    ORDER BY
    CASE @SortOrder --Conditional ORDER BY needs to be grouped by value type
    --ASCendingr
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 0 THEN QO.Number + ISNULL(QO.Version, ' ') + ISNULL(QO.Indice,' ') --varchar
    WHEN 4 THEN ISNULL(SM.FirstName, '') --varchar
    WHEN 5 THEN QO.CustomerName--varchar
    WHEN 6 THEN OU.Name --varchar
    WHEN 7 THEN QS.[Description] --varchar
    WHEN 8 THEN QO.[Description] --varchar
    WHEN 9 THEN CP.Name --varchar
    WHEN 10 THEN CG.Name --varchar
    WHEN 11 THEN BP.Code --varchar
    WHEN 12 THEN ISNULL(SMM.FirstName, '') --varchar
    --WHEN 13 THEN QuotationWriters
    WHEN 14 THEN QO.CustomerOrderNumber
    WHEN 21 THEN QO.ProgressNote
    WHEN 24 THEN ISNULL(SMMM.FirstName, '')
    WHEN 26 THEN BP.[Description]
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 1 THEN QO.BusinessProject_IDENT--bigint
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 22 THEN QO.SuccessRate --float
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 2 THEN QO.CreationDate --datetime
    WHEN 25 THEN QO.QuotationDate
    WHEN 27 THEN QO.RealOrderDate
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 3 THEN QO.TotalAmount --decimal(17, 2)
    WHEN 15 THEN QO.InvoicedAmount
    WHEN 16 THEN QO.InvoicedPercent
    WHEN 17 THEN QO.InvoicePending
    WHEN 18 THEN QO.InvoiceRequest
    WHEN 19 THEN QO.ProgressHours
    WHEN 20 THEN QO.ProgressMaterials
    END
    END
    ASC,
    --DESCending
    CASE @SortOrder --Conditional ORDER BY needs to be grouped by value type
    --ASCending
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 0 THEN QO.Number + ISNULL(QO.Version, ' ') + ISNULL(QO.Indice,' ') --varchar
    WHEN 4 THEN ISNULL(SM.FirstName, '') --varchar
    WHEN 5 THEN QO.CustomerName--varchar
    WHEN 6 THEN OU.Name --varchar
    WHEN 7 THEN QS.[Description] --varchar
    WHEN 8 THEN QO.[Description] --varchar
    WHEN 9 THEN CP.Name --varchar
    WHEN 10 THEN CG.Name --varchar
    WHEN 11 THEN BP.Code --varchar
    WHEN 12 THEN ISNULL(SMM.FirstName, '') --varchar
    WHEN 14 THEN QO.CustomerOrderNumber
    WHEN 21 THEN QO.ProgressNote
    WHEN 24 THEN ISNULL(SMMM.FirstName, '')
    WHEN 26 THEN BP.[Description]
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 1 THEN QO.BusinessProject_IDENT--bigint
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 22 THEN QO.SuccessRate --float
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 2 THEN QO.CreationDate--datetime
    WHEN 25 THEN QO.QuotationDate
    WHEN 27 THEN QO.RealOrderDate
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 3 THEN QO.TotalAmount --decimal(17, 2)
    WHEN 15 THEN QO.InvoicedAmount
    WHEN 16 THEN QO.InvoicedPercent
    WHEN 17 THEN QO.InvoicePending
    WHEN 18 THEN QO.InvoiceRequest
    WHEN 19 THEN QO.ProgressHours
    WHEN 20 THEN QO.ProgressMaterials
    END
    END
    DESC
    END
    ELSE
    BEGIN
    INSERT INTO @RESULTS
    (IDENT
    , Number
    , Ver
    , [Description]
    , Notes
    , CreationDate
    , BP_Code
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , OU
    , Company
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , VersionIndice
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , [LockingStaffMember_IDENT]
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , CustomerBlocked
    , CustomerBlockedNotes
    , BPERPExternalRef
    , BPCRMExternalRef
    , WorkSituationStep_State
    )
    SELECT QO.IDENT
    , QO.Number
    , QO.Version
    , QO.[Description]
    , QO.Notes
    , QO.CreationDate
    , BP.Code as BP_Code
    , ISNULL(SM.FirstName, '') + ' ' + ISNULL(SM.LastName, '') AS Creator
    , QO.Customer_IDENT
    , QO.CustomerName
    , QO.CustomerExternalRef
    , QO.QuotationOptimaPK
    , QS.Code as QuotationState
    , QS.IDENT as QuotationState_IDENT
    , ISNULL(SMM.FirstName, '') + ' ' + ISNULL(SMM.LastName, '') AS Accountable
    , STUFF((SELECT ', ' + ISNULL(STM.FirstName, '') + ' ' + ISNULL(STM.LastName, '') AS [text()]
    FROM SC_QuotationWriter QW INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriters
    , STUFF((SELECT CAST(',' AS varchar(max)) + LOWER(CAST(QW.StaffMember_IDENT AS varchar(20)))
    FROM SC_QuotationWriter QW
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriterIDs
    , ISNULL(SMMM.FirstName, '') + ' ' + ISNULL(SMMM.LastName, '') AS SalesRepresentative
    , OU.Name AS OU
    , CP.Name AS Company
    , CG.Name AS CompanyGroup
    , QO.TotalAmount
    , QO.[CustomerOrderNumber]
    , QO.[InvoicedAmount]
    , QO.[InvoicedPercent]
    , QO.[InvoicePending]
    , QO.[InvoiceRequest]
    , QO.[ProgressNote]
    , QO.[ProgressHours]
    , QO.[ProgressMaterials]
    , QO.SaveTimeStamp
    , QO.[LastPath]
    , QO.[AzureDbzPath]
    , BP.Description as BPLibelle
    , QO.SuccessRate
    , BF.[Name]
    , BP.Description as BP_Desc
    , QS.Color as QuotationStateColor
    , QO.QuotationDate AS QuotationDate
    , QO.IsActive
    , QO.ForecastOrderDate AS AwardDate
    , QO.RealOrderDate AS RealOrderDate
    , QO.Indice
    , ISNULL(QO.[Version], '')+'-'+ISNULL(QO.Indice, '') as VersionIndice
    , QO.QuoteLinkID
    , QL.CCOMarginVal
    , QL.CCOMarginPC
    , QL.RexCCOVal
    , QL.RexCCOPC
    , QO.[LockingStaffMember_IDENT]
    , SM_Lock.LoginName
    , QO.LockDate
    , QO.OfflineLockStaffMember_IDENT
    , SM_Offline.LoginName
    , QO.OfflineLockDate
    , SM_Unprotect.LoginName
    , QO.UnprotectedDate
    , QO.UnprotectedReason
    , CP.Blocked
    , CP.BlockedNotes
    , BP.ERPExternalRef
    , BP.CRMExternalRef
    , SWSS.StepState
    FROM SC_Quotation QO
    LEFT OUTER JOIN StaffMember AS SMON (SM.IDENT = QO.Creator_IDENT)
    LEFT OUTER JOIN StaffMember AS SMMON (SMM.IDENT = QO.Accountable_IDENT)
    LEFT OUTER JOIN OrganisationalUnit AS OUON (OU.IDENT = QO.OrganisationalUnit_IDENT)
    LEFT OUTER JOIN SC_QuotationState AS QSON (QO.QuotationState_IDENT = QS.IDENT)
    LEFT OUTER JOIN SC_BusinessProject AS BPON (QO.BusinessProject_IDENT = BP.IDENT)
    LEFT OUTER JOIN StaffMember AS SMMMON (SMMM.IDENT = BP.SalesRepresentative_IDENT)
    LEFT OUTER JOIN SC_Company AS CPON (CP.IDENT = BP.ProjectOwner_IDENT)
    LEFT OUTER JOIN SC_CompanyGroup AS CGON (CG.IDENT = BP.CompanyGroup_IDENT)
    LEFT OUTER JOIN SC_BpFolder AS BFON (BF.IDENT = BP.BpFolder_IDENT)
    LEFT OUTER JOIN SC_QuoteLinkAS QLON (QL.Ident = QO.QuoteLinkID)
    LEFT OUTER JOIN (SELECT [QuotationIdent], [StepState], rank() over (partition by [QuotationIdent] order by [StepDate] desc) Worksituation_Date
    FROM SC_WorkSituationStep
    ) SWSS ON SWSS.QuotationIdent = QO.IDENT AND Worksituation_Date = 1
    LEFT OUTER JOIN StaffMember as SM_LockON (SM_Lock.IDENT = QO.LockingStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_OfflineON (SM_Offline.IDENT = QO.OfflineLockStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_UnprotectON (SM_Unprotect.IDENT = QO.UnprotectedStaffMember_IDENT)
    INNER JOIN CTEOrgaAncestors AS ANON AN.IDENT = ISNULL(QO.OrganisationalUnit_IDENT, AN.IDENT) AND AN.Ancestor = @OU_IDENT AND (AN.Distance = 0 OR @ISGLOBAL = 1)
    WHERE
    (isnull (@QuotationIDENT, '') = '' OR ISNULL(QO.IDENT, '') = COALESCE(@QuotationIDENT, QO.IDENT, ''))
    AND (isnull (@QuotationNumber, '') = '' OR QO.Number LIKE '%' + COALESCE(@QuotationNumber, QO.Number, '')+'%')
    AND (isnull (@Accountable, '') = '' OR ISNULL(QO.Accountable_IDENT, '') = COALESCE(@Accountable, QO.Accountable_IDENT, ''))
    AND (isnull (@BP_IDENT, '') = '' OR ISNULL(QO.BusinessProject_IDENT, '') = COALESCE(@BP_IDENT, QO.BusinessProject_IDENT, '') )
    AND (isnull (@SaleRepresentative, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@SaleRepresentative, BP.SalesRepresentative_IDENT, ''))
    AND ISNULL(QO.TotalAmount, 0) BETWEEN ISNULL(@AmountMin, @MinAmount) AND ISNULL(@AmountMax, @MaxAmount)
    AND ISNULL(CONVERT(DATE,QO.QuotationDate,103), @MinDate) BETWEEN ISNULL(CONVERT(DATE,@DateMin,103), @MinDate) AND ISNULL(CONVERT(DATE,@DateMax,103), @MaxDate)
    AND ISNULL(QO.Customer_IDENT, '') = COALESCE(@Company, QO.Customer_IDENT, '')
    AND ((@ISGLOBAL = 1 AND (@CompanyGroup is null OR CG.IDENT in (SELECT IDENT from @IdentCompanyGroup)))
    OR (@ISGLOBAL = 0 AND (@CompanyGroup is null OR CG.IDENT = @CompanyGroup)))
    AND (@QuotationStateIDENT is null OR ISNULL(QO.QuotationState_IDENT, '') IN (select Ident from @IdentQuotationState) )
    AND (
    (
    ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.Creator_IDENT, '') = COALESCE(@StaffMemberIdent, QO.Creator_IDENT, '')))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.accountable_IDENT, '') = COALESCE(@StaffMemberIdent, QO.accountable_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@StaffMemberIdent, BP.SalesRepresentative_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    )
    OR
    (
    ISNULL(QS.IsPrivate, 0)=0
    AND
    (@ISLOCAL = 1 OR @ISGLOBAL = 1
    OR (QO.IDENT IN (selectident from @IdentQuotationCollaborator
    ))
    )
    )
    )
    AND ((@QuotationWriter IS NULL)
    OR (EXISTS (SELECT 1 FROM SC_QuotationWriter QWrit WHERE QWrit.BusinessProject_IDENT = QO.BusinessProject_IDENT AND StaffMember_IDENT = ISNULL(@QuotationWriter, StaffMember_IDENT)))
    )
    AND (isnull(@Description, '') = '' OR ( ISNULL(QO.[Description], '') LIKE '%'+COALESCE(@Description COLLATE French_CI_AI, QO.[Description] , '')+'%'))
    AND (isnull(@BPDesc, '') = '' OR ISNULL(BP.[Description], '') LIKE '%'+COALESCE(@BPDesc, BP.[Description] COLLATE French_CI_AI, '')+'%')
    AND (isnull(@BPCode, '') = '' OR ISNULL(BP.Code, '') LIKE COALESCE(@BPCode, BP.Code, '')+'%')
    AND (isnull(@BPExternalRef, '') = '' OR ISNULL(BP.ERPExternalRef, '') LIKE '%'+ COALESCE(@BPExternalRef, BP.ERPExternalRef, '')+'%')
    AND ( ISNULL(QO.CustomerName, '') LIKE '%'+COALESCE(@FilterCustomerName, QO.CustomerName, '')+'%')
    ORDER BY
    CASE @SortOrder --Conditional ORDER BY needs to be grouped by value type
    --ASCending
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 0 THEN QO.Number + ISNULL(QO.Version, ' ') + ISNULL(QO.Indice,' ') --varchar
    WHEN 4 THEN SM.FirstName --varchar
    WHEN 5 THEN QO.CustomerName--varchar
    WHEN 6 THEN OU.Name --varchar
    WHEN 7 THEN QS.[Description] --varchar
    WHEN 8 THEN QO.[Description] --varchar
    WHEN 9 THEN CP.Name --varchar
    WHEN 10 THEN CG.Name --varchar
    WHEN 11 THEN BP.Code --varchar
    WHEN 12 THEN SMM.FirstName --varchar
    --WHEN 13 THEN QuotationWriters
    WHEN 14 THEN QO.CustomerOrderNumber
    WHEN 21 THEN QO.ProgressNote
    --WHEN 23 THEN BF.Name
    WHEN 24 THEN SMMM.FirstName
    WHEN 26 THEN BP.[Description]
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 1 THEN QO.BusinessProject_IDENT--bigint
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 22 THEN QO.SuccessRate --float
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 2 THEN QO.CreationDate--datetime
    WHEN 25 THEN QO.QuotationDate
    WHEN 27 THEN QO.RealOrderDate
    END
    END
    ASC,
    CASE @SortOrder
    WHEN 1 THEN --ASC
    CASE @SortColumn WHEN 3 THEN QO.TotalAmount --decimal(17, 2)
    WHEN 15 THEN QO.InvoicedAmount
    WHEN 16 THEN QO.InvoicedPercent
    WHEN 17 THEN QO.InvoicePending
    WHEN 18 THEN QO.InvoiceRequest
    WHEN 19 THEN QO.ProgressHours
    WHEN 20 THEN QO.ProgressMaterials
    END
    END
    ASC,
    --DESCending
    CASE @SortOrder --Conditional ORDER BY needs to be grouped by value type
    --ASCending
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 0 THEN QO.Number + ISNULL(QO.Version, ' ') + ISNULL(QO.Indice,' ') --varchar
    WHEN 4 THEN SM.FirstName --varchar
    WHEN 5 THEN QO.CustomerName--varchar
    WHEN 6 THEN OU.Name --varchar
    WHEN 7 THEN QS.[Description] --varchar
    WHEN 8 THEN QO.[Description] --varchar
    WHEN 9 THEN CP.Name --varchar
    WHEN 10 THEN CG.Name --varchar
    WHEN 11 THEN BP.Code --varchar
    WHEN 12 THEN SMM.FirstName --varchar
    --WHEN 13 THEN QuotationWriters
    WHEN 14 THEN QO.CustomerOrderNumber
    WHEN 21 THEN QO.ProgressNote
    --WHEN 23 THEN BF.Name
    WHEN 24 THEN SMMM.FirstName
    WHEN 26 THEN BP.[Description]
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 1 THEN QO.BusinessProject_IDENT--bigint
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 22 THEN QO.SuccessRate --float
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 2 THEN QO.CreationDate--datetime
    WHEN 25 THEN QO.QuotationDate
    WHEN 27 THEN QO.RealOrderDate
    END
    END
    DESC,
    CASE @SortOrder
    WHEN 2 THEN --DESC
    CASE @SortColumn WHEN 3 THEN QO.TotalAmount --decimal(17, 2)
    WHEN 15 THEN QO.InvoicedAmount
    WHEN 16 THEN QO.InvoicedPercent
    WHEN 17 THEN QO.InvoicePending
    WHEN 18 THEN QO.InvoiceRequest
    WHEN 19 THEN QO.ProgressHours
    WHEN 20 THEN QO.ProgressMaterials
    END
    END
    DESC
    END
    SELECT
    -1 AS IDENT
    , NULL AS Number
    , NULL AS Ver
    , NULL AS Notes
    , NULL AS CreationDate
    , NULL As [Description]
    , NULL AS BP_Code
    , NULL AS Creator
    , NULL AS Customer_IDENT
    , NULL AS CustomerName
    , NULL AS CustomerExternalRef
    , NULL AS QuotationOptimaPK
    , NULL AS QuotationState
    , NULL AS QuotationState_IDENT
    , NULL AS Accountable
    , NULL AS QuotationWriters
    , NULL AS QuotationWriterIDs
    , NULL AS SalesRepresentative
    , NULL AS OU
    , NULL AS Company
    , NULL AS CompanyGroup
    , (SELECT SUM(TotalAmount) FROM @RESULTS) AS TotalAmount
    , NULL AS [CustomerOrderNumber]
    , NULL AS [InvoicedAmount]
    , NULL AS [InvoicedPercent]
    , NULL AS [InvoicePending]
    , NULL AS [InvoiceRequest]
    , NULL AS [ProgressNote]
    , NULL AS [ProgressHours]
    , NULL AS [ProgressMaterials]
    , NULL AS [SaveTimeStamp]
    , NULL AS [LastPath]
    , NULL AS [AzureDbzPath]
    , NULL AS BPLibelle
    , NULL AS SuccessRate
    , NULL AS BPFolder
    , NULL AS BP_Desc
    , NULL AS QuotationStateColor
    , NULL AS QuotationDate
    , 0 AS IsActive
    , NULL AS AwardDate
    , NULL AS RealOrderDate
    , NULL AS Indice
    , NULL AS VersionIndice
    , NULL AS QuoteLinkID
    , NULL AS CCOMarginVal
    , NULL AS CCOMarginPC
    , NULL AS RexCCOVal
    , NULL AS RexCCOPC
    , NULL AS [LockingStaffMember_IDENT]
    , NULL as LockingStaffMemberUserName
    , NULL as LockDate
    , NULL as OfflineLockStaffMember_IDENT
    , NULL as OfflineLockStaffMemberUserName
    , NULL as OfflineLockDate
    , NULL as UnprotectedStaffMemberUserName
    , NULL as UnprotectedDate
    , NULL as UnprotectedReason
    , NULL AS CustomerBlocked
    , NULL AS CustomerBlockedNotes
    , NULL AS BPERPExternalRef
    , NULL AS BPCRMExternalRef
    , NULL AS WorkSituationStep_State
    , (SELECT COUNT(*) FROM @RESULTS) AS NbResults
    , @CurrentPage AS CurrentPage
    , @PageSize AS PageSize
    UNION ALL
    SELECT IDENT
    , Number
    , Ver
    , Notes
    , CreationDate
    , [Description]
    , BP_Code
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , OU
    , Company
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , VersionIndice
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , [LockingStaffMember_IDENT]
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , case when CustomerBlocked = 1 then CustomerBlocked else NULL end
    , CustomerBlockedNotes
    , BPERPExternalRef
    , BPCRMExternalRef
    , WorkSituationStep_State
    , NULL AS NbResults
    , NULL AS CurrentPage
    , NULL AS PageSize
    FROM @RESULTS WHERE orderedIndex BETWEEN 1+((@CurrentPage -1) * @PageSize) AND (@CurrentPage) * @PageSize
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    END --Stored Procedure creation

    the second sp

    USE [MERCURE_GIT_DEVELOP]
    GO
    /****** Object: StoredProcedure [dbo].[SC_SearchQuotations] Script Date: 23/09/2021 09:36:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[SC_SearchQuotations] (
    @Descriptionvarchar(255)
    , @QuotationIDENTbigint
    , @QuotationNumbervarchar(50)
    , @OU_IDENTint
    , @BP_IDENTbigint
    , @BPDescvarchar(255)
    , @Accountablebigint
    , @QuotationWriterbigint
    , @SaleRepresentativebigint
    , @AmountMindecimal(17,2)
    , @AmountMaxdecimal(17,2)
    , @DateMindate
    , @DateMaxdate
    , @Companybigint
    , @CompanyGroupbigint
    , @QuotationStateIDENTbigint
    , @StaffMemberIdentbigint
    , @CurrentPageint
    , @PageSizeint
    , @SortColumnint
    , @SortOrderint
    , @OnlyWinningQuotations bit/* null = ignore ### 1 = IsWinning ### 0 = not IsWinning */
    , @BPCodevarchar(50)
    , @onlyMyQuotationbit
    , @FilterCustomerNamevarchar(255)
    , @BPExternalRef nvarchar(50)
    )
    AS
    BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @MinAmount decimal(17,2) = -999999999999999.99
    DECLARE @MaxAmount decimal(17,2) = 999999999999999.99
    DECLARE @MinDate datetime = CONVERT(DATETIME, '17530101', 101)
    DECLARE @MaxDate datetime = CONVERT(DATETIME, '99991231', 101)
    /*
    @SortColumn :
    0 : No. devis (default sort column, clustered index)
    2 : Date
    3 : Amount
    4 : Creator
    5 : Client
    6 : Organisational unit
    7 : QuotationStatus
    8 : Description
    9 : Company
    10: CompanyGroup
    11: BusinessProjectCode
    12: Accountable
    13: QuotationWriter
    14: CustomerOrderNumber
    15: InvoicedAmount
    16: InvoicedPercent
    17: InvoicePending
    18: InvoiceRequest
    19: ProgressHours
    20: ProgressMaterials
    21: ProgressNote
    22: SuccessRate
    23: BPFolder
    24: SaleRepresentative
    25: QuotationDate
    26: BPDescription
    27: RealOrderDate
    @SortOrder :
    1 : ASC,
    2 : DESC (default sort order to show last inserted first)
    */
    SET @CurrentPage =ISNULL(@CurrentPage, 1)
    SET @PageSize =ISNULL(@PageSize, 10)
    SET @SortColumn =ISNULL(@SortColumn, 2)
    SET @SortOrder =ISNULL(@SortOrder, 2)
    DECLARE @ISGLOBAL bit;-- = (SELECT IsGlobalAdmin from StaffMember where IDENT = @StaffMemberIdent)
    DECLARE @ISLOCAL bit; -- = (SELECT IsLocalAdmin from StaffMember where IDENT = @StaffMemberIdent)
    if(@onlyMyQuotation = 1)
    BEGIN
    SET @ISGLOBAL = 0;
    SET @ISLOCAL = 0;
    END
    ELSE
    BEGIN
    SELECT @ISGLOBAL= IsGlobalAdmin, @ISLOCAL = IsLocalAdmin from StaffMember where IDENT = @StaffMemberIdent
    END
    DEclare @IdentQuotationState table (IDENT int);
    insert into @IdentQuotationState
    select qs2.IDENT from SC_QuotationState as qs1 left join SC_QuotationState as qs2 on qs2.RootGUID = qs1.RootGUID where qs1.IDENT = @QuotationStateIDENT

    DECLARE @IdentCompanyGroup table (IDENT int);
    insert into @IdentCompanyGroup
    SELECT cg2.IDENT FROM SC_CompanyGroup as cg1 left join SC_CompanyGroup as cg2 on cg2.RootGUID = cg1.RootGUID where cg1.IDENT = @CompanyGroup;

    DECLARE @IdentQuotationStateForWinning table (IDENT int);
    insert into @IdentQuotationStateForWinning
    SELECT IDENT FROM SC_QuotationState WHERE ISNULL(IsSuccess, 0) = @OnlyWinningQuotations;

    DECLARE @IdentQuotationCollaborator table (IDENT int);
    insert into @IdentQuotationCollaborator
    select quotation_ident from [SC_QuotationCollaborator]
    where StaffMember_IDENT in
    (select ident from [CTEStaffMemberAncestors]
    where Ancestor=@StaffMemberIdent)

    DECLARE @ExistQuotationWriter table (IDENT int);
    insert into @ExistQuotationWriter
    SELECT QWrit.BusinessProject_IDENT FROM SC_QuotationWriter QWrit WHERE StaffMember_IDENT = ISNULL(@QuotationWriter, StaffMember_IDENT)

    DECLARE @RESULTS TABLE (
    orderedIndexint IDENTITY(1,1) PRIMARY KEY
    , IDENTbigintNOT NULL
    , Numbernvarchar(50)NULL
    , Vernvarchar(2)NULL
    , [Description]nvarchar(255)NULL
    , Notesnvarchar(max)NULL
    , CreationDatedatetimeNULL
    , BP_IDENTbigintNULL
    , BP_Codenvarchar(50)NULL
    , Creator_IDENTbigintNULL
    , Creatornvarchar(150)NULL
    , Customer_IDENTbigintNULL
    , CustomerName nvarchar(255)NULL
    , CustomerExternalRefnvarchar(50)NULL
    , CustomerAddress1 nvarchar(100)NULL
    , CustomerAddress2 nvarchar(100)NULL
    , CustomerAddress3 nvarchar(100)NULL
    , CustomerZipCode nvarchar(40)NULL
    , CustomerCitynvarchar(100)NULL
    , CustomerPhonenvarchar(40)NULL
    , CustomerPhone2nvarchar(40)NULL
    , CustomerContactTitlenvarchar(255)NULL
    , CustomerContactFirstNamenvarchar(255)NULL
    , CustomerContactLastNamenvarchar(255)NULL
    , CustomerContactEmailnvarchar(255)NULL
    , CustomerContactDepartmentnvarchar(255)NULL
    , CustomerContactExternalRef nvarchar(255)NULL
    , QuotationOptimaPKnvarchar(36)NULL
    , QuotationStatenvarchar(50)NULL
    , QuotationState_IDENTbigintNULL
    , Accountablenvarchar(301)NULL
    , Accountable_IDENTbigintNULL
    , QuotationWritersnvarchar(max)NULL
    , QuotationWriterIDsnvarchar(128)NULL
    , SalesRepresentativenvarchar(301)NULL
    , SalesRepresentative_IDENTbigintNULL
    , OUnvarchar(128)NULL
    , Company_IDENTbigintNULL
    , Companynvarchar(255)NULL
    , CompanyGroup_IDENTbigintNULL
    , CompanyGroupnvarchar(50)NULL
    , TotalAmountdecimal(17, 2)NULL
    , [CustomerOrderNumber]nvarchar(50)NULL
    , [InvoicedAmount]decimal(17, 2)NULL
    , [InvoicedPercent]decimal(17, 2)NULL
    , [InvoicePending]decimal(17, 2)NULL
    , [InvoiceRequest]decimal(17, 2)NULL
    , [ProgressNote]nvarchar(200)NULL
    , [ProgressHours]decimal(17, 2)NULL
    , [ProgressMaterials]decimal(17, 2)NULL
    , [SaveTimeStamp]datetimenull
    , [LastPath] nvarchar(max)
    , [AzureDbzPath] nvarchar(max)
    , BPLibellenvarchar(max)
    , SuccessRatefloatNULL
    , BPFoldernvarchar(255)NULL
    , BP_Descnvarchar(max)NULL
    , BPActivityAxisnvarchar(75)NULL
    , BPActivityAxis_IDENTbigintNULL
    , BPActivityAxis_TMZIDbigintNULL
    , BPWorktypeAxisnvarchar(75)NULL
    , BPWorktypeAxis_IDENTbigintNULL
    , BPWorktypeAxis_TMZIDbigintNULL
    , BPMarketTypeAxisnvarchar(75)NULL
    , BPMarketTypeAxis_IDENTbigintNULL
    , BPMarketTypeAxis_TMZIDbigintNULL
    , BPGeographicDatanvarchar(255)NULL
    , BPGeographicData_IDENTbigintNULL
    , BPReckoningTypenvarchar(255)NULL
    , BPReckoningType_IDENTbigintNULL
    , QuotationStateColornvarchar(15)NULL
    , QuotationDateDatetimeNULL
    , IsActivebitNOT NULL
    , AwardDate datetimeNULL
    , RealOrderDatedatetimeNULL
    , Indicenvarchar(2) NULL
    , IdFrombigintNULL
    , LastVersionnvarchar (2) NULL
    , VersionIndicevarchar(5) NULL
    , IsNotSendToCustomerbit not null
    , QuoteLinkIDbigint NULL
    , CCOMarginValdecimal(17,4)NULL
    , CCOMarginPCdecimal(17,4)NULL
    , RexCCOValdecimal(17,4)NULL
    , RexCCOPCdecimal(17,4)NULL
    , Contact_IDENTintNULL
    , LockingStaffMember_IDENTbigintNULL
    , LockingStaffMemberUserName varchar(150) NULL
    , LockDatedatetimeNULL
    , OfflineLockStaffMember_IDENT bigintNULL
    , OfflineLockStaffMemberUserName varchar(150) NULL
    , OfflineLockDatedatetimeNULL
    , UnprotectedStaffMember_IDENT bigintNULL
    , UnprotectedStaffMemberUserName varchar(150) NULL
    , UnprotectedDatedatetimeNULL
    , UnprotectedReasonvarchar(520) NULL
    , Incrementint NULL
    , CustomerBlockedbit null
    , CustomerBlockedNotesnvarchar(max)
    , BPERPExternalRefnvarchar(50)
    , BPCRMExternalRefnvarchar(50)
    )
    IF (@OnlyWinningQuotations IS NOT NULL) --only winning quotations
    BEGIN
    INSERT INTO @RESULTS
    (IDENT
    , Number
    , Ver
    , [Description]
    , Notes
    , CreationDate
    , BP_IDENT
    , BP_Code
    , Creator_IDENT
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , CustomerAddress1
    , CustomerAddress2
    , CustomerAddress3
    , CustomerZipCode
    , CustomerCity
    , CustomerPhone
    , CustomerPhone2
    , CustomerContactTitle
    , CustomerContactFirstName
    , CustomerContactLastName
    , CustomerContactEmail
    , CustomerContactDepartment
    , CustomerContactExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , Accountable_IDENT
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , SalesRepresentative_IDENT
    , OU
    , Company_IDENT
    , Company
    , CompanyGroup_IDENT
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , BPActivityAxis
    , BPActivityAxis_IDENT
    , BPActivityAxis_TMZID
    , BPWorktypeAxis
    , BPWorktypeAxis_IDENT
    , BPWorktypeAxis_TMZID
    , BPMarketTypeAxis
    , BPMarketTypeAxis_IDENT
    , BPMarketTypeAxis_TMZID
    , BPGeographicData
    , BPGeographicData_IDENT
    , BPReckoningType
    , BPReckoningType_IDENT
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , IdFrom
    , LastVersion
    , VersionIndice
    , IsNotSendToCustomer
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , Contact_IDENT
    , LockingStaffMember_IDENT
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMember_IDENT
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , Increment
    , CustomerBlocked
    , CustomerBlockedNotes
    , BPERPExternalRef
    ,BPCRMExternalRef
    )
    SELECT QO.IDENT
    , QO.Number
    , QO.[Version]
    , QO.[Description]
    , QO.Notes
    , QO.CreationDate
    , QO.BusinessProject_IDENT as BP_IDENT
    , BP.Code as BP_Code
    , QO.Creator_IDENT
    , concat(SM.FirstName, ' ', SM.LastName) AS Creator --ISNULL(SM.FirstName, '') + ' ' + ISNULL(SM.LastName, '') AS Creator
    , QO.Customer_IDENT
    , QO.CustomerName
    , QO.CustomerExternalRef
    , QO.CustomerAddress1
    , QO.CustomerAddress2
    , QO.CustomerAddress3
    , QO.CustomerZipCode
    , QO.CustomerCity
    , QO.CustomerPhone
    , QO.CustomerPhone2
    , QO.CustomerContactTitle
    , QO.CustomerContactFirstName
    , QO.CustomerContactLastName
    , QO.CustomerContactEmail
    , QO.CustomerContactDepartment
    , QO.CustomerContactExternalRef
    , QO.QuotationOptimaPK
    , QS.Code as QuotationState
    , QS.IDENT as QuotationState_IDENT
    , concat(SMM.FirstName, ' ', SMM.LastName) AS Accountable--, ISNULL(SMM.FirstName, '') + ' ' + ISNULL(SMM.LastName, '') AS Accountable
    , SMM.IDENT AS Accountable_IDENT
    , STUFF((SELECT ', ' + ISNULL(STM.FirstName, '') + ' ' + ISNULL(STM.LastName, '') AS [text()]
    FROM SC_QuotationWriter QW INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriters
    , STUFF((SELECT CAST(',' AS varchar(max)) + LOWER(CAST(QW.StaffMember_IDENT AS varchar(20)))
    FROM SC_QuotationWriter QW --INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriterIDs
    , concat(SMMM.FirstName, ' ', SMMM.LastName) AS SalesRepresentative--, ISNULL(SMMM.FirstName, '') + ' ' + ISNULL(SMMM.LastName, '') AS SalesRepresentative
    , BP.SalesRepresentative_IDENT AS SalesRepresentative_IDENT
    , OU.[Name] AS OU
    , CP.IDENT AS Company_IDENT
    , CP.[Name] AS Company
    , CG.IDENT AS CompanyGroup_IDENT
    , CG.[Name] AS CompanyGroup
    , QO.TotalAmount
    , QO.[CustomerOrderNumber]
    , QO.[InvoicedAmount]
    , QO.[InvoicedPercent]
    , QO.[InvoicePending]
    , QO.[InvoiceRequest]
    , QO.[ProgressNote]
    , QO.[ProgressHours]
    , QO.[ProgressMaterials]
    , QO.SaveTimeStamp
    , QO.LastPath
    , QO.[AzureDbzPath]
    , BP.[Description] as BPLibelle
    , QO.SuccessRate
    , BF.[Name]
    , BP.[Description] as BP_Desc
    , AA.[Name] as BPActivityAxis
    , AA.IDENT as BPActivityAxis_IDENT
    , AA.TamzagID as BPActivityAxis_TMZID
    , WT.[Name] as BPWorktypeAxis
    , WT.IDENT as BPWorktypeAxis_IDENT
    , WT.TamzagID as BPWorktypeAxis_TMZID
    , MT.[Name] as BPMarketTypeAxis
    , MT.IDENT as BPMarketTypeAxis_IDENT
    , MT.TamzagID as BPMarketTypeAxis_TMZID
    , GE.[Description] as BPGeographicData
    , GE.IDENT as BPGeographicData_IDENT
    , RT.[Description] as BPReckoningType
    , RT.IDENT as BPReckoningType_IDENT
    , QS.Color as QuotationStateColor
    , QO.QuotationDate AS QuotationDate
    , QO.IsActive
    , QO.ForecastOrderDate AS AwardDate --, CONVERT(VARCHAR(10),BP.Transformation_Date,103) AS AwardDate
    , QO.RealOrderDate AS RealOrderDate
    , QO.Indice
    , QO.IdFrom
    , QO.LastVersion
    , concat(QO.[Version], '-', QO.[Indice]) as VersionIndice-- ISNULL(QO.[Version], '')+'-'+ISNULL(QO.Indice, '') as VersionIndice
    , [dbo].[SC_IsQuotNumSendToCustomer] (QO.Number) as IsNotSendToCustomer
    , QO.QuoteLinkID
    , QL.CCOMarginVal
    , QL.CCOMarginPC
    , QL.RexCCOVal
    , QL.RexCCOPC
    , QO.Contact_IDENT
    , QO.LockingStaffMember_IDENT
    , SM_Lock.LoginName
    , QO.LockDate
    , QO.OfflineLockStaffMember_IDENT
    , SM_Offline.LoginName
    , QO.OfflineLockDate
    , QO.UnprotectedStaffMember_IDENT
    , SM_Unprotect.LoginName
    , QO.UnprotectedDate
    , QO.UnprotectedReason
    , QO.Increment
    , CP.Blocked
    , CP.BlockedNotes
    , BP.ERPExternalRef
    ,BP.CRMExternalRef
    FROM SC_Quotation QO
    LEFT OUTER JOIN StaffMember SM ON (SM.IDENT = QO.Creator_IDENT)
    LEFT OUTER JOIN StaffMember SMM ON (SMM.IDENT = QO.Accountable_IDENT)
    LEFT OUTER JOIN OrganisationalUnit OU ON (OU.IDENT = QO.OrganisationalUnit_IDENT)
    LEFT OUTER JOIN SC_QuotationState QS ON (QO.QuotationState_IDENT = QS.IDENT)
    LEFT OUTER JOIN SC_BusinessProject BP ON (QO.BusinessProject_IDENT = BP.IDENT)
    LEFT OUTER JOIN StaffMember SMMM ON (SMMM.IDENT = BP.SalesRepresentative_IDENT)
    LEFT OUTER JOIN SC_Company CP ON (CP.IDENT = BP.ProjectOwner_IDENT)
    LEFT OUTER JOIN SC_CompanyGroup CG ON (CG.IDENT = BP.CompanyGroup_IDENT)
    LEFT OUTER JOIN SC_BpFolder BF ON (BF.IDENT = BP.BpFolder_IDENT)
    LEFT OUTER JOIN SC_ActivityAxis AA ON (AA.IDENT = BP.ActivityAxis_IDENT)
    LEFT OUTER JOIN SC_MarketTypeAxis MT ON (MT.IDENT = BP.MarketTypeAxis_IDENT)
    LEFT OUTER JOIN SC_WorkTypeAxis WT ON (WT.IDENT = BP.WorktypeAxis_IDENT)
    LEFT OUTER JOIN SC_GeographicData GE ON (GE.IDENT = BP.GeographicData_IDENT)
    LEFT OUTER JOIN SC_ReckoningType RT ON (RT.IDENT = BP.ReckoningType_IDENT)
    LEFT OUTER JOIN SC_QuoteLinkQL ON (QL.Ident = QO.QuoteLinkID)
    LEFT OUTER JOIN StaffMember as SM_LockON (SM_Lock.IDENT = QO.LockingStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_OfflineON (SM_Offline.IDENT = QO.OfflineLockStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_UnprotectON (SM_Unprotect.IDENT = QO.UnprotectedStaffMember_IDENT)
    INNER JOIN CTEOrgaAncestors AS ANON AN.IDENT = ISNULL(QO.OrganisationalUnit_IDENT, AN.IDENT) AND AN.Ancestor = @OU_IDENT AND (AN.Distance = 0 OR @ISGLOBAL = 1)
    WHERE
    (isnull (@QuotationIDENT, '') = '' OR ISNULL(QO.IDENT, '') = COALESCE(@QuotationIDENT, QO.IDENT, ''))
    AND (isnull (@QuotationNumber, '') = '' OR QO.Number LIKE '%' + COALESCE(@QuotationNumber, QO.Number, '')+'%')
    AND (isnull (@Accountable, '') = '' OR ISNULL(QO.Accountable_IDENT, '') = COALESCE(@Accountable, QO.Accountable_IDENT, ''))
    AND (isnull (@BP_IDENT, '') = '' OR ISNULL(QO.BusinessProject_IDENT, '') = COALESCE(@BP_IDENT, QO.BusinessProject_IDENT, '') )
    AND (isnull (@SaleRepresentative, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@SaleRepresentative, BP.SalesRepresentative_IDENT, ''))
    AND ISNULL(QO.TotalAmount, 0) BETWEEN ISNULL(@AmountMin, @MinAmount) AND ISNULL(@AmountMax, @MaxAmount)
    AND ISNULL(CONVERT(DATE,QO.QuotationDate,103), @MinDate) BETWEEN ISNULL(CONVERT(DATE,@DateMin,103), @MinDate) AND ISNULL(CONVERT(DATE,@DateMax,103), @MaxDate)
    AND (isnull (@Company, '') = '' OR ISNULL(QO.Customer_IDENT, '') = COALESCE(@Company, QO.Customer_IDENT, '') )
    AND ((@ISGLOBAL = 1 AND (@CompanyGroup is null OR CG.IDENT in (SELECT IDENT FROM @IdentQuotationState)))
    OR (@ISGLOBAL = 0 AND (@CompanyGroup is null OR CG.IDENT = @CompanyGroup)))--ISNULL(CG.IDENT, '') = COALESCE(@CompanyGroup, CG.IDENT, '')))
    AND (
    (QO.QuotationState_IDENT IS NULL AND @OnlyWinningQuotations = 0)
    OR
    (QO.QuotationState_IDENT IS NOT NULL AND QO.QuotationState_IDENT IN (SELECT IDENT FROM @IdentQuotationStateForWinning)) -- TODO : ajouter filtre sur @OUIDENT. Voir PS de recherche de quotationState dans Mercuresur la pagede filtre des projets
    )
    AND (
    @ISLOCAL = 1
    OR
    @ISGLOBAL = 1
    OR
    (
    ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.Creator_IDENT, '') = COALESCE(@StaffMemberIdent, QO.Creator_IDENT, '')))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.accountable_IDENT, '') = COALESCE(@StaffMemberIdent, QO.accountable_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@StaffMemberIdent, BP.SalesRepresentative_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    )
    OR
    (
    ISNULL(QS.IsPrivate, 0)=0
    AND
    (QO.IDENT IN (select IDENT from @IdentQuotationCollaborator)
    )
    )
    )
    AND ((@QuotationWriter IS NULL)
    OR (EXISTS (SELECT 1 FROM @ExistQuotationWriter WHERE IDENT = QO.BusinessProject_IDENT ))
    )
    AND (isnull(@Description, '') = '' OR ( ISNULL(QO.[Description], '') LIKE '%'+COALESCE(@Description COLLATE French_CI_AI, QO.[Description] , '')+'%'))
    AND (isnull(@BPDesc, '') = '' OR ISNULL(BP.[Description], '') LIKE '%'+COALESCE(@BPDesc, BP.[Description] COLLATE French_CI_AI, '')+'%')
    AND (isnull(@BPCode, '') = '' OR ISNULL(BP.Code, '') LIKE COALESCE(@BPCode, BP.Code, '')+'%')
    AND (isnull(@BPExternalRef, '') = '' OR ISNULL(BP.ERPExternalRef, '') LIKE '%'+ COALESCE(@BPExternalRef, BP.ERPExternalRef, '')+'%')
    AND (isnull(@FilterCustomerName, '') = '' OR ISNULL(QO.CustomerName, '') LIKE '%'+COALESCE(@FilterCustomerName, QO.CustomerName, '')+'%')
    END
    ELSE
    BEGIN
    INSERT INTO @RESULTS
    (IDENT
    , Number
    , Ver
    , [Description]
    , Notes
    , CreationDate
    , BP_IDENT
    , BP_Code
    , Creator_IDENT
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , CustomerAddress1
    , CustomerAddress2
    , CustomerAddress3
    , CustomerZipCode
    , CustomerCity
    , CustomerPhone
    , CustomerPhone2
    , CustomerContactTitle
    , CustomerContactFirstName
    , CustomerContactLastName
    , CustomerContactEmail
    , CustomerContactDepartment
    , CustomerContactExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , Accountable_IDENT
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , SalesRepresentative_IDENT
    , OU
    , Company_IDENT
    , Company
    , CompanyGroup_IDENT
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , BPActivityAxis
    , BPActivityAxis_IDENT
    , BPActivityAxis_TMZID
    , BPWorktypeAxis
    , BPWorktypeAxis_IDENT
    , BPWorktypeAxis_TMZID
    , BPMarketTypeAxis
    , BPMarketTypeAxis_IDENT
    , BPMarketTypeAxis_TMZID
    , BPGeographicData
    , BPGeographicData_IDENT
    , BPReckoningType
    , BPReckoningType_IDENT
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , IdFrom
    , LastVersion
    , VersionIndice
    , IsNotSendToCustomer
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , Contact_IDENT
    , LockingStaffMember_IDENT
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMember_IDENT
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , Increment
    , CustomerBlocked
    , CustomerBlockedNotes
    , BPERPExternalRef
    ,BPCRMExternalRef
    )
    SELECT QO.IDENT
    , QO.Number
    , QO.Version
    , QO.[Description]
    , QO.Notes
    , QO.CreationDate
    , QO.BusinessProject_IDENT as BP_IDENT
    , BP.Code as BP_Code
    , QO.Creator_IDENT
    , ISNULL(SM.FirstName, '') + ' ' + ISNULL(SM.LastName, '') AS Creator
    , QO.Customer_IDENT
    , QO.CustomerName
    , QO.CustomerExternalRef
    , QO.CustomerAddress1
    , QO.CustomerAddress2
    , QO.CustomerAddress3
    , QO.CustomerZipCode
    , QO.CustomerCity
    , QO.CustomerPhone
    , QO.CustomerPhone2
    , QO.CustomerContactTitle
    , QO.CustomerContactFirstName
    , QO.CustomerContactLastName
    , QO.CustomerContactEmail
    , QO.CustomerContactDepartment
    , QO.CustomerContactExternalRef
    , QO.QuotationOptimaPK
    , QS.Code as QuotationState
    , QS.IDENT as QuotationState_IDENT
    , ISNULL(SMM.FirstName, '') + ' ' + ISNULL(SMM.LastName, '') AS Accountable
    , SMM.IDENT AS Accountable_IDENT
    , STUFF((SELECT ', ' + ISNULL(STM.FirstName, '') + ' ' + ISNULL(STM.LastName, '') AS [text()]
    FROM SC_QuotationWriter QW INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriters
    , STUFF((SELECT CAST(',' AS varchar(max)) + LOWER(CAST(QW.StaffMember_IDENT AS varchar(20)))
    FROM SC_QuotationWriter QW --INNER JOIN StaffMember STM ON QW.StaffMember_IDENT = STM.IDENT
    WHERE QW.BusinessProject_IDENT = BP.IDENT
    FOR XML PATH('')), 1, 1, '')
    AS QuotationWriterIDs
    , ISNULL(SMMM.FirstName, '') + ' ' + ISNULL(SMMM.LastName, '') AS SalesRepresentative
    , BP.SalesRepresentative_IDENT AS SalesRepresentative_IDENT
    , OU.Name AS OU
    , CP.IDENT AS Company_IDENT
    , CP.Name AS Company
    , CG.IDENT AS CompanyGroup_IDENT
    , CG.Name AS CompanyGroup
    , QO.TotalAmount
    , QO.[CustomerOrderNumber]
    , QO.[InvoicedAmount]
    , QO.[InvoicedPercent]
    , QO.[InvoicePending]
    , QO.[InvoiceRequest]
    , QO.[ProgressNote]
    , QO.[ProgressHours]
    , QO.[ProgressMaterials]
    , QO.SaveTimeStamp
    , QO.LastPath
    , QO.[AzureDbzPath]
    , BP.Description as BPLibelle
    , QO.SuccessRate
    , BF.Name
    , BP.Description as BP_Desc
    , AA.Name as BPActivityAxis
    , AA.IDENT as BPActivityAxis_IDENT
    , AA.TamzagID as BPActivityAxis_TMZID
    , WT.Name as BPWorktypeAxis
    , WT.IDENT as BPWorktypeAxis_IDENT
    , WT.TamzagID as BPWorktypeAxis_TMZID
    , MT.Name as BPMarketTypeAxis
    , MT.IDENT as BPMarketTypeAxis_IDENT
    , MT.TamzagID as BPMarketTypeAxis_TMZID
    , GE.Description as BPGeographicData
    , GE.IDENT as BPGeographicData_IDENT
    , RT.Description as BPReckoningType
    , RT.IDENT as BPReckoningType_IDENT
    , QS.Color as QuotationStateColor
    , QO.QuotationDate AS QuotationDate
    , QO.IsActive
    , QO.ForecastOrderDate AS AwardDate --, CONVERT(VARCHAR(10),BP.Transformation_Date,103) AS AwardDate
    , QO.RealOrderDate AS RealOrderDate
    , QO.Indice
    , QO.IdFrom
    , QO.LastVersion
    , ISNULL(QO.[Version], '')+'-'+ISNULL(QO.Indice, '') as VersionIndice
    , [dbo].[SC_IsQuotNumSendToCustomer] (QO.Number) as IsNotSendToCustomer
    , QO.QuoteLinkID
    , QL.CCOMarginVal
    , QL.CCOMarginPC
    , QL.RexCCOVal
    , QL.RexCCOPC
    , QO.Contact_IDENT
    , QO.LockingStaffMember_IDENT
    , SM_Lock.LoginName
    , QO.LockDate
    , QO.OfflineLockStaffMember_IDENT
    , SM_Offline.LoginName
    , QO.OfflineLockDate
    , QO.UnprotectedStaffMember_IDENT
    , SM_Unprotect.LoginName
    , QO.UnprotectedDate
    , QO.UnprotectedReason
    , QO.Increment
    , CP.Blocked
    , CP.BlockedNotes
    , BP.ERPExternalRef
    , BP.CRMExternalRef
    FROM SC_Quotation QO
    LEFT OUTER JOIN StaffMember AS SMON (SM.IDENT = QO.Creator_IDENT)
    LEFT OUTER JOIN StaffMember AS SMMON (SMM.IDENT = QO.Accountable_IDENT)
    LEFT OUTER JOIN OrganisationalUnit AS OUON (OU.IDENT = QO.OrganisationalUnit_IDENT)
    LEFT OUTER JOIN SC_QuotationState AS QSON (QO.QuotationState_IDENT = QS.IDENT)
    LEFT OUTER JOIN SC_BusinessProject AS BPON (QO.BusinessProject_IDENT = BP.IDENT)
    LEFT OUTER JOIN StaffMember AS SMMMON (SMMM.IDENT = BP.SalesRepresentative_IDENT)
    LEFT OUTER JOIN SC_Company AS CPON (CP.IDENT = BP.ProjectOwner_IDENT)
    LEFT OUTER JOIN SC_CompanyGroup AS CGON (CG.IDENT = BP.CompanyGroup_IDENT)
    LEFT OUTER JOIN SC_BpFolder AS BFON (BF.IDENT = BP.BpFolder_IDENT)
    LEFT OUTER JOIN SC_ActivityAxis AS AAON (AA.IDENT = BP.ActivityAxis_IDENT)
    LEFT OUTER JOIN SC_MarketTypeAxis AS MTON (MT.IDENT = BP.MarketTypeAxis_IDENT)
    LEFT OUTER JOIN SC_WorkTypeAxis AS WTON (WT.IDENT = BP.WorktypeAxis_IDENT)
    LEFT OUTER JOIN SC_GeographicData AS GEON (GE.IDENT = BP.GeographicData_IDENT)
    LEFT OUTER JOIN SC_ReckoningType AS RTON (RT.IDENT = BP.ReckoningType_IDENT)
    LEFT OUTER JOIN SC_QuoteLinkAS QLON (QL.Ident = QO.QuoteLinkID)
    LEFT OUTER JOIN StaffMember as SM_LockON (SM_Lock.IDENT = QO.LockingStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_OfflineON (SM_Offline.IDENT = QO.OfflineLockStaffMember_IDENT)
    LEFT OUTER JOIN StaffMember as SM_UnprotectON (SM_Unprotect.IDENT = QO.UnprotectedStaffMember_IDENT)
    INNER JOIN CTEOrgaAncestors AS ANON AN.IDENT = ISNULL(QO.OrganisationalUnit_IDENT, AN.IDENT) AND AN.Ancestor = @OU_IDENT AND (AN.Distance = 0 OR @ISGLOBAL = 1)
    WHERE
    (isnull (@QuotationIDENT, '') = '' OR ISNULL(QO.IDENT, '') = COALESCE(@QuotationIDENT, QO.IDENT, ''))
    AND (isnull (@QuotationNumber, '') = '' OR QO.Number LIKE '%' + COALESCE(@QuotationNumber, QO.Number, '')+'%')
    AND (isnull (@Accountable, '') = '' OR ISNULL(QO.Accountable_IDENT, '') = COALESCE(@Accountable, QO.Accountable_IDENT, ''))
    AND (isnull (@BP_IDENT, '') = '' OR ISNULL(QO.BusinessProject_IDENT, '') = COALESCE(@BP_IDENT, QO.BusinessProject_IDENT, '') )
    AND (isnull (@SaleRepresentative, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@SaleRepresentative, BP.SalesRepresentative_IDENT, ''))
    AND ISNULL(QO.TotalAmount, 0) BETWEEN ISNULL(@AmountMin, @MinAmount) AND ISNULL(@AmountMax, @MaxAmount)
    AND ISNULL(CONVERT(DATE,QO.QuotationDate,103), @MinDate) BETWEEN ISNULL(CONVERT(DATE,@DateMin,103), @MinDate) AND ISNULL(CONVERT(DATE,@DateMax,103), @MaxDate)
    AND ISNULL(QO.Customer_IDENT, '') = COALESCE(@Company, QO.Customer_IDENT, '')
    AND ((@ISGLOBAL = 1 AND (@CompanyGroup is null OR CG.IDENT in (SELECT IDENT from @IdentCompanyGroup)))
    OR (@ISGLOBAL = 0 AND (@CompanyGroup is null OR CG.IDENT = @CompanyGroup)))--ISNULL(CG.IDENT, '') = COALESCE(@CompanyGroup, CG.IDENT, '')))
    AND (@QuotationStateIDENT is null OR ISNULL(QO.QuotationState_IDENT, '') IN (select qs2.IDENT from SC_QuotationState as qs1 left join SC_QuotationState as qs2 on qs2.RootGUID = qs1.RootGUID where qs1.IDENT = @QuotationStateIDENT) ) --= COALESCE(@QuotationStateIDENT, QO.QuotationState_IDENT, '')
    AND (
    (--ISNULL(QS.IsPrivate, 0)=1 AND
    --(QO.IDENT in (select quotation_ident from [SC_QuotationCollaborator] where StaffMember_IDENT = @StaffMemberIdent))
    ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.Creator_IDENT, '') = COALESCE(@StaffMemberIdent, QO.Creator_IDENT, '')))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(QO.accountable_IDENT, '') = COALESCE(@StaffMemberIdent, QO.accountable_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    OR ((isnull (@StaffMemberIdent, '') = '' OR ISNULL(BP.SalesRepresentative_IDENT, '') = COALESCE(@StaffMemberIdent, BP.SalesRepresentative_IDENT, '')OR @ISLOCAL = 1 OR @ISGLOBAL = 1))
    )
    OR
    (
    ISNULL(QS.IsPrivate, 0)=0
    AND
    (@ISLOCAL = 1 OR @ISGLOBAL = 1
    OR (QO.IDENT IN (selectident from @IdentQuotationCollaborator
    ))
    )
    )
    )
    AND ((@QuotationWriter IS NULL)
    OR (EXISTS (SELECT 1 FROM SC_QuotationWriter QWrit WHERE QWrit.BusinessProject_IDENT = QO.BusinessProject_IDENT AND StaffMember_IDENT = ISNULL(@QuotationWriter, StaffMember_IDENT)))
    )
    AND (isnull(@Description, '') = '' OR ( ISNULL(QO.[Description], '') LIKE '%'+COALESCE(@Description COLLATE French_CI_AI, QO.[Description] , '')+'%'))
    AND (isnull(@BPDesc, '') = '' OR ISNULL(BP.[Description], '') LIKE '%'+COALESCE(@BPDesc, BP.[Description] COLLATE French_CI_AI, '')+'%')
    AND (isnull(@BPCode, '') = '' OR ISNULL(BP.Code, '') LIKE COALESCE(@BPCode, BP.Code, '')+'%')
    AND (isnull(@BPExternalRef, '') = '' OR ISNULL(BP.ERPExternalRef, '') LIKE '%'+ COALESCE(@BPExternalRef, BP.ERPExternalRef, '')+'%')
    AND ( ISNULL(QO.CustomerName, '') LIKE '%'+COALESCE(@FilterCustomerName, QO.CustomerName, '')+'%')
    END
    SELECT
    -1 AS IDENT
    , NULL AS Number
    , NULL AS Ver
    , NULL AS Notes
    , NULL AS CreationDate
    , NULL As [Description]
    , NULL AS BP_IDENT
    , NULL AS BP_Code
    , NULL AS Creator_IDENT
    , NULL AS Creator
    , NULL AS Customer_IDENT
    , NULL AS CustomerName
    , NULL AS CustomerExternalRef
    , NULL AS CustomerAddress1
    , NULL AS CustomerAddress2
    , NULL AS CustomerAddress3
    , NULL AS CustomerZipCode
    , NULL AS CustomerCity
    , NULL AS CustomerPhone
    , NULL AS CustomerPhone2
    , NULL AS CustomerContactTitle
    , NULL AS CustomerContactFirstName
    , NULL AS CustomerContactLastName
    , NULL AS CustomerContactEmail
    , NULL AS CustomerContactDepartment
    , NULL AS CustomerContactExternalRef
    , NULL AS QuotationOptimaPK
    , NULL AS QuotationState
    , NULL AS QuotationState_IDENT
    , NULL AS Accountable
    , NULL AS Accountable_IDENT
    , NULL AS QuotationWriters
    , NULL AS QuotationWriterIDs
    , NULL AS SalesRepresentative
    , NULL AS SalesRepresentative_IDENT
    , NULL AS OU
    , NULL AS Company_IDENT
    , NULL AS Company
    , NULL AS CompanyGroup_IDENT
    , NULL AS CompanyGroup
    , (SELECT SUM(TotalAmount) FROM @RESULTS) AS TotalAmount
    , NULL AS [CustomerOrderNumber]
    , NULL AS [InvoicedAmount]
    , NULL AS [InvoicedPercent]
    , NULL AS [InvoicePending]
    , NULL AS [InvoiceRequest]
    , NULL AS [ProgressNote]
    , NULL AS [ProgressHours]
    , NULL AS [ProgressMaterials]
    , NULL AS [SaveTimeStamp]
    , NULL AS [LastPath]
    , NULL AS [AzureDbzPath]
    , NULL AS BPLibelle
    , NULL AS SuccessRate
    , NULL AS BPFolder
    , NULL AS BP_Desc
    , NULL AS BPActivityAxis
    , NULL AS BPActivityAxis_IDENT
    , NULL AS BPActivityAxis_TMZID
    , NULL AS BPWorktypeAxis
    , NULL AS BPWorktypeAxis_IDENT
    , NULL AS BPWorktypeAxis_TMZID
    , NULL AS BPMarketTypeAxis
    , NULL AS BPMarketTypeAxis_IDENT
    , NULL AS BPMarketTypeAxis_TMZID
    , NULL AS BPGeographicData
    , NULL AS BPGeographicData_IDENT
    , NULL AS BPReckoningType
    , NULL AS BPReckoningType_IDENT
    , NULL AS QuotationStateColor
    , NULL AS QuotationDate
    , 0 AS IsActive
    , NULL AS AwardDate
    , NULL AS RealOrderDate
    , NULL AS Indice
    , NULL AS IdFrom
    , NULL AS LastVersion
    , NULL AS VersionIndice
    , 'false' AS IsNotSendToCustomer
    , NULL AS QuoteLinkID
    , NULL AS CCOMarginVal
    , NULL AS CCOMarginPC
    , NULL AS RexCCOVal
    , NULL AS RexCCOPC
    , NULL as Contact_IDENT
    , NULL as LockingStaffMember_IDENT
    , NULL as LockingStaffMemberUserName
    , NULL as LockDate
    , NULL as OfflineLockStaffMember_IDENT
    , NULL as OfflineLockStaffMemberUserName
    , NULL as OfflineLockDate
    , NULL as UnprotectedStaffMember_IDENT
    , NULL as UnprotectedStaffMemberUserName
    , NULL as UnprotectedDate
    , NULL as UnprotectedReason
    , NULL AS Increment
    , NULL AS CustomerBlocked
    , NULL AS CustomerBlockedNotes
    , NULL AS BPERPExternalRef
    , NULL AS BPCRMExternalRef
    , (SELECT COUNT(*) FROM @RESULTS) AS NbResults
    , @CurrentPage AS CurrentPage
    , @PageSize AS PageSize
    UNION ALL
    SELECT IDENT
    , Number
    , Ver
    , Notes
    , CreationDate
    , [Description]
    , BP_IDENT
    , BP_Code
    , Creator_IDENT
    , Creator
    , Customer_IDENT
    , CustomerName
    , CustomerExternalRef
    , CustomerAddress1
    , CustomerAddress2
    , CustomerAddress3
    , CustomerZipCode
    , CustomerCity
    , CustomerPhone
    , CustomerPhone2
    , CustomerContactTitle
    , CustomerContactFirstName
    , CustomerContactLastName
    , CustomerContactEmail
    , CustomerContactDepartment
    , CustomerContactExternalRef
    , QuotationOptimaPK
    , QuotationState
    , QuotationState_IDENT
    , Accountable
    , Accountable_IDENT
    , QuotationWriters
    , QuotationWriterIDs
    , SalesRepresentative
    , SalesRepresentative_IDENT
    , OU
    , Company_IDENT
    , Company
    , CompanyGroup_IDENT
    , CompanyGroup
    , TotalAmount
    , [CustomerOrderNumber]
    , [InvoicedAmount]
    , [InvoicedPercent]
    , [InvoicePending]
    , [InvoiceRequest]
    , [ProgressNote]
    , [ProgressHours]
    , [ProgressMaterials]
    , [SaveTimeStamp]
    , [LastPath]
    , [AzureDbzPath]
    , BPLibelle
    , SuccessRate
    , BPFolder
    , BP_Desc
    , BPActivityAxis
    , BPActivityAxis_IDENT
    , BPActivityAxis_TMZID
    , BPWorktypeAxis
    , BPWorktypeAxis_IDENT
    , BPWorktypeAxis_TMZID
    , BPMarketTypeAxis
    , BPMarketTypeAxis_IDENT
    , BPMarketTypeAxis_TMZID
    , BPGeographicData
    , BPGeographicData_IDENT
    , BPReckoningType
    , BPReckoningType_IDENT
    , QuotationStateColor
    , QuotationDate
    , IsActive
    , AwardDate
    , RealOrderDate
    , Indice
    , IdFrom
    , LastVersion
    , VersionIndice
    , IsNotSendToCustomer
    , QuoteLinkID
    , CCOMarginVal
    , CCOMarginPC
    , RexCCOVal
    , RexCCOPC
    , Contact_IDENT
    , LockingStaffMember_IDENT
    , LockingStaffMemberUserName
    , LockDate
    , OfflineLockStaffMember_IDENT
    , OfflineLockStaffMemberUserName
    , OfflineLockDate
    , UnprotectedStaffMember_IDENT
    , UnprotectedStaffMemberUserName
    , UnprotectedDate
    , UnprotectedReason
    , Increment
    , case when CustomerBlocked = 1 then CustomerBlocked else NULL end
    , CustomerBlockedNotes
    , BPERPExternalRef
    ,BPCRMExternalRef
    , NULL AS NbResults
    , NULL AS CurrentPage
    , NULL AS PageSize
    FROM @RESULTS WHERE orderedIndex BETWEEN 1+((@CurrentPage -1) * @PageSize) AND (@CurrentPage) * @PageSize
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    END --Stored Procedure creation

     

    thanks

     

     

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Can you paste XML plan or and Graphical Ex. Plan?

  • This was removed by the editor as SPAM

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply