dbo.QER_PITShopProductNodeMove

1    create   procedure QER_PITShopProductNodeMove ( @uid_OrgPR_Old varchar(38)  , @uid_orgBO_new varchar(38)  ) as begin declare @uid_OrgPR_New 
2varchar(38) declare @uid_OrgSH_New varchar(38) declare @uid_OrgCU_New varchar(38) declare @uid_orgBO_Old varchar(38) declare @uid_orgSH_Old varchar(38)
3 declare @uid_orgCU_Old varchar(38) declare @Ident_OrgBO_New nvarchar(256) declare @Ident_OrgSH_New nvarchar(256) declare @ObjectKeyElement varchar(138
4)  declare @UID_AccProduct varchar(38) declare @SQLCmdDelete nvarchar(max) declare @SQLCmdInsert nvarchar(max)  declare @UID_BaseTreeHasObject varchar(38
5) declare @SQLCmdBaetreeHasObject nvarchar(max) declare @uid_personwantsorg varchar(38) declare @BasisObjectKey varchar(138) declare @TargetTable varchar
6(30) declare @TargetView varchar(30)  declare @uid_DialogTableAssigned varchar(38) declare @pkname varchar(30) declare @DebugSwitch int = 0 declare @DebugLevel
7 char(1) = 'W' declare @DebugMessage nvarchar(2000) declare @StringQERFGIBitPatternInheritInfo_Direct nvarchar(100) declare @GenProcID varchar(38) declare
8 @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @PWOsToMove QBM_YParameterList    declare @QER_BitPatternInherit_Direct
9 int = dbo.QER_FGIBitPatternInheritInfo('|Direct|', 0)  SET XACT_ABORT OFF BEGIN TRY select @GenProcID = dbo.QBM_FGISessionContext('') select @StringQERFGIBitPatternInheritInfo_Direct
10 = dbo.QBM_FCVBinaryToString(convert(varbinary, dbo.QER_FGIBitPatternInheritInfo('|Direct|', 0)), 0)  select @uid_orgBO_Old = null select @uid_orgSH_Old
11 = null select @uid_orgCU_Old = null select top 1 @uid_orgBO_Old = bo.uid_org, @uid_orgSH_Old = sh.uid_org, @uid_orgCU_Old = cu.uid_org , @Ident_OrgBO_New
12 = bo.Ident_Org , @Ident_OrgSH_New = sh.Ident_Org , @UID_AccProduct = pr.UID_AccProduct from BaseTree pr join BaseTree bo on pr.uid_parentorg = bo.uid_org
13 join BaseTree sh on bo.uid_parentorg = sh.uid_org join BaseTree cu on cu.uid_parentorg = sh.uid_org and cu.itShopInfo = 'CU' where pr.uid_org = @uid_OrgPR_Old
14 and pr.uid_OrgRoot = 'QER-V-ITShopOrg' if @uid_orgBO_Old is null begin  raiserror( '#LDS#The shelf can not be found for the product node.|', 18, 2) with
15 nowait end if @uid_orgSH_Old is null begin  raiserror( '#LDS#The shop cannot be found for the product node.|', 18, 2) with nowait end if @uid_orgCU_Old
16 is null begin  raiserror( '#LDS#The customer node cannot be found for the product node.|', 18, 2) with nowait end if not exists (select top 1 1 from BaseTree
17 where uid_OrgRoot = 'QER-V-ITShopOrg' and ITShopInfo = 'BO' and uid_Org = @uid_orgBO_new ) begin  raiserror( '#LDS#The given target node is not a shelf in IT Shop.|'
18, 18, 2) with nowait end if @uid_orgBO_Old = @uid_orgBO_New begin  goto ende end select @ObjectKeyElement = null  select top 1 @ObjectKeyElement = bho.ObjectKey
19 from BaseTreeHasObject bho where bho.UID_Org = @uid_OrgPR_Old  and InheritInfo = @QER_BitPatternInherit_Direct if @Debugswitch > 0 begin select @DebugMessage
20 = '@ObjectKeyElement  '+ ISNULL(@ObjectKeyElement, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel end  select top 1 @TargetTable
21 = tmn.TableName from BaseTreeAssign ba join DialogTable tb with (readpast) on ba.UID_DialogTableElement = tb.UID_DialogTable join DialogTable tmn with
22 (readpast) on ba.UID_DialogTableMN = tmn.UID_DialogTable  where @ObjectKeyElement like concat('<Key><T>' , tb.TableName , '</T>%') if @Debugswitch > 0
23 begin select @DebugMessage = '@TargetTable  '+ ISNULL(@TargetTable, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel end select top
24 1 @pkname = dbo.QBM_FGIPrimaryKeyName_F(@TargetTable,1) if @pkname = 'uid_org' begin select top 1 @pkname = dbo.QBM_FGIPrimaryKeyName_F(@TargetTable,2
25) end if @DebugSwitch > 0 begin select @DebugMessage = '@pkname  '+ ISNULL(@pkname, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel
26 end  select @SQLCmdDelete = 'update ' + @TargetTable + '
27				set XOrigin = 0
28			where uid_Org = ''' + @uid_OrgBO_Old + '''
29			 and ' + @pkname + '  = '''
30 + dbo.QBM_FCVObjectkeyToElement('ColumnValue1', @ObjectKeyElement) + '''
31
32			delete ' + @TargetTable + '
33			where uid_Org = ''' + @uid_OrgBO_Old + 
34'''
35			 and ' + @pkname + '  = ''' + dbo.QBM_FCVObjectkeyToElement('ColumnValue1', @ObjectKeyElement) + ''''    if @Debugswitch > 0 begin select @DebugMessage
36 = '@SQLCmdDelete  '+ ISNULL(@SQLCmdDelete, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel end if @ObjectKeyElement is null begin
37  raiserror( '#LDS#The product could not be found for the given product node.|', 18, 2) with nowait end if @uid_accProduct is Null begin  raiserror( '#LDS#Associate Accproduct could not be found.|'
38, 18, 2) with nowait end  select top 1 @TargetView = v.TableName from OrgRootAssign oa join BaseTreeAssign ba on oa.UID_BaseTreeAssign = ba.UID_BaseTreeAssign
39 join DialogTable v with (readpast) on v.UID_DialogTableBase = ba.UID_DialogTableMN join QBM_VQBMRelation r on r.UID_DialogTableParent = 'QER-T-ITShopOrg'
40 and r.UID_DialogTableChild = v.UID_DialogTable where oa.UID_OrgRoot = 'QER-V-ITShopOrg' and dbo.QBM_FGITableName(ba.UID_DialogTableMN) = @TargetTable 
41if @Debugswitch > 0 begin select @DebugMessage = '@TargetView  '+ ISNULL(@TargetView, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel
42 end select @SQLCmdInsert = N'
43 if not exists (select top 1 1
44				 from ' + @TargetTable + '
45				 where XObjectKey = ''' + dbo.QBM_FCVElementToObjectKey2
46(@TargetView, 'UID_ITShopOrg',@uid_orgBO_New, @pkname,dbo.QBM_FCVObjectkeyToElement('ColumnValue1', @ObjectKeyElement)) + '''
47			   )
48	begin
49		insert into '
50 + @TargetTable + '
51		(uid_org, ' + @pkname + '
52		 , Xorigin, XDateinserted, XdateUpdated
53		 , XUserInserted, XUserupdated
54		 , XObjectKey
55					 ) 
56		select '''
57 + @uid_orgBO_New + ''', ''' + dbo.QBM_FCVObjectkeyToElement('ColumnValue1', @ObjectKeyElement) + '''
58		 , 1, ''' + dbo.QBM_FCVDatetimeToString(getutcdate
59()) + ''', ''' + dbo.QBM_FCVDatetimeToString(getutcdate()) + '''
60		, ''' + OBJECT_NAME(@@procid) + ''', ''' + OBJECT_NAME(@@procid) + '''
61		, ''' + dbo.QBM_FCVElementToObjectKey2
62(@TargetView, 'UID_ITShopOrg',@uid_orgBO_New, @pkname,dbo.QBM_FCVObjectkeyToElement('ColumnValue1', @ObjectKeyElement)) + '''
63	end
64	
65	'  if @Debugswitch
66 > 0 begin select @DebugMessage = '@SQLCmdInsert  '+ ISNULL(@SQLCmdInsert, '<null>') exec QBM_PJournal @debugmessage, @@procid, 'D', @DebugLevel end exec
67 QBM_PExecuteSQLWithRetry_LLP @SQLStatement = @SQLCmdInsert , @LockTimeout_ms = 250 , @MaxWaitTimeForLock_s = 20 , @ProcIDForJournal = default , @HandleErrorSilent
68 = 0 select @UID_BaseTreeHasObject = dbo.QBM_FCVStringToGUIDMAll(null, @uid_orgBO_New, @ObjectKeyElement) select @SQLCmdBaetreeHasObject = '
69	insert into BaseTreeHasObject (UID_BaseTreeHasObject, UID_Org, ObjectKey
70									, InheritInfo, XObjectKey
71								)
72		select '''
73 + @UID_BaseTreeHasObject + ''', ''' + @uid_orgBO_New + ''', ''' + @ObjectKeyElement + '''
74									, ' + @StringQERFGIBitPatternInheritInfo_Direct + 
75' , dbo.QBM_FCVElementToObjectKey1(''BaseTreeHasObject'', ''UID_BaseTreeHasObject'', ''' + @UID_BaseTreeHasObject + ''')
76		where Not exists (select top 1 1
77							from BaseTreeHasObject bho
78							where bho.UID_Org = '''
79 + @uid_orgBO_New + '''
80							and bho.ObjectKey = ''' + @ObjectKeyElement + '''