USP_DATALIST_REVENUEGLREVERSALBYTRANSACTION
A datalist of revenue GL reversal distribution by transaction.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_REVENUEGLREVERSALBYTRANSACTION(@ID uniqueidentifier)
as
set nocount on;
select
REVENUEGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.REVENUEGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on REVENUEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
REVENUEGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
STOCKSALEGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.STOCKSALEGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on STOCKSALEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
STOCKSALEGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
GIFTINKINDSALEGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.GIFTINKINDSALEGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on GIFTINKINDSALEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
GIFTINKINDSALEGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
PROPERTYDETAILGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.PROPERTYDETAILGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on PROPERTYDETAILGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
WRITEOFFGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.WRITEOFFGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on WRITEOFFGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
WRITEOFFGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
GIFTAIDGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.GIFTAIDGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on GIFTAIDGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
GIFTAIDGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
BENEFITGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.BENEFITGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on BENEFITGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on BENEFITGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
BENEFITGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
GIFTFEEGLDISTRIBUTION.REVENUEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.GIFTFEEGLDISTRIBUTION
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on GIFTFEEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join
dbo.GLTRANSACTION GL1 on GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
GIFTFEEGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID,
MAP.DESCRIPTION,
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
GL2.AMOUNT,
GL2.REFERENCE,
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.TRANSACTIONAMOUNT,0) else GL2.TRANSACTIONAMOUNT end as [TRANSACTIONAMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.AMOUNT,0) else GL2.AMOUNT end as [AMOUNT],
case when GL2.TRANSACTIONCURRENCYID is null then nullif(GL2.ORGANIZATIONAMOUNT,0) else GL2.ORGANIZATIONAMOUNT end as [ORGANIZATIONAMOUNT],
GL2.TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.AUCTIONPURCHASEGLDISTRIBUTION
inner join dbo.GLPAYMENTMETHODREVENUETYPEMAPPING as MAP on AUCTIONPURCHASEGLDISTRIBUTION.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
inner join dbo.GLTRANSACTION GL1 on AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID = @ID
and GL2.POSTSTATUSCODE = 1
union all
select
UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID,
'Currency revaluation',
GL2.TRANSACTIONTYPE,
GL2.ACCOUNT,
GL2.PROJECT,
nullif(GL2.AMOUNT, 0) as [AMOUNT],
GL2.REFERENCE,
null as [TRANSACTIONAMOUNT],
nullif(GL2.AMOUNT, 0) as [BASEAMOUNT],
nullif(GL2.ORGANIZATIONAMOUNT, 0) as [ORGANIZATIONAMOUNT],
null as TRANSACTIONCURRENCYID,
GL2.BASECURRENCYID
from
dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION
inner join
dbo.GLTRANSACTION GL1 on UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID = GL1.ID
inner join
dbo.GLTRANSACTION GL2 on GL1.ID = GL2.REVERSEDGLTRANSACTIONID
where
UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = @ID
and GL2.POSTSTATUSCODE = 1
order by
MAP.DESCRIPTION,GL2.TRANSACTIONTYPE desc;