Back to OIM Explorer

dbo.QER_PGIQERWorkingMethod

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.208 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

Complete Source

SQL221 lines
1CREATE PROCEDURE QER_PGIQERWorkingMethod(2  @UID_PWODecisionMethod varchar(38),3  @Orderstate varchar(30),4  @UID_QERWorkingMethod varchar(38) OUTPUT5)6AS7BEGIN8  DECLARE @uid_PWODecisionSubMethod varchar(38) = NULL9  DECLARE @RevisionNumber int = 010  DECLARE @SQLCmd nvarchar(max)11  DECLARE @LayoutInformation nvarchar(max)12  DECLARE @old varchar(38)13  DECLARE @new varchar(38)14  DECLARE @DebugSwitch int = 015  DECLARE @ElementBuffer QBM_YCursorBuffer16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  SET XACT_ABORT OFF20  BEGIN TRY21    SELECT @UID_QERWorkingMethod = NULL22    SELECT TOP 1 @uid_PWODecisionSubMethod = CASE @Orderstate23    WHEN 'OrderProlongate' THEN24    isnull(m.UID_SubMethodOrderProlongate,25    m.UID_SubMethodOrderProduct)26    WHEN 'OrderUnsubscribe' THEN27    m.UID_SubMethodOrderUnsubscribe28    ELSE m.UID_SubMethodOrderProduct29    END30    FROM PWODecisionMethod m31    WHERE32      m.UID_PWODecisionMethod = @UID_PWODecisionMethod33    IF @uid_PWODecisionSubMethod IS NULL34    BEGIN35      GOTO EndLabel36    END37    SELECT TOP 1 @RevisionNumber = s.RevisionNumber38    FROM PWODecisionSubMethod s39    WHERE40      s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod41    SELECT TOP 1 @UID_QERWorkingMethod = m.UID_QERWorkingMethod42    FROM QERWorkingMethod m43    WHERE44      m.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod AND m.RevisionNumber = @RevisionNumber45    IF @UID_QERWorkingMethod > ' '46    BEGIN47      GOTO EndLabel48    END49    SELECT @UID_QERWorkingMethod = newid()50    INSERT INTO QERWorkingMethod(UID_QERWorkingMethod,51    Ident_PWODecisionSubMethod,52    XObjectKey,53    DaysToAbort,54    RevisionNumber,55    UID_PWODecisionSubMethod)56    SELECT57      @UID_QERWorkingMethod,58      s.Ident_PWODecisionSubMethod,59      dbo.QBM_FCVElementToObjectKey1('QERWorkingMethod',60      'UID_QERWorkingMethod',61      @UID_QERWorkingMethod),62      s.DaysToAbort,63      s.RevisionNumber,64      s.UID_PWODecisionSubMethod65    FROM PWODecisionSubMethod s66    WHERE67      s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod68    INSERT INTO QERWorkingStep(UID_QERWorkingStep,69    LevelNumber,70    Ident_PWODecisionStep,71    SubLevelNumber,72    DirectSteps,73    PositiveSteps,74    NegativeSteps,75    LevelDisplay,76    WhereClause,77    AutomaticReasonTrue,78    AutomaticReasonFalse,79    CountApprover,80    EscalationSteps,81    MinutesReminder,82    MinutesAutomaticDecision,83    AutomaticDecision,84    IsAdditionalAllowed,85    IsInsteadOfAllowed,86    IsToHideInHistory,87    IsNoAutoDecision,88    ObjectKeyOfAssignedOrg,89    UID_QERWorkingMethod,90    UID_PWODecisionRule,91    UID_AERoleFallBack,92    UID_DialogRichMailInsert,93    UID_DialogRichMailReminder,94    UID_DialogRichMailGrant,95    UID_DialogRichMailNoGrant,96    UID_DialogRichMailEscalate,97    UID_DialogRichMailFromDelegat,98    UID_DialogRichMailToDelegat,99    UID_PWOStateFinalSuccess,100    UID_PWOStateFinalError,101    XObjectKey,102    UID_PWODecisionStep,103    IgnoreNoDecideForPerson,104    EscalateIfNoApprover,105    ApproveReasonType,106    DenyReasonType)107    SELECT108      UID_QERWorkingStep,109      LevelNumber,110      Ident_PWODecisionStep,111      SubLevelNumber,112      DirectSteps,113      PositiveSteps,114      NegativeSteps,115      LevelDisplay,116      WhereClause,117      AutomaticReasonTrue,118      AutomaticReasonFalse,119      CountApprover,120      EscalationSteps,121      MinutesReminder,122      MinutesAutomaticDecision,123      AutomaticDecision,124      IsAdditionalAllowed,125      IsInsteadOfAllowed,126      IsToHideInHistory,127      IsNoAutoDecision,128      ObjectKeyOfAssignedOrg,129      @UID_QERWorkingMethod,130      UID_PWODecisionRule,131      UID_AERoleFallBack,132      UID_DialogRichMailInsert,133      UID_DialogRichMailReminder,134      UID_DialogRichMailGrant,135      UID_DialogRichMailNoGrant,136      UID_DialogRichMailEscalate,137      UID_DialogRichMailFromDelegat,138      UID_DialogRichMailToDelegat,139      UID_PWOStateFinalSuccess,140      UID_PWOStateFinalError,141      dbo.QBM_FCVElementToObjectKey1('QERWorkingStep',142      'UID_QERWorkingStep',143      x.UID_QERWorkingStep),144      UID_PWODecisionStep,145      IgnoreNoDecideForPerson,146      EscalateIfNoApprover,147      ApproveReasonType,148      DenyReasonType149    FROM(150    SELECT151      newid() AS UID_QERWorkingStep,152      s.*153    FROM PWODecisionStep s154    WHERE155      s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod) AS x156    INSERT INTO @ElementBuffer(uid1,157    uid2)158    SELECT159      s.UID_PWODecisionStep,160      s.UID_QERWorkingStep161    FROM QERWorkingStep s162    WHERE163      s.UID_QERWorkingMethod = @UID_QERWorkingMethod164    UNION165    SELECT166      @uid_PWODecisionSubMethod,167      @UID_QERWorkingMethod168    SELECT @ElementCount = @@ROWCOUNT169    SELECT TOP 1 @LayoutInformation = s.LayoutInformation170    FROM PWODecisionSubMethod s171    WHERE172      s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod173    IF @DebugSwitch > 0174    BEGIN175      print 'Layout alt' + @LayoutInformation176    END177    SELECT @ElementIndex = 1178    WHILE @ElementIndex <= @ElementCount179    BEGIN180      SELECT181        TOP 1 @old = bu.uid1,182        @new = bu.uid2183      FROM @ElementBuffer bu184      WHERE185        bu.ElementIndex = @ElementIndex186      IF @DebugSwitch > 0187      BEGIN188        print '@old ' + @old print '@new ' + @new189      END190      SELECT191        @LayoutInformation = replace(@LayoutInformation,192        @old,193        @new)194      SELECT @ElementIndex += 1195    END196    IF @DebugSwitch > 0197    BEGIN198      print 'Layout neu' + @LayoutInformation199    END200    EXEC QBM_PSessionContextSet 'QER_PGIQERWorkingMethod',201    'TRUE'202    UPDATE QERWorkingMethod203    SET LayoutInformation = @LayoutInformation204    WHERE205      UID_QERWorkingMethod = @UID_QERWorkingMethod206    EXEC QBM_PSessionContextSet 'QER_PGIQERWorkingMethod',207    ''208  END TRY209  BEGIN CATCH210    EXEC QBM_PSessionErrorAdd DEFAULT211    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()212    RAISERROR(@Rethrow,213    18,214    1)215      WITH NOWAIT216  END CATCH217  EndLabel:218  EXEC QBM_PSessionContextSet 'GenProcID',219    @GenProcID_R220  RETURN221END
Open raw exported source
SQL ยท Raw34 lines
1   create   procedure QER_PGIQERWorkingMethod (@UID_PWODecisionMethod varchar(38) , @Orderstate varchar(30) , @UID_QERWorkingMethod varchar(38) 2output ) as begin declare @uid_PWODecisionSubMethod varchar(38) = null declare @RevisionNumber int = 0 declare @SQLCmd nvarchar(max) declare @LayoutInformation3 nvarchar(max) declare @old varchar(38) declare @new varchar(38) declare @DebugSwitch int = 0 declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount4 int declare @ElementIndex int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') SET XACT_ABORT OFF BEGIN TRY select @UID_QERWorkingMethod5 = null select top 1 @uid_PWODecisionSubMethod = case @Orderstate when 'OrderProlongate' then isnull(m.UID_SubMethodOrderProlongate, m.UID_SubMethodOrderProduct6) when 'OrderUnsubscribe' then m.UID_SubMethodOrderUnsubscribe else  m.UID_SubMethodOrderProduct end from PWODecisionMethod m where m.UID_PWODecisionMethod7 = @UID_PWODecisionMethod if @uid_PWODecisionSubMethod is null begin goto EndLabel end select top 1 @RevisionNumber = s.RevisionNumber from PWODecisionSubMethod8 s where s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod select top 1 @UID_QERWorkingMethod = m.UID_QERWorkingMethod from QERWorkingMethod m where9 m.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod and m.RevisionNumber = @RevisionNumber if @UID_QERWorkingMethod > ' ' begin  goto EndLabel end 10select @UID_QERWorkingMethod = newid() insert into QERWorkingMethod (UID_QERWorkingMethod, Ident_PWODecisionSubMethod , XObjectKey , DaysToAbort, RevisionNumber11, UID_PWODecisionSubMethod ) select @UID_QERWorkingMethod, s.Ident_PWODecisionSubMethod , dbo.QBM_FCVElementToObjectKey1('QERWorkingMethod', 'UID_QERWorkingMethod'12, @UID_QERWorkingMethod) , s.DaysToAbort, s.RevisionNumber, s.UID_PWODecisionSubMethod from PWODecisionSubMethod s where s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod13  insert into QERWorkingStep (UID_QERWorkingStep, LevelNumber, Ident_PWODecisionStep, SubLevelNumber, DirectSteps, PositiveSteps, NegativeSteps, LevelDisplay14, WhereClause, AutomaticReasonTrue , AutomaticReasonFalse, CountApprover, EscalationSteps, MinutesReminder, MinutesAutomaticDecision, AutomaticDecision15, IsAdditionalAllowed, IsInsteadOfAllowed , IsToHideInHistory, IsNoAutoDecision, ObjectKeyOfAssignedOrg, UID_QERWorkingMethod, UID_PWODecisionRule, UID_AERoleFallBack16, UID_DialogRichMailInsert, UID_DialogRichMailReminder, UID_DialogRichMailGrant, UID_DialogRichMailNoGrant , UID_DialogRichMailEscalate, UID_DialogRichMailFromDelegat17, UID_DialogRichMailToDelegat, UID_PWOStateFinalSuccess, UID_PWOStateFinalError , XObjectKey, UID_PWODecisionStep , IgnoreNoDecideForPerson, EscalateIfNoApprover18 , ApproveReasonType, DenyReasonType ) select UID_QERWorkingStep, LevelNumber, Ident_PWODecisionStep, SubLevelNumber, DirectSteps, PositiveSteps, NegativeSteps19, LevelDisplay, WhereClause, AutomaticReasonTrue , AutomaticReasonFalse, CountApprover, EscalationSteps, MinutesReminder, MinutesAutomaticDecision, AutomaticDecision20, IsAdditionalAllowed, IsInsteadOfAllowed , IsToHideInHistory, IsNoAutoDecision, ObjectKeyOfAssignedOrg, @UID_QERWorkingMethod, UID_PWODecisionRule, UID_AERoleFallBack21, UID_DialogRichMailInsert, UID_DialogRichMailReminder, UID_DialogRichMailGrant, UID_DialogRichMailNoGrant , UID_DialogRichMailEscalate, UID_DialogRichMailFromDelegat22, UID_DialogRichMailToDelegat, UID_PWOStateFinalSuccess, UID_PWOStateFinalError , dbo.QBM_FCVElementToObjectKey1('QERWorkingStep', 'UID_QERWorkingStep'23, x.UID_QERWorkingStep), UID_PWODecisionStep , IgnoreNoDecideForPerson, EscalateIfNoApprover , ApproveReasonType, DenyReasonType from ( select newid() 24as UID_QERWorkingStep , s.* from PWODecisionStep s where s.UID_PWODecisionSubMethod = @uid_PWODecisionSubMethod ) as x insert into @ElementBuffer(uid1,25 uid2) select s.UID_PWODecisionStep, s.UID_QERWorkingStep from QERWorkingStep s where s.UID_QERWorkingMethod = @UID_QERWorkingMethod union select @uid_PWODecisionSubMethod26, @UID_QERWorkingMethod select @ElementCount = @@ROWCOUNT select top 1 @LayoutInformation = s.LayoutInformation from PWODecisionSubMethod s where s.UID_PWODecisionSubMethod27 = @uid_PWODecisionSubMethod if @DebugSwitch > 0 begin print 'Layout alt' + @LayoutInformation end select @ElementIndex = 1 while @ElementIndex <= @ElementCount28 begin select top 1 @old = bu.uid1 , @new = bu.uid2 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin print '@old '29 + @old print '@new ' + @new end select @LayoutInformation = replace(@LayoutInformation, @old, @new) select @ElementIndex += 1 end  if @DebugSwitch > 030 begin print 'Layout neu' + @LayoutInformation end exec QBM_PSessionContextSet 'QER_PGIQERWorkingMethod', 'TRUE' update QERWorkingMethod set LayoutInformation31 = @LayoutInformation where UID_QERWorkingMethod = @UID_QERWorkingMethod   exec QBM_PSessionContextSet 'QER_PGIQERWorkingMethod', '' END TRY BEGIN CATCH32 exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH 33EndLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 34