Back to OIM Explorer

dbo.QER_FGIITShopNewPersonOrdered

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

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

dbo.QBM_FGIConfigparmValue

Referenced By

dbo.QER_PITShopOrderMove_bulk

Complete Source

1CREATE FUNCTION dbo.QER_FGIITShopNewPersonOrdered(2  @UID_PersonWantsOrg varchar(38),3  @UID_BOTarget varchar(38),4  @ObjectkeyOrdered varchar(138)5) RETURNS varchar(386)7AS8BEGIN9  RETURN(10  SELECT11    convert(varchar(38), substring(min(z.MinPerson), 11, 38))12  FROM(13  SELECT14    CONCAT(str(y.weglaenge), y.UID_Person) AS MinPerson15  FROM(16  SELECT17    p.UID_Person, 1 AS weglaenge18  FROM PersonWantsOrg pwo19  JOIN Person p20    ON pwo.ObjectKeyElementUsedInAssign = p.XObjectKey AND p.UID_Person <> pwo.UID_PersonOrdered21  WHERE22    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg23  UNION all24  SELECT25    hh.UID_PersonHead, 100 + hh.XOrigin AS weglaenge26  FROM PersonWantsOrg pwo27  JOIN Person p28    ON pwo.ObjectKeyElementUsedInAssign = p.XObjectKey AND p.UID_Person <> pwo.UID_PersonOrdered29  JOIN HelperHeadPerson hh30    ON hh.UID_Person = p.UID_Person AND hh.XOrigin > 031  WHERE32    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg33  UNION all34  SELECT35    hh.UID_PersonHead, 200 + hh.XOrigin AS weglaenge36  FROM PersonWantsOrg pwo37  JOIN Person p38    ON pwo.ObjectKeyElementUsedInAssign = p.XObjectKey AND p.UID_Person <> pwo.UID_PersonOrdered39  JOIN HelperHeadOrg hh40    ON hh.UID_org = p.UID_Department AND hh.XOrigin > 041  WHERE42    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg43  UNION all44  SELECT45    pio.UID_Person, 300 + pio.XOrigin46  FROM PersonInBaseTree pio47  WHERE48    pio.UID_Org = 'QER-AEROLE-ITSHOP-INTERVENTION' AND xorigin > 049  UNION all50  SELECT51    dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU\UID_PersonFallback'),52    400 AS Weglaenge53  UNION all54  SELECT55    hh.UID_PersonHead, 1 + hh.XOrigin AS weglaenge56  FROM PersonWantsOrg pwo57  JOIN BaseTree o58    ON pwo.ObjectKeyOrgUsedInAssign = o.XObjectKey59  JOIN HelperHeadOrg hh60    ON o.UID_Org = hh.UID_Org61  WHERE62    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg AND isnull(pwo.ObjectKeyElementUsedInAssign, '') NOT LIKE '<Key><T>Person</T><P>%'63  UNION all64  SELECT65    pio.UID_Person, 100 + pio.XOrigin AS weglaenge66  FROM PersonWantsOrg pwo67  JOIN BaseTree o68    ON pwo.ObjectKeyOrgUsedInAssign = o.XObjectKey69  JOIN PersonInBaseTree pio70    ON o.UID_Org = pio.UID_Org71  WHERE72    pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg AND isnull(pwo.ObjectKeyElementUsedInAssign, '') NOT LIKE '<Key><T>Person</T><P>%') AS y73  JOIN PersonInBaseTree pcu74    ON y.UID_Person = pcu.UID_Person75  JOIN BaseTree cu76    ON pcu.UID_Org = cu.UID_Org77  JOIN BaseTree sh78    ON cu.UID_ParentOrg = sh.UID_Org79  JOIN BaseTree bo80    ON bo.UID_ParentOrg = sh.UID_Org81  JOIN BaseTree pr82    ON pr.UID_ParentOrg = bo.UID_Org83  JOIN QER_VPWOProductNodesSlim n84    ON n.UID_OrgPR = pr.UID_Org AND n.ObjectkeyOrdered = @ObjectkeyOrdered85  WHERE86    1 = 1 AND((@UID_BOTarget > ' ' AND bo.UID_Org = @UID_BOTarget) OR(isnull(@UID_BOTarget, '') = ''))) AS z87  WHERE88    dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU') > ' ')89END

Open raw exported source

1   create   function dbo.QER_FGIITShopNewPersonOrdered(@UID_PersonWantsOrg varchar(38) , @UID_BOTarget varchar(38) , @ObjectkeyOrdered varchar(1382) ) returns varchar(38) as begin return ( select convert(varchar(38), substring(min(z.MinPerson), 11,38)) from ( select concat( str(y.weglaenge) , y.UID_Person3) as MinPerson from (  select p.UID_Person, 1 as weglaenge from PersonWantsOrg pwo join Person p on pwo.ObjectKeyElementUsedInAssign = p.XObjectKey and4 p.UID_Person <> pwo.UID_PersonOrdered where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg union all  select hh.UID_PersonHead, 100 + hh.XOrigin as weglaenge5 from PersonWantsOrg pwo join Person p on pwo.ObjectKeyElementUsedInAssign = p.XObjectKey and p.UID_Person <> pwo.UID_PersonOrdered join HelperHeadPerson6 hh on hh.UID_Person = p.UID_Person and hh.XOrigin > 0 where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg union all  select hh.UID_PersonHead, 200 + hh.XOrigin7 as weglaenge from PersonWantsOrg pwo join Person p on pwo.ObjectKeyElementUsedInAssign = p.XObjectKey and p.UID_Person <> pwo.UID_PersonOrdered join HelperHeadOrg8 hh on hh.UID_org = p.UID_Department and hh.XOrigin > 0 where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg union all  select pio.UID_Person , 300 + pio.XOrigin9 from PersonInBaseTree pio where pio.UID_Org  = 'QER-AEROLE-ITSHOP-INTERVENTION' and xorigin > 0 union all  select dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU\UID_PersonFallback'10), 400 as Weglaenge union all      select hh.UID_PersonHead, 1 + hh.XOrigin as weglaenge from PersonWantsOrg pwo join BaseTree o on pwo.ObjectKeyOrgUsedInAssign11 = o.XObjectKey join HelperHeadOrg hh on o.UID_Org = hh.UID_Org where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg and isnull(pwo.ObjectKeyElementUsedInAssign12, '') not like '<Key><T>Person</T><P>%' union all  select pio.UID_Person, 100 + pio.XOrigin as weglaenge from PersonWantsOrg pwo join BaseTree o on pwo.ObjectKeyOrgUsedInAssign13 = o.XObjectKey join PersonInBaseTree pio on o.UID_Org = pio.UID_Org where pwo.UID_PersonWantsOrg = @UID_PersonWantsOrg and isnull(pwo.ObjectKeyElementUsedInAssign14, '') not like '<Key><T>Person</T><P>%'          ) as y  join PersonInBaseTree pcu on y.UID_Person = pcu.UID_Person join BaseTree cu on pcu.UID_Org = cu.UID_Org15 join BaseTree sh on cu.UID_ParentOrg = sh.UID_Org join BaseTree bo on bo.UID_ParentOrg = sh.UID_Org join BaseTree pr on pr.UID_ParentOrg = bo.UID_Org 16join QER_VPWOProductNodesSlim n on n.UID_OrgPR = pr.UID_Org and n.ObjectkeyOrdered = @ObjectkeyOrdered where 1 = 1   and ( ( @UID_BOTarget > ' ' and bo.UID_Org17 = @UID_BOTarget ) or (isnull(@UID_BOTarget, '') = ''  ) ) ) as z where dbo.QBM_FGIConfigparmValue('QER\ITShop\ReplaceAssignmentRequestOnLeaveCU') > ' '18 ) end 19