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

create view [dbo].[vCashReceipts]
as

select
    Branch = b.BranchID,
    Batch = sh.StatementDesc,
    Status = sh.Status,
    BatchCreateBy = bcb.FIRSTNAME + ' ' + bcb.LASTNAME,
    BatchCreateOn = sh.DateEntered,
    ReceiptNumber = sd.CRNumber,
    ReceiptCreatedBy = rcb.FIRSTNAME + ' ' + rcb.LASTNAME,
    ReceiptCreatedOn = sd.DateTimeCreated,
    PaymentType = pt.Description,
    Amount = sd.Amount,
    CheckNumber = sd.CheckConf,
    CustomerLastName = sdc.LastName,
    CustomerFirstName = sdc.FirstName,
    OrderNumber = o.OrderNo,
    Shipper = o.LastName + ', ' + o.FirstName,
    MovingFrom = oao.City + ', ' + oao.State,
    MovingTo = oad.City + ', ' + oad.State,
    ActualLoad = o.ActLoadDate,
    ActualDelivery = o.ActDelDate,
    PostedDate = sh.DatePosted,
    PostedBy = pb.FIRSTNAME + ' ' + pb.LASTNAME,
    JournalDate = sh.JournalDate,
    VoidedDate = sd.VoidedDate,
    VoidedBy = vb.FIRSTNAME + ' ' + vb.LASTNAME,
    VoidedNote = anv.NoteText,
    StatementHeaderId = sh.SHPriKey,
    StatementDetailId = sd.SDPriKey
from    StatementHeader sh
join    Branch b on sh.BranchPriKey = b.BranchPriKey
left join StatementDetail sd on sh.SHPriKey = sd.SHPriKey
left join StatementDetailDist sdd on sd.SDPriKey = sdd.SDPriKey
left join Sysuser bcb on sh.EnteredBy = bcb.SysUserID
left join Sysuser rcb on sd.CreatedBy = rcb.SysUserID
left join PaymentType pt on sd.PTPriKey = pt.PTPriKey
left join StatementDetailContact sdc on sd.SDPriKey = sdc.StatementDetailFID
left join Orders o on sdd.OrdPriKey = o.PriKey
left join OrderAddress oao on o.PriKey = oao.OrderFID and oao.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Origin')
left join OrderAddress oad on o.PriKey = oad.OrderFID and oad.AddressTypeFID = (select at.AddressTypeID from AddressType at where at.TypeName = 'Destination')
left join Sysuser pb on sh.PostedBy = pb.SysUserID
left join Sysuser vb on sd.VoidedBy = vb.SysUserID
left join AccountingNote anv on sd.VoidedNote = anv.ANPriKey
where sh.IsCRBatch = 1
GO
GRANT SELECT ON  [dbo].[vCashReceipts] TO [MssExec]
GRANT INSERT ON  [dbo].[vCashReceipts] TO [MssExec]
GRANT DELETE ON  [dbo].[vCashReceipts] TO [MssExec]
GRANT UPDATE ON  [dbo].[vCashReceipts] TO [MssExec]
GO