USP_PAYABLESCHECK

/*
  User options allow the user to resort the checks by 
  (Payee OR Disbursement amount) AND (ASC OR DESC).  Instead of having
  two or four SQL statements that are the same except for the order by clause
  this SQL statement will combine all of the SQLs into one statement combining
  the ordering options.

  DF.PRINTTRANSACTIONSORDERCODE
    0 = Payee
    1 = Disbursement amount
  DF.PRINTORDERCODE
    0 = ASC
    1 = DESC
*/

CREATE procedure dbo.USP_PAYABLESCHECK
(
    @DISBURSEMENTPROCESSID uniqueidentifier
)
with execute as owner
as
set nocount on;

select
    BATE.ID
    --,                                                                                    as CHECKNOTE    -- Currently not available.

    ,FT.DATE                                                                          as CHECKDATE
    ,case when DF.PRINTNUMBER = 1 
        THEN BATE.TRANSACTIONNUMBER
        ELSE 0 END                                                                                        as CHECKNUMBER
    ,dbo.UFN_NUMBERTOWORDS(FT.TRANSACTIONAMOUNT)                            as CHECKAMOUNTINWORDS    
    ,FT.TRANSACTIONAMOUNT                                                                        as CHECKAMOUNT
    ,C.NAME                                                                              as VENDORNAME
    ,BATE.ADDRESSBLOCK                                                                as VENDORADDRESSBLOCK
    ,BATE.CITY + ', ' + S.ABBREVIATION + ' ' + BATE.POSTCODE            as VENDORCITYSTATEZIP
    ,case DF.PRINTCUSTOMERNUMBER
        when 1 then V.CUSTOMERNUMBER                                            
        else ''
     end                                                                                as VENDORCUSTOMERNUMBER
    --,                                                                                    as VENDORPRIMARYCONTACT        -- Currently not available.

    ,P.NUMBER                                                                          as VENDORPRIMARYPHONE
    ,E.EMAILADDRESS                                                                  as VENDORPRIMARYEMAIL
    --, as SIGNATURE1                                        -- Use UFN_PAYABLESCHECK_SIGNATURES to find this value due to the possibility of signature being based on amount.

    --, as SIGNATURE2                                        -- Use UFN_PAYABLESCHECK_SIGNATURES to find this value due to the possibility of signature being based on amount.

from
  dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
  inner join dbo.BANKACCOUNTTRANSACTION_EXT as BATE on DPD.ID = BATE.ID
    inner join dbo.FINANCIALTRANSACTION as FT on BATE.ID = FT.ID
  inner join dbo.DISBURSEMENTPROCESSFORMAT as DPF on BATE.DISBURSEMENTPROCESSID = DPF.DISBURSEMENTPROCESSID
    inner join dbo.DISBURSEMENTFORMAT as DF on DPF.DISBURSEMENTFORMATID = DF.ID
  inner join dbo.UFN_DISBURSEMENTPROCESS_FORMATPRINTORDER(@DISBURSEMENTPROCESSID) as DPFPO on DPD.ID = DPFPO.ID
    inner join dbo.VENDOR as V on FT.CONSTITUENTID = V.ID
  inner join dbo.CONSTITUENT as C on V.ID = C.ID
    left outer  join dbo.PHONE as P on V.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
    left outer join dbo.EMAILADDRESS as E on V.ID = E.CONSTITUENTID and E.ISPRIMARY = 1
    left outer join dbo.STATE as S on BATE.STATEID = S.ID
where
    BATE.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
order by 
  DPFPO.SORTCOLUMN
return