USP_DATAFORMTEMPLATE_EDITLOAD_PDCOMPOSITESEGMENTMAPPING

The load procedure used by the edit dataform template "Composite Segment Mapping 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.
@NAME	nvarchar(50)	INOUT	Name
@FIRST	uniqueidentifier	INOUT	First Table
@SECOND	uniqueidentifier	INOUT	Second Table
@THIRD	uniqueidentifier	INOUT	Third Table
@FOURTH	uniqueidentifier	INOUT	Fourth Table
@TABLE1	uniqueidentifier	INOUT	Segment value
@TABLE2	uniqueidentifier	INOUT	Segment value
@TABLE3	uniqueidentifier	INOUT	Segment value
@TABLE4	uniqueidentifier	INOUT	Segment value
@TABLE1NAME	nvarchar(25)	INOUT	Table 1 Name
@TABLE2NAME	nvarchar(25)	INOUT	Table 2 Name
@TABLE3NAME	nvarchar(25)	INOUT	Table 3 Name
@TABLE4NAME	nvarchar(25)	INOUT	Table 4 Name
@STRUCTUREID	uniqueidentifier	INOUT	Segment value
@ISDEFAULT	bit	INOUT	Default
@PDACCOUNTSEGMENTVALUEID	uniqueidentifier	INOUT	Segment value
@FIRST_2	uniqueidentifier	INOUT	First Table 2
@SECOND_2	uniqueidentifier	INOUT	Second Table 2
@THIRD_2	uniqueidentifier	INOUT	Third Table 2
@FOURTH_2	uniqueidentifier	INOUT	Fourth Table 2
@SHOWSIMPLE1	bit	INOUT	Show Simple 1
@SHOWSIMPLE2	bit	INOUT	Show Simple 2
@SHOWSIMPLE3	bit	INOUT	Show Simple 3
@SHOWSIMPLE4	bit	INOUT	Show Simple 4

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PDCOMPOSITESEGMENTMAPPING(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @NAME nvarchar(50) = null output,    
    @FIRST uniqueidentifier = null output,
    @SECOND uniqueidentifier = null output,
    @THIRD    uniqueidentifier = null output,
    @FOURTH uniqueidentifier = null output,
    @TABLE1 uniqueidentifier = null output,
    @TABLE2 uniqueidentifier = null output,
    @TABLE3 uniqueidentifier = null output,
    @TABLE4 uniqueidentifier = null output,
    @TABLE1NAME nvarchar(25) = null output,
    @TABLE2NAME nvarchar(25) = null output,
    @TABLE3NAME nvarchar(25) = null output,
    @TABLE4NAME nvarchar(25) = null output,        
    @STRUCTUREID uniqueidentifier = null output,
    @ISDEFAULT bit = null output,
    @PDACCOUNTSEGMENTVALUEID uniqueidentifier = null output,
    @FIRST_2 uniqueidentifier = null output,
    @SECOND_2 uniqueidentifier = null output,
    @THIRD_2 uniqueidentifier = null output,
    @FOURTH_2 uniqueidentifier = null output,     
    @SHOWSIMPLE1 bit = null output,
    @SHOWSIMPLE2 bit = null output,
    @SHOWSIMPLE3 bit = null output,
    @SHOWSIMPLE4 bit = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0
    set @TSLONG = 0

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.  Also note that we fetch the TSLONG so that concurrency
    -- can be considered.    
    select
        @DATALOADED = 1,
        @TSLONG = TSLONG,
        @NAME = NAME,
        @FIRST = (select a.LONGDESCRIPTIONID 
            from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
            join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
            where c.SEQUENCE = 1 and b.PDCOMPOSITESEGMENTMAPPINGID = PDCOMPOSITESEGMENTMAPPING.ID),
        @SECOND = (select a.LONGDESCRIPTIONID 
            from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
            join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
            where c.SEQUENCE = 2 and b.PDCOMPOSITESEGMENTMAPPINGID = PDCOMPOSITESEGMENTMAPPING.ID),
        @THIRD = (select a.LONGDESCRIPTIONID 
            from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
            join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
            where c.SEQUENCE = 3 and b.PDCOMPOSITESEGMENTMAPPINGID = PDCOMPOSITESEGMENTMAPPING.ID),    
        @FOURTH = (select a.LONGDESCRIPTIONID 
            from PDCOMPOSITESEGMENTMAPPINGVIEW as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.LONGDESCRIPTIONID = b.LONGDESCRIPTIONID 
            join PDCOMPOSITESEGMENTTABLELIST as c  on b.PDCOMPOSITESEGMENTTABLELISTID = c.id and c.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    = a.PDACCOUNTTABLESAVAILABLEFORSEGMENTID    
            where c.SEQUENCE = 4 and b.PDCOMPOSITESEGMENTMAPPINGID = PDCOMPOSITESEGMENTMAPPING.ID),

        @TABLE1 = (select PDACCOUNTTABLESAVAILABLEFORSEGMENTID from PDCOMPOSITESEGMENTTABLELIST as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.ID = b.PDCOMPOSITESEGMENTTABLELISTID 
            where a.SEQUENCE = 1 and b.PDCOMPOSITESEGMENTMAPPINGID =PDCOMPOSITESEGMENTMAPPING.ID  ),
        @TABLE2 = (select PDACCOUNTTABLESAVAILABLEFORSEGMENTID from PDCOMPOSITESEGMENTTABLELIST as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.ID = b.PDCOMPOSITESEGMENTTABLELISTID 
            where a.SEQUENCE = 2 and b.PDCOMPOSITESEGMENTMAPPINGID =PDCOMPOSITESEGMENTMAPPING.ID  ),
        @TABLE3 = (select PDACCOUNTTABLESAVAILABLEFORSEGMENTID from PDCOMPOSITESEGMENTTABLELIST as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.ID = b.PDCOMPOSITESEGMENTTABLELISTID 
            where a.SEQUENCE = 3 and b.PDCOMPOSITESEGMENTMAPPINGID =PDCOMPOSITESEGMENTMAPPING.ID  ),
        @TABLE4 = (select PDACCOUNTTABLESAVAILABLEFORSEGMENTID from PDCOMPOSITESEGMENTTABLELIST as a 
            join PDCOMPOSITESEGMENTMAPPINGENTRY as b on a.ID = b.PDCOMPOSITESEGMENTTABLELISTID 
            where a.SEQUENCE = 4 and b.PDCOMPOSITESEGMENTMAPPINGID =PDCOMPOSITESEGMENTMAPPING.ID ),
        @TABLE1NAME = (select friendlytablename from PDACCOUNTTABLESAVAILABLEFORSEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where SEQUENCE = 1 and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENTMAPPING.PDCOMPOSITESEGMENTID),
        @TABLE2NAME = (select friendlytablename from PDACCOUNTTABLESAVAILABLEFORSEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where SEQUENCE = 2 and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENTMAPPING.PDCOMPOSITESEGMENTID),
        @TABLE3NAME = isnull((select friendlytablename from PDACCOUNTTABLESAVAILABLEFORSEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where SEQUENCE = 3 and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENTMAPPING.PDCOMPOSITESEGMENTID),'EMPTY'),
        @TABLE4NAME = isnull((select friendlytablename from PDACCOUNTTABLESAVAILABLEFORSEGMENT as a join PDCOMPOSITESEGMENTTABLELIST as b on a.ID = b.PDACCOUNTTABLESAVAILABLEFORSEGMENTID where SEQUENCE = 4 and b.PDCOMPOSITESEGMENTID = PDCOMPOSITESEGMENTMAPPING.PDCOMPOSITESEGMENTID),'EMPTY'),        
        @ISDEFAULT =  ISDEFAULT ,
        @STRUCTUREID = (select ID  from PDACCOUNTSTRUCTURE where PDACCOUNTSTRUCTURE.PDACCOUNTTABLESAVAILABLEFORSEGMENTID = PDCOMPOSITESEGMENTMAPPING.PDCOMPOSITESEGMENTID and PDACCOUNTSTRUCTURE.ID =PDCOMPOSITESEGMENTMAPPING.PDACCOUNTSTRUCTUREID),
        @PDACCOUNTSEGMENTVALUEID=PDACCOUNTSEGMENTVALUEID
    from dbo.PDCOMPOSITESEGMENTMAPPING
    where ID = @ID;

    set @SHOWSIMPLE1 = dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(@TABLE1);
    set @SHOWSIMPLE2 = dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(@TABLE2);
    set @SHOWSIMPLE3 = dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(@TABLE3);
    set @SHOWSIMPLE4 = dbo.UFN_PDCOMPOSITESEGMENTMAPPING_USESIMPLE(@TABLE4);    

    if @SHOWSIMPLE1 = 0
        begin
            set @FIRST_2 = @FIRST
            set @FIRST = null
        end 
    if @SHOWSIMPLE2 = 0
        begin
            set @SECOND_2 = @SECOND
            set @SECOND = null
        end 
    if @SHOWSIMPLE3 = 0
        begin
            set @THIRD_2 = @THIRD
            set @THIRD = null
        end 
    if @SHOWSIMPLE4 = 0
        begin
            set @FOURTH_2 = @FOURTH
            set @FOURTH = null
        end         
    return 0;