USP_DATAFORMTEMPLATE_EDIT_PDACCOUNTSTRUCTURE2

The save procedure used by the edit dataform template "Account Structure Edit Form 2".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@DESCRIPTION	nvarchar(100)	IN	Segment name
@SEQUENCE	int	IN	Sequence
@LENGTH	tinyint	IN	Length
@SEPARATORCODE	tinyint	IN	Separator
@PDACCOUNTSYSTEMID	uniqueidentifier	IN
@ISPROJECTSEGMENT	bit	IN	Associate this segment with 'Project'
@PDACCOUNTTABLESAVAILABLEFORSEGMENTID	uniqueidentifier	IN	Calculated using

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PDACCOUNTSTRUCTURE2 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @DESCRIPTION nvarchar(100),
    @SEQUENCE int,
    @LENGTH tinyint,
    @SEPARATORCODE tinyint, 
    @PDACCOUNTSYSTEMID uniqueidentifier,
    @ISPROJECTSEGMENT bit,
    @PDACCOUNTTABLESAVAILABLEFORSEGMENTID uniqueidentifier
)
as

    set nocount on;

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()
    if @SEPARATORCODE is null 
        set @SEPARATORCODE = 1

    begin try
        -- Only one system can be set to default.

        if @ISPROJECTSEGMENT = 1 
            update dbo.PDACCOUNTSTRUCTURE set ISPROJECTSEGMENT = 0 where ISPROJECTSEGMENT = 1 and PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID;

        declare @PDACCOUNTTABLESAVAILABLEFORSEGMENTCHANGED bit = 0;
        if @PDACCOUNTTABLESAVAILABLEFORSEGMENTID != (select PDACCOUNTTABLESAVAILABLEFORSEGMENTID from  PDACCOUNTSTRUCTURE where ID =  @ID)
            set @PDACCOUNTTABLESAVAILABLEFORSEGMENTCHANGED = 1

        if @PDACCOUNTTABLESAVAILABLEFORSEGMENTCHANGED = 1
            begin
                -- remove existing mappings

                delete from dbo.PDCOMPOSITESEGMENTMAPPING where PDACCOUNTSTRUCTUREID = @ID;
                delete from dbo.PDACCOUNTSEGMENTMAPPING where PDACCOUNTSTRUCTUREID = @ID;
            end 
        -- handle updating the data

        declare @ISCONSTANTVALUE bit = 0
            if @PDACCOUNTTABLESAVAILABLEFORSEGMENTID = '1BA6F27F-8454-4FC4-A830-3FDA8CD35D04'
            set @ISCONSTANTVALUE = 1
        declare @SEGMENTTYPE tinyint;
        Select @SEGMENTTYPE = case when ISCODETABLE < 3 then 3 else 4 end  from PDACCOUNTTABLESAVAILABLEFORSEGMENT where ID = @PDACCOUNTTABLESAVAILABLEFORSEGMENTID

        update dbo.PDACCOUNTSTRUCTURE set
            [DESCRIPTION] = @DESCRIPTION,
            SEQUENCE = @SEQUENCE,
      SEGMENTSEQUENCE = @SEQUENCE,
            LENGTH = @LENGTH,
            SEPARATORCODE = @SEPARATORCODE,            
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
      ISPROJECTSEGMENT = @ISPROJECTSEGMENT
      ,PDACCOUNTTABLESAVAILABLEFORSEGMENTID = case ELEMENTDEFINITIONCODE when 1 then PDACCOUNTTABLESAVAILABLEFORSEGMENTID else @PDACCOUNTTABLESAVAILABLEFORSEGMENTID end
      ,ISCONSTANTVALUE = @ISCONSTANTVALUE
      ,SEGMENTTYPE = case ELEMENTDEFINITIONCODE when 1 then 1 else @SEGMENTTYPE end

        where ID = @ID
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

return 0;