dbo.QER_ZITShopCheckValid

1    create   procedure QER_ZITShopCheckValid ( @SlotNumber int )  as begin  declare @WhereClause nvarchar(1000) declare @uid_personwantsorg varchar
2(38) declare @BasisObjectKey varchar(138) declare @ObjectkeyOrdered varchar(138)  declare @ObjectKeyAssignment varchar(138)  declare @UID_OrgPR varchar
3(38) declare @ObjectKeyToTest varchar(138)  declare @IsReusePossible bit declare @IsReusePossibleUS bit declare @IsAssignment bit declare @uid_personOrdered
4 varchar(38) declare @uid_PWOConflicted varchar(38) declare @Existiert int declare @Orderstate nvarchar(32) declare @GenProcID varchar(38) declare @CfgUseGenProcID
5 bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @ExceededValidUntilUnsubscribe bit = dbo.QBM_FCVStringToBit
6(dbo.QBM_FGIConfigparmValue('QER\ITShop\ExceededValidUntilUnsubscribe'))     declare @UID_PWOStateFinalError varchar(38) declare @ElementLast int declare
7 @ElementCount int declare @ElementIndex int declare @PersonOrdered QBM_YParameterlist   BEGIN TRY  insert into @PersonOrdered(Parameter1, Parameter2) 
8select distinct cu.UID_Parameter, cu.GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber   declare @ElementGranted QBM_YCursorBuffer
9 insert into @ElementGranted ( UID1  , UID2  , ObjectKey1  , ObjectKey2  , ObjectKey3  , Ident1 , Ident2  , Ident3  , UID3  , UID4  ) select pwo.uid_PersonWantsOrg
10 , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, cu.Parameter2 , @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, pwo.ObjectkeyOrdered, pwo.ObjectKeyAssignment
11 , dbo.QBM_FCVBitToString( ba.IsReusePossible) , dbo.QBM_FCVBitToString(ba.IsReusePossibleUS) , dbo.QBM_FCVBitToString(sign(len(isnull(r.UID_QERAssign,
12 '')))) as IsAssignment , pwo.uid_PersonOrdered, pwo.uid_Org from personwantsorg pwo join @PersonOrdered cu on pwo.UID_PersonOrdered = cu.Parameter1  join
13 BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.objectkeyordered) = ba.UID_DialogTableElement left outer join QERAssign r on
14 pwo.ObjectkeyOrdered = r.XObjectKey where pwo.OrderState = 'GRANTED'  and (ba.IsReusePossible = 1 or (ba.IsReusePossible = 0 and pwo.UID_PersonWantsOrg
15 in ( select MAX(pw.uid_personwantsorg) as uid_personwantsorg  from PersonWantsOrg pw join @PersonOrdered cu on pwo.UID_PersonOrdered = cu.Parameter1  
16where pw.OrderState = 'GRANTED' and isnull(pw.validFrom , '1899-12-30') < getUTCDate() and isnull(pw.validUntil , '2200-01-01') > getUTCDate() group by
17 pw.UID_Org, pw.UID_PersonOrdered ) ) )  and isnull(validFrom , '1899-12-30') < getUTCDate() and isnull(validUntil , '2200-01-01') > getUTCDate()  and 
18dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast
19 = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @BasisObjectKey = bu.ObjectKey1
20  , @ObjectkeyOrdered = bu.ObjectKey2  , @ObjectKeyAssignment = bu.ObjectKey3  , @IsReusePossible = dbo.QBM_FCVStringToBit(bu.Ident1) , @IsReusePossibleUS
21 = dbo.QBM_FCVStringToBit(bu.Ident2) , @IsAssignment = dbo.QBM_FCVStringToBit(bu.Ident3) , @uid_PersonOrdered = bu.UID3  , @uid_OrgPR = bu.UID4  from @ElementGranted
22 bu where bu.ElementIndex = @ElementIndex  select @ObjectkeyToTest = '' select @Existiert = 0 select @uid_PwoConflicted = null  if @IsReusePossible = 0
23 begin  select @ObjectkeyToTest = dbo.QBM_FCVElementToObjectKey2('PersonInITShopOrg', 'uid_Person', @uid_PersonOrdered, 'UID_ITShopOrg', @uid_OrgPR) exec
24 @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 1 begin select top 1 @uid_PwoConflicted = pwo.uid_personwantsorg from personwantsorg
25 pwo   join personInBaseTree pio on pwo.uid_org = pio.uid_org and pio.XObjectKey = @ObjectkeyToTest and pio.XOrigin > 0  where isnull(pwo.validUntil, '2200-01-01'
26) > getUTCDate() and pwo.UID_PersonWantsOrg <> @uid_personwantsorg  order by pwo.XDateinserted end  end  else  begin if @IsAssignment = 1 begin select 
27@ObjectkeyToTest = @ObjectKeyAssignment exec @existiert = QBM_PObjectExistsInDatabase @ObjectkeyToTest  if @Existiert = 1 begin select top 1 @uid_PwoConflicted
28 = pwo.uid_personwantsorg from personwantsorg pwo   where ObjectKeyAssignment = @ObjectKeyAssignment   and OrderState in ('Assigned', 'OrderProlongate'
29, 'OrderUnsubscribe' )  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg <> @uid_personwantsorg  order by pwo.XDateinserted
30 end  end  end  select @WhereClause = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N'''' if @existiert = 1 begin if @uid_PwoConflicted is null
31 begin  select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg'
32 , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID  , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Request has been closed because the requested assignment already exists as direct.|'
33 , @param2 = @UID_PWOStateFinalError , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional
34 = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'  end else begin exec
35 QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Waiting' , @GenProcID = @GenprocID
36  , @ObjectKeysAffected = DEFAULT , @param1 = @uid_PwoConflicted , @param2 = '#LDS#Automatic system approval: Other similar requests have been enabled.|'
37 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
38  end end else begin exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Assign'
39 , @GenProcID = @GenprocID  , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: ValidFrom time was reached.|' , @Retries = 3 ,
40 @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
41  end select @ElementIndex += 1 end   declare @ElementZuAlt QBM_YCursorBuffer insert into @ElementZuAlt (UID1  , UID2  , ObjectKey1  , ObjectKey2  , ObjectKey3
42  , UID3  , UID4  , Ident1  ) select pwo.uid_PersonWantsOrg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, cu.Parameter2 , @CfgUseGenProcID) as GenProcID 
43, pwo.XObjectKey, pwo.ObjectkeyOrdered, pwo.ObjectKeyAssignment , pwo.uid_PersonOrdered, pwo.uid_Org, pwo.Orderstate from personwantsorg pwo join @PersonOrdered
44 cu on pwo.UID_PersonOrdered = cu.Parameter1  where pwo.validUntil < getUTCDate()      and pwo.OrderState in ('Assigned', 'Granted', 'OrderProlongate',
45 'OrderUnsubscribe', 'Waiting')  and dbo.QBM_FGIJobCreatedExists(pwo.XObjectKey) = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY
46 - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID
47 = bu.UID2  , @BasisObjectKey = bu.ObjectKey1  , @ObjectkeyOrdered = bu.ObjectKey2  , @ObjectKeyAssignment = bu.ObjectKey3  , @uid_PersonOrdered = bu.UID3
48  , @uid_OrgPR = bu.UID4  , @Orderstate = bu.Ident1  from @ElementZuAlt bu where bu.ElementIndex = @ElementIndex  select @IsAssignment = 0 if exists (select
49 top 1 1 from QERAssign r where r.XObjectKey = @ObjectkeyOrdered ) begin select @IsAssignment = 1 end select top 1 @IsReusePossible = ba.IsReusePossible
50 , @IsReusePossibleUS = ba.IsReusePossibleUS from personwantsorg pwo   join BaseTreeAssign ba on dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', pwo.objectkeyordered
51) = ba.UID_DialogTableElement where pwo.uid_personwantsorg = @uid_personwantsorg   select @uid_PwoConflicted = null   if @OrderState in ('Assigned', 'OrderProlongate'
52, 'OrderUnsubscribe' ) begin  if @IsReusePossible = 0 begin select top 1 @uid_PwoConflicted = pwo.uid_personwantsorg from personwantsorg pwo   where orderstate
53 = 'waiting' and uid_personOrdered = @uid_personOrdered and uid_Org = @uid_OrgPR  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate() and pwo.UID_PersonWantsOrg
54 <> @uid_personwantsorg  order by pwo.XDateinserted end  else begin  if @IsAssignment = 1 begin select top 1 @uid_PwoConflicted = pwo.uid_personwantsorg
55 from personwantsorg pwo   where orderstate = 'waiting' and ObjectKeyAssignment = @ObjectKeyAssignment  and isnull(pwo.validUntil, '2200-01-01') > getUTCDate
56() and pwo.UID_PersonWantsOrg <> @uid_personwantsorg  order by pwo.XDateinserted end  end  end  select @WhereClause = N'uid_personwantsorg = ''' + rtrim
57(@uid_personwantsorg) + N'''' if @uid_PwoConflicted is null begin  select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) 
58 if @ExceededValidUntilUnsubscribe = 0 or (@ExceededValidUntilUnsubscribe = 1 and @Orderstate in( 'Waiting', 'Granted') ) begin exec QBM_PJobCreate_HOCallMethod
59 @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT
60 , @param1 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|' , @param2 = @UID_PWOStateFinalError , @Retries = 3 , @isToFreezeOnError
61 = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
62  end if @ExceededValidUntilUnsubscribe = 1 and @Orderstate in ('Assigned', 'OrderProlongate')  begin exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg'
63 , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Unsubscribe' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa'  , 
64@param2 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|'  , @param3 = 'QER-VALIDUNTIL-EXCEEDED'  , @Retries = 3 , @isToFreezeOnError
65 = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''OrderProlongate'')' end end
66 else begin exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save = 1 , @MethodName = 'Replace' , @GenProcID
67 = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = @uid_PwoConflicted , @param2 = '#LDS#Automatic system approval: ValidUntil time period exceeded for the replaced order.|'
68 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
69  end select @ElementIndex += 1 end   declare @ElementVorbei QBM_YCursorBuffer insert into @ElementVorbei (UID1, UID2, ObjectKey1) select pwo.uid_PersonWantsOrg
70 , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, cu.Parameter2 , @CfgUseGenProcID) as GenProcID , pwo.XObjectKey from personwantsorg pwo join @PersonOrdered
71 cu on pwo.UID_PersonOrdered = cu.Parameter1  where pwo.OrderState in ( 'OrderProduct') and pwo.validUntil < getUTCDate()  and dbo.QBM_FGIJobCreatedExists
72(pwo.XObjectKey) = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex
73 <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 from @ElementVorbei bu where
74 bu.ElementIndex = @ElementIndex  select @WhereClause = 'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''''  select @UID_PWOStateFinalError 
75= dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @WhereClause , @save
76 = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: ValidUntil time period exceeded.|'
77 , @param2 = @UID_PWOStateFinalError , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional
78 = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'  select @ElementIndex
79 += 1 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 
80