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

create view [dbo].[vARCreditCollections]
as

select
    CustomerNumber = gpw.CustomerNumber,
    OrderNumber = gpw.OrderNumber,
    InvoiceNumber = gpw.InvoiceNumber,
    InvoiceDate = gpw.InvoiceDate,
    InvoiceTotal = gpw.InvoiceAmount,
    DaysOld = gpw.InvoiceAge,
    InvoiceCreatedDate = gpw.InvoiceCreatedDate,
    InvoiceCreatedBy = gpw.InvoiceCreatedBy,
    ShipperName = gpw.Shipper,
    FromAddress = gpw.OrderOriginAddress,
    ToAddress = gpw.OrderDestinationAddress,
    RevenueClerk = rev.FIRSTNAME + ' ' + rev.LASTNAME,
    TypeOfMove = mt.MoveName,
    NationalAccount = a.AcctName,
    Alerts = arct.ARCAlertDescription,
    PurchaseOrderNumber = gpw.PONumber,
    GBLNumber = gpw.GBLNumber,
    DocumentNumber = gpw.DocumentNumber,
    OriginalAmount = gpw.OriginalTransactionAmount,
    CurrentBalance = gpw.CurrentTransactionAmount,
    DocumentType = gpw.DocumentType,
    DocumentDescription = gpw.DocumentDescription,
    ItemCode = gpw.ItemCode,
    JournalDate = gpw.JournalDate,
    DocumentDate = gpw.DocumentDate,
    DatePosted = gpw.PostDate,
    DocumentAge = gpw.DocumentAge,
    Branch = b.BranchID,
    Collector = coll.FIRSTNAME + ' ' + coll.LASTNAME
from    GPWarehouse gpw
join Orders o on gpw.OrderID = o.PriKey
left join Sysuser rev on o.RevenueClerk = rev.SysUserID
left join MoveType mt on o.MoveType = mt.PriKey
left join Accounts a on o.AcctPriKey = a.AccountPriKey
left join ARCTriggeredAlertOrderMap arct on gpw.OrderID = arct.OrderFID and gpw.InvoicedHeaderID = arct.InvoicedHeaderFID
left join Branch b on gpw.BranchFID = b.BranchPriKey
left join ARCCustomerInformation arcc on gpw.CustomerNumber = arcc.CustomerNumber
left join Sysuser coll on arcc.CollectorSysuserFID = coll.SysUserID
GO
GRANT SELECT ON  [dbo].[vARCreditCollections] TO [MssExec]
GRANT INSERT ON  [dbo].[vARCreditCollections] TO [MssExec]
GRANT DELETE ON  [dbo].[vARCreditCollections] TO [MssExec]
GRANT UPDATE ON  [dbo].[vARCreditCollections] TO [MssExec]
GO