[dbo].[MssWebGetOrderBillingInformation]
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