Back to OIM Explorer

dbo.QER_ZITShopMakeDecisionWC

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

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

Source: sandbox-db sys.sql_modules

Source size: 4.992 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 57
  • References QBM_PJobCreate*
  • References QBM_PJobCreate_HOCallMethod*

Typed Edges

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

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL211 lines
1CREATE PROCEDURE QER_ZITShopMakeDecisionWC(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @UID_Personwantsorg varchar(38)7  DECLARE @GenProcID varchar(38)8  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))9  DECLARE @Decision nvarchar(16)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 @CountItems int17  DECLARE @AutomaticReasonTrue nvarchar(128)18  DECLARE @AutomaticReasonFalse nvarchar(128)19  DECLARE @BasisObjectKey varchar(138)20  DECLARE @DecisionLevel int21  DECLARE @DebugLevel char(1) = 'W'22  DECLARE @ElementBuffer QBM_YCursorBuffer23  DECLARE @ElementCount int24  DECLARE @ElementIndex int25  DECLARE @SlotNumberSource int26  DECLARE @SlotNumberTarget int27  DECLARE @DBQueueToMove QBM_YDBQCSCurrentToMove28  DECLARE @RowsMoved int29  SET XACT_ABORT OFF30  BEGIN TRY31    SELECT32      @muster = '33declare @erg int34select @erg = 35-- die Kundenklausel36@whereclause (''@uid_personwantsorg'')3738--print @erg3940if @erg > 041 begin42	select 143	 union all 44	select 245	 union all 46	select 347	 48 end49else --bleiben noch 0 und < 050 begin51	 if @erg < 052	  begin53		select 154	  end55	 else -- ist es gleich 056	  begin57		select 158		 union all59		select 260	  end61 end62		63'64    INSERT INTO @ElementBuffer(UID1,65    UID2,66    ContentFull,67    LongIdent1,68    LongIdent2,69    ObjectKey1,70    Int1)71    SELECT72      pwo.uid_personwantsorg,73      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,74      p.GenProcID,75      @CfgUseGenProcID) AS GenProcID,76      isnull(ds.WhereClause,77      N ''),78      isnull(ds.AutomaticReasonTrue,79      ''),80      isnull(ds.AutomaticReasonFalse,81      ''),82      pwo.XObjectKey,83      pwo.DecisionLevel84    FROM QBMDBQueueCurrent p85      WITH(readpast)86    JOIN personwantsorg pwo87      ON p.uid_parameter = pwo.uid_personwantsorg AND p.SlotNumber = @SlotNumber88    JOIN PWOHelperPWO ph89      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber90    JOIN QERWorkingStep ds91      ON ph.UID_QERWorkingStep = ds.UID_QERWorkingStep92    WHERE93      ph.UID_PWODecisionRule = 'QER-PWODecisionRule-WC' AND pwo.OrderState IN('OrderProduct',94    'OrderProlongate',95    'OrderUnsubscribe')96    SELECT @ElementCount = @@ROWCOUNT97    SELECT @ElementIndex = 198    WHILE @ElementIndex <= @ElementCount99    BEGIN100      SELECT101        TOP 1 @uid_personwantsorg = bu.UID1,102        @GenProcID = bu.UID2,103        @whereclause = bu.ContentFull,104        @AutomaticReasonTrue = bu.LongIdent1,105        @AutomaticReasonFalse = bu.LongIdent2,106        @BasisObjectKey = bu.ObjectKey1,107        @DecisionLevel = bu.Int1108      FROM @ElementBuffer bu109      WHERE110        bu.ElementIndex = @ElementIndex111      SELECT @SQLcmd = @muster112      SELECT113        @SQLcmd = replace(@SQLcmd,114        N '@whereclause',115        @whereclause)116      SELECT117        @SQLcmd = replace(@SQLcmd,118        N '@uid_personwantsorg',119        @uid_personwantsorg)120      BEGIN TRY121        EXEC sp_executesql @SQLcmd122        SELECT @CountItems = @@rowcount123      END TRY124      BEGIN CATCH125        SELECT @CountItems = -1126      END CATCH127      IF @CountItems = 3128      BEGIN129        SELECT @decision = 'True'130        IF @AutomaticReasonTrue > ' '131        BEGIN132          SELECT @Reason = @AutomaticReasonTrue133        END134        ELSE135        BEGIN136          SELECT @reason = '#LDS#Automatic system approval with method WC: Condition was met.|'137        END138      END139      IF @CountItems = 1140      BEGIN141        SELECT @decision = 'False'142        IF @AutomaticReasonFalse > ' '143        BEGIN144          SELECT @Reason = @AutomaticReasonFalse145        END146        ELSE147        BEGIN148          SELECT149            @reason = '#LDS#Automatic system approval with method WC: Condition was not met.|'150        END151      END152      IF @CountItems = 2153      BEGIN154        DELETE @DBQueueToMove155        INSERT INTO @DBQueueToMove(UID_DialogDBQueue)156        SELECT cul.UID_DialogDBQueue157        FROM QBMDBQueueCurrent cul158        WHERE159          cul.SlotNumber = @SlotNumber AND cul.UID_Parameter = @UID_Personwantsorg160        IF @@ROWCOUNT > 0161        BEGIN162          SELECT @SlotNumberSource = @SlotNumber163          SELECT @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Wait')164          EXEC @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove,165            @SlotNumberSource,166            @SlotnumberTarget167          EXEC QBM_PJournal '#LDS#Deferring operations resulting form the IT Shop procedure "waiting for condition".|',168            @@procid,169          'I',170            @DebugLevel171        END172      END173      IF @CountItems < 0174      BEGIN175        SELECT @decision = 'False'176        SELECT177          @reason = '#LDS#Automatic system approval: Condition caused runtime error using method WC.|'178      END179      IF @CountItems = 3 OR @CountItems = 1 OR @CountItems < 0180      BEGIN181        SELECT182          @cond = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)183        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',184          @whereclause = @cond,185          @save = 1,186          @MethodName = 'MakeDecision',187          @GenProcID = @GenprocID,188          @ObjectKeysAffected = DEFAULT,189          @param1 = 'sa',190          @param2 = @decision,191          @param3 = @reason,192          @Retries = 3,193          @isToFreezeOnError = 1,194          @BasisObjectKey = @BasisObjectKey,195          @checkForExisting = 1,196          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'197      END198      SELECT @ElementIndex += 1199    END200  END TRY201  BEGIN CATCH202    EXEC QBM_PSessionErrorAdd DEFAULT,203      @SQLcmd204    RAISERROR('',205    18,206    1)207      WITH NOWAIT208  END CATCH209  endLabel:210  RETURN211END
Open raw exported source
SQL ยท Raw62 lines
1   create   procedure QER_ZITShopMakeDecisionWC (@SlotNumber int) as begin declare @UID_Personwantsorg varchar(38) declare @GenProcID varchar(382) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @Decision nvarchar3(16) 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 @CountItems int declare @AutomaticReasonTrue nvarchar(128) declare @AutomaticReasonFalse nvarchar(128) declare5 @BasisObjectKey varchar(138) declare @DecisionLevel int declare @DebugLevel char(1) = 'W' declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount6 int declare @ElementIndex int  declare @SlotNumberSource int declare @SlotNumberTarget int declare @DBQueueToMove QBM_YDBQCSCurrentToMove declare @RowsMoved7 int SET XACT_ABORT OFF BEGIN TRY  select @muster = '8declare @erg int9select @erg = 10-- die Kundenklausel11@whereclause (''@uid_personwantsorg'')1213--print @erg1415if @erg > 016 begin17	select 118	 union all 19	select 220	 union all 21	select 322	 23 end24else --bleiben noch 0 und < 025 begin26	 if @erg < 027	  begin28		select 129	  end30	 else -- ist es gleich 031	  begin32		select 133		 union all34		select 235	  end36 end37		38'39 insert into @ElementBuffer ( UID1  , UID2  , ContentFull  , LongIdent1  , LongIdent2  , ObjectKey1  , Int1  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO40(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , isnull(ds.WhereClause, N'') , isnull(ds.AutomaticReasonTrue, ''), isnull(ds.AutomaticReasonFalse41, ''), pwo.XObjectKey , pwo.DecisionLevel from QBMDBQueueCurrent p with (readpast) join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg 42and p.SlotNumber = @SlotNumber join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel = ph.levelnumber join QERWorkingStep43 ds on ph.UID_QERWorkingStep = ds.UID_QERWorkingStep  where ph.UID_PWODecisionRule = 'QER-PWODecisionRule-WC'   and pwo.OrderState in ('OrderProduct', 44'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top45 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @whereclause = bu.ContentFull  , @AutomaticReasonTrue = bu.LongIdent1  , @AutomaticReasonFalse46 = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel = bu.Int1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex select @SQLcmd47 = @muster select @SQLcmd = replace(@SQLcmd , N'@whereclause', @whereclause) select @SQLcmd = replace(@SQLcmd, N'@uid_personwantsorg', @uid_personwantsorg48)  BEGIN TRY exec sp_executesql @SQLcmd select @CountItems = @@rowcount END TRY BEGIN CATCH select @CountItems = -1 END CATCH if @CountItems = 3  begin49  select @decision = 'True'  if @AutomaticReasonTrue > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @reason = '#LDS#Automatic system approval with method WC: Condition was met.|'50 end end if @CountItems = 1  begin select @decision = 'False'  if @AutomaticReasonFalse > ' ' begin select @Reason = @AutomaticReasonFalse end else begin51 select @reason = '#LDS#Automatic system approval with method WC: Condition was not met.|' end end if @CountItems = 2  begin  delete @DBQueueToMove  insert52 into @DBQueueToMove(UID_DialogDBQueue) select cul.UID_DialogDBQueue from QBMDBQueueCurrent cul  where cul.SlotNumber = @SlotNumber and cul.UID_Parameter53 = @UID_Personwantsorg if @@ROWCOUNT > 0 begin select @SlotNumberSource = @SlotNumber  select @SlotnumberTarget = dbo.QBM_FGIDBQueueSlotResetType('Wait'54) exec @RowsMoved = QBM_PDBQCS_CurrentMoveSlot @DBQueueToMove, @SlotNumberSource, @SlotnumberTarget exec QBM_PJournal '#LDS#Deferring operations resulting form the IT Shop procedure "waiting for condition".|'55, @@procid, 'I', @DebugLevel end end  if @CountItems < 0  begin select @decision = 'False' select @reason = '#LDS#Automatic system approval: Condition caused runtime error using method WC.|'56 end if @CountItems = 3 or @CountItems = 1 or @CountItems < 0 begin select @cond = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = '57 + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @cond , @save = 1 , @MethodName = 'MakeDecision'58 , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , @param3 = @reason , @Retries = 3 , @isToFreezeOnError59 = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'60  end select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel:61 return end 62