USP_DATAFORMTEMPLATE_EDITLOAD_SEPAMANDATE

create procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_SEPAMANDATE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @TSLONG bigint = 0 output,
    @CONSTITUENTACCOUNTDESCRIPTION nvarchar(255) = null output,
    @SIGNATUREDATE datetime = null output,
    @LOOKUPID nvarchar(35) = null output,
    @OVERRIDELOOKUPID bit = null output,
    @TYPECODE tinyint = null output,
    @STATUSCODE tinyint = null output,
    @ISPAYMENTMADE bit = null output,
    @ISPENDINGINBATCH bit = null output
)
as
    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0;
    set @TSLONG = 0;

    select
        @DATALOADED = 1,
        @TSLONG = SEPAMANDATE.TSLONG,
        @CONSTITUENTACCOUNTDESCRIPTION = dbo.UFN_CONSTITUENTACCOUNT_GETDESCRIPTION(CONSTITUENTACCOUNT.ID),
        @SIGNATUREDATE = SEPAMANDATE.SIGNATUREDATE,
        @LOOKUPID = SEPAMANDATE.LOOKUPID,
        @OVERRIDELOOKUPID = case
                                when SEPAMANDATE.CUSTOMIDENTIFIER = '' then
                                    0
                                else
                                    1
                            end,
        @TYPECODE = SEPAMANDATE.TYPECODE,
        @STATUSCODE = SEPAMANDATE.STATUSCODE,
        @ISPAYMENTMADE = case SEPAMANDATE.PAYMENTCOUNT when 0 then 0
                     else 1
                     end
    from 
        dbo.SEPAMANDATE
        inner join dbo.CONSTITUENTACCOUNT on CONSTITUENTACCOUNT.ID = SEPAMANDATE.CONSTITUENTACCOUNTID
    where 
        SEPAMANDATE.ID = @ID;

    set @ISPENDINGINBATCH = 0;
    if @DATALOADED = 1
    begin
        select 
            @ISPENDINGINBATCH = 1
        from
            dbo.REVENUESCHEDULE
            inner join dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT on REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = REVENUESCHEDULE.ID
        where
            REVENUESCHEDULEDIRECTDEBITPAYMENT.SEPAMANDATEID = @ID
            and REVENUESCHEDULE.ISPENDING = 1
    end

    return 0;