/*
* Description: Get the plastic instrument for the specified primary key.
**/
CREATE procedure [dbo].[GetPlasticInstrumentByInstrumentId]
    @inInstrumentId int
as
set nocount on

select
    PreferenceOrder = isnull( PlasticProvider.PreferenceOrder, 999999999 ),
    PlasticInstrument.InstrumentID,
    PlasticInstrument.ProviderFID,
    ProviderBrand = PlasticProviderType.Brand,
    UsesSeparateNameFields = PlasticProviderType.UsesSeparateNameFields,
    PlasticInstrument.Token,
    PlasticInstrument.Accessory,
    PlasticInstrument.ExpirationDate,
    PlasticInstrument.LastFour,
    PlasticInstrument.TokenTypeFID,
    PlasticInstrument.CustomerNumber,
    PlasticInstrument.OrderFID,
    PlasticInstrument.Inactive,
    PlasticInstrument.FirstUsedDate,
    PlasticInstrument.LastUsedDate,
    AttachedTo = case
        when isnull( PlasticInstrument.OrderFID, 0 ) > 0 and isnull( PlasticInstrument.CustomerNumber, '' ) != '' then 'Order/Customer'
        when isnull( PlasticInstrument.OrderFID, 0 ) > 0 then 'Order'
        when isnull( PlasticInstrument.CustomerNumber, '' ) != '' then 'Customer'
        else null
    end,
    IsNewPaymentType = convert( bit, 0 ),
    NickName = PlasticInstrument.NickName,
    HolderName = PlasticInstrument.HolderName,
    HolderLastName = PlasticInstrument.HolderLastName,
    BillingAddress = PlasticInstrument.BillingAddress,
    BillingAddress2 = PlasticInstrument.BillingAddress2,
    BillingCity = PlasticInstrument.BillingCity,
    BillingState = PlasticInstrument.BillingState,
    BillingPostalCode = PlasticInstrument.BillingPostalCode,
    BillingCountry = PlasticInstrument.BillingCountry,
    TokenTypeCode = PlasticTokenType.Code,
    TokenTypeDescription = PlasticTokenType.[Description],
    TokenAccountTypeCode = PlasticTokenAccountType.AccountType,
    TokenAccountTypeDescription = PlasticTokenAccountType.[Description],
    PlasticInstrument.GatewayCustomerID,
    IsCreditCard = PlasticTokenAccountType.IsCreditCard,
    PlasticInstrument.OnlineOkForFutureInv,
    PlasticInstrument.OnlineOkDate
from PlasticInstrument
inner join PlasticProvider on PlasticInstrument.ProviderFID = PlasticProvider.ProviderID
inner join PlasticProviderType on PlasticProvider.ProviderTypeFID = PlasticProviderType.ProviderTypeID
inner join PlasticTokenType on PlasticInstrument.TokenTypeFID = PlasticTokenType.TokenTypeID
inner join PlasticTokenAccountType on PlasticTokenType.TokenAccountTypeFID = PlasticTokenAccountType.TokenAccountTypeID
where PlasticInstrument.InstrumentID = @inInstrumentId
GO
GRANT EXECUTE ON  [dbo].[GetPlasticInstrumentByInstrumentId] TO [MssExec]
GO