USP_ACCOUNTINGELEMENTSECURITYGROUP_ADDELEMENTS

Adds data elements from a selection and a collection to an accounting element security group.

Parameters

Parameter	Parameter Type	Mode
@GROUPID	uniqueidentifier	IN
@DATAELEMENTQUERYID	uniqueidentifier	IN
@SELECTEDELEMENTS	xml	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN

Definition

Copy



      CREATE procedure dbo.USP_ACCOUNTINGELEMENTSECURITYGROUP_ADDELEMENTS
      (
        @GROUPID uniqueidentifier,
        @DATAELEMENTQUERYID uniqueidentifier,
        @SELECTEDELEMENTS xml,
        @CHANGEAGENTID uniqueidentifier,
        @CURRENTDATE datetime
      )
      as
      begin          
          create table #DATAELEMENTS (DATAELEMENTID uniqueidentifier)

          if @DATAELEMENTQUERYID is not null
          begin
             insert into #DATAELEMENTS
             select distinct ID
             from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@DATAELEMENTQUERYID)
          end

          if (select count(*) from @SELECTEDELEMENTS.nodes('/SELECTEDELEMENTS/ITEM') T(c)) > 0
          begin
            insert into #DATAELEMENTS
            select distinct T.c.value('(DATAELEMENTID)[1]', 'uniqueidentifier')
            from @SELECTEDELEMENTS.nodes('SELECTEDELEMENTS/ITEM') T(c)
          end

          if (select count(*) from #DATAELEMENTS) = 0 and (select count(*) from dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT where ACCOUNTINGELEMENTSECURITYGROUPID = @GROUPID) = 0
          begin
            raiserror('ERR_ACCOUNTINGELEMENTSECURITYGROUP_ELEMENTREQUIRED',13,1)
            return 1
          end

          insert into dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT(ACCOUNTINGELEMENTSECURITYGROUPID, DATAELEMENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          select distinct @GROUPID, DATAELEMENTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
          from #DATAELEMENTS
          where DATAELEMENTID not in (select DATAELEMENTID from dbo.ACCOUNTINGELEMENTSECURITYGROUPELEMENT where ACCOUNTINGELEMENTSECURITYGROUPID = @GROUPID)

          drop table #DATAELEMENTS

          return 0;
      end