UFN_POSTTOGLPROCESS_GETGLDISTRIBUTION
Gets all the GL Distribution rows for revenue record(s)
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_POSTTOGLPROCESS_GETGLDISTRIBUTION(@REVENUEID uniqueidentifier)
returns
@GLDISTRIBUTION table (
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
WRITEOFFID uniqueidentifier,
ISSOLDPROPERTYORSTOCK bit,
TRANTYPE nvarchar(50),
DEBITCREDIT nvarchar(50),
TRANDATE nvarchar(10),
POSTDATE nvarchar(10),
POSTSTATUSCODE tinyint,
ACCOUNTSTRING nvarchar(255),
PROJECT nvarchar(100),
AMOUNT money,
JOURNAL nvarchar(50),
REFERENCE nvarchar(100),
BATCH nvarchar(50),
ISREVERSAL bit,
ISADJUSTED bit,
GROUPID nvarchar(255),
ERRORMESSAGE nvarchar(255),
REVERSALID uniqueidentifier
)
as
begin
/*
Get the GL Distribution rows for Revenue record from RevenueGLDistribution table
*/
declare @TRANTYPE varchar(1);
declare @JOURNAL varchar(25);
declare @BATCH varchar(10);
set @TRANTYPE = 'R';
set @JOURNAL = 'Blackbaud Enterprise';
set @BATCH = '';
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select DETAIL.REVENUEID,
DETAIL.ID as REVENUESPLITID,
DETAIL.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
0 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
DETAIL.TRANTYPE as DEBITCREDIT,
case when ADJUSTMENT.ID is null then convert(varchar(10),REVENUE.DATE,101) --if unposted adjustment exists we want to use it's date for post process else just use Revenue Date
else convert(varchar(10), ADJUSTMENT.DATE, 101)
end as TRANDATE,
case when ADJUSTMENT.ID is null then convert(varchar(10), REVENUE.POSTDATE, 101) -- same reason as above
else convert(varchar(10), ADJUSTMENT.POSTDATE, 101)
end as POSTDATE,
case when ADJUSTMENT.ID is null then (case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end) -- same reason as above.
else ADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
DETAIL.ACCOUNT,
DETAIL.PROJECT,
DETAIL.AMOUNT,
@JOURNAL as JOURNAL,
DETAIL.REFERENCE as REFERENCE,
@BATCH as BATCH,
0 as ISREVERSAL,
case when ADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED,
DETAIL.ID as GROUPID,
'' as ERRORMESSAGE,
null as REVERSALID
from dbo.REVENUE
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID
inner join
(select ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'D' as TRANTYPE, DEBITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.REVENUEGLDISTRIBUTION
union all
select ID, REVENUEID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'C' as TRANTYPE, CREDITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.REVENUEGLDISTRIBUTION)
as DETAIL on REVENUE.ID = DETAIL.REVENUEID
where
(REVENUE.ID = @REVENUEID or @REVENUEID is null)
and ((REVENUE.DONOTPOST = 0 and REVENUEPOSTED.ID is null) or (REVENUEPOSTED.ID is not null and ADJUSTMENT.POSTSTATUSCODE = 1))
and REVENUE.TRANSACTIONTYPECODE <> 2 and REVENUE.TRANSACTIONTYPECODE <> 3;
/*
Get the GL Distribution rows for revenue generated by the system
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select tf.REVENUEID,
tf.REVENUESPLITID,
tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
0 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
tf.DEBITCREDIT,
convert(varchar(10),tf.TRANDATE, 101),
convert(varchar(10),tf.POSTDATE, 101),
tf.POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECT,
tf.AMOUNT,
@JOURNAL as JOURNAL,
tf.REFERENCE,
@BATCH as BATCH,
0 AS ISREVERSAL,
tf.ISADJUSTED,
tf.GROUPID,
tf.ERRORMESSAGE,
null as REVERSALID
from dbo.UFN_REVENUE_GENERATEGLDISTRIBUTION(default) as tf
where
(tf.REVENUEID = @REVENUEID or @REVENUEID is null)
and tf.POSTSTATUSCODE = 1
and not exists (select ID from dbo.REVENUEGLDISTRIBUTION where REVENUEGLDISTRIBUTION.REVENUEID = tf.REVENUEID)
and tf.TRANSACTIONTYPECODE <> 2 -- is not a recurring gift
and tf.TRANSACTIONTYPECODE <> 3; -- matching gift claim
/*
Get the GL Distribution rows for Stock Detail records from StockDetailGLDistribution table
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select REVENUE.ID,
DETAIL.ID as REVENUESPLITID,
DETAIL.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
1 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
DETAIL.TRANTYPE as DEBITCREDIT,
case when STOCKDETAILADJUSTMENT.ID is null then convert(varchar(10),STOCKDETAIL.SALEDATE,101) --if unposted adjustment exists we want to use it's date for post process else just use STOCKDETAIL Date
else convert(varchar(10), STOCKDETAILADJUSTMENT.DATE, 101)
end as TRANDATE,
case when STOCKDETAILADJUSTMENT.ID is null then convert(varchar(10), STOCKDETAIL.SALEPOSTDATE, 101) -- same reason as above
else convert(varchar(10), STOCKDETAILADJUSTMENT.POSTDATE, 101)
end as POSTDATE,
case when STOCKDETAILADJUSTMENT.ID is null then STOCKDETAIL.SALEPOSTSTATUSCODE -- same reason as above.
else STOCKDETAILADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
DETAIL.ACCOUNT,
DETAIL.PROJECT,
DETAIL.AMOUNT,
@JOURNAL as JOURNAL,
DETAIL.REFERENCE as REFERENCE,
@BATCH as BATCH,
0 as ISREVERSAL,
case when STOCKDETAILADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED,
DETAIL.ID,
'' as ERRORMESSAGE,
null as REVERSALID
from dbo.REVENUE
inner join REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.STOCKDETAIL on REVENUE.ID = STOCKDETAIL.ID
left join dbo.STOCKDETAILADJUSTMENT on STOCKDETAIL.ID = STOCKDETAILADJUSTMENT.STOCKDETAILID
inner join
(select ID, STOCKDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'C' as TRANTYPE, CREDITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.STOCKDETAILGLDISTRIBUTION
union all
select ID, STOCKDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'D' as TRANTYPE, DEBITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.STOCKDETAILGLDISTRIBUTION)
as DETAIL on STOCKDETAIL.ID = DETAIL.STOCKDETAILID
where
(STOCKDETAIL.ID = @REVENUEID or @REVENUEID is null)
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 7
and (STOCKDETAIL.SALEPOSTSTATUSCODE = 1 or (STOCKDETAIL.SALEPOSTSTATUSCODE = 0 and STOCKDETAILADJUSTMENT.POSTSTATUSCODE = 1));
/*
Get the GL Distribution rows for stock detail revenue generated by the system
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select tf.REVENUEID,
tf.REVENUESPLITID,
tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
1 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
tf.DEBITCREDIT,
convert(varchar(10),tf.TRANDATE, 101),
convert(varchar(10),tf.POSTDATE, 101),
tf.POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECT,
tf.AMOUNT,
@JOURNAL as JOURNAL,
tf.REFERENCE,
@BATCH as BATCH,
0 AS ISREVERSAL,
tf.ISADJUSTED,
tf.GROUPID,
tf.ERRORMESSAGE,
null as REVERSALID
from dbo.UFN_REVENUE_GENERATESTOCKDETAILGLDISTRIBUTION(default) as tf
where
(tf.REVENUEID = @REVENUEID or @REVENUEID is null)
and tf.PAYMENTMETHODCODE = 7
and tf.POSTSTATUSCODE = 1
and not exists (select ID from dbo.STOCKDETAILGLDISTRIBUTION where STOCKDETAILGLDISTRIBUTION.STOCKDETAILID = tf.REVENUEID);
/*
Get the GL Distribution rows for property detail records from PropertyDetailGLDistribution table
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select REVENUE.ID,
DETAIL.ID as REVENUESPLITID,
DETAIL.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
1 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
DETAIL.TRANTYPE as DEBITCREDIT,
case when PROPERTYDETAILADJUSTMENT.ID is null then convert(varchar(10),PROPERTYDETAIL.SALEDATE,101) --if unposted adjustment exists we want to use it's date for post process else just use Revenue Date
else convert(varchar(10), PROPERTYDETAILADJUSTMENT.DATE, 101)
end as TRANDATE,
case when PROPERTYDETAILADJUSTMENT.ID is null then convert(varchar(10), PROPERTYDETAIL.SALEPOSTDATE, 101) -- same reason as above
else convert(varchar(10), PROPERTYDETAILADJUSTMENT.POSTDATE, 101)
end as POSTDATE,
case when PROPERTYDETAILADJUSTMENT.ID is null then PROPERTYDETAIL.SALEPOSTSTATUSCODE -- same reason as above.
else PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
DETAIL.ACCOUNT,
DETAIL.PROJECT,
DETAIL.AMOUNT,
@JOURNAL as JOURNAL,
DETAIL.REFERENCE as REFERENCE,
@BATCH as BATCH,
0 as ISREVERSAL,
case when PROPERTYDETAILADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED,
DETAIL.ID,
'' as ERRORMESSAGE,
null as REVERSALID
from dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.PROPERTYDETAIL on REVENUE.ID = PROPERTYDETAIL.ID
left join dbo.PROPERTYDETAILADJUSTMENT on PROPERTYDETAIL.ID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID
inner join
(select ID, PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'C' as TRANTYPE, CREDITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.PROPERTYDETAILGLDISTRIBUTION
union all
select ID, PROPERTYDETAILID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'D' as TRANTYPE, DEBITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.PROPERTYDETAILGLDISTRIBUTION)
as DETAIL on PROPERTYDETAIL.ID = DETAIL.PROPERTYDETAILID
where
(PROPERTYDETAIL.ID = @REVENUEID or @REVENUEID is null)
and REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 8
and (PROPERTYDETAIL.SALEPOSTSTATUSCODE = 1 or (PROPERTYDETAIL.SALEPOSTSTATUSCODE = 0 and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1));
/*
Get the GL Distribution rows for property detail revenue generated by the system
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select tf.REVENUEID,
tf.REVENUESPLITID,
tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
1 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
tf.DEBITCREDIT,
convert(varchar(10),tf.TRANDATE, 101),
convert(varchar(10),tf.POSTDATE, 101),
tf.POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECT,
tf.AMOUNT,
@JOURNAL as JOURNAL,
tf.REFERENCE,
@BATCH as BATCH,
0 AS ISREVERSAL,
tf.ISADJUSTED,
tf.GROUPID,
tf.ERRORMESSAGE,
null as REVERSALID
from dbo.UFN_REVENUE_GENERATEPROPERTYDETAILGLDISTRIBUTION(default) as tf
where
(tf.REVENUEID = @REVENUEID or @REVENUEID is null)
and tf.PAYMENTMETHODCODE = 8
and tf.POSTSTATUSCODE = 1
and not exists (select ID from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = tf.REVENUEID);
/*
Get the GL Distribution rows for pledge write-off generated by the system
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select tf.REVENUEID,
tf.REVENUESPLITID,
tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.WRITEOFFID as WRITEOFFID,
0 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
tf.DEBITCREDIT,
convert(varchar(10),tf.TRANDATE, 101),
convert(varchar(10),tf.POSTDATE, 101),
tf.POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECT,
tf.AMOUNT,
@JOURNAL as JOURNAL,
tf.REFERENCE,
@BATCH as BATCH,
0 AS ISREVERSAL,
0 as ISADJUSTED,
tf.GROUPID,
tf.ERRORMESSAGE,
null as REVERSALID
from dbo.UFN_REVENUE_GENERATEWRITEOFFGLDISTRIBUTION(default) as tf
where
(tf.REVENUEID = @REVENUEID or @REVENUEID is null)
and (tf.POSTSTATUSCODE = 1)
and not exists (select ID from dbo.WRITEOFFGLDISTRIBUTION where WRITEOFFGLDISTRIBUTION.WRITEOFFID = tf.WRITEOFFID);
/*
Get the GL Distribution rows for write-off records from WriteOffGLDistribution table
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select REVENUE.ID,
DETAIL.ID as REVENUESPLITID,
DETAIL.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
WRITEOFF.ID as WRITEOFFID,
1 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
DETAIL.TRANTYPE as DEBITCREDIT,
case when WRITEOFFADJUSTMENT.ID is null then convert(varchar(10),WRITEOFF.DATE,101) --if unposted adjustment exists we want to use it's date for post process else just use Revenue Date
else convert(varchar(10), WRITEOFFADJUSTMENT.DATE, 101)
end as TRANDATE,
case when WRITEOFFADJUSTMENT.ID is null then convert(varchar(10), WRITEOFF.POSTDATE, 101) -- same reason as above
else convert(varchar(10), WRITEOFFADJUSTMENT.POSTDATE, 101)
end as POSTDATE,
case when WRITEOFFADJUSTMENT.ID is null then WRITEOFF.POSTSTATUSCODE -- same reason as above.
else WRITEOFFADJUSTMENT.POSTSTATUSCODE
end as POSTSTATUSCODE,
DETAIL.ACCOUNT,
DETAIL.PROJECT,
DETAIL.AMOUNT,
@JOURNAL as JOURNAL,
DETAIL.REFERENCE as REFERENCE,
@BATCH as BATCH,
0 as ISREVERSAL,
case when WRITEOFFADJUSTMENT.ID is null then 0 else 1 end as ISADJUSTED,
DETAIL.ID,
'' as ERRORMESSAGE,
null as REVERSALID
from dbo.REVENUE
inner join dbo.WRITEOFF on REVENUE.ID = WRITEOFF.REVENUEID
left join dbo.WRITEOFFADJUSTMENT on WRITEOFF.ID = WRITEOFFADJUSTMENT.WRITEOFFID
inner join
(select ID, WRITEOFFID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'C' as TRANTYPE, CREDITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.WRITEOFFGLDISTRIBUTION
union all
select ID, WRITEOFFID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, 'D' as TRANTYPE, DEBITACCOUNT as ACCOUNT, PROJECT, AMOUNT, REFERENCE from dbo.WRITEOFFGLDISTRIBUTION)
as DETAIL on WRITEOFF.ID = DETAIL.WRITEOFFID
where
(REVENUE.ID = @REVENUEID or @REVENUEID is null)
and (WRITEOFF.POSTSTATUSCODE = 1 or (WRITEOFF.POSTSTATUSCODE = 0 and WRITEOFFADJUSTMENT.POSTSTATUSCODE = 1));
/*
Getting reversals
*/
insert into @GLDISTRIBUTION(REVENUEID, REVENUESPLITID, GLPAYMENTMETHODREVENUETYPEMAPPINGID, WRITEOFFID, ISSOLDPROPERTYORSTOCK, TRANTYPE, DEBITCREDIT, TRANDATE, POSTDATE,
POSTSTATUSCODE, ACCOUNTSTRING, PROJECT, AMOUNT, JOURNAL, REFERENCE, BATCH, ISREVERSAL, ISADJUSTED, GROUPID, ERRORMESSAGE, REVERSALID)
select
REVERSAL.REVENUEID as REVENUEID,
NULL as REVENUESPLITID,
REVERSAL.GLPAYMENTMETHODREVENUETYPEMAPPINGID,
NULL as WRITEOFFID,
0 as ISSOLDPROPERTYORSTOCK,
@TRANTYPE as TRANTYPE,
REVERSAL.TRANTYPE as DEBITCREDIT,
convert(varchar(10),REVERSAL.DATEADDED, 101) as TRANDATE,
convert(varchar(10),REVERSAL.POSTDATE, 101),
REVERSAL.POSTSTATUSCODE,
REVERSAL.ACCOUNTSTRING,
REVERSAL.PROJECT,
REVERSAL.AMOUNT,
@JOURNAL as JOURNAL,
case ISDELETE when 0 then REVERSAL.REFERENCE else REVERSAL.REFERENCE + '-DEL' end as REFERENCE,
@BATCH as BATCH,
1 as ISREVERSAL,
0 as ISADJUSTED,
REVERSAL.ID as GROUPID,
'' as ERRORMESSAGE,
REVERSAL.ID as REVERSALID
from
(select 'D' as TRANTYPE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REFERENCE, DATEADDED, POSTDATE, POSTSTATUSCODE, DEBITACCOUNT AS ACCOUNTSTRING, PROJECT, AMOUNT, ID, ISDELETE, REVENUEID from dbo.GLREVERSAL
union
select 'C' as TRANTYPE, GLPAYMENTMETHODREVENUETYPEMAPPINGID, REFERENCE, DATEADDED, POSTDATE, POSTSTATUSCODE, CREDITACCOUNT AS ACCOUNTSTRING, PROJECT, AMOUNT, ID, ISDELETE, REVENUEID from dbo.GLREVERSAL) AS REVERSAL
where
REVERSAL.POSTSTATUSCODE = 1 and @REVENUEID is null;
return;
end