USP_GLFISCALYEAR_EDITSAVE

The save procedure used by the edit dataform template "Fiscal Year Edit Form".

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.
@YEARID	nvarchar(12)	IN	Fiscal year
@DESCRIPTION	nvarchar(60)	IN	Description
@FISCALPERIODSINYEAR	tinyint	IN	Number of periods
@FISCALYEARPERIODS	xml	IN	Periods

Definition

Copy



CREATE procedure dbo.USP_GLFISCALYEAR_EDITSAVE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @YEARID nvarchar(12),
    @DESCRIPTION nvarchar(60),
    @FISCALPERIODSINYEAR tinyint,
    @FISCALYEARPERIODS xml
)
as
    set nocount on;

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


    -- Validate that the End of year has not pressed into next year.

    if dbo.UFN_GLFISCALYEAR_VALIDYEARENDDATE(@FISCALYEARPERIODS) = 1
    begin
        RAISERROR('ENDDATEINVALID', 16, 10);
        return 1;
    end

    -- Validate changed periods do not have activity.

    if dbo.UFN_GLFISCALYEAR_PERIODSHAVEACTIVITY(@FISCALYEARPERIODS) = 1
    begin
        RAISERROR('TRANEXISTSINPERIOD', 16, 10);
        return 1;
    end

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

    begin try
        update
            dbo.GLFISCALYEAR 
        set
            YEARID = @YEARID,
            DESCRIPTION = @DESCRIPTION,
            FISCALPERIODSINYEAR = @FISCALPERIODSINYEAR,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where
            ID = @ID;

        exec dbo.USP_GLFISCALYEAR_PERIODS_UPDATEFROMXML @ID, @FISCALYEARPERIODS, @CHANGEAGENTID, @CURRENTDATE;

        update
            dbo.GLFISCALPERIOD
        set 
            STARTDATE = ((SELECT TOP 1 ENDDATE FROM GLFISCALPERIOD FP
                    INNER JOIN GLFISCALYEAR FY ON FP.GLFISCALYEARID = FY.ID
                    WHERE FP.SEQUENCE = FY.FISCALPERIODSINYEAR AND 
                          FY.YEARSEQUENCE = (SELECT YEARSEQUENCE FROM GLFISCALYEAR
                                              WHERE ID = @ID)) + 1)
        from GLFISCALPERIOD FP2
            inner join GLFISCALYEAR FY2 on FP2.GLFISCALYEARID = FY2.ID 
        where FP2.SEQUENCE = 1 AND FY2.YEARSEQUENCE = ((SELECT YEARSEQUENCE FROM GLFISCALYEAR
                                                  WHERE ID = @ID) + 1)

    declare @STATUSCODE tinyint
    set @STATUSCODE = (select STATUSCODE from dbo.GLFISCALYEAR where ID = @ID)
    if @STATUSCODE = 1
    begin
      if not exists (select CLOSED from dbo.UFN_GLFISCALYEAR_PERIODS_FROMITEMLISTXML(@FISCALYEARPERIODS) where CLOSED = 0)
        update dbo.GLFISCALYEAR set STATUSCODE = 2 where ID = @ID
    end
    else if @STATUSCODE = 2
    begin
      if exists (select CLOSED from dbo.UFN_GLFISCALYEAR_PERIODS_FROMITEMLISTXML(@FISCALYEARPERIODS) where CLOSED = 0)
        update dbo.GLFISCALYEAR set STATUSCODE = 1 where ID = @ID
    end


    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch
    return 0;