Back to OIM Explorer

dbo.QER_ZITShopCheck

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-ShoppingRackCheckValid / QER_ZITShopCheckValid at line 10; Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 10; Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 10; Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 14

Source: sandbox-db sys.sql_modules

Source size: 3.721 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

  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckValid / QER_ZITShopCheckValid at line 10
  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 10
  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 10
  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 14
  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 14
  • Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 23
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZITShopCheckValid at line 10 Bulk DBQueue insert -> QER-K-ShoppingRackCheckValid / QER_ZITShopCheckValid at line 10
  • queues DBQueue task QER_ZITShopCheckDecision at line 10 Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 10
  • queues DBQueue task QER_ZITShopCheckDecision at line 14 Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 14
  • queues DBQueue task QER_ZITShopCheckDecision at line 23 Bulk DBQueue insert -> QER-K-ShoppingRackCheckDecision / QER_ZITShopCheckDecision at line 23
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QER-K-ShoppingRackCheckValid -> QER_ZITShopCheckValid QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckValid', @DBQueueElements_01 declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, x.GenProcID from (select distinct pw…
  • queues DBQueue task QER-K-ShoppingRackCheckDecision -> QER_ZITShopCheckDecision QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision' , @DBQueueElements_02 declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, x.GenProcID from (select pwo.UID…

References

Referenced By

Complete Source

SQL126 lines
1CREATE PROCEDURE QER_ZITShopCheck(2  @Slotnumber int,3  @Dummy1 varchar(38),4  @Dummy2 varchar(38),5  @Dummy3 varchar(38)6)7AS8BEGIN9  DECLARE @GenProcID varchar(38) = NULL10  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))11  BEGIN TRY12    SELECT TOP 1 @GenProcID = cu.GenProcID13    FROM QBMDBQueueCurrent cu14      WITH(readpast)15    WHERE16      cu.SlotNumber = @Slotnumber17    IF @GenProcID IS NULL18    BEGIN19      SELECT @GenProcID = NEWID()20    END21    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw22    INSERT INTO @DBQueueElements_01(object,23    subobject,24    genprocid)25    SELECT26      x.uid,27      NULL,28      x.GenProcID29    FROM(30    SELECT31      pwo.UID_PersonOrdered AS uid,32      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID33    FROM personwantsorg pwo34    WHERE35      pwo.OrderState = 'GRANTED' AND isnull(pwo.validFrom, '1899-12-30') < getUTCDate() AND isnull(pwo.validUntil,36    '2200-01-01') > getUTCDate()) AS x37    UNION38    SELECT39      x.uid,40      NULL,41      x.GenProcID42    FROM(43    SELECT44      pwo.UID_PersonOrdered AS uid,45      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID46    FROM personwantsorg pwo47    WHERE48      pwo.validUntil < getUTCDate() AND pwo.OrderState IN('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe',49    'Waiting')) AS x50    UNION51    SELECT52      x.uid,53      NULL,54      x.GenProcID55    FROM(56    SELECT57      pwo.UID_PersonOrdered AS uid,58      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID59    FROM personwantsorg pwo60    WHERE61      pwo.OrderState IN('OrderProduct') AND pwo.validUntil < getUTCDate()) AS x62    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckValid',63      @DBQueueElements_0164    DECLARE @DBQueueElements_02 QBM_YDBQueueRaw65    INSERT INTO @DBQueueElements_02(object,66    subobject,67    genprocid)68    SELECT69      x.uid,70      NULL,71      x.GenProcID72    FROM(73    SELECT74      DISTINCT pwo.UID_PersonWantsOrg AS uid,75      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID76    FROM Personwantsorg pwo77    JOIN PWOHelperPWO h78      ON pwo.uid_Personwantsorg = h.uid_Personwantsorg79    WHERE80      (h.NextReminder < getutcdate() OR h.NextAutomaticDecision < getutcdate()) AND pwo.UID_QERWorkingMethod > ' ') AS x81    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision',82      @DBQueueElements_0283    DECLARE @DBQueueElements_03 QBM_YDBQueueRaw84    INSERT INTO @DBQueueElements_03(object,85    subobject,86    genprocid)87    SELECT88      x.uid,89      NULL,90      x.GenProcID91    FROM(92    SELECT93      pwo.UID_PersonWantsOrg AS uid,94      dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID95    FROM Personwantsorg pwo96    JOIN QERWorkingMethod wm97      ON pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod98    WHERE99      dateadd(dd, wm.DaysToAbort, pwo.OrderDate) < getutcdate() AND isnull(wm.DaysToAbort, 0) > 0 AND pwo.Orderstate = 'OrderProduct'100    UNION101    SELECT102      pwo.UID_PersonWantsOrg,103      max(dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID)) AS GenProcID104    FROM PersonWantsOrg pwo105    JOIN PWODecisionHistory h1106      ON pwo.UID_PersonWantsOrg = h1.UID_PersonWantsOrg107    JOIN QERWorkingMethod wm108      ON pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod AND wm.DaysToAbort > 0109    WHERE110      pwo.OrderState IN('OrderUnsubscribe', 'OrderProlongate') AND((h1.DecisionType = 'Unsubscribe' AND pwo.OrderState = 'OrderUnsubscribe' AND111    h1.OrderState = 'OrderUnsubscribe') OR(h1.DecisionType = 'Prolongate' AND pwo.OrderState = 'OrderProlongate' AND h1.OrderState = 'OrderProlongate'112    ))113    GROUP BY pwo.UID_PersonWantsOrg,114    wm.DaysToAbort115    HAVING dateadd(dd, wm.DaysToAbort, isnull(MAX(h1.XDateInserted), '1899-12-30')) < GETUTCDATE()) AS x116    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision',117      @DBQueueElements_03118  END TRY119  BEGIN CATCH120    EXEC QBM_PSessionErrorAdd DEFAULT121    RAISERROR('',122    18,123    1)124      WITH NOWAIT125  END CATCH126END
Open raw exported source
SQL · Raw25 lines
1  create   procedure QER_ZITShopCheck ( @Slotnumber int , @Dummy1 varchar(38) , @Dummy2 varchar(38) , @Dummy3 varchar(38) ) AS begin  declare @GenProcID2 varchar(38) = null declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) BEGIN 3TRY  select top 1 @GenProcID = cu.GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @Slotnumber if @GenProcID is null begin select4 @GenProcID = NEWID() end declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid)  select x.uid, null5, x.GenProcID from (select pwo.UID_PersonOrdered as uid, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) as GenProcID from personwantsorg6 pwo where pwo.OrderState = 'GRANTED' and isnull(pwo.validFrom , '1899-12-30') < getUTCDate() and isnull(pwo.validUntil , '2200-01-01') > getUTCDate() 7) as x union  select x.uid, null, x.GenProcID from (select pwo.UID_PersonOrdered as uid, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID8) as GenProcID from personwantsorg pwo where pwo.validUntil < getUTCDate()      and pwo.OrderState in ('Assigned', 'Granted', 'OrderProlongate', 'OrderUnsubscribe'9, 'Waiting') ) as x union select x.uid, null, x.GenProcID from (select pwo.UID_PersonOrdered as uid, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID10, @CfgUseGenProcID) as GenProcID from personwantsorg pwo where pwo.OrderState in ( 'OrderProduct') and pwo.validUntil < getUTCDate()  ) as x exec QBM_PDBQueueInsert_Bulk11 'QER-K-ShoppingRackCheckValid', @DBQueueElements_01    declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject,12 genprocid) select x.uid, null, x.GenProcID from (select distinct pwo.UID_PersonWantsOrg as uid, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, 13@CfgUseGenProcID) as GenProcID from Personwantsorg pwo join PWOHelperPWO h on pwo.uid_Personwantsorg = h.uid_Personwantsorg where (h.NextReminder < getutcdate14() or h.NextAutomaticDecision < getutcdate() )  and pwo.UID_QERWorkingMethod > ' ' ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision'15, @DBQueueElements_02  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, x.GenProcID16 from (select pwo.UID_PersonWantsOrg as uid, dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) as GenProcID from Personwantsorg pwo17 join QERWorkingMethod wm on pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod where dateadd(dd, wm.DaysToAbort, pwo.OrderDate) < getutcdate() and isnull18(wm.DaysToAbort, 0) > 0  and pwo.Orderstate = 'OrderProduct' union select pwo.UID_PersonWantsOrg, max(dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID19, @CfgUseGenProcID)) as GenProcID from PersonWantsOrg pwo join PWODecisionHistory h1 on pwo.UID_PersonWantsOrg = h1.UID_PersonWantsOrg join QERWorkingMethod20 wm on pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod and wm.DaysToAbort > 0 where pwo.OrderState in ( 'OrderUnsubscribe', 'OrderProlongate') and (21 (h1.DecisionType = 'Unsubscribe' and pwo.OrderState = 'OrderUnsubscribe' and h1.OrderState = 'OrderUnsubscribe' ) or (h1.DecisionType = 'Prolongate' and22 pwo.OrderState = 'OrderProlongate' and h1.OrderState = 'OrderProlongate' ) ) group by pwo.UID_PersonWantsOrg, wm.DaysToAbort having dateadd(dd, wm.DaysToAbort23,isnull(MAX(h1.XDateInserted), '1899-12-30')) < GETUTCDATE()  ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision', @DBQueueElements_0324 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 25