Back to OIM Explorer

dbo.QER_ZITShopMakeDecisionCD

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. HOCallMethod -> personwantsorg.MakeDecision at line 34; References QBM_PJobCreate*; References QBM_PJobCreate_HOCallMethod*

Source: sandbox-db sys.sql_modules

Source size: 4.228 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 -> personwantsorg.MakeDecision at line 34
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*

Typed Edges

  • calls object method personwantsorg.MakeDecision HOCallMethod -> personwantsorg.MakeDecision at line 34
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QBM_PJobCreate source text reference
  • references source dbo.QBM_PJobCreate_HOCallMethod source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL180 lines
1CREATE PROCEDURE QER_ZITShopMakeDecisionCD(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @UID_Personwantsorg varchar(38)7  DECLARE @GenProcID varchar(38)8  DECLARE @Decision nvarchar(16)9  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))10  DECLARE @Reason nvarchar(255)11  DECLARE @uid_person varchar(38)12  DECLARE @cond nvarchar(max)13  DECLARE @whereclause nvarchar(max)14  DECLARE @SQLcmd nvarchar(max)15  DECLARE @muster nvarchar(max)16  DECLARE @BasisObjectKey varchar(138)17  DECLARE @CountOfRows int18  DECLARE @DecisionLevel int19  DECLARE @ElementBuffer QBM_YCursorBuffer20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent23  SET XACT_ABORT OFF24  BEGIN TRY25    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,26    UID_Parameter,27    UID_SubParameter,28    GenProcID)29    SELECT30      UID_DialogDBQueue,31      UID_Parameter,32      UID_SubParameter,33      GenProcID34    FROM QBMDBQueueCurrent cu35      WITH(readpast)36    WHERE37      cu.SlotNumber = @SlotNumber38    IF @@rowcount = 039    BEGIN40      GOTO EndLabel41    END42    SELECT43      @muster = N '44if exists (select top 1 145			 from Personwantsorg46				where (47-- die Kundenklausel48@whereclause49						)50				and uid_personwantsorg = ''@uid_personwantsorg''51			)52 begin53	select 154 end55'56    DECLARE @AutomaticReasonTrue nvarchar(128)57    DECLARE @AutomaticReasonFalse nvarchar(128)58    INSERT INTO @ElementBuffer(UID1,59    UID2,60    ContentFull,61    LongIdent1,62    LongIdent2,63    ObjectKey1,64    Int1)65    SELECT66      pwo.uid_personwantsorg,67      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,68      p.GenProcID,69      @CfgUseGenProcID) AS GenProcID,70      isnull(ds.WhereClause,71      N ''),72      isnull(ds.AutomaticReasonTrue,73      ''),74      isnull(ds.AutomaticReasonFalse,75      ''),76      pwo.XObjectKey,77      pwo.DecisionLevel78    FROM @DBQueueCurrent p79    JOIN personwantsorg pwo80      ON p.uid_parameter = pwo.uid_personwantsorg81    JOIN PWOHelperPWO ph82      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber83    JOIN QERWorkingStep ds84      ON ph.UID_QERWorkingStep = ds.UID_QERWorkingStep85    WHERE86      ph.UID_PWODecisionRule = 'QER-PWODecisionRule-CD' AND OrderState IN('OrderProduct',87    'OrderProlongate',88    'OrderUnsubscribe')89    SELECT @ElementCount = @@ROWCOUNT90    SELECT @ElementIndex = 191    WHILE @ElementIndex <= @ElementCount92    BEGIN93      SELECT94        TOP 1 @uid_personwantsorg = bu.UID1,95        @GenProcID = bu.UID2,96        @whereclause = bu.ContentFull,97        @AutomaticReasonTrue = bu.LongIdent1,98        @AutomaticReasonFalse = bu.LongIdent2,99        @BasisObjectKey = bu.ObjectKey1,100        @DecisionLevel = Int1101      FROM @ElementBuffer bu102      WHERE103        bu.ElementIndex = @ElementIndex104      SELECT @SQLcmd = @muster105      SELECT106        @SQLcmd = replace(@SQLcmd,107        N '@whereclause',108        @whereclause)109      SELECT110        @SQLcmd = replace(@SQLcmd,111        N '@uid_personwantsorg',112        @uid_personwantsorg)113      BEGIN TRY114        EXEC sp_executesql @SQLcmd115        SELECT @CountOfRows = @@Rowcount116      END TRY117      BEGIN CATCH118        SELECT @CountOfRows = -1119      END CATCH120      IF @CountOfRows > 0121      BEGIN122        SELECT @decision = 'True'123        IF @AutomaticReasonTrue > ' '124        BEGIN125          SELECT @Reason = @AutomaticReasonTrue126        END127        ELSE128        BEGIN129          SELECT @reason = '#LDS#Automatic system approval with method CD : Condition was met.|'130        END131      END132      IF @CountOfRows = 0133      BEGIN134        SELECT @decision = 'False'135        IF @AutomaticReasonFalse > ' '136        BEGIN137          SELECT @Reason = @AutomaticReasonFalse138        END139        ELSE140        BEGIN141          SELECT142            @reason = '#LDS#Automatic system approval with method CD : Condition was not met.|'143        END144      END145      IF @CountOfRows < 0146      BEGIN147        SELECT @decision = 'False'148        SELECT149          @reason = '#LDS#Automatic system approval: Condition caused runtime error using method CD.|'150      END151      SELECT152        @cond = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + ''' and DecisionLevel = ' + STR(@DecisionLevel)153      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',154        @whereclause = @cond,155        @save = 1,156        @MethodName = 'MakeDecision',157        @GenProcID = @GenprocID,158        @ObjectKeysAffected = DEFAULT,159        @param1 = 'sa',160        @param2 = @decision,161        @param3 = @reason,162        @Retries = 3,163        @isToFreezeOnError = 1,164        @BasisObjectKey = @BasisObjectKey,165        @checkForExisting = 1,166        @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'167      SELECT @ElementIndex += 1168    END169  END TRY170  BEGIN CATCH171    EXEC QBM_PSessionErrorAdd DEFAULT,172      @SQLcmd173    RAISERROR('',174    18,175    1)176      WITH NOWAIT177  END CATCH178  endLabel:179  RETURN180END
Open raw exported source
SQL ยท Raw39 lines
1   create   procedure QER_ZITShopMakeDecisionCD (@SlotNumber int) as begin declare @UID_Personwantsorg varchar(38) declare @GenProcID varchar(382) declare @Decision nvarchar(16) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'3)) declare @Reason nvarchar(255) declare @uid_person varchar(38) declare @cond nvarchar(max) declare @whereclause nvarchar(max) declare @SQLcmd nvarchar4(max) declare @muster nvarchar(max) declare @BasisObjectKey varchar(138) declare @CountOfRows int declare @DecisionLevel int declare @ElementBuffer QBM_YCursorBuffer5 declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent SET XACT_ABORT OFF BEGIN TRY insert into @DBQueueCurrent6(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent7 cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @muster = N'8if exists (select top 1 19			 from Personwantsorg10				where (11-- die Kundenklausel12@whereclause13						)14				and uid_personwantsorg = ''@uid_personwantsorg''15			)16 begin17	select 118 end19'20 declare @AutomaticReasonTrue nvarchar(128) declare @AutomaticReasonFalse nvarchar(128) insert into @ElementBuffer (UID1  , UID2  , ContentFull  , LongIdent121  , LongIdent2  , ObjectKey1  , Int1  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID22 , isnull(ds.WhereClause, N'') , isnull(ds.AutomaticReasonTrue, ''), isnull(ds.AutomaticReasonFalse, ''), pwo.XObjectKey , pwo.DecisionLevel from @DBQueueCurrent23 p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel24 = ph.levelnumber join QERWorkingStep ds on ph.UID_QERWorkingStep = ds.UID_QERWorkingStep  where ph.UID_PWODecisionRule = 'QER-PWODecisionRule-CD'   and25 OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <=26 @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @whereclause = bu.ContentFull  , @AutomaticReasonTrue = bu.LongIdent127  , @AutomaticReasonFalse = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel = Int1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex28 select @SQLcmd = @muster select @SQLcmd = replace(@SQLcmd , N'@whereclause', @whereclause) select @SQLcmd = replace(@SQLcmd, N'@uid_personwantsorg', @uid_personwantsorg29)  BEGIN TRY exec sp_executesql @SQLcmd select @CountOfRows = @@Rowcount END TRY BEGIN CATCH select @CountOfRows = -1 END CATCH if @CountOfRows > 0 begin30  select @decision = 'True'  if @AutomaticReasonTrue > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @reason = '#LDS#Automatic system approval with method CD : Condition was met.|'31 end end if @CountOfRows = 0  begin select @decision = 'False'  if @AutomaticReasonFalse > ' ' begin select @Reason = @AutomaticReasonFalse end else begin32 select @reason = '#LDS#Automatic system approval with method CD : Condition was not met.|' end end if @CountOfRows < 0  begin select @decision = 'False'33 select @reason = '#LDS#Automatic system approval: Condition caused runtime error using method CD.|' end  select @cond = 'uid_personwantsorg = ''' + rtrim34(@uid_personwantsorg) + ''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause35 = @cond , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , 36@param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'37  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return38 end 39