Back to OIM Explorer

dbo.QER_FTOrgRulerHighest

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 2.518 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_FGIConfigparmValue source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL82 lines
1CREATE FUNCTION dbo.QER_FTOrgRulerHighest(2  @uid_org varchar(38),3  @uid_personinserted varchar(38),4  @uid_personordered varchar(38),5  @WithFallBackRoot BIT,6  @UseRulerContainerIT int,7  @IgnoreNoDecideForPerson BIT8) RETURNS TABLE9AS10RETURN(11SELECT12  pib.UID_Org,13  pib.UID_Person,14  pwo.UID_PersonWantsOrg AS UID_PWORulerOrigin15FROM(16SELECT TOP 1 z.UID_Org17FROM(18SELECT19  b.treelevel + 1 AS treelevel, CASE @UseRulerContainerIT20  WHEN 1 THEN21  b.UID_RulerContainerIT22  WHEN -1 THEN23  b.UID_Org24ELSE b.UID_RulerContainer25END AS UID_Org26FROM BaseTreeCollection co27JOIN BaseTree b28  ON co.UID_Org = @uid_org AND co.UID_ParentOrg = b.UID_Org29UNION all30SELECT31  0, r.UID_OrgAttestator AS uid_org32FROM OrgRoot r33JOIN BaseTree b34  ON b.UID_OrgRoot = r.UID_OrgRoot AND b.UID_Org = @uid_org35WHERE36  @WithFallBackRoot = 1) AS z37JOIN PersonInBaseTree pio38  ON z.UID_Org = pio.UID_Org AND pio.XOrigin > 039LEFT40OUTER41JOIN QER_VPersonsAreMe api42  ON pio.UID_Person = api.UID_PersonAlsoMe AND api.UID_PersonOrigin = CASE43WHEN @uid_personinserted > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') = '1' THEN44@uid_personinserted45ELSE NULL46END47LEFT48OUTER49JOIN QER_VPersonsAreMe apo50  ON pio.UID_Person = apo.UID_PersonAlsoMe AND apo.UID_PersonOrigin = CASE51WHEN @uid_personordered > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide') = '1' THEN52@uid_personordered53ELSE NULL54END55GROUP BY z.treelevel, z.UID_Org56HAVING min(isnull(api.UID_PersonAlsoMe, '')) = '' AND min(isnull(apo.UID_PersonAlsoMe, '')) = ''57ORDER BY z.treelevel DESC) AS oo58JOIN PersonInBaseTree pib59  ON oo.UID_Org = pib.UID_Org60LEFT61OUTER62JOIN PersonWantsOrg pwo63  ON pwo.ObjectKeyAssignment = pib.XObjectKey AND pib.XOrigin > 0 AND pwo.OrderState IN('Assigned', 'OrderProlongate',64'OrderUnsubscribe')65LEFT66OUTER67JOIN QER_VPersonsAreMe api68  ON pib.UID_Person = api.UID_PersonAlsoMe AND api.UID_PersonOrigin = CASE69WHEN @uid_personinserted > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') = '1' THEN70@uid_personinserted71ELSE NULL72END73LEFT74OUTER75JOIN QER_VPersonsAreMe apo76  ON pib.UID_Person = apo.UID_PersonAlsoMe AND apo.UID_PersonOrigin = CASE77WHEN @uid_personordered > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide') = '1' THEN78@uid_personordered79ELSE NULL80END81WHERE82  api.UID_PersonAlsoMe IS NULL AND apo.UID_PersonAlsoMe IS NULL)
Open raw exported source
SQL ยท Raw17 lines
1create function dbo.QER_FTOrgRulerHighest (@uid_org varchar(38)  , @uid_personinserted varchar(38)  , @uid_personordered varchar(38) , @WithFallBackRoot2 bit  , @UseRulerContainerIT int    , @IgnoreNoDecideForPerson bit  ) returns table as return ( select pib.UID_Org, pib.UID_Person, pwo.UID_PersonWantsOrg3 as UID_PWORulerOrigin from ( select top 1 z.UID_Org from (select b.treelevel + 1 as treelevel, case @UseRulerContainerIT when 1 then b.UID_RulerContainerIT4 when -1 then b.UID_Org else b.UID_RulerContainer end as UID_Org from BaseTreeCollection co join BaseTree b on co.UID_Org = @uid_org and co.UID_ParentOrg5 = b.UID_Org 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_org6 where @WithFallBackRoot = 1 ) as z join PersonInBaseTree pio on z.UID_Org = pio.UID_Org and pio.XOrigin > 0 left outer join QER_VPersonsAreMe api on pio.UID_Person7 = api.UID_PersonAlsoMe and api.UID_PersonOrigin = case when @uid_personinserted > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue(8'QER\ITShop\PersonInsertedNoDecide') = '1' then @uid_personinserted else null end left outer join QER_VPersonsAreMe apo on pio.UID_Person = apo.UID_PersonAlsoMe9 and apo.UID_PersonOrigin = case when @uid_personordered > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'10) = '1' then @uid_personordered else null end group by z.treelevel, z.UID_Org having min(isnull(api.UID_PersonAlsoMe, '') ) = '' and min(isnull(apo.UID_PersonAlsoMe11, '') ) = '' order by z.treelevel desc  ) as oo join PersonInBaseTree pib on oo.UID_Org = pib.UID_Org left outer join PersonWantsOrg pwo on pwo.ObjectKeyAssignment12 = pib.XObjectKey and pib.XOrigin > 0 and pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) left outer join QER_VPersonsAreMe api 13on pib.UID_Person = api.UID_PersonAlsoMe and api.UID_PersonOrigin = case when @uid_personinserted > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue14('QER\ITShop\PersonInsertedNoDecide') = '1' then @uid_personinserted else null end left outer join QER_VPersonsAreMe apo on pib.UID_Person = apo.UID_PersonAlsoMe15 and apo.UID_PersonOrigin = case when @uid_personordered > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'16) = '1' then @uid_personordered else null end where api.UID_PersonAlsoMe is null and apo.UID_PersonAlsoMe is null ) 17