Back to OIM Explorer

dbo.CPL_ZITShopMakeDecisionOH

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

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

Source: sandbox-db sys.sql_modules

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

Typed Edges

  • calls object method personwantsorg.MakeDecision HOCallMethod -> personwantsorg.MakeDecision at line 35
  • 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

SQL238 lines
1CREATE PROCEDURE CPL_ZITShopMakeDecisionOH(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 @Levelnumber int14  DECLARE @SQLcmd nvarchar(max)15  DECLARE @IsDecision BIT16  DECLARE @AnzahlVerstoss int17  DECLARE @AnzahlGenehmigt int18  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  DECLARE @OrderState nvarchar(16)27  BEGIN TRY28    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,29    UID_Parameter,30    UID_SubParameter,31    GenProcID)32    SELECT33      UID_DialogDBQueue,34      UID_Parameter,35      UID_SubParameter,36      GenProcID37    FROM QBMDBQueueCurrent cu38      WITH(readpast)39    WHERE40      cu.SlotNumber = @SlotNumber41    IF @@rowcount = 042    BEGIN43      GOTO EndLabel44    END45    INSERT INTO @ElementBuffer(UID1,46    UID2,47    UID3,48    Int1,49    ObjectKey1,50    Int2,51    LongIdent1)52    SELECT53      DISTINCT pwo.uid_personwantsorg,54      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID,55      p.GenProcID,56      @CfgUseGenProcID) AS GenProcID,57      pwo.uid_personordered,58      ph.levelnumber,59      pwo.XObjectKey,60      pwo.DecisionLevel,61      pwo.OrderState62    FROM @DBQueueCurrent p63    JOIN personwantsorg pwo64      ON p.uid_parameter = pwo.uid_personwantsorg65    JOIN PWOHelperPWO ph66      ON pwo.uid_personwantsorg = ph.uid_personwantsorg AND pwo.decisionlevel = ph.levelnumber67    WHERE68      ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-OH'69    SELECT @ElementCount = @@ROWCOUNT70    SELECT @ElementIndex = 171    WHILE @ElementIndex <= @ElementCount72    BEGIN73      SELECT74        TOP 1 @uid_personwantsorg = bu.UID1,75        @GenProcID = bu.UID2,76        @uid_person = bu.UID3,77        @levelnumber = bu.Int1,78        @BasisObjectKey = bu.ObjectKey1,79        @DecisionLevel = bu.Int2,80        @OrderState = bu.LongIdent181      FROM @ElementBuffer bu82      WHERE83        bu.ElementIndex = @ElementIndex84      SELECT @IsDecision = 085      SELECT @Decision = ''86      SELECT @Reason = ''87      IF NOT EXISTS(88        SELECT TOP 1 189        FROM HelperPWOCompliance90        WHERE91          uid_PersonWantsOrg = @UID_Personwantsorg)92      BEGIN93        SELECT @Decision = 'True'94        SELECT95          @Reason = '#LDS#No compliance rule has been violated., (missing CC step?) detected in Rule OH).|'96        SELECT @IsDecision = 197        GOTO istEntschieden98      END99      IF EXISTS(100        SELECT TOP 1 1101        FROM HelperPWOCompliance pir102        JOIN ComplianceRule cr103          ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0104        WHERE105          pir.uid_PersonWantsOrg = @UID_Personwantsorg AND isnull(cr.IsExceptionAllowed, 0) = 0)106      BEGIN107        SELECT @Decision = 'False'108        SELECT109          @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|'110        SELECT @IsDecision = 1111        GOTO istEntschieden112      END113      IF EXISTS(114        SELECT TOP 1 1115        FROM PWOHelperPWO116        WHERE117          uid_personWantsOrg = @UID_Personwantsorg AND LevelNumber = @levelnumber AND uid_personHead IS NULL AND decision = 'R')118      BEGIN119        SELECT @Decision = 'False'120        SELECT @Reason = '#LDS#There is no approver available for rule {0}.|OH|'121        SELECT @IsDecision = 1122      END123      IF EXISTS(124        SELECT TOP 1 1125        FROM HelperPWOCompliance pir126        JOIN ComplianceRule cr127          ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0128        WHERE129          pir.uid_PersonWantsOrg = @UID_Personwantsorg AND cr.isToGrantEver = 1)130      BEGIN131        SELECT @Decision = ''132        SELECT133          @Reason = '#LDS#At least one rule has been violated that requires approval for every exception. Rule: {0}.|OH|'134        SELECT @IsDecision = 0135        GOTO istEntschieden136      END137      SELECT @AnzahlVerstoss = count(*)138      FROM HelperPWOCompliance139      WHERE140        uid_PersonWantsOrg = @UID_Personwantsorg141      SELECT @AnzahlGenehmigt = count(*)142      FROM HelperPWOCompliance pir143      JOIN ComplianceRule cr144        ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND pir.uid_PersonWantsOrg = @UID_Personwantsorg AND cr.IsInActive = 0 AND cr.IsWorkingCopy145      = 0 AND cr.DetectRuleTypeForPWO <> 'AnyRule'146      JOIN personinBaseTree pin147        ON pin.uid_org = cr.uid_nonCompliance AND pin.uid_person = pir.uid_person AND pin.IsExceptiongranted = 1 AND pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay148      = 0 AND pin.XOrigin > 0149      IF(@AnzahlVerstoss = @AnzahlGenehmigt)150      BEGIN151        SELECT @Decision = 'True'152        SELECT153          @Reason = '#LDS#All affected compliance rules have been granted exceptions. Rule: {0}.|OH|'154        SELECT @IsDecision = 1155      END156      ELSE157      BEGIN158        SELECT @IsDecision = 0159      END160      IF EXISTS(161        SELECT TOP 1 1162        FROM HelperPWOCompliance163        WHERE164          uid_PersonWantsOrg = @UID_Personwantsorg)165      BEGIN166        IF EXISTS(167          SELECT TOP 1 1168          FROM HelperPWOCompliance pir169          JOIN ComplianceRule cr170            ON pir.uid_ComplianceRule = cr.uid_ComplianceRule AND cr.IsInActive = 0 AND cr.IsWorkingCopy = 0171          WHERE172            pir.uid_PersonWantsOrg = @UID_Personwantsorg AND isnull(cr.IsExceptionAllowed, 0) = 0)173        BEGIN174          SELECT @Decision = 'False'175          SELECT176            @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|'177          SELECT @IsDecision = 1178        END179      END180      istEntschieden:181      IF @IsDecision = 1182      BEGIN183        SELECT184          @SQLcmd = N 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)185        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg',186          @whereclause = @SQLcmd,187          @save = 1,188          @MethodName = 'MakeDecision',189          @GenProcID = @GenprocID,190          @ObjectKeysAffected = DEFAULT,191          @param1 = 'sa',192          @param2 = @decision,193          @param3 = @reason,194          @Retries = 3,195          @isToFreezeOnError = 1,196          @BasisObjectKey = @BasisObjectKey,197          @checkForExisting = 1,198          @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'199      END200      ELSE201      BEGIN202        EXEC QBM_PSessionContextSet 'GenProcID',203          @GenProcID204        UPDATE PWOHelperPWO205        SET decision = ''206        WHERE207          levelnumber = @levelnumber AND uid_personWantsOrg = @uid_personwantsorg AND Decision IN('R')208        UPDATE PWOHelperPWO209        SET Decision = ''210        FROM PWOHelperPWO ph211        JOIN(212        SELECT TOP 1 h.UID_ComplianceRule213        FROM PWOHelperPWO h214        JOIN ComplianceRule cr215          ON h.UID_ComplianceRule = cr.UID_ComplianceRule216        WHERE217          h.levelnumber = @levelnumber AND h.UID_PersonWantsOrg = @uid_personwantsorg AND h.Decision IN('+')218        ORDER BY isnull(cr.RuleSeverity, 0.0) DESC,219        cr.UID_ComplianceRule ASC) AS x220          ON x.UID_ComplianceRule = ph.UID_ComplianceRule221        WHERE222          ph.levelnumber = @levelnumber AND ph.UID_PersonWantsOrg = @uid_personwantsorg AND ph.Decision IN('+') AND @OrderState = 'OrderProlongate'223      END224      SELECT @ElementIndex += 1225    END226  END TRY227  BEGIN CATCH228    EXEC QBM_PSessionErrorAdd DEFAULT229    RAISERROR('',230    18,231    1)232      WITH NOWAIT233  END CATCH234  endLabel:235  EXEC QBM_PSessionContextSet 'GenProcID',236    @GenProcID_R237  RETURN238END
Open raw exported source
SQL ยท Raw45 lines
1    create   procedure CPL_ZITShopMakeDecisionOH (@SlotNumber int) as begin  declare @DecisionLevel int declare @UID_Personwantsorg varchar(38) 2declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'3)) declare @Decision nvarchar(16) declare @Reason nvarchar(255) declare @uid_person varchar(38) declare @Levelnumber int declare @SQLcmd nvarchar(max) 4declare @IsDecision bit declare @AnzahlVerstoss int declare @AnzahlGenehmigt int declare @BasisObjectKey varchar(138) declare @GenProcID_R varchar(38) 5= dbo.QBM_FGISessionContext('') declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent6 declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0)  declare @OrderState nvarchar(16) BEGIN TRY insert into7 @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from8 QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @ElementBuffer (UID1  , UID29  , UID3  , Int1  , ObjectKey1  , Int2  , LongIdent1  ) select distinct pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID,10 @CfgUseGenProcID) as GenProcID , pwo.uid_personordered, ph.levelnumber, pwo.XObjectKey , pwo.DecisionLevel, pwo.OrderState from @DBQueueCurrent p join11 personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel12 = ph.levelnumber  where ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-OH'  select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex13 <= @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @uid_person = bu.UID3  , @levelnumber = bu.Int1  , @BasisObjectKey14 = bu.ObjectKey1  , @DecisionLevel = bu.Int2 , @OrderState = bu.LongIdent1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex  select @IsDecision15 = 0 select @Decision = '' select @Reason = ''  if not exists (select top 1 1 from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg) 16begin   select @Decision = 'True'  select @Reason = '#LDS#No compliance rule has been violated., (missing CC step?) detected in Rule OH).|' select @IsDecision17 = 1 goto istEntschieden end   if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule18  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and isnull(cr.IsExceptionAllowed,0) = 0 ) begin select19 @Decision = 'False'  select @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|' select @IsDecision = 1 20goto istEntschieden end   if exists (select top 1 1 from PWOHelperPWO where uid_personWantsOrg = @UID_Personwantsorg and LevelNumber = @levelnumber and21 uid_personHead is null and decision = 'R' ) begin select @Decision = 'False'  select @Reason = '#LDS#There is no approver available for rule {0}.|OH|'22 select @IsDecision = 1 end  if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule23  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and cr.isToGrantEver = 1 ) begin select @Decision 24= '' select @Reason = '#LDS#At least one rule has been violated that requires approval for every exception. Rule: {0}.|OH|' select @IsDecision = 0 goto25 istEntschieden end   select @AnzahlVerstoss = count(*) from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg select @AnzahlGenehmigt26 = count(*) from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule and pir.uid_PersonWantsOrg = @UID_Personwantsorg27  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0    and cr.DetectRuleTypeForPWO <> 'AnyRule'  join personinBaseTree  pin on pin.uid_org  = cr.uid_nonCompliance28 and pin.uid_person = pir.uid_person and pin.IsExceptiongranted = 1 and pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0 and pin.XOrigin29 > 0 if (@AnzahlVerstoss = @AnzahlGenehmigt) begin select @Decision = 'True'  select @Reason = '#LDS#All affected compliance rules have been granted exceptions. Rule: {0}.|OH|'30 select @IsDecision = 1 end else  begin select @IsDecision = 0 end  if exists (select top 1 1 from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg31) begin   if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule  and cr.IsInActive32 = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and isnull(cr.IsExceptionAllowed,0) = 0 ) begin select @Decision = 'False'33  select @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|' select @IsDecision = 1 end end istEntschieden:34 if @IsDecision = 1 begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  35exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID36 , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , @param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey 37= @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  end38  else begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID update PWOHelperPWO set decision = '' where levelnumber = @levelnumber and uid_personWantsOrg39 = @uid_personwantsorg and Decision in ( 'R')  update PWOHelperPWO set Decision = '' from PWOHelperPWO ph join ( select top 1 h.UID_ComplianceRule from40 PWOHelperPWO h join ComplianceRule cr on h.UID_ComplianceRule = cr.UID_ComplianceRule where h.levelnumber = @levelnumber and h.UID_PersonWantsOrg = @uid_personwantsorg41 and h.Decision in ('+') order by isnull(cr.RuleSeverity, 0.0) desc, cr.UID_ComplianceRule asc ) as x on x.UID_ComplianceRule = ph.UID_ComplianceRule where42 ph.levelnumber = @levelnumber and ph.UID_PersonWantsOrg = @uid_personwantsorg and ph.Decision in ('+') and @OrderState = 'OrderProlongate'  end select43 @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet44 'GenProcID', @GenProcID_R return end 45