UFN_REVENUE_GENERATEUNREALIZEDGAINLOSSGLDISTRIBUTION
Generates unrealized currency gain/loss GL distributions from the account code mappings defined in the system for the given revenue record.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @POSTDATE | datetime | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUE_GENERATEUNREALIZEDGAINLOSSGLDISTRIBUTION
(
@REVENUEID uniqueidentifier,
@POSTDATE datetime = null
)
returns @DISTRIBUTIONS table
(
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
POSTDATE datetime,
POSTSTATUSCODE tinyint,
ACCOUNTSTRING nvarchar(255),
PROJECT nvarchar(255),
AMOUNT money,
REFERENCE nvarchar(255),
ERRORMESSAGE nvarchar(max),
PAYMENTMETHODCODE tinyint,
REVENUETRANSACTIONTYPECODE tinyint,
ACCOUNTID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
ORGANIZATIONAMOUNT money,
MAPPEDVALUES xml
)
as begin
declare @TRANSACTIONTYPECODE tinyint;
--If we are using legacy GL, don't return any distributions
if dbo.UFN_GLACCOUNT_EXISTS() = 0
return;
if @POSTDATE is null set @POSTDATE = getdate();
select
@TRANSACTIONTYPECODE = TRANSACTIONTYPECODE
from
dbo.REVENUE with (nolock)
where
REVENUE.ID = @REVENUEID;
--Pledge
if @TRANSACTIONTYPECODE = 1
begin
declare @FISCALYEARID uniqueidentifier = dbo.UFN_GLFISCALYEAR_GETIDFROMDATE();
declare @REVALGAINLOSS table
(
INSTALLMENTID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
BASEGAINLOSS money,
ORGANIZATIONGAINLOSS money,
ISGAIN bit
)
insert into @REVALGAINLOSS
(
INSTALLMENTID,
DESIGNATIONID,
BASEGAINLOSS,
ORGANIZATIONGAINLOSS,
ISGAIN
)
select
INSTALLMENTSPLIT.INSTALLMENTID,
INSTALLMENTSPLIT.DESIGNATIONID,
BASEGAINLOSS,
ORGANIZATIONGAINLOSS,
ISGAIN
from dbo.UFN_PLEDGE_GETINSTALLMENTSPLITREVALUATIONGAINLOSS(@REVENUEID) GAINLOSS
inner join INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = GAINLOSS.INSTALLMENTSPLITID;
--Need to handle the case where there is no fiscal year by inserting a dummy row.
--Otherwise the transaction is successfully created with no distributions
if @FISCALYEARID is null
begin
insert into @DISTRIBUTIONS
(
REVENUEID,
ERRORMESSAGE,
POSTSTATUSCODE
)
values
(
@REVENUEID,
'Post date must be in an open period.',
1
);
return;
end
-- Pledge installments inside the open period.
if dbo.UFN_INSTALLMENT_CURRENTYEAR(@REVENUEID, @FISCALYEARID, 1) = 1
begin
--Get all installments in the open period
with OPENPERIOD_INSTALLMENTS(INSTALLMENTID) as
(
select
INSTALLMENT.ID
from
dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join dbo.REVENUE on INSTALLMENT.REVENUEID = REVENUE.ID
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
where
REVENUE.ID = @REVENUEID
and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
and INSTALLMENT.DATE between
(
select min(STARTDATE) from dbo.GLFISCALPERIOD where GLFISCALYEARID = @FISCALYEARID
)
and
(
select max(ENDDATE) from dbo.GLFISCALPERIOD where GLFISCALYEARID = @FISCALYEARID
)
),
GAINLOSS_CTE(DESIGNATIONID,BASEGAINLOSS,ORGANIZATIONGAINLOSS) as (
--Get the sum of the gains and losses
select
DESIGNATIONID,
sum
(
case [REVALGAINLOSS].ISGAIN
when 1 then [REVALGAINLOSS].BASEGAINLOSS
else ([REVALGAINLOSS].BASEGAINLOSS * -1)
end
) as BASEGAINLOSS
,
sum
(
case [REVALGAINLOSS].ISGAIN
when 1 then [REVALGAINLOSS].ORGANIZATIONGAINLOSS
else ([REVALGAINLOSS].ORGANIZATIONGAINLOSS * -1)
end
) as ORGANIZATIONGAINLOSS
from @REVALGAINLOSS [REVALGAINLOSS]
where
[REVALGAINLOSS].INSTALLMENTID in
(
select INSTALLMENTID from OPENPERIOD_INSTALLMENTS
)
--If the installment has a gain in base amount and loss in org amount (or vice versa), we need to return 2 rows.
--Otherwise, return the gains or losses on the same row.
group by
[REVALGAINLOSS].DESIGNATIONID, [REVALGAINLOSS].ISGAIN
)
insert into @DISTRIBUTIONS
(
REVENUEID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
MAPPEDVALUES
)
select
REVENUE.ID,
--The currency gain/loss account is always stored as the debit in PDACCOUNTCODEMAPPING.
--If we have a currency gain, we need to credit the gain/loss account and debit the receivable account,
--thus the need to switch the TRANSACTIONTYPECODE below.
case when (GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0)
then
case [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
when 1 then 0 else 1
end
else [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
end as TRANSACTIONTYPECODE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then @POSTDATE
else UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
end as POSTDATE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then
(
case when REVENUE.DONOTPOST = 1
then 2
when exists (select 1 from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID)
then 0 -- Posted
else
1 -- Not posted
end
)
else
UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[GAINLOSSGLACCOUNT].ACCOUNTSTRING,
[GAINLOSSGLACCOUNT].PROJECTCODE as PROJECT,
abs(GAINLOSS_CTE.BASEGAINLOSS) as AMOUNT,
case
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ((GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0))
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency gain', CODES.REVENUETYPE)
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and not ((GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0))
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency loss', CODES.REVENUETYPE)
else
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Commitment revaluation', CODES.REVENUETYPE)
end as REFERENCE,
[GAINLOSSGLACCOUNT].ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, -- TODO: PAYMENTMETHOD?????
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[GAINLOSSGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.BASECURRENCYID,
abs(GAINLOSS_CTE.ORGANIZATIONGAINLOSS) as ORGANIZATIONAMOUNT,
GAINLOSSGLACCOUNT.MAPPEDVALUES
from
dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
left join dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) on REVENUE.ID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE <> 0
inner join GAINLOSS_CTE on GAINLOSS_CTE.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
cross join
(
select '207' as PAYMENTMETHODCODE, 'Currency gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '205' as REVENUETYPECODE
union all
select '208' as PAYMENTMETHODCODE, 'Currency loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '205' as REVENUETYPECODE
) as CODES
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as [GAINLOSSGLACCOUNT]
where
REVENUE.ID = @REVENUEID
and REVENUE.TRANSACTIONTYPECODE = 1
and REVENUE.DONOTPOST = 0
and not (GAINLOSS_CTE.BASEGAINLOSS = 0 and GAINLOSS_CTE.ORGANIZATIONGAINLOSS = 0)
and(
(CODES.PAYMENTMETHODCODE = 207 and (GAINLOSS_CTE.BASEGAINLOSS > 0 or GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0)) -- Currency Gain
or
(CODES.PAYMENTMETHODCODE = 208 and (GAINLOSS_CTE.BASEGAINLOSS < 0 or GAINLOSS_CTE.ORGANIZATIONGAINLOSS < 0)) -- Currency Loss
)
end
-- Pledge installments outside the open period.
if dbo.UFN_INSTALLMENT_CURRENTYEAR(@REVENUEID, @FISCALYEARID, 0) = 1
begin
--Get all installments outside the open period
with OUTSIDEOPENPERIOD_INSTALLMENTS(INSTALLMENTID) as
(
select
INSTALLMENT.ID
from
dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join dbo.REVENUE on INSTALLMENT.REVENUEID = REVENUE.ID
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
where
REVENUE.ID = @REVENUEID
and REVENUESPLIT.DESIGNATIONID = INSTALLMENTSPLIT.DESIGNATIONID
and INSTALLMENT.DATE not between
(
select min(STARTDATE) from dbo.GLFISCALPERIOD where GLFISCALYEARID = @FISCALYEARID
)
and
(
select max(ENDDATE) from dbo.GLFISCALPERIOD where GLFISCALYEARID = @FISCALYEARID
)
),
GAINLOSS_CTE(DESIGNATIONID,BASEGAINLOSS,ORGANIZATIONGAINLOSS) as (
--Get the sum of the gains and losses
select
DESIGNATIONID,
sum
(
case [REVALGAINLOSS].ISGAIN
when 1 then [REVALGAINLOSS].BASEGAINLOSS
else ([REVALGAINLOSS].BASEGAINLOSS * -1)
end
) as BASEGAINLOSS,
sum
(
case [REVALGAINLOSS].ISGAIN
when 1 then [REVALGAINLOSS].ORGANIZATIONGAINLOSS
else ([REVALGAINLOSS].ORGANIZATIONGAINLOSS * -1)
end
) as ORGANIZATIONGAINLOSS
from
@REVALGAINLOSS [REVALGAINLOSS]
where
[REVALGAINLOSS].INSTALLMENTID in
(
select INSTALLMENTID from OUTSIDEOPENPERIOD_INSTALLMENTS
)
--If the installment has a gain in base amount and loss in org amount (or vice versa), we need to return 2 rows.
--Otherwise, return the gains or losses on the same row.
group by
[REVALGAINLOSS].DESIGNATIONID,[REVALGAINLOSS].ISGAIN
)
insert into @DISTRIBUTIONS
(
REVENUEID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
MAPPEDVALUES
)
select
REVENUE.ID,
--The currency gain/loss account is always stored as the debit in PDACCOUNTCODEMAPPING.
--If we have a currency gain, we need to credit the gain/loss account and debit the receivable account,
--thus the need to switch the TRANSACTIONTYPECODE below.
case when (GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0)
then
case [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
when 1 then 0 else 1
end
else [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
end as TRANSACTIONTYPECODE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then @POSTDATE
else UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
end as POSTDATE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then
(
case when REVENUE.DONOTPOST = 1
then 2
when exists (select 1 from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID)
then 0 -- Posted
else
1 -- Not posted
end
)
else
UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[GAINLOSSGLACCOUNT].ACCOUNTSTRING,
[GAINLOSSGLACCOUNT].PROJECTCODE as PROJECT,
abs(GAINLOSS_CTE.BASEGAINLOSS) as AMOUNT,
case
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ((GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0))
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency gain', CODES.REVENUETYPE)
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and not ((GAINLOSS_CTE.BASEGAINLOSS > 0) or (GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0))
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency loss', CODES.REVENUETYPE)
else
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Commitment revaluation', CODES.REVENUETYPE)
end as REFERENCE,
[GAINLOSSGLACCOUNT].ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, -- TODO: PAYMENTMETHOD?????
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[GAINLOSSGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.BASECURRENCYID,
abs(GAINLOSS_CTE.ORGANIZATIONGAINLOSS) as ORGANIZATIONAMOUNT,
GAINLOSSGLACCOUNT.MAPPEDVALUES
from
dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
left join dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) on REVENUE.ID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE <> 0
inner join GAINLOSS_CTE on GAINLOSS_CTE.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
cross join
(
select '207' as PAYMENTMETHODCODE, 'Currency gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '206' as REVENUETYPECODE
union all
select '208' as PAYMENTMETHODCODE, 'Currency loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '206' as REVENUETYPECODE
) as CODES
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as [GAINLOSSGLACCOUNT]
where
REVENUE.ID = @REVENUEID
and REVENUE.TRANSACTIONTYPECODE = 1
and not (GAINLOSS_CTE.BASEGAINLOSS = 0 and GAINLOSS_CTE.ORGANIZATIONGAINLOSS = 0)
and
(
(CODES.PAYMENTMETHODCODE = 207 and (GAINLOSS_CTE.BASEGAINLOSS > 0 or GAINLOSS_CTE.ORGANIZATIONGAINLOSS > 0)) -- Currency Gain
or
(CODES.PAYMENTMETHODCODE = 208 and (GAINLOSS_CTE.BASEGAINLOSS < 0 or GAINLOSS_CTE.ORGANIZATIONGAINLOSS < 0)) -- Currency Loss
)
end
end
--Grant awards
if (@TRANSACTIONTYPECODE = 6)
begin
insert into @DISTRIBUTIONS
(
REVENUEID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
ORGANIZATIONAMOUNT
)
select
REVENUE.ID,
--The currency gain/loss account is always stored as the debit in PDACCOUNTCODEMAPPING.
--If we have a currency gain, we need to credit the gain/loss account and debit the receivable account,
--thus the need to switch the TRANSACTIONTYPECODE below.
case when ([REVALGAINLOSS].ISGAIN = 1)
then
case [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
when 1 then 0 else 1
end
else [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
end as TRANSACTIONTYPECODE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then @POSTDATE
else UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
end as POSTDATE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then
(
case when REVENUE.DONOTPOST = 1
then 2
when exists (select 1 from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID)
then 0 -- Posted
else
1 -- Not posted
end
)
else
UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[GAINLOSSGLACCOUNT].ACCOUNTSTRING,
[GAINLOSSGLACCOUNT].PROJECTCODE as PROJECT,
[REVALGAINLOSS].BASEGAINLOSS as AMOUNT,
case
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 1)
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency gain', CODES.REVENUETYPE)
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 0)
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency loss', CODES.REVENUETYPE)
else
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Commitment revaluation', CODES.REVENUETYPE)
end as REFERENCE,
[GAINLOSSGLACCOUNT].ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[GAINLOSSGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
REVENUESPLIT.BASECURRENCYID,
[REVALGAINLOSS].ORGANIZATIONGAINLOSS as ORGANIZATIONAMOUNT
from
dbo.REVENUE with (nolock)
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
left join dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) on REVENUE.ID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_PLEDGE_GETSPLITREVALUATIONGAINLOSS(@REVENUEID) [REVALGAINLOSS]
cross join
(
select '207' as PAYMENTMETHODCODE, 'Currency gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '207' as REVENUETYPECODE
union all
select '208' as PAYMENTMETHODCODE, 'Currency loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '207' as REVENUETYPECODE
) as CODES
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as [GAINLOSSGLACCOUNT]
where
(REVENUE.ID = @REVENUEID)
and REVALGAINLOSS.SPLITID = REVENUESPLIT.ID
and not ([REVALGAINLOSS].BASEGAINLOSS = 0 and [REVALGAINLOSS].ORGANIZATIONGAINLOSS = 0)
and
(
(CODES.PAYMENTMETHODCODE = 207 and [REVALGAINLOSS].ISGAIN = 1) -- Currency Gain
or
(CODES.PAYMENTMETHODCODE = 208 and [REVALGAINLOSS].ISGAIN = 0) -- Currency Loss
)
end
--Planned gifts
if (@TRANSACTIONTYPECODE = 4)
begin
insert into @DISTRIBUTIONS
(
REVENUEID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
ORGANIZATIONAMOUNT
)
select
REVENUE.ID as REVENUEID,
--The currency gain/loss account is always stored as the debit in PDACCOUNTCODEMAPPING.
--If we have a currency gain, we need to credit the gain/loss account and debit the receivable account,
--thus the need to switch the TRANSACTIONTYPECODE below.
case when ([REVALGAINLOSS].ISGAIN = 1)
then
case [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
when 1 then 0 else 1
end
else [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
end as TRANSACTIONTYPECODE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then @POSTDATE
else UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
end as POSTDATE,
case when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then
(
case when REVENUE.DONOTPOST = 1
then 2
when exists (select 1 from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID)
then 0 -- Posted
else
1 -- Not posted
end
)
else
UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[GAINLOSSGLACCOUNT].ACCOUNTSTRING,
[GAINLOSSGLACCOUNT].PROJECTCODE as PROJECT,
[REVALGAINLOSS].BASEGAINLOSS as AMOUNT,
case
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 1)
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency gain', CODES.REVENUETYPE)
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 0)
then dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency loss', CODES.REVENUETYPE)
else
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Commitment revaluation', CODES.REVENUETYPE)
end as REFERENCE,
[GAINLOSSGLACCOUNT].ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[GAINLOSSGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
PLANNEDGIFTDESIGNATION.BASECURRENCYID,
[REVALGAINLOSS].ORGANIZATIONGAINLOSS as ORGANIZATIONAMOUNT
from
dbo.REVENUE with (nolock)
inner join dbo.PLANNEDGIFTREVENUE with (nolock) on REVENUE.ID = PLANNEDGIFTREVENUE.REVENUEID
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PLANNEDGIFTDESIGNATION with (nolock) on PLANNEDGIFTREVENUE.ID = PLANNEDGIFTDESIGNATION.PLANNEDGIFTID and REVENUESPLIT.DESIGNATIONID = PLANNEDGIFTDESIGNATION.DESIGNATIONID
left join dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) on REVENUE.ID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_PLEDGE_GETSPLITREVALUATIONGAINLOSS(@REVENUEID) [REVALGAINLOSS]
cross join
(
select '207' as PAYMENTMETHODCODE, 'Currency gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '208' as REVENUETYPECODE
union all
select '208' as PAYMENTMETHODCODE, 'Currency loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '208' as REVENUETYPECODE
) as CODES
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as [GAINLOSSGLACCOUNT]
where
REVENUE.ID = @REVENUEID
and REVALGAINLOSS.SPLITID = REVENUESPLIT.ID
and not ([REVALGAINLOSS].BASEGAINLOSS = 0 and [REVALGAINLOSS].ORGANIZATIONGAINLOSS = 0)
and
(
(CODES.PAYMENTMETHODCODE = 207 and [REVALGAINLOSS].ISGAIN = 1) -- Currency Gain
or
(CODES.PAYMENTMETHODCODE = 208 and [REVALGAINLOSS].ISGAIN = 0) -- Currency Loss
)
--Additions
insert into @DISTRIBUTIONS
(
REVENUEID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
ORGANIZATIONAMOUNT
)
select
REVENUE.ID as REVENUEID,
--The currency gain/loss account is always stored as the debit in PDACCOUNTCODEMAPPING.
--If we have a currency gain, we need to credit the gain/loss account and debit the receivable account,
--thus the need to switch the TRANSACTIONTYPECODE below.
case
when ([REVALGAINLOSS].ISGAIN = 1) then
case [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
when 1 then 0
else 1
end
else [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE
end as TRANSACTIONTYPECODE,
case
when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then @POSTDATE
else UNREALIZEDGAINLOSSADJUSTMENT.POSTDATE
end as POSTDATE,
case
when UNREALIZEDGAINLOSSADJUSTMENT.ID is null then
(case
when REVENUE.DONOTPOST = 1 then 2
when exists (select 1 from dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION where REVENUEID = @REVENUEID) then 0 -- Posted
else 1 -- Not posted
end)
else
UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[GAINLOSSGLACCOUNT].ACCOUNTSTRING,
[GAINLOSSGLACCOUNT].PROJECTCODE as PROJECT,
[REVALGAINLOSS].BASEGAINLOSS as AMOUNT,
case
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 1) then
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency gain', CODES.REVENUETYPE)
when [GAINLOSSGLACCOUNT].TRANSACTIONTYPECODE = 0 and ([REVALGAINLOSS].ISGAIN = 0) then
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Currency loss', CODES.REVENUETYPE)
else
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'Commitment revaluation', CODES.REVENUETYPE)
end as REFERENCE,
[GAINLOSSGLACCOUNT].ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[GAINLOSSGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
PLANNEDGIFTADDITIONDESIGNATION.BASECURRENCYID,
[REVALGAINLOSS].ORGANIZATIONGAINLOSS as ORGANIZATIONAMOUNT
from
dbo.REVENUE with (nolock)
inner join
dbo.PLANNEDGIFTADDITIONREVENUE with (nolock) on PLANNEDGIFTADDITIONREVENUE.REVENUEID = REVENUE.ID
inner join
dbo.REVENUESPLIT with (nolock) on REVENUESPLIT.REVENUEID = REVENUE.ID
inner join
dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join
dbo.PLANNEDGIFTADDITIONDESIGNATION with (nolock) on PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PLANNEDGIFTADDITIONREVENUE.ID and PLANNEDGIFTADDITIONDESIGNATION.DESIGNATIONID = REVENUESPLIT.DESIGNATIONID
left join
dbo.UNREALIZEDGAINLOSSADJUSTMENT with (nolock) on REVENUE.ID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID and UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE <> 0
cross apply
dbo.UFN_PLEDGE_GETSPLITREVALUATIONGAINLOSS(@REVENUEID) [REVALGAINLOSS]
cross join
(
select '207' as PAYMENTMETHODCODE, 'Currency gain' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '208' as REVENUETYPECODE
union all
select '208' as PAYMENTMETHODCODE, 'Currency loss' AS PAYMENTMETHOD, 'All' as REVENUETYPE, '208' as REVENUETYPECODE
) as CODES
cross apply
dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.REVENUETYPECODE, CODES.PAYMENTMETHODCODE, REVENUESPLIT.DESIGNATIONID) as [GAINLOSSGLACCOUNT]
where
REVENUE.ID = @REVENUEID and
REVALGAINLOSS.SPLITID = REVENUESPLIT.ID and
not ([REVALGAINLOSS].BASEGAINLOSS = 0 and [REVALGAINLOSS].ORGANIZATIONGAINLOSS = 0) and
((CODES.PAYMENTMETHODCODE = 207 and [REVALGAINLOSS].ISGAIN = 1) or -- Currency Gain
(CODES.PAYMENTMETHODCODE = 208 and [REVALGAINLOSS].ISGAIN = 0)) -- Currency Loss
end
return;
end