[dbo].[GetPlasticProviderDepositDate]
create procedure [dbo].[GetPlasticProviderDepositDate]
@inProviderId int,
@inCreatedOnUtcDate datetime,
@outTransactionDate datetime out
as
set nocount on
declare @theCurrentDate datetime
declare @theCurrentUtcDate datetime
declare @theUtcMinutesOffset int
declare @theCreatedOnUtcDate datetime
declare @theCalculateBestGuessDepositDate bit
declare @theAdjustmentDays int
declare @theMerchantCutoffTime time
declare @theMerchantIsSaturdayBusinessDay bit
declare @theMerchantIsSundayBusinessDay bit
declare @theAdjustForHolidays bit
declare @SATURDAY int
declare @SUNDAY int
select
@theCurrentUtcDate = getutcdate(),
@theCurrentDate = getdate()
if( @inProviderId is null )
begin
set @outTransactionDate = convert( datetime, convert( date, @theCurrentDate ) )
end
else
begin
select
@theUtcMinutesOffset = datediff( minute, @theCurrentUtcDate, @theCurrentDate ),
@theCreatedOnUtcDate = isnull( @inCreatedOnUtcDate, @theCurrentUtcDate ),
@theCalculateBestGuessDepositDate = PlasticProvider.CalculateBestGuessDepositDate,
@theAdjustmentDays = PlasticProvider.AdjustmentDays,
@theMerchantCutoffTime = convert( time, PlasticProvider.CutoffTime ),
@theMerchantIsSaturdayBusinessDay = PlasticProvider.IsSaturdayBusinessDay,
@theMerchantIsSundayBusinessDay = PlasticProvider.IsSundayBusinessDay,
@theAdjustForHolidays = PlasticProvider.AdjustForHolidays,
@SATURDAY = case @@datefirst
when 7 then 7
else 7 - @@datefirst
end,
@SUNDAY = 8 - @@datefirst
from PlasticProvider
where PlasticProvider.ProviderID = @inProviderId
declare @theLocalDateTime datetime = convert( datetime, dateadd( minute, @theUtcMinutesOffset, @theCreatedOnUtcDate ) )
declare @theCreationDate date = convert( date, @theLocalDateTime )
declare @theCreationTimeOfDay time = convert( time, @theLocalDateTime )
if( @theCalculateBestGuessDepositDate = 0 )
begin
set @outTransactionDate = @theCreationDate
end
else
begin
declare @theTransDateBusinessDateAdjustment int = 0;
declare @theTransDateBusinessDateDayOfTheWeek int;
set @theAdjustmentDays = case
when @theAdjustmentDays <= 0 then 0
else @theAdjustmentDays
end
if( @theMerchantCutoffTime < convert( time, '23:59:00' ) and @theCreationTimeOfDay >= @theMerchantCutoffTime )
begin
set @theTransDateBusinessDateAdjustment = 1
end
while( @theAdjustmentDays >= 0 )
begin
set @outTransactionDate = dateadd( day, @theTransDateBusinessDateAdjustment, @theCreationDate )
set @theTransDateBusinessDateDayOfTheWeek = datepart( dw, @outTransactionDate )
if( @theAdjustForHolidays = 1 and exists( select top 1 1 from Holiday where [Date] = @outTransactionDate ) )
begin
set @theAdjustmentDays = @theAdjustmentDays + 1
end
else if( @theTransDateBusinessDateDayOfTheWeek = @SATURDAY )
begin
if( @theMerchantIsSaturdayBusinessDay = 0 )
begin
set @theAdjustmentDays = @theAdjustmentDays + 1
end
end
else if(@theTransDateBusinessDateDayOfTheWeek = @SUNDAY )
begin
if( @theMerchantIsSundayBusinessDay = 0 )
begin
set @theAdjustmentDays = @theAdjustmentDays + 1
end
end
set @theAdjustmentDays = @theAdjustmentDays - 1
set @theTransDateBusinessDateAdjustment = @theTransDateBusinessDateAdjustment + 1
end
end
end
GO
GRANT EXECUTE ON [dbo].[GetPlasticProviderDepositDate] TO [MssExec]
GO