Back to OIM Explorer

dbo.QER_PITShopCheckMethodBO

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-ShoppingRackProductNode / QER_ZITShopCheckMethodPR at line 37; Single DBQueue insert -> QER-K-ShoppingRackPWODecisionMaker / QER_ZITShopDecisionMakerFill at line 37; References QBM_PDBQueueInsert_Single; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 5.981 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-ShoppingRackProductNode / QER_ZITShopCheckMethodPR at line 37
  • Single DBQueue insert -> QER-K-ShoppingRackPWODecisionMaker / QER_ZITShopDecisionMakerFill at line 37
  • References QBM_PDBQueueInsert_Single
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZITShopCheckMethodPR at line 37 Bulk DBQueue insert -> QER-K-ShoppingRackProductNode / QER_ZITShopCheckMethodPR at line 37
  • queues DBQueue task QER_ZITShopDecisionMakerFill at line 37 Single DBQueue insert -> QER-K-ShoppingRackPWODecisionMaker / QER_ZITShopDecisionMakerFill at line 37
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PDBQueueInsert_Single source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QER-K-ShoppingRackProductNode -> QER_ZITShopCheckMethodPR QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode', @DBQueueElements_01 if @CountItems > 0 begin exec QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker' , '', '', @GenProcIDErsatz end END TRY BEGIN CATCH exec QBM_PSessionE…
  • queues DBQueue task QER-K-ShoppingRackPWODecisionMaker -> QER_ZITShopDecisionMakerFill QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker' , '', '', @GenProcIDErsatz end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end

References

Referenced By

Complete Source

SQL230 lines
1CREATE PROCEDURE QER_PITShopCheckMethodBO(2  @Boards QBM_YCursorBuffer READONLY3)4AS5BEGIN6  DECLARE @uid_OrgBO varchar(38)7  DECLARE @uid_PWODecisionMethod varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @GenProcIDErsatz varchar(38)10  DECLARE @CountItems int11  SELECT @CountItems = 012  DECLARE @CollectionBeforeAll QBM_YMNTable13  DECLARE @CollectionAfterAll QBM_YMNTable14  DECLARE @XUser nvarchar(64) = object_name(@@procid)15  BEGIN TRY16    SELECT @GenProcIDErsatz = max(UID2)17    FROM @Boards18    WHERE19      UID2 > ' '20    IF isnull(@GenProcIDErsatz,21    '') = ''22    BEGIN23      SELECT @GenProcIDErsatz = newid()24    END25    INSERT INTO @CollectionBeforeAll(UID_Element1,26    UID_Element2,27    XOrigin)28    SELECT29      r.uid_PWODecisionMethod,30      r.uid_OrgBO,31      count(*)32    FROM PWOHelperBoardMethod r33    JOIN @Boards p34      ON r.uid_orgBO = p.UID135    LEFT36    OUTER37    JOIN pwodecisionmethod m38      ON r.uid_PWODecisionMethod = m.uid_PWODecisionMethod39    LEFT40    OUTER41    JOIN PWODecisionStep s42      ON m.UID_SubMethodOrderProduct = s.UID_PWODecisionSubMethod43    GROUP BY r.uid_PWODecisionMethod,44    r.uid_OrgBO45    INSERT INTO @CollectionAfterAll(UID_Element1,46    UID_Element2,47    XOrigin)48    SELECT49      substring(min(x.rang),50      5,51      38) AS uid_PWODecisionmethod,52      x.uid_org,53      cast(substring(min(x.rang), 3, 2) AS int) AS CountItems54    FROM(55    SELECT56      cast(4000 +(1000- 100* isnull(priority, 0)) + isnull(xs.CountItems, 0) AS nchar(4)) + dm.uid_PWODecisionMethod AS rang,57      bo.uid_org58    FROM @Boards p59    JOIN BaseTree bo60      ON p.UID1 = bo.uid_org61    JOIN BaseTreeHasPWODecisionMethod ohdm62      ON bo.uid_org = ohdm.uid_org63    JOIN PWODecisionMethod dm64      ON ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod65    LEFT66    OUTER67    JOIN(68    SELECT69      pm.uid_pwodecisionmethod, count(*) AS CountItems70    FROM pwodecisionstep ps71    JOIN pwodecisionmethod pm72      ON pm.UID_SubMethodOrderProduct = ps.UID_PWODecisionSubMethod73    GROUP BY pm.uid_pwodecisionmethod) AS xs74      ON dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod75    WHERE76      (isnull(dm.UID_OrgType, '') = '' OR isnull(bo.UID_OrgType, '') = isnull(dm.UID_OrgType, ''))77    UNION all78    SELECT79      cast(5000 +(1000- 100*isnull(priority, 0)) + isnull(xs.CountItems, 0) AS nchar(4)) + dm.uid_PWODecisionMethod,80      bo.uid_org81    FROM @Boards p82    JOIN BaseTree bo83      ON p.UID1 = bo.uid_org84    JOIN BaseTree sh85      ON bo.uid_parentorg = sh.uid_org86    JOIN BaseTreeHasPWODecisionMethod ohdm87      ON sh.uid_org = ohdm.uid_org88    JOIN PWODecisionMethod dm89      ON ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod90    LEFT91    OUTER92    JOIN(93    SELECT94      pm.uid_pwodecisionmethod, count(*) AS CountItems95    FROM pwodecisionstep ps96    JOIN pwodecisionmethod pm97      ON pm.UID_SubMethodOrderProduct = ps.UID_PWODecisionSubMethod98    GROUP BY pm.uid_pwodecisionmethod) AS xs99      ON dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod100    WHERE101      (isnull(dm.UID_OrgType, '') = '' OR isnull(bo.UID_OrgType, '') = isnull(dm.UID_OrgType, ''))102    UNION all103    SELECT104      cast(6000 +(1000- 100*isnull(priority, 0)) + isnull(xs.CountItems, 0) AS nchar(4)) + dm.uid_PWODecisionMethod,105      bo.uid_org106    FROM @Boards p107    JOIN BaseTree bo108      ON p.UID1 = bo.uid_org109    JOIN BaseTree sh110      ON bo.uid_parentorg = sh.uid_org111    JOIN BaseTree sc112      ON sh.uid_parentorg = sc.uid_org113    JOIN BaseTreeHasPWODecisionMethod ohdm114      ON sc.uid_org = ohdm.uid_org115    JOIN PWODecisionMethod dm116      ON ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod117    LEFT118    OUTER119    JOIN(120    SELECT121      pm.uid_pwodecisionmethod, count(*) AS CountItems122    FROM pwodecisionstep ps123    JOIN pwodecisionmethod pm124      ON pm.UID_SubMethodOrderProduct = ps.UID_PWODecisionSubMethod125    GROUP BY pm.uid_pwodecisionmethod) AS xs126      ON dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod127    WHERE128      (isnull(dm.UID_OrgType, '') = '' OR isnull(bo.UID_OrgType, '') = isnull(dm.UID_OrgType, ''))129    UNION all130    SELECT131      cast(1000 +(1000- 100* isnull(priority, 0)) AS nchar(4)) + dm.uid_PWODecisionMethod AS rang,132      bo.uid_org133    FROM @Boards p134    JOIN BaseTree bo135      ON p.UID1 = bo.uid_org136    JOIN BaseTreeHasPWODecisionMethod ohdm137      ON bo.uid_org = ohdm.uid_org138    JOIN PWODecisionMethod dm139      ON ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod140    JOIN PWODecisionSubMethod sm141      ON dm.UID_SubMethodOrderProduct = sm.UID_PWODecisionSubMethod142    JOIN PWODecisionStep s143      ON sm.uid_PWODecisionSubMethod = s.uid_PWODecisionSubMethod AND s.UID_PWODecisionRule = 'QER-PWODecisionRule-SB'144    WHERE145      (isnull(dm.UID_OrgType, '') = '' OR isnull(bo.UID_OrgType, '') = isnull(dm.UID_OrgType, ''))) AS x146    GROUP BY x.uid_org147    DELETE PWOHelperBoardMethod148    FROM PWOHelperBoardMethod h149    JOIN(150    SELECT151      v.UID_Element2,152      p.UID2153    FROM @CollectionBeforeAll v154    JOIN @Boards p155      ON v.UID_Element2 = p.UID1156    WHERE157      NOT EXISTS(158    SELECT TOP 1 1159    FROM @CollectionAfterAll n160    WHERE161      n.UID_Element2 = v.UID_Element2 AND n.UID_Element1 = v.UID_Element1)) AS x162      ON h.uid_orgBO = x.UID_Element2163    INSERT INTO PWOHelperBoardMethod(uid_OrgBO,164    uid_PWODecisionMethod)165    SELECT166      n.UID_Element2,167      n.UID_Element1168    FROM @CollectionAfterAll n169    JOIN @Boards p170      ON n.UID_Element2 = p.UID1171    WHERE172      NOT EXISTS(173    SELECT TOP 1 1174    FROM @CollectionBeforeAll v175    WHERE176      n.UID_Element2 = v.UID_Element2 AND n.UID_Element1 = v.UID_Element1)177    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw178    INSERT INTO @DBQueueElements_01(object,179    subobject,180    genprocid)181    SELECT182      x.uid,183      NULL,184      x.GenProcID185    FROM(186    SELECT187      b.uid_org AS uid,188      p.UID2 AS GenProcID189    FROM @Boards p190    JOIN(191    SELECT UID_Element2192    FROM @CollectionBeforeAll v193    WHERE194      NOT EXISTS(195    SELECT TOP 1 1196    FROM @CollectionAfterAll n197    WHERE198      n.UID_Element2 = v.UID_Element2 AND n.UID_Element1 = v.UID_Element1)199    UNION200    SELECT UID_Element2201    FROM @CollectionAfterAll n202    WHERE203      NOT EXISTS(204    SELECT TOP 1 1205    FROM @CollectionBeforeAll v206    WHERE207      n.UID_Element2 = v.UID_Element2 AND n.UID_Element1 = v.UID_Element1)) AS m208      ON p.UID1 = m.UID_Element2209    JOIN BaseTree b210      ON b.uid_parentorg = m.UID_Element2) AS x211    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode',212      @DBQueueElements_01213    IF @CountItems > 0214    BEGIN215      EXEC QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker',216      '',217      '',218        @GenProcIDErsatz219    END220  END TRY221  BEGIN CATCH222    EXEC QBM_PSessionErrorAdd DEFAULT223    RAISERROR('',224    18,225    1)226      WITH NOWAIT227  END CATCH228  endLabel:229  RETURN230END
Open raw exported source
SQL · Raw39 lines
1     create   procedure QER_PITShopCheckMethodBO (@Boards QBM_YCursorBuffer readonly) as begin declare @uid_OrgBO varchar(38) declare @uid_PWODecisionMethod2 varchar(38) declare @GenProcID varchar(38) declare @GenProcIDErsatz varchar(38) declare @CountItems int select @CountItems = 0 declare @CollectionBeforeAll3 QBM_YMNTable declare @CollectionAfterAll QBM_YMNTable declare @XUser nvarchar(64) = object_name(@@procid) BEGIN TRY select @GenProcIDErsatz = max(UID24) from @Boards where UID2 > ' ' if isnull(@GenProcIDErsatz, '') = '' begin select @GenProcIDErsatz = newid() end insert into @CollectionBeforeAll (UID_Element15 , UID_Element2 , XOrigin) select r.uid_PWODecisionMethod, r.uid_OrgBO , count(*) from PWOHelperBoardMethod r join @Boards p on r.uid_orgBO = p.UID1 left6 outer Join pwodecisionmethod m on r.uid_PWODecisionMethod = m.uid_PWODecisionMethod left outer join PWODecisionStep s on m.UID_SubMethodOrderProduct =7 s.UID_PWODecisionSubMethod group by r.uid_PWODecisionMethod, r.uid_OrgBO insert into @CollectionAfterAll (UID_Element1 , UID_Element2 , XOrigin) select8 substring(min(x.rang),5,38) as uid_PWODecisionmethod , x.uid_org, cast(substring(min(x.rang),3,2) as int) as CountItems from  ( select cast(4000 + (1000-9 100* isnull(priority,0)) + isnull(xs.CountItems,0) as nchar(4)) + dm.uid_PWODecisionMethod as rang, bo.uid_org from @Boards p join BaseTree bo on p.UID110 = bo.uid_org join BaseTreeHasPWODecisionMethod ohdm on bo.uid_org = ohdm.uid_org join PWODecisionMethod dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod11 left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct12 = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType13,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(5000 + (1000- 100*isnull(priority,0)) + isnull(xs.CountItems14,0) as nchar(4)) + dm.uid_PWODecisionMethod , bo.uid_org from @Boards p join BaseTree bo on p.UID1 = bo.uid_org join BaseTree sh on bo.uid_parentorg = 15sh.uid_org join BaseTreeHasPWODecisionMethod ohdm on sh.uid_org = ohdm.uid_org join PWODecisionMethod dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod16 left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct17 = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType18,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(6000 + (1000- 100*isnull(priority,0)) + isnull(xs.CountItems19,0) as nchar(4)) + dm.uid_PWODecisionMethod , bo.uid_org from @Boards p join BaseTree bo on p.UID1 = bo.uid_org join BaseTree sh on bo.uid_parentorg = 20sh.uid_org join BaseTree sc on sh.uid_parentorg = sc.uid_org join BaseTreeHasPWODecisionMethod ohdm on sc.uid_org = ohdm.uid_org join PWODecisionMethod21 dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep22 ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod23 = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(100024 + (1000- 100* isnull(priority,0)) as nchar(4)) + dm.uid_PWODecisionMethod as rang, bo.uid_org from @Boards p join BaseTree bo on p.UID1 = bo.uid_org join25 BaseTreeHasPWODecisionMethod ohdm on bo.uid_org = ohdm.uid_org join PWODecisionMethod dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod join26 PWODecisionSubMethod sm on dm.UID_SubMethodOrderProduct = sm.UID_PWODecisionSubMethod join PWODecisionStep s on sm.uid_PWODecisionSubMethod = s.uid_PWODecisionSubMethod27 and s.UID_PWODecisionRule = 'QER-PWODecisionRule-SB' where (isnull(dm.UID_OrgType,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,'')) )28 as x group by x.uid_org   delete PWOHelperBoardMethod from PWOHelperBoardMethod h join ( select v.UID_Element2 , p.UID2 from @CollectionBeforeAll v join29 @Boards p on v.UID_Element2  = p.UID1 where not exists ( select top 1 1 from @CollectionAfterAll n where n.UID_Element2  = v.UID_Element2  and n.UID_Element130  = v.UID_Element1   ) ) as x on h.uid_orgBO = x.UID_Element2    Insert into PWOHelperBoardMethod (uid_OrgBO, uid_PWODecisionMethod) select n.UID_Element231 , n.UID_Element1  from @CollectionAfterAll n join @Boards p on n.UID_Element2  = p.UID1 where not exists ( select top 1 1 from @CollectionBeforeAll v 32where n.UID_Element2  = v.UID_Element2  and n.UID_Element1  = v.UID_Element1   )   declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_0133 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select b.uid_org as uid, p.UID2 as GenProcID from @Boards p join ( select UID_Element234  from @CollectionBeforeAll v where not exists ( select top 1 1 from @CollectionAfterAll n where n.UID_Element2  = v.UID_Element2  and n.UID_Element1  35= v.UID_Element1   ) union select UID_Element2  from @CollectionAfterAll n where not exists ( select top 1 1 from @CollectionBeforeAll v where n.UID_Element236  = v.UID_Element2  and n.UID_Element1  = v.UID_Element1   ) ) as m on p.UID1 = m.UID_Element2   join BaseTree b on b.uid_parentorg = m.UID_Element2  )37 as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode', @DBQueueElements_01 if @CountItems > 0 begin exec QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker'38, '', '', @GenProcIDErsatz end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: return end 39