/*
* Gets information about an invoiced header to populate MoversSuite bookmark values.
*/
create procedure [dbo].[GetInvoiceInfoForLetter]
    @inInvoicedHeaderId int
as

-- Calculate the "late payment amount" - that is, how much the shipper would need to pay (in addition to the invoice amount) if their payment was late.
declare @latePaymentAmount decimal(19,4)
select @latePaymentAmount =
    case
        when ChargeAmountType = 'Percentage' then InvoiceTotal * ( ChargeAmount * 0.01 )
        when ChargeAmountType = 'Flat' then ChargeAmount
        else 0.00
    end
from InvoicedHeader
where IHPriKey = @inInvoicedHeaderId

select
    InvoicedHeader.HeaderTitle,
    InvoicedHeader.BillingContact,
    dbo.BuildAddressCityStateCountryPostalCode
    (
        InvoicedHeader.HeaderAddress,
        InvoicedHeader.HeaderCity,
        InvoicedHeader.HeaderState,
        null,
        InvoicedHeader.HeaderPostalCode
    ) as HeaderAddress,
    InvoicedHeader.HeaderPhone,
    InvoicedHeader.InvoiceDate,
    InvoicedHeader.InvoiceNumber,
    InvoicedHeader.InvoiceTotal, -- Amount Due
    InvoicedHeader.DueDate,
    case
        when @latePaymentAmount > InvoicedHeader.MinimumCharge then InvoicedHeader.InvoiceTotal + @latePaymentAmount
        else InvoicedHeader.InvoiceTotal + InvoicedHeader.MinimumCharge
    end as InvoiceLateAmount,
    InvoicedHeader.RemittanceTitle,
    dbo.BuildAddressCityStateCountryPostalCode
    (
        InvoicedHeader.RemittanceAddress,
        InvoicedHeader.RemittanceCity,
        InvoicedHeader.RemittanceState,
        null,
        InvoicedHeader.RemittancePostalCode
    ) as RemittanceAddress,
    InvoicedHeader.RemittancePhone,
    InvoicedHeader.RemittanceEmail,
    InvoicedHeader.Conditions,
    AccountingNote.NoteText as InvoiceNote
from InvoicedHeader
left outer join AccountingNote on AccountingNote.ANPriKey = InvoicedHeader.ANPriKey
where InvoicedHeader.IHPriKey = @inInvoicedHeaderId
GO
GRANT EXECUTE ON  [dbo].[GetInvoiceInfoForLetter] TO [MssExec]
GO