Back to OIM Explorer

dbo.QER_ZITShopDeleteClosed

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22; Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22; Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22; Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 29

Source: sandbox-db sys.sql_modules

Source size: 5.747 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22
  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22
  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22
  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 29
  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 29
  • Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 35
  • References QBM_PDBQueueInsert_Single

Typed Edges

  • queues DBQueue task QER_ZITShopDeleteClosed at line 22 Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 22
  • queues DBQueue task QER_ZITShopDeleteClosed at line 29 Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 29
  • queues DBQueue task QER_ZITShopDeleteClosed at line 35 Single DBQueue insert -> QER-K-ITShopDeleteClosed / QER_ZITShopDeleteClosed at line 35
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FCVStringToDatetime source text reference
  • references source dbo.QBM_FCVStringToInt source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QER-K-ITShopDeleteClosed -> QER_ZITShopDeleteClosed QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed', '', '', @GenProcID goto EndLabel end if @ProcessState_PropertyLog = 0 begin if @DebugSwitch > 0 begin print 'Propertylog ist aus' end goto endLabel end if @DebugSwitch > 0 begin print '…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL197 lines
1CREATE PROCEDURE QER_ZITShopDeleteClosed(2  @Slotnumber int,3  @Dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @MaximumLifetime int = 9999910  DECLARE @MaximumRowcount int = 500011  DECLARE @DeletePWO BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed'))12  DECLARE @DeleteShoppingCart BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts'))13  DECLARE @AbortedLifetime int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Aborted'),14  @MaximumLifetime)15  DECLARE @DismissedLifetime int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Dismissed'),16  @MaximumLifetime)17  DECLARE @UnsubscribedLifetime int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Unsubscribed'),18  @MaximumLifetime)19  DECLARE @DeleteShoppingCarts_Immediately BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts\DeleteEmptyShoppingCartsImmediately'20  ))21  DECLARE @DeleteShoppingCarts_LifeTime int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts\LifeTime'),22  @MaximumLifetime)23  DECLARE @ProcessState_PropertyLog int = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog'),24  0)25  DECLARE @DebugSwitch int = 026  DECLARE @XUser nvarchar(64) = object_name(@@procid)27  DECLARE @XDate datetime = getutcdate()28  DECLARE @GenProcID varchar(38) = newid()29  DECLARE @RowsAffected int = 030  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')31  DECLARE @updateCount int = 032  SET XACT_ABORT OFF33  BEGIN TRY34    SET nocount35      ON36    EXEC QBM_PSessionContextSet 'GenProcID',37      @GenProcID38    IF @DeletePWO = 0 AND @DeleteShoppingCart = 039    BEGIN40      IF @DebugSwitch > 041      BEGIN42        print 'nichts zu tun'43      END44      GOTO EndLabel45    END46    UPDATE DialogColumn47    SET IsToWatchDelete = 1,48    XDateUpdated = @Xdate,49    XUserUpdated = @XUser50    FROM DialogColumn c51    JOIN DialogTable t52      WITH(readpast)53      ON c.UID_DialogTable = t.UID_DialogTable54    WHERE55      t.TableName IN('PWODecisionHistory',56    'PersonWantsOrg',57    'Delegation',58    'PAGAccessorder',59    'AccProductParamCategory',60    'QERUniversalSubstitute',61    'ShoppingCartOrder') AND c.IsToWatchDelete = 0 AND c.ColumnName NOT LIKE 'X%' AND @ProcessState_PropertyLog = 162    SELECT @updateCount = @@rowcount63    UPDATE DialogColumn64    SET IsToWatchDelete = 1,65    XDateUpdated = @Xdate,66    XUserUpdated = @XUser67    FROM DialogColumn c68    JOIN DialogTable t69      WITH(readpast)70      ON c.UID_DialogTable = t.UID_DialogTable71    JOIN(72    VALUES('AccProductParameter', 'UID_AccProductParameter'),73    ('AccProductParameter', 'UID_AccProductParamCategory'),74    ('AccProductParameter', 'ColumnName'),75    ('AccProductParameter', 'DisplayValue'),76    ('DialogParameterSet', 'ObjectKeyUsedBy'),77    ('DialogParameterSet', 'UID_DialogParameterSetParent'),78    ('DialogParameter', 'ParameterName'),79    ('DialogParameter', 'DisplayName'),80    ('DialogParameter', 'ParameterValue'),81    ('DialogParameter', 'UID_DialogParameterSet')) AS v(TableName,82    ColumnName)83      ON t.TableName = v.TableName AND c.ColumnName = v.ColumnName AND c.IsToWatchDelete = 0 AND @ProcessState_PropertyLog = 184    SELECT @updateCount += @@rowcount85    IF @updateCount > 086    BEGIN87      IF @DebugSwitch > 088      BEGIN89        print 'IsToWatchDelete war noch zu setzen'90      END91      EXEC QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed',92      '',93      '',94        @GenProcID95      GOTO EndLabel96    END97    IF @ProcessState_PropertyLog = 098    BEGIN99      IF @DebugSwitch > 0100      BEGIN101        print 'Propertylog ist aus'102      END103      GOTO endLabel104    END105    IF @DebugSwitch > 0106    BEGIN107      print 'IsToWatchDelete ist gesetzt'108    END109    DELETE PersonWantsOrg110    FROM PersonWantsOrg pw111    WHERE112      pw.UID_PersonWantsOrg IN(113    SELECT TOP(@MaximumRowcount) pwo.UID_PersonWantsOrg114    FROM PersonWantsOrg pwo115    WHERE116      pwo.OrderState IN('Aborted', 'Dismissed', 'Unsubscribed') AND dateadd(dd, CASE pwo.OrderState117    WHEN 'Aborted' THEN118    @AbortedLifetime119    WHEN 'Dismissed' THEN120    @DismissedLifetime121    WHEN 'Unsubscribed' THEN122    @UnsubscribedLifetime123    ELSE @MaximumLifetime124    END, CASE125    WHEN pwo.DateDeactivated IS NULL THEN126    pwo.XDateUpdated127    ELSE pwo.DateDeactivated128    END) < dbo.QBM_FCVStringToDatetime(Convert(varchar(10), getutcdate(), 120)) AND NOT EXISTS(129    SELECT TOP 1 1130    FROM PersonWantsOrg c131    WHERE132      c.UID_PersonWantsOrgParent = pwo.UID_PersonWantsOrg))133    SELECT @RowsAffected = @@ROWCOUNT134    IF @DebugSwitch > 0135    BEGIN136      print 'pwo gelöscht : ' + str(@RowsAffected)137    END138    IF @RowsAffected = @MaximumRowcount139    BEGIN140      EXEC QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed',141      '',142      '',143        @GenProcID144      IF @DebugSwitch > 0145      BEGIN146        print 'neu eingestellt nach PWO'147      END148      GOTO endLabel149    END150    DELETE ShoppingCartOrder151    FROM ShoppingCartOrder so152    WHERE153      so.UID_ShoppingCartOrder IN(154    SELECT155      DISTINCT TOP(@MaximumRowcount - @RowsAffected + 1) sco.UID_ShoppingCartOrder156    FROM ShoppingCartOrder sco157    LEFT158    OUTER159    JOIN ShoppingCartItem si160      ON sco.UID_ShoppingCartOrder = si.UID_ShoppingCartOrder161    LEFT162    OUTER163    JOIN PersonWantsOrg pwo164      ON sco.UID_ShoppingCartOrder = pwo.UID_ShoppingCartOrder165    WHERE166      (pwo.UID_ShoppingCartOrder IS NULL AND si.UID_ShoppingCartOrder IS NULL AND @DeleteShoppingCarts_Immediately = 1) OR(si.UID_ShoppingCartOrder167    IS NULL AND dateadd(dd, @DeleteShoppingCarts_LifeTime, sco.XDateUpdated) < getutcdate()))168    SELECT @RowsAffected += @@ROWCOUNT169    IF @DebugSwitch > 0170    BEGIN171      print 'pwo  + SCO gelöscht : ' + str(@RowsAffected)172    END173    IF @RowsAffected >= @MaximumRowcount174    BEGIN175      EXEC QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed',176      '',177      '',178        @GenProcID179      IF @DebugSwitch > 0180      BEGIN181        print 'Neu eingestellt nach ShoppingCart'182      END183      GOTO endLabel184    END185  END TRY186  BEGIN CATCH187    EXEC QBM_PSessionErrorAdd DEFAULT188    RAISERROR('',189    18,190    1)191      WITH NOWAIT192  END CATCH193  endLabel:194  EXEC QBM_PSessionContextSet 'GenProcID',195    @GenProcID_R196  RETURN197END
Open raw exported source
SQL · Raw38 lines
1     create   procedure QER_ZITShopDeleteClosed ( @Slotnumber int , @Dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcIDDummy varchar(38) ) AS2 begin declare @MaximumLifetime int = 99999 declare @MaximumRowcount int = 5000  declare @DeletePWO bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue3('QER\ITShop\DeleteClosed')) declare @DeleteShoppingCart bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts')) declare4 @AbortedLifetime int = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Aborted') , @MaximumLifetime) declare @DismissedLifetime5 int = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Dismissed') , @MaximumLifetime) declare @UnsubscribedLifetime int = 6dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteClosed\Unsubscribed') , @MaximumLifetime) declare @DeleteShoppingCarts_Immediately7 bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts\DeleteEmptyShoppingCartsImmediately')) declare @DeleteShoppingCarts_LifeTime8 int = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('QER\ITShop\DeleteShoppingCarts\LifeTime'), @MaximumLifetime) declare @ProcessState_PropertyLog9 int = dbo.QBM_FCVStringToInt( dbo.QBM_FGIConfigparmValue('Common\ProcessState\PropertyLog') , 0) declare @DebugSwitch int = 0 declare @XUser nvarchar(6410) = object_name(@@procid) declare @XDate datetime = getutcdate() declare @GenProcID varchar(38) = newid() declare @RowsAffected int = 0 declare @GenProcID_R11 varchar(38) = dbo.QBM_FGISessionContext('') declare @updateCount int = 0 SET XACT_ABORT OFF BEGIN TRY set nocount on exec QBM_PSessionContextSet 'GenProcID'12, @GenProcID if @DeletePWO = 0 and @DeleteShoppingCart = 0 begin if @DebugSwitch > 0 begin print 'nichts zu tun' end goto EndLabel end update DialogColumn13 set IsToWatchDelete = 1  , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t with (readpast) on c.UID_DialogTable =14 t.UID_DialogTable where t.TableName in ('PWODecisionHistory' , 'PersonWantsOrg' , 'Delegation'  , 'PAGAccessorder'   , 'AccProductParamCategory' , 'QERUniversalSubstitute'15   , 'ShoppingCartOrder' ) and c.IsToWatchDelete = 0 and c.ColumnName not like 'X%'  and @ProcessState_PropertyLog = 1 select @updateCount = @@rowcount16 update DialogColumn set IsToWatchDelete = 1  , XDateUpdated = @Xdate , XUserUpdated = @XUser from DialogColumn c join DialogTable t with (readpast) on17 c.UID_DialogTable = t.UID_DialogTable join( values ('AccProductParameter', 'UID_AccProductParameter') , ('AccProductParameter', 'UID_AccProductParamCategory'18) , ('AccProductParameter', 'ColumnName') , ('AccProductParameter', 'DisplayValue') , ('DialogParameterSet', 'ObjectKeyUsedBy') , ('DialogParameterSet'19, 'UID_DialogParameterSetParent') , ('DialogParameter', 'ParameterName') , ('DialogParameter', 'DisplayName') , ('DialogParameter', 'ParameterValue') ,20 ('DialogParameter', 'UID_DialogParameterSet')  ) as v (TableName, ColumnName) on t.TableName = v.TableName and c.ColumnName = v.ColumnName and c.IsToWatchDelete21 = 0 and @ProcessState_PropertyLog = 1 select @updateCount += @@rowcount  if @updateCount > 0 begin if @DebugSwitch > 0 begin print 'IsToWatchDelete war noch zu setzen'22 end  exec QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed', '', '', @GenProcID goto EndLabel end if @ProcessState_PropertyLog = 0 begin if @DebugSwitch23 > 0 begin print 'Propertylog ist aus' end goto endLabel end if @DebugSwitch > 0 begin print 'IsToWatchDelete ist gesetzt' end delete PersonWantsOrg from24 PersonWantsOrg pw where pw.UID_PersonWantsOrg in ( select top (@MaximumRowcount) pwo.UID_PersonWantsOrg from PersonWantsOrg pwo where pwo.OrderState in25 ('Aborted', 'Dismissed', 'Unsubscribed') and dateadd(dd , case pwo.OrderState when 'Aborted' then @AbortedLifetime when 'Dismissed' then @DismissedLifetime26 when 'Unsubscribed' then @UnsubscribedLifetime else @MaximumLifetime end , case when pwo.DateDeactivated is null then pwo.XDateUpdated else pwo.DateDeactivated27 end ) < dbo.QBM_FCVStringToDatetime(Convert(varchar(10), getutcdate(), 120))  and not exists (select top 1 1 from PersonWantsOrg c where c.UID_PersonWantsOrgParent28 = pwo.UID_PersonWantsOrg ) ) select @RowsAffected = @@ROWCOUNT if @DebugSwitch > 0 begin print 'pwo gelöscht : ' + str(@RowsAffected) end if @RowsAffected29 = @MaximumRowcount begin exec QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed', '', '', @GenProcID if @DebugSwitch > 0 begin print 'neu eingestellt nach PWO'30 end goto endLabel end delete ShoppingCartOrder from ShoppingCartOrder so where so.UID_ShoppingCartOrder in ( select distinct top (@MaximumRowcount - @RowsAffected31 + 1) sco.UID_ShoppingCartOrder  from ShoppingCartOrder sco left outer join ShoppingCartItem si on sco.UID_ShoppingCartOrder = si.UID_ShoppingCartOrder32 left outer join PersonWantsOrg pwo on sco.UID_ShoppingCartOrder = pwo.UID_ShoppingCartOrder where ( pwo.UID_ShoppingCartOrder is null and si.UID_ShoppingCartOrder33 is null and @DeleteShoppingCarts_Immediately = 1 ) or (si.UID_ShoppingCartOrder is null and dateadd(dd, @DeleteShoppingCarts_LifeTime, sco.XDateUpdated34) < getutcdate() ) ) select @RowsAffected += @@ROWCOUNT if @DebugSwitch > 0 begin print 'pwo  + SCO gelöscht : ' + str(@RowsAffected) end if @RowsAffected35 >= @MaximumRowcount begin exec QBM_PDBQueueInsert_Single 'QER-K-ITShopDeleteClosed', '', '', @GenProcID if @DebugSwitch > 0 begin print 'Neu eingestellt nach ShoppingCart'36 end goto endLabel end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet37 'GenProcID', @GenProcID_R return end 38