Back to OIM Explorer

dbo.QER_FTOrgAttestatorHighest

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.377 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_FTOrgAttestatorHighest(2  @uid_org varchar(38),3  @uid_personinserted varchar(38),4  @uid_personordered varchar(38),5  @WithFallBackRoot BIT6) RETURNS @erg TABLE(UID_Person varchar(38) collate database_default,7UID_Org varchar(38) collate database_default,8UID_PWORulerOrigin varchar(38) collate database_default9)10AS11BEGIN12  DECLARE @UID_Rulerorg varchar(38)13  DECLARE @QER_ITShop_PersonInsertedNoDecide nvarchar(256) = ''14  DECLARE @QER_ITShop_PersonOrderedNoDecide nvarchar(256) = ''15  DECLARE @allPersonsOrdered QBM_YSingleGUID16  DECLARE @allPersonsInserted QBM_YSingleGUID17  IF @uid_personinserted > ' '18  BEGIN19    SELECT20      @QER_ITShop_PersonInsertedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide')21  END22  IF @QER_ITShop_PersonInsertedNoDecide = '1' AND @uid_personinserted > ' '23  BEGIN24    INSERT INTO @allPersonsInserted(UID_SingleGuid)25    SELECT26      DISTINCT me.UID_PersonAlsoMe27    FROM QER_VPersonsAreMe me28    WHERE29      me.UID_PersonOrigin = @uid_personinserted30  END31  IF @uid_personordered > ' '32  BEGIN33    SELECT34      @QER_ITShop_PersonOrderedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide')35  END36  IF @QER_ITShop_PersonOrderedNoDecide = '1' AND @uid_personordered > ' '37  BEGIN38    INSERT INTO @allPersonsOrdered(UID_SingleGuid)39    SELECT40      DISTINCT me.UID_PersonAlsoMe41    FROM QER_VPersonsAreMe me42    WHERE43      me.UID_PersonOrigin = @uid_personordered44  END45  SELECT46    TOP 1 @UID_Rulerorg = SUBSTRING(y.zusammen,47    6,48    38)49  FROM(50  SELECT51    max(dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(x.treelevel), 5, '0') + x.UID_Org) AS zusammen52  FROM(53  SELECT54    DISTINCT b.treelevel + 1 AS treelevel, b.UID_OrgAttestator AS UID_Org55  FROM BaseTreeCollection co56  JOIN BaseTree b57    ON co.UID_Org = @uid_org AND co.UID_ParentOrg = b.UID_Org58  JOIN PersonInBaseTree pio59    ON b.UID_OrgAttestator = pio.UID_Org AND pio.XOrigin > 060  WHERE61    (@QER_ITShop_PersonInsertedNoDecide = '' OR NOT EXISTS(62  SELECT TOP 1 163  FROM @allPersonsInserted me64  WHERE65    me.UID_SingleGuid = pio.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide = '' OR NOT EXISTS(66  SELECT TOP 1 167  FROM @allPersonsOrdered me68  WHERE69    me.UID_SingleGuid = pio.uid_person))70  UNION all71  SELECT72    0, r.UID_OrgAttestator AS uid_org73  FROM OrgRoot r74  JOIN BaseTree b75    ON b.UID_OrgRoot = r.UID_OrgRoot AND b.UID_Org = @uid_org76  JOIN PersonInBaseTree pio77    ON r.UID_OrgAttestator = pio.UID_Org AND pio.XOrigin > 078  WHERE79    (@QER_ITShop_PersonInsertedNoDecide = '' OR NOT EXISTS(80  SELECT TOP 1 181  FROM @allPersonsInserted me82  WHERE83    me.UID_SingleGuid = pio.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide = '' OR NOT EXISTS(84  SELECT TOP 1 185  FROM @allPersonsOrdered me86  WHERE87    me.UID_SingleGuid = pio.uid_person)) AND @WithFallBackRoot = 1) AS x) AS y88  INSERT INTO @erg(UID_Org,89  UID_Person,90  UID_PWORulerOrigin)91  SELECT92    pib.UID_Org,93    pib.UID_Person,94    pwo.UID_PersonWantsOrg95  FROM PersonInBaseTree pib96  LEFT97  OUTER98  JOIN PersonWantsOrg pwo99    ON pwo.ObjectKeyAssignment = pib.XObjectKey AND pib.XOrigin > 0 AND pwo.OrderState IN('Assigned',100  'OrderProlongate',101  'OrderUnsubscribe')102  WHERE103    pib.UID_Org = @UID_Rulerorg AND(@QER_ITShop_PersonInsertedNoDecide = '' OR NOT EXISTS(104  SELECT TOP 1 1105  FROM @allPersonsInserted me106  WHERE107    me.UID_SingleGuid = pib.uid_person)) AND(@QER_ITShop_PersonOrderedNoDecide = '' OR NOT EXISTS(108  SELECT TOP 1 1109  FROM @allPersonsOrdered me110  WHERE111    me.UID_SingleGuid = pib.uid_person))112  RETURN113END

Open raw exported source

1create function dbo.QER_FTOrgAttestatorHighest (@uid_org varchar(38) , @uid_personinserted varchar(38)  , @uid_personordered varchar(38) , @WithFallBackRoot2 bit  ) returns @erg table (UID_Person varchar(38) collate database_default , UID_Org varchar(38) collate database_default , UID_PWORulerOrigin varchar3(38) collate database_default ) as begin  declare @UID_Rulerorg varchar(38) declare @QER_ITShop_PersonInsertedNoDecide nvarchar(256) = '' declare @QER_ITShop_PersonOrderedNoDecide4 nvarchar(256) = '' declare @allPersonsOrdered QBM_YSingleGUID declare @allPersonsInserted QBM_YSingleGUID   if @uid_personinserted > ' ' begin select 5@QER_ITShop_PersonInsertedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') end if @QER_ITShop_PersonInsertedNoDecide = '1' and6 @uid_personinserted > ' ' begin  insert into @allPersonsInserted(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me where 7me.UID_PersonOrigin = @uid_personinserted end if @uid_personordered > ' ' begin select @QER_ITShop_PersonOrderedNoDecide = dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'8) end if @QER_ITShop_PersonOrderedNoDecide = '1' and @uid_personordered > ' ' begin insert into @allPersonsOrdered(UID_SingleGuid ) select distinct me.UID_PersonAlsoMe9 from QER_VPersonsAreMe me where me.UID_PersonOrigin = @uid_personordered end select top 1 @UID_Rulerorg = SUBSTRING(y.zusammen, 6,38) from ( select max10( dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(x.treelevel),5, '0') + x.UID_Org) as zusammen from (select distinct b.treelevel + 1 as treelevel, b.UID_OrgAttestator11 as UID_Org from BaseTreeCollection co join BaseTree b on co.UID_Org = @uid_org and co.UID_ParentOrg = b.UID_Org  join PersonInBaseTree pio on b.UID_OrgAttestator12 = pio.UID_Org and pio.XOrigin > 0 where (@QER_ITShop_PersonInsertedNoDecide = '' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid13  = pio.uid_person ) ) and ( @QER_ITShop_PersonOrderedNoDecide = '' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  =14 pio.uid_person ) ) 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 = 15@uid_org join PersonInBaseTree pio on r.UID_OrgAttestator = pio.UID_Org and pio.XOrigin > 0 where ( @QER_ITShop_PersonInsertedNoDecide = '' or not exists16 (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pio.uid_person ) ) and ( @QER_ITShop_PersonOrderedNoDecide = '' or not exists (select17 top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pio.uid_person ) ) and @WithFallBackRoot = 1 ) as x ) as y insert into @erg (UID_Org, UID_Person18, UID_PWORulerOrigin) select pib.UID_Org, pib.UID_Person, pwo.UID_PersonWantsOrg from PersonInBaseTree pib left outer join PersonWantsOrg pwo on pwo.ObjectKeyAssignment19 = pib.XObjectKey and pib.XOrigin > 0   and pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) where pib.UID_Org = @UID_Rulerorg and20 ( @QER_ITShop_PersonInsertedNoDecide = '' or not exists (select top 1 1 from @allPersonsInserted me where me.UID_SingleGuid  = pib.uid_person ) ) and 21( @QER_ITShop_PersonOrderedNoDecide = '' or not exists (select top 1 1 from @allPersonsOrdered me where me.UID_SingleGuid  = pib.uid_person ) ) return 22end 23