USP_DESIGNATION_VALIDATEGLMAPPINGS

            CREATE procedure dbo.USP_DESIGNATION_VALIDATEGLMAPPINGS
            (
                @DESIGNATIONID uniqueidentifier, 
                @REVENUETRANSACTIONTYPECODE tinyint,
                @PAYMENTMETHODCODE tinyint,
                @SPLITTYPECODE tinyint,
                @APPLICATIONCODE tinyint,
                @CATEGORYCODEID uniqueidentifier
            )
            as
                set nocount on;

                declare @REVENUECATEGORYACCOUNT nvarchar(100);
                declare @REVENUECATEGORY nvarchar(100);
                declare @ERRORMESSAGE nvarchar(max)
                declare @DESIGNATIONNAME nvarchar(512);
                declare @MAPPINGID uniqueidentifier;
                declare @ACCOUNTTYPE nvarchar(100);

                if dbo.UFN_GLACCOUNT_EXISTS() = 0
              begin
                  -- 1. Validate that the revenue category has been mapped

                  select 
                      @REVENUECATEGORYACCOUNT = coalesce(MAP.ACCOUNTCODE, ''),
                      @REVENUECATEGORY = MAP.REVENUECATEGORYNAME
                  from 
                      dbo.GLREVENUECATEGORYMAPPING MAP 
                  where
                      ID = @CATEGORYCODEID;

                  --The revenue record does not have a category, so use the empty account code from config

                  if @REVENUECATEGORYACCOUNT is null
                      select @REVENUECATEGORY = 'default', @REVENUECATEGORYACCOUNT = coalesce((select top 1 EMPTYREVENUECATEGORYACCOUNTCODE from dbo.GLPREFERENCEINFO), '');

                  --If the revenue category account is not mapped, raise an error 

                  if coalesce(@REVENUECATEGORYACCOUNT, '') = '' and (@SPLITTYPECODE <> 1)
                  begin
                      raiserror('GL account code is not defined for the %s revenue category.', 13, 1, @REVENUECATEGORY);
                      return;
                  end

                  -- 2. Validate that the designation is mapped

                  select 
                      @DESIGNATIONNAME = DESIGNATION.NAME
                  from 
                      dbo.DESIGNATION 
                  where 
                      DESIGNATION.ID = @DESIGNATIONID
                      and ACCOUNTNUMBER = '';

                  if @DESIGNATIONNAME is not null
                  begin
                      raiserror('GL account/fund code is not defined for the %s designation.', 13, 1, @DESIGNATIONNAME);
                      return;
                  end

                  -- 3. Validate that the payment method revenue type mappings have account types defined for them

                  select top 1 
                      @MAPPINGID = MAP.ID
                  from 
                      dbo.GLPAYMENTMETHODREVENUETYPEMAPPING AS MAP 
                  where 
                      MAP.REVENUETRANSACTIONTYPECODE = @REVENUETRANSACTIONTYPECODE 
                      and MAP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE 
                      and MAP.REVENUESPLITTYPECODE = @SPLITTYPECODE 
                      and MAP.APPLICATIONCODE = @APPLICATIONCODE
                      and MAP.GLACCOUNTTYPEMAPPINGID is null

                  if @MAPPINGID is not null
                  begin
                      raiserror('Payment method and revenue type of the revenue record does not map to a valid GL account type.', 13, 1);
                      return;
                  end

                  -- 4. Validate that the account type codes are mapped

                  select top 1 
                      @ACCOUNTTYPE = GLACCOUNTTYPEMAPPING.ACCOUNTTYPE
                  from 
                      dbo.GLPAYMENTMETHODREVENUETYPEMAPPING AS MAP 
                  left join 
                      dbo.GLACCOUNTTYPEMAPPING on MAP.GLACCOUNTTYPEMAPPINGID = GLACCOUNTTYPEMAPPING.ID
                  where 
                      MAP.REVENUETRANSACTIONTYPECODE = @REVENUETRANSACTIONTYPECODE 
                      and MAP.PAYMENTMETHODCODE = @PAYMENTMETHODCODE 
                      and MAP.REVENUESPLITTYPECODE = @SPLITTYPECODE 
                      and MAP.APPLICATIONCODE = @APPLICATIONCODE
                      and coalesce(GLACCOUNTTYPEMAPPING.GLCODE, '') = ''

                  if @ACCOUNTTYPE is not null
                  begin
                      raiserror('GL account code is not defined for the %s account type.', 13, 1, @ACCOUNTTYPE);
                      return;
                  end
        end

                return 0;