Back to OIM Explorer

dbo.QER_PPersonDelete_GDPR

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOCallMethod -> AttestationCase.Abort at line 22; HOCallMethod -> PersonWantsOrg.Abort at line 22; HOCallMethod -> PersonWantsOrg.Abort at line 22; HOCallMethod -> PersonWantsOrg.Abort at line 36

Source: sandbox-db sys.sql_modules

Source size: 6.318 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • Object-layer bridge detected through QBM_PJobCreate helper usage.

Relations

  • HOCallMethod -> AttestationCase.Abort at line 22
  • HOCallMethod -> PersonWantsOrg.Abort at line 22
  • HOCallMethod -> PersonWantsOrg.Abort at line 22
  • HOCallMethod -> PersonWantsOrg.Abort at line 36
  • HOCallMethod -> PersonWantsOrg.Abort at line 36
  • HOCallMethod -> PersonWantsOrg.Abort at line 41
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*

Typed Edges

  • calls object method AttestationCase.Abort HOCallMethod -> AttestationCase.Abort at line 22
  • calls object method PersonWantsOrg.Abort HOCallMethod -> PersonWantsOrg.Abort at line 22
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FCVStringTrimLDSPrefix source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PDeleteDeep source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod_L source text reference
  • references source dbo.QBM_PJobCreate_HODelete source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PWaitForSeconds source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL247 lines
1CREATE PROCEDURE QER_PPersonDelete_GDPR(2  @UID_person varchar(38)3)4AS5BEGIN6  DECLARE @ObjectKeyPerson varchar(138)7  DECLARE @ElementsToClose QBM_YParameterList8  DECLARE @GenprocID varchar(38) = newid()9  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')10  DECLARE @Reason nvarchar(1000) = dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval: Assigned Element was deleted or workflow has changed.|'11  )12  DECLARE @cmd nvarchar(max)13  DECLARE @JobqueueBusy int = 114  DECLARE @Waittime int = 515  DECLARE @maxtry int = 5 * 60 / @Waittime16  DECLARE @ElementBuffer QBM_YCursorBuffer17  DECLARE @ElementCount int18  DECLARE @ElementIndex int19  DECLARE @AccountTablename varchar(30)20  DECLARE @AccountObjectkey varchar(138)21  DECLARE @AccountWhereclause nvarchar(max)22  DECLARE @XUser nvarchar(64) = object_name(@@procid)23  DECLARE @Xdate datetime = getutcdate()24  SET XACT_ABORT OFF25  BEGIN TRY26    SET nocount27      ON28    EXEC QBM_PSessionContextSet 'GenProcID',29      @GenprocID30    SELECT TOP 1 @ObjectKeyPerson = p.XObjectKey31    FROM Person p32    WHERE33      p.UID_Person = @UID_person34    IF @@ROWCOUNT = 035    BEGIN36      GOTO EndLabel37    END38    UPDATE PersonWantsOrg39    SET DisplayPersonInserted = NULL,40    XDateUpdated = @Xdate,41    XUserUpdated = @XUser42    WHERE43      UID_PersonInserted = @UID_person44    UPDATE PersonWantsOrg45    SET DisplayPersonHead = NULL,46    XDateUpdated = @Xdate,47    XUserUpdated = @XUser48    WHERE49      UID_PersonHead = @UID_person50    IF EXISTS(51      SELECT TOP 1 152      FROM sys.tables t53      WHERE54        t.name = 'AttestationCase')55    BEGIN56      SELECT57        @cmd = '	update AttestationCase 58	set DisplayPersonHead = null59		, XDateUpdated = getutcdate(), XUserUpdated = ''QER_PPersonDelete_GDPR'' 60	where UID_PersonHead = '''61        + @UID_person + '''6263	'64      EXEC sp_executesql @cmd65    END66    DELETE @ElementsToClose67    IF EXISTS(68      SELECT TOP 1 169      FROM sys.tables t70      WHERE71        t.name = 'AttestationCase')72    BEGIN73      SELECT74        @cmd = '	select a.XObjectKey75		from person p join AttestationCase a on p.XObjectKey in (a.ObjectKey1, a.ObjectKey2, a.ObjectKey3, a.ObjectKeyBase)76		where  p.UID_Person = '''77        + @uid_person + '''78			and a.IsClosed = 079	'80      INSERT INTO @ElementsToClose(Parameter1)81      EXEC sp_executesql @cmd82    END83    EXEC QBM_PJobCreate_HOCallMethod_L @objecttype = 'AttestationCase',84      @XObjectKeys = @ElementsToClose,85      @save = 1,86      @MethodName = 'Abort',87      @GenProcID = @GenprocID,88      @AdditionalObjectKeysAffected = DEFAULT,89      @param1 = '#LDS#Automatic system approval: object to attest does not exist.|',90      @Retries = 3,91      @isToFreezeOnError = 1,92      @checkForExisting = 1,93      @WhereClauseAdditional = ' IsClosed = 0 '94    IF EXISTS(95      SELECT TOP 1 196      FROM sys.tables t97      WHERE98        t.name = 'TroubleWorkTimes')99    BEGIN100      SELECT101        @cmd = '	update TroubleWorkTimes102		set DisplayPersonInserted = null103		where UID_PersonInserted = ''' + @uid_person + '''104	'105      EXEC sp_executesql @cmd106    END107    IF EXISTS(108      SELECT TOP 1 1109      FROM sys.tables t110      WHERE111        t.name = 'QERPolicyHasObject')112    BEGIN113      SELECT114        @cmd = '	delete QERPolicyHasObject115		where ObjectKey = ''' + @ObjectKeyPerson + '''116	'117      EXEC sp_executesql @cmd118    END119    DELETE QBMLock120    WHERE121      ObjectKeyOfRow = @ObjectKeyPerson122    DELETE @ElementsToClose123    INSERT INTO @ElementsToClose(Parameter1)124    SELECT pwo.XObjectKey125    FROM person p126    JOIN PersonWantsOrg pwo127      ON p.XObjectKey IN(pwo.ObjectKeyElementUsedInAssign) OR pwo.UID_PersonOrdered = p.UID_Person OR EXISTS(128    SELECT TOP 1 1129    FROM Delegation d130    WHERE131      d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND p.UID_Person IN(d.UID_PersonReceiver, d.UID_PersonSender))132    WHERE133      p.UID_Person = @uid_person AND pwo.Orderstate = 'OrderProduct'134    EXEC QBM_PJobCreate_HOCallMethod_L @objecttype = 'PersonWantsOrg',135      @XObjectKeys = @ElementsToClose,136      @save = 1,137      @MethodName = 'Abort',138      @GenProcID = @GenprocID,139      @AdditionalObjectKeysAffected = DEFAULT,140      @param1 = @reason,141      @Retries = 3,142      @isToFreezeOnError = 1,143      @checkForExisting = 1144    DELETE @ElementsToClose145    INSERT INTO @ElementsToClose(Parameter1)146    SELECT pwo.XObjectKey147    FROM person p148    JOIN PersonWantsOrg pwo149      ON p.XObjectKey IN(pwo.ObjectKeyElementUsedInAssign) OR pwo.UID_PersonOrdered = p.UID_Person OR EXISTS(150    SELECT TOP 1 1151    FROM Delegation d152    WHERE153      d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg AND p.UID_Person IN(d.UID_PersonReceiver, d.UID_PersonSender))154    WHERE155      p.UID_Person = @uid_person AND pwo.OrderState IN('Assigned',156    'Granted',157    'New',158    'OrderProlongate',159    'OrderUnsubscribe',160    'Waiting')161    EXEC QBM_PJobCreate_HOCallMethod_L @objecttype = 'PersonWantsOrg',162      @XObjectKeys = @ElementsToClose,163      @save = 1,164      @MethodName = 'Abort',165      @GenProcID = @GenprocID,166      @AdditionalObjectKeysAffected = DEFAULT,167      @param1 = @reason,168      @Retries = 3,169      @isToFreezeOnError = 1,170      @checkForExisting = 1171    IF dbo.QBM_FGIModuleExists('TSB') = 1172    BEGIN173      SELECT174        @cmd = '175	update PersonHasTSBAccountDef176		set XOrigin = 0177		where UID_person = ''' + @UID_person + '''178	'179      EXEC sp_executesql @cmd180      SELECT181        @cmd = '182	delete PersonHasTSBAccountDef183		where UID_person = ''' + @UID_person + '''184	'185      EXEC sp_executesql @cmd186      SELECT187        @cmd = '188	select a.XObjectKey, dbo.QBM_FCVObjectkeyToElement(''tablename'', a.XObjectKey)	189		from UNSAccount a190		where a.UID_Person = '''191        + @UID_person + '''192	'193      INSERT INTO @ElementBuffer(ObjectKey1,194      Ident1)195      EXEC sp_executesql @cmd196      SELECT @ElementCount = @@ROWCOUNT197      SELECT @ElementIndex = 1198      WHILE @ElementIndex <= @ElementCount199      BEGIN200        SELECT201          TOP 1 @AccountTablename = bu.Ident1,202          @AccountObjectkey = bu.ObjectKey1,203          @AccountWhereclause = 'XObjectKey = ''' + bu.ObjectKey1 + ''''204        FROM @ElementBuffer bu205        WHERE206          bu.ElementIndex = @ElementIndex207        EXEC QBM_PJobCreate_HODelete @objecttype = @AccountTablename,208          @whereclause = @AccountWhereclause,209          @GenProcID = @GenprocID,210          @ObjectKeysAffected = DEFAULT,211          @isToFreezeOnError = 1,212          @checkForExisting = 1,213          @DeepDelete = 1214        SELECT @ElementIndex += 1215      END216    END217    WHILE @JobqueueBusy > 0 AND @maxtry > 0218    BEGIN219      IF NOT EXISTS(220        SELECT TOP 1 1221        FROM JobQueue q222        WHERE223          q.GenProcID = @GenprocID AND q.Ready2EXE LIKE N '[^DH][^I]%')224      BEGIN225        SELECT @JobqueueBusy = 0226      END227      IF @JobqueueBusy > 0228      BEGIN229        EXEC QBM_PWaitForSeconds @Waittime230      END231      SELECT @maxtry -= 1232    END233    EXEC QBM_PDeleteDeep @ObjectKeyPerson234  END TRY235  BEGIN CATCH236    EXEC QBM_PSessionErrorAdd DEFAULT237    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()238    RAISERROR(@Rethrow,239    18,240    1)241      WITH NOWAIT242  END CATCH243  EndLabel:244  EXEC QBM_PSessionContextSet 'GenProcID',245    @GenProcID_R246  RETURN247END
Open raw exported source
SQL ยท Raw65 lines
1  create   procedure QER_PPersonDelete_GDPR (@UID_person varchar(38) ) as begin declare @ObjectKeyPerson varchar(138) declare @ElementsToClose QBM_YParameterList2 declare @GenprocID varchar(38) = newid() declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @Reason nvarchar(1000) = dbo.QBM_FCVStringTrimLDSPrefix3('#LDS#Automatic system approval: Assigned Element was deleted or workflow has changed.|') declare @cmd nvarchar(max) declare @JobqueueBusy int = 1 declare4 @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', @GenprocID7 select top 1 @ObjectKeyPerson = p.XObjectKey from Person p where p.UID_Person = @UID_person if @@ROWCOUNT = 0 begin goto EndLabel end update PersonWantsOrg8 set DisplayPersonInserted = null  , XDateUpdated = @Xdate , XUserUpdated = @XUser where UID_PersonInserted = @UID_person update PersonWantsOrg set DisplayPersonHead9 = 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 = null12		, XDateUpdated = getutcdate(), XUserUpdated = ''QER_PPersonDelete_GDPR'' 13	where UID_PersonHead = '''14 + @UID_person + '''1516	' 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.XObjectKey18		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 = 022	' insert into @ElementsToClose(Parameter1) exec sp_executesql @cmd end exec QBM_PJobCreate_HOCallMethod_L23 @objecttype = 'AttestationCase' , @XObjectKeys = @ElementsToClose , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @AdditionalObjectKeysAffected24 = 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 TroubleWorkTimes26		set DisplayPersonInserted = null27		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 @cmd30 = '	delete QERPolicyHasObject31		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.XObjectKey34 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' , @GenProcID37 = @GenprocID , @AdditionalObjectKeysAffected = DEFAULT , @param1 = @reason , @Retries = 3 , @isToFreezeOnError = 1 , @checkForExisting = 1 delete  @ElementsToClose38  insert into @ElementsToClose(Parameter1) select pwo.XObjectKey from person p join PersonWantsOrg pwo on p.XObjectKey in (pwo.ObjectKeyElementUsedInAssign39) 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_Person40 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 , @MethodName42 = 'Abort' , @GenProcID = @GenprocID , @AdditionalObjectKeysAffected = DEFAULT , @param1 = @reason , @Retries = 3 , @isToFreezeOnError = 1 , @checkForExisting43 = 1  if dbo.QBM_FGIModuleExists('TSB') = 1 begin select @cmd = '44	update PersonHasTSBAccountDef45		set XOrigin = 046		where UID_person = ''' + @UID_person47 + '''48	' exec sp_executesql @cmd select @cmd = '49	delete PersonHasTSBAccountDef50		where UID_person = ''' + @UID_person + '''51	' exec sp_executesql52 @cmd select @cmd = '53	select a.XObjectKey, dbo.QBM_FCVObjectkeyToElement(''tablename'', a.XObjectKey)	54		from UNSAccount a55		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 , @whereclause60 = @AccountWhereclause , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @isToFreezeOnError = 1 , @checkForExisting = 1 , @DeepDelete = 1 select61 @ElementIndex += 1 end  end  while @JobqueueBusy > 0 and @maxtry > 0 begin if not exists (select top 1 1 from JobQueue q where q.GenProcID = @GenprocID62 and q.Ready2EXE like N'[^DH][^I]%'  ) begin select @JobqueueBusy = 0 end if @JobqueueBusy > 0 begin exec QBM_PWaitForSeconds @Waittime end select @maxtry63 -= 1 end exec QBM_PDeleteDeep @ObjectKeyPerson END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow64() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH EndLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R  return end 65