USP_REVENUE_UPDATEAUCTIONPURCHASEGLDISTRIBUTION_SINGLEREVENUE

CREATE procedure [dbo].[USP_REVENUE_UPDATEAUCTIONPURCHASEGLDISTRIBUTION_SINGLEREVENUE]
(
    @REVENUEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null
)
as
begin

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    begin try

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

        declare @ADJUSTMENTDATE datetime;
        if @ADJUSTMENTDATE is null
            select @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE)

        declare @GLTRANSACTIONSTODELETE table (GLTRANSACTIONID uniqueidentifier,AUCTIONPURCHASEGLDISTRIBUTIONID uniqueidentifier);    

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

        --Delete all distributions not posted 

/*        delete dbo.AUCTIONPURCHASEGLDISTRIBUTION
        output DELETED.GLTRANSACTIONID into @GLTRANSACTIONSTODELETE
        from
             dbo.AUCTIONPURCHASEGLDISTRIBUTION     
            left join dbo.GLTRANSACTION on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
        where
            AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @REVENUEID
            and
            GLTRANSACTION.POSTSTATUSCODE <> 0;
*/        

        insert into @GLTRANSACTIONSTODELETE (GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTIONID)    
        select  AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID,AUCTIONPURCHASEGLDISTRIBUTION.ID 
        from
            dbo.AUCTIONPURCHASEGLDISTRIBUTION     
            left join dbo.GLTRANSACTION on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
        where
            AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @REVENUEID
            and
            GLTRANSACTION.POSTSTATUSCODE <> 0;


        delete dbo.AUCTIONPURCHASEGLDISTRIBUTION where AUCTIONPURCHASEGLDISTRIBUTION.ID in (select AUCTIONPURCHASEGLDISTRIBUTIONID from @GLTRANSACTIONSTODELETE);                                

        -- Delete the GL Transactions

        delete dbo.GLTRANSACTION where GLTRANSACTION.ID in (select GLTRANSACTIONID from @GLTRANSACTIONSTODELETE)

        if((select
                count(*)
            from
                 dbo.AUCTIONPURCHASEGLDISTRIBUTION     
            left join dbo.GLTRANSACTION on
                AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
            where
                AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @REVENUEID and
                GLTRANSACTION.POSTSTATUSCODE = 0) 
            > 0)
        begin
            exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT 
                @REVENUEID = @REVENUEID,
                @DATE = @ADJUSTMENTDATE,
                @CHANGEAGENTID = @CHANGEAGENTID,
                @CHANGEDATE = @CHANGEDATE,
                @POSTDATE = @ADJUSTMENTDATE
        end

        -- Generate GL distributions for the auction purchase record.

        insert into @DISTRIBUTIONS(GLTRANSACTIONID,ACCOUNT,AMOUNT,PROJECT,REFERENCE,TRANSACTIONTYPECODE,POSTDATE,GLPAYMENTMETHODREVENUETYPEMAPPINGID,ACCOUNTID,REVENUEID,REVENUESPLITTYPECODE,POSTSTATUSCODE,AUCTIONITEMPOSTSTATUSCODE,REVENUEPURCHASEID,BASECURRENCYID,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,ORGANIZATIONAMOUNT,ERRORMESSAGE,MAPPEDVALUES)
        select 
            newid(),
            DISTRIBUTIONS.ACCOUNTSTRING,
            DISTRIBUTIONS.AMOUNT,
            DISTRIBUTIONS.PROJECT,
            DISTRIBUTIONS.REFERENCE, 
            DISTRIBUTIONS.TRANSACTIONTYPECODE,
            DISTRIBUTIONS.POSTDATE,
            DISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            DISTRIBUTIONS.ACCOUNTID,
            DISTRIBUTIONS.REVENUEID,
            DISTRIBUTIONS.REVENUESPLITTYPECODE,
            1, --DISTRIBUTIONS.POSTSTATUSCODE,

            DISTRIBUTIONS.AUCTIONITEMPOSTSTATUSCODE,
            DISTRIBUTIONS.REVENUEPURCHASEID,
      DISTRIBUTIONS.BASECURRENCYID,
      DISTRIBUTIONS.TRANSACTIONCURRENCYID,
      DISTRIBUTIONS.BASEEXCHANGERATEID,
      DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
      DISTRIBUTIONS.TRANSACTIONAMOUNT,
      DISTRIBUTIONS.ORGANIZATIONAMOUNT,
      DISTRIBUTIONS.ERRORMESSAGE,
      DISTRIBUTIONS.MAPPEDVALUES
        from 
            dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2(@REVENUEID,null) as DISTRIBUTIONS
        where not exists(select 1 from @DISTRIBUTIONS CURRENTDISTRIBUTIONS where CURRENTDISTRIBUTIONS.REVENUEPURCHASEID = DISTRIBUTIONS.REVENUEPURCHASEID and CURRENTDISTRIBUTIONS.REVENUEID = DISTRIBUTIONS.REVENUEID);

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

        if nullif(@ERRORMESSAGE,'') is not null
            raiserror('%s', 13, 1, @ERRORMESSAGE);
        else
            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)

    if (select count(*) from @DISTRIBUTIONS) > 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 New Distributions----


        insert into dbo.GLTRANSACTION
        (
            ID,
            TRANSACTIONTYPECODE,
            ACCOUNT,
            AMOUNT,PROJECT,
            REFERENCE,
            POSTDATE,
            POSTSTATUSCODE,
            JOURNAL,
            GLACCOUNTID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            TRANSACTIONAMOUNT, 
            ORGANIZATIONAMOUNT, 
            TRANSACTIONCURRENCYID, 
            BASECURRENCYID, 
            BASEEXCHANGERATEID, 
            ORGANIZATIONEXCHANGERATEID
        )
        select 
            DISTRIBUTIONS.GLTRANSACTIONID,
            DISTRIBUTIONS.TRANSACTIONTYPECODE,
            DISTRIBUTIONS.ACCOUNT,
            DISTRIBUTIONS.AMOUNT,
            DISTRIBUTIONS.PROJECT,
            DISTRIBUTIONS.REFERENCE,
            DISTRIBUTIONS.POSTDATE,
            DISTRIBUTIONS.POSTSTATUSCODE,
            @JOURNAL,
            DISTRIBUTIONS.ACCOUNTID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE,
            DISTRIBUTIONS.TRANSACTIONAMOUNT,
            DISTRIBUTIONS.ORGANIZATIONAMOUNT,
            DISTRIBUTIONS.TRANSACTIONCURRENCYID,
            DISTRIBUTIONS.BASECURRENCYID,
            DISTRIBUTIONS.BASEEXCHANGERATEID,
            DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID
        from
            @DISTRIBUTIONS DISTRIBUTIONS 
        where 
            DISTRIBUTIONS.REVENUEPURCHASEID = @REVENUEID

        insert into dbo.AUCTIONPURCHASEGLDISTRIBUTION
        (
            ID,
            REVENUEID,
            GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            PROJECT,
            REFERENCE,
            AMOUNT,
            ACCOUNT,
            TRANSACTIONTYPECODE,
            GLTRANSACTIONID,
            REVENUEPURCHASEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            BASECURRENCYID,
            TRANSACTIONCURRENCYID,
            BASEEXCHANGERATEID,
            ORGANIZATIONEXCHANGERATEID,
            TRANSACTIONAMOUNT,
            ORGANIZATIONAMOUNT
        )
        select
            newid(),
            DISTRIBUTIONS.REVENUEID,
            DISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
            DISTRIBUTIONS.PROJECT,
            DISTRIBUTIONS.REFERENCE,
            DISTRIBUTIONS.AMOUNT,
            DISTRIBUTIONS.ACCOUNT,
            DISTRIBUTIONS.TRANSACTIONTYPECODE,
            DISTRIBUTIONS.GLTRANSACTIONID,
            DISTRIBUTIONS.REVENUEPURCHASEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CHANGEDATE,
            @CHANGEDATE,
            DISTRIBUTIONS.BASECURRENCYID,
            DISTRIBUTIONS.TRANSACTIONCURRENCYID,
            DISTRIBUTIONS.BASEEXCHANGERATEID,
            DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID,
            DISTRIBUTIONS.TRANSACTIONAMOUNT,
            DISTRIBUTIONS.ORGANIZATIONAMOUNT
        from @DISTRIBUTIONS DISTRIBUTIONS 
        where DISTRIBUTIONS.REVENUEPURCHASEID = @REVENUEID

        exec dbo.USP_SAVE_AUCTIONPURCHASEREALIZEDGLDISTRIBUTION 
            @REVENUEID=@REVENUEID, 
            @CHANGEAGENTID=@CHANGEAGENTID, 
            @CURRENTDATE=@CURRENTDATE, 
            @REVENUEIDONLY=1;

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end