USP_REPORT_ADJUSTEDREVENUE
Returns the data necessary for the Adjusted Revenue report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_ADJUSTEDREVENUE
(
@STARTDATE datetime,
@ENDDATE datetime,
@CURRENCYCODE tinyint = null,
@REPORTUSERID nvarchar(128) = null,
@ALTREPORTUSERID nvarchar(128) = null )
with execute as owner
as
set nocount on;
set transaction isolation level read uncommitted;
declare @CURRENTAPPUSERID uniqueidentifier;
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @NOSECURITYREQUIRED bit = coalesce((select DFTC.NOSECURITYREQUIRED from dbo.DATAFORMINSTANCECATALOG DFIC inner join dbo.DATAFORMTEMPLATECATALOG DFTC on DFIC.DATAFORMTEMPLATECATALOGID = DFTC.ID where DFIC.ID = 'BE5252A8-1D7F-4F34-A319-B024B06C0A03'),0);
declare @CHECKSITE bit = 0;
if @ISSYSADMIN = 0 and @NOSECURITYREQUIRED = 0 and exists (select top 1 1 from dbo.SITE)
set @CHECKSITE = 1;
if @CHECKSITE = 1
begin
if object_id('tempdb..#TMP_USP_REPORT_ADJUSTEDREVENUE_SITEONFEATURE') is not null
drop table #TMP_USP_REPORT_ADJUSTEDREVENUE_SITEONFEATURE
create table #TMP_USP_REPORT_ADJUSTEDREVENUE_SITEONFEATURE
(
SITEID uniqueidentifier
)
insert into #TMP_USP_REPORT_ADJUSTEDREVENUE_SITEONFEATURE (SITEID)
select
SITEID
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, 'BE5252A8-1D7F-4F34-A319-B024B06C0A03', 21) --21 is the feature type for reports
end
declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
declare @STARTENDDATEEXIST tinyint = 0;
-- 0: Neither start nor end date exist
-- 1: Only start date exists
-- 2: Only end date exists
-- 3: Both start and end date exist
if @STARTDATEEARLIEST is not null
begin
set @STARTENDDATEEXIST = 1;
if @ENDDATELATEST is not null
begin
set @STARTENDDATEEXIST = 3;
end
end
else if @ENDDATELATEST is not null
begin
set @STARTDATEEARLIEST = 2;
end
if object_id('tempdb..#TMP_DATA_ADJUSTEDREVENUE') is not null
drop table #TMP_DATA_ADJUSTEDREVENUE;
create table #TMP_DATA_ADJUSTEDREVENUE
(
ADJUSTMENTIDENTIFIER nvarchar(36) collate database_default
,TRANSACTIONIDENTIFIER nvarchar(36) collate database_default
,REVENUEIDENTIFIER nvarchar(36) collate database_default
,CONSTITUENTNAME nvarchar(255) collate database_default
,REVENUETYPE nvarchar(30) collate database_default
,REVENUEDATE datetime
,ADJUSTMENTDATE datetime
,DETAILID uniqueidentifier
,ADJUSTEDFIELD nvarchar(100) collate database_default
,ADJUSTEDFIELDPREVIOUSVALUE nvarchar(max) collate database_default
,ADJUSTEDFIELDADJUSTEDVALUE nvarchar(max) collate database_default
,ADJUSTMENTREASON nvarchar(300) collate database_default
,DISTRIBUTIONID nvarchar(36) collate database_default
,GLTYPECODE tinyint
,GLTYPE nvarchar(20) collate database_default
,TRANSACTIONTYPE nvarchar(50) collate database_default
,ACCOUNT nvarchar(100) collate database_default
,PROJECT nvarchar(100) collate database_default
,AMOUNT money
,POSTSTATUS nvarchar(50) collate database_default
,POSTDATE datetime
,REASONCODE nvarchar(63) collate database_default
,ISMONEY bit
,ADJUSTEDFIELDPREVIOUSMONEYVALUE money
,ADJUSTEDFIELDADJUSTEDMONEYVALUE money
,DETAILISOCURRENCYCODE nvarchar(3) collate database_default
,DETAILCURRENCYSYMBOL nvarchar(5) collate database_default
,DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint
,DETAILDECIMALDIGITS tinyint
,DISTRIBUTIONISOCURRENCYCODE nvarchar(3) collate database_default
,DISTRIBUTIONCURRENCYSYMBOL nvarchar(5) collate database_default
,DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint
,DISTRIBUTIONDECIMALDIGITS tinyint
)
declare @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL as nvarchar(max)
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL =
'insert into #TMP_DATA_ADJUSTEDREVENUE
select
ADJUSTMENTHISTORY.ADJUSTMENTIDENTIFIER,
null as TRANSACTIONIDENTIFIER,
ADJUSTMENTHISTORY.REVENUEIDENTIFIER,
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,
ADJUSTMENTHISTORY.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,
ADJUSTMENTHISTORY.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,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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*/
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 ' +
case @STARTENDDATEEXIST
when 1 then 'where ADJUSTMENTHISTORY.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'where ADJUSTMENTHISTORY.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'where ADJUSTMENTHISTORY.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORY.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for sold stock items*/
select
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYSTOCK.STOCKDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYSTOCK.STOCKDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYSTOCK.CONSTITUENTNAME,
''Sold stock'' as [REVENUETYPE],
ADJUSTMENTHISTORYSTOCK.REVENUEDATE,
ADJUSTMENTHISTORYSTOCK.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,
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,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(STOCKSALEADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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*/
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 ' +
case @STARTENDDATEEXIST
when 1 then 'where ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'where ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'where ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for sold gift-in-kind items*/
select
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYGIFTINKIND.CONSTITUENTNAME,
''Sold gift-in-kind'' as [REVENUETYPE],
ADJUSTMENTHISTORYGIFTINKIND.REVENUEDATE,
ADJUSTMENTHISTORYGIFTINKIND.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,
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,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(GIFTINKINDSALEADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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*/
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 ' +
case @STARTENDDATEEXIST
when 1 then 'where ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'where ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'where ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for sold property items*/
select
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYPROPERTY.CONSTITUENTNAME,
''Sold property'' as [REVENUETYPE],
ADJUSTMENTHISTORYPROPERTY.REVENUEDATE,
ADJUSTMENTHISTORYPROPERTY.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,
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,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(PROPERTYDETAILADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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*/
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 ' +
case @STARTENDDATEEXIST
when 1 then 'where ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'where ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'where ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for deleted stock items */
select
ADJUSTMENTHISTORYSTOCK.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYSTOCK.STOCKDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYSTOCK.STOCKDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYSTOCK.CONSTITUENTNAME,
''Sold stock'' as [REVENUETYPE],
ADJUSTMENTHISTORYSTOCK.REVENUEDATE,
ADJUSTMENTHISTORYSTOCK.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,
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
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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
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 /*STOCKSALEADJUSTMENTID is set to null when the revenue is deleted*/ ' +
case @STARTENDDATEEXIST
when 1 then 'and ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'and ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'and ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for deleted gift-in-kind items */
select
ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYGIFTINKIND.GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYGIFTINKIND.CONSTITUENTNAME,
''Sold gift-in-kind'' as [REVENUETYPE],
ADJUSTMENTHISTORYGIFTINKIND.REVENUEDATE,
ADJUSTMENTHISTORYGIFTINKIND.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,
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
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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
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 /*GIFTINKINDSALEADJUSTMENTID is set to null when the revenue is deleted*/ ' +
case @STARTENDDATEEXIST
when 1 then 'and ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'and ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'and ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYGIFTINKIND.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for deleted property items */
select
ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYPROPERTY.PROPERTYDETAILIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYPROPERTY.CONSTITUENTNAME,
''Sold property'' as [REVENUETYPE],
ADJUSTMENTHISTORYPROPERTY.REVENUEDATE,
ADJUSTMENTHISTORYPROPERTY.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,
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
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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
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 /*PROPERTYDETAILADJUSTMENTID is set to null when the revenue is deleted*/ ' +
case @STARTENDDATEEXIST
when 1 then 'and ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'and ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'and ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYPROPERTY.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for write-off items*/
select
ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER as [TRANSACTIONIDENTIFIER],
ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER as [REVENUEIDENTIFIER],
ADJUSTMENTHISTORYWRITEOFF.CONSTITUENTNAME,
''Write-off'' as [REVENUETYPE],
ADJUSTMENTHISTORYWRITEOFF.DATE,
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,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(WRITEOFFADJUSTMENT.REASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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*/
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 ' +
case @STARTENDDATEEXIST
when 1 then 'where ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'where ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'where ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get reversals for deleted write-offs */
select
ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTIDENTIFIER,
ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER,
ADJUSTMENTHISTORYWRITEOFF.WRITEOFFIDENTIFIER,
ADJUSTMENTHISTORYWRITEOFF.CONSTITUENTNAME,
''Write-off'' as [REVENUETYPE],
ADJUSTMENTHISTORYWRITEOFF.DATE,
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,
case
when [DETAIL].ADJUSTEDFIELD = ''Deleted write-off''
then
case when not ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID is null then
(select POSTSTATUS from dbo.GLTRANSACTION where ID = ADJUSTMENTHISTORYWRITEOFF.GLTRANSACTIONID)
else
''N/A''
end
else
''Posted''
end,
ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTPOSTDATE,
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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]
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
ADJUSTMENTHISTORYWRITEOFF.WRITEOFFADJUSTMENTID is null ' +
case @STARTENDDATEEXIST
when 1 then 'and ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'and ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'and ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORYWRITEOFF.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'union all
/*get adjustments for deleted items */
select
ADJUSTMENTHISTORY.ADJUSTMENTIDENTIFIER,
null as TRANSACTIONIDENTIFIER,
ADJUSTMENTHISTORY.REVENUEIDENTIFIER,
ADJUSTMENTHISTORY.CONSTITUENTNAME,
ADJUSTMENTHISTORY.REVENUETYPE,
ADJUSTMENTHISTORY.REVENUEDATE,
ADJUSTMENTHISTORY.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,
ADJUSTMENTHISTORY.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''
then
case when not ADJUSTMENTHISTORY.GLTRANSACTIONID is null then
(select POSTSTATUS from dbo.GLTRANSACTION where ID = ADJUSTMENTHISTORY.GLTRANSACTIONID)
else
''N/A''
end
else
''Posted''
end,
ADJUSTMENTHISTORY.ADJUSTMENTPOSTDATE, --Use the post date stored in the history table
dbo.UFN_ADJUSTMENTREASONCODE_TRANSLATE(ADJUSTMENTHISTORY.ADJUSTMENTREASONCODEID) as REASONCODE,
coalesce([DETAIL].ISMONEY, 0) as ISMONEY,'
set @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL = @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL +
'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]
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]
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.ADJUSTMENTID is null /*ADJUSTMENTID is set to null when the revenue is deleted */ ' +
case @STARTENDDATEEXIST
when 1 then 'and ADJUSTMENTHISTORY.ADJUSTMENTDATE >= @STARTDATEEARLIEST '
when 2 then 'and ADJUSTMENTHISTORY.ADJUSTMENTDATE <= @ENDDATELATEST '
when 3 then 'and ADJUSTMENTHISTORY.ADJUSTMENTDATE >= @STARTDATEEARLIEST and ADJUSTMENTHISTORY.ADJUSTMENTDATE <= @ENDDATELATEST '
else ''
end
+
'option(recompile);'
exec sp_executesql @POPULATE_TMP_DATA_ADJUSTEDREVENUE_SQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CURRENCYCODE tinyint',
@STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CURRENCYCODE=@CURRENCYCODE;
if @CHECKSITE = 1
begin
delete R
from #TMP_DATA_ADJUSTEDREVENUE R
where
not exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUEID(R.REVENUEIDENTIFIER) REVSITES
inner join #TMP_USP_REPORT_ADJUSTEDREVENUE_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, '00000000-0000-0000-0000-000000000000') = isnull(REVSITES.SITEID, '00000000-0000-0000-0000-000000000000'))
end
select
ADJUSTMENTIDENTIFIER
,TRANSACTIONIDENTIFIER
,REVENUEIDENTIFIER
,CONSTITUENTNAME
,REVENUETYPE
,REVENUEDATE
,ADJUSTMENTDATE
,DETAILID
,ADJUSTEDFIELD
,ADJUSTEDFIELDPREVIOUSVALUE
,ADJUSTEDFIELDADJUSTEDVALUE
,ADJUSTMENTREASON
,DISTRIBUTIONID
,GLTYPECODE
,GLTYPE
,TRANSACTIONTYPE
,ACCOUNT
,PROJECT
,AMOUNT
,POSTSTATUS
,POSTDATE
,REASONCODE
,ISMONEY
,ADJUSTEDFIELDPREVIOUSMONEYVALUE
,ADJUSTEDFIELDADJUSTEDMONEYVALUE
,DETAILISOCURRENCYCODE
,DETAILCURRENCYSYMBOL
,DETAILCURRENCYSYMBOLDISPLAYSETTINGCODE
,DETAILDECIMALDIGITS
,DISTRIBUTIONISOCURRENCYCODE
,DISTRIBUTIONCURRENCYSYMBOL
,DISTRIBUTIONCURRENCYSYMBOLDISPLAYSETTINGCODE
,DISTRIBUTIONDECIMALDIGITS
from #TMP_DATA_ADJUSTEDREVENUE
order by ADJUSTMENTDATE, CONSTITUENTNAME, REVENUEDATE, REVENUEIDENTIFIER, [DISTRIBUTIONID]