Back to OIM Explorer

dbo.CPL_FTCCSPersonCheckWithOrg

Inline Table FunctionSQL_INLINE_TABLE_VALUED_FUNCTIONSandbox DB

Inline Table Function.

Source: sandbox-db sys.sql_modules

Source size: 1.919 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.CPL_FTComplianceSubRulePerson source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL62 lines
1CREATE FUNCTION dbo.CPL_FTCCSPersonCheckWithOrg(2  @UID_Person varchar(38),3  @UID_Org varchar(38)4) RETURNS TABLE5AS6RETURN(7SELECT8  x.UID_Person,9  x.UID_ComplianceRule,10  cr.UID_NonCompliance,11  x.isExceptionGranted,12  x.IsNew13FROM(14SELECT15  ps.UID_Person, ps.UID_ComplianceRule, isnull(pinc.IsExceptionGranted, 0) AS isExceptionGranted,16  sign(len(isnull(pinc.UID_Org, ''))) ^ 1 AS IsNew17FROM(18SELECT19  sr.UID_ComplianceRule, sr.UID_ComplianceSubRule, @UID_Person AS UID_Person20FROM ComplianceSubRule sr21JOIN ComplianceSubRuleObject cm22  ON cm.uid_ComplianceSubRule = sr.uid_ComplianceSubRule CROSS apply dbo.CPL_FTComplianceSubRulePerson(sr.uid_complianceRule) cp23JOIN(24SELECT25  @UID_Person AS UID_Person, p1.ObjectKey26FROM PersonHasObject p127WHERE28  p1.UID_Person = @UID_Person29UNION30SELECT31  @UID_Person, b1.ObjectKey32FROM BaseTreeHasObject b133WHERE34  b1.UID_Org = @UID_Org35UNION36SELECT37  @UID_Person, b1.XObjectKey AS ObjectKey38FROM BaseTree b139WHERE40  b1.UID_Org = @UID_Org) AS pho41  ON cp.uid_person = @UID_Person AND pho.UID_Person = @UID_Person AND pho.Objectkey = cm.ObjectKeyElement42GROUP BY sr.UID_ComplianceRule, sr.UID_ComplianceSubRule, sr.CountMatchesMin, pho.UID_Person43HAVING COUNT(cm.ObjectKeyElement) >= CASE sr.CountMatchesMin44WHEN 0 THEN45146ELSE sr.CountMatchesMin47END) AS ps48JOIN compliancerule cr49  ON ps.uid_complianceRule = cr.uid_complianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 050LEFT51OUTER52JOIN personinBaseTree pinc53  ON pinc.uid_person = ps.uid_person AND pinc.uid_org = cr.uid_noncompliance54GROUP BY ps.uid_person, ps.uid_ComplianceRule, isnull(pinc.uid_org, ''), isnull(pinc.isexceptiongranted,550)56HAVING count(DISTINCT ps.uid_ComplianceSubRule) =(57SELECT count(*)58FROM ComplianceSubrule59WHERE60  uid_complianceRule = ps.uid_complianceRule)) AS x61JOIN ComplianceRule cr62  ON x.UID_ComplianceRule = cr.UID_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0)
Open raw exported source
SQL ยท Raw14 lines
1create function dbo.CPL_FTCCSPersonCheckWithOrg(@UID_Person varchar(38) , @UID_Org varchar(38)  ) returns table as return( select x.UID_Person, x.UID_ComplianceRule2 , cr.UID_NonCompliance , x.isExceptionGranted , x.IsNew from ( select ps.UID_Person, ps.UID_ComplianceRule  , isnull(pinc.IsExceptionGranted, 0) as isExceptionGranted3 , sign(len(isnull(pinc.UID_Org,''))) ^ 1 as IsNew from ( select sr.UID_ComplianceRule, sr.UID_ComplianceSubRule, @UID_Person as UID_Person from ComplianceSubRule4 sr join ComplianceSubRuleObject cm on cm.uid_ComplianceSubRule = sr.uid_ComplianceSubRule cross apply dbo.CPL_FTComplianceSubRulePerson(sr.uid_complianceRule5) cp join ( select @UID_Person as UID_Person, p1.ObjectKey from PersonHasObject p1 where p1.UID_Person = @UID_Person union select @UID_Person, b1.ObjectKey6 from BaseTreeHasObject b1 where b1.UID_Org = @UID_Org   union select @UID_Person, b1.XObjectKey as ObjectKey from BaseTree b1 where b1.UID_Org = @UID_Org7 ) as pho on cp.uid_person = @UID_Person and pho.UID_Person = @UID_Person and pho.Objectkey = cm.ObjectKeyElement group by sr.UID_ComplianceRule, sr.UID_ComplianceSubRule8, sr.CountMatchesMin, pho.UID_Person having COUNT(cm.ObjectKeyElement) >= case sr.CountMatchesMin when 0 then 1 else sr.CountMatchesMin end ) as ps join9 compliancerule cr on ps.uid_complianceRule = cr.uid_complianceRule  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0   left outer join personinBaseTree 10pinc on pinc.uid_person = ps.uid_person and pinc.uid_org = cr.uid_noncompliance group by ps.uid_person, ps.uid_ComplianceRule, isnull(pinc.uid_org,'') 11, isnull(pinc.isexceptiongranted, 0) having count(distinct ps.uid_ComplianceSubRule) = (select count( * ) from ComplianceSubrule where uid_complianceRule12 = ps.uid_complianceRule ) ) as x join ComplianceRule cr on x.UID_ComplianceRule = cr.UID_ComplianceRule  and cr.IsInActive = 0 and cr.IsWorkingCopy = 130  ) 14