UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION_FORADJUSTMENTHISTORYSTOCKID

            CREATE function dbo.UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION_FORADJUSTMENTHISTORYSTOCKID
            (
                @ADJUSTMENTHISTORYSTOCKID uniqueidentifier
            )
            returns @RESULT table
            (
                ID nvarchar(36),
                ADJUSTMENTHISTORYSTOCKID uniqueidentifier,
                TYPECODE tinyint,
                TYPE nvarchar(20),
                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 @STOCKSALEID uniqueidentifier;

                insert into @RESULT(ID, ADJUSTMENTHISTORYSTOCKID, TYPECODE, TYPE, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        cast(ID as nvarchar(36)),
                        ADJUSTMENTHISTORYSTOCKID,
                        TYPECODE,
                        TYPE,
                        TRANSACTIONTYPE,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION
                    where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID;

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

                declare @NEEDSGL bit;
                set @NEEDSGL = 1;
                select
                    @NEEDSGL = case when TYPECODE = 1 then 0 else @NEEDSGL end
                from @RESULT;

                if @NEEDSGL = 1
                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, TRANSACTIONTYPE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                cast(row_number() over (order by AMOUNT) as nvarchar(36)),
                                @ADJUSTMENTHISTORYSTOCKID,
                                1,
                                'Adjustment',
                                DEBITCREDIT,
                                ACCOUNT,
                                PROJECT,
                                REFERENCE,
                                AMOUNT,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID,
                                TRANSACTIONCURRENCYID
                            from dbo.UFN_REVENUE_GETSTOCKDETAILGLDISTRIBUTION(@STOCKDETAILID)
                            where STOCKSALEID = @STOCKSALEID;
                end            
                return;
            end