Back to OIM Explorer

dbo.QER_FTPwoNextDecisionMaker

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.134 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

SQL85 lines
1CREATE FUNCTION dbo.QER_FTPwoNextDecisionMaker(2  @uid_personwantsorg varchar(38)3) RETURNS @erg TABLE(Ordernumber int,4UID_PWOHelperPWO 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  SELECT19    TOP 1 @UID_QERWorkingMethod = pwo.UID_QERWorkingMethod,20    @CurrentLevel = pwo.DecisionLevel21  FROM personwantsorg pwo22  WHERE23    pwo.uid_personwantsorg = @uid_personwantsorg;24  INSERT INTO @work(UID_QERWorkingStep,25  LevelNumber,26  positivesteps,27  Ordernumber)28  SELECT29    s.UID_QERWorkingStep,30    s.LevelNumber,31    s.PositiveSteps,32    1 AS Ordernumber33  FROM QERWorkingStep s34  WHERE35    s.UID_QERWorkingMethod = @UID_QERWorkingMethod AND s.LevelNumber = @CurrentLevel36  WHILE @Lauf > 037  BEGIN38    INSERT INTO @work(UID_QERWorkingStep,39    LevelNumber,40    positivesteps,41    Ordernumber)42    SELECT43      DISTINCT a.UID_QERWorkingStep,44      a.LevelNumber,45      a.positivesteps,46      h.ordernumber +147    FROM QERWorkingStep a48    JOIN @work h49      ON a.levelnumber = h.levelnumber + h.positiveSteps50    WHERE51      a.UID_QERWorkingMethod = @UID_QERWorkingMethod AND h.positiveSteps <> 0 AND NOT EXISTS(52    SELECT TOP 1 153    FROM @work w54    WHERE55      w.UID_QERWorkingStep = a.UID_QERWorkingStep)56    SELECT @lauf = @@rowcount57  END58  INSERT INTO @erg(Ordernumber,59  UID_PWOHelperPWO,60  UID_PersonHead,61  UID_PWODecisionRule,62  LevelNumber,63  SubLevelNumber,64  UID_QERWorkingStep,65  RulerLevel)66  SELECT67    DISTINCT sNext.Ordernumber,68    hNext.UID_PWOHelperPWO,69    hNext.UID_Person,70    hNext.UID_PWODecisionRule,71    hNext.LevelNumber,72    hNext.SubLevelNumber,73    hNext.UID_QERWorkingStep,74    hnext.RulerLevel75  FROM(76  SELECT77    UID_QERWorkingStep,78    LevelNumber,79    positivesteps,80    ordernumber81  FROM @work) AS sNext82  JOIN QER_VITShopDecisionPerson_IS hNext83    ON hNext.uid_personwantsorg = @uid_personwantsorg AND sNext.levelnumber = hNext.Levelnumber AND sNext.UID_QERWorkingStep = hNext.UID_QERWorkingStep84  RETURN85END
Open raw exported source
SQL ยท Raw15 lines
1create function dbo.QER_FTPwoNextDecisionMaker (@uid_personwantsorg varchar(38)) returns @erg table ( Ordernumber int , UID_PWOHelperPWO varchar(38) collate2 database_default , UID_PersonHead varchar(38) collate database_default , UID_PWODecisionRule varchar(38) collate database_default , LevelNumber int , 3SubLevelNumber 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 @UID_QERWorkingMethod = pwo.UID_QERWorkingMethod , @CurrentLevel5 = pwo.DecisionLevel from personwantsorg pwo where pwo.uid_personwantsorg = @uid_personwantsorg;    insert into @work (UID_QERWorkingStep, LevelNumber,6 positivesteps, Ordernumber) select s.UID_QERWorkingStep, s.LevelNumber, s.PositiveSteps, 1 as Ordernumber from QERWorkingStep s where s.UID_QERWorkingMethod7 = @UID_QERWorkingMethod and s.LevelNumber = @CurrentLevel while @Lauf > 0 begin insert into @work (UID_QERWorkingStep, LevelNumber, positivesteps, Ordernumber8) select distinct a.UID_QERWorkingStep, a.LevelNumber, a.positivesteps, h.ordernumber +1 from QERWorkingStep a join @work h on a.levelnumber = h.levelnumber9 + h.positiveSteps where a.UID_QERWorkingMethod = @UID_QERWorkingMethod and h.positiveSteps <> 0 and not exists (select top 1 1 from @work w where w.UID_QERWorkingStep10 = a.UID_QERWorkingStep ) select @lauf = @@rowcount end insert into @erg ( Ordernumber, UID_PWOHelperPWO, UID_PersonHead, UID_PWODecisionRule, LevelNumber11, SubLevelNumber, UID_QERWorkingStep , RulerLevel) select distinct sNext.Ordernumber, hNext.UID_PWOHelperPWO, hNext.UID_Person  , hNext.UID_PWODecisionRule12, hNext.LevelNumber, hNext.SubLevelNumber, hNext.UID_QERWorkingStep , hnext.RulerLevel from (select UID_QERWorkingStep, LevelNumber, positivesteps , ordernumber13 from @work ) as sNext  join QER_VITShopDecisionPerson_IS hNext on hNext.uid_personwantsorg = @uid_personwantsorg and sNext.levelnumber = hNext.Levelnumber14 and sNext.UID_QERWorkingStep = hNext.UID_QERWorkingStep return end 15