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

CREATE view [dbo].[vClaimsGrid]
as

select
    ClaimId = c.ClaimID,
    OrderId = o.PriKey,
    ClaimBranch = cb.BranchID,
    ClaimDivision = cd.Description,
    OrderBranch = ob.BranchID,
    ClaimNumber = c.ClaimNumber,
    LastName = o.LastName,
    FirstName = o.FirstName,
    VL = vl.VanLineCode,
    OrderNumber = o.OrderNo,
    Adjuster = adj.FIRSTNAME + ' ' + adj.LASTNAME,
    ClaimStatus = cs.Status,
    ClaimType = ct.Type,
    MultiLossAlertNumber = c.MultiLossAlertNumber,
    DateCloased = c.ClosedDate,
    DaysOpen = datediff(d, c.CreatedDate, isnull(c.ClosedDate, getdate())),
    DateCreated = c.CreatedDate,
    CreatedBy = cby.FIRSTNAME + ' ' + cby.LASTNAME,
    Valuation = v.Name,
    DeclaredValue = o.ValuationAmount,
    ShipperDeductible = o.ShipperDeductible,
    InsuranceProvider = cip.Provider,
    InsuranceDeductible = cip.Deductible,
    AlertReceived = o.AlertReceivedDate,
    NationalAccountNumber = a.AcctNo,
    NationalAccountName = a.AcctName,
    OrderStatus = o.OrderStatus,
    WorkNumber = '(' + cpnw.AreaCode + ') ' + cpnw.LocalNumber,
    HomeNumber = '(' + cpnh.AreaCode + ') ' + cpnh.LocalNumber,
    CellNumber = '(' + cpnc.AreaCode + ') ' + cpnc.LocalNumber,
    FaxNumber = '(' + cpnf.AreaCode + ') ' + cpnf.LocalNumber,
    Email1 = cea1.EmailAddress,
    Email2 = cea2.EmailAddress,
    ClaimReassignedTo = c.ForwardTo,
    ClaimReassignedOn = c.ForwardDate,
    ClaimAmount = c.TotalAmount,
    CustomerNumber = o.CustomerNumber,
    SettlementAmount = isnull(vcsa.SummarySettlementAmount,0),
    ChargebacksAmount = isnull(vcsa.SummaryVendorChargebackAmount,0) + isnull(vcsa.SummaryBranchChargebackAmount,0),
    ReceivablesAmount = isnull(vcsa.SummaryReveivableAmount,0),
    HandlingRevenue = isnull(vcsa.SummaryHandlingRevenueAmount,0),
    HandlingExpenses = isnull(vcsa.SummaryHandlingExpensesAmount,0)
from    Orders o
join    Claim c on o.PriKey = c.OrdersFID
left join Branch cb on c.BranchFID = cb.BranchPriKey
left join Division cd on c.DivisionFID = cd.DivisionID
left join Branch ob on o.BranchPriKey = ob.BranchPriKey
left join VanLine vl on o.VanLineFID = vl.VLPriKey
left join Sysuser adj on c.AdjusterSysUserFID = adj.SysUserID
left join ClaimStatus cs on c.ClaimStatusFID = cs.ClaimStatusID
left join ClaimType ct on c.ClaimTypeFID = ct.ClaimTypeID
left join Sysuser cby on c.CreatedBySysUserFID = cby.SysUserID
left join Valuation v on o.ValuationFID = v.ValuationID
left join ClaimInsuranceProvider cip on c.ClaimInsuranceProviderFID = cip.ClaimInsuranceProviderID
left join Accounts a on o.AcctPriKey = a.AccountPriKey
left join ClaimPhoneNumber cpnw on c.ClaimID = cpnw.ClaimFID and cpnw.ClaimPhoneTypeFID = (select top 1 cpt.ClaimPhoneTypeID from ClaimPhoneType cpt where cpt.TypeName like '%work%' order by cpt.ClaimPhoneTypeID )
left join ClaimPhoneNumber cpnh on c.ClaimID = cpnh.ClaimFID and cpnh.ClaimPhoneTypeFID = (select top 1 cpt.ClaimPhoneTypeID from ClaimPhoneType cpt where cpt.TypeName like '%home%' order by cpt.ClaimPhoneTypeID )
left join ClaimPhoneNumber cpnc on c.ClaimID = cpnc.ClaimFID and cpnc.ClaimPhoneTypeFID = (select top 1 cpt.ClaimPhoneTypeID from ClaimPhoneType cpt where cpt.TypeName like '%cell%' order by cpt.ClaimPhoneTypeID )
left join ClaimPhoneNumber cpnf on c.ClaimID = cpnf.ClaimFID and cpnf.ClaimPhoneTypeFID = (select top 1 cpt.ClaimPhoneTypeID from ClaimPhoneType cpt where cpt.TypeName like '%fax%' order by cpt.ClaimPhoneTypeID )
left join ClaimEmailAddress cea1 on c.ClaimID = cea1.ClaimFID and cea1.EmailAddressTypeFID = (select eat.EmailAddressTypeID from EmailAddressType eat where eat.TypeName = 'email 1')
left join ClaimEmailAddress cea2 on c.ClaimID = cea2.ClaimFID and cea2.EmailAddressTypeFID = (select eat.EmailAddressTypeID from EmailAddressType eat where eat.TypeName = 'email 2')
left join vClaimsSummaryAmounts vcsa on c.ClaimID = vcsa.ClaimId
GO
GRANT SELECT ON  [dbo].[vClaimsGrid] TO [MssExec]
GRANT INSERT ON  [dbo].[vClaimsGrid] TO [MssExec]
GRANT DELETE ON  [dbo].[vClaimsGrid] TO [MssExec]
GRANT UPDATE ON  [dbo].[vClaimsGrid] TO [MssExec]
GO