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

create view [dbo].[vPaymentManagement]
as

select
    Branch = b.BranchID,
    Description = sh.StatementDesc,
    Status = sh.Status,
    CreatedDate = sh.DateEntered,
    PostedDate = sh.DatePosted,
    DepositDate = sh.JournalDate,
    ControlAmount = sh.Amount,
    PostedBy = pb.FIRSTNAME + ' ' + pb.LASTNAME,
    DownloadDate = sh.DateDownload,
    DownloadBy = db.FIRSTNAME + ' ' + db.LASTNAME,
    CreatedBy = bcb.FIRSTNAME + ' ' + bcb.LASTNAME,
    SplitDate = sh.DateSplit,
    SplitBy = sb.FIRSTNAME + ' ' + sb.LASTNAME,
    CashClearingAccount = pmpa.Description + '/' + pmpa.GLNumber,
    StatementHeaderId = sh.SHPriKey,
    SplitStatementId = null
from    StatementHeader sh
join    Branch b on sh.BranchPriKey = b.BranchPriKey
left join Sysuser bcb on sh.EnteredBy = bcb.SysUserID
left join Sysuser db on sh.DownLoadedBy = db.SysUserID
left join Sysuser pb on sh.PostedBy = pb.SysUserID
left join Sysuser sb on sh.Splitby = sb.SysUserID
left join PMPostingAccounts pmpa on sh.PMPostingAccountsPriKey = pmpa.PMPostingAccountsPrikey
union
select
    Branch = b.BranchID,
    Description = ss.StatementDesc,
    Status = ss.Status,
    CreatedDate = ss.DateEntered,
    PostedDate = ss.DatePosted,
    DepositDate = sh.JournalDate,
    ControlAmount = ss.Amount,
    PostedBy = pb.FIRSTNAME + ' ' + pb.LASTNAME,
    DownloadDate = ss.DateDownload,
    DownloadBy = db.FIRSTNAME + ' ' + db.LASTNAME,
    CreatedBy = bcb.FIRSTNAME + ' ' + bcb.LASTNAME,
    SplitDate = ss.DateSplit,
    SplitBy = sb.FIRSTNAME + ' ' + sb.LASTNAME,
    CashClearingAccount = pmpa.Description + '/' + pmpa.GLNumber,
    StatementHeaderId = sh.SHPriKey,
    SplitStatementId = ss.SSPriKey
from    StatementHeader sh
join SplitStatement ss on sh.SHPriKey = ss.SHPriKey
join Branch b on sh.BranchPriKey = b.BranchPriKey
left join Sysuser bcb on ss.EnteredBy = bcb.SysUserID
left join Sysuser db on ss.DownLoadedBy = db.SysUserID
left join Sysuser pb on ss.WhoPosted = pb.SysUserID
left join Sysuser sb on ss.Splitby = sb.SysUserID
left join PMPostingAccounts pmpa on sh.PMPostingAccountsPriKey = pmpa.PMPostingAccountsPrikey
GO
GRANT SELECT ON  [dbo].[vPaymentManagement] TO [MssExec]
GRANT INSERT ON  [dbo].[vPaymentManagement] TO [MssExec]
GRANT DELETE ON  [dbo].[vPaymentManagement] TO [MssExec]
GRANT UPDATE ON  [dbo].[vPaymentManagement] TO [MssExec]
GO