dbo.QER_PITShopCheckMethodBO

1     create   procedure QER_PITShopCheckMethodBO (@Boards QBM_YCursorBuffer readonly) as begin declare @uid_OrgBO varchar(38) declare @uid_PWODecisionMethod
2 varchar(38) declare @GenProcID varchar(38) declare @GenProcIDErsatz varchar(38) declare @CountItems int select @CountItems = 0 declare @CollectionBeforeAll
3 QBM_YMNTable declare @CollectionAfterAll QBM_YMNTable declare @XUser nvarchar(64) = object_name(@@procid) BEGIN TRY select @GenProcIDErsatz = max(UID2
4) from @Boards where UID2 > ' ' if isnull(@GenProcIDErsatz, '') = '' begin select @GenProcIDErsatz = newid() end insert into @CollectionBeforeAll (UID_Element1
5 , UID_Element2 , XOrigin) select r.uid_PWODecisionMethod, r.uid_OrgBO , count(*) from PWOHelperBoardMethod r join @Boards p on r.uid_orgBO = p.UID1 left
6 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) select
8 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.UID1
10 = bo.uid_org join BaseTreeHasPWODecisionMethod ohdm on bo.uid_org = ohdm.uid_org join PWODecisionMethod dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod
11 left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct
12 = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType
13,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(5000 + (1000- 100*isnull(priority,0)) + isnull(xs.CountItems
14,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_PWODecisionMethod
16 left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct
17 = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType
18,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(6000 + (1000- 100*isnull(priority,0)) + isnull(xs.CountItems
19,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 PWODecisionMethod
21 dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod left outer join ( select pm.uid_pwodecisionmethod, count(*) as CountItems from pwodecisionstep
22 ps join pwodecisionmethod pm on pm.UID_SubMethodOrderProduct = ps.UID_PWODecisionSubMethod group by pm.uid_pwodecisionmethod ) as xs on dm.uid_PWODecisionMethod
23 = xs.uid_PWODecisionMethod where (isnull(dm.UID_OrgType,'') = '' or isnull(bo.UID_OrgType,'') = isnull(dm.UID_OrgType,''))  union all  select cast(1000
24 + (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 join
25 BaseTreeHasPWODecisionMethod ohdm on bo.uid_org = ohdm.uid_org join PWODecisionMethod dm on ohdm.uid_PWODecisionMethod = dm.uid_PWODecisionMethod join
26 PWODecisionSubMethod sm on dm.UID_SubMethodOrderProduct = sm.UID_PWODecisionSubMethod join PWODecisionStep s on sm.uid_PWODecisionSubMethod = s.uid_PWODecisionSubMethod
27 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 join
29 @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_Element1
30  = v.UID_Element1   ) ) as x on h.uid_orgBO = x.UID_Element2    Insert into PWOHelperBoardMethod (uid_OrgBO, uid_PWODecisionMethod) select n.UID_Element2
31 , 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_01
33 (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_Element2
34  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_Element2
36  = 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