Page History

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-----------------------------------------------------------
----------DELETE PROCS IF EXIST:
-----------------------------------------------------------
IF EXISTS ( SELECT  * FROM  sys.objects WHERE   object_id = OBJECT_ID(N'_as_AddUpdateBudget') AND type IN ( N'P', N'PC' ) ) 
DROP PROCEDURE _as_AddUpdateBudget

GO
-----------------------------------------------------------
----------START CREATING PROCS:
-----------------------------------------------------------

-- =============================================
-- Author:		Alexander Toufexis
-- Create date: 23/09/2015, updated sep 2019 (period date possibility)
-- Description:	Add/Update Budget Value for Account
-- =============================================
CREATE PROCEDURE _as_AddUpdateBudget 
	-- Add the parameters for the stored procedure here
	@GLCode as varchar(200),
	@BudgetAmount as float,
	@ForecastAmount as float,
	@PeriodID as int = 0,
	@ProjectCode as varchar(200),
	@PeriodDate as datetime = null
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	--Lookups:
		--Getting GL Account:
	DECLARE @GLLink as int
	DECLARE @AccountTypeID as int
	SELECT @GLLink = COALESCE((SELECT AccountLink FROM Accounts WHERE Master_Sub_Account = @GLCode),0)
	SELECT @AccountTypeID = COALESCE((SELECT iAccountType FROM Accounts WHERE AccountLink = @GLLink),0)
	IF(@GLLink = 0)
		BEGIN
			 RAISERROR('Account %s not found!', 11, 1, @GLCode);
			 RETURN -1
		END

	--if and period is 0 and perioddate is null, then error
	IF(@PeriodID = 0 and @PeriodDate is null)
		BEGIN
			 RAISERROR('Both period and period date are not provided! - GL code: %s', 11, 1, @GLCode);
			 RETURN -1
		END

	--if period is 0, and period date is set, go find the period:
	if (@PeriodID = 0 and COALESCE(@PeriodDate,'2099-01-01') <> '2099-01-01')
		BEGIN
			SELECT @PeriodID = COALESCE((SELECT  min(idPeriod) from _etblPeriod P WHERE  @PeriodDate <=P.dPeriodDate),0)
		END

	--if and period is 0 and perioddate is null, then error
	IF(@PeriodID = 0)
		BEGIN
			 RAISERROR('periodID is 0! for GLCode %s', 11, 1, @GLCode);
			 RETURN -1
		END

		--getting Project:
	DECLARE @ProjectLink as int
	SELECT @ProjectLink = COALESCE((SELECT ProjectLink FROM Project WHERE MasterSubProject = @ProjectCode),0)
	IF(@ProjectLink = 0 AND @ProjectCode != '')
		BEGIN
			 RAISERROR('Project %s not found!', 11, 1, @ProjectCode);
			 RETURN -1
		END

	--Check if budget line already exists:
	DECLARE @LineID as int
	SELECT @LineID = COALESCE((SELECT idBudgets FROM _etblBudgets WHERE iBudgetAccountID = @GLLink AND iBudgetPeriodID = @PeriodID AND iBudgetProjectID = @ProjectLink),0)

	IF (@LineID = 0)
		BEGIN
			--INSERT NEW RECORD
			INSERT INTO [dbo].[_etblBudgets]
           ([iBudgetAccountID]
           ,[iBudgetProjectID]
           ,[iBudgetPeriodID]
           ,[iBudgetTxBranchID]
           ,[iBudgetAccountType]
           ,[fBudget]
           ,[fUnprocessedPOValue]
           ,[dBudgetDTStamp]
           ,[_etblBudgets_iBranchID]
           ,[fForecast])
     VALUES
           (@GLLink
           ,@ProjectLink
           ,@PeriodID
           ,0 --<iBudgetTxBranchID, int,>
           ,@AccountTypeID
           ,@BudgetAmount
           ,0 --<fUnprocessedPOValue, float,>
           ,GETDATE()
           ,0 --<_etblBudgets_iBranchID, int,>
           ,@ForecastAmount)
		END
	ELSE
		BEGIN
			--Update budget record
			UPDATE [_etblBudgets]
			   SET 
				  [fBudget] = @BudgetAmount
				  ,[dBudgetDTStamp] = GETDATE()
				  ,[fForecast] = @ForecastAmount
			 WHERE idBudgets = @LineID
		END
	
END
GO