/*
* Description: Gets the plastic information used to build a specific PlasticDeal.
**/
CREATE procedure [dbo].[GetPlasticPaymentInfo]
    @inDealId int
as
set nocount on

select
    BranchPriKey = StatementDetail.BranchPriKey,
    BranchName = Branch.[Name],
    ShipperName = dbo.FormatFirstNameLastName( Orders.FirstName, Orders.LastName ),
    ShipperLastName = Orders.LastName,
    OrderId = Orders.PriKey,
    OrderNumber = Orders.OrderNo,
    CustomerNumber = StatementDetail.CustomerNumber,
    CustomerName = RM00101_Synonym.CUSTNAME,
    [Description] = PlasticDeal.[Description],
    PaymentAmount = PlasticDeal.OriginalAmount,
    ApprovalCode = PlasticDeal.ApprovalCode,
    AccountTypeDesc = rtrim( PaymentType.[Description] ),
    RetainAccountForFutureUse = convert( bit, case PlasticInstrument.Inactive when 1 then 0 else 1 end ),
    AttachCardToCustomer = convert( bit, case when isnull( PlasticInstrument.CustomerNumber, '' ) = '' then 0 else 1 end ),
    TransactionTypeCode = PlasticDeal.DealType,
    PlasticOriginCode = PlasticOrigin.Code,
    PaymentTypeID = PaymentType.PTPriKey,
    PaymentCodeID = PaymentCode.PaymentCodePriKey,
    IsNewPaymentType = convert( bit, case when PlasticTokenType.Code = '**' then 1 else 0 end ),
    PurchaseOrderNo = PlasticDeal.PurchaseOrderNo,
    ApiSessionID = PlasticDeal.HppSessionID
from PlasticDeal
inner join PlasticInstrument on PlasticInstrument.InstrumentID = PlasticDeal.InstrumentFID
inner join PlasticTokenType on PlasticTokenType.TokenTypeID = PlasticInstrument.TokenTypeFID
inner join PlasticOrigin on PlasticOrigin.OriginID = PlasticDeal.OriginFID
left outer join StatementDetail on StatementDetail.SDPriKey = PlasticDeal.StatementDetailFID
left outer join PaymentType on PaymentType.PTPriKey = StatementDetail.PTPriKey
left outer join PaymentCode on PaymentCode.PaymentCodePriKey = StatementDetail.PaymentCodePriKey
left outer join Branch on Branch.BranchPriKey = StatementDetail.BranchPriKey
left outer join Orders on Orders.PriKey = PlasticInstrument.OrderFID
left outer join RM00101_Synonym on RM00101_Synonym.CUSTNMBR = StatementDetail.CustomerNumber
where PlasticDeal.DealID = @inDealId
GO
GRANT EXECUTE ON  [dbo].[GetPlasticPaymentInfo] TO [MssExec]
GO