/**
* This view is for ProServ, used in custom reports. Don't rename the view without confirmation from ProServ.
*/

create view [dbo].[vRetainedRevenue]
as

select    OrderId = o.PriKey,
    OrderNumber = o.OrderNo,
    CompanyRevenue = isnull((
        select    sum(bmn.Amount)                    --Billing Minor Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    Branch b
            on    bmn.BranchFID = b.BranchPriKey
        where    bmj.Ordprikey = o.PriKey and
            bmn.BMinPriKey not in (select BMinPriKey from BillingMinorDist) and
            bmn.DocumentNumber is not null), 0) + isnull((
        select    sum(bmd.Amount)                    --Billing Minor Distribution Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    BillingMinorDist bmd
            on    bmn.BMinPriKey = bmd.BMinPriKey
        join    Branch b
            on    bmn.BranchFID = b.BranchPriKey
        where    bmj.OrdPriKey = o.PriKey and
            bmd.DocumentNumber is not null), 0),
    TotalCommissionPaid = isnull((
        select    sum(cd.CommissionAfterSplit)    --Commissions from Billing Minor Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    CommissionedDetail cd
            on    bmn.BMinPriKey = cd.BMinPriKey
        where    bmj.OrdPrikey = o.PriKey and
            cd.DocumentNumber is not null), 0) + isnull((
        select    sum(cd.CommissionAfterSplit)    --Commissions from Billing Minor Distribution Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    BillingMinorDist bmd
            on    bmn.BMinPriKey = bmd.BMinPriKey
        join    CommissionedDetail cd
            on    bmd.BMinDistPriKey = cd.BminDistPriKey
        where    bmj.OrdPrikey = o.PriKey and
            cd.DocumentNumber is not null), 0) + isnull((
        select    sum(sc.Amount)                    --Split Commissions from Billing Minor Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    CommissionedDetail cd
            on    bmn.BMinPriKey = cd.BMinPriKey
        join    SplitCommissions sc
            on    cd.CDPriKey = sc.CDPriKey
        where    bmj.OrdPrikey = o.PriKey and
            sc.DocumentNumber is not null), 0) + isnull((
        select    sum(sc.Amount)                    --Split Commissions from Billing Minor Distribution Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    BillingMinorDist bmd
            on    bmn.BMinPriKey = bmd.BMinPriKey
        join    CommissionedDetail cd
            on    bmd.BMinDistPriKey = cd.BminDistPriKey
        join    SplitCommissions sc
            on    cd.CDPriKey = sc.CDPriKey
        where    bmj.OrdPrikey = o.PriKey and
            sc.DocumentNumber is not null),0),
    ThirdPartyExpenses = isnull((
        select    sum(tp.PayableAmount)            --Third Party from Billing Minor Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    ThirdParty tp
            on    bmn.BMinPrikey = tp.BMinPriKey
        where    bmj.OrdPriKey = o.PriKey and
            tp.DocumentNumber is not null), 0) + isnull((
        select    sum(tp.PayableAmount)            --Third Party from Billing Minor Distribution Items **********
        from    BillingMajorItem bmj
        join    BillingMinorItem bmn
            on    bmj.BMajPriKey = bmn.BMajPriKey
        join    BillingMinorDist bmd
            on    bmn.BMinPriKey = bmd.BMinPriKey
        join    ThirdParty tp
            on    bmd.BMinDistPriKey = tp.BMinDistPriKey
        where    bmj.OrdPriKey = o.PriKey and
            tp.DocumentNumber is not null), 0)
from Orders o
where o.OrderNo is not null
GO
GRANT SELECT ON  [dbo].[vRetainedRevenue] TO [MssExec]
GRANT INSERT ON  [dbo].[vRetainedRevenue] TO [MssExec]
GRANT DELETE ON  [dbo].[vRetainedRevenue] TO [MssExec]
GRANT UPDATE ON  [dbo].[vRetainedRevenue] TO [MssExec]
GO