Считать планирование (Forecast) на основании Счетов (Invoice), а не Продаж (Opportunities).

Вопрос

В разделе Планирование (Forecast) необходимо расчёт факта выполнять на основе счетов.

Ответ

Необходимо:

1) Внести изменения в схему ForecastBuilder. В методе openForecastPage() код:

var valuePairs = [
 {
  name: "EntitySchemaUId",
  value: "ae46fb87-c02c-4ae8-ad31-a923cdd994cf"
 },
 {
  name: "EntitySchemaName",
  value: "Opportunity"
 }
];

заменить на:

var valuePairs = [
 {
  name: "EntitySchemaUId",
  value: "bfb313dd-bb55-4e1b-8e42-3d346e0da7c5"
 },
 {
  name: "EntitySchemaName",
  value: "Invoice"
 }
];

2) Внести изменения в хранимую процедуру tsp_RecalculateForecastFact

Заменить таблицу продаж на таблицу счетов, изменить условие по стадии и условие по дате. Исходный код хранимой процедуры:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tsp_RecalculateForecastFact]
@ForecastId UNIQUEIDENTIFIER = NULL,
@CurrentUserContactId UNIQUEIDENTIFIER = NULL
AS
IF @ForecastId IS NULL
BEGIN
	RETURN;
END
DECLARE @PlanIndicatorId UNIQUEIDENTIFIER
DECLARE @FactIndicatorId UNIQUEIDENTIFIER
DECLARE @FactPercentIndicatorId UNIQUEIDENTIFIER
DECLARE @PotentialIndicatorId UNIQUEIDENTIFIER
DECLARE @CompletedId UNIQUEIDENTIFIER
DECLARE @ForecastItemId UNIQUEIDENTIFIER
DECLARE @DimensionId UNIQUEIDENTIFIER
DECLARE @DimensionValueId UNIQUEIDENTIFIER
DECLARE @PeriodId UNIQUEIDENTIFIER
DECLARE @StartDate DATE
DECLARE @DueDate DATE
DECLARE @MaxDueDate DATE
DECLARE @ColumnName NVARCHAR(100)
DECLARE @SQLText NVARCHAR(MAX)
DECLARE @PlanAmount DECIMAL(18,2)
DECLARE @FactAmount DECIMAL(18,2)
DECLARE @PotentialAmount DECIMAL(18,2)
DECLARE @FactPotentialAmountTable TABLE (PlanAmount DECIMAL(18,2), FactAmount DECIMAL(18,2), PotentialAmount DECIMAL(18,2))
SET @PlanIndicatorId = '{CBD311C7-6E1B-4324-BF21-192681349DDF}'
SET @FactIndicatorId = '{52CAE26F-84F6-42A0-AAEF-97790AF3B8D9}'
SET @FactPercentIndicatorId = '{E0D66FFB-A3E3-4DA9-BCB7-95D27033286E}'
SET @PotentialIndicatorId = '{A004FC7A-D63D-4E3C-9356-0AD77B2600F3}'
--SET @CompletedId = '{60D5310C-5BE6-DF11-971B-001D60E938C6}'
SET @CompletedId = '{698D39FD-52E6-DF11-971B-001D60E938C6}' --Paid PaymentStatusId from InvoicePaymentStatus
DELETE FROM ForecastItemValue 
WHERE ForecastIndicatorId IN (@FactIndicatorId, @FactPercentIndicatorId, @PotentialIndicatorId)
AND EXISTS (SELECT 1 
			FROM ForecastItem fi 
			WHERE ForecastItemValue.ForecastItemId = fi.Id
				AND fi.ForecastId = @ForecastId 
			) 
SET @MaxDueDate = (SELECT Convert(Date, MAX(StartDate), 104) FROM Invoice o WHERE o.PaymentStatusId = @CompletedId)
DECLARE Cur CURSOR STATIC LOCAL FOR
SELECT
fi.Id ForecastItemId,
d.Id DimensionId, 
fi.DimensionValueId DimensionValueId,
p.Id PeriodId,
p.StartDate StartDate,
p.DueDate DueDate,
d.[Path] + 'Id'
FROM ForecastItem fi
INNER JOIN Forecast f ON f.Id = fi.ForecastId
INNER JOIN ForecastDimension fd ON fd.Id = fi.ForecastDimensionId
INNER JOIN Dimension d ON d.Id = fd.DimensionId
INNER JOIN Period p ON p.PeriodTypeId = f.PeriodTypeId
WHERE f.Id = @ForecastId
AND ISNULL(d.[Path],'') <> ''
AND p.StartDate <= @MaxDueDate
 
SET NOCOUNT ON;
 
OPEN Cur
FETCH NEXT FROM Cur INTO @ForecastItemId, @DimensionId, @DimensionValueId, @PeriodId, @StartDate, @DueDate, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @DueDate = DATEADD(DAY, 1, @DueDate)
	DELETE FROM @FactPotentialAmountTable
	SET @SQLText = N'
		SELECT
		(SELECT SUM(ISNULL(fiv.[Value], 0))
		FROM [ForecastItemValue] fiv
		WHERE fiv.[ForecastItemId] = @P5
		AND fiv.[PeriodId] = @P6
		AND fiv.[ForecastIndicatorId] = @P7
		) PlanAmount,
		(SELECT SUM(ISNULL(o.[Amount], 0)) 
		FROM [Invoice] o
		WHERE o.[PaymentStatusId] = @P1
		AND o.[StartDate] >= @P2 
		AND o.[StartDate] < @P3
		AND o.' + @ColumnName + N' = @P4
		) FactAmount,
		(SELECT SUM(ISNULL(o.[Amount], 0)) 
		FROM [Invoice] o
		INNER JOIN [InvoicePaymentStatus] os ON os.[Id] = o.[PaymentStatusId]
		WHERE os.[FinalStatus] = 0
		AND o.[StartDate] >= @P2 
		AND o.[StartDate] < @P3
		AND o.' + @ColumnName + N' = @P4
		) PotentialAmount'
	INSERT INTO @FactPotentialAmountTable exec sp_executesql @SQLText,
	N'@P1 UNIQUEIDENTIFIER, @P2 DATE, @P3 DATE, @P4 UNIQUEIDENTIFIER, @P5 UNIQUEIDENTIFIER, @P6 UNIQUEIDENTIFIER, @P7 UNIQUEIDENTIFIER',
	@P1 = @CompletedId, @P2 = @StartDate, @P3 = @DueDate, @P4 = @DimensionValueId, @P5 = @ForecastItemId,
	@P6 = @PeriodId, @P7 =  @PlanIndicatorId
	SELECT @PlanAmount = PlanAmount, @FactAmount = FactAmount, @PotentialAmount = PotentialAmount 
	FROM @FactPotentialAmountTable
	IF (@FactAmount <> 0)
	BEGIN
		INSERT INTO ForecastItemValue
		(
			Id,
			CreatedOn,
			CreatedById,
			ModifiedOn,
			ModifiedById,
			ProcessListeners,
			ForecastIndicatorId,
			[Value],
			PeriodId,
			ForecastItemId
		)
		VALUES
		(
			NEWID(),
			GETUTCDATE(),
			@CurrentUserContactId,
			GETUTCDATE(),
			@CurrentUserContactId,
			0,
			@FactIndicatorId,
			@FactAmount,
			@PeriodId,
			@ForecastItemId
		)
		IF (@PlanAmount <> 0)
		BEGIN
			INSERT INTO ForecastItemValue
			(
				Id,
				CreatedOn,
				CreatedById,
				ModifiedOn,
				ModifiedById,
				ProcessListeners,
				ForecastIndicatorId,
				[Value],
				PeriodId,
				ForecastItemId
			)
			VALUES
			(
				NEWID(),
				GETUTCDATE(),
				@CurrentUserContactId,
				GETUTCDATE(),
				@CurrentUserContactId,
				0,
				@FactPercentIndicatorId,
				CAST((@FactAmount * 100 / @PlanAmount) AS DECIMAL(18, 2)),
				@PeriodId,
				@ForecastItemId
			)
		END
	END
 
	IF (@PotentialAmount <> 0)
	BEGIN
		INSERT  INTO ForecastItemValue
		(
			Id,
			CreatedOn,
			CreatedById,
			ModifiedOn,
			ModifiedById,
			ProcessListeners,
			ForecastIndicatorId,
			[Value],
			PeriodId,
			ForecastItemId
		)
		VALUES
		(
			NEWID(),
			GETUTCDATE(),
			@CurrentUserContactId,
			GETUTCDATE(),
			@CurrentUserContactId,
			0,
			@PotentialIndicatorId,
			@PotentialAmount ,
			@PeriodId,
			@ForecastItemId
		)
	END
FETCH NEXT FROM Cur INTO @ForecastItemId, @DimensionId, @DimensionValueId, @PeriodId, @StartDate, @DueDate, @ColumnName
END
CLOSE Cur
DEALLOCATE Cur

Колонки условий, по которым создаются отдельные вкладки можно задать в таблице:

SELECT * FROM Dimension

По умолчанию в ней указаны: Account, ResponsibleDepartment, Owner, LeadType