USP_REPORT_ADJUSTEDREVENUE_SINGLERECORD
Returns the data necessary for the Adjusted Revenue Single Record report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_ADJUSTEDREVENUE_SINGLERECORD
(
@REVENUEID uniqueidentifier,
@CURRENCYCODE tinyint = null
)
as
set nocount on;
set transaction isolation level read uncommitted;
select
ADJUSTMENTHISTORY.ADJUSTMENTIDENTIFIER,
ADJUSTMENT.DATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORY.CONSTITUENTNAME,
--JamesWill 04/29/2008 CR299003-042508 The data is stored somewhat inconveniently in the report tables, so convert the "payment" types to say "Payment"; otherwise, use what's there.
case
when ADJUSTMENTHISTORY.REVENUETYPE in ('Gift', 'Pledge payment', 'Recurring gift payment',
'Matching gift payment', 'Event registration fee', 'Membership fee', 'Donor challenge payment') then 'Payment'
else ADJUSTMENTHISTORY.REVENUETYPE
end as [REVENUETYPE],
ADJUSTMENTHISTORY.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENT.REASON as [ADJUSTMENTREASON],
[DISTRIBUTION].ID as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
ADJUSTMENT.POSTSTATUS,
ADJUSTMENT.POSTDATE,
case @CURRENCYCODE when 0 then FINANCIALTRANSACTION.BASEAMOUNT when 2 then FINANCIALTRANSACTION.TRANSACTIONAMOUNT else FINANCIALTRANSACTION.ORGAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORY.DATEADDED
from dbo.ADJUSTMENTHISTORY
/*this needs to be a left join because adjustments consisting of just GL changes will not have changed fields.*/
left join dbo.ADJUSTMENTHISTORYDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYID = ADJUSTMENTHISTORY.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETDISTRIBUTION_FORADJUSTMENTHISTORYID_FORDISPLAY(ADJUSTMENTHISTORY.ID) as [DISTRIBUTION]
inner join dbo.ADJUSTMENT on ADJUSTMENT.ID = ADJUSTMENTHISTORY.ADJUSTMENTID /*this join means this select only works for non-deleted adjustments*/
inner join dbo.FINANCIALTRANSACTION on ADJUSTMENT.REVENUEID = FINANCIALTRANSACTION.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where
ADJUSTMENTHISTORY.REVENUEIDENTIFIER = cast(@REVENUEID as nvarchar(36))
group by
ADJUSTMENTIDENTIFIER, ADJUSTMENT.DATE, CONSTITUENTNAME, REVENUETYPE, REVENUEDATE, [DETAIL].ID, [DETAIL].ADJUSTEDFIELD,
[DETAIL].ADJUSTEDFIELDPREVIOUSVALUE, [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE, [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE,
[DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE,
[DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ADJUSTMENT.REASON, [DISTRIBUTION].ID, [DISTRIBUTION].TYPECODE,
[DISTRIBUTION].TYPE, [DISTRIBUTION].TRANSACTIONTYPE, [DISTRIBUTION].ACCOUNT, [DISTRIBUTION].PROJECT,
[DISTRIBUTION].AMOUNT, [DISTRIBUTION].TRANSACTIONAMOUNT, [DISTRIBUTION].ORGANIZATIONAMOUNT, ADJUSTMENT.POSTSTATUS,
ADJUSTMENT.POSTDATE, FINANCIALTRANSACTION.BASEAMOUNT, FINANCIALTRANSACTION.TRANSACTIONAMOUNT, FINANCIALTRANSACTION.ORGAMOUNT, ADJUSTMENT.REASONCODEID, [DETAIL].ISMONEY,
[DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE, [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE,
[DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE,
[DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217, DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL, DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
DETAILCURRENCYPROPERTIES.DECIMALDIGITS, DISTRIBUTIONCURRENCYPROPERTIES.ISO4217, DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL,
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS ,ADJUSTMENTHISTORY.DATEADDED
union all
/*get adjustments for sold stock items*/
select
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTIDENTIFIER,
STOCKSALEADJUSTMENT.DATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYSTOCK.CONSTITUENTNAME,
'Sold stock' as [REVENUETYPE],
ADJUSTMENTHISTORYSTOCK.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
STOCKSALEADJUSTMENT.POSTSTATUS,
STOCKSALEADJUSTMENT.POSTDATE,
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(STOCKSALEADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYSTOCK.DATEADDED
from dbo.ADJUSTMENTHISTORYSTOCK
left join dbo.ADJUSTMENTHISTORYSTOCKDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYSTOCKID = ADJUSTMENTHISTORYSTOCK.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION_FORADJUSTMENTHISTORYSTOCKID_FORDISPLAY(ADJUSTMENTHISTORYSTOCK.ID) as [DISTRIBUTION]
inner join dbo.STOCKSALEADJUSTMENT on STOCKSALEADJUSTMENT.ID = ADJUSTMENTHISTORYSTOCK.STOCKSALEADJUSTMENTID /*this join means this select only works for non-deleted adjustments*/
inner join dbo.STOCKSALE on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID
inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where REVENUE.ID = @REVENUEID
union all
/*get adjustments for sold gift-in-kind items*/
select
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTIDENTIFIER,
GIFTINKINDSALEADJUSTMENT.DATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYGIFTINKIND.CONSTITUENTNAME,
'Sold gift-in-kind' as [REVENUETYPE],
ADJUSTMENTHISTORYGIFTINKIND.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
GIFTINKINDSALEADJUSTMENT.POSTSTATUS,
GIFTINKINDSALEADJUSTMENT.POSTDATE,
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(GIFTINKINDSALEADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYGIFTINKIND.DATEADDED
from dbo.ADJUSTMENTHISTORYGIFTINKIND
left join dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYGIFTINKINDID = ADJUSTMENTHISTORYGIFTINKIND.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETGIFTINKINDDISTRIBUTION_FORADJUSTMENTHISTORYGIFTINKINDID_FORDISPLAY(ADJUSTMENTHISTORYGIFTINKIND.ID) as [DISTRIBUTION]
inner join dbo.GIFTINKINDSALEADJUSTMENT on GIFTINKINDSALEADJUSTMENT.ID = ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDSALEADJUSTMENTID /*this join means this select only works for non-deleted adjustments*/
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.ID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where REVENUE.ID = @REVENUEID
union all
/*get adjustments for sold property items*/
select
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTIDENTIFIER,
PROPERTYDETAILADJUSTMENT.DATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYPROPERTY.CONSTITUENTNAME,
'Sold property' as [REVENUETYPE],
ADJUSTMENTHISTORYPROPERTY.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
PROPERTYDETAILADJUSTMENT.POSTSTATUS,
PROPERTYDETAILADJUSTMENT.POSTDATE,
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(PROPERTYDETAILADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYPROPERTY.DATEADDED
from dbo.ADJUSTMENTHISTORYPROPERTY
left join dbo.ADJUSTMENTHISTORYPROPERTYDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYPROPERTYID = ADJUSTMENTHISTORYPROPERTY.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETPROPERTYDISTRIBUTION_FORADJUSTMENTHISTORYPROPERTYID_FORDISPLAY(ADJUSTMENTHISTORYPROPERTY.ID) as [DISTRIBUTION]
inner join dbo.PROPERTYDETAILADJUSTMENT on PROPERTYDETAILADJUSTMENT.ID = ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILADJUSTMENTID /*this join means this select only works for non-deleted adjustments*/
inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where REVENUE.ID = @REVENUEID
union all
/*get adjustments for deleted stock items */
select
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYSTOCK.CONSTITUENTNAME,
'Sold stock' as [REVENUETYPE],
ADJUSTMENTHISTORYSTOCK.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
/*if this is the record for a deletion reversal, there's no way to know if it was posted or not.
Otherwise, assume that it's posted since it's purely historical*/
case
when [DETAIL].ADJUSTEDFIELD = 'Deleted' or [DETAIL].ADJUSTEDFIELD = 'Unsold'
then
case when not ADJUSTMENTHISTORYSTOCK.GLTRANSACTIONID is null then
(select POSTSTATUS from dbo.GLTRANSACTION where ID = ADJUSTMENTHISTORYSTOCK.GLTRANSACTIONID)
else
'N/A'
end
else
'Posted'
end,
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTPOSTDATE, --Use the post date stored in the history table
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYSTOCK.DATEADDED
from dbo.ADJUSTMENTHISTORYSTOCK
/*this needs to be a left join because adjustments consisting of just GL changes will not have changed fields*/
left join dbo.ADJUSTMENTHISTORYSTOCKDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYSTOCKID = ADJUSTMENTHISTORYSTOCK.ID
inner join dbo.REVENUEPAYMENTMETHOD on ADJUSTMENTHISTORYSTOCK.STOCKDETAILIDENTIFIER = cast(REVENUEPAYMENTMETHOD.ID as nvarchar(36))
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETSTOCKDISTRIBUTION_FORADJUSTMENTHISTORYSTOCKID_FORDISPLAY(ADJUSTMENTHISTORYSTOCK.ID) as [DISTRIBUTION]
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where
ADJUSTMENTHISTORYSTOCK.STOCKSALEADJUSTMENTID is null
and REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
union all
/*get adjustments for deleted gift-in-kind items */
select
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYGIFTINKIND.CONSTITUENTNAME,
'Sold gift-in-kind' as [REVENUETYPE],
ADJUSTMENTHISTORYGIFTINKIND.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
/*if this is the record for a deletion reversal, there's no way to know if it was posted or not.
Otherwise, assume that it's posted since it's purely historical*/
case
when [DETAIL].ADJUSTEDFIELD = 'Deleted' or [DETAIL].ADJUSTEDFIELD = 'Unsold'
then
case when not ADJUSTMENTHISTORYGIFTINKIND.GLTRANSACTIONID is null then
(select POSTSTATUS from dbo.GLTRANSACTION where ID = ADJUSTMENTHISTORYGIFTINKIND.GLTRANSACTIONID)
else
'N/A'
end
else
'Posted'
end,
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTPOSTDATE, --Use the post date stored in the history table
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYGIFTINKIND.DATEADDED
from dbo.ADJUSTMENTHISTORYGIFTINKIND
/*this needs to be a left join because adjustments consisting of just GL changes will not have changed fields*/
left join dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYGIFTINKINDID = ADJUSTMENTHISTORYGIFTINKIND.ID
inner join dbo.REVENUEPAYMENTMETHOD on ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER = cast(REVENUEPAYMENTMETHOD.ID as nvarchar(36))
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETGIFTINKINDDISTRIBUTION_FORADJUSTMENTHISTORYGIFTINKINDID_FORDISPLAY(ADJUSTMENTHISTORYGIFTINKIND.ID) as [DISTRIBUTION]
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where
ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDSALEADJUSTMENTID is null
and REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
union all
/*get adjustments for deleted property items */
select
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE as [ADJUSTMENTDATE],
ADJUSTMENTHISTORYPROPERTY.CONSTITUENTNAME,
'Sold property' as [REVENUETYPE],
ADJUSTMENTHISTORYPROPERTY.REVENUEDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
/*if this is the record for a deletion reversal, there's no way to know if it was posted or not.
Otherwise, assume that it's posted since it's purely historical*/
case
when [DETAIL].ADJUSTEDFIELD = 'Deleted' or [DETAIL].ADJUSTEDFIELD = 'Unsold'
then
case when not ADJUSTMENTHISTORYPROPERTY.GLTRANSACTIONID is null then
(select POSTSTATUS from dbo.GLTRANSACTION where ID = ADJUSTMENTHISTORYPROPERTY.GLTRANSACTIONID)
else
'N/A'
end
else
'Posted'
end,
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTPOSTDATE, --Use the post date stored in the history table
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYPROPERTY.DATEADDED
from dbo.ADJUSTMENTHISTORYPROPERTY
/*this needs to be a left join because adjustments consisting of just GL changes will not have changed fields*/
left join dbo.ADJUSTMENTHISTORYPROPERTYDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYPROPERTYID = ADJUSTMENTHISTORYPROPERTY.ID
inner join dbo.REVENUEPAYMENTMETHOD on ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILIDENTIFIER = cast(REVENUEPAYMENTMETHOD.ID as nvarchar(36))
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETPROPERTYDISTRIBUTION_FORADJUSTMENTHISTORYPROPERTYID_FORDISPLAY(ADJUSTMENTHISTORYPROPERTY.ID) as [DISTRIBUTION]
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where
ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILADJUSTMENTID is null
and REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
union all
/* Write-offs */
select
ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTIDENTIFIER,
WRITEOFFADJUSTMENT.DATE as [ADJUSTMENTDATE],
--ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER as [TRANSACTIONIDENTIFIER],
--ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYWRITEOFF.CONSTITUENTNAME,
'Write-off' as [REVENUETYPE],
ADJUSTMENTHISTORYWRITEOFF.DATE as REVENUEDATE,
--ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE,
[DETAIL].ID as [DETAILID],
[DETAIL].ADJUSTEDFIELD,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE end as ADJUSTEDFIELDPREVIOUSVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE end as ADJUSTEDFIELDADJUSTEDVALUE,
ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTREASON,
cast([DISTRIBUTION].ID as nvarchar(36)) as [DISTRIBUTIONID],
[DISTRIBUTION].TYPECODE as [GLTYPECODE],
[DISTRIBUTION].TYPE as [GLTYPE],
[DISTRIBUTION].TRANSACTIONTYPE,
[DISTRIBUTION].ACCOUNT,
[DISTRIBUTION].PROJECT,
case @CURRENCYCODE when 0 then [DISTRIBUTION].AMOUNT when 2 then [DISTRIBUTION].TRANSACTIONAMOUNT else [DISTRIBUTION].ORGANIZATIONAMOUNT end as AMOUNT,
WRITEOFFADJUSTMENT.POSTSTATUS,
WRITEOFFADJUSTMENT.POSTDATE,
case @CURRENCYCODE when 0 then REVENUE.AMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGANIZATIONAMOUNT end as [REVENUEAMOUNT],
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDPREVIOUSMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE end as ADJUSTEDFIELDPREVIOUSMONEYVALUE,
case @CURRENCYCODE when 0 then [DETAIL].ADJUSTEDFIELDADJUSTEDMONEYVALUE when 2 then [DETAIL].TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE else [DETAIL].ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE end as ADJUSTEDFIELDADJUSTEDMONEYVALUE,
DETAILCURRENCYPROPERTIES.ISO4217 [DETAILISOCURRENCYCODE],
DETAILCURRENCYPROPERTIES.CURRENCYSYMBOL [DETAILCURRENCYSYMBOL],
DETAILCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE],
DETAILCURRENCYPROPERTIES.DECIMALDIGITS [DETAILDECIMALDIGITS],
DISTRIBUTIONCURRENCYPROPERTIES.ISO4217 [DISTRIBUTIONISOCURRENCYCODE],
DISTRIBUTIONCURRENCYPROPERTIES.CURRENCYSYMBOL [DISTRIBUTIONCURRENCYSYMBOL],
DISTRIBUTIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE],
DISTRIBUTIONCURRENCYPROPERTIES.DECIMALDIGITS [DISTRIBUTIONDECIMALDIGITS]
,ADJUSTMENTHISTORYWRITEOFF.DATEADDED
from dbo.ADJUSTMENTHISTORYWRITEOFF
left join dbo.ADJUSTMENTHISTORYWRITEOFFDETAIL as [DETAIL] on [DETAIL].ADJUSTMENTHISTORYWRITEOFFID = ADJUSTMENTHISTORYWRITEOFF.ID
cross apply dbo.UFN_ADJUSTMENTHISTORY_GETWRITEOFFDISTRIBUTION_FORADJUSTMENTHISTORYWRITEOFFID_FORDISPLAY(ADJUSTMENTHISTORYWRITEOFF.ID) as [DISTRIBUTION]
inner join dbo.WRITEOFFADJUSTMENT on WRITEOFFADJUSTMENT.ID = ADJUSTMENTHISTORYWRITEOFF.WRITEOFFADJUSTMENTID/*this join means this select only works for non-deleted adjustments*/
inner join dbo.WRITEOFF on WRITEOFFADJUSTMENT.WRITEOFFID = WRITEOFF.ID
inner join dbo.REVENUE on WRITEOFF.REVENUEID = REVENUE.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DISTRIBUTION].BASECURRENCYID when 2 then [DISTRIBUTION].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DISTRIBUTIONCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then [DETAIL].BASECURRENCYID when 2 then [DETAIL].TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) DETAILCURRENCYPROPERTIES
where REVENUE.ID = @REVENUEID
order by
REVENUETYPE, ADJUSTMENTDATE desc, DATEADDED desc;