• State Suggested Answer
  • Replies 3 replies
  • Answers 2 answers
  • Subscribers 95 subscribers
  • Views 1963 views
  • Users 0 members are here
Options
Related

Attestation of PersonHasEset

paul love
over 3 years ago

I am trying to create an Attestation Policy for PersonHasEset, where the first approval comes from the manager of the Person.  It looks like the CM Approval procedure won't fulfill the needs of that workflow, so I have copied it and made a change to the SQL:

select hho.UID_PersonHead as UID_Person

, dbo.QER_FGIPWORulerOrigin(hho.XObjectKey) as UID_PWORulerOrigin

 from AttestationCase ac 

 join PersonHasEset pee on pee.XObjectKey = ac.ObjectKeyBase

 join Person pe on pee.UID_Person = pe.UID_Person

join HelperHeadPerson hho on hho.UID_Person = pe.UID_Person

and hho.XOrigin > 0

 where ac.UID_AttestationCase = @UID_AttestationCase

union

select hho.UID_PersonHead as UID_Person

, dbo.QER_FGIPWORulerOrigin(hho.XObjectKey) as UID_PWORulerOrigin

 from AttestationCase ac 

 join PersonHasEset pee on pee.XObjectKey = ac.ObjectKeyBase

 join PersonInBaseTree /*PersonInOrg*/   pe on pee.UID_Person = pe.UID_Person

join Person p on pe.UID_Person = p.UID_Person

join HelperHeadPerson hho on p.UID_Person = hho.UID_Person

and hho.XOrigin > 0

 where ac.UID_AttestationCase = @UID_AttestationCase

Now when I am trying to deny the System Role assignment in IT Shop, I get this error message:

Error during execution of statement: update AttestationCase set DateHead = '2021-05-27 14:31:31.990', DecisionLevel = 1, DisplayPersonHead = N'Paul Love', UID_PersonHead = 'd2632be2-2960-4e64-8709-0dc856828808', xdateupdated = GetUTCDate(), xuserupdated = N'JUST-EAT-DEV\d.paul.love' where (AttestationCase.UID_AttestationCase = '5e00022a-0d78-49ad-a7c8-99bfe094ebc4') and (isnull(AttestationCase.DateHead, '1899-12-30 00:00:00.000') = '1899-12-30 00:00:00.000') and (AttestationCase.DecisionLevel = 0) and (isnull(AttestationCase.DisplayPersonHead, N'') = N'') and (isnull(AttestationCase.UID_PersonHead, '') = '') 

Database error 50000: re-throw in Procedure ATT_PAttestationHelperFill, Line 125 

Database error 50000: detected in (SRV=AWS-MIM-DEV-03, DB=OneIM) Procedure GEN_T108BA2E4DE93DD613CD49280F, Line 73 

Database error 50000: Trigger referential integrity for AttestationHelper(Insert) 

50000 0 detected in (SRV=AWS-MIM-DEV-03, DB=OneIM) Procedure GEN_T108BA2E4DE93DD613CD49280F, Line 43 

Database error 50000: Cannot insert object in AttestationHelper because the associated object in Person does not exist. Rule ATT_RFRL2008 

(2021-05-27 15:31:32)

Can anyone suggest what is the error here please and how to resolve it?

  • 0 over 3 years ago

    Hi Paul,

    this SQL should do the trick:

    SELECT hho.UID_PersonHead AS uid_person
	,dbo.QER_FGIPWORulerOrigin(hho.XObjectKey) AS UID_PWORulerOrigin
FROM attestationCase ac
JOIN PersonHasEset phe ON ac.objectkeybase = phe.XObjectkey
	AND phe.XOrigin > 0
JOIN Person pe ON phe.UID_Person = pe.UID_Person
JOIN HelperHeadPerson hho ON hho.UID_Person = pe.UID_Person
	AND hho.XOrigin > 0
WHERE ac.UID_AttestationCase = @UID_AttestationCase

    And remember to assign the table PersonHasESet to your custom method.

  • 0 over 3 years ago in reply to Markus Weiss-Ehlers

    Hi Markus, thanks for the info - just got around to testing this - still having the same issue, but only when trying to deny the PersonHasEset when running the attestation... still getting this error message in the IT Shop

    An exception has occurred while executing the form method Popup0_Popup0_ControlRef2_Container6_Button1_Method. 

    Error flushing changes to database. 

    Error during execution of statement: update AttestationCase set DateHead = '2021-06-02 11:28:21.657', DecisionLevel = 1, DisplayPersonHead = N'Paul Love', UID_PersonHead = 'd2632be2-2960-4e64-8709-0dc856828808', xdateupdated = GetUTCDate(), xuserupdated = N'paul.love' where (AttestationCase.UID_AttestationCase = '62e77492-9427-4f8c-91db-aa1ac56e1a48') and (isnull(AttestationCase.DateHead, '1899-12-30 00:00:00.000') = '1899-12-30 00:00:00.000') and (AttestationCase.DecisionLevel = 0) and (isnull(AttestationCase.DisplayPersonHead, N'') = N'') and (isnull(AttestationCase.UID_PersonHead, '') = '') 

    Database error 50000: re-throw in Procedure ATT_PAttestationHelperFill, Line 125 

    Database error 50000: detected in (SRV=AWS-MIM-DEV-03, DB=OneIM) Procedure GEN_T108BA2E4DE93DD613CD49280F, Line 73 

    Database error 50000: Trigger referential integrity for AttestationHelper(Insert) 

    50000 0 detected in (SRV=AWS-MIM-DEV-03, DB=OneIM) Procedure GEN_T108BA2E4DE93DD613CD49280F, Line 43 

    Database error 50000: Cannot insert object in AttestationHelper because the associated object in Person does not exist. Rule ATT_RFRL2008 

    (2021-06-02 12:28:22)