USP_DATALIST_APPEALDEFAULTSLOOKUP
Provides look-up information for an appeal for a given amount.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @APPEALID | uniqueidentifier | IN | Appeal ID |
| @AMOUNT | money | IN | Amount |
| @TRANSACTIONCURRENCYID | uniqueidentifier | IN | Transaction currency ID |
| @BASECURRENCYID | uniqueidentifier | IN | Base currency ID |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_APPEALDEFAULTSLOOKUP
(
@APPEALID uniqueidentifier,
@AMOUNT money,
@TRANSACTIONCURRENCYID uniqueidentifier = null,
@BASECURRENCYID uniqueidentifier = null
)
as
set nocount on;
if @BASECURRENCYID is null
select @BASECURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
if @TRANSACTIONCURRENCYID is null
select @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
declare @DECIMALDIGITS tinyint
declare @ROUNDINGTYPECODE tinyint
select
@DECIMALDIGITS = DECIMALDIGITS,
@ROUNDINGTYPECODE = ROUNDINGTYPECODE
from dbo.CURRENCY
where ID = @TRANSACTIONCURRENCYID
select
APPEALDESIGNATION.DESIGNATIONID,
DESIGNATION.USERID,
0,
0,
0,
0 TYPE,
null,
null,
null,
null,
null,
null,
null,
null
from dbo.APPEALDESIGNATION
inner join dbo.DESIGNATION on DESIGNATION.ID = APPEALDESIGNATION.DESIGNATIONID
where APPEALDESIGNATION.APPEALID = @APPEALID and APPEALDESIGNATION.ISDEFAULT = 1
union
select
BENEFITID,
dbo.UFN_BENEFIT_GETNAME(BENEFITID) as [TRANSLATION],
VALUE,
QUANTITY,
SEQUENCE,
1 TYPE,
null,
null,
null,
null,
null,
case
when BASECURRENCYID = @TRANSACTIONCURRENCYID then VALUE * QUANTITY
else
dbo.UFN_CURRENCY_CONVERT(
VALUE * QUANTITY,
dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(
BASECURRENCYID,
@TRANSACTIONCURRENCYID,
getdate(),
1,
default
)
)
end,
null,
null
from dbo.UFN_APPEAL_GETBENEFITDETAILS_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID, @BASECURRENCYID)
union
select
MEMBERSHIPPROGRAMID,
MEMBERSHIPPROGRAM,
0,
0,
0,
2 TYPE,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVEL,
MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTERM,
null,
null,
null,
null
from dbo.UFN_APPEAL_GETMEMBERSHIPDEFAULTS(@APPEALID, @AMOUNT)
union
select
BENEFITID,
dbo.UFN_BENEFIT_GETNAME(BENEFITID) as [TRANSLATION],
null,
QUANTITY,
SEQUENCE,
3 TYPE,
null,
null,
null,
null,
VALUEPERCENT,
null,
@DECIMALDIGITS,
@ROUNDINGTYPECODE
from dbo.UFN_APPEAL_GETBENEFITDETAILS_PCT_2(@APPEALID, @AMOUNT, @TRANSACTIONCURRENCYID)
order by TYPE