USP_PAYABLESCHECK_SIGNATURES

CREATE procedure dbo.USP_PAYABLESCHECK_SIGNATURES
(
    @DISBURSEMENTPROCESSID uniqueidentifier 
    ,@AMOUNT money
)
as

with SIGNATURES_CTE (SIGNATURE1, SIGNATURE2) as (
select
        case
            when DP.SIGNATURE1OPTIONCODE = 0 then 
                (
                    select 
                        S1.SIGNATURE 
                    from 
                        dbo.BANKACCOUNTAUTHORIZEDSIGNATURE as BAAS1 
                        left outer join dbo.SIGNATURE as S1 on BAAS1.SIGNATUREID = S1.ID
                    where
                        BAAS1.ID = DP.SIGNATURE1ID
                )
            when DP.SIGNATURE1OPTIONCODE = 1 then null
            when DP.SIGNATURE1OPTIONCODE = 2 then
                (
                    select
                        top 1 S1.SIGNATURE
                    from 
                        dbo.DISBURSEMENTPROCESSSIGNATURE as DPS1
                        left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE as BAAS1 on DPS1.BANKACCOUNTAUTHORIZEDSIGNATUREID = BAAS1.ID
                        left outer join dbo.SIGNATURE as S1 on BAAS1.SIGNATUREID = S1.ID
                    where 
                        DPS1.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID 
                        and DPS1.FROMAMOUNT <= @AMOUNT 
                        and DPS1.TYPECODE = 0
                    order by
                        DPS1.FROMAMOUNT DESC
                )
        end SIGNATURE1
    ,    case
            when DP.SIGNATURE2OPTIONCODE = 0 then 
                (
                    select 
                        S2.SIGNATURE 
                    from 
                        dbo.BANKACCOUNTAUTHORIZEDSIGNATURE as BAAS2
                        left outer join dbo.SIGNATURE as S2 on BAAS2.SIGNATUREID = S2.ID
                    where
                        BAAS2.ID = DP.SIGNATURE2ID
                )
            when DP.SIGNATURE2OPTIONCODE = 1 then null
            when DP.SIGNATURE2OPTIONCODE = 2 then
                (
                    select
                        top 1 S2.SIGNATURE
                    from 
                        dbo.DISBURSEMENTPROCESSSIGNATURE as DPS2
                        left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE as BAAS2 on DPS2.BANKACCOUNTAUTHORIZEDSIGNATUREID = BAAS2.ID
                        left outer join dbo.SIGNATURE as S2 on BAAS2.SIGNATUREID = S2.ID
                    where 
                        DPS2.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID 
                        and DPS2.FROMAMOUNT <= @AMOUNT 
                        and DPS2.TYPECODE = 1
                    order by
                        DPS2.FROMAMOUNT DESC
                )
        end SIGNATURE2
from
    dbo.DISBURSEMENTPROCESS as DP
where
    DP.ID = @DISBURSEMENTPROCESSID
) 
select SIGNATURE1
  ,dbo.UFN_GETIMAGEMIMETYPE(SIGNATURE1) SIGNATURE1MIMETYPE  
  ,SIGNATURE2
  ,dbo.UFN_GETIMAGEMIMETYPE(SIGNATURE2) SIGNATURE2MIMETYPE  
from SIGNATURES_CTE

return 0