UFN_ADJUSTMENTHISTORY_GETDISTRIBUTION_FORADJUSTMENTHISTORYID

            CREATE function dbo.UFN_ADJUSTMENTHISTORY_GETDISTRIBUTION_FORADJUSTMENTHISTORYID
            (
                @ADJUSTMENTHISTORYID uniqueidentifier
            )
            returns @RESULT table
            (
                ID nvarchar(36),
                ADJUSTMENTHISTORYID uniqueidentifier,
                TYPECODE tinyint,
                TYPE nvarchar(20),
                TRANSACTIONTYPECODE tinyint,
                TRANSACTIONTYPE nvarchar(50),
                ACCOUNT nvarchar(100),
                PROJECT nvarchar(100),
                REFERENCE nvarchar(255),
                AMOUNT money,
                TRANSACTIONAMOUNT money,
                ORGANIZATIONAMOUNT money,
                BASECURRENCYID uniqueidentifier,
                TRANSACTIONCURRENCYID uniqueidentifier
            )
            as
            begin
                declare @TEMP table
                (
                    ADJUSTMENTHISTORYID uniqueidentifier,
                    TYPECODE tinyint,
                    TYPE nvarchar(20),
                    TRANSACTIONTYPECODE tinyint,
                    TRANSACTIONTYPE nvarchar(50),
                    ACCOUNT nvarchar(100),
                    PROJECT nvarchar(100),
                    REFERENCE nvarchar(255),
                    AMOUNT money,
                    TRANSACTIONAMOUNT money,
                    ORGANIZATIONAMOUNT money,
                    BASECURRENCYID uniqueidentifier,
                    TRANSACTIONCURRENCYID uniqueidentifier
                );

                declare @REVENUEID uniqueidentifier;

                --Need to account for revenue added by the "Add revenue to posted" button. These will not have a distribution saved until after they are posted.

                declare @NEEDSGL bit; 
                declare @NEWREVENUE bit;

                insert into @TEMP(ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select 
                        ADJUSTMENTHISTORYID,
                        TYPECODE,
                        TYPE,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.ADJUSTMENTHISTORYDISTRIBUTION
                    where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID;

                set @NEEDSGL = 1;
                set @NEWREVENUE = 0;

                --If there's only a reversal here, and not an adjustment as well, then we need to include the current GL for this item.

                select
                    @NEEDSGL = case when TYPECODE = 1 then 0 else @NEEDSGL end
                from @TEMP;

                --If there's no ADJUSTMENTHISTORYDISTRIBUTION at all for this adjustment history record, it must be new revenue.

                if (select count(*) from @TEMP) = 0 
                    set @NEWREVENUE = 1;

                if @NEEDSGL = 1
                begin
                    set @REVENUEID = null;
                    /*this should only be the case for undeleted revenue, so find that revenue*/
                    select 
                        @REVENUEID = ADJUSTMENT.REVENUEID 
                    from dbo.ADJUSTMENT
                    inner join dbo.ADJUSTMENTHISTORY on ADJUSTMENTHISTORY.ADJUSTMENTID = ADJUSTMENT.ID
                    where ADJUSTMENTHISTORY.ID = @ADJUSTMENTHISTORYID;

                    if not @REVENUEID is null /*if we couldn't find the revenue, there's no sense in finding its GL info*/
                        insert into @TEMP(ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @ADJUSTMENTHISTORYID,
                                1, 
                                'Adjustment',
                                TRANSACTIONTYPECODE,
                                DEBITCREDIT,
                                ACCOUNT,
                                PROJECT,
                                REFERENCE,
                                AMOUNT,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID,
                                TRANSACTIONCURRENCYID
                            from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);
                end


                --Need to account for revenues added by the "Add revenue to posted" button. These will not have a distribution saved until after they are posted.

                if @NEWREVENUE = 1
                begin
                    set @REVENUEID = null

                    select 
                        @REVENUEID = ADJUSTMENT.REVENUEID
                    from dbo.ADJUSTMENT
                    inner join dbo.ADJUSTMENTHISTORY on ADJUSTMENTHISTORY.ADJUSTMENTID = ADJUSTMENT.ID
                    where ADJUSTMENTHISTORY.ID = @ADJUSTMENTHISTORYID;

                    if not @REVENUEID is null
                        insert into @TEMP( ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @ADJUSTMENTHISTORYID,
                                1,
                                'Adjustment',
                                TRANSACTIONTYPECODE,
                                DEBITCREDIT,
                                ACCOUNT,
                                PROJECT,
                                REFERENCE,
                                AMOUNT,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID,
                                TRANSACTIONCURRENCYID
                            from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);

                end

                --Make sure every row in @RESULT has a unique ID

                insert into @RESULT(ID, ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        cast(row_number() over (order by AMOUNT) as nvarchar(36)),
                        ADJUSTMENTHISTORYID,
                        TYPECODE,
                        TYPE,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from @TEMP

                return;
            end