USP_SALESORDER_CREATEDISCOUNTGLDISTRIBUTION

        CREATE procedure [dbo].[USP_SALESORDER_CREATEDISCOUNTGLDISTRIBUTION]
            (@CREDITID uniqueidentifier,
             @CHANGEAGENTID uniqueidentifier = null,
             @CHANGEDATE datetime = null)
        as
        begin
            set nocount on;

            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            declare @JOURNAL nvarchar(50);
            set @JOURNAL = 'Blackbaud Enterprise';

            declare @REFERENCE nvarchar(255);
            declare @CURRENCYID uniqueidentifier;
            select top 1
                @REFERENCE = 'Discount-Order-' + convert(nvarchar(50), SALESORDER.SEQUENCEID)
                ,@CURRENCYID = FT.TRANSACTIONCURRENCYID
            from dbo.CREDITITEM_EXT EXT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = EXT.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
            cross apply (  -- Keeps from scanning the SALESORDER table

                select SALESORDER.SEQUENCEID
                from dbo.SALESORDER
                where SALESORDER.REVENUEID = FT.ID

                union
                select SALESORDER.SEQUENCEID
                from dbo.SALESORDER
                where SALESORDER.ID = EXT.SALESORDERID
            ) as SALESORDER
            where EXT.CREDITID = @CREDITID and LI.TYPECODE = 5;

            declare @DISTRIBUTIONS table(
                CREDITITEMID uniqueidentifier,
                REVENUEID uniqueidentifier,
                GLTRANSACTIONID uniqueidentifier,
                ACCOUNT nvarchar(100),
                AMOUNT money,
                PROJECT nvarchar(100),
                TRANSACTIONTYPECODE tinyint,
                POSTDATE datetime,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                ACCOUNTID uniqueidentifier,
                POSTSTATUSCODE tinyint,
                ERRORMESSAGE nvarchar(max),
                MAPPEDVALUES xml
            );

            -- Generate GL distributions for the discount record.

            insert into @DISTRIBUTIONS(GLTRANSACTIONID, CREDITITEMID, REVENUEID, ACCOUNT, AMOUNT, PROJECT, TRANSACTIONTYPECODE, POSTDATE, GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,POSTSTATUSCODE,ERRORMESSAGE,MAPPEDVALUES)
            select 
                newid(),
                DISCOUNTDISTRIBUTIONS.CREDITITEMID,
                DISCOUNTDISTRIBUTIONS.REVENUEID,
                DISCOUNTDISTRIBUTIONS.ACCOUNTSTRING,
                DISCOUNTDISTRIBUTIONS.AMOUNT,
                DISCOUNTDISTRIBUTIONS.PROJECT,
                DISCOUNTDISTRIBUTIONS.TRANSACTIONTYPECODE,
                DISCOUNTDISTRIBUTIONS.POSTDATE,
                DISCOUNTDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                DISCOUNTDISTRIBUTIONS.ACCOUNTID,
                DISCOUNTDISTRIBUTIONS.POSTSTATUSCODE,
                DISCOUNTDISTRIBUTIONS.ERRORMESSAGE,
                DISCOUNTDISTRIBUTIONS.MAPPEDVALUES
            from 
                dbo.UFN_CREDIT_GENERATEDISCOUNTGLDISTRIBUTION(@CREDITID) as DISCOUNTDISTRIBUTIONS
            where not exists
                (select ID from dbo.JOURNALENTRY_EXT where JOURNALENTRY_EXT.CREDITITEMID = DISCOUNTDISTRIBUTIONS.CREDITITEMID and JOURNALENTRY_EXT.OUTDATED = 0)

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

            if nullif(@ERRORMESSAGE,'') is not null
                raiserror('%s', 13, 1, @ERRORMESSAGE);
            else
                --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
                    isnull(S.ID, R.ID)
                    ,R.TYPECODE
                    ,D.ERRORMESSAGE
                    ,convert(varchar(max),D.MAPPEDVALUES)
                    ,@CHANGEAGENTID
                    ,@CHANGEAGENTID
                    ,@CHANGEDATE
                    ,@CHANGEDATE
                from @DISTRIBUTIONS D 
                inner join dbo.FINANCIALTRANSACTION R on R.ID = D.REVENUEID
                left join dbo.SALESORDER S on S.REVENUEID = R.ID
                where nullif(D.ERRORMESSAGE,'') is not null 
                    and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = isnull(S.ID, R.ID) and DELETED = 0)                        

            delete from @DISTRIBUTIONS where POSTSTATUSCODE <> 1

            if @@rowcount > 0      
                set @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE((select top 1 POSTDATE from @DISTRIBUTIONS));

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

      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
          v.MD5HASHCODE
        ,d.ERRORMESSAGE
        ,d.ACCOUNTID
        ,d.TRANSACTIONTYPECODE
        ,v.ERRORCODE
        ,v.MAPPEDVALUES
        ,v.PDACCOUNTSYSTEMID
      from @DISTRIBUTIONS d
      cross apply dbo.UFN_PDACCOUNTLOOKUPCACHE_PARSE_MAPPEDVALUES(MAPPEDVALUES) v
      where v.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 tt
      where not exists (select MD5HASHCODE from dbo.PDACCOUNTLOOKUPCACHE with (NOLOCK) where MD5HASHCODE = tt.HashCode and TRANSACTIONTYPECODE = tt.TransactionTypeCode and MAPPEDVALUES = tt.MappedValues)

insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, BASEAMOUNT, TRANSACTIONAMOUNT, ORGAMOUNT, POSTDATE, COMMENT, GLACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,TRANSACTIONCURRENCYID)
select DIST.GLTRANSACTIONID, DIST.CREDITITEMID, DIST.TRANSACTIONTYPECODE, DIST.AMOUNT, DIST.AMOUNT, DIST.AMOUNT, DIST.POSTDATE, @REFERENCE, DIST.ACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @CURRENCYID
from @DISTRIBUTIONS as DIST 

insert into dbo.JOURNALENTRY_EXT (ID, TABLENAMECODE, JOURNAL, CREDITITEMID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select DIST.GLTRANSACTIONID, 6, @JOURNAL, DIST.CREDITITEMID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
from @DISTRIBUTIONS as DIST

        end