USP_DATAFORMTEMPLATE_VIEW_ORDERPAYMENTREVENUEDETAIL

The load procedure used by the view dataform template "Order Payment Revenue Detail View Form"

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.
@TICKETAMOUNT	money	INOUT	Tickets
@FEEAMOUNT	money	INOUT	Fees
@TAXAMOUNT	money	INOUT	Taxes
@MEMBERSHIPAMOUNT	money	INOUT	Memberships
@GIFTAMOUNT	money	INOUT	Donations
@APPLIEDID	uniqueidentifier	INOUT	Applied ID
@APPLIEDDATE	datetime	INOUT	Applied date
@APPLIEDTYPE	nvarchar(100)	INOUT	Applied type
@APPLIEDNAME	nvarchar(700)	INOUT	Applied to
@REGISTRATIONAMOUNT	money	INOUT	Event registrations
@MERCHANDISEAMOUNT	money	INOUT	Merchandise
@STATUSCODE	tinyint	INOUT	STATUSCODE
@SALESMETHODTYPECODE	tinyint	INOUT	SALESMETHODTYPECODE
@RESOURCEAMOUNT	money	INOUT
@FACILITYAMOUNT	money	INOUT
@LIABILITYAMOUNT	money	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ORDERPAYMENTREVENUEDETAIL
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TICKETAMOUNT money = null output,
  @FEEAMOUNT money = null output,
  @TAXAMOUNT money = null output,
  @MEMBERSHIPAMOUNT money = null output,
  @GIFTAMOUNT money = null output,
  @APPLIEDID uniqueidentifier = null output,
  @APPLIEDDATE datetime = null output,
  @APPLIEDTYPE nvarchar(100) = null output,
  @APPLIEDNAME nvarchar(700) = null output,
  @REGISTRATIONAMOUNT money = null output,
  @MERCHANDISEAMOUNT money = null output,
  @STATUSCODE tinyint = null output,
  @SALESMETHODTYPECODE tinyint = null output,
  @RESOURCEAMOUNT money = null output,
  @FACILITYAMOUNT money = null output,
  @LIABILITYAMOUNT money = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    select 
        @DATALOADED = 1, 
        @TICKETAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 5 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @FEEAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 6 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @TAXAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 7 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @MEMBERSHIPAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 2 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @GIFTAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 0 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @REGISTRATIONAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 1 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @MERCHANDISEAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 16 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @RESOURCEAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 10 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT 
                                when 11 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @FACILITYAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 14 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end),
        @LIABILITYAMOUNT = sum(case REVENUESPLIT_EXT.TYPECODE when 19 then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT else 0 end)
    from 
        dbo.FINANCIALTRANSACTIONLINEITEM
    inner join 
        dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    where 
        FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1  -- Reversal

    select 
        top 1 @APPLIEDID = SALESORDER.ID,
        @APPLIEDDATE  = SALESORDER.TRANSACTIONDATE,
        @APPLIEDTYPE = 'Order',
        @APPLIEDNAME = cast(SALESORDER.SEQUENCEID as nvarchar(20)),
        @STATUSCODE = SALESORDER.STATUSCODE,
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
    from 
        dbo.SALESORDERPAYMENT 
    inner join 
        dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
    where 
        SALESORDERPAYMENT.PAYMENTID = @ID;

    return 0;