USP_DATAFORMTEMPLATE_EDITLOAD_ACCOUNTINGELEMENTRELATIONSHIP

The load procedure used by the edit dataform template "Accounting Element Relationship Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@RELATIONSHIPID	nvarchar(60)	INOUT	Relationship ID
@DESCRIPTION	nvarchar(60)	INOUT	Relationship description
@BASEELEMENTACCOUNTSTRUCTUREID	uniqueidentifier	INOUT	Relationship basis
@RESTRICTION	tinyint	INOUT	Restrict\Allow
@ACCOUNTSTRUCTURE	xml	INOUT
@QUERYID	uniqueidentifier	INOUT
@SELECTEDELEMENTS	xml	INOUT	Choose selected accounts from grid
@DATAELEMENTRECORDTYPEID	uniqueidentifier	INOUT
@ACCOUNTCODERECORDTYPEID	uniqueidentifier	INOUT
@PROJECTRECORDTYPEID	uniqueidentifier	INOUT
@ACCOUNTRECORDTYPEID	uniqueidentifier	INOUT

Definition

Copy



          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_ACCOUNTINGELEMENTRELATIONSHIP
          (
          @ID uniqueidentifier,
          @DATALOADED bit = 0 output,
          @TSLONG bigint = 0 output,
          @RELATIONSHIPID nvarchar(60) = null output,
          @DESCRIPTION nvarchar(60) = null output,
          @BASEELEMENTACCOUNTSTRUCTUREID uniqueidentifier = null output,
          @RESTRICTION tinyint = null output,
          @ACCOUNTSTRUCTURE xml = null output,
          @QUERYID uniqueidentifier = null output,
          @SELECTEDELEMENTS xml = null output,
          @DATAELEMENTRECORDTYPEID uniqueidentifier = null output,
          @ACCOUNTCODERECORDTYPEID uniqueidentifier = null output,
          @PROJECTRECORDTYPEID uniqueidentifier = null output,
          @ACCOUNTRECORDTYPEID uniqueidentifier = null output
          )
          with execute as owner
          as
              set nocount on;
              set @DATALOADED = 0
              set @TSLONG = 0

              select
                  @DATALOADED = 1,
                  @TSLONG = TSLONG,
              @RELATIONSHIPID = RELATIONSHIPID,
                  @DESCRIPTION = DESCRIPTION,
                  @BASEELEMENTACCOUNTSTRUCTUREID = BASEELEMENTACCOUNTSTRUCTUREID,
              @RESTRICTION = RESTRICTIONCODE
              from dbo.ACCOUNTINGELEMENTRELATIONSHIP
              where ID = @ID

            set @ACCOUNTSTRUCTURE = (select ACTSTR.ID, 
              ACTSTR.DESCRIPTION as NAME, 
              ACTSTR.ELEMENTTYPECODE,
              ACTSTR.SEGMENTCOLUMN,
              ACTSTR.ELEMENTDEFINITIONCODE,
              AERQP.QUERYID,
              (select DATAELEMENTID as ID
                  from dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER AEREP
                inner join dbo.PDACCOUNTSEGMENTVALUE PDASV on AEREP.DATAELEMENTID = PDASV.ID
                  where AEREP.PDACCOUNTSTRUCTUREID = ACTSTR.ID and ACCOUNTINGELEMENTRELATIONSHIPID = @ID
                order by PDASV.SHORTDESCRIPTION
                  for xml raw('ITEM'),type,elements,root('SELECTEDELEMENTS'),BINARY BASE64)
              from dbo.PDACCOUNTSTRUCTURE ACTSTR
              left outer join dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER AERQP on ACTSTR.ID = AERQP.PDACCOUNTSTRUCTUREID and AERQP.ACCOUNTINGELEMENTRELATIONSHIPID = @ID
              order by ACTSTR.DESCRIPTION
              for xml raw('ITEM'),type,elements,root('ACCOUNTSTRUCTURE'),BINARY BASE64)

            select @QUERYID = QUERYID
            from dbo.ACCOUNTINGELEMENTRELATIONSHIPQUERYPARAMETER
            where PDACCOUNTSTRUCTUREID is null and ACCOUNTINGELEMENTRELATIONSHIPID = @ID

            set @SELECTEDELEMENTS = 
              (select GLA.ID
              from dbo.ACCOUNTINGELEMENTRELATIONSHIPELEMENTPARAMETER AEREP
              inner join dbo.GLACCOUNT GLA on AEREP.ACCOUNTID = GLA.ID
              where AEREP.ACCOUNTINGELEMENTRELATIONSHIPID = @ID
              order by GLA.ACCOUNTSTRING
              for xml raw('ITEM'),type,elements,root('SELECTEDELEMENTS'),BINARY BASE64)

            select @DATAELEMENTRECORDTYPEID = ID from dbo.RECORDTYPE
            where upper(NAME) = 'ACCOUNTING ELEMENT'

            select @ACCOUNTCODERECORDTYPEID = ID from dbo.RECORDTYPE
            where upper(NAME) = 'ACCOUNT CODE'

            select @PROJECTRECORDTYPEID = ID from dbo.RECORDTYPE
            where upper(NAME) = 'PROJECT'

            select @ACCOUNTRECORDTYPEID = ID from dbo.RECORDTYPE
            where upper(NAME) = 'ACCOUNT'

              return 0;