USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION

            CREATE procedure [dbo].[USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION]
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CHANGEDATE datetime = null,
                @GIFTINKINDSALEID uniqueidentifier = null
            )
            as
                set nocount on;

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

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

                declare @CUSTOMIZED integer = 0;
                exec  @CUSTOMIZED = dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@CHANGEAGENTID,@CHANGEDATE,@GIFTINKINDSALEID

                if @CUSTOMIZED = 0
                begin
                    declare @ERRORMESSAGE nvarchar(max);
                    declare @JOURNAL nvarchar(50);
                    set @JOURNAL = 'Blackbaud Enterprise';

                    declare @DISTRIBUTIONS table(
                        GIFTINKINDSALEID uniqueidentifier,
                        GLTRANSACTIONID uniqueidentifier,
                        ACCOUNT nvarchar(100),
                        AMOUNT money,
                        PROJECT nvarchar(100),
                        REFERENCE nvarchar(255),
                        TRANSACTIONTYPECODE tinyint,
                        POSTDATE datetime,
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                        ACCOUNTID uniqueidentifier,
                        TRANSACTIONAMOUNT money,
                        ORGANIZATIONAMOUNT money,
                        TRANSACTIONCURRENCYID uniqueidentifier,
                        BASECURRENCYID uniqueidentifier,
                        BASEEXCHANGERATEID uniqueidentifier,
                        ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                        POSTSTATUSCODE tinyint,
                        ERRORMESSAGE nvarchar(max),
                        MAPPEDVALUES xml,
                        SOURCELINEITEMID uniqueidentifier,
                        REVENUESPLITID uniqueidentifier        
                    );


                    -- Generate GL distributions for the GIFTINKIND detail record.

                    insert into @DISTRIBUTIONS(
                        GLTRANSACTIONID, 
                        GIFTINKINDSALEID, 
                        ACCOUNT, 
                        AMOUNT, 
                        PROJECT, 
                        REFERENCE, 
                        TRANSACTIONTYPECODE, 
                        POSTDATE, 
                        GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        ACCOUNTID,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        TRANSACTIONCURRENCYID,
                        BASECURRENCYID,
                        BASEEXCHANGERATEID,
                        ORGANIZATIONEXCHANGERATEID,
                        POSTSTATUSCODE,
                        ERRORMESSAGE,
                        MAPPEDVALUES,
                        SOURCELINEITEMID 
                    )
                    select 
                        newid(),
                        GIFTINKINDDISTRIBUTIONS.GIFTINKINDSALEID,
                        GIFTINKINDDISTRIBUTIONS.ACCOUNTSTRING,
                        GIFTINKINDDISTRIBUTIONS.AMOUNT,
                        GIFTINKINDDISTRIBUTIONS.PROJECT,
                        GIFTINKINDDISTRIBUTIONS.REFERENCE, 
                        GIFTINKINDDISTRIBUTIONS.TRANSACTIONTYPECODE,
                        GIFTINKINDDISTRIBUTIONS.POSTDATE,
                        GIFTINKINDDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                        GIFTINKINDDISTRIBUTIONS.ACCOUNTID,
                        GIFTINKINDDISTRIBUTIONS.TRANSACTIONAMOUNT,
                        GIFTINKINDDISTRIBUTIONS.ORGANIZATIONAMOUNT,
                        GIFTINKINDDISTRIBUTIONS.TRANSACTIONCURRENCYID,
                        GIFTINKINDDISTRIBUTIONS.BASECURRENCYID,
                        GIFTINKINDDISTRIBUTIONS.BASEEXCHANGERATEID,
                        GIFTINKINDDISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
                        GIFTINKINDDISTRIBUTIONS.POSTSTATUSCODE,
                        GIFTINKINDDISTRIBUTIONS.ERRORMESSAGE,
                        GIFTINKINDDISTRIBUTIONS.MAPPEDVALUES,
                        GIFTINKINDDISTRIBUTIONS.REVENUESPLITID
                    from 
                        dbo.UFN_REVENUE_GENERATEGIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION(@REVENUEID) as GIFTINKINDDISTRIBUTIONS
                    where not exists
                        (select ID from dbo.GIFTINKINDSALEGLDISTRIBUTION where GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDSALEID = GIFTINKINDDISTRIBUTIONS.GIFTINKINDSALEID and GIFTINKINDSALEGLDISTRIBUTION.OUTDATED = 0)
                        and GIFTINKINDDISTRIBUTIONS.POSTSTATUSCODE > 0
                        and (@GIFTINKINDSALEID is null or GIFTINKINDDISTRIBUTIONS.GIFTINKINDSALEID = @GIFTINKINDSALEID);

                    select top 1 @ERRORMESSAGE = ERRORMESSAGE from @DISTRIBUTIONS where ACCOUNTID is null

                    if @ERRORMESSAGE <> ''
                        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
                            @REVENUEID
                            ,(select TRANSACTIONTYPECODE from dbo.REVENUE where ID = @REVENUEID)
                            ,D.ERRORMESSAGE
                            ,convert(varchar(max),D.MAPPEDVALUES)
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                        from @DISTRIBUTIONS D 
                        where nullif(D.ERRORMESSAGE,'') is not null 
                            and D.ERRORMESSAGE not in (select ERRORMESSAGE from dbo.GLACCOUNTMAPPINGERROR where TRANSACTIONID = @REVENUEID and DELETED = 0)

                    select top 1 @ERRORMESSAGE = dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(POSTDATE)
                    from @DISTRIBUTIONS
                    where
                        POSTSTATUSCODE <> 2 and
                        dbo.UFN_GLDISTRIBUTION_VALIDPOSTDATE(POSTDATE) <> ''

                    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)


                    declare @Split table(SALEID uniqueidentifier,SOURCEID uniqueidentifier,FTLIID uniqueidentifier,    AMOUNT money,TRANSACTIONAMOUNT money,ORGANIZATIONAMOUNT money, POSTDATE datetime,POSTSTATUSCODE tinyint)

                    insert into @Split(SALEID,SOURCEID,FTLIID,POSTDATE,POSTSTATUSCODE,AMOUNT,TRANSACTIONAMOUNT,ORGANIZATIONAMOUNT) 
                    select  D.GIFTINKINDSALEID,D.SOURCELINEITEMID, isnull(FINANCIALTRANSACTIONLINEITEM.ID, newID()),D.POSTDATE,isnull(FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE,1),
                    sum(D.AMOUNT),
                    sum(D.TRANSACTIONAMOUNT),
                    sum(D.ORGANIZATIONAMOUNT)
                    from @DISTRIBUTIONS D 
                     left outer join dbo.FINANCIALTRANSACTIONLINEITEM on D.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID and D.GIFTINKINDSALEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                    where D.TRANSACTIONTYPECODE = 0 
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 
                    group by D.GIFTINKINDSALEID,D.SOURCELINEITEMID,D.REVENUESPLITID,D.POSTDATE,FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE

                    update D set D.REVENUESPLITID = S.FTLIID from @DISTRIBUTIONS D join @Split S on D.SOURCELINEITEMID = S.SOURCEID 

                    merge dbo.FINANCIALTRANSACTIONLINEITEM as target
                    using @Split as source 
                    left outer join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = source.FTLIID
                    on (target.ID = source.FTLIID)
                    when matched then update set
                    target.TRANSACTIONAMOUNT = source.AMOUNT
                    ,target.BASEAMOUNT = source.TRANSACTIONAMOUNT
                    ,target.ORGAMOUNT = source.ORGANIZATIONAMOUNT
          ,target.POSTDATE = source.POSTDATE
                    when not matched by target then 
                    insert 
                    (ID,FINANCIALTRANSACTIONID,SOURCELINEITEMID,TRANSACTIONAMOUNT,VISIBLE,DESCRIPTION
                    ,SEQUENCE,TYPECODE,POSTDATE,POSTSTATUSCODE,BASEAMOUNT,ORGAMOUNT
                    -- Boilerplate

                    ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values  
                    (source.FTLIID,source.SALEID,source.SOURCEID,source.AMOUNT,1,'Sold Gift In Kind',1,0,source.POSTDATE,1,source.TRANSACTIONAMOUNT,source.ORGANIZATIONAMOUNT
                    ,@CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);


                    delete JOURNALENTRY from dbo.JOURNALENTRY join @Split S on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = S.FTLIID 


                    insert into dbo.JOURNALENTRY (ID, FINANCIALTRANSACTIONLINEITEMID, TRANSACTIONTYPECODE, BASEAMOUNT, COMMENT, 
                    POSTDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, GLACCOUNTID, ORGAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, TYPECODE, SUBLEDGERTYPECODE)        
                    select GLTRANSACTIONID, REVENUESPLITID, TRANSACTIONTYPECODE, AMOUNT, REFERENCE,
                    POSTDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, ACCOUNTID, ORGANIZATIONAMOUNT, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, 0, TRANSACTIONTYPECODE
                    from @DISTRIBUTIONS

                    insert into dbo.JOURNALENTRY_EXT (ID, DISTRIBUTIONTABLEID, TABLENAMECODE, PROJECT, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, PRECALCPOSTSTATUSCODE, PRECALCORGANIZATIONEXCHANGERATEID,
                    PRECALCBASEEXCHANGERATEID,  ACCOUNT, GLPAYMENTMETHODREVENUETYPEMAPPINGID,LOGICALREVENUEID,GIFTINKINDSALEID,PAYMENTMETHODCODE)
                    select T1.GLTRANSACTIONID,T1.GLTRANSACTIONID,13,T1.PROJECT, @JOURNAL, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, T1.POSTSTATUSCODE, T1.ORGANIZATIONEXCHANGERATEID,
                    T1.BASEEXCHANGERATEID,  T1.ACCOUNT, T1.GLPAYMENTMETHODREVENUETYPEMAPPINGID,@REVENUEID,T1.GIFTINKINDSALEID,isnull(T2.PAYMENTMETHODCODE,1)
                    from @DISTRIBUTIONS T1
                    left outer join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING T2 on T1.GLPAYMENTMETHODREVENUETYPEMAPPINGID = T2.ID


                    update FINANCIALTRANSACTIONLINEITEM set  FINANCIALTRANSACTIONLINEITEM.DELETEDON = @CHANGEDATE
                    ,FINANCIALTRANSACTIONLINEITEM.TYPECODE = 99        --orphaned

                    ,FINANCIALTRANSACTIONLINEITEM.CHANGEDBYID = @CHANGEAGENTID,FINANCIALTRANSACTIONLINEITEM.DATECHANGED = @CHANGEDATE
                    from dbo.FINANCIALTRANSACTION 
                    join  dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                    left outer join dbo.JOURNALENTRY on FINANCIALTRANSACTIONLINEITEM.ID = JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID
                    left outer join @Split S on FINANCIALTRANSACTIONLINEITEM.ID = S.FTLIID
                    where FINANCIALTRANSACTION.PARENTID = @REVENUEID and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.REVERSEDLINEITEMID is null and JOURNALENTRY.ID is null and S.FTLIID is null

                end