USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION

CREATE procedure dbo.USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION
(
    @REVENUEID uniqueidentifier,
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @REVENUEIDONLY bit = 0
)
as
begin

    /* To calculate the currency gains and loses for auctions, we just need to compare the amount of the debits and credits
        per financial transaction line item and create balancing distributions based on those amounts alone.*/

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

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

    declare @CUSTOMIZED integer = 0;
    exec  @CUSTOMIZED = dbo.USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION_CUSTOMIZE @REVENUEID,@PDACCOUNTSYSTEMID,@CHANGEAGENTID,@CURRENTDATE,@REVENUEIDONLY

    if @CUSTOMIZED = 0
    begin
        declare @TRANSACTIONCURRENCYID uniqueidentifier;
        declare @POSTDATE date;
        declare @POSTSTATUSCODE tinyint
        select @PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
            ,@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
            ,@POSTDATE = FT.POSTDATE
            ,@POSTSTATUSCODE = 1
        from dbo.FINANCIALTRANSACTION FT
        where FT.ID = @REVENUEID;    

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

        declare @DISTRIBUTION table (
                ID uniqueidentifier,
                REVENUESPLITID uniqueidentifier,
                GLTRANSACTIONID uniqueidentifier,
                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
                TRANSACTIONTYPECODE tinyint,
                POSTDATE datetime,
                POSTSTATUSCODE tinyint,
                ACCOUNTSTRING nvarchar(100),
                PROJECT nvarchar(100),
                AMOUNT money,
                REFERENCE nvarchar(255),
                JOURNAL nvarchar(50),
                ERRORMESSAGE nvarchar(max),
                GLACCOUNTID uniqueidentifier,
                REVENUEPURCHASEID uniqueidentifier,
                ORGANIZATIONAMOUNT money,
                MAPPEDVALUES xml
        )

        declare @AMOUNTS UDT_GAINLOSS_AMOUNTS;
        insert into @AMOUNTS (
            ID
            ,NEWBASEAMOUNT
            ,ORIGINALBASEAMOUNT
            ,NEWORGANIZATIONAMOUNT
            ,ORIGINALORGANIZATIONAMOUNT
        )
        select LI.ID
            ,SUM(case when JE.TRANSACTIONTYPECODE = 1 then JE.BASEAMOUNT else 0 end) [CREDITBASEAMOUNT]
            ,SUM(case when JE.TRANSACTIONTYPECODE = 0 then JE.BASEAMOUNT else 0 end) [DEBITBASEAMOUNT]
            ,SUM(case when JE.TRANSACTIONTYPECODE = 1 then JE.ORGAMOUNT else 0 end) [CREDITORGAMOUNT]
            ,SUM(case when JE.TRANSACTIONTYPECODE = 0 then JE.ORGAMOUNT else 0 end) [DEBITORGAMOUNT]
        from dbo.FINANCIALTRANSACTIONLINEITEM LI
        inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
        inner join dbo.JOURNALENTRY_EXT X on X.ID = JE.ID
        where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.TYPECODE != 1 and LI.DELETEDON is null
            and X.TABLENAMECODE = 2
        group by LI.ID;

        declare @DEBITSIDE bit = 0;

        insert into @DISTRIBUTION (
            REVENUESPLITID
            ,GLTRANSACTIONID
            ,ACCOUNTSTRING
            ,GLACCOUNTID
            ,TRANSACTIONTYPECODE
            ,AMOUNT
            ,ORGANIZATIONAMOUNT
            ,REFERENCE
            ,ERRORMESSAGE
            ,MAPPEDVALUES
        )
        exec dbo.USP_GET_GAINLOSSDISTRIBUTIONS @AMOUNTS, @DEBITSIDE, @TRANSACTIONCURRENCYID, @PDACCOUNTSYSTEMID;

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

        update DISTRIBUTION set
            ID = GLTRANSACTIONID
            ,GLPAYMENTMETHODREVENUETYPEMAPPINGID = dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, FT.TYPECODE, REVENUESPLIT.TYPECODE, REVENUESPLIT.APPLICATIONCODE, DISTRIBUTION.TRANSACTIONTYPECODE)
            ,PROJECT = ''
            ,JOURNAL = @JOURNAL
            ,POSTSTATUSCODE = @POSTSTATUSCODE
            ,POSTDATE = @POSTDATE
        from @DISTRIBUTION DISTRIBUTION
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = @REVENUEID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on FT.ID = LI.FINANCIALTRANSACTIONID
        inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on REVENUESPLIT.ID = LI.ID
        left join dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID;

        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.GLACCOUNTID
            ,d.TRANSACTIONTYPECODE
            ,v.ERRORCODE
            ,v.MAPPEDVALUES
            ,v.PDACCOUNTSYSTEMID
        from @DISTRIBUTION 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 the newly generated distributions into the GLTRANSACTION table

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

        insert into dbo.JOURNALENTRY_EXT (
            ID
            ,DISTRIBUTIONTABLEID
            ,REVENUEPURCHASEID
            ,TABLENAMECODE
            ,PROJECT
            ,JOURNAL
            ,PRECALCPOSTSTATUSCODE
            ,PRECALCORGANIZATIONEXCHANGERATEID
            ,PRECALCBASEEXCHANGERATEID
            ,ACCOUNT
            ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
            ,ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            GLTRANSACTIONID
            ,GLTRANSACTIONID
            ,@REVENUEID
            ,2
            ,''
            ,@JOURNAL
            ,POSTSTATUSCODE
            ,null
            ,null
            ,ACCOUNTSTRING
            ,GLPAYMENTMETHODREVENUETYPEMAPPINGID
            ,@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @DISTRIBUTION
    end
end