CREATE PROCEDURE [dbo].[MssWebGetOrderBillingInformation] @inOrderID int
AS
BEGIN
  SET NOCOUNT ON;
  WITH AccountPhoneType
  AS (SELECT
    OrderPhoneTypeID
  FROM OrderPhoneType
  WHERE typeName = 'Account')
  SELECT
    PaymentTypeId = Orders.PayTypeFID,
    PaymentMethodId = Orders.PayMethodFID,
    Orders.CustomerNumber,
    CustomerName = RM00101_Synonym.CUSTNAME,
    CreditAuthorization = Orders.CreditAuth,
    PurchaseOrderNumber = Orders.PurchaseOrderNo,
    Orders.CustomerAddressCode,
    Orders.BillToContact,
    BillToAddress = Orders.BillToAddr,
    Orders.BillToCity,
    Orders.BillToState,
    Orders.BillToZip,
    Orders.BillToCountryName,
    AccountFid = Orders.AcctPriKey,
    Orders.AccountName,
    Orders.AccountLocationCode,
    Orders.AccountContact,
    Orders.AccountMemo,
    AccountAddressId = AccountAddress.AccountAddressID,
    AccountAddress1 = AccountAddress.Address1,
    AccountAddress2 = AccountAddress.Address2,
    AccountAddress3 = AccountAddress.Address3,
    AccountCity = AccountAddress.City,
    AccountState = AccountAddress.State,
    AccountPostalCode = AccountAddress.PostalCode,
    CountryName = CountryCodeStandard.CountryName,
    AccountPhoneNumberId = OrderPhoneNumber.OrderPhoneNumberID,
    AccountPhoneNumberCountryCode = ISNULL(CountryCodeStandard.PhoneCountryCode, DefaultCountry.PhoneCountryCode),
    AccountPhoneNumberAreaCode = OrderPhoneNumber.AreaCode,
    AccountPhoneNumberLocalNumber = OrderPhoneNumber.LocalNumber,
    AccountPhoneNumberExtension = OrderPhoneNumber.Extension,
    CountryCodeStandardFid = ISNULL(CountryCodeStandard.CountryCodeStandardID, DefaultCountry.CountryCodeStandardID),
    RatePlanId = Orders.RPPriKey,
    Orders.Section,
    Orders.Discount,
    ContractMasterId = Orders.CMPriKey,
    Orders.ApplyPeakRateFlag,
    Orders.TeamPay,
    EstimatedAmount = Orders.EstAmt,
    ActualAmount = Orders.ActualCost,
    CommodityDetailFid = OrdersExtended.CommodityDetailFID,
    OrdersExtended.CodCollectionAmount,
    OrdersExtended.WillAdvise,
    HaulModeFid = OrdersExtended.HaulModeFID
  FROM Orders
  CROSS JOIN dbo.GetDefaultCountry() DefaultCountry
  CROSS JOIN AccountPhoneType
  LEFT OUTER JOIN RM00101_Synonym
    ON RM00101_Synonym.CUSTNMBR = Orders.CustomerNumber
  LEFT OUTER JOIN AccountAddress
    ON AccountAddress.AccountFID = Orders.AcctPriKey
  LEFT OUTER JOIN OrdersExtended
    ON OrdersExtended.OrderFID = Orders.PriKey
  LEFT OUTER JOIN OrderPhoneNumber
    ON OrderPhoneNumber.OrderFID = Orders.PriKey
    AND OrderPhoneNumber.OrderPhoneTypeFID = AccountPhoneType.OrderPhoneTypeID
  LEFT OUTER JOIN CountryCodeStandard
    ON OrderPhoneNumber.CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
  WHERE Orders.PriKey = @inOrderID

END
GO
GRANT EXECUTE ON  [dbo].[MssWebGetOrderBillingInformation] TO [MssExec]
GO