USP_REVENUE_GETBENEFITSSPLIT

Returns splits revenue benefits into to xml fields.
Parameters

Parameter	Parameter Type	Mode
@REVENUEID	uniqueidentifier	IN
@BENEFITS	xml	INOUT
@PERCENTAGEBENEFITS	xml	INOUT
Definition

Copy


            CREATE procedure dbo.USP_REVENUE_GETBENEFITSSPLIT
            (
                @REVENUEID uniqueidentifier,
                @BENEFITS xml = null output,
                @PERCENTAGEBENEFITS xml = null output
            )
            as
                set nocount on

                --Only get benefit expenses. Liabilities will be auto-calculated


                set @BENEFITS = 
                    (select 
                        REVENUEBENEFIT.[ID], 
                        REVENUEBENEFIT.[BENEFITID], 
                        REVENUEBENEFIT.[QUANTITY], 
                        REVENUEBENEFIT.[UNITVALUE], 
                        REVENUEBENEFIT.[TOTALVALUE],
                        REVENUEBENEFIT.[DETAILS], 
                        REVENUEBENEFIT.[SEQUENCE],
                        REVENUEBENEFIT.[PERCENTAPPLICABLEAMOUNT],
                        REVENUEBENEFIT.[VALUEPERCENT],
                        [BENEFIT].[USEPERCENT],
                        REVENUEBENEFIT.[BASECURRENCYID],
                        REVENUEBENEFIT.[TRANSACTIONTOTALVALUE],
                        REVENUEBENEFIT.[REVENUESPLITID]
                    from 
                        dbo.REVENUEBENEFIT
                        inner join dbo.BENEFIT on REVENUEBENEFIT.BENEFITID = BENEFIT.ID
                        inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = REVENUEBENEFIT.ID
                    where 
                        REVENUEID = @REVENUEID 
                        and [BENEFIT].[USEPERCENT] = 0
                        and RBX.BENEFITTYPECODE = 1
                    order by 
                        SEQUENCE, ID
                    for xml raw('ITEM'),type,elements,root('BENEFITS'),BINARY BASE64);

                set @PERCENTAGEBENEFITS = 
                    (select 
                        REVENUEBENEFIT.[ID], 
                        REVENUEBENEFIT.[BENEFITID], 
                        REVENUEBENEFIT.[QUANTITY], 
                        REVENUEBENEFIT.[UNITVALUE], 
                        REVENUEBENEFIT.[TOTALVALUE],
                        REVENUEBENEFIT.[DETAILS], 
                        REVENUEBENEFIT.[SEQUENCE],
                        REVENUEBENEFIT.[PERCENTAPPLICABLEAMOUNT],
                        REVENUEBENEFIT.[VALUEPERCENT],
                        [BENEFIT].[USEPERCENT],
                        REVENUEBENEFIT.BASECURRENCYID,
                        REVENUEBENEFIT.[TRANSACTIONTOTALVALUE],
                        REVENUEBENEFIT.[REVENUESPLITID]
                    from 
                        dbo.REVENUEBENEFIT
                        inner join dbo.BENEFIT on REVENUEBENEFIT.BENEFITID = BENEFIT.ID
                        inner join dbo.REVENUEBENEFIT_EXT RBX on RBX.ID = REVENUEBENEFIT.ID
                    where 
                        REVENUEID = @REVENUEID and [BENEFIT].[USEPERCENT] = 1
                        and RBX.BENEFITTYPECODE = 1
                    order by 
                        SEQUENCE, ID
                    for xml raw('ITEM'),type,elements,root('PERCENTAGEBENEFITS'),BINARY BASE64);