Back to OIM Explorer

dbo.QER_FGIAdminUnitForPerson

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.290 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.QER_FGIAdminUnitForPerson_help source text reference

References

dbo.QER_FGIAdminUnitForPerson_help

Referenced By

Complete Source

1CREATE FUNCTION dbo.QER_FGIAdminUnitForPerson(2  @UID_Person varchar(38)3) RETURNS varchar(1384)5AS6BEGIN7  DECLARE @ObjectkeyAdminUnit varchar(138)8  DECLARE @uid_dialogcountry varchar(38)9  DECLARE @uid_dialogState varchar(38)10  DECLARE @uid_locality varchar(38)11  DECLARE @uid_department varchar(38)12  DECLARE @uid_ProfitCenter varchar(38)13  SELECT14    TOP 1 @uid_dialogcountry = c.UID_DialogCountry,15    @uid_dialogState = s.UID_DialogState,16    @uid_locality = p.UID_Locality,17    @uid_department = p.UID_Department,18    @uid_ProfitCenter = p.UID_ProfitCenter19  FROM Person p20  LEFT21  OUTER22  JOIN DialogState s23    ON p.UID_DialogState = s.UID_DialogState AND s.IsInUse = 124  LEFT25  OUTER26  JOIN DialogCountry c27    ON p.UID_DialogCountry = c.UID_DialogCountry AND c.IsInUse = 128  WHERE29    p.UID_Person = @UID_Person30  IF @uid_dialogState IS NOT NULL31  BEGIN32    RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))33  END34  IF @uid_dialogCountry IS NOT NULL35  BEGIN36    RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))37  END38  IF @uid_locality IS NOT NULL39  BEGIN40    SELECT41      TOP 1 @uid_dialogcountry = c.UID_DialogCountry,42      @uid_dialogState = s.UID_DialogState43    FROM BaseTree p44    LEFT45    OUTER46    JOIN DialogState s47      ON p.UID_DialogState = s.UID_DialogState AND s.IsInUse = 148    LEFT49    OUTER50    JOIN DialogCountry c51      ON p.UID_DialogCountry = c.UID_DialogCountry AND c.IsInUse = 152    WHERE53      p.UID_Org = @uid_locality AND(c.UID_DialogCountry IS NOT NULL OR s.UID_DialogState IS NOT NULL)54    IF @@rowcount > 055    BEGIN56      RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))57    END58  END59  IF @uid_department IS NOT NULL60  BEGIN61    SELECT62      TOP 1 @uid_dialogcountry = c.UID_DialogCountry,63      @uid_dialogState = s.UID_DialogState64    FROM BaseTree p65    LEFT66    OUTER67    JOIN DialogState s68      ON p.UID_DialogState = s.UID_DialogState AND s.IsInUse = 169    LEFT70    OUTER71    JOIN DialogCountry c72      ON p.UID_DialogCountry = c.UID_DialogCountry AND c.IsInUse = 173    WHERE74      p.UID_Org = @uid_department AND(c.UID_DialogCountry IS NOT NULL OR s.UID_DialogState IS NOT NULL)75    IF @@rowcount > 076    BEGIN77      RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))78    END79  END80  IF @uid_ProfitCenter IS NOT NULL81  BEGIN82    SELECT83      TOP 1 @uid_dialogcountry = c.UID_DialogCountry,84      @uid_dialogState = s.UID_DialogState85    FROM BaseTree p86    LEFT87    OUTER88    JOIN DialogState s89      ON p.UID_DialogState = s.UID_DialogState AND s.IsInUse = 190    LEFT91    OUTER92    JOIN DialogCountry c93      ON p.UID_DialogCountry = c.UID_DialogCountry AND c.IsInUse = 194    WHERE95      p.UID_Org = @uid_ProfitCenter AND(c.UID_DialogCountry IS NOT NULL OR s.UID_DialogState IS NOT NULL)96    IF @@rowcount > 097    BEGIN98      RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))99    END100  END101  SELECT102    TOP 1 @uid_dialogcountry = c.UID_DialogCountry,103    @uid_dialogState = s.UID_DialogState104  FROM PersonInBaseTree pio105  JOIN BaseTree b106    ON pio.UID_Org = b.UID_Org107  JOIN(108  VALUES(1, 'QER-V-Locality'),109  (2, 'QER-V-Department'),110  (3, 'QER-V-ProfitCenter')) AS x(sortier,111  UID_OrgRoot)112    ON x.UID_OrgRoot = b.UID_OrgRoot AND pio.XOrigin > 0113  LEFT114  OUTER115  JOIN DialogState s116    ON b.UID_DialogState = s.UID_DialogState AND s.IsInUse = 1117  LEFT118  OUTER119  JOIN DialogCountry c120    ON b.UID_DialogCountry = c.UID_DialogCountry AND c.IsInUse = 1121  WHERE122    pio.UID_Person = @uid_person AND(c.UID_DialogCountry IS NOT NULL OR s.UID_DialogState IS NOT NULL)123  ORDER BY x.sortier,124  b.uid_org125  IF @@rowcount > 0126  BEGIN127    RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))128  END129  SELECT TOP 1 @uid_dialogCountry = c.uid_dialogCountry130  FROM DialogDatabase d131    WITH(readpast)132  JOIN DialogCountry c133    WITH(readpast)134    ON c.UID_DialogCountry = d.UID_DialogCountryDefault135  IF @@rowcount > 0136  BEGIN137    RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))138  END139  SELECT TOP 1 @uid_dialogCountry = uid_dialogCountry140  FROM dialogcountry141  WHERE142    isInuse = 1 AND telephone > ' '143  ORDER BY Telephone ASC,144  Iso3166_3 DESC145  IF @@rowcount > 0146  BEGIN147    RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))148  END149  SELECT TOP 1 @uid_dialogCountry = uid_dialogCountry150  FROM dialogCountry151  WHERE152    telephone > ' '153  ORDER BY Telephone ASC,154  Iso3166_3 DESC endLabel:155  RETURN(dbo.QER_FGIAdminUnitForPerson_help(@uid_dialogcountry, @uid_dialogState))156END

Open raw exported source

1create   function dbo.QER_FGIAdminUnitForPerson ( @UID_Person varchar(38) ) returns varchar(138) as begin declare @ObjectkeyAdminUnit varchar(1382)  declare @uid_dialogcountry varchar(38) declare @uid_dialogState varchar(38) declare @uid_locality varchar(38) declare @uid_department varchar(38) declare3 @uid_ProfitCenter varchar(38)  select top 1 @uid_dialogcountry = c.UID_DialogCountry , @uid_dialogState = s.UID_DialogState , @uid_locality = p.UID_Locality4 , @uid_department = p.UID_Department , @uid_ProfitCenter = p.UID_ProfitCenter  from Person p left outer join DialogState s on p.UID_DialogState = s.UID_DialogState5 and s.IsInUse = 1 left outer join DialogCountry c on p.UID_DialogCountry = c.UID_DialogCountry and c.IsInUse = 1 where p.UID_Person = @UID_Person  if 6@uid_dialogState is not null begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)) end  if @uid_dialogCountry is not7 null begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)) end if @uid_locality is not null begin select top 1 @uid_dialogcountry8 = c.UID_DialogCountry , @uid_dialogState = s.UID_DialogState from BaseTree p left outer join DialogState s on p.UID_DialogState = s.UID_DialogState and9 s.IsInUse = 1 left outer join DialogCountry c on p.UID_DialogCountry = c.UID_DialogCountry and c.IsInUse = 1 where p.UID_Org = @uid_locality and (c.UID_DialogCountry10 is not null or s.UID_DialogState is not null ) if @@rowcount > 0 begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState11)) end end if @uid_department is not null begin select top 1 @uid_dialogcountry = c.UID_DialogCountry , @uid_dialogState = s.UID_DialogState from BaseTree12 p left outer join DialogState s on p.UID_DialogState = s.UID_DialogState and s.IsInUse = 1 left outer join DialogCountry c on p.UID_DialogCountry = c.UID_DialogCountry13 and c.IsInUse = 1 where p.UID_Org = @uid_department and (c.UID_DialogCountry is not null or s.UID_DialogState is not null ) if @@rowcount > 0 begin  return14(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)) end end if @uid_ProfitCenter is not null begin select top 1 @uid_dialogcountry15 = c.UID_DialogCountry , @uid_dialogState = s.UID_DialogState from BaseTree p left outer join DialogState s on p.UID_DialogState = s.UID_DialogState and16 s.IsInUse = 1 left outer join DialogCountry c on p.UID_DialogCountry = c.UID_DialogCountry and c.IsInUse = 1 where p.UID_Org = @uid_ProfitCenter and (c.UID_DialogCountry17 is not null or s.UID_DialogState is not null ) if @@rowcount > 0 begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState18)) end end  select top 1 @uid_dialogcountry = c.UID_DialogCountry , @uid_dialogState = s.UID_DialogState from PersonInBaseTree pio join BaseTree b on pio.UID_Org19 = b.UID_Org join ( values (1, 'QER-V-Locality' ) , (2, 'QER-V-Department' ) , (3, 'QER-V-ProfitCenter' ) ) as x (sortier, UID_OrgRoot) on x.UID_OrgRoot20 = b.UID_OrgRoot and pio.XOrigin > 0 left outer join DialogState s on b.UID_DialogState = s.UID_DialogState and s.IsInUse = 1 left outer join DialogCountry21 c on b.UID_DialogCountry = c.UID_DialogCountry and c.IsInUse = 1 where pio.UID_Person = @uid_person and (c.UID_DialogCountry is not null or s.UID_DialogState22 is not null ) order by x.sortier, b.uid_org if @@rowcount > 0 begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)23) end select top 1 @uid_dialogCountry = c.uid_dialogCountry from DialogDatabase d with (readpast) join DialogCountry c with (readpast) on c.UID_DialogCountry24 = d.UID_DialogCountryDefault if @@rowcount > 0 begin return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)) end select top25 1 @uid_dialogCountry = uid_dialogCountry from dialogcountry where isInuse = 1 and telephone > ' ' order by Telephone asc, Iso3166_3 desc if @@rowcount26 > 0 begin  return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry , @uid_dialogState)) end select top 1 @uid_dialogCountry = uid_dialogCountry 27from dialogCountry where telephone > ' ' order by Telephone asc, Iso3166_3 desc endLabel: return(dbo.QER_FGIAdminUnitForPerson_help (@uid_dialogcountry28 , @uid_dialogState)) end 29