USP_POSTTOGLPROCESS_UPDATEREVENUEPOSTSTATUS
Marks the Revenue table rows as Processed or Posted.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @OUTPUTTABLE | nvarchar(255) | IN | |
| @POSTINGOPTION | tinyint | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @LEGACYGLBUSINESSPROCESSSTATUSID | uniqueidentifier | IN | |
| @BASICGLBUSINESSPROCESSSTATUSID | uniqueidentifier | IN |
Definition
Copy
CREATE proc dbo.USP_POSTTOGLPROCESS_UPDATEREVENUEPOSTSTATUS
(
@OUTPUTTABLE nvarchar(255),
@POSTINGOPTION tinyint = 0,
@CHANGEAGENTID uniqueidentifier,
@LEGACYGLBUSINESSPROCESSSTATUSID uniqueidentifier = null,
@BASICGLBUSINESSPROCESSSTATUSID uniqueidentifier = null
)
as
set nocount on;
declare @POSTSTATUSCODE tinyint;
declare @SQL nvarchar(4000);
set @POSTSTATUSCODE = 0;
declare @POSTSTATUSCODE2 tinyint = 2;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin transaction UPDATEREVENUE;
begin try
/*Use a temp table to store GL details so it can be accessed across dynamic sql calls*/
declare @POSTDETAILS_TEMPTABLE nvarchar(100) = '##REVENUEPOSTED_' + replace(cast(newid() as nvarchar(36)), '-', '_')
set @SQL =
'
create table dbo.[' + @POSTDETAILS_TEMPTABLE + ']
(
REVENUEID uniqueidentifier,
GLBATCHID uniqueidentifier,
AMOUNT money,
REFERENCE nvarchar(255)
)
'
exec sp_executesql @SQL;
/*Capture GiftAid Transactions*/
declare @GIFTAID_TEMPTABLE nvarchar(100) = '##GIFTAID_' + replace(cast(newid() as nvarchar(36)), '-', '_')
set @SQL =
'
create table dbo.[' + @GIFTAID_TEMPTABLE + ']
(
JOURNALENTRYID uniqueidentifier
)
'
exec sp_executesql @SQL;
set @SQL =
'
insert into dbo.[' + @GIFTAID_TEMPTABLE + '](JOURNALENTRYID)
select T1.ID
from dbo.JOURNALENTRY_EXT T1
inner join ' + @OUTPUTTABLE + ' as [T2] on [T2].GLTRANSACTIONID = T1.ID
where T1.TABLENAMECODE = 7
group by T1.ID
'
exec sp_executesql @SQL;
/* Insert a GLBATCH record. */
set @SQL = 'insert into dbo.GLBATCH(ID,POSTDATE,POSTPROCESSSTATUSID,POSTPROCESSTYPECODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select distinct T.BATCHID,@TODAY,
coalesce(@LEGACYGLBUSINESSPROCESSSTATUSID,@BASICGLBUSINESSPROCESSSTATUSID),
case when not @LEGACYGLBUSINESSPROCESSSTATUSID is null then 0 when not @BASICGLBUSINESSPROCESSSTATUSID is null then 1 else 255 end,
@DYNCHANGEAGENTID, @DYNCHANGEAGENTID, @TODAY, @TODAY
from ' + @OUTPUTTABLE + ' as T';
exec sp_executesql @SQL,
N'@DYNCHANGEAGENTID uniqueidentifier, @TODAY datetime, @LEGACYGLBUSINESSPROCESSSTATUSID uniqueidentifier, @BASICGLBUSINESSPROCESSSTATUSID uniqueidentifier',
@DYNCHANGEAGENTID = @CHANGEAGENTID, @TODAY = @CURRENTDATE,
@LEGACYGLBUSINESSPROCESSSTATUSID = @LEGACYGLBUSINESSPROCESSSTATUSID,
@BASICGLBUSINESSPROCESSSTATUSID = @BASICGLBUSINESSPROCESSSTATUSID;
if (select dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7')) = 1
begin
/* Mark the bank account transaction records as posted for a deposit */
set @SQL = 'update T1
set T1.POSTSTATUSCODE = @DYNPOSTSTATUSCODE, T1.CHANGEDBYID = @DYNCHANGEAGENTID, T1.DATECHANGED = GETDATE()
from dbo.FINANCIALTRANSACTION T1
inner join ' + @OUTPUTTABLE + ' T2 on T1.ID = T2.DEPOSITID and T1.TYPECODE = 10
where T1.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE2;
set @SQL = 'update T2
set T2.POSTSTATUSCODE = @DYNPOSTSTATUSCODE, T2.CHANGEDBYID = @DYNCHANGEAGENTID, T2.DATECHANGED = GETDATE()
from dbo.FINANCIALTRANSACTION T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.ID = T2.FINANCIALTRANSACTIONID
inner join ' + @OUTPUTTABLE + ' T3 on T1.ID = T3.DEPOSITID and T1.TYPECODE = 10
where T2.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE2;
/* Mark the revenue records that are linked to a deposit with a post date of the deposit */
set @SQL = 'update T1
set T1.POSTDATE = T6.POSTDATE ,CHANGEDBYID = @DYNCHANGEAGENTID ,DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.FINANCIALTRANSACTIONLINEITEMID = T2.ID and T2.TYPECODE != 3
inner join dbo.FINANCIALTRANSACTION T3 on T2.FINANCIALTRANSACTIONID = T3.ID
inner join dbo.BANKACCOUNTDEPOSITPAYMENT T4 on T4.ID = T3.ID
inner join ' + @OUTPUTTABLE + ' T5 on T1.ID = T5.GLTRANSACTIONID and T5.RECORDID = T3.ID
inner join dbo.FINANCIALTRANSACTION T6 on T6.ID = T4.DEPOSITID
where T2.REVERSEDLINEITEMID not in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where TYPECODE = 3)
or T2.REVERSEDLINEITEMID is null';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/* Mark the revenue records that are linked to a deposit with a post date of the deposit */
set @SQL = 'update T1
set T1.POSTDATE = isnull(T4.POSTDATE, isnull(T3.POSTDATE, T1.POSTDATE)) ,CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = getdate()
from dbo.FINANCIALTRANSACTION T1
inner join ' + @OUTPUTTABLE + ' T2 on T2.RECORDID = T1.ID
left outer join dbo.FINANCIALTRANSACTION T3 on T3.ID = T2.DEPOSITID and T3.TYPECODE = 10
left outer join dbo.ADJUSTMENT T4 on T4.REVENUEID = T1.ID and T4.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/* Mark the revenue records that are linked to a deposit with a post date of the deposit */
set @SQL = 'update T2
set T2.POSTDATE = T6.POSTDATE ,CHANGEDBYID = @DYNCHANGEAGENTID ,DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.FINANCIALTRANSACTIONLINEITEMID = T2.ID and T2.TYPECODE != 3
inner join dbo.FINANCIALTRANSACTION T3 on T2.FINANCIALTRANSACTIONID = T3.ID
inner join dbo.BANKACCOUNTDEPOSITPAYMENT T4 on T4.ID = T3.ID
inner join ' + @OUTPUTTABLE + ' T5 on T1.ID = T5.GLTRANSACTIONID and T5.RECORDID = T3.ID
inner join dbo.FINANCIALTRANSACTION T6 on T6.ID = T4.DEPOSITID
where T2.REVERSEDLINEITEMID not in (select ID from dbo.FINANCIALTRANSACTIONLINEITEM where TYPECODE = 3)
or T2.REVERSEDLINEITEMID is null';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/*Make the bank account deposit as locked */
set @SQL = 'update dbo.BANKACCOUNTDEPOSIT set STATUSCODE = 0, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.BANKACCOUNTDEPOSIT
inner join ' + @OUTPUTTABLE + ' as T on BANKACCOUNTDEPOSIT.ID = T.DEPOSITID';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/* Mark the bank account transaction records as posted for a deposit */
set @SQL = 'update T1
set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.FINANCIALTRANSACTION T1
inner join ' + @OUTPUTTABLE + ' T2 on T1.ID = T2.RECORDID
where T1.POSTSTATUSCODE = 1 and T1.TYPECODE = 11'
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE2;
/* Mark the refund records that are linked to a deposit with a post date of the deposit */
set @SQL = 'update T1
set T1.POSTDATE = T7.POSTDATE ,CHANGEDBYID = @DYNCHANGEAGENTID ,DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.JOURNALENTRY_EXT T2 on T1.ID = T2.ID and TABLENAMECODE = 6
inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T1.FINANCIALTRANSACTIONLINEITEMID = T3.ID
inner join dbo.FINANCIALTRANSACTION T4 on T3.FINANCIALTRANSACTIONID = T4.ID
inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT T5 on T2.CREDITPAYMENTID = T5.ID
inner join ' + @OUTPUTTABLE + ' T6 on T1.ID = T6.GLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTION T7 on T7.ID = T5.DEPOSITID';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
set @SQL = 'update T3
set T3.POSTDATE = T7.POSTDATE ,CHANGEDBYID = @DYNCHANGEAGENTID ,DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.JOURNALENTRY_EXT T2 on T1.ID = T2.ID and TABLENAMECODE = 6
inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T1.FINANCIALTRANSACTIONLINEITEMID = T3.ID
inner join dbo.FINANCIALTRANSACTION T4 on T3.FINANCIALTRANSACTIONID = T4.ID
inner join dbo.BANKACCOUNTDEPOSITCREDITPAYMENT T5 on T2.CREDITPAYMENTID = T5.ID
inner join ' + @OUTPUTTABLE + ' T6 on T1.ID = T6.GLTRANSACTIONID
inner join dbo.FINANCIALTRANSACTION T7 on T7.ID = T5.DEPOSITID';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/* Mark the Unrealized Gain/Loss Adjustment records as posted */
set @SQL = 'update dbo.UNREALIZEDGAINLOSSADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION on GLTRANSACTION.ID = UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.UNREALIZEDGAINLOSSADJUSTMENT on UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID = UNREALIZEDGAINLOSSADJUSTMENT.REVENUEID
where UNREALIZEDGAINLOSSADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Update WRITEOFF with adjustment postdate */
set @SQL = 'update T3
set T3.POSTDATE = isnull(T4.POSTDATE,T3.POSTDATE) ,CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = getdate()
from dbo.FINANCIALTRANSACTION T1
inner join ' + @OUTPUTTABLE + ' T2 on T2.RECORDID = T1.ID
left outer join dbo.FINANCIALTRANSACTION T3 on T3.PARENTID = T1.ID and T3.TYPECODE = 20
left outer join dbo.WRITEOFFADJUSTMENT T4 on T4.WRITEOFFID = T3.ID and T4.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
end
/* Mark the GL Transaction records as posted */
set @SQL = 'update T3
set T3.POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.FINANCIALTRANSACTIONLINEITEMID = T2.ID
inner join dbo.FINANCIALTRANSACTION T3 on T2.FINANCIALTRANSACTIONID = T3.ID
inner join ' + @OUTPUTTABLE + ' T4 on T1.ID = T4.GLTRANSACTIONID
left outer join ' + @GIFTAID_TEMPTABLE + ' T5 on T1.ID = T5.JOURNALENTRYID
where T3.TYPECODE between 0 and 99 and T3.POSTSTATUSCODE = 1 and T5.JOURNALENTRYID is null ';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE2;
set @SQL = 'update T2
set T2.POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.FINANCIALTRANSACTIONLINEITEM T2 on T1.FINANCIALTRANSACTIONLINEITEMID = T2.ID
inner join dbo.FINANCIALTRANSACTION T3 on T2.FINANCIALTRANSACTIONID = T3.ID
inner join ' + @OUTPUTTABLE + ' T4 on T1.ID = T4.GLTRANSACTIONID
where T2.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE2;
/* set batch number for all */
set @SQL = 'update T2
set T2.BATCHID = T3.BATCHID, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = getdate()
from dbo.JOURNALENTRY T1
inner join dbo.JOURNALENTRY_EXT T2 on T1.ID = T2.ID
inner join ' + @OUTPUTTABLE + ' T3 on T1.ID = T3.GLTRANSACTIONID';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier', @DYNCHANGEAGENTID = @CHANGEAGENTID;
/*Store the post details into the temp table. NOTE: This will only store the details for newly posted revenues*/
set @SQL =
'
insert into dbo.[' + @POSTDETAILS_TEMPTABLE + '](REVENUEID, GLBATCHID, AMOUNT, REFERENCE)
select
T4.FINANCIALTRANSACTIONID as REVENUEID
,T2.BATCHID as [GLBATCHID]
,sum(T1.TRANSACTIONAMOUNT)
,isnull(nullif(T6.REFERENCE, ''''), max(T1.COMMENT)) as REFERENCE
from dbo.JOURNALENTRY T1
inner join dbo.JOURNALENTRY_EXT T2 on T1.ID = T2.ID
inner join ' + @OUTPUTTABLE + ' as [T3] on [T3].GLTRANSACTIONID = T1.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM T4 on T1.FINANCIALTRANSACTIONLINEITEMID = T4.ID
left join dbo.REVENUEPOSTEDDETAIL T5 on T5.REVENUEPOSTEDID = T4.FINANCIALTRANSACTIONID
left join dbo.REVENUE_EXT T6 on T6.ID = T4.FINANCIALTRANSACTIONID
where T5.REVENUEPOSTEDID is null and T1.TYPECODE = 0
and T2.TABLENAMECODE = 1 and T1.TRANSACTIONTYPECODE = 1
group by T4.FINANCIALTRANSACTIONID, T2.BATCHID, T6.REFERENCE
'
exec sp_executesql @SQL;
/*Store the post details for auction purchases into the temp table. */
set @SQL =
'
insert into dbo.[' + @POSTDETAILS_TEMPTABLE + '](REVENUEID, GLBATCHID, AMOUNT, REFERENCE)
select
T4.FINANCIALTRANSACTIONID as REVENUEID
,T2.BATCHID as [GLBATCHID]
,sum(T1.TRANSACTIONAMOUNT)
,isnull(nullif(T6.REFERENCE, ''''), max(T1.COMMENT)) as REFERENCE
from dbo.JOURNALENTRY T1
inner join dbo.JOURNALENTRY_EXT T2 on T1.ID = T2.ID
inner join ' + @OUTPUTTABLE + ' as [T3] on [T3].GLTRANSACTIONID = T1.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM T4 on T1.FINANCIALTRANSACTIONLINEITEMID = T4.ID
left join dbo.REVENUEPOSTEDDETAIL T5 on T5.REVENUEPOSTEDID = T4.FINANCIALTRANSACTIONID
left join dbo.REVENUE_EXT T6 on T6.ID = T4.FINANCIALTRANSACTIONID
where T5.REVENUEPOSTEDID is null and T1.TYPECODE = 0
and T2.TABLENAMECODE = 2 and T1.TRANSACTIONTYPECODE = 1
group by T4.FINANCIALTRANSACTIONID, T2.BATCHID, T6.REFERENCE
'
exec sp_executesql @SQL;
/* Store the post details for each newly posted revenue */
set @SQL =
'
insert into dbo.REVENUEPOSTEDDETAIL(REVENUEPOSTEDID, GLBATCHID, AMOUNT, REFERENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
REVENUEID,
GLBATCHID,
AMOUNT,
REFERENCE,
@DYNCHANGEAGENTID,
@DYNCHANGEAGENTID,
@TODAY,
@TODAY
from dbo.[' + @POSTDETAILS_TEMPTABLE + ']
'
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @TODAY datetime', @DYNCHANGEAGENTID = @CHANGEAGENTID, @TODAY = @CURRENTDATE;
/* Mark the gift aid as posted */
set @SQL = 'insert into dbo.REVENUESPLITGIFTAIDPOSTED (ID,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
select distinct GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID, @DYNCHANGEAGENTID, @DYNCHANGEAGENTID, @TODAY, @TODAY
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.GIFTAIDGLDISTRIBUTION on GLTRANSACTION.ID = GIFTAIDGLDISTRIBUTION.GLTRANSACTIONID
left join dbo.REVENUESPLITGIFTAIDPOSTED on REVENUESPLITGIFTAIDPOSTED.ID = GIFTAIDGLDISTRIBUTION.REVENUESPLITGIFTAIDID
where REVENUESPLITGIFTAIDPOSTED.ID is null';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint, @TODAY datetime', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE, @TODAY = @CURRENTDATE;
/* Mark the Plannedgiftpayout Adjustment records as posted */
set @SQL = 'update T5 set T5.POSTSTATUSCODE = @DYNPOSTSTATUSCODE, T5.CHANGEDBYID = @DYNCHANGEAGENTID, T5.DATECHANGED = GETDATE()
from dbo.JOURNALENTRY T
inner join ' + @OUTPUTTABLE + ' as T1 on T.ID = T1.GLTRANSACTIONID
inner join dbo.JOURNALENTRY_EXT T2 on T.ID = T2.ID and T2.TABLENAMECODE = 9
inner join dbo.FINANCIALTRANSACTIONLINEITEM T3 on T3.ID = T.FINANCIALTRANSACTIONLINEITEMID
inner join dbo.FINANCIALTRANSACTION T4 on T4.ID = T3.FINANCIALTRANSACTIONID
inner join dbo.PLANNEDGIFTPAYOUTADJUSTMENT T5 on T4.PARENTID = T5.REVENUEID
where T5.POSTSTATUSCODE = 1 ';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the Stock Detail Adjustment records as posted */
set @SQL = 'update dbo.STOCKSALEADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.STOCKSALEGLDISTRIBUTION on GLTRANSACTION.ID = STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.STOCKSALEADJUSTMENT on STOCKSALEGLDISTRIBUTION.STOCKSALEID = STOCKSALEADJUSTMENT.STOCKSALEID
where STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the Gift-in-kind Detail Adjustment records as posted */
set @SQL = 'update dbo.GIFTINKINDSALEADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.GIFTINKINDSALEGLDISTRIBUTION on GLTRANSACTION.ID = GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.GIFTINKINDSALEADJUSTMENT on GIFTINKINDSALEGLDISTRIBUTION.GIFTINKINDSALEID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID
where GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the Property Detail Adjustment records as posted */
set @SQL = 'update dbo.PROPERTYDETAILADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.PROPERTYDETAILGLDISTRIBUTION on GLTRANSACTION.ID = PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.PROPERTYDETAILADJUSTMENT on PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID
where PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the Write-off Adjustment records as posted */
set @SQL = 'update dbo.WRITEOFFADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.WRITEOFFGLDISTRIBUTION on GLTRANSACTION.ID = WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.WRITEOFFADJUSTMENT on WRITEOFFGLDISTRIBUTION.WRITEOFFID = WRITEOFFADJUSTMENT.WRITEOFFID
where WRITEOFFADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the benefit adjustment records as posted */
set @SQL = 'update dbo.BENEFITADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.BENEFITGLDISTRIBUTION on GLTRANSACTION.ID = BENEFITGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.BENEFITADJUSTMENT on BENEFITADJUSTMENT.REVENUEID = BENEFITGLDISTRIBUTION.REVENUEID
where BENEFITADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the auction purchase adjustment records as posted */
set @SQL = 'update dbo.AUCTIONPURCHASEADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.AUCTIONPURCHASEGLDISTRIBUTION on GLTRANSACTION.ID = AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.AUCTIONPURCHASEADJUSTMENT on AUCTIONPURCHASEADJUSTMENT.REVENUEID = AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID
where AUCTIONPURCHASEADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the Adjustment records as posted */
set @SQL = 'update dbo.ADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.REVENUEGLDISTRIBUTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = REVENUEGLDISTRIBUTION.REVENUEID
where ADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark auction purchase adjustment records as posted */
set @SQL = 'update dbo.ADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.AUCTIONPURCHASEGLDISTRIBUTION on GLTRANSACTION.ID = AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.ADJUSTMENT on ADJUSTMENT.REVENUEID = AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID
where ADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
/* Mark the gift fee adjustment records as posted */
set @SQL = 'update dbo.GIFTFEEADJUSTMENT set POSTSTATUSCODE = @DYNPOSTSTATUSCODE, CHANGEDBYID = @DYNCHANGEAGENTID, DATECHANGED = GETDATE()
from dbo.GLTRANSACTION
inner join ' + @OUTPUTTABLE + ' as T on GLTRANSACTION.ID = T.GLTRANSACTIONID
inner join dbo.GIFTFEEGLDISTRIBUTION on GLTRANSACTION.ID = GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID
inner join dbo.GIFTFEEADJUSTMENT on GIFTFEEADJUSTMENT.REVENUEID = GIFTFEEGLDISTRIBUTION.REVENUEID
where GIFTFEEADJUSTMENT.POSTSTATUSCODE = 1';
exec sp_executesql @SQL, N'@DYNCHANGEAGENTID uniqueidentifier, @DYNPOSTSTATUSCODE tinyint', @DYNCHANGEAGENTID = @CHANGEAGENTID, @DYNPOSTSTATUSCODE = @POSTSTATUSCODE;
end try
begin catch
if @@TRANCOUNT > 0
rollback transaction UPDATEREVENUE;
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
if @@TRANCOUNT > 0
commit transaction UPDATEREVENUE;
return 0;