/**
*  $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