UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE
Generates GL Account Code 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 |
Definition
Copy
CREATE function [dbo].[UFN_REVENUE_GENERATEGLDISTRIBUTION_FORSINGLEREVENUE](@REVENUEID uniqueidentifier)
returns @DISTRIBUTIONS TABLE
(
REVENUEID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID 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,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
MAPPEDVALUES xml
)
as begin
declare @TRANSACTIONTYPECODE tinyint;
declare @PAYMENTMETHODCODE tinyint;
declare @PAYMENTMETHOD nvarchar(100);
declare @REVENUEPAYMENTMETHODID uniqueidentifier;
declare @POSTDATE date;
declare @POSTSTATUSCODE tinyint;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @ORGEXCHANGERATEID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @HASEVENTS bit = 0;
declare @HASMEMBERSHIP bit = 0;
declare @HASPLEDGEPAY bit = 0;
declare @hasOther bit = 0;
declare @USINGBASICGL bit = dbo.UFN_VALID_BASICGL_INSTALLED();
select
@TRANSACTIONTYPECODE = REVENUE.TYPECODE,
@POSTDATE = REVENUE.POSTDATE,
@POSTSTATUSCODE = REVENUE.POSTSTATUSCODE,
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = V.BASECURRENCYID,
@BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
@ORGEXCHANGERATEID = REVENUE.ORGEXCHANGERATEID,
@PAYMENTMETHODCODE = PAYMENTMETHODCODE,
@PAYMENTMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
@REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID
from dbo.FINANCIALTRANSACTION as REVENUE with (nolock)
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on
REVENUE.ID = V.FINANCIALTRANSACTIONID
left join dbo.REVENUEPAYMENTMETHOD with (nolock) on
REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
where REVENUE.ID = @REVENUEID;
-- Pledge/Membership installment
if @TRANSACTIONTYPECODE in (1, 15)
begin
/*
We need special handling for Pledge and Membership Installment Plans. Their installments can be paid with various payment methods (e.g. 'credit card', 'debit card' or 'none').
However, the the pledge/installment plan itself should get mapped to the 'None' payment method code (9 and sometimes 99 for pledge--read the next line). This is their appropriate mapping in the GLPAYMENTMETHODREVENUETYPEMAPPING table.
Also, under BasicGL for Pledge: We need to differentiate Inside vs Outside Current Fiscal Year. These correspond to payment method code 9 (Inside) and 99 (Outside).
*/
declare @INSTALLMENTTYPE nvarchar(30)
if @TRANSACTIONTYPECODE = 1 -- Pledge
set @INSTALLMENTTYPE = 'Pledge'
else
set @INSTALLMENTTYPE = 'Membership installment plan'
if @USINGBASICGL = 0 or @TRANSACTIONTYPECODE = 15 -- Membership Installment
begin
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select
REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case
when ADJUSTMENT.ID is null then @POSTDATE -- same reason as above [Where? - cr, 4/28/2014]
else ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then (
case
when @POSTSTATUSCODE = 3 then 2
when @POSTSTATUSCODE = 2 then 0
else 1
end
) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'None', @INSTALLMENTTYPE) as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
9,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT as ORGANIZATIONAMOUNT,
@ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.ADJUSTMENT with (nolock) on
REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and
ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 9, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE != 1
and REVENUESPLIT.POSTSTATUSCODE!=2
end
else
begin
declare @FISCALYEARID uniqueidentifier;
select @FISCALYEARID = GLFISCALYEARID
from dbo.GLFISCALPERIOD
where dbo.UFN_DATE_GETEARLIESTTIME(@POSTDATE) between STARTDATE and ENDDATE;
--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
if dbo.UFN_INSTALLMENT_CURRENTYEAR(@REVENUEID, @FISCALYEARID, 1) = 1
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then @POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when @POSTSTATUSCODE = 3 -- Do Not Post
then 2
when @POSTSTATUSCODE = 2 -- Posted
then 0
else 1 -- Not Posted
end
) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
(
select
sum(INSTALLMENTSPLIT.AMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on
INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as AMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'None', 'Pledge') as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
9, -- None?
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
(
select
sum(INSTALLMENTSPLIT.TRANSACTIONAMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
(
select
sum(INSTALLMENTSPLIT.ORGANIZATIONAMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as ORGANIZATIONAMOUNT,
@ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT
on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.ADJUSTMENT with (nolock)
on REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 9, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and -- Reversal
@TRANSACTIONTYPECODE = 1 and -- Pledge
REVENUESPLIT.POSTSTATUSCODE != 2 and -- Posted
exists
(
select *
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on
INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID and
REVENUESPLIT_EXT.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 )
)
--special handling for Pledge
if dbo.UFN_INSTALLMENT_CURRENTYEAR(@REVENUEID, @FISCALYEARID, 0) = 1
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(9, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
@POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(case when @POSTSTATUSCODE = 3
then
2
when @POSTSTATUSCODE = 2
then
0
else
1
end
) -- same reason as above.
else
ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
(
select
sum(INSTALLMENTSPLIT.AMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as AMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, 'None', 'Pledge') as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
9, -- None?
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
(
select
sum(INSTALLMENTSPLIT.TRANSACTIONAMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on
INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
(
select
sum(INSTALLMENTSPLIT.ORGANIZATIONAMOUNT)
from dbo.INSTALLMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
and REVENUESPLIT_EXT.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
)
) as ORGANIZATIONAMOUNT,
@ORGEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on
REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.ADJUSTMENT with (nolock) on
REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and
ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, 99, REVENUESPLIT_EXT.DESIGNATIONID)
where REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and
@TRANSACTIONTYPECODE = 1 and
REVENUESPLIT.POSTSTATUSCODE !=2
and exists
(
select *
from dbo.INSTALLMENT
inner join INSTALLMENTSPLIT on
INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
where
REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID and
REVENUESPLIT_EXT.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 )
)
--special handling for Pledge
end
return;
end
-- Sold stock/property
if (@PAYMENTMETHODCODE = 4 or @PAYMENTMETHODCODE = 5)
if (@PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)) or
(@PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1))
begin
/*
We need special handling for sold stock and property. When a property is sold its payment code is changed from 5 to 8
But even after selling we need to capture the unsold information and so need to pass in 4 and 5 to UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE
and UFN_REVENUE_GENERATEGLACCOUNT to get the correct journal reference and account numbers respectively.
*/
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select REVENUE.ID as REVENUEID,
case REVENUESPLIT_EXT.APPLICATIONCODE
when 1
then dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(SORP.PAYMENTMETHODCODE, REVENUE.TYPECODE, 1, 1, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) -- Event registration fee
when 5
then dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(SORP.PAYMENTMETHODCODE, REVENUE.TYPECODE, 2, 5, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) -- Membership payment
else
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(SORP.PAYMENTMETHODCODE, REVENUE.TYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE)
end as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
REVENUE.POSTDATE
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when REVENUE.POSTSTATUSCODE = 3 -- Do Not Post
then
2
when REVENUE.POSTSTATUSCODE = 2 -- Posted
then
0
else
1
end
)
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
coalesce(REVENUESPLIT.BASEAMOUNT,REVENUE.BASEAMOUNT),
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, SORP.PAYMENTMETHOD,REVENUESPLIT_EXT.APPLICATION) as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
@PAYMENTMETHODCODE,
REVENUE.TYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
V.BASECURRENCYID,
coalesce(REVENUESPLIT.TRANSACTIONAMOUNT,REVENUE.TRANSACTIONAMOUNT),
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.BASEEXCHANGERATEID,
coalesce(REVENUESPLIT.ORGAMOUNT,REVENUE.ORGAMOUNT),
REVENUE.ORGEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTION REVENUE with (nolock)
inner join dbo.REVENUE_EXT on
REVENUE.ID = REVENUE_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock) on
REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 -- Reversal
left join dbo.REVENUESPLIT_EXT on
REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on
REVENUE.ID = V.FINANCIALTRANSACTIONID
inner join
(
select ID, '4' as PAYMENTMETHODCODE, 'Stock' as PAYMENTMETHOD from dbo.STOCKDETAIL
union all
select ID, '5' as PAYMENTMETHODCODE, 'Property' as PAYMENTMETHOD from dbo.PROPERTYDETAIL
)
as SORP on
@REVENUEPAYMENTMETHODID = SORP.ID
left join dbo.ADJUSTMENT with (nolock) on
REVENUE.ID = ADJUSTMENT.REVENUEID and
ADJUSTMENT.POSTSTATUSCODE <> 0 -- Posted
cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, SORP.PAYMENTMETHODCODE, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUE.ID = @REVENUEID and
REVENUE.DELETEDON is null and
REVENUESPLIT_EXT.APPLICATIONCODE <> 2 and
REVENUESPLIT.POSTSTATUSCODE !=2 and
((@PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)) or
(@PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1))) --special handling for sold stock/property
union all
/*
Special handling for pledge payments - bookable and non-bookable
Pledge payments post different based on whether their pledges are marked as
'Do not post' or 'Not Posted'
*/
select distinct
REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(SORP.PAYMENTMETHODCODE, DETAIL.REVENUETRANSACTIONTYPECODE, DETAIL.SPLITTYPECODE, DETAIL.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
@POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when @POSTSTATUSCODE = 3
then
2
when @POSTSTATUSCODE = 2
then
0
else
1
end
) -- same reason as above.
else
ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, SORP.PAYMENTMETHOD, DETAIL.TYPE) as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
convert(varchar(max),[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES)
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on
REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join
(
select ID, '4' as PAYMENTMETHODCODE, 'Stock' as PAYMENTMETHOD from dbo.STOCKDETAIL
union all
select ID, '5' as PAYMENTMETHODCODE, 'Property' as PAYMENTMETHOD from dbo.PROPERTYDETAIL
)
as SORP on @REVENUEPAYMENTMETHODID = SORP.ID
inner join dbo.INSTALLMENTPAYMENT with (nolock) on
REVENUESPLIT.ID = INSTALLMENTPAYMENT.PAYMENTID
inner join dbo.FINANCIALTRANSACTION as PLEDGE with (nolock) on
INSTALLMENTPAYMENT.PLEDGEID = PLEDGE.ID and
PLEDGE.DELETEDON is null
cross apply dbo.UFN_POSTTOGLPROCESS_MAPPLEDGEPAYMENTS(PLEDGE.ID, case when PLEDGE.POSTSTATUSCODE = 3 then 2 when PLEDGE.POSTSTATUSCODE = 2 then 0 else 1 end) as DETAIL
left join dbo.ADJUSTMENT with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and
ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, DETAIL.REVENUETRANSACTIONTYPECODE,DETAIL.SPLITTYPECODE, DETAIL.APPLICATIONCODE, SORP.PAYMENTMETHODCODE, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and
((@PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)) or
(@PAYMENTMETHODCODE = 5 and exists(select ID from dbo.PROPERTYDETAIL where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1))) and --special handling for sold stock/property
(REVENUESPLIT_EXT.APPLICATIONCODE = 2) and
REVENUESPLIT.POSTSTATUSCODE != 2
return;
end
select
@HASEVENTS = case when EXT.APPLICATIONCODE = 1 then 1 else @HASEVENTS end,
@HASMEMBERSHIP = case when EXT.APPLICATIONCODE = 5 then 1 else @HASMEMBERSHIP end,
@HASPLEDGEPAY = case when EXT.APPLICATIONCODE in (2, 19) then 1 else @HASPLEDGEPAY end, --2: Pledge, 19: Membership installment plan
@hasOther = case when EXT.APPLICATIONCODE not in (1, 2, 5, 12, 19) then 1 else @hasOther end --1: Event Registration, 2: Pledge, 5: Membership, 12: Auction purchase, 19: Membership Installment Plan
from dbo.FINANCIALTRANSACTIONLINEITEM LI
inner join dbo.REVENUESPLIT_EXT EXT on LI.ID = EXT.ID
where LI.FINANCIALTRANSACTIONID = @REVENUEID and LI.DELETEDON is null and LI.TYPECODE != 1
-- Event registration payments
if (@HASEVENTS = 1)
begin
/*
We need special handling for Event Registration since they don't have Designations and have their own mapping table;
...Split TYPECODE and DESIGNATIONID are now passed in for gifts as part of an event registration.
*/
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, 1, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
@POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when @POSTSTATUSCODE = 3
then
2
when @POSTSTATUSCODE = 2
then
0
else
1
end
)
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, @PAYMENTMETHOD, REVENUESPLIT_EXT.APPLICATION) as REFERENCE,
tf.ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
tf.MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.ADJUSTMENT with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, @PAYMENTMETHODCODE, REVENUESPLIT_EXT.DESIGNATIONID) as tf
where (REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
and (REVENUESPLIT_EXT.APPLICATIONCODE = 1) --special handling for Event Registration
and REVENUESPLIT.POSTSTATUSCODE!=2
end
-- Membership payments
if (@HASMEMBERSHIP = 1)
begin
/*
We need special handling for Memberships since they don't have Designations and have their own mapping table
*/
insert into @DISTRIBUTIONS(REVENUEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TRANSACTIONTYPECODE,POSTDATE,POSTSTATUSCODE,ACCOUNTSTRING,PROJECT,AMOUNT,REFERENCE,ERRORMESSAGE,PAYMENTMETHODCODE,REVENUETRANSACTIONTYPECODE,ACCOUNTID,REVENUESPLITID,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES)
select REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, @TRANSACTIONTYPECODE, 2, 5, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null then @POSTDATE -- same reason as above
else ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null then (case when @POSTSTATUSCODE = 3 then 2 when @POSTSTATUSCODE = 2 then 0 else 1 end) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, @PAYMENTMETHOD, REVENUESPLIT_EXT.APPLICATION) as REFERENCE,
tf.ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
tf.MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.ADJUSTMENT with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, @PAYMENTMETHODCODE, null) as tf
where (REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID) and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
and (REVENUESPLIT_EXT.APPLICATIONCODE = 5) --special handling for Memberships
and REVENUESPLIT.POSTSTATUSCODE!=2
end
-- Pledge payments
if (@HASPLEDGEPAY = 1)
begin
/*
Special handling for pledge and membership installment plan payments - bookable and non-bookable
These payments post differently based on whether their pledges/plans are marked as 'Do not post' or 'Not Posted'
*/
insert into @DISTRIBUTIONS(REVENUEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,TRANSACTIONTYPECODE,POSTDATE,POSTSTATUSCODE,ACCOUNTSTRING,PROJECT,AMOUNT,REFERENCE,ERRORMESSAGE,PAYMENTMETHODCODE,REVENUETRANSACTIONTYPECODE,ACCOUNTID,REVENUESPLITID,
BASECURRENCYID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, MAPPEDVALUES)
select distinct
REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, DETAIL.REVENUETRANSACTIONTYPECODE, DETAIL.SPLITTYPECODE, DETAIL.APPLICATIONCODE, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null then @POSTDATE -- same reason as above
else ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null then (case when @POSTSTATUSCODE = 3 then 2 when @POSTSTATUSCODE = 2 then 0 else 1 end) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, @PAYMENTMETHOD, DETAIL.TYPE) as REFERENCE,
tf.ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
convert(varchar(max),tf.MAPPEDVALUES)
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.INSTALLMENTPAYMENT with (nolock) on REVENUESPLIT.ID = INSTALLMENTPAYMENT.PAYMENTID
inner join dbo.FINANCIALTRANSACTION as PLEDGE with (nolock) on INSTALLMENTPAYMENT.PLEDGEID = PLEDGE.ID and PLEDGE.DELETEDON is null
cross apply dbo.UFN_POSTTOGLPROCESS_MAPPLEDGEPAYMENTS_2 (
PLEDGE.ID,
case
when PLEDGE.POSTSTATUSCODE = 3 then 2
when PLEDGE.POSTSTATUSCODE = 2 then 0
else 1
end,
@USINGBASICGL,
PLEDGE.TYPECODE,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT_EXT.APPLICATION,
REVENUESPLIT_EXT.TYPECODE
) as DETAIL
left join dbo.ADJUSTMENT with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, @PAYMENTMETHODCODE, REVENUESPLIT_EXT.DESIGNATIONID) as tf
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and
REVENUESPLIT_EXT.APPLICATIONCODE in (2, 19) and --Pledge, Membership Installment Plan
REVENUESPLIT.POSTSTATUSCODE!=2
end
if (@TRANSACTIONTYPECODE in (0,5,6,7)) and
(@HASOTHER = 1)
begin
/* Getting 'Postable' Revenue */
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select FTLI.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, @TRANSACTIONTYPECODE, RSX.TYPECODE, RSX.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
@POSTDATE-- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case @POSTSTATUSCODE when 3
then
2
when 2
then
0
else
1
end
) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
FTLI.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (FTLI.ID, @PAYMENTMETHOD, RSX.APPLICATION) as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
FTLI.ID as REVENUESPLITID,
@BASECURRENCYID,
FTLI.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
FTLI.ORGAMOUNT,
@ORGEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock)
inner join dbo.REVENUESPLIT_EXT as RSX with (nolock) on
FTLI.ID = RSX.ID
left join dbo.ADJUSTMENT with (nolock) on
FTLI.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and
ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(FTLI.ID, @TRANSACTIONTYPECODE,RSX.TYPECODE, RSX.APPLICATIONCODE, @PAYMENTMETHODCODE, RSX.DESIGNATIONID)
where
(
FTLI.FINANCIALTRANSACTIONID = @REVENUEID) and
FTLI.TYPECODE = 0 and
FTLI.DELETEDON is NULL and
(not RSX.APPLICATIONCODE in (1,2,5,12,19)
) --special handling for Pledge, Event Registration, Membership, Auction purchase, and Membership Installment Plan
and
(
RSX.APPLICATIONCODE <> 10 or -- Order
RSX.TYPECODE <> 20 -- Overage
)
and FTLI.POSTSTATUSCODE!=2
end
if (@TRANSACTIONTYPECODE = 4) and (@USINGBASICGL = 1) and (@HASOTHER = 1)
begin
/* Getting 'Postable' Revenue */
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case when ADJUSTMENT.ID is null
then
@POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when @POSTSTATUSCODE = 3
then
2
when @POSTSTATUSCODE = 2
then
0
else
1
end
) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, @PAYMENTMETHOD, REVENUESPLIT_EXT.APPLICATION) + ' Contribution' as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on
REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.PLANNEDGIFTREVENUE with (nolock)
on REVENUESPLIT.FINANCIALTRANSACTIONID = PLANNEDGIFTREVENUE.REVENUEID
inner join dbo.PLANNEDGIFT with (nolock) on
PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.ID
left join dbo.ADJUSTMENT with (nolock) on
REVENUESPLIT.FINANCIALTRANSACTIONID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID,@TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, PLANNEDGIFT.VEHICLECODE, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and
(
not REVENUESPLIT_EXT.APPLICATIONCODE in (1,2,5,19)
) --special handling for Pledge, Event Registration, Membership, and Membership Installment Plan
and REVENUESPLIT.POSTSTATUSCODE != 2
--Additions
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
MAPPEDVALUES
)
select
REVENUESPLIT.FINANCIALTRANSACTIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(@PAYMENTMETHODCODE, @TRANSACTIONTYPECODE, REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, [UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
[UFN_REVENUE_GENERATEGLACCOUNT].TRANSACTIONTYPECODE,
case
when ADJUSTMENT.ID is null
then
@POSTDATE -- same reason as above
else
ADJUSTMENT.POSTDATE
end as POSTDATE,
case when ADJUSTMENT.ID is null
then
(
case when @POSTSTATUSCODE = 3
then
2
when @POSTSTATUSCODE = 2
then
0
else
1
end
) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTSTRING,
[UFN_REVENUE_GENERATEGLACCOUNT].PROJECTCODE as PROJECT,
REVENUESPLIT.BASEAMOUNT,
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, @PAYMENTMETHOD, REVENUESPLIT_EXT.APPLICATION) + ' Contribution' as REFERENCE,
[UFN_REVENUE_GENERATEGLACCOUNT].ERRORMESSAGE,
@PAYMENTMETHODCODE,
@TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
[UFN_REVENUE_GENERATEGLACCOUNT].ACCOUNTID as ACCOUNTID,
REVENUESPLIT.ID as REVENUESPLITID,
@BASECURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.ORGAMOUNT,
@ORGEXCHANGERATEID,
[UFN_REVENUE_GENERATEGLACCOUNT].MAPPEDVALUES
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT with (nolock)
inner join dbo.REVENUESPLIT_EXT on
REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.PLANNEDGIFTADDITIONREVENUE with (nolock) on
PLANNEDGIFTADDITIONREVENUE.REVENUEID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.PLANNEDGIFTADDITION on
PLANNEDGIFTADDITION.ID = PLANNEDGIFTADDITIONREVENUE.ID
inner join dbo.PLANNEDGIFT with (nolock) on
PLANNEDGIFT.ID = PLANNEDGIFTADDITION.PLANNEDGIFTID
left join dbo.ADJUSTMENT with (nolock) on
ADJUSTMENT.REVENUEID = REVENUESPLIT.FINANCIALTRANSACTIONID and
ADJUSTMENT.POSTSTATUSCODE <> 0
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, @TRANSACTIONTYPECODE,REVENUESPLIT_EXT.TYPECODE, REVENUESPLIT_EXT.APPLICATIONCODE, PLANNEDGIFT.VEHICLECODE, REVENUESPLIT_EXT.DESIGNATIONID)
where
REVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID and
REVENUESPLIT_EXT.APPLICATIONCODE not in (1,2,5,19) and --special handling for Pledge, Event Registration, Membership, and Membership Installment Plan
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE != 1 and
REVENUESPLIT.POSTSTATUSCODE != 2
end
return;
end