dbo.QER_FTPWOVisibleForPerson_I

1create function dbo.QER_FTPWOVisibleForPerson_I (@uid_person varchar(38) , @AsManager bit   , @UID_PersonWantsOrg varchar(38)   ) returns @erg table (UID_PersonWantsOrg
2 varchar(38) collate database_default , IsClosed bit primary key (UID_PersonWantsOrg) ) as begin       declare @erg_01orderd QBM_YParameterList declare
3 @erg_01inserted QBM_YParameterList declare @erg_02 QBM_YParameterList declare @erg_03 QBM_YParameterList declare @erg_04 QBM_YParameterList declare @erg_05
4 QBM_YParameterList declare @erg_06 QBM_YParameterList declare @erg_07 QBM_YParameterList declare @MyReports QBM_YSingleGUID   declare @MyOrgs QBM_YSingleGUID
5   declare @MyOrgsBasis QBM_YSingleGUID   declare @PersonsMe QBM_YSingleGUID   declare @ShowClosedAssignment int declare @ReduceMyReports bit = dbo.QBM_FCVStringToBit
6( 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_01orderd
8(Parameter1, Parameter2) select top 1  pwo.UID_PersonWantsOrg, pwo.OrderState  from PersonWantsOrg pwo  where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg
9 goto TestsDone end end insert into @PersonsMe (UID_SingleGuid ) select distinct me.UID_PersonAlsoMe from QER_VPersonsAreMe me where me.UID_PersonOrigin
10 = @uid_person     if @AsManager = 1 begin                insert into @MyOrgsBasis(UID_SingleGuid) select x.UID_Org from( select hho.UID_Org from HelperHeadOrg
11 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 on
12 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 BaseTreeCollectionF
16 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 @MyReports
17(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_SingleGuid
19 from @PersonsMe me end else begin  insert into @MyReports(UID_SingleGuid)  select hpo.uid_person from @MyOrgsBasis c join HelperPersonOrg hpo on hpo.UID_Org
20 = c.UID_SingleGuid union  select hhp.UID_Person from @PersonsMe me join HelperHeadPerson hhp on hhp.UID_PersonHead = me.UID_SingleGuid where hhp.XOrigin
21 > 0  union select me.UID_SingleGuid from @PersonsMe me end end else begin  insert into @MyReports(UID_SingleGuid) select me.UID_SingleGuid from @PersonsMe
22 me end                               if @AsManager = 1 begin if @ShowClosedAssignment = 1 begin insert into @erg_01orderd( Parameter1, Parameter2) select
23 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.OrderState
25 in ('Aborted', 'Dismissed', 'Unsubscribed' )  and pwo.ObjectKeyAssignment > ' '  ) insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg
26, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonInserted = p.UID_SingleGuid and pwo.UID_PersonInserted <> pwo.UID_PersonOrdered
27   where pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment
28 is null or ( pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' )  and pwo.ObjectKeyAssignment > ' '  ) end  else begin insert into @erg_01orderd
29( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid
30   where pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting')   or pwo.ObjectKeyAssignment
31 is null      insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports
32 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_01orderd
34( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonOrdered = p.UID_SingleGuid
35   insert into @erg_01inserted( Parameter1, Parameter2) select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join @MyReports p on pwo.UID_PersonInserted
36 = p.UID_SingleGuid and pwo.UID_PersonInserted <> pwo.UID_PersonOrdered    end if @AsManager = 1 begin    if @ReduceMyReports = 0 begin  insert into @erg_02
37(Parameter1, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo    join
38 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(Parameter1
40, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo join BaseTree b on
41 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_PersonWantsOrg
44 update @erg_03 set Parameter3 = pwo.UID_ShoppingCartOrder , Parameter2 = pwo.OrderState from @erg_03 w join personwantsorg pwo on pwo.uid_personwantsorg
45 = w.Parameter1     insert into @erg_04 (Parameter1, Parameter2) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsorg , pwo.OrderState
46 from PersonWantsOrg pwo join ( select distinct h.UID_PersonWantsOrg from PWODecisionHistory h join @PersonsMe me on me.UID_SingleGuid = h.UID_PersonHead
47 ) hp on hp.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg  ) as x      insert into @erg_05 (Parameter1) select x.UID_PersonWantsOrg from ( select distinct
48 d.UID_PersonWantsOrg from Delegation d join @PersonsMe me on d.UID_PersonReceiver = me.UID_SingleGuid ) as x   update @erg_05 set Parameter2 = pwo.OrderState
49 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_07
51 set Parameter2 = pwo.OrderState from @erg_07 w join personwantsorg pwo on pwo.uid_personwantsorg = w.Parameter1    insert into @erg_06 (Parameter1, Parameter2
52) select x.UID_PersonWantsOrg, x.OrderState from ( select pwo.UID_PersonWantsOrg, pwo.OrderState from PersonWantsOrg pwo       join ( select hp.Parameter3
53 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_05
54(Parameter1, Parameter2)         SELECT d.UID_PersonWantsOrg, pwo.OrderState FROM Delegation d JOIN @MyReports mr ON d.UID_PersonReceiver = mr.UID_SingleGuid
55  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, IsClosed
57) select z.UID_PersonWantsOrg, case when z.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) then 1 else 0 end as IsClosed from ( select y.Parameter1
58 as UID_PersonWantsOrg, max(y.Parameter2) as OrderState from ( select x.Parameter1, x.Parameter2 from @erg_01orderd x union all select x.Parameter1, x.Parameter2
59 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.Parameter2
61 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 when
62 z.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) then 1 else 0 end as IsClosed from ( select y.Parameter1 as UID_PersonWantsOrg, max(y.Parameter2
63) 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 end
65 endLabel: return end 
66