UFN_CREDIT_GETTAXES

CREATE function dbo.UFN_CREDIT_GETTAXES
(
    @SALESORDERID uniqueidentifier
) returns @TAXES table
(
    ITEMID uniqueidentifier,
    TAXID uniqueidentifier,
    TAXABLEAMOUNT money,
    REFUNDQUANTITY integer,
    MAXQUANTITY integer
)
begin
    declare @ORDERDISCOUNTTOTAL money;
    select @ORDERDISCOUNTTOTAL = coalesce(sum([SALESORDERITEM].[TOTAL]),0)
    from dbo.[SALESORDERITEM]
    where
        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
        [SALESORDERITEM].[TYPECODE] = 5;  -- Discount


    --Total item amount that is discountable

    declare @TOTALDISCOUNTABLE money;
    select @TOTALDISCOUNTABLE = coalesce(sum([SALESORDERITEM].[TOTAL]),0)
    from dbo.[SALESORDERITEM]
    where 
        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID and
        [SALESORDERITEM].[TYPECODE] = 0;  -- Ticket


    declare @CALCULATEDFIELDS table (
        [SALESORDERITEMID] uniqueidentifier,
        [ITEMDISCOUNTS] money,
        [ORDERDISCOUNTS] money,
        [REFUNDS] money,
        [QUANTITY] integer
    );

    insert into @CALCULATEDFIELDS ([SALESORDERITEMID],[ITEMDISCOUNTS],[ORDERDISCOUNTS],[REFUNDS],[QUANTITY])
    select V1.ID, sum(ITEMDISCOUNTS), sum(ORDERDISCOUNTS), sum(REFUNDS), max(QUANTITY)
    from (
        select 
            [SALESORDERITEM].[ID],
            (
                select coalesce(sum([AMOUNT]),0.0)
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                where [SALESORDERITEMID] = [SALESORDERITEM].[ID]
            ) as [ITEMDISCOUNTS],
            coalesce([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT], 0) as ORDERDISCOUNTS,
            (
                select coalesce(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS),0.0)
                from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
                where EXT.SALESORDERITEMID = [SALESORDERITEM].[ID]
            ) as [REFUNDS],
            case SALESORDERITEMFEE.APPLIESTOCODE
                when 1 then (  -- Item

                    select SOI.QUANTITY - isnull(
                        (
                            select sum(LI.QUANTITY)
                            from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                            inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
                            where EXT.SALESORDERITEMID = SOI.ID
                        ), 0)
                    from dbo.SALESORDERITEM as SOI
                    where SOI.ID = SALESORDERITEMFEE.SALESORDERITEMID
                )
                else
                    [SALESORDERITEM].[QUANTITY] - coalesce(
                        (
                            select sum(LI.QUANTITY)
                            from dbo.FINANCIALTRANSACTIONLINEITEM as LI
                            inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
                            where EXT.SALESORDERITEMID = SALESORDERITEM.ID
                        ), 0)
            end as [QUANTITY]
        from [SALESORDERITEM]
        left join [SALESORDERITEMORDERDISCOUNTDETAIL] on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        left outer join dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.ID = SALESORDERITEM.ID
        where [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
    ) V1
    group by ID;

    insert into @TAXES (ITEMID,TAXID,TAXABLEAMOUNT,REFUNDQUANTITY,MAXQUANTITY)
    select 
        case
            when [SALESORDERITEM].[TYPECODE] = 3 and [SALESORDERITEMFEE].[APPLIESTOCODE] = 1 then
                [SALESORDERITEMFEE].[ID]
            else
                [SALESORDERITEMTAX].[SALESORDERITEMID]
        end [SALESORDERITEMID], 
        [TAXITEMID], 
        [SALESORDERITEM].[TOTAL] - [CALCULATEDFIELDS].[ITEMDISCOUNTS] - [CALCULATEDFIELDS].[REFUNDS] - [CALCULATEDFIELDS].[ORDERDISCOUNTS],
        0,
        [CALCULATEDFIELDS].[QUANTITY]
    from dbo.[SALESORDERITEMTAX]
    inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMTAX].[SALESORDERITEMID]
    inner join dbo.[TAX] on [SALESORDERITEMTAX].[TAXID] = [TAX].[ID]
    left join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
    left join @CALCULATEDFIELDS as [CALCULATEDFIELDS] on [CALCULATEDFIELDS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
    left join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    where [SALESORDERID] = @SALESORDERID
        and TAXITEMID is not null;

    return;
end