Back to OIM Explorer

dbo.QER_FGIPwoValidDateRangesExist

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 3.536 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 source dbo.QER_FGIBitPatternInheritInfo source text reference

References

Referenced By

Complete Source

1CREATE FUNCTION dbo.QER_FGIPwoValidDateRangesExist(2  @UID_PersonOrdered varchar(38),3  @UID_ITShopOrgPR varchar(38),4  @ValidFrom datetime,5  @ValidUntil datetime6) RETURNS BIT7AS8BEGIN9  DECLARE @wantedFrom datetime10  DECLARE @wantedUntil datetime11  DECLARE @erg BIT = 112  DECLARE @IsReusePossible BIT13  DECLARE @heute datetime = getutcdate()14  DECLARE @ObjectKeyAtPR varchar(138)15  DECLARE @QER_BitPatternInheritInfo int = dbo.QER_FGIBitPatternInheritInfo('|Direct|',16  0)17  DECLARE @ist QBM_YDateTimePair18  DECLARE @Luecke QBM_YDateTimePair19  DECLARE @GapFitting int = 020  DECLARE @GapDefinition int = 021  SELECT22    @wantedFrom = ISNULL(@ValidFrom,23    @heute)24  SELECT25    @wantedUntil = ISNULL(@ValidUntil,26    '2200-01-01')27  IF @wantedFrom < @heute28  BEGIN29    SELECT @wantedFrom = @heute30  END31  IF @wantedUntil < @wantedFrom32  BEGIN33    SELECT @erg = 034    GOTO EndLabel35  END36  IF @wantedUntil < @heute37  BEGIN38    SELECT @erg = 039    GOTO EndLabel40  END41  SELECT42    TOP 1 @ObjectKeyAtPR = bho.ObjectKey,43    @IsReusePossible =((sign(len(isnull(qa.ObjectKeyAssignTarget, ''))) ^1) & ba.IsReusePossible)44  FROM DialogTable t45    WITH(readpast)46  JOIN BaseTreeAssign ba47    WITH(readpast)48    ON ba.UID_DialogTableElement = t.UID_DialogTable49  JOIN BaseTreeHasObject bho50    ON bho.ObjectKey LIKE '<Key><T>' + t.TableName + '</T>%'51  LEFT52  OUTER53  JOIN QERAssign qa54    ON bho.ObjectKey = qa.XObjectKey55  WHERE56    bho.UID_Org = @UID_ITShopOrgPR AND bho.InheritInfo = @QER_BitPatternInheritInfo57  IF @IsReusePossible = 158  BEGIN59    GOTO endLabel60  END61  SELECT62    @GapFitting = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapFitting'),63    0)64  IF @GapFitting = 265  BEGIN66    GOTO endlabel67  END68  IF EXISTS(69    SELECT TOP 1 170    FROM PersonWantsOrg pwo71    JOIN BaseTreeHasObject bho72      ON pwo.UID_Org = bho.UID_Org AND bho.InheritInfo = @QER_BitPatternInheritInfo73    WHERE74      bho.ObjectKey = @ObjectKeyAtPR AND pwo.UID_PersonOrdered = @UID_PersonOrdered AND pwo.OrderState IN('Assigned',75  'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting'))76  BEGIN77    SELECT78      @GapDefinition = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapDefinition'),79      0)80    INSERT INTO @ist(FromDate,81    ToDate)82    SELECT CASE83    WHEN isnull(pwo.ValidFrom,84    '1899-12-30') < @heute THEN85    @heute86    ELSE pwo.ValidFrom87    END,88    dbo.QBM_FCVDatetimeToDatetimeEve(isnull(pwo.ValidUntil, '2200-01-01'))89    FROM PersonWantsOrg pwo90    JOIN BaseTreeHasObject bho91      ON pwo.UID_Org = bho.UID_Org AND bho.InheritInfo = @QER_BitPatternInheritInfo92    WHERE93      bho.ObjectKey = @ObjectKeyAtPR AND pwo.UID_PersonOrdered = @UID_PersonOrdered AND(pwo.OrderState IN('Assigned',94    'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') AND @GapDefinition = 0 OR pwo.OrderState IN('Assigned',95    'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') AND @GapDefinition = 1 OR pwo.OrderState IN('Assigned',96    'OrderProlongate', 'OrderUnsubscribe') AND @GapDefinition = 2)97    INSERT INTO @Luecke(FromDate,98    ToDate)99    SELECT100      g.GapStart,101      g.GapEnd102    FROM dbo.QBM_FTDateTimeGaps(@ist) g103    IF @ValidFrom IS NULL AND @ValidUntil IS NULL AND EXISTS(104      SELECT TOP 1 1105      FROM @Luecke)106    BEGIN107      GOTO endLabel108    END109    IF @GapFitting = 1110    BEGIN111      IF NOT EXISTS(112        SELECT TOP 1 1113        FROM @Luecke l114        WHERE115          l.FromDate <= @wantedFrom AND @wantedUntil <= l.ToDate)116      BEGIN117        SELECT @erg = 0118      END119    END120    IF @GapFitting = 0121    BEGIN122      IF NOT EXISTS(123        SELECT TOP 1 1124        FROM @Luecke l125        WHERE126          dbo.QBM_FGIDateTimeSpanOverlap(@wantedFrom, @wantedUntil, l.FromDate, l.ToDate, 0) = 1)127      BEGIN128        SELECT @erg = 0129      END130    END131  END132  endLabel:133  RETURN(@erg)134END

Open raw exported source

1     create   function dbo.QER_FGIPwoValidDateRangesExist(@UID_PersonOrdered varchar(38)  , @UID_ITShopOrgPR varchar(38)  , @ValidFrom datetime 2  , @ValidUntil datetime   ) returns bit as begin declare @wantedFrom datetime declare @wantedUntil datetime declare @erg bit = 1 declare @IsReusePossible3 bit declare @heute datetime = getutcdate() declare @ObjectKeyAtPR varchar(138) declare @QER_BitPatternInheritInfo int = dbo.QER_FGIBitPatternInheritInfo4('|Direct|', 0) declare @ist QBM_YDateTimePair declare @Luecke QBM_YDateTimePair declare @GapFitting int = 0 declare @GapDefinition int = 0  select @wantedFrom5 = ISNULL(@ValidFrom, @heute) select @wantedUntil = ISNULL(@ValidUntil, '2200-01-01')  if @wantedFrom < @heute begin select @wantedFrom = @heute end if6 @wantedUntil < @wantedFrom begin select @erg = 0 goto EndLabel end if @wantedUntil < @heute begin select @erg = 0 goto EndLabel end select top 1 @ObjectKeyAtPR7 = bho.ObjectKey , @IsReusePossible = ((sign(len(isnull(qa.ObjectKeyAssignTarget, ''))) ^1 ) & ba.IsReusePossible) from DialogTable t with (readpast) join8 BaseTreeAssign ba with (readpast) on ba.UID_DialogTableElement = t.UID_DialogTable join BaseTreeHasObject bho on bho.ObjectKey like '<Key><T>' + t.TableName9 + '</T>%'  left outer join QERAssign qa on bho.ObjectKey = qa.XObjectKey where bho.UID_Org = @UID_ITShopOrgPR and bho.InheritInfo = @QER_BitPatternInheritInfo10 if @IsReusePossible = 1 begin goto endLabel end  select @GapFitting = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapFitting'11), 0) if @GapFitting = 2  begin goto endlabel end if exists (select top 1 1 from PersonWantsOrg pwo join BaseTreeHasObject bho on pwo.UID_Org = bho.UID_Org12 and bho.InheritInfo = @QER_BitPatternInheritInfo where  bho.ObjectKey = @ObjectKeyAtPR and pwo.UID_PersonOrdered = @UID_PersonOrdered      and pwo.OrderState13 in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) begin  select @GapDefinition = dbo.QBM_FCVStringToInt14(dbo.QBM_FGIConfigparmValue('QER\ITShop\GapBehavior\GapDefinition'), 0) insert into @ist (FromDate , ToDate) select case when isnull(pwo.ValidFrom, '1899-12-30'15) < @heute then @heute else pwo.ValidFrom end , dbo.QBM_FCVDatetimeToDatetimeEve(isnull(pwo.ValidUntil, '2200-01-01')) from PersonWantsOrg pwo join BaseTreeHasObject16 bho on pwo.UID_Org = bho.UID_Org and bho.InheritInfo = @QER_BitPatternInheritInfo where  bho.ObjectKey = @ObjectKeyAtPR and pwo.UID_PersonOrdered = @UID_PersonOrdered17        and (pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 0 18 or pwo.OrderState in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and @GapDefinition = 1  or pwo.OrderState in ('Assigned'19, 'OrderProlongate', 'OrderUnsubscribe' ) and @GapDefinition = 2  )  insert into @Luecke(FromDate, ToDate) select g.GapStart, g.GapEnd from dbo.QBM_FTDateTimeGaps20(@ist) g  if @ValidFrom is null and @ValidUntil is null and exists (select top 1 1 from @Luecke ) begin goto endLabel end if @GapFitting = 1  begin if 21not exists (select top 1 1 from @Luecke l where l.FromDate <= @wantedFrom and @wantedUntil <= l.ToDate ) begin select @erg = 0 end end if @GapFitting =22 0  begin if not exists (select top 1 1 from @Luecke l where dbo.QBM_FGIDateTimeSpanOverlap(@wantedFrom, @wantedUntil, l.FromDate, l.ToDate, 0) = 1 ) begin23 select @erg = 0 end end    end endLabel: return (@erg) end 24