Views [dbo].[vARCreditCollections]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created10:56:48 AM Monday, May 4, 2015
Last Modified1:53:56 PM Wednesday, April 10, 2024
Columns
Name
CustomerNumber
OrderNumber
InvoiceNumber
InvoiceDate
InvoiceTotal
DaysOld
InvoiceCreatedDate
InvoiceCreatedBy
ShipperName
FromAddress
ToAddress
RevenueClerk
TypeOfMove
NationalAccount
Alerts
PurchaseOrderNumber
GBLNumber
DocumentNumber
OriginalAmount
CurrentBalance
DocumentType
DocumentDescription
ItemCode
JournalDate
DocumentDate
DatePosted
DocumentAge
Branch
Collector
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].[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
Uses