UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION

            CREATE function dbo.UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION
            (
            )
            returns @RESULT table
            (
                ID nvarchar(36),
                ADJUSTMENTHISTORYSTOCKID 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 @STOCKSALEID uniqueidentifier;
                declare @ADJUSTMENTHISTORYSTOCKID uniqueidentifier;

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

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

                /*for all the adjustmentstockhistories where the GL distribution hasn't been stored, return the calculated distribution*/
                open NEEDSGLCURSOR;
                    fetch next from NEEDSGLCURSOR into @ADJUSTMENTHISTORYSTOCKID;
                    while @@FETCH_STATUS = 0 
                    begin
                        declare @STOCKDETAILID uniqueidentifier

                        /*this should be the case for undeleted sold-stock so find that sold-stock*/
                        select
                            @STOCKSALEID = STOCKSALEADJUSTMENT.STOCKSALEID,
                            @STOCKDETAILID = STOCKSALE.STOCKDETAILID
                        from dbo.STOCKSALEADJUSTMENT
                        inner join dbo.ADJUSTMENTHISTORYSTOCK on ADJUSTMENTHISTORYSTOCK.STOCKSALEADJUSTMENTID = STOCKSALEADJUSTMENT.ID
                        inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                        where ADJUSTMENTHISTORYSTOCK.ID = @ADJUSTMENTHISTORYSTOCKID

                        if not @STOCKSALEID is null /*if we couldn't find the stock detail, there's no sense in finding its GL info*/
                            insert into @RESULT(ID, ADJUSTMENTHISTORYSTOCKID, TYPECODE, TYPE, TRANSACTIONTYPECODE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT)
                                select
                                    cast(row_number() over (order by AMOUNT) as nvarchar(36)),
                                    @ADJUSTMENTHISTORYSTOCKID,
                                    1,
                                    'Adjustment',
                                    TRANSACTIONTYPECODE,
                                    DEBITCREDIT,
                                    ACCOUNT,
                                    PROJECT,
                                    REFERENCE,
                                    AMOUNT
                                from dbo.UFN_REVENUE_GETSTOCKDETAILGLDISTRIBUTION(@STOCKDETAILID)
                                where STOCKSALEID = @STOCKSALEID;

                        fetch next from NEEDSGLCURSOR into @ADJUSTMENTHISTORYSTOCKID;
                    end
                --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

                close NEEDSGLCURSOR;
                deallocate NEEDSGLCURSOR;

                return;
            end