UFN_ADJUSTMENTHISTORY_GETDISTRIBUTION

            CREATE function dbo.UFN_ADJUSTMENTHISTORY_GETDISTRIBUTION
            (
            )
            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
            )
            as 
            begin
                declare @TEMP 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
                );

                declare @REVENUEID uniqueidentifier;
                declare @ADJUSTMENTHISTORYID uniqueidentifier;

                declare NEEDSGLCURSOR cursor local fast_forward for 
                    select 
                        ADJUSTMENTHISTORYID 
                    from @TEMP
                    where ADJUSTMENTHISTORYID not in (select ADJUSTMENTHISTORYID from @TEMP where TYPECODE = 1);

                --JamesWill 03/11/2008 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.

                declare NEWREVENUE cursor local fast_forward for
                    select
                        ADJUSTMENTHISTORY.ID
                    from dbo.ADJUSTMENTHISTORY
                    left join @TEMP as [R] on [R].ADJUSTMENTHISTORYID = ADJUSTMENTHISTORY.ID
                    where [R].ID is null;

                insert into @TEMP(ID, ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT)
                    select 
                        cast(ID as nvarchar(36)),
                        ADJUSTMENTHISTORYID,
                        TYPECODE,
                        TYPE,
                        TRANSACTIONTYPECODE,
                        TRANSACTIONTYPE,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT
                    from dbo.ADJUSTMENTHISTORYDISTRIBUTION;

                /*for all the adjustmenthistories where the GL distribution hasn't been stored, return the calculated distribution*/
                open NEEDSGLCURSOR;
                    fetch next from NEEDSGLCURSOR into @ADJUSTMENTHISTORYID;
                    while @@FETCH_STATUS = 0
                    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(ID, ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT)
                                select
                                    cast(row_number() over (order by AMOUNT) as nvarchar(36)),
                                    @ADJUSTMENTHISTORYID,
                                    1, 
                                    'Adjustment',
                                    TRANSACTIONTYPECODE,
                                    DEBITCREDIT,
                                    ACCOUNT,
                                    PROJECT,
                                    REFERENCE,
                                    AMOUNT
                                from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);

                        fetch next from NEEDSGLCURSOR into @ADJUSTMENTHISTORYID;
                    end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close NEEDSGLCURSOR;
                deallocate NEEDSGLCURSOR;

                --JamesWill 03/11/2008 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.


                    open NEWREVENUE;
                    fetch next from NEWREVENUE into @ADJUSTMENTHISTORYID;
                    while @@FETCH_STATUS = 0
                    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(ID, ADJUSTMENTHISTORYID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT)
                                select
                                    cast(row_number() over (order by AMOUNT) as nvarchar(36)),
                                    @ADJUSTMENTHISTORYID,
                                    1,
                                    'Adjustment',
                                    TRANSACTIONTYPECODE,
                                    DEBITCREDIT,
                                    ACCOUNT,
                                    PROJECT,
                                    REFERENCE,
                                    AMOUNT
                                from dbo.UFN_REVENUE_GETGLDISTRIBUTION(@REVENUEID);

                        fetch next from NEWREVENUE into @ADJUSTMENTHISTORYID;
                    end

                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close NEWREVENUE;
                deallocate NEWREVENUE;

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

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

                return;
            end