USP_DATAFORMTEMPLATE_VIEW_TRANSACTIONDETAILS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_TRANSACTIONDETAILS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @REVENUEID uniqueidentifier = null output,
    @DEPOSITID uniqueidentifier = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @REVENUEUSERDEFINEDID nvarchar(255) = null output,
    @DEPOSITNUMBER nvarchar(255) = null output,
    @CONSTITUENTNAME nvarchar(255) = null output,
    @DATE date = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;

    if exists (select 1 from dbo.CREDITPAYMENT where ID = @ID)
        select
            @REVENUEID = FT.ID,
            @DEPOSITID = BATX.ID,
            @CONSTITUENTID = C.ID,
            @REVENUEUSERDEFINEDID = FT.CALCULATEDUSERDEFINEDID,
            @DEPOSITNUMBER = BA.ACCOUNTNAME + ' - ' + cast(BATX.TRANSACTIONNUMBER as nvarchar(10)),
            @CONSTITUENTNAME = C.NAME,
            @DATE = cast(FT.DATE as date)
        from
            dbo.CREDITPAYMENT CP
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = CP.REVENUEID
            left join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT BADCP on BADCP.ID = CP.ID
            left join dbo.BANKACCOUNTTRANSACTION_EXT BATX on BATX.ID = BADCP.DEPOSITID
            left join dbo.BANKACCOUNT BA on BA.ID = BATX.BANKACCOUNTID
            left join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID
        where
            CP.ID = @ID
    else
        select
            @REVENUEID = FT.ID,
            @DEPOSITID = BATX.ID,
            @CONSTITUENTID = C.ID,
            @REVENUEUSERDEFINEDID = FT.CALCULATEDUSERDEFINEDID,
            @DEPOSITNUMBER = BA.ACCOUNTNAME + ' - ' + cast(BATX.TRANSACTIONNUMBER as nvarchar(10)),
            @CONSTITUENTNAME = C.NAME,
            @DATE = cast(FT.DATE as date)
        from
            dbo.CREDITCARDPAYMENTMETHODDETAIL CCPMD
            inner join dbo.REVENUEPAYMENTMETHOD RPM on RPM.ID = CCPMD.ID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = RPM.REVENUEID
            left join dbo.BANKACCOUNTDEPOSITPAYMENT BADP on BADP.ID = FT.ID
            left join dbo.BANKACCOUNTTRANSACTION_EXT BATX on BATX.ID = BADP.DEPOSITID
            left join dbo.BANKACCOUNT BA on BA.ID = BATX.BANKACCOUNTID
            left join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID
    where
        CCPMD.ID = @ID