/*
* Returns Total Company Reveneue.
* This calculation is same as in spRevenueSummaryAmounts
* @inOrderID: Accepts Order ID as Input
*/
CREATE FUNCTION [dbo].[CalculateCompanyRevenue]
(
    @inOrderID int
)
RETURNS money
AS
begin

declare @TotalBMinRevenue money
declare @TotalBMinDistRevenue money

    /* Sum BillingMinorItem revenue. */
    select @TotalBMinRevenue = sum(isnull(BMin.Amount,0))
    from BillingMajorItem bmaj
    inner join BillingMinorItem bmin on (bmin.BMajPriKey = bmaj.BMajPriKey)
    where (bmaj.OrdPriKey = @inOrderID) and
        (bmin.BMinPriKey not in (select BMinPriKey from BillingMinorDist))

    /* Sum BillingMinorDist revenue. */
    select @TotalBMinDistRevenue = sum(isnull(bmindist.Amount,0))
    from BillingMajorItem bmaj
    inner join BillingMinorItem bmin on (bmin.BMajPriKey = bmaj.BMajPriKey)
    inner join BillingMinorDist bmindist on (bmindist.BMinPriKey = bmin.BMinPriKey)
    where (bmaj.OrdPriKey = @inOrderID)

return isnull(@TotalBMinRevenue,0) + isnull(@TotalBMinDistRevenue,0);

end
GO
GRANT EXECUTE ON  [dbo].[CalculateCompanyRevenue] TO [MssExec]
GO