dbo.QER_ZITShopCheckWaiting

1    create   procedure QER_ZITShopCheckWaiting (@SlotNumber int) as begin  declare @WhereClause nvarchar(1000) declare @uid_personwantsorg varchar
2(38) declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'
3)) declare @BasisObjectKey varchar(138) declare @ObjectkeyOrdered varchar(138)  declare @ObjectKeyAssignment varchar(138)  declare @UID_OrgPR varchar(38
4) declare @ObjectKeyToTest varchar(138)  declare @IsReusePossible bit declare @IsAssignment bit declare @uid_personOrdered varchar(38) declare @uid_PwoToActivate
5 varchar(38) declare @Existiert int declare @Orderstate nvarchar(32) declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex
6 int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select
7 UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount
8 = 0 begin goto EndLabel end  insert into @ElementBuffer ( UID1  , UID2  , ObjectKey1  , ObjectKey2  , ObjectKey3  , Bit1  , Ident1  , UID3  , UID4  ) 
9select pwo.uid_PersonWantsOrg ,   dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, pwo.ObjectkeyOrdered
10, pwo.ObjectKeyAssignment , ba.IsReusePossible , dbo.QBM_FCVBitToString( SIGN(LEN(ISNULL(r.uid_QERAssign, '')))) as IsAssignment , pwo.uid_PersonOrdered
11, pwo.uid_Org from @DBQueueCurrent p join personwantsorg pwo on p.uid_Parameter = pwo.uid_Personwantsorg join BaseTree pr on pwo.uid_org = pr.uid_org join
12 BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.ObjectkeyOrdered) = ba.UID_DialogTableElement   join ( select MAX(pw.uid_personwantsorg
13) as uid_personwantsorg  from @DBQueueCurrent p join PersonWantsOrg pw on p.UID_Parameter = pw.UID_PersonWantsOrg   where pw.OrderState in ('Aborted', 
14'Dismissed', 'Unsubscribed' ) group by pw.UID_Org, pw.UID_PersonOrdered ) as z on z.uid_personwantsorg = pwo.UID_PersonWantsOrg  left outer join QERAssign
15 r on pwo.ObjectkeyOrdered = r.XObjectKey   where pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) and ba.IsReusePossible = 0 union select pwo.uid_PersonWantsOrg
16 ,  dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, pwo.ObjectkeyOrdered, pwo.ObjectKeyAssignment
17 , ba.IsReusePossible , sign(len(isnull(r.uid_qerassign, ''))) , pwo.uid_PersonOrdered, pwo.uid_Org from @DBQueueCurrent p join personwantsorg pwo on p.uid_Parameter
18 = pwo.uid_Personwantsorg join BaseTree pr on pwo.uid_org = pr.uid_org join BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.ObjectkeyOrdered
19) = ba.UID_DialogTableElement  left outer join QERAssign r on pwo.ObjectkeyOrdered = r.XObjectKey   where pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed'
20 ) and ba.IsReusePossible = 1   select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_personwantsorg
21 = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @ObjectkeyOrdered = bu.ObjectKey2 , @ObjectKeyAssignment = ObjectKey3 , @IsReusePossible
22 = bu.Bit1 , @IsAssignment = dbo.QBM_FCVStringToBit(bu.Ident1) , @uid_PersonOrdered = bu.UID3  , @uid_OrgPR = bu.UID4 from @ElementBuffer bu where bu.ElementIndex
23 = @ElementIndex  select @uid_PwoToActivate = null  if @IsReusePossible = 0 begin  select @ObjectkeyToTest = dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg'
24, 'uid_Person', @uid_PersonOrdered, 'UID_ITShopOrg', @uid_OrgPR) exec @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 0 begin
25 select top 1 @uid_PwoToActivate = pwo.uid_personwantsorg, @BasisObjectKey = pwo.XObjectKey from personwantsorg pwo where pwo.uid_personordered = @uid_personordered
26 and pwo.ObjectkeyOrdered = @ObjectkeyOrdered and orderstate = 'Waiting'  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg
27 <> @uid_personwantsorg  and dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0         order by pwo.XDateinserted end  end  else  begin if @IsAssignment 
28= 1 begin select @ObjectkeyToTest = @ObjectKeyAssignment exec @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 0 begin select
29 top 1 @uid_PwoToActivate = pwo.uid_personwantsorg, @BasisObjectKey = pwo.XObjectKey from personwantsorg pwo where ObjectKeyAssignment = @ObjectKeyAssignment
30 and Orderstate = 'Waiting'  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg <> @uid_personwantsorg  and dbo.QBM_FGIJobCreatedExists
31(pwo.XObjectKey) = 0         order by pwo.XDateinserted end  end  end  if @uid_PwoToActivate is not null begin select @WhereClause = 'uid_personwantsorg = '''
32 + rtrim(@uid_PwoToActivate) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName
33 = 'Assign' , @GenProcID = @GenprocID  , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: Other similar requests have been disabled.|'
34 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
35  end select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return
36 end 
37