Views [dbo].[OrdersFinancialInformation]
Properties
PropertyValue
ANSI Nulls OnYes
Quoted Identifier OnYes
Created6:18:19 PM Tuesday, February 27, 2007
Last Modified2:29:39 AM Saturday, February 4, 2017
Columns
Name
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
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
/**
*  $File: //Dev02/Maroon/R2_1/Reports/SQL/Views/OrdersFinancialInformation.sql $
*  $DateTime: 2009/07/23 13:16:18 $
*  $Change: 26601 $
*  $Revision: #1 $
*  $Author: ssaad $
*  
*  Description of funtionality
*/


CREATE view [dbo].[OrdersFinancialInformation]
  (
    OrdersFinancialInformationOrdersFID,
    OrdersFinancialInformationOrderNumber,
    
    --Customer information
    CustomerNumber,
    BillToContact,
    BillToAddr,
    BillToCity,
    BillToState,
    BillToZip,
    
    --National Account Information
    NationalAcctNo,
    NationalAcctName,
    NationalAcctContact,
    NationalAcctAddress,
    NationalAcctCity,
    NationalAcctState,
    NationalAcctZip,
    NationalAcctCounty,
    NationalAcctPhone,
    NationalAcctExt,
    NationalAcctMemo,
    
    --Billing Information
    ValuationFID,
    ValuationAmount,
    PaymentType,
    Discount,
    CreditAuthorization,
    PurchaseOrderNo,
    RatePlanName,
    RatePlanDesc,
    Contract,
    VanLineSection,
    EstimateAmount,
    ActualAmount,
    PaperWorkReceived,
    RevenueClerkFirstName,
    RevenueClerkLastName,
    BillDate,
    PeakRates,
    
    --Various Other Information
    EstimatedLineHaul
  )

as

select distinct
  OrdersFinancialInformationOrdersFID        = Orders.PriKey,
  OrdersFinancialInformationOrderNumber    = Orders.OrderNo,
  
  --Customer information
  CustomerNumber        = Orders.CustomerNumber,
  BillToContact         = Orders.BillToContact,
  BillToAddr            = Orders.BillToAddr,
  BillToCity            = Orders.BillToCity,
  BillToState           = Orders.BillToState,
  BillToZip             = Orders.BillToZip,
  
  --National Account Information
  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 ),
  
  --Billing Information
  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,
  
  --Various Other Information
  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
Uses