UFN_REVENUE_GETRECEIPTSTATUS

CREATE function dbo.UFN_REVENUE_GETRECEIPTSTATUS
(
  @REVENUEID uniqueidentifier
)
returns nvarchar(50)
with execute as caller
as
begin
  declare @STATUS nvarchar(50);
  declare @DONOTRECEIPT bit;
  declare @NEEDSRERECEIPT bit;
  declare @RECEIPTTYPECODE int;

  select 
    @DONOTRECEIPT = REVENUE_EXT.DONOTRECEIPT, 
    @NEEDSRERECEIPT = REVENUE_EXT.NEEDSRERECEIPT,
    @RECEIPTTYPECODE = REVENUE_EXT.RECEIPTTYPECODE
  from 
    dbo.FINANCIALTRANSACTION
  inner join
    dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where 
    FINANCIALTRANSACTION.ID = @REVENUEID and
    FINANCIALTRANSACTION.DELETEDON is null;

  if @DONOTRECEIPT = 1
    begin
      set @STATUS = 'Do not receipt';
    end
  else
    begin
      declare @RECEIPTID uniqueidentifier;
      declare @RECEIPTDATE datetime;
      declare @NEWNUMBERONRERECEIPT bit;

      select top 1
        @RECEIPTID = RR.ID,
        @RECEIPTDATE = RR.RECEIPTDATE,
        @NEWNUMBERONRERECEIPT = RRD.NEWNUMBERONRERECEIPT
      from
        dbo.REVENUERECEIPT RR 
      left join
        dbo.REVENUERECEIPTRERECEIPTDETAIL RRD on RRD.REVENUERECEIPTID = RR.ID
      where
        RR.REVENUEID = @REVENUEID 
      order by
        RR.RECEIPTPROCESSDATE desc,
        RRD.DATEADDED desc;

      if @RECEIPTID is null
        begin
          set @STATUS = 'Not receipted';
        end
      else if @RECEIPTDATE is null
        begin
          set @STATUS = 'Pending';
        end
      else
        begin
          set @STATUS = 'Receipted';

          if @NEEDSRERECEIPT = 1
            begin
              if @NEWNUMBERONRERECEIPT is null
                select top 1 @NEWNUMBERONRERECEIPT = case when SAMENUMBERONRERECEIPT = 1 then 0 else 1 end from dbo.RECEIPTPREFERENCEINFO;

              if @NEWNUMBERONRERECEIPT = 1
                set @STATUS = @STATUS + ' (eligible for re-receipt, new number)';
              else
                set @STATUS = @STATUS + ' (eligible for re-receipt, same number)';
            end
        end
    end

  return @STATUS;
end