Back to OIM Explorer

dbo.ATT_FTAttNextDecisionMaker

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.230 characters

Interpretation

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

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

  • No direct source references extracted.

Complete Source

SQL87 lines
1CREATE FUNCTION dbo.ATT_FTAttNextDecisionMaker(2  @uid_AttestationCase varchar(38)3) RETURNS @erg TABLE(Ordernumber int,4UID_AttestationHelper varchar(38) collate database_default,5UID_PersonHead varchar(38) collate database_default,6UID_PWODecisionRule varchar(38) collate database_default,7LevelNumber int,8SubLevelNumber int,9UID_QERWorkingStep varchar(38) collate database_default,10RulerLevel int11)12AS13BEGIN14  DECLARE @CurrentLevel int15  DECLARE @uid_QERWorkingMethod varchar(38)16  DECLARE @lauf int = 117  DECLARE @work QER_YPWONextDecisionMaker18  SELECT TOP 1 @CurrentLevel = decisionlevel19  FROM AttestationCase20  WHERE21    uid_AttestationCase = @uid_AttestationCase22  SELECT TOP 1 @uid_QERWorkingMethod = pwo.UID_QERWorkingMethod23  FROM AttestationCase pwo24  WHERE25    pwo.uid_AttestationCase = @uid_AttestationCase;26  INSERT INTO @work(UID_QERWorkingStep,27  LevelNumber,28  positivesteps,29  Ordernumber)30  SELECT31    ws.UID_QERWorkingStep,32    LevelNumber,33    positivesteps,34    1 AS Ordernumber35  FROM QERWorkingStep ws36  WHERE37    ws.UID_QERWorkingMethod = @uid_QERWorkingMethod AND ws.levelnumber = @CurrentLevel38  WHILE @Lauf > 039  BEGIN40    INSERT INTO @work(UID_QERWorkingStep,41    LevelNumber,42    positivesteps,43    Ordernumber)44    SELECT45      DISTINCT a.UID_QERWorkingStep,46      a.LevelNumber,47      a.positivesteps,48      h.ordernumber +149    FROM QERWorkingStep a50    JOIN @work h51      ON a.levelnumber = h.levelnumber + h.positiveSteps52    WHERE53      a.UID_QERWorkingMethod = @UID_QERWorkingMethod AND h.positiveSteps <> 0 AND NOT EXISTS(54    SELECT TOP 1 155    FROM @work w56    WHERE57      w.UID_QERWorkingStep = a.UID_QERWorkingStep)58    SELECT @lauf = @@rowcount59  END60  INSERT INTO @erg(Ordernumber,61  UID_AttestationHelper,62  UID_PersonHead,63  UID_PWODecisionRule,64  LevelNumber,65  SubLevelNumber,66  UID_QERWorkingStep,67  RulerLevel)68  SELECT69    DISTINCT sNext.Ordernumber,70    hNext.UID_AttestationHelper,71    hNext.UID_PersonHead,72    hNext.UID_PWODecisionRule,73    hNext.LevelNumber,74    hNext.SubLevelNumber,75    hNext.UID_QERWorkingStep,76    hNext.RulerLevel77  FROM(78  SELECT79    UID_QERWorkingStep,80    LevelNumber,81    positivesteps,82    ordernumber83  FROM @work) AS sNext84  JOIN ATT_VAttDecisionPerson_I hNext85    ON hNext.uid_AttestationCase = @uid_AttestationCase AND sNext.levelnumber = hNext.Levelnumber AND sNext.UID_QERWorkingStep = hNext.UID_QERWorkingStep86  RETURN87END
Open raw exported source
SQL ยท Raw16 lines
1create function dbo.ATT_FTAttNextDecisionMaker (@uid_AttestationCase varchar(38)) returns @erg table ( Ordernumber int , UID_AttestationHelper varchar(382) collate database_default , UID_PersonHead varchar(38) collate database_default , UID_PWODecisionRule varchar(38) collate database_default , LevelNumber3 int , SubLevelNumber int , UID_QERWorkingStep varchar(38) collate database_default , RulerLevel int ) as begin declare @CurrentLevel int declare @uid_QERWorkingMethod4 varchar(38) declare @lauf int = 1 declare @work QER_YPWONextDecisionMaker select top 1 @CurrentLevel = decisionlevel from AttestationCase where uid_AttestationCase5 = @uid_AttestationCase  select top 1 @uid_QERWorkingMethod = pwo.UID_QERWorkingMethod from AttestationCase pwo where pwo.uid_AttestationCase = @uid_AttestationCase;6   insert into @work (UID_QERWorkingStep, LevelNumber, positivesteps, Ordernumber) select ws.UID_QERWorkingStep, LevelNumber, positivesteps, 1 as Ordernumber7 from QERWorkingStep ws where ws.UID_QERWorkingMethod = @uid_QERWorkingMethod and ws.levelnumber = @CurrentLevel    while @Lauf > 0 begin insert into @work8 (UID_QERWorkingStep, LevelNumber, positivesteps, Ordernumber) select distinct a.UID_QERWorkingStep, a.LevelNumber, a.positivesteps, h.ordernumber +1 from9 QERWorkingStep a join @work h on a.levelnumber = h.levelnumber + h.positiveSteps where a.UID_QERWorkingMethod = @UID_QERWorkingMethod and h.positiveSteps10 <> 0 and not exists (select top 1 1 from @work w where w.UID_QERWorkingStep = a.UID_QERWorkingStep ) select @lauf = @@rowcount end insert into @erg ( 11Ordernumber, UID_AttestationHelper, UID_PersonHead, UID_PWODecisionRule, LevelNumber, SubLevelNumber, UID_QERWorkingStep , RulerLevel) select distinct 12sNext.Ordernumber, hNext.UID_AttestationHelper, hNext.UID_PersonHead, hNext.UID_PWODecisionRule, hNext.LevelNumber, hNext.SubLevelNumber, hNext.UID_QERWorkingStep13 , hNext.RulerLevel from (select UID_QERWorkingStep, LevelNumber, positivesteps , ordernumber from @work ) as sNext  join ATT_VAttDecisionPerson_I hNext14 on hNext.uid_AttestationCase = @uid_AttestationCase and sNext.levelnumber = hNext.Levelnumber and sNext.UID_QERWorkingStep = hNext.UID_QERWorkingStep 15return end 16