USP_REVENUEDETAIL_LOAD

Stored procedure to load a payment.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@TYPECODE	tinyint	INOUT
@TYPE	nvarchar(50)	INOUT
@MAXSOFTCREDITAMOUNT	money	INOUT
@MAXSOLICITORAMOUNT	money	INOUT
@AMOUNT	money	INOUT
@TRANSACTIONAMOUNT	money	INOUT
@RECEIPTAMOUNT	money	INOUT
@SPLITS	xml	INOUT
@SOURCECODE	nvarchar(50)	INOUT
@APPEALID	uniqueidentifier	INOUT
@BENEFITS	xml	INOUT
@BENEFITSWAIVED	bit	INOUT
@MAILINGID	uniqueidentifier	INOUT
@CHANNELCODEID	uniqueidentifier	INOUT
@DONOTACKNOWLEDGE	bit	INOUT
@TSLONG	bigint	INOUT
@CATEGORYCODEID	uniqueidentifier	INOUT

Definition

Copy



            CREATE procedure dbo.USP_REVENUEDETAIL_LOAD
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @CONSTITUENTID uniqueidentifier = null output,    
                @TYPECODE tinyint = null output,
                @TYPE nvarchar(50) = null output,
                @MAXSOFTCREDITAMOUNT money = null output,
                @MAXSOLICITORAMOUNT money = null output,
                @AMOUNT money = null output,                    
                @TRANSACTIONAMOUNT money = null output,
                @RECEIPTAMOUNT money = null output,
                @SPLITS xml = null output,
                @SOURCECODE nvarchar(50) = null output,
                @APPEALID uniqueidentifier = null output, 
                @BENEFITS xml = null output,
                @BENEFITSWAIVED bit = null output,
                @MAILINGID uniqueidentifier = null output,
                @CHANNELCODEID uniqueidentifier = null output,
                @DONOTACKNOWLEDGE bit = null output,
                @TSLONG bigint = 0 output,
                @CATEGORYCODEID uniqueidentifier = null output
            )
            as
            set nocount on;

            declare @TRANSACTIONID uniqueidentifier;

            set @DATALOADED = 0;
            set @TSLONG = 0;

            select
                @DATALOADED = 1,
                @TRANSACTIONID = FINANCIALTRANSACTION.ID,
                @CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
                @TYPECODE = FINANCIALTRANSACTION.TYPECODE,
                @TYPE = FINANCIALTRANSACTION.TYPE,
                @MAXSOFTCREDITAMOUNT = 0,
                @MAXSOLICITORAMOUNT = coalesce((select max(REVENUESOLICITOR.AMOUNT) from dbo.REVENUESOLICITOR inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUESOLICITOR.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID), 0),
                @AMOUNT = FINANCIALTRANSACTION.BASEAMOUNT,
                @TRANSACTIONAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                @SPLITS = dbo.UFN_REVENUE_GETSPLITS_TOITEMLISTXML(FINANCIALTRANSACTION.ID),
                @RECEIPTAMOUNT = REVENUE_EXT.RECEIPTAMOUNT,
                @SOURCECODE = REVENUE_EXT.SOURCECODE,
                @APPEALID = REVENUE_EXT.APPEALID,
                @MAILINGID = REVENUE_EXT.MAILINGID,
                @CHANNELCODEID = REVENUE_EXT.CHANNELCODEID,
                @BENEFITSWAIVED = REVENUE_EXT.BENEFITSWAIVED,
                @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
                @TSLONG = FINANCIALTRANSACTION.TSLONG,
                @CATEGORYCODEID = (select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.FINANCIALTRANSACTIONLINEITEM on REVENUECATEGORY.ID = FINANCIALTRANSACTIONLINEITEM.ID where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID)
            from dbo.FINANCIALTRANSACTION
            inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
            left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FINANCIALTRANSACTION.ID
            where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.TYPECODE = 0;

            -- Load benefits

            set @BENEFITS = dbo.UFN_REVENUE_GETBENEFITS_TOITEMLISTXML(@ID);

            -- Load splits

            set @SPLITS = dbo.UFN_REVENUE_GETSPLITS_TOITEMLISTXML(@ID);

            return 0;