USP_DATAFORMTEMPLATE_COPY_GLFISCALYEAR_PRELOAD

The load procedure used by the edit dataform template "Fiscal Year Copy Form"

Parameters

Parameter	Parameter Type	Mode	Description
@COPYID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@YEARID	nvarchar(12)	INOUT	Fiscal year
@DESCRIPTION	nvarchar(60)	INOUT	Description
@FISCALPERIODSINYEAR	tinyint	INOUT	Number of periods
@STATUSCODE	tinyint	INOUT	Status
@FISCALYEARPERIODS	xml	INOUT	Periods
@NEXTFISCALYEARID	nvarchar(12)	INOUT	Year ID
@FIRSTSTARTDATE	datetime	INOUT	First start date
@FIRSTENDDATE	datetime	INOUT	First end date

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_COPY_GLFISCALYEAR_PRELOAD
(
    @COPYID uniqueidentifier,
    @YEARID nvarchar(12) = null output,
    @DESCRIPTION nvarchar(60) = null output,
    @FISCALPERIODSINYEAR tinyint = null output,
    @STATUSCODE tinyint = null output,
    @FISCALYEARPERIODS xml = null output,
    @NEXTFISCALYEARID nvarchar(12) = null output,
    @FIRSTSTARTDATE datetime = null output,
    @FIRSTENDDATE datetime = null output
)
as

    set nocount on;

    declare @NEWSTARTDATE datetime
    select @NEWSTARTDATE = max(ENDDATE)+1 from dbo.GLFISCALPERIOD
    declare @COPYSTARTDATE datetime
    select @COPYSTARTDATE = min(STARTDATE) from dbo.UFN_GLFISCALYEAR_PERIODS(@COPYID)
    if month(@COPYSTARTDATE) <> month(@NEWSTARTDATE) and day(@COPYSTARTDATE) <> day(@NEWSTARTDATE)
        raiserror('ERR_INVALID_STARTDATE',13,1)

    select
        @YEARID = '', 
        @DESCRIPTION = '',
        @FISCALPERIODSINYEAR = FISCALPERIODSINYEAR,
        @STATUSCODE = 1,
        @NEXTFISCALYEARID = ''
    from
        dbo.GLFISCALYEAR
    where
        GLFISCALYEAR.ID = @COPYID

    declare @YEARDIFF int
    select @YEARDIFF = (year(max(T1.ENDDATE)+1) - year(min(V1.STARTDATE))) from dbo.UFN_GLFISCALYEAR_PERIODS(@COPYID) v1, dbo.GLFISCALPERIOD t1

    SELECT @FIRSTSTARTDATE = max(ENDDATE)+1 FROM dbo.GLFISCALPERIOD

    set @FISCALYEARPERIODS = 
        (select 0 as CLOSED, dbo.UFN_GLFISCALYEAR_COPYENDDATE(ENDDATE, @YEARDIFF) as ENDDATE, 
        newid() as ID, SEQUENCE, dbo.UFN_GLFISCALYEAR_COPYSTARTDATE(STARTDATE, @YEARDIFF) as STARTDATE
        from dbo.UFN_GLFISCALYEAR_PERIODS(@COPYID)
        order by SEQUENCE
        for xml raw('ITEM'),type,elements,root('FISCALYEARPERIODS'),BINARY BASE64)

    return 0;