USP_BBNC_GETBENEFITSBYAPPEALID

      CREATE procedure dbo.USP_BBNC_GETBENEFITSBYAPPEALID
      (
        @APPEALID as uniqueidentifier,
        @AMOUNT as money = null
      )

      as
      begin

            if @AMOUNT IS null
              begin
                      select
                        APPEALBENEFITDETAIL.ID as ID ,
                        APPEALBENEFITDETAIL.BENEFITID as BENEFITID,
                        APPEALBENEFITDETAIL.QUANTITY,
                        isnull(APPEALBENEFITDETAIL.VALUE, 0) * isnull(APPEALBENEFITDETAIL.QUANTITY, 0) as TOTALVALUE,
                        APPEALBENEFITDETAIL.VALUE as UNITVALUE,
                        NAME as DETAILS,
                        SEQUENCE,
                        APPEALBENEFITDETAIL.BASECURRENCYID

                      from
                        dbo.BENEFIT
                        left join APPEALBENEFITDETAIL on BENEFIT.ID = APPEALBENEFITDETAIL.BENEFITID 
                        left join APPEALBENEFIT on APPEALBENEFITDETAIL.APPEALBENEFITID = APPEALBENEFIT.ID
                      where
                        APPEALBENEFIT.APPEALID = @APPEALID;
              end

            else
              begin
                      declare @APPEALBENEFITID As uniqueIdentifier;
                      set @APPEALBENEFITID = (select top 1 ID from APPEALBENEFIT where APPEALID = @APPEALID and AMOUNT <= @AMOUNT order by amount desc)

                      select
                        APPEALBENEFITDETAIL.ID as ID ,
                        APPEALBENEFITDETAIL.BENEFITID as BENEFITID,
                        APPEALBENEFITDETAIL.QUANTITY,
                        isnull(APPEALBENEFITDETAIL.VALUE, 0) * isnull(APPEALBENEFITDETAIL.QUANTITY, 0) as TOTALVALUE,
                        APPEALBENEFITDETAIL.VALUE as UNITVALUE,
                        NAME as DETAILS,
                        SEQUENCE,
                        APPEALBENEFITDETAIL.BASECURRENCYID

                      from
                        dbo.BENEFIT
                        inner join APPEALBENEFITDETAIL on BENEFIT.ID = APPEALBENEFITDETAIL.BENEFITID and APPEALBENEFITDETAIL.APPEALBENEFITID = @APPEALBENEFITID;
              End
        End