USP_DATALIST_PAYABLESVENDOR_ACTIVITY

CREATE procedure dbo.USP_DATALIST_PAYABLESVENDOR_ACTIVITY
(
    @CONSTITUENTID uniqueidentifier,
    @TRANSACTIONTYPECODE int = NULL,
    @STATUSCODE int = NULL,
    @DATERANGECODE int = NULL) as  
begin
    declare @STARTDATE as date;
    declare @ENDDATE as date;

    set @TRANSACTIONTYPECODE = COALESCE(@TRANSACTIONTYPECODE,0)
    set @STATUSCODE = COALESCE(@STATUSCODE,0)
    set @DATERANGECODE = COALESCE(@DATERANGECODE,0)

    execute dbo.USP_FINANCIAL_GETDATESFORPAYMENTRANGE @DATERANGECODE,@STARTDATE output, @ENDDATE output;

    select
         FT.ID
        ,convert(date, FT.[DATE]) [DATE]
        ,FT.USERDEFINEDID
        ,FT.[TYPE]
        ,case when FT.[TYPECODE]=101 then dbo.UFN_INVOICE_GETSTATUS(I.HOLDPAYMENT, I.BALANCE, FT.TRANSACTIONAMOUNT) 
            when FT.[TYPECODE]=102 then dbo.UFN_CREDITMEMO_GETSTATUS(CM.BALANCE, FT.TRANSACTIONAMOUNT) end as [STATUS]
        ,FT.TRANSACTIONAMOUNT as AMOUNT
        ,I.DATEDUE -- It will be just null for a credit memo

        ,coalesce(I.BALANCE, CM.BALANCE) as BALANCE
        ,coalesce(I.PURCHASEORDERID, CM.PURCHASEORDERID) as PURCHASEORDERID
        ,FT.TYPECODE
        ,case when FT.[TYPECODE]=101 then case when I.DISBURSEMENTPROCESSID is null then 0 else 1 end  
            when FT.[TYPECODE]=102 then case when CM.DISBURSEMENTPROCESSID is null then 0 else 1 end  end as [ISLOCKED]
    from dbo.FINANCIALTRANSACTION as FT 
    left outer join dbo.INVOICE as I on FT.ID = I.ID
    left outer join dbo.CREDITMEMO as CM on FT.ID = CM.ID
    where 
        (FT.DATE >= @STARTDATE)
        and (@TRANSACTIONTYPECODE = 0 or FT.[TYPECODE] = @TRANSACTIONTYPECODE)
        and ( 
                 (FT.[TYPECODE] = 102 -- credit memo 

                   and (
                        (@STATUSCODE = 0)
                        or (@STATUSCODE = 2 and CM.BALANCE=0)
                        or (@STATUSCODE = 3 and CM.BALANCE>0 and CM.BALANCE<FT.TRANSACTIONAMOUNT)
                        or (@STATUSCODE = 4 and FT.TRANSACTIONAMOUNT=CM.BALANCE)
                   )
                 )
              or ( FT.[TYPECODE] = 101 -- invoice

                   and (
                        (@STATUSCODE = 0)
                        or (@STATUSCODE = 1 and I.HOLDPAYMENT=1)
                        or (@STATUSCODE = 2 and I.BALANCE=0 and I.HOLDPAYMENT=0)
                        or (@STATUSCODE = 3 and I.BALANCE>0 and I.BALANCE<FT.TRANSACTIONAMOUNT and I.HOLDPAYMENT=0)
                        or (@STATUSCODE = 4 and FT.TRANSACTIONAMOUNT=I.BALANCE and I.HOLDPAYMENT=0)
                    )
                 )
            )
        and FT.CONSTITUENTID = @CONSTITUENTID
        and ((FT.TYPECODE = 101 AND NOT (I.ID IS NULL)) or (FT.TYPECODE=102 AND NOT (CM.ID IS NULL)))
        and FT.DELETEDON IS NULL
    order by FT.[DATE] DESC
    set nocount on;
end;