Back to OIM Explorer

dbo.QER_ZITShopMakeDecisionRI

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

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

Source: sandbox-db sys.sql_modules

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

Typed Edges

  • calls object method personwantsorg.MakeDecision HOCallMethod -> personwantsorg.MakeDecision at line 69
  • references source dbo.QBM_FCVFloatToString source text reference
  • references source dbo.QBM_FCVObjectKeyMNToParentKey source text reference
  • references source dbo.QBM_FCVObjectkeyToElement source text reference
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FCVStringToFloat 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

SQL295 lines
1CREATE PROCEDURE QER_ZITShopMakeDecisionRI(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 @cond nvarchar(max)12  DECLARE @BasisObjectKey varchar(138)13  DECLARE @CountOfRows int14  DECLARE @DecisionLevel int15  DECLARE @RiskIndexLimit float16  DECLARE @objectkeyassignment varchar(138) = NULL17  DECLARE @TargetTableOrder varchar(30) = NULL18  DECLARE @ObjectKeyOrder varchar(138) = NULL19  DECLARE @ObjectKeyElementUsedInAssign varchar(138) = NULL20  DECLARE @SQLCmd nvarchar(max)21  DECLARE @AutomaticReasonTrue nvarchar(128)22  DECLARE @AutomaticReasonFalse nvarchar(128)23  DECLARE @DebugSwitch int = 024  DECLARE @TargetTableAssign1 varchar(30) = NULL25  DECLARE @TargetTableAssign2 varchar(30) = NULL26  DECLARE @ObjectKeyFinal1 varchar(138) = NULL27  DECLARE @ObjectKeyFinal2 varchar(138) = NULL28  DECLARE @ElementBuffer QBM_YCursorBuffer29  DECLARE @ElementCount int30  DECLARE @ElementIndex int31  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent32  SET XACT_ABORT OFF33  BEGIN TRY34    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,35    UID_Parameter,36    UID_SubParameter,37    GenProcID)38    SELECT39      UID_DialogDBQueue,40      UID_Parameter,41      UID_SubParameter,42      GenProcID43    FROM QBMDBQueueCurrent cu44      WITH(readpast)45    WHERE46      cu.SlotNumber = @SlotNumber47    IF @@rowcount = 048    BEGIN49      GOTO EndLabel50    END51    INSERT INTO @ElementBuffer(UID1,52    UID2,53    LongIdent1,54    LongIdent2,55    ObjectKey1,56    Int1,57    Ident3,58    ObjectKey2,59    ObjectKey3,60    ContentShort)61    SELECT62      pwo.uid_personwantsorg,63      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,64      p.GenProcID,65      @CfgUseGenProcID) AS GenProcID,66      isnull(ds.AutomaticReasonTrue,67      ''),68      isnull(ds.AutomaticReasonFalse,69      ''),70      pwo.XObjectKey,71      pwo.DecisionLevel,72      dbo.QBM_FCVFloatToString(dbo.QBM_FCVStringToFloat(ds.WhereClause, 1.0)),73      pwo.ObjectKeyAssignment,74      pwo.ObjectKeyOrdered,75      pwo.ObjectKeyElementUsedInAssign76    FROM @DBQueueCurrent p77    JOIN personwantsorg pwo78      ON p.uid_parameter = pwo.uid_personwantsorg79    JOIN PWOHelperPWO ph80      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber81    JOIN QERWorkingStep ds82      ON ph.UID_QERWorkingStep = ds.UID_QERWorkingStep83    WHERE84      ph.UID_PWODecisionRule = 'QER-PWODecisionRule-RI' AND OrderState IN('OrderProduct',85    'OrderProlongate',86    'OrderUnsubscribe')87    SELECT @ElementCount = @@ROWCOUNT88    SELECT @ElementIndex = 189    WHILE @ElementIndex <= @ElementCount90    BEGIN91      SELECT92        TOP 1 @uid_personwantsorg = bu.UID1,93        @GenProcID = bu.UID2,94        @AutomaticReasonTrue = bu.LongIdent1,95        @AutomaticReasonFalse = bu.LongIdent2,96        @BasisObjectKey = bu.ObjectKey1,97        @DecisionLevel = bu.Int1,98        @RiskIndexLimit = dbo.QBM_FCVStringToFloat(bu.Ident3,99        0),100        @ObjectKeyAssignment = bu.ObjectKey2,101        @ObjectKeyOrder = bu.ObjectKey3,102        @ObjectKeyElementUsedInAssign = bu.ContentShort103      FROM @ElementBuffer bu104      WHERE105        bu.ElementIndex = @ElementIndex106      SELECT @TargetTableAssign1 = NULL107      SELECT @TargetTableAssign2 = NULL108      SELECT @ObjectKeyFinal1 = NULL109      SELECT @ObjectKeyFinal2 = NULL110      IF @objectkeyassignment > ' '111      BEGIN112        IF dbo.QBM_FCVObjectkeyToElement('UID_DialogTable',113        @ObjectKeyAssignment) <> 'QER-T-ESetHasEntitlement'114        BEGIN115          SELECT116            @ObjectKeyFinal1 = dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment,117            0)118          SELECT TOP 1 @TargetTableAssign1 = r.ParentTable119          FROM QBM_VQBMRelation r120          JOIN DialogColumn c121            ON r.UID_DialogTableParent = c.UID_DialogTable AND c.ColumnName = 'RiskIndex'122          WHERE123            r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName',124          @objectkeyassignment) AND r.IsMNRelation = 1 AND @ObjectKeyFinal1 LIKE '<Key><T>' + r.ParentTable + '</T>%'125          SELECT126            @ObjectKeyFinal2 = dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment,127            1)128          SELECT TOP 1 @TargetTableAssign2 = r.ParentTable129          FROM QBM_VQBMRelation r130          JOIN DialogColumn c131            ON r.UID_DialogTableParent = c.UID_DialogTable AND c.ColumnName = 'RiskIndex'132          WHERE133            r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName',134          @objectkeyassignment) AND r.IsMNRelation = 1 AND @ObjectKeyFinal2 LIKE '<Key><T>' + r.ParentTable + '</T>%'135          IF @DebugSwitch > 0136          BEGIN137            print @TargetTableAssign1 print @objectkeyFinal1 print @TargetTableAssign2 print @objectkeyFinal2138          END139        END140        ELSE141        BEGIN142          SELECT @ObjectKeyFinal1 = @ObjectKeyElementUsedInAssign143          SELECT TOP 1 @TargetTableAssign1 = x.tab144          FROM(145          SELECT146            dbo.QBM_FCVObjectkeyToElement('TableName', @ObjectKeyElementUsedInAssign) AS tab) x147          JOIN DialogTable t148            WITH(readpast)149            ON x.tab = t.TableName150          JOIN DialogColumn c151            ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = 'RiskIndex'152          IF @DebugSwitch > 0153          BEGIN154            print @TargetTableAssign1 print @ObjectKeyFinal1155          END156        END157      END158      ELSE159      BEGIN160        IF @ObjectKeyOrder > ' '161        BEGIN162          SELECT TOP 1 @TargetTableOrder = x.tab163          FROM(164          SELECT165            dbo.QBM_FCVObjectkeyToElement('TableName', @ObjectKeyOrder) AS tab) x166          JOIN DialogTable t167            WITH(readpast)168            ON x.tab = t.TableName169          JOIN DialogColumn c170            ON t.UID_DialogTable = c.UID_DialogTable AND c.ColumnName = 'RiskIndex'171          IF @DebugSwitch > 0172          BEGIN173            print @TargetTableOrder print @ObjectKeyOrder174          END175        END176      END177      IF @TargetTableAssign1 > ' ' OR @TargetTableAssign2 > ' ' OR @TargetTableOrder > ' '178      BEGIN179        SELECT @SQLCmd = '180				if 1 = 0181				  ' + CASE182        WHEN @TargetTableAssign1 IS NULL THEN183        ''184        ELSE ' or ( select top 1 isnull(Riskindex, 0.0) 185										from ' + @TargetTableAssign1 + ' 186										where XObjectKey = ''' + @ObjectKeyFinal1187        + ''' 188									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '189					'190        END + CASE191        WHEN @TargetTableAssign2 IS NULL THEN192        ''193        ELSE ' or ( select top 1 isnull(Riskindex, 0.0) 194										from ' + @TargetTableAssign2 + ' 195										where XObjectKey = ''' + @ObjectKeyFinal2196        + ''' 197									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '198					'199        END + CASE200        WHEN @TargetTableOrder IS NULL THEN201        ''202        ELSE ' or ( select top 1 isnull(Riskindex, 0.0) 203										from ' + @TargetTableOrder + ' 204										where XObjectKey = ''' + @ObjectKeyOrder205        + ''' 206									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '207					'208        END + '209				 begin210					print '''' -- 0 rows affected211				 end212				else213				 begin214					select 1 as Decision215				 end216					'217      END218      ELSE219      BEGIN220        SELECT @SQLCmd = '221					select 1 as Decision'222      END223      IF @DebugSwitch > 0224      BEGIN225        print @SQLCmd226      END227      BEGIN TRY228        EXEC sp_executesql @SQLcmd229        SELECT @CountOfRows = @@Rowcount230      END TRY231      BEGIN CATCH232        SELECT @CountOfRows = -1233      END CATCH234      IF @CountOfRows > 0235      BEGIN236        SELECT @decision = 'True'237        IF @AutomaticReasonTrue > ' '238        BEGIN239          SELECT @Reason = @AutomaticReasonTrue240        END241        ELSE242        BEGIN243          SELECT244            @reason = '#LDS#Automatic system approval with method RI : Riskindex lower then limit.|'245        END246      END247      IF @CountOfRows = 0248      BEGIN249        SELECT @decision = 'False'250        IF @AutomaticReasonFalse > ' '251        BEGIN252          SELECT @Reason = @AutomaticReasonFalse253        END254        ELSE255        BEGIN256          SELECT257            @reason = '#LDS#Automatic system approval with method RI : Riskindex equal or higher then limit.|'258        END259      END260      IF @CountOfRows < 0261      BEGIN262        SELECT @decision = 'False'263        SELECT264          @reason = '#LDS#Automatic system approval: Condition caused runtime error using method RI.|'265      END266      SELECT267        @cond = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + ''' and DecisionLevel = ' + STR(@DecisionLevel)268      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',269        @whereclause = @cond,270        @save = 1,271        @MethodName = 'MakeDecision',272        @GenProcID = @GenprocID,273        @ObjectKeysAffected = DEFAULT,274        @param1 = 'sa',275        @param2 = @decision,276        @param3 = @reason,277        @Retries = 3,278        @isToFreezeOnError = 1,279        @BasisObjectKey = @BasisObjectKey,280        @checkForExisting = 1,281        @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'282      SELECT @ElementIndex += 1283    END284  END TRY285  BEGIN CATCH286    EXEC QBM_PSessionErrorAdd DEFAULT,287      @SQLcmd288    RAISERROR('',289    18,290    1)291      WITH NOWAIT292  END CATCH293  endLabel:294  RETURN295END
Open raw exported source
SQL ยท Raw74 lines
1    create   procedure QER_ZITShopMakeDecisionRI (@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 @cond nvarchar(max) declare @BasisObjectKey varchar(138) declare @CountOfRows int declare @DecisionLevel int4 declare @RiskIndexLimit float declare @objectkeyassignment varchar(138) = null declare @TargetTableOrder varchar(30) = null declare @ObjectKeyOrder varchar5(138) = null declare @ObjectKeyElementUsedInAssign varchar(138) = null declare @SQLCmd nvarchar(max) declare @AutomaticReasonTrue nvarchar(128) declare6 @AutomaticReasonFalse nvarchar(128) declare @DebugSwitch int = 0 declare @TargetTableAssign1 varchar(30) = null declare @TargetTableAssign2 varchar(307) = null declare @ObjectKeyFinal1 varchar(138) = null declare @ObjectKeyFinal2 varchar(138) = null declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount8 int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent SET XACT_ABORT OFF BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, 9UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast10) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @ElementBuffer (UID1  , UID2  , LongIdent1  , LongIdent2  , ObjectKey111  , Int1  , Ident3  , ObjectKey2 , ObjectKey3  , ContentShort  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID,12 @CfgUseGenProcID) as GenProcID , isnull(ds.AutomaticReasonTrue, ''), isnull(ds.AutomaticReasonFalse, ''), pwo.XObjectKey , pwo.DecisionLevel , dbo.QBM_FCVFloatToString13( dbo.QBM_FCVStringToFloat( ds.WhereClause , 1.0))  , pwo.ObjectKeyAssignment, pwo.ObjectKeyOrdered , pwo.ObjectKeyElementUsedInAssign from @DBQueueCurrent14 p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel15 = ph.levelnumber join QERWorkingStep ds on ph.UID_QERWorkingStep = ds.UID_QERWorkingStep where ph.UID_PWODecisionRule = 'QER-PWODecisionRule-RI'   and16 OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <=17 @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @AutomaticReasonTrue = bu.LongIdent1  , @AutomaticReasonFalse18 = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel = bu.Int1  , @RiskIndexLimit = dbo.QBM_FCVStringToFloat(bu.Ident3, 0)  , @ObjectKeyAssignment19 = bu.ObjectKey2 , @ObjectKeyOrder = bu.ObjectKey3  , @ObjectKeyElementUsedInAssign = bu.ContentShort from @ElementBuffer bu where bu.ElementIndex = @ElementIndex20  select @TargetTableAssign1 = null select @TargetTableAssign2 = null select @ObjectKeyFinal1 = null select @ObjectKeyFinal2 = null if @objectkeyassignment21 > ' ' begin if dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', @ObjectKeyAssignment) <> 'QER-T-ESetHasEntitlement' begin    select @ObjectKeyFinal1 =22 dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment, 0) select top 1 @TargetTableAssign1 = r.ParentTable from QBM_VQBMRelation r join DialogColumn 23c on r.UID_DialogTableParent = c.UID_DialogTable and c.ColumnName = 'RiskIndex' where r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName', @objectkeyassignment24) and r.IsMNRelation = 1 and @ObjectKeyFinal1 like '<Key><T>' + r.ParentTable + '</T>%'  select @ObjectKeyFinal2 = dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment25, 1) select top 1 @TargetTableAssign2 = r.ParentTable from QBM_VQBMRelation r join DialogColumn c on r.UID_DialogTableParent = c.UID_DialogTable and c.ColumnName26 = 'RiskIndex' where r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName', @objectkeyassignment) and r.IsMNRelation = 1 and @ObjectKeyFinal2 like '<Key><T>'27 + r.ParentTable + '</T>%' if @DebugSwitch > 0 begin print @TargetTableAssign1 print @objectkeyFinal1 print @TargetTableAssign2 print @objectkeyFinal2 28end end else begin   select @ObjectKeyFinal1 = @ObjectKeyElementUsedInAssign select top 1 @TargetTableAssign1 = x.tab from ( select dbo.QBM_FCVObjectkeyToElement29('TableName', @ObjectKeyElementUsedInAssign) as tab ) x join DialogTable t with (readpast) on x.tab = t.TableName join DialogColumn c on t.UID_DialogTable30 = c.UID_DialogTable and c.ColumnName = 'RiskIndex' if @DebugSwitch > 0 begin print @TargetTableAssign1 print @ObjectKeyFinal1 end end end else begin if31 @ObjectKeyOrder > ' ' begin select top 1 @TargetTableOrder = x.tab from ( select dbo.QBM_FCVObjectkeyToElement('TableName', @ObjectKeyOrder) as tab ) 32x join DialogTable t with (readpast) on x.tab = t.TableName join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName = 'RiskIndex'33 if @DebugSwitch > 0 begin print @TargetTableOrder print @ObjectKeyOrder end end end if @TargetTableAssign1 > ' ' or @TargetTableAssign2 > ' ' or @TargetTableOrder34 > ' ' begin select @SQLCmd = '35				if 1 = 036				  ' + case when @TargetTableAssign1 is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 37										from '38 + @TargetTableAssign1 + ' 39										where XObjectKey = ''' + @ObjectKeyFinal1 + ''' 40									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + 41'42					' end + case when @TargetTableAssign2 is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 43										from ' + @TargetTableAssign2 44+ ' 45										where XObjectKey = ''' + @ObjectKeyFinal2 + ''' 46									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '47					' end + case when48 @TargetTableOrder is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 49										from ' + @TargetTableOrder + ' 50										where XObjectKey = '''51 + @ObjectKeyOrder + ''' 52									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '53					' end + '54				 begin55					print '''' -- 0 rows affected56				 end57				else58				 begin59					select 1 as Decision60				 end61					'62 end else begin  select @SQLCmd = '63					select 1 as Decision' end if @DebugSwitch > 0 begin print @SQLCmd end  BEGIN TRY exec sp_executesql @SQLcmd select64 @CountOfRows = @@Rowcount END TRY BEGIN CATCH select @CountOfRows = -1 END CATCH if @CountOfRows > 0 begin  select @decision = 'True' if @AutomaticReasonTrue65 > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @reason = '#LDS#Automatic system approval with method RI : Riskindex lower then limit.|'66 end end if @CountOfRows = 0  begin select @decision = 'False'  if @AutomaticReasonFalse > ' ' begin select @Reason = @AutomaticReasonFalse end else begin67 select @reason = '#LDS#Automatic system approval with method RI : Riskindex equal or higher then limit.|' end end if @CountOfRows < 0  begin select @decision68 = 'False' select @reason = '#LDS#Automatic system approval: Condition caused runtime error using method RI.|' end  select @cond = 'uid_personwantsorg = '''69 + rtrim(@uid_personwantsorg) + ''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause70 = @cond , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , 71@param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'72  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return73 end 74