USP_DATAFORMTEMPLATE_VIEW_CREDITORDERVIEW

The load procedure used by the view dataform template "Credit Order View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@ITEMS	xml	INOUT	ITEMS
@TOTAL	money	INOUT	Order total
@PAYMENTS	xml	INOUT	PAYMENTS
@TAXES	xml	INOUT	TAXES
@TAXESBYITEM	xml	INOUT	TAXESBYITEM
@ORDERDISCOUNTS	money	INOUT	Discounts
@OTHERPAYMENTMETHODS	xml	INOUT	OTHERPAYMENTMETHODS
@TRANSACTIONID	uniqueidentifier	INOUT	TRANSACTIONID
@TIMESTAMP	bigint	INOUT	TIMESTAMP
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(154)	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITORDERVIEW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ITEMS xml = null output,
    @TOTAL money = null output,
    @PAYMENTS xml = null output,
    @TAXES xml = null output,
    @TAXESBYITEM xml = null output,
    @ORDERDISCOUNTS money = null output,
    @OTHERPAYMENTMETHODS xml = null output,
    @TRANSACTIONID uniqueidentifier = null output,
    @TIMESTAMP bigint = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(154) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    --Error if it's a group sales order
    if exists (select * from dbo.RESERVATION where [ID] = @ID) begin
        raiserror('The selected order is a group sales order.  Group sales orders may not be refunded.', 13, 1);
        return 1;
    end

    select 
        @ITEMS = dbo.UFN_CREDIT_GETSALESORDERITEMS_TOITEMLISTXML(@ID),
        @PAYMENTS = dbo.UFN_CREDIT_GETORDERPAYMENTS_2_TOITEMLISTXML(@ID),
        @TAXES = dbo.UFN_CREDIT_GETSALESORDERTAXITEMS_TOITEMLISTXML(@ID),
        @TAXESBYITEM = dbo.UFN_CREDIT_GETTAXES_TOITEMLISTXML(@ID),
        @TIMESTAMP = [TSLONG],
        @CONSTITUENTID = SALESORDER.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(SALESORDER.CONSTITUENTID)
    from dbo.SALESORDER
    where ID = @ID

    select
        @ORDERDISCOUNTS = isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0)
    from
        dbo.[SALESORDERITEM]
    left join
        dbo.CREDITITEM_EXT as EXT on EXT.[SALESORDERITEMID] = [SALESORDERITEM].[ID]
    inner join
        dbo.[SALESORDERITEMORDERDISCOUNTDETAIL] on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
    where
        [SALESORDERITEM].[SALESORDERID] = @ID
        and EXT.[ID] is null

    select
        @TOTAL = sum(T.item.value('(AMOUNTPAID)[1]','decimal(10,4)'))
    from @PAYMENTS.nodes('/PAYMENTS/ITEM') T(item);

    select
        @OTHERPAYMENTMETHODS = 
            (select 
                [ID], 
                [DESCRIPTION] 
            from dbo.[OTHERPAYMENTMETHODCODE]
        for xml raw ('ITEM'), type, elements, root('OTHERPAYMENTMETHODS'), BINARY BASE64)

    set @TRANSACTIONID = @ID;

    set @DATALOADED = 1;

    --Error if there are no items to return on this order
    if (select count(*) from @ITEMS.nodes('/ITEMS/ITEM') T(item)) < 1 begin
        raiserror('The selected order has no items to refund.', 13, 1);
        return 1;
    end

    return 0;