UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK

CREATE function dbo.UFN_OPPORTUNITY_AMOUNTINCURRENCY_BULK( 
    @CURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier
)
returns table
as return(

    select  OPPORTUNITY.ID, OPPORTUNITY.BASECURRENCYID, OPPORTUNITY.STATUSCODE, OPPORTUNITY.DATEADDED, 
                 OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT,OPPORTUNITY.ORGANIZATIONASKAMOUNT,OPPORTUNITY.PROSPECTPLANID,
                 OPPORTUNITY.ASKDATE,OPPORTUNITY.EXPECTEDASKDATE, OPPORTUNITY.RESPONSEDATE,
        coalesce (
            (case when STATUSCODE = 3 then  -- Accepted        

              coalesce((
                    select 
                        case when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID) then sum(ORGANIZATIONAMOUNT)
                        else sum(AMOUNT)
                        end
                    from
                        dbo.OPPORTUNITYDESIGNATION
                    where
                        OPPORTUNITYID=OPPORTUNITY.ID
                ), OPPORTUNITY.ACCEPTEDASKAMOUNT)
            else
                case when (@CURRENCYID is null) or (@CURRENCYID = @ORGANIZATIONCURRENCYID) then
                    case when ORGANIZATIONASKAMOUNT > 0 then ORGANIZATIONASKAMOUNT
                    else ORGANIZATIONEXPECTEDASKAMOUNT
                    end
                else
                    case when ASKAMOUNT > 0 then ASKAMOUNT
                    else EXPECTEDASKAMOUNT
                    end
                end
            end),
            0
        ) as AMOUNTINCURRENCY
    from
        dbo.OPPORTUNITY
    where @CURRENCYID is null 
     or (@CURRENCYID = @ORGANIZATIONCURRENCYID)
     or (@CURRENCYID = OPPORTUNITY.BASECURRENCYID)

    union all

    select OPPORTUNITY.ID, OPPORTUNITY.BASECURRENCYID, OPPORTUNITY.STATUSCODE, OPPORTUNITY.DATEADDED, 
             OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT,OPPORTUNITY.ORGANIZATIONASKAMOUNT,OPPORTUNITY.PROSPECTPLANID,
             OPPORTUNITY.ASKDATE,OPPORTUNITY.EXPECTEDASKDATE, OPPORTUNITY.RESPONSEDATE,
        coalesce(
        case when OPPORTUNITY.STATUSCODE = 3 then  -- Accepted

             case when LATESTEXCHANGERATE.ID is not null then
                    dbo.UFN_CURRENCY_CONVERT(OPPORTUNITY.DESIGNATIONORGAMOUNT,[LATESTEXCHANGERATE].ID)
                    --dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(OPPORTUNITY.DESIGNATIONORGAMOUNT, [LATESTEXCHANGERATE].RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                else
                    dbo.UFN_CURRENCY_CONVERTINVERSE(OPPORTUNITY.DESIGNATIONORGAMOUNT,[LATESTEXCHANGERATE].ID)
             end
         else
             case when ORGANIZATIONASKAMOUNT > 0 then 
                case when LATESTEXCHANGERATE.ID is not null then
                    dbo.UFN_CURRENCY_CONVERT(OPPORTUNITY.ORGANIZATIONASKAMOUNT, LATESTEXCHANGERATE.ID)
                    --dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(OPPORTUNITY.ORGANIZATIONASKAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                else 
                    dbo.UFN_CURRENCY_CONVERTINVERSE(OPPORTUNITY.ORGANIZATIONASKAMOUNT, LATESTEXCHANGERATE.ID)
                    --dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(OPPORTUNITY.ORGANIZATIONASKAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                end
             else 
                case when LATESTEXCHANGERATE.ID is not null then 
                    dbo.UFN_CURRENCY_CONVERT(OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT, LATESTEXCHANGERATE.ID)
                    --dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT, LATESTEXCHANGERATE.RATE), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                else 
                    dbo.UFN_CURRENCY_CONVERTINVERSE(OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT, LATESTEXCHANGERATE.ID)
                    --dbo.UFN_CURRENCY_ROUND(dbo.UFN_CURRENCY_APPLYRATE(OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT, cast((1 / LATESTINVERSEEXCHANGERATE.RATE) as decimal(20,8))), @DECIMALDIGITS, @ROUNDINGTYPECODE)

                end
             end
         end,
             0
        ) as AMOUNTINCURRENCY
    from (select OPPORTUNITY.ID, OPPORTUNITY.BASECURRENCYID, OPPORTUNITY.STATUSCODE, OPPORTUNITY.DATEADDED, 
                 OPPORTUNITY.ORGANIZATIONEXPECTEDASKAMOUNT,OPPORTUNITY.ORGANIZATIONASKAMOUNT,OPPORTUNITY.PROSPECTPLANID,
                 OPPORTUNITY.ASKDATE,OPPORTUNITY.EXPECTEDASKDATE, OPPORTUNITY.RESPONSEDATE,
                 (select SUM (OPPORTUNITYDESIGNATION.ORGANIZATIONAMOUNT) from OPPORTUNITYDESIGNATION
                  where OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID) DESIGNATIONORGAMOUNT
            from dbo.OPPORTUNITY
            ) as OPPORTUNITY
    outer apply
    (
        select
            ID
        from
            dbo.CURRENCYEXCHANGERATE
        where
            CURRENCYEXCHANGERATE.FROMCURRENCYID = @ORGANIZATIONCURRENCYID 
            and CURRENCYEXCHANGERATE.TOCURRENCYID = @CURRENCYID
            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= OPPORTUNITY.DATEADDED
            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= OPPORTUNITY.DATEADDED 
    ) LATESTEXCHANGERATE
    outer apply
    (
        select
            ID
        from
            dbo.CURRENCYEXCHANGERATE
        where
            CURRENCYEXCHANGERATE.FROMCURRENCYID = @CURRENCYID
            and CURRENCYEXCHANGERATE.TOCURRENCYID = @ORGANIZATIONCURRENCYID
            and CURRENCYEXCHANGERATE.TYPECODE in (0,1)
            and CURRENCYEXCHANGERATE.ISLATESTRATEFORASOFDATE = 1
            and CURRENCYEXCHANGERATE.ASOFDATESDTZ <= OPPORTUNITY.DATEADDED
            and CURRENCYEXCHANGERATE.NEXTRATEASOFDATEORMAXSDTZ >= OPPORTUNITY.DATEADDED 
    ) LATESTINVERSEEXCHANGERATE
    where (@CURRENCYID is not null) 
    and (@CURRENCYID <> @ORGANIZATIONCURRENCYID)
    and @CURRENCYID <> OPPORTUNITY.BASECURRENCYID        
);