SQL - Add / Update GL and Project Budgets

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

-- =============================================
-- Author:  Alexander Toufexis
-- Create date: 23/09/2015  // update: 24/06/2017 //update 28/03/2020
-- Description: Add/Update Budget Value for Account
-- Update 2020: ID based updating
-- =============================================
CREATE PROCEDURE _as_AddUpdateBudget_V3B
 -- Add the parameters for the stored procedure here
 @GLAccountLink as int,
 @BudgetAmount as float,
 @BudgetAmountForeign as float,
 @PeriodID2 as int,
 @ProjectID as int
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 AccountLink = @GLAccountLink),0)
 SELECT @AccountTypeID = COALESCE((SELECT iAccountType FROM Accounts WHERE AccountLink = @GLLink),0)
 IF(@GLLink = 0)
  BEGIN
    RAISERROR('AccountLink %s not found!', 11, 1, @GLAccountLink);
    RETURN -1
  END

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

  --Check for master project:
  DECLARE @ProjectLevel int
  SELECT @ProjectLevel = (SELECT ProjectLevel FROM Project WHERE ProjectLink = @ProjectLink)
  IF(@ProjectLevel = 1)
  BEGIN
    RAISERROR('Project %s is a master project (with sub-projects) and cannot be assigned a budget!', 11, 1, @ProjectID);
    RETURN -1
  END

  --Getting Period ID:
  DECLARE @PeriodID int
  SELECT @PeriodID = COALESCE((SELECT idPeriod FROM _etblPeriod WHERE _etblPeriod.idPeriod = @PeriodID2),0)
  IF(@PeriodID = 0)
  BEGIN
    RAISERROR('Period %s not found!', 15, 1, @PeriodID2);
    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]
           ,[fBudgetForeign]
     ,[fForecast])
     VALUES
           (@GLLink
           ,@ProjectLink
           ,@PeriodID
           ,0 --<iBudgetTxBranchID, int,>
           ,@AccountTypeID
           ,@BudgetAmount
           ,0 --<fUnprocessedPOValue, float,>
           ,GETDATE()
           ,0 --<_etblBudgets_iBranchID, int,>
           ,@BudgetAmountForeign
     ,0) --Forecast)
  END
 ELSE
  BEGIN
   --Update budget record
   UPDATE [_etblBudgets]
      SET 
      [fBudget] = @BudgetAmount
      ,[fBudgetForeign] = @BudgetAmountForeign
      ,[dBudgetDTStamp] = GETDATE()
    WHERE idBudgets = @LineID
  END
 
END