Back to OIM Explorer

dbo.QER_FTOrgAttHighest

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

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

SQL57 lines
1CREATE FUNCTION dbo.QER_FTOrgAttHighest(2  @uid_org varchar(38),3  @uid_personinserted varchar(38),4  @uid_personordered varchar(38),5  @WithFallBackRoot BIT,6  @IgnoreNoDecideForPerson BIT7) RETURNS TABLE8AS9RETURN(10SELECT11  pib.UID_Org,12  pib.UID_Person,13  pwo.UID_PersonWantsOrg AS UID_PWORulerOrigin14FROM(15SELECT TOP 1 z.UID_Org16FROM(17SELECT18  b.treelevel + 1 AS treelevel, b.UID_OrgAttestator AS UID_Org19FROM BaseTreeCollection co20JOIN BaseTree b21  ON co.UID_Org = @uid_org AND co.UID_ParentOrg = b.UID_Org22UNION all23SELECT24  0, r.UID_OrgAttestator AS uid_org25FROM OrgRoot r26JOIN BaseTree b27  ON b.UID_OrgRoot = r.UID_OrgRoot AND b.UID_Org = @uid_org28WHERE29  @WithFallBackRoot = 1) AS z30JOIN PersonInBaseTree pio31  ON z.UID_Org = pio.UID_Org AND pio.XOrigin > 032LEFT33OUTER34JOIN QER_VPersonsAreMe api35  ON pio.UID_Person = api.UID_PersonAlsoMe AND api.UID_PersonOrigin = CASE36WHEN @uid_personinserted > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide') = '1' THEN37@uid_personinserted38ELSE NULL39END40LEFT41OUTER42JOIN QER_VPersonsAreMe apo43  ON pio.UID_Person = apo.UID_PersonAlsoMe AND apo.UID_PersonOrigin = CASE44WHEN @uid_personordered > ' ' AND @IgnoreNoDecideForPerson = 0 AND dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide') = '1' THEN45@uid_personordered46ELSE NULL47END48GROUP BY z.treelevel, z.UID_Org49HAVING min(api.UID_PersonAlsoMe) IS NULL AND min(apo.UID_PersonAlsoMe) IS NULL50ORDER BY z.treelevel DESC) AS oo51JOIN PersonInBaseTree pib52  ON oo.UID_Org = pib.UID_Org53LEFT54OUTER55JOIN PersonWantsOrg pwo56  ON pwo.ObjectKeyAssignment = pib.XObjectKey AND pib.XOrigin > 0 AND pwo.OrderState IN('Assigned', 'OrderProlongate',57'OrderUnsubscribe'))
Open raw exported source
SQL ยท Raw12 lines
1create function dbo.QER_FTOrgAttHighest (@uid_org varchar(38) , @uid_personinserted varchar(38)  , @uid_personordered varchar(38) , @WithFallBackRoot bit2  , @IgnoreNoDecideForPerson bit  ) returns table as return ( select pib.UID_Org, pib.UID_Person, pwo.UID_PersonWantsOrg as UID_PWORulerOrigin from ( select3 top 1 z.UID_Org from (select b.treelevel + 1 as treelevel, b.UID_OrgAttestator as UID_Org from BaseTreeCollection co join BaseTree b on co.UID_Org = @uid_org4 and co.UID_ParentOrg = 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 5and b.UID_Org = @uid_org where @WithFallBackRoot = 1 ) as z join PersonInBaseTree pio on z.UID_Org = pio.UID_Org and pio.XOrigin > 0 left outer join QER_VPersonsAreMe6 api on pio.UID_Person = api.UID_PersonAlsoMe and api.UID_PersonOrigin = case when @uid_personinserted > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue7('QER\ITShop\PersonInsertedNoDecide') = '1' then @uid_personinserted else null end  left outer join QER_VPersonsAreMe apo on pio.UID_Person = apo.UID_PersonAlsoMe8 and apo.UID_PersonOrigin = case when @uid_personordered > ' ' and @IgnoreNoDecideForPerson = 0 and dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'9) = '1' then @uid_personordered else null end  group by z.treelevel, z.UID_Org having min(api.UID_PersonAlsoMe ) is null and min(apo.UID_PersonAlsoMe )10 is null order by z.treelevel desc  ) as oo join PersonInBaseTree pib on oo.UID_Org = pib.UID_Org left outer join PersonWantsOrg pwo on pwo.ObjectKeyAssignment11 = pib.XObjectKey and pib.XOrigin > 0 and pwo.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) ) 12