USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVESNAPSHOT
            (
                @ADJUSTMENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @ISNEWREVENUE bit = 0
            )
            as 
            set nocount on;

            declare @CHANGEDATE datetime;
            declare @REVENUEID uniqueidentifier;
            declare @REVENUETYPECODE tinyint;
            declare @ADJUSTMENTHISTORYID uniqueidentifier;
            declare @SNAPSHOTEXISTS bit;
            declare @BASECURRENCYID uniqueidentifier;

            set @CHANGEDATE = getdate();
            set @SNAPSHOTEXISTS = 0;

            /*determine if a snapshot exists */
            if (select count(ADJUSTMENTHISTORYREVENUETRANSACTION.ID)
                from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION 
                inner join dbo.ADJUSTMENTHISTORY on ADJUSTMENTHISTORY.ID = ADJUSTMENTHISTORYREVENUETRANSACTION.ID
                where ADJUSTMENTHISTORY.ADJUSTMENTID = @ADJUSTMENTID) > 0
                set @SNAPSHOTEXISTS = 1;
            else
                set @SNAPSHOTEXISTS = 0;

            if @SNAPSHOTEXISTS = 1 /*update the ADJUSTMENTHISTORY record to match the ADJUSTMENTRECORD*/
            begin
                select @ADJUSTMENTHISTORYID = ID 
                from dbo.ADJUSTMENTHISTORY 
                where ADJUSTMENTID = @ADJUSTMENTID;

                if @ADJUSTMENTHISTORYID is not null
                    update dbo.ADJUSTMENTHISTORY set
                        ADJUSTMENTHISTORY.CONSTITUENTNAME = CONSTITUENT.NAME,
                        ADJUSTMENTHISTORY.REVENUEDATE = FINANCIALTRANSACTION.DATE,
                        ADJUSTMENTHISTORY.ADJUSTMENTDATE = ADJUSTMENT.DATE,
                        ADJUSTMENTHISTORY.ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
                        ADJUSTMENTHISTORY.ADJUSTMENTREASON = ADJUSTMENT.REASON,
                        ADJUSTMENTHISTORY.ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                    from dbo.ADJUSTMENTHISTORY    
                    inner join dbo.ADJUSTMENT on ADJUSTMENT.ID = ADJUSTMENTHISTORY.ADJUSTMENTID
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
                    where ADJUSTMENT.ID = @ADJUSTMENTID
            end
            else
            begin
                /*since the snapshot has an FK to ADJUSTMENTHISTORY, need to create an ADJUSTMENTHISTORY row. The needed information is 
                  attached to the ADJUSTMENT record */
                select
                    @REVENUEID = ADJUSTMENT.REVENUEID,
                    @REVENUETYPECODE = FINANCIALTRANSACTION.TYPECODE
                from dbo.ADJUSTMENT 
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID
                where ADJUSTMENT.ID = @ADJUSTMENTID;

                if object_id('tempdb..#TMP_ADJUSTMENTHISTORY_LINEITEMS') is not null
                    drop table #TMP_ADJUSTMENTHISTORY_LINEITEMS;

                create table #TMP_ADJUSTMENTHISTORY_LINEITEMS
                (
                    FT_ID uniqueidentifier
                    ,FT_TYPECODE tinyint
                    ,FT_TYPE nvarchar(27) collate database_default
                    ,LI_ID uniqueidentifier
                    ,LI_APPLICATIONCODE tinyint
                    ,LI_APPLICATION nvarchar(27) collate database_default
                    ,TRANSACTIONAMOUNT money
                    ,BASEAMOUNT money
                    ,ORGAMOUNT money
                    ,DESIGNATIONID uniqueidentifier
                    ,PDACCOUNTSYSTEMID uniqueidentifier
                )

                insert into #TMP_ADJUSTMENTHISTORY_LINEITEMS
                (
                    FT_ID
                    ,FT_TYPECODE
                    ,FT_TYPE
                    ,LI_ID 
                    ,LI_APPLICATIONCODE 
                    ,LI_APPLICATION 
                    ,TRANSACTIONAMOUNT 
                    ,BASEAMOUNT 
                    ,ORGAMOUNT 
                    ,DESIGNATIONID
                    ,PDACCOUNTSYSTEMID
                )
                select
                    FT.ID
                    ,FT.TYPECODE
                    ,FT.TYPE
                    ,LI.ID
                    ,RSE.APPLICATIONCODE
                    ,RSE.APPLICATION
                    ,LI.TRANSACTIONAMOUNT
                    ,LI.BASEAMOUNT
                    ,LI.ORGAMOUNT
                    ,RSE.DESIGNATIONID
                    ,FT.PDACCOUNTSYSTEMID
                from dbo.FINANCIALTRANSACTION FT
                inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
                inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
                where FT.ID = @REVENUEID and LI.DELETEDON is null;

                select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                from dbo.FINANCIALTRANSACTION FT
                inner join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
                inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
                where FT.ID = @REVENUEID;

                set @ADJUSTMENTHISTORYID = newid();

                insert into dbo.ADJUSTMENTHISTORY(ID, ADJUSTMENTID, REVENUEIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, ISNEWREVENUE, REVENUETYPE, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADJUSTMENTREASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select top 1
                        @ADJUSTMENTHISTORYID,
                        ADJUSTMENT.ID,
                        cast(ADJUSTMENT.REVENUEID as nvarchar(36)),
                        cast(ADJUSTMENT.ID as nvarchar(36)),
                        isnull(CONSTITUENT.NAME, ''),
                        @ISNEWREVENUE,
                        FINANCIALTRANSACTION.TYPE,
                        FINANCIALTRANSACTION.DATE,
                        ADJUSTMENT.DATE,
                        ADJUSTMENT.POSTDATE,
                        ADJUSTMENT.REASON,
                        ADJUSTMENT.REASONCODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from dbo.ADJUSTMENT
                    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = ADJUSTMENT.REVENUEID
                    left outer join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
                    where ADJUSTMENT.ID = @ADJUSTMENTID;

                /*create the snapshot of the revenue record*/
                insert into dbo.ADJUSTMENTHISTORYREVENUETRANSACTION(ID, PAYMENTMETHODCODE, AMOUNT, CONSTITUENTIDENTIFIER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASERATE, ORGANIZATIONRATE)
                    select
                        @ADJUSTMENTHISTORYID,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
                        FINANCIALTRANSACTION.BASEAMOUNT,
                        FINANCIALTRANSACTION.CONSTITUENTID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                        FINANCIALTRANSACTION.ORGAMOUNT,
                        coalesce(BASERATE.RATE,0),
                        coalesce(ORGANIZATIONRATE.RATE,0)
                    from dbo.FINANCIALTRANSACTION
                        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
                        left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
                        left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
                    where FINANCIALTRANSACTION.ID = @REVENUEID;

                /*create the snapshot of the revenue details*/
                insert into dbo.ADJUSTMENTHISTORYREVENUE(ADJUSTMENTHISTORYID, REVENUEIDENTIFIER, AMOUNT, TYPECODE, TYPE, APPLICATION, APPLICATIONCODE, REVENUECATEGORYCODEIDENTIFIER, REVENUECATEGORYCODETRANSLATION, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT)
                    select
                        @ADJUSTMENTHISTORYID,
                        cast([SPLITS].FT_ID as nvarchar(36)),
                        [SPLITS].BASEAMOUNT,
                        [SPLITS].FT_TYPECODE,
                        [SPLITS].FT_TYPE,
                        [SPLITS].LI_APPLICATION,
                        [SPLITS].LI_APPLICATIONCODE,
                        case when REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null then '00000000-0000-0000-0000-000000000000' else cast(REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID as nvarchar(36)) end,
                        case when REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID is null then '' else (select top 1 REVENUECATEGORYNAME from dbo.GLREVENUECATEGORYMAPPING where GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID) end,

                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        [SPLITS].TRANSACTIONAMOUNT,
                        [SPLITS].ORGAMOUNT
                    from #TMP_ADJUSTMENTHISTORY_LINEITEMS [SPLITS]
                    left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = [SPLITS].LI_ID;


                /*create the detail snapshot*/
                insert into dbo.ADJUSTMENTHISTORYSPLIT(ADJUSTMENTHISTORYID, REVENUEIDENTIFIER, TYPE, DESIGNATIONIDENTIFIER, DESIGNATIONNAME, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT)
                    select
                        @ADJUSTMENTHISTORYID,
                        cast([SPLITS].FT_ID as nvarchar(36)),
                        [SPLITS].FT_TYPE,
                        coalesce(cast([SPLITS].DESIGNATIONID as nvarchar(36)), ''),
                        coalesce(dbo.UFN_DESIGNATION_BUILDNAME([SPLITS].DESIGNATIONID), ''),
                        [SPLITS].BASEAMOUNT, 
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        [SPLITS].TRANSACTIONAMOUNT,
                        [SPLITS].ORGAMOUNT
                    from #TMP_ADJUSTMENTHISTORY_LINEITEMS [SPLITS];

                /*create the distribution snapshot*/
                /*JamesWill CR254668-091306 10/02/2006 Reverse the debit and credit accounts to actually capture a reversal*/
                insert into dbo.ADJUSTMENTHISTORYDISTRIBUTIONSNAPSHOT(ADJUSTMENTHISTORYID, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        @ADJUSTMENTHISTORYID,
                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                        isnull(GLACCOUNT.ACCOUNTNUMBER, JOURNALENTRY_EXT.ACCOUNT),                    
                        JOURNALENTRY_EXT.PROJECT,
                        JOURNALENTRY.COMMENT,
                        JOURNALENTRY.BASEAMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        JOURNALENTRY.TRANSACTIONAMOUNT,
                        JOURNALENTRY.ORGAMOUNT,
                        @BASECURRENCYID,
                        JOURNALENTRY.TRANSACTIONCURRENCYID
                    from #TMP_ADJUSTMENTHISTORY_LINEITEMS [SPLITS]
                    inner join dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = [SPLITS].LI_ID
                    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
                    left join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID;

                /*JamesWill 03/17/2008 The last adjustment may have been adding brand new revenue to an existing transaction. In this case, there will be no distribution snapshot for the previous adjustment (since new revenue 
                    will not have anything in REVENUEGLDISTRIBUTION until after the adjustment is posted). So the report will be able to report this distribution correctly in the future, we should write it out now.
                    This is a perfect opportunity to do so because we know this is the next adjustment (therefore the previous adjustment MUST have been posted) and we know that the information in REVENUEGLDISTRIBUTION 
                    is still accurate for that adjustment (since changes to it will not be made until this adjustment). */

                declare @PREVIOUSADJUSTMENTID uniqueidentifier;
                declare @LASTADJUSTMENTWASNEWREVENUE bit;

                select top 1 @PREVIOUSADJUSTMENTID = ID, @LASTADJUSTMENTWASNEWREVENUE = ISNEWREVENUE from dbo.ADJUSTMENTHISTORY where ID <> @ADJUSTMENTHISTORYID and REVENUEIDENTIFIER = cast(@REVENUEID as nvarchar(36)) order by DATEADDED desc;
                if not @PREVIOUSADJUSTMENTID is null and @LASTADJUSTMENTWASNEWREVENUE = 1
                    insert into dbo.ADJUSTMENTHISTORYDISTRIBUTION(ID, ADJUSTMENTHISTORYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                        select
                            newid(),
                            @PREVIOUSADJUSTMENTID,
                            1,
                            JOURNALENTRY.TRANSACTIONTYPECODE,
                            isnull(GLACCOUNT.ACCOUNTNUMBER, JOURNALENTRY_EXT.ACCOUNT),
                            JOURNALENTRY_EXT.PROJECT,
                            JOURNALENTRY.COMMENT,
                            JOURNALENTRY.BASEAMOUNT,
                            @CHANGEAGENTID, 
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            JOURNALENTRY.TRANSACTIONAMOUNT,
                            JOURNALENTRY.ORGAMOUNT,
                            @BASECURRENCYID,
                            JOURNALENTRY.TRANSACTIONCURRENCYID
                        from #TMP_ADJUSTMENTHISTORY_LINEITEMS [SPLITS]
                        inner join dbo.JOURNALENTRY on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = [SPLITS].LI_ID
                        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
                        left join dbo.GLACCOUNT on JOURNALENTRY.GLACCOUNTID = GLACCOUNT.ID;

                drop table #TMP_ADJUSTMENTHISTORY_LINEITEMS;
            end