USP_DATAFORMTEMPLATE_ADD_PDCOMPOSITESEGMENTMAPPING

The save procedure used by the add dataform template "Composite Segment Mapping Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@NAME	nvarchar(50)	IN	Name
@PDACCOUNTSEGMENTVALUEID	uniqueidentifier	IN	Segment value
@PDACCOUNTSTRUCTUREID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@PDCOMPOSITESEGMENTID	uniqueidentifier	IN	Segment value
@FIRST	uniqueidentifier	IN	First Table
@SECOND	uniqueidentifier	IN	Second Table
@THIRD	uniqueidentifier	IN	Third Table
@FOURTH	uniqueidentifier	IN	Fourth Table
@FIRST_2	uniqueidentifier	IN	First Table 2
@SECOND_2	uniqueidentifier	IN	Second Table 2
@THIRD_2	uniqueidentifier	IN	Third Table 2
@FOURTH_2	uniqueidentifier	IN	Fourth Table 2

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PDCOMPOSITESEGMENTMAPPING
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @NAME nvarchar(50) = null,
    @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null,
    @PDACCOUNTSTRUCTUREID uniqueidentifier ,
    @PDCOMPOSITESEGMENTID uniqueidentifier = null,

    @FIRST    uniqueidentifier = null,
    @SECOND uniqueidentifier = null,
    @THIRD    uniqueidentifier = null,
    @FOURTH uniqueidentifier = null,     

    @FIRST_2 uniqueidentifier = null,
    @SECOND_2 uniqueidentifier = null,
    @THIRD_2 uniqueidentifier = null,
    @FOURTH_2 uniqueidentifier = null     


)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime
declare @FIRSTTABLE uniqueidentifier 
declare @SECONDTABLE uniqueidentifier
declare @THIRDTABLE uniqueidentifier 
declare @FOURTHTABLE uniqueidentifier
declare @TABLECOUNT integer 
declare @DEFAULT bit

select @TABLECOUNT = count(a.ID) from  PDCOMPOSITESEGMENTTABLELIST as a join PDCOMPOSITESEGMENTMAPPING as b on a.PDCOMPOSITESEGMENTID = b.PDCOMPOSITESEGMENTID where b.ISDEFAULT = 1 and b.PDACCOUNTSTRUCTUREID =  @PDACCOUNTSTRUCTUREID
select @FIRSTTABLE = b.ID from PDCOMPOSITESEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDCOMPOSITESEGMENTID join PDACCOUNTSTRUCTURE as c on a.ID = c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where b.SEQUENCE = 1 and c.ID = @PDACCOUNTSTRUCTUREID
select @SECONDTABLE = b.ID from PDCOMPOSITESEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDCOMPOSITESEGMENTID join PDACCOUNTSTRUCTURE as c on a.ID = c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where b.SEQUENCE = 2 and c.ID = @PDACCOUNTSTRUCTUREID
select @THIRDTABLE = b.ID from PDCOMPOSITESEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDCOMPOSITESEGMENTID join PDACCOUNTSTRUCTURE as c on a.ID = c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where b.SEQUENCE = 3 and c.ID = @PDACCOUNTSTRUCTUREID
select @FOURTHTABLE = b.ID from PDCOMPOSITESEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDCOMPOSITESEGMENTID join PDACCOUNTSTRUCTURE as c on a.ID = c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where b.SEQUENCE = 4 and c.ID = @PDACCOUNTSTRUCTUREID



set @CURRENTDATE = getdate()

begin try
  begin tran
        if @THIRD is null and @THIRD_2 is null and @TABLECOUNT > 2  
            raiserror('ERR_PDCOMPOSITESEGMENTMAP_ENTRYTHREE',13,1);
        else
            if @FOURTH is null and @FOURTH_2 is null and @TABLECOUNT > 3  
            raiserror('ERR_PDCOMPOSITESEGMENTMAP_ENTRYFOUR',13,1);        
        else    
            begin 
                -- handle inserting the data

                insert into dbo.PDCOMPOSITESEGMENTMAPPING
                    (ID, NAME, PDCOMPOSITESEGMENTID,PDACCOUNTSEGMENTVALUEID,PDACCOUNTSTRUCTUREID,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (@ID, @NAME, @PDCOMPOSITESEGMENTID,@PDACCOUNTSEGMENTVALUEID,@PDACCOUNTSTRUCTUREID,  @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                set @DEFAULT = 0
                if @FIRST is null
                    set @FIRST = @FIRST_2
                if @FIRST='99999999-9999-9999-9999-999999999999'
                    set @DEFAULT = 1
                insert into dbo.PDCOMPOSITESEGMENTMAPPINGENTRY
                    (ID, PDCOMPOSITESEGMENTMAPPINGID, PDCOMPOSITESEGMENTTABLELISTID, LONGDESCRIPTIONID,ISDEFAULT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (newid(), @ID,@FIRSTTABLE, @FIRST, @DEFAULT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                set @DEFAULT = 0
                if @SECOND is null 
                    set @SECOND = @SECOND_2                    
                if @SECOND='99999999-9999-9999-9999-999999999999'
                    set @DEFAULT = 1
                insert into dbo.PDCOMPOSITESEGMENTMAPPINGENTRY
                    (ID, PDCOMPOSITESEGMENTMAPPINGID, PDCOMPOSITESEGMENTTABLELISTID, LONGDESCRIPTIONID,ISDEFAULT,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                values
                    (newid(), @ID,@SECONDTABLE, @SECOND,  @DEFAULT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                if @THIRDTABLE is not null
                    begin
                        set @DEFAULT = 0
                        if @THIRD is null 
                            set @THIRD = @THIRD_2                            
                        if @THIRD='99999999-9999-9999-9999-999999999999' 
                            set @DEFAULT = 1

                        insert into dbo.PDCOMPOSITESEGMENTMAPPINGENTRY
                            (ID, PDCOMPOSITESEGMENTMAPPINGID, PDCOMPOSITESEGMENTTABLELISTID, LONGDESCRIPTIONID,ISDEFAULT,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values
                            (newid(), @ID, @THIRDTABLE, @THIRD,@DEFAULT , @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                    end

                if @FOURTHTABLE is not null
                    begin
                        set @DEFAULT = 0
                        if @FOURTH is null
                            set @FOURTH = @FOURTH_2                            
                        if @FOURTH='99999999-9999-9999-9999-999999999999' 
                            set @DEFAULT = 1
                            insert into dbo.PDCOMPOSITESEGMENTMAPPINGENTRY
                                (ID, PDCOMPOSITESEGMENTMAPPINGID, PDCOMPOSITESEGMENTTABLELISTID, LONGDESCRIPTIONID,ISDEFAULT,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values
                                (newid(), @ID,@FOURTHTABLE, @FOURTH,@DEFAULT , @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
                    end
            end

        commit
end try

begin catch
    rollback
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0