dbo.QBM_PMNTableInsert

1      create   procedure QBM_PMNTableInsert (@TargetTable varchar(30)  , @ElementColumn varchar(30) , @AssignedElementColumn varchar(30) , @TargetIsView
2 bit = 0   , @FKTableNameElement varchar(30) = ''  , @FKColumnNameElement varchar(30) = ''      ) as begin declare @GenProcID varchar(38) declare @SQLCmdInsert
3 nvarchar(max) = N'' declare @SQLCmdFire nvarchar(max) = N'' declare @SQLAction nvarchar(max) declare @InsertLine nvarchar(max) declare @SelectLine nvarchar
4(max)  declare @TargetTableView varchar(30)  declare @UID_DialogTableTarget varchar(38) declare @IsAssignmentWithEvent bit declare @DebugSwitch int = 0
5 declare @EventName nvarchar(64) declare @ExistsXObjectKey bit declare @ExistsXOrigin bit declare @ExistsXIsInEffect bit declare @GenProcID_R varchar(38
6) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @ElementCount int declare @ElementIndex int
7 declare @DifferentTables QBM_YCursorBuffer declare @ElementCountDifferentTables int declare @ElementIndexDifferentTables int declare @ElementLastDifferentTables
8 int declare @MaxWaitTimeForMainOperation float = 8.0 SET XACT_ABORT OFF BEGIN TRY select top 1 @UID_DialogTableTarget = t.UID_DialogTable  , @IsAssignmentWithEvent
9 = t.isAssignmentWithEvent from DialogTable t with (readpast) where t.TableName = @TargetTable select @ExistsXObjectKey = dbo.QBM_FGIColumnExists(@TargetTable
10, 'XObjectKey') select @ExistsXOrigin = dbo.QBM_FGIColumnExists(@TargetTable, 'XOrigin') select @ExistsXIsInEffect = dbo.QBM_FGIColumnExists(@TargetTable
11, 'XIsInEffect') select @InsertLine = concat('
12		insert into ' , @TargetTable , ' ( ' , @ElementColumn , ' , ' , @AssignedElementColumn ) select @SelectLine
13 = '
14		    select  d.Element, d.AssignedElement ' if @ExistsXObjectKey = 1 begin select @InsertLine = concat(@InsertLine , ', XObjectKey') select @SelectLine
15 = concat(@SelectLine , ', @XObjectKey@') end if @ExistsXIsInEffect = 1 begin select @EventName = 'Assign' select @InsertLine = concat(@InsertLine , ', XIsInEffect'
16) select @SelectLine = concat(@SelectLine , ', d.XIsInEffect') end else begin select @EventName = 'Insert' end if @IsAssignmentWithEvent = 1 and not exists
17 (select top 1 1 from QBMEvent q with (readpast) join JobEventGen e with (readpast) on q.UID_QBMEvent = e.UID_QBMEvent join JobChain c on e.UID_JobChain
18 = c.UID_JobChain where q.UID_DialogTable = @UID_DialogTableTarget and q.EventName = @EventName and c.NoGenerate = 0 and c.IsDeactivatedByPreProcessor 
19= 0 ) begin select @IsAssignmentWithEvent = 0 end  if @ExistsXOrigin = 1 begin select @InsertLine = concat(@InsertLine , ', XOrigin') select @SelectLine
20 = concat(@SelectLine , ', d.XOrigin') end if exists(select top 1 1 from DialogColumn c with (readpast) where c.UID_DialogTable = @UID_DialogTableTarget
21 and c.ColumnName = 'XdateInserted' ) begin select @InsertLine = concat(@InsertLine , ', XDateInserted , XDateUpdated ,  XUserInserted , XUserUpdated '
22)  select @SelectLine = concat(@SelectLine , ', getutcdate() , getutcdate() ,  ''QBM_DBQueueProcess'' , ''QBM_DBQueueProcess'' ') end select @SQLCmdInsert
23 = concat(@InsertLine , ')' , @SelectLine , '									
24		from #QBMDeltaInsert  d 
25		where d.GenProcID = ''@GenProciD''
26		 and Not exists (select top 1 1
27							from '
28 , @TargetTable , ' z 
29							where z.' , @ElementColumn , ' = d.element
30							 and z.' , @AssignedElementColumn , ' = d.AssignedElement
31						)' )
32 if @TargetIsView = 0 begin if @IsAssignmentWithEvent = 1 begin select @SQLCmdFire = '
33		declare @EntriesToFire QBM_YParameterList 
34		declare @AddonElements QBM_YParameterList 
35			
36		insert into @EntriesToFire(Parameter1)
37			select @XObjectKey@
38			from #QBMDeltaInsert d
39			where d.GenProcID = ''@GenProciD''
40				'
41  if @ExistsXIsInEffect = 1 begin select @SQLCmdFire = concat(@SQLCmdFire , '
42			and d.XIsInEffect = 1
43			' ) end if @FKTableNameElement > ' ' and @FKColumnNameElement
44 > ' ' begin select @SQLCmdFire = concat(@SQLCmdFire , '
45				
46			insert into @AddonElements(Parameter1, Parameter2)
47				select distinct x.XObjectKey, @XObjectKey@
48				from #QBMDeltaInsert d join '
49 , @FKTableNameElement , ' x on x.' , @FKColumnNameElement , ' = d.Element
50				where d.GenProcID = ''@GenProciD''
51			' ) end  select @SQLCmdFire = concat
52(@SQLCmdFire , '		
53
54		exec QBM_PJobCreate_HOFireEvent_L ''' , @TargetTable , ''', @EntriesToFire, ''' , @EventName , ''', ''@GenProcID''
55				, @AddonElements
56				, @isToFreezeOnError = 1
57
58				'
59 ) end select @SQLCmdInsert = REPLACE(@SQLCmdInsert, '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey2(''' , @TargetTable , ''', ''' , @ElementColumn
60 , ''',  d.Element, ''' , @AssignedElementColumn , ''', d.AssignedElement)' ) ) select @SQLCmdFire = REPLACE(@SQLCmdFire, '@XObjectKey@', concat('dbo.QBM_FCVElementToObjectKey2('''
61 , @TargetTable , ''', ''' , @ElementColumn , ''',  d.Element, ''' , @AssignedElementColumn , ''', d.AssignedElement)') ) end else begin select @SQLCmdFire
62 = '
63		declare @EntriesToFire QBM_YParameterList 
64		declare @AddonElements QBM_YParameterList 
65			
66		insert into @EntriesToFire(Parameter1)
67			select @XObjectKey@
68			from #QBMDeltaInsert d
69			where d.GenProcID = ''@GenProciD''
70			 and d.TargetTableView = ''@TargetTableView@''
71				'
72  if @ExistsXIsInEffect = 1 begin select @SQLCmdFire = concat(@SQLCmdFire , '
73			and d.XIsInEffect = 1
74			' ) end if @FKTableNameElement > ' ' and @FKColumnNameElement
75 > ' ' begin select @SQLCmdFire = concat(@SQLCmdFire , '
76			insert into @AddonElements(Parameter1, Parameter2)
77				select distinct x.XObjectKey, @XObjectKey@
78				from #QBMDeltaInsert d join '
79 , @FKTableNameElement , ' x on x.' , @FKColumnNameElement , ' = d.Element
80				where d.GenProcID = ''@GenProciD''