Back to OIM Explorer

dbo.QER_FTPWOVisibleForPerson_I

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 10.136 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_FTPWOVisibleForPerson source text reference
references source dbo.QBM_FCVStringToBit source text reference
references source dbo.QBM_FCVStringToInt source text reference
references source dbo.QBM_FGIConfigparmValue source text reference
references source dbo.QBM_FGISessionContext source text reference
references source dbo.QER_FGIPWOVisibleForPerson source text reference

References

Referenced By

dbo.QER_FTPWOVisibleForPerson

Complete Source

1CREATE FUNCTION dbo.QER_FTPWOVisibleForPerson_I(2  @uid_person varchar(38),3  @AsManager BIT,4  @UID_PersonWantsOrg varchar(38)5) RETURNS @erg TABLE(UID_PersonWantsOrg varchar(38) collate database_default,6IsClosed BIT primary key(UID_PersonWantsOrg)7)8AS9BEGIN10  DECLARE @erg_01orderd QBM_YParameterList11  DECLARE @erg_01inserted QBM_YParameterList12  DECLARE @erg_02 QBM_YParameterList13  DECLARE @erg_03 QBM_YParameterList14  DECLARE @erg_04 QBM_YParameterList15  DECLARE @erg_05 QBM_YParameterList16  DECLARE @erg_06 QBM_YParameterList17  DECLARE @erg_07 QBM_YParameterList18  DECLARE @MyReports QBM_YSingleGUID19  DECLARE @MyOrgs QBM_YSingleGUID20  DECLARE @MyOrgsBasis QBM_YSingleGUID21  DECLARE @PersonsMe QBM_YSingleGUID22  DECLARE @ShowClosedAssignment int23  DECLARE @ReduceMyReports BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGISessionContext('PWOAccessAsManagerReduced'))24  SELECT25    @ShowClosedAssignment = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\ShowClosedAssignmentOrders'),26    0)27  IF @UID_PersonWantsOrg > ' '28  BEGIN29    IF dbo.QER_FGIPWOVisibleForPerson(@uid_person,30    @UID_PersonWantsOrg,31    @AsManager) = 132    BEGIN33      INSERT INTO @erg_01orderd(Parameter1,34      Parameter2)35      SELECT36        TOP 1 pwo.UID_PersonWantsOrg,37        pwo.OrderState38      FROM PersonWantsOrg pwo39      WHERE40        pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg41      GOTO TestsDone42    END43  END44  INSERT INTO @PersonsMe(UID_SingleGuid)45  SELECT46    DISTINCT me.UID_PersonAlsoMe47  FROM QER_VPersonsAreMe me48  WHERE49    me.UID_PersonOrigin = @uid_person50  IF @AsManager = 151  BEGIN52    INSERT INTO @MyOrgsBasis(UID_SingleGuid)53    SELECT x.UID_Org54    FROM(55    SELECT hho.UID_Org56    FROM HelperHeadOrg hho57    JOIN @PersonsMe me58      ON hho.UID_PersonHead = me.UID_SingleGuid59    WHERE60      hho.XOrigin > 061    UNION all62    SELECT UID_Org63    FROM BaseTree b64    JOIN @PersonsMe me65      ON b.UID_PersonHead = me.UID_SingleGuid66    UNION all67    SELECT UID_Org f68    FROM BaseTree b69    JOIN @PersonsMe me70      ON b.UID_PersonHeadSecond = me.UID_SingleGuid) AS x71    GROUP BY x.UID_Org72    INSERT INTO @MyOrgs(UID_SingleGuid,73    BitProperty)74    SELECT75      x.UID_SingleGuid,76      min(x.isParent)77    FROM(78    SELECT79      b.UID_SingleGuid,80      0 AS isParent81    FROM @MyOrgsBasis b82    UNION all83    SELECT84      co.UID_Org,85      086    FROM @MyOrgsBasis b87    JOIN BaseTreeCollectionF co88      ON b.UID_SingleGuid = co.UID_ParentOrg AND co.UID_Org <> co.UID_ParentOrg89    UNION all90    SELECT91      co.UID_ParentOrg,92    CASE93      WHEN co.UID_Org = co.UID_ParentOrg THEN94    095    ELSE 196    END97    FROM @MyOrgsBasis b98    JOIN BaseTreeCollectionF co99      ON b.UID_SingleGuid = co.UID_Org AND co.UID_Org <> co.UID_ParentOrg) AS x100    GROUP BY x.UID_SingleGuid101    IF @ReduceMyReports = 0102    BEGIN103      INSERT INTO @MyReports(UID_SingleGuid)104      SELECT hpo.uid_person105      FROM @MyOrgs c106      JOIN helperpersonorg hpo107        ON hpo.uid_org = c.UID_SingleGuid108      WHERE109        BitProperty = 0110      UNION111      SELECT hhp.UID_Person112      FROM @PersonsMe me113      JOIN HelperHeadPerson hhp114        ON hhp.UID_PersonHead = me.UID_SingleGuid115      WHERE116        hhp.XOrigin > 0117      UNION118      SELECT me.UID_SingleGuid119      FROM @PersonsMe me120    END121    ELSE122    BEGIN123      INSERT INTO @MyReports(UID_SingleGuid)124      SELECT hpo.uid_person125      FROM @MyOrgsBasis c126      JOIN HelperPersonOrg hpo127        ON hpo.UID_Org = c.UID_SingleGuid128      UNION129      SELECT hhp.UID_Person130      FROM @PersonsMe me131      JOIN HelperHeadPerson hhp132        ON hhp.UID_PersonHead = me.UID_SingleGuid133      WHERE134        hhp.XOrigin > 0135      UNION136      SELECT me.UID_SingleGuid137      FROM @PersonsMe me138    END139  END140  ELSE141  BEGIN142    INSERT INTO @MyReports(UID_SingleGuid)143    SELECT me.UID_SingleGuid144    FROM @PersonsMe me145  END146  IF @AsManager = 1147  BEGIN148    IF @ShowClosedAssignment = 1149    BEGIN150      INSERT INTO @erg_01orderd(Parameter1,151      Parameter2)152      SELECT153        pwo.UID_PersonWantsOrg,154        pwo.OrderState155      FROM PersonWantsOrg pwo156      JOIN @MyReports p157        ON pwo.UID_PersonOrdered = p.UID_SingleGuid158      WHERE159        pwo.OrderState IN('Assigned',160      'Granted',161      'New',162      'OrderProduct',163      'OrderProlongate',164      'OrderUnsubscribe',165      'Waiting') OR pwo.ObjectKeyAssignment IS NULL OR(pwo.OrderState IN('Aborted', 'Dismissed', 'Unsubscribed') AND pwo.ObjectKeyAssignment > ' ')166      INSERT INTO @erg_01inserted(Parameter1,167      Parameter2)168      SELECT169        pwo.UID_PersonWantsOrg,170        pwo.OrderState171      FROM PersonWantsOrg pwo172      JOIN @MyReports p173        ON pwo.UID_PersonInserted = p.UID_SingleGuid AND pwo.UID_PersonInserted <> pwo.UID_PersonOrdered174      WHERE175        pwo.OrderState IN('Assigned',176      'Granted',177      'New',178      'OrderProduct',179      'OrderProlongate',180      'OrderUnsubscribe',181      'Waiting') OR pwo.ObjectKeyAssignment IS NULL OR(pwo.OrderState IN('Aborted', 'Dismissed', 'Unsubscribed') AND pwo.ObjectKeyAssignment > ' ')182    END183    ELSE184    BEGIN185      INSERT INTO @erg_01orderd(Parameter1,186      Parameter2)187      SELECT188        pwo.UID_PersonWantsOrg,189        pwo.OrderState190      FROM PersonWantsOrg pwo191      JOIN @MyReports p192        ON pwo.UID_PersonOrdered = p.UID_SingleGuid193      WHERE194        pwo.OrderState IN('Assigned',195      'Granted',196      'New',197      'OrderProduct',198      'OrderProlongate',199      'OrderUnsubscribe',200      'Waiting') OR pwo.ObjectKeyAssignment IS NULL201      INSERT INTO @erg_01inserted(Parameter1,202      Parameter2)203      SELECT204        pwo.UID_PersonWantsOrg,205        pwo.OrderState206      FROM PersonWantsOrg pwo207      JOIN @MyReports p208        ON pwo.UID_PersonInserted = p.UID_SingleGuid AND pwo.UID_PersonInserted <> pwo.UID_PersonOrdered209      WHERE210        pwo.OrderState IN('Assigned',211      'Granted',212      'New',213      'OrderProduct',214      'OrderProlongate',215      'OrderUnsubscribe',216      'Waiting') OR pwo.ObjectKeyAssignment IS NULL217    END218  END219  ELSE220  BEGIN221    INSERT INTO @erg_01orderd(Parameter1,222    Parameter2)223    SELECT224      pwo.UID_PersonWantsOrg,225      pwo.OrderState226    FROM PersonWantsOrg pwo227    JOIN @MyReports p228      ON pwo.UID_PersonOrdered = p.UID_SingleGuid229    INSERT INTO @erg_01inserted(Parameter1,230    Parameter2)231    SELECT232      pwo.UID_PersonWantsOrg,233      pwo.OrderState234    FROM PersonWantsOrg pwo235    JOIN @MyReports p236      ON pwo.UID_PersonInserted = p.UID_SingleGuid AND pwo.UID_PersonInserted <> pwo.UID_PersonOrdered237  END238  IF @AsManager = 1239  BEGIN240    IF @ReduceMyReports = 0241    BEGIN242      INSERT INTO @erg_02(Parameter1,243      Parameter2)244      SELECT245        x.UID_PersonWantsOrg,246        x.OrderState247      FROM(248      SELECT249        pwo.UID_PersonWantsOrg,250        pwo.OrderState251      FROM PersonWantsOrg pwo252      JOIN BaseTree b253        ON pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey254      JOIN @MyOrgs mc255        ON b.uid_org = mc.UID_SingleGuid256      WHERE257        (pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',258      'Waiting') OR @ShowClosedAssignment = 1)) AS x259    END260    ELSE261    BEGIN262      INSERT INTO @erg_02(Parameter1,263      Parameter2)264      SELECT265        x.UID_PersonWantsOrg,266        x.OrderState267      FROM(268      SELECT269        pwo.UID_PersonWantsOrg,270        pwo.OrderState271      FROM PersonWantsOrg pwo272      JOIN BaseTree b273        ON pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey274      JOIN @MyOrgsBasis mc275        ON b.uid_org = mc.UID_SingleGuid276      WHERE277        (pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',278      'Waiting') OR @ShowClosedAssignment = 1)) AS x279    END280  END281  IF @AsManager = 0282  BEGIN283    INSERT INTO @erg_03(Parameter1)284    SELECT h.UID_PersonWantsOrg285    FROM PWOHelperPWO h286    JOIN @PersonsMe me287      ON me.UID_SingleGuid = h.UID_PersonHead288    GROUP BY h.UID_PersonWantsOrg289    UPDATE @erg_03290    SET Parameter3 = pwo.UID_ShoppingCartOrder,291    Parameter2 = pwo.OrderState292    FROM @erg_03 w293    JOIN personwantsorg pwo294      ON pwo.uid_personwantsorg = w.Parameter1295    INSERT INTO @erg_04(Parameter1,296    Parameter2)297    SELECT298      x.UID_PersonWantsOrg,299      x.OrderState300    FROM(301    SELECT302      pwo.UID_PersonWantsorg,303      pwo.OrderState304    FROM PersonWantsOrg pwo305    JOIN(306    SELECT307      DISTINCT h.UID_PersonWantsOrg308    FROM PWODecisionHistory h309    JOIN @PersonsMe me310      ON me.UID_SingleGuid = h.UID_PersonHead) hp311      ON hp.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg) AS x312    INSERT INTO @erg_05(Parameter1)313    SELECT x.UID_PersonWantsOrg314    FROM(315    SELECT316      DISTINCT d.UID_PersonWantsOrg317    FROM Delegation d318    JOIN @PersonsMe me319      ON d.UID_PersonReceiver = me.UID_SingleGuid) AS x320    UPDATE @erg_05321    SET Parameter2 = pwo.OrderState322    FROM @erg_05 w323    JOIN personwantsorg pwo324      ON pwo.uid_personwantsorg = w.Parameter1325    INSERT INTO @erg_07(Parameter1)326    SELECT x.UID_PersonWantsOrg327    FROM(328    SELECT329      DISTINCT d.UID_PersonWantsOrg330    FROM QERUniversalSubstitute d331    JOIN @PersonsMe me332      ON d.UID_PersonReceiver = me.UID_SingleGuid) AS x333    UPDATE @erg_07334    SET Parameter2 = pwo.OrderState335    FROM @erg_07 w336    JOIN personwantsorg pwo337      ON pwo.uid_personwantsorg = w.Parameter1338    INSERT INTO @erg_06(Parameter1,339    Parameter2)340    SELECT341      x.UID_PersonWantsOrg,342      x.OrderState343    FROM(344    SELECT345      pwo.UID_PersonWantsOrg,346      pwo.OrderState347    FROM PersonWantsOrg pwo348    JOIN(349    SELECT hp.Parameter3350    FROM @erg_03 hp351    GROUP BY hp.Parameter3) AS so352      ON pwo.UID_ShoppingCartOrder = so.Parameter3) AS x353  END354  IF @AsManager = 1355  BEGIN356    INSERT INTO @erg_05(Parameter1,357    Parameter2)358    SELECT359      d.UID_PersonWantsOrg,360      pwo.OrderState361    FROM Delegation d362    JOIN @MyReports mr363      ON d.UID_PersonReceiver = mr.UID_SingleGuid364    JOIN PersonWantsOrg pwo365      ON d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg366    WHERE367      (pwo.OrderState IN('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe',368    'Waiting') OR @ShowClosedAssignment = 1)369  END370  TestsDone:371  IF @AsManager = 0372  BEGIN373    INSERT INTO @erg(UID_PersonWantsOrg,374    IsClosed)375    SELECT376      z.UID_PersonWantsOrg,377    CASE378      WHEN z.OrderState IN('Aborted',379    'Dismissed',380    'Unsubscribed') THEN381    1382    ELSE 0383    END AS IsClosed384    FROM(385    SELECT386      y.Parameter1 AS UID_PersonWantsOrg,387      max(y.Parameter2) AS OrderState388    FROM(389    SELECT390      x.Parameter1, x.Parameter2391    FROM @erg_01orderd x392    UNION all393    SELECT394      x.Parameter1, x.Parameter2395    FROM @erg_01inserted x396    UNION all397    SELECT398      x.Parameter1, x.Parameter2399    FROM @erg_03 x400    UNION all401    SELECT402      x.Parameter1, x.Parameter2403    FROM @erg_04 x404    UNION all405    SELECT406      x.Parameter1, x.Parameter2407    FROM @erg_05 x408    UNION all409    SELECT410      x.Parameter1, x.Parameter2411    FROM @erg_06 x412    UNION all413    SELECT414      x.Parameter1, x.Parameter2415    FROM @erg_07 x) AS y416    GROUP BY y.Parameter1) AS z417  END418  ELSE419  BEGIN420    INSERT INTO @erg(UID_PersonWantsOrg,421    IsClosed)422    SELECT423      z.UID_PersonWantsOrg,424    CASE425      WHEN z.OrderState IN('Aborted',426    'Dismissed',427    'Unsubscribed') THEN428    1429    ELSE 0430    END AS IsClosed431    FROM(432    SELECT433      y.Parameter1 AS UID_PersonWantsOrg,434      max(y.Parameter2) AS OrderState435    FROM(436    SELECT437      x.Parameter1, x.Parameter2438    FROM @erg_01orderd x439    UNION all440    SELECT441      x.Parameter1, x.Parameter2442    FROM @erg_01inserted x443    UNION all444    SELECT445      x.Parameter1, x.Parameter2446    FROM @erg_02 x447    UNION all448    SELECT449      x.Parameter1, x.Parameter2450    FROM @erg_05 x) AS y451    GROUP BY y.Parameter1) AS z452  END453  endLabel:454  RETURN455END

Open raw exported source

1create function dbo.QER_FTPWOVisibleForPerson_I (@uid_person varchar(38) , @AsManager bit   , @UID_PersonWantsOrg varchar(38)   ) returns @erg table (UID_PersonWantsOrg2 varchar(38) collate database_default , IsClosed bit primary key (UID_PersonWantsOrg) ) as begin       declare @erg_01orderd QBM_YParameterList declare3 @erg_01inserted QBM_YParameterList declare @erg_02 QBM_YParameterList declare @erg_03 QBM_YParameterList declare @erg_04 QBM_YParameterList declare @erg_054 QBM_YParameterList declare @erg_06 QBM_YParameterList declare @erg_07 QBM_YParameterList declare @MyReports QBM_YSingleGUID   declare @MyOrgs QBM_YSingleGUID5   declare @MyOrgsBasis QBM_YSingleGUID   declare @PersonsMe QBM_YSingleGUID   declare @ShowClosedAssignment int declare @ReduceMyReports bit = dbo.QBM_FCVStringToBit6( dbo.QBM_FGISessionContext('PWOAccessAsManagerReduced')) select @ShowClosedAssignment = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\ShowClosedAssignmentOrders'7) , 0) if @UID_PersonWantsOrg > ' ' begin  if dbo.QER_FGIPWOVisibleForPerson(@uid_person, @UID_PersonWantsOrg, @AsManager) = 1 begin insert into @erg_01orderd8(Parameter1, Parameter2) select top 1  pwo.UID_PersonWantsOrg, pwo.OrderState  from PersonWantsOrg pwo  where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg9 goto TestsDone end end insert into @PersonsMe (UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me where me.UID_PersonOrigin10 = @uid_person     if @AsManager = 1 begin                insert into @MyOrgsBasis(UID_SingleGuid) select x.UID_Org from( select hho.UID_Org from HelperHeadOrg11 hho join @PersonsMe me on hho.UID_PersonHead = me.UID_SingleGuid where hho.XOrigin > 0  union all select UID_Org from BaseTree b join @PersonsMe me on12 b.UID_PersonHead = me.UID_SingleGuid union all select UID_Org f from BaseTree b join @PersonsMe me on b.UID_PersonHeadSecond = me.UID_SingleGuid ) as 13x group by x.UID_Org  insert into @MyOrgs (UID_SingleGuid, BitProperty) select x.UID_SingleGuid, min(x.isParent) from ( select b.UID_SingleGuid , 0 as 14isParent from @MyOrgsBasis b union all select co.UID_Org, 0 from @MyOrgsBasis b join BaseTreeCollectionF co on b.UID_SingleGuid = co.UID_ParentOrg and 15co.UID_Org <> co.UID_ParentOrg union all select co.UID_ParentOrg, case when co.UID_Org = co.UID_ParentOrg then 0 else 1 end from @MyOrgsBasis b join BaseTreeCollectionF16 co on b.UID_SingleGuid = co.UID_Org and co.UID_Org <> co.UID_ParentOrg ) as x group by x.UID_SingleGuid if @ReduceMyReports = 0 begin  insert into @MyReports17(UID_SingleGuid)  select hpo.uid_person from @MyOrgs c join helperpersonorg hpo on hpo.uid_org = c.UID_SingleGuid  where BitProperty = 0 union  select 18hhp.UID_Person from @PersonsMe me join HelperHeadPerson hhp on hhp.UID_PersonHead = me.UID_SingleGuid where hhp.XOrigin > 0  union select me.UID_SingleGuid19 from @PersonsMe me end else begin  insert into @MyReports(UID_SingleGuid)  select hpo.uid_person from @MyOrgsBasis c join HelperPersonOrg hpo on hpo.UID_Org20 = c.UID_SingleGuid union  select hhp.UID_Person from @PersonsMe me join HelperHeadPerson hhp on hhp.UID_PersonHead = me.UID_SingleGuid where hhp.XOrigin21 > 0  union select me.UID_SingleGuid from @PersonsMe me end end else begin  insert into @MyReports(UID_SingleGuid) select me.UID_SingleGuid from @PersonsMe22 me end                               if @AsManager = 1 begin if @ShowClosedAssignment = 1 begin insert into @erg_01orderd( Parameter1, Parameter2) select23 pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid  where pwo.OrderState in 24('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment is null or ( pwo.OrderState25 in ('Aborted', 'Dismissed', 'Unsubscribed' )  and pwo.ObjectKeyAssignment > ' '  ) insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg26, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonInserted = p.UID_SingleGuid and pwo.UID_PersonInserted <> pwo.UID_PersonOrdered27   where pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment28 is null or ( pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' )  and pwo.ObjectKeyAssignment > ' '  ) end  else begin insert into @erg_01orderd29( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid30   where pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment31 is null      insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports32 p on pwo.UID_PersonInserted = p.UID_SingleGuid and pwo.UID_PersonInserted <> pwo.UID_PersonOrdered    where pwo.OrderState in ('Assigned', 'Granted', 33'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment is null     end  end else begin  insert into @erg_01orderd34( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid35   insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonInserted36 = p.UID_SingleGuid and pwo.UID_PersonInserted <> pwo.UID_PersonOrdered    end if @AsManager = 1 begin    if @ReduceMyReports = 0 begin  insert into @erg_0237(Parameter1, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo    join38 BaseTree b on pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey join @MyOrgs mc on b.uid_org = mc.UID_SingleGuid where (pwo.OrderState in ('Assigned', 'Granted'39, 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')  or @ShowClosedAssignment = 1 ) ) as x  end else begin  insert into @erg_02(Parameter140, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join BaseTree b on41 pwo.ObjectKeyOrgUsedInAssign = b.XObjectKey join @MyOrgsBasis mc on b.uid_org = mc.UID_SingleGuid where (pwo.OrderState in ('Assigned', 'Granted', 'New'42, 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')  or @ShowClosedAssignment = 1 ) ) as x end  end  if @AsManager = 0 begin    insert 43into @erg_03 (Parameter1) select h.UID_PersonWantsOrg from PWOHelperPWO h join @PersonsMe me on me.UID_SingleGuid = h.UID_PersonHead group by h.UID_PersonWantsOrg44 update @erg_03 set Parameter3 = pwo.UID_ShoppingCartOrder , Parameter2 = pwo.OrderState from @erg_03 w join personwantsorg pwo on pwo.uid_personwantsorg45 = w.Parameter1     insert into @erg_04 (Parameter1, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsorg , pwo.OrderState46 from PersonWantsOrg pwo join ( select distinct h.UID_PersonWantsOrg from PWODecisionHistory h join @PersonsMe me on me.UID_SingleGuid = h.UID_PersonHead47 ) hp on hp.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg  ) as x      insert into @erg_05 (Parameter1) select x.UID_PersonWantsOrg from ( select distinct48 d.UID_PersonWantsOrg from Delegation d join @PersonsMe me on d.UID_PersonReceiver = me.UID_SingleGuid ) as x   update @erg_05 set Parameter2 = pwo.OrderState49 from @erg_05 w join personwantsorg pwo on pwo.uid_personwantsorg = w.Parameter1    insert into @erg_07 (Parameter1) select x.UID_PersonWantsOrg from (50 select distinct d.UID_PersonWantsOrg from QERUniversalSubstitute d join @PersonsMe me on d.UID_PersonReceiver = me.UID_SingleGuid ) as x   update @erg_0751 set Parameter2 = pwo.OrderState from @erg_07 w join personwantsorg pwo on pwo.uid_personwantsorg = w.Parameter1    insert into @erg_06 (Parameter1, Parameter252) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo       join ( select hp.Parameter353 from @erg_03 hp group by hp.Parameter3 ) as so on pwo.UID_ShoppingCartOrder = so.Parameter3      ) as x   end   if @AsManager = 1 begin INSERT INTO @erg_0554(Parameter1, Parameter2)         SELECT d.UID_PersonWantsOrg, pwo.OrderState FROM Delegation d JOIN @MyReports mr ON d.UID_PersonReceiver = mr.UID_SingleGuid55  join PersonWantsOrg pwo on d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg  where (pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate'56, 'OrderUnsubscribe', 'Waiting')  or @ShowClosedAssignment = 1 )  end   TestsDone: if @AsManager = 0 begin insert into @erg (UID_PersonWantsOrg, IsClosed57) select z.UID_PersonWantsOrg, case when z.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) then 1 else 0 end as IsClosed from ( select y.Parameter158 as UID_PersonWantsOrg, max(y.Parameter2) as OrderState from ( select x.Parameter1, x.Parameter2 from @erg_01orderd x union all select x.Parameter1, x.Parameter259 from @erg_01inserted x union all    select x.Parameter1, x.Parameter2 from @erg_03 x union all select x.Parameter1, x.Parameter2 from @erg_04 x union 60all select x.Parameter1, x.Parameter2 from @erg_05 x union all select x.Parameter1, x.Parameter2 from @erg_06 x union all select x.Parameter1, x.Parameter261 from @erg_07 x ) as y group by y.Parameter1 ) as z end else  begin insert into @erg (UID_PersonWantsOrg, IsClosed) select z.UID_PersonWantsOrg, case when62 z.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) then 1 else 0 end as IsClosed from ( select y.Parameter1 as UID_PersonWantsOrg, max(y.Parameter263) as OrderState from ( select x.Parameter1, x.Parameter2 from @erg_01orderd x union all select x.Parameter1, x.Parameter2 from @erg_01inserted x union 64all select x.Parameter1, x.Parameter2 from @erg_02 x  union all select x.Parameter1, x.Parameter2 from @erg_05 x  ) as y group by y.Parameter1 ) as z end65 endLabel: return end 66