Вопрос
В разделе Планирование (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
