Back to OIM Explorer

dbo.CPL_FTCCSPWOCheck

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 4.757 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_FTCCSPersonHasObjectWPWO source text reference

References

Referenced By

Complete Source

SQL155 lines
1CREATE FUNCTION dbo.CPL_FTCCSPWOCheck(2  @uid_personwantsorg varchar(38)3) RETURNS @erg TABLE(UID_PersonWantsOrg varchar(38) collate database_default,4UID_Person varchar(38) collate database_default,5UID_ComplianceRule varchar(38) collate database_default,6UID_NonCompliance varchar(38) collate database_default,7IsExceptionGranted BIT,8IsNewObject BIT,9IsNewRule BIT10)11AS12BEGIN13  DECLARE @PersonsPossible QBM_YParameterList14  INSERT INTO @PersonsPossible(Parameter1)15  SELECT me.UID_PersonAlsoMe16  FROM HelperPWOPersonHasObject pho17  JOIN QER_VPersonsAreMe me18    ON pho.UID_Person = me.UID_PersonOrigin19  WHERE20    pho.uid_personwantsorg = @uid_personwantsorg21  UNION22  SELECT pho.UID_Person23  FROM HelperPWOPersonHasObject pho24  WHERE25    pho.uid_personwantsorg = @uid_personwantsorg26  DECLARE @ComRuleforPerson QBM_YParameterList27  INSERT INTO @ComRuleforPerson(Parameter1,28  Parameter2)29  SELECT30    per.Parameter1,31    csrp.UID_ComplianceRule32  FROM @PersonsPossible per33  JOIN ComplianceSubRulePerson csrp34    WITH(readpast,35  forceSeek)36    ON per.Parameter1 = csrp.UID_Person37  JOIN ComplianceRule cr38    WITH(readpast,39  forceSeek)40    ON csrp.UID_ComplianceRule = cr.UID_ComplianceRule AND cr.IsPersonStoreInverted = 0 AND cr.IsWorkingCopy = 0 AND cr.IsInActive = 041  UNION42  SELECT43    per.Parameter1,44    cr.UID_ComplianceRule45  FROM @PersonsPossible per46  JOIN ComplianceRule cr47    ON cr.IsPersonStoreInverted = 1 AND cr.IsWorkingCopy = 0 AND cr.IsInActive = 048  LEFT49  OUTER50  JOIN ComplianceSubRulePerson csrp51    WITH(readpast,52  forceSeek)53    ON per.Parameter1 = csrp.UID_Person AND csrp.UID_ComplianceRule = cr.UID_ComplianceRule54  WHERE55    csrp.UID_Person IS NULL56  DECLARE @InnerData TABLE(UID_ComplianceRule VARCHAR(38) collate database_default,57  UID_ComplianceSubRule VARCHAR(38) collate database_default,58  UID_Person VARCHAR(38) collate database_default,59  IsNew int,60  CountMatchesMin int,61  ObjectKeyElement VARCHAR(138) collate database_default)62  INSERT INTO @innerData(UID_ComplianceRule,63  UID_ComplianceSubRule,64  UID_Person,65  IsNew,66  CountMatchesMin,67  ObjectKeyElement)68  SELECT69    sr.UID_ComplianceRule,70    sr.UID_ComplianceSubRule,71    pho.UID_Person,72    pho.IsNew,73    sr.CountMatchesMin,74    cm.ObjectKeyElement75  FROM @ComRuleforPerson ptoc76  JOIN ComplianceRule cr77    ON ptoc.Parameter2 = cr.UID_ComplianceRule AND cr.IsCrossPersonCheck = 178  JOIN ComplianceSubRule sr79    ON cr.UID_ComplianceRule = sr.UID_ComplianceRule80  JOIN ComplianceSubRuleObject cm81    ON sr.UID_ComplianceSubRule = cm.UID_ComplianceSubRule82  JOIN dbo.CPL_FTCCSPersonHasObjectWPWO(@UID_PersonWantsOrg,83  1) pho84    ON pho.uid_person = ptoc.Parameter1 AND pho.Objectkey = cm.ObjectKeyElement AND(pho.isNew = 1 AND pho.uid_personwantsorg = @uid_personwantsorg OR85  pho.isNew = 0)86  UNION ALL87  SELECT88    sr.UID_ComplianceRule,89    sr.UID_ComplianceSubRule,90    pho.UID_Person,91    pho.IsNew,92    sr.CountMatchesMin,93    cm.ObjectKeyElement94  FROM @ComRuleforPerson ptoc95  JOIN ComplianceRule cr96    ON ptoc.Parameter2 = cr.UID_ComplianceRule AND cr.IsCrossPersonCheck = 097  JOIN ComplianceSubRule sr98    ON cr.UID_ComplianceRule = sr.UID_ComplianceRule99  JOIN ComplianceSubRuleObject cm100    ON sr.UID_ComplianceSubRule = cm.UID_ComplianceSubRule101  JOIN dbo.CPL_FTCCSPersonHasObjectWPWO(@UID_PersonWantsOrg,102  0) pho103    ON pho.uid_person = ptoc.Parameter1 AND pho.Objectkey = cm.ObjectKeyElement AND(pho.isNew = 1 AND pho.uid_personwantsorg = @uid_personwantsorg OR104  pho.isNew = 0)105  INSERT INTO @erg(UID_PersonWantsOrg,106  UID_Person,107  UID_ComplianceRule,108  UID_NonCompliance,109  IsExceptionGranted,110  IsNewObject,111  IsNewRule)112  SELECT113    @UID_PersonWantsOrg AS uid_personwantsOrg,114    yy.uid_person,115    yy.uid_ComplianceRule,116    yy.uid_noncompliance,117    yy.isExceptionGranted,118    max(yy.isNewObject) AS isNewObject,119    max(yy.isNewRule) AS isNewRule120  FROM(121  SELECT122    ps.uid_person,123    ps.uid_ComplianceRule,124    isnull(pinc.uid_org, '') AS uid_noncompliance,125    isnull(pinc.isexceptiongranted, 0) AS isExceptionGranted,126    ps.isNew AS isNewObject,127    (sign(len(isnull(pinc.uid_org, ''))) ^1) & ps.isNew AS isNewRule,128    ps.uid_complianceSubrule129  FROM(130  SELECT131    y.UID_ComplianceRule, y.UID_ComplianceSubRule, y.UID_Person, MAX(y.IsNew) AS IsNew132  FROM @InnerData AS y133  GROUP BY y.UID_ComplianceRule, y.UID_ComplianceSubRule, y.CountMatchesMin, y.UID_Person134  HAVING COUNT(y.ObjectKeyElement) >= CASE y.CountMatchesMin135  WHEN 0 THEN136  1137  ELSE y.CountMatchesMin138  END) AS ps139  JOIN compliancerule cr140    ON ps.uid_complianceRule = cr.uid_complianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0141  LEFT142  OUTER143  JOIN PersonInBaseTree pinc144    ON pinc.UID_Person = ps.UID_Person AND pinc.UID_Org = cr.uid_noncompliance) AS yy145  GROUP BY yy.uid_person,146  yy.uid_ComplianceRule,147  yy.uid_noncompliance,148  yy.isexceptiongranted149  HAVING count(DISTINCT yy.uid_ComplianceSubRule) =(150  SELECT count(*)151  FROM ComplianceSubrule152  WHERE153    uid_complianceRule = yy.uid_complianceRule)154  RETURN155END
Open raw exported source
SQL ยท Raw31 lines
1 create function dbo.CPL_FTCCSPWOCheck(@uid_personwantsorg varchar(38)) returns @erg table ( UID_PersonWantsOrg varchar(38) collate database_default , 2UID_Person varchar(38) collate database_default , UID_ComplianceRule varchar(38) collate database_default , UID_NonCompliance varchar(38) collate database_default3 , IsExceptionGranted bit , IsNewObject bit , IsNewRule bit ) as begin declare @PersonsPossible QBM_YParameterList insert into @PersonsPossible (Parameter14 ) select me.UID_PersonAlsoMe from HelperPWOPersonHasObject pho join QER_VPersonsAreMe me on pho.UID_Person = me.UID_PersonOrigin where pho.uid_personwantsorg5 = @uid_personwantsorg union select pho.UID_Person from HelperPWOPersonHasObject pho where pho.uid_personwantsorg = @uid_personwantsorg declare @ComRuleforPerson6 QBM_YParameterList  insert into @ComRuleforPerson (Parameter1, Parameter2) select per.Parameter1, csrp.UID_ComplianceRule from @PersonsPossible per join7 ComplianceSubRulePerson csrp with (readpast, forceSeek) on per.Parameter1 = csrp.UID_Person join ComplianceRule cr with (readpast, forceSeek) on csrp.UID_ComplianceRule8 = cr.UID_ComplianceRule and cr.IsPersonStoreInverted = 0 and cr.IsWorkingCopy = 0 and cr.IsInActive = 0 union select per.Parameter1, cr.UID_ComplianceRule9 from @PersonsPossible per join ComplianceRule cr on cr.IsPersonStoreInverted = 1 and cr.IsWorkingCopy = 0 and cr.IsInActive = 0 left outer join ComplianceSubRulePerson10 csrp with (readpast, forceSeek) on per.Parameter1 = csrp.UID_Person and csrp.UID_ComplianceRule = cr.UID_ComplianceRule where csrp.UID_Person is null 11 DECLARE @InnerData TABLE ( UID_ComplianceRule VARCHAR(38) collate database_default ,UID_ComplianceSubRule VARCHAR(38) collate database_default ,UID_Person12 VARCHAR(38) collate database_default ,IsNew int ,CountMatchesMin int ,ObjectKeyElement VARCHAR(138) collate database_default ) INSERT INTO @innerData(UID_ComplianceRule13, UID_ComplianceSubRule, UID_Person, IsNew, CountMatchesMin, ObjectKeyElement) SELECT sr.UID_ComplianceRule, sr.UID_ComplianceSubRule, pho.UID_Person, 14pho.IsNew, sr.CountMatchesMin, cm.ObjectKeyElement FROM @ComRuleforPerson ptoc JOIN ComplianceRule cr on ptoc.Parameter2 = cr.UID_ComplianceRule AND cr.IsCrossPersonCheck15 = 1 JOIN ComplianceSubRule sr on cr.UID_ComplianceRule = sr.UID_ComplianceRule JOIN ComplianceSubRuleObject cm on sr.UID_ComplianceSubRule = cm.UID_ComplianceSubRule16 JOIN dbo.CPL_FTCCSPersonHasObjectWPWO(@UID_PersonWantsOrg, 1) pho ON pho.uid_person = ptoc.Parameter1  AND pho.Objectkey = cm.ObjectKeyElement AND ( pho.isNew17 = 1 AND pho.uid_personwantsorg = @uid_personwantsorg OR pho.isNew = 0 ) UNION ALL SELECT sr.UID_ComplianceRule, sr.UID_ComplianceSubRule, pho.UID_Person18, pho.IsNew, sr.CountMatchesMin, cm.ObjectKeyElement FROM @ComRuleforPerson ptoc JOIN ComplianceRule cr on ptoc.Parameter2 = cr.UID_ComplianceRule AND 19cr.IsCrossPersonCheck = 0 JOIN ComplianceSubRule sr on cr.UID_ComplianceRule = sr.UID_ComplianceRule JOIN ComplianceSubRuleObject cm on sr.UID_ComplianceSubRule20 = cm.UID_ComplianceSubRule JOIN dbo.CPL_FTCCSPersonHasObjectWPWO(@UID_PersonWantsOrg, 0) pho ON pho.uid_person = ptoc.Parameter1  AND pho.Objectkey = 21cm.ObjectKeyElement AND ( pho.isNew = 1 AND pho.uid_personwantsorg = @uid_personwantsorg OR pho.isNew = 0 )  insert into @erg(UID_PersonWantsOrg , UID_Person22 , UID_ComplianceRule , UID_NonCompliance , IsExceptionGranted , IsNewObject , IsNewRule) select @UID_PersonWantsOrg as uid_personwantsOrg, yy.uid_person23, yy.uid_ComplianceRule, yy.uid_noncompliance, yy.isExceptionGranted, max(yy.isNewObject) as isNewObject, max(yy.isNewRule) as isNewRule from ( select 24 ps.uid_person, ps.uid_ComplianceRule, isnull(pinc.uid_org,'') as uid_noncompliance, isnull(pinc.isexceptiongranted, 0) as isExceptionGranted, ps.isNew25 as isNewObject, (sign(len(isnull(pinc.uid_org,''))) ^1 ) & ps.isNew as isNewRule, ps.uid_complianceSubrule  from ( select y.UID_ComplianceRule, y.UID_ComplianceSubRule26, y.UID_Person, MAX(y.IsNew) as IsNew    FROM @InnerData as y  group by y.UID_ComplianceRule, y.UID_ComplianceSubRule, y.CountMatchesMin, y.UID_Person 27having COUNT(y.ObjectKeyElement) >= case y.CountMatchesMin when 0 then 1 else y.CountMatchesMin end ) as ps join compliancerule cr on ps.uid_complianceRule28 = cr.uid_complianceRule  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0   left outer join PersonInBaseTree pinc on pinc.UID_Person = ps.UID_Person and29 pinc.UID_Org = cr.uid_noncompliance ) as yy group by  yy.uid_person, yy.uid_ComplianceRule, yy.uid_noncompliance , yy.isexceptiongranted having count(distinct30 yy.uid_ComplianceSubRule) = (select count( * ) from ComplianceSubrule where uid_complianceRule = yy.uid_complianceRule ) return end 31