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