Back to OIM Explorer

dbo.QER_FTOrgHighestRulerContainer

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.634 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_FCVIntToString source text reference
references source dbo.QBM_FCVStringPadLeft source text reference
references source dbo.QBM_FGIConfigparmValue source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE FUNCTION dbo.QER_FTOrgHighestRulerContainer(2  @uid_org varchar(38),3  @uid_personinserted varchar(38),4  @uid_personordered varchar(38),5  @WithFallBackRoot BIT,6  @UseRulerContainerIT int7) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default,8UID_Org varchar(38) collate database_default,9UID_PWORulerOrigin varchar(38) collate database_default10)11AS12BEGIN13  DECLARE @UID_Rulerorg varchar(38)14  DECLARE @QER_ITShop_PersonInsertedNoDecide nvarchar(256) = ''15  DECLARE @QER_ITShop_PersonOrderedNoDecide nvarchar(256) = ''16  DECLARE @allPersonsOrdered QBM_YSingleGUID17  DECLARE @allPersonsInserted QBM_YSingleGUID18  IF @uid_personinserted > ' '19  BEGIN20    SELECT21      @QER_ITShop_PersonInsertedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide')22  END23  IF @QER_ITShop_PersonInsertedNoDecide = '1' AND @uid_personinserted > ' '24  BEGIN25    INSERT INTO @allPersonsInserted(UID_SingleGuid)26    SELECT27      DISTINCT me.UID_PersonAlsoMe28    FROM QER_VPersonsAreMe me29    WHERE30      me.UID_PersonOrigin = @uid_personinserted31  END32  IF @uid_personordered > ' '33  BEGIN34    SELECT35      @QER_ITShop_PersonOrderedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide')36  END37  IF @QER_ITShop_PersonOrderedNoDecide = '1' AND @uid_personordered > ' '38  BEGIN39    INSERT INTO @allPersonsOrdered(UID_SingleGuid)40    SELECT41      DISTINCT me.UID_PersonAlsoMe42    FROM QER_VPersonsAreMe me43    WHERE44      me.UID_PersonOrigin = @uid_personordered45  END46  SELECT47    TOP 1 @UID_Rulerorg = SUBSTRING(y.zusammen,48    6,49    38)50  FROM(51  SELECT52    max(dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(x.treelevel), 5, '0') + x.UID_Org) AS zusammen53  FROM(54  SELECT55    DISTINCT b.treelevel + 1 AS treelevel, CASE @UseRulerContainerIT56    WHEN 1 THEN57    b.UID_RulerContainerIT58    WHEN -1 THEN59    b.UID_Org60  ELSE b.UID_RulerContainer61  END AS UID_Org62  FROM BaseTreeCollection co63  JOIN BaseTree b64    ON co.UID_Org = @uid_org AND co.UID_ParentOrg = b.UID_Org65  JOIN PersonInBaseTree pio66    ON pio.UID_Org = CASE @UseRulerContainerIT67  WHEN 1 THEN68  b.UID_RulerContainerIT69  WHEN -1 THEN70  b.UID_Org71  ELSE b.UID_RulerContainer72  END AND pio.XOrigin > 073  WHERE74    (@QER_ITShop_PersonInsertedNoDecide <> '1' OR NOT EXISTS(75  SELECT TOP 1 176  FROM @allPersonsInserted me77  WHERE78    me.UID_SingleGuid = pio.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide <> '1' OR NOT EXISTS(79  SELECT TOP 1 180  FROM @allPersonsOrdered me81  WHERE82    me.UID_SingleGuid = pio.uid_person))83  UNION all84  SELECT85    0, r.UID_OrgAttestator AS uid_org86  FROM OrgRoot r87  JOIN BaseTree b88    ON b.UID_OrgRoot = r.UID_OrgRoot AND b.UID_Org = @uid_org89  JOIN PersonInBaseTree pio90    ON r.UID_OrgAttestator = pio.UID_Org AND pio.XOrigin > 091  WHERE92    (@QER_ITShop_PersonInsertedNoDecide <> '1' OR NOT EXISTS(93  SELECT TOP 1 194  FROM @allPersonsInserted me95  WHERE96    me.UID_SingleGuid = pio.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide <> '1' OR NOT EXISTS(97  SELECT TOP 1 198  FROM @allPersonsOrdered me99  WHERE100    me.UID_SingleGuid = pio.uid_person)) AND @WithFallBackRoot = 1) AS x) AS y101  INSERT INTO @erg(UID_Org,102  UID_Person,103  UID_PWORulerOrigin)104  SELECT105    pib.UID_Org,106    pib.UID_Person,107    pwo.UID_PersonWantsOrg108  FROM PersonInBaseTree pib109  LEFT110  OUTER111  JOIN PersonWantsOrg pwo112    ON pwo.ObjectKeyAssignment = pib.XObjectKey AND pwo.OrderState IN('Assigned',113  'OrderProlongate',114  'OrderUnsubscribe') AND pib.XOrigin > 0115  WHERE116    pib.UID_Org = @UID_Rulerorg AND pib.XOrigin > 0 AND(@QER_ITShop_PersonInsertedNoDecide <> '1' OR NOT EXISTS(117  SELECT TOP 1 1118  FROM @allPersonsInserted me119  WHERE120    me.UID_SingleGuid = pib.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide <> '1' OR NOT EXISTS(121  SELECT TOP 1 1122  FROM @allPersonsOrdered me123  WHERE124    me.UID_SingleGuid = pib.uid_person))125  RETURN126END

Open raw exported source

1create function dbo.QER_FTOrgHighestRulerContainer (@uid_org varchar(38)  , @uid_personinserted varchar(38)  , @uid_personordered varchar(38) , @WithFallBackRoot2 bit  , @UseRulerContainerIT int    ) returns @erg table (UID_Person varchar(38) collate database_default , UID_Org varchar(38) collate database_default3 , UID_PWORulerOrigin varchar(38) collate database_default ) as begin declare @UID_Rulerorg varchar(38) declare @QER_ITShop_PersonInsertedNoDecide nvarchar4(256) = '' declare @QER_ITShop_PersonOrderedNoDecide nvarchar(256) = '' declare @allPersonsOrdered QBM_YSingleGUID declare @allPersonsInserted QBM_YSingleGUID5   if @uid_personinserted > ' ' begin select @QER_ITShop_PersonInsertedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') end if6 @QER_ITShop_PersonInsertedNoDecide = '1' and @uid_personinserted > ' ' begin  insert into @allPersonsInserted(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe7 from QER_VPersonsAreMe me where me.UID_PersonOrigin = @uid_personinserted end if @uid_personordered > ' ' begin select @QER_ITShop_PersonOrderedNoDecide8 = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide') end if @QER_ITShop_PersonOrderedNoDecide = '1' and @uid_personordered > ' ' begin insert9 into @allPersonsOrdered(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me where me.UID_PersonOrigin = @uid_personordered 10end select top 1 @UID_Rulerorg = SUBSTRING(y.zusammen, 6,38) from ( select max( dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(x.treelevel),5, '0') +11 x.UID_Org) as zusammen from (select distinct b.treelevel + 1 as treelevel, case @UseRulerContainerIT when 1 then b.UID_RulerContainerIT when -1 then b.UID_Org12 else b.UID_RulerContainer end as UID_Org from BaseTreeCollection co join BaseTree b on co.UID_Org = @uid_org and co.UID_ParentOrg = b.UID_Org join PersonInBaseTree13 pio on pio.UID_Org = case @UseRulerContainerIT when 1 then b.UID_RulerContainerIT when -1 then b.UID_Org else b.UID_RulerContainer end and pio.XOrigin14 > 0 where (@QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pio.uid_person15 ) ) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pio.uid_person 16) ) union all select 0, r.UID_OrgAttestator as uid_org from OrgRoot r join BaseTree b on b.UID_OrgRoot = r.UID_OrgRoot and b.UID_Org = @uid_org join PersonInBaseTree17 pio on r.UID_OrgAttestator = pio.UID_Org and pio.XOrigin > 0 where ( @QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted18 me where me.UID_SingleGuid  = pio.uid_person ) ) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered 19me where me.UID_SingleGuid  = pio.uid_person ) ) and @WithFallBackRoot = 1 ) as x ) as y insert into @erg (UID_Org, UID_Person, UID_PWORulerOrigin) select20 pib.UID_Org, pib.UID_Person, pwo.UID_PersonWantsOrg from PersonInBaseTree pib left outer join PersonWantsOrg pwo on pwo.ObjectKeyAssignment = pib.XObjectKey21   and pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) and pib.XOrigin > 0 where pib.UID_Org = @UID_Rulerorg and pib.XOrigin > 022 and ( @QER_ITShop_PersonInsertedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pib.uid_person ) 23) and ( @QER_ITShop_PersonOrderedNoDecide <> '1' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pib.uid_person ) )24 return end 25