dbo.QER_PPersonDelete_GDPR

1  create   procedure QER_PPersonDelete_GDPR (@UID_person varchar(38) ) as begin declare @ObjectKeyPerson varchar(138) declare @ElementsToClose QBM_YParameterList
2 declare @GenprocID varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @Reason nvarchar(1000) = dbo.QBM_FCVStringTrimLDSPrefix
3('#LDS#Automatic system approval: Assigned Element was deleted or workflow has changed.|') declare @cmd nvarchar(max) declare @JobqueueBusy int = 1 declare
4 @Waittime int = 5 declare @maxtry int = 5  * 60  / @Waittime declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex 
5int declare @AccountTablename varchar(30) declare @AccountObjectkey varchar(138) declare @AccountWhereclause nvarchar(max) declare @XUser nvarchar(64) 
6= object_name(@@procid) declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY set nocount on exec QBM_PSessionContextSet 'GenProcID', @GenprocID
7 select top 1 @ObjectKeyPerson = p.XObjectKey from Person p where p.UID_Person = @UID_person if @@ROWCOUNT = 0 begin goto EndLabel end update PersonWantsOrg
8 set DisplayPersonInserted = null  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_PersonInserted = @UID_person update PersonWantsOrg set DisplayPersonHead
9 = null  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_PersonHead = @UID_person if exists (select top 1 1 from sys.tables t where t.name = 
10'AttestationCase' ) begin select @cmd = '	update AttestationCase 
11	set DisplayPersonHead = null
12		, XDateUpdated = getutcdate(), XUserUpdated = ''QER_PPersonDelete_GDPR'' 
13	where UID_PersonHead = '''
14 + @UID_person + '''
15
16	' exec sp_executesql @cmd end delete  @ElementsToClose  if exists (select top 1 1 from sys.tables t where t.name = 'AttestationCase'
17 ) begin select @cmd = '	select a.XObjectKey
18		from person p join AttestationCase a on p.XObjectKey in (a.ObjectKey1, a.ObjectKey2, a.ObjectKey3, a.ObjectKeyBase)
19		where  p.UID_Person = '''
20 + @uid_person + '''
21			and a.IsClosed = 0
22	' insert into @ElementsToClose(Parameter1) exec sp_executesql @cmd end exec QBM_PJobCreate_HOCallMethod_L
23 @objecttype = 'AttestationCase' , @XObjectKeys = @ElementsToClose , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @AdditionalObjectKeysAffected
24 = DEFAULT , @param1 = '#LDS#Automatic system approval: object to attest does not exist.|' , @Retries = 3 , @isToFreezeOnError = 1 , @checkForExisting 
25= 1 , @WhereClauseAdditional = ' IsClosed = 0 '   if exists (select top 1 1 from sys.tables t where t.name = 'TroubleWorkTimes' ) begin select @cmd = '	update TroubleWorkTimes
26		set DisplayPersonInserted = null
27		where UID_PersonInserted = '''
28 + @uid_person + '''
29	' exec sp_executesql @cmd end if exists (select top 1 1 from sys.tables t where t.name = 'QERPolicyHasObject' ) begin select @cmd
30 = '	delete QERPolicyHasObject
31		where ObjectKey = ''' + @ObjectKeyPerson + '''
32	' exec sp_executesql @cmd end delete QBMLock where ObjectKeyOfRow = 
33@ObjectKeyPerson  delete  @ElementsToClose insert into @ElementsToClose(Parameter1) select pwo.XObjectKey from person p join PersonWantsOrg pwo on p.XObjectKey
34 in (pwo.ObjectKeyElementUsedInAssign) or pwo.UID_PersonOrdered = p.UID_Person or exists (select top 1 1 from Delegation d where d.UID_PersonWantsOrg =
35 pwo.UID_PersonWantsOrg and p.UID_Person in ( d.UID_PersonReceiver, d.UID_PersonSender) )    where p.UID_Person = @uid_person and pwo.Orderstate = 'OrderProduct'
36   exec QBM_PJobCreate_HOCallMethod_L @objecttype = 'PersonWantsOrg' , @XObjectKeys = @ElementsToClose , @save = 1 , @MethodName = 'Abort' , @GenProcID
37 = @GenprocID , @AdditionalObjectKeysAffected = DEFAULT , @param1 = @reason , @Retries = 3 , @isToFreezeOnError = 1 , @checkForExisting = 1 delete  @ElementsToClose
38  insert into @ElementsToClose(Parameter1) select pwo.XObjectKey from person p join PersonWantsOrg pwo on p.XObjectKey in (pwo.ObjectKeyElementUsedInAssign
39) or pwo.UID_PersonOrdered = p.UID_Person or exists (select top 1 1 from Delegation d where d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg and p.UID_Person
40 in ( d.UID_PersonReceiver, d.UID_PersonSender) )    where p.UID_Person = @uid_person  and pwo.OrderState in ('Assigned', 'Granted', 'New',  'OrderProlongate'
41, 'OrderUnsubscribe', 'Waiting') exec QBM_PJobCreate_HOCallMethod_L @objecttype = 'PersonWantsOrg' , @XObjectKeys = @ElementsToClose , @save = 1 , @MethodName
42 = 'Abort' , @GenProcID = @GenprocID , @AdditionalObjectKeysAffected = DEFAULT , @param1 = @reason , @Retries = 3 , @isToFreezeOnError = 1 , @checkForExisting
43 = 1  if dbo.QBM_FGIModuleExists('TSB') = 1 begin select @cmd = '
44	update PersonHasTSBAccountDef
45		set XOrigin = 0
46		where UID_person = ''' + @UID_person
47 + '''
48	' exec sp_executesql @cmd select @cmd = '
49	delete PersonHasTSBAccountDef
50		where UID_person = ''' + @UID_person + '''
51	' exec sp_executesql
52 @cmd select @cmd = '
53	select a.XObjectKey, dbo.QBM_FCVObjectkeyToElement(''tablename'', a.XObjectKey)	
54		from UNSAccount a
55		where a.UID_Person = '''
56 + @UID_person + '''
57	' insert into @ElementBuffer(ObjectKey1, Ident1) exec sp_executesql @cmd select @ElementCount = @@ROWCOUNT select @ElementIndex 
58= 1 while @ElementIndex <= @ElementCount begin select top 1 @AccountTablename = bu.Ident1 , @AccountObjectkey = bu.ObjectKey1 , @AccountWhereclause = 'XObjectKey = '''
59 + bu.ObjectKey1 + '''' from @ElementBuffer bu where bu.ElementIndex = @ElementIndex exec QBM_PJobCreate_HODelete @objecttype = @AccountTablename , @whereclause
60 = @AccountWhereclause , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @isToFreezeOnError = 1 , @checkForExisting = 1 , @DeepDelete = 1 select
61 @ElementIndex += 1 end  end  while @JobqueueBusy > 0 and @maxtry > 0 begin if not exists (select top 1 1 from JobQueue q where q.GenProcID = @GenprocID
62 and q.Ready2EXE like N'[^DH][^I]%'  ) begin select @JobqueueBusy = 0 end if @JobqueueBusy > 0 begin exec QBM_PWaitForSeconds @Waittime end select @maxtry
63 -= 1 end exec QBM_PDeleteDeep @ObjectKeyPerson END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
64() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH EndLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R  return end 
65