Views [dbo].[vCashReceipts]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:48 AM Monday, May 4, 2015
Last Modified9:12:11 AM Thursday, May 23, 2024
Columns
Name
Branch
Batch
Status
BatchCreateBy
BatchCreateOn
ReceiptNumber
ReceiptCreatedBy
ReceiptCreatedOn
PaymentType
Amount
CheckNumber
CustomerLastName
CustomerFirstName
OrderNumber
Shipper
MovingFrom
MovingTo
ActualLoad
ActualDelivery
PostedDate
PostedBy
JournalDate
VoidedDate
VoidedBy
VoidedNote
StatementHeaderId
StatementDetailId
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
/**
* 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
Uses