USP_REVENUE_VALIDATEGLMAPPING

      CREATE procedure [dbo].[USP_REVENUE_VALIDATEGLMAPPING]
      (
        @REVENUEID as uniqueidentifier,
        @INFORMATIONSOURCECODE tinyint, -- 0 revenue tables, 1 batch revenue tables

        @CURRENTAPPUSERID uniqueidentifier = null
      )
      as

      /*
        You can alter this function to return customized data (account string, project etc.) as long as 
        the altered function takes the same parameters and 
        returns the four columns returned by the function call below
      */

      begin
        if dbo.UFN_VALID_BASICGL_INSTALLED() != 0
        begin
          -- For revenue batch if generate GL account does not have support we simply skip this section.

          -- This is only being done to allow customers who currently have custom segments defined to continue to work 

          -- with those custom segments in batch, however they will NOT see GL mapping errors in validation.

          -- Instead those errors will appear in COMMIT.

          declare @CONTINUE bit = 1;

          if (@INFORMATIONSOURCECODE = 1)
            set @CONTINUE = dbo.UFN_REVENUE_GENERATEGLACCOUNT_SUPPORTSINFORMATIONSOURCE(@INFORMATIONSOURCECODE);

          if (@CONTINUE = 1)
          begin
            declare @DATA table 
            (
              POSTDATE datetime,
              ERRORMESSAGE nvarchar(max),
              MAPPEDVALUES xml,
              ACCOUNTID uniqueidentifier,
              TRANSACTIONTYPECODE tinyint
            );

            declare @CURRENTDATE date = getdate();
            declare @REVENUEINFORMATION xml = dbo.UFN_REVENUEINFORMATION_TOITEMLISTXML(@REVENUEID, @INFORMATIONSOURCECODE, @CURRENTAPPUSERID)
            declare @REVENUEINFORMATION_DATE datetime;
            declare @REVENUEINFORMATION_POSTDATE datetime;
            declare @REVENUEINFORMATION_TRANSACTIONTYPECODE int;
            declare @REVENUEINFORMATION_DONOTPOST tinyint;

            select 
              @REVENUEINFORMATION_TRANSACTIONTYPECODE = T.c.value('(TRANSACTIONTYPECODE)[1]', 'int') ,
              @REVENUEINFORMATION_POSTDATE = T.c.value('(POSTDATE)[1]', 'datetime'),
              @REVENUEINFORMATION_DATE = T.c.value('(DATE)[1]', 'datetime'),
              @REVENUEINFORMATION_DONOTPOST = T.c.value('(DONOTPOST)[1]', 'tinyint')
            from @REVENUEINFORMATION.nodes('REVENUEINFORMATION/ITEM') T(c);

            declare @REVENUESPLITINFORMATION xml = dbo.UFN_REVENUESPLITINFORMATION_ALL_TOITEMLISTXML(@REVENUEID, @INFORMATIONSOURCECODE, @CURRENTAPPUSERID);

            with split_cte(SPLITXML) as
            (
              select T.c.query('<REVENUESPLITINFORMATION>{.}</REVENUESPLITINFORMATION>') SPLITXML
              from @REVENUESPLITINFORMATION.nodes('/REVENUESPLITINFORMATION/ITEM') T(c)
            )
            insert into @DATA
            (
              POSTDATE,
              ERRORMESSAGE,
              MAPPEDVALUES,
              ACCOUNTID,
              TRANSACTIONTYPECODE
            )
            select
              coalesce(@REVENUEINFORMATION_POSTDATE, @REVENUEINFORMATION_DATE, dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)),
              ERRORMESSAGE,
              MAPPEDVALUES,
              ACCOUNTID,
              @REVENUEINFORMATION_TRANSACTIONTYPECODE
            from split_cte
            cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT_STANDARD_5(@REVENUEINFORMATION, split_cte.SPLITXML, @INFORMATIONSOURCECODE, @CURRENTAPPUSERID);

            declare @ERRORMESSAGE nvarchar(max);
            select top 1 @ERRORMESSAGE = ERRORMESSAGE
            from @DATA
            where
              nullif(ERRORMESSAGE,'') is not null and
              ACCOUNTID is null;

            if nullif(@ERRORMESSAGE,'') is not null
              raiserror('%s', 13, 1, @ERRORMESSAGE);
            else
            begin
      declare @CHANGEAGENTID uniqueidentifier;
              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

              --Trap the error when a holding account exists so we can show it to the user later.

              insert into dbo.GLACCOUNTMAPPINGERROR (
                [TRANSACTIONID],
                [TRANSACTIONTYPECODE],
                [ERRORMESSAGE],
                [MAPPEDVALUES],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED])
              select distinct
                @REVENUEID,
                D.TRANSACTIONTYPECODE,
                D.ERRORMESSAGE,
                convert(varchar(max),D.MAPPEDVALUES),
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
              from @DATA D 
              where
                nullif(D.ERRORMESSAGE,'') is not null and
                D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @REVENUEID and DELETED = 0);
            end

            --Check to see if there is an adjustment, if so, use adjustment poststatuscode

            declare @ADJUSTMENTPOSTSTATUSCODE tinyint;

            if @REVENUEINFORMATION_DONOTPOST = 1
              set @ADJUSTMENTPOSTSTATUSCODE = 2;
            else
              set @ADJUSTMENTPOSTSTATUSCODE = 1;

            if exists (select ID from ADJUSTMENT with (nolock) where REVENUEID = @REVENUEID)
              select @ADJUSTMENTPOSTSTATUSCODE = POSTSTATUSCODE
              from dbo.ADJUSTMENT with (nolock)
              where REVENUEID = @REVENUEID;

            if exists (select top 1 * from @DATA) and @ADJUSTMENTPOSTSTATUSCODE <> 2
              set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DATA));

            if @ERRORMESSAGE <> ''
              raiserror(@ERRORMESSAGE, 13, 1);
          end

          declare @CACHE table
          (
            HASHCODE uniqueidentifier,
            ERRORMESSAGE nvarchar(max),
            GLACCOUNTID uniqueidentifier,
            TRANSACTIONTYPECODE tinyint,
            ERRORNUMBER tinyint,
            MAPPEDVALUES nvarchar(max),
            PDACCOUNTSYSTEMID uniqueidentifier
          );

          insert into @CACHE
          (
            HASHCODE,
            ERRORMESSAGE,
            GLACCOUNTID,
            TRANSACTIONTYPECODE,
            ERRORNUMBER,
            MAPPEDVALUES,
            PDACCOUNTSYSTEMID
          )
          select
            [VALUES].MD5HASHCODE,
            DATA.ERRORMESSAGE,
            DATA.ACCOUNTID,
            DATA.TRANSACTIONTYPECODE,
            [VALUES].ERRORCODE,
            [VALUES].MAPPEDVALUES,
            [VALUES].PDACCOUNTSYSTEMID
          from @DATA DATA
          cross apply dbo.UFN_PDACCOUNTLOOKUPCACHE_PARSE_MAPPEDVALUES(MAPPEDVALUES) [VALUES]
          where [VALUES].MD5HASHCODE is not null;

          insert into dbo.PDACCOUNTLOOKUPCACHE
          (
            MD5HASHCODE,
            ERRORMESSAGE,
            GLACCOUNTID,
            TRANSACTIONTYPECODE,
            ERRORNUMBER,
            MAPPEDVALUES,
            PDACCOUNTSYSTEMID
          )
          select
            HASHCODE,
            ERRORMESSAGE,
            GLACCOUNTID,
            TRANSACTIONTYPECODE,
            ERRORNUMBER,
            MAPPEDVALUES,
            PDACCOUNTSYSTEMID
          from @CACHE CACHE
          where not exists (
            select MD5HASHCODE
            from dbo.PDACCOUNTLOOKUPCACHE with (NOLOCK)
            where
              MD5HASHCODE = CACHE.HASHCODE and
              TRANSACTIONTYPECODE = CACHE.TRANSACTIONTYPECODE and
              MAPPEDVALUES = CACHE.MAPPEDVALUES
              );
        end
   end