September 24, 2021 at 4:51 pm
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
September 25, 2021 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 3, 2021 at 2:19 pm
Can you paste XML plan or and Graphical Ex. Plan?
October 13, 2021 at 9:56 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply