USP_REVENUE_STOCK_VALIDATEGLMAPPINGS

      CREATE procedure dbo.USP_REVENUE_STOCK_VALIDATEGLMAPPINGS
      (
        @PDACCOUNTSYSTEMID uniqueidentifier,
        @CURRENTAPPUSERID uniqueidentifier,
        @AMOUNT money,
        @SALEAMOUNT money,
        @NUMBEROFUNITSSOLD money,
        @MEDIANPRICE money,
        @BROKERFEE money
      )
      as
      begin

        if (dbo.UFN_VALID_BASICGL_INSTALLED() != 0 and dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 1)
        begin
          declare @CURRENTVALUE money
          declare @UNMAPPEDITEM int = null

          if @PDACCOUNTSYSTEMID is NULL
              set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER(@CURRENTAPPUSERID)

          -- Collect IDs for all account mappings

          declare @PDACCOUNTCODEMAPPINGIDS table (ID uniqueidentifier)
          insert into @PDACCOUNTCODEMAPPINGIDS (ID)
          select ID from PDACCOUNTCODEMAPPING where PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID and OFFICEID = 9

          -- WARNING: The caller must verify that @NUMBEROFUNITSSOLD <> 0

          set @CURRENTVALUE = (@SALEAMOUNT / @NUMBEROFUNITSSOLD)

          -- Add mappings that are needed to complete the commit

          declare @NEEDEDMAPPINGS table (NAMEID int)
          insert into @NEEDEDMAPPINGS (NAMEID) values (8) -- Sold Stock

          if @CURRENTVALUE > @MEDIANPRICE
              insert into @NEEDEDMAPPINGS (NAMEID) values (16) -- Sold Stock Gain

          if @CURRENTVALUE < @MEDIANPRICE
              insert into @NEEDEDMAPPINGS (NAMEID) values (32) -- Sold Stock Loss


          if @BROKERFEE <> 0
              insert into @NEEDEDMAPPINGS (NAMEID) values (64); -- Fee


          -- Find unmapped items

          with EXISTINGMAPPINGS as
          (
              select
                  NAMEID
              from
                  dbo.PDACCOUNTCODEMAPPING t1
                      inner join dbo.PDACCOUNTCODEMAPPAYMENTMETHODTYPE t2 on (t1.PAYMENTMETHOD & t2.NAMEID) > 0 and t1.OFFICEID = t2.OFFICEID 
              where
                  t1.ID in (select ID from @PDACCOUNTCODEMAPPINGIDS)
          )
          select top 1
              @UNMAPPEDITEM = NAMEID
          from
              @NEEDEDMAPPINGS
          where
              NAMEID not in (select * from EXISTINGMAPPINGS)

          -- Construct error message if needed

          if @UNMAPPEDITEM is not NULL
              begin
                  declare @MESSAGE varchar(500)
                  declare @ACCTSYSNAME varchar(50)
                  declare @OFFICENAME varchar(100)
                  declare @UNMAPPEDITEMNAME varchar(100)

                  -- Get account system name

                  select @ACCTSYSNAME = NAME from dbo.PDACCOUNTSYSTEM where ID = @PDACCOUNTSYSTEMID
                  select @OFFICENAME = NAME from dbo.PDACCOUNTCODEMAPOFFICE where OFFICEID = 9
                  select @UNMAPPEDITEMNAME = TYPENAME from PDACCOUNTCODEMAPPAYMENTMETHODTYPE where OFFICEID = 9 and NAMEID = @UNMAPPEDITEM

                  set @MESSAGE = 'In account system "' + COALESCE(@ACCTSYSNAME,'<Unknown system>') + '", a mapping does not exist for Office: ' + COALESCE(@OFFICENAME,'<Unknown office>') + ', ' + coalesce(@UNMAPPEDITEMNAME,'<Unknown mapping>') 
                  raiserror(@MESSAGE, 13, 1)
              end
        end
      end