[dbo].[OrdersFinancialInformation]
CREATE view [dbo].[OrdersFinancialInformation]
(
OrdersFinancialInformationOrdersFID,
OrdersFinancialInformationOrderNumber,
CustomerNumber,
BillToContact,
BillToAddr,
BillToCity,
BillToState,
BillToZip,
NationalAcctNo,
NationalAcctName,
NationalAcctContact,
NationalAcctAddress,
NationalAcctCity,
NationalAcctState,
NationalAcctZip,
NationalAcctCounty,
NationalAcctPhone,
NationalAcctExt,
NationalAcctMemo,
ValuationFID,
ValuationAmount,
PaymentType,
Discount,
CreditAuthorization,
PurchaseOrderNo,
RatePlanName,
RatePlanDesc,
Contract,
VanLineSection,
EstimateAmount,
ActualAmount,
PaperWorkReceived,
RevenueClerkFirstName,
RevenueClerkLastName,
BillDate,
PeakRates,
EstimatedLineHaul
)
as
select distinct
OrdersFinancialInformationOrdersFID = Orders.PriKey,
OrdersFinancialInformationOrderNumber = Orders.OrderNo,
CustomerNumber = Orders.CustomerNumber,
BillToContact = Orders.BillToContact,
BillToAddr = Orders.BillToAddr,
BillToCity = Orders.BillToCity,
BillToState = Orders.BillToState,
BillToZip = Orders.BillToZip,
NationalAcctNo = Accounts.AcctNo,
NationalAcctName = isnull( Orders.AccountName, Accounts.AcctName ),
NationalAcctContact = isnull( Orders.AccountContact, Accounts.Contact ),
NationalAcctAddress = AccountAddress.Address1,
NationalAcctCity = AccountAddress.City,
NationalAcctState = AccountAddress.State,
NationalAcctZip = AccountAddress.PostalCode,
NationalAcctCounty = Accounts.County,
NationalAcctPhone =
case
when OrderAccountPhone.OrderPhoneNumberID is not null
then dbo.GetFormattedPhoneNumber( OrderAccountPhone.CountryCodeStandardFID, OrderAccountPhone.AreaCode, OrderAccountPhone.LocalNumber )
else dbo.GetFormattedPhoneNumber( AccountPhoneNumber.CountryCodeStandardFID, AccountPhoneNumber.AreaCode, AccountPhoneNumber.LocalNumber )
end,
NationalAcctExt =
case
when OrderAccountPhone.OrderPhoneNumberID is not null
then OrderAccountPhone.Extension
else AccountPhoneNumber.Extension
end,
NationalAcctMemo = isnull( Orders.AccountMemo, Accounts.Note ),
Valuation = Valuation.Name,
ValuationAmount = Orders.ValuationAmount,
PaymentType = isnull( PayType.PayName + ' - ', '' ) + PayMethod.Method,
Discount = Orders.Discount,
CreditAuthorization = Orders.CreditAuth,
PurchaseOrderNo = Orders.PurchaseOrderNo,
RatePlanName = RatePlans.RatePlanName,
RatePlanDesc = RatePlans.Description,
Contract = Contractmaster.Description,
VanLineSection = Orders.Section,
EstimateAmount = Orders.EstAmt,
ActualAmount = Orders.ActualCost,
PaperWorkReceived = Orders.PaperworkRecd,
RevenueClerkFirstName = Sysuser.FirstName,
RevenueClerkLastName = Sysuser.LastName,
BillDate = Orders.ReleaseDate,
PeakRates = Orders.ApplyPeakRateFlag,
EstimatedLineHaul = Orders.LineHaul
from Orders
left outer join Branch on ( Branch.BranchPriKey = Orders.BranchPriKey )
left outer join Accounts on ( Accounts.AccountPriKey = Orders.AcctPriKey )
left outer join AccountPhoneNumber on ( AccountPhoneNumber.AccountFID = Orders.AcctPriKey )
left outer join AddressType on ( AddressType.TypeName = 'Main' )
left outer join AccountAddress on ( AccountAddress.AccountFID = Accounts.AccountPriKey and AccountAddress.AddressTypeFID = AddressType.AddressTypeID )
left outer join RatePlans on ( RatePlans.RPPriKey = Orders.RPPriKey )
left outer join Valuation on Valuation.ValuationID = Orders.ValuationFID
left outer join PayMethod on ( PayMethod.PayMethodID = Orders.PayMethodFID )
left outer join PayType on ( Orders.PayTypeFID = PayType.PayTypeID )
left outer join ContractMaster on ( ContractMaster.CMPriKey = Orders.CMPriKey )
left outer join Sysuser on ( Sysuser.SysUserID = Orders.RevenueClerk )
left outer join OrderPhoneType on ( OrderPhoneType.TypeName = 'Account' )
left outer join OrderPhoneNumber as OrderAccountPhone on ( OrderAccountPhone.OrderFID = Orders.PriKey and OrderAccountPhone.OrderPhoneTypeFID = OrderPhoneType.OrderPhoneTypeID )
GO
GRANT SELECT ON [dbo].[OrdersFinancialInformation] TO [MssExec]
GRANT INSERT ON [dbo].[OrdersFinancialInformation] TO [MssExec]
GRANT DELETE ON [dbo].[OrdersFinancialInformation] TO [MssExec]
GRANT UPDATE ON [dbo].[OrdersFinancialInformation] TO [MssExec]
GO