Back to OIM Explorer

dbo.QER_PITShopOrderResetOneLevel

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 1.938 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_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

SQL65 lines
1CREATE PROCEDURE QER_PITShopOrderResetOneLevel(2  @uid_PersonwantsOrg varchar(38),3  @NewLevel int,4  @GenProcID varchar(38)5)6AS7BEGIN8  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')9  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')10  DECLARE @WhereclauseForMail nvarchar(1000)11  SET XACT_ABORT OFF12  BEGIN TRY13    IF EXISTS(14      SELECT TOP 1 115      FROM PWOHelperPWO h16      JOIN QERWorkingStep s17        ON h.UID_QERWorkingStep = s.UID_QERWorkingStep18      JOIN PWODecisionRule r19        ON r.UID_PWODecisionRule = s.UID_PWODecisionRule AND r.UID_PWODecisionRule NOT IN('CPL-PWODecisionRule-OC',20    'CPL-PWODecisionRule-OH')21      WHERE22        h.UID_PersonWantsOrg = @uid_PersonwantsOrg AND h.LevelNumber = @NewLevel AND(h.decision > ' ' OR h.UID_PersonInsteadOf > ' ' OR h.uid_PersonAdditional23    > ' '))24    BEGIN25      UPDATE PWOHelperPWO26      SET Decision = '',27      UID_PersonInsteadOf = NULL,28      UID_PersonAdditional = NULL29      FROM PWOHelperPWO h30      JOIN QERWorkingStep s31        ON h.UID_QERWorkingStep = s.UID_QERWorkingStep32      JOIN PWODecisionRule r33        ON r.UID_PWODecisionRule = s.UID_PWODecisionRule AND r.UID_PWODecisionRule NOT IN('CPL-PWODecisionRule-OC',34      'CPL-PWODecisionRule-OH')35      WHERE36        h.UID_PersonWantsOrg = @uid_personwantsorg AND h.LevelNumber = @NewLevel AND(h.decision > ' ' OR h.UID_PersonInsteadOf > ' ' OR h.uid_PersonAdditional37      > ' ')38    END39    IF EXISTS(40      SELECT TOP 1 141      FROM PWOHelperPWO h42      WHERE43        h.UID_PersonWantsOrg = @uid_PersonwantsOrg AND h.LevelNumber = @NewLevel AND(h.IsFromDelegation = 1))44    BEGIN45      DELETE PWOHelperPWO46      FROM PWOHelperPWO h47      WHERE48        h.IsFromDelegation = 1 AND h.UID_PersonWantsOrg = @uid_personwantsorg AND h.LevelNumber = @NewLevel49    END50  END TRY51  BEGIN CATCH52    EXEC QBM_PSessionErrorAdd DEFAULT53    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()54    RAISERROR(@Rethrow,55    18,56    1)57      WITH NOWAIT58  END CATCH59  ende:60  EXEC QBM_PSessionContextSet 'GenProcID',61    @GenProcID_R62  EXEC QBM_PSessionContextSet 'XUser',63    @XUser_R64  RETURN65END
Open raw exported source
SQL ยท Raw14 lines
1   create   procedure QER_PITShopOrderResetOneLevel (@uid_PersonwantsOrg varchar(38) , @NewLevel int , @GenProcID varchar(38)  ) as begin  declare2 @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @WhereclauseForMail3 nvarchar(1000) SET XACT_ABORT OFF BEGIN TRY if exists (select top 1 1 from PWOHelperPWO h join QERWorkingStep s on h.UID_QERWorkingStep = s.UID_QERWorkingStep4 join PWODecisionRule r on r.UID_PWODecisionRule = s.UID_PWODecisionRule and r.UID_PWODecisionRule not in ('CPL-PWODecisionRule-OC' , 'CPL-PWODecisionRule-OH'5 ) where h.UID_PersonWantsOrg = @uid_PersonwantsOrg and h.LevelNumber = @NewLevel and ( h.decision > ' ' or h.UID_PersonInsteadOf > ' ' or h.uid_PersonAdditional6 > ' ' ) ) begin update PWOHelperPWO set Decision = '' , UID_PersonInsteadOf = null , UID_PersonAdditional = null from PWOHelperPWO h join QERWorkingStep7 s on h.UID_QERWorkingStep = s.UID_QERWorkingStep join PWODecisionRule r on r.UID_PWODecisionRule = s.UID_PWODecisionRule and r.UID_PWODecisionRule not8 in ('CPL-PWODecisionRule-OC' , 'CPL-PWODecisionRule-OH' ) where h.UID_PersonWantsOrg = @uid_personwantsorg and h.LevelNumber = @NewLevel and ( h.decision9 > ' ' or h.UID_PersonInsteadOf > ' ' or h.uid_PersonAdditional > ' ' ) end if exists (select top 1 1 from PWOHelperPWO h where h.UID_PersonWantsOrg = 10@uid_PersonwantsOrg and h.LevelNumber = @NewLevel and ( h.IsFromDelegation = 1 ) ) begin delete PWOHelperPWO from PWOHelperPWO h where h.IsFromDelegation11 = 1 and h.UID_PersonWantsOrg = @uid_personwantsorg and h.LevelNumber = @NewLevel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow12 varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH  ende: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R13 exec QBM_PSessionContextSet 'XUser', @XUser_R return end 14