Back to OIM Explorer

dbo.QER_FGIPwoValidDateRangeAssign

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.525 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FTDateTimeGaps source text reference
  • references source dbo.QBM_FCVDatetimeToDatetimeEve source text reference
  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIDate source text reference
  • references source dbo.QBM_FGIDateTimeSpanOverlap source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL105 lines
1CREATE FUNCTION dbo.QER_FGIPwoValidDateRangeAssign(2  @ObjectKeyAssignment varchar(138),3  @ValidFrom datetime,4  @ValidUntil datetime5) RETURNS BIT6AS7BEGIN8  DECLARE @wantedFrom datetime9  DECLARE @wantedUntil datetime10  DECLARE @erg BIT = 111  DECLARE @heute datetime = getutcdate()12  DECLARE @ist QBM_YDateTimePair13  DECLARE @Luecke QBM_YDateTimePair14  DECLARE @GapFitting int = 015  DECLARE @GapDefinition int = 016  SELECT17    @wantedFrom = ISNULL(@ValidFrom,18    @heute)19  SELECT20    @wantedUntil = ISNULL(@ValidUntil,21    '2200-01-01')22  IF @wantedFrom < @heute23  BEGIN24    SELECT @wantedFrom = @heute25  END26  IF @wantedUntil < @wantedFrom27  BEGIN28    SELECT @erg = 029    GOTO EndLabel30  END31  IF @wantedUntil < @heute32  BEGIN33    SELECT @erg = 034    GOTO EndLabel35  END36  SELECT37    @GapFitting = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapFitting'),38    0)39  IF @GapFitting = 240  BEGIN41    GOTO endlabel42  END43  IF EXISTS(44    SELECT TOP 1 145    FROM PersonWantsOrg pwo46    WHERE47      pwo.ObjectKeyAssignment = @ObjectKeyAssignment AND pwo.OrderState IN('Assigned', 'Granted', 'New',48  'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting'))49  BEGIN50    SELECT51      @GapDefinition = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapDefinition'),52      0)53    INSERT INTO @ist(FromDate,54    ToDate)55    SELECT CASE56    WHEN isnull(pwo.ValidFrom,57    '1899-12-30') < @heute THEN58    @heute59    ELSE pwo.ValidFrom60    END,61    dbo.QBM_FCVDatetimeToDatetimeEve(isnull(pwo.ValidUntil, '2200-01-01'))62    FROM PersonWantsOrg pwo63    WHERE64      pwo.ObjectKeyAssignment = @ObjectKeyAssignment AND(pwo.OrderState IN('Assigned', 'Granted', 'New',65    'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') AND @GapDefinition = 0 OR pwo.OrderState IN('Assigned',66    'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') AND @GapDefinition = 1 OR pwo.OrderState IN('Assigned',67    'OrderProlongate', 'OrderUnsubscribe') AND @GapDefinition = 2)68    INSERT INTO @Luecke(FromDate,69    ToDate)70    SELECT71      g.GapStart,72      g.GapEnd73    FROM dbo.QBM_FTDateTimeGaps(@ist) g74    IF @ValidFrom IS NULL AND @ValidUntil IS NULL AND EXISTS(75      SELECT TOP 1 176      FROM @Luecke)77    BEGIN78      GOTO endLabel79    END80    IF @GapFitting = 181    BEGIN82      IF NOT EXISTS(83        SELECT TOP 1 184        FROM @Luecke l85        WHERE86          l.FromDate <= @wantedFrom AND @wantedUntil <= l.ToDate)87      BEGIN88        SELECT @erg = 089      END90    END91    IF @GapFitting = 092    BEGIN93      IF NOT EXISTS(94        SELECT TOP 1 195        FROM @Luecke l96        WHERE97          dbo.QBM_FGIDateTimeSpanOverlap(@wantedFrom, @wantedUntil, l.FromDate, l.ToDate, 0) = 1)98      BEGIN99        SELECT @erg = 0100      END101    END102  END103  endLabel:104  RETURN(@erg)105END
Open raw exported source
SQL ยท Raw18 lines
1   create   function dbo.QER_FGIPwoValidDateRangeAssign(@ObjectKeyAssignment varchar(138)  , @ValidFrom datetime   , @ValidUntil datetime   ) returns2 bit as begin declare @wantedFrom datetime declare @wantedUntil datetime declare @erg bit = 1 declare @heute datetime = getutcdate() declare @ist QBM_YDateTimePair3 declare @Luecke QBM_YDateTimePair  declare @GapFitting int = 0 declare @GapDefinition int = 0  select @wantedFrom = ISNULL(@ValidFrom, @heute) select 4@wantedUntil = ISNULL(@ValidUntil, '2200-01-01') if @wantedFrom < @heute begin select @wantedFrom = @heute end if @wantedUntil < @wantedFrom begin select5 @erg = 0 goto EndLabel end if @wantedUntil < @heute begin select @erg = 0 goto EndLabel end  select @GapFitting = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue6('QER\ITShop\GapBehavior\GapFitting'), 0) if @GapFitting = 2  begin goto endlabel end if exists (select top 1 1 from PersonWantsOrg pwo where pwo.ObjectKeyAssignment7 = @ObjectKeyAssignment      and pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) begin8  select @GapDefinition = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapDefinition'), 0) insert into @ist (FromDate , ToDate9) select case when isnull(pwo.ValidFrom, '1899-12-30') < @heute then @heute else pwo.ValidFrom end , dbo.QBM_FCVDatetimeToDatetimeEve(isnull(pwo.ValidUntil10, '2200-01-01')) from PersonWantsOrg pwo where pwo.ObjectKeyAssignment = @ObjectKeyAssignment        and (pwo.OrderState in ('Assigned', 'Granted', 'New'11, 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 0  or pwo.OrderState in ('Assigned', 'Granted', 'OrderProlongate'12, 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 1  or pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) and @GapDefinition =13 2  )  insert into @Luecke(FromDate , ToDate ) select g.GapStart, g.GapEnd from dbo.QBM_FTDateTimeGaps(@ist) g  if @ValidFrom is null and @ValidUntil is14 null and exists (select top 1 1 from @Luecke ) begin goto endLabel end if @GapFitting = 1  begin if not exists (select top 1 1 from @Luecke l where l.FromDate15  <= @wantedFrom and @wantedUntil <= l.ToDate  ) begin select @erg = 0 end end if @GapFitting = 0  begin if not exists (select top 1 1 from @Luecke l where16 dbo.QBM_FGIDateTimeSpanOverlap(@wantedFrom, @wantedUntil, l.FromDate , l.ToDate , 0) = 1 ) begin select @erg = 0 end end    end endLabel: return (@erg17) end 18