Back to OIM Explorer

dbo.CPL_ZITShopMakeDecisionOC

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

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

Source: sandbox-db sys.sql_modules

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

Typed Edges

  • calls object method personwantsorg.MakeDecision HOCallMethod -> personwantsorg.MakeDecision at line 30
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel 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

Referenced By

  • No direct source references extracted.

Complete Source

SQL203 lines
1CREATE PROCEDURE CPL_ZITShopMakeDecisionOC(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @DecisionLevel int7  DECLARE @UID_Personwantsorg varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))10  DECLARE @Decision nvarchar(16)11  DECLARE @Reason nvarchar(255)12  DECLARE @uid_person varchar(38)13  DECLARE @SQLcmd nvarchar(max)14  DECLARE @levelnumber int15  DECLARE @AnzahlVerstoss int16  DECLARE @AnzahlGenehmigt int17  DECLARE @IsDecision BIT18  DECLARE @BasisObjectKey varchar(138)19  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')20  DECLARE @ElementBuffer QBM_YCursorBuffer21  DECLARE @ElementCount int22  DECLARE @ElementIndex int23  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent24  DECLARE @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',25  0)26  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    INSERT INTO @ElementBuffer(UID1,45    UID2,46    UID3,47    Int1,48    ObjectKey1,49    Int2)50    SELECT51      DISTINCT pwo.uid_personwantsorg,52      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,53      p.GenProcID,54      @CfgUseGenProcID) AS GenProcID,55      pwo.uid_personordered,56      ph.Levelnumber,57      pwo.XObjectKey,58      pwo.DecisionLevel59    FROM @DBQueueCurrent p60    JOIN personwantsorg pwo61      ON p.uid_parameter = pwo.uid_personwantsorg62    JOIN pwohelperpwo ph63      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber64    WHERE65      ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-OC'66    SELECT @ElementCount = @@ROWCOUNT67    SELECT @ElementIndex = 168    WHILE @ElementIndex <= @ElementCount69    BEGIN70      SELECT71        TOP 1 @uid_personwantsorg = bu.UID1,72        @GenProcID = bu.UID2,73        @uid_person = bu.UID3,74        @levelnumber = bu.Int1,75        @BasisObjectKey = bu.ObjectKey1,76        @DecisionLevel = bu.Int277      FROM @ElementBuffer bu78      WHERE79        bu.ElementIndex = @ElementIndex80      SELECT @IsDecision = 081      SELECT @Decision = ''82      SELECT @Reason = ''83      IF NOT EXISTS(84        SELECT TOP 1 185        FROM HelperPWOCompliance86        WHERE87          uid_PersonWantsOrg = @UID_Personwantsorg)88      BEGIN89        SELECT @Decision = 'True'90        SELECT91          @Reason = '#LDS#No compliance rule has been violated., (missing CC step?) detected in Rule OC).|'92        SELECT @IsDecision = 193        GOTO istEntschieden94      END95      IF EXISTS(96        SELECT TOP 1 197        FROM HelperPWOCompliance pir98        JOIN ComplianceRule cr99          ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0100        WHERE101          pir.uid_PersonWantsOrg = @UID_Personwantsorg AND isnull(cr.IsExceptionAllowed, 0) = 0)102      BEGIN103        SELECT @Decision = 'False'104        SELECT105          @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OC|'106        SELECT @IsDecision = 1107        GOTO istEntschieden108      END109      IF EXISTS(110        SELECT TOP 1 1111        FROM pwohelperpwo112        WHERE113          uid_personWantsOrg = @UID_Personwantsorg AND LevelNumber = @levelnumber AND uid_personHead IS NULL)114      BEGIN115        SELECT @Decision = 'False'116        SELECT @Reason = '#LDS#There is no approver available for rule {0}.|OC|'117        SELECT @IsDecision = 1118        GOTO istEntschieden119      END120      IF EXISTS(121        SELECT TOP 1 1122        FROM HelperPWOCompliance pir123        JOIN ComplianceRule cr124          ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0125        WHERE126          pir.uid_PersonWantsOrg = @UID_Personwantsorg AND cr.isToGrantEver = 1)127      BEGIN128        SELECT @Decision = ''129        SELECT130          @Reason = '#LDS#At least one rule has been violated that requires approval for every exception. Rule: {0}.|OC|'131        SELECT @IsDecision = 0132        GOTO istEntschieden133      END134      SELECT @AnzahlVerstoss = count(*)135      FROM HelperPWOCompliance136      WHERE137        uid_PersonWantsOrg = @UID_Personwantsorg138      SELECT @AnzahlGenehmigt = count(*)139      FROM HelperPWOCompliance pir140      JOIN ComplianceRule cr141        ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND pir.uid_PersonWantsOrg = @UID_Personwantsorg AND cr.IsInActive = 0 AND cr.IsWorkingCopy142      = 0 AND cr.DetectRuleTypeForPWO <> 'AnyRule'143      JOIN personinBaseTree pin144        ON pin.uid_org = cr.uid_nonCompliance AND pin.uid_person = pir.uid_Person AND pin.IsExceptiongranted = 1 AND pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay145      = 0 AND pin.XOrigin > 0146      IF(@AnzahlVerstoss = @AnzahlGenehmigt)147      BEGIN148        SELECT @Decision = 'True'149        SELECT150          @Reason = '#LDS#All affected compliance rules have been granted exceptions. Rule: {0}.|OC|'151        SELECT @IsDecision = 1152        GOTO istEntschieden153      END154      ELSE155      BEGIN156        SELECT @IsDecision = 0157        GOTO istEntschieden158      END159      istEntschieden:160      IF @IsDecision = 1161      BEGIN162        SELECT163          @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)164        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',165          @whereclause = @SQLcmd,166          @save = 1,167          @MethodName = 'MakeDecision',168          @GenProcID = @GenprocID,169          @ObjectKeysAffected = DEFAULT,170          @param1 = 'sa',171          @param2 = @decision,172          @param3 = @reason,173          @Retries = 3,174          @isToFreezeOnError = 1,175          @BasisObjectKey = @BasisObjectKey,176          @checkForExisting = 1,177          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'178      END179      ELSE180      BEGIN181        EXEC QBM_PSessionContextSet 'GenProcID',182          @GenProcID183        UPDATE pwohelperpwo184        SET decision = ''185        WHERE186          levelnumber = @levelnumber AND uid_personWantsOrg = @uid_personwantsorg AND Decision IN('R',187        '+')188      END189      SELECT @ElementIndex += 1190    END191  END TRY192  BEGIN CATCH193    EXEC QBM_PSessionErrorAdd DEFAULT194    RAISERROR('',195    18,196    1)197      WITH NOWAIT198  END CATCH199  endLabel:200  EXEC QBM_PSessionContextSet 'GenProcID',201    @GenProcID_R202  RETURN203END
Open raw exported source
SQL ยท Raw37 lines
1   create   procedure CPL_ZITShopMakeDecisionOC (@SlotNumber int) as begin  declare @DecisionLevel int declare @UID_Personwantsorg varchar(38) declare2 @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare3 @Decision nvarchar(16) declare @Reason nvarchar(255) declare @uid_person varchar(38) declare @SQLcmd nvarchar(max) declare @levelnumber int declare @AnzahlVerstoss4 int declare @AnzahlGenehmigt int declare @IsDecision bit declare @BasisObjectKey varchar(138) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext5('') declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXMarkedForDel_Delay6 int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID7) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount8 = 0 begin goto EndLabel end insert into @ElementBuffer (UID1  , UID2  , UID3  , Int1  , ObjectKey1  , Int2  ) select distinct pwo.uid_personwantsorg ,9 dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.uid_personordered, ph.Levelnumber, pwo.XObjectKey , pwo.DecisionLevel10 from @DBQueueCurrent p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join pwohelperpwo ph on pwo.uid_personwantsorg = ph.uid_personwantsorg11 and pwo.decisionlevel = ph.levelnumber  where ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-OC'  select @ElementCount = @@ROWCOUNT select @ElementIndex12 = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @uid_person = bu.UID3  , @levelnumber13 = bu.Int1  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel = bu.Int2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex  select @IsDecision14 = 0 select @Decision = '' select @Reason = ''  if not exists (select top 1 1 from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg) 15begin   select @Decision = 'True'  select @Reason = '#LDS#No compliance rule has been violated., (missing CC step?) detected in Rule OC).|' select @IsDecision16 = 1 goto istEntschieden end   if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule17  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and isnull(cr.IsExceptionAllowed,0) = 0 ) begin select18 @Decision = 'False'  select @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OC|' select @IsDecision = 1 19goto istEntschieden end   if exists (select top 1 1 from pwohelperpwo where uid_personWantsOrg = @UID_Personwantsorg and LevelNumber = @levelnumber and20 uid_personHead is null ) begin select @Decision = 'False' select @Reason = '#LDS#There is no approver available for rule {0}.|OC|' select @IsDecision 21= 1 goto istEntschieden end  if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule22  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and cr.isToGrantEver = 1 ) begin select @Decision 23= '' select @Reason = '#LDS#At least one rule has been violated that requires approval for every exception. Rule: {0}.|OC|' select @IsDecision = 0 goto24 istEntschieden end   select @AnzahlVerstoss = count(*) from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg select @AnzahlGenehmigt25 = count(*) from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule and pir.uid_PersonWantsOrg = @UID_Personwantsorg26  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0    and cr.DetectRuleTypeForPWO <> 'AnyRule'  join personinBaseTree  pin on pin.uid_org  = cr.uid_nonCompliance27 and pin.uid_person = pir.uid_Person and pin.IsExceptiongranted = 1 and pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0 and pin.XOrigin28 > 0 if (@AnzahlVerstoss = @AnzahlGenehmigt) begin select @Decision = 'True'  select @Reason = '#LDS#All affected compliance rules have been granted exceptions. Rule: {0}.|OC|'29 select @IsDecision = 1 goto istEntschieden end else  begin select @IsDecision = 0 goto istEntschieden end   istEntschieden: if @IsDecision = 1 begin select30 @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod31 @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT32 , @param1 = 'sa' , @param2 = @decision , @param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting33 = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  end  else begin exec QBM_PSessionContextSet34 'GenProcID', @GenProcID update pwohelperpwo set decision = '' where levelnumber = @levelnumber and uid_personWantsOrg = @uid_personwantsorg and Decision35 in ( 'R', '+') end select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel:36 exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 37