Back to OIM Explorer

dbo.CPL_ZITShopMakeDecisionCR

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

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

Source: sandbox-db sys.sql_modules

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

Typed Edges

  • calls object method personwantsorg.MakeDecision HOCallMethod -> personwantsorg.MakeDecision at line 29
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext 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_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.CPL_FTCCSPWOCheck source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL198 lines
1CREATE PROCEDURE CPL_ZITShopMakeDecisionCR(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @MyName nvarchar(64) = object_name(@@procid)7  DECLARE @DecisionLevel int8  DECLARE @UID_Personwantsorg varchar(38)9  DECLARE @GenProcID varchar(38)10  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))11  DECLARE @Decision nvarchar(16)12  DECLARE @Reason nvarchar(max)13  DECLARE @uid_person varchar(38)14  DECLARE @SQLcmd nvarchar(max)15  DECLARE @BasisObjectKey varchar(138)16  DECLARE @xuser nvarchar(64)17  DECLARE @xdate datetime18  DECLARE @AutomaticReasonTrue nvarchar(128)19  DECLARE @AutomaticReasonFalse nvarchar(128)20  DECLARE @ComplianceRules nvarchar(max)21  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')22  DECLARE @ElementBuffer QBM_YCursorBuffer23  DECLARE @ElementCount int24  DECLARE @ElementIndex int25  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent26  BEGIN TRY27    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,28    UID_Parameter,29    UID_SubParameter,30    GenProcID)31    SELECT32      UID_DialogDBQueue,33      UID_Parameter,34      UID_SubParameter,35      GenProcID36    FROM QBMDBQueueCurrent cu37      WITH(readpast)38    WHERE39      cu.SlotNumber = @SlotNumber40    IF @@rowcount = 041    BEGIN42      GOTO EndLabel43    END44    SELECT @xuser = 'DBScheduler'45    SELECT @xdate = GetUTCDate()46    INSERT INTO @ElementBuffer(UID1,47    UID2,48    UID3,49    LongIdent1,50    LongIdent2,51    ObjectKey1,52    Int1)53    SELECT54      pwo.uid_personwantsorg,55      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,56      p.GenProcID,57      @CfgUseGenProcID) AS GenProcID,58      pwo.uid_personordered,59      isnull(s.AutomaticReasonTrue,60      ''),61      isnull(s.AutomaticReasonFalse,62      ''),63      pwo.XObjectKey,64      pwo.DecisionLevel65    FROM @DBQueueCurrent p66    JOIN personwantsorg pwo67      ON p.uid_parameter = pwo.uid_personwantsorg68    JOIN pwohelperpwo ph69      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber70    JOIN QERWorkingStep s71      ON ph.uid_QERWorkingStep = s.uid_QERWorkingStep72    WHERE73      ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-CR' AND OrderState IN('OrderProduct',74    'OrderProlongate',75    'OrderUnsubscribe')76    SELECT @ElementCount = @@ROWCOUNT77    SELECT @ElementIndex = 178    WHILE @ElementIndex <= @ElementCount79    BEGIN80      SELECT81        TOP 1 @uid_personwantsorg = bu.UID1,82        @GenProcID = bu.UID2,83        @uid_person = bu.UID3,84        @AutomaticReasonTrue = bu.LongIdent1,85        @AutomaticReasonFalse = bu.LongIdent2,86        @BasisObjectKey = bu.ObjectKey1,87        @DecisionLevel = bu.Int188      FROM @ElementBuffer bu89      WHERE90        bu.ElementIndex = @ElementIndex91      EXEC QBM_PSessionContextSet 'GenProcID',92        @GenProcID93      DELETE HelperPWOCompliance94      WHERE95        uid_personwantsorg = @uid_personwantsorg96      EXEC QBM_PSessionContextSet 'GenProcID',97        @GenProcID98      INSERT INTO HelperPWOCompliance(UID_HelperPWOCompliance,99      UID_PersonWantsOrg,100      uid_complianceRule,101      UID_Person)102      SELECT103        newid(),104        @uid_personwantsorg,105        xx.uid_ComplianceRule,106        xx.uid_person107      FROM(108      SELECT109        DISTINCT v.uid_complianceRule,110        v.uid_Person111      FROM dbo.CPL_FTCCSPWOCheck(@uid_personwantsorg) v112      JOIN ComplianceRule cr113        ON v.uid_complianceRule = cr.uid_complianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0114      WHERE115        (((cr.DetectRuleTypeForPWO = 'NewRule' AND v.isNewRule = 1) OR(cr.DetectRuleTypeForPWO = 'AnyRule') OR(cr.DetectRuleTypeForPWO = 'UnGrantedRule' AND116      v.isexceptiongranted = 0)) OR(cr.isToGrantEver = 1 AND(v.isNewRule = 1 OR v.isNewObject = 1))) AND NOT EXISTS(117      SELECT TOP 1 1118      FROM HelperPWOCompliance x119      WHERE120        x.UID_PersonWantsOrg = @uid_personwantsorg AND x.uid_complianceRule = v.uid_complianceRule AND x.uid_Person = v.uid_Person)) AS xx121      SELECT @ComplianceRules = NULL122      SELECT @ComplianceRules = string_agg(123      LEFT(c.Ident_ComplianceRule, 64),124      ',')125      FROM HelperPWOCompliance hc126      JOIN ComplianceRule c127        ON hc.UID_ComplianceRule = c.UID_ComplianceRule AND c.IsInActive = 0 AND c.IsWorkingCopy = 0128      WHERE129        hc.UID_PersonWantsOrg = @UID_Personwantsorg130      IF @ComplianceRules > ' '131      BEGIN132        SELECT @Decision = 'False'133        IF @AutomaticReasonFalse > ' '134        BEGIN135          SELECT @Reason = CASE136          WHEN @AutomaticReasonFalse LIKE CONCAT('#',137          'L',138          'D',139          'S',140          '#',141          '%') THEN142          ''143          ELSE CONCAT('#',144          'L',145          'D',146          'S',147          '#')148          END + @AutomaticReasonFalse + '|' + @ComplianceRules + '|'149        END150        ELSE151        BEGIN152          SELECT153            @Reason = '#LDS#At least one compliance rule has been violated: {0}.|' + @ComplianceRules + '|'154        END155      END156      ELSE157      BEGIN158        SELECT @Decision = 'True'159        IF @AutomaticReasonTrue > ' '160        BEGIN161          SELECT @Reason = @AutomaticReasonTrue162        END163        ELSE164        BEGIN165          SELECT @Reason = '#LDS#No compliance rule has been violated.|'166        END167      END168      SELECT169        @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)170      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',171        @whereclause = @SQLcmd,172        @save = 1,173        @MethodName = 'MakeDecision',174        @GenProcID = @GenprocID,175        @ObjectKeysAffected = DEFAULT,176        @param1 = 'sa',177        @param2 = @decision,178        @param3 = @Reason,179        @Retries = 3,180        @isToFreezeOnError = 1,181        @BasisObjectKey = @BasisObjectKey,182        @checkForExisting = 1,183        @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'184      SELECT @ElementIndex += 1185    END186  END TRY187  BEGIN CATCH188    EXEC QBM_PSessionErrorAdd DEFAULT189    RAISERROR('',190    18,191    1)192      WITH NOWAIT193  END CATCH194  endLabel:195  EXEC QBM_PSessionContextSet 'GenProcID',196    @GenProcID_R197  RETURN198END
Open raw exported source
SQL ยท Raw35 lines
1     create   procedure CPL_ZITShopMakeDecisionCR (@SlotNumber int) as begin  declare @MyName nvarchar(64) = object_name(@@procid) declare @DecisionLevel2 int declare @UID_Personwantsorg varchar(38) declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue3('Common\ProcessState\UseGenProcIDFromPWO')) declare @Decision nvarchar(16) declare @Reason nvarchar(max) declare @uid_person varchar(38) declare @SQLcmd4 nvarchar(max) declare @BasisObjectKey varchar(138) declare @xuser nvarchar(64) declare @xdate datetime declare @AutomaticReasonTrue nvarchar(128) declare5 @AutomaticReasonFalse nvarchar(128) declare @ComplianceRules nvarchar(max) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementBuffer6 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent7(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent8 cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @xuser = 'DBScheduler' select @xdate = GetUTCDate9() insert into @ElementBuffer (UID1  , UID2  , UID3  , LongIdent1  , LongIdent2  , ObjectKey1  , Int1  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO10(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.uid_personordered , isnull(s.AutomaticReasonTrue, ''), isnull(s.AutomaticReasonFalse,11 ''), pwo.XObjectKey , pwo.DecisionLevel from @DBQueueCurrent p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join pwohelperpwo ph12 on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel = ph.levelnumber join QERWorkingStep s on ph.uid_QERWorkingStep = s.uid_QERWorkingStep13  where ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-CR'   and OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount14 = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  ,15 @uid_person = bu.UID3  , @AutomaticReasonTrue = bu.LongIdent1  , @AutomaticReasonFalse = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel16 = bu.Int1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex   exec QBM_PSessionContextSet 'GenProcID', @GenProcID delete HelperPWOCompliance17 where uid_personwantsorg = @uid_personwantsorg  exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into HelperPWOCompliance (UID_HelperPWOCompliance18, UID_PersonWantsOrg, uid_complianceRule , UID_Person) select newid(), @uid_personwantsorg, xx.uid_ComplianceRule, xx.uid_person from ( select distinct19 v.uid_complianceRule, v.uid_Person  from dbo.CPL_FTCCSPWOCheck(@uid_personwantsorg) v join ComplianceRule cr on v.uid_complianceRule = cr.uid_complianceRule20  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where (( (cr.DetectRuleTypeForPWO = 'NewRule' and v.isNewRule = 1 ) or (cr.DetectRuleTypeForPWO = 'AnyRule'21      ) or (cr.DetectRuleTypeForPWO = 'UnGrantedRule' and v.isexceptiongranted = 0 ) )  or ( cr.isToGrantEver = 1 and ( v.isNewRule = 1 or v.isNewObject22 = 1 ) ) )  and not exists ( select top 1 1 from HelperPWOCompliance x where x.UID_PersonWantsOrg = @uid_personwantsorg and x.uid_complianceRule = v.uid_complianceRule23 and x.uid_Person = v.uid_Person ) ) as xx select @ComplianceRules = null select @ComplianceRules = string_agg(left(c.Ident_ComplianceRule, 64) , ',') 24from HelperPWOCompliance hc join ComplianceRule c on hc.UID_ComplianceRule = c.UID_ComplianceRule  and c.IsInActive = 0 and c.IsWorkingCopy = 0  where 25hc.UID_PersonWantsOrg = @UID_Personwantsorg if @ComplianceRules > ' ' begin   select @Decision = 'False' if @AutomaticReasonFalse > ' ' begin select @Reason26 = case when @AutomaticReasonFalse like concat('#', 'L', 'D', 'S', '#', '%') then '' else concat( '#', 'L', 'D', 'S', '#' ) end + @AutomaticReasonFalse27 + '|' + @ComplianceRules + '|' end else begin select @Reason = '#LDS#At least one compliance rule has been violated: {0}.|' + @ComplianceRules + '|' end28 end else  begin select @Decision = 'True' if @AutomaticReasonTrue > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @Reason = '#LDS#No compliance rule has been violated.|'29 end end select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod30 @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT31 , @param1 = 'sa' , @param2 = @decision , @param3 = @Reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting32 = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  select @ElementIndex += 1 end  END TRY 33BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R34 return end 35