USP_DATALIST_CREDITREFUND_ADJUSTMENTS

Lists all adjustments belonging to a refund.

Parameters

Parameter	Parameter Type	Mode	Description
@CREDITID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_CREDITREFUND_ADJUSTMENTS
(
    @CREDITID uniqueidentifier
)
as
    set nocount on;

    -- Refunded Fees

    select
        SALESORDERITEM.SALESORDERID,
        SALESORDERITEM.DESCRIPTION,
        ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as TOTAL,
        SALESORDERITEM.TYPECODE,
        SALESORDERITEM.TYPE
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    inner join
        dbo.SALESORDERITEMFEE on SALESORDERITEM.ID = SALESORDERITEMFEE.ID
    where 
        FT.ID = @CREDITID
        and SALESORDERITEMFEE.APPLIESTOCODE = 0

    -- Refunded Taxes, Discounts Discounts

    union all
    select
        SALESORDERITEM.SALESORDERID,
        SALESORDERITEM.DESCRIPTION,
        ((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS) as TOTAL,
        SALESORDERITEM.TYPECODE,
        SALESORDERITEM.TYPE
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.SALESORDERITEM on SALESORDERITEM.ID = EXT.SALESORDERITEMID
    where 
        FT.ID = @CREDITID
        and SALESORDERITEM.TYPECODE in (4,5)

    order by 
        SALESORDERITEM.TYPECODE asc,
        DESCRIPTION asc

    return 0;